Inspecting Code Coverage in Salesforce

Getting Code coverage is very crucial part while deploying the apex triggers or class from Sandbox to Production org.

Inspecting Code Coverage

After running tests, you can view code coverage information in the Tests tab of the Developer Console. The code coverage pane includes coverage information for each Apex class and the overall coverage for all Apex code in your organization.

Also, code coverage is stored in two Lightning Platform Tooling API objects: ApexCodeCoverageAggregate and ApexCodeCoverage. ApexCodeCoverageAggregate stores the sum of covered lines for a class after checking all test methods that test it. ApexCodeCoverage stores the lines that are covered and uncovered by each individual test method. For this reason, a class can have multiple coverage results in ApexCodeCoverage—one for each test method that has tested it. You can query these objects by using SOQL and the Tooling API to retrieve coverage information. Using SOQL queries with Tooling API is an alternative way of checking code coverage and a quick way to get more details.

For example, this SOQL query gets the code coverage for the TaskUtil class. The coverage is aggregated from all test classes that exercised the methods in this class.

SELECT ApexClassOrTrigger.Name, NumLinesCovered, NumLinesUncoveredFROM ApexCodeCoverageAggregate

WHERE ApexClassOrTrigger.Name = 'TaskUtil'

PO Import Steps

What are the steps required to import Standard Purchase Orders using the Purchasing Document Open Interface and is there any sample sample data that can be used in a vision environment ?

This note is designed to explain the steps to import a Standard Purchase Order and to provide sample data from a Vision environment to facilitate testing.

Please note that the scripts provided here are only sample scripts to introduce users to the functionality. More columns are available for use in the interface tables and more data can be imported depending on what is required. Please see the section at the end of the note for more documentation on the import process.

The scope of this Note is limited to importing Standard Purchase Orders. The use of the Import Price Catalog program will not be demonstrated here.

SOLUTION

1 - Open SQL Developer and open database connection for the environment to be used for import

WARNING : This step will delete data from the interface tables. Do not do this if others are testing any part of the purchasing process as these tables are used, not only during import, but during other processes such as autocreate. This step is only to be used during testing in test environment. Please do not delete interface data in a production environment.

Run the following statements to clear existing data from the interface tables. Please note that this may interfere with other testing.

delete from po_headers_interface;
delete from po_lines_interface;
delete from po_distributions_interface;
delete from po_interface_errors;
commit;

This step can be ignored if required. This step is only included here to clear out any data from the interface tables so as to prevent any confusion over the results obtained by running the import program.

2 - Please use the following insert statements to populate the interface tables depending on the scenario required :

a - PO with a single line :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
1,
'PENDING',
'ORIGINAL',  --  Indicates this is a new document
204, -- Org id for operating unit Vision Operations
'STANDARD',  -- Indicates a standard PO is being imported
'USD', -- The currency to be used in the PO
57, -- The ID of the buyer
'Office Supplies, Inc.',  -- Supplier name
'OFFICESUPPLIES',  --  Supplier Site
'V1- New York City', -- Ship to location name
'V1- New York City', -- Bill to location name
'TestPO'); -- Reference that can be tied to the imported PO

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',  -- Item to imported on the PO line
'Ea',  -- Unit of Measure
1,
50,  --  Line price in specified currency
'V1',  --  Inventory Organization which will receive the shipment
'V1- New York City' );  --  Ship to location name

commit;

b - PO with one line and one distribution :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
2,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57, -- Your buyer id
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City' );

INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
1,
1,
12975); -- Code Combination ID for the Charge Account to be used on the Distribution

commit;

c - PO with two lines :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
3,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City' );

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
2,
1,
'Goods',
'CM96713',
'Ea',
1,
80,
'V1',
'V1- New York City' );

commit;


d - PO with one line and two shipments :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
4,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
1,
50,
'V1',
'V1- New York City',
sysdate+1);

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(apps.po_lines_interface_s.NEXTVAL,
apps.po_headers_interface_s.CURRVAL,
1,
2,
'Goods',
'CM96713',
'Ea',
2,
50,
'V1',
'V1- New York City',
sysdate+5);

commit;

e - PO with one line, one shipment and two distributions :

INSERT INTO po.po_headers_interface
(interface_header_id,
batch_id,
process_code,
action,
org_id,
document_type_code,
currency_code,
agent_id,
vendor_name,
vendor_site_code,
ship_to_location,
bill_to_location,
reference_num)
VALUES
(apps.po_headers_interface_s.NEXTVAL,
5,
'PENDING',
'ORIGINAL',
204,
'STANDARD',
'USD',
57,
'Office Supplies, Inc.',
'OFFICESUPPLIES',
'V1- New York City',
'V1- New York City',
'TestPO');

INSERT INTO po.po_lines_interface
(interface_line_id,
interface_header_id,
line_num,
shipment_num,
line_type,
item,
uom_code,
quantity,
unit_price,
ship_to_organization_code,
ship_to_location,
promised_date)
VALUES
(po_lines_interface_s.nextval,
po_headers_interface_s.currval,
1,
1,
'Goods',
'CM96713',
'Ea',
5,
50,
'V1',
'V1- New York City',
sysdate+1);

INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
1,
1,
12975);

INSERT INTO po.po_distributions_interface
(interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
quantity_ordered,
charge_account_id)
VALUES
(po_headers_interface_s.currval,
po.po_lines_interface_s.CURRVAL,
po.po_distributions_interface_s.NEXTVAL,
2,
4,
12976);

commit

3 - The following queries can be used to check the data before the import program is run :

select * from po_headers_interface;
select * from po_lines_interface;
select * from po_distributions_interface;

4 - Login to Oracle Applications as CBAKER/WELCOME

5 - Navigate to responsibility Purchasing, Vision Operations (USA)

6 - Navigate to the Request Submission form and select the Import Standard Purchase Orders request. Set the parameter Create or Update Items to No and the parameter Approval Status to Incomplete as show below :

7 - Once the program has completed successfully, the next step is to find the imported document. Navigate to the Purchase Order Summary form and query using the Order fields under the Date Ranges tab. The easiest way to do this is to search for POs created from today's date to tomorrow's date. This will capture the newly imported PO as shown below :

8 - Clicking the Open button will open the PO in the Purchase Orders form. The screen shot below shows a PO imported using Scenario (a) as shown above (PO with single Line)

This note only shows a few possible examples and there any many more combinations of data that can be used in the interface tables.. For more information on the open interface, please review the following documentation :

Oracle Manufacturing APIs and Open Interfaces Manual

This manual contains a detailed explanation of the import process as well as the usable fields in the interface tables. The user guide is only available with the 11i version of the online documentation. For information on changes made to the import process in R12, please see the following notes:

Note 437587.1 Release 12 Oracle Purchasing API and Open Interface Changes
Note 823468.1 Release 12.1.1 New and Changed Features in Oracle Purchasing
Note 755979.1 - Where to find Open Interface Documentation for Release 12 Oracle Purchasing?

The following note is useful for diagnosing problems while importing Standard Purchase Orders
Note.781351.1 How To Diagnose Problems With Importing Standard Purchase Orders:




For more Vision Demo Notes, please see Note 1075748.1 : Vision Demo Document Index Procurement)

REFERENCES

NOTE:437587.1 - Release 12 Oracle Purchasing API and Open Interface Changes
NOTE:755979.1 - Where to find Open Interface Documentation for Release 12 Oracle Purchasing?
NOTE:781351.1 - How To Diagnose Problems With Importing Standard Purchase Orders
NOTE:823468.1 - Release 12.1.1 New and Changed Features in Oracle Purchasing

Retreving the variance using LAG function

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

SELECT empno,
       ename,
       job,
       sal,
       LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,
       sal - LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_diff
FROM   emp;

API TO Create New Code Combination

DECLARE
  l_segment1   GL_CODE_COMBINATIONS.SEGMENT1%TYPE;
  l_segment2   GL_CODE_COMBINATIONS.SEGMENT2%TYPE;
  l_segment3   GL_CODE_COMBINATIONS.SEGMENT3%TYPE;
  l_segment4   GL_CODE_COMBINATIONS.SEGMENT4%TYPE;
  l_segment5   GL_CODE_COMBINATIONS.SEGMENT5%TYPE;
  l_segment6   GL_CODE_COMBINATIONS.SEGMENT6%TYPE;
  l_valid_combination BOOLEAN;
  l_cr_combination    BOOLEAN;
  l_ccid       GL_CODE_COMBINATIONS_KFV.code_combination_id%TYPE;
  l_structure_num FND_ID_FLEX_STRUCTURES.ID_FLEX_NUM%TYPE;
  l_conc_segs GL_CODE_COMBINATIONS_KFV.CONCATENATED_SEGMENTS%TYPE;
  p_error_msg1                 VARCHAR2(240);
  p_error_msg2                 VARCHAR2(240);
BEGIN
  l_segment1  := '00101';
  l_segment2  := '28506';
  l_segment3  := '00000';
  l_segment4  := '14302';
  l_segment5  := '00455';
  l_segment6  := '00000';
  l_conc_segs := l_segment1||'.'||l_segment2||'.'||l_segment3||'.'||l_segment4||'.'||l_segment5||'.'||l_segment6 ;
  BEGIN
    SELECT id_flex_num
      INTO l_structure_num
      FROM apps.fnd_id_flex_structures
     WHERE id_flex_code        = 'GL#'
       AND id_flex_structure_code='EPC_GL_ACCOUNTING_FLEXFIELD';
  EXCEPTION
  WHEN OTHERS THEN
    l_structure_num:=NULL;
  END;
  ---------------Check if CCID exits with the above Concatenated Segments---------------
  BEGIN
    SELECT code_combination_id
      INTO l_ccid
      FROM apps.gl_code_combinations_kfv
     WHERE concatenated_segments = l_conc_segs;
  EXCEPTION
  WHEN OTHERS THEN
    l_ccid:=NULL;
  END;
  IF l_ccid IS NOT NULL THEN
    ------------------------The CCID is Available----------------------
    DBMS_OUTPUT.PUT_LINE('COMBINATION_ID= ' ||l_ccid);
  ELSE
  DBMS_OUTPUT.PUT_LINE('This is a New Combination. Validation Starts....');
    ------------Validate the New Combination--------------------------
    l_valid_combination := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CHECK_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS
                          );
    p_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

    IF l_valid_combination then

      DBMS_OUTPUT.PUT_LINE('Validation Successful! Creating the Combination...');
      -------------------Create the New CCID--------------------------

      L_CR_COMBINATION := APPS.FND_FLEX_KEYVAL.VALIDATE_SEGS
                          (
                          operation => 'CREATE_COMBINATION',
                          appl_short_name => 'SQLGL',
                          key_flex_code => 'GL#',
                          structure_number => L_STRUCTURE_NUM,
                          concat_segments => L_CONC_SEGS );
          p_error_msg2 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

      IF l_cr_combination THEN
        -------------------Fetch the New CCID--------------------------
        SELECT code_combination_id
          INTO l_ccid
          FROM apps.gl_code_combinations_kfv
        WHERE concatenated_segments = l_conc_segs;
        DBMS_OUTPUT.PUT_LINE('NEW COMBINATION_ID = ' || l_ccid);
      ELSE
        -------------Error in creating a combination-----------------
        DBMS_OUTPUT.PUT_LINE('Error in creating the combination: '||p_error_msg2);
      END IF;
    ELSE
      --------The segments in the account string are not defined in gl value set----------
      DBMS_OUTPUT.PUT_LINE('Error in validating the combination: '||p_error_msg1);
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM);
END;

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;
/

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;
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;
/