Example 6: Complex Parameter Type (CPT)

The Complex Parameter Type Cookbook example shows how to use Liquid Data to create an integrated view that connects two enterprise information systems: a database and an in-flight XML data source using a complex parameter type (CPT). A query that uses both data sources determines whether the customer has sufficient credit for the incoming order to be processed.

The Problem

The company receives dozens of electronically transmitted orders daily and needs to quickly respond to its field office if an order cannot be accepted because a customer has exceeded their credit limit. The credit limit and amount of outstanding orders is known to the system. The quantity and price of the items being ordered is supplied in real-time along with the order.

The Solution

The company develops a complex parameter type (CPT) that models the incoming purchase order as an XML schema and sets a simple orderLimit parameter that an operator can change whenever the query is run. The query also calculates the total amount outstanding of current orders and the total amount of the incoming order. The objective is to accept orders if the total amount of both outstanding and incoming orders is within the order limit. Otherwise, the order is rejected.

To recreate the solution, follow these steps:

• View a Demo
• Ex 6: Step 1. Verify the Availability of Schemas and Sample Data Stream
• Ex 6: Step 2. Open the Target Schema and CO-CPTSAMPLE CPT
• Ex: 6: Step 3. Create an orderLimit Query Parameter
• Ex 6: Step 4. Save the Project
• Ex 6: Step 5. Test Access to the Complex Parameter Source
• Ex 6: Step 6: Determine the Total Amount of New Orders
• Ex 6: Step 7. Create the Necessary Joins and Mappings to the Target Schema
• Ex 6: Step 8. Determine the Amount of Currently Open Orders
• Ex 6: Step 9: Determine the Total Amount of All Open and New Orders
• Ex 6: Step 10: Test If Open Orders + New Orders Exceeds the Order Limit
• Ex 6: Step 11: Determine If the Order is Accepted or Rejected
• Ex 6: Step 12: View the XQuery
• Ex 6: Step 13. Run the XQuery to Verify the Result

Note: The implementation details of the Complex Parameter Type demo, the DB-COCPT sample, and the CPT cookbook example vary slightly.

View a Demo

Complex Parameter Type (CPT) Demo. If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to build the conditions and create the mappings described in this example.

Ex 6: Step 1. Verify the Availability of Schemas and Sample Data Stream

In creating the DB-CPTCO sample query, we use the following files that are installed with Liquid Data samples. (See Example directories for information on how example directory names are used.)

From the Liquid Data samples repository schema directory:

• BroadBand database schema. The path to this file is:

ld_repository/schemas/broadbandp.sql

• Complex parameter type schema. The path to this file is:

ld_repository/schemas/coCptSample2.xsd

• Target schema. The path to this file is:

ld_repository/schemas/COCPTSampleTarget-Schema.xsd

• CPT sample XML stream. The path to this file is:

ld_repository/xml_files/coCPTsample2.xml

If you want to refer to the sample DB-CPTCO project, it is installed as the following file:

<BEA_HOME>/liquiddata/samples/buildQuery/db-cptco/coCPTSample.qpr

For reference purposes, code listings for several of the XML files used in this example appear below:

Listing 9-17 DB-CPTCO Sample CPT Schema (coCptSample2.xsd)

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt"
								xmlns:cocpt="urn:schemas-bea-com:ld-cocpt"
								xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="CustOrder">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="CUSTOMER_ORDER" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
							<xsd:element name=
											"NEW_ORDER_LINE_ITEM"type="cocpt:NEW_ORDER_LINE_ITEMType" 
											minOccurs="0" maxOccurs="unbounded"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:complexType name="NEW_ORDER_LINE_ITEMType">
		<xsd:sequence>
			<xsd:element name="PRODUCT_NAME" type="xsd:string"/>
			<xsd:element name="QUANTITY" type="xsd:decimal"/>
			<xsd:element name="PRICE" type="xsd:decimal"/>
		</xsd:sequence>
	</xsd:complexType>
</xsd:schema>

 

---

Listing 9-18 DB-CPTCO Target Schema (COCPTSampleTargetSchema.xsd)

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema targetNamespace="urn:schemas-bea-com:ld-cocpt" xmlns:cocpt="urn:schemas-bea-com:ld-cocpt" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<xsd:element name="CustOrder">
		<xsd:complexType>
			<xsd:sequence>
				<xsd:element name="CUSTOMER_ORDER" minOccurs="0" maxOccurs="unbounded">
					<xsd:complexType>
						<xsd:sequence>
							<xsd:element name="CUSTOMER_ID" type="xsd:string"/>
							<xsd:element name="NEW_ORDER_LINE_ITEM" type="cocpt:NEW_ORDER_LINE_ITEMType" minOccurs="0" maxOccurs="unbounded"/>
						</xsd:sequence>
					</xsd:complexType>
				</xsd:element>
			</xsd:sequence>
		</xsd:complexType>
	</xsd:element>
	<xsd:complexType name="NEW_ORDER_LINE_ITEMType">
		<xsd:sequence>
			<xsd:element name="PRODUCT_NAME" type="xsd:string"/>
			<xsd:element name="QUANTITY" type="xsd:decimal"/>
			<xsd:element name="PRICE" type="xsd:decimal"/>
		</xsd:sequence>
	</xsd:complexType>
</xsd:schema>

 

---

Listing 9-19 DB-CPTCO Sample XML Data Stream (coCptSample2.xml)

<?xml version="1.0" encoding="UTF-8"?>
<cocpt:CustOrder xmlns:cocpt="urn:schemas-bea-com:ld-cocpt"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="urn:schemas-bea-com:ld-cocpt
		coCptSample2.xsd">	
	<CUSTOMER_ORDER>
		<CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
		<NEW_ORDER_LINE_ITEM>
			<PRODUCT_NAME>RBBC01</PRODUCT_NAME>
			<QUANTITY>1000</QUANTITY>
			<PRICE>20</PRICE>
		</NEW_ORDER_LINE_ITEM>
		<NEW_ORDER_LINE_ITEM>
			<PRODUCT_NAME>CS2610</PRODUCT_NAME>
			<QUANTITY>1000</QUANTITY>
			<PRICE>20</PRICE>
		</NEW_ORDER_LINE_ITEM>
	</CUSTOMER_ORDER>
</cocpt:CustOrder>

 

---

You may also want to examine the CO-CPTSAMPLE definition in the WLS Administration Console for the Samples server you are using.

1. Login to the Administration Server. See Start the WLS Administration Console in the Getting Started guide for details.

2. In the left pane, click the Liquid Data node.

3. In the right pane, click the Complex Parameter Types tab and click on CO-CPTSAMPLE.

See the section Creating a Complex Parameter Type for details.

Ex 6: Step 2. Open the Target Schema and CO-CPTSAMPLE CPT

1. In the Data View Builder, choose File—>New Project.

2. On the Design tab, on the Builder Toolbar, click the Toolbox tab, then click Complex Parameter Type. The CO-CPTSAMPLE complex parameter type is listed.

3. Double-click on CO-CPTSAMPLE to open the CPT schema. Right click on the top element (cocpt:CustOrder) to expand.

4. Choose File—>Set Target Schema. Browse to the Liquid Data repository schema directory.

5. Select the following file as the target schema:

   ld_repository/schemas/COCPTSampleTargetSchema.xsd

   In the target schema window on the right side of the design area, right-click on the top element expand the target schema.

Ex: 6: Step 3. Create an orderLimit Query Parameter

Since credit limits vary from customer to customer, it is convenient to have an order limit query parameter that can be changed whenever a query is run.

1. In the Data View Builder select Design—>Toolbox.

2. Click on the Query Parameter tab.

3. Enter orderLimit as a parameter name.

4. Select xs:decimal as the parameter type.

5. Click Add.

6. Drag and drop the orderLimit parameter icon onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/TOTAL_OPEN_ORDERLIMIT.

Ex 6: Step 4. Save the Project

You can save a project at any time. To initially create a project, use File—>Save Project As. Use the file browser to choose a location and project name (we use myCoCPT).

Ex 6: Step 5. Test Access to the Complex Parameter Source

Follow these steps to verify access to the CPT data source:

1. Drag and drop output from [CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/CUSTOMER_ID onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ID.

2. Click the Test tab.

3. In the lower-left pane of the Data View Builder (Test mode), click in the Values area under Query Parameters to the right of the CPT name (CO-CPTSAMPLE).

4. Navigate to the XML data file associated with the CO-CPTSAMPLE complex parameter type.

   ld_repository/xml_files/coCptSample2.xml

5. Enter an orderLimit value such as 200000.

6. Now we can click the Run button to execute a preliminary query. The following result shows that your CPT is successfully retrieving from the XML file data:

Listing 9-20 Interim Results (1) from CPT Example Query

<cocpt:CustOrderStatus xmlns:cocpt="urn:schemas-bea-com:ld-cocpt">
  <CUSTOMER>
    <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
    <CUSTOMER_ORDER>
      <TOTAL_OPEN_ORDERLIMIT>200000</TOTAL_OPEN_ORDERLIMIT>
    </CUSTOMER_ORDER>
  </CUSTOMER>
</cocpt:CustOrderStatus>

 

---

7. Return to the Toolbar Design mode.

In the case of this data source, the customer identification is provided so there is no need to create a customer ID query parameter.

Ex 6: Step 6: Determine the Total Amount of New Orders

Since all runtime source items from a CPT are treated as character strings, any data items from the CO-CPTSAMPLE data source must first be cast appropriately. Then quantities and prices are multiplied together. The sum of the products of quantity and prices is the total amount of new orders.

1. Click XQuery Functions.

2. Drag and drop (or double-click) two cast as xs:decimal functions into the design area. These are labeled as xs:decimal and xs:decimal2.

3. Drag and drop a multiply (*) function into the design area.

4. Drag and drop a sum aggregate function into the design area.

   Figure 9-5 Functions Used to Calculate Total New Orders in Data Stream

5. Drag and drop [CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/QUANTITY to become the input parameter to xs:decimal.

6. Drag and drop [CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/NEW_ORDER_LINE_ITEM/PRICE to become the input parameter to xs:decimal2.

7. Drag and drop the output result of xs:decimal to one side of the multiply equation and the output result of xs:decimal2 to the other.

8. Drag the * - Function output result to the input parameter of xf:sum. This gives us the total order amount in the CPT data source.

9. Drag and drop output from xf:sum onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/NEW_ORDER_TOTAL_AMOUNT.

10. Rerun your query. A new order total of 40,000 appears.

11. Return to Design mode.

The only query construction components we will reuse are sum and CO-CPTSAMPLE source. The others can be closed or minimized.

Ex 6: Step 7. Create the Necessary Joins and Mappings to the Target Schema

Move the PB-BB relational source schema onto the design area.

1. On the Builder Toolbar Design—>Sources tab, click Relational Databases, and double-click on PB-BB to open the schema for the broadband sample data source.

2. Expand the PB-BB schema.

Create the necessary joins to allow us to fetch the line items for a particular order for a particular customer.

1. Drag and drop [CO-CPTSAMPLE]/cocpt:CustOrder/CUSTOMER_ORDER/CUSTOMER_ID onto [PB-BB]/db/CUSTOMER/CUSTOMER_ID.

2. Drag and drop [PB-BB]/db/CUSTOMER/CUSTOMER_ID onto the [PB-BB]/db/CUSTOMER_ORDER/CUSTOMER_ID.

3. Drag and drop [PB-BB]/db/CUSTOMER_ORDER/ORDER_ID onto [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/ORDER_ID.

   Figure 9-6 Setting Joins Between CPT and Relational Data Source

   

   
    

Next, project FIRST_NAME and LAST_NAME elements onto the target schema.

1. Drag and drop [PB-BB]/db/CUSTOMER/FIRST_NAME onto the target schema [cocptsampletarget-schema]/cocptCustOrderStatus/CUSTOMER/FIRST_NAME.

2. Drag and drop [PB-BB]/db/CUSTOMER/LAST_NAME onto the target schema [cocptsampletarget-schema]/cocptCustOrderStatus/CUSTOMER/LAST_NAME.

Note: In this version of the CO-CPTSAMPLE, automatic scoping is used. See Understanding Scope in Basic and Advanced Views for more information.

Although your query is not complete, you can test run it again.

Listing 9-21 Interim Results (2) from CPT Example Query

<cocpt:CustOrderStatus xmlns:cocpt="urn:schemas-bea-com:ld-cocpt">
  <CUSTOMER>
    <FIRST_NAME>JOHN_B_1</FIRST_NAME>
    <LAST_NAME>KAY_1</LAST_NAME>
    <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
    <CUSTOMER_ORDER>
      <NEWORDER_TOTAL_AMOUNT>40000</NEWORDER_TOTAL_AMOUNT>
      <TOTAL_OPEN_ORDERLIMIT>200000</TOTAL_OPEN_ORDERLIMIT>
    </CUSTOMER_ORDER>
  </CUSTOMER>
</cocpt:CustOrderStatus>

 

---

Ex 6: Step 8. Determine the Amount of Currently Open Orders

Follow these steps to find the total amount of open orders in the sample PB-BB database:

1. Click the XQuery Functions.

2. Drag and drop the multiply (*) operator into the design area.

3. Drag and drop [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/QUANTITY and [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/PRICE to the function operands.

4. Drag and drop another xf:sum into the design area. Drag the output result of the f()*2 - Function (*2 being the second use of a multiplication function) to the input parameter of xf:sum2.

5. Drag and drop output from xf:sum2 onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/OPEN_ORDER_TOTAL_AMOUNT.

Finally, we need to restrict results to open orders:

1. In the Data View Builder select Design—>Toolbox.

2. Click on Constants.

3. Create a string constant called OPEN and drag the icon to the right of OPEN to the [PB-BB]/db/CUSTOMER_ORDER_LINE_ITEM/STATUS item.

   If we run our current query, the amount of open orders should be 150,000.

Ex 6: Step 9: Determine the Total Amount of All Open and New Orders

1. Click XQuery Functions.

2. Drag and drop the plus (+) numeric operator into the design area.

3. Use xf:sum (total new orders) and xf:sum2 (total open orders) as the operands to obtain the total open and new order amount.

Ex 6: Step 10: Test If Open Orders + New Orders Exceeds the Order Limit

1. Click on XQuery Functions.

2. Under Comparison operators locate the gt (greater than) function. Drag it into the design area.

3. Use the output of the sum of open and newly arrived orders as the first input parameter.

4. Use the query parameter orderLimit as input for operand2. The result is a Boolean value that is True if the sum of open and newly arrived orders is greater than the overall order limit.

Ex 6: Step 11: Determine If the Order is Accepted or Rejected

Now that the relationships and conditions are established, set up an if-then-else test to solve the business problem. See The Problem.

1. Under Other functions locate the xfext:if-then-else function. Drag it into the design area.

2. Drag the output of the gt function to the if-then-else input condition parameter.

3. Click on the Toolbox Constants tab. In the String field enter REJECT, drag the String field icon to the then parameter in the xfext:if-then-else function.

4. Change the String field to ACCEPT, then drag the String field icon to the else parameter.

5. Drag the output result onto the target schema [COCPTSampleTargetSchema.xsd]/cocpt:CustOrderStatus/CUSTOMER/CUSTOMER_ORDER/ORDER_REVIEW_STATUS.

Ex 6: Step 12: View the XQuery

The generated XQuery is shown in the following code listing.

Listing 9-22 XQuery for Example 6: Complex Parameter Type (CPT)

{--	Generated by Data View Builder 1.1	--}
namespace cocpt = "urn:schemas-bea-com:ld-cocpt"
<cocpt:CustOrderStatus>
	{
	for $CO_CPTSAMPLE.CUSTOMER_ORDER_2 in ($#CO-CPTSAMPLE of type element cocpt:CustOrder)/CUSTOMER_ORDER
	for $MyBroadBand_LD_DS.CUSTOMER_3 in document("MyBroadBand-LD-DS")/db/CUSTOMER
	let $srcval_4 := 
	               for $MyBroadBand_LD_DS.CUSTOMER_ORDER_LINE_ITEM_5 in document("MyBroadBand-LD-DS")/db/CUSTOMER_ORDER_LINE_ITEM
	               let $srcval_9 := 
	                              for $MyBroadBand_LD_DS.CUSTOMER_ORDER_10 in document("MyBroadBand-LD-DS")/db/CUSTOMER_ORDER
	                              where ($MyBroadBand_LD_DS.CUSTOMER_ORDER_10/ORDER_ID  eq  $MyBroadBand_LD_DS.CUSTOMER_ORDER_LINE_ITEM_5/ORDER_ID)
	                                and ($MyBroadBand_LD_DS.CUSTOMER_3/CUSTOMER_ID  eq  $MyBroadBand_LD_DS.CUSTOMER_ORDER_10/CUSTOMER_ID)
	                              return
	                              xf:true()
	               where xf:not(xf:empty($srcval_9))
	                 and ("OPEN"  eq  $MyBroadBand_LD_DS.CUSTOMER_ORDER_LINE_ITEM_5/STATUS)
	               return
	               $MyBroadBand_LD_DS.CUSTOMER_ORDER_LINE_ITEM_5/QUANTITY * $MyBroadBand_LD_DS.CUSTOMER_ORDER_LINE_ITEM_5/PRICE
	let $xf:sum2_11 := xf:sum($srcval_4)
	let $srcval_12 := 
	               for $CO_CPTSAMPLE.NEW_ORDER_LINE_ITEM_14 in $CO_CPTSAMPLE.CUSTOMER_ORDER_2/NEW_ORDER_LINE_ITEM
	               let $cast_as_xs:decimal2_17 := cast as xs:decimal($CO_CPTSAMPLE.NEW_ORDER_LINE_ITEM_14/QUANTITY)
	               let $cast_as_xs:decimal_20 := cast as xs:decimal($CO_CPTSAMPLE.NEW_ORDER_LINE_ITEM_14/PRICE)
	               return
	               $cast_as_xs:decimal2_17 * $cast_as_xs:decimal_20
	let $xf:sum_22 := xf:sum($srcval_12)
	let $v_23 := $xf:sum2_11 + $xf:sum_22
	let $gt_24 := $v_23 gt $#orderLimit of type xs:decimal
	where ($CO_CPTSAMPLE.CUSTOMER_ORDER_2/CUSTOMER_ID  eq  $MyBroadBand_LD_DS.CUSTOMER_3/CUSTOMER_ID)
	return
	<CUSTOMER>
		<FIRST_NAME>{ xf:data($MyBroadBand_LD_DS.CUSTOMER_3/FIRST_NAME) }</FIRST_NAME>
		<LAST_NAME>{ xf:data($MyBroadBand_LD_DS.CUSTOMER_3/LAST_NAME) }</LAST_NAME>
		<CUSTOMER_ID>{ xf:data($CO_CPTSAMPLE.CUSTOMER_ORDER_2/CUSTOMER_ID) }</CUSTOMER_ID>
		<CUSTOMER_ORDER>
			<OPENORDER_TOTAL_AMOUNT>{ cast as xs:decimal($xf:sum2_11) }</OPENORDER_TOTAL_AMOUNT>
			<NEWORDER_TOTAL_AMOUNT>{ cast as xs:decimal($xf:sum_22) }</NEWORDER_TOTAL_AMOUNT>
			<TOTAL_OPEN_ORDERLIMIT>{ $#orderLimit of type xs:decimal }</TOTAL_OPEN_ORDERLIMIT>
			<ORDER_REVIEW_STATUS>{ cast as xs:string(xfext:if-then-else( treat as xs:boolean($gt_24), "REJECT", "ACCEPT")) }</ORDER_REVIEW_STATUS>
		</CUSTOMER_ORDER>
	</CUSTOMER>
	}
</cocpt:CustOrderStatus>

 

---

Ex 6: Step 13. Run the XQuery to Verify the Result

When you run this query on the sample data sources as described in this example, the result is an accepted order.

Listing 9-23 Result of Example 6: Complex Parameter Type (CPT)

<cocpt:CustOrderStatus xmlns:cocpt="urn:schemas-bea-com:ld-cocpt">
  <CUSTOMER>
    <FIRST_NAME>JOHN_B_1</FIRST_NAME>
    <LAST_NAME>KAY_1</LAST_NAME>
    <CUSTOMER_ID>CUSTOMER_1</CUSTOMER_ID>
    <CUSTOMER_ORDER>
      <OPENORDER_TOTAL_AMOUNT>150000</OPENORDER_TOTAL_AMOUNT>
      <NEWORDER_TOTAL_AMOUNT>40000</NEWORDER_TOTAL_AMOUNT>
      <TOTAL_OPEN_ORDERLIMIT>200000</TOTAL_OPEN_ORDERLIMIT>
      <ORDER_REVIEW_STATUS>ACCEPT</ORDER_REVIEW_STATUS>
    </CUSTOMER_ORDER>
  </CUSTOMER>
</cocpt:CustOrderStatus>

 

---