Sequence Of Firing Triggers In Oracle Forms

Navigational events occur at different levels of the Form Builder object hierarchy (Form, Block, Record, Item). Navigational triggers fire in response to some navigational events in the below order:

- Logon Triggers are fired first in below sequence:

1.PRE-LOGON
2.ON-LOGON
3.POST-LOGON

- After that, Pre Triggers are fired

1. PRE-FORM

2. PRE-BLOCK

3. PRE-TEXT-ITEM

- After that, WHEN-NEW Triggers are fired

1. WHEN-NEW-FORM-INSTANCE

2. WHEN-NEW-BLOCK-INSTANCE

3. WHEN-NEW-RECORD-INSTANCE

4. WHEN-NEW-ITEM-INSTANCE

- After this focus is on the first item of the Block. If we type some data and press the tab key following trigger will fire in sequence

1.KEY-NEXT-ITEM (This trigger is present on the item level).

2.POST-CHANGE (This trigger is present on the item level).

3.WHEN-VALIDATE-ITEM (This trigger is present on the item level).

4.POST-TEXT-ITEM (This trigger is present on the item level).

5.WHEN-NEW-ITEM-INSTANCE (Block Level Trigger).

- After That, POST TRIGGERS are fired

1. POST-BLOCK
2. POST-FORM

Read more »

Kill Session in Oracle

Retrieve session identifiers and session serial number (which uniquely identifies a session's objects):

select sid, serial# from v$session where username = 'USER'

kill the session:

alter system kill session 'sid,serial#'

Disconnect the session:

alter system disconnect session 'sid,serial#' post_transaction;

alter system disconnect session 'sid,serial#' immediate;

Read more »

Collection and Record In Oracle

A collection is an ordered group of elements, all of the same type. 

In a collection, the internal components are always of the same data type, and are called elements. We can access each element by its unique subscript. e.g. Lists and arrays.

A record is a group of elements, which can be of different types. 

In a record, the internal components can be of different data types, and are called fields. We can access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

PL/SQL has 3 collection types as below:

• Index-by tables, also known as associative arrays,  are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.
•  Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. We can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.
• Varrays (short for variable-size arrays) hold a fixed number of elements (although we can change the number of elements at runtime). They use sequential numbers as subscripts. We can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

Collection Type	Number of Elements	Subscript Type	Dense or Sparse	Where Created
Associative array (or index-by table)	Unbounded	String or integer	Either	Only in PL/SQL block
Nested table	Unbounded	Integer	Starts dense, can become sparse	Either in PL/SQL block or at schema level
Variable-size array (varray)	Bounded	Integer	Always dense	Either in PL/SQL block or at schema level

Read more »

DENSE_RANK in Oracle/PL-SQL

DENSE_RANK Function:

• Returns the rank of a value in a group of values.
• A built in analytic function which is used to rank a record within a group of rows. 
• Return type is number and serves for both aggregate and analytic purpose in SQL.
• Rows with equal values for the ranking criteria receive the same rank.
• The ranks are consecutive. No ranks are skipped if there are ranks with multiple items.
  
  Examples:
  1.  Query to return the dense_rank for a $50000 salary(Single Column DENSE_RANK)

  
  SELECT DENSE_RANK(50000) WITHIN GROUP
  (ORDER BY salary DESC NULLS LAST) SAL_RANK
  FROM employees;
  

  
  

  2.  Query to return the dense_rank for an employee with a salary of $50,000 

  and a commission of 10%$(Multiple Column DENSE_RANK)

  
  SELECT DENSE_RANK(10,50000) WITHIN GROUP
  (ORDER BY commission_pct, salary) SAL_RANK
  FROM employees;

  
  

  3. Query to rank the employees in department '60' based on their salaries. Identical salary values receive the same rank. However, no rank values are skipped. 

  SELECT department_id, last_name, salary,

         DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary) DENSE_RANK

    FROM employees 

    WHERE department_id = 60

    ORDER BY DENSE_RANK, last_name;

  
  

  
  

Read more »

RANK In Oracle/ PL-SQL

RANK Function:

• Returns the rank of a value in a group of values.
• A built in analytic function which is used to rank a record within a group of rows. 
• Return type is number and serves for both aggregate and analytic purpose in SQL.
• Rows with equal values for the ranking criteria receive the same rank.
• Ties are assigned the same rank, with the next ranking(s) skipped. So, if we have 3 items at rank 2, the next rank listed would be ranked 5.

Examples:

1.  Query to return the rank for a $50000 salary(Single Column RANK)

SELECT RANK(50000) WITHIN GROUP
(ORDER BY salary DESC NULLS LAST) SAL_RANK
FROM employees;

2.  Query to return the rank for an employee with a salary of $50,000 and a commission of 10%$(Multiple Column RANK)

SELECT RANK(.10,50000) WITHIN GROUP
(ORDER BY commission_pct, salary) RANK
FROM employees;

3.  Query to find the employee with the nth highest salary

SELECT *
FROM (
  SELECT employee_id, last_name, salary,
  RANK() OVER (ORDER BY salary DESC) EMPRANK
  FROM employees)
WHERE emprank = n;

4. Query to rank the employees  in department 60 based on their salaries. 
Identical salary values receive the same rank and cause nonconsecutive ranks.

SELECT department_id, last_name, salary,

    RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK

  FROM employees WHERE department_id = 60

  ORDER BY RANK, last_name;

Read more »

Bulk Collect - Save Exceptions

The SAVE EXCEPTIONS clause will record any exception during the bulk operation, and still continue processing.

BULK COLLECT construct is used to work with batches of data rather than single record at a time. Whenever we have to deal with large amount of data, bulk collect provides considerable performance improvement.

Declare 
cursor cur_emp 
is 
select * from Emp; 
  
type array is table of c%rowtype;
l_data array; 
dml_errors EXCEPTION; 
PRAGMA exception_init(dml_errors, -24381); 
l_errors number; 
l_errno number; 
l_msg varchar2(4000); 
l_idx number;

Begin 
open cur_emp;
loop 
 fetch cur_emp bulk collect into l_data limit 100;
begin forall i in 1 .. l_data.count SAVE EXCEPTIONS 
 insert into t2 values l_data(i);
exit when cur_emp%notfound;
end loop;
close cur_emp;

Exception
when DML_ERRORS 
then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;

DBMS_OUTPUT.PUT_LINE(‘Error #’ || i || ‘ occurred during ‘||‘iteration #’ || l_idx);

DBMS_OUTPUT.PUT_LINE(‘Error message is ‘ || l_msg);

end loop;
end;
/

Read more »

Oracle Regular Expression

Regular expressions specify patterns to search for in string data using standardized syntax conventions. A regular expression can specify complex patterns of character sequences. For example, the following regular expression:

a(b|c)d

searches for the pattern: 'a', followed by either 'b' or 'c', then followed by 'd'. This regular expression matches both 'abd' and 'acd'.

Oracle Database 11g offers five regular expression functions as below:

1. REGEXP_LIKE
2. REGEXP_SUBSTR
3. REGEXP_REPLACE
4. REGEXP_INSTR
5. REGEXP_COUNT

REGEXP_LIKE(source, regexp, modes) :

This function searches a character column for a pattern. 

source parameter - is the string or column the regex should be matched against. 

regexp parameter - is a string with the regular expression. 

modes parameter -  is optional. It sets the matching modes.

• In SQL, can be used in the WHERE and HAVING clauses of a SELECT statement to return rows matching the regular expression specified.  

          Example:

         SELECT * FROM emp 
     WHERE REGEXP_LIKE (first_name, '^Ste(v|ph)en$');

     FIRST_NAME           LAST_NAME
     -------------------- -------------------------
     Steven               King
     Steven               Markle
     Stephen              Stiles

• In PL/SQL script, it returns a Boolean value. It can be used in Check Conditions.

       Example:

      IF REGEXP_LIKE('subject', 'regexp') 

      THEN 

          /* Match */ 

      ELSE 

          /* No match */ 

      END IF;

REGEXP_SUBSTR(source, regexp, position, occurrence, modes) :

This function returns the actual substring matching the regular expression pattern specified. If the match attempt fails, NULL is returned. 

position parameter - specifies the character position in the source string at which the match attempt should start. The first character has position 1. 

occurrence parameter - specifies which match to get. Set it to 1 to get the first match. If you specify a higher number, Oracle will continue to attempt to match the regex starting at the end of the previous match, until it found as many matches as you specified. The last match is then returned. If there are fewer matches, NULL is returned. 

Example:

The following example examines the string, looking for the first substring bounded by commas. Oracle Database searches for a comma followed by one or more occurrences of non-comma characters followed by a comma. Oracle returns the substring, including the leading and trailing commas.

SELECT
  REGEXP_SUBSTR('500 Oracle Parkway, Redwood Shores, CA',',[^,]+,')"REGEXPR_SUBSTR"
  FROM DUAL;
REGEXPR_SUBSTR
-----------------
, Redwood Shores,

REGEXP_REPLACE(source, regexp, replacement, position, occurrence, modes) 

This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern specified.

Example:

The following example examines phone_number, looking for the pattern xxx.xxx.xxxx. Oracle reformats this pattern with (xxx) xxx-xxxx.

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM emp;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

REGEXP_INSTR(source, regexp, position, occurrence, return_option, modes) 

This function searches a string for a given occurrence of a regular expression pattern. If we specify, which occurrence we want to find and the start position to search from, this function returns an integer indicating the position in the string where the match is found.

Example:

The following example examines the string, looking for occurrences of one or more non-blank characters. Oracle begins searching at the first character in the string and returns the starting position (default) of the sixth occurrence of one or more non-blank characters.

SELECT
  REGEXP_INSTR('500 Oracle Parkway, Redwood Shores, CA',
               '[^ ]+', 1, 6) "REGEXP_INSTR"
  FROM DUAL;
REGEXP_INSTR
------------
          37

REGEXP_COUNT(source, regexp, position, modes) 

This function returns the number of times the regex can be matched in the source string. It returns zero if the regex finds no matches at all. This function is only available in Oracle 11g and later.

Example:

SELECT REGEXP_COUNT(first_name, 'S', 1) FROM emp;

Read more »

Query - Get Supply-Demand for an Item for an org (As in Oracle Supply Demand Form)

--Reserved Sales Orders
SELECT
  d.requirement_date Required_date ,
  ml.meaning Supply_Demand_Type,
  to_char(ooha.order_number) Identifier,
  -1 * ( d.primary_uom_quantity - GREATEST (NVL (d.reservation_quantity, 0), d.completed_quantity) ) quantity,
  oola.line_id,
  oola.request_date,
  wda.delivery_id
FROM
  mtl_parameters p,
  mtl_system_items i,
  bom_calendar_dates c,
  mtl_demand d,
  mfg_lookups ml,
  (
    SELECT
      DECODE (demand_source_type, 2, DECODE (reservation_type, 1, 2, 3, DECODE
      (supply_source_type, 5, 23, 31), 9 ), 8, DECODE (reservation_type, 1, 21,
      22), demand_source_type) supply_demand_source_type,
      demand_id
    FROM
      mtl_demand
  )
  dx,
  oe_order_headers_all ooha,
  oe_order_lines_all oola,
  wsh_delivery_assignments wda,
  wsh_delivery_details wdd
WHERE
  1                        =1
AND d.demand_source_line   = oola.line_id
AND ooha.header_id         = oola.header_id
AND d.organization_id      = p_org_id
AND d.demand_id            = dx.demand_id
AND wdd.source_line_id(+)  = oola.line_id
AND wdd.source_header_id(+)= oola.header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id(+)
AND ml.lookup_type         = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code         = dx.supply_demand_source_type
AND d.primary_uom_quantity > GREATEST (NVL (d.reservation_quantity, 0),
  d.completed_quantity)
AND d.inventory_item_id   = p_inventory_item_id 
AND d.available_to_atp    = 1
AND d.reservation_type   != -1
AND d.demand_source_type != 13
AND d.demand_source_type != -1
AND
  (
    d.subinventory  IS NULL
  OR d.subinventory IN
    (
      SELECT
        s.secondary_inventory_name
      FROM
        mtl_secondary_inventories s
      WHERE
        s.organization_id      = d.organization_id
      AND s.inventory_atp_code = 1
    )
  )
AND i.organization_id           = d.organization_id
AND i.inventory_item_id         = d.inventory_item_id
AND p.organization_id           = d.organization_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date             = TRUNC (d.requirement_date)
AND d.inventory_item_id         = DECODE (d.reservation_type, 1, DECODE (
  d.parent_demand_id, NULL, d.inventory_item_id, -1 ), 2, d.inventory_item_id,
  3, d.inventory_item_id,                        -1 )
UNION
-- Sales Orders and Internal Sales Orders
SELECT   d.requirement_date required_date, ml.meaning Supply_demand_Type,
to_char(ooha.order_number) Identifier,
NVL(  -1
       * (  d.primary_uom_quantity
          - d.total_reservation_quantity
          - d.completed_quantity
         ), 0) Quantity  ,
oola.line_id,
oola.request_date ,
wda.delivery_id  
  FROM mtl_parameters p,
       mtl_system_items i,
       bom_calendar_dates c,
       mrp_demand_om_reservations_v d,
       oe_order_headers_all ooha,
       oe_order_lines_all oola,
  wsh_delivery_assignments wda,
       wsh_delivery_details wdd,
       mfg_lookups ml,
       (select DECODE (demand_source_type,
               2, DECODE (reservation_type, 1, 2, 3, 23, 9),
               8, DECODE (reservation_type, 1, 21, 22),
               demand_source_type
              ) supply_demand_source_type, demand_id from  mrp_demand_om_reservations_v) dx
 WHERE d.open_flag = 'Y'
   AND ml.lookup_type = 'MTL_SUPPLY_DEMAND_SOURCE_TYPE'
   and ml.lookup_code = dx.supply_demand_source_type
   and d.demand_id = dx.demand_id
   AND ooha.header_id = oola.header_id
   and oola.line_id = d.demand_id
   AND wdd.source_line_id(+)   = oola.line_id
AND wdd.source_header_id(+)    = oola.header_id
AND wdd.delivery_detail_id     = wda.delivery_detail_id(+)
   AND d.reservation_type != 2
   AND d.organization_id = p_org_id
   AND d.primary_uom_quantity >
                        (d.total_reservation_quantity + d.completed_quantity
                        )
   AND d.inventory_item_id = p_inventory_item_id
   AND (   d.visible_demand_flag = 'Y'
        OR (    NVL (d.visible_demand_flag, 'N') = 'N'
            AND d.ato_line_id IS NOT NULL
            AND NOT EXISTS (
                   SELECT NULL
                     FROM oe_order_lines_all ool, mtl_demand md
                    WHERE TO_CHAR (ool.line_id) = md.demand_source_line
                      AND ool.ato_line_id = d.ato_line_id
                      AND ool.item_type_code = 'CONFIG'
                      AND md.reservation_type IN (2, 3))
           )
       )
   AND d.reservation_type != -1
   AND d.reservation_type != -1
   AND d.demand_source_type != -1
   AND d.demand_source_type != -1
   AND (d.subinventory IS NULL
        OR d.subinventory IN (
              SELECT s.secondary_inventory_name
                FROM mtl_secondary_inventories s
               WHERE s.organization_id = d.organization_id
                 AND s.inventory_atp_code = 1
                 AND s.attribute1 = 'FG')
                 )
   AND i.organization_id = d.organization_id
   AND i.inventory_item_id = d.inventory_item_id
   AND p.organization_id = d.organization_id
   AND p.calendar_code = c.calendar_code
   AND p.calendar_exception_set_id = c.exception_set_id
   AND c.calendar_date = TRUNC (d.requirement_date)
   AND d.inventory_item_id =
          DECODE (d.reservation_type,
                  1, DECODE (d.parent_demand_id,
                             NULL, d.inventory_item_id,
                             -1
                            ),
                  2, d.inventory_item_id,
                  3, d.inventory_item_id,
                  -1
                 )
UNION
--WIP DEMAND
 SELECT   o.date_required required_date,    ml.meaning Supply_Demand_Type, we.wip_entity_name Identifier,
        LEAST (-1 * (o.required_quantity - o.quantity_issued), 0) quantity, NULL, NULL,NULL
  FROM                                           
       mtl_parameters p,
       mfg_lookups ml,
       -- mtl_atp_rules r,
       mtl_system_items i,
       bom_calendar_dates c,
       wip_requirement_operations o,
       wip_discrete_jobs d,
       wip_entities we,
       (select DECODE (job_type, 1, 5, 7) supply_demand_source_type, wip_entity_id from wip_discrete_jobs) dx
 WHERE 1 = 1
 and we.wip_entity_id = d.wip_entity_id
   AND ml.lookup_type         = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
  AND ml.lookup_code         = dx.supply_demand_source_type
  and d.wip_entity_id = dx.wip_entity_id
   AND o.organization_id = d.organization_id
   AND o.organization_id = p_org_id
   AND o.inventory_item_id = p_inventory_item_id
   AND o.wip_entity_id = d.wip_entity_id
   AND o.wip_supply_type NOT IN (5, 6)
   AND o.required_quantity > 0
   AND o.required_quantity <> (o.quantity_issued)
   AND o.operation_seq_num > 0
   AND o.date_required IS NOT NULL
   AND (   o.supply_subinventory IS NULL
        OR EXISTS (
              SELECT 'X'
                FROM mtl_secondary_inventories s
               WHERE s.organization_id = o.organization_id
                 AND o.supply_subinventory = s.secondary_inventory_name
                 AND s.inventory_atp_code = 1)
       )
   AND d.status_type IN (1, 3, 4, 6)
   AND p.organization_id = o.organization_id
   AND i.organization_id = o.organization_id
   AND i.inventory_item_id = o.inventory_item_id
   AND p.calendar_code = c.calendar_code
   AND p.calendar_exception_set_id = c.exception_set_id
   AND c.calendar_date = TRUNC (o.date_required)
UNION
--WIP Supply
   SELECT
  d.scheduled_completion_date required_date,
  ml.meaning Supply_Demand_Type,
  we.wip_entity_name Identifier,
  (d.start_quantity - d.quantity_completed - d.quantity_scrapped )Quantity, NULL,NULL,NULL
FROM
  wip_discrete_jobs d,
  bom_calendar_dates c,
  mtl_parameters p,
  mtl_system_items i,
  wip_entities we,
  (
    SELECT
      DECODE (job_type, 1, 5, 7) supply_demand_source_type,
      wip_entity_id
    FROM
      wip_discrete_jobs
  )
  dx,
  mfg_lookups ml
WHERE
  1                              =1
AND d.wip_entity_id              = dx.wip_entity_id
AND dx.supply_demand_source_type = ml.lookup_code
AND ml.lookup_type               = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND d.wip_entity_id              = we.wip_entity_id
AND d.status_type               IN (1, 3, 4, 6)
AND
  (
    d.start_quantity - d.quantity_completed
  )
                      > 0
AND d.organization_id = p_org_id
AND d.primary_item_id = p_inventory_item_id
AND
  (
    d.completion_subinventory IS NULL
  OR EXISTS
    (
      SELECT
        'X'
      FROM
        mtl_secondary_inventories s
      WHERE
        s.organization_id           = d.organization_id
      AND d.completion_subinventory = s.secondary_inventory_name
      AND s.inventory_atp_code      = 1
    )
  )
AND p.organization_id           = d.organization_id
AND i.organization_id           = d.organization_id
AND i.inventory_item_id         = d.primary_item_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date             = TRUNC (d.scheduled_completion_date)
UNION ALL
SELECT
  d.scheduled_completion_date required_date,
  ml.meaning Supply_Demand_Type,
  we.wip_entity_name Identifier,
  (d.start_quantity - d.quantity_completed - d.quantity_scrapped ) Quantity,NULL,NULL,NULL
FROM
  mtl_parameters p,
  mtl_system_items i,
  bom_calendar_dates c,
  wip_requirement_operations o,
  wip_discrete_jobs d,
  wip_entities we,
  (
    SELECT
      DECODE (job_type, 1, 5, 7) supply_demand_source_type,
      wip_entity_id
    FROM
      wip_discrete_jobs
  )
  dx,
  mfg_lookups ml
WHERE
  1                             =1
AND d.wip_entity_id             = dx.wip_entity_id
AND dx.supply_demand_source_type= ml.lookup_code
AND ml.lookup_type              = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND we.wip_entity_id            = d.wip_entity_id
AND o.organization_id           = d.organization_id
AND o.inventory_item_id         = p_inventory_item_id
AND o.wip_entity_id             = d.wip_entity_id
AND o.organization_id           = p_org_id
AND o.wip_supply_type NOT      IN (5, 6)
AND o.required_quantity         < 0
AND
  (
    o.required_quantity - o.quantity_issued
  )
                        < 0
AND o.operation_seq_num > 0
AND
  (
    d.completion_subinventory IS NULL
  OR EXISTS
    (
      SELECT
        'X'
      FROM
        mtl_secondary_inventories s
      WHERE
        s.organization_id           = d.organization_id
      AND d.completion_subinventory = s.secondary_inventory_name
      AND s.inventory_atp_code      = 1
    )
  )
AND
  (
    d.job_type  = 1
  OR d.job_type = 3
  )
AND d.status_type              IN (1, 3, 4, 6)
AND d.organization_id           = o.organization_id
AND p.organization_id           = o.organization_id
AND i.organization_id           = o.organization_id
AND i.inventory_item_id         = o.inventory_item_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND c.calendar_date             = TRUNC (o.date_required)
UNION
--Purchase Orders:
SELECT c.next_date Required_Date,
  ml.meaning Supply_demand_Type,
  sx.identifier,
  DECODE (s.supply_type_code, 'SHIPMENT', s.to_org_primary_quantity,
  s.to_org_primary_quantity ) Quantity,NULL,NULL,NULL
FROM
  mtl_system_items i,
  mtl_parameters p,
  bom_calendar_dates c,
  mtl_supply s,
  mfg_lookups ml,
  (    SELECT
      DECODE (ms.po_header_id, NULL, DECODE (ms.supply_type_code, 'REQ', DECODE (
      ms.from_organization_id, NULL, 18, 20), 12 ), DECODE (ms.supply_type_code,
      'SHIPMENT', 35, 'RECEIVING', 36, 1) ) supply_demand_source_type,
      poh.segment1 Identifier,
      supply_source_id
    FROM
      mtl_supply ms,
      po_headers_all poh
    WHERE
      1=1
    AND poh.po_header_id = ms.po_header_id
  ) sx
WHERE
    1  = 1
AND s.supply_source_id = sx.supply_source_id
AND ml.lookup_type     = 'MRP_SUPPLY_DEMAND_SOURCE_TYPE'
AND ml.lookup_code     = sx.supply_demand_source_type
AND
  (
    (
      s.req_header_id  IS NULL
    AND s.po_header_id IS NULL
    )
  OR
    (
      s.req_header_id           = s.req_header_id
    AND s.from_organization_id IS NOT NULL
    )
  OR
    (
      s.supply_type_code        = 'REQ'
    AND s.from_organization_id IS NULL
    )
  OR s.po_header_id = s.po_header_id
  )
AND s.to_organization_id    = p_org_id
AND s.item_id               = p_inventory_item_id --v.inventory_item_id
AND s.destination_type_code = 'INVENTORY'
AND
  (
    s.to_subinventory IS NULL
  OR EXISTS
    (
      SELECT
        'X'
      FROM
        mtl_secondary_inventories s2
      WHERE
        s2.organization_id      = s.to_organization_id
      AND s.to_subinventory     = s2.secondary_inventory_name
      AND s2.inventory_atp_code = 1
      AND s2.availability_type  = s2.availability_type
    )
  )
AND i.organization_id           = s.to_organization_id
AND i.inventory_item_id         = s.item_id
AND p.organization_id           = s.to_organization_id
AND p.calendar_code             = c.calendar_code
AND p.calendar_exception_set_id = c.exception_set_id
AND NOT EXISTS
  (
    SELECT
      'X'
    FROM
      oe_drop_ship_sources odss
    WHERE
      DECODE (s.po_header_id, NULL, s.req_line_id, s.po_line_location_id ) =
      DECODE (s.po_header_id, NULL, odss.requisition_line_id,
      odss.line_location_id )
  )
AND c.calendar_date = TRUNC (s.expected_delivery_date)
/*
UNION
-- User Supply
SELECT expected_delivery_date Required_date,
       'User Supply' Supply_Demand_Type,
       source_name Identifier,
       SUM(primary_uom_quantity) quantity,
       NULL,
       NULL,NULL
  FROM mtl_user_supply
 WHERE inventory_item_id = p_inventory_item_id
   AND organization_id = p_org_id
   AND primary_uom_quantity <> '0'
 GROUP BY expected_delivery_date,
          creation_date,
          primary_uom_quantity,
          source_name
--
UNION
--Booked Sales Orders with NULL Scheduled Date
SELECT
  oola.schedule_ship_date Required_date,
  'Sales Order' Supply_Demand_Type,
  to_char(ooha.order_number) Identifier,
  -1 * (oola.ordered_quantity ) quantity,
  oola.line_id,
  oola.request_date,
  wda.delivery_id
FROM
  oe_order_headers_all ooha,
  oe_order_lines_all oola,
  wsh_delivery_assignments wda,
  wsh_delivery_details wdd
WHERE ooha.header_id           = oola.header_id
AND wdd.source_line_id(+)   = oola.line_id
AND wdd.source_header_id(+)    = oola.header_id
AND wdd.delivery_detail_id     = wda.delivery_detail_id(+)
AND oola.inventory_item_id     = p_inventory_item_id 
AND oola.ship_from_org_id      = p_org_id
AND oola.schedule_ship_date IS NULL
AND UPPER(ooha.flow_status_code) = 'BOOKED'
*/
ORDER BY Required_date,
          Supply_Demand_Type DESC,
          quantity
;

Read more »