CST_COGS_DETAILS

Details

Schema: FUSION
Object owner: CST
Object type: VIEW

Columns

Name
COST_ORG_ID COST_BOOK_ID LEDGER_ID LEGAL_ENTITY_ID PROFIT_CENTER_BU_ID INVENTORY_ORG_ID ITEM_ORGANIZATION_ID SUCCESSOR_INV_ORG_ID SUCCESSOR_BU_ID DOO_ORDER_NUMBER DOO_ORDER_TYPE DOO_FULLFILL_LINE_ID SOURCE_DOC_NO SOURCE_DOC_TYPE CUSTOMER_ID SHIP_TO_CUSTOMER_ID FLOW_INSTANCE_ID AGREEMENT_NUMBER COGS_TYPE BASE_TXN_TYPE_ID BASE_TXN_SOURCE_TYPE_ID BASE_TXN_ACTION_ID GL_DATE INVENTORY_ITEM_ID CONFIG_INVENTORY_ITEM_ID ROOT_INVENTORY_ITEM_ID COST_ELEMENT_ID COST_ELEMENT_DESC COST_ELEMENT_TYPE COGS_AMOUNT DCOGS_AMOUNT BASE_CURRENCY_CODE LAYER_QUANTITY COST_TRANSACTION_UOM ACCOUNTED_FLAG COGS_ACCOUNT_ID

Name

COST_ORG_ID

COST_BOOK_ID

LEDGER_ID

LEGAL_ENTITY_ID

PROFIT_CENTER_BU_ID

INVENTORY_ORG_ID

ITEM_ORGANIZATION_ID

SUCCESSOR_INV_ORG_ID

SUCCESSOR_BU_ID

DOO_ORDER_NUMBER

DOO_ORDER_TYPE

DOO_FULLFILL_LINE_ID

SOURCE_DOC_NO

SOURCE_DOC_TYPE

CUSTOMER_ID

SHIP_TO_CUSTOMER_ID

FLOW_INSTANCE_ID

AGREEMENT_NUMBER

COGS_TYPE

BASE_TXN_TYPE_ID

BASE_TXN_SOURCE_TYPE_ID

BASE_TXN_ACTION_ID

GL_DATE

INVENTORY_ITEM_ID

CONFIG_INVENTORY_ITEM_ID

ROOT_INVENTORY_ITEM_ID

COST_ELEMENT_ID

COST_ELEMENT_DESC

COST_ELEMENT_TYPE

COGS_AMOUNT

DCOGS_AMOUNT

BASE_CURRENCY_CODE

LAYER_QUANTITY

COST_TRANSACTION_UOM

ACCOUNTED_FLAG

COGS_ACCOUNT_ID

Query

SQL_Statement
SELECT ccd.cost_organization_id cost_org_id, ccob.cost_book_id, ccd.ledger_id, ccd.legal_entity_id, NVL(iop.profit_center_bu_id, iop.business_unit_id) profit_center_bu_id, ct.inventory_org_id, DECODE (CT.INTRANSIT_FLAG, 'Y', IOP.MASTER_ORGANIZATION_ID, CT.INVENTORY_ORG_ID ) ITEM_ORGANIZATION_ID, cte.successor_inv_org_id, CASE WHEN cit.transaction_flow_type IN (4,5,7,8,11,14,15) THEN -1 ELSE cte.successor_bu_id END AS successor_bu_id, CASE WHEN ct.txn_source_doc_type IN ('OO', 'SO') THEN ct.txn_source_doc_number ELSE ct.txn_source_ref_doc_number END doo_order_number, CASE WHEN ct.txn_source_doc_type IN ('OO', 'SO') THEN ct.txn_source_doc_type ELSE ct.txn_source_ref_doc_type END doo_order_type, CASE WHEN (dfl.item_sub_type_code = 'ATO' OR dfl.item_sub_type_code = 'PTO-SMC' OR dfl.item_sub_type_code = 'PTO-NONSMC' OR dfl.item_sub_type_code = 'KIT-SMC' OR dfl.item_sub_type_code = 'KIT-NONSMC') THEN dfl.fulfill_line_id WHEN (p_dfl.item_sub_type_code = 'ATO' OR p_dfl.item_sub_type_code = 'PTO-SMC' OR p_dfl.item_sub_type_code = 'PTO-NONSMC' OR p_dfl.item_sub_type_code = 'KIT-SMC' OR p_dfl.item_sub_type_code = 'KIT-NONSMC') THEN p_dfl.fulfill_line_id WHEN (pp_dfl.item_sub_type_code = 'ATO' OR pp_dfl.item_sub_type_code = 'PTO-SMC' OR pp_dfl.item_sub_type_code = 'PTO-NONSMC' OR pp_dfl.item_sub_type_code = 'KIT-SMC' OR pp_dfl.item_sub_type_code = 'KIT-NONSMC') THEN pp_dfl.fulfill_line_id ELSE dfl.fulfill_line_id END doo_fullfill_line_id, ct.txn_source_doc_number source_doc_no, ct.txn_source_doc_type source_doc_type, CASE WHEN cte.external_system_reference = 'FUSION' AND cte.bill_to_customer_id > 0 AND cit.transaction_flow_type IN (1,2,3,4,5,7,8,11,14,15,12) AND (cit.base_txn_source_type_id, cit.base_txn_action_id) NOT IN ((0,19),(0,20)) THEN cte.bill_to_customer_id ELSE dfl.bill_to_customer_id END customer_id, dfl.ship_to_party_id ship_to_customer_id, CASE WHEN cit.transaction_flow_type IN (1,2,3,4,5,7,8,11,14,15,12) AND ( cit.base_txn_source_type_id, cit.base_txn_action_id ) NOT IN ((0,19),(0,20)) THEN cte.external_system_ref_id ELSE NULL END flow_instance_id, cte.agreement_number, CASE WHEN NVL(ct.intercompany_invoicing_flag, 'Y') = 'Y' THEN 'Invoice' ELSE 'Due To/From' END cogs_type, ct.base_txn_type_id, ct.base_txn_source_type_id, ct.base_txn_action_id, ccd.gl_date, CASE WHEN (ct.doo_fullfill_line_id is NULL) THEN ct.inventory_item_id WHEN (dfl.item_sub_type_code = 'ATO' OR dfl.item_sub_type_code = 'PTO-SMC' OR dfl.item_sub_type_code = 'PTO-NONSMC' OR dfl.item_sub_type_code = 'KIT-SMC' OR dfl.item_sub_type_code = 'KIT-NONSMC') THEN dfl.inventory_item_id WHEN (p_dfl.item_sub_type_code = 'ATO' OR p_dfl.item_sub_type_code = 'PTO-SMC' OR p_dfl.item_sub_type_code = 'PTO-NONSMC' OR p_dfl.item_sub_type_code = 'KIT-SMC' OR p_dfl.item_sub_type_code = 'KIT-NONSMC') THEN p_dfl.inventory_item_id WHEN (pp_dfl.item_sub_type_code = 'ATO' OR pp_dfl.item_sub_type_code = 'PTO-SMC' OR pp_dfl.item_sub_type_code = 'PTO-NONSMC' OR pp_dfl.item_sub_type_code = 'KIT-SMC' OR pp_dfl.item_sub_type_code = 'KIT-NONSMC') THEN pp_dfl.inventory_item_id ELSE dfl.inventory_item_id END inventory_item_id, CASE WHEN (dfl.item_sub_type_code = 'ATO' OR dfl.item_sub_type_code = 'PTO-SMC' OR dfl.item_sub_type_code = 'PTO-NONSMC' OR dfl.item_sub_type_code = 'KIT-SMC' OR dfl.item_sub_type_code = 'KIT-NONSMC') THEN dfl.config_inventory_item_id WHEN (p_dfl.item_sub_type_code = 'ATO' OR p_dfl.item_sub_type_code = 'PTO-SMC' OR p_dfl.item_sub_type_code = 'PTO-NONSMC' OR p_dfl.item_sub_type_code = 'KIT-SMC' OR p_dfl.item_sub_type_code = 'KIT-NONSMC') THEN p_dfl.config_inventory_item_id WHEN (pp_dfl.item_sub_type_code = 'ATO' OR pp_dfl.item_sub_type_code = 'PTO-SMC' OR pp_dfl.item_sub_type_code = 'PTO-NONSMC' OR pp_dfl.item_sub_type_code = 'KIT-SMC' OR pp_dfl.item_sub_type_code = 'KIT-NONSMC') THEN pp_dfl.config_inventory_item_id ELSE dfl.config_inventory_item_id END config_inventory_item_id, r_dfl.inventory_item_id root_inventory_item_id, ccdl.cost_element_id, NVL(cce.cost_element_desc, cce.cost_element_code) cost_element_desc, cce.cost_element_type, CASE WHEN ccdl.accounting_line_type IN ( 'COST_OF_GOODS_SOLD', 'RMA_GAIN_LOSS', 'INTER_COMPANY_COGS', 'INTER_ORGANIZATION_RECEIVABLES', 'INTER_ORGANIZATION_GAIN_LOSS', 'FREIGHT_EXPENSE') THEN NVL(ccdl.ledger_amount,0) ELSE 0 END cogs_amount, CASE WHEN ccdl.accounting_line_type IN ('DEFERRED_COST_OF_GOODS_SOLD', 'DEFERRED_RMA_GAIN_LOSS') THEN NVL(ccdl.ledger_amount,0) ELSE 0 END dcogs_amount, ccd.base_currency_code, NVL(ccd.layer_quantity,0) layer_quantity, ccd.cost_transaction_uom, ccd.accounted_flag, CASE WHEN ccdl.accounting_line_type IN ('COST_OF_GOODS_SOLD', 'RMA_GAIN_LOSS', 'INTER_COMPANY_COGS', 'INTER_ORGANIZATION_RECEIVABLES', 'INTER_ORGANIZATION_GAIN_LOSS', 'FREIGHT_EXPENSE') THEN ccdl.sla_code_combination_id ELSE NULL END cogs_account_id FROM cst_cost_distributions ccd, cst_cost_distribution_lines ccdl, cst_transactions ct, cst_inv_transactions cit, cst_trade_events cte, doo_fulfill_lines_all_v dfl, doo_fulfill_lines_all_v p_dfl, doo_fulfill_lines_all_v pp_dfl, doo_fulfill_lines_all_v r_dfl, inv_org_parameters iop, cst_cost_org_books ccob, cst_cost_elements_vl cce WHERE ccd.distribution_id = ccdl.distribution_id AND ( ( ccdl.accounting_line_type IN ('COST_OF_GOODS_SOLD', 'INTER_COMPANY_COGS', 'DEFERRED_COST_OF_GOODS_SOLD', 'DEFERRED_RMA_GAIN_LOSS', 'RMA_GAIN_LOSS', 'INTER_ORGANIZATION_RECEIVABLES', 'INTER_ORGANIZATION_GAIN_LOSS') ) OR ( ccdl.accounting_line_type = 'FREIGHT_EXPENSE' AND cit.transaction_flow_type NOT IN (4,5,7,8,11,14,15) AND ct.intransit_flag = 'N' ) ) AND ( CASE WHEN SIGN(NVL(layer_quantity,0)) = -1 THEN ccd.dep_trxn_id ELSE ccd.rec_trxn_id END ) = ct.transaction_id AND NVL(cit.transaction_flow_type, 0) NOT IN (6, 9, 13, 16, 17) AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND cit.external_system_ref_id = cte.trade_event_id (+) AND ct.inventory_org_id = iop.organization_id AND dfl.fulfill_line_id (+) = NVL(ct.doo_split_fulfill_line_id, ct.doo_fullfill_line_id) AND p_dfl.fulfill_line_id (+) = dfl.parent_fulfill_line_id AND pp_dfl.fulfill_line_id (+) = p_dfl.parent_fulfill_line_id AND r_dfl.fulfill_line_id (+) = dfl.root_parent_fulfill_line_id AND ccd.cost_organization_id = ccob.cost_org_id AND ccd.cost_book_id = ccob.cost_book_id AND ccob.primary_book_flag = 'Y' AND cce.cost_element_id = ccdl.cost_element_id UNION ALL SELECT ccd.cost_organization_id cost_org_id, ccob.cost_book_id, ccd.ledger_id, ccd.legal_entity_id, NVL(iop.profit_center_bu_id, iop.business_unit_id) profit_center_bu_id, ct.inventory_org_id, DECODE (CT.INTRANSIT_FLAG, 'Y', IOP.MASTER_ORGANIZATION_ID, CT.INVENTORY_ORG_ID ) ITEM_ORGANIZATION_ID, cte.successor_inv_org_id, cte.successor_bu_id, ct.txn_source_ref_doc_number doo_order_number, ct.txn_source_ref_doc_type doo_order_type, ct.doo_fullfill_line_id, ct.txn_source_doc_number source_doc_no, ct.txn_source_doc_type source_doc_type, CASE WHEN cte.external_system_reference = 'FUSION' AND cte.bill_to_customer_id > 0 THEN cte.bill_to_customer_id ELSE NULL END customer_id, NULL ship_to_customer_id, cte.external_system_ref_id flow_instance_id, cte.agreement_number, CASE WHEN NVL(cte.intercompany_invoicing_flag, 'Y') = 'Y' THEN 'Invoice' ELSE 'Due To/From' END cogs_type, ct.base_txn_type_id, ct.base_txn_source_type_id, ct.base_txn_action_id, ccd.gl_date, ct.inventory_item_id, NULL config_inventory_item_id, ct.inventory_item_id root_inventory_item_id, ccdl.cost_element_id, NVL(cce.cost_element_desc, cce.cost_element_code) cost_element_desc, cce.cost_element_type, CASE WHEN ccdl.accounting_line_type = 'FREIGHT_EXPENSE' THEN NVL(ccdl.ledger_amount,0) ELSE 0 END cogs_amount, CAST(0 AS NUMBER) AS dcogs_amount, ccd.base_currency_code, NVL(ccd.layer_quantity,0) layer_quantity, ccd.cost_transaction_uom, ccd.accounted_flag, CASE WHEN ccdl.accounting_line_type = 'FREIGHT_EXPENSE' THEN ccdl.sla_code_combination_id ELSE NULL END cogs_account_id FROM cst_cost_distributions ccd, cst_cost_distribution_lines ccdl, cst_transactions ct, cst_inv_transactions cit, cst_trade_events cte, inv_org_parameters iop, cst_cost_org_books ccob, cst_cost_elements_vl cce WHERE ccd.distribution_id = ccdl.distribution_id AND ccdl.accounting_line_type IN ('FREIGHT_EXPENSE') AND cit.transaction_flow_type IN (4,5,7,8,11,14,15,12) AND ct.intransit_flag = 'N' AND ( CASE WHEN SIGN(NVL(layer_quantity,0)) = -1 THEN ccd.dep_trxn_id ELSE ccd.rec_trxn_id END ) = ct.transaction_id AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id AND cit.external_system_ref_id = cte.inv_transaction_id AND cte.trade_event_id = (SELECT MIN(min_cte.trade_event_id) FROM cst_trade_events min_cte WHERE min_cte.inv_transaction_id = cte.inv_transaction_id AND min_cte.transaction_type = 'INTRANSIT_ISSUE' AND min_cte.prior_inventory_org_id IS NULL ) AND cit.inventory_org_id = cte.inventory_org_id AND ct.inventory_org_id = iop.organization_id AND ccd.cost_organization_id = ccob.cost_org_id AND ccd.cost_book_id = ccob.cost_book_id AND ccob.primary_book_flag = 'Y' AND cce.cost_element_id = ccdl.cost_element_id

SQL_Statement

SELECT ccd.cost_organization_id cost_org_id,

ccob.cost_book_id,

ccd.ledger_id,

ccd.legal_entity_id,

NVL(iop.profit_center_bu_id, iop.business_unit_id) profit_center_bu_id,

ct.inventory_org_id,

DECODE (CT.INTRANSIT_FLAG, 'Y', IOP.MASTER_ORGANIZATION_ID, CT.INVENTORY_ORG_ID ) ITEM_ORGANIZATION_ID,

cte.successor_inv_org_id,

CASE

WHEN cit.transaction_flow_type IN (4,5,7,8,11,14,15)

THEN -1

ELSE cte.successor_bu_id

END AS successor_bu_id,

CASE

WHEN ct.txn_source_doc_type IN ('OO', 'SO')

THEN ct.txn_source_doc_number

ELSE ct.txn_source_ref_doc_number

END doo_order_number,

CASE

WHEN ct.txn_source_doc_type IN ('OO', 'SO')

THEN ct.txn_source_doc_type

ELSE ct.txn_source_ref_doc_type

END doo_order_type,

CASE

WHEN (dfl.item_sub_type_code = 'ATO' OR dfl.item_sub_type_code = 'PTO-SMC' OR dfl.item_sub_type_code = 'PTO-NONSMC' OR dfl.item_sub_type_code = 'KIT-SMC' OR dfl.item_sub_type_code = 'KIT-NONSMC') THEN dfl.fulfill_line_id

WHEN (p_dfl.item_sub_type_code = 'ATO' OR p_dfl.item_sub_type_code = 'PTO-SMC' OR p_dfl.item_sub_type_code = 'PTO-NONSMC' OR p_dfl.item_sub_type_code = 'KIT-SMC' OR p_dfl.item_sub_type_code = 'KIT-NONSMC') THEN p_dfl.fulfill_line_id

WHEN (pp_dfl.item_sub_type_code = 'ATO' OR pp_dfl.item_sub_type_code = 'PTO-SMC' OR pp_dfl.item_sub_type_code = 'PTO-NONSMC' OR pp_dfl.item_sub_type_code = 'KIT-SMC' OR pp_dfl.item_sub_type_code = 'KIT-NONSMC') THEN pp_dfl.fulfill_line_id

ELSE dfl.fulfill_line_id

END doo_fullfill_line_id,

ct.txn_source_doc_number source_doc_no,

ct.txn_source_doc_type source_doc_type,

CASE WHEN cte.external_system_reference = 'FUSION'

AND cte.bill_to_customer_id > 0

AND cit.transaction_flow_type IN (1,2,3,4,5,7,8,11,14,15,12)

AND (cit.base_txn_source_type_id, cit.base_txn_action_id) NOT IN ((0,19),(0,20))

THEN cte.bill_to_customer_id

ELSE dfl.bill_to_customer_id

END customer_id,

dfl.ship_to_party_id ship_to_customer_id,

CASE

WHEN cit.transaction_flow_type IN (1,2,3,4,5,7,8,11,14,15,12)

AND ( cit.base_txn_source_type_id, cit.base_txn_action_id ) NOT IN ((0,19),(0,20))

THEN cte.external_system_ref_id

ELSE NULL

END flow_instance_id,

cte.agreement_number,

CASE

WHEN NVL(ct.intercompany_invoicing_flag, 'Y') = 'Y'

THEN 'Invoice'

ELSE 'Due To/From'

END cogs_type,

ct.base_txn_type_id,

ct.base_txn_source_type_id,

ct.base_txn_action_id,

ccd.gl_date,

CASE

WHEN (ct.doo_fullfill_line_id is NULL) THEN ct.inventory_item_id

WHEN (dfl.item_sub_type_code = 'ATO' OR dfl.item_sub_type_code = 'PTO-SMC' OR dfl.item_sub_type_code = 'PTO-NONSMC' OR dfl.item_sub_type_code = 'KIT-SMC' OR dfl.item_sub_type_code = 'KIT-NONSMC') THEN dfl.inventory_item_id

WHEN (p_dfl.item_sub_type_code = 'ATO' OR p_dfl.item_sub_type_code = 'PTO-SMC' OR p_dfl.item_sub_type_code = 'PTO-NONSMC' OR p_dfl.item_sub_type_code = 'KIT-SMC' OR p_dfl.item_sub_type_code = 'KIT-NONSMC') THEN p_dfl.inventory_item_id

WHEN (pp_dfl.item_sub_type_code = 'ATO' OR pp_dfl.item_sub_type_code = 'PTO-SMC' OR pp_dfl.item_sub_type_code = 'PTO-NONSMC' OR pp_dfl.item_sub_type_code = 'KIT-SMC' OR pp_dfl.item_sub_type_code = 'KIT-NONSMC') THEN pp_dfl.inventory_item_id

ELSE dfl.inventory_item_id

END inventory_item_id,

CASE

WHEN (dfl.item_sub_type_code = 'ATO' OR dfl.item_sub_type_code = 'PTO-SMC' OR dfl.item_sub_type_code = 'PTO-NONSMC' OR dfl.item_sub_type_code = 'KIT-SMC' OR dfl.item_sub_type_code = 'KIT-NONSMC') THEN dfl.config_inventory_item_id

WHEN (p_dfl.item_sub_type_code = 'ATO' OR p_dfl.item_sub_type_code = 'PTO-SMC' OR p_dfl.item_sub_type_code = 'PTO-NONSMC' OR p_dfl.item_sub_type_code = 'KIT-SMC' OR p_dfl.item_sub_type_code = 'KIT-NONSMC') THEN p_dfl.config_inventory_item_id

WHEN (pp_dfl.item_sub_type_code = 'ATO' OR pp_dfl.item_sub_type_code = 'PTO-SMC' OR pp_dfl.item_sub_type_code = 'PTO-NONSMC' OR pp_dfl.item_sub_type_code = 'KIT-SMC' OR pp_dfl.item_sub_type_code = 'KIT-NONSMC') THEN pp_dfl.config_inventory_item_id

ELSE dfl.config_inventory_item_id

END config_inventory_item_id,

r_dfl.inventory_item_id root_inventory_item_id,

ccdl.cost_element_id,

NVL(cce.cost_element_desc, cce.cost_element_code) cost_element_desc,

cce.cost_element_type,

CASE

WHEN ccdl.accounting_line_type IN ( 'COST_OF_GOODS_SOLD', 'RMA_GAIN_LOSS', 'INTER_COMPANY_COGS',

'INTER_ORGANIZATION_RECEIVABLES', 'INTER_ORGANIZATION_GAIN_LOSS', 'FREIGHT_EXPENSE')

THEN NVL(ccdl.ledger_amount,0)

ELSE 0

END cogs_amount,

CASE

WHEN ccdl.accounting_line_type IN ('DEFERRED_COST_OF_GOODS_SOLD', 'DEFERRED_RMA_GAIN_LOSS')

THEN NVL(ccdl.ledger_amount,0)

ELSE 0

END dcogs_amount,

ccd.base_currency_code,

NVL(ccd.layer_quantity,0) layer_quantity,

ccd.cost_transaction_uom,

ccd.accounted_flag,

CASE

WHEN ccdl.accounting_line_type IN ('COST_OF_GOODS_SOLD', 'RMA_GAIN_LOSS', 'INTER_COMPANY_COGS',

'INTER_ORGANIZATION_RECEIVABLES', 'INTER_ORGANIZATION_GAIN_LOSS', 'FREIGHT_EXPENSE')

THEN ccdl.sla_code_combination_id

ELSE NULL

END cogs_account_id

FROM cst_cost_distributions ccd,

cst_cost_distribution_lines ccdl,

cst_transactions ct,

cst_inv_transactions cit,

cst_trade_events cte,

doo_fulfill_lines_all_v dfl,

doo_fulfill_lines_all_v p_dfl,

doo_fulfill_lines_all_v pp_dfl,

doo_fulfill_lines_all_v r_dfl,

inv_org_parameters iop,

cst_cost_org_books ccob,

cst_cost_elements_vl cce

WHERE ccd.distribution_id = ccdl.distribution_id

AND ( ( ccdl.accounting_line_type IN ('COST_OF_GOODS_SOLD', 'INTER_COMPANY_COGS', 'DEFERRED_COST_OF_GOODS_SOLD',

'DEFERRED_RMA_GAIN_LOSS', 'RMA_GAIN_LOSS', 'INTER_ORGANIZATION_RECEIVABLES', 'INTER_ORGANIZATION_GAIN_LOSS') )

OR ( ccdl.accounting_line_type = 'FREIGHT_EXPENSE'

AND cit.transaction_flow_type NOT IN (4,5,7,8,11,14,15)

AND ct.intransit_flag = 'N' ) )

AND (

CASE

WHEN SIGN(NVL(layer_quantity,0)) = -1

THEN ccd.dep_trxn_id

ELSE ccd.rec_trxn_id

END ) = ct.transaction_id

AND NVL(cit.transaction_flow_type, 0) NOT IN (6, 9, 13, 16, 17)

AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

AND cit.external_system_ref_id = cte.trade_event_id (+)

AND ct.inventory_org_id = iop.organization_id

AND dfl.fulfill_line_id (+) = NVL(ct.doo_split_fulfill_line_id, ct.doo_fullfill_line_id)

AND p_dfl.fulfill_line_id (+) = dfl.parent_fulfill_line_id

AND pp_dfl.fulfill_line_id (+) = p_dfl.parent_fulfill_line_id

AND r_dfl.fulfill_line_id (+) = dfl.root_parent_fulfill_line_id

AND ccd.cost_organization_id = ccob.cost_org_id

AND ccd.cost_book_id = ccob.cost_book_id

AND ccob.primary_book_flag = 'Y'

AND cce.cost_element_id = ccdl.cost_element_id

UNION ALL

SELECT ccd.cost_organization_id cost_org_id,

ccob.cost_book_id,

ccd.ledger_id,

ccd.legal_entity_id,

NVL(iop.profit_center_bu_id, iop.business_unit_id) profit_center_bu_id,

ct.inventory_org_id,

DECODE (CT.INTRANSIT_FLAG, 'Y', IOP.MASTER_ORGANIZATION_ID, CT.INVENTORY_ORG_ID ) ITEM_ORGANIZATION_ID,

cte.successor_inv_org_id,

cte.successor_bu_id,

ct.txn_source_ref_doc_number doo_order_number,

ct.txn_source_ref_doc_type doo_order_type,

ct.doo_fullfill_line_id,

ct.txn_source_doc_number source_doc_no,

ct.txn_source_doc_type source_doc_type,

CASE WHEN cte.external_system_reference = 'FUSION'

AND cte.bill_to_customer_id > 0

THEN cte.bill_to_customer_id

ELSE NULL

END customer_id,

NULL ship_to_customer_id,

cte.external_system_ref_id flow_instance_id,

cte.agreement_number,

CASE

WHEN NVL(cte.intercompany_invoicing_flag, 'Y') = 'Y'

THEN 'Invoice'

ELSE 'Due To/From'

END cogs_type,

ct.base_txn_type_id,

ct.base_txn_source_type_id,

ct.base_txn_action_id,

ccd.gl_date,

ct.inventory_item_id,

NULL config_inventory_item_id,

ct.inventory_item_id root_inventory_item_id,

ccdl.cost_element_id,

NVL(cce.cost_element_desc, cce.cost_element_code) cost_element_desc,

cce.cost_element_type,

CASE

WHEN ccdl.accounting_line_type = 'FREIGHT_EXPENSE'

THEN NVL(ccdl.ledger_amount,0)

ELSE 0

END cogs_amount,

CAST(0 AS NUMBER) AS dcogs_amount,

ccd.base_currency_code,

NVL(ccd.layer_quantity,0) layer_quantity,

ccd.cost_transaction_uom,

ccd.accounted_flag,

CASE

WHEN ccdl.accounting_line_type = 'FREIGHT_EXPENSE'

THEN ccdl.sla_code_combination_id

ELSE NULL

END cogs_account_id

FROM cst_cost_distributions ccd,

cst_cost_distribution_lines ccdl,

cst_transactions ct,

cst_inv_transactions cit,

cst_trade_events cte,

inv_org_parameters iop,

cst_cost_org_books ccob,

cst_cost_elements_vl cce

WHERE ccd.distribution_id = ccdl.distribution_id

AND ccdl.accounting_line_type IN ('FREIGHT_EXPENSE')

AND cit.transaction_flow_type IN (4,5,7,8,11,14,15,12)

AND ct.intransit_flag = 'N'

AND (

CASE

WHEN SIGN(NVL(layer_quantity,0)) = -1

THEN ccd.dep_trxn_id

ELSE ccd.rec_trxn_id

END ) = ct.transaction_id

AND ct.cst_inv_transaction_id = cit.cst_inv_transaction_id

AND cit.external_system_ref_id = cte.inv_transaction_id

AND cte.trade_event_id =

(SELECT MIN(min_cte.trade_event_id)

FROM cst_trade_events min_cte

WHERE min_cte.inv_transaction_id = cte.inv_transaction_id

AND min_cte.transaction_type = 'INTRANSIT_ISSUE'

AND min_cte.prior_inventory_org_id IS NULL

)

AND cit.inventory_org_id = cte.inventory_org_id

AND ct.inventory_org_id = iop.organization_id

AND ccd.cost_organization_id = ccob.cost_org_id

AND ccd.cost_book_id = ccob.cost_book_id

AND ccob.primary_book_flag = 'Y'

AND cce.cost_element_id = ccdl.cost_element_id