[Home] [Help]
PACKAGE BODY: APPS.CST_JOBCLOSEVAR_GRP
Source
1 PACKAGE BODY CST_JobCloseVar_GRP AS
2 /* $Header: CSTGWJVB.pls 120.3 2012/03/14 23:02:46 fayang ship $ */
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 --{BUG#13072387
276 , org_acct_periods ocpf
277 , org_acct_periods ocpt
278 --}
279 WHERE wcti.group_id = p_wcti_group_id
280 AND wcti.wip_entity_id = wpb.wip_entity_id
281 AND wcti.wip_entity_id = wdj.wip_entity_id
282 --{
283 AND wcti.acct_period_id = ocpt.acct_period_id
284 AND wcti.organization_id = ocpt.organization_id
285 AND wpb.acct_period_id = ocpf.acct_period_id
286 AND wpb.organization_id = ocpf.organization_id
287 AND ocpt.period_start_date >= ocpf.period_start_date
288 --AND wcti.acct_period_id >= wpb.acct_period_id
289 --}
290 GROUP BY wcti.transaction_id,
291 wcti.wip_entity_id,
292 wcti.organization_id,
293 wcti.transaction_date,
294 decode(cce.cost_element_id,
295 1, wdj.material_account,
296 2, wdj.material_overhead_account,
297 3, wdj.resource_account,
298 4, wdj.outside_processing_account,
299 5, wdj.overhead_account);
300
301 /*------------------------------------------------------------------------+
302 | Post single level variances for discrete and non-expense non-std jobs |
303 | NOTE: The period close form gives warning if there is pending uncosted|
304 | txns. But the user can go ahead closing the period. This will |
305 | cause In's and Out's of the period to be changed after the period |
306 | is closed. In order to recognize these late txns, need to sum |
307 | across all accounting periods. |
308 +------------------------------------------------------------------------*/
309 l_stmt_num := 40;
310
311 INSERT INTO wip_transaction_accounts
312 (transaction_id,
313 reference_account,
314 last_update_date,
315 last_updated_by,
316 creation_date,
317 created_by,
321 wip_entity_id,
318 last_update_login,
319 organization_id,
320 transaction_date,
322 repetitive_schedule_id,
323 accounting_line_type,
324 transaction_value,
325 base_transaction_value,
326 contra_set_id,
327 primary_quantity,
328 rate_or_amount,
329 basis_type,
330 resource_id,
331 cost_element_id,
332 activity_id,
333 currency_code,
334 currency_conversion_date,
335 currency_conversion_type,
336 currency_conversion_rate,
337 request_id,
338 program_application_id,
339 program_id,
340 program_update_date)
341 SELECT wcti.transaction_id,
342 DECODE(cce.cost_element_id,
343 1, wdj.material_variance_account,
344 3, wdj.resource_variance_account,
345 4, wdj.outside_proc_variance_account,
346 5, wdj.overhead_variance_account),
347 SYSDATE,
348 p_user_id,
349 SYSDATE,
350 p_user_id,
351 p_login_id,
352 wcti.organization_id,
353 wcti.transaction_date,
354 wcti.wip_entity_id,
355 NULL,
356 8,
357 NULL,
358 SUM(DECODE(cce.cost_element_id,
359 1, -1 * ( NVL(wpb.pl_material_out,0)
360 - NVL(wpb.pl_material_in,0)
361 + NVL(wpb.pl_material_var,0)
362 + NVL(wpb.pl_material_overhead_out,0)
363 - NVL(wpb.pl_material_overhead_in,0)
364 + NVL(wpb.pl_material_overhead_var,0)
365 + NVL(wpb.pl_resource_out,0)
366 - NVL(wpb.pl_resource_in,0)
367 + NVL(wpb.pl_resource_var,0)
368 + NVL(wpb.pl_overhead_out,0)
369 - NVL(wpb.pl_overhead_in,0)
370 + NVL(wpb.pl_overhead_var,0)
371 + NVL(wpb.pl_outside_processing_out,0)
372 - NVL(wpb.pl_outside_processing_in,0)
373 + NVL(wpb.pl_outside_processing_var,0)
374 + NVL(wpb.tl_material_out,0)
375 - 0
376 + NVL(wpb.tl_material_var,0)
377 + NVL(wpb.tl_material_overhead_out,0)
378 - 0
379 + NVL(wpb.tl_material_overhead_var,0)),
380 3, -1 * ( NVL(wpb.tl_resource_out,0)
381 - NVL(wpb.tl_resource_in,0)
382 + NVL(wpb.tl_resource_var,0)),
383 4, -1 * ( NVL(wpb.tl_outside_processing_out,0)
384 - NVL(wpb.tl_outside_processing_in,0)
385 + NVL(wpb.tl_outside_processing_var,0)),
386 5, -1 * ( NVL(wpb.tl_overhead_out,0)
387 - NVL(wpb.tl_overhead_in,0)
388 + NVL(wpb.tl_overhead_var,0)))),
389 wcti.wip_entity_id,
390 NULL,
391 NULL,
392 NULL,
393 NULL,
394 DECODE((MAX(cce.cost_element_id) - MIN(cce.cost_element_id)),
395 0, MAX(cce.cost_element_id), NULL),
396 NULL,
397 NULL,
398 NULL,
399 NULL,
400 NULL,
401 p_req_id,
402 p_prg_appl_id,
403 p_prg_id,
404 SYSDATE
405 FROM wip_cost_txn_interface wcti,
406 wip_period_balances wpb,
407 wip_discrete_jobs wdj,
408 cst_cost_elements cce
409 --{BUG#13072387
410 , org_acct_periods ocpf
411 , org_acct_periods ocpt
412 --}
413 WHERE wcti.group_id = p_wcti_group_id
414 AND cce.cost_element_id <> 2
415 AND wcti.wip_entity_id = wpb.wip_entity_id
416 AND wcti.wip_entity_id = wdj.wip_entity_id
417 --{
418 AND wcti.acct_period_id = ocpt.acct_period_id
419 AND wcti.organization_id= ocpt.organization_id
420 AND wpb.acct_period_id = ocpf.acct_period_id
421 AND wpb.organization_id = ocpf.organization_id
422 AND ocpt.period_start_date >= ocpf.period_start_date
423 --AND wcti.acct_period_id >= wpb.acct_period_id
424 --}
425 GROUP BY wcti.transaction_id,
426 wcti.wip_entity_id,
427 wcti.organization_id,
428 wcti.transaction_date,
429 DECODE(cce.cost_element_id,
430 1, wdj.material_variance_account,
431 3, wdj.resource_variance_account,
432 4, wdj.outside_proc_variance_account,
433 5, wdj.overhead_variance_account);
434 l_stmt_num := 45;
435 OPEN c_transactions;
436 FETCH c_transactions BULK COLLECT INTO l_transaction_t;
437 CLOSE c_transactions;
438
439 l_stmt_num := 46;
440 FORALL l_index in l_transaction_t.FIRST..l_transaction_t.LAST
444
441 UPDATE WIP_TRANSACTION_ACCOUNTS
442 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
443 WHERE TRANSACTION_ID = l_transaction_t(l_index);
445 l_stmt_num := 47;
446 /* Create the Events for the transactions in the WCTI group */
447
448 CST_XLA_PVT.CreateBulk_WIPXLAEvent(
449 p_api_version => 1.0,
450 p_init_msg_list => FND_API.G_FALSE,
451 p_commit => FND_API.G_FALSE,
452 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
453 x_return_status => l_return_status,
454 x_msg_count => x_msg_count,
455 x_msg_data => x_msg_data,
456 p_wcti_group_id => p_wcti_group_id,
457 p_organization_id => p_org_id );
458
459 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462
463
464 /*------------------------------------------------------------------------+
465 | Update variance columns. |
466 | While summing across wip_period_balance rows to accumulate costs we |
467 | do not want the var values in the close period to get picked up. So |
468 | we need them out with the decode. This is needed since wip now lets |
469 | you re-open a closed job and variance could be posted multiple |
470 | times in the same period if the job were closed repeatedly. |
471 +------------------------------------------------------------------------*/
472 l_stmt_num := 50;
473
474 UPDATE wip_period_balances wpb
475 SET (last_updated_by,
476 last_update_date,
477 last_update_login,
478 pl_material_var,
479 pl_material_overhead_var,
480 pl_resource_var,
481 pl_outside_processing_var,
482 pl_overhead_var,
483 tl_material_var,
484 tl_material_overhead_var,
485 tl_resource_var,
486 tl_outside_processing_var,
487 tl_overhead_var )
488 =
489 (SELECT p_user_id,
490 SYSDATE,
491 p_login_id,
492 SUM( NVL(pl_material_in,0)
493 - NVL(pl_material_out,0)
494 - DECODE(wpb2.acct_period_id,
495 wpb.acct_period_id,0,
496 NVL(pl_material_var,0))),
497 SUM( NVL(pl_material_overhead_in,0)
498 - NVL(pl_material_overhead_out,0)
499 - DECODE(wpb2.acct_period_id,
500 wpb.acct_period_id,0,
501 NVL(pl_material_overhead_var,0))),
502 SUM( NVL(pl_resource_in,0)
503 - NVL(pl_resource_out,0)
504 - DECODE(wpb2.acct_period_id,
505 wpb.acct_period_id,0,
506 NVL(pl_resource_var,0))),
507 SUM( NVL(pl_outside_processing_in,0)
508 - NVL(pl_outside_processing_out,0)
509 - DECODE(wpb2.acct_period_id,
510 wpb.acct_period_id,0,
511 NVL(pl_outside_processing_var,0))),
512 SUM( NVL(pl_overhead_in,0)
513 - NVL(pl_overhead_out,0)
514 - DECODE(wpb2.acct_period_id,
515 wpb.acct_period_id,0,
516 NVL(pl_overhead_var,0))),
517 SUM( 0
518 - NVL(tl_material_out,0)
519 - DECODE(wpb2.acct_period_id,
520 wpb.acct_period_id,0,
521 NVL(tl_material_var,0))),
522 SUM( 0
523 - NVL(tl_material_overhead_out,0)
524 - DECODE(wpb2.acct_period_id,
525 wpb.acct_period_id,0,
526 NVL(tl_material_overhead_var,0))),
527 SUM( NVL(tl_resource_in,0)
528 - NVL(tl_resource_out,0)
529 - DECODE(wpb2.acct_period_id,
530 wpb.acct_period_id,0,
531 NVL(tl_resource_var,0))),
532 SUM( NVL(tl_outside_processing_in,0)
533 - NVL(tl_outside_processing_out,0)
534 - DECODE(wpb2.acct_period_id,
535 wpb.acct_period_id,0,
536 NVL(tl_outside_processing_var,0))),
537 SUM( NVL(tl_overhead_in,0)
538 - NVL(tl_overhead_out,0)
539 - DECODE(wpb2.acct_period_id,
540 wpb.acct_period_id,0,
541 NVL(tl_overhead_var,0)))
542 FROM wip_period_balances wpb2
543 --{BUG#13072387
544 , org_acct_periods ocpf
545 , org_acct_periods ocpt
546 --}
547 WHERE wpb2.wip_entity_id = wpb.wip_entity_id
548 --{
549 --AND wpb2.acct_period_id <= wpb.acct_period_id
553 AND wpb.organization_id = ocpt.organization_id
550 AND wpb2.acct_period_id = ocpf.acct_period_id
551 AND wpb2.organization_id= ocpf.organization_id
552 AND wpb.acct_period_id = ocpt.acct_period_id
554 AND ocpf.period_start_date <= ocpt.period_start_date
555 --}
556 )
557 WHERE (wpb.acct_period_id,
558 wpb.wip_entity_id)
559 IN (SELECT i.acct_period_id,
560 i.wip_entity_id
561 FROM wip_cost_txn_interface i
562 WHERE i.group_id = p_wcti_group_id);
563
564 /*------------------------------------------------------------------------+
565 | Get the primary costing method of the Organization. |
566 +------------------------------------------------------------------------*/
567 l_stmt_num := 60;
568
569 SELECT primary_cost_method
570 INTO l_costing_method
571 FROM mtl_parameters
572 WHERE organization_id = p_org_id;
573
574 /*------------------------------------------------------------------------+
575 | If primary_cost_method is average, FIFO or LIFO then update the |
576 | value of variance relieved |
577 +------------------------------------------------------------------------*/
578 l_stmt_num := 70;
579
580 IF (l_costing_method IN (2, 5, 6)) THEN
581
582 UPDATE wip_req_operation_cost_details w
583 SET (relieved_variance_value)
584 = (SELECT NVL(applied_matl_value,0)
585 - NVL(relieved_matl_completion_value,0)
586 - NVL(relieved_matl_scrap_value,0)
587 FROM wip_req_operation_cost_details w2
588 WHERE w.wip_entity_id = w2.wip_entity_id
589 AND w.organization_id = w2.organization_id
590 AND w.inventory_item_id = w2.inventory_item_id
591 AND w.operation_seq_num = w2.operation_seq_num
592 AND w.cost_element_id = w2.cost_element_id )
593 WHERE w.wip_entity_id
594 IN (SELECT wip_entity_id
595 FROM wip_cost_txn_interface wcti
596 WHERE wcti.group_id = p_wcti_group_id );
597
598 UPDATE wip_operation_resources w
599 SET (relieved_variance_value)
600 = (SELECT NVL(applied_resource_value,0)
601 - NVL(relieved_res_completion_value,0)
602 - NVL(relieved_res_scrap_value,0)
603 FROM wip_operation_resources w2
604 WHERE w.wip_entity_id = w2.wip_entity_id
605 AND w.organization_id = w2.organization_id
606 AND w.operation_seq_num = w2.operation_seq_num
607 AND w.resource_seq_num = w2.resource_seq_num)
608 WHERE w.wip_entity_id
609 IN (SELECT wip_entity_id
610 FROM wip_cost_txn_interface wcti
611 WHERE wcti.group_id = p_wcti_group_id);
612
613 UPDATE wip_operation_overheads w
614 SET (relieved_variance_value)
615 = (SELECT NVL(applied_ovhd_value,0)
616 - NVL(relieved_ovhd_completion_value,0)
617 - NVL(relieved_ovhd_scrap_value,0)
618 FROM wip_operation_overheads w2
619 WHERE w.wip_entity_id = w2.wip_entity_id
620 AND w.organization_id = w2.organization_id
621 AND w.operation_seq_num = w2.operation_seq_num
622 AND w.resource_seq_num = w2.resource_seq_num
623 AND w.overhead_id = w2.overhead_id
624 AND w.basis_type = w2.basis_type )
625 WHERE w.wip_entity_id
626 IN (SELECT wip_entity_id
627 FROM wip_cost_txn_interface wcti
628 WHERE wcti.group_id = p_wcti_group_id);
629 END IF;
630
631 /*------------------------------------------------------------------------+
632 | Delete any balance rows beyond the job's close date (accounting |
633 | period starting date > job close date) |
634 +------------------------------------------------------------------------*/
635 l_stmt_num := 80;
636
637 DELETE FROM WIP_PERIOD_BALANCES wpb
638 WHERE (wpb.acct_period_id,
639 wpb.wip_entity_id)
640 IN
641 (SELECT a.acct_period_id,
642 i.wip_entity_id
643 FROM wip_cost_txn_interface i,
644 org_acct_periods a
645 --{BUG#13072387
646 , org_acct_periods f
647 --}
648
649 WHERE i.group_id = p_wcti_group_id
650 --{
651 AND i.acct_period_id = f.acct_period_id
652 AND i.organization_id = f.organization_id
653 AND a.period_start_date > f.period_start_date
654 --AND a.acct_period_id > i.acct_period_id
655 --}
656 AND a.organization_id = i.organization_id);
657
658 /*------------------------------------------------------------------------+
659 | Copy rows from wip_cost_txn_interface to wip_transactions and |
660 | delete from wip_cost_txn_interface. |
661 +------------------------------------------------------------------------*/
662 l_stmt_num := 90;
663
664 l_err_num := CSTPWCPX.CMLCPX(p_wcti_group_id,
665 p_org_id,
666 6,
667 p_user_id,
668 p_login_id,
669 p_prg_appl_id,
670 p_prg_id,
671 p_req_id,
672 l_err_msg);
673
674 IF (l_err_num <> 0) THEN
675 l_msg_data := 'CSTPWCPX.CMLCPX: ' || l_err_msg;
676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
677 END IF;
678
679 /* Procedure level log message for Entry point */
680 IF (l_pLog) THEN
681 FND_LOG.STRING(
682 FND_LOG.LEVEL_PROCEDURE,
683 l_module || '.end',
684 'End of ' || l_full_name
685 );
686 END IF;
687
688 /* Get message count and if 1, return message data. */
689 FND_MSG_PUB.Count_And_Get
690 ( p_count => x_msg_count,
691 p_data => x_msg_data
692 );
693
694 /* Standard check of p_commit. */
695 IF FND_API.To_Boolean( p_commit ) THEN
696 COMMIT WORK;
697 END IF;
698
699 EXCEPTION
700 WHEN FND_API.G_EXC_ERROR THEN
701 ROLLBACK TO Calculate_Job_Variance_GRP;
702 x_return_status := FND_API.G_RET_STS_ERROR ;
703
704 IF l_errorLog THEN
705 FND_LOG.STRING(
706 FND_LOG.LEVEL_ERROR,
707 l_module || '.' || l_stmt_num,
708 l_msg_data
709 );
710 END IF;
711
712 /* Get message count and if 1, return message data. */
713 FND_MSG_PUB.Count_And_Get
714 ( p_count => x_msg_count,
715 p_data => x_msg_data
716 );
717
718 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
719 ROLLBACK TO Calculate_Job_Variance_GRP;
720 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
721
722 IF (l_exceptionLog) THEN
723 FND_LOG.STRING(
724 FND_LOG.LEVEL_EXCEPTION,
725 l_module || '.' || l_stmt_num,
726 l_msg_data
727 );
728 END IF;
729
730 FND_MSG_PUB.Count_And_Get
731 ( p_count => x_msg_count,
732 p_data => x_msg_data
733 );
734
735 WHEN OTHERS THEN
736 ROLLBACK TO Calculate_Job_Variance_GRP;
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738
739 IF (l_uLog) THEN
740 FND_LOG.STRING(
741 FND_LOG.LEVEL_UNEXPECTED,
742 l_module || '.' || l_stmt_num,
743 SQLERRM
744 );
745 END IF;
746
747 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
748 THEN
749 FND_MSG_PUB.Add_Exc_Msg
750 ( G_PKG_NAME,
751 l_api_name,
752 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 240)
753 );
754 END IF;
755 FND_MSG_PUB.Count_And_Get
756 ( p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759
760 END Calculate_Job_Variance;
761
762 END CST_JobCloseVar_GRP; /* end package body */