O2C Cycle Reports

 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
                       )