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