[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.15 2009/06/10 13:05:11 tthangav 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 PROCEDURE upgrade_misc_cash_dist(
1003 l_table_owner IN VARCHAR2,
1004 l_table_name IN VARCHAR2,
1005 l_script_name IN VARCHAR2,
1006 l_worker_id IN VARCHAR2,
1007 l_num_workers IN VARCHAR2,
1008 l_batch_size IN VARCHAR2,
1009 l_batch_id IN NUMBER,
1010 l_action_flag IN VARCHAR2)
1011 IS
1012 l_start_rowid rowid;
1013 l_end_rowid rowid;
1014 l_any_rows_to_process boolean;
1015 l_rows_processed number := 0;
1016 BEGIN
1017
1018 /* ------ Initialize the rowid ranges ------ */
1019 ad_parallel_updates_pkg.initialize_rowid_range(
1020 ad_parallel_updates_pkg.ROWID_RANGE,
1021 l_table_owner,
1022 l_table_name,
1023 l_script_name,
1024 l_worker_id,
1025 l_num_workers,
1026 l_batch_size, 0);
1027
1028 /* ------ Get rowid ranges ------ */
1029 ad_parallel_updates_pkg.get_rowid_range(
1030 l_start_rowid,
1031 l_end_rowid,
1032 l_any_rows_to_process,
1033 l_batch_size,
1034 TRUE);
1035
1036
1037 WHILE ( l_any_rows_to_process = TRUE )
1038 LOOP
1039
1040
1041
1042
1043 -- UPgrade Multi Fund Misc Cash distributions MIgrated to AR
1044 -- Only the posted MCD need to be upgraded - non posted AAD will do it
1045
1046 /*
1047 The logic used in the below code is:
1048
1049 -1 Dr is MFAR entry Cr is Reversal Entry
1050 1 Dr is Reversal entry Cr is MFAR entry
1051
1052 The below code derives the sign which will be applicable for above pattern for all the test cases:
1053 sign((cr-dr) * mcd.amount * cr.amount * -1(if its reversed) )
1054
1055 Test Cases:
1056 1. +ve Receipt with mixed distributions and multiple status
1057 2. -ve Receipt with mixed distributions and mutiple status
1058 3. +ve Receipt with mixed distributions and Reversed
1059 4. -ve Receipt with mixed distributions and Reversed
1060 */
1061
1062 INSERT INTO ar_distributions_all (
1063 LINE_ID
1064 , SOURCE_ID
1065 , SOURCE_TABLE
1066 , SOURCE_TYPE
1067 , CODE_COMBINATION_ID
1068 , AMOUNT_DR
1069 , AMOUNT_CR
1070 , ACCTD_AMOUNT_DR
1071 , ACCTD_AMOUNT_CR
1072 , CREATION_DATE
1073 , CREATED_BY
1074 , LAST_UPDATED_BY
1075 , LAST_UPDATE_DATE
1076 , LAST_UPDATE_LOGIN
1077 , ORG_ID
1078 , SOURCE_TABLE_SECONDARY
1079 , SOURCE_ID_SECONDARY
1080 , CURRENCY_CODE
1081 , CURRENCY_CONVERSION_RATE
1082 , CURRENCY_CONVERSION_TYPE
1083 , CURRENCY_CONVERSION_DATE
1084 , TAXABLE_ENTERED_DR
1085 , TAXABLE_ENTERED_CR
1086 , TAXABLE_ACCOUNTED_DR
1087 , TAXABLE_ACCOUNTED_CR
1088 , TAX_LINK_ID
1089 , THIRD_PARTY_ID
1090 , THIRD_PARTY_SUB_ID
1091 , REVERSED_SOURCE_ID
1092 , TAX_CODE_ID
1093 , LOCATION_SEGMENT_ID
1094 , SOURCE_TYPE_SECONDARY
1095 , TAX_GROUP_CODE_ID
1096 , REF_CUSTOMER_TRX_LINE_ID
1097 , REF_CUST_TRX_LINE_GL_DIST_ID
1098 , REF_ACCOUNT_CLASS
1099 , ACTIVITY_BUCKET
1100 , REF_LINE_ID
1101 , FROM_AMOUNT_DR
1102 , FROM_AMOUNT_CR
1103 , FROM_ACCTD_AMOUNT_DR
1104 , FROM_ACCTD_AMOUNT_CR
1105 , REF_MF_DIST_FLAG
1106 , REF_DIST_CCID)
1107 SELECT /*+ ordered rowid(mcd) use_nl(psamcd,cr) INDEX(psamcd psa_mf_misc_dist_u1) */
1108 ar_distributions_s.nextval -- LINE_ID
1109 , crhard.source_id -- SOURCE_ID
1110 , 'CRH' -- SOURCE_TABLE
1111 , crhard.source_type -- SOURCE_TYPE
1112 , CASE WHEN doub.side = 'C' THEN
1113 DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
1114 1, DECODE( crh.status,
1115 'REVERSED', psamcd.cash_ccid,
1116 DECODE(crhard.source_type,
1117 'CONFIRMATION', 'CONFIRMED',
1118 'REMITTANCE', 'REMITTED',
1119 'CASH','CLEARED'),
1120 psamcd.cash_ccid,
1121 psamcd.reversal_ccid),
1122 crhard.code_combination_id)
1123 ELSE
1124 DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
1125 -1, DECODE( crh.status,
1126 'REVERSED', psamcd.cash_ccid,
1127 DECODE(crhard.source_type,
1128 'CONFIRMATION', 'CONFIRMED',
1129 'REMITTANCE', 'REMITTED',
1130 'CASH','CLEARED'),
1131 psamcd.cash_ccid,
1132 psamcd.reversal_ccid),
1133 crhard.code_combination_id)
1134 END -- CODE_COMBINATION_ID
1135 , DECODE(doub.side,'D', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_DR
1136 , DECODE(doub.side,'C', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_CR
1137 , DECODE(doub.side,'D', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_DR
1138 , DECODE(doub.side,'C', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_CR
1139 , SYSDATE -- CREATION_DATE
1140 , 0 -- CREATED_BY
1141 , 0 -- LAST_UPDATED_BY
1142 , SYSDATE -- LAST_UPDATE_DATE
1143 , 0 -- LAST_UPDATE_LOGIN
1144 , mcd.org_id -- ORG_ID
1145 , CASE
1146 WHEN doub.side = 'C' THEN
1147 DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
1148 1, 'UPMFMCMIAR',
1149 'UPMFMCREAR')
1150 ELSE
1151 DECODE(sign ((nvl(amount_cr,0)-nvl(amount_dr,0))*mcd.amount*cr.amount*decode(crh.status, 'REVERSED' , -1, 1)),
1152 -1, 'UPMFMCMIAR',
1153 'UPMFMCREAR')
1154
1155 END -- SOURCE_TABLE_SECONDARY
1156 , NULL -- SOURCE_ID_SECONDARY
1157 , cr.currency_code -- CURRENCY_CODE
1158 , NULL -- CURRENCY_CONVERSION_RATE
1159 , NULL -- CURRENCY_CONVERSION_TYPE
1160 , NULL -- CURRENCY_CONVERSION_DATE
1161 , NULL -- TAXABLE_ENTERED_DR
1162 , NULL -- TAXABLE_ENTERED_CR
1163 , NULL -- TAXABLE_ACCOUNTED_DR
1164 , NULL -- TAXABLE_ACCOUNTED_CR
1165 , NULL -- TAX_LINK_ID
1166 , NULL -- THIRD_PARTY_ID
1167 , NULL -- THIRD_PARTY_SUB_ID
1168 , NULL -- REVERSED_SOURCE_ID
1169 , NULL -- TAX_CODE_ID
1170 , NULL -- LOCATION_SEGMENT_ID
1171 , 'PSA_MF_MISC_DIST_ALL' -- SOURCE_TYPE_SECONDARY
1172 , NULL -- TAX_GROUP_CODE_ID
1173 , NULL -- REF_CUSTOMER_TRX_LINE_ID
1174 , NULL -- REF_CUST_TRX_LINE_GL_DIST_ID
1175 , NULL -- REF_ACCOUNT_CLASS
1176 , NULL -- ACTIVITY_BUCKET
1177 , NULL -- REF_LINE_ID
1178 , DECODE(doub.side,'D', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_DR
1179 , DECODE(doub.side,'C', DECODE(nvl(amount_dr, amount_cr), 0, 0, ABS(mcd.amount)) ,NULL ) --AMOUNT_CR
1180 , DECODE(doub.side,'D', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_DR
1181 , DECODE(doub.side,'C', DECODE(nvl(acctd_amount_dr, acctd_amount_cr), 0, 0, ABS(mcd.acctd_amount)) ,NULL ) --ACCTD_AMOUNT_CR
1182 , NULL -- REF_MF_DIST_FLAG
1183 , NULL -- REF_DIST_CCID
1184 FROM ar_misc_cash_distributions_all mcd,
1185 psa_mf_misc_dist_all psamcd,
1186 ar_cash_receipts_all cr,
1187 ar_cash_receipt_history_all crh,
1188 ar_distributions_all crhard,
1189 (SELECT 'D' side FROM DUAL UNION ALL
1190 SELECT 'C' side FROM DUAL) doub
1191 WHERE mcd.rowid >= l_start_rowid
1192 AND mcd.rowid <= l_end_rowid
1193 AND mcd.misc_cash_distribution_id = psamcd.misc_cash_distribution_id
1194 AND mcd.cash_receipt_id = cr.cash_receipt_id
1195 AND mcd.posting_control_id <> -3
1196 AND crh.cash_receipt_id = mcd.cash_receipt_id
1197 AND crh.posting_control_id <> -3
1198 AND crh.status = psamcd.reference1
1199 AND crh.cash_receipt_history_id = crhard.source_id
1200 AND crhard.source_table = 'CRH';
1201
1202
1203 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
1204
1205 ad_parallel_updates_pkg.processed_rowid_range(
1206 l_rows_processed,
1207 l_end_rowid);
1208
1209 commit;
1210
1211 ad_parallel_updates_pkg.get_rowid_range(
1212 l_start_rowid,
1213 l_end_rowid,
1214 l_any_rows_to_process,
1215 l_batch_size,
1216 FALSE);
1217
1218 l_rows_processed := 0 ;
1219
1220 END LOOP ; /* end of WHILE loop */
1221
1222
1223 EXCEPTION
1224 WHEN NO_DATA_FOUND THEN
1225 NULL;
1226 WHEN OTHERS THEN
1227 RAISE;
1228 END upgrade_misc_cash_dist;
1229
1230
1231
1232
1233
1234
1235 PROCEDURE UPGRADE_TRANSACTIONS(
1236 l_table_owner IN VARCHAR2,
1237 l_table_name IN VARCHAR2,
1238 l_script_name IN VARCHAR2,
1239 l_worker_id IN VARCHAR2,
1240 l_num_workers IN VARCHAR2,
1241 l_batch_size IN VARCHAR2,
1242 l_batch_id IN NUMBER,
1243 l_action_flag IN VARCHAR2)
1244 IS
1245 ln_AE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1246 ln_AE_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1247 ln_APPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1248 ln_CODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1249 ln_GL_TRANSFER_MODE_CODE DBMS_SQL.VARCHAR2_TABLE;
1250 ln_GL_SL_LINK_ID DBMS_SQL.NUMBER_TABLE;
1251 ln_ACCOUNTING_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1252 ln_PARTY_ID DBMS_SQL.NUMBER_TABLE;
1253 ln_PARTY_SITE_ID DBMS_SQL.NUMBER_TABLE;
1254 ln_PARTY_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1255 ln_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1256 ln_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1257 ln_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1258 ln_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1259 ln_DESCRIPTION DBMS_SQL.VARCHAR2_TABLE;
1260 ln_STATISTICAL_AMOUNT DBMS_SQL.NUMBER_TABLE;
1261 ln_CURRENCY_CODE DBMS_SQL.VARCHAR2_TABLE;
1262 ln_CURRENCY_CONVERSION_DATE DBMS_SQL.DATE_TABLE;
1263 ln_CURRENCY_CONVERSION_RATE DBMS_SQL.NUMBER_TABLE;
1264 ln_CURRENCY_CONVERSION_TYPE DBMS_SQL.VARCHAR2_TABLE;
1265 ln_USSGL_TRANSACTION_CODE DBMS_SQL.VARCHAR2_TABLE;
1266 ln_JGZZ_RECON_REF DBMS_SQL.VARCHAR2_TABLE;
1267 ln_CONTROL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1268 ln_ANALYTICAL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1269 ln_GL_SL_LINK_TABLE DBMS_SQL.VARCHAR2_TABLE;
1270 ln_DISPLAYED_LINE_NUMBER DBMS_SQL.NUMBER_TABLE;
1271 ln_UPG_BATCH_ID DBMS_SQL.NUMBER_TABLE;
1272 ln_UNROUNDED_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1273 ln_UNROUNDED_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1274 ln_GAIN_OR_LOSS_FLAG DBMS_SQL.VARCHAR2_TABLE;
1275 ln_UNROUNDED_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1276 ln_UNROUNDED_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1277 ln_SUBSTITUTED_CCID DBMS_SQL.NUMBER_TABLE;
1278 ln_BUSINESS_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1279 ln_MPA_ACCRUAL_ENTRY_FLAG DBMS_SQL.VARCHAR2_TABLE;
1280 ln_ENCUMBRANCE_TYPE_ID DBMS_SQL.NUMBER_TABLE;
1281 ln_FUNDS_STATUS_CODE DBMS_SQL.VARCHAR2_TABLE;
1282 ln_MERGE_CODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1283 ln_MERGE_PARTY_ID DBMS_SQL.NUMBER_TABLE;
1284 ln_MERGE_PARTY_SITE_ID DBMS_SQL.NUMBER_TABLE;
1285 ln_accounting_date DBMS_SQL.DATE_TABLE;
1286 ln_ledger_id DBMS_SQL.NUMBER_TABLE;
1287
1288 ev_EVENT_ID DBMS_SQL.NUMBER_TABLE;
1289 ev_APPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1290 ev_EVENT_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1291 ev_EVENT_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1292 ev_EVENT_DATE DBMS_SQL.DATE_TABLE;
1293 ev_ENTITY_ID DBMS_SQL.NUMBER_TABLE;
1294 ev_EVENT_STATUS_CODE DBMS_SQL.VARCHAR2_TABLE;
1295 ev_PROCESS_STATUS_CODE DBMS_SQL.VARCHAR2_TABLE;
1296 ev_REFERENCE_NUM_1 DBMS_SQL.NUMBER_TABLE;
1297 ev_EVENT_NUMBER DBMS_SQL.NUMBER_TABLE;
1298 ctlgd_CUST_TRX_LINE_GL_DIST_ID DBMS_SQL.NUMBER_TABLE;
1299 ctlgd_CUSTOMER_TRX_LINE_ID DBMS_SQL.NUMBER_TABLE;
1300 ctlgd_CODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1301 ctlgd_SET_OF_BOOKS_ID DBMS_SQL.NUMBER_TABLE;
1302 ctlgd_AMOUNT DBMS_SQL.NUMBER_TABLE;
1303 ctlgd_ACCTD_AMOUNT DBMS_SQL.NUMBER_TABLE;
1304 ctlgd_GL_DATE DBMS_SQL.DATE_TABLE;
1305 ctlgd_GL_POSTED_DATE DBMS_SQL.DATE_TABLE;
1306 ctlgd_ACCOUNT_CLASS DBMS_SQL.VARCHAR2_TABLE;
1307 ctlgd_posting_control_id DBMS_SQL.NUMBER_TABLE;
1308 pd_CUST_TRX_LINE_GL_DIST_ID DBMS_SQL.NUMBER_TABLE;
1309 pd_MF_RECEIVABLES_CCID DBMS_SQL.NUMBER_TABLE;
1310 pd_POSTING_CONTROL_ID DBMS_SQL.NUMBER_TABLE;
1311 cnt_by_hdr DBMS_SQL.NUMBER_TABLE;
1312 pdf_cust_trx_line_gl_dist_id DBMS_SQL.NUMBER_TABLE;
1313
1314 CURSOR c(l_start_rowid ROWID, l_end_rowid ROWID) IS
1315 SELECT /*+ leading(pd,ctlgd,gud) rowid(pd) use_nl(ctlgd,ct,ent,ev,hdr,ln,lnk) use_hash(gud) swap_join_inputs(gud)
1316 INDEX(ent xla_transaction_entities_N1)
1317 INDEX(ev XLA_EVENTS_U2)
1318 INDEX(hdr XLA_AE_HEADERS_N2)
1319 INDEX (ln, XLA_AE_LINES_U1)
1320 INDEX (lnk, XLA_DISTRIBUTION_LINKS_N1) */
1321 ln.AE_HEADER_ID
1322 , ln.AE_LINE_NUM
1323 , ln.APPLICATION_ID
1324 , ln.CODE_COMBINATION_ID
1325 , ln.GL_TRANSFER_MODE_CODE
1326 , ln.GL_SL_LINK_ID
1327 , ln.ACCOUNTING_CLASS_CODE
1328 , ln.PARTY_ID
1329 , ln.PARTY_SITE_ID
1330 , ln.PARTY_TYPE_CODE
1331 , ln.ENTERED_DR
1332 , ln.ENTERED_CR
1333 , ln.ACCOUNTED_DR
1334 , ln.ACCOUNTED_CR
1335 , ln.DESCRIPTION
1336 , ln.STATISTICAL_AMOUNT
1337 , ln.CURRENCY_CODE
1338 , ln.CURRENCY_CONVERSION_DATE
1339 , ln.CURRENCY_CONVERSION_RATE
1340 , ln.CURRENCY_CONVERSION_TYPE
1341 , ln.USSGL_TRANSACTION_CODE
1342 , ln.JGZZ_RECON_REF
1343 , ln.CONTROL_BALANCE_FLAG
1344 , ln.ANALYTICAL_BALANCE_FLAG
1345 , ln.GL_SL_LINK_TABLE
1346 , ln.DISPLAYED_LINE_NUMBER
1347 , ln.UPG_BATCH_ID
1348 , ln.UNROUNDED_ACCOUNTED_DR
1349 , ln.UNROUNDED_ACCOUNTED_CR
1350 , ln.GAIN_OR_LOSS_FLAG
1351 , ln.UNROUNDED_ENTERED_DR
1352 , ln.UNROUNDED_ENTERED_CR
1353 , ln.SUBSTITUTED_CCID
1354 , 'RECEIVABLE'
1355 , ln.MPA_ACCRUAL_ENTRY_FLAG
1356 , ln.ENCUMBRANCE_TYPE_ID
1357 , ln.FUNDS_STATUS_CODE
1358 , ln.MERGE_CODE_COMBINATION_ID
1359 , ln.MERGE_PARTY_ID
1360 , ln.MERGE_PARTY_SITE_ID
1361 , ev.EVENT_ID
1362 , ev.APPLICATION_ID
1363 , ev.EVENT_TYPE_CODE
1364 , decode(ev.event_type_code, 'INV_CREATE', 'INVOICE',
1365 'INV_UPDATE', 'INVOICE',
1366 'CM_CREATE' , 'CREDIT_MEMO',
1367 'CM_UPDATE' , 'CREDIT_MEMO',
1368 'DM_CREATE' , 'DEBIT_MEMO',
1369 'DM_UPDATE' , 'DEBIT_MEMO',
1370 'CB_CREATE' , 'CHARGEBACK',
1371 'DEP_CREATE', 'DEPOSIT',
1372 'DEP_UPDATE', 'DEPOSIT',
1373 'GUAR_CREATE','GUARANTEE',
1374 'GUAR_UPDATE','GUARANTEE',
1375 'UNKNOWN') event_class_code
1376 , ev.EVENT_DATE
1377 , ev.ENTITY_ID
1378 , ev.EVENT_STATUS_CODE
1379 , ev.PROCESS_STATUS_CODE
1380 , ev.REFERENCE_NUM_1
1381 , ev.EVENT_NUMBER
1382 , ctlgd.CUST_TRX_LINE_GL_DIST_ID
1383 , ctlgd.CUSTOMER_TRX_LINE_ID
1384 , ctlgd.CODE_COMBINATION_ID
1385 , ctlgd.SET_OF_BOOKS_ID
1386 , ctlgd.AMOUNT
1387 , ctlgd.ACCTD_AMOUNT
1388 , ctlgd.GL_DATE
1389 , ctlgd.GL_POSTED_DATE
1390 , ctlgd.ACCOUNT_CLASS
1391 , ctlgd.posting_control_id
1392 , pd.CUST_TRX_LINE_GL_DIST_ID
1393 , pd.MF_RECEIVABLES_CCID
1394 , pd.POSTING_CONTROL_ID
1395 , MAX(ln.ae_line_num) OVER (PARTITION BY ln.ae_header_id) cnt_by_hdr
1396 , pd.cust_trx_line_gl_dist_id
1397 , ln.accounting_date
1398 , ln.ledger_id
1399 FROM ra_customer_trx_all ct
1400 , ra_cust_trx_line_gl_dist_all ctlgd
1401 , xla_upgrade_dates gud
1402 , xla_transaction_entities_upg ent
1403 , xla_events ev
1404 , xla_ae_headers hdr
1405 , xla_ae_lines ln
1406 , xla_distribution_links lnk
1407 , psa_mf_trx_dist_all pd
1408 WHERE pd.ROWID >= l_start_rowid
1409 AND pd.ROWID <= l_end_rowid
1410 AND ct.customer_trx_id = ctlgd.customer_trx_id
1411 AND ctlgd.cust_trx_line_gl_dist_id = pd.cust_trx_line_gl_dist_id
1412 AND NVL(ct.ax_accounted_flag,'N') = 'N'
1413 AND ctlgd.account_set_flag = 'N'
1414 AND trunc(ctlgd.gl_date) BETWEEN gud.start_date AND gud.end_date
1415 AND CTLGD.set_of_books_id = gud.ledger_id -- changed this from ct to ctlgd to enable better join to GUD
1416 AND ent.ledger_id = ct.set_of_books_id
1417 AND ent.application_id = 222
1418 AND ent.entity_code = 'TRANSACTIONS'
1419 AND ev.application_id = 222
1420 AND hdr.application_id = 222
1421 AND ln.application_id = 222
1422 AND lnk.application_id = 222
1423 AND ent.entity_id = ev.entity_id
1424 AND ent.ledger_id = ct.set_of_books_id
1425 AND ev.upg_batch_id = l_batch_id
1426 AND ev.event_id = hdr.event_id
1427 AND hdr.ledger_id = ent.ledger_id
1428 AND hdr.event_id = ev.event_id
1429 AND hdr.ae_header_id = ln.ae_header_id
1430 AND hdr.ae_header_id = lnk.ae_header_id
1431 AND ln.ae_line_num = lnk.ae_line_num
1432 AND lnk.event_id = ev.event_id
1433 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
1434 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1435 AND nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
1436 AND ev.reference_num_1 = ctlgd.posting_control_id
1437 AND NVL(TRUNC(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date
1438 UNION
1439 SELECT /*+ leading(pd,ct,ctlgd,gud) rowid(pd) use_nl(ctlgd,ct,ent,ev,hdr,ln,lnk) use_hash(gud) swap_join_inputs(gud)
1440 INDEX(ent xla_transaction_entities_N1)
1441 INDEX(ev XLA_EVENTS_U2)
1442 INDEX(hdr XLA_AE_HEADERS_N2)
1443 INDEX (ln, XLA_AE_LINES_U1)
1444 INDEX (lnk, XLA_DISTRIBUTION_LINKS_N1) */
1445 ln.AE_HEADER_ID
1446 , ln.AE_LINE_NUM
1447 , ln.APPLICATION_ID
1448 , ln.CODE_COMBINATION_ID
1449 , ln.GL_TRANSFER_MODE_CODE
1450 , ln.GL_SL_LINK_ID
1451 , ln.ACCOUNTING_CLASS_CODE
1452 , ln.PARTY_ID
1453 , ln.PARTY_SITE_ID
1454 , ln.PARTY_TYPE_CODE
1455 , ln.ENTERED_DR
1456 , ln.ENTERED_CR
1457 , ln.ACCOUNTED_DR
1458 , ln.ACCOUNTED_CR
1459 , ln.DESCRIPTION
1460 , ln.STATISTICAL_AMOUNT
1461 , ln.CURRENCY_CODE
1462 , ln.CURRENCY_CONVERSION_DATE
1463 , ln.CURRENCY_CONVERSION_RATE
1464 , ln.CURRENCY_CONVERSION_TYPE
1465 , ln.USSGL_TRANSACTION_CODE
1466 , ln.JGZZ_RECON_REF
1467 , ln.CONTROL_BALANCE_FLAG
1468 , ln.ANALYTICAL_BALANCE_FLAG
1469 , ln.GL_SL_LINK_TABLE
1470 , ln.DISPLAYED_LINE_NUMBER
1471 , ln.UPG_BATCH_ID
1472 , ln.UNROUNDED_ACCOUNTED_DR
1473 , ln.UNROUNDED_ACCOUNTED_CR
1474 , ln.GAIN_OR_LOSS_FLAG
1475 , ln.UNROUNDED_ENTERED_DR
1476 , ln.UNROUNDED_ENTERED_CR
1477 , ln.SUBSTITUTED_CCID
1478 , ln.BUSINESS_CLASS_CODE
1479 , ln.MPA_ACCRUAL_ENTRY_FLAG
1480 , ln.ENCUMBRANCE_TYPE_ID
1481 , ln.FUNDS_STATUS_CODE
1482 , ln.MERGE_CODE_COMBINATION_ID
1483 , ln.MERGE_PARTY_ID
1484 , ln.MERGE_PARTY_SITE_ID
1485 , ev.EVENT_ID
1486 , ev.APPLICATION_ID
1487 , ev.EVENT_TYPE_CODE
1488 , decode(ev.event_type_code, 'INV_CREATE', 'INVOICE',
1489 'INV_UPDATE', 'INVOICE',
1490 'CM_CREATE' , 'CREDIT_MEMO',
1491 'CM_UPDATE' , 'CREDIT_MEMO',
1492 'DM_CREATE' , 'DEBIT_MEMO',
1493 'DM_UPDATE' , 'DEBIT_MEMO',
1494 'CB_CREATE' , 'CHARGEBACK',
1495 'DEP_CREATE', 'DEPOSIT',
1496 'DEP_UPDATE', 'DEPOSIT',
1497 'GUAR_CREATE','GUARANTEE',
1498 'GUAR_UPDATE','GUARANTEE',
1499 'UNKNOWN') event_class_code
1500 , ev.EVENT_DATE
1501 , ev.ENTITY_ID
1502 , ev.EVENT_STATUS_CODE
1503 , ev.PROCESS_STATUS_CODE
1504 , ev.REFERENCE_NUM_1
1505 , ev.EVENT_NUMBER
1506 , ctlgd.CUST_TRX_LINE_GL_DIST_ID
1507 , ctlgd.CUSTOMER_TRX_LINE_ID
1508 , ctlgd.CODE_COMBINATION_ID
1509 , ctlgd.SET_OF_BOOKS_ID
1510 , ctlgd.AMOUNT
1511 , ctlgd.ACCTD_AMOUNT
1512 , ctlgd.GL_DATE
1513 , ctlgd.GL_POSTED_DATE
1514 , ctlgd.ACCOUNT_CLASS
1515 , ctlgd.posting_control_id
1516 , ctlgd.CUST_TRX_LINE_GL_DIST_ID
1517 , NULL
1518 , ctlgd.POSTING_CONTROL_ID
1519 , 999999999 cnt_by_hdr
1520 , ctlgd.cust_trx_line_gl_dist_id
1521 , ln.accounting_date
1522 , ln.ledger_id
1523 FROM ra_customer_trx_all ct
1524 , ra_cust_trx_line_gl_dist_all ctlgd
1525 , xla_upgrade_dates gud
1526 , xla_transaction_entities_upg ent
1527 , xla_events ev
1528 , xla_ae_headers hdr
1529 , xla_ae_lines ln
1530 , xla_distribution_links lnk
1531 ,(SELECT /*+ rowid(pdist) use_nl(dist) no_merge */ dist.customer_trx_id
1532 FROM ra_cust_trx_line_gl_dist_all dist,
1533 psa_mf_trx_dist_all pdist,
1534 xla_upgrade_dates xud
1535 WHERE pdist.cust_trx_line_gl_dist_id = dist.cust_trx_line_gl_dist_id
1536 AND trunc(dist.gl_date) between xud.start_date and xud.end_date
1537 AND pdist.ROWID >= l_start_rowid
1538 AND pdist.ROWID <= l_end_rowid
1539 AND NOT EXISTS
1540 ( SELECT /*+ordered */ 'x'
1541 FROM xla_transaction_entities_upg xte,
1542 xla_ae_headers xah,
1543 xla_ae_lines xal
1544 WHERE nvl(xte.source_id_int_1,-99) = dist.customer_trx_id
1545 AND xte.ledger_id = dist.set_of_books_id
1546 AND xte.application_id = 222
1547 AND xte.entity_code = 'TRANSACTIONS'
1548 AND xte.entity_id = xah.entity_id
1549 AND xah.application_id = 222
1550 AND xte.ledger_id = xah.ledger_id
1551 AND xah.ae_header_id = xal.ae_header_id
1552 AND xal.application_id = 222
1553 AND xal.accounting_class_code = 'RECEIVABLE'
1554 AND xal.ae_line_num > 999999999
1555 AND xal.accounting_date between xud.start_date and xud.end_date
1556 )
1557 GROUP BY dist.customer_trx_id
1558 ) pd
1559 WHERE ct.customer_trx_id = pd.customer_trx_id
1560 AND ct.customer_trx_id = ctlgd.customer_trx_id
1561 AND ctlgd.account_class = 'REC'
1562 AND ctlgd.account_set_flag = 'N'
1563 AND NVL(ct.ax_accounted_flag,'N') = 'N'
1564 AND trunc(ctlgd.gl_date) BETWEEN gud.start_date AND gud.end_date
1565 AND CTLGD.set_of_books_id = gud.ledger_id -- changed this from ct to ctlgd to enable better join to GUD
1566 AND ent.ledger_id = ct.set_of_books_id
1567 AND ent.application_id = 222
1568 AND ent.entity_code = 'TRANSACTIONS'
1569 AND ev.upg_batch_id = l_batch_id
1570 AND ev.application_id = 222
1571 AND hdr.application_id = 222
1572 AND ln.application_id = 222
1573 AND lnk.application_id = 222
1574 AND ent.entity_id = ev.entity_id
1575 AND ent.ledger_id = ct.set_of_books_id
1576 AND ev.event_id = hdr.event_id
1577 AND hdr.ledger_id = ent.ledger_id
1578 AND hdr.event_id = ev.event_id
1579 AND hdr.ae_header_id = ln.ae_header_id
1580 AND hdr.ae_header_id = lnk.ae_header_id
1581 AND ln.ae_line_num = lnk.ae_line_num
1582 AND lnk.event_id = ev.event_id
1583 AND lnk.source_distribution_id_num_1 = ctlgd.cust_trx_line_gl_dist_id
1584 AND lnk.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
1585 AND nvl(ent.source_id_int_1,-99) = ct.customer_trx_id
1586 AND ev.reference_num_1 = ctlgd.posting_control_id
1587 AND NVL(TRUNC(ctlgd.gl_date), to_date('01-01-1900','DD-MM-YYYY')) = ev.event_date;
1588
1589
1590
1591 --xla_ae_lines
1592
1593 LAE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1594 LAE_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1595 LAPPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1596 LCODE_COMBINATION_ID DBMS_SQL.NUMBER_TABLE;
1597 LGL_TRANSFER_MODE_CODE DBMS_SQL.VARCHAR2_TABLE;
1598 LGL_SL_LINK_ID DBMS_SQL.VARCHAR2_TABLE;
1599 LACCOUNTING_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1600 LPARTY_ID DBMS_SQL.NUMBER_TABLE;
1601 LPARTY_SITE_ID DBMS_SQL.NUMBER_TABLE;
1602 LPARTY_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1603 LENTERED_DR DBMS_SQL.NUMBER_TABLE;
1604 LENTERED_CR DBMS_SQL.NUMBER_TABLE;
1605 LACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1606 LACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1607 LDESCRIPTION DBMS_SQL.VARCHAR2_TABLE;
1608 LSTATISTICAL_AMOUNT DBMS_SQL.NUMBER_TABLE;
1609 LCURRENCY_CODE DBMS_SQL.VARCHAR2_TABLE;
1610 LCURRENCY_CONVERSION_DATE DBMS_SQL.DATE_TABLE;
1611 LCURRENCY_CONVERSION_RATE DBMS_SQL.NUMBER_TABLE;
1612 LCURRENCY_CONVERSION_TYPE DBMS_SQL.VARCHAR2_TABLE;
1613 LUSSGL_TRANSACTION_CODE DBMS_SQL.VARCHAR2_TABLE;
1614 LJGZZ_RECON_REF DBMS_SQL.VARCHAR2_TABLE;
1615 LCONTROL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1616 LANALYTICAL_BALANCE_FLAG DBMS_SQL.VARCHAR2_TABLE;
1617 LGL_SL_LINK_TABLE DBMS_SQL.VARCHAR2_TABLE;
1618 LDISPLAYED_LINE_NUMBER DBMS_SQL.NUMBER_TABLE;
1619 LUPG_BATCH_ID DBMS_SQL.NUMBER_TABLE;
1620 LUNROUNDED_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1621 LUNROUNDED_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1622 LGAIN_OR_LOSS_FLAG DBMS_SQL.VARCHAR2_TABLE;
1623 LUNROUNDED_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1624 LUNROUNDED_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1625 LBUSINESS_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1626 laccounting_date DBMS_SQL.DATE_TABLE;
1627 lledger_id DBMS_SQL.NUMBER_TABLE;
1628
1629
1630 --xla_distribution_links
1631
1632 DAPPLICATION_ID DBMS_SQL.NUMBER_TABLE;
1633 DEVENT_ID DBMS_SQL.NUMBER_TABLE;
1634 DAE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1635 DAE_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1636 DSOURCE_DISTRIBUTION_TYPE DBMS_SQL.VARCHAR2_TABLE;
1637 DSOURCE_DISTRIBUTION_ID_NUM_1 DBMS_SQL.NUMBER_TABLE;
1638 DTAX_LINE_REF_ID DBMS_SQL.NUMBER_TABLE;
1639 DREF_AE_HEADER_ID DBMS_SQL.NUMBER_TABLE;
1640 DREF_TEMP_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1641 DACCOUNTING_LINE_CODE DBMS_SQL.VARCHAR2_TABLE;
1642 DACCOUNTING_LINE_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1643 DMERGE_DUPLICATE_CODE DBMS_SQL.VARCHAR2_TABLE;
1644 DTEMP_LINE_NUM DBMS_SQL.NUMBER_TABLE;
1645 DREF_EVENT_ID DBMS_SQL.NUMBER_TABLE;
1646 DEVENT_CLASS_CODE DBMS_SQL.VARCHAR2_TABLE;
1647 DEVENT_TYPE_CODE DBMS_SQL.VARCHAR2_TABLE;
1648 DUPG_BATCH_ID DBMS_SQL.NUMBER_TABLE;
1649 DUNROUNDED_ENTERED_DR DBMS_SQL.NUMBER_TABLE;
1650 DUNROUNDED_ENTERED_CR DBMS_SQL.NUMBER_TABLE;
1651 DUNROUNDED_ACCOUNTED_CR DBMS_SQL.NUMBER_TABLE;
1652 DUNROUNDED_ACCOUNTED_DR DBMS_SQL.NUMBER_TABLE;
1653
1654 empty_varchar2_list DBMS_SQL.VARCHAR2_TABLE;
1655 empty_number_list DBMS_SQL.NUMBER_TABLE;
1656 empty_date_list DBMS_SQL.DATE_TABLE;
1657
1658
1659 l_sys_date DATE := SYSDATE;
1660
1661 l_start_rowid rowid;
1662 l_end_rowid rowid;
1663 l_any_rows_to_process BOOLEAN;
1664 l_rows_processed NUMBER := 0;
1665 l_last_fetch BOOLEAN := FALSE;
1666 BEGIN
1667
1668 /* ------ Initialize the rowid ranges ------ */
1669 ad_parallel_updates_pkg.initialize_rowid_range(
1670 ad_parallel_updates_pkg.ROWID_RANGE,
1671 l_table_owner,
1672 l_table_name,
1673 l_script_name,
1674 l_worker_id,
1675 l_num_workers,
1676 l_batch_size, 0);
1677
1678 /* ------ Get rowid ranges ------ */
1679 ad_parallel_updates_pkg.get_rowid_range(
1680 l_start_rowid,
1681 l_end_rowid,
1682 l_any_rows_to_process,
1683 l_batch_size,
1684 TRUE);
1685
1686
1687 WHILE ( l_any_rows_to_process = TRUE )
1688 LOOP
1689
1690 l_rows_processed := 0;
1691
1692 OPEN c(l_start_rowid, l_end_rowid);
1693 LOOP
1694 FETCH c BULK COLLECT INTO
1695 ln_AE_HEADER_ID
1696 ,ln_AE_LINE_NUM
1697 ,ln_APPLICATION_ID
1698 ,ln_CODE_COMBINATION_ID
1699 ,ln_GL_TRANSFER_MODE_CODE
1700 ,ln_GL_SL_LINK_ID
1701 ,ln_ACCOUNTING_CLASS_CODE
1702 ,ln_PARTY_ID
1703 ,ln_PARTY_SITE_ID
1704 ,ln_PARTY_TYPE_CODE
1705 ,ln_ENTERED_DR
1706 ,ln_ENTERED_CR
1707 ,ln_ACCOUNTED_DR
1708 ,ln_ACCOUNTED_CR
1709 ,ln_DESCRIPTION
1710 ,ln_STATISTICAL_AMOUNT
1711 ,ln_CURRENCY_CODE
1712 ,ln_CURRENCY_CONVERSION_DATE
1713 ,ln_CURRENCY_CONVERSION_RATE
1714 ,ln_CURRENCY_CONVERSION_TYPE
1715 ,ln_USSGL_TRANSACTION_CODE
1716 ,ln_JGZZ_RECON_REF
1717 ,ln_CONTROL_BALANCE_FLAG
1718 ,ln_ANALYTICAL_BALANCE_FLAG
1719 ,ln_GL_SL_LINK_TABLE
1720 ,ln_DISPLAYED_LINE_NUMBER
1721 ,ln_UPG_BATCH_ID
1722 ,ln_UNROUNDED_ACCOUNTED_DR
1723 ,ln_UNROUNDED_ACCOUNTED_CR
1724 ,ln_GAIN_OR_LOSS_FLAG
1725 ,ln_UNROUNDED_ENTERED_DR
1726 ,ln_UNROUNDED_ENTERED_CR
1727 ,ln_SUBSTITUTED_CCID
1728 ,ln_BUSINESS_CLASS_CODE
1729 ,ln_MPA_ACCRUAL_ENTRY_FLAG
1730 ,ln_ENCUMBRANCE_TYPE_ID
1731 ,ln_FUNDS_STATUS_CODE
1732 ,ln_MERGE_CODE_COMBINATION_ID
1733 ,ln_MERGE_PARTY_ID
1734 ,ln_MERGE_PARTY_SITE_ID
1735 ,ev_EVENT_ID
1736 ,ev_APPLICATION_ID
1737 ,ev_EVENT_TYPE_CODE
1738 ,ev_EVENT_CLASS_CODE
1739 ,ev_EVENT_DATE
1740 ,ev_ENTITY_ID
1741 ,ev_EVENT_STATUS_CODE
1742 ,ev_PROCESS_STATUS_CODE
1743 ,ev_REFERENCE_NUM_1
1744 ,ev_EVENT_NUMBER
1745 ,ctlgd_CUST_TRX_LINE_GL_DIST_ID
1746 ,ctlgd_CUSTOMER_TRX_LINE_ID
1747 ,ctlgd_CODE_COMBINATION_ID
1748 ,ctlgd_SET_OF_BOOKS_ID
1749 ,ctlgd_AMOUNT
1750 ,ctlgd_ACCTD_AMOUNT
1751 ,ctlgd_GL_DATE
1752 ,ctlgd_GL_POSTED_DATE
1753 ,ctlgd_ACCOUNT_CLASS
1754 ,ctlgd_posting_control_id
1755 ,pd_CUST_TRX_LINE_GL_DIST_ID
1756 ,pd_MF_RECEIVABLES_CCID
1757 ,pd_POSTING_CONTROL_ID
1758 ,cnt_by_hdr
1759 ,pdf_cust_trx_line_gl_dist_id
1760 ,ln_accounting_date
1761 ,ln_ledger_id
1762 LIMIT 1000;
1763
1764 IF c%NOTFOUND THEN
1765 l_last_fetch := TRUE;
1766 END IF;
1767 IF (ctlgd_ACCOUNT_CLASS.COUNT = 0) AND (l_last_fetch) THEN
1768 EXIT;
1769 END IF;
1770
1771 LAE_HEADER_ID := empty_number_list;
1772 LAE_LINE_NUM := empty_number_list;
1773 LAPPLICATION_ID := empty_number_list;
1774 LCODE_COMBINATION_ID := empty_number_list;
1775 LGL_TRANSFER_MODE_CODE := empty_varchar2_list;
1776 LGL_SL_LINK_ID := empty_varchar2_list;
1777 LACCOUNTING_CLASS_CODE := empty_varchar2_list;
1778 LPARTY_ID := empty_number_list;
1779 LPARTY_SITE_ID := empty_number_list;
1780 LPARTY_TYPE_CODE := empty_varchar2_list;
1781 LENTERED_DR := empty_number_list;
1782 LENTERED_CR := empty_number_list;
1783 LACCOUNTED_DR := empty_number_list;
1784 LACCOUNTED_CR := empty_number_list;
1785 LDESCRIPTION := empty_varchar2_list;
1786 LSTATISTICAL_AMOUNT := empty_number_list;
1787 LCURRENCY_CODE := empty_varchar2_list;
1788 LCURRENCY_CONVERSION_DATE := empty_date_list;
1789 LCURRENCY_CONVERSION_RATE := empty_number_list;
1790 LCURRENCY_CONVERSION_TYPE := empty_varchar2_list;
1791 LUSSGL_TRANSACTION_CODE := empty_varchar2_list;
1792 LJGZZ_RECON_REF := empty_varchar2_list;
1793 LCONTROL_BALANCE_FLAG := empty_varchar2_list;
1794 LANALYTICAL_BALANCE_FLAG := empty_varchar2_list;
1795 LGL_SL_LINK_TABLE := empty_varchar2_list;
1796 LDISPLAYED_LINE_NUMBER := empty_number_list;
1797 LUPG_BATCH_ID := empty_number_list;
1798 LUNROUNDED_ACCOUNTED_DR := empty_number_list;
1799 LUNROUNDED_ACCOUNTED_CR := empty_number_list;
1800 LGAIN_OR_LOSS_FLAG := empty_varchar2_list;
1801 LUNROUNDED_ENTERED_DR := empty_number_list;
1802 LUNROUNDED_ENTERED_CR := empty_number_list;
1803 LBUSINESS_CLASS_CODE := empty_varchar2_list;
1804 laccounting_date := empty_date_list;
1805 lledger_id := empty_number_list;
1806
1807
1808 --xla_distribution_links
1809
1810 DAPPLICATION_ID := empty_number_list;
1811 DEVENT_ID := empty_number_list;
1812 DAE_HEADER_ID := empty_number_list;
1813 DAE_LINE_NUM := empty_number_list;
1814 DSOURCE_DISTRIBUTION_TYPE := empty_varchar2_list;
1815 DSOURCE_DISTRIBUTION_ID_NUM_1 := empty_number_list;
1816 DTAX_LINE_REF_ID := empty_number_list;
1817 DREF_AE_HEADER_ID := empty_number_list;
1818 DREF_TEMP_LINE_NUM := empty_number_list;
1819 DACCOUNTING_LINE_CODE := empty_varchar2_list;
1820 DACCOUNTING_LINE_TYPE_CODE := empty_varchar2_list;
1821 DMERGE_DUPLICATE_CODE := empty_varchar2_list;
1822 DTEMP_LINE_NUM := empty_number_list;
1823 DREF_EVENT_ID := empty_number_list;
1824 DEVENT_CLASS_CODE := empty_varchar2_list;
1825 DEVENT_TYPE_CODE := empty_varchar2_list;
1826 DUPG_BATCH_ID := empty_number_list;
1827 DUNROUNDED_ENTERED_DR := empty_number_list;
1828 DUNROUNDED_ENTERED_CR := empty_number_list;
1829 DUNROUNDED_ACCOUNTED_CR := empty_number_list;
1830 DUNROUNDED_ACCOUNTED_DR := empty_number_list;
1831
1832
1833 -- FOR PSA upgrade, only ae lines are necessary
1834 -- No distribution links
1835 -- no denormalization event
1836
1837 -- Distribution links data is also required for Business Flows bug 8437222
1838 FOR i IN ln_AE_HEADER_ID.FIRST .. ln_AE_HEADER_ID.LAST LOOP
1839
1840 IF ctlgd_ACCOUNT_CLASS(i) = 'REC' THEN
1841
1842 -- Construct the reversal
1843 -- ae line
1844
1845 LAE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1846 LAE_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i);
1847 LAPPLICATION_ID(i) := 222;
1848 LCODE_COMBINATION_ID(i) := NVL(ln_CODE_COMBINATION_ID(i),-1);
1849 LGL_TRANSFER_MODE_CODE(i) := ln_GL_TRANSFER_MODE_CODE(i);
1850 LGL_SL_LINK_ID(i) := ln_GL_SL_LINK_ID(i);
1851 LACCOUNTING_CLASS_CODE(i) := ln_ACCOUNTING_CLASS_CODE(i);
1852 LPARTY_ID(i) := ln_PARTY_ID(i);
1853 LPARTY_SITE_ID(i) := ln_PARTY_SITE_ID(i);
1854 LPARTY_TYPE_CODE(i) := ln_PARTY_TYPE_CODE(i);
1855 LENTERED_DR(i) := ln_ENTERED_CR(i);
1856 LENTERED_CR(i) := ln_ENTERED_DR(i);
1857 LACCOUNTED_DR(i) := ln_ACCOUNTED_CR(i);
1858 LACCOUNTED_CR(i) := ln_ACCOUNTED_DR(i);
1859 LDESCRIPTION(i) := 'MFAR UPGRADE REVERSE AR RECEIVABLES';
1860 LSTATISTICAL_AMOUNT(i) := ln_STATISTICAL_AMOUNT(i);
1861 LCURRENCY_CODE(i) := ln_CURRENCY_CODE(i);
1862 LCURRENCY_CONVERSION_DATE(i) := ln_CURRENCY_CONVERSION_DATE(i);
1863 LCURRENCY_CONVERSION_RATE(i) := ln_CURRENCY_CONVERSION_RATE(i);
1864 LCURRENCY_CONVERSION_TYPE(i) := ln_CURRENCY_CONVERSION_TYPE(i);
1865 LUSSGL_TRANSACTION_CODE(i) := ln_USSGL_TRANSACTION_CODE(i);
1866 LJGZZ_RECON_REF(i) := ln_JGZZ_RECON_REF(i);
1867 LCONTROL_BALANCE_FLAG(i) := ln_CONTROL_BALANCE_FLAG(i);
1868 LANALYTICAL_BALANCE_FLAG(i) := ln_ANALYTICAL_BALANCE_FLAG(i);
1869 LGL_SL_LINK_TABLE(i) := ln_GL_SL_LINK_TABLE(i);
1870 LDISPLAYED_LINE_NUMBER(i) := ln_DISPLAYED_LINE_NUMBER(i);
1871 LUPG_BATCH_ID(i) := ln_UPG_BATCH_ID(i);
1872 LUNROUNDED_ACCOUNTED_DR(i) := ln_UNROUNDED_ACCOUNTED_CR(i);
1873 LUNROUNDED_ACCOUNTED_CR(i) := ln_UNROUNDED_ACCOUNTED_DR(i);
1874 LGAIN_OR_LOSS_FLAG(i) := ln_GAIN_OR_LOSS_FLAG(i);
1875 LUNROUNDED_ENTERED_DR(i) := ln_UNROUNDED_ENTERED_CR(i);
1876 LUNROUNDED_ENTERED_CR(i) := ln_UNROUNDED_ENTERED_DR(i);
1877 LBUSINESS_CLASS_CODE(i) := ln_BUSINESS_CLASS_CODE(i);
1878 laccounting_date(i) := ln_accounting_date(i);
1879 lledger_id(i) := ln_ledger_id(i);
1880
1881 -- distribution links for MFAR reversal
1882 DAPPLICATION_ID(i) := 222;
1883 DEVENT_ID(i) := ev_EVENT_ID(i);
1884 DAE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1885 DAE_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i);
1886 DSOURCE_DISTRIBUTION_TYPE(i) := 'RA_CUST_TRX_LINE_GL_DIST_ALL';
1887 DSOURCE_DISTRIBUTION_ID_NUM_1(i) := pdf_cust_trx_line_gl_dist_id(i);
1888 DTAX_LINE_REF_ID(i) := NULL;
1889 DREF_AE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1890 DREF_TEMP_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i);
1891 DACCOUNTING_LINE_CODE(i) := ln_ACCOUNTING_CLASS_CODE(i);
1892 DACCOUNTING_LINE_TYPE_CODE(i) := 'C';
1893 DMERGE_DUPLICATE_CODE(i) := 'N';
1894 DTEMP_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i);
1895 DREF_EVENT_ID(i) := ev_EVENT_ID(i);
1896 DEVENT_CLASS_CODE(i) := ev_EVENT_CLASS_CODE(i);
1897 DEVENT_TYPE_CODE(i) := ev_EVENT_TYPE_CODE(i);
1898 DUPG_BATCH_ID(i) := ln_UPG_BATCH_ID(i);
1899 -- Populating entered amounts in unrounded amounts columns as
1900 -- upgrade data doesn't have unrounded amounts
1901 DUNROUNDED_ENTERED_DR(i) := ln_ENTERED_CR(i);
1902 DUNROUNDED_ENTERED_CR(i) := ln_ENTERED_DR(i);
1903 DUNROUNDED_ACCOUNTED_DR(i) := ln_ACCOUNTED_CR(i);
1904 DUNROUNDED_ACCOUNTED_CR(i) := ln_ACCOUNTED_DR(i);
1905
1906 ELSIF pdf_cust_trx_line_gl_dist_id(i) IS NOT NULL THEN
1907
1908 -- Construct the MFAR REC
1909 -- ae line
1910
1911 LAE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1912 LAE_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i) + 1; /*bug 5837507*/
1913 LAPPLICATION_ID(i) := 222;
1914 LCODE_COMBINATION_ID(i) := NVL(pd_MF_RECEIVABLES_CCID(i),-1);
1915 LGL_TRANSFER_MODE_CODE(i) := ln_GL_TRANSFER_MODE_CODE(i);
1916 LGL_SL_LINK_ID(i) := ln_GL_SL_LINK_ID(i);
1917 LACCOUNTING_CLASS_CODE(i) := 'RECEIVABLE';
1918 LPARTY_ID(i) := ln_PARTY_ID(i);
1919 LPARTY_SITE_ID(i) := ln_PARTY_SITE_ID(i);
1920 LPARTY_TYPE_CODE(i) := ln_PARTY_TYPE_CODE(i);
1921 IF ctlgd_AMOUNT(i) >= 0 THEN
1922 LENTERED_DR(i) := ctlgd_AMOUNT(i);
1923 LENTERED_CR(i) := NULL;
1924 ELSE
1925 LENTERED_DR(i) := NULL;
1926 LENTERED_CR(i) := ctlgd_AMOUNT(i);
1927 END IF;
1928 IF ctlgd_ACCTD_AMOUNT(i) >= 0 THEN
1929 LACCOUNTED_DR(i) := ctlgd_ACCTD_AMOUNT(i);
1930 LACCOUNTED_CR(i) := NULL;
1931 ELSE
1932 LACCOUNTED_DR(i) := NULL;
1933 LACCOUNTED_CR(i) := ctlgd_ACCTD_AMOUNT(i);
1934 END IF;
1935 LDESCRIPTION(i) := 'MFAR UPGRADE CREATE MFAR RECEIVABLES';
1936 LSTATISTICAL_AMOUNT(i) := ln_STATISTICAL_AMOUNT(i);
1937 LCURRENCY_CODE(i) := ln_CURRENCY_CODE(i);
1938 LCURRENCY_CONVERSION_DATE(i) := ln_CURRENCY_CONVERSION_DATE(i);
1939 LCURRENCY_CONVERSION_RATE(i) := ln_CURRENCY_CONVERSION_RATE(i);
1940 LCURRENCY_CONVERSION_TYPE(i) := ln_CURRENCY_CONVERSION_TYPE(i);
1941 LUSSGL_TRANSACTION_CODE(i) := ln_USSGL_TRANSACTION_CODE(i);
1942 LJGZZ_RECON_REF(i) := ln_JGZZ_RECON_REF(i);
1943 LCONTROL_BALANCE_FLAG(i) := ln_CONTROL_BALANCE_FLAG(i);
1944 LANALYTICAL_BALANCE_FLAG(i) := ln_ANALYTICAL_BALANCE_FLAG(i);
1945 LGL_SL_LINK_TABLE(i) := ln_GL_SL_LINK_TABLE(i);
1946 LDISPLAYED_LINE_NUMBER(i) := ln_DISPLAYED_LINE_NUMBER(i);
1947 LUPG_BATCH_ID(i) := ln_UPG_BATCH_ID(i);
1948 IF ctlgd_ACCTD_AMOUNT(i) >= 0 THEN
1949 LUNROUNDED_ACCOUNTED_DR(i) := ctlgd_ACCTD_AMOUNT(i);
1950 LUNROUNDED_ACCOUNTED_CR(i) := NULL;
1951 ELSE
1952 LUNROUNDED_ACCOUNTED_DR(i) := NULL;
1953 LUNROUNDED_ACCOUNTED_CR(i) := ctlgd_ACCTD_AMOUNT(i);
1954 END IF;
1955 LGAIN_OR_LOSS_FLAG(i) := ln_GAIN_OR_LOSS_FLAG(i);
1956 IF ctlgd_AMOUNT(i) >= 0 THEN
1957 LUNROUNDED_ENTERED_DR(i) := ctlgd_AMOUNT(i);
1958 LUNROUNDED_ENTERED_CR(i) := NULL;
1959 ELSE
1960 LUNROUNDED_ENTERED_DR(i) := NULL;
1961 LUNROUNDED_ENTERED_CR(i) := ctlgd_AMOUNT(i);
1962 END IF;
1963 LBUSINESS_CLASS_CODE(i) := ln_BUSINESS_CLASS_CODE(i);
1964 laccounting_date(i) := ln_accounting_date(i);
1965 lledger_id(i) := ln_ledger_id(i);
1966
1967 -- distribution links for MFAR
1968 DAPPLICATION_ID(i) := 222;
1969 DEVENT_ID(i) := ev_EVENT_ID(i);
1970 DAE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1971 DAE_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i) + 1;
1972 DSOURCE_DISTRIBUTION_TYPE(i) := 'RA_CUST_TRX_LINE_GL_DIST_ALL';
1973 DSOURCE_DISTRIBUTION_ID_NUM_1(i) := pdf_cust_trx_line_gl_dist_id(i);
1974 DTAX_LINE_REF_ID(i) := NULL;
1975 DREF_AE_HEADER_ID(i) := ln_AE_HEADER_ID(i);
1976 DREF_TEMP_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i) + 1;
1977 DACCOUNTING_LINE_CODE(i) := 'RECEIVABLE';
1978 DACCOUNTING_LINE_TYPE_CODE(i) := 'C';
1979 DMERGE_DUPLICATE_CODE(i) := 'N';
1980 DTEMP_LINE_NUM(i) := cnt_by_hdr(i) + ln_AE_LINE_NUM(i) + 1;
1981 DREF_EVENT_ID(i) := ev_EVENT_ID(i);
1982 DEVENT_CLASS_CODE(i) := ev_EVENT_CLASS_CODE(i);
1983 DEVENT_TYPE_CODE(i) := ev_EVENT_TYPE_CODE(i);
1984 DUPG_BATCH_ID(i) := ln_UPG_BATCH_ID(i);
1985 IF ctlgd_AMOUNT(i) >= 0 THEN
1986 DUNROUNDED_ENTERED_DR(i) := ctlgd_AMOUNT(i);
1987 DUNROUNDED_ENTERED_CR(i) := NULL;
1988 ELSE
1989 DUNROUNDED_ENTERED_DR(i) := NULL;
1990 DUNROUNDED_ENTERED_CR(i) := ctlgd_AMOUNT(i);
1991 END IF;
1992 IF ctlgd_ACCTD_AMOUNT(i) >= 0 THEN
1993 DUNROUNDED_ACCOUNTED_DR(i) := ctlgd_ACCTD_AMOUNT(i);
1994 DUNROUNDED_ACCOUNTED_CR(i) := NULL;
1995 ELSE
1996 DUNROUNDED_ACCOUNTED_DR(i) := NULL;
1997 DUNROUNDED_ACCOUNTED_CR(i) := ctlgd_ACCTD_AMOUNT(i);
1998 END IF;
1999 END IF;
2000 END LOOP;
2001
2002
2003 FORALL i IN LAE_HEADER_ID.FIRST .. LAE_HEADER_ID.LAST
2004 INSERT INTO xla_ae_lines
2005 ( AE_HEADER_ID ,
2006 AE_LINE_NUM ,
2007 APPLICATION_ID ,
2008 CODE_COMBINATION_ID ,
2009 GL_TRANSFER_MODE_CODE ,
2010 GL_SL_LINK_ID ,
2011 ACCOUNTING_CLASS_CODE ,
2012 PARTY_ID ,
2013 PARTY_SITE_ID ,
2014 PARTY_TYPE_CODE ,
2015 ENTERED_DR ,
2016 ENTERED_CR ,
2017 ACCOUNTED_DR ,
2018 ACCOUNTED_CR ,
2019 DESCRIPTION ,
2020 STATISTICAL_AMOUNT ,
2021 CURRENCY_CODE ,
2022 CURRENCY_CONVERSION_DATE ,
2023 CURRENCY_CONVERSION_RATE ,
2024 CURRENCY_CONVERSION_TYPE ,
2025 USSGL_TRANSACTION_CODE ,
2026 JGZZ_RECON_REF ,
2027 CONTROL_BALANCE_FLAG ,
2028 ANALYTICAL_BALANCE_FLAG ,
2029 GL_SL_LINK_TABLE ,
2030 DISPLAYED_LINE_NUMBER ,
2031 UPG_BATCH_ID ,
2032 UNROUNDED_ACCOUNTED_DR ,
2033 UNROUNDED_ACCOUNTED_CR ,
2034 GAIN_OR_LOSS_FLAG ,
2035 UNROUNDED_ENTERED_DR ,
2036 UNROUNDED_ENTERED_CR ,
2037 BUSINESS_CLASS_CODE ,
2038 CREATION_DATE ,
2039 CREATED_BY ,
2040 LAST_UPDATE_DATE ,
2041 LAST_UPDATED_BY ,
2042 accounting_date ,
2043 ledger_id ) VALUES
2044 (LAE_HEADER_ID(i),
2045 LAE_LINE_NUM(i),
2046 LAPPLICATION_ID(i),
2047 LCODE_COMBINATION_ID(i),
2048 LGL_TRANSFER_MODE_CODE(i),
2049 LGL_SL_LINK_ID(i),
2050 LACCOUNTING_CLASS_CODE(i),
2051 LPARTY_ID(i),
2052 LPARTY_SITE_ID(i),
2053 LPARTY_TYPE_CODE(i),
2054 LENTERED_DR(i),
2055 LENTERED_CR(i),
2056 LACCOUNTED_DR(i),
2057 LACCOUNTED_CR(i),
2058 LDESCRIPTION(i),
2059 LSTATISTICAL_AMOUNT(i),
2060 LCURRENCY_CODE(i),
2061 LCURRENCY_CONVERSION_DATE(i),
2062 LCURRENCY_CONVERSION_RATE(i),
2063 LCURRENCY_CONVERSION_TYPE(i),
2064 LUSSGL_TRANSACTION_CODE(i),
2065 LJGZZ_RECON_REF(i),
2066 LCONTROL_BALANCE_FLAG(i),
2067 LANALYTICAL_BALANCE_FLAG(i),
2068 LGL_SL_LINK_TABLE(i),
2069 LDISPLAYED_LINE_NUMBER(i),
2070 LUPG_BATCH_ID(i),
2071 LUNROUNDED_ACCOUNTED_DR(i),
2072 LUNROUNDED_ACCOUNTED_CR(i),
2073 LGAIN_OR_LOSS_FLAG(i),
2074 LUNROUNDED_ENTERED_DR(i),
2075 LUNROUNDED_ENTERED_CR(i),
2076 LBUSINESS_CLASS_CODE(i),
2077 l_sys_date,
2078 0,
2079 l_sys_date,
2080 0 ,
2081 laccounting_date(i),
2082 lledger_id(i));
2083
2084 FORALL i IN LAE_HEADER_ID.FIRST .. LAE_HEADER_ID.LAST
2085 INSERT INTO XLA_DISTRIBUTION_LINKS
2086 (APPLICATION_ID,
2087 EVENT_ID,
2088 AE_HEADER_ID,
2089 AE_LINE_NUM,
2090 ACCOUNTING_LINE_CODE,
2091 ACCOUNTING_LINE_TYPE_CODE,
2092 REF_AE_HEADER_ID,
2093 SOURCE_DISTRIBUTION_TYPE,
2094 SOURCE_DISTRIBUTION_ID_NUM_1,
2095 SOURCE_DISTRIBUTION_ID_NUM_2,
2096 SOURCE_DISTRIBUTION_ID_NUM_3,
2097 SOURCE_DISTRIBUTION_ID_NUM_4,
2098 SOURCE_DISTRIBUTION_ID_NUM_5,
2099 UNROUNDED_ENTERED_DR,
2100 UNROUNDED_ENTERED_CR,
2101 UNROUNDED_ACCOUNTED_DR,
2102 UNROUNDED_ACCOUNTED_CR,
2103 MERGE_DUPLICATE_CODE,
2104 TAX_LINE_REF_ID,
2105 TAX_SUMMARY_LINE_REF_ID,
2106 TAX_REC_NREC_DIST_REF_ID,
2107 STATISTICAL_AMOUNT,
2108 TEMP_LINE_NUM,
2109 EVENT_TYPE_CODE,
2110 EVENT_CLASS_CODE,
2111 REF_EVENT_ID,
2112 UPG_BATCH_ID)
2113 VALUES
2114 (DAPPLICATION_ID(i),
2115 DEVENT_ID(i),
2116 DAE_HEADER_ID(i),
2117 DAE_LINE_NUM(i),
2118 DACCOUNTING_LINE_CODE(i),
2119 DACCOUNTING_LINE_TYPE_CODE(i),
2120 DREF_AE_HEADER_ID(i), --reference header id
2121 DSOURCE_DISTRIBUTION_TYPE(i),
2122 DSOURCE_DISTRIBUTION_ID_NUM_1(i),
2123 '',
2124 '',
2125 '',
2126 '',
2127 DUNROUNDED_ENTERED_DR(i),
2128 DUNROUNDED_ENTERED_CR(i),
2129 DUNROUNDED_ACCOUNTED_DR(i),
2130 DUNROUNDED_ACCOUNTED_CR(i),
2131 DMERGE_DUPLICATE_CODE(i),
2132 DTAX_LINE_REF_ID(i),
2133 '',
2134 '',
2135 '',
2136 DTEMP_LINE_NUM(i),
2137 DEVENT_TYPE_CODE(i),
2138 DEVENT_CLASS_CODE(i),
2139 DREF_EVENT_ID(i),
2140 DUPG_BATCH_ID(i));
2141
2142 IF l_last_fetch = TRUE THEN
2143 EXIT;
2144 END IF;
2145
2146
2147
2148 END LOOP;
2149 CLOSE c;
2150
2151 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2152
2153 ad_parallel_updates_pkg.processed_rowid_range(
2154 l_rows_processed,
2155 l_end_rowid);
2156
2157 commit;
2158
2159 ad_parallel_updates_pkg.get_rowid_range(
2160 l_start_rowid,
2161 l_end_rowid,
2162 l_any_rows_to_process,
2163 l_batch_size,
2164 FALSE);
2165
2166 l_rows_processed := 0 ;
2167
2168 END LOOP ; /* end of WHILE loop */
2169
2170 commit;
2171
2172 EXCEPTION
2173 WHEN NO_DATA_FOUND THEN NULL;
2174 WHEN OTHERS THEN
2175 RAISE;
2176 END UPGRADE_TRANSACTIONS;
2177
2178
2179
2180 END;