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