[Home] [Help]
PACKAGE BODY: APPS.AR_UNPOSTED_ITEM_UPG
Source
1 PACKAGE BODY ar_unposted_item_upg AS
2 /* $Header: ARCBUPGB.pls 120.2.12010000.2 2008/11/14 05:47:39 dgaurab ship $ */
3
4 g_ae_sys_rec arp_acct_main.ae_sys_rec_type;
5
6 PROCEDURE Init_Curr_Details
7 (p_sob_id IN NUMBER,
8 p_org_id IN NUMBER,
9 x_accounting_method IN OUT NOCOPY ar_system_parameters.accounting_method%TYPE)
10 IS
11 BEGIN
12 SELECT sob.set_of_books_id,
13 sob.chart_of_accounts_id,
14 sob.currency_code,
15 c.precision,
16 c.minimum_accountable_unit,
17 sysp.code_combination_id_gain,
18 sysp.code_combination_id_loss,
19 sysp.code_combination_id_round,
20 sysp.accounting_method
21 INTO g_ae_sys_rec.set_of_books_id,
22 g_ae_sys_rec.coa_id,
23 g_ae_sys_rec.base_currency,
24 g_ae_sys_rec.base_precision,
25 g_ae_sys_rec.base_min_acc_unit,
26 g_ae_sys_rec.gain_cc_id,
27 g_ae_sys_rec.loss_cc_id,
28 g_ae_sys_rec.round_cc_id,
29 x_accounting_method
30 FROM ar_system_parameters_all sysp,
31 gl_sets_of_books sob,
32 fnd_currencies c
33 WHERE sysp.org_id = p_org_id
34 AND sob.set_of_books_id = sysp.set_of_books_id --would be the row returned from multi org view
35 AND sob.currency_code = c.currency_code;
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN
38 NULL;
39 WHEN OTHERS THEN
40 RAISE;
41 END Init_Curr_Details;
42
43
44
45 PROCEDURE upgrade_11i_cash_basis
46 (l_table_owner IN VARCHAR2, -- AR
47 l_table_name IN VARCHAR2, -- AR_RECEIVABLE_APPLICATIONS_ALL
48 l_script_name IN VARCHAR2, -- ar120cbupi.sql
49 l_worker_id IN VARCHAR2,
50 l_num_workers IN VARCHAR2,
51 l_batch_size IN VARCHAR2)
52 AS
53
54 l_start_rowid rowid;
55 l_end_rowid rowid;
56 l_any_rows_to_process boolean;
57 l_rows_processed number := 0;
58
59 l_status VARCHAR2(10);
60 l_industry VARCHAR2(10);
61 l_res BOOLEAN := FALSE;
62 no_global EXCEPTION;
63
64
65 CURSOR c_app(p_start_rowid IN ROWID,
66 p_end_rowid IN ROWID)
67 IS
68 SELECT app.*
69 FROM ar_receivable_applications_all app,
70 ar_system_parameters_all ars
71 WHERE app.status = 'APP'
72 AND app.upgrade_method IS NULL
73 AND app.org_id = ars.org_id
74 AND app.posting_control_id = -3
75 AND app.rowid >= p_start_rowid
76 AND app.rowid <= p_end_rowid
77 AND ars.accounting_method = 'CASH'
78 AND NOT EXISTS (SELECT '1'
79 FROM psa_trx_types_all psa,
80 ra_customer_trx_all inv
81 WHERE inv.customer_trx_id = app.applied_customer_trx_id
82 AND inv.cust_trx_type_id = psa.psa_trx_type_id)
83 ORDER BY app.org_id;
84
85
86
87 l_org_id NUMBER := -9999;
88 l_app_rec ar_receivable_applications%ROWTYPE;
89 l_line_acctd_amt NUMBER;
90 l_tax_acctd_amt NUMBER;
91 l_frt_acctd_amt NUMBER;
92 l_chrg_acctd_amt NUMBER;
93 l_ed_line_acctd_amt NUMBER;
94 l_ed_tax_acctd_amt NUMBER;
95 l_ed_frt_acctd_amt NUMBER;
96 l_ed_chrg_acctd_amt NUMBER;
97 l_ued_line_acctd_amt NUMBER;
98 l_ued_tax_acctd_amt NUMBER;
99 l_ued_frt_acctd_amt NUMBER;
100 l_ued_chrg_acctd_amt NUMBER;
101 dummy VARCHAR2(1);
102 l_ra_list DBMS_SQL.NUMBER_TABLE;
103 erase_ra_list DBMS_SQL.NUMBER_TABLE;
104 i NUMBER := 0;
105
106 g_ind_current NUMBER := -9;
107 g_run_tot NUMBER := 0;
108 g_run_acctd_tot NUMBER := 0;
109 l_gt_id NUMBER := 0;
110 l_accounting_method VARCHAR2(30);
111 end_process_stop EXCEPTION;
112
113 BEGIN
114
115 /* ------ Initialize the rowid ranges ------ */
116 ad_parallel_updates_pkg.initialize_rowid_range(
117 ad_parallel_updates_pkg.ROWID_RANGE,
118 l_table_owner,
119 l_table_name,
120 l_script_name,
121 l_worker_id,
122 l_num_workers,
123 l_batch_size, 0);
124
125 /* ------ Get rowid ranges ------ */
126 ad_parallel_updates_pkg.get_rowid_range(
127 l_start_rowid,
128 l_end_rowid,
129 l_any_rows_to_process,
130 l_batch_size,
131 TRUE);
132
133
134 WHILE ( l_any_rows_to_process = TRUE )
135 LOOP
136
137 l_rows_processed := 0;
138
139
140
141 -------------------------------------------
142 -- Get all invoices for the applications
143 -------------------------------------------
144 INSERT INTO RA_AR_GT
145 ( GT_ID ,
146 AMT ,
147 ACCTD_AMT ,
148 ACCOUNT_CLASS ,
149 CCID_SECONDARY ,
150 REF_CUST_TRX_LINE_GL_DIST_ID,
151 REF_CUSTOMER_TRX_LINE_ID ,
152 REF_CUSTOMER_TRX_ID ,
153 TO_CURRENCY ,
154 BASE_CURRENCY ,
155 -- ADJ and APP Elmt
156 DIST_AMT ,
157 DIST_ACCTD_AMT ,
158 DIST_CHRG_AMT ,
159 DIST_CHRG_ACCTD_AMT ,
160 DIST_FRT_AMT ,
161 DIST_FRT_ACCTD_AMT ,
162 DIST_TAX_AMT ,
163 DIST_TAX_ACCTD_AMT ,
164 -- Buc
165 tl_alloc_amt ,
166 tl_alloc_acctd_amt ,
167 tl_chrg_alloc_amt ,
168 tl_chrg_alloc_acctd_amt,
169 tl_frt_alloc_amt ,
170 tl_frt_alloc_acctd_amt,
171 tl_tax_alloc_amt ,
172 tl_tax_alloc_acctd_amt,
173 -- ED Elmt
174 DIST_ed_AMT,
175 DIST_ed_ACCTD_AMT,
176 DIST_ed_chrg_AMT,
177 DIST_ed_chrg_ACCTD_AMT,
178 DIST_ed_frt_AMT ,
179 DIST_ed_frt_ACCTD_AMT,
180 DIST_ed_tax_AMT ,
181 DIST_ed_tax_ACCTD_AMT,
182 --
183 tl_ed_alloc_amt ,
184 tl_ed_alloc_acctd_amt ,
185 tl_ed_chrg_alloc_amt ,
186 tl_ed_chrg_alloc_acctd_amt,
187 tl_ed_frt_alloc_amt ,
188 tl_ed_frt_alloc_acctd_amt,
189 tl_ed_tax_alloc_amt ,
190 tl_ed_tax_alloc_acctd_amt,
191 -- UNED
192 DIST_uned_AMT ,
193 DIST_uned_ACCTD_AMT ,
194 DIST_uned_chrg_AMT ,
195 DIST_uned_chrg_ACCTD_AMT ,
196 DIST_uned_frt_AMT ,
197 DIST_uned_frt_ACCTD_AMT ,
198 DIST_uned_tax_AMT ,
199 DIST_uned_tax_ACCTD_AMT ,
200 --
201 tl_uned_alloc_amt ,
202 tl_uned_alloc_acctd_amt ,
203 tl_uned_chrg_alloc_amt ,
204 tl_uned_chrg_alloc_acctd_amt,
205 tl_uned_frt_alloc_amt ,
206 tl_uned_frt_alloc_acctd_amt,
207 tl_uned_tax_alloc_amt ,
208 tl_uned_tax_alloc_acctd_amt,
209 --
210 source_type ,
211 source_table ,
212 source_id ,
213 line_type,
214 --
215 group_id,
216 source_data_key1 ,
217 source_data_key2 ,
218 source_data_key3 ,
219 source_data_key4 ,
220 source_data_key5 ,
221 gp_level,
222 --
223 set_of_books_id,
224 sob_type,
225 se_gt_id,
226 tax_link_id,
227 tax_inc_flag
228 )
229 SELECT
230 0 -- GT_ID
231 , ctlgd.amount -- AMT
232 , ctlgd.acctd_amount -- ACCTD_AMT
233 , DECODE(ctl.line_type,'LINE','REV',
234 'TAX','TAX',
235 'FREIGHT','FREIGHT',
236 'CHARGES','CHARGES',
237 'CB','REV') -- ACCOUNT_CLASS
238 , DECODE(ctlgd.collected_tax_ccid,
239 NULL, ctlgd.code_combination_id,
240 0 , ctlgd.code_combination_id,
241 ctlgd.collected_tax_ccid) -- CCID_SECONDARY
242 , ctlgd.cust_trx_line_gl_dist_id --REF_CUST_TRX_LINE_GL_DIST_ID
243 , DECODE(ctl.line_type,'LINE' ,-6,
244 'TAX' ,-8,
245 'FREIGHT',-9,
246 'CHARGES',-7,
247 'CB' ,-6)
248 --, ctlgd.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
249 , trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
250 , trx.invoice_currency_code -- TO_CURRENCY
251 , NULL -- BASE_CURRENCY
252 -- ADJ and APP Elmt
253 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
254 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
255 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
256 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
257 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
258 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
259 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
260 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
261 -- Buc
262 , 0 -- tl_alloc_amt
263 , 0 -- tl_alloc_acctd_amt
264 , 0 -- tl_chrg_alloc_amt
265 , 0 -- tl_chrg_alloc_acctd_amt
266 , 0 -- tl_frt_alloc_amt
267 , 0 -- tl_frt_alloc_acctd_amt
268 , 0 -- tl_tax_alloc_amt
269 , 0 -- tl_tax_alloc_acctd_amt
270 -- ED Elmt
271 , DECODE(ctl.line_type,'LINE' ,ctlgd.amount,0) --DIST_ed_AMT
272 , DECODE(ctl.line_type,'LINE' ,ctlgd.acctd_amount,0) --DIST_ed_ACCTD_AMT
273 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) --DIST_ed_chrg_AMT
274 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) --DIST_ed_chrg_ACCTD_AMT
275 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) --DIST_ed_frt_AMT
276 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) --DIST_ed_frt_ACCTD_AMT
277 , DECODE(ctl.line_type,'TAX' ,ctlgd.amount,0) --DIST_ed_tax_AMT
278 , DECODE(ctl.line_type,'TAX' ,ctlgd.acctd_amount,0) --DIST_ed_tax_ACCTD_AMT
279 --
280 , 0 -- tl_ed_alloc_amt
281 , 0 -- tl_ed_alloc_acctd_amt
282 , 0 -- tl_ed_chrg_alloc_amt
283 , 0 -- tl_ed_chrg_alloc_acctd_amt
284 , 0 -- tl_ed_frt_alloc_amt
285 , 0 -- tl_ed_frt_alloc_acctd_amt
286 , 0 -- tl_ed_tax_alloc_amt
287 , 0 -- tl_ed_tax_alloc_acctd_amt
288 -- UNED
289 , DECODE(ctl.line_type,'LINE' ,ctlgd.amount,0) --DIST_uned_AMT
290 , DECODE(ctl.line_type,'LINE' ,ctlgd.acctd_amount,0) --DIST_uned_ACCTD_AMT
291 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) --DIST_uned_chrg_AMT
292 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) --DIST_uned_chrg_ACCTD_AMT
293 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) --DIST_uned_frt_AMT
294 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) --DIST_uned_frt_ACCTD_AMT
295 , DECODE(ctl.line_type,'TAX' ,ctlgd.amount,0) --DIST_uned_tax_AMT
296 , DECODE(ctl.line_type,'TAX' ,ctlgd.acctd_amount,0) --DIST_uned_tax_ACCTD_AMT
297 --
298 , 0 -- tl_uned_alloc_amt
299 , 0 -- tl_uned_alloc_acctd_amt
300 , 0 -- tl_uned_chrg_alloc_amt
301 , 0 -- tl_uned_chrg_alloc_acctd_amt
302 , 0 -- tl_uned_frt_alloc_amt
303 , 0 -- tl_uned_frt_alloc_acctd_amt
304 , 0 -- tl_uned_tax_alloc_amt
305 , 0 -- tl_uned_tax_alloc_acctd_amt
306 --
307 , NULL -- source_type
308 , 'CTLGD' -- source_table
309 , NULL -- source_id
310 , ctl.line_type -- line_type
311 --
312 , NULL -- group_id
313 , '00' -- source_data_key1
314 , '00' -- source_data_key2
315 , '00' -- source_data_key3
316 , '00' -- source_data_key4
317 , '00' -- source_data_key5
318 , 'D' -- gp_level
319 --
320 , trx.set_of_books_id -- set_of_books_id
321 , 'P' -- sob_type
322 , USERENV('SESSIONID') -- se_gt_id
323 , NULL -- tax_link_id
324 , NULL -- tax_inc_flag
325 FROM (SELECT applied_customer_trx_id,
326 org_id
327 FROM ar_receivable_applications_all
328 WHERE status = 'APP'
329 AND upgrade_method IS NULL
330 AND posting_control_id = -3
331 AND rowid >= l_start_rowid
332 AND rowid <= l_end_rowid
333 GROUP BY applied_customer_trx_id,
334 org_id ) app,
335 ar_system_parameters_all ars,
336 ra_customer_trx_all trx,
337 ra_customer_trx_lines_all ctl,
338 ra_cust_trx_line_gl_dist_all ctlgd
339 WHERE ars.accounting_method = 'CASH'
340 AND app.org_id = ars.org_id
341 AND app.applied_customer_trx_id = trx.customer_trx_id
342 AND trx.customer_trx_id = ctl.customer_trx_id
343 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
344 AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
345 AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
346 AND ctlgd.account_set_flag = 'N'
347 AND NOT EXISTS (SELECT '1'
348 FROM psa_trx_types_all psa,
349 ra_customer_trx_all inv
350 WHERE inv.customer_trx_id = app.applied_customer_trx_id
351 AND inv.cust_trx_type_id = psa.psa_trx_type_id);
352
353
354 -------------------------------------------------------
355 -- Get the adjustments on those invoice being applied
356 -------------------------------------------------------
357 INSERT INTO RA_AR_GT
358 ( GT_ID ,
359 AMT ,
360 ACCTD_AMT ,
361 ACCOUNT_CLASS ,
362 CCID_SECONDARY ,
363 REF_CUST_TRX_LINE_GL_DIST_ID,
364 REF_CUSTOMER_TRX_LINE_ID ,
365 REF_CUSTOMER_TRX_ID ,
366 TO_CURRENCY ,
367 BASE_CURRENCY ,
368 -- ADJ and APP Elmt
369 DIST_AMT ,
370 DIST_ACCTD_AMT ,
371 DIST_CHRG_AMT ,
372 DIST_CHRG_ACCTD_AMT ,
373 DIST_FRT_AMT ,
374 DIST_FRT_ACCTD_AMT ,
375 DIST_TAX_AMT ,
376 DIST_TAX_ACCTD_AMT ,
377 -- Buc
378 tl_alloc_amt ,
379 tl_alloc_acctd_amt ,
380 tl_chrg_alloc_amt ,
381 tl_chrg_alloc_acctd_amt,
382 tl_frt_alloc_amt ,
383 tl_frt_alloc_acctd_amt,
384 tl_tax_alloc_amt ,
385 tl_tax_alloc_acctd_amt,
386 -- ED Elmt
387 DIST_ed_AMT,
388 DIST_ed_ACCTD_AMT,
389 DIST_ed_chrg_AMT,
390 DIST_ed_chrg_ACCTD_AMT,
391 DIST_ed_frt_AMT ,
392 DIST_ed_frt_ACCTD_AMT,
393 DIST_ed_tax_AMT ,
394 DIST_ed_tax_ACCTD_AMT,
395 --
396 tl_ed_alloc_amt ,
397 tl_ed_alloc_acctd_amt ,
398 tl_ed_chrg_alloc_amt ,
399 tl_ed_chrg_alloc_acctd_amt,
400 tl_ed_frt_alloc_amt ,
401 tl_ed_frt_alloc_acctd_amt,
402 tl_ed_tax_alloc_amt ,
403 tl_ed_tax_alloc_acctd_amt,
404 --
405 -- UNED
406 DIST_uned_AMT ,
407 DIST_uned_ACCTD_AMT ,
408 DIST_uned_chrg_AMT ,
409 DIST_uned_chrg_ACCTD_AMT ,
410 DIST_uned_frt_AMT ,
411 DIST_uned_frt_ACCTD_AMT ,
412 DIST_uned_tax_AMT ,
413 DIST_uned_tax_ACCTD_AMT ,
414 --
415 tl_uned_alloc_amt ,
416 tl_uned_alloc_acctd_amt ,
417 tl_uned_chrg_alloc_amt ,
418 tl_uned_chrg_alloc_acctd_amt,
419 tl_uned_frt_alloc_amt ,
420 tl_uned_frt_alloc_acctd_amt,
421 tl_uned_tax_alloc_amt ,
422 tl_uned_tax_alloc_acctd_amt,
423 --
424 source_type ,
425 source_table ,
426 source_id ,
427 line_type,
428 --
429 group_id,
430 source_data_key1 ,
431 source_data_key2 ,
432 source_data_key3 ,
433 source_data_key4 ,
434 source_data_key5 ,
435 gp_level,
436 --
437 set_of_books_id,
438 sob_type,
439 se_gt_id,
440 --{Taxable Amount
441 tax_link_id,
442 tax_inc_flag,
443 --}
444 ref_line_id
445 )
446 SELECT
447 0 -- GT_ID
448 , NVL(ard.amount_cr,0)
449 - NVL(ard.amount_dr,0) -- AMT
450 , NVL(ard.acctd_amount_cr,0)
451 - NVL(ard.acctd_amount_dr,0) -- ACCTD_AMT
452 , DECODE(adj.type,
453 'LINE',DECODE(ard.source_type,'ADJ','REV',
454 'TAX','TAX',
455 'DEFERRED_TAX','TAX',
456 'ADJ_NON_REC_TAX','TAX','REV'),
457 'TAX' ,DECODE(ard.source_type,'TAX','TAX',
458 'ADJ','TAX',
459 'DEFERRED_TAX','TAX',
460 'ADJ_NON_REC_TAX','TAX','TAX'),
461 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
462 'FREIGHT','FREIGHT'),
463 'CHARGES',DECODE(ard.source_type,'FINCHRG',
464 'CHARGES','CHARGES'),
465 'REV') -- ACCOUNT_CLASS
466 , ard.code_combination_id -- CCID_SECONDARY
467 , DECODE(adj.type,
468 'LINE',DECODE(ard.source_type,'ADJ',-6,
469 'TAX',-8,
470 'DEFERRED_TAX',-8,
471 'ADJ_NON_REC_TAX',-8,-6),
472 'TAX' ,DECODE(ard.source_type,'TAX',-8,
473 'ADJ',-8,
474 'DEFERRED_TAX',-8,
475 'ADJ_NON_REC_TAX',-8,-8),
476 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
477 -9,-9),
478 'CHARGES',DECODE(ard.source_type,'FINCHRG',
479 -7,-7),
480 -6) --REF_CUST_TRX_LINE_GL_DIST_ID
481 , DECODE(adj.type,
482 'LINE',DECODE(ard.source_type,'ADJ',-6,
483 'TAX',-8,
484 'DEFERRED_TAX',-8,
485 'ADJ_NON_REC_TAX',-8,-6),
486 'TAX' ,DECODE(ard.source_type,'TAX',-8,
487 'ADJ',-8,
488 'DEFERRED_TAX',-8,
489 'ADJ_NON_REC_TAX',-8,-8),
490 'FREIGHT' ,DECODE(ard.source_type,'ADJ',
491 -9,-9),
492 'CHARGES',DECODE(ard.source_type,'FINCHRG',
493 -7,-7),
494 -6) --REF_CUSTOMER_TRX_LINE_ID
495 , adj.customer_trx_id --REF_CUSTOMER_TRX_ID
496 , trx.invoice_currency_code --TO_CURRENCY
497 , NULL -- BASE_CURRENCY
498 -- ADJ and APP Elmt
499 , DECODE(adj.type,'LINE', DECODE(ard.source_type,
500 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
501 0) --DIST_AMT
502 ,DECODE(adj.type,'LINE', DECODE(ard.source_type,
503 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
504 0) --DIST_ACCTD_AMT
505 --
506 ,DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
507 'FINCHRG',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
508 'ADJ' , (NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
509 0) --DIST_CHRG_AMT
510 ,DECODE(adj.type,'CHARGES',DECODE(ard.source_type,
511 'FINCHRG',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
512 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
513 0) --DIST_CHRG_ACCTD_AMT
514 , DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
515 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
516 0) --DIST_FRT_AMT
517 , DECODE(adj.type,'FREIGHT',DECODE(ard.source_type,
518 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
519 0) --DIST_FRT_ACCTD_AMT
520 , DECODE(adj.type,'TAX', DECODE(ard.source_type,
521 'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
522 'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
523 'ADJ',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
524 'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
525 'LINE', DECODE(ard.source_type,
526 'TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
527 'ADJ_NON_REC_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),
528 'DEFERRED_TAX',(NVL(ard.amount_cr,0)-NVL(ard.amount_dr,0)),0),
529 0) --DIST_TAX_AMT
530 , DECODE(adj.type,'TAX', DECODE(ard.source_type,
531 'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
532 'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
533 'ADJ',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
534 'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
535 'LINE', DECODE(ard.source_type,
536 'TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
537 'ADJ_NON_REC_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),
538 'DEFERRED_TAX',(NVL(ard.acctd_amount_cr,0)-NVL(ard.acctd_amount_dr,0)),0),
539 0) -- DIST_TAX_ACCTD_AMT
540 -- Buc
541 , 0 -- tl_alloc_amt
542 , 0 -- tl_alloc_acctd_amt
543 , 0 -- tl_chrg_alloc_amt
544 , 0 -- tl_chrg_alloc_acctd_amt
545 , 0 -- tl_frt_alloc_amt
546 , 0 -- tl_frt_alloc_acctd_amt
547 , 0 -- tl_tax_alloc_amt
548 , 0 -- tl_tax_alloc_acctd_amt
549 -- ED Elmt
550 , 0 -- DIST_ed_AMT
551 , 0 -- DIST_ed_ACCTD_AMT
552 , 0 -- DIST_ed_chrg_AMT
553 , 0 -- DIST_ed_chrg_ACCTD_AMT
554 , 0 -- DIST_ed_frt_AMT
555 , 0 -- DIST_ed_frt_ACCTD_AMT
556 , 0 -- DIST_ed_tax_AMT
557 , 0 -- DIST_ed_tax_ACCTD_AMT
558 --
559 , 0 -- tl_ed_alloc_amt
560 , 0 -- tl_ed_alloc_acctd_amt
561 , 0 -- tl_ed_chrg_alloc_amt
562 , 0 -- tl_ed_chrg_alloc_acctd_amt
563 , 0 -- tl_ed_frt_alloc_amt
564 , 0 -- tl_ed_frt_alloc_acctd_amt
565 , 0 -- tl_ed_tax_alloc_amt
566 , 0 -- tl_ed_tax_alloc_acctd_amt
567 -- UNED
568 , 0 -- DIST_uned_AMT
569 , 0 -- DIST_uned_ACCTD_AMT
570 , 0 -- DIST_uned_chrg_AMT
571 , 0 -- DIST_uned_chrg_ACCTD_AMT
572 , 0 -- DIST_uned_frt_AMT
573 , 0 -- DIST_uned_frt_ACCTD_AMT
574 , 0 -- DIST_uned_tax_AMT
575 , 0 -- DIST_uned_tax_ACCTD_AMT
576 --
577 , 0 -- tl_uned_alloc_amt
578 , 0 -- tl_uned_alloc_acctd_amt
579 , 0 -- tl_uned_chrg_alloc_amt
580 , 0 -- tl_uned_chrg_alloc_acctd_amt
581 , 0 -- tl_uned_frt_alloc_amt
582 , 0 -- tl_uned_frt_alloc_acctd_amt
583 , 0 -- tl_uned_tax_alloc_amt
584 , 0 -- tl_uned_tax_alloc_acctd_amt
585 --
586 , ard.source_type -- source_type
587 , ard.source_table -- source_table
588 , ard.source_id -- source_id
589 , DECODE(adj.type,
590 'LINE',DECODE(ard.source_type,'ADJ','LINE',
591 'TAX','TAX',
592 'DEFERRED_TAX','TAX','LINE'),
593 'TAX','TAX',
594 'CHARGES','CHARGES',
595 'FREIGHT','FREIGHT', 'LINE') -- line_type
596 --
597 , NULL -- group_id
598 , '00' -- source_data_key1
599 , '00' -- source_data_key2
600 , '00' -- source_data_key3
601 , '00' -- source_data_key4
602 , '00' -- source_data_key5
603 , 'D' -- gp_level
604 --
605 , adj.set_of_books_id -- set_of_books_id
606 , 'P' -- sob_type
607 , USERENV('SESSIONID') -- se_gt_id
608 , NULL -- tax_link_id
609 , NULL -- tax_inc_flag
610 , ard.line_id -- ref_line_id
611 FROM ar_adjustments_all adj,
612 ar_distributions_all ard,
613 ar_system_parameters_all ars,
614 (SELECT MAX(ref_customer_trx_id) ref_customer_trx_id,
615 MAX(to_currency) invoice_currency_code
616 FROM ra_ar_gt
617 GROUP BY ref_customer_trx_id, to_currency) trx
618 WHERE adj.customer_trx_id = trx.ref_customer_trx_id
619 AND adj.status = 'A'
620 AND adj.postable = 'Y'
621 AND adj.upgrade_method = '11I'
622 AND adj.adjustment_id = ard.source_id
623 AND ard.source_table = 'ADJ'
624 AND adj.type IN ('LINE','CHARGES','TAX','FREIGHT')
625 AND adj.org_id = ars.org_id
626 AND ars.accounting_method = 'CASH'
627 AND DECODE(adj.type, 'LINE',DECODE(ard.source_type,
628 'ADJ','Y',
629 'TAX','Y',
630 'DEFERRED_TAX','Y',
631 'ADJ_NON_REC_TAX','Y','N'),
632 'CHARGES',DECODE(ard.source_type,
633 'FINCHRG','Y',
634 'ADJ','Y','N'),
635 'TAX',DECODE(ard.source_type,
636 'TAX','Y',
637 'DEFERRED_TAX','Y',
638 'ADJ','Y',
639 'ADJ_NON_REC_TAX','Y','N'),
640 'FREIGHT',DECODE(ard.source_type,
641 'ADJ','Y','N'),
642 'N') = 'Y';
643
644 --populate the base amounts
645 INSERT INTO ar_base_dist_amts_gt
646 ( gt_id,
647 gp_level,
648 ref_customer_trx_id ,
649 ref_customer_trx_line_id,
650 base_dist_amt ,
651 base_dist_acctd_amt ,
652 base_dist_chrg_amt ,
653 base_dist_chrg_acctd_amt ,
654 base_dist_frt_amt ,
655 base_dist_frt_acctd_amt ,
656 base_dist_tax_amt ,
657 base_dist_tax_acctd_amt ,
658
659 base_ed_dist_amt ,
660 base_ed_dist_acctd_amt ,
661 base_ed_dist_chrg_amt ,
662 base_ed_dist_chrg_acctd_amt,
663 base_ed_dist_frt_amt ,
664 base_ed_dist_frt_acctd_amt ,
665 base_ed_dist_tax_amt ,
666 base_ed_dist_tax_acctd_amt ,
667
668 base_uned_dist_amt,
669 base_uned_dist_acctd_amt,
670 base_uned_dist_chrg_amt,
671 base_uned_dist_chrg_acctd_amt,
672 base_uned_dist_frt_amt,
673 base_uned_dist_frt_acctd_amt,
674 base_uned_dist_tax_amt,
675 base_uned_dist_tax_acctd_amt,
676 set_of_books_id,
677 sob_type,
678 source_table,
679 source_type
680 )
681 SELECT DISTINCT
682 a.gt_id,
683 a.gp_level,
684 a.ref_customer_trx_id ,
685 a.ref_customer_trx_line_id,
686 s.sum_dist_amt,
687 s.sum_dist_acctd_amt,
688 s.sum_dist_chrg_amt,
689 s.sum_dist_chrg_acctd_amt,
690 s.sum_dist_frt_amt,
691 s.sum_dist_frt_acctd_amt,
692 s.sum_dist_tax_amt,
693 s.sum_dist_tax_acctd_amt,
694 --
695 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_amt,0),
696 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_acctd_amt,0),
697 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_amt,0),
698 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_acctd_amt,0),
699 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_amt,0),
700 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_acctd_amt,0),
701 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_amt,0),
702 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_acctd_amt,0),
703 --
704 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_amt,0),
705 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_acctd_amt,0),
706 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_amt,0),
707 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_acctd_amt,0),
708 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_amt,0),
709 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_acctd_amt,0),
710 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_amt,0),
711 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_acctd_amt,0),
712 a.set_of_books_id,
713 a.sob_type,
714 a.source_table,
715 a.source_type
716 FROM (SELECT
717 SUM(NVL(b.DIST_AMT,0)) sum_dist_amt ,
718 SUM(NVL(b.DIST_ACCTD_AMT,0)) sum_dist_acctd_amt,
719 SUM(NVL(b.DIST_CHRG_AMT,0)) sum_dist_chrg_amt,
720 SUM(NVL(b.DIST_CHRG_ACCTD_AMT,0)) sum_dist_chrg_acctd_amt,
721 SUM(NVL(b.DIST_FRT_AMT,0)) sum_dist_frt_amt,
722 SUM(NVL(b.DIST_FRT_ACCTD_AMT,0)) sum_dist_frt_acctd_amt,
723 SUM(NVL(b.DIST_TAX_AMT,0)) sum_dist_tax_amt,
724 SUM(NVL(b.DIST_TAX_ACCTD_AMT,0)) sum_dist_tax_acctd_amt,
725 --
726 SUM(NVL(b.DIST_ed_AMT,0)) sum_dist_ed_amt,
727 SUM(NVL(b.DIST_ed_ACCTD_AMT,0)) sum_dist_ed_acctd_amt,
728 SUM(NVL(b.DIST_ed_chrg_AMT,0)) sum_dist_ed_chrg_amt,
729 SUM(NVL(b.DIST_ed_chrg_ACCTD_AMT,0)) sum_dist_ed_chrg_acctd_amt,
730 SUM(NVL(b.DIST_ed_frt_AMT,0)) sum_dist_ed_frt_amt,
731 SUM(NVL(b.DIST_ed_frt_ACCTD_AMT,0)) sum_dist_ed_frt_acctd_amt,
732 SUM(NVL(b.DIST_ed_tax_AMT,0)) sum_dist_ed_tax_amt,
733 SUM(NVL(b.DIST_ed_tax_ACCTD_AMT,0)) sum_dist_ed_tax_acctd_amt,
734 --
735 SUM(NVL(b.DIST_uned_AMT,0)) sum_dist_uned_amt,
736 SUM(NVL(b.DIST_uned_ACCTD_AMT,0)) sum_dist_uned_acctd_amt,
737 SUM(NVL(b.DIST_uned_chrg_AMT,0)) sum_dist_uned_chrg_amt,
738 SUM(NVL(b.DIST_uned_chrg_ACCTD_AMT,0)) sum_dist_uned_chrg_acctd_amt,
739 SUM(NVL(b.DIST_uned_frt_AMT,0)) sum_dist_uned_frt_amt,
740 SUM(NVL(b.DIST_uned_frt_ACCTD_AMT,0)) sum_dist_uned_frt_acctd_amt,
741 SUM(NVL(b.DIST_uned_tax_AMT,0)) sum_dist_uned_tax_amt,
742 SUM(NVL(b.DIST_uned_tax_ACCTD_AMT,0)) sum_dist_uned_tax_acctd_amt,
743 b.ref_customer_trx_id ref_customer_trx_id,
744 b.gt_id gt_id
745 FROM ra_ar_gt b
746 GROUP BY b.ref_customer_trx_id,
747 b.gt_id ) s,
748 ra_ar_gt a
749 WHERE a.ref_customer_trx_id = s.ref_customer_trx_id
750 AND a.gt_id = s.gt_id;
751
752 -- Cash Basis
753 OPEN c_app(l_start_rowid, l_end_rowid);
754 LOOP
755 FETCH c_app INTO l_app_rec;
756 EXIT WHEN c_app%NOTFOUND;
757 IF l_app_rec.org_id <> l_org_id THEN
758 -- fnd_client_info.set_currency_context(NULL);
759 l_org_id := l_app_rec.org_id;
760 Init_Curr_Details(p_sob_id => l_app_rec.set_of_books_id,
761 p_org_id => l_app_rec.org_id,
762 x_accounting_method => l_accounting_method);
763 -- fnd_client_info.set_currency_context(g_ae_sys_rec.set_of_books_id);
764 -- fnd_client_info.set_org_context(l_app_rec.org_id);
765
766 END IF;
767
768 g_ae_sys_rec.sob_type := 'P';
769
770 l_gt_id := l_gt_id + 1;
771
772 -- proration
773 arp_det_dist_pkg.prepare_for_ra
774 ( p_gt_id => l_gt_id,
775 p_app_rec => l_app_rec,
776 p_ae_sys_rec => g_ae_sys_rec,
777 p_inv_cm => 'I',
778 p_cash_mfar => 'CASH');
779
780
781 l_ra_list(l_gt_id) := l_app_rec.receivable_application_id;
782
783 -- fnd_client_info.set_currency_context(NULL);
784
785 END LOOP;
786 CLOSE c_app;
787
788
789 FORALL i IN l_ra_list.FIRST .. l_ra_list.LAST
790 UPDATE ar_receivable_applications_all
791 SET upgrade_method = 'R12_11ICASH_POST'
792 WHERE receivable_application_id = l_ra_list(i);
793
794
795 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
796
797 ad_parallel_updates_pkg.processed_rowid_range(
798 l_rows_processed,
799 l_end_rowid);
800
801 commit;
802
803 ad_parallel_updates_pkg.get_rowid_range(
804 l_start_rowid,
805 l_end_rowid,
806 l_any_rows_to_process,
807 l_batch_size,
808 FALSE);
809
810 l_rows_processed := 0 ;
811
812 END LOOP ; /* end of WHILE loop */
813
814 END;
815
816
817
818
819
820
821
822 PROCEDURE upgrade_11i_cm_cash_basis
823 (l_table_owner IN VARCHAR2, -- AR
824 l_table_name IN VARCHAR2, -- AR_RECEIVABLE_APPLICATIONS_ALL
825 l_script_name IN VARCHAR2, -- ar120cbupi.sql
826 l_worker_id IN VARCHAR2,
827 l_num_workers IN VARCHAR2,
828 l_batch_size IN VARCHAR2)
829 AS
830
831 l_start_rowid rowid;
832 l_end_rowid rowid;
833 l_any_rows_to_process boolean;
834 l_rows_processed number := 0;
835
836 l_status VARCHAR2(10);
837 l_industry VARCHAR2(10);
838 l_res BOOLEAN := FALSE;
839 no_global EXCEPTION;
840
841
842 CURSOR c_app(p_start_rowid IN ROWID,
843 p_end_rowid IN ROWID)
844 IS
845 SELECT app.*
846 FROM ar_receivable_applications_all app,
847 ar_system_parameters_all ars
848 WHERE app.status = 'APP'
849 AND app.upgrade_method = 'R12_11ICASH_POST'
850 AND app.org_id = ars.org_id
851 AND app.posting_control_id = -3
852 AND app.rowid >= p_start_rowid
853 AND app.rowid <= p_end_rowid
854 AND ars.accounting_method = 'CASH'
855 AND app.customer_trx_id IS NOT NULL
856 AND app.cash_receipt_id IS NULL
857 AND NOT EXISTS (SELECT '1'
858 FROM psa_trx_types_all psa,
859 ra_customer_trx_all inv
860 WHERE inv.customer_trx_id = app.customer_trx_id
861 AND inv.cust_trx_type_id = psa.psa_trx_type_id)
862 ORDER BY app.org_id;
863
864
865
866 l_org_id NUMBER := -9999;
867 l_app_rec ar_receivable_applications%ROWTYPE;
868 l_line_acctd_amt NUMBER;
869 l_tax_acctd_amt NUMBER;
870 l_frt_acctd_amt NUMBER;
871 l_chrg_acctd_amt NUMBER;
872 l_ed_line_acctd_amt NUMBER;
873 l_ed_tax_acctd_amt NUMBER;
874 l_ed_frt_acctd_amt NUMBER;
875 l_ed_chrg_acctd_amt NUMBER;
876 l_ued_line_acctd_amt NUMBER;
877 l_ued_tax_acctd_amt NUMBER;
878 l_ued_frt_acctd_amt NUMBER;
879 l_ued_chrg_acctd_amt NUMBER;
880 dummy VARCHAR2(1);
881 l_ra_list DBMS_SQL.NUMBER_TABLE;
882 erase_ra_list DBMS_SQL.NUMBER_TABLE;
883 i NUMBER := 0;
884
885 g_ind_current NUMBER := -9;
886 g_run_tot NUMBER := 0;
887 g_run_acctd_tot NUMBER := 0;
888 l_gt_id NUMBER := 0;
889 l_accounting_method VARCHAR2(30);
890 end_process_stop EXCEPTION;
891
892 BEGIN
893
894 /* ------ Initialize the rowid ranges ------ */
895 ad_parallel_updates_pkg.initialize_rowid_range(
896 ad_parallel_updates_pkg.ROWID_RANGE,
897 l_table_owner,
898 l_table_name,
899 l_script_name,
900 l_worker_id,
901 l_num_workers,
902 l_batch_size, 0);
903
904 /* ------ Get rowid ranges ------ */
905 ad_parallel_updates_pkg.get_rowid_range(
906 l_start_rowid,
907 l_end_rowid,
908 l_any_rows_to_process,
909 l_batch_size,
910 TRUE);
911
912
913 WHILE ( l_any_rows_to_process = TRUE )
914 LOOP
915
916 l_rows_processed := 0;
917
918
919
920 -------------------------------------------
921 -- Get all invoices for the applications
922 -------------------------------------------
923 INSERT INTO RA_AR_GT
924 ( GT_ID ,
925 AMT ,
926 ACCTD_AMT ,
927 ACCOUNT_CLASS ,
928 CCID_SECONDARY ,
929 REF_CUST_TRX_LINE_GL_DIST_ID,
930 REF_CUSTOMER_TRX_LINE_ID ,
931 REF_CUSTOMER_TRX_ID ,
932 TO_CURRENCY ,
933 BASE_CURRENCY ,
934 -- ADJ and APP Elmt
935 DIST_AMT ,
936 DIST_ACCTD_AMT ,
937 DIST_CHRG_AMT ,
938 DIST_CHRG_ACCTD_AMT ,
939 DIST_FRT_AMT ,
940 DIST_FRT_ACCTD_AMT ,
941 DIST_TAX_AMT ,
942 DIST_TAX_ACCTD_AMT ,
943 -- Buc
944 tl_alloc_amt ,
945 tl_alloc_acctd_amt ,
946 tl_chrg_alloc_amt ,
947 tl_chrg_alloc_acctd_amt,
948 tl_frt_alloc_amt ,
949 tl_frt_alloc_acctd_amt,
950 tl_tax_alloc_amt ,
951 tl_tax_alloc_acctd_amt,
952 -- ED Elmt
953 DIST_ed_AMT,
954 DIST_ed_ACCTD_AMT,
955 DIST_ed_chrg_AMT,
956 DIST_ed_chrg_ACCTD_AMT,
957 DIST_ed_frt_AMT ,
958 DIST_ed_frt_ACCTD_AMT,
959 DIST_ed_tax_AMT ,
960 DIST_ed_tax_ACCTD_AMT,
961 --
962 tl_ed_alloc_amt ,
963 tl_ed_alloc_acctd_amt ,
964 tl_ed_chrg_alloc_amt ,
965 tl_ed_chrg_alloc_acctd_amt,
966 tl_ed_frt_alloc_amt ,
967 tl_ed_frt_alloc_acctd_amt,
968 tl_ed_tax_alloc_amt ,
969 tl_ed_tax_alloc_acctd_amt,
970 -- UNED
971 DIST_uned_AMT ,
972 DIST_uned_ACCTD_AMT ,
973 DIST_uned_chrg_AMT ,
974 DIST_uned_chrg_ACCTD_AMT ,
975 DIST_uned_frt_AMT ,
976 DIST_uned_frt_ACCTD_AMT ,
977 DIST_uned_tax_AMT ,
978 DIST_uned_tax_ACCTD_AMT ,
979 --
980 tl_uned_alloc_amt ,
981 tl_uned_alloc_acctd_amt ,
982 tl_uned_chrg_alloc_amt ,
983 tl_uned_chrg_alloc_acctd_amt,
984 tl_uned_frt_alloc_amt ,
985 tl_uned_frt_alloc_acctd_amt,
986 tl_uned_tax_alloc_amt ,
987 tl_uned_tax_alloc_acctd_amt,
988 --
989 source_type ,
990 source_table ,
991 source_id ,
992 line_type,
993 --
994 group_id,
995 source_data_key1 ,
996 source_data_key2 ,
997 source_data_key3 ,
998 source_data_key4 ,
999 source_data_key5 ,
1000 gp_level,
1001 --
1002 set_of_books_id,
1003 sob_type,
1004 se_gt_id,
1005 tax_link_id,
1006 tax_inc_flag
1007 )
1008 SELECT
1009 0 -- GT_ID
1010 , ctlgd.amount -- AMT
1011 , ctlgd.acctd_amount -- ACCTD_AMT
1012 , DECODE(ctl.line_type,'LINE','REV',
1013 'TAX','TAX',
1014 'FREIGHT','FREIGHT',
1015 'CHARGES','CHARGES',
1016 'CB','REV') -- ACCOUNT_CLASS
1017 , DECODE(ctlgd.collected_tax_ccid,
1018 NULL, ctlgd.code_combination_id,
1019 0 , ctlgd.code_combination_id,
1020 ctlgd.collected_tax_ccid) -- CCID_SECONDARY
1021 , ctlgd.cust_trx_line_gl_dist_id --REF_CUST_TRX_LINE_GL_DIST_ID
1022 , DECODE(ctl.line_type,'LINE' ,-6,
1023 'TAX' ,-8,
1024 'FREIGHT',-9,
1025 'CHARGES',-7,
1026 'CB' ,-6)
1027 --, ctlgd.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
1028 , trx.customer_trx_id -- REF_CUSTOMER_TRX_ID
1029 , trx.invoice_currency_code -- TO_CURRENCY
1030 , NULL -- BASE_CURRENCY
1031 -- ADJ and APP Elmt
1032 , DECODE(ctl.line_type,'LINE',ctlgd.amount,0) -- DIST_AMT
1033 , DECODE(ctl.line_type,'LINE',ctlgd.acctd_amount,0) -- DIST_ACCTD_AMT
1034 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) -- DIST_CHRG_AMT
1035 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) -- DIST_CHRG_ACCTD_AMT
1036 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) -- DIST_FRT_AMT
1037 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) -- DIST_FRT_ACCTD_AMT
1038 , DECODE(ctl.line_type,'TAX',ctlgd.amount,0) -- DIST_TAX_AMT
1039 , DECODE(ctl.line_type,'TAX',ctlgd.acctd_amount,0) -- DIST_TAX_ACCTD_AMT
1040 -- Buc
1041 , 0 -- tl_alloc_amt
1042 , 0 -- tl_alloc_acctd_amt
1043 , 0 -- tl_chrg_alloc_amt
1044 , 0 -- tl_chrg_alloc_acctd_amt
1045 , 0 -- tl_frt_alloc_amt
1046 , 0 -- tl_frt_alloc_acctd_amt
1047 , 0 -- tl_tax_alloc_amt
1048 , 0 -- tl_tax_alloc_acctd_amt
1049 -- ED Elmt
1050 , DECODE(ctl.line_type,'LINE' ,ctlgd.amount,0) --DIST_ed_AMT
1051 , DECODE(ctl.line_type,'LINE' ,ctlgd.acctd_amount,0) --DIST_ed_ACCTD_AMT
1052 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) --DIST_ed_chrg_AMT
1053 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) --DIST_ed_chrg_ACCTD_AMT
1054 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) --DIST_ed_frt_AMT
1055 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) --DIST_ed_frt_ACCTD_AMT
1056 , DECODE(ctl.line_type,'TAX' ,ctlgd.amount,0) --DIST_ed_tax_AMT
1057 , DECODE(ctl.line_type,'TAX' ,ctlgd.acctd_amount,0) --DIST_ed_tax_ACCTD_AMT
1058 --
1059 , 0 -- tl_ed_alloc_amt
1060 , 0 -- tl_ed_alloc_acctd_amt
1061 , 0 -- tl_ed_chrg_alloc_amt
1062 , 0 -- tl_ed_chrg_alloc_acctd_amt
1063 , 0 -- tl_ed_frt_alloc_amt
1064 , 0 -- tl_ed_frt_alloc_acctd_amt
1065 , 0 -- tl_ed_tax_alloc_amt
1066 , 0 -- tl_ed_tax_alloc_acctd_amt
1067 -- UNED
1068 , DECODE(ctl.line_type,'LINE' ,ctlgd.amount,0) --DIST_uned_AMT
1069 , DECODE(ctl.line_type,'LINE' ,ctlgd.acctd_amount,0) --DIST_uned_ACCTD_AMT
1070 , DECODE(ctl.line_type,'CHARGES',ctlgd.amount,0) --DIST_uned_chrg_AMT
1071 , DECODE(ctl.line_type,'CHARGES',ctlgd.acctd_amount,0) --DIST_uned_chrg_ACCTD_AMT
1072 , DECODE(ctl.line_type,'FREIGHT',ctlgd.amount,0) --DIST_uned_frt_AMT
1073 , DECODE(ctl.line_type,'FREIGHT',ctlgd.acctd_amount,0) --DIST_uned_frt_ACCTD_AMT
1074 , DECODE(ctl.line_type,'TAX' ,ctlgd.amount,0) --DIST_uned_tax_AMT
1075 , DECODE(ctl.line_type,'TAX' ,ctlgd.acctd_amount,0) --DIST_uned_tax_ACCTD_AMT
1076 --
1077 , 0 -- tl_uned_alloc_amt
1078 , 0 -- tl_uned_alloc_acctd_amt
1079 , 0 -- tl_uned_chrg_alloc_amt
1080 , 0 -- tl_uned_chrg_alloc_acctd_amt
1081 , 0 -- tl_uned_frt_alloc_amt
1082 , 0 -- tl_uned_frt_alloc_acctd_amt
1083 , 0 -- tl_uned_tax_alloc_amt
1084 , 0 -- tl_uned_tax_alloc_acctd_amt
1085 --
1086 , NULL -- source_type
1087 , 'CTLGD' -- source_table
1088 , NULL -- source_id
1089 , ctl.line_type -- line_type
1090 --
1091 , NULL -- group_id
1092 , '00' -- source_data_key1
1093 , '00' -- source_data_key2
1094 , '00' -- source_data_key3
1095 , '00' -- source_data_key4
1096 , '00' -- source_data_key5
1097 , 'D' -- gp_level
1098 --
1099 , trx.set_of_books_id -- set_of_books_id
1100 , 'P' -- sob_type
1101 , USERENV('SESSIONID') -- se_gt_id
1102 , NULL -- tax_link_id
1103 , NULL -- tax_inc_flag
1104 FROM (SELECT customer_trx_id,
1105 org_id
1106 FROM ar_receivable_applications_all
1107 WHERE status = 'APP'
1108 AND upgrade_method = 'R12_11ICASH_POST'
1109 AND posting_control_id = -3
1110 AND customer_trx_id IS NOT NULL
1111 AND cash_receipt_id IS NULL
1112 AND rowid >= l_start_rowid
1113 AND rowid <= l_end_rowid
1114 GROUP BY customer_trx_id,
1115 org_id ) app,
1116 ar_system_parameters_all ars,
1117 ra_customer_trx_all trx,
1118 ra_customer_trx_lines_all ctl,
1119 ra_cust_trx_line_gl_dist_all ctlgd
1120 WHERE ars.accounting_method = 'CASH'
1121 AND app.org_id = ars.org_id
1122 AND app.customer_trx_id = trx.customer_trx_id
1123 AND trx.customer_trx_id = ctl.customer_trx_id
1124 AND ctl.customer_trx_line_id = ctlgd.customer_trx_line_id
1125 AND ctl.line_type IN ('LINE','TAX','FREIGHT','CHARGES','CB')
1126 AND ctlgd.account_class IN ('REV','SUSPENSE','UNBILL','UNEARN','FREIGHT','TAX')
1127 AND ctlgd.account_set_flag = 'N'
1128 AND NOT EXISTS (SELECT '1'
1129 FROM psa_trx_types_all psa,
1130 ra_customer_trx_all inv
1131 WHERE inv.customer_trx_id = app.customer_trx_id
1132 AND inv.cust_trx_type_id = psa.psa_trx_type_id);
1133
1134
1135 --populate the base amounts
1136 INSERT INTO ar_base_dist_amts_gt
1137 ( gt_id,
1138 gp_level,
1139 ref_customer_trx_id ,
1140 ref_customer_trx_line_id,
1141 base_dist_amt ,
1142 base_dist_acctd_amt ,
1143 base_dist_chrg_amt ,
1144 base_dist_chrg_acctd_amt ,
1145 base_dist_frt_amt ,
1146 base_dist_frt_acctd_amt ,
1147 base_dist_tax_amt ,
1148 base_dist_tax_acctd_amt ,
1149
1150 base_ed_dist_amt ,
1151 base_ed_dist_acctd_amt ,
1152 base_ed_dist_chrg_amt ,
1153 base_ed_dist_chrg_acctd_amt,
1154 base_ed_dist_frt_amt ,
1155 base_ed_dist_frt_acctd_amt ,
1156 base_ed_dist_tax_amt ,
1157 base_ed_dist_tax_acctd_amt ,
1158
1159 base_uned_dist_amt,
1160 base_uned_dist_acctd_amt,
1161 base_uned_dist_chrg_amt,
1162 base_uned_dist_chrg_acctd_amt,
1163 base_uned_dist_frt_amt,
1164 base_uned_dist_frt_acctd_amt,
1165 base_uned_dist_tax_amt,
1166 base_uned_dist_tax_acctd_amt,
1167 set_of_books_id,
1168 sob_type,
1169 source_table,
1170 source_type
1171 )
1172 SELECT DISTINCT
1173 a.gt_id,
1174 a.gp_level,
1175 a.ref_customer_trx_id ,
1176 a.ref_customer_trx_line_id,
1177
1178 s.sum_dist_amt,
1179 s.sum_dist_acctd_amt,
1180 s.sum_dist_chrg_amt,
1181 s.sum_dist_chrg_acctd_amt,
1182 s.sum_dist_frt_amt,
1183 s.sum_dist_frt_acctd_amt,
1184 s.sum_dist_tax_amt,
1185 s.sum_dist_tax_acctd_amt,
1186 --
1187 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_amt,0),
1188 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_acctd_amt,0),
1189 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_amt,0),
1190 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_chrg_acctd_amt,0),
1191 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_amt,0),
1192 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_frt_acctd_amt,0),
1193 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_amt,0),
1194 DECODE(a.source_table,'CTLGD',s.sum_dist_ed_tax_acctd_amt,0),
1195 --
1196 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_amt,0),
1197 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_acctd_amt,0),
1198 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_amt,0),
1199 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_chrg_acctd_amt,0),
1200 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_amt,0),
1201 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_frt_acctd_amt,0),
1202 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_amt,0),
1203 DECODE(a.source_table,'CTLGD',s.sum_dist_uned_tax_acctd_amt,0),
1204
1205 a.set_of_books_id,
1206 a.sob_type,
1207 a.source_table,
1208 a.source_type
1209 FROM (SELECT
1210 SUM(NVL(b.DIST_AMT,0)) sum_dist_amt ,
1211 SUM(NVL(b.DIST_ACCTD_AMT,0)) sum_dist_acctd_amt,
1212 SUM(NVL(b.DIST_CHRG_AMT,0)) sum_dist_chrg_amt,
1213 SUM(NVL(b.DIST_CHRG_ACCTD_AMT,0)) sum_dist_chrg_acctd_amt,
1214 SUM(NVL(b.DIST_FRT_AMT,0)) sum_dist_frt_amt,
1215 SUM(NVL(b.DIST_FRT_ACCTD_AMT,0)) sum_dist_frt_acctd_amt,
1216 SUM(NVL(b.DIST_TAX_AMT,0)) sum_dist_tax_amt,
1217 SUM(NVL(b.DIST_TAX_ACCTD_AMT,0)) sum_dist_tax_acctd_amt,
1218 --
1219 SUM(NVL(b.DIST_ed_AMT,0)) sum_dist_ed_amt,
1220 SUM(NVL(b.DIST_ed_ACCTD_AMT,0)) sum_dist_ed_acctd_amt,
1221 SUM(NVL(b.DIST_ed_chrg_AMT,0)) sum_dist_ed_chrg_amt,
1222 SUM(NVL(b.DIST_ed_chrg_ACCTD_AMT,0)) sum_dist_ed_chrg_acctd_amt,
1223 SUM(NVL(b.DIST_ed_frt_AMT,0)) sum_dist_ed_frt_amt,
1224 SUM(NVL(b.DIST_ed_frt_ACCTD_AMT,0)) sum_dist_ed_frt_acctd_amt,
1225 SUM(NVL(b.DIST_ed_tax_AMT,0)) sum_dist_ed_tax_amt,
1226 SUM(NVL(b.DIST_ed_tax_ACCTD_AMT,0)) sum_dist_ed_tax_acctd_amt,
1227 --
1228 SUM(NVL(b.DIST_uned_AMT,0)) sum_dist_uned_amt,
1229 SUM(NVL(b.DIST_uned_ACCTD_AMT,0)) sum_dist_uned_acctd_amt,
1230 SUM(NVL(b.DIST_uned_chrg_AMT,0)) sum_dist_uned_chrg_amt,
1231 SUM(NVL(b.DIST_uned_chrg_ACCTD_AMT,0)) sum_dist_uned_chrg_acctd_amt,
1232 SUM(NVL(b.DIST_uned_frt_AMT,0)) sum_dist_uned_frt_amt,
1233 SUM(NVL(b.DIST_uned_frt_ACCTD_AMT,0)) sum_dist_uned_frt_acctd_amt,
1234 SUM(NVL(b.DIST_uned_tax_AMT,0)) sum_dist_uned_tax_amt,
1235 SUM(NVL(b.DIST_uned_tax_ACCTD_AMT,0)) sum_dist_uned_tax_acctd_amt,
1236 b.ref_customer_trx_id ref_customer_trx_id,
1237 b.gt_id gt_id
1238 FROM ra_ar_gt b
1239 GROUP BY b.ref_customer_trx_id,
1240 b.gt_id ) s,
1241 ra_ar_gt a
1242 WHERE a.ref_customer_trx_id = s.ref_customer_trx_id
1243 AND a.gt_id = s.gt_id;
1244
1245 -- Cash Basis
1246 OPEN c_app(l_start_rowid, l_end_rowid);
1247 LOOP
1248 FETCH c_app INTO l_app_rec;
1249 EXIT WHEN c_app%NOTFOUND;
1250 IF l_app_rec.org_id <> l_org_id THEN
1251 -- fnd_client_info.set_currency_context(NULL);
1252 l_org_id := l_app_rec.org_id;
1253 Init_Curr_Details(p_sob_id => l_app_rec.set_of_books_id,
1254 p_org_id => l_app_rec.org_id,
1255 x_accounting_method => l_accounting_method);
1256 -- fnd_client_info.set_currency_context(g_ae_sys_rec.set_of_books_id);
1257 -- fnd_client_info.set_org_context(l_app_rec.org_id);
1258
1259 END IF;
1260
1261 g_ae_sys_rec.sob_type := 'P';
1262
1263 l_gt_id := l_gt_id + 1;
1264
1265 -- proration
1266 arp_det_dist_pkg.prepare_for_ra
1267 ( p_gt_id => l_gt_id,
1268 p_app_rec => l_app_rec,
1269 p_ae_sys_rec => g_ae_sys_rec,
1270 p_inv_cm => 'C',
1271 p_cash_mfar => 'CASH');
1272
1273
1274
1275 -- fnd_client_info.set_currency_context(NULL);
1276
1277 END LOOP;
1278 CLOSE c_app;
1279
1280
1281
1282 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1283
1284 ad_parallel_updates_pkg.processed_rowid_range(
1285 l_rows_processed,
1286 l_end_rowid);
1287
1288 commit;
1289
1290 ad_parallel_updates_pkg.get_rowid_range(
1291 l_start_rowid,
1292 l_end_rowid,
1293 l_any_rows_to_process,
1294 l_batch_size,
1295 FALSE);
1296
1297 l_rows_processed := 0 ;
1298
1299 END LOOP ; /* end of WHILE loop */
1300
1301 END;
1302
1303
1304 END;