1 PACKAGE BODY CSTPUPDT AS
2 /* $Header: CSTPUPDB.pls 120.1 2006/02/14 17:03:10 ssreddy noship $ */
3
4 FUNCTION cstulock (
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
425
426 END CSTPUPDT;
427
[Home] [Help]