Blanket Purchase Order Import Program Script: Oracle EBS R12

To create Blanket Purchase Order(PO) using Oracle standard API please follow below steps.

Step:1 Validate and Insert PO header and line details in PO_HEADERS_INTERFACE and PO_LINES_INTERFACE tables respectively.

SET serveroutput ON
DECLARE
p_supplier VARCHAR2(1000):='Staples';
p_supplier_site VARCHAR2(1000):='STAPLES LA';
p_ship_to_location VARCHAR2(1000):='V1- New York City';
p_bill_to_location VARCHAR2(1000):='V1- New York City';
p_currency VARCHAR2(1000):='USD';
p_buyer VARCHAR2(1000):='Stock, Ms. Pat';
p_item VARCHAR2(1000):='Oil';
p_unit_of_measure VARCHAR2(1000):='METRICTON';
p_unit_price number:=1;
v_org_name       VARCHAR2(30)                               := 'Vision Operations';
v_dest_code      VARCHAR2(30)                               := 'INVENTORY';
v_error_flag NUMBER:= 0;
v_main_error VARCHAR2(1000):= NULL;
v_currency VARCHAR2(1000);
v_agent_id number;
v_org_id NUMBER;
v_vendor_id NUMBER;
v_vendor_site_id NUMBER;
v_ship_to_location_id NUMBER;
v_bill_to_location_id NUMBER;
v_item_id NUMBER;
v_uom_code VARCHAR2(1000);
v_intf_dist_id NUMBER;
v_intf_line_id NUMBER;
v_intf_header_id NUMBER;
v_batch_id NUMBER;
v_line_type_id NUMBER;

BEGIN
  -----------VALIDATING THE PRICE CURRENCY CODE --------------
  BEGIN
    SELECT currency_code
    into v_currency
    FROM apps.fnd_currencies
    WHERE enabled_flag       = 'Y'
    AND currency_flag        = 'Y'
    AND upper(currency_code) = upper(trim(p_currency));
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'PRICE CURRENCY NOT EXIST IN EBS (WHEN ND) ';
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR WHILE VALIDATING CURRENCY CODE(WHEN OTH) '||' '||SQLCODE||':'||sqlerrm ;
  END;
   --------- --VALIDATING AGENT AND FETCHING THE AGENT_ID-------------------------
  BEGIN
    SELECT ppf.person_id
    INTO v_agent_id
    FROM per_people_f ppf
    WHERE ppf.full_name = p_buyer
    and rownum=1;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'AGENT ID/TRADER PERSON NUMBER NOT EXIST IN EBS(WHEN ND) ';
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error|| 'ERROR WHILE VALIDATING AGENT ID / TRADER PERSON NUMBER(WHEN OTH)-'|| SQLCODE ||':'||sqlerrm;
  END;
  ---------VALIDATING ORAGANIZATION AND FETCHING  ORANIZATION ID-----------------
  BEGIN
    SELECT  organization_id
    INTO v_org_id
    FROM apps.hr_operating_units
    WHERE NAME = v_org_name
    and rownum=1;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'ORGANIZATION ID/INTERNAL COMPANY NUMBER NOT FOUND IN EBS(WHEN ND) '||v_org_name;
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR WHILE VALIDATING ORGANIZATION ID/INTERNAL COMPANY NUMBER (WHEN OTH)-'||v_org_name||' '||SQLCODE||' '|| sqlerrm;
  END;
  ---------------------VALIDATING THE VENDOR NAME AND FETCHING VENDOR_ID----------------------
  BEGIN
    SELECT vendor_id
    INTO v_vendor_id
    FROM apps.po_vendors
    WHERE upper(vendor_name) = upper(p_supplier);
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'VENDOR ID/COUNTERPART COMPANY NUMBER NOT EXISTS IN EBS (WHEN ND) '||p_supplier;
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR WHILE VALIDATING VENDOR ID/COUNTERPART COMPANY NUMBER (WHEN OTH) '||p_supplier||' '||SQLCODE||' '||sqlerrm;
  END;
  ---------------------VALIDATING THE VENDOR SITE ID----------------------------
  BEGIN
    SELECT vendor_site_id
    INTO v_vendor_site_id
    FROM apps.po_vendor_sites_all
    WHERE upper(vendor_site_code) = upper(p_supplier_site)
    AND vendor_id                 = v_vendor_id
    AND org_id                    = v_org_id;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'VENDOR SITE ID NOT EXIST IN EBS (WHEN ND) '||p_supplier_site;
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR WHILE VALIDATING VENDOR SITE ID(WHEN OTH)-'||p_supplier_site||' '||SQLCODE||':'|| sqlerrm;
  END;
  --------VALIDATING THE SHIP TO LOCATION AND FETCHING SHIP_TO_LOCATION_ID-------------
  BEGIN
    SELECT location_id
    INTO v_ship_to_location_id
    FROM hr_locations_all
    WHERE location_code = p_ship_to_location;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'SHIP TO LOCATION ID/DESTINATION LOCATION NUMBER NOT FOUND IN EBS (WHEN ND)-'||p_ship_to_location;
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'ERROR WHILE VALIDATING SHIP TO LOCATION ID/DESTINATION LOCATION NUMBER (WHEN OTH)'||p_ship_to_location||' '||SQLCODE||':'||sqlerrm;
  END;
  -------------------VALIDATING THE BILL TO LOCATION AND FETCHING THE BILL_TO_LOCATION_ID--------------------------
  BEGIN
    SELECT location_id
    INTO v_bill_to_location_id
    FROM hr_locations_all
    WHERE location_code = p_bill_to_location ;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' BILL TO LOCATION ID NOT EXISTS IN EBS (WHEN ND) '||p_bill_to_location;
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR WHILE VALIDATING BILL TO LOCATION ID(WHEN OTH) '||p_bill_to_location||':'||SQLCODE||':'||sqlerrm;
  END;
  ----------------VALIDATING THE ITEM AND FETCHING ITEM ID----------------------
  BEGIN
    SELECT inventory_item_id
    INTO v_item_id
    FROM apps.mtl_system_items
    WHERE enabled_flag  = 'Y'
    AND SYSDATE         > TRUNC(NVL(start_date_active, SYSDATE -1))
    AND SYSDATE         < TRUNC(NVL(end_date_active, SYSDATE   + 1))
    AND segment1        = p_item
    AND organization_id = v_org_id;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'ITEM ID/COMODITY NOT FOUND IN EBS PLEASE CHECK FOR REFERENCE DATA MAPPING FOR TRADE ';
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'ERROR WHILE VALIDATING ITEM ID/COMODITY FOR TRADE '||p_item||' '||SQLCODE||' '||sqlerrm;
  END;
  ------------------- VALIDATE UNIT_PRICE---------------------
  BEGIN
    IF(NVL(p_unit_price,0) < 1) THEN
      v_error_flag        := 1;
      v_main_error        := v_main_error||' UNIT_PRICE CANNOT BE LESS THAN ZERO OR NULL FOR OBLIGATION ';
    END IF;
  END;
  -------------- VALIDATE UNIT_OF_MEASURE------------------------
  BEGIN
    SELECT uom_code
    INTO v_uom_code
    FROM apps.mtl_units_of_measure
    WHERE SYSDATE              < TRUNC(NVL(disable_date, SYSDATE + 1))
    AND upper(unit_of_measure) = upper(p_unit_of_measure);
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'NO UOM FOUND IN EBS FOR UOM CODE '||p_unit_of_measure;
  WHEN too_many_rows THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'MULTIPLE UOM CODE FOUND IN EBS FOR UOM '||p_unit_of_measure;
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR WHILE FETCHING UOM CODE '||p_unit_of_measure||':'||SQLCODE||':'||sqlerrm;
  END;
  ------------ GETTING SEQUESNCE VALUE FOR INTERFACE HEADER ID --------
  SELECT apps.po_headers_interface_s.nextval
  INTO v_intf_header_id
  FROM dual;
  -----------GENERATING BATCH ID--------------------
  SELECT TO_CHAR(SYSDATE,'DDMMYYHHMMSS')
  INTO v_batch_id
  FROM dual;
  ------GETTING INTERFACE LINE ID FROM LINE INTERFACE SEQUENCE----------------
  BEGIN
    SELECT apps.po_lines_interface_s.nextval INTO v_intf_line_id FROM dual;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' Error In Getting Value From Sequence For Line Id ';
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' Error In Getting Value From Sequence For Line Id '||SQLCODE||':'||sqlerrm;
  END;
  -------------Getting Line Type Id for CM line Type --------------
  BEGIN
    SELECT line_type_id
    INTO v_line_type_id
    FROM apps.po_line_types_v
    WHERE NVL(inactive_date,SYSDATE+1) > SYSDATE
    AND line_type                      = 'Goods';
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := 'ERROR WHILE FETCHING LINE TYPE ID(WHEN ND)-';
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_error_flag := v_error_flag||' ERROR WHILE FETCHING LINE TYPE ID(WHEN OTH) '||SQLCODE||':'||sqlerrm;
  END;
  ----------- GETTING INTERFACE DISTRIBTION ID FROM DISTRIBUTION INTERFACE SEQUENCE ----------------
  BEGIN
    SELECT apps.po_distributions_interface_s.nextval
    INTO v_intf_dist_id
    FROM dual;
  EXCEPTION
  WHEN no_data_found THEN
    v_error_flag := 1;
    v_main_error := v_main_error||' ERROR IN GETTING VALUE FROM SEQUENCE FOR DISTRIBUTION ID ';
  WHEN OTHERS THEN
    v_error_flag := 1;
    v_main_error := v_main_error||'ERROR IN GETTING VALUE FROM SEQUENCE FOR DISTRIBUTION ID '||SQLCODE||':'||sqlerrm;
  END;
dbms_output.put_line(v_main_error);
  --===========================Inserting Records IN Interface Table ==================================-----------------
  IF v_error_flag <> 1 THEN
    BEGIN
      INSERT
      INTO apps.po_headers_interface
        (
          interface_header_id,
          action,
          process_code,
          document_type_code,
          batch_id,
          document_num,
          currency_code, 
          approval_required_flag,
          approval_status,
          vendor_id,
          terms_id,
          fob,
          org_id,
          agent_id,
          effective_date,
          vendor_site_id,
          ship_to_location_id,
          bill_to_location_id
        )
        VALUES
        (
          v_intf_header_id,       -- INTERFACE_HEADER_ID
          'ORIGINAL',             -- ACTION
          'PENDING' ,             -- PROCESS_CODE
          'BLANKET',              -- DOCUMENT_TYPE_CODE
          v_batch_id,
          NULL,                   -- DOCUMENT_NUM
          v_currency,             -- CURRENCY_CODE
          'Y',                    -- APPROVAL_REQUIRED_FLAG
          'APPROVED',             -- APPROVAL_STATUS
          v_vendor_id,            -- VENDOR_ID
          NULL,                   -- V_PAYEMENT_TERM_ID,          
          NULL,                   -- V_DELIVERY_TERM,              
          v_org_id,               -- ORG_ID
          v_agent_id,             -- AGENT_ID
          to_date(SYSDATE),       -- EFFECTIVE_DATE
          v_vendor_site_id,       -- VENDOR_SITE_ID
          v_ship_to_location_id,  -- SHIP_TO_LOCATION_ID
          v_bill_to_location_id   -- BILL_TO_LOCATION_ID
        );
      IF SQL%notfound THEN
        v_error_flag := 1;
        v_main_error := v_main_error||' '||'Record Not Inserted in Header Interface Table';
      ELSE
        dbms_output.put_line('Inserted Records In Header Interface Table');
      END IF;
      IF v_error_flag <> 1 THEN
        BEGIN
          INSERT
          INTO apps.po_lines_interface
            (
              interface_line_id,
              interface_header_id,
              line_num,
              uom_code,
              item_id,
              unit_price,
              need_by_date,
              promised_date,
              line_type_id,
              ship_to_organization_id,
              ship_to_location,
              note_to_receiver
            )
            VALUES
            (
              v_intf_line_id,         --INTERFACE_LINE_ID
              v_intf_header_id,       --INTERFACE_HEADER_ID
              NULL,                   --LINE_NUM
              v_uom_code,             --UNIT_OF_MEASURE
              v_item_id,              --ITEM_ID
              p_unit_price,           --UNIT_PRICE
              SYSDATE,                --NEED_BY_DATE
              SYSDATE,                --PROMISED_DATE
              v_line_type_id,         --LINE_TYPE_ID
              NULL,                   --SHIP_TO_ORGANIZATION_ID
              v_ship_to_location_id,  --SHIP_TO_LOCATION
              NULL                    --NOTE_TO_RECEIVER
            );
          IF SQL%notfound THEN
            v_error_flag := 1;
            v_main_error := v_main_error||' '||'Record Not Inserted in Lines Interface Table';
          ELSE
            dbms_output.put_line('Inserted Records In Lines Interface Table');
          END IF;
       END;
      END IF;
    END;
  END IF;
 commit;
END;

Step:2 Once the records are inserted in PO header and line interface tables run the API Import Price Catalogs(POXPDOI) to import Blanket Purchase Order.

SET serveroutput ON

DECLARE

v_request_id NUMBER;

v_org_id     NUMBER := 204;

BEGIN

  apps.mo_global.init ('PO');

  apps.mo_global.set_policy_context ('S', 204);

  apps.fnd_global.apps_initialize ( user_id => 1318, resp_id => 50578, resp_appl_id => 201 );

  v_request_id :=apps.fnd_request.submit_request 

                                            ('PO' ,

                                            'POXPDOI' ,

                                            'Import Price Catalogs' ,

                                              SYSDATE ,

                                              FALSE ,

                                              NULL ,

                                              'Blanket' ,

                                              NULL ,

                                              'N' ,

                                              'N' ,

                                              'APPROVED' ,

                                              NULL ,

                                              NULL ,

                                              v_org_id,   -- operating unit ID

                                              'N' ,

                                              NULL ,

                                              NULL ,

                                              NULL ,

                                              NULL );

  dbms_output.put_line(v_request_id);

commit;

END;

Step:3 Once concurrent program gets completed you can query the Blanket PO.