[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_COPY_PKG
Source
1 PACKAGE BODY IGC_CC_COPY_PKG AS
2 /* $Header: IGCCCPCB.pls 120.5.12000000.3 2007/10/18 15:20:43 vumaasha ship $*/
3
4 -- ------------------------------------------------------------------------------
5 -- Define globals to be used within this procedure itself.
6 -- ------------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_COPY_PKG';
8 g_update_login igc_cbc_je_lines.last_update_login%TYPE;
9 g_update_by igc_cbc_je_lines.last_updated_by%TYPE;
10 g_prod VARCHAR2(3) := 'IGC';
11 g_sub_comp VARCHAR2(7) := 'CC_COPY';
12 g_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
13 g_debug VARCHAR2(10000);
14 g_new_cc_header_id NUMBER;
15
16 -- g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
17 g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
18 --following variables added for bug 3199488: fnd logging changes: sdixit
19 g_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
20 g_state_level number := FND_LOG.LEVEL_STATEMENT;
21 g_proc_level number := FND_LOG.LEVEL_PROCEDURE;
22 g_event_level number := FND_LOG.LEVEL_EVENT;
23 g_excep_level number := FND_LOG.LEVEL_EXCEPTION;
24 g_error_level number := FND_LOG.LEVEL_ERROR;
25 g_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
26 g_path varchar2(500) := 'igc.plsql.igcccpcb.igc_cc_copy_pkg.';
27
28
29 -- ------------------------------------------------------------------------------
30 -- Declare the procedures that are within this package.
31 -- ------------------------------------------------------------------------------
32 PROCEDURE Acct_Line_Copy (
33 p_cc_acct_line IN igc_cc_acct_lines%ROWTYPE,
34 p_conversion_rate IN igc_cc_headers.conversion_rate%TYPE,
35 x_cc_acct_line_id OUT NOCOPY igc_cc_acct_lines.cc_acct_line_id%TYPE,
36 x_return_status OUT NOCOPY VARCHAR2
37 );
38
39 PROCEDURE Det_Pf_Line_Copy (
40 p_cc_pmt_fcst IN igc_cc_det_pf%ROWTYPE,
41 p_cc_acct_line_id IN igc_cc_acct_lines.cc_acct_line_id%TYPE,
42 p_conversion_rate IN igc_cc_headers.conversion_rate%TYPE,
43 x_return_status OUT NOCOPY VARCHAR2
44 );
45
46 PROCEDURE Access_Copy (
47 p_access_lines IN igc_cc_access%ROWTYPE,
48 x_return_status OUT NOCOPY VARCHAR2
49 );
50
51 PROCEDURE Put_Debug_Msg (
52 p_path IN VARCHAR2,
53 p_debug_msg IN VARCHAR2,
54 p_sev_level IN VARCHAR2 := g_state_level
55 );
56
57
58 /* ================================================================================
59 PROCEDURE Acct_Line_Copy
60 ===============================================================================*/
61
62 PROCEDURE Acct_Line_Copy (
63 p_cc_acct_line IN igc_cc_acct_lines%ROWTYPE,
64 p_conversion_rate IN igc_cc_headers.conversion_rate%TYPE,
65 x_cc_acct_line_id OUT NOCOPY igc_cc_acct_lines.cc_acct_line_id%TYPE,
66 x_return_status OUT NOCOPY VARCHAR2
67 ) IS
68
69 l_cc_acct_line_id igc_cc_acct_lines.cc_acct_line_id%TYPE;
70
71 l_return_status VARCHAR2(1);
72 l_msg_count NUMBER;
73 l_msg_data VARCHAR2(2000);
74 l_Rowid VARCHAR2(18);
75 l_flag VARCHAR2(1);
76 l_functional_amount igc_cc_acct_lines.cc_acct_func_amt%TYPE;
77 -- bug 2043221 ssmales - added variable declaration on l_func_withheld_amt
78 l_func_withheld_amt igc_cc_acct_lines.cc_func_withheld_amt%TYPE;
79 l_seq_num NUMBER;
80
81 l_full_path VARCHAR2(500) := g_path||'Acct_Line_Copy';--bug 3199488
82
83 BEGIN
84
85 x_return_status := FND_API.G_RET_STS_SUCCESS;
86 IF (g_debug_mode = 'Y') THEN
87 Put_Debug_Msg (l_full_path,' IGCCCPCB - Starting to Insert Account Line.');
88 END IF;
89
90
91
92 -- ---------------------------------------------------------------------
93 -- Make sure that the functional amount is correctly calculated.
94 -- ---------------------------------------------------------------------
95 IF (p_conversion_rate IS NULL) THEN
96 l_functional_amount := p_cc_acct_line.CC_Acct_Entered_Amt;
97 -- bug 2043221 ssmales - added line below
98 l_func_withheld_amt := p_cc_acct_line.CC_Ent_Withheld_Amt;
99 ELSE
100 l_functional_amount := p_cc_acct_line.CC_Acct_Entered_Amt * p_conversion_rate;
101 -- bug 2043221 ssmales - added line below
102 l_func_withheld_amt := p_cc_acct_line.CC_Ent_Withheld_Amt * p_conversion_rate;
103 END IF;
104
105 IF (g_debug_mode = 'Y') THEN
106 Put_Debug_Msg (l_full_path,' IGCCCPCB - Both Acct Line Entered Amounts are : ' || p_cc_acct_line.CC_Acct_Entered_Amt);
107 Put_Debug_Msg (l_full_path,' IGCCCPCB - OLD Acct Line Functional Amount is : ' || p_cc_acct_line.CC_Acct_Func_Amt);
108 Put_Debug_Msg (l_full_path,' IGCCCPCB - New Copied Acct Line Functional Amount is : ' || l_functional_amount);
109
110
111 -- bug 2043221 ssmales - start block
112 Put_Debug_Msg (l_full_path,' IGCCCPCB - Acct Line Withheld Entered Amount is : ' || p_cc_acct_line.CC_Ent_Withheld_Amt);
113 Put_Debug_Msg (l_full_path,' IGCCCPCB - OLD Acct Line Func Withheld Amount is : ' || p_cc_acct_line.CC_Func_Withheld_Amt);
114 Put_Debug_Msg (l_full_path,' IGCCCPCB - New Copied Acct Line Functional Amount is : ' || l_func_withheld_amt);
115 -- bug 2043221 ssmales - end block
116 END IF;
117
118 IGC_CC_ACCT_LINES_PKG.Insert_Row
119 (p_api_version => 1.0,
120 p_init_msg_list => FND_API.G_FALSE,
121 p_commit => FND_API.G_FALSE,
122 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
123 X_return_status => l_return_status,
124 X_msg_count => l_msg_count,
125 X_msg_data => l_msg_data,
126 p_Rowid => l_rowid,
127 p_CC_Acct_Line_Id => l_cc_acct_line_id,
128 p_CC_Header_Id => g_new_cc_header_id,
129 p_Parent_Header_Id => NULL,
130 p_Parent_Acct_Line_Id => NULL,
131 p_CC_Charge_Code_Comb_Id => p_cc_acct_line.CC_Charge_Code_Combination_Id,
132 p_CC_Acct_Line_Num => p_cc_acct_line.CC_Acct_Line_Num,
133 p_CC_Budget_Code_Comb_Id => p_cc_acct_line.CC_Budget_Code_Combination_Id,
134 p_CC_Acct_Entered_Amt => p_cc_acct_line.CC_Acct_Entered_Amt,
135 p_CC_Acct_Func_Amt => l_functional_amount,
136 p_CC_Acct_Desc => p_cc_acct_line.CC_Acct_Desc,
137 p_CC_Acct_Billed_Amt => NULL,
138 p_CC_Acct_Unbilled_Amt => NULL,
139 p_CC_Acct_Taxable_Flag => p_cc_acct_line.CC_Acct_Taxable_Flag,
140 p_Tax_Id => p_cc_acct_line.Tax_Id,
141 p_CC_Acct_Encmbrnc_Amt => NULL,
142 p_CC_Acct_Encmbrnc_Date => NULL,
143 p_CC_Acct_Encmbrnc_Status => NULL,
144 p_Project_Id => p_cc_acct_line.Project_Id,
145 p_Task_Id => p_cc_acct_line.Task_Id,
146 p_Expenditure_Type => p_cc_acct_line.Expenditure_Type,
147 p_Expenditure_Org_Id => p_cc_acct_line.Expenditure_Org_Id,
148 p_Expenditure_Item_Date => p_cc_acct_line.Expenditure_Item_Date,
149 p_Last_Update_Date => SYSDATE,
150 p_Last_Updated_By => g_update_by,
151 p_Last_Update_Login => g_update_login,
152 p_Creation_Date => SYSDATE,
153 p_Created_By => g_update_by,
154 p_Attribute1 => p_cc_acct_line.Attribute1,
155 p_Attribute2 => p_cc_acct_line.Attribute2,
156 p_Attribute3 => p_cc_acct_line.Attribute3,
157 p_Attribute4 => p_cc_acct_line.Attribute4,
158 p_Attribute5 => p_cc_acct_line.Attribute5,
159 p_Attribute6 => p_cc_acct_line.Attribute6,
160 p_Attribute7 => p_cc_acct_line.Attribute7,
161 p_Attribute8 => p_cc_acct_line.Attribute8,
162 p_Attribute9 => p_cc_acct_line.Attribute9,
163 p_Attribute10 => p_cc_acct_line.Attribute10,
164 p_Attribute11 => p_cc_acct_line.Attribute11,
165 p_Attribute12 => p_cc_acct_line.Attribute12,
166 p_Attribute13 => p_cc_acct_line.Attribute13,
167 p_Attribute14 => p_cc_acct_line.Attribute14,
168 p_Attribute15 => p_cc_acct_line.Attribute15,
169 p_Context => p_cc_acct_line.Context,
170 --bug 2043221 ssmales - 2 arguments below added
171 p_CC_Func_Withheld_Amt => l_func_withheld_amt,
172 p_CC_Ent_Withheld_Amt => p_cc_acct_line.CC_Ent_Withheld_Amt,
173 G_FLAG => l_flag,
174 P_Tax_Classif_Code => p_cc_acct_line.Tax_Classif_Code
175 );
176
177 -- --------------------------------------------------------------
178 -- At this point l_cc_acct_line_id has been assigned a sequence
179 -- number by the table handler.
180 -- --------------------------------------------------------------
181 x_cc_acct_line_id := l_cc_acct_line_id;
182
183
184 -- ---------------------------------------------------------------
185 -- Check to make sure that the Access Copy was actually done.
186 -- ---------------------------------------------------------------
187 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
188 IF (g_debug_mode = 'Y') THEN
189 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure Inserting new Account Line ID......');
190 END IF;
191 RAISE FND_API.G_EXC_ERROR ;
192 END IF;
193
194
195 IF (g_debug_mode = 'Y') THEN
196 Put_Debug_Msg (l_full_path,' IGCCCPCB - Acct Line has successfully been added......');
197 END IF;
198
199 RETURN;
200
201 -- --------------------------------------
202 -- Exception Handlers Section
203 -- --------------------------------------
204 EXCEPTION
205 WHEN FND_API.G_EXC_ERROR THEN
206 x_return_status := FND_API.G_RET_STS_ERROR;
207 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
208 p_data => l_msg_data );
209
210 IF (g_debug_mode = 'Y') THEN
211 Put_Debug_Msg (l_full_path,' IGCCCPCB - EXC ERROR Failure for Acct Line ID Copy.');
212 END IF;
213 RETURN;
214
215 WHEN OTHERS THEN
216 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
217 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
218 p_data => l_msg_data );
219 IF g_unexp_level >= g_debug_level THEN
220 fnd_message.set_name('IGC','IGC_LOGGING_UNEXP_ERROR');
221 fnd_message.set_token('CODE',SQLCODE);
222 fnd_message.set_token('MESG',SQLERRM);
223 fnd_log.message(g_unexp_level,l_full_path,TRUE);
224 END IF;
225 RETURN;
226
227 END Acct_Line_Copy;
228
229 /* ================================================================================
230 PROCEDURE Det_Pf_Line_Copy
231 ===============================================================================*/
232
233 PROCEDURE Det_Pf_Line_Copy (
234 p_cc_pmt_fcst IN igc_cc_det_pf%ROWTYPE,
235 p_cc_acct_line_id IN igc_cc_acct_lines.cc_acct_line_id%TYPE,
236 p_conversion_rate IN igc_cc_headers.conversion_rate%TYPE,
237 x_return_status OUT NOCOPY VARCHAR2
238 ) IS
239
240 l_cc_pmt_fcst_id igc_cc_det_pf.cc_det_pf_line_id%TYPE;
241 l_return_status VARCHAR2(1);
242 l_msg_count NUMBER;
243 l_msg_data VARCHAR2(2000);
244 l_Rowid VARCHAR2(18);
245 l_flag VARCHAR2(1);
246 l_functional_amount igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
247
248 l_full_path VARCHAR2(500) := g_path||'Det_Pf_Line_Copy';--bug 3199488
249 BEGIN
250
251 x_return_status := FND_API.G_RET_STS_SUCCESS;
252 IF (g_debug_mode = 'Y') THEN
253 Put_Debug_Msg (l_full_path,' IGCCCPCB - Starting Det PF Line Copy......');
254
255
256 Put_Debug_Msg (l_full_path,' IGCCCPCB - Conversion rate is : ' || p_conversion_rate);
257 END IF;
258
259 -- ---------------------------------------------------------------------
260 -- Make sure that the functional amount is correctly calculated.
261 -- ---------------------------------------------------------------------
262 IF (p_conversion_rate IS NULL) THEN
263 l_functional_amount := p_cc_pmt_fcst.cc_det_pf_entered_amt;
264 ELSE
265 l_functional_amount := p_cc_pmt_fcst.cc_det_pf_entered_amt * p_conversion_rate;
266 END IF;
267
268 IF (g_debug_mode = 'Y') THEN
269 Put_Debug_Msg (l_full_path,' IGCCCPCB - Both Det Pf Entered Amounts are : ' || p_cc_pmt_fcst.CC_Det_PF_Entered_Amt);
270 Put_Debug_Msg (l_full_path,' IGCCCPCB - OLD Det PF Functional Amount is : ' || p_cc_pmt_fcst.cc_det_pf_func_amt);
271 Put_Debug_Msg (l_full_path,' IGCCCPCB - New Copied Det PF Functional Amount is : ' || l_functional_amount);
272 END IF;
273
274 IGC_CC_DET_PF_PKG.Insert_Row
275 (p_api_version => 1.0,
276 p_init_msg_list => FND_API.G_FALSE,
277 p_commit => FND_API.G_FALSE,
278 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
279 X_return_status => l_return_status,
280 X_msg_count => l_msg_count,
281 X_msg_data => l_msg_data,
282 p_Rowid => l_rowid,
283 p_CC_Det_PF_Line_Id => l_cc_pmt_fcst_id,
284 p_CC_Det_PF_Line_Num => p_cc_pmt_fcst.CC_Det_PF_Line_Num,
285 p_CC_Acct_Line_Id => p_cc_acct_line_id,
286 p_Parent_Acct_Line_Id => NULL,
287 p_Parent_Det_PF_Line_Id => NULL,
288 p_CC_Det_PF_Entered_Amt => p_cc_pmt_fcst.CC_Det_PF_Entered_Amt,
289 p_CC_Det_PF_Func_Amt => l_functional_amount,
290 p_CC_Det_PF_Date => p_cc_pmt_fcst.CC_Det_PF_Date,
291 p_CC_Det_PF_Billed_Amt => NULL,
292 p_CC_Det_PF_Unbilled_Amt => NULL,
293 p_CC_Det_PF_Encmbrnc_Amt => NULL,
294 p_CC_Det_PF_Encmbrnc_Date => NULL,
295 p_CC_Det_PF_Encmbrnc_Status => NULL,
296 p_Last_Update_Date => SYSDATE,
297 p_Last_Updated_By => g_update_by,
298 p_Last_Update_Login => g_update_login,
299 p_Creation_Date => SYSDATE,
300 p_Created_By => g_update_by,
301 p_Attribute1 => p_cc_pmt_fcst.Attribute1,
302 p_Attribute2 => p_cc_pmt_fcst.Attribute2,
303 p_Attribute3 => p_cc_pmt_fcst.Attribute3,
304 p_Attribute4 => p_cc_pmt_fcst.Attribute4,
305 p_Attribute5 => p_cc_pmt_fcst.Attribute5,
306 p_Attribute6 => p_cc_pmt_fcst.Attribute6,
307 p_Attribute7 => p_cc_pmt_fcst.Attribute7,
308 p_Attribute8 => p_cc_pmt_fcst.Attribute8,
309 p_Attribute9 => p_cc_pmt_fcst.Attribute9,
310 p_Attribute10 => p_cc_pmt_fcst.Attribute10,
311 p_Attribute11 => p_cc_pmt_fcst.Attribute11,
312 p_Attribute12 => p_cc_pmt_fcst.Attribute12,
313 p_Attribute13 => p_cc_pmt_fcst.Attribute13,
314 p_Attribute14 => p_cc_pmt_fcst.Attribute14,
315 p_Attribute15 => p_cc_pmt_fcst.Attribute15,
316 p_Context => p_cc_pmt_fcst.Context,
317 G_FLAG => l_flag
318 );
319
320 -- ---------------------------------------------------------------
321 -- Check to make sure that the Det PF Line was actually done.
322 -- ---------------------------------------------------------------
323 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
324
325 IF (g_debug_mode = 'Y') THEN
326 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure Inserting Det PF Line ID on Copy.......');
327 END IF;
328 RAISE FND_API.G_EXC_ERROR ;
329 END IF;
330
331
332 IF (g_debug_mode = 'Y') THEN
333 Put_Debug_Msg (l_full_path,' IGCCCPCB - Successfully inserted Det PF Line ID.' || l_cc_pmt_fcst_id);
334 END IF;
335
336 RETURN;
337
338 -- --------------------------------------
339 -- Exception Handlers Section
340 -- --------------------------------------
341 EXCEPTION
342 WHEN FND_API.G_EXC_ERROR THEN
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
345 p_data => l_msg_data );
346 IF (g_debug_mode = 'Y') THEN
347 Put_Debug_Msg (l_full_path,' IGCCCPCB - EXC ERROR Copying new Det PF Line ID.');
348 END IF;
349
350 RETURN;
351 WHEN OTHERS THEN
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count, p_data => l_msg_data );
354 IF g_unexp_level >= g_debug_level THEN
355 fnd_message.set_name('IGC','IGC_LOGGING_UNEXP_ERROR');
356 fnd_message.set_token('CODE',SQLCODE);
357 fnd_message.set_token('MESG',SQLERRM);
358 fnd_log.message(g_unexp_level,l_full_path,TRUE);
359 END IF;
360 RETURN;
361
362 END Det_Pf_Line_Copy;
363
364
365 /* ================================================================================
366 PROCEDURE Access_Copy
367 ===============================================================================*/
368
369 PROCEDURE Access_Copy (
370 p_access_lines IN igc_cc_access%ROWTYPE,
371 x_return_status OUT NOCOPY VARCHAR2
372 ) IS
373
374 l_cc_access_id igc_cc_access.cc_access_id%TYPE;
375 l_return_status VARCHAR2(1);
376 l_msg_count NUMBER;
377 l_msg_data VARCHAR2(2000);
378 l_Rowid VARCHAR2(18);
379 l_flag VARCHAR2(1);
380
381 l_full_path VARCHAR2(500) := g_path||'Access_Copy';--bug 3199488
382 BEGIN
383
384 x_return_status := FND_API.G_RET_STS_SUCCESS;
385 IF (g_debug_mode = 'Y') THEN
386 Put_Debug_Msg (l_full_path,' IGCCCPCB - Starting the Access Copy routine..........');
387 END IF;
388
389
390 IGC_CC_ACCESS_PKG.Insert_Row
391 (p_api_version => 1.0,
392 p_init_msg_list => FND_API.G_FALSE,
393 p_commit => FND_API.G_FALSE,
394 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
395 x_return_status => l_return_status,
396 x_msg_count => l_msg_count,
397 x_msg_data => l_msg_data,
398 p_row_id => l_rowid,
399 p_CC_HEADER_ID => g_new_cc_header_id,
400 p_USER_ID => p_access_lines.user_id,
401 p_CC_GROUP_ID => p_access_lines.cc_group_id,
402 p_CC_ACCESS_ID => l_cc_access_id,
403 p_CC_ACCESS_LEVEL => p_access_lines.cc_access_level,
404 p_CC_ACCESS_TYPE => p_access_lines.cc_access_type,
405 p_LAST_UPDATE_DATE => SYSDATE,
406 p_LAST_UPDATED_BY => g_update_by,
407 p_CREATION_DATE => SYSDATE,
408 p_CREATED_BY => g_update_by,
409 p_LAST_UPDATE_LOGIN => g_update_login
410 );
411
412 -- ---------------------------------------------------------------
413 -- Check to make sure that the Access Copy was actually done.
414 -- ---------------------------------------------------------------
415 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
416 RAISE FND_API.G_EXC_ERROR ;
417 END IF;
418
419
420 IF (g_debug_mode = 'Y') THEN
421 Put_Debug_Msg (l_full_path,' IGCCCPCB - Successfully Added new access ID : ' || l_cc_access_id);
422 END IF;
423
424 RETURN;
425
426 -- --------------------------------------
427 -- Exception Handlers Section
428 -- --------------------------------------
429 EXCEPTION
430 WHEN FND_API.G_EXC_ERROR THEN
431 x_return_status := FND_API.G_RET_STS_ERROR;
432 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
433 p_data => l_msg_data );
434 IF (g_debug_mode = 'Y') THEN
435 Put_Debug_Msg (l_full_path,' IGCCCPCB - EXC ERROR in inserting new Access ID........');
436 END IF;
437 RETURN;
438
439 WHEN OTHERS THEN
440 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
441 FND_MSG_PUB.Count_And_Get (p_count => l_msg_count,
442 p_data => l_msg_data );
443 IF g_unexp_level >= g_debug_level THEN
444 fnd_message.set_name('IGC','IGC_LOGGING_UNEXP_ERROR');
445 fnd_message.set_token('CODE',SQLCODE);
446 fnd_message.set_token('MESG',SQLERRM);
447 fnd_log.message(g_unexp_level,l_full_path,TRUE);
448 END IF;
449 RETURN;
450
451 END Access_Copy;
452
453 /* ================================================================================
454 PROCEDURE Header_Copy
455 =============================================================================== */
456
457 PROCEDURE Header_Copy (
458 p_api_version IN NUMBER,
459 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
460 p_commit IN VARCHAR2 := FND_API.G_FALSE,
461 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_msg_count OUT NOCOPY NUMBER,
464 x_msg_data OUT NOCOPY VARCHAR2,
465 p_old_cc_header_id IN igc_cc_headers.cc_header_id%TYPE,
466 p_new_cc_header_id IN igc_cc_headers.cc_header_id%TYPE,
467 p_cc_num IN igc_cc_headers.cc_num%TYPE,
468 p_cc_type IN igc_cc_headers.cc_type%TYPE
469 ) IS
470
471 l_api_name CONSTANT VARCHAR2(30) := 'Header_Copy';
472 l_api_version CONSTANT NUMBER := 1.0;
473 l_cc_headers_rec igc_cc_headers%ROWTYPE;
474 l_cc_acct_lines_rec igc_cc_acct_lines%ROWTYPE;
475 l_cc_pmt_fcst_rec igc_cc_det_pf%ROWTYPE;
476 l_cc_access_lines_rec igc_cc_access%ROWTYPE;
477 l_cc_acct_line_id igc_cc_acct_lines.cc_acct_line_id%TYPE;
478 l_return_status VARCHAR2(1);
479 l_msg_count NUMBER;
480 l_msg_data VARCHAR2(2000);
481 l_Rowid VARCHAR2(18);
482 l_action_rowid VARCHAR2(18);
483 l_flag VARCHAR2(1);
484 l_debug VARCHAR2(1);
485 l_cc_num_val igc_cc_headers.cc_num%TYPE;
486 l_cc_type_val igc_cc_headers.cc_type%TYPE;
487 l_CC_State igc_cc_headers.cc_state%TYPE;
488 l_CC_ctrl_status igc_cc_headers.cc_ctrl_status%TYPE;
489 l_CC_Encmbrnc_Status igc_cc_headers.cc_encmbrnc_status%TYPE;
490 l_CC_Apprvl_Status igc_cc_headers.cc_apprvl_status%TYPE;
491
492 l_full_path VARCHAR2(500) := g_path||'Header_Copy';--bug 3199488
493
494 CURSOR c_account_lines (t_cc_header_id NUMBER) IS
495 SELECT *
496 FROM igc_cc_acct_lines ccac
497 WHERE ccac.cc_header_id = t_cc_header_id;
498
499 /* Contract Detail Payment Forecast lines */
500
501 CURSOR c_payment_forecast (t_cc_acct_line_id NUMBER) IS
502 SELECT *
503 FROM igc_cc_det_pf
504 WHERE cc_acct_line_id = t_cc_acct_line_id;
505
506 /* Contract Access Lines */
507
508 CURSOR c_access_lines (t_cc_header_id NUMBER) IS
509 SELECT *
510 FROM igc_cc_access
511 WHERE cc_header_id = t_cc_header_id;
512
513 CURSOR c_obtain_cc_header (t_cc_header_id NUMBER) IS
514 SELECT *
515 FROM igc_cc_headers
516 WHERE cc_header_id = t_cc_header_id;
517
518 BEGIN
519
520 SAVEPOINT Header_Copy_Pvt ;
521
522 -- ---------------------------------------------------------------------------------------
523 -- Ensure that the arguments passed in match the appropriate version of this
524 -- procedure.
525 -- ---------------------------------------------------------------------------------------
526 IF NOT FND_API.Compatible_API_Call ( l_api_version,
527 p_api_version,
528 l_api_name,
529 G_PKG_NAME )
530 THEN
531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
532 END IF;
533
534 IF FND_API.to_Boolean (p_init_msg_list ) THEN
535 FND_MSG_PUB.initialize ;
536 END IF;
537
538 X_return_status := FND_API.G_RET_STS_SUCCESS ;
539
540 -- --------------------------------------------------------------------------------------
541 -- Initialize Global variables that are used by procedure.
542 -- --------------------------------------------------------------------------------------
543 g_update_login := FND_GLOBAL.LOGIN_ID;
544 g_update_by := FND_GLOBAL.USER_ID;
545 g_new_cc_header_id := p_new_cc_header_id;
546 l_cc_num_val := p_cc_num;
547 l_cc_type_val := p_cc_type;
548 l_CC_State := 'PR';
549 l_CC_ctrl_status := 'E';
550 l_CC_Encmbrnc_Status := 'N';
551 l_CC_Apprvl_Status := 'IN';
552
553 -- --------------------------------------------------------------------------------------
554 -- Determine if debug has been enabled for this process.
555 -- --------------------------------------------------------------------------------------
556 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
557
558 -- IF (l_debug = 'Y') THEN
559 -- l_debug := FND_API.G_TRUE;
560 -- ELSE
561 -- l_debug := FND_API.G_FALSE;
562 -- END IF;
563
564 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN (l_debug);
565 IF (g_debug_mode = 'Y') THEN
566 Put_Debug_Msg (l_full_path,' IGCCCPCB - Starting Header Copy for New Header : ' || p_new_cc_header_id ||
567 ' from Old Header ID : ' || p_old_cc_header_id);
568 END IF;
569
570 -- -------------------------------------------------------------------------------------
571 -- Obtain the CC Header record that is to be copied
572 -- -------------------------------------------------------------------------------------
573 OPEN c_obtain_cc_header (p_old_cc_header_id);
574 FETCH c_obtain_cc_header
575 INTO l_cc_headers_rec;
576
577 IF (c_obtain_cc_header%NOTFOUND) THEN
578 IGC_MSGS_PKG.message_token (p_tokname => 'CC_NUM',
579 p_tokval => p_cc_num);
580 IGC_MSGS_PKG.message_token (p_tokname => 'CC_HEADER_ID',
581 p_tokval => p_old_cc_header_id);
582 IGC_MSGS_PKG.add_message (p_appname => 'IGC',
583 p_msgname => 'IGC_HEADER_ID_NOT_FOUND');
584 RAISE FND_API.G_EXC_ERROR;
585 END IF;
586
587 CLOSE c_obtain_cc_header;
588
589 -- ------------------------------------------------------------------------------------
590 -- Call table handler to insert appropriate CC Header record into table as well as
591 -- the appropriate MRC records.
592 -- ------------------------------------------------------------------------------------
593 IGC_CC_HEADERS_PKG.Insert_Row
594 (p_api_version => 1.0,
595 p_init_msg_list => FND_API.G_FALSE,
596 p_commit => FND_API.G_FALSE,
597 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
598 X_return_status => l_return_status,
599 X_msg_count => l_msg_count,
600 X_msg_data => l_msg_data,
601 p_Rowid => l_rowid,
602 p_CC_Header_Id => p_new_CC_Header_Id,
603 p_Org_id => l_cc_headers_rec.org_id,
604 p_CC_Type => l_cc_type_val,
605 p_CC_Num => l_CC_Num_val,
606 p_CC_Version_num => 0,
607 p_Parent_Header_Id => NULL,
608 p_CC_State => l_cc_state,
609 p_CC_ctrl_status => l_cc_ctrl_status,
610 p_CC_Encmbrnc_Status => l_cc_encmbrnc_status,
611 p_CC_Apprvl_Status => l_cc_apprvl_status,
612 p_Vendor_Id => l_cc_headers_rec.vendor_id,
613 p_Vendor_Site_Id => l_cc_headers_rec.vendor_site_id,
614 p_Vendor_Contact_Id => l_cc_headers_rec.vendor_contact_id,
615 p_Term_Id => l_cc_headers_rec.term_id,
616 p_Location_Id => l_cc_headers_rec.location_id,
617 p_Set_Of_Books_Id => l_cc_headers_rec.set_of_books_id,
618 p_CC_Acct_Date => NULL,
619 p_CC_Desc => l_cc_headers_rec.cc_desc,
620 p_CC_Start_Date => trunc(SYSDATE),
621 p_CC_End_Date => NULL,
622 p_CC_Owner_User_Id => l_cc_headers_rec.cc_owner_user_id,
623 p_CC_Preparer_User_Id => l_cc_headers_rec.cc_preparer_user_id,
624 p_Currency_Code => l_cc_headers_rec.currency_code,
625 p_Conversion_Type => l_cc_headers_rec.conversion_type,
626 p_Conversion_Date => trunc(SYSDATE),
627 p_Conversion_Rate => l_cc_headers_rec.conversion_rate,
628 p_Last_Update_Date => SYSDATE,
629 p_Last_Updated_By => g_update_by,
630 p_Last_Update_Login => g_update_login,
631 p_Created_By => g_update_by,
632 p_Creation_Date => SYSDATE,
633 p_CC_Current_User_Id => l_cc_headers_rec.cc_current_user_id,
634 p_Wf_Item_Type => NULL,
635 p_Wf_Item_Key => NULL,
636 p_Attribute1 => l_cc_headers_rec.Attribute1,
637 p_Attribute2 => l_cc_headers_rec.Attribute2,
638 p_Attribute3 => l_cc_headers_rec.Attribute3,
639 p_Attribute4 => l_cc_headers_rec.Attribute4,
640 p_Attribute5 => l_cc_headers_rec.Attribute5,
641 p_Attribute6 => l_cc_headers_rec.Attribute6,
642 p_Attribute7 => l_cc_headers_rec.Attribute7,
643 p_Attribute8 => l_cc_headers_rec.Attribute8,
644 p_Attribute9 => l_cc_headers_rec.Attribute9,
645 p_Attribute10 => l_cc_headers_rec.Attribute10,
646 p_Attribute11 => l_cc_headers_rec.Attribute11,
647 p_Attribute12 => l_cc_headers_rec.Attribute12,
648 p_Attribute13 => l_cc_headers_rec.Attribute13,
649 p_Attribute14 => l_cc_headers_rec.Attribute14,
650 p_Attribute15 => l_cc_headers_rec.Attribute15,
651 p_Context => l_cc_headers_rec.Context,
652 p_CC_Guarantee_Flag => l_cc_headers_rec.CC_Guarantee_Flag,
653 G_FLAG => l_flag
654 );
655
656 -- ------------------------------------------------------------------------------------
657 -- Check to make sure that the Header was actually created.
658 -- ------------------------------------------------------------------------------------
659 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
660 IF (g_debug_mode = 'Y') THEN
661 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure inserting New CC Header id.........');
662 END IF;
663 RAISE FND_API.G_EXC_ERROR;
664 END IF;
665
666 -- ------------------------------------------------------------------------------------
667 -- Add the Entry into the Actions table indicating that the CC action is a COPY. This
668 -- resolves bug 1518478 and the new ACTION_TYPE added into lookups is "CP"
669 -- ------------------------------------------------------------------------------------
670 IGC_CC_ACTIONS_PKG.Insert_Row(
671 1.0,
672 FND_API.G_FALSE,
673 FND_API.G_FALSE,
674 FND_API.G_VALID_LEVEL_FULL,
675 l_return_status,
676 l_msg_count,
677 l_msg_data,
678 l_action_rowid,
679 p_new_CC_Header_Id,
680 0, -- Version number
681 'CP', -- Action Type "COPY"
682 l_CC_State,
683 l_cc_ctrl_status,
684 l_CC_Apprvl_Status,
685 'Copied From CC Number : ' || l_cc_headers_rec.cc_num, -- Note Field
686 Sysdate,
687 g_update_by,
688 g_update_login,
689 Sysdate,
690 g_update_by
691 );
692
693 -- ------------------------------------------------------------------------------------
694 -- Check to make sure that the Header was actually created.
695 -- ------------------------------------------------------------------------------------
696 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
697 IF (g_debug_mode = 'Y') THEN
698 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure inserting New CC Header id.........');
699 END IF;
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 -- ------------------------------------------------------------------------------------
704 -- Copy all the corresponding account lines for the CC Header given.
705 -- ------------------------------------------------------------------------------------
706 OPEN c_account_lines(p_old_cc_header_id);
707
708 LOOP
709
710 FETCH c_account_lines INTO l_cc_acct_lines_rec;
711
712 EXIT WHEN c_account_lines%NOTFOUND;
713
714 Acct_Line_Copy (p_cc_acct_line => l_cc_acct_lines_rec,
715 p_conversion_rate => l_cc_headers_rec.conversion_rate,
716 x_cc_acct_line_id => l_cc_acct_line_id,
717 x_return_status => l_return_status);
718
719 -- -----------------------------------------------------------------------------------
720 -- Check to make sure that the Account Line was actually created.
721 -- -----------------------------------------------------------------------------------
722 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
723 IF (g_debug_mode = 'Y') THEN
724 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure returned from Acct_Line_Copy......');
725 END IF;
726 RAISE FND_API.G_EXC_ERROR ;
727 END IF;
728
729 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
730
731 LOOP
732
733 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
734
735 EXIT WHEN c_payment_forecast%NOTFOUND;
736
737 Det_Pf_Line_Copy (p_cc_pmt_fcst => l_cc_pmt_fcst_rec,
738 p_cc_acct_line_id => l_cc_acct_line_id,
739 p_conversion_rate => l_cc_headers_rec.conversion_rate,
740 x_return_status => l_return_status);
741
742 -- -----------------------------------------------------------------------------------
743 -- Check to make sure that the Det PF Line was actually created.
744 -- -----------------------------------------------------------------------------------
745 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
746 IF (g_debug_mode = 'Y') THEN
747 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure returned from Det_Pf_Line_Copy........');
748 END IF;
749 RAISE FND_API.G_EXC_ERROR ;
750 END IF;
751
752 END LOOP;
753
754 CLOSE c_payment_forecast;
755
756 END LOOP;
757
758 CLOSE c_account_lines;
759
760 -- ------------------------------------------------------------------------------------
761 -- Copy all the corresponding Access lines records for the CC Header given.
762 -- ------------------------------------------------------------------------------------
763 OPEN c_access_lines(p_old_cc_header_id);
764
765 LOOP
766
767 FETCH c_access_lines INTO l_cc_access_lines_rec;
768
769 EXIT WHEN c_access_lines%NOTFOUND;
770
771 Access_Copy (p_access_lines => l_cc_access_lines_rec,
772 x_return_status => l_return_status);
773
774 -- -----------------------------------------------------------------------------------
775 -- Check to make sure that the Access Copy was actually done.
776 -- -----------------------------------------------------------------------------------
777 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
778 IF (g_debug_mode = 'Y') THEN
779 Put_Debug_Msg (l_full_path,' IGCCCPCB - Failure returned from Access_Copy.......');
780 END IF;
781 RAISE FND_API.G_EXC_ERROR ;
782 END IF;
783
784 END LOOP;
785
786 CLOSE c_access_lines;
787
788 IF (g_debug_mode = 'Y') THEN
789 Put_Debug_Msg (l_full_path,' IGCCCPCB - CC Header has been copied successfully.......');
790 END IF;
791
792 -- --------------------------------------------------------------------------------------
793 -- If there are no errors and the caller wants the commit to be performed then commit
794 -- since there were no exceptions encountered.
795 -- --------------------------------------------------------------------------------------
796 IF FND_API.To_Boolean(p_commit)
797 THEN
798 IF (g_debug_mode = 'Y') THEN
799 Put_Debug_Msg (l_full_path,' IGCCCPCB - Committing work performed......');
800 END IF;
801 COMMIT WORK;
802 END IF;
803
804 RETURN;
805
806 -- --------------------------------------------------------------------------------------
807 -- Exception Handler section for procedure.
808 -- --------------------------------------------------------------------------------------
809 EXCEPTION
810
811 WHEN FND_API.G_EXC_ERROR THEN
812 ROLLBACK TO Header_Copy_Pvt;
813 X_return_status := FND_API.G_RET_STS_ERROR;
814 g_new_cc_header_id := NULL;
815 IF (g_debug_mode = 'Y') THEN
816 Put_Debug_Msg (l_full_path,' IGCCCPCB - EXC ERROR encountered in the Header Copy routine.....');
817 END IF;
818 IF (c_access_lines%ISOPEN) THEN
819 CLOSE c_access_lines;
820 END IF;
821 IF (c_payment_forecast%ISOPEN) THEN
822 CLOSE c_payment_forecast;
823 END IF;
824 IF (c_account_lines%ISOPEN) THEN
825 CLOSE c_account_lines;
826 END IF;
827 IF (c_obtain_cc_header%ISOPEN) THEN
828 CLOSE c_obtain_cc_header;
829 END IF;
830 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
831 p_data => X_msg_data );
832 RETURN;
833
834 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
835 ROLLBACK TO Header_Copy_Pvt;
836 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
837 g_new_cc_header_id := NULL;
838 IF (g_debug_mode = 'Y') THEN
839 Put_Debug_Msg (l_full_path,' IGCCCPCB - EXC UNEXPECTED ERROR encountered in the Header Copy routine.....');
840 END IF;
841 IF (c_access_lines%ISOPEN) THEN
842 CLOSE c_access_lines;
843 END IF;
844 IF (c_payment_forecast%ISOPEN) THEN
845 CLOSE c_payment_forecast;
846 END IF;
847 IF (c_account_lines%ISOPEN) THEN
848 CLOSE c_account_lines;
849 END IF;
850 IF (c_obtain_cc_header%ISOPEN) THEN
851 CLOSE c_obtain_cc_header;
852 END IF;
853 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
854 p_data => X_msg_data );
855 RETURN;
856
857 WHEN OTHERS THEN
858 ROLLBACK TO Header_Copy_Pvt;
859 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
860 g_new_cc_header_id := NULL;
861 IF (g_debug_mode = 'Y') THEN
862 Put_Debug_Msg (l_full_path,' IGCCCPCB - OTHERS ERROR encountered in the Header Copy routine.....');
863 END IF;
864 IF (c_access_lines%ISOPEN) THEN
865 CLOSE c_access_lines;
866 END IF;
867 IF (c_payment_forecast%ISOPEN) THEN
868 CLOSE c_payment_forecast;
869 END IF;
870 IF (c_account_lines%ISOPEN) THEN
871 CLOSE c_account_lines;
872 END IF;
873 IF (c_obtain_cc_header%ISOPEN) THEN
874 CLOSE c_obtain_cc_header;
875 END IF;
876
877 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
878 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,
879 l_api_name);
880 END if;
881
882 FND_MSG_PUB.Count_And_Get (p_count => X_msg_count,
883 p_data => X_msg_data );
884 RETURN;
885
886 END Header_Copy;
887
888 /*modifed for 3199488 - fnd logging changes*/
889 PROCEDURE Put_Debug_Msg (
890 p_path IN VARCHAR2,
891 p_debug_msg IN VARCHAR2,
892 p_sev_level IN VARCHAR2 := g_state_level
893 ) IS
894 BEGIN
895
896 IF p_sev_level >= g_debug_level THEN
897 fnd_log.string(p_sev_level, p_path, p_debug_msg);
898 END IF;
899 END;
900 /*************
901 PROCEDURE Put_Debug_Msg (
902 p_debug_msg IN VARCHAR2
903 ) IS
904
905 l_Return_Status VARCHAR2(1);
906 l_api_name CONSTANT VARCHAR2(30) := 'Put_Debug_Msg';
907
908 BEGIN
909
910 IGC_MSGS_PKG.Put_Debug_Msg (l_full_path,p_debug_message => p_debug_msg,
911 p_profile_log_name => g_profile_name,
912 p_prod => g_prod,
913 p_sub_comp => g_sub_comp,
914 p_filename_val => NULL,
915 x_Return_Status => l_Return_Status
916 );
917
918 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
919 raise FND_API.G_EXC_ERROR;
920 END IF;
921
922 RETURN;
923
924 EXCEPTION
925
926 WHEN FND_API.G_EXC_ERROR THEN
927 RETURN;
928
929 WHEN OTHERS THEN
930 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
931 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
932 END IF;
933 RETURN;
934
935 END Put_Debug_Msg;
936 ****************/
937 END IGC_CC_COPY_PKG;