VIEW: APPS.IEX_LS_DISPUTES_V

Source

View Text - Preformatted

SELECT DIS.DISPUTE_ID DISPUTE_ID, DIS.CREATION_DATE CREATION_DATE , ROUND(SYSDATE - DIS.CREATION_DATE) NUMBER_OF_DAYS , DIS.DISPUTE_SECTION DISPUTE_SECTION , DIS.CAMPAIGN_SCHED_ID CAMPAIGN_SCHED_ID , DIS.DELINQUENCY_ID DELINQUENCY_ID, DIS.REQUEST_ID REQUEST_ID, DIS.CM_REQUEST_ID CM_REQUEST_ID, REQ.ORIG_TRX_NUMBER ORIG_TRX_NUMBER, REQ.STATUS STATUS, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REQUEST_STATUS', REQ.STATUS) STATUS_MEANING, LKP.LOOKUP_CODE CM_REASON_CODE, REQ.TOTAL_AMOUNT TOTAL_AMOUNT, TRX.BILL_TO_CUSTOMER_ID PARTY_ID, TRX.CUSTOMER_TRX_ID INVOICE_ID, TRX.TRX_NUMBER INVOICE_NUMBER, TRX.CUST_TRX_TYPE_ID TYPE, TRX.STATUS_TRX TRANSACTION_STATUS, ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER, PARTY.PARTY_NUMBER PARTY_NUMBER, PARTY.PARTY_NAME PARTY_NAME, PARTY.COMPETITOR_FLAG COMPETITOR_FLAG, PARTY.CUSTOMER_KEY CUSTOMER_KEY, PARTY.CATEGORY_CODE CATEGORY_CODE, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS, PER.PERSON_FIRST_NAME PERSON_FIRST_NAME, PER.PERSON_LAST_NAME PERSON_LAST_NAME, PARTY.ADDRESS1 ADDRESS1, PARTY.ADDRESS2 ADDRESS2, PARTY.ADDRESS3 ADDRESS3, PARTY.ADDRESS4 ADDRESS4, PARTY.CITY CITY, PARTY.STATE STATE, PARTY.PROVINCE PROVINCE, PARTY.COUNTY COUNTY, PARTY.POSTAL_CODE POSTAL_CODE, PARTY.COUNTRY COUNTRY, CPT.CONTACT_POINT_TYPE CONTACT_POINT_TYPE, CPT.TELEPHONE_TYPE TELEPHONE_TYPE, CPT.PHONE_LINE_TYPE PHONE_LINE_TYPE, CPT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE, CPT.PHONE_AREA_CODE PHONE_AREA_CODE, CPT.PHONE_NUMBER PHONE_NUMBER, CPT.PHONE_EXTENSION PHONE_EXTENSION, CPT.PHONE_COUNTRY_CODE||' '||CPT.PHONE_AREA_CODE||'-'||CPT.PHONE_NUMBER||' '|| CPT.PHONE_EXTENSION FULL_PHONE_NUMBER, DIS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, DIS.ATTRIBUTE1 ATTRIBUTE1, DIS.ATTRIBUTE2 ATTRIBUTE2, DIS.ATTRIBUTE3 ATTRIBUTE3, DIS.ATTRIBUTE4 ATTRIBUTE4, DIS.ATTRIBUTE5 ATTRIBUTE5, DIS.ATTRIBUTE6 ATTRIBUTE6, DIS.ATTRIBUTE7 ATTRIBUTE7, DIS.ATTRIBUTE8 ATTRIBUTE8, DIS.ATTRIBUTE9 ATTRIBUTE9, DIS.ATTRIBUTE10 ATTRIBUTE10, DIS.ATTRIBUTE11 ATTRIBUTE11, DIS.ATTRIBUTE12 ATTRIBUTE12, DIS.ATTRIBUTE13 ATTRIBUTE13, DIS.ATTRIBUTE14 ATTRIBUTE14, DIS.ATTRIBUTE15 ATTRIBUTE15 FROM IEX_DISPUTES DIS , RA_CUSTOMER_TRX TRX , RA_CM_REQUESTS REQ , HZ_PARTIES PARTY , HZ_CONTACT_POINTS CPT , HZ_CUST_ACCOUNTS ACCT , HZ_CUST_ACCOUNT_ROLES ROL , HZ_PARTIES PER , HZ_RELATIONSHIPS REL , AR_LOOKUPS LKP WHERE DIS.CM_REQUEST_ID = REQ.REQUEST_ID AND TRX.CUSTOMER_TRX_ID = REQ.CUSTOMER_TRX_ID AND TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID AND TRX.BILL_TO_CONTACT_ID = ROL.CUST_ACCOUNT_ROLE_ID(+) AND ROL.ROLE_TYPE(+) = 'CONTACT' AND ACCT.PARTY_ID = PARTY.PARTY_ID AND ROL.PARTY_ID = REL.PARTY_ID(+) AND REL.SUBJECT_ID = PER.PARTY_ID(+) /*Included the outer joins in CPT for fix 4117290 */ AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES' AND REL.DIRECTIONAL_FLAG(+) = 'F' AND CPT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CPT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID AND CPT.CONTACT_POINT_TYPE(+) = 'PHONE' AND CPT.PRIMARY_FLAG(+) = 'Y' AND REQ.CM_REASON_CODE = LKP.LOOKUP_CODE AND LKP.LOOKUP_TYPE = 'CREDIT_MEMO_REASON'

View Text - HTML Formatted

SELECT DIS.DISPUTE_ID DISPUTE_ID
, DIS.CREATION_DATE CREATION_DATE
, ROUND(SYSDATE - DIS.CREATION_DATE) NUMBER_OF_DAYS
, DIS.DISPUTE_SECTION DISPUTE_SECTION
, DIS.CAMPAIGN_SCHED_ID CAMPAIGN_SCHED_ID
, DIS.DELINQUENCY_ID DELINQUENCY_ID
, DIS.REQUEST_ID REQUEST_ID
, DIS.CM_REQUEST_ID CM_REQUEST_ID
, REQ.ORIG_TRX_NUMBER ORIG_TRX_NUMBER
, REQ.STATUS STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('CREDIT_MEMO_REQUEST_STATUS'
, REQ.STATUS) STATUS_MEANING
, LKP.LOOKUP_CODE CM_REASON_CODE
, REQ.TOTAL_AMOUNT TOTAL_AMOUNT
, TRX.BILL_TO_CUSTOMER_ID PARTY_ID
, TRX.CUSTOMER_TRX_ID INVOICE_ID
, TRX.TRX_NUMBER INVOICE_NUMBER
, TRX.CUST_TRX_TYPE_ID TYPE
, TRX.STATUS_TRX TRANSACTION_STATUS
, ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, PARTY.PARTY_NUMBER PARTY_NUMBER
, PARTY.PARTY_NAME PARTY_NAME
, PARTY.COMPETITOR_FLAG COMPETITOR_FLAG
, PARTY.CUSTOMER_KEY CUSTOMER_KEY
, PARTY.CATEGORY_CODE CATEGORY_CODE
, PARTY.EMAIL_ADDRESS EMAIL_ADDRESS
, PER.PERSON_FIRST_NAME PERSON_FIRST_NAME
, PER.PERSON_LAST_NAME PERSON_LAST_NAME
, PARTY.ADDRESS1 ADDRESS1
, PARTY.ADDRESS2 ADDRESS2
, PARTY.ADDRESS3 ADDRESS3
, PARTY.ADDRESS4 ADDRESS4
, PARTY.CITY CITY
, PARTY.STATE STATE
, PARTY.PROVINCE PROVINCE
, PARTY.COUNTY COUNTY
, PARTY.POSTAL_CODE POSTAL_CODE
, PARTY.COUNTRY COUNTRY
, CPT.CONTACT_POINT_TYPE CONTACT_POINT_TYPE
, CPT.TELEPHONE_TYPE TELEPHONE_TYPE
, CPT.PHONE_LINE_TYPE PHONE_LINE_TYPE
, CPT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, CPT.PHONE_AREA_CODE PHONE_AREA_CODE
, CPT.PHONE_NUMBER PHONE_NUMBER
, CPT.PHONE_EXTENSION PHONE_EXTENSION
, CPT.PHONE_COUNTRY_CODE||' '||CPT.PHONE_AREA_CODE||'-'||CPT.PHONE_NUMBER||' '|| CPT.PHONE_EXTENSION FULL_PHONE_NUMBER
, DIS.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, DIS.ATTRIBUTE1 ATTRIBUTE1
, DIS.ATTRIBUTE2 ATTRIBUTE2
, DIS.ATTRIBUTE3 ATTRIBUTE3
, DIS.ATTRIBUTE4 ATTRIBUTE4
, DIS.ATTRIBUTE5 ATTRIBUTE5
, DIS.ATTRIBUTE6 ATTRIBUTE6
, DIS.ATTRIBUTE7 ATTRIBUTE7
, DIS.ATTRIBUTE8 ATTRIBUTE8
, DIS.ATTRIBUTE9 ATTRIBUTE9
, DIS.ATTRIBUTE10 ATTRIBUTE10
, DIS.ATTRIBUTE11 ATTRIBUTE11
, DIS.ATTRIBUTE12 ATTRIBUTE12
, DIS.ATTRIBUTE13 ATTRIBUTE13
, DIS.ATTRIBUTE14 ATTRIBUTE14
, DIS.ATTRIBUTE15 ATTRIBUTE15
FROM IEX_DISPUTES DIS
, RA_CUSTOMER_TRX TRX
, RA_CM_REQUESTS REQ
, HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CPT
, HZ_CUST_ACCOUNTS ACCT
, HZ_CUST_ACCOUNT_ROLES ROL
, HZ_PARTIES PER
, HZ_RELATIONSHIPS REL
, AR_LOOKUPS LKP
WHERE DIS.CM_REQUEST_ID = REQ.REQUEST_ID
AND TRX.CUSTOMER_TRX_ID = REQ.CUSTOMER_TRX_ID
AND TRX.BILL_TO_CUSTOMER_ID = ACCT.CUST_ACCOUNT_ID
AND TRX.BILL_TO_CONTACT_ID = ROL.CUST_ACCOUNT_ROLE_ID(+)
AND ROL.ROLE_TYPE(+) = 'CONTACT'
AND ACCT.PARTY_ID = PARTY.PARTY_ID
AND ROL.PARTY_ID = REL.PARTY_ID(+)
AND REL.SUBJECT_ID = PER.PARTY_ID(+) /*INCLUDED THE OUTER JOINS IN CPT FOR FIX 4117290 */
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND CPT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CPT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CPT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CPT.PRIMARY_FLAG(+) = 'Y'
AND REQ.CM_REASON_CODE = LKP.LOOKUP_CODE
AND LKP.LOOKUP_TYPE = 'CREDIT_MEMO_REASON'