1 PACKAGE BODY CSTPPCAT AS
2 /* $Header: CSTPPCAB.pls 120.1.12010000.2 2008/11/13 12:19:26 smsasidh ship $ */
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 p_avg_rates_cost_type MTL_PARAMETERS.AVG_RATES_COST_TYPE_ID%TYPE; --Added for 7237799
19 retval NUMBER;
20 retmsg VARCHAR(100);
21 BEGIN
22
23 /*
24 * get the primary cost method. the primary cost method matches
25 * the code for the valuation cost type for that method.
26 *
27 * 1 = Standard costing/Frozen cost type
28 * 2 = Average costing/Average cost type
29 */
30 select primary_cost_method, avg_rates_cost_type_id
31 into p_cost_method, p_avg_rates_cost_type
32 from mtl_parameters
33 where organization_id = I_ORGANIZATION_ID;
34
35 /*
36 * Added the below ELSE condition or bug 7237799
37 */
38 IF p_cost_method = 1 THEN
39 /*
40 * check if previous-level costs exist, or if this-level
41 * "non-material overhead" costs exist for the item in the
42 * valuation cost type.
43 */
44 select count(*)
45 into row_count
46 from cst_item_cost_details
47 where organization_id = I_ORGANIZATION_ID
48 and inventory_item_id = I_INVENTORY_ITEM_ID
49 and cost_type_id = p_cost_method
50 and (level_type = 2
51 OR
52 cost_element_id <> 2)
53 and ROWNUM < 2; -- Added for 7237799
54
55 if (row_count > 0) then
56 return;
57 end if;
58 ELSE
59 /*
60 * In Average, LIFO and FIFO Costing Organizations Default MOH Check.
61 * Check if previous-level costs exist, or if this-level
62 * "non-material overhead" costs exist for the item in the
63 * average rates cost type.
64 */
65 select count(*)
66 into row_count
67 from cst_item_cost_details
68 where organization_id = I_ORGANIZATION_ID
69 and inventory_item_id = I_INVENTORY_ITEM_ID
70 and cost_type_id = p_avg_rates_cost_type
71 and NOT (level_type = 1
72 AND (cost_element_id = 2
73 OR (cost_element_id = 1
74 AND item_cost = 0)))
75 and ROWNUM < 2; -- Added for 7237799
76
77 if (row_count > 0) then
78 return;
79 end if;
80 /*
81 * Added for bug 7237799
82 * check if there is any cst_quantiry_layers for this item
83 */
84 select count(*)
85 into row_count
86 from cst_quantity_layers
87 where organization_id = I_ORGANIZATION_ID
88 and inventory_item_id = I_INVENTORY_ITEM_ID
89 and ROWNUM < 2;
90
91 if (row_count > 0) then
92 return;
93 end if;
94
95 END IF;
96
97 /*
98 * Added for bug 7237799
99 * check if any On Hand Quantities are there for this item
100 */
101 select count(*)
102 into row_count
103 from mtl_onhand_quantities
104 where organization_id = I_ORGANIZATION_ID
105 and inventory_item_id = I_INVENTORY_ITEM_ID
106 and ROWNUM < 2;
107
108 if (row_count > 0) then
109 return;
110 end if;
111
112 /*
113 * check if any WIP transactions have been performed on the item
114 */
115 /* Commented for bug 7237799
116 select count(*)
117 into row_count
118 from wip_transactions
119 where organization_id = I_ORGANIZATION_ID
120 and primary_item_id = I_INVENTORY_ITEM_ID;
121
122 if (row_count > 0) then
123 return;
124 end if;
125
126 select count(*)
127 into row_count
128 from wip_move_transactions
129 where organization_id = I_ORGANIZATION_ID
130 and primary_item_id = I_INVENTORY_ITEM_ID;
131
132 if (row_count > 0) then
133 return;
134 end if;
135 */
136
137 /*
138 * check if any material transactions have been performed on the item
139 */
140 select count(*)
141 into row_count
142 from mtl_material_transactions
143 where organization_id = I_ORGANIZATION_ID
144 and inventory_item_id = I_INVENTORY_ITEM_ID
145 and ROWNUM < 2; -- Added for 7237799
146
147 if (row_count > 0) then
148 return;
149 end if;
150
151
152 /*
153 * delete all previously existing costs
154 */
155 delete
156 from cst_item_cost_details
157 where organization_id = I_ORGANIZATION_ID
158 and inventory_item_id = I_INVENTORY_ITEM_ID
159 and cost_type_id = p_cost_method;
160
161 /*
162 * Bug FP 5218221: The previously existing costs were not being deleted from the
163 * Summary table - CST_ITEM_COSTS, causing discrepancy between CIC and CICD.
164 */
165 UPDATE cst_item_costs
166 SET pl_material = 0, pl_material_overhead = 0,
167 pl_resource = 0, pl_outside_processing = 0,
168 pl_overhead = 0, tl_material = 0,
169 tl_material_overhead = 0, tl_resource = 0,
170 tl_outside_processing = 0, tl_overhead = 0,
171 material_cost = 0, material_overhead_cost = 0,
172 resource_cost = 0, outside_processing_cost = 0,
173 overhead_cost = 0, pl_item_cost = 0,
174 tl_item_cost = 0, item_cost = 0,
175 unburdened_cost = 0, burden_cost = 0,
176 last_update_date = SYSDATE,
177 last_updated_by = i_last_updated_by
178 WHERE inventory_item_id = i_inventory_item_id
179 AND organization_id = i_organization_id
180 AND cost_type_id = p_cost_method;
181
182
183 /*
184 * now call the function to assign material overhead defaults
185 * (CSTPIDIO)
186 */
187 CSTPIDIC.CSTPIDIO(I_INVENTORY_ITEM_ID,
188 I_ORGANIZATION_ID,
189 I_LAST_UPDATED_BY,
190 I_COST_TYPE_ID,
191 I_ITEM_TYPE,
192 I_LOT_SIZE,
193 I_SHRINKAGE_RATE,
194 retval,
195 retmsg);
196
197
198 O_RETURN_CODE := retval;
199 O_RETURN_ERR := retmsg;
200
201 End CSTPCCAT;
202
203 End CSTPPCAT;
[Home] [Help]