Oracle EBS R12/AP/iPayments: Supplier Bank Accounts

In R12 Supplier Bank Accounts can be setup at four different Levels:

·         Supplier

·         Supplier Site

·         Address

·         Address Operating Unit

Following are the SQL queries to select Supplier Bank Accounts at all levels:

Query 1: Banks Associated at Supplier Level

SELECT  HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

          'Supplier' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND ASS.VENDOR_ID           = APS.VENDOR_ID

AND IEP.PAYEE_PARTY_ID      = HZP.PARTY_ID

AND IEP.EXT_PAYEE_ID        = IPI.EXT_PMT_PARTY_ID

AND IPI.INSTRUMENT_ID       = IEB.EXT_BANK_ACCOUNT_ID

AND IEB.BANK_ID             = HZPBANK.PARTY_ID

AND IEP.PARTY_SITE_ID IS  NULL

AND IEP.ORG_ID IS  NULL 

AND IEP.ORG_TYPE IS  NULL

AND IEP.SUPPLIER_SITE_ID IS   NULL  

Query 2: Banks Associated at Supplier Site Level

SELECT HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

          ASS.VENDOR_SITE_CODE "SITE_CODE",

          HOU.NAME "OU_NAME",

          'Supplier Site' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  HZ_PARTY_SITES SITE_SUPP ,

  AP_SUPPLIER_SITES_ALL ASS ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK,

  hr_operating_units hou

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND HZP.PARTY_ID            = SITE_SUPP.PARTY_ID

AND SITE_SUPP.PARTY_SITE_ID = ASS.PARTY_SITE_ID

AND ASS.VENDOR_ID           = APS.VENDOR_ID

AND IEP.PAYEE_PARTY_ID      = HZP.PARTY_ID

AND IEP.PARTY_SITE_ID       = SITE_SUPP.PARTY_SITE_ID

AND IEP.SUPPLIER_SITE_ID    = ASS.VENDOR_SITE_ID

AND IEP.EXT_PAYEE_ID        = IPI.EXT_PMT_PARTY_ID

AND IPI.INSTRUMENT_ID       = IEB.EXT_BANK_ACCOUNT_ID

AND IEB.BANK_ID             = HZPBANK.PARTY_ID

AND IEP.ORG_TYPE IS NOT NULL

AND ass.ORG_ID = hou.organization_id

Query 3: Banks Associated at Address Level

SELECT   HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

          'Supplier Address' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  HZ_PARTY_SITES SITE_SUPP ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND HZP.PARTY_ID            = SITE_SUPP.PARTY_ID

AND IEP.PAYEE_PARTY_ID      = HZP.PARTY_ID

AND IEP.PARTY_SITE_ID       = SITE_SUPP.PARTY_SITE_ID

AND IEP.EXT_PAYEE_ID        = IPI.EXT_PMT_PARTY_ID

AND IPI.INSTRUMENT_ID       = IEB.EXT_BANK_ACCOUNT_ID

AND IEB.BANK_ID             = HZPBANK.PARTY_ID

AND IEP.ORG_ID IS  NULL 

AND IEP.SUPPLIER_SITE_ID IS   NULL  

Query 4: Banks Associated at Address OU Level

SELECT HZP.PARTY_NAME "SUPPLIER_NAME" ,

          APS.SEGMENT1 "SUPPLIER_NUMBER",

          IEB.BANK_ACCOUNT_NUM "BANK_ACCOUNT_NUMBER" ,

          HZPBANK.PARTY_NAME "BANK_NAME",

                                  HOU.NAME "OU_NAME"

          'Supplier Address OU' Record_type

FROM HZ_PARTIES HZP ,

  AP_SUPPLIERS APS ,

  HZ_PARTY_SITES SITE_SUPP ,

  IBY_EXTERNAL_PAYEES_ALL IEP ,

  IBY_PMT_INSTR_USES_ALL IPI ,

  IBY_EXT_BANK_ACCOUNTS IEB ,

  HZ_PARTIES HZPBANK

WHERE HZP.PARTY_ID          = APS.PARTY_ID

AND HZP.PARTY_ID            = SITE_SUPP.PARTY_ID

AND IEP.PAYEE_PARTY_ID      = HZP.PARTY_ID

AND IEP.PARTY_SITE_ID       = SITE_SUPP.PARTY_SITE_ID

AND IEP.EXT_PAYEE_ID        = IPI.EXT_PMT_PARTY_ID

AND IPI.INSTRUMENT_ID       = IEB.EXT_BANK_ACCOUNT_ID

AND IEB.BANK_ID             = HZPBANK.PARTY_ID

AND IEP.ORG_ID IS  NOT NULL 

AND IEP.SUPPLIER_SITE_ID IS   NULL  

AND IEP.ORG_ID = hou.organization_id

 

R12 Order Management/E Biz Tax: API to Re-Calculate Tax at Order Level

You can re-calculate tax for any given sales order by using following PL/SQL Block. This API works same as ‘Actions->Calculate Tax’ from Sales Order Form (OEXOEORD)

DECLARE

l_header_id NUMBER;

l_return_status VARCHAR2(10);

BEGIN

            l_header_id := 12345;--pass order headers header id

            --Initialize OU parameter before calling if you running from Back End

            OM_TAX_UTIL.calculate_tax(p_header_id       => l_header_id

                                     ,x_return_status   => l_return_status);

          IF (l_return_status = 'S') THEN

             commit;

          ELSE

              Dbms_output.put_line(‘Failed in Re-Calculating Tax’);

              Rollback;

          END IF;

END;

R12 PO /Purchasing: API to Update Purchase Requisition Requestor/Preparer/Approver

Following PL/SQL Block can be used to Update Purchase Requisition:

DECLARE

     l_progress          VARCHAR2(4);

     l_msg_data          VARCHAR2(2000);

     l_msg_count         NUMBER;

     l_return_status     VARCHAR2(1);

     l_update_person     VARCHAR2(200);

     l_old_personid      NUMBER;

     l_new_personid      NUMBER;

     l_document_type     VARCHAR2(200);

     l_document_no_from  VARCHAR2(200);

     l_document_no_to    VARCHAR2(200);

     l_date_from         VARCHAR2(200);

     l_date_to           VARCHAR2(200);

     l_commit_interval   NUMBER;

     x_date_from         DATE;

     x_date_to           DATE;

BEGIN

        --Set your OU of Respective Requisition

        mo_global.set_policy_context('S', 204);

        l_update_person      :='REQUESTOR'; -- You can also Pass 'PREPARER' or 'APPROVER' or 'ALL'

        l_old_personid       :=1234; -- Pass Old Person Id

        l_new_personid       := 4576; -- Pass New Person Id

        l_document_type      := 'ALL'; -- You can Document Type of Requisition

        l_document_no_from   := '12345'; -- You can pass Requisition Number

        l_document_no_to     := '12345'; --If you want to update Range, you can Requistion Number To as Different

        l_commit_interval    := 100;

        x_date_from := to_date(NULL);

        x_date_to   := to_date(NULL);

        PO_Mass_Update_Req_GRP.Update_Persons

                                             (p_update_person    => l_update_person,

                                              p_old_personid     => l_old_personid,

                                              p_new_personid     => l_new_personid,

                                              p_document_type    => l_document_type,

                                              p_document_no_from => l_document_no_from,

                                              p_document_no_to   => l_document_no_to,

                                              p_date_from        => x_date_from,

                                              p_date_to          => x_date_to,

                                              p_commit_interval  => l_commit_interval,

                                              p_msg_data         => l_msg_data,

                                              p_msg_count        => l_msg_count,

                                              p_return_status    => l_return_status);

                dbms_output.put_line('l_msg_count '||l_msg_count);

                dbms_output.put_line('l_return_status '||l_return_status);

                IF nvl(l_msg_count,0) > 0 THEN

                   FOR i IN 1..l_msg_count LOOP

                       l_msg_data := FND_MSG_PUB.get

                                                      ( p_msg_index => i,

                                                                p_encoded => 'F');

                                dbms_output.put_line('l_msg_data '||l_msg_data);

                   END LOOP;

                END IF;

COMMIT;

END;