but somehow i didn't got proper information about it.So i want to take this opportunity to share my work around to implement Auto increment xquery.
Goal:
1.Auto Increment xquery udpate
2.how to put header and detail entries with distinct rows in same table.
3.how to use predicate
Auto Increment xquery udpate:
Sample xml file format:
<PO_Receipt_Confirmations>
<Receipt_Confirmation>
<Confirmation_Header>
<warehouse>OML</warehouse>
<customer_code>XP</customer_code>
<Logitech_PO_number>71201516</Logitech_PO_number>
<receipt_number>12341</receipt_number>
<doc_type>RD</doc_type>
</Confirmation_Header>
<Confirmation_Detail>
<Logitech_PO_line_number>6</Logitech_PO_line_number>
<product_code>920-004967</product_code>
<product_description>GAMING</product_description>
<actual_receipt_qt>3</actual_receipt_qt>
<unit_of_measure>EA</unit_of_measure>
<subinventory>UZN</subinventory>
<waybill_number/>
</Confirmation_Detail>
<Confirmation_Detail>
<Logitech_PO_line_number>6</Logitech_PO_line_number>
<product_code>920-004967</product_code>
<product_description>GAMING</product_description>
<actual_receipt_qt>12</actual_receipt_qt>
<unit_of_measure>EA</unit_of_measure>
<subinventory>UZN</subinventory>
<waybill_number/>
</Confirmation_Detail>
<Confirmation_Detail>
<Logitech_PO_line_number>9</Logitech_PO_line_number>
<product_code>981-000536</product_code>
<product_description>G430</product_description>
<actual_receipt_qt>7</actual_receipt_qt>
<unit_of_measure>XP</unit_of_measure>
<subinventory>UZN</subinventory>
<waybill_number/>
</Confirmation_Detail>
</Receipt_Confirmation>
<Receipt_Confirmation>
<Confirmation_Header>
<warehouse>OML</warehouse>
<customer_code>NZ</customer_code>
<Logitech_PO_number>71201516</Logitech_PO_number>
<receipt_number>1000536687</receipt_number>
</Confirmation_Header>
<Confirmation_Detail>
<Logitech_PO_line_number>6</Logitech_PO_line_number>
<product_code>45234</product_code>
<product_description>G510S</product_description>
<actual_receipt_qt>3</actual_receipt_qt>
<unit_of_measure>EA</unit_of_measure>
<subinventory>UZN</subinventory>
<waybill_number/>
</Confirmation_Detail>
<Confirmation_Detail>
<Logitech_PO_line_number>6</Logitech_PO_line_number>
<product_code>45234</product_code>
<product_description>G510S</product_description>
<actual_receipt_qt>3</actual_receipt_qt>
<unit_of_measure>EA</unit_of_measure>
<subinventory>UZN</subinventory>
<waybill_number/>
</Confirmation_Detail>
<Confirmation_Detail>
<Logitech_PO_line_number>9</Logitech_PO_line_number>
<product_code>981-000536</product_code>
<product_description>G430</product_description>
<actual_receipt_qt>7</actual_receipt_qt>
<unit_of_measure>EA</unit_of_measure>
<subinventory>UZN</subinventory>
<waybill_number/>
</Confirmation_Detail>
</Receipt_Confirmation>
</PO_Receipt_Confirmations>
In this above xml file we have two receipt_Confirmation with one header and multiple details distinctly.When inserting into table through xquery mapping,We have to capture position of the first receipt header and details position and second receipt as well.
for ex:
We have two receipts like below
source xml:
<PO>
<Receipt> position:
<header></header> 1
<detail></detail> 2
<detail></detail> 3
</Receipt>
<Receipt>
<header></header> 4
<detail></detail> 5
<detail></detail> 6
</Receipt>
<PO>
Requirement need to be achieved:
<addPoReceiptConfirmation>
<poReceiptConfirmationRequest>
<recordHeaderId>1</recordHeaderId>
<recordType>H</recordType>
<seqNum>1</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>2</recordHeaderId>
<recordType>L</recordType>
<seqNum>2</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>3</recordHeaderId>
<recordType>L</recordType>
<seqNum>3</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>4</recordHeaderId>
<recordType>H</recordType>
<seqNum>4</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>5</recordHeaderId>
<recordType>L</recordType>
<seqNum>5</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>6/recordHeaderId>
<recordType>L</recordType>
<seqNum>6</seqNum>
</poReceiptConfirmationRequest>
</addPoReceiptConfirmation>
Below xquery will help for achieving the auto increment number:<poReceiptConfirmationRequest>
<recordHeaderId>1</recordHeaderId>
<recordType>H</recordType>
<seqNum>1</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>2</recordHeaderId>
<recordType>L</recordType>
<seqNum>2</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>3</recordHeaderId>
<recordType>L</recordType>
<seqNum>3</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>4</recordHeaderId>
<recordType>H</recordType>
<seqNum>4</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>5</recordHeaderId>
<recordType>L</recordType>
<seqNum>5</seqNum>
</poReceiptConfirmationRequest>
<poReceiptConfirmationRequest>
<recordHeaderId>6/recordHeaderId>
<recordType>L</recordType>
<seqNum>6</seqNum>
</poReceiptConfirmationRequest>
</addPoReceiptConfirmation>
declare function xf:POReceiptConfim_3PL_Inflow($anyXML as element(*))
as element(ns0:addPoReceiptConfirmation) {
return
<ns0:addPoReceiptConfirmation>
<poReceiptConfirmationRequest>
{
for $Receipt_Confirmation at $RcptPOS in
$anyXML/Receipt_Confirmation,
$Confirmation_Header at $HdrPos in $Receipt_Confirmation/Confirmation_Header
let $HdrPOSIT:=xs:int($RcptPOS)-1
let $hdrSum:=
count($anyXML/Receipt_Confirmation[position()>=1 and position()<=$HdrPOSIT]/Confirmation_Detail)
let $diff := xs:int($hdrSum) - xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
let $SUM := $hdrSum - $diff + xs:int($RcptPOS)
return
(
let $collection:=(
<Collection> <poReceiptConfirmations> {if($RcptPOS>1) then <recordHeaderId>{($SUM)}</recordHeaderId> else <recordHeaderId>{$HdrPos}</recordHeaderId> } <recordType>H</recordType> {if($RcptPOS>1) then <seqNum>{($SUM)}</seqNum> else <seqNum>{$HdrPos}</seqNum> } </poReceiptConfirmations> { let $ProductCode:=distinct-values($Receipt_Confirmation/Confirmation_Detail/product_code) for $product_code at $linePOS in $ProductCode let $actual_receipt_qt_count:=fn:sum($Receipt_Confirmation/Confirmation_Detail[product_code=$product_code]/actual_receipt_qt)[1] let $unit_of_measure:=($Receipt_Confirmation/Confirmation_Detail[product_code=$product_code]/unit_of_measure)[1] let $Logitech_PO_line_number:=($Receipt_Confirmation/Confirmation_Detail[product_code=$product_code]/Logitech_PO_line_number)[1] return <poReceiptConfirmations> {if(data($Confirmation_Header/doc_type)='PE') then () else (<itemNum>{data($product_code)}</itemNum> ) } <quantity>{xs:integer($actual_receipt_qt_count)}</quantity> {if($RcptPOS>1) then <recordHeaderId>{$SUM+$linePOS}</recordHeaderId> else <recordHeaderId>{$HdrPos+$linePOS}</recordHeaderId> } <recordType>L</recordType> {if($RcptPOS>1) then <seqNum>{$SUM+$linePOS}</seqNum> else <seqNum>{$HdrPos+$linePOS}</seqNum> } </poReceiptConfirmations> } </Collection>
)
return
$collection/*
)
}
</poReceiptConfirmationRequest>
</ns0:addPoReceiptConfirmation>
};
let me explain you about by taking small piece of above xquery:
for $Receipt_Confirmation at $RcptPOS in
$anyXML/Receipt_Confirmation,
$Confirmation_Header at $HdrPos in $Receipt_Confirmation/Confirmation_Header
let $HdrPOSIT:=xs:int($RcptPOS)-1
let $hdrSum:=
count($anyXML/Receipt_Confirmation[position()>=1 and position()<=$HdrPOSIT]/Confirmation_Detail)
let $diff := xs:int($hdrSum) - xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
let $SUM := $hdrSum - $diff + xs:int($RcptPOS)
As most of you know about 'at' keyword why we have used in this xquery to check the position of the loop like above we have used it.In xquery we can get the position upto in the loop boundary
<PO>
<Receipt>
<header></header>
<detail></detail>
<detail></detail>
</Receipt>
<Receipt>
<header></header>
<detail></detail>
<detail></detail>
</Receipt>
<PO>
This xml file is having two receipts,if you loop on receipt(<Receipt>) element.We can get only first recipt position like 1,2,3 for the second receipts we will get again 1,2,3.Since we are looping on Receipt we don't have a control to get the last position of the previous receipt.So to achive this logic,if you could know the above receipt last detail position then our task will be completed.In this case
(position()) function,We can use to get the previous receipt last detail position.
RcptPOS is telling about loop position of the Receipt_Confirmation,HdrPOSIT is helpful for calculating the previous receipt,$hdrSum is helpful for calculating count of the previous loop detail count.
explanation:
xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
above statement will helpful to check the previous distinct product-code detail position count by summation till HdrPOSIT satisfies.
for $Receipt_Confirmation at $RcptPOS in
$anyXML/Receipt_Confirmation,
$Confirmation_Header at $HdrPos in $Receipt_Confirmation/Confirmation_Header
let $HdrPOSIT:=xs:int($RcptPOS)-1
let $hdrSum:=
count($anyXML/Receipt_Confirmation[position()>=1 and position()<=$HdrPOSIT]/Confirmation_Detail)
let $diff := xs:int($hdrSum) - xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
let $SUM := $hdrSum - $diff + xs:int($RcptPOS)
As most of you know about 'at' keyword why we have used in this xquery to check the position of the loop like above we have used it.In xquery we can get the position upto in the loop boundary
<PO>
<Receipt>
<header></header>
<detail></detail>
<detail></detail>
</Receipt>
<Receipt>
<header></header>
<detail></detail>
<detail></detail>
</Receipt>
<PO>
This xml file is having two receipts,if you loop on receipt(<Receipt>) element.We can get only first recipt position like 1,2,3 for the second receipts we will get again 1,2,3.Since we are looping on Receipt we don't have a control to get the last position of the previous receipt.So to achive this logic,if you could know the above receipt last detail position then our task will be completed.In this case
(position()) function,We can use to get the previous receipt last detail position.
RcptPOS is telling about loop position of the Receipt_Confirmation,HdrPOSIT is helpful for calculating the previous receipt,$hdrSum is helpful for calculating count of the previous loop detail count.
explanation:
xs:int(sum(for $item in 1 to $HdrPOSIT return count(distinct-values($anyXML/Receipt_Confirmation[$item]/Confirmation_Detail/product_code))))
above statement will helpful to check the previous distinct product-code detail position count by summation till HdrPOSIT satisfies.
No comments:
Post a Comment