VIEW: APPS.IEX_LS_TRX_ADV_V

Source

View Text - Preformatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, CT.TRX_NUMBER TRX_NUMBER, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER, PS.LAST_UPDATE_DATE LAST_UPDATE_DATE, PS.LAST_UPDATED_BY LAST_UPDATED_BY, PS.CREATION_DATE CREATION_DATE, PS.CREATED_BY CREATED_BY, PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, PS.DUE_DATE DUE_DATE, CT.TRX_DATE TRX_DATE, DECODE(PS.AMOUNT_DUE_REMAINING, 0, TO_NUMBER(NULL), TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING, PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING, PS.STATUS CUST_TRX_STATUS_CODE, AL_STATUS.MEANING CUST_TRX_STATUS_NAME, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, PS.CLASS CUST_TRX_CLASS_CODE, AL_CLASS.MEANING CUST_TRX_CLASS_NAME, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID, CTT.NAME CUST_TRX_TYPE_NAME, PARTY.PARTY_ID PARTY_ID, PS.CUSTOMER_ID CUSTOMER_ID, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PS.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE, PS.EXCHANGE_DATE EXCHANGE_DATE, PS.EXCHANGE_RATE EXCHANGE_RATE, PS.ORG_ID ORG_ID, SUBSTRB(PARTY.PARTY_NAME,1,50) CUSTOMER_NAME, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER, SU.LOCATION LOCATION, CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID, RACO_BILL_PARTY.PERSON_FIRST_NAME BILL_TO_FIRST_NAME, RACO_BILL_PARTY.PERSON_LAST_NAME BILL_TO_LAST_NAME, RAA_BILL_PS.PARTY_SITE_ID PARTY_SITE_ID, RAA_BILL_LOC.LOCATION_ID LOCATION_ID, RAA_BILL_LOC.ADDRESS1 BILL_TO_ADDRESS1, RAA_BILL_LOC.ADDRESS2 BILL_TO_ADDRESS2, RAA_BILL_LOC.ADDRESS3 BILL_TO_ADDRESS3, RAA_BILL_LOC.ADDRESS4 BILL_TO_ADDRESS4, RAA_BILL_LOC.CITY BILL_TO_CITY, RAA_BILL_LOC.COUNTY BILL_TO_COUNTY, RAA_BILL_LOC.STATE BILL_TO_STATE, RAA_BILL_LOC.PROVINCE BILL_TO_PROVINCE, RAA_BILL_LOC.POSTAL_CODE BILL_TO_POSTAL_CODE, RAA_BILL_LOC.COUNTRY BILL_TO_COUNTRY_CODE, FT_BILL.TERRITORY_SHORT_NAME BILL_TO_COUNTRY, RACO_BILL_PARTY.EMAIL_ADDRESS BILL_TO_EMAIL_ADDRESS, CP.PHONE_NUMBER BILL_TO_PHONE_NUMBER, CP.PHONE_AREA_CODE BILL_TO_PHONE_AREA_CODE, CP.PHONE_COUNTRY_CODE BILL_TO_PHONE_COUNTRY_CODE, CP.PHONE_LINE_TYPE BILL_TO_PHONE_TYPE_CODE, AL_PHONE.MEANING BILL_TO_PHONE_TYPE_NAME, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY, CT.ATTRIBUTE1 ATTRIBUTE1, CT.ATTRIBUTE2 ATTRIBUTE2, CT.ATTRIBUTE3 ATTRIBUTE3, CT.ATTRIBUTE4 ATTRIBUTE4, CT.ATTRIBUTE5 ATTRIBUTE5, CT.ATTRIBUTE6 ATTRIBUTE6, CT.ATTRIBUTE7 ATTRIBUTE7, CT.ATTRIBUTE8 ATTRIBUTE8, CT.ATTRIBUTE9 ATTRIBUTE9, CT.ATTRIBUTE10 ATTRIBUTE10, CT.ATTRIBUTE11 ATTRIBUTE11, CT.ATTRIBUTE12 ATTRIBUTE12, CT.ATTRIBUTE13 ATTRIBUTE13, CT.ATTRIBUTE14 ATTRIBUTE14, CT.ATTRIBUTE15 ATTRIBUTE15, CP.PHONE_EXTENSION BILL_TO_PHONE_EXTENSION, NVL(CP.PHONE_COUNTRY_CODE,'   ') || '-' || NVL(CP.PHONE_AREA_CODE,'   ') || '-' || CP.PHONE_NUMBER || ' ' || CP.PHONE_EXTENSION BILL_TO_FULL_PHONE_NUMBER, CT.PURCHASE_ORDER PURCHASE_ORDER FROM AR_LOOKUPS AL_STATUS, AR_PAYMENT_SCHEDULES PS, RA_CUST_TRX_TYPES CTT, RA_CUSTOMER_TRX CT, HZ_CUST_SITE_USES SU, HZ_CUST_ACCOUNTS CUST_ACCT, HZ_PARTIES PARTY, HZ_CUST_ACCOUNT_ROLES RACO_BILL, HZ_PARTIES RACO_BILL_PARTY, HZ_RELATIONSHIPS RACO_BILL_REL, HZ_CUST_ACCT_SITES RAA_BILL, HZ_PARTY_SITES RAA_BILL_PS, HZ_LOCATIONS RAA_BILL_LOC, FND_TERRITORIES_VL FT_BILL, HZ_CONTACT_POINTS CP, AR_LOOKUPS AL_PHONE, AR_LOOKUPS AL_REL, AR_LOOKUPS AL_CLASS WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID AND CT.BILL_TO_SITE_USE_ID = SU.SITE_USE_ID AND SU.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND CT.BILL_TO_CONTACT_ID = RACO_BILL.CUST_ACCOUNT_ROLE_ID(+) AND RACO_BILL.ROLE_TYPE(+) = 'CONTACT' AND RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+) AND RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F' AND RACO_BILL_REL.SUBJECT_ID = RACO_BILL_PARTY.PARTY_ID(+) AND RACO_BILL_REL.RELATIONSHIP_CODE = AL_REL.LOOKUP_CODE(+) AND AL_REL.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE' AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+) AND RACO_BILL_REL.PARTY_ID = CP.OWNER_TABLE_ID(+) AND CP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CP.PHONE_LINE_TYPE = AL_PHONE.LOOKUP_CODE(+) AND AL_PHONE.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE' AND CP.CONTACT_POINT_TYPE(+) = 'PHONE' AND CP.PRIMARY_FLAG(+) = 'Y' AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID AND CT.ORG_ID = CTT.ORG_ID AND PS.STATUS = AL_STATUS.LOOKUP_CODE AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS' AND PS.CLASS = AL_CLASS.LOOKUP_CODE AND AL_CLASS.LOOKUP_TYPE = 'INV/CM'

View Text - HTML Formatted

SELECT PS.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, CT.TRX_NUMBER TRX_NUMBER
, PS.TERMS_SEQUENCE_NUMBER TERMS_SEQUENCE_NUMBER
, PS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PS.LAST_UPDATED_BY LAST_UPDATED_BY
, PS.CREATION_DATE CREATION_DATE
, PS.CREATED_BY CREATED_BY
, PS.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, PS.DUE_DATE DUE_DATE
, CT.TRX_DATE TRX_DATE
, DECODE(PS.AMOUNT_DUE_REMAINING
, 0
, TO_NUMBER(NULL)
, TRUNC(SYSDATE) - PS.DUE_DATE) DAYS_PAST_DUE
, PS.AMOUNT_DUE_ORIGINAL AMOUNT_DUE_ORIGINAL
, PS.AMOUNT_DUE_REMAINING AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING ACCTD_AMOUNT_DUE_REMAINING
, PS.STATUS CUST_TRX_STATUS_CODE
, AL_STATUS.MEANING CUST_TRX_STATUS_NAME
, PS.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, PS.CLASS CUST_TRX_CLASS_CODE
, AL_CLASS.MEANING CUST_TRX_CLASS_NAME
, PS.CUST_TRX_TYPE_ID CUST_TRX_TYPE_ID
, CTT.NAME CUST_TRX_TYPE_NAME
, PARTY.PARTY_ID PARTY_ID
, PS.CUSTOMER_ID CUSTOMER_ID
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.EXCHANGE_RATE_TYPE EXCHANGE_RATE_TYPE
, PS.EXCHANGE_DATE EXCHANGE_DATE
, PS.EXCHANGE_RATE EXCHANGE_RATE
, PS.ORG_ID ORG_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, CUST_ACCT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, SU.LOCATION LOCATION
, CT.BILL_TO_CONTACT_ID BILL_TO_CONTACT_ID
, RACO_BILL_PARTY.PERSON_FIRST_NAME BILL_TO_FIRST_NAME
, RACO_BILL_PARTY.PERSON_LAST_NAME BILL_TO_LAST_NAME
, RAA_BILL_PS.PARTY_SITE_ID PARTY_SITE_ID
, RAA_BILL_LOC.LOCATION_ID LOCATION_ID
, RAA_BILL_LOC.ADDRESS1 BILL_TO_ADDRESS1
, RAA_BILL_LOC.ADDRESS2 BILL_TO_ADDRESS2
, RAA_BILL_LOC.ADDRESS3 BILL_TO_ADDRESS3
, RAA_BILL_LOC.ADDRESS4 BILL_TO_ADDRESS4
, RAA_BILL_LOC.CITY BILL_TO_CITY
, RAA_BILL_LOC.COUNTY BILL_TO_COUNTY
, RAA_BILL_LOC.STATE BILL_TO_STATE
, RAA_BILL_LOC.PROVINCE BILL_TO_PROVINCE
, RAA_BILL_LOC.POSTAL_CODE BILL_TO_POSTAL_CODE
, RAA_BILL_LOC.COUNTRY BILL_TO_COUNTRY_CODE
, FT_BILL.TERRITORY_SHORT_NAME BILL_TO_COUNTRY
, RACO_BILL_PARTY.EMAIL_ADDRESS BILL_TO_EMAIL_ADDRESS
, CP.PHONE_NUMBER BILL_TO_PHONE_NUMBER
, CP.PHONE_AREA_CODE BILL_TO_PHONE_AREA_CODE
, CP.PHONE_COUNTRY_CODE BILL_TO_PHONE_COUNTRY_CODE
, CP.PHONE_LINE_TYPE BILL_TO_PHONE_TYPE_CODE
, AL_PHONE.MEANING BILL_TO_PHONE_TYPE_NAME
, CT.DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE
, CT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, CT.ATTRIBUTE1 ATTRIBUTE1
, CT.ATTRIBUTE2 ATTRIBUTE2
, CT.ATTRIBUTE3 ATTRIBUTE3
, CT.ATTRIBUTE4 ATTRIBUTE4
, CT.ATTRIBUTE5 ATTRIBUTE5
, CT.ATTRIBUTE6 ATTRIBUTE6
, CT.ATTRIBUTE7 ATTRIBUTE7
, CT.ATTRIBUTE8 ATTRIBUTE8
, CT.ATTRIBUTE9 ATTRIBUTE9
, CT.ATTRIBUTE10 ATTRIBUTE10
, CT.ATTRIBUTE11 ATTRIBUTE11
, CT.ATTRIBUTE12 ATTRIBUTE12
, CT.ATTRIBUTE13 ATTRIBUTE13
, CT.ATTRIBUTE14 ATTRIBUTE14
, CT.ATTRIBUTE15 ATTRIBUTE15
, CP.PHONE_EXTENSION BILL_TO_PHONE_EXTENSION
, NVL(CP.PHONE_COUNTRY_CODE
, ' ') || '-' || NVL(CP.PHONE_AREA_CODE
, ' ') || '-' || CP.PHONE_NUMBER || ' ' || CP.PHONE_EXTENSION BILL_TO_FULL_PHONE_NUMBER
, CT.PURCHASE_ORDER PURCHASE_ORDER
FROM AR_LOOKUPS AL_STATUS
, AR_PAYMENT_SCHEDULES PS
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX CT
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_ACCOUNT_ROLES RACO_BILL
, HZ_PARTIES RACO_BILL_PARTY
, HZ_RELATIONSHIPS RACO_BILL_REL
, HZ_CUST_ACCT_SITES RAA_BILL
, HZ_PARTY_SITES RAA_BILL_PS
, HZ_LOCATIONS RAA_BILL_LOC
, FND_TERRITORIES_VL FT_BILL
, HZ_CONTACT_POINTS CP
, AR_LOOKUPS AL_PHONE
, AR_LOOKUPS AL_REL
, AR_LOOKUPS AL_CLASS
WHERE PS.CUSTOMER_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CT.BILL_TO_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID
AND PS.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND CT.BILL_TO_CONTACT_ID = RACO_BILL.CUST_ACCOUNT_ROLE_ID(+)
AND RACO_BILL.ROLE_TYPE(+) = 'CONTACT'
AND RACO_BILL.PARTY_ID = RACO_BILL_REL.PARTY_ID(+)
AND RACO_BILL_REL.DIRECTIONAL_FLAG(+) = 'F'
AND RACO_BILL_REL.SUBJECT_ID = RACO_BILL_PARTY.PARTY_ID(+)
AND RACO_BILL_REL.RELATIONSHIP_CODE = AL_REL.LOOKUP_CODE(+)
AND AL_REL.LOOKUP_TYPE(+) = 'PARTY_RELATIONS_TYPE'
AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
AND RACO_BILL_REL.PARTY_ID = CP.OWNER_TABLE_ID(+)
AND CP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CP.PHONE_LINE_TYPE = AL_PHONE.LOOKUP_CODE(+)
AND AL_PHONE.LOOKUP_TYPE(+) = 'PHONE_LINE_TYPE'
AND CP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CP.PRIMARY_FLAG(+) = 'Y'
AND CT.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CT.ORG_ID = CTT.ORG_ID
AND PS.STATUS = AL_STATUS.LOOKUP_CODE
AND AL_STATUS.LOOKUP_TYPE = 'INVOICE_TRX_STATUS'
AND PS.CLASS = AL_CLASS.LOOKUP_CODE
AND AL_CLASS.LOOKUP_TYPE = 'INV/CM'