[Home] [Help]
PACKAGE BODY: APPS.CSTPPCLM
Source
1 PACKAGE BODY CSTPPCLM AS
2 /* $Header: CSTPCLMB.pls 120.3.12010000.2 2008/08/08 12:31:36 smsasidh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPPCLM';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 /*------------------------------------------------------------------
8 | PROCEDURE layer_id
9 |
10 | Obtain quantity layer id and cost layer id if already exists
11 | Otherwise they are 0.
12 ------------------------------------------------------------------*/
13 PROCEDURE layer_id (
14 i_pac_period_id IN NUMBER,
15 i_legal_entity IN NUMBER,
16 i_item_id IN NUMBER,
17 i_cost_group_id IN NUMBER,
18 o_cost_layer_id OUT NOCOPY NUMBER,
19 o_quantity_layer_id OUT NOCOPY NUMBER,
20 o_err_num OUT NOCOPY NUMBER,
21 o_err_code OUT NOCOPY VARCHAR2,
22 o_err_msg OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_cpiql_cnt NUMBER;
26 l_cpic_cnt NUMBER;
27 retval NUMBER;
28 layer_errors EXCEPTION;
29 l_stmt_num NUMBER;
30 l_cpic_cost_layer_id NUMBER;
31 l_cpql_quantity_layer_id NUMBER;
32 l_cpql_count NUMBER;
33
34 l_api_name CONSTANT VARCHAR2(30) := 'layer_id';
35 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
36 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
37
38 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
39 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
40 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
41 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
42 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
43 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
44
45 -- Cursor to get cost_layer_id from CPIC
46 -- This is to check the existence of atleast one record
47 CURSOR c_cpic_cost_layer IS
48 SELECT cost_layer_id
49 FROM cst_pac_item_costs
50 WHERE pac_period_id = i_pac_period_id
51 AND inventory_item_id = i_item_id
52 AND cost_group_id = i_cost_group_id;
53
54 -- Cursor to get cost_layer_id from CPQL
55 -- This is to check the existence of atleast one record
56 -- and to get maximum quantity_layer_id
57 -- included count condition
58 CURSOR c_cpql_quantity_layer IS
59 SELECT COUNT(quantity_layer_id), MAX(quantity_layer_id)
60 FROM cst_pac_quantity_layers
61 WHERE pac_period_id = i_pac_period_id
62 AND inventory_item_id = i_item_id
63 AND cost_group_id = i_cost_group_id
64 GROUP BY quantity_layer_id;
65
66 BEGIN
67
68 IF (l_pLog) THEN
69 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
70 l_module || '.begin',
71 l_api_name || ' <<< Parameters: ' ||
72 ' i_pac_period_id: ' || i_pac_period_id ||
73 ' i_legal_entity: ' || i_legal_entity ||
74 ' i_item_id: ' || i_item_id ||
75 ' i_cost_group_id: ' || i_cost_group_id);
76 END IF;
77
78 o_err_num := 0;
79 o_err_code := '';
80 o_err_msg := '';
81
82
83
84 l_stmt_num := 10;
85 -- Get cost_layer_id from CPIC
86 OPEN c_cpic_cost_layer;
87 FETCH c_cpic_cost_layer
88 INTO l_cpic_cost_layer_id;
89
90 IF c_cpic_cost_layer%FOUND THEN
91 l_cpic_cnt := 1;
92 ELSE
93 l_cpic_cnt := 0;
94 END IF;
95
96 CLOSE c_cpic_cost_layer;
97
98 l_stmt_num := 20;
99 -- Get cost_layer_id from CPQL
100 OPEN c_cpql_quantity_layer;
101 FETCH c_cpql_quantity_layer
102 INTO l_cpql_count, l_cpql_quantity_layer_id;
103
104 IF l_cpql_count > 0 THEN
105 l_cpiql_cnt := 1;
106 ELSE
107 l_cpiql_cnt := 0;
108 END IF;
109
110 CLOSE c_cpql_quantity_layer;
111
112
113 /*------------------------------------------------------------------
114 | Raise layer errors if :
115 | - Nothing in item cost table , and row exists in quantity layer table
116 | - Row exists in item cost table, and nothing in quantity layer table
117 ------------------------------------------------------------------*/
118 IF ( (l_cpic_cnt = 0 AND l_cpiql_cnt > 0) OR
119 (l_cpic_cnt > 0 AND l_cpiql_cnt = 0) ) THEN
120 raise layer_errors;
121 END IF;
122
123 IF (l_cpic_cnt = 0 AND l_cpiql_cnt = 0) THEN
124 l_stmt_num := 30;
125 o_cost_layer_id := 0;
126 o_quantity_layer_id := 0;
127 ELSE
128 l_stmt_num := 40;
129 o_cost_layer_id := l_cpic_cost_layer_id;
130
131 o_quantity_layer_id := l_cpql_quantity_layer_id;
132
133 END IF;
134
135 IF (l_pLog) THEN
136 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
137 l_module || '.end',
138 l_api_name || ' >>> Out Parameters: ' ||
139 ' o_cost_layer_id: ' || o_cost_layer_id ||
140 ' o_quantity_layer_id: ' || o_quantity_layer_id);
141 END IF;
142
143
144 EXCEPTION
145
146 WHEN NO_DATA_FOUND THEN
147 o_cost_layer_id := 0;
148 o_quantity_layer_id := 0;
149
150 WHEN layer_errors THEN
151 o_cost_layer_id := 0;
152 o_quantity_layer_id := 0;
153 o_err_num := SQLCODE;
154 o_err_msg := 'CSTPPCLM.LAYER_ID: layers inconsistency';
155 IF (l_exceptionLog) THEN
156 FND_LOG.STRING (FND_LOG.LEVEL_EXCEPTION,
157 l_module || '.' || l_stmt_num,
158 o_err_msg);
159 END IF;
160 WHEN others THEN
161 IF (l_uLog) THEN
162 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
163 l_module || '.' || l_stmt_num,
164 SQLERRM);
165 END IF;
166 o_cost_layer_id := 0;
167 o_quantity_layer_id := 0;
168 o_err_num := SQLCODE;
169 o_err_msg := 'CSTPPCLM.LAYER_ID:' || substrb(SQLERRM,1,150);
170
171 END layer_id;
172
173 PROCEDURE create_layer (
174 i_pac_period_id IN NUMBER,
175 i_legal_entity IN NUMBER,
176 i_item_id IN NUMBER,
177 i_cost_group_id IN NUMBER,
178 i_user_id IN NUMBER,
179 i_login_id IN NUMBER,
180 i_request_id IN NUMBER,
181 i_prog_id IN NUMBER,
182 i_prog_appl_id IN NUMBER,
183 o_cost_layer_id OUT NOCOPY NUMBER,
184 o_quantity_layer_id OUT NOCOPY NUMBER,
185 o_err_num OUT NOCOPY NUMBER,
186 o_err_code OUT NOCOPY VARCHAR2,
187 o_err_msg OUT NOCOPY VARCHAR2
188 )
189 IS
190 l_cost_layer_id NUMBER;
191 l_quantity_layer_id NUMBER;
192 l_stmt_num NUMBER;
193
194 l_api_name CONSTANT VARCHAR2(30) := 'create_layer';
195 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
196 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.' || l_full_name;
197
198 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
199 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
200 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
201 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
202 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
203 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
204
205 BEGIN
206
207 IF (l_pLog) THEN
208 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
209 l_module || '.begin',
210 l_api_name || ' <<<');
211 END IF;
212
213 o_err_num := 0;
214 o_err_code := '';
215 o_err_msg := '';
216
217 /*
218 ** check for existing layer
219 */
220 l_stmt_num := 10;
221 CSTPPCLM.layer_id(i_pac_period_id, i_legal_entity, i_item_id,
222 i_cost_group_id, l_cost_layer_id, l_quantity_layer_id,
223 o_err_num, o_err_code, o_err_msg);
224
225 IF ( l_cost_layer_id = 0) THEN
226 l_stmt_num := 20;
227 /*
228 ** if the cost_layer_id is 0, then the layer doesn't exist, so we
229 ** should create it in cst_pac_item_costs and cst_pac_quantity_layers
230 */
231 SELECT cst_pac_item_costs_s.nextval
232 INTO l_cost_layer_id
233 FROM dual;
234
235 l_stmt_num := 30;
236 SELECT bom.cst_pac_quantity_layers_s.nextval
237 INTO l_quantity_layer_id
238 FROM dual;
239
240 l_stmt_num := 40;
241 INSERT INTO cst_pac_item_costs (
242 cost_layer_id,
243 pac_period_id,
244 inventory_item_id,
245 cost_group_id,
246 total_layer_quantity,
247 buy_quantity,
248 make_quantity,
249 issue_quantity,
250 item_cost,
251 item_buy_cost,
252 item_make_cost,
253 begin_item_cost,
254 material_cost,
255 material_overhead_cost,
256 resource_cost,
257 overhead_cost,
258 outside_processing_cost,
259 pl_material,
260 pl_material_overhead,
261 pl_resource,
262 pl_outside_processing,
263 pl_overhead,
264 tl_material,
265 tl_material_overhead,
266 tl_resource,
267 tl_outside_processing,
268 tl_overhead,
269 pl_item_cost,
270 tl_item_cost,
271 unburdened_cost,
272 burden_cost,
273 last_update_date,
274 last_updated_by,
275 creation_date,
276 created_by,
277 request_id,
278 program_id,
279 program_application_id,
280 program_update_date,
281 last_update_login
282 )
283 VALUES (
284 l_cost_layer_id,
285 i_pac_period_id,
286 i_item_id,
287 i_cost_group_id,
288 0,
289 0,
290 0,
291 0,
292 0,
293 0,
294 0,
295 0,
296 0,
297 0,
298 0,
299 0,
300 0,
301 0,
302 0,
303 0,
304 0,
305 0,
306 0,
307 0,
308 0,
309 0,
310 0,
311 0,
312 0,
313 0,
314 0,
315 SYSDATE,
316 i_user_id,
317 SYSDATE,
318 i_user_id,
319 i_request_id,
320 i_prog_id,
321 i_prog_appl_id,
322 SYSDATE,
323 i_login_id
324 );
325
326 l_stmt_num := 50;
327 INSERT INTO cst_pac_item_cost_details (
328 cost_layer_id,
329 cost_element_id,
330 level_type,
331 item_cost,
332 item_buy_cost,
333 item_make_cost,
334 item_balance,
335 make_balance,
336 buy_balance,
337 last_update_date,
338 last_updated_by,
339 creation_date,
340 created_by,
341 request_id,
342 program_id,
343 program_application_id,
344 program_update_date,
345 last_update_login
346 )
347 VALUES (
348 l_cost_layer_id,
349 1,
350 1,
351 0,
352 0,
353 0,
354 0,
355 0,
356 0,
357 SYSDATE,
358 i_user_id,
359 SYSDATE,
360 i_user_id,
361 i_request_id,
362 i_prog_id,
363 i_prog_appl_id,
364 SYSDATE,
365 i_login_id
366
367 );
368
369 l_stmt_num := 60;
370 INSERT INTO cst_pac_quantity_layers (
371 quantity_layer_id,
372 cost_layer_id,
373 pac_period_id,
374 inventory_item_id,
375 cost_group_id,
376 layer_quantity,
377 begin_layer_quantity,
378 last_update_date,
379 last_updated_by,
380 creation_date,
381 created_by,
382 request_id,
383 program_id,
384 program_application_id,
385 program_update_date,
386 last_update_login
387 )
388 VALUES (
389 l_quantity_layer_id,
390 l_cost_layer_id,
391 i_pac_period_id,
392 i_item_id,
393 i_cost_group_id,
394 0,
395 0,
396 SYSDATE,
397 i_user_id,
398 SYSDATE,
399 i_user_id,
400 i_request_id,
401 i_prog_id,
402 i_prog_appl_id,
403 SYSDATE,
404 i_login_id
405 );
406
407 l_stmt_num := 70;
408 INSERT INTO cst_pac_period_balances (
409 pac_period_id,
410 cost_group_id,
411 inventory_item_id,
412 cost_layer_id,
413 quantity_layer_id,
414 cost_element_id,
415 level_type,
416 txn_category,
417 txn_category_qty,
418 txn_category_value,
419 period_quantity,
420 periodic_cost,
421 period_balance,
422 variance_amount,
423 last_update_date,
424 last_updated_by,
425 last_update_login,
426 created_by,
427 creation_date,
428 request_id,
429 program_application_id,
430 program_id,
431 program_update_date)
432 (SELECT i_pac_period_id,
433 i_cost_group_id,
434 i_item_id,
435 l_cost_layer_id,
436 l_quantity_layer_id,
437 1, -- cost element
438 1, -- level type
439 1, -- txn_category (Period Beginning)
440 0, -- txn_category_qty
441 0, -- txn_category_value
442 0, -- period_quantity
443 0, -- periodic_cost
444 0, -- period_balance
445 0, -- variance
446 sysdate,
447 i_user_id,
448 i_login_id,
449 i_user_id,
450 sysdate,
451 i_request_id,
452 i_prog_appl_id,
453 i_prog_id,
454 sysdate
455 from dual
456 -- Insert balance records in CPPB only for asset items. Check asset flag for
457 -- the item in the item master organization
458 where exists (select 1
459 from mtl_system_items msi, cst_cost_groups ccg
460 where msi.inventory_item_id = i_item_id
461 and msi.inventory_asset_flag = 'Y'
462 and msi.organization_id = ccg.organization_id
463 and ccg.cost_group_id = i_cost_group_id));
464
465 END IF;
466
467 o_cost_layer_id := l_cost_layer_id;
468 o_quantity_layer_id := l_quantity_layer_id;
469
470 IF (l_pLog) THEN
471 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
472 l_module || '.end',
473 l_api_name || ' >>> Out Parameters: ' ||
474 ' o_cost_layer_id: ' || o_cost_layer_id ||
475 ' o_quantity_layer_id: ' || o_quantity_layer_id);
476 END IF;
477
478 EXCEPTION
479
480 WHEN others THEN
481 IF (l_uLog) THEN
482 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
483 l_module || '.' || l_stmt_num,
484 SQLERRM);
485 END IF;
486 o_cost_layer_id := 0;
487 o_quantity_layer_id := 0;
488 o_err_num := SQLCODE;
489 o_err_msg := 'CSTPPCLM.CREATE_LAYER:' || substrb(SQLERRM,1,150);
490
491 END create_layer;
492
493 END CSTPPCLM;