Solved

how to tune this sql query

Posted on 2016-09-15
61
99 Views
Last Modified: 2016-09-30
SELECT b.custno,
       b.stockto,
       TO_CHAR (a.dateholder, 'DY') "DAYNAME",
       TO_CHAR (a.dateholder, 'IYYY') || TO_CHAR (a.dateholder, 'IW')
          "WEEKNO",
       a.dateholder "RAD",
       0 "TDY_LOAD_CNT",
       NVL (TO_NUMBER (c.avail), 0) "TDY_CAP",
       DECODE (TRUNC (a.dateholder),
               TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),
               NULL)
          "PRIOR_LOAD_CNT"
  FROM (    SELECT TRUNC (SYSDATE) - 1 + LEVEL AS dateholder
              FROM DUAL
        CONNECT BY LEVEL <= 180) a,
       (  SELECT DISTINCT custno, stockto, MAX (rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_order
        GROUP BY custno, stockto) b,
       (SELECT cal,
               (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
               avail
          FROM caldata) c
 WHERE     a.dateholder <= b.last_rad
       AND a.dateholder = NVL (c.effdate, a.dateholder)
       AND (b.custno || '-INCAP') = c.cal(+)
ORDER BY rad;

Open in new window

0
Comment
Question by:pardeshirahul
  • 27
  • 14
  • 9
  • +2
61 Comments
 
LVL 20

Expert Comment

by:Russ Suter
Comment Utility
Without knowing anything about the data structure you're querying against it's nearly impossible to offer any real advice. If you have Oracle SQL Developer installed you can use the Explain Plan feature to help you determine where you might be able to improve performance either by indexing or refactoring the query.

SQL Developer can be found here: http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index-097090.html

You will need Java installed to use it.
0
 

Author Comment

by:pardeshirahul
Comment Utility
atleastr let me know the fasted way of writing this query

SELECT distinct custno, stockto, MAX (rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_order
        GROUP BY custno, stockto
       

here RAD is date
and xxx_ess_scp_ib_cap_order is a view if you want i can give the construct of the view
0
 

Author Comment

by:pardeshirahul
Comment Utility
the query is taking 1 minutes 30 seconds to run

anf the main query is running for ever

SELECT b.custno,
       b.stockto,
       TO_CHAR (a.dateholder, 'DY') "DAYNAME",
       TO_CHAR (a.dateholder, 'IYYY') || TO_CHAR (a.dateholder, 'IW')
          "WEEKNO",
       a.dateholder "RAD",
       0 "TDY_LOAD_CNT",
       NVL (TO_NUMBER (c.avail), 0) "TDY_CAP",
       DECODE (TRUNC (a.dateholder),
               TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),
               NULL)
          "PRIOR_LOAD_CNT"
  FROM
       (  SELECT distinct custno, stockto, MAX (rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_order
        GROUP BY custno, stockto) b,
       (SELECT cal,
               (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
               avail
          FROM caldata) c,
           (    SELECT TRUNC (SYSDATE) - 1 + LEVEL AS dateholder
              FROM DUAL
        CONNECT BY LEVEL <= 180) a
 WHERE     a.dateholder <= b.last_rad
       AND a.dateholder = NVL (c.effdate, a.dateholder)
       AND (b.custno || '-INCAP') = c.cal(+)
ORDER BY rad;

Open in new window

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
"at least let me know the fastest way of writing this query:

 SELECT distinct custno, stockto, MAX (rad) "LAST_RAD"
             FROM xxx_ess_scp_ib_cap_order
         GROUP BY custno, stockto"

Add a "where" clause!

This query (or sub-query) forces full-table scan of the xxx_ess_scp_ib_cap_order table, since you aren't providing any "where" clause values here to limit it.

And, I see two other problems:
1. "distinct"
2. MAX(rad)

1. You always have to be careful with using "distinct" in an Oracle query, since the results may be different from what you might expect.  This is especially true when you have a "date" column value included at the same level in the query, as you do here.   One other problem with using "distinct" is that always forces Oracle to do a sort on the intermediate results to check for duplicates.  Depending on how many rows are returned at this level, that sort operation may, or may not, add a significant performance penalty.

2. Since you have a group operator: MAX on the "rad" column, you need to have a "group by" clause that includes the other columns being selected here.  And you have that, so the results will already be grouped by these two column (that is: only distinct combinations of these two will be returned) so why do you want to add a separate "distinct"?
0
 

Author Comment

by:pardeshirahul
Comment Utility
okay i got that thanks i will re write the query and get back if i have any questions

Thanks,
Rahul
0
 

Author Comment

by:pardeshirahul
Comment Utility
different way of writing this query

 SELECT TRUNC (SYSDATE) - 1 + LEVEL AS dateholder
              FROM DUAL
        CONNECT BY LEVEL <= 180
0
 

Author Comment

by:pardeshirahul
Comment Utility
as well as different way of writing this query

SELECT cal,
               (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
               avail
          FROM caldata
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 500 total points
Comment Utility
========= topic 1

"distinct" is REDUNDANT in the following:

SELECT DISTINCT
         custno, stockto, MAX (rad) "LAST_RAD"
FROM xxx_ess_scp_ib_cap_order
GROUP BY custno, stockto

The GROUP BY clause forces uniqueness of rows
(it is not necessary to ask for "distinct unique" rows)

So the fastest way is simply:

SELECT
         custno, stockto, MAX (rad) "LAST_RAD"
FROM xxx_ess_scp_ib_cap_order
GROUP BY custno, stockto

Nothing I can do will make that part of your query faster
UNLESS you add an index involving custno, stockto on that table
(assuming that index does not already exist and you are able to change the table definition)

==============

SELECT
        b.custno
      , b.stockto
      , TO_CHAR(a.dateholder, 'DY')                                 "DAYNAME"
      , TO_CHAR(a.dateholder, 'IYYY') || TO_CHAR(a.dateholder, 'IW') "WEEKNO"
      , a.dateholder                                                "RAD"
      , 0                                                           "TDY_LOAD_CNT"
      , NVL(TO_NUMBER(c.avail), 0)                                  "TDY_CAP"
      , DECODE (TRUNC (a.dateholder),  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),  NULL)   "PRIOR_LOAD_CNT"
FROM (SELECT
                TRUNC(SYSDATE) - 1 + LEVEL AS dateholder
      FROM DUAL
      CONNECT BY LEVEL <= 180
      ) a
INNER JOIN (SELECT
                custno
              , stockto
              , MAX(rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_order
            GROUP BY
                custno
              , stockto
            ) b ON a.dateholder <= b.last_rad
LEFT JOIN (SELECT
                cal
              , (eff / 1440 + TO_DATE('01011970', 'MMDDYYYY')) "EFFDATE"
              , avail
            FROM caldata
            ) c ON a.dateholder = c.effdate and (b.custno || '-INCAP') = c.cal
ORDER BY a.dateholder
;

Open in new window


Because I have no data at all to look at I really am left guessing. I have doubts about the following:
line 9 there is a function on that line. It could be causing performance problems
line 11 do you want (TRUNC(SYSDATE) - 1) + LEVEL  or  TRUNC(SYSDATE) - (1 + LEVEL)   ?????
                       you have missing parentheses in this line
line 15 remove distinct in a group by query
lines 15 and 24 learn to use ANSI standard join syntax

line 29 you are joining via a calculated value .dateholder =  ((eff / 1440 + TO_DATE('01011970', 'MMDDYYYY'))
line 29 you are using a concatenation in a join (b.custno || '-INCAP') = c.cal

the last 2 listed above mean the query probably cannot use any indexes to perform the joins UNLESS you have function based indexes that match the calculations

As I have to guess: I suggest that function (see line 9) is most likely to be your performance problem: jms_ess_scp_ib_cap_prior (b.custno)
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
This appears to be the third time that this is has been posted in one form or another.

Your issue isn't SYSDATE or DUAL.  Here is the plan for your date query.  

SQL> explain plan for select trunc(sysdate) - 1 + level as dateholder from dual connect by level < 180;

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LEVEL<180)

Open in new window

If you actually let it run, these are the stats:
---------------------------------------------------------
Plan hash value: 1236776825

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
|   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(LEVEL<180)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
       4391  bytes sent via SQL*Net to client
        664  bytes received via SQL*Net from client
         13  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        179  rows processed

Open in new window

Seriously.  Less than one second execution time.  No disk access, no nothing.  It is totally done in memory and the time it takes is really the time to display the output on the screen.

Your issue is the massive view that you are pulling from.  Based on your other questions, the view generates a 500+ line query plan.  And you think that the date is the issue and not the view?  It also appears to have quite a few remote operations, which would vary in time based on load on other systems and network latency.

I suggest working on the view and not focusing on the date.  Do you really need everything that view is doing?  Can you just narrow it down to the few tables you need and go from there?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
If the view: xxx_ess_scp_ib_cap_order  was developed for some other purpose, and now you use it to only extract three column values for this query, that view may be a huge performance problem.  Many custom views that I have seen developed to retrieve information from purchased applications correctly retrieve the information that they were written to retrieve.  However, in many cases, these views were developed by people who either had no knowledge of how to write views that perform efficiently in Oracle, or they had no concern for how the views perform in Oracle.

You are using this view without providing a "where" clause at all, so you force Oracle to read *EVERY* possible row that the view can retrieve.  Views like this often include many columns (you are only interested in three columns for this query) and views like this often have joins to lots of tables, so these can be quite slow.

Sure, it may seem easier to use an existing view than to write a specific query that targets only the specific tables and columns you need, but when you use the easy approach (using an existing view) the performance is often disappointing.  I agree with johnsone's comments, especially the last paragraph of his post just before this one.
0
 

Author Comment

by:pardeshirahul
Comment Utility
xxx_ess_scp_ib_cap_order is a view so if i have to create an index on custno, stockto
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
No. I believe the advice is:

Use the TABLES and do NOT use the view


=== footnote
Not sure where the information about that view exists. It would be useful to have it here on this question.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
I found the info on the view in this question:

https://www.experts-exchange.com/questions/28969751/tune-the-query.html

The query seems pretty similar.  There are a couple of plans in there which are well over 500 lines which appear to be generated by that view.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
And, adding an index just to help an "order by" clause may not add much value, when there is no "where" clause!
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
Any wonder you are having performance issues, that view has a massive explain plan. Here is a list of the full table accesses from that explain plan I have not removed duplicates so that you see how many times each table is fully scanned:
TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456 Bytes: 870,606 Cardinality: 79,146 
TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 533 Bytes: 1,377,456  Cardinality: 114,788 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 16 Bytes: 14,440 Cardinality: 1,805 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 24 Bytes: 10,292 Cardinality: 332 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE HR.PER_PHONES Cost: 69 Bytes: 37,818 Cardinality: 3,438 
TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7 Bytes: 26 Cardinality: 1 
TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7 Bytes: 26 Cardinality: 1 
TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7 Bytes: 26 Cardinality: 1 
TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7 Bytes: 26 Cardinality: 1 
TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7 Bytes: 26 Cardinality: 1 
TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7 Bytes: 26 Cardinality: 1 
TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,031 Bytes: 5,307,550 Cardinality: 106,151 
TABLE ACCESS FULL TABLE PO.PO_LINES_ALL Cost: 4,541 Bytes: 6,978,735 Cardinality: 465,249 
TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 8,768 Bytes: 7,752,834 Cardinality: 287,142 
TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 8,771 Bytes: 445,067 Cardinality: 14,357 

Open in new window

And here is a list of tables accessed by index rowid:
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 11 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 11 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 11 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 11 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 11 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1 Bytes: 11 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_CONTACTS Cost: 1 Bytes: 10 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE CPGC.CPGC_ITEMS Cost: 2 Bytes: 12 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1 Bytes: 8 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2 Bytes: 25 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_CATEGORY_SETS_TL Cost: 1 Bytes: 26 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1 Bytes: 9 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2 Bytes: 7 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2 Bytes: 19 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 50 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 50 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 50 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 65 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 65 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2 Bytes: 65 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7 Bytes: 75 Cardinality: 5 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7 Bytes: 75 Cardinality: 5 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7 Bytes: 75 Cardinality: 5 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7 Bytes: 75 Cardinality: 5 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7 Bytes: 75 Cardinality: 5 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7 Bytes: 75 Cardinality: 5 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 62 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 66 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 66 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 66 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 69 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 69 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3 Bytes: 69 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2 Bytes: 33 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2 Bytes: 33 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2 Bytes: 33 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2 Bytes: 33 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2 Bytes: 15 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2 Bytes: 15 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2 Bytes: 15 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4 Bytes: 37 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4 Bytes: 37 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4 Bytes: 43 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 5 Bytes: 23 Cardinality: 1 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 5 Bytes: 31 Cardinality: 1
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3 Bytes: 24,044,496 Cardinality: 1,144,976 
TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 4 Bytes: 21 Cardinality: 1 

Open in new window

So. That view is doing a massive amount of work, almost all of which you ignore. You only use this:

custno, stockto, MAX (rad)

It's like driving to the next city via the north pole = very very wasteful

Somewhere in the following tables you will find custno, stockto, rad
but you won't need all of them
AP.AP_SUPPLIERS
AP.AP_SUPPLIER_CONTACTS
AP.AP_SUPPLIER_SITES_ALL
CPGC.CPGC_ITEMS
GL.GL_LEDGERS
HR.HR_ALL_ORGANIZATION_UNITS
HR.HR_ORGANIZATION_INFORMATION
HR.PER_PHONES
INV.MTL_CATEGORY_SETS_TL
INV.MTL_PARAMETERS
INV.MTL_SYSTEM_ITEMS_B
PO.PO_HEADERS_ALL
PO.PO_LINES_ALL
PO.PO_LINE_LOCATIONS_ALL
PO.PO_RELEASES_ALL
PO.RCV_SHIPMENT_HEADERS
PO.RCV_SHIPMENT_LINES
PO.RCV_TRANSACTIONS

Open in new window

My guess (for custno and stockno) you would start with these:
      PO.PO_HEADERS_ALL & PO.PO_LINES_ALL
I don't know what "rad" signifies in the context of this query so have no idea where that comes from

LOOK AT THE VIEW'S SQL to locate the sources of the 3 columns, and just use those source tables in your query.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
That list of tables confirms that this is an Oracle E-Business Suite application.  That is advertised as being very configurable and flexible.  What isn't advertised is the fact that it is not very efficient, and it certainly isn't optimized for any particular business or industry
Most custom views that I have seen for E-Business Suite are the kind of views I described earlier: flexible, but terribly inefficient.
0
 

Author Comment

by:pardeshirahul
Comment Utility
the ebusiness suite query which is inefficient

DROP VIEW JMSUSER.JMS_PUR_EXPECTED_RECEIPTS_SUM;

/* Formatted on 9/20/2016 10:09:40 AM (QP5 v5.252.13127.32867) */
CREATE OR REPLACE FORCE VIEW JMSUSER.JMS_PUR_EXPECTED_RECEIPTS_SUM
(
   INVENTORY_ORG_CODE,
   ITEM_NO,
   ITEM_DESCRIPTION,
   QUANTITY_ORDERED,
   QUANTITY_RECEIVED,
   QUANTITY_CANCELLED,
   QUANTITY_SHIPPED,
   QUANTITY_DUE,
   UOM,
   DOCUMENT_NO,
   REVISION,
   ORDER_DATE,
   PROMISED_DATE,
   EXPECTED_DATE,
   STATUS,
   SUPPLIER_NO,
   SUPPLIER_NAME,
   SUPPLIER_SITE_CODE,
   SUPPLIER_WHSE_CODE,
   SUPPLIER_WHSE_NAME,
   DOCUMENT_TYPE,
   LINE_NUM,
   SHIPMENT_NUM,
   DELIVERY_TIME,
   RAIL_CAR,
   UNIT_PRICE,
   CURRENCY_CODE,
   INSPECTION_REQUIRED,
   HEADER_NOTE_TO_RECEIVER,
   SHIPMENT_NOTE_TO_RECEIVER,
   NOTE_TO_RECEIVER,
   WMS_ITEM,
   PACKING_SLIP,
   BILL_OF_LADING,
   ASN_SHIPMENT_NUM,
   FREIGHT_CARRIER_CODE,
   SCAC,
   PALLET_COUNT,
   ASN_TRUCK_NUM,
   ASN_SEAL,
   ASN_FLAG,
   EXPO_DATE,
   ASN_LINE_COMMENTS,
   ASN_HEADER_COMMENTS,
   INCO_TERMS,
   ORDER_TYPE,
   TRANSPORTATION_NOTES,
   QUERY_STATUS,
   IRP_STW_IDENTIFIER,
   RECEIPT_WITH_ASN_RECEIVED
)
AS
   SELECT    /*+ FIRST_ROWS */                                  --HO.NAME "INVENTORY_ORG_NAME",
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
          --HL.LOCATION_CODE "LOCATION",
          MSI.SEGMENT1 "ITEM_NO",
          MSI.DESCRIPTION "ITEM_DESCRIPTION",
          --OICV.CATEGORY "INV_CLASS",
          PLL.QUANTITY "QUANTITY_ORDERED",
          PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
          PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
          rsl.quantity_shipped "QUANTITY_SHIPPED",
          (  NVL (PLL.QUANTITY, 0)
           - NVL (PLL.QUANTITY_CANCELLED, 0)
           - NVL (PLL.quantity_received, rsl.quantity_shipped))
             "QUANTITY_DUE",
          POL.UNIT_MEAS_LOOKUP_CODE "UOM",
          POH.SEGMENT1 || '-' || PRA.RELEASE_NUM "DOCUMENT_NO",
          PRA.REVISION_NUM "REVISION",
          NVL (PRA.CREATION_DATE, POH.CREATION_DATE) "ORDER_DATE",
          PLL.PROMISED_DATE "PROMISED_DATE",
          NVL (RSH.EXPECTED_RECEIPT_DATE,
               NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE))
             "EXPECTED_DATE",
          NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
          PV.SEGMENT1 "SUPPLIER_NO",
          PV.VENDOR_NAME "SUPPLIER_NAME",
          asa.vendor_site_code "SUPPLIER_SITE_CODE",
          ood.organization_code "SUPPLIER_WHSE_CODE",
          ood.organization_name "SUPPLIER_WHSE_NAME",
          -- sup_con.email_address "SUPPLIER_EMAIL",
          --PAP.FULL_NAME "BUYER_NAME",
          'BLANKET RELEASE' "DOCUMENT_TYPE",
          POL.LINE_NUM,
          PLL.SHIPMENT_NUM,
          DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                  '00:00', NULL,
                  TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
             "DELIVERY_TIME",
          PLL.ATTRIBUTE2 "RAIL_CAR",
          POL.UNIT_PRICE,
          POH.CURRENCY_CODE,
          DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
             "INSPECTION_REQUIRED",
          POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
          PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
          POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER
             "NOTE_TO_RECEIVER",
          NVL (
             (SELECT gws.jms_inv_sys_code
                FROM jms_gem_wms_sku gws
               WHERE     gws.plant_code = mtp.organization_code
                     AND gws.item_no = MSI.SEGMENT1
                     AND ROWNUM < 2),
             'N')
             "WMS_ITEM",
          RSL.PACKING_SLIP "PACKING_SLIP",
          RSh.BILL_OF_LADING "BILL_OF_LADING",
          RSH.SHIPMENT_NUM "ASN_SHIPMENT_NUM",
          rsh.freight_carrier_code "FREIGHT_CARRIER_CODE",
          rsh.carrier_equipment "SCAC",
          rsh.num_of_containers "PALLET_COUNT",
          rsl.truck_num "ASN_TRUCK_NUM",
          rsl.container_num "ASN_SEAL",
          RSL.asn_line_flag "ASN_FLAG",
          --pol_fob.displayed_field "FOB_DSP",
          rsh.shipped_date "EXPO_DATE",
          rsl.comments "ASN_LINE_COMMENTS",
          rsh.comments "ASN_HEADER_COMMENTS",
          poh.freight_terms_lookup_code "INCO_TERMS",
          'ASN' "ORDER_TYPE",
          pra.attribute15 "TRANSPORTATION_NOTES",
          'BLANKET_PO_ASN_NOT_RECEIVED' QUERY_STATUS,
          rsh.special_handling_code IRP_STW_IDENTIFIER,
          NULL RECEIPT_WITH_ASN_RECEIVED
     FROM RCV_SHIPMENT_HEADERS RSH,
          rcv_shipment_lines rsl,
          PO_VENDORS PV,
          ap_supplier_sites_all asa,
          --ap_supplier_contacts sup_con,
          --JMS_PER_ALL_PEOPLE_F PAP,
          --HR.HR_LOCATIONS_ALL HL,
          PO_LINE_LOCATIONS_ALL PLL,
          MTL_SYSTEM_ITEMS MSI,
          --JMS_OPM_ITEM_CATEGORY_V OICV,
          PO_RELEASES_ALL PRA,
          MTL_PARAMETERS MTP,
          --HR_ALL_ORGANIZATION_UNITS HO,
          PO_HEADERS_ALL POH,
          PO_LINES_ALL POL,
          --PO_LOOKUP_CODES POL_FOB--,
          ORG_ORGANIZATION_DEFINITIONS OOD
    WHERE     1 = 1
          AND MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
          -- AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
          AND POH.VENDOR_ID = PV.VENDOR_ID
          AND poh.vendor_site_id = asa.vendor_site_id
          AND asa.address_line4 = ood.organization_code(+)
          AND asa.vendor_id = pv.vendor_id
          -- AND sup_con.vendor_contact_id(+) = poh.vendor_contact_id
          --AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID
          --AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID
          --AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS'
          AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
          AND POL.PO_LINE_ID = PLL.PO_LINE_ID
          AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
          AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
          AND MSI.ORGANIZATION_ID = 0
          --AND PRA.AGENT_ID = PAP.PERSON_ID
          --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
          AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID
          AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
          AND PLL.SHIPMENT_TYPE = 'BLANKET'
          AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                    'CLOSED',
                                                    'CLOSED FOR RECEIVING')
          AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
          AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
          AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >
                 0
          AND NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
          AND rsl.PO_LINE_LOCATION_ID = pll.line_location_id
          AND rsl.quantity_shipped - rsl.quantity_received > 0
          AND rsl.shipment_line_status_code = 'EXPECTED'
          AND rsl.asn_line_flag = 'Y'
          AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
   --AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
   --AND POL_FOB.LOOKUP_TYPE(+) = 'FOB'
   UNION ALL
   --Blanket Release has ASN and Receipt created but not Delivered to Warehouse by XENATARA
   SELECT  /*+ FIRST_ROWS */                                    --HO.NAME "INVENTORY_ORG_NAME",
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
          --HL.LOCATION_CODE "LOCATION",
          MSI.SEGMENT1 "ITEM_NO",
          MSI.DESCRIPTION "ITEM_DESCRIPTION",
          --OICV.CATEGORY "INV_CLASS",
          PLL.QUANTITY "QUANTITY_ORDERED",
          PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
          PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
          rsl.quantity_shipped "QUANTITY_SHIPPED",
          --rsl.quantity_shipped                                                                          "QUANTITY_DUE",      --03/28/2016 by XVKIRAN
          (  NVL (PLL.QUANTITY, 0)
           - NVL (PLL.QUANTITY_CANCELLED, 0)
           - rsl.quantity_shipped)
             "QUANTITY_DUE",                           --03/28/2016 by XVKIRAN
          POL.UNIT_MEAS_LOOKUP_CODE "UOM",
          POH.SEGMENT1 || '-' || PRA.RELEASE_NUM "DOCUMENT_NO",
          PRA.REVISION_NUM "REVISION",
          NVL (PRA.CREATION_DATE, POH.CREATION_DATE) "ORDER_DATE", --- Added on02/04/2016 by XVKIRAN
          PLL.PROMISED_DATE "PROMISED_DATE", --- Added on02/04/2016 by XVKIRAN
          NVL (RSH.EXPECTED_RECEIPT_DATE,
               NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE))
             "EXPECTED_DATE",
          NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
          PV.SEGMENT1 "SUPPLIER_NO",
          PV.VENDOR_NAME "SUPPLIER_NAME",
          asa.vendor_site_code "SUPPLIER_SITE_CODE",
          ood.organization_code "SUPPLIER_WHSE_CODE", --- Added on02/04/2016 by XVKIRAN
          ood.organization_name "SUPPLIER_WHSE_NAME", --- Added on02/04/2016 by XVKIRAN
          --sup_con.email_address "SUPPLIER_EMAIL",
          --PAP.FULL_NAME "BUYER_NAME",
          'BLANKET RELEASE' "DOCUMENT_TYPE",
          POL.LINE_NUM,
          PLL.SHIPMENT_NUM,
          DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                  '00:00', NULL,
                  TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
             "DELIVERY_TIME",
          PLL.ATTRIBUTE2 "RAIL_CAR",
          POL.UNIT_PRICE,
          POH.CURRENCY_CODE,
          DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
             "INSPECTION_REQUIRED",
          POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
          PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
          POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER
             "NOTE_TO_RECEIVER",
          NVL (
             (SELECT gws.jms_inv_sys_code
                FROM jms_gem_wms_sku gws
               WHERE     gws.plant_code = mtp.organization_code
                     AND gws.item_no = MSI.SEGMENT1
                     AND ROWNUM < 2),
             'N')
             "WMS_ITEM",
          RSL.PACKING_SLIP "PACKING_SLIP",
          RSh.BILL_OF_LADING "BILL_OF_LADING",
          RSH.SHIPMENT_NUM "ASN_SHIPMENT_NUM",
          rsh.freight_carrier_code "FREIGHT_CARRIER_CODE",
          rsh.carrier_equipment "SCAC",      --- Added on02/04/2016 by XVKIRAN
          rsh.num_of_containers "PALLET_COUNT", --- Added on02/04/2016 by XVKIRAN
          rsl.truck_num "ASN_TRUCK_NUM",     --- Added on02/04/2016 by XVKIRAN
          rsl.container_num "ASN_SEAL",     --- Added on 03/14/2016 by XVKIRAN
          RSL.asn_line_flag "ASN_FLAG",
          --pol_fob.displayed_field "FOB_DSP",
          rsh.shipped_date "EXPO_DATE",
          rsl.comments "ASN_LINE_COMMENTS",
          rsh.comments "ASN_HEADER_COMMENTS",
          poh.freight_terms_lookup_code "INCO_TERMS",
          'ASNRCVD' "ORDER_TYPE",
          pra.attribute15 "TRANSPORTATION_NOTES" --- Added on 03/14/2016 by XVKIRAN
                                                ,
          'BLANKET_PO_ASN_RECEIVED_NOT_DELIVERED' QUERY_STATUS --- Added on 04/21/2016 by XVKIRAN
                                                              ,
          rsh.special_handling_code IRP_STW_IDENTIFIER --- Added on 04/21/2016 by XVKIRAN
                                                      ,
          NULL RECEIPT_WITH_ASN_RECEIVED     -- Added by XPTOMAR on 10/08/2016
     FROM RCV_SHIPMENT_HEADERS RSH,
          rcv_shipment_lines rsl,
          PO_VENDORS PV,
          ap_supplier_sites_all asa,
          --ap_supplier_contacts sup_con,
          --JMS_PER_ALL_PEOPLE_F PAP,
          --HR.HR_LOCATIONS_ALL HL,
          PO_LINE_LOCATIONS_ALL PLL,
          MTL_SYSTEM_ITEMS MSI,
          --JMS_OPM_ITEM_CATEGORY_V OICV,
          PO_RELEASES_ALL PRA,
          MTL_PARAMETERS MTP,
          --HR_ALL_ORGANIZATION_UNITS HO,
          PO_HEADERS_ALL POH,
          PO_LINES_ALL POL,
          -- PO_LOOKUP_CODES POL_FOB,
          ORG_ORGANIZATION_DEFINITIONS OOD   --- Added on02/04/2016 by XVKIRAN
    WHERE     MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
          --AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
          AND POH.VENDOR_ID = PV.VENDOR_ID
          AND poh.vendor_site_id = asa.vendor_site_id
          AND asa.address_line4 = ood.organization_code(+) --- Added on 02/04/2016 by XVKIRAN
          AND asa.vendor_id = pv.vendor_id
          --AND sup_con.vendor_contact_id(+) = poh.vendor_contact_id
          --AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID
          --AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID
          --AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS'
          AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
          AND POL.PO_LINE_ID = PLL.PO_LINE_ID
          AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
          AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
          AND MSI.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
          --AND PRA.AGENT_ID = PAP.PERSON_ID
          --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
          AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID
          AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
          AND PLL.SHIPMENT_TYPE = 'BLANKET'
          AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                    'CLOSED',
                                                    'CLOSED FOR RECEIVING')
          AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
          AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
          --AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >= 0 -- Commented for Defect#62
          AND NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
          AND rsl.PO_LINE_LOCATION_ID = pll.line_location_id
          AND rsl.quantity_shipped = rsl.quantity_received
          AND NOT EXISTS
                     (SELECT 1
                        FROM rcv_transactions rt
                       WHERE     rt.shipment_header_id =
                                    rsl.shipment_header_id
                             AND rt.shipment_line_id = rsl.shipment_line_id
                             AND rt.transaction_type IN ('RETURN TO VENDOR',
                                                         'DELIVER'))
          AND EXISTS
                 (SELECT 1
                    FROM rcv_transactions rt
                   WHERE     rt.shipment_header_id = rsl.shipment_header_id
                         AND rt.shipment_line_id = rsl.shipment_line_id
                         AND rt.transaction_type IN ('RETURN TO RECEIVING',
                                                     'RECEIVE'))
          AND rsl.shipment_line_status_code = 'FULLY RECEIVED'
          AND rsl.asn_line_flag = 'Y'
          AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
   --AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
   --AND POL_FOB.LOOKUP_TYPE(+) = 'FOB'
   UNION ALL
   -- EXPECTED RECEIPTS FROM BLANKET RELEASES
   -- with an asn, and there is quantity remaining that is not covered by the ASN
   SELECT /*+ FIRST_ROWS */ --MTP.PROCESS_ORGN_CODE                                                                      "PLANT_CODE",             -- 09/29/2011 ----Modified for R12
 --HO.NAME                                                                                       "INVENTORY_ORG",        -- 07/23/2012 Modified for R12
       --HO.NAME "INVENTORY_ORG_NAME",          -- 07/23/2012 Modified for R12
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
         --HL.LOCATION_CODE "LOCATION",
         MSI.SEGMENT1 "ITEM_NO",
         MSI.DESCRIPTION "ITEM_DESCRIPTION",
         --IIM.INV_CLASS,                                                                                                         -- 09/29/2011 ----Modified for R12
         --OICV.CATEGORY "INV_CLASS",         -- 09/29/2011 ----Modified for R12
         PLL.QUANTITY "QUANTITY_ORDERED",
         PLL.QUANTITY_RECEIVED,
         pll.quantity_cancelled,
         asn_sum.quantity_shipped,
         (  NVL (PLL.QUANTITY, 0)
          - NVL (PLL.QUANTITY_CANCELLED, 0) --- NVL (PLL.QUANTITY_RECEIVED, 0)
          - asn_sum.quantity_shipped)
            "QUANTITY_DUE",
         --NVL(PLL.QUANTITY,0) - NVL(PLL.QUANTITY_CANCELLED,0) - asn_sum.quantity_shipped                "QUANTITY_DUE",
         POL.UNIT_MEAS_LOOKUP_CODE "UOM",
         POH.SEGMENT1 || '-' || PRA.RELEASE_NUM "DOCUMENT_NO",
         PRA.REVISION_NUM "REVISION",
         NVL (PRA.CREATION_DATE, POH.CREATION_DATE) "ORDER_DATE", --- Added on02/04/2016 by XVKIRAN
         PLL.PROMISED_DATE "PROMISED_DATE",  --- Added on02/04/2016 by XVKIRAN
         NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE) "EXPECTED_DATE",
         NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
         PV.SEGMENT1 "SUPPLIER_NO",
         PV.VENDOR_NAME "SUPPLIER_NAME",
         asa.vendor_site_code "SUPPLIER_SITE_CODE",                --DTR#55945
         ood.organization_code "SUPPLIER_WHSE_CODE", --- Added on02/04/2016 by XVKIRAN
         ood.organization_name "SUPPLIER_WHSE_NAME", --- Added on02/04/2016 by XVKIRAN
         --sup_con.email_address "SUPPLIER_EMAIL",                   --DTR#55945
         --PAP.FULL_NAME "BUYER_NAME",
         'BLANKET RELEASE' "DOCUMENT_TYPE",
         POL.LINE_NUM,
         PLL.SHIPMENT_NUM,
         DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                 '00:00', NULL,
                 TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
            "DELIVERY_TIME",
         PLL.ATTRIBUTE2 "RAIL_CAR",
         POL.UNIT_PRICE,
         POH.CURRENCY_CODE,
         DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
            "INSPECTION_REQUIRED",
         POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
         PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
         POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER "NOTE_TO_RECEIVER",
         NVL (
            (SELECT gws.jms_inv_sys_code
               FROM jms_gem_wms_sku gws
              --WHERE gws.plant_code = MTP.PROCESS_ORGN_CODE                                                                    -- 09/29/2011 ----Modified for R12
              WHERE     gws.plant_code = mtp.organization_code -- 09/29/2011 ----Modified for R12
                    AND gws.item_no = MSI.SEGMENT1
                    AND ROWNUM < 2),
            'N')
            "WMS_ITEM",
         NULL "PACKING_SLIP",
         NULL "BILL_OF_LADING",
         NULL "SHIPMENT_NUM",
         NULL "FREIGHT_CARRIER_CODE",
         NULL "SCAC",                        --- Added on02/04/2016 by XVKIRAN
         NULL "PALLET_COUNT",                --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_TRUCK_NUM",               --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_SEAL",                   --- Added on 03/14/2016 by XVKIRAN
         NULL "ASN_FLAG",
         --POL_FOB.DISPLAYED_FIELD "FOB_DSP",
         NULL "EXPO_DATE",         --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_LINE_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_HEADER_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         poh.freight_terms_lookup_code "INCO_TERMS", --01/22/2013: Modified for R12 for ESS view
         'PO' "ORDER_TYPE"                                         --DTR#55945
                          ,
         pra.attribute15 "TRANSPORTATION_NOTES" --- Added on 03/14/2016 by XVKIRAN
                                               ,
         'BLANKET_PO_ASN_RECEIVED_QTY_DUE' QUERY_STATUS --- Added on 04/21/2016 by XVKIRAN
                                                       ,
         (SELECT rsh.special_handling_code
            FROM rcv_shipment_headers rsh, rcv_shipment_lines rsl
           WHERE     1 = 1
                 AND rsh.special_handling_code IN ('MAN', 'EDI')
                 AND rsh.shipment_header_id = rsl.shipment_header_id
                 AND rsl.po_header_id = poh.PO_HEADER_ID
                 AND rsl.po_release_id = pra.po_release_id)
            IRP_STW_IDENTIFIER              --- Added on 04/21/2016 by XVKIRAN
                              ,
         NULL RECEIPT_WITH_ASN_RECEIVED      -- Added by XPTOMAR on 10/08/2016
    FROM PO_VENDORS PV,
         ap_supplier_sites_all asa,                                --DTR#55945
         --ap_supplier_contacts sup_con,                             --DTR#55945
         --JMS_PER_ALL_PEOPLE_F PAP,
         --HR.HR_LOCATIONS_ALL HL,
         PO_LINE_LOCATIONS_ALL PLL,
         MTL_SYSTEM_ITEMS MSI,
         --IC_ITEM_MST IIM,                                                                                                      -- 09/29/2011 ----Modified for R12
         --JMS_OPM_ITEM_CATEGORY_V OICV,      -- 09/29/2011 ----Modified for R12
         PO_RELEASES_ALL PRA,
         MTL_PARAMETERS MTP,
         --HR_ALL_ORGANIZATION_UNITS HO,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         (  SELECT PO_LINE_LOCATION_ID,
                   SUM (rsl.quantity_shipped) quantity_shipped
              FROM rcv_shipment_lines rsl
             WHERE     1 = 1
                   --AND rsl.quantity_shipped - rsl.quantity_received <> 0
                   AND rsl.shipment_line_status_code = 'EXPECTED'
                   AND rsl.asn_line_flag = 'Y'
          GROUP BY po_line_location_id) asn_sum,
         --PO_LOOKUP_CODES POL_FOB,
         ORG_ORGANIZATION_DEFINITIONS OOD    --- Added on02/04/2016 by XVKIRAN
   WHERE     1 = 2                          -- Added by XPTOMAR on 08/26/2016.
         -- POs returned by this query were also returned by BLANKET_PO_ASN_NOT_RECEIVED query
         -- so cancelled the results of this query.
         AND MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
         --AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
         AND POH.VENDOR_ID = PV.VENDOR_ID
         --DTR#55945
         AND poh.vendor_site_id = asa.vendor_site_id
         AND asa.address_line4 = ood.organization_code(+) --- Added on 02/04/2016 by XVKIRAN
         AND asa.vendor_id = pv.vendor_id
         --AND sup_con.vendor_contact_id = poh.vendor_contact_id(+)    --- Commented on 01/26/2016 by XENATARA
         --AND sup_con.vendor_contact_id(+) = poh.vendor_contact_id --- Added on 01/26/2016 by XENATARA
         --
         --AND IIM.ITEM_NO = MSI.SEGMENT1                                                                                        -- 09/29/2011 ----Modified for R12
         --AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID -- 09/29/2011 ----Modified for R12
         --AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE = 'INV_CLASS'                                                                                  -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS' -- 07/23/2012 Modified for R12
         AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
         AND POL.PO_LINE_ID = PLL.PO_LINE_ID
         AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
         AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
         AND MSI.ORGANIZATION_ID = 0       -- Must be setup business group (0)
         --AND PRA.AGENT_ID = PAP.PERSON_ID
         --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
         AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID
         AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
         AND PLL.SHIPMENT_TYPE = 'BLANKET'
         AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                   'CLOSED',
                                                   'CLOSED FOR RECEIVING')
         AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
         AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
         AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >
                0
         AND NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
         AND asn_sum.PO_LINE_LOCATION_ID = pll.line_location_id
         AND (  NVL (PLL.QUANTITY, 0)
              - NVL (PLL.QUANTITY_CANCELLED, 0)
              - NVL (PLL.QUANTITY_RECEIVED, 0)
              - NVL (asn_sum.quantity_shipped, 0)) > 0
   --AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
   --AND POL_FOB.LOOKUP_TYPE(+) = 'FOB'
   UNION ALL
   -- expected receipts FROM RELEASES without an asn
   SELECT /*+ FIRST_ROWS */ --MTP.PROCESS_ORGN_CODE                                                                       "PLANT_CODE",             -- 09/29/2011 ----Modified for R12
 --HO.NAME                                                                                       "INVENTORY_ORG",        -- 07/23/2012 Modified for R12
       --HO.NAME "INVENTORY_ORG_NAME",          -- 07/23/2012 Modified for R12
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
         --HL.LOCATION_CODE "LOCATION",
         MSI.SEGMENT1 "ITEM_NO",
         MSI.DESCRIPTION "ITEM_DESCRIPTION",
         --IIM.INV_CLASS,                                                                                                        -- 09/29/2011 ----Modified for R12
         --OICV.CATEGORY "INV_CLASS",         -- 09/29/2011 ----Modified for R12
         PLL.QUANTITY "QUANTITY_ORDERED",
         PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
         PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
         0 "QUANTITY_SHIPPED",
           NVL (PLL.QUANTITY, 0)
         - NVL (PLL.QUANTITY_CANCELLED, 0)
         - NVL (PLL.QUANTITY_RECEIVED, 0)
            "QUANTITY_DUE",
         POL.UNIT_MEAS_LOOKUP_CODE "UOM",
         POH.SEGMENT1 || '-' || PRA.RELEASE_NUM "DOCUMENT_NO",
         PRA.REVISION_NUM "REVISION",
         NVL (PRA.CREATION_DATE, POH.CREATION_DATE) "ORDER_DATE", --- Added on02/04/2016 by XVKIRAN
         PLL.PROMISED_DATE "PROMISED_DATE",  --- Added on02/04/2016 by XVKIRAN
         NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE) "EXPECTED_DATE",
         NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
         PV.SEGMENT1 "SUPPLIER_NO",
         PV.VENDOR_NAME "SUPPLIER_NAME",
         asa.vendor_site_code "SUPPLIER_SITE_CODE",                --DTR#55945
         ood.organization_code "SUPPLIER_WHSE_CODE", --- Added on02/04/2016 by XVKIRAN
         ood.organization_name "SUPPLIER_WHSE_NAME", --- Added on02/04/2016 by XVKIRAN
         --sup_con.email_address "SUPPLIER_EMAIL",                   --DTR#55945
         --PAP.FULL_NAME "BUYER_NAME",
         'BLANKET RELEASE' "DOCUMENT_TYPE",
         POL.LINE_NUM,
         PLL.SHIPMENT_NUM,
         DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                 '00:00', NULL,
                 TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
            "DELIVERY_TIME",
         PLL.ATTRIBUTE2 "RAIL_CAR",
         POL.UNIT_PRICE,
         POH.CURRENCY_CODE,
         DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
            "INSPECTION_REQUIRED",
         POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
         PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
         POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER "NOTE_TO_RECEIVER",
         NVL (
            (SELECT gws.jms_inv_sys_code
               FROM jms_gem_wms_sku gws
              --WHERE gws.plant_code = MTP.PROCESS_ORGN_CODE                                                                    -- 09/29/2011 ----Modified for R12
              WHERE     gws.plant_code = mtp.organization_code -- 09/29/2011 ----Modified for R12
                    AND gws.item_no = MSI.SEGMENT1
                    AND ROWNUM < 2),
            'N')
            "WMS_ITEM",
         NULL "PACKING_SLIP",
         NULL "BILL_OF_LADING",
         NULL "SHIPMENT_NUM",
         NULL "FREIGHT_CARRIER_CODE",
         NULL "SCAC",                        --- Added on02/04/2016 by XVKIRAN
         NULL "PALLET_COUNT",                --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_TRUCK_NUM",               --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_SEAL",                   --- Added on 03/14/2016 by XVKIRAN
         NULL "ASN_FLAG",
         --POL_FOB.DISPLAYED_FIELD "FOB_DSP",
         NULL "EXPO_DATE",         --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_LINE_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_HEADER_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         poh.freight_terms_lookup_code "INCO_TERMS", --01/22/2013: Modified for R12 for ESS view
         'PO' "ORDER_TYPE"                                         --DTR#55945
                          ,
         pra.attribute15 "TRANSPORTATION_NOTES" --- Added on 03/14/2016 by XVKIRAN
                                               ,
         'BLANKET_PO_NO_ASN' QUERY_STATUS   --- Added on 04/21/2016 by XVKIRAN
                                         ,
         NULL IRP_STW_IDENTIFIER            --- Added on 04/21/2016 by XVKIRAN
                                ,
         DECODE (rsh.asn_status, 'FULLY RECEIVED', 'YES', NULL)
            RECEIPT_WITH_ASN_RECEIVED
    FROM PO_VENDORS PV,
         ap_supplier_sites_all asa,                                --DTR#55945
         --ap_supplier_contacts sup_con,                             --DTR#55945
         --JMS_PER_ALL_PEOPLE_F PAP,
         -- HR.HR_LOCATIONS_ALL HL,
         PO_LINE_LOCATIONS_ALL PLL,
         MTL_SYSTEM_ITEMS MSI,
         --IC_ITEM_MST IIM,                                                                                                      -- 09/29/2011 ----Modified for R12
         --JMS_OPM_ITEM_CATEGORY_V OICV,      -- 09/29/2011 ----Modified for R12
         PO_RELEASES_ALL PRA,
         MTL_PARAMETERS MTP,
         HR_ALL_ORGANIZATION_UNITS HO,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         --PO_LOOKUP_CODES POL_FOB,
         ORG_ORGANIZATION_DEFINITIONS OOD    --- Added on02/04/2016 by XVKIRAN
                                         ,
         (SELECT DISTINCT
                 shipment_line_status_code asn_status, po_line_location_id
            FROM rcv_shipment_lines rsl
           WHERE     asn_line_flag = 'Y'
                 AND shipment_line_status_code IN ('FULLY RECEIVED')
                 AND EXISTS
                        (SELECT 1
                           FROM rcv_transactions rt
                          WHERE     rt.shipment_header_id =
                                       rsl.shipment_header_id
                                AND rt.shipment_line_id = rsl.shipment_line_id
                                AND rt.transaction_type IN ('RETURN TO VENDOR',
                                                            'DELIVER'))) rsh -- Added by XPTOMAR on 10/08/2016
   WHERE     1 = 1
         AND pll.line_location_id = rsh.po_line_location_id(+) -- Added by XPTOMAR on 10/08/2016
         AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >
                0
         AND (   (0 <
                     (SELECT COUNT (1)
                        FROM rcv_transactions rt, rcv_shipment_lines rsl
                       WHERE     1 = 1
                             AND rsl.po_line_location_id =
                                    pll.line_location_id
                             AND rsl.shipment_header_id =
                                    rt.shipment_header_id
                             AND rsl.shipment_line_id = rt.shipment_line_id
                             AND rt.transaction_type IN ('DELIVER'))) --('RETURN TO RECEIVING','RECEIVE')
              OR (0 =
                     (SELECT COUNT (1)
                        FROM rcv_transactions rt, rcv_shipment_lines rsl
                       WHERE     1 = 1
                             AND rsl.po_line_location_id =
                                    pll.line_location_id
                             AND rsl.shipment_header_id =
                                    rt.shipment_header_id
                             AND rsl.shipment_line_id = rt.shipment_line_id)))
         AND MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
         AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
         AND POH.VENDOR_ID = PV.VENDOR_ID
         --DTR#55945
         AND poh.vendor_site_id = asa.vendor_site_id
         AND asa.address_line4 = ood.organization_code(+) --- Added on 02/04/2016 by XVKIRAN
         AND asa.vendor_id = pv.vendor_id
         --AND sup_con.vendor_contact_id = poh.vendor_contact_id(+)   --- Commented on 01/26/2016 by XENATARA
         --AND sup_con.vendor_contact_id(+) = poh.vendor_contact_id --- Added  on 01/26/2016 by XENATARA
         --AND IIM.ITEM_NO = MSI.SEGMENT1                                                                                        -- 09/29/2011 ----Modified for R12
         --AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID -- 09/29/2011 ----Modified for R12
         --AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE = 'INV_CLASS'                                                                                  -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS' -- 07/23/2012 Modified for R12
         AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
         AND POL.PO_LINE_ID = PLL.PO_LINE_ID
         AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
         AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
         AND MSI.ORGANIZATION_ID = 0       -- Must be setup business group (0)
         --AND PRA.AGENT_ID = PAP.PERSON_ID
         --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
         AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID
         AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
         AND PLL.SHIPMENT_TYPE = 'BLANKET'
         AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                   'CLOSED',
                                                   'CLOSED FOR RECEIVING')
         AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
         AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
         AND NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
         AND NOT EXISTS
                    (SELECT 'NO ASN'
                       FROM rcv_shipment_lines rsl
                      WHERE     1 = 1
                            AND rsl.PO_LINE_LOCATION_ID =
                                   pll.line_location_id -- commented by XVKIRAN on 04/21/2016
                            --AND rsl.quantity_shipped - rsl.quantity_received <> 0     -- commented by XVKIRAN on 04/21/2016
                            --AND rsl.shipment_line_status_code = 'EXPECTED' -- commented by XVKIRAN on 04/21/2016
                            --AND rsl.PO_header_ID = poh.po_header_id
                            --AND rsl.PO_release_ID = pra.po_release_id
                            --AND rsl.shipment_line_status_code != 'CANCELLED' -- added by XVKIRAN on 04/21/2016
                            AND rsl.shipment_line_status_code NOT IN ('CANCELLED',
                                                                      'FULLY RECEIVED') -- Added by XPTOMAR on 10/08/2016
                            AND rsl.asn_line_flag = 'Y')
   --AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
   --AND POL_FOB.LOOKUP_TYPE(+) = 'FOB'
   UNION ALL
   -- EXPECTED RECEIPT FROM STANDARD PO - WITH ASN
   SELECT /*+ FIRST_ROWS */--MTP.PROCESS_ORGN_CODE              "PLANT_CODE",                                                                      -- 09/29/2011 ----Modified for R12
 --HO.NAME                                                                                   "INVENTORY_ORG",            -- 07/23/2012 Modified for R12
       --HO.NAME "INVENTORY_ORG_NAME",          -- 07/23/2012 Modified for R12
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
         --HL.LOCATION_CODE "LOCATION",
         MSI.SEGMENT1 "ITEM_NO",
         MSI.DESCRIPTION "ITEM_DESCRIPTION",
         --IIM.INV_CLASS,                                                                                                        -- 09/29/2011 ----Modified for R12
         --OICV.CATEGORY "INV_CLASS",         -- 09/29/2011 ----Modified for R12
         PLL.QUANTITY "QUANTITY_ORDERED",
         PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
         PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
         rsl.quantity_shipped "QUANTITY_SHIPPED",
         rsl.quantity_shipped "QUANTITY_DUE",
         POL.UNIT_MEAS_LOOKUP_CODE "UOM",
         POH.SEGMENT1 "DOCUMENT_NO",
         POH.REVISION_NUM "REVISION",
         POH.CREATION_DATE "ORDER_DATE",     --- Added on02/04/2016 by XVKIRAN
         PLL.PROMISED_DATE "PROMISED_DATE",  --- Added on02/04/2016 by XVKIRAN
         NVL (RSH.EXPECTED_RECEIPT_DATE,
              NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE))
            "EXPECTED_DATE",
         NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
         PV.SEGMENT1 "SUPPLIER_NO",
         PV.VENDOR_NAME "SUPPLIER_NAME",
         asa.vendor_site_code "SUPPLIER_SITE_CODE",                --DTR#55945
         ood.organization_code "SUPPLIER_WHSE_CODE", --- Added on02/04/2016 by XVKIRAN
         ood.organization_name "SUPPLIER_WHSE_NAME", --- Added on02/04/2016 by XVKIRAN
         --sup_con.email_address "SUPPLIER_EMAIL",                   --DTR#55945
         --PAP.FULL_NAME "BUYER_NAME",
         'STANDARD PO' "DOCUMENT_TYPE",
         POL.LINE_NUM,
         PLL.SHIPMENT_NUM,
         DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                 '00:00', NULL,
                 TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
            "DELIVERY_TIME",
         PLL.ATTRIBUTE2 "RAIL_CAR",
         POL.UNIT_PRICE,
         POH.CURRENCY_CODE,
         DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
            "INSPECTION_REQUIRED",
         POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
         PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
         POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER "NOTE_TO_RECEIVER",
         NVL (
            (SELECT gws.jms_inv_sys_code
               FROM jms_gem_wms_sku gws
              --WHERE gws.plant_code = MTP.PROCESS_ORGN_CODE                                                                    -- 09/29/2011 ----Modified for R12
              WHERE     gws.plant_code = mtp.organization_code -- 09/29/2011 ----Modified for R12
                    AND gws.item_no = MSI.SEGMENT1
                    AND ROWNUM < 2),
            'N')
            "WMS_ITEM",
         RSL.PACKING_SLIP "PACKING_SLIP",
         RSh.BILL_OF_LADING "BILL_OF_LADING",
         RSH.SHIPMENT_NUM "SHIPMENT_NUM",
         rsh.freight_carrier_code "FREIGHT_CARRIER_CODE",
         rsh.carrier_equipment "SCAC",       --- Added on02/04/2016 by XVKIRAN
         rsh.num_of_containers "PALLET_COUNT", --- Added on02/04/2016 by XVKIRAN
         rsl.truck_num "ASN_TRUCK_NUM",      --- Added on02/04/2016 by XVKIRAN
         rsl.container_num "ASN_SEAL",      --- Added on 03/14/2016 by XVKIRAN
         RSL.asn_line_flag "ASN_FLAG",
         --POL_FOB.DISPLAYED_FIELD "FOB_DSP",
         rsh.shipped_date "EXPO_DATE", --01/22/2013: Modified for R12 for ESS view
         rsl.comments "ASN_LINE_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         rsh.comments "ASN_HEADER_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         poh.freight_terms_lookup_code "INCO_TERMS", --01/22/2013: Modified for R12 for ESS view
         'ASN' "ORDER_TYPE"                                        --DTR#55945
                           ,
         NULL "TRANSPORTATION_NOTES"        --- Added on 03/14/2016 by XVKIRAN
                                    ,
         'STANDARD_PO_ASN_NOT_RECEIVED' QUERY_STATUS --- Added on 04/21/2016 by XVKIRAN
                                                    ,
         NULL IRP_STW_IDENTIFIER            --- Added on 04/21/2016 by XVKIRAN
                                ,
         NULL RECEIPT_WITH_ASN_RECEIVED      -- Added by XPTOMAR on 10/08/2016
    FROM RCV_SHIPMENT_HEADERS RSH,
         rcv_shipment_lines rsl,
         PO_VENDORS PV,
         ap_supplier_sites_all asa,                                --DTR#55945
         --ap_supplier_contacts sup_con,                             --DTR#55945
         --JMS_PER_ALL_PEOPLE_F PAP,
         --HR.HR_LOCATIONS_ALL HL,
         PO_LINE_LOCATIONS_ALL PLL,
         MTL_SYSTEM_ITEMS MSI,
         --IC_ITEM_MST IIM,                                                                                                      -- 09/29/2011 ----Modified for R12
         --JMS_OPM_ITEM_CATEGORY_V OICV,      -- 09/29/2011 ----Modified for R12
         MTL_PARAMETERS MTP,
         --HR_ALL_ORGANIZATION_UNITS HO,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         --PO_LOOKUP_CODES POL_FOB,
         ORG_ORGANIZATION_DEFINITIONS OOD    --- Added on02/04/2016 by XVKIRAN
   WHERE     MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
         --AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
         AND POH.VENDOR_ID = PV.VENDOR_ID
         --DTR#55945
         AND poh.vendor_site_id = asa.vendor_site_id
         AND asa.address_line4 = ood.organization_code(+) --- Added on 02/04/2016 by XVKIRAN
         AND asa.vendor_id = pv.vendor_id
         --AND sup_con.vendor_contact_id = poh.vendor_contact_id(+)    --- Commented on 01/26/2016 by XENATARA
         --AND sup_con.vendor_contact_id(+) = poh.vendor_contact_id --- Added on 01/26/2016 by XENATARA
         AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
         AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
         AND POL.PO_LINE_ID = PLL.PO_LINE_ID
         AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
         --AND IIM.ITEM_NO = MSI.SEGMENT1                                                                                        -- 09/29/2011 ----Modified for R12
         --AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID -- 09/29/2011 ----Modified for R12
         --AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE = 'INV_CLASS'                                                                                  -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS' -- 07/23/2012 Modified for R12
         AND MSI.ORGANIZATION_ID = 0       -- Must be setup business group (0)
         --AND POH.AGENT_ID = PAP.PERSON_ID
         --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
         AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                   'CLOSED',
                                                   'CLOSED FOR RECEIVING')
         AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
         AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
         AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >
                0
         AND POH.TYPE_LOOKUP_CODE = 'STANDARD'
         AND PLL.SHIPMENT_TYPE = 'STANDARD'
         AND NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
         AND rsl.PO_LINE_LOCATION_ID = pll.line_location_id
         AND rsl.quantity_shipped - rsl.quantity_received <> 0
         AND rsl.shipment_line_status_code = 'EXPECTED'
         AND rsl.asn_line_flag = 'Y'
         AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
   --AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
   --AND POL_FOB.LOOKUP_TYPE(+) = 'FOB'
   UNION ALL
   -- EXPECTED RECEIPTS FROM STANDARD PO
   -- with an asn, and there is quantity remaining that is not covered by the ASN
   SELECT /*+ FIRST_ROWS */--MTP.PROCESS_ORGN_CODE              "PLANT_CODE",                                                                      -- 09/29/2011 ----Modified for R12
 --HO.NAME                                                                                   "INVENTORY_ORG",            -- 07/23/2012 Modified for R12
       --HO.NAME "INVENTORY_ORG_NAME",          -- 07/23/2012 Modified for R12
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
         --HL.LOCATION_CODE "LOCATION",
         MSI.SEGMENT1 "ITEM_NO",
         MSI.DESCRIPTION "ITEM_DESCRIPTION",
         --IIM.INV_CLASS,                                                                                                        -- 09/29/2011 ----Modified for R12
         --OICV.CATEGORY "INV_CLASS",         -- 09/29/2011 ----Modified for R12
         PLL.QUANTITY "QUANTITY_ORDERED",
         PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
         PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
         asn_sum.quantity_shipped,
           NVL (PLL.QUANTITY, 0)
         - NVL (PLL.QUANTITY_CANCELLED, 0)
         - NVL (PLL.QUANTITY_RECEIVED, 0)
         - asn_sum.quantity_shipped
            "QUANTITY_DUE",
         POL.UNIT_MEAS_LOOKUP_CODE "UOM",
         POH.SEGMENT1 "DOCUMENT_NO",
         POH.REVISION_NUM "REVISION",
         POH.CREATION_DATE "ORDER_DATE",     --- Added on02/04/2016 by XVKIRAN
         PLL.PROMISED_DATE "PROMISED_DATE",  --- Added on02/04/2016 by XVKIRAN
         NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE) "EXPECTED_DATE",
         NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
         PV.SEGMENT1 "SUPPLIER_NO",
         PV.VENDOR_NAME "SUPPLIER_NAME",
         asa.vendor_site_code "SUPPLIER_SITE_CODE",                --DTR#55945
         ood.organization_code "SUPPLIER_WHSE_CODE", --- Added on02/04/2016 by XVKIRAN
         ood.organization_name "SUPPLIER_WHSE_NAME", --- Added on02/04/2016 by XVKIRAN
         --sup_con.email_address "SUPPLIER_EMAIL",                   --DTR#55945
         --PAP.FULL_NAME "BUYER_NAME",
         'STANDARD PO' "DOCUMENT_TYPE",
         POL.LINE_NUM,
         PLL.SHIPMENT_NUM,
         DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                 '00:00', NULL,
                 TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
            "DELIVERY_TIME",
         PLL.ATTRIBUTE2 "RAIL_CAR",
         POL.UNIT_PRICE,
         POH.CURRENCY_CODE,
         DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
            "INSPECTION_REQUIRED",
         POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
         PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
         POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER "NOTE_TO_RECEIVER",
         NVL (
            (SELECT gws.jms_inv_sys_code
               FROM jms_gem_wms_sku gws
              --WHERE gws.plant_code = MTP.PROCESS_ORGN_CODE                                                                    -- 09/29/2011 ----Modified for R12
              WHERE     gws.plant_code = mtp.organization_code -- 09/29/2011 ----Modified for R12
                    AND gws.item_no = MSI.SEGMENT1
                    AND ROWNUM < 2),
            'N')
            "WMS_ITEM",
         NULL "PACKING_SLIP",
         NULL "BILL_OF_LADING",
         NULL "SHIPMENT_NUM",
         NULL "FREIGHT_CARRIER_CODE",
         NULL "SCAC",                        --- Added on02/04/2016 by XVKIRAN
         NULL "PALLET_COUNT",                --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_TRUCK_NUM",               --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_SEAL",                   --- Added on 03/14/2016 by XVKIRAN
         NULL "ASN_FLAG",
         --POL_FOB.DISPLAYED_FIELD "FOB_DSP",
         NULL "EXPO_DATE",         --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_LINE_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_HEADER_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         poh.freight_terms_lookup_code "INCO_TERMS", --01/22/2013: Modified for R12 for ESS view
         'PO' "ORDER_TYPE"                                         --DTR#55945
                          ,
         NULL "TRANSPORTATION_NOTES"        --- Added on 03/14/2016 by XVKIRAN
                                    ,
         'STANDARD_PO_ASN_RECEIVED_QTY_DUE' QUERY_STATUS --- Added on 04/21/2016 by XVKIRAN
                                                        ,
         NULL IRP_STW_IDENTIFIER            --- Added on 04/21/2016 by XVKIRAN
                                ,
         NULL RECEIPT_WITH_ASN_RECEIVED      -- Added by XPTOMAR on 10/08/2016
    FROM PO_VENDORS PV,
         ap_supplier_sites_all asa,                                --DTR#55945
         --ap_supplier_contacts sup_con,                             --DTR#55945
         --JMS_PER_ALL_PEOPLE_F PAP,
         --HR.HR_LOCATIONS_ALL HL,
         PO_LINE_LOCATIONS_ALL PLL,
         MTL_SYSTEM_ITEMS MSI,
         --IC_ITEM_MST IIM,                                                                                                      -- 09/29/2011 ----Modified for R12
         --JMS_OPM_ITEM_CATEGORY_V OICV,      -- 09/29/2011 ----Modified for R12
         MTL_PARAMETERS MTP,
         --HR_ALL_ORGANIZATION_UNITS HO,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         (  SELECT PO_LINE_LOCATION_ID,
                   SUM (rsl.quantity_shipped) quantity_shipped
              FROM rcv_shipment_lines rsl
             WHERE     rsl.quantity_shipped - rsl.quantity_received <> 0
                   AND rsl.shipment_line_status_code = 'EXPECTED'
                   AND rsl.asn_line_flag = 'Y'
          GROUP BY po_line_location_id) asn_sum,
         --PO_LOOKUP_CODES POL_FOB,
         ORG_ORGANIZATION_DEFINITIONS OOD    --- Added on02/04/2016 by XVKIRAN
   WHERE     MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
         --AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
         AND POH.VENDOR_ID = PV.VENDOR_ID
         --DTR#55945
         AND poh.vendor_site_id = asa.vendor_site_id
         AND asa.address_line4 = ood.organization_code(+) --- Added on 02/04/2016 by XVKIRAN
         AND asa.vendor_id = pv.vendor_id
         --AND asa.vendor_site_id = sup_con.vendor_site_id
         --AND sup_con.vendor_contact_id = poh.vendor_contact_id
         --
         AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
         AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
         AND POL.PO_LINE_ID = PLL.PO_LINE_ID
         AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
         --AND IIM.ITEM_NO = MSI.SEGMENT1                                                                                        -- 09/29/2011 ----Modified for R12
         --AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID -- 09/29/2011 ----Modified for R12
         --AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE = 'INV_CLASS'                                                                                  -- 09/29/2011 ----Modified for R12
         --AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS' -- 07/23/2012 Modified for R12
         AND MSI.ORGANIZATION_ID = 0       -- Must be setup business group (0)
         --AND POH.AGENT_ID = PAP.PERSON_ID
         --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
         AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                   'CLOSED',
                                                   'CLOSED FOR RECEIVING')
         AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
         AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
         AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >
                0
         AND POH.TYPE_LOOKUP_CODE = 'STANDARD'
         AND PLL.SHIPMENT_TYPE = 'STANDARD'
         AND NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
         AND asn_sum.PO_LINE_LOCATION_ID = pll.line_location_id
         AND (  NVL (PLL.QUANTITY, 0)
              - NVL (PLL.QUANTITY_CANCELLED, 0)
              - NVL (PLL.QUANTITY_RECEIVED, 0)
              - NVL (asn_sum.quantity_shipped, 0)) > 0
   --AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
   --AND POL_FOB.LOOKUP_TYPE(+) = 'FOB'
   UNION ALL
   -- EXPECTED RECEIPTS FROM STANDARD PO
   -- WITHOUT AN ASN
   SELECT /*+ FIRST_ROWS */--MTP.PROCESS_ORGN_CODE              "PLANT_CODE",                                                                      -- 09/29/2011 ----Modified for R12
 --HO.NAME                                                                                   "INVENTORY_ORG",            -- 07/23/2012 Modified for R12
      -- HO.NAME "INVENTORY_ORG_NAME",          -- 07/23/2012 Modified for R12
         MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
         --HL.LOCATION_CODE "LOCATION",
         MSI.SEGMENT1 "ITEM_NO",
         MSI.DESCRIPTION "ITEM_DESCRIPTION",
         --IIM.INV_CLASS,                                                                                                        -- 09/29/2011 ----Modified for R12
         --OICV.CATEGORY "INV_CLASS",         -- 09/29/2011 ----Modified for R12
         PLL.QUANTITY "QUANTITY_ORDERED",
         PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
         PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
         0 "QUANTITY_SHIPPED",
           NVL (PLL.QUANTITY, 0)
         - NVL (PLL.QUANTITY_CANCELLED, 0)
         - NVL (PLL.QUANTITY_RECEIVED, 0)
            "QUANTITY_DUE",
         POL.UNIT_MEAS_LOOKUP_CODE "UOM",
         POH.SEGMENT1 "DOCUMENT_NO",
         POH.REVISION_NUM "REVISION",
         POH.CREATION_DATE "ORDER_DATE",     --- Added on02/04/2016 by XVKIRAN
         PLL.PROMISED_DATE "PROMISED_DATE",  --- Added on02/04/2016 by XVKIRAN
         NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE) "EXPECTED_DATE",
         NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
         PV.SEGMENT1 "SUPPLIER_NO",
         PV.VENDOR_NAME "SUPPLIER_NAME",
         asa.vendor_site_code "SUPPLIER_SITE_CODE",                --DTR#55945
         ood.organization_code "SUPPLIER_WHSE_CODE", --- Added on02/04/2016 by XVKIRAN
         ood.organization_name "SUPPLIER_WHSE_NAME", --- Added on02/04/2016 by XVKIRAN
         --sup_con.email_address "SUPPLIER_EMAIL",                   --DTR#55945
         --PAP.FULL_NAME "BUYER_NAME",
         'STANDARD PO' "DOCUMENT_TYPE",
         POL.LINE_NUM,
         PLL.SHIPMENT_NUM,
         DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
                 '00:00', NULL,
                 TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
            "DELIVERY_TIME",
         PLL.ATTRIBUTE2 "RAIL_CAR",
         POL.UNIT_PRICE,
         POH.CURRENCY_CODE,
         DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
            "INSPECTION_REQUIRED",
         POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
         PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
         POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER "NOTE_TO_RECEIVER",
         NVL (
            (SELECT gws.jms_inv_sys_code
               FROM jms_gem_wms_sku gws
              --WHERE gws.plant_code = MTP.PROCESS_ORGN_CODE                                                                    -- 09/29/2011 ----Modified for R12
              WHERE     gws.plant_code = mtp.organization_code -- 09/29/2011 ----Modified for R12
                    AND gws.item_no = MSI.SEGMENT1
                    AND ROWNUM < 2),
            'N')
            "WMS_ITEM",
         NULL "PACKING_SLIP",
         NULL "BILL_OF_LADING",
         NULL "SHIPMENT_NUM",
         NULL "FREIGHT_CARRIER_CODE",
         NULL "SCAC",                        --- Added on02/04/2016 by XVKIRAN
         NULL "PALLET_COUNT",                --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_TRUCK_NUM",               --- Added on02/04/2016 by XVKIRAN
         NULL "ASN_SEAL",                   --- Added on 03/14/2016 by XVKIRAN
         NULL "ASN_FLAG",
         --POL_FOB.DISPLAYED_FIELD "FOB_DSP",
         NULL "EXPO_DATE",         --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_LINE_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         NULL "ASN_HEADER_COMMENTS", --01/22/2013: Modified for R12 for ESS view
         poh.freight_terms_lookup_code "INCO_TERMS", --01/22/2013: Modified for R12 for ESS view
         'PO' "ORDER_TYPE"                                         --DTR#55945
                          ,
         NULL "TRANSPORTATION_NOTES"        --- Added on 03/14/2016 by XVKIRAN
                                    ,
         'STANDARD_PO_NO_ASN' QUERY_STATUS  --- Added on 04/21/2016 by XVKIRAN
                                          ,
         NULL IRP_STW_IDENTIFIER            --- Added on 04/21/2016 by XVKIRAN
                                ,
         NULL RECEIPT_WITH_ASN_RECEIVED      -- Added by XPTOMAR on 10/08/2016
    FROM PO_VENDORS PV,
         ap_supplier_sites_all asa,                                --DTR#55945
         ap_supplier_contacts sup_con,                             --DTR#55945
         --JMS_PER_ALL_PEOPLE_F PAP,
         --HR.HR_LOCATIONS_ALL HL,
         PO_LINE_LOCATIONS_ALL PLL,
         MTL_SYSTEM_ITEMS MSI,
         --IC_ITEM_MST IIM,                                                                                                      -- 09/29/2011 ----Modified for R12
         JMS_OPM_ITEM_CATEGORY_V OICV,      -- 09/29/2011 ----Modified for R12
         MTL_PARAMETERS MTP,
         --HR_ALL_ORGANIZATION_UNITS HO,
         PO_HEADERS_ALL POH,
         PO_LINES_ALL POL,
         --PO_LOOKUP_CODES POL_FOB,
         ORG_ORGANIZATION_DEFINITIONS OOD    --- Added on02/04/2016 by XVKIRAN
   WHERE     MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
         --AND HO.ORGANIZATION_ID = MTP.ORGANIZATION_ID
         AND POH.VENDOR_ID = PV.VENDOR_ID
         --DTR#55945
         AND poh.vendor_site_id = asa.vendor_site_id
         AND asa.address_line4 = ood.organization_code(+) --- Added on 02/04/2016 by XVKIRAN
         AND asa.vendor_id = pv.vendor_id
         AND sup_con.vendor_contact_id = poh.vendor_contact_id(+)
         --
         AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
         AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
         AND POL.PO_LINE_ID = PLL.PO_LINE_ID
         AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
         --AND IIM.ITEM_NO = MSI.SEGMENT1                                                                                        -- 09/29/2011 ----Modified for R12
         AND MSI.INVENTORY_ITEM_ID = OICV.INVENTORY_ITEM_ID -- 09/29/2011 ----Modified for R12
         AND MSI.ORGANIZATION_ID = OICV.ORGANIZATION_ID -- 09/29/2011 ----Modified for R12
         AND OICV.CATEGORY_TYPE = 'INV_CLASS' -- 09/29/2011 ----Modified for R12
         AND OICV.CATEGORY_TYPE(+) = 'INV_CLASS' -- 07/23/2012 Modified for R12
         AND MSI.ORGANIZATION_ID = 0       -- Must be setup business group (0)
         --AND POH.AGENT_ID = PAP.PERSON_ID
         --AND HL.LOCATION_ID = PLL.SHIP_TO_LOCATION_ID
         AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                   'CLOSED',
                                                   'CLOSED FOR RECEIVING')
         AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
         AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
         AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED >
                0
         AND POH.TYPE_LOOKUP_CODE = 'STANDARD'
         AND PLL.SHIPMENT_TYPE = 'STANDARD'
         AND NVL (POH.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
         AND NOT EXISTS
                    (SELECT 'NO ASN'
                       FROM rcv_shipment_lines rsl
                      WHERE     rsl.PO_LINE_LOCATION_ID =
                                   pll.line_location_id
                            AND rsl.quantity_shipped - rsl.quantity_received <>
                                   0
                            AND rsl.shipment_line_status_code = 'EXPECTED'
                            AND rsl.asn_line_flag = 'Y')
--AND POL_FOB.LOOKUP_CODE(+) = POH.FOB_LOOKUP_CODE
--AND POL_FOB.LOOKUP_TYPE(+) = 'FOB';
;

Open in new window

0
 

Author Comment

by:pardeshirahul
Comment Utility
explain plan

Plan
SELECT STATEMENT  HINT: FIRST_ROWSCost: 150,451  Bytes: 3,920  Cardinality: 28  																																			
	757 SORT ORDER BY  Cost: 150,450  Bytes: 3,920  Cardinality: 28  																																		
		756 UNION-ALL  																																	
			375 HASH GROUP BY  Cost: 78,748  Bytes: 490  Cardinality: 7  																																
				374 NESTED LOOPS OUTER  Cost: 78,747  Bytes: 490  Cardinality: 7  																															
					372 VIEW VIEW JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY Cost: 78,733  Bytes: 224  Cardinality: 7  																														
						371 UNION-ALL  																													
							1 REMOTE REMOTE SERIAL_FROM_REMOTE JMS_INT_TO_ESS.WORLD																												
							3 TABLE ACCESS BY INDEX ROWID TABLE CPGC.CPGC_ITEMS Cost: 2  Bytes: 12  Cardinality: 1  																												
								2 INDEX UNIQUE SCAN INDEX (UNIQUE) CPGC.ITEMNO_MST_IDX Cost: 1  Cardinality: 1  																											
							370 HASH GROUP BY  Cost: 78,733  Bytes: 1,393  Cardinality: 7  																												
								369 NESTED LOOPS OUTER  Cost: 78,732  Bytes: 1,393  Cardinality: 7  																											
									367 HASH JOIN  Cost: 78,725  Bytes: 1,246  Cardinality: 7  																										
										365 HASH JOIN  Cost: 78,720  Bytes: 1,071  Cardinality: 7  																									
											363 VIEW VIEW JMSUSER.JMS_PUR_EXPECTED_RECEIPTS_SUM Cost: 78,714  Bytes: 896  Cardinality: 7  																								
												362 UNION-ALL  																							
													50 HASH JOIN OUTER  Cost: 7,207  Bytes: 257  Cardinality: 1  																						
														33 NESTED LOOPS  Cost: 7,167  Bytes: 253  Cardinality: 1  																					
															31 NESTED LOOPS  Cost: 7,167  Bytes: 253  Cardinality: 1  																				
																29 NESTED LOOPS  Cost: 7,165  Bytes: 242  Cardinality: 1  																			
																	26 NESTED LOOPS  Cost: 7,162  Bytes: 205  Cardinality: 1  																		
																		23 NESTED LOOPS  Cost: 7,160  Bytes: 177  Cardinality: 1  																	
																			20 NESTED LOOPS  Cost: 7,159  Bytes: 168  Cardinality: 1  																
																				17 NESTED LOOPS  Cost: 7,156  Bytes: 103  Cardinality: 1  															
																					15 HASH JOIN  Cost: 7,155  Bytes: 97  Cardinality: 1  														
																						13 HASH JOIN  Cost: 6,698  Bytes: 150,070  Cardinality: 1,745  													
																							11 HASH JOIN  Cost: 6,152  Bytes: 129,204  Cardinality: 1,746  												
																								9 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  											
																									7 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  										
																										5 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 19,741  Cardinality: 1,039  									
																											4 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  								
																										6 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  									
																									8 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  										
																								10 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 4,581,280  Cardinality: 114,532  											
																							12 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  												
																						14 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  													
																					16 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  														
																				19 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 65  Cardinality: 1  															
																					18 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N2 Cost: 2  Cardinality: 2  														
																			22 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																
																				21 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  															
																		25 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  																	
																			24 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  																
																	28 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 37  Cardinality: 1  																		
																		27 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  																	
																30 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  																			
															32 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 11  Cardinality: 1  																				
														49 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																					
															48 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																				
																46 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	44 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																		
																		41 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																	
																			38 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  																
																				36 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  															
																					34 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  														
																					35 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  														
																				37 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  															
																			40 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  																
																				39 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  															
																		43 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																	
																			42 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  																
																	45 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																		
																47 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																			
													101 FILTER  																						
														98 HASH JOIN OUTER  Cost: 3,466  Bytes: 277  Cardinality: 1  																					
															81 NESTED LOOPS SEMI  Cost: 3,426  Bytes: 273  Cardinality: 1  																				
																78 NESTED LOOPS  Cost: 3,423  Bytes: 252  Cardinality: 1  																			
																	75 NESTED LOOPS  Cost: 3,421  Bytes: 241  Cardinality: 1  																		
																		72 NESTED LOOPS  Cost: 3,418  Bytes: 198  Cardinality: 1  																	
																			69 NESTED LOOPS  Cost: 3,416  Bytes: 179  Cardinality: 1  																
																				66 NESTED LOOPS  Cost: 3,414  Bytes: 151  Cardinality: 1  															
																					63 NESTED LOOPS  Cost: 3,413  Bytes: 142  Cardinality: 1  														
																						60 NESTED LOOPS  Cost: 3,407  Bytes: 168  Cardinality: 2  													
																							58 NESTED LOOPS  Cost: 3,405  Bytes: 156  Cardinality: 2  												
																								55 HASH JOIN  Cost: 3,402  Bytes: 63  Cardinality: 1  											
																									51 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  										
																									54 HASH JOIN  Cost: 2,943  Bytes: 5,954,728  Cardinality: 114,514  										
																										52 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  									
																										53 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 4,581,280  Cardinality: 114,532  									
																								57 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 3  Bytes: 15  Cardinality: 1  											
																									56 INDEX RANGE SCAN INDEX PO.PO_LINES_N15 Cost: 2  Cardinality: 3  										
																							59 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  												
																						62 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 58  Cardinality: 1  													
																							61 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N2 Cost: 2  Cardinality: 2  												
																					65 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  														
																						64 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  													
																				68 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  															
																					67 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  														
																			71 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  																
																				70 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  															
																		74 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 43  Cardinality: 1  																	
																			73 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  																
																	77 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 11  Cardinality: 1  																		
																		76 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  																	
																80 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3  Bytes: 26,172,636  Cardinality: 1,246,316  																			
																	79 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 2  Cardinality: 2  																		
															97 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																				
																96 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	94 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																		
																		92 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																	
																			89 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																
																				86 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  															
																					84 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  														
																						82 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  													
																						83 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  													
																					85 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  														
																				88 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  															
																					87 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  														
																			91 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																
																				90 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  															
																		93 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																	
																	95 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																		
														100 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 4  Bytes: 21  Cardinality: 1  																					
															99 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 3  Cardinality: 2  																				
													149 HASH JOIN OUTER  Cost: 54  Bytes: 228  Cardinality: 1  																						
														132 NESTED LOOPS  Cost: 14  Bytes: 224  Cardinality: 1  																					
															130 NESTED LOOPS  Cost: 14  Bytes: 224  Cardinality: 1  																				
																128 NESTED LOOPS  Cost: 13  Bytes: 215  Cardinality: 1  																			
																	126 NESTED LOOPS  Cost: 12  Bytes: 209  Cardinality: 1  																		
																		123 NESTED LOOPS  Cost: 11  Bytes: 198  Cardinality: 1  																	
																			120 NESTED LOOPS  Cost: 10  Bytes: 186  Cardinality: 1  																
																				117 NESTED LOOPS  Cost: 8  Bytes: 167  Cardinality: 1  															
																					114 NESTED LOOPS  Cost: 6  Bytes: 152  Cardinality: 1  														
																						111 NESTED LOOPS  Cost: 4  Bytes: 112  Cardinality: 1  													
																							108 NESTED LOOPS  Cost: 2  Bytes: 84  Cardinality: 1  												
																								105 VIEW JMSUSER. Cost: 5  Bytes: 19  Cardinality: 1  											
																									104 HASH GROUP BY  Bytes: 27  Cardinality: 1  										
																										103 FILTER  									
																											102 TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 9,082  Bytes: 8,013,357  Cardinality: 296,791  								
																								107 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 2  Bytes: 65  Cardinality: 1  											
																									106 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_LINE_LOCATIONS_U1 Cost: 1  Cardinality: 1  										
																							110 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  												
																								109 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  											
																						113 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2  Bytes: 40  Cardinality: 1  													
																							112 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1  Cardinality: 1  												
																					116 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 2  Bytes: 15  Cardinality: 1  														
																						115 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_LINES_U1 Cost: 1  Cardinality: 1  													
																				119 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  															
																					118 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  														
																			122 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1  Bytes: 12  Cardinality: 1  																
																				121 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_SITES_U1 Cost: 0  Cardinality: 1  															
																		125 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1  Bytes: 11  Cardinality: 1  																	
																			124 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0  Cardinality: 1  																
																	127 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  																		
																129 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																			
															131 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																				
														148 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																					
															147 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																				
																145 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	143 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																		
																		140 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																	
																			137 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  																
																				135 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  															
																					133 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  														
																					134 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  														
																				136 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  															
																			139 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  																
																				138 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  															
																		142 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																	
																			141 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  																
																	144 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																		
																146 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																			
													213 FILTER  																						
														198 HASH JOIN OUTER  Cost: 40,763  Bytes: 220  Cardinality: 1  																					
															181 HASH JOIN OUTER  Cost: 40,723  Bytes: 216  Cardinality: 1  																				
																175 NESTED LOOPS  Cost: 7,162  Bytes: 210  Cardinality: 1  																			
																	173 NESTED LOOPS  Cost: 7,162  Bytes: 210  Cardinality: 1  																		
																		171 NESTED LOOPS  Cost: 7,160  Bytes: 182  Cardinality: 1  																	
																			169 NESTED LOOPS  Cost: 7,160  Bytes: 177  Cardinality: 1  																
																				166 NESTED LOOPS  Cost: 7,159  Bytes: 168  Cardinality: 1  															
																					163 NESTED LOOPS  Cost: 7,156  Bytes: 103  Cardinality: 1  														
																						161 HASH JOIN  Cost: 7,155  Bytes: 97  Cardinality: 1  													
																							159 HASH JOIN  Cost: 6,698  Bytes: 150,070  Cardinality: 1,745  												
																								157 HASH JOIN  Cost: 6,152  Bytes: 129,204  Cardinality: 1,746  											
																									155 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  										
																										153 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  									
																											151 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 19,741  Cardinality: 1,039  								
																												150 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  							
																											152 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  								
																										154 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  									
																									156 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 4,581,280  Cardinality: 114,532  										
																								158 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  											
																							160 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  												
																						162 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  													
																					165 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 65  Cardinality: 1  														
																						164 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N1 Cost: 2  Cardinality: 2  													
																				168 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  															
																					167 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  														
																			170 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ORGANIZATION_UNITS_PK Cost: 0  Bytes: 5  Cardinality: 1  																
																		172 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  																	
																	174 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  																		
																180 VIEW JMSUSER. Cost: 33,558  Bytes: 1,226,424  Cardinality: 204,404  																			
																	179 HASH UNIQUE  Cost: 33,558  Bytes: 11,446,624  Cardinality: 204,404  																		
																		178 HASH JOIN SEMI  Cost: 30,949  Bytes: 16,620,296  Cardinality: 296,791  																	
																			176 TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 9,082  Bytes: 10,387,685  Cardinality: 296,791  																
																			177 TABLE ACCESS FULL TABLE PO.RCV_TRANSACTIONS Cost: 20,614  Bytes: 26,172,636  Cardinality: 1,246,316  																
															197 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																				
																196 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	194 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																		
																		192 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																	
																			189 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																
																				186 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  															
																					184 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  														
																						182 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  													
																						183 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  													
																					185 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  														
																				188 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  															
																					187 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  														
																			191 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																
																				190 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  															
																		193 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																	
																	195 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																		
														200 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 23  Cardinality: 1  																					
															199 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  																				
														206 NESTED LOOPS  Cost: 7  Bytes: 39  Cardinality: 1  																					
															204 NESTED LOOPS  Cost: 7  Bytes: 39  Cardinality: 2  																				
																202 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 18  Cardinality: 1  																			
																	201 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  																		
																203 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 2  Cardinality: 2  																			
															205 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3  Bytes: 21  Cardinality: 1  																				
														212 NESTED LOOPS  Cost: 7  Bytes: 30  Cardinality: 1  																					
															210 NESTED LOOPS  Cost: 7  Bytes: 30  Cardinality: 2  																				
																208 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 18  Cardinality: 1  																			
																	207 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  																		
																209 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 2  Cardinality: 2  																			
															211 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3  Bytes: 12  Cardinality: 1  																				
													257 HASH JOIN OUTER  Cost: 7,237  Bytes: 236  Cardinality: 1  																						
														240 NESTED LOOPS  Cost: 7,197  Bytes: 232  Cardinality: 1  																					
															238 NESTED LOOPS  Cost: 7,197  Bytes: 232  Cardinality: 1  																				
																236 NESTED LOOPS  Cost: 7,195  Bytes: 221  Cardinality: 1  																			
																	233 NESTED LOOPS  Cost: 7,192  Bytes: 184  Cardinality: 1  																		
																		230 NESTED LOOPS  Cost: 7,191  Bytes: 175  Cardinality: 1  																	
																			227 NESTED LOOPS  Cost: 7,188  Bytes: 113  Cardinality: 1  																
																				225 HASH JOIN  Cost: 7,187  Bytes: 107  Cardinality: 1  															
																					223 HASH JOIN  Cost: 6,730  Bytes: 139,584  Cardinality: 1,454  														
																						221 HASH JOIN  Cost: 6,184  Bytes: 122,220  Cardinality: 1,455  													
																							219 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  												
																								217 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  											
																									215 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 19,741  Cardinality: 1,039  										
																										214 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  									
																									216 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  										
																								218 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  											
																							220 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,232  Bytes: 4,772,150  Cardinality: 95,443  												
																						222 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  													
																					224 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  														
																				226 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  															
																			229 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 62  Cardinality: 1  																
																				228 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N1 Cost: 2  Cardinality: 2  															
																		232 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																	
																			231 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																
																	235 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 37  Cardinality: 1  																		
																		234 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  																	
																237 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  																			
															239 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 11  Cardinality: 1  																				
														256 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																					
															255 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																				
																253 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	251 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																		
																		248 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																	
																			245 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  																
																				243 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  															
																					241 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  														
																					242 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  														
																				244 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  															
																			247 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  																
																				246 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  															
																		250 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																	
																			249 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  																
																	252 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																		
																254 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																			
													301 HASH JOIN OUTER  Cost: 19,619  Bytes: 207  Cardinality: 1  																						
														284 NESTED LOOPS  Cost: 19,580  Bytes: 203  Cardinality: 1  																					
															282 NESTED LOOPS  Cost: 19,579  Bytes: 197  Cardinality: 1  																				
																279 NESTED LOOPS  Cost: 19,578  Bytes: 186  Cardinality: 1  																			
																	276 NESTED LOOPS  Cost: 19,577  Bytes: 174  Cardinality: 1  																		
																		273 NESTED LOOPS  Cost: 19,575  Bytes: 155  Cardinality: 1  																	
																			270 NESTED LOOPS  Cost: 19,574  Bytes: 146  Cardinality: 1  																
																				267 NESTED LOOPS  Cost: 19,558  Bytes: 768  Cardinality: 8  															
																					264 HASH JOIN  Cost: 19,542  Bytes: 648  Cardinality: 8  														
																						259 JOIN FILTER CREATE SYS.:BF0000 Cost: 10,453  Bytes: 9,610  Cardinality: 155  													
																							258 TABLE ACCESS FULL TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 10,453  Bytes: 9,610  Cardinality: 155  												
																						263 VIEW JMSUSER. Cost: 9,088  Bytes: 281,599  Cardinality: 14,821  													
																							262 HASH GROUP BY  Cost: 9,088  Bytes: 459,451  Cardinality: 14,821  												
																								261 JOIN FILTER USE SYS.:BF0000 Cost: 9,086  Bytes: 460,040  Cardinality: 14,840  											
																									260 TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 9,086  Bytes: 460,040  Cardinality: 14,840  										
																					266 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 2  Bytes: 15  Cardinality: 1  														
																						265 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_LINES_U1 Cost: 1  Cardinality: 1  													
																				269 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2  Bytes: 50  Cardinality: 1  															
																					268 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1  Cardinality: 1  														
																			272 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																
																				271 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  															
																		275 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  																	
																			274 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  																
																	278 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1  Bytes: 12  Cardinality: 1  																		
																		277 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_SITES_U1 Cost: 0  Cardinality: 1  																	
																281 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1  Bytes: 11  Cardinality: 1  																			
																	280 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0  Cardinality: 1  																		
															283 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  																				
														300 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																					
															299 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																				
																297 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	295 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																		
																		292 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																	
																			289 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  																
																				287 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  															
																					285 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  														
																					286 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  														
																				288 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  															
																			291 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  																
																				290 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  															
																		294 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																	
																			293 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  																
																	296 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																		
																298 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																			
													361 FILTER  																						
														358 HASH JOIN OUTER  Cost: 349  Bytes: 275  Cardinality: 1  																					
															341 NESTED LOOPS  Cost: 310  Bytes: 271  Cardinality: 1  																				
																339 NESTED LOOPS  Cost: 310  Bytes: 271  Cardinality: 1  																			
																	337 NESTED LOOPS  Cost: 309  Bytes: 262  Cardinality: 1  																		
																		334 NESTED LOOPS  Cost: 306  Bytes: 200  Cardinality: 1  																	
																			332 NESTED LOOPS  Cost: 305  Bytes: 194  Cardinality: 1  																
																				329 NESTED LOOPS  Cost: 294  Bytes: 2,013  Cardinality: 11  															
																					326 NESTED LOOPS  Cost: 283  Bytes: 1,881  Cardinality: 11  														
																						324 NESTED LOOPS  Cost: 283  Bytes: 1,826  Cardinality: 11  													
																							321 NESTED LOOPS  Cost: 163  Bytes: 6,720  Cardinality: 60  												
																								318 NESTED LOOPS  Cost: 120  Bytes: 1,164  Cardinality: 12  											
																									315 NESTED LOOPS  Cost: 96  Bytes: 936  Cardinality: 12  										
																										313 NESTED LOOPS  Cost: 40  Bytes: 3,864  Cardinality: 56  									
																											311 NESTED LOOPS  Cost: 40  Bytes: 3,360  Cardinality: 56  								
																												309 NESTED LOOPS  Cost: 40  Bytes: 3,080  Cardinality: 56  							
																													307 NESTED LOOPS  Cost: 9  Bytes: 38  Cardinality: 1  						
																														305 NESTED LOOPS  Cost: 9  Bytes: 33  Cardinality: 1  					
																															303 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2  Bytes: 7  Cardinality: 1  				
																																302 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 1  Cardinality: 1  			
																															304 TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7  Bytes: 26  Cardinality: 1  				
																														306 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_CATEGORY_SETS_B_U1 Cost: 0  Bytes: 5  Cardinality: 1  					
																													308 INDEX RANGE SCAN INDEX (UNIQUE) INV.MTL_ITEM_CATEGORIES_U1 Cost: 31  Bytes: 2,754  Cardinality: 162  						
																												310 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_CATEGORIES_B_U1 Cost: 0  Bytes: 5  Cardinality: 1  							
																											312 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_CATEGORIES_TL_U1 Cost: 0  Bytes: 9  Cardinality: 1  								
																										314 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Bytes: 9  Cardinality: 1  									
																									317 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  										
																										316 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  									
																								320 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  											
																									319 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  										
																							323 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2  Bytes: 54  Cardinality: 1  												
																								322 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1  Cardinality: 1  											
																						325 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_CONTACTS_U1 Cost: 0  Bytes: 5  Cardinality: 1  													
																					328 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1  Bytes: 12  Cardinality: 1  														
																						327 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_SITES_U1 Cost: 0  Cardinality: 1  													
																				331 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1  Bytes: 11  Cardinality: 1  															
																					330 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0  Cardinality: 1  														
																			333 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  																
																		336 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 62  Cardinality: 1  																	
																			335 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N1 Cost: 2  Cardinality: 2  																
																	338 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																		
																340 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																			
															357 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																				
																356 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																			
																	354 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																		
																		352 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  																	
																			349 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  																
																				346 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  															
																					344 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  														
																						342 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  													
																						343 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  													
																					345 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  														
																				348 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  															
																					347 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  														
																			351 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  																
																				350 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  															
																		353 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																	
																	355 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																		
														360 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 31  Cardinality: 1  																					
															359 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  																				
											364 REMOTE REMOTE SERIAL_FROM_REMOTE STOCK JMS_INT_TO_ESS.WORLDCost: 5  Bytes: 16,200  Cardinality: 648  																								
										366 REMOTE REMOTE SERIAL_FROM_REMOTE STOCK JMS_INT_TO_ESS.WORLDCost: 5  Bytes: 16,200  Cardinality: 648  																									
									368 REMOTE REMOTE SERIAL_FROM_REMOTE JMS_PURCHASE_ORDER_DATES JMS_INT_TO_MTP.WORLDCost: 1  Bytes: 21  Cardinality: 1  																										
					373 REMOTE REMOTE SERIAL_FROM_REMOTE CALDATA JMS_APP_TO_MAN.WORLDCost: 2  Bytes: 38  Cardinality: 1  																														
			755 FILTER  																																
				754 NESTED LOOPS OUTER  Cost: 71,702  Bytes: 1,470  Cardinality: 21  																															
					752 NESTED LOOPS  Cost: 71,700  Bytes: 32  Cardinality: 1  																														
						378 VIEW APPS. Cost: 2  Bytes: 6  Cardinality: 1  																													
							377 CONNECT BY WITHOUT FILTERING  																												
								376 FAST DUAL  Cost: 2  Cardinality: 1  																											
						751 VIEW APPS. Cost: 71,698  Bytes: 26  Cardinality: 1  																													
							750 SORT GROUP BY  Cost: 71,698  Bytes: 182  Cardinality: 7  																												
								749 VIEW VIEW JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY Cost: 71,697  Bytes: 182  Cardinality: 7  																											
									748 UNION-ALL  																										
										379 REMOTE REMOTE SERIAL_FROM_REMOTE JMS_INT_TO_ESS.WORLD																									
										381 TABLE ACCESS BY INDEX ROWID TABLE CPGC.CPGC_ITEMS Cost: 2  Bytes: 12  Cardinality: 1  																									
											380 INDEX UNIQUE SCAN INDEX (UNIQUE) CPGC.ITEMNO_MST_IDX Cost: 1  Cardinality: 1  																								
										747 SORT GROUP BY  Cost: 71,697  Bytes: 1,393  Cardinality: 7  																									
											746 NESTED LOOPS OUTER  Cost: 71,696  Bytes: 1,393  Cardinality: 7  																								
												744 HASH JOIN  Cost: 71,689  Bytes: 1,246  Cardinality: 7  																							
													742 HASH JOIN  Cost: 71,684  Bytes: 1,071  Cardinality: 7  																						
														740 VIEW VIEW JMSUSER.JMS_PUR_EXPECTED_RECEIPTS_SUM Cost: 71,679  Bytes: 896  Cardinality: 7  																					
															739 UNION-ALL  																				
																428 HASH JOIN OUTER  Cost: 7,207  Bytes: 257  Cardinality: 1  																			
																	411 NESTED LOOPS  Cost: 7,167  Bytes: 253  Cardinality: 1  																		
																		409 NESTED LOOPS  Cost: 7,167  Bytes: 253  Cardinality: 1  																	
																			407 NESTED LOOPS  Cost: 7,165  Bytes: 242  Cardinality: 1  																
																				404 NESTED LOOPS  Cost: 7,162  Bytes: 205  Cardinality: 1  															
																					401 NESTED LOOPS  Cost: 7,160  Bytes: 177  Cardinality: 1  														
																						398 NESTED LOOPS  Cost: 7,159  Bytes: 168  Cardinality: 1  													
																							395 NESTED LOOPS  Cost: 7,156  Bytes: 103  Cardinality: 1  												
																								393 HASH JOIN  Cost: 7,155  Bytes: 97  Cardinality: 1  											
																									391 HASH JOIN  Cost: 6,698  Bytes: 150,070  Cardinality: 1,745  										
																										389 HASH JOIN  Cost: 6,152  Bytes: 129,204  Cardinality: 1,746  									
																											387 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  								
																												385 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  							
																													383 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 19,741  Cardinality: 1,039  						
																														382 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  					
																													384 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  						
																												386 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  							
																											388 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 4,581,280  Cardinality: 114,532  								
																										390 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  									
																									392 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  										
																								394 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  											
																							397 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 65  Cardinality: 1  												
																								396 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N2 Cost: 2  Cardinality: 2  											
																						400 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  													
																							399 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  												
																					403 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  														
																						402 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  													
																				406 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 37  Cardinality: 1  															
																					405 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  														
																			408 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  																
																		410 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 11  Cardinality: 1  																	
																	427 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																		
																		426 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																	
																			424 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																
																				422 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  															
																					419 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  														
																						416 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  													
																							414 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  												
																								412 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  											
																								413 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  											
																							415 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  												
																						418 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  													
																							417 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  												
																					421 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  														
																						420 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  													
																				423 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  															
																			425 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																
																479 FILTER  																			
																	476 HASH JOIN OUTER  Cost: 3,466  Bytes: 277  Cardinality: 1  																		
																		459 NESTED LOOPS SEMI  Cost: 3,426  Bytes: 273  Cardinality: 1  																	
																			456 NESTED LOOPS  Cost: 3,423  Bytes: 252  Cardinality: 1  																
																				453 NESTED LOOPS  Cost: 3,421  Bytes: 241  Cardinality: 1  															
																					450 NESTED LOOPS  Cost: 3,418  Bytes: 198  Cardinality: 1  														
																						447 NESTED LOOPS  Cost: 3,416  Bytes: 179  Cardinality: 1  													
																							444 NESTED LOOPS  Cost: 3,414  Bytes: 151  Cardinality: 1  												
																								441 NESTED LOOPS  Cost: 3,413  Bytes: 142  Cardinality: 1  											
																									438 NESTED LOOPS  Cost: 3,407  Bytes: 168  Cardinality: 2  										
																										436 NESTED LOOPS  Cost: 3,405  Bytes: 156  Cardinality: 2  									
																											433 HASH JOIN  Cost: 3,402  Bytes: 63  Cardinality: 1  								
																												429 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  							
																												432 HASH JOIN  Cost: 2,943  Bytes: 5,954,728  Cardinality: 114,514  							
																													430 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  						
																													431 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 4,581,280  Cardinality: 114,532  						
																											435 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 3  Bytes: 15  Cardinality: 1  								
																												434 INDEX RANGE SCAN INDEX PO.PO_LINES_N15 Cost: 2  Cardinality: 3  							
																										437 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  									
																									440 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 58  Cardinality: 1  										
																										439 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N2 Cost: 2  Cardinality: 2  									
																								443 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  											
																									442 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  										
																							446 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  												
																								445 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  											
																						449 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  													
																							448 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  												
																					452 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 43  Cardinality: 1  														
																						451 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  													
																				455 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 11  Cardinality: 1  															
																					454 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  														
																			458 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3  Bytes: 26,172,636  Cardinality: 1,246,316  																
																				457 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 2  Cardinality: 2  															
																		475 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																	
																			474 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																
																				472 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  															
																					470 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  														
																						467 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  													
																							464 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  												
																								462 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  											
																									460 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  										
																									461 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  										
																								463 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  											
																							466 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  												
																								465 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  											
																						469 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  													
																							468 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  												
																					471 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  														
																				473 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  															
																	478 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 4  Bytes: 21  Cardinality: 1  																		
																		477 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 3  Cardinality: 2  																	
																528 NESTED LOOPS  Cost: 54  Bytes: 228  Cardinality: 1  																			
																	526 NESTED LOOPS  Cost: 54  Bytes: 228  Cardinality: 1  																		
																		524 NESTED LOOPS  Cost: 52  Bytes: 209  Cardinality: 1  																	
																			521 NESTED LOOPS  Cost: 50  Bytes: 194  Cardinality: 1  																
																				518 NESTED LOOPS  Cost: 48  Bytes: 166  Cardinality: 1  															
																					516 HASH JOIN OUTER  Cost: 47  Bytes: 160  Cardinality: 1  														
																						499 NESTED LOOPS  Cost: 7  Bytes: 156  Cardinality: 1  													
																							497 NESTED LOOPS  Cost: 7  Bytes: 156  Cardinality: 1  												
																								495 NESTED LOOPS  Cost: 6  Bytes: 144  Cardinality: 1  											
																									492 NESTED LOOPS  Cost: 5  Bytes: 133  Cardinality: 1  										
																										489 NESTED LOOPS  Cost: 3  Bytes: 93  Cardinality: 1  									
																											486 NESTED LOOPS  Cost: 2  Bytes: 84  Cardinality: 1  								
																												483 VIEW JMSUSER. Cost: 5  Bytes: 19  Cardinality: 1  							
																													482 SORT GROUP BY  Bytes: 27  Cardinality: 1  						
																														481 FILTER  					
																															480 TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 9,082  Bytes: 8,013,357  Cardinality: 296,791  				
																												485 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 2  Bytes: 65  Cardinality: 1  							
																													484 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_LINE_LOCATIONS_U1 Cost: 1  Cardinality: 1  						
																											488 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  								
																												487 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  							
																										491 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2  Bytes: 40  Cardinality: 1  									
																											490 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1  Cardinality: 1  								
																									494 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1  Bytes: 11  Cardinality: 1  										
																										493 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0  Cardinality: 1  									
																								496 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_SITES_U1 Cost: 0  Cardinality: 1  											
																							498 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1  Bytes: 12  Cardinality: 1  												
																						515 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  													
																							514 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  												
																								512 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  											
																									510 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  										
																										507 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  									
																											504 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  								
																												502 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  							
																													500 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  						
																													501 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  						
																												503 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  							
																											506 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  								
																												505 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  							
																										509 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  									
																											508 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  								
																									511 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  										
																								513 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  											
																					517 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  														
																				520 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  															
																					519 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  														
																			523 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 2  Bytes: 15  Cardinality: 1  																
																				522 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_LINES_U1 Cost: 1  Cardinality: 1  															
																		525 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  																	
																	527 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  																		
																592 FILTER  																			
																	577 HASH JOIN OUTER  Cost: 40,763  Bytes: 220  Cardinality: 1  																		
																		560 HASH JOIN OUTER  Cost: 40,723  Bytes: 216  Cardinality: 1  																	
																			554 NESTED LOOPS  Cost: 7,162  Bytes: 210  Cardinality: 1  																
																				552 NESTED LOOPS  Cost: 7,162  Bytes: 210  Cardinality: 1  															
																					550 NESTED LOOPS  Cost: 7,160  Bytes: 182  Cardinality: 1  														
																						548 NESTED LOOPS  Cost: 7,160  Bytes: 177  Cardinality: 1  													
																							545 NESTED LOOPS  Cost: 7,159  Bytes: 168  Cardinality: 1  												
																								542 NESTED LOOPS  Cost: 7,156  Bytes: 103  Cardinality: 1  											
																									540 HASH JOIN  Cost: 7,155  Bytes: 97  Cardinality: 1  										
																										538 HASH JOIN  Cost: 6,698  Bytes: 150,070  Cardinality: 1,745  									
																											536 HASH JOIN  Cost: 6,152  Bytes: 129,204  Cardinality: 1,746  								
																												534 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  							
																													532 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  						
																														530 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 19,741  Cardinality: 1,039  					
																															529 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  				
																														531 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  					
																													533 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  						
																												535 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 4,581,280  Cardinality: 114,532  							
																											537 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  								
																										539 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  									
																									541 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  										
																								544 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 65  Cardinality: 1  											
																									543 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N1 Cost: 2  Cardinality: 2  										
																							547 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  												
																								546 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  											
																						549 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ORGANIZATION_UNITS_PK Cost: 0  Bytes: 5  Cardinality: 1  													
																					551 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  														
																				553 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 28  Cardinality: 1  															
																			559 VIEW JMSUSER. Cost: 33,558  Bytes: 1,226,424  Cardinality: 204,404  																
																				558 SORT UNIQUE  Cost: 33,558  Bytes: 11,446,624  Cardinality: 204,404  															
																					557 HASH JOIN SEMI  Cost: 30,949  Bytes: 16,620,296  Cardinality: 296,791  														
																						555 TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 9,082  Bytes: 10,387,685  Cardinality: 296,791  													
																						556 TABLE ACCESS FULL TABLE PO.RCV_TRANSACTIONS Cost: 20,614  Bytes: 26,172,636  Cardinality: 1,246,316  													
																		576 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																	
																			575 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																
																				573 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  															
																					571 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  														
																						568 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  													
																							565 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  												
																								563 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  											
																									561 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  										
																									562 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  										
																								564 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  											
																							567 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  												
																								566 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  											
																						570 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  													
																							569 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  												
																					572 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  														
																				574 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  															
																	579 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 23  Cardinality: 1  																		
																		578 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  																	
																	585 NESTED LOOPS  Cost: 7  Bytes: 39  Cardinality: 1  																		
																		583 NESTED LOOPS  Cost: 7  Bytes: 39  Cardinality: 2  																	
																			581 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 18  Cardinality: 1  																
																				580 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  															
																			582 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 2  Cardinality: 2  																
																		584 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3  Bytes: 21  Cardinality: 1  																	
																	591 NESTED LOOPS  Cost: 7  Bytes: 30  Cardinality: 1  																		
																		589 NESTED LOOPS  Cost: 7  Bytes: 30  Cardinality: 2  																	
																			587 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 18  Cardinality: 1  																
																				586 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  															
																			588 INDEX RANGE SCAN INDEX PO.RCV_TRANSACTIONS_N3 Cost: 2  Cardinality: 2  																
																		590 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_TRANSACTIONS Cost: 3  Bytes: 12  Cardinality: 1  																	
																636 HASH JOIN OUTER  Cost: 7,237  Bytes: 236  Cardinality: 1  																			
																	619 NESTED LOOPS  Cost: 7,197  Bytes: 232  Cardinality: 1  																		
																		617 NESTED LOOPS  Cost: 7,197  Bytes: 232  Cardinality: 1  																	
																			615 NESTED LOOPS  Cost: 7,195  Bytes: 221  Cardinality: 1  																
																				612 NESTED LOOPS  Cost: 7,192  Bytes: 184  Cardinality: 1  															
																					609 NESTED LOOPS  Cost: 7,191  Bytes: 175  Cardinality: 1  														
																						606 NESTED LOOPS  Cost: 7,188  Bytes: 113  Cardinality: 1  													
																							604 HASH JOIN  Cost: 7,187  Bytes: 107  Cardinality: 1  												
																								602 HASH JOIN  Cost: 6,730  Bytes: 139,584  Cardinality: 1,454  											
																									600 HASH JOIN  Cost: 6,184  Bytes: 122,220  Cardinality: 1,455  										
																										598 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  									
																											596 NESTED LOOPS  Cost: 3,951  Bytes: 180,438  Cardinality: 5,307  								
																												594 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 19,741  Cardinality: 1,039  							
																													593 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  						
																												595 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  							
																											597 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  								
																										599 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,232  Bytes: 4,772,150  Cardinality: 95,443  									
																									601 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  										
																								603 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  											
																							605 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  												
																						608 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 62  Cardinality: 1  													
																							607 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N1 Cost: 2  Cardinality: 2  												
																					611 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  														
																						610 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  													
																				614 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 37  Cardinality: 1  															
																					613 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  														
																			616 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  																
																		618 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 11  Cardinality: 1  																	
																	635 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																		
																		634 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																	
																			632 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																
																				630 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  															
																					627 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  														
																						624 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  													
																							622 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  												
																								620 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  											
																								621 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  											
																							623 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  												
																						626 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  													
																							625 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  												
																					629 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  														
																						628 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  													
																				631 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  															
																			633 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																
																678 NESTED LOOPS  Cost: 12,584  Bytes: 207  Cardinality: 1  																			
																	676 NESTED LOOPS  Cost: 12,584  Bytes: 207  Cardinality: 1  																		
																		674 NESTED LOOPS  Cost: 12,583  Bytes: 198  Cardinality: 1  																	
																			672 HASH JOIN  Cost: 12,582  Bytes: 192  Cardinality: 1  																
																				668 NESTED LOOPS  Cost: 3,494  Bytes: 173  Cardinality: 1  															
																					666 NESTED LOOPS  Cost: 3,494  Bytes: 173  Cardinality: 1  														
																						664 NESTED LOOPS  Cost: 3,492  Bytes: 154  Cardinality: 1  													
																							661 NESTED LOOPS  Cost: 3,490  Bytes: 139  Cardinality: 1  												
																								658 HASH JOIN  Cost: 3,485  Bytes: 77  Cardinality: 1  											
																									637 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 881,210  Cardinality: 80,110  										
																									657 HASH JOIN  Cost: 3,026  Bytes: 6,298,314  Cardinality: 95,429  										
																										655 HASH JOIN RIGHT OUTER  Cost: 586  Bytes: 1,870,608  Cardinality: 116,913  									
																											653 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  								
																												652 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  							
																													650 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  						
																														648 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  					
																															645 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  				
																																642 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  			
																																	640 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  		
																																		638 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  	
																																		639 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  	
																																	641 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  		
																																644 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  			
																																	643 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  		
																															647 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  				
																																646 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  			
																														649 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  					
																													651 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  						
																											654 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 1,402,956  Cardinality: 116,913  								
																										656 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,232  Bytes: 4,772,150  Cardinality: 95,443  									
																								660 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 5  Bytes: 62  Cardinality: 1  											
																									659 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N2 Cost: 2  Cardinality: 5  										
																							663 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 2  Bytes: 15  Cardinality: 1  												
																								662 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_LINES_U1 Cost: 1  Cardinality: 1  											
																						665 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  													
																					667 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  														
																				671 VIEW JMSUSER. Cost: 9,088  Bytes: 281,599  Cardinality: 14,821  															
																					670 SORT GROUP BY  Cost: 9,088  Bytes: 459,451  Cardinality: 14,821  														
																						669 TABLE ACCESS FULL TABLE PO.RCV_SHIPMENT_LINES Cost: 9,086  Bytes: 460,040  Cardinality: 14,840  													
																			673 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  																
																		675 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  																	
																	677 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																		
																738 FILTER  																			
																	735 HASH JOIN OUTER  Cost: 349  Bytes: 275  Cardinality: 1  																		
																		718 NESTED LOOPS  Cost: 310  Bytes: 271  Cardinality: 1  																	
																			716 NESTED LOOPS  Cost: 310  Bytes: 271  Cardinality: 1  																
																				714 NESTED LOOPS  Cost: 309  Bytes: 262  Cardinality: 1  															
																					711 NESTED LOOPS  Cost: 306  Bytes: 200  Cardinality: 1  														
																						709 NESTED LOOPS  Cost: 305  Bytes: 194  Cardinality: 1  													
																							706 NESTED LOOPS  Cost: 294  Bytes: 2,013  Cardinality: 11  												
																								703 NESTED LOOPS  Cost: 283  Bytes: 1,881  Cardinality: 11  											
																									701 NESTED LOOPS  Cost: 283  Bytes: 1,826  Cardinality: 11  										
																										698 NESTED LOOPS  Cost: 163  Bytes: 6,720  Cardinality: 60  									
																											695 NESTED LOOPS  Cost: 120  Bytes: 1,164  Cardinality: 12  								
																												692 NESTED LOOPS  Cost: 96  Bytes: 936  Cardinality: 12  							
																													690 NESTED LOOPS  Cost: 40  Bytes: 3,864  Cardinality: 56  						
																														688 NESTED LOOPS  Cost: 40  Bytes: 3,360  Cardinality: 56  					
																															686 NESTED LOOPS  Cost: 40  Bytes: 3,080  Cardinality: 56  				
																																684 NESTED LOOPS  Cost: 9  Bytes: 38  Cardinality: 1  			
																																	682 NESTED LOOPS  Cost: 9  Bytes: 33  Cardinality: 1  		
																																		680 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 2  Bytes: 7  Cardinality: 1  	
																																			679 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 1  Cardinality: 1  
																																		681 TABLE ACCESS FULL TABLE INV.MTL_CATEGORY_SETS_TL Cost: 7  Bytes: 26  Cardinality: 1  	
																																	683 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_CATEGORY_SETS_B_U1 Cost: 0  Bytes: 5  Cardinality: 1  		
																																685 INDEX RANGE SCAN INDEX (UNIQUE) INV.MTL_ITEM_CATEGORIES_U1 Cost: 31  Bytes: 2,754  Cardinality: 162  			
																															687 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_CATEGORIES_B_U1 Cost: 0  Bytes: 5  Cardinality: 1  				
																														689 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_CATEGORIES_TL_U1 Cost: 0  Bytes: 9  Cardinality: 1  					
																													691 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Bytes: 9  Cardinality: 1  						
																												694 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 2  Bytes: 19  Cardinality: 1  							
																													693 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_SYSTEM_ITEMS_B_U1 Cost: 1  Cardinality: 1  						
																											697 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 75  Cardinality: 5  								
																												696 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  							
																										700 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_HEADERS_ALL Cost: 2  Bytes: 54  Cardinality: 1  									
																											699 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_HEADERS_U1 Cost: 1  Cardinality: 1  								
																									702 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_CONTACTS_U1 Cost: 0  Bytes: 5  Cardinality: 1  										
																								705 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 1  Bytes: 12  Cardinality: 1  											
																									704 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIER_SITES_U1 Cost: 0  Cardinality: 1  										
																							708 TABLE ACCESS BY INDEX ROWID TABLE AP.AP_SUPPLIERS Cost: 1  Bytes: 11  Cardinality: 1  												
																								707 INDEX UNIQUE SCAN INDEX (UNIQUE) AP.AP_SUPPLIERS_U1 Cost: 0  Cardinality: 1  											
																						710 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  													
																					713 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 62  Cardinality: 1  														
																						712 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N1 Cost: 2  Cardinality: 2  													
																				715 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  															
																			717 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  																
																		734 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 40  Bytes: 12  Cardinality: 3  																	
																			733 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  																
																				731 NESTED LOOPS  Cost: 40  Bytes: 279  Cardinality: 3  															
																					729 NESTED LOOPS  Cost: 38  Bytes: 252  Cardinality: 3  														
																						726 NESTED LOOPS  Cost: 33  Bytes: 380  Cardinality: 5  													
																							723 NESTED LOOPS  Cost: 23  Bytes: 250  Cardinality: 5  												
																								721 HASH JOIN  Cost: 23  Bytes: 10,332  Cardinality: 252  											
																									719 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  										
																									720 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  										
																								722 INDEX UNIQUE SCAN INDEX (UNIQUE) HR.HR_ALL_ORGANIZATION_UNTS_TL_PK Cost: 0  Bytes: 9  Cardinality: 1  											
																							725 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  												
																								724 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  											
																						728 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  													
																							727 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  												
																					730 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  														
																				732 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  															
																	737 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 4  Bytes: 31  Cardinality: 1  																		
																		736 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 3  Cardinality: 1  																	
														741 REMOTE REMOTE SERIAL_FROM_REMOTE STOCK JMS_INT_TO_ESS.WORLDCost: 5  Bytes: 16,200  Cardinality: 648  																					
													743 REMOTE REMOTE SERIAL_FROM_REMOTE STOCK JMS_INT_TO_ESS.WORLDCost: 5  Bytes: 16,200  Cardinality: 648  																						
												745 REMOTE REMOTE SERIAL_FROM_REMOTE JMS_PURCHASE_ORDER_DATES JMS_INT_TO_MTP.WORLDCost: 1  Bytes: 21  Cardinality: 1  																							
					753 REMOTE REMOTE SERIAL_FROM_REMOTE CALDATA JMS_APP_TO_MAN.WORLDCost: 2  Bytes: 2,318  Cardinality: 61

Open in new window

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Yes, that view definition and that "explain plan" look terrible, if you want good performance.

So, don't use this view!  Create an efficient view instead that just selects the three columns you need for your query.  Yes, this may take some development time.  Depending on where those columns are in the base tables, you may need to spend some time studying the current view definition to determine which base tables and columns you actually need to include, and which ones you don't need.  And, if multiple tables are involved, you will need to test your view for performance to make sure that your joins are based on existing indexes, and that your view contains "where" clause values that allow the database to do the joins and/or filtering efficiently.
0
 

Author Comment

by:pardeshirahul
Comment Utility
There are 2 unins in the query this first union is taking more then 30 minutes

SELECT a.custno,
            a.stockto,
            a.dayname,
            TO_CHAR (a.rad, 'IYYY') || TO_CHAR (a.rad, 'IW') "WEEKNO",
            a.rad,
            --            COUNT (NVL (a.load_number_last, a.cono)) "TDY_LOAD_CNT",
            SUM (a.load_count) "TDY_LOAD_CNT",
            NVL (TO_NUMBER (b.avail), 0) "TDY_CAP",
            DECODE (TRUNC (a.rad),
                    TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (a.custno),
                    NULL)
               "PRIOR_LOAD_CNT"
       FROM JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY a,
            (SELECT cal,
                    (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
                    avail
               FROM caldata) b
      WHERE     a.rad >= TRUNC (SYSDATE)
            AND a.rad = b.effdate(+)
            AND (a.custno || '-INCAP') = b.cal(+)
   GROUP BY a.custno,
            a.stockto,
            a.rad,
              TO_CHAR (a.rad, 'IYYY') || TO_CHAR (a.rad, 'IW') ,
            --a.weekno,
            a.dayname,
            b.avail

Open in new window

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
I don't see any "unions" in the query you just posted.  So, how or where do you want us to help you?
0
 

Author Comment

by:pardeshirahul
Comment Utility
this condition is creating an issue


WHERE     a.rad >= TRUNC (SYSDATE)

but if i remove the trunc the number of records differ if i kept the trunc
but trunc is very costly
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
We're back to blaming TRUNC and SYSDATE again?

Again.  Neither of those are your issue.  There is a possibility (pretty slim, but possible) that there is a change in the query plan due to this.  That is what you have to focus on.  Personally, I don't have the time to fish through over 700 lines of an explan plan, and with both copies of the plan, you should be able to find the differences.  Post the differences and maybe we can help.  But, honestly unless you are going to be willing to change the view, there is nothing we can suggest anyway.

Why can't you just create your own view or simple query that will get the few columns you need?  It would be much simpler.  I did a quick count on the plan and saw a ton of full table scans and multiple remote operations.  I'm sure you could eliminate those.
0
 

Author Comment

by:pardeshirahul
Comment Utility
okay thanks
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
My count shows 66 full table scans and 10 remote operations.  I'm sure you can cut out quite a bit of those to get to the information that you want.
0
 

Author Comment

by:pardeshirahul
Comment Utility
as 10 remote operations are because of the Dbms link
0
 

Author Comment

by:pardeshirahul
Comment Utility
i pasted the first query last time

and the unions is after that

so the complete query is

SELECT a.custno,
            a.stockto,
            a.dayname,
            TO_CHAR (a.rad, 'IYYY') || TO_CHAR (a.rad, 'IW') "WEEKNO",
            a.rad,
            --            COUNT (NVL (a.load_number_last, a.cono)) "TDY_LOAD_CNT",
            SUM (a.load_count) "TDY_LOAD_CNT",
            NVL (TO_NUMBER (b.avail), 0) "TDY_CAP",
            DECODE (TRUNC (a.rad),
                    TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (a.custno),
                    NULL)
               "PRIOR_LOAD_CNT"
       FROM JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY a,
            (SELECT cal,
                    (eff / 1440 + TO_DATE ('01011970', 'MMDDYYYY')) "EFFDATE",
                    avail
               FROM caldata) b
      WHERE     a.rad >=SYSDATE
            AND a.rad = b.effdate(+)
            AND (a.custno || '-INCAP') = b.cal(+)
   GROUP BY a.custno,
            a.stockto,
            a.rad,
              TO_CHAR (a.rad, 'IYYY') || TO_CHAR (a.rad, 'IW') ,
            --a.weekno,
            a.dayname,
            b.avail
            
   UNION ALL
   SELECT
        b.custno
      , b.stockto
      , TO_CHAR(a.dateholder, 'DY')                                 "DAYNAME"
      , TO_CHAR(a.dateholder, 'IYYY') || TO_CHAR(a.dateholder, 'IW') "WEEKNO"
      , a.dateholder                                                "RAD"
      , 0                                                           "TDY_LOAD_CNT"
      , NVL(TO_NUMBER(c.avail), 0)                                  "TDY_CAP"
      , DECODE (TRUNC (a.dateholder),  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),  NULL)   "PRIOR_LOAD_CNT"
FROM (SELECT
                TRUNC(SYSDATE) - 1 + LEVEL AS dateholder
      FROM DUAL
      CONNECT BY LEVEL <= 180
      ) a
INNER JOIN (SELECT
                custno
              , stockto
              , MAX(rad) "LAST_RAD"
            FROM JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY
            GROUP BY
                custno
              , stockto
            ) b ON a.dateholder <= b.last_rad
LEFT JOIN (SELECT
                cal
              , (eff / 1440 + TO_DATE('01011970', 'MMDDYYYY')) "EFFDATE"
              , avail
            FROM caldata
            ) c ON a.dateholder = c.effdate and (b.custno || '-INCAP') = c.cal
--ORDER BY a.dateholder

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
I'm pretty sure we all know where remote operations come from.

Your shortened query still uses the view.  You have to not use the view and directly query the base tables that you need.  If you are looking for someone to do that for you, maybe someone else can help as I don't know anything about EBS.
0
 

Author Comment

by:pardeshirahul
Comment Utility
Okay i will write that query
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
You said: "but trunc is very costly", and you posted this example:
WHERE     a.rad >= TRUNC (SYSDATE)

It is true in some cases, that "trunc" can be very costly.  But that is *NOT* the problem here!

This is *NOT* your performance problem: "... >= TRUNC (SYSDATE)"

If you use "trunc" and apply that to an indexed column in a "where" clause, that is usually very bad in terms of performance.

You need to work on building a simple, efficient query to replace the big, complex, slow query.
0
 

Author Comment

by:pardeshirahul
Comment Utility
okay i will try to write a simple query
0
 

Author Comment

by:pardeshirahul
Comment Utility
This is the one of the unions in the view  most of them are calling the same tables


SELECT MTP.ORGANIZATION_CODE "INVENTORY_ORG_CODE",
       MSI.SEGMENT1 "ITEM_NO",
       MSI.DESCRIPTION "ITEM_DESCRIPTION",
       PLL.QUANTITY "QUANTITY_ORDERED",
       PLL.QUANTITY_RECEIVED "QUANTITY_RECEIVED",
       PLL.QUANTITY_CANCELLED "QUANTITY_CANCELLED",
       rsl.quantity_shipped "QUANTITY_SHIPPED",
       (  NVL (PLL.QUANTITY, 0)
        - NVL (PLL.QUANTITY_CANCELLED, 0)
        - NVL (PLL.quantity_received, rsl.quantity_shipped))
          "QUANTITY_DUE",
       POL.UNIT_MEAS_LOOKUP_CODE "UOM",
       POH.SEGMENT1 || '-' || PRA.RELEASE_NUM "DOCUMENT_NO",
       PRA.REVISION_NUM "REVISION",
       NVL (PRA.CREATION_DATE, POH.CREATION_DATE) "ORDER_DATE",
       PLL.PROMISED_DATE "PROMISED_DATE",
       NVL (RSH.EXPECTED_RECEIPT_DATE,
            NVL (PLL.PROMISED_DATE, PLL.NEED_BY_DATE))
          "EXPECTED_DATE",
       NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') "STATUS",
       PV.SEGMENT1 "SUPPLIER_NO",
       PV.VENDOR_NAME "SUPPLIER_NAME",
       asa.vendor_site_code "SUPPLIER_SITE_CODE",
       ood.organization_code "SUPPLIER_WHSE_CODE",
       ood.organization_name "SUPPLIER_WHSE_NAME",
       'BLANKET RELEASE' "DOCUMENT_TYPE",
       POL.LINE_NUM,
       PLL.SHIPMENT_NUM,
       DECODE (TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'),
               '00:00', NULL,
               TO_CHAR (PLL.NEED_BY_DATE, 'HH24:MI'))
          "DELIVERY_TIME",
       PLL.ATTRIBUTE2 "RAIL_CAR",
       POL.UNIT_PRICE,
       POH.CURRENCY_CODE,
       DECODE (MSI.ATTRIBUTE1,  NULL, NULL,  'Y', 'INSPECT',  'UNKNOWN')
          "INSPECTION_REQUIRED",
       POH.NOTE_TO_RECEIVER "HEADER_NOTE_TO_RECEIVER",
       PLL.NOTE_TO_RECEIVER "SHIPMENT_NOTE_TO_RECEIVER",
       POH.NOTE_TO_RECEIVER || ' ' || PLL.NOTE_TO_RECEIVER "NOTE_TO_RECEIVER",
       NVL (
          (SELECT gws.jms_inv_sys_code
             FROM jms_gem_wms_sku gws
            WHERE     gws.plant_code = mtp.organization_code
                  AND gws.item_no = MSI.SEGMENT1
                  AND ROWNUM < 2),
          'N')
          "WMS_ITEM",
       RSL.PACKING_SLIP "PACKING_SLIP",
       RSh.BILL_OF_LADING "BILL_OF_LADING",
       RSH.SHIPMENT_NUM "ASN_SHIPMENT_NUM",
       rsh.freight_carrier_code "FREIGHT_CARRIER_CODE",
       rsh.carrier_equipment "SCAC",
       rsh.num_of_containers "PALLET_COUNT",
       rsl.truck_num "ASN_TRUCK_NUM",
       rsl.container_num "ASN_SEAL",
       RSL.asn_line_flag "ASN_FLAG",
       rsh.shipped_date "EXPO_DATE",
       rsl.comments "ASN_LINE_COMMENTS",
       rsh.comments "ASN_HEADER_COMMENTS",
       poh.freight_terms_lookup_code "INCO_TERMS",
       'ASN' "ORDER_TYPE",
       pra.attribute15 "TRANSPORTATION_NOTES",
       'BLANKET_PO_ASN_NOT_RECEIVED' QUERY_STATUS,
       rsh.special_handling_code IRP_STW_IDENTIFIER,
       NULL RECEIPT_WITH_ASN_RECEIVED
  FROM RCV_SHIPMENT_HEADERS RSH,
       rcv_shipment_lines rsl,
       PO_VENDORS PV,
       ap_supplier_sites_all asa,
       PO_LINE_LOCATIONS_ALL PLL,
       MTL_SYSTEM_ITEMS MSI,
       PO_RELEASES_ALL PRA,
       MTL_PARAMETERS MTP,
       PO_HEADERS_ALL POH,
       PO_LINES_ALL POL,
       ORG_ORGANIZATION_DEFINITIONS OOD
 WHERE     1 = 1
       AND MTP.ORGANIZATION_ID = PLL.SHIP_TO_ORGANIZATION_ID
       AND POH.VENDOR_ID = PV.VENDOR_ID
       AND poh.vendor_site_id = asa.vendor_site_id
       AND asa.address_line4 = ood.organization_code(+)
       AND asa.vendor_id = pv.vendor_id
       AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
       AND POL.PO_LINE_ID = PLL.PO_LINE_ID
       AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
       AND MSI.INVENTORY_ITEM_ID = POL.ITEM_ID
       AND MSI.ORGANIZATION_ID = 0
       AND PLL.PO_RELEASE_ID = PRA.PO_RELEASE_ID
       AND POH.TYPE_LOOKUP_CODE = 'BLANKET'
       AND PLL.SHIPMENT_TYPE = 'BLANKET'
       AND NVL (PLL.CLOSED_CODE, 'OPEN') NOT IN ('FINALLY CLOSED',
                                                 'CLOSED',
                                                 'CLOSED FOR RECEIVING')
       AND NVL (PLL.APPROVED_FLAG, 'N') = 'Y'
       AND NVL (PLL.CANCEL_FLAG, 'N') = 'N'
       AND PLL.QUANTITY - PLL.QUANTITY_CANCELLED - PLL.QUANTITY_RECEIVED > 0
       AND NVL (PRA.AUTHORIZATION_STATUS, 'INCOMPLETE') != 'INCOMPLETE'
       AND rsl.PO_LINE_LOCATION_ID = pll.line_location_id
       AND rsl.quantity_shipped - rsl.quantity_received > 0
       AND rsl.shipment_line_status_code = 'EXPECTED'
       AND rsl.asn_line_flag = 'Y'
       AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID

Open in new window

0
 

Author Comment

by:pardeshirahul
Comment Utility
an this is it explain plan

Plan
SELECT STATEMENT  ALL_ROWSCost: 7,222  Bytes: 541  Cardinality: 1  															
	3 COUNT STOPKEY  														
		2 TABLE ACCESS BY INDEX ROWID TABLE JMSUSER.JMS_GEM_WMS_SKU Cost: 2  Bytes: 16  Cardinality: 1  													
			1 INDEX UNIQUE SCAN INDEX (UNIQUE) JMSUSER.JMS_GEM_WMS_SKU_X1 Cost: 1  Cardinality: 1  												
	50 HASH JOIN OUTER  Cost: 7,222  Bytes: 541  Cardinality: 1  														
		33 NESTED LOOPS  Cost: 7,167  Bytes: 415  Cardinality: 1  													
			31 NESTED LOOPS  Cost: 7,167  Bytes: 415  Cardinality: 1  												
				29 NESTED LOOPS  Cost: 7,165  Bytes: 378  Cardinality: 1  											
					26 NESTED LOOPS  Cost: 7,162  Bytes: 331  Cardinality: 1  										
						23 NESTED LOOPS  Cost: 7,160  Bytes: 298  Cardinality: 1  									
							20 NESTED LOOPS  Cost: 7,159  Bytes: 289  Cardinality: 1  								
								17 NESTED LOOPS  Cost: 7,156  Bytes: 206  Cardinality: 1  							
									15 HASH JOIN  Cost: 7,155  Bytes: 200  Cardinality: 1  						
										13 HASH JOIN  Cost: 6,698  Bytes: 279,200  Cardinality: 1,745  					
											11 HASH JOIN  Cost: 6,152  Bytes: 239,202  Cardinality: 1,746  				
												9 NESTED LOOPS  Cost: 3,951  Bytes: 456,402  Cardinality: 5,307  			
													7 NESTED LOOPS  Cost: 3,951  Bytes: 456,402  Cardinality: 5,307  		
														5 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_SYSTEM_ITEMS_B Cost: 294  Bytes: 61,301  Cardinality: 1,039  	
															4 INDEX RANGE SCAN INDEX INV.MTL_SYSTEM_ITEMS_B_N10 Cost: 5  Cardinality: 1,039  
														6 INDEX RANGE SCAN INDEX PO.PO_LINES_N1 Cost: 2  Cardinality: 5  	
													8 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINES_ALL Cost: 7  Bytes: 135  Cardinality: 5  		
												10 TABLE ACCESS FULL TABLE PO.PO_HEADERS_ALL Cost: 2,199  Bytes: 5,841,132  Cardinality: 114,532  			
											12 TABLE ACCESS FULL TABLE AP.AP_SUPPLIER_SITES_ALL Cost: 545  Bytes: 2,688,999  Cardinality: 116,913  				
										14 TABLE ACCESS FULL TABLE AP.AP_SUPPLIERS Cost: 456  Bytes: 3,204,400  Cardinality: 80,110  					
									16 INDEX UNIQUE SCAN INDEX (UNIQUE) AR.HZ_PARTIES_U1 Cost: 1  Bytes: 6  Cardinality: 1  						
								19 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_LINE_LOCATIONS_ALL Cost: 3  Bytes: 83  Cardinality: 1  							
									18 INDEX RANGE SCAN INDEX PO.PO_LINE_LOCATIONS_N2 Cost: 2  Cardinality: 2  						
							22 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1  								
								21 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  							
						25 TABLE ACCESS BY INDEX ROWID TABLE PO.PO_RELEASES_ALL Cost: 2  Bytes: 33  Cardinality: 1  									
							24 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.PO_RELEASES_U1 Cost: 1  Cardinality: 1  								
					28 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_LINES Cost: 3  Bytes: 47  Cardinality: 1  										
						27 INDEX RANGE SCAN INDEX PO.RCV_SHIPMENT_LINES_N4 Cost: 2  Cardinality: 1  									
				30 INDEX UNIQUE SCAN INDEX (UNIQUE) PO.RCV_SHIPMENT_HEADERS_U1 Cost: 1  Cardinality: 1  											
			32 TABLE ACCESS BY INDEX ROWID TABLE PO.RCV_SHIPMENT_HEADERS Cost: 2  Bytes: 37  Cardinality: 1  												
		49 VIEW VIEW APPS.ORG_ORGANIZATION_DEFINITIONS Cost: 55  Bytes: 378  Cardinality: 3  													
			48 NESTED LOOPS  Cost: 55  Bytes: 369  Cardinality: 3  												
				46 NESTED LOOPS  Cost: 55  Bytes: 369  Cardinality: 3  											
					44 NESTED LOOPS  Cost: 54  Bytes: 342  Cardinality: 3  										
						41 NESTED LOOPS  Cost: 49  Bytes: 530  Cardinality: 5  									
							38 HASH JOIN  Cost: 39  Bytes: 400  Cardinality: 5  								
								36 HASH JOIN  Cost: 25  Bytes: 1,316  Cardinality: 28  							
									34 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS_TL Cost: 16  Bytes: 1,092  Cardinality: 28  						
									35 TABLE ACCESS FULL TABLE HR.HR_ALL_ORGANIZATION_UNITS Cost: 9  Bytes: 11,352  Cardinality: 1,419  						
								37 TABLE ACCESS FULL TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 14  Bytes: 8,316  Cardinality: 252  							
							40 TABLE ACCESS BY INDEX ROWID TABLE HR.HR_ORGANIZATION_INFORMATION Cost: 2  Bytes: 26  Cardinality: 1  								
								39 INDEX RANGE SCAN INDEX HR.HR_ORGANIZATION_INFORMATIO_FK2 Cost: 1  Cardinality: 1  							
						43 TABLE ACCESS BY INDEX ROWID TABLE GL.GL_LEDGERS Cost: 1  Bytes: 8  Cardinality: 1  									
							42 INDEX UNIQUE SCAN INDEX (UNIQUE) GL.GL_LEDGERS_U2 Cost: 0  Cardinality: 1  								
					45 INDEX UNIQUE SCAN INDEX (UNIQUE) INV.MTL_PARAMETERS_U1 Cost: 0  Cardinality: 1  										
				47 TABLE ACCESS BY INDEX ROWID TABLE INV.MTL_PARAMETERS Cost: 1  Bytes: 9  Cardinality: 1

Open in new window

0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
I'm not sure what you expect us to do with that.

The query plan isn't a valid match to the view either.  The view specifies a hint and that hint is not present in the plan that you posted.

Think of it this way.  You are looking to retrieve less than 10 columns from the database.  Then take a look at the explain plan that you are posting.  Do you really think you need to access that many tables/views/remote tables to get to it?  I bet that the view you are trying to use is a view that is based on a view that is based on many more views.  Rather than taking the time to do it right people just kept adding views.  Now you are the one to pay the price.
0
 

Author Comment

by:pardeshirahul
Comment Utility
i will write a view with only those 3 columns
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
access the 3 wanted columns from the source tables, you don't absolutely require a view to do that for your query in the question.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
True, a view may not be required.  But, depending on which base tables are involved, and on how many base tables are involved ( I didn't take the time to dig through the current view definition to see) and on whether the tables are all on the remote database, or not, a view may be helpful, and may allow the report query to look much simpler.  If the base tables are all in the remote DB, and a join (or joins) is/are required, a view in that database will be very helpful.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Im pretty sure the big view DOES reuse other views.
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Yes, the big view does have multiple other views in it.  The last plan posted (https://www.experts-exchange.com/questions/28970058/how-to-tune-this-sql-query.html#a41807409) clearly shows another view being materialized within that query.
0
 

Author Comment

by:pardeshirahul
Comment Utility
which view is that
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Which view?

That massive big view you use in the query you provided in the originating question.  That view has a horrible explain plan and it references at least one other view which adds to the complexity of the explain plan.

The view we recommend you stop using.

I would go up to find the actual name but on a phone there is way too much scrolling. But there is a view we have been discussing for quite some time. It's that view we refer to.

Stop using that view in your query.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
I now have a keyboard and laptop and can scroll up quickly. The view we have been referring to is shown below:

SELECT DISTINCT custno, stockto, MAX (rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_order
        GROUP BY custno, stockto
0
 

Author Comment

by:pardeshirahul
Comment Utility
Thanks
0
 

Author Comment

by:pardeshirahul
Comment Utility
This query is creating an issue

SELECT DISTINCT custno, stockto, MAX (rad) "LAST_RAD"
            FROM xxx_ess_scp_ib_cap_order
        GROUP BY custno, stockto

and it is in a view can i use like with  option whicle creating a view
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Yes, that query is a huge performance problem, as I told you on Sept 15, in my first comment in this question.  The main problem with this query is:  it does *NOT* include a "where" clause!  That means it forces Oracle to read *EVERY* record that this view includes.

Now, to answer your latest question, yes it is (likely) possible to use a "with" clause in a view definition.  (I say likely, because that is valid query syntax in Oracle, but I've never tested it in a view definition.)  That doesn't mean though that the "with" option is a good solution in this case.  It depends on how many records the view can include, how much RAM your server has, and on how often you query this view.

A much simpler answer may be to add a "where" clause to your query from this view.
0
 

Author Comment

by:pardeshirahul
Comment Utility
How to incorporate the with option in this select statement

 SELECT
        b.custno
      , b.stockto
      , TO_CHAR(a.dateholder, 'DY')                                 "DAYNAME"
      , TO_CHAR(a.dateholder, 'IYYY') || TO_CHAR(a.dateholder, 'IW') "WEEKNO"
      , a.dateholder                                                "RAD"
      , 0                                                           "TDY_LOAD_CNT"
      , NVL(TO_NUMBER(c.avail), 0)                                  "TDY_CAP"
      , DECODE (TRUNC (a.dateholder),  TRUNC (SYSDATE), jms_ess_scp_ib_cap_prior (b.custno),  NULL)   "PRIOR_LOAD_CNT"
FROM (SELECT
                TRUNC(SYSDATE) - 1 + LEVEL AS dateholder
      FROM DUAL
      CONNECT BY LEVEL <= 180
      ) a
INNER JOIN (SELECT
                custno
              , stockto
              , MAX(rad) "LAST_RAD"
            FROM JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY
            GROUP BY
                custno
              , stockto
            ) b ON a.dateholder <= b.last_rad
LEFT JOIN (SELECT
                cal
              , (eff / 1440 + TO_DATE('01011970', 'MMDDYYYY')) "EFFDATE"
              , avail
            FROM caldata
            ) c ON a.dateholder = c.effdate and (b.custno || '-INCAP') = c.cal
--ORDER BY a.dateholder
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
The "with" option in queries isn't the solution to every performance problem in Oracle.  That is just one tool among many that can help.  In your latest post you have this sub-query with no "where" clause:

(SELECT
                 custno
               , stockto
               , MAX(rad) "LAST_RAD"
             FROM JMSUSER.JMS_ESS_SCP_IB_CAP_SUMMARY
             GROUP BY
                 custno
               , stockto
             )

That looks to me like the obvious place to make a change to make the entire query faster.  Is there no possible way to add a filter or two in this sub-query?
0
 

Author Comment

by:pardeshirahul
Comment Utility
i can't even create a clustered index we have oracle 11g
0
 

Author Comment

by:pardeshirahul
Comment Utility
clustered index on  custno
               , stockto
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
Why are we still trying to go down the same rat hole of using these giant views (JMS_ESS_SCP_IB_CAP_SUMMARY and xxx_ess_scp_ib_cap_order)?  They are never going to perform well and are going to be nearly impossible to tune.

You need to start over and go directly against the few tables that you actually need to access.
0
 

Author Comment

by:pardeshirahul
Comment Utility
okay
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Have you written and tested a query yet that selects only these three values:  custno, stockto, MAX(rad) "LAST_RAD" from the base tables for the complex view that your main query uses now?
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
In your other question you indicated that you now have a query that runs in 24 minutes, but you didn't tell us if that time is acceptable, or not.  What is your expectation of how long this query should take?
0
 

Author Comment

by:pardeshirahul
Comment Utility
previously it was taking 57 minutes to run so i divided into 2 unions one with
having filter for count(*)=1

and another having count(*) > 1

and now it is taking 23 minutes
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Is that acceptable, or not?  You have not told us what your expectation is of how fast you think this should run.

You also haven't told us if you've tried re-writing the big, complex view query into a much simpler (and likely much faster) view or sub-query that only gathers the columns you need.  Have you tried that?
0
 

Author Comment

by:pardeshirahul
Comment Utility
yes i tried that by writing 2 union with different data set
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
If these are the same queries that were posted before, then that is not what we are asking for.  You just took the view query and ran it outside the view.  They are still going against other underlying views and not the base tables.

If you need help with a query, you need to post the query and the plan.  If the query is still using the underlying views and going against over 70 tables, we are just going in circles.
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
But didn't your re-write still include references to big, complex E-Business Suite views?

And, you still haven't told us how much time you think these queries should take.
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
23 minutes is still a very long time for a query.

I wouldnt permit it in my production environment.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now