[Home] [Help]
PACKAGE BODY: APPS.GMF_COPY_ITEM_COST
Source
1 PACKAGE BODY gmf_copy_item_cost AS
2 /* $Header: gmfcpicb.pls 120.11.12000000.2 2007/04/25 10:39:18 pmarada ship $ */
3 /*****************************************************************************
4 * PACKAGE
5 * gmf_copy_item_cost
6 *
7 * DESCRIPTION
8 * Copy Item Costs Package
9 *
10 * CONTENTS
11 * PROCEDURE copy_item_cost ( ... )
12 *
13 * HISTORY
14 * 13-Oct-1999 Rajesh Seshadri
15 * 21-Nov-2000 Uday Moogala - Bug# 1419482 Copy Cost Enhancement.
16 * Add last 6 new parameters.
17 * 24-Jan-2002 Chetan Nagar - B2198228 Added paramter copy_to_upper_lvl
18 * for enhancement fix related to cost rollup (Ref. Bug 2116142).
19 * 30/Oct/2002 R.Sharath Kumar Bug# 2641405
20 * Added NOCOPY hint
21 ******************************************************************************/
22
23
24 PROCEDURE end_copy (
25 pi_errstat IN VARCHAR2,
26 pi_errmsg IN VARCHAR2
27 );
28
29 PROCEDURE copy_cost_dtl(
30 pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
31 pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
32 pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
33 pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
34 pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
35 pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
36 pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
37 pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
38 pi_range_type IN NUMBER,
39 pi_from_range IN VARCHAR2,
40 pi_to_range IN VARCHAR2,
41 pi_incr_pct IN NUMBER,
42 pi_incr_decr_cost IN NUMBER,
43 pi_rem_repl IN NUMBER,
44 pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
45 pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
46 pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE,
47 pi_copy_to_upper_lvl IN NUMBER
48 );
49
50
51
52
53 PROCEDURE copy_burden_dtl(
54 pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
55 pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
56 pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
57 pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
58 pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
59 pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
60 pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
61 pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
62 pi_range_type IN NUMBER,
63 pi_from_range IN VARCHAR2,
64 pi_to_range IN VARCHAR2,
65 pi_rem_repl IN NUMBER,
66 pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
67 pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
68 pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE
69 );
70
71
72
73
74 PROCEDURE delete_item_costs(
75 pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
76 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
77 pi_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
78 pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
79 pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
80 );
81
82
83 FUNCTION verify_frozen_costs(
84 pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
85 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
86 pi_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
87 pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
88 pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
89 )
90 RETURN NUMBER;
91
92 -- added this procedure as part of bug 5567102
93 PROCEDURE verify_frozen_periods (p_period_id IN gmf_period_statuses.period_id%TYPE,
94 p_period_code OUT NOCOPY gmf_period_statuses.period_code%TYPE,
95 p_period_status OUT NOCOPY gmf_period_statuses.period_status%TYPE );
96
97 -- Added to check record exists in a frozen period, bug 5672543
98 FUNCTION check_rec_infrozen_period(p_organization_id cm_cmpt_dtl.organization_id%TYPE,
99 p_inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
100 p_period_id cm_cmpt_dtl.period_id%TYPE,
101 p_cost_type_id cm_cmpt_dtl.cost_type_id%TYPE)
102 RETURN BOOLEAN ;
103 FUNCTION verify_item_assigned_to_org(
104 pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
105 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE
106 )
107 RETURN NUMBER;
108
109 PROCEDURE delete_burden_costs(
110 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
111 pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
112 pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE,
113 pi_range_type IN NUMBER,
114 pi_from_range IN VARCHAR2,
115 pi_to_range IN VARCHAR2
116 );
117
118 -- Static type indicators
119 G_ITEM NUMBER := 1;
120 G_ITEMCC NUMBER := 2;
121
122 -- WHO columns
123 g_user_id NUMBER;
124 g_login_id NUMBER;
125 g_prog_appl_id NUMBER;
126 g_program_id NUMBER;
127 g_request_id NUMBER;
128 g_effid_copy VARCHAR2(2) ;
129
130 /*****************************************************************************
131 * PROCEDURE
132 * copy_item_cost
133 *
134 * DESCRIPTION
135 * Copy Item Costs Procedure
136 * Copies costs from the one set of orgn/cost calendar/period/cost method
137 * to another for the item OR item cost class range specified on the form.
138 *
139 * INPUT PARAMETERS
140 * From and To organization_id/calendar/period/cost method
141 * Item from/to range
142 * Item cost class from/to range
143 * Increase or Decrease Cost Percentage
144 * Increase or Decrease Cost Amount
145 * Remove before copy or Replace during copy indicator
146 *
147 * OUTPUT PARAMETERS
148 * po_errbuf Completion message to the Concurrent Manager
149 * po_retcode Return code to the Concurrent Manager
150 *
151 * HISTORY
152 * 13-Oct-1999 Rajesh Seshadri
153 * 21-Nov-2000 Uday Moogala - Bug# 1419482 Copy Cost Enhancement.
154 *
155 * 24-Jan-2002 Chetan Nagar - B2198228 Added paramter copy_to_upper_lvl
156 * for enhancement fix related to cost rollup (Ref. Bug 2116142).
157 ******************************************************************************/
158
159 PROCEDURE copy_item_cost
160 (
161 po_errbuf OUT NOCOPY VARCHAR2,
162 po_retcode OUT NOCOPY VARCHAR2,
163 pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
164 pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
165 pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
166 pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
167 pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
168 pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
169 pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
170 pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
171 pi_item_from IN mtl_system_items_b_kfv.concatenated_segments%TYPE,
172 pi_item_to IN mtl_system_items_b_kfv.concatenated_segments%TYPE,
173 pi_itemcc_from IN mtl_categories_b_kfv.concatenated_segments%TYPE,
174 pi_itemcc_to IN mtl_categories_b_kfv.concatenated_segments%TYPE,
175 pi_incr_pct IN VARCHAR2,
176 pi_incr_decr_cost IN VARCHAR2,
177 pi_rem_repl IN VARCHAR2,
178 pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
179 pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
180 pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE,
181 pi_copy_to_upper_lvl IN VARCHAR2
182 )
183 IS
184
185 l_from_range VARCHAR2(32767);
186 l_to_range VARCHAR2(32767);
187 l_range_type NUMBER;
188 l_effid_copy VARCHAR2(4) ;
189
190 l_rem_repl NUMBER;
191 l_incr_pct NUMBER;
192 l_incr_decr_cost NUMBER;
193 l_copy_to_upper_lvl NUMBER;
194 e_same_from_to EXCEPTION;
195 e_no_cost_rows EXCEPTION;
196
197 BEGIN
198
199 /* uncomment the call below to write to a local file */
200 FND_FILE.PUT_NAMES('gmfcpic.log','gmfcpic.out','/appslog/opm_top/utl/opmm0dv/log');
201
202
203 gmf_util.msg_log( 'GMF_CPIC_START' );
204
205 gmf_util.msg_log( 'GMF_CPIC_SRCPARAM', nvl(to_char(pi_organization_id_from), ' '), nvl(pi_calendar_code_from, ' '), nvl(pi_period_code_from, ' '), nvl(to_char(pi_cost_type_id_from), ' ') );
206
207 gmf_util.msg_log( 'GMF_CPIC_TGTPARAM', nvl(to_char(pi_organization_id_to), ' '), nvl(pi_calendar_code_to, ' '), nvl(pi_period_code_to, ' '), nvl(to_char(pi_cost_type_id_to), ' ') );
208
209 gmf_util.msg_log( 'GMF_CPIC_ITEMRANGE', nvl(pi_item_from, ' '), nvl(pi_item_to, ' ') );
210 gmf_util.msg_log( 'GMF_CPIC_ITEMCCRANGE', nvl(pi_itemcc_from, ' '), nvl(pi_itemcc_to, ' ') );
211
212 gmf_util.msg_log( 'GMF_CPIC_INCPCT', nvl(pi_incr_pct, ' ') );
213 gmf_util.msg_log( 'GMF_CPIC_INCCOST', nvl(pi_incr_decr_cost, ' ') );
214
215 -- Bug# 1419482 Copy Cost Enhancement. Uday Moogala
216 IF ( (pi_period_code_to IS NULL) AND -- all periods
217 ((pi_all_periods_from IS NOT NULL) OR (pi_all_periods_to IS NOT NULL))
218 ) THEN
219
220 gmf_util.msg_log('GMF_CPIC_ALLPERIODS', nvl(pi_calendar_code_to, ' ') ) ;
221 gmf_util.msg_log('GMF_CPIC_PERIODS_RANGE', nvl(pi_all_periods_from, ' '),
222 nvl(pi_all_periods_to, ' '), nvl(pi_calendar_code_to, ' ') ) ;
223 END IF ;
224
225 -- End Bug# 1419482
226
227 l_rem_repl := 0;
228 IF ( pi_rem_repl = '1' ) THEN -- Remove before copy
229 l_rem_repl := 1;
230 gmf_util.msg_log( 'GMF_CPIC_OPTREM' );
231 ELSE -- Replace before copy
232 l_rem_repl := 0;
233 gmf_util.msg_log( 'GMF_CPIC_OPTREP' );
234 END IF;
235
236 -- B2198228
237 l_copy_to_upper_lvl := 0;
238 IF ( pi_copy_to_upper_lvl = '1' ) THEN
239 -- Copy lower level cost from source to upper level at target
240 l_copy_to_upper_lvl := 1;
241 gmf_util.msg_log( 'GMF_CPIC_TO_UPPER_YES' );
242 ELSE
243 l_copy_to_upper_lvl := 0;
244 gmf_util.msg_log( 'GMF_CPIC_TO_UPPER_NO' );
245 END IF;
246
247 gmf_util.log;
248
249 IF ( (pi_period_code_from = pi_period_code_to) AND
250 (pi_cost_type_id_from = pi_cost_type_id_to) AND
251 (pi_calendar_code_from = pi_calendar_code_to) AND
252 (pi_organization_id_from = pi_organization_id_to) ) THEN
253
254 gmf_util.msg_log( 'GMF_CP_SAME_FROMTO' );
255 RAISE e_same_from_to;
256 END IF;
257
258 -- Determine what kind of where clause needs to be concatenated
259 -- depending on what options were sent in
260 l_from_range := NULL;
261 l_to_range := NULL;
262 l_range_type := G_ITEM;
263
264 IF ( (pi_item_from IS NOT NULL) OR (pi_item_to IS NOT NULL) ) THEN
265 l_from_range := pi_item_from;
266 l_to_range := pi_item_to;
267 l_range_type := G_ITEM;
268 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 1 );
269 ELSIF ( (pi_itemcc_from IS NOT NULL) OR (pi_itemcc_to IS NOT NULL) ) THEN
270 l_from_range := pi_itemcc_from;
271 l_to_range := pi_itemcc_to;
272 l_range_type := G_ITEMCC;
273 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 1 );
274 ELSE
275 l_from_range := pi_item_from;
276 l_to_range := pi_item_to;
277 l_range_type := G_ITEM;
278 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 1 );
279 END IF;
280
281 -- Set the increase or decrease percentage and cost
282 IF( pi_incr_pct IS NOT NULL ) THEN
283 l_incr_pct := 1.0 + TO_NUMBER(pi_incr_pct)/100.0 ;
284 ELSE
285 l_incr_pct := 1.0;
286 END IF;
287 IF( pi_incr_decr_cost IS NOT NULL ) THEN
288 l_incr_decr_cost := TO_NUMBER(pi_incr_decr_cost);
289 ELSE
290 l_incr_decr_cost := 0.0;
291 END IF;
292
293 gmf_util.trace( ' Incr pct = ' || l_incr_pct || ' Incr/Decr Cost = ' ||l_incr_decr_cost, 1 );
294
295 -- Initialize WHO columns
296 g_user_id := FND_GLOBAL.USER_ID;
297 g_login_id := FND_GLOBAL.LOGIN_ID;
298 g_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
299 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
300 g_request_id := FND_GLOBAL.CONC_REQUEST_ID;
301
302 -- Bug# 1419482 Copy Cost Enhancement. Uday Moogala
303
304 BEGIN
305 g_effid_copy := FND_PROFILE.VALUE('GMF_CPIC_EFF') ;
309 gmf_util.msg_log('GMF_CPIC_EFFID', l_effid_copy ) ; --niyadav
306 SELECT decode(g_effid_copy, 'Y', 'YES', 'N', 'NO', '')
307 INTO l_effid_copy
308 FROM dual ;
310
311 EXCEPTION
312 WHEN OTHERS THEN
313 l_effid_copy := 'N' ;
314 gmf_util.msg_log('GMF_CPIC_EFFID', l_effid_copy) ;
315 END ;
316
317 -- End Bug# 1419482
318
319 -- Houston, do you copy?
320 copy_cost_dtl
321 (
322 pi_organization_id_from, pi_calendar_code_from,
323 pi_period_code_from, pi_cost_type_id_from,
324 pi_organization_id_to, pi_calendar_code_to,
325 pi_period_code_to, pi_cost_type_id_to,
326 l_range_type, l_from_range, l_to_range,
327 l_incr_pct,l_incr_decr_cost,l_rem_repl,
328 pi_all_periods_from, pi_all_periods_to,
329 pi_all_org_id, l_copy_to_upper_lvl
330 );
331 -- Copy that, Roger!
332
333
334 -- All is well
335 po_retcode := 0;
336 po_errbuf := NULL;
337 end_copy( 'NORMAL', NULL );
338 COMMIT;
339
340 gmf_util.log;
341 gmf_util.msg_log( 'GMF_CPIC_END' );
342
343 EXCEPTION
344 WHEN e_no_cost_rows THEN
345 po_retcode := 0;
346 po_errbuf := NULL;
347 end_copy( 'NORMAL', NULL );
348
349 WHEN e_same_from_to THEN
350 po_retcode := 0;
351 po_errbuf := NULL;
352 end_copy( 'NORMAL', NULL );
353
354 WHEN utl_file.invalid_path then
355 po_retcode := 3;
356 po_errbuf := 'Invalid path - '||to_char(SQLCODE) || ' ' || SQLERRM;
357 end_copy ('ERROR', NULL);
358
359 WHEN utl_file.invalid_mode then
360 po_retcode := 3;
361 po_errbuf := 'Invalid Mode - '||to_char(SQLCODE) || ' ' || SQLERRM;
362 end_copy ('ERROR', NULL);
363
364 WHEN utl_file.invalid_filehandle then
365 po_retcode := 3;
366 po_errbuf := 'Invalid filehandle - '||to_char(SQLCODE) || ' ' || SQLERRM;
367 end_copy ('ERROR', NULL);
368
369 WHEN utl_file.invalid_operation then
370 po_retcode := 3;
371 po_errbuf := 'Invalid operation - '||to_char(SQLCODE) || ' ' || SQLERRM;
372 end_copy ('ERROR', NULL);
373
374 WHEN utl_file.write_error then
375 po_retcode := 3;
376 po_errbuf := 'Write error - '||to_char(SQLCODE) || ' ' || SQLERRM;
377 end_copy ('ERROR', NULL);
378 WHEN others THEN
379 po_retcode := 3;
380 po_errbuf := to_char(SQLCODE) || ' ' || SQLERRM;
381 end_copy ('ERROR', po_errbuf);
382 END copy_item_cost;
383
384 /*****************************************************************************
385 * PROCEDURE
386 * copy_cost_dtl
387 *
388 * DESCRIPTION
389 * Copies item costs from source to target period
390 *
391 * INPUT PARAMETERS
392 * From: organization_id, calendar_code, period_code, cost_mthd_code
393 * To : organization_id, calendar_code, period_code, cost_mthd_code
394 * Range_Type: whether item or itemcost_class was specified by user
395 * From_Range, To_Range : from/to range or item/itemcost_class
396 * Increase_percentage: % by which costs have to be increased before copy
397 * Increase/Decrease cost: increase or decrease of cost before copy
398 * Remove_or_Replace indicator: Either costs in target period have to be
399 * removed before copy starts or just replace the existing rows
400 *
401 * HISTORY
402 * 13-Oct-1999 Rajesh Seshadri
403 * 09-Nov-1999 Rajesh Seshadri Bug 1069117 - The delete stmt should not be
404 * run again and again for the same item. Otherwise it will write only
405 * the last component row that is selected for copy
406 * 21-Nov-2000 Uday Moogala - Bug# 1419482 Copy Cost Enhancement :
407 * Copy to all periods and/or warehouses option
408 *
409 * 24-Jan-2002 Chetan Nagar - B2198228 Added paramter copy_to_upper_lvl
410 * for enhancement fix related to cost rollup (Ref. Bug 2116142).
411 * 27-Oct-2006 prasad marada Bug 5567156, 5567102. Not allowing to delete/update
412 * the cost for frozen periods.
413 * 24-Apr-2007 Prasad Marada BUg 5672543 Added call to check records in frozen
414 * period. In a frozen period existing costs not be changed during a copy.
415 * But New costs can be added though,
416 ******************************************************************************/
417
418 PROCEDURE copy_cost_dtl(
419 pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
420 pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
421 pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
422 pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
423 pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
424 pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
425 pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
426 pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
427 pi_range_type IN NUMBER,
428 pi_from_range IN VARCHAR2,
432 pi_rem_repl IN NUMBER,
429 pi_to_range IN VARCHAR2,
430 pi_incr_pct IN NUMBER,
431 pi_incr_decr_cost IN NUMBER,
433 pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
434 pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
435 pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE,
436 pi_copy_to_upper_lvl IN NUMBER
437 )
438 IS
439
440 TYPE rectyp_cost_detail IS RECORD (
441 cmpntcost_id cm_cmpt_dtl.cmpntcost_id%TYPE,
442 inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
443 cost_cmpntcls_id cm_cmpt_dtl.cost_cmpntcls_id%TYPE,
444 cost_analysis_code cm_cmpt_dtl.cost_analysis_code%TYPE,
445 cost_level cm_cmpt_dtl.cost_level%TYPE,
446 cmpnt_cost cm_cmpt_dtl.cmpnt_cost%TYPE,
447 burden_ind cm_cmpt_dtl.burden_ind%TYPE,
448 total_qty cm_cmpt_dtl.total_qty%TYPE,
449 rmcalc_type cm_cmpt_dtl.rmcalc_type%TYPE,
450 fmeff_id cm_cmpt_dtl.fmeff_id%TYPE,
451 text_code cm_cmpt_dtl.text_code%TYPE,
452 attribute1 cm_cmpt_dtl.attribute1%TYPE,
453 attribute2 cm_cmpt_dtl.attribute2%TYPE,
454 attribute3 cm_cmpt_dtl.attribute3%TYPE,
455 attribute4 cm_cmpt_dtl.attribute4%TYPE,
456 attribute5 cm_cmpt_dtl.attribute5%TYPE,
457 attribute6 cm_cmpt_dtl.attribute6%TYPE,
458 attribute7 cm_cmpt_dtl.attribute7%TYPE,
459 attribute8 cm_cmpt_dtl.attribute8%TYPE,
460 attribute9 cm_cmpt_dtl.attribute9%TYPE,
461 attribute10 cm_cmpt_dtl.attribute10%TYPE,
462 attribute11 cm_cmpt_dtl.attribute11%TYPE,
463 attribute12 cm_cmpt_dtl.attribute12%TYPE,
464 attribute13 cm_cmpt_dtl.attribute13%TYPE,
465 attribute14 cm_cmpt_dtl.attribute14%TYPE,
466 attribute15 cm_cmpt_dtl.attribute15%TYPE,
467 attribute16 cm_cmpt_dtl.attribute16%TYPE,
468 attribute17 cm_cmpt_dtl.attribute17%TYPE,
469 attribute18 cm_cmpt_dtl.attribute18%TYPE,
470 attribute19 cm_cmpt_dtl.attribute19%TYPE,
471 attribute20 cm_cmpt_dtl.attribute20%TYPE,
472 attribute21 cm_cmpt_dtl.attribute21%TYPE,
473 attribute22 cm_cmpt_dtl.attribute22%TYPE,
474 attribute23 cm_cmpt_dtl.attribute23%TYPE,
475 attribute24 cm_cmpt_dtl.attribute24%TYPE,
476 attribute25 cm_cmpt_dtl.attribute25%TYPE,
477 attribute26 cm_cmpt_dtl.attribute26%TYPE,
478 attribute27 cm_cmpt_dtl.attribute27%TYPE,
479 attribute28 cm_cmpt_dtl.attribute28%TYPE,
480 attribute29 cm_cmpt_dtl.attribute29%TYPE,
481 attribute30 cm_cmpt_dtl.attribute30%TYPE
482 );
483
484 TYPE curtyp_cost_detail IS REF CURSOR;
485 cv_cost_detail curtyp_cost_detail;
486
487 TYPE curtyp_periods IS REF CURSOR;
488 cv_periods curtyp_periods;
489
490 TYPE curtyp_org IS REF CURSOR;
491 cv_org curtyp_org;
492
493 r_cost_detail rectyp_cost_detail;
494
495
496 l_sql_stmt VARCHAR2(2000);
497 l_sql_org VARCHAR2(2000) ;
498 l_sql_periods VARCHAR2(2000) ;
499 l_org_id gmf_legal_entities.legal_entity_id%TYPE ;
500 l_organization_id_from cm_cmpt_dtl.organization_id%TYPE;
501 l_organization_id_to cm_cmpt_dtl.organization_id%TYPE;
502
503 l_period_id_to cm_cmpt_dtl.period_id%TYPE;
504
505 l_from_range VARCHAR2(32767);
506 l_to_range VARCHAR2(32767);
507
508 l_rem_repl NUMBER;
509 l_incr_pct NUMBER;
510 l_incr_decr_cost NUMBER;
511
512 l_curr_inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE;
513 l_curr_organization_id cm_cmpt_dtl.organization_id%TYPE;
514 l_curr_period_code cm_cmpt_dtl.period_code%TYPE;
515 l_frozen_flag NUMBER;
516 l_assigned_flag NUMBER;
517
518 l_curr_inventory_item_id2 cm_cmpt_dtl.inventory_item_id%TYPE;
519 l_curr_organization_id2 cm_cmpt_dtl.organization_id%TYPE;
520 l_curr_period_code2 cm_cmpt_dtl.period_code%TYPE;
521
522 l_cost_rows NUMBER;
523 l_cost_rows_upd NUMBER;
524 l_cost_rows_ins NUMBER;
525 l_cost_rows_skip NUMBER;
526 -- bug 5567102
527 l_period_code gmf_period_statuses.period_code%TYPE;
531
528 l_period_status gmf_period_statuses.period_status%TYPE;
529
530 l_copy_to_upper_lvl NUMBER;
532 pi_period_id_to NUMBER;
533 pi_period_id_from NUMBER;
534
535 --e_same_from_to EXCEPTION;
536 e_item_is_frozen EXCEPTION;
537 e_item_not_assigned EXCEPTION;
538 e_period_frozen EXCEPTION;
539 --e_no_cost_rows EXCEPTION;
540
541
542
543 BEGIN
544
545 --finding the valus of period_id based upon the parameter values passed.
546
547 if(pi_period_code_to is not null) then
548 if(pi_organization_id_to is not null) then
549 SELECT gps.period_id
550 INTO pi_period_id_to
551 FROM gmf_period_statuses gps, hr_organization_information org
552 WHERE gps.PERIOD_CODE = pi_period_code_to
553 AND gps.CALENDAR_CODE = pi_calendar_code_to
554 AND gps.legal_entity_id = org.org_information2
555 AND org.organization_id = pi_organization_id_to
556 AND org.org_information_context = 'Accounting Information'
557 AND gps.cost_type_id = pi_cost_type_id_to;
558 else
559 SELECT period_id
560 INTO pi_period_id_to
561 FROM gmf_period_statuses
562 WHERE PERIOD_CODE = pi_period_code_to
563 AND CALENDAR_CODE = pi_calendar_code_to
564 AND legal_entity_id = pi_all_org_id
565 AND cost_type_id = pi_cost_type_id_to;
566 end if;
567 else
568 pi_period_id_to := NULL;
569 end if;
570
571 SELECT gps.period_id
572 INTO pi_period_id_from
573 FROM gmf_period_statuses gps, hr_organization_information org
574 WHERE gps.PERIOD_CODE = pi_period_code_from
575 AND gps.CALENDAR_CODE = pi_calendar_code_from
576 AND gps.legal_entity_id = org.org_information2
577 AND org.organization_id = pi_organization_id_from
578 AND org.org_information_context = 'Accounting Information'
579 AND gps.cost_type_id = pi_cost_type_id_from;
580
581
582 -- Set the input values
583 l_rem_repl := pi_rem_repl;
584 l_incr_pct := pi_incr_pct;
585 l_incr_decr_cost := pi_incr_decr_cost;
586
587 l_copy_to_upper_lvl := pi_copy_to_upper_lvl;
588
589 -- l_sql_stmt := '';
590 l_sql_stmt :=
591 ' SELECT ' ||
592 'cst.cmpntcost_id,' ||
593 'cst.inventory_item_id,' ||
594 'cst.cost_cmpntcls_id,' ||
595 'cst.cost_analysis_code,' ||
596 'cst.cost_level,' ||
597 'cst.cmpnt_cost,' ||
598 'cst.burden_ind,' ||
599 'cst.total_qty,' ||
600 'cst.rmcalc_type,' ||
601 'cst.fmeff_id,' ||
602 'cst.text_code,' ||
603 'cst.attribute1,' ||
604 'cst.attribute2,' ||
605 'cst.attribute3,' ||
606 'cst.attribute4,' ||
607 'cst.attribute5,' ||
608 'cst.attribute6,' ||
609 'cst.attribute7,' ||
610 'cst.attribute8,' ||
611 'cst.attribute9,' ||
612 'cst.attribute10,' ||
613 'cst.attribute11,' ||
614 'cst.attribute12,' ||
615 'cst.attribute13,' ||
616 'cst.attribute14,' ||
617 'cst.attribute15,' ||
618 'cst.attribute16,' ||
619 'cst.attribute17,' ||
620 'cst.attribute18,' ||
621 'cst.attribute19,' ||
622 'cst.attribute20,' ||
623 'cst.attribute21,' ||
624 'cst.attribute22,' ||
625 'cst.attribute23,' ||
626 'cst.attribute24,' ||
627 'cst.attribute25,' ||
628 'cst.attribute26,' ||
629 'cst.attribute27,' ||
630 'cst.attribute28,' ||
631 'cst.attribute29,' ||
632 'cst.attribute30 ' ||
633 ' FROM ' ||
634 'cm_cmpt_dtl cst' ||
635 ' WHERE ' ||
636 'cst.organization_id = :b_organization_id AND ' ||
637 'cst.period_id = :b_period_id AND ' ||
638 'cst.cost_type_id = :b_cost_type_id '; -- AND ' ||
639 -- 'cst.delete_mark = 0 ';--AND ' || bug 5567156
640 -- 'itm.delete_mark = 0 ';
641
642 -- Determine what kind of where clause needs to be concatenated
643 -- depending on what options were sent in
644 l_from_range := NULL;
645 l_to_range := NULL;
646
647 IF ( pi_range_type = G_ITEM ) THEN
648
649 l_sql_stmt := l_sql_stmt ||
650 ' AND exists ( '||
651 ' select ''z'' from MTL_ITEM_FLEXFIELDS x'||
652 ' where x.organization_id = cst.organization_id '||
653 ' and x.item_number between :pi_from_range and :pi_to_range '||
657 l_to_range := pi_to_range;
654 ' and x.inventory_item_id = cst.inventory_item_id )';
655
656 l_from_range := pi_from_range;
658
659 ELSIF ( pi_range_type = G_ITEMCC ) THEN
660
661 l_sql_stmt := l_sql_stmt ||
662 'AND EXISTS (select ''X'' from mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories y, mtl_categories_kfv z '||
663 ' where mdc.functional_area_id = 19 '||
664 ' and mdc.category_set_id = mcs.category_set_id '||
665 ' and mcs.category_set_id = y.category_set_id '||
666 ' and mcs.structure_id = z.structure_id '||
667 ' and y.inventory_item_id = cst.inventory_item_id '||
668 ' and y.organization_id = cst.organization_id '||
669 ' and y.category_id = z.category_id '||
670 ' and z.concatenated_segments >= nvl(:b_from_itemcc, z.concatenated_segments) '||
671 ' and z.concatenated_segments <= nvl(:b_to_itemcc, z.concatenated_segments))';
672
673 l_from_range := pi_from_range;
674 l_to_range := pi_to_range;
675 END IF;
676
677 -- Bug# 1419482 Copy Cost Enhancement. Uday Moogala
678
679 l_sql_stmt := l_sql_stmt ||
680 ' ORDER BY ' ||
681 'cst.cost_type_id, ' ||
682 'cst.inventory_item_id, ' ||
683 'cst.organization_id, ' ||
684 'cst.cost_cmpntcls_id, ' ||
685 'cst.cost_analysis_code, ' ||
686 'cst.cost_level ' ;
687
688
689 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 0 );
690
691 gmf_util.trace( 'Item details Query : ' || l_sql_stmt, 3 );
692
693
694 -- get org_id for the target calendar
695
696
697 IF (pi_organization_id_to IS NOT NULL) THEN
698
699 l_sql_org := '' ;
700
701 l_sql_org := 'SELECT :pi_organization_id_to '||' FROM dual ' ;
702
703 ELSE
704
705 -- 'All Warehouse' option selected
706 -- Build SQL to get target organization when from/to organization are not null.
707
708 l_sql_org := '' ;
709
710 l_sql_org :=
711 'SELECT ' ||
712 'hoi.organization_id ' ||
713 'FROM ' ||
714 'hr_organization_information hoi , mtl_parameters mp ' ||
715 ' WHERE ' ||
716 'hoi.org_information2 = :pi_all_org_id '||
717 ' AND hoi.org_information_context = ''Accounting Information'' '||
718 ' AND hoi.organization_id = mp.organization_id '||
719 ' and mp.process_enabled_flag = ''Y'' ' ;
720
721 --
722 -- We should AVOID copying on to source period and organization. So we should
723 -- eliminate 'from organization' from the query only when copying to same period,
724 -- same calendar and to all organizations. For all the other cases no need to check for
725 -- this condition since from period is getting eliminated from all periods query.
726 --
727
728 IF ( (pi_calendar_code_from = pi_calendar_code_to) AND
729 (pi_period_id_to IS NOT NULL) AND
730 (pi_period_id_from = pi_period_id_to)
731 ) THEN
732 l_sql_org := l_sql_org ||' AND hoi.organization_id <> :pi_organization_id_from ' ;
733
734 END IF ;
735
736 -- bug 5567528, pmarada added hoi as alias to orderby
737 l_sql_org := l_sql_org || ' ORDER BY hoi.organization_id ' ;
738
739 END If ;
740
741
742 -- Build SQL to get target periods when From/To Periods are not null.
743
744 IF (pi_period_id_to IS NOT NULL) THEN -- copy to one period.
745
746 l_sql_periods := 'SELECT :pi_period_id_to FROM dual ' ;
747
748 ELSE
749 l_sql_periods := '' ;
750 if(pi_organization_id_to is not null) then
751 l_sql_periods := 'SELECT ' ||
752 'c3.period_id ' ||
753 'FROM ' ||
754 'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1, hr_organization_information d ' ||
755 'WHERE ' ||
756 'd.organization_id = :pi_organization_id_to AND '||
757 'd.org_information_context = ''Accounting Information'' AND '||
758 'c1.calendar_code = :pi_calendar_code_to AND ' ||
759 'c1.period_code = :pi_all_periods_from AND ' ||
760 'c2.calendar_code = :pi_calendar_code_to AND ' ||
761 'c2.period_code = :pi_all_periods_to AND ' ||
762 'c3.calendar_code = :pi_calendar_code_to AND ' ||
763 'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
764 'c2.cost_type_id = c3.cost_type_id AND ' ||
768 'c1.legal_entity_id = c2.legal_entity_id AND ' ||
765 'c1.cost_type_id = c2.cost_type_id AND ' ||
766 'c3.legal_entity_id = d.org_information2 AND ' ||
767 'c2.legal_entity_id = c3.legal_entity_id AND ' ||
769 'c3.start_date >= c1.start_date AND ' ||
770 'c3.end_date <= c2.end_date AND ' ||
771 'c3.period_status <> ''C'' ';
772 else
773 l_sql_periods := 'SELECT ' ||
774 'c3.period_id ' ||
775 'FROM ' ||
776 'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1 ' ||
777 'WHERE ' ||
778 'c1.calendar_code = :pi_calendar_code_to AND ' ||
779 'c1.period_code = :pi_all_periods_from AND ' ||
780 'c2.calendar_code = :pi_calendar_code_to AND ' ||
781 'c2.period_code = :pi_all_periods_to AND ' ||
782 'c3.calendar_code = :pi_calendar_code_to AND ' ||
783 'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
784 'c2.cost_type_id = c3.cost_type_id AND ' ||
785 'c1.cost_type_id = c2.cost_type_id AND ' ||
786 'c3.legal_entity_id = :pi_all_org_id AND ' ||
787 'c2.legal_entity_id = c3.legal_entity_id AND ' ||
788 'c1.legal_entity_id = c2.legal_entity_id AND ' ||
789 'c3.start_date >= c1.start_date AND ' ||
790 'c3.end_date <= c2.end_date AND ' ||
791 'c3.period_status <> ''C'' ';
792 end if;
793
794 IF (pi_calendar_code_from = pi_calendar_code_to) THEN
795
796 l_sql_periods := l_sql_periods||' AND c3.period_id <> :pi_period_id_from ';
797 END IF ;
798
799 l_sql_periods := l_sql_periods || ' ORDER BY c3.start_date' ;
800
801 END IF ; -- To Period code check
802
803 gmf_util.trace( 'Org Query : ' || l_sql_org, 3 );
804 gmf_util.trace( 'Periods Query : ' || l_sql_periods, 3 );
805
806 IF (pi_period_id_to IS NOT NULL) THEN
807 OPEN cv_periods FOR l_sql_periods
808 USING pi_period_id_to;
809 ELSIF (pi_calendar_code_from = pi_calendar_code_to) THEN
810 if(pi_organization_id_to is not null) then
811 OPEN cv_periods FOR l_sql_periods
812 USING pi_organization_id_to,
813 pi_calendar_code_to,
814 pi_all_periods_from,
815 pi_calendar_code_to,
816 pi_all_periods_to,
817 pi_calendar_code_to,
818 pi_cost_type_id_to,
819 pi_period_id_from;
820 else
821 OPEN cv_periods FOR l_sql_periods
822 USING pi_calendar_code_to,
823 pi_all_periods_from,
824 pi_calendar_code_to,
825 pi_all_periods_to,
826 pi_calendar_code_to,
827 pi_cost_type_id_to,
828 pi_all_org_id,
829 pi_period_id_from;
830
831 end if;
832 ELSIF (pi_calendar_code_from <> pi_calendar_code_to) THEN
833 if(pi_organization_id_to is not null) then
834 OPEN cv_periods FOR l_sql_periods
835 USING pi_organization_id_to,
836 pi_calendar_code_to,
837 pi_all_periods_from,
838 pi_calendar_code_to,
839 pi_all_periods_to,
840 pi_calendar_code_to,
841 pi_cost_type_id_to;
842 else
843 OPEN cv_periods FOR l_sql_periods
844 USING pi_calendar_code_to,
845 pi_all_periods_from,
846 pi_calendar_code_to,
847 pi_all_periods_to,
848 pi_calendar_code_to,
849 pi_cost_type_id_to,
850 pi_all_org_id;
851
852 end if;
853 END IF;
854
855
856 /* end sschinch dt 05/2/03 bug 2934528 Bind variable fix */
857 LOOP
858 FETCH cv_periods INTO l_period_id_to ;
859 EXIT WHEN cv_periods%NOTFOUND ;
860
861
862 IF (pi_organization_id_to IS NOT NULL) THEN
863 OPEN cv_org FOR l_sql_org
864 USING pi_organization_id_to;
865 ELSIF ((pi_calendar_code_from = pi_calendar_code_to) AND
866 (pi_period_id_to IS NOT NULL) AND
870 pi_organization_id_from;
867 (pi_period_id_from = pi_period_id_to)) THEN
868 OPEN cv_org FOR l_sql_org
869 USING pi_all_org_id,
871 ELSE
872 OPEN cv_org FOR l_sql_org
873 USING pi_all_org_id;
874
875 END IF;
876
877 LOOP
878
879 FETCH cv_org INTO l_organization_id_to ;
880 EXIT WHEN cv_org%NOTFOUND ;
881
882 gmf_util.log;
883 gmf_util.msg_log('GMF_CPIC_ALLWHSEPRD', l_organization_id_to,l_period_id_to) ;
884
885 -- End Bug# 1419482
886
887 l_curr_inventory_item_id := -1;
888 l_curr_organization_id := -1 ;
889 l_curr_period_code := ' ';
890 l_frozen_flag := 0;
891 l_assigned_flag := 1;
892 l_period_status := 'O'; -- bug 5567102
893
894 l_curr_inventory_item_id2 := -1;
895 l_curr_organization_id2 := -1;
896 l_curr_period_code2 := ' ';
897
898 l_cost_rows := 0;
899 l_cost_rows_upd := 0;
900 l_cost_rows_ins := 0;
901 l_cost_rows_skip := 0;
902
903 OPEN cv_cost_detail FOR l_sql_stmt USING
904 pi_organization_id_from,
905 pi_period_id_from,
906 pi_cost_type_id_from,
907 l_from_range,
908 l_to_range
909 ;
910 LOOP
911 FETCH cv_cost_detail INTO r_cost_detail;
912 EXIT WHEN cv_cost_detail%NOTFOUND;
913
914 /**
915 * Try update of cm_cmpt_dtl first
916 * Update can fail for two reasons: either the row is not there
917 * or, the row exists but is frozen (rollover_ind = 1)
918 * If the costs are frozen in the target period then do not update the rows
919 * in cm_cmpt_dtl nor delete them from cm_scst_led/cm_acst_led.
920 * The item cost rows should be left untouched in the target period even if
921 * one of the components is frozen.
922 */
923
924 -- Bug# 1419482 Copy Cost Enhancement. Uday Moogala
925
926 gmf_util.trace('item id and costcomp id...'|| r_cost_detail.inventory_item_id || '-' || r_cost_detail.cmpntcost_id,0) ;
927 gmf_util.trace( 'old cost = ' || r_cost_detail.cmpnt_cost , 3 );
928
929 r_cost_detail.cmpnt_cost := r_cost_detail.cmpnt_cost * l_incr_pct + l_incr_decr_cost;
930 --Bug# 1584302 The above line is moved here from the inner most loop.
931
932 gmf_util.trace( 'New cost = ' || r_cost_detail.cmpnt_cost , 3 );
933
934 l_cost_rows := l_cost_rows + 1;
935
936
937 <<process_cost_row>>
938 BEGIN
939 IF( (l_curr_inventory_item_id = r_cost_detail.inventory_item_id) AND
940 (l_frozen_flag = 1)) THEN
941 -- Skip rows for this item
942 gmf_util.trace( 'Skipping rows for Item ' || r_cost_detail.inventory_item_id ||
943 ' Org ' || l_organization_id_to || ' period ' || l_period_id_to, 0 );
944 RAISE e_item_is_frozen;
945 END IF;
946
947 IF( (l_curr_inventory_item_id = r_cost_detail.inventory_item_id) AND
948 (l_assigned_flag = 0)) THEN
949 gmf_util.trace( 'Item ' || r_cost_detail.inventory_item_id ||
950 ' is not assigned to the Org ' || l_organization_id_to, 0 );
951 RAISE e_item_not_assigned;
952 END IF;
953
954 IF (l_curr_inventory_item_id <> r_cost_detail.inventory_item_id) THEN
955
956 -- Update the current item_id
957 l_curr_inventory_item_id := r_cost_detail.inventory_item_id;
958
959 -- Find out if Item is frozen in the target period.
960 -- 1 if item is to be skipped, 0 if copy can proceed
961 l_frozen_flag := verify_frozen_costs(
962 l_curr_inventory_item_id, l_organization_id_to,
963 pi_calendar_code_to, l_period_id_to,
964 pi_cost_type_id_to );
965
966 gmf_util.trace( 'Verify_frozen: Item ' || r_cost_detail.inventory_item_id ||
967 ' Organization ' || l_organization_id_to || ' period ' || l_period_id_to ||
968 ' Status = ' || l_frozen_flag, 3 );
969
970 l_assigned_flag := verify_item_assigned_to_org(
971 l_curr_inventory_item_id, l_organization_id_to);
972
976 END IF;
973 gmf_util.trace( 'Verify_item_assigned_to_org: Item ' || r_cost_detail.inventory_item_id ||
974 ' Organization ' || l_organization_id_to ||
975 ' Status = ' || l_assigned_flag, 3 );
977
978 -- Item is frozen so skip this row for this item
979 IF( l_frozen_flag = 1 ) THEN
980 RAISE e_item_is_frozen;
981 END IF;
982
983 IF( l_assigned_flag = 0 ) THEN
984 gmf_util.trace( 'Item ' || r_cost_detail.inventory_item_id ||
985 ' is not assigned to the Org ' || l_organization_id_to, 0 );
986 RAISE e_item_not_assigned;
987 END IF;
988
989 -- Copy logic here
990 gmf_util.trace( 'Copying cost row:' ||
991 'Cc_id = ' || r_cost_detail.cmpntcost_id ||
992 ' Cmpnt = ' || r_cost_detail.cost_cmpntcls_id ||
993 ' Ancd = ' || r_cost_detail.cost_analysis_code ||
994 ' Level = ' || r_cost_detail.cost_level ||
995 ' Cost = ' || r_cost_detail.cmpnt_cost , 0 );
996
997 /* Bug# 1584302: Moved the next 2 lines into 1st for loop.
998 * r_cost_detail.cmpnt_cost := r_cost_detail.cmpnt_cost * l_incr_pct + l_incr_decr_cost;
999 * gmf_util.trace( 'New cost = ' || r_cost_detail.cmpnt_cost , 3 );
1000 */
1001
1002 /**
1003 * RS B1069117 - Call the delete stmt only once for an item
1004 */
1005 -- start for bug 5567102, pmarada
1006 IF( (l_curr_inventory_item_id2 = r_cost_detail.inventory_item_id) AND
1007 (l_period_status = 'F') AND ( l_rem_repl = 1 )
1008 ) THEN
1009 -- Skip this row for this item
1010 gmf_util.trace( 'Period ' || l_period_code ||
1011 ' is Frozen. You can not Delete Frozen period cost.', 0 );
1012 RAISE e_period_frozen;
1013
1014 END IF; -- end for bug 5567102,pmarada
1015
1016 IF (l_curr_inventory_item_id2 <> r_cost_detail.inventory_item_id) THEN
1017
1018 l_curr_inventory_item_id2 := r_cost_detail.inventory_item_id;
1019
1020 IF( l_rem_repl = 1 ) THEN
1021 -- start for bug 5567102, pmarada,
1022 -- Don't allow to delete the Frozen period costs.
1023 verify_frozen_periods(l_period_id_to, l_period_code, l_period_status);
1024 IF l_period_status = 'F' THEN
1025 -- For frozen period existing costs should not be changed during a copy.
1026 -- But New costs can be added though for the item, Bug 5672543
1027 IF (check_rec_infrozen_period(l_organization_id_to,
1028 l_curr_inventory_item_id2,
1029 l_period_id_to,
1030 pi_cost_type_id_to
1031 )) THEN
1032 gmf_util.trace( 'Period ' || l_period_code ||
1033 ' is Frozen. You can not Delete Frozen period cost.', 0 );
1034 RAISE e_period_frozen;
1035 END IF;
1036 END IF; -- end for bug 5567102 pmarada
1037 -- Delete the costs for the target parameters
1038 delete_item_costs(
1039 r_cost_detail.inventory_item_id,
1040 l_organization_id_to,
1041 pi_calendar_code_to,
1042 l_period_id_to,
1043 pi_cost_type_id_to
1044 );
1045 END IF;
1046 END IF;
1047
1048 <<insert_or_update>>
1049 DECLARE
1050 CURSOR c_updins_cc_id(
1051 p_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
1052 p_period_id IN cm_cmpt_dtl.period_id%TYPE,
1053 p_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE,
1054 p_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
1055 p_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
1056 p_cost_cmpntcls_id IN cm_cmpt_dtl.cost_cmpntcls_id%TYPE,
1060 SELECT
1057 p_cost_analysis_code IN cm_cmpt_dtl.cost_analysis_code%TYPE,
1058 p_cost_level IN cm_cmpt_dtl.cost_level%TYPE
1059 ) IS
1061 cmpntcost_id
1062 FROM
1063 cm_cmpt_dtl
1064 WHERE
1065 period_id = p_period_id AND
1066 cost_type_id = p_cost_type_id AND
1067 organization_id = p_organization_id AND
1068 inventory_item_id = p_inventory_item_id AND
1069 cost_cmpntcls_id = p_cost_cmpntcls_id AND
1070 cost_analysis_code = p_cost_analysis_code AND
1071 cost_level = p_cost_level;
1072
1073 l_updins_cc_id cm_cmpt_dtl.cmpntcost_id%TYPE;
1074 e_insert_row EXCEPTION;
1075 BEGIN
1076
1077 l_updins_cc_id := 0;
1078 /* B2198228 - If l_copy_to_upper_lvl flag is set to 1 then
1079 * always try to update, if we fail it will insert anyway */
1080 /* IF( l_rem_repl = 1 ) THEN */
1081 IF( l_rem_repl = 1 and l_copy_to_upper_lvl <> 1 ) THEN
1082 RAISE e_insert_row;
1083 END IF;
1084
1085 -- Verify whether the period is frozen or not. bug 5567102 start, pmarada
1086 -- if the period is frozen then don't update the cost.
1087 verify_frozen_periods(l_period_id_to, l_period_code, l_period_status);
1088 IF l_period_status = 'F' THEN
1089 gmf_util.trace( 'Period ' || l_period_code ||
1090 ' is Frozen. You can not Update Frozen period cost.', 0 );
1091 RAISE e_period_frozen;
1092 END IF; -- end for bug 5567102 pmarada
1093
1094 /** There is a unique index on cm_cmpt_dtl on these columns
1095 * and we expect only one row and only one row is fetched
1096 * if not we have bigger problems, houston!
1097 */
1098 IF ( l_copy_to_upper_lvl = 1 ) THEN
1099 /* B2198228 Pass hard-coded level - 0 since we are going to copy
1100 * lower level cost from source to this level at target */
1101 OPEN c_updins_cc_id( pi_calendar_code_to, l_period_id_to,
1102 pi_cost_type_id_to, l_organization_id_to,
1103 r_cost_detail.inventory_item_id,
1104 r_cost_detail.cost_cmpntcls_id,
1105 r_cost_detail.cost_analysis_code,
1106 0
1107 );
1108 ELSE
1109 OPEN c_updins_cc_id( pi_calendar_code_to, l_period_id_to,
1110 pi_cost_type_id_to, l_organization_id_to,
1111 r_cost_detail.inventory_item_id,
1112 r_cost_detail.cost_cmpntcls_id,
1113 r_cost_detail.cost_analysis_code,
1114 r_cost_detail.cost_level
1115 );
1116 END IF;
1117
1118 FETCH c_updins_cc_id INTO l_updins_cc_id;
1119 IF( c_updins_cc_id%FOUND ) THEN
1120
1121 /**
1122 * Delete from scst_led, acst_led for the target parameters
1123 * Update brdn_dtl and set cmpntcost_id to null
1124 * Update cmpt_dtl
1125 */
1126
1127 DELETE FROM
1128 cm_scst_led
1129 WHERE
1130 cmpntcost_id = l_updins_cc_id
1131 ;
1132
1133 gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from scst_led ', 1 );
1134
1135 DELETE FROM
1136 cm_acst_led
1137 WHERE
1138 cmpntcost_id = l_updins_cc_id
1139 ;
1140
1144 SET
1141 gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from acst_led', 1 );
1142
1143 UPDATE cm_brdn_dtl
1145 cmpntcost_id = NULL
1146 WHERE
1147 cmpntcost_id = l_updins_cc_id
1148 ;
1149
1150 gmf_util.trace( SQL%ROWCOUNT || ' rows updated in brdn_dtl', 0);
1151
1152 IF ( l_copy_to_upper_lvl = 1 and r_cost_detail.cost_level = 1 ) THEN
1153 -- B2198228 We have read cost from lower level at source and user
1154 -- wants to copy it to this level at target
1155
1156 UPDATE
1157 cm_cmpt_dtl
1158 SET
1159 cmpntcost_id = GEM5_CMPNT_COST_ID_S.NEXTVAL,
1160 cmpnt_cost = cmpnt_cost + r_cost_detail.cmpnt_cost,
1161 burden_ind = r_cost_detail.burden_ind,
1162 rollover_ind = 0,
1163 total_qty = 0,
1164 costcalc_orig = 4, -- B2232752 copied specially from lower level to upper level
1165 rmcalc_type = 0,
1166 rollup_ref_no = NULL,
1167 acproc_id = NULL,
1168 trans_cnt = 1,
1169 text_code = NULL,
1170 delete_mark = 0,
1171 last_update_date = SYSDATE,
1172 last_updated_by = g_user_id,
1173 last_update_login = g_login_id,
1174 request_id = g_request_id,
1175 program_application_id = g_prog_appl_id,
1176 program_id = g_program_id,
1177 program_update_date = SYSDATE
1178 WHERE
1179 cmpntcost_id = l_updins_cc_id
1180 ;
1181 ELSE
1182 UPDATE
1183 cm_cmpt_dtl
1184 SET
1185 cmpntcost_id = GEM5_CMPNT_COST_ID_S.NEXTVAL,
1186 cmpnt_cost = r_cost_detail.cmpnt_cost,
1187 burden_ind = r_cost_detail.burden_ind,
1188 fmeff_id = decode(g_effid_copy, -- Bug# 1419482
1189 'Y', r_cost_detail.fmeff_id,
1190 NULL),
1191 rollover_ind = 0,
1192 total_qty = 0,
1193 costcalc_orig = decode(l_copy_to_upper_lvl, 1, 4, 2), -- B2232752 copied specially from lower level to upper level
1194 rmcalc_type = 0,
1195 rollup_ref_no = NULL,
1196 acproc_id = NULL,
1197 trans_cnt = 1,
1198 text_code = NULL,
1199 delete_mark = 0,
1200 last_update_date = SYSDATE,
1201 last_updated_by = g_user_id,
1202 last_update_login = g_login_id,
1203 request_id = g_request_id,
1204 program_application_id = g_prog_appl_id,
1205 program_id = g_program_id,
1206 program_update_date = SYSDATE,
1207 attribute1 = r_cost_detail.attribute1,
1208 attribute2 = r_cost_detail.attribute2,
1212 attribute6 = r_cost_detail.attribute6,
1209 attribute3 = r_cost_detail.attribute3,
1210 attribute4 = r_cost_detail.attribute4,
1211 attribute5 = r_cost_detail.attribute5,
1213 attribute7 = r_cost_detail.attribute7,
1214 attribute8 = r_cost_detail.attribute8,
1215 attribute9 = r_cost_detail.attribute9,
1216 attribute10 = r_cost_detail.attribute10,
1217 attribute11 = r_cost_detail.attribute11,
1218 attribute12 = r_cost_detail.attribute12,
1219 attribute13 = r_cost_detail.attribute13,
1220 attribute14 = r_cost_detail.attribute14,
1221 attribute15 = r_cost_detail.attribute15,
1222 attribute16 = r_cost_detail.attribute16,
1223 attribute17 = r_cost_detail.attribute17,
1224 attribute18 = r_cost_detail.attribute18,
1225 attribute19 = r_cost_detail.attribute19,
1226 attribute20 = r_cost_detail.attribute20,
1227 attribute21 = r_cost_detail.attribute21,
1228 attribute22 = r_cost_detail.attribute22,
1229 attribute23 = r_cost_detail.attribute23,
1230 attribute24 = r_cost_detail.attribute24,
1231 attribute25 = r_cost_detail.attribute25,
1232 attribute26 = r_cost_detail.attribute26,
1233 attribute27 = r_cost_detail.attribute27,
1234 attribute28 = r_cost_detail.attribute28,
1235 attribute29 = r_cost_detail.attribute29,
1236 attribute30 = r_cost_detail.attribute30
1237 WHERE
1238 cmpntcost_id = l_updins_cc_id
1239 ;
1240
1241 gmf_util.trace( ' row updated to cmpt_dtl', 0 );
1242
1243 END IF; /* l_copy_to_upper_lvl = 1 */
1244 ELSE -- cursor not found
1245 -- update failed, try inserting the row into cm_cmpt_dtl
1246 RAISE e_insert_row;
1247 END IF; -- if row is found in target period
1248
1249 l_cost_rows_upd := l_cost_rows_upd + 1;
1250
1251 IF( c_updins_cc_id%ISOPEN ) THEN
1252 CLOSE c_updins_cc_id;
1253 END IF;
1254
1255 EXCEPTION
1256 WHEN e_insert_row THEN
1257 -- First close the open cursor
1258 IF( c_updins_cc_id%ISOPEN ) THEN
1259 CLOSE c_updins_cc_id;
1260 END IF;
1261 -- Attempt to insert the row
1262 INSERT INTO
1263 cm_cmpt_dtl(
1264 cmpntcost_id,
1265 inventory_item_id,
1266 organization_id,
1267 cost_cmpntcls_id,
1268 cost_analysis_code,
1269 cost_level,
1270 cmpnt_cost,
1271 burden_ind,
1272 fmeff_id,
1273 rollover_ind,
1274 total_qty,
1275 costcalc_orig,
1276 rmcalc_type,
1277 rollup_ref_no,
1278 acproc_id,
1279 trans_cnt,
1280 text_code,
1281 delete_mark,
1282 creation_date,
1283 created_by,
1284 last_update_date,
1288 program_application_id,
1285 last_updated_by,
1286 last_update_login,
1287 request_id,
1289 program_id,
1290 program_update_date,
1291 attribute1,
1292 attribute2,
1293 attribute3,
1294 attribute4,
1295 attribute5,
1296 attribute6,
1297 attribute7,
1298 attribute8,
1299 attribute9,
1300 attribute10,
1301 attribute11,
1302 attribute12,
1303 attribute13,
1304 attribute14,
1305 attribute15,
1306 attribute16,
1307 attribute17,
1308 attribute18,
1309 attribute19,
1310 attribute20,
1311 attribute21,
1312 attribute22,
1313 attribute23,
1314 attribute24,
1315 attribute25,
1316 attribute26,
1317 attribute27,
1318 attribute28,
1319 attribute29,
1320 attribute30,
1321 period_id,
1322 cost_type_id
1323 )
1324 VALUES (
1325 GEM5_CMPNT_COST_ID_S.NEXTVAL,
1326 r_cost_detail.inventory_item_id,
1327 l_organization_id_to,
1328 r_cost_detail.cost_cmpntcls_id,
1329 r_cost_detail.cost_analysis_code,
1330 decode(l_copy_to_upper_lvl, 1, 0, r_cost_detail.cost_level), -- B2198228
1331 r_cost_detail.cmpnt_cost,
1332 r_cost_detail.burden_ind,
1333 decode(g_effid_copy, 'Y', r_cost_detail.fmeff_id, -- Bug# 1419482
1334 NULL), -- fmeff_id,
1335 0, -- rollover_ind,
1336 0, -- total_qty,
1337 decode(l_copy_to_upper_lvl, 1, 4, 2), -- B2232752 2, -- costcalc_orig,
1338 0, -- rmcalc_type,
1339 NULL, -- rollup_ref_no,
1340 NULL, -- acproc_id,
1341 1, -- trans_cnt,
1342 NULL, -- text_code,
1343 0, -- delete_mark,
1344 SYSDATE, -- creation_date,
1345 g_user_id, -- created_by,
1346 SYSDATE, -- last_update_date,
1347 g_user_id, -- last_updated_by,
1348 g_login_id, -- last_update_login,
1349 g_request_id, -- request_id,
1350 g_prog_appl_id, -- program_application_id,
1351 g_program_id, -- program_id,
1352 SYSDATE, -- program_update_date,
1353 r_cost_detail.attribute1,
1354 r_cost_detail.attribute2,
1355 r_cost_detail.attribute3,
1356 r_cost_detail.attribute4,
1357 r_cost_detail.attribute5,
1358 r_cost_detail.attribute6,
1359 r_cost_detail.attribute7,
1363 r_cost_detail.attribute11,
1360 r_cost_detail.attribute8,
1361 r_cost_detail.attribute9,
1362 r_cost_detail.attribute10,
1364 r_cost_detail.attribute12,
1365 r_cost_detail.attribute13,
1366 r_cost_detail.attribute14,
1367 r_cost_detail.attribute15,
1368 r_cost_detail.attribute16,
1369 r_cost_detail.attribute17,
1370 r_cost_detail.attribute18,
1371 r_cost_detail.attribute19,
1372 r_cost_detail.attribute20,
1373 r_cost_detail.attribute21,
1374 r_cost_detail.attribute22,
1375 r_cost_detail.attribute23,
1376 r_cost_detail.attribute24,
1377 r_cost_detail.attribute25,
1378 r_cost_detail.attribute26,
1379 r_cost_detail.attribute27,
1380 r_cost_detail.attribute28,
1381 r_cost_detail.attribute29,
1382 r_cost_detail.attribute30,
1383 l_period_id_to,
1384 pi_cost_type_id_to
1385 );
1386
1387 l_cost_rows_ins := l_cost_rows_ins + 1;
1388 gmf_util.trace( SQL%ROWCOUNT || ' rows inserted to cmpt_dtl', 0 );
1389
1390 END insert_or_update;
1391
1392 EXCEPTION
1393 WHEN e_item_is_frozen THEN
1394 -- Just continue the loop
1395 l_cost_rows_skip := l_cost_rows_skip + 1;
1396 null;
1397 WHEN e_item_not_assigned THEN
1398 NULL;
1399 WHEN e_period_frozen THEN -- bug 5567102
1400 NULL;
1401 END process_cost_row;
1402 END LOOP; -- End of main cursor loop
1403 CLOSE cv_cost_detail;
1404
1405 IF( l_cost_rows > 0 ) THEN
1406 gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_cost_rows) );
1407 gmf_util.msg_log( 'GMF_CP_ROWS_UPDINS', TO_CHAR(l_cost_rows_upd), TO_CHAR(l_cost_rows_ins) );
1408 ELSE
1409 gmf_util.msg_log( 'GMF_CP_NO_ROWS' );
1410 END IF;
1411
1412 IF( l_cost_rows_skip > 0 ) THEN
1413 gmf_util.msg_log( 'GMF_CPIC_ROWS_FRZ', TO_CHAR(l_cost_rows_skip) );
1414 END IF;
1415
1416 END LOOP ; -- Organizations loop
1417 CLOSE cv_org;
1418
1419 END LOOP ; -- Periods loop
1420 CLOSE cv_periods;
1421
1422 gmf_util.log;
1423 gmf_util.msg_log( 'GMF_CPIC_ITM_END' );
1424 gmf_util.log;
1425
1426 END copy_cost_dtl;
1427
1428
1429 /*****************************************************************************
1430 * PROCEDURE
1431 * end_copy
1432 *
1433 * DESCRIPTION
1434 * Sets the concurrent manager completion status
1435 *
1436 * INPUT PARAMETERS
1437 * pi_errstat - Completion status, must be one of 'NORMAL', 'WARNING', or
1438 * 'ERROR'
1439 * pi_errmsg - Completion message to be passed back
1440 *
1441 * HISTORY
1442 * 13-Oct-1999 Rajesh Seshadri
1443 *
1444 ******************************************************************************/
1445
1446 PROCEDURE end_copy (
1447 pi_errstat IN VARCHAR2,
1448 pi_errmsg IN VARCHAR2
1449 )
1450 IS
1451 l_retval BOOLEAN;
1452 BEGIN
1453
1454 l_retval := fnd_concurrent.set_completion_status(pi_errstat,pi_errmsg);
1455
1456 END end_copy;
1457
1458 /*****************************************************************************
1459 * PROCEDURE
1460 * delete_item_costs
1461 *
1462 * DESCRIPTION
1463 * Deletes the child rows from cm_scst_led, cm_acst_led and sets
1464 * cmpntcost_id to null in cm_brdn_dtl for the cost parameters passed
1465 * NOTE: We do not have to worry about rollover_ind here since this procedure
1466 * is not even called if the item is frozen in the target period.
1467 *
1468 * INPUT PARAMETERS
1469 * item_id, organization_id, calendar_code, period_code, cost_mthd_code
1470 *
1471 * HISTORY
1472 * 13-Oct-1999 Rajesh Seshadri
1473 *
1474 ******************************************************************************/
1475
1476 PROCEDURE delete_item_costs(
1477 pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
1478 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
1482 )
1479 pi_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
1480 pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
1481 pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
1483 IS
1484 CURSOR c_cc_id(
1485 p_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
1486 p_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
1487 p_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
1488 p_period_id IN cm_cmpt_dtl.period_id%TYPE,
1489 p_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
1490 )
1491 IS
1492 SELECT
1493 cmpntcost_id
1494 FROM
1495 cm_cmpt_dtl
1496 WHERE
1497 inventory_item_id = p_inventory_item_id AND
1498 organization_id = p_organization_id AND
1499 period_id = p_period_id AND
1500 cost_type_id = p_cost_type_id
1501 FOR UPDATE
1502 ;
1503
1504 BEGIN
1505
1506 gmf_util.trace( 'Deleting dependent rows', 0 );
1507 gmf_util.trace( 'Item:' || pi_inventory_item_id || ' Org:' || pi_organization_id ||
1508 ' Cal:' || pi_calendar_code || ' Per:' || pi_period_id || ' Mthd:' ||
1509 pi_cost_type_id , 0 );
1510
1511 FOR r_cc_id IN c_cc_id(
1512 pi_inventory_item_id, pi_organization_id, pi_calendar_code, pi_period_id, pi_cost_type_id
1513 ) LOOP
1514
1515 -- Delete rows from acst_led
1516 DELETE FROM
1517 cm_acst_led
1518 WHERE
1519 cmpntcost_id = r_cc_id.cmpntcost_id
1520 ;
1521
1522 gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from acst_led', 3 );
1523
1524 -- Delete rows from scst_led
1525 DELETE FROM
1526 cm_scst_led
1527 WHERE
1528 cmpntcost_id = r_cc_id.cmpntcost_id
1529 ;
1530
1531 gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from scst_led', 3 );
1532
1533 -- Update brdn_dtl
1534 UPDATE
1535 cm_brdn_dtl
1536 SET
1537 cmpntcost_id = NULL
1538 WHERE
1539 cmpntcost_id = r_cc_id.cmpntcost_id
1540 ;
1541
1542 gmf_util.trace( SQL%ROWCOUNT || ' rows updated in brdn_dtl', 3 );
1543
1544 -- Finally delete the row itself from cmpt_dtl
1545 DELETE FROM
1546 cm_cmpt_dtl
1547 WHERE CURRENT OF c_cc_id
1548 ;
1549
1550 gmf_util.trace( SQL%ROWCOUNT || ' rows deleted from cmpt_dtl', 3 );
1551
1552 END LOOP;
1553
1554 END delete_item_costs;
1555
1556 /*****************************************************************************
1557 * FUNCTION
1558 * verify_frozen_costs
1559 *
1560 * DESCRIPTION
1561 * Verifies if the item costs are frozen in the copy-to period
1562 *
1563 * INPUT PARAMETERS
1564 * calendar_code, period_code, cost_mthd_code, organization_id, item
1565 *
1566 * HISTORY
1567 * 13-Oct-1999 Rajesh Seshadri
1568 *
1569 ******************************************************************************/
1570
1571 FUNCTION verify_frozen_costs(
1572 pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
1573 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
1574 pi_calendar_code IN cm_cmpt_dtl.calendar_code%TYPE,
1575 pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
1576 pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE
1577 )
1578 RETURN NUMBER IS
1579
1580 l_frozen_ind NUMBER;
1581 BEGIN
1582 gmf_util.trace( 'Entering verify_frozen_costs', 0 );
1583
1584 l_frozen_ind := 0;
1585 SELECT nvl(max(rollover_ind),0) INTO l_frozen_ind
1586 FROM
1587 cm_cmpt_dtl
1588 WHERE period_id = pi_period_id AND
1589 cost_type_id = pi_cost_type_id AND
1590 organization_id = pi_organization_id AND
1591 inventory_item_id = pi_inventory_item_id
1592 ;
1593
1594 RETURN l_frozen_ind;
1595
1596 END verify_frozen_costs;
1597 /*****************************************************************************
1598 * Procedure
1599 * verify_frozen_periods
1600 *
1601 * DESCRIPTION
1602 * Verifies if the period is frozen or not
1603 *
1604 * INPUT PARAMETERS
1605 * p_period_id period as input parameter and period_code and period_status are out parameters
1606 *
1607 * HISTORY
1608 * 12-Oct-2006 pmarada, created for bug 5567102
1609 *
1610 ******************************************************************************/
1611 PROCEDURE verify_frozen_periods (p_period_id IN gmf_period_statuses.period_id%TYPE,
1612 p_period_code OUT NOCOPY gmf_period_statuses.period_code%TYPE,
1613 p_period_status OUT NOCOPY gmf_period_statuses.period_status%TYPE )
1617 SELECT period_code, period_status FROM gmf_period_statuses
1614 IS
1615
1616 CURSOR cur_froz_periods (cp_period_id gmf_period_statuses.period_id%TYPE) IS
1618 WHERE period_id = cp_period_id;
1619
1620 l_per_code gmf_period_statuses.period_code%TYPE;
1621 l_per_status gmf_period_statuses.period_status%TYPE;
1622
1623 BEGIN
1624 gmf_util.trace( 'Entering verify_frozen_periods', 0 );
1625 OPEN cur_froz_periods (p_period_id);
1626 FETCH cur_froz_periods INTO l_per_code, l_per_status;
1627 CLOSE cur_froz_periods;
1628
1629 p_period_code := l_per_code ;
1630 p_period_status := l_per_status ;
1631
1632 END verify_frozen_periods;
1633
1634 /*****************************************************************************
1635 * Procedure
1636 * check_rec_infrozen_period
1637 *
1638 * DESCRIPTION
1639 * Verifies if there exists any records for the frozen period.
1640 * In Frozen period existing costs should not be changed during a copy.
1641 * New costs can be added though.
1642 *
1643 * INPUT PARAMETERS
1644 * p_organization_id, p_inventory_item_id, p_period_id, p_cost-type_id
1645 *
1646 * HISTORY
1647 * 24-Apr-2007 pmarada, created for bug 5672543
1648 *
1649 ******************************************************************************/
1650 FUNCTION check_rec_infrozen_period(p_organization_id cm_cmpt_dtl.organization_id%TYPE,
1651 p_inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
1652 p_period_id cm_cmpt_dtl.period_id%TYPE,
1653 p_cost_type_id cm_cmpt_dtl.cost_type_id%TYPE
1654 )
1655 RETURN BOOLEAN IS
1656
1657 CURSOR cur_check_rec (cp_organization_id cm_cmpt_dtl.organization_id%TYPE,
1658 cp_inventory_item_id cm_cmpt_dtl.inventory_item_id%TYPE,
1659 cp_period_id cm_cmpt_dtl.period_id%TYPE,
1660 cp_cost_type_id cm_cmpt_dtl.cost_type_id%TYPE) IS
1661 SELECT 'x' FROM cm_cmpt_dtl
1662 WHERE organization_id = cp_organization_id
1663 AND inventory_item_id = cp_inventory_item_id
1664 AND period_id = cp_period_id
1665 AND cost_type_id = cp_cost_type_id;
1666 l_found VARCHAR2(1);
1667 BEGIN
1668
1669 OPEN cur_check_rec (p_organization_id, p_inventory_item_id, p_period_id, p_cost_type_id );
1670 FETCH cur_check_rec INTO l_found;
1671 CLOSE cur_check_rec;
1672 IF l_found IS NOT NULL THEN
1673 RETURN TRUE;
1674 ELSE
1675 RETURN FALSE;
1676 END IF;
1677
1678 END check_rec_infrozen_period;
1679
1680 /*****************************************************************************
1681 * PROCEDURE
1682 * copy_burden_costs
1683 *
1684 * DESCRIPTION
1685 * Copy Overhead Costs Procedure
1686 * Copies overhead costs from the one set of orgn/cost calendar/period/cost
1687 * method to another for the item OR item cost class range specified on the
1688 * form.
1689 * INPUT PARAMETERS
1690 * From and To organization_id/calendar/period/cost method
1691 * Item from/to range
1692 * Item cost class from/to range
1693 * Remove before copy or Replace during copy indicator
1694 *
1695 * OUTPUT PARAMETERS
1696 * po_errbuf Completion message to the Concurrent Manager
1697 * po_retcode Return code to the Concurrent Manager
1698 *
1699 * INPUT PARAMETERS
1700 * calendar_code, period_code, cost_mthd_code, organization_id, item
1701 *
1702 * HISTORY
1703 * 13-Oct-1999 Rajesh Seshadri
1704 * 21-Nov-2000 Uday Moogala - Bug# 1419482 Copy Cost Enhancement.
1705 *
1706 ******************************************************************************/
1707
1708
1709
1710 PROCEDURE copy_burden_cost
1711 (
1712 po_errbuf OUT NOCOPY VARCHAR2,
1713 po_retcode OUT NOCOPY VARCHAR2,
1714 pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
1715 pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
1716 pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
1717 pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
1718 pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
1719 pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
1720 pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
1721 pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
1722 pi_item_from IN mtl_item_flexfields.item_number%TYPE,
1723 pi_item_to IN mtl_item_flexfields.item_number%TYPE,
1724 pi_itemcc_from IN mtl_categories_b_kfv.concatenated_segments%TYPE,
1725 pi_itemcc_to IN mtl_categories_b_kfv.concatenated_segments%TYPE,
1726 pi_rem_repl IN VARCHAR2,
1727 pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
1731
1728 pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
1729 pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE
1730 )
1732 IS
1733
1734 l_from_range VARCHAR2(32767);
1735 l_to_range VARCHAR2(32767);
1736 l_range_type NUMBER;
1737 l_effid_copy VARCHAR2(4) ;
1738
1739 l_rem_repl NUMBER;
1740 e_same_from_to EXCEPTION;
1741 e_no_cost_rows EXCEPTION;
1742
1743 BEGIN
1744 /* uncomment the call below to write to a local file */
1745
1746 FND_FILE.PUT_NAMES('gmfcpoc.log','gmfcpoc.out','/appslog/opm_top/utl/opmm0dv/log');
1747
1748
1749 gmf_util.msg_log( 'GMF_CPOC_START' );
1750 /*gmf_util.msg_log( 'GMF_CPOC_SRCPARAM', nvl(pi_organization_id_from, ' '), nvl(pi_calendar_code_from, ' '), nvl(pi_period_code_from, ' '), nvl(pi_cost_type_id_from, ' ') );
1751
1752 gmf_util.msg_log( 'GMF_CPOC_TGTPARAM', nvl(pi_organization_id_to, ' '), nvl(pi_calendar_code_to, ' '), nvl(pi_period_code_to, ' '), nvl(pi_cost_type_id_to, ' ') );
1753
1754 gmf_util.msg_log( 'GMF_CPOC_ITEMRANGE', nvl(pi_item_from, ' '), nvl(pi_item_to, ' ') );
1755 gmf_util.msg_log( 'GMF_CPOC_ITEMCCRANGE', nvl(pi_itemcc_from, ' '), nvl(pi_itemcc_to, ' ') );*/
1756
1757
1758 -- Bug# 1419482 Copy Cost Enhancement. Uday Moogala
1759 IF ( (pi_period_code_to IS NULL) AND -- all periods
1760 ((pi_all_periods_from IS NOT NULL) OR (pi_all_periods_to IS NOT NULL))
1761 ) THEN
1762
1763 gmf_util.msg_log('GMF_CPOC_ALLPERIODS', nvl(pi_calendar_code_to, ' ') ) ;
1764 gmf_util.msg_log('GMF_CPOC_PERIODS_RANGE', nvl(pi_all_periods_from, ' '),
1765 nvl(pi_all_periods_to, ' '), nvl(pi_calendar_code_to, ' ') ) ;
1766
1767 END IF ;
1768
1769 -- End Bug# 1419482
1770
1771 l_rem_repl := 0;
1772 IF ( pi_rem_repl = '1' ) THEN -- Remove before copy
1773 l_rem_repl := 1;
1774 gmf_util.msg_log( 'GMF_CPOC_OPTREM' );
1775 ELSE -- Replace before copy
1776 l_rem_repl := 0;
1777 gmf_util.msg_log( 'GMF_CPOC_OPTREP' );
1778 END IF;
1779
1780 gmf_util.log;
1781
1782 IF ( (pi_period_code_from = pi_period_code_to) AND
1783 (pi_cost_type_id_from = pi_cost_type_id_to) AND
1784 (pi_calendar_code_from = pi_calendar_code_to) AND
1785 (pi_organization_id_from = pi_organization_id_to) ) THEN
1786
1787 gmf_util.msg_log( 'GMF_CP_SAME_FROMTO' );
1788 RAISE e_same_from_to;
1789 END IF;
1790
1791 -- Determine what kind of where clause needs to be concatenated
1792 -- depending on what options were sent in
1793 l_from_range := NULL;
1794 l_to_range := NULL;
1795 l_range_type := G_ITEM;
1796 IF ( (pi_item_from IS NOT NULL) OR (pi_item_to IS NOT NULL) ) THEN
1797 l_from_range := pi_item_from;
1798 l_to_range := pi_item_to;
1799 l_range_type := G_ITEM;
1800 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 0 );
1801 ELSIF ( (pi_itemcc_from IS NOT NULL) OR (pi_itemcc_to IS NOT NULL) ) THEN
1802 l_from_range := pi_itemcc_from;
1803 l_to_range := pi_itemcc_to;
1804 l_range_type := G_ITEMCC;
1805 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 0 );
1806 ELSE
1807 l_from_range := pi_item_from;
1808 l_to_range := pi_item_to;
1809 l_range_type := G_ITEM;
1810 gmf_util.trace( 'Range : ' || l_from_range || ' - ' || l_to_range, 0);
1811 END IF;
1812
1813
1814 -- Initialize WHO columns
1815 g_user_id := FND_GLOBAL.USER_ID;
1816 g_login_id := FND_GLOBAL.LOGIN_ID;
1817 g_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
1818 g_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
1819 g_request_id := FND_GLOBAL.CONC_REQUEST_ID;
1820
1821 -- Houston, do you copy?
1822 copy_burden_dtl
1823 (
1824 pi_organization_id_from, pi_calendar_code_from,
1825 pi_period_code_from, pi_cost_type_id_from,
1826 pi_organization_id_to, pi_calendar_code_to,
1827 pi_period_code_to, pi_cost_type_id_to,
1828 l_range_type, l_from_range, l_to_range,
1829 l_rem_repl,
1830 pi_all_periods_from, pi_all_periods_to,
1831 pi_all_org_id
1832 );
1833 -- Copy that, Roger!
1834
1835 -- All is well
1836 po_retcode := 0;
1837 po_errbuf := NULL;
1838 end_copy( 'NORMAL', NULL );
1839 COMMIT;
1840
1841
1842 gmf_util.log;
1843 gmf_util.msg_log( 'GMF_CPIC_END' );
1844
1845 EXCEPTION
1846 WHEN e_no_cost_rows THEN
1847 po_retcode := 0;
1848 po_errbuf := NULL;
1849 end_copy( 'NORMAL', NULL );
1850
1851 WHEN e_same_from_to THEN
1852 po_retcode := 0;
1856 WHEN utl_file.invalid_path then
1853 po_errbuf := NULL;
1854 end_copy( 'NORMAL', NULL );
1855
1857 po_retcode := 3;
1858 po_errbuf := 'Invalid path - '||to_char(SQLCODE) || ' ' || SQLERRM;
1859 end_copy ('ERROR', NULL);
1860 WHEN utl_file.invalid_mode then
1861 po_retcode := 3;
1862 po_errbuf := 'Invalid Mode - '||to_char(SQLCODE) || ' ' || SQLERRM;
1863 end_copy ('ERROR', NULL);
1864 WHEN utl_file.invalid_filehandle then
1865 po_retcode := 3;
1866 po_errbuf := 'Invalid filehandle - '||to_char(SQLCODE) || ' ' || SQLERRM;
1867 end_copy ('ERROR', NULL);
1868 WHEN utl_file.invalid_operation then
1869 po_retcode := 3;
1870 po_errbuf := 'Invalid operation - '||to_char(SQLCODE) || ' ' || SQLERRM;
1871 end_copy ('ERROR', NULL);
1872 WHEN utl_file.write_error then
1873 po_retcode := 3;
1874 po_errbuf := 'Write error - '||to_char(SQLCODE) || ' ' || SQLERRM;
1875 end_copy ('ERROR', NULL);
1876 WHEN others THEN
1877 po_retcode := 3;
1878 po_errbuf := to_char(SQLCODE) || ' ' || SQLERRM;
1879 end_copy ('ERROR', po_errbuf);
1880
1881 END copy_burden_cost;
1882
1883
1884 /*****************************************************************************
1885 * PROCEDURE
1886 * copy_burden_dtl
1887 *
1888 * DESCRIPTION
1889 * Verifies if the item costs are frozen in the copy-to period
1890 *
1891 * INPUT PARAMETERS
1892 * calendar_code, period_code, cost_mthd_code, organization_id
1893 *
1894 * HISTORY
1895 * 13-Oct-1999 Rajesh Seshadri
1896 * 21-Nov-2000 Uday Moogala - Bug# 1419482 Copy Cost Enhancement.
1897 *
1898 ******************************************************************************/
1899
1900
1901 PROCEDURE copy_burden_dtl(
1902 pi_organization_id_from IN cm_cmpt_dtl.organization_id%TYPE,
1903 pi_calendar_code_from IN cm_cmpt_dtl.calendar_code%TYPE,
1904 pi_period_code_from IN cm_cmpt_dtl.period_code%TYPE,
1905 pi_cost_type_id_from IN cm_cmpt_dtl.cost_type_id%TYPE,
1906 pi_organization_id_to IN cm_cmpt_dtl.organization_id%TYPE,
1907 pi_calendar_code_to IN cm_cmpt_dtl.calendar_code%TYPE,
1908 pi_period_code_to IN cm_cmpt_dtl.period_code%TYPE,
1909 pi_cost_type_id_to IN cm_cmpt_dtl.cost_type_id%TYPE,
1910 pi_range_type IN NUMBER,
1911 pi_from_range IN VARCHAR2,
1912 pi_to_range IN VARCHAR2,
1913 pi_rem_repl IN NUMBER,
1914 pi_all_periods_from IN cm_cmpt_dtl.period_code%TYPE,
1915 pi_all_periods_to IN cm_cmpt_dtl.period_code%TYPE,
1916 pi_all_org_id IN gmf_legal_entities.legal_entity_id%TYPE
1917 )
1918
1919 IS
1920
1921 TYPE rectype_brdn_dtl IS RECORD(
1922 inventory_item_id cm_brdn_dtl.inventory_item_id%TYPE,
1923 -- item_no mtl_system_items_b_kfv.concatenated_segments%TYPE,
1924 resources cm_brdn_dtl.resources%TYPE,
1925 cost_cmpntcls_id cm_brdn_dtl.cost_cmpntcls_id%TYPE,
1926 cost_analysis_code cm_brdn_dtl.cost_analysis_code%TYPE,
1927 burden_qty cm_brdn_dtl.burden_qty%TYPE,
1928 burden_usage cm_brdn_dtl.burden_usage%TYPE,
1929 burden_um cm_brdn_dtl.burden_um%TYPE,
1930 item_qty cm_brdn_dtl.item_qty%TYPE,
1931 item_um cm_brdn_dtl.item_um%TYPE,
1932 burden_factor cm_brdn_dtl.burden_factor%TYPE
1933 );
1934 r_brdn_dtl rectype_brdn_dtl;
1935
1936 TYPE curtyp_brdn_dtl IS REF CURSOR;
1937 cv_brdn_dtl curtyp_brdn_dtl;
1938
1939 TYPE curtyp_periods IS REF CURSOR;
1940 cv_periods curtyp_periods;
1941
1942 TYPE curtyp_org IS REF CURSOR;
1943 cv_org curtyp_org;
1944
1945 l_sql_stmt_b VARCHAR2(2000);
1946 l_sql_org_b VARCHAR2(2000);
1947 l_sql_periods_b VARCHAR2(2000);
1948 l_org_id mtl_organizations.organization_id%TYPE ;
1949
1950 l_period_id_to gmf_period_statuses.period_id%TYPE ;
1951 l_brdn_rows NUMBER;
1952 l_brdn_rows_upd NUMBER;
1953 l_brdn_rows_ins NUMBER;
1954
1955 l_organization_id_to cm_cmpt_dtl.organization_id%TYPE;
1956
1957 pi_period_id_to NUMBER;
1958 pi_period_id_from NUMBER;
1959 l_sql_stmt VARCHAR2(2000);
1960 stmtny varchar2(4000);
1961 L_legal_entity_id_from number;
1962 L_legal_entity_id_to number;
1963
1964 l_assigned_flag NUMBER;
1965
1966 BEGIN
1967
1968 if(pi_period_code_to is not null) then
1969 if(pi_organization_id_to is not null) then
1970 SELECT gps.period_id
1971 INTO pi_period_id_to
1972 FROM gmf_period_statuses gps, hr_organization_information org
1973 WHERE gps.PERIOD_CODE = pi_period_code_to
1974 AND gps.CALENDAR_CODE = pi_calendar_code_to
1975 AND gps.legal_entity_id = org.org_information2
1976 AND org.organization_id = pi_organization_id_to
1977 AND org.org_information_context = 'Accounting Information'
1978 AND gps.cost_type_id = pi_cost_type_id_to;
1979 else
1980 SELECT period_id
1981 INTO pi_period_id_to
1982 FROM gmf_period_statuses
1983 WHERE PERIOD_CODE = pi_period_code_to
1984 AND CALENDAR_CODE = pi_calendar_code_to
1985 AND legal_entity_id = pi_all_org_id
1986 AND cost_type_id = pi_cost_type_id_to;
1987 end if;
1988 else
1989 pi_period_id_to := NULL;
1990 end if;
1991
1992 SELECT gps.period_id
1993 INTO pi_period_id_from
1994 FROM gmf_period_statuses gps, hr_organization_information org
1995 WHERE gps.PERIOD_CODE = pi_period_code_from
1996 AND gps.CALENDAR_CODE = pi_calendar_code_from
1997 AND gps.legal_entity_id = org.org_information2
1998 and org.organization_id = pi_organization_id_from
1999 AND org.org_information_context = 'Accounting Information'
2000 AND gps.cost_type_id = pi_cost_type_id_from;
2001
2002 gmf_util.msg_log( 'GMF_CPIC_BUR_START' );
2003 gmf_util.log;
2004
2005 IF ( (pi_period_code_to IS NULL) AND -- all periods
2006 ((pi_all_periods_from IS NOT NULL) OR (pi_all_periods_to IS NOT NULL))
2007 ) THEN
2008 gmf_util.msg_log('GMF_CPBRD_PERIODS_RANGE', nvl(pi_all_periods_from, ' '),
2009 nvl(pi_all_periods_to, ' '), nvl(pi_calendar_code_to, ' ') ) ;
2010 END IF ;
2011
2012 l_sql_stmt_b := '';
2013 l_sql_stmt_b :=
2014 ' SELECT ' ||
2015 ' bur.inventory_item_id, ' ||
2016 ' bur.resources, ' ||
2017 ' bur.cost_cmpntcls_id, ' ||
2018 ' bur.cost_analysis_code, ' ||
2019 ' bur.burden_qty, ' ||
2020 ' bur.burden_usage, ' ||
2021 ' bur.burden_uom, ' ||
2022 ' bur.item_qty, ' ||
2023 ' bur.item_uom, ' ||
2024 ' bur.burden_factor ' ||
2025 ' FROM ' ||
2026 ' cm_brdn_dtl bur ' ||
2030 ' bur.cost_type_id = :b_cost_type_id '; -- AND ' ||
2027 ' WHERE ' ||
2028 ' bur.organization_id = :b_organization_id AND ' ||
2029 ' bur.period_id = :b_period_id AND ' ||
2031 -- ' bur.delete_mark = 0 '; bug 5567156
2032
2033 IF ( pi_range_type = G_ITEM ) THEN
2034 l_sql_stmt_b := l_sql_stmt_b ||
2035 ' AND exists ( '||
2036 ' select 1 from MTL_ITEM_FLEXFIELDS x'||
2037 ' where x.organization_id = bur.organization_id '||
2038 ' and x.item_number between :pi_from_range and :pi_to_range '||
2039 ' and x.inventory_item_id = bur.inventory_item_id )';
2040
2041 ELSIF ( pi_range_type = G_ITEMCC ) THEN
2042
2043 l_sql_stmt_b := l_sql_stmt_b ||
2044 'AND EXISTS (select ''X'' from mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories y, mtl_categories_kfv z
2045 where mdc.functional_area_id = 19
2046 and mdc.category_set_id = mcs.category_set_id
2047 and mcs.category_set_id = y.category_set_id
2048 and mcs.structure_id = z.structure_id
2049 and y.inventory_item_id = bur.inventory_item_id
2050 and y.organization_id = bur.organization_id
2051 and y.category_id = z.category_id
2052 and z.concatenated_segments >= nvl(:b_from_itemcc, z.concatenated_segments)
2053 and z.concatenated_segments <= nvl(:b_to_itemcc, z.concatenated_segments))';
2054
2055 ELSE
2056 gmf_util.msg_log( 'GMF_CPIC_UNKNOWN' );
2057 RETURN;
2058 END IF;
2059
2060 l_sql_stmt_b := l_sql_stmt_b ||
2061 ' ORDER BY ' ||
2062 'bur.organization_id, bur.inventory_item_id, ' ||
2063 'bur.resources, bur.period_id, ' ||
2064 'bur.cost_type_id, bur.cost_cmpntcls_id, bur.cost_analysis_code'
2065 ;
2066
2067 gmf_util.trace( 'Burden Sql Stmt: ' || l_sql_stmt_b, 3 );
2068
2069 IF (pi_organization_id_to IS NOT NULL) THEN -- copying to one organization
2070
2071 l_sql_org_b := '' ;
2072 l_sql_org_b := 'SELECT :pi_organization_id_to FROM dual ' ;
2073
2074 ELSE
2075
2076 -- 'All organizations' option selected
2077 -- Build SQL to get target organizations when from/to org are not null.
2078
2079 l_sql_org_b := '' ;
2080
2081 l_sql_org_b :=
2082 'SELECT ' ||
2083 'hoi.organization_id ' ||
2084 'FROM ' ||
2085 'hr_organization_information hoi , mtl_parameters mp ' ||
2086 ' WHERE ' ||
2087 'hoi.org_information2 = :pi_all_org_id '||
2088 ' AND hoi.org_information_context = ''Accounting Information'' '||
2089 ' AND hoi.organization_id = mp.organization_id '||
2090 ' and mp.process_enabled_flag = ''Y'' ' ;
2091
2092 --
2093 -- We should AVOID copying on to source period and organization. So we should
2094 -- eliminate 'from organization' from the query only when copying to same period,
2095 -- same calendar and to all organizations. For all the other cases no need to check for
2096 -- this condition since from period is getting eliminated from all periods query.
2097 --
2098
2099 IF ( (pi_calendar_code_from = pi_calendar_code_to) AND
2100 (pi_period_id_to IS NOT NULL) AND
2101 (pi_period_id_from = pi_period_id_to)
2102 ) THEN
2103
2104 l_sql_org_b := l_sql_org_b ||' AND hoi.organization_id <> :pi_organization_id_from ';
2105
2106 END IF ;
2107 l_sql_org_b := l_sql_org_b || ' ORDER BY hoi.organization_id ' ;
2108 END IF ;
2109
2110
2111
2112
2113 -- Build SQL to get target periods when From/To Periods are not null.
2114 IF (pi_period_code_to IS NOT NULL) THEN -- copy to one period.
2115 l_sql_periods_b := 'SELECT :pi_period_id_to FROM dual ' ;
2116 ELSE
2117 l_sql_periods_b := '' ;
2118 if(pi_organization_id_to is not null) then
2119 l_sql_periods_b := 'SELECT ' ||
2120 'c3.period_id ' ||
2121 'FROM ' ||
2122 'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1, hr_organization_information d ' ||
2123 'WHERE ' ||
2124 'd.organization_id = :pi_organization_id_to AND '||
2125 'd.org_information_context = ''Accounting Information'' AND '||
2126 'c1.calendar_code = :pi_calendar_code_to AND ' ||
2127 'c1.period_code = :pi_all_periods_from AND ' ||
2128 'c2.calendar_code = :pi_calendar_code_to AND ' ||
2129 'c2.period_code = :pi_all_periods_to AND ' ||
2130 'c3.calendar_code = :pi_calendar_code_to AND ' ||
2131 'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
2132 'c2.cost_type_id = c3.cost_type_id AND ' ||
2136 'c1.legal_entity_id = c2.legal_entity_id AND ' ||
2133 'c1.cost_type_id = c2.cost_type_id AND ' ||
2134 'c3.legal_entity_id = d.org_information2 AND ' ||
2135 'c2.legal_entity_id = c3.legal_entity_id AND ' ||
2137 'c3.start_date >= c1.start_date AND ' ||
2138 'c3.end_date <= c2.end_date AND ' ||
2139 'c3.period_status <> ''C'' ';
2140 else
2141 l_sql_periods_b := 'SELECT ' ||
2142 'c3.period_id ' ||
2143 'FROM ' ||
2144 'gmf_period_statuses c3, gmf_period_statuses c2, gmf_period_statuses c1 ' ||
2145 'WHERE ' ||
2146 'c1.calendar_code = :pi_calendar_code_to AND ' ||
2147 'c1.period_code = :pi_all_periods_from AND ' ||
2148 'c2.calendar_code = :pi_calendar_code_to AND ' ||
2149 'c2.period_code = :pi_all_periods_to AND ' ||
2150 'c3.calendar_code = :pi_calendar_code_to AND ' ||
2151 'c3.cost_type_id = :pi_cost_type_id_to AND ' ||
2152 'c2.cost_type_id = c3.cost_type_id AND ' ||
2153 'c1.cost_type_id = c2.cost_type_id AND ' ||
2154 'c3.legal_entity_id = :pi_all_org_id AND ' ||
2155 'c2.legal_entity_id = c3.legal_entity_id AND ' ||
2156 'c1.legal_entity_id = c2.legal_entity_id AND ' ||
2157 'c3.start_date >= c1.start_date AND ' ||
2158 'c3.end_date <= c2.end_date AND ' ||
2159 'c3.period_status <> ''C'' ';
2160 end if;
2161
2162 IF (pi_calendar_code_from = pi_calendar_code_to) THEN
2163 l_sql_periods_b := l_sql_periods_b||' AND c3.period_id <> :pi_period_id_from ';
2164 END IF ;
2165 l_sql_periods_b := l_sql_periods_b || 'ORDER BY c3.start_date' ;
2166 END IF ; -- To Period code check
2167
2168
2169 gmf_util.trace( 'Organization Query : ' || l_sql_org_b, 3 );
2170 gmf_util.trace( 'Periods Query : ' || l_sql_periods_b, 3 );
2171
2172 -- End Bug# 1419482
2173 /* begin sschinch dt 05/2/03 bug 2934528 Bind variable fix */
2174
2175 IF (pi_period_id_to IS NOT NULL) THEN
2176 OPEN cv_periods FOR l_sql_periods_b
2177 USING pi_period_id_to;
2178 ELSIF (pi_calendar_code_from = pi_calendar_code_to) THEN
2179 if(pi_organization_id_to is not null) then
2180 OPEN cv_periods FOR l_sql_periods_b
2181 USING pi_organization_id_to,
2182 pi_calendar_code_to,
2183 pi_all_periods_from,
2184 pi_calendar_code_to,
2185 pi_all_periods_to,
2186 pi_calendar_code_to,
2187 pi_cost_type_id_to,
2188 pi_period_id_from;
2189 else
2190 OPEN cv_periods FOR l_sql_periods_b
2191 USING pi_calendar_code_to,
2192 pi_all_periods_from,
2193 pi_calendar_code_to,
2194 pi_all_periods_to,
2195 pi_calendar_code_to,
2196 pi_cost_type_id_to,
2197 pi_all_org_id,
2198 pi_period_id_from;
2199
2200 end if;
2201 ELSIF (pi_calendar_code_from <> pi_calendar_code_to) THEN
2202 if(pi_organization_id_to is not null) then
2203 OPEN cv_periods FOR l_sql_periods_b
2204 USING pi_organization_id_to,
2205 pi_calendar_code_to,
2206 pi_all_periods_from,
2207 pi_calendar_code_to,
2208 pi_all_periods_to,
2209 pi_calendar_code_to,
2210 pi_cost_type_id_to;
2211 else
2212 OPEN cv_periods FOR l_sql_periods_b
2213 USING pi_calendar_code_to,
2214 pi_all_periods_from,
2215 pi_calendar_code_to,
2216 pi_all_periods_to,
2217 pi_calendar_code_to,
2218 pi_cost_type_id_to,
2219 pi_all_org_id;
2220
2221 end if;
2222 END IF;
2223
2224 -- Loop through periods using l_sql_periods_b
2225 LOOP
2226 FETCH cv_periods INTO l_period_id_to ;
2227 EXIT WHEN cv_periods%NOTFOUND ;
2228
2229 IF (pi_organization_id_to IS NOT NULL) THEN
2230 OPEN cv_org FOR l_sql_org_b
2231 USING pi_organization_id_to;
2232 ELSIF ((pi_calendar_code_from = pi_calendar_code_to) AND
2233 (pi_period_id_to IS NOT NULL) AND
2234 (pi_period_id_from = pi_period_id_to)) THEN
2235 OPEN cv_org FOR l_sql_org_b
2236 USING pi_all_org_id,
2237 pi_organization_id_from;
2238 ELSE
2239 OPEN cv_org FOR l_sql_org_b
2240 USING pi_all_org_id;
2241
2242 END IF;
2243 LOOP
2244 FETCH cv_org INTO l_organization_id_to ;
2245 EXIT WHEN cv_org%NOTFOUND ;
2246
2247 IF( pi_rem_repl = 1 ) THEN
2248 -- deleting whole range of items
2249 delete_burden_costs(
2250 l_organization_id_to,
2251 l_period_id_to,
2252 pi_cost_type_id_to,
2253 pi_range_type,
2254 pi_from_range, pi_to_range
2255 );
2256 END IF;
2257
2258 gmf_util.log;
2259 gmf_util.msg_log('GMF_CPBRD_ALLWHSEPRD', l_organization_id_to, l_period_id_to ) ;
2260
2261 -- Copy the burden costs
2262 l_brdn_rows := 0;
2263 l_brdn_rows_upd := 0;
2264 l_brdn_rows_ins := 0;
2265 gmf_util.trace('From: Organization-'||pi_organization_id_from||
2266 ' cal-'||pi_calendar_code_from||' prd-'||pi_period_code_from||
2267 ' mthd-'||pi_cost_type_id_from||'itemfrom-'||pi_from_range||
2268 ' item2-'||pi_to_range,0);
2269
2270 OPEN cv_brdn_dtl FOR l_sql_stmt_b USING
2271 pi_organization_id_from,
2272 pi_period_id_from,
2273 pi_cost_type_id_from,
2274 pi_from_range,
2275 pi_to_range;
2276 LOOP
2277 FETCH cv_brdn_dtl INTO r_brdn_dtl;
2278 EXIT WHEN cv_brdn_dtl%NOTFOUND;
2279
2280 gmf_util.trace( 'Item = ' || r_brdn_dtl.inventory_item_id ||
2281 ' Rsrc ' || r_brdn_dtl.resources ||
2282 ' cmpt ' || r_brdn_dtl.cost_cmpntcls_id ||
2283 ' ancd ' || r_brdn_dtl.cost_analysis_code ||
2284 ' bqty ' || r_brdn_dtl.burden_qty ||
2285 ' busg ' || r_brdn_dtl.burden_usage ||
2286 ' buom ' || r_brdn_dtl.burden_um ||
2287 ' iqty ' || r_brdn_dtl.item_qty ||
2288 ' iuom ' || r_brdn_dtl.item_um ||
2289 ' bfct ' || r_brdn_dtl.burden_factor
2290 , 0);
2291
2292
2293 l_brdn_rows := l_brdn_rows + 1;
2294
2295 -- try update first
2296 <<insert_or_update_bur>>
2297 DECLARE
2298 e_insert_row_b EXCEPTION;
2299 e_item_not_assigned EXCEPTION;
2300 BEGIN
2301 l_assigned_flag := verify_item_assigned_to_org(
2302 r_brdn_dtl.inventory_item_id, l_organization_id_to);
2303
2304 gmf_util.trace( 'Verify_item_assigned_to_org: Item ' || r_brdn_dtl.inventory_item_id ||
2305 ' Organization ' || l_organization_id_to ||
2306 ' Status = ' || l_assigned_flag, 3 );
2307 IF(l_assigned_flag = 0) THEN
2308 gmf_util.trace( 'Item ' || r_brdn_dtl.inventory_item_id ||
2309 ' is not assigned to Organization ' || l_organization_id_to, 0 );
2310 RAISE e_item_not_assigned;
2311 END IF;
2312
2313 IF( pi_rem_repl = 1 ) THEN
2314 RAISE e_insert_row_b;
2315 END IF;
2316
2317 UPDATE
2318 cm_brdn_dtl
2319 SET
2320 -- burdenline_id = GEM5_BURDENLINE_ID_S.NEXTVAL,
2321 burden_qty = r_brdn_dtl.burden_qty,
2322 burden_usage = r_brdn_dtl.burden_usage,
2323 burden_uom = r_brdn_dtl.burden_um,
2324 item_qty = r_brdn_dtl.item_qty,
2325 item_uom = r_brdn_dtl.item_um,
2326 burden_factor = r_brdn_dtl.burden_factor,
2327 rollover_ind = 0,
2328 cmpntcost_id = NULL,
2329 trans_cnt = 1,
2330 delete_mark = 0,
2331 text_code = NULL,
2332 last_updated_by = g_user_id,
2333 last_update_login = g_login_id,
2334 last_update_date = SYSDATE,
2335 request_id = g_request_id,
2336 program_application_id = g_prog_appl_id,
2337 program_id = g_program_id,
2338 program_update_date = SYSDATE
2339 WHERE
2340 organization_id = l_organization_id_to AND
2341 inventory_item_id = r_brdn_dtl.inventory_item_id AND
2342 resources = r_brdn_dtl.resources AND
2343 period_id = l_period_id_to AND
2344 cost_type_id = pi_cost_type_id_to AND
2345 cost_cmpntcls_id = r_brdn_dtl.cost_cmpntcls_id AND
2346 cost_analysis_code = r_brdn_dtl.cost_analysis_code;
2347
2348 -- If update fails then try insert
2349 IF( SQL%ROWCOUNT <= 0 ) THEN
2350 RAISE e_insert_row_b;
2351 END IF;
2352
2353 l_brdn_rows_upd := l_brdn_rows_upd + 1;
2354
2355 EXCEPTION
2356 WHEN e_insert_row_b THEN
2357 INSERT INTO
2358 cm_brdn_dtl(
2359 burdenline_id,
2360 organization_id,
2361 inventory_item_id,
2362 resources,
2363 cost_cmpntcls_id,
2364 cost_analysis_code,
2365 burden_qty,
2366 burden_usage,
2367 burden_uom,
2368 item_qty,
2369 item_uom,
2370 burden_factor,
2371 rollover_ind,
2372 cmpntcost_id,
2373 trans_cnt,
2374 delete_mark,
2375 text_code,
2376 created_by,
2377 creation_date,
2378 last_updated_by,
2379 last_update_login,
2380 last_update_date,
2381 request_id,
2382 program_application_id,
2383 program_id,
2384 program_update_date,
2385 period_id,
2386 cost_type_id)
2387 VALUES (
2388 GEM5_BURDENLINE_ID_S.NEXTVAL, -- burdenline_id
2389 l_organization_id_to,
2390 r_brdn_dtl.inventory_item_id,
2391 r_brdn_dtl.resources,
2392 r_brdn_dtl.cost_cmpntcls_id,
2393 r_brdn_dtl.cost_analysis_code,
2394 r_brdn_dtl.burden_qty,
2395 r_brdn_dtl.burden_usage,
2396 r_brdn_dtl.burden_um,
2397 r_brdn_dtl.item_qty,
2398 r_brdn_dtl.item_um,
2399 r_brdn_dtl.burden_factor,
2400 0, -- rollover_ind
2401 NULL, -- cmpntcost_id
2402 1, -- trans_cnt
2403 0, -- delete_mark
2404 NULL, -- text_code
2408 g_login_id, -- last_update_login
2405 g_user_id, -- created_by
2406 SYSDATE, -- creation_date
2407 g_user_id, -- last_updated_by
2409 SYSDATE , -- last_update_date
2410 g_request_id,
2411 g_prog_appl_id, -- program_application_id
2412 g_program_id, -- program_id
2413 SYSDATE, -- program_update_date
2414 l_period_id_to,
2415 pi_cost_type_id_to
2416 );
2417 l_brdn_rows_ins := l_brdn_rows_ins + 1;
2418
2419 WHEN e_item_not_assigned THEN
2420 NULL;
2421 END insert_or_update_bur;
2422 END LOOP;
2423 CLOSE cv_brdn_dtl;
2424
2425 IF( l_brdn_rows > 0 ) THEN
2426 gmf_util.msg_log( 'GMF_CP_ROWS_SELECTED', TO_CHAR(l_brdn_rows) );
2427 gmf_util.msg_log( 'GMF_CP_ROWS_UPDINS', TO_CHAR(l_brdn_rows_upd), TO_CHAR(l_brdn_rows_ins) );
2428 ELSE
2429 gmf_util.msg_log( 'GMF_CP_NO_ROWS' );
2430 END IF;
2431
2432 END LOOP ; -- organization loop
2433 CLOSE cv_org;
2434 END LOOP ; -- periods loop
2435 CLOSE cv_periods;
2436
2437 gmf_util.log;
2438 gmf_util.msg_log( 'GMF_CPIC_BUR_END' );
2439
2440 END copy_burden_dtl;
2441
2442 /*****************************************************************************
2443 * PROCEDURE
2444 * delete_burden_costs
2445 *
2446 * DESCRIPTION
2447 * Deletes the burden costs for the parameters passed
2448 *
2449 * INPUT PARAMETERS
2450 * organization_id, calendar, period, cost_mthd, item or itemcost_class range
2451 *
2452 * HISTORY
2453 * 13-Oct-1999 Rajesh Seshadri
2454 *
2455 ******************************************************************************/
2456
2457 PROCEDURE delete_burden_costs(
2458 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE,
2459 pi_period_id IN cm_cmpt_dtl.period_id%TYPE,
2460 pi_cost_type_id IN cm_cmpt_dtl.cost_type_id%TYPE,
2461 pi_range_type IN NUMBER,
2462 pi_from_range IN VARCHAR2,
2463 pi_to_range IN VARCHAR2
2464 )
2465 IS
2466 l_del_stmt_b VARCHAR2(2000);
2467 l_sub_qry_b VARCHAR2(2000);
2468
2469 BEGIN
2470 fnd_file.put_line(fnd_file.log,'In delete_burden_costs');
2471 l_del_stmt_b := '';
2472 l_sub_qry_b := '';
2473
2474 l_del_stmt_b :=
2475 ' DELETE FROM ' ||
2476 ' cm_brdn_dtl bur ' ||
2477 ' WHERE ' ||
2478 ' bur.organization_id = :b_organization_id AND ' ||
2479 ' bur.period_id = :b_period_id AND ' ||
2480 ' bur.cost_type_id = :b_cost_type_id AND ' ||
2481 ' bur.inventory_item_id IN ( '
2482 ;
2483
2484 l_sub_qry_b :=
2485 ' SELECT ' ||
2486 ' itm.inventory_item_id ' ||
2487 ' FROM ' ||
2488 ' mtl_system_items_b_kfv itm ' ||
2489 ' WHERE ' ||
2490 ' 1 = 1';
2491 IF ( pi_range_type = G_ITEM ) THEN
2492 l_sub_qry_b := l_sub_qry_b ||
2493 ' AND itm.concatenated_segments >= nvl(:b_from_item,itm.concatenated_segments) ' ||
2494 ' AND itm.concatenated_segments <= nvl(:b_to_item,itm.concatenated_segments) ' ;
2495
2496 ELSIF ( pi_range_type = G_ITEMCC ) THEN
2497 l_sub_qry_b := l_sub_qry_b ||
2498 ' AND EXISTS (select ''X'' from mtl_default_category_sets mdc, mtl_category_sets mcs, mtl_item_categories y, mtl_categories_kfv z
2499 where mdc.functional_area_id = 19
2500 and mdc.category_set_id = mcs.category_set_id
2501 and mcs.category_set_id = y.category_set_id
2502 and mcs.structure_id = z.structure_id
2503 and y.inventory_item_id = itm.inventory_item_id
2504 and y.organization_id = itm.organization_id
2505 and y.category_id = z.category_id
2506 and z.concatenated_segments >= nvl(:b_from_itemcc, z.concatenated_segments)
2507 and z.concatenated_segments <= nvl(:b_to_itemcc, z.concatenated_segments))';
2508
2509 ELSE
2510 gmf_util.msg_log( 'GMF_CPIC_UNKNOWN' );
2511 RETURN;
2512 END IF;
2513
2514 gmf_util.trace( 'Burden del sub-qry: ' || l_sub_qry_b, 3 );
2515
2516 l_del_stmt_b := l_del_stmt_b || l_sub_qry_b || ' ) ' ;
2517
2518 gmf_util.trace( ' Burden Del Stmt: ' || l_del_stmt_b, 3 );
2519
2520 BEGIN
2521 EXECUTE IMMEDIATE l_del_stmt_b USING
2522 pi_organization_id,
2523 pi_period_Id, pi_cost_type_id,
2524 pi_from_range, pi_to_range;
2525 EXCEPTION
2526 WHEN OTHERS THEN
2527 fnd_file.put_line(fnd_file.log,'THE ERROR IS :'||SQLERRM);
2528 END;
2529 gmf_util.trace( SQL%ROWCOUNT || ' Rows deleted from Burden Details', 0);
2530
2531 END delete_burden_costs;
2532
2533 /*****************************************************************************
2534 * FUNCTION
2535 * verify_item_assigned_to_org
2536 *
2537 * DESCRIPTION
2538 * Verifies if the item is assigned to the org or not
2539 *
2540 * INPUT PARAMETERS
2541 * organization_id, item_id
2542 *
2543 * HISTORY
2544 * 11 April 2006 Jahnavi Boppana
2545 *
2546 ******************************************************************************/
2547
2548 FUNCTION verify_item_assigned_to_org(
2549 pi_inventory_item_id IN cm_cmpt_dtl.inventory_item_id%TYPE,
2550 pi_organization_id IN cm_cmpt_dtl.organization_id%TYPE
2551 )
2552 RETURN NUMBER IS
2553 l_assigned_ind NUMBER;
2554 BEGIN
2555 gmf_util.trace( 'Entering verify_item_assigned_to_org', 0 );
2556
2557 l_assigned_ind := 0;
2558 SELECT count(1) INTO l_assigned_ind
2559 FROM
2560 mtl_system_items_b
2561 WHERE
2562 organization_id = pi_organization_id AND
2563 inventory_item_id = pi_inventory_item_id
2564 ;
2565 RETURN l_assigned_ind;
2566
2567 END verify_item_assigned_to_org;
2568
2569
2570 END GMF_COPY_ITEM_COST ;