Sunday, October 16, 2016

Script to Create Oracle Purchase Order Receipt from Backend

DECLARE
    l_user_id            NUMBER :=Fnd_Profile.VALUE('USER_ID');--profile value
    l_resp_id            NUMBER :=fnd_global.resp_id;
    l_appl_id            NUMBER :=fnd_global.resp_appl_id;
    l_org_id             NUMBER :=fnd_global.org_id;
    l_login_id           NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');--profile value
    L_RCV_HEADER_ID      NUMBER;
    L_RCV_GROUP_ID       NUMBER;
    L_RCV_TRANSACTION_ID NUMBER;
    l_chr_lot_number    VARCHAR2 (50);

BEGIN
   fnd_client_info.set_org_context(204);--initializing org_id
   fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id,l_login_id);
   SELECT RCV_HEADERS_INTERFACE_S.NEXTVAL
      INTO L_RCV_HEADER_ID
   FROM DUAL;
   SELECT RCV_INTERFACE_GROUPS_S.NEXTVAL
      INTO L_RCV_GROUP_ID
   FROM DUAL;  
   SELECT rcv_transactions_interface_s.NEXTVAL
   INTO L_RCV_TRANSACTION_ID
   FROM DUAL;     
   DBMS_OUTPUT.PUT_LINE('BEFORE CALLING');
  
   INSERT INTO rcv_headers_interface
            (header_interface_id,
             GROUP_ID,
             processing_status_code,
             receipt_source_code,
             transaction_type,
             auto_transact_code,
             test_flag,
             last_update_date,
             last_updated_by,
             last_update_login,
             creation_date,
             created_by,
             expected_receipt_date,
             comments,
             validation_flag,
             transaction_date,
         
             vendor_id,
             vendor_site_id)
           
    VALUES(L_RCV_HEADER_ID
           ,L_RCV_GROUP_ID 
           ,'PENDING'      
           ,'VENDOR'       
           ,'RECEIVE'      
           ,null           
           ,null                  
           ,SYSDATE        
           ,l_user_id      
           ,l_login_id     
           ,SYSDATE        
           ,l_user_id      
           ,null,
           ,'test'         
           ,'Y'            
           ,sysdate        
           ,600
           ,1414);
                   
    INSERT INTO RCV_TRANSACTIONS_INTERFACE
         (
             interface_transaction_id,
             header_interface_id,
             processing_status_code,
             transaction_date,
             GROUP_ID,
             last_update_date,
             last_updated_by,
             creation_date,
             created_by,
             last_update_login,
             transaction_type,
                      
             processing_mode_code,
             transaction_status_code,
             quantity,
        
             to_organization_id,
             source_document_code,
             po_header_id,
             po_line_id,
             po_line_location_id,
             po_distribution_id,
             comments)
             
      VALUES( L_RCV_TRANSACTION_ID               
              ,L_RCV_HEADER_ID   
              ,'PENDING'         
              ,SYSDATE           
              ,L_RCV_GROUP_ID    
              ,sysdate           
              ,l_user_id         
              ,SYSDATE            
              ,l_user_id         
              ,l_login_id        
              ,'RECEIVE'         
              ,'BATCH'           
              ,'PENDING'         
              ,10               
              ,204              
              ,'PO'             
              ,110350           
              ,173279           
              ,264405           
              ,270871           
              ,'TEST'         
         );
     
   COMMIT;
   DBMS_OUTPUT.PUT_LINE('**** EXECUTED SUCCESSFULLY *****');
  
EXCEPTION
  WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE('ERROE');
   DBMS_OUTPUT.PUT_LINE('ERROR'||SQLERRM);      

END;

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect