Sunday 19 June 2016

Get On Hand Quantities using API in Oracle Apps

This script can be used to get the below quantities.
1. On-hand Quantity
2. Available to Reserve
3. Quantity Reserved
4. Quantity Suggested
5. Available to Transact
6. Available to Reserve

You can also get the On-hand quantities from the table mtl_onhand_quantities 

Script :

==========================================================
DECLARE
--  
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   v_item_id               NUMBER;
   v_org_id                NUMBER;
   v_qoh                   NUMBER;
   v_rqoh                  NUMBER;
   v_atr                   NUMBER;
   v_att                   NUMBER;
   v_qr                    NUMBER;
   v_qs                    NUMBER;
   v_lot_control_code      BOOLEAN;
   v_serial_control_code   BOOLEAN;
--
 BEGIN
--
    -- Set the variable values
    v_item_id := '7867';
    v_org_id := 122;
    v_qoh := NULL;
    v_rqoh := NULL;
    v_atr := NULL;
    v_lot_control_code := FALSE;
    v_serial_control_code := FALSE;
--
    -- Set the org context
    fnd_client_info.set_org_context (26);
--
    -- Call API
    inv_quantity_tree_pub.query_quantities
    (p_api_version_number       => 1.0,
     p_init_msg_lst             => 'F',
     x_return_status            => x_return_status,
     x_msg_count                => x_msg_count,
     x_msg_data                 => x_msg_data,
     p_organization_id          => v_org_id,
     p_inventory_item_id        => v_item_id,
     p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode,     
     p_is_revision_control      => FALSE,
     p_is_lot_control           => v_lot_control_code,     
     p_is_serial_control        => v_serial_control_code,
     p_revision                 => NULL,        
     p_lot_number               => NULL,           
     p_lot_expiration_date      => SYSDATE,
     p_subinventory_code        => NULL,    
     p_locator_id               => NULL,     
     p_onhand_source            => 3,
     x_qoh                      => v_qoh,      
     x_rqoh                     => v_rqoh,           
     x_qr                       => v_qr,
     x_qs                       => v_qs,
     x_att                      => v_att,  
     x_atr                      => v_atr    
    );
--

   DBMS_OUTPUT.put_line ('On-Hand Quantity is: ' || v_qoh);
   DBMS_OUTPUT.put_line ('Available to reserve is: ' || v_atr);
   DBMS_OUTPUT.put_line ('Quantity Reserved is: ' || v_qr);
   DBMS_OUTPUT.put_line ('Quantity Suggested is: ' || v_qs);
   DBMS_OUTPUT.put_line ('Available to Transact is: ' || v_att);
   DBMS_OUTPUT.put_line ('Available to Reserve is: ' || v_atr);
--
 EXCEPTION
    WHEN OTHERS
    THEN
       DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
 END;
========================================================================================

Thursday 2 June 2016

Oracle BULK COLLECT with example

Oracle BULK COLLECT technique enables the PL/SQL engine to collect many rows at once and place them in a collection of array.
A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes.
Each element has a unique subscript that determines its position in the collection.

BULK COLLECT improves performance of queries that retrieve more than one row.
BULK COLLECT also leave it to the optimizer to speed up "read only" cursor FOR loops.
Use the LIMIT clause to avoid excessive PGA memory consumption.

 e.g. 
SELECT * BULK COLLECT INTO collection(s) FROM table;
FETCH cur BULK COLLECT INTO collection(s);
EXECUTE IMMEDIATE query BULK COLLECT INTO collection(s);

Example :
==========================================================
CREATE OR REPLACE PROCEDURE XXBULKCOLLECTTEST
IS
--
--Declare a nested table of records to hold the queried data.
TYPE EMP_COL_REC IS RECORD
    (
    EMPNO_R EMP.EMPNO%TYPE,
    ENAME_R EMP.ENAME%TYPE,
    JOB_R EMP.JOB%TYPE,
    MGR_R EMP.MGR%TYPE,
    HIREDATE_R EMP.HIREDATE%TYPE,
    COMM_R EMP.COMM%TYPE,
    DEPTNO_R EMP.DEPTNO%TYPE,
    SAL_R EMP.SAL%TYPE
    );
--  
TYPE EMP_INFO IS TABLE OF EMP_COL_REC;
P_EMP_INFO EMP_INFO;
--
BEGIN
--  
    SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,COMM,DEPTNO, SAL
    BULK COLLECT INTO P_EMP_INFO
    FROM EMP
    WHERE DEPTNO=10;
--
  FOR I IN P_EMP_INFO.FIRST .. P_EMP_INFO.LAST
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPNO '||P_EMP_INFO(I).EMPNO_R||'  '
                         ||'SAL '||P_EMP_INFO(I).SAL_R||'  '
                         ||'ENAME '||P_EMP_INFO(I).ENAME_R||'  '
                         ||'JOB '||P_EMP_INFO(I).JOB_R||'  '
                         ||'MGR '||P_EMP_INFO(I).MGR_R||'  '
                         ||'HIREDATE '||P_EMP_INFO(I).HIREDATE_R||'  ');
  END LOOP;
--
END;
=================================================================
BULK COLLECT WITH LIMIT

SAMPLE SCRIPT:

CREATE OR REPLACE PROCEDURE XXBULKCOLLECTLIMIT
IS
--
CURSOR EMP_CUR
IS
SELECT *  FROM EMP;
--  
TYPE EMP_CUR_TBL IS TABLE OF EMP_CUR%ROWTYPE
INDEX BY PLS_INTEGER;
--
EMP_TBL EMP_CUR_TBL;
--
limit_in PLS_INTEGER:=5;
--
BEGIN
--  
OPEN EMP_CUR;
LOOP
  FETCH EMP_CUR
  BULK COLLECT INTO EMP_TBL LIMIT limit_in;
--
  FOR I IN 1 .. EMP_TBL.COUNT
  LOOP
    DBMS_OUTPUT.PUT_LINE('EMPNO '||EMP_TBL(I).EMPNO||'  '
                         ||'SAL '||EMP_TBL(I).SAL||'  '
                         ||'ENAME '||EMP_TBL(I).ENAME||'  '
                         ||'JOB '||EMP_TBL(I).JOB||'  '
                         ||'MGR '||EMP_TBL(I).MGR||'  '
                         ||'HIREDATE '||EMP_TBL(I).HIREDATE||'  ');
  END LOOP;
--
  EXIT WHEN EMP_TBL.COUNT <= limit_in;
  END LOOP;
  CLOSE EMP_CUR;
--

END;

How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>

 How to resolve issue for BIP RTF template XML tags showing value as <?ref: 0000xx?>  Sometime these xml data tags automatically chang...