Hello,
Every PO line needs it's own <property name="ln.ItemGroup"> element in UserArea:
po line 10 has R350, getItemGroupByItem('KR350', ..., ...)
po line 20 has R100, getItemGroupByItem('KR100', ..., ...)
I've got the correct results but not has expected ... I have twice for each po line while each one should have it's own as above ... where is it wrong? i have added the clause "where i$/DataArea ..." but maybe I do not understand how that works.
<DataArea>
<PurchaseOrder>
<PurchaseOrderHeader>
...
</PurchaseOrderHeader>
<PurchaseOrderLine>
<LineNumber>10</LineNumber>
<Item>
<ItemID>
<ID>KR350</ID>
</ItemID>
</Item>
<UserArea>
...
</UserArea>
</PurchaseOrderLine>
<PurchaseOrderLine>
<LineNumber>20</LineNumber>
<Item>
<ItemID>
<ID>KR100</ID>
</ItemID>
</Item>
<UserArea>
...
</UserArea>
</PurchaseOrderLine>
</PurchaseOrder>
</DataArea>
FOR r IN (
SELECT ExtractValue(Value(p),'/PurchaseOrderLine/Item/ItemID/ID/text()') as itemId,
ExtractValue(Value(p),'/PurchaseOrderLine/LineNumber/text()') as lineNumber
FROM TABLE(XMLSequence(Extract(xmlContent,'DataArea/PurchaseOrder/PurchaseOrderLine'))) p
) LOOP
BEGIN
ItemGroup := getItemGroupByItem(trim(r.itemId), AccountingEntityID, LogicalID);
xml_string := '<Property><NameValue name="ln.ItemGroup" type="StringType">'||ItemGroup||'</NameValue></Property>';
UPDATE LN\_BOD\_DECORATION
SET BOD\_SPEC =
XMLQuery('copy $i := $p1 modify
(for $j in $i/DataArea/PurchaseOrder/PurchaseOrderLine/UserArea where $i/DataArea/PurchaseOrder/PurchaseOrderLine/LineNumber =' || lineNumber
return (# ora:child-element-name Property #)
{insert node $p2 into $j})
return $i'
PASSING BOD\_SPEC AS "p1",
XMLType(xml\_string) AS "p2"
RETURNING CONTENT)
WHERE BOD\_NAME = 'SyncPurchaseOrder';
COMMIT;
END;
END LOOP;
Thanks.
BR.
Marco.