Oracle EBS - SQL Query - Supplier Site associated Bank Details

Subject: Business need to extract the supplier site level associated bank details.

--SQL:

SELECT

    'Suppplier Site'    "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    iban,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv,

    apps.ap_supplier_sites_all   ass

WHERE

        1 = 1

    AND aps.vendor_id = ass.vendor_id

    AND ass.org_id = :p_org_id

    AND iepa.payee_party_id = aps.party_id

    AND iepa.party_site_id IS NOT NULL

    AND iepa.supplier_site_id IS NOT NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

Oracle EBS - SQL Query - Supplier Header associated Bank Details

Subject: Business required to get the supplier information where Supplier Header associated bank details.

--SQL:

 SELECT

    'Suppplier Header'  "Banking_Info",

    aps.vendor_name,

    ieba.bank_account_num,

    ieba.bank_account_name,

    cbv.bank_name,

    cbv.address_line1   bank_address_1,

    cbv.country         bank_country,

    cbv.city            bank_city,

    cbbv.bank_branch_name,

    cbbv.address_line1  branch_address_1,

    cbbv.city           branch_city,

    cbbv.country        branch_country,

    cbbv.branch_number,

    cbbv.eft_swift_code bic,

    foreign_payment_use_flag

FROM

    apps.ap_suppliers            aps,

    apps.iby_external_payees_all iepa,

    apps.iby_pmt_instr_uses_all  ipiua,

    apps.iby_ext_bank_accounts   ieba,

    apps.ce_banks_v              cbv,

    apps.ce_bank_branches_v      cbbv

WHERE

        1 = 1

    AND aps.vendor_id IN (

        SELECT

            vendor_id

        FROM

            apps.ap_supplier_sites_all ass

        WHERE

            org_id = nvl(:p_org_id, org_id)

    )

    AND iepa.payee_party_id = aps.party_id

    AND party_site_id IS NULL

    AND supplier_site_id IS NULL

    AND ipiua.ext_pmt_party_id (+) = iepa.ext_payee_id

    AND ieba.ext_bank_account_id (+) = ipiua.instrument_id

    AND ieba.bank_id = cbv.bank_party_id (+)

    AND ieba.branch_id = cbbv.branch_party_id (+)

--    AND aps.vendor_id in (32323,34962)

    AND ieba.bank_account_num IS NOT NULL

ORDER BY

    aps.vendor_name,

    ieba.bank_account_num

Oracle EBS - SQL Query to get the employee default expense account

 

SELECT

    concatenated_segments

FROM

    apps.gl_code_combinations_kfv

WHERE

    code_combination_id IN (

        SELECT DISTINCT

            default_code_comb_id

        FROM

            apps.per_all_assignments_f

            WHERE  effective_end_date >= sysdate

        AND

            person_id IN (

                SELECT

                    person_id

                FROM

                    apps.per_all_people_f

                WHERE

                    effective_end_date >= sysdate

                    and person_id LIKE '&person_id'

            )

    );

Oracle EBS - SQL Query to get custom top directory path

select  

variable_name, value

from fnd_env_context

where variable_name like '%CUST%_TOP' 

GROUP BY variable_name, value

order by variable_name;