[Home] [Help]
PACKAGE BODY: APPS.CST_SLA_UPDATE_PKG
Source
1 PACKAGE BODY CST_SLA_UPDATE_PKG AS
2 /* $Header: CSTPUPGB.pls 120.31.12020000.2 2012/07/11 13:13:00 vkatakam ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_SLA_UPDATE_PKG';
5 G_LOG_LEVEL CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
6 gUserId NUMBER := nvl(fnd_global.user_id, -888);
7 gLoginId NUMBER := nvl(fnd_global.login_id, -888);
8 gRequestId NUMBER := nvl(fnd_global.conc_request_id, -1);
9 gUpdateDate DATE := SYSDATE;
10 gLogError BOOLEAN := FALSE;
11
12 g_mrc_enabled boolean := TRUE;
13
14 ------------------------------------------------------------------------------------
15 -- API name : log_message
16 -- Type : Private
17 -- Function : Function to log messages into fnd_log_messages
18 --
19 -- Pre-reqs :
20 -- Parameters :
21 -- IN : X_fndlevel IN NUMBER
22 -- X_module IN VARCHAR2
23 -- X_message IN VARCHAR2
24 --
25 -- OUT :
26 --
27 -- Version : Initial version 1.0
28 -- Notes : The API is used for Error logging during Exception
29 --
30 -- End of comments
31 -------------------------------------------------------------------------------------
32 PROCEDURE log_message (
33 X_fndlevel IN NUMBER,
34 X_module IN VARCHAR2,
35 X_message IN VARCHAR2)
36 IS
37 BEGIN
38 IF X_fndlevel >= G_LOG_LEVEL AND NOT gLogError THEN
39 fnd_log.string( X_fndlevel, X_module, X_message);
40 END IF;
41 EXCEPTION
42 WHEN OTHERS THEN
43 gLogError := TRUE;
44 fnd_file.put_line(fnd_file.log, 'EXCEPTION: log_message: '||SQLERRM);
45 fnd_file.put_line(fnd_file.log, 'Disabling FND Logging..');
46 END log_message;
47
48 ------------------------------------------------------------------------------------
49 -- API name : handle_error
50 -- Type : Private
51 -- Function : Function to Log Errors, Exceptions depending upon the whether
52 -- called from Concurrent Request or Hot Patch
53 -- Pre-reqs :
54 -- Parameters :
55 -- IN : X_module IN VARCHAR2
56 -- X_message IN VARCHAR2
57 -- X_reqerror IN BOOLEAN
58 --
59 -- OUT : X_errbuf OUT NOCOPY VARCHAR2
60 --
61 -- Version : Initial version 1.0
62 -- Notes : The API is used for Error logging during Exception
63 --
64 -- End of comments
65 -------------------------------------------------------------------------------------
66 PROCEDURE handle_error (
67 X_module IN VARCHAR2,
68 X_message IN VARCHAR2,
69 X_reqerror IN BOOLEAN DEFAULT FALSE,
70 X_errbuf OUT NOCOPY VARCHAR2)
71 IS
72 l_conc_status BOOLEAN;
73 l_stmt_num NUMBER;
74 BEGIN
75
76 /* Error Logging depending upon whether called from
77 Concurrent Request or Hot Patch */
78
79 IF gRequestId > 0 THEN --Concurrent Request
80
81 l_stmt_num := 10;
82
83 --Print the Error in Request Log File
84 fnd_file.put_line(fnd_file.log, X_module||' => '||X_message);
85
86 l_stmt_num := 20;
87
88 --Set the Concurrent Request as Error, depending upon the parameter
89 IF X_reqerror THEN
90 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', X_errbuf);
91 END IF;
92
93 ELSE --Hot Patch
94
95 l_stmt_num := 30;
96
97 --Save the Error in FND Memory Variables
98 fnd_msg_pub.add_exc_msg (
99 p_pkg_name => 'CST_SLA_UPDATE_PKG',
100 p_procedure_name => SUBSTR(X_module, 30),
101 p_error_text => X_message
102 );
103
104 END IF;
105
106 X_errbuf := X_message;
107
108 EXCEPTION
109 WHEN OTHERS THEN
110 X_errbuf := 'UNEXPECTED: handle_error.'||l_stmt_num||':'||SQLERRM;
111 END handle_error;
112
113 ------------------------------------------------------------------------------------
114 -- API name : CST_Upgrade_Wrapper
115 -- Type : Public
116 -- Function : Wrapper to support XLA Concurrent mode Upgrade
117 --
118 -- Pre-reqs :
119 -- Parameters :
120 -- IN : X_batch_size in number default 10000,
121 -- X_Num_Workers in number default 16,
122 -- X_Num_Workers in number default 16,
123 -- X_ledger_id in number default null,
124 -- X_Application_Id in number default null
125 --
126 -- OUT : X_errbuf out NOCOPY varchar2,
127 -- X_retcode out NOCOPY varchar2
128 --
129 -- Version : Initial version 1.0
130 -- Notes : Wrapper to support XLA Concurrent mode Upgrade
131 --
132 -- End of comments
133 -------------------------------------------------------------------------------------
134
135 PROCEDURE CST_Upgrade_Wrapper (
136 X_errbuf out NOCOPY varchar2,
137 X_retcode out NOCOPY varchar2,
138 X_batch_size in number default 10000,
139 X_Num_Workers in number default 16,
140 X_Ledger_Id in number default null,
141 X_Application_Id in number default null)
142 IS
143
144 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper';
145 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
146
147 l_api_version NUMBER := 1.0;
148 l_init_msg_list VARCHAR2(1) := 'T';
149
150 l_stmt_num number;
151
152 l_inv_req_id number;
153 l_wip_req_id number;
154 l_rcv_req_id number;
155
156 l_sub_reqtab fnd_concurrent.requests_tab_type;
157 l_req_status boolean := TRUE;
158 l_subreq_wait boolean := TRUE;
159
160 l_reqid_count number;
161
162 l_ret_code varchar2(10);
163
164 BEGIN
165
166 IF l_Log THEN
167 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
168 ELSE
169 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
170 END IF;
171
172 l_stmt_num :=0;
173 IF l_Log THEN
174 log_message(
175 fnd_log.level_procedure,
176 l_module||'.begin',
177 'Entering CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper with '||
178 'X_batch_size = '||X_batch_size||','||
179 'X_Num_Workers = '||X_Num_Workers||','||
180 'X_ledger_id = '||X_ledger_id||','||
181 'X_Application_Id = '||X_Application_Id
182 );
183 END IF;
184
185 /* can not run two costing upgrade requests at the same time */
186 l_stmt_num :=5;
187 l_reqid_count := 0;
188
189 SELECT min(fcr.request_id)
190 into l_reqid_count
191 FROM fnd_concurrent_requests fcr,
192 fnd_concurrent_programs fcp
193 WHERE fcp.concurrent_program_name IN ('CSTSLAUM')
194 AND fcp.application_id = 702
195 AND fcr.concurrent_program_id = fcp.concurrent_program_id
196 AND fcr.program_application_id = fcp.application_id
197 AND fcr.phase_code IN ('I','P','R');
198
199 if (l_reqid_count <> 0 ) then
200 X_errbuf := 'Another Costing Upgrade Manager is running. Check Request: '||l_reqid_count;
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 end if;
203
204 IF X_Application_Id NOT IN (201, 401) THEN
205 X_errbuf := 'Incorrect Application ID. X_Application_Id = '||X_Application_Id;
206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
207 END IF;
208
209 x_retcode := FND_API.G_RET_STS_SUCCESS;
210
211 IF X_Application_Id = 401 THEN
212 l_stmt_num :=10;
213 l_inv_req_id := FND_REQUEST.submit_request(
214 application => 'BOM',
215 program => 'CSTUINVM',
216 description => NULL,
217 start_time => NULL,
218 --sub_request => TRUE,
219 argument1 => l_api_version,
220 argument2 => l_init_msg_list,
221 argument3 => X_batch_size,
222 argument4 => X_Num_Workers,
223 argument5 => X_Ledger_Id);
224
225 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> Upgrade Historical Inventory Transactions for Subledger Accounting [CSTUINVM] -> Request: '||l_inv_req_id);
226
227 l_stmt_num :=15;
228 l_wip_req_id := FND_REQUEST.submit_request(
229 application => 'BOM',
230 program => 'CSTUWIPM',
231 description => NULL,
232 start_time => NULL,
233 --sub_request => TRUE,
234 argument1 => l_api_version,
235 argument2 => l_init_msg_list,
236 argument3 => X_batch_size,
237 argument4 => X_Num_Workers,
238 argument5 => X_Ledger_Id);
239
240 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> Upgrade Historical Work In Process Transactions for Subledger Accounting [CSTUWIPM] -> Request: '||l_wip_req_id );
241
242 IF (l_inv_req_id = 0 AND l_wip_req_id = 0 ) THEN
243 X_errbuf := 'Failure in Sub-Request Submission';
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246
247 ELSIF X_Application_Id = 201 THEN
248
249 l_stmt_num :=20;
250 l_rcv_req_id := FND_REQUEST.submit_request(
251 application => 'BOM',
252 program => 'CSTURCVM',
253 description => NULL,
254 start_time => NULL,
255 --sub_request => TRUE,
256 argument1 => l_api_version,
257 argument2 => l_init_msg_list,
258 argument3 => X_batch_size,
259 argument4 => X_Num_Workers,
260 argument5 => X_Ledger_Id);
261
262 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> Upgrade Historical Receiving Transactions for Subledger Accounting [CSTURCVM] -> Request: '||l_rcv_req_id);
263
264 IF (l_rcv_req_id = 0 ) THEN
265 X_errbuf := 'Failure in Sub-Request Submission';
266 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
267 END IF;
268
269 END IF;
270
271 commit; --explicit important one
272
273 l_stmt_num :=30;
274
275 /* Wait for child requests to complete */
276 WHILE l_subreq_wait LOOP
277
278 dbms_lock.sleep(100); --Sleep for 100 seconds
279
280 IF X_Application_Id = 401 THEN
281 l_sub_reqtab(1).request_id := l_inv_req_id;
282 l_req_status := fnd_concurrent.get_request_status(request_id => l_sub_reqtab(1).request_id,
283 phase => l_sub_reqtab(1).phase,
284 status => l_sub_reqtab(1).status,
285 dev_phase => l_sub_reqtab(1).dev_phase,
286 dev_status => l_sub_reqtab(1).dev_status,
287 message => l_sub_reqtab(1).message);
288 l_sub_reqtab(2).request_id := l_wip_req_id;
289 l_req_status := fnd_concurrent.get_request_status(request_id => l_sub_reqtab(2).request_id,
290 phase => l_sub_reqtab(2).phase,
291 status => l_sub_reqtab(2).status,
292 dev_phase => l_sub_reqtab(2).dev_phase,
293 dev_status => l_sub_reqtab(2).dev_status,
297 ELSIF X_Application_Id = 201 THEN
294 message => l_sub_reqtab(2).message);
295
296
298 l_sub_reqtab(1).request_id := l_rcv_req_id;
299 l_req_status := fnd_concurrent.get_request_status(request_id => l_sub_reqtab(1).request_id,
300 phase => l_sub_reqtab(1).phase,
301 status => l_sub_reqtab(1).status,
302 dev_phase => l_sub_reqtab(1).dev_phase,
303 dev_status => l_sub_reqtab(1).dev_status,
304 message => l_sub_reqtab(1).message);
305 END IF;
306
307 l_subreq_wait := FALSE;
308
309 for i IN 1..l_sub_reqtab.COUNT() loop
310 if (l_sub_reqtab(i).dev_phase <> 'COMPLETE') then
311 l_subreq_wait := TRUE;
312 exit;
313 end if;
314 end loop;
315
316 END LOOP;
317
318 l_stmt_num :=40;
319
320 /* Checking whether the child requests completed succesful */
321 x_retcode := FND_API.G_RET_STS_SUCCESS;
322 for i IN 1..l_sub_reqtab.COUNT() loop
323 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
324 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Concurrent Request: '||l_sub_reqtab(i).request_id||' -> Status: '||l_sub_reqtab(i).dev_status);
325 X_retcode := FND_API.g_ret_sts_unexp_error;
326 end if;
327 end loop;
328
329 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
330 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>All Concurrent Sub-Requests Successfull');
331 l_stmt_num :=50;
332 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Setting Migration Status Code in GPS as ''U'' for Application ID '||X_Application_Id);
333 l_ret_code := XLA_UPGRADE_PUB.set_migration_status_code(
334 p_application_id =>X_Application_Id,
335 p_set_of_books_id=>null,
336 p_period_name =>null,
337 p_period_year =>null);
338 else
339 X_errbuf := 'One or More of Upgrade Sub-Managers completed in Error';
340 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
341 end if;
342
343 COMMIT;
344
345 IF l_Log THEN
346 log_message(
347 fnd_log.level_procedure,
348 l_module||'.end',
349 'Exiting CST_SLA_UPDATE_PKG.CST_Upgrade_Wrapper with '||
350 'X_errbuf = '||X_errbuf||','||
351 'X_retcode = '||X_retcode
352 );
353 END IF;
354
355 EXCEPTION
356
357 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
358 X_retcode := FND_API.g_ret_sts_unexp_error;
359 handle_error ( X_module => l_module||'.'||l_stmt_num,
360 X_message => 'ERROR: '||X_errbuf,
361 X_reqerror => FALSE,
362 X_errbuf => X_errbuf );
363
364 WHEN OTHERS THEN
365 X_retcode := FND_API.g_ret_sts_unexp_error;
366 handle_error ( X_module => l_module||'.'||l_stmt_num,
367 X_message => 'EXCEPTION: '||SQLERRM,
368 X_reqerror => FALSE,
369 X_errbuf => X_errbuf );
370
371 END CST_Upgrade_Wrapper;
372 ------------------------------------------------------------------------------------
373 -- API name : Update_Proc_MGR
374 -- Type : Public
375 -- Function : Manager process to launch three sub-managers that are
376 -- 1.Upgrade Historical Inventory Transactions for Subledger Accounting
377 -- 2.Upgrade Historical Work In Progress Transactions for Subledger Accounting
378 -- 3.Upgrade Historical Receiving Transactions for Subledger Accounting
379 -- Pre-reqs :
380 -- Parameters :
381 -- IN : X_api_version IN NUMBER,
382 -- X_init_msg_list IN VARCHAR2,
383 -- X_batch_size in number default 10000,
384 -- X_Num_Workers in number default 16,
385 -- X_Argument4 in varchar2 default null,
386 -- X_Argument5 in varchar2 default null,
387 -- X_Argument6 in varchar2 default null,
388 -- X_Argument7 in varchar2 default null,
389 -- X_Argument8 in varchar2 default null,
390 -- X_Argument9 in varchar2 default null,
391 -- X_Argument10 in varchar2 default null
392 --
393 -- OUT : X_errbuf out NOCOPY varchar2,
394 -- X_retcode out NOCOPY varchar2
395 -- Version : Initial version 1.0
396 -- Notes : The API is used for defining the "Upgrade Costing Subledgers to SLA"
397 -- manager Concurrent Executable and Concurrent Program.
398 --
399 -- End of comments
400 -------------------------------------------------------------------------------------
401
402 PROCEDURE Update_Proc_MGR (
403 X_errbuf out NOCOPY varchar2,
404 X_retcode out NOCOPY varchar2,
405 X_api_version IN NUMBER DEFAULT 1.0,
406 X_init_msg_list IN VARCHAR2 DEFAULT 'T',
407 X_batch_size in number default 10000,
408 X_Num_Workers in number default 16,
409 X_Argument4 in varchar2 default null,
410 X_Argument5 in varchar2 default null,
411 X_Argument6 in varchar2 default null,
412 X_Argument7 in varchar2 default null,
413 X_Argument8 in varchar2 default null,
414 X_Argument9 in varchar2 default null,
418 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_MGR';
415 X_Argument10 in varchar2 default NULL)
416 IS
417
419 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
420
421 l_stmt_num number;
422 l_api_name CONSTANT VARCHAR2(30) := 'Update_Proc_MGR';
423 l_api_version CONSTANT NUMBER := 1.0;
424
425 l_inv_req_id number;
426 l_wip_req_id number;
427 l_rcv_req_id number;
428
429 l_sub_reqtab fnd_concurrent.requests_tab_type;
430 req_data varchar2(10);
431 submit_req boolean;
432
433 l_prg_appid number;
434 l_program_name varchar2(15);
435 l_reqid_count number;
436
437 l_ret_code varchar2(10);
438
439 BEGIN
440
441 IF l_Log THEN
442 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
443 ELSE
444 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
445 END IF;
446
447 l_stmt_num :=0;
448 IF l_Log THEN
449 log_message(
450 fnd_log.level_procedure,
451 l_module||'.begin',
452 'Entering CST_SLA_UPDATE_PKG.Update_Proc_MGR with '||
453 'X_batch_size = '||X_batch_size||','||
454 'X_Num_Workers = '||X_Num_Workers||','||
455 'X_Argument4 = '||X_Argument4||','||
456 'X_Argument5 = '||X_Argument5||','||
457 'X_Argument6 = '||X_Argument6||','||
458 'X_Argument7 = '||X_Argument7||','||
459 'X_Argument8 = '||X_Argument8||','||
460 'X_Argument9 = '||X_Argument9||','||
461 'X_Argument10 = '||X_Argument10
462 );
463 END IF;
464
465 if gRequestId > 0 then
466 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Upgrade Costing Subledgers to SLA Request: '||gRequestId);
467 else
468 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
469 end if;
470
471 /* can not run two costing upgrade requests at the same time */
472 l_stmt_num :=5;
473 l_prg_appid := 702;
474 l_program_name := 'CSTSLAUM';
475 l_reqid_count := 0;
476
477 SELECT min(fcr.request_id)
478 into l_reqid_count
479 FROM fnd_concurrent_requests fcr,
480 fnd_concurrent_programs fcp
481 WHERE fcp.concurrent_program_name = l_program_name
482 AND fcp.application_id = l_prg_appid
483 AND fcr.concurrent_program_id = fcp.concurrent_program_id
484 AND fcr.program_application_id = fcp.application_id
485 AND fcr.phase_code IN ('I','P','R');
486
487 if (l_reqid_count <> gRequestId) then
488 X_errbuf := 'Another Costing Upgrade Manager is running. Check Request: '||l_reqid_count;
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 end if;
491
492 -- Standard call to check for call compatibility
493 IF NOT FND_API.Compatible_API_Call (
494 l_api_version,
495 X_api_version,
496 l_api_name,
497 G_PKG_NAME ) THEN
498 X_errbuf := 'Incompatible API Call.';
499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
500 END IF;
501
502 req_data := fnd_conc_global.request_data;
503
504 if (req_data is null) then
505 submit_req := TRUE;
506 else
507 submit_req := FALSE;
508 end if;
509
510 if (submit_req = TRUE) then
511
512 -- Initialize message list if X_init_msg_list is set to TRUE
513 IF FND_API.to_Boolean(X_init_msg_list) THEN
514 FND_MSG_PUB.initialize;
515 END IF;
516
517 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Submitting Concurrent Sub-Requests....');
518
519 x_retcode := FND_API.G_RET_STS_SUCCESS;
520
521 l_stmt_num :=10;
522 l_inv_req_id := FND_REQUEST.submit_request(
523 application => 'BOM',
524 program => 'CSTUINVM',
525 description => NULL,
526 start_time => NULL,
527 sub_request => TRUE,
528 argument1 => X_api_version,
529 argument2 => X_init_msg_list,
530 argument3 => X_batch_size,
531 argument4 => X_Num_Workers);
532
533 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> Upgrade Historical Inventory Transactions for Subledger Accounting [CSTUINVM] -> Request: '||l_inv_req_id);
534
535 l_stmt_num :=20;
536 l_wip_req_id := FND_REQUEST.submit_request(
537 application => 'BOM',
538 program => 'CSTUWIPM',
539 description => NULL,
540 start_time => NULL,
541 sub_request => TRUE,
542 argument1 => X_api_version,
543 argument2 => X_init_msg_list,
544 argument3 => X_batch_size,
545 argument4 => X_Num_Workers);
546
547 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> Upgrade Historical Work In Process Transactions for Subledger Accounting [CSTUWIPM] -> Request: '||l_wip_req_id );
548
549 l_stmt_num :=30;
550 l_rcv_req_id := FND_REQUEST.submit_request(
551 application => 'BOM',
552 program => 'CSTURCVM',
556 argument1 => X_api_version,
553 description => NULL,
554 start_time => NULL,
555 sub_request => TRUE,
557 argument2 => X_init_msg_list,
558 argument3 => X_batch_size,
559 argument4 => X_Num_Workers);
560
561 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> Upgrade Historical Receiving Transactions for Subledger Accounting [CSTURCVM] -> Request: '||l_rcv_req_id);
562
563 IF (l_inv_req_id = 0 AND l_wip_req_id = 0 AND l_rcv_req_id = 0) THEN
564 X_errbuf := 'Failure in Sub-Request Submission';
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566 END IF;
567
568 l_stmt_num :=40;
569 fnd_conc_global.set_req_globals(conc_status=>'PAUSED',
570 request_data=>3);
571
572 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Waiting for Concurrent Sub-Requests to Complete....');
573
574 X_errbuf := 'Submitted Sub-Requests';
575
576 return;
577
578 else
579 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Checking Status Of Concurrent Sub-Requests....');
580 --
581 -- restart case
582 --
583 l_stmt_num :=50;
584 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
585 x_retcode := FND_API.G_RET_STS_SUCCESS;
586
587 for i IN 1..l_sub_reqtab.COUNT()
588 loop
589 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
590 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Concurrent Request: '||l_sub_reqtab(i).request_id||' -> Status: '||l_sub_reqtab(i).dev_status);
591 X_retcode := FND_API.g_ret_sts_unexp_error;
592 end if;
593 end loop;
594
595 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
596 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>All Concurrent Sub-Requests Successfull');
597 else
598 X_errbuf := 'One or More of Upgrade Sub-Managers completed in Error';
599 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
600 end if;
601
602 commit;
603 end if;
604
605 <<out_arg_log>>
606
607 IF l_Log THEN
608 log_message(
609 fnd_log.level_procedure,
610 l_module||'.end',
611 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_MGR with '||
612 'X_errbuf = '||X_errbuf||','||
613 'X_retcode = '||X_retcode
614 );
615 END IF;
616
617 EXCEPTION
618
619 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
620 X_retcode := FND_API.g_ret_sts_unexp_error;
621 handle_error ( X_module => l_module||'.'||l_stmt_num,
622 X_message => 'ERROR: '||X_errbuf,
623 X_reqerror => TRUE,
624 X_errbuf => X_errbuf );
625
626 WHEN OTHERS THEN
627 X_retcode := FND_API.g_ret_sts_unexp_error;
628 handle_error ( X_module => l_module||'.'||l_stmt_num,
629 X_message => 'EXCEPTION: '||SQLERRM,
630 X_reqerror => TRUE,
631 X_errbuf => X_errbuf );
632
633 END Update_Proc_MGR;
634
635 -------------------------------------------------------------------------------------
636 -- API name : Update_Proc_INV_MGR
637 -- Type : Public
638 -- Function : Manager process to update Inventory Sub Ledger to SLA data model
639 -- Pre-reqs :
640 -- Parameters :
641 -- IN : X_api_version IN NUMBER,
642 -- X_init_msg_list IN VARCHAR2,
643 -- X_batch_size in number default 10000,
644 -- X_Num_Workers in number default 16,
645 -- X_Argument4 in varchar2 default null,
646 -- X_Argument5 in varchar2 default null,
647 -- X_Argument6 in varchar2 default null,
648 -- X_Argument7 in varchar2 default null,
649 -- X_Argument8 in varchar2 default null,
650 -- X_Argument9 in varchar2 default null,
651 -- X_Argument10 in varchar2 default null
652 --
653 -- OUT : X_errbuf out NOCOPY varchar2,
654 -- X_retcode out NOCOPY varchar2
655 -- Version : Initial version 1.0
656 -- Notes : The API is used for defining the "Upgrade Historical Inventory
657 -- Transactions for Subledger Accounting"
658 -- manager Concurrent Executable and Concurrent Program.
659 --
660 -- End of comments
661 -------------------------------------------------------------------------------------
662
663 PROCEDURE Update_Proc_INV_MGR (
664 X_errbuf out NOCOPY varchar2,
665 X_retcode out NOCOPY varchar2,
666 X_api_version IN NUMBER,
667 X_init_msg_list IN VARCHAR2,
668 X_batch_size in number default 10000,
669 X_Num_Workers in number default 16,
670 X_Ledger_Id in varchar2 default null,
671 X_Argument5 in varchar2 default null,
672 X_Argument6 in varchar2 default null,
673 X_Argument7 in varchar2 default null,
674 X_Argument8 in varchar2 default null,
675 X_Argument9 in varchar2 default null,
676 X_Argument10 in varchar2 default null)
677 IS
678 l_argument4 number;
679 l_argument5 number;
680 l_product varchar2(30);
681 l_status varchar2(30);
685
682 l_industry varchar2(30);
683 l_retstatus boolean;
684 l_table_owner varchar2(30);
686 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_INV_MGR';
687 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
688
689 l_sub_reqtab fnd_concurrent.requests_tab_type;
690 req_data varchar2(10);
691 submit_req boolean;
692
693 l_stmt_num number;
694 l_api_name CONSTANT VARCHAR2(30) := 'Update_Proc_INV_MGR';
695 l_api_version CONSTANT NUMBER := 1.0;
696
697 BEGIN
698
699 IF l_Log THEN
700 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
701 ELSE
702 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
703 END IF;
704
705 l_stmt_num :=0;
706 IF l_Log THEN
707 log_message(
708 fnd_log.level_procedure,
709 l_module||'.begin',
710 'Entering CST_SLA_UPDATE_PKG.Update_Proc_INV_MGR with '||
711 'X_batch_size = '||X_batch_size||','||
712 'X_Num_Workers = '||X_Num_Workers||','||
713 'X_Ledger_Id = '||X_Ledger_Id||','||
714 'X_Argument5 = '||X_Argument5||','||
715 'X_Argument6 = '||X_Argument6||','||
716 'X_Argument7 = '||X_Argument7||','||
717 'X_Argument8 = '||X_Argument8||','||
718 'X_Argument9 = '||X_Argument9||','||
719 'X_Argument10 = '||X_Argument10
720 );
721 END IF;
722
723 if gRequestId > 0 then
724 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>INV Upgrade Manager Concurrent Request: '||gRequestId);
725 else
726 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
727 end if;
728
729 -- Standard call to check for call compatibility
730 IF NOT FND_API.Compatible_API_Call (
731 l_api_version,
732 X_api_version,
733 l_api_name,
734 G_PKG_NAME ) THEN
735 X_errbuf := 'Incompatible API Call.';
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738
739 req_data := fnd_conc_global.request_data;
740
741 if (req_data is null) then
742 submit_req := TRUE;
743 else
744 submit_req := FALSE;
745 end if;
746
747 if (submit_req = TRUE) then
748
749 -- Initialize message list if X_init_msg_list is set to TRUE
750 IF FND_API.to_Boolean(X_init_msg_list) THEN
751 FND_MSG_PUB.initialize;
752 END IF;
753
754 x_retcode := FND_API.G_RET_STS_SUCCESS;
755
756 --
757 -- get schema name of the table for ID range processing
758 --
759 l_product :='INV';
760
761 l_stmt_num :=10;
762 l_retstatus := fnd_installation.get_app_info(
763 l_product, l_status, l_industry, l_table_owner);
764
765 if ((l_retstatus = TRUE) AND (l_table_owner is not null)) then
766
767 Begin
768
769 select TO_CHAR(MIN(xud.start_date), 'YYYYDDMM'), TO_CHAR(MAX(xud.end_date), 'YYYYDDMM')
770 into l_argument4, l_argument5
771 from xla_upgrade_dates xud
772 where ledger_id = nvl(X_Ledger_Id,ledger_id);
773
774 l_stmt_num :=20;
775 if (l_argument4 is not null) then
776 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Submitting Workers for INV Upgrade [CSTUINVW]');
777 AD_CONC_UTILS_PKG.submit_subrequests(
778 X_errbuf=>X_errbuf,
779 X_retcode=>X_retcode,
780 X_WorkerConc_app_shortname=>'BOM',
781 X_WorkerConc_progname=>'CSTUINVW',
782 X_Batch_size=>X_batch_size,
783 X_Num_Workers=>X_Num_Workers,
784 X_Argument4 => l_argument4,
785 X_Argument5 => l_argument5,
786 X_Argument6 => X_Ledger_Id,
787 X_Argument7 => null,
788 X_Argument8 => null,
789 X_Argument9 => null,
790 X_Argument10 => null);
791
792 if (X_retcode <>AD_CONC_UTILS_PKG.CONC_SUCCESS) THEN
793 X_errbuf := 'Submission of INV Upgrade Workers failed -> X_retcode = '||X_retcode;
794 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
795 end if;
796
797 l_stmt_num :=30;
798
799 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
800
801 for i IN 1..l_sub_reqtab.COUNT() loop
802 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> INV Worker [CSTUINVW] '||RPAD(i,2)||' - '||l_sub_reqtab(i).request_id);
803 end loop;
804
805 end if;
806 /* can not update migration_status_code until WIP upgrade is done, because they share the same period.*/
807 exception
808 when no_data_found then
809 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'No Data Found in XUD');
810 end;
811 else
812 X_errbuf := 'Cannot get schema name for product : '||l_product;
813 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
814 end if;
815
816 /* can not update migration_status_code until WIP upgrade is done, because they share the same period.*/
817
818 else
822 --
819 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Checking Status Of Child Workers....');
820 --
821 -- restart case
823 l_stmt_num :=40;
824 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
825
826 x_retcode := FND_API.G_RET_STS_SUCCESS;
827 for i IN 1..l_sub_reqtab.COUNT()
828 loop
829 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
830 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Concurrent Request: '||l_sub_reqtab(i).request_id||' -> Status: '||l_sub_reqtab(i).dev_status);
831 X_retcode := FND_API.g_ret_sts_unexp_error;
832 end if;
833 end loop;
834
835 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
836 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>All Child Workers are Successfull');
837 else
838 X_errbuf := 'One or More of Child Workers completed in Error';
839 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
840 end if;
841
842 commit;
843 end if;
844
845 <<out_arg_log>>
846
847 IF l_Log THEN
848 log_message(
849 fnd_log.level_procedure,
850 l_module||'.end',
851 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_INV_MGR with '||
852 'X_errbuf = '||X_errbuf||','||
853 'X_retcode = '||X_retcode
854 );
855 END IF;
856
857 EXCEPTION
858
859 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
860 X_retcode := FND_API.g_ret_sts_unexp_error;
861 handle_error ( X_module => l_module||'.'||l_stmt_num,
862 X_message => 'ERROR: '||X_errbuf,
863 X_reqerror => TRUE,
864 X_errbuf => X_errbuf );
865
866 WHEN OTHERS THEN
867 X_retcode := FND_API.g_ret_sts_unexp_error;
868 handle_error ( X_module => l_module||'.'||l_stmt_num,
869 X_message => 'EXCEPTION: '||SQLERRM,
870 X_reqerror => TRUE,
871 X_errbuf => X_errbuf );
872
873 END Update_Proc_INV_MGR;
874
875 -------------------------------------------------------------------------------------
876 -- API name : Update_Proc_WIP_MGR
877 -- Type : Public
878 -- Function : Manager process to update WIP Sub Ledger to SLA data model
879 -- Pre-reqs :
880 -- Parameters :
881 -- IN : X_api_version IN NUMBER,
882 -- X_init_msg_list IN VARCHAR2,
883 -- X_batch_size in number default 10000,
884 -- X_Num_Workers in number default 16,
885 -- X_Argument4 in varchar2 default null,
886 -- X_Argument5 in varchar2 default null,
887 -- X_Argument6 in varchar2 default null,
888 -- X_Argument7 in varchar2 default null,
889 -- X_Argument8 in varchar2 default null,
890 -- X_Argument9 in varchar2 default null,
891 -- X_Argument10 in varchar2 default null
892 --
893 -- OUT : X_errbuf out NOCOPY varchar2,
894 -- X_retcode out NOCOPY varchar2
895 -- Version : Initial version 1.0
896 -- Notes : The API is used for defining the "Upgrade Historical Work In Process
897 -- Transactions for Subledger Accounting"
898 --
899 -- End of comments
900 -------------------------------------------------------------------------------------
901
902 PROCEDURE Update_Proc_WIP_MGR (
903 X_errbuf out NOCOPY varchar2,
904 X_retcode out NOCOPY varchar2,
905 X_api_version IN NUMBER,
906 X_init_msg_list IN VARCHAR2,
907 X_batch_size in number default 10000,
908 X_Num_Workers in number default 16,
909 X_Ledger_Id in varchar2 default null,
910 X_Argument5 in varchar2 default null,
911 X_Argument6 in varchar2 default null,
912 X_Argument7 in varchar2 default null,
913 X_Argument8 in varchar2 default null,
914 X_Argument9 in varchar2 default null,
915 X_Argument10 in varchar2 default null)
916 IS
917 l_argument4 number;
918 l_argument5 number;
919 l_product varchar2(30);
920 l_status varchar2(30);
921 l_industry varchar2(30);
922 l_retstatus boolean;
923 l_table_owner varchar2(30);
924
925 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_WIP_MGR';
926 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
927
928 l_sub_reqtab fnd_concurrent.requests_tab_type;
929 req_data varchar2(10);
930 submit_req boolean;
931
932 l_stmt_num number;
933 l_api_name CONSTANT VARCHAR2(30) := 'Update_Proc_WIP_MGR';
934 l_api_version CONSTANT NUMBER := 1.0;
935
936 BEGIN
937
938 IF l_Log THEN
939 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
940 ELSE
941 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
942 END IF;
943
944 l_stmt_num :=0;
945 IF l_Log THEN
946 log_message(
947 fnd_log.level_procedure,
948 l_module||'.begin',
949 'Entering CST_SLA_UPDATE_PKG.Update_Proc_WIP_MGR with '||
950 'X_batch_size = '||X_batch_size||','||
951 'X_Num_Workers = '||X_Num_Workers||','||
955 'X_Argument7 = '||X_Argument7||','||
952 'X_Ledger_Id = '||X_Ledger_Id||','||
953 'X_Argument5 = '||X_Argument5||','||
954 'X_Argument6 = '||X_Argument6||','||
956 'X_Argument8 = '||X_Argument8||','||
957 'X_Argument9 = '||X_Argument9||','||
958 'X_Argument10 = '||X_Argument10
959 );
960 END IF;
961
962 if gRequestId > 0 then
963 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>WIP Upgrade Manager Concurrent Request: '||gRequestId);
964 else
965 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
966 end if;
967
968 -- Standard call to check for call compatibility
969 IF NOT FND_API.Compatible_API_Call (
970 l_api_version,
971 X_api_version,
972 l_api_name,
973 G_PKG_NAME ) THEN
974 X_errbuf := 'Incompatible API Call.';
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977
978 req_data := fnd_conc_global.request_data;
979
980 if (req_data is null) then
981 submit_req := TRUE;
982 else
983 submit_req := FALSE;
984 end if;
985
986 if (submit_req = TRUE) then
987
988 -- Initialize message list if X_init_msg_list is set to TRUE
989 IF FND_API.to_Boolean(X_init_msg_list) THEN
990 FND_MSG_PUB.initialize;
991 END IF;
992
993 x_retcode := FND_API.G_RET_STS_SUCCESS;
994
995 --
996 -- get schema name of the table for ID range processing
997 --
998 l_product :='WIP';
999
1000 l_stmt_num :=10;
1001 l_retstatus := fnd_installation.get_app_info(
1002 l_product, l_status, l_industry, l_table_owner);
1003
1004 if ((l_retstatus = TRUE) AND (l_table_owner is not null)) then
1005
1006 begin
1007
1008 select TO_CHAR(MIN(xud.start_date), 'YYYYDDMM'), TO_CHAR(MAX(xud.end_date), 'YYYYDDMM')
1009 into l_argument4, l_argument5
1010 from xla_upgrade_dates xud
1011 where ledger_id = nvl(X_Ledger_Id,ledger_id);
1012
1013 l_stmt_num :=20;
1014 if (l_argument4 is not null) then
1015 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Submitting Workers for WIP Upgrade [CSTUWIPW]');
1016 AD_CONC_UTILS_PKG.submit_subrequests(
1017 X_errbuf=>X_errbuf,
1018 X_retcode=>X_retcode,
1019 X_WorkerConc_app_shortname=>'BOM',
1020 X_WorkerConc_progname=>'CSTUWIPW',
1021 X_Batch_size=>X_batch_size,
1022 X_Num_Workers=>X_Num_Workers,
1023 X_Argument4 => l_argument4,
1024 X_Argument5 => l_argument5,
1025 X_Argument6 => X_Ledger_Id,
1026 X_Argument7 => null,
1027 X_Argument8 => null,
1028 X_Argument9 => null,
1029 X_Argument10 => null );
1030
1031 if (X_retcode <>AD_CONC_UTILS_PKG.CONC_SUCCESS) THEN
1032 X_errbuf := 'Submission of WIP Upgrade Workers failed -> X_retcode = '||X_retcode;
1033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1034 end if;
1035
1036 l_stmt_num :=30;
1037
1038 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
1039
1040 for i IN 1..l_sub_reqtab.COUNT() loop
1041 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> WIP Worker [CSTUWIPW] '||RPAD(i,2)||' - '||l_sub_reqtab(i).request_id);
1042 end loop;
1043
1044 end if;
1045 exception
1046 when no_data_found then
1047 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'No Data Found in XUD');
1048 end;
1049 else
1050 X_errbuf := 'Cannot get schema name for product : '||l_product;
1051 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1052 end if;
1053
1054 /* can not update migration_status_code until Inventory upgrade is done, because they share the same period.*/
1055
1056 else
1057 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Checking Status Of Child Workers....');
1058 --
1059 -- restart case
1060 --
1061 l_stmt_num :=40;
1062 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
1063
1064 x_retcode := FND_API.G_RET_STS_SUCCESS;
1065 for i IN 1..l_sub_reqtab.COUNT()
1066 loop
1067 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
1068 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Concurrent Request: '||l_sub_reqtab(i).request_id||' -> Status: '||l_sub_reqtab(i).dev_status);
1069 X_retcode := FND_API.g_ret_sts_unexp_error;
1070 end if;
1071 end loop;
1072
1073 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
1074 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>All Child Workers are Successfull');
1075 else
1076 X_errbuf := 'One or More of Child Workers completed in Error';
1077 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1078 end if;
1079
1080 commit;
1081 end if;
1082
1083 <<out_arg_log>>
1084
1085 IF l_Log THEN
1086 log_message(
1087 fnd_log.level_procedure,
1088 l_module||'.end',
1089 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_WIP_MGR with '||
1090 'X_errbuf = '||X_errbuf||','||
1094
1091 'X_retcode = '||X_retcode
1092 );
1093 END IF;
1095 EXCEPTION
1096
1097 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1098 X_retcode := FND_API.g_ret_sts_unexp_error;
1099 handle_error ( X_module => l_module||'.'||l_stmt_num,
1100 X_message => 'ERROR: '||X_errbuf,
1101 X_reqerror => TRUE,
1102 X_errbuf => X_errbuf );
1103
1104 WHEN OTHERS THEN
1105 X_retcode := FND_API.g_ret_sts_unexp_error;
1106 handle_error ( X_module => l_module||'.'||l_stmt_num,
1107 X_message => 'EXCEPTION: '||SQLERRM,
1108 X_reqerror => TRUE,
1109 X_errbuf => X_errbuf );
1110
1111 END Update_Proc_WIP_MGR;
1112
1113 -------------------------------------------------------------------------------------
1114 -- API name : Update_Proc_RCV_MGR
1115 -- Type : Public
1116 -- Function : Manager process to update Receiving Sub Ledger to SLA data model
1117 -- Pre-reqs :
1118 -- Parameters :
1119 -- IN : X_api_version IN NUMBER,
1120 -- X_init_msg_list IN VARCHAR2,
1121 -- X_batch_size in number default 10000,
1122 -- X_Num_Workers in number default 16,
1123 -- X_Argument4 in varchar2 default null,
1124 -- X_Argument5 in varchar2 default null,
1125 -- X_Argument6 in varchar2 default null,
1126 -- X_Argument7 in varchar2 default null,
1127 -- X_Argument8 in varchar2 default null,
1128 -- X_Argument9 in varchar2 default null,
1129 -- X_Argument10 in varchar2 default null
1130 --
1131 -- OUT : X_errbuf out NOCOPY varchar2,
1132 -- X_retcode out NOCOPY varchar2
1133 -- Version : Initial version 1.0
1134 -- Notes : The API is used for defining the "Upgrade Historical Receiving
1135 -- Transactions for Subledger Accounting"
1136 --
1137 -- End of comments
1138 -------------------------------------------------------------------------------------
1139
1140 PROCEDURE Update_Proc_RCV_MGR (
1141 X_errbuf out NOCOPY varchar2,
1142 X_retcode out NOCOPY varchar2,
1143 X_api_version IN NUMBER,
1144 X_init_msg_list IN VARCHAR2,
1145 X_batch_size in number default 10000,
1146 X_Num_Workers in number default 16,
1147 X_Ledger_Id in varchar2 default null,
1148 X_Argument5 in varchar2 default null,
1149 X_Argument6 in varchar2 default null,
1150 X_Argument7 in varchar2 default null,
1151 X_Argument8 in varchar2 default null,
1152 X_Argument9 in varchar2 default null,
1153 X_Argument10 in varchar2 default null)
1154 IS
1155 l_argument4 number;
1156 l_argument5 number;
1157 l_product varchar2(30);
1158 l_status varchar2(30);
1159 l_industry varchar2(30);
1160 l_retstatus boolean;
1161 l_table_owner varchar2(30);
1162
1163 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_RCV_MGR';
1164 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module);
1165
1166 l_sub_reqtab fnd_concurrent.requests_tab_type;
1167 req_data varchar2(10);
1168 submit_req boolean;
1169
1170 l_stmt_num number;
1171 l_api_name CONSTANT VARCHAR2(30) := 'Update_Proc_RCV_MGR';
1172 l_api_version CONSTANT NUMBER := 1.0;
1173
1174 BEGIN
1175
1176 IF l_Log THEN
1177 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
1178 ELSE
1179 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
1180 END IF;
1181
1182 l_stmt_num :=0;
1183 IF l_Log THEN
1184 log_message(
1185 fnd_log.level_procedure,
1186 l_module||'.begin',
1187 'Entering CST_SLA_UPDATE_PKG.Update_Proc_RCV_MGR with '||
1188 'X_batch_size = '||X_batch_size||','||
1189 'X_Num_Workers = '||X_Num_Workers||','||
1190 'X_Ledger_Id = '||X_Ledger_Id||','||
1191 'X_Argument5 = '||X_Argument5||','||
1192 'X_Argument6 = '||X_Argument6||','||
1193 'X_Argument7 = '||X_Argument7||','||
1194 'X_Argument8 = '||X_Argument8||','||
1195 'X_Argument9 = '||X_Argument9||','||
1196 'X_Argument10 = '||X_Argument10
1197 );
1198 END IF;
1199
1200 if gRequestId > 0 then
1201 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>RCV Upgrade Manager Concurrent Request: '||gRequestId);
1202 else
1203 raise_application_error(-20001, 'SUBMIT_SUBREQUESTS() must be called from a concurrent request');
1204 end if;
1205
1206 -- Standard call to check for call compatibility
1207 IF NOT FND_API.Compatible_API_Call (
1208 l_api_version,
1209 X_api_version,
1210 l_api_name,
1211 G_PKG_NAME ) THEN
1212 X_errbuf := 'Incompatible API Call.';
1213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1214 END IF;
1215
1216 req_data := fnd_conc_global.request_data;
1217
1218 if (req_data is null) then
1219 submit_req := TRUE;
1220 else
1221 submit_req := FALSE;
1222 end if;
1223
1224 if (submit_req = TRUE) then
1225
1226 -- Initialize message list if X_init_msg_list is set to TRUE
1227 IF FND_API.to_Boolean(X_init_msg_list) THEN
1231 x_retcode := FND_API.G_RET_STS_SUCCESS;
1228 FND_MSG_PUB.initialize;
1229 END IF;
1230
1232
1233 --
1234 -- get schema name of the table for ID range processing
1235 --
1236 l_product :='PO';
1237
1238 l_stmt_num :=10;
1239 l_retstatus := fnd_installation.get_app_info(
1240 l_product, l_status, l_industry, l_table_owner);
1241
1242 if ((l_retstatus = TRUE) AND (l_table_owner is not null)) then
1243
1244 begin
1245
1246 select TO_CHAR(MIN(xud.start_date), 'YYYYDDMM'), TO_CHAR(MAX(xud.end_date), 'YYYYDDMM')
1247 into l_argument4, l_argument5
1248 from xla_upgrade_dates xud
1249 where ledger_id = nvl(X_Ledger_Id,ledger_id);
1250
1251 l_stmt_num :=20;
1252 if (l_argument4 is not null) then
1253 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Submitting Workers for RCV Upgrade [CSTURCVW]');
1254 AD_CONC_UTILS_PKG.submit_subrequests(
1255 X_errbuf=>X_errbuf,
1256 X_retcode=>X_retcode,
1257 X_WorkerConc_app_shortname=>'BOM',
1258 X_WorkerConc_progname=>'CSTURCVW',
1259 X_Batch_size=>X_batch_size,
1260 X_Num_Workers=>X_Num_Workers,
1261 X_Argument4 => l_argument4,
1262 X_Argument5 => l_argument5,
1263 X_Argument6 => X_Ledger_Id,
1264 X_Argument7 => null,
1265 X_Argument8 => null,
1266 X_Argument9 => null,
1267 X_Argument10 => null );
1268
1269 if (X_retcode <>AD_CONC_UTILS_PKG.CONC_SUCCESS) THEN
1270 X_errbuf := 'Submission of RCV Upgrade Workers failed -> X_retcode = '||X_retcode;
1271 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1272 end if;
1273
1274 l_stmt_num :=30;
1275
1276 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
1277
1278 for i IN 1..l_sub_reqtab.COUNT() loop
1279 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'> RCV Worker [CSTURCVW] '||RPAD(i,2)||' - '||l_sub_reqtab(i).request_id);
1280 end loop;
1281
1282 end if;
1283 exception
1284 when no_data_found then
1285 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'No Data Found in XUD');
1286 end;
1287 else
1288 X_errbuf := 'Cannot get schema name for product : '||l_product;
1289 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1290 end if;
1291
1292 --update migration_status_code and je_from_sla_flag
1293
1294 else
1295 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Checking Status Of Child Workers....');
1296 --
1297 -- restart case
1298 --
1299 l_stmt_num :=40;
1300 l_sub_reqtab := fnd_concurrent.get_sub_requests(gRequestId);
1301
1302 x_retcode := FND_API.G_RET_STS_SUCCESS;
1303 for i IN 1..l_sub_reqtab.COUNT()
1304 loop
1305 if (l_sub_reqtab(i).dev_status <> 'NORMAL') then
1306 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>Concurrent Request: '||l_sub_reqtab(i).request_id||' -> Status: '||l_sub_reqtab(i).dev_status);
1307 X_retcode := FND_API.g_ret_sts_unexp_error;
1308 end if;
1309 end loop;
1310
1311 if (X_retcode = FND_API.G_RET_STS_SUCCESS) then
1312 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>All Child Workers are Successfull');
1313 else
1314 X_errbuf := 'One or More of Child Workers completed in Error';
1315 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1316 end if;
1317
1318 commit;
1319 end if;
1320
1321 <<out_arg_log>>
1322
1323 IF l_Log THEN
1324 log_message(
1325 fnd_log.level_procedure,
1326 l_module||'.end',
1327 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_RCV_MGR with '||
1328 'X_errbuf = '||X_errbuf||','||
1329 'X_retcode = '||X_retcode
1330 );
1331 END IF;
1332
1333 EXCEPTION
1334
1335 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1336 X_retcode := FND_API.g_ret_sts_unexp_error;
1337 handle_error ( X_module => l_module||'.'||l_stmt_num,
1338 X_message => 'ERROR: '||X_errbuf,
1339 X_reqerror => TRUE,
1340 X_errbuf => X_errbuf );
1341
1342 WHEN OTHERS THEN
1343 X_retcode := FND_API.g_ret_sts_unexp_error;
1344 handle_error ( X_module => l_module||'.'||l_stmt_num,
1345 X_message => 'EXCEPTION: '||SQLERRM,
1346 X_reqerror => TRUE,
1347 X_errbuf => X_errbuf );
1348
1349 END Update_Proc_RCV_MGR;
1350
1351
1352 -------------------------------------------------------------------------------------
1353 -- API name : Update_Proc_INV_WKR
1354 -- Type : Private
1355 -- Function : Worker process to update Inventory Sub Ledger to SLA data model
1356 -- Pre-reqs :
1357 -- Parameters : X_Argument4 is used to pass minimum ID;
1358 -- X_Argument5 is used to pass maximum ID.
1359 -- IN : X_batch_size in number,
1360 -- X_Worker_Id in number,
1361 -- X_Num_Workers in number,
1362 -- X_Argument4 in varchar2 default null,
1363 -- X_Argument5 in varchar2 default null,
1364 -- X_Argument6 in varchar2 default null,
1368 -- X_Argument10 in varchar2 default null
1365 -- X_Argument7 in varchar2 default null,
1366 -- X_Argument8 in varchar2 default null,
1367 -- X_Argument9 in varchar2 default null,
1369 --
1370 -- OUT : X_errbuf out NOCOPY varchar2,
1371 -- X_retcode out NOCOPY varchar2
1372 -- Version : Initial version 1.0
1373 -- Notes : The API is used for defining the "Upgrade Inventory Subledger to SLA"
1374 -- worker Concurrent Executable and Concurrent Program. It is called
1375 -- from Update_Proc_INV_MGR by submitting multiple requests
1376 -- via AD_CONC_UTILS_PKG.submit_subrequests. It is also used by the
1377 -- downtime upgrade script cstmtaupg.sql.
1378 --
1379 -- End of comments
1380 -------------------------------------------------------------------------------------
1381
1382 PROCEDURE Update_Proc_INV_WKR (
1383 X_errbuf out NOCOPY varchar2,
1384 X_retcode out NOCOPY varchar2,
1385 X_batch_size in number,
1386 X_Worker_Id in number,
1387 X_Num_Workers in number,
1388 X_Argument4 in varchar2 default null,
1389 X_Argument5 in varchar2 default null,
1390 X_Argument6 in varchar2 default null,
1391 X_Argument7 in varchar2 default null,
1392 X_Argument8 in varchar2 default null,
1393 X_Argument9 in varchar2 default null,
1394 X_Argument10 in varchar2 default null)
1395 IS
1396
1397 l_table_name varchar2(30) := 'MTL_MATERIAL_TRANSACTIONS';
1398 l_id_column varchar2(30) := 'TRANSACTION_ID';
1399
1400 l_update_name varchar2(30);
1401
1402 l_table_owner varchar2(30);
1403 l_status VARCHAR2(30);
1404 l_industry VARCHAR2(30);
1405 l_retstatus BOOLEAN;
1406 l_any_rows_to_process boolean;
1407
1408 l_start_id number;
1409 l_end_id number;
1410 l_rows_processed number;
1411
1412 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_INV_WKR';
1413 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND gRequestId > 0;
1414
1415 l_stmt_num number;
1416
1417 BEGIN
1418
1419 IF l_Log THEN
1420 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
1421 ELSE
1422 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
1423 END IF;
1424
1425 l_stmt_num :=0;
1426
1427 IF l_Log THEN
1428 log_message(
1429 fnd_log.level_procedure,
1430 l_module||'.begin',
1431 'Entering CST_SLA_UPDATE_PKG.Update_Proc_INV_WKR with '||
1432 'X_batch_size = '||X_batch_size||','||
1433 'X_Worker_Id = '||X_Worker_Id||','||
1434 'X_Num_Workers = '||X_Num_Workers||','||
1435 'X_Argument4 = '||X_Argument4||','||
1436 'X_Argument5 = '||X_Argument5||','||
1437 'X_Argument6 = '||X_Argument6||','||
1438 'X_Argument7 = '||X_Argument7||','||
1439 'X_Argument8 = '||X_Argument8||','||
1440 'X_Argument9 = '||X_Argument9||','||
1441 'X_Argument10 = '||X_Argument10
1442 );
1443 END IF;
1444
1445 --
1446 -- The following could be coded to use EXECUTE IMMEDIATE in order to remove build
1447 -- time
1448 -- dependencies as the processing could potentially reference some tables that
1449 -- could be obsoleted in the current release
1450 --
1451 BEGIN
1452 l_stmt_num :=10;
1453
1454 l_update_name := X_Argument6 || 'I' || X_Argument4 || '-' || X_Argument5;
1455
1456 l_retstatus := FND_INSTALLATION.GET_APP_INFO('INV', l_status, l_industry, l_table_owner);
1457
1458 ad_parallel_updates_pkg.initialize_id_range(
1459 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
1460 X_owner=>l_table_owner,
1461 X_table=>l_table_name,
1462 X_script=>l_update_name,
1463 X_ID_column=>l_id_column,
1464 X_worker_id=>X_Worker_Id,
1465 X_num_workers=>X_num_workers,
1466 X_batch_size=>X_batch_size,
1467 X_debug_level=>0);
1468
1469 l_stmt_num :=20;
1470 ad_parallel_updates_pkg.get_id_range(
1471 l_start_id,
1472 l_end_id,
1473 l_any_rows_to_process,
1474 X_batch_size,
1475 TRUE);
1476
1477 IF NOT l_any_rows_to_process AND l_Log THEN
1478 log_message(
1479 fnd_log.level_procedure,
1480 l_module||'.'||l_stmt_num,
1481 'No Rows to Process in INV'
1482 );
1483 END IF;
1484
1485 l_stmt_num :=25;
1486 while (l_any_rows_to_process = TRUE)
1487 loop
1488 --
1489 -- Code CST SLA update logic here
1490 --
1491 IF l_Log THEN
1492 log_message(
1493 fnd_log.level_procedure,
1494 l_module||'.'||l_stmt_num,
1495 'Processing INV Rows From '||l_start_id||' To '||l_end_id
1496 );
1497 END IF;
1498
1499 l_stmt_num :=30;
1500 CST_SLA_UPDATE_PKG.Update_Inventory_Subledger(
1501 X_errbuf=>X_errbuf,
1502 X_retcode=>X_retcode,
1503 X_min_id=>l_start_id,
1504 X_max_id=>l_end_id);
1505
1506 IF (X_retcode <> FND_API.G_RET_STS_SUCCESS) then
1510 ELSE
1507 X_errbuf := 'Failure while updating INV Subledger rows from '|| l_start_id || ' To ' || l_end_id;
1508 fnd_file.put_line(fnd_file.LOG, 'RETCODE := ' || X_retcode || '. ' ||x_errbuf);
1509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1511 fnd_file.put_line(fnd_file.LOG, 'Inventory upgrade completed successfully for Range : ' || l_start_id || ' To ' || l_end_id);
1512 END IF;
1513
1514 /*l_rows_processed := SQL%ROWCOUNT;*/
1515 l_rows_processed := l_end_id - l_start_id + 1;
1516
1517 l_stmt_num :=40;
1518 ad_parallel_updates_pkg.processed_id_range(
1519 l_rows_processed,
1520 l_end_id);
1521
1522 COMMIT;
1523
1524 l_stmt_num :=50;
1525 ad_parallel_updates_pkg.get_id_range(
1526 l_start_id,
1527 l_end_id,
1528 l_any_rows_to_process,
1529 X_batch_size,
1530 FALSE);
1531
1532 end loop;
1533
1534 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
1535 fnd_file.put_line(fnd_file.LOG, 'Inventory upgrade worker completed successfully.... ');
1536
1537 EXCEPTION
1538 WHEN OTHERS THEN
1539 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1540 fnd_file.put_line(fnd_file.LOG, x_errbuf);
1541 raise;
1542 END;
1543
1544 <<out_arg_log>>
1545
1546 IF l_Log THEN
1547 log_message(
1548 fnd_log.level_procedure,
1549 l_module||'.end',
1550 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_INV_WKR with '||
1551 'X_errbuf = '||X_errbuf||','||
1552 'X_retcode = '||X_retcode
1553 );
1554 END IF;
1555
1556 EXCEPTION
1557
1558 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1559 ROLLBACK;
1560 fnd_file.put_line(fnd_file.LOG, 'Unexpected error encountered. RETCODE:= ' || x_retcode || '. ' || x_errbuf);
1561 X_retcode := FND_API.g_ret_sts_unexp_error;
1562 handle_error ( X_module => l_module||'.'||l_stmt_num,
1563 X_message => 'ERROR: '||X_errbuf,
1564 X_reqerror => TRUE,
1565 X_errbuf => X_errbuf );
1566
1567 WHEN OTHERS THEN
1568 fnd_file.put_line(fnd_file.LOG, 'Exception encountered. RETCODE:= ' || x_retcode || '. ' || x_errbuf);
1569 ROLLBACK;
1570 X_retcode := FND_API.g_ret_sts_unexp_error;
1571 handle_error ( X_module => l_module||'.'||l_stmt_num,
1572 X_message => 'EXCEPTION: '||SQLERRM,
1573 X_reqerror => TRUE,
1574 X_errbuf => X_errbuf );
1575
1576 END Update_Proc_INV_WKR;
1577
1578 -------------------------------------------------------------------------------------
1579 -- API name : Update_Proc_WIP_WKR
1580 -- Type : Private
1581 -- Function : Worker process to update WIP Sub Ledger to SLA data model
1582 -- Pre-reqs :
1583 -- Parameters : X_Argument4 is used to pass minimum ID;
1584 -- X_Argument5 is used to pass maximum ID.
1585 -- IN : X_batch_size in number,
1586 -- X_Worker_Id in number,
1587 -- X_Num_Workers in number,
1588 -- X_Argument4 in varchar2 default null,
1589 -- X_Argument5 in varchar2 default null,
1590 -- X_Argument6 in varchar2 default null,
1591 -- X_Argument7 in varchar2 default null,
1592 -- X_Argument8 in varchar2 default null,
1593 -- X_Argument9 in varchar2 default null,
1594 -- X_Argument10 in varchar2 default null
1595 --
1596 -- OUT : X_errbuf out NOCOPY varchar2,
1597 -- X_retcode out NOCOPY varchar2
1598 -- Version : Initial version 1.0
1599 -- Notes : The API is used for defining the "Upgrade WIP Subledger to SLA"
1600 -- worker Concurrent Executable and Concurrent Program. It is called
1601 -- from Update_Proc_WIP_MGR by submitting multiple requests
1602 -- via AD_CONC_UTILS_PKG.submit_subrequests. It is also used by the
1603 -- downtime upgrade script cstwtaupg.sql.
1604 --
1605 -- End of comments
1606 -------------------------------------------------------------------------------------
1607
1608 PROCEDURE Update_Proc_WIP_WKR (
1609 X_errbuf out NOCOPY varchar2,
1610 X_retcode out NOCOPY varchar2,
1611 X_batch_size in number,
1612 X_Worker_Id in number,
1613 X_Num_Workers in number,
1614 X_Argument4 in varchar2 default null,
1615 X_Argument5 in varchar2 default null,
1616 X_Argument6 in varchar2 default null,
1617 X_Argument7 in varchar2 default null,
1618 X_Argument8 in varchar2 default null,
1619 X_Argument9 in varchar2 default null,
1620 X_Argument10 in varchar2 default null)
1621 IS
1622
1623 l_product varchar2(30);
1624 l_table_name varchar2(30) := 'WIP_TRANSACTIONS';
1625 l_id_column varchar2(30) := 'TRANSACTION_ID';
1626
1627 l_update_name varchar2(30);
1628
1629 l_table_owner varchar2(30);
1630 l_status VARCHAR2(30);
1631 l_industry VARCHAR2(30);
1632 l_retstatus BOOLEAN;
1633 l_any_rows_to_process boolean;
1634
1635 l_start_id number;
1636 l_end_id number;
1637 l_rows_processed number;
1638
1639 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_WIP_WKR';
1640 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND gRequestId > 0;
1641
1645
1642 l_stmt_num number;
1643
1644 BEGIN
1646 IF l_Log THEN
1647 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
1648 ELSE
1649 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
1650 END IF;
1651
1652 l_stmt_num :=0;
1653
1654 IF l_Log THEN
1655 log_message(
1656 fnd_log.level_procedure,
1657 l_module||'.begin',
1658 'Entering CST_SLA_UPDATE_PKG.Update_Proc_WIP_WKR with '||
1659 'X_batch_size = '||X_batch_size||','||
1660 'X_Worker_Id = '||X_Worker_Id||','||
1661 'X_Num_Workers = '||X_Num_Workers||','||
1662 'X_Argument4 = '||X_Argument4||','||
1663 'X_Argument5 = '||X_Argument5||','||
1664 'X_Argument6 = '||X_Argument6||','||
1665 'X_Argument7 = '||X_Argument7||','||
1666 'X_Argument8 = '||X_Argument8||','||
1667 'X_Argument9 = '||X_Argument9||','||
1668 'X_Argument10 = '||X_Argument10
1669 );
1670 END IF;
1671
1672 --
1673 -- The following could be coded to use EXECUTE IMMEDIATE in order to remove build
1674 -- time
1675 -- dependencies as the processing could potentially reference some tables that
1676 -- could be obsoleted in the current release
1677 --
1678 BEGIN
1679 l_stmt_num :=10;
1680
1681 l_update_name := X_Argument6 || 'W' || X_Argument4 || '-' || X_Argument5;
1682
1683 l_retstatus := FND_INSTALLATION.GET_APP_INFO('WIP', l_status, l_industry, l_table_owner);
1684
1685 ad_parallel_updates_pkg.initialize_id_range(
1686 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
1687 X_owner=>l_table_owner,
1688 X_table=>l_table_name,
1689 X_script=>l_update_name,
1690 X_ID_column=>l_id_column,
1691 X_worker_id=>X_Worker_Id,
1692 X_num_workers=>X_num_workers,
1693 X_batch_size=>X_batch_size,
1694 X_debug_level=>0);
1695
1696 l_stmt_num :=20;
1697 ad_parallel_updates_pkg.get_id_range(
1698 l_start_id,
1699 l_end_id,
1700 l_any_rows_to_process,
1701 X_batch_size,
1702 TRUE);
1703
1704 l_stmt_num :=25;
1705 IF NOT l_any_rows_to_process AND l_Log THEN
1706 log_message(
1707 fnd_log.level_procedure,
1708 l_module||'.'||l_stmt_num,
1709 'No Rows to Process in WIP'
1710 );
1711 END IF;
1712
1713 while (l_any_rows_to_process = TRUE)
1714 loop
1715 --
1716 -- Code CST SLA update logic here
1717 --
1718 IF l_Log THEN
1719 log_message(
1720 fnd_log.level_procedure,
1721 l_module||'.'||l_stmt_num,
1722 'Processing WIP Rows From '||l_start_id||' To '||l_end_id
1723 );
1724 END IF;
1725
1726 l_stmt_num :=30;
1727 CST_SLA_UPDATE_PKG.Update_WIP_Subledger(
1728 X_errbuf=>X_errbuf,
1729 X_retcode=>X_retcode,
1730 X_min_id=>l_start_id,
1731 X_max_id=>l_end_id);
1732
1733 IF (X_retcode <>FND_API.G_RET_STS_SUCCESS) then
1734 X_errbuf := 'Failure while updating WIP Subledger from '|| l_start_id || ' To ' || l_end_id;
1735 fnd_file.put_line(fnd_file.LOG, 'RETCODE := ' || X_retcode || '. ' ||x_errbuf);
1736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
1737 ELSE
1738 fnd_file.put_line(fnd_file.LOG, 'WIP upgrade completed successfully for Range : ' || l_start_id || ' To ' || l_end_id);
1739 END IF;
1740
1741 /*l_rows_processed := SQL%ROWCOUNT;*/
1742 l_rows_processed := l_end_id - l_start_id + 1;
1743
1744 l_stmt_num :=40;
1745 ad_parallel_updates_pkg.processed_id_range(
1746 l_rows_processed,
1747 l_end_id);
1748
1749 COMMIT;
1750
1751 l_stmt_num :=50;
1752 ad_parallel_updates_pkg.get_id_range(
1753 l_start_id,
1754 l_end_id,
1755 l_any_rows_to_process,
1756 X_batch_size,
1757 FALSE);
1758
1759 end loop;
1760 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
1761 fnd_file.put_line(fnd_file.LOG, 'WIP upgrade worker completed successfully.... ');
1762
1763 EXCEPTION
1764 WHEN OTHERS THEN
1765 fnd_file.put_line(fnd_file.LOG, x_errbuf);
1766 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
1767 raise;
1768 END;
1769
1770 <<out_arg_log>>
1771
1772 IF l_Log THEN
1773 log_message(
1774 fnd_log.level_procedure,
1775 l_module||'.end',
1776 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_WIP_WKR with '||
1777 'X_errbuf = '||X_errbuf||','||
1778 'X_retcode = '||X_retcode
1779 );
1780 END IF;
1781
1782 EXCEPTION
1783
1784 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1785 fnd_file.put_line(fnd_file.LOG, 'Unexpected error encountered. RETCODE:= ' || x_retcode || '. ' || x_errbuf);
1786 ROLLBACK;
1787 X_retcode := FND_API.g_ret_sts_unexp_error;
1788 handle_error ( X_module => l_module||'.'||l_stmt_num,
1789 X_message => 'ERROR: '||X_errbuf,
1790 X_reqerror => TRUE,
1791 X_errbuf => X_errbuf );
1792
1793 WHEN OTHERS THEN
1794 ROLLBACK;
1798 X_message => 'EXCEPTION: '||SQLERRM,
1795 fnd_file.put_line(fnd_file.LOG, 'Exception encountered. RETCODE:= ' || x_retcode || '. ' || x_errbuf);
1796 X_retcode := FND_API.g_ret_sts_unexp_error;
1797 handle_error ( X_module => l_module||'.'||l_stmt_num,
1799 X_reqerror => TRUE,
1800 X_errbuf => X_errbuf );
1801
1802 END Update_Proc_WIP_WKR;
1803
1804 -------------------------------------------------------------------------------------
1805 -- API name : Update_Proc_RCV_WKR
1806 -- Type : Private
1807 -- Function : Worker process to update RCV Sub Ledger to SLA data model
1808 -- Pre-reqs :
1809 -- Parameters : X_Argument4 is used to pass minimum ID;
1810 -- X_Argument5 is used to pass maximum ID.
1811 -- IN : X_batch_size in number,
1812 -- X_Worker_Id in number,
1813 -- X_Num_Workers in number,
1814 -- X_Argument4 in varchar2 default null,
1815 -- X_Argument5 in varchar2 default null,
1816 -- X_Argument6 in varchar2 default null,
1817 -- X_Argument7 in varchar2 default null,
1818 -- X_Argument8 in varchar2 default null,
1819 -- X_Argument9 in varchar2 default null,
1820 -- X_Argument10 in varchar2 default null
1821 --
1822 -- OUT : X_errbuf out NOCOPY varchar2,
1823 -- X_retcode out NOCOPY varchar2
1824 -- Version : Initial version 1.0
1825 -- Notes : The API is used for defining the "Upgrade Receiving Subledger to SLA"
1826 -- worker Concurrent Executable and Concurrent Program. It is called
1827 -- from Update_Proc_RCV_MGR by submitting multiple requests
1828 -- via AD_CONC_UTILS_PKG.submit_subrequests. It is also used by the
1829 -- downtime upgrade script cstrrsupg.sql.
1830 --
1831 -- End of comments
1832 -------------------------------------------------------------------------------------
1833
1834 PROCEDURE Update_Proc_RCV_WKR (
1835 X_errbuf out NOCOPY varchar2,
1836 X_retcode out NOCOPY varchar2,
1837 X_batch_size in number,
1838 X_Worker_Id in number,
1839 X_Num_Workers in number,
1840 X_Argument4 in varchar2 default null,
1841 X_Argument5 in varchar2 default null,
1842 X_Argument6 in varchar2 default null,
1843 X_Argument7 in varchar2 default null,
1844 X_Argument8 in varchar2 default null,
1845 X_Argument9 in varchar2 default null,
1846 X_Argument10 in varchar2 default null)
1847 IS
1848
1849 l_product varchar2(30);
1850 l_table_name varchar2(30) := 'RCV_TRANSACTIONS';
1851 l_id_column varchar2(30) := 'TRANSACTION_ID';
1852
1853
1854 l_update_name varchar2(30);
1855
1856 l_table_owner varchar2(30);
1857 l_status VARCHAR2(30);
1858 l_industry VARCHAR2(30);
1859 l_retstatus BOOLEAN;
1860 l_any_rows_to_process boolean;
1861
1862 l_start_id number;
1863 l_end_id number;
1864 l_rows_processed number;
1865
1866 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Proc_RCV_WKR';
1867 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND gRequestId > 0;
1868
1869 l_stmt_num number;
1870 l_mrc_temp number;
1871
1872 BEGIN
1873
1874 IF l_Log THEN
1875 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled TRUE');
1876 ELSE
1877 fnd_file.put_line(fnd_file.log, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')||'>FND Logging Enabled FALSE');
1878 END IF;
1879
1880 l_stmt_num :=0;
1881
1882 IF l_Log THEN
1883 log_message(
1884 fnd_log.level_procedure,
1885 l_module||'.begin',
1886 'Entering CST_SLA_UPDATE_PKG.Update_Proc_RCV_WKR with '||
1887 'X_batch_size = '||X_batch_size||','||
1888 'X_Worker_Id = '||X_Worker_Id||','||
1889 'X_Num_Workers = '||X_Num_Workers||','||
1890 'X_Argument4 = '||X_Argument4||','||
1891 'X_Argument5 = '||X_Argument5||','||
1892 'X_Argument6 = '||X_Argument6||','||
1893 'X_Argument7 = '||X_Argument7||','||
1894 'X_Argument8 = '||X_Argument8||','||
1895 'X_Argument9 = '||X_Argument9||','||
1896 'X_Argument10 = '||X_Argument10
1897 );
1898 END IF;
1899
1900 begin
1901 l_mrc_temp :=0;
1902
1903 select count(*)
1904 into l_mrc_temp
1905 from gl_mc_reporting_options_11i
1906 where application_id = 201
1907 and enabled_flag = 'Y'
1908 and rownum=1;
1909
1910 if (l_mrc_temp = 0) then
1911 g_mrc_enabled := FALSE;
1912 else
1913 g_mrc_enabled := TRUE;
1914 end if;
1915
1916 exception
1917 when others then
1918 g_mrc_enabled := FALSE;
1919 end;
1920
1921 --
1922 -- The following could be coded to use EXECUTE IMMEDIATE in order to remove build
1923 -- time
1924 -- dependencies as the processing could potentially reference some tables that
1925 -- could be obsoleted in the current release
1926 --
1927 BEGIN
1928 l_stmt_num :=10;
1929
1930 l_update_name := X_Argument6 || 'R' || X_Argument4 || '-' || X_Argument5;
1931
1932 l_retstatus := FND_INSTALLATION.GET_APP_INFO('PO', l_status, l_industry, l_table_owner);
1933
1934 ad_parallel_updates_pkg.initialize_id_range(
1935 X_update_type=>ad_parallel_updates_pkg.ID_RANGE,
1939 X_ID_column=>l_id_column,
1936 X_owner=>l_table_owner,
1937 X_table=>l_table_name,
1938 X_script=>l_update_name,
1940 X_worker_id=>X_Worker_Id,
1941 X_num_workers=>X_num_workers,
1942 X_batch_size=>X_batch_size,
1943 X_debug_level=>0);
1944
1945 l_stmt_num :=20;
1946 ad_parallel_updates_pkg.get_id_range(
1947 l_start_id,
1948 l_end_id,
1949 l_any_rows_to_process,
1950 X_batch_size,
1951 TRUE);
1952
1953 l_stmt_num :=25;
1954 IF NOT l_any_rows_to_process AND l_Log THEN
1955 log_message(
1956 fnd_log.level_procedure,
1957 l_module||'.'||l_stmt_num,
1958 'No Rows to Process in RCV'
1959 );
1960 END IF;
1961
1962 while (l_any_rows_to_process = TRUE)
1963 loop
1964 --
1965 -- Code CST SLA update logic here
1966 --
1967 IF l_Log THEN
1968 log_message(
1969 fnd_log.level_procedure,
1970 l_module||'.'||l_stmt_num,
1971 'Processing RCV Rows From '||l_start_id||' To '||l_end_id
1972 );
1973 END IF;
1974
1975 l_stmt_num :=30;
1976 CST_SLA_UPDATE_PKG.Update_Receiving_Subledger(
1977 X_errbuf=>X_errbuf,
1978 X_retcode=>X_retcode,
1979 X_min_id=>l_start_id,
1980 X_max_id=>l_end_id);
1981
1982 IF (X_retcode <>FND_API.G_RET_STS_SUCCESS) then
1983 X_errbuf := 'Failure while updating RCV Subledger from '|| l_start_id || ' To ' || l_end_id;
1984 fnd_file.put_line(fnd_file.LOG, 'RETCODE := ' || X_retcode || '. ' ||x_errbuf);
1985 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1986 ELSE
1987 fnd_file.put_line(fnd_file.LOG, 'Receiving upgrade completed successfully for Range : ' || l_start_id || ' To ' || l_end_id);
1988 END IF;
1989
1990 /*l_rows_processed := SQL%ROWCOUNT;*/
1991 l_rows_processed := l_end_id - l_start_id + 1;
1992
1993 l_stmt_num :=40;
1994 ad_parallel_updates_pkg.processed_id_range(
1995 l_rows_processed,
1996 l_end_id);
1997
1998 COMMIT;
1999
2000 l_stmt_num :=50;
2001 ad_parallel_updates_pkg.get_id_range(
2002 l_start_id,
2003 l_end_id,
2004 l_any_rows_to_process,
2005 X_batch_size,
2006 FALSE);
2007
2008 end loop;
2009 X_retcode := AD_CONC_UTILS_PKG.CONC_SUCCESS;
2010 fnd_file.put_line(fnd_file.LOG, 'Receiving upgrade worker completed successfully.... ');
2011
2012 EXCEPTION
2013 WHEN OTHERS THEN
2014 fnd_file.put_line(fnd_file.LOG, x_errbuf);
2015 X_retcode := AD_CONC_UTILS_PKG.CONC_FAIL;
2016 raise;
2017 END;
2018
2019 <<out_arg_log>>
2020
2021 IF l_Log THEN
2022 log_message(
2023 fnd_log.level_procedure,
2024 l_module||'.end',
2025 'Exiting CST_SLA_UPDATE_PKG.Update_Proc_RCV_WKR with '||
2026 'X_errbuf = '||X_errbuf||','||
2027 'X_retcode = '||X_retcode
2028 );
2029 END IF;
2030
2031 EXCEPTION
2032
2033 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2034 fnd_file.put_line(fnd_file.LOG, 'Unexpected error encountered. RETCODE:= ' || x_retcode || '. ' || x_errbuf);
2035 ROLLBACK;
2036 X_retcode := FND_API.g_ret_sts_unexp_error;
2037 handle_error ( X_module => l_module||'.'||l_stmt_num,
2038 X_message => 'ERROR: '||X_errbuf,
2039 X_reqerror => TRUE,
2040 X_errbuf => X_errbuf );
2041
2042 WHEN OTHERS THEN
2043 fnd_file.put_line(fnd_file.LOG, 'Exception encountered. RETCODE:= ' || x_retcode || '. ' || x_errbuf);
2044 ROLLBACK;
2045 X_retcode := FND_API.g_ret_sts_unexp_error;
2046 handle_error ( X_module => l_module||'.'||l_stmt_num,
2047 X_message => 'EXCEPTION: '||SQLERRM,
2048 X_reqerror => TRUE,
2049 X_errbuf => X_errbuf );
2050
2051 END Update_Proc_RCV_WKR;
2052
2053 -------------------------------------------------------------------------------------
2054 -- API name : Update_Inventory_Subledger
2055 -- Type : Private
2056 -- Function : To update Inventory Sub Ledger to SLA data model from minimum
2057 -- transaction ID to maximum transaction ID.
2058 -- Pre-reqs :
2059 -- Parameters :
2060 -- IN : X_min_id in number,
2061 -- X_max_id in number
2062 --
2063 -- OUT : X_errbuf out NOCOPY varchar2,
2064 -- X_retcode out NOCOPY varchar2
2065 --
2066 -- Notes : The API is called from Update_Proc_INV_WKR.
2067 --
2068 -- End of comments
2069 -------------------------------------------------------------------------------------
2070
2071 PROCEDURE Update_Inventory_Subledger (
2072 X_errbuf out NOCOPY varchar2,
2073 X_retcode out NOCOPY varchar2,
2074 X_min_id in number,
2075 X_max_id in number)
2076 IS
2077 l_upg_batch_id number(15):=0;
2078 l_je_category_name varchar2(30);
2079
2080 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Inventory_Subledger';
2081 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND gRequestId > 0;
2082
2083 l_stmt_num number;
2087
2084 l_rows_processed number;
2085
2086 BEGIN
2088 l_stmt_num :=0;
2089
2090 IF l_Log THEN
2091 log_message(
2092 fnd_log.level_procedure,
2093 l_module||'.begin',
2094 'Entering CST_SLA_UPDATE_PKG.Update_Inventory_Subledger with '||
2095 'X_min_id = '||X_min_id||','||
2096 'X_max_id = '||X_max_id
2097 );
2098 END IF;
2099
2100 select XLA_UPG_BATCHES_S.nextval into l_upg_batch_id from dual;
2101
2102 IF l_Log THEN
2103 log_message(
2104 fnd_log.level_procedure,
2105 l_module||'.'||l_stmt_num,
2106 'Upgrade Batch ID = '||l_upg_batch_id
2107 );
2108 END IF;
2109
2110 l_je_category_name := 'MTL';
2111
2112 /*execute immediate 'CREATE GLOBAL TEMPORARY TABLE cst_xla_seq_gt(
2113 source_id_int_1 NUMBER,
2114 source_id_int_2 number,
2115 source_id_int_3 number,
2116 source_id_int_4 number,
2117 source_id_int_5 number,
2118 entity_id number,
2119 event_id number,
2120 header_id NUMBER,
2121 legal_entity number,
2122 org_id number) ON COMMIT DELETE ROWS';*/
2123
2124 x_retcode := FND_API.G_RET_STS_SUCCESS;
2125
2126 /* one (txn_id,org_id,txn_src_type_id) -> one entity_id -> one event_id -> one header_id */
2127 /* one mta line -> one xla_ae_line -> one xla_distribution_link */
2128
2129 l_stmt_num :=10;
2130
2131 insert into cst_xla_seq_gt (
2132 source_id_int_1,
2133 source_id_int_2,
2134 source_id_int_3,
2135 entity_id,
2136 event_id,
2137 header_id)
2138 select transaction_id,
2139 organization_id,
2140 transaction_source_type_id,
2141 xla_transaction_entities_s.nextval,
2142 xla_events_s.nextval,
2143 xla_ae_headers_s.nextval
2144 from (
2145 select /*+ leading(mmt,oap) use_hash(xud, oap) swap_join_inputs(oap) index(mmt, mtl_material_transactions_u1) */
2146 distinct
2147 mta.transaction_id, mta.organization_id, mta.transaction_source_type_id
2148 from mtl_transaction_accounts mta,
2149 xla_upgrade_dates xud,
2150 hr_organization_information hoi2,
2151 org_acct_periods oap,
2152 mtl_material_transactions mmt
2153 where mmt.transaction_id >= X_min_id
2154 and mmt.transaction_id <= X_max_id
2155 and mmt.acct_period_id = oap.acct_period_id
2156 and mmt.organization_id = oap.organization_id
2157 and oap.period_start_date >= xud.start_date
2158 and oap.schedule_close_date <= xud.end_date
2159 and hoi2.organization_id = oap.organization_id
2160 and hoi2.org_information_context = 'Accounting Information'
2161 and hoi2.org_information1 = to_char (xud.ledger_id)
2162 and mmt.transaction_action_id not in (15, 22, 36)
2163 and mta.transaction_id = mmt.transaction_id
2164 and mta.encumbrance_type_id is null
2165 and mta.inv_sub_ledger_id is null
2166 and (mta.gl_batch_id > 0
2167 or mta.gl_batch_id = -1
2168 and not exists (
2169 select null
2170 from pjm_org_parameters pop
2171 where pop.organization_id = mta.organization_id
2172 and pop.pa_posting_flag = 'Y'
2173 and exists (
2174 select 1
2175 from mtl_material_transactions mmt1
2176 where mmt1.transaction_id = mta.transaction_id
2177 and (nvl(mmt1.logical_transaction, 2) = 2
2178 or mmt1.logical_transaction = 1
2179 and mmt1.transaction_type_id = 19
2180 and mmt1.transaction_action_id = 26
2181 and mmt1.transaction_source_type_id = 1
2182 and mmt1.logical_trx_type_code = 2
2183 and exists (
2184 select 1
2185 from rcv_transactions rt
2186 where rt.transaction_id = mmt1.rcv_transaction_id
2187 and rt.organization_id = mta.organization_id)))))
2188 --BUG#12660014: Do not upgrade AX entities -
2189 -- AX team has been provided with a script to do the upgrade of XLA entities for AX avents
2190 and not exists (SELECT NULL
2191 FROM ax_events ae
2192 WHERE ae.application_id = 401
2193 AND ae.translation_status IN ('T','W')
2194 AND ae.event_type <> 'INV_EVENT_OPW'
2195 AND ae.set_of_books_id = xud.ledger_id
2196 AND mta.transaction_id= ae.event_field1 ));
2197
2198 l_rows_processed := SQL%ROWCOUNT;
2199 IF (l_rows_processed = 0) THEN
2200 IF l_Log THEN
2201 log_message(
2202 fnd_log.level_procedure,
2203 l_module||'.'||l_stmt_num,
2204 'No rows to be upgraded. Exiting CST_SLA_UPDATE_PKG.Update_Inventory_Subledger with '||
2205 'X_errbuf = '||X_errbuf||','||
2206 'X_retcode = '||X_retcode
2207 );
2208 END IF;
2209 return;
2210 ELSE
2211 fnd_file.put_line(fnd_file.LOG, 'Update_Inventory_Subledger.. Processed rows : ' || l_rows_processed);
2212 END IF;
2213
2214 l_stmt_num :=20;
2215
2219 last_updated_by = gUserId,
2216 update mtl_transaction_accounts mta
2217 set inv_sub_ledger_id = cst_inv_sub_ledger_id_s.nextval,
2218 last_update_date = gUpdateDate,
2220 last_update_login = gLoginId
2221 where (mta.transaction_id, mta.organization_id, mta.transaction_source_type_id) in
2222 (select /*+ unnest */
2223 source_id_int_1,
2224 source_id_int_2,
2225 source_id_int_3
2226 from cst_xla_seq_gt cxs)
2227 and mta.encumbrance_type_id is null;
2228
2229 l_stmt_num :=30;
2230
2231 insert all
2232 when (line_id=1) then
2233 into xla_transaction_entities (
2234 upg_batch_id,
2235 entity_id,
2236 application_id,
2237 ledger_id,
2238 entity_code,
2239 source_id_int_1,
2240 source_id_int_2,
2241 source_id_int_3,
2242 security_id_int_1,
2243 transaction_number,
2244 creation_date,
2245 created_by,
2246 last_update_date,
2247 last_updated_by,
2248 last_update_login,
2249 source_application_id,
2250 upg_source_application_id)
2251 values (l_upg_batch_id,
2252 entity_id,
2253 707,
2254 ledger_id,
2255 'MTL_ACCOUNTING_EVENTS',
2256 transaction_id,
2257 organization_id,
2258 transaction_source_type_id,
2259 organization_id,
2260 transaction_id,
2261 creation_date,
2262 created_by,
2263 last_update_date,
2264 last_updated_by,
2265 last_update_login,
2266 401,
2267 401)
2268 into xla_events (
2269 upg_batch_id,
2270 application_id,
2271 entity_id,
2272 event_id,
2273 event_number,
2274 event_type_code,
2275 event_date,
2276 event_status_code,
2277 process_status_code,
2278 on_hold_flag,
2279 creation_date,
2280 created_by,
2281 last_update_date,
2282 last_updated_by,
2283 last_update_login,
2284 program_update_date,
2285 program_application_id,
2286 program_id,
2287 request_id,
2288 transaction_date,
2289 upg_source_application_id)
2290 values (l_upg_batch_id,
2291 707,
2292 entity_id,
2293 event_id,
2294 1,
2295 event_type_code,
2296 transaction_date,
2297 'P',
2298 'P',
2299 'N',
2300 creation_date,
2301 created_by,
2302 last_update_date,
2303 last_updated_by,
2304 last_update_login,
2305 program_update_date,
2306 program_application_id,
2307 program_id,
2308 request_id,
2309 transaction_date,
2310 401)
2311 into xla_ae_headers (
2312 upg_batch_id,
2313 application_id,
2314 amb_context_code,
2315 entity_id,
2316 event_id,
2317 event_type_code,
2318 ae_header_id,
2319 ledger_id,
2320 je_category_name,
2321 accounting_date,
2322 period_name,
2323 balance_type_code,
2324 gl_transfer_status_code,
2325 gl_transfer_date,
2326 accounting_entry_status_code,
2327 accounting_entry_type_code,
2328 creation_date,
2329 created_by,
2330 last_update_date,
2331 last_updated_by,
2332 last_update_login,
2333 program_update_date,
2334 program_application_id,
2335 program_id,
2336 request_id,
2337 zero_amount_flag,
2338 upg_source_application_id)
2339 values (l_upg_batch_id,
2340 707,
2341 'DEFAULT',
2342 entity_id,
2343 event_id,
2344 event_type_code,
2345 header_id,
2346 ledger_id,
2347 l_je_category_name,
2348 gl_date,
2349 period_name,
2350 'A',
2351 gl_transfer_status_code,
2352 gl_transfer_date,
2353 'F',
2354 'STANDARD',
2355 creation_date,
2356 created_by,
2357 last_update_date,
2358 last_updated_by,
2359 last_update_login,
2360 program_update_date,
2361 program_application_id,
2362 program_id,
2363 request_id,
2364 zero_amount_flag,
2365 401)
2366 when (1=1) then
2367 into xla_ae_lines (
2368 upg_batch_id,
2369 application_id,
2370 ae_header_id,
2371 ae_line_num,
2372 code_combination_id,
2373 gl_transfer_mode_code,
2374 accounted_dr,
2375 accounted_cr,
2376 currency_code,
2377 currency_conversion_date,
2378 currency_conversion_rate,
2379 currency_conversion_type,
2380 entered_dr,
2381 entered_cr,
2382 accounting_class_code,
2383 gl_sl_link_id,
2387 gain_or_loss_flag,
2384 gl_sl_link_table,
2385 ussgl_transaction_code,
2386 control_balance_flag,
2388 creation_date,
2389 created_by,
2390 last_update_date,
2391 last_updated_by,
2392 last_update_login,
2393 program_update_date,
2394 program_application_id,
2395 program_id,
2396 request_id,
2397 accounting_date,
2398 ledger_id)
2399 values (l_upg_batch_id,
2400 707,
2401 header_id,
2402 line_id,
2403 ref_account,
2404 gl_update_code,
2405 accounted_dr,
2406 accounted_cr,
2407 currency_code,
2408 currency_conversion_date,
2409 currency_conversion_rate,
2410 currency_conversion_type,
2411 entered_dr,
2412 entered_cr,
2413 accounting_class_code,
2414 link_id,
2415 'MTA',
2416 ussgl_transaction_code,
2417 control_balance_flag,
2418 'N',
2419 creation_date,
2420 created_by,
2421 last_update_date,
2422 last_updated_by,
2423 last_update_login,
2424 program_update_date,
2425 program_application_id,
2426 program_id,
2427 request_id,
2428 gl_date,
2429 ledger_id)
2430 into xla_distribution_links (
2431 upg_batch_id,
2432 application_id,
2433 event_id,
2434 ae_header_id,
2435 ae_line_num,
2436 accounting_line_code,
2437 accounting_line_type_code,
2438 source_distribution_type,
2439 source_distribution_id_num_1,
2440 merge_duplicate_code,
2441 ref_ae_header_id,
2442 temp_line_num,
2443 event_class_code,
2444 event_type_code)
2445 values (l_upg_batch_id,
2446 707,
2447 event_id,
2448 header_id,
2449 line_id,
2450 accounting_class_code,
2451 'C',
2452 'MTL_TRANSACTION_ACCOUNTS',
2453 src_dist_id_num_1,
2454 'N',
2455 header_id,
2456 line_id,
2457 event_class_code,
2458 event_type_code)
2459
2460 select /*+ leading(txn) use_hash(cem) swap_join_inputs(cem) */
2461 row_number () over (partition by txn.transaction_id, txn.organization_id, txn.transaction_source_type_id
2462 order by txn.transaction_id) as line_id,
2463 txn.creation_date,
2464 txn.last_update_date,
2465 txn.created_by,
2466 txn.last_updated_by,
2467 txn.last_update_login,
2468 txn.program_update_date,
2469 txn.program_application_id,
2470 txn.program_id,
2471 txn.request_id,
2472 txn.entity_id,
2473 txn.event_id,
2474 txn.header_id,
2475 txn.transaction_id,
2476 txn.organization_id,
2477 txn.transaction_source_type_id,
2478 txn.transaction_date,
2479 txn.gl_date,
2480 txn.gl_transfer_date,
2481 txn.ledger_id,
2482 txn.period_name,
2483 txn.gl_transfer_status_code,
2484 txn.currency_code,
2485 txn.ref_account,
2486 txn.gl_update_code,
2487 txn.link_id,
2488 txn.src_dist_id_num_1,
2489 txn.accounted_dr,
2490 txn.accounted_cr,
2491 txn.currency_conversion_date,
2492 txn.currency_conversion_rate,
2493 txn.currency_conversion_type,
2494 txn.entered_dr,
2495 txn.entered_cr,
2496 txn.entered_amount,
2497 decode (txn.btvsign, 0, 'Y', 'N') zero_amount_flag,
2498 txn.ussgl_transaction_code,
2499 txn.control_balance_flag,
2500 cem.event_type_code,
2501 cem.event_class_code,
2502 decode(txn.accounting_line_type,
2503 1, 'INVENTORY_VALUATION',
2504 2, decode(cem.event_class_code,
2505 'FOB_RCPT_SENDER_RCPT', decode(txn.btvsign, 1, 'COST_OF_GOODS_SOLD', 'OFFSET'),
2506 'FOB_SHIP_SENDER_SHIP', decode(txn.btvsign, 1, 'COST_OF_GOODS_SOLD', 'OFFSET'),
2507 'SALES_ORDER', 'COST_OF_GOODS_SOLD',
2508 'MTL_COST_UPD', 'COST_UPDATE_ADJUSTMENT',
2509 'WIP_COST_UPD', 'COST_UPDATE_ADJUSTMENT',
2510 'LOG_INTERCOMPANY', decode(txn.transaction_action_id,
2511 9, decode(txn.btvsign, 1, 'INTERCOMPANY_COGS', 'OFFSET'),
2512 decode(txn.btvsign, -1, 'INTERCOMPANY_COGS', 'OFFSET')),
2513 'FOB_RCPT_RECIPIENT_RCPT', decode(txn.btvsign, -1, 'INTERCOMPANY_ACCRUAL', 'OFFSET'),
2514 'FOB_SHIP_RECIPIENT_SHIP', decode(txn.btvsign, -1, 'INTERCOMPANY_ACCRUAL', 'OFFSET'),
2515 'OFFSET'),
2516 3, decode(cem.event_class_code,
2517 'ABSORPTION', 'OVERHEAD_ABSORPTION',
2518 'OSP', 'OVERHEAD_ABSORPTION',
2519 'MATERIAL_OVERHEAD_ABSORPTION'),
2520 4, 'RESOURCE_ABSORPTION',
2521 5, 'RECEIVING_INSPECTION',
2522 6, decode(cem.event_class_code,
2526 8, 'WIP_VARIANCE',
2523 'ABSORPTION', 'RESOURCE_RATE_VARIANCE',
2524 'PURCHASE_PRICE_VARIANCE'),
2525 7, 'WIP_VALUATION',
2527 9, 'INTERORG_PAYABLES',
2528 10, 'INTERORG_RECEIVABLES',
2529 11, 'INTERORG_TRANSFER_CREDIT',
2530 12, 'INTERORG_FREIGHT_CHARGE',
2531 13, 'COST_VARIANCE',
2532 14, 'INTRANSIT_VALUATION',
2533 15, 'ENCUMBRANCE_REVERSAL',
2534 16, decode(cem.event_class_code,
2535 'LOG_INTERCOMPANY', 'INTERCOMPANY_ACCRUAL',
2536 'ACCRUAL'),
2537 17, 'INVOICE_PRICE_VARIANCE',
2538 18, 'EXCHANGE_RATE_VARIANCE',
2539 19, 'SPECIAL_CHARGE_EXPENSE',
2540 20, 'EXPENSE',
2541 21, 'WIP_VALUATION',
2542 22, 'WIP_VALUATION',
2543 23, 'WIP_VALUATION',
2544 24, 'WIP_VALUATION',
2545 25, 'WIP_VALUATION',
2546 26, 'WIP_VALUATION',
2547 27, 'WIP_VALUATION',
2548 28, 'WIP_VALUATION',
2549 29, 'ESTIMATED_SCRAP_ABSORPTION',
2550 30, 'PROFIT_IN_INVENTORY',
2551 31, 'CLEARING',
2552 32, 'RETROACTIVE_PRICE_ADJUSTMENT',
2553 33, 'SHIKYU_VARIANCE',
2554 34, 'INTERORG_PROFIT',
2555 35, 'COST_OF_GOODS_SOLD',
2556 36, 'DEFERRED_COGS',
2557 37, 'COST_UPDATE_ADJUSTMENT',
2558 'OFFSET') accounting_class_code
2559 from cst_xla_inv_event_map cem,
2560 (select /*+ no_merge leading(cxs, mmt) use_hash(oap) swap_join_inputs(oap)
2561 use_nl_with_index(mmt) use_nl_with_index(mta) use_nl_with_index(hoi2) use_nl_with_index(gcc) */
2562 mta.creation_date,
2563 mta.last_update_date,
2564 mta.created_by,
2565 mta.last_updated_by,
2566 mta.last_update_login,
2567 mta.program_update_date,
2568 mta.program_application_id,
2569 mta.program_id,
2570 mta.request_id,
2571 cxs.entity_id,
2572 cxs.event_id,
2573 cxs.header_id,
2574 mta.transaction_id,
2575 mta.organization_id,
2576 mta.transaction_source_type_id,
2577 mmt.transaction_date,
2578 mmt.primary_quantity,
2579 trunc(inv_le_timezone_pub.get_le_day_for_server(mmt.transaction_date, hoi2.org_information2)) gl_date,
2580 ogb.gl_batch_date gl_transfer_date,
2581 sob.ledger_id,
2582 period_name,
2583 decode(mta.gl_batch_id,
2584 -1, decode (mp.general_ledger_update_code, 3, 'NT', 'N'),
2585 decode (sign (mta.gl_batch_id), 1, 'Y', 'NT')) gl_transfer_status_code,
2586 decode(nvl(mta.encumbrance_type_id, -1),
2587 -1, nvl(mta.currency_code, sob.currency_code),
2588 sob.currency_code) currency_code,
2589 mta.reference_account ref_account,
2590 decode(mp.general_ledger_update_code,
2591 1, 'D',
2592 2, 'S',
2593 'N') gl_update_code,
2594 mta.gl_sl_link_id link_id,
2595 mta.inv_sub_ledger_id src_dist_id_num_1,
2596 decode(sign(mta.base_transaction_value),
2597 0, decode(sign(nvl(mta.transaction_value, mta.primary_quantity)),
2598 0, 0,
2599 1, 0,
2600 null),
2601 1, mta.base_transaction_value,
2602 null) accounted_dr,
2603 decode(sign(mta.base_transaction_value),
2604 0, decode(sign(nvl(mta.transaction_value, mta.primary_quantity)),
2605 -1, 0,
2606 null),
2607 -1, (-1 * mta.base_transaction_value),
2608 null) accounted_cr,
2609 mta.currency_conversion_date,
2610 mta.currency_conversion_rate,
2611 mta.currency_conversion_type,
2612 decode(nvl(mta.encumbrance_type_id, -1),
2613 -1, decode(sign(mta.base_transaction_value),
2614 0, decode(sign(nvl(mta.transaction_value, mta.primary_quantity)),
2615 0, 0,
2616 1, nvl(mta.transaction_value, 0),
2617 null),
2618 1, nvl(mta.transaction_value, mta.base_transaction_value),
2619 null),
2620 decode(sign(mta.base_transaction_value),
2621 0, 0, 1, mta.base_transaction_value, null)) entered_dr,
2622 decode(nvl(mta.encumbrance_type_id, -1),
2623 -1, decode(sign(mta.base_transaction_value),
2624 0, decode(sign(nvl(mta.transaction_value, mta.primary_quantity)),
2625 -1, -1 * nvl(mta.transaction_value, 0),
2626 null),
2627 -1, (-1 * nvl(mta.transaction_value, mta.base_transaction_value)),
2628 null),
2629 decode(sign(mta.base_transaction_value),
2630 -1, (-1 * mta.base_transaction_value), null)) entered_cr,
2631 nvl (mta.transaction_value, mta.base_transaction_value) entered_amount,
2635 mmt.transaction_action_id,
2632 mta.ussgl_transaction_code,
2633 decode (gcc.reference3, 'Y', 'P', null) control_balance_flag,
2634 mta.accounting_line_type,
2636 mmt.source_code,
2637 mmt.organization_id mmt_organization_id,
2638 mmt.transaction_source_type_id mmt_transaction_source_type_id,
2639 sign(mta.base_transaction_value) btvsign,
2640 case when mmt.transaction_action_id in (12, 21) then (
2641 select 1
2642 from mtl_transaction_accounts mta1
2643 where mta1.accounting_line_type in (9,10)
2644 and mta1.transaction_id = mta.transaction_id
2645 and mta1.organization_id = mta.organization_id
2646 and mta1.transaction_source_type_id = mta.transaction_source_type_id
2647 and rownum = 1)
2648 else null
2649 end exists_9_10,
2650 case when mmt.transaction_action_id in (12, 21) then (
2651 select 1
2652 from mtl_transaction_accounts mta1
2653 where mta1.accounting_line_type = 14
2654 and mta1.transaction_id = mta.transaction_id
2655 and mta1.organization_id = mta.organization_id
2656 and mta1.transaction_source_type_id = mta.transaction_source_type_id
2657 and rownum = 1)
2658 else null
2659 end exists_14
2660 from mtl_transaction_accounts mta,
2661 mtl_material_transactions mmt,
2662 gl_code_combinations gcc,
2663 mtl_parameters mp,
2664 hr_organization_information hoi2,
2665 gl_ledgers sob,
2666 org_acct_periods oap,
2667 org_gl_batches ogb,
2668 cst_xla_seq_gt cxs
2669 where mta.transaction_id = cxs.source_id_int_1
2670 and mta.organization_id = cxs.source_id_int_2
2671 and nvl(mta.transaction_source_type_id, -1) = nvl(cxs.source_id_int_3, -1)
2672 and mta.encumbrance_type_id is null
2673 and mp.organization_id = hoi2.organization_id
2674 and mta.organization_id = mp.organization_id
2675 and mta.organization_id = ogb.organization_id (+)
2676 and mta.gl_batch_id = ogb.gl_batch_id (+)
2677 and mmt.transaction_id = cxs.source_id_int_1
2678 and gcc.code_combination_id = mta.reference_account
2679 and oap.organization_id = mmt.organization_id
2680 and mmt.acct_period_id = oap.acct_period_id
2681 and hoi2.org_information_context = 'Accounting Information'
2682 and sob.ledger_ID = TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI2.ORG_INFORMATION1,'0123456789',' ')),
2683 NULL, HOI2.ORG_INFORMATION1,
2684 -99999))
2685 and sob.object_type_code = 'L')
2686 txn
2687 where txn.transaction_action_id = cem.transaction_action_id
2688 and (cem.transaction_source_type_id = txn.transaction_source_type_id
2689 and (txn.transaction_action_id not in (1, 2, 3, 12, 21, 24, 17)
2690 or txn.transaction_action_id = 1
2691 and (txn.transaction_source_type_id <> 13
2692 or cem.attribute = 'CITW' and txn.mmt_transaction_source_type_id = 5
2693 or cem.attribute is null and txn.mmt_transaction_source_type_id = 13)
2694 and (txn.transaction_source_type_id <> 8 or cem.tp = 'N')
2695 or txn.transaction_action_id = 2
2696 and txn.transaction_source_type_id in (4, 8, 9, 10, 13)
2697 or txn.transaction_action_id = 3
2698 and txn.transaction_source_type_id = 8
2699 and cem.organization = 'SAME'
2700 and cem.tp = 'N'
2701 and txn.organization_id = txn.mmt_organization_id
2702 or txn.transaction_action_id = 3
2703 and txn.transaction_source_type_id = 13
2704 and cem.organization = 'SAME'
2705 and cem.tp = 'N'
2706 and txn.organization_id = txn.mmt_organization_id
2707 and (txn.primary_quantity < 0 and cem.transfer_type = 'SHIP'
2708 or
2709 txn.primary_quantity > 0 and cem.transfer_type = 'RCPT')
2710 or txn.transaction_action_id = 3
2711 and txn.transaction_source_type_id in (7, 13)
2712 and cem.organization = 'TRANSFER'
2713 and cem.tp = 'N'
2714 and txn.organization_id <> txn.mmt_organization_id
2715 /* Added for Direct interorg int req receipt in avg/LIFO/FIFO */
2716 or txn.transaction_action_id = 3
2717 and txn.transaction_source_type_id = 7
2718 and cem.organization = 'SAME'
2719 and cem.tp = 'N'
2720 and txn.organization_id = txn.mmt_organization_id
2721 or txn.transaction_action_id = 24
2722 and txn.transaction_source_type_id = 13
2723 and cem.attribute = 'VARIANCE TRF'
2724 and txn.source_code is not null
2725 or txn.transaction_action_id = 24
2726 and txn.transaction_source_type_id = 13
2727 and cem.attribute is null
2728 and txn.source_code is null
2729 or txn.transaction_action_id = 24
2730 and txn.transaction_source_type_id in (11, 15)
2731 /* FOB_SHIP_RECIPIENT_RCPT */
2732 or txn.transaction_action_id = 12
2736 and cem.fob_point = 1
2733 and txn.transaction_source_type_id in (7, 13)
2734 and txn.organization_id = txn.mmt_organization_id
2735 and cem.organization = 'SAME'
2737 and cem.tp is null
2738 and txn.exists_14 = 1
2739 /* FOB_RCPT_RECIPIENT_RCPT */
2740 or txn.transaction_action_id = 12
2741 and txn.transaction_source_type_id in (7, 13)
2742 and txn.organization_id = txn.mmt_organization_id
2743 and cem.organization = 'SAME'
2744 and cem.fob_point = 2
2745 and txn.exists_14 is null
2746 and (cem.tp = 'Y' and txn.exists_9_10 is null
2747 or
2748 cem.tp = 'N' and txn.exists_9_10 = 1)
2749 /* FOB_RCPT_SENDER_RCPT */
2750 or txn.transaction_action_id = 12
2751 and txn.transaction_source_type_id in (7, 13)
2752 and txn.organization_id <> txn.mmt_organization_id
2753 and cem.organization = 'TRANSFER'
2754 and cem.fob_point = 2
2755 and txn.exists_14 = 1
2756 and (cem.tp = 'Y' and txn.exists_9_10 is null
2757 or
2758 cem.tp = 'N' and txn.exists_9_10 = 1)
2759 /* FOB_RCPT_SENDER_SHIP */
2760 or txn.transaction_action_id = 21
2761 and txn.transaction_source_type_id in (8, 13)
2762 and txn.organization_id = txn.mmt_organization_id
2763 and cem.organization = 'SAME'
2764 and cem.fob_point = 2
2765 and cem.tp is null
2766 and txn.exists_14 = 1
2767 /* FOB_SHIP_SENDER_SHIP */
2768 or txn.transaction_action_id = 21
2769 and txn.transaction_source_type_id in (8, 13)
2770 and txn.organization_id = txn.mmt_organization_id
2771 and cem.organization = 'SAME'
2772 and cem.fob_point = 1
2773 and txn.exists_14 is null
2774 and (cem.tp = 'Y' and txn.exists_9_10 is null
2775 or
2776 cem.tp = 'N' and txn.exists_9_10 = 1)
2777 /* FOB_SHIP_RECIPIENT_SHIP */
2778 or txn.transaction_action_id = 21
2779 and txn.transaction_source_type_id in (8, 13)
2780 and txn.organization_id <> txn.mmt_organization_id
2781 and cem.organization = 'TRANSFER'
2782 and cem.fob_point = 1
2783 and txn.exists_14 = 1
2784 and (cem.tp = 'Y' and txn.exists_9_10 is null
2785 or
2786 cem.tp = 'N' and txn.exists_9_10 = 1)
2787 or txn.transaction_action_id = 17
2788 and (txn.transaction_source_type_id <> 7
2789 or cem.tp = 'N'))
2790 or cem.transaction_source_type_id is null
2791 and
2792 ( txn.transaction_action_id = 2
2793 and txn.transaction_source_type_id not in (4, 8, 9, 10, 13)
2794 or txn.transaction_action_id = 3
2795 and txn.transaction_source_type_id not in (7, 8, 13)
2796 or txn.transaction_action_id = 24
2797 and txn.transaction_source_type_id not in (11, 13, 15)
2798 /* FOB_SHIP_SENDER_SHIP_ALL */
2799 or txn.transaction_action_id = 21
2800 and txn.transaction_source_type_id not in (8, 13)
2801 and txn.organization_id = txn.mmt_organization_id
2802 and cem.organization = 'SAME'
2803 and cem.fob_point = 1
2804 and txn.exists_14 is null
2805 /* FOB_SHIP_RECIPIENT_SHIP_ALL */
2806 or txn.transaction_action_id = 21
2807 and txn.transaction_source_type_id not in (8, 13)
2808 and txn.organization_id <> txn.mmt_organization_id
2809 and cem.organization = 'TRANSFER'
2810 and cem.fob_point = 1
2811 and txn.exists_14 = 1
2812 /* FOB_RCPT_SENDER_SHIP_ALL */
2813 or txn.transaction_action_id = 21
2814 and txn.transaction_source_type_id not in (8, 13)
2815 and txn.organization_id = txn.mmt_organization_id
2816 and cem.organization = 'SAME'
2817 and cem.fob_point = 2
2818 and txn.exists_14 = 1)
2819 /* For User Defined Transaction Types for action 1 and 27*/
2820 or cem.transaction_source_type_id = -999
2821 and( txn.transaction_action_id in (1,27)
2822 and txn.transaction_source_type_id not in
2823 ( 1,2,3,4,5,6,7,8,9,10,11,12,13,15,16 )
2824 )
2825 and cem.attribute is null
2826 /* Added the condition for including transaction_source_type =7
2827 when creating the event for action =3 and source type = 8
2828 in the case of std to std. As only shipment transaction
2829 (action =3 and src_type =8) will be accounted but we need
2830 to raise event for the shipment transaction with event type
2831 of receipt( action = 3 and source type = 7) too.
2832 */
2833 or cem.transaction_source_type_id = 7
2834 and txn.transaction_action_id = 3
2835 and txn.transaction_source_type_id = 8
2836 and cem.organization = 'TRANSFER'
2837 and cem.tp = 'N'
2838 and txn.organization_id <> txn.mmt_organization_id
2839 );
2840
2841 <<out_arg_log>>
2842
2846 l_module||'.end',
2843 IF l_Log THEN
2844 log_message(
2845 fnd_log.level_procedure,
2847 'Exiting CST_SLA_UPDATE_PKG.Update_Inventory_Subledger with '||
2848 'X_errbuf = '||X_errbuf||','||
2849 'X_retcode = '||X_retcode
2850 );
2851 END IF;
2852
2853 EXCEPTION
2854
2855 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2856 ROLLBACK;
2857 X_retcode := FND_API.g_ret_sts_unexp_error;
2858 handle_error ( X_module => l_module||'.'||l_stmt_num,
2859 X_message => 'ERROR: '||X_errbuf,
2860 X_reqerror => FALSE,
2861 X_errbuf => X_errbuf );
2862
2863 WHEN OTHERS THEN
2864 ROLLBACK;
2865 X_retcode := FND_API.g_ret_sts_unexp_error;
2866 handle_error ( X_module => l_module||'.'||l_stmt_num,
2867 X_message => 'EXCEPTION: '||SQLERRM,
2868 X_reqerror => FALSE,
2869 X_errbuf => X_errbuf );
2870
2871 end Update_Inventory_Subledger;
2872
2873 -------------------------------------------------------------------------------------
2874 -- API name : Update_WIP_Subledger
2875 -- Type : Private
2876 -- Function : To update WIP Sub Ledger to SLA data model from minimum
2877 -- transaction ID to maximum transaction ID.
2878 -- Pre-reqs :
2879 -- Parameters :
2880 -- IN : X_min_id in number,
2881 -- X_max_id in number
2882 --
2883 -- OUT : X_errbuf out NOCOPY varchar2,
2884 -- X_retcode out NOCOPY varchar2
2885 --
2886 -- Notes : The API is called from Update_Proc_WIP_WKR.
2887 --
2888 -- End of comments
2889 -------------------------------------------------------------------------------------
2890 PROCEDURE Update_WIP_Subledger (
2891 X_errbuf out NOCOPY varchar2,
2892 X_retcode out NOCOPY varchar2,
2893 X_min_id in number,
2894 X_max_id in number)
2895 IS
2896 l_upg_batch_id number(15):=0;
2897 l_je_category_name varchar2(30);
2898
2899 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_WIP_Subledger';
2900 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND gRequestId > 0;
2901
2902 l_stmt_num number;
2903 l_rows_processed number;
2904 BEGIN
2905
2906 l_stmt_num :=0;
2907
2908 IF l_Log THEN
2909 log_message(
2910 fnd_log.level_procedure,
2911 l_module||'.begin',
2912 'Entering CST_SLA_UPDATE_PKG.Update_WIP_Subledger with '||
2913 'X_min_id = '||X_min_id||','||
2914 'X_max_id = '||X_max_id
2915 );
2916 END IF;
2917
2918 select XLA_UPG_BATCHES_S.nextval into l_upg_batch_id from dual;
2919
2920 IF l_Log THEN
2921 log_message(
2922 fnd_log.level_procedure,
2923 l_module||'.'||l_stmt_num,
2924 'Upgrade Batch ID = '||l_upg_batch_id
2925 );
2926 END IF;
2927
2928 l_je_category_name := 'WIP';
2929
2930 x_retcode := FND_API.G_RET_STS_SUCCESS;
2931
2932 /* one (txn_id,org_id,txn_src_type_id) -> one entity_id -> one event_id -> one header_id */
2933 /* one wta line -> one xla_ae_line -> one xla_distribution_link */
2934
2935 l_stmt_num :=10;
2936
2937 insert into cst_xla_seq_gt(
2938 source_id_int_1,
2939 source_id_int_2,
2940 source_id_int_3,
2941 entity_id,
2942 event_id,
2943 header_id)
2944 select transaction_id,
2945 resource_id,
2946 basis_type,
2947 XLA_transaction_ENTITies_S.nextval,
2948 xla_events_s.nextval,
2949 xla_ae_headers_s.NEXTVAL
2950 from (select distinct wta.transaction_id, wta.resource_id, wta.basis_type
2951 from wip_transaction_accounts wta
2952 where wta.transaction_id >= X_min_id
2953 and wta.transaction_id <= X_max_id
2954 and exists (select null
2955 from xla_upgrade_dates xud,
2956 HR_ORGANIZATION_INFORMATION HOI2,
2957 ORG_ACCT_PERIODS oap,
2958 wip_transactions wt
2959 where HOI2.organization_id = oap.organization_id
2960 and HOI2.ORG_INFORMATION_CONTEXT ='Accounting Information'
2961 AND HOI2.ORG_INFORMATION1 = TO_CHAR(xud.ledger_ID)
2962 and wt.acct_period_id = oap.acct_period_id
2963 and oap.ORGANIZATION_ID = wt.organization_id
2964 and oap.period_start_date >= xud.start_date
2965 and oap.schedule_close_date <= xud.end_date
2966 and wta.transaction_id = wt.transaction_id)
2967 and (wta.gl_batch_id > 0
2968 or wta.gl_batch_id = -1
2969 and not exists
2970 (select null
2971 from pjm_org_parameters pop
2972 where pop.organization_id = wta.organization_id
2973 and pop.pa_posting_flag = 'Y')
2974
2975 )
2976 and wta.wip_sub_ledger_id is null);
2977
2978 l_rows_processed := SQL%ROWCOUNT;
2979 IF (l_rows_processed = 0) THEN
2980 IF l_Log THEN
2981 log_message(
2982 fnd_log.level_procedure,
2983 l_module||'.'||l_stmt_num,
2984 'No rows to be upgraded. Exiting CST_SLA_UPDATE_PKG.Update_WIP_Subledger with '||
2985 'X_errbuf = '||X_errbuf||','||
2986 'X_retcode = '||X_retcode
2990 ELSE
2987 );
2988 END IF;
2989 return;
2991 fnd_file.put_line(fnd_file.LOG, 'Update_WIP_Subledger.. Processed rows : ' || l_rows_processed);
2992 END IF;
2993
2994 l_stmt_num :=20;
2995
2996 update /*+ leading(cxs) use_nl(wta) index(wta) */
2997 wip_transaction_accounts wta
2998 set wip_sub_ledger_id = cst_wip_sub_ledger_id_s.nextval,
2999 last_update_date = gUpdateDate,
3000 last_updated_by = gUserId,
3001 last_update_login = gLoginId
3002 where (wta.transaction_id, nvl(wta.resource_id,-6661), nvl(wta.basis_type,-6661)) in
3003 (select source_id_int_1,
3004 nvl(source_id_int_2,-6661),
3005 nvl(source_id_int_3,-6661)
3006 from cst_xla_seq_gt cxs);
3007
3008
3009 l_stmt_num :=30;
3010
3011 insert all
3012 when (line_id=1) then
3013 INTO XLA_TRANSACTION_ENTITIES (
3014 upg_batch_id,
3015 entity_id,
3016 application_id,
3017 ledger_id,
3018 legal_entity_id,
3019 entity_code,
3020 source_id_int_1,
3021 SOURCE_ID_INT_2,
3022 SOURCE_ID_INT_3,
3023 security_id_int_1,
3024 TRANSACTION_NUMBER,
3025 creation_date,
3026 created_by,
3027 last_update_date,
3028 last_updated_by,
3029 LAST_UPDATE_LOGIN,
3030 source_application_id,
3031 UPG_SOURCE_APPLICATION_ID)
3032 values (l_upg_batch_id,
3033 entity_id,
3034 707,
3035 ledger_id,
3036 legal_entity_id,
3037 'WIP_ACCOUNTING_EVENTS',
3038 transaction_id,
3039 resource_id,
3040 basis_type,
3041 organization_id,
3042 transaction_id,
3043 creation_date,
3044 CREATED_BY,
3045 last_update_date,
3046 last_updated_by,
3047 LAST_UPDATE_LOGIN,
3048 706,
3049 706)
3050 into xla_events (
3051 upg_batch_id,
3052 APPLICATION_ID,
3053 entity_id,
3054 event_id,
3055 event_number,
3056 event_type_code,
3057 event_date,
3058 event_status_code,
3059 PROCESS_STATUS_CODE,
3060 ON_HOLD_FLAG,
3061 CREATION_DATE,
3062 created_by,
3063 last_update_date,
3064 last_updated_by,
3065 LAST_UPDATE_LOGIN,
3066 PROGRAM_UPDATE_DATE,
3067 PROGRAM_APPLICATION_ID,
3068 PROGRAM_ID,
3069 REQUEST_ID,
3070 TRANSACTION_DATE,
3071 UPG_SOURCE_APPLICATION_ID)
3072 values (l_upg_batch_id,
3073 707,
3074 entity_id,
3075 event_id,
3076 1,
3077 event_type_code,
3078 transaction_date,
3079 'P',
3080 'P',
3081 'N',
3082 creation_date,
3083 created_by,
3084 last_update_date,
3085 last_updated_by,
3086 LAST_UPDATE_LOGIN,
3087 PROGRAM_UPDATE_DATE,
3088 PROGRAM_APPLICATION_ID,
3089 PROGRAM_ID,
3090 REQUEST_ID,
3091 TRANSACTION_DATE,
3092 706)
3093 into xla_ae_headers (
3094 upg_batch_id,
3095 application_id,
3096 AMB_CONTEXT_CODE,
3097 entity_id,
3098 event_id,
3099 event_type_code,
3100 ae_header_id,
3101 ledger_id,
3102 je_category_name,
3103 ACCOUNTING_DATE,
3104 PERIOD_NAME,
3105 BALANCE_TYPE_CODE,
3106 GL_TRANSFER_STATUS_CODE,
3107 GL_TRANSFER_DATE,
3108 ACCOUNTING_ENTRY_STATUS_CODE,
3109 ACCOUNTING_ENTRY_TYPE_CODE,
3110 CREATION_DATE,
3111 created_by,
3112 last_update_date,
3113 last_updated_by,
3114 LAST_UPDATE_LOGIN,
3115 PROGRAM_UPDATE_DATE,
3116 PROGRAM_APPLICATION_ID,
3117 PROGRAM_ID,
3118 REQUEST_ID,
3119 ZERO_AMOUNT_FLAG,
3120 UPG_SOURCE_APPLICATION_ID)
3121 values (l_upg_batch_id,
3122 707,
3123 'DEFAULT',
3124 entity_id,
3125 event_id,
3126 event_type_code,
3127 header_id,
3128 ledger_id,
3129 l_je_category_name,
3130 gl_date,
3131 PERIOD_NAME,
3132 'A',
3133 GL_TRANSFER_STATUS_CODE,
3134 gl_transfer_date,
3135 'F',
3136 'STANDARD',
3137 creation_date,
3138 created_by,
3139 last_update_date,
3140 last_updated_by,
3141 LAST_UPDATE_LOGIN,
3142 PROGRAM_UPDATE_DATE,
3143 PROGRAM_APPLICATION_ID,
3144 PROGRAM_ID,
3145 REQUEST_ID,
3146 ZERO_AMOUNT_FLAG,
3147 706)
3148 when (1=1) then
3149 into xla_ae_lines (
3150 upg_batch_id,
3151 application_id,
3152 ae_header_id,
3153 ae_line_num,
3154 code_combination_id,
3155 gl_transfer_mode_code,
3156 ACCOUNTED_DR,
3157 ACCOUNTED_CR,
3158 CURRENCY_CODE,
3159 CURRENCY_CONVERSION_DATE,
3160 CURRENCY_CONVERSION_RATE,
3161 CURRENCY_CONVERSION_TYPE,
3162 ENTERED_DR,
3163 ENTERED_CR,
3167 CONTROL_BALANCE_FLAG,
3164 accounting_class_code,
3165 gl_sl_link_id,
3166 gl_sl_link_table,
3168 GAIN_OR_LOSS_FLAG,
3169 CREATION_DATE,
3170 created_by,
3171 last_update_date,
3172 last_updated_by,
3173 LAST_UPDATE_LOGIN,
3174 PROGRAM_UPDATE_DATE,
3175 PROGRAM_APPLICATION_ID,
3176 PROGRAM_ID,
3177 REQUEST_ID,
3178 accounting_date,
3179 ledger_id)
3180 values (l_upg_batch_id,
3181 707,
3182 header_id,
3183 line_id,
3184 ref_account,
3185 GL_Update_code,
3186 ACCOUNTED_DR,
3187 ACCOUNTED_CR,
3188 CURRENCY_CODE,
3189 CURRENCY_CONVERSION_DATE,
3190 CURRENCY_CONVERSION_RATE,
3191 CURRENCY_CONVERSION_TYPE,
3192 ENTERED_DR,
3193 ENTERED_CR,
3194 accounting_class_code,
3195 link_id,
3196 link_table,
3197 CONTROL_BALANCE_FLAG,
3198 'N',
3199 CREATION_DATE,
3200 created_by,
3201 last_update_date,
3202 last_updated_by,
3203 LAST_UPDATE_LOGIN,
3204 PROGRAM_UPDATE_DATE,
3205 PROGRAM_APPLICATION_ID,
3206 PROGRAM_ID,
3207 REQUEST_ID,
3208 gl_date,
3209 ledger_id)
3210 into xla_distribution_links (
3211 upg_batch_id,
3212 application_id,
3213 event_id,
3214 ae_header_id,
3215 ae_line_num,
3216 accounting_line_code,
3217 accounting_line_type_code,
3218 source_distribution_type,
3219 source_distribution_id_num_1,
3220 merge_duplicate_code,
3221 REF_AE_HEADER_ID,
3222 TEMP_LINE_NUM,
3223 event_class_code,
3224 event_type_code)
3225 values (l_upg_batch_id,
3226 707,
3227 event_id,
3228 header_id,
3229 line_id,
3230 accounting_class_code,
3231 line_type_code,
3232 src_dist_type,
3233 src_dist_id_num_1,
3234 merge_dup_code,
3235 header_id,
3236 line_id,
3237 event_class_code,
3238 event_type_code)
3239
3240 select /*+ leading(cxs) use_nl(wta wt HOI2 oap mp gcc) index(wta) index(wt) index(mp) index(HOI2) index(oap) index(mp) index(gcc)*/
3241 row_number() over(partition by wta.transaction_id,wta.resource_id,wta.basis_type
3242 order by wta.transaction_id) as line_id,
3243 wta.creation_date creation_date,
3244 wta.last_update_date last_update_date,
3245 wta.created_by created_by,
3246 wta.last_updated_by last_updated_by,
3247 wta.last_update_login LAST_UPDATE_LOGIN,
3248 wta.program_update_date PROGRAM_UPDATE_DATE,
3249 wta.program_application_id PROGRAM_APPLICATION_ID,
3250 wta.program_id PROGRAM_ID,
3251 wta.request_id REQUEST_ID,
3252 wta.transaction_id transaction_id,
3253 wta.resource_id resource_id,
3254 wta.basis_type basis_type,
3255 cxs.entity_id entity_id,
3256 cxs.event_id event_id,
3257 cxs.header_id header_id,
3258 wta.organization_id organization_id,
3259 wt.transaction_date transaction_date,
3260 trunc(INV_LE_TIMEZONE_PUB.Get_Le_Day_For_Server(wt.transaction_date, HOI2.ORG_INFORMATION2)) gl_date,
3261 ogb.gl_batch_date gl_transfer_date,
3262 sob.ledger_id ledger_id,
3263 /*DECODE(HOI2.ORG_INFORMATION_CONTEXT, 'Accounting Information', TO_NUMBER(HOI2.ORG_INFORMATION2), null)*/
3264 null legal_entity_id,
3265 event_type_code,
3266 PERIOD_NAME,
3267 DECODE(wta.GL_BATCH_ID, -1, decode(mp.GENERAL_LEDGER_UPDATE_CODE,3,'NT','N'),decode(sign(wta.gl_batch_id),1, 'Y', 'NT')) GL_TRANSFER_STATUS_CODE,
3268 nvl(wt.currency_code, nvl(wta.currency_code, sob.currency_code)) CURRENCY_CODE,
3269 wta.REFERENCE_ACCOUNT ref_account,
3270 decode(mp.GENERAL_LEDGER_UPDATE_CODE,1, 'D', 2, 'S', 'N') GL_Update_code,
3271 decode(wta.accounting_line_type,
3272 1,'INVENTORY_VALUATION',
3273 2,decode(cem.event_class_code,
3274 'PURCHASE_ORDER' ,'CLEARING',
3275 'FOB_RCPT_SENDER_RCPT' ,'COST_OF_GOODS_SOLD',
3276 'FOB_SHIP_SENDER_SHIP' ,'COST_OF_GOODS_SOLD',
3277 'SALES_ORDER' ,'COST_OF_GOODS_SOLD',
3278 'MTL_COST_UPD' ,'COST_UPDATE_ADJUSTMENT',
3279 'WIP_COST_UPD' ,'COST_UPDATE_ADJUSTMENT',
3280 'LOG_INTERCOMPANY' ,'INTERCOMPANY_COGS',
3281 'FOB_RCPT_RECIPIENT_RCPT','INTERCOMPANY_ACCRUAL',
3282 'FOB_SHIP_RECIPIENT_SHIP','INTERCOMPANY_ACCRUAL',
3283 'OFFSET'),
3284 3,decode(cem.event_class_code,
3285 'ABSORPTION' ,'OVERHEAD_ABSORPTION',
3286 'OSP' ,'OVERHEAD_ABSORPTION',
3287 'MATERIAL_OVERHEAD_ABSORPTION'),
3288 4,'RESOURCE_ABSORPTION',
3289 5,'RECEIVING_INSPECTION',
3290 6,decode(cem.event_class_code,
3291 'ABSORPTION','RESOURCE_RATE_VARIANCE',
3292 'PURCHASE_PRICE_VARIANCE'),
3293 7,'WIP_VALUATION',
3294 8,'WIP_VARIANCE',
3295 9,'INTERORG_PAYABLES',
3296 10,'INTERORG_RECEIVABLES',
3300 14,'INTRANSIT_VALUATION',
3297 11,'INTERORG_TRANSFER_CREDIT',
3298 12,'INTERORG_FREIGHT_CHARGE',
3299 13,'COST_VARIANCE',
3301 15,'ENCUMBRANCE_REVERSAL',
3302 16,decode(cem.event_class_code,
3303 'LOG_INTERCOMPANY','INTERCOMPANY_ACCRUAL',
3304 'ACCRUAL'),
3305 17,'INVOICE_PRICE_VARIANCE',
3306 18,'EXCHANGE_RATE_VARIANCE',
3307 19,'SPECIAL_CHARGE_EXPENSE',
3308 20,'EXPENSE',
3309 21,'WIP_VALUATION',
3310 22,'WIP_VALUATION',
3311 23,'WIP_VALUATION',
3312 24,'WIP_VALUATION',
3313 25,'WIP_VALUATION',
3314 26,'WIP_VALUATION',
3315 27,'WIP_VALUATION',
3316 28,'WIP_VALUATION',
3317 29,'ESTIMATED_SCRAP_ABSORPTION',
3318 30,'PROFIT_IN_INVENTORY',
3319 31,'CLEARING',
3320 32,'RETROACTIVE_PRICE_ADJUSTMENT',
3321 33,'SHIKYU_VARIANCE',
3322 34,'INTERORG_PROFIT',
3323 35,'COST_OF_GOODS_SOLD',
3324 36,'DEFERRED_COGS',
3325 37,'COST_UPDATE_ADJUSTMENT',
3326 'UNKNOWN') accounting_class_code,
3327 wta.gl_sl_link_id link_id,
3328 'WTA' link_table,
3329 'C' line_type_code,
3330 'WIP_TRANSACTION_ACCOUNTS' src_dist_type,
3331 wta.wip_sub_ledger_id src_dist_id_num_1,
3332 'N' merge_dup_code,
3333 /* Bug 11842928: Modified the Entered value calculation
3334 DECODE(sign(wta.base_transaction_value), 1, wta.base_transaction_value, 0, 0, NULL) ACCOUNTED_DR,
3335 DECODE(sign(wta.base_transaction_value), -1,(-1*wta.base_transaction_value), NULL) ACCOUNTED_CR,*/
3336 decode(sign(wta.base_transaction_value),
3337 0, decode(sign(nvl(wta.transaction_value, wta.primary_quantity)),
3338 0, 0,
3339 1, 0,
3340 null),
3341 1, wta.base_transaction_value,
3342 null) accounted_dr,
3343 decode(sign(wta.base_transaction_value),
3344 0, decode(sign(nvl(wta.transaction_value, wta.primary_quantity)),
3345 -1, 0,
3346 null),
3347 -1, (-1 * wta.base_transaction_value),
3348 null) accounted_cr,
3349 wta.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE,
3350 wta.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE,
3351 wta.CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE,
3352 /* Bug 11842928: Modified the Entered value calculation
3353 DECODE(sign(nvl(wta.transaction_value, wta.base_transaction_value)), 1, nvl(wta.transaction_value, wta.base_transaction_value), 0, 0, NULL) ENTERED_DR,
3354 DECODE(sign(nvl(wta.transaction_value, wta.base_transaction_value)), -1,(-1*nvl(wta.transaction_value, wta.base_transaction_value)), NULL) ENTERED_CR,*/
3355 decode(sign(wta.base_transaction_value),
3356 0, decode(sign(nvl(wta.transaction_value, wta.primary_quantity)),
3357 0, 0,
3358 1, nvl(wta.transaction_value, 0),
3359 null),
3360 1, nvl(wta.transaction_value, wta.base_transaction_value),
3361 null) entered_dr,
3362 decode(sign(wta.base_transaction_value),
3363 0, decode(sign(nvl(wta.transaction_value, wta.primary_quantity)),
3364 -1, -1 * nvl(wta.transaction_value, 0),
3365 null),
3366 -1, (-1 * nvl(wta.transaction_value, wta.base_transaction_value)),
3367 null) entered_cr,
3368 NVL(wta.TRANSACTION_VALUE,wta.BASE_TRANSACTION_VALUE) entered_amount,
3369 decode(sign(wta.base_transaction_value),0,'Y', 'N') ZERO_AMOUNT_FLAG,
3370 decode(gcc.reference3,'Y', 'P', null) CONTROL_BALANCE_FLAG,
3371 cem.event_class_code event_class_code
3372 from wip_transaction_accounts wta,
3373 cst_xla_wip_event_map cem,
3374 wip_transactions wt,
3375 GL_CODE_COMBINATIONS gcc,
3376 mtl_parameters mp,
3377 HR_ORGANIZATION_INFORMATION HOI2,
3378 gl_ledgers sob,
3379 ORG_ACCT_PERIODS oap,
3380 org_gl_batches ogb,
3381 cst_xla_seq_gt cxs
3382 where wta.transaction_id=cxs.source_id_int_1
3383 and wt.transaction_id=cxs.source_id_int_1
3384 and nvl(wta.resource_id,-6661)=nvl(cxs.source_id_int_2,-6661)
3385 and nvl(wta.basis_type,-6661)=nvl(cxs.source_id_int_3,-6661)
3386 and mp.organization_id=HOI2.organization_id
3387 and wta.organization_id=mp.organization_id
3388 and ((wt.transaction_type=cem.transaction_type_id
3389 and wt.transaction_type not in (1,2,3))
3390 or (wt.transaction_type in (1,2,3)
3391 and DECODE( wta.COST_ELEMENT_ID,
3392 3, 'RESOURCE_ABSORPTION',
3393 4, DECODE (wt.source_code, 'IPV', 'IPV_TRANSFER_WO',
3394 DECODE (wt.autocharge_type, 3, 'OSP',
3395 4, 'OSP',
3396 'RESOURCE_ABSORPTION')),
3397 5, 'OVERHEAD_ABSORPTION') = cem.event_type_code))
3398 and gcc.CODE_COMBINATION_ID=wta.REFERENCE_ACCOUNT
3399 AND wt.acct_period_id = oap.acct_period_id
3400 and oap.ORGANIZATION_ID = wt.organization_id
3401 AND wt.organization_id = HOI2.organization_id
3402 and wta.organization_id = ogb.organization_id (+)
3403 and wta.gl_batch_id = ogb.gl_batch_id (+)
3404 and HOI2.ORG_INFORMATION_CONTEXT ='Accounting Information'
3408 and sob.object_type_code = 'L';
3405 AND sob.ledger_ID = TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI2.ORG_INFORMATION1,'0123456789',' ')),
3406 NULL, HOI2.ORG_INFORMATION1,
3407 -99999))
3409
3410 <<out_arg_log>>
3411
3412 IF l_Log THEN
3413 log_message(
3414 fnd_log.level_procedure,
3415 l_module||'.end',
3416 'Exiting CST_SLA_UPDATE_PKG.Update_WIP_Subledger with '||
3417 'X_errbuf = '||X_errbuf||','||
3418 'X_retcode = '||X_retcode
3419 );
3420 END IF;
3421
3422 EXCEPTION
3423
3424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3425 ROLLBACK;
3426 X_retcode := FND_API.g_ret_sts_unexp_error;
3427 handle_error ( X_module => l_module||'.'||l_stmt_num,
3428 X_message => 'ERROR: '||X_errbuf,
3429 X_reqerror => FALSE,
3430 X_errbuf => X_errbuf );
3431
3432 WHEN OTHERS THEN
3433 ROLLBACK;
3434 X_retcode := FND_API.g_ret_sts_unexp_error;
3435 handle_error ( X_module => l_module||'.'||l_stmt_num,
3436 X_message => 'EXCEPTION: '||SQLERRM,
3437 X_reqerror => FALSE,
3438 X_errbuf => X_errbuf );
3439
3440 end Update_WIP_Subledger;
3441
3442 -------------------------------------------------------------------------------------
3443 -- API name : Update_RCV_Subledger
3444 -- Type : Private
3445 -- Function : To update Receiving Sub Ledger to SLA data model from minimum
3446 -- transaction ID to maximum transaction ID.
3447 -- Pre-reqs :
3448 -- Parameters :
3449 -- IN : X_min_id in number,
3450 -- X_max_id in number
3451 --
3452 -- OUT : X_errbuf out NOCOPY varchar2,
3453 -- X_retcode out NOCOPY varchar2
3454 --
3455 -- Notes : The API is called from Update_Proc_RCV_WKR.
3456 --
3457 -- End of comments
3458 -------------------------------------------------------------------------------------
3459
3460 PROCEDURE Update_Receiving_Subledger (
3461 X_errbuf out NOCOPY varchar2,
3462 X_retcode out NOCOPY varchar2,
3463 X_min_id in number,
3464 X_max_id in number)
3465 IS
3466 l_upg_batch_id number(15):=0;
3467 l_je_category_name varchar2(30);
3468
3469 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_SLA_UPDATE_PKG.Update_Receiving_Subledger';
3470 l_Log CONSTANT BOOLEAN := fnd_log.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND gRequestId > 0;
3471
3472 l_stmt_num number;
3473 l_multi_org_flag varchar2(1);
3474 l_rows_processed number;
3475 CST_RCV_MRC_UPG_EXCEPTION exception;
3476
3477 BEGIN
3478
3479 l_stmt_num :=0;
3480
3481 IF l_Log THEN
3482 log_message(
3483 fnd_log.level_procedure,
3484 l_module||'.'||l_stmt_num,
3485 'Entering CST_SLA_UPDATE_PKG.Update_Receiving_Subledger with '||
3486 'X_min_id = '||X_min_id||','||
3487 'X_max_id = '||X_max_id
3488 );
3489 END IF;
3490
3491 select XLA_UPG_BATCHES_S.nextval into l_upg_batch_id from dual;
3492
3493 IF l_Log THEN
3494 log_message(
3495 fnd_log.level_procedure,
3496 l_module,
3497 'Upgrade Batch ID = '||l_upg_batch_id
3498 );
3499 END IF;
3500
3501 l_je_category_name :='Receiving';
3502
3503 x_retcode := FND_API.G_RET_STS_SUCCESS;
3504
3505 SELECT MULTI_ORG_FLAG
3506 INTO l_multi_org_flag
3507 FROM FND_PRODUCT_GROUPS;
3508
3509 /* For one rt/rae combo, we are going to have one SLA.entity_id, one SLA.event_id, one SLA.header_id */
3510 /* one rrs line -> one xla_ae_line -> one xla_distribution_link */
3511
3512 /* For 11.5.9 or before, we only had one org_id, one organization_id, and one ledger_id
3513 for the accounting lines corresponding to one RT.txn_id;
3514 But since 11.5.10 introducing global procurement, we have multiple org_id, multiple organization_id,
3515 and multiple ledger_id in RAE corresponding to one RT.txn_id. I.e, Customer(RT.txn_id)--OU3---OU2---OU1--S
3516 */
3517
3518 l_stmt_num :=10;
3519
3520 insert into cst_xla_seq_gt(
3521 source_id_int_1,
3522 source_id_int_2,
3523 source_id_int_3,
3524 source_id_int_4,
3525 source_id_int_5,
3526 entity_id,
3527 event_id,
3528 header_id,
3529 legal_entity,
3530 org_id)
3531 select /*+ leading(rrs) */
3532 rt.transaction_id,
3533 rae.accounting_event_id,
3534 HOI2.organization_id,
3535 nvl(rae.EVENT_TYPE_ID,decode(rt.transaction_type, 'RECEIVE', 1, 'DELIVER', 2, 'CORRECT', 3,
3536 'MATCH', 4, 'RETURN TO RECEIVING', 5, 'RETURN TO VENDOR', 6, -1)),
3537 rt.parent_transaction_id,
3538 XLA_transaction_ENTITies_S.nextval,
3539 xla_events_s.nextval,
3540 xla_ae_headers_s.NEXTVAL,
3541 null,
3542 nvl(rae.org_id,
3543 DECODE(l_multi_org_flag,'Y',TO_NUMBER(HOI2.ORG_INFORMATION3),TO_NUMBER(NULL)))
3544 from rcv_transactions rt,
3545 rcv_accounting_events rae,
3546 hr_organization_information hoi2,
3547 gl_ledgers sob,
3548 (select /*+ no_merge leading(rrs) use_hash(xud) swap_join_inputs(xud) */
3549 distinct rcv_transaction_id,
3550 accounting_event_id
3551 from xla_upgrade_dates xud,
3555 and rrs.accounting_date between xud.start_date and xud.end_date
3552 rcv_receiving_sub_ledger rrs
3553 where rrs.rcv_transaction_id between x_min_id and x_max_id
3554 and xud.ledger_id = rrs.set_of_books_id
3556 and rrs.encumbrance_type_id is null
3557 and rrs.reference10 is null
3558 and rrs.je_batch_name is not null) rrs
3559 where rrs.rcv_transaction_id = rae.rcv_transaction_id (+) /*Bug 12538372*/
3560 and rt.transaction_id = rrs.rcv_transaction_id
3561 and rrs.accounting_event_id = rae.accounting_event_id (+)
3562 and hoi2.organization_id = nvl (rae.organization_id, rt.organization_id)
3563 and hoi2.org_information_context = 'Accounting Information'
3564 and sob.ledger_ID = TO_NUMBER(DECODE(RTRIM(TRANSLATE(HOI2.ORG_INFORMATION1,'0123456789',' ')),
3565 NULL, HOI2.ORG_INFORMATION1,
3566 -99999))
3567 and sob.object_type_code = 'L';
3568
3569 l_rows_processed := SQL%ROWCOUNT;
3570 IF (l_rows_processed = 0) THEN
3571 IF l_Log THEN
3572 log_message(
3573 fnd_log.level_procedure,
3574 l_module||'.'||l_stmt_num,
3575 'No rows to be upgraded. Exiting CST_SLA_UPDATE_PKG.Update_Receiving_Subledger with '||
3576 'X_errbuf = '||X_errbuf||','||
3577 'X_retcode = '||X_retcode
3578 );
3579 END IF;
3580 return;
3581 ELSE
3582 fnd_file.put_line(fnd_file.LOG, 'Update_Receiving_Subledger.. Processed rows : ' || l_rows_processed);
3583 END IF;
3584
3585 l_stmt_num :=20;
3586 /* Bug 6729184 Moved update of rcv_sub_ledger_id prior to XLA insert to avoid NULL into
3587 XDL.SOURCE_DISTRIBUTION_ID_NUM_1*/
3588
3589 update /*+ leading(cxs) use_nl(rrs) index(rrs) */
3590 rcv_receiving_sub_ledger rrs
3591 set reference10 = 'Migrated to SLA',
3592 rcv_sub_ledger_id = nvl(rcv_sub_ledger_id, rcv_receiving_sub_ledger_s.nextval),
3593 last_update_date = gUpdateDate,
3594 last_updated_by = gUserId,
3595 last_update_login = gLoginId
3596 where rrs.rcv_transaction_id in (select source_id_int_1 from cst_xla_seq_gt cxs)
3597 and rrs.ENCUMBRANCE_TYPE_ID is null;
3598
3599
3600 l_stmt_num :=30;
3601
3602 insert all
3603 when (line_id=1) then
3604 INTO XLA_TRANSACTION_ENTITIES (
3605 upg_batch_id,
3606 entity_id,
3607 application_id,
3608 ledger_id,
3609 legal_entity_id,
3610 entity_code,
3611 source_id_int_1,
3612 SOURCE_ID_INT_2,
3613 SOURCE_ID_INT_3,
3614 security_id_int_1,
3615 SECURITY_ID_INT_2,
3616 TRANSACTION_NUMBER,
3617 creation_date,
3618 created_by,
3619 last_update_date,
3620 last_updated_by,
3621 LAST_UPDATE_LOGIN,
3622 source_application_id,
3623 UPG_SOURCE_APPLICATION_ID)
3624 values (l_upg_batch_id,
3625 entity_id,
3626 707,
3627 ledger_id,
3628 legal_entity_id,
3629 'RCV_ACCOUNTING_EVENTS',
3630 transaction_id,
3631 accounting_event_id,
3632 organization_id,
3633 organization_id,
3634 org_id,
3635 transaction_number,
3636 creation_date,
3637 CREATED_BY,
3638 last_update_date,
3639 last_updated_by,
3640 LAST_UPDATE_LOGIN,
3641 201,
3642 201)
3643 into xla_events (
3644 upg_batch_id,
3645 APPLICATION_ID,
3646 entity_id,
3647 event_id,
3648 event_number,
3649 event_type_code,
3650 event_date,
3651 event_status_code,
3652 PROCESS_STATUS_CODE,
3653 ON_HOLD_FLAG,
3654 CREATION_DATE,
3655 created_by,
3656 last_update_date,
3657 last_updated_by,
3658 LAST_UPDATE_LOGIN,
3659 PROGRAM_UPDATE_DATE,
3660 PROGRAM_APPLICATION_ID,
3661 PROGRAM_ID,
3662 REQUEST_ID,
3663 TRANSACTION_DATE,
3664 UPG_SOURCE_APPLICATION_ID)
3665 values (l_upg_batch_id,
3666 707,
3667 entity_id,
3668 event_id,
3669 1,
3670 event_type_code,
3671 transaction_date,
3672 'P',
3673 'P',
3674 'N',
3675 creation_date,
3676 created_by,
3677 last_update_date,
3678 last_updated_by,
3679 LAST_UPDATE_LOGIN,
3680 PROGRAM_UPDATE_DATE,
3681 PROGRAM_APPLICATION_ID,
3682 PROGRAM_ID,
3683 REQUEST_ID,
3684 TRANSACTION_DATE,
3685 201)
3686 into xla_ae_headers (
3687 upg_batch_id,
3688 application_id,
3689 AMB_CONTEXT_CODE,
3690 entity_id,
3691 event_id,
3692 event_type_code,
3693 ae_header_id,
3694 ledger_id,
3695 je_category_name,
3696 ACCOUNTING_DATE,
3697 PERIOD_NAME,
3698 BALANCE_TYPE_CODE,
3699 BUDGET_VERSION_ID,
3700 DOC_SEQUENCE_ID,
3701 DOC_SEQUENCE_VALUE,
3702 GL_TRANSFER_STATUS_CODE,
3703 GL_TRANSFER_DATE,
3704 ACCOUNTING_ENTRY_STATUS_CODE,
3705 ACCOUNTING_ENTRY_TYPE_CODE,
3706 CREATION_DATE,
3707 created_by,
3708 last_update_date,
3712 PROGRAM_APPLICATION_ID,
3709 last_updated_by,
3710 LAST_UPDATE_LOGIN,
3711 PROGRAM_UPDATE_DATE,
3713 PROGRAM_ID,
3714 REQUEST_ID,
3715 UPG_SOURCE_APPLICATION_ID,
3716 description)
3717 values (l_upg_batch_id,
3718 707,
3719 'DEFAULT',
3720 entity_id,
3721 event_id,
3722 event_type_code,
3723 header_id,
3724 ledger_id,
3725 l_je_category_name,
3726 accounting_date,
3727 PERIOD_NAME,
3728 actual_flag,
3729 BUDGET_VERSION_ID,
3730 DOC_SEQUENCE_ID,
3731 DOC_SEQUENCE_VALUE,
3732 GL_TRANSFER_STATUS_CODE,
3733 DATE_CREATED_IN_GL,
3734 'F',
3735 'STANDARD',
3736 creation_date,
3737 created_by,
3738 last_update_date,
3739 last_updated_by,
3740 LAST_UPDATE_LOGIN,
3741 PROGRAM_UPDATE_DATE,
3742 PROGRAM_APPLICATION_ID,
3743 PROGRAM_ID,
3744 REQUEST_ID,
3745 201,
3746 je_header_name)
3747 when (1=1) then
3748 into xla_ae_lines (
3749 upg_batch_id,
3750 application_id,
3751 ae_header_id,
3752 ae_line_num,
3753 code_combination_id,
3754 gl_transfer_mode_code,
3755 ACCOUNTED_DR,
3756 ACCOUNTED_CR,
3757 CURRENCY_CODE,
3758 CURRENCY_CONVERSION_DATE,
3759 CURRENCY_CONVERSION_RATE,
3760 CURRENCY_CONVERSION_TYPE,
3761 ENTERED_DR,
3762 ENTERED_CR,
3763 accounting_class_code,
3764 gl_sl_link_id,
3765 gl_sl_link_table,
3766 USSGL_TRANSACTION_CODE,
3767 CONTROL_BALANCE_FLAG,
3768 GAIN_OR_LOSS_FLAG,
3769 CREATION_DATE,
3770 created_by,
3771 last_update_date,
3772 last_updated_by,
3773 LAST_UPDATE_LOGIN,
3774 PROGRAM_UPDATE_DATE,
3775 PROGRAM_APPLICATION_ID,
3776 PROGRAM_ID,
3777 REQUEST_ID,
3778 description,
3779 accounting_date,
3780 ledger_id)
3781 values (l_upg_batch_id,
3782 707,
3783 header_id,
3784 line_id,
3785 ccid,
3786 GL_Update_code,
3787 ACCOUNTED_DR,
3788 ACCOUNTED_CR,
3789 CURRENCY_CODE,
3790 CURRENCY_CONVERSION_DATE,
3791 CURRENCY_CONVERSION_RATE,
3792 CURRENCY_CONVERSION_TYPE,
3793 ENTERED_DR,
3794 ENTERED_CR,
3795 accounting_class_code,
3796 link_id,
3797 link_table,
3798 USSGL_TRANSACTION_CODE,
3799 CONTROL_BALANCE_FLAG,
3800 'N',
3801 CREATION_DATE,
3802 created_by,
3803 last_update_date,
3804 last_updated_by,
3805 LAST_UPDATE_LOGIN,
3806 PROGRAM_UPDATE_DATE,
3807 PROGRAM_APPLICATION_ID,
3808 PROGRAM_ID,
3809 REQUEST_ID,
3810 je_line_description,
3811 accounting_date,
3812 ledger_id)
3813 into xla_distribution_links (
3814 upg_batch_id,
3815 application_id,
3816 event_id,
3817 ae_header_id,
3818 ae_line_num,
3819 accounting_line_code,
3820 accounting_line_type_code,
3821 source_distribution_type,
3822 source_distribution_id_num_1,
3823 merge_duplicate_code,
3824 REF_AE_HEADER_ID,
3825 TEMP_LINE_NUM,
3826 event_class_code,
3827 event_type_code)
3828 values (l_upg_batch_id,
3829 707,
3830 event_id,
3831 header_id,
3832 line_id,
3833 accounting_class_code,
3834 line_type_code,
3835 src_dist_type,
3836 rcv_sub_ledger_id,
3837 merge_dup_code,
3838 header_id,
3839 line_id,
3840 event_class_code,
3841 event_type_code)
3842
3843 select /*+ leading(cxs) use_nl(rrs) index(rrs) index(gcc)*/
3844 row_number() over(partition by cxs.source_id_int_1, cxs.source_id_int_2 ,cxs.source_id_int_3
3845 order by cxs.source_id_int_1) as line_id,
3846 rrs.creation_date creation_date,
3847 rrs.last_update_date last_update_date,
3848 rrs.created_by created_by,
3849 rrs.last_updated_by last_updated_by,
3850 rrs.last_update_login LAST_UPDATE_LOGIN,
3851 rrs.program_update_date PROGRAM_UPDATE_DATE,
3852 rrs.program_application_id PROGRAM_APPLICATION_ID,
3853 rrs.program_id PROGRAM_ID,
3854 rrs.request_id REQUEST_ID,
3855 cxs.entity_id entity_id,
3856 cxs.event_id event_id,
3857 cxs.header_id header_id,
3858 rrs.rcv_transaction_id transaction_id,
3859 cxs.source_id_int_3 organization_id,
3860 cxs.source_id_int_2 accounting_event_id,
3861 cxs.org_id org_id,
3862 cxs.source_id_int_5 parent_transaction_id,
3863 cxs.source_id_int_1 transaction_number,
3864 rrs.transaction_date transaction_date,
3865 rrs.accounting_date accounting_date,
3866 rrs.set_of_books_id ledger_id,
3867 cxs.legal_entity legal_entity_id,
3868 event_type_code,
3869 rrs.PERIOD_NAME,
3870 rrs.actual_flag,
3871 'Y' GL_TRANSFER_STATUS_CODE,
3875 rrs.SUBLEDGER_DOC_SEQUENCE_VALUE DOC_SEQUENCE_VALUE,
3872 rrs.CURRENCY_CODE CURRENCY_CODE,
3873 rrs.budget_version_id BUDGET_VERSION_ID,
3874 rrs.SUBLEDGER_DOC_SEQUENCE_ID DOC_SEQUENCE_ID,
3876 nvl(rrs.DATE_CREATED_IN_GL,rrs.accounting_date) DATE_CREATED_IN_GL,
3877 rrs.je_header_name je_header_name,
3878 rrs.je_line_description je_line_description,
3879 rrs.CODE_COMBINATION_ID ccid,
3880 rrs.rcv_sub_ledger_id rcv_sub_ledger_id,
3881 'D' GL_Update_code,
3882 decode(nvl(rrs.accounting_line_type,'888'),
3883 'Accrual','ACCRUAL',
3884 'Charge','CHARGE',
3885 'Clearing','CLEARING',
3886 'IC Accrual','INTERCOMPANY_ACCRUAL',
3887 'IC Cost of Sales','INTERCOMPANY_COGS',
3888 'Receiving Inspection','RECEIVING_INSPECTION',
3889 'Retroprice Adjustment','RETROACTIVE_PRICE_ADJUSTMENT',
3890 '888',decode(cem.transaction_type_id,
3891 1, decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
3892 2, decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','CHARGE'),
3893 3, decode(cem.attribute,
3894 'RECEIVE', decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
3895 'MATCH', decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
3896 'DELIVER', decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','CHARGE'),
3897 'RETURN TO VENDOR',decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','ACCRUAL'),
3898 'RETURN TO RECEIVING', decode(sign(rrs.accounted_cr),1,'CHARGE','RECEIVING_INSPECTION'),
3899 ''),
3900 4, decode(sign(rrs.accounted_cr),1,'ACCRUAL','RECEIVING_INSPECTION'),
3901 5, decode(sign(rrs.accounted_cr),1,'CHARGE','RECEIVING_INSPECTION'),
3902 6, decode(sign(rrs.accounted_cr),1,'RECEIVING_INSPECTION','ACCRUAL'),
3903 ''),
3904 'UNKNOWN') accounting_class_code,
3905 rrs.gl_sl_link_id link_id,
3906 'RSL' link_table,
3907 'C' line_type_code,
3908 'RCV_RECEIVING_SUB_LEDGER' src_dist_type,
3909 'N' merge_dup_code,
3910 rrs.accounted_dr ACCOUNTED_DR,
3911 rrs.accounted_cr ACCOUNTED_CR,
3912 rrs.CURRENCY_CONVERSION_DATE CURRENCY_CONVERSION_DATE,
3913 rrs.CURRENCY_CONVERSION_RATE CURRENCY_CONVERSION_RATE,
3914 rrs.USER_CURRENCY_CONVERSION_TYPE CURRENCY_CONVERSION_TYPE,
3915 rrs.entered_dr ENTERED_DR,
3916 rrs.entered_cr ENTERED_CR,
3917 rrs.USSGL_TRANSACTION_CODE USSGL_TRANSACTION_CODE,
3918 decode(gcc.reference3,'Y', 'P', null) CONTROL_BALANCE_FLAG,
3919 cem.event_class_code event_class_code
3920 from rcv_receiving_sub_ledger rrs,
3921 cst_xla_rcv_event_map cem,
3922 GL_CODE_COMBINATIONS gcc,
3923 cst_xla_seq_gt cxs
3924 where rrs.rcv_transaction_id = cxs.source_id_int_1
3925 and nvl(rrs.accounting_event_id,-1) = nvl(cxs.source_id_int_2,-1) /*Bug 12538372*/
3926 and rrs.ENCUMBRANCE_TYPE_ID is null
3927 and cxs.source_id_int_4 = cem.transaction_type_id
3928 and (cem.transaction_type_id <> 3
3929 or
3930 (cem.transaction_type_id = 3
3931 and cxs.source_id_int_5 is not null
3932 and cem.attribute = (SELECT TRANSACTION_TYPE
3933 FROM RCV_TRANSACTIONS rt1
3934 WHERE rt1.transaction_id = cxs.source_id_int_5))
3935 )
3936 and gcc.CODE_COMBINATION_ID=rrs.CODE_COMBINATION_ID;
3937
3938 l_stmt_num :=40;
3939
3940 if (g_mrc_enabled) then
3941 RCV_SLA_MRC_UPDATE_PKG.Update_Receiving_MRC_Subledger(X_errbuf => X_errbuf,
3942 X_retcode => X_retcode,
3943 X_upg_batch_id => l_upg_batch_id,
3944 X_je_category_name => l_je_category_name);
3945 if (X_retcode <> FND_API.G_RET_STS_SUCCESS) then
3946 raise CST_RCV_MRC_UPG_EXCEPTION;
3947 end if;
3948 end if;
3949
3950 <<out_arg_log>>
3951
3952 IF l_Log THEN
3953 log_message(
3954 fnd_log.level_procedure,
3955 l_module||'.end',
3956 'Exiting CST_SLA_UPDATE_PKG.Update_Receiving_Subledger with '||
3957 'X_errbuf = '||X_errbuf||','||
3958 'X_retcode = '||X_retcode
3959 );
3960 END IF;
3961
3962 EXCEPTION
3963
3964 WHEN CST_RCV_MRC_UPG_EXCEPTION THEN
3965 ROLLBACK;
3966 X_retcode := FND_API.g_ret_sts_unexp_error;
3967 handle_error ( X_module => l_module||'.'||l_stmt_num,
3968 X_message => 'ERROR: An exception has occurred in upgrade of receiving MRC table.',
3969 X_reqerror => FALSE,
3970 X_errbuf => X_errbuf );
3971
3972 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3973 ROLLBACK;
3974 X_retcode := FND_API.g_ret_sts_unexp_error;
3975 handle_error ( X_module => l_module||'.'||l_stmt_num,
3976 X_message => 'ERROR: '||X_errbuf,
3977 X_reqerror => FALSE,
3978 X_errbuf => X_errbuf );
3979
3980 WHEN OTHERS THEN
3981 ROLLBACK;
3982 X_retcode := FND_API.g_ret_sts_unexp_error;
3983 handle_error ( X_module => l_module||'.'||l_stmt_num,
3984 X_message => 'EXCEPTION: '||SQLERRM,
3985 X_reqerror => FALSE,
3986 X_errbuf => X_errbuf );
3987
3988 end Update_Receiving_Subledger;
3989
3990 END CST_SLA_UPDATE_PKG;