[Home] [Help]
PACKAGE BODY: APPS.AR_UPG_PSA_DIST_PKG
Source
1 PACKAGE BODY ar_upg_psa_dist_pkg AS
2 /* $Header: ARPSAUPB.pls 120.7.12000000.2 2007/03/07 14:02:04 salladi ship $ */
3
4
5
6 PROCEDURE upgrade_adjustments(
7 l_table_owner IN VARCHAR2,
8 l_table_name IN VARCHAR2,
9 l_script_name IN VARCHAR2,
10 l_worker_id IN VARCHAR2,
11 l_num_workers IN VARCHAR2,
12 l_batch_size IN VARCHAR2,
13 l_batch_id IN NUMBER,
14 l_action_flag IN VARCHAR2)
15 IS
16 l_start_rowid rowid;
17 l_end_rowid rowid;
18 l_any_rows_to_process boolean;
19 l_rows_processed number := 0;
20
21 CURSOR c(p_start_rowid IN ROWID,p_end_rowid IN ROWID) IS
22 SELECT count(*)
23 FROM psa_mf_adj_dist_all
24 WHERE rowid >= p_start_rowid
25 AND rowid <= p_end_rowid;
26
27 l_nb_row NUMBER;
28 BEGIN
29
30 /* ------ Initialize the rowid ranges ------ */
31 ad_parallel_updates_pkg.initialize_rowid_range(
32 ad_parallel_updates_pkg.ROWID_RANGE,
33 l_table_owner,
34 l_table_name,
35 l_script_name,
36 l_worker_id,
37 l_num_workers,
38 l_batch_size, 0);
39
40 /* ------ Get rowid ranges ------ */
41 ad_parallel_updates_pkg.get_rowid_range(
42 l_start_rowid,
43 l_end_rowid,
44 l_any_rows_to_process,
45 l_batch_size,
46 TRUE);
47
48
49 WHILE ( l_any_rows_to_process = TRUE )
50 LOOP
51
52 -- 1) UPgrade Multi Fund AdJustment REversal AR distributions
53 INSERT INTO ar_distributions_all (
54 LINE_ID
55 , SOURCE_ID
56 , SOURCE_TABLE
57 , SOURCE_TYPE
58 , CODE_COMBINATION_ID
59 , AMOUNT_DR
60 , AMOUNT_CR
61 , ACCTD_AMOUNT_DR
62 , ACCTD_AMOUNT_CR
63 , CREATION_DATE
64 , CREATED_BY
65 , LAST_UPDATED_BY
66 , LAST_UPDATE_DATE
67 , LAST_UPDATE_LOGIN
68 , ORG_ID
69 , SOURCE_TABLE_SECONDARY
70 , SOURCE_ID_SECONDARY
71 , CURRENCY_CODE
72 , CURRENCY_CONVERSION_RATE
73 , CURRENCY_CONVERSION_TYPE
74 , CURRENCY_CONVERSION_DATE
75 , TAXABLE_ENTERED_DR
76 , TAXABLE_ENTERED_CR
77 , TAXABLE_ACCOUNTED_DR
78 , TAXABLE_ACCOUNTED_CR
79 , TAX_LINK_ID
80 , THIRD_PARTY_ID
81 , THIRD_PARTY_SUB_ID
82 , REVERSED_SOURCE_ID
83 , TAX_CODE_ID
84 , LOCATION_SEGMENT_ID
85 , SOURCE_TYPE_SECONDARY
86 , TAX_GROUP_CODE_ID
87 , REF_CUSTOMER_TRX_LINE_ID
88 , REF_CUST_TRX_LINE_GL_DIST_ID
89 , REF_ACCOUNT_CLASS
90 , ACTIVITY_BUCKET
91 , REF_LINE_ID
92 , FROM_AMOUNT_DR
93 , FROM_AMOUNT_CR
94 , FROM_ACCTD_AMOUNT_DR
95 , FROM_ACCTD_AMOUNT_CR
96 , REF_MF_DIST_FLAG
97 , REF_DIST_CCID)
98 SELECT
99 ar_distributions_s.nextval -- LINE_ID
100 , ard.SOURCE_ID
101 , ard.SOURCE_TABLE
102 , ard.SOURCE_TYPE
103 , ard.CODE_COMBINATION_ID
104 , ard.AMOUNT_CR -- Switch DR to CR
105 , ard.AMOUNT_DR -- Switch CR to DR
106 , ard.ACCTD_AMOUNT_CR -- Switch DR to CR
107 , ard.ACCTD_AMOUNT_DR -- Switch CR to DR
108 , SYSDATE -- CREATION_DATE
109 , 0 -- CREATED_BY
110 , 0 -- LAST_UPDATED_BY
111 , SYSDATE -- LAST_UPDATE_DATE
112 , 0 -- LAST_UPDATE_LOGIN
113 , ard.ORG_ID
114 , 'UPMFAJREAR' -- SOURCE_TABLE_SECONDARY
115 , ard.SOURCE_ID_SECONDARY
116 , ard.CURRENCY_CODE
117 , ard.CURRENCY_CONVERSION_RATE
118 , ard.CURRENCY_CONVERSION_TYPE
119 , ard.CURRENCY_CONVERSION_DATE
120 , ard.TAXABLE_ENTERED_DR
121 , ard.TAXABLE_ENTERED_CR
122 , ard.TAXABLE_ACCOUNTED_DR
123 , ard.TAXABLE_ACCOUNTED_CR
124 , ard.TAX_LINK_ID
125 , ard.THIRD_PARTY_ID
126 , ard.THIRD_PARTY_SUB_ID
127 , ard.REVERSED_SOURCE_ID
128 , ard.TAX_CODE_ID
129 , ard.LOCATION_SEGMENT_ID
130 , 'PSA_MF_ADJ_DIST_ALL' -- SOURCE_TYPE_SECONDARY
131 , ard.TAX_GROUP_CODE_ID
132 , ard.REF_CUSTOMER_TRX_LINE_ID
133 , ard.REF_CUST_TRX_LINE_GL_DIST_ID
134 , ard.REF_ACCOUNT_CLASS
135 , ard.ACTIVITY_BUCKET
136 , ard.REF_LINE_ID
137 , ard.FROM_AMOUNT_DR
138 , ard.FROM_AMOUNT_CR
139 , ard.FROM_ACCTD_AMOUNT_DR
140 , ard.FROM_ACCTD_AMOUNT_CR
141 , ard.REF_MF_DIST_FLAG
142 , ard.REF_DIST_CCID
143 FROM ar_adjustments_all adj,
144 ar_distributions_all ard
145 WHERE adj.rowid >= l_start_rowid
146 AND adj.rowid <= l_end_rowid
147 AND ard.source_table = 'ADJ'
148 AND ard.source_id = adj.adjustment_id
149 AND EXISTS (SELECT NULL FROM psa_mf_adj_dist_all a
150 WHERE a.adjustment_id = adj.adjustment_id);
151
152 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
153
154 -- 2) UPgrade Multi Fund AdJustment MIgrated to AR
155
156 INSERT INTO ar_distributions_all (
157 LINE_ID
158 , SOURCE_ID
159 , SOURCE_TABLE
160 , SOURCE_TYPE
161 , CODE_COMBINATION_ID
162 , AMOUNT_DR
163 , AMOUNT_CR
164 , ACCTD_AMOUNT_DR
165 , ACCTD_AMOUNT_CR
166 , CREATION_DATE
167 , CREATED_BY
168 , LAST_UPDATED_BY
169 , LAST_UPDATE_DATE
170 , LAST_UPDATE_LOGIN
171 , ORG_ID
172 , SOURCE_TABLE_SECONDARY
173 , SOURCE_ID_SECONDARY
174 , CURRENCY_CODE
175 , CURRENCY_CONVERSION_RATE
176 , CURRENCY_CONVERSION_TYPE
177 , CURRENCY_CONVERSION_DATE
178 , TAXABLE_ENTERED_DR
179 , TAXABLE_ENTERED_CR
180 , TAXABLE_ACCOUNTED_DR
181 , TAXABLE_ACCOUNTED_CR
182 , TAX_LINK_ID
183 , THIRD_PARTY_ID
184 , THIRD_PARTY_SUB_ID
185 , REVERSED_SOURCE_ID
186 , TAX_CODE_ID
187 , LOCATION_SEGMENT_ID
188 , SOURCE_TYPE_SECONDARY
189 , TAX_GROUP_CODE_ID
190 , REF_CUSTOMER_TRX_LINE_ID
191 , REF_CUST_TRX_LINE_GL_DIST_ID
192 , REF_ACCOUNT_CLASS
193 , ACTIVITY_BUCKET
194 , REF_LINE_ID
195 , FROM_AMOUNT_DR
196 , FROM_AMOUNT_CR
197 , FROM_ACCTD_AMOUNT_DR
198 , FROM_ACCTD_AMOUNT_CR
199 , REF_MF_DIST_FLAG
200 , REF_DIST_CCID)
201 SELECT /*+ ordered rowid(adj) use_nl(psaadj,psatd,ctlgd,trx) INDEX(psaadj psa_mf_adj_dist_u1) INDEX(psatd psa_mf_trx_dist_u1)
202 INDEX(ctlgd ra_cust_trx_line_gl_dist_u1) */
203 ar_distributions_s.nextval -- LINE_ID
204 , psaadj.adjustment_id -- SOURCE_ID
205 , 'ADJ' -- SOURCE_TABLE
206 , CASE WHEN doub.side = 'D' THEN
207 DECODE(SIGN(psaadj.amount),
208 -1 , 'ADJ',
209 'REC' )
210 ELSE
211 DECODE(SIGN(psaadj.amount),
212 1, 'ADJ',
213 'REC' )
214 END -- SOURCE_TYPE
215 , CASE WHEN doub.side = 'D' THEN
216 DECODE(SIGN(psaadj.amount),
217 -1 , psaadj.mf_adjustment_ccid,
218 psatd.mf_receivables_ccid )
219 ELSE
220 DECODE(SIGN(psaadj.amount),
221 1, psaadj.mf_adjustment_ccid,
222 psatd.mf_receivables_ccid )
223 END -- CODE_COMBINATION_ID
224 , DECODE(doub.side,'D',ABS(psaadj.amount),NULL ) -- AMOUNT_DR
225 , DECODE(doub.side,'C',ABS(psaadj.amount),NULL ) -- AMOUNT_CR
226 , DECODE(doub.side,'D',ABS(psaadj.amount),NULL ) -- ACCTD_AMOUNT_DR
227 , DECODE(doub.side,'C',ABS(psaadj.amount),NULL ) -- ACCTD_AMOUNT_CR
228 , SYSDATE -- CREATION_DATE
229 , 0 -- CREATED_BY
230 , 0 -- LAST_UPDATED_BY
231 , SYSDATE -- LAST_UPDATE_DATE
232 , 0 -- LAST_UPDATE_LOGIN
233 , trx.org_id -- ORG_ID
234 , 'UPMFAJMIAR' -- SOURCE_TABLE_SECONDARY
235 , NULL -- SOURCE_ID_SECONDARY
236 , trx.invoice_currency_code -- CURRENCY_CODE
237 , NULL -- CURRENCY_CONVERSION_RATE
238 , NULL -- CURRENCY_CONVERSION_TYPE
239 , NULL -- CURRENCY_CONVERSION_DATE
240 , NULL -- TAXABLE_ENTERED_DR
241 , NULL -- TAXABLE_ENTERED_CR
242 , NULL -- TAXABLE_ACCOUNTED_DR
243 , NULL -- TAXABLE_ACCOUNTED_CR
244 , NULL -- TAX_LINK_ID
245 , NULL -- THIRD_PARTY_ID
246 , NULL -- THIRD_PARTY_SUB_ID
247 , NULL -- REVERSED_SOURCE_ID
248 , NULL -- TAX_CODE_ID
249 , NULL -- LOCATION_SEGMENT_ID
250 , 'PSA_MF_ADJ_DIST_ALL' -- SOURCE_TYPE_SECONDARY
251 , NULL -- TAX_GROUP_CODE_ID
252 , ctlgd.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
253 , psatd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
254 , ctlgd.account_class -- REF_ACCOUNT_CLASS
255 , 'ADJ_'||
256 -- DECODE(doub.type,'CHARGES','CHRG',
257 DECODE(ctlgd.account_class,
258 'REV','LINE',
259 'TAX','TAX',
260 'FREIGHT','FRT',
261 'LINE') -- ACTIVITY_BUCKET
262 --)
263 , NULL -- REF_LINE_ID
264 , NULL -- FROM_AMOUNT_DR
265 , NULL -- FROM_AMOUNT_CR
266 , NULL -- FROM_ACCTD_AMOUNT_DR
267 , NULL -- FROM_ACCTD_AMOUNT_CR
268 , NULL -- REF_MF_DIST_FLAG
269 , NULL -- REF_DIST_CCID
270 FROM
271 ar_adjustments_all adj,
272 psa_mf_adj_dist_all psaadj,
273 (SELECT a.flag side,
274 b.adjustment_id adj_id,
275 b.customer_trx_id customer_trx_id,
276 b.type type
277 FROM
278 (SELECT 'D' AS flag FROM DUAL
279 UNION ALL
280 SELECT 'C' AS flag FROM DUAL) a,
281 ar_adjustments_all b) doub,
282 psa_mf_trx_dist_all psatd,
283 ra_customer_trx_all trx,
284 ra_cust_trx_line_gl_dist_all ctlgd
285 WHERE adj.rowid >= l_start_rowid
286 AND adj.rowid <= l_end_rowid
287 AND adj.adjustment_id = psaadj.adjustment_id
288 AND doub.adj_id = psaadj.adjustment_id
289 AND psaadj.cust_trx_line_gl_dist_id = psatd.cust_trx_line_gl_dist_id
290 AND psatd.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
291 AND doub.customer_trx_id = trx.customer_trx_id;
292
293
294 ad_parallel_updates_pkg.processed_rowid_range(
295 l_rows_processed,
296 l_end_rowid);
297
298 commit;
299
300 ad_parallel_updates_pkg.get_rowid_range(
301 l_start_rowid,
302 l_end_rowid,
303 l_any_rows_to_process,
304 l_batch_size,
305 FALSE);
306
307 l_rows_processed := 0 ;
308
309 END LOOP ; /* end of WHILE loop */
310
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 NULL;
315 WHEN OTHERS THEN
316 RAISE;
317 END upgrade_adjustments;
318
319
320
321
322
323
324
325 PROCEDURE upgrade_applications(
326 l_table_owner IN VARCHAR2,
327 l_table_name IN VARCHAR2,
328 l_script_name IN VARCHAR2,
329 l_worker_id IN VARCHAR2,
330 l_num_workers IN VARCHAR2,
331 l_batch_size IN VARCHAR2,
332 l_batch_id IN NUMBER,
333 l_action_flag IN VARCHAR2)
334 IS
335 l_start_rowid rowid;
336 l_end_rowid rowid;
337 l_any_rows_to_process boolean;
338 l_rows_processed number := 0;
339 BEGIN
340
341 /* ------ Initialize the rowid ranges ------ */
342 ad_parallel_updates_pkg.initialize_rowid_range(
343 ad_parallel_updates_pkg.ROWID_RANGE,
344 l_table_owner,
345 l_table_name,
346 l_script_name,
347 l_worker_id,
348 l_num_workers,
349 l_batch_size, 0);
350
351 /* ------ Get rowid ranges ------ */
352 ad_parallel_updates_pkg.get_rowid_range(
353 l_start_rowid,
354 l_end_rowid,
355 l_any_rows_to_process,
356 l_batch_size,
357 TRUE);
358
359
360 WHILE ( l_any_rows_to_process = TRUE )
361 LOOP
362
363 -- UPgrade Multi Fund Receivable Application REverse AR distributions
364
365 INSERT INTO ar_distributions_all
366 ( LINE_ID
367 , SOURCE_ID
368 , SOURCE_TABLE
369 , SOURCE_TYPE
370 , CODE_COMBINATION_ID
371 , AMOUNT_DR
372 , AMOUNT_CR
373 , ACCTD_AMOUNT_DR
374 , ACCTD_AMOUNT_CR
375 , CREATION_DATE
376 , CREATED_BY
377 , LAST_UPDATED_BY
378 , LAST_UPDATE_DATE
379 , LAST_UPDATE_LOGIN
380 , ORG_ID
381 , SOURCE_TABLE_SECONDARY
382 , SOURCE_ID_SECONDARY
383 , CURRENCY_CODE
384 , CURRENCY_CONVERSION_RATE
385 , CURRENCY_CONVERSION_TYPE
386 , CURRENCY_CONVERSION_DATE
387 , TAXABLE_ENTERED_DR
388 , TAXABLE_ENTERED_CR
389 , TAXABLE_ACCOUNTED_DR
390 , TAXABLE_ACCOUNTED_CR
391 , TAX_LINK_ID
392 , THIRD_PARTY_ID
393 , THIRD_PARTY_SUB_ID
394 , REVERSED_SOURCE_ID
395 , TAX_CODE_ID
396 , LOCATION_SEGMENT_ID
397 , SOURCE_TYPE_SECONDARY
398 , TAX_GROUP_CODE_ID
399 , REF_CUSTOMER_TRX_LINE_ID
400 , REF_CUST_TRX_LINE_GL_DIST_ID
401 , REF_ACCOUNT_CLASS
402 , ACTIVITY_BUCKET
403 , REF_LINE_ID
404 , FROM_AMOUNT_DR
405 , FROM_AMOUNT_CR
406 , FROM_ACCTD_AMOUNT_DR
407 , FROM_ACCTD_AMOUNT_CR
408 , REF_MF_DIST_FLAG
409 , REF_DIST_CCID)
410 SELECT
411 ar_distributions_s.nextval -- LINE_ID
412 , ard.SOURCE_ID -- SOURCE_ID
413 , ard.source_table -- SOURCE_TABLE
414 , ard.SOURCE_TYPE -- SOURCE_TYPE
415 , DECODE(double.side,'CASH',
416 crh.account_code_combination_id,
417 ard.CODE_COMBINATION_ID) -- code_combination_id
418 , DECODE(double.side,'APP',
419 --
420 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
421 ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL),
422 --
423 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
424 NULL, ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)))) --AMOUNT_DR
425 --
426 , DECODE(double.side,'APP',
427 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
428 NULL,ABS(NVL(ard.AMOUNT_DR,0)-NVL(ard.AMOUNT_CR,0))),
429 --
430 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
431 ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL)) --AMOUNT_CR
432 --
433 , DECODE(double.side,'APP',
434 --
435 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
436 ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL),
437 --
438 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
439 NULL, ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)))) --ACCTD_AMOUNT_DR
440 --
441 , DECODE(double.side,'APP',
442 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
443 NULL,ABS(NVL(ard.ACCTD_AMOUNT_DR,0)-NVL(ard.ACCTD_AMOUNT_CR,0))),
444 --
445 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
446 ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL)) --ACCTD_AMOUNT_CR
447 --
448 , SYSDATE -- CREATION_DATE
449 , 0 -- CREATED_BY
450 , 0 -- LAST_UPDATED_BY
451 , SYSDATE -- LAST_UPDATE_DATE
452 , 0 -- LAST_UPDATE_LOGIN
453 , ard.ORG_ID
454 , DECODE(double.side,'APP',
455 'UPMFRAREAR','UPMFCRREAR') -- SOURCE_TABLE_SECONDARY
456 , DECODE(double.side,'CASH'
457 , crh.cash_receipt_history_id
458 , ard.SOURCE_ID) -- SOURCE_ID_SECONDARY
459 , ard.CURRENCY_CODE
460 , ard.CURRENCY_CONVERSION_RATE
461 , ard.CURRENCY_CONVERSION_TYPE
462 , ard.CURRENCY_CONVERSION_DATE
463 , ard.TAXABLE_ENTERED_DR
464 , ard.TAXABLE_ENTERED_CR
465 , ard.TAXABLE_ACCOUNTED_DR
466 , ard.TAXABLE_ACCOUNTED_CR
467 , ard.TAX_LINK_ID
468 , ard.THIRD_PARTY_ID
469 , ard.THIRD_PARTY_SUB_ID
470 , ard.REVERSED_SOURCE_ID
471 , ard.TAX_CODE_ID
472 , ard.LOCATION_SEGMENT_ID
473 , 'PSA_MF_RCT_DIST_ALL' -- SOURCE_TYPE_SECONDARY
474 , ard.TAX_GROUP_CODE_ID
475 , ard.REF_CUSTOMER_TRX_LINE_ID
476 , ard.REF_CUST_TRX_LINE_GL_DIST_ID
477 , ard.REF_ACCOUNT_CLASS
478 , ard.ACTIVITY_BUCKET
479 , ard.REF_LINE_ID
480 --
481 , DECODE(double.side,'APP',
482 --
483 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
484 ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL),
485 --
486 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
487 NULL, ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)))) --FROM_AMOUNT_DR
488 --
489 , DECODE(double.side,'APP',
490 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
491 NULL,ABS(NVL(ard.AMOUNT_DR,0)-NVL(ard.AMOUNT_CR,0))),
492 --
493 DECODE(SIGN(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),1,
494 ABS(NVL(ard.AMOUNT_CR,0)-NVL(ard.AMOUNT_DR,0)),NULL)) --FROM_AMOUNT_CR
495 --
496 , DECODE(double.side,'APP',
497 --
498 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
499 ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL),
500 --
501 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
502 NULL, ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)))) --FROM_ACCTD_AMOUNT_DR
503 --
504 , DECODE(double.side,'APP',
505 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
506 NULL,ABS(NVL(ard.ACCTD_AMOUNT_DR,0)-NVL(ard.ACCTD_AMOUNT_CR,0))),
507 --
508 DECODE(SIGN(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),1,
509 ABS(NVL(ard.ACCTD_AMOUNT_CR,0)-NVL(ard.ACCTD_AMOUNT_DR,0)),NULL)) --FROM_ACCTD_AMOUNT_CR
510 --
511 , DECODE(double.side,'CASH','N','Y') -- REF_MF_DIST_FLAG
512 , ard.REF_DIST_CCID
513 FROM ar_receivable_applications_all app,
514 ar_distributions_all ard,
515 (SELECT 'CASH' side FROM DUAL UNION
516 SELECT 'APP' side FROM DUAL ) double,
517 ar_cash_receipt_history_all crh
518 WHERE app.rowid >= l_start_rowid
519 AND app.rowid <= l_end_rowid
520 AND app.receivable_application_id = ard.source_id
521 AND ard.source_table = 'RA'
522 AND app.cash_receipt_history_id = crh.cash_receipt_history_id(+)
523 AND DECODE(double.side,'CASH',
524 DECODE(crh.cash_receipt_history_id,
525 NULL,'N','Y'),
526 'APP' ,'Y') = 'Y'
527 AND EXISTS (SELECT NULL FROM psa_mf_rct_dist_all psa
528 WHERE psa.receivable_application_id = app.receivable_application_id);
529
530 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
531
532 -- UPgrade Multi Fund Receipt Application MIgrated to AR
533
534 INSERT INTO ar_distributions_all (
535 LINE_ID
536 , SOURCE_ID
537 , SOURCE_TABLE
538 , SOURCE_TYPE
539 , CODE_COMBINATION_ID
540 , AMOUNT_DR
541 , AMOUNT_CR
542 , ACCTD_AMOUNT_DR
543 , ACCTD_AMOUNT_CR
544 , CREATION_DATE
545 , CREATED_BY
546 , LAST_UPDATED_BY
547 , LAST_UPDATE_DATE
548 , LAST_UPDATE_LOGIN
549 , ORG_ID
550 , SOURCE_TABLE_SECONDARY
551 , SOURCE_ID_SECONDARY
552 , CURRENCY_CODE
553 , CURRENCY_CONVERSION_RATE
554 , CURRENCY_CONVERSION_TYPE
555 , CURRENCY_CONVERSION_DATE
556 , TAXABLE_ENTERED_DR
557 , TAXABLE_ENTERED_CR
558 , TAXABLE_ACCOUNTED_DR
559 , TAXABLE_ACCOUNTED_CR
560 , TAX_LINK_ID
561 , THIRD_PARTY_ID
562 , THIRD_PARTY_SUB_ID
563 , REVERSED_SOURCE_ID
564 , TAX_CODE_ID
565 , LOCATION_SEGMENT_ID
566 , SOURCE_TYPE_SECONDARY
567 , TAX_GROUP_CODE_ID
568 , REF_CUSTOMER_TRX_LINE_ID
569 , REF_CUST_TRX_LINE_GL_DIST_ID
570 , REF_ACCOUNT_CLASS
571 , ACTIVITY_BUCKET
572 , REF_LINE_ID
573 , FROM_AMOUNT_DR
574 , FROM_AMOUNT_CR
575 , FROM_ACCTD_AMOUNT_DR
576 , FROM_ACCTD_AMOUNT_CR
577 , REF_MF_DIST_FLAG
578 , REF_DIST_CCID)
579 SELECT /*+ ordered rowid(app) use_nl(a,psatd,ctlgd,trx,crh) INDEX(a psa_mf_rct_dist_u1) INDEX(psatd psa_mf_trx_dist_u1)
580 INDEX(ctlgd ra_cust_trx_line_gl_dist_u1) */
581 ar_distributions_s.nextval -- LINE_ID
582 , a.receivable_application_id --SOURCE_ID
583 , 'RA' -- SOURCE_TABLE
584 , b.source_type
585 , DECODE(doub.side,'D',
586 DECODE(SIGN( DECODE(b.source_type,
587 'REC' ,a.amount,
588 'EDISC' ,a.discount_amount,
589 'UNEDISC',a.ue_discount_amount)),1,
590 DECODE( b.source_type,
591 'REC' ,a.mf_cash_ccid,
592 'EDISC' ,a.discount_ccid,
593 'UNEDISC',a.ue_discount_ccid),
594 psatd.mf_receivables_ccid),
595 DECODE(SIGN(DECODE
596 ( b.source_type,
597 'REC' ,a.amount,
598 'EDISC' ,a.discount_amount,
599 'UNEDISC',a.ue_discount_amount)
600 ), 1 , psatd.mf_receivables_ccid,
601 DECODE( b.source_type,
602 'REC' ,a.mf_cash_ccid,
603 'EDISC' ,a.discount_ccid,
604 'UNEDISC',a.ue_discount_ccid))) -- CODE_COMBINATION_ID
605 , TO_NUMBER(DECODE(doub.side,'C',
606 DECODE(SIGN(
607 DECODE( b.source_type,
608 'REC' ,a.amount,
609 'EDISC' ,a.discount_amount,
610 'UNEDISC',a.ue_discount_amount)),1,
611 DECODE( b.source_type,
612 'REC' ,NULL,
613 'EDISC' ,NULL,
614 'UNEDISC',NULL),
615 DECODE( b.source_type,
616 'REC' ,-1 * a.amount,
617 'EDISC' ,-1 * a.discount_amount,
618 'UNEDISC',-1 * a.ue_discount_amount)),
619 DECODE(SIGN(
620 DECODE( b.source_type,
621 'REC' ,a.amount,
622 'EDISC' ,a.discount_amount,
623 'UNEDISC',a.ue_discount_amount)),1,
624 DECODE( b.source_type,
625 'REC' ,a.amount,
626 'EDISC' ,a.discount_amount,
627 'UNEDISC',a.ue_discount_amount),
628 DECODE( b.source_type,
629 'REC' ,NULL,
630 'EDISC' ,NULL,
631 'UNEDISC',NULL)))) -- AMOUNT_DR
632 , TO_NUMBER(DECODE(doub.side,'D',
633 DECODE(SIGN(
634 DECODE( b.source_type,
635 'REC' ,a.amount,
636 'EDISC' ,a.discount_amount,
637 'UNEDISC',a.ue_discount_amount)),1,
638 DECODE( b.source_type,
639 'REC' ,NULL,
640 'EDISC' ,NULL, --a.discount_amount,
641 'UNEDISC',NULL),
642 DECODE( b.source_type,
643 'REC' ,-1 * a.amount,
644 'EDISC' ,-1 * a.discount_amount,
645 'UNEDISC',-1 * a.ue_discount_amount)),
646 DECODE(SIGN(
647 DECODE( b.source_type,
648 'REC' ,a.amount,
649 'EDISC' ,a.discount_amount,
650 'UNEDISC',a.ue_discount_amount)),1,
651 DECODE( b.source_type,
652 'REC' ,a.amount,
653 'EDISC' ,a.discount_amount,
654 'UNEDISC',a.ue_discount_amount),
655 DECODE( b.source_type,
656 'REC' ,NULL,
657 'EDISC' ,NULL,
658 'UNEDISC',NULL)))) -- AMOUNT_CR
659 , TO_NUMBER(DECODE(doub.side,'C',
660 DECODE(SIGN(
661 DECODE( b.source_type,
662 'REC' ,a.amount,
663 'EDISC' ,a.discount_amount,
664 'UNEDISC',a.ue_discount_amount)),1,
665 DECODE( b.source_type,
666 'REC' ,NULL,
667 'EDISC' ,NULL, --a.discount_amount,
668 'UNEDISC',NULL), --a.ue_discount_amount),
669 DECODE( b.source_type,
670 'REC' ,-1 * a.amount,
671 'EDISC' ,-1 * a.discount_amount, --NULL,
672 'UNEDISC',-1 * a.ue_discount_amount)),
673 DECODE(SIGN(
674 DECODE( b.source_type,
675 'REC' ,a.amount,
676 'EDISC' ,a.discount_amount,
677 'UNEDISC',a.ue_discount_amount)),1,
678 DECODE( b.source_type,
679 'REC' ,a.amount,
680 'EDISC' ,a.discount_amount, --NULL,
681 'UNEDISC',a.ue_discount_amount),
682 DECODE( b.source_type,
683 'REC' ,NULL,
684 'EDISC' ,NULL,
685 'UNEDISC',NULL)))) -- ACCTD_AMOUNT_DR
686 , TO_NUMBER(DECODE(doub.side,'D',
687 DECODE(SIGN(
688 DECODE( b.source_type,
689 'REC' ,a.amount,
690 'EDISC' ,a.discount_amount,
691 'UNEDISC',a.ue_discount_amount)),1,
692 DECODE( b.source_type,
693 'REC' ,NULL,
694 'EDISC' ,NULL, --a.discount_amount,
695 'UNEDISC',NULL),
696 DECODE( b.source_type,
697 'REC' ,-1 * a.amount,
698 'EDISC' ,-1 * a.discount_amount,
699 'UNEDISC',-1 * a.ue_discount_amount)),
700 DECODE(SIGN(
701 DECODE( b.source_type,
702 'REC' ,a.amount,
703 'EDISC' ,a.discount_amount,
704 'UNEDISC',a.ue_discount_amount)),1,
705 DECODE( b.source_type,
706 'REC' ,a.amount,
707 'EDISC' ,a.discount_amount,
708 'UNEDISC',a.ue_discount_amount),
709 DECODE( b.source_type,
710 'REC' ,NULL,
711 'EDISC' ,NULL,
712 'UNEDISC',NULL)))) -- ACCTD_AMOUNT_CR
713 , SYSDATE -- CREATION_DATE
714 , 0 -- CREATED_BY
715 , 0 -- LAST_UPDATED_BY
716 , SYSDATE -- LAST_UPDATE_DATE
717 , 0 -- LAST_UPDATE_LOGIN
718 , app.org_id -- ORG_ID
719 ,DECODE(doub.side,'D',
720 DECODE(SIGN( DECODE(b.source_type,
721 'REC' ,a.amount,
722 'EDISC' ,a.discount_amount,
723 'UNEDISC',a.ue_discount_amount)),1,
724 DECODE( b.source_type,
725 'REC' ,DECODE(crh.status,NULL,'UPMFRAMIAR','UPMFCHMIAR'),
726 'EDISC' ,'UPMFRAMIAR' ,
727 'UNEDISC','UPMFRAMIAR' ),
728 DECODE( b.source_type,
729 'REC' ,'UPMFRAMIAR',
730 'EDISC' ,'UPMFRAMIAR',
731 'UNEDISC','UPMFRAMIAR')),
732 DECODE(SIGN( DECODE(b.source_type,
733 'REC' ,a.amount,
734 'EDISC' ,a.discount_amount,
735 'UNEDISC',a.ue_discount_amount)),1,
736 DECODE( b.source_type,
737 'REC' ,'UPMFRAMIAR',
738 'EDISC' ,'UPMFRAMIAR',
739 'UNEDISC','UPMFRAMIAR'),
740 DECODE( b.source_type,
741 'REC' , DECODE(crh.status,NULL,'UPMFRAMIAR','UPMFCHMIAR'),
742 'EDISC' ,'UPMFRAMIAR',
743 'UNEDISC','UPMFRAMIAR'))) -- SOURCE_TABLE_SECONDARY
744 ,DECODE(doub.side,'D',
745 DECODE(SIGN( DECODE(b.source_type,
746 'REC' ,a.amount,
747 'EDISC' ,a.discount_amount,
748 'UNEDISC',a.ue_discount_amount)),1,
749 DECODE( b.source_type,
750 'REC' ,DECODE(crh.status,NULL,
751 a.receivable_application_id,
752 crh.cash_receipt_history_id),
753 'EDISC' ,a.receivable_application_id ,
754 'UNEDISC',a.receivable_application_id ),
755 DECODE( b.source_type,
756 'REC' , a.receivable_application_id,
757 'EDISC' ,a.receivable_application_id ,
758 'UNEDISC',a.receivable_application_id )),
759 DECODE(SIGN( DECODE(b.source_type,
760 'REC' ,a.amount,
761 'EDISC' ,a.discount_amount,
762 'UNEDISC',a.ue_discount_amount)),1,
763 DECODE( b.source_type,
764 'REC' , a.receivable_application_id,
765 'EDISC' ,a.receivable_application_id ,
766 'UNEDISC',a.receivable_application_id ),
767 DECODE( b.source_type,
768 'REC' , DECODE(crh.status,NULL,
769 a.receivable_application_id,
770 crh.cash_receipt_history_id),
771 'EDISC' ,a.receivable_application_id ,
772 'UNEDISC',a.receivable_application_id ))) -- SOURCE_ID_SECONDARY
773 , trx.invoice_currency_code -- CURRENCY_CODE
774 , NULL -- CURRENCY_CONVERSION_RATE
775 , NULL -- CURRENCY_CONVERSION_TYPE
776 , NULL -- CURRENCY_CONVERSION_DATE
777 , NULL -- TAXABLE_ENTERED_DR
778 , NULL -- TAXABLE_ENTERED_CR
779 , NULL -- TAXABLE_ACCOUNTED_DR
780 , NULL -- TAXABLE_ACCOUNTED_CR
781 , NULL -- TAX_LINK_ID
782 , NULL -- THIRD_PARTY_ID
783 , NULL -- THIRD_PARTY_SUB_ID
784 , NULL -- REVERSED_SOURCE_ID
785 , NULL -- TAX_CODE_ID
786 , NULL -- LOCATION_SEGMENT_ID
787 , 'PSA_MF_RCT_DIST_ALL' -- SOURCE_TYPE_SECONDARY
788 , NULL -- TAX_GROUP_CODE_ID
789 , ctlgd.customer_trx_line_id -- REF_CUSTOMER_TRX_LINE_ID
790 , ctlgd.cust_trx_line_gl_dist_id -- REF_CUST_TRX_LINE_GL_DIST_ID
791 , ctlgd.account_class -- REF_ACCOUNT_CLASS
792 , CASE
793 WHEN b.source_type = 'REC' THEN
794 DECODE(ctlgd.account_class, 'REV', 'APP_LINE',
795 'TAX', 'APP_TAX',
796 'FREIGHT', 'APP_FRT','APP_LINE')
797 WHEN b.source_type = 'EDISC' THEN
798 DECODE(ctlgd.account_class, 'REV', 'ED_LINE',
799 'TAX', 'ED_TAX',
800 'FREIGHT', 'ED_FRT','ED_LINE')
801 ELSE
802 DECODE(ctlgd.account_class, 'REV', 'UNED_LINE',
803 'TAX', 'UNED_TAX',
804 'FREIGHT', 'UNED_FRT','UNED_LINE')
805 END -- ACTIVITY_BUCKET
806 , NULL -- REF_LINE_ID
807 , TO_NUMBER(DECODE(doub.side,'C',
808 DECODE(SIGN(
809 DECODE( b.source_type,
810 'REC' ,a.amount,
811 'EDISC' ,a.discount_amount,
812 'UNEDISC',a.ue_discount_amount)),1,
813 DECODE( b.source_type,
814 'REC' ,NULL,
815 'EDISC' ,NULL,
816 'UNEDISC',NULL),
817 DECODE( b.source_type,
818 'REC' ,-1 * a.amount,
819 'EDISC' ,-1 * a.discount_amount,
820 'UNEDISC',-1 * a.ue_discount_amount)),
821 DECODE(SIGN(
822 DECODE( b.source_type,
823 'REC' ,a.amount,
824 'EDISC' ,a.discount_amount,
825 'UNEDISC',a.ue_discount_amount)),1,
826 DECODE( b.source_type,
827 'REC' ,a.amount,
828 'EDISC' ,a.discount_amount,
829 'UNEDISC',a.ue_discount_amount),
830 DECODE( b.source_type,
831 'REC' ,NULL,
832 'EDISC' ,NULL,
833 'UNEDISC',NULL)))) --FROM_AMOUNT_DR
834 , TO_NUMBER(DECODE(doub.side,'D',
835 DECODE(SIGN(
836 DECODE( b.source_type,
837 'REC' ,a.amount,
838 'EDISC' ,a.discount_amount,
839 'UNEDISC',a.ue_discount_amount)),1,
840 DECODE( b.source_type,
841 'REC' ,NULL,
842 'EDISC' ,NULL,
843 'UNEDISC',NULL),
844 DECODE( b.source_type,
845 'REC' ,-1 * a.amount,
846 'EDISC' ,-1 * a.discount_amount,
847 'UNEDISC',-1 * a.ue_discount_amount)),
848 DECODE(SIGN(
849 DECODE( b.source_type,
850 'REC' ,a.amount,
851 'EDISC' ,a.discount_amount,
852 'UNEDISC',a.ue_discount_amount)),1,
853 DECODE( b.source_type,
854 'REC' ,a.amount,
855 'EDISC' ,a.discount_amount,
856 'UNEDISC',a.ue_discount_amount),
857 DECODE( b.source_type,
858 'REC' ,NULL,
859 'EDISC' ,NULL,
860 'UNEDISC',NULL)))) -- FROM_AMOUNT_CR
861 , TO_NUMBER(DECODE(doub.side,'C',
862 DECODE(SIGN(
863 DECODE( b.source_type,
864 'REC' ,a.amount,
865 'EDISC' ,a.discount_amount,
866 'UNEDISC',a.ue_discount_amount)),1,
867 DECODE( b.source_type,
868 'REC' ,NULL,
869 'EDISC' ,NULL,
870 'UNEDISC',NULL),
871 DECODE( b.source_type,
872 'REC' ,-1 * a.amount,
873 'EDISC' ,-1 * a.discount_amount,
874 'UNEDISC',-1 * a.ue_discount_amount)),
875 DECODE(SIGN(
876 DECODE( b.source_type,
877 'REC' ,a.amount,
878 'EDISC' ,a.discount_amount,
879 'UNEDISC',a.ue_discount_amount)),1,
880 DECODE( b.source_type,
881 'REC' ,a.amount,
882 'EDISC' ,a.discount_amount,
883 'UNEDISC',a.ue_discount_amount),
884 DECODE( b.source_type,
885 'REC' ,NULL,
886 'EDISC' ,NULL,
887 'UNEDISC',NULL)))) --FROM_ACCTD_AMOUNT_DR
888 , TO_NUMBER(DECODE(doub.side,'D',
889 DECODE(SIGN(
890 DECODE( b.source_type,
891 'REC' ,a.amount,
892 'EDISC' ,a.discount_amount,
893 'UNEDISC',a.ue_discount_amount)),1,
894 DECODE( b.source_type,
895 'REC' ,NULL,
896 'EDISC' ,NULL,
897 'UNEDISC',NULL),
898 DECODE( b.source_type,
899 'REC' ,-1 * a.amount,
900 'EDISC' ,-1 * a.discount_amount,
901 'UNEDISC',-1 * a.ue_discount_amount)),
902 DECODE(SIGN(
903 DECODE( b.source_type,
904 'REC' ,a.amount,
905 'EDISC' ,a.discount_amount,
906 'UNEDISC',a.ue_discount_amount)),1,
907 DECODE( b.source_type,
908 'REC' ,a.amount,
909 'EDISC' ,a.discount_amount,
910 'UNEDISC',a.ue_discount_amount),
911 DECODE( b.source_type,
912 'REC' ,NULL,
913 'EDISC' ,NULL,
914 'UNEDISC',NULL)))) --FROM_ACCTD_AMOUNT_CR
915 ,DECODE(doub.side,'D',
916 DECODE(SIGN( DECODE(b.source_type,
917 'REC' ,a.amount,
918 'EDISC' ,a.discount_amount,
919 'UNEDISC',a.ue_discount_amount)),1,
920 DECODE( b.source_type,
921 'REC' ,DECODE(crh.status,NULL,'Y','N'),
922 'EDISC' ,'Y' ,
923 'UNEDISC','Y' ),
924 DECODE( b.source_type,
925 'REC' ,'Y',
926 'EDISC' ,'Y',
927 'UNEDISC','Y')),
928 DECODE(SIGN( DECODE(b.source_type,
929 'REC' ,a.amount,
930 'EDISC' ,a.discount_amount,
931 'UNEDISC',a.ue_discount_amount)),1,
932 DECODE( b.source_type,
933 'REC' ,'Y',
934 'EDISC' ,'Y',
935 'UNEDISC','Y'),
936 DECODE( b.source_type,
937 'REC' , DECODE(crh.status,NULL,'Y','N'),
938 'EDISC' ,'Y',
939 'UNEDISC','Y'))) -- REF_MF_DIST_FLAG
940 , NULL -- REF_DIST_CCID
941 FROM
942 ar_receivable_applications_all app,
943 psa_mf_rct_dist_all a,
944 psa_mf_trx_dist_all psatd,
945 ra_cust_trx_line_gl_dist_all ctlgd,
946 (SELECT 'REC' source_type FROM DUAL UNION
947 SELECT 'EDISC' source_type FROM DUAL UNION
948 SELECT 'UNEDISC' source_type FROM DUAL ) b,
949 (SELECT 'D' side FROM DUAL UNION
950 SELECT 'C' side FROM DUAL ) doub,
951 ra_customer_trx_all trx,
952 ar_cash_receipt_history_all crh
953 WHERE app.rowid >= l_start_rowid
954 AND app.rowid <= l_end_rowid
955 AND app.receivable_application_id = a.receivable_application_id
956 AND a.cust_trx_line_gl_dist_id = psatd.cust_trx_line_gl_dist_id
957 AND psatd.cust_trx_line_gl_dist_id = ctlgd.cust_trx_line_gl_dist_id
958 AND app.applied_customer_trx_id = trx.customer_trx_id
959 AND app.cash_receipt_history_id = crh.cash_receipt_history_id(+)
960 AND NVL(DECODE(b.source_type, 'REC' ,a.AMOUNT,
961 'EDISC' ,a.DISCOUNT_AMOUNT,
962 'UNEDISC',a.UE_DISCOUNT_AMOUNT),0) <> 0;
963
964
965
966 UPDATE ar_receivable_applications_all app
967 SET upgrade_method = '11I_MFAR_UPG'
968 WHERE app.rowid >= l_start_rowid
969 AND app.rowid <= l_end_rowid
970 AND EXISTS (SELECT NULL FROM psa_mf_rct_dist_all psa
971 WHERE psa.receivable_application_id = app.receivable_application_id);
972
973
974 ad_parallel_updates_pkg.processed_rowid_range(
975 l_rows_processed,
976 l_end_rowid);
977
978 commit;
979
980 ad_parallel_updates_pkg.get_rowid_range(
981 l_start_rowid,
982 l_end_rowid,
983 l_any_rows_to_process,
984 l_batch_size,
985 FALSE);
986
987 l_rows_processed := 0 ;
988
989 END LOOP ; /* end of WHILE loop */
990
991
992 EXCEPTION
993 WHEN NO_DATA_FOUND THEN
994 NULL;
995 WHEN OTHERS THEN
996 RAISE;
997 END upgrade_applications;
998
999
1000
1001
1002
1003
1004
1005
1006 PROCEDURE upgrade_misc_cash_dist(
1007 l_table_owner IN VARCHAR2,
1008 l_table_name IN VARCHAR2,
1009 l_script_name IN VARCHAR2,
1010 l_worker_id IN VARCHAR2,
1011 l_num_workers IN VARCHAR2,
1012 l_batch_size IN VARCHAR2,
1013 l_batch_id IN NUMBER,
1014 l_action_flag IN VARCHAR2)
1015 IS
1016 l_start_rowid rowid;
1017 l_end_rowid rowid;
1018 l_any_rows_to_process boolean;
1019 l_rows_processed number := 0;
1020 BEGIN
1021
1022 /* ------ Initialize the rowid ranges ------ */
1023 ad_parallel_updates_pkg.initialize_rowid_range(
1024 ad_parallel_updates_pkg.ROWID_RANGE,
1025 l_table_owner,
1026 l_table_name,
1027 l_script_name,
1028 l_worker_id,
1029 l_num_workers,
1030 l_batch_size, 0);
1031
1032 /* ------ Get rowid ranges ------ */
1033 ad_parallel_updates_pkg.get_rowid_range(
1034 l_start_rowid,
1035 l_end_rowid,
1036 l_any_rows_to_process,
1037 l_batch_size,
1038 TRUE);
1039
1040
1041 WHILE ( l_any_rows_to_process = TRUE )
1042 LOOP
1043
1044
1045
1046
1047 -- UPgrade Multi Fund Misc Cash distributions MIgrated to AR
1048 -- Only the posted MCD need to be upgraded - non posted AAD will do it
1049 INSERT INTO ar_distributions_all (
1050 LINE_ID
1051 , SOURCE_ID
1052 , SOURCE_TABLE
1053 , SOURCE_TYPE
1054 , CODE_COMBINATION_ID
1055 , AMOUNT_DR
1056 , AMOUNT_CR
1057 , ACCTD_AMOUNT_DR
1058 , ACCTD_AMOUNT_CR
1059 , CREATION_DATE
1060 , CREATED_BY
1061 , LAST_UPDATED_BY
1062 , LAST_UPDATE_DATE
1063 , LAST_UPDATE_LOGIN
1064 , ORG_ID
1065 , SOURCE_TABLE_SECONDARY
1066 , SOURCE_ID_SECONDARY
1067 , CURRENCY_CODE
1068 , CURRENCY_CONVERSION_RATE
1069 , CURRENCY_CONVERSION_TYPE
1070 , CURRENCY_CONVERSION_DATE
1071 , TAXABLE_ENTERED_DR
1072 , TAXABLE_ENTERED_CR
1073 , TAXABLE_ACCOUNTED_DR
1074 , TAXABLE_ACCOUNTED_CR
1075 , TAX_LINK_ID
1076 , THIRD_PARTY_ID
1077 , THIRD_PARTY_SUB_ID
1078 , REVERSED_SOURCE_ID
1079 , TAX_CODE_ID
1080 , LOCATION_SEGMENT_ID
1081 , SOURCE_TYPE_SECONDARY
1082 , TAX_GROUP_CODE_ID
1083 , REF_CUSTOMER_TRX_LINE_ID
1084 , REF_CUST_TRX_LINE_GL_DIST_ID
1085 , REF_ACCOUNT_CLASS
1086 , ACTIVITY_BUCKET
1087 , REF_LINE_ID
1088 , FROM_AMOUNT_DR
1089 , FROM_AMOUNT_CR
1090 , FROM_ACCTD_AMOUNT_DR
1091 , FROM_ACCTD_AMOUNT_CR
1092 , REF_MF_DIST_FLAG
1093 , REF_DIST_CCID)
1094 SELECT /*+ ordered rowid(mcd) use_nl(psamcd,cr) INDEX(psamcd psa_mf_misc_dist_u1) */
1095 ar_distributions_s.nextval -- LINE_ID
1096 , psamcd.misc_cash_distribution_id -- SOURCE_ID
1097 , 'MCD' -- SOURCE_TABLE
1098 , 'MISCCASH' -- SOURCE_TYPE
1099 , CASE
1100 WHEN doub.side = 'C' THEN
1101 DECODE(SIGN(mcd.amount),
1102 1, psamcd.cash_ccid,
1103 psamcd.distribution_ccid)
1104 ELSE
1105 DECODE(SIGN(mcd.amount),
1106 -1, psamcd.cash_ccid,
1107 psamcd.distribution_ccid)
1108 END -- CODE_COMBINATION_ID
1109 , DECODE(doub.side,'D',ABS(mcd.amount),NULL ) -- AMOUNT_DR
1110 , DECODE(doub.side,'C',ABS(mcd.amount),NULL ) -- AMOUNT_CR
1111 , DECODE(doub.side,'D',ABS(mcd.amount),NULL ) -- ACCTD_AMOUNT_DR
1112 , DECODE(doub.side,'C',ABS(mcd.amount),NULL ) -- ACCTD_AMOUNT_CR
1113 , SYSDATE -- CREATION_DATE
1114 , 0 -- CREATED_BY
1115 , 0 -- LAST_UPDATED_BY
1116 , SYSDATE -- LAST_UPDATE_DATE
1117 , 0 -- LAST_UPDATE_LOGIN
1118 , mcd.org_id -- ORG_ID
1119 , CASE
1120 WHEN doub.side = 'C' THEN
1121 DECODE(SIGN(mcd.amount),
1122 1, 'UPMFMCMIAR',
1123 'UPMFMCREAR')
1124 ELSE
1125 DECODE(SIGN(mcd.amount),
1126 -1, 'UPMFMCMIAR',
1127 'UPMFMCREAR')
1128 END -- SOURCE_TABLE_SECONDARY
1129 , NULL -- SOURCE_ID_SECONDARY
1130 , cr.currency_code -- CURRENCY_CODE
1131 , NULL -- CURRENCY_CONVERSION_RATE
1132 , NULL -- CURRENCY_CONVERSION_TYPE
1133 , NULL -- CURRENCY_CONVERSION_DATE
1134 , NULL -- TAXABLE_ENTERED_DR
1135 , NULL -- TAXABLE_ENTERED_CR
1136 , NULL -- TAXABLE_ACCOUNTED_DR
1137 , NULL -- TAXABLE_ACCOUNTED_CR
1138 , NULL -- TAX_LINK_ID
1139 , NULL -- THIRD_PARTY_ID
1140 , NULL -- THIRD_PARTY_SUB_ID
1141 , NULL -- REVERSED_SOURCE_ID
1142 , NULL -- TAX_CODE_ID
1143 , NULL -- LOCATION_SEGMENT_ID
1144 , 'PSA_MF_MISC_DIST_ALL' -- SOURCE_TYPE_SECONDARY
1145 , NULL -- TAX_GROUP_CODE_ID
1146 , NULL -- REF_CUSTOMER_TRX_LINE_ID
1147 , NULL -- REF_CUST_TRX_LINE_GL_DIST_ID
1148 , NULL -- REF_ACCOUNT_CLASS
1149 , NULL -- ACTIVITY_BUCKET
1150 , NULL -- REF_LINE_ID
1151 , DECODE(doub.side,'D',ABS(mcd.amount),NULL ) -- FROM_AMOUNT_DR
1152 , DECODE(doub.side,'C',ABS(mcd.amount),NULL ) -- FROM_AMOUNT_CR
1153 , DECODE(doub.side,'D',ABS(mcd.amount),NULL ) -- FROM_ACCTD_AMOUNT_DR
1154 , DECODE(doub.side,'C',ABS(mcd.amount),NULL ) -- FROM_ACCTD_AMOUNT_CR
1155 , NULL -- REF_MF_DIST_FLAG
1156 , NULL -- REF_DIST_CCID
1157 FROM ar_misc_cash_distributions_all mcd,
1158 psa_mf_misc_dist_all psamcd,
1159 ar_cash_receipts_all cr,
1160 (SELECT 'D' side FROM DUAL UNION ALL
1161 SELECT 'C' side FROM DUAL) doub
1162 WHERE mcd.rowid >= l_start_rowid
1163 AND mcd.rowid <= l_end_rowid
1164 AND mcd.misc_cash_distribution_id = psamcd.misc_cash_distribution_id
1165 AND mcd.cash_receipt_id = cr.cash_receipt_id
1166 AND mcd.posting_control_id <> -3;
1167
1168
1169 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1170
1171 ad_parallel_updates_pkg.processed_rowid_range(
1172 l_rows_processed,
1173 l_end_rowid);
1174
1175 commit;
1176
1177 ad_parallel_updates_pkg.get_rowid_range(
1178 l_start_rowid,
1179 l_end_rowid,
1180 l_any_rows_to_process,
1181 l_batch_size,
1182 FALSE);
1183
1184 l_rows_processed := 0 ;
1185
1186 END LOOP ; /* end of WHILE loop */
1187
1188
1189 EXCEPTION
1190 WHEN NO_DATA_FOUND THEN
1191 NULL;
1192 WHEN OTHERS THEN
1193 RAISE;
1194 END upgrade_misc_cash_dist;
1195
1196
1197
1198
1199
1200
1201 PROCEDURE UPGRADE_TRANSACTIONS(
1202 l_table_owner IN VARCHAR2,
1203 l_table_name IN VARCHAR2,
1204 l_script_name IN VARCHAR2,
1205 l_worker_id IN VARCHAR2,
1206 l_num_workers IN VARCHAR2,
1207 l_batch_size IN VARCHAR2,
1208 l_batch_id IN NUMBER,
1209 l_action_flag IN VARCHAR2)
1210 IS
1211 ln_AE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1212 ln_AE_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1213 ln_APPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1214 ln_CODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1215 ln_GL_TRANSFER_MODE_CODE DBMS_SQL.VARCHAR2_TABLE;
1216 ln_GL_SL_LINK_ID DBMS_SQL.NUMBER_TABLE;
1217 ln_ACCOUNTING_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1218 ln_PARTY_ID DBMS_SQL.NUMBER_TABLE;
1219 ln_PARTY_SITE_ID DBMS_SQL.NUMBER_TABLE;
1220 ln_PARTY_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1221 ln_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1222 ln_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1223 ln_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1224 ln_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1225 ln_DESCRIPTION DBMS_SQL.VARCHAR2_TABLE;
1226 ln_STATISTICAL_AMOUNT DBMS_SQL.NUMBER_TABLE;
1227 ln_CURRENCY_CODE DBMS_SQL.VARCHAR2_TABLE;
1228 ln_CURRENCY_CONVERSION_DATE DBMS_SQL.DATE_TABLE;
1229 ln_CURRENCY_CONVERSION_RATE DBMS_SQL.NUMBER_TABLE;
1230 ln_CURRENCY_CONVERSION_TYPE DBMS_SQL.VARCHAR2_TABLE;
1231 ln_USSGL_TRANSACTION_CODE DBMS_SQL.VARCHAR2_TABLE;
1232 ln_JGZZ_RECON_REF DBMS_SQL.VARCHAR2_TABLE;
1233 ln_CONTROL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1234 ln_ANALYTICAL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1235 ln_GL_SL_LINK_TABLE DBMS_SQL.VARCHAR2_TABLE;
1236 ln_DISPLAYED_LINE_NUMBER DBMS_SQL.NUMBER_TABLE;
1237 ln_UPG_BATCH_ID DBMS_SQL.NUMBER_TABLE;
1238 ln_UNROUNDED_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1239 ln_UNROUNDED_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1240 ln_GAIN_OR_LOSS_FLAG DBMS_SQL.VARCHAR2_TABLE;
1241 ln_UNROUNDED_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1242 ln_UNROUNDED_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1243 ln_SUBSTITUTED_CCID DBMS_SQL.NUMBER_TABLE;
1244 ln_BUSINESS_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1245 ln_MPA_ACCRUAL_ENTRY_FLAG DBMS_SQL.VARCHAR2_TABLE;
1246 ln_ENCUMBRANCE_TYPE_ID DBMS_SQL.NUMBER_TABLE;
1247 ln_FUNDS_STATUS_CODE DBMS_SQL.VARCHAR2_TABLE;
1248 ln_MERGE_CODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1249 ln_MERGE_PARTY_ID DBMS_SQL.NUMBER_TABLE;
1250 ln_MERGE_PARTY_SITE_ID DBMS_SQL.NUMBER_TABLE;
1251 ln_accounting_date DBMS_SQL.DATE_TABLE;
1252 ln_ledger_id DBMS_SQL.NUMBER_TABLE;
1253
1254 ev_EVENT_ID DBMS_SQL.NUMBER_TABLE;
1255 ev_APPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1256 ev_EVENT_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1257 ev_EVENT_DATE DBMS_SQL.DATE_TABLE;
1258 ev_ENTITY_ID DBMS_SQL.NUMBER_TABLE;
1259 ev_EVENT_STATUS_CODE DBMS_SQL.VARCHAR2_TABLE;
1260 ev_PROCESS_STATUS_CODE DBMS_SQL.VARCHAR2_TABLE;
1261 ev_REFERENCE_NUM_1 DBMS_SQL.NUMBER_TABLE;
1262 ev_EVENT_NUMBER DBMS_SQL.NUMBER_TABLE;
1263 ctlgd_CUST_TRX_LINE_GL_DIST_ID DBMS_SQL.NUMBER_TABLE;
1264 ctlgd_CUSTOMER_TRX_LINE_ID DBMS_SQL.NUMBER_TABLE;
1265 ctlgd_CODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1266 ctlgd_SET_OF_BOOKS_ID DBMS_SQL.NUMBER_TABLE;
1267 ctlgd_AMOUNT DBMS_SQL.NUMBER_TABLE;
1268 ctlgd_ACCTD_AMOUNT DBMS_SQL.NUMBER_TABLE;
1269 ctlgd_GL_DATE DBMS_SQL.DATE_TABLE;
1270 ctlgd_GL_POSTED_DATE DBMS_SQL.DATE_TABLE;
1271 ctlgd_ACCOUNT_CLASS DBMS_SQL.VARCHAR2_TABLE;
1272 ctlgd_posting_control_id DBMS_SQL.NUMBER_TABLE;
1273 pd_CUST_TRX_LINE_GL_DIST_ID DBMS_SQL.NUMBER_TABLE;
1274 pd_MF_RECEIVABLES_CCID DBMS_SQL.NUMBER_TABLE;
1275 pd_POSTING_CONTROL_ID DBMS_SQL.NUMBER_TABLE;
1276 cnt_by_hdr DBMS_SQL.NUMBER_TABLE;
1277 pdf_cust_trx_line_gl_dist_id DBMS_SQL.NUMBER_TABLE;
1278
1279 CURSOR c(l_start_rowid ROWID, l_end_rowid ROWID) IS
1280 SELECT /*+ leading(pd,ctlgd,gud) rowid(pd) use_nl(ctlgd,ct,ent,ev,hdr,ln,lnk) use_hash(gud) swap_join_inputs(gud)
1281 INDEX(ent xla_transaction_entities_N1)
1282 INDEX(ev XLA_EVENTS_U2)
1283 INDEX(hdr XLA_AE_HEADERS_N2)
1284 INDEX (ln, XLA_AE_LINES_U1)
1285 INDEX (lnk, XLA_DISTRIBUTION_LINKS_N1) */
1286 ln.AE_HEADER_ID
1287 , ln.AE_LINE_NUM
1288 , ln.APPLICATION_ID
1289 , ln.CODE_COMBINATION_ID
1290 , ln.GL_TRANSFER_MODE_CODE
1291 , ln.GL_SL_LINK_ID
1292 , ln.ACCOUNTING_CLASS_CODE
1293 , ln.PARTY_ID
1294 , ln.PARTY_SITE_ID
1295 , ln.PARTY_TYPE_CODE
1296 , ln.ENTERED_DR
1297 , ln.ENTERED_CR
1298 , ln.ACCOUNTED_DR
1299 , ln.ACCOUNTED_CR
1300 , ln.DESCRIPTION
1301 , ln.STATISTICAL_AMOUNT
1302 , ln.CURRENCY_CODE
1303 , ln.CURRENCY_CONVERSION_DATE
1304 , ln.CURRENCY_CONVERSION_RATE
1305 , ln.CURRENCY_CONVERSION_TYPE
1306 , ln.USSGL_TRANSACTION_CODE
1307 , ln.JGZZ_RECON_REF
1308 , ln.CONTROL_BALANCE_FLAG
1309 , ln.ANALYTICAL_BALANCE_FLAG
1310 , ln.GL_SL_LINK_TABLE
1311 , ln.DISPLAYED_LINE_NUMBER
1312 , ln.UPG_BATCH_ID
1313 , ln.UNROUNDED_ACCOUNTED_DR
1314 , ln.UNROUNDED_ACCOUNTED_CR
1315 , ln.GAIN_OR_LOSS_FLAG
1316 , ln.UNROUNDED_ENTERED_DR
1317 , ln.UNROUNDED_ENTERED_CR
1318 , ln.SUBSTITUTED_CCID
1319 , ln.BUSINESS_CLASS_CODE
1320 , ln.MPA_ACCRUAL_ENTRY_FLAG
1321 , ln.ENCUMBRANCE_TYPE_ID
1322 , ln.FUNDS_STATUS_CODE
1323 , ln.MERGE_CODE_COMBINATION_ID
1324 , ln.MERGE_PARTY_ID
1325 , ln.MERGE_PARTY_SITE_ID
1326 , ev.EVENT_ID
1327 , ev.APPLICATION_ID
1328 , ev.EVENT_TYPE_CODE
1329 , ev.EVENT_DATE
1330 , ev.ENTITY_ID
1331 , ev.EVENT_STATUS_CODE
1332 , ev.PROCESS_STATUS_CODE
1333 , ev.REFERENCE_NUM_1
1334 , ev.EVENT_NUMBER
1335 , ctlgd.CUST_TRX_LINE_GL_DIST_ID
1336 , ctlgd.CUSTOMER_TRX_LINE_ID
1337 , ctlgd.CODE_COMBINATION_ID
1338 , ctlgd.SET_OF_BOOKS_ID
1339 , ctlgd.AMOUNT
1340 , ctlgd.ACCTD_AMOUNT
1341 , ctlgd.GL_DATE
1342 , ctlgd.GL_POSTED_DATE
1343 , ctlgd.ACCOUNT_CLASS
1344 , ctlgd.posting_control_id
1345 , pd.CUST_TRX_LINE_GL_DIST_ID
1346 , pd.MF_RECEIVABLES_CCID
1347 , pd.POSTING_CONTROL_ID
1348 , MAX(ln.ae_line_num) OVER (PARTITION BY ln.ae_header_id) cnt_by_hdr
1349 , pd.cust_trx_line_gl_dist_id
1350 , ln.accounting_date
1351 , ln.ledger_id
1352 FROM ra_customer_trx_all ct
1353 , ra_cust_trx_line_gl_dist_all ctlgd
1354 , xla_upgrade_dates gud
1355 , xla_transaction_entities_upg ent
1356 , xla_events ev
1357 , xla_ae_headers hdr
1358 , xla_ae_lines ln
1359 , xla_distribution_links lnk
1360 , psa_mf_trx_dist_all pd
1361 WHERE pd.ROWID >= l_start_rowid
1362 AND pd.ROWID <= l_end_rowid
1363 AND ct.customer_trx_id = ctlgd.customer_trx_id
1364 AND ctlgd.cust_trx_line_gl_dist_id = pd.cust_trx_line_gl_dist_id
1365 AND NVL(ct.ax_accounted_flag,'N') = 'N'
1366 AND ctlgd.account_set_flag = 'N'
1367 AND trunc(ctlgd.gl_date) BETWEEN gud.start_date AND gud.end_date
1368 AND CTLGD.set_of_books_id = gud.ledger_id -- changed this from ct to ctlgd to enable better join to GUD
1369 AND ent.ledger_id = ct.set_of_books_id
1370 AND ent.application_id = 222
1371 AND ent.entity_code = 'TRANSACTIONS'
1372 AND ev.application_id = 222
1373 AND hdr.application_id = 222
1374 AND ln.application_id = 222
1375 AND lnk.application_id = 222
1376 AND ent.entity_id = ev.entity_id
1377 AND ent.ledger_id = ct.set_of_books_id
1378 AND ev.upg_batch_id = l_batch_id
1379 AND ev.event_id = hdr.event_id
1380 AND hdr.ledger_id = ent.ledger_id
1381 AND hdr.event_id = ev.event_id
1382 AND hdr.ae_header_id = ln.ae_header_id
1383 AND hdr.ae_header_id = lnk.ae_header_id
1384 AND ln.ae_line_num = lnk.ae_line_num
1385 AND lnk.event_id = ev.event_id
1386 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
1387 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1388 AND nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
1389 AND ev.reference_num_1 = ctlgd.posting_control_id
1390 AND NVL(TRUNC(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
1391 UNION
1392 SELECT /*+ leading(pd,ct,ctlgd,gud) rowid(pd) use_nl(ctlgd,ct,ent,ev,hdr,ln,lnk) use_hash(gud) swap_join_inputs(gud)
1393 INDEX(ent xla_transaction_entities_N1)
1394 INDEX(ev XLA_EVENTS_U2)
1395 INDEX(hdr XLA_AE_HEADERS_N2)
1396 INDEX (ln, XLA_AE_LINES_U1)
1397 INDEX (lnk, XLA_DISTRIBUTION_LINKS_N1) */
1398 ln.AE_HEADER_ID
1399 , ln.AE_LINE_NUM
1400 , ln.APPLICATION_ID
1401 , ln.CODE_COMBINATION_ID
1402 , ln.GL_TRANSFER_MODE_CODE
1403 , ln.GL_SL_LINK_ID
1404 , ln.ACCOUNTING_CLASS_CODE
1405 , ln.PARTY_ID
1406 , ln.PARTY_SITE_ID
1407 , ln.PARTY_TYPE_CODE
1408 , ln.ENTERED_DR
1409 , ln.ENTERED_CR
1410 , ln.ACCOUNTED_DR
1411 , ln.ACCOUNTED_CR
1412 , ln.DESCRIPTION
1413 , ln.STATISTICAL_AMOUNT
1414 , ln.CURRENCY_CODE
1415 , ln.CURRENCY_CONVERSION_DATE
1416 , ln.CURRENCY_CONVERSION_RATE
1417 , ln.CURRENCY_CONVERSION_TYPE
1418 , ln.USSGL_TRANSACTION_CODE
1419 , ln.JGZZ_RECON_REF
1420 , ln.CONTROL_BALANCE_FLAG
1421 , ln.ANALYTICAL_BALANCE_FLAG
1422 , ln.GL_SL_LINK_TABLE
1423 , ln.DISPLAYED_LINE_NUMBER
1424 , ln.UPG_BATCH_ID
1425 , ln.UNROUNDED_ACCOUNTED_DR
1426 , ln.UNROUNDED_ACCOUNTED_CR
1427 , ln.GAIN_OR_LOSS_FLAG
1428 , ln.UNROUNDED_ENTERED_DR
1429 , ln.UNROUNDED_ENTERED_CR
1430 , ln.SUBSTITUTED_CCID
1431 , ln.BUSINESS_CLASS_CODE
1432 , ln.MPA_ACCRUAL_ENTRY_FLAG
1433 , ln.ENCUMBRANCE_TYPE_ID
1434 , ln.FUNDS_STATUS_CODE
1435 , ln.MERGE_CODE_COMBINATION_ID
1436 , ln.MERGE_PARTY_ID
1437 , ln.MERGE_PARTY_SITE_ID
1438 , ev.EVENT_ID
1439 , ev.APPLICATION_ID
1440 , ev.EVENT_TYPE_CODE
1441 , ev.EVENT_DATE
1442 , ev.ENTITY_ID
1443 , ev.EVENT_STATUS_CODE
1444 , ev.PROCESS_STATUS_CODE
1445 , ev.REFERENCE_NUM_1
1446 , ev.EVENT_NUMBER
1447 , ctlgd.CUST_TRX_LINE_GL_DIST_ID
1448 , ctlgd.CUSTOMER_TRX_LINE_ID
1449 , ctlgd.CODE_COMBINATION_ID
1450 , ctlgd.SET_OF_BOOKS_ID
1451 , ctlgd.AMOUNT
1452 , ctlgd.ACCTD_AMOUNT
1453 , ctlgd.GL_DATE
1454 , ctlgd.GL_POSTED_DATE
1455 , ctlgd.ACCOUNT_CLASS
1456 , ctlgd.posting_control_id
1457 , ctlgd.CUST_TRX_LINE_GL_DIST_ID
1458 , NULL
1459 , ctlgd.POSTING_CONTROL_ID
1460 , 999999999 cnt_by_hdr
1461 , ctlgd.cust_trx_line_gl_dist_id
1462 , ln.accounting_date
1463 , ln.ledger_id
1464 FROM ra_customer_trx_all ct
1465 , ra_cust_trx_line_gl_dist_all ctlgd
1466 , xla_upgrade_dates gud
1467 , xla_transaction_entities_upg ent
1468 , xla_events ev
1469 , xla_ae_headers hdr
1470 , xla_ae_lines ln
1471 , xla_distribution_links lnk
1472 ,(SELECT /*+ rowid(pdist) use_nl(dist) no_merge */ dist.customer_trx_id
1473 FROM ra_cust_trx_line_gl_dist_all dist,
1474 psa_mf_trx_dist_all pdist
1475 WHERE pdist.cust_trx_line_gl_dist_id = dist.cust_trx_line_gl_dist_id
1476 AND pdist.ROWID >= l_start_rowid
1477 AND pdist.ROWID <= l_end_rowid
1478 GROUP BY dist.customer_trx_id
1479 ) pd
1480 WHERE ct.customer_trx_id = pd.customer_trx_id
1481 AND ct.customer_trx_id = ctlgd.customer_trx_id
1482 AND ctlgd.account_class = 'REC'
1483 AND ctlgd.account_set_flag = 'N'
1484 AND NVL(ct.ax_accounted_flag,'N') = 'N'
1485 AND trunc(ctlgd.gl_date) BETWEEN gud.start_date AND gud.end_date
1486 AND CTLGD.set_of_books_id = gud.ledger_id -- changed this from ct to ctlgd to enable better join to GUD
1487 AND ent.ledger_id = ct.set_of_books_id
1488 AND ent.application_id = 222
1489 AND ent.entity_code = 'TRANSACTIONS'
1490 AND ev.upg_batch_id = l_batch_id
1491 AND ev.application_id = 222
1492 AND hdr.application_id = 222
1493 AND ln.application_id = 222
1494 AND lnk.application_id = 222
1495 AND ent.entity_id = ev.entity_id
1496 AND ent.ledger_id = ct.set_of_books_id
1497 AND ev.event_id = hdr.event_id
1498 AND hdr.ledger_id = ent.ledger_id
1499 AND hdr.event_id = ev.event_id
1500 AND hdr.ae_header_id = ln.ae_header_id
1501 AND hdr.ae_header_id = lnk.ae_header_id
1502 AND ln.ae_line_num = lnk.ae_line_num
1503 AND lnk.event_id = ev.event_id
1504 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
1505 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1506 AND nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
1507 AND ev.reference_num_1 = ctlgd.posting_control_id
1508 AND NVL(TRUNC(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date;
1509
1510
1511
1512 --xla_ae_lines
1513
1514 LAE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1515 LAE_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1516 LAPPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1517 LCODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1518 LGL_TRANSFER_MODE_CODE DBMS_SQL.VARCHAR2_TABLE;
1519 LGL_SL_LINK_ID DBMS_SQL.VARCHAR2_TABLE;
1520 LACCOUNTING_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1521 LPARTY_ID DBMS_SQL.NUMBER_TABLE;
1522 LPARTY_SITE_ID DBMS_SQL.NUMBER_TABLE;
1523 LPARTY_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1524 LENTERED_DR DBMS_SQL.NUMBER_TABLE;
1525 LENTERED_CR DBMS_SQL.NUMBER_TABLE;
1526 LACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1527 LACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1528 LDESCRIPTION DBMS_SQL.VARCHAR2_TABLE;
1529 LSTATISTICAL_AMOUNT DBMS_SQL.NUMBER_TABLE;
1530 LCURRENCY_CODE DBMS_SQL.VARCHAR2_TABLE;
1531 LCURRENCY_CONVERSION_DATE DBMS_SQL.DATE_TABLE;
1532 LCURRENCY_CONVERSION_RATE DBMS_SQL.NUMBER_TABLE;
1533 LCURRENCY_CONVERSION_TYPE DBMS_SQL.VARCHAR2_TABLE;
1534 LUSSGL_TRANSACTION_CODE DBMS_SQL.VARCHAR2_TABLE;
1535 LJGZZ_RECON_REF DBMS_SQL.VARCHAR2_TABLE;
1536 LCONTROL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1537 LANALYTICAL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1538 LGL_SL_LINK_TABLE DBMS_SQL.VARCHAR2_TABLE;
1539 LDISPLAYED_LINE_NUMBER DBMS_SQL.NUMBER_TABLE;
1540 LUPG_BATCH_ID DBMS_SQL.NUMBER_TABLE;
1541 LUNROUNDED_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1542 LUNROUNDED_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1543 LGAIN_OR_LOSS_FLAG DBMS_SQL.VARCHAR2_TABLE;
1544 LUNROUNDED_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1545 LUNROUNDED_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1546 LBUSINESS_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1547 laccounting_date DBMS_SQL.DATE_TABLE;
1548 lledger_id DBMS_SQL.NUMBER_TABLE;
1549
1550
1551 --xla_distribution_links
1552
1553 DAPPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1554 DEVENT_ID DBMS_SQL.NUMBER_TABLE;
1555 DAE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1556 DAE_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1557 DSOURCE_DISTRIBUTION_TYPE DBMS_SQL.VARCHAR2_TABLE;
1558 DSOURCE_DISTRIBUTION_ID_NUM_1 DBMS_SQL.NUMBER_TABLE;
1559 DTAX_LINE_REF_ID DBMS_SQL.NUMBER_TABLE;
1560 DREF_AE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1561 DREF_TEMP_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1562 DACCOUNTING_LINE_CODE DBMS_SQL.VARCHAR2_TABLE;
1563 DACCOUNTING_LINE_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1564 DMERGE_DUPLICATE_CODE DBMS_SQL.VARCHAR2_TABLE;
1565 DTEMP_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1566 DREF_EVENT_ID DBMS_SQL.NUMBER_TABLE;
1567 DEVENT_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1568 DEVENT_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1569 DUPG_BATCH_ID DBMS_SQL.NUMBER_TABLE;
1570 DUNROUNDED_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1571 DUNROUNDED_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1572 DUNROUNDED_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1573 DUNROUNDED_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1574
1575 empty_varchar2_list DBMS_SQL.VARCHAR2_TABLE;
1576 empty_number_list DBMS_SQL.NUMBER_TABLE;
1577 empty_date_list DBMS_SQL.DATE_TABLE;
1578
1579
1580 l_sys_date DATE := SYSDATE;
1581
1582 l_start_rowid rowid;
1583 l_end_rowid rowid;
1584 l_any_rows_to_process BOOLEAN;
1585 l_rows_processed NUMBER := 0;
1586 l_last_fetch BOOLEAN := FALSE;
1587 BEGIN
1588
1589 /* ------ Initialize the rowid ranges ------ */
1590 ad_parallel_updates_pkg.initialize_rowid_range(
1591 ad_parallel_updates_pkg.ROWID_RANGE,
1592 l_table_owner,
1593 l_table_name,
1594 l_script_name,
1595 l_worker_id,
1596 l_num_workers,
1597 l_batch_size, 0);
1598
1599 /* ------ Get rowid ranges ------ */
1600 ad_parallel_updates_pkg.get_rowid_range(
1601 l_start_rowid,
1602 l_end_rowid,
1603 l_any_rows_to_process,
1604 l_batch_size,
1605 TRUE);
1606
1607
1608 WHILE ( l_any_rows_to_process = TRUE )
1609 LOOP
1610
1611 l_rows_processed := 0;
1612
1613 OPEN c(l_start_rowid, l_end_rowid);
1614 LOOP
1615 FETCH c BULK COLLECT INTO
1616 ln_AE_HEADER_ID
1617 ,ln_AE_LINE_NUM
1618 ,ln_APPLICATION_ID
1619 ,ln_CODE_COMBINATION_ID
1620 ,ln_GL_TRANSFER_MODE_CODE
1621 ,ln_GL_SL_LINK_ID
1622 ,ln_ACCOUNTING_CLASS_CODE
1623 ,ln_PARTY_ID
1624 ,ln_PARTY_SITE_ID
1625 ,ln_PARTY_TYPE_CODE
1626 ,ln_ENTERED_DR
1627 ,ln_ENTERED_CR
1628 ,ln_ACCOUNTED_DR
1629 ,ln_ACCOUNTED_CR
1630 ,ln_DESCRIPTION
1631 ,ln_STATISTICAL_AMOUNT
1632 ,ln_CURRENCY_CODE
1633 ,ln_CURRENCY_CONVERSION_DATE
1634 ,ln_CURRENCY_CONVERSION_RATE
1635 ,ln_CURRENCY_CONVERSION_TYPE
1636 ,ln_USSGL_TRANSACTION_CODE
1637 ,ln_JGZZ_RECON_REF
1638 ,ln_CONTROL_BALANCE_FLAG
1639 ,ln_ANALYTICAL_BALANCE_FLAG
1640 ,ln_GL_SL_LINK_TABLE
1641 ,ln_DISPLAYED_LINE_NUMBER
1642 ,ln_UPG_BATCH_ID
1643 ,ln_UNROUNDED_ACCOUNTED_DR
1644 ,ln_UNROUNDED_ACCOUNTED_CR
1645 ,ln_GAIN_OR_LOSS_FLAG
1646 ,ln_UNROUNDED_ENTERED_DR
1647 ,ln_UNROUNDED_ENTERED_CR
1648 ,ln_SUBSTITUTED_CCID
1649 ,ln_BUSINESS_CLASS_CODE
1650 ,ln_MPA_ACCRUAL_ENTRY_FLAG
1651 ,ln_ENCUMBRANCE_TYPE_ID
1652 ,ln_FUNDS_STATUS_CODE
1653 ,ln_MERGE_CODE_COMBINATION_ID
1654 ,ln_MERGE_PARTY_ID
1655 ,ln_MERGE_PARTY_SITE_ID
1656 ,ev_EVENT_ID
1657 ,ev_APPLICATION_ID
1658 ,ev_EVENT_TYPE_CODE
1659 ,ev_EVENT_DATE
1660 ,ev_ENTITY_ID
1661 ,ev_EVENT_STATUS_CODE
1662 ,ev_PROCESS_STATUS_CODE
1663 ,ev_REFERENCE_NUM_1
1664 ,ev_EVENT_NUMBER
1665 ,ctlgd_CUST_TRX_LINE_GL_DIST_ID
1666 ,ctlgd_CUSTOMER_TRX_LINE_ID
1667 ,ctlgd_CODE_COMBINATION_ID
1668 ,ctlgd_SET_OF_BOOKS_ID
1669 ,ctlgd_AMOUNT
1670 ,ctlgd_ACCTD_AMOUNT
1671 ,ctlgd_GL_DATE
1672 ,ctlgd_GL_POSTED_DATE
1673 ,ctlgd_ACCOUNT_CLASS
1674 ,ctlgd_posting_control_id
1675 ,pd_CUST_TRX_LINE_GL_DIST_ID
1676 ,pd_MF_RECEIVABLES_CCID
1677 ,pd_POSTING_CONTROL_ID
1678 ,cnt_by_hdr
1679 ,pdf_cust_trx_line_gl_dist_id
1680 ,ln_accounting_date
1681 ,ln_ledger_id
1682 LIMIT 1000;
1683
1684 IF c%NOTFOUND THEN
1685 l_last_fetch := TRUE;
1686 END IF;
1687 IF (ctlgd_ACCOUNT_CLASS.COUNT = 0) AND (l_last_fetch) THEN
1688 EXIT;
1689 END IF;
1690
1691 LAE_HEADER_ID := empty_number_list;
1692 LAE_LINE_NUM := empty_number_list;
1693 LAPPLICATION_ID := empty_number_list;
1694 LCODE_COMBINATION_ID := empty_number_list;
1695 LGL_TRANSFER_MODE_CODE := empty_varchar2_list;
1696 LGL_SL_LINK_ID := empty_varchar2_list;
1697 LACCOUNTING_CLASS_CODE := empty_varchar2_list;
1698 LPARTY_ID := empty_number_list;
1699 LPARTY_SITE_ID := empty_number_list;
1700 LPARTY_TYPE_CODE := empty_varchar2_list;
1701 LENTERED_DR := empty_number_list;
1702 LENTERED_CR := empty_number_list;
1703 LACCOUNTED_DR := empty_number_list;
1704 LACCOUNTED_CR := empty_number_list;
1705 LDESCRIPTION := empty_varchar2_list;
1706 LSTATISTICAL_AMOUNT := empty_number_list;
1707 LCURRENCY_CODE := empty_varchar2_list;
1708 LCURRENCY_CONVERSION_DATE := empty_date_list;
1709 LCURRENCY_CONVERSION_RATE := empty_number_list;
1710 LCURRENCY_CONVERSION_TYPE := empty_varchar2_list;
1711 LUSSGL_TRANSACTION_CODE := empty_varchar2_list;
1712 LJGZZ_RECON_REF := empty_varchar2_list;
1713 LCONTROL_BALANCE_FLAG := empty_varchar2_list;
1714 LANALYTICAL_BALANCE_FLAG := empty_varchar2_list;
1715 LGL_SL_LINK_TABLE := empty_varchar2_list;
1716 LDISPLAYED_LINE_NUMBER := empty_number_list;
1717 LUPG_BATCH_ID := empty_number_list;
1718 LUNROUNDED_ACCOUNTED_DR := empty_number_list;
1719 LUNROUNDED_ACCOUNTED_CR := empty_number_list;
1720 LGAIN_OR_LOSS_FLAG := empty_varchar2_list;
1721 LUNROUNDED_ENTERED_DR := empty_number_list;
1722 LUNROUNDED_ENTERED_CR := empty_number_list;
1723 LBUSINESS_CLASS_CODE := empty_varchar2_list;
1724 laccounting_date := empty_date_list;
1725 lledger_id := empty_number_list;
1726
1727
1728 --xla_distribution_links
1729
1730 DAPPLICATION_ID := empty_number_list;
1731 DEVENT_ID := empty_number_list;
1732 DAE_HEADER_ID := empty_number_list;
1733 DAE_LINE_NUM := empty_number_list;
1734 DSOURCE_DISTRIBUTION_TYPE := empty_varchar2_list;
1735 DSOURCE_DISTRIBUTION_ID_NUM_1 := empty_number_list;
1736 DTAX_LINE_REF_ID := empty_number_list;
1737 DREF_AE_HEADER_ID := empty_number_list;
1738 DREF_TEMP_LINE_NUM := empty_number_list;
1739 DACCOUNTING_LINE_CODE := empty_varchar2_list;
1740 DACCOUNTING_LINE_TYPE_CODE := empty_varchar2_list;
1741 DMERGE_DUPLICATE_CODE := empty_varchar2_list;
1742 DTEMP_LINE_NUM := empty_number_list;
1743 DREF_EVENT_ID := empty_number_list;
1744 DEVENT_CLASS_CODE := empty_varchar2_list;
1745 DEVENT_TYPE_CODE := empty_varchar2_list;
1746 DUPG_BATCH_ID := empty_number_list;
1747 DUNROUNDED_ENTERED_DR := empty_number_list;
1748 DUNROUNDED_ENTERED_CR := empty_number_list;
1749 DUNROUNDED_ACCOUNTED_CR := empty_number_list;
1750 DUNROUNDED_ACCOUNTED_DR := empty_number_list;
1751
1752
1753 -- FOR PSA upgrade, only ae lines are necessary
1754 -- No distribution links
1755 -- no denormalization event
1756 FOR i IN ln_AE_HEADER_ID.FIRST .. ln_AE_HEADER_ID.LAST LOOP
1757
1758 IF ctlgd_ACCOUNT_CLASS(i) = 'REC' THEN
1759
1760 -- Construct the reversal
1761 -- ae line
1762
1763 LAE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1764 LAE_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i);
1765 LAPPLICATION_ID(i) := 222;
1766 LCODE_COMBINATION_ID(i) := NVL(ln_CODE_COMBINATION_ID(i),-1);
1767 LGL_TRANSFER_MODE_CODE(i) := ln_GL_TRANSFER_MODE_CODE(i);
1768 LGL_SL_LINK_ID(i) := ln_GL_SL_LINK_ID(i);
1769 LACCOUNTING_CLASS_CODE(i) := ln_ACCOUNTING_CLASS_CODE(i);
1770 LPARTY_ID(i) := ln_PARTY_ID(i);
1771 LPARTY_SITE_ID(i) := ln_PARTY_SITE_ID(i);
1772 LPARTY_TYPE_CODE(i) := ln_PARTY_TYPE_CODE(i);
1773 LENTERED_DR(i) := ln_ENTERED_CR(i);
1774 LENTERED_CR(i) := ln_ENTERED_DR(i);
1775 LACCOUNTED_DR(i) := ln_ACCOUNTED_CR(i);
1776 LACCOUNTED_CR(i) := ln_ACCOUNTED_DR(i);
1777 LDESCRIPTION(i) := 'MFAR UPGRADE REVERSE AR RECEIVABLES';
1778 LSTATISTICAL_AMOUNT(i) := ln_STATISTICAL_AMOUNT(i);
1779 LCURRENCY_CODE(i) := ln_CURRENCY_CODE(i);
1780 LCURRENCY_CONVERSION_DATE(i) := ln_CURRENCY_CONVERSION_DATE(i);
1781 LCURRENCY_CONVERSION_RATE(i) := ln_CURRENCY_CONVERSION_RATE(i);
1782 LCURRENCY_CONVERSION_TYPE(i) := ln_CURRENCY_CONVERSION_TYPE(i);
1783 LUSSGL_TRANSACTION_CODE(i) := ln_USSGL_TRANSACTION_CODE(i);
1784 LJGZZ_RECON_REF(i) := ln_JGZZ_RECON_REF(i);
1785 LCONTROL_BALANCE_FLAG(i) := ln_CONTROL_BALANCE_FLAG(i);
1786 LANALYTICAL_BALANCE_FLAG(i) := ln_ANALYTICAL_BALANCE_FLAG(i);
1787 LGL_SL_LINK_TABLE(i) := ln_GL_SL_LINK_TABLE(i);
1788 LDISPLAYED_LINE_NUMBER(i) := ln_DISPLAYED_LINE_NUMBER(i);
1789 LUPG_BATCH_ID(i) := ln_UPG_BATCH_ID(i);
1790 LUNROUNDED_ACCOUNTED_DR(i) := ln_UNROUNDED_ACCOUNTED_CR(i);
1791 LUNROUNDED_ACCOUNTED_CR(i) := ln_UNROUNDED_ACCOUNTED_DR(i);
1792 LGAIN_OR_LOSS_FLAG(i) := ln_GAIN_OR_LOSS_FLAG(i);
1793 LUNROUNDED_ENTERED_DR(i) := ln_UNROUNDED_ENTERED_CR(i);
1794 LUNROUNDED_ENTERED_CR(i) := ln_UNROUNDED_ENTERED_DR(i);
1795 LBUSINESS_CLASS_CODE(i) := ln_BUSINESS_CLASS_CODE(i);
1796 laccounting_date(i) := ln_accounting_date(i);
1797 lledger_id(i) := ln_ledger_id(i);
1798
1799
1800
1801 ELSIF pdf_cust_trx_line_gl_dist_id(i) IS NOT NULL THEN
1802
1803 -- Construct the MFAR REC
1804 -- ae line
1805
1806 LAE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1807 LAE_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i) + 1; /*bug 5837507*/
1808 LAPPLICATION_ID(i) := 222;
1809 LCODE_COMBINATION_ID(i) := NVL(pd_MF_RECEIVABLES_CCID(i),-1);
1810 LGL_TRANSFER_MODE_CODE(i) := ln_GL_TRANSFER_MODE_CODE(i);
1811 LGL_SL_LINK_ID(i) := ln_GL_SL_LINK_ID(i);
1812 LACCOUNTING_CLASS_CODE(i) := 'RECEIVABLE';
1813 LPARTY_ID(i) := ln_PARTY_ID(i);
1814 LPARTY_SITE_ID(i) := ln_PARTY_SITE_ID(i);
1815 LPARTY_TYPE_CODE(i) := ln_PARTY_TYPE_CODE(i);
1816 IF ctlgd_AMOUNT(i) >= 0 THEN
1817 LENTERED_DR(i) := ctlgd_AMOUNT(i);
1818 LENTERED_CR(i) := NULL;
1819 ELSE
1820 LENTERED_DR(i) := NULL;
1821 LENTERED_CR(i) := ctlgd_AMOUNT(i);
1822 END IF;
1823 IF ctlgd_ACCTD_AMOUNT(i) >= 0 THEN
1824 LACCOUNTED_DR(i) := ctlgd_ACCTD_AMOUNT(i);
1825 LACCOUNTED_CR(i) := NULL;
1826 ELSE
1827 LACCOUNTED_DR(i) := NULL;
1828 LACCOUNTED_CR(i) := ctlgd_ACCTD_AMOUNT(i);
1829 END IF;
1830 LDESCRIPTION(i) := 'MFAR UPGRADE CREATE MFAR RECEIVABLES';
1831 LSTATISTICAL_AMOUNT(i) := ln_STATISTICAL_AMOUNT(i);
1832 LCURRENCY_CODE(i) := ln_CURRENCY_CODE(i);
1833 LCURRENCY_CONVERSION_DATE(i) := ln_CURRENCY_CONVERSION_DATE(i);
1834 LCURRENCY_CONVERSION_RATE(i) := ln_CURRENCY_CONVERSION_RATE(i);
1835 LCURRENCY_CONVERSION_TYPE(i) := ln_CURRENCY_CONVERSION_TYPE(i);
1836 LUSSGL_TRANSACTION_CODE(i) := ln_USSGL_TRANSACTION_CODE(i);
1837 LJGZZ_RECON_REF(i) := ln_JGZZ_RECON_REF(i);
1838 LCONTROL_BALANCE_FLAG(i) := ln_CONTROL_BALANCE_FLAG(i);
1839 LANALYTICAL_BALANCE_FLAG(i) := ln_ANALYTICAL_BALANCE_FLAG(i);
1840 LGL_SL_LINK_TABLE(i) := ln_GL_SL_LINK_TABLE(i);
1841 LDISPLAYED_LINE_NUMBER(i) := ln_DISPLAYED_LINE_NUMBER(i);
1842 LUPG_BATCH_ID(i) := ln_UPG_BATCH_ID(i);
1843 IF ctlgd_ACCTD_AMOUNT(i) >= 0 THEN
1844 LUNROUNDED_ACCOUNTED_DR(i) := ctlgd_ACCTD_AMOUNT(i);
1845 LUNROUNDED_ACCOUNTED_CR(i) := NULL;
1846 ELSE
1847 LUNROUNDED_ACCOUNTED_DR(i) := NULL;
1848 LUNROUNDED_ACCOUNTED_CR(i) := ctlgd_ACCTD_AMOUNT(i);
1849 END IF;
1850 LGAIN_OR_LOSS_FLAG(i) := ln_GAIN_OR_LOSS_FLAG(i);
1851 IF ctlgd_AMOUNT(i) >= 0 THEN
1852 LUNROUNDED_ENTERED_DR(i) := ctlgd_AMOUNT(i);
1853 LUNROUNDED_ENTERED_CR(i) := NULL;
1854 ELSE
1855 LUNROUNDED_ENTERED_DR(i) := NULL;
1856 LUNROUNDED_ENTERED_CR(i) := ctlgd_AMOUNT(i);
1857 END IF;
1858 LBUSINESS_CLASS_CODE(i) := ln_BUSINESS_CLASS_CODE(i);
1859 laccounting_date(i) := ln_accounting_date(i);
1860 lledger_id(i) := ln_ledger_id(i);
1861
1862 END IF;
1863
1864 END LOOP;
1865
1866
1867 FORALL i IN LAE_HEADER_ID.FIRST .. LAE_HEADER_ID.LAST
1868 INSERT INTO xla_ae_lines
1869 ( AE_HEADER_ID ,
1870 AE_LINE_NUM ,
1871 APPLICATION_ID ,
1872 CODE_COMBINATION_ID ,
1873 GL_TRANSFER_MODE_CODE ,
1874 GL_SL_LINK_ID ,
1875 ACCOUNTING_CLASS_CODE ,
1876 PARTY_ID ,
1877 PARTY_SITE_ID ,
1878 PARTY_TYPE_CODE ,
1879 ENTERED_DR ,
1880 ENTERED_CR ,
1881 ACCOUNTED_DR ,
1882 ACCOUNTED_CR ,
1883 DESCRIPTION ,
1884 STATISTICAL_AMOUNT ,
1885 CURRENCY_CODE ,
1886 CURRENCY_CONVERSION_DATE ,
1887 CURRENCY_CONVERSION_RATE ,
1888 CURRENCY_CONVERSION_TYPE ,
1889 USSGL_TRANSACTION_CODE ,
1890 JGZZ_RECON_REF ,
1891 CONTROL_BALANCE_FLAG ,
1892 ANALYTICAL_BALANCE_FLAG ,
1893 GL_SL_LINK_TABLE ,
1894 DISPLAYED_LINE_NUMBER ,
1895 UPG_BATCH_ID ,
1896 UNROUNDED_ACCOUNTED_DR ,
1897 UNROUNDED_ACCOUNTED_CR ,
1898 GAIN_OR_LOSS_FLAG ,
1899 UNROUNDED_ENTERED_DR ,
1900 UNROUNDED_ENTERED_CR ,
1901 BUSINESS_CLASS_CODE ,
1902 CREATION_DATE ,
1903 CREATED_BY ,
1904 LAST_UPDATE_DATE ,
1905 LAST_UPDATED_BY ,
1906 accounting_date ,
1907 ledger_id ) VALUES
1908 (LAE_HEADER_ID(i),
1909 LAE_LINE_NUM(i),
1910 LAPPLICATION_ID(i),
1911 LCODE_COMBINATION_ID(i),
1912 LGL_TRANSFER_MODE_CODE(i),
1913 LGL_SL_LINK_ID(i),
1914 LACCOUNTING_CLASS_CODE(i),
1915 LPARTY_ID(i),
1916 LPARTY_SITE_ID(i),
1917 LPARTY_TYPE_CODE(i),
1918 LENTERED_DR(i),
1919 LENTERED_CR(i),
1920 LACCOUNTED_DR(i),
1921 LACCOUNTED_CR(i),
1922 LDESCRIPTION(i),
1923 LSTATISTICAL_AMOUNT(i),
1924 LCURRENCY_CODE(i),
1925 LCURRENCY_CONVERSION_DATE(i),
1926 LCURRENCY_CONVERSION_RATE(i),
1927 LCURRENCY_CONVERSION_TYPE(i),
1928 LUSSGL_TRANSACTION_CODE(i),
1929 LJGZZ_RECON_REF(i),
1930 LCONTROL_BALANCE_FLAG(i),
1931 LANALYTICAL_BALANCE_FLAG(i),
1932 LGL_SL_LINK_TABLE(i),
1933 LDISPLAYED_LINE_NUMBER(i),
1934 LUPG_BATCH_ID(i),
1935 LUNROUNDED_ACCOUNTED_DR(i),
1936 LUNROUNDED_ACCOUNTED_CR(i),
1937 LGAIN_OR_LOSS_FLAG(i),
1938 LUNROUNDED_ENTERED_DR(i),
1939 LUNROUNDED_ENTERED_CR(i),
1940 LBUSINESS_CLASS_CODE(i),
1941 l_sys_date,
1942 0,
1943 l_sys_date,
1944 0 ,
1945 laccounting_date(i),
1946 lledger_id(i));
1947
1948 IF l_last_fetch = TRUE THEN
1949 EXIT;
1950 END IF;
1951
1952
1953
1954 END LOOP;
1955 CLOSE c;
1956
1957 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1958
1959 ad_parallel_updates_pkg.processed_rowid_range(
1960 l_rows_processed,
1961 l_end_rowid);
1962
1963 commit;
1964
1965 ad_parallel_updates_pkg.get_rowid_range(
1966 l_start_rowid,
1967 l_end_rowid,
1968 l_any_rows_to_process,
1969 l_batch_size,
1970 FALSE);
1971
1972 l_rows_processed := 0 ;
1973
1974 END LOOP ; /* end of WHILE loop */
1975
1976 commit;
1977
1978 EXCEPTION
1979 WHEN NO_DATA_FOUND THEN NULL;
1980 WHEN OTHERS THEN
1981 RAISE;
1982 END UPGRADE_TRANSACTIONS;
1983
1984
1985
1986 END;