VIEW: APPS.OKL_AR_DIST_UV

Source

View Text - Preformatted

SELECT rctlgd.cust_trx_line_gl_dist_id id, rctlgd.account_class AE_LINE_TYPE, rctlgd.CODE_COMBINATION_ID CODE_COMBINATION_ID, OKL_ACCOUNTING_UTIL.get_concat_segments(rctlgd.CODE_COMBINATION_ID) CONCATE_SEGMENTS, OKL_ACCOUNTING_UTIL.get_concate_desc(rctlgd.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('AUTOGL_TYPE', rctlgd.account_class, 222, 222) AE_LINE_TYPE_MEANING, TO_CHAR(NULL) CR_DR_FLAG, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR', DECODE(RCTT.TYPE, 'INV', DECODE(rctlgd.account_class, 'REC', 'D', 'C'), 'CM', DECODE(rctlgd.account_class, 'REC', 'C', 'D')), 101, 101) DR_CR_FLAG_MEANING, DECODE(RCTT.TYPE, 'CM', (- rctlgd.amount), rctlgd.amount) AMOUNT, rctlgd.GL_DATE ACCOUNTING_DATE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('AUTOGL_TYPE', rctlgd.account_class, 222, 222) TEMPLATE_NAME, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO', DECODE(rctlgd.GL_POSTED_DATE, NULL, 'N', 'Y'), 0, 0) POSTED, rctlgd.CUSTOMER_TRX_ID SOURCE_ID, 'RA_CUSTOMER_TRX_LINES' SOURCE_TABLE, rctlgd.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM RA_CUST_TRX_LINE_GL_DIST RCTLGD,RA_CUSTOMER_TRX RCT,RA_CUST_TRX_TYPES RCTT WHERE RCTLGD.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID AND RCTLGD.ACCOUNT_SET_FLAG = 'N' AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID UNION ALL SELECT AD.LINE_ID id, AD.SOURCE_TYPE AE_LINE_TYPE, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID, OKL_ACCOUNTING_UTIL.get_concat_segments(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS, OKL_ACCOUNTING_UTIL.get_concate_desc(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) AE_LINE_TYPE_MEANING, DECODE(AMOUNT_CR, NULL, 'D', 'C') CR_DR_FLAG, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR', DECODE(AMOUNT_CR, NULL, 'D', 'C'), 101, 101) DR_CR_FLAG_MEANING, NVL(AMOUNT_CR, AMOUNT_DR) AMOUNT, AD.LAST_UPDATE_DATE ACCOUNTING_DATE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) TEMPLATE_NAME, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO', DECODE (RAP.GL_POSTED_DATE, NULL, 'N', 'Y'), 0, 0) POSTED, RAP.CASH_RECEIPT_ID SOURCE_ID, 'AR_CASH_RECEIPT' SOURCE_TABLE, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_DISTRIBUTIONS AD, AR_RECEIVABLE_APPLICATIONS RAP, AR_PAYMENT_SCHEDULES PS WHERE AD.SOURCE_TABLE = 'RA' AND AD.SOURCE_ID = RAP.RECEIVABLE_APPLICATION_ID AND PS.PAYMENT_SCHEDULE_ID = RAP.APPLIED_PAYMENT_SCHEDULE_ID AND RAP.STATUS = 'APP' UNION ALL SELECT AD.LINE_ID id, AD.SOURCE_TYPE AE_LINE_TYPE, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID, OKL_ACCOUNTING_UTIL.get_concat_segments(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS, OKL_ACCOUNTING_UTIL.get_concate_desc(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) AE_LINE_TYPE_MEANING, DECODE(AMOUNT_CR, NULL, 'D', 'C') CR_DR_FLAG, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR', DECODE(AMOUNT_CR, NULL, 'D', 'C'), 101, 101) DR_CR_FLAG_MEANING, NVL(AMOUNT_CR, AMOUNT_DR) AMOUNT, AD.LAST_UPDATE_DATE ACCOUNTING_DATE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) TEMPLATE_NAME, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO', DECODE (RAP.GL_POSTED_DATE, NULL, 'N', 'Y'), 0, 0) POSTED, RAP.CASH_RECEIPT_ID SOURCE_ID, 'AR_CASH_RECEIPT' SOURCE_TABLE, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID FROM AR_DISTRIBUTIONS AD, AR_RECEIVABLE_APPLICATIONS RAP, AR_PAYMENT_SCHEDULES PS WHERE AD.SOURCE_TABLE_SECONDARY = 'RA' AND AD.SOURCE_ID_SECONDARY = RAP.RECEIVABLE_APPLICATION_ID AND PS.PAYMENT_SCHEDULE_ID = RAP.APPLIED_PAYMENT_SCHEDULE_ID AND RAP.STATUS = 'APP' UNION ALL SELECT AD.LINE_ID id, AD.SOURCE_TYPE AE_LINE_TYPE, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID, OKL_ACCOUNTING_UTIL.get_concat_segments(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS, OKL_ACCOUNTING_UTIL.get_concate_desc(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) AE_LINE_TYPE_MEANING, DECODE(AMOUNT_CR, NULL, 'D', 'C') CR_DR_FLAG, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR', DECODE(AMOUNT_CR, NULL, 'D', 'C'), 101, 101) DR_CR_FLAG_MEANING , NVL(AMOUNT_CR, AMOUNT_DR) AMOUNT, AD.LAST_UPDATE_DATE ACCOUNTING_DATE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) TEMPLATE_NAME, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO', DECODE (CRH.GL_POSTED_DATE, NULL, 'N', 'Y'), 0, 0) POSTED, CRH.CASH_RECEIPT_ID SOURCE_ID, 'AR_CASH_RECEIPT' SOURCE_TABLE, - 1 CUSTOMER_TRX_ID FROM AR_DISTRIBUTIONS AD, AR_CASH_RECEIPT_HISTORY CRH WHERE AD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID AND AD.SOURCE_TABLE = 'CRH' UNION ALL SELECT AD.LINE_ID id, AD.SOURCE_TYPE AE_LINE_TYPE, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID, OKL_ACCOUNTING_UTIL.get_concat_segments(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS, OKL_ACCOUNTING_UTIL.get_concate_desc(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) AE_LINE_TYPE_MEANING, DECODE(AMOUNT_CR, NULL, 'D', 'C') CR_DR_FLAG, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR', DECODE(AMOUNT_CR, NULL, 'D', 'C'), 101, 101) DR_CR_FLAG_MEANING, NVL(AMOUNT_CR, AMOUNT_DR) AMOUNT, AD.LAST_UPDATE_DATE ACCOUNTING_DATE, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE', AD.SOURCE_TYPE, 222, 222) TEMPLATE_NAME, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO', DECODE (RAP.GL_POSTED_DATE, NULL, 'N', 'Y'), 0, 0) POSTED, RAP.CASH_RECEIPT_ID SOURCE_ID, 'AR_CASH_RECEIPT' SOURCE_TABLE, -1 CUSTOMER_TRX_ID FROM AR_DISTRIBUTIONS AD, AR_RECEIVABLE_APPLICATIONS RAP WHERE AD.SOURCE_TABLE = 'RA' AND AD.SOURCE_ID = RAP.RECEIVABLE_APPLICATION_ID AND AD.SOURCE_TABLE_SECONDARY IS NULL AND AD.SOURCE_TYPE NOT LIKE 'REC'

View Text - HTML Formatted

SELECT RCTLGD.CUST_TRX_LINE_GL_DIST_ID ID
, RCTLGD.ACCOUNT_CLASS AE_LINE_TYPE
, RCTLGD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, OKL_ACCOUNTING_UTIL.GET_CONCAT_SEGMENTS(RCTLGD.CODE_COMBINATION_ID) CONCATE_SEGMENTS
, OKL_ACCOUNTING_UTIL.GET_CONCATE_DESC(RCTLGD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('AUTOGL_TYPE'
, RCTLGD.ACCOUNT_CLASS
, 222
, 222) AE_LINE_TYPE_MEANING
, TO_CHAR(NULL) CR_DR_FLAG
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR'
, DECODE(RCTT.TYPE
, 'INV'
, DECODE(RCTLGD.ACCOUNT_CLASS
, 'REC'
, 'D'
, 'C')
, 'CM'
, DECODE(RCTLGD.ACCOUNT_CLASS
, 'REC'
, 'C'
, 'D'))
, 101
, 101) DR_CR_FLAG_MEANING
, DECODE(RCTT.TYPE
, 'CM'
, (- RCTLGD.AMOUNT)
, RCTLGD.AMOUNT) AMOUNT
, RCTLGD.GL_DATE ACCOUNTING_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('AUTOGL_TYPE'
, RCTLGD.ACCOUNT_CLASS
, 222
, 222) TEMPLATE_NAME
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO'
, DECODE(RCTLGD.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, 0
, 0) POSTED
, RCTLGD.CUSTOMER_TRX_ID SOURCE_ID
, 'RA_CUSTOMER_TRX_LINES' SOURCE_TABLE
, RCTLGD.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM RA_CUST_TRX_LINE_GL_DIST RCTLGD
, RA_CUSTOMER_TRX RCT
, RA_CUST_TRX_TYPES RCTT
WHERE RCTLGD.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
AND RCTLGD.ACCOUNT_SET_FLAG = 'N'
AND RCT.CUST_TRX_TYPE_ID = RCTT.CUST_TRX_TYPE_ID UNION ALL SELECT AD.LINE_ID ID
, AD.SOURCE_TYPE AE_LINE_TYPE
, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, OKL_ACCOUNTING_UTIL.GET_CONCAT_SEGMENTS(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS
, OKL_ACCOUNTING_UTIL.GET_CONCATE_DESC(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) AE_LINE_TYPE_MEANING
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C') CR_DR_FLAG
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR'
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C')
, 101
, 101) DR_CR_FLAG_MEANING
, NVL(AMOUNT_CR
, AMOUNT_DR) AMOUNT
, AD.LAST_UPDATE_DATE ACCOUNTING_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) TEMPLATE_NAME
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO'
, DECODE (RAP.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, 0
, 0) POSTED
, RAP.CASH_RECEIPT_ID SOURCE_ID
, 'AR_CASH_RECEIPT' SOURCE_TABLE
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_DISTRIBUTIONS AD
, AR_RECEIVABLE_APPLICATIONS RAP
, AR_PAYMENT_SCHEDULES PS
WHERE AD.SOURCE_TABLE = 'RA'
AND AD.SOURCE_ID = RAP.RECEIVABLE_APPLICATION_ID
AND PS.PAYMENT_SCHEDULE_ID = RAP.APPLIED_PAYMENT_SCHEDULE_ID
AND RAP.STATUS = 'APP' UNION ALL SELECT AD.LINE_ID ID
, AD.SOURCE_TYPE AE_LINE_TYPE
, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, OKL_ACCOUNTING_UTIL.GET_CONCAT_SEGMENTS(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS
, OKL_ACCOUNTING_UTIL.GET_CONCATE_DESC(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) AE_LINE_TYPE_MEANING
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C') CR_DR_FLAG
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR'
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C')
, 101
, 101) DR_CR_FLAG_MEANING
, NVL(AMOUNT_CR
, AMOUNT_DR) AMOUNT
, AD.LAST_UPDATE_DATE ACCOUNTING_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) TEMPLATE_NAME
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO'
, DECODE (RAP.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, 0
, 0) POSTED
, RAP.CASH_RECEIPT_ID SOURCE_ID
, 'AR_CASH_RECEIPT' SOURCE_TABLE
, PS.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
FROM AR_DISTRIBUTIONS AD
, AR_RECEIVABLE_APPLICATIONS RAP
, AR_PAYMENT_SCHEDULES PS
WHERE AD.SOURCE_TABLE_SECONDARY = 'RA'
AND AD.SOURCE_ID_SECONDARY = RAP.RECEIVABLE_APPLICATION_ID
AND PS.PAYMENT_SCHEDULE_ID = RAP.APPLIED_PAYMENT_SCHEDULE_ID
AND RAP.STATUS = 'APP' UNION ALL SELECT AD.LINE_ID ID
, AD.SOURCE_TYPE AE_LINE_TYPE
, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, OKL_ACCOUNTING_UTIL.GET_CONCAT_SEGMENTS(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS
, OKL_ACCOUNTING_UTIL.GET_CONCATE_DESC(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) AE_LINE_TYPE_MEANING
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C') CR_DR_FLAG
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR'
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C')
, 101
, 101) DR_CR_FLAG_MEANING
, NVL(AMOUNT_CR
, AMOUNT_DR) AMOUNT
, AD.LAST_UPDATE_DATE ACCOUNTING_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) TEMPLATE_NAME
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO'
, DECODE (CRH.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, 0
, 0) POSTED
, CRH.CASH_RECEIPT_ID SOURCE_ID
, 'AR_CASH_RECEIPT' SOURCE_TABLE
, - 1 CUSTOMER_TRX_ID
FROM AR_DISTRIBUTIONS AD
, AR_CASH_RECEIPT_HISTORY CRH
WHERE AD.SOURCE_ID = CRH.CASH_RECEIPT_HISTORY_ID
AND AD.SOURCE_TABLE = 'CRH' UNION ALL SELECT AD.LINE_ID ID
, AD.SOURCE_TYPE AE_LINE_TYPE
, AD.CODE_COMBINATION_ID CODE_COMBINATION_ID
, OKL_ACCOUNTING_UTIL.GET_CONCAT_SEGMENTS(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS
, OKL_ACCOUNTING_UTIL.GET_CONCATE_DESC(AD.CODE_COMBINATION_ID) CONCATE_SEGMENTS_DESC
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) AE_LINE_TYPE_MEANING
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C') CR_DR_FLAG
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DR_CR'
, DECODE(AMOUNT_CR
, NULL
, 'D'
, 'C')
, 101
, 101) DR_CR_FLAG_MEANING
, NVL(AMOUNT_CR
, AMOUNT_DR) AMOUNT
, AD.LAST_UPDATE_DATE ACCOUNTING_DATE
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('DISTRIBUTION_SOURCE_TYPE'
, AD.SOURCE_TYPE
, 222
, 222) TEMPLATE_NAME
, OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('YES_NO'
, DECODE (RAP.GL_POSTED_DATE
, NULL
, 'N'
, 'Y')
, 0
, 0) POSTED
, RAP.CASH_RECEIPT_ID SOURCE_ID
, 'AR_CASH_RECEIPT' SOURCE_TABLE
, -1 CUSTOMER_TRX_ID
FROM AR_DISTRIBUTIONS AD
, AR_RECEIVABLE_APPLICATIONS RAP
WHERE AD.SOURCE_TABLE = 'RA'
AND AD.SOURCE_ID = RAP.RECEIVABLE_APPLICATION_ID
AND AD.SOURCE_TABLE_SECONDARY IS NULL
AND AD.SOURCE_TYPE NOT LIKE 'REC'