Access to Edit or Add New Agreements (Mass Upload) for any Global BPA from iSupplier Portal R12

1. Enable Global BPA
    Enable Global while creating the BPA for the Supplier you wish while creating the Purchase Order


2. We  can enable the “Edit Agreement” option in the action menu in iSupplier      Portal responsibility as below,

Catalog Blanket Agreements can be flagged as enabled for Supplier Editing through iSupplier Portal. This setup is done either as buyer in the work centre, or as Catalog Administrator, using below,

• Buyer Work Centre > Agreements > Action: Enable for Supplier Authoring
• iProcurement Catalog Administration > Agreements > Enable Supplier Authoring (only those agreements enabled for Catalog Administrator Authoring)
  

   The supplier can then find the agreements under the Orders tab, and select to Edit Agreement to be able to   create or update items either via file upload (OAG XML, cXML, CIF and text file) . After suppliers submit  agreement changes, the buyer will receive a notification. The Buyer will then have the option to approve the change through notification or review and compare the details via the Pending Change page.

3. To setup a supplier user in any Env. we need to follow the below steps : 

1. Set profile POS: External URL to the same as the internal URL (profile POS: Internal URL)
2. Go to Purchasing responsibility > Suppliers
3. Search for a supplier
4. Click Update
5. Click Contact Directory
6. Click Create Contact
7. Enter the contact information
8. Check Create User Account for this Contact
9. Enter username (preferably not the same as the email)
10. Check the responsibilities to assign
11. Click Apply
12. Go to System administrator to reset the password if the email with the password is not received.
13. Log as the supplier user to test iSupplier Feature

Procure to Pay (P2P) Life Cycle

P2P Query :

SELECT   DISTINCT
         RH.SEGMENT1 "REQ NUMBER",
         RH.PREPARER_ID,
         RH.CREATION_DATE,
         PH.SEGMENT1 "PO NUMBER",
         RL.ITEM_DESCRIPTION,
         RL.TO_PERSON_ID,
         RL.SOURCE_TYPE_CODE,
         RL.NEED_BY_DATE,
         PH.VENDOR_ID,
         PH.VENDOR_SITE_ID,
         PV.VENDOR_NAME "VENDOR NAME",
         UPPER (PV.VENDOR_TYPE_LOOKUP_CODE) "VENDOR TYPE",
         PVS.VENDOR_SITE_CODE "VENDOR SITE",
         PVS.ADDRESS_LINE1 "ADDRESS",
         PVS.CITY "CITY",
         PVS.COUNTRY "COUNTRY",
         TO_CHAR (TRUNC (PH.CREATION_DATE)) "PO DATE",
         PH.TYPE_LOOKUP_CODE "PO TYPE",
         PD.QUANTITY_ORDERED "QTY ORDERED",
         PD.QUANTITY_CANCELLED "QTY CANCALLED",
         PL.ITEM_DESCRIPTION "ITEM DESCRIPTION",
         PL.UNIT_PRICE "UNIT PRICE",
         (NVL (PD.QUANTITY_ORDERED, 0) - NVL (PD.QUANTITY_CANCELLED, 0))
         * NVL (PL.UNIT_PRICE, 0)
            "PO LINE AMOUNT",
         DECODE (PH.APPROVED_FLAG, 'Y', 'Approved') "PO STATUS",
         PL.ITEM_ID,
         AIA.INVOICE_TYPE_LOOKUP_CODE "INVOICE TYPE",
         AIA.INVOICE_AMOUNT "INVOICE AMOUNT",
         TO_CHAR (TRUNC (AIA.INVOICE_DATE)) "INVOICE DATE",
         AIA.INVOICE_NUM "INVOICE NUMBER",
         (SELECT   DESCRIPTION
            FROM   APPS.AP_TERMS_TL Y
           WHERE   Y.TERM_ID = AIA.TERMS_ID)
            "INVOICE TERMS",
         (SELECT   DECODE (X.MATCH_STATUS_FLAG, 'A', 'Approved')
            FROM   AP.AP_INVOICE_DISTRIBUTIONS_ALL X
           WHERE   X.INVOICE_DISTRIBUTION_ID = AIDA.INVOICE_DISTRIBUTION_ID)
            "INVOICE APPROVED ?",
         AIA.AMOUNT_PAID,
         AIPA.AMOUNT,
         AIA.INVOICE_CURRENCY_CODE,
         AIA.PAYMENT_CURRENCY_CODE,
         AIA.PAYMENT_METHOD_LOOKUP_CODE "PAYMENT METHOD",
         ACA.CHECK_NUMBER "PAYMENT NUMBER",
         TO_CHAR (TRUNC (ACA.CHECK_DATE)) "PAYMENT DATE"
  FROM   APPS.PO_HEADERS_ALL PH,
         APPS.PO_DISTRIBUTIONS_ALL PD,
         APPS.PO_REQ_DISTRIBUTIONS_ALL RD,
         APPS.PO_REQUISITION_LINES_ALL RL,
         APPS.PO_REQUISITION_HEADERS_ALL RH,
         APPS.PO_LINES_ALL PL,
         APPS.PO_VENDORS PV,
         APPS.PO_VENDOR_SITES_ALL PVS,
         APPS.RCV_TRANSACTIONS RT,
         APPS.RCV_SHIPMENT_HEADERS RSH,
         APPS.RCV_SHIPMENT_LINES RSL,
         APPS.AP_INVOICES_ALL AIA,
         APPS.AP_INVOICE_LINES_ALL AILA,
         APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
         APPS.AP_INVOICE_PAYMENTS_ALL AIPA,
         APPS.AP_CHECKS_ALL ACA
 WHERE       PH.PO_HEADER_ID = PD.PO_HEADER_ID
         AND PD.REQ_DISTRIBUTION_ID = RD.DISTRIBUTION_ID
         AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
         AND RL.REQUISITION_HEADER_ID = RH.REQUISITION_HEADER_ID
         AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
         AND PV.VENDOR_ID(+) = PH.VENDOR_ID
         AND PVS.VENDOR_SITE_ID(+) = PH.VENDOR_SITE_ID
         AND RT.PO_HEADER_ID = PH.PO_HEADER_ID
         AND RT.PO_LINE_ID = PL.PO_LINE_ID
         AND RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
         AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
         AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
         AND AILA.PO_HEADER_ID = PH.PO_HEADER_ID
         AND AILA.PO_LINE_ID = PL.PO_LINE_ID
         AND AIA.INVOICE_ID = AILA.INVOICE_ID
         AND AIDA.INVOICE_ID = AILA.INVOICE_ID
         AND AIDA.INVOICE_LINE_NUMBER = AILA.LINE_NUMBER
         AND AIPA.INVOICE_ID = AIA.INVOICE_ID
         AND ACA.CHECK_ID = AIPA.CHECK_ID

Joins and Tables :

 

1. Requisition:

SELECT * FROM PO_REQUISITION_HEADERS_ALL WHERE SEGMENT1='XXXX' --REQUISITION_HEADER_ID SELECT * FROM PO_REQUISITION_LINES_ALL WHERE REQUISITION_HEADER_ID=XXXX  --REQUISITION_LINE_ID SELECT * FROM PO_REQ_DISTRIBUTIONS_ALL WHERE REQUISITION_LINE_ID=XXX

   2. Purchase Order :

             PO_HEADERS_ALL

 

SELECT PO_HEADER_ID FROM PO_HEADERS_ALL WHERE SEGMENT1 =;

SELECT * FROM PO_HEADERS_ALL WHERE PO_HEADER_ID =;

 

PO_LINES_ALL

SELECT * FROM PO_LINES_ALL WHERE PO_HEADER_ID =;

 PO_LINE_LOCATIONS_ALL

 SELECT * FROM PO_LINE_LOCATIONS_ALL WHERE PO_HEADER_ID =;

 

             PO_DISTRIBUTIONS_ALL 

SELECT * FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =;

 PO_RELEASES_ALL

 SELECT * FROM PO_RELEASES_ALL WHERE PO_HEADER_ID =; 

    3. Receiving :

RCV_SHIPMENT_HEADERS

 SELECT * FROM RCV_SHIPMENT_HEADERS WHERE SHIPMENT_HEADER_ID IN

(SELECT SHIPMENT_HEADER_ID FROM RCV_SHIPMENT_LINES

WHERE PO_HEADER_ID =);

 RCV_SHIPMENT_LINES

 SELECT * FROM RCV_SHIPMENT_LINES WHERE PO_HEADER_ID =;

 RCV_TRANSACTIONS 

SELECT * FROM RCV_TRANSACTIONS WHERE PO_HEADER_ID =;

 RCV_ACCOUNTING_EVENTS

 SELECT * FROM RCV_ACCOUNTING_EVENTS WHERE RCV_TRANSACTION_ID IN

(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS

WHERE PO_HEADER_ID =);

 RCV_RECEIVING_SUB_LEDGER 

SELECT * FROM RCV_RECEIVING_SUB_LEDGER WHERE RCV_TRANSACTION_ID IN

(SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS WHERE PO_HEADER_ID =);

 RCV_SUB_LEDGER_DETAILS

 SELECT * FROM RCV_SUB_LEDGER_DETAILS

WHERE RCV_TRANSACTION_ID IN (SELECT TRANSACTION_ID FROM RCV_TRANSACTIONS

WHERE PO_HEADER_ID =);

     4. Invoices :

AP_INVOICE_DISTRIBUTIONS_ALL

 SELECT * FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =);

 AP_INVOICES_ALL

 SELECT * FROM AP_INVOICES_ALL WHERE INVOICE_ID IN

(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =));

 AP_INVOICE_LINES_ALL  

 SELECT * FROM AP_INVOICE_LINES_ALL WHERE INVOICE_ID IN

(SELECT INVOICE_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL WHERE PO_DISTRIBUTION_ID IN

( SELECT PO_DISTRIBUTION_ID FROM PO_DISTRIBUTIONS_ALL WHERE PO_HEADER_ID =));

       5. Payments :

         We Can join AP_INVOICE_LINES_ALL with PO_HEADER_ID & PO_LINE_ID from        PO_HEADERS_ALL table.

 

FOR VIEW LIST OF PAYMENTS:

            SELECT * FROM AP_INVOICE_PAYMENTS_ALL;

            SELECT * FROM AP_PAYMENT_SCHEDULES_ALL;

 FOR CHECK’S INFORMATION:

            SELECT * FROM AP_CHECKS_ALL;

 FOR CHECK FORMAT:

            SELECT * FROM AP_CHECK_FORMATS;

            SELECT * FROM AP_CHECKRUN_CONC_PROCESSES_ALL;