---Oracle R12 Supplier/Vendor extraction script for India GST Tax
SELECT (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
ap.segment1 vendor_code,
ap.vendor_name Suppliers_Name,
aps.vendor_site_code Supplier_Site_Code,
ap.vendor_type_lookup_code Type_Code,
aps.address_line1,
aps.address_line2,
aps.address_line3,
aps.city,
aps.state,
aps.zip,
aps.Country,
apc.area_code,
apc.phone,
apc.fax_area_code,
apc.fax,
apc.email_address,
ap.payment_currency_code currency_code,
ap.offset_tax_flag offset_tax_flag_header,
aps.auto_tax_calc_flag,
aps.offset_tax_flag offset_tax_flag_line,
aps.attribute1
,(SELECT b.registration_number
FROM ja.jai_party_regs a, ja.jai_party_reg_lines b
WHERE a.org_id = aps.org_id
AND a.party_id = aps.vendor_id
AND a.party_site_id = aps.vendor_site_id
AND a.party_reg_id = b.party_reg_id
AND a.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND NVL(a.SUPPLIER_FLAG,'N') = 'Y' AND NVL(SITE_FLAG,'N') = 'Y'
AND b.REGISTRATION_TYPE_CODE = 'GSTIN'
AND SYSDATE BETWEEN b.effective_from AND NVL (b.effective_to, SYSDATE) ) GSTIN_NUM
,NVL((SELECT b.SECONDARY_REGISTRATION_NUMBER
FROM ja.jai_party_regs a, ja.jai_party_reg_lines b
WHERE a.org_id = aps.org_id
AND a.party_id = aps.vendor_id
AND a.party_site_id = aps.vendor_site_id
AND a.party_reg_id = b.party_reg_id
AND a.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND NVL(a.SUPPLIER_FLAG,'N') = 'Y' AND NVL(SITE_FLAG,'N') = 'Y'
AND b.SEC_REGISTRATION_TYPE_CODE = 'PAN'
AND SYSDATE BETWEEN b.effective_from AND NVL (b.effective_to, SYSDATE)),(SELECT b.registration_number
FROM ja.jai_party_regs a, ja.jai_party_reg_lines b
WHERE a.org_id = aps.org_id
AND a.party_id = aps.vendor_id
AND a.party_site_id = aps.vendor_site_id
AND a.party_reg_id = b.party_reg_id
AND a.PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
AND NVL(a.SUPPLIER_FLAG,'N') = 'Y' AND NVL(SITE_FLAG,'N') = 'Y'
AND b.REGISTRATION_TYPE_CODE = 'PAN'
AND SYSDATE BETWEEN b.effective_from AND NVL (b.effective_to, SYSDATE) ) )PAN_NUM
FROM ap_suppliers ap,
ap_supplier_sites_all aps,
ap_supplier_contacts apc
WHERE ap.vendor_id = aps.vendor_id
AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
AND ap.VENDOR_TYPE_LOOKUP_CODE <> 'EMPLOYEE'
--AND aps.vendor_site_code NOT IN 'OFFICE'
AND ap.enabled_flag ='Y'
ORDER BY ap.vendor_name,ap.segment1