[Home] [Help]
PACKAGE BODY: APPS.CSTPUPDT
Source
4 FUNCTION cstulock (
1 PACKAGE BODY CSTPUPDT AS
2 /* $Header: CSTPUPDB.pls 120.2 2011/12/19 09:25:12 pregoel ship $ */
3
5 table_name in varchar2,
6 l_cost_type_id in number,
7 l_organization_id in number,
8 l_list_id in number,
9 err_buf out NOCOPY varchar2,
10 l_list_id1 in number
11 )
12 return integer
13 IS
14 status number;
15 L_NO_DATA_FOUND number := 100;
16
17 /* Removed hint from follwing Insert stmt for POSCO issue: Bug #1665358.
18 + ORDERED USE_NL(CIC CL) INDEX(CL CST_LISTS_U1) INDEX(CIC CST_ITEM_COSTS_U1)
19 */
20 CURSOR cc is
21 SELECT
22 CIC.INVENTORY_ITEM_ID
23 FROM
24 CST_LISTS CL,
25 CST_ITEM_COSTS CIC
26 WHERE CIC.ORGANIZATION_ID = l_organization_id
27 AND CIC.COST_TYPE_ID = l_cost_type_id
28 AND CL.LIST_ID = l_list_id
29 AND CL.ENTITY_ID = CIC.INVENTORY_ITEM_ID
30 FOR UPDATE OF CIC.LAST_UPDATED_BY NOWAIT;
31
32 /* Removed hint from follwing Insert stmt for POSCO issue: Bug #1665358.
33 + ORDERED USE_NL(CICD CL) INDEX(CL CST_LISTS_U1) INDEX(CICD CST_ITEM_COST_DETAILS_N1)
34 */
35 CURSOR cd is
36 SELECT
37 CICD.INVENTORY_ITEM_ID
38 FROM
39 CST_LISTS CL,
40 CST_ITEM_COST_DETAILS CICD
41 WHERE CICD.ORGANIZATION_ID = l_organization_id
42 AND CL.LIST_ID = l_list_id
43 AND CL.ENTITY_ID = CICD.INVENTORY_ITEM_ID
44 AND CICD.COST_TYPE_ID = l_cost_type_id
45 FOR UPDATE OF CICD.LAST_UPDATED_BY NOWAIT;
46
47 CURSOR crc is
48 SELECT
49 CRC.RESOURCE_ID
50 FROM
51 CST_LISTS_TEMP CLT,
52 CST_RESOURCE_COSTS CRC
53 WHERE CRC.ORGANIZATION_ID = l_organization_id
54 AND CLT.LIST_ID = l_list_id
55 AND CLT.NUMBER_1 = CRC.RESOURCE_ID
56 AND CRC.COST_TYPE_ID = l_cost_type_id
57 FOR UPDATE OF CRC.LAST_UPDATED_BY NOWAIT;
58
59 CURSOR cdo is
60 SELECT
61 CDO.DEPARTMENT_ID
62 FROM
63 CST_LISTS_TEMP CLT,
64 CST_DEPARTMENT_OVERHEADS CDO
65 WHERE CDO.ORGANIZATION_ID = l_organization_id
66 AND CLT.LIST_ID = l_list_id
67 AND CLT.NUMBER_1 = CDO.OVERHEAD_ID
68 AND CDO.COST_TYPE_ID = l_cost_type_id
69 FOR UPDATE OF CDO.LAST_UPDATED_BY NOWAIT;
70
71 CURSOR cro is
72 SELECT
73 CRO.RESOURCE_ID
74 FROM
75 CST_LISTS_TEMP CLT1,
76 CST_LISTS_TEMP CLT2,
77 CST_RESOURCE_OVERHEADS CRO
78 WHERE CRO.ORGANIZATION_ID = l_organization_id
79 AND CLT1.LIST_ID = l_list_id
80 AND CLT1.NUMBER_1 = CRO.RESOURCE_ID
81 AND CLT2.LIST_ID = l_list_id1
82 AND CLT2.NUMBER_1 = CRO.OVERHEAD_ID
83 AND CRO.COST_TYPE_ID = l_cost_type_id
84 FOR UPDATE OF CRO.LAST_UPDATED_BY NOWAIT;
85
86 BEGIN
87 if table_name = 'CST_ITEM_COSTS' then
88 OPEN cc;
89 status := SQLCODE;
90 elsif table_name = 'CST_ITEM_COST_DETAILS' then
91 OPEN cd;
92 status := SQLCODE;
93 elsif table_name = 'CST_RESOURCE_COSTS' then
94 OPEN crc;
95 status := SQLCODE;
96 elsif table_name = 'CST_DEPARTMENT_OVERHEADS' then
97 OPEN cdo;
98 status := SQLCODE;
99 elsif table_name = 'CST_RESOURCE_OVERHEADS' then
100 OPEN cro;
101 status := SQLCODE;
102 else
103 status := L_NO_DATA_FOUND;
104 end if;
105 return (status);
106 EXCEPTION
107 when others then
108 status := SQLCODE;
109 err_buf := 'CSTULOCK:' || substrb(sqlerrm,1,60);
110 return(status);
111 END cstulock;
112
113 function cstuwait_lock(
114 l_cost_type_id in number,
115 l_organization_id in number,
116 l_list_id in number,
117 err_buf out NOCOPY varchar2,
118 l_res_list_id in number,
119 l_ovh_list_id in number
120 )
121 return integer
122 is
123 status number := -54; /* Refers to the ORA-00054 error message:
124 resource busy and acquire with NOWAIT specified */
125 counter number := 0;
126 BEGIN
127 /*
128 ** Lock the table of CST_ITEM_COSTS
129 */
130 while (counter < NUM_TRIES and status = -54) LOOP
131 status := CSTPUPDT.cstulock('CST_ITEM_COSTS',l_cost_type_id,
132 l_organization_id,
133 l_list_id,err_buf);
134 if status = -54 then
135 DBMS_LOCK.SLEEP(SLEEP_TIME);
136 end if;
137 counter := counter + 1;
138 end LOOP;
139 if status <> 0 then
140 if status = -54 then
141 err_buf := 'CST_LOCK_FAILED_CIC';
142 status := 9999;
143 end if;
144 return(status);
145 end if;
146 /*
147 ** Lock the table of CST_ITEM_COST_DETAILS
148 */
149 status := -54;
150 while (counter < NUM_TRIES and status = -54) LOOP
151 status := cstpupdt.cstulock('CST_ITEM_COST_DETAILS',
152 l_cost_type_id,
153 l_organization_id,
154 l_list_id,err_buf);
155 if status = -54 then
156 DBMS_LOCK.SLEEP(SLEEP_TIME);
157 end if;
158 counter := counter + 1;
159 end LOOP;
160 if status <> 0 then
161 if status = -54 then
162 err_buf := 'CST_LOCK_FAILED_CICD';
163 status := 9999;
164 end if;
165 return(status);
166 end if;
167 if (l_res_list_id is not null and l_ovh_list_id is not null) then
168 /*
169 ** Lock the table of CST_RESOURCE_COSTS
170 */
171 status := -54;
172 while (counter < NUM_TRIES and status = -54) LOOP
173 status := cstpupdt.cstulock('CST_RESOURCE_COSTS',
174 l_cost_type_id,
175 l_organization_id,
176 l_res_list_id, err_buf);
177 if status = -54 then
178 DBMS_LOCK.SLEEP(SLEEP_TIME);
179 end if;
180 counter := counter + 1;
181 end LOOP;
182 if status <> 0 then
183 if status = -54 then
184 err_buf := 'CST_LOCK_FAILED_CRC';
185 status := 9999;
186 end if;
187 return(status);
188 end if;
189 /*
190 ** Lock the table of CST_DEPARTMENT_OVERHEADS
191 */
192 status := -54;
193 while (counter < NUM_TRIES and status = -54) LOOP
194 status := cstpupdt.cstulock('CST_DEPARTMENT_OVERHEADS',
195 l_cost_type_id,
196 l_organization_id,
197 l_ovh_list_id, err_buf);
198 if status = -54 then
199 DBMS_LOCK.SLEEP(SLEEP_TIME);
200 end if;
201 counter := counter + 1;
202 end LOOP;
203 if status <> 0 then
204 if status = -54 then
205 err_buf := 'CST_LOCK_FAILED_CDO';
206 status := 9999;
207 end if;
208 return(status);
209 end if;
210 /*
211 ** Lock the table of CST_RESOURCE_OVERHEADS
212 */
213 status := -54;
214 while (counter < NUM_TRIES and status = -54) LOOP
215 status := cstpupdt.cstulock('CST_RESOURCE_OVERHEADS',
216 l_cost_type_id,
217 l_organization_id,
218 l_res_list_id, err_buf, l_ovh_list_id);
219 if status = -54 then
220 DBMS_LOCK.SLEEP(SLEEP_TIME);
221 end if;
222 counter := counter + 1;
223 end LOOP;
224 if status <> 0 then
225 if status = -54 then
226 err_buf := 'CST_LOCK_FAILED_CRO';
227 status := 9999;
228 end if;
229 return(status);
230 end if;
231 end if;
232 return(status);
233 EXCEPTION
234 when others then
235 status := SQLCODE;
236 err_buf := 'CSTUWAIT:' || substrb(sqlerrm,1,60);
237 return(status);
238 END cstuwait_lock;
239
240 FUNCTION cstudlci(
241 l_cost_type_id in number,
242 l_organization_id in number,
243 err_buf out NOCOPY varchar2
244 )
245 return integer
246 is
247
248 return_code number;
249
250 CURSOR del_cur1 IS
251 SELECT inventory_item_id
252 FROM cst_item_costs cic
253 WHERE cic.organization_id = l_organization_id
254 AND cic.cost_type_id = l_cost_type_id;
255
256 BEGIN
257 FOR del_cic_cur IN del_cur1 loop
258 DELETE CST_ITEM_COSTS cic
259 WHERE organization_id = l_organization_id
260 AND cost_type_id = 1
261 AND inventory_item_id = del_cic_cur.inventory_item_id;
262 END LOOP;
263
264 return_code := 0;
265 return(return_code);
266
267 EXCEPTION
268 when others then
269 return_code := SQLCODE;
270 err_buf := 'CSTUDLCI: '|| substrb(sqlerrm,1,60);
271 return(return_code);
272 END cstudlci;
273
274 FUNCTION cstudlcd(
275 l_cost_type_id in number,
276 l_organization_id in number,
277 err_buf out NOCOPY varchar2
278 )
279 return integer
280 is
281
282 return_code number;
283
284 CURSOR del_cur2 IS
285 SELECT inventory_item_id
286 FROM cst_item_costs_temp cict;
287
288 BEGIN
289 FOR del_cicd_cur IN del_cur2 loop
290 DELETE CST_ITEM_COST_DETAILS cicd
291 WHERE organization_id = l_organization_id
292 AND cost_type_id = 1
293 AND inventory_item_id = del_cicd_cur.inventory_item_id;
294 END LOOP;
295
296 return_code := 0;
297 return(return_code);
298
299 EXCEPTION
300 when others then
301 return_code := SQLCODE;
302 err_buf := 'CSTUDLCD: '|| substrb(sqlerrm,1,60);
303 return(return_code);
304 END cstudlcd;
305
306 FUNCTION cstudlcv(
307 l_cost_update_id in number,
308 err_buf out NOCOPY varchar2
309 )
310 return integer
311 is
312
313 return_code number;
314
315 BEGIN
316 DELETE FROM cst_std_cost_adj_values V
317 WHERE v.cost_update_id = l_cost_update_id
318 AND v.transaction_type = 7 -- resource overhead
319 AND (V.old_unit_cost = 0 OR V.new_unit_cost = 0)
320 AND EXISTS
321 (SELECT 'X'
322 FROM CST_STD_COST_ADJ_VALUES v1
323 WHERE v1.cost_update_id = l_cost_update_id
324 AND v1.transaction_type = 7
325 AND v1.organization_id = v.organization_id
326 AND v1.wip_entity_id = v.wip_entity_id
327 AND v1.operation_seq_num = v.operation_seq_num
328 AND v1.resource_id = v.resource_id
329 AND v1.resource_seq_num = v.resource_seq_num
330 AND v1.basis_type = v.basis_type
331 AND v1.adjustment_quantity = v.adjustment_quantity
332 AND (
333 ( v.new_unit_cost = 0
334 AND v1.old_unit_cost = 0
335 AND v.old_unit_cost = v1.new_unit_cost
336 )
337 OR
338 ( v1.new_unit_cost = 0
339 AND v.old_unit_cost = 0
340 AND v1.old_unit_cost = v.new_unit_cost
341 )
342 )
343 AND v1.rowid <> v.rowid
344 );
345 return_code := 0;
346 return(return_code);
347
348 EXCEPTION
349 when others then
350 return_code := SQLCODE;
351 err_buf := 'CSTUDLCV: '|| substrb(sqlerrm,1,60);
352 return(return_code);
353 END cstudlcv;
354
355 FUNCTION cstudlc2(
356 l_cost_update_id in number,
357 err_buf out NOCOPY varchar2
358 )
359 return integer
360 is
361
362 return_code number;
363
364 CURSOR del_cur4 IS
365 SELECT organization_id, wip_entity_id,
366 operation_seq_num, resource_seq_num
367 FROM CST_STD_COST_ADJ_VALUES
368 WHERE cost_update_id = l_cost_update_id
369 AND transaction_type + 0 = 7;
370
371 BEGIN
372 FOR del_c2_cur IN del_cur4 LOOP
373 DELETE CST_STD_COST_ADJ_VALUES
374 WHERE cost_update_id = l_cost_update_id
375 AND new_unit_cost = old_unit_cost
376 AND transaction_type = 6
377 AND organization_id + 0 = del_c2_cur.organization_id
378 and wip_entity_id = del_c2_cur.wip_entity_id
379 AND operation_seq_num = del_c2_cur.operation_seq_num
380 AND resource_seq_num = del_c2_cur.resource_seq_num;
381 END LOOP;
382
383 return_code := 0;
384 return(return_code);
385
386 EXCEPTION
387 when others then
388 return_code := SQLCODE;
389 err_buf := 'CSTUDLC2: '|| substrb(sqlerrm,1,60);
390 return(return_code);
391 END cstudlc2;
392
393 FUNCTION cstudlc3(
394 l_cost_update_id in number,
395 err_buf out NOCOPY varchar2
396 )
397 return integer
398 is
399
400 return_code number;
401
402 BEGIN
403 DELETE cst_std_cost_adj_values V
404 WHERE V.rowid in
405 (SELECT VV1.rowidtodel
406 FROM cst_std_cost_adj_tmp1_v VV1, cst_std_cost_adj_tmp2_v VV2
407 WHERE VV1.transaction_id = VV2.transaction_id
408 AND VV1.cost_element_id = VV2.cost_element_id
409 AND VV1.level_type = VV2.level_type
410 AND VV1.unit_cost = VV2.unit_cost
411 AND VV1.new_unit_cost = VV2.old_unit_cost
412 AND VV1.cost_update_id = l_cost_update_id
413 AND VV2.cost_update_id = l_cost_update_id);
414
415 return_code := 0;
416 return(return_code);
417
418 EXCEPTION
419 when others then
420 return_code := SQLCODE;
421 err_buf := 'CSTUDLC3: '|| substrb(sqlerrm,1,60);
422 return(return_code);
423 END cstudlc3;
424 /*Added cst_del_cilt function for bug 11889101*/
425 FUNCTION cst_del_cilt
426 (l_org_id IN number,
427 l_cost_type_id IN number,
428 b_cm_frozen_standard IN NUMBER,
429 err_buf out NOCOPY varchar2)
430 RETURN INTEGER IS
431
432 return_code number;
433
434 TYPE cilt_item_tbl_type IS TABLE OF cst_item_costs.inventory_item_id%TYPE INDEX BY BINARY_INTEGER;
435 cilt_item_id cilt_item_tbl_type ;
436
437 bulk_limit NUMBER := 2000;
438 l_stmt_num NUMBER;
439
440 CURSOR CILT_CUR is
441 SELECT CIC1.inventory_item_id
442 FROM CST_ITEM_COSTS CIC1,
443 CST_ITEM_COSTS CIC2,
444 CST_ITEM_LIST_TEMP CLT
445 WHERE CIC1.organization_id = l_org_id
446 AND CIC1.cost_type_id = b_cm_frozen_standard
447 AND CIC1.inventory_item_id = CLT.inventory_item_id
448 AND CIC2.organization_id (+) = l_org_id
449 AND CIC2.cost_type_id (+) = l_cost_type_id
450 AND CIC2.inventory_item_id (+) = CIC1.inventory_item_id
451 AND ( CIC2.item_cost IS NULL
452 OR ( CIC1.inventory_asset_flag = CIC2.inventory_asset_flag /* mandatory column */
453 AND ( CIC1.defaulted_flag = CIC2.defaulted_flag /* mandatory column */
454 OR CIC1.defaulted_flag = 2)
455 /* Defaulted flag is always updated to 2 in cmcuuic() */
456 AND CIC1.item_cost = CIC2.item_cost
457 AND NVL(CIC1.material_cost, 0) = NVL(CIC2.material_cost, 0)
458 AND NVL(CIC1.material_overhead_cost, 0) = NVL(CIC2.material_overhead_cost, 0)
459 AND NVL(CIC1.resource_cost, 0) = NVL(CIC2.resource_cost, 0)
460 AND NVL(CIC1.outside_processing_cost, 0) = NVL(CIC2.outside_processing_cost, 0)
461 AND NVL(CIC1.overhead_cost, 0) = NVL(CIC2.overhead_cost, 0)
462 AND NVL(CIC1.pl_item_cost, 0) = NVL(CIC2.pl_item_cost, 0)
463 AND NVL(CIC1.pl_material, 0) = NVL(CIC2.pl_material, 0)
464 AND NVL(CIC1.pl_material_overhead, 0) = NVL(CIC2.pl_material_overhead, 0)
465 AND NVL(CIC1.pl_resource, 0) = NVL(CIC2.pl_resource, 0)
466 AND NVL(CIC1.pl_outside_processing, 0) = NVL(CIC2.pl_outside_processing, 0)
467 AND NVL(CIC1.pl_overhead, 0) = NVL(CIC2.pl_overhead, 0)
468 AND NVL(CIC1.tl_item_cost, 0) = NVL(CIC2.tl_item_cost, 0)
469 AND NVL(CIC1.tl_material, 0) = NVL(CIC2.tl_material, 0)
470 AND NVL(CIC1.tl_material_overhead, 0) = NVL(CIC2.tl_material_overhead, 0)
471 AND NVL(CIC1.tl_resource, 0) = NVL(CIC2.tl_resource, 0)
472 AND NVL(CIC1.tl_outside_processing, 0) = NVL(CIC2.tl_outside_processing, 0)
473 AND NVL(CIC1.tl_overhead, 0) = NVL(CIC2.tl_overhead, 0)
474 AND NVL(CIC1.lot_size, 1) = NVL(CIC2.lot_size, 1)
475 AND NVL(CIC1.based_on_rollup_flag, -1) = NVL(CIC2.based_on_rollup_flag, -1)
476 AND NVL(CIC1.shrinkage_rate, 0) = NVL(CIC2.shrinkage_rate, 0)
477 AND NVL(CIC1.burden_cost, 0) = NVL(CIC2.burden_cost, 0)
478 AND NVL(CIC1.unburdened_cost, 0) = NVL(CIC2.unburdened_cost, 0)
479 AND NVL(CIC1.attribute_category,'-1') = NVL(CIC2.attribute_category, '-1')
480 AND NVL(CIC1.attribute1, '-1') = NVL(CIC2.attribute1, '-1')
481 AND NVL(CIC1.attribute2, '-1') = NVL(CIC2.attribute2, '-1')
482 AND NVL(CIC1.attribute3, '-1') = NVL(CIC2.attribute3, '-1')
483 AND NVL(CIC1.attribute4, '-1') = NVL(CIC2.attribute4, '-1')
484 AND NVL(CIC1.attribute5, '-1') = NVL(CIC2.attribute5, '-1')
485 AND NVL(CIC1.attribute6, '-1') = NVL(CIC2.attribute6, '-1')
486 AND NVL(CIC1.attribute7, '-1') = NVL(CIC2.attribute7, '-1')
487 AND NVL(CIC1.attribute8, '-1') = NVL(CIC2.attribute8, '-1')
488 AND NVL(CIC1.attribute9, '-1') = NVL(CIC2.attribute9, '-1')
489 AND NVL(CIC1.attribute10, '-1') = NVL(CIC2.attribute10, '-1')
490 AND NVL(CIC1.attribute11, '-1') = NVL(CIC2.attribute11, '-1')
494 AND NVL(CIC1.attribute15, '-1') = NVL(CIC2.attribute15, '-1')
491 AND NVL(CIC1.attribute12, '-1') = NVL(CIC2.attribute12, '-1')
492 AND NVL(CIC1.attribute13, '-1') = NVL(CIC2.attribute13, '-1')
493 AND NVL(CIC1.attribute14, '-1') = NVL(CIC2.attribute14, '-1')
495 /* Added for bug 7338350 for checking CICD Difference */
496 AND NOT EXISTS( ( SELECT rollup_source_type, level_type,
497 cost_element_id, Nvl(resource_id, -1),
498 basis_type, basis_factor,
499 usage_rate_or_amount, item_cost
500 FROM CST_ITEM_COST_DETAILS CICD1
501 WHERE CICD1.organization_id = l_org_id
502 AND CICD1.cost_type_id = b_cm_frozen_standard
503 AND CICD1.inventory_item_id = CIC2.inventory_item_id)
504 MINUS
505 ( SELECT rollup_source_type, level_type,
506 cost_element_id, Nvl(resource_id, -1),
507 basis_type, basis_factor,
508 usage_rate_or_amount, item_cost
509 FROM CST_ITEM_COST_DETAILS CICD2
510 WHERE CICD2.organization_id = l_org_id
511 AND CICD2.cost_type_id = l_cost_type_id
512 AND CICD2.inventory_item_id = CIC2.inventory_item_id))));
513
514
515 BEGIN
516 fnd_file.put_line(fnd_file.LOG, ' cst_del_cilt << ' || TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
517 OPEN CILT_CUR;
518 LOOP
519 l_stmt_num := 10;
520 FETCH CILT_CUR BULK COLLECT INTO
521 cilt_item_id LIMIT bulk_limit;
522
523 l_stmt_num := 20;
524
525 FORALL i IN cilt_item_id.first..cilt_item_id.last
526
527 DELETE FROM CST_ITEM_LIST_TEMP CILT
528 WHERE inventory_item_id=cilt_item_id(i);
529
530 EXIT WHEN CILT_CUR%NOTFOUND;
531 END LOOP;
532
533 fnd_file.put_line(fnd_file.LOG, 'cst_del_cilt >> ' || TO_CHAR(sysdate, 'DD-MON-RRRR HH24:MI:SS'));
534 l_stmt_num := 40;
535
536 CLOSE cilt_cur;
537 return_code := 0;
538 return(return_code);
539
540 EXCEPTION
541 WHEN OTHERS THEN
542 return_code := SQLCODE;
543 err_buf := 'cstpupdt.cst_del_cilt (' || l_stmt_num || ')' || substrb(sqlerrm,1,60);
544 return(return_code);
545 END cst_del_cilt;
546
547 END CSTPUPDT;
548