[Home] [Help]
PACKAGE BODY: APPS.CSTPPWMX
Source
1 PACKAGE BODY CSTPPWMX AS
2 /* $Header: CSTPPWMB.pls 120.1 2008/01/11 23:11:26 ipineda ship $ */
3
4 /*==================================================================*/
5 /* function for WIP material issue */
6 /*==================================================================*/
7
8 FUNCTION issue (
9 i_cost_type_id IN NUMBER,
10 i_txn_id IN NUMBER,
11 i_org_id IN NUMBER,
12 i_period_id IN NUMBER,
13 i_item_id IN NUMBER,
14 i_txn_qty IN NUMBER,
15 i_entity_id IN NUMBER,
16 i_entity_type IN NUMBER,
17 i_user_id IN NUMBER,
18 i_login_id IN NUMBER,
19 i_prg_appl_id IN NUMBER,
20 i_prg_id IN NUMBER,
21 i_req_id IN NUMBER)
22 RETURN integer
23 IS
24 l_round_unit NUMBER;
25 l_precision NUMBER;
26 l_ext_precision NUMBER;
27
28 /* EAM Acct Enh Project */
29 l_debug VARCHAR(80);
30 l_zero_cost_flag NUMBER := -1;
31 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
32 l_msg_count NUMBER := 0;
33 l_msg_data VARCHAR2(8000) := '';
34 l_api_message VARCHAR2(8000);
35 /* Added for bug6709905 */
36 CURSOR wpb_rep_sch IS
37 SELECT wpb.pl_material_in
38 FROM wip_period_balances wpb
39 WHERE wpb.acct_period_id = i_period_id
40 AND wpb.organization_id = i_org_id
41 AND wpb.wip_entity_id = i_entity_id
42 AND wpb.repetitive_schedule_id in
43 (SELECT mmta.repetitive_schedule_id
44 FROM mtl_material_txn_allocations mmta
45 WHERE mmta.transaction_id = i_txn_id)
46 AND EXISTS
47 (SELECT 'Check if the item has cost'
48 FROM cst_item_costs cic, mtl_parameters mp
49 WHERE cic.inventory_item_id = i_item_id
50 AND mp.organization_id = i_org_id
51 AND cic.organization_id = mp.cost_organization_id
52 AND cic.cost_type_id = i_cost_type_id)
53 FOR UPDATE OF pl_material_in;
54
55 BEGIN
56
57 /* Obtain round unit */
58
59 CSTPUTIL.CSTPUGCI (i_org_id, l_round_unit, l_precision, l_ext_precision);
60
61 /* Update WIP_PERIOD_BALANCES */
62
63 IF i_entity_type <> 2 THEN
64 /* EAM Acct Enh Project */
65 CST_Utility_PUB.get_zeroCostIssue_flag (
66 p_api_version => 1.0,
67 x_return_status => l_return_status,
68 x_msg_count => l_msg_count,
69 x_msg_data => l_msg_data,
70 p_txn_id => i_txn_id,
71 x_zero_cost_flag => l_zero_cost_flag
72 );
73
74 if (l_return_status <> fnd_api.g_ret_sts_success) then
75 FND_FILE.put_line(FND_FILE.log, l_msg_data);
76 l_api_message := 'get_zeroCostIssue_flag returned unexpected error';
77 FND_MESSAGE.set_name('BOM','CST_API_MESSAGE');
78 FND_MESSAGE.set_token('TEXT', l_api_message);
79 FND_MSG_pub.add;
80 raise fnd_api.g_exc_unexpected_error;
81 end if;
82
83 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
84
85 if (l_debug = 'Y') then
86 FND_FILE.PUT_LINE(FND_FILE.LOG, 'zero_cost_flag: '||to_char(l_zero_cost_flag));
87 end if;
88
89 if (l_zero_cost_flag = 1) then
90
91 /* update wip_period_balances WHO columns */
92 UPDATE wip_period_balances b
93 SET LAST_UPDATE_DATE = sysdate,
94 LAST_UPDATED_BY = i_user_id,
95 LAST_UPDATE_LOGIN = i_login_id,
96 REQUEST_ID = DECODE(i_req_id, -1, NULL, i_req_id),
97 PROGRAM_APPLICATION_ID = DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
98 PROGRAM_ID = DECODE(i_prg_id, -1, NULL, i_prg_id),
99 PROGRAM_UPDATE_DATE = DECODE(i_req_id, -1, NULL, SYSDATE)
100 WHERE acct_period_id = i_period_id
101 AND organization_id = i_org_id
102 AND wip_entity_id = i_entity_id;
103 else
104
105 UPDATE wip_period_balances b
106 SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
107 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
108 pl_material_in,
109 pl_material_overhead_in,
110 pl_resource_in,
111 pl_outside_processing_in,
112 pl_overhead_in )=
113 (SELECT
114 SYSDATE, i_user_id, i_login_id,
115 DECODE(i_req_id, -1, NULL, i_req_id),
116 DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
117 DECODE(i_prg_id, -1, NULL, i_prg_id),
118 DECODE(i_req_id, -1, NULL, SYSDATE),
119 nvl(b.pl_material_in,0) +
120 (ROUND((NVL(material_cost,0) * -1 * i_txn_qty) /
121 l_round_unit) * l_round_unit),
122 nvl(b.pl_material_overhead_in,0) +
123 (ROUND((NVL(material_overhead_cost,0) * -1 * i_txn_qty) /
124 l_round_unit) * l_round_unit),
125 nvl(b.pl_resource_in,0) +
126 (ROUND((NVL(resource_cost,0) * -1 * i_txn_qty) /
127 l_round_unit) * l_round_unit),
128 nvl(b.pl_outside_processing_in,0)+
129 (ROUND((NVL(outside_processing_cost,0) * -1 * i_txn_qty) /
130 l_round_unit) * l_round_unit),
131 nvl(b.pl_overhead_in,0) +
132 (ROUND((NVL(overhead_cost,0) * -1 * i_txn_qty) /
133 l_round_unit) * l_round_unit)
134 FROM cst_item_costs cic, mtl_parameters mp
135 WHERE cic.inventory_item_id = i_item_id
136 AND cic.organization_id = mp.cost_organization_id
137 AND mp.organization_id = i_org_id
138 AND cic.cost_type_id = i_cost_type_id
139 )
140 WHERE acct_period_id = i_period_id
141 AND organization_id = i_org_id
142 AND wip_entity_id = i_entity_id
143 AND EXISTS
144 (SELECT 'Check if the item has cost'
145 FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
146 WHERE CIC.INVENTORY_ITEM_ID = i_item_id
147 AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
148 AND MP.ORGANIZATION_ID = i_org_id
149 AND CIC.COST_TYPE_ID = i_cost_type_id);
150 end if; /* l_zero_cost_flag */
151 ELSE
152
153 /* i_entity_type = 2 */
154 /* bug 6709905, introduced loop to cursor to prevent deadlock
155 scenario*/
156 FOR wpb_rec IN wpb_rep_sch LOOP
157 UPDATE wip_period_balances b
158 SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
159 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
160 pl_material_in,
161 pl_material_overhead_in,
162 pl_resource_in,
163 pl_outside_processing_in,
164 pl_overhead_in) =
165 (SELECT
166 SYSDATE, i_user_id, i_login_id,
167 DECODE(i_req_id, -1, NULL, i_req_id),
168 DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
169 DECODE(i_prg_id, -1, NULL, i_prg_id),
170 DECODE(i_req_id, -1, NULL, SYSDATE),
171 nvl(b.pl_material_in,0) +
172 ROUND((NVL(material_cost,0) * -1 * alloc.primary_quantity)/
173 l_round_unit) * l_round_unit,
174 nvl(b.pl_material_overhead_in,0) +
175 ROUND((NVL(material_overhead_cost,0) * -1 *
176 alloc.primary_quantity)/l_round_unit) * l_round_unit,
177 nvl(b.pl_resource_in,0) +
178 ROUND((NVL(resource_cost,0) * -1 * alloc.primary_quantity)/
179 l_round_unit) * l_round_unit,
180 nvl(b.pl_outside_processing_in,0)+
181 ROUND((NVL(outside_processing_cost,0) * -1 *
182 alloc.primary_quantity)/l_round_unit)* l_round_unit,
183 nvl(b.pl_overhead_in,0) +
184 ROUND((NVL(overhead_cost,0) * -1 * alloc.primary_quantity)/
185 l_round_unit) * l_round_unit
186 FROM cst_item_costs cic,
187 mtl_parameters mp,
188 mtl_material_txn_allocations alloc
189 WHERE NVL(alloc.repetitive_schedule_id, -99) =
190 NVL(b.repetitive_schedule_id, -99)
191 AND alloc.transaction_id = i_txn_id
192 AND cic.inventory_item_id = i_item_id
193 AND cic.organization_id = mp.cost_organization_id
194 AND mp.organization_id = i_org_id
195 AND cic.cost_type_id = 1
196 )
197 WHERE CURRENT OF wpb_rep_sch;
198 /* bug 6709905
199 WHERE acct_period_id = i_period_id
200 AND organization_id = i_org_id
201 AND wip_entity_id = i_entity_id
202 AND repetitive_schedule_id in
203 (SELECT repetitive_schedule_id
204 FROM mtl_material_txn_allocations
205 WHERE transaction_id = i_txn_id)
206 AND EXISTS
207 (SELECT 'Check if the item has cost'
208 FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
209 WHERE INVENTORY_ITEM_ID = i_item_id
210 AND MP.ORGANIZATION_ID = i_org_id
211 AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
212 AND COST_TYPE_ID = i_cost_type_id);*/
213 END LOOP;
214
215
216 END IF;
217
218 IF SQL%ROWCOUNT > 0 THEN
219 return (1);
220 ELSE
221 return (-999);
222 END IF;
223
224 /* exception handlers */
225 EXCEPTION
226 WHEN OTHERS THEN
227 raise_application_error(-20001, SQLERRM);
228 return (0);
229 END issue;
230
231 /*==================================================================*/
232 /* function for WIP completion */
233 /*==================================================================*/
234
235 FUNCTION complete (
236 i_cost_type_id IN NUMBER,
237 i_txn_id IN NUMBER,
238 i_org_id IN NUMBER,
239 i_period_id IN NUMBER,
240 i_item_id IN NUMBER,
241 i_txn_qty IN NUMBEr,
242 i_entity_id IN NUMBER,
243 i_entity_type IN NUMBER,
244 i_user_id IN NUMBER,
245 i_login_id IN NUMBER,
246 i_prg_appl_id IN NUMBER,
247 i_prg_id IN NUMBER,
248 i_req_id IN NUMBER)
249 RETURN integer
250 IS
251 l_round_unit NUMBER;
252 l_precision NUMBER;
253 l_ext_precision NUMBER;
254 x_realoc_yld_cost NUMBER;
255 x_op_yield_cost NUMBER := 0;
256 x_pl_mat_yld_cost NUMBER := 0;
257 x_tl_mat_yld_cost NUMBER := 0;
258 x_pl_mat_ovhd_yld_cost NUMBER := 0;
259 x_tl_mat_ovhd_yld_cost NUMBER := 0;
260 x_pl_osp_yld_cost NUMBER := 0;
261 x_tl_osp_yld_cost NUMBER := 0;
262 x_pl_res_yld_cost NUMBER := 0;
263 x_tl_res_yld_cost NUMBER := 0;
264 x_pl_ovhd_yld_cost NUMBER := 0;
265 x_tl_ovhd_yld_cost NUMBER := 0;
266
267 /* Changes for Optional Scrap */
268 x_est_scrap_acct_flag NUMBER := 0;
269 l_err_num NUMBER := 0;
270 l_err_msg VARCHAR2(240) := '';
271 WSM_ESA_PKG_ERROR EXCEPTION;
272
273
274 CURSOR c_op_yld_cost IS
275 SELECT SUM(NVL(yielded_cost, 0)) yielded_cost,
276 cost_element_id,
277 level_type
278 FROM cst_item_cost_details
279 WHERE inventory_item_id = i_item_id
280 AND organization_id = i_org_id
281 AND cost_type_id = 1
282
283 GROUP BY cost_element_id, level_type;
284
285 /* Added for bug6709905*/
286 CURSOR wpb_rep_sch IS
287 SELECT wpb.pl_material_out
288 FROM wip_period_balances wpb
289 WHERE wpb.acct_period_id = i_period_id
290 AND wpb.organization_id = i_org_id
291 AND wpb.wip_entity_id = i_entity_id
292 AND wpb.repetitive_schedule_id in
293 (SELECT mmta.repetitive_schedule_id
294 FROM mtl_material_txn_allocations mmta
295 WHERE mmta.transaction_id = i_txn_id)
296 AND EXISTS
297 (SELECT 'Check if the item has cost'
298 FROM cst_item_costs cic, mtl_parameters mp
299 WHERE cic.inventory_item_id = i_item_id
300 AND mp.organization_id = i_org_id
301 AND cic.organization_id = mp.cost_organization_id
302 AND cic.cost_type_id = i_cost_type_id)
303 FOR UPDATE OF pl_material_out;
304
305 BEGIN
306 /* Obtain round unit */
307
308 CSTPUTIL.CSTPUGCI (i_org_id, l_round_unit, l_precision, l_ext_precision);
309
310 /* Update TL, PL (OUT) costs to WIP_PERIOD_BALANCES for the assembly */
311 /* Separte SQL statement for job and schedule for faster performance */
312 /* Complete into INV take positive quantity */
313
314 IF i_entity_type <> 2 THEN /* discrete job */
315 /*----------------------------------------------
316 | Check if reallocation of operation yield cost |
317 | is to be done. This was added by Sujit Dalai |
318 ------------------------------------------------- */
319
320 SELECT DECODE (entity_type, 5, 1, 0)
321 INTO x_realoc_yld_cost
322 FROM wip_entities
323 WHERE wip_entity_id = i_entity_id
324 AND organization_id = i_org_id ;
325
326 /* Changes for Optional Scrap */
327 IF x_realoc_yld_cost = 1 THEN
328 x_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(i_entity_id,
329 l_err_num,
330 l_err_msg);
331
332 IF (x_est_scrap_acct_flag = 0) THEN
333 RAISE WSM_ESA_PKG_ERROR;
334 END IF;
335
336 IF(x_est_scrap_acct_flag <> 1) THEN
337 x_realoc_yld_cost := 0;
338 END IF;
339 END IF;
340
341 /*---------------------------------------------------
342 | If reallocation is to be done then get elemental |
343 | yielded cost. This was added by Sujit Dalai |
344 ------------------------------------------------- */
345
346 IF (x_realoc_yld_cost = 1) THEN
347
348 FOR rec_op_yld_cost in c_op_yld_cost LOOP
349
350 IF (rec_op_yld_cost.cost_element_id = 1 AND
351 rec_op_yld_cost.level_type = 1) THEN
352
353 x_tl_mat_yld_cost := rec_op_yld_cost.yielded_cost;
354 ELSIF (rec_op_yld_cost.cost_element_id = 1 AND
355 rec_op_yld_cost.level_type = 2) THEN
356
357 x_pl_mat_yld_cost := rec_op_yld_cost.yielded_cost;
358
359 ELSIF (rec_op_yld_cost.cost_element_id = 2 AND
360 rec_op_yld_cost.level_type = 1) THEN
361
365 rec_op_yld_cost.level_type = 2) THEN
362 x_tl_mat_ovhd_yld_cost := rec_op_yld_cost.yielded_cost;
363
364 ELSIF (rec_op_yld_cost.cost_element_id = 2 AND
366
367 x_pl_mat_ovhd_yld_cost := rec_op_yld_cost.yielded_cost;
368 ELSIF (rec_op_yld_cost.cost_element_id = 3 AND
369 rec_op_yld_cost.level_type = 1) THEN
370
371 x_tl_res_yld_cost := rec_op_yld_cost.yielded_cost;
372
373 ELSIF (rec_op_yld_cost.cost_element_id = 3 AND
374 rec_op_yld_cost.level_type = 2) THEN
375
376 x_pl_res_yld_cost := rec_op_yld_cost.yielded_cost;
377
378 ELSIF (rec_op_yld_cost.cost_element_id = 4 AND
379 rec_op_yld_cost.level_type = 1) THEN
380
381 x_tl_osp_yld_cost := rec_op_yld_cost.yielded_cost;
382
383 ELSIF (rec_op_yld_cost.cost_element_id = 4 AND
384 rec_op_yld_cost.level_type = 2) THEN
385
386 x_pl_osp_yld_cost := rec_op_yld_cost.yielded_cost;
387
388 ELSIF (rec_op_yld_cost.cost_element_id = 5 AND
389 rec_op_yld_cost.level_type = 1) THEN
390
391 x_tl_ovhd_yld_cost := rec_op_yld_cost.yielded_cost;
392
393 ELSE
394
395 x_pl_ovhd_yld_cost := rec_op_yld_cost.yielded_cost;
396
397 END IF;
398
399 x_op_yield_cost := x_op_yield_cost + rec_op_yld_cost.yielded_cost;
400
401 END LOOP;
402
403 END IF;
404
405
406 UPDATE wip_period_balances b
407 SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
408 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
409 pl_material_out,
410 pl_material_overhead_out,
411 pl_resource_out,
412 pl_outside_processing_out,
413 pl_overhead_out,
414 tl_material_out,
415 tl_material_overhead_out,
416 tl_resource_out,
417 tl_outside_processing_out,
418 tl_overhead_out,
419 tl_scrap_out) =
420 (SELECT
421 SYSDATE, i_user_id, i_login_id,
422 DECODE(i_req_id, -1, NULL, i_req_id),
423 DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
424 DECODE(i_prg_id, -1, NULL, i_prg_id),
425 DECODE(i_req_id, -1, NULL, SYSDATE),
426 nvl(b.pl_material_out,0) +
427 ROUND(((NVL(pl_material,0) - x_pl_mat_yld_cost) * i_txn_qty)/l_round_unit)*
428 l_round_unit,
429 nvl(b.pl_material_overhead_out,0) +
430 ROUND(((NVL(pl_material_overhead,0) - x_pl_mat_ovhd_yld_cost) * i_txn_qty)/l_round_unit)*
431 l_round_unit,
432 nvl(b.pl_resource_out,0) +
433 ROUND(((NVL(pl_resource,0) - x_pl_res_yld_cost)* i_txn_qty)/l_round_unit)*
434 l_round_unit,
435 nvl(b.pl_outside_processing_out,0) +
436 ROUND(((NVL(pl_outside_processing,0) - x_pl_osp_yld_cost) * i_txn_qty)/l_round_unit)*
437 l_round_unit,
438 nvl(b.pl_overhead_out,0) +
439 ROUND(((NVL(pl_overhead,0) - x_pl_ovhd_yld_cost) * i_txn_qty)/l_round_unit)*
440 l_round_unit,
441 nvl(b.tl_material_out,0)+
442 ROUND(((NVL(tl_material,0) - x_tl_mat_yld_cost) * i_txn_qty)/l_round_unit)*
443 l_round_unit,
444 nvl(b.tl_material_overhead_out,0)+
445 /* code change for bug 2090740 - decode modified for osfm jobs(class_type = 5) */
446 /* if standard job or repetitive or osfm job, do not credit the job
447 tl matl ovhd ;
448 if non-std job, credit the job tl matl ovhd */
449 DECODE(b.class_type, 1, 0, 2, 0, 5, 0,
450 ROUND(((NVL(tl_material_overhead,0) - x_tl_mat_ovhd_yld_cost) * i_txn_qty)/
451 l_round_unit)* l_round_unit),
452 nvl(b.tl_resource_out,0) +
453 ROUND(((NVL(tl_resource,0) - x_tl_res_yld_cost) * i_txn_qty)/l_round_unit)*
454 l_round_unit,
455 nvl(b.tl_outside_processing_out,0) +
456 ROUND(((NVL(tl_outside_processing,0) - x_tl_osp_yld_cost) * i_txn_qty)/l_round_unit)*
457 l_round_unit,
458 nvl(b.tl_overhead_out,0) +
459 ROUND(((NVL(tl_overhead,0) - x_tl_ovhd_yld_cost) * i_txn_qty)/l_round_unit)*
460 l_round_unit,
461 nvl(b.tl_scrap_out,0) +
462 DECODE(b.class_type, 5 , ROUND(((x_op_yield_cost - x_tl_mat_ovhd_yld_cost) * i_txn_qty)
463 /l_round_unit)*l_round_unit,
464 0)
465
466
467 FROM cst_item_costs cic , mtl_parameters mp
468 WHERE cic.inventory_item_id = i_item_id
469 AND cic.organization_id = mp.cost_organization_id
470 AND mp.organization_id = i_org_id
471 AND cic.cost_type_id = 1
472 )
473 WHERE b.acct_period_id = i_period_id
474 AND b.organization_id = i_org_id
475 AND b.wip_entity_id = i_entity_id
476 AND EXISTS
477 (SELECT 'Check if the item has cost'
478 FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
479 WHERE INVENTORY_ITEM_ID = i_item_id
480 AND MP.ORGANIZATION_ID = i_org_id
481 AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
482 AND COST_TYPE_ID = i_cost_type_id);
483
484 ELSE /* repetitive schedules */
485
486 FOR wpb_rec IN wpb_rep_sch LOOP
487 UPDATE wip_period_balances b
488 SET (LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN,
489 REQUEST_ID,PROGRAM_APPLICATION_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE,
490 pl_material_out,
491 pl_material_overhead_out,
492 pl_resource_out,
493 pl_outside_processing_out,
494 pl_overhead_out,
495 tl_material_out,
496 tl_resource_out,
497 tl_outside_processing_out,
498 tl_overhead_out) =
499 (SELECT
500 SYSDATE, i_user_id, i_login_id,
501 DECODE(i_req_id, -1, NULL, i_req_id),
502 DECODE(i_prg_appl_id, -1, NULL, i_prg_appl_id),
503 DECODE(i_prg_id, -1, NULL, i_prg_id),
504 DECODE(i_req_id, -1, NULL, SYSDATE),
505 nvl(b.pl_material_out,0) +
506 ROUND((NVL(pl_material,0) * alloc.primary_quantity)/
507 l_round_unit)* l_round_unit,
508 nvl(b.pl_material_overhead_out,0) +
509 ROUND((NVL(pl_material_overhead,0) *alloc.primary_quantity)
510 /l_round_unit)* l_round_unit,
511 nvl(b.pl_resource_out,0) +
512 ROUND((NVL(pl_resource,0) *alloc.primary_quantity)
513 /l_round_unit)* l_round_unit,
514 nvl(b.pl_outside_processing_out,0) +
515 ROUND((NVL(pl_outside_processing,0) *alloc.primary_quantity)
516 /l_round_unit)* l_round_unit,
517 nvl(b.pl_overhead_out,0) +
518 ROUND((NVL(pl_overhead,0) * alloc.primary_quantity)/
519 l_round_unit)* l_round_unit,
520 nvl(b.tl_material_out,0)+
521 ROUND((NVL(tl_material,0) * alloc.primary_quantity)/
522 l_round_unit)* l_round_unit,
523 nvl(b.tl_resource_out,0) +
524 ROUND((NVL(tl_resource,0) * alloc.primary_quantity)/
525 l_round_unit)* l_round_unit,
526 nvl(b.tl_outside_processing_out,0) +
527 ROUND((NVL(tl_outside_processing,0)*alloc.primary_quantity)
528 /l_round_unit)* l_round_unit,
529 nvl(b.tl_overhead_out,0) +
530 ROUND((NVL(tl_overhead,0) * alloc.primary_quantity)/
531 l_round_unit)* l_round_unit
532 FROM cst_item_costs cic,
533 mtl_parameters mp,
534 mtl_material_txn_allocations alloc
535 WHERE cic.inventory_item_id = i_item_id
536 AND cic.organization_id = mp.cost_organization_id
537 AND mp.organization_id = i_org_id
538 AND cic.cost_type_id = 1
539 AND alloc.transaction_id = i_txn_id
540 AND NVL(alloc.repetitive_schedule_id, -99) =
541 NVL(b.repetitive_schedule_id, -99)
542 )
543 WHERE CURRENT OF wpb_rep_sch;
544 /* Changes introduced for bug 6709905 to prevent deadlock scenario
545 WHERE b.acct_period_id = i_period_id
546 AND b.organization_id = i_org_id
547 AND b.wip_entity_id = i_entity_id
548 AND b.repetitive_schedule_id in
549 (SELECT repetitive_schedule_id
550 FROM mtl_material_txn_allocations
551 WHERE transaction_id = i_txn_id)
552 AND EXISTS
553 (SELECT 'Check if the item has cost'
554 FROM CST_ITEM_COSTS CIC, MTL_PARAMETERS MP
555 WHERE INVENTORY_ITEM_ID = i_item_id
556 AND MP.ORGANIZATION_ID = i_org_id
557 AND CIC.ORGANIZATION_ID = MP.COST_ORGANIZATION_ID
558 AND COST_TYPE_ID = i_cost_type_id);*/
559 END LOOP;
560
561 END IF;
562
563 IF SQL%ROWCOUNT > 0 THEN
564 return (1);
565 ELSE
566 return (-999);
567 END IF;
568
569 /* exception handlers */
570 EXCEPTION
571 WHEN WSM_ESA_PKG_ERROR THEN
572 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Failure in WSM_ESA_ENABLED : '||'wip_entity_id : '||
573 i_entity_id || ' : ' || l_err_num || ' : ' ||l_err_msg);
574 raise_application_error(-20001,l_err_msg);
575 return (0);
576
577 WHEN OTHERS THEN
578 raise_application_error(-20001, SQLERRM);
579 return (0);
580 END complete;
581
582 END CSTPPWMX;