API to update supplier site payment method in R12

Payment Method Update at Supplier Site in R12:

You may observe that you are able to see payment method for supplier site from the application but payment_method_lookup_code value is null in ap_supplier_sites_all table. The reason is Supplier site payment method details are stored in iby_ext_party_pmt_mthds table.

So in order to update the supplier site payment method details we have to use the "iby_disbursement_setup_pub.update_external_payee" API instead of "AP_VENDOR_PUB_PKG.Update_Vendor_Site".

DECLARE
   x_return_status             VARCHAR2 (200) := NULL;
   x_msg_count                 NUMBER := 0;
   x_msg_data                  VARCHAR2 (200) := NULL;
   l_payee_upd_status          iby_disbursement_setup_pub.ext_payee_update_tab_type;
   p_external_payee_tab_type   iby_disbursement_setup_pub.external_payee_tab_type;
   p_ext_payee_id_tab_type     iby_disbursement_setup_pub.ext_payee_id_tab_type;
   p_ext_payee_id_rec          iby_disbursement_setup_pub.Ext_Payee_ID_Rec_Type;
   l_ext_payee_rec             IBY_DISBURSEMENT_SETUP_PUB.External_Payee_Rec_Type;

   i                           NUMBER := 0;
   g_org_id                    NUMBER := 5320;
   g_user_id                   NUMBER := 19087;
   g_resp_id                   NUMBER := 57148;
   g_resp_appl_id              NUMBER := 201;
BEGIN
   FOR j
   IN (SELECT   ieppm.payment_method_code,
                iepa.payee_party_id,
                assa.vendor_site_id,
                iepa.ext_payee_id,
                assa.org_id,
                iepa.supplier_site_id,
                assa.party_site_id
         FROM   ap_supplier_sites_all assa,
                ap_suppliers sup,
                iby_external_payees_all iepa,
                iby_ext_party_pmt_mthds ieppm,
                rbc_supplier_sites_int_stg sites_stg,
                hr_operating_units ou
        WHERE       sup.vendor_id = assa.vendor_id
                AND assa.pay_site_flag = 'Y'
                AND assa.vendor_site_id = iepa.supplier_site_id
                AND iepa.ext_payee_id = ieppm.ext_pmt_party_id(+)
                AND ieppm.payment_method_code IS NULL
                AND sites_stg.vendor_site_code = assa.vendor_site_code
                AND sites_stg.operating_unit_name = ou.name
                AND assa.org_id = ou.organization_id
--AND assa.vendor_site_id = 135632
                --and assa.vendor_site_code = 'ADR001047'
                --and assa.vendor_id = 3116634
      )
   LOOP
      IF j.org_id = 5320
      THEN
         g_resp_id := 57148;
      ELSE
         g_resp_id := 57143;
      END IF;

      fnd_global.apps_initialize (user_id        => g_user_id,
                                  resp_id        => g_resp_id,
                                  resp_appl_id   => g_resp_appl_id);
      mo_global.set_policy_context ('S', j.org_id);

      p_external_payee_tab_type (i).default_pmt_method := 'CHECK';
      p_external_payee_tab_type (i).payment_function := 'PAYABLES_DISB';
      p_external_payee_tab_type (i).exclusive_pay_flag := 'N';
      p_external_payee_tab_type (i).payee_party_id := j.payee_party_id;
      p_external_payee_tab_type (i).payer_org_id := j.org_id;
      p_external_payee_tab_type (i).payer_org_type := 'OPERATING_UNIT';
      p_external_payee_tab_type (i).supplier_site_id := j.supplier_site_id;
      p_external_payee_tab_type (i).Payee_Party_Site_Id := j.party_site_id;
      p_ext_payee_id_tab_type (i).ext_payee_id := j.ext_payee_id;
      iby_disbursement_setup_pub.update_external_payee (
         p_api_version            => 1.0,
         p_init_msg_list          => 'T',
         p_ext_payee_tab          => p_external_payee_tab_type,
         p_ext_payee_id_tab       => p_ext_payee_id_tab_type,
         x_return_status          => x_return_status,
         x_msg_count              => x_msg_count,
         x_msg_data               => x_msg_data,
         x_ext_payee_status_tab   => l_payee_upd_status
      );

      --COMMIT;
      --DBMS_OUTPUT.PUT_LINE ('External Payee Update :' || j.ext_payee_id);
      --DBMS_OUTPUT.PUT_LINE ('x_return_status: ' || x_return_status);

      IF x_return_status = 'E'
      THEN
         FOR k IN l_payee_upd_status.FIRST .. l_payee_upd_status.LAST
         LOOP
            DBMS_OUTPUT.put_line('Error Message from table type : '
                                 || l_payee_upd_status (k).Payee_update_Msg);
         END LOOP;
      END IF;

      i := 0;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error ' || SQLERRM);
END;

You must specify a valid bill for this bill revision - OM Error

Error: "You must specify a valid bill for this bill revision"
When: While creating the sales order on BOM item
Reason: BOM is not created in ship from organization.

Solution:  Either create a BOM in Warehouse (ship from org) entered on the order or create a common BOM in the Warehouse from the master org.

Useful Queries:
1) Extract item and organization

SELECT inventory_item_id, ship_from_org_id
   FROM oe_order_lines_all
WHERE 1=1
      AND line_id = :line_id

2) Check if bill is created in the warehouse
SELECT inventory_item_id, ship_from_org_id
   FROM bom_bill_of_materials
WHERE 1=1
      AND assembly_item_id = :inventory_item_id
      AND organization_id = :ship_from_org_id