Oracle EBS GST-Tax Authority

Oracle EBS GST-Tax Authority
Configuration:Tax Authority,
Responsibility:Oracle Payables,
Navigation:Suppliers ->Entry

Tables:-
AP_SUPPLIERS
FND_LOOKUP_VALUES
AP_SUPPLIER_SITES_ALL
HZ_LOCATIONS
FND_TERRITORIES_VL
HZ_PARTY_SITES
HR_OPERATING_UNITS

SQL Example:-

SELECT
asp.VENDOR_NAME "Supplier Name"
,asp.SEGMENT1 "Supplier Number"
,flv.MEANING  "Type"
,hps.PARTY_SITE_NUMBER  "Site Number"
,ftv.TERRITORY_SHORT_NAME "Country"
,hl.ADDRESS1
,hl.ADDRESS2
,hl.ADDRESS3
,hl.ADDRESS4
,hl.CITY
,hl.COUNTY
,hl.STATE
,hl.PROVINCE
,hl.POSTAL_CODE
,hps.PARTY_SITE_NAME  "Address Name"
,hps.ADDRESSEE 
,decode(hps.status,'A','Active','I','Inactive','M') "Status"
,hps.LANGUAGE
,hps.ATTRIBUTE_CATEGORY "Context Value"
,(select DISTINCT NOTES
FROM pos_address_notes
WHERE PARTY_SITE_ID=hps.PARTY_SITE_ID) "Communication Details"
,(SELECT DISTINCT HCP2.PHONE_AREA_CODE
FROM   APPS.HZ_RELATIONSHIPS HRR2,
APPS.HZ_CONTACT_POINTS HCP2,
APPS.AP_SUPPLIERS ASS2, 
APPS.AP_SUPPLIER_SITES_ALL  assa3,
      APPS.HZ_PARTIES  hp3,
APPS.HR_OPERATING_UNITS     hou3
WHERE 1=1
AND HRR2.SUBJECT_TYPE='ORGANIZATION'
AND HRR2.RELATIONSHIP_CODE='CONTACT'
AND HRR2.STATUS='A'
AND HCP2.OWNER_TABLE_NAME='HZ_PARTIES'
AND HRR2.SUBJECT_ID=ASS2.PARTY_ID
AND HCP2.PRIMARY_FLAG='Y'
AND HCP2.STATUS='A'
AND HCP2.CONTACT_POINT_TYPE='PHONE'
AND ass2.segment1=asp.segment1
AND HCP2.OWNER_TABLE_ID=HRR2.PARTY_ID
AND HRR2.object_id= hps.party_id
AND ass2.vendor_id = assa3.vendor_id) "Phone Area Code"
,(SELECT DISTINCT HCP1.PHONE_NUMBER
FROM   APPS.HZ_RELATIONSHIPS HRR1,
APPS.HZ_CONTACT_POINTS HCP1,
APPS.AP_SUPPLIERS ASS1, 
APPS.AP_SUPPLIER_SITES_ALL  assa2,
APPS.HZ_PARTIES  hp2,
APPS.HR_OPERATING_UNITS     hou2
WHERE 1=1
AND HRR1.SUBJECT_TYPE='ORGANIZATION'
AND HRR1.RELATIONSHIP_CODE='CONTACT'
AND HRR1.STATUS='A'
AND HCP1.OWNER_TABLE_NAME='HZ_PARTIES'
AND HRR1.SUBJECT_ID=ASS1.PARTY_ID
AND HCP1.PRIMARY_FLAG='Y'
AND HCP1.STATUS='A'
AND HCP1.CONTACT_POINT_TYPE='PHONE'
AND ass1.segment1=asp.segment1
AND HCP1.OWNER_TABLE_ID=HRR1.PARTY_ID
AND HRR1.object_id=hps.party_id
AND ass1.vendor_id = assa2.vendor_id) "Phone Number"
,(SELECT DISTINCT HCP.PHONE_AREA_CODE 
FROM HZ_RELATIONSHIPS HR
,HZ_CONTACT_POINTS HCP
,AP_SUPPLIERS ASS
WHERE 1=1
AND HR.SUBJECT_TYPE='ORGANIZATION'
AND HR.RELATIONSHIP_CODE='CONTACT'
AND HR.STATUS='A'
AND HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND HR.SUBJECT_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='FAX'
AND ASS.PARTY_ID=asp.PARTY_ID) "Fax Area Code"
,(SELECT DISTINCT HCP.PHONE_NUMBER
FROM HZ_RELATIONSHIPS HR
,HZ_CONTACT_POINTS HCP
,AP_SUPPLIERS ASS
WHERE 1=1
AND HR.SUBJECT_TYPE='ORGANIZATION'
AND HR.RELATIONSHIP_CODE='CONTACT'
AND HR.STATUS='A'
AND HCP.OWNER_TABLE_NAME='HZ_PARTIES'
AND HR.SUBJECT_ID=ASS.PARTY_ID
AND HCP.OWNER_TABLE_ID=HR.PARTY_ID
AND HCP.PRIMARY_FLAG='Y'
AND HCP.STATUS='A'
AND HCP.CONTACT_POINT_TYPE='PHONE'
AND HCP.PHONE_LINE_TYPE='FAX'
AND ASS.PARTY_ID=asp.PARTY_ID)  "Fax Number"
,(SELECT DISTINCT HCP1.PHONE_NUMBER
FROM   APPS.HZ_RELATIONSHIPS HRR1,
APPS.HZ_CONTACT_POINTS HCP1,
APPS.AP_SUPPLIERS ASS1, 
APPS.AP_SUPPLIER_SITES_ALL  assa2,
APPS.HZ_PARTIES  hp2,
APPS.HR_OPERATING_UNITS     hou2
WHERE 1=1
AND HRR1.SUBJECT_TYPE='ORGANIZATION'
AND HRR1.RELATIONSHIP_CODE='CONTACT'
AND HRR1.STATUS='A'
AND HCP1.OWNER_TABLE_NAME='HZ_PARTIES'
AND HRR1.SUBJECT_ID=ASS1.PARTY_ID
AND HCP1.PRIMARY_FLAG='Y'
AND HCP1.STATUS='A'
AND HCP1.CONTACT_POINT_TYPE='PHONE'
AND ass1.segment1=asp.segment1
AND HCP1.OWNER_TABLE_ID=HRR1.PARTY_ID
AND HRR1.object_id= hps.party_id
AND ass1.vendor_id = assa2.vendor_id) "Email Address"
,(SELECT DECODE(hpsu.site_use_type, null, 'N', 'Y')
FROM hz_party_site_uses  hpsu
WHERE  hps.PARTY_SITE_ID=hpsu.PARTY_SITE_ID
AND hpsu.SITE_USE_TYPE='PAY') "Purchasing"
,(SELECT DECODE(hpsu1.site_use_type, null, 'N', 'Y')
  FROM hz_party_site_uses  hpsu1
WHERE  hps.PARTY_SITE_ID=hpsu1.PARTY_SITE_ID 
AND hpsu1.SITE_USE_TYPE='PURCHASING')   "Payment"
,(SELECT DECODE(HPSU2.SITE_USE_TYPE, NULL, 'N', 'Y')
  FROM hz_party_site_uses  hpsu2
WHERE  hps.PARTY_SITE_ID=hpsu2.PARTY_SITE_ID
AND hpsu2.SITE_USE_TYPE='RFQ') "RFQ Only"
,'Y' As "Select"
,hps.PARTY_SITE_NAME "Site Name"
,hou.NAME  "Opearting Unit"
,NULL AS "Tax Reportable"
FROM
APPS.AP_SUPPLIERS asp,
APPS.FND_LOOKUP_VALUES flv,
APPS.AP_SUPPLIER_SITES_ALL assa,
APPS.HZ_LOCATIONS  hl,
APPS.FND_TERRITORIES_VL  ftv,
APPS.HZ_PARTY_SITES  hps,
APPS.HR_OPERATING_UNITS  hou
WHERE 1=1
AND asp.VENDOR_TYPE_LOOKUP_CODE=flv.LOOKUP_CODE
AND flv.LANGUAGE='US'
AND flv.LOOKUP_TYPE='VENDOR TYPE'
AND asp.VENDOR_NAME='GST Tax Authority'
AND asp.vendor_id=assa.vendor_id
AND assa.location_id=hl.location_id
AND hl.COUNTRY=ftv.TERRITORY_CODE
AND assa.location_id=hps.location_id
AND assa.org_id=hou.ORGANIZATION_ID
AND hou.name= '&&';