Inventory On Hand Balance Conversion



                                    Inventory On Hand Balance Conversion

 Create Procedure  for  Account issue On hand balances

CREATE OR REPLACE PROCEDURE XXXINV_ACCOUNT_ISSUE_OHB_P (org_code VARCHAR2)  AS

   v_transaction_uom  MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
   v_org_id           NUMBER;

   CURSOR cur_account_issue(org_id NUMBER) IS
      SELECT inventory_item_id
                    ,organization_id
                    ,locator_id
                  ,subinventory_code
                  ,sum(transaction_quantity) tot
                  ,revision
    FROM   MTL_ONHAND_QUANTITIES 
   WHERE  transaction_quantity>0
   AND       organization_id =org_id
   GROUP BY inventory_item_id            
               ,organization_id
               ,locator_id
               ,subinventory_code
               ,revision;

      BEGIN

          BEGIN
              SELECT organization_id
              INTO   v_org_id
              FROM   MTL_PARAMEXXXS
              WHERE  organization_code=org_code;

          EXCEPTION
                WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_ACCOUNT_ISSUE_OHB_P - organization_id
                                        '||SQLERRM);
          END;                  
          
       FOR v_cur_account_issue IN cur_account_issue(v_org_id)
         LOOP

          BEGIN
              SELECT primary_uom_code
              INTO   v_transaction_uom
              FROM   MTL_SYSTEM_ITEMS msi
              WHERE  msi.inventory_item_id =v_cur_account_issue.inventory_item_id 
              AND    msi.organization_id    =v_cur_account_issue.organization_id;
            
          EXCEPTION
                WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_ACCOUNT_ISSUE_OHB_P - primary_uom_code
                                        '||SQLERRM);
          END;
              
          INSERT INTO XXXINV_ACCOUNT_ISSUE_OHB (organization_id
                                                ,transaction_quantity
                                                ,transaction_uom
                                                ,inventory_item_id
                                                ,subinventory_code
                                                ,revision
                                                ,created_by
                                                ,last_updated_by
                                                ,last_update_date
                                                ,creation_date
                                                ,locator_id
                                                                                                  )
                                                                        VALUES          (
                                                v_cur_account_issue.organization_id
                                                ,v_cur_account_issue.tot  
                                                ,v_transaction_uom
                                                ,v_cur_account_issue.inventory_item_id
                                                ,v_cur_account_issue.subinventory_code
                                                ,v_cur_account_issue.revision
                                                ,38259
                                                ,38259
                                                ,SYSDATE
                                                ,SYSDATE
                                                ,v_cur_account_issue.locator_id
                                                                                                 );
                                                         
         END LOOP;
       COMMIT;
                 
END XXXINV_ACCOUNT_ISSUE_OHB_P;

 Process 2: Create Procedure  for  Unreserve the item quantity against the sale order

CREATE OR REPLACE PROCEDURE XXXINV_DEL_RES_P (org_code VARCHAR2) IS

v_mtl_reservation_record Inv_Reservation_Global.mtl_reservation_rec_type;
    v_serial_number_tbl_type Inv_Reservation_Global.serial_number_tbl_type;
    v_return_status           VARCHAR2(30);
    v_msg_data                  VARCHAR2(256);
    v_msg_count                NUMBER;
    v_message                       VARCHAR2(250);
    v_msg_index_out        NUMBER;
    v_count                            NUMBER;
    v_res_flag                        VARCHAR2(2);
    v_INTERFACE_id        NUMBER;
    v_org_id                           NUMBER;
    v_user              FND_USER.user_id%TYPE;
    v_code_comb_id         GL_CODE_COMBINATIONS.code_combination_id%TYPE;
    v_transaction_uom   MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
     
   CURSOR cur_del_res(org_id NUMBER) IS
      SELECT reservation_id
             ,organization_id
             ,inventory_item_id
             ,demand_source_type_id      
             ,demand_source_header_id
             ,demand_source_line_id 
             ,primary_uom_code           
             ,reservation_uom_code        
             ,reservation_quantity       
             ,primary_reservation_quantity
             ,supply_source_type_id
      FROM   MTL_RESERVATIONS
      WHERE  organization_id =org_id;
               
   CURSOR cur_account_stag(org_id NUMBER) IS
      SELECT *
      FROM   XXXINV_ACCOUNT_ISSUE_OHB
      WHERE  transaction_quantity>0
      AND    organization_id =org_id;
                      
      BEGIN    
          BEGIN
              SELECT organization_id
              INTO   v_org_id
              FROM   MTL_PARAMEXXXS
              WHERE  organization_code=org_code;
                       
          EXCEPTION
                WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_DELETE_RES_P - organization_id'||SQLERRM);
          END;

   INSERT INTO XXXINV_MTL_RESERVATIONS_BK (SELECT MTL_RESERVATIONS.*
                                                                                                           FROM                                    MTL_RESERVATIONS
                                                                                                           WHERE                                 organization_id=v_org_id);
                                                                                 
FOR v_cur_del_res IN cur_del_res(v_org_id)
 LOOP

      v_mtl_reservation_record.reservation_id                           := v_cur_del_res.reservation_id;
      v_mtl_reservation_record.organization_id                        := v_cur_del_res.organization_id;         
      v_mtl_reservation_record.inventory_item_id                    := v_cur_del_res.inventory_item_id;
      v_mtl_reservation_record.demand_source_type_id         := v_cur_del_res.demand_source_type_id;
      v_mtl_reservation_record.demand_source_header_id      := v_cur_del_res.demand_source_header_id;
      v_mtl_reservation_record.demand_source_line_id           := v_cur_del_res.demand_source_line_id; 
      v_mtl_reservation_record.primary_uom_code                  := v_cur_del_res.primary_uom_code;   
      v_mtl_reservation_record.reservation_uom_code            := v_cur_del_res.reservation_uom_code;      
      v_mtl_reservation_record.reservation_quantity                    := v_cur_del_res.reservation_quantity;    
      v_mtl_reservation_record.primary_reservation_quantity:= v_cur_del_res.primary_reservation_quantity;
      v_mtl_reservation_record.supply_source_type_id            := v_cur_del_res.supply_source_type_id;
 
Inv_Reservation_Pub.delete_reservation (p_api_version_number => 1.0
                                                                                ,x_return_status                 => v_return_status
                                                                                 ,x_msg_count                     => v_msg_count
                                                                                ,x_msg_data                        => v_msg_data
                                                                                ,p_rsv_rec            => v_mtl_reservation_record
                                                                                 ,p_serial_number              => v_serial_number_tbl_type
                                                                                 );
                                       
      Fnd_Msg_Pub.get(p_msg_index         => 1
                                          ,p_encoded             => 'F'
                                          ,p_data                     => v_message
                                          ,p_msg_index_out => v_msg_index_out
                                         );
                     
         DBMS_OUTPUT.PUT_LINE('AfXXX Deletetion of Reservation  p_msg_index_out'||' '||
                               v_msg_index_out||'Message'||v_message);  
   
 END LOOP;
COMMIT;

     FOR v_cur_account_stag IN cur_account_stag(v_org_id)
       LOOP
          BEGIN
              SELECT  MTL_MAXXXIAL_TRANSACTIONS_S.NEXTVAL
              INTO    v_INTERFACE_id
              FROM    DUAL;
                                
          EXCEPTION
                WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_DELETE_RES_P - sequence '||SQLERRM);
          END;
                   
          BEGIN
              SELECT code_combination_id
              INTO   v_code_comb_id
              FROM   GL_CODE_COMBINATIONS
              WHERE  segment1='001'
              AND    segment2 ='0787796'
              AND    segment3 ='134668'
              AND    segment4 ='130';
              
          EXCEPTION
                WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_DELETE_RES_P - code combination id '||SQLERRM);
          END;
                                                                                                                      
        IF NVL(v_cur_account_stag.record_status,'N') !='Y' THEN
          BEGIN
             INSERT INTO MTL_TRANSACTIONS_INTERFACE (source_code
                                                     ,source_line_id
                                                     ,source_header_id
                                                     ,transaction_INTERFACE_id
                                                     ,process_flag
                                                     ,validation_required
                                                     ,transaction_mode
                                                     ,lock_flag
                                                     ,organization_id
                                                     ,transaction_quantity
                                                     ,transaction_uom
                                                     ,transaction_date
                                                     ,transaction_type_id
                                                     ,transaction_source_id
                                                     ,flow_schedule
                                                     ,scheduled_flag
                                                     ,inventory_item_id
                                                     ,last_update_date
                                                     ,last_updated_by
                                                     ,creation_date
                                                     ,created_by
                                                     ,subinventory_code
                                                     ,distribution_account_id
                                                     ,revision
                                                     ,locator_id
                                                    )
                                                                                 VALUES                     (
                                                     'Conversion'
                                                     ,v_INTERFACE_id
                                                     ,v_INTERFACE_id
                                                     ,v_INTERFACE_id   
                                                     ,1
                                                     ,1
                                                     ,3
                                                     ,2
                                                     ,v_cur_account_stag.organization_id
                                                     ,-v_cur_account_stag.transaction_quantity 
                                                     ,v_cur_account_stag.transaction_uom
                                                     ,SYSDATE
                                                     ,1                                 
                                                     ,v_code_comb_id
                                                     ,'Y'
                                                     ,2
                                                     ,v_cur_account_stag.inventory_item_id
                                                     ,SYSDATE
                                                     ,38259
                                                     , SYSDATE
                                                     ,38259
                                                     ,v_cur_account_stag.subinventory_code
                                                     ,v_code_comb_id
                                                     ,v_cur_account_stag.revision
                                                     ,v_cur_account_stag.locator_id
                                                    );

               
             UPDATE XXXINV_ACCOUNT_ISSUE_OHB
             SET    record_status    ='Y'
             WHERE  organization_id  =v_cur_account_stag.organization_id
             AND    inventory_item_id=v_cur_account_stag.inventory_item_id;
               
          EXCEPTION
             WHEN OTHERS THEN
             UPDATE XXXINV_ACCOUNT_ISSUE_OHB
             SET    record_status ='E',error_msg ='Procedure XXXINV_DELETE_RES_P - Error in fetching the data'                                                     WHERE organization_id  =v_cur_account_stag.organization_id
             AND    inventory_item_id=v_cur_account_stag.inventory_item_id;

         END;  
      END IF;          
   END LOOP;
 COMMIT;
           
          EXCEPTION
               WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE ('Data insertion is Failed '||SQLERRM);

END XXXINV_DELETE_RES_P;



Process 3: Create Procedure  for Account receipt   

CREATE OR REPLACE PROCEDURE XXXINV_ACCOUNT_RECEIPT_OHB_P (org_code VARCHAR2)  AS

  CURSOR cur_account_receipt(org_id NUMBER) IS
     SELECT *
     FROM   XXXINV_ACCOUNT_ISSUE_OHB
     WHERE  organization_id = org_id;
 
  v_user                                  FND_USER.user_id%TYPE;
  v_transaction_uom        MTL_SYSTEM_ITEMS.primary_uom_code%TYPE;
  v_code_comb_id              GL_CODE_COMBINATIONS.code_combination_id%TYPE;
  v_org_id             NUMBER;
  v_INTERFACE_id          NUMBER;

     BEGIN
          BEGIN
              SELECT organization_id
              INTO   v_org_id
              FROM   MTL_PARAMEXXXS
              WHERE  organization_code=org_code;
                                      
          EXCEPTION
               WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P - organization_id
                                        '||SQLERRM);
            END;                   
                 
FOR v_cur_account_receipt IN cur_account_receipt(v_org_id)
 LOOP

          BEGIN
              SELECT  primary_uom_code
              INTO    v_transaction_uom
              FROM    MTL_SYSTEM_ITEMS msi
              WHERE   v_cur_account_receipt.inventory_item_id = msi.inventory_item_id
              AND     v_cur_account_receipt.organization_id   = msi.organization_id;
                 
          EXCEPTION
                WHEN OTHERS THEN
                   DBMS_OUTPUT.PUT_LINE('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P -   primary_uom_code'||SQLERRM);
          END;      

          BEGIN
              SELECT MTL_MAXXXIAL_TRANSACTIONS_S.NEXTVAL
              INTO   v_INTERFACE_id
              FROM   DUAL;
                    
          EXCEPTION
               WHEN OTHERS THEN
                          DBMS_OUTPUT.PUT_LINE('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P - sequence '||SQLERRM);
          END;     

          BEGIN
              SELECT code_combination_id
              INTO   v_code_comb_id
              FROM   GL_CODE_COMBINATIONS
              WHERE  segment1='206' 
              AND    segment2 ='0787796'
              AND    segment3 ='042709'
              AND    segment4 ='040';
        
          EXCEPTION
                WHEN OTHERS THEN
                           DBMS_OUTPUT.PUT_LINE('Procedure XXXINV_ACCOUNT_RECEIPT_OHB_P-code combination id'||SQLERRM);
          END;        

      INSERT INTO MTL_TRANSACTIONS_INTERFACE    (source_code
                                                                             ,source_line_id
                                                                                      ,source_header_id
                                                                                      ,transaction_INTERFACE_id
                                                                                      ,process_flag
                                                                                    ,validation_required
                                                                                      ,transaction_mode
                                                                                      ,lock_flag
                                                                                      ,organization_id
                                                                                      ,transaction_quantity
                                                                                      ,transaction_uom
                                                                                      ,transaction_date
                                                                                      ,transaction_type_id
                                                                                      ,transaction_source_id
                                                                                      ,flow_schedule
                                                                                      ,scheduled_flag
                                                                                      ,inventory_item_id
                                                                                      ,last_update_date
                                                                                      ,last_updated_by
                                                                                      ,creation_date
                                                                                      ,created_by
                                                                                      ,subinventory_code
                                                                                      ,Distribution_account_id
                                                                                      ,revision
                                                                                      ,locator_id
                                                                                     )
                                                                         VALUES                       (
                                                                               'Conversion'
                                                                                      ,v_INTERFACE_id
                                                                                      ,v_INTERFACE_id
                                                                                      ,v_INTERFACE_id 
                                                                                      ,1
                                                                                      ,1
                                                                                      ,3
                                                                                      ,2
                                                                                      ,v_cur_account_receipt.organization_id
                                                                                      ,abs(v_cur_account_receipt.transaction_quantity)
                                                                                      ,v_transaction_uom
                                                                                      ,SYSDATE
                                                                                      ,40
                                                                                      ,v_code_comb_id
                                                                                      ,'Y'
                                                                                      ,2
                                                                                      ,v_cur_account_receipt.inventory_item_id
                                                                                      , SYSDATE
                                                                                      ,38259
                                                                                      , SYSDATE
                                                                                      ,38259
                                                                                      ,v_cur_account_receipt.subinventory_code
                                                                                      ,v_code_comb_id
                                                                                      ,v_cur_account_receipt.revision
                                                                                      ,v_cur_account_receipt.locator_id
                                                                                     );
   END LOOP;
   COMMIT;
        
          EXCEPTION
               WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE ('Data insertion is Failed '||SQLERRM);
    
 END XXXINV_ACCOUNT_RECEIPT_OHB_P;


Process 4: Create Procedure  for Reserve the item quantity against the sale order

CREATE OR REPLACE PROCEDURE XXXINV_CREATE_RES_P (org_code VARCHAR2) AS

v_org_id                   NUMBER;
v_batch_number     NUMBER := 1;

   CURSOR cur_create_res(org_id NUMBER) IS
          SELECT *
                  FROM   XXXINV_MTL_RESERVATIONS_BK
          WHERE  organization_id =org_id;
       
   BEGIN
          BEGIN
              SELECT organization_id
              INTO   v_org_id
              FROM   MTL_PARAMEXXXS
              WHERE  organization_code=org_code;
                                      
          EXCEPTION
               WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE ('Procedure XXXINV_CREATE_RES_P - organization_id
                                                '||SQLERRM);
          END;                  
  
FOR v_cur_create_res IN cur_create_res(v_org_id)
 LOOP
          INSERT INTO MTL_RESERVATIONS_INTERFACE (reservation_INTERFACE_id
                                                  ,reservation_batch_id
                                                  ,requirement_date
                                                  ,organization_id
                                                  ,inventory_item_id
                                                  ,primary_uom_code
                                                  ,reservation_uom_code
                                                  ,reservation_quantity
                                                  ,primary_reservation_quantity
                                                  ,supply_source_type_id
                                                  ,row_status_code
                                                  ,lock_flag
                                                  ,reservation_action_code
                                                  ,transaction_mode
                                                  ,validation_flag
                                                  ,demand_source_type_id
                                                  ,last_update_date
                                                  ,last_updated_by
                                                  ,created_by
                                                  ,creation_date
                                                  ,demand_source_header_id
                                                  ,demand_source_line_id
                                                  ,subinventory_code
                                                  ,demand_source_name
                                                  ,supply_source_name
                                                  ,supply_source_header_id
                                                  ,supply_source_line_id
                                                 )
                                                                  VALUES                         (
                                                   MTL_RESERVATIONS_INTERFACE_S.NEXTVAL
                                                    , v_batch_number
                                                    ,v_cur_create_res.requirement_date    
                                                    ,v_cur_create_res.organization_id     
                                                    ,v_cur_create_res.inventory_item_id   
                                                    ,v_cur_create_res.primary_uom_code    
                                                    ,v_cur_create_res.reservation_uom_code
                                                    ,v_cur_create_res.reservation_quantity
                                                    ,v_cur_create_res.primary_reservation_quantity     
                                                    ,v_cur_create_res.supply_source_type_id            
                                                    ,1                                        
                                                    ,2                                        
                                                    ,1                                        
                                                    ,3                                        
                                                    ,1                                        
                                                    ,v_cur_create_res.demand_source_type_id            
                                                    ,SYSDATE
                                                    ,v_cur_create_res.last_updated_by
                                                    ,v_cur_create_res.created_by
                                                    ,SYSDATE
                                                    ,v_cur_create_res.demand_source_header_id      
                                                    ,v_cur_create_res.demand_source_line_id        
                                                    ,v_cur_create_res.subinventory_code
                                                  ,v_cur_create_res.demand_source_name
                                                  ,v_cur_create_res.supply_source_name
                                                  ,v_cur_create_res.supply_source_header_id
                                                  ,v_cur_create_res.supply_source_line_id
                                                 );
v_batch_number := v_batch_number + 1;

   END LOOP;
 COMMIT;
     
          EXCEPTION
               WHEN OTHERS THEN
                  DBMS_OUTPUT.PUT_LINE ('Data insertion is Failed '||SQLERRM);
                               
END XXXINV_CREATE_RES_P;


No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...