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