Blanket Purchase Agreement Interface OR Conversion in Oracle Apps R12.2.7

Overview:

We need to create blanket purchase agreements when we know the detail of the goods or services we plan to buy from a specific supplier in a period
We can use blanket purchase agreements to specify negotiated prices for our items before actually purchasing them.

This document help you to create Blanket purchase agreements using Interface.
I have used this document for migrate Blanket purchase agreements data from 11i to R12.2.7

Standard Import Program:

     "Import Price Catalogs" concurrent program is used to import Catalog Quotations, Standard Quotations, and Blanket Purchase Agreements.

Interface Tables are:

  1) PO_HEADERS_INTERFACE
  2) PO_LINES_INTERFACE
  3) PO_LINE_LOCATIONS_INTERFACE

Base Tables are:

  1) PO_HEADERS_ALL
  2) PO_LINES_ALL
  3) PO_LINE_LOCATIONS_ALL

Mandatory Columns

1) PO_HEADERS_INTERFACE:

  1)  Interface_header_id
  2)  Interface_source_code
  3)  Action -- (ORIGINAL -- for new record / UPDATE -- if you UPDATE any PO then add PO_HEADER_ID field)
  4)  Document_type_code
  5)  Document_subtype
  6)  Agent_id
  7)  Document_num   -- this is PO Number(In conversion it is used, and in interface it is may or may not be used)
  8)  Vendor_id
  9)  Vendor_site_id
  10) Ship_to_location
  11) Bill_to_location
  12) Vendor_contact_id
  13) Created_by
  14) Creation_date
  15) Last_updated_by
  16) Last_update_date
  17) Last_update_login
  18) Supply_agreement_flag
  19) Org_id
  20) rate
  21) Currency_code
  22) Rate_type
  23) Rate_date
  24) Payment_terms
  25) Effective_date

2) PO_LINES_INTERFACE:

  1)  Interface_header_id
  2)  Interface_line_id
  3)  Action
  4)  Line_type_id
  5)  Item
  6)  Uom_code
  7)  Quantity
  8)  Unit_price
  9)  Line_num
  10) Created_by
  11) Creation_date
  12) Last_updated_by
  13) Last_update_date
  14) Last_update_login
  15) Category
  16) Item_description
  17) Item_revision
  18) Expiration_date

3) PO_LINE_LOCATIONS_INTERFACE:

  1)  Interface_line_location_id
  2)  Interface_header_id
  3)  Interface_line_id
  4)  Quantity
  5)  Unit_of_measure
  6)  Shipment_type
  7)  Shipment_num
  8)  Ship_to_organization_id
  9)  Ship_to_location_id
  10) Start_date
  11) End_date
  12) Price_override
  13) Creation_date
  14) Qty_rcv_tolerance
  15) Receiving_routing_id

Validate all mandatory fields and insert valid records in Interface tables:

*Validation of header level fields:

  --1) Validation of Buyer Name
     SELECT agent_id INTO ln_agent_id
     FROM po_agents_v
     WHERE UPPER (agent_name) = UPPER (lreq_hdr.buyer_name)
       AND (end_date_active IS NULL OR end_date_active > SYSDATE);

 --2) Validation of Vendor Number
     SELECT vendor_id INTO ln_vendor_id
     FROM ap_suppliers
     WHERE UPPER (segment1) = UPPER (lreq_hdr.supplier_no)
       AND (end_date_active IS NULL OR end_date_active > SYSDATE);
 
 --3) Validation of Vendor Site Code
     SELECT vendor_site_id INTO ln_vendor_site_id
     FROM ap_supplier_sites_all
     WHERE UPPER (vendor_site_code) = UPPER (lreq_hdr.vendor_site_code)
       AND vendor_id = ln_vendor_id
       AND org_id = ln_org_id
       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

 --4) Validation of Ship to location/ deliver_loc_code
     SELECT location_code INTO lc_ship_to_location
     FROM hr_locations
     WHERE UPPER (location_code) = UPPER (lreq_hdr.deliver_loc_code)
       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

 --5) Validation of Vendor Contact
     SELECT vendor_contact_id INTO ln_vendor_contact_id
     FROM po_vendor_contacts
     WHERE vendor_site_id = ln_vendor_site_id
       AND UPPER (first_name || ' ' || middle_name || ' ' || last_name ) = UPPER (lreq_hdr.vendor_contact)
       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

 --6) Validation of Bill to location code
     SELECT location_id INTO ln_bill_to_location_id
     FROM hr_locations
     WHERE UPPER (location_code) = UPPER (lreq_hdr.bill_to_location_code)
       AND (inactive_date IS NULL OR inactive_date > SYSDATE);

 
* Insert data into PO_HEADERS_INTERFACE table:

INSERT INTO po_headers_interface
          (Interface_header_id,
           Interface_source_code,
           Action,
           Po_header_id,
           Document_type_code,
           Document_subtype,
           Agent_id,
           Document_num,
           Vendor_id,
           Vendor_site_id,
           Ship_to_location,
           Bill_to_location,
           Vendor_contact_id,
           Created_by,
           Creation_date,
           Last_updated_by,
           Last_update_date,
           Last_update_login,
           Supply_agreement_flag,
           Org_id,
           Rate,
           Currency_code,
           Rate_type,
           Rate_date,
           Terms_id)
   VALUES (po_headers_interface_s.NEXTVAL,-- interface_header_id
           'XXPO_007',  -- interface_source_code
           'ORIGINAL',  -- action
           po_headers_s.NEXTVAL,  -- po_header_id
           lrec_po_hdr.TYPE,      -- document_type_code
           lrec_po_hdr.sub_type,  -- document_subtype
           ln_agent_id,           -- Agent_id
           lrec_po_hdr.po_number, -- document_num
           ln_vendor_id,          -- vendor_id
           ln_vendor_site_id,     -- vendor_site_id
           lrec_po_hdr.deliver_loc_code,  -- ship_to_location
           lrec_po_hdr.bill_to_location_code,  -- bill_to_location
           ln_vendor_contact_id,      -- vendor_contact_id
           ln_user_id,                -- created_by
           lrec_po_hdr.creation_date, -- creation_date
           ln_user_id,                -- last_updated_by
           SYSDATE,                   -- last_update_date
           ln_user_id,                -- last_update_login
           DECODE (lrec_po_hdr.TYPE, 'BLANKET', 'Y', NULL), -- supply_agreement_flag
           rec_po_hdr.org_id,     -- org_id
           lrec_po_hdr.rate,      -- Rate
           lrec_po_hdr.currency_code,  -- Currency_code
           lrec_po_hdr.rate_type, -- Rate_type
           lrec_po_hdr.rate_date, -- Rate_date
           ln_terms_id  -- Terms_id
   );

* Validation of Line level fields:

 --1) Validation of line type
     SELECT line_type_id INTO ln_line_type_id
     FROM po_line_types_val_v
     WHERE UPPER (line_type) = UPPER (lreq_line.line_type);

 --2) Validation of Item Number
     SELECT inventory_item_id INTO ln_item_id
     FROM mtl_system_items_b
     WHERE UPPER (segment1) = UPPER (lreq_line.item_number)
       AND organization_id = ln_organization_id
   AND purchasing_enabled_flag = 'Y'
   AND purchasing_item_flag = 'Y';

 --3) Validation of UOM_CODE
     SELECT unit_of_measure INTO lc_unit_of_measure
     FROM mtl_units_of_measure_tl
     WHERE UPPER (uom_code) = UPPER (lreq_line.uom_code)
       AND LANGUAGE = 'US';

* Insert data into PO_LINES_INTERFACE table:

    INSERT INTO po_lines_interface
               (Interface_header_id,
                Interface_line_id,
                Po_header_id,
                Po_line_id,
                Action,
                Line_type_id,
                Line_num,
                Item,
                Uom_code,
                Unit_price,             
                Created_by,
                Creation_date,
                Last_updated_by,
                Last_update_date,
                Last_update_login,
                Item_revision,
                List_price_per_unit
               )
        VALUES (po_headers_interface_s.CURRVAL,  --Interface_header_id
                po_lines_interface_s.NEXTVAL,    --Interface_line_id
                po_headers_s.CURRVAL,       --Po_header_id
                po_lines_s.NEXTVAL,         --Po_line_id
                'ADD',                      --Action
                ln_line_type_id,            --Line_type_id
                lrec_po_line.line_number,   --Line_num
                lrec_po_line.item_number,   --Item
                lrec_po_line.uom_code,      --Uom_code
                lrec_po_line.unit_price,    --Unit_price
                ln_user_id,                     --Created_by
                SYSDATE,                    --Creation_date
                ln_user_id,                     --Last_updated_by
                SYSDATE,                    --Last_update_date
                ln_user_id,                    --Last_update_login
                lrec_po_line.item_revision, --Item_revision
                lrec_po_line.list_price     --List_price_per_unit
                );

* Validation of Line Location level fields:

 --1) Ship_to_location_code
   SELECT location_id, location_code
   INTO ln_ship_to_location_id, lc_ship_to_location_code
   FROM hr_locations_all
   WHERE UPPER (location_code) = UPPER (lrec_po_line_loc.ship_to_location_code);

 --2) Unit_of_measure
   SELECT unit_of_measure
   INTO lc_unit_of_measure
   FROM mtl_units_of_measure_tl
   WHERE UPPER (uom_code) = UPPER (lrec_po_line.UOM);

 --3) Shipment_type
   IF (lrec_po_line_loc.shipment_type = 'BLANKET') THEN
     lc_shipment_type := NULL;
   ELSE
     lc_shipment_type := 'PRICE BREAK';
   END IF;

 --4) Ship_to_organization_Code
   SELECT organization_id, organization_code
   INTO ln_ship_to_organization_id, lc_ship_to_org_code
   FROM org_organization_definitions
   WHERE organization_code = lrec_po_line_loc.ship_to_organization_code;

* Insert data into PO_LINES_LOCATIONS_INTERFACE table:

    INSERT INTO po_line_locations_interface
               (Interface_line_location_id,
                Interface_header_id,
                Interface_line_id,
                Quantity,
                Unit_of_measure,
                Shipment_type,
                Shipment_num,
                Ship_to_organization_id,
                Ship_to_location_id,
                Start_date,
                End_date,
                Price_override,
                Creation_date)
        VALUES (Po_line_locations_interface_s.NEXTVAL, -- Interface_line_location_id
                Po_headers_interface_s.CURRVAL, --Interface_header_id
                Po_lines_interface_s.CURRVAL,   --Interface_line_id
                Lrec_po_line_loc.quantity,      --Quantity
                Lc_unit_of_measure,             --Unit_of_measure
                Lc_shipment_type,               --Lrec_po_line_loc.shipment_type
                Lrec_po_line_loc.shipment_num,  --Shipment_num
                Ln_ship_to_organization_id,     --Ship_to_organization_id
                Ln_ship_to_location_id,         --Ship_to_location_id
                Lrec_po_line_loc.start_date,    --Start_date
                Lrec_po_line_loc.end_date,      --End_date
                Lrec_po_line_loc.price_override,--Price_override
                SYSDATE,                        --Creation_date
               );

--Check the values in interface tables

   SELECT * FROM po_headers_interface
   where 1=1
   AND creation_date LIKE sysdate
   AND document_type_code = 'BLANKET';
 
   SELECT * FROM po_lines_interface
   where 1=1
   AND creation_date LIKE sysdate;
 
   SELECT * FROM po_line_locations_interface
   where 1=1
   AND creation_date LIKE sysdate;
 
   SELECT * FROM po_interface_errors
   where 1=1
   AND creation_date LIKE sysdate;

-- Run Standard program for insert data in Base tables from interface tables:

   Responsibility: Purchasing Super User
   Standard Program Name: Import Standard Purchase Orders
   Navigation : Purchasing Super User → Reports → Run → Import Price Catalogs
   
   The parameters for the concurrent program are
      1. Default Buyer             → Optional:  A default buyer name which will appear on the imported PO.
      2. Document Type             → Mandatory:  'BLANKET'
      3. Document SubType          → Optional
      4. Create or Update Items    → Mandatory:  'NO'
      5. Creare Sourcing Rules     → Mandatory:  'NO'
      6. Approval Status           → Mandatory:  'APPROVED'
      7. Release Generation Method → Optional
      8. Batch Id                  → Optional
      9. Global Agreement          → Optional
     10. Sourcing Level            → Optional
     11. Inventory Organization    → Optional
     12. Group Lines               → Optional
     13. Batch Size                → Optional
     14. Gather Stas               → Optional

   If the import programs completes as normal check for the status of po_headers_interface action
      column. If its 'REJECTED' then check in the PO_INTERFACE_ERRORS.

      SELECT *
      FROM po_interface_errors
      WHERE last_update_date LIKE sysdate;

-- Check the Base tables:   

   SELECT * FROM po_headers_all
   where 1=1
   AND creation_date LIKE sysdate
   AND type_lookup_code = 'BLANKET';
 
   SELECT * FROM po_lines_all
   where 1=1
   AND creation_date LIKE sysdate;
   
   SELECT * FROM po_line_locations_all
   where 1=1
   AND creation_date LIKE sysdate;
 

** Typical errors:

   1) Error:    Records went in error at line level when Ordered_Quantity is more than Zero.
      Solution: Ordered_Quantity is always Null in Blanket PO.