How to Repeat Row by Specific Value in Data Field [message #683563] |
Sun, 31 January 2021 20:44 |
|
ags.island
Messages: 3 Registered: January 2021
|
Junior Member |
|
|
Master,
Can You help me for the query to repeat row by specific value in data field
Query:
SELECT x.order_number,
x.ordered_item,
x.description,
x.ordered_quantity,
x.order_quantity_uom,
x.pallet_used
FROM ( SELECT ooha.order_number,
oola.ordered_item,
msib.description,
SUM (oola.ordered_quantity) ordered_quantity,
oola.order_quantity_uom,
msib.global_attribute20 AS pallet_size,
CASE
WHEN SUM (oola.ordered_quantity) / msib.global_attribute20 <
1
THEN
1
ELSE
FLOOR (
SUM (oola.ordered_quantity) / msib.global_attribute20)
END
pallet_used
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
mtl_system_items_b msib
WHERE ooha.header_id = oola.header_id
AND oola.ordered_item = msib.segment1
AND oola.ship_from_org_id = msib.organization_id
AND ooha.order_number = '112100120' --p_order_number
GROUP BY ooha.order_number,
oola.ordered_item,
msib.description,
msib.global_attribute20,
oola.order_quantity_uom) x
Output :
ORDER_NUMBER ORDERED_ITEM DESCRIPTION ORDERED_QUANTITY ORDER_QUANTITY_UOM PALLET_USED
112100120 M4010014 MBS COCOPANDAN 460 ML X 12 2150 CTN 5
112100120 M4010032 MBS MELON 460 ML X 12 2400 CTN 3
Expected Result :
ORDER_NUMBER ORDERED_ITEM DESCRIPTION ORDERED_QUANTITY ORDER_QUANTITY_UOM PALLET_USED
112100120 M4010014 MBS COCOPANDAN 460 ML X 12 2150 CTN 5
112100120 M4010014 MBS COCOPANDAN 460 ML X 12 2150 CTN 5
112100120 M4010014 MBS COCOPANDAN 460 ML X 12 2150 CTN 5
112100120 M4010014 MBS COCOPANDAN 460 ML X 12 2150 CTN 5
112100120 M4010014 MBS COCOPANDAN 460 ML X 12 2150 CTN 5
112100120 M4010032 MBS MELON 460 ML X 12 2400 CTN 3
112100120 M4010032 MBS MELON 460 ML X 12 2400 CTN 3
112100120 M4010032 MBS MELON 460 ML X 12 2400 CTN 3
[Edit MC: add code tags]
[Updated on: Mon, 01 February 2021 00:45] by Moderator Report message to a moderator
|
|
|
|
|
|
|