I'm trying to create a Fusion BI publisher report. That will show the parent child relationship between a product and service line. I'm unable to find how to link the correct service line with the parent line. I need help finding the parent child relationship.
Here is my query.
SELECT DISTINCT OOH.ORDER_NUMBER,
OOH.HEADER_ID,
OOL.LINE_ID,
OOL.PARENT_LINE_ID,
OOL.LINE_NUMBER,
OOL.DISPLAY_LINE_NUMBER,
ESIB.ITEM_NUMBER,
GRPS.TRANSACTION_ENTITY_ID,
GRPS.RELATED_TRANSACTION_ENTITY_ID,
GRPS.FULFILLMENT_LINE_ID,
OOL.SOURCE_LINE_NUMBER
FROM DOO_HEADERS_ALL OOH,
DOO_LINES_ALL OOL,
DOO_FULFILL_LINES_ALL FLINE,
EGP_SYSTEM_ITEMS_B ESIB,
DOO_ORCHESTRATION_GROUPS GRPS
WHERE 1 = 1
AND OOH.HEADER_ID = OOL.HEADER_ID
AND OOL.INVENTORY_ITEM_ID = ESIB.INVENTORY_ITEM_ID
AND OOL.INVENTORY_ORGANIZATION_ID = ESIB.INVENTORY_ORGANIZATION_ID
AND OOL.LINE_ID = FLINE.LINE_ID
AND ESIB.INVENTORY_ITEM_ID = FLINE.INVENTORY_ITEM_ID
AND ESIB.INVENTORY_ORGANIZATION_ID = FLINE.INVENTORY_ORGANIZATION_ID
AND GRPS.HEADER_ID = OOH.HEADER_ID
AND GRPS.LINE_ID = OOL.LINE_ID
AND GRPS.FULFILLMENT_LINE_ID = FLINE.FULFILL_LINE_ID
AND GRPS.ORG_ID = FLINE.ORG_ID
AND OOH.ORDER_NUMBER = 1000011854;
Can someone let me know. How what is the correct join to get the service line to associate to the correct parent line as in the screenshot below?
