with
perpetual_qry_1 as
(
select
x.*
from
(
select
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_moh_cost,
crcml.unit_op_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<=:p_gps_end_dt and
cce.event_date>=:p_gps_start_dt 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_moh_cost,
crcml.unit_op_cost,
crcml.unit_resource_cost,
crcml.unit_overhead_cost
) x
),
perpetual_qry_2 as
(
select
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,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type,
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_moh_cost) / sum(cce.event_quantity))material_oh_cost,
decode(sum(cce.event_quantity),0,0,sum(cce.event_quantity * crcml.unit_op_cost) / sum(cce.event_quantity))osp_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,
cce.event_date gl_date
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<=:p_gps_end_dt and
cce.event_date>=:p_gps_start_dt 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.item_type,
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,
cce.event_date
),
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.user_item_type,
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.material_oh_cost,
pq2.osp_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,
pq2.gl_date
from
perpetual_qry_2 pq2
),
perpetual_lines_qry as
(
select /*+ leading(pq,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.quantity_invoiced,
x.invoice_line,
x.item,
x.item_description,
&category_columns
x.user_item_type,
x.organization_code,
(select mp.organization_code from mtl_parameters mp,oe_order_lines_all oola where oola.line_id=x.cogs_om_line_id and oola.ship_from_org_id=mp.organization_id)ship_from_warehouse,
x.ledger_currency,
:p_period_name period,
x.unit_selling_price,
x.item_cost,
x.material_cost,
x.material_oh_cost,
x.osp_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,
(select actual_shipment_date from oe_order_lines_all where line_id = x.sales_order_line_id) so_line_closed_date,
x.gl_date,
case when x.earned_cogs is not null and x.deferred_cogs is not null
then case when x.earned_cogs + x.deferred_cogs = 0
then 100
else x.earned_cogs/(x.earned_cogs + x.deferred_cogs)*100
end
end cogs_percent,
x.cogs_account,
x.deferred_cogs_account,
case when x.revenue_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('seg','SQLGL','GL#',x.chart_of_accounts_id,NULL,x.revenue_ccid,'ALL','Y','VALUE') end revenue_account_segments,
case when x.unearn_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('seg','SQLGL','GL#',x.chart_of_accounts_id,NULL,x.unearn_ccid,'ALL','Y','VALUE') end unearned_account_segments,
case when x.unbill_ccid is not null then fnd_flex_xml_publisher_apis.process_kff_combination_1('seg','SQLGL','GL#',x.chart_of_accounts_id,NULL,x.unbill_ccid,'ALL','Y','VALUE') end unbilled_account_segments,
&lp_cogs_account_segments
&lp_rev_account_segments
x.cogs_om_line_id
from
(
select
pq.ledger,
pq.ledger_currency,
pq.operating_unit,
pq.order_number,
pq.order_date,
pq.customer_name customer,
pq.order_currency,
pq.sales_order_line order_line,
decode(rcta.trx_number,min(plq.trx_number),decode(pq.invoice_line,min(plq.min_inv_line),pq.total_line_quantity)) order_quantity,
pq.uom,
pq.sales_order_issue_date,
rcta.trx_number invoice_number,
rctla.quantity_invoiced,
pq.invoice_line,
pq.unit_selling_price,
pq.item_cost,
pq.material_cost,
pq.material_oh_cost,
pq.osp_cost,
pq.resource_cost,
pq.overhead_cost,
pq.item,
pq.item_description,
pq.user_item_type,
pq.inventory_item_id,
pq.organization_code,
pq.organization_id,
round(sum(decode(axclv.account_class,'UNEARN',0,'UNBILL',0, axclv.acctd_amount))*decode(pq.earned_cogs, pq.total_earned_cogs,1,pq.earned_cogs/decode(pq.total_earned_cogs,0,1,pq.total_earned_cogs)),2) earned_revenue,
round(sum(decode(axclv.account_class,'REV', 0,'UNBILL',0, axclv.acctd_amount))*decode(pq.deferred_cogs, pq.total_deferred_cogs,1,pq.deferred_cogs/decode(pq.total_deferred_cogs,0,1,pq.total_deferred_cogs)),2) unearned_revenue,
round(sum(decode(axclv.account_class,'REV', 0,'UNEARN',0, axclv.acctd_amount))*decode(pq.deferred_cogs, pq.total_deferred_cogs,1,pq.deferred_cogs/decode(pq.total_deferred_cogs,0,1,pq.total_deferred_cogs)),2) unbilled_revenue,
decode(rcta.trx_number,min(plq.trx_number),decode(pq.invoice_line,min(plq.min_inv_line),pq.earned_cogs)) earned_cogs,
decode(rcta.trx_number,min(plq.trx_number),decode(pq.invoice_line,min(plq.min_inv_line),pq.deferred_cogs)) deferred_cogs,
pq.cogs_account,
pq.deferred_cogs_account,
min(decode(axclv.account_class,'REV',axclv.code_combination_id)) revenue_ccid,
min(decode(axclv.account_class,'UNEARN',axclv.code_combination_id)) unearn_ccid,
min(decode(axclv.account_class,'UNBILL',axclv.code_combination_id)) unbill_ccid,
plq.interface_line_attribute6,
pq.sales_order_line_id,
pq.cogs_om_line_id,
pq.cogs_account_ccid,
pq.chart_of_accounts_id,
axclv.gl_date
from
perpetual_qry pq,
ar_xla_ctlgd_lines_v axclv,
ra_customer_trx_lines_all rctla,
ra_customer_trx_all rcta,
(
select
plq.interface_line_attribute1,
plq.interface_line_attribute6,
plq.trx_number,
min(plq.line_number) min_inv_line
from
perpetual_lines_qry plq
where
plq.rctla_rank=1
group by
plq.interface_line_attribute1,
plq.interface_line_attribute6,
plq.trx_number
) plq
where
(pq.customer_trx_line_id = rctla.customer_trx_line_id or pq.customer_trx_line_id = rctla.previous_customer_trx_line_id) and
pq.customer_trx_id = rcta.customer_trx_id and
axclv.customer_trx_line_id = rctla.customer_trx_line_id and
axclv.account_set_flag = 'N' and
axclv.account_class in ('REV', 'UNEARN', 'UNBILL') and
axclv.gl_date<=:p_gps_end_dt and
axclv.gl_date>=:p_gps_start_dt and
to_char(pq.sales_order_line_id)=rctla.interface_line_attribute6 and
to_char(pq.sales_order_line_id)=plq.interface_line_attribute6
group by
pq.ledger,
pq.ledger_currency,
pq.operating_unit,
pq.order_number,
pq.order_date,
pq.customer_name,
pq.order_currency,
pq.sales_order_line,
pq.sales_order_issue_date,
rcta.trx_number,
rctla.quantity_invoiced,
pq.invoice_line,
pq.unit_selling_price,
pq.item_cost,
pq.material_cost,
pq.material_oh_cost,
pq.osp_cost,
pq.resource_cost,
pq.overhead_cost,
pq.item,
pq.item_description,
pq.user_item_type,
pq.inventory_item_id,
pq.organization_code,
pq.organization_id,
pq.total_line_quantity,
pq.uom,
pq.earned_cogs,
pq.total_earned_cogs,
pq.deferred_cogs,
pq.total_deferred_cogs,
pq.cogs_account,
pq.deferred_cogs_account,
plq.interface_line_attribute6,
pq.sales_order_line_id,
pq.cogs_om_line_id,
pq.cogs_account_ccid,
pq.chart_of_accounts_id,
axclv.gl_date
having
:p_all_lines = 'Y' or
(decode(sum(rctla.revenue_amount),0,1,round(sum(decode(axclv.account_class,'UNEARN',0,'UNBILL',0, axclv.amount))/(sum(rctla.revenue_amount)/count(axclv.cust_trx_line_gl_dist_id)),3)) <>
decode(pq.earned_cogs,0,decode(pq.deferred_cogs,0,1,0),round((pq.earned_cogs/decode(pq.deferred_cogs+pq.earned_cogs,0,1,pq.deferred_cogs+pq.earned_cogs)),3)) and
pq.deferred_cogs not between -1*:p_amt_tolerance and :p_amt_tolerance
)
union
select
pq.ledger,
pq.ledger_currency,
pq.operating_unit,
pq.order_number,
pq.order_date,
pq.customer_name customer,
pq.order_currency,
pq.sales_order_line order_line,
pq.total_line_quantity order_quantity,
pq.uom,
pq.sales_order_issue_date,
null invoice_number,
null quantity_invoiced,
null invoice_line,
null unit_selling_price,
pq.item_cost,
pq.material_cost,
pq.material_oh_cost,
pq.osp_cost,
pq.resource_cost,
pq.overhead_cost,
pq.item,
pq.item_description,
pq.user_item_type,
pq.inventory_item_id,
pq.organization_code,
pq.organization_id,
null earned_revenue,
null unearned_revenue,
null unbilled_revenue,
pq.earned_cogs,
pq.deferred_cogs,
pq.cogs_account,
pq.deferred_cogs_account,
null revenue_ccid,
null unearn_ccid,
null unbill_ccid,
null interface_line_attribute6,
pq.sales_order_line_id,
pq.cogs_om_line_id,
pq.cogs_account_ccid,
pq.chart_of_accounts_id,
pq.gl_date
from
perpetual_qry pq
where
(pq.customer_trx_line_id is null or pq.customer_trx_id is null) and
(:p_all_lines = 'Y' or pq.deferred_cogs not between -1*:p_amt_tolerance and :p_amt_tolerance)
union
select
pq.ledger,
pq.ledger_currency,
pq.operating_unit,
pq.order_number,
pq.order_date,
pq.customer_name customer,
pq.order_currency,
pq.sales_order_line order_line,
pq.total_line_quantity order_quantity,
pq.uom,
pq.sales_order_issue_date,
null invoice_number,
null quantity_invoiced,
null invoice_line,
null unit_selling_price,
pq.item_cost,
pq.material_cost,
pq.material_oh_cost,
pq.osp_cost,
pq.resource_cost,
pq.overhead_cost,
pq.item,
pq.item_description,
pq.user_item_type,
pq.inventory_item_id,
pq.organization_code,
pq.organization_id,
null earned_revenue,
null unearned_revenue,
null unbilled_revenue,
pq.earned_cogs,
pq.deferred_cogs,
pq.cogs_account,
pq.deferred_cogs_account,
null revenue_ccid,
null unearn_ccid,
null unbill_ccid,
null interface_line_attribute6,
pq.sales_order_line_id,
pq.cogs_om_line_id,
pq.cogs_account_ccid,
pq.chart_of_accounts_id,
pq.gl_date
from
perpetual_qry pq
where
pq.customer_trx_line_id is not null and
pq.customer_trx_id is not null and
not exists
(
select /*+ use_concat no_unnest leading(rctla) use_nl(rctla,rctlgda) */
null
from
ra_customer_trx_lines_all rctla,
ra_cust_trx_line_gl_dist_all rctlgda
where
(pq.customer_trx_line_id = rctla.customer_trx_line_id or pq.customer_trx_line_id = rctla.previous_customer_trx_line_id) and
rctla.customer_trx_line_id = rctlgda.customer_trx_line_id and
rctlgda.account_set_flag = 'N' and
rctlgda.account_class in ('REV', 'UNEARN', 'UNBILL') and
rctlgda.gl_date<=:p_gps_end_dt and
rctlgda.gl_date>=:p_gps_start_dt
) and
(:p_all_lines = 'Y' or pq.deferred_cogs not between -1*:p_amt_tolerance and :p_amt_tolerance)
) x
where
3=3 and
:p_ledger=:p_ledger and
:p_cost_method=1 and
nvl(:p_cost_type,'?')=nvl(:p_cost_type,'?')
order by
x.order_number,
x.order_line,
nvl2(order_quantity,1,2) |
