1. Tables and Views for SCM
  2. CST_REVENUE_DETAILS_V

CST_REVENUE_DETAILS_V

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: VIEW

Columns

Name

EXTERNAL_SYSTEM_REFERENCE

LEDGER_ID

LEGAL_ENTITY_ID

PROFIT_CENTER_BU_ID

COST_ORG_ID

COST_BOOK_ID

INVENTORY_ORG_ID

ITEM_ORGANIZATION_ID

DOO_ORDER_NUMBER

DOO_ORDER_TYPE

DOO_FULLFILL_LINE_ID

FLOW_INSTANCE_ID

SALES_ORDER_SHIPMENT_NUMBER

SALES_ORDER_SHIPMENT_TYPE

INVOICE_SOURCE

INVOICE_NUMBER

INVOICE_ID

INVOICE_LINE_ID

LINE_NO

CUSTOMER_ID

SHIP_TO_CUSTOMER_ID

SUCCESSOR_BU_ID

INVOICE_DATE

GL_DATE

REVENUE_TYPE

INVENTORY_ITEM_ID

CONFIG_INVENTORY_ITEM_ID

ROOT_INVENTORY_ITEM_ID

TOTAL_REV

RECOGNIZED_REV

INVOICE_CURRENCY

ACCTD_RECOGNIZED_REV

LEDGER_CURRENCY

Query

SQL_Statement

SELECT

crl.external_system_reference,

crl.ledger_id,

crl.legal_entity_id,

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

ccio.cost_org_id,

ccob.cost_book_id,

CASE WHEN crl.INVENTORY_ORG_ID > 0 then crl.INVENTORY_ORG_ID

else dfl.inventory_organization_id end inventory_org_id,

IOP.MASTER_ORGANIZATION_ID item_organization_id,

crl.doo_order_number,

'SO' doo_order_type,

CASE

WHEN (crl.ato_top_model_fline_id > 0 AND dfl.item_sub_type_code IN ('ATO', 'ATO-OPTION-CLASS', 'ATO-OPTION')) THEN crl.ato_top_model_fline_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.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,

NULL flow_instance_id,

CAST(NULL AS VARCHAR2(30)) sales_order_shipment_number,

CAST(NULL AS VARCHAR2(30)) sales_order_shipment_type,

crl.invoice_source,

crl.invoice_number,

crl.customer_trx_id invoice_id,

crl.customer_trx_line_id invoice_line_id,

row_number() OVER (PARTITION BY crl.external_system_reference, crl.customer_trx_line_id ORDER BY crl.gl_date, crl.customer_trx_line_id) line_no,

dfl.bill_to_customer_id customer_id,

dfl.ship_to_party_id ship_to_customer_id,

NULL successor_bu_id,

crl.invoice_date,

crl.gl_date,

'Invoice' revenue_type,

CASE

WHEN (crl.ato_top_model_item_id > 0 AND dfl.item_sub_type_code IN ('ATO', 'ATO-OPTION-CLASS', 'ATO-OPTION')) THEN crl.ato_top_model_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 (crl.config_inventory_item_id > 0 AND dfl.item_sub_type_code IN ('ATO', 'ATO-OPTION-CLASS', 'ATO-OPTION')) THEN crl.config_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.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,

CASE WHEN crl.external_system_reference = 'VRM' then crl.acctd_amount

ELSE nvl(crl.revenue_amount,crl.extended_amount) *

CASE

WHEN (crl.acctd_amount = 0)

THEN 1

WHEN (crl.invoice_line_amount = 0)

THEN 1

ELSE (crl.acctd_amount/crl.invoice_line_amount)

END

END total_rev,

crl.acctd_amount recognized_rev,

crl.invoice_currency_code invoice_currency,

crl.acctd_amount acctd_recognized_rev,

gl.currency_code ledger_currency

FROM

cst_revenue_lines crl,

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_inv_orgs ccio,

gl_ledgers gl,

cst_cost_org_books ccob

WHERE

dfl.fulfill_line_id (+) = crl.doo_fulfill_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 (CASE WHEN crl.INVENTORY_ORG_ID > 0 then crl.INVENTORY_ORG_ID

else dfl.inventory_organization_id end) = iop.organization_id (+)

AND (CASE WHEN crl.INVENTORY_ORG_ID > 0 then crl.INVENTORY_ORG_ID

else dfl.inventory_organization_id end) = ccio.inv_org_id (+)

AND crl.ledger_id = gl.ledger_id

AND ccio.cost_org_id = ccob.cost_org_id (+)

AND NVL(ccob.primary_book_flag, 'Y') = 'Y'

AND crl.invoice_source = 'DOO'

UNION ALL

SELECT

crl.external_system_reference,

crl.ledger_id,

crl.legal_entity_id,

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

ccio.cost_org_id,

ccob.cost_book_id,

cte.inventory_org_id inventory_org_id,

IOP.MASTER_ORGANIZATION_ID ITEM_ORGANIZATION_ID,

cte.txn_source_doc_number doo_order_number,

cte.txn_source_doc_type doo_order_type,

NULL doo_fullfill_line_id,

cte.external_system_ref_id flow_instance_id,

cte.ownership_change_doc_number sales_order_shipment_number,

cte.ownership_change_doc_type sales_order_shipment_type,

crl.invoice_source,

crl.invoice_number,

crl.customer_trx_id invoice_id,

crl.customer_trx_line_id invoice_line_id,

row_number() OVER (PARTITION BY crl.customer_trx_line_id ORDER BY crl.gl_date, crl.customer_trx_line_id) line_no,

crl.bill_to_customer_id customer_id,

NULL ship_to_customer_id,

cte.successor_bu_id successor_bu_id,

crl.invoice_date,

crl.gl_date,

'Intercompany' revenue_type,

crl.inventory_item_id,

NULL config_inventory_item_id,

crl.inventory_item_id root_inventory_item_id,

nvl(crl.revenue_amount,crl.extended_amount) *

CASE

WHEN (crl.acctd_amount = 0)

THEN 1

WHEN (crl.invoice_line_amount = 0)

THEN 1

ELSE (crl.acctd_amount/crl.invoice_line_amount)

END total_rev,

crl.acctd_amount recognized_rev,

crl.invoice_currency_code invoice_currency,

crl.acctd_amount acctd_recognized_rev,

gl.currency_code ledger_currency

FROM

cst_revenue_lines crl,

cst_trade_events cte,

inv_org_parameters iop,

cst_cost_inv_orgs ccio,

gl_ledgers gl,

cst_cost_org_books ccob

WHERE

crl.invoice_source = 'FOS'

AND NVL(cte.external_system_reference, 'FUSION') = 'FUSION'

AND crl.doo_order_type = TO_CHAR( cte.ownership_change_txn_id)

AND nvl(crl.root_parent_fulfill_line_id,crl.doo_fulfill_line_id) = cte.ptr_id

AND cte.intercompany_invoicing_flag = 'Y'

AND cte.transaction_type IN ('INTRANSIT_ISSUE', 'INTRANSIT_RETURN_RECEIPT')

AND crl.legal_entity_id = cte.legal_entity_id

AND crl.business_unit_id = cte.business_unit_id

AND cte.inventory_org_id = iop.organization_id (+)

AND cte.inventory_org_id = ccio.inv_org_id (+)

AND crl.ledger_id = gl.ledger_id

AND ccio.cost_org_id = ccob.cost_org_id (+)

AND NVL(ccob.primary_book_flag, 'Y') = 'Y'

UNION ALL

SELECT

NULL external_system_reference,

ccd.ledger_id,

ccd.legal_entity_id,

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

ccd.cost_organization_id cost_org_id,

ccob.cost_book_id,

ct.inventory_org_id,

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

NULL,

NULL,

ct.doo_fullfill_line_id,

cte.external_system_ref_id flow_instance_id,

ct.txn_source_doc_number sales_order_shipment_number,

ct.txn_source_doc_type sales_order_shipment_type,

DECODE(cte.external_system_reference, 'FUSION' , 'SFO' , 'CST' ) invoice_source,

NULL,

NULL,

NULL,

1 line_no,

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.successor_bu_id,

ccd.gl_date,

ccd.gl_date,

'Due To/From' revenue_type,

ct.inventory_item_id,

NULL config_inventory_item_id,

ct.inventory_item_id root_inventory_item_id,

CASE

WHEN ccdl.accounting_line_type = 'INTER_ORGANIZATION_RECEIVABLES'

THEN ccdl.ledger_amount

ELSE 0

END total_rev,

CASE

WHEN ccdl.accounting_line_type = 'INTER_ORGANIZATION_RECEIVABLES'

THEN ccdl.ledger_amount

ELSE 0

END recognized_rev,

ccd.base_currency_code invoice_currency,

CASE

WHEN ccdl.accounting_line_type = 'INTER_ORGANIZATION_RECEIVABLES'

THEN ccdl.ledger_amount

ELSE 0

END acctd_recognized_rev,

ccd.base_currency_code ledger_currency

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

WHERE

ccd.distribution_id = ccdl.distribution_id

AND ccdl.accounting_line_type IN ('INTER_ORGANIZATION_RECEIVABLES')

AND ccd.transaction_id = ct.transaction_id

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 ccd.cost_organization_id = ccob.cost_org_id

AND ccd.cost_book_id = ccob.cost_book_id

AND ccob.primary_book_flag = 'Y'