[Home] [Help]
PACKAGE BODY: APPS.INVPCOII
Source
1 PACKAGE BODY INVPCOII AS
2 /* $Header: INVPCOIB.pls 120.2 2008/08/20 22:25:42 trudave ship $ */
3
4 l_location NUMBER;
5
6 /*======================================================================*/
7 /* Function : CSTFVSUB */
8 /* */
9 /* Description : */
10 /* Validate the sub element */
11 /* If the org is average cost and there should be value for */
12 /* material overhead sub element */
13 /* */
14 /* Note : */
15 /* I_COST_ELEMENT_ID : 1 Material */
16 /* 2 Material Overhead */
17 /*06MAY96 NPARATE */
18 /*This function does not need xset_id parameter since processing */
19 /*done using transaction_id which is unique enough in MSII */
20 /*======================================================================*/
21
22 FUNCTION CSTFVSUB (
23 I_TRANSID IN NUMBER,
24 I_MATERIAL_SUB_ELEM IN VARCHAR2 DEFAULT NULL,
25 I_MATERIAL_OH_SUB_ELEM IN VARCHAR2 DEFAULT NULL,
26 I_ORGANIZATION_ID IN NUMBER,
27 I_USER_ID IN NUMBER := -1,
28 I_LOGIN_ID IN NUMBER := -1,
29 I_REQ_ID IN NUMBER := -1,
30 I_PRGM_ID IN NUMBER := -1,
31 I_PRGM_APPL_ID IN NUMBER := -1,
32 O_ERR_TEXT IN OUT NOCOPY VARCHAR2)
33 RETURN INTEGER IS
34
35 l_material_sub_elem VARCHAR2(10);
36 l_material_oh_sub_elem VARCHAR2(10);
37 l_organization_id NUMBER;
38 l_material_sub_elem_id NUMBER;
39 l_material_oh_sub_elem_id NUMBER;
40 l_cost_type_id NUMBER;
41 l_cost_organization_id NUMBER;
42 l_transaction_id NUMBER;
43 l_status NUMBER;
44 l_error_msg VARCHAR2(240);
45
46 BEGIN
47 O_ERR_TEXT := ' ';
48 l_error_msg := ' ';
49 l_material_sub_elem_id := 0;
50 l_material_oh_sub_elem_id := 0;
51
52 l_location := 100;
53
54 /* Bug 4705184. Get these values as parameters instead of querying
55 select material_sub_elem,
56 material_oh_sub_elem,
57 organization_id,
58 transaction_id
59 into l_material_sub_elem,
60 l_material_oh_sub_elem,
61 l_organization_id,
62 l_transaction_id
63 from mtl_system_items_interface
64 where transaction_id = I_TRANSID; */
65
66 l_material_sub_elem := I_MATERIAL_SUB_ELEM;
67 l_material_oh_sub_elem := I_MATERIAL_OH_SUB_ELEM;
68 l_organization_id := I_ORGANIZATION_ID;
69 l_transaction_id := I_TRANSID;
70
71 l_location := 105;
72
73 select primary_cost_method,
74 cost_organization_id
75 into l_cost_type_id,
76 l_cost_organization_id
77 from mtl_parameters
78 where organization_id = l_organization_id;
79
80 /* Material Sub element */
81 If (l_material_sub_elem is not NULL) Then
82 l_location := 110;
83 select resource_id
84 into l_material_sub_elem_id
85 from bom_resources br
86 where br.resource_code = l_material_sub_elem
87 and br.organization_id = l_cost_organization_id
88 and br.cost_code_type = 1;
89 End if;
90
91 /* moh valid for frozen std costing only */
92 If (l_cost_type_id = 1) Then
93 /* Material overhead sub element */
94 If (l_material_oh_sub_elem is not NULL) Then
95 l_location := 120;
96 select resource_id
97 into l_material_oh_sub_elem_id
98 from bom_resources br
99 where br.resource_code = l_material_oh_sub_elem
100 and br.organization_id = l_cost_organization_id
101 and br.cost_code_type = 2;
102 End if;
103 Else
104 If (l_material_oh_sub_elem is not NULL) Then
105 l_error_msg := 'CST- Average cost could not have material overhead';
106 l_status := INVPUOPI.mtl_log_interface_err(
107 l_organization_id,
108 I_USER_ID,
109 I_LOGIN_ID,
110 I_PRGM_APPL_ID,
111 I_PRGM_ID,
112 I_REQ_ID,
113 l_transaction_id,
114 l_error_msg,
115 null,
116 'MTL_SYSTEM_ITEMS_INTERFACE',
117 'CST_INVALID_MOH_AVE',
118 O_ERR_TEXT);
119 return(1);
120 End if;
121 End if;
122
123 /* Update mtl_system_items_interface
124 ** NP 06MAY96: Does not require the xset_id since transaction_id
125 ** is unique to each row in MSII even though there may be multiple
126 ** parallel IOI processes
127 */
128 l_location := 130;
129 update mtl_system_items_interface
130 set material_sub_elem_id = decode(l_material_sub_elem_id,
131 0,NULL,l_material_sub_elem_id),
132 material_oh_sub_elem_id = decode(l_material_oh_sub_elem_id,
133 0,NULL,l_material_oh_sub_elem_id)
134 where transaction_id = I_TRANSID;
135
136 return(0);
137
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 if (l_location = 110) then
141 l_error_msg := 'CST- Material Sub Element does not exist in costing org '||
142 to_char(l_cost_organization_id);
143 l_status := INVPUOPI.mtl_log_interface_err(
144 l_organization_id,
145 I_USER_ID,
146 I_LOGIN_ID,
147 I_PRGM_APPL_ID,
148 I_PRGM_ID,
149 I_REQ_ID,
150 l_transaction_id,
151 l_error_msg,
152 null,
153 'MTL_SYSTEM_ITEMS_INTERFACE',
154 'CST_INVALID_MAT_SUB',
155 O_ERR_TEXT);
156 else
157 l_error_msg := 'CST- Material Overhead Sub Element does not exist in costing org '||
158 to_char(l_cost_organization_id);
159 l_status := INVPUOPI.mtl_log_interface_err(
160 l_organization_id,
161 I_USER_ID,
162 I_LOGIN_ID,
163 I_PRGM_APPL_ID,
164 I_PRGM_ID,
165 I_REQ_ID,
166 l_transaction_id,
167 l_error_msg,
168 null,
169 'MTL_SYSTEM_ITEMS_INTERFACE',
170 'CST_INVALID_MOH_SUB',
171 O_ERR_TEXT);
172 end if;
173 return (1);
174 WHEN OTHERS THEN
175 O_ERR_TEXT := substr('CSTFVSUB - '||to_char(l_location)||' : '||SQLERRM,1, 240);
176 return(SQLCODE);
177
178 END CSTFVSUB;
179
180
181 /*======================================================================*/
182 /* Procedure: CSTPIICD */
183 /* */
184 /* Description : */
185 /* Insert material and material overhead cost into */
186 /* cst_item_cost_details */
187 /* */
188 /* */
189 /* Note : */
190 /* The basis type will default to the following if one is not define */
191 /* by the user in the bom_resources table. */
192 /* I_COST_ELEMENT_ID : 1 Material item basis */
193 /* 2 Material Overhead total value basis */
194 /* For average costing, there is no material overhead concept */
195 /* Assume that the record does not exist yet */
196 /* */
197 /*======================================================================*/
198
199 PROCEDURE CSTPIICD (
200 I_ORGANIZATION_ID IN NUMBER,
201 I_INVENTORY_ITEM_ID IN NUMBER,
202 I_COST_ELEMENT_ID IN NUMBER,
203 I_COST_RATE IN NUMBER,
204 I_RESOURCE_ID IN NUMBER,
205 I_USER_ID IN NUMBER,
206 I_LOGIN_ID IN NUMBER,
207 I_REQ_ID IN NUMBER,
208 I_PRGM_ID IN NUMBER,
209 I_PRGM_APPL_ID IN NUMBER,
210 O_RETURN_CODE OUT NOCOPY NUMBER,
211 O_ERR_TEXT IN OUT NOCOPY VARCHAR2) IS
212 l_basis_type NUMBER;
213 cost_update NUMBER;
214 BEGIN
215 O_ERR_TEXT := ' ';
216 O_RETURN_CODE := -99999;
217 l_location := 199;
218
219 select nvl(default_basis_type,decode(i_cost_element_id,1,1,2,5,0))
220 into l_basis_type
221 from bom_resources
222 where resource_id = i_resource_id;
223
224 l_location := 200;
225
226 /* Added delete st for bug 3219632 */
227
228 delete from cst_item_cost_details cicd
229 where cicd.ORGANIZATION_ID = I_ORGANIZATION_ID
230 AND cicd.INVENTORY_ITEM_ID = I_INVENTORY_ITEM_ID
231 AND cicd.COST_ELEMENT_ID = I_COST_ELEMENT_ID;
232
233 /* End of bug fix 3219632 */
234
235 insert into cst_item_cost_details
236 ( inventory_item_id
237 , organization_id
238 , cost_type_id
239 , last_update_date
240 , last_updated_by
241 , creation_date
242 , created_by
243 , last_update_login
244 , operation_sequence_id
245 , operation_seq_num
246 , department_id
247 , level_type
248 , activity_id
249 , resource_seq_num
250 , resource_id
251 , resource_rate
252 , item_units
253 , activity_units
254 , usage_rate_or_amount
255 , basis_type
256 , basis_resource_id
257 , basis_factor
258 , net_yield_or_shrinkage_factor
259 , item_cost
260 , cost_element_id
261 , rollup_source_type
262 , activity_context
263 , request_id
264 , program_application_id
265 , program_id
266 , program_update_date
267 , attribute_category
268 , attribute1
269 , attribute2
270 , attribute3
271 , attribute4
272 , attribute5
273 , attribute6
274 , attribute7
275 , attribute8
276 , attribute9
277 , attribute10
278 , attribute11
279 , attribute12
280 , attribute13
281 , attribute14
282 , attribute15)
283 select i_inventory_item_id, -- inventory_item_id
284 i_organization_id, -- organization_id
285 mp.primary_cost_method, -- cost_type_id
286 sysdate, -- last_update_date
287 I_USER_ID, -- last_updated_by
288 sysdate, -- creation_date
289 I_USER_ID, -- created_by
290 I_LOGIN_ID, -- last_update_login
291 NULL, -- operation_sequence_id
292 NULL, -- operation_seq_num
293 NULL, -- department_id
294 1, -- level_type
295 NULL, -- activity_id
296 NULL, -- resource_seq_num
297 I_RESOURCE_ID, -- resource_id
298 NULL, -- resource_rate
299 NULL, -- item_units
300 NULL, -- activity_units
301 nvl(I_COST_RATE,0), -- usage_rate_or_amount
302 l_basis_type, -- basis_type
303 NULL, -- basis_resource_id
304 1, -- basis_factor
305 1, -- net_yield_or_shrinkage_factor
306 decode(l_basis_type,
307 5,0,nvl(I_COST_RATE,0)), -- item_cost
308 I_COST_ELEMENT_ID, -- cost_element_id
309 1, -- rollup_source_type
310 NULL, -- activity_context
311 I_REQ_ID, -- request_id
312 I_PRGM_APPL_ID, -- program_application_id
313 I_PRGM_ID, -- program_id
314 NULL, -- program_update_date
315 NULL, -- attribute_category
316 NULL, -- attribute1
317 NULL, -- attribute2
318 NULL, -- attribute3
319 NULL, -- attribute4
320 NULL, -- attribute5
321 NULL, -- attribute6
322 NULL, -- attribute7
323 NULL, -- attribute8
324 NULL, -- attribute9
325 NULL, -- attribute10
326 NULL, -- attribute11
327 NULL, -- attribute12
328 NULL, -- attribute13
329 NULL, -- attribute14
330 NULL -- attribute15
331 from mtl_parameters mp
332 where mp.organization_id = i_organization_id;
333
334 Select cst_lists_s.nextval
335 INTO cost_update
336 From DUAL;
337
338
339 INSERT INTO cst_standard_costs
340 (cost_update_id, organization_id,
341 inventory_item_id,
342 last_update_date, last_updated_by,
343 creation_date, created_by, last_update_login,
344 standard_cost_revision_date, standard_cost)
345 SELECT cost_update, I_ORGANIZATION_ID,
346 I_INVENTORY_ITEM_ID,
347 SYSDATE, I_USER_ID,
348 SYSDATE, I_USER_ID, I_LOGIN_ID,
349 SYSDATE, NVL(SUM(cicd.item_cost),0)
350 FROM cst_item_cost_details cicd, mtl_parameters mp
351 WHERE cicd.ORGANIZATION_ID = I_ORGANIZATION_ID
352 AND cicd.INVENTORY_ITEM_ID = I_INVENTORY_ITEM_ID
353 AND cicd.COST_TYPE_ID = mp.primary_cost_method
354 AND mp.ORGANIZATION_ID = I_ORGANIZATION_ID;
355
356
357 INSERT INTO cst_elemental_costs
358 (cost_update_id, organization_id, inventory_item_id, cost_element_id,
359 last_update_date, last_updated_by, creation_date, created_by,
360 last_update_login, standard_cost)
361 SELECT cost_update, I_ORGANIZATION_ID,
362 I_INVENTORY_ITEM_ID, cicd.cost_element_id,
363 SYSDATE, I_USER_ID,
364 SYSDATE, I_USER_ID, I_LOGIN_ID,
365 NVL(SUM(cicd.item_cost),0)
366 FROM cst_item_cost_details cicd, mtl_parameters mp
367 WHERE cicd.ORGANIZATION_ID = I_ORGANIZATION_ID
368 AND cicd.INVENTORY_ITEM_ID = I_INVENTORY_ITEM_ID
369 AND cicd.COST_TYPE_ID = mp.primary_cost_method
370 AND mp.ORGANIZATION_ID = I_ORGANIZATION_ID
371 GROUP BY cost_element_id;
372
373
374 O_ERR_TEXT := ' ';
375 O_RETURN_CODE := 0;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 O_RETURN_CODE := SQLCODE;
380 O_ERR_TEXT := substr('CSTPIICD- '||to_char(l_location)||' : '||SQLERRM, 1, 240);
381 END CSTPIICD;
382
383 /*======================================================================*/
384 /* Procedure: CSTPPCOI */
385 /* */
386 /* Description : */
387 /* */
388 /* */
389 /* Note : */
390 /* I_COST_ELEMENT_ID : 1 Material item basis */
391 /* 2 Material Overhead total value basis */
392 /* For average costing, there is no material overhead concept */
393 /* */
394 /*======================================================================*/
395
396 PROCEDURE CSTPPCOI (
397 I_ORGANIZATION_ID IN NUMBER,
398 I_INVENTORY_ITEM_ID IN NUMBER,
399 I_MATERIAL_COST IN NUMBER,
400 I_MATERIAL_SUB_ELEM_ID IN NUMBER,
401 I_MATERIAL_OH_RATE IN NUMBER,
402 I_MATERIAL_OH_SUB_ELEM_ID IN NUMBER,
403 I_USER_ID IN NUMBER,
404 I_LOGIN_ID IN NUMBER,
405 I_REQ_ID IN NUMBER,
406 I_PRGM_ID IN NUMBER,
407 I_PRGM_APPL_ID IN NUMBER,
408 O_RETURN_CODE OUT NOCOPY NUMBER,
409 O_ERR_TEXT IN OUT NOCOPY VARCHAR2) IS
410
411 l_cost_type_id NUMBER;
412 l_return_status NUMBER;
413 l_return_err VARCHAR2(240);
414 l_status_mat NUMBER;
415 l_status_moh NUMBER;
416 l_list_id NUMBER;
417
418 BEGIN
419
420 O_RETURN_CODE := -99999;
421 l_return_err := ' ';
422
423 /* Get the cost type id, either frozen or average */
424 l_location := 400;
425 select primary_cost_method
426 into l_cost_type_id
427 from mtl_parameters
428 where organization_id = I_ORGANIZATION_ID;
429
430 /* Moved call to CSTPIICC.CSTPIICI before Material Cost Processing for bug 3219632 */
431 /* Create default costing information */
432
433 CSTPIICC.CSTPIICI( I_INVENTORY_ITEM_ID,
434 I_ORGANIZATION_ID,
435 I_USER_ID,
436 l_return_status,
437 l_return_err );
438
439 If (l_return_status <> 0) Then
440 O_RETURN_CODE := 99999;
441 O_ERR_TEXT := l_return_err;
442 return;
443 End if;
444
445
446 /**** Material Cost Processing *****/
447 If (nvl(I_MATERIAL_SUB_ELEM_ID,0) > 0 ) Then
448
449 INVPCOII.CSTPIICD (I_ORGANIZATION_ID ,
450 I_INVENTORY_ITEM_ID,
451 1,
452 I_MATERIAL_COST,
453 I_MATERIAL_SUB_ELEM_ID,
454 I_USER_ID,
455 I_LOGIN_ID,
456 I_REQ_ID,
457 I_PRGM_ID,
458 I_PRGM_APPL_ID,
459 l_status_mat,
460 l_return_err );
461 If l_status_mat <> 0 Then
462 O_RETURN_CODE := 99998;
463 O_ERR_TEXT := l_return_err;
464 return;
465 End if;
466
467 End if; /* For material */
468
469
470 /**** Material Cost Processing *****/
471 /* Material Overhead Cost Processing for std costing only */
472 If (l_cost_type_id = 1) then
473
474 If (nvl(I_MATERIAL_OH_SUB_ELEM_ID,0) > 0) Then
475
476 INVPCOII.CSTPIICD (I_ORGANIZATION_ID ,
477 I_INVENTORY_ITEM_ID,
478 2,
479 I_MATERIAL_OH_RATE,
480 I_MATERIAL_OH_SUB_ELEM_ID,
481 I_USER_ID,
482 I_LOGIN_ID,
483 I_REQ_ID,
484 I_PRGM_ID,
485 I_PRGM_APPL_ID,
486 l_status_moh,
487 l_return_err );
488 If l_status_moh <> 0 Then
489 O_RETURN_CODE := 99997;
490 O_ERR_TEXT := l_return_err;
491 return;
492 End if;
493
494 End if;
495
496 End if; /* std costing only for moh */
497
498 /* Removed code for bug 2603043 */
499
500 O_RETURN_CODE := 0;
501
502 EXCEPTION
503 WHEN OTHERS THEN
504 O_RETURN_CODE := SQLCODE;
505 O_ERR_TEXT := substr('CSTPPCOI -'||to_char(l_location)||' : '||SQLERRM, 1, 240);
506 END CSTPPCOI;
507
508 /*======================================================================*/
509 /* Procedure: CSTPIICP */
510 /* */
511 /* Description : */
512 /* Main program for Processing costing open interface */
513 /* Logic : */
514 /* Org Master_Org Cost_Org Costing table */
515 /* ------- ---------- -------- ------------- */
516 /* FRE FRE FRE FRE org */
517 /* CM1 FRE FRE FRE org */
518 /* CM2 FRE CM2 FRE org CM2 org */
519 /* */
520 /* */
521 /* Note: 06MAY96 NPARATE Added xset_id logic */
522 /* */
523 /*======================================================================*/
524
525 PROCEDURE CSTPIICP (
526 I_USER_ID IN NUMBER := -1,
527 I_LOGIN_ID IN NUMBER := -1,
528 I_REQ_ID IN NUMBER := -1,
529 I_PRGM_ID IN NUMBER := -1,
530 I_PRGM_APPL_ID IN NUMBER := -1,
531 O_RETURN_CODE OUT NOCOPY NUMBER,
532 O_RETURN_ERR OUT NOCOPY VARCHAR2,
533 xset_id IN NUMBER DEFAULT NULL) IS
534
535 l_cst_exist NUMBER;
536 l_return_status NUMBER;
537 l_status NUMBER;
538 l_org_id NUMBER;
539 l_trx_id NUMBER;
540 l_error_msg VARCHAR2(240);
541 l_err_out VARCHAR2(240);
542 l_cst_error exception;
543
544 -- Bug 2603043
545 l_list_id NUMBER;
546 -- commented following one code line added the next one to fix 7108920
547 -- Temp_Org_Id Number := 0;
548 Temp_Org_Id Number := -99999;
549 Temp_Cost_Org_Id Number := 0;
550 Temp_Cost_Type Number := 0;
551 New_list_ct Number := 0;
552 -- Bug 2603043
553
554 cursor cst1 is
555 select msii.transaction_id,
556 msii.inventory_item_id,
557 msii.material_cost,
558 msii.material_sub_elem,
559 msii.material_sub_elem_id,
560 msii.material_oh_rate,
561 msii.material_oh_sub_elem,
562 msii.material_oh_sub_elem_id,
563 mp.primary_cost_method,
564 mp.organization_id,
565 mp.master_organization_id,
566 mp.cost_organization_id
567 from mtl_system_items_interface msii,
568 mtl_parameters mp
569 where msii.process_flag = 4
570 and msii.transaction_type = 'CREATE'
571 and msii.costing_enabled_flag = 'Y'
572 and msii.organization_id = mp.organization_id
573 and mp.organization_id = mp.cost_organization_id
574 and msii.set_process_id = xset_id
575 order by mp.organization_id;
576
577
578 cursor cst2 is
579 select msii.transaction_id,
580 msii.inventory_item_id,
581 msii.material_cost,
582 msii.material_sub_elem,
583 msii.material_sub_elem_id,
584 msii.material_oh_rate,
585 msii.material_oh_sub_elem,
586 msii.material_oh_sub_elem_id,
587 mp.primary_cost_method,
588 mp.organization_id,
589 mp.master_organization_id,
590 mp.cost_organization_id
591 from mtl_system_items_interface msii,
592 mtl_parameters mp
593 where msii.process_flag = 4
594 and msii.transaction_type = 'CREATE'
595 and msii.costing_enabled_flag = 'Y'
596 and msii.organization_id = mp.organization_id
597 and msii.set_process_id = xset_id
598 and mp.organization_id <> mp.cost_organization_id
599 and mp.cost_organization_id = mp.master_organization_id
600 order by mp.organization_id;
601
602
603 cursor cst3 is
604 select msii.transaction_id,
605 msii.inventory_item_id,
606 msii.material_cost,
607 msii.material_sub_elem,
608 msii.material_sub_elem_id,
609 msii.material_oh_rate,
610 msii.material_oh_sub_elem,
611 msii.material_oh_sub_elem_id,
612 mp.primary_cost_method,
613 mp.organization_id,
614 mp.master_organization_id,
615 mp.cost_organization_id
616 from mtl_system_items_interface msii,
617 mtl_parameters mp
618 where msii.process_flag = 4
619 and msii.transaction_type = 'CREATE'
620 and msii.costing_enabled_flag = 'Y'
621 and msii.organization_id = mp.organization_id
622 and msii.set_process_id = xset_id
623 and mp.organization_id = mp.cost_organization_id
624 and mp.organization_id <> mp.master_organization_id
625 order by mp.organization_id;
626
627 BEGIN
628
629 O_RETURN_CODE := -99999;
630
631 /*NP 06MAY96: All logic in this procedure is within cursors
632 ** and all cursors have already ben modified to take xset_id
633 ** so no further checks required.
634 */
635
636 l_location := 410;
637 select cst_lists_s.nextval
638 into l_list_id
639 from dual;
640
641 FOR cc1 IN cst1 LOOP
642 -- fix for 3425593
643 -- Temp_Cost_Org_Id := cc1.cost_organization_id;
644
645 -- commented following one code line added the next one to fix 7108920
646 -- If (Temp_Org_Id = 0) then
647 If (Temp_Org_Id = -99999) then
648 Temp_Org_Id := cc1.organization_id;
649 Temp_Cost_Type := cc1.primary_cost_method;
650 -- fix for 3425593
651 Temp_Cost_Org_Id := cc1.cost_organization_id;
652 end if;
653
654 INVPCOII.CSTPPCOI ( cc1.cost_organization_id,
655 cc1.inventory_item_id,
656 cc1.material_cost,
657 cc1.material_sub_elem_id,
658 cc1.material_oh_rate,
659 cc1.material_oh_sub_elem_id,
660 I_USER_ID,
661 I_LOGIN_ID,
662 I_REQ_ID,
663 I_PRGM_ID,
664 I_PRGM_APPL_ID,
665 l_return_status,
666 l_error_msg);
667
668
669 -- Bug 2603043
670 if (Temp_Org_Id <> cc1.organization_id) then
671 CSTPUMEC.CSTPERIC (Temp_Cost_Org_Id,
672 Temp_Cost_type,
673 l_list_id,
674 I_USER_ID,
675 I_REQ_ID,
676 I_PRGM_ID,
677 I_PRGM_APPL_ID,
678 l_return_status);
679
680 l_location := 430;
681 delete from cst_lists
682 where list_id = l_list_id;
683
684 Temp_Org_Id := cc1.organization_id;
685 Temp_Cost_Type := cc1.primary_cost_method;
686 -- fix for 3425593
687 Temp_Cost_Org_Id := cc1.cost_organization_id;
688 end if;
689
690 l_location := 420;
691 insert into cst_lists (LIST_ID,ENTITY_ID)
692 values (l_list_id,cc1.inventory_item_id);
693
694 If (l_return_status <> 0) Then
695 l_org_id := cc1.organization_id;
696 l_trx_id := cc1.transaction_id;
697 raise l_cst_error;
698 End if;
699 END LOOP; /* cst1 cursor */
700
701 -- Calling this for the last org for cc1 cursor
702 CSTPUMEC.CSTPERIC (Temp_Cost_Org_Id,
703 Temp_Cost_type,
704 l_list_id,
705 I_USER_ID,
706 I_REQ_ID,
707 I_PRGM_ID,
708 I_PRGM_APPL_ID,
709 l_return_status);
710
711
712 l_location := 480;
713 delete from cst_lists
714 where list_id = l_list_id;
715
716 -- commented following one code line added the next one to fix 7108920
717 -- Temp_Org_Id := 0;
718 Temp_Org_Id := -99999;
719 Temp_Cost_Org_Id := 0;
720 Temp_Cost_Type := 0;
721
722 -- Completed Calling this for the last org for cc1 cursor
723
724 FOR cc2 IN cst2 LOOP
725
726 -- fix for 3425593
727 -- Temp_Cost_Org_Id := cc2.cost_organization_id;
728 /* Check the existence of the costing org for this item */
729 select count(*)
730 into l_cst_exist
731 from cst_item_costs
732 where inventory_item_id = cc2.inventory_item_id
733 and organization_id = cc2.cost_organization_id
734 and cost_type_id = cc2.primary_cost_method;
735
736 If (l_cst_exist = 0) then
737
738 -- commented following one code line added the next one to fix 7108920
739 -- If (Temp_Org_Id = 0) then
740 If (Temp_Org_Id = -99999) then
741 Temp_Org_Id := cc2.organization_id;
742 Temp_Cost_Type := cc2.primary_cost_method;
743 -- fix for 3425593
744 Temp_Cost_Org_Id := cc2.cost_organization_id;
745 end if;
746
747 INVPCOII.CSTPPCOI ( cc2.cost_organization_id,
748 cc2.inventory_item_id,
749 cc2.material_cost,
750 cc2.material_sub_elem_id,
751 cc2.material_oh_rate,
752 cc2.material_oh_sub_elem_id,
753 I_USER_ID,
754 I_LOGIN_ID,
755 I_REQ_ID ,
756 I_PRGM_ID,
757 I_PRGM_APPL_ID,
758 l_return_status,
759 l_error_msg);
760
761 -- Bug 2603043
762 if (Temp_Org_Id <> cc2.organization_id) then
763 CSTPUMEC.CSTPERIC (Temp_Cost_Org_Id,
764 Temp_Cost_type,
765 l_list_id,
766 I_USER_ID,
767 I_REQ_ID,
768 I_PRGM_ID,
769 I_PRGM_APPL_ID,
770 l_return_status);
771
772
773 l_location := 450;
774 delete from cst_lists
775 where list_id = l_list_id;
776
777 Temp_Org_Id := cc2.organization_id;
778 Temp_Cost_Type := cc2.primary_cost_method;
779 -- fix for 3425593
780 Temp_Cost_Org_Id := cc2.cost_organization_id;
781 end if;
782
783 l_location := 440;
784 insert into cst_lists (LIST_ID,ENTITY_ID)
785 values (l_list_id,cc2.inventory_item_id);
786
787 If (l_return_status <> 0) Then
788 l_org_id := cc2.organization_id;
789 l_trx_id := cc2.transaction_id;
790 raise l_cst_error;
791 End if;
792
793 End if;
794
795 END LOOP; /* cst2 cursor */
796
797 select count(*)
798 into New_List_ct
799 from cst_lists
800 where list_id = l_list_id
801 and rownum =1;
802
803 if (New_list_ct = 1) then
804 -- Calling this for the last org for cc2 cursor
805 CSTPUMEC.CSTPERIC (Temp_Cost_Org_Id,
806 Temp_Cost_type,
807 l_list_id,
808 I_USER_ID,
809 I_REQ_ID,
810 I_PRGM_ID,
811 I_PRGM_APPL_ID,
812 l_return_status);
813
814
815 l_location := 480;
816 delete from cst_lists
817 where list_id = l_list_id;
818 End if;
819 -- commented following one code line added the next one to fix 7108920
820 -- Temp_Org_Id := 0;
821 Temp_Org_Id := -99999;
822 Temp_Cost_Org_Id := 0;
823 Temp_Cost_Type := 0;
824 New_list_ct := 0;
825
826 -- Completed Calling this for the last org for cc2 cursor
827
828 FOR cc3 IN cst3 LOOP
829
830 -- fix for 3425593
831 -- Temp_Cost_Org_Id := cc3.master_organization_id;
832 /* Check the existence of the master org for this item */
833 select count(*)
834 into l_cst_exist
835 from cst_item_costs
836 where inventory_item_id = cc3.inventory_item_id
837 and organization_id = cc3.master_organization_id
838 and cost_type_id = cc3.primary_cost_method;
839
840 If (l_cst_exist = 0) then
841
842 -- commented following one code line added the next one to fix 7108920
843 -- If (Temp_Org_Id = 0) then
844 If (Temp_Org_Id = -99999) then
845 Temp_Org_Id := cc3.organization_id;
846 Temp_Cost_Type := cc3.primary_cost_method;
847 -- fix for 3425593
848 Temp_Cost_Org_Id := cc3.master_organization_id;
849 end if;
850
851 INVPCOII.CSTPPCOI ( cc3.master_organization_id,
852 cc3.inventory_item_id,
853 NULL,
854 NULL,
855 NULL,
856 NULL,
857 I_USER_ID,
858 I_LOGIN_ID,
859 I_REQ_ID,
860 I_PRGM_ID,
861 I_PRGM_APPL_ID,
862 l_return_status,
863 l_error_msg);
864
865 -- Bug 2603043
866 if (Temp_Org_Id <> cc3.organization_id) then
867 CSTPUMEC.CSTPERIC (Temp_Cost_Org_Id,
868 Temp_Cost_type,
869 l_list_id,
870 I_USER_ID,
871 I_REQ_ID,
872 I_PRGM_ID,
873 I_PRGM_APPL_ID,
874 l_return_status);
875
876
877 l_location := 470;
878 delete from cst_lists
879 where list_id = l_list_id;
880
881 Temp_Org_Id := cc3.organization_id;
882 Temp_Cost_Type := cc3.primary_cost_method;
883 -- fix for 3425593
884 Temp_Cost_Org_Id := cc3.master_organization_id;
885 end if;
886
887 l_location := 460;
888 insert into cst_lists (LIST_ID,ENTITY_ID)
889 values (l_list_id,cc3.inventory_item_id);
890
891 If (l_return_status <> 0) Then
892 l_org_id := cc3.organization_id;
893 l_trx_id := cc3.transaction_id;
894 raise l_cst_error;
895 End if;
896
897 End if;
898
899 END LOOP; /* cst3 cursor */
900 -- Calling this for the last org for cc3 cursor
901 select count(*)
902 into New_List_ct
903 from cst_lists
904 where list_id = l_list_id
905 and rownum =1;
906
907 if (New_list_ct = 1 ) then
908 CSTPUMEC.CSTPERIC (Temp_Cost_Org_Id,
909 Temp_Cost_type,
910 l_list_id,
911 I_USER_ID,
912 I_REQ_ID,
913 I_PRGM_ID,
914 I_PRGM_APPL_ID,
915 l_return_status);
916
917
918 l_location := 480;
919 delete from cst_lists
920 where list_id = l_list_id;
921
922 end if;
923 -- commented following one code line added the next one to fix 7108920
924 -- Temp_Org_Id := 0;
925 Temp_Org_Id := -99999;
926 Temp_Cost_Org_Id := 0;
927 Temp_Cost_Type := 0;
928 New_list_ct := 0;
929 -- Completed Calling this for the last org for cc3 cursor
930
931 O_RETURN_CODE := 0;
932
933 exception
934 when l_cst_error then
935 O_RETURN_CODE := l_return_status;
936 O_RETURN_ERR := l_error_msg;
937 l_status := INVPUOPI.mtl_log_interface_err(
938 l_org_id,
939 I_USER_ID,
940 I_LOGIN_ID,
941 I_PRGM_APPL_ID,
942 I_PRGM_ID,
943 I_REQ_ID,
944 l_trx_id,
945 l_error_msg,
946 null,
947 'MTL_SYSTEM_ITEMS_INTERFACE',
948 'CST_ERR_IN_OI',
949 l_err_out);
950 WHEN OTHERS THEN
951 O_RETURN_CODE := SQLCODE;
952 O_RETURN_ERR := substr('CSTPIICP -'||to_char(l_location)||' : '||SQLERRM,1, 240);
953
954 END CSTPIICP;
955
956
957 END INVPCOII;