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