[Home] [Help]
PACKAGE BODY: APPS.CSTPPBBS
Source
1 PACKAGE BODY CSTPPBBS AS
2 /* $Header: CSTPBBSB.pls 120.8 2007/05/24 12:37:04 vmutyala ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPPBBS';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 /*---------------------------------------------------------------------------*
8 | PUBLIC PROCEDURE |
9 | copy_prior_info |
10 *----------------------------------------------------------------------------*/
11 PROCEDURE copy_prior_info(
12 i_pac_period_id IN NUMBER,
13 i_prior_pac_period_id IN NUMBER,
14 i_legal_entity IN NUMBER,
15 i_cost_type_id IN NUMBER,
16 i_cost_group_id IN NUMBER,
17 i_cost_method IN NUMBER,
18 i_user_id IN NUMBER,
19 i_login_id IN NUMBER,
20 i_request_id IN NUMBER,
21 i_prog_id IN NUMBER DEFAULT -1,
22 i_prog_app_id IN NUMBER DEFAULT -1,
23 o_err_num OUT NOCOPY NUMBER,
24 o_err_code OUT NOCOPY VARCHAR2,
25 o_err_msg OUT NOCOPY VARCHAR2)
26 IS
27
28 l_err_num NUMBER;
29 l_err_code VARCHAR2(240);
30 l_err_msg VARCHAR2(240);
31 PROCESS_ERROR EXCEPTION;
32 l_stmt_num NUMBER;
33
34 l_api_name CONSTANT VARCHAR2(30) := 'copy_prior_info';
35 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
36 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
37
38 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
39 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
40 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
41 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
42 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
43 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
44
45 BEGIN
46 IF (l_pLog) THEN
47 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
48 l_module || '.begin',
49 l_api_name || ' <<< ' || 'Parameters :' ||
50 ' i_pac_period_id: ' || i_pac_period_id ||
51 ' i_prior_pac_period_id: ' || i_prior_pac_period_id ||
52 ' i_legal_entity: ' || i_legal_entity ||
53 ' i_cost_type_id: ' || i_cost_type_id ||
54 ' i_cost_group_id: ' || i_cost_group_id ||
55 ' i_cost_method: ' || i_cost_method);
56 END IF;
57
58 l_err_num := 0;
59 l_err_code := '';
60 l_err_msg := '';
61
62 IF (i_cost_method = 3) THEN
63 l_stmt_num := 10;
64 copy_prior_info_PWAC (i_pac_period_id, i_prior_pac_period_id,
65 i_legal_entity, i_cost_type_id, i_cost_group_id,
66 i_user_id,
67 i_login_id, i_request_id, i_prog_id, i_prog_app_id,
68 l_err_num, l_err_code, l_err_msg);
69 IF (l_err_num <> 0) THEN
70 raise PROCESS_ERROR;
71 END IF;
72 ELSE
73 l_stmt_num := 20;
74 CSTPFCHK.copy_prior_info_hook (i_pac_period_id, i_prior_pac_period_id,
75 i_legal_entity, i_cost_type_id, i_cost_group_id,
76 i_cost_method, i_user_id,
77 i_login_id, i_request_id, i_prog_app_id, i_prog_id,
78 l_err_num, l_err_code, l_err_msg);
79 IF (l_err_num <> 0) THEN
80 raise PROCESS_ERROR;
81 END IF;
82 END IF;
83
84 IF (l_pLog) THEN
85 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
86 l_module || '.end',
87 l_api_name || ' >>>');
88 END IF;
89
90 EXCEPTION
91
92 WHEN PROCESS_ERROR THEN
93 IF (l_exceptionLog) THEN
94 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
95 l_module || '.' || l_stmt_num,
96 l_err_msg);
97 END IF;
98 o_err_num := l_err_num;
99 o_err_code := l_err_code;
100 o_err_msg := l_err_msg;
101
102 END copy_prior_info;
103
104
105
106
107 /*---------------------------------------------------------------------------*
108 | PRIVATE PROCEDURES/FUNCTIONS |
109 *----------------------------------------------------------------------------*/
110 PROCEDURE copy_prior_info_PWAC(
111 i_pac_period_id IN NUMBER,
112 i_prior_pac_period_id IN NUMBER,
113 i_legal_entity IN NUMBER,
114 i_cost_type_id IN NUMBER,
115 i_cost_group_id IN NUMBER,
116 i_user_id IN NUMBER,
117 i_login_id IN NUMBER,
118 i_request_id IN NUMBER,
119 i_prog_id IN NUMBER DEFAULT -1,
120 i_prog_app_id IN NUMBER DEFAULT -1,
121 o_err_num OUT NOCOPY NUMBER,
122 o_err_code OUT NOCOPY VARCHAR2,
123 o_err_msg OUT NOCOPY VARCHAR2)
124 IS
125
126 l_err_num NUMBER;
127 l_err_code VARCHAR2(240);
128 l_err_msg VARCHAR2(240);
129 l_stmt_num NUMBER;
130 l_count NUMBER;
131 l_use_hook NUMBER;
132 l_cost_layer_id NUMBER;
133 l_quantity_layer_id NUMBER;
134 l_cost_method_type NUMBER;
135 l_current_start_date DATE;
136 CURRENT_DATA_EXISTS EXCEPTION;
137 PROCESS_ERROR EXCEPTION;
138
139
140 l_api_name CONSTANT VARCHAR2(30) := 'copy_prior_info_PWAC';
141 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
142 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
143
144 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
145 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
146 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
147 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
148 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
149 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
150
151 BEGIN
152
153 IF (l_pLog) THEN
154 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
155 l_module || '.begin',
156 l_api_name || ' <<< ' || 'Parameters :' ||
157 ' i_pac_period_id: ' || i_pac_period_id ||
158 ' i_prior_pac_period_id: ' || i_prior_pac_period_id ||
159 ' i_legal_entity: ' || i_legal_entity ||
160 ' i_cost_type_id: ' || i_cost_type_id ||
161 ' i_cost_group_id: ' || i_cost_group_id);
162 END IF;
163 ----------------------------------------------------------------------
164 -- Initialize Variables
165 ----------------------------------------------------------------------
166
167 l_err_num := 0;
168 l_err_code := '';
169 l_err_msg := '';
170
171 --------------------------------------------------------------------
172 -- Copy from previous period, if this is not the first run period --
173 --------------------------------------------------------------------
174 IF (i_prior_pac_period_id <> -1) THEN
175
176 --------------------------------------------------------
177 -- Making sure that we have no data in current period --
178 --------------------------------------------------------
179 l_stmt_num := 10;
180 l_count := 0;
181 SELECT count(1)
182 INTO l_count
183 FROM cst_pac_item_costs
184 WHERE pac_period_id = i_pac_period_id
185 AND cost_group_id = i_cost_group_id
186 AND rownum = 1;
187
188 IF (l_count <> 0) THEN
189 raise CURRENT_DATA_EXISTS;
190 END IF;
191
192 l_stmt_num := 20;
193 l_count := 0;
194 SELECT count(1)
195 INTO l_count
196 FROM wip_pac_period_balances
197 WHERE pac_period_id = i_pac_period_id
198 AND cost_group_id = i_cost_group_id
199 AND rownum = 1;
200
201 IF (l_count <> 0) THEN
202 raise CURRENT_DATA_EXISTS;
203 END IF;
204
205 l_stmt_num := 22;
206 l_count := 0;
207 SELECT count(1)
208 INTO l_count
209 FROM cst_pac_req_oper_cost_details
210 WHERE pac_period_id = i_pac_period_id
211 AND cost_group_id = i_cost_group_id
212 AND rownum = 1;
213
214 IF (l_count <> 0) THEN
215 raise CURRENT_DATA_EXISTS;
216 END IF;
217
218 l_stmt_num := 25;
219 l_count := 0;
220 SELECT count(1)
221 INTO l_count
222 FROM cst_pac_period_balances
223 WHERE pac_period_id = i_pac_period_id
224 AND cost_group_id = i_cost_group_id
225 AND rownum = 1;
226
227 IF (l_count <> 0) THEN
228 raise CURRENT_DATA_EXISTS;
229 END IF;
230
231 --------------------------------------------------------------------------------
232 -- Copy data from previous period to current period of the following tables : --
233 -- 1. cst_pac_item_costs --
234 -- 2. cst_pac_item_cost_details --
235 -- 3. cst_pac_quantity_layers --
236 -- New cost_layer_id and quantity_layer_id are generated for every rows --
237 -- inserted. --
238 --------------------------------------------------------------------------------
239 -------------------------------------------
240 -- Copy prior info of CST_PAC_ITEM_COSTS --
241 -------------------------------------------
242 l_stmt_num := 30;
243 INSERT INTO cst_pac_item_costs (
244 cost_layer_id,
245 pac_period_id,
246 cost_group_id,
247 inventory_item_id,
248 total_layer_quantity,
249 buy_quantity,
250 make_quantity,
251 issue_quantity,
252 item_cost,
253 begin_item_cost,
254 item_buy_cost,
255 item_make_cost,
256 material_cost,
257 material_overhead_cost,
258 resource_cost,
259 overhead_cost,
260 outside_processing_cost,
261 pl_material,
262 pl_material_overhead,
263 pl_resource,
264 pl_outside_processing,
265 pl_overhead,
266 tl_material,
267 tl_material_overhead,
268 tl_resource,
269 tl_outside_processing,
270 tl_overhead,
271 pl_item_cost,
272 tl_item_cost,
273 unburdened_cost,
274 burden_cost,
275 last_update_date,
276 last_updated_by,
277 creation_date,
278 created_by,
279 request_id,
280 program_application_id,
281 program_id,
282 program_update_date,
283 last_update_login)
284 SELECT
285 cst_pac_item_costs_s.nextval,
286 i_pac_period_id,
287 cost_group_id,
288 inventory_item_id,
289 total_layer_quantity,
290 0,
291 0,
292 0,
293 item_cost,
294 item_cost,
295 0,
296 0,
297 material_cost,
298 material_overhead_cost,
299 resource_cost,
300 overhead_cost,
301 outside_processing_cost,
302 pl_material,
303 pl_material_overhead,
304 pl_resource,
305 pl_outside_processing,
306 pl_overhead,
307 tl_material,
308 tl_material_overhead,
309 tl_resource,
310 tl_outside_processing,
311 tl_overhead,
312 pl_item_cost,
313 tl_item_cost,
314 unburdened_cost,
315 burden_cost,
316 SYSDATE,
317 i_user_id,
318 SYSDATE,
319 i_user_id,
320 i_request_id,
321 i_prog_app_id,
322 i_prog_id,
323 SYSDATE,
324 i_login_id
325 FROM cst_pac_item_costs cpic
326 WHERE cpic.pac_period_id = i_prior_pac_period_id
327 AND cpic.cost_group_id = i_cost_group_id;
328
329 --------------------------------------------------
330 -- Copy prior info of CST_PAC_ITEM_COST_DETAILS --
331 --------------------------------------------------
332 l_stmt_num := 40;
333 INSERT INTO cst_pac_item_cost_details (
334 cost_layer_id,
335 cost_element_id,
336 level_type,
337 item_cost,
338 item_buy_cost,
339 item_make_cost,
340 item_balance,
341 make_balance,
342 buy_balance,
343 last_update_date,
344 last_updated_by,
345 creation_date,
346 created_by,
347 request_id,
348 program_application_id,
349 program_id,
350 program_update_date,
351 last_update_login)
352 SELECT
353 cpic2.cost_layer_id,
354 cpicd.cost_element_id,
355 cpicd.level_type,
356 cpicd.item_cost,
357 0,
358 0,
359 cpicd.item_cost * cpic1.total_layer_quantity,
360 0,
361 0,
362 SYSDATE,
363 i_user_id,
364 SYSDATE,
365 i_user_id,
366 i_request_id,
367 i_prog_app_id,
368 i_prog_id,
369 SYSDATE,
370 i_login_id
371 FROM cst_pac_item_cost_details cpicd,
372 cst_pac_item_costs cpic1,
373 cst_pac_item_costs cpic2
374 WHERE cpicd.cost_layer_id = cpic1.cost_layer_id
375 AND cpic1.pac_period_id = i_prior_pac_period_id
376 AND cpic1.cost_group_id = i_cost_group_id
377 AND cpic2.pac_period_id = i_pac_period_id
378 AND cpic2.cost_group_id = cpic1.cost_group_id
379 AND cpic2.inventory_item_id = cpic1.inventory_item_id;
380
381 ------------------------------------------------
382 -- Copy prior info of CST_PAC_QUANTITY_LAYERS --
383 ------------------------------------------------
384 l_stmt_num := 50;
385 INSERT INTO cst_pac_quantity_layers (
386 quantity_layer_id,
387 cost_layer_id,
388 pac_period_id,
389 cost_group_id,
390 inventory_item_id,
391 layer_quantity,
392 begin_layer_quantity,
393 last_update_date,
394 last_updated_by,
395 creation_date,
396 created_by,
397 request_id,
398 program_application_id,
399 program_id,
400 program_update_date,
401 last_update_login)
402 SELECT
403 cst_pac_quantity_layers_s.nextval,
404 cpic.cost_layer_id,
405 i_pac_period_id,
406 cpql.cost_group_id,
407 cpql.inventory_item_id,
408 cpql.layer_quantity,
409 cpql.layer_quantity,
410 SYSDATE,
411 i_user_id,
412 SYSDATE,
413 i_user_id,
414 i_request_id,
415 i_prog_app_id,
416 i_prog_id,
417 SYSDATE,
418 i_login_id
419 FROM cst_pac_quantity_layers cpql,
420 cst_pac_item_costs cpic
421 WHERE cpql.pac_period_id = i_prior_pac_period_id
422 AND cpic.pac_period_id = i_pac_period_id
423 AND cpic.cost_group_id = i_cost_group_id
424 AND cpic.cost_group_id = cpql.cost_group_id
425 AND cpic.inventory_item_id = cpql.inventory_item_id;
426
427 ------------------------------------------------
428 -- Copy prior info of CST_PAC_PERIOD_BALANCES --
429 ------------------------------------------------
430
431 /* Bug 5496879 If the prior period was closed with pre R12 code, txn_category 10
432 (ending balance line) would not have been created. So insert begining balance
433 txn_category 1 from CPIC and CPICD instead of prior period CPPB */
434 /* Note: this might cause regression to Bug 5337969 as Exp item data is inserted into CPPB */
435
436 l_stmt_num := 55;
437 INSERT INTO cst_pac_period_balances (
438 pac_period_id,
439 cost_group_id,
440 inventory_item_id,
441 cost_layer_id,
442 quantity_layer_id,
443 cost_element_id,
444 level_type,
445 txn_category,
446 txn_category_qty,
447 txn_category_value,
448 period_quantity,
449 periodic_cost,
450 period_balance,
451 variance_amount,
452 last_update_date,
453 last_updated_by,
454 last_update_login,
455 created_by,
456 creation_date,
457 request_id,
458 program_application_id,
459 program_id,
460 program_update_date)
461 (SELECT i_pac_period_id,
462 i_cost_group_id,
463 cpic.inventory_item_id,
464 cpic.cost_layer_id,
465 cpql.quantity_layer_id,
466 cpicd.cost_element_id,
467 cpicd.level_type,
468 1, -- txn_category
469 0, -- txn_category_qty
470 0, -- txn_category_value
471 cpic.total_layer_quantity,
472 cpicd.item_cost,
473 cpicd.item_balance, -- period_balance
474 0, -- variance
475 sysdate,
476 i_user_id,
477 i_login_id,
478 i_user_id,
479 sysdate,
480 i_request_id,
481 i_prog_app_id,
482 i_prog_id,
483 sysdate
484 FROM cst_pac_item_costs cpic,
485 cst_pac_item_cost_details cpicd,
486 cst_pac_quantity_layers cpql
487 WHERE cpic.cost_group_id = i_cost_group_id
488 AND cpic.pac_period_id = i_pac_period_id
489 AND cpicd.cost_layer_id = cpic.cost_layer_id
490 AND cpql.cost_layer_id = cpic.cost_layer_id
491 AND cpql.inventory_item_id = cpic.inventory_item_id
492 AND cpql.cost_group_id = cpic.cost_group_id
493 AND cpql.pac_period_id = cpic.pac_period_id);
494
495 ---------------------------------------------------------------------------
496 -- Copy prior info of wip_pac_period_balances --
497 -- Only the followings are copied : --
498 -- 1. Discrete jobs that are opened or closed in the current period. --
499 -- 2. Scheduled CFM that are opened or closed in the current period. --
500 -- 3. Repetitive Schedules having at least line that are opened or --
501 -- closed in the current period. --
502 -- Thus jobs/schedules that are closed in the previous period will not --
503 -- be copied to current period.
504 ---------------------------------------------------------------------------
505 l_stmt_num := 60;
506 SELECT period_start_date
507 INTO l_current_start_date
508 FROM CST_PAC_PERIODS
509 WHERE pac_period_id = i_pac_period_id;
510
511 l_stmt_num := 70;
512 INSERT INTO wip_pac_period_balances (
513 pac_period_id,
514 cost_group_id,
515 cost_type_id,
516 organization_id,
517 wip_entity_id,
518 line_id,
519 operation_seq_num,
520 operation_completed_units,
521 relieved_assembly_units,
522 tl_resource_in,
523 tl_resource_out,
524 tl_outside_processing_in,
525 tl_outside_processing_out,
526 tl_overhead_in,
527 tl_overhead_out,
528 pl_material_in,
529 pl_material_out,
530 pl_resource_in,
531 pl_resource_out,
532 pl_overhead_in,
533 pl_overhead_out,
534 pl_outside_processing_in,
535 pl_outside_processing_out,
536 pl_material_overhead_in,
537 pl_material_overhead_out,
538 /*added _apull columns for bug#3229515*/
539 pl_material_in_apull,
540 pl_resource_in_apull,
541 pl_overhead_in_apull,
542 pl_outside_processing_in_apull,
543 pl_material_overhead_in_apull,
544 /*end of addition for bug#3229515*/
545 tl_resource_temp,
546 tl_outside_processing_temp,
547 tl_overhead_temp,
548 pl_material_temp,
549 pl_material_overhead_temp,
550 pl_resource_temp,
551 pl_outside_processing_temp,
552 pl_overhead_temp,
553 tl_resource_var,
554 tl_outside_processing_var,
555 tl_overhead_var,
556 pl_material_var,
557 pl_material_overhead_var,
558 pl_resource_var,
559 pl_outside_processing_var,
560 pl_overhead_var,
561 wip_entity_type,
562 unrelieved_scrap_quantity,
563 last_update_date,
564 last_updated_by,
565 creation_date,
566 created_by,
567 request_id,
568 program_application_id,
569 program_id,
570 program_update_date,
571 last_update_login )
572 SELECT
573 i_pac_period_id,
574 wppb.cost_group_id,
575 wppb.cost_type_id,
576 wppb.organization_id,
577 wppb.wip_entity_id,
578 wppb.line_id,
579 wppb.operation_seq_num,
580 wppb.operation_completed_units,
581 wppb.relieved_assembly_units,
582 wppb.tl_resource_in,
583 wppb.tl_resource_out,
584 wppb.tl_outside_processing_in,
585 wppb.tl_outside_processing_out,
586 wppb.tl_overhead_in,
587 wppb.tl_overhead_out,
588 wppb.pl_material_in,
589 wppb.pl_material_out,
590 wppb.pl_resource_in,
591 wppb.pl_resource_out,
592 wppb.pl_overhead_in,
593 wppb.pl_overhead_out,
594 wppb.pl_outside_processing_in,
595 wppb.pl_outside_processing_out,
596 wppb.pl_material_overhead_in,
597 wppb.pl_material_overhead_out,
598 /*bug#3229515-make _apull cols 0 since whatever is incurred would be
599 relieved in the same period*/
600 0,
601 0,
602 0,
603 0,
604 0,
605 /*end of addition for bug#3229515*/
606 wppb.tl_resource_temp,
607 wppb.tl_outside_processing_temp,
608 wppb.tl_overhead_temp,
609 wppb.pl_material_temp,
610 wppb.pl_material_overhead_temp,
611 wppb.pl_resource_temp,
612 wppb.pl_outside_processing_temp,
613 wppb.pl_overhead_temp,
614 wppb.tl_resource_var,
615 wppb.tl_outside_processing_var,
616 wppb.tl_overhead_var,
617 wppb.pl_material_var,
618 wppb.pl_material_overhead_var,
619 wppb.pl_resource_var,
620 wppb.pl_outside_processing_var,
621 wppb.pl_overhead_var,
622 wppb.wip_entity_type,
623 wppb.unrelieved_scrap_quantity,
624 SYSDATE,
625 i_user_id,
626 SYSDATE,
627 i_user_id,
628 i_request_id,
629 i_prog_app_id,
630 i_prog_id,
631 SYSDATE,
632 i_login_id
633 FROM
634 wip_pac_period_balances wppb, wip_entities we
635 WHERE
636 wppb.pac_period_id = i_prior_pac_period_id
637 AND wppb.cost_group_id = i_cost_group_id
638 AND wppb.wip_entity_id = we.wip_entity_id
639 AND (
640 ( we.entity_type IN (1,3) AND EXISTS (
641 SELECT 'X'
642 FROM wip_discrete_jobs wdj
643 WHERE
644 wdj.wip_entity_id = wppb.wip_entity_id AND
645 NVL(wdj.date_closed, l_current_start_date) >= l_current_start_date))
646 OR (we.entity_type = 4 AND EXISTS (
647 SELECT 'X'
648 FROM wip_flow_schedules wfs
649 WHERE
650 wfs.wip_entity_id = wppb.wip_entity_id AND
651 wfs.scheduled_flag = 1 AND
652 wfs.status IN (1,2) AND
653 NVL(wfs.date_closed, l_current_start_date) >= l_current_start_date))
654 OR (we.entity_type =2 AND EXISTS (
655 SELECT 'X'
656 FROM wip_repetitive_schedules wrs
657 WHERE
658 wrs.wip_entity_id = wppb.wip_entity_id AND
659 wrs.line_id = wppb.line_id AND
660 NVL(wrs.date_closed, l_current_start_date) >= l_current_start_date)));
661
662 ---------------------------------------
663 -- Added R12 PAC enhancement
664 ---------------------------------------
665 l_stmt_num := 75;
666 INSERT INTO CST_PAC_REQ_OPER_COST_DETAILS
667 (pac_period_id,
668 cost_group_id,
669 wip_entity_id,
670 line_id,
671 inventory_item_id,
672 cost_element_id,
673 operation_seq_num,
674 applied_value,
675 applied_quantity,
676 relieved_value,
677 relieved_quantity,
678 comp_variance,
679 temp_relieved_value,
680 last_update_date,
681 last_updated_by,
682 creation_date,
683 created_by,
684 request_id ,
685 program_application_id,
686 program_id,
687 program_update_date,
688 last_update_login)
689 SELECT i_pac_period_id,
690 wprocd.cost_group_id,
691 wprocd.wip_entity_id,
692 wprocd.line_id,
693 wprocd.inventory_item_id,
694 wprocd.cost_element_id,
695 wprocd.operation_seq_num,
696 wprocd.applied_value,
697 wprocd.applied_quantity,
698 wprocd.relieved_value,
699 wprocd.relieved_quantity,
700 wprocd.comp_variance,
701 0,
702 SYSDATE,
703 i_user_id,
704 SYSDATE,
705 i_user_id,
706 i_request_id,
707 i_prog_app_id,
708 i_prog_id,
709 SYSDATE,
710 i_login_id
711 FROM CST_PAC_REQ_OPER_COST_DETAILS wprocd,
712 WIP_ENTITIES we
713 WHERE wprocd.pac_period_id = i_prior_pac_period_id
714 AND wprocd.cost_group_id = i_cost_group_id
715 AND wprocd.wip_entity_id = we.wip_entity_id
716 AND (
717 ( we.entity_type IN (1,3) AND EXISTS (
718 SELECT 'X'
719 FROM wip_discrete_jobs wdj
720 WHERE
721 wdj.wip_entity_id = wprocd.wip_entity_id AND
722 NVL(wdj.date_closed, l_current_start_date) >= l_current_start_date))
723 OR (we.entity_type = 2 AND EXISTS (
724 SELECT 'X'
725 FROM wip_repetitive_schedules wrs
726 WHERE
727 wrs.wip_entity_id = wprocd.wip_entity_id AND
728 wrs.line_id = wprocd.line_id AND
729 NVL(wrs.date_closed, l_current_start_date) >= l_current_start_date)));
730
731 END IF;
732
733 ----------------------
734 -- Calling the hook --
735 ----------------------
736 l_stmt_num := 80;
737 l_use_hook := CSTPPCHK.beginning_balance_hook(
738 i_pac_period_id,
739 i_prior_pac_period_id,
740 i_legal_entity,
741 i_cost_type_id,
742 i_cost_group_id,
743 3,
744 i_user_id,
745 i_login_id,
746 i_request_id,
747 i_prog_id,
748 i_prog_app_id,
749 l_err_num,
750 l_err_code,
751 l_err_msg );
752
753 IF (l_err_num <> 0) THEN
754 raise PROCESS_ERROR;
755 END IF;
756
757 IF (l_pLog) THEN
758 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
759 l_module || '.end',
760 l_api_name || ' >>>');
761 END IF;
762
763 EXCEPTION
764
765 WHEN CURRENT_DATA_EXISTS THEN
766 o_err_num := 9999;
767 o_err_code := NULL;
768 o_err_msg := SUBSTR('CSTPPBBS.copy_prior_info_PWAC('
769 || to_char(l_stmt_num)
770 || '): current period data already exists' ,1,240);
771 IF (l_exceptionLog) THEN
772 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
773 l_module || '.' || l_stmt_num,
774 o_err_msg);
775 END IF;
776
777 WHEN PROCESS_ERROR THEN
778 IF (l_exceptionLog) THEN
779 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
780 l_module || '.' || l_stmt_num,
781 l_err_msg);
782 END IF;
783 o_err_num := l_err_num;
784 o_err_code := l_err_code;
785 o_err_msg := l_err_msg;
786
787 WHEN OTHERS THEN
788 o_err_num := SQLCODE;
789 o_err_code := NULL;
790 o_err_msg := SUBSTR('CSTPPBBS.copy_prior_info_PWAC('
791 || to_char(l_stmt_num) || '): ' ||SQLERRM,1,240);
792
793 IF (l_uLog) THEN
794 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
795 l_module || '.' || l_stmt_num,
796 SQLERRM);
797 END IF;
798
799 END copy_prior_info_PWAC;
800
801 END CSTPPBBS;