CST COGS Revenue Matching
Description
Categories: Enginatics
Repository: Github
Repository: Github
Imported from Concurrent Program
Application: Bills of Material
Source: COGS Revenue Matching Report
Short Name: CSTRCMRX
The COGS/Revenue Matching Report displays earned and unearned (deferred) revenue, and cost of goods sold amounts for sales orders issues specified in the report's run-time parameters.
The report displays shipped sales order and associated sales order lines an ... more
Application: Bills of Material
Source: COGS Revenue Matching Report
Short Name: CSTRCMRX
The COGS/Revenue Matching Report displays earned and unearned (deferred) revenue, and cost of goods sold amounts for sales orders issues specified in the report's run-time parameters.
The report displays shipped sales order and associated sales order lines an ... more
Run
CST COGS Revenue Matching and other Oracle EBS reports with Blitz Report™ on our demo environment
with perpetual_qry_1 as ( select x.* from ( select /*+ leading(haou,crcml,oola,ooha,cce,mta) use_nl(haou,crcml, oola, ooha) index(crcml cst_rev_cogs_match_lines_n2) */ crcml.operating_unit_id, haou.name operating_unit, gl.name ledger, gl.currency_code currency, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id, crcml.inventory_item_id, crcml.unit_cost, crcml.unit_material_cost, crcml.unit_resource_cost, crcml.unit_overhead_cost, sum(decode(mta.accounting_line_type, 35, mta.base_transaction_value,0)) cogs_balance, last_value(sum(decode(mta.accounting_line_type, 35, mta.base_transaction_value,0))) -- CST_ACCOUNTING_LINE_TYPE 35 = Cost of Goods Sold over ( partition by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id order by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id ) total_cogs_balance, sum(decode(mta.accounting_line_type, 36, mta.base_transaction_value,0)) def_cogs_balance, last_value(sum(decode(mta.accounting_line_type, 36, mta.base_transaction_value,0))) -- CST_ACCOUNTING_LINE_TYPE 36 = Deferred Cost of Goods Sold over( partition by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id order by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id ) total_def_cogs_balance from cst_revenue_cogs_match_lines crcml, cst_cogs_events cce, oe_order_lines_all oola, oe_order_headers_all ooha, mtl_transaction_accounts mta, gl_ledgers gl, hr_all_organization_units haou where 1=1 and crcml.ledger_id = :p_ledger_id and crcml.pac_cost_type_id is null and cce.event_date <= nvl2(:p_gps_end_dt,:p_gps_end_dt - (inv_le_timezone_pub.get_le_day_time_for_ou(sysdate,crcml.operating_unit_id)-sysdate),cce.event_date) and cce.cogs_om_line_id = crcml.cogs_om_line_id and gl.ledger_id = crcml.ledger_id and oola.header_id = ooha.header_id and oola.line_id = crcml.cogs_om_line_id and mta.transaction_id (+) = cce.mmt_transaction_id and haou.organization_id (+) = crcml.operating_unit_id group by crcml.operating_unit_id, haou.name, gl.name, gl.currency_code, ooha.order_number, ooha.booked_date, ooha.transactional_curr_code, oola.line_number, oola.sold_to_org_id, oola.item_type_code, oola.order_quantity_uom, crcml.sales_order_issue_date, crcml.revenue_om_line_id, crcml.organization_id, crcml.deferred_cogs_acct_id, crcml.cogs_om_line_id, crcml.inventory_item_id, crcml.unit_cost, crcml.unit_material_cost, crcml.unit_resource_cost, crcml.unit_overhead_cost ) x ), perpetual_qry_2 as ( select /*+ index(rctla ra_customer_trx_lines_n9) leading (pq1, rctla) use_nl(pq1, rctla) */ pq1.operating_unit_id, pq1.operating_unit, pq1.ledger, pq1.currency ledger_currency, pq1.order_number order_number, pq1.booked_date order_date, substrb(hp.party_name,1,50) customer_name, pq1.transactional_curr_code order_currency, pq1.line_number sales_order_line, pq1.sales_order_issue_date, pq1.revenue_om_line_id sales_order_line_id, pq1.item_type_code item_type_code, msiv.concatenated_segments item, msiv.description item_description, msiv.inventory_item_id, mp.organization_code, mp.organization_id, rctla.line_number invoice_line, rctla.unit_selling_price, rctla.customer_trx_line_id, rctla.customer_trx_id, sum(cce.event_quantity) total_line_quantity, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_cost) / sum(cce.event_quantity)) item_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_material_cost) / sum(cce.event_quantity)) material_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_resource_cost) / sum(cce.event_quantity)) resource_cost, decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_overhead_cost) / sum(cce.event_quantity)) overhead_cost, pq1.uom, pq1.cogs_balance earned_cogs, pq1.total_cogs_balance total_earned_cogs, pq1.def_cogs_balance deferred_cogs, pq1.total_def_cogs_balance total_deferred_cogs, gcck_cogs.concatenated_segments cogs_account, gcck_dcogs.concatenated_segments deferred_cogs_account, pq1.cogs_om_line_id cogs_om_line_id, gcck_cogs.code_combination_id cogs_account_ccid, gcck_cogs.chart_of_accounts_id from perpetual_qry_1 pq1, cst_revenue_cogs_match_lines crcml, mtl_system_items_vl msiv, mtl_parameters mp, gl_code_combinations_kfv gcck_cogs, gl_code_combinations_kfv gcck_dcogs, ra_customer_trx_lines_all rctla, hz_cust_accounts hca, hz_parties hp, cst_cogs_events cce where msiv.inventory_item_id = pq1.inventory_item_id and msiv.organization_id = pq1.organization_id and mp.organization_id = pq1.organization_id and gcck_cogs.code_combination_id = crcml.cogs_acct_id and gcck_dcogs.code_combination_id = pq1.deferred_cogs_acct_id and rctla.line_type (+) = 'LINE' and rctla.interface_line_context (+) = 'ORDER ENTRY' and rctla.interface_line_attribute6 (+) = to_char(pq1.revenue_om_line_id) and rctla.sales_order (+) = to_char(pq1.order_number) and pq1.sold_to_org_id = hca.cust_account_id (+)and hca.party_id = hp.party_id and cce.cogs_om_line_id = pq1.cogs_om_line_id and cce.event_type in (1,2) and cce.event_date <= nvl2(:p_gps_end_dt,:p_gps_end_dt - (inv_le_timezone_pub.get_le_day_time_for_ou(sysdate,pq1.operating_unit_id)-sysdate),cce.event_date) and crcml.cogs_om_line_id = pq1.cogs_om_line_id and crcml.pac_cost_type_id is null and ( :p_all_lines = 'Y' or pq1.def_cogs_balance not between -1*:p_amt_tolerance and :p_amt_tolerance) group by pq1.operating_unit_id, pq1.operating_unit, pq1.ledger, pq1.currency, pq1.order_number, pq1.booked_date, substrb(hp.party_name,1,50), pq1.transactional_curr_code, pq1.line_number, pq1.sales_order_issue_date, pq1.revenue_om_line_id, pq1.item_type_code, pq1.uom, msiv.concatenated_segments, msiv.description, msiv.inventory_item_id, mp.organization_code, mp.organization_id, rctla.line_number, rctla.unit_selling_price, rctla.customer_trx_line_id, rctla.customer_trx_id, pq1.cogs_balance, pq1.total_cogs_balance, pq1.def_cogs_balance, pq1.total_def_cogs_balance, gcck_cogs.concatenated_segments, gcck_dcogs.concatenated_segments, pq1.cogs_om_line_id, gcck_cogs.code_combination_id, gcck_cogs.chart_of_accounts_id ), perpetual_qry as ( select pq2.operating_unit_id, pq2.operating_unit, pq2.ledger, pq2.ledger_currency, pq2.order_number, pq2.order_date, pq2.customer_name, pq2.order_currency, pq2.sales_order_line, pq2.sales_order_issue_date, pq2.sales_order_line_id, pq2.item_type_code, pq2.item, pq2.item_description, pq2.inventory_item_id, pq2.organization_code, pq2.organization_id, pq2.invoice_line, pq2.unit_selling_price, pq2.customer_trx_line_id, pq2.customer_trx_id, pq2.total_line_quantity, pq2.item_cost, pq2.material_cost, pq2.resource_cost, pq2.overhead_cost, pq2.uom, pq2.earned_cogs, case when pq2.item_type_code = 'INCLUDED' then (select sum(pq2_1.earned_cogs) from perpetual_qry_2 pq2_1 where pq2_1.operating_unit_id = pq2.operating_unit_id and pq2_1.order_number = pq2.order_number and pq2_1.sales_order_line = pq2.sales_order_line and pq2_1.sales_order_line_id = pq2.sales_order_line_id and pq2_1.item_type_code = 'INCLUDED' and pq2_1.customer_trx_line_id = pq2.customer_trx_line_id and pq2_1.customer_trx_id = pq2.customer_trx_id ) else pq2.total_earned_cogs end total_earned_cogs, pq2.deferred_cogs, pq2.total_deferred_cogs, pq2.cogs_account, pq2.deferred_cogs_account, pq2.cogs_om_line_id, pq2.cogs_account_ccid, pq2.chart_of_accounts_id from perpetual_qry_2 pq2 ), perpetual_lines_qry as ( select /*+ leading(pq2,rctla) index(rctla ra_customer_trx_lines_n9)*/ rctla.interface_line_attribute1, rctla.interface_line_attribute6, rcta.trx_number, rctla.line_number, rctla.customer_trx_id, row_number() over (partition by rctla.sales_order,rctla.interface_line_attribute6,rctla.interface_line_context,rctla.line_type order by rctla.customer_trx_id) rctla_rank from ra_customer_trx_lines_all rctla, ra_customer_trx_all rcta, perpetual_qry pq where rctla.interface_line_context='ORDER ENTRY' and rctla.line_type ='LINE' and rctla.customer_trx_id = rcta.customer_trx_id and rctla.sales_order = to_char(pq.order_number) and rctla.interface_line_attribute6 = to_char(pq.sales_order_line_id) ) -- -- Main Query Starts Here -- select x.ledger, x.operating_unit, x.order_number, x.order_date, x.customer, x.order_currency, x.order_line, x.order_quantity, x.uom, x.sales_order_issue_date, x.invoice_number, x.invoice_line, x.item, x.item_description, x.organization_code, x.ledger_currency, :p_period_name period, x.unit_selling_price, x.item_cost, x.material_cost, x.resource_cost, x.overhead_cost, &lp_cost_type_col x.earned_revenue, x.unearned_revenue, x.unbilled_revenue, case when x.earned_revenue is not null and x.unearned_revenue is not null and x.unbilled_revenue is not null then x.earned_revenue + x.unearned_revenue + x.unbilled_revenue end total_revenue, case when x.earned_revenue is not null and x.unearned_revenue is not null and x.unbilled_revenue is not null then case when x.earned_revenue + x.unearned_revenue + x.unbilled_revenue = 0 then 100 else x.earned_revenue/(x.earned_revenue + x.unearned_revenue + x.unbilled_revenue)*100 end end revenue_percent, x.earned_cogs, x.deferred_cogs, x.earned_cogs+x.deferred_cogs total_cogs, case when x.earned_cogs is not null and x.deferred_cogs is not |