[Home] [Help]
PACKAGE BODY: APPS.CSTPCGUT
Source
1 PACKAGE BODY CSTPCGUT AS
2 /* $Header: CSTCGUTB.pls 120.1 2005/08/26 12:00:44 awwang noship $ */
3
4
5
6
7 ----------------------------------------------------------------------------
8 -- PROCEDURE --
9 -- get_cost_group --
10 -- --
11 -- DESCRIPTION --
12 -- Use this procedure to obatain cost groups based on account information.--
13 -- --
14 -- PURPOSE: --
15 -- Oracle Applications Rel 11i.2 --
16 -- --
17
18 -- HISTORY: --
19 -- 03/02/00 Sujit Dalai Created --
20 ----------------------------------------------------------------------------
21
22 PROCEDURE get_cost_group(x_return_status OUT NOCOPY VARCHAR2,
23 x_msg_count OUT NOCOPY NUMBER,
24 x_msg_data OUT NOCOPY VARCHAR2,
25 x_cost_group_id_tbl OUT NOCOPY CSTPCGUT.cost_group_tbl,
26 x_count OUT NOCOPY NUMBER,
27 p_material_account IN NUMBER default FND_API.G_MISS_NUM,
28 p_material_overhead_account IN NUMBER default FND_API.G_MISS_NUM,
29 p_resource_account IN NUMBER default FND_API.G_MISS_NUM,
30 p_overhead_account IN NUMBER default FND_API.G_MISS_NUM,
31 p_outside_processing_account IN NUMBER default FND_API.G_MISS_NUM,
32 p_expense_account IN NUMBER default FND_API.G_MISS_NUM,
33 p_encumbrance_account IN NUMBER default FND_API.G_MISS_NUM,
34 p_average_cost_var_account IN NUMBER default FND_API.G_MISS_NUM,
35 p_payback_mat_var_account IN NUMBER default FND_API.G_MISS_NUM,
36 p_payback_res_var_account IN NUMBER default FND_API.G_MISS_NUM,
37 p_payback_osp_var_account IN NUMBER default FND_API.G_MISS_NUM,
38 p_payback_moh_var_account IN NUMBER default FND_API.G_MISS_NUM,
39 p_payback_ovh_var_account IN NUMBER default FND_API.G_MISS_NUM,
40 p_organization_id IN NUMBER ,
41 p_cost_group_type_id IN NUMBER) IS
42
43 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
44 l_counter INTEGER := 0;
45 l_statement NUMBER;
46 l_miss_num NUMBER := FND_API.G_MISS_NUM;
47
48
49 CURSOR c_cost_group is SELECT ccg.cost_group_id
50 FROM cst_cost_groups ccg,
51 cst_cost_group_accounts cca
52 WHERE ccg.cost_group_id = cca.cost_group_id
53 AND NVL(ccg.organization_id, p_organization_id)
54 = cca.organization_id
55 AND ccg.cost_group_type = p_cost_group_type_id
56 AND sysdate <= nvl(ccg.disable_date, sysdate)
57 AND NVL(ccg.organization_id, p_organization_id )
58 = p_organization_id
59 AND (p_material_account = l_miss_num
60 OR (p_material_account IS NULL AND cca.material_account IS NULL)
61 OR p_material_account = cca.material_account
62 )
63 AND (p_material_overhead_account = l_miss_num
64 OR (p_material_overhead_account IS NULL AND cca.material_overhead_account IS NULL)
65 OR p_material_overhead_account = cca.material_overhead_account
66 )
67 AND (p_resource_account = l_miss_num
68 OR (p_resource_account IS NULL AND cca.resource_account IS NULL)
69 OR p_resource_account = cca.resource_account
70 )
71 AND (p_overhead_account = l_miss_num
72 OR (p_overhead_account IS NULL AND cca.overhead_account IS NULL)
73 OR p_overhead_account = cca.overhead_account
74 )
75 AND (p_outside_processing_account = l_miss_num
76 OR (p_outside_processing_account IS NULL AND cca.outside_processing_account IS NULL)
77 OR p_outside_processing_account = cca.outside_processing_account
78 )
79 AND (p_expense_account = l_miss_num
80 OR (p_expense_account IS NULL AND cca.expense_account IS NULL)
81 OR p_expense_account = cca.expense_account
82 )
83 AND (p_encumbrance_account = l_miss_num
84 OR (p_encumbrance_account IS NULL AND cca.encumbrance_account IS NULL)
85 OR p_encumbrance_account = cca.encumbrance_account
86 )
87 AND (p_average_cost_var_account = l_miss_num
88 OR (p_average_cost_var_account IS NULL AND cca.average_cost_var_account IS NULL)
89 OR p_average_cost_var_account = cca.average_cost_var_account
90 )
91 AND (p_payback_mat_var_account = l_miss_num
92 OR (p_payback_mat_var_account IS NULL AND cca.payback_mat_var_account IS NULL)
93 OR p_payback_mat_var_account = cca.payback_mat_var_account
94 )
95 AND (p_payback_res_var_account = l_miss_num
96 OR (p_payback_res_var_account IS NULL AND cca.payback_res_var_account IS NULL)
97 OR p_payback_res_var_account = cca.payback_res_var_account
98 )
99 AND (p_payback_osp_var_account = l_miss_num
100 OR (p_payback_osp_var_account IS NULL AND cca.payback_osp_var_account IS NULL)
101 OR p_payback_osp_var_account = cca.payback_osp_var_account
102 )
103 AND (p_payback_moh_var_account = l_miss_num
104 OR (p_payback_moh_var_account IS NULL AND cca.payback_moh_var_account IS NULL)
105 OR p_payback_moh_var_account = cca.payback_moh_var_account
106 )
107 AND (p_payback_ovh_var_account = l_miss_num
108 OR (p_payback_ovh_var_account IS NULL AND cca.payback_ovh_var_account IS NULL)
109 OR p_payback_ovh_var_account = cca.payback_ovh_var_account
110 );
111
112
113
114
115 BEGIN
116
117 IF p_organization_id IS NULL THEN
118
119 RAISE fnd_api.g_exc_error;
120 END IF;
121
122 FOR rec_cost_group IN c_cost_group LOOP
123
124 l_statement := 10;
125 l_counter := l_counter + 1;
126 x_cost_group_id_tbl( l_counter) := rec_cost_group.cost_group_id;
127
128 END LOOP;
129 l_statement := 20;
130 x_count := l_counter;
131 x_return_status := l_return_status;
132
133 EXCEPTION
134 WHEN fnd_api.g_exc_error THEN
135 x_return_status := fnd_api.g_ret_sts_error;
136
137 -- Get message count and data
138 fnd_msg_pub.count_and_get
139 ( p_count => x_msg_count
140 , p_data => x_msg_data
141 );
142 --
143 WHEN fnd_api.g_exc_unexpected_error THEN
144 x_return_status := fnd_api.g_ret_sts_unexp_error ;
145
146 -- Get message count and data
147 fnd_msg_pub.count_and_get
148 ( p_count => x_msg_count
149 , p_data => x_msg_data
150 );
151 --
152 WHEN OTHERS THEN
153 x_return_status := fnd_api.g_ret_sts_unexp_error ;
154 --
155 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
156 THEN
157 fnd_msg_pub.add_exc_msg
158 ( 'CSTPCGUT'
159 , 'GET_COST_GROUP : Statement -'||to_char(l_statement)
160 );
161
162 END IF;
163
164 -- Get message count and data
165 fnd_msg_pub.count_and_get
166 ( p_count => x_msg_count
167 , p_data => x_msg_data
168 );
169 END get_cost_group;
170
171
172 ----------------------------------------------------------------------------
173 -- PROCEDURE --
174 -- create_cost_group --
175 -- --
176 -- DESCRIPTION --
177 -- Use this procedure to create a new cost group. --
178 -- --
179 -- PURPOSE: --
180 -- Oracle Applications Rel 11i.2 --
181 -- --
182
183 -- HISTORY: --
184 -- 05/26/00 Sujit Dalai Created --
185 ----------------------------------------------------------------------------
186
187 PROCEDURE create_cost_group(x_return_status OUT NOCOPY VARCHAR2,
188 x_msg_count OUT NOCOPY NUMBER,
189 x_msg_data OUT NOCOPY VARCHAR2,
190 x_cost_group_id OUT NOCOPY NUMBER,
191 p_cost_group IN VARCHAR2,
192 p_material_account IN NUMBER default NULL,
193 p_material_overhead_account IN NUMBER default NULL,
194 p_resource_account IN NUMBER default NULL,
195 p_overhead_account IN NUMBER default NULL,
196 p_outside_processing_account IN NUMBER default NULL,
197 p_expense_account IN NUMBER default NULL,
198 p_encumbrance_account IN NUMBER default NULL,
199 p_average_cost_var_account IN NUMBER default NULL,
200 p_payback_mat_var_account IN NUMBER default NULL,
201 p_payback_res_var_account IN NUMBER default NULL,
202 p_payback_osp_var_account IN NUMBER default NULL,
203 p_payback_moh_var_account IN NUMBER default NULL,
204 p_payback_ovh_var_account IN NUMBER default NULL,
205 p_organization_id IN NUMBER,
206 p_cost_group_type_id IN NUMBER,
207 p_multi_org IN NUMBER DEFAULT 2) IS
208
209
210 l_last_updated_by NUMBER := fnd_global.user_id;
211 l_last_update_login NUMBER := fnd_global.login_id;
212 l_request_id NUMBER := fnd_global.conc_request_id;
213 l_program_application_id NUMBER := fnd_global.prog_appl_id;
214 l_program_id NUMBER := fnd_global.conc_program_id;
215 l_sysdate DATE := SYSDATE;
216 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
217 l_cost_group_id NUMBER;
218 l_statement NUMBER;
219 l_count NUMBER;
220 l_cost_group VARCHAR2(15);
221
222 BEGIN
223
224 SAVEPOINT create_CG;
225
226 l_statement := 10;
227 IF p_cost_group IS NOT NULL THEN
228 SELECT COUNT(*)
229 INTO l_count
230 FROM CST_COST_GROUPS
231 WHERE COST_GROUP = p_cost_group;
232
233 IF l_count <> 0 THEN
234 RAISE fnd_api.g_exc_error;
235 END IF;
236 END IF;
237
238 IF p_organization_id IS NULL THEN
239
240 RAISE fnd_api.g_exc_error;
241 END IF;
242
243 l_statement := 20;
244
245 SELECT cst_cost_groups_s.NEXTVAL
246 INTO l_cost_group_id
247 FROM dual;
248 l_statement := 30;
249
250 IF p_cost_group IS NULL THEN
251 l_cost_group := 'CG-'||to_char(l_cost_group_id);
252 ELSE
253 l_cost_group := p_cost_group;
254 END IF;
255
256
257 l_statement := 40;
258
259 INSERT INTO CST_COST_GROUPS( COST_GROUP_ID,
260 LAST_UPDATE_DATE,
261 LAST_UPDATED_BY,
262 CREATION_DATE,
263 CREATED_BY,
264 LAST_UPDATE_LOGIN,
265 REQUEST_ID,
266 PROGRAM_APPLICATION_ID,
267 PROGRAM_ID ,
268 PROGRAM_UPDATE_DATE,
269 ORGANIZATION_ID,
270 COST_GROUP,
271 COST_GROUP_TYPE)
272 VALUES (l_cost_group_id,
273 l_sysdate,
274 l_last_updated_by,
275 l_sysdate,
276 l_last_updated_by,
277 l_last_update_login,
278 l_request_id,
279 l_program_application_id,
280 l_program_id,
281 l_sysdate,
282 decode (p_multi_org, 1, NULL, p_organization_id),
283 l_cost_group,
284 p_cost_group_type_id);
285
286 l_statement := 50;
287
288 INSERT INTO CST_COST_GROUP_ACCOUNTS( COST_GROUP_ID,
289 ORGANIZATION_ID,
290 LAST_UPDATE_DATE,
291 LAST_UPDATED_BY,
292 CREATION_DATE,
293 CREATED_BY,
294 LAST_UPDATE_LOGIN,
295 REQUEST_ID,
296 PROGRAM_APPLICATION_ID,
297 PROGRAM_ID ,
298 PROGRAM_UPDATE_DATE,
299 MATERIAL_ACCOUNT,
300 MATERIAL_OVERHEAD_ACCOUNT,
301 RESOURCE_ACCOUNT,
302 OVERHEAD_ACCOUNT,
303 OUTSIDE_PROCESSING_ACCOUNT,
304 ENCUMBRANCE_ACCOUNT,
308 PAYBACK_RES_VAR_ACCOUNT,
305 EXPENSE_ACCOUNT,
306 AVERAGE_COST_VAR_ACCOUNT,
307 PAYBACK_MAT_VAR_ACCOUNT,
309 PAYBACK_OSP_VAR_ACCOUNT,
310 PAYBACK_MOH_VAR_ACCOUNT,
311 PAYBACK_OVH_VAR_ACCOUNT)
312 VALUES (l_cost_group_id,
313 p_organization_id,
314 l_sysdate,
315 l_last_updated_by,
316 l_sysdate,
317 l_last_updated_by,
318 l_last_update_login,
319 l_request_id,
320 l_program_application_id,
321 l_program_id,
322 l_sysdate,
323 p_material_account,
324 p_material_overhead_account,
325 p_resource_account,
326 p_overhead_account,
327 p_outside_processing_account,
328 p_encumbrance_account,
329 p_expense_account,
330 p_average_cost_var_account,
331 p_payback_mat_var_account,
332 p_payback_res_var_account,
333 p_payback_osp_var_account,
334 p_payback_moh_var_account,
335 p_payback_ovh_var_account);
336 x_cost_group_id := l_cost_group_id;
337 x_return_status := l_return_status;
338
339 EXCEPTION
340 WHEN fnd_api.g_exc_error THEN
341 x_return_status := fnd_api.g_ret_sts_error;
342
343 fnd_msg_pub.count_and_get
344 ( p_count => x_msg_count
345 , p_data => x_msg_data
346 );
347
348 ROLLBACK WORK TO SAVEPOINT create_CG;
349 --
350 WHEN fnd_api.g_exc_unexpected_error THEN
351 x_return_status := fnd_api.g_ret_sts_unexp_error ;
352
353 fnd_msg_pub.count_and_get
354 ( p_count => x_msg_count
355 , p_data => x_msg_data
356 );
357
358 ROLLBACK WORK TO SAVEPOINT create_CG;
359 --
360 WHEN OTHERS THEN
361 x_return_status := fnd_api.g_ret_sts_unexp_error ;
362 --
363 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
364 THEN
365 fnd_msg_pub.add_exc_msg
366 ( 'CSTPCGUT'
367 , 'CREATE_COST_GROUP : Statement -'||to_char(l_statement)
368 );
369 END IF;
370
371 fnd_msg_pub.count_and_get
372 ( p_count => x_msg_count
373 , p_data => x_msg_data
374 );
375 ROLLBACK WORK TO SAVEPOINT create_CG;
376 END;
377
378 ----------------------------------------------------------------------------
379 -- PROCEDURE --
380 -- get_cost_group_accounts --
381 -- --
382 -- DESCRIPTION --
383 -- Use this procedure to obatain cost groups based on account information.--
384 -- --
385 -- PURPOSE: --
386 -- Oracle Applications Rel 11i.2 --
387 -- --
388
389 -- HISTORY: --
390 -- 05/26/00 Sujit Dalai Created --
391 ----------------------------------------------------------------------------
392 PROCEDURE get_cost_group_accounts(x_return_status OUT NOCOPY VARCHAR2,
393 x_msg_count OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2,
395 x_material_account OUT NOCOPY NUMBER,
396 x_material_overhead_account OUT NOCOPY NUMBER,
397 x_resource_account OUT NOCOPY NUMBER,
398 x_overhead_account OUT NOCOPY NUMBER,
399 x_outside_processing_account OUT NOCOPY NUMBER,
400 x_expense_account OUT NOCOPY NUMBER,
401 x_encumbrance_account OUT NOCOPY NUMBER,
402 x_average_cost_var_account OUT NOCOPY NUMBER,
403 x_payback_mat_var_account OUT NOCOPY NUMBER,
404 x_payback_res_var_account OUT NOCOPY NUMBER,
405 x_payback_osp_var_account OUT NOCOPY NUMBER,
406 x_payback_moh_var_account OUT NOCOPY NUMBER,
410 ) IS
407 x_payback_ovh_var_account OUT NOCOPY NUMBER,
408 p_cost_group_id IN NUMBER,
409 p_organization_id IN NUMBER
411
412
413 l_statement NUMBER;
414 BEGIN
415
416
417 IF p_organization_id IS NULL OR
418 p_cost_group_id IS NULL THEN
419
420 RAISE fnd_api.g_exc_error;
421 END IF;
422
423
424 l_statement := 10;
425
426 SELECT MATERIAL_ACCOUNT,
427 MATERIAL_OVERHEAD_ACCOUNT,
428 RESOURCE_ACCOUNT,
429 OVERHEAD_ACCOUNT,
430 OUTSIDE_PROCESSING_ACCOUNT,
431 ENCUMBRANCE_ACCOUNT,
432 EXPENSE_ACCOUNT,
433 AVERAGE_COST_VAR_ACCOUNT,
434 PAYBACK_MAT_VAR_ACCOUNT,
435 PAYBACK_RES_VAR_ACCOUNT,
436 PAYBACK_OSP_VAR_ACCOUNT,
437 PAYBACK_MOH_VAR_ACCOUNT,
438 PAYBACK_OVH_VAR_ACCOUNT
439
440 INTO x_material_account,
441 x_material_overhead_account,
442 x_resource_account,
443 x_overhead_account,
444 x_outside_processing_account,
445 x_encumbrance_account,
446 x_expense_account,
447 x_average_cost_var_account,
448 x_payback_mat_var_account,
449 x_payback_res_var_account,
450 x_payback_osp_var_account,
451 x_payback_moh_var_account,
452 x_payback_ovh_var_account
453 FROM CST_COST_GROUP_ACCOUNTS
454 WHERE COST_GROUP_ID = p_cost_group_id
455 AND ORGANIZATION_ID = p_organization_id ;
456
457 x_return_status := fnd_api.g_ret_sts_success;
458
459 EXCEPTION
460 WHEN fnd_api.g_exc_error THEN
461 x_return_status := fnd_api.g_ret_sts_error;
462
463 fnd_msg_pub.count_and_get
464 ( p_count => x_msg_count
465 , p_data => x_msg_data
466 );
467 --
468 WHEN fnd_api.g_exc_unexpected_error THEN
469 x_return_status := fnd_api.g_ret_sts_unexp_error ;
470
471 fnd_msg_pub.count_and_get
472 ( p_count => x_msg_count
473 , p_data => x_msg_data
474 );
475 --
476 WHEN OTHERS THEN
477 x_return_status := fnd_api.g_ret_sts_unexp_error ;
478 --
479 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
480 THEN
481 fnd_msg_pub.add_exc_msg
482 ( 'CSTPCGUT'
483 , 'GET_COST_GROUP_ACCOUNTS : Statement - '||to_char(l_statement)
484 );
485 END IF;
486 fnd_msg_pub.count_and_get
487 ( p_count => x_msg_count
488 , p_data => x_msg_data
489 );
490
491 END;
492
493 ----------------------------------------------------------------------------
494 -- PROCEDURE --
495 -- verify_cg_change --
496 -- --
497 -- DESCRIPTION --
498 -- Use this procedure to verify if changing the accounts of a cost group--
499 -- is allowed. Replaces get_cg_pending_txns. --
500 -- --
501 -- Allow the change of accounts if the following conditions are met: --
502 -- 1. Cost group / org holds no quantity inside MOQ --
503 -- 2. Cost group / org holds no quantity inside CQL --
504 -- 3. No uncosted transactions for this cost group / org --
505 -- 4. No pending transactions for this cost group / org --
506 ----------------------------------------------------------------------------
507
508 PROCEDURE verify_cg_change(x_return_status OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER,
510 x_msg_data OUT NOCOPY VARCHAR2,
511 x_change_allowed OUT NOCOPY NUMBER,
512 p_cost_group_id IN NUMBER,
513 p_organization_id IN NUMBER) IS
514
515 l_statement NUMBER;
516 l_cost_method NUMBER;
517
518 BEGIN
519
520 x_change_allowed := 0;
521 l_cost_method := 0;
522
523 l_statement := 10;
524
525 SELECT COUNT(*)
526 INTO x_change_allowed
527 FROM mtl_onhand_quantities
528 WHERE organization_id = p_organization_id
529 AND cost_group_id = p_cost_group_id
530 AND rownum = 1;
531
532 IF x_change_allowed = 1 THEN
533 x_return_status := fnd_api.g_ret_sts_success;
534 RETURN;
535 END IF;
536
537 l_statement := 20;
538
539 SELECT primary_cost_method
540 INTO l_cost_method
541 FROM mtl_parameters
542 WHERE organization_id = p_organization_id;
543
544 IF l_cost_method <> 1 THEN
545 SELECT COUNT(*)
546 INTO x_change_allowed
547 FROM cst_quantity_layers
548 WHERE organization_id = p_organization_id
552
549 AND cost_group_id = p_cost_group_id
550 AND layer_quantity <> 0
551 AND rownum = 1;
553 IF x_change_allowed = 1 THEN
554 x_return_status := fnd_api.g_ret_sts_success;
555 RETURN;
556 END IF;
557 END IF;
558
559 l_statement := 30;
560
561 SELECT COUNT(*)
562 INTO x_change_allowed
563 FROM mtl_material_transactions_temp
564 WHERE (
565 ( organization_id = p_organization_id
566 AND cost_group_id = p_cost_group_id
567 )
568 OR
569 ( transfer_organization = p_organization_id
570 AND transfer_cost_group_id = p_cost_group_id
571 )
572 )
573 AND rownum = 1;
574
575 IF x_change_allowed = 1 THEN
576 x_return_status := fnd_api.g_ret_sts_success;
577 RETURN;
578 END IF;
579
580 l_statement := 40;
581
582 SELECT COUNT(*)
583 INTO x_change_allowed
584 FROM mtl_material_transactions
585 WHERE costed_flag in ('N','E')
586 AND (
587 ( organization_id = p_organization_id
588 AND cost_group_id = p_cost_group_id
589 )
590 OR
591 ( transfer_organization_id = p_organization_id
592 AND transfer_cost_group_id = p_cost_group_id
593 )
594 )
595 AND rownum = 1;
596
597 IF x_change_allowed = 1 THEN
598 x_return_status := fnd_api.g_ret_sts_success;
599 RETURN;
600 END IF;
601
602 x_return_status := fnd_api.g_ret_sts_success;
603
604 EXCEPTION
605 WHEN fnd_api.g_exc_error THEN
606 x_return_status := fnd_api.g_ret_sts_error;
607
608 fnd_msg_pub.count_and_get
609 ( p_count => x_msg_count
610 , p_data => x_msg_data
611 );
612
613 WHEN fnd_api.g_exc_unexpected_error THEN
614 x_return_status := fnd_api.g_ret_sts_unexp_error ;
615
616 fnd_msg_pub.count_and_get
617 ( p_count => x_msg_count
618 , p_data => x_msg_data
619 );
620
621 WHEN OTHERS THEN
622 x_return_status := fnd_api.g_ret_sts_unexp_error ;
623
624 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
625 THEN
626 fnd_msg_pub.add_exc_msg
627 ( 'CSTPCGUT'
628 , 'VERIFY_CG_CHANGE : Statement - '||to_char(l_statement)
629 );
630 END IF;
631 fnd_msg_pub.count_and_get
632 ( p_count => x_msg_count
633 , p_data => x_msg_data
634 );
635
636 END;
637
638 end CSTPCGUT;