VIEW: APPS.IEX_HI_ADJUSTMENTS_V

Source

View Text - Preformatted

SELECT ADJ.ADJUSTMENT_ID ADJUSTMENT_ID, ADJ.ADJUSTMENT_NUMBER ADJUSTMENT_NUMBER, RT.NAME ACTIVITY_NAME, ADJ.ADJUSTMENT_TYPE ADJUSTMENT_TYPE, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ADJUSTMENT_TYPE', ADJ.TYPE) TYPE_MEANING, ADJ.AMOUNT AMOUNT, ADJ.APPLY_DATE APPLY_DATE, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ADJUST_REASON', ADJ.REASON_CODE) REASON_MEANING, ADJ.STATUS STATUS, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPROVAL_TYPE', ADJ.STATUS) STATUS_MEANING, ADJ.GL_DATE GL_DATE, CT.TRX_NUMBER TRX_NUMBER, CT_CHARGE.TRX_NUMBER CHARGEBACK_NUMBER, CTL_LINENUM.LINE_NUMBER LINE_NUMBER, ADJ.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE, ADJ.CUSTOMER_TRX_ID CUSTOMER_TRX_ID, PS.CUSTOMER_ID CUSTOMER_ID, RAC.ACCOUNT_NUMBER ACCOUNT_NUMBER, RAC.PARTY_ID PARTY_ID, TO_NUMBER(NULL) DELINQUENCY_ID, ADJ.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID, FND_FLEX_EXT.GET_SEGS('SQLGL', 'GL#', GL.CHART_OF_ACCOUNTS_ID, NVL(ADJ.CODE_COMBINATION_ID, 0)) GL_ACCOUNT, PS.TERMS_SEQUENCE_NUMBER INSTALLMENT_NUMBER, PS.DUE_DATE DUE_DATE, DECODE(RT.TAX_CODE_SOURCE, 'NONE', 'N', 'Y') TAX_INCLUDED, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID, SITE_USES.LOCATION LOCATION, PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER, TRIM(SUBSTR(LOC.ADDRESS1, 1, 25) || ' ' || SUBSTR(LOC.ADDRESS2, 1, 25) || ' ' || SUBSTR(LOC.ADDRESS3, 1, 25) || ' ' || SUBSTR(LOC.ADDRESS4, 1, 25) || ' ' || LOC.CITY || ' ' || NVL(LOC.STATE, LOC.PROVINCE) || ' ' || T.TERRITORY_SHORT_NAME) ADDRESS, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER, ADJ.ORG_ID ORG_ID, OTL.NAME OPERATING_UNIT, HP.PARTY_NAME PARTY_NAME, ADJ.COMMENTS RECEIVABLES_COMMENTS FROM AR_PAYMENT_SCHEDULES_ALL PS, AR_RECEIVABLES_TRX_ALL RT, AR_ADJUSTMENTS ADJ, RA_CUSTOMER_TRX_ALL CT, RA_CUSTOMER_TRX_ALL CT_CHARGE, RA_CUSTOMER_TRX_LINES_ALL CTL_LINENUM, HZ_CUST_ACCOUNTS RAC, GL_LEDGERS_PUBLIC_V GL, HZ_CUST_ACCT_SITES ACCT_SITE, HZ_PARTY_SITES PARTY_SITE, HZ_LOCATIONS LOC, HZ_CUST_SITE_USES SITE_USES, FND_TERRITORIES_TL T, HR_ALL_ORGANIZATION_UNITS_TL OTL, AR_CONS_INV CONS, HZ_PARTIES HP WHERE ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID AND ADJ.ORG_ID = PS.ORG_ID AND PS.AMOUNT_ADJUSTED > -10000000 AND ADJ.RECEIVABLES_TRX_ID = RT.RECEIVABLES_TRX_ID AND ADJ.ORG_ID = RT.ORG_ID AND RT.RECEIVABLES_TRX_ID <> -15 AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID AND ADJ.ORG_ID = CT.ORG_ID AND ADJ.CHARGEBACK_CUSTOMER_TRX_ID = CT_CHARGE.CUSTOMER_TRX_ID(+) AND ADJ.ORG_ID = CT_CHARGE.ORG_ID(+) AND ADJ.CUSTOMER_TRX_LINE_ID = CTL_LINENUM.CUSTOMER_TRX_LINE_ID(+) AND PS.CUSTOMER_ID = RAC.CUST_ACCOUNT_ID AND ADJ.SET_OF_BOOKS_ID = GL.LEDGER_ID(+) AND PS.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID AND SITE_USES.SITE_USE_CODE = 'BILL_TO' AND SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID AND LOC.COUNTRY = T.TERRITORY_CODE AND T.LANGUAGE = USERENV('LANG') AND ADJ.ORG_ID = OTL.ORGANIZATION_ID AND OTL.LANGUAGE = USERENV('LANG') AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID AND RAC.PARTY_ID = HP.PARTY_ID

View Text - HTML Formatted

SELECT ADJ.ADJUSTMENT_ID ADJUSTMENT_ID
, ADJ.ADJUSTMENT_NUMBER ADJUSTMENT_NUMBER
, RT.NAME ACTIVITY_NAME
, ADJ.ADJUSTMENT_TYPE ADJUSTMENT_TYPE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ADJUSTMENT_TYPE'
, ADJ.TYPE) TYPE_MEANING
, ADJ.AMOUNT AMOUNT
, ADJ.APPLY_DATE APPLY_DATE
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('ADJUST_REASON'
, ADJ.REASON_CODE) REASON_MEANING
, ADJ.STATUS STATUS
, ARPT_SQL_FUNC_UTIL.GET_LOOKUP_MEANING('APPROVAL_TYPE'
, ADJ.STATUS) STATUS_MEANING
, ADJ.GL_DATE GL_DATE
, CT.TRX_NUMBER TRX_NUMBER
, CT_CHARGE.TRX_NUMBER CHARGEBACK_NUMBER
, CTL_LINENUM.LINE_NUMBER LINE_NUMBER
, ADJ.DOC_SEQUENCE_VALUE DOCUMENT_NUMBER
, PS.INVOICE_CURRENCY_CODE CURRENCY_CODE
, ADJ.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
, PS.CUSTOMER_ID CUSTOMER_ID
, RAC.ACCOUNT_NUMBER ACCOUNT_NUMBER
, RAC.PARTY_ID PARTY_ID
, TO_NUMBER(NULL) DELINQUENCY_ID
, ADJ.PAYMENT_SCHEDULE_ID PAYMENT_SCHEDULE_ID
, FND_FLEX_EXT.GET_SEGS('SQLGL'
, 'GL#'
, GL.CHART_OF_ACCOUNTS_ID
, NVL(ADJ.CODE_COMBINATION_ID
, 0)) GL_ACCOUNT
, PS.TERMS_SEQUENCE_NUMBER INSTALLMENT_NUMBER
, PS.DUE_DATE DUE_DATE
, DECODE(RT.TAX_CODE_SOURCE
, 'NONE'
, 'N'
, 'Y') TAX_INCLUDED
, PS.CUSTOMER_SITE_USE_ID CUSTOMER_SITE_USE_ID
, SITE_USES.LOCATION LOCATION
, PARTY_SITE.PARTY_SITE_NUMBER PARTY_SITE_NUMBER
, TRIM(SUBSTR(LOC.ADDRESS1
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS2
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS3
, 1
, 25) || ' ' || SUBSTR(LOC.ADDRESS4
, 1
, 25) || ' ' || LOC.CITY || ' ' || NVL(LOC.STATE
, LOC.PROVINCE) || ' ' || T.TERRITORY_SHORT_NAME) ADDRESS
, CONS.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
, ADJ.ORG_ID ORG_ID
, OTL.NAME OPERATING_UNIT
, HP.PARTY_NAME PARTY_NAME
, ADJ.COMMENTS RECEIVABLES_COMMENTS
FROM AR_PAYMENT_SCHEDULES_ALL PS
, AR_RECEIVABLES_TRX_ALL RT
, AR_ADJUSTMENTS ADJ
, RA_CUSTOMER_TRX_ALL CT
, RA_CUSTOMER_TRX_ALL CT_CHARGE
, RA_CUSTOMER_TRX_LINES_ALL CTL_LINENUM
, HZ_CUST_ACCOUNTS RAC
, GL_LEDGERS_PUBLIC_V GL
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SITE_USES
, FND_TERRITORIES_TL T
, HR_ALL_ORGANIZATION_UNITS_TL OTL
, AR_CONS_INV CONS
, HZ_PARTIES HP
WHERE ADJ.PAYMENT_SCHEDULE_ID = PS.PAYMENT_SCHEDULE_ID
AND ADJ.ORG_ID = PS.ORG_ID
AND PS.AMOUNT_ADJUSTED > -10000000
AND ADJ.RECEIVABLES_TRX_ID = RT.RECEIVABLES_TRX_ID
AND ADJ.ORG_ID = RT.ORG_ID
AND RT.RECEIVABLES_TRX_ID <> -15
AND ADJ.CUSTOMER_TRX_ID = CT.CUSTOMER_TRX_ID
AND ADJ.ORG_ID = CT.ORG_ID
AND ADJ.CHARGEBACK_CUSTOMER_TRX_ID = CT_CHARGE.CUSTOMER_TRX_ID(+)
AND ADJ.ORG_ID = CT_CHARGE.ORG_ID(+)
AND ADJ.CUSTOMER_TRX_LINE_ID = CTL_LINENUM.CUSTOMER_TRX_LINE_ID(+)
AND PS.CUSTOMER_ID = RAC.CUST_ACCOUNT_ID
AND ADJ.SET_OF_BOOKS_ID = GL.LEDGER_ID(+)
AND PS.CUSTOMER_SITE_USE_ID = SITE_USES.SITE_USE_ID
AND SITE_USES.SITE_USE_CODE = 'BILL_TO'
AND SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID
AND LOC.COUNTRY = T.TERRITORY_CODE
AND T.LANGUAGE = USERENV('LANG')
AND ADJ.ORG_ID = OTL.ORGANIZATION_ID
AND OTL.LANGUAGE = USERENV('LANG')
AND CONS.CONS_INV_ID(+) = PS.CONS_INV_ID
AND RAC.PARTY_ID = HP.PARTY_ID