[Home] [Help]
PACKAGE BODY: APPS.CST_ACCRUAL_LOAD
Source
1 PACKAGE BODY CST_ACCRUAL_LOAD as
2 /* $Header: CSTACCLB.pls 120.62.12020000.6 2013/02/25 15:22:38 pmarada ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_ACCRUAL_LOAD';
5 G_LOG_HEADER CONSTANT VARCHAR2(40) := 'cst.plsql.CST_ACCRUAL_LOAD';
6 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
8
9 --Local procedure and function
10 g_dummy_date CONSTANT DATE := TO_DATE('3000/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS');
11 g_def_start_date CONSTANT DATE := TO_DATE('1900/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS');
12
16 | |
13 /*===========================================================================+
14 | |
15 | Procedure Name : debug |
17 | Purpose : This Procedure logs the message in fnd_log_messages |
18 | |
19 | Called from : |
20 | |
21 | Parameters : |
22 | IN : line IN VARCHAR2 |
23 | msg_prefix IN VARCHAR2 |
24 | msg_module IN VARCHAR2 |
25 | msg_level IN NUMBER |
26 | OUT : |
27 | |
28 | Created Aug-08 Herve Yu |
29 | |
30 +===========================================================================*/
31 PROCEDURE debug
32 ( line IN VARCHAR2,
33 msg_prefix IN VARCHAR2 DEFAULT 'CST',
34 msg_module IN VARCHAR2 DEFAULT G_PKG_NAME,
35 msg_level IN NUMBER DEFAULT FND_LOG.LEVEL_STATEMENT)
36 IS
37 l_msg_prefix VARCHAR2(64);
38 l_msg_level NUMBER;
39 l_msg_module VARCHAR2(256);
40 l_beg_end_suffix VARCHAR2(15);
41 l_org_cnt NUMBER;
42 l_line VARCHAR2(32767);
43 BEGIN
44 l_line := line;
45 l_msg_prefix := msg_prefix;
46 l_msg_level := msg_level;
47 l_msg_module := msg_module;
48
49 fnd_file.put_line(fnd_file.LOG, line);
50
51 IF (INSTRB(upper(l_line), 'EXCEPTION') <> 0) THEN
52 l_msg_level := FND_LOG.LEVEL_EXCEPTION;
53 END IF;
54 IF l_msg_level <> FND_LOG.LEVEL_EXCEPTION AND G_DEBUG = 'N' THEN
55 RETURN;
56 END IF;
57 IF ( l_msg_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
58 FND_LOG.STRING(l_msg_level,l_msg_module,SUBSTRB(l_line,1,1000));
59 FND_FILE.put_line(fnd_file.log, SUBSTRB(l_line,1,1000) );
60 END IF;
61 EXCEPTION
62 WHEN OTHERS THEN RAISE;
63 END debug;
64
65 /*===========================================================================+
66 | |
67 | Procedure Name : xla_min_upg_date |
68 | |
69 | Purpose : This Procedure gets information whether XLA is upgraded |
70 | or not for that Operating Unit. |
71 | x_xla_upg = Y If XLA upgraded |
72 | = N If xla NOT upgraded |
73 | x_min_upg_date = g_dummy_date, If XLA NOT upgraded |
74 | = Min xla Upgrade Date, If XLA upgraded |
75 | |
76 | Called from : DetUpgDatesFromDate |
77 | |
78 | Parameters : |
79 | IN : p_operating_unit IN NUMBER REQUIRED |
80 | |
81 | OUT : x_xla_upg OUT VARCHAR2 |
82 | x_min_upg_date OUT DATE |
83 | |
84 | Created Aug-08 Herve Yu |
85 | |
86 +===========================================================================*/
87 PROCEDURE xla_min_upg_date
88 (p_operating_unit_id IN NUMBER,
89 x_xla_upg OUT NOCOPY VARCHAR2,
90 x_min_upg_date OUT NOCOPY DATE)
91 IS
92 CURSOR sob_upg_date(p_operating_unit_id IN NUMBER) IS
93 SELECT MIN(b.start_date)
94 FROM cst_acct_info_v a,
95 gl_period_statuses b
96 WHERE a.ledger_id = b.set_of_books_id
97 AND b.migration_status_code = 'U'
98 AND a.operating_unit = to_char(p_operating_unit_id);
99
100 l_min_date DATE;
101 BEGIN
102 debug('ou_min_upg_date +');
103 debug(' p_operating_unit_id:'||p_operating_unit_id);
104 OPEN sob_upg_date(p_operating_unit_id);
105 FETCH sob_upg_date INTO x_min_upg_date ;
106 IF sob_upg_date%NOTFOUND THEN
107 x_xla_upg := 'N';
108 x_min_upg_date := g_dummy_date;
109 ELSE
110 IF x_min_upg_date IS NULL THEN
111 x_xla_upg := 'N';
112 x_min_upg_date := g_dummy_date;
113 ELSE
114 x_xla_upg := 'Y';
115 END IF;
116 END IF;
117 CLOSE sob_upg_date;
118 debug(' x_min_upg_date :'||x_min_upg_date );
119 debug(' x_xla_upg :'||x_xla_upg );
120 debug('ou_min_upg_date -');
121 EXCEPTION
122 WHEN OTHERS THEN
123 debug('EXCEPTION in sob_upg_date:'||SQLERRM);
124 RAISE;
125 END xla_min_upg_date;
126
127 /*===========================================================================+
128 | |
129 | Procedure Name : build_run |
130 | |
134 | = N <=> NOT 1st build |
131 | Purpose : This Procedure gets information whether the Accrual Load |
132 | Build is first run or not for that Operating Unit |
133 | x_first_build = Y <=> 1st build |
135 | x_min_build_date = g_dummy_date, IF first_build |
136 | = Min Build Date, If Second upgraded |
137 | |
138 | Called from : DetUpgDatesFromDate |
139 | |
140 | Parameters : |
141 | IN : p_operating_unit IN NUMBER REQUIRED |
142 | |
143 | OUT : x_first_build OUT VARCHAR2 |
144 | x_min_build_date OUT DATE |
145 | |
146 | Created Aug-08 Herve Yu |
147 | |
148 +===========================================================================*/
149 PROCEDURE build_run
150 (p_operating_unit IN NUMBER,
151 x_first_build OUT NOCOPY VARCHAR2,
152 x_min_build_date OUT NOCOPY DATE)
153 IS
154 CURSOR min_build_date(p_operating_unit IN NUMBER) IS
155 SELECT MIN(DECODE(from_date,g_def_start_date, g_dummy_date,from_date))
156 FROM CST_RECONCILIATION_BUILD
157 WHERE operating_unit_id = p_operating_unit;
158 BEGIN
159 debug('build_run +');
160 debug(' p_operating_unit :'||p_operating_unit);
161
162 OPEN min_build_date(p_operating_unit=>p_operating_unit);
163 FETCH min_build_date INTO x_min_build_date;
164 IF min_build_date%NOTFOUND THEN
165 x_first_build := 'Y';
166 x_min_build_date := g_dummy_date;
167 ELSE
168 IF x_min_build_date IS NULL THEN
169 x_first_build := 'Y';
170 x_min_build_date := g_dummy_date;
171 ELSE
172 x_first_build := 'N';
173 END IF;
174 END IF;
175 CLOSE min_build_date;
176
177 debug(' x_first_build :'||x_first_build);
178 debug(' x_min_build_date :'||x_min_build_date);
179 debug('build_run -');
180 EXCEPTION
181 WHEN OTHERS THEN
182 debug('EXCEPTION build_run :'||SQLERRM);
183 END build_run;
184
185 /*===========================================================================+
186 | |
187 | Procedure Name : min_mre_date |
188 | |
189 | Purpose : This Procedure gets the minimum receipt date from |
190 | xla_events table. |
191 | |
192 | Called from : DetUpgDatesFromDate |
193 | |
194 | Parameters : |
195 | IN : NONE |
196 | |
197 | OUT : x_mre_date OUT DATE |
198 | |
199 | Created Aug-08 Herve Yu |
200 | |
201 +===========================================================================*/
202 PROCEDURE min_mre_date
203 (x_mre_date OUT NOCOPY DATE)
204 IS
205 CURSOR c_min_recp IS
206 SELECT Min(min_event_date) FROM (
207 SELECT min(ev.event_date) min_event_date
208 FROM xla_events ev
209 WHERE ev.application_id IN (707,555) /*Bug 8426283 - included application id 555 also*/
210 AND ev.event_type_code = 'RECEIVE'
211 AND ev.process_status_code = 'P'
212 AND ev.event_status_code = 'P'
213 union
214 SELECT min(ev.event_date) min_event_date
215 FROM xla_events ev
216 WHERE ev.application_id IN (200)
217 /*Bug 8349881 in case of manual invoices,
218 an invoice can be raised ahead of PO*/
219 AND ev.process_status_code = 'P'
220 AND ev.event_status_code = 'P' ) event_date_minimum;
221
222 BEGIN
223 debug('min_mre_date +');
224 OPEN c_min_recp;
225 FETCH c_min_recp INTO x_mre_date;
226 IF c_min_recp%NOTFOUND THEN
227 x_mre_date := SYSDATE;
228 ELSE
229 IF x_mre_date IS NULL THEN
230 x_mre_date := SYSDATE;
231 END IF;
232 END IF;
233 CLOSE c_min_recp;
234 debug(' x_mre_date:'||x_mre_date);
235 debug('min_mre_date -');
236 END min_mre_date;
237
238
239 /*===========================================================================+
240 | |
241 | Procedure Name : DetUpgDatesFromDate |
242 | |
243 | Purpose : This Procedure determines the From Date of Load Build |
244 | program, and whether to upgrade old data or not. |
245 | |
246 | |
250 | IN : p_operating_unit IN NUMBER |
247 | Called from : Start_accrual_load |
248 | |
249 | Parameters : |
251 | p_from_date IN DATE |
252 | |
253 | OUT : x_from_date OUT NOCOPY DATE, |
254 | x_upg_old_data OUT NOCOPY VARCHAR2 |
255 | x_old_from_date OUT NOCOPY DATE |
256 | x_old_to_date OUT NOCOPY DATE |
257 | |
258 | Created Aug-08 Herve Yu |
259 | |
260 +===========================================================================*/
261 PROCEDURE DetUpgDatesFromDate
262 (p_operating_unit IN NUMBER,
263 p_from_date IN DATE,
264 x_from_date OUT NOCOPY DATE,
265 x_upg_old_data OUT NOCOPY VARCHAR2,
266 x_old_from_date OUT NOCOPY DATE,
267 x_old_to_date OUT NOCOPY DATE)
268 IS
269 l_xla_upg VARCHAR2(1);
270 l_min_upg_date DATE;
271 l_first_build VARCHAR2(1);
272 l_min_build_date DATE;
273 l_mre_date DATE;
274 BEGIN
275 debug('DetUpgDatesFromDate+');
276 debug(' p_operating_unit :' || p_operating_unit);
277
278 xla_min_upg_date(p_operating_unit_id => p_operating_unit,
279 x_xla_upg => l_xla_upg,
280 x_min_upg_date => l_min_upg_date);
281
282 build_run(p_operating_unit => p_operating_unit,
283 x_first_build => l_first_build,
284 x_min_build_date => l_min_build_date);
285
286 ----------------------------------------------------------------------------------------------------------------------
287 -- FirstBuild XlaUpg BldDate XlaUpgDate PFromDate Condition xFromDate OldUpg OFromDate OToDate Comment
288 ----------------------------------------------------------------------------------------------------------------------
289 -- Y Y NA XD PD NA XD Y XD SYSDATE 1
290 -- Y Y NA XD NULL NA XD Y XD SYSDATE 2
291 -- Y N NA NA PD PD>=MRE PD N NA NA 3 New Customer
292 -- PD<MRE MRE N NA NA 4 New Customer
293 -- Y N NA NA NULL NA MRE N NA NA 5 New Customer
294 -----------------------------------------------------------------------------------------------------------------------
295 -- N Y BD XD NULL BD=XD XD N NA NA 6
296 -- N Y BD XD NULL BD>XD XD Y XD BD-1 7 IncBuild
297 -- N Y BD XD PD BD>XD AND PD>=BD PD N NA NA 8 User needs to activate Incbuild
298 -- N Y BD XD PD BD>XD AND PD<BD XD Y XD BD-1 9 IncBuild
299 -- N Y BD XD PD BD=XD AND PD<BD XD N NA NA 10
300 -- N Y BD XD PD BD=XD AND PD>=BD PD N NA NA 11
301 -- N N BD NA NULL NA BD N NA NA 12 New Customer
302 -- N N BD NA PD PD>=BD PD N NA NA 13 New Customer
303 -- N N BD NA PD PD<BD AND PD>MRE PD N NA NA 14 New Customer
304 -- N N BD NA PD PD<BD AND PD<MRE MRE N NA NA 15 New Customer
305 -----------------------------------------------------------------------------------------------------------------------
306 -- Concurrent program Accrual build should default the MAX BD to the end user always except first build
307 -----------------------------------------------------------------------------------------------------------------------
308
309 debug('----------------------');
310 debug('l_first_build :'||l_first_build);
311 debug(' l_xla_upg :'||l_xla_upg);
312 debug(' BD l_min_build_date :'||l_min_build_date);
313 debug(' XD l_min_upg_date :'||l_min_upg_date);
314 debug(' PD p_from_date :'||p_from_date);
315 debug('----------------------');
316
317 IF l_first_build = 'Y' AND l_xla_upg = 'Y' THEN
318 debug(' Case 1 or 2');
319 x_from_date := l_min_upg_date;
320 x_upg_old_data := 'Y';
321 x_old_from_date := l_min_upg_date;
322 x_old_to_date := SYSDATE;
323 ELSIF l_first_build = 'Y' AND l_xla_upg = 'N' THEN
324 min_mre_date(x_mre_date => l_mre_date);
325 IF p_from_date IS NULL THEN
326 debug(' Case 5');
327 x_from_date := l_mre_date;
328 x_upg_old_data := 'N';
329 ELSE
330 IF p_from_date >= l_mre_date THEN
331 debug(' Case 3');
332 x_from_date := p_from_date;
333 x_upg_old_data := 'N';
334 ELSE
335 debug(' Case 4');
336 x_from_date := l_mre_date;
337 x_upg_old_data := 'N';
338 END IF;
339 END IF;
340 ELSIF l_first_build = 'N' AND l_xla_upg = 'Y' THEN
341 IF p_from_date IS NULL THEN
342 IF l_min_build_date <= l_min_upg_date THEN
343 debug(' Case 6');
344 x_from_date := l_min_upg_date;
345 x_upg_old_data := 'N';
346 ELSE
350 x_old_from_date := l_min_upg_date;
347 debug(' Case 7');
348 x_from_date := l_min_upg_date;
349 x_upg_old_data := 'Y';
351 x_old_to_date := l_min_build_date-1;
352 END IF;
353 ELSE
354 IF l_min_build_date > l_min_upg_date THEN
355 IF p_from_date >= l_min_build_date THEN
356 debug(' Case 8');
357 x_from_date := p_from_date;
358 x_upg_old_data := 'N';
359 ELSE
360 debug(' Case 9');
361 x_from_date := l_min_upg_date;
362 x_upg_old_data := 'Y';
363 x_old_from_date := l_min_upg_date;
364 x_old_to_date := l_min_build_date-1;
365 END IF;
366 ELSE
367 IF p_from_date >= l_min_build_date THEN
368 debug(' Case 11');
369 x_from_date := p_from_date;
370 x_upg_old_data := 'N';
371 ELSE
372 debug(' Case 10');
373 x_from_date := l_min_upg_date;
374 x_upg_old_data := 'N';
375 END IF;
376 END IF;
377 END IF;
378 ELSIF l_first_build = 'N' AND l_xla_upg = 'N' THEN
379 IF p_from_date IS NULL THEN
380 debug(' Case 12');
381 x_from_date := l_min_build_date;
382 x_upg_old_data := 'N';
383 ELSE
384 IF p_from_date >= l_min_build_date THEN
385 debug(' Case 13');
386 x_from_date := p_from_date;
387 x_upg_old_data := 'N';
388 ELSE
389 min_mre_date(x_mre_date => l_mre_date);
390 IF p_from_date < l_min_build_date THEN
391 IF p_from_date >= l_mre_date THEN
392 debug(' Case 14');
393 x_from_date := p_from_date;
394 x_upg_old_data := 'N';
395 ELSE
396 debug(' Case 15');
397 x_from_date := l_mre_date;
398 x_upg_old_data := 'N';
399 END IF;
400 END IF;
401 END IF;
402 END IF;
403 END IF;
404 IF x_upg_old_data = 'N' THEN
405 x_old_from_date := NULL;
406 x_old_to_date := NULL;
407 END IF;
408 debug(' x_from_date :'||x_from_date);
409 debug(' x_upg_old_data :'||x_upg_old_data);
410 debug(' x_old_from_date:'||x_old_from_date);
411 debug(' x_old_to_date :'||x_old_to_date);
412 debug('DetUpgDatesFromDate-');
413 EXCEPTION
414 WHEN OTHERS THEN
415 debug('OTHERS EXCEPTION IN DetUpgDatesFromDate'||SQLERRM);
416 RAISE;
417 END DetUpgDatesFromDate;
418
419 /*===========================================================================+
420 | |
421 | Procedure Name : Start_accrual_load |
422 | |
423 | Purpose : This Procedure kicks off the Accrual load process |
424 | and passes control to the accrual load procedure |
425 | |
426 | Called from : The Accrual Concurrent Load program |
427 | |
428 | Parameters : |
429 | IN : p_operating_unit IN NUMBER REQUIRED |
430 | p_from_date IN VARCHAR2 can be NULL |
431 | p_to_date IN VARCHAR2 can be NULL |
432 | |
433 | OUT : errbuf OUT NOCOPY VARCHAR2 |
434 | retcode OUT NOCOPY NUMBER |
435 | These 2 OUT variables are the standard OUT variables |
436 | that the conc program definition expects. |
437 | |
438 | NOTES : None |
439 +===========================================================================*/
440
441 PROCEDURE start_accrual_load(errbuf OUT NOCOPY VARCHAR2,
442 retcode OUT NOCOPY NUMBER,
443 p_from_date IN VARCHAR2,
444 p_to_date IN VARCHAR2
445 )
446 IS
447 l_stmt_num NUMBER;
448 l_operating_unit NUMBER;
449 l_conc_request BOOLEAN;
450
451 l_api_version CONSTANT NUMBER := 1.0;
452 l_init_message_list CONSTANT VARCHAR2(10) := 'FALSE';
453 l_commit CONSTANT VARCHAR2(1) := FND_API.G_FALSE;
454
455 l_call_error VARCHAR2(400);
456
457 l_err_data VARCHAR2(2400);
458 l_err_status VARCHAR2(10);
459 l_err_ret_status VARCHAR2(10);
460 l_err_count NUMBER;
461
462 l_api_name CONSTANT VARCHAR2(30) := 'Start_accrual_load';
463 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
464 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
465
466 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
467 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
468 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
469 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
473
470 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
471
472 BEGIN
474 debug('start_accrual_load +');
475
476 l_stmt_num := 5;
477
478 l_err_status := fnd_api.g_ret_sts_success;
479 l_err_ret_status := fnd_api.g_ret_sts_success;
480
481 debug(' l_stmt_num :' || l_stmt_num);
482 debug(' p_from_date :' || p_from_date);
483 debug(' p_to_date :' || p_to_date);
484
485
486 /* Get the current ORG_ID by calling the MOAC package */
487
488 l_operating_unit := mo_global.get_current_org_id;
489
490 debug(' l_operating_unit:'||l_operating_unit);
491
492 /* Call the Accrual Load API and pass it all the parameters */
493
494 accrual_load(p_api_version => l_api_version,
495 p_init_msg_list => l_init_message_list,
496 p_commit => l_commit,
497 p_operating_unit => l_operating_unit,
498 p_from_date => p_from_date,
499 p_to_date => p_to_date,
500 x_return_status => l_err_status,
501 x_msg_count => l_err_count,
502 x_msg_data => l_err_data
503 );
504
505 debug(' accrual_load - l_err_status:'||l_err_status);
506 debug(' accrual_load - l_err_count :'||l_err_count);
507 debug(' accrual_load - l_err_data :'||l_err_data);
508
509
510 IF l_err_count IS NOT NULL AND l_err_count > 0 THEN
511 RAISE fnd_api.g_exc_unexpected_error;
512 END IF;
513
514 l_stmt_num := 10;
515
516 debug('start_accrual_load-');
517
518 EXCEPTION
519
520 WHEN FND_API.g_exc_unexpected_error THEN
521 ROLLBACK;
522 debug('EXCEPTION FND_API.g_exc_unexpected_error IN Start_accrual_load');
523 debug(' l_stmt_num :'||l_stmt_num);
524 debug(' l_err_status:'||l_err_status);
525 debug(' l_err_count :'||l_err_count);
526 debug(' l_err_data :'||l_err_data);
527
528 l_conc_request := fnd_concurrent.set_completion_status('ERROR',substrb(l_err_data,1,240));
529
530 RETURN;
531
532 WHEN OTHERS THEN
533 rollback;
534 debug('EXCEPTION OTHERS IN Start_accrual_load: '||substr(SQLERRM,1,180));
535
536 l_conc_request := fnd_concurrent.set_completion_status('ERROR',substrb(SQLERRM,1,240));
537
538 RETURN;
539
540 END Start_accrual_load;
541
542 /*===========================================================================+
543 | |
544 | Procedure Name : accrual_load |
545 | |
546 | Purpose : This Procedure transfers control to the procedures |
547 | that upgrade and load data from the transaction tables. |
548 | |
549 | Called from : Start_accrual_load Procedure |
550 | |
551 | Parameters : |
552 | IN : p_api_version IN NUMBER REQUIRED |
553 | p_init_msg_list IN VARCHAR2 REQUIRED |
554 | p_commit IN VARCHAR2 REQUIRED |
555 | p_operating_unit IN NUMBER REQUIRED |
556 | p_from_date IN VARCHAR2 can be NULL |
557 | p_to_date IN VARCHAR2 can be NULL |
558 | |
559 | OUT : x_return_status OUT NOCOPY VARCHAR2 |
560 | x_msg_count OUT NOCOPY NUMBER |
561 | x_msg_data OUT NOCOPY VARCHAR2 |
562 | |
563 | NOTES : None |
564 +===========================================================================*/
565
566
567 PROCEDURE accrual_load(p_api_version IN NUMBER,
568 p_init_msg_list IN VARCHAR2,
569 p_commit IN VARCHAR2,
570 p_operating_unit IN NUMBER,
571 p_from_date IN VARCHAR2,
572 p_to_date IN VARCHAR2,
573 x_return_status OUT NOCOPY VARCHAR2,
574 x_msg_count OUT NOCOPY NUMBER,
575 x_msg_data OUT NOCOPY VARCHAR2
576 )
577 IS
578
579 l_from_date DATE;
580 l_to_date DATE;
581 l_build_count NUMBER;
582 l_debug VARCHAR2(80);
583 l_round_unit NUMBER;
584 l_stmt_num NUMBER;
585 l_err_data VARCHAR2(2400);
586 l_err_status VARCHAR2(10);
587 l_err_count NUMBER;
588 l_conc_request BOOLEAN;
589 l_req_arg VARCHAR2(20);
590 l_req_running NUMBER;
591 l_acc_accounts NUMBER;
592
593 l_api_version CONSTANT NUMBER := 1.0;
594 l_api_message VARCHAR2(1000);
595 l_error_message VARCHAR2(300);
596 l_call_error VARCHAR2(400);
597
598 l_api_name CONSTANT VARCHAR2(30) := 'accrual_load';
599 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
600 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
601
605 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
602 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
603 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
604 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
606 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
607
608 l_old_from_date DATE;
609 l_old_to_date DATE;
610 l_upg_old_data VARCHAR2(1);
611 l_p_from_date DATE;
612 stop_here EXCEPTION;
613 --}
614
615 BEGIN
616 debug('accrual_load+');
617 debug(' p_operating_unit:'||p_operating_unit);
618 debug(' p_from_date := ' || p_from_date);
619 debug(' p_to_date := ' || p_to_date);
620
621 l_stmt_num := 5;
622
623 debug(' l_stmt_num:'||l_stmt_num);
624 -- Standard call to check for call compatibility
625 IF NOT FND_API.Compatible_API_Call (
626 l_api_version,
627 p_api_version,
628 l_api_name,
629 G_PKG_NAME
630 ) THEN
631 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
632 END IF;
633
634 x_return_status := fnd_api.g_ret_sts_success;
635
636 /* check for incompatibility. If there is another load running for the same OU,error out this one */
637 --{BUG 12625738
638 /*SELECT FCR.argument1 into l_req_arg
639 FROM FND_CONCURRENT_REQUESTS FCR
640 WHERE FCR.concurrent_program_id = FND_GLOBAL.CONC_PROGRAM_ID
641 AND FCR.program_application_id = FND_GLOBAL.PROG_APPL_ID
642 AND FCR.request_id = FND_GLOBAL.CONC_REQUEST_ID;
643
644 debug(' l_req_arg:'||l_req_arg);
645
646 SELECT count(*) into l_req_running
647 FROM FND_CONCURRENT_REQUESTS FCR
648 WHERE FCR.concurrent_program_id = FND_GLOBAL.CONC_PROGRAM_ID
649 AND FCR.program_application_id = FND_GLOBAL.PROG_APPL_ID
650 AND FCR.phase_code = 'R'
651 AND FCR.argument1 = l_req_arg;*/
652
653 SELECT count(*) into l_req_running
654 FROM FND_CONCURRENT_REQUESTS FCR
655 WHERE FCR.concurrent_program_id = FND_GLOBAL.CONC_PROGRAM_ID
656 AND FCR.program_application_id = FND_GLOBAL.PROG_APPL_ID
657 AND FCR.phase_code = 'R'
658 AND FCR.org_id = p_operating_unit;
659 --}
660
661 debug(' l_req_running:'||l_req_running);
662
663
664 IF (l_req_running > 1) THEN /* More than one running, so error this one out*/
665 l_error_message := 'CST_ACC_ERROR';
666 fnd_message.set_name('BOM','CST_ACC_ERROR');
667 RAISE fnd_api.g_exc_error;
668 END IF;
669
670
671 l_stmt_num := 10;
672 debug(' l_stmt_num:'||l_stmt_num);
673
674 /* check if there are accounts selected in CST_ACCRUAL_ACCOUNTS table. If not then error out */
675
676 SELECT count(*)
677 INTO l_acc_accounts
678 FROM cst_accrual_accounts
679 WHERE operating_unit_id = p_operating_unit
680 AND ROWNUM = 1;
681
682 debug(' l_acc_accounts:'||l_acc_accounts);
683
684
685 IF l_acc_accounts = 0 THEN
686 l_error_message := 'CST_ACC_ACCOUNTS_ERR';
687 fnd_message.set_name('BOM','CST_ACC_ACCOUNTS_ERR');
688 RAISE fnd_api.g_exc_error;
689 END IF;
690
691 l_stmt_num := 20;
692 debug(' l_stmt_num:'||l_stmt_num);
693
694 /* Check if this is the first build.If it is,then,make a call to the upgrade
695 function.Also ignore the from and to dates provided by the user and run the
696 load for the complete time range */
697
698
699 --BUG#7275286
700
701
702 /*
703 Select count(*)
704 INTO l_build_count
705 FROM CST_RECONCILIATION_BUILD
706 WHERE operating_unit_id = p_operating_unit
707 AND rownum = 1;
708
709
710 If (l_build_count = 0 OR p_from_date is NULL)then
711 l_from_date := to_date('1900/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS');
712 elsif (p_from_date is NOT NULL) then
713 l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
714 End If;
715 */
716
717
718 l_p_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
719
720
721 DetUpgDatesFromDate(p_operating_unit => p_operating_unit,
722 p_from_date => l_p_from_date,
723 x_from_date => l_from_date,
724 x_upg_old_data => l_upg_old_data,
725 x_old_from_date => l_old_from_date,
726 x_old_to_date => l_old_to_date);
727
728 l_stmt_num := 30;
729 debug(' l_stmt_num:'||l_stmt_num);
730
731
732
733
734 IF (p_to_date IS NULL) THEN
735 l_to_date := trunc(sysdate) + 0.99999;
736 ELSE
737 l_to_date := to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS') + 0.99999 /*Bug 16188096*/;
738 END IF;
739
740
741 debug(' The from date is:' || to_char(l_from_date,'YYYY/MM/DD HH24:MI:SS'));
742 debug(' The to date is :' || to_char(l_to_date,'YYYY/MM/DD HH24:MI:SS'));
743
744 l_stmt_num := 40;
745 debug(' l_stmt_num:'||l_stmt_num);
746
747 /* check the dates passed in and error out if p_to_date < p_from_date */
748
749 IF l_from_date > l_to_date THEN
750
751
755 RAISE fnd_api.g_exc_error;
752 IF l_p_from_date > l_to_date THEN
753 l_error_message := 'CST_INVALID_TO_DATE';
754 fnd_message.set_name('BOM','CST_INVALID_TO_DATE');
756 ELSE
757 fnd_message.set_name('FND','FORM_TECHNICAL_ERROR');
758 fnd_message.set_token('MESSAGE','Calculated from_date:'||
759 TO_CHAR(l_from_date,'DD-MM-YYYY')||' > l_to_date:'||
760 TO_CHAR(l_to_date,'DD-MM-YYYY'));
761 RAISE stop_here;
762 END IF;
763
764 END IF;
765
766 l_stmt_num := 45;
767 debug(' l_stmt_num:'||l_stmt_num);
768 debug(' l_upg_old_data :'||l_upg_old_data );
769
770 -- If l_build_count = 0 then
771
772 IF l_upg_old_data = 'Y' THEN
773
774 -- make a call to the upgrade script handler
775
776 upgrade_old_data(p_operating_unit => p_operating_unit,
777 --BUG#7275286
778
779 p_upg_from_date => l_old_from_date,
780 p_upg_to_date => l_old_to_date,
781 --}
782 x_msg_count => l_err_count,
783 x_msg_data => l_err_data,
784 x_return_status => l_err_status);
785
786 If l_err_status <> FND_API.G_RET_STS_SUCCESS then
787
788 debug(' Upgrade_old_data API fails with ');
789 debug(' x_msg_count = '||l_err_count);
790 debug(' x_msg_data = '||l_err_data);
791 debug(' x_return_status = '||l_err_status );
792 RAISE fnd_api.g_exc_unexpected_error;
793
794 END IF; --check of l_err_status
795
796
797 END IF; -- l_upg_old_data = 'Y'
798
799
800 -- End If;/* l_build_count = 0 */
801
802 l_stmt_num := 50;
803 debug(' l_stmt_num:'||l_stmt_num);
804
805
806 Insert_build_parameters(p_operating_unit => p_operating_unit,
807 p_from_date => l_from_date,
808 p_to_date => l_to_date,
809 x_msg_count => l_err_count,
810 x_msg_data => l_err_data,
811 x_return_status => l_err_status
812 );
813
814 IF l_err_status <> FND_API.G_RET_STS_SUCCESS THEN
815 IF l_exceptionLog THEN
816 l_call_error := 'Insert_build_parameters API fails with '
817 ||'x_msg_count = '||to_char(l_err_count)
818 ||'x_msg_data = '||l_err_data
819 ||'x_return_status = '||l_err_status ;
820 END IF;
821 debug(l_call_error);
822 RAISE fnd_api.g_exc_unexpected_error;
823 END IF;
824
825
826 -- Added for bug 7528609 so that upgrade data is committed
827 -- even if the request errors later while processing.
828 -- This would prevent re-upgrade of old data in the next run
829 -- and saves lot of time.
830 IF l_upg_old_data = 'Y' THEN
831 COMMIT;
832 debug(' As upgrading of old data was successful, Committed the upgrade data ');
833 END IF;
834
835 l_stmt_num := 60;
836 debug(' l_stmt_num:'||l_stmt_num);
837
838
839 /* pick up currency related stuff */
840
841 SELECT NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
842 INTO l_round_unit
843 FROM fnd_currencies fc,
844 gl_sets_of_books gsb,
845 financials_system_params_all fsp
846 WHERE fsp.org_id = p_operating_unit
847 AND fsp.set_of_books_id = gsb.set_of_books_id
848 AND fc.currency_code = gsb.currency_code;
849
850 debug(' l_round_unit:'||l_round_unit);
851
852
853 l_stmt_num := 70;
854 debug(' l_stmt_num:'||l_stmt_num);
855
856 Load_ap_po_data(p_operating_unit => p_operating_unit,
857 p_from_date => l_from_date,
858 p_to_date => l_to_date,
859 p_round_unit => l_round_unit,
860 x_msg_count => l_err_count,
861 x_msg_data => l_err_data,
862 x_return_status => l_err_status
863 );
864
865 IF l_err_status <> FND_API.G_RET_STS_SUCCESS THEN
866
867 debug('Load_ap_po_data API fails with ');
868 debug(' x_msg_count : '||l_err_count);
869 debug(' x_msg_data : '||l_err_data);
870 debug(' x_return_status : '||l_err_status);
871 RAISE fnd_api.g_exc_unexpected_error;
872
873 END IF;
874
875
876 l_stmt_num := 80;
877 debug(' l_stmt_num:'||l_stmt_num);
878
879 Load_ap_misc_data(p_operating_unit => p_operating_unit,
880 p_from_date => l_from_date,
881 p_to_date => l_to_date,
882 p_round_unit => l_round_unit,
883 x_msg_count => l_err_count,
884 x_msg_data => l_err_data,
885 x_return_status => l_err_status
886 );
887
888
889 IF l_err_status <> FND_API.G_RET_STS_SUCCESS THEN
890 debug('Load_ap_misc_data API fails with ');
891 debug(' x_msg_count :'||to_char(l_err_count));
892 debug(' x_msg_data :'||l_err_data);
893 debug(' x_return_status :'||l_err_status);
894 debug(l_call_error);
895 RAISE fnd_api.g_exc_unexpected_error;
896 END IF;
897
898
899 l_stmt_num := 90;
903 p_from_date => l_from_date,
900 debug(' l_stmt_num:'||l_stmt_num);
901
902 Load_inv_misc_data(p_operating_unit => p_operating_unit,
904 p_to_date => l_to_date,
905 p_round_unit => l_round_unit,
906 x_msg_count => l_err_count,
907 x_msg_data => l_err_data,
908 x_return_status => l_err_status
909 );
910
911
912 IF l_err_status <> FND_API.G_RET_STS_SUCCESS THEN
913 debug('Load_inv_misc_data API fails with ');
914 debug(' x_msg_count : '||l_err_count);
915 debug(' x_msg_data : '||l_err_data);
916 debug(' x_return_status : '||l_err_status );
917 RAISE fnd_api.g_exc_unexpected_error;
918 END IF;
919
920 --- Standard check of p_commit
921 IF FND_API.to_Boolean(p_commit) THEN
922 COMMIT WORK;
923 END IF;
924
925 debug('accrual_load-');
926
927 EXCEPTION
928 WHEN FND_API.g_exc_error THEN
929 ROLLBACK;
930 x_return_status := FND_API.g_ret_sts_error;
931 debug('EXCEPTION FND_API.g_exc_error IN accrual_load');
932 debug('l_stmt_num :'||l_stmt_num);
933 FND_MSG_PUB.count_and_get
934 ( p_count => x_msg_count
935 , p_data => x_msg_data
936 );
937 debug('x_msg_count:'||x_msg_count);
938 debug('x_msg_data :'||SUBSTRB(x_msg_data,1,1000));
939
940 l_conc_request := fnd_concurrent.set_completion_status('ERROR',SUBSTRB(x_msg_data,1,140)||' in accrual_load - statement:'||l_stmt_num);
941
942
943 WHEN FND_API.g_exc_unexpected_error THEN
944 ROLLBACK;
945 x_return_status := FND_API.g_ret_sts_unexp_error ;
946 debug('EXCEPTION FND_API.g_exc_unexpected_error IN accrual_load');
947 debug('l_stmt_num :'||l_stmt_num);
948
949 FND_MSG_PUB.count_and_get
950 ( p_count => x_msg_count
951 , p_data => x_msg_data
952 );
953 debug('x_msg_count:'||x_msg_count);
954 debug('x_msg_data :'||SUBSTRB(x_msg_data,1,1000));
955
956 l_conc_request := fnd_concurrent.set_completion_status('ERROR',SUBSTRB(x_msg_data,1,140)||'accrual_load - statement:'||l_stmt_num);
957
958 WHEN stop_here THEN
959
960 ROLLBACK;
961 x_return_status := FND_API.g_ret_sts_success;
962 debug('Stop here IN accrual_load');
963 debug('l_stmt_num :'||l_stmt_num);
964 FND_MSG_PUB.count_and_get
965 ( p_count => x_msg_count
966 , p_data => x_msg_data
967 );
968
969 l_conc_request := fnd_concurrent.set_completion_status('WARNING','STOP HERE in accrual_load at statement '||l_stmt_num);
970
971
972 WHEN OTHERS THEN
973
974 ROLLBACK;
975 x_return_status := FND_API.g_ret_sts_unexp_error ;
976 fnd_message.set_name('BOM','CST_UNEXPECTED');
977 fnd_message.set_token('TOKEN',SUBSTRB(SQLERRM,1,180));
978 debug('EXCEPTION OTHERS in accrual_load '||SUBSTRB(SQLERRM,1,180));
979 debug('l_stmt_num:'||l_stmt_num);
980
981 fnd_msg_pub.add;
982
983 FND_MSG_PUB.count_and_get
984 ( p_count => x_msg_count
985 , p_data => x_msg_data
986 );
987
988 l_conc_request := fnd_concurrent.set_completion_status('ERROR',
989 'EXCEPTION OTHERS in accrual_load '||SUBSTRB(SQLERRM,1,140)||' at statement '||l_stmt_num);
990
991 END accrual_load;
992
993 /*===========================================================================+
994 | |
995 | Procedure Name : Upgrade_old_data |
996 | |
997 | Purpose : This Procedure has all the necessary code to upgrade |
998 | old write off data into the new tables. |
999 | This procedure upgrades PO,AP and Inventory Write off |
1000 | transactions from the old PO_ACCRUAL_WRITE_OFFS_ALL |
1001 | table into the new CST_WRITE_OFFS and |
1002 | CST_WRITE_OFF_DETAILS table.This upgrade is done only |
1003 | for the very first run of the load program for the |
1004 | given OU.The old WIP write off data is not Upgraded. |
1005 | |
1006 | Called from : Start_accrual_load Procedure |
1007 | |
1008 | Parameters : |
1009 | IN : p_operating_unit IN NUMBER REQUIRED |
1010 | p_upg_from_date IN DATE REQUIRED |
1011 | p_upg_to_date IN DATE REQUIRED |
1012 | |
1013 | OUT : x_return_status OUT NOCOPY VARCHAR2 |
1014 | x_msg_count OUT NOCOPY NUMBER |
1015 | x_msg_data OUT NOCOPY VARCHAR2 |
1016 | |
1017 | NOTES : None
1018 | Modified multiple relevant queries to insert poh.vendor_id|
1019 | from PO_Headers to make this process immune to vendor |
1020 | mismatch between AP, PO and Write_Offs. Bug 7213170 |
1021 +===========================================================================*/
1022
1023 --BUG#7275286
1027 x_msg_count OUT NOCOPY NUMBER,
1024 PROCEDURE upgrade_old_data(p_operating_unit IN NUMBER,
1025 p_upg_from_date IN DATE,
1026 p_upg_to_date IN DATE,
1028 x_msg_data OUT NOCOPY VARCHAR2,
1029 x_return_status OUT NOCOPY VARCHAR2)
1030 IS
1031
1032 l_stmt_num NUMBER;
1033 l_old_ipv VARCHAR2(100);
1034 l_old_erv VARCHAR2(100);
1035 --{
1036 l_min_date DATE;
1037 l_max_date DATE;
1038 --}
1039 l_api_name CONSTANT VARCHAR2(30) := 'Upgrade_old_data';
1040 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
1041 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1042
1043 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1044 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
1045 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1046 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1047 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1048
1049
1050
1051 BEGIN
1052 debug('upgrade_old_data+');
1053 debug(' p_operating_unit : ' || p_operating_unit);
1054 debug(' p_upg_from_date : ' || TO_CHAR(p_upg_from_date,'YYYY/MM/DD HH24:MI:SS'));
1055 debug(' p_upg_to_date : ' || TO_CHAR(p_upg_to_date,'YYYY/MM/DD HH24:MI:SS'));
1056
1057
1058 x_return_status := fnd_api.g_ret_sts_success;
1059
1060 /* Upgrade PO data. We are joining on the sub ledger tables directly to get the entered amounts
1061 as data for pre 11.5.10 may not have the entered columns populated in the old write off table */
1062
1063 l_stmt_num := 10;
1064 debug(' l_stmt_num:'||l_stmt_num);
1065 debug(' Upgrading WO Receiving data ');
1066
1067 INSERT into cst_write_offs
1068 (write_off_id,
1069 transaction_date,
1070 accrual_account_id,
1071 offset_account_id,
1072 write_off_amount,
1073 entered_amount,
1074 currency_code,
1075 currency_conversion_type,
1076 currency_conversion_rate,
1077 currency_conversion_date,
1078 transaction_type_code,
1079 po_distribution_id,
1080 inventory_transaction_id,
1081 invoice_distribution_id,
1082 reversal_id,
1083 reason_id,
1084 comments,
1085 inventory_item_id,
1086 vendor_id,
1087 destination_type_code,
1088 operating_unit_id,
1089 last_update_date,
1090 last_updated_by,
1091 last_update_login,
1092 creation_date,
1093 created_by,
1094 request_id,
1095 program_application_id,
1096 program_id,
1097 program_update_date
1098 )
1099 SELECT DISTINCT pawo.write_off_id,
1100 pawo.WRITE_OFF_GL_DATE,
1101 pawo.accrual_account_id,
1102 NULL, -- offset_account_id
1103 -1 * pawo.TRANSACTION_AMOUNT, -- Accounted_amount
1104 -1 * NVL(pawo.entered_transaction_amount, sign(pawo.transaction_amount)* NVL(rrs.entered_dr,entered_cr)),
1105 NVL(pawo.currency_code,rrs.currency_code),
1106 NVL(pawo.currency_conversion_type,rrs.user_currency_conversion_type),
1107 NVL(pawo.currency_conversion_rate,rrs.currency_conversion_rate),
1108 NVL(pawo.currency_conversion_date,rrs.currency_conversion_date),
1109 pawo.WRITE_OFF_CODE ,
1110 pawo.PO_DISTRIBUTION_ID,
1111 NULL, -- INV_TRANSACTION_ID
1112 NULL, -- INVOICE_DISTRIBUTION_ID
1113 null,
1114 pawo.reason_id,
1115 pawo.comments,
1116 pawo.inventory_item_id,
1117 poh.vendor_id,
1118 nvl(pawo.destination_type_code,pod.destination_type_code),
1119 pawo.org_id,
1120 pawo.last_update_date,
1121 pawo.last_updated_by,
1122 pawo.last_update_login,
1123 pawo.creation_date,
1124 pawo.created_by,
1125 pawo.request_id,
1126 pawo.program_application_id,
1127 pawo.program_id,
1128 pawo.program_update_date
1129 FROM
1130 po_accrual_write_offs_all pawo,
1131 rcv_receiving_sub_ledger rrs,
1132 xla_distribution_links xld, --BUG#7275286
1133 rcv_transactions rt,
1134 po_headers_all poh
1135 ,cst_accrual_accounts ca --BUG#7528609
1136 ,PO_distributions_all pod
1137 WHERE pawo.org_id = p_operating_unit
1138 AND pawo.transaction_source_code = 'PO'
1139 AND pawo.po_transaction_id IS NOT NULL
1140 AND rrs.rcv_transaction_id = pawo.po_transaction_id
1141 AND rrs.rcv_transaction_id = rt.transaction_id
1142 --{BUG#7528609
1143 AND rrs.code_combination_id = ca.accrual_account_id
1144 AND ca.operating_unit_id = p_operating_unit
1145 --}
1146 AND poh.po_header_id = rt.po_header_id /* Bug 7312170. Vendor mismatch fix */
1147 AND rt.transaction_date BETWEEN p_upg_from_date AND p_upg_to_date
1148 AND pawo.accrual_account_id = rrs.code_combination_id
1149 AND ABS(NVL(rrs.accounted_dr,rrs.accounted_cr)) = ABS(pawo.transaction_amount)
1153 )
1150 --BUG#8666698: round precision limited to 20 in 11i po_accrual_reconciliation_temo
1151 AND ((ABS(ROUND(pawo.transaction_quantity,20)) = ABS(ROUND(rrs.source_doc_quantity,20)))
1152 OR pawo.transaction_quantity is NULL
1154 AND xld.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
1155 AND xld.source_distribution_id_num_1 = rrs.rcv_sub_ledger_id
1156 AND xld.application_id = 707
1157 and pod.po_distribution_id =rrs.reference3
1158 and rrs.reference3 = pawo.po_distribution_id;
1159 debug(' Done upgrading Receiving data');
1160
1161
1162 l_stmt_num := 20;
1163 debug(' l_stmt_num :'||l_stmt_num);
1164
1165 /* Upgrade old AP transactions */
1166
1167 Select plu.displayed_field
1168 into l_old_ipv
1169 FROM po_lookup_codes plu
1170 WHERE plu.lookup_type = 'ACCRUAL TYPE'
1171 AND plu.lookup_code = 'AP INVOICE PRICE VAR';
1172
1173 l_stmt_num := 30;
1174 debug(' l_stmt_num :'||l_stmt_num);
1175 debug(' l_old_ipv :'||l_old_ipv);
1176
1177 Select plu.displayed_field
1178 into l_old_erv
1179 FROM po_lookup_codes plu
1180 WHERE plu.lookup_type = 'ACCRUAL TYPE'
1181 AND plu.lookup_code = 'AP EXCHANGE RATE VAR';
1182
1183 l_stmt_num := 40;
1184 debug(' l_stmt_num :'||l_stmt_num);
1185 debug(' l_old_erv :'||l_old_erv);
1186 debug(' Upgrading WO miscellenaous AP invoice 11i');
1187
1188 INSERT into cst_write_offs
1189 (write_off_id,
1190 transaction_date,
1191 accrual_account_id,
1192 offset_account_id,
1193 write_off_amount,
1194 entered_amount,
1195 currency_code,
1196 currency_conversion_type,
1197 currency_conversion_rate,
1198 currency_conversion_date,
1199 transaction_type_code,
1200 po_distribution_id,
1201 inventory_transaction_id,
1202 invoice_distribution_id,
1203 reversal_id,
1204 reason_id,
1205 comments,
1206 inventory_item_id,
1207 vendor_id,
1208 destination_type_code,
1209 operating_unit_id,
1210 last_update_date,
1211 last_updated_by,
1212 last_update_login,
1213 creation_date,
1214 created_by,
1215 request_id,
1216 program_application_id,
1217 program_id,
1218 program_update_date
1219 )
1220 SELECT pawo.write_off_id,
1221 pawo.write_off_gl_date,
1222 pawo.accrual_account_id,
1223 null,
1224 -1 * pawo.transaction_amount, /* Bug 6757017: In R12, the writeoff amount will have its sign reversed */
1225 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) / /* Bug 6757017: In R12, the writeoff amount will have its sign reversed */
1226 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1227 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1228 aal.currency_code,
1229 aal.currency_conversion_type,
1230 aal.currency_conversion_rate,
1231 aal.currency_conversion_date,
1232 pawo.write_off_code,
1233 pawo.po_distribution_id,
1234 NULL,
1235 aida.invoice_distribution_id,
1236 null,
1237 pawo.reason_id,
1238 pawo.comments,
1239 pawo.inventory_item_id,
1240 pawo.vendor_id,
1241 pawo.destination_type_code,
1242 pawo.org_id,
1243 pawo.last_update_date,
1244 pawo.last_updated_by,
1245 pawo.last_update_login,
1246 pawo.creation_date,
1247 pawo.created_by,
1248 pawo.request_id,
1249 pawo.program_application_id,
1250 pawo.program_id,
1251 pawo.program_update_date
1252 FROM
1253 po_accrual_write_offs_all pawo,
1254 ap_invoice_distributions_all aida,
1255 financials_system_params_all fsp,
1256 gl_sets_of_books gsob,
1257 fnd_currencies fc,
1258 ap_ae_lines_all aal,
1259 xla_distribution_links xld
1260 ,cst_accrual_accounts ca
1261 WHERE pawo.org_id = p_operating_unit
1262 AND pawo.po_distribution_id IS NULL -- Misc Invoices
1263 AND pawo.transaction_source_code = 'AP'
1264 AND pawo.invoice_id IS NOT NULL
1265 AND aida.invoice_id = pawo.invoice_id
1266 AND aida.accounting_date BETWEEN p_upg_from_date AND p_upg_to_date
1267 AND pawo.line_match_order IS NOT NULL
1268 AND aal.ae_line_id = pawo.line_match_order
1269 --{BUG#7528609
1270 AND aal.code_combination_id = ca.accrual_account_id
1271 AND ca.operating_unit_id = p_operating_unit
1272 --}
1273 --AND aida.invoice_distribution_id = aal.source_id Bug 12956713
1274 AND aida.old_distribution_id = aal.source_id
1275 AND fsp.org_id = pawo.org_id
1276 AND fsp.set_of_books_id = gsob.set_of_books_id
1277 AND fc.currency_code = gsob.currency_code
1278 AND xld.source_distribution_id_num_1 = aida.invoice_distribution_id
1279 AND xld.source_distribution_type = 'AP_INV_DIST'
1280 AND xld.application_id = 200
1281 GROUP BY pawo.write_off_id,
1282 pawo.write_off_gl_date,
1283 pawo.accrual_account_id,
1284 -1 * pawo.transaction_amount,
1288 aal.currency_code,
1285 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) /
1286 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1287 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1289 aal.currency_conversion_type,
1290 aal.currency_conversion_rate,
1291 aal.currency_conversion_date,
1292 pawo.write_off_code,
1293 pawo.po_distribution_id,
1294 aida.invoice_distribution_id,
1295 pawo.reason_id,
1296 pawo.comments,
1297 pawo.inventory_item_id,
1298 pawo.vendor_id,
1299 pawo.destination_type_code,
1300 pawo.org_id,
1301 pawo.last_update_date,
1302 pawo.last_updated_by,
1303 pawo.last_update_login,
1304 pawo.creation_date,
1305 pawo.created_by,
1306 pawo.request_id,
1307 pawo.program_application_id,
1308 pawo.program_id,
1309 pawo.program_update_date;
1310
1311
1312 debug(' Done with upgrading miscellenaous AP invoice 11i');
1313
1314
1315 /* Bug 6757017: The following query will upgrade write off date from 11.0 releases
1316 where the line_match_order would be null */
1317
1318 l_stmt_num := 45;
1319 debug(' l_stmt_num :'||l_stmt_num);
1320 debug(' Upgrading WO miscellenaous AP invoice 11.0');
1321
1322 INSERT into cst_write_offs
1323 (write_off_id,
1324 transaction_date,
1325 accrual_account_id,
1326 offset_account_id,
1327 write_off_amount,
1328 entered_amount,
1329 currency_code,
1330 currency_conversion_type,
1331 currency_conversion_rate,
1332 currency_conversion_date,
1333 transaction_type_code,
1334 po_distribution_id,
1335 inventory_transaction_id,
1336 invoice_distribution_id,
1337 reversal_id,
1338 reason_id,
1339 comments,
1340 inventory_item_id,
1341 vendor_id,
1342 destination_type_code,
1343 operating_unit_id,
1344 last_update_date,
1345 last_updated_by,
1346 last_update_login,
1347 creation_date,
1348 created_by,
1349 request_id,
1350 program_application_id,
1351 program_id,
1352 program_update_date
1353 )
1354 SELECT --po_accrual_write_offs_s.nextval, --BUG#7950123
1355 pawo.write_off_id,
1356 pawo.write_off_gl_date,
1357 aal.code_combination_id, /* pawo.accrual_account_id,*/
1358 null,
1359 -1 * pawo.transaction_amount,
1360 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) /
1361 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1362 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1363 aal.currency_code,
1364 aal.currency_conversion_type,
1365 aal.currency_conversion_rate,
1366 aal.currency_conversion_date,
1367 pawo.write_off_code,
1368 pawo.po_distribution_id,
1369 NULL,
1370 aida.invoice_distribution_id,
1371 null,
1372 pawo.reason_id,
1373 pawo.comments,
1374 pawo.inventory_item_id,
1375 pawo.vendor_id,
1376 pawo.destination_type_code,
1377 pawo.org_id,
1378 pawo.last_update_date,
1379 pawo.last_updated_by,
1380 pawo.last_update_login,
1381 pawo.creation_date,
1382 pawo.created_by,
1383 pawo.request_id,
1384 pawo.program_application_id,
1385 pawo.program_id,
1386 pawo.program_update_date
1387 FROM
1388 po_accrual_write_offs_all pawo,
1389 ap_invoice_distributions_all aida,
1390 financials_system_params_all fsp,
1391 gl_sets_of_books gsob,
1392 fnd_currencies fc,
1393 ap_ae_lines_all aal,
1394 cst_accrual_accounts caa,
1395 xla_distribution_links xld
1396 WHERE pawo.org_id = p_operating_unit
1397 AND pawo.po_distribution_id IS NULL -- Misc Invoices
1398 AND pawo.transaction_source_code = 'AP'
1399 AND pawo.invoice_id IS NOT NULL
1400 AND aida.invoice_id = pawo.invoice_id
1401 --AND aida.invoice_line_number = pawo.invoice_line_num
1402 AND aida.OLD_DIST_LINE_NUMBER = pawo.invoice_line_num
1403 AND aida.accounting_date BETWEEN p_upg_from_date AND p_upg_to_date
1404 AND pawo.line_match_order IS NULL
1405 AND aal.code_combination_id = caa.accrual_account_id
1406 AND caa.operating_unit_id = p_operating_unit
1407 AND aida.invoice_distribution_id = aal.source_id
1408 AND aal.source_table = 'AP_INVOICE_DISTRIBUTIONS'
1409 AND fsp.org_id = pawo.org_id
1410 AND fsp.set_of_books_id = gsob.set_of_books_id
1411 AND fc.currency_code = gsob.currency_code
1412 AND xld.source_distribution_id_num_1 = aida.invoice_distribution_id
1413 AND xld.source_distribution_type = 'AP_INV_DIST'
1414 AND xld.application_id = 200
1415 --{BUG#7950123
1416 GROUP BY pawo.write_off_id,
1417 pawo.write_off_gl_date,
1418 aal.code_combination_id,
1419 -1 * pawo.transaction_amount,
1420 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) /
1424 aal.currency_conversion_type,
1421 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1422 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1423 aal.currency_code,
1425 aal.currency_conversion_rate,
1426 aal.currency_conversion_date,
1427 pawo.write_off_code,
1428 pawo.po_distribution_id,
1429 aida.invoice_distribution_id,
1430 pawo.reason_id,
1431 pawo.comments,
1432 pawo.inventory_item_id,
1433 pawo.vendor_id,
1434 pawo.destination_type_code,
1435 pawo.org_id,
1436 pawo.last_update_date,
1437 pawo.last_updated_by,
1438 pawo.last_update_login,
1439 pawo.creation_date,
1440 pawo.created_by,
1441 pawo.request_id,
1442 pawo.program_application_id,
1443 pawo.program_id,
1444 pawo.program_update_date;
1445 --}
1446
1447 debug(' Done with upgrading miscellenaous AP Invoice 11.0');
1448
1449
1450 l_stmt_num := 50;
1451 debug(' l_stmt_num :'||l_stmt_num);
1452 debug(' Upgrading WO regular AP INVOICE data 11i');
1453
1454 INSERT into cst_write_offs
1455 (write_off_id,
1456 transaction_date,
1457 accrual_account_id,
1458 offset_account_id,
1459 write_off_amount,
1460 entered_amount,
1461 currency_code,
1462 currency_conversion_type,
1463 currency_conversion_rate,
1464 currency_conversion_date,
1465 transaction_type_code,
1466 po_distribution_id,
1467 inventory_transaction_id,
1468 invoice_distribution_id,
1469 reversal_id,
1470 reason_id,
1471 comments,
1472 inventory_item_id,
1473 vendor_id,
1474 destination_type_code,
1475 operating_unit_id,
1476 last_update_date,
1477 last_updated_by,
1478 last_update_login,
1479 creation_date,
1480 created_by,
1481 request_id,
1482 program_application_id,
1483 program_id,
1484 program_update_date
1485 )
1486 SELECT pawo.write_off_id,
1487 pawo.write_off_gl_date,
1488 pawo.accrual_account_id,
1489 null,
1490 -1 * pawo.transaction_amount, /* Bug 6757017: In R12, the writeoff amount will have its sign reversed */
1491 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) / /* Bug 6757017: In R12, the writeoff amount will have its sign reversed */
1492 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1493 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1494 aal.currency_code,
1495 aal.currency_conversion_type,
1496 aal.currency_conversion_rate,
1497 aal.currency_conversion_date,
1498 pawo.write_off_code,
1499 pawo.po_distribution_id,
1500 NULL,
1501 Decode(pawo.accrual_code,
1502 l_old_ipv,aida.invoice_distribution_id,
1503 l_old_erv,aida.invoice_distribution_id,
1504 decode(pod.po_release_id,
1505 NULL,Decode(NVL(poh.consigned_consumption_flag,'N'),
1506 'Y',aida.invoice_distribution_id,
1507 NULL
1508 ),
1509 Decode(NVL(pra.consigned_consumption_flag,'N'),
1510 'Y',aida.invoice_distribution_id,
1511 NULL
1512 )
1513 )
1514 ),
1515 null,
1516 pawo.reason_id,
1517 pawo.comments,
1518 pawo.inventory_item_id,
1519 poh.vendor_id,
1520 nvl(pawo.destination_type_code,pod.destination_type_code),
1521 pawo.org_id,
1522 pawo.last_update_date,
1523 pawo.last_updated_by,
1524 pawo.last_update_login,
1525 pawo.creation_date,
1526 pawo.created_by,
1527 pawo.request_id,
1528 pawo.program_application_id,
1529 pawo.program_id,
1530 pawo.program_update_date
1531 FROM
1532 po_accrual_write_offs_all pawo,
1533 po_distributions_all pod,
1534 po_releases_all pra,
1535 po_headers_all poh,
1536 ap_invoice_distributions_all aida,
1537 financials_system_params_all fsp,
1538 gl_sets_of_books gsob,
1539 fnd_currencies fc,
1540 ap_ae_lines aal,
1541 cst_accrual_accounts caa,
1542 xla_distribution_links xld,
1543 xla_ae_lines xal
1544 WHERE pawo.org_id = p_operating_unit
1545 AND pawo.po_distribution_id IS NOT NULL -- Reg Invoices and consigned
1546 AND pod.po_distribution_id = pawo.po_distribution_id
1547 AND pra.po_release_id(+) = pod.po_release_id
1548 AND poh.po_header_id = pod.po_header_id
1549 AND pawo.transaction_source_code = 'AP'
1550 AND pawo.invoice_id IS NOT NULL
1551 AND aida.invoice_id = pawo.invoice_id
1552 AND pawo.line_match_order IS NOT NULL
1553 AND aal.ae_line_id = pawo.line_match_order
1554 --AND aida.invoice_distribution_id = aal.source_id Bug 12956713
1555 AND aida.old_distribution_id = aal.source_id
1559 AND fc.currency_code = gsob.currency_code
1556 AND aida.accounting_date BETWEEN p_upg_from_date AND p_upg_to_date
1557 AND fsp.org_id = pawo.org_id
1558 AND fsp.set_of_books_id = gsob.set_of_books_id
1560 AND xld.source_distribution_id_num_1 = aida.invoice_distribution_id
1561 AND xld.source_distribution_type = 'AP_INV_DIST'
1562 AND xld.application_id = 200
1563 AND xld.ae_header_id = xal.ae_header_id
1564 AND xld.ae_line_num = xal.ae_line_num
1565 AND xal.application_id = 200
1566 AND caa.operating_unit_id = p_operating_unit
1567 AND xal.code_combination_id = caa.accrual_account_id
1568 -- Bug 7528609. Added the Group by clause to prevent unique constraint error
1569 -- This could cause perf issue but is the best fix option available.
1570 GROUP BY pawo.write_off_id,
1571 pawo.write_off_gl_date,
1572 pawo.accrual_account_id,
1573 -1 * pawo.transaction_amount,
1574 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) /
1575 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1576 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1577 aal.currency_code,
1578 aal.currency_conversion_type,
1579 aal.currency_conversion_rate,
1580 aal.currency_conversion_date,
1581 pawo.write_off_code,
1582 pawo.po_distribution_id,
1583 Decode(pawo.accrual_code,
1584 l_old_ipv,aida.invoice_distribution_id,
1585 l_old_erv,aida.invoice_distribution_id,
1586 decode(pod.po_release_id,
1587 NULL,Decode(NVL(poh.consigned_consumption_flag,'N'),
1588 'Y',aida.invoice_distribution_id,
1589 NULL
1590 ),
1591 Decode(NVL(pra.consigned_consumption_flag,'N'),
1592 'Y',aida.invoice_distribution_id,
1593 NULL
1594 )
1595 )
1596 ),
1597 pawo.reason_id,
1598 pawo.comments,
1599 pawo.inventory_item_id,
1600 poh.vendor_id,
1601 nvl(pawo.destination_type_code,pod.destination_type_code),
1602 pawo.org_id,
1603 pawo.last_update_date,
1604 pawo.last_updated_by,
1605 pawo.last_update_login,
1606 pawo.creation_date,
1607 pawo.created_by,
1608 pawo.request_id,
1609 pawo.program_application_id,
1610 pawo.program_id,
1611 pawo.program_update_date;
1612
1613 debug(' Done with regular AP INVOICE data 11i');
1614
1615 l_stmt_num := 55;
1616 debug(' l_stmt_num :'||l_stmt_num);
1617 debug(' Upgrading WO AP INVOICE data 11.0');
1618
1619 /* Bug 6757017: The following query will upgrade write off date from 11.0 releases
1620 where the line_match_order wouild be null */
1621 INSERT into cst_write_offs
1622 (write_off_id,
1623 transaction_date,
1624 accrual_account_id,
1625 offset_account_id,
1626 write_off_amount,
1627 entered_amount,
1628 currency_code,
1629 currency_conversion_type,
1630 currency_conversion_rate,
1631 currency_conversion_date,
1632 transaction_type_code,
1633 po_distribution_id,
1634 inventory_transaction_id,
1635 invoice_distribution_id,
1636 reversal_id,
1637 reason_id,
1638 comments,
1639 inventory_item_id,
1640 vendor_id,
1641 destination_type_code,
1642 operating_unit_id,
1643 last_update_date,
1644 last_updated_by,
1645 last_update_login,
1646 creation_date,
1647 created_by,
1648 request_id,
1649 program_application_id,
1650 program_id,
1651 program_update_date
1652 )
1653 SELECT --po_accrual_write_offs_s.nextval, --BUG#7950123
1654 pawo.write_off_id,
1655 pawo.write_off_gl_date,
1656 aal.code_combination_id, /*pawo.accrual_account_id,*/
1657 null,
1658 -1 * pawo.transaction_amount,
1659 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) /
1660 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1661 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1662 aal.currency_code,
1663 aal.currency_conversion_type,
1664 aal.currency_conversion_rate,
1665 aal.currency_conversion_date,
1666 pawo.write_off_code,
1667 pawo.po_distribution_id,
1668 NULL,
1669 Decode(aal.ae_line_type_code,
1670 'IPV',aida.invoice_distribution_id,
1671 'ERV',aida.invoice_distribution_id,
1672 decode(pod.po_release_id,
1673 NULL,Decode(NVL(poh.consigned_consumption_flag,'N'),
1674 'Y',aida.invoice_distribution_id,
1675 NULL
1676 ),
1677 Decode(NVL(pra.consigned_consumption_flag,'N'),
1678 'Y',aida.invoice_distribution_id,
1679 NULL
1680 )
1681 )
1682 ),
1683 null,
1684 pawo.reason_id,
1688 nvl(pawo.destination_type_code,pod.destination_type_code),
1685 pawo.comments,
1686 pawo.inventory_item_id,
1687 poh.vendor_id,
1689 pawo.org_id,
1690 pawo.last_update_date,
1691 pawo.last_updated_by,
1692 pawo.last_update_login,
1693 pawo.creation_date,
1694 pawo.created_by,
1695 pawo.request_id,
1696 pawo.program_application_id,
1697 pawo.program_id,
1698 pawo.program_update_date
1699 FROM
1700 po_accrual_write_offs_all pawo,
1701 po_distributions_all pod,
1702 po_releases_all pra,
1703 po_headers_all poh,
1704 ap_invoice_distributions_all aida,
1705 financials_system_params_all fsp,
1706 gl_sets_of_books gsob,
1707 fnd_currencies fc,
1708 ap_ae_lines aal,
1709 cst_accrual_accounts caa,
1710 xla_distribution_links xld
1711 WHERE pawo.org_id = p_operating_unit
1712 AND pawo.po_distribution_id IS NOT NULL -- Reg Invoices and consigned
1713 AND pod.po_distribution_id = pawo.po_distribution_id
1714 AND pra.po_release_id(+) = pod.po_release_id
1715 AND poh.po_header_id = pod.po_header_id
1716 AND pawo.transaction_source_code = 'AP'
1717 AND pawo.invoice_id IS NOT NULL
1718 AND aida.invoice_id = pawo.invoice_id
1719 --AND aida.invoice_line_number = pawo.invoice_line_num
1720 AND aida.OLD_DIST_LINE_NUMBER = pawo.invoice_line_num
1721 AND aida.accounting_date BETWEEN p_upg_from_date AND p_upg_to_date
1722 AND pawo.line_match_order IS NULL
1723 AND aal.code_combination_id = caa.accrual_account_id
1724 AND caa.operating_unit_id = p_operating_unit
1725 AND aida.invoice_distribution_id = aal.source_id
1726 AND aal.source_table = 'AP_INVOICE_DISTRIBUTIONS'
1727 AND fsp.org_id = pawo.org_id
1728 AND fsp.set_of_books_id = gsob.set_of_books_id
1729 AND fc.currency_code = gsob.currency_code
1730 AND xld.source_distribution_id_num_1 = aida.invoice_distribution_id
1731 AND xld.source_distribution_type = 'AP_INV_DIST'
1732 AND xld.application_id = 200
1733 --{BUG#7950123
1734 GROUP BY pawo.write_off_id,
1735 pawo.write_off_gl_date,
1736 aal.code_combination_id,
1737 -1 * pawo.transaction_amount,
1738 -1 * Round((NVL(aal.entered_dr,0)- NVL(aal.entered_cr,0)) /
1739 NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0)))
1740 ) * NVL(fc.minimum_accountable_unit,power(10,NVL(-fc.precision,0))),
1741 aal.currency_code,
1742 aal.currency_conversion_type,
1743 aal.currency_conversion_rate,
1744 aal.currency_conversion_date,
1745 pawo.write_off_code,
1746 pawo.po_distribution_id,
1747 Decode(aal.ae_line_type_code,
1748 'IPV',aida.invoice_distribution_id,
1749 'ERV',aida.invoice_distribution_id,
1750 decode(pod.po_release_id,
1751 NULL,Decode(NVL(poh.consigned_consumption_flag,'N'),
1752 'Y',aida.invoice_distribution_id,
1753 NULL
1754 ),
1755 Decode(NVL(pra.consigned_consumption_flag,'N'),
1756 'Y',aida.invoice_distribution_id,
1757 NULL
1758 )
1759 )
1760 ),
1761 pawo.reason_id,
1762 pawo.comments,
1763 pawo.inventory_item_id,
1764 poh.vendor_id,
1765 nvl(pawo.destination_type_code,pod.destination_type_code),
1766 pawo.org_id,
1767 pawo.last_update_date,
1768 pawo.last_updated_by,
1769 pawo.last_update_login,
1770 pawo.creation_date,
1771 pawo.created_by,
1772 pawo.request_id,
1773 pawo.program_application_id,
1774 pawo.program_id,
1775 pawo.program_update_date;
1776
1777
1778 debug(' Done with regular AP INVOICE data 11.0');
1779 l_stmt_num := 60;
1780 debug(' l_stmt_num :'||l_stmt_num);
1781 debug(' Upgrading WO miscellenaous INV data');
1782
1783 /* Upgrade MTA data */
1784
1785
1786 /* Now Insert these values into the new write off table. For MISC INV txns, we are not calculating the
1787 entered amount for pre 11.5.10 txns as it is not technically feasible because of the way
1788 we store the currency info in MTA for the txns.So we will be inserting NULL as entered amounts
1789 for pre 11.5.10 txns */
1790
1791 INSERT into cst_write_offs
1792 (write_off_id,
1793 transaction_date,
1794 accrual_account_id,
1795 offset_account_id,
1796 write_off_amount,
1797 entered_amount,
1798 currency_code,
1799 currency_conversion_type,
1800 currency_conversion_rate,
1801 currency_conversion_date,
1802 transaction_type_code,
1803 po_distribution_id,
1804 inventory_transaction_id,
1805 reason_id,
1806 comments,
1807 inventory_item_id,
1808 vendor_id,
1809 destination_type_code,
1813 last_update_login,
1810 operating_unit_id,
1811 last_update_date,
1812 last_updated_by,
1814 creation_date,
1815 created_by,
1816 request_id,
1817 program_application_id,
1818 program_id,
1819 program_update_date
1820 )
1821 SELECT pawo.write_off_id,
1822 pawo.write_off_gl_date,
1823 pawo.accrual_account_id,
1824 NULL,
1825 -1 * pawo.transaction_amount,
1826 -1 * pawo.entered_transaction_amount,
1827 pawo.currency_code,
1828 pawo.currency_conversion_type,
1829 pawo.currency_conversion_rate,
1830 pawo.currency_conversion_date,
1831 pawo.write_off_code,
1832 pawo.po_distribution_id,
1833 pawo.inv_transaction_id,
1834 pawo.reason_id,
1835 pawo.comments,
1836 pawo.inventory_item_id,
1837 pawo.vendor_id,
1838 pawo.destination_type_code,
1839 pawo.org_id,
1840 pawo.last_update_date,
1841 pawo.last_updated_by,
1842 pawo.last_update_login,
1843 pawo.creation_date,
1844 pawo.created_by,
1845 pawo.request_id,
1846 pawo.program_application_id,
1847 pawo.program_id,
1848 pawo.program_update_date
1849 FROM po_accrual_write_offs_all pawo,
1850 mtl_transaction_accounts mta,
1851 xla_distribution_links xld
1852 ,cst_accrual_accounts ca --BUG#7528609
1853 WHERE pawo.transaction_source_code = 'INV'
1854 AND pawo.org_id = p_operating_unit
1855 AND pawo.inv_transaction_id = mta.transaction_id
1856 --{BUG#7528609
1857 AND mta.reference_account = ca.accrual_account_id
1858 AND ca.operating_unit_id = p_operating_unit
1859 --}
1860 AND mta.transaction_date BETWEEN p_upg_from_date AND p_upg_to_date
1861 AND xld.source_distribution_id_num_1 = mta.inv_sub_ledger_id
1862 AND xld.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
1863 AND xld.application_id = 707
1864 GROUP BY pawo.write_off_id,
1865 pawo.write_off_gl_date,
1866 pawo.accrual_account_id,
1867 pawo.transaction_amount,
1868 pawo.entered_transaction_amount,
1869 pawo.currency_code,
1870 pawo.currency_conversion_type,
1871 pawo.currency_conversion_rate,
1872 pawo.currency_conversion_date,
1873 pawo.write_off_code,
1874 pawo.po_distribution_id,
1875 pawo.inv_transaction_id,
1876 pawo.reason_id,
1877 pawo.comments,
1878 pawo.inventory_item_id,
1879 pawo.vendor_id,
1880 pawo.destination_type_code,
1881 pawo.org_id,
1882 pawo.last_update_date,
1883 pawo.last_updated_by,
1884 pawo.last_update_login,
1885 pawo.creation_date,
1886 pawo.created_by,
1887 pawo.request_id,
1888 pawo.program_application_id,
1889 pawo.program_id,
1890 pawo.program_update_date;
1891
1892 debug(' Done upgrading Inventory Data' );
1893
1894
1895 l_stmt_num := 70;
1896 debug(' l_stmt_num :'||l_stmt_num);
1897 debug(' Upgrading miscellenaous INV WO detail data');
1898
1899
1900 /* Now insert into the cst_write_off details table */
1901 Insert into cst_write_off_details
1902 (
1903 write_off_id,
1904 rcv_transaction_id,
1905 inventory_transaction_id,
1906 invoice_distribution_id,
1907 transaction_type_code,
1908 transaction_date,
1909 amount,
1910 entered_amount,
1911 quantity,
1912 currency_code,
1913 currency_conversion_type,
1914 currency_conversion_rate,
1915 currency_conversion_date,
1916 inventory_organization_id,
1917 operating_unit_id,
1918 last_update_date,
1919 last_updated_by,
1920 last_update_login,
1921 creation_date,
1922 created_by,
1923 request_id,
1924 program_application_id,
1925 program_id,
1926 program_update_date
1927 --{Need original XLA accounting entries
1928 , ae_header_id
1929 , ae_line_num
1930 --}
1931 )
1932 Select cwo.write_off_id,
1933 pawo.po_transaction_id,
1934 cwo.inventory_transaction_id,
1935 cwo.invoice_distribution_id,
1936 to_char(pawo.inv_transaction_type_id),
1937 cwo.transaction_date,
1938 DECODE(cwo.transaction_type_code,
1939 'REVERSE WRITE OFF', cwo.write_off_amount,-1 * cwo.write_off_amount),
1940 DECODE(cwo.transaction_type_code,
1941 'REVERSE WRITE OFF', cwo.entered_amount, -1 * cwo.entered_amount),
1942 pawo.transaction_quantity,
1943 cwo.currency_code,
1944 cwo.currency_conversion_type,
1945 cwo.currency_conversion_rate,
1946 cwo.currency_conversion_date,
1947 pawo.transaction_organization_id,
1948 cwo.operating_unit_id,
1949 cwo.last_update_date,
1950 cwo.last_updated_by,
1954 cwo.request_id,
1951 cwo.last_update_login,
1952 cwo.creation_date,
1953 cwo.created_by,
1955 cwo.program_application_id,
1956 cwo.program_id,
1957 cwo.program_update_date
1958 --{Need the original XLA entries
1959 ,xld.ae_header_id
1960 ,xld.ae_line_num
1961 --}
1962 FROM cst_write_offs cwo,
1963 po_accrual_write_offs_all pawo,
1964 mtl_transaction_accounts mta,
1965 xla_distribution_links xld,
1966 cst_accrual_accounts ca --BUG7528609
1967 WHERE pawo.org_id = p_operating_unit
1968 AND pawo.transaction_source_code ='INV'
1969 AND cwo.write_off_id = pawo.write_off_id
1970 AND pawo.inv_transaction_id = mta.transaction_id
1971 --{BUG#7528609
1972 AND ca.operating_unit_id = p_operating_unit
1973 AND mta.reference_account = ca.accrual_account_id
1974 --}
1975 AND mta.transaction_date BETWEEN p_upg_from_date AND p_upg_to_date
1976 AND xld.source_distribution_id_num_1 = mta.inv_sub_ledger_id
1977 AND xld.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'
1978 AND xld.application_id = 707;
1979
1980
1981 debug(' Done Updating the write off details for INV');
1982
1983 l_stmt_num := 75;
1984 debug(' l_stmt_num :'||l_stmt_num);
1985 debug(' Updating the WO details for PO RCV');
1986
1987 /* Insert details for PO data */
1988 Insert into cst_write_off_details
1989 (
1990 write_off_id,
1991 rcv_transaction_id,
1992 inventory_transaction_id,
1993 invoice_distribution_id,
1994 transaction_type_code,
1995 transaction_date,
1996 amount,
1997 entered_amount,
1998 quantity,
1999 currency_code,
2000 currency_conversion_type,
2001 currency_conversion_rate,
2002 currency_conversion_date,
2003 inventory_organization_id,
2004 operating_unit_id,
2005 last_update_date,
2006 last_updated_by,
2007 last_update_login,
2008 creation_date,
2009 created_by,
2010 request_id,
2011 program_application_id,
2012 program_id,
2013 program_update_date
2014 --{Need original XLA accounting entries
2015 , ae_header_id
2016 , ae_line_num
2017 --}
2018 )
2019 Select cwo.write_off_id,
2020 pawo.po_transaction_id,
2021 cwo.inventory_transaction_id,
2022 cwo.invoice_distribution_id,
2023 plc.lookup_code,
2024 cwo.transaction_date,
2025 DECODE(cwo.transaction_type_code,
2026 'REVERSE WRITE OFF', cwo.write_off_amount,-1 * cwo.write_off_amount),
2027 DECODE(cwo.transaction_type_code,
2028 'REVERSE WRITE OFF', cwo.entered_amount, -1 * cwo.entered_amount),
2029 pawo.transaction_quantity,
2030 cwo.currency_code,
2031 cwo.currency_conversion_type,
2032 cwo.currency_conversion_rate,
2033 cwo.currency_conversion_date,
2034 pawo.transaction_organization_id,
2035 cwo.operating_unit_id,
2036 cwo.last_update_date,
2037 cwo.last_updated_by,
2038 cwo.last_update_login,
2039 cwo.creation_date,
2040 cwo.created_by,
2041 cwo.request_id,
2042 cwo.program_application_id,
2043 cwo.program_id,
2044 cwo.program_update_date
2045 --{Need the original XLA entries
2046 ,xld.ae_header_id
2047 ,xld.ae_line_num
2048 --}
2049 FROM cst_write_offs cwo,
2050 po_accrual_write_offs_all pawo,
2051 po_lookup_codes plc,
2052 rcv_receiving_sub_ledger rrs,
2053 rcv_transactions rt,
2054 xla_distribution_links xld,
2055 cst_accrual_accounts ca --BUG#7528609
2056 WHERE pawo.org_id = p_operating_unit
2057 AND pawo.transaction_source_code = 'PO'
2058 AND cwo.write_off_id = pawo.write_off_id
2059 AND plc.displayed_field = pawo.accrual_code
2060 AND plc.lookup_type = 'RCV TRANSACTION TYPE'
2061 AND rrs.rcv_transaction_id = pawo.po_transaction_id
2062 AND rrs.rcv_transaction_id = rt.transaction_id
2063 --{BUG#7528609
2064 AND ca.operating_unit_id = p_operating_unit
2065 AND rrs.code_combination_id = ca.accrual_account_id
2066 --}
2067 AND rt.transaction_date BETWEEN p_upg_from_date AND p_upg_to_date
2068 AND xld.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
2069 AND xld.source_distribution_id_num_1 = rrs.rcv_sub_ledger_id
2070 AND cwo.po_distribution_id = rrs.reference3 --BUG#10085698
2071 AND xld.application_id = 707;
2072
2073 debug(' Done Updating the write off details for PO RCV' );
2074
2075
2076 l_stmt_num := 80;
2077 debug(' l_stmt_num :'||l_stmt_num);
2078 debug(' Updating WO details for AP Invoice');
2079
2080
2081 Insert into cst_write_off_details
2082 (
2083 write_off_id,
2084 rcv_transaction_id,
2085 inventory_transaction_id,
2086 invoice_distribution_id,
2087 transaction_type_code,
2088 transaction_date,
2089 amount,
2090 entered_amount,
2091 quantity,
2092 currency_code,
2093 currency_conversion_type,
2094 currency_conversion_rate,
2098 last_update_date,
2095 currency_conversion_date,
2096 inventory_organization_id,
2097 operating_unit_id,
2099 last_updated_by,
2100 last_update_login,
2101 creation_date,
2102 created_by,
2103 request_id,
2104 program_application_id,
2105 program_id,
2106 program_update_date
2107 --{Need original XLA accounting entries
2108 , ae_header_id
2109 , ae_line_num
2110 --}
2111 )
2112 SELECT cwo.write_off_id,
2113 pawo.po_transaction_id,
2114 cwo.inventory_transaction_id,
2115 aida.invoice_distribution_id,
2116 plc.lookup_code,
2117 cwo.transaction_date,
2118 DECODE(cwo.transaction_type_code, 'REVERSE WRITE OFF',
2119 cwo.write_off_amount,-1 * cwo.write_off_amount),
2120 DECODE(cwo.transaction_type_code, 'REVERSE WRITE OFF',
2121 cwo.entered_amount, -1 * cwo.entered_amount),
2122 pawo.transaction_quantity,
2123 cwo.currency_code,
2124 cwo.currency_conversion_type,
2125 cwo.currency_conversion_rate,
2126 cwo.currency_conversion_date,
2127 pawo.transaction_organization_id,
2128 cwo.operating_unit_id,
2129 cwo.last_update_date,
2130 cwo.last_updated_by,
2131 cwo.last_update_login,
2132 cwo.creation_date,
2133 cwo.created_by,
2134 cwo.request_id,
2135 cwo.program_application_id,
2136 cwo.program_id,
2137 cwo.program_update_date
2138 --{ Need the original XLA entries
2139 ,MAX(xld.ae_header_id)
2140 ,MAX(xld.ae_line_num)
2141 --}
2142 FROM cst_write_offs cwo,
2143 po_accrual_write_offs_all pawo,
2144 po_lookup_codes plc,
2145 ap_invoice_distributions_all aida,
2146 ap_ae_lines_all aal,
2147 cst_accrual_accounts ca, --BUG#7528609
2148 xla_distribution_links xld
2149 ,xla_ae_lines xlal --XLD AP <=> n XLA AE line AP with different GL Accounts
2150 ,financials_system_params_all fsp --BUG#13869348
2151 WHERE pawo.org_id = p_operating_unit
2152 AND fsp.org_id = p_operating_unit
2153 AND pawo.transaction_source_code = 'AP'
2154 AND cwo.write_off_id = pawo.write_off_id
2155 AND plc.lookup_type = 'ACCRUAL TYPE'
2156 AND plc.displayed_field = pawo.accrual_code
2157 AND pawo.invoice_id IS NOT NULL
2158 AND aida.invoice_id = pawo.invoice_id
2159 AND aida.accounting_date BETWEEN p_upg_from_date AND p_upg_to_date
2160 --{BUG#8533705
2161 -- AND aida.distribution_line_number = pawo.invoice_line_num
2162 --}
2163 AND NVL(pawo.line_match_order,aal.ae_line_id) IS NOT NULL
2164 AND aal.ae_line_id = NVL(pawo.line_match_order,aal.ae_line_id)
2165 /* commented for Bug 9581715
2166 AND cwo.invoice_distribution_id = aida.invoice_distribution_id */
2167 --{BUG#7528609
2168 AND ca.operating_unit_id = p_operating_unit
2169 AND aal.code_combination_id = ca.accrual_account_id
2170 --}
2171 AND aida.old_distribution_id = aal.source_id--changed for bug 13495209
2172 AND xld.source_distribution_id_num_1 = aida.invoice_distribution_id
2173 AND xld.source_distribution_type = 'AP_INV_DIST'
2174 AND xlal.ledger_id = fsp.set_of_books_id
2175 AND xld.application_id = 200
2176 AND xlal.application_id = 200
2177 AND xlal.ae_header_id = xld.ae_header_id
2178 AND xlal.ae_line_num = xld.ae_line_num
2179 AND xlal.accounting_class_code NOT IN ('LIABILITY')
2180 AND ( (aida.po_distribution_id IS NULL )
2181 OR (xlal.accounting_class_code in ('IPV','EXCHANGE_RATE_VARIANCE','TRV','TIPV','TERV'
2182 ,'ACCRUAL','ITEM EXPENSE'))
2183 OR EXISTS ( SELECT 1
2184 FROM po_releases_all pra,
2185 po_distributions_all pod
2186 WHERE pod.po_distribution_id = aida.po_distribution_id
2187 AND pod.po_release_id IS NOT NULL
2188 AND pra.po_release_id = pod.po_release_id
2189 AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
2190 )
2191 OR EXISTS ( SELECT 1
2192 FROM po_headers_all poh,
2193 po_distributions_all pod
2194 WHERE pod.po_distribution_id = aida.po_distribution_id
2195 AND pod.po_release_id IS NULL
2196 AND poh.po_header_id = pod.po_header_id
2197 AND NVL(poh.consigned_consumption_flag,'N') = 'Y'
2198 )
2199 )
2200 GROUP BY cwo.write_off_id,
2201 pawo.po_transaction_id,
2202 cwo.inventory_transaction_id,
2203 aida.invoice_distribution_id,
2204 plc.lookup_code,
2205 cwo.transaction_date,
2206 DECODE(cwo.transaction_type_code, 'REVERSE WRITE OFF',
2207 cwo.write_off_amount,-1 * cwo.write_off_amount),
2208 DECODE(cwo.transaction_type_code, 'REVERSE WRITE OFF',
2209 cwo.entered_amount, -1 * cwo.entered_amount),
2210 pawo.transaction_quantity,
2211 cwo.currency_code,
2215 pawo.transaction_organization_id,
2212 cwo.currency_conversion_type,
2213 cwo.currency_conversion_rate,
2214 cwo.currency_conversion_date,
2216 cwo.operating_unit_id,
2217 cwo.last_update_date,
2218 cwo.last_updated_by,
2219 cwo.last_update_login,
2220 cwo.creation_date,
2221 cwo.created_by,
2222 cwo.request_id,
2223 cwo.program_application_id,
2224 cwo.program_id,
2225 cwo.program_update_date;
2226
2227 debug(' Done updating the Write of details table for AP data');
2228
2229 debug('Upgrade_old_data -');
2230
2231 EXCEPTION
2232
2233 WHEN OTHERS THEN
2234 rollback;
2235 x_return_status := FND_API.g_ret_sts_unexp_error ;
2236 debug('EXCEPTION OTHERS in Upgrade_old_data :'|| l_stmt_num || ' - ' || substrb(SQLERRM,1,180));
2237 fnd_message.set_name('BOM','CST_UNEXPECTED');
2238 fnd_message.set_token('TOKEN',substrb(SQLERRM,1,180));
2239 debug('l_stmt_num:'||l_stmt_num);
2240
2241 fnd_msg_pub.add;
2242
2243 FND_MSG_PUB.count_and_get
2244 ( p_count => x_msg_count
2245 , p_data => x_msg_data
2246 );
2247
2248
2249 END Upgrade_old_data;
2250
2251 /*===========================================================================+
2252 | |
2253 | Procedure Name : Load_ap_misc_data |
2254 | |
2255 | Purpose : This Procedure loads all the AP Miscellaneous Invoice |
2256 | data that hits the accrual account.This procedure also |
2257 | loads the IPV and ERV data and the Invoice data that is |
2258 | matched to the CONSIGNMENT PO. Only the Invoice data |
2259 | that has not been written off or for which the write offs |
2260 | have been revered, is loaded into the reconciliation table|
2261 | |
2262 | Called from : Start_accrual_load Procedure |
2263 | |
2264 | Parameters : |
2265 | IN : p_operating_unit IN NUMBER REQUIRED |
2266 | p_from_date IN VARCHAR2 can be NULL |
2267 | p_to_date IN VARCHAR2 can be NULL |
2268 | p_round_unit IN NUMBER REQUIRED |
2269 | |
2270 | OUT : x_return_status OUT NOCOPY VARCHAR2 |
2271 | x_msg_count OUT NOCOPY NUMBER |
2272 | x_msg_data OUT NOCOPY VARCHAR2 |
2273 | |
2274 | NOTES : None |
2275 +===========================================================================*/
2276
2277
2278 Procedure Load_ap_misc_data(p_operating_unit IN NUMBER,
2279 p_from_date IN DATE,
2280 p_to_date IN DATE,
2281 p_round_unit IN NUMBER,
2282 x_msg_count OUT NOCOPY NUMBER,
2283 x_msg_data OUT NOCOPY VARCHAR2,
2284 x_return_status OUT NOCOPY VARCHAR2
2285 )
2286
2287 IS
2288
2289 l_stmt_num NUMBER;
2290 l_build_id NUMBER;
2291 l_last_update_date DATE;
2292 l_last_updated_by NUMBER;
2293 l_last_update_login NUMBER;
2294 l_creation_date DATE;
2295 l_created_by NUMBER;
2296 l_request_id NUMBER;
2297 l_program_application_id NUMBER;
2298 l_program_id NUMBER;
2299 l_program_update_date DATE;
2300
2301 l_api_name CONSTANT VARCHAR2(30) := 'Load_ap_misc_data';
2302 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2303 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
2304
2305 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2306 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2307 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2308 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2309 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2310
2311 BEGIN
2312 debug('Load_ap_misc_data +');
2313 debug(' p_operating_unit : ' || p_operating_unit);
2314 debug(' p_from_date : ' || to_char(p_from_date,'DD-MON-YYYY HH24:MI:SS'));
2315 debug(' p_to_date : ' || to_char(p_to_date,'DD-MON-YYYY HH24:MI:SS'));
2316 debug(' p_round_unit : ' || p_round_unit);
2317
2318
2319 l_stmt_num := 10;
2320 debug(' l_stmt_num :'||l_stmt_num );
2321
2322 x_return_status := fnd_api.g_ret_sts_success;
2323
2324 xla_security_pkg.set_security_context(p_application_id => 200);
2325
2326 /* Get all the CONC WHO columns */
2327
2328 SELECT crb.build_id,
2329 crb.last_update_date,
2330 crb.last_updated_by,
2331 crb.last_update_login,
2332 crb.creation_date,
2333 crb.created_by,
2334 crb.request_id,
2338 INTO l_build_id,
2335 crb.program_application_id,
2336 crb.program_id,
2337 crb.program_update_date
2339 l_last_update_date,
2340 l_last_updated_by,
2341 l_last_update_login,
2342 l_creation_date,
2343 l_created_by,
2344 l_request_id,
2345 l_program_application_id,
2346 l_program_id,
2347 l_program_update_date
2348 FROM cst_reconciliation_build crb
2349 WHERE crb.request_id = FND_GLOBAL.CONC_REQUEST_ID;
2350
2351 debug(' l_build_id:'||l_build_id);
2352
2353 l_stmt_num := 15;
2354 debug(' l_stmt_num :'||l_stmt_num );
2355
2356
2357 /* Delete data from misc reconciliation table for the time range */
2358 DELETE from cst_misc_reconciliation
2359 WHERE transaction_date between p_from_date AND p_to_date
2360 AND operating_unit_id = p_operating_unit;
2361
2362 debug(' Nb rows deleted from cst_misc_reconciliation '||SQL%ROWCOUNT);
2363
2364 l_stmt_num := 25;
2365 debug(' l_stmt_num :'||l_stmt_num );
2366
2367
2368 /* For IPV and ERV lines, there will be a po_dist_id.So we have to handle them seperately. For Consigned AP invoices
2369 there will be a po_dist_id.So we need to handle them separetely by joing to poll and checking the consigned flag */
2370
2371 /* When AP creates accounting, it is possible for the line types to be merged thereby creating a summarized
2372 line in XAL.So one line in XAL can point to one or more lines in XDL (i.e one or different invoice distributions.
2373 So we need to pick up the amount from XDL from the unrounded columns.But even though the columns are called unrounded,
2374 they are actually rounded amounts since AP always passes rounded amounts to SLA and no further rounding in SLA is
2375 possible. */
2376
2377
2378 debug(' Nb rows deleted from cst_misc_reconciliation '||SQL%ROWCOUNT);
2379
2380 debug(' Inserting into cst_misc_reconciliation');
2381 Insert into cst_misc_reconciliation
2382 (
2383 transaction_date,
2384 amount,
2385 entered_amount,
2386 quantity,
2387 currency_code,
2388 currency_conversion_type,
2389 currency_conversion_rate,
2390 currency_conversion_date,
2391 invoice_distribution_id,
2392 po_distribution_id,
2393 inventory_transaction_id,
2394 accrual_account_id,
2395 transaction_type_code,
2396 inventory_item_id,
2397 vendor_id,
2398 inventory_organization_id,
2399 operating_unit_id,
2400 build_id,
2401 last_update_date,
2402 last_updated_by,
2403 last_update_login,
2404 creation_date,
2405 created_by,
2406 request_id,
2407 program_application_id,
2408 program_id,
2409 program_update_date,
2410 Ae_header_id,
2411 Ae_line_num
2412 )
2413 WITH AP_MISC_TRANSACTIONS_XLA AS
2414 (SELECT /*+ parallel(xah) leading(xah) NO_MERGE */
2415 xal.accounting_date,
2416 ROUND((NVL(xdl.unrounded_accounted_dr,0) - NVL(xdl.unrounded_accounted_cr,0)) / p_round_unit) * p_round_unit accounted_amount,
2417 ROUND((NVL(xdl.unrounded_entered_dr,0) - NVL(xdl.unrounded_entered_cr,0)) / p_round_unit) * p_round_unit entered_amount,
2418 xal.currency_code,
2419 xal.currency_conversion_type,
2420 xal.currency_conversion_rate,
2421 xal.currency_conversion_date,
2422 xal.accounting_class_code,
2423 xal.code_combination_id,
2424 xal.ae_header_id,
2425 xal.ae_line_num,
2426 xdl.source_distribution_id_num_1
2427 FROM xla_ae_headers xah,
2428 xla_ae_lines xal,
2429 xla_distribution_links xdl,
2430 cst_accrual_accounts caa,
2431 financials_system_params_all fsp
2432 WHERE fsp.org_id = p_operating_unit
2433 AND caa.operating_unit_id = p_operating_unit
2434 AND xah.application_id = 200 -- AP
2435 AND xah.accounting_date between p_from_date AND p_to_date
2436 AND xal.accounting_date between p_from_date AND p_to_date
2437 AND xah.ledger_id = fsp.set_of_books_id
2438 AND xah.gl_transfer_status_code = 'Y'
2439 AND xah.balance_type_code = 'A'
2440 AND xal.application_id = 200
2441 AND xal.ae_header_id = xah.ae_header_id
2442 AND xal.code_combination_id = caa.accrual_account_id
2443 AND xal.accounting_class_code NOT IN ('LIABILITY')
2444 AND xdl.application_id = 200
2445 AND xdl.ae_header_id = xal.ae_header_id
2446 AND xdl.ae_line_num = xal.ae_line_num
2447 AND xdl.source_distribution_type = 'AP_INV_DIST')
2448 SELECT /*+ LEADING(xla aida) INDEX(aida AP_INVOICE_DISTRIBUTIONS_U2) */
2449 xla.accounting_date,
2450 xla.accounted_amount,
2451 xla.entered_amount,
2452 decode(aida.corrected_invoice_dist_id,
2453 NULL,round(NVL(aida.quantity_invoiced,0),20),
2454 NULL
2455 ),
2456 xla.currency_code,
2457 xla.currency_conversion_type,
2458 xla.currency_conversion_rate,
2459 xla.currency_conversion_date,
2460 aida.invoice_distribution_id,
2461 aida.po_distribution_id,
2462 NULL, -- Inventory_transaction_id
2463 xla.code_combination_id,
2464 Decode (aida.line_type_lookup_code,
2465 'IPV','AP INVOICE PRICE VAR',
2466 'ERV','AP EXCHANGE RATE VAR',
2467 'TERV','TERV',
2471 NULL,'AP NO PO',
2468 'TIPV','TIPV',
2469 'TRV','TRV',
2470 Decode(aida.po_distribution_id,
2472 'CONSIGNMENT'
2473 )
2474 ), -- transaction_type_code
2475 pol.item_id, -- Inventory_item_id
2476 NVL(poh.vendor_id,apia.vendor_id),
2477 NULL, -- Inventory_organization_id
2478 p_operating_unit,
2479 l_build_id,
2480 l_last_update_date,
2481 l_last_updated_by,
2482 l_last_update_login,
2483 l_creation_date,
2484 l_created_by,
2485 l_request_id,
2486 l_program_application_id,
2487 l_program_id,
2488 l_program_update_date,
2489 xla.ae_header_id,
2490 xla.ae_line_num
2491 FROM AP_MISC_TRANSACTIONS_XLA xla,
2492 ap_invoice_distributions_all aida,
2493 ap_invoices_all apia,
2494 po_distributions_all pod,
2495 po_lines_all pol,
2496 po_headers_all poh
2497 WHERE xla.source_distribution_id_num_1 = aida.invoice_distribution_id
2498 AND aida.org_id = p_operating_unit
2499 AND apia.invoice_id = aida.invoice_id
2500 AND aida.po_distribution_id = pod.po_distribution_id(+)
2501 AND pod.po_line_id = pol.po_line_id(+)
2502 AND pod.po_header_id = poh.po_header_id(+)
2503 AND NVL(pod.lcm_flag,'N') = 'N' --LCM update
2504 AND ( xla.accounting_class_code in ('IPV','EXCHANGE_RATE_VARIANCE','TRV','TIPV','TERV')
2505 OR ( aida.po_distribution_id IS NULL )
2506 OR ( pod.po_release_id is NULL
2507 AND NVL(poh.consigned_consumption_flag,'N') = 'Y' )
2508 OR EXISTS (
2509 SELECT 1
2510 FROM po_releases_all pra
2511 WHERE pod.po_release_id is NOT NULL
2512 AND pra.po_release_id = pod.po_release_id
2513 AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
2514 )
2515 )
2516 AND NOT EXISTS (SELECT 1
2517 FROM cst_write_offs cwo1
2518 WHERE cwo1.transaction_type_code = 'WRITE OFF'
2519 AND cwo1.invoice_distribution_id is NOT NULL
2520 AND cwo1.accrual_account_id = xla.code_combination_id
2521 AND cwo1.invoice_distribution_id = aida.invoice_distribution_id
2522 AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
2523 FROM cst_write_offs cwo2
2524 WHERE cwo2.invoice_distribution_id is NOT NULL
2525 AND cwo2.invoice_distribution_id = aida.invoice_distribution_id
2526 AND cwo2.accrual_account_id = xla.code_combination_id
2527 AND EXISTS (Select 1 from cst_write_off_details cwod
2528 where cwod.write_off_id = cwo2.write_off_id
2529 and cwod.ae_header_id = xla.ae_header_id
2530 and cwod.ae_line_num = xla.ae_line_num
2531 )
2532 )
2533 )
2534 UNION ALL
2535 SELECT /*+ LEADING(xla aida) INDEX(aida AP_SELF_ASSESSED_TAX_DIST_U2) */
2536 xla.accounting_date,
2537 xla.accounted_amount,
2538 xla.entered_amount,
2539 decode(aida.corrected_invoice_dist_id,
2540 NULL,round(NVL(aida.quantity_invoiced,0),20),
2541 NULL
2542 ),
2543 xla.currency_code,
2544 xla.currency_conversion_type,
2545 xla.currency_conversion_rate,
2546 xla.currency_conversion_date,
2547 aida.invoice_distribution_id,
2548 aida.po_distribution_id,
2549 NULL, -- Inventory_transaction_id
2550 xla.code_combination_id,
2551 Decode (aida.line_type_lookup_code,
2552 'IPV','AP INVOICE PRICE VAR',
2553 'ERV','AP EXCHANGE RATE VAR',
2554 'TERV','TERV',
2555 'TIPV','TIPV',
2556 'TRV','TRV',
2557 Decode(aida.po_distribution_id,
2558 NULL,'AP NO PO',
2559 'CONSIGNMENT'
2560 )
2561 ), -- transaction_type_code
2562 pol.item_id, -- Inventory_item_id
2563 NVL(poh.vendor_id,apia.vendor_id),
2564 NULL, -- Inventory_organization_id
2565 p_operating_unit,
2566 l_build_id,
2567 l_last_update_date,
2568 l_last_updated_by,
2569 l_last_update_login,
2570 l_creation_date,
2571 l_created_by,
2572 l_request_id,
2573 l_program_application_id,
2574 l_program_id,
2575 l_program_update_date,
2576 xla.ae_header_id,
2577 xla.ae_line_num
2578 FROM AP_MISC_TRANSACTIONS_XLA xla,
2579 ap_self_assessed_tax_dist_all aida,
2580 ap_invoices_all apia,
2581 po_distributions_all pod,
2582 po_lines_all pol,
2583 po_headers_all poh
2584 WHERE xla.source_distribution_id_num_1 = aida.invoice_distribution_id
2585 AND aida.org_id = p_operating_unit
2586 AND apia.invoice_id = aida.invoice_id
2587 AND aida.po_distribution_id = pod.po_distribution_id(+)
2588 AND pod.po_line_id = pol.po_line_id(+)
2589 AND pod.po_header_id = poh.po_header_id(+)
2593 OR ( pod.po_release_id is NULL
2590 and NVL(pod.lcm_flag,'N') = 'N' --LCM update
2591 AND ( xla.accounting_class_code in ('IPV','EXCHANGE_RATE_VARIANCE','TRV','TIPV','TERV')
2592 OR ( aida.po_distribution_id IS NULL )
2594 AND NVL(poh.consigned_consumption_flag,'N') = 'Y' )
2595 OR EXISTS (
2596 SELECT 1
2597 FROM po_releases_all pra
2598 WHERE pod.po_release_id is NOT NULL
2599 AND pra.po_release_id = pod.po_release_id
2600 AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
2601 )
2602 )
2603 AND NOT EXISTS (SELECT 1
2604 FROM cst_write_offs cwo1
2605 WHERE cwo1.transaction_type_code = 'WRITE OFF'
2606 AND cwo1.invoice_distribution_id is NOT NULL
2607 AND cwo1.accrual_account_id = xla.code_combination_id
2608 AND cwo1.invoice_distribution_id = aida.invoice_distribution_id
2609 AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
2610 FROM cst_write_offs cwo2
2611 WHERE cwo2.invoice_distribution_id is NOT NULL
2612 AND cwo2.invoice_distribution_id = aida.invoice_distribution_id
2613 AND cwo2.accrual_account_id = xla.code_combination_id
2614 AND EXISTS (Select 1 from cst_write_off_details cwod
2615 where cwod.write_off_id = cwo2.write_off_id
2616 and cwod.ae_header_id = xla.ae_header_id
2617 and cwod.ae_line_num = xla.ae_line_num
2618 )
2619 )
2620 );
2621
2622 debug('Load_ap_misc_data -');
2623
2624 EXCEPTION
2625
2626 WHEN OTHERS THEN
2627 rollback;
2628 x_return_status := FND_API.g_ret_sts_unexp_error ;
2629 fnd_message.set_name('BOM','CST_UNEXPECTED');
2630 fnd_message.set_token('TOKEN',substrb(SQLERRM,1,180));
2631 debug('l_stmt_num :'||l_stmt_num);
2632 debug('EXCEPTION OTHERS in Load_ap_misc_data '||substrb(SQLERRM,1,180));
2633 fnd_msg_pub.add;
2634 FND_MSG_PUB.count_and_get
2635 ( p_count => x_msg_count
2636 , p_data => x_msg_data
2637 );
2638
2639
2640 END Load_ap_misc_data;
2641
2642 /*===========================================================================+
2643 | |
2644 | Procedure Name : Load_inv_misc_data |
2645 | |
2646 | Purpose : This Procedure loads all the Inventory Transaction data |
2647 | that hits the accrual account.Only Inventory Transactions |
2648 | that have not been written off or for which the write offs|
2649 | have been reversed,is loaded into the reconciliation table|
2650 | This procedure also updates the Vendor information for |
2651 | Intercompany transactions and attempts to group the |
2652 | ownership transfer transactions with the Invoice that has |
2653 | been matched to the Consignment PO by updating and then |
2654 | grouping these together by PO_DISTRIBUTION_ID. |
2655 | |
2656 | Called from : Start_accrual_load Procedure |
2657 | |
2658 | Parameters : |
2659 | IN : p_operating_unit IN NUMBER REQUIRED |
2660 | p_from_date IN VARCHAR2 can be NULL |
2661 | p_to_date IN VARCHAR2 can be NULL |
2662 | p_round_unit IN NUMBER REQUIRED |
2663 | |
2664 | OUT : x_return_status OUT NOCOPY VARCHAR2 |
2665 | x_msg_count OUT NOCOPY NUMBER |
2666 | x_msg_data OUT NOCOPY VARCHAR2 |
2667 | |
2668 | NOTES : None |
2669 | 11-Aug-2008 pmarada Added code to insert OPM financials related inventory |
2670 | data into cst_misc_reconciliation table,bug 6995413 |
2671 | 17-Apr-2012 Uday Phadtare Bug 13728770.
2672 | In case of for OPM Financials module, used separate queries for entity_code PURCHASING and INVENTORY.
2673 +===========================================================================*/
2674
2675 Procedure Load_inv_misc_data(p_operating_unit IN NUMBER,
2676 p_from_date IN DATE,
2677 p_to_date IN DATE,
2678 p_round_unit IN NUMBER,
2679 x_msg_count OUT NOCOPY NUMBER,
2680 x_msg_data OUT NOCOPY VARCHAR2,
2681 x_return_status OUT NOCOPY VARCHAR2
2682 )
2683
2684 IS
2685
2686 l_stmt_num NUMBER;
2687 l_build_id NUMBER;
2688 l_last_update_date DATE;
2689 l_last_updated_by NUMBER;
2690 l_last_update_login NUMBER;
2691 l_creation_date DATE;
2695 l_program_id NUMBER;
2692 l_created_by NUMBER;
2693 l_request_id NUMBER;
2694 l_program_application_id NUMBER;
2696 l_program_update_date DATE;
2697
2698 l_api_name CONSTANT VARCHAR2(30) := 'Load_inv_misc_data';
2699 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
2700 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
2701
2702 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2703 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
2704 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2705 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2706 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2707
2708 l_misc_inv VARCHAR2(1);
2709
2710 /* BUG #12628449 : The check for existence of inventory transactions hitting the accrual account as a fix for Bug #7384429
2711 is only for Cost Management. */
2712 CURSOR c_chk_misc_inv(p_ou_id IN NUMBER,
2713 p_from_dt IN DATE,
2714 p_to_dt IN DATE)
2715 IS
2716 SELECT
2717 'Y'
2718 FROM financials_system_params_all fsp
2719 WHERE fsp.org_id = p_ou_id
2720 AND EXISTS(
2721 SELECT 1 FROM
2722 xla_ae_lines l,
2723 xla_ae_headers h,
2724 xla_event_types_b xet,
2725 cst_accrual_accounts caa
2726 WHERE caa.operating_unit_id = p_ou_id
2727 AND xet.application_id = 707
2728 AND xet.entity_code = 'MTL_ACCOUNTING_EVENTS'
2729 AND h.application_id = 707
2730 AND xet.event_type_code = h.event_type_code
2731 AND h.ledger_id = fsp.set_of_books_id
2732 AND l.accounting_date BETWEEN p_from_date AND p_to_date
2733 AND h.ACCOUNTING_ENTRY_STATUS_CODE = 'F'
2734 AND h.gl_transfer_status_code = 'Y'
2735 AND l.application_id = 707
2736 AND h.ae_header_id = l.ae_header_id
2737 AND l.code_combination_id = caa.accrual_account_id
2738 AND l.ledger_id = fsp.set_of_books_id
2739 );
2740
2741 CURSOR c_opm_count IS
2742 SELECT 1 FROM gmf_rcv_accounting_txns
2743 WHERE org_id = p_operating_unit
2744 AND rownum < 2;
2745 l_opm_count NUMBER;
2746
2747 BEGIN
2748 debug('Load_inv_misc_data+');
2749 debug(' p_operating_unit : ' || p_operating_unit);
2750 debug(' p_from_date : ' || to_char(p_from_date,'DD-MON-YYYY HH24:MI:SS'));
2751 debug(' p_to_date : ' || to_char(p_to_date,'DD-MON-YYYY HH24:MI:SS'));
2752 debug(' p_round_unit : ' || p_round_unit);
2753 l_stmt_num := 10;
2754 debug(' l_stmt_num :'|| l_stmt_num);
2755 x_return_status := fnd_api.g_ret_sts_success;
2756
2757
2758 xla_security_pkg.set_security_context(p_application_id => 707);
2759
2760 /* Get all the CONC WHO columns */
2761
2762 SELECT crb.build_id,
2763 crb.last_update_date,
2764 crb.last_updated_by,
2765 crb.last_update_login,
2766 crb.creation_date,
2767 crb.created_by,
2768 crb.request_id,
2769 crb.program_application_id,
2770 crb.program_id,
2771 crb.program_update_date
2772 INTO l_build_id,
2773 l_last_update_date,
2774 l_last_updated_by,
2775 l_last_update_login,
2776 l_creation_date,
2777 l_created_by,
2778 l_request_id,
2779 l_program_application_id,
2780 l_program_id,
2781 l_program_update_date
2782 FROM cst_reconciliation_build crb
2783 WHERE crb.request_id = FND_GLOBAL.CONC_REQUEST_ID;
2784
2785 debug(' l_build_id :'|| l_build_id);
2786
2787 l_stmt_num := 20;
2788 debug(' l_stmt_num :'|| l_stmt_num);
2789
2790 /* Insert INV data into the MISC details table. If there is a write off against the Txn, the txn will be
2791 inserted only if the write off has been reverse written off or the txn has never been written off. */
2792 /* BUG #12628449 : The check for existence of inventory transactions hitting the accrual account as a fix for Bug #7384429
2793 is only for Cost Management. Hence, it should be performed only for insertion of accrual data for INV */
2794
2795 OPEN c_chk_misc_inv(p_ou_id => p_operating_unit,
2796 p_from_dt => p_from_date,
2797 p_to_dt => p_to_date);
2798 FETCH c_chk_misc_inv INTO l_misc_inv;
2799 IF c_chk_misc_inv%NOTFOUND THEN
2800 l_misc_inv := 'N';
2801 END IF;
2802 CLOSE c_chk_misc_inv;
2803
2804 debug(' l_misc_inv:'||l_misc_inv);
2805
2806 IF l_misc_inv = 'Y' THEN
2807
2808 debug(' Inserting into cst_misc_reconciliation' );
2809
2810 Insert into cst_misc_reconciliation
2811 (
2812 transaction_date,
2813 amount,
2814 entered_amount,
2815 quantity,
2816 currency_code,
2817 currency_conversion_type,
2818 currency_conversion_rate,
2819 currency_conversion_date,
2820 invoice_distribution_id,
2821 inventory_transaction_id,
2822 accrual_account_id,
2823 transaction_type_code,
2824 inventory_item_id,
2825 vendor_id,
2826 inventory_organization_id,
2827 operating_unit_id,
2828 build_id,
2829 last_update_date,
2830 last_updated_by,
2831 last_update_login,
2832 creation_date,
2836 program_id,
2833 created_by,
2834 request_id,
2835 program_application_id,
2837 program_update_date,
2838 Ae_header_id,
2839 Ae_line_num
2840 )
2841 SELECT mmt.transaction_date,
2842 round((NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0)) / p_round_unit) * p_round_unit,
2843 round((NVL(entered_dr,0) - NVL(entered_cr,0)) / p_round_unit) * p_round_unit,
2844 round(NVL(mmt.primary_quantity,0),20),
2845 xal.currency_code,
2846 xal.currency_conversion_type,
2847 xal.currency_conversion_rate,
2848 xal.currency_conversion_date,
2849 NULL, -- Invoice_distribution_id
2850 mmt.transaction_id,
2851 xal.code_combination_id,
2852 Decode(mmt.transaction_action_id,
2853 6,'CONSIGNMENT',
2854 25,'CONSIGNMENT', /*pick up retro active consigned price updates as consigned */
2855 to_char(mmt.transaction_type_id)
2856 ),
2857 mmt.inventory_item_id,
2858 NULL, -- vendor ID will be updated later for I/C txns */
2859 mmt.organization_id,
2860 p_operating_unit,
2861 l_build_id,
2862 l_last_update_date,
2863 l_last_updated_by,
2864 l_last_update_login,
2865 l_creation_date,
2866 l_created_by,
2867 l_request_id,
2868 l_program_application_id,
2869 l_program_id,
2870 l_program_update_date,
2871 xal.ae_header_id,
2872 xal.ae_line_num
2873 FROM xla_ae_headers xah,
2874 xla_ae_lines xal,
2875 xla_transaction_entities_upg xte,
2876 mtl_material_transactions mmt,
2877 cst_accrual_accounts caa,
2878 financials_system_params_all fsp
2879 WHERE xal.code_combination_id = caa.accrual_account_id
2880 AND caa.operating_unit_id = p_operating_unit
2881 AND fsp.org_id = p_operating_unit
2882 AND fsp.set_of_books_id = xah.ledger_id
2883 AND xah.ae_header_id = xal.ae_header_id
2884 AND xah.application_id = 707 -- Oracle Cost management
2885 AND xal.application_id = 707
2886 AND xte.ledger_id = fsp.set_of_books_id
2887 AND xte.application_id = 707
2888 AND xte.entity_id = xah.entity_id
2889 AND xte.entity_code = 'MTL_ACCOUNTING_EVENTS'
2890 AND xah.gl_transfer_status_code = 'Y'
2891 AND mmt.transaction_id = NVL(xte.source_id_int_1,(-99))
2892 AND NOT( mmt.transaction_action_id = 24 -- LCM Change
2893 AND NVL(mmt.source_code,'XXX') = 'LCMADJ' -- LCM Change
2894 )
2895 AND mmt.transaction_date between p_from_date AND p_to_date
2896 AND EXISTS (
2897 SELECT 1
2898 FROM hr_organization_information hoi
2899 WHERE --{BUG#8398114
2900 -- hoi.organization_id = mmt.organization_id
2901 (hoi.organization_id = mmt.organization_id OR
2902 hoi.organization_id = mmt.transfer_organization_id)
2903 --}
2904 AND hoi.org_information_context = 'Accounting Information'
2905 AND hoi.org_information3 = to_char(p_operating_unit)
2906 )
2907 AND NOT EXISTS (
2908 SELECT 1
2909 FROM cst_write_offs cwo1
2910 WHERE cwo1.transaction_type_code = 'WRITE OFF'
2911 AND cwo1.inventory_transaction_id is NOT NULL
2912 AND cwo1.inventory_transaction_id = mmt.transaction_id
2913 AND cwo1.accrual_account_id = xal.code_combination_id
2914 AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
2915 FROM cst_write_offs cwo2
2916 WHERE cwo2.inventory_transaction_id is NOT NULL
2917 AND cwo2.inventory_transaction_id = mmt.transaction_id
2918 AND cwo2.accrual_account_id = xal.code_combination_id
2919 AND EXISTS ( Select 1 from cst_write_off_details cwod
2920 where cwod.write_off_id = cwo2.write_off_id
2921 and cwod.ae_header_id = xah.ae_header_id
2922 and cwod.ae_line_num = xal.ae_line_num
2923 )
2924 )
2925 );
2926
2927
2928 debug(' Done Inserting the INV misc data into the accrual table');
2929
2930 END IF;
2931
2932 /* Start OPM financials data postng into cst_misc_reconcialiation, pmarada, bug 6995413 */
2933 OPEN c_opm_count;
2934 FETCH c_opm_count INTO l_opm_count;
2935 CLOSE c_opm_count;
2936 debug(' l_opm_count:'||l_opm_count);
2937
2938 IF l_opm_count > 0 THEN
2939 /* Call the xla security package for OPM Financials application also, pmarada */
2940 xla_security_pkg.set_security_context(p_application_id => 555);
2941
2942 l_stmt_num := 25;
2943 debug(' l_stmt_num :'|| l_stmt_num);
2944 debug(' OPM misc inventory insertion');
2945
2949 amount,
2946 Insert into cst_misc_reconciliation
2947 (
2948 transaction_date,
2950 entered_amount,
2951 quantity,
2952 currency_code,
2953 currency_conversion_type,
2954 currency_conversion_rate,
2955 currency_conversion_date,
2956 invoice_distribution_id,
2957 inventory_transaction_id,
2958 accrual_account_id,
2959 transaction_type_code,
2960 inventory_item_id,
2961 vendor_id,
2962 inventory_organization_id,
2963 operating_unit_id,
2964 build_id,
2965 last_update_date,
2966 last_updated_by,
2967 last_update_login,
2968 creation_date,
2969 created_by,
2970 request_id,
2971 program_application_id,
2972 program_id,
2973 program_update_date,
2974 Ae_header_id,
2975 Ae_line_num
2976 )
2977 SELECT mmt.transaction_date,
2978 round((NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0)) / p_round_unit) * p_round_unit,
2979 round((NVL(entered_dr,0) - NVL(entered_cr,0)) / p_round_unit) * p_round_unit,
2980 round(NVL(mmt.primary_quantity,0),20),
2981 xal.currency_code,
2982 xal.currency_conversion_type,
2983 xal.currency_conversion_rate,
2984 xal.currency_conversion_date,
2985 NULL, -- Invoice_distribution_id
2986 mmt.transaction_id,
2987 xal.code_combination_id,
2988 Decode(mmt.transaction_action_id,
2989 6,'CONSIGNMENT', /* Ownership Transfer */
2990 25,'CONSIGNMENT', /* check pick up retro active consigned price updates as consigned */
2991 to_char(mmt.transaction_type_id)
2992 ),
2993 mmt.inventory_item_id,
2994 NULL, -- vendor ID will be updated later for I/C txns */
2995 mmt.organization_id,
2996 p_operating_unit,
2997 l_build_id,
2998 l_last_update_date,
2999 l_last_updated_by,
3000 l_last_update_login,
3001 l_creation_date,
3002 l_created_by,
3003 l_request_id,
3004 l_program_application_id,
3005 l_program_id,
3006 l_program_update_date,
3007 xal.ae_header_id,
3008 xal.ae_line_num
3009 FROM xla_ae_headers xah,
3010 xla_ae_lines xal,
3011 xla_transaction_entities_upg xte,
3012 mtl_material_transactions mmt,
3013 cst_accrual_accounts caa,
3014 financials_system_params_all fsp,
3015 mtl_parameters mp /* Bug 13728770 */
3016 WHERE xal.code_combination_id = caa.accrual_account_id
3017 AND caa.operating_unit_id = p_operating_unit
3018 AND fsp.org_id = p_operating_unit
3019 AND fsp.set_of_books_id = xah.ledger_id
3020 AND xah.ae_header_id = xal.ae_header_id
3021 AND xah.application_id = 555 -- OPM financials
3022 AND xal.application_id = 555
3023 AND xte.ledger_id = fsp.set_of_books_id
3024 AND xte.application_id = 555
3025 AND xte.entity_id = xah.entity_id
3026 AND xte.entity_code IN ('INVENTORY')
3027 AND xah.gl_transfer_status_code = 'Y'
3028 AND mmt.transaction_id = NVL(xte.source_id_int_1,(-99))
3029 AND mp.organization_id = mmt.organization_id /* Bug 13728770 */
3030 AND mp.process_enabled_flag = 'Y' /* Bug 13728770 */
3031 AND mmt.transaction_date between p_from_date AND p_to_date
3032 AND EXISTS (
3033 SELECT 1
3034 FROM hr_organization_information hoi
3035 WHERE hoi.organization_id = mmt.organization_id
3036 AND hoi.org_information_context = 'Accounting Information'
3037 AND hoi.org_information3 = to_char(p_operating_unit) --bug 10648494
3038 )
3039 AND NOT EXISTS (
3040 SELECT 1
3041 FROM cst_write_offs cwo1
3042 WHERE cwo1.transaction_type_code = 'WRITE OFF'
3043 AND cwo1.inventory_transaction_id is NOT NULL
3044 AND cwo1.inventory_transaction_id = mmt.transaction_id
3045 AND cwo1.accrual_account_id = xal.code_combination_id
3046 AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
3047 FROM cst_write_offs cwo2
3048 WHERE cwo2.inventory_transaction_id is NOT NULL
3049 AND cwo2.inventory_transaction_id = mmt.transaction_id
3050 AND cwo2.accrual_account_id = xal.code_combination_id
3051 AND EXISTS ( Select 1 from cst_write_off_details cwod
3052 where cwod.write_off_id = cwo2.write_off_id
3053 and cwod.ae_header_id = xah.ae_header_id
3054 and cwod.ae_line_num = xal.ae_line_num
3055 )
3056 )
3057 )
3058 UNION ALL /* Bug 13728770 */
3059 SELECT mmt.transaction_date,
3060 round((NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0)) / p_round_unit) * p_round_unit,
3064 xal.currency_conversion_type,
3061 round((NVL(entered_dr,0) - NVL(entered_cr,0)) / p_round_unit) * p_round_unit,
3062 round(NVL(mmt.primary_quantity,0),20),
3063 xal.currency_code,
3065 xal.currency_conversion_rate,
3066 xal.currency_conversion_date,
3067 NULL, -- Invoice_distribution_id
3068 mmt.transaction_id,
3069 xal.code_combination_id,
3070 Decode(mmt.transaction_action_id,
3071 6,'CONSIGNMENT', /* Ownership Transfer */
3072 25,'CONSIGNMENT', /* check pick up retro active consigned price updates as consigned */
3073 to_char(mmt.transaction_type_id)
3074 ),
3075 mmt.inventory_item_id,
3076 NULL, -- vendor ID will be updated later for I/C txns */
3077 mmt.organization_id,
3078 p_operating_unit,
3079 l_build_id,
3080 l_last_update_date,
3081 l_last_updated_by,
3082 l_last_update_login,
3083 l_creation_date,
3084 l_created_by,
3085 l_request_id,
3086 l_program_application_id,
3087 l_program_id,
3088 l_program_update_date,
3089 xal.ae_header_id,
3090 xal.ae_line_num
3091 FROM xla_ae_headers xah,
3092 xla_ae_lines xal,
3093 xla_transaction_entities_upg xte,
3094 mtl_material_transactions mmt,
3095 cst_accrual_accounts caa,
3096 financials_system_params_all fsp,
3097 mtl_parameters mp /* Bug 13728770 */
3098 WHERE xal.code_combination_id = caa.accrual_account_id
3099 AND caa.operating_unit_id = p_operating_unit
3100 AND fsp.org_id = p_operating_unit
3101 AND fsp.set_of_books_id = xah.ledger_id
3102 AND xah.ae_header_id = xal.ae_header_id
3103 AND xah.application_id = 555 -- OPM financials
3104 AND xal.application_id = 555
3105 AND xte.ledger_id = fsp.set_of_books_id
3106 AND xte.application_id = 555
3107 AND xte.entity_id = xah.entity_id
3108 AND xte.entity_code IN ('PURCHASING') -- consignment transactions types are under purchasing
3109 AND xah.gl_transfer_status_code = 'Y'
3110 AND mmt.transaction_id = NVL(xte.source_id_int_1,(-99))
3111 AND mmt.transaction_action_id = 6 /* Bug 13728770 */
3112 AND mp.organization_id = mmt.organization_id /* Bug 13728770 */
3113 AND mp.process_enabled_flag = 'Y' /* Bug 13728770 */
3114 AND mmt.transaction_date between p_from_date AND p_to_date
3115 AND EXISTS (
3116 SELECT 1
3117 FROM hr_organization_information hoi
3118 WHERE hoi.organization_id = mmt.organization_id
3119 AND hoi.org_information_context = 'Accounting Information'
3120 AND hoi.org_information3 = to_char(p_operating_unit) --bug 10648494
3121 )
3122 AND NOT EXISTS (
3123 SELECT 1
3124 FROM cst_write_offs cwo1
3125 WHERE cwo1.transaction_type_code = 'WRITE OFF'
3126 AND cwo1.inventory_transaction_id is NOT NULL
3127 AND cwo1.inventory_transaction_id = mmt.transaction_id
3128 AND cwo1.accrual_account_id = xal.code_combination_id
3129 AND cwo1.write_off_id = ( SELECT MAX(write_off_id)
3130 FROM cst_write_offs cwo2
3131 WHERE cwo2.inventory_transaction_id is NOT NULL
3132 AND cwo2.inventory_transaction_id = mmt.transaction_id
3133 AND cwo2.accrual_account_id = xal.code_combination_id
3134 AND EXISTS ( Select 1 from cst_write_off_details cwod
3135 where cwod.write_off_id = cwo2.write_off_id
3136 and cwod.ae_header_id = xah.ae_header_id
3137 and cwod.ae_line_num = xal.ae_line_num
3138 )
3139 )
3140 );
3141
3142 debug(' Done Inserting the OPM Financials related INV misc data into the accrual table' );
3143
3144 END IF;
3145 /* End OPM financials data posting,pmarada */
3146
3147 l_stmt_num := 30;
3148 debug(' l_stmt_num :'|| l_stmt_num);
3149
3150
3151 -- reset the SLA security back to discrete costing module
3152 IF l_opm_count > 0 THEN
3153 xla_security_pkg.set_security_context(p_application_id => 707);
3154 debug(' resetting the context to 707');
3155 END IF;
3156
3157 /* Update Intercompany INV txns with the Vendor information */
3158 debug(' Inserting miscellenaous inventory for discrete');
3159
3160 UPDATE cst_misc_reconciliation cmr
3161 SET cmr.vendor_id = (
3162 SELECT mip.vendor_id
3163 FROM mtl_material_transactions mmt,
3164 mtl_intercompany_parameters mip,
3165 hr_organization_information hoi1,
3169 AND hoi1.organization_id = decode(mmt.transaction_action_id,
3166 hr_organization_information hoi2
3167 WHERE mmt.transaction_id = cmr.inventory_transaction_id
3168 AND hoi1.org_information_context = 'Accounting Information'
3170 12,mmt.transfer_organization_id,
3171 mmt.organization_id
3172 )
3173 AND mip.ship_organization_id = to_number(hoi1.org_information3)
3174 AND hoi2.org_information_context = 'Accounting Information'
3175 AND hoi2.organization_id = Decode(mmt.transaction_action_id,
3176 12,mmt.organization_id,
3177 mmt.transfer_organization_id
3178 )
3179 AND mip.sell_organization_id = to_number(hoi2.org_information3)
3180 AND mip.flow_type = 1
3181 )
3182 WHERE cmr.inventory_transaction_id is NOT NULL
3183 AND cmr.operating_unit_id = p_operating_unit
3184 AND cmr.transaction_type_code in ('61','62');
3185
3186
3187 debug(' Done Updating the Vendor information for Intercompany txns');
3188
3189
3190 l_stmt_num := 40;
3191 debug(' l_stmt_num :'|| l_stmt_num);
3192
3193 /* Update PO_DISTRIBUTION_ID for consigned ownership transfer txns so that balancing txns can then be deleted */
3194
3195 debug(' Updating the PO_DISTRIBUTION_ID for consigned INV transactions');
3196
3197 Update CST_MISC_RECONCILIATION cmr
3198 Set po_distribution_id = (select mct.po_distribution_id
3199 from mtl_consumption_transactions mct
3200 where mct.consumption_processed_flag = 'Y'
3201 AND mct.transaction_id in
3202 (select transaction_id
3203 from mtl_material_transactions mmt
3204 where mmt.transaction_id = cmr.inventory_transaction_id
3205 or mmt.transfer_transaction_id = cmr.inventory_transaction_id
3206 )
3207 )
3208 WHERE cmr.inventory_transaction_id is NOT NULL
3209 AND cmr.po_distribution_id is NULL
3210 AND cmr.transaction_type_code = 'CONSIGNMENT'
3211 AND cmr.operating_unit_id = p_operating_unit;
3212
3213 l_stmt_num := 50;
3214
3215 debug(' Deleting the Consigment transactions that balance out');
3216
3217 /* Now delete all the Consigned matching txns after grouping them by po_distribution_id */
3218
3219 DELETE FROM cst_misc_reconciliation cmr
3220 WHERE cmr.transaction_type_code = 'CONSIGNMENT'
3221 AND cmr.operating_unit_id = p_operating_unit
3222 AND cmr.po_distribution_id is NOT NULL
3223 AND EXISTS ( SELECT 1
3224 FROM cst_misc_reconciliation cmr2
3225 WHERE cmr2.po_distribution_id = cmr.po_distribution_id
3226 AND cmr2.accrual_account_id = cmr.accrual_account_id
3227 AND cmr2.operating_unit_id = p_operating_unit
3228 HAVING SUM(cmr2.amount) = 0
3229 GROUP BY cmr2.po_distribution_id,
3230 cmr2.accrual_account_id
3231 );
3232
3233
3234 debug('Load_inv_misc_data -');
3235
3236 EXCEPTION
3237
3238 WHEN OTHERS THEN
3239 rollback;
3240 x_return_status := FND_API.g_ret_sts_unexp_error ;
3241 fnd_message.set_name('BOM','CST_UNEXPECTED');
3242 fnd_message.set_token('TOKEN',substrb(SQLERRM,1,180));
3243 debug('EXCEPTION OTHERS in Load_inv_misc_data '||substrb(SQLERRM,1,140));
3244 debug('l_stmt_num :'||l_stmt_num);
3245 fnd_msg_pub.add;
3246 FND_MSG_PUB.count_and_get
3247 ( p_count => x_msg_count
3248 , p_data => x_msg_data
3249 );
3250
3251 END Load_inv_misc_data;
3252
3253 /*===========================================================================+
3254 | |
3255 | Procedure Name : Insert_build_parameters |
3256 | |
3257 | Purpose : This Procedure inserts a row into the |
3258 | CST_RECONCILIATION_BUILD table for every run of the load |
3259 | |
3260 | Called from : Start_accrual_load Procedure |
3261 | |
3262 | Parameters : |
3263 | IN : p_operating_unit IN NUMBER REQUIRED |
3264 | p_from_date IN VARCHAR2 can be NULL |
3265 | p_to_date IN VARCHAR2 can be NULL |
3266 | |
3267 | OUT : x_return_status OUT NOCOPY VARCHAR2 |
3268 | x_msg_count OUT NOCOPY NUMBER |
3269 | x_msg_data OUT NOCOPY VARCHAR2 |
3273
3270 | |
3271 | NOTES : None |
3272 +===========================================================================*/
3274
3275 Procedure Insert_build_parameters(p_operating_unit IN NUMBER,
3276 p_from_date IN DATE,
3277 p_to_date IN DATE,
3278 x_msg_count OUT NOCOPY NUMBER,
3279 x_msg_data OUT NOCOPY VARCHAR2,
3280 x_return_status OUT NOCOPY VARCHAR2
3281 )
3282
3283 IS
3284
3285 l_stmt_num NUMBER;
3286
3287 l_api_name CONSTANT VARCHAR2(30) := 'Insert_build_parameters';
3288 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3289 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
3290
3291 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
3292 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
3293 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
3294 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3295 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3296
3297 BEGIN
3298 debug('Insert_build_parameters+');
3299 debug(' p_operating_unit : ' || p_operating_unit);
3300 debug(' p_from_date : ' || to_char(p_from_date,'DD-MON-YYYY HH24:MI:SS'));
3301 debug(' p_to_date : ' || to_char(p_to_date,'DD-MON-YYYY HH24:MI:SS'));
3302
3303 l_stmt_num := 10;
3304 debug(' l_stmt_num :'||l_stmt_num);
3305 x_return_status := fnd_api.g_ret_sts_success;
3306
3307
3308 INSERT into CST_RECONCILIATION_BUILD(
3309 build_id,
3310 operating_unit_id,
3311 -- HYU: at the from run this is the main xla upgrade date for the OU
3312 from_date,
3313 to_date,
3314 last_update_date,
3315 last_updated_by,
3316 last_update_login,
3317 creation_date,
3318 created_by,
3319 request_id,
3320 program_id,
3321 program_application_id,
3322 program_update_date)
3323 values(
3324 cst_reconciliation_build_s.nextval,
3325 p_operating_unit,
3326 p_from_date,
3327 p_to_date,
3328 sysdate,
3329 FND_GLOBAL.USER_ID,
3330 FND_GLOBAL.USER_ID,
3331 sysdate,
3332 FND_GLOBAL.USER_ID,
3333 FND_GLOBAL.CONC_REQUEST_ID,
3334 FND_GLOBAL.CONC_PROGRAM_ID,
3335 FND_GLOBAL.PROG_APPL_ID,
3336 sysdate);
3337
3338 debug('Insert_build_parameters-');
3339
3340 EXCEPTION
3341
3342 WHEN OTHERS THEN
3343 rollback;
3344 x_return_status := FND_API.g_ret_sts_unexp_error ;
3345 fnd_message.set_name('BOM','CST_UNEXPECTED');
3346 fnd_message.set_token('TOKEN',substr(SQLERRM,1,180));
3347 debug('EXCEPTION OTHERS in Insert_build_parameters '||substrb(SQLERRM,1,140));
3348 debug('l_stmt_num :'||l_stmt_num);
3349 fnd_msg_pub.add;
3350 FND_MSG_PUB.count_and_get
3351 ( p_count => x_msg_count
3352 , p_data => x_msg_data
3353 );
3354
3355 END Insert_build_parameters;
3356
3357 /*===========================================================================+
3358 | |
3359 | Procedure Name : Load_ap_po_data |
3360 | |
3361 | Purpose : This Procedure loads all the PO, regular AP and write off |
3362 | data into the reconciliation table by looking at the |
3363 | transaction table. |
3364 | All the effect PO_DISTRIBUTION_IDs are identified and then|
3365 | the transaction information for this PO distribution is |
3366 | built all over.If the total transaction amount for the |
3367 | PO_DISTRIBUTION_ID balances out to zero, the txns |
3368 | against it are not inserted into the reconciliation table.|
3369 | |
3370 | Called from : Start_accrual_load Procedure |
3371 | |
3372 | Parameters : |
3373 | IN : p_operating_unit IN NUMBER REQUIRED |
3374 | p_from_date IN VARCHAR2 can be NULL |
3375 | p_to_date IN VARCHAR2 can be NULL |
3376 | p_round_unit IN NUMBER REQUIRED |
3377 | |
3378 | OUT : x_return_status OUT NOCOPY VARCHAR2 |
3379 | x_msg_count OUT NOCOPY NUMBER |
3380 | x_msg_data OUT NOCOPY VARCHAR2 |
3381 | |
3382 | NOTES : None |
3383 | 12-Aug-2008 Pmarada bug Added code for OPM Financials to support AP PO |
3384 | reconciliation in R12, bug6995413 |
3385 | 21-nov-2008 pmarada, bug7516621, added nvl to the inventory_item_id in |
3386 | insert cst_reconciliation_gtt where clause, for expanse PO |
3390 | 22-Feb-2013 Uday Phadtare Bug 16277734.
3387 | 05-Jul-2012 Uday Phadtare Bug 13630026.
3388 | Commented a join condition in the OPM query which is used for insering data into cst_reconciliation_gtt
3389 | so that all OPM receipt related data is loaded even if that join condition fails.
3391 | Reinstated the join condition gel.line_id = xdl.source_distribution_id_num_1.
3392 +===========================================================================*/
3393
3394 Procedure Load_ap_po_data(p_operating_unit IN VARCHAR2,
3395 p_from_date IN DATE,
3396 p_to_date IN DATE,
3397 p_round_unit IN NUMBER,
3398 x_msg_count OUT NOCOPY NUMBER,
3399 x_msg_data OUT NOCOPY VARCHAR2,
3400 x_return_status OUT NOCOPY VARCHAR2
3401 )
3402
3403 IS
3404
3405 l_stmt_num NUMBER;
3406 l_err_num NUMBER;
3407 l_err_code VARCHAR2(200);
3408 l_err_msg VARCHAR2(2000);
3409 l_build_id NUMBER;
3410 l_last_update_date DATE;
3411 l_last_updated_by NUMBER;
3412 l_last_update_login NUMBER;
3413 l_creation_date DATE;
3414 l_created_by NUMBER;
3415 l_request_id NUMBER;
3416 l_program_application_id NUMBER;
3417 l_program_id NUMBER;
3418 l_program_update_date DATE;
3419
3420 l_api_name CONSTANT VARCHAR2(30) := 'Load_ap_po_data';
3421 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
3422 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
3423
3424 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
3425 l_errorLog CONSTANT BOOLEAN := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
3426 l_exceptionLog CONSTANT BOOLEAN := l_errorLog and (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
3427 l_pLog CONSTANT BOOLEAN := l_exceptionLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
3428 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
3429
3430 /* The following cursor picks up PO distributions IDs that have had any activity
3431 (receipts or Invoices) against them recorded between the supplied from and to dates.Exclude consigned POs*/
3432 /* This query has been hinted based on suggestion from lguitterez,skoka of the perf team to avoid the bind peeking issue*/
3433
3434 CURSOR c_po_dists is
3435 SELECT /*+ LEADING(rt) USE_NL(rrs,pod) */
3436 DISTINCT pod.po_distribution_id
3437 FROM po_distributions_all pod,
3438 rcv_transactions rt,
3439 rcv_receiving_sub_ledger rrs
3440 WHERE pod.accrual_account_id is NOT NULL
3441 AND pod.accrue_on_receipt_flag = 'Y'
3442 AND rt.transaction_date between p_from_date AND p_to_date
3443 AND pod.org_id = p_operating_unit
3444 AND rrs.rcv_transaction_id = rt.transaction_id
3445 AND pod.po_distribution_id = rrs.reference3
3446 /* start added below sql for OPM Receiving data to insert the AP PO data, pmarada, bug6995413 */
3447 UNION
3448 SELECT /*+ LEADING(rt) USE_NL(grat,pod) */
3449 DISTINCT pod.po_distribution_id
3450 FROM po_distributions_all pod,
3451 rcv_transactions rt,
3452 gmf_rcv_accounting_txns grat
3453 WHERE pod.accrual_account_id is NOT NULL
3454 AND pod.accrue_on_receipt_flag = 'Y'
3455 /*Bug 12346836 - Replaced rt.transaction_date with grat.transaction_date here*/
3456 AND grat.transaction_date between p_from_date AND p_to_date
3457 AND pod.org_id = p_operating_unit
3458 AND grat.rcv_transaction_id = rt.transaction_id
3459 AND pod.po_distribution_id = grat.po_distribution_id
3460 AND grat.org_id = p_operating_unit
3461 /* End OPM Financials */
3462 UNION
3463 SELECT DISTINCT pod.po_distribution_id
3464 FROM po_distributions_all pod,
3465 rcv_accounting_events rae,
3466 rcv_receiving_sub_ledger rrs
3467 WHERE pod.accrual_account_id is NOT NULL
3468 AND pod.accrue_on_receipt_flag = 'Y'
3469 AND rae.transaction_date between p_from_date AND p_to_date
3470 AND pod.org_id = p_operating_unit
3471 AND rrs.accounting_event_id = rae.accounting_event_id
3472 AND rrs.reference3 = pod.po_distribution_id
3473 AND rae.event_source_id = rrs.rcv_transaction_id
3474 AND rae.event_type_id in (7,8,9,10)
3475 ----------------------
3476 --7 ADJUST_RECEIVE
3477 --8 ADJUST_DELIVER
3478 --9 LOGICAL_RECEIVE
3479 --10 LOGICAL_RETURN_TO_VENDOR
3480 ----------------------
3481 UNION
3482 SELECT DISTINCT aida.po_distribution_id
3483 FROM ap_invoice_distributions_all aida,
3484 po_distributions_all pod
3485 WHERE pod.accrual_account_id is NOT NULL
3486 AND aida.accounting_date between p_from_date AND p_to_date
3487 AND aida.po_distribution_id is NOT NULL
3488 AND aida.po_distribution_id = pod.po_distribution_id
3489 AND pod.accrue_on_receipt_flag = 'Y'
3490 AND aida.org_id = p_operating_unit
3491 AND pod.org_id = p_operating_unit
3492 AND NOT EXISTS
3493 (SELECT 1
3494 FROM po_releases_all pra
3495 WHERE pod.po_release_id is NOT NULL
3496 AND pra.org_id = p_operating_unit
3500 AND NOT EXISTS
3497 AND pra.po_release_id = pod.po_release_id
3498 AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
3499 )
3501 (
3502 SELECT 1
3503 FROM po_headers_all poh
3504 WHERE pod.po_release_id is NULL
3505 AND poh.org_id = p_operating_unit
3506 AND poh.po_header_id = pod.po_header_id
3507 AND NVL(poh.consigned_consumption_flag,'N') = 'Y'
3508 )
3509 UNION
3510 -- Consig can have po distribution ID stamped but should be excluded from AP PO Load
3511 --FP BUG10406951
3512 SELECT DISTINCT cwo.po_distribution_id
3513 FROM cst_write_offs cwo
3514 , po_distributions_all pod
3515 WHERE cwo.transaction_date between p_from_date AND p_to_date
3516 AND cwo.operating_unit_id = p_operating_unit
3517 AND cwo.po_distribution_id = pod.po_distribution_id
3518 AND NOT EXISTS
3519 (SELECT NULL
3520 FROM po_releases_all pra
3521 WHERE pod.po_release_id is NOT NULL
3522 AND pra.po_release_id = pod.po_release_id
3523 AND NVL(pra.consigned_consumption_flag,'N') = 'Y'
3524 )
3525 AND NOT EXISTS
3526 (
3527 SELECT NULL
3528 FROM po_headers_all poh
3529 WHERE pod.po_release_id is NULL
3530 AND poh.po_header_id = pod.po_header_id
3531 AND NVL(poh.consigned_consumption_flag,'N') = 'Y'
3532 );
3533
3534
3535 TYPE dists_table is TABLE OF po_distributions_all.po_distribution_id%TYPE;
3536
3537 po_dists_tab dists_table;
3538
3539 CURSOR c_opm_count IS
3540 SELECT 1 FROM gmf_rcv_accounting_txns
3541 WHERE org_id = p_operating_unit
3542 AND rownum <2 ;
3543 l_opm_count NUMBER;
3544
3545 BEGIN
3546 debug('Load_ap_po_data+');
3547 debug(' p_operating_unit : ' || p_operating_unit);
3548 debug(' p_from_date : ' || to_char(p_from_date,'DD-MON-YYYY HH24:MI:SS'));
3549 debug(' p_to_date : ' || to_char(p_to_date,'DD-MON-YYYY HH24:MI:SS'));
3550 debug(' p_round_unit : ' || p_round_unit);
3551
3552 /* Start inserting PO data into the GTT */
3553
3554 l_stmt_num := 40;
3555 debug(' l_stmt_num :' ||l_stmt_num);
3556
3557 /* Get all the CONC WHO columns */
3558
3559 SELECT crb.build_id,
3560 crb.last_update_date,
3561 crb.last_updated_by,
3562 crb.last_update_login,
3563 crb.creation_date,
3564 crb.created_by,
3565 crb.request_id,
3566 crb.program_application_id,
3567 crb.program_id,
3568 crb.program_update_date
3569 INTO l_build_id,
3570 l_last_update_date,
3571 l_last_updated_by,
3572 l_last_update_login,
3573 l_creation_date,
3574 l_created_by,
3575 l_request_id,
3576 l_program_application_id,
3577 l_program_id,
3578 l_program_update_date
3579 FROM cst_reconciliation_build crb
3580 WHERE crb.request_id = FND_GLOBAL.CONC_REQUEST_ID;
3581 debug(' l_build_id :'||l_build_id);
3582
3583 OPEN c_po_dists;
3584 LOOP
3585
3586 FETCH c_po_dists BULK COLLECT INTO po_dists_tab LIMIT 5000;
3587
3588 l_stmt_num := 50;
3589 debug(' l_stmt_num :'||l_stmt_num);
3590 debug(' Inserting data from RRS into the global temp table' );
3591 xla_security_pkg.set_security_context(p_application_id => 707);
3592
3593 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
3594
3595 INSERT into cst_reconciliation_gtt
3596 (
3597 Transaction_date,
3598 Amount,
3599 Entered_amount,
3600 Quantity,
3601 Currency_code,
3602 Currency_conversion_type,
3603 Currency_conversion_rate,
3604 Currency_conversion_date,
3605 Po_distribution_id,
3606 Rcv_transaction_id,
3607 Invoice_distribution_id,
3608 Accrual_account_id,
3609 Transaction_type_code,
3610 Inventory_item_id,
3611 Vendor_id,
3612 Inventory_organization_id,
3613 Write_off_id,
3614 Destination_type_code,
3615 Operating_unit_id,
3616 Build_id,
3617 Request_id,
3618 Ae_header_id,
3619 Ae_line_num
3620 )
3621 SELECT /*+ Leading(POD,POL,POH,RRS,XDL) INDEX(RRS RCV_RECEIVING_SUB_LEDGER_N2)*/
3622 rrs.transaction_date,
3623 ROUND((NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0))
3624 / p_round_unit) * p_round_unit,
3625 ROUND((NVL(xal.entered_dr,0) - NVL(xal.entered_cr,0))
3626 / p_round_unit) * p_round_unit,
3627 DECODE(rae.event_type_id,
3628 7,NULL,
3629 8,NULL,
3630 ABS(ROUND(NVL(rrs.source_doc_quantity,NVL(rct.source_doc_quantity,0)),20)) *
3631 DECODE(xal.accounted_dr,NULL,-1 * sign(xal.accounted_cr),sign(xal.accounted_dr)) /* Bug 6913157: Pre-R12, In RRSL, sometimes accounted_dr / cr could be negative, in which case */
3635 xal.currency_conversion_rate,
3632 ),
3633 xal.currency_code,
3634 xal.currency_conversion_type,
3636 xal.currency_conversion_date,
3637 pod.po_distribution_id,
3638 xte.source_id_int_1,
3639 NULL, /* Invoice_distribution_id for PO receipts */
3640 xal.code_combination_id,
3641 DECODE(rae.event_type_id,
3642 7,'ADJUST RECEIVE',
3643 8,'ADJUST DELIVER',
3644 rct.transaction_type
3645 ),
3646 pol.item_id,
3647 poh.vendor_id,
3648 NVL(rct.organization_id,p_operating_unit),
3649 NULL, -- Write_off_id
3650 pod.destination_type_code,
3651 p_operating_unit,
3652 l_build_id,
3653 l_request_id,
3654 xal.ae_header_id,
3655 xal.ae_line_num
3656 FROM rcv_transactions rct,
3657 rcv_accounting_events rae,
3658 rcv_receiving_sub_ledger rrs,
3659 xla_ae_headers xah,
3660 xla_ae_lines xal,
3661 xla_transaction_entities_upg xte,
3662 xla_distribution_links xdl,
3663 po_headers_all poh,
3664 po_lines_all pol,
3665 po_distributions_all pod,
3666 cst_accrual_accounts caa,
3667 financials_system_params_all fsp
3668 WHERE fsp.org_id = p_operating_unit
3669 AND xah.ledger_id = fsp.set_of_books_id
3670 AND xah.application_id = 707
3671 AND xal.application_id = 707
3672 AND xte.application_id = 707
3673 AND xdl.application_id = 707
3674 AND xal.code_combination_id = caa.accrual_account_id
3675 AND caa.operating_unit_id = p_operating_unit
3676 AND xah.ae_header_id = xal.ae_header_id
3677 AND xah.gl_transfer_status_code = 'Y'
3678 AND xte.entity_id = xah.entity_id
3679 AND xte.ledger_id = fsp.set_of_books_id
3680 AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'
3681 AND xdl.ae_header_id = xal.ae_header_id
3682 AND xdl.ae_line_num = xal.ae_line_num
3683 AND xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'
3684 AND rct.transaction_id = NVL(xte.source_id_int_1,(-99))
3685 AND rct.source_document_code <> 'REQ'
3686 AND rct.transaction_date <= p_to_date /* Added for bug 6913157 */
3687 AND rrs.transaction_date <= p_to_date /* Bug# 12751777 Added to prevent future reto adj txns */
3688 AND rct.transaction_id = rrs.rcv_transaction_id
3689 AND rae.rcv_transaction_id(+) = rrs.rcv_transaction_id
3690 AND rae.accounting_event_id(+) = rrs.accounting_event_id
3691 AND rrs.rcv_sub_ledger_id = xdl.source_distribution_id_num_1
3692 AND pod.org_id = p_operating_unit
3693 AND pod.po_distribution_id = rrs.reference3
3694 AND pod.po_distribution_id = po_dists_tab(indx)
3695 AND rrs.reference3 = to_char(po_dists_tab(indx))
3696 AND pol.po_line_id = pod.po_line_id
3697 AND poh.po_header_id = pol.po_header_id
3698 AND NVL(rrs.accounting_line_type,'Accrual') <> 'Landed Cost Absorption'; -- LCM Change
3699
3700 debug(' Done Inserting the receipt information into the accrual table');
3701
3702 /* Start inserting OPM Financials reconciliation data, pmarada, bug6995413 */
3703 OPEN c_opm_count;
3704 FETCH c_opm_count INTO l_opm_count;
3705 CLOSE c_opm_count;
3706 debug(' l_opm_count :'||l_opm_count);
3707
3708 IF l_opm_count > 0 THEN
3709 l_stmt_num := 55;
3710 debug(' l_stmt_num :'||l_stmt_num);
3711 xla_security_pkg.set_security_context(p_application_id => 555);
3712
3713 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
3714
3715 INSERT into cst_reconciliation_gtt
3716 (
3717 Transaction_date,
3718 Amount,
3719 Entered_amount,
3720 Quantity,
3721 Currency_code,
3722 Currency_conversion_type,
3723 Currency_conversion_rate,
3724 Currency_conversion_date,
3725 Po_distribution_id,
3726 Rcv_transaction_id,
3727 Invoice_distribution_id,
3728 Accrual_account_id,
3729 Transaction_type_code,
3730 Inventory_item_id,
3731 Vendor_id,
3732 Inventory_organization_id,
3733 Write_off_id,
3734 Destination_type_code,
3735 Operating_unit_id,
3736 Build_id,
3737 Request_id,
3738 Ae_header_id,
3739 Ae_line_num
3740 )
3741 SELECT /*+ Leading(POD)*/
3742 grat.transaction_date,
3743 ROUND((NVL(xal.accounted_dr,0) - NVL(xal.accounted_cr,0))
3744 / p_round_unit) * p_round_unit,
3745 ROUND((NVL(xal.entered_dr,0) - NVL(xal.entered_cr,0))
3746 / p_round_unit) * p_round_unit,
3747 DECODE(grat.event_type,
3748 7,NULL,
3749 8,NULL,
3753 xal.currency_code,
3750 ABS(ROUND(NVL(grat.source_doc_quantity,NVL(rct.source_doc_quantity,0)),20)) *
3751 DECODE(xal.accounted_dr,NULL,-1 * sign(xal.accounted_cr),sign(xal.accounted_dr))
3752 ),
3754 xal.currency_conversion_type,
3755 xal.currency_conversion_rate,
3756 xal.currency_conversion_date,
3757 pod.po_distribution_id,
3758 grat.rcv_transaction_id,
3759 NULL, /* Invoice_distribution_id for PO receipts */
3760 xal.code_combination_id,
3761 DECODE(grat.event_type,
3762 7,'ADJUST RECEIVE', /* RECEIVING_ADJUST_RECEIVE */
3763 8,'ADJUST DELIVER', /* RECEIVING_ADJUST_DELIVER */
3764 rct.transaction_type
3765 ),
3766 pol.item_id,
3767 poh.vendor_id,
3768 NVL(rct.organization_id,p_operating_unit),
3769 NULL, -- Write_off_id
3770 pod.destination_type_code,
3771 p_operating_unit,
3772 l_build_id,
3773 l_request_id,
3774 xal.ae_header_id,
3775 xal.ae_line_num
3776 FROM rcv_transactions rct,
3777 gmf_rcv_accounting_txns grat,
3778 gmf_xla_extract_headers geh,
3779 gmf_xla_extract_lines gel,
3780 xla_ae_headers xah,
3781 xla_ae_lines xal,
3782 xla_transaction_entities_upg xte,
3783 xla_distribution_links xdl,
3784 po_headers_all poh,
3785 po_lines_all pol,
3786 po_distributions_all pod,
3787 cst_accrual_accounts caa,
3788 financials_system_params_all fsp
3789 WHERE fsp.org_id = p_operating_unit
3790 AND xah.ledger_id = fsp.set_of_books_id
3791 AND xah.application_id = 555
3792 AND xal.application_id = 555
3793 AND xte.application_id = 555
3794 AND xdl.application_id = 555
3795 AND xal.code_combination_id = caa.accrual_account_id
3796 AND caa.operating_unit_id = p_operating_unit
3797 AND xah.ae_header_id = xal.ae_header_id
3798 AND xah.gl_transfer_status_code = 'Y'
3799 AND xte.entity_id = xah.entity_id
3800 AND xte.ledger_id = fsp.set_of_books_id
3801 AND xte.entity_code = 'PURCHASING'
3802 AND xdl.ae_header_id = xah.ae_header_id
3803 AND xdl.ae_line_num = xal.ae_line_num
3804 AND xdl.source_distribution_type = 'PURCHASING'
3805 AND grat.accounting_txn_id = NVL(xte.source_id_int_1,(-99))
3806 AND rct.source_document_code <> 'REQ'
3807 /* Bug 12346836 - Replaced rct.transaction_date with grat.transaction_date in below condition*/
3808 AND grat.transaction_date <= p_to_date /* Added for bug 6913157 */
3809 AND rct.transaction_id = grat.rcv_transaction_id
3810 AND geh.transaction_id = grat.accounting_txn_id
3811 /*Commented this condition as a part of bug 7640489*/
3812 --AND geh.operating_unit = p_operating_unit
3813 AND geh.source_line_id = grat.rcv_transaction_id
3814 AND nvl(geh.inventory_item_id,0) = nvl(grat.inventory_item_id,0) --for expanse item pos added nvl, bug 7516621
3815 AND geh.organization_Id = grat.organization_Id
3816 AND geh.header_id = gel.header_id
3817 AND geh.event_id = gel.event_id
3818 AND gel.line_id = xdl.source_distribution_id_num_1 /* Bug 16277734 join condition reinstated */
3819 AND gel.journal_line_type = 'AAP'
3820 AND pod.org_id = p_operating_unit
3821 AND pod.po_distribution_id = grat.po_distribution_id
3822 AND pod.po_distribution_id = po_dists_tab(indx)
3823 AND grat.po_distribution_id = to_char(po_dists_tab(indx))
3824 AND pol.po_line_id = pod.po_line_id
3825 AND poh.po_header_id = pol.po_header_id ;
3826
3827 debug(' Done Inserting the OPM related receipt information into the accrual table');
3828 END IF;
3829 /* End OPM Financials code, pmarada */
3830
3831 l_stmt_num := 60;
3832 debug(' l_stmt_num :'||l_stmt_num);
3833 xla_security_pkg.set_security_context(p_application_id => 200);
3834
3835 /* Start Inserting AP Data */
3836
3837 /* The IPV and ERV lines will have a po_dist_id against them in AIDA. SO we need to handle
3838 them seperately.*/
3839 /* When AP creates accounting, it is possible for the line types to be merged thereby creating a summarized
3840 line in XAL.So one line in XAL can point to one or more lines in XDL (i.e one or different invoice distributions.
3841 So we need to pick up the amount from XDL from the unrounded columns.But even though the columns are called unrounded,
3842 they are actually rounded amounts since AP always passes rounded amounts to SLA and no further rounding in SLA is
3843 possible. */
3844
3845 debug(' Inserting the AP data into the accrual table');
3846 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
3847
3848 Insert into cst_reconciliation_gtt
3849 (
3853 Quantity,
3850 transaction_date,
3851 Amount,
3852 Entered_amount,
3854 Currency_code,
3855 Currency_conversion_type,
3856 Currency_conversion_rate,
3857 Currency_conversion_date,
3858 po_distribution_id,
3859 rcv_transaction_id,
3860 invoice_distribution_id,
3861 accrual_account_id,
3862 transaction_type_code,
3863 Inventory_item_id,
3864 vendor_id,
3865 Inventory_organization_id,
3866 Write_off_id,
3867 destination_type_code,
3868 Operating_unit_id,
3869 build_id,
3870 request_id,
3871 Ae_header_id,
3872 Ae_line_num
3873 )
3874 SELECT /*+ Leading(AIDA,POD,POL,POH,XDL)*/
3875 aida.accounting_date,
3876 ROUND((NVL(xdl.unrounded_accounted_dr,0) - NVL(xdl.unrounded_accounted_cr,0)) / p_round_unit) * p_round_unit,
3877 ROUND((NVL(xdl.unrounded_entered_dr,0) - NVL(xdl.unrounded_entered_cr,0)) / p_round_unit) * p_round_unit,
3878 decode(aida.corrected_invoice_dist_id,
3879 NULL, decode(aida.quantity_invoiced,
3880 NULL, NULL,
3881 inv_convert.inv_um_convert(
3882 pol.item_id,
3883 20,
3884 round(aida.quantity_invoiced,20),
3885 NULL,
3886 NULL,
3887 NVL(aida.MATCHED_UOM_LOOKUP_CODE,pol.unit_meas_lookup_code),
3888 pol.unit_meas_lookup_code
3889 )
3890 ),
3891 NULL
3892 ),
3893 xal.currency_code,
3894 xal.currency_conversion_type,
3895 xal.currency_conversion_rate,
3896 xal.currency_conversion_date,
3897 aida.po_distribution_id,
3898 aida.rcv_transaction_id,
3899 aida.invoice_distribution_id,
3900 xal.code_combination_id,
3901 Decode(aida.rcv_transaction_id,
3902 NULL,'AP PO MATCH',
3903 'AP RECEIPT MATCH'
3904 ),
3905 pol.item_id,
3906 poh.vendor_id, /* -- Changed from apia.vendor_id to poh.vendor_id. Bug 7312170 */
3907 NULL, --- Inventory_organization
3908 NULL, --Write off ID
3909 pod.destination_type_code,
3910 p_operating_unit,
3911 l_build_id,
3912 l_request_id,
3913 xal.ae_header_id,
3914 xal.ae_line_num
3915 FROM
3916 --{BUG#8410174
3917 (SELECT 'APID' tn
3918 ,invoice_id
3919 ,invoice_distribution_id
3920 ,accounting_date
3921 ,corrected_invoice_dist_id
3922 ,quantity_invoiced
3923 ,MATCHED_UOM_LOOKUP_CODE
3924 ,po_distribution_id
3925 ,rcv_transaction_id
3926 ,LINE_TYPE_LOOKUP_CODE
3930 UNION ALL
3927 ,org_id
3928 FROM ap_invoice_distributions_all
3929 WHERE po_distribution_id = po_dists_tab(indx)
3931 SELECT 'APSTD' tn
3932 ,invoice_id
3933 ,invoice_distribution_id
3934 ,accounting_date
3935 ,corrected_invoice_dist_id
3936 ,quantity_invoiced
3937 ,MATCHED_UOM_LOOKUP_CODE
3938 ,po_distribution_id
3939 ,rcv_transaction_id
3940 ,LINE_TYPE_LOOKUP_CODE
3941 ,org_id
3942 FROM ap_self_assessed_tax_dist_all
3943 WHERE po_distribution_id = po_dists_tab(indx)) aida,
3944 -- ap_invoice_distributions_all aida,
3945 --}
3946 xla_ae_headers xah,
3947 xla_ae_lines xal,
3948 xla_distribution_links xdl,
3949 po_lines_all pol,
3950 po_distributions_all pod,
3951 cst_accrual_accounts caa,
3952 financials_system_params_all fsp,
3953 xla_transaction_entities_upg xte,
3954 po_headers_all poh /* -- Changes to pick Vendor from PO instead of APIA. Bug 7312170 */
3955 WHERE xal.code_combination_id = caa.accrual_account_id
3956 AND caa.operating_unit_id = p_operating_unit
3957 AND fsp.org_id = p_operating_unit
3958 AND fsp.set_of_books_id = xah.ledger_id
3959 AND xah.application_id = 200
3960 AND xal.application_id = 200
3961 AND xdl.application_id = 200
3962 AND xte.application_id = 200
3963 AND xah.ae_header_id = xal.ae_header_id
3964 AND xah.gl_transfer_status_code = 'Y'
3965 AND xdl.ae_header_id = xal.ae_header_id
3966 AND xdl.source_distribution_type = 'AP_INV_DIST'
3967 AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
3968 AND xdl.ae_line_num = xal.ae_line_num
3969 AND aida.org_id = p_operating_unit
3970 AND aida.accounting_date <= p_to_date /* Added for bug 6913157 */
3971 AND xte.entity_id = xah.entity_id
3972 AND xte.ledger_id = fsp.set_of_books_id
3973 AND NVL(xte.source_id_int_1,(-99)) = aida.invoice_id
3974 AND xte.entity_code = 'AP_INVOICES'
3975 AND xal.accounting_class_code NOT IN ('IPV','EXCHANGE_RATE_VARIANCE','LIABILITY','TIPV','TRV','TERV')
3976 AND xah.balance_type_code = 'A'
3977 -- AND aida.invoice_id = apia.invoice_id
3978 AND aida.po_distribution_id = pod.po_distribution_id
3979 AND pol.po_line_id = pod.po_line_id
3980 AND poh.po_header_id = pol.po_header_ID;
3981
3982 debug(' Done Inserting the AP data into the accrual table');
3983
3984
3985 l_stmt_num := 70;
3986 debug(' l_stmt_num :'||l_stmt_num);
3987
3988 /* Insert Write Off data from Write Off tables */
3989 debug(' Inserting the write off data into the accrual table');
3990
3991 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
3992
3993 Insert into cst_reconciliation_gtt
3994 (
3995 transaction_date,
3996 amount,
3997 entered_amount,
3998 quantity,
3999 currency_code,
4000 currency_conversion_type,
4001 currency_conversion_rate,
4002 currency_conversion_date,
4003 po_distribution_id,
4004 rcv_transaction_id,
4005 invoice_distribution_id,
4006 accrual_account_id,
4007 transaction_type_code,
4008 inventory_item_id,
4009 vendor_id,
4010 inventory_organization_id,
4011 write_off_id,
4012 destination_type_code,
4013 operating_unit_id,
4014 build_id,
4015 request_id
4016 )
4017 SELECT /*+ INDEX(CWO CST_WRITE_OFFS_N5)*/
4018 cwo.transaction_date,
4019 cwo.write_off_amount,
4020 cwo.entered_amount,
4021 NULL, -- quantity for write off is NULL
4022 cwo.currency_code,
4023 cwo.currency_conversion_type,
4024 cwo.currency_conversion_rate,
4025 cwo.currency_conversion_date,
4026 cwo.po_distribution_id,
4027 NULL, -- rcv_transaction_id
4028 NULL, -- invoice_distribution_id
4029 cwo.accrual_account_id,
4030 cwo.transaction_type_code,
4031 cwo.inventory_item_id,
4032 poh.vendor_id, -- immunization for vendor
4033 NULL, -- Inventory Organization ID
4034 cwo.write_off_id,
4035 --cwo.destination_type_code,
4036 pod.destination_type_code,
4037 cwo.operating_unit_id,
4041 --{ Immunization for po_vendor merge
4038 l_build_id,
4039 l_request_id
4040 FROM cst_write_offs cwo,
4042 po_distributions_all pod,
4043 po_headers_all poh
4044 --}
4045 WHERE cwo.po_distribution_id is NOT NULL
4046 AND cwo.operating_unit_id = p_operating_unit
4047 AND cwo.inventory_transaction_id is NULL -- do not pick up old deliver txns
4048 AND cwo.transaction_date <= p_to_date /* Added for bug 6913157 */
4049 AND cwo.po_distribution_id = po_dists_tab(indx)
4050 --
4051 -- BUG#9098164
4052 -- The consignmnet transfer AP invoices are being shown in AP PO report
4053 -- hence the AP consignmnet transfer balance write off needs to be considered in AP PO load
4054 --
4055 -- AND cwo.invoice_distribution_id is NULL -- This will guarantee that we do not pick IPV/ERV/consigned stuff
4056 --
4057 --{immunization for po_vendor
4058 AND cwo.po_distribution_id = pod.po_distribution_id
4059 AND pod.po_header_id = poh.po_header_id;
4060 --}
4061 debug(' Done Inserting the write off data into the accrual table');
4062
4063 l_stmt_num := 70;
4064 debug(' l_stmt_num :'||l_stmt_num);
4065
4066 /* Update the summary table now for each po_dist_id. First delete the current info from the table
4067 and then update it with the latest data */
4068
4069 debug(' deletion from cst_reconciliation_summary');
4070
4071 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
4072 DELETE from cst_reconciliation_summary crs
4073 WHERE crs.operating_unit_id = p_operating_unit
4074 AND crs.po_distribution_id = po_dists_tab(indx);
4075
4076 debug(' Done Deleting from CRS');
4077
4078
4079 l_stmt_num := 75;
4080 debug(' l_stmt_num :' ||l_stmt_num);
4081
4082 /* There will be a hint added to force the GTT to use the index. This is required as the
4083 Temp table is a global temp table */
4084
4085 debug(' populating CRS from cst_reconciliation_gtt');
4086
4087 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
4088
4089 Insert into CST_RECONCILIATION_SUMMARY
4090 (
4091 po_distribution_id,
4092 accrual_account_id,
4093 po_balance,
4094 ap_balance,
4095 write_off_balance,
4096 last_receipt_date,
4097 last_invoice_dist_date,
4098 last_write_off_date,
4099 inventory_item_id,
4100 vendor_id,
4101 destination_type_code,
4102 operating_unit_id,
4103 build_id,
4104 last_update_date,
4105 last_updated_by,
4106 last_update_login,
4107 creation_date,
4108 created_by,
4109 request_id,
4110 program_application_id,
4111 program_id,
4112 program_update_date
4113 )
4114 SELECT /*+ INDEX(gtt, cst_reconciliation_gtt_n1) */
4115 gtt.po_distribution_id,
4116 gtt.accrual_account_id,
4117 SUM(decode(gtt.invoice_distribution_id,
4118 NULL,Decode(gtt.write_off_id,
4119 NULL,gtt.amount,
4120 0
4121 ),
4122 0
4123 )
4124 ),
4125 SUM(decode(gtt.invoice_distribution_id,
4126 NULL,0,
4127 gtt.amount
4128 )
4129 ),
4130 SUM(decode(gtt.write_off_id,
4131 NULL,0,
4132 gtt.amount
4133 )
4134 ),
4135 MAX(decode(gtt.invoice_distribution_id,
4136 NULL,Decode(gtt.write_off_id,
4137 NULL,gtt.transaction_date,
4138 NULL
4139 ),
4140 NULL
4141 )
4142 ),
4143 MAX(decode(gtt.invoice_distribution_id,
4144 NULL,NULL,
4145 gtt.transaction_date
4146 )
4147 ),
4148 MAX(decode(gtt.write_off_id,
4149 NULL,NULL,
4150 gtt.transaction_date
4151 )
4152 ),
4153 gtt.inventory_item_id,
4154 gtt.vendor_id,
4155 gtt.destination_type_code,
4156 gtt.operating_unit_id,
4157 l_build_id,
4158 l_last_update_date,
4159 l_last_updated_by,
4160 l_last_update_login,
4161 l_creation_date,
4162 l_created_by,
4163 l_request_id,
4164 l_program_application_id,
4165 l_program_id,
4166 l_program_update_date
4167 FROM cst_reconciliation_gtt gtt
4171 AND gtt.request_id = FND_GLOBAL.CONC_REQUEST_ID
4168 WHERE gtt.operating_unit_id = p_operating_unit
4169 AND gtt.po_distribution_id = po_dists_tab(indx)
4170 AND gtt.build_id = l_build_id
4172 GROUP BY
4173 gtt.po_distribution_id,
4174 gtt.accrual_account_id,
4175 gtt.inventory_item_id,
4176 gtt.vendor_id,
4177 gtt.destination_type_code,
4178 gtt.operating_unit_id,
4179 l_build_id,
4180 l_last_update_date,
4181 l_last_updated_by,
4182 l_last_update_login,
4183 l_creation_date,
4184 l_created_by,
4185 l_request_id,
4186 l_program_application_id,
4187 l_program_id,
4188 l_program_update_date
4189 HAVING
4190 SUM(decode(gtt.invoice_distribution_id,
4191 NULL,Decode(gtt.write_off_id,
4192 NULL,gtt.amount,
4193 0
4194 ),
4195 0
4196 )
4197 ) +
4198 SUM(decode(gtt.invoice_distribution_id,
4199 NULL,0,
4200 gtt.amount
4201 )
4202 ) +
4203 SUM(decode(gtt.write_off_id,
4204 NULL,0,
4205 gtt.amount
4206 )
4207 ) <> 0 ;
4208
4209
4210 debug(' Done Inserting the new data into CRS from cst_reconciliation_gtt');
4211
4212 l_stmt_num := 80;
4213 debug(' l_stmt_num :' ||l_stmt_num);
4214
4215 /* Delete all transactions details from the AP/PO details table for those rows that belong to
4216 the current po_dist_id */
4217
4218 debug(' deleting into cst_ap_po_reconciliation');
4219
4220 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
4221 DELETE from cst_ap_po_reconciliation capr
4222 WHERE capr.operating_unit_id = p_operating_unit
4223 AND capr.po_distribution_id = po_dists_tab(indx);
4224
4225 debug(' Done Deleting old data from CAPR');
4226
4227 l_stmt_num := 90;
4228
4229 /* insert into AP/PO table from GTT */
4230
4231 debug(' deleting into cst_ap_po_reconciliation');
4232
4233 FORALL indx in po_dists_tab.FIRST..po_dists_tab.LAST
4234 Insert into CST_AP_PO_RECONCILIATION
4235 (
4236 transaction_date,
4237 amount,
4238 entered_amount,
4239 quantity,
4240 currency_code,
4241 currency_conversion_type,
4242 currency_conversion_rate,
4243 currency_conversion_date,
4244 po_distribution_id,
4245 rcv_transaction_id,
4246 invoice_distribution_id,
4247 accrual_account_id,
4248 transaction_type_code,
4249 inventory_organization_id,
4250 write_off_id,
4251 operating_unit_id,
4252 build_id,
4253 last_update_date,
4254 last_updated_by,
4255 last_update_login,
4256 creation_date,
4257 created_by,
4258 request_id,
4259 program_application_id,
4260 program_id,
4261 program_update_date,
4262 Ae_header_id,
4263 Ae_line_num
4264 )
4265 SELECT gtt.transaction_date,
4266 gtt.amount,
4267 gtt.entered_amount,
4268 gtt.quantity,
4269 gtt.currency_code,
4270 gtt.currency_conversion_type,
4271 gtt.currency_conversion_rate,
4272 gtt.currency_conversion_date,
4273 gtt.po_distribution_id,
4274 gtt.rcv_transaction_id,
4275 gtt.invoice_distribution_id,
4276 gtt.accrual_account_id,
4277 gtt.transaction_type_code,
4278 gtt.inventory_organization_id,
4279 gtt.write_off_id,
4280 gtt.operating_unit_id,
4281 gtt.build_id,
4282 l_last_update_date,
4283 l_last_updated_by,
4284 l_last_update_login,
4285 l_creation_date,
4286 l_created_by,
4287 gtt.request_id,
4288 l_program_application_id,
4289 l_program_id,
4290 l_program_update_date,
4291 gtt.ae_header_id,
4292 gtt.ae_line_num
4293 FROM
4294 cst_reconciliation_gtt gtt
4295 WHERE gtt.operating_unit_id = p_operating_unit
4296 AND gtt.po_distribution_id = po_dists_tab(indx)
4297 AND EXISTS (
4298 SELECT 1
4299 FROM cst_reconciliation_summary crs
4300 WHERE crs.operating_unit_id = p_operating_unit
4301 AND crs.po_distribution_id = gtt.po_distribution_id
4302 AND crs.accrual_account_id = gtt.accrual_account_id
4303 );
4304 debug(' Done Inserting new data into CAPR');
4305
4306
4307 EXIT WHEN c_po_dists%notfound;
4308
4309 END LOOP; /* looping through po_dist_id */
4310 CLOSE c_po_dists;
4311
4312 debug('Load_ap_po_data-');
4313
4314 EXCEPTION
4315
4316 WHEN OTHERS THEN
4317 debug('EXCEPTION OTHERS in Load_ap_po_data ' || l_stmt_num || ' ' || substrb(SQLERRM,1,140));
4318 CLOSE c_po_dists;
4319 x_return_status := FND_API.g_ret_sts_unexp_error ;
4320 fnd_message.set_name('BOM','CST_UNEXPECTED');
4321 fnd_message.set_token('TOKEN',substr(SQLERRM,1,180));
4322 ROLLBACK;
4323 fnd_msg_pub.add;
4324
4325 FND_MSG_PUB.count_and_get
4326 ( p_count => x_msg_count
4327 , p_data => x_msg_data
4328 );
4329
4330 END Load_ap_po_data;
4331
4332
4333 END CST_ACCRUAL_LOAD;