--Oracle R12 Vendor Extract with Bank PAN GST
SELECT
(
SELECT
hou.name
FROM
apps.hr_operating_units hou
WHERE
1 = 1
AND hou.organization_id = aps.org_id
) ou_name,
ass.segment1 supplier_number,
ass.vendor_type_lookup_code supplier_type,
ass.vendor_name supplier_name,
ass.vendor_name_alt supplier_name_alt,
decode(ass.end_date_active, NULL, 'ACTIVE', 'IN ACTIVE') "Supplier active code",
aps.vendor_site_code supplier_site_code,
aps.address_line1,
aps.address_line2,
aps.address_line3,
aps.address_line4,
aps.city,
aps.state,
aps.zip post_code,
aps.pay_group_lookup_code,
iepa.default_payment_method_code site_payment_method,
t.name site_terms_name,
branch.bank_name "bank_name",
branch.bank_branch_name bank_branch_name,
accts.bank_account_name bank_account_name,
accts.bank_account_num bank_account_number,
branch.eft_swift_code ifsc_code,
(
SELECT
registration_number
FROM
jai_party_reg_lines jprl,
jai_party_regs jpr
WHERE jprl.EFFECTIVE_TO is null
AND registration_type_code = 'PAN'
AND jprl.party_reg_id = jpr.party_reg_id
AND party_id = aps.vendor_id
AND party_site_id = aps.vendor_site_id
) pan_number,
(
SELECT
registration_number
FROM
jai_party_reg_lines jprl,
jai_party_regs jpr
WHERE jprl.EFFECTIVE_TO is null
AND jprl.registration_type_code IN ( 'GSTN', 'GSTIN' )
AND jprl.party_reg_id = jpr.party_reg_id
AND party_id = aps.vendor_id
AND party_site_id = aps.vendor_site_id
) gstin_number,
uses.order_of_preference,
uses.end_date bank_end_date
FROM
apps.ap_supplier_sites_all aps,
apps.ap_suppliers ass,
apps.ap_terms t,
apps.iby_external_payees_all iepa,
apps.iby_pmt_instr_uses_all uses,
apps.iby_account_owners owners,
apps.iby_ext_bank_accounts accts,
apps.ce_bank_branches_v branch
WHERE
1 = 1--Aps.ORG_ID=82
AND ass.end_date_active IS NULL
AND aps.inactive_date IS NULL
AND aps.terms_id = t.term_id
--AND ass.creation_date like sysdate
AND ass.vendor_id = aps.vendor_id
AND owners.ext_bank_account_id = accts.ext_bank_account_id (+)
AND owners.ext_bank_account_id (+) = uses.instrument_id
AND iepa.ext_payee_id = uses.ext_pmt_party_id (+)
AND iepa.payee_party_id = owners.account_owner_party_id (+)
--and uses.end_date is null
--AND uses.END_DATE is not null
AND iepa.supplier_site_id (+) = aps.vendor_site_id
AND branch.branch_party_id (+) = accts.branch_id
AND ass.vendor_type_lookup_code = 'VENDOR' --IN ('VENDOR', 'LANDLORD')