[Home] [Help]
PACKAGE BODY: APPS.CST_JOBCLOSEVAR_GRP
Source
1 PACKAGE BODY CST_JobCloseVar_GRP AS
2 /* $Header: CSTGWJVB.pls 120.1 2005/08/04 14:59:09 visrivas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CST_JobCloseVar_GRP';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 PROCEDURE Calculate_Job_Variance
8 (
9 p_api_version IN NUMBER,
10 p_init_msg_list IN VARCHAR2,
11 p_commit IN VARCHAR2,
12 p_validation_level IN NUMBER,
13
14 x_return_status OUT NOCOPY VARCHAR2,
15 x_msg_count OUT NOCOPY NUMBER,
16 x_msg_data OUT NOCOPY VARCHAR2,
17
18 p_user_id IN NUMBER,
19 p_login_id IN NUMBER,
20 p_prg_appl_id IN NUMBER,
21 p_prg_id IN NUMBER,
22 p_req_id IN NUMBER,
23 p_wcti_group_id IN NUMBER,
24 p_org_id IN NUMBER
25 )
26 IS
27 l_api_name CONSTANT VARCHAR2(30) :='Calculate_Job_Variance';
28 l_api_version CONSTANT NUMBER := 1.0;
29 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
30 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
31
32 /* Log Severities*/
33 /* 6- UNEXPECTED */
34 /* 5- ERROR */
35 /* 4- EXCEPTION */
36 /* 3- EVENT */
37 /* 2- PROCEDURE */
38 /* 1- STATEMENT */
39
40 /* In general, we should use the following:
41 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
42 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
43 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
44 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
45 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
46 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
47 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
48 */
49
50 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
51 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
52 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
53 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
54
55 l_err_num NUMBER;
56 l_err_msg VARCHAR2(240);
57 l_err_code VARCHAR2(240);
58 l_costing_method NUMBER;
59 l_return_code NUMBER;
60
61 l_return_status VARCHAR2(1);
62 l_stmt_num NUMBER;
63 l_msg_data VARCHAR2(240);
64
65 /* SLA */
66 TYPE l_num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
67 l_transaction_t l_num_tab;
68 l_index NUMBER;
69
70 CURSOR c_transactions IS
71 SELECT TRANSACTION_ID
72 FROM WIP_COST_TXN_INTERFACE
73 WHERE GROUP_ID = p_wcti_group_id;
74
75 BEGIN
76
77 /* Standard Start of API savepoint */
78 SAVEPOINT Calculate_Job_Variance_GRP;
79
80 l_stmt_num := 0;
81 /* Procedure level log message for Entry point */
82 IF (l_pLog) THEN
83 FND_LOG.STRING(
84 FND_LOG.LEVEL_PROCEDURE,
85 l_module || '.begin',
86 'Start of ' || l_full_name || '(' ||
87 'p_user_id=' || p_user_id || ',' ||
88 'p_login_id=' || p_login_id ||',' ||
89 'p_prg_appl_id=' || p_prg_appl_id ||',' ||
90 'p_prg_id=' || p_prg_id ||',' ||
91 'p_req_id=' || p_req_id ||',' ||
92 'p_wcti_group_id=' || p_wcti_group_id ||',' ||
93 'p_org_id=' || p_org_id ||
94 ')');
95 END IF;
96
97 /* Standard call to check for call compatibility. */
98 IF NOT FND_API.Compatible_API_Call ( l_api_version,
99 p_api_version,
100 l_api_name,
101 G_PKG_NAME )
102 THEN
103 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
104 END IF;
105
106 /* Initialize message list if p_init_msg_list is set to TRUE. */
107 IF FND_API.to_Boolean( p_init_msg_list ) THEN
108 FND_MSG_PUB.initialize;
109 END IF;
110
111 /* Initialize API return status to success */
112 l_return_status := FND_API.G_RET_STS_SUCCESS;
113 x_return_status := FND_API.G_RET_STS_SUCCESS;
114
115 /*------------------------------------------------------------------------+
116 | Calling CSTPOYLD.transact_op_yield_var to calculate and account |
117 | operation yield variance for lot based job. |
118 +------------------------------------------------------------------------*/
119 l_stmt_num := 10;
120
121 l_return_code := CSTPOYLD.transact_op_yield_var (
122 p_wcti_group_id,
123 p_user_id,
124 p_login_id,
125 p_prg_appl_id,
126 p_prg_id,
127 p_req_id,
128 l_err_num,
129 l_err_code,
130 l_err_msg);
131
132 IF (l_return_code <> 1) THEN
133 l_msg_data := 'CSTPOYLD.transact_op_yield_var: ' || l_err_msg;
134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
135 END IF;
136
137 /*------------------------------------------------------------------------+
138 | Check if any of the jobs have an asset route associated with it. |
139 | CST_eamCost_PUB.Redistribute_WIP_Accounts redistributes accounts |
140 | values from the Accounting class of the route job to the accounting |
141 | class of the memeber assets. |
142 +------------------------------------------------------------------------*/
143 l_stmt_num := 20;
144
145 CST_eamCost_PUB.Redistribute_WIP_Accounts (
146 p_api_version => 1.0,
147 p_wcti_group_id => p_wcti_group_id,
148 p_user_id => p_user_id,
149 p_request_id => p_req_id,
150 p_prog_id => p_prg_id,
151 p_prog_app_id => p_prg_appl_id,
152 p_login_id => p_login_id,
153 x_return_status => l_return_status,
154 x_msg_count => x_msg_count,
155 x_msg_data => x_msg_data);
156
157 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
158 l_msg_data := 'Error in CST_eamCost_PUB.Redistribute_WIP_Accounts()';
159 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
160 END IF;
161
162 /*------------------------------------------------------------------------+
163 | Post elemental variance for discrete and non-std jobs. |
164 | Group same accounts. If the account is unique for the cost element |
165 | then populate cost_element_id, otherwise, NULL. |
166 | Sum across all accounting periods and minus variance that has |
167 | already been posted. |
168 | NOTE: The period close form gives warning if there is pending uncosted|
169 | txns. But the user can go ahead closing the period. This will |
170 | cause In's and Out's of the period to be changed after the period |
171 | is closed. In order to recognize these late txns, need to sum |
172 | across all accounting periods. |
173 +------------------------------------------------------------------------*/
174 l_stmt_num := 30;
175
176 INSERT INTO wip_transaction_accounts
177 (transaction_id,
178 reference_account,
179 last_update_date,
180 last_updated_by,
181 creation_date,
182 created_by,
183 last_update_login,
184 organization_id,
185 transaction_date,
186 wip_entity_id,
187 repetitive_schedule_id,
188 accounting_line_type,
189 transaction_value,
190 base_transaction_value,
191 contra_set_id,
192 primary_quantity,
193 rate_or_amount,
194 basis_type,
195 resource_id,
196 cost_element_id,
197 activity_id,
198 currency_code,
199 currency_conversion_date,
200 currency_conversion_type,
201 currency_conversion_rate,
202 request_id,
203 program_application_id,
204 program_id,
205 program_update_date)
206 SELECT wcti.transaction_id,
207 DECODE(cce.cost_element_id,
208 1, wdj.material_account,
209 2, wdj.material_overhead_account,
210 3, wdj.resource_account,
211 4, wdj.outside_processing_account,
212 5, wdj.overhead_account),
213 sysdate,
214 p_user_id,
215 sysdate,
216 p_user_id,
217 p_login_id,
218 wcti.organization_id,
219 wcti.transaction_date,
220 wcti.wip_entity_id,
221 NULL,
222 7,
223 NULL,
224 SUM(DECODE(cce.cost_element_id,
225 1, (NVL(wpb.pl_material_out,0)
226 - NVL(wpb.pl_material_in,0)
227 + NVL(wpb.pl_material_var,0)
228 + NVL(wpb.tl_material_out,0)
229 - 0
230 + NVL(wpb.tl_material_var,0)),
231 2, (NVL(wpb.pl_material_overhead_out,0)
232 - NVL(wpb.pl_material_overhead_in,0)
233 + NVL(wpb.pl_material_overhead_var,0)
234 + NVL(wpb.tl_material_overhead_out,0)
235 - 0
236 + NVL(wpb.tl_material_overhead_var,0)),
237 3, (NVL(wpb.pl_resource_out,0)
238 - NVL(wpb.pl_resource_in,0)
239 + NVL(wpb.pl_resource_var,0)
240 + NVL(wpb.tl_resource_out,0)
241 - NVL(wpb.tl_resource_in,0)
242 + NVL(wpb.tl_resource_var,0)),
243 4, (NVL(wpb.pl_outside_processing_out,0)
244 - NVL(wpb.pl_outside_processing_in,0)
245 + NVL(wpb.pl_outside_processing_var,0)
246 + NVL(wpb.tl_outside_processing_out,0)
247 - NVL(wpb.tl_outside_processing_in,0)
248 + NVL(wpb.tl_outside_processing_var,0)),
249 5, (NVL(wpb.pl_overhead_out,0)
250 - NVL(wpb.pl_overhead_in,0)
251 + NVL(wpb.pl_overhead_var,0)
252 + NVL(wpb.tl_overhead_out,0)
253 - NVL(wpb.tl_overhead_in,0)
254 + NVL(wpb.tl_overhead_var,0)))),
255 wcti.wip_entity_id,
256 NULL,
257 NULL,
258 NULL,
259 NULL,
260 DECODE((MAX(cce.cost_element_id) - MIN(cce.cost_element_id)),
261 0, MAX(cce.cost_element_id), NULL),
262 NULL,
263 NULL,
264 NULL,
265 NULL,
266 NULL,
267 p_req_id,
268 p_prg_appl_id,
269 p_prg_id,
270 sysdate
271 FROM wip_cost_txn_interface wcti,
272 wip_period_balances wpb,
273 wip_discrete_jobs wdj,
274 cst_cost_elements cce
275 WHERE wcti.group_id = p_wcti_group_id
276 AND wcti.wip_entity_id = wpb.wip_entity_id
277 AND wcti.wip_entity_id = wdj.wip_entity_id
278 AND wcti.acct_period_id >= wpb.acct_period_id
279 GROUP BY wcti.transaction_id,
280 wcti.wip_entity_id,
281 wcti.organization_id,
282 wcti.transaction_date,
283 decode(cce.cost_element_id,
284 1, wdj.material_account,
285 2, wdj.material_overhead_account,
286 3, wdj.resource_account,
287 4, wdj.outside_processing_account,
288 5, wdj.overhead_account);
289
290 /*------------------------------------------------------------------------+
291 | Post single level variances for discrete and non-expense non-std jobs |
292 | NOTE: The period close form gives warning if there is pending uncosted|
293 | txns. But the user can go ahead closing the period. This will |
294 | cause In's and Out's of the period to be changed after the period |
295 | is closed. In order to recognize these late txns, need to sum |
296 | across all accounting periods. |
297 +------------------------------------------------------------------------*/
298 l_stmt_num := 40;
299
300 INSERT INTO wip_transaction_accounts
301 (transaction_id,
302 reference_account,
303 last_update_date,
304 last_updated_by,
305 creation_date,
306 created_by,
307 last_update_login,
308 organization_id,
309 transaction_date,
310 wip_entity_id,
311 repetitive_schedule_id,
312 accounting_line_type,
313 transaction_value,
314 base_transaction_value,
315 contra_set_id,
316 primary_quantity,
317 rate_or_amount,
318 basis_type,
319 resource_id,
320 cost_element_id,
321 activity_id,
322 currency_code,
323 currency_conversion_date,
324 currency_conversion_type,
325 currency_conversion_rate,
326 request_id,
327 program_application_id,
328 program_id,
329 program_update_date)
330 SELECT wcti.transaction_id,
331 DECODE(cce.cost_element_id,
332 1, wdj.material_variance_account,
333 3, wdj.resource_variance_account,
334 4, wdj.outside_proc_variance_account,
335 5, wdj.overhead_variance_account),
336 SYSDATE,
337 p_user_id,
338 SYSDATE,
339 p_user_id,
340 p_login_id,
341 wcti.organization_id,
342 wcti.transaction_date,
343 wcti.wip_entity_id,
344 NULL,
345 8,
346 NULL,
347 SUM(DECODE(cce.cost_element_id,
348 1, -1 * ( NVL(wpb.pl_material_out,0)
349 - NVL(wpb.pl_material_in,0)
350 + NVL(wpb.pl_material_var,0)
351 + NVL(wpb.pl_material_overhead_out,0)
352 - NVL(wpb.pl_material_overhead_in,0)
353 + NVL(wpb.pl_material_overhead_var,0)
354 + NVL(wpb.pl_resource_out,0)
355 - NVL(wpb.pl_resource_in,0)
356 + NVL(wpb.pl_resource_var,0)
357 + NVL(wpb.pl_overhead_out,0)
358 - NVL(wpb.pl_overhead_in,0)
359 + NVL(wpb.pl_overhead_var,0)
360 + NVL(wpb.pl_outside_processing_out,0)
361 - NVL(wpb.pl_outside_processing_in,0)
362 + NVL(wpb.pl_outside_processing_var,0)
363 + NVL(wpb.tl_material_out,0)
364 - 0
365 + NVL(wpb.tl_material_var,0)
366 + NVL(wpb.tl_material_overhead_out,0)
367 - 0
368 + NVL(wpb.tl_material_overhead_var,0)),
369 3, -1 * ( NVL(wpb.tl_resource_out,0)
370 - NVL(wpb.tl_resource_in,0)
371 + NVL(wpb.tl_resource_var,0)),
372 4, -1 * ( NVL(wpb.tl_outside_processing_out,0)
373 - NVL(wpb.tl_outside_processing_in,0)
374 + NVL(wpb.tl_outside_processing_var,0)),
375 5, -1 * ( NVL(wpb.tl_overhead_out,0)
376 - NVL(wpb.tl_overhead_in,0)
377 + NVL(wpb.tl_overhead_var,0)))),
378 wcti.wip_entity_id,
379 NULL,
380 NULL,
381 NULL,
382 NULL,
383 DECODE((MAX(cce.cost_element_id) - MIN(cce.cost_element_id)),
384 0, MAX(cce.cost_element_id), NULL),
385 NULL,
386 NULL,
387 NULL,
388 NULL,
389 NULL,
390 p_req_id,
391 p_prg_appl_id,
392 p_prg_id,
393 SYSDATE
394 FROM wip_cost_txn_interface wcti,
395 wip_period_balances wpb,
396 wip_discrete_jobs wdj,
397 cst_cost_elements cce
398 WHERE wcti.group_id = p_wcti_group_id
399 AND cce.cost_element_id <> 2
400 AND wcti.wip_entity_id = wpb.wip_entity_id
401 AND wcti.wip_entity_id = wdj.wip_entity_id
402 AND wcti.acct_period_id >= wpb.acct_period_id
403 GROUP BY wcti.transaction_id,
404 wcti.wip_entity_id,
405 wcti.organization_id,
406 wcti.transaction_date,
407 DECODE(cce.cost_element_id,
408 1, wdj.material_variance_account,
409 3, wdj.resource_variance_account,
410 4, wdj.outside_proc_variance_account,
411 5, wdj.overhead_variance_account);
412 l_stmt_num := 45;
413 OPEN c_transactions;
414 FETCH c_transactions BULK COLLECT INTO l_transaction_t;
415 CLOSE c_transactions;
416
417 l_stmt_num := 46;
418 FORALL l_index in l_transaction_t.FIRST..l_transaction_t.LAST
419 UPDATE WIP_TRANSACTION_ACCOUNTS
420 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
421 WHERE TRANSACTION_ID = l_transaction_t(l_index);
422
423 l_stmt_num := 47;
424 /* Create the Events for the transactions in the WCTI group */
425
426 CST_XLA_PVT.CreateBulk_WIPXLAEvent(
427 p_api_version => 1.0,
428 p_init_msg_list => FND_API.G_FALSE,
429 p_commit => FND_API.G_FALSE,
430 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
431 x_return_status => l_return_status,
432 x_msg_count => x_msg_count,
433 x_msg_data => x_msg_data,
434 p_wcti_group_id => p_wcti_group_id,
435 p_organization_id => p_org_id );
436
437 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
438 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
439 END IF;
440
441
442 /*------------------------------------------------------------------------+
443 | Update variance columns. |
444 | While summing across wip_period_balance rows to accumulate costs we |
445 | do not want the var values in the close period to get picked up. So |
446 | we need them out with the decode. This is needed since wip now lets |
447 | you re-open a closed job and variance could be posted multiple |
448 | times in the same period if the job were closed repeatedly. |
449 +------------------------------------------------------------------------*/
450 l_stmt_num := 50;
451
452 UPDATE wip_period_balances wpb
453 SET (last_updated_by,
454 last_update_date,
455 last_update_login,
456 pl_material_var,
457 pl_material_overhead_var,
458 pl_resource_var,
459 pl_outside_processing_var,
460 pl_overhead_var,
461 tl_material_var,
462 tl_material_overhead_var,
463 tl_resource_var,
464 tl_outside_processing_var,
465 tl_overhead_var )
466 =
467 (SELECT p_user_id,
468 SYSDATE,
469 p_login_id,
470 SUM( NVL(pl_material_in,0)
471 - NVL(pl_material_out,0)
472 - DECODE(wpb2.acct_period_id,
473 wpb.acct_period_id,0,
474 NVL(pl_material_var,0))),
475 SUM( NVL(pl_material_overhead_in,0)
476 - NVL(pl_material_overhead_out,0)
477 - DECODE(wpb2.acct_period_id,
478 wpb.acct_period_id,0,
479 NVL(pl_material_overhead_var,0))),
480 SUM( NVL(pl_resource_in,0)
481 - NVL(pl_resource_out,0)
482 - DECODE(wpb2.acct_period_id,
483 wpb.acct_period_id,0,
484 NVL(pl_resource_var,0))),
485 SUM( NVL(pl_outside_processing_in,0)
486 - NVL(pl_outside_processing_out,0)
487 - DECODE(wpb2.acct_period_id,
488 wpb.acct_period_id,0,
489 NVL(pl_outside_processing_var,0))),
490 SUM( NVL(pl_overhead_in,0)
491 - NVL(pl_overhead_out,0)
492 - DECODE(wpb2.acct_period_id,
493 wpb.acct_period_id,0,
494 NVL(pl_overhead_var,0))),
495 SUM( 0
496 - NVL(tl_material_out,0)
497 - DECODE(wpb2.acct_period_id,
498 wpb.acct_period_id,0,
499 NVL(tl_material_var,0))),
500 SUM( 0
501 - NVL(tl_material_overhead_out,0)
502 - DECODE(wpb2.acct_period_id,
503 wpb.acct_period_id,0,
504 NVL(tl_material_overhead_var,0))),
505 SUM( NVL(tl_resource_in,0)
506 - NVL(tl_resource_out,0)
507 - DECODE(wpb2.acct_period_id,
508 wpb.acct_period_id,0,
509 NVL(tl_resource_var,0))),
510 SUM( NVL(tl_outside_processing_in,0)
511 - NVL(tl_outside_processing_out,0)
512 - DECODE(wpb2.acct_period_id,
513 wpb.acct_period_id,0,
514 NVL(tl_outside_processing_var,0))),
515 SUM( NVL(tl_overhead_in,0)
516 - NVL(tl_overhead_out,0)
517 - DECODE(wpb2.acct_period_id,
518 wpb.acct_period_id,0,
519 NVL(tl_overhead_var,0)))
520 FROM wip_period_balances wpb2
521 WHERE wpb2.wip_entity_id = wpb.wip_entity_id
522 AND wpb2.acct_period_id <= wpb.acct_period_id)
523 WHERE (wpb.acct_period_id,
524 wpb.wip_entity_id)
525 IN (SELECT i.acct_period_id,
526 i.wip_entity_id
527 FROM wip_cost_txn_interface i
528 WHERE i.group_id = p_wcti_group_id);
529
530 /*------------------------------------------------------------------------+
531 | Get the primary costing method of the Organization. |
532 +------------------------------------------------------------------------*/
533 l_stmt_num := 60;
534
535 SELECT primary_cost_method
536 INTO l_costing_method
537 FROM mtl_parameters
538 WHERE organization_id = p_org_id;
539
540 /*------------------------------------------------------------------------+
541 | If primary_cost_method is average, FIFO or LIFO then update the |
542 | value of variance relieved |
543 +------------------------------------------------------------------------*/
544 l_stmt_num := 70;
545
546 IF (l_costing_method IN (2, 5, 6)) THEN
547
548 UPDATE wip_req_operation_cost_details w
549 SET (relieved_variance_value)
550 = (SELECT NVL(applied_matl_value,0)
551 - NVL(relieved_matl_completion_value,0)
552 - NVL(relieved_matl_scrap_value,0)
553 FROM wip_req_operation_cost_details w2
554 WHERE w.wip_entity_id = w2.wip_entity_id
555 AND w.organization_id = w2.organization_id
556 AND w.inventory_item_id = w2.inventory_item_id
557 AND w.operation_seq_num = w2.operation_seq_num
558 AND w.cost_element_id = w2.cost_element_id )
559 WHERE w.wip_entity_id
560 IN (SELECT wip_entity_id
561 FROM wip_cost_txn_interface wcti
562 WHERE wcti.group_id = p_wcti_group_id );
563
564 UPDATE wip_operation_resources w
565 SET (relieved_variance_value)
566 = (SELECT NVL(applied_resource_value,0)
567 - NVL(relieved_res_completion_value,0)
568 - NVL(relieved_res_scrap_value,0)
569 FROM wip_operation_resources w2
570 WHERE w.wip_entity_id = w2.wip_entity_id
571 AND w.organization_id = w2.organization_id
572 AND w.operation_seq_num = w2.operation_seq_num
573 AND w.resource_seq_num = w2.resource_seq_num)
574 WHERE w.wip_entity_id
575 IN (SELECT wip_entity_id
576 FROM wip_cost_txn_interface wcti
577 WHERE wcti.group_id = p_wcti_group_id);
578
579 UPDATE wip_operation_overheads w
580 SET (relieved_variance_value)
581 = (SELECT NVL(applied_ovhd_value,0)
582 - NVL(relieved_ovhd_completion_value,0)
583 - NVL(relieved_ovhd_scrap_value,0)
584 FROM wip_operation_overheads w2
585 WHERE w.wip_entity_id = w2.wip_entity_id
586 AND w.organization_id = w2.organization_id
587 AND w.operation_seq_num = w2.operation_seq_num
588 AND w.resource_seq_num = w2.resource_seq_num
589 AND w.overhead_id = w2.overhead_id
590 AND w.basis_type = w2.basis_type )
591 WHERE w.wip_entity_id
592 IN (SELECT wip_entity_id
593 FROM wip_cost_txn_interface wcti
594 WHERE wcti.group_id = p_wcti_group_id);
595 END IF;
596
597 /*------------------------------------------------------------------------+
598 | Delete any balance rows beyond the job's close date (accounting |
599 | period starting date > job close date) |
600 +------------------------------------------------------------------------*/
601 l_stmt_num := 80;
602
603 DELETE FROM WIP_PERIOD_BALANCES wpb
604 WHERE (wpb.acct_period_id,
605 wpb.wip_entity_id)
606 IN
607 (SELECT a.acct_period_id,
608 i.wip_entity_id
609 FROM wip_cost_txn_interface i,
610 org_acct_periods a
611 WHERE i.group_id = p_wcti_group_id
612 AND a.acct_period_id > i.acct_period_id
613 AND a.organization_id = i.organization_id);
614
615 /*------------------------------------------------------------------------+
616 | Copy rows from wip_cost_txn_interface to wip_transactions and |
617 | delete from wip_cost_txn_interface. |
618 +------------------------------------------------------------------------*/
619 l_stmt_num := 90;
620
621 l_err_num := CSTPWCPX.CMLCPX(p_wcti_group_id,
622 p_org_id,
623 6,
624 p_user_id,
625 p_login_id,
626 p_prg_appl_id,
627 p_prg_id,
628 p_req_id,
629 l_err_msg);
630
631 IF (l_err_num <> 0) THEN
632 l_msg_data := 'CSTPWCPX.CMLCPX: ' || l_err_msg;
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634 END IF;
635
636 /* Procedure level log message for Entry point */
637 IF (l_pLog) THEN
638 FND_LOG.STRING(
639 FND_LOG.LEVEL_PROCEDURE,
640 l_module || '.end',
641 'End of ' || l_full_name
642 );
643 END IF;
644
645 /* Get message count and if 1, return message data. */
646 FND_MSG_PUB.Count_And_Get
647 ( p_count => x_msg_count,
648 p_data => x_msg_data
649 );
650
651 /* Standard check of p_commit. */
652 IF FND_API.To_Boolean( p_commit ) THEN
653 COMMIT WORK;
654 END IF;
655
656 EXCEPTION
657 WHEN FND_API.G_EXC_ERROR THEN
658 ROLLBACK TO Calculate_Job_Variance_GRP;
659 x_return_status := FND_API.G_RET_STS_ERROR ;
660
661 IF l_errorLog THEN
662 FND_LOG.STRING(
663 FND_LOG.LEVEL_ERROR,
664 l_module || '.' || l_stmt_num,
665 l_msg_data
666 );
667 END IF;
668
669 /* Get message count and if 1, return message data. */
670 FND_MSG_PUB.Count_And_Get
671 ( p_count => x_msg_count,
672 p_data => x_msg_data
673 );
674
675 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
676 ROLLBACK TO Calculate_Job_Variance_GRP;
677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
678
679 IF (l_exceptionLog) THEN
680 FND_LOG.STRING(
681 FND_LOG.LEVEL_EXCEPTION,
682 l_module || '.' || l_stmt_num,
683 l_msg_data
684 );
685 END IF;
686
687 FND_MSG_PUB.Count_And_Get
688 ( p_count => x_msg_count,
689 p_data => x_msg_data
690 );
691
692 WHEN OTHERS THEN
693 ROLLBACK TO Calculate_Job_Variance_GRP;
694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
695
696 IF (l_uLog) THEN
697 FND_LOG.STRING(
698 FND_LOG.LEVEL_UNEXPECTED,
699 l_module || '.' || l_stmt_num,
700 SQLERRM
701 );
702 END IF;
703
704 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
705 THEN
706 FND_MSG_PUB.Add_Exc_Msg
707 ( G_PKG_NAME,
708 l_api_name,
709 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 240)
710 );
711 END IF;
712 FND_MSG_PUB.Count_And_Get
713 ( p_count => x_msg_count,
714 p_data => x_msg_data
715 );
716
717 END Calculate_Job_Variance;
718
719 END CST_JobCloseVar_GRP; /* end package body */