TCA.
--------
5:11 PM 8/26/2013
---------------------
/*********************************Customer Name,Account,Site(Address) and Purpose Details***********************/
SELECT CUST.PARTY_NAME CUSTOMER_NAME
,CUST.PARTY_TYPE CUSTOMER_TYPE
,CUST.PARTY_NUMBER REGISTRY_ID
,'CUSTOMER ACCOUNT'
,HCA.ACCOUNT_NUMBER
,HCA.ACCOUNT_NAME
,HCA.ATTRIBUTE5 CUSTOMER_STATUS
,'ACCOUNT SITE'
,HPS.PARTY_SITE_NUMBER SITE_NUMBER
,HL.ADDRESS1
,HL.ADDRESS2
,HL.ADDRESS3
,HL.CITY
,HL.STATE
,HL.POSTAL_CODE
,FT.NLS_TERRITORY COUNTRY
,'PURPOSE'
,HOU.NAME OPERATING_UNIT
,CPS1.SITE_USE_CODE
,CPS1.LOCATION
,CPS1.PRIMARY_FLAG
,CPS1.STATUS
,CPS2.SITE_USE_CODE SITE_USE_COD
,CPS2.LOCATION SHIP_LOC
,CPS2.PRIMARY_FLAG PRI_FLAG
,CPS2.STATUS SHIP_STATUS
,CPS1.LOCATION BILL_TO_LOCATION
FROM HZ_PARTIES CUST
,HZ_CUST_ACCOUNTS HCA
,HZ_PARTY_SITES HPS
,HZ_LOCATIONS HL
,FND_TERRITORIES FT
,HR_OPERATING_UNITS HOU
,(SELECT CUST_ACCT_SITE_ID,CUST_ACCOUNT_ID FROM HZ_CUST_ACCT_SITES_ALL WHERE PARTY_SITE_ID=226632) CPS
,(SELECT CUST_ACCT_SITE_ID,SITE_USE_CODE,PRIMARY_FLAG,STATUS,LOCATION,ORG_ID FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_CODE='BILL_TO'
AND CUST_ACCT_SITE_ID=11275) CPS1
,(SELECT CUST_ACCT_SITE_ID,SITE_USE_CODE,PRIMARY_FLAG,STATUS,LOCATION,ORG_ID FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_CODE='SHIP_TO'
AND CUST_ACCT_SITE_ID=11275) CPS2
WHERE 1=1
AND CUST.PARTY_ID=412679
AND CUST.PARTY_ID=HCA.PARTY_ID
AND CUST.PARTY_ID=HPS.PARTY_ID
AND HPS.LOCATION_ID=HL.LOCATION_ID
AND HL.COUNTRY=FT.TERRITORY_CODE
AND HCA.CUST_ACCOUNT_ID=CPS.CUST_ACCOUNT_ID
AND CPS.CUST_ACCT_SITE_ID=CPS1.CUST_ACCT_SITE_ID
AND CPS1.ORG_ID=HOU.ORGANIZATION_ID
AND CPS.CUST_ACCT_SITE_ID=CPS2.CUST_ACCT_SITE_ID
/***********************************Supplier and His Contact Details*************************************/
11:28 PM 8/26/2013
-----------------------
SELECT HPUA.PARTY_USAGE_CODE PARTY_TYPE_CODE
,AS1.VENDOR_NAME SUPPLIER_NAME
,HPS.PARTY_TYPE
,AS1.SEGMENT1 SUP_NUM
,HPS.PARTY_NUMBER REGISTRY_ID
,HPS.KNOWN_AS ALIAS_NAME
,'Contact-Person'
,HPUA1.PARTY_USAGE_CODE
,SUP.PARTY_NAME
,SUP.PERSON_PRE_NAME_ADJUNCT CONTACT_TITLE
,SUP.PERSON_FIRST_NAME
,SUP.PERSON_MIDDLE_NAME
,SUP.PERSON_LAST_NAME
,HOC.JOB_TITLE
,HOC.CONTACT_NUMBER
,EMAIL.EMAIL_ADDRESS EMAIL
,DECODE(PHN.PHONE_LINE_TYPE,'GEN','PHONE',PHN.PHONE_LINE_TYPE) PHONE_TYPE
,PHN.PHONE_AREA_CODE
,PHN.PHONE_NUMBER
,PHN.PHONE_EXTENSION
,FAX.PHONE_LINE_TYPE
,FAX.PHONE_AREA_CODE FAX_CODE
,FAX.PHONE_NUMBER FAX_NUMBER
,'Contact-Address'
,HPSS.PARTY_SITE_NUMBER
,ASSA.VENDOR_SITE_CODE SITE_NAME
,HL.ADDRESS1
,HL.ADDRESS2
,HL.ADDRESS3
,HL.ADDRESS4
,HL.CITY
,HL.STATE
,HL.POSTAL_CODE
,FT.NLS_TERRITORY COUNTRY
FROM AP_SUPPLIERS AS1
,HZ_PARTIES HPS
,AP_SUPPLIER_SITES_ALL ASSA
,HZ_PARTY_USG_ASSIGNMENTS HPUA
,(SELECT PARTY_ID,PARTY_USAGE_CODE FROM HZ_PARTY_USG_ASSIGNMENTS WHERE PARTY_ID=412687 AND OWNER_TABLE_ID IS NULL) HPUA1
,HZ_PARTY_SITES HPSS
,HZ_RELATIONSHIPS REL
,HZ_PARTIES SUP
,AP_SUPPLIER_CONTACTS ASC1
,HZ_ORG_CONTACTS HOC
,(SELECT OWNER_TABLE_ID,EMAIL_ADDRESS FROM HZ_CONTACT_POINTS WHERE CONTACT_POINT_TYPE='EMAIL') EMAIL
,(SELECT OWNER_TABLE_ID,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_LINE_TYPE,PHONE_EXTENSION FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_TYPE='PHONE' AND PHONE_LINE_TYPE='GEN') PHN
,(SELECT OWNER_TABLE_ID,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_LINE_TYPE FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_TYPE='PHONE' AND PHONE_LINE_TYPE='FAX') FAX
,HZ_LOCATIONS HL
,FND_TERRITORIES FT
WHERE 1=1
AND AS1.SEGMENT1=26
AND AS1.VENDOR_ID=ASSA.VENDOR_ID
AND AS1.PARTY_ID=HPS.PARTY_ID
AND AS1.PARTY_ID=HPUA.PARTY_ID
AND REL.SUBJECT_ID=HPS.PARTY_ID
AND SUP.PARTY_ID=REL.OBJECT_ID
AND HPSS.PARTY_ID=REL.PARTY_ID
AND HPSS.LOCATION_ID=HL.LOCATION_ID
AND ASSA.PARTY_SITE_ID=ASC1.ORG_PARTY_SITE_ID
AND ASC1.PER_PARTY_ID=SUP.PARTY_ID
AND ASC1.RELATIONSHIP_ID=HOC.PARTY_RELATIONSHIP_ID
AND ASC1.REL_PARTY_ID=EMAIL.OWNER_TABLE_ID(+)
AND ASC1.REL_PARTY_ID=PHN.OWNER_TABLE_ID(+)
AND ASC1.REL_PARTY_ID=FAX.OWNER_TABLE_ID(+)
AND HL.COUNTRY=FT.TERRITORY_CODE
/******************************************Query Bank,Branch and Account Details************************************/
9:16 AM 8/28/2013
----------------------
SELECT BN.PARTY_NAME BANK,BN.PARTY_ID BANK_ID,HPUA.PARTY_USAGE_CODE TYPE,BR.PARTY_NAME BRANCH
,CBA.BANK_ACCOUNT_NAME,CBA.SHORT_ACCOUNT_NAME,CBA.BANK_ACCOUNT_NUM
,CBA.MASKED_ACCOUNT_NUM,AH.PERSON_PRE_NAME_ADJUNCT TITLE,AH.PERSON_FIRST_NAME FIRST_NAME
,AH.PERSON_MIDDLE_NAME MID_NAME,AH.PERSON_LAST_NAME LAST_NAME,AH.PERSON_NAME_SUFFIX SUFFIX
,HL.ADDRESS1,HL.ADDRESS2,HL.CITY,HL.STATE,HL.POSTAL_CODE,FT.NLS_TERRITORY COUNTRY
,HOC.CONTACT_NUMBER,HOC.JOB_TITLE
,EMAIL.EMAIL_ADDRESS EMAIL
,PHN.PHONE_AREA_CODE,PHN.PHONE_NUMBER,PHN.PHONE_COUNTRY_CODE,PHN.PHONE_EXTENSION
FROM
HZ_PARTIES BN
,HZ_PARTY_USG_ASSIGNMENTS HPUA
,HZ_RELATIONSHIPS HREL
,HZ_PARTIES BR
,CE_BANK_ACCOUNTS CBA
,HZ_PARTIES AH
,HZ_RELATIONSHIPS HREL1
,HZ_PARTY_SITES HPS
,HZ_LOCATIONS HL
,FND_TERRITORIES FT
,HZ_ORG_CONTACTS HOC
,(SELECT OWNER_TABLE_ID,EMAIL_ADDRESS FROM HZ_CONTACT_POINTS WHERE CONTACT_POINT_TYPE='EMAIL') EMAIL
,(SELECT OWNER_TABLE_ID,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_COUNTRY_CODE,PHONE_EXTENSION FROM HZ_CONTACT_POINTS WHERE PHONE_LINE_TYPE='GEN') PHN
WHERE 1=1
AND BN.PARTY_ID=413679
AND BN.PARTY_NAME='ICICI Bank'
AND HPUA.PARTY_USAGE_CODE='BANK'
AND BN.PARTY_ID=HPUA.PARTY_ID
AND HREL.OBJECT_TYPE='ORGANIZATION'
AND HREL.SUBJECT_ID=BN.PARTY_ID
AND HREL.OBJECT_ID=BR.PARTY_ID
AND BN.PARTY_ID=CBA.BANK_ID
AND BR.PARTY_ID=CBA.BANK_BRANCH_ID
AND BN.PARTY_ID=HREL1.SUBJECT_ID
AND HREL1.OBJECT_ID=AH.PARTY_ID
--AND HREL1.OBJECT_TYPE='PERSON'
AND AH.PARTY_NUMBER=57756
AND HREL1.PARTY_ID=HPS.PARTY_ID
AND HPS.LOCATION_ID=HL.LOCATION_ID
AND FT.TERRITORY_CODE=HL.COUNTRY
AND HREL1.RELATIONSHIP_ID=HOC.PARTY_RELATIONSHIP_ID
AND HREL1.PARTY_ID=EMAIL.OWNER_TABLE_ID
AND HREL1.PARTY_ID=PHN.OWNER_TABLE_ID
AND HREL1.SUBJECT_ID=AH.PARTY_ID
/*************************************************************************************************************/
--------
5:11 PM 8/26/2013
---------------------
/*********************************Customer Name,Account,Site(Address) and Purpose Details***********************/
SELECT CUST.PARTY_NAME CUSTOMER_NAME
,CUST.PARTY_TYPE CUSTOMER_TYPE
,CUST.PARTY_NUMBER REGISTRY_ID
,'CUSTOMER ACCOUNT'
,HCA.ACCOUNT_NUMBER
,HCA.ACCOUNT_NAME
,HCA.ATTRIBUTE5 CUSTOMER_STATUS
,'ACCOUNT SITE'
,HPS.PARTY_SITE_NUMBER SITE_NUMBER
,HL.ADDRESS1
,HL.ADDRESS2
,HL.ADDRESS3
,HL.CITY
,HL.STATE
,HL.POSTAL_CODE
,FT.NLS_TERRITORY COUNTRY
,'PURPOSE'
,HOU.NAME OPERATING_UNIT
,CPS1.SITE_USE_CODE
,CPS1.LOCATION
,CPS1.PRIMARY_FLAG
,CPS1.STATUS
,CPS2.SITE_USE_CODE SITE_USE_COD
,CPS2.LOCATION SHIP_LOC
,CPS2.PRIMARY_FLAG PRI_FLAG
,CPS2.STATUS SHIP_STATUS
,CPS1.LOCATION BILL_TO_LOCATION
FROM HZ_PARTIES CUST
,HZ_CUST_ACCOUNTS HCA
,HZ_PARTY_SITES HPS
,HZ_LOCATIONS HL
,FND_TERRITORIES FT
,HR_OPERATING_UNITS HOU
,(SELECT CUST_ACCT_SITE_ID,CUST_ACCOUNT_ID FROM HZ_CUST_ACCT_SITES_ALL WHERE PARTY_SITE_ID=226632) CPS
,(SELECT CUST_ACCT_SITE_ID,SITE_USE_CODE,PRIMARY_FLAG,STATUS,LOCATION,ORG_ID FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_CODE='BILL_TO'
AND CUST_ACCT_SITE_ID=11275) CPS1
,(SELECT CUST_ACCT_SITE_ID,SITE_USE_CODE,PRIMARY_FLAG,STATUS,LOCATION,ORG_ID FROM HZ_CUST_SITE_USES_ALL
WHERE SITE_USE_CODE='SHIP_TO'
AND CUST_ACCT_SITE_ID=11275) CPS2
WHERE 1=1
AND CUST.PARTY_ID=412679
AND CUST.PARTY_ID=HCA.PARTY_ID
AND CUST.PARTY_ID=HPS.PARTY_ID
AND HPS.LOCATION_ID=HL.LOCATION_ID
AND HL.COUNTRY=FT.TERRITORY_CODE
AND HCA.CUST_ACCOUNT_ID=CPS.CUST_ACCOUNT_ID
AND CPS.CUST_ACCT_SITE_ID=CPS1.CUST_ACCT_SITE_ID
AND CPS1.ORG_ID=HOU.ORGANIZATION_ID
AND CPS.CUST_ACCT_SITE_ID=CPS2.CUST_ACCT_SITE_ID
/***********************************Supplier and His Contact Details*************************************/
11:28 PM 8/26/2013
-----------------------
SELECT HPUA.PARTY_USAGE_CODE PARTY_TYPE_CODE
,AS1.VENDOR_NAME SUPPLIER_NAME
,HPS.PARTY_TYPE
,AS1.SEGMENT1 SUP_NUM
,HPS.PARTY_NUMBER REGISTRY_ID
,HPS.KNOWN_AS ALIAS_NAME
,'Contact-Person'
,HPUA1.PARTY_USAGE_CODE
,SUP.PARTY_NAME
,SUP.PERSON_PRE_NAME_ADJUNCT CONTACT_TITLE
,SUP.PERSON_FIRST_NAME
,SUP.PERSON_MIDDLE_NAME
,SUP.PERSON_LAST_NAME
,HOC.JOB_TITLE
,HOC.CONTACT_NUMBER
,EMAIL.EMAIL_ADDRESS EMAIL
,DECODE(PHN.PHONE_LINE_TYPE,'GEN','PHONE',PHN.PHONE_LINE_TYPE) PHONE_TYPE
,PHN.PHONE_AREA_CODE
,PHN.PHONE_NUMBER
,PHN.PHONE_EXTENSION
,FAX.PHONE_LINE_TYPE
,FAX.PHONE_AREA_CODE FAX_CODE
,FAX.PHONE_NUMBER FAX_NUMBER
,'Contact-Address'
,HPSS.PARTY_SITE_NUMBER
,ASSA.VENDOR_SITE_CODE SITE_NAME
,HL.ADDRESS1
,HL.ADDRESS2
,HL.ADDRESS3
,HL.ADDRESS4
,HL.CITY
,HL.STATE
,HL.POSTAL_CODE
,FT.NLS_TERRITORY COUNTRY
FROM AP_SUPPLIERS AS1
,HZ_PARTIES HPS
,AP_SUPPLIER_SITES_ALL ASSA
,HZ_PARTY_USG_ASSIGNMENTS HPUA
,(SELECT PARTY_ID,PARTY_USAGE_CODE FROM HZ_PARTY_USG_ASSIGNMENTS WHERE PARTY_ID=412687 AND OWNER_TABLE_ID IS NULL) HPUA1
,HZ_PARTY_SITES HPSS
,HZ_RELATIONSHIPS REL
,HZ_PARTIES SUP
,AP_SUPPLIER_CONTACTS ASC1
,HZ_ORG_CONTACTS HOC
,(SELECT OWNER_TABLE_ID,EMAIL_ADDRESS FROM HZ_CONTACT_POINTS WHERE CONTACT_POINT_TYPE='EMAIL') EMAIL
,(SELECT OWNER_TABLE_ID,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_LINE_TYPE,PHONE_EXTENSION FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_TYPE='PHONE' AND PHONE_LINE_TYPE='GEN') PHN
,(SELECT OWNER_TABLE_ID,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_LINE_TYPE FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_TYPE='PHONE' AND PHONE_LINE_TYPE='FAX') FAX
,HZ_LOCATIONS HL
,FND_TERRITORIES FT
WHERE 1=1
AND AS1.SEGMENT1=26
AND AS1.VENDOR_ID=ASSA.VENDOR_ID
AND AS1.PARTY_ID=HPS.PARTY_ID
AND AS1.PARTY_ID=HPUA.PARTY_ID
AND REL.SUBJECT_ID=HPS.PARTY_ID
AND SUP.PARTY_ID=REL.OBJECT_ID
AND HPSS.PARTY_ID=REL.PARTY_ID
AND HPSS.LOCATION_ID=HL.LOCATION_ID
AND ASSA.PARTY_SITE_ID=ASC1.ORG_PARTY_SITE_ID
AND ASC1.PER_PARTY_ID=SUP.PARTY_ID
AND ASC1.RELATIONSHIP_ID=HOC.PARTY_RELATIONSHIP_ID
AND ASC1.REL_PARTY_ID=EMAIL.OWNER_TABLE_ID(+)
AND ASC1.REL_PARTY_ID=PHN.OWNER_TABLE_ID(+)
AND ASC1.REL_PARTY_ID=FAX.OWNER_TABLE_ID(+)
AND HL.COUNTRY=FT.TERRITORY_CODE
/******************************************Query Bank,Branch and Account Details************************************/
9:16 AM 8/28/2013
----------------------
SELECT BN.PARTY_NAME BANK,BN.PARTY_ID BANK_ID,HPUA.PARTY_USAGE_CODE TYPE,BR.PARTY_NAME BRANCH
,CBA.BANK_ACCOUNT_NAME,CBA.SHORT_ACCOUNT_NAME,CBA.BANK_ACCOUNT_NUM
,CBA.MASKED_ACCOUNT_NUM,AH.PERSON_PRE_NAME_ADJUNCT TITLE,AH.PERSON_FIRST_NAME FIRST_NAME
,AH.PERSON_MIDDLE_NAME MID_NAME,AH.PERSON_LAST_NAME LAST_NAME,AH.PERSON_NAME_SUFFIX SUFFIX
,HL.ADDRESS1,HL.ADDRESS2,HL.CITY,HL.STATE,HL.POSTAL_CODE,FT.NLS_TERRITORY COUNTRY
,HOC.CONTACT_NUMBER,HOC.JOB_TITLE
,EMAIL.EMAIL_ADDRESS EMAIL
,PHN.PHONE_AREA_CODE,PHN.PHONE_NUMBER,PHN.PHONE_COUNTRY_CODE,PHN.PHONE_EXTENSION
FROM
HZ_PARTIES BN
,HZ_PARTY_USG_ASSIGNMENTS HPUA
,HZ_RELATIONSHIPS HREL
,HZ_PARTIES BR
,CE_BANK_ACCOUNTS CBA
,HZ_PARTIES AH
,HZ_RELATIONSHIPS HREL1
,HZ_PARTY_SITES HPS
,HZ_LOCATIONS HL
,FND_TERRITORIES FT
,HZ_ORG_CONTACTS HOC
,(SELECT OWNER_TABLE_ID,EMAIL_ADDRESS FROM HZ_CONTACT_POINTS WHERE CONTACT_POINT_TYPE='EMAIL') EMAIL
,(SELECT OWNER_TABLE_ID,PHONE_AREA_CODE,PHONE_NUMBER,PHONE_COUNTRY_CODE,PHONE_EXTENSION FROM HZ_CONTACT_POINTS WHERE PHONE_LINE_TYPE='GEN') PHN
WHERE 1=1
AND BN.PARTY_ID=413679
AND BN.PARTY_NAME='ICICI Bank'
AND HPUA.PARTY_USAGE_CODE='BANK'
AND BN.PARTY_ID=HPUA.PARTY_ID
AND HREL.OBJECT_TYPE='ORGANIZATION'
AND HREL.SUBJECT_ID=BN.PARTY_ID
AND HREL.OBJECT_ID=BR.PARTY_ID
AND BN.PARTY_ID=CBA.BANK_ID
AND BR.PARTY_ID=CBA.BANK_BRANCH_ID
AND BN.PARTY_ID=HREL1.SUBJECT_ID
AND HREL1.OBJECT_ID=AH.PARTY_ID
--AND HREL1.OBJECT_TYPE='PERSON'
AND AH.PARTY_NUMBER=57756
AND HREL1.PARTY_ID=HPS.PARTY_ID
AND HPS.LOCATION_ID=HL.LOCATION_ID
AND FT.TERRITORY_CODE=HL.COUNTRY
AND HREL1.RELATIONSHIP_ID=HOC.PARTY_RELATIONSHIP_ID
AND HREL1.PARTY_ID=EMAIL.OWNER_TABLE_ID
AND HREL1.PARTY_ID=PHN.OWNER_TABLE_ID
AND HREL1.SUBJECT_ID=AH.PARTY_ID
/*************************************************************************************************************/
No comments:
Post a Comment