eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.IGI_CBR_ARC_V

Source

View Text - Preformatted


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, hz.party_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, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca, 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 = hca.account_number AND hz.party_id = hca.party_id AND nvl(rep.accounted_dr,0)-nvl(rep.accounted_cr,0) <> 0 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, hz.party_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, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca, 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 = hca.account_number AND hca.party_id = hz.party_id AND nvl(rep.accounted_dr,0)-nvl(rep.accounted_cr,0) <> 0 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, hz.party_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, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca, 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 = hca.account_number AND hz.party_id = hca.party_id AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0 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, hz.party_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) ,'AR_CASH_BASIS_DISTRIBUTIONSCMAPP_REC' ,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, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca, 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 in ( 'CMAPP_APP','CMAPP_REC') AND rep.reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS' AND hz.party_id = hca.party_id AND rep.reference26 = hca.account_number AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0 UNION ALL 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, hz.party_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, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca, 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 = hca.account_number AND hz.party_id = hca.party_id AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0 UNION ALL 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, nvl(rep.reference25,rep.reference24) trans_number, rep.reference26 customer_number, null customer_name, rep.accounting_date, nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) amount FROM IGI_AR_JOURNAL_INTERIM rep, GL_CODE_COMBINATIONS c, AR_CASH_RECEIPTS_ALL acr WHERE rep.code_combination_id = c.code_combination_id AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0 AND rep.reference28 in ('TRADE','MISC') AND rep.reference22 = to_char(acr.cash_receipt_id) AND rep.set_of_books_id = (select nvl(arc_cash_sob_id,0) from igi_ar_system_options) AND rep.reference30 != 'AR_CASH_BASIS_DISTRIBUTIONS'

View Text - HTML Formatted

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
, HZ.PARTY_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
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS HCA
, 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 = HCA.ACCOUNT_NUMBER
AND HZ.PARTY_ID = HCA.PARTY_ID
AND NVL(REP.ACCOUNTED_DR
, 0)-NVL(REP.ACCOUNTED_CR
, 0) <> 0 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
, HZ.PARTY_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
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS HCA
, 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 = HCA.ACCOUNT_NUMBER
AND HCA.PARTY_ID = HZ.PARTY_ID
AND NVL(REP.ACCOUNTED_DR
, 0)-NVL(REP.ACCOUNTED_CR
, 0) <> 0 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
, HZ.PARTY_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
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS HCA
, 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 = HCA.ACCOUNT_NUMBER
AND HZ.PARTY_ID = HCA.PARTY_ID
AND NVL(REP.ACCOUNTED_DR
, 0) - NVL(REP.ACCOUNTED_CR
, 0) <> 0 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
, HZ.PARTY_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)
, 'AR_CASH_BASIS_DISTRIBUTIONSCMAPP_REC'
, 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
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS HCA
, 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 IN ( 'CMAPP_APP'
, 'CMAPP_REC')
AND REP.REFERENCE30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
AND HZ.PARTY_ID = HCA.PARTY_ID
AND REP.REFERENCE26 = HCA.ACCOUNT_NUMBER
AND NVL(REP.ACCOUNTED_DR
, 0) - NVL(REP.ACCOUNTED_CR
, 0) <> 0 UNION ALL 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
, HZ.PARTY_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
, HZ_PARTIES HZ
, HZ_CUST_ACCOUNTS HCA
, 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 = HCA.ACCOUNT_NUMBER
AND HZ.PARTY_ID = HCA.PARTY_ID
AND NVL(REP.ACCOUNTED_DR
, 0) - NVL(REP.ACCOUNTED_CR
, 0) <> 0 UNION ALL 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
, NVL(REP.REFERENCE25
, REP.REFERENCE24) TRANS_NUMBER
, REP.REFERENCE26 CUSTOMER_NUMBER
, NULL CUSTOMER_NAME
, REP.ACCOUNTING_DATE
, NVL(REP.ACCOUNTED_DR
, 0) - NVL(REP.ACCOUNTED_CR
, 0) AMOUNT
FROM IGI_AR_JOURNAL_INTERIM REP
, GL_CODE_COMBINATIONS C
, AR_CASH_RECEIPTS_ALL ACR
WHERE REP.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
AND NVL(REP.ACCOUNTED_DR
, 0) - NVL(REP.ACCOUNTED_CR
, 0) <> 0
AND REP.REFERENCE28 IN ('TRADE'
, 'MISC')
AND REP.REFERENCE22 = TO_CHAR(ACR.CASH_RECEIPT_ID)
AND REP.SET_OF_BOOKS_ID = (SELECT NVL(ARC_CASH_SOB_ID
, 0)
FROM IGI_AR_SYSTEM_OPTIONS)
AND REP.REFERENCE30 != 'AR_CASH_BASIS_DISTRIBUTIONS'