[Home] [Help]
PACKAGE BODY: APPS.CSTPPCAT
Source
1 PACKAGE BODY CSTPPCAT AS
2 /* $Header: CSTPPCAB.pls 120.1 2006/05/16 01:42:15 ggautam noship $ */
3
4 PROCEDURE CSTPCCAT (
5 I_INVENTORY_ITEM_ID IN NUMBER,
6 I_ORGANIZATION_ID IN NUMBER,
7 I_LAST_UPDATED_BY IN NUMBER,
8 I_COST_TYPE_ID IN NUMBER,
9 I_ITEM_TYPE IN NUMBER,
10 I_LOT_SIZE IN NUMBER,
11 I_SHRINKAGE_RATE IN NUMBER,
12
13 O_RETURN_CODE OUT NOCOPY NUMBER,
14 O_RETURN_ERR OUT NOCOPY VARCHAR2
15 ) IS
16 row_count NUMBER;
17 p_cost_method MTL_PARAMETERS.PRIMARY_COST_METHOD%TYPE;
18 retval NUMBER;
19 retmsg VARCHAR(100);
20 BEGIN
21
22 /*
23 * get the primary cost method. the primary cost method matches
24 * the code for the valuation cost type for that method.
25 *
26 * 1 = Standard costing/Frozen cost type
27 * 2 = Average costing/Average cost type
28 */
29 select primary_cost_method
30 into p_cost_method
31 from mtl_parameters
32 where organization_id = I_ORGANIZATION_ID;
33
34
35 /*
36 * check if previous-level costs exist, or if this-level
37 * "non-material overhead" costs exist for the item in the
38 * valuation cost type.
39 */
40 select count(*)
41 into row_count
42 from cst_item_cost_details
43 where organization_id = I_ORGANIZATION_ID
44 and inventory_item_id = I_INVENTORY_ITEM_ID
45 and cost_type_id = p_cost_method
46 and (level_type = 2
47 OR
48 cost_element_id <> 2);
49
50 if (row_count > 0) then
51 return;
52 end if;
53
54
55 /*
56 * check if any WIP transactions have been performed on the item
57 */
58 select count(*)
59 into row_count
60 from wip_transactions
61 where organization_id = I_ORGANIZATION_ID
62 and primary_item_id = I_INVENTORY_ITEM_ID;
63
64 if (row_count > 0) then
65 return;
66 end if;
67
68 select count(*)
69 into row_count
70 from wip_move_transactions
71 where organization_id = I_ORGANIZATION_ID
72 and primary_item_id = I_INVENTORY_ITEM_ID;
73
74 if (row_count > 0) then
75 return;
76 end if;
77
78 /*
79 * check if any material transactions have been performed on the item
80 */
81 select count(*)
82 into row_count
83 from mtl_material_transactions
84 where organization_id = I_ORGANIZATION_ID
85 and inventory_item_id = I_INVENTORY_ITEM_ID;
86
87 if (row_count > 0) then
88 return;
89 end if;
90
91
92 /*
93 * delete all previously existing costs
94 */
95 delete
96 from cst_item_cost_details
97 where organization_id = I_ORGANIZATION_ID
98 and inventory_item_id = I_INVENTORY_ITEM_ID
99 and cost_type_id = p_cost_method;
100
101 /*
102 * Bug FP 5218221: The previously existing costs were not being deleted from the
103 * Summary table - CST_ITEM_COSTS, causing discrepancy between CIC and CICD.
104 */
105 UPDATE cst_item_costs
106 SET pl_material = 0, pl_material_overhead = 0,
107 pl_resource = 0, pl_outside_processing = 0,
108 pl_overhead = 0, tl_material = 0,
109 tl_material_overhead = 0, tl_resource = 0,
110 tl_outside_processing = 0, tl_overhead = 0,
111 material_cost = 0, material_overhead_cost = 0,
112 resource_cost = 0, outside_processing_cost = 0,
113 overhead_cost = 0, pl_item_cost = 0,
114 tl_item_cost = 0, item_cost = 0,
115 unburdened_cost = 0, burden_cost = 0,
116 last_update_date = SYSDATE,
117 last_updated_by = i_last_updated_by
118 WHERE inventory_item_id = i_inventory_item_id
119 AND organization_id = i_organization_id
120 AND cost_type_id = p_cost_method;
121
122
123 /*
124 * now call the function to assign material overhead defaults
125 * (CSTPIDIO)
126 */
127 CSTPIDIC.CSTPIDIO(I_INVENTORY_ITEM_ID,
128 I_ORGANIZATION_ID,
129 I_LAST_UPDATED_BY,
130 I_COST_TYPE_ID,
131 I_ITEM_TYPE,
132 I_LOT_SIZE,
133 I_SHRINKAGE_RATE,
134 retval,
135 retmsg);
136
137
138 O_RETURN_CODE := retval;
139 O_RETURN_ERR := retmsg;
140
141 End CSTPCCAT;
142
143 End CSTPPCAT;