[Home] [Help]
PACKAGE BODY: APPS.IGI_CBR_ARC_INTERFACE_PKG
Source
1 PACKAGE BODY IGI_CBR_ARC_INTERFACE_PKG as
2 /* $Header: igircbib.pls 120.5 2008/02/15 10:04:01 sharoy ship $ */
3
4
5 PROCEDURE Insert_Rows(p_request_id NUMBER, p_CashSetOfBooksId NUMBER)
6 IS
7
8 BEGIN
9
10 INSERT INTO igi_cbr_arc_interface
11 (SEGMENT1
12 ,SEGMENT2
13 ,SEGMENT3
14 ,SEGMENT4
15 ,SEGMENT5
16 ,SEGMENT6
17 ,SEGMENT7
18 ,SEGMENT8
19 ,SEGMENT9
20 ,SEGMENT10
21 ,SEGMENT11
22 ,SEGMENT12
23 ,SEGMENT13
24 ,SEGMENT14
25 ,SEGMENT15
26 ,SEGMENT16
27 ,SEGMENT17
28 ,SEGMENT18
29 ,SEGMENT19
30 ,SEGMENT20
31 ,SEGMENT21
32 ,SEGMENT22
33 ,SEGMENT23
34 ,SEGMENT24
35 ,SEGMENT25
36 ,SEGMENT26
37 ,SEGMENT27
38 ,SEGMENT28
39 ,SEGMENT29
40 ,SEGMENT30
41 ,REQUEST_ID
42 ,CURRENCY_CODE
43 ,DOC_SEQ_NUM
44 ,TRANS_NUMBER
45 ,CUSTOMER_NUMBER
46 ,CUSTOMER_NAME
47 ,ACCOUNTING_DATE
48 ,AMOUNT
49 )
50
51 SELECT
52
53 SEGMENT1
54 ,SEGMENT2
55 ,SEGMENT3
56 ,SEGMENT4
57 ,SEGMENT5
58 ,SEGMENT6
59 ,SEGMENT7
60 ,SEGMENT8
61 ,SEGMENT9
62 ,SEGMENT10
63 ,SEGMENT11
64 ,SEGMENT12
65 ,SEGMENT13
66 ,SEGMENT14
67 ,SEGMENT15
68 ,SEGMENT16
69 ,SEGMENT17
70 ,SEGMENT18
71 ,SEGMENT19
72 ,SEGMENT20
73 ,SEGMENT21
74 ,SEGMENT22
75 ,SEGMENT23
76 ,SEGMENT24
77 ,SEGMENT25
78 ,SEGMENT26
79 ,SEGMENT27
80 ,SEGMENT28
81 ,SEGMENT29
82 ,SEGMENT30
83 ,REQUEST_ID
84 ,CURRENCY_CODE
85 ,DOC_SEQ_NUM
86 ,TRANS_NUMBER
87 ,CUSTOMER_NUMBER
88 ,CUSTOMER_NAME
89 ,ACCOUNTING_DATE
90 ,AMOUNT
91
92 FROM (SELECT c.SEGMENT1, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5,
93 c.SEGMENT6, c.SEGMENT7, c.SEGMENT8, c.SEGMENT9, c.SEGMENT10,
94 c.SEGMENT11, c.SEGMENT12, c.SEGMENT13, c.SEGMENT14, c.SEGMENT15,
95 c.SEGMENT16, c.SEGMENT17, c.SEGMENT18, c.SEGMENT19, c.SEGMENT20,
96 c.SEGMENT21, c.SEGMENT22, c.SEGMENT23, c.SEGMENT24, c.SEGMENT25,
97 c.SEGMENT26, c.SEGMENT27, c.SEGMENT28, c.SEGMENT29, c.SEGMENT30,
98 rep.request_id, rep.currency_code, rct.doc_sequence_value doc_seq_num,
99 rep.reference25 trans_number, rep.reference26 customer_number,
100 hz.party_name customer_name, rep.accounting_date,
101 decode(rep.reference30||rep.reference29 , 'AR_ADJUSTMENTSADJ_ADJ' ,
102 nvl(rep.accounted_cr, 0)-nvl(rep.accounted_dr, 0) , 0 ) amount
103 FROM IGI_AR_JOURNAL_INTERIM rep, IGI_CBR_ARC_RECONCILE_V aat,
104 AR_ADJUSTMENTS aad, RA_CUSTOMER_TRX rct, HZ_PARTIES hz,
105 HZ_CUST_ACCOUNTS hca, GL_CODE_COMBINATIONS c, AR_LOOKUPS l
106 WHERE rep.CODE_COMBINATION_ID = c.code_combination_id(+)
107 AND l.lookup_type = 'ARRGTA_CATEGORIES'
108 AND rep.reference28 = l.lookup_code
109 AND rep.reference25 = aat.trans_num
110 AND rep.reference25 = rct.trx_number
111 AND rep.reference22 = to_char(aad.adjustment_id)
112 AND (rep.accounted_cr <> 0 OR rep.accounted_dr <> 0)
113 AND rep.reference25 is not null
114 AND rep.reference29 = 'ADJ_ADJ'
115 AND rep.reference30 = 'AR_ADJUSTMENTS'
116 AND rep.reference26 = hca.account_number
117 AND hz.party_id = hca.party_id
118 AND nvl(rep.accounted_dr,0)-nvl(rep.accounted_cr,0) <> 0
119 UNION
120 SELECT c.SEGMENT1, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5,
121 c.SEGMENT6, c.SEGMENT7, c.SEGMENT8, c.SEGMENT9, c.SEGMENT10,
122 c.SEGMENT11, c.SEGMENT12, c.SEGMENT13, c.SEGMENT14, c.SEGMENT15,
123 c.SEGMENT16, c.SEGMENT17, c.SEGMENT18, c.SEGMENT19, c.SEGMENT20,
124 c.SEGMENT21, c.SEGMENT22, c.SEGMENT23, c.SEGMENT24, c.SEGMENT25,
125 c.SEGMENT26, c.SEGMENT27, c.SEGMENT28, c.SEGMENT29, c.SEGMENT30,
126 rep.request_id, rep.currency_code, rct.doc_sequence_value doc_seq_num,
127 rep.reference25 trans_number, rep.reference26 customer_number,
128 hz.party_name customer_name, rep.accounting_date,
129 decode(rep.reference30||rep.reference29 ,'AR_RECEIVABLE_APPLICATIONSTRADE_GL' ,
130 nvl(rep.accounted_cr,0)-nvl(rep.accounted_dr,0) , 0 ) amount
131 FROM IGI_AR_JOURNAL_INTERIM rep, IGI_CBR_ARC_RECONCILE_V aat,
132 AR_CASH_RECEIPTS acr, RA_CUSTOMER_TRX rct, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca,
133 GL_CODE_COMBINATIONS c, AR_LOOKUPS l
134 WHERE rep.CODE_COMBINATION_ID = c.code_combination_id(+)
135 AND l.lookup_type = 'ARRGTA_CATEGORIES'
136 AND rep.reference28 = l.lookup_code
137 AND rep.reference25 = aat.trans_num
138 AND rep.reference25 = rct.trx_number
139 AND rep.reference22 = to_char(acr.cash_receipt_id)
140 AND (rep.accounted_cr <> 0 OR rep.accounted_dr <> 0)
141 AND rep.reference25 is not null
142 AND rep.reference29 = 'TRADE_GL'
143 AND rep.reference30 = 'AR_RECEIVABLE_APPLICATIONS'
144 AND rep.reference26 = hca.account_number
145 AND hca.party_id = hz.party_id
146 AND nvl(rep.accounted_dr,0)-nvl(rep.accounted_cr,0) <> 0
147 UNION
148 SELECT c.SEGMENT1, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5,
149 c.SEGMENT6, c.SEGMENT7, c.SEGMENT8, c.SEGMENT9, c.SEGMENT10,
150 c.SEGMENT11, c.SEGMENT12, c.SEGMENT13, c.SEGMENT14, c.SEGMENT15,
151 c.SEGMENT16, c.SEGMENT17, c.SEGMENT18, c.SEGMENT19, c.SEGMENT20,
152 c.SEGMENT21, c.SEGMENT22, c.SEGMENT23, c.SEGMENT24, c.SEGMENT25,
153 c.SEGMENT26, c.SEGMENT27, c.SEGMENT28, c.SEGMENT29, c.SEGMENT30,
154 rep.request_id, rep.currency_code, rct.doc_sequence_value doc_seq_num,
155 rep.reference25 trans_number, rep.reference26 customer_number,
156 hz.party_name customer_name, nvl(rep.accounting_date,sysdate),
157 decode(rep.reference30||rep.reference29 ,'RA_CUST_TRX_LINE_GL_DISTINV_REV' ,
158 nvl(rep.accounted_cr,0)-nvl(rep.accounted_dr,0) ,'RA_CUST_TRX_LINE_GL_DISTINV_TAX' ,
159 nvl(rep.accounted_cr,0)-nvl(rep.accounted_dr,0) ,'RA_CUST_TRX_LINE_GL_DISTCM_TAX' ,
160 nvl(rep.accounted_cr,0)-nvl(rep.accounted_dr,0) ,'RA_CUST_TRX_LINE_GL_DISTCM_REV' ,
161 nvl(rep.accounted_cr,0)-nvl(rep.accounted_dr,0) , 0 ) amount
162 FROM IGI_AR_JOURNAL_INTERIM rep, HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca,
163 RA_CUSTOMER_TRX rct, IGI_CBR_ARC_RECONCILE_V aat,
164 GL_CODE_COMBINATIONS c, AR_LOOKUPS l
165 WHERE rep.CODE_COMBINATION_ID = c.code_combination_id(+)
166 AND l.lookup_type = 'ARRGTA_CATEGORIES'
167 AND rep.reference28 = l.lookup_code
168 AND rep.reference25 = aat.trans_num
169 AND rep.reference25 = rct.trx_number
170 AND rep.reference22 = to_char(rct.customer_trx_id)
171 AND (nvl(rep.accounted_cr,0) <> 0 OR nvl(rep.accounted_dr,0) <> 0 )
172 AND rep.reference25 is not null
173 AND rep.reference29 <> 'INV_REC'
174 AND rep.reference29 <> 'CM_REC' AND rep.reference30 = 'RA_CUST_TRX_LINE_GL_DIST'
175 AND rep.reference26 = hca.account_number
176 AND hz.party_id = hca.party_id
177 AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0
178 UNION
179 SELECT c.SEGMENT1, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5,
180 c.SEGMENT6, c.SEGMENT7, c.SEGMENT8, c.SEGMENT9, c.SEGMENT10,
181 c.SEGMENT11, c.SEGMENT12, c.SEGMENT13, c.SEGMENT14, c.SEGMENT15,
182 c.SEGMENT16, c.SEGMENT17, c.SEGMENT18, c.SEGMENT19, c.SEGMENT20,
183 c.SEGMENT21, c.SEGMENT22, c.SEGMENT23, c.SEGMENT24, c.SEGMENT25,
184 c.SEGMENT26, c.SEGMENT27, c.SEGMENT28, c.SEGMENT29, c.SEGMENT30,
185 rep.request_id, rep.currency_code, rct.doc_sequence_value doc_seq_num,
186 rep.reference25 trans_number, rep.reference26 customer_number,
187 substr(hp.party_name,1,50) customer_name, rep.accounting_date,
188 decode(rep.reference30||rep.reference29 ,'AR_CASH_BASIS_DISTRIBUTIONSCMAPP_APP' ,
189 nvl(rep.accounted_dr,0)-nvl(rep.accounted_cr,0) ,'AR_CASH_BASIS_DISTRIBUTIONSCMAPP_REC' ,
190 nvl(rep.accounted_dr,0)-nvl(rep.accounted_cr,0)) amount
191 FROM IGI_AR_JOURNAL_INTERIM rep, IGI_CBR_ARC_RECONCILE_V aat,
192 RA_CUSTOMER_TRX rct, hz_parties hp, hz_cust_accounts hca, GL_CODE_COMBINATIONS c,
193 AR_LOOKUPS l
194 WHERE rep.CODE_COMBINATION_ID = c.code_combination_id(+)
195 AND l.lookup_type = 'ARRGTA_CATEGORIES'
196 AND rep.reference28 = l.lookup_code
197 AND rep.reference25 = aat.trans_num
198 AND rep.reference22 = to_char(rct.customer_trx_id)
199 AND (rep.accounted_cr <> 0 OR rep.accounted_dr <> 0)
200 AND rep.reference25 is not null
201 AND rep.reference29 in ( 'CMAPP_APP','CMAPP_REC')
202 AND rep.reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
203 AND rep.reference26 = hca.account_number
204 AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0
205 AND hca.party_id = hp.party_id
206 UNION ALL
207 SELECT c.SEGMENT1, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5,
208 c.SEGMENT6, c.SEGMENT7, c.SEGMENT8, c.SEGMENT9, c.SEGMENT10,
209 c.SEGMENT11, c.SEGMENT12, c.SEGMENT13, c.SEGMENT14, c.SEGMENT15,
210 c.SEGMENT16, c.SEGMENT17, c.SEGMENT18, c.SEGMENT19, c.SEGMENT20,
211 c.SEGMENT21, c.SEGMENT22, c.SEGMENT23, c.SEGMENT24, c.SEGMENT25,
212 c.SEGMENT26, c.SEGMENT27, c.SEGMENT28, c.SEGMENT29, c.SEGMENT30,
213 rep.request_id, rep.currency_code, acr.doc_sequence_value doc_seq_num,
214 rep.reference25 trans_number, rep.reference26 customer_number,
215 hz.party_name customer_name, rep.accounting_date,
216 decode(rep.reference30||rep.reference29 ,'AR_CASH_BASIS_DISTRIBUTIONSTRADE_APP' ,
217 nvl(-rep.accounted_cr,0)-nvl(-rep.accounted_dr,0)) amount
218 FROM IGI_AR_JOURNAL_INTERIM rep, IGI_CBR_ARC_RECONCILE_V aat, AR_CASH_RECEIPTS acr,
219 HZ_PARTIES hz, HZ_CUST_ACCOUNTS hca, GL_CODE_COMBINATIONS c, AR_LOOKUPS l
220 WHERE rep.CODE_COMBINATION_ID = c.code_combination_id(+)
221 AND l.lookup_type = 'ARRGTA_CATEGORIES'
222 AND rep.reference28 = l.lookup_code
223 AND rep.reference25 = aat.trans_num
224 AND rep.reference22 = to_char(acr.cash_receipt_id)
225 AND (rep.accounted_cr <> 0 OR rep.accounted_dr <> 0)
226 AND rep.reference25 is not null
227 AND rep.reference29 <> 'INV_REC'
228 AND rep.reference29 <> 'CM_REC'
229 AND rep.reference29 <> 'CMAPP_APP'
230 AND rep.reference30 = 'AR_CASH_BASIS_DISTRIBUTIONS'
231 AND rep.reference26 = hca.account_number
232 AND hz.party_id = hca.party_id
233 AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0
234 UNION ALL
235 SELECT c.SEGMENT1, c.SEGMENT2, c.SEGMENT3, c.SEGMENT4, c.SEGMENT5,
236 c.SEGMENT6, c.SEGMENT7, c.SEGMENT8, c.SEGMENT9, c.SEGMENT10,
237 c.SEGMENT11, c.SEGMENT12, c.SEGMENT13, c.SEGMENT14, c.SEGMENT15,
238 c.SEGMENT16, c.SEGMENT17, c.SEGMENT18, c.SEGMENT19, c.SEGMENT20,
239 c.SEGMENT21, c.SEGMENT22, c.SEGMENT23, c.SEGMENT24, c.SEGMENT25,
240 c.SEGMENT26, c.SEGMENT27, c.SEGMENT28, c.SEGMENT29, c.SEGMENT30,
241 rep.request_id, rep.currency_code, acr.doc_sequence_value doc_seq_num,
242 nvl(rep.reference25,rep.reference24) trans_number, rep.reference26 customer_number,
243 null customer_name, rep.accounting_date,
244 nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) amount
245 FROM IGI_AR_JOURNAL_INTERIM rep, GL_CODE_COMBINATIONS c, AR_CASH_RECEIPTS_ALL acr
246 WHERE rep.code_combination_id = c.code_combination_id
247 AND nvl(rep.accounted_dr,0) - nvl(rep.accounted_cr,0) <> 0
248 AND rep.reference28 in ('TRADE','MISC')
249 AND rep.reference22 = to_char(acr.cash_receipt_id)
250 AND rep.set_of_books_id = nvl(p_CashSetOfBooksId,0)
251 AND rep.reference30 <> 'AR_CASH_BASIS_DISTRIBUTIONS') icav
252 WHERE icav.request_id = p_request_id;
253 END Insert_Rows;
254
255
256 END IGI_CBR_ARC_INTERFACE_PKG;