Requisition import and PO Aoutocreation using API

CREATE OR REPLACE PACKAGE BODY APPS.xxspanvi_req_po_load
IS
PROCEDURE MAIN (errbuf  OUT varchar2,
                retcode OUT number,
                p_batch_id  number)
 IS
CURSOR c_requistion_rec IS
   SELECT *
   FROM
   xxspanv_po_req_preinteface
   WHERE batch_id=p_batch_id
   and batch_id not in (select substr(attribute14,12) from po_requisition_lines_all);
 
   l_error_flag  varchar2 (1);
   l_error_msg   varchar2 (1000);
   l_record_id   number   ;    
   l_item_id     number   ;
   l_emp_id      number   ;
   l_location_id number   ;
   l_organization_id number;
   l_need_by_date date;
   l_org_id      number;
   l_vendor_id   number;
   l_site_id     number;
   l_requestor_id number;
   l_chanrge_account_id number;
   --l_request_stat varchar2 (10);
 BEGIN
   
    BEGIN
   
      SELECT NVL(MAX(record_id),0)
      INTO l_record_id
      FROM  xxspanv_po_req_preinteface
      WHERE batch_id=p_batch_id;
     
      UPDATE  xxspanv_po_req_preinteface
      SET record_id=l_record_id+ROWNUM;
     
    COMMIT;      
   
    END;
   
     fnd_file.put_line(fnd_file.log,'Validation started for stageging table');
   
   FOR v_requistion_rec IN c_requistion_rec LOOP
       ----initializtion local varibale to null-----------
       l_error_flag  := null;
       l_error_msg   := null;
       l_item_id     :=null ;
       l_emp_id      :=null;
       l_location_id :=null;
       l_organization_id :=null;
       l_need_by_date :=null;
       l_org_id      :=null;
       l_vendor_id   :=null;
       l_site_id     :=null;
       l_requestor_id :=null;
       l_chanrge_account_id :=null;
   
    -----------VALIDATION STARTS HERE------------------
       
     IF v_requistion_rec.INTERFACE_SOURCE_CODE IS NULL THEN
      l_error_flag := 'E';
      l_error_msg  := 'INTERFACE_SOURCE_CODE Can not be Null';
     END IF;  
   
     IF v_requistion_rec.requisition_type IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'REQUISITION_TYPE Can not be Null';
     END IF;  
   
     IF v_requistion_rec.destination_type_code IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'DESTINATION_TYPE_CODE Can not be Null';      
     END IF;
   
     IF v_requistion_rec.item_name IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'ITEM_NAME Can not be Null';
   
     ELSE
     
       BEGIN
           SELECT DISTINCT inventory_item_id
           INTO l_item_id
           FROM mtl_system_items_b
           WHERE UPPER(segment1)=UPPER(v_requistion_rec.item_name);
           
       EXCEPTION WHEN others THEN
          -- l_item_id:=NULL
           l_error_flag := 'E';
           l_error_msg  := l_error_msg||'error occure while retreiving ITEM id'||SQLERRM;
       END;
   
     END IF;
   
     IF v_requistion_rec.quantity IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'QUANTITY Can not be Null';
     END IF;
   
     IF  v_requistion_rec.preparer_full_name IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'PREPARER_FULL_NAME Can not be Null';
   
     ELSE
         BEGIN
           
          SELECT DISTINCT per.person_id
            INTO l_emp_id
          FROM per_all_people_f per,
               per_all_assignments_f paaf  
          WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.preparer_full_name )
          AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
          AND per.person_id=paaf.person_id;
       
         EXCEPTION WHEN others THEN
           --l_emp_id:=NULL
           l_error_flag := 'E';
           l_error_msg  := l_error_msg||'Error occure while retreiving emp_id'||SQLERRM;
         END;
               
     END IF;
   
     IF v_requistion_rec.uom_code IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'PREPARER_FULL_NAME Can not be Null';    
     END IF;
   
     IF  v_requistion_rec.destination_organization IS NULL THEN
       l_error_flag := 'E';
       l_error_msg  := l_error_msg||'DESTINATION_ORGANIZATION Can not be Null';
   
     ELSE
        BEGIN
       
         SELECT organization_id
          INTO l_organization_id
          FROM ORG_ORGANIZATION_DEFINITIONS OOD
          WHERE UPPER(ORGANIZATION_NAME)=UPPER(v_requistion_rec.DESTINATION_ORGANIZATION);
       
        EXCEPTION WHEN OTHERS THEN
        --l_organization_id:=NULL;
        l_error_flag := 'E';
        l_error_msg  := l_error_msg||'Eroor occure while retreiving inventory org_id'||SQLERRM;              
        END;
       
     END IF;
   
     IF v_requistion_rec.deliver_to_location IS NULL THEN
        l_error_flag := 'E';
        l_error_msg  := l_error_msg||'DELIVER_TO_LOCATION can not be null';
     ELSE
        BEGIN
         SELECT  location_id
         INTO l_location_id
         FROM HR_LOCATIONS
         WHERE UPPER(location_code)=UPPER(v_requistion_rec.deliver_to_location);
     
         EXCEPTION WHEN OTHERS THEN
         --l_location_id:=NULL;
         l_error_flag := 'E';
         l_error_msg  := l_error_msg||'Error occure while retreiving the deliver_to_location_id'||SQLERRM;      
        END ;  
     
     END IF;
   
     IF v_requistion_rec.deliver_to_requestor IS NULL THEN
     l_error_flag := 'E';
     l_error_msg  := l_error_msg||'DELIVER_TO_REQUESTOR can not be null';
   
     ELSE
         BEGIN
           
          SELECT per.person_id
            INTO l_requestor_id
          FROM per_all_people_f per,
               per_all_assignments_f paaf
          WHERE UPPER(per.full_name)=UPPER(v_requistion_rec.deliver_to_requestor)
          AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
          AND per.person_id=paaf.person_id;
       
         EXCEPTION WHEN others THEN
           --l_emp_id:=NULL
           l_error_flag := 'E';
           l_error_msg  := l_error_msg||'Error occure while retreiving requestor_id'||SQLERRM;
         END;
   
     END IF;
   
     IF v_requistion_rec.charge_account IS NULL THEN
      l_error_flag := 'E';
      l_error_msg  := l_error_msg||'CHARGE_ACCOUNT can not be null';
     ELSE
        BEGIN
       
         SELECT DISTINCT code_combination_id
         INTO
         l_chanrge_account_id
         FROM gl_code_combinations_kfv a,
              GL_SETS_OF_BOOKS gl
         WHERE concatenated_segments=v_requistion_rec.charge_account
         AND   gl.chart_of_accounts_id=a.chart_of_accounts_id
         AND   gl.set_of_books_id=fnd_profile.value('gl_set_of_bks_id')  ;    
        EXCEPTION WHEN OTHERS THEN
          --l_chanrge_account_id:=NULL;
          l_error_flag := 'E';
          l_error_msg  := l_error_msg||'Error occure while retrieving the charge_account_id'||SQLERRM;        
        END;      
     END IF;
   
     IF  v_requistion_rec.need_by_date IS NULL THEN
     l_need_by_date:= SYSDATE+1;
     ELSE l_need_by_date:=v_requistion_rec.need_by_date;
     END IF;
   
     IF v_requistion_rec.ou_name IS NULL THEN
   
     l_error_flag := 'E';
     l_error_msg  := l_error_msg||'OU_NAME can not be null';
     ELSE
   
       BEGIN
       
        SELECT organization_id
        INTO l_org_id
        FROM hr_operating_units
        WHERE  UPPER(NAME)=UPPER(v_requistion_rec.ou_name);
       EXCEPTION WHEN OTHERS THEN
          --l_org_id:=NULL;
          l_error_flag := 'E';
          l_error_msg  := l_error_msg||'Error occure while retrieving the org_id'||SQLERRM;  
       END;    
     END IF;
   
     IF v_requistion_rec.unit_price IS NULL THEN
      l_error_flag := 'E';
      l_error_msg  := l_error_msg||'UNIT_PRICE can not be null';
     END IF;
   
     IF v_requistion_rec.suggested_vendor_name IS NULL THEN
      l_error_flag := 'E';
      l_error_msg  := l_error_msg||'SUGGESTED_VENDOR_NAME can not be null';
     ELSE
        BEGIN
       
        SELECT vendor_id
        INTO
        l_vendor_id
        FROM AP_SUPPLIERS
        WHERE UPPER(vendor_name)=UPPER(v_requistion_rec.suggested_vendor_name);
        EXCEPTION WHEN OTHERS
        THEN
          --l_vendor_id:=NULL;
          l_error_flag := 'E';
          l_error_msg  := l_error_msg||'Error occure while retrieving the vendor_id'||SQLERRM;
        END;
     END IF;
   
     IF v_requistion_rec.suggested_vendor_site IS NULL THEN
      l_error_flag := 'E';
      l_error_msg  := l_error_msg||'SUGGESTED_VENDOR_SITE can not be null';
     ELSE
        BEGIN
          SELECT vendor_site_id
          INTO l_site_id
          FROM ap_supplier_sites
          WHERE UPPER(vendor_site_code)=UPPER(v_requistion_rec.suggested_vendor_site)
                AND vendor_id=l_vendor_id
                AND org_id=l_org_id;
        EXCEPTION WHEN OTHERS THEN
          l_error_flag := 'E';
          l_error_msg  := l_error_msg||'Error occure while retrieving the site_id'||SQLERRM;
        END;
       
     END IF;
   
     -------VALIDATION ENDS HERE------------------
     ------STRTING THE INSERTION IN REQUSITION INTERFACE -------------------------
     IF l_error_flag IS NULL THEN
       
        INSERT INTO PO_REQUISITIONS_INTERFACE_ALL
             (batch_id
            ,TRANSACTION_ID
            ,interface_source_code
            ,source_type_code
            ,requisition_type
            ,destination_type_code
            ,item_id
            ,item_description
            ,quantity
            ,authorization_status
            ,preparer_id
            --,category_id
            ,uom_code
            ,destination_organization_id
            ,destination_subinventory
            ,deliver_to_location_id
            ,deliver_to_requestor_id
            ,charge_account_id
            ,need_by_date
            ,org_id
            ,unit_price
            ,autosource_flag
            ,line_attribute13
            ,line_attribute14
            ,suggested_vendor_id
            ,suggested_vendor_site_id        
             )
      values    
            (v_requistion_rec.batch_id
            ,PO_REQUISITIONS_INTERFACE_S.nextval
            ,'FUTURE'
            ,'VENDOR'
            ,'PURCHASE'
            ,'INVENTORY'
            ,l_item_id
            ,v_requistion_rec.item_description
            ,v_requistion_rec.quantity
            ,'INCOMPLETE'
            ,l_emp_id
            ,v_requistion_rec.uom_code
            ,l_organization_id
            ,v_requistion_rec.destination_subinventory
            ,l_location_id
            ,l_requestor_id
            ,l_chanrge_account_id
            ,l_need_by_date
            ,l_org_id
            ,v_requistion_rec.unit_price
            ,NULL
            ,v_requistion_rec.record_id
            ,'REQ_WRAPPER'||p_batch_id
            ,l_vendor_id
            ,l_site_id
            );
     ELSE
        UPDATE xxspanv_po_req_preinteface
        SET ERROR_FLAG=l_error_flag,
           ERROR_MSG  =l_error_msg
        WHERE batch_id=v_requistion_rec.batch_id
        AND record_id =v_requistion_rec.record_id;
                 
     END IF;    
     COMMIT;          
   END LOOP;
   SUBMIT_REQUEST (P_BATCH_ID);
 END MAIN;        
PROCEDURE SUBMIT_REQUEST (P_BATCH_ID IN NUMBER --,
                          --STATUS     OUT VARCHAR2
                          )
IS
 l_request_id number;
 l_max_wait   number;
 l_phase      varchar2(10);
 l_status     varchar2(10);
 l_dev_status  varchar2(10);
 l_message    varchar2(100);
 l_interval   number;
 l_dev_phase  varchar2(10);
 rphase       varchar2(10);
 rstatus      varchar2(10);
 dphase       varchar2(10);    
 dstatus      varchar2(10);
 message      varchar2(100);
 call_status boolean ;
 wait_status boolean ;
 BEGIN
 fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
                            fnd_profile.value('RESP_ID'),
                            fnd_profile.value('RESP_APPL_ID'),
                            null,
                            null
                            );

 l_request_id:= fnd_request.submit_request('PO'
                                           ,'REQIMPORT'
                                           ,NULL
                                           ,NULL
                                           ,FALSE
                                           ,NULL---arg1
                                           ,p_batch_id
                                           ,'VENDOR'
                                           ,NULL
                                           ,NULL
                                           ,'Y'
                                           );                                          
 COMMIT;

 wait_status:=fnd_concurrent.wait_for_request (l_request_id,
                                  60  ,
                                  0,
                                  l_phase   ,
                                  l_status  ,
                                  l_dev_phase,
                                  l_dev_status,
                                  L_message);

 
 -- call_status :=fnd_concurrent.get_request_status(l_request_id, '', '',
       --          rphase,rstatus,dphase,dstatus, message);
  fnd_file.put_line(fnd_file.log,'dphase = '||l_dev_phase||'and '||'dstatus ='||l_dev_status)  ;
               
  IF UPPER(l_dev_phase)='COMPLETE' AND UPPER(l_dev_status)= 'NORMAL' THEN
   dbms_output.put_line ('calling the procuedure to create po');----calling the po_creation procedure
  fnd_file.put_line(fnd_file.log,'calling the procuedure to create po');
   CREATE_PO (P_BATCH_ID);
  END IF;                                                                  
   exception when others
   then
   fnd_file.put_line(fnd_file.log,'Error occure in procedure submit_request'||SQLERRM);                              
 END SUBMIT_REQUEST;
----to create po using aoutocreate need to insert all approved requisions into po interfaces tables and then calling the api--
PROCEDURE CREATE_PO (P_BATCH_ID IN number)
IS
l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(5000);
l_num_lines_processed number;
l_document_number po_headers_all.segment1%TYPE;
l_document_id number;
l_msg  varchar2(5000);
l_msg_index_out number;
CURSOR po_interface_headers_rec IS
SELECT  distinct prl.requisition_header_id
       ,prl.vendor_id
       ,prl.vendor_site_id
       ,prl.attribute14
       --,prl.unit_meas_lookup_code
       ,prl.destination_organization_id
       --,prl.org_id
       ,prh.org_id
       --,prl.deliver_to_location_id
       ,prh.preparer_id
       --,prh.authorization_status
       ,xprp.ship_to_location
       ,xprp.bill_to_location
       --,xprp.item_name
       ,xprp.suggested_vendor_name
       ,xprp.suggested_vendor_site    
 FROM  po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd,
       xxspanv_po_req_preinteface xprp      
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prh.type_lookup_code = 'PURCHASE'
   AND to_char(xprp.batch_id)=substr(prl.attribute14,12)
   --AND to_char(xprp.record_id)=(prl.ATTRIBUTE13)
   AND prh.authorization_status='APPROVED'
   --AND prh.closed_code<>'FINALLY CLOSED'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
   AND prl.attribute14='REQ_WRAPPER'||p_batch_id;
 
CURSOR po_interface_lines_rec (p_requistion_header_id number)
IS
SELECT  prl.requisition_header_id
       ,prl.quantity
       ,prl.requisition_line_id
       ,prl.item_id
       ,prl.unit_price
       ,prl.line_num
       --,prl.vendor_id
       --,prl.vendor_site_id
       ,prl.attribute14
       ,prl.unit_meas_lookup_code
       ,prl.destination_organization_id
       ,prl.org_id
       ,prl.deliver_to_location_id
       ,prh.preparer_id
       ,prh.authorization_status
 FROM  po_requisition_headers_all prh,
       po_requisition_lines_all prl,
       po_req_distributions_all prd      
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prh.type_lookup_code = 'PURCHASE'
   AND prh.authorization_status='APPROVED'
   --AND prh.closed_code<>'FINALLY CLOSED'
   AND prd.requisition_line_id = prl.requisition_line_id
   AND prl.requisition_header_id=p_requistion_header_id
   AND NOT EXISTS (SELECT 1 FROM po_distributions_all pda WHERE pda.req_distribution_id=prd.distribution_id)
   AND prl.attribute14='REQ_WRAPPER'||p_batch_id ;

 BEGIN
  DBMS_OUTPUT.PUT_LINE('CREATE PO PROCEDURE STARTS');
  fnd_file.put_line      (fnd_file.log,'PO CREATE Procedure starts here.');
 
   fnd_global.apps_initialize(fnd_profile.value('USER_ID'),
                            fnd_profile.value('RESP_ID'),
                            fnd_profile.value('RESP_APPL_ID'),
                            null,
                            null
                            );
  mo_global.SET_POLICY_CONTEXT('S',204);  
                         
  for i in po_interface_headers_rec
   loop
    fnd_file.put_line(fnd_file.log,'requistion_header_id = '||i.requisition_header_id);    
         INSERT INTO po.po_headers_interface
            (interface_header_id,
            batch_id,
            interface_source_code,
            process_code,
            action,
            org_id,
            document_type_code,
            document_subtype,
            currency_code,
            agent_id,
            vendor_id,
            vendor_site_id,
            ship_to_location,
            bill_to_location,--,
            group_code,
            style_id
            )
            VALUES
            (apps.po_headers_interface_s.NEXTVAL,          
            apps.po_headers_interface_s.currval,
            'PO',
            'PENDING',
            'NEW',  --  Indicates this is a new document
            i.org_id, -- Org id for operating unit Vision Operations
            'PO',  -- Indicates a standard PO is being imported
            'STANDARD',
            'USD', -- The currency to be used in the PO
            i.preparer_id, -- The ID of the buyer
            i.vendor_id,  -- Supplier name
            i.vendor_site_id,  --  Supplier Site
            i.ship_to_location, -- Ship to location name
            i.bill_to_location,--, -- Bill to location name          
            'DEFAULT',--'TestPO'
            1
            );
    FOR j IN po_interface_lines_rec (i.requisition_header_id)
      LOOP
       
        INSERT INTO po_lines_interface
            (interface_line_id,
            interface_header_id,
            line_num,
            --shipment_num,
            line_type,
            item_id,--item,
            unit_of_measure,
            quantity,
            unit_price,
            --ship_to_organization_code,
            --ship_to_location,
            requisition_line_id
            )
            VALUES
            (po_lines_interface_s.nextval,
            po_headers_interface_s.currval,
            j.line_num,
            --1,
            'Goods',
            j.item_id,  -- Item to imported on the PO line
            j.unit_meas_lookup_code,  -- Unit of Measure
            j.quantity,
            j.unit_price,  --  Line price in specified currency
            j.requisition_line_id--'V1',  --  Inventory Organization which will receive the shipment
            --'V1- New York City'
            );
           dbms_output.put_line('insertion done in line interface');
           fnd_file.put_line(fnd_file.log,'insertion done in line interface');
      END LOOP;
     commit;
     dbms_output.put_line('api_calling');
    fnd_file.put_line(fnd_file.log,'api calling');
     FND_MSG_PUB.Initialize;
   
     dbms_output.put_line('po_headers_interface_s.currvalis'||po_headers_interface_s.currval);
     PO_INTERFACE_S.create_documents (
                                    p_api_version => 1.0,
                                    x_return_status => l_return_status,
                                    x_msg_count => l_msg_count,
                                    x_msg_data => l_msg_data,
                                    p_batch_id => po_headers_interface_s.currval,
                                    p_req_operating_unit_id => i.org_id,--–p_req_operating_unit_id,
                                    p_purch_operating_unit_id =>i.org_id,--–p_purch_operating_unit_id,
                                    x_document_id => l_document_id,
                                    x_number_lines => l_num_lines_processed,
                                    x_document_number => l_document_number,                                    --— <DBI FPJ>
                                    p_sourcing_k_doc_type => null,
                                    p_conterms_exist_flag => null,
                                    p_document_creation_method => 'AUTOCREATE',
                                    p_orig_org_id => null
                                    );
      fnd_file.put_line(fnd_file.log,'api calling done');                                  
     IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
     fnd_file.put_line(fnd_file.log,'l_return_status='||l_return_status);                                      
      UPDATE xxspanv_po_req_preinteface a
      SET    po_num=l_document_number,
             error_flag='P',
             a.error_msg=null
      WHERE  a.suggested_vendor_name=i.suggested_vendor_name
      AND    a.suggested_vendor_site=i.suggested_vendor_site
      AND    a.batch_id=p_batch_id;
   
     COMMIT;
   
     ELSIF L_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
       
      ROLLBACK;
         
          FOR i IN 1 .. l_msg_count
           LOOP
                  fnd_msg_pub.get(p_msg_index     => i,
                                  p_data          => l_msg_data,
                                  p_encoded       => fnd_api.g_false,
                                  p_msg_index_out => l_msg_index_out);
                  l_msg := l_msg||'.'|| l_msg_data;
           END LOOP;
         
       FND_MSG_PUB.DELETE_MSG();
     
      UPDATE xxspanv_po_req_preinteface a            
      SET    --po_num=l_document_number,
             error_flag='E',
             a.error_msg=L_MSG
      WHERE  a.suggested_vendor_name =i.suggested_vendor_name
      AND    a.suggested_vendor_site =i.suggested_vendor_site
      AND    a.batch_id=p_batch_id;
      --AND    a.item_name             =i.item_name;
   
     COMMIT;
   
     END IF;                        
 
   END LOOP;
  EXCEPTION WHEN OTHERS THEN
      fnd_file.put_line(fnd_file.log,'Error occure in procedure create_po'||SQLERRM);
      dbms_output.put_line('exception occured'||sqlerrm);
 END CREATE_PO;
end xxspanvi_req_po_load;
/