VIEW: APPS.OKI_HPARTY_DTL_V

Source

View Text - Preformatted

SELECT SHD.CHR_ID CHR_ID ,SHD.CONTRACT_NUMBER , SHD.CONTRACT_NUMBER_MODIFIER ,SHD.COMPLETE_K_NUMBER , CPR.RLE_CODE ,OKX.PARTY_NAME ,OKX.PARTY_NUMBER FROM (SELECT OKX.CUSTOMER_NAME PARTY_NAME , OKX.CUSTOMER_NUMBER PARTY_NUMBER , OKX.CUSTOMER_ID PARTY_ID, 'OKX_PARTY' TYP FROM ( SELECT HZ.PARTY_ID CUSTOMER_ID ,HZ.PARTY_NAME CUSTOMER_NAME, DECODE( HZ.PARTY_TYPE, 'ORGANIZATION', HZ.ORGANIZATION_NAME_PHONETIC,HZ.PERSON_LAST_NAME_PHONETIC || ' ' ||HZ.PERSON_FIRST_NAME_PHONETIC ) ALTERNATE_NAME , HZ.PARTY_NUMBER CUSTOMER_NUMBER , HZ.PARTY_TYPE CUSTOMER_TYPE FROM HZ_PARTIES HZ WHERE EXISTS(SELECT 1 FROM HZ_CUST_ACCOUNTS HZA WHERE HZ.PARTY_ID = HZA.PARTY_ID) ) OKX WHERE OKX.CUSTOMER_TYPE IN( 'PERSON', 'ORGANIZATION' ) UNION ALL SELECT OKX.NAME , MTLP.ORGANIZATION_CODE , OKX.ORGANIZATION_ID PARTY_ID, 'OKX_OPERUNIT' TYP FROM OKI_ORGS_OLTP_V OKX , MTL_PARAMETERS MTLP WHERE OKX.ORGANIZATION_ID = MTLP.ORGANIZATION_ID(+) ) OKX , OKC_K_PARTY_ROLES_B CPR , OKI_SRM_006_V SHD WHERE CPR.JTOT_OBJECT1_CODE IN( 'OKX_PARTY', 'OKX_OPERUNIT' ) AND CPR.OBJECT1_ID1 = OKX.PARTY_ID AND SHD.CHR_ID = CPR.DNZ_CHR_ID AND CPR.JTOT_OBJECT1_CODE = OKX.TYP AND CPR.CLE_ID IS NULL

View Text - HTML Formatted

SELECT SHD.CHR_ID CHR_ID
, SHD.CONTRACT_NUMBER
, SHD.CONTRACT_NUMBER_MODIFIER
, SHD.COMPLETE_K_NUMBER
, CPR.RLE_CODE
, OKX.PARTY_NAME
, OKX.PARTY_NUMBER
FROM (SELECT OKX.CUSTOMER_NAME PARTY_NAME
, OKX.CUSTOMER_NUMBER PARTY_NUMBER
, OKX.CUSTOMER_ID PARTY_ID
, 'OKX_PARTY' TYP
FROM ( SELECT HZ.PARTY_ID CUSTOMER_ID
, HZ.PARTY_NAME CUSTOMER_NAME
, DECODE( HZ.PARTY_TYPE
, 'ORGANIZATION'
, HZ.ORGANIZATION_NAME_PHONETIC
, HZ.PERSON_LAST_NAME_PHONETIC || ' ' ||HZ.PERSON_FIRST_NAME_PHONETIC ) ALTERNATE_NAME
, HZ.PARTY_NUMBER CUSTOMER_NUMBER
, HZ.PARTY_TYPE CUSTOMER_TYPE
FROM HZ_PARTIES HZ
WHERE EXISTS(SELECT 1
FROM HZ_CUST_ACCOUNTS HZA
WHERE HZ.PARTY_ID = HZA.PARTY_ID) ) OKX
WHERE OKX.CUSTOMER_TYPE IN( 'PERSON'
, 'ORGANIZATION' ) UNION ALL SELECT OKX.NAME
, MTLP.ORGANIZATION_CODE
, OKX.ORGANIZATION_ID PARTY_ID
, 'OKX_OPERUNIT' TYP
FROM OKI_ORGS_OLTP_V OKX
, MTL_PARAMETERS MTLP
WHERE OKX.ORGANIZATION_ID = MTLP.ORGANIZATION_ID(+) ) OKX
, OKC_K_PARTY_ROLES_B CPR
, OKI_SRM_006_V SHD
WHERE CPR.JTOT_OBJECT1_CODE IN( 'OKX_PARTY'
, 'OKX_OPERUNIT' )
AND CPR.OBJECT1_ID1 = OKX.PARTY_ID
AND SHD.CHR_ID = CPR.DNZ_CHR_ID
AND CPR.JTOT_OBJECT1_CODE = OKX.TYP
AND CPR.CLE_ID IS NULL