[Home] [Help]
PACKAGE BODY: APPS.GMF_COPY_PERCENTAGE_BURDEN
Source
1 PACKAGE BODY gmf_copy_percentage_burden AS
2 /* $Header: gmfcppbb.pls 120.3 2006/04/07 05:00:26 anthiyag noship $ */
3
4
5 /*****************************************************************************
6 * PACKAGE BODY *
7 * GMF_COPY_PERCENTAGE_BURDEN *
8 * *
9 * DESCRIPTION *
10 * Copy Percentage Burdens *
11 * *
12 * CONTENTS *
13 * PROCEDURE copy_percentage_burden ( ... ) *
14 * PROCEDURE end_copy ( ... ) *
15 * PROCEDURE copy_burden_pct ( ... ) *
16 * PROCEDURE delete_burden_pct ( ... ) *
17 * FUNCTION do_pct_exist ( ... ) *
18 * *
19 * HISTORY *
20 * 21-Nov-2000 Uday Moogala - Created *
21 * Bug# 1419482 Percentage Burden Enhancements. *
22 * 1. Copy to all periods option : *
23 * Copy Percentage burden from one costing period to all the subsequent *
24 * open/frozen costing periods in the same calendar or *
25 * to all the open/frozen periods if it is a different calendar. *
26 * For more details refer to DLD : pct_burden_dld.rtf *
27 * 30-OCT-2002 RajaSekhar Bug#2641405 Added NOCOPY hint. *
28 *****************************************************************************/
29
30 PROCEDURE end_copy
31 (
32 pi_errstat IN VARCHAR2,
33 pi_errmsg IN VARCHAR2
34 );
35
36 PROCEDURE copy_burden_pct
37 (
38 pi_legal_entity_id_from IN gmf_burden_percentages.legal_entity_id%TYPE,
39 pi_calendar_code_from IN cm_cldr_hdr.calendar_code%TYPE,
40 pi_period_code_from IN cm_cldr_dtl.period_code%TYPE,
41 pi_cost_type_id_from IN cm_mthd_mst.cost_type_id%TYPE,
42 pi_legal_entity_id_to IN gmf_burden_percentages.legal_entity_id%TYPE,
43 pi_calendar_code_to IN cm_cldr_hdr.calendar_code%TYPE,
44 pi_period_code_to IN cm_cldr_dtl.period_code%TYPE,
45 pi_cost_type_id_to IN cm_mthd_mst.cost_type_id%TYPE,
46 pi_from_range IN gmf_burden_codes.burden_code%TYPE,
47 pi_to_range IN gmf_burden_codes.burden_code%TYPE,
48 pi_rem_repl IN NUMBER,
49 pi_all_periods_from IN cm_cldr_dtl.period_code%TYPE,
50 pi_all_periods_to IN cm_cldr_dtl.period_code%TYPE
51 );
52
53 PROCEDURE delete_burden_pct
54 (
55 pi_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
56 pi_period_id IN gmf_burden_percentages.period_id%TYPE,
57 pi_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
58 pi_from_range IN gmf_burden_codes.burden_code%TYPE,
59 pi_to_range IN gmf_burden_codes.burden_code%TYPE
60 );
61
62 FUNCTION do_pct_exist
63 (
64 pi_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
65 pi_period_id IN gmf_burden_percentages.period_id%TYPE,
66 pi_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
67 pi_burden_code_from IN gmf_burden_codes.burden_code%TYPE,
68 pi_burden_code_to IN gmf_burden_codes.burden_code%TYPE
69 )
70 RETURN NUMBER ;
71
72 /**************
73 * WHO columns *
74 **************/
75
76 g_user_id NUMBER;
77 g_login_id NUMBER;
78
79
80 /*******************************************************************************
81 * PROCEDURE *
82 * copy_percentage_burden *
83 * *
84 * DESCRIPTION *
85 * Copy Copy Percentage Burdens *
86 * Copies Burden Percentages from the one set of calendar/period/cost method *
87 * to another for the burden codes range specified on the form. *
88 * *
89 * INPUT PARAMETERS *
90 * From and To calendar/period/cost method *
91 * Burden Codes from/to range *
92 * Remove before copy or Replace during copy indicator *
93 * *
94 * OUTPUT PARAMETERS *
95 * po_errbuf Completion message to the Concurrent Manager *
96 * po_retcode Return code to the Concurrent Manager *
97 * *
98 * HISTORY *
99 * 13-Oct-1999 Uday Moogala Created *
100 *******************************************************************************/
101
102 PROCEDURE copy_percentage_burden
103 (
104 po_errbuf OUT NOCOPY VARCHAR2,
105 po_retcode OUT NOCOPY VARCHAR2,
106 pi_legal_entity_id_from IN gmf_burden_percentages.legal_entity_id%TYPE,
107 pi_calendar_code_from IN cm_cldr_hdr.calendar_code%TYPE,
108 pi_period_code_from IN cm_cldr_dtl.period_code%TYPE,
109 pi_cost_type_id_from IN cm_mthd_mst.cost_type_id%TYPE,
110 pi_legal_entity_id_to IN gmf_burden_percentages.legal_entity_id%TYPE,
111 pi_calendar_code_to IN cm_cldr_hdr.calendar_code%TYPE,
112 pi_period_code_to IN cm_cldr_dtl.period_code%TYPE,
113 pi_cost_type_id_to IN cm_mthd_mst.cost_type_id%TYPE,
114 pi_burden_code_from IN gmf_burden_codes.burden_code%TYPE,
115 pi_burden_code_to IN gmf_burden_codes.burden_code%TYPE,
116 pi_rem_repl IN VARCHAR2,
117 pi_all_periods_from IN cm_cldr_dtl.period_code%TYPE,
118 pi_all_periods_to IN cm_cldr_dtl.period_code%TYPE
119 )
120 IS
121
122 /******************
123 * Local Variables *
124 ******************/
125
126 l_from_range gmf_burden_codes.burden_code%TYPE;
127 l_to_range gmf_burden_codes.burden_code%TYPE;
128 l_rem_repl NUMBER;
129 l_num_src_rows NUMBER; -- num rows in source period
130 l_period_id_from gmf_burden_percentages.period_id%TYPE;
131
132 /*************
133 * Exceptions *
134 *************/
135
136 e_same_from_to EXCEPTION;
137 e_no_cost_rows EXCEPTION;
138 e_no_brdn_range EXCEPTION;
139
140 BEGIN
141
142 /****************************************************
143 * Uncomment the call below to write to a local file *
144 ****************************************************/
145
146 ----FND_FILE.PUT_NAMES('gmfcppb.log','gmfcppb.out','/sqlcom/log/dom1151');
147
148
149 gmf_util.msg_log( 'GMF_CPPB_START' );
150 gmf_util.msg_log( 'GMF_CPPB_SRCPARAM', nvl(pi_calendar_code_from, ' '), nvl(pi_period_code_from, ' '), nvl(TO_CHAR(pi_cost_type_id_from), ' '));
151 gmf_util.msg_log( 'GMF_CPPB_TGTPARAM', nvl(pi_calendar_code_to, ' '), nvl(pi_period_code_to, ' '), nvl(TO_CHAR(pi_cost_type_id_to), ' '));
152 gmf_util.msg_log( 'GMF_CPPB_BRDNRANGE', nvl(pi_burden_code_from, ' '), nvl(pi_burden_code_to, ' '));
153
154 IF ( (pi_period_code_to IS NULL) AND ((pi_all_periods_from IS NOT NULL) OR (pi_all_periods_to IS NOT NULL))) THEN
155
156 gmf_util.msg_log('GMF_CPIC_ALLPERIODS', nvl(pi_calendar_code_to, ' ')) ;
157 gmf_util.msg_log('GMF_CPIC_PERIODS_RANGE', nvl(pi_all_periods_from, ' '),nvl(pi_all_periods_to, ' '), nvl(pi_calendar_code_to, ' '));
158
159 END IF ;
160
161 l_rem_repl := 0;
162
163 IF ( pi_rem_repl = '1' ) THEN --Remove Before Copy
164
165 l_rem_repl := 1;
166 gmf_util.msg_log( 'GMF_CPIC_OPTREM' );
167
168 ELSE -- Replace before copy
169
170 l_rem_repl := 0;
171 gmf_util.msg_log( 'GMF_CPIC_OPTREP' );
172
173 END IF;
174
175 gmf_util.log;
176
177 l_from_range := NULL;
178 l_to_range := NULL;
179
180 IF ((pi_burden_code_from IS NOT NULL) OR (pi_burden_code_to IS NOT NULL)) THEN
181
182 l_from_range := pi_burden_code_from;
183 l_to_range := pi_burden_code_to;
184
185 ELSE
186
187 gmf_util.msg_log( 'GMF_CPPB_NO_BRDN_RANGE' );
188 RAISE e_no_brdn_range;
189
190 END IF;
191
192 BEGIN
193 SELECT a.period_id
194 INTO l_period_id_from
195 FROM cm_cldr_mst_v a
196 WHERE a.legal_entity_id = pi_legal_entity_id_from
197 AND a.calendar_code = pi_calendar_code_from
198 AND a.period_code = pi_period_code_from
199 AND a.cost_type_id = pi_cost_type_id_from;
200 EXCEPTION
201 WHEN OTHERS THEN
202 l_period_id_from := NULL;
203 END;
204
205 IF l_period_id_from IS NULL THEN
206 gmf_util.msg_log( 'GMF_CP_NO_ROWS' );
207 RAISE e_no_cost_rows;
208 END IF;
209
210 l_num_src_rows := do_pct_exist( pi_legal_entity_id_from, l_period_id_from, pi_cost_type_id_from, pi_burden_code_from, pi_burden_code_to);
211
212 IF ( l_num_src_rows <= 0 ) THEN
213
214 gmf_util.msg_log( 'GMF_CP_NO_ROWS' );
215 RAISE e_no_cost_rows;
216
217 END IF;
218
219 gmf_util.trace( 'Burden Codes Range : ' || l_from_range || ' - ' || l_to_range, 1 );
220
221 /*************************
222 * Initialize WHO columns *
223 *************************/
224
225 g_user_id := FND_GLOBAL.USER_ID;
226 g_login_id := FND_GLOBAL.LOGIN_ID;
227
228 /**************************************************************
229 * If all parameters then burden percentages cannot be copied. *
230 **************************************************************/
231
232 IF ((pi_period_code_from = pi_period_code_to) AND (pi_cost_type_id_from = pi_cost_type_id_to) AND (pi_calendar_code_from = pi_calendar_code_to)) THEN
233
234 gmf_util.msg_log( 'GMF_CPPB_SAME_FROMTO' );
235 RAISE e_same_from_to;
236
237 ELSE
238
239 copy_burden_pct
240 (
241 pi_legal_entity_id_from,
242 pi_calendar_code_from,
243 pi_period_code_from,
244 pi_cost_type_id_from,
245 pi_legal_entity_id_to,
246 pi_calendar_code_to,
247 pi_period_code_to,
248 pi_cost_type_id_to,
249 l_from_range,
250 l_to_range,
251 l_rem_repl,
252 pi_all_periods_from,
253 pi_all_periods_to
254 );
255
256 END IF;
257
258 po_retcode := 0;
259 po_errbuf := NULL;
260 end_copy( 'NORMAL', NULL );
261 COMMIT;
262
263 gmf_util.log;
264 gmf_util.msg_log( 'GMF_CPPB_END' );
265
266 EXCEPTION
267 WHEN e_no_cost_rows THEN
268 po_retcode := 0;
269 po_errbuf := NULL;
270 end_copy( 'NORMAL', NULL );
271
272 WHEN e_same_from_to THEN
273 po_retcode := 0;
274 po_errbuf := NULL;
275 end_copy( 'NORMAL', NULL );
276
277 WHEN e_no_brdn_range THEN
278 po_retcode := 0;
279 po_errbuf := NULL;
280 end_copy( 'NORMAL', NULL );
281
282 WHEN utl_file.invalid_path then
283 po_retcode := 3;
284 po_errbuf := 'Invalid path - '||to_char(SQLCODE) || ' ' || SQLERRM;
285 end_copy ('ERROR', NULL);
286
287 WHEN utl_file.invalid_mode then
288 po_retcode := 3;
289 po_errbuf := 'Invalid Mode - '||to_char(SQLCODE) || ' ' || SQLERRM;
290 end_copy ('ERROR', NULL);
291
292 WHEN utl_file.invalid_filehandle then
293 po_retcode := 3;
294 po_errbuf := 'Invalid filehandle - '||to_char(SQLCODE) || ' ' || SQLERRM;
295 end_copy ('ERROR', NULL);
296
297 WHEN utl_file.invalid_operation then
298 po_retcode := 3;
299 po_errbuf := 'Invalid operation - '||to_char(SQLCODE) || ' ' || SQLERRM;
300 end_copy ('ERROR', NULL);
301
302 WHEN utl_file.write_error then
303 po_retcode := 3;
304 po_errbuf := 'Write error - '||to_char(SQLCODE) || ' ' || SQLERRM;
305 end_copy ('ERROR', NULL);
306
307 WHEN others THEN
308 po_retcode := 3;
309 po_errbuf := to_char(SQLCODE) || ' ' || SQLERRM;
310 end_copy ('ERROR', po_errbuf);
311
312 END copy_percentage_burden;
313
314 /****************************************************************************************
315 * PROCEDURE *
316 * copy_burden_pct *
317 * *
318 * DESCRIPTION *
319 * Copies burden percentages from source to target period *
320 * *
321 * INPUT PARAMETERS *
322 * From: calendar_code, period_code, cost_mthd_code *
323 * To : calendar_code, period_code, cost_mthd_code *
324 * From_Range, To_Range : from/to burden codes range *
325 * Remove_or_Replace indicator: Either burden percentages in target period have to be *
326 * removed before copy starts or just replace the existing rows *
327 * *
328 * HISTORY *
329 * 13-Oct-1999 Uday Moogala - created. *
330 * 02-MARY-2003 sschinch - Bug 2934528. Bind variables fix. *
331 ****************************************************************************************/
332
336 pi_calendar_code_from IN cm_cldr_hdr.calendar_code%TYPE,
333 PROCEDURE copy_burden_pct
334 (
335 pi_legal_entity_id_from IN gmf_burden_percentages.legal_entity_id%TYPE,
337 pi_period_code_from IN cm_cldr_dtl.period_code%TYPE,
338 pi_cost_type_id_from IN cm_mthd_mst.cost_type_id%TYPE,
339 pi_legal_entity_id_to IN gmf_burden_percentages.legal_entity_id%TYPE,
340 pi_calendar_code_to IN cm_cldr_hdr.calendar_code%TYPE,
341 pi_period_code_to IN cm_cldr_dtl.period_code%TYPE,
342 pi_cost_type_id_to IN cm_mthd_mst.cost_type_id%TYPE,
343 pi_from_range IN gmf_burden_codes.burden_code%TYPE,
344 pi_to_range IN gmf_burden_codes.burden_code%TYPE,
345 pi_rem_repl IN NUMBER,
346 pi_all_periods_from IN cm_cldr_dtl.period_code%TYPE,
347 pi_all_periods_to IN cm_cldr_dtl.period_code%TYPE
348 )
349 IS
350
351 /***************************
352 * PL/SQL Types Definitions *
353 ***************************/
354
355 TYPE rectype_brdn_pct IS RECORD
356 (
357 legal_entity_id gmf_burden_percentages.legal_entity_id%TYPE,
358 period_id gmf_burden_percentages.period_id%TYPE,
359 cost_type_id cm_mthd_mst.cost_type_id%TYPE,
360 burden_id gmf_burden_codes.burden_id%TYPE,
361 burden_code gmf_burden_codes.burden_code%TYPE,
362 organization_id gmf_burden_percentages.organization_id%TYPE,
363 master_organization_id gmf_burden_percentages.master_organization_id%TYPE,
364 inventory_item_id gmf_burden_percentages.inventory_item_id%TYPE,
365 gl_category_id gmf_burden_percentages.gl_category_id%TYPE,
366 cost_category_id gmf_burden_percentages.cost_category_id%TYPE,
367 gl_prod_line_category_id gmf_burden_percentages.gl_prod_line_category_id%TYPE,
368 gl_business_category_id gmf_burden_percentages.gl_business_category_id%TYPE,
369 sspl_category_id gmf_burden_percentages.sspl_category_id%TYPE,
370 percentage gmf_burden_percentages.percentage%TYPE
371 );
372
373 TYPE curtyp_brdn_pct IS REF CURSOR;
374
375 TYPE curtyp_periods IS REF CURSOR;
376
377 /******************
378 * Local Variables *
379 ******************/
380
381 l_sql_stmt VARCHAR2(2000);
382 l_sql_periods VARCHAR2(2000);
383 l_period_id_to gmf_burden_percentages.period_id%TYPE ;
384 l_brdn_rows NUMBER;
385 l_brdn_rows_upd NUMBER;
386 l_brdn_rows_ins NUMBER;
387 l_period_id_from gmf_burden_percentages.period_id%TYPE;
388 pi_period_id_from gmf_burden_percentages.period_id%TYPE;
389 pi_period_id_to gmf_burden_percentages.period_id%TYPE;
390
391
392 r_brdn_pct rectype_brdn_pct;
393 cv_brdn_pct curtyp_brdn_pct;
394 cv_periods curtyp_periods;
395
396 e_no_cost_rows EXCEPTION;
397
398 BEGIN
399
400 l_sql_stmt := '';
401
402 l_sql_stmt := ' SELECT ' ||
403 ' pct.legal_entity_id, ' ||
404 ' pct.period_id, ' ||
405 ' pct.cost_type_id, ' ||
406 ' bur.burden_id, ' ||
407 ' bur.burden_code, ' ||
408 ' pct.organization_id, ' ||
409 ' pct.master_organization_id, ' ||
410 ' pct.inventory_item_id, ' ||
411 ' pct.gl_category_id, ' ||
412 ' pct.cost_category_id, ' ||
413 ' pct.gl_prod_line_category_id, ' ||
414 ' pct.gl_business_category_id, ' ||
415 ' pct.sspl_category_id, ' ||
416 ' pct.percentage ' ||
417 ' FROM ' ||
418 ' gmf_burden_percentages pct, ' ||
419 ' gmf_burden_codes bur ' ||
420 ' WHERE ' ||
421 ' pct.legal_entity_id = :b_legal_entity_id AND ' ||
422 ' pct.period_id = :b_period_id AND ' ||
423 ' pct.cost_type_id = :b_cost_type_id AND ' ||
424 ' pct.delete_mark = 0 AND ' ||
425 ' pct.burden_id = bur.burden_id AND ' ||
426 ' bur.delete_mark = 0 AND ' ||
427 ' bur.burden_code >= nvl(:b_from_brdn,bur.burden_code) AND ' ||
428 ' bur.burden_code <= nvl(:b_to_brdn,bur.burden_code) '||
429 ' ORDER BY ' ||
430 ' pct.legal_entity_id, pct.period_id, pct.cost_type_id, pct.burden_id';
431
432 gmf_util.trace( 'Burden Percentages Sql Stmt: ' || l_sql_stmt, 1 );
433
437
434 /*********************************************************************
435 * Build SQL to get target periods when From/To Periods are not null. *
436 *********************************************************************/
438 IF (pi_period_code_to IS NOT NULL) THEN -- copy to one period.
439
440 l_sql_periods := 'SELECT :pi_period_id_to FROM dual ' ;
441
442 ELSE
443
444 l_sql_periods := '' ;
445 l_sql_periods := 'SELECT DISTINCT ' ||
446 ' c3.period_id ' ||
447 'FROM ' ||
448 'cm_cldr_mst_v c3, cm_cldr_mst_v c2, cm_cldr_mst_v c1 ' ||
449 'WHERE ' ||
450 'c3.legal_entity_id = :pi_legal_entity_id AND ' ||
451 'c1.calendar_code = :pi_calendar_code_to AND ' ||
452 'c1.period_code = :pi_all_periods_from AND ' ||
453 'c2.calendar_code = :pi_calendar_code_to AND ' ||
454 'c2.period_code = :pi_all_periods_to AND ' ||
455 'c3.calendar_code = :pi_calendar_code_to AND ' ||
456 'c3.cost_Type_id = :pi_cost_type_id_to AND ' ||
457 'c2.legal_entity_id = c3.legal_entity_id AND ' ||
458 'c1.legal_entity_id = c2.legal_entity_id AND ' ||
459 'c3.start_date >= c1.start_date AND ' ||
460 'c3.end_date <= c2.end_date AND ' ||
461 'c3.period_status <> ''C''';
462
463 IF (pi_calendar_code_from = pi_calendar_code_to) THEN
464
465 l_sql_periods := l_sql_periods || ' AND c3.period_code <> :pi_period_code_from ';
466
467 END IF ;
468
469 END IF ; -- To Period code check
470
471 BEGIN
472 SELECT a.period_id
473 INTO pi_period_id_from
474 FROM cm_cldr_mst_v a
475 WHERE a.legal_entity_id = pi_legal_entity_id_from
476 AND a.calendar_code = pi_calendar_code_from
477 AND a.period_code = pi_period_code_from
478 AND a.cost_type_id = pi_cost_type_id_from;
479 EXCEPTION
480 WHEN OTHERS THEN
481 pi_period_id_from := NULL;
482 END;
483
484 gmf_util.trace( 'Periods Query : ' || l_sql_periods, 1 );
485
486 IF (pi_period_code_to IS NOT NULL) THEN
487
488 BEGIN
489 SELECT a.period_id
490 INTO pi_period_id_to
491 FROM cm_cldr_mst_v a
492 WHERE a.legal_entity_id = pi_legal_entity_id_to
493 AND a.calendar_code = pi_calendar_code_to
494 AND a.period_code = pi_period_code_to
495 AND a.cost_type_id = pi_cost_type_id_to;
496 EXCEPTION
497 WHEN OTHERS THEN
498 pi_period_id_to := NULL;
499 END;
500
501
502 OPEN cv_periods FOR l_sql_periods USING pi_period_id_to;
503
504 ELSIF (pi_calendar_code_from = pi_calendar_code_to) THEN
505
506 OPEN cv_periods FOR l_sql_periods USING pi_legal_entity_id_to, pi_calendar_code_to, pi_all_periods_from, pi_calendar_code_to, pi_all_periods_to, pi_calendar_code_to, pi_cost_type_id_to, pi_period_code_from;
507
508 ELSE
509
510 OPEN cv_periods FOR l_sql_periods USING pi_legal_entity_id_to, pi_calendar_code_to, pi_all_periods_from, pi_calendar_code_to, pi_all_periods_to, pi_calendar_code_to, pi_cost_type_id_to;
511
512 END IF;
513
514 LOOP
515
516 FETCH cv_periods INTO l_period_id_to ;
517 EXIT WHEN cv_periods%NOTFOUND ;
518
519 IF pi_rem_repl = 1 THEN
520
521 /*********************************************
522 * deleting whole range of burden percentages *
523 *********************************************/
524
525 delete_burden_pct
526 (
527 pi_legal_entity_id_to,
528 l_period_id_to,
529 pi_cost_type_id_to,
530 pi_from_range,
531 pi_to_range
532 );
533
534 END IF;
535
536 /************************
537 * Copy the burden costs *
538 ************************/
539
540 l_brdn_rows := 0;
541 l_brdn_rows_upd := 0;
542 l_brdn_rows_ins := 0;
543
544 OPEN cv_brdn_pct FOR l_sql_stmt USING pi_legal_entity_id_from, pi_period_id_from, pi_cost_type_id_from, pi_from_range, pi_to_range;
545 LOOP
546
547 FETCH cv_brdn_pct INTO r_brdn_pct;
548 EXIT WHEN cv_brdn_pct%NOTFOUND;
549 gmf_util.log;
550 gmf_util.msg_log('GMF_CPPB_PERIOD_BRDN', r_brdn_pct.burden_code, to_char(l_period_id_to)) ;
551 gmf_util.trace( 'Burden : ' || r_brdn_pct.burden_code || ' Prd Id: ' || TO_CHAR(r_brdn_pct.period_id) || ' Cost Type: ' || TO_CHAR(r_brdn_pct.cost_type_id) ||
552 ' Organization Id: ' || nvl(TO_CHAR(r_brdn_pct.organization_id),'') || ' Item Id: ' || nvl(TO_CHAR(r_brdn_pct.inventory_item_id),'') || ' GL Class: ' ||
553 nvl(r_brdn_pct.gl_category_id,'') || ' ItemCC: ' || nvl(r_brdn_pct.cost_category_id,''), 3 );
554 l_brdn_rows := l_brdn_rows + 1;
555
556 /*******************
557 * Try update first *
558 *******************/
559
560 <<insert_or_update_bur>>
561 DECLARE
562 e_insert_row_b EXCEPTION;
563 BEGIN
564 IF( pi_rem_repl = 1 ) THEN
565 RAISE e_insert_row_b;
566 END IF;
567
568 UPDATE gmf_burden_percentages
569 SET burden_percentage_id = GMF_BURDEN_PERCENTAGE_ID_S.NEXTVAL,
570 percentage = r_brdn_pct.percentage,
571 delete_mark = 0,
572 last_updated_by = g_user_id,
573 last_update_login = g_login_id,
574 last_update_date = SYSDATE
575 WHERE
576 legal_entity_id = pi_legal_entity_id_to AND
577 period_id = l_period_id_to AND
578 cost_type_id = pi_cost_type_id_to AND
579 burden_id = r_brdn_pct.burden_id AND
580 nvl(organization_id,-1) = nvl(r_brdn_pct.organization_id,-1) AND
581 nvl(master_organization_id,-1) = nvl(r_brdn_pct.master_organization_id,-1) AND
582 nvl(inventory_item_id,-1) = nvl(r_brdn_pct.inventory_item_id,-1) AND
583 nvl(gl_category_id,-1) = nvl(r_brdn_pct.gl_category_id,-1) AND
584 nvl(cost_category_id,-1) = nvl(r_brdn_pct.cost_category_id,-1) AND
585 nvl(gl_prod_line_category_id,-1) = nvl(r_brdn_pct.gl_prod_line_category_id,-1) AND
586 nvl(gl_business_category_id,-1) = nvl(r_brdn_pct.gl_business_category_id,-1) AND
587 nvl(sspl_category_id,-1) = nvl(r_brdn_pct.sspl_category_id,-1);
588
589 /**********************************
590 * If update fails then try insert *
591 **********************************/
592 IF( SQL%ROWCOUNT <= 0 ) THEN
596 l_brdn_rows_upd := l_brdn_rows_upd + 1;
593 RAISE e_insert_row_b;
594 END IF;
595
597
598 EXCEPTION
599 WHEN e_insert_row_b THEN
600
601 INSERT INTO gmf_burden_percentages
602 (
603 burden_percentage_id,
604 legal_entity_id,
605 period_id,
606 cost_type_id,
607 burden_id,
608 organization_id,
609 master_organization_id,
610 inventory_item_id,
611 gl_category_id,
612 cost_category_id,
613 gl_prod_line_category_id,
614 gl_business_category_id,
615 sspl_category_id,
616 percentage,
617 delete_mark,
618 created_by,
619 creation_date,
620 last_updated_by,
621 last_update_date,
622 last_update_login
623 )
624 VALUES
625 (
626 GMF_BURDEN_PERCENTAGE_ID_S.NEXTVAL,
627 pi_legal_entity_id_to,
628 l_period_id_to,
629 pi_cost_type_id_to,
630 r_brdn_pct.burden_id,
631 r_brdn_pct.organization_id,
632 r_brdn_pct.master_organization_id,
633 r_brdn_pct.inventory_item_id,
634 r_brdn_pct.gl_category_id,
635 r_brdn_pct.cost_category_id,
636 r_brdn_pct.gl_prod_line_category_id,
637 r_brdn_pct.gl_business_category_id,
638 r_brdn_pct.sspl_category_id,
639 r_brdn_pct.percentage,
640 0, -- delete_mark
641 g_user_id, -- created_by
642 SYSDATE, -- creation_date
643 g_user_id, -- last_updated_by
644 SYSDATE, -- last_update_date
645 g_login_id -- last_update_login
646 );
647
648 l_brdn_rows_ins := l_brdn_rows_ins + 1;
649 END insert_or_update_bur;
650
651 END LOOP; -- End loop of Source Burden Percentage.
652
653 CLOSE cv_brdn_pct;
654
655 IF( l_brdn_rows > 0 ) THEN
656
657 gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_brdn_rows) );
658 gmf_util.msg_log( 'GMF_CP_ROWS_UPDINS',TO_CHAR(l_brdn_rows_upd), TO_CHAR(l_brdn_rows_ins));
659
660 ELSE
661
662 gmf_util.msg_log( 'GMF_CP_NO_ROWS' );
663
664 END IF;
665
666 END LOOP ; -- periods loop
667
668 CLOSE cv_periods;
669
670 END copy_burden_pct;
671
672 /**************************************************************
673 * PROCEDURE *
674 * delete_burden_pct *
675 * *
676 * DESCRIPTION *
677 * Deletes the burden percentages for the parameters passed *
678 * *
679 * INPUT PARAMETERS *
680 * calendar, period, cost_mthd, burden_codes range *
681 * *
682 * HISTORY *
683 * 15-Feb-2001 Uday Moogala Seshadri *
684 **************************************************************/
685
686 PROCEDURE delete_burden_pct
687 (
688 pi_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
689 pi_period_id IN gmf_burden_percentages.period_id%TYPE,
690 pi_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
691 pi_from_range IN gmf_burden_codes.burden_code%TYPE,
692 pi_to_range IN gmf_burden_codes.burden_code%TYPE
693 )
694 IS
695
696 /******************
697 * Local Variables *
698 ******************/
699
700 l_del_stmt VARCHAR2(1500);
701 l_sub_qry VARCHAR2(500);
702
703 BEGIN
704
705 l_del_stmt := '';
706 l_sub_qry := '';
707
708 l_del_stmt := ' DELETE FROM gmf_burden_percentages pct ' ||
709 ' WHERE ' ||
710 ' pct.legal_entity_id = :b_legal_entity_id AND ' ||
711 ' pct.period_id = :b_period_id AND ' ||
712 ' pct.cost_type_id = :b_cost_type_id AND ' ||
713 ' pct.burden_id IN ( ';
714
715 l_sub_qry := ' SELECT ' ||
716 ' bur.burden_id ' ||
717 ' FROM ' ||
718 ' gmf_burden_codes bur ' ||
719 ' WHERE ' ||
720 ' bur.delete_mark = 0 AND ' ||
721 ' bur.burden_code >= nvl(:b_burden_code_from,bur.burden_code) AND ' ||
722 ' bur.burden_code <= nvl(:b_burden_code_to,bur.burden_code) ' ;
723
724 l_del_stmt := l_del_stmt || l_sub_qry || ' ) ' ;
725
726 gmf_util.trace( ' Burden Del Stmt: ' || l_del_stmt, 1 );
727
728 EXECUTE IMMEDIATE l_del_stmt USING pi_legal_entity_id, pi_period_id, pi_cost_type_id, pi_from_range, pi_to_range;
729
730 gmf_util.trace( SQL%ROWCOUNT || ' Rows deleted', 1 );
731
732 END delete_burden_pct;
733
737 * *
734 /************************************************************************************
735 * PROCEDURE *
736 * end_copy *
738 * DESCRIPTION *
739 * Sets the concurrent manager completion status *
740 * *
741 * INPUT PARAMETERS *
742 * pi_errstat - Completion status, must be one of 'NORMAL', 'WARNING', OR 'ERROR' *
743 * pi_errmsg - Completion message to be passed back *
744 * *
745 * HISTORY *
746 * 13-Oct-1999 Rajesh Seshadri *
747 ************************************************************************************/
748
749 PROCEDURE end_copy
750 (
751 pi_errstat IN VARCHAR2,
752 pi_errmsg IN VARCHAR2
753 )
754 IS
755
756 /******************
757 * Local Variables *
758 ******************/
759
760 l_retval BOOLEAN;
761
762 BEGIN
763
764 l_retval := fnd_concurrent.set_completion_status(pi_errstat,pi_errmsg);
765
766 END end_copy;
767
768 /*******************************************************************************
769 * FUNCTION *
770 * do_pct_exist *
771 * *
772 * DESCRIPTION *
773 * Verifies if there exists any burden percentages for the parameters passed *
774 * *
775 * INPUT PARAMETERS *
776 * pi_calendar_code Cost Calendar *
777 * pi_period_code Cost Period *
778 * pi_cost_mthd_code Cost Method *
779 * pi_burden_code_from Burden Code from *
780 * pi_burden_code_to Burden Code to *
781 * Verifies if any costs exists for the above parameters *
782 * *
783 * HISTORY *
784 * 20-Feb-2001 Uday Moogala *
785 *******************************************************************************/
786
787 FUNCTION do_pct_exist
788 (
789 pi_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
790 pi_period_id IN gmf_burden_percentages.period_id%TYPE,
791 pi_cost_type_id IN cm_mthd_mst.cost_type_id%TYPE,
792 pi_burden_code_from IN gmf_burden_codes.burden_code%TYPE,
793 pi_burden_code_to IN gmf_burden_codes.burden_code%TYPE
794 )
795 RETURN NUMBER
796 IS
797
798 /**********
799 * Cursors *
800 **********/
801
802 CURSOR cur_num_pct_rows
803 (
804 p_legal_entity_id IN gmf_burden_percentages.legal_entity_id%TYPE,
805 p_period_id IN gmf_burden_percentages.period_id%TYPE,
806 p_burden_code_from IN gmf_burden_codes.burden_code%TYPE,
807 p_burden_code_to IN gmf_burden_codes.burden_code%TYPE
808 )
809 IS
810 SELECT COUNT(1)
811 FROM gmf_burden_percentages pct,
812 gmf_burden_codes bur
813 WHERE pct.legal_entity_id = p_legal_entity_id
814 AND pct.period_id = p_period_id
815 AND pct.delete_mark = 0
816 AND pct.burden_id = bur.burden_id
817 AND bur.delete_mark = 0
818 AND bur.burden_code >= nvl(p_burden_code_from,bur.burden_code)
819 AND bur.burden_code <= nvl(p_burden_code_to,bur.burden_code);
820
821 /******************
822 * Local Variables *
823 ******************/
824
825 l_num_rows NUMBER := 0;
826
827 BEGIN
828
829 OPEN cur_num_pct_rows( pi_legal_entity_id, pi_period_id, pi_burden_code_from, pi_burden_code_to );
830 FETCH cur_num_pct_rows INTO l_num_rows;
831 CLOSE cur_num_pct_rows;
832
833 RETURN l_num_rows;
834 END do_pct_exist;
835
836 END gmf_copy_percentage_burden;