GL Query for Purchase India(source) - Receiving India(category)

SELECT msib.segment1 item, msib.description, rsh.receipt_num,
       mp.organization_code, gjl.accounted_dr, gjl.accounted_cr, gjh.je_source, gjh.je_category
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_code_combinations_kfv gcc,
       apps.gl_import_references gir,
       apps.rcv_transactions rt,
       apps.rcv_shipment_headers rsh,
       apps.rcv_shipment_lines rsl,
       apps.gl_je_batches gjb,
       apps.mtl_parameters mp,
       apps.mtl_system_items_b msib
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjh.je_header_id = gir.je_header_id
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjb.je_batch_id = gir.je_batch_id
   AND gjh.je_source = 'Purchasing India'
   AND gjl.reference_2 LIKE 'India Localization Entry'
   AND gjl.code_combination_id = gcc.code_combination_id
   AND rt.organization_id = mp.organization_id
   AND rt.transaction_id = gjl.reference_5
   AND rt.shipment_header_id = rsh.shipment_header_id
   AND rsl.shipment_header_id = rsh.shipment_header_id
   AND rsl.shipment_line_id = rt.shipment_line_id
   AND msib.inventory_item_id = rsl.item_id
   AND msib.organization_id = rt.organization_id
   AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
   AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
   AND gcc.segment3 in (100603)
   AND gcc.segment2 not in (102)  

GL Query for Register India(category)

SELECT jrmt.transaction_number, jrmt.regime_code, jrmt.transaction_type_code,
       jrmt.transaction_date, jrmt.remarks, fu.description, gjl.accounted_dr,
       gjl.accounted_cr , gjh.je_source, gjh.je_category
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_je_batches gjb,
       apps.gl_import_references gir,
       apps.gl_code_combinations_kfv gcc,
       apps.jai_rgm_manual_trxs jrmt,
       apps.fnd_user fu
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjb.je_batch_id = gir.je_batch_id
   AND gjh.je_source IN ('Service Tax India','VAT India')
   AND jrmt.transaction_number = gjl.reference_5
   AND fu.user_id = jrmt.created_by
   AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
   AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
   AND gcc.segment3 in (100603)
   AND gcc.segment2 not in (102)
 
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SELECT msib.segment1 item, mp.organization_code, pv.vendor_name,
       jcrt.excise_invoice_no, jcrt.excise_invoice_date, jcrt.register_type,
       jcrt.remarks, jcrt.transaction_date, gjl.accounted_dr,
       gjl.accounted_cr , gjh.je_source, gjh.je_category
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_je_batches gjb,
       apps.gl_import_references gir,
       apps.gl_code_combinations_kfv gcc,
       apps.jai_cmn_rg_23ac_ii_trxs jcrt,
       apps.mtl_system_items_b msib,
       apps.mtl_parameters mp,
       apps.po_vendors pv
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjb.je_batch_id = gir.je_batch_id
   AND gjh.je_source = 'Register India'
   AND TO_CHAR (jcrt.register_id) = gjl.reference_5
   AND msib.inventory_item_id = jcrt.inventory_item_id
   AND msib.organization_id = jcrt.organization_id
   AND mp.organization_id = jcrt.organization_id
   AND pv.vendor_id = jcrt.vendor_id
   AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
   AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
   AND gcc.segment3 in (100603)
   AND gcc.segment2 not in (102)
UNION ALL
SELECT msib.segment1, mp.organization_code, pv.vendor_name,
       jcrt.excise_invoice_no, jcrt.excise_invoice_date, jcrt.register_type,
       jcrt.remarks, jcrt.transaction_date, gjl.accounted_dr,
       gjl.accounted_cr, gjh.je_source, gjh.je_category
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_je_batches gjb,
       apps.gl_import_references gir,
       apps.gl_code_combinations_kfv gcc,
       apps.jai_cmn_rg_23ac_ii_trxs jcrt,
       apps.mtl_system_items_b msib,
       apps.mtl_parameters mp,
       apps.po_vendors pv
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjl.code_combination_id = gcc.code_combination_id
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjb.je_batch_id = gir.je_batch_id
   AND gjh.je_source = 'Register India'
   AND TO_CHAR (jcrt.register_id) = gjl.reference_6
   AND msib.inventory_item_id = jcrt.inventory_item_id
   AND msib.organization_id = jcrt.organization_id
   AND mp.organization_id = jcrt.organization_id
   AND pv.vendor_id = jcrt.vendor_id
   AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
   AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
   AND gcc.segment3 in (100603)
   AND gcc.segment2 not in (102)

GL Query for Cost management(source) - Receiing(category)

select distinct gjl.accounted_dr, gjl.accounted_cr, gjh.je_source, gjh.je_category, gcc.concatenated_segments, gjl.reference_5, gjh.status
, gcc.code_combination_id, pda.code_combination_id
--    xdl.source_distribution_id_num_1  , xdl.source_distribution_type
from
    apps.gl_je_headers gjh,
    apps.gl_je_lines gjl,
    apps.gl_code_combinations_kfv gcc,
    apps.gl_import_references gir,
    apps.gl_je_batches gjb,
    apps.po_distributions_all pda
where 1=1
    AND gjh.je_header_id = gjl.je_header_id
    AND gjh.je_header_id = gir.je_header_id
    AND gir.je_header_id = gjl.je_header_id
    AND gir.je_line_num = gjl.je_line_num
    AND gjl.code_combination_id = gcc.code_combination_id
    AND gjb.je_batch_id = gir.je_batch_id  
    AND pda.code_combination_id = gcc.code_combination_id
    AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
    AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
    AND gcc.segment3 in (100603)
    AND gcc.segment2 not in (102)
    and gjh.je_source = 'Cost Management'
    and gjh.je_category = 'Receiving'

GL Query for Payables(source) - Purchase Invoice(category)

SELECT  aia.invoice_num, aia.doc_sequence_value, aia.invoice_date,
         gjl.period_name, xal.accounting_class_code,
         gcc.concatenated_segments transaction_acc_code,
         xal.accounted_dr, xal.accounted_cr, gjh.je_source, gjh.je_category
    FROM apps.gl_je_headers gjh,
         apps.gl_je_lines gjl,
         apps.gl_je_batches gjb,
         apps.gl_import_references gir,
         xla.xla_ae_lines xal,
         xla.xla_ae_headers xah,
         xla.xla_transaction_entities xte,
         apps.ap_invoices_all aia,
         apps.gl_code_combinations_kfv gcc
   WHERE gjh.je_header_id = gjl.je_header_id
     AND gjh.je_header_id = gir.je_header_id
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjb.je_batch_id = gir.je_batch_id
     AND gir.gl_sl_link_id = xal.gl_sl_link_id
     AND xal.ae_header_id = xah.ae_header_id
     AND gjl.code_combination_id = gcc.code_combination_id
     AND xal.application_id = '200'
     AND xah.application_id = '200'
     AND xah.entity_id = xte.entity_id
     AND xte.source_id_int_1 = aia.invoice_id
     AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
     AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
     AND gcc.segment3 in (100603)
     AND gcc.segment2 not in (102)  

GL Query for Inventory(source) - Receiving(category)

SELECT   msib.segment1 item, msib.description, pha.segment1 po_num,
        rsh.receipt_num, hp.party_number, hp.party_name, xal.accounted_dr,
        xal.accounted_cr , gjh.je_source, gjh.je_category
   FROM apps.gl_je_headers gjh,
        apps.gl_je_lines gjl,
        apps.gl_je_batches gjb,
        apps.gl_import_references gir,
        xla.xla_ae_lines xal,
        xla.xla_ae_headers xah,
        xla.xla_transaction_entities xte,
        apps.gmf_rcv_accounting_txns grat,
        apps.rcv_transactions rt,
        apps.po_headers_all pha,
        apps.rcv_shipment_lines rsl,
        apps.rcv_shipment_headers rsh,
        apps.po_vendors po,
        apps.hz_parties hp,
        apps.mtl_system_items_b msib,
        apps.gl_code_combinations gcc,
        apps.mtl_parameters mp
  WHERE gjh.je_header_id = gjl.je_header_id
    AND gjh.je_header_id = gir.je_header_id
    AND gir.je_header_id = gjl.je_header_id
    AND gir.je_line_num = gjl.je_line_num
    AND gjb.je_batch_id = gir.je_batch_id
    AND gjh.je_source = 'Inventory'
    AND gjh.je_category = 'Receiving'
    AND gir.gl_sl_link_id = xal.gl_sl_link_id
    AND xal.ae_header_id = xah.ae_header_id
    AND gjl.code_combination_id = gcc.code_combination_id
    AND xah.entity_id = xte.entity_id
    AND xte.source_id_int_1 = grat.accounting_txn_id
    AND grat.rcv_transaction_id = rt.transaction_id
    AND rsl.shipment_line_id = rt.shipment_line_id
    AND rsl.shipment_header_id = rt.shipment_header_id
    AND rsh.shipment_header_id = rsl.shipment_header_id
    AND rsh.shipment_header_id = rt.shipment_header_id
    AND rsh.vendor_id = po.vendor_id
    AND po.party_id = hp.party_id
    AND rsl.item_id = msib.inventory_item_id
    AND rt.organization_id = msib.organization_id
    AND mp.organization_id = rt.organization_id
    AND pha.po_header_id = rt.po_header_id
    and gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
    and gcc.segment3 in (102099)
    and gcc.segment2  in (000)
    and gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
ORDER BY rsh.receipt_num 

GL Query for Cost Management(source) - Inventory(category)

SELECT msib.segment1, msib.description, mtt.transaction_type_name,
       xal.accounting_class_code, xal.accounted_dr, xal.accounted_cr, gjh.je_source, gjh.je_category
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_je_batches gjb,
       apps.gl_import_references gir,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       xla.xla_transaction_entities xte,
       apps.mtl_material_transactions mmt,
       apps.mtl_system_items_b msib,
       apps.gl_code_combinations_kfv gcc,
       apps.mtl_parameters mp,
       apps.mtl_transaction_types mtt
 WHERE gjh.je_header_id = gjl.je_header_id
   AND gjh.je_header_id = gir.je_header_id
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjb.je_batch_id = gir.je_batch_id
--     AND gjl.period_name = 'AUG-12-13'
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = '707'
   AND xah.application_id = '707'
   AND xte.application_id = '707'
   AND xah.entity_id = xte.entity_id
   AND xte.source_id_int_1 = mmt.transaction_id
--   AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'
   AND mtt.transaction_type_id = mmt.transaction_type_id
   AND mmt.inventory_item_id = msib.inventory_item_id
   AND mmt.organization_id = msib.organization_id
   AND mp.organization_id = mmt.organization_id
        AND gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
     AND gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
     AND gcc.segment3 in (100603)
     AND gcc.segment2 not in (102)
--   AND TRUNC (xal.accounting_date) BETWEEN '01-APR-2012' AND '31-MAR-2013'
   AND gjl.code_combination_id = gcc.code_combination_id
--   AND gcc.segment4 = '3021000'

GL Query for Cost Management(source) - WIP(category)

SELECT   distinct
         gjh.je_source                  as sour
       , gjh.je_category                as categ
       , gjh.doc_sequence_value         as doc
       , gjh.period_name                as period
       , trunc(mmt.transaction_date)    as trans_date
       , msi.segment1                   as item
       , REGEXP_REPLACE (msi.description,
       '([^[:alnum:]])', ' ')           as item_dec
       , mmt.primary_quantity           as qty
--       , xal.accounted_dr, xal.accounted_cr
       , (mta.base_transaction_value)     as amt
       , gcc.segment2                   as activity
       , gcc.segment3                   as dept
       , ffvv.description               as des
       , gcc.segment4                   as acc_code
       , mmt.organization_id            as org
       , mtt.transaction_type_name      as type_name
       , mtt.transaction_type_id        as type_id
       , mmt.transaction_source_id
       , mmt.trx_source_line_id
       , mmt.transaction_id
 FROM apps.gl_je_headers gjh
      , apps.gl_je_lines gjl
      , apps.gl_je_batches gjb
      , apps.gl_import_references gir
      , xla.xla_ae_lines xal
      , xla.xla_ae_headers xah
      , xla.xla_transaction_entities xte
      , apps.gl_code_combinations_kfv gcc
      , apps.wip_transactions wt
      , apps.eam_work_orders_v ewov
      , apps.wip_entities we
      , apps.mtl_parameters mp
      , apps.mtl_material_transactions mmt
      , apps.mtl_system_items msi
      , apps.mtl_transaction_accounts mta
      , apps.mtl_transaction_types mtt
      , apps.fnd_flex_values_vl ffvv
WHERE 1=1
    and gjh.je_header_id = gjl.je_header_id
    and gjh.je_header_id = gir.je_header_id
    and gir.je_header_id = gjl.je_header_id
    and gir.je_line_num = gjl.je_line_num
    and gjb.je_batch_id = gir.je_batch_id
--    and gjh.je_source = 'Cost Management'
    and gjh.je_category = 'WIP'
    and gir.gl_sl_link_id = xal.gl_sl_link_id
    and xal.ae_header_id = xah.ae_header_id
    and gjl.code_combination_id = gcc.code_combination_id
    and xah.entity_id = xte.entity_id
    and xte.source_id_int_1 = wt.transaction_id
    and ewov.wip_entity_id = wt.wip_entity_id
    and wt.wip_entity_id  = we.wip_entity_id
    and ewov.organization_id = mp.organization_id
    and wt.wip_entity_id = mmt.transaction_source_id
    and mmt.inventory_item_id = msi.inventory_item_id
    and mmt.organization_id = msi.organization_id
    and mmt.transaction_id = mta.transaction_id
    and mmt.transaction_type_id = mtt.transaction_type_id
    and ffvv.flex_value = gcc.segment3
    and ffvv.compiled_value_attributes is not null
    and gcc.segment4 in (3021000, 3021100)--, 3021700, 3023000, 3023100)
    and gcc.segment3 in (100603)
    and gcc.segment2 not in (102)
    and to_char(mmt.transaction_date, 'MON-YYYY') between to_char(:p_from_per, 'MON-YYYY') and to_char(:p_to_per, 'MON-YYYY')
    and gjl.period_name in (select distinct period_name from apps.gl_periods where start_date between :p_from_per and :p_to_per)
--    and msi.segment1 = :p_s

Date Format 6 AM TO 6 AM FOR THE FINANCIAL YEAR (FOR AN YEAR) 01-APR-2013 TO 31-MAR-2014

Date BETWEEN   TRUNC
                                       (TO_DATE
                                           (   '01'
                                            || '-'
                                            || 'APR'
                                            || '-'
                                            || TRUNC
                                                  (TO_NUMBER
                                                          (TO_CHAR
                                                                  (t_date,
                                                                   'YYYY'
                                                                  )
                                                          )
                                                  )
                                           ),
                                        'dd'
                                       )
                                  + (6 / 24)   --to_date (01, 'dd') + (6 / 24)
                              AND TRUNC (t_date, 'dd') + (6 / 24) + 1

DATE FORMAT 6AM TO 6AM and 12AM TO 12AM

6AM TO 6AM
(mmt.transaction_date) BETWEEN TO_DATE (
                                                                                           TO_CHAR (
                                                                                              :P_From_date,
                                                                                              'fmdd/mon/yyyy'
                                                                                           )
                                                                                           || ' '
                                                                                           || '06:00:00',
                                                                                           'fmdd/mm/yyyy hh24:mi:ss'
                                                                                        )
                                                                                    AND  TO_DATE (
                                                                                            TO_CHAR (
                                                                                               :P_TO_DATE
                                                                                               + 1,
                                                                                               'fmdd/mm/yyyy'
                                                                                            )
                                                                                            || ' '
                                                                                            || '05:59:59',
                                                                                            'fmdd/mm/yyyy hh24:mi:ss'
                                                                                         )
12AM TO 12AM 

(mmt.transaction_date) BETWEEN TO_DATE (
                                                                                           TO_CHAR (
                                                                                              :P_From_date,
                                                                                              'fmdd/mon/yyyy'
                                                                                           )
                                                                                           || ' '
                                                                                           || '12:00:00',
                                                                                           'fmdd/mm/yyyy hh24:mi:ss'
                                                                                        )
                                                                                    AND  TO_DATE (
                                                                                            TO_CHAR (
                                                                                               :P_TO_DATE
                                                                                               + 1,
                                                                                               'fmdd/mm/yyyy'
                                                                                            )
                                                                                            || ' '
                                                                                            || '23:59:59',
                                                                                            'fmdd/mm/yyyy hh24:mi:ss'
                                                                                         )

(Sample Procedure) Fetch values from base table using cursor and insertion to custom table

CREATE OR REPLACE PROCEDURE APPS.MON_STK_STAT
(
    p_from in date
)

IS

CURSOR dep_cost IS
SELECT cost_cmpntcls_id,
       source_ind,
       trans_date,
       cost_amt,
       trans_qty,
       cmpntcost_id,
       cost_type_id,
       inventory_item_id,
       organization_id,
       period_id
FROM apps.cm_acst_led cal
WHERE
cal.period_id = (SELECT period_id FROM apps.gmf_period_statuses where to_char(START_DATE, 'MON-YYYY') = to_char(p_from, 'MON-YYYY'));

    begin

        for d in dep_cost
            loop
                insert into APPS.MON_STK_DEP_CST values (d.cost_cmpntcls_id,
                                                                   d.source_ind,
                                                                   d.trans_date,
                                                                   d.cost_amt,
                                                                   d.trans_qty,
                                                                   d.cmpntcost_id,
                                                                   d.cost_type_id,
                                                                   d.inventory_item_id,
                                                                   d.organization_id,
                                                                   d.period_id);
                commit;                                                                
            end loop;
    end;


/

Material Transaction Type Wise (/*Po receipt standard and blanket*/) transaction details

/*Po receipt (standard)*/
SELECT distinct REGEXP_REPLACE (msib.description,
                                 '([^[:alnum:]])',
                                 ' '
                                ) AS item_description,
                 rrsl.transaction_date AS trans_date, rt.uom_code AS uom,
                 gcc.segment3, gcc.segment2, msib.segment1 AS item_code,
                 SUBSTR (msib.segment1, 1, 1) AS item_code_series,
                 rt.primary_quantity AS quantity,
                 rt.po_unit_price AS unit_rate,
                 rt.primary_quantity * rt.po_unit_price AS VALUE,
                 gcc.segment4, 'PO Receipt' AS transaction_type_name,
                 ppa.segment1 AS project, pt.task_number AS task,
                 rsh.receipt_num AS reference_number,
                 msib.organization_id AS org_id
--                 tnpl_repair_pomaxdate(rsh.receipt_num) as last_update_date
            FROM apps.po_headers_all pha,
                 apps.po_lines_all pla,
                 po_distributions_all pda,
                 apps.po_line_types_tl pltt,
                 apps.mtl_system_items_b msib,
                 apps.gl_code_combinations gcc,
                 apps.pa_projects_all ppa,
                 apps.pa_tasks pt,
                 apps.rcv_transactions rt,
                 apps.rcv_shipment_headers rsh,
                 apps.rcv_shipment_lines rsl,
                 rcv_receiving_sub_ledger rrsl
           WHERE 1 = 1
--             and rsh.receipt_num=201200001249
             AND pha.authorization_status = 'APPROVED'
             AND pha.approved_flag = 'Y'
             AND pha.type_lookup_code = 'STANDARD'
             AND pha.po_header_id = pla.po_header_id
             AND pda.po_line_id = pla.po_line_id
             AND pda.po_header_id = pha.po_header_id
--             AND pltt.line_type = 'Services'
             AND pda.destination_type_code = 'EXPENSE'
             AND rt.transaction_type = 'DELIVER'
             AND pltt.line_type_id = pla.line_type_id
             AND pla.item_id = msib.inventory_item_id
             AND msib.organization_id = pda.destination_organization_id
             AND gcc.code_combination_id = pda.code_combination_id
             AND gcc.segment4 IN
                    ('3021000', '3021100', '3021200', '3021300', '3021400',
                     '3021500', '3021600', '3021700', '3021610')
             AND pda.project_id = ppa.project_id(+)
             AND pda.task_id = pt.task_id(+)
             AND rsh.shipment_header_id = rsl.shipment_header_id
             AND rsh.shipment_header_id = rt.shipment_header_id
             AND rt.po_header_id = pha.po_header_id
             AND rt.po_line_id = pla.po_line_id
             AND msib.organization_id = rt.organization_id
             AND rsl.item_id = pla.item_id
             AND accounting_line_type = 'Charge'
             AND rt.transaction_id = rrsl.rcv_transaction_id
             AND gcc.code_combination_id = rrsl.code_combination_id
             AND msib.organization_id = '102'
--             and tnpl_repair_pomaxdate(rsh.receipt_num)= rt.last_update_date
             AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
             AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
             AND gcc.segment3 BETWEEN NVL (:p_from_department, gcc.segment3)
                                  AND NVL (:p_to_department, gcc.segment3)
             AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
             AND trunc(rrsl.transaction_date) BETWEEN :p_f_date AND :p_t_date
        ORDER BY msib.segment1

/*Po receipt (blanket)*/

SELECT   REGEXP_REPLACE (msib.description,
                                 '([^[:alnum:]])',
                                 ' '
                                ) AS item_description,
                 rrsl.transaction_date AS trans_date, rt.uom_code AS uom,
                 gcc.segment3, gcc.segment2, msib.segment1 AS item_code,
                 SUBSTR (msib.segment1, 1, 1) AS item_code_series,
                 rt.primary_quantity AS quantity,
                 rt.po_unit_price AS unit_rate,
                 rt.primary_quantity * rt.po_unit_price AS VALUE,
                 gcc.segment4, ppa.segment1 AS project,
                 pt.task_number AS task,
                 'PO Receipt' AS transaction_type_name,
                 rsh.receipt_num AS reference_number,
                 msib.organization_id AS org_id
--                 tnpl_repair_pomaxdate1(rsh.receipt_num) as last_update_date
            FROM po_headers_all pha,
                 po_lines_all pla,
                 po_distributions_all pda,
                 po_releases_all pra,
                 po_line_locations_all plla,
                 po_line_types_tl pltt,
                 pa_projects_all ppa,
                 apps.pa_tasks pt,
                 mtl_system_items_b msib,
                 apps.rcv_transactions rt,
                 apps.rcv_shipment_headers rsh,
                 apps.rcv_shipment_lines rsl,
                 apps.rcv_receiving_sub_ledger rrsl,
                 apps.gl_code_combinations gcc
           WHERE 1 = 1
             AND pha.type_lookup_code = 'BLANKET'
             AND pha.po_header_id = pla.po_header_id
             AND pla.po_line_id = pda.po_line_id
             AND pha.po_header_id = pra.po_header_id
             AND pra.po_release_id = plla.po_release_id
             AND plla.line_location_id = pda.line_location_id
             AND pltt.line_type_id = pla.line_type_id
             AND ppa.project_id(+) = pda.project_id
             AND pt.task_id(+) = pda.task_id
             AND msib.inventory_item_id = pla.item_id
             AND msib.organization_id = pda.destination_organization_id
             AND rt.po_line_location_id = plla.line_location_id
             AND rt.po_distribution_id = pda.po_distribution_id
             AND rt.organization_id = pda.destination_organization_id
             AND rt.shipment_header_id = rsh.shipment_header_id
             AND rt.organization_id = rsh.ship_to_org_id
             AND rt.transaction_type = 'DELIVER'
             AND rsh.shipment_header_id = rsl.shipment_header_id
             AND rsl.destination_type_code = 'EXPENSE'
             AND rt.shipment_line_id = rsl.shipment_line_id
             AND rt.po_release_id = pra.po_release_id
             AND rt.transaction_id = rrsl.rcv_transaction_id
             AND rrsl.code_combination_id = gcc.code_combination_id
             AND rrsl.accounting_line_type = 'Charge'
             AND gcc.code_combination_id = pda.code_combination_id
             AND gcc.segment4 IN
                    ('3021000', '3021100', '3021200', '3021300', '3021400',
                     '3021500', '3021600', '3021700', '3021610')
             AND msib.organization_id = '102'
--             and tnpl_repair_pomaxdate1(rsh.receipt_num)= rt.last_update_date
             AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
             AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
             AND gcc.segment3 BETWEEN NVL (:p_from_department, gcc.segment3)
                                  AND NVL (:p_to_department, gcc.segment3)
             AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
             AND trunc(rrsl.transaction_date) BETWEEN :p_f_date AND :p_t_date
        ORDER BY msib.segment1

Material Transaction Type Wise (/*Other trans (return)*/) transaction details

/*Other trans (return)*/
SELECT DISTINCT mmt.organization_id org_id,
                        (mmt.transaction_date) trans_date,
                        (SUBSTR (msib.segment1, 1, 1)) AS item_code_series,
                        gcc.segment2, gcc.segment3, gcc.segment4,
                        msib.segment1 AS item_code, mtt.transaction_type_name,
                        mta.base_transaction_value AS VALUE,
                        mta.primary_quantity AS quantity,
                        (mta.base_transaction_value / mta.primary_quantity
                        ) AS unit_rate,
                        TO_CHAR (mmt.transaction_id) AS reference_number,
                        REGEXP_REPLACE (msib.description,
                                        '([^[:alnum:]])',
                                        ' '
                                       ) AS item_description,
                        msib.primary_uom_code AS uom, ood.organization_id,
                        ood.organization_code, ood.organization_name,
                        gcc.segment1, gcc.segment5, gcc.segment6,
                        ppa.segment1 AS project, pt.task_number AS task
                   FROM apps.gl_code_combinations gcc,
                        apps.xla_ae_lines xal,
                        apps.xla_ae_headers xah,
                        apps.xla_transaction_entities_upg xteu,
                        apps.mtl_material_transactions mmt,
                        apps.mtl_transaction_accounts mta,
                        apps.mtl_transaction_types mtt,
                        apps.mtl_system_items_b msib,
                        apps.pa_projects_all ppa,
                        apps.pa_tasks pt,
                        apps.org_organization_definitions ood
                  WHERE 1 = 1
                    AND mtt.transaction_type_id NOT IN
                           ('35', '63', '34', '95', '36', '100', '43', '48','27',
                            '17', '37', '13', '14', '23', '39', '100', '1003')
                                                                             --
                    AND mta.primary_quantity >= 0
                    AND gcc.segment4 IN
                           ('3021000', '3021100', '3021200', '3021300',
                            '3021400', '3021500', '3021600', '3021610',
                            '3021700')
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND xal.ae_header_id = xah.ae_header_id
                    AND xah.entity_id = xteu.entity_id
                    AND xteu.source_id_int_1 = mmt.transaction_id
                    AND mta.transaction_id = mmt.transaction_id
                    AND mta.organization_id = mmt.organization_id
                    AND ood.organization_id = mmt.organization_id
                    AND mta.organization_id = ood.organization_id
                    AND mmt.transaction_type_id = mtt.transaction_type_id
                    AND mmt.inventory_item_id = msib.inventory_item_id
                    AND mmt.organization_id = msib.organization_id
                    AND SUBSTR (msib.segment1, 1, 1) IN
                                ('1', '2', '3', '4', '5', '6', '7', '8', '9')
                    AND mmt.project_id = ppa.project_id(+)
                    AND mmt.task_id = pt.task_id(+)
                    AND ood.organization_id not in (102)
                    AND trunc(mmt.transaction_date) BETWEEN :p_f_date AND :p_t_date
                    AND ood.organization_id =
                                 NVL (:p_organization_id, ood.organization_id)
                    AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
                    AND gcc.segment3 BETWEEN NVL (:p_from_department,
                                                  gcc.segment3
                                                 )
                                         AND NVL (:p_to_department,
                                                  gcc.segment3
                                                 )
                    AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
                    AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
               ORDER BY msib.segment1

Material Transaction Type Wise (/*Logical expenses & requsi recpt*/) transaction details

/*Logical expenses & requsi recpt*/
SELECT mmt.organization_id org_id,
--                        (mmt.transaction_date) trans_date,
                        (mmt.transaction_date) trans_date,  
                        mtt.transaction_type_id transaction_id,
                        (SUBSTR (msib.segment1, 1, 1)) AS item_code_series,
                        gcc.segment2, gcc.segment3, gcc.segment4,
                        msib.segment1 AS item_code, mtt.transaction_type_name,
                        mta.base_transaction_value AS VALUE,
                        mta.primary_quantity AS quantity,
                        (mta.base_transaction_value / mta.primary_quantity
                        ) AS unit_rate,
                        prha.segment1 AS reference_number,
                        REGEXP_REPLACE (msib.description,
                                        '([^[:alnum:]])',
                                        ' '
                                       ) AS item_description,
                        msib.primary_uom_code AS uom, ood.organization_id,
                        ood.organization_code, ood.organization_name,
                        gcc.segment1, gcc.segment5, gcc.segment6,
                        ppa.segment1 AS project, pt.task_number AS task
                   FROM apps.po_requisition_lines_all prla,
                        apps.po_requisition_headers_all prha,
                        apps.oe_order_headers_all ooha,
                        apps.oe_order_lines_all oola,
                        apps.gl_code_combinations gcc,
                        apps.xla_ae_lines xal,
                        apps.xla_ae_headers xah,
                        apps.xla_transaction_entities_upg xteu,
                        apps.mtl_material_transactions mmt,
                        apps.mtl_transaction_accounts mta,
                        apps.mtl_transaction_types mtt,
                        apps.mtl_system_items_b msib,
                        apps.pa_projects_all ppa,
                        apps.pa_tasks pt,
                        apps.org_organization_definitions ood
                  WHERE 1 = 1
                    AND prha.requisition_header_id =
                                                    prla.requisition_header_id
                    AND ooha.orig_sys_document_ref = prha.segment1
                    AND oola.source_document_line_id =
                                                      prla.requisition_line_id
                    AND ooha.header_id = oola.header_id
                    AND mta.primary_quantity >= 0
                    AND prla.destination_organization_id = mmt.organization_id
                    AND prha.requisition_header_id =
                             mmt.transaction_source_id
                    AND prla.requisition_line_id = mmt.trx_source_line_id
                    AND gcc.segment4 IN
                           ('3021000', '3021100', '3021200', '3021300',
                            '3021400', '3021500', '3021600', '3021610',
                            '3021700')
                    AND mtt.transaction_type_id  IN ('27')
--                           ('36', '100', '43', '48', '17', '37', '13', '14',
--                            '23', '39', '100', '1003')
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND xal.ae_header_id = xah.ae_header_id
                    AND xah.entity_id = xteu.entity_id
                    AND xteu.source_id_int_1 = mmt.transaction_id
                    AND mta.transaction_id = mmt.transaction_id
                    AND mta.organization_id = mmt.organization_id
                    AND ood.organization_id = mmt.organization_id
                    AND mta.organization_id = ood.organization_id
                    AND mmt.transaction_type_id = mtt.transaction_type_id
                    AND mmt.inventory_item_id = msib.inventory_item_id
                    AND mmt.organization_id = msib.organization_id
                    AND SUBSTR (msib.segment1, 1, 1) IN
                                ('1', '2', '3', '4', '5', '6', '7', '8', '9')
                    AND mmt.project_id = ppa.project_id(+)
                    AND mmt.task_id = pt.task_id(+)
                    AND ood.organization_id not in (102)
                    AND trunc(mmt.transaction_date) BETWEEN :p_f_date AND :p_t_date
                    AND ood.organization_id = NVL (:p_organization_id, ood.organization_id)
                    AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
                    AND gcc.segment3 BETWEEN NVL (:p_from_department,
                                                  gcc.segment3
                                                 )
                                         AND NVL (:p_to_department,
                                                  gcc.segment3
                                                 )
                    AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
                    AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
               ORDER BY msib.segment1

Material Transaction Type Wise (/*Internal requ*/) transaction details

/*Internal requ*/
SELECT mmt.organization_id org_id,
                        (mmt.transaction_date) trans_date,
                        mtt.transaction_type_id transaction_id,
                        (SUBSTR (msib.segment1, 1, 1)) AS item_code_series,
                        gcc.segment2, gcc.segment3, gcc.segment4,
                        msib.segment1 AS item_code, mtt.transaction_type_name,
                        mta.base_transaction_value AS VALUE,
                        mta.primary_quantity AS quantity,
                        (mta.base_transaction_value / mta.primary_quantity
                        ) AS unit_rate,
                        prha.segment1 AS reference_number,
                        REGEXP_REPLACE (msib.description,
                                        '([^[:alnum:]])',
                                        ' '
                                       ) AS item_description,
                        msib.primary_uom_code AS uom, ood.organization_id,
                        ood.organization_code, ood.organization_name,
                        gcc.segment1, gcc.segment5, gcc.segment6,
                        ppa.segment1 AS project, pt.task_number AS task
                   FROM apps.po_requisition_lines_all prla,
                        apps.po_requisition_headers_all prha,
                        apps.oe_order_headers_all ooha,
                        apps.oe_order_lines_all oola,
                        apps.gl_code_combinations gcc,
                        apps.xla_ae_lines xal,
                        apps.xla_ae_headers xah,
                        apps.xla_transaction_entities_upg xteu,
                        apps.mtl_material_transactions mmt,
                        apps.mtl_transaction_accounts mta,
                        apps.mtl_transaction_types mtt,
                        apps.mtl_system_items_b msib,
                        apps.pa_projects_all ppa,
                        apps.pa_tasks pt,
                        apps.org_organization_definitions ood
                  WHERE 1 = 1
                    AND prha.requisition_header_id =
                                                    prla.requisition_header_id
                    AND ooha.orig_sys_document_ref = prha.segment1
                    AND oola.source_document_line_id =
                                                      prla.requisition_line_id
                    AND ooha.header_id = oola.header_id
                    AND mta.primary_quantity >= 0
                    AND mtt.transaction_type_id = 95
                    AND prla.destination_organization_id = mmt.organization_id
                    AND prha.requisition_header_id =
                             mmt.transaction_source_id
                    AND prla.requisition_line_id = mmt.trx_source_line_id
                    AND gcc.segment4 IN
                           ('3021000', '3021100', '3021200', '3021300',
                            '3021400', '3021500', '3021600', '3021610',
                            '3021700')
                    AND gcc.code_combination_id = xal.code_combination_id
                    AND xal.ae_header_id = xah.ae_header_id
                    AND xah.entity_id = xteu.entity_id
                    AND xteu.source_id_int_1 = mmt.transaction_id
                    AND mta.transaction_id = mmt.transaction_id
                    AND mta.organization_id = mmt.organization_id
                    AND ood.organization_id = mmt.organization_id
                    AND mta.organization_id = ood.organization_id
                    AND mmt.transaction_type_id = mtt.transaction_type_id
                    AND mmt.inventory_item_id = msib.inventory_item_id
                    AND mmt.organization_id = msib.organization_id
                    AND SUBSTR (msib.segment1, 1, 1) IN
                                ('1', '2', '3', '4', '5', '6', '7', '8', '9')
                    AND mmt.project_id = ppa.project_id(+)
                    AND mmt.task_id = pt.task_id(+)
                    AND ood.organization_id not in (102)
                    AND trunc(mmt.transaction_date) BETWEEN :p_f_date AND :p_t_date
                    AND ood.organization_id =
                                 NVL (:p_organization_id, ood.organization_id)
                    AND gcc.segment2 = NVL (:p_cost_center, gcc.segment2)
                    AND gcc.segment3 BETWEEN NVL (:p_from_department,
                                                  gcc.segment3
                                                 )
                                         AND NVL (:p_to_department,
                                                  gcc.segment3
                                                 )
                    AND gcc.segment4 = NVL (:p_account_code, gcc.segment4)
                    AND SUBSTR (msib.segment1, 1, 1) =
                            NVL (:p_item_series, SUBSTR (msib.segment1, 1, 1))
               ORDER BY msib.segment1