module namespace example = "http://www.datadirect.com/xquery/examples"; import module namespace ddtek-sql = "http://www.datadirect.com/xquery/sql-function" at "ddtek-sql.xquery"; declare namespace tx="http://ACORD.org/Standards/Life/2"; declare namespace ws="http://webservicemart.com/ws/"; (: 8.169. OLI_LU_GENDER: Gender Type :) declare variable $example:OLI_GENDER_FEMALE as xs:integer := 2; declare variable $example:OLI_GENDER_MALE as xs:integer := 1; (: 8.287. OLI_LU_PARTY: Party Type :) declare variable $example:OLI_PT_PERSON as xs:integer := 1; declare variable $example:OLI_PT_ORG as xs:integer := 2; (:8.275. OLI_LU_OBJECTTYPE: Object Type:) declare variable $example:OLI_PHONE as xs:integer := 3; declare variable $example:OLI_PARTY as xs:integer := 6; declare variable $example:OLI_PERSON as xs:integer := 115; declare variable $example:OLI_ORGANIZATION as xs:integer := 116; (:8.5. CRITERIA_OPERATOR: Criteria Operator:) declare variable $example:LOGICAL_OPERATOR_OR as xs:integer := 1; declare variable $example:LOGICAL_OPERATOR_AND as xs:integer := 2; (: 8.9. INQUIRY_LEVEL_CODES: Inquiry Level :) declare variable $example:OLI_INQUIRY_OBJ as xs:string := "1"; declare variable $example:OLI_INQUIRY_OBJREL as xs:string := "2"; declare function example:trace($value as item()*, $label as xs:string) as item()* { if ( fn:empty($value) ) then fn:trace($value, $label) else for $i in $value where typeswitch ($i) case $attr as attribute() (: serialize does not accept top level attribute nodes :) return fn:not(fn:empty(fn:trace($i, fn:concat("dump of ", $label)))) default return fn:not(fn:empty(fn:trace(ddtek:serialize($i, ""), fn:concat("dump of ", $label)))) return $i }; (: Creates a GUID with uses MS SQLServer NEWID function :) (: The function is made has a dummy argument to avoid optimizing it away :) declare function example:createGUID($dummy) as xs:string { if ( fn:false() ) then example:createGUID($dummy) else ddtek-sql:NEWID() }; (: returns a string representation from a given date :) declare function example:formatDate($date as xs:date) as xs:string { fn:substring(xs:string($date), 1, 10) }; (: returns a string representation from a given time :) declare function example:formatTime($time as xs:time) as xs:string { fn:substring(xs:string($time), 1, 8) }; (: Validate ZIP code with http (USPS) request :) declare function example:getZipInfo2($zipCode as xs:string) as element()? { () }; (: Validate ZIP code with webservice call (http://www.webservicemart.com) :) declare function example:getZipInfo($zipCode as xs:string) as element()? { let $response := ddtek:wscall( , {$zipCode} )/ws:ValidateZipResponse/ws:ValidateZipResult return (: because the webservice returns the result as text (not xml) wrapped in a ValidateZipResult node we have to parse the content :) ddtek:parse($response/text())/* }; (: Creates a TXLife message with non-empty TXLifeResponse :) declare function example:create-response( $request as element(tx:TXLifeRequest), $payLoad as element()*) as element(tx:TXLifeResponse) { { $request/tx:TransRefGUID, $request/tx:TransType, {example:formatDate(fn:current-date())}, {example:formatTime(fn:current-time())}, $payLoad } }; (: creates a TXLife Address object from a database address :) declare function example:create-address($address as element(ACORD_ADDRESS)) { { , {xs:string($address/Line1)}, {xs:string($address/City)}, {xs:string($address/AddressState)}, {xs:string($address/Zip)}, {xs:string($address/AddressCountry)} } }; (: creates a TXLife Party (Person) from a database party and person :) declare function example:create-person( $party as element(ACORD_PARTY), $person as element(ACORD_PERSON), $inquiryLevel as xs:string? ) as element(tx:Party) { let $partyKey := xs:string($party/PartyKey) let $relatedObjects := if ( $inquiryLevel eq $example:OLI_INQUIRY_OBJREL ) then ( (: Example of a 1-N relation {Party -> Address} modeledj with foreign key. ACORD_ADDRESS.OwnerKey must match with ACORD_PARTY.PartyKey :) for $address in collection("ACORD_ADDRESS")/ACORD_ADDRESS[OwnerKey eq $partyKey] return example:create-address($address), (: Example of an N-N relation {Party <-> Phone} modeled with a relation table. :) for $relation in collection("ACORD_RELATION")/ACORD_RELATION[OriginatingObjectKey eq $partyKey] [OriginatingObjectType eq $example:OLI_PARTY] [RelatedObjectType eq $example:OLI_PHONE] for $phone in collection("ACORD_PHONE")/ACORD_PHONE[PhoneKey eq $relation/RelatedObjectKey] return example:create-phone($phone) ) else () return { (: use GUID prefixed with underscore for @id :) attribute id { fn:concat("_", $partyKey) }, Person, {$partyKey}, {xs:string($party/FullName)}, {xs:string($party/GovtID)}, {xs:string($person/FirstName)} {xs:string($person/LastName)} {xs:string($person/BirthDate)} , $relatedObjects } }; (: creates a TXLife Phone object from a database phone :) declare function example:create-phone($phone as element(ACORD_PHONE)) { { , {xs:string($phone/CountryCode)}, {xs:string($phone/AreaCode)}, {xs:string($phone/DialNumber)} } }; (: Implementation of a Person search. Depending on if $input is an empty sequence or not, the search is performed either on the database or a filter is applied on the input sequence (in case $input is a non-empty sequence). Only propertyNames "LastName" and "FirstName" :) declare function example:search-person( $input as element()*, $propertyName as xs:string, $propertyValue as xs:string, $inquiryLevel as xs:string?) as element()* { typeswitch ($input) case empty-sequence() return (: fetch from DBMS:) if ( $propertyName eq "LastName" ) then for $person in collection("ACORD_PERSON")/ACORD_PERSON[LastName eq $propertyValue] for $party in collection("ACORD_PARTY")/ACORD_PARTY[PartyKey eq $person/PartyKey] return example:create-person($party, $person, $inquiryLevel) else if ( $propertyName eq "FirstName" ) then for $person in collection("ACORD_PERSON")/ACORD_PERSON[FirstName eq $propertyValue] for $party in collection("ACORD_PARTY")/ACORD_PARTY[PartyKey eq $person/PartyKey] return example:create-person($party, $person, $inquiryLevel) else fn:error((), "unsupported property name") case $party as element()* return (: filter results :) if ( $propertyName eq "LastName" ) then $party[xs:string(tx:Person/tx:LastName) eq $propertyValue] else if ( $propertyName eq "FirstName" ) then $party[xs:string(tx:Person/tx:FirstName) eq $propertyValue] else fn:error((), "unsupported property name") default return example:search-person($input, $propertyName, $propertyValue, $inquiryLevel) }; (: Object search with a single tx:Criteria as input. Only a search on Person objects is supported. :) declare function example:search-object( $criteria as element(tx:Criteria), $inquiryLevel as xs:string?, $result as element()*) as element()* { let $objectType as xs:string? := xs:string($criteria/tx:ObjectType/@tc) let $propertyName as xs:string := xs:string($criteria/tx:PropertyName) let $propertyValue as xs:string := xs:string($criteria/tx:PropertyValue) return if ( $objectType eq "115" ) then example:search-person($result, $propertyName, $propertyValue, $inquiryLevel) else fn:error((), "search not supported for this object type") }; (: Depending on the dynamic type of $entry, process a single Criteria or CriteriaExpression :) declare function example:process-search-entry( $entry as element()?, $inquiryLevel as xs:string?, $result as element()* ) as element()* { typeswitch ($entry) case $criteria as element(tx:Criteria) return example:search-object($criteria, $inquiryLevel, $result) case $expr as element(tx:CriteriaExpression) return example:process-criteria-expression($expr, $inquiryLevel, $result) default return () }; (: Recusive processing of a criteria list. Only the AND and OR logical operators for expressions and only the equal comparison operator are supported. :) declare function example:process-search-list( $criteriaList as element()*, $operator as xs:integer?, $inquiryLevel as xs:string?, $result as element()* ) as element()* { if ( fn:empty($criteriaList) ) then $result else if ( $operator eq $example:LOGICAL_OPERATOR_AND ) then let $result := example:process-search-entry($criteriaList[1], $inquiryLevel, $result) return if ( fn:empty($result) ) then $result else example:process-search-list(fn:subsequence($criteriaList, 2), $operator, $inquiryLevel, $result ) else if ( $operator eq $example:LOGICAL_OPERATOR_OR ) then let $result := ($result, example:process-search-entry($criteriaList[1], $inquiryLevel, ())) return example:process-search-list(fn:subsequence($criteriaList, 2), $operator, $inquiryLevel, $result ) else if ( fn:empty($operator) ) then example:process-search-entry($criteriaList[1], $inquiryLevel, ()) else fn:error((), "logical operator not supported") }; (: Generic object search using a TXLife CriteriaExpression as input :) declare function example:process-criteria-expression( $expr as element(tx:CriteriaExpression), $inquiryLevel as xs:string?, $result as element()* ) as element()* { let $operator as xs:integer? := xs:integer(xs:string($expr/tx:CriteriaOperator/@tc)) let $list as element()* := $expr/element()[fn:node-name(.) ne xs:QName("tx:CriteriaOperator")] return example:process-search-list($list, $operator, $inquiryLevel, ()) }; (: Validate "Address Change" request :) declare function example:validate-181($request as element(tx:TXLifeRequest)) as element(tx:TXLifeResponse)? { let $party := $request/tx:OLifE/tx:Party[@id eq $request/@PrimaryObjectID] let $key := xs:string($party/tx:PartyKey) let $person := collection("ACORD_PERSON")/ACORD_PERSON[PartyKey eq $key] let $oldAddress := $party/tx:Address[@DataRep eq "Removed"] let $newAddress := $party/tx:Address[@DataRep eq "Full"] let $row := collection("ACORD_ADDRESS")/ACORD_ADDRESS[OwnerKey eq $key] [AddressTypeCode eq xs:integer($oldAddress/tx:AddressTypeCode/@tc)] [Line1 eq $oldAddress/tx:Line1] [City eq $oldAddress/tx:City] (: AddressState is nullable :) [AddressState eq $oldAddress/tx:AddressState or fn:empty(AddressState) and fn:empty($oldAddress/tx:AddressState)] [Zip eq $oldAddress/tx:Zip] [AddressCountry eq $oldAddress/tx:AddressCountry] let $rowValid := fn:count($row) eq 1 let $zipState := if ( $newAddress/tx:AddressCountry eq "USA" ) then example:getZipInfo($newAddress/tx:Zip) else () (: Business validation rules: - the person must exist - the old address must exist - the new ZIP code must be valid - the new ZIP code must correspond with the new state :) let $errorDescription := if ( fn:count($person) ne 1 ) then "invalid person key" else if ( fn:not($rowValid) ) then "invalid value for old address" else if ( fn:not(fn:empty($zipState/@description)) ) then $zipState/@description else if ( $newAddress/tx:AddressCountry eq "USA" and $newAddress/tx:AddressState ne $zipState/*:item/@*:state ) then "invalid state for new zip code" else (: ok :) () return (: if validation succeeds, return empty sequence, if not create a failure response message :) if ( fn:empty($errorDescription) ) then () else example:create-response( $request, ( "Failure" {$errorDescription} ) ) }; (: process update of transaction 181 (change address) :) declare updating function example:update-181($request as element(tx:TXLifeRequest)) { let $party := $request/tx:OLifE/tx:Party[@id eq $request/@PrimaryObjectID] let $key := xs:string($party/tx:PartyKey) let $oldAddress := $party/tx:Address[@DataRep eq "Removed"] let $newAddress := $party/tx:Address[@DataRep eq "Full"] let $row := collection("ACORD_ADDRESS")/ACORD_ADDRESS[OwnerKey eq $key] [AddressTypeCode eq xs:integer($oldAddress/tx:AddressTypeCode/@tc)] [Line1 eq $oldAddress/tx:Line1] [City eq $oldAddress/tx:City] (: nullable :) [AddressState eq $oldAddress/tx:AddressState or fn:empty(AddressState) and fn:empty($oldAddress/tx:AddressState)] [Zip eq $oldAddress/tx:Zip] [AddressCountry eq $oldAddress/tx:AddressCountry] return ddtek:sql-update( $row, "AddressTypeCode", xs:integer($newAddress/tx:AddressTypeCode/@tc), "Line1", $newAddress/tx:Line1, "AddressState", $newAddress/tx:AddressState, "City", $newAddress/tx:City, "Zip", $newAddress/tx:Zip, "AddressCountry", $newAddress/tx:AddressCountry) }; (: Deletes a Party (Person) and all its related objects from the database. :) declare updating function example:delete-186($request as element(tx:TXLifeRequest)) { if ( fn:not(fn:empty($request/tx:OLifE/tx:Party/tx:Organization)) ) (: update of organization not supported in example :) then fn:error((), "update organization not supported") else ( (: look up existing Party based on GovtID :) for $partyKey in collection("ACORD_PARTY")/ACORD_PARTY[GovtID eq $request/tx:OLifE/tx:Party/tx:GovtID]/PartyKey return ( (: Example delete of Party with 1 - 1 relation {Party -> Person}:) ddtek:sql-delete( collection("ACORD_PARTY")/ACORD_PARTY[PartyKey eq $partyKey] ), ddtek:sql-delete( collection("ACORD_PERSON")/ACORD_PERSON[PartyKey eq $partyKey] ), (: Example deleting an 1 - n relation {Party -> Address} :) ddtek:sql-delete( collection("ACORD_ADDRESS")/ACORD_ADDRESS[OwnerKey eq $partyKey] ), (: Example deleting an n - n relation {Party <-> Phone} :) for $relation in collection("ACORD_RELATION")/ACORD_RELATION[OriginatingObjectKey eq $partyKey] [OriginatingObjectType eq $example:OLI_PARTY] [RelatedObjectType eq $example:OLI_PHONE] return ( ddtek:sql-delete($relation), ddtek:sql-delete(collection("ACORD_PHONE")/ACORD_PHONE[PhoneKey eq $relation/RelatedObjectKey]) ) ) ) }; (: Adds a Party (Person) and its related objects to the database. :) declare updating function example:insert-186($request as element(tx:TXLifeRequest)) { let $party := $request/tx:OLifE/tx:Party let $partyKey := example:createGUID($party) let $person := $party/tx:Person let $addresses := $party/tx:Address return ( ddtek:sql-insert( "ACORD_PARTY", "PartyKey", $partyKey, "PartyTypeCode", $example:OLI_PT_PERSON, "FullName", $party/tx:FullName, "GovtID", $party/tx:GovtID), ddtek:sql-insert( "ACORD_PERSON", "PartyKey", $partyKey, "FirstName", $person/tx:FirstName, "LastName", $person/tx:LastName, "MarStat", $person/tx:MarStat/@tc, "Gender", $person/tx:Gender/@tc, "BirthDate", $person/tx:BirthDate), (: Example of adding a 1 - n relation {Person -> Address} :) for $a in $addresses return ddtek:sql-insert( "ACORD_ADDRESS", "OwnerKey", $partyKey, "OwnerTypeCode", $example:OLI_PARTY, "AddressTypeCode", xs:integer($a/tx:AddressTypeCode/@tc), "Line1", $a/tx:Line1, "City", $a/tx:City, "AddressState", $a/tx:AddressState, "Zip", $a/tx:Zip, "AddressCountry", $a/tx:AddressCountry), (: Example of adding an n - n relation {Party <-> Phone} :) for $p in $party/tx:Phone let $phoneKey := example:createGUID($p) return ( ddtek:sql-insert( "ACORD_RELATION", "OriginatingObjectKey", $partyKey, "OriginatingObjectType", $example:OLI_PARTY, "RelatedObjectKey", $phoneKey, "RelatedObjectType", $example:OLI_PHONE), ddtek:sql-insert( "ACORD_PHONE", "PhoneKey", $phoneKey, "PhoneTypeCode", xs:integer($p/tx:PhoneTypeCode/@tc), "CountryCode", $p/tx:CountryCode, "AreaCode", $p/tx:AreaCode, "DialNumber", $p/tx:DialNumber) ) ) }; (: Process transaction 201. :) declare function example:reply-201($request as element(tx:TXLifeRequest)) as element(tx:TXLifeResponse)? { (: can request contain multiple inquiries? :) let $results := for $policyProduct at $pos in $request/tx:OLifE/tx:PolicyProduct let $carrierCode := xs:string($policyProduct/tx:CarrierCode) let $productCode := xs:string($policyProduct/tx:ProductCode) for $policy as element()? in collection("ACORD_POLICY")/ACORD_POLICY[CarrierCode eq $carrierCode][ProductCode eq $productCode] return { attribute id {fn:concat("PolicyProduct_", xs:string($pos))}, $carrierCode, {xs:string($policy/PlanName)}, $productCode, {xs:string($policy/MarketingName)} } return example:create-response( $request, ( Success , {$results} ) ) }; (: Process transaction 204 (Party Inquiry). Only an inquiry for Person objects is implemented. The Person is looked up based on the GovtID of the Party. The reply contains the Person object and its related objects (Address, Phone) in case the inquiry level is set to OLI_INQUIRY_OBJREL. :) declare function example:reply-204($request as element(tx:TXLifeRequest)) as element(tx:TXLifeResponse)? { let $results := for $p in $request/tx:OLifE/tx:Party let $govtID := $p/tx:GovtID (: Party inquiry on GovtID :) for $party in collection("ACORD_PARTY")/ACORD_PARTY[GovtID eq $govtID] let $partyKey := $party/PartyKey (: Example of 1-1 relation. ACORD_PERSON.PartyKey must match with ACORD_PARTY.PartyKey :) for $person in collection("ACORD_PERSON")/ACORD_PERSON[PartyKey eq $partyKey] return example:create-person($party, $person, $request/tx:InquiryLevel/@tc) return example:create-response( $request, ( Success , {$results} ) ) }; (: Process transaction 301 "Party Search". :) declare function example:reply-301( $request as element(tx:TXLifeRequest) ) as element(tx:TXLifeResponse)? { let $inquiryLevel := $request/tx:InquiryLevel/@tc (: The request contains either one CriteriaExpression OR one Criteria node. :) let $results := ( example:process-search-entry($request/tx:CriteriaExpression, $inquiryLevel, ()), example:process-search-entry($request/tx:Criteria, $inquiryLevel, ()) ) let $nrRecords := fn:count($results) return example:create-response( $request, ( 0, Success {$nrRecords} , {$results} ) ) }; (: creates a tx:Party of type person from an x12 request :) declare function example:x12-to-party( $request as document-node(), $partyId as xs:string) as element(tx:Party) { { attribute id { $partyId }, Person, { concat( xs:string($request/X12/TS_100/GROUP_1/NM1/NM103-NameLastOrOrganizationName), ", ", xs:string($request/X12/TS_100/GROUP_1/NM1/NM104-NameFirst) ) } , 14266, {xs:string($request/X12/TS_100/GROUP_1/NM1/NM104-NameFirst)} {xs:string($request/X12/TS_100/GROUP_1/NM1/NM103-NameLastOrOrganizationName)} 1 2 1980-01-01 , { , {xs:string($request/X12/TS_100/GROUP_1/N3/N301-AddressInformation)}, {xs:string($request/X12/TS_100/GROUP_1/N3/N302-AddressInformation)}, {xs:string($request/X12/TS_100/GROUP_1/N4/N401-CityName)}, {xs:string($request/X12/TS_100/GROUP_1/N4/N402-StateOrProvinceCode)}, {xs:string($request/X12/TS_100/GROUP_1/N4/N403-PostalCode)}, {xs:string($request/X12/TS_100/GROUP_1/N4/N404-CountryCode)} } , SCR 14266 SCR 14266 SCR 14266 } }; (: Stylus Studio meta-information - (c) 2004-2007. Progress Software Corporation. All rights reserved. :)