Employee Bank Account Script oracle R12

SELECT

aps.vendor_id,

apss.vendor_site_id,

aps.vendor_name,

apss.vendor_site_code,

ieb.bank_name,

ieb.country,

iebb.bank_branch_name,

iebb.eft_swift_code,

iebb.branch_number,

ieba.bank_account_num,

ieba.bank_account_name

FROM ap.ap_suppliers aps,

per_all_people_f papf,

ap.ap_supplier_sites_all apss,

apps.iby_ext_bank_accounts ieba,

apps.iby_account_owners iao,

apps.iby_ext_banks_v ieb,

apps.iby_ext_bank_branches_v iebb

WHERE aps.vendor_id = apss.vendor_id

AND iao.account_owner_party_id = aps.party_id

AND ieba.ext_bank_account_id = iao.ext_bank_account_id

AND ieb.bank_party_id = iebb.bank_party_id

AND ieba.branch_id = iebb.branch_party_id

AND ieba.bank_id = ieb.bank_party_id

AND aps.employee_id = papf.person_id

AND TRUNC(sysdate) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE;

Supplier and Site bank detail query in R12

/* Formatted on 2/15/2018 2:46:46 PM (QP5 v5.114.809.3010) */
SELECT   /*Supplier Information*/
         aps.segment1 oracle_supplier_number,
         aps.vendor_id,
         aps.vendor_name supplier_name,
         aps.party_id supplier_party_id,
         iepa.remit_advice_fax remit_advice_fax,
         iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
         ,
         assa.vendor_site_id,
         assa.party_site_id supplier_party_site_id,
         assa.vendor_site_code vendor_site_code,
         assa.pay_site_flag pay_site_flag,
         assa.purchasing_site_flag purchasing_site_flag,
         assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
         ,
         ieba.ext_bank_account_id,
         hp.party_name Bank_party_name,
         ieba.bank_account_num bank_account_num,
         ieba.bank_account_name bank_account_name,
         ieba.country_code bank_acct_country_code,
         ieba.currency_code bank_acct_currency_code/* Bank Address */
         ,
         hp.address1 bank_address_line1,
         hp.address2 bank_address_line2,
         hp.address3 bank_address_line3,
         hp.city bank_address_city,
         hp.state bank_address_state,
         hp.postal_code bank_address_zip,
         hp.country bank_address_country/* Bank Branch Address */
         ,
         hp1.address1 branch_address_line1,
         hp1.address2 branch_address_line2,
         hp1.address3 branch_address_line3,
         hp1.city branch_address_city,
         hp1.state branch_address_state,
         hp1.postal_code branch_address_zip,
         hp1.country branch_address_country
  FROM   ap_supplier_sites_all assa,
         hz_parties hp,
         iby_ext_bank_accounts ieba,
         iby_external_payees_all iepa,
         iby_pmt_instr_uses_all ipiua,
         ap_suppliers aps,
         hz_parties hp1
 WHERE       assa.vendor_site_id = iepa.supplier_site_id
         AND hp.party_id = ieba.bank_id
         AND ipiua.instrument_id = ieba.ext_bank_account_id
         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
         AND assa.vendor_id = aps.vendor_id
         AND ieba.branch_id = hp1.party_id
         AND ipiua.instrument_type = 'BANKACCOUNT'
         AND aps.vendor_name LIKE '3M%'
         AND assa.vendor_site_id = 6916
         AND ipiua.payment_flow = 'DISBURSEMENTS'
         AND ipiua.order_of_preference = 1;

Supplier / Supplier Site Details:-

SELECT   assa.vendor_site_id
                  FROM   ap_supplier_sites_all assa,
                         hz_parties hp,
                         iby_ext_bank_accounts ieba,
                         iby_external_payees_all iepa,
                         iby_pmt_instr_uses_all ipiua,
                         ap_suppliers aps,
                         hz_parties hp1
                 WHERE       assa.vendor_site_id = nvl(iepa.supplier_site_id,assa.vendor_site_id)
                         AND hp.party_id = ieba.bank_id
                         AND ipiua.instrument_id = ieba.ext_bank_account_id
                         AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
                         AND assa.vendor_id = aps.vendor_id
                         AND ieba.branch_id = hp1.party_id
                         AND ipiua.instrument_type = ''BANKACCOUNT''
                         --and assa.vendor_site_id = 6916
                         AND ieba.ext_bank_account_id = : Bank Id
                         AND assa.org_id = fnd_profile.VALUE (''org_id'')


        ********************  ##################  ********************

SELECT  aps.vendor_name "VERDOR NAME",

        apss.vendor_site_code "VENDOR SITE CODE",

        ieb.bank_name "BANK NAME",

        iebb.bank_branch_name "BANK BRANCH NAME",

        iebb.branch_number "BRANCH NUMBER",

        ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

        ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME"

FROM    ap.ap_suppliers aps,

        ap.ap_supplier_sites_all apss,

        apps.iby_ext_bank_accounts ieba,

        apps.iby_account_owners iao,

        apps.iby_ext_banks_v ieb,

        apps.iby_ext_bank_branches_v iebb

WHERE   aps.vendor_id = apss.vendor_id

        and iao.account_owner_party_id = aps.party_id

        and ieba.ext_bank_account_id = iao.ext_bank_account_id

        and ieb.bank_party_id = iebb.bank_party_id

        and ieba.branch_id = iebb.branch_party_id

        and ieba.bank_id = ieb.bank_party_id;

Supplier With Bank Details and Payment Details in oracle apps

SQL  Query
-----------------------------------
select asp.party_id,
       asp.vendor_id,
       ass.pay_site_flag,
       ass.vendor_site_id,
       hrl_ship.location_code,
       hp_supp.party_id,
       asp.VENDOR_NAME Supplier_name,
       asp.VENDOR_TYPE_LOOKUP_CODE type,
       asp.ONE_TIME_FLAG,
       asp.individual_1099,
       DECODE (
            UPPER (asp.vendor_type_lookup_code),
            'EMPLOYEE',
            papf.national_identifier,
            DECODE (asp.organization_type_lookup_code,
                    'INDIVIDUAL', asp.individual_1099,
                    'FOREIGN INDIVIDUAL', asp.individual_1099,
                    hp_supp.jgzz_fiscal_code)
         ) Taxpayer_ID,
         asp.VAT_REGISTRATION_NUM ,
         hp_supp.DUNS_Number_c  ,
         asp.start_date_active,
         asp.end_date_active,
         asp.hold_flag,
         asp.last_update_date,
         ass.address_line1,
         ass.address_line2,
         ass.address_line3 ,
         ass.city,
         ass.state,
         ass.zip
         , ass.country,
         ass.purchasing_site_flag ,
         ass.RFQ_ONLY_SITE_FLAG ,
         ass.PAY_SITE_FLAG
         , ass.Vendor_Site_Code
         , ass.ATTRIBUTE1 Supplier_Qualification_Status
         , ass.ATTRIBUTE15  Invoice_Approver --, ass.SHIP_TO_LOCATION_CODE
         , hrl_ship.location_code ship_to_location_code
         , hrl_bill.location_code bill_to_location_code
         , ass.inactive_date
         , ass.last_update_date site_last_update_date
         , ass.terms_id
         , att.name terms_name
         , ass.invoice_currency_code
         , ass.payment_currency_code
         , ass.hold_all_payments_flag
         , ass.hold_future_payments_flag
         , ass.vat_registration_num
         , hou.name OPERATING_UNIT_NAME
         , ass.email_address
         , ass.primary_pay_site_flag
         , ass.DUNS_NUMBER
         , ass.PAY_GROUP_LOOKUP_CODE
         , person.person_first_name first_name
         , person.person_last_name  last_name
         , pty_rel.email_address
         , pty_rel.primary_phone_number
         , apsc.creation_date contact_creation_date
         , apsc.last_update_date contact_last_update_date
          --hrl_ship.*
-- person.person_first_name ,
-- person.person_last_name ,pty_rel.primary_phone_number ,
-- pty_rel.email_address
-- , apsc.email_address , ass.*
,ass.duns_number site_duns_number
from ap_suppliers asp
   , ap_supplier_sites_all ass
   , ap_supplier_contacts apsc
   , hz_parties person
   , hz_parties pty_rel
   , hz_parties hp_supp
   , per_all_people_f papf
   , hr_locations hrl_ship
   , hr_locations hrl_bill  
   , ap_terms_tl att
   , hr_operating_units hou
   --, AND pv.party_id = hp.party_id
where 1 = 1
--and asp.vendor_id = 46006 -- 5001 -- 
and ass.vendor_id = asp.vendor_id
and asp.party_id = hp_supp.party_id
AND apsc.org_party_site_id(+) = ass.party_site_id
--and ass.org_id = 222 -- 123
AND apsc.per_party_id = person.party_id(+)
AND apsc.rel_party_id = pty_rel.party_id(+)
AND asp.employee_id = papf.person_id(+)
and hrl_ship.location_id(+) = ass.ship_to_location_id
and hrl_bill.location_id(+) = ass.bill_to_location_id
and att.term_id(+) = ass.terms_id
and ass.org_id = hou.organization_id
and TRUNC(asp.creation_date) between :P_FROM_CREATION_DATE AND :P_TO_CREATION_DATE
and TRUNC(asp.last_update_date) between NVL(:P_FROM_UPDATE_DATE,TRUNC(asp.last_update_date)) AND NVL(:P_TO_UPDATE_DATE,TRUNC(asp.last_update_date))
and asp.vendor_name = nvl(:p_supplier_name,asp.vendor_name)

Formula Column1(CF_BANK_DATA)
-----------------------------
function CF_BANK_DATAFormula return Number is
begin
 
 
     SELECT   eb.bank_name                 ,
              eb.bank_number ,
                  ebb.bank_branch_name         ,
              ebb.branch_number            ,
              eba.bank_account_num         ,
              eba.bank_account_name
       INTO :CP_BANK_NAME,
            :CP_BANK_NUMBER,
            :CP_BANK_BRANCH_NAME,
            :CP_BRANCH_NUMBER,
            :CP_BANK_ACCOUNT_NUM  ,
            :CP_bank_account_name
   FROM ap.ap_suppliers av         ,
        ap.ap_supplier_sites_all assa     ,
        apps.iby_ext_bank_accounts eba  ,
        apps.iby_account_owners ao      ,
        apps.iby_ext_banks_v eb         ,
        apps.iby_ext_bank_branches_v ebb,
            hr_operating_units hou
  WHERE av.vendor_id           = assa.vendor_id
AND ao.account_owner_party_id = av.party_id
AND eba.ext_bank_account_id   = ao.ext_bank_account_id
AND eb.bank_party_id          = ebb.bank_party_id
AND eba.branch_id             = ebb.branch_party_id
AND eba.bank_id               = eb.bank_party_id
AND assa.org_id                 =hou.organization_id
AND av.vendor_id               = :vendor_id
AND assa.vendor_site_id    = :vendor_site_id;

    RETURN(NULL);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(100,'No bank records for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Formula Column2(CF_PAYMENT_METHOD_CODE)
--------------------------------
function CF_PAYMENT_METHOD_CODEFormula return Char is
lv_payment_method_code   VARCHAR2(30);
begin
 
 
  SELECT ieppm.payment_method_code
   INTO lv_payment_method_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id;
 

    RETURN(lv_payment_method_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(101,'No payment_method_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

formula Column3(CF_FORMAT_CODE)
-----------------------
function CF_FORMAT_CODEFormula return Char is
lv_format_code   VARCHAR2(30);
begin
 
 
  SELECT ifv.format_code
   INTO lv_format_code
   FROM IBY_EXT_PARTY_PMT_MTHDS IEPPM,
        IBY_EXTERNAL_PAYEES_ALL IEPA,
        IBY_FORMATS_VL IFV
  WHERE ieppm.ext_pmt_party_id = iepa.ext_payee_id
    AND iepa.payee_party_id = :party_id
    AND iepa.supplier_site_id = :vendor_site_id
    AND ifv.format_code(+) = iepa.payment_format_code;
 

    RETURN(lv_format_code);
 
EXCEPTION
    WHEN OTHERS THEN
    SRW.MESSAGE(102,'No format_code for vendor site id:- '||:vendor_site_id);
    RETURN(NULL);
 
end;

Query to find the cridit card in oracle apps

SELECT lookup_code,
      meaning,
      enabled_flag,
      start_date_active,
      end_date_active
FROM oe_lookups
WHERE lookup_type = 'CREDIT_CARD';

Creating Bank Branch in Accounts Payables oracle apps

Responsibility: Payables Manager
Navigation: Setup -> Payments -> Banks and Bank Branches

Click on Tab "Bank Branches" and hit "Create" button. You will navigate to "Create Bank Branch" Page. In this page enter Country and Bank name and hit "Continue" button .

You will navigate to "Create Bank Branch: Bank Branch Information" Page. Only branch name and type are mandatory

Hit "Save and Next" button

At this time, following changes are committed to database and you move to "Create Bank Branch: Branch Address" Page Two records will be inserted into HZ_PARTIES table:
One record for bank branch name, other record for relationship between Bank and Bank Branch. Record for Bank brach will have party_name and Branch name and PARTY_TYPE
 as ORGANIZATION. Record for bank and branch relationship will have PARTY_NAME as Bank name + '-' + Branch_name + '-' + PARTY_NUMBER for the record. Get PARTY_ID for both records.
Let’s say party_id for BRANCH is branch_party_id and bank branch relationship party is bank_branch_relation_party_id Two recors are inserted into HZ_RELATIONSHIPS table.
 Details of these records will be

A record is inserted into HZ_ORGANIZATION_PROFILES. branch_party_id, branch name, routing number, Alternate name go to party_id, organization_name,
 bank_or_branch_number and organization_name_phonetic columns respectively.

On "Create Bank Branch: Branch Address" Page, Hit "Create" button to create branch Address. You will navigate to "Branch Address Detail" Page.

Enter Address and hit "Apply button

You will go back to "Create Bank Branch: Branch Address" Page and a record in created in HZ_PARTY_SITES and HZ_LOCATIONS tables.

Following query can give address details
SELECT *
  FROM hz_party_sites psite
     , hz_locations   loc
 WHERE psite.location_id = loc.location_id
   AND psite.party_id    = :branch_party_id
Multiple addresses can be entered on this page

Hit "Save and Next" button. You will navigate to "Create Bank Branch: Branch Contact" page

Hit "Create contact Button on this page "Create Contact Page". In this page you can enter following
Contact Information
Contact Email
Contact Phones
Contact Addresses

To enter contact phone, hit "Add Another Row" button on "Phone" region and enter phone details

To enter Contact address, hit "Create" button on "Addresses" region. You will navigate to "address Details" page.

Enter Address Details and then hit "Apply" button. I think there is bug in this page. Hitting APPLY gives error "Attribute PartyId in HzPuiPartySiteEOEx is required".
So we need to Commit Contact information first and then create Address for the contact. By this time, following changes are committed to database Two records are created in HZ_PARTIES.
 one for Contact person and other for relationship between Branch Contact person and Bank (not the bank branch). These records will have party type as PERSON and PARTY_RELATIONSHIP.
 Take note of party_id for PERSON and PARTY_RELATIONSHIP records and call them branch_contact_party_id and branch_contact_rel_party_id. This branch_contact_rel_party_id value is
 used for creating Contact point and address records. The value of branch_contact_party_id is used for creating relationship between BANK and Contact PERSON.

Following query can retrieve Contact points

SELECT *
  FROM HZ_CONTACT_POINTS
 WHERE owner_table_id   = :branch_contact_rel_party_id
   AND owner_table_name = 'HZ_PARTIES'

Following query can give details of relationship records. I do not know why Oracle created relationship between branch contact and bank instead of bank branch. This may be a bug in Oracle.
SELECT *
  FROM hz_relationships
 WHERE subject_id         = :branch_contact_party_id
   AND subject_table_name = 'HZ_PARTIES'
   AND object_id          = :bank_party_id
   AND object_table_name  = 'HZ_PARTIES';
For this record, DIRECTIONAL_FLAG, DIRECTION_CODE and RELATIONSHIP_CODE should be 'F' (forward), 'P' (for Parent) and 'CONTACT_OF' Oracle will create backward
relationship between bank_party_id and branch_contact_party_id. For backward relationship record, DIRECTIONAL_FLAG, DIRECTION_CODE and RELATIONSHIP_CODE should be 'B' (backward),
 'C' (child) and 'CONTACT'. PARTY_ID value in both of these records will be same as relation_party_id value. RELATIONSHIP_TYPE for both records is CONTACT.

For each of these two records, a record is inserted into CE_CONTACT_ASSIGNMENTS table. Branch_party_id, bank_party_id and relationship_id go branch_party_id,
 bank_party_id and relationship_columns of this table. Since there seems to be a bug in the way branch contacts are inserted into HZ schema, this table is
importance to get details of branch contact. In order to get bracnh contact, hz_parties record for branch with this table on branch_party_id column and then relationship_id
 column with hz_relationships table and select only that record that directional_flag as 'F' and use SUBJECT_ID column to get branch contact details.
 For this record OBJECT_ID in hz_relationship is pointing to bank_party_id not the branch party_id.

Navigate to Navigation: Setup -> Payments -> Banks and Bank Branches

Query Branch created in previous steps and hit "Update Branch icon

Hit "Save and Next twice to reach "Update Bank Branch: Branch Contact" Page

Click on Update icon for Contact created in previous steps

Hit "Create" button on Address region

Enter Address and hit APPLY button

You will go back to "Create Contact page". Hit APPLY button again. By this time Contact address is inserted into database.

Following query can retrieve address details
SELECT *
  FROM hz_party_sites psite
     , hz_locations   loc
 WHERE psite.location_id = loc.location_id
   AND psite.party_id    = :branch_contact_rel_party_id

You will navigate to "Update Bank Branch: Branch Contact" page. Now Hit Finish button. Now bank branch is created.

Creating Banks in Accounts Payables in oracle apps

Responsibility: Payables Manager
Navigation: Setup -> Payments -> Banks and Bank Branches

Click on Tab “Banks” and hit “Create” button. You will navigate to “Create Bank: Bank Information”. In this page only Country and Bank name are mandatory.

Enter these values and hit “Save and Next” Button. You will navigate to “Create Bank : Bank Address”. At this time, a record is inserted into following tables

a. HZ_PARTIES
b. HZ_CODE_ASSIGNMENTS
c. HZ_ORGANIZATION_PROFILES

Following Query can retrieve record from hz_parties

SELECT *
  FROM hz_parties
  WHERE party_name = :bank name

Take a note of party_id, lets call it bank_party_id

Following query can retrieve record from hz_code_assignments

SELECT *
  FROM hz_code_assignments
 WHERE owner_table_id   = :bank_party_id
   AND owner_table_name = 'HZ_PARTIES'

Important columns in this table are CLASS_CATEGORY and CLASS_CODE

Following query can retrieve data from hz_organization_profiles table
SELECT *
  FROM hz_organization_profiles
 WHERE party_id = :bank_party_id

Hit “Create” button in Address region and you will navigate to “Bank Address Details Page” Enter Address Details and hit “Apply” button.
You will come back to “Create Bank : Bank Address”.

At this time one record per address is inserted into HZ_PARTY_SITES and HZ_LOCATION tables.

Following query can give address details

SELECT *
  FROM hz_party_sites psite
     , hz_locations loc
 WHERE psite.location_id = loc.location_id
   AND psite.party_id    = :bank_party_id

Now hit “Save and Next” button. You will navigate to “Contacts” Page.

Hit “Create Contact Button. You will navigate to Contact Information Page

Enter contact Detail, Email, Phone and Address details.

For entering Phone details, hit “Add Another Row” button and phone number details

For entering Address details, hit “Create” button and enter address detail. If you click “Identifying Address” check-box and if contact already has one identifying address defined, then UI will give you option to uncheck existing identifying address.

After entering Addresses, Hit apply. You will navigate to page “Contacts”.

Hit “Finish” button to save your work. At this time, 2 records will be inserted into HZ_PARTIES. One record will have PARTY_TYPE as PERSON and it will be for CONTACT person. Other record will be for relationship between BANK and CONTACT person. It will have PARTY_TYPE as PARTY_RELATIONSHIP.
Take a note of party_id for PARTY_RELATIONSHIP and PERSON records. Let’s call these "relation_party_id" and "person_party_id".
This relation_party_id value is used for creating Contact point and address records. The value of person_party_id is used for
creating relationship between BANK and Contact PERSON.

Following query can retrieve Contact points

SELECT *
  FROM HZ_CONTACT_POINTS
 WHERE owner_table_id   = :relation_party_id
   AND owner_table_name = 'HZ_PARTIES'

Following query can retrieve address details

SELECT *
  FROM hz_party_sites psite
     , hz_locations   loc
 WHERE psite.location_id = loc.location_id
   AND psite.party_id    = :relation_party_id

Following query can give details of relationship records

SELECT *
  FROM hz_relationships
 WHERE subject_id         = :person_party_id
   AND subject_table_name = 'HZ_PARTIES'
   AND object_id          = :bank_party_id
   AND object_table_name  = 'HZ_PARTIES';

For this record, DIRECTIONAL_FLAG, DIRECTION_CODE and RELATIONSHIP_CODE should be 'F' (forward), 'P' (for Parent) and 'CONTACT_OF' Oracle will create backward relationship between bank_party_id and person_party_id. For backward relationship record, DIRECTIONAL_FLAG, DIRECTION_CODE and RELATIONSHIP_CODE should be 'B' (backward), 'C' (child) and 'CONTACT'. PARTY_ID value in both of these records will be same as relation_party_id value. RELATIONSHIP_TYPE for both records is CONTACT

Verifying Supplier Bank Account Details at all levels

SELECT 'Bank Account At Supplier Site Level' Bank_Account_Level
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , ss.vendor_site_code
     , NULL   Party_Site_Code
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , ap_supplier_sites_all       ss
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.vendor_id     = ss.vendor_id
   AND ss.vendor_site_id = epa.supplier_site_id
   AND epa.ext_payee_id  = piu.ext_pmt_party_id      
   AND piu.instrument_id = eba.ext_bank_account_id
   AND sup.segment1      = :a
UNION
SELECT 'Bank Account at Supplier Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , NULL
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers sup
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = epa.payee_party_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id      
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = :a
   AND supplier_site_id    IS NULL
   AND party_site_id       IS NULL
UNION
SELECT 'Bank Account at Address + Opearting Unit Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , psite.party_site_name
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers            sup
     , hz_party_sites          psite
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = psite.party_id
   AND psite.party_site_id = epa.party_site_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id      
   AND piu.instrument_id   = eba.ext_bank_account_id
  AND sup.segment1        = :a
   AND supplier_site_id    IS NULL
   AND epa.org_id          IS NOT NULL
UNION
SELECT 'Bank Account at Address Level'
     , sup.segment1
     , sup.vendor_name
     , epa.org_id
     , NULL
     , psite.party_site_name
     , eba.bank_account_num
     , piu.order_of_preference priority
     , eba.ext_bank_account_id
  FROM ap_suppliers            sup
     , hz_party_sites          psite
     , iby_external_payees_all epa
     , iby_pmt_instr_uses_all  piu
     , iby_ext_bank_accounts   eba
 WHERE sup.party_id        = psite.party_id
   AND psite.party_site_id = epa.party_site_id
   AND epa.ext_payee_id    = piu.ext_pmt_party_id      
   AND piu.instrument_id   = eba.ext_bank_account_id
   AND sup.segment1        = :a
   AND supplier_site_id    IS NULL
   AND epa.org_id          IS NULL
 ORDER BY bank_account_num

R12 Supplier/Site/Banking Drilldown

Supplier level

PO_VENDORS.party_id = HZ_PARTIES.party_id

PO_VENDORS.employee_id = PER_EMPLOYEES_CURRENT_X.employee_id(+)

PO_VENDORS.employee_id = PER_ALL_PEOPLE_F.person_id(+)

Site level

AP_SUPPLIER_SITES_ALL.vendor_id = PO_VENDORS.vendor_id

AP_SUPPLIER_SITES_ALL.location_id = HZ_LOCATIONS.location_id(+)

AP_SUPPLIER_SITES_ALL.ship_to_location_id = HR_LOCATIONS.location_id(+)

AP_SUPPLIER_SITES_ALL.org_id = AP_SYSTEM_PARAMETERS_ALL.org_id

AP_SUPPLIER_SITES_ALL.org_id = HR_OPERATING_UNITS.org_id

AP_SUPPLIER.SITES_ALL.party_site_id=HZ_PARTY_SITES.party_site_id

Bank level

IBY_EXT_BANK_ACCOUNTS.ext_bank_account_id = IBY_PMT_INSTR_USES_ALL.instrument_id

IBY_PMT_INSTR_USES_ALL.instrument_type = ‘BANKACCOUNT’

IBY_PMT_INSTR_USES_ALL.ext_pmt_party_id = IBY_EXTERNAL_PAYEES_ALL.ext_payee_id

IBY_EXTERNAL_PAYEES_ALL.payee_party_id = PO_VENDORS.party_id

IBY_EXTERNAL_PAYEES_ALL.party_site_id = AP_SUPPLIER_SITES_ALL.party_site_id

IBY_EXTERNAL_PAYEES_ALL.payment_function =  ‘PAYABLES_DISB’

IBY_EXTERNAL_PAYEES.supplier_site_id = AP_SUPPLIER_SITES_ALL.vendor_site_id

--Bank as Party

IBY_EXT_BANK_ACCOUNTS.bank_id = HZ_PARTIES.party_id(+)

IBY_EXT_BANK_ACCOUNTS.bank_id = HZ_ORGANIZATION_PROFILES.party_id(+)

IBY_EXT_BANK_ACCOUNTS.branch_id = CE_BANK_BRANCHES_V.branch_party_id(+)

--------------------------------------------------------------------
Sample Vendor Name      - 'XXABC Company Inc.'
       Vendor Site Code – 'DIGITAL INC’


Steps to Drill down to Bank Account info in R12
--------------------------------------------------------------------


1. Get the Supplier Party Id
    select * from ap_suppliers where vendor_name = 'XXABC Company Inc.';

    party_id 3310541
   

2. Get the Supplier Site Info
     select * from apps.ap_supplier_sites_all where vendor_id = 654980
     and org_id = 484;
   
     vendor_site_id 1342690
     party_site_id 2339546
     vendor_site_code 'DIGITAL INC’

3. Get the External Payee Info

     select * from apps.iby_external_payees_all where party_site_id = 2339546 and org_id = 484;
   
     ext_payee_id 3005139
     payee_party_id 3310541
     supplier_site_id 1342690

4. Get the Payment Instrument Info

     select * from apps.iby_pmt_instr_uses_all where ext_pmt_party_id = 3005139 and sysdate between nvl(start_date, sysdate) and nvl(end_date, sysdate);
     
     instrument_id 743177

5. Get the External Bank Account Info

    select * from apps.iby_ext_bank_accounts  where ext_bank_account_id = 743177;





-----------------------------------------------------------------------------------------------------------


Query used to get the active bank account info for a given Supplier Name, Org Id and Site Code. – R12

    SELECT accts.ext_bank_account_id,
                 accts.bank_account_name,
                 accts.masked_iban AS iban,
                 accts.currency_code,
                 uses.order_of_preference,
                 uses.start_date,
                 uses.end_date,
                 fc.name AS currency_name,
                 bank.party_name AS bank_name,
                 accts.masked_bank_account_num AS bank_account_number,
                 branch.bank_branch_name,
                 branch.branch_number,
                 bankProfile.bank_or_branch_number AS bank_number,
                 branch.eft_swift_code,
                 accts.bank_account_type
            FROM apps.IBY_PMT_INSTR_USES_ALL uses,
                 apps.IBY_EXTERNAL_PAYEES_ALL payee,
                 apps.IBY_EXT_BANK_ACCOUNTS accts,
                 apps.FND_CURRENCIES_VL fc,
                 apps.HZ_PARTIES bank,
                 apps.HZ_ORGANIZATION_PROFILES bankProfile,
                 apps.CE_BANK_BRANCHES_V branch,
                 apps.po_vendors pv,
                 apps.ap_supplier_sites_all sites
           WHERE     pv.vendor_name = 'XXABC Company Inc.'
                 and pv.vendor_id = sites.vendor_id
                 and sites.vendor_site_code = ‘DIGITAL INC’
                 and sites.org_id = 484
                 and uses.instrument_type = 'BANKACCOUNT'
                 AND payee.ext_payee_id = uses.ext_pmt_party_id
                 AND payee.payee_party_id = pv.party_id
                 AND payee.payment_function = 'PAYABLES_DISB'
                 AND payee.party_site_id = sites.party_site_id
                 AND payee.org_id = sites.org_id
                 AND payee.supplier_site_id = sites.vendor_site_id
                 AND uses.instrument_id = accts.ext_bank_account_id
                 AND fc.currency_code(+) = accts.currency_code
                 AND SYSDATE BETWEEN NVL (accts.start_date, SYSDATE)
                                 AND NVL (accts.end_date, SYSDATE)
                 AND SYSDATE BETWEEN NVL (uses.start_date, SYSDATE)
                                 AND NVL (uses.end_date, SYSDATE)                                
                 AND accts.bank_id = bank.party_id(+)
                 AND accts.bank_id = bankProfile.party_id(+)
                 AND accts.branch_id = branch.branch_party_id(+)
                 AND SYSDATE BETWEEN TRUNC(bankProfile.effective_start_date(+))  AND NVL(TRUNC(bankProfile.effective_end_date(+)),SYSDATE + 1)
 ORDER BY ORDER_OF_PREFERENCE ASC

Payment Batch Status Query

/* Formatted on 7/17/2014 8:04:22 PM (QP5 v5.115.810.9015) */
SELECT *
FROM (SELECT checkrun_id,
             checkrun_name,
             status checkrun_status,
             (SELECT payment_service_request_status
              FROM iby.iby_pay_service_requests
              WHERE calling_app_id = 200
                    AND call_app_pay_service_req_code = isc.checkrun_name)
                pay_service_req_status,
             (SELECT pia.payment_instruction_status
              FROM iby.iby_pay_instructions_all pia
              WHERE pay_admin_assigned_ref_code = isc.checkrun_name)
                instruction_status,
             last_update_date
      FROM ap.ap_inv_selection_criteria_all isc)
WHERE 1 = 1
--
-- these conditions will return payment batches in process
--
--AND checkrun_status                 NOT IN ('CANCELLED NO PAYMENTS','CANCELED','CONFIRMED')
--AND NVL(pay_service_req_status,'X') NOT IN ('COMPLETED', 'TERMINATED', 'CONFIRMED')
--AND NVL(instruction_status    ,'X') NOT IN ('TERMINATED')
ORDER BY last_update_date DESC;

Query to check Duplicates in iby_external_payees_all table

/* Formatted on 7/4/2014 10:42:56 AM (QP5 v5.115.810.9015) */
SELECT a.ext_payee_id,
       a.payee_party_id,
       a.payment_function,
       a.exclusive_payment_flag,
       a.party_site_id,
       a.supplier_site_id,
       a.org_id,
       a.org_type,
       a.default_payment_method_code,
       a.ece_tp_location_code,
       a.bank_charge_bearer,
       a.bank_instruction1_code,
       a.bank_instruction2_code,
       a.bank_instruction_details,
       a.payment_reason_code,
       a.payment_reason_comments,
       a.inactive_date,
       a.payment_text_message1,
       a.payment_text_message2,
       a.payment_text_message3,
       a.delivery_channel_code,
       a.payment_format_code,
       a.settlement_priority,
       a.remit_advice_delivery_method,
       a.remit_advice_email,
       a.remit_advice_fax
FROM iby_external_payees_all a
WHERE EXISTS
         (SELECT 'duplicates'
          FROM iby_external_payees_all b
          WHERE     a.payee_party_id = b.payee_party_id
                AND a.payment_function = b.payment_function
                AND NVL (a.party_site_id, '0') = NVL (b.party_site_id, '0')
                AND NVL (a.supplier_site_id, '0') =
                      NVL (b.supplier_site_id, '0')
                AND NVL (a.org_id, '0') = NVL (b.org_id, '0')
                AND NVL (a.org_type, '0') = NVL (b.org_type, '0')
                AND a.ext_payee_id <> b.ext_payee_id)
ORDER BY a.payee_party_id, a.last_update_date DESC;

Customer & Supplier Banks(External Banks) Technical Information in Oracle Apps

Suppliers and their Bank Details in Oracle Apps R12

In this post, I tried to show the relationship between Important Supplier tables, TCA Tables,Payment Tables with the help of a query. I hope it will be useful.

Table Introduction:

Ø  IBY_EXTERNAL_PAYEES_ALL :  supplier information and customer information

Ø  IBY_EXT_BANK_ACCOUNTS : external bank account information

Ø  IBY_PMT_INSTR_USES_ALL : Payment instruments assignments

Ø  AP_SUPPLIERS : Stores Supplier Information

Ø  AP_SUPPLIERS_SITES_ALL : Stores Supplier site Information     

Query:

/* Formatted on 6/23/2014 5:22:03 PM (QP5 v5.115.810.9015) */
SELECT /*Supplier Information*/
       aps.segment1 oracle_supplier_number,
       aps.vendor_id,
       aps.vendor_name supplier_name,
       aps.party_id supplier_party_id,
       iepa.remit_advice_fax remit_advice_fax,
       iepa.remit_advice_email remit_advice_email/* Supplier Site Information */
       ,
       assa.vendor_site_id,
       assa.party_site_id supplier_party_site_id,
       assa.vendor_site_code vendor_site_code,
       assa.pay_site_flag pay_site_flag,
       assa.purchasing_site_flag purchasing_site_flag,
       assa.rfq_only_site_flag rfq_only_site_flag/* Bank Information*/
       ,
       ieba.ext_bank_account_id,
       hp.party_name bank_party_name,
       ieba.bank_account_num bank_account_num,
       ieba.bank_account_name bank_account_name,
       ieba.country_code bank_acct_country_code,
       ieba.currency_code bank_acct_currency_code/* Bank Address */
       ,
       hp.address1 bank_address_line1,
       hp.address2 bank_address_line2,
       hp.address3 bank_address_line3,
       hp.city bank_address_city,
       hp.state bank_address_state,
       hp.postal_code bank_address_zip,
       hp.country bank_address_country/* Bank Branch Address */
       ,
       hp1.address1 branch_address_line1,
       hp1.address2 branch_address_line2,
       hp1.address3 branch_address_line3,
       hp1.city branch_address_city,
       hp1.state branch_address_state,
       hp1.postal_code branch_address_zip,
       hp1.country branch_address_country
FROM ap_supplier_sites_all assa,
     hz_parties hp,
     iby_ext_bank_accounts ieba,
     iby_external_payees_all iepa,
     iby_pmt_instr_uses_all ipiua,
     ap_suppliers aps,
     hz_parties hp1
WHERE     assa.vendor_site_id = iepa.supplier_site_id
      AND hp.party_id = ieba.bank_id
      AND ipiua.instrument_id = ieba.ext_bank_account_id
      AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
      AND assa.vendor_id = aps.vendor_id
      AND ieba.branch_id = hp1.party_id
      AND ipiua.instrument_type = 'BANKACCOUNT'
      AND ipiua.payment_flow = 'DISBURSEMENTS'
      AND ipiua.order_of_preference = 1;   

Suppliers and their Bank Details in Oracle Apps R12    

Let us go step by step and finally we can see the consolidated Query. All these queries were tested in R12.1.1 instance.

Query to Fetch Customer Data:

SELECT
      cust.party_name customer_name
    , cust_acct.cust_account_id
    , cust_acct.account_number
    , cust_uses.site_use_code
    , cust_loc.address1
    , cust_loc.address2
    , cust_loc.address3
    , cust_loc.address4
    , cust_loc.city
    , cust_loc.postal_code
FROM
      hz_parties             cust
    , hz_cust_accounts       cust_acct
    , hz_cust_acct_sites_all cust_site
    , hz_party_sites         party_site
    , hz_cust_site_uses_all  cust_uses
    , hz_locations           cust_loc
WHERE cust.party_id               = cust_acct.party_id
AND   cust_acct.cust_account_id   = cust_site.cust_account_id
AND   cust_site.cust_acct_site_id = cust_uses.cust_acct_site_id
AND   cust_site.party_site_id     = party_site.party_site_id
AND   party_site.location_id      = cust_loc.location_id
AND   cust.party_name             like '%&party_name%';

Query to Find the Bank Account id based on Customer Info

SELECT
    account.ext_bank_account_id -- Link to Bank and Branch Information
   ,acc_instr.instrument_id
   ,acc_instr.ext_pmt_party_id
   ,ext_payer.ext_payer_id
   ,ext_payer.cust_account_id  -- Link to Cust Account Info
   ,ext_payer.acct_site_use_id
FROM
    iby_ext_bank_accounts   account
  , iby_pmt_instr_uses_all  acc_instr
  , iby_external_payers_all ext_payer
WHERE 1= 1
AND account.ext_bank_account_id = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id  = ext_payer.ext_payer_id
AND ext_payer.cust_account_id   = '&id_from_previus_query'; 

Query to Find the Bank And Branch Information based on previous Query    

SELECT
     cust.party_name                   customer_Party_name
    ,cust.party_id                     customer_party_id
    ,bank.party_name                   bank_name
    ,bank_prof.home_country  
    ,account.bank_account_num
    ,account.bank_account_name
    ,branch.party_name                 branch_name
    ,branch_prof.bank_or_branch_number branch_number
FROM hz_parties               bank
   , hz_relationships         rel
   , hz_parties               branch
   , hz_organization_profiles bank_prof
   , hz_organization_profiles branch_prof
   , iby_ext_bank_accounts    account
   , iby_account_owners       acc_owner
   ,hz_parties                cust
WHERE 1=1
AND bank.party_id                    = rel.object_id
and bank.party_type                  = rel.object_type
AND rel.object_table_name            = 'HZ_PARTIES'
AND rel.relationship_code            = 'BRANCH_OF'
AND rel.subject_id                   = branch.party_id
AND rel.subject_type                 = branch.party_type
AND rel.subject_table_name           = 'HZ_PARTIES'
AND bank.party_id                    = bank_prof.party_id
AND branch.party_id                  = branch_prof.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_id
AND account.ext_bank_account_id      = acc_owner.ext_bank_account_id
AND acc_owner.account_owner_party_id = cust.party_id
AND account.ext_bank_account_id      ='&ext_bank_accout_id_frm_previous_query';

Consolidated Query to fetch Customer info, Customer Site info, Bank Info and Bank Branch Info:

SELECT
     cust.party_name customer_name
   , cust_acct.account_number
   , cust_acct.cust_account_id
   , cust_uses.site_use_code
   , cust_loc.address1
   , cust_loc.address2
   , cust_loc.address3
   , cust_loc.address4
   , cust_loc.city
   , cust_loc.postal_code
   , bank.party_name                   bank_name
   , bank_prof.home_country
   , branch.party_name                 branch_name
   , branch_prof.bank_or_branch_number branch_number
   , account.bank_account_num
   , account.bank_account_name
FROM hz_parties               bank
   , hz_relationships         rel
   , hz_parties               branch
   , hz_organization_profiles bank_prof
   , hz_organization_profiles branch_prof
   , iby_ext_bank_accounts    account
   , iby_external_payers_all  ext_payer
   , iby_pmt_instr_uses_all   acc_instr
   , hz_parties               cust
   , hz_cust_accounts         cust_acct
   , hz_cust_acct_sites_all   cust_site
   , hz_cust_site_uses_all    cust_uses
   , hz_party_sites           party_site
   , hz_locations             cust_loc
WHERE 1=1
AND bank.party_id                    = rel.object_id
and bank.party_type                  = rel.object_type
AND rel.object_table_name            = 'HZ_PARTIES'
AND rel.relationship_code            = 'BRANCH_OF'
AND rel.subject_id                   = branch.party_id
AND rel.subject_type                 = branch.party_type
AND rel.subject_table_name           = 'HZ_PARTIES'
AND bank.party_id                    = bank_prof.party_id
AND branch.party_id                  = branch_prof.party_id
AND cust_acct.cust_account_id        = cust_site.cust_account_id
AND cust_site.cust_acct_site_id      = cust_uses.cust_acct_site_id
AND party_site.party_id              = cust.party_id
AND party_site.party_site_id         = cust_site.party_site_id
AND party_site.location_id           = cust_loc.location_id
AND cust.party_id                    = cust_acct.party_id
AND bank.party_id                    = account.bank_id
AND branch.party_id                  = account.branch_id
AND account.ext_bank_account_id      = acc_instr.instrument_id
AND acc_instr.ext_pmt_party_id       = ext_payer.ext_payer_id
AND ext_payer.cust_account_id        = cust_acct.cust_account_id
AND cust_uses.site_use_id            = ext_payer.acct_site_use_id
AND cust.party_name             like '%$Party_name%';