[Home] [Help]
PACKAGE BODY: APPS.CSTPACOV
Source
1 PACKAGE BODY CSTPACOV AS
2 /* $Header: CSTACOVB.pls 120.0.12010000.3 2008/11/13 12:17:35 smsasidh ship $ */
3
4 PROCEDURE ins_overhead(
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) IS
15
16 p_location NUMBER;
17 p_dummy NUMBER;
18 p_category_set_id NUMBER;
19 p_category_id NUMBER;
20
21 BEGIN
22
23 O_RETURN_ERR := ' ';
24
25 BEGIN
26 SELECT d.category_set_id, c.category_id
27 INTO p_category_set_id,
28 p_category_id
29 FROM mtl_default_category_sets d,
30 mtl_item_categories c
31 WHERE d.functional_area_id = 5
32 AND c.category_set_id = d.category_set_id
33 AND c.inventory_item_id = I_INVENTORY_ITEM_ID
34 AND c.organization_id = I_ORGANIZATION_ID;
35
36 EXCEPTION
37 WHEN NO_DATA_FOUND THEN
38 O_RETURN_CODE := SQLCODE;
39 O_RETURN_ERR := 'CSTPACOV(1):' || substrb(SQLERRM,1,68);
40 RETURN;
41 WHEN OTHERS THEN
42 O_RETURN_CODE := SQLCODE;
43 O_RETURN_ERR := 'CSTPACOV(1):' || substrb(SQLERRM,1,68);
44 RETURN;
45 END;
46
47 p_location := 10;
48
49 /* Added for bug 7237799 */
50 IF I_COST_TYPE_ID <> 1 THEN
51
52 p_location := 12;
53
54 DELETE FROM cst_item_cost_details
55 WHERE inventory_item_id = I_INVENTORY_ITEM_ID
56 AND organization_id = I_ORGANIZATION_ID
57 AND cost_type_id = I_COST_TYPE_ID;
58
59 DELETE FROM cst_item_costs
60 WHERE inventory_item_id = I_INVENTORY_ITEM_ID
61 AND organization_id = I_ORGANIZATION_ID
62 AND cost_type_id = I_COST_TYPE_ID;
63
64 END IF;
65
66 p_location := 15;
67
68 INSERT INTO cst_item_cost_details
69 (inventory_item_id,
70 organization_id,
71 cost_type_id,
72 last_update_date,
73 last_updated_by,
74 creation_date,
75 created_by,
76 level_type,
77 resource_id,
78 resource_rate,
79 activity_id,
80 basis_type,
81 item_units,
82 activity_units,
83 usage_rate_or_amount,
84 basis_factor,
85 net_yield_or_shrinkage_factor,
86 item_cost,
87 rollup_source_type,
88 cost_element_id
89 )
90 SELECT
91 I_INVENTORY_ITEM_ID,
92 I_ORGANIZATION_ID,
93 I_COST_TYPE_ID,
94 SYSDATE,
95 I_LAST_UPDATED_BY,
96 SYSDATE,
97 I_LAST_UPDATED_BY,
98 1,
99 material_overhead_id,
100 1,
101 activity_id,
102 basis_type,
103 item_units,
104 activity_units,
105 usage_rate_or_amount,
106 DECODE(basis_type, 1,1,
107 2,1 / NVL(I_LOT_SIZE,1),
108 3,0,
109 4,0,
110 5,0,
111 6,NVL(activity_units,0)/NVL(item_units,1),
112 0),
113 DECODE(NVL(I_SHRINKAGE_RATE,-9),-9,1,
114 1 / (1 - I_SHRINKAGE_RATE)),
115 usage_rate_or_amount *
116 DECODE(basis_type, 1,1,
117 2,1 / NVL(I_LOT_SIZE,1),
118 3,0,
119 4,0,
120 5,0,
121 6,NVL(activity_units,0)/NVL(item_units,1),
122 0) *
123 DECODE(NVL(I_SHRINKAGE_RATE,-9),-9,1,
124 1 / (1 - I_SHRINKAGE_RATE)),
125 1, /* rollup soure type = user defined */
126 br.cost_element_id
127 FROM cst_item_overhead_defaults ciod,
128 bom_resources br
129 WHERE NOT EXISTS /* Don't insert if item already exists */
130 (SELECT /*+ INDEX(cicd CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
131 'X'
132 FROM cst_item_cost_details cicd
133 WHERE inventory_item_id = I_INVENTORY_ITEM_ID
134 AND organization_id = I_ORGANIZATION_ID
135 AND cost_type_id = I_COST_TYPE_ID)
136 AND ciod.organization_id = I_ORGANIZATION_ID
137 and br.resource_id = material_overhead_id
138 AND (
139 ( category_id is NULL
140 AND item_type = 3 /* all items */
141 AND NOT EXISTS
142 (SELECT 'X'
143 FROM cst_item_overhead_defaults ciod2
144 WHERE category_set_id = p_category_set_id
145 AND organization_id = I_ORGANIZATION_ID
146 AND category_id = p_category_id
147 AND ciod2.material_overhead_id = ciod.material_overhead_id
148 AND item_type = I_ITEM_TYPE)
149 AND NOT EXISTS
150 (SELECT 'X'
151 FROM cst_item_overhead_defaults ciod2
152 WHERE category_set_id = p_category_set_id
153 AND organization_id = I_ORGANIZATION_ID
154 AND category_id = p_category_id
155 AND ciod2.material_overhead_id = ciod.material_overhead_id
156 AND item_type = 3)
157 AND NOT EXISTS
158 (SELECT 'X'
159 FROM cst_item_overhead_defaults ciod2
160 WHERE category_id is NULL
161 AND organization_id = I_ORGANIZATION_ID
162 AND ciod2.material_overhead_id = ciod.material_overhead_id
163 AND item_type = I_ITEM_TYPE)
164 )
165 OR
166 ( category_id is NULL
167 AND item_type = I_ITEM_TYPE
168 AND NOT EXISTS
169 (SELECT 'X'
170 FROM cst_item_overhead_defaults ciod2
171 WHERE category_set_id = p_category_set_id
172 AND organization_id = I_ORGANIZATION_ID
173 AND category_id = p_category_id
174 AND ciod2.material_overhead_id = ciod.material_overhead_id
175 AND item_type = I_ITEM_TYPE)
176 AND NOT EXISTS
177 (SELECT 'X'
178 FROM cst_item_overhead_defaults ciod2
179 WHERE category_set_id = p_category_set_id
180 AND organization_id = I_ORGANIZATION_ID
181 AND category_id = p_category_id
182 AND ciod2.material_overhead_id = ciod.material_overhead_id
183 AND item_type = 3) /* all items */
184 )
185 OR
186 ( category_set_id = p_category_set_id
187 AND category_id = p_category_id
188 AND item_type = 3 /* all items */
189 AND NOT EXISTS
190 (SELECT 'X'
191 FROM cst_item_overhead_defaults ciod2
192 WHERE category_set_id = p_category_set_id
193 AND organization_id = I_ORGANIZATION_ID
194 AND category_id = p_category_id
195 AND ciod2.material_overhead_id = ciod.material_overhead_id
196 AND item_type = I_ITEM_TYPE)
197 )
198 OR
199 ( category_set_id = p_category_set_id
200 AND category_id = p_category_id
201 AND item_type = I_ITEM_TYPE
202 )
203 );
204
205 /* Update CIC only if the item exists in CICD. */
206 p_location := 20;
207
208 select /*+ INDEX (CICD CST_ITEM_COST_DETAILS_N1) */ -- Added for bug 6908147
209 count(1)
210 into p_dummy
211 from cst_item_cost_details cicd
212 where inventory_item_id = I_INVENTORY_ITEM_ID
213 AND organization_id = I_ORGANIZATION_ID
214 AND cost_type_id = I_COST_TYPE_ID
215 AND rownum <2; -- Added for bug 6908147
216
217 IF p_dummy > 0 THEN
218
219 p_location := 30;
220
221 IF I_COST_TYPE_ID <> 1 THEN
222
223 p_location := 40;
224
225 INSERT INTO cst_item_costs
226 (
227 inventory_item_id,
228 organization_id,
229 cost_type_id,
230 last_update_date,
231 last_updated_by,
232 creation_date,
233 created_by,
234 defaulted_flag,
235 shrinkage_rate,
236 lot_size,
237 based_on_rollup_flag,
238 inventory_asset_flag)
239 VALUES (I_INVENTORY_ITEM_ID,
240 I_ORGANIZATION_ID,
241 I_COST_TYPE_ID,
242 sysdate,
243 I_LAST_UPDATED_BY,
244 sysdate,
245 I_LAST_UPDATED_BY,
246 2,
247 0,
248 I_LOT_SIZE,
249 I_ITEM_TYPE,
250 1);
251
252 END IF;
253
254 UPDATE cst_item_costs
255 SET (
256 tl_material, tl_material_overhead,
257 tl_resource, tl_outside_processing,
258 tl_overhead,
259 material_cost, material_overhead_cost,
260 resource_cost, outside_processing_cost,
261 overhead_cost,
262 tl_item_cost,
263 item_cost,
264 unburdened_cost,
265 burden_cost,
266 last_update_date,
267 last_updated_by
268 ) = (
269 SELECT tl_material, tl_material_overhead,
270 tl_resource, tl_outside_processing,
271 tl_overhead,
272 material_cost, material_overhead_cost,
273 resource_cost, outside_processing_cost,
274 overhead_cost,
275 tl_item_cost,
276 item_cost,
277 unburdened_cost,
278 burden_cost,
279 sysdate,
280 I_LAST_UPDATED_BY
281 FROM cst_item_costs_view
282 WHERE inventory_item_id = I_INVENTORY_ITEM_ID
283 AND organization_id = I_ORGANIZATION_ID
284 AND cost_type_id = I_COST_TYPE_ID
285 )
286 WHERE inventory_item_id = I_INVENTORY_ITEM_ID
287 AND organization_id = I_ORGANIZATION_ID
288 AND cost_type_id = I_COST_TYPE_ID;
289
290 END IF;
291
292 O_RETURN_CODE := 0;
293 RETURN;
294
295 END ins_overhead;
296
297 END CSTPACOV;
298