1. Customer Sales Margin Report
In This Report All O2C Cycle Is Covered
SELECT DISTINCT rct.trx_number invoice_number, rct.trx_date invocie_date,
rct.bill_to_site_use_id bill_to, rct.cust_trx_type_id,
rct.invoice_currency_code currency_code,
rctl.customer_trx_line_id, rctl.interface_line_attribute6,
rctl.sales_order_source,
NVL (rctl.quantity_credited, rctl.quantity_invoiced) quantity,mmt.TRANSACTION_QUANTITY,
rctl.unit_selling_price sale_rate_per,
ooh.cust_po_number customer_po_number,
ooh.order_number sales_order_number,
ool.order_quantity_uom umo, hp.party_name,
hp.address1 site_name, msi.inventory_item_id,
msi.organization_id, ooh.ship_from_org_id,
msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6 item,
msi.description, rbs.NAME transaction_type,
gck.concatenated_segments ACCOUNT,
(SELECT NVL (SUM (jatt.tax_amount), 0)
FROM jai_ar_trx_tax_lines jatt
WHERE jatt.link_to_cust_trx_line_id =
rctl.customer_trx_line_id)
tax
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
oe_order_headers_all ooh,
oe_order_lines_all ool,
oe_transaction_types_tl ott,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts_all hcaa,
hz_parties hp,
hz_party_sites hps,
mtl_system_items_b msi,
ra_batch_sources_all rbs,
ra_cust_trx_line_gl_dist_all lgd,
gl_code_combinations gcc,
gl_code_combinations_kfv gck,
mtl_material_transactions mmt
WHERE rct.customer_trx_id = rctl.customer_trx_id
-- AND rct.trx_number = NVL (:invoice_no, rct.trx_number)
--AND rct.trx_date BETWEEN NVL (:from_date, rct.trx_date)
-- AND NVL (:TO_DATE, rct.trx_date)
AND rctl.line_type = 'LINE'
AND rctl.interface_line_context = 'ORDER ENTRY'
AND rctl.interface_line_attribute1 = ooh.order_number
AND rct.cust_trx_type_id NOT IN(select rct.cust_trx_type_id from ra_cust_trx_types_all rct
where rct.NAME like '%DM')
AND ooh.header_id = ool.header_id
AND ooh.order_type_id = ott.transaction_type_id
AND ott.NAME = NVL (:order_type, ott.NAME)
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND rct.bill_to_customer_id = hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND hp.party_name = NVL (:cust_name, hp.party_name)
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id(+)
--AND ool.ship_from_org_id = msi.organization_id
AND ooh.ship_from_org_id = msi.organization_id
AND rct.batch_source_id = rbs.batch_source_id
AND lgd.customer_trx_line_id = rctl.customer_trx_line_id
AND lgd.code_combination_id = gcc.code_combination_id
AND gck.code_combination_id = gcc.code_combination_id
AND rctl.inventory_item_id = msi.inventory_item_id
AND ( msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6
) =
NVL (:item_code,
msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6
)
AND mmt.trx_source_line_id = rctl.interface_line_attribute6
-- AND mmt.transaction_type_id = 33
AND mmt.costed_flag IS NULL
and ool.ORDERED_ITEM=NVL (:item_code,
msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6
)
In This Report All O2C Cycle Is Covered
SELECT DISTINCT rct.trx_number invoice_number, rct.trx_date invocie_date,
rct.bill_to_site_use_id bill_to, rct.cust_trx_type_id,
rct.invoice_currency_code currency_code,
rctl.customer_trx_line_id, rctl.interface_line_attribute6,
rctl.sales_order_source,
NVL (rctl.quantity_credited, rctl.quantity_invoiced) quantity,mmt.TRANSACTION_QUANTITY,
rctl.unit_selling_price sale_rate_per,
ooh.cust_po_number customer_po_number,
ooh.order_number sales_order_number,
ool.order_quantity_uom umo, hp.party_name,
hp.address1 site_name, msi.inventory_item_id,
msi.organization_id, ooh.ship_from_org_id,
msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6 item,
msi.description, rbs.NAME transaction_type,
gck.concatenated_segments ACCOUNT,
(SELECT NVL (SUM (jatt.tax_amount), 0)
FROM jai_ar_trx_tax_lines jatt
WHERE jatt.link_to_cust_trx_line_id =
rctl.customer_trx_line_id)
tax
FROM ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
oe_order_headers_all ooh,
oe_order_lines_all ool,
oe_transaction_types_tl ott,
hz_cust_site_uses_all hcsu,
hz_cust_acct_sites_all hcas,
hz_cust_accounts_all hcaa,
hz_parties hp,
hz_party_sites hps,
mtl_system_items_b msi,
ra_batch_sources_all rbs,
ra_cust_trx_line_gl_dist_all lgd,
gl_code_combinations gcc,
gl_code_combinations_kfv gck,
mtl_material_transactions mmt
WHERE rct.customer_trx_id = rctl.customer_trx_id
-- AND rct.trx_number = NVL (:invoice_no, rct.trx_number)
--AND rct.trx_date BETWEEN NVL (:from_date, rct.trx_date)
-- AND NVL (:TO_DATE, rct.trx_date)
AND rctl.line_type = 'LINE'
AND rctl.interface_line_context = 'ORDER ENTRY'
AND rctl.interface_line_attribute1 = ooh.order_number
AND rct.cust_trx_type_id NOT IN(select rct.cust_trx_type_id from ra_cust_trx_types_all rct
where rct.NAME like '%DM')
AND ooh.header_id = ool.header_id
AND ooh.order_type_id = ott.transaction_type_id
AND ott.NAME = NVL (:order_type, ott.NAME)
AND rct.bill_to_site_use_id = hcsu.site_use_id
AND rct.bill_to_customer_id = hcaa.cust_account_id
AND hcaa.party_id = hp.party_id
AND hp.party_name = NVL (:cust_name, hp.party_name)
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcas.party_site_id = hps.party_site_id(+)
--AND ool.ship_from_org_id = msi.organization_id
AND ooh.ship_from_org_id = msi.organization_id
AND rct.batch_source_id = rbs.batch_source_id
AND lgd.customer_trx_line_id = rctl.customer_trx_line_id
AND lgd.code_combination_id = gcc.code_combination_id
AND gck.code_combination_id = gcc.code_combination_id
AND rctl.inventory_item_id = msi.inventory_item_id
AND ( msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6
) =
NVL (:item_code,
msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6
)
AND mmt.trx_source_line_id = rctl.interface_line_attribute6
-- AND mmt.transaction_type_id = 33
AND mmt.costed_flag IS NULL
and ool.ORDERED_ITEM=NVL (:item_code,
msi.segment1
|| '.'
|| msi.segment2
|| '.'
|| msi.segment3
|| '.'
|| msi.segment4
|| '.'
|| msi.segment5
|| '.'
|| msi.segment6
)
No comments:
Post a Comment