, , , , , , ,

Re-Compile Invalid Schema Objects in Oracle Database

Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don’t cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.

Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects

The DBA_OBJECTS view can be used to identify invalid objects using the following query.

COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = ‘INVALID’
ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.

The Manual Approach

For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types.

ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word “BODY” at the end of the command.

An alternative approach is to use the DBMS_DDL package to perform the recompilations.

EXEC DBMS_DDL.alter_compile(‘PACKAGE’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PACKAGE BODY’, ‘MY_SCHEMA’, ‘MY_PACKAGE’);
EXEC DBMS_DDL.alter_compile(‘PROCEDURE’, ‘MY_SCHEMA’, ‘MY_PROCEDURE’);
EXEC DBMS_DDL.alter_compile(‘FUNCTION’, ‘MY_SCHEMA’, ‘MY_FUNCTION’);
EXEC DBMS_DDL.alter_compile(‘TRIGGER’, ‘MY_SCHEMA’, ‘MY_TRIGGER’);

This method is limited to PL/SQL objects, so it is not applicable for views.

Custom Script

In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.

SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT owner,
                         object_name,
                         object_type,
                         DECODE(object_type, ‘PACKAGE’, 1,
                                             ‘PACKAGE BODY’, 2, 2) AS recompile_order
                  FROM   dba_objects
                  WHERE  object_type IN (‘PACKAGE’, ‘PACKAGE BODY’)
                  AND    status != ‘VALID’
                  ORDER BY 4)
  LOOP
    BEGIN
      IF cur_rec.object_type = ‘PACKAGE’ THEN
        EXECUTE IMMEDIATE ‘ALTER ‘ || cur_rec.object_type ||
            ‘ “‘ || cur_rec.owner || ‘”.”‘ || cur_rec.object_name || ‘” COMPILE’;
      ElSE
        EXECUTE IMMEDIATE ‘ALTER PACKAGE “‘ || cur_rec.owner ||
            ‘”.”‘ || cur_rec.object_name || ‘” COMPILE BODY’;
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line(cur_rec.object_type || ‘ : ‘ || cur_rec.owner ||
                             ‘ : ‘ || cur_rec.object_name);
    END;
  END LOOP;
END;
/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.

DBMS_UTILITY.compile_schema

The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus.

EXEC DBMS_UTILITY.compile_schema(schema => ‘SCOTT’);
UTL_RECOMP

The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below.

 PROCEDURE RECOMP_SERIAL(
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

PROCEDURE RECOMP_PARALLEL(
   threads  IN   PLS_INTEGER DEFAULT NULL,
   schema   IN   VARCHAR2    DEFAULT NULL,
   flags    IN   PLS_INTEGER DEFAULT 0);

The usage notes for the parameters are listed below.
schema – The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads – The number of threads used in a parallel operation. If NULL the value of the “job_queue_processes” parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags – Used for internal diagnostics and testing only.

The following examples show how these procedures are used.
— Schema level.
EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);
EXEC UTL_RECOMP.recomp_parallel(4, ‘SCOTT’);

— Database level.
EXEC UTL_RECOMP.recomp_serial();
EXEC UTL_RECOMP.recomp_parallel(4);

— Using job_queue_processes value.
EXEC UTL_RECOMP.recomp_parallel();
EXEC UTL_RECOMP.recomp_parallel(NULL, ‘SCOTT’);

There are a number of restrictions associated with the use of this package including:

Parallel execution is performed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Running DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql

The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of “0”. The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows.

0 – The level of parallelism is derived based on the CPU_COUNT parameter.
1 – The recompilation is run serially, one object at a time.
N – The recompilation is run in parallel with “N” number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

For more information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
/by
, , , ,

Opm Accounting Preprocessor debugging scripts in R12

Orgn: &&organization_id 
==================
SELECT * FROM mtl_parameters WHERE organization_id = &&organization_id;

Item details FOR Item: &&inv_item_id 
=========================
SELECT *
FROM mtl_system_items_kfv
WHERE organization_id =
  &&organization_id
AND inventory_item_id =
  &&inv_item_id;

Cost Type details FOR Cost Type: &&cost_type
=================================
SELECT * FROM cm_mthd_mst WHERE cost_mthd_code = ‘&&cost_type’;

Period Balances FOR Item: &&inv_item_id Orgn: &&organization_id 
==============================================
SELECT pbal.inventory_item_id AS item_id,
  pbal.primary_quantity       AS quantity,
  pbal.organization_id        AS mtl_organization_id,
  pbal.period_balance_id      AS perd_bal_id,
  NVL(pbal.lot_number,’ ‘),
  pbal.subinventory_code,
  pbal.locator_id,
  pbal.costed_flag,
  pbal.acct_period_id
FROM org_acct_periods oap,
  gmf_period_balances pbal,
  gmf_fiscal_policies gfp,
  gl_ledgers gl,
  hr_organization_information hoi
WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND oap.schedule_close_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND hoi.organization_id =
  &&organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND gfp.legal_entity_id         = hoi.org_information2
AND gl.ledger_id                = gfp.ledger_id
AND oap.period_set_name         = gl.period_set_name
AND oap.organization_id         = pbal.organization_id
AND pbal.acct_period_id         = oap.acct_period_id
AND pbal.inventory_item_id      =
  &&inv_item_id
AND pbal.organization_id =
  &&organization_id ;
  
Item Costs 
==========
SELECT c.inventory_item_id ,
  c.organization_id ,
  c.period_id ,
  TO_CHAR (end_date, ‘MM/DD/YYYY hh24:mi:ss’) perd_end_date ,
  m.cost_mthd_code ,
  c.cost_type_id ,
  c.acctg_cost ,
  c.itemcost_id ,
  d.cost_cmpntcls_id ,
  d.cost_analysis_code ,
  d.cmptcost_amt
FROM gl_item_cst c,
  gl_item_dtl d,
  cm_mthd_mst m
WHERE d.itemcost_id     = c.itemcost_id
AND c.inventory_item_id =
  &&inv_item_id
AND c.organization_id =
  &&organization_id
AND c.cost_type_id   = m.cost_type_id
AND m.cost_mthd_code = ‘&&cost_type’
ORDER BY c.end_date DESC ;

Actual Cost Adjs 
============
SELECT d.inventory_item_id,
  d.organization_id,
  d.cost_cmpntcls_id,
  d.cost_analysis_code,
  d.cost_adjust_id,
  d.adjust_qty,
  d.adjust_qty_uom,
  d.adjust_cost,
  d.reason_code,
  NVL(d.adjustment_ind, DECODE(d.adjust_qty, 0, 1, 0)) adjustment_ind,
  TO_CHAR(d.adjustment_date,’MM/DD/YYYY hh24:mi:ss’),
  cmpt.usage_ind,
  d.adjust_status,
  d.subledger_ind,
  d.delete_mark
FROM cm_adjs_dtl d,
  cm_cmpt_mst cmpt,
  cm_mthd_mst m
WHERE d.inventory_item_id =
  &&inv_item_id
AND d.organization_id =
  &&organization_id
AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
AND d.cost_type_id        = m.cost_type_id
AND m.cost_mthd_code      = ‘&&cost_type’
AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND d.adjustment_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
ORDER BY d.inventory_item_id,
  d.organization_id,
  d.adjustment_date,
  d.cost_adjust_id ;
  
Lot Cost Adjs 
==========
SELECT lca.inventory_item_id,
  lca.lot_number,
  lca.reason_code,
  TO_CHAR(lca.adjustment_date,’MM/DD/YYYY hh24:mi:ss’) adjustment_date,
  lca.adjustment_id,
  lcad.cost_cmpntcls_id,
  lcad.cost_analysis_code,
  (NVL(lcad.adjustment_cost,0) * NVL(lca.onhand_qty,0)) delta_amount,
  lca.onhand_qty,
  lca.organization_id,
  lcad.adjustment_cost,
  cmpt.usage_ind ,
  lca.old_cost_header_id ,
  lca.new_cost_header_id ,
  lca.gl_posted_ind ,
  lca.applied_ind
FROM gmf_lot_cost_adjustment_dtls lcad,
  gmf_lot_cost_adjustments lca,
  cm_cmpt_mst cmpt,
  cm_mthd_mst m
WHERE lca.cost_type_id   = m.cost_type_id
AND m.cost_mthd_code     = ‘&&cost_type’
AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND lca.adjustment_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND lcad.adjustment_id    = lca.adjustment_id
AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
AND lca.inventory_item_id =
  &&inv_item_id
AND lca.organization_id =
  &&organization_id
ORDER BY 1,
  12,
  3,
  4,
  6,
  7,
  8 ;
  
GL Expense Allocation 
===============
SELECT gps.calendar_code,
  gps.period_code,
  gps.cost_type_id,
  ‘ ‘ dtl_cost_mthd_code,
  bas.inventory_item_id AS item_id,
  bas.whse_code,
  bas.cmpntcls_id,
  cmpt.cost_cmpntcls_code,
  bas.analysis_code,
  dtl.allocated_expense_amt,
  NVL(dtl.period_qty,0),
  bas.organization_id,
  dtl.period_id,
  dtl.alloc_id,
  dtl.line_no,
  dtl.allocdtl_id,
  cmpt.usage_ind ,
  mst.alloc_code ,
  dtl.ac_status ,
  dtl.delete_mark
FROM gl_aloc_dtl dtl,
  gl_aloc_bas bas,
  gl_aloc_mst mst,
  gmf_period_statuses gps,
  cm_cmpt_mst cmpt ,
  hr_organization_information hoi ,
  cm_mthd_mst m
WHERE mst.legal_entity_id       = hoi.org_information2
AND hoi.organization_id         = bas.organization_id
AND hoi.org_information_context = ‘Accounting Information’
AND bas.inventory_item_id       =
  &&inv_item_id
AND bas.organization_id =
  &&organization_id
AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
AND dtl.alloc_id          = mst.alloc_id
AND dtl.alloc_id          = bas.alloc_id
AND dtl.line_no           = bas.line_no
AND dtl.cost_type_id      = gps.cost_type_id
AND gps.period_id         = dtl.period_id
AND gps.start_date       >= TO_DATE(‘&&datefrom’
  ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.end_date <= TO_DATE(‘&&dateto’
  ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
AND gps.legal_entity_id= hoi.org_information2
AND gps.cost_type_id   = m.cost_type_id
AND m.cost_mthd_code   = ‘&&cost_type’
AND gps.delete_mark    = 0
ORDER BY dtl.period_id,
  dtl.cost_type_id,
  bas.inventory_item_id,
  bas.organization_id,
  bas.cmpntcls_id,
  bas.analysis_code ;
  
Extract Header (COSTREVAL) 
====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
  (SELECT pbal.period_balance_id
  FROM org_acct_periods oap,
    gmf_period_balances pbal,
    gmf_fiscal_policies gfp,
    gl_ledgers gl,
    hr_organization_information hoi
  WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
  AND oap.schedule_close_date <= TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
  AND hoi.organization_id      =
    &&organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND gfp.legal_entity_id         = hoi.org_information2
  AND gl.ledger_id                = gfp.ledger_id
  AND oap.period_set_name         = gl.period_set_name
  AND oap.organization_id         = pbal.organization_id
  AND pbal.acct_period_id         = oap.acct_period_id
  AND pbal.inventory_item_id      =
    &&inv_item_id
  AND pbal.organization_id =
    &&organization_id
  );

Extract Lines (COSTREVAL) 
==================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
  (SELECT pbal.period_balance_id
  FROM org_acct_periods oap,
    gmf_period_balances pbal,
    gmf_fiscal_policies gfp,
    gl_ledgers gl,
    hr_organization_information hoi
  WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
  AND oap.schedule_close_date <= TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
  AND hoi.organization_id      =
    &&organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND gfp.legal_entity_id         = hoi.org_information2
  AND gl.ledger_id                = gfp.ledger_id
  AND oap.period_set_name         = gl.period_set_name
  AND oap.organization_id         = pbal.organization_id
  AND pbal.acct_period_id         = oap.acct_period_id
  AND pbal.inventory_item_id      =
    &&inv_item_id
  AND pbal.organization_id =
    &&organization_id
  );
  
SLA Events (COSTREVAL) 
=================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘COSTREVAL’
AND geh.transaction_id IN
  (SELECT pbal.period_balance_id
  FROM org_acct_periods oap,
    gmf_period_balances pbal,
    gmf_fiscal_policies gfp,
    gl_ledgers gl,
    hr_organization_information hoi
  WHERE oap.period_start_date >= TO_DATE(‘&&datefrom’,’MM/DD/YYYY’)
  AND oap.schedule_close_date <= TO_DATE(‘&&dateto’,’MM/DD/YYYY’)
  AND hoi.organization_id      =
    &&organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND gfp.legal_entity_id         = hoi.org_information2
  AND gl.ledger_id                = gfp.ledger_id
  AND oap.period_set_name         = gl.period_set_name
  AND oap.organization_id         = pbal.organization_id
  AND pbal.acct_period_id         = oap.acct_period_id
  AND pbal.inventory_item_id      =
    &&inv_item_id
  AND pbal.organization_id =
    &&organization_id
  );
  
Extract Header (ACTCOSTADJ) 
=======================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
  (SELECT d.cost_adjust_id
  FROM cm_adjs_dtl d,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE d.inventory_item_id =
    &&inv_item_id
  AND d.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
  AND d.cost_type_id        = m.cost_type_id
  AND m.cost_mthd_code      = ‘&&cost_type’
  AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND d.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  );
  
Extract Lines (ACTCOSTADJ) 
===================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
  (SELECT d.cost_adjust_id
  FROM cm_adjs_dtl d,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE d.inventory_item_id =
    &&inv_item_id
  AND d.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
  AND d.cost_type_id        = m.cost_type_id
  AND m.cost_mthd_code      = ‘&&cost_type’
  AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND d.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  );
  
Sla Events (ACTCOSTADJ) 
====================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘ACTCOSTADJ’
AND geh.transaction_id IN
  (SELECT d.cost_adjust_id
  FROM cm_adjs_dtl d,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE d.inventory_item_id =
    &&inv_item_id
  AND d.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = d.cost_cmpntcls_id
  AND d.cost_type_id        = m.cost_type_id
  AND m.cost_mthd_code      = ‘&&cost_type’
  AND d.adjustment_date    >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND d.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  );
  
Extract Header (LOTCOSTADJ) 
=====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
  (SELECT lca.adjustment_id
  FROM gmf_lot_cost_adjustment_dtls lcad,
    gmf_lot_cost_adjustments lca,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE lca.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code     = ‘&&cost_type’
  AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND lca.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND lcad.adjustment_id    = lca.adjustment_id
  AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
  AND lca.inventory_item_id =
    &&inv_item_id
  AND lca.organization_id =
    &&organization_id
  );
  
Extract Lines (LOTCOSTADJ) 
===================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
  (SELECT lca.adjustment_id
  FROM gmf_lot_cost_adjustment_dtls lcad,
    gmf_lot_cost_adjustments lca,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE lca.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code     = ‘&&cost_type’
  AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND lca.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND lcad.adjustment_id    = lca.adjustment_id
  AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
  AND lca.inventory_item_id =
    &&inv_item_id
  AND lca.organization_id =
    &&organization_id
  );
  
SLA Events (LOTCOSTADJ) 
==================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘LOTCOSTADJ’
AND geh.transaction_id IN
  (SELECT lca.adjustment_id
  FROM gmf_lot_cost_adjustment_dtls lcad,
    gmf_lot_cost_adjustments lca,
    cm_cmpt_mst cmpt,
    cm_mthd_mst m
  WHERE lca.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code     = ‘&&cost_type’
  AND lca.adjustment_date >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND lca.adjustment_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND lcad.adjustment_id    = lca.adjustment_id
  AND cmpt.cost_cmpntcls_id = lcad.cost_cmpntcls_id
  AND lca.inventory_item_id =
    &&inv_item_id
  AND lca.organization_id =
    &&organization_id
  );
  
Extract Header (GLCOSTALOC) 
=====================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
  (SELECT dtl.allocdtl_id
  FROM gl_aloc_dtl dtl,
    gl_aloc_bas bas,
    gl_aloc_mst mst,
    gmf_period_statuses gps,
    cm_cmpt_mst cmpt ,
    hr_organization_information hoi ,
    cm_mthd_mst m
  WHERE mst.legal_entity_id       = hoi.org_information2
  AND hoi.organization_id         = bas.organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND bas.inventory_item_id       =
    &&inv_item_id
  AND bas.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
  AND dtl.alloc_id          = mst.alloc_id
  AND dtl.alloc_id          = bas.alloc_id
  AND dtl.line_no           = bas.line_no
  AND dtl.cost_type_id      = gps.cost_type_id
  AND gps.period_id         = dtl.period_id
  AND gps.start_date       >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.end_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.legal_entity_id= hoi.org_information2
  AND gps.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code   = ‘&&cost_type’
  AND gps.delete_mark    = 0
  );
  
Extract Lines (GLCOSTALOC) 
====================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id     = geh.header_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
  (SELECT dtl.allocdtl_id
  FROM gl_aloc_dtl dtl,
    gl_aloc_bas bas,
    gl_aloc_mst mst,
    gmf_period_statuses gps,
    cm_cmpt_mst cmpt ,
    hr_organization_information hoi ,
    cm_mthd_mst m
  WHERE mst.legal_entity_id       = hoi.org_information2
  AND hoi.organization_id         = bas.organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND bas.inventory_item_id       =
    &&inv_item_id
  AND bas.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
  AND dtl.alloc_id          = mst.alloc_id
  AND dtl.alloc_id          = bas.alloc_id
  AND dtl.line_no           = bas.line_no
  AND dtl.cost_type_id      = gps.cost_type_id
  AND gps.period_id         = dtl.period_id
  AND gps.start_date       >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.end_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.legal_entity_id= hoi.org_information2
  AND gps.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code   = ‘&&cost_type’
  AND gps.delete_mark    = 0
  );
  
SLA Events (GLCOSTALOC) 
==================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE xe.event_id       = geh.event_id
AND geh.entity_code     = ‘REVALUATION’
AND geh.event_type_code = ‘GLCOSTALOC’
AND geh.transaction_id IN
  (SELECT dtl.allocdtl_id
  FROM gl_aloc_dtl dtl,
    gl_aloc_bas bas,
    gl_aloc_mst mst,
    gmf_period_statuses gps,
    cm_cmpt_mst cmpt ,
    hr_organization_information hoi ,
    cm_mthd_mst m
  WHERE mst.legal_entity_id       = hoi.org_information2
  AND hoi.organization_id         = bas.organization_id
  AND hoi.org_information_context = ‘Accounting Information’
  AND bas.inventory_item_id       =
    &&inv_item_id
  AND bas.organization_id =
    &&organization_id
  AND cmpt.cost_cmpntcls_id = bas.cmpntcls_id
  AND dtl.alloc_id          = mst.alloc_id
  AND dtl.alloc_id          = bas.alloc_id
  AND dtl.line_no           = bas.line_no
  AND dtl.cost_type_id      = gps.cost_type_id
  AND gps.period_id         = dtl.period_id
  AND gps.start_date       >= TO_DATE(‘&&datefrom’
    ||’ 00:00:00′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.end_date <= TO_DATE(‘&&dateto’
    ||’ 23:59:59′,’MM/DD/YYYY hh24:mi:ss’)
  AND gps.legal_entity_id= hoi.org_information2
  AND gps.cost_type_id   = m.cost_type_id
  AND m.cost_mthd_code   = ‘&&cost_type’
  AND gps.delete_mark    = 0
  );
  
ORDER MANAGEMENT RELATED: 
ORDER header info FOR sales ORDER NUMBER: &ord_num 
=====================================
SELECT o.name operating_unit,
  h.header_id,
  h.ship_from_org_id inv_org,
  h.order_type_id,
  h.flow_status_code status,
  h.open_flag,
  booked_flag,
  h.cancelled_flag,
  h.ordered_date ord_dt
FROM oe_order_headers_all h,
  hr_operating_units o
WHERE header_id =
  &hid
AND o.organization_id                                                    = h.org_id;

ORDER line details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
==================================================
SELECT rtrim(l.line_number
  ||’.’
  || l.shipment_number
  ||’.’
  || l.option_number
  ||’.’
  || l.component_number
  ||’.’
  || l.service_number, ‘.’) line_num,
  l.line_id,
  l.split_from_line_id,
  l.split_by,
  l.flow_status_code status,
  l.source_document_type_id,
  l.org_id,
  l.ordered_item item,
  l.ordered_quantity qty1,
  l.order_quantity_uom um1,
  l.ordered_quantity2 qty2,
  l.ordered_quantity_uom2 um2,
  l.shipped_quantity sqty,
  l.shipping_quantity sgqty,
  l.ship_tolerance_above sta,
  l.ship_tolerance_below stb,
  l.fulfilled_flag ful_flag,
  l.shipping_interfaced_flag ship_int_flag,
  l.fulfilled_quantity fqty,
  l.invoiced_quantity invoice_qty,
  l.invoice_interface_status_code inv_int_sta_code,
  l.open_flag,
  l.booked_flag,
  l.cancelled_quantity cqty,
  l.cancelled_flag,
  l.inventory_item_id,
  l.ship_from_org_id,
  w.organization_code,
  w.process_enabled_flag,–w.loct_ctl wlc,
  m.lot_divisible_flag,
  m.tracking_quantity_ind,
  m.dual_uom_control dualum_ind,
  m.secondary_default_ind,
  m.primary_uom_code uom,
  m.secondary_uom_code uom2,
  m.dual_uom_deviation_high,
  m.dual_uom_deviation_low,
  m.lot_control_code lot_ctl,
  m.child_lot_flag sublot_ctl,
  m.location_control_code loct_ctl,
  m.grade_control_flag grade_ctl,
  m.lot_status_enabled status_ctl,
  –i.lot_indivisible, i.noninv_ind, i.dualum_ind, i.lot_ctl, i.sublot_ctl, i.loct_ctl,
  –i.grade_ctl,i.status_ctl,
  DECODE(m.ont_pricing_qty_source,’P’,’Primary’,’Secondary’) ont_pricing_qty_source,
  l.source_document_line_id sdli,
  l.source_type_code src_type,
  ott.name line_type,
  TO_CHAR(l.creation_date,’dd-mon-yyyy hh24:mi:ss’) cr_dt,
  TO_CHAR(l.last_update_date,’dd-mon-yyyy hh24:mi:ss’) upd_dt
FROM oe_order_lines_all l,
  mtl_parameters w,
  mtl_system_items_b m,
  oe_transaction_types_tl ott
WHERE l.header_id =
  &hid
AND l.line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
AND l.ship_from_org_id  = w.organization_id
AND l.inventory_item_id = m.inventory_item_id
AND l.ship_from_org_id  = m.organization_id
AND l.line_type_id      = ott.transaction_type_id
AND ott.language        =
  ( SELECT fl.language_code FROM fnd_languages fl WHERE fl.installed_flag = ‘B’
  )
ORDER BY l.line_id;

Delivery line details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
====================================================
SELECT source_line_number,
  source_line_id,
  delivery_detail_id,
  split_from_delivery_detail_id,
  move_order_line_id,
  DECODE(rele ased_status,’R’,’R-Ready for rel’,’S’,’S-Rel to whse’,’Y’,’Y-Staged’, ‘C’,’C-Shipped’,’B’,’B-Backordered’,’D’,’D-Cancelled’,released_status) rel_sts,
  requested_quantity,
  requested_quantity_uom,
  requested_quantity2,
  requested_quantity_uom2,
  picked_quantity,
  picked_quantity2,
  shipped_quantity,
  shipped_quantity2,
  lot_number lotno,
  sublot_number slotno,
  oe_interfaced_flag oeif,
  inv_interfaced_flag invif,
  ship_tolerance_above sta,
  ship_tolerance_below stb,
  ship_set_id,
  inventory_item_id,
  src_requested_quantity,
  src_requested_quantity_uom,
  src_requested_quantity2,
  src_requested_quantity_uom2,
  organization_id inv_org,
  cancelled_quantity,
  cancelled_quantity2,
  delivered_quantity,
  delivered_quantity2,
  TO_CHAR(creation_date,’dd-mon-yyyy hh24:mi:ss’) cr_dt,
  TO_CHAR(last_update_date,’dd-mon-yyyy hh24:mi:ss’) upd_dt
FROM wsh_delivery_details
WHERE source_header_id =
  &hid
AND source_line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
ORDER BY source_line_id,
  delivery_detail_id;
  
Reservation details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
====================================================
SELECT mso.sales_order_id,
  typ.name
INTO :sales_ord_id,
  :ord_type_name
FROM mtl_sales_orders mso,
  oe_order_headers_all ord,
  oe_transaction_types_tl typ
WHERE ord.header_id =
  &hid
AND ord.order_type_id = typ.transaction_type_id
AND language          =
  ( SELECT fl.language_code FROM fnd_languages fl WHERE fl.installed_flag = ‘B’
  )
AND mso.segment1 =
  &ord_num
AND typ.name = mso.segment2;
dbms_output.put_line(‘sales order id order number order type ‘);
dbms_output.put_line(‘——————————————————————‘);
dbms_output.put_line(rpad(TO_CHAR(:sales_ord_id), 16, ‘ ‘)||rpad(‘&ord_num’, 20, ‘ ‘)||:ord_type_name);
END;
/
SELECT res.reservation_id reserv_id,
  DECODE(res.ship_ready_flag, 1,’1=released’, 2,’2=submitted’, TO_CHAR(res.ship_ready_flag)) ship_ready,
  res.demand_source_header_id ds_head_id,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  res.demand_source_line_id ds_line_id,
  res.primary_reservation_quantity res_q,
  res.primary_uom_code uom,
  res.secondary_reservation_quantity sec_res_q,
  inv_convert.inv_um_convert( res.inventory_item_id, res.lot_number, res.organization_id, 5, res.primary_reservation_quantity, res.primary_uom_code, res.secondary_uom_code, NULL, NULL) calc_sec_res_q,
  res.secondary_uom_code uom2,
  res.lot_number lot_num,
  res.organization_id orgn_id,
  res.subinventory_code subinv,
  — res.revision rev,
  res.locator_id loc_id,
  res.detailed_quantity dtl_q,
  res.secondary_detailed_quantity sec_dtl_q,
  res.inventory_item_id inventory_item_id,
  itm.segment1 item,
  res.requirement_date requird_d,
  res.demand_source_delivery ds_deliv,
  res.demand_source_type_id ds_type,
  — res.serial_number serial_num,
  res.supply_source_header_id ss_header_id,
  res.supply_source_line_id ss_source_line,
  res.supply_source_line_detail ss_source_line_det
  –enable_timestamp ,to_char(res.creation_date,’dd-mon hh24:mi:ss’) create_dt
  –enable_timestamp ,to_char(res.last_update_date,’dd-mon hh24:mi:ss’) update_dt
  –enable_timestamp ,res.request_id request_id
FROM mtl_reservations res,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE res.demand_source_header_id          = :sales_ord_id
AND res.demand_source_type_id             IN (2,8,9,21,22)
AND res.demand_source_line_id              = lin.line_id(+)
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id)
AND res.organization_id                    = itm.organization_id(+)
AND res.inventory_item_id                  = itm.inventory_item_id(+)
ORDER BY NVL(lin.top_model_line_id, lin.line_id),
  NVL(lin.ato_line_id, lin.line_id),
  NVL(lin.sort_order, ‘0000’),
  NVL(lin.link_to_line_id, lin.line_id),
  NVL(lin.source_document_line_id, lin.line_id),
  lin.line_id,
  res.reservation_id;
  
Allocation details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
==================================================
SELECT tmp.transaction_temp_id mtl_trns_id,
  tmp.move_order_line_id move_line_id,
  itm.segment1 item,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  lin.line_id line_id,
  tmp.primary_quantity prm_q,
  tmp.secondary_transaction_quantity sec_q,
  inv_convert.inv_um_convert( tmp.inventory_item_id, 5, tmp.primary_quantity, itm.primary_uom_code, tmp.secondary_uom_code, NULL, NULL) tmp_cal_sec_q,
  lot.primary_quantity lot_prm_q,
  lot.secondary_quantity lot_sec_q,
  inv_convert.inv_um_convert( tmp.inventory_item_id, lot.lot_number, tmp.organization_id, 5, NVL(lot.primary_quantity,0), itm.primary_uom_code, tmp.secondary_uom_code, NULL, NULL) lot_cal_sec_q,
  tmp.secondary_uom_code uom2,
  lot.lot_number lot_num,
  tmp.subinventory_code from_sub,
  tmp.locator_id from_loc_id,
  tmp.pick_slip_number pick_slip,
  tmp.transfer_subinventory to_sub,
  tmp.transfer_to_location to_loc_id,
  tmp.process_flag process,
  tmp.lock_flag lck,
  tmp.transaction_mode trans_mode,
  tmp.error_code error_code,
  tmp.error_explanation error_expl
FROM mtl_material_transactions_temp tmp,
  mtl_transaction_lots_temp lot,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE tmp.demand_source_line   = lin.line_id
AND lin.line_category_code     = ‘ORDER’
AND lin.ship_from_org_id       = itm.organization_id(+)
AND lin.inventory_item_id      = itm.inventory_item_id(+)
AND lot.transaction_temp_id (+)= tmp.transaction_temp_id
AND lin.header_id              =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id);
–create index mtl_material_transactions_n99 on mtl_material_transactions(trx_source_line_id);
prompt mtl_material_transactions (trn) – picked lines
PROMPT
— This is commented out because it runs slowly without an index
–<do not run> CREATE INDEX MTL_MATL_TRANS_777
–<do not run> ON INV.MTL_MATERIAL_TRANSACTIONS
–<do not run> (trx_source_line_id);
SELECT
  /*moac_sql_no_changes*/
  trn.transaction_id mtl_trns_id,
  trn.move_order_line_id move_line_id,
  trn.OPM_COSTED_FLAG,
  trn.SHIPMENT_COSTED,
  trn.SO_ISSUE_ACCOUNT_TYPE,
  trn.COGS_RECOGNITION_PERCENT,
  trn.TRANSFER_PRICE,
  trn.FOB_POINT,
  trn.OWNING_ORGANIZATION_ID,
  trn.TRANSFER_ORGANIZATION_ID,
  trn.TRANSACTION_ACTION_ID,
  trn.TRANSACTION_SOURCE_TYPE_ID,
  trn.TRANSACTION_TYPE_ID,
  trn.TRANSACTION_QUANTITY,
  trn.TRANSACTION_UOM,
  trn.TRANSACTION_DATE,
  trn.TRANSFER_TRANSACTION_ID,
  cst.ACCTG_COST,
  cst.period_id,
  DECODE(trn.transaction_type_id, 52,’52=stage trans’, 33,’33=so issue’, trn.transaction_type_id) trans_type,
  (SELECT TO_CHAR(LIN.line_number)
    || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
    || TO_CHAR(LIN.shipment_number))
    || DECODE(LIN.option_number, NULL, NULL, ‘.’
    || TO_CHAR(LIN.option_number))
    || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
    || ‘.’
    ||TO_CHAR(LIN.component_number))
    || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
    || DECODE(LIN.option_number, NULL, ‘.’, NULL )
    || ‘.’
    || TO_CHAR(LIN.service_number))
  FROM oe_order_lines_all lin
  WHERE trn.trx_source_line_id = lin.line_id
  ) line,
  trn.trx_source_line_id line_id,
  trn.primary_quantity prm_q,
  trn.secondary_transaction_quantity sec_q,
  inv_convert.inv_um_convert( trn.inventory_item_id, 5, trn.primary_quantity, itm.primary_uom_code, trn.secondary_uom_code, NULL, NULL) trn_cal_sec_q,
  lot.primary_quantity lot_prm_q,
  lot.secondary_transaction_quantity lot_sec_q,
  inv_convert.inv_um_convert( trn.inventory_item_id, lot.lot_number, trn.organization_id, 5, NVL(lot.primary_quantity,0), itm.primary_uom_code, trn.secondary_uom_code, NULL, NULL) lot_cal_sec_q,
  trn.secondary_uom_code uom2,
  lot.lot_number lot_num,
  trn.subinventory_code from_sub,
  trn.locator_id from_loc_id,
  trn.pick_slip_number pick_slip,
  trn.transfer_subinventory to_sub,
  trn.transfer_locator_id to_loc_id,
  trn.organization_id orgn_id,
  trn.transaction_source_id
FROM mtl_material_transactions trn,
  mtl_transaction_lot_numbers lot,
  mtl_system_items_b itm,
  gl_item_cst cst,
  gmf_fiscal_policies gfp,
  gmf_period_statuses gps,
  gmf_organization_definitions god,
  cm_mthd_mst cmm
WHERE trn.trx_source_line_id IN
  (SELECT DISTINCT line_id
  FROM oe_order_lines_all lin1
  WHERE lin1.header_id =
    &hid
  AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin1.line_id, lin1.top_model_line_id, lin1.ato_line_id, lin1.link_to_line_id, lin1.reference_line_id, lin1.service_reference_line_id)
  )
AND trn.organization_id            = itm.organization_id
AND trn.inventory_item_id          = itm.inventory_item_id
AND lot.transaction_id (+)         = trn.transaction_id
AND trn.transaction_source_type_id = 2
AND gfp.cost_type_id               = cmm.cost_type_id
AND cst.period_id                  = gps.period_id
AND gps.legal_entity_id            = gfp.legal_entity_id
AND gfp.legal_entity_id            = god.legal_entity_id
AND trn.organization_id            = god.organization_id
AND trn.transaction_date          >= gps.start_date
AND trn.transaction_date          <= gps.end_date
ORDER BY trn.trx_source_line_id,
  trn.transaction_id;
prompt mtl_transactions_interface (mti)
prompt
SELECT
  /*MOAC_SQL_NO_CHANGES*/
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  lin.line_id line_id,
  det.delivery_detail_id del_detail_id,
  itm.segment1 item,
  tmp.primary_quantity prm_q,
  secondary_transaction_quantity sec_q,
  tmp.subinventory_code from_sub,
  tmp.locator_id from_loc_id,
  tmp.process_flag process,
  tmp.lock_flag lck,
  tmp.transaction_mode trans_mode,
  tmp.error_code error_code,
  tmp.error_explanation error_expl
FROM mtl_transactions_interface tmp,
  wsh_delivery_details det,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE tmp.source_line_id   = lin.line_id
AND lin.line_category_code = ‘order’
AND lin.ship_from_org_id   = itm.organization_id(+)
AND lin.inventory_item_id  = itm.inventory_item_id(+)
AND det.source_line_id     = lin.line_id
AND lin.header_id          =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’) IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id)
UNION ALL
SELECT TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  lin.line_id line_id,
  det.delivery_detail_id del_detail_id,
  itm.segment1 item,
  tmp.primary_quantity prm_q,
  tmp.secondary_transaction_quantity sec_q,
  tmp.subinventory_code from_sub,
  tmp.locator_id from_loc_id,
  tmp.process_flag process,
  tmp.lock_flag lck,
  tmp.transaction_mode trans_mode,
  tmp.error_code error_code,
  tmp.error_explanation error_expl
FROM mtl_transactions_interface tmp,
  wsh_delivery_details det,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE tmp.trx_source_line_id = lin.line_id
AND lin.line_category_code   = ‘return’
AND lin.ship_from_org_id     = itm.organization_id(+)
AND lin.inventory_item_id    = itm.inventory_item_id(+)
AND det.source_line_id       = lin.line_id
AND lin.header_id            =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’)                         IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id);

Trip details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid 
==============================================
SELECT wdd.source_header_id,
  wdd.source_line_id,
  assign.delivery_assignment_id,
  assign.delivery_id,
  assign.delivery_detail_id,
  deli.name deli_name,
  deli.status_code deli_status,
  legs.delivery_leg_id,
  legs.pick_up_stop_id,
  — legs.drop_off_stop_id,
  DECODE(stops.pending_interface_flag, NULL, ‘Not Pending’, ‘Y’, ‘Pending’, stops.pending_interface_flag) pif,
  stops.status_code trip_stop_status,
  stops.actual_departure_date,
  trips.name trip_name,
  trips.trip_id,
  trips.status_code trip_status
FROM wsh_delivery_details wdd,
  wsh_delivery_assignments assign,
  wsh_new_deliveries deli,
  wsh_delivery_legs legs,
  wsh_trip_stops stops,
  wsh_trips trips
WHERE wdd.source_header_id =
  &hid
AND wdd.source_line_id LIKE DECODE(‘&lineid’,’all’,’%’,’&lineid’)
AND wdd.delivery_detail_id =assign.delivery_detail_id
AND assign.delivery_id     = deli.delivery_id
AND deli.delivery_id       =legs.delivery_id
AND legs.pick_up_stop_id   = stops.stop_id
AND stops.trip_id          = trips.trip_id
ORDER BY assign.delivery_assignment_id,
  assign.delivery_id,
  assign.delivery_detail_id;
  
Move ORDER details FOR sales ORDER NUMBER: &ord_num AND line_id: &lineid
 ===================================================
SELECT DISTINCT trl.line_id mo_line_id,
  trh.request_number mo_number,
  — trl.header_id mv_hdr_id,
  trl.line_number mv_line_num,
  DECODE(trl.line_status, 1, ‘1=Incomplete’, 2, ‘2=Pend Aprvl’, 3, ‘3=Approved’, 4, ‘4=Not Apprvd’, 5, ‘5=Closed’, 6, ‘6=Canceled’, 7, ‘7=Pre Apprvd’, 8, ‘8=Part Aprvd’) mv_line_stat,
  TO_CHAR(LIN.line_number)
  || DECODE(LIN.shipment_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.shipment_number))
  || DECODE(LIN.option_number, NULL, NULL, ‘.’
  || TO_CHAR(LIN.option_number))
  || DECODE(LIN.component_number, NULL, NULL, DECODE(LIN.option_number, NULL, ‘.’,NULL)
  || ‘.’
  ||TO_CHAR(LIN.component_number))
  || DECODE(LIN.service_number,NULL,NULL, DECODE(LIN.component_number, NULL, ‘.’ , NULL)
  || DECODE(LIN.option_number, NULL, ‘.’, NULL )
  || ‘.’
  || TO_CHAR(LIN.service_number)) LINE,
  trl.txn_source_line_id ord_line_id,
  det.delivery_detail_id ,
  itm.segment1 item,
  trl.quantity qty,
  trl.primary_quantity prm_q,
  trl.quantity_delivered dlv_q,
  trl.quantity_detailed dtl_q,
  trl.secondary_quantity sec_q,
  trl.secondary_quantity_detailed sec_dtl_q,
  trl.secondary_quantity_delivered sec_dlv_q,
  trl.move_order_type_name move_type_name,
  DECODE(trl.transaction_source_type_id,2,’Sales Order’,trl.transaction_source_type_id) trns_src_type,
  trl.transaction_type_name trns_type_name,
  trl.organization_id orgn_id,
  trl.from_subinventory_code from_sub,
  trl.from_locator_id from_loc_id,
  trl.to_subinventory_code to_sub,
  trl.to_locator_id to_loc_id,
  trl.lot_number lot_num,
  trl.transaction_header_id trns_head_id
FROM mtl_txn_request_lines_v trl,
  mtl_txn_request_headers trh,
  wsh_delivery_details det,
  oe_order_lines_all lin,
  mtl_system_items_b itm
WHERE trl.line_id = det.move_order_line_id
  –trl.txn_source_line_id = lin.line_id
AND lin.ship_from_org_id  = itm.organization_id(+)
AND lin.inventory_item_id = itm.inventory_item_id(+)
AND det.source_line_id    = lin.line_id
AND trl.header_id         = trh.header_id
AND lin.header_id         =
  &hid
AND DECODE(‘&lineid’,’all’,’%’,’&lineid’)              IN (‘%’,lin.line_id, lin.top_model_line_id, lin.ato_line_id, lin.link_to_line_id, lin.reference_line_id, lin.service_reference_line_id);

Extract Header details FOR sales ORDER NUMBER: &ord_num 
========================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt
WHERE geh.transaction_id           = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
Extract Lines FOR sales ORDER NUMBER: &ord_num 
==================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id                = geh.header_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
SLA Events FOR sales ORDER NUMBER: &ord_num 
=================================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  xla.xla_events xe
WHERE xe.event_id                  = geh.event_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
SLA Headers FOR sales ORDER NUMBER: &ord_num 
==================================
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id        IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 2
  AND mmt.trx_source_line_id        IN
    (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
    )
  );

SLA Lines FOR sales ORDER NUMBER: &ord_num 
================================
SELECT al.*
FROM xla.xla_ae_headers ah ,
  xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id      IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 2
  AND mmt.trx_source_line_id        IN
    (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
    )
  );
  
SLA Distributions FOR sales ORDER NUMBER: &ord_num 
=====================================
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt,
  xla.xla_distribution_links dl
WHERE dl.event_id                  = geh.event_id
AND dl.application_id              = 555
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 2
AND mmt.trx_source_line_id        IN
  (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
  );
  
Item Cost details 
============
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt
    WHERE mmt.transaction_source_type_id = 2
    AND god.organization_id              = mmt.organization_id
    AND gfp.legal_entity_id              = god.legal_entity_id
    AND mthd.cost_type_id                = gfp.cost_type_id
    AND gps.legal_entity_id              = gfp.legal_entity_id
    AND gps.cost_type_id                 = gfp.cost_type_id
    AND mmt.transaction_date            >= gps.start_date
    AND mmt.transaction_date            <= gps.end_date
    AND mmt.trx_source_line_id          IN
      (SELECT DISTINCT line_id FROM oe_order_lines_all WHERE header_id = &hid
      )
    )
  );
  
COGS RELATED:
Run Following Queries BY entering related SO# numbers AND RMA# Numbers FOR which COGS/DCOGS being analized **/ 
OE Headers 
==========
SELECT * FROM oe_order_headers_all WHERE order_number IN ( ‘&so_number’);

OE lines 
=====
SELECT oeh.order_number,
  oeh.header_id,
  oeh.order_type_id,
  oel.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel
WHERE oel.header_id   = oeh.header_id
AND oeh.order_number IN ( ‘&so_number’);

MMT rows              
=======
SELECT oeh.order_number,
  oeh.header_id oe_header_id,
  oeh.order_type_id,
  mmt.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel,
  mtl_material_transactions mmt
WHERE mmt.trx_source_line_id        = oel.line_id
AND oel.header_id                   = oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number               IN ( ‘&so_number’);

COGS events                         
===========
SELECT *
FROM cst_cogs_events
WHERE cogs_om_line_id IN
  (SELECT oel.line_id
  FROM oe_order_headers_all oeh,
    oe_order_lines_all oel
  WHERE oel.header_id   = oeh.header_id
  AND oeh.order_number IN ( ‘&so_number’)
  ) ;

Extract Headers 
===========
SELECT oeh.order_number,
  oeh.header_id oe_header_id,
  oeh.order_type_id,
  geh.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel,
  mtl_material_transactions mmt,
  gmf_xla_extract_headers geh
WHERE mmt.transaction_id            = geh.transaction_id
AND mmt.trx_source_line_id          = oel.line_id
AND oel.header_id                   = oeh.header_id
AND mmt.transaction_source_type_id IN (2, 12)
AND oeh.order_number               IN ( ‘&so_number’);

Extract Lines                       
=========
SELECT oeh.order_number,
  oeh.header_id oe_header_id,
  oeh.order_type_id,
  gel.*
FROM oe_order_headers_all oeh,
  oe_order_lines_all oel,
  mtl_material_transactions mmt,
  gmf_xla_extract_headers geh,
  gmf_xla_extract_lines gel
WHERE gel.header_id                      = geh.header_id
AND geh.transaction_id                   = mmt.transaction_id
AND mmt.trx_source_line_id               = oel.line_id
AND oel.header_id                        = oeh.header_id
AND mmt.transaction_source_type_id      IN (2, 12)
AND oeh.order_number                    IN ( ‘&so_number’);

INVENTORY RELATED: 
Material transactions 
==============
SELECT *
FROM mtl_material_transactions
WHERE organization_id =
  &&orgid
AND inventory_item_id =
  &&invitemid
AND transaction_id =
  &&transid;
  
Lot NUMBER transactions 
=================
SELECT *
FROM mtl_transaction_lot_numbers
WHERE organization_id =
  &&orgid
AND inventory_item_id =
  &&invitemid
AND transaction_id =
  &&transid;
  
MTL Parameter organization details Organization id: &&orgid 
==========================================
SELECT * FROM mtl_parameters WHERE organization_id = &&orgid;

Item details FOR Organization id: &&orgid AND item id: &&invitemid 
==============================================
SELECT *
FROM mtl_system_items_kfv
WHERE organization_id =
  &&orgid
AND inventory_item_id =
  &&invitemid;
  
Extract Header details FOR Transaction id: &&transid 
====================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh
WHERE geh.entity_code   = ‘INVENTORY’
AND geh.transaction_id IN
  (SELECT t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_id =
    &&transid
  );
  
Extract Lines details FOR Transaction id: &&transid 
===================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_xla_extract_lines gel
WHERE geh.entity_code   = ‘INVENTORY’
AND gel.header_id       = geh.header_id
AND geh.transaction_id IN
  (SELECT t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_id =
    &&transid
  );
  
Sla Events FOR Transaction id: &&transid 
============================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  xla.xla_events xe
WHERE geh.entity_code   = ‘INVENTORY’
AND xe.event_id         = geh.event_id
AND geh.transaction_id IN
  (SELECT t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_id =
    &&transid
  );
  
Item Component Cost details FOR Transaction id: &&transid 
==========================================
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt
    WHERE god.organization_id = mmt.organization_id
    AND mmt.transaction_id    =
      &&transid
    AND mmt.organization_id =
      &&orgid
    AND mmt.inventory_item_id =
      &&invitemid
    AND gfp.legal_entity_id   = god.legal_entity_id
    AND mthd.cost_type_id     = gfp.cost_type_id
    AND gps.legal_entity_id   = gfp.legal_entity_id
    AND gps.cost_type_id      = gfp.cost_type_id
    AND mmt.transaction_date >= gps.start_date
    AND mmt.transaction_date <= gps.end_date
    )
  );
  
PURCHASING RELATED: 
PO Header details FOR purchase ORDER id: &&poheaderid 
=======================================
SELECT * FROM po.po_headers_all WHERE po_header_id = &poheaderid;

PO Line details FOR purchase ORDER id: &&poheaderid 
=====================================
SELECT * FROM po_lines_all WHERE po_header_id = &poheaderid;

PO Line location details FOR purchase ORDER id: &&poheaderid 
===========================================
SELECT * FROM po_line_locations WHERE po_header_id = &poheaderid;

PO distribution details FOR purchase ORDER id: &&poheaderid 
==========================================
SELECT * FROM po_distributions WHERE po_header_id = &poheaderid;

RCV Transactions FOR purchase ORDER id: &&poheaderid 
=======================================
SELECT * FROM po.rcv_transactions WHERE po_header_id = &&poheaderid;

RCV accounting txns FOR purchase ORDER id: &&poheaderid 
=========================================
SELECT * FROM gmf.gmf_rcv_accounting_txns WHERE po_header_id = &&poheaderid;

MMT txns FOR purchase ORDER id: &&poheaderid 
==================================
SELECT *
FROM inv.mtl_material_transactions
WHERE transaction_source_type_id = 1
AND rcv_transaction_id          IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Opm financials data 
===============
Extract Header details FOR purchase ORDER id: &&poheaderid: 
==========================================
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt
WHERE geh.transaction_id           = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT geh.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt
WHERE geh.transaction_id    = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Extract Lines details FOR purchase ORDER id: &&poheaderid 
========================================
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id                = geh.header_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT gel.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt ,
  gmf.gmf_xla_extract_lines gel
WHERE gel.header_id         = geh.header_id
AND geh.transaction_id      = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Sla Events FOR purchase ORDER id: &&poheaderid 
=================================
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  xla.xla_events xe
WHERE xe.event_id                  = geh.event_id
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT xe.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt,
  xla.xla_events xe
WHERE xe.event_id           = geh.event_id
AND geh.transaction_id      = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Sla Header details FOR purchase ORDER id: &&poheaderid 
======================================
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id        IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 1
  AND mmt.rcv_transaction_id        IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  )
UNION ALL
SELECT ah.*
FROM xla.xla_ae_headers ah
WHERE ah.application_id = 555
AND ah.event_id        IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    gmf.gmf_rcv_accounting_txns mmt
  WHERE geh.transaction_id    = mmt.rcv_transaction_id
  AND mmt.rcv_transaction_id IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  );
  
Sla Lines details FOR purchase ORDER id: &&poheaderid 
======================================
SELECT al.*
FROM xla.xla_ae_headers ah ,
  xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id      IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    inv.mtl_material_transactions mmt
  WHERE geh.transaction_id           = mmt.transaction_id
  AND mmt.transaction_source_type_id = 1
  AND mmt.rcv_transaction_id        IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  )
UNION ALL
SELECT al.*
FROM xla.xla_ae_headers ah ,
  xla.xla_ae_lines al
WHERE al.ae_header_id = ah.ae_header_id
AND ah.application_id = 555
AND ah.event_id      IN
  (SELECT geh.event_id
  FROM gmf.gmf_xla_extract_headers geh,
    gmf.gmf_rcv_accounting_txns mmt
  WHERE geh.transaction_id    = mmt.rcv_transaction_id
  AND mmt.rcv_transaction_id IN
    (SELECT transaction_id
    FROM po.rcv_transactions
    WHERE po_header_id =
      &&poheaderid
    )
  );
  
Sla Distributions FOR purchase ORDER id: &&poheaderid 
======================================
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
  inv.mtl_material_transactions mmt ,
  xla.xla_distribution_links dl
WHERE dl.event_id                  = geh.event_id
AND dl.application_id              = 555
AND geh.transaction_id             = mmt.transaction_id
AND mmt.transaction_source_type_id = 1
AND mmt.rcv_transaction_id        IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  )
UNION ALL
SELECT dl.*
FROM gmf.gmf_xla_extract_headers geh,
  gmf.gmf_rcv_accounting_txns mmt ,
  xla.xla_distribution_links dl
WHERE dl.event_id           = geh.event_id
AND dl.application_id       = 555
AND geh.transaction_id      = mmt.rcv_transaction_id
AND mmt.rcv_transaction_id IN
  (SELECT transaction_id
  FROM po.rcv_transactions
  WHERE po_header_id =
    &&poheaderid
  );
  
Item Cost details 
============
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt,
      rcv_transactions rct
    WHERE mmt.transaction_source_type_id = 1
    AND god.organization_id              = mmt.organization_id
    AND mmt.rcv_transaction_id           = rct.transaction_id
    AND rct.po_header_id                 =
      &&poheaderid
    AND gfp.legal_entity_id   = god.legal_entity_id
    AND mthd.cost_type_id     = gfp.cost_type_id
    AND gps.legal_entity_id   = gfp.legal_entity_id
    AND gps.cost_type_id      = gfp.cost_type_id
    AND mmt.transaction_date >= gps.start_date
    AND mmt.transaction_date <= gps.end_date
    )
  );
  

PRODUCTION MANAGEMENT: 
Batch Header details FOR Batch id: &&batch_id 
================================
SELECT * FROM gme_batch_header WHERE batch_id=’&&batch_id’;

Recipe details FOR Batch id: &&batch_id 
============================
SELECT r.*
FROM gme_batch_header b ,
  gmd_recipes r ,
  gmd_recipe_validity_rules vr
WHERE b.batch_id=
  &&batch_id
AND b.recipe_validity_rule_id                          =vr.recipe_validity_rule_id
AND vr.recipe_id                                       =r.recipe_id;

Recipe Validity Rules details FOR Batch id: &&batch_id 
======================================
SELECT vr.*
FROM gme_batch_header b ,
  gmd_recipes r ,
  gmd_recipe_validity_rules vr
WHERE b.batch_id=
  &&batch_id
AND b.recipe_validity_rule_id                   =vr.recipe_validity_rule_id
AND vr.recipe_id                                =r.recipe_id;

Batch Material Details FOR Batch id: &&batch_id 
=================================
SELECT d.* FROM gme_material_details d WHERE d.batch_id = &&batch_id;

Inventory Transactions details FOR Batch id: &&batch_id 
=======================================
SELECT t.*
FROM mtl_material_transactions t
WHERE t.transaction_source_id =
  &&batch_id
AND t.transaction_source_type_id                              = 5;

Batch Material Transaction Pairs FOR Batch NUMBER: &&batch_id 
=============================================
SELECT * FROM gme_transaction_pairs p WHERE p.batch_id = &&batch_id;

Batch RESOURCE Transactions details FOR Batch NUMBER: &&batch_id 
===============================================
SELECT t.*
FROM gme_batch_header h,
  gme_resource_txns t
WHERE h.batch_id =
  &&batch_id
AND h.batch_id                        = t.doc_id
AND t.doc_type                        = ‘PROD’;

Yield Layers FOR Batch id: &&batch_id 
===========================
SELECT *
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
  (SELECT DISTINCT t.organization_id,
    t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_source_id =
    &&batch_id
  AND t.transaction_source_type_id = 5
  );
  
Material Consumption Layers FOR Batch id: &&batch_id 
=======================================
SELECT *
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
  (SELECT DISTINCT t.organization_id,
    t.transaction_id
  FROM mtl_material_transactions t
  WHERE t.transaction_source_id =
    &&batch_id
  AND t.transaction_source_type_id = 5
  );
  
RESOURCE Consumption Layers FOR Batch id: &&batch_id 
========================================
SELECT *
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
  (SELECT t.poc_trans_id
  FROM gme_resource_txns t
  WHERE t.doc_id =
    &&batch_id
  AND t.doc_type = ‘PROD’
  );
  
VIB Details FOR Batch id: &&batch_id 
==========================
SELECT *
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
  (SELECT br.requirement_id
  FROM gmf_batch_requirements br
  WHERE br.batch_id =
    &&batch_id
  );
  
Batch Requirement Details FOR Batch id: &&batch_id 
=====================================
SELECT * FROM gmf_batch_requirements br WHERE br.batch_id = &&batch_id;

Layer cost details FOR Batch id: &&batch_id 
==============================
SELECT *
FROM gmf_layer_cost_details c
WHERE c.layer_id IN
  (SELECT il.layer_id
  FROM gme_batch_header h,
    mtl_material_transactions t,
    gmf_incoming_material_layers il
  WHERE h.batch_id =
    &&batch_id
  AND h.batch_id                   = t.transaction_source_id
  AND t.transaction_source_type_id = 5
  AND il.mmt_transaction_id        = t.transaction_id
  AND il.mmt_organization_id       = t.organization_id
  );
  
Extract Header details FOR Batch id: &&batch_id 
=================================
SELECT *
FROM gmf_xla_extract_headers
WHERE entity_code      = ‘PRODUCTION’
AND source_document_id =
  &l_batch_id;
  
Extract Lines details FOR Batch id: &&batch_id 
=================================
SELECT *
FROM gmf_xla_extract_lines
WHERE header_id IN
  (SELECT header_id
  FROM gmf_xla_extract_headers
  WHERE entity_code      = ‘PRODUCTION’
  AND source_document_id =
    &l_batch_id
  );
  
Sla Events FOR Batch id: &&batch_id 
==========================
SELECT xe.*
FROM gmf_xla_extract_headers eh,
  xla_events xe
WHERE eh.event_id         = xe.event_id
AND xe.application_id     =555
AND eh.entity_code        = ‘PRODUCTION’
AND eh.source_document_id =
  &l_batch_id;
  
Item Component Cost details FOR Batch id: &&batch_id 
=======================================
SELECT a.*
FROM gl_item_dtl a
WHERE a.itemcost_id IN
  (SELECT itemcost_id
  FROM gl_item_cst
  WHERE (inventory_item_id, organization_id,cost_type_id, period_id) IN
    (SELECT DISTINCT mmt.inventory_item_id,
      mmt.organization_id,
      gps.cost_type_id,
      gps.period_id
    FROM gmf_organization_definitions god,
      gmf_period_statuses gps,
      gmf_fiscal_policies gfp,
      cm_mthd_mst mthd,
      mtl_material_transactions mmt
    WHERE mmt.transaction_source_type_id = 5
    AND god.organization_id              = mmt.organization_id
    AND mmt.transaction_source_id        =
      &&batch_id
    AND gfp.legal_entity_id   = god.legal_entity_id
    AND mthd.cost_type_id     = gfp.cost_type_id
    AND gps.legal_entity_id   = gfp.legal_entity_id
    AND gps.cost_type_id      = gfp.cost_type_id
    AND mmt.transaction_date >= gps.start_date
    AND mmt.transaction_date <= gps.end_date
    )
  );
/by
, , ,

R12 Query/Script to find profile option value from Back-end

Background:
When we run, the following SQL Query/Script will provide the profile option values set at different levels. That is Site, Application, Responsibility, User.
So, this script lists ALL Users/Responsibilities/Applications set against a particular Profile option

SQL Script:
Enter ‘Profile Option Name’ while running this query.
Eg. FND: Debug Log Enabled, MO: Operating Unit
[You can get the exact name from table fnd_profile_options_tl]  

  SELECT fpo.profile_option_name SHORT_NAME,
         fpot.user_profile_option_name NAME,
         DECODE (fpov.level_id,
                 10001, ‘Site’,
                 10002, ‘Application’,
                 10003, ‘Responsibility’,
                 10004, ‘User’,
                 10005, ‘Server’,
                 ‘UnDef’)
            LEVEL_SET,
         DECODE (TO_CHAR (fpov.level_id),
                 ‘10001’, ”,
                 ‘10002’, fap.application_short_name,
                 ‘10003’, frsp.responsibility_key,
                 ‘10005’, fnod.node_name,
                 ‘10006’, hou.name,
                 ‘10004’, fu.user_name,
                 ‘UnDef’)
            “CONTEXT”,
         fpov.profile_option_value VALUE
    FROM fnd_profile_options fpo,
         fnd_profile_option_values fpov,
         fnd_profile_options_tl fpot,
         fnd_user fu,
         fnd_application fap,
         fnd_responsibility frsp,
         fnd_nodes fnod,
         hr_operating_units hou
   WHERE     fpo.profile_option_id = fpov.profile_option_id(+)
         AND fpo.profile_option_name = fpot.profile_option_name
         AND fu.user_id(+) = fpov.level_value
         AND frsp.application_id(+) = fpov.level_value_application_id
         AND frsp.responsibility_id(+) = fpov.level_value
         AND fap.application_id(+) = fpov.level_value
         AND fnod.node_id(+) = fpov.level_value
         AND hou.organization_id(+) = fpov.level_value
         AND fpot.user_profile_option_name IN (‘&User_Profile_Option_Name’)
ORDER BY short_name;
/by
, , ,

Tricks for generating SQL statements in Excel 2003/2007

The semi-colon needs to be inside the last double quote with a closing param. When adding single quotes around a string, remember to add them outside your selected cell.
(spaces added for visibility – remove before inserting)
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")
Here is another view:
=CONCATENATE(“insert into table (id, date, price) values (“,C3,”,'”,D3,”‘,”,B3,”);”)
/by
, , , , ,

Some Useful Queries in Oracle Apps R12

How to initialize session in Oracle ?
FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API’s in Oracle Ebusiness suite. Its not required for all the API’s but its recommended that you set this profile before making any calls to either private or public API. 

Mentioned below is a sample call to FND_GLOBAL.APPS_INITIALIZE function

fnd_global.APPS_INITIALIZE(user_id=>l_user_id, 
                           resp_id=>l_resp_id, 
                           resp_appl_id=>l_resp_appl_id);

l_user_id is the fnd user ID which will be utilized during the call. 
l_resp_id is the responsibility ID 
l_resp_appl_id is the responsibility application ID. 
You can use either sysadmin or use some user who has all the above listed responsibilities.

For SYSADMIN, utilize the following query to get the respective values

select fnd.user_id , 
       fresp.responsibility_id, 
       fresp.application_id 
from   fnd_user fnd 
,      fnd_responsibility_tl fresp 
where  fnd.user_name = ‘SYSADMIN’ 
and    fresp.responsibility_name = ‘Order Management Super User’;

Another option is Help > Diagnostics > Examine and get the values from $profile session values.

How to Find PO Requisitions associated with PO Orders?
By using below query , we can find out the all the requisitions (Purchasing Requisitions) which are associated with what all Purchasing orders.

They are 2 types of requisitions.
1) Purchasing Requisition
2) Internal Requisition.
Note:- Only Purchase Requisitions will be converted to the Purchase orders.

SELECT prha.segment1 “Requisition Number”,
prha.type_lookup_code “Requisition Type”,
pha.segment1 “Purchase Order Number”,
pha.type_lookup_code “Purchase Order Type”
FROM po_headers_all pha,
po_distributions_all pda,
po_req_distributions_all rd,
po_requisition_lines_all prla,
po_requisition_headers_all prha
WHERE pha.po_header_id = pda.po_header_id
AND pda.req_distribution_id = rd.distribution_id
AND rd.requisition_line_id = prla.requisition_line_id
AND prla.requisition_header_id = prha.requisition_header_id

How to get the Cancelled Requisitions?
You can get all the Canceled Requisitions in the PO Module using the following Query.

SELECT
prha.requisition_header_id “requisition_header_id”
,prha.segment1 “Requisition Number”
,prha.preparer_id “preparer_id”
,TRUNC(prha.creation_date) “creation_date”
,prha.description “description”
,prha.note_to_authorizer “note_to_authorizer”
FROM
po_requisition_headers_all prha
,po_action_history pah
WHERE action_code=’CANCEL’
AND pah.object_type_code=’

REQUISITION’
AND pah.object_id=prha.requisition_header_id
How to find On-hand inventory information?
Execute below query to see the onhand inventory information in oracle applications.

SELECT
NVL(substr(org.organization_code, 1,3), ‘ ‘) orgcode
,NVL(substr(msi.segment1, 1, 8), ‘ ‘) seg11
,NVL(substr(msi.segment1, 9, 8), ‘ ‘) seg12
,NVL(substr(msi.segment1, 17, 4), ‘ ‘) seg13
,NVL(moq.subinventory_code, ‘ ‘) sub_inv_code
,NVL(to_char(round(sum(moq.transaction_quantity))), ‘ ‘) trans_qnty
FROM mtL_system_items msi
,org_organization_definitions org
,mtl_onhand_quantities moq
,hr_organization_units hou
WHERE moq.inventory_iteM_id = msi.inventory_item_id
AND moq.organizatioN_id = msi.organizatioN_id
AND moq.organizatioN_id = org.organizatioN_id
AND moq.organization_id = hou.organization_id
GROUP BY org.organization_code
, moq.subinventory_code
, msi.segment1;

How to know the version in oracle apps?
select * from v$version;
 
With the help of above query we can find out the oracle apps version. 


How to know the application is multi-org or not?


By using below query we can get the multi org status.


 


SELECT multi_org_flag
FROM   fnd_product_groups




How to know Which User is Locked the table?



This Query will list the name of user who locked the table table. The object name is taken as an input parameter.

SELECT c.owner
      ,c.object_name
      ,c.object_type
      ,fu.user_name locking_fnd_user_name
      ,fl.start_time locking_fnd_user_login_time
      ,vs.module
      ,vs.machine
      ,vs.osuser
      ,vlocked.oracle_username
      ,vs.sid
      ,vp.pid
      ,vp.spid AS os_process
      ,vs.serial#
      ,vs.status
      ,vs.saddr
      ,vs.audsid
      ,vs.process
FROM fnd_logins      fl
    ,fnd_user        fu
    ,v$locked_object vlocked
    ,v$process       vp
    ,v$session       vs
    ,dba_objects     c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name = :P_TABLE_NAME
AND nvl(vs.status,'XX') != 'KILLED';




How to get Customer Address Details in Oracle apps?





The following Query will provide the customer address details in oracle applications.

SELECT  hca.account_number customer_number,
                hp.party_name customer_name,
                hps.party_site_number site_number, hl.address1 address1,
                hl.address2 address2, hl.address3 address3,
                hl.address4 address4, hl.city city,
                hl.postal_code postal_code, hl.state state,
                ftt.territory_short_name country,
                hcsua1.LOCATION bill_to_location,
                hcsua2.LOCATION ship_to_location
  FROM hz_parties hp,
                hz_party_sites hps,
                hz_cust_accounts hca,
                hz_cust_acct_sites_all hcasa1,
                hz_cust_site_uses_all hcsua1,
                hz_locations hl,
                fnd_territories_tl ftt,
                hz_cust_acct_sites_all hcasa2,
                hz_cust_site_uses_all hcsua2
  WHERE hp.party_id = hps.party_id(+)
       AND hp.party_id = hca.party_id(+)
       AND hcasa1.party_site_id(+) = hps.party_site_id
       AND hcasa2.party_site_id(+) = hps.party_site_id
       AND hcsua1.cust_acct_site_id(+) = hcasa1.cust_acct_site_id
       AND hcsua2.cust_acct_site_id(+) = hcasa2.cust_acct_site_id
       AND hcsua1.site_use_code(+) = 'bill_to'
       AND hcsua2.site_use_code(+) = 'ship_to'
       AND hcasa1.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hcasa2.org_id(+) = fnd_profile.VALUE ('org_id')
       AND hps.location_id = hl.location_id
       AND hl.country = ftt.territory_code
       AND ftt.LANGUAGE = USERENV ('lang')
  ORDER BY customer_number;





Script to find Oracle API’s for any module


With the help of below query we can find the API for a module in Oracle Apps.


select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name







/by