View: IGI_CBR_ARC_V

Product:	IGI - Public Sector Financials International
Description:	This view queries up all the reconciled transaction records.
Implementation/DBA Data:	APPS.IGI_CBR_ARC_V

View Text

SELECT C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5
, C.SEGMENT6
, C.SEGMENT7
, C.SEGMENT8
, C.SEGMENT9
, C.SEGMENT10
, C.SEGMENT11
, C.SEGMENT12
, C.SEGMENT13
, C.SEGMENT14
, C.SEGMENT15
, C.SEGMENT16
, C.SEGMENT17
, C.SEGMENT18
, C.SEGMENT19
, C.SEGMENT20
, C.SEGMENT21
, C.SEGMENT22
, C.SEGMENT23
, C.SEGMENT24
, C.SEGMENT25
, C.SEGMENT26
, C.SEGMENT27
, C.SEGMENT28
, C.SEGMENT29
, C.SEGMENT30
, REP.REQUEST_ID
, REP.CURRENCY_CODE
, RCT.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
, REP.REFERENCE25 TRANS_NUMBER
, REP.REFERENCE26 CUSTOMER_NUMBER
, RC.CUSTOMER_NAME CUSTOMER_NAME
, REP.ACCOUNTING_DATE
, DECODE(REP.REFERENCE30||REP.REFERENCE29
, 'AR_ADJUSTMENTSADJ_ADJ'
, NVL(REP.ACCOUNTED_CR
, 0)-NVL(REP.ACCOUNTED_DR
, 0)
, 0 ) AMOUNT
FROM IGI_AR_JOURNAL_INTERIM REP
, IGI_CBR_ARC_RECONCILE_V AAT
, AR_ADJUSTMENTS AAD
, RA_CUSTOMER_TRX RCT
, RA_CUSTOMERS RC
, GL_CODE_COMBINATIONS C
, AR_LOOKUPS L
WHERE REP.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID(+)
AND L.LOOKUP_TYPE = 'ARRGTA_CATEGORIES'
AND REP.REFERENCE28 = L.LOOKUP_CODE
AND REP.REFERENCE25 = AAT.TRANS_NUM
AND REP.REFERENCE25 = RCT.TRX_NUMBER
AND REP.REFERENCE22 = TO_CHAR(AAD.ADJUSTMENT_ID)
AND (REP.ACCOUNTED_CR <> 0 OR REP.ACCOUNTED_DR <> 0)
AND REP.REFERENCE25 IS NOT NULL
AND REP.REFERENCE29 = 'ADJ_ADJ'
AND REP.REFERENCE30 = 'AR_ADJUSTMENTS'
AND REP.REFERENCE26 = RC.CUSTOMER_NUMBER UNION SELECT C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5
, C.SEGMENT6
, C.SEGMENT7
, C.SEGMENT8
, C.SEGMENT9
, C.SEGMENT10
, C.SEGMENT11
, C.SEGMENT12
, C.SEGMENT13
, C.SEGMENT14
, C.SEGMENT15
, C.SEGMENT16
, C.SEGMENT17
, C.SEGMENT18
, C.SEGMENT19
, C.SEGMENT20
, C.SEGMENT21
, C.SEGMENT22
, C.SEGMENT23
, C.SEGMENT24
, C.SEGMENT25
, C.SEGMENT26
, C.SEGMENT27
, C.SEGMENT28
, C.SEGMENT29
, C.SEGMENT30
, REP.REQUEST_ID
, REP.CURRENCY_CODE
, RCT.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
, REP.REFERENCE25 TRANS_NUMBER
, REP.REFERENCE26 CUSTOMER_NUMBER
, RC.CUSTOMER_NAME CUSTOMER_NAME
, REP.ACCOUNTING_DATE
, DECODE(REP.REFERENCE30||REP.REFERENCE29
, 'AR_RECEIVABLE_APPLICATIONSTRADE_GL'
, NVL(REP.ACCOUNTED_CR
, 0)-NVL(REP.ACCOUNTED_DR
, 0)
, 0 ) AMOUNT
FROM IGI_AR_JOURNAL_INTERIM REP
, IGI_CBR_ARC_RECONCILE_V AAT
, AR_CASH_RECEIPTS ACR
, RA_CUSTOMER_TRX RCT
, RA_CUSTOMERS RC
, GL_CODE_COMBINATIONS C
, AR_LOOKUPS L
WHERE REP.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID(+)
AND L.LOOKUP_TYPE = 'ARRGTA_CATEGORIES'
AND REP.REFERENCE28 = L.LOOKUP_CODE
AND REP.REFERENCE25 = AAT.TRANS_NUM
AND REP.REFERENCE25 = RCT.TRX_NUMBER
AND REP.REFERENCE22 = TO_CHAR(ACR.CASH_RECEIPT_ID)
AND (REP.ACCOUNTED_CR <> 0 OR REP.ACCOUNTED_DR <> 0)
AND REP.REFERENCE25 IS NOT NULL
AND REP.REFERENCE29 = 'TRADE_GL'
AND REP.REFERENCE30 = 'AR_RECEIVABLE_APPLICATIONS'
AND REP.REFERENCE26 = RC.CUSTOMER_NUMBER UNION SELECT C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5
, C.SEGMENT6
, C.SEGMENT7
, C.SEGMENT8
, C.SEGMENT9
, C.SEGMENT10
, C.SEGMENT11
, C.SEGMENT12
, C.SEGMENT13
, C.SEGMENT14
, C.SEGMENT15
, C.SEGMENT16
, C.SEGMENT17
, C.SEGMENT18
, C.SEGMENT19
, C.SEGMENT20
, C.SEGMENT21
, C.SEGMENT22
, C.SEGMENT23
, C.SEGMENT24
, C.SEGMENT25
, C.SEGMENT26
, C.SEGMENT27
, C.SEGMENT28
, C.SEGMENT29
, C.SEGMENT30
, REP.REQUEST_ID
, REP.CURRENCY_CODE
, RCT.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
, REP.REFERENCE25 TRANS_NUMBER
, REP.REFERENCE26 CUSTOMER_NUMBER
, RC.CUSTOMER_NAME CUSTOMER_NAME
, NVL(REP.ACCOUNTING_DATE
, SYSDATE)
, DECODE(REP.REFERENCE30||REP.REFERENCE29
, 'RA_CUST_TRX_LINE_GL_DISTINV_REV'
, NVL(REP.ACCOUNTED_CR
, 0)-NVL(REP.ACCOUNTED_DR
, 0)
, 'RA_CUST_TRX_LINE_GL_DISTINV_TAX'
, NVL(REP.ACCOUNTED_CR
, 0)-NVL(REP.ACCOUNTED_DR
, 0)
, 'RA_CUST_TRX_LINE_GL_DISTCM_TAX'
, NVL(REP.ACCOUNTED_CR
, 0)-NVL(REP.ACCOUNTED_DR
, 0)
, 'RA_CUST_TRX_LINE_GL_DISTCM_REV'
, NVL(REP.ACCOUNTED_CR
, 0)-NVL(REP.ACCOUNTED_DR
, 0)
, 0 ) AMOUNT
FROM IGI_AR_JOURNAL_INTERIM REP
, RA_CUSTOMERS RC
, RA_CUSTOMER_TRX RCT
, IGI_CBR_ARC_RECONCILE_V AAT
, GL_CODE_COMBINATIONS C
, AR_LOOKUPS L
WHERE REP.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID(+)
AND L.LOOKUP_TYPE = 'ARRGTA_CATEGORIES'
AND REP.REFERENCE28 = L.LOOKUP_CODE
AND REP.REFERENCE25 = AAT.TRANS_NUM
AND REP.REFERENCE25 = RCT.TRX_NUMBER
AND REP.REFERENCE22 = TO_CHAR(RCT.CUSTOMER_TRX_ID)
AND (NVL(REP.ACCOUNTED_CR
, 0) <> 0 OR NVL(REP.ACCOUNTED_DR
, 0) <> 0 )
AND REP.REFERENCE25 IS NOT NULL
AND REP.REFERENCE29 != 'INV_REC'
AND REP.REFERENCE29 != 'CM_REC'
AND REP.REFERENCE30 = 'RA_CUST_TRX_LINE_GL_DIST'
AND REP.REFERENCE26 = RC.CUSTOMER_NUMBER UNION SELECT C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5
, C.SEGMENT6
, C.SEGMENT7
, C.SEGMENT8
, C.SEGMENT9
, C.SEGMENT10
, C.SEGMENT11
, C.SEGMENT12
, C.SEGMENT13
, C.SEGMENT14
, C.SEGMENT15
, C.SEGMENT16
, C.SEGMENT17
, C.SEGMENT18
, C.SEGMENT19
, C.SEGMENT20
, C.SEGMENT21
, C.SEGMENT22
, C.SEGMENT23
, C.SEGMENT24
, C.SEGMENT25
, C.SEGMENT26
, C.SEGMENT27
, C.SEGMENT28
, C.SEGMENT29
, C.SEGMENT30
, REP.REQUEST_ID
, REP.CURRENCY_CODE
, RCT.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
, REP.REFERENCE25 TRANS_NUMBER
, REP.REFERENCE26 CUSTOMER_NUMBER
, RC.CUSTOMER_NAME CUSTOMER_NAME
, REP.ACCOUNTING_DATE
, DECODE(REP.REFERENCE30||REP.REFERENCE29
, 'AR_CASH_BASIS_DISTRIBUTIONSCMAPP_APP'
, NVL(REP.ACCOUNTED_DR
, 0)-NVL(REP.ACCOUNTED_CR
, 0)) AMOUNT
FROM IGI_AR_JOURNAL_INTERIM REP
, IGI_CBR_ARC_RECONCILE_V AAT
, RA_CUSTOMER_TRX RCT
, RA_CUSTOMERS RC
, GL_CODE_COMBINATIONS C
, AR_LOOKUPS L
WHERE REP.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID(+)
AND L.LOOKUP_TYPE = 'ARRGTA_CATEGORIES'
AND REP.REFERENCE28 = L.LOOKUP_CODE
AND REP.REFERENCE25 = AAT.TRANS_NUM
AND REP.REFERENCE22 = TO_CHAR(RCT.CUSTOMER_TRX_ID)
AND (REP.ACCOUNTED_CR <> 0 OR REP.ACCOUNTED_DR <> 0)
AND REP.REFERENCE25 IS NOT NULL
AND REP.REFERENCE29 = 'CMAPP_APP'
AND REP.REFERENCE30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND REP.REFERENCE26 = RC.CUSTOMER_NUMBER UNION SELECT C.SEGMENT1
, C.SEGMENT2
, C.SEGMENT3
, C.SEGMENT4
, C.SEGMENT5
, C.SEGMENT6
, C.SEGMENT7
, C.SEGMENT8
, C.SEGMENT9
, C.SEGMENT10
, C.SEGMENT11
, C.SEGMENT12
, C.SEGMENT13
, C.SEGMENT14
, C.SEGMENT15
, C.SEGMENT16
, C.SEGMENT17
, C.SEGMENT18
, C.SEGMENT19
, C.SEGMENT20
, C.SEGMENT21
, C.SEGMENT22
, C.SEGMENT23
, C.SEGMENT24
, C.SEGMENT25
, C.SEGMENT26
, C.SEGMENT27
, C.SEGMENT28
, C.SEGMENT29
, C.SEGMENT30
, REP.REQUEST_ID
, REP.CURRENCY_CODE
, ACR.DOC_SEQUENCE_VALUE DOC_SEQ_NUM
, REP.REFERENCE25 TRANS_NUMBER
, REP.REFERENCE26 CUSTOMER_NUMBER
, RC.CUSTOMER_NAME CUSTOMER_NAME
, REP.ACCOUNTING_DATE
, DECODE(REP.REFERENCE30||REP.REFERENCE29
, 'AR_CASH_BASIS_DISTRIBUTIONSTRADE_APP'
, NVL(-REP.ACCOUNTED_CR
, 0)-NVL(-REP.ACCOUNTED_DR
, 0)) AMOUNT
FROM IGI_AR_JOURNAL_INTERIM REP
, IGI_CBR_ARC_RECONCILE_V AAT
, AR_CASH_RECEIPTS ACR
, RA_CUSTOMERS RC
, GL_CODE_COMBINATIONS C
, AR_LOOKUPS L
WHERE REP.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID(+)
AND L.LOOKUP_TYPE = 'ARRGTA_CATEGORIES'
AND REP.REFERENCE28 = L.LOOKUP_CODE
AND REP.REFERENCE25 = AAT.TRANS_NUM
AND REP.REFERENCE22 = TO_CHAR(ACR.CASH_RECEIPT_ID)
AND (REP.ACCOUNTED_CR <> 0 OR REP.ACCOUNTED_DR <> 0)
AND REP.REFERENCE25 IS NOT NULL
AND REP.REFERENCE29 != 'INV_REC'
AND REP.REFERENCE29 != 'CM_REC'
AND REP.REFERENCE29 != 'CMAPP_APP'
AND REP.REFERENCE30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND REP.REFERENCE26 = RC.CUSTOMER_NUMBER

Columns