[Home] [Help]
PACKAGE BODY: APPS.CSTPWPVR
Source
1 PACKAGE BODY CSTPWPVR AS
2 /* $Header: CSTPWPVB.pls 120.4 2011/05/24 15:01:46 mpuranik ship $ */
3
4 FUNCTION REPVAR
5 (i_org_id IN NUMBER,
6 i_close_period_id IN NUMBER,
7 i_user_id IN NUMBER,
8 i_login_id IN NUMBER,
9 err_buf OUT NOCOPY VARCHAR2)
10 RETURN INTEGER
11 IS
12 cmlcpx_status EXCEPTION;
13 realloc_failed EXCEPTION;
14 l_status NUMBER;
15 l_group_id NUMBER;
16 l_eam_org VARCHAR2(1) := 'N';
17 l_repe_var_type NUMBER;
18 where_num NUMBER;
19 my_rowid ROWID;
20 /* Commented for bug 12402436
21 cursor c1 is
22 SELECT a2.ROWID the_rowid
23 FROM WIP_TRANSACTION_ACCOUNTS a2
24 , WIP_COST_TXN_INTERFACE i
25 WHERE i.group_id = l_group_id
26 AND i.transaction_id = a2.transaction_id
27 AND a2.base_transaction_value = 0;*/
28 l_msg_count NUMBER := 0;
29 l_msg_data VARCHAR2(8000);
30 l_return_status VARCHAR2(1);
31
32 BEGIN
33
34 /****************************************************************
35 * Obtain a group_id
36 ****************************************************************/
37 where_num := 50;
38 SELECT wip_transactions_s.nextval
39 INTO l_group_id
40 FROM dual;
41
42 /****************************************************************
43 * Obtain REPETITIVE_VARIANCE_TYPE
44 ****************************************************************/
45 where_num := 60;
46 BEGIN
47 SELECT REPETITIVE_VARIANCE_TYPE
48 INTO l_repe_var_type
49 FROM WIP_PARAMETERS
50 WHERE ORGANIZATION_ID = i_org_id;
51 EXCEPTION
52 WHEN NO_DATA_FOUND THEN NULL;
53 END;
54
55 /****************************************************************
56 * Insert header rows for each expense non-std job/eam job that is NOT
57 * closed.
58 ****************************************************************/
59 where_num := 100;
60 INSERT INTO wip_cost_txn_interface
61 (TRANSACTION_ID, LAST_UPDATE_DATE,
62 LAST_UPDATED_BY, CREATION_DATE,
63 CREATED_BY, LAST_UPDATE_LOGIN,
64 PROCESS_PHASE, PROCESS_STATUS,
65 ORGANIZATION_ID, WIP_ENTITY_ID,
66 ACCT_PERIOD_ID, TRANSACTION_TYPE,
67 TRANSACTION_DATE, GROUP_ID,
68 LINE_ID)
69 SELECT
70 wip_transactions_s.nextval, SYSDATE,
71 i_user_id, SYSDATE,
72 i_user_id, i_login_id,
73 2, 2,
74 i_org_id, b.wip_entity_id,
75 i_close_period_id, 5,
76 oa.schedule_close_date, l_group_id,
77 NULL
78 FROM wip_period_balances b,
79 org_acct_periods oa,
80 wip_discrete_jobs j
81 WHERE b.class_type IN (4,6) -- Bug #2357983.
82 /* modified for EAM (class_type = 6); modified for OSFM (class_type = 7) */
83 AND b.acct_period_id = i_close_period_id
84 AND j.wip_entity_id = b.wip_entity_id
85 AND b.organization_id = i_org_id
86 AND oa.organization_id = i_org_id
87 AND oa.acct_period_id = i_close_period_id
88 /* Added for bug 12402436 */
89 AND ((NVL(b.pl_material_out, 0)
90 -NVL(b.pl_material_in, 0)
91 +NVL(b.pl_material_var, 0)
92 +NVL(b.tl_material_out, 0)
93 +NVL(b.tl_material_var, 0)) <> 0
94 OR (NVL(b.pl_material_overhead_out, 0)
95 -NVL(b.pl_material_overhead_in, 0)
96 +NVL(b.pl_material_overhead_var, 0)
97 +NVL(b.tl_material_overhead_out,0)
98 +NVL(b.tl_material_overhead_var, 0)) <> 0
99 OR (NVL(b.pl_resource_out, 0)
100 -NVL(b.pl_resource_in, 0)
101 +NVL(b.pl_resource_var, 0)
102 +NVL(b.tl_resource_out, 0)
103 -NVL(b.tl_resource_in, 0)
104 +NVL(b.tl_resource_var, 0)) <> 0
105 OR ( NVL(b.pl_outside_processing_out, 0)
106 -NVL(b.pl_outside_processing_in, 0)
107 +NVL(b.pl_outside_processing_var, 0)
108 +NVL(b.tl_outside_processing_out, 0)
109 -NVL(b.tl_outside_processing_in, 0)
110 +NVL(b.tl_outside_processing_var, 0)) <> 0
111 OR ( NVL(b.pl_overhead_out, 0)
112 -NVL(b.pl_overhead_in, 0)
113 +NVL(b.pl_overhead_var, 0)
114 +NVL(b.tl_overhead_out, 0)
115 -NVL(b.tl_overhead_in, 0)
116 +NVL(b.tl_overhead_var, 0)) <> 0)
117 AND j.date_closed IS NULL;
118
119 /*-------------------------------------------------------------
120 | See whether there is any expense job
121 --------------------------------------------------------------*/
122
123 IF SQL%ROWCOUNT > 0 THEN
124
125 /**************************************************************
126 * Asset Route Re-distribution
127 * - Maintenance Work Orders
128 *************************************************************/
129
130 select nvl(eam_enabled_flag, 'N')
131 into l_eam_org
132 from mtl_parameters
133 where organization_id = i_org_id;
134
135 if (l_eam_org = 'Y') then
136 CST_eamCost_PUB.Redistribute_WIP_Accounts (
137 p_api_version => 1.0,
138 p_wcti_group_id => l_group_id,
139 p_user_id => i_user_id,
140 p_request_id => null,
141 p_prog_id => null,
142 p_prog_app_id => null,
143 p_login_id => i_login_id,
144 x_return_status => l_return_status,
145 x_msg_count => l_msg_count,
146 x_msg_data => l_msg_data);
147
148 if (l_return_status <> fnd_api.g_ret_sts_success) then
149 raise realloc_failed;
150 end if;
151 end if;
152
153 /***************************************************************
154 * Elemental variance for wip valuation for expense/eam jobs
155 **************************************************************/
156 where_num := 120;
157 INSERT INTO wip_transaction_accounts
158 (TRANSACTION_ID, REFERENCE_ACCOUNT,
159 LAST_UPDATE_DATE, LAST_UPDATED_BY,
160 CREATION_DATE, CREATED_BY,
161 LAST_UPDATE_LOGIN, ORGANIZATION_ID,
162 TRANSACTION_DATE, WIP_ENTITY_ID,
163 REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
164 TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
165 CONTRA_SET_ID, COST_ELEMENT_ID )
166 SELECT /*+ ORDERED INDEX(WPB WIP_PERIOD_BALANCES_N1) */
167 wcti.transaction_id,
168 decode(cce.cost_element_id,
169 1, wdj.material_account,
170 2, wdj.material_overhead_account,
171 3, wdj.resource_account,
172 4, wdj.outside_processing_account,
173 5, wdj.overhead_account) ,
174 SYSDATE, i_user_id, SYSDATE, i_user_id, i_login_id,
175 wpb.organization_id,
176 wcti.transaction_date,
177 wpb.wip_entity_id,
178 NULL,
179 7,
180 NULL,
181 SUM(decode(cce.cost_element_id,
182 1, ( NVL(wpb.pl_material_out, 0)
183 -NVL(wpb.pl_material_in, 0)
184 +NVL(wpb.pl_material_var, 0)
185 +NVL(wpb.tl_material_out, 0)
186 -0
187 +NVL(wpb.tl_material_var, 0)),
188 2, ( NVL(wpb.pl_material_overhead_out, 0)
189 -NVL(wpb.pl_material_overhead_in, 0)
190 +NVL(wpb.pl_material_overhead_var, 0)
191 +NVL(wpb.tl_material_overhead_out,0)
192 -0
193 +NVL(wpb.tl_material_overhead_var, 0)),
194 3, ( NVL(wpb.pl_resource_out, 0)
195 -NVL(wpb.pl_resource_in, 0)
196 +NVL(wpb.pl_resource_var, 0)
197 +NVL(wpb.tl_resource_out, 0)
198 -NVL(wpb.tl_resource_in, 0)
199 +NVL(wpb.tl_resource_var, 0)),
200 4, ( NVL(wpb.pl_outside_processing_out, 0)
201 -NVL(wpb.pl_outside_processing_in, 0)
202 +NVL(wpb.pl_outside_processing_var, 0)
203 +NVL(wpb.tl_outside_processing_out, 0)
204 -NVL(wpb.tl_outside_processing_in, 0)
205 +NVL(wpb.tl_outside_processing_var, 0)),
206 5, ( NVL(wpb.pl_overhead_out, 0)
207 -NVL(wpb.pl_overhead_in, 0)
208 +NVL(wpb.pl_overhead_var, 0)
209 +NVL(wpb.tl_overhead_out, 0)
210 -NVL(wpb.tl_overhead_in, 0)
211 +NVL(wpb.tl_overhead_var, 0)))),
212 wpb.wip_entity_id,
213 DECODE((max(cce.cost_element_id) - min(cce.cost_element_id)),
214 0, max(cce.cost_element_id), NULL)
215 from
216 wip_cost_txn_interface wcti,
217 wip_discrete_jobs wdj,
218 wip_period_balances wpb,
219 cst_cost_elements cce
220 WHERE wcti.group_id = l_group_id
221 AND wcti.line_id IS NULL
222 AND wdj.wip_entity_id = wcti.wip_entity_id
223 AND wdj.organization_id = wcti.organization_id
224 AND wpb.wip_entity_id = wdj.wip_entity_id
225 AND wpb.organization_id = wdj.organization_id
226 AND wpb.acct_period_id /*<*/ = wcti.acct_period_id
227 /* Removed this for bug 12402436 -> sum across all prior accounting periods */
228 group by
229 wcti.transaction_id, wcti.wip_entity_id, wcti.organization_id,
230 wpb.organization_id, wcti.transaction_date,
231 wpb.wip_entity_id,
232 decode(cce.cost_element_id,
233 1, wdj.material_account,
234 2, wdj.material_overhead_account,
235 3, wdj.resource_account,
236 4, wdj.outside_processing_account,
237 5, wdj.overhead_account);
238
239 /***************************************************************
240 * Single level variance to variance account for expense/eam jobs
241 **************************************************************/
242 where_num := 140;
243 INSERT INTO wip_transaction_accounts
244 (TRANSACTION_ID, REFERENCE_ACCOUNT,
245 LAST_UPDATE_DATE, LAST_UPDATED_BY,
246 CREATION_DATE, CREATED_BY,
247 LAST_UPDATE_LOGIN, ORGANIZATION_ID,
248 TRANSACTION_DATE, WIP_ENTITY_ID,
249 REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
250 TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
251 CONTRA_SET_ID, COST_ELEMENT_ID )
252 SELECT /*+ ORDERED INDEX(WPB WIP_PERIOD_BALANCES_N1) */
253 wcti.transaction_id,
254 decode(cce.cost_element_id,
255 1, wdj.material_variance_account,
256 3, wdj.resource_variance_account,
257 4, wdj.outside_proc_variance_account,
258 5, wdj.overhead_variance_account),
259 SYSDATE, i_user_id, SYSDATE, i_user_id, i_login_id,
260 wpb.organization_id,
261 wcti.transaction_date,
262 wpb.wip_entity_id,
263 NULL,
264 8,
265 NULL,
266 SUM(decode(cce.cost_element_id,
267 1, -1 * (NVL(wpb.pl_material_out,0)
268 - NVL(wpb.pl_material_in,0)
269 + NVL(wpb.pl_material_var,0)
270 + NVL(wpb.pl_material_overhead_out,0)
271 - NVL(wpb.pl_material_overhead_in,0)
272 + NVL(wpb.pl_material_overhead_var,0)
273 + NVL(wpb.pl_resource_out,0)
274 - NVL(wpb.pl_resource_in,0)
275 + NVL(wpb.pl_resource_var,0)
276 + NVL(wpb.pl_outside_processing_out,0)
277 - NVL(wpb.pl_outside_processing_in,0)
278 + NVL(wpb.pl_outside_processing_var,0)
279 + NVL(wpb.pl_overhead_out,0)
280 - NVL(wpb.pl_overhead_in,0)
281 + NVL(wpb.pl_overhead_var,0)
282 + NVL(wpb.tl_material_out,0)
283 - 0
284 + NVL(wpb.tl_material_var,0)
285 + NVL(wpb.tl_material_overhead_out,0)
286 - 0
287 + NVL(wpb.tl_material_overhead_var,0)),
288 3, -1 * (NVL(wpb.tl_resource_out,0)
289 - NVL(wpb.tl_resource_in,0)
290 + NVL(wpb.tl_resource_var,0)),
291 4, -1 * (NVL(wpb.tl_outside_processing_out,0)
292 - NVL(wpb.tl_outside_processing_in,0)
293 + NVL(wpb.tl_outside_processing_var,0)),
294 5, -1 * (NVL(wpb.tl_overhead_out,0)
295 - NVL(wpb.tl_overhead_in,0)
296 + NVL(wpb.tl_overhead_var,0)))),
297 wpb.wip_entity_id,
298 DECODE((max(cce.cost_element_id) - min(cce.cost_element_id)),
299 0, max(cce.cost_element_id), NULL)
300 from
301 wip_cost_txn_interface wcti,
302 wip_discrete_jobs wdj,
303 wip_period_balances wpb,
304 cst_cost_elements cce
305 WHERE wcti.group_id = l_group_id
306 AND wcti.line_id IS NULL
307 AND wdj.wip_entity_id = wcti.wip_entity_id
308 AND wdj.organization_id = wcti.organization_id
309 AND wpb.wip_entity_id = wdj.wip_entity_id
310 AND wpb.organization_id = wdj.organization_id
311 AND wpb.acct_period_id /*<*/ = wcti.acct_period_id
312 /* Removed this for bug 12402436 -> sum across all prior accounting periods */
313 and cce.cost_element_id <> 2
314 group by
315 wcti.transaction_id, wcti.wip_entity_id, wcti.organization_id,
316 wpb.organization_id, wcti.transaction_date,
317 wpb.class_type, wpb.wip_entity_id,
318 decode(cce.cost_element_id,
319 1, wdj.material_variance_account,
320 3, wdj.resource_variance_account,
321 4, wdj.outside_proc_variance_account,
322 5, wdj.overhead_variance_account);
323
324 /****************************************************************
325 * Update variance columns for expense/eam jobs
326 ****************************************************************/
327 where_num := 160;
328 /* Replaced with the query below for bug 12402436
329 UPDATE WIP_PERIOD_BALANCES wpb
330 SET (LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
331 PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
332 PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
333 PL_OVERHEAD_VAR, TL_MATERIAL_VAR,
334 TL_MATERIAL_OVERHEAD_VAR, TL_RESOURCE_VAR,
335 TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR ) =
336 (SELECT i_user_id, SYSDATE, i_login_id,
337 SUM( NVL(PL_MATERIAL_IN,0)
338 - NVL(PL_MATERIAL_OUT,0)
339 - decode(acct_period_id,i_close_period_id,0,NVL(PL_MATERIAL_VAR,0))),
340 SUM( NVL(PL_MATERIAL_OVERHEAD_IN,0)
341 - NVL(PL_MATERIAL_OVERHEAD_OUT,0)
342 - decode(acct_period_id,i_close_period_id,0,NVL(PL_MATERIAL_OVERHEAD_VAR,0))),
343 SUM( NVL(PL_RESOURCE_IN,0)
344 - NVL(PL_RESOURCE_OUT,0)
345 - decode(acct_period_id,i_close_period_id,0,NVL(PL_RESOURCE_VAR,0))),
346 SUM( NVL(PL_OUTSIDE_PROCESSING_IN,0)
347 - NVL(PL_OUTSIDE_PROCESSING_OUT,0)
348 - decode(acct_period_id,i_close_period_id,0,NVL(PL_OUTSIDE_PROCESSING_VAR,0))),
349 SUM( NVL(PL_OVERHEAD_IN,0)
350 - NVL(PL_OVERHEAD_OUT,0)
351 - decode(acct_period_id,i_close_period_id,0,NVL(PL_OVERHEAD_VAR,0))),
352 SUM( 0
353 - NVL(TL_MATERIAL_OUT,0)
354 - decode(acct_period_id,i_close_period_id,0,NVL(TL_MATERIAL_VAR,0))),
355 SUM( 0
356 - NVL(TL_MATERIAL_OVERHEAD_OUT,0)
357 - decode(acct_period_id,i_close_period_id,0,NVL(TL_MATERIAL_OVERHEAD_VAR,0))),
358 SUM( NVL(TL_RESOURCE_IN,0)
359 - NVL(TL_RESOURCE_OUT,0)
360 - decode(acct_period_id,i_close_period_id,0,NVL(TL_RESOURCE_VAR,0))),
361 SUM( NVL(TL_OUTSIDE_PROCESSING_IN,0)
362 - NVL(TL_OUTSIDE_PROCESSING_OUT,0)
363 - decode(acct_period_id,i_close_period_id,0,NVL(TL_OUTSIDE_PROCESSING_VAR,0))),
364 SUM( NVL(TL_OVERHEAD_IN,0)
365 - NVL(TL_OVERHEAD_OUT,0)
366 - decode(acct_period_id,i_close_period_id,0,NVL(TL_OVERHEAD_VAR,0)))
367 FROM WIP_PERIOD_BALANCES wpb2
368 WHERE wpb2.wip_entity_id = wpb.wip_entity_id
369 AND wpb2.acct_period_id <= wpb.acct_period_id)
370 /* sum across all periods */
371 /* WHERE (wpb.acct_period_id, wpb.wip_entity_id) IN
372 (SELECT i.acct_period_id, i.wip_entity_id
373 FROM WIP_COST_TXN_INTERFACE i
374 WHERE i.group_id = l_group_id
375 AND i.line_id IS NULL);*/
376
377 UPDATE WIP_PERIOD_BALANCES wpb
378 SET LAST_UPDATED_BY = i_user_id, LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = i_login_id,
379 PL_MATERIAL_VAR =
380 (NVL(PL_MATERIAL_IN,0)
381 - NVL(PL_MATERIAL_OUT,0)),
382 PL_MATERIAL_OVERHEAD_VAR =
383 (NVL(PL_MATERIAL_OVERHEAD_IN,0)
384 - NVL(PL_MATERIAL_OVERHEAD_OUT,0)),
385 PL_RESOURCE_VAR =
386 (NVL(PL_RESOURCE_IN,0)
387 - NVL(PL_RESOURCE_OUT,0)),
388 PL_OUTSIDE_PROCESSING_VAR =
389 (NVL(PL_OUTSIDE_PROCESSING_IN,0)
390 - NVL(PL_OUTSIDE_PROCESSING_OUT,0)),
391 PL_OVERHEAD_VAR =
392 (NVL(PL_OVERHEAD_IN,0)
393 - NVL(PL_OVERHEAD_OUT,0)),
394 TL_MATERIAL_VAR =
395 (0
396 - NVL(TL_MATERIAL_OUT,0)),
397 TL_MATERIAL_OVERHEAD_VAR =
398 (0
399 - NVL(TL_MATERIAL_OVERHEAD_OUT,0)),
400 TL_RESOURCE_VAR =
401 (NVL(TL_RESOURCE_IN,0)
402 - NVL(TL_RESOURCE_OUT,0)),
403 TL_OUTSIDE_PROCESSING_VAR =
404 (NVL(TL_OUTSIDE_PROCESSING_IN,0)
405 - NVL(TL_OUTSIDE_PROCESSING_OUT,0)),
406 TL_OVERHEAD_VAR =
407 (NVL(TL_OVERHEAD_IN,0)
408 - NVL(TL_OVERHEAD_OUT,0))
409 WHERE (wpb.acct_period_id, wpb.wip_entity_id) IN
410 (SELECT i.acct_period_id, i.wip_entity_id
411 FROM WIP_COST_TXN_INTERFACE i
412 WHERE i.group_id = l_group_id
413 AND i.line_id IS NULL);
414
415 END IF; /* end for expense jobs */
416
417 /****************************************************************
418 * Insert header rows for each schedule
419 ****************************************************************/
420 where_num := 200;
421 INSERT INTO wip_cost_txn_interface
422 (TRANSACTION_ID, LAST_UPDATE_DATE,
423 LAST_UPDATED_BY, CREATION_DATE,
424 CREATED_BY, LAST_UPDATE_LOGIN,
425 PROCESS_PHASE, PROCESS_STATUS,
426 ORGANIZATION_ID, WIP_ENTITY_ID,
427 ACCT_PERIOD_ID, TRANSACTION_TYPE,
428 TRANSACTION_DATE, GROUP_ID,
429 LINE_ID)
430 SELECT
431 wip_transactions_s.nextval, SYSDATE,
432 i_user_id, SYSDATE,
433 i_user_id, i_login_id,
434 2, 2,
435 i_org_id, wri.wip_entity_id,
436 i_close_period_id, 5,
437 oa.schedule_close_date, l_group_id,
438 wri.line_id
439 FROM wip_repetitive_items wri,
440 org_acct_periods oa
441 WHERE oa.organization_id = i_org_id
442 AND oa.acct_period_id = i_close_period_id
443 AND (wri.wip_entity_id, wri.line_id) IN
444 (SELECT s.wip_entity_id, s.line_id
445 FROM wip_period_balances b,
446 wip_repetitive_schedules s,
447 org_acct_periods a
448 WHERE b.acct_period_id = i_close_period_id
449 /* only if the schedule has a balance row in this period */
450 AND b.organization_id = i_org_id
451 AND b.class_type = 2
452 AND b.wip_entity_id = s.wip_entity_id
453 AND b.repetitive_schedule_id = s.repetitive_schedule_id
454 AND a.organization_id =i_org_id
455 AND a.acct_period_id = i_close_period_id
456 AND ( (l_repe_var_type = 1)
457 OR
458 (l_repe_var_type = 2
459 AND s.status_type IN (5, 7)
460 AND s.date_closed BETWEEN a.period_start_date
461 AND a.schedule_close_date+.99999)
462 )
463 );
464
465 /* Bug number 11660202. In the above sql +.99999 is added to inculde the jobs closed in the last day of
466 the period */
467 IF SQL%ROWCOUNT > 0 THEN
468
469 /***************************************************************
470 * Insert header rows for each schedule in to allocation
471 **************************************************************/
472 where_num := 210;
473 INSERT INTO wip_txn_allocations
474 (transaction_id, repetitive_schedule_id,
475 organization_id, last_update_date,
476 last_updated_by, creation_date,
477 created_by, last_update_login,
478 transaction_quantity, primary_quantity)
479 SELECT i.transaction_id,
480 b.repetitive_schedule_id,
481 b.organization_id,
482 SYSDATE, i_user_id, SYSDATE, i_user_id, i_login_id,
483 0, 0
484 FROM wip_cost_txn_interface i,
485 wip_period_balances b,
486 wip_repetitive_schedules s,
487 org_acct_periods a
488 WHERE i.group_id = l_group_id
489 AND i.line_id IS NOT NULL
490 AND S.WIP_ENTITY_ID = I.WIP_ENTITY_ID
491 AND s.line_id = i.line_id
492 AND i.wip_entity_id = b.wip_entity_id
493 AND i.acct_period_id = b.acct_period_id
494 AND a.organization_id = i_org_id
495 AND a.acct_period_id = i_close_period_id
496 /* only if the schedule exists in this period */
497 AND b.class_type = 2
498 AND b.repetitive_schedule_id = s.repetitive_schedule_id
499 AND ( (l_repe_var_type = 1)
500 OR
501 (l_repe_var_type = 2
502 AND s.status_type IN (5, 7)
503 AND s.date_closed BETWEEN a.period_start_date
504 AND a.schedule_close_date+.99999)
505 )
506 AND s.organization_id = i_org_id;
507 /* Bug number 11660202. In the above sql +.99999 is added to inculde the jobs closed in the last day of
508 the period */
509
510 /***************************************************************
511 * Elemental variance for wip valuation for schedules
512 **************************************************************/
513 where_num := 220;
514 INSERT INTO wip_transaction_accounts
515 (TRANSACTION_ID, REFERENCE_ACCOUNT,
516 LAST_UPDATE_DATE, LAST_UPDATED_BY,
517 CREATION_DATE, CREATED_BY,
518 LAST_UPDATE_LOGIN, ORGANIZATION_ID,
519 TRANSACTION_DATE, WIP_ENTITY_ID,
520 REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
521 TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
522 CONTRA_SET_ID, COST_ELEMENT_ID)
523 SELECT
524 wcti.transaction_id,
525 decode(cce.cost_element_id,
526 1, wrs.material_account,
527 2, wrs.material_overhead_account,
528 3, wrs.resource_account,
529 4, wrs.outside_processing_account,
530 5, wrs.overhead_account),
531 SYSDATE, i_user_id, SYSDATE, i_user_id, i_login_id,
532 wpb.organization_id,
533 wcti.transaction_date,
534 wpb.wip_entity_id,
535 wpb.repetitive_schedule_id,
536 7,
537 NULL,
538 SUM(decode(cce.cost_element_id,
539 1, ( NVL(wpb.pl_material_out, 0)
540 -NVL(wpb.pl_material_in, 0)
541 +NVL(wpb.pl_material_var, 0)
542 +NVL(wpb.tl_material_out, 0)
543 -0
544 +NVL(wpb.tl_material_var, 0)),
545 2, ( NVL(wpb.pl_material_overhead_out, 0)
546 -NVL(wpb.pl_material_overhead_in, 0)
547 +NVL(wpb.pl_material_overhead_var, 0)
548 +NVL(wpb.tl_material_overhead_out,0)
549 -0
550 +NVL(wpb.tl_material_overhead_var, 0)),
551 3, ( NVL(wpb.pl_resource_out, 0)
552 -NVL(wpb.pl_resource_in, 0)
553 +NVL(wpb.pl_resource_var, 0)
554 +NVL(wpb.tl_resource_out, 0)
555 -NVL(wpb.tl_resource_in, 0)
556 +NVL(wpb.tl_resource_var, 0)),
557 4, ( NVL(wpb.pl_outside_processing_out, 0)
558 -NVL(wpb.pl_outside_processing_in, 0)
559 +NVL(wpb.pl_outside_processing_var, 0)
560 +NVL(wpb.tl_outside_processing_out, 0)
561 -NVL(wpb.tl_outside_processing_in, 0)
562 +NVL(wpb.tl_outside_processing_var, 0)),
563 5, ( NVL(wpb.pl_overhead_out, 0)
564 -NVL(wpb.pl_overhead_in, 0)
565 +NVL(wpb.pl_overhead_var, 0)
566 +NVL(wpb.tl_overhead_out, 0)
567 -NVL(wpb.tl_overhead_in, 0)
568 +NVL(wpb.tl_overhead_var, 0)))),
569 wpb.repetitive_schedule_id,
570 DECODE((max(cce.cost_element_id) - min(cce.cost_element_id)),
571 0, max(cce.cost_element_id), NULL)
572 from
573 wip_cost_txn_interface wcti,
574 wip_txn_allocations alloc,
575 wip_period_balances wpb,
576 cst_cost_elements cce,
577 wip_repetitive_schedules wrs
578 WHERE wcti.group_id = l_group_id
579 AND wcti.line_id IS NOT NULL
580 AND WRS.WIP_ENTITY_ID = WCTI.WIP_ENTITY_ID
581 AND wrs.line_id = wcti.line_id
582 AND wcti.transaction_id = alloc.transaction_id
583 AND wcti.organization_id = alloc.organization_id
584 AND wcti.wip_entity_id = wpb.wip_entity_id
585 AND wcti.acct_period_id /*>*/ = wpb.acct_period_id
586 /* Removed this for bug 12402436 -> need to sum up across all prior acct periods */
587 AND alloc.repetitive_schedule_id = wpb.repetitive_schedule_id
588 and alloc.repetitive_schedule_id = wrs.repetitive_schedule_id
589 AND wrs.organization_id = alloc.organization_id
590 group by
591 wcti.transaction_id, wcti.wip_entity_id, wcti.organization_id,
592 wpb.organization_id, wcti.transaction_date, wcti.line_id,
593 wpb.class_type, wpb.wip_entity_id,
594 wpb.repetitive_schedule_id,
595 decode(cce.cost_element_id,
596 1, wrs.material_account,
597 2, wrs.material_overhead_account,
598 3, wrs.resource_account,
599 4, wrs.outside_processing_account,
600 5, wrs.overhead_account);
601
602 /***************************************************************
603 * Single level variance to variance account for schedules
604 **************************************************************/
605 where_num := 240;
606 INSERT INTO wip_transaction_accounts
607 (TRANSACTION_ID, REFERENCE_ACCOUNT,
608 LAST_UPDATE_DATE, LAST_UPDATED_BY,
609 CREATION_DATE, CREATED_BY,
610 LAST_UPDATE_LOGIN, ORGANIZATION_ID,
611 TRANSACTION_DATE, WIP_ENTITY_ID,
612 REPETITIVE_SCHEDULE_ID, ACCOUNTING_LINE_TYPE,
613 TRANSACTION_VALUE, BASE_TRANSACTION_VALUE,
614 CONTRA_SET_ID, COST_ELEMENT_ID )
615 SELECT
616 wcti.transaction_id,
617 decode(cce.cost_element_id,
618 1, wrs.material_variance_account,
619 3, wrs.resource_variance_account,
620 4, wrs.outside_proc_variance_account,
621 5, wrs.overhead_variance_account),
622 SYSDATE, i_user_id, SYSDATE, i_user_id, i_login_id,
623 wpb.organization_id,
624 wcti.transaction_date,
625 wpb.wip_entity_id,
626 wpb.repetitive_schedule_id,
627 8,
628 NULL,
629 SUM(decode(cce.cost_element_id,
630 1, -1 * (NVL(wpb.pl_material_out,0)
631 - NVL(wpb.pl_material_in,0)
632 + NVL(wpb.pl_material_var,0)
633 + NVL(wpb.pl_material_overhead_out,0)
634 - NVL(wpb.pl_material_overhead_in,0)
635 + NVL(wpb.pl_material_overhead_var,0)
636 + NVL(wpb.pl_resource_out,0)
637 - NVL(wpb.pl_resource_in,0)
638 + NVL(wpb.pl_resource_var,0)
639 + NVL(wpb.pl_outside_processing_out,0)
640 - NVL(wpb.pl_outside_processing_in,0)
641 + NVL(wpb.pl_outside_processing_var,0)
642 + NVL(wpb.pl_overhead_out,0)
643 - NVL(wpb.pl_overhead_in,0)
644 + NVL(wpb.pl_overhead_var,0)
645 + NVL(wpb.tl_material_out,0)
646 - 0
647 + NVL(wpb.tl_material_var,0)
648 + NVL(wpb.tl_material_overhead_out,0)
649 - 0
650 + NVL(wpb.tl_material_overhead_var,0)
651 ),
652 3, -1 * (NVL(wpb.tl_resource_out,0)
653 - NVL(wpb.tl_resource_in,0)
654 + NVL(wpb.tl_resource_var,0)),
655 4, -1 * (NVL(wpb.tl_outside_processing_out,0)
656 - NVL(wpb.tl_outside_processing_in,0)
657 + NVL(wpb.tl_outside_processing_var,0)),
658 5, -1 * (NVL(wpb.tl_overhead_out,0)
659 - NVL(wpb.tl_overhead_in,0)
660 + NVL(wpb.tl_overhead_var,0)))),
661 wpb.repetitive_schedule_id,
662 DECODE((max(cce.cost_element_id) - min(cce.cost_element_id)),
663 0, max(cce.cost_element_id), NULL)
664 from
665 wip_cost_txn_interface wcti,
666 wip_txn_allocations alloc,
667 wip_period_balances wpb,
668 cst_cost_elements cce,
669 wip_repetitive_schedules wrs
670 WHERE wcti.group_id = l_group_id
671 AND wcti.line_id IS NOT NULL
672 AND WRS.WIP_ENTITY_ID = WCTI.WIP_ENTITY_ID
673 AND wrs.line_id = wcti.line_id
674 AND wcti.transaction_id = alloc.transaction_id
675 AND wcti.organization_id = alloc.organization_id
676 AND wcti.wip_entity_id = wpb.wip_entity_id
677 AND wcti.acct_period_id /*>*/ = wpb.acct_period_id
678 /* Removed this for bug 12402436 -> need to sum up across all prior acct periods */
679 AND alloc.repetitive_schedule_id = wpb.repetitive_schedule_id
680 and alloc.repetitive_schedule_id = wrs.repetitive_schedule_id
681 AND wrs.organization_id = alloc.organization_id
682 AND cce.cost_element_id <> 2
683 group by
684 wcti.transaction_id, wcti.wip_entity_id, wcti.organization_id,
685 wpb.organization_id, wcti.transaction_date, wcti.line_id,
686 wpb.class_type, wpb.wip_entity_id,
687 wpb.repetitive_schedule_id,
688 decode(cce.cost_element_id,
689 1, wrs.material_variance_account,
690 3, wrs.resource_variance_account,
691 4, wrs.outside_proc_variance_account,
692 5, wrs.overhead_variance_account);
693
694 /*--------------------------------------------------------------------+
695 | Update variance columns for schedules
696 +---------------------------------------------------------------*/
697 where_num := 260;
698 /* Replaced with the query below for bug 12402436
699 UPDATE WIP_PERIOD_BALANCES wpb
700 SET (LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,
701 PL_MATERIAL_VAR, PL_MATERIAL_OVERHEAD_VAR,
702 PL_RESOURCE_VAR, PL_OUTSIDE_PROCESSING_VAR,
703 PL_OVERHEAD_VAR, TL_MATERIAL_VAR,
704 TL_MATERIAL_OVERHEAD_VAR, TL_RESOURCE_VAR,
705 TL_OUTSIDE_PROCESSING_VAR, TL_OVERHEAD_VAR ) =
706 (SELECT i_user_id, SYSDATE, i_login_id,
707 SUM( NVL(PL_MATERIAL_IN,0)
708 - NVL(PL_MATERIAL_OUT,0)
709 - decode(acct_period_id,i_close_period_id,0,NVL(PL_MATERIAL_VAR,0))),
710 SUM( NVL(PL_MATERIAL_OVERHEAD_IN,0)
711 - NVL(PL_MATERIAL_OVERHEAD_OUT,0)
712 - decode(acct_period_id,i_close_period_id,0,NVL(PL_MATERIAL_OVERHEAD_VAR,0))),
713 SUM( NVL(PL_RESOURCE_IN,0)
714 - NVL(PL_RESOURCE_OUT,0)
715 - decode(acct_period_id,i_close_period_id,0,NVL(PL_RESOURCE_VAR,0))),
716 SUM( NVL(PL_OUTSIDE_PROCESSING_IN,0)
717 - NVL(PL_OUTSIDE_PROCESSING_OUT,0)
718 - decode(acct_period_id,i_close_period_id,0,NVL(PL_OUTSIDE_PROCESSING_VAR,0))),
719 SUM( NVL(PL_OVERHEAD_IN,0)
720 - NVL(PL_OVERHEAD_OUT,0)
721 - decode(acct_period_id,i_close_period_id,0,NVL(PL_OVERHEAD_VAR,0))),
722 SUM( 0
723 - NVL(TL_MATERIAL_OUT,0)
724 - decode(acct_period_id,i_close_period_id,0,NVL(TL_MATERIAL_VAR,0))),
725 SUM( 0
726 - NVL(TL_MATERIAL_OVERHEAD_OUT,0)
727 - decode(acct_period_id,i_close_period_id,0,NVL(TL_MATERIAL_OVERHEAD_VAR,0))),
728 SUM( NVL(TL_RESOURCE_IN,0)
729 - NVL(TL_RESOURCE_OUT,0)
730 - decode(acct_period_id,i_close_period_id,0,NVL(TL_RESOURCE_VAR,0))),
731 SUM( NVL(TL_OUTSIDE_PROCESSING_IN,0)
732 - NVL(TL_OUTSIDE_PROCESSING_OUT,0)
733 - decode(acct_period_id,i_close_period_id,0,NVL(TL_OUTSIDE_PROCESSING_VAR,0))),
734 SUM( NVL(TL_OVERHEAD_IN,0)
735 - NVL(TL_OVERHEAD_OUT,0)
736 - decode(acct_period_id,i_close_period_id,0,NVL(TL_OVERHEAD_VAR,0)))
737 FROM WIP_PERIOD_BALANCES wpb2
738 WHERE wpb2.wip_entity_id = wpb.wip_entity_id
739 AND wpb2.acct_period_id <= wpb.acct_period_id
740 /* sum across all acct periods */
741 /*AND wpb2.organization_id = wpb.organization_id
742 AND wpb2.repetitive_schedule_id = wpb.repetitive_schedule_id)
743 WHERE wpb.acct_period_id = i_close_period_id
744 AND wpb.organization_id = i_org_id
745 AND (wpb.wip_entity_id, wpb.repetitive_schedule_id) IN
746 (SELECT i.wip_entity_id,
747 alloc.repetitive_schedule_id
748 FROM WIP_COST_TXN_INTERFACE i,
749 WIP_TXN_ALLOCATIONS alloc
750 WHERE i.group_id = l_group_id
751 AND i.transaction_id = alloc.transaction_id
752 AND i.line_id IS NOT NULL);*/
753
754 UPDATE WIP_PERIOD_BALANCES wpb
755 SET LAST_UPDATED_BY = i_user_id, LAST_UPDATE_DATE = SYSDATE, LAST_UPDATE_LOGIN = i_login_id,
756 PL_MATERIAL_VAR =
757 (NVL(PL_MATERIAL_IN,0)
758 - NVL(PL_MATERIAL_OUT,0)),
759 PL_MATERIAL_OVERHEAD_VAR =
760 (NVL(PL_MATERIAL_OVERHEAD_IN,0)
761 - NVL(PL_MATERIAL_OVERHEAD_OUT,0)),
762 PL_RESOURCE_VAR =
763 (NVL(PL_RESOURCE_IN,0)
764 - NVL(PL_RESOURCE_OUT,0)),
765 PL_OUTSIDE_PROCESSING_VAR =
766 (NVL(PL_OUTSIDE_PROCESSING_IN,0)
767 - NVL(PL_OUTSIDE_PROCESSING_OUT,0)),
768 PL_OVERHEAD_VAR =
769 (NVL(PL_OVERHEAD_IN,0)
770 - NVL(PL_OVERHEAD_OUT,0)),
771 TL_MATERIAL_VAR =
772 (0
773 - NVL(TL_MATERIAL_OUT,0)),
774 TL_MATERIAL_OVERHEAD_VAR =
775 (0
776 - NVL(TL_MATERIAL_OVERHEAD_OUT,0)),
777 TL_RESOURCE_VAR =
778 (NVL(TL_RESOURCE_IN,0)
779 - NVL(TL_RESOURCE_OUT,0)),
780 TL_OUTSIDE_PROCESSING_VAR =
781 (NVL(TL_OUTSIDE_PROCESSING_IN,0)
782 - NVL(TL_OUTSIDE_PROCESSING_OUT,0)),
783 TL_OVERHEAD_VAR =
784 (NVL(TL_OVERHEAD_IN,0)
785 - NVL(TL_OVERHEAD_OUT,0))
786 WHERE wpb.acct_period_id = i_close_period_id
787 AND wpb.organization_id = i_org_id
788 AND (wpb.wip_entity_id, wpb.repetitive_schedule_id) IN
789 (SELECT i.wip_entity_id,
790 alloc.repetitive_schedule_id
791 FROM WIP_COST_TXN_INTERFACE i,
792 WIP_TXN_ALLOCATIONS alloc
793 WHERE i.group_id = l_group_id
794 AND i.transaction_id = alloc.transaction_id
795 AND i.line_id IS NOT NULL);
796
797 END IF; /* end of schedules */
798
799 /***************************************************************
800 * Delete any 0 value accounting rows
801 * Note :
802 * has to use cursor because of the PL/SQL limitation on rowid
803 **************************************************************/
804
805 /* Commented for bug 12402436 - where_num := 550;
806 OPEN c1;
807 LOOP
808 FETCH c1 into my_rowid;
809 EXIT WHEN c1%NOTFOUND;
810 DELETE FROM WIP_TRANSACTION_ACCOUNTS
811 WHERE ROWID = my_rowid;
812 END LOOP;
813 CLOSE c1;*/
814
815
816 where_num := 560;
817
818 /* Update WTA with WIP_SUB_LEDGER_ID */
819 UPDATE WIP_TRANSACTION_ACCOUNTS
820 SET WIP_SUB_LEDGER_ID = CST_WIP_SUB_LEDGER_ID_S.NEXTVAL
821 WHERE TRANSACTION_ID IN
822 ( SELECT TRANSACTION_ID
823 FROM WIP_COST_TXN_INTERFACE
824 WHERE GROUP_ID = l_group_id
825 AND ORGANIZATION_ID = i_org_id );
826
827
828 where_num := 570;
829
830 /* Create the Events for the transactions in the WCTI group */
831
832 CST_XLA_PVT.CreateBulk_WIPXLAEvent(
833 p_api_version => 1.0,
834 p_init_msg_list => FND_API.G_FALSE,
835 p_commit => FND_API.G_FALSE,
836 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
837 x_return_status => l_return_status,
838 x_msg_count => l_msg_count,
839 x_msg_data => l_msg_data,
840 p_wcti_group_id => l_group_id,
841 p_organization_id => i_org_id );
842
843 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
845 END IF;
846
847
848
849 /***************************************************************
850 * Delete any balance rows for schedule beyond the cancel or
851 * completion date
852 **************************************************************/
853 where_num := 600;
854
855 DELETE FROM wip_period_balances wpb
856 WHERE wpb.acct_period_id > i_close_period_id
857 AND wpb.organization_id = i_org_id
858 AND (wpb.wip_entity_id, wpb.repetitive_schedule_id) IN
859 (SELECT i.wip_entity_id,
860 alloc.repetitive_schedule_id
861 FROM WIP_COST_TXN_INTERFACE i,
862 WIP_TXN_ALLOCATIONS alloc,
863 WIP_REPETITIVE_SCHEDULES s,
864 ORG_ACCT_PERIODS oap
865 WHERE i.group_id = l_group_id
866 AND i.line_id IS NOT NULL
867 AND i.transaction_id = alloc.transaction_id
868 AND alloc.repetitive_schedule_id = s.repetitive_schedule_id
869 AND s.organization_id = i_org_id
870 AND oap.acct_period_id = i_close_period_id
871 --AND s.date_closed IS NOT NULL)
872 AND s.date_closed between oap.period_start_date and
873 oap.schedule_close_date
874 AND oap.organization_id = i_org_id)
875 ;
876
877 /*---------------------------------------------------------------+
878 | Copy rows from wip_cost_txn_interface to wip_transactions
879 | and delete from wip_cost_txn_interface
880 +---------------------------------------------------------------*/
881 l_status := CSTPWCPX.CMLCPX(l_group_id,i_org_id,5,i_user_id,i_login_id,-1,-1,-1,err_buf);
882 IF l_status <> 0 THEN
883 RAISE cmlcpx_status;
884 END IF;
885
886 RETURN(0);
887
888 EXCEPTION
889 WHEN cmlcpx_status THEN
890 ROLLBACK;
891 RETURN(l_status);
892
893 WHEN realloc_failed THEN
894 ROLLBACK;
895 err_buf := 'CSTPWPVR: Failed to redistribute Asset Route';
896 RETURN(l_status);
897
898 WHEN OTHERS THEN
899 ROLLBACK;
900 err_buf := 'CSTPWPVR:' || to_char(where_num) || substr(SQLERRM,1,150);
901 RETURN(SQLCODE);
902
903 END REPVAR;
904
905 END CSTPWPVR;