1. Tables and Views for SCM
  2. CST_XLA_COGS_TXNS_V

CST_XLA_COGS_TXNS_V

Details

  • Schema: FUSION

  • Object owner: CST

  • Object type: VIEW

Columns

Name

BILLING_FULFILL_LINE_ID

COGS_TRANSACTION_ID

Query

SQL_Statement

SELECT

nvl(dfla.root_parent_fulfill_line_id,dfla.fulfill_line_id) billing_fulfill_line_id,

ct.transaction_id cogs_transaction_id

FROM cst_transactions ct,

cst_transfer_order_lines ctol,

doo_fulfill_lines_all dfla

WHERE ctol.external_system_reference (+) = 'FUSION'

AND ctol.cst_transfer_order_line_id (+) = ct.cst_transfer_order_line_id

AND dfla.fulfill_line_id(+) =ct.doo_fullfill_line_id

UNION ALL

select nvl(crl.root_parent_fulfill_line_id, crl.doo_fulfill_line_id) billing_fulfill_line_id,

cct.transaction_id cogs_transaction_id

from cst_cogs_transactions cct,

cst_revenue_lines crl

where

crl.revenue_line_id (+) = cct.revenue_line_id

and cct.revenue_line_id >0

union all

select

nvl(dfla.root_parent_fulfill_line_id,dfla.fulfill_line_id) billing_fulfill_line_id,

csrmd.cogs_transaction_id cogs_transaction_id

from

(select

case

when csrm.billing_fullfill_line_id='-1'

then cct.shipment_fullfill_line_id

else csrm.billing_fullfill_line_id

end ship_ref_fullfill_line_id,

cct.transaction_id cogs_transaction_id

from fusion.cst_cogs_transactions cct,

fusion.cst_shipment_revenue_map csrm

where cct.shipment_fullfill_line_id = csrm.shipment_fullfill_line_id

and csrm.bill_only_flag = 'N'

and cct.revenue_line_id ='-1'

and (csrm.delivery_id = to_char(cct.delivery_id)

or csrm.rma_transaction_id = to_char(cct.rma_transaction_id))

) csrmd,

doo_fulfill_lines_all dfla

where

dfla.fulfill_line_id = csrmd.ship_ref_fullfill_line_id