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 »

Oracle Advanced Queuing - Troubleshooting

Debugging can be done by the following steps:

1. Check if messages are being propagated at all or the propagation is slow

• queue-to-dblink: The propagation delivers messages or events from the source queue to all subscribing queues at the destination database identified by the dblink. A single propagation schedule is used to propagate messages to all subscribing queues. Hence any changes made to this schedule will affect message delivery to all the subscribing queues. 
• queue-to-queue: This propagation mode delivers messages or events from the source queue to a specific destination queue identified on the database link. This allows the user to have fine-grained control on the propagation schedule for message delivery. This new propagation mode also supports transparent failover when propagating to a destination Oracle RAC system. With queue-to-queue propagation, you are no longer required to re-point a database link if the owner instance of the queue fails on Oracle RAC. This mode supports multiple propagations to the same target database if the target queues are different.

select TOTAL_NUMBER 
from DBA_QUEUE_SCHEDULES 
where QNAME=’<source_queue_name>’;


If TOTAL_NUMBER is increasing, then propagation is most likely functioning, although it may be slow.

2. Check if the database link to the destination database has been set up properly. 

3. Check Message State and Destination. Find the queue table for a given queue

select QUEUE_TABLE 
from DBA_QUEUES 
where NAME = &queue_name;

4. Check for messages in the source queue with

select count (*) 

from AQ$<source_queue_table>  

where q_name = 'source_queue_name';

5. Check for messages in the destination queue.

select count (*) 
from AQ$<destination_queue_table>  
where q_name = 'destination_queue_name';

6. Check to see who is using job queue processes.

7. Check which jobs are being run by querying dba_jobs_running. It is possible that other jobs are starving the propagation jobs.

8. Check to see that the queue table sys.aq$_prop_table_instno exists in DBA_QUEUE_TABLES. The queue sys.aq$_prop_notify_queue_instnomust also exist in DBA_QUEUES and must be enabled for enqueue and dequeue.

9. In case of Oracle Real Application Clusters (Oracle RAC), this queue table and queue pair must exist for each Oracle RAC node in the system. They are used for communication between job queue processes and are automatically created.

10. Check that the consumer attempting to dequeue a message from the destination queue is a recipient of the propagated messages.

11. Turn on propagation tracing at the highest level using event 24040, level 10.

12. Debugging information is logged to job queue trace files as propagation takes place. You can check the trace file for errors and for statements indicating that messages have been sent.

Read more »

Oracle Advanced Queue - Technical Concepts

Create a USER with Administrator Role:

CONNECT / AS SYSDBA

CREATE USER aq_admin IDENTIFIED BY aq_admin DEFAULT TABLESPACE users

GRANT connect TO aq_admin;

GRANT create type TO aq_admin;

GRANT aq_administrator_role TO aq_admin;

ALTER USER aq_admin QUOTA UNLIMITED ON users;

Create a USER with USER role:

CREATE USER aq_user IDENTIFIED BY aq_user DEFAULT TABLESPACE users;

GRANT connect TO aq_user;

GRANT aq_user_role TO aq_user;

Define Payload

The format or structure of a message is called the payload. While creating a queue, we need to tell Oracle the Payload structure.

CONNECT aq_admin/aq_admin

CREATE OR REPLACE TYPE event_msg_type AS OBJECT (

  Header_ID NUMBER,

  Line_ID   NUMBER,

  Current_status VARCHAR2(50),

);

/

GRANT EXECUTE ON event_msg_type TO aq_user;

Create Queue Table 

Queues are implemented using a queue table which can hold multiple queues with the same payload type. 

GRANT EXECUTE ON event_msg_type TO aq_user;

EXECUTE DBMS_AQADM.create_queue_table 

( queue_table         =>  'aq_admin.event_queue_tab', 

  queue_payload_type  =>  'aq_admin.event_msg_type'

);

Create Queue

EXECUTE DBMS_AQADM.create_queue

(queue_name   =>  'aq_admin.event_queue',

 queue_table  =>  'aq_admin.event_queue_tab'

);

Start Queue

EXECUTE DBMS_AQADM.start_queue 

(queue_name         => 'aq_admin.event_queue',

 enqueue            => TRUE

);

Grant Privilege to AQ_USER

CONNECT aq_admin/aq_admin

EXECUTE DBMS_AQADM.grant_queue_privilege 

(  privilege     =>     'ALL', 

   queue_name    =>     'aq_admin.event_queue', 

   grantee       =>     'aq_user', 

   grant_option  =>      TRUE

);

Enqueue Message

Messages can be written to the queue using the DBMS_AQ.ENQUEUE procedure.

CONNECT aq_user/aq_user

DECLARE

  l_enqueue_options     DBMS_AQ.enqueue_options_t;

  l_message_properties  DBMS_AQ.message_properties_t;

  l_message_handle      RAW(16);

  l_event_msg           AQ_ADMIN.event_msg_type;

BEGIN

  l_event_msg := AQ_ADMIN.event_msg_type(1,1,'Entered');

  DBMS_AQ.enqueue(queue_name          => 'aq_admin.event_queue',        

                  enqueue_options     => l_enqueue_options,     

                  message_properties  => l_message_properties,   

                  payload             => l_event_msg,             

                  msgid               => l_message_handle);

  COMMIT;

END;

/

Dequeue Message

Messages can be read from the queue using the DBMS_AQ.DEQUEUE procedure.

CONNECT aq_user/aq_user

SET SERVEROUTPUT ON

DECLARE

  l_dequeue_options     DBMS_AQ.dequeue_options_t;

  l_message_properties  DBMS_AQ.message_properties_t;

  l_message_handle      RAW(16);

  l_event_msg           AQ_ADMIN.event_msg_type;

BEGIN

  DBMS_AQ.dequeue(queue_name          => 'aq_admin.event_queue',

                  dequeue_options     => l_dequeue_options,

                  message_properties  => l_message_properties,

                  payload             => l_event_msg,

                  msgid               => l_message_handle);

  DBMS_OUTPUT.put_line ('Event Name  : ' ||l_event_msg.name);

  DBMS_OUTPUT.put_line ('Header ID   : ' ||l_event_msg.Header_id);

  DBMS_OUTPUT.put_line ('Line ID     : ' ||l_event_msg.line_id);

  DBMS_OUTPUT.put_line ('Status     : ' ||l_event_msg.status);

  COMMIT;

END;

/

Read more »

Oracle Advanced Queuing - Understanding

Advanced Queuing (AQ) is a flexible message exchange mechanism so that the web based business applications can communicate with each other. One producer application enqueues one or more messages into one queue. Each message is dequeued and processed by one of the consumers application. A message stays in the queue until a consumer dequeues it or the message expires.

Administration and access privileges for advanced queuing are controled using two roles:

• AQ_ADMINISTRATOR_ROLE - Allows creation and administration of queuing infrastructure.
• AQ_USER_ROLE - Allows access to queues for enqueue and dequeue operations.

Advanced Queuing sends and receives messages in two ways:

Point-to-Point : 

A point-to-point message is aimed at a specific target i.e single-consumer queue. 

Senders and receivers decide on a common queue in which to exchange messages. 

Each message is consumed by only one receiver.  

Publish-Subscribe: 

A publish-subscribe message can be consumed by multiple receivers.

Publish-subscribe messaging has a wide dissemination mode--broadcast--and a more narrowly aimed mode--multicast, also called point-to-multipoint.

Read more »

Bulk Collect - NO_DATA_FOUND Exception Handling

When we use BULK COLLECT, if a query does not fetch any records, it does not throw NO_DATA_FOUND exception.  So, we need to check whether the collection variable has any elements or not. 

Example:

1. SQL> declare
2.  type emp_tab is table of emp%rowtype;
3.  t_emp emp_tab;
4.  begin
5.  select * bulk collect into t_emp from emp;
6.  IF t_emp.count = 0 THEN
7.   dbms_output.put_line(‘Bulk Collect: No Records in the Table’);
8.  ELSE
9.   dbms_output.put_line(t_emp.count)
10.  END IF;
11.  end;
12.  / 

Read more »

PL/SQL Bulk Collect

Oracle Bulk Collect is a method of fetching data. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. The SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine.  Thus, when rows are retrieved using Oracle bulk collect, they are retrieved with only two context switches.  

When data involved is very large, we can use Bulk Collect clause to fetch the data into local PL/SQL variables faster without looping through one record at a time.  We can store the result set into either individual collection variables, if we are fetching certain number of columns or collection records, if we are fetching all the columns of the table.

Example:

SQL> declare
2 type emp_tab is table of emp%rowtype;
3 t_emp emp_tab;
4 begin
5 select * bulk collect into t_emp from emp;
6 dbms_output.put_line(t_emp.count);
7 end;
8 /

Read more »

PL/SQL REF Cursor

What is a REF CURSOR?

1. A Ref Cursor is a PL/SQL data type or cursor variable.
2. A Ref Cursor is a pointer to a result set on the database. 
3. A Ref Cursor can be used to associate multiple Queries at run time dynamically. 
4. A Ref Cursor can be passed as a variable to a procedure or a function

What are the categories of REF CURSOR?
REF CURSOR is of 3 types:

1. Strong Ref Cursor - Ref Cursors which has a return type is classified as Strong Ref Cursor. Example :-

-- Strongly typed REF CURSOR.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR RETURN emp%ROWTYPE;
  c_cursor  t_ref_cursor;

  l_row     emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Strongly typed REF CURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM emp;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;  

  CLOSE c_cursor;
END;
/

2. Weak Ref Cursor -  When a Ref-Cursor is defined without a return type, it is called as a weakly typed dynamic Ref-Cursor. 
Example :

-- Weakly typed REF CURSOR.
DECLARE
  TYPE t_ref_cursor IS REF CURSOR;
  c_cursor  t_ref_cursor;

  l_row     emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('Weakly typed REF CURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM emp;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;  

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

3. System Ref Cursor - SYS_REFCURSOR is predefined REF CURSOR defined in standard package of Oracle. SYS_REFCURSOR is available from Oracle 9i onwards as part of standard package and weak reference created for programmer easiness. 
Example:

-- REF CURSOR using SYS_RECURSOR.
DECLARE
  c_cursor  SYS_REFCURSOR;

  l_row     emp%ROWTYPE;
BEGIN
  DBMS_OUTPUT.put_line('REF CURSOR using SYS_RECURSOR');

  OPEN c_cursor FOR
    SELECT *
    FROM emp;

  LOOP
    FETCH c_cursor
    INTO  l_row;

    EXIT WHEN c_cursor%NOTFOUND;

    DBMS_OUTPUT.put_line(l_row.id || ' : ' || l_row.description);
  END LOOP;

  CLOSE c_cursor;
END;
/

Read more »

Create an Extended Warranty Contract with Monthly Billing From Order Management

In releases prior to 12.2.2, when an Extended Warranty is invoiced through Order Management, the invoice amount is for the entire duration of the Extended Warranty. Hence, Flexible billing schedules are not possible in the prior releases.

As from release 12.2.2, additional billing options have been introduced for Extended Warranties which allow flexibility as to how billing is done.  The options are as follows:

• Retain the existing behavior of generating an invoice for the entire duration from Order Management.
• Generate the invoice for the first installment from Order Management and for the subsequent installments from Service Contracts.
• Generate invoices for all installments from Service Contracts. In this scenario, Order Management does not generate any invoices.

For orders that are billed in multiple installments over the duration of the service contract, the billing schedule can be specified in the Billing Profile, which is a new field on the order line. To support this enhancement, several new fields have been added to the order line. 

The following attributes are managed in Order Management and interfaced to Service Contracts:

Service Billing Option:

A list of values (LOV) field and the values are maintained in Service Contracts.The following values are available:

Full Billing from Order Management:  When a contract (Extended Warranty or Service Agreement or Subscription Agreement) is created from OM; the contract is billed completely from OM. This doesn’t allow users to have multiple billing periods.

Full Billing from Service Contracts:When a contract (Extended Warranty or Service Agreement or Subscription Agreement) is created from OM, the contract is billed completely from OKS.This allow users to have multiple billing periods.

First Period Billing from OM, subsequent from Contracts: First period is billed from OM and the rest from OKS.  Periodic Billing Amounts are calculated and maintained based on the amount billed in OM. This allow users to have multiple billing periods.

Service Billing Profile:

The Service Billing Profiles are templates used to specify
- Simple periodic billing schedules
- Accounting rules
- Invoicing rules.
Service Billing Profile information is specified for the subscription or service item in the Sales Order line.

Service Coverage Template:

 Optional, A list of values (LOV) field and the values are maintained in Service Contracts.

Optionally, select from the list of values that displays all active templates. Service Contracts uses this value for creating the contract. The field is enabled only for Extended Warranty lines. We cannot edit or update the coverage template details in Order Management. If we do not specify a template in Order Management, then Oracle Service Contracts populates the value (taken from the Item Master).  We can update the coverage template on the order line when line status is either 'Entered', 'Booked' or 'Awaiting Fulfillment' and it is yet to be interfaced to Service Contracts.
Ensure to define service coverage templates in Oracle Service Contracts.

Contract Effectivity: 

Service Start Date (e.g. 01-JAN-2014), 

Service End Date (e.g. 31-DEC-2014), 

Service Duration (e.g. 12) , 

Service Period (e.g. Months);
First Period Bill Amount (calculated and displayed);
First Period End Date (calculated and displayed).

Read more »

Categories of Oracle Service Contracts

There are 4 categories of Service Contracts:

1. Warranty
2. Extended Warranty
3. Subscription Agreements
4. Service Agreements  

Warranty - The Service that are generally given away for free when the customer purchases a product.  For the purpose of Oracle Service Contracts, warranties are always free of charge and are created automatically by Oracle Order Management when a product is sold.  

Extended warranty - The Service that are generally sold to the customer at an additional cost at the time their products are purchased.  We can sell the extended warranty contract using Oracle Order Management at the time the product is sold or we can also sell extended warranty contracts separately after the product is sold from Order Management or Oracle Service Contract Module.

These contracts often take advantage of the ability to bill on a recurring basis either monthly, quarterly or annually and can give the customer the ability to pay for their services in advance or in arrears of the period of service. 

Subscription Agreements - The service/products that can also be sold for both tangible and intangible items and that often have monetary benefits involved for customers. Tangible items include magazines, collateral, or any other physical item that can be shipped through Oracle Order Management. Intangible items can be collateral sent via e-mail or permission to access a web site for a set period of time.

Service Agreements - (also referred as Service Contracts) are contracts those are sold to customers to repair, support / maintain product or services that a customer has or sold by the vendor / seller. All the services agreements bind within the boundaries of terms and conditions that is associated with the contract.

Service agreement give the flexibility to bill the customer on a recurring basis like Monthly, Quarterly, Half yearly or annually and gives customer the ability to pay in advance or in arrears. Service agreements can be created manually or created automatically from Order Management.  

Read more »

Compile Oracle forms in 11i and R12

Steps to compile Oracle Forms in 11i and r12:

1. Login to the Application Server as applmgr and run .env file to set the applications environment.
2. Change directory to $AU_TOP/forms/US.
3. Place “.fmb” file in binary mode
4. Execute the below command to generate “.fmx”.

In 11i use the below command:

$ f60gen module=<formname>.fmb userid=apps/<apps_pwd> output_file=/forms/US/<formname>.fmx

In R12 use the below command:

frmcmp_batch userid=apps/<apps_paswd> module=<Form_Name>.fmb output_file=<Form_Name>.fmx module_type=form batch=no compile_all=special

Read more »

How to upgrade/migrate release 11i custom forms to Release 12.

All custom forms that were built and working fine on release 11i are designed and compiled using the Form Builder 6i, while the developer version for R12 is 10G.  

To Upgrade / Migrate:

1.  Download the Forms(.fmb's) and all PLL's(all the PLL from resource folder in AU_TOP) into a Local Machine Folder
2.  Open the custom forms using Forms Developer 10G and connect to DB
3. Compile  and then save them.
4.  Upload the Saved Forms(.fmb's) into the new R12 server(system) in the respective custom paths(paths similar to 11i Server)
5. Compile all the forms to create the .fmx files
6. Open the form and check in R12 instance

If one finds one cannot open them after doing that, make sure to add the Custom_Top entry to the default.env file on the Applications tier.

Read more »

Query - Get Descriptive Flex Fields (DFF) Details

SELECT 
ffv.descriptive_flexfield_name “DFF Name”,
ffv.application_table_name “Table Name”,
ffv.title “Title”,
ap.application_name “Application”,
ffc.descriptive_flex_context_code “Context Code”,
ffc.descriptive_flex_context_name “Context Name”,
ffc.description “Context Desc”,
ffc.enabled_flag “Context Enable Flag”,
att.column_seq_num “Segment Number”,
att.form_left_prompt “Segment Name”,
att.application_column_name “Column”,
fvs.flex_value_set_name “Value Set”,
att.display_flag “Displayed”,
att.enabled_flag “Enabled”,
att.required_flag “Required”
FROM 
apps.fnd_descriptive_flexs_vl ffv,
apps.fnd_descr_flex_contexts_vl ffc,
apps.fnd_descr_flex_col_usage_vl att,
apps.fnd_flex_value_sets fvs,
apps.fnd_application_vl ap
WHERE
    ffv.descriptive_flexfield_name = att.descriptive_flexfield_name
AND ap.application_id=ffv.application_id
AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
AND ffv.application_id = ffc.application_id
AND ffc.descriptive_flex_context_code=att.descriptive_flex_context_code
AND fvs.flex_value_set_id=att.flex_value_set_id
AND ffv.title like ‘%Give Title Name%’
AND ffc.descriptive_flex_context_code like ‘%Context Code Value%’
ORDER BY att.column_seq_num

Read more »

Query - Link AR XLA and GL

SELECT 
       rcta.trx_number transaction_num, 
       rcta.trx_date transaction_date,       
       gjh.posted_date posted_date, 
       gjh.je_source, 
       gjh.je_category,
       gjb.NAME je_batch_name, 
       gjh.NAME journal_name, 
       gjl.je_line_num je_line, 
       gjl.description je_line_descr,
       xal.entered_cr global_cr, 
       xal.entered_dr global_dr,
       xal.currency_code global_cur, 
       ac.customer_name vendor_customer,
       xal.accounting_class_code transaction_type, 
       xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, 
       gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, 
       gjh.period_name fiscal_period,
       gl.NAME ledger_name
  FROM apps.gl_je_headers gjh,
       apps.gl_je_lines gjl,
       apps.gl_import_references gir,
       xla.xla_ae_lines xal,
       xla.xla_ae_headers xah,
       apps.gl_code_combinations glcc,
       xla.xla_transaction_entities xte,
       apps.ra_customer_trx_all rcta,
       apps.gl_ledgers gl,
       apps.gl_balances gb,
       apps.ar_customers ac,
       apps.gl_je_batches gjb
 WHERE 1 = 1
   AND gjh.je_header_id = gjl.je_header_id
   AND gjl.je_header_id = gir.je_header_id
   AND gjh.je_source = 'Receivables'
   AND gjl.je_line_num = gir.je_line_num
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gir.gl_sl_link_table = xal.gl_sl_link_table
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.code_combination_id
   AND xte.entity_id = xah.entity_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xte.ledger_id = gl.ledger_id
   AND xte.application_id = xal.application_id
   AND NVL (xte.source_id_int_1, -99) = rcta.customer_trx_id
   AND gjh.ledger_id = gl.ledger_id
   AND gb.code_combination_id = glcc.code_combination_id
   AND gb.period_name = gjh.period_name
   AND gb.currency_code = gl.currency_code
   AND gjh.je_batch_id = gjb.je_batch_id
   AND rcta.bill_to_customer_id = ac.customer_id(+)
   AND rcta.trx_number = :trx_number
  

Read more »

Query - Check if ITS has successfully interfaced the delivery detail to OM and INV

SELECT 

delivery_detail_id, 

released_status, 

oe_interfaced_flag, 

inv_interfaced_flag
FROM wsh_delivery_details
WHERE source_code = 'OE' 

AND source_line_id = :line_id;

Read more »