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