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