Oracle Installation Issue Recovering

    1)     Open control panel

    2)    User account and open change user account  control settings

    3)    Make it as Never notify

    4)    Open system properties by Right clicking on my computer

    5)    Open advanced system settings

    6)    Go to advanced tab

    7)    Open performance settings 

    8)    Go to advanced tab and open change

    9)    Uncheck automatically manage…………..

    10)  Set initial size and maximum size as 600 – 1000 (depends on system memory also)

    11)   Restart the system (Recommanded)

    12)  Right click on setup file and make windows service pack 2 for compatibility 

BI Publisher error: object library invalid or contains references to object definitions that could not be found

Error : (object library invalid or contains references to object definitions that could not be found)

Method 1

Note In this procedure, the folders that are mentioned may or may not exist, depending on which application uses the custom solution and whether the custom solution is used on a document or in a UserForm.

1.     Exit all instances of Microsoft Excel.

2.     Click Start, type %temp% in the Open box, and then press Enter.

3.     In the folder that opens, delete the Excel8.0 folder and the VBE folder.

4.     Click Start, type %appdata%\microsoft\forms in the Open box, and then press Enter.

5.     In the folder that opens, delete the MSComctlLib.exd file.

6.     Close all the instances of Windows Explorer that you opened.

Method 2

1.     Click Start, type cmd in the Open box, and then press Enter.

2.     Type the following commands at the command prompt, and press Enter after each command:

Note You can safely ignore any results that say "Could Not/cannot find."

del %temp%\vbe\MSComctlLib.exd
del %temp%\excel8.0\MSComctlLib.exd
del %appdata%\microsoft\forms\MSComctlLib.exd
del %temp%\word8.0\MSComctlLib.exd
del %temp%\ppt11.0\MSComctlLib.exd
del %temp%\ppt12.0\MSComctlLib.exd
del %temp%\ppt14.0\MSComctlLib.exd

Note An administrator can use a script that contains these commands to delete the unnecessary files. For example, an administrator can copy the commands to a text file, save the file as a .bat file (for example, Scriptfile.bat), and then double-click the file to run it.

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