[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_MPFS_PROCESS_PKG
Source
1 PACKAGE BODY IGC_CC_MPFS_PROCESS_PKG as
2 /* $Header: IGCCMPSB.pls 120.23.12010000.3 2008/11/04 09:24:24 dramired ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IGC_CC_MPFS_PROCESS_PKG';
5 g_debug_flag VARCHAR2(1);
6
7 g_line_num NUMBER;
8
9 --g_debug_mode VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
10 g_debug_mode VARCHAR2(1);
11 --Variables for ATG Central logging
12 g_debug_level NUMBER;
13 g_state_level NUMBER;
14 g_proc_level NUMBER;
15 g_event_level NUMBER;
16 g_excep_level NUMBER;
17 g_error_level NUMBER;
18 g_unexp_level NUMBER;
19 g_path VARCHAR2(255);
20
21
22 -- Write log
23 PROCEDURE WriteLog(p_mesg IN VARCHAR2) IS
24 BEGIN
25 -- FND_FILE.put_line(FND_FILE.log, p_mesg);
26 null;
27 END WriteLog;
28 --
29 -- Generic Procedure for putting out debug information
30 --
31 PROCEDURE Output_Debug (p_path VARCHAR2,
32 p_debug_msg IN VARCHAR2
33 );
34
35 -- ==============================================================================
36 -- Generic procedure that will extract the fiscal year
37 -- for a given date (bug 2124595)
38 -- ==============================================================================
39
40 FUNCTION Get_Fiscal_Year(p_date IN DATE,
41 p_sob_id IN NUMBER)
42 RETURN number IS
43
44 -- Define cursor to extract the fiscal year for p_date
45 CURSOR c_fiscal_year(p_sob_id NUMBER) IS
46 SELECT period_year
47 FROM gl_periods gp,
48 gl_sets_of_books gsob
49 WHERE gp.period_set_name = gsob.period_set_name
50 AND gp.period_type = gsob.accounted_period_type
51 AND trunc(p_date) BETWEEN trunc(gp.start_date)
52 AND trunc(gp.end_date)
53 AND gsob.set_of_books_id = p_sob_id;
54
55 -- Define local variables
56 l_fiscal_year NUMBER;
57
58 -- Define exceptions
59 e_fiscal_year_not_found EXCEPTION;
60
61 l_full_path VARCHAR2(255);
62
63 BEGIN
64
65 l_full_path := g_path || 'Get_Fiscal_Year';
66
67 -- Get the fiscal year
68 OPEN c_fiscal_year(p_sob_id);
69
70 IF (c_fiscal_year%NOTFOUND) THEN
71 RAISE e_fiscal_year_not_found;
72 END IF;
73
74 FETCH c_fiscal_year INTO l_fiscal_year;
75
76 CLOSE c_fiscal_year;
77
78 RETURN l_fiscal_year;
79
80 EXCEPTION
81 WHEN e_fiscal_year_not_found THEN
82 IF (g_excep_level >= g_debug_level ) THEN
83 FND_LOG.STRING (g_excep_level,l_full_path,'e_fiscal_year_not_found Exception Raised');
84 END IF;
85 l_fiscal_year := '';
86 Output_Debug(l_full_path, 'IGCCPSMB, procedure Get_Fiscal_Year, fiscal year not found');
87 RETURN l_fiscal_year;
88
89 END Get_Fiscal_Year;
90
91 /*=================================================================================
92 Procedure Insert_Interface_Row
93 =================================================================================*/
94
95 /* Inserts row into budgetary control interface table */
96
97 PROCEDURE Insert_Interface_Row(p_cc_interface_rec IN igc_cc_interface%ROWTYPE)
98 IS
99 l_full_path VARCHAR2(255);
100 BEGIN
101
102 l_full_path := g_path || 'Insert_Interface_Row';
103 INSERT INTO igc_cc_interface (
104 batch_line_num,
105 cc_header_id,
106 cc_version_num,
107 cc_acct_line_id,
108 cc_det_pf_line_id,
109 set_of_books_id,
110 code_combination_id,
111 cc_transaction_date,
112 transaction_description,
113 encumbrance_type_id,
114 currency_code,
115 cc_func_dr_amt,
116 cc_func_cr_amt,
117 je_source_name,
118 je_category_name,
119 actual_flag,
120 budget_dest_flag,
121 last_update_date,
122 last_updated_by,
123 last_update_login,
124 creation_date,
125 created_by,
126 period_set_name,
127 period_name,
128 cbc_result_code,
129 status_code,
130 budget_version_id,
131 budget_amt,
132 commitment_encmbrnc_amt,
133 obligation_encmbrnc_amt,
134 funds_available_amt,
135 document_type,
136 reference_1,
137 reference_2,
138 reference_3,
139 reference_4,
140 reference_5,
141 reference_6,
142 reference_7,
143 reference_8,
144 reference_9,
145 reference_10,
146 cc_encmbrnc_date,
147 /* Bug No : 6341012. SLA uptake. Event_ID, Project_Line are added to IGC_CC_INTERFACE Table */
148 event_id,
149 project_line)
150 VALUES
151 (p_cc_interface_rec.batch_line_num,
152 p_cc_interface_rec.cc_header_id,
153 p_cc_interface_rec.cc_version_num,
154 p_cc_interface_rec.cc_acct_line_id,
155 p_cc_interface_rec.cc_det_pf_line_id,
156 p_cc_interface_rec.set_of_books_id,
157 p_cc_interface_rec.code_combination_id,
158 p_cc_interface_rec.cc_transaction_date,
159 p_cc_interface_rec.transaction_description,
160 p_cc_interface_rec.encumbrance_type_id,
161 p_cc_interface_rec.currency_code,
162 p_cc_interface_rec.cc_func_dr_amt,
163 p_cc_interface_rec.cc_func_cr_amt,
164 p_cc_interface_rec.je_source_name,
165 p_cc_interface_rec.je_category_name,
166 p_cc_interface_rec.actual_flag,
167 p_cc_interface_rec.budget_dest_flag,
168 p_cc_interface_rec.last_update_date,
169 p_cc_interface_rec.last_updated_by,
170 p_cc_interface_rec.last_update_login,
171 p_cc_interface_rec.creation_date,
172 p_cc_interface_rec.created_by,
173 p_cc_interface_rec.period_set_name,
174 p_cc_interface_rec.period_name,
175 p_cc_interface_rec.cbc_result_code,
176 p_cc_interface_rec.status_code,
177 p_cc_interface_rec.budget_version_id,
178 p_cc_interface_rec.budget_amt,
179 p_cc_interface_rec.commitment_encmbrnc_amt,
180 p_cc_interface_rec.obligation_encmbrnc_amt,
181 p_cc_interface_rec.funds_available_amt,
182 p_cc_interface_rec.document_type,
183 p_cc_interface_rec.reference_1,
184 p_cc_interface_rec.reference_2,
185 p_cc_interface_rec.reference_3,
186 p_cc_interface_rec.reference_4,
187 p_cc_interface_rec.reference_5,
188 p_cc_interface_rec.reference_6,
189 p_cc_interface_rec.reference_7,
190 p_cc_interface_rec.reference_8,
191 p_cc_interface_rec.reference_9,
192 p_cc_interface_rec.reference_10,
193 p_cc_interface_rec.cc_encmbrnc_date,
194 /* Bug No : 6341012. SLA uptake. Event_ID, Project_Line are added to IGC_CC_INTERFACE Table */
195 p_cc_interface_rec.event_id,
196 p_cc_interface_rec.project_line);
197
198
199 END Insert_Interface_Row;
200
201 /*=================================================================================
202 Procedure Process_Interface_Row
203 =================================================================================*/
204
205 /* Populates the interface table for the budgetary control as per the process*/
206
207 PROCEDURE Process_Interface_Row(
208 p_currency_code IN VARCHAR2,
209 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_Ids are not required*/
210 -- p_purch_encumbrance_type_id IN financials_system_params_all.purch_encumbrance_type_id%TYPE,
211 p_cc_headers_rec IN igc_cc_headers%ROWTYPE,
212 p_cc_acct_lines_rec IN igc_cc_acct_lines%ROWTYPE,
213 p_cc_pmt_fcst_rec IN igc_cc_det_pf_v%ROWTYPE,
214 p_enc_type IN VARCHAR2,
215 p_enc_date IN DATE,
216 p_enc_amt IN NUMBER,
217 x_return_status OUT NOCOPY VARCHAR2,
218 x_msg_count OUT NOCOPY NUMBER,
219 x_msg_data OUT NOCOPY VARCHAR2)
220 IS
221
222 l_cc_interface_rec igc_cc_interface%ROWTYPE;
223
224 l_enc_amt NUMBER;
225 l_enc_tax_amt NUMBER;
226 l_msg_count NUMBER;
227 l_msg_data VARCHAR2(2000);
228 l_return_status VARCHAR2(1);
229 l_full_path VARCHAR2(255);
230 P_Error_Code VARCHAR2(32); /*EB Tax uptake - Bug No : 6472296*/
231 l_taxable_flag VARCHAR2(2); /*Bug 6472296 EB Tax uptake - CC*/
232
233 BEGIN
234
235 l_full_path := g_path || 'Process_Interface_Row';
236
237 -- Bug 2409502, Calculate the non recoverable tax on the p_enc_amt
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239 l_enc_amt := p_enc_amt;
240
241 /*EB Tax uptake - Bug No : 6472296*/
242 /*igc_cc_budgetary_ctrl_pkg.calculate_nonrec_tax
243 (p_api_version => 1.0,
244 p_init_msg_list => FND_API.G_TRUE,
245 p_commit => FND_API.G_FALSE,
246 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
247 x_return_status => l_return_status,
248 x_msg_count => l_msg_count,
249 x_msg_data => l_msg_data,
250 p_tax_id => p_cc_acct_lines_rec.tax_id,
251 p_amount => l_enc_amt,
252 p_tax_amount => l_enc_tax_amt);
253 */
254 l_taxable_flag := nvl(p_cc_acct_lines_rec.cc_acct_taxable_flag,'N');
255 IF (l_taxable_flag = 'Y') THEN
256 IGC_ETAX_UTIL_PKG.Calculate_Tax
257 (P_CC_Header_Rec =>p_cc_headers_rec,
258 P_Calling_Mode =>null,
259 P_Amount =>l_enc_amt,
260 P_Line_Id =>p_cc_acct_lines_rec.cc_acct_line_id,
261 P_Tax_Amount =>l_enc_tax_amt,
262 P_Return_Status =>l_return_status,
263 P_Error_Code =>P_Error_Code);
264 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
265 THEN
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268 END IF;
269 /*EB Tax uptake - Bug No : 6472296 END*/
270 l_enc_amt := l_enc_amt + Nvl(l_enc_tax_amt,0);
271 -- Bug 2409502, End
272
273 l_cc_interface_rec.cbc_result_code := NULL;
274 l_cc_interface_rec.status_code := NULL;
275 l_cc_interface_rec.budget_version_id := NULL;
276 l_cc_interface_rec.budget_amt := NULL;
277 l_cc_interface_rec.commitment_encmbrnc_amt := NULL;
278 l_cc_interface_rec.obligation_encmbrnc_amt := NULL;
279 l_cc_interface_rec.funds_available_amt := NULL;
280 l_cc_interface_rec.reference_1 := NULL;
281 l_cc_interface_rec.reference_2 := NULL;
282 l_cc_interface_rec.reference_3 := NULL;
283 l_cc_interface_rec.reference_4 := NULL;
284 l_cc_interface_rec.reference_5 := NULL;
285 l_cc_interface_rec.reference_6 := NULL;
286 l_cc_interface_rec.reference_7 := NULL;
287 l_cc_interface_rec.reference_8 := NULL;
288 l_cc_interface_rec.reference_9 := NULL;
289 l_cc_interface_rec.reference_10 := NULL;
290 l_cc_interface_rec.cc_encmbrnc_date := NULL;
291 l_cc_interface_rec.document_type := 'CC';
292
293 l_cc_interface_rec.cc_header_id := p_cc_headers_rec.cc_header_id;
294 l_cc_interface_rec.cc_version_num := p_cc_headers_rec.cc_version_num + 1;
295 l_cc_interface_rec.set_of_books_id := p_cc_headers_rec.set_of_books_id;
296
297 l_cc_interface_rec.code_combination_id := p_cc_acct_lines_rec.cc_budget_code_combination_id;
298
299 l_cc_interface_rec.currency_code := p_currency_code;
300 /* Bug No : 6341012. SLA uptake. je_source_name will not be populated by this package*/
301 -- l_cc_interface_rec.je_source_name := 'Contract Commitment';
302 l_cc_interface_rec.actual_flag := 'E';
303 l_cc_interface_rec.last_update_date := sysdate;
304 l_cc_interface_rec.last_updated_by := -1;
305 l_cc_interface_rec.last_update_login := -1;
306 l_cc_interface_rec.creation_date := sysdate;
307 l_cc_interface_rec.created_by := -1;
308
309 l_cc_interface_rec.transaction_description := LTRIM(RTRIM(p_cc_headers_rec.cc_num))
310 || ' ' || rtrim(ltrim(p_cc_acct_lines_rec.cc_acct_desc));
311
312 /* Bug No : 6341012. SLA uptake. je_category_name will not be populated by this package*/
313 -- l_cc_interface_rec.je_category_name := 'Confirmed';
314
315 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_Ids are not required */
316 -- l_cc_interface_rec.encumbrance_type_id := p_purch_encumbrance_type_id;
317 l_cc_interface_rec.encumbrance_type_id := Null;
318
319 /* Bug No : 6341012. SLA uptake. Acct_line_Id should be populated*/
320 l_cc_interface_rec.cc_acct_line_id := p_cc_acct_lines_rec.cc_acct_line_id; --NULL;
321 l_cc_interface_rec.cc_det_pf_line_id := p_cc_pmt_fcst_rec.cc_det_pf_line_id;
322 l_cc_interface_rec.budget_dest_flag := 'S';
323 l_cc_interface_rec.reference_1 := p_cc_headers_rec.cc_header_id;
324 l_cc_interface_rec.reference_2 := p_cc_acct_lines_rec.cc_acct_line_id;
325 l_cc_interface_rec.reference_3 := p_cc_headers_rec.cc_version_num + 1;
326 /* Bug No : 6341012. SLA uptake. Reference4 Should be CC_Number*/
327 -- l_cc_interface_rec.reference_4 := p_cc_pmt_fcst_rec.cc_det_pf_line_id;
328 l_cc_interface_rec.reference_4 := p_cc_headers_rec.cc_num;
329
330 /* Bug No : 6341012. SLA uptake. Event_id, Project_Line are new columns in Interface table*/
331 l_cc_interface_rec.Event_id := Null;
332 l_cc_interface_rec.Project_line := Null;
333
334
335
336 g_line_num := g_line_num + 1;
337
338 l_cc_interface_rec.cc_transaction_date := p_enc_date;
339 l_cc_interface_rec.batch_line_num := g_line_num;
340
341 IF (p_enc_type = 'DR')
342 THEN
343 l_cc_interface_rec.cc_func_cr_amt := NULL;
344 l_cc_interface_rec.cc_func_dr_amt := l_enc_amt;
345 ELSIF (p_enc_type = 'CR')
346 THEN
347 l_cc_interface_rec.cc_func_cr_amt := l_enc_amt;
348 l_cc_interface_rec.cc_func_dr_amt := NULL;
349 END IF;
350
351 Insert_Interface_Row(l_cc_interface_rec);
352
353 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
354 p_data => x_msg_data );
355 EXCEPTION
356 WHEN FND_API.G_EXC_ERROR
357 THEN
358 x_return_status := FND_API.G_RET_STS_ERROR;
359
360 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
361 p_data => x_msg_data );
362 IF (g_excep_level >= g_debug_level ) THEN
363 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
364 END IF;
365
366 RETURN;
367
368 END Process_Interface_Row;
369
370 /*=================================================================================
371 Function Encumber_CC
372 =================================================================================*/
373
374 FUNCTION Encumber_CC
375 ( p_currency_code IN VARCHAR2,
376 p_cc_header_id IN NUMBER,
377 p_sbc_on IN BOOLEAN,
378 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_Ids are not required*/
379 -- p_purch_encumbrance_type_id IN NUMBER,
380 p_start_date IN DATE,
381 p_end_date IN DATE,
382 p_transfer_date IN DATE,
383 p_target_date IN DATE
384 ) RETURN VARCHAR2
385 IS
386 l_interface_row_count NUMBER;
387
388 l_cc_headers_rec igc_cc_headers%ROWTYPE;
389 l_cc_acct_lines_rec igc_cc_acct_lines%ROWTYPE;
390 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
391
392 l_enc_amt NUMBER := 0;
393 l_enc_date DATE;
394
395 l_cc_det_pf_line_id igc_cc_det_pf.cc_det_pf_line_id%TYPE;
396
397 l_debug VARCHAR2(1);
398
399 l_batch_result_code VARCHAR2(3);
400 l_bc_return_status VARCHAR2(2);
401 l_bc_success BOOLEAN;
402
403 l_msg_count NUMBER;
404 l_msg_data VARCHAR2(2000);
405 l_return_status VARCHAR2(1);
406
407 e_process_row EXCEPTION;
408 e_bc_execution EXCEPTION;
409 e_cc_not_found EXCEPTION;
410 e_delete EXCEPTION;
411 e_no_target_pf EXCEPTION;
412
413 /* Contract Commitment detail payment forecast */
414
415 -- SELECT *
416 -- FROM igc_cc_det_pf_v
417 -- WHERE cc_acct_line_id = t_cc_acct_line_id;
418
419 --Replaced the above query with the one below.
420 --Performance Tuning project. The record definition remains
421 --the same , but only the relevant columns are selected.
422 CURSOR c_payment_forecast(t_cc_acct_line_id NUMBER) IS
423 SELECT ccdpf.ROWID,
424 ccdpf.cc_det_pf_line_id,
425 ccdpf.cc_det_pf_line_num,
426 NULL cc_acct_line_num,
427 ccdpf.cc_acct_line_id,
428 NULL parent_det_pf_line_num,
429 ccdpf.parent_det_pf_line_id,
430 ccdpf.parent_acct_line_id,
431 ccdpf.cc_det_pf_entered_amt,
432 ccdpf.cc_det_pf_func_amt,
433 ccdpf.cc_det_pf_date,
434 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
435 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
436 ccdpf.cc_det_pf_unbilled_amt,
437 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
438 ccdpf.cc_det_pf_encmbrnc_amt,
439 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
440 ccdpf.cc_det_pf_encmbrnc_date,
441 ccdpf.cc_det_pf_encmbrnc_status,
442 ccdpf.context,
443 ccdpf.attribute1,
444 ccdpf.attribute2,
445 ccdpf.attribute3,
446 ccdpf.attribute4,
447 ccdpf.attribute5,
448 ccdpf.attribute6,
449 ccdpf.attribute7,
450 ccdpf.attribute8,
451 ccdpf.attribute9,
452 ccdpf.attribute10,
453 ccdpf.attribute11,
454 ccdpf.attribute12,
455 ccdpf.attribute13,
456 ccdpf.attribute14,
457 ccdpf.attribute15,
458 ccdpf.last_update_date,
459 ccdpf.last_updated_by,
460 ccdpf.last_update_login,
461 ccdpf.creation_date,
462 ccdpf.created_by
463 FROM igc_cc_det_pf ccdpf
464 WHERE cc_acct_line_id = t_cc_acct_line_id;
465
466 /* Current year payment forecast lines only */
467
468 /* Contract Commitment account lines */
469
470 CURSOR c_account_lines(t_cc_header_id NUMBER) IS
471 SELECT *
472 FROM igc_cc_acct_lines ccac
473 WHERE ccac.cc_header_id = t_cc_header_id;
474
475 l_full_path VARCHAR2(255);
476
477 BEGIN
478
479 l_full_path := g_path || 'Encumber_CC';
480 SAVEPOINT Execute_Budgetary_Ctrl1;
481
482 g_line_num := 0;
483
484
485 BEGIN
486
487 SELECT *
488 INTO l_cc_headers_rec
489 FROM igc_cc_headers
490 WHERE cc_header_id = p_cc_header_id;
491
492 EXCEPTION
493
494 WHEN OTHERS
495 THEN
496 IF ( g_unexp_level >= g_debug_level ) THEN
497 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
498 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
499 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
500 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
501 END IF;
502 RAISE E_CC_NOT_FOUND;
503
504 END;
505
506
507 /* Delete existing interface rows */
508
509
510 BEGIN
511
512 DELETE igc_cc_interface
513 WHERE cc_header_id = p_cc_header_id AND
514 actual_flag = 'E';
515 EXCEPTION
516 WHEN OTHERS
517 THEN
518 NULL;
519 END;
520
521 COMMIT;
522
523 SAVEPOINT Execute_Budgetary_Ctrl2;
524
525 /* Populate interface rows with source pf lines un_billed amounts*/
526
527
528 OPEN c_account_lines(p_cc_header_id);
529
530 LOOP
531 FETCH c_account_lines INTO l_cc_acct_lines_rec;
532
533 EXIT WHEN c_account_lines%NOTFOUND;
534
535
536 OPEN c_payment_forecast(l_cc_acct_lines_rec.cc_acct_line_id);
537
538 LOOP
539 FETCH c_payment_forecast INTO l_cc_pmt_fcst_rec;
540
541 EXIT WHEN c_payment_forecast%NOTFOUND;
542
543 l_enc_amt := 0;
544
545 /* check whether payment forecast belongs to yr-end processing year */
546 IF ( (l_cc_pmt_fcst_rec.cc_det_pf_date <= p_end_date) AND
547 (l_cc_pmt_fcst_rec.cc_det_pf_date >= p_start_date)
548 )
549 THEN
550 l_enc_amt := l_cc_pmt_fcst_rec.cc_det_pf_func_amt - l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt;
551 IF (p_transfer_date >= l_cc_pmt_fcst_rec.cc_det_pf_date)
552 THEN
553 l_enc_date := p_transfer_date;
554 ELSE
555 l_enc_date := l_cc_pmt_fcst_rec.cc_det_pf_date;
556 END IF;
557
558 IF (l_enc_amt > 0)
559 THEN
560 Process_Interface_Row(
561 p_currency_code, /* Bug 634102 commented the following parameter
562 p_purch_encumbrance_type_id, */
563 l_cc_headers_rec,
564 l_cc_acct_lines_rec,
565 l_cc_pmt_fcst_rec,
566 'CR',
567 l_enc_date,
568 l_enc_amt,
569 l_return_status,
570 l_msg_count,
571 l_msg_data
572 );
573 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
574 THEN
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 END IF;
579 END IF;
580
581 END LOOP;
582
583 CLOSE c_payment_forecast;
584
585 l_enc_amt := 0;
586 l_cc_det_pf_line_id := 0;
587
588 /* Get the target pf */
589
590 BEGIN
591
592 SELECT a.cc_det_pf_line_id
593 INTO l_cc_det_pf_line_id
594 FROM igc_cc_det_pf a
595 WHERE
596 a.cc_det_pf_line_num = (SELECT NVL(min(b.cc_det_pf_line_num) , -1)
597 FROM igc_cc_det_pf b
598 WHERE b.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id AND
599 b.cc_det_pf_date = /* bug fix 1702768 */
600 (SELECT min(c.cc_det_pf_date)
601 FROM igc_cc_det_pf c
602 WHERE c.cc_acct_line_id =
603 l_cc_acct_lines_rec.cc_acct_line_id AND
604 c.cc_det_pf_date >= p_target_date)
605 ) AND
606 a.cc_det_pf_date >= p_target_date AND
607 a.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id;
608
609 -- Replaced the following sql with the one below to eliminate
610 -- the use of igc_cc_det_pf_v
611 -- SELECT *
612 -- INTO l_cc_pmt_fcst_rec
613 -- FROM igc_cc_det_pf_v
614 -- WHERE cc_det_pf_line_id = l_cc_det_pf_line_id;
615
616 SELECT ccdpf.ROWID,
617 ccdpf.cc_det_pf_line_id,
618 ccdpf.cc_det_pf_line_num,
619 NULL cc_acct_line_num,
620 ccdpf.cc_acct_line_id,
621 NULL parent_det_pf_line_num,
622 ccdpf.parent_det_pf_line_id,
623 ccdpf.parent_acct_line_id,
624 ccdpf.cc_det_pf_entered_amt,
625 ccdpf.cc_det_pf_func_amt,
626 ccdpf.cc_det_pf_date,
627 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
628 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
629 ccdpf.cc_det_pf_unbilled_amt,
630 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
631 ccdpf.cc_det_pf_encmbrnc_amt,
632 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
633 ccdpf.cc_det_pf_encmbrnc_date,
634 ccdpf.cc_det_pf_encmbrnc_status,
635 ccdpf.context,
636 ccdpf.attribute1,
637 ccdpf.attribute2,
638 ccdpf.attribute3,
639 ccdpf.attribute4,
640 ccdpf.attribute5,
641 ccdpf.attribute6,
642 ccdpf.attribute7,
643 ccdpf.attribute8,
644 ccdpf.attribute9,
645 ccdpf.attribute10,
646 ccdpf.attribute11,
647 ccdpf.attribute12,
648 ccdpf.attribute13,
649 ccdpf.attribute14,
650 ccdpf.attribute15,
651 ccdpf.last_update_date,
652 ccdpf.last_updated_by,
653 ccdpf.last_update_login,
654 ccdpf.creation_date,
655 ccdpf.created_by
656 INTO l_cc_pmt_fcst_rec
657 FROM igc_cc_det_pf ccdpf
658 WHERE cc_det_pf_line_id = l_cc_det_pf_line_id;
659 EXCEPTION
660 WHEN NO_DATA_FOUND
661 THEN
662 IF (g_excep_level >= g_debug_level ) THEN
663 FND_LOG.STRING (g_excep_level,l_full_path,'NO_DATA_FOUND Exception Raised');
664 END IF;
665 RAISE E_NO_TARGET_PF;
666 END;
667
668 /* Get target pf encumbrance amt */
669
670 -- Replaced igc_cc_det_pf_v with igc_cc_det_pf
671 -- Also replaced the following line
672 -- SELECT NVL(SUM(NVL(cc_det_pf_func_amt,0) - NVL(cc_det_pf_func_billed_amt,0)) ,0)
673 SELECT NVL(SUM(NVL(a.cc_det_pf_func_amt,0) -
674 NVL(IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(a.cc_det_pf_line_id, a.cc_det_pf_line_num, a.cc_acct_line_id),0)) ,0)
675 INTO l_enc_amt
676 FROM igc_cc_det_pf a
677 WHERE a.cc_acct_line_id = l_cc_acct_lines_rec.cc_acct_line_id
678 -- The check for det_pf_line is not correct.The amount calculated should not be from
679 -- the target payment forecast. Bug 2858425, 19 March 2003
680 -- AND cc_det_pf_line_id = l_cc_det_pf_line_id
681 AND a.cc_det_pf_date >= p_start_date AND a.cc_det_pf_date <= p_end_date;
682
683
684 IF (l_enc_amt > 0)
685 THEN
686 Process_Interface_Row(
687 p_currency_code,
688 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_Ids are not required*/
689 -- p_purch_encumbrance_type_id,
690 l_cc_headers_rec,
691 l_cc_acct_lines_rec,
692 l_cc_pmt_fcst_rec,
693 'DR',
694 l_cc_pmt_fcst_rec.cc_det_pf_date,
695 l_enc_amt,
696 l_return_status,
697 l_msg_count,
698 l_msg_data);
699
700 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
701 THEN
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704 END IF;
705
706 END LOOP;
707
708 CLOSE c_account_lines;
709
710 COMMIT;
711
712 l_interface_row_count := 0;
713
714 SELECT count(*)
715 INTO l_interface_row_count
716 FROM igc_cc_interface
717 WHERE cc_header_id = p_cc_header_id;
718
719 SAVEPOINT Execute_Budgetary_Ctrl4;
720
721 /* Execute budgetary control */
722
723 IF (l_interface_row_count <> 0)
724 THEN
725 l_batch_result_code := NULL;
726
727 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
728
729 -- IF (l_debug = 'Y')
730 IF (g_debug_mode = 'Y')
731 THEN
732 l_debug := FND_API.G_TRUE;
733 ELSE
734 l_debug := FND_API.G_FALSE;
735 END IF;
736
737 BEGIN
738
739 -- The call to IGCFCK updated to IGCPAFCK for bug 1844214.
740 -- Bidisha S , 21 June 2001
741 -- l_bc_success := IGC_CBC_FUNDS_CHECKER.IGCFCK(
742 l_bc_success := IGC_CBC_PA_BC_PKG.IGCPAFCK(
743 p_sobid => l_cc_headers_rec.set_of_books_id,
744 p_header_id => l_cc_headers_rec.cc_header_id,
745 p_mode => 'F',
746 p_actual_flag => 'E',
747 p_ret_status => l_bc_return_status,
748 p_batch_result_code => l_batch_result_code,
749 p_doc_type => 'CC',
750 p_debug => l_debug,
751 p_conc_proc => FND_API.G_FALSE);
752 EXCEPTION
753 WHEN OTHERS
754 THEN
755 IF ( g_unexp_level >= g_debug_level ) THEN
756 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
757 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
758 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
759 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
760 END IF;
761 /*IF (g_debug_mode = 'Y') THEN
762 Output_Debug (l_full_path, ' SQLERRM ' || SQLERRM);
763 END IF;*/
764 RETURN 'F';
765
766 END;
767
768 IF (l_bc_success = TRUE)
769 THEN
770 IF ( (l_bc_return_status <> 'NA') AND
771 (l_bc_return_status <> 'AN') AND
772 (l_bc_return_status <> 'AA') AND
773 (l_bc_return_status <> 'AS') AND
774 (l_bc_return_status <> 'SA') AND
775 (l_bc_return_status <> 'SS') AND
776 (l_bc_return_status <> 'SN') AND
777 (l_bc_return_status <> 'NS') )
778 THEN
779 RETURN('F');
780 ELSE
781 RETURN('P');
782 END IF;
783 ELSE
784 RETURN('F');
785 END IF;
786 ELSE
787 RETURN('P');
788 END IF;
789
790 EXCEPTION
791 WHEN FND_API.G_EXC_ERROR
792 THEN
793 IF (g_excep_level >= g_debug_level ) THEN
794 FND_LOG.STRING (g_excep_level,l_full_path,'FND_API.G_EXC_ERROR Exception Raised');
795 END IF;
796 RETURN('F');
797
798 WHEN OTHERS
799 THEN
800 IF ( g_unexp_level >= g_debug_level ) THEN
801 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
802 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
803 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
804 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
805 END IF;
806 /* IF (g_debug_mode = 'Y') THEN
807 Output_Debug (l_full_path, ' SQLERRM ' || SQLERRM);
808 END IF;*/
809 RETURN('F');
810
811 END Encumber_CC;
812
813 /* This Function returns TRUE if the date which is passed as a parameter fall in a
814 GL Period which is Open or Future Entry status */
815
816 /*=================================================================================
817 Function IS_GL_PERIOD_OPEN
818 =================================================================================*/
819
820 FUNCTION IS_GL_PERIOD_OPEN (p_date_to_check IN DATE,
821 p_sob_id1 IN NUMBER)
822
823 RETURN VARCHAR2 AS
824 l_period_status gl_period_statuses.closing_status%type;
825 l_full_path VARCHAR2(255);
826 BEGIN
827
828 l_full_path := g_path || 'IS_GL_PERIOD_OPEN';
829
830 SELECT gps.closing_status
831 INTO l_period_status
832 FROM gl_period_statuses gps,
833 gl_periods gp,
834 gl_sets_of_books gb,
835 gl_period_types gpt,
836 fnd_application fa
837 WHERE
838 gb.set_of_books_id = p_sob_id1 AND
839 gp.period_set_name = gb.period_set_name AND
840 gp.period_type = gb.accounted_period_type AND
841 gp.adjustment_period_flag = 'N' AND
842 gpt.period_type = gp.period_type AND
843 gps.set_of_books_id = gb.set_of_books_id AND
844 gps.period_name = gp.period_name AND
845 gps.application_id = fa.application_id AND
846 fa.application_short_name = 'SQLGL' AND
847 (gp.start_date <= p_date_to_check AND gp.end_date >= p_date_to_check);
848
849 IF (l_period_status = NULL) OR ((NVL(l_period_status,'X') <> 'O') AND (NVL(l_period_status,'X')<> 'F')) THEN
850 RETURN (FND_API.G_FALSE);
851 ELSE
852 RETURN (FND_API.G_TRUE);
853 END IF;
854
855
856 EXCEPTION
857
858 WHEN NO_DATA_FOUND
859 THEN
860 IF ( g_unexp_level >= g_debug_level ) THEN
861 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
862 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
863 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
864 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
865 END IF;
866 RETURN (FND_API.G_FALSE);
867 END IS_GL_PERIOD_OPEN;
868
869 -- Bug 1634159 Fixed
870 /*=================================================================================
871 Function IS_CC_PERIOD_OPEN
872 =================================================================================*/
873
874
875 FUNCTION IS_CC_PERIOD_OPEN (p_date_to_check IN DATE,
876 p_sob_id2 IN NUMBER,
877 p_org_id IN NUMBER)
878
879 RETURN VARCHAR2 AS
880 l_cc_period_status igc_cc_periods.cc_period_status%type;
881 l_full_path VARCHAR2(255);
882
883 BEGIN
884
885 l_full_path := g_path || 'IS_CC_PERIOD_OPEN';
886 SELECT ccp.cc_period_status
887 INTO l_cc_period_status
888 FROM igc_cc_periods ccp,
889 gl_periods gp ,
890 gl_sets_of_books gb
891 WHERE
892 ccp.period_set_name = gp.period_set_name AND
893 gp.period_set_name = gb.period_set_name AND
894 ccp.org_id = p_org_id AND
895 ccp.period_name = gp.period_name AND
896 gp.period_type = gb.accounted_period_type AND
897 gp.adjustment_period_flag = 'N' AND
898 gb.set_of_books_id = p_sob_id2 AND
899 (gp.start_date <= p_date_to_check AND gp.end_date >= p_date_to_check);
900
901 IF (l_cc_period_status = NULL) OR ((NVL(l_cc_period_status,'X') <> 'O') AND (NVL(l_cc_period_status,'X')<> 'F')) THEN
902 RETURN (FND_API.G_FALSE);
903 ELSE
904 RETURN (FND_API.G_TRUE);
905 END IF;
906
907
908 EXCEPTION
909 WHEN NO_DATA_FOUND
910 THEN
911 IF ( g_unexp_level >= g_debug_level ) THEN
912 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
913 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
914 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
915 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
916 END IF;
917 RETURN (FND_API.G_FALSE);
918 END; /* End of IS_CC_PERIOD_OPEN Function */
919
920
921 /* This Procedure validates the parameter and return the result exception if any.
922 Please refer to the technical design document for Mass Payment Forecast Shift
923 for more detail explanation about the paramter validation. */
924
925 /*=================================================================================
926 Procedure VALIDATE_PARAMS
927 =================================================================================*/
928
929 PROCEDURE VALIDATE_PARAMS (p_process_phase IN VARCHAR2,
930 p_start_date IN DATE,
931 p_end_date IN DATE,
932 p_transfer_date IN DATE,
933 p_target_date IN DATE,
934 p_sob_id IN NUMBER,
935 p_org_id IN NUMBER,
936 p_sbc_on IN BOOLEAN,
937 p_result OUT NOCOPY VARCHAR2,
938 p_exception OUT NOCOPY VARCHAR2)
939 AS
940
941 l_gl_period_open VARCHAR2(1);
942 l_cc_period_open VARCHAR2(1);
943 l_validated VARCHAR2(1);
944
945 l_sdate_fiscal_year NUMBER;
946 l_edate_fiscal_year NUMBER;
947 l_tdate_fiscal_year NUMBER;
948
949 l_full_path VARCHAR2(255);
950
951 BEGIN
952 l_gl_period_open := FND_API.G_FALSE;
953 l_cc_period_open := FND_API.G_FALSE;
954 l_validated := FND_API.G_FALSE;
955
956 l_full_path := g_path || 'VALIDATE_PARAMS';
957
958 -- Get the fiscal years for the dates
959 -- Bug fix 2124595 start 1
960 l_sdate_fiscal_year := Get_Fiscal_Year(p_start_date, p_sob_id);
961 l_edate_fiscal_year := Get_Fiscal_Year(p_end_date, p_sob_id);
962 l_tdate_fiscal_year := Get_Fiscal_Year(p_transfer_date, p_sob_id);
963
964 -- Bug fix 2124595 end 1
965
966 IF (p_end_date >= p_start_date) THEN
967
968 -- Bug fix 2124595 start 2
969 /* IF (to_char(p_start_date,'YYYY') = to_char(p_end_date,'YYYY')) THEN
970 IF (to_char(p_transfer_date,'YYYY')=to_char(p_start_date,'YYYY')) THEN */
971
972 IF (l_sdate_fiscal_year = l_edate_fiscal_year) THEN
973 IF (l_sdate_fiscal_year = l_tdate_fiscal_year) THEN
974 -- Bug fix 2124595 end 2
975 IF (p_transfer_date >= p_start_date) THEN
976 IF (p_target_date >= p_transfer_date) THEN
977 IF (p_end_date < p_target_date ) THEN
978 l_cc_period_open := FND_API.G_FALSE;
979 -- Bug 1634159 Fixed
980 l_cc_period_open := IGC_CC_MPFS_PROCESS_PKG.IS_CC_PERIOD_OPEN(p_transfer_date,p_sob_id,p_org_id);
981 IF FND_API.TO_BOOLEAN(l_cc_period_open) THEN
982
983 IF (p_sbc_on) THEN
984 l_gl_period_open := IGC_CC_MPFS_PROCESS_PKG.IS_GL_PERIOD_OPEN(p_transfer_date,p_sob_id);
985 IF FND_API.TO_BOOLEAN(l_gl_period_open )THEN
986 l_validated := FND_API.G_TRUE;
987 ELSE
988 p_exception := NULL;
989 FND_MESSAGE.SET_NAME('IGC','IGC_CC_TX_DT_NOT_OPEN_GL_PRD');
990 FND_MESSAGE.SET_TOKEN('TRANSFER_DT',TO_CHAR(p_transfer_date),TRUE);
991 IF(g_excep_level >= g_debug_level) THEN
992 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
993 END IF;
994 p_exception := FND_MESSAGE.GET;
995 END IF;
996
997 ELSE
998 l_validated := FND_API.G_TRUE;
999 END IF;
1000 ELSE
1001 p_exception := NULL;
1002 FND_MESSAGE.SET_NAME('IGC','IGC_CC_TX_DT_NOT_OPEN_CC_PRD');
1003 FND_MESSAGE.SET_TOKEN('TRANSFER_DT',TO_CHAR(p_transfer_date),TRUE);
1004 IF(g_excep_level >= g_debug_level) THEN
1005 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1006 END IF;
1007 p_exception := FND_MESSAGE.GET;
1008 END IF;
1009
1010 ELSE
1011 p_exception := NULL;
1012 FND_MESSAGE.SET_NAME('IGC','IGC_CC_TG_NOT_IN_SELECT_DT');
1013 FND_MESSAGE.SET_TOKEN('TARGET_DATE',TO_CHAR(p_target_date),TRUE);
1014 IF(g_excep_level >= g_debug_level) THEN
1015 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1016 END IF;
1017 p_exception := FND_MESSAGE.GET;
1018 END IF;
1019 ELSE
1020 p_exception := NULL;
1021 FND_MESSAGE.SET_NAME('IGC','IGC_CC_TG_DT_LESS_TX_DT');
1022 FND_MESSAGE.SET_TOKEN('TARGET_DATE',TO_CHAR(p_target_date),TRUE);
1023 FND_MESSAGE.SET_TOKEN('TRANSFER_DATE',TO_CHAR(p_transfer_date),TRUE);
1024 IF(g_excep_level >= g_debug_level) THEN
1025 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1026 END IF;
1027 p_exception := FND_MESSAGE.GET;
1028 END IF;
1029 ELSE
1030 p_exception := NULL;
1031 FND_MESSAGE.SET_NAME('IGC','IGC_CC_TX_DT_LESS_START_DT');
1032 FND_MESSAGE.SET_TOKEN('TRANSFER_DATE',TO_CHAR(p_transfer_date),TRUE);
1033 FND_MESSAGE.SET_TOKEN('START_DATE',TO_CHAR(p_start_date),TRUE);
1034 IF(g_excep_level >= g_debug_level) THEN
1035 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1036 END IF;
1037 p_exception := FND_MESSAGE.GET;
1038 END IF;
1039 ELSE
1040 p_exception := NULL;
1041 FND_MESSAGE.SET_NAME('IGC','IGC_CC_TX_DATE_NOT_IN_FISCAL');
1042 FND_MESSAGE.SET_TOKEN('TRANSFER_DATE',TO_CHAR(p_transfer_date),TRUE);
1043 IF(g_excep_level >= g_debug_level) THEN
1044 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1045 END IF;
1046 p_exception := FND_MESSAGE.GET;
1047 END IF;
1048 ELSE
1049 p_exception := NULL;
1050 FND_MESSAGE.SET_NAME('IGC','IGC_CC_START_DT_END_DT_FISCAL');
1051 FND_MESSAGE.SET_TOKEN('START_DATE',TO_CHAR(p_start_date),TRUE);
1052 FND_MESSAGE.SET_TOKEN('END_DATE',TO_CHAR(p_end_date),TRUE);
1053 IF(g_excep_level >= g_debug_level) THEN
1054 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1055 END IF;
1056 p_exception := FND_MESSAGE.GET;
1057 END IF;
1058 ELSE
1059 p_exception := NULL;
1060 FND_MESSAGE.SET_NAME('IGC','IGC_CC_END_DT_LESS_START_DT');
1061 FND_MESSAGE.SET_TOKEN('START_DATE',TO_CHAR(p_start_date),TRUE);
1062 FND_MESSAGE.SET_TOKEN('END_DATE',TO_CHAR(p_end_date),TRUE);
1063 IF(g_excep_level >= g_debug_level) THEN
1064 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1065 END IF;
1066 p_exception := FND_MESSAGE.GET;
1067 END IF;
1068
1069 IF FND_API.TO_BOOLEAN(l_validated) THEN
1070 p_result := FND_API.G_TRUE;
1071 ELSE
1072 p_result := FND_API.G_FALSE;
1073
1074 END IF;
1075
1076 END VALIDATE_PARAMS; /* End of Validate Params Procedure */
1077
1078
1079 /* This Function returns 'P' if the Payment Forecast Update is successfull for the cc_header_id passed.
1080 If the update is failed then it returns 'F' along with the error_message */
1081
1082
1083 /*==================================================================================
1084 Function MPFS_UPDATE
1085 =================================================================================*/
1086 FUNCTION MPFS_UPDATE (p_cc_header_id IN NUMBER,
1087 p_request_id IN NUMBER,
1088 p_sob_id IN NUMBER,
1089 p_org_id IN NUMBER,
1090 p_start_date IN DATE,
1091 p_end_date IN DATE,
1092 p_target_date IN DATE,
1093 p_transfer_date IN DATE,
1094 l_error_message OUT NOCOPY VARCHAR2
1095 )
1096 RETURN VARCHAR2
1097 IS
1098 l_cc_headers_rec igc_cc_headers%ROWTYPE;
1099 l_cc_acct_lines_rec igc_cc_acct_lines_v%ROWTYPE;
1100 l_cc_pmt_fcst_rec igc_cc_det_pf_v%ROWTYPE;
1101 l_cc_pf_target igc_cc_det_pf_v%ROWTYPE;
1102 l_rel_cc_headers_rec igc_cc_headers%ROWTYPE;
1103 l_action_hist_msg igc_cc_actions.cc_action_notes%TYPE;
1104 l_DUMMY VARCHAR2(1);
1105
1106 -- Cursors c_acct_lines and c_det_pf_lines
1107 -- modified for bug 2876467
1108 -- ccdpf.cc_det_pf_billed_amt changed to ccdpf.cc_det_pf_func_billed_amt
1109 -- Performance Tuning project. Replaced selection from views
1110 -- igc_cc_acct_lines_v with table igc_cc_acct_lines
1111 -- and igc_cc_det_pf_v with igc_cc_det_pf
1112 -- Replaced the following line as well
1113 -- AND (NVL(ccdpf.cc_det_pf_func_amt,0) - NVL(ccdpf.cc_det_pf_func_billed_amt,0) ) > 0
1114 CURSOR c_acct_lines(p_cc_header_id NUMBER)
1115 IS
1116 -- SELECT *
1117 -- FROM igc_cc_acct_lines_v ccal
1118 -- WHERE ccal.cc_header_id = p_cc_header_id
1119 -- AND exists
1120 -- ( Select 'x' FROM igc_cc_det_pf_v ccdpf
1121 -- WHERE ccdpf.cc_acct_line_id = ccal.cc_acct_line_id
1122 -- AND ccdpf.cc_det_pf_date >= p_start_date AND ccdpf.cc_det_pf_date <= p_end_date
1123 -- AND (ccdpf.cc_det_pf_func_amt-ccdpf.cc_det_pf_func_billed_amt) >0);
1124
1125 SELECT ccal.ROWID,
1126 ccal.cc_header_id,
1127 NULL org_id,
1128 NULL cc_type,
1129 NULL cc_type_code,
1130 NULL cc_num,
1131 ccal.cc_acct_line_id,
1132 ccal.cc_acct_line_num,
1133 ccal.cc_acct_desc,
1134 ccal.parent_header_id,
1135 ccal.parent_acct_line_id,
1136 NULL parent_cc_acct_line_num,
1137 NULL cc_budget_acct_desc,
1138 ccal.cc_budget_code_combination_id,
1139 NULL cc_charge_acct_desc,
1140 ccal.cc_charge_code_combination_id,
1141 ccal.cc_acct_entered_amt,
1142 ccal.cc_acct_func_amt,
1143 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_billed_amt,
1144 IGC_CC_COMP_AMT_PKG.COMPUTE_ACCT_FUNC_BILLED_AMT( ccal.cc_acct_line_id) cc_acct_func_billed_amt,
1145 ccal.cc_acct_encmbrnc_amt,
1146 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccal.cc_acct_entered_amt,0) ) - NVL(ccal.cc_acct_encmbrnc_amt,0) ) cc_acct_unencmrd_amt,
1147 ccal.cc_acct_unbilled_amt,
1148 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id, NVL(ccal.cc_acct_entered_amt,0)) cc_acct_comp_func_amt,
1149 NULL project_number ,
1150 ccal.project_id,
1151 NULL task_number,
1152 ccal.task_id,
1153 ccal.expenditure_type,
1154 NULL expenditure_org_name,
1155 ccal.expenditure_org_id,
1156 ccal.expenditure_item_date,
1157 ccal.cc_acct_taxable_flag,
1158 NULL tax_name,
1159 ccal.tax_id,
1160 ccal.cc_acct_encmbrnc_status,
1161 ccal.cc_acct_encmbrnc_date,
1162 ccal.context,
1163 ccal.attribute1,
1164 ccal.attribute2,
1165 ccal.attribute3,
1166 ccal.attribute4,
1167 ccal.attribute5,
1168 ccal.attribute6,
1169 ccal.attribute7,
1170 ccal.attribute8,
1171 ccal.attribute9,
1172 ccal.attribute10,
1173 ccal.attribute11,
1174 ccal.attribute12,
1175 ccal.attribute13,
1176 ccal.attribute14,
1177 ccal.attribute15,
1178 ccal.created_by,
1179 ccal.creation_date,
1180 ccal.last_updated_by,
1181 ccal.last_update_date,
1182 ccal.last_update_login,
1183 ccal.cc_func_withheld_amt,
1184 ccal.cc_ent_withheld_amt,
1185 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id, NVL(ccal.cc_func_withheld_amt,0)) cc_comp_func_whld_amt,
1186 ccal.tax_classif_code -- modified for Ebtax uptake (Bug No-6472296)
1187 FROM igc_cc_acct_lines ccal
1188 WHERE ccal.cc_header_id = p_cc_header_id
1189 AND exists ( SELECT 'x'
1190 FROM igc_cc_det_pf ccdpf
1191 WHERE ccdpf.cc_acct_line_id = ccal.cc_acct_line_id
1192 AND ccdpf.cc_det_pf_date >= p_start_date
1193 AND ccdpf.cc_det_pf_date <= p_end_date
1194 AND (ccdpf.cc_det_pf_func_amt -
1195 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) >0);
1196
1197
1198
1199 CURSOR c_pf_lines(p_cc_acct_line_id NUMBER,p_start_date DATE, p_end_date DATE)
1200 IS
1201
1202 -- Modified the following sql for performance tuning.
1203 -- SELECT *
1204 -- FROM igc_cc_det_pf_v ccdpf
1205 -- WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
1206 -- AND ccdpf.cc_det_pf_date >= p_start_date AND ccdpf.cc_det_pf_date <= p_end_date
1207 -- AND (ccdpf.cc_det_pf_func_amt-ccdpf.cc_det_pf_func_billed_amt) >0;
1208
1209 SELECT ccdpf.ROWID,
1210 ccdpf.cc_det_pf_line_id,
1211 ccdpf.cc_det_pf_line_num,
1212 NULL cc_acct_line_num,
1213 ccdpf.cc_acct_line_id,
1214 NULL parent_det_pf_line_num,
1215 ccdpf.parent_det_pf_line_id,
1216 ccdpf.parent_acct_line_id,
1217 ccdpf.cc_det_pf_entered_amt,
1218 ccdpf.cc_det_pf_func_amt,
1219 ccdpf.cc_det_pf_date,
1220 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
1221 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
1222 ccdpf.cc_det_pf_unbilled_amt,
1223 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
1224 ccdpf.cc_det_pf_encmbrnc_amt,
1225 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
1226 ccdpf.cc_det_pf_encmbrnc_date,
1227 ccdpf.cc_det_pf_encmbrnc_status,
1228 ccdpf.context,
1229 ccdpf.attribute1,
1230 ccdpf.attribute2,
1231 ccdpf.attribute3,
1232 ccdpf.attribute4,
1233 ccdpf.attribute5,
1234 ccdpf.attribute6,
1235 ccdpf.attribute7,
1236 ccdpf.attribute8,
1237 ccdpf.attribute9,
1238 ccdpf.attribute10,
1239 ccdpf.attribute11,
1240 ccdpf.attribute12,
1241 ccdpf.attribute13,
1242 ccdpf.attribute14,
1243 ccdpf.attribute15,
1244 ccdpf.last_update_date,
1245 ccdpf.last_updated_by,
1246 ccdpf.last_update_login,
1247 ccdpf.creation_date,
1248 ccdpf.created_by
1249 FROM igc_cc_det_pf ccdpf
1250 WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
1251 AND ccdpf.cc_det_pf_date >= p_start_date
1252 AND ccdpf.cc_det_pf_date <= p_end_date
1253 AND (ccdpf.cc_det_pf_func_amt-IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) >0;
1254
1255 CURSOR c_pf_amt_shift(p_cc_acct_line_id NUMBER,p_start_date DATE,p_end_date DATE)
1256 IS
1257 -- Performance Tuning, replaced the view igc_cc_det_pf_v
1258 -- igc_cc_det_pf. Also replaced the following 2 lines
1259 -- SELECT sum(ccdpf.cc_det_pf_func_amt-ccdpf.cc_det_pf_func_billed_amt) func_amt_shift, sum(ccdpf.cc_det_pf_entered_amt - ccdpf.cc_det_pf_billed_amt) amt_shift
1260 -- AND (ccdpf.cc_det_pf_func_amt-ccdpf.cc_det_pf_func_billed_amt) >0;
1261
1262
1263 SELECT sum(ccdpf.cc_det_pf_func_amt-
1264 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) func_amt_shift,
1265 sum(ccdpf.cc_det_pf_entered_amt -
1266 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) amt_shift
1267 FROM igc_cc_det_pf ccdpf
1268 WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
1269 AND ccdpf.cc_det_pf_date >= p_start_date
1270 AND ccdpf.cc_det_pf_date <= p_end_date
1271 AND (ccdpf.cc_det_pf_func_amt -
1272 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id)) >0;
1273
1274 -- Replaced view igc_cc_det_pf_v with igc_cc_det_pf
1275 CURSOR c_pf_target (p_cc_acct_line_id NUMBER,p_target_date DATE)
1276 IS
1277 -- SELECT *
1278 -- FROM igc_cc_det_pf_v ccdpf
1279
1280 SELECT ccdpf.ROWID,
1281 ccdpf.cc_det_pf_line_id,
1282 ccdpf.cc_det_pf_line_num,
1283 NULL cc_acct_line_num,
1284 ccdpf.cc_acct_line_id,
1285 NULL parent_det_pf_line_num,
1286 ccdpf.parent_det_pf_line_id,
1287 ccdpf.parent_acct_line_id,
1288 ccdpf.cc_det_pf_entered_amt,
1289 ccdpf.cc_det_pf_func_amt,
1290 ccdpf.cc_det_pf_date,
1291 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_billed_amt,
1292 IGC_CC_COMP_AMT_PKG.COMPUTE_PF_FUNC_BILLED_AMT(ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num, ccdpf.cc_acct_line_id) cc_det_pf_func_billed_amt,
1293 ccdpf.cc_det_pf_unbilled_amt,
1294 IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT(p_cc_header_id,NVL(ccdpf.cc_det_pf_entered_amt,0)) cc_det_pf_comp_func_amt,
1295 ccdpf.cc_det_pf_encmbrnc_amt,
1296 ( IGC_CC_COMP_AMT_PKG.COMPUTE_FUNCTIONAL_AMT ( p_cc_header_id, NVL(ccdpf.cc_det_pf_entered_amt,0) ) - NVL(ccdpf.cc_det_pf_encmbrnc_amt,0) ) cc_det_pf_unencmbrd_amt ,
1297 ccdpf.cc_det_pf_encmbrnc_date,
1298 ccdpf.cc_det_pf_encmbrnc_status,
1299 ccdpf.context,
1300 ccdpf.attribute1,
1301 ccdpf.attribute2,
1302 ccdpf.attribute3,
1303 ccdpf.attribute4,
1304 ccdpf.attribute5,
1305 ccdpf.attribute6,
1306 ccdpf.attribute7,
1307 ccdpf.attribute8,
1308 ccdpf.attribute9,
1309 ccdpf.attribute10,
1310 ccdpf.attribute11,
1311 ccdpf.attribute12,
1312 ccdpf.attribute13,
1313 ccdpf.attribute14,
1314 ccdpf.attribute15,
1315 ccdpf.last_update_date,
1316 ccdpf.last_updated_by,
1317 ccdpf.last_update_login,
1318 ccdpf.creation_date,
1319 ccdpf.created_by
1320 FROM igc_cc_det_pf ccdpf
1321 WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
1322 AND ccdpf.cc_det_pf_date >= p_target_date
1323 AND ccdpf.cc_det_pf_line_num =
1324 (SELECT min(ccdpf1.cc_det_pf_line_num)
1325 FROM igc_cc_det_pf ccdpf1
1326 WHERE ccdpf1.cc_acct_line_id = p_cc_acct_line_id
1327 AND ccdpf1.cc_det_pf_date = /* bug fix 1702768 */
1328 (SELECT MIN(ccdpf2.cc_det_pf_date)
1329 FROM igc_cc_det_pf ccdpf2
1330 WHERE ccdpf2.cc_acct_line_id = p_cc_acct_line_id
1331 AND ccdpf2.cc_det_pf_date >= p_target_date)
1332 );
1333
1334 l_amt_shift_rec c_pf_amt_shift%ROWTYPE;
1335 l_hdr_row_id VARCHAR2(18);
1336 l_hist_hdr_row_id VARCHAR2(18);
1337 l_acct_row_id VARCHAR2(18);
1338 l_hist_acct_row_id VARCHAR2(18);
1339 l_pf_row_id VARCHAR2(18);
1340 l_hist_pf_row_id VARCHAR2(18);
1341 l_action_row_id VARCHAR2(18);
1342 l_new_cc_det_pf_func_amt igc_cc_det_pf.cc_det_pf_func_amt%TYPE;
1343 l_new_cc_acct_func_amt igc_cc_acct_lines.cc_acct_func_amt%TYPE;
1344 l_api_version CONSTANT NUMBER := 1.0;
1345 l_init_msg_list VARCHAR2(1);
1346 l_commit VARCHAR2(1);
1347 l_validation_level NUMBER;
1348 l_return_status VARCHAR2(1);
1349 l_msg_count NUMBER;
1350 l_msg_data VARCHAR2(2000);
1351 G_FLAG VARCHAR2(1);
1352 l_approval_status igc_cc_process_data.old_approval_status%TYPE;
1353 l_Last_Updated_By NUMBER;
1354 l_Last_Update_Login NUMBER;
1355 l_Created_By NUMBER;
1356 l_cc_version_num igc_cc_headers.cc_version_num%TYPE;
1357 l_cc_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
1358 l_full_path VARCHAR2(255);
1359 BEGIN
1360 l_init_msg_list := FND_API.G_FALSE;
1361 l_commit := FND_API.G_FALSE;
1362 l_validation_level := FND_API.G_VALID_LEVEL_FULL;
1363 l_Last_Updated_By := FND_GLOBAL.USER_ID;
1364 l_Last_Update_Login := FND_GLOBAL.LOGIN_ID;
1365 l_Created_By := FND_GLOBAL.USER_ID;
1366
1367 l_full_path := g_path || 'MPFS_UPDATE';
1368
1369 IF (g_debug_mode = 'Y') THEN
1370 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Begin MPFS_UPDATE '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1371 END IF;
1372 SELECT *
1373 INTO l_cc_headers_rec
1374 FROM igc_cc_headers
1375 WHERE cc_header_id = p_cc_header_id
1376 FOR UPDATE NOWAIT;
1377
1378 SELECT old_approval_status
1379 INTO l_approval_status
1380 FROM igc_cc_process_data
1381 WHERE cc_header_id = p_cc_header_id AND
1382 request_id = p_request_id ;
1383
1384 -- Fixed Bug 1633021 Removed the History Inserts for Header and Lines.
1385
1386 OPEN c_acct_lines(l_cc_headers_rec.cc_header_id);
1387 LOOP
1388 FETCH c_acct_lines INTO l_cc_acct_lines_rec;
1389 EXIT WHEN c_acct_lines%NOTFOUND;
1390 IF (g_debug_mode = 'Y') THEN
1391 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Fetch Account Row '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1392 END IF;
1393
1394
1395 OPEN c_pf_amt_shift(l_cc_acct_lines_rec.cc_acct_line_id,p_start_date,p_end_date);
1396 LOOP
1397 FETCH c_pf_amt_shift INTO l_amt_shift_rec;
1398 EXIT WHEN c_pf_amt_shift%NOTFOUND;
1399 IF (g_debug_mode = 'Y') THEN
1400 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Fetch Payment Forecast Row '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1401 END IF;
1402 END LOOP;
1403 CLOSE c_pf_amt_shift;
1404
1405
1406 OPEN c_pf_target (l_cc_acct_lines_rec.cc_acct_line_id,p_target_date);
1407 LOOP
1408 FETCH c_pf_target INTO l_cc_pf_target;
1409 EXIT WHEN c_pf_target%NOTFOUND;
1410
1411 /* Update PF Line History */
1412 l_return_status := FND_API.G_RET_STS_SUCCESS;
1413 IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
1414 l_api_version,
1415 l_init_msg_list,
1416 l_commit,
1417 l_validation_level,
1418 l_return_status,
1419 l_msg_count,
1420 l_msg_data,
1421 l_hist_pf_row_id,
1422 l_cc_pf_target.CC_Det_PF_Line_Id,
1423 l_cc_pf_target.CC_Det_PF_Line_Num,
1424 l_cc_pf_target.CC_Acct_Line_Id,
1425 l_cc_pf_target.Parent_Acct_Line_Id,
1426 l_cc_pf_target.Parent_Det_PF_Line_Id,
1427 l_cc_headers_rec.cc_version_num,
1428 'U',
1429 l_cc_pf_target.CC_Det_PF_Entered_Amt,
1430 l_cc_pf_target.CC_Det_PF_Func_Amt,
1431 l_cc_pf_target.CC_Det_PF_Date,
1432 l_cc_pf_target.CC_Det_PF_Billed_Amt,
1433 l_cc_pf_target.CC_Det_PF_Unbilled_Amt,
1434 l_cc_pf_target.CC_Det_PF_Encmbrnc_Amt,
1435 l_cc_pf_target.CC_Det_PF_Encmbrnc_Date,
1436 l_cc_pf_target.CC_Det_PF_Encmbrnc_Status,
1437 l_cc_pf_target.Last_Update_Date,
1438 l_cc_pf_target.Last_Updated_By,
1439 l_cc_pf_target.Last_Update_Login,
1440 l_cc_pf_target.Creation_Date,
1441 l_cc_pf_target.Created_By,
1442 l_cc_pf_target.Attribute1,
1443 l_cc_pf_target.Attribute2,
1444 l_cc_pf_target.Attribute3,
1445 l_cc_pf_target.Attribute4,
1446 l_cc_pf_target.Attribute5,
1447 l_cc_pf_target.Attribute6,
1448 l_cc_pf_target.Attribute7,
1449 l_cc_pf_target.Attribute8,
1450 l_cc_pf_target.Attribute9,
1451 l_cc_pf_target.Attribute10,
1452 l_cc_pf_target.Attribute11,
1453 l_cc_pf_target.Attribute12,
1454 l_cc_pf_target.Attribute13,
1455 l_cc_pf_target.Attribute14,
1456 l_cc_pf_target.Attribute15,
1457 l_cc_pf_target.Context,
1458 G_FLAG );
1459 IF (g_debug_mode = 'Y') THEN
1460 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Payment Forecast History Insert '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1461 END IF;
1462
1463 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1464 THEN
1465 IF (g_debug_mode = 'Y') THEN
1466 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Insert Payment Forecast History Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1467 END IF;
1468 l_error_message := NULL;
1469 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_HST_INSERT');
1470 IF(g_excep_level >= g_debug_level) THEN
1471 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1472 END IF;
1473 l_error_message := FND_MESSAGE.GET;
1474 RETURN 'F';
1475 END IF;
1476 l_cc_pf_target.cc_det_pf_entered_amt := l_cc_pf_target.cc_det_pf_entered_amt +l_amt_shift_rec.amt_shift;
1477 l_cc_pf_target.cc_det_pf_func_amt := l_cc_pf_target.cc_det_pf_func_amt+l_amt_shift_rec.func_amt_shift;
1478 l_cc_pf_target.cc_det_pf_encmbrnc_amt := l_cc_pf_target.cc_det_pf_entered_amt * NVL(l_cc_headers_rec.CONVERSION_RATE,1);
1479 l_cc_pf_target.cc_det_pf_encmbrnc_date := l_cc_pf_target.cc_det_pf_date;
1480
1481 SELECT rowid
1482 INTO l_pf_row_id
1483 FROM igc_cc_det_pf
1484 WHERE cc_det_pf_line_id = l_cc_pf_target.cc_det_pf_line_id;
1485
1486 IGC_CC_DET_PF_PKG.Update_Row(
1487 l_api_version,
1488 l_init_msg_list,
1489 l_commit,
1490 l_validation_level,
1491 l_return_status,
1492 l_msg_count,
1493 l_msg_data,
1494 l_pf_row_id,
1495 l_cc_pf_target.CC_Det_PF_Line_Id,
1496 l_cc_pf_target.CC_Det_PF_Line_Num,
1497 l_cc_pf_target.CC_Acct_Line_Id,
1498 l_cc_pf_target.Parent_Acct_Line_Id,
1499 l_cc_pf_target.Parent_Det_PF_Line_Id,
1500 l_cc_pf_target.CC_Det_PF_Entered_Amt,
1501 l_cc_pf_target.cc_det_pf_func_amt,
1502 l_cc_pf_target.cc_det_pf_date,
1503 l_cc_pf_target.CC_Det_PF_Billed_Amt,
1504 l_cc_pf_target.CC_Det_PF_Unbilled_Amt,
1505 l_cc_pf_target.cc_det_pf_encmbrnc_amt,
1506 l_cc_pf_target.cc_det_pf_encmbrnc_date,
1507 l_cc_pf_target.CC_Det_PF_Encmbrnc_Status,
1508 l_cc_pf_target.Last_Update_Date,
1509 l_cc_pf_target.Last_Updated_By,
1510 l_cc_pf_target.Last_Update_Login,
1511 l_cc_pf_target.Creation_Date,
1512 l_cc_pf_target.Created_By,
1513 l_cc_pf_target.Attribute1,
1514 l_cc_pf_target.Attribute2,
1515 l_cc_pf_target.Attribute3,
1516 l_cc_pf_target.Attribute4,
1517 l_cc_pf_target.Attribute5,
1518 l_cc_pf_target.Attribute6,
1519 l_cc_pf_target.Attribute7,
1520 l_cc_pf_target.Attribute8,
1521 l_cc_pf_target.Attribute9,
1522 l_cc_pf_target.Attribute10,
1523 l_cc_pf_target.Attribute11,
1524 l_cc_pf_target.Attribute12,
1525 l_cc_pf_target.Attribute13,
1526 l_cc_pf_target.Attribute14,
1527 l_cc_pf_target.Attribute15,
1528 l_cc_pf_target.Context,
1529 G_FLAG );
1530 IF (g_debug_mode = 'Y') THEN
1531 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Payment Forecast Update '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1532 END IF;
1533
1534 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1535 THEN
1536 IF (g_debug_mode = 'Y') THEN
1537 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Update Payment Forecast Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1538 END IF;
1539 l_error_message := NULL;
1540 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_UPDATE');
1541 IF(g_excep_level >= g_debug_level) THEN
1542 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1543 END IF;
1544 l_error_message := FND_MESSAGE.GET;
1545 RETURN 'F';
1546 END IF;
1547
1548 END LOOP;
1549 CLOSE c_pf_target;
1550
1551 OPEN c_pf_lines(l_cc_acct_lines_rec.cc_acct_line_id,p_start_date,p_end_date);
1552 LOOP
1553 FETCH c_pf_lines INTO l_cc_pmt_fcst_rec;
1554 EXIT WHEN c_pf_lines%NOTFOUND;
1555
1556 /* Update PF Line History */
1557 l_return_status := FND_API.G_RET_STS_SUCCESS;
1558 IGC_CC_DET_PF_HISTORY_PKG.Insert_Row(
1559 l_api_version,
1560 l_init_msg_list,
1561 l_commit,
1562 l_validation_level,
1563 l_return_status,
1564 l_msg_count,
1565 l_msg_data,
1566 l_hist_pf_row_id,
1567 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
1568 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
1569 l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
1570 l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
1571 l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
1572 l_cc_headers_rec.cc_version_num,
1573 'U',
1574 l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
1575 l_cc_pmt_fcst_rec.CC_Det_PF_Func_Amt,
1576 l_cc_pmt_fcst_rec.CC_Det_PF_Date,
1577 l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
1578 l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
1579 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Amt,
1580 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Date,
1581 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
1582 l_cc_pmt_fcst_rec.Last_Update_Date,
1583 l_cc_pmt_fcst_rec.Last_Updated_By,
1584 l_cc_pmt_fcst_rec.Last_Update_Login,
1585 l_cc_pmt_fcst_rec.Creation_Date,
1586 l_cc_pmt_fcst_rec.Created_By,
1587 l_cc_pmt_fcst_rec.Attribute1,
1588 l_cc_pmt_fcst_rec.Attribute2,
1589 l_cc_pmt_fcst_rec.Attribute3,
1590 l_cc_pmt_fcst_rec.Attribute4,
1591 l_cc_pmt_fcst_rec.Attribute5,
1592 l_cc_pmt_fcst_rec.Attribute6,
1593 l_cc_pmt_fcst_rec.Attribute7,
1594 l_cc_pmt_fcst_rec.Attribute8,
1595 l_cc_pmt_fcst_rec.Attribute9,
1596 l_cc_pmt_fcst_rec.Attribute10,
1597 l_cc_pmt_fcst_rec.Attribute11,
1598 l_cc_pmt_fcst_rec.Attribute12,
1599 l_cc_pmt_fcst_rec.Attribute13,
1600 l_cc_pmt_fcst_rec.Attribute14,
1601 l_cc_pmt_fcst_rec.Attribute15,
1602 l_cc_pmt_fcst_rec.Context,
1603 G_FLAG );
1604 IF (g_debug_mode = 'Y') THEN
1605 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Insert Payment Forecast history '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1606 END IF;
1607
1608 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1609 THEN
1610 IF (g_debug_mode = 'Y') THEN
1611 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Insert Payment Forecast history Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1612 END IF;
1613 l_error_message := NULL;
1614 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_HST_INSERT');
1615 IF(g_excep_level >= g_debug_level) THEN
1616 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1617 END IF;
1618 l_error_message := FND_MESSAGE.GET;
1619 RETURN 'F';
1620 END IF;
1621
1622 IF (l_cc_pmt_fcst_rec.cc_det_pf_date < p_transfer_date)
1623 THEN
1624
1625 l_cc_pmt_fcst_rec.cc_det_pf_date := p_transfer_date;
1626 l_cc_pmt_fcst_rec.cc_det_pf_entered_amt := l_cc_pmt_fcst_rec.cc_det_pf_billed_amt;
1627 l_cc_pmt_fcst_rec.cc_det_pf_func_amt := l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt;
1628 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_amt:= ( l_cc_pmt_fcst_rec.cc_det_pf_billed_amt* NVL(l_cc_headers_rec.CONVERSION_RATE,1));
1629 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date:= p_transfer_date;
1630
1631 END IF;
1632
1633 IF (l_cc_pmt_fcst_rec.cc_det_pf_date >= p_transfer_date)
1634 THEN
1635 l_cc_pmt_fcst_rec.cc_det_pf_entered_amt := l_cc_pmt_fcst_rec.cc_det_pf_billed_amt;
1636 l_cc_pmt_fcst_rec.cc_det_pf_func_amt := l_cc_pmt_fcst_rec.cc_det_pf_func_billed_amt;
1637 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_amt:= ( l_cc_pmt_fcst_rec.cc_det_pf_billed_amt* NVL(l_cc_headers_rec.CONVERSION_RATE,1));
1638 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date := p_transfer_date;
1639 END IF;
1640
1641 SELECT rowid
1642 INTO l_pf_row_id
1643 FROM igc_cc_det_pf
1644 WHERE cc_det_pf_line_id = l_cc_pmt_fcst_rec.cc_det_pf_line_id;
1645
1646 IGC_CC_DET_PF_PKG.Update_Row(
1647 l_api_version,
1648 l_init_msg_list,
1649 l_commit,
1650 l_validation_level,
1651 l_return_status,
1652 l_msg_count,
1653 l_msg_data,
1654 l_pf_row_id,
1655 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Id,
1656 l_cc_pmt_fcst_rec.CC_Det_PF_Line_Num,
1657 l_cc_pmt_fcst_rec.CC_Acct_Line_Id,
1658 l_cc_pmt_fcst_rec.Parent_Acct_Line_Id,
1659 l_cc_pmt_fcst_rec.Parent_Det_PF_Line_Id,
1660 l_cc_pmt_fcst_rec.CC_Det_PF_Entered_Amt,
1661 l_cc_pmt_fcst_rec.cc_det_pf_func_amt,
1662 l_cc_pmt_fcst_rec.cc_det_pf_date,
1663 l_cc_pmt_fcst_rec.CC_Det_PF_Billed_Amt,
1664 l_cc_pmt_fcst_rec.CC_Det_PF_Unbilled_Amt,
1665 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_amt,
1666 l_cc_pmt_fcst_rec.cc_det_pf_encmbrnc_date,
1667 l_cc_pmt_fcst_rec.CC_Det_PF_Encmbrnc_Status,
1668 l_cc_pmt_fcst_rec.Last_Update_Date,
1669 l_cc_pmt_fcst_rec.Last_Updated_By,
1670 l_cc_pmt_fcst_rec.Last_Update_Login,
1671 l_cc_pmt_fcst_rec.Creation_Date,
1672 l_cc_pmt_fcst_rec.Created_By,
1673 l_cc_pmt_fcst_rec.Attribute1,
1674 l_cc_pmt_fcst_rec.Attribute2,
1675 l_cc_pmt_fcst_rec.Attribute3,
1676 l_cc_pmt_fcst_rec.Attribute4,
1677 l_cc_pmt_fcst_rec.Attribute5,
1678 l_cc_pmt_fcst_rec.Attribute6,
1679 l_cc_pmt_fcst_rec.Attribute7,
1680 l_cc_pmt_fcst_rec.Attribute8,
1681 l_cc_pmt_fcst_rec.Attribute9,
1682 l_cc_pmt_fcst_rec.Attribute10,
1683 l_cc_pmt_fcst_rec.Attribute11,
1684 l_cc_pmt_fcst_rec.Attribute12,
1685 l_cc_pmt_fcst_rec.Attribute13,
1686 l_cc_pmt_fcst_rec.Attribute14,
1687 l_cc_pmt_fcst_rec.Attribute15,
1688 l_cc_pmt_fcst_rec.Context,
1689 G_FLAG );
1690 IF (g_debug_mode = 'Y') THEN
1691 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Update Payment Forecast '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1692 END IF;
1693 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1694 THEN
1695 IF (g_debug_mode = 'Y') THEN
1696 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Update Payment Forecast Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1697 END IF;
1698 l_error_message := NULL;
1699 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_DET_PF_UPDATE');
1700 IF(g_excep_level >= g_debug_level) THEN
1701 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1702 END IF;
1703 l_error_message := FND_MESSAGE.GET;
1704 RETURN 'F';
1705 END IF;
1706
1707 END LOOP;
1708 CLOSE c_pf_lines;
1709
1710 END LOOP;
1711 CLOSE c_acct_lines;
1712 l_cc_apprvl_status := l_approval_status;
1713 l_cc_version_num:= l_cc_headers_rec.CC_VERSION_NUM + 1;
1714
1715 SELECT rowid
1716 INTO l_hdr_row_id
1717 FROM igc_cc_headers
1718 WHERE CC_HEADER_ID = l_cc_headers_rec.cc_header_id;
1719
1720 IGC_CC_HEADERS_PKG.Update_Row(
1721 l_api_version,
1722 l_init_msg_list,
1723 l_commit,
1724 l_validation_level,
1725 l_return_status,
1726 l_msg_count,
1727 l_msg_data,
1728 l_hdr_row_id,
1729 l_cc_headers_rec.CC_HEADER_ID,
1730 l_cc_headers_rec.ORG_ID,
1731 l_cc_headers_rec.CC_TYPE,
1732 l_cc_headers_rec.CC_NUM,
1733 l_cc_version_num,
1734 l_cc_headers_rec.PARENT_HEADER_ID,
1735 l_cc_headers_rec.CC_STATE,
1736 l_cc_headers_rec.CC_CTRL_STATUS,
1737 l_cc_headers_rec.CC_ENCMBRNC_STATUS,
1738 l_cc_apprvl_status,
1739 l_cc_headers_rec.VENDOR_ID,
1740 l_cc_headers_rec.VENDOR_SITE_ID,
1741 l_cc_headers_rec.VENDOR_CONTACT_ID,
1742 l_cc_headers_rec.TERM_ID,
1743 l_cc_headers_rec.LOCATION_ID,
1744 l_cc_headers_rec.SET_OF_BOOKS_ID,
1745 l_cc_headers_rec.CC_ACCT_DATE,
1746 l_cc_headers_rec.CC_DESC,
1747 l_cc_headers_rec.CC_START_DATE,
1748 l_cc_headers_rec.CC_END_DATE,
1749 l_cc_headers_rec.CC_OWNER_USER_ID,
1750 l_cc_headers_rec.CC_PREPARER_USER_ID,
1751 l_cc_headers_rec.CURRENCY_CODE,
1752 l_cc_headers_rec.CONVERSION_TYPE,
1753 l_cc_headers_rec.CONVERSION_DATE,
1754 l_cc_headers_rec.CONVERSION_RATE,
1755 l_cc_headers_rec.LAST_UPDATE_DATE,
1756 l_cc_headers_rec.LAST_UPDATED_BY,
1757 l_cc_headers_rec.LAST_UPDATE_LOGIN,
1758 l_cc_headers_rec.CREATED_BY,
1759 l_cc_headers_rec.CREATION_DATE,
1760 l_cc_headers_rec.CC_CURRENT_USER_ID,
1761 l_cc_headers_rec.WF_ITEM_TYPE,
1762 l_cc_headers_rec.WF_ITEM_KEY,
1763 l_cc_headers_rec.ATTRIBUTE1,
1764 l_cc_headers_rec.ATTRIBUTE2,
1765 l_cc_headers_rec.ATTRIBUTE3,
1766 l_cc_headers_rec.ATTRIBUTE4,
1767 l_cc_headers_rec.ATTRIBUTE5,
1768 l_cc_headers_rec.ATTRIBUTE6,
1769 l_cc_headers_rec.ATTRIBUTE7,
1770 l_cc_headers_rec.ATTRIBUTE8,
1771 l_cc_headers_rec.ATTRIBUTE9,
1772 l_cc_headers_rec.ATTRIBUTE10,
1773 l_cc_headers_rec.ATTRIBUTE11,
1774 l_cc_headers_rec.ATTRIBUTE12,
1775 l_cc_headers_rec.ATTRIBUTE13,
1776 l_cc_headers_rec.ATTRIBUTE14,
1777 l_cc_headers_rec.ATTRIBUTE15,
1778 l_cc_headers_rec.CONTEXT,
1779 l_cc_headers_rec.CC_GUARANTEE_FLAG, -- 2043221
1780 G_FLAG);
1781 IF (g_debug_mode = 'Y') THEN
1782 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Header Update'|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1783 END IF;
1784
1785 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1786 THEN
1787 IF (g_debug_mode = 'Y') THEN
1788 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Header Update Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1789 END IF;
1790 l_error_message := NULL;
1791 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_HEADERS_UPDATE');
1792 IF(g_excep_level >= g_debug_level) THEN
1793 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1794 END IF;
1795 l_error_message := FND_MESSAGE.GET;
1796
1797 RETURN 'F';
1798 END IF;
1799
1800 /* Update Corresponding PO */
1801 -- Fixed the Bug 1632315
1802 IF l_approval_status = 'AP' THEN
1803 BEGIN
1804 -- Performance Tuning. Replaced the select with
1805 -- the one below
1806 -- SELECT 'Y'
1807 -- INTO l_DUMMY
1808 -- FROM po_headers pha1
1809 -- WHERE pha1.po_header_id = (SELECT pha2.po_header_id
1810 -- FROM igc_cc_headers cchd,
1811 -- po_headers pha2
1812 -- WHERE cchd.org_id = p_org_id
1813 -- AND cchd.cc_header_id = l_cc_headers_rec.cc_header_id
1814 -- AND cchd.cc_num = pha2.segment1
1815 -- AND pha2.type_lookup_code = 'STANDARD');
1816 SELECT 'Y'
1817 INTO l_dummy
1818 FROM po_headers_all pha1,
1819 igc_cc_headers cchd
1820 WHERE cchd.org_id = p_org_id
1821 AND cchd.cc_header_id = l_cc_headers_rec.cc_header_id
1822 AND cchd.cc_num = pha1.segment1
1823 AND pha1.type_lookup_code = 'STANDARD'
1824 AND pha1.org_id = p_org_id;
1825
1826 l_return_status := FND_API.G_RET_STS_SUCCESS;
1827 IF (g_debug_mode = 'Y') THEN
1828 Output_Debug (l_full_path, ' IGCCMPSB -- ************ Before Update PO '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1829 END IF;
1830 IGC_CC_PO_INTERFACE_PKG.Convert_CC_TO_PO(1.0,
1831 FND_API.G_FALSE,
1832 FND_API.G_TRUE,
1833 FND_API.G_VALID_LEVEL_NONE,
1834 l_return_status,
1835 l_msg_count,
1836 l_msg_data,
1837 l_cc_headers_rec.cc_header_id);
1838 IF (g_debug_mode = 'Y') THEN
1839 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Update PO '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1840 END IF;
1841 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1842 THEN
1843 IF (g_debug_mode = 'Y') THEN
1844 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Update PO Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1845 END IF;
1846 l_error_message := NULL;
1847 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_PO_UPDATE_FAILED');
1848 IF(g_excep_level >= g_debug_level) THEN
1849 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1850 END IF;
1851 l_error_message := FND_MESSAGE.GET;
1852
1853 RETURN 'F';
1854 END IF;
1855 EXCEPTION
1856 WHEN NO_DATA_FOUND THEN
1857 NULL;
1858 END;
1859
1860 END IF;
1861
1862
1863 l_action_hist_msg := NULL;
1864 -- Fixed Bug 1632552
1865 /*
1866 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_ACT_HIST_MSG');
1867 l_action_hist_msg := FND_MESSAGE.GET;
1868
1869 */
1870 IGC_CC_ACTIONS_PKG.Insert_Row(
1871 l_api_version,
1872 l_init_msg_list,
1873 l_commit,
1874 l_validation_level,
1875 l_return_status,
1876 l_msg_count,
1877 l_msg_data,
1878 l_action_row_id,
1879 l_cc_headers_rec.CC_HEADER_ID,
1880 l_cc_version_num,
1881 'MS',
1882 l_cc_headers_rec.CC_STATE,
1883 l_cc_headers_rec.CC_CTRL_STATUS,
1884 l_cc_apprvl_status,
1885 l_action_hist_msg,
1886 Sysdate,
1887 l_Last_Updated_By,
1888 l_Last_Update_Login,
1889 Sysdate,
1890 l_Created_By);
1891 IF (g_debug_mode = 'Y') THEN
1892 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Insert Action History '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1893 END IF;
1894 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1895 THEN
1896 IF (g_debug_mode = 'Y') THEN
1897 Output_Debug (l_full_path, ' IGCCMPSB -- ************ After Insert Action History Failure '|| to_char(sysdate,'DD-MON-YY:MI:SS') || ' *************************');
1898 END IF;
1899 l_error_message := NULL;
1900 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_ACTION_HST_INSERT');
1901 IF(g_excep_level >= g_debug_level) THEN
1902 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1903 END IF;
1904 l_error_message := FND_MESSAGE.GET;
1905 RETURN 'F';
1906 END IF;
1907
1908 /* Update validation status, in temporary table*/
1909 UPDATE igc_cc_process_data
1910 SET
1911 processed = 'Y'
1912 WHERE request_id = p_request_id AND
1913 cc_header_id = P_cc_header_id ;
1914 RETURN 'P';
1915 EXCEPTION
1916 WHEN OTHERS
1917 THEN
1918 /* IF (g_debug_mode = 'Y') THEN
1919 Output_Debug (l_full_path, ' SQLERRM ' || SQLERRM);
1920 END IF;*/
1921 IF ( g_unexp_level >= g_debug_level ) THEN
1922 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
1923 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
1924 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
1925 FND_LOG.MESSAGE ( g_unexp_level,l_full_path, TRUE);
1926 END IF;
1927 l_error_message := NULL;
1928 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_CC_UPDATE_FAILED');
1929 IF(g_excep_level >= g_debug_level) THEN
1930 FND_LOG.MESSAGE(g_excep_level, l_full_path, FALSE);
1931 END IF;
1932 l_error_message := FND_MESSAGE.GET;
1933 RETURN 'F';
1934 END MPFS_UPDATE;
1935
1936 /* Formatted on 2004/07/15 16:25 (Formatter Plus v4.8.0) */
1937 /*==================================================================================
1938 Procedure MASS_PAYMENT_FORECAST_SHIFT_MAIN
1939 =================================================================================*/
1940
1941 PROCEDURE mpfs_main (
1942 errbuf OUT NOCOPY VARCHAR2,
1943 retcode OUT NOCOPY VARCHAR2,
1944 p_process_phase IN VARCHAR2,
1945 p_owner IN NUMBER,
1946 p_start_date IN VARCHAR2,
1947 p_end_date IN VARCHAR2,
1948 p_transfer_date IN VARCHAR2,
1949 p_target_date IN VARCHAR2,
1950 p_threshold_value IN NUMBER
1951 )
1952 AS
1953 l_request_id1 NUMBER;
1954 l_request_idt_id2 NUMBER;
1955 l_process_type igc_cc_process_data.process_type%TYPE;
1956 l_org_id NUMBER;
1957 l_sob_id NUMBER;
1958 l_start_date DATE;
1959 l_end_date DATE;
1960 l_transfer_date DATE;
1961 l_target_date DATE;
1962 -- 01/03/02, CC enabled in IGI
1963 l_option_name VARCHAR2 (80);
1964 lv_message VARCHAR2 (1000);
1965 /* Bug No : 6341012. MOAC uptake. Local variable SOB_Name added*/
1966 l_sob_name VARCHAR2(30);
1967
1968 -- Performance Tuning project. Replaced selection from views
1969 -- igc_cc_acct_lines_v with table igc_cc_acct_lines
1970 -- and igc_cc_det_pf_v with igc_cc_det_pf
1971 -- Replaced the following 2 lines a well
1972 -- sum (ccdpf.cc_det_pf_func_amt-ccdpf.cc_det_pf_func_billed_amt) tot_unbilled_amt
1973 -- AND (NVL(ccdpf.cc_det_pf_func_amt,0) - NVL(ccdpf.cc_det_pf_func_billed_amt,0) ) > 0
1974 CURSOR c1 (
1975 p_org_id NUMBER,
1976 p_set_of_books_id NUMBER,
1977 p_owner_id NUMBER,
1978 p_start_date DATE,
1979 p_end_date DATE,
1980 p_target_date DATE
1981 )
1982 IS
1983 SELECT cchd.cc_header_id, cchd.cc_apprvl_status,
1984 SUM
1985 ( ccdpf.cc_det_pf_func_amt
1986 - igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
1987 (ccdpf.cc_det_pf_line_id,
1988 ccdpf.cc_det_pf_line_num,
1989 ccdpf.cc_acct_line_id
1990 )
1991 ) tot_unbilled_amt
1992 FROM igc_cc_headers cchd,
1993 igc_cc_acct_lines ccal,
1994 igc_cc_det_pf ccdpf
1995 WHERE cchd.cc_header_id = ccal.cc_header_id
1996 AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
1997 AND cchd.org_id = p_org_id
1998 AND cchd.set_of_books_id = p_set_of_books_id
1999 AND cchd.cc_type = 'S'
2000 AND cchd.cc_state = 'CM'
2001 AND cchd.cc_encmbrnc_status = 'C'
2002 AND cchd.cc_owner_user_id = p_owner_id
2003 AND ccdpf.cc_det_pf_date >= p_start_date
2004 AND ccdpf.cc_det_pf_date <= p_end_date
2005 AND ( NVL (ccdpf.cc_det_pf_func_amt, 0)
2006 - NVL
2007 (igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
2008 (ccdpf.cc_det_pf_line_id,
2009 ccdpf.cc_det_pf_line_num,
2010 ccdpf.cc_acct_line_id
2011 ),
2012 0
2013 )
2014 ) > 0
2015 GROUP BY cchd.cc_header_id, cchd.cc_apprvl_status;
2016
2017 -- Performance Tuning project. Replaced selection from views
2018 -- igc_cc_acct_lines_v with table igc_cc_acct_lines
2019 -- and igc_cc_det_pf_v with igc_cc_det_pf
2020 -- Replaced the following 2 lines a well
2021 -- sum (ccdpf.cc_det_pf_func_amt-ccdpf.cc_det_pf_func_billed_amt) tot_unbilled_amt
2022 -- AND (NVL(ccdpf.cc_det_pf_func_amt,0) - NVL(ccdpf.cc_det_pf_func_billed_amt,0) ) > 0
2023 CURSOR c2 (
2024 p_org_id NUMBER,
2025 p_set_of_books_id NUMBER,
2026 p_start_date DATE,
2027 p_end_date DATE,
2028 p_target_date DATE
2029 )
2030 IS
2031 SELECT cchd.cc_header_id, cchd.cc_apprvl_status,
2032 SUM
2033 ( ccdpf.cc_det_pf_func_amt
2034 - igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
2035 (ccdpf.cc_det_pf_line_id,
2036 ccdpf.cc_det_pf_line_num,
2037 ccdpf.cc_acct_line_id
2038 )
2039 ) tot_unbilled_amt
2040 FROM igc_cc_headers cchd,
2041 igc_cc_acct_lines ccal,
2042 igc_cc_det_pf ccdpf
2043 WHERE cchd.cc_header_id = ccal.cc_header_id
2044 AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
2045 AND cchd.org_id = p_org_id
2046 AND cchd.set_of_books_id = p_set_of_books_id
2047 AND cchd.cc_type = 'S'
2048 AND cchd.cc_state = 'CM'
2049 AND cchd.cc_encmbrnc_status = 'C'
2050 AND ccdpf.cc_det_pf_date >= p_start_date
2051 AND ccdpf.cc_det_pf_date <= p_end_date
2052 AND ( NVL (ccdpf.cc_det_pf_func_amt, 0)
2053 - NVL
2054 (igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
2055 (ccdpf.cc_det_pf_line_id,
2056 ccdpf.cc_det_pf_line_num,
2057 ccdpf.cc_acct_line_id
2058 ),
2059 0
2060 )
2061 ) > 0
2062 GROUP BY cchd.cc_header_id, cchd.cc_apprvl_status;
2063
2064 CURSOR c3
2065 IS
2066 SELECT *
2067 FROM igc_cc_process_data ccpd
2068 WHERE ccpd.set_of_books_id = l_sob_id
2069 AND ccpd.request_id = l_request_id1
2070 AND ccpd.org_id = l_org_id
2071 AND ccpd.process_type = l_process_type
2072 AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL);
2073
2074 -- Performance Tuning project. Replaced selection from views
2075 -- igc_cc_acct_lines_v with table igc_cc_acct_lines
2076 -- and igc_cc_det_pf_v with igc_cc_det_pf
2077 -- Replaced the following line as well
2078 -- AND (NVL(ccdpf.cc_det_pf_func_amt,0) - NVL(ccdpf.cc_det_pf_func_billed_amt,0) ) > 0
2079 CURSOR c4 (
2080 p_org_id NUMBER,
2081 p_sob_id NUMBER,
2082 p_request_id1 NUMBER,
2083 p_process_type VARCHAR2,
2084 p_start_date DATE,
2085 p_end_date DATE,
2086 p_transfer_date DATE
2087 )
2088 IS
2089 SELECT cchd.cc_header_id, cchd.cc_num, ccdpf.cc_acct_line_id,
2090 ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_date
2091 FROM igc_cc_headers cchd,
2092 igc_cc_acct_lines ccal,
2093 igc_cc_det_pf ccdpf,
2094 igc_cc_process_data ccpd
2095 WHERE cchd.cc_header_id = ccpd.cc_header_id
2096 AND cchd.cc_header_id = ccal.cc_header_id
2097 AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
2098 AND cchd.org_id = l_org_id
2099 AND cchd.set_of_books_id = l_sob_id
2100 AND ccpd.request_id = p_request_id1
2101 AND ccpd.process_type = p_process_type
2102 AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL)
2103 AND ccdpf.cc_det_pf_date >= p_start_date
2104 AND ccdpf.cc_det_pf_date <= p_end_date
2105 AND ( ccdpf.cc_det_pf_func_amt
2106 - igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
2107 (ccdpf.cc_det_pf_line_id,
2108 ccdpf.cc_det_pf_line_num,
2109 ccdpf.cc_acct_line_id
2110 )
2111 ) > 0;
2112
2113
2114 CURSOR c4_1 (
2115 p_org_id NUMBER,
2116 p_sob_id NUMBER,
2117 p_request_id1 NUMBER,
2118 p_process_type VARCHAR2
2119 )
2120 IS
2121 SELECT cchd.cc_header_id, cchd.cc_num
2122 FROM igc_cc_headers cchd,
2123 igc_cc_process_data ccpd
2124 WHERE cchd.cc_header_id = ccpd.cc_header_id
2125 AND cchd.org_id = l_org_id
2126 AND cchd.set_of_books_id = l_sob_id
2127 AND ccpd.request_id = p_request_id1
2128 AND ccpd.process_type = p_process_type
2129 AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL);
2130
2131 CURSOR c4_2 (
2132 p_cc_header_id NUMBER,
2133 p_start_date DATE,
2134 p_end_date DATE,
2135 p_transfer_date DATE
2136 )
2137 IS
2138 SELECT ccdpf.cc_acct_line_id, ccdpf.cc_det_pf_line_id,
2139 ccdpf.cc_det_pf_date
2140 FROM igc_cc_acct_lines ccal,
2141 igc_cc_det_pf ccdpf
2142 WHERE ccal.cc_header_id = p_cc_header_id
2143 AND ccal.cc_acct_line_id = ccdpf.cc_acct_line_id
2144 AND ccdpf.cc_det_pf_date >= p_start_date
2145 AND ccdpf.cc_det_pf_date <= p_end_date
2146 AND ( ccdpf.cc_det_pf_func_amt
2147 - igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
2148 (ccdpf.cc_det_pf_line_id,
2149 ccdpf.cc_det_pf_line_num,
2150 ccdpf.cc_acct_line_id
2151 )
2152 ) > 0;
2153
2154
2155 CURSOR c5
2156 IS
2157 SELECT *
2158 FROM igc_cc_process_data ccpd
2159 WHERE ccpd.request_id = l_request_id1
2160 AND ccpd.org_id = l_org_id
2161 AND ccpd.process_type = l_process_type
2162 AND ccpd.validation_status = 'P'
2163 AND (ccpd.processed <> 'Y' OR ccpd.processed IS NULL);
2164
2165 /* This cursor is not used anywhere. The logic also seems incorrect. Commented it
2166 out so that it does not get picked out in scans for poor sqls.
2167 */
2168 --CURSOR C6 (p_cc_header_id NUMBER,p_target_date DATE,p_org_id NUMBER,p_sob_id NUMBER)
2169 --IS
2170 -- SELECT cchd.cc_header_id
2171 -- FROM igc_cc_headers cchd
2172 -- WHERE cchd.cc_header_id = p_cc_header_id
2173 -- AND cchd.org_id = p_org_id
2174 -- AND cchd.set_of_books_id = p_sob_id
2175 -- AND NOT EXISTS (SELECT 'X'
2176 -- FROM igc_cc_acct_lines_v ccal1
2177 -- WHERE ccal1.cc_header_id = cchd.cc_header_id
2178 -- AND NOT EXISTS (SELECT 'X'
2179 -- FROM igc_cc_det_pf_v ccdpf1
2180 -- WHERE ccdpf1.cc_acct_line_id = ccal1.cc_acct_line_id
2181 -- AND ccdpf1.cc_det_pf_date >= p_target_date )
2182 -- );
2183 CURSOR c7 (
2184 p_cc_acct_line_id NUMBER,
2185 p_target_date DATE,
2186 p_cc_header_id NUMBER
2187 )
2188 IS
2189 -- SELECT *
2190 -- FROM igc_cc_det_pf_v ccdpf
2191 SELECT ccdpf.ROWID, ccdpf.cc_det_pf_line_id, ccdpf.cc_det_pf_line_num,
2192 NULL cc_acct_line_num, ccdpf.cc_acct_line_id,
2193 NULL parent_det_pf_line_num, ccdpf.parent_det_pf_line_id,
2194 ccdpf.parent_acct_line_id, ccdpf.cc_det_pf_entered_amt,
2195 ccdpf.cc_det_pf_func_amt, ccdpf.cc_det_pf_date,
2196 igc_cc_comp_amt_pkg.compute_pf_billed_amt
2197 (ccdpf.cc_det_pf_line_id,
2198 ccdpf.cc_det_pf_line_num,
2199 ccdpf.cc_acct_line_id
2200 ) cc_det_pf_billed_amt,
2201 igc_cc_comp_amt_pkg.compute_pf_func_billed_amt
2202 (ccdpf.cc_det_pf_line_id,
2203 ccdpf.cc_det_pf_line_num,
2204 ccdpf.cc_acct_line_id
2205 ) cc_det_pf_func_billed_amt,
2206 ccdpf.cc_det_pf_unbilled_amt,
2207 igc_cc_comp_amt_pkg.compute_functional_amt
2208 (p_cc_header_id,
2209 NVL (ccdpf.cc_det_pf_entered_amt, 0)
2210 ) cc_det_pf_comp_func_amt,
2211 ccdpf.cc_det_pf_encmbrnc_amt,
2212 ( igc_cc_comp_amt_pkg.compute_functional_amt
2213 (p_cc_header_id,
2214 NVL (ccdpf.cc_det_pf_entered_amt,
2215 0
2216 )
2217 )
2218 - NVL (ccdpf.cc_det_pf_encmbrnc_amt, 0)
2219 ) cc_det_pf_unencmbrd_amt,
2220 ccdpf.cc_det_pf_encmbrnc_date, ccdpf.cc_det_pf_encmbrnc_status,
2221 ccdpf.CONTEXT, ccdpf.attribute1, ccdpf.attribute2,
2222 ccdpf.attribute3, ccdpf.attribute4, ccdpf.attribute5,
2223 ccdpf.attribute6, ccdpf.attribute7, ccdpf.attribute8,
2224 ccdpf.attribute9, ccdpf.attribute10, ccdpf.attribute11,
2225 ccdpf.attribute12, ccdpf.attribute13, ccdpf.attribute14,
2226 ccdpf.attribute15, ccdpf.last_update_date, ccdpf.last_updated_by,
2227 ccdpf.last_update_login, ccdpf.creation_date, ccdpf.created_by
2228 FROM igc_cc_det_pf ccdpf
2229 WHERE ccdpf.cc_acct_line_id = p_cc_acct_line_id
2230 AND ccdpf.cc_det_pf_date >= p_target_date
2231 AND ccdpf.cc_det_pf_line_num =
2232 (SELECT MIN (ccdpf1.cc_det_pf_line_num)
2233 FROM igc_cc_det_pf ccdpf1
2234 WHERE ccdpf1.cc_acct_line_id = p_cc_acct_line_id
2235 AND ccdpf1.cc_det_pf_date = /* bug fix 1702768 */
2236 (SELECT MIN (ccdpf2.cc_det_pf_date)
2237 FROM igc_cc_det_pf ccdpf2
2238 WHERE ccdpf2.cc_acct_line_id = p_cc_acct_line_id
2239 AND ccdpf2.cc_det_pf_date >= p_target_date));
2240
2241 v1 c1%ROWTYPE;
2242 v2 c2%ROWTYPE;
2243 v3 c3%ROWTYPE;
2244 v4 c4%ROWTYPE;
2245 v4_1 c4_1%ROWTYPE;
2246 v4_2 c4_2%ROWTYPE;
2247 v5 c5%ROWTYPE;
2248 --V6 C6%ROWTYPE;
2249 v7 c7%ROWTYPE;
2250 /*************** ENCUMBRANCE CHECK DECLARATION ****************************/
2251 l_currency_code gl_sets_of_books.currency_code%TYPE;
2252 l_sbc_on BOOLEAN;
2253 l_cbc_on BOOLEAN;
2254 l_prov_enc_on BOOLEAN;
2255 l_conf_enc_on BOOLEAN;
2256 l_req_encumbrance_type_id NUMBER;
2257 l_purch_encumbrance_type_id NUMBER;
2258 l_cc_prov_enc_type_id NUMBER;
2259 l_cc_conf_enc_type_id NUMBER;
2260 /************** END ENCUMBRANCE CHECK PARAMS *****************************/
2261 l_debug VARCHAR2 (1);
2262 l_rec_found NUMBER;
2263 l_valid_params VARCHAR2 (1);
2264 l_exception igc_cc_process_exceptions.exception_reason%TYPE;
2265 l_header_id igc_cc_headers.cc_header_id%TYPE;
2266 l_cc_num igc_cc_headers.cc_num%TYPE;
2267 l_lock_cc_status BOOLEAN;
2268 l_lock_po_status BOOLEAN;
2269 l_budg_status BOOLEAN;
2270 l_cc_inprocess VARCHAR2 (1);
2271 l_gl_period_open VARCHAR2 (1);
2272 l_cc_period_open VARCHAR2 (1);
2273 l_validate_cc VARCHAR2 (1);
2274 l_source_pf_cc_prd VARCHAR2 (1);
2275 l_source_pf_gl_prd VARCHAR2 (1);
2276 l_target_pf_found VARCHAR2 (1);
2277 l_tgt_gl_open VARCHAR2 (1);
2278 l_tgt_cc_open VARCHAR2 (1);
2279 l_previous_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
2280 l_dummy VARCHAR2 (1);
2281 l_result_of_reservation igc_cc_process_data.reservation_status%TYPE;
2282 l_result_mpfs_update VARCHAR2 (1);
2283 l_return_status VARCHAR2 (1);
2284 l_msg_count NUMBER;
2285 l_msg_data VARCHAR2 (12000);
2286 l_error_text VARCHAR2 (12000);
2287 l_usr_msg igc_cc_process_exceptions.exception_reason%TYPE;
2288 l_err_mesg igc_cc_process_exceptions.exception_reason%TYPE;
2289 l_fail VARCHAR2 (1);
2290 l_full_path VARCHAR2 (255);
2291 ------Variables related to XML Report
2292 l_terr VARCHAR2(10):='US';
2293 l_lang VARCHAR2(10):='en';
2294 l_layout BOOLEAN;
2295 BEGIN
2296 l_full_path := g_path || 'MPFS_MAIN';
2297 l_request_id1 := fnd_global.conc_request_id;
2298 l_process_type := 'M';
2299 l_rec_found := 1;
2300 l_valid_params := fnd_api.g_false;
2301 l_cc_inprocess := fnd_api.g_false;
2302 l_gl_period_open := fnd_api.g_false;
2303 l_cc_period_open := fnd_api.g_false;
2304 l_validate_cc := fnd_api.g_true;
2305 l_source_pf_cc_prd := fnd_api.g_true;
2306 l_source_pf_gl_prd := fnd_api.g_true;
2307 l_target_pf_found := fnd_api.g_false;
2308 l_tgt_gl_open := fnd_api.g_false;
2309 l_tgt_cc_open := fnd_api.g_false;
2310 l_result_of_reservation := 'F';
2311 l_result_mpfs_update := 'F';
2312 l_fail := fnd_api.g_false;
2313
2314 -- 01/03/02, check to see if CC is installed
2315 IF NOT igi_gen.is_req_installed ('CC')
2316 THEN
2317 SELECT meaning
2318 INTO l_option_name
2319 FROM igi_lookups
2320 WHERE lookup_code = 'CC' AND lookup_type = 'GCC_DESCRIPTION';
2321
2322 fnd_message.set_name ('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
2323 fnd_message.set_token ('OPTION_NAME', l_option_name);
2324
2325 IF (g_error_level >= g_debug_level)
2326 THEN
2327 fnd_log.MESSAGE (g_error_level, l_full_path, FALSE);
2328 END IF;
2329
2330 lv_message := fnd_message.get;
2331 errbuf := lv_message;
2332 retcode := 2;
2333 RETURN;
2334 END IF;
2335
2336 --
2337 -- Setup debug information based upon profile setup options.
2338 --
2339 -- l_debug := FND_PROFILE.VALUE('IGC_DEBUG_ENABLED');
2340 -- IF (l_debug = 'Y') THEN
2341 IF (g_debug_mode = 'Y')
2342 THEN
2343 l_debug := fnd_api.g_true;
2344 ELSE
2345 l_debug := fnd_api.g_false;
2346 END IF;
2347
2348 -- IGC_MSGS_PKG.g_debug_mode := FND_API.TO_BOOLEAN(l_debug);
2349 IF (g_debug_mode = 'Y')
2350 THEN
2351 output_debug
2352 (l_full_path,
2353 ' IGCCMPSB -- ************ Starting Mass Payment Forecast Shift CC '
2354 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2355 || ' *************************'
2356 );
2357 END IF;
2358
2359 -- Bug 1914745, clear any old records from the igc_cc_interface table
2360 DELETE FROM igc_cc_interface
2361 WHERE TO_DATE (creation_date, 'DD/MM/YYYY') <=
2362 TO_DATE (SYSDATE, 'DD/MM/YYYY')
2363 - 2;
2364
2365 retcode := 0;
2366 l_start_date := TRUNC (TO_DATE (p_start_date, 'YYYY/MM/DD HH24:MI:SS'));
2367 l_end_date := TRUNC (TO_DATE (p_end_date, 'YYYY/MM/DD HH24:MI:SS'));
2368 l_transfer_date :=
2369 TRUNC (TO_DATE (p_transfer_date, 'YYYY/MM/DD HH24:MI:SS'));
2370 l_target_date := TRUNC (TO_DATE (p_target_date, 'YYYY/MM/DD HH24:MI:SS'));
2371
2372 /* Bug No : 6341012. MOAC uptake. ORG_ID,SOB_ID are retrieved from packages rather than from profiles*/
2373 -- l_org_id := TO_NUMBER (fnd_profile.VALUE ('ORG_ID'));
2374 -- l_sob_id := TO_NUMBER (fnd_profile.VALUE ('GL_SET_OF_BKS_ID'));
2375 l_org_id := MO_GLOBAL.get_current_org_id;
2376 MO_UTILS.get_ledger_info(l_org_id,l_sob_id,l_sob_name);
2377
2378
2379 /* Begin bug fix 1576023 */
2380 /* This procedure checks the Encumbrance setup for Standard and Commitment Budgets */
2381 l_msg_data := NULL;
2382 l_msg_count := 0;
2383 l_usr_msg := NULL;
2384
2385 IF (g_debug_mode = 'Y')
2386 THEN
2387 output_debug
2388 (l_full_path,
2389 ' IGCCMPSB -- ************ Before getting budgetray ctrl info '
2390 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2391 || ' *************************'
2392 );
2393 END IF;
2394
2395 l_budg_status :=
2396 igc_cc_rep_yep_pvt.get_budg_ctrl_params (l_sob_id,
2397 l_org_id,
2398 l_currency_code,
2399 l_sbc_on,
2400 l_cbc_on,
2401 l_prov_enc_on,
2402 l_conf_enc_on,
2403 /*Bug No : 6341012. SLA Uptake. Encumbrance Type ID are not required */
2404 -- l_req_encumbrance_type_id,
2405 -- l_purch_encumbrance_type_id,
2406 -- l_cc_prov_enc_type_id,
2407 -- l_cc_conf_enc_type_id ,
2408 l_msg_data,
2409 l_msg_count,
2410 l_usr_msg
2411 );
2412
2413
2414 IF (g_debug_mode = 'Y')
2415 THEN
2416 output_debug
2417 (l_full_path,
2418 ' IGCCMPSB -- ************ After getting budgetray ctrl info '
2419 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2420 || ' *************************'
2421 );
2422 END IF;
2423
2424 IF (l_budg_status = FALSE) AND (l_usr_msg IS NOT NULL)
2425 THEN
2426 INSERT INTO igc_cc_process_exceptions
2427 (process_type, process_phase, cc_header_id,
2428 cc_acct_line_id, cc_det_pf_line_id, exception_reason,
2429 org_id, set_of_books_id, request_id
2430 )
2431 VALUES (l_process_type, p_process_phase, NULL,
2432 NULL, NULL, l_usr_msg,
2433 l_org_id, l_sob_id, l_request_id1
2434 );
2435
2436 COMMIT;
2437 /* Concurrent Program Request Id for generating Report */
2438 /*Bug No : 6341012. MOAC Uptake. Set ORG_ID before submitting request */
2439
2440 Fnd_request.set_org_id(l_org_id);
2441
2442 l_request_idt_id2 :=
2443 fnd_request.submit_request ('IGC',
2444 'IGCCMPPR',
2445 NULL,
2446 NULL,
2447 FALSE,
2448 l_sob_id,
2449 l_org_id,
2450 p_process_phase,
2451 'M',
2452 l_request_id1
2453 );
2454 /* End of Concurrent Program Request Id for generating Report */
2455
2456
2457 ---------------------------
2458 ---Run XML Report
2459 ---------------------------
2460 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2461 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2462 l_lang,
2463 l_terr,
2464 'IGCCMPPR_XML',
2465 'IGC',
2466 'IGCCMPPR_XML' );
2467 l_layout := FND_REQUEST.ADD_LAYOUT(
2468 'IGC',
2469 'IGCCMPPR_XML',
2470 l_lang,
2471 l_terr,
2472 'RTF');
2473 IF l_layout then
2474 Fnd_request.set_org_id(l_org_id);
2475 l_request_idt_id2 :=
2476 fnd_request.submit_request (
2477 'IGC',
2478 'IGCCMPPR_XML',
2479 NULL,
2480 NULL,
2481 FALSE,
2482 l_sob_id,
2483 l_org_id,
2484 p_process_phase,
2485 'M',
2486 l_request_id1
2487 );
2488 END IF;
2489 END IF;
2490 ---------------------------
2491 ---End Of Run XML Report
2492 ---------------------------
2493
2494 END IF;
2495
2496 -- ------------------------------------------------------------------------------------
2497 -- Ensure that any exceptions raised are output into the log file to be reported to
2498 -- the user if any are present.
2499 -- ------------------------------------------------------------------------------------
2500 IF (l_budg_status = FALSE)
2501 THEN
2502 fnd_msg_pub.count_and_get (p_count => l_msg_count,
2503 p_data => l_msg_data);
2504
2505 IF (l_msg_count > 0)
2506 THEN
2507 l_error_text := '';
2508
2509 FOR l_cur IN 1 .. l_msg_count
2510 LOOP
2511 --l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2512 l_error_text :=
2513 l_cur || ' ' || fnd_msg_pub.get (l_cur, fnd_api.g_false);
2514 fnd_file.put_line (fnd_file.LOG, l_error_text);
2515
2516 IF (g_excep_level >= g_debug_level)
2517 THEN
2518 fnd_log.STRING (g_excep_level, l_full_path, l_error_text);
2519 END IF;
2520 END LOOP;
2521 END IF;
2522 END IF;
2523
2524 IF (l_usr_msg IS NULL) AND (l_budg_status = FALSE)
2525 THEN
2526 retcode := 2;
2527 END IF;
2528
2529 IF (l_budg_status = FALSE)
2530 THEN
2531 RETURN;
2532 END IF;
2533
2534 /* End bug fix 1576023 */
2535 /*Perform the clean-up operation of IGC_CC_PROCESS_DATA and IGC_CC_PROCESS_EXCEPTIONS */
2536 SAVEPOINT s1; /* Flag 1 Marked for the Complete Roll back */
2537
2538 /* Deletes from IGC_CC_PROCESS_DATA with PHASE - Preliminary and TYPE - MPFS */
2539 DELETE FROM igc_cc_process_data a
2540 WHERE a.process_type = l_process_type
2541 AND a.process_phase = 'P'
2542 AND a.org_id = l_org_id
2543 AND a.set_of_books_id = l_sob_id;
2544
2545 /* Deletes from IGC_CC_PROCESS_EXCEPTIONS with Type - MPFS */
2546 DELETE FROM igc_cc_process_exceptions b
2547 WHERE b.process_type = l_process_type
2548 AND b.org_id = l_org_id
2549 AND b.set_of_books_id = l_sob_id;
2550
2551 IF p_process_phase = 'F'
2552 THEN
2553 /* Deletes Unprocessed lines for Final Phase */
2554 DELETE FROM igc_cc_process_data a
2555 WHERE a.process_type = l_process_type
2556 AND a.process_phase IN ('F', 'P')
2557 AND (a.processed <> 'Y' OR a.processed IS NULL)
2558 AND a.org_id = l_org_id
2559 AND a.set_of_books_id = l_sob_id;
2560 /* Updates with new Request ID to those which were processed */
2561 /*
2562 UPDATE IGC_CC_PROCESS_DATA A
2563 SET REQUEST_ID = l_request_id1
2564 WHERE A.PROCESS_TYPE = l_process_type
2565 AND A.PROCESS_PHASE = 'F'
2566 AND A.PROCESSED = 'Y'
2567 AND A.ORG_ID = l_org_id
2568 AND A.SET_OF_BOOKS_ID = l_sob_id;
2569 */
2570 END IF;
2571
2572 /* This function checks the validity of the user entered parameters and raises exception which
2573 will end the Process, If successful then continue with the process. */
2574 IF (g_debug_mode = 'Y')
2575 THEN
2576 output_debug
2577 (l_full_path,
2578 ' IGCCMPSB -- ************ Before validating parameters '
2579 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2580 || ' *************************'
2581 );
2582 END IF;
2583
2584 igc_cc_mpfs_process_pkg.validate_params (p_process_phase,
2585 l_start_date,
2586 l_end_date,
2587 l_transfer_date,
2588 l_target_date,
2589 l_sob_id,
2590 l_org_id,
2591 l_sbc_on,
2592 l_valid_params,
2593 l_exception
2594 );
2595
2596 IF (g_debug_mode = 'Y')
2597 THEN
2598 output_debug
2599 (l_full_path,
2600 ' IGCCMPSB -- ************ After validating parameters '
2601 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2602 || ' *************************'
2603 );
2604 END IF;
2605
2606 IF fnd_api.to_boolean (l_valid_params)
2607 THEN
2608 /* Select the contracts based on the parameters */
2609 /* Selection and Filteration Phase Starts */
2610 l_exception := NULL;
2611
2612 IF p_owner IS NOT NULL
2613 THEN
2614 OPEN c1 (l_org_id,
2615 l_sob_id,
2616 p_owner,
2617 l_start_date,
2618 l_end_date,
2619 l_target_date
2620 );
2621
2622 LOOP
2623 FETCH c1
2624 INTO v1;
2625
2626 IF c1%ROWCOUNT = 0
2627 THEN
2628 l_exception := NULL;
2629 fnd_message.set_name ('IGC', 'IGC_CC_NO_RECORD_FOUND');
2630
2631 IF (g_error_level >= g_debug_level)
2632 THEN
2633 fnd_log.MESSAGE (g_error_level, l_full_path, FALSE);
2634 END IF;
2635
2636 l_exception := fnd_message.get;
2637
2638 INSERT INTO igc_cc_process_exceptions
2639 (process_type, process_phase, cc_header_id,
2640 cc_acct_line_id, cc_det_pf_line_id,
2641 exception_reason, org_id, set_of_books_id,
2642 request_id
2643 )
2644 VALUES (l_process_type, p_process_phase, NULL,
2645 NULL, NULL,
2646 l_exception, l_org_id, l_sob_id,
2647 l_request_id1
2648 );
2649
2650 l_fail := fnd_api.g_true;
2651 END IF;
2652
2653 EXIT WHEN c1%NOTFOUND;
2654
2655 IF (v1.tot_unbilled_amt <= p_threshold_value)
2656 AND p_process_phase = 'F'
2657 THEN
2658 BEGIN
2659 SELECT cc_header_id
2660 INTO l_header_id
2661 FROM igc_cc_process_data ccpd
2662 WHERE ccpd.cc_header_id = v1.cc_header_id
2663 AND ccpd.request_id = l_request_id1;
2664
2665 UPDATE igc_cc_process_data ccpd
2666 SET old_approval_status = v1.cc_apprvl_status
2667 WHERE ccpd.cc_header_id = v1.cc_header_id
2668 AND ccpd.org_id = l_org_id
2669 AND ccpd.set_of_books_id = l_sob_id;
2670 EXCEPTION
2671 WHEN NO_DATA_FOUND
2672 THEN
2673 INSERT INTO igc_cc_process_data
2674 (process_type, process_phase,
2675 cc_header_id, validation_status,
2676 reservation_status, processed,
2677 old_approval_status, org_id,
2678 set_of_books_id, validate_only, request_id
2679 )
2680 VALUES (l_process_type, p_process_phase,
2681 v1.cc_header_id, 'I',
2682 'F', 'N',
2683 v1.cc_apprvl_status, l_org_id,
2684 l_sob_id, NULL, l_request_id1
2685 );
2686 END;
2687 END IF;
2688
2689 IF (v1.tot_unbilled_amt <= p_threshold_value)
2690 AND p_process_phase = 'P'
2691 THEN
2692 INSERT INTO igc_cc_process_data
2693 (process_type, process_phase,
2694 cc_header_id, validation_status,
2695 reservation_status, processed,
2696 old_approval_status, org_id, set_of_books_id,
2697 validate_only, request_id
2698 )
2699 VALUES (l_process_type, p_process_phase,
2700 v1.cc_header_id, 'I',
2701 'F', 'N',
2702 v1.cc_apprvl_status, l_org_id, l_sob_id,
2703 NULL, l_request_id1
2704 );
2705 END IF;
2706 END LOOP;
2707
2708 CLOSE c1;
2709 ELSE
2710 OPEN c2 (l_org_id, l_sob_id, l_start_date, l_end_date,
2711 l_target_date);
2712
2713 IF (g_debug_mode = 'Y')
2714 THEN
2715 output_debug (l_full_path,
2716 ' IGCCMPSB -- ************ Open C2 '
2717 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2718 || ' *************************'
2719 );
2720 END IF;
2721
2722 LOOP
2723 FETCH c2
2724 INTO v2;
2725
2726 IF (g_debug_mode = 'Y')
2727 THEN
2728 output_debug (l_full_path,
2729 ' IGCCMPSB -- ************ After fetch C2 '
2730 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2731 || ' *************************'
2732 );
2733 END IF;
2734
2735 IF c2%ROWCOUNT = 0
2736 THEN
2737 IF (g_debug_mode = 'Y')
2738 THEN
2739 output_debug
2740 (l_full_path,
2741 ' IGCCMPSB -- ************ Zero Rows Selected '
2742 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2743 || ' *************************'
2744 );
2745 END IF;
2746
2747 l_exception := NULL;
2748 fnd_message.set_name ('IGC', 'IGC_CC_NO_RECORD_FOUND');
2749
2750 IF (g_excep_level >= g_debug_level)
2751 THEN
2752 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
2753 END IF;
2754
2755 l_exception := fnd_message.get;
2756
2757 INSERT INTO igc_cc_process_exceptions
2758 (process_type, process_phase, cc_header_id,
2759 cc_acct_line_id, cc_det_pf_line_id,
2760 exception_reason, org_id, set_of_books_id,
2761 request_id
2762 )
2763 VALUES (l_process_type, p_process_phase, NULL,
2764 NULL, NULL,
2765 l_exception, l_org_id, l_sob_id,
2766 l_request_id1
2767 );
2768
2769 l_fail := fnd_api.g_true;
2770 END IF;
2771
2772 EXIT WHEN c2%NOTFOUND;
2773
2774 IF (v2.tot_unbilled_amt <= p_threshold_value)
2775 AND p_process_phase = 'F'
2776 THEN
2777 BEGIN
2778 SELECT cc_header_id
2779 INTO l_header_id
2780 FROM igc_cc_process_data ccpd
2781 WHERE ccpd.cc_header_id = v2.cc_header_id
2782 AND ccpd.request_id = l_request_id1;
2783
2784 UPDATE igc_cc_process_data ccpd
2785 SET old_approval_status = v2.cc_apprvl_status
2786 WHERE ccpd.cc_header_id = v2.cc_header_id
2787 AND ccpd.org_id = l_org_id
2788 AND ccpd.set_of_books_id = l_sob_id;
2789 EXCEPTION
2790 WHEN NO_DATA_FOUND
2791 THEN
2792 IF (g_debug_mode = 'Y')
2793 THEN
2794 output_debug
2795 (l_full_path,
2796 ' IGCCMPSB -- ************ Insert data into igc_cc_process_data '
2797 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2798 || ' *************************'
2799 );
2800 END IF;
2801
2802 INSERT INTO igc_cc_process_data
2803 (process_type, process_phase,
2804 cc_header_id, validation_status,
2805 reservation_status, processed,
2806 old_approval_status, org_id,
2807 set_of_books_id, validate_only, request_id
2808 )
2809 VALUES (l_process_type, p_process_phase,
2810 v2.cc_header_id, 'I',
2811 'F', 'N',
2812 v2.cc_apprvl_status, l_org_id,
2813 l_sob_id, NULL, l_request_id1
2814 );
2815 END;
2816 END IF;
2817
2818 IF (v2.tot_unbilled_amt <= p_threshold_value)
2819 AND p_process_phase = 'P'
2820 THEN
2821 INSERT INTO igc_cc_process_data
2822 (process_type, process_phase,
2823 cc_header_id, validation_status,
2824 reservation_status, processed,
2825 old_approval_status, org_id, set_of_books_id,
2826 validate_only, request_id
2827 )
2828 VALUES (l_process_type, p_process_phase,
2829 v2.cc_header_id, 'I',
2830 'F', 'N',
2831 v2.cc_apprvl_status, l_org_id, l_sob_id,
2832 NULL, l_request_id1
2833 );
2834 END IF;
2835 END LOOP;
2836
2837 CLOSE c2;
2838 END IF;
2839
2840 /* End of Contract Selection and Filteration */
2841 -- Flag Mark 2
2842 SELECT COUNT (ROWID)
2843 INTO l_rec_found
2844 FROM igc_cc_process_data
2845 WHERE request_id = l_request_id1
2846 AND set_of_books_id = l_sob_id
2847 AND org_id = l_org_id
2848 AND process_type = l_process_type;
2849
2850 IF l_rec_found = 0 AND l_exception IS NULL
2851 THEN
2852 l_fail := fnd_api.g_true;
2853 fnd_message.set_name ('IGC', 'IGC_CC_NO_RECORD_FOUND');
2854
2855 IF (g_excep_level >= g_debug_level)
2856 THEN
2857 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
2858 END IF;
2859
2860 l_exception := fnd_message.get;
2861
2862 INSERT INTO igc_cc_process_exceptions
2863 (process_type, process_phase, cc_header_id,
2864 cc_acct_line_id, cc_det_pf_line_id, exception_reason,
2865 org_id, set_of_books_id, request_id
2866 )
2867 VALUES (l_process_type, p_process_phase, NULL,
2868 NULL, NULL, l_exception,
2869 l_org_id, l_sob_id, l_request_id1
2870 );
2871 END IF;
2872
2873 IF p_process_phase = 'P' AND NOT fnd_api.to_boolean (l_fail)
2874 THEN
2875 OPEN c4 (l_org_id,
2876 l_sob_id,
2877 l_request_id1,
2878 l_process_type,
2879 l_start_date,
2880 l_end_date,
2881 l_transfer_date
2882 );
2883
2884 LOOP
2885 FETCH c4
2886 INTO v4;
2887
2888 EXIT WHEN c4%NOTFOUND;
2889
2890 SELECT cc_apprvl_status
2891 INTO l_previous_apprvl_status
2892 FROM igc_cc_headers cchd
2893 WHERE cchd.cc_header_id = v4.cc_header_id;
2894
2895 -- Bug 1632539 Fixed
2896 IF l_previous_apprvl_status = 'IP'
2897 THEN
2898 l_cc_inprocess := fnd_api.g_true;
2899
2900 BEGIN
2901 SELECT 'X'
2902 INTO l_dummy
2903 FROM igc_cc_process_exceptions
2904 WHERE cc_header_id = v4.cc_header_id
2905 AND cc_acct_line_id IS NULL
2906 AND cc_det_pf_line_id IS NULL
2907 AND org_id = l_org_id
2908 AND process_type = l_process_type
2909 AND process_phase = p_process_phase
2910 AND set_of_books_id = l_sob_id
2911 AND request_id = l_request_id1;
2912 EXCEPTION
2913 WHEN NO_DATA_FOUND
2914 THEN
2915 l_exception := NULL;
2916 fnd_message.set_name ('IGC',
2917 'IGC_CC_MPFS_CC_IN_PROCESS');
2918 fnd_message.set_token ('NUMBER', v4.cc_num, TRUE);
2919 fnd_message.set_token ('PROCESS_TYPE',
2920 l_process_type,
2921 TRUE
2922 );
2923
2924 IF (g_excep_level >= g_debug_level)
2925 THEN
2926 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
2927 END IF;
2928
2929 l_exception := fnd_message.get;
2930
2931 INSERT INTO igc_cc_process_exceptions
2932 (process_type, process_phase,
2933 cc_header_id, cc_acct_line_id,
2934 cc_det_pf_line_id, exception_reason,
2935 org_id, set_of_books_id, request_id
2936 )
2937 VALUES (l_process_type, p_process_phase,
2938 v4.cc_header_id, NULL,
2939 NULL, l_exception,
2940 l_org_id, l_sob_id, l_request_id1
2941 );
2942 END;
2943 ELSE
2944 l_cc_inprocess := fnd_api.g_false;
2945 END IF;
2946
2947 IF (v4.cc_det_pf_date > l_transfer_date)
2948 THEN
2949 -- Bug 1634159 Fixed
2950 l_cc_period_open :=
2951 is_cc_period_open (v4.cc_det_pf_date, l_sob_id, l_org_id);
2952
2953 IF fnd_api.to_boolean (l_cc_period_open)
2954 THEN
2955 l_source_pf_cc_prd := fnd_api.g_true;
2956
2957 IF (g_debug_mode = 'Y')
2958 THEN
2959 output_debug
2960 (l_full_path,
2961 ' IGCCMPSB -- ************ Source CC Period Open '
2962 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2963 || ' *************************'
2964 );
2965 END IF;
2966 ELSE
2967 l_source_pf_cc_prd := fnd_api.g_false;
2968
2969 IF (g_debug_mode = 'Y')
2970 THEN
2971 output_debug
2972 (l_full_path,
2973 ' IGCCMPSB -- ************ Source CC Period Not Open '
2974 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
2975 || ' *************************'
2976 );
2977 END IF;
2978
2979 l_exception := NULL;
2980 fnd_message.set_name ('IGC',
2981 'IGC_CC_SOURCE_PF_DT_NOT_CC_PRD'
2982 );
2983 fnd_message.set_token ('SOURCE_PF_DT',
2984 TO_CHAR (v4.cc_det_pf_date),
2985 TRUE
2986 );
2987
2988 IF (g_excep_level >= g_debug_level)
2989 THEN
2990 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
2991 END IF;
2992
2993 l_exception := fnd_message.get;
2994
2995 INSERT INTO igc_cc_process_exceptions
2996 (process_type, process_phase,
2997 cc_header_id, cc_acct_line_id,
2998 cc_det_pf_line_id, exception_reason, org_id,
2999 set_of_books_id, request_id
3000 )
3001 VALUES (l_process_type, p_process_phase,
3002 v4.cc_header_id, v4.cc_acct_line_id,
3003 v4.cc_det_pf_line_id, l_exception, l_org_id,
3004 l_sob_id, l_request_id1
3005 );
3006 END IF;
3007
3008 IF (l_sbc_on)
3009 THEN
3010 l_gl_period_open :=
3011 is_gl_period_open (v4.cc_det_pf_date, l_sob_id);
3012
3013 IF fnd_api.to_boolean (l_gl_period_open)
3014 THEN
3015 l_source_pf_gl_prd := fnd_api.g_true;
3016
3017 IF (g_debug_mode = 'Y')
3018 THEN
3019 output_debug
3020 (l_full_path,
3021 ' IGCCMPSB -- ************ Source GL Period Open '
3022 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3023 || ' *************************'
3024 );
3025 END IF;
3026 ELSE
3027 l_source_pf_gl_prd := fnd_api.g_false;
3028
3029 IF (g_debug_mode = 'Y')
3030 THEN
3031 output_debug
3032 (l_full_path,
3033 ' IGCCMPSB -- ************ Source GL Period Not Open '
3034 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3035 || ' *************************'
3036 );
3037 END IF;
3038
3039 l_exception := NULL;
3040 fnd_message.set_name ('IGC',
3041 'IGC_CC_SOURCE_PF_DT_NOT_OPEN'
3042 );
3043 fnd_message.set_token ('SOURCE_PF_DT',
3044 TO_CHAR (v4.cc_det_pf_date),
3045 TRUE
3046 );
3047
3048 IF (g_excep_level >= g_debug_level)
3049 THEN
3050 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
3051 END IF;
3052
3053 l_exception := fnd_message.get;
3054
3055 INSERT INTO igc_cc_process_exceptions
3056 (process_type, process_phase,
3057 cc_header_id, cc_acct_line_id,
3058 cc_det_pf_line_id, exception_reason,
3059 org_id, set_of_books_id, request_id
3060 )
3061 VALUES (l_process_type, p_process_phase,
3062 v4.cc_header_id, v4.cc_acct_line_id,
3063 v4.cc_det_pf_line_id, l_exception,
3064 l_org_id, l_sob_id, l_request_id1
3065 );
3066 END IF;
3067 END IF;
3068 END IF;
3069
3070 OPEN c7 (v4.cc_acct_line_id, l_target_date, v4.cc_header_id);
3071
3072 FETCH c7
3073 INTO v7;
3074
3075 IF c7%ROWCOUNT = 0
3076 THEN
3077 l_target_pf_found := fnd_api.g_false;
3078
3079 BEGIN
3080 SELECT 'X'
3081 INTO l_dummy
3082 FROM igc_cc_process_exceptions
3083 WHERE cc_header_id = v4.cc_header_id
3084 AND cc_acct_line_id = v4.cc_acct_line_id
3085 AND cc_det_pf_line_id IS NULL
3086 AND org_id = l_org_id
3087 AND process_type = l_process_type
3088 AND process_phase = p_process_phase
3089 AND set_of_books_id = l_sob_id
3090 AND request_id = l_request_id1;
3091 EXCEPTION
3092 WHEN NO_DATA_FOUND
3093 THEN
3094 l_exception := NULL;
3095 fnd_message.set_name ('IGC',
3096 'IGC_CC_MPFS_TGT_PF_NOT_FOUND'
3097 );
3098 fnd_message.set_token ('CC_NUM', v4.cc_num, TRUE);
3099
3100 IF (g_excep_level >= g_debug_level)
3101 THEN
3102 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
3103 END IF;
3104
3105 l_exception := fnd_message.get;
3106
3107 INSERT INTO igc_cc_process_exceptions
3108 (process_type, process_phase,
3109 cc_header_id, cc_acct_line_id,
3110 cc_det_pf_line_id, exception_reason,
3111 org_id, set_of_books_id, request_id
3112 )
3113 VALUES (l_process_type, p_process_phase,
3114 v4.cc_header_id, v4.cc_acct_line_id,
3115 NULL, l_exception,
3116 l_org_id, l_sob_id, l_request_id1
3117 );
3118 END;
3119 ELSE
3120 -- Bug 1634159 Fixed
3121 l_target_pf_found := fnd_api.g_true;
3122 l_tgt_cc_open :=
3123 is_cc_period_open (v7.cc_det_pf_date, l_sob_id, l_org_id);
3124
3125 IF fnd_api.to_boolean (l_tgt_cc_open)
3126 THEN
3127 l_tgt_cc_open := fnd_api.g_true;
3128 ELSE
3129 l_tgt_cc_open := fnd_api.g_false;
3130
3131 BEGIN
3132 SELECT 'X'
3133 INTO l_dummy
3134 FROM igc_cc_process_exceptions
3135 WHERE cc_header_id = v4.cc_header_id
3136 AND cc_acct_line_id = v4.cc_acct_line_id
3137 AND cc_det_pf_line_id = v7.cc_det_pf_line_id
3138 AND org_id = l_org_id
3139 AND process_type = l_process_type
3140 AND process_phase = p_process_phase
3141 AND set_of_books_id = l_sob_id
3142 AND request_id = l_request_id1;
3143 EXCEPTION
3144 WHEN NO_DATA_FOUND
3145 THEN
3146 l_exception := NULL;
3147 fnd_message.set_name
3148 ('IGC',
3149 'IGC_CC_TARGET_PF_DT_NOT_CC_PRD'
3150 );
3151 fnd_message.set_token ('TARGET_PF_DT',
3152 TO_CHAR (v7.cc_det_pf_date),
3153 TRUE
3154 );
3155
3156 IF (g_excep_level >= g_debug_level)
3157 THEN
3158 fnd_log.MESSAGE (g_excep_level, l_full_path,
3159 FALSE);
3160 END IF;
3161
3162 l_exception := fnd_message.get;
3163
3164 INSERT INTO igc_cc_process_exceptions
3165 (process_type, process_phase,
3166 cc_header_id, cc_acct_line_id,
3167 cc_det_pf_line_id, exception_reason,
3168 org_id, set_of_books_id, request_id
3169 )
3170 VALUES (l_process_type, p_process_phase,
3171 v4.cc_header_id, v4.cc_acct_line_id,
3172 v7.cc_det_pf_line_id, l_exception,
3173 l_org_id, l_sob_id, l_request_id1
3174 );
3175 END;
3176 END IF;
3177
3178 -- Bug 1634218 Fixed
3179 l_tgt_gl_open :=
3180 is_gl_period_open (v7.cc_det_pf_date, l_sob_id);
3181
3182 IF fnd_api.to_boolean (l_tgt_gl_open)
3183 THEN
3184 l_tgt_gl_open := fnd_api.g_true;
3185 ELSE
3186 l_tgt_gl_open := fnd_api.g_false;
3187
3188 BEGIN
3189 SELECT 'X'
3190 INTO l_dummy
3191 FROM igc_cc_process_exceptions
3192 WHERE cc_header_id = v4.cc_header_id
3193 AND cc_acct_line_id = v4.cc_acct_line_id
3194 AND cc_det_pf_line_id = v7.cc_det_pf_line_id
3195 AND org_id = l_org_id
3196 AND process_type = l_process_type
3197 AND process_phase = p_process_phase
3198 AND set_of_books_id = l_sob_id
3199 AND request_id = l_request_id1;
3200 EXCEPTION
3201 WHEN NO_DATA_FOUND
3202 THEN
3203 l_exception := NULL;
3204 fnd_message.set_name
3205 ('IGC',
3206 'IGC_CC_TARGET_PF_DT_NOT_GL_PRD'
3207 );
3208 fnd_message.set_token ('TARGET_PF_DT',
3209 TO_CHAR (v7.cc_det_pf_date),
3210 TRUE
3211 );
3212
3213 IF (g_excep_level >= g_debug_level)
3214 THEN
3215 fnd_log.MESSAGE (g_excep_level, l_full_path,
3216 FALSE);
3217 END IF;
3218
3219 l_exception := fnd_message.get;
3220
3221 INSERT INTO igc_cc_process_exceptions
3222 (process_type, process_phase,
3223 cc_header_id, cc_acct_line_id,
3224 cc_det_pf_line_id, exception_reason,
3225 org_id, set_of_books_id, request_id
3226 )
3227 VALUES (l_process_type, p_process_phase,
3228 v4.cc_header_id, v4.cc_acct_line_id,
3229 v7.cc_det_pf_line_id, l_exception,
3230 l_org_id, l_sob_id, l_request_id1
3231 );
3232 END;
3233 END IF;
3234 END IF;
3235
3236 CLOSE c7;
3237
3238 /*
3239
3240 BEGIN
3241 SELECT 'X'
3242 INTO l_DUMMY
3243 FROM igc_cc_process_exceptions
3244 WHERE cc_header_id = V4.cc_header_id
3245 AND cc_acct_line_id IS NULL
3246 AND org_id = l_org_id
3247 AND process_type = l_process_type
3248 AND process_phase = p_process_phase
3249 AND set_of_books_id = l_sob_id
3250 AND request_id = l_request_id1;
3251
3252 l_target_pf_found := FND_API.G_FALSE;
3253
3254 EXCEPTION
3255 WHEN NO_DATA_FOUND THEN
3256 OPEN C6(V4.cc_header_id,l_target_date,l_org_id,l_sob_id);
3257 FETCH C6 INTO V6;
3258 IF C6%ROWCOUNT=0 THEN
3259 l_target_pf_found := FND_API.G_FALSE;
3260 l_EXCEPTION := NULL;
3261 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_TGT_PF_NOT_FOUND');
3262 FND_MESSAGE.SET_TOKEN('CC_NUM',V4.cc_num,TRUE);
3263 l_EXCEPTION := FND_MESSAGE.GET;
3264 INSERT INTO igc_cc_process_exceptions
3265 (PROCESS_TYPE ,
3266 PROCESS_PHASE ,
3267 CC_HEADER_ID ,
3268 CC_ACCT_LINE_ID ,
3269 CC_DET_PF_LINE_ID ,
3270 EXCEPTION_REASON ,
3271 ORG_ID ,
3272 SET_OF_BOOKS_ID ,
3273 REQUEST_ID )
3274 values (l_process_type,
3275 p_process_phase,
3276 V4.cc_header_id,
3277 NULL,
3278 NULL,
3279 l_EXCEPTION,
3280 l_org_id,
3281 l_sob_id,
3282 l_request_id1);
3283
3284
3285 ELSE
3286 l_target_pf_found := FND_API.G_TRUE;
3287 END IF;
3288
3289 CLOSE C6;
3290 END;
3291 */
3292
3293 /* Changed FND_API.TO_BOOLEAN(l_cc_inprocess) to NOT NOT FND_API.TO_BOOLEAN(l_cc_inprocess) in the following
3294 Statement to fix bug 1689697 */
3295 IF fnd_api.to_boolean (l_source_pf_cc_prd)
3296 AND fnd_api.to_boolean (l_source_pf_gl_prd)
3297 AND fnd_api.to_boolean (l_target_pf_found)
3298 AND NOT fnd_api.to_boolean (l_cc_inprocess)
3299 AND fnd_api.to_boolean (l_tgt_gl_open)
3300 THEN
3301 UPDATE igc_cc_process_data ccpd
3302 SET ccpd.validation_status = 'P'
3303 WHERE ccpd.cc_header_id = v4.cc_header_id
3304 AND ccpd.org_id = l_org_id
3305 AND ccpd.set_of_books_id = l_sob_id
3306 AND ccpd.request_id = l_request_id1
3307 AND ccpd.process_type = l_process_type;
3308 ELSE
3309 l_fail := fnd_api.g_true;
3310
3311 UPDATE igc_cc_process_data ccpd
3312 SET ccpd.validation_status = 'F'
3313 WHERE ccpd.cc_header_id = v4.cc_header_id
3314 AND ccpd.org_id = l_org_id
3315 AND ccpd.set_of_books_id = l_sob_id
3316 AND ccpd.request_id = l_request_id1
3317 AND ccpd.process_type = l_process_type;
3318 END IF;
3319 END LOOP;
3320
3321 CLOSE c4;
3322 END IF;
3323
3324 -- Mark Flag 1;
3325 COMMIT;
3326
3327 IF (g_debug_mode = 'Y')
3328 THEN
3329 output_debug (l_full_path,
3330 ' IGCCMPSB -- ************ Before lock CC and PO '
3331 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3332 || ' *************************'
3333 );
3334 END IF;
3335
3336 /* Locking CC and PO phase starts */
3337 IF p_process_phase = 'F' AND NOT fnd_api.to_boolean (l_fail)
3338 THEN
3339 OPEN c4_1 (l_org_id,
3340 l_sob_id,
3341 l_request_id1,
3342 l_process_type
3343 );
3344
3345 LOOP
3346 IF (g_debug_mode = 'Y')
3347 THEN
3348 output_debug (l_full_path,
3349 ' IGCCMPSB -- ************ Open C4 '
3350 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3351 || ' *************************'
3352 );
3353 END IF;
3354
3355 FETCH c4_1
3356 INTO v4_1;
3357
3358 EXIT WHEN c4_1%NOTFOUND;
3359
3360 SELECT cc_num
3361 INTO l_cc_num
3362 FROM igc_cc_headers
3363 WHERE cc_header_id = v4_1.cc_header_id;
3364
3365 IF (g_debug_mode = 'Y')
3366 THEN
3367 output_debug (l_full_path,
3368 ' IGCCMPSB -- ************ Lock CC '
3369 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3370 || ' *************************'
3371 );
3372 END IF;
3373
3374 l_lock_cc_status := igc_cc_rep_yep_pvt.lock_cc (v4_1.cc_header_id);
3375
3376 IF l_lock_cc_status = FALSE
3377 THEN
3378 l_exception := NULL;
3379 fnd_message.set_name ('IGC', 'IGC_CC_MPFS_CC_LOCKED');
3380 fnd_message.set_token ('CC_NUMBER', l_cc_num, TRUE);
3381
3382 IF (g_excep_level >= g_debug_level)
3383 THEN
3384 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
3385 END IF;
3386
3387 l_exception := fnd_message.get;
3388
3389 IF (g_debug_mode = 'Y')
3390 THEN
3391 output_debug
3392 (l_full_path,
3393 ' IGCCMPSB -- ************ Before Insert lock exception '
3394 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3395 || ' *************************'
3396 );
3397 END IF;
3398
3399 INSERT INTO igc_cc_process_exceptions
3400 (process_type, process_phase, cc_header_id,
3401 cc_acct_line_id, cc_det_pf_line_id,
3402 exception_reason, org_id, set_of_books_id,
3403 request_id
3404 )
3405 VALUES (l_process_type, p_process_phase, v4_1.cc_header_id,
3406 NULL, NULL,
3407 l_exception, l_org_id, l_sob_id,
3408 l_request_id1
3409 );
3410
3411 IF (g_debug_mode = 'Y')
3412 THEN
3413 output_debug
3414 (l_full_path,
3415 ' IGCCMPSB -- ************ After Insert lock exception '
3416 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3417 || ' *************************'
3418 );
3419 END IF;
3420 END IF;
3421
3422 IF (g_debug_mode = 'Y')
3423 THEN
3424 output_debug (l_full_path,
3425 ' IGCCMPSB -- ************ Before PO Lock '
3426 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3427 || ' *************************'
3428 );
3429 END IF;
3430
3431 l_lock_po_status := igc_cc_rep_yep_pvt.lock_po (v4_1.cc_header_id);
3432
3433 IF l_lock_po_status = FALSE
3434 THEN
3435 IF (g_debug_mode = 'Y')
3436 THEN
3437 output_debug
3438 (l_full_path,
3439 ' IGCCMPSB -- ************ Before Insert PO Lock Exception '
3440 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3441 || ' *************************'
3442 );
3443 END IF;
3444
3445 l_exception := NULL;
3446 fnd_message.set_name ('IGC', 'IGC_CC_MPFS_PO_LOCKED');
3447 fnd_message.set_token ('CC_NUMBER', l_cc_num, TRUE);
3448
3449 IF (g_excep_level >= g_debug_level)
3450 THEN
3451 fnd_log.MESSAGE (g_excep_level, l_full_path, FALSE);
3452 END IF;
3453
3454 l_exception := fnd_message.get;
3455
3456 INSERT INTO igc_cc_process_exceptions
3457 (process_type, process_phase, cc_header_id,
3458 cc_acct_line_id, cc_det_pf_line_id,
3459 exception_reason, org_id, set_of_books_id,
3460 request_id
3461 )
3462 VALUES (l_process_type, p_process_phase, v4_1.cc_header_id,
3463 NULL, NULL,
3464 l_exception, l_org_id, l_sob_id,
3465 l_request_id1
3466 );
3467
3468 IF (g_debug_mode = 'Y')
3469 THEN
3470 output_debug
3471 (l_full_path,
3472 ' IGCCMPSB -- ************ After Insert PO Lock Exception '
3473 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3474 || ' *************************'
3475 );
3476 END IF;
3477 END IF;
3478
3479 IF l_lock_po_status = TRUE
3480 AND l_lock_cc_status = TRUE
3481 AND p_process_phase = 'F'
3482 THEN
3483 /*Check for the period open for the date at which the source lines are getting liquidated.*/
3484 /* Validation Phase Starts */
3485 IF (g_debug_mode = 'Y')
3486 THEN
3487 output_debug
3488 (l_full_path,
3489 ' IGCCMPSB -- ************ Update IGC_Process_Data After successful Lock'
3490 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3491 || ' *************************'
3492 );
3493 END IF;
3494
3495 SELECT cc_apprvl_status
3496 INTO l_previous_apprvl_status
3497 FROM igc_cc_headers cchd
3498 WHERE cchd.cc_header_id = v4_1.cc_header_id;
3499
3500 -- Bug 1632539 Fixed
3501 IF l_previous_apprvl_status = 'IP'
3502 THEN
3503 l_cc_inprocess := fnd_api.g_true;
3504
3505 BEGIN
3506 SELECT 'X'
3507 INTO l_dummy
3508 FROM igc_cc_process_exceptions
3509 WHERE cc_header_id = v4_1.cc_header_id
3510 AND cc_acct_line_id IS NULL
3511 AND cc_det_pf_line_id IS NULL
3512 AND org_id = l_org_id
3513 AND process_type = l_process_type
3514 AND process_phase = p_process_phase
3515 AND set_of_books_id = l_sob_id
3516 AND request_id = l_request_id1;
3517 EXCEPTION
3518 WHEN NO_DATA_FOUND
3519 THEN
3520 l_exception := NULL;
3521 fnd_message.set_name ('IGC',
3522 'IGC_CC_MPFS_CC_IN_PROCESS'
3523 );
3524 fnd_message.set_token ('NUMBER', v4_1.cc_num, TRUE);
3525 fnd_message.set_token ('PROCESS_TYPE',
3526 l_process_type,
3527 TRUE
3528 );
3529
3530 IF (g_excep_level >= g_debug_level)
3531 THEN
3532 fnd_log.MESSAGE (g_excep_level, l_full_path,
3533 FALSE);
3534 END IF;
3535
3536 l_exception := fnd_message.get;
3537
3538 INSERT INTO igc_cc_process_exceptions
3539 (process_type, process_phase,
3540 cc_header_id, cc_acct_line_id,
3541 cc_det_pf_line_id, exception_reason,
3542 org_id, set_of_books_id, request_id
3543 )
3544 VALUES (l_process_type, p_process_phase,
3545 v4_1.cc_header_id, NULL,
3546 NULL, l_exception,
3547 l_org_id, l_sob_id, l_request_id1
3548 );
3549 END;
3550 ELSE
3551 l_cc_inprocess := fnd_api.g_false;
3552 END IF;
3553
3554
3555 IF NOT fnd_api.to_boolean (l_cc_inprocess) THEN
3556 OPEN c4_2 (v4_1.cc_header_id,
3557 l_start_date,
3558 l_end_date,
3559 l_transfer_date);
3560 LOOP
3561 FETCH c4_2 INTO v4_2;
3562 EXIT WHEN c4_2%NOTFOUND;
3563
3564 IF (v4_2.cc_det_pf_date > l_transfer_date)
3565 THEN
3566 IF (g_debug_mode = 'Y')
3567 THEN
3568 output_debug
3569 (l_full_path,
3570 ' IGCCMPSB -- ************ Before CC Period Validation'
3571 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3572 || ' *************************'
3573 );
3574 END IF;
3575
3576 -- Bug 1634159 Fixed
3577 l_cc_period_open :=
3578 is_cc_period_open (v4_2.cc_det_pf_date, l_sob_id, l_org_id);
3579
3580 IF fnd_api.to_boolean (l_cc_period_open)
3581 THEN
3582 l_source_pf_cc_prd := fnd_api.g_true;
3583
3584 IF (g_debug_mode = 'Y')
3585 THEN
3586 output_debug
3587 (l_full_path,
3588 ' IGCCMPSB -- ************ Source CC Period Open '
3589 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3590 || ' *************************'
3591 );
3592 END IF;
3593 ELSE
3594 l_source_pf_cc_prd := fnd_api.g_false;
3595
3596 IF (g_debug_mode = 'Y')
3597 THEN
3598 output_debug
3599 (l_full_path,
3600 ' IGCCMPSB -- ************ Source CC Period Not Open '
3601 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3602 || ' *************************'
3603 );
3604 END IF;
3605
3606 l_exception := NULL;
3607 fnd_message.set_name ('IGC',
3608 'IGC_CC_SOURCE_PF_DT_NOT_CC_PRD'
3609 );
3610 fnd_message.set_token ('SOURCE_PF_DT',
3611 TO_CHAR (v4_2.cc_det_pf_date),
3612 TRUE
3613 );
3614 l_exception := fnd_message.get;
3615
3616 INSERT INTO igc_cc_process_exceptions
3617 (process_type, process_phase,
3618 cc_header_id, cc_acct_line_id,
3619 cc_det_pf_line_id, exception_reason,
3620 org_id, set_of_books_id, request_id
3621 )
3622 VALUES (l_process_type, p_process_phase,
3623 v4.cc_header_id, v4.cc_acct_line_id,
3624 v4.cc_det_pf_line_id, l_exception,
3625 l_org_id, l_sob_id, l_request_id1
3626 );
3627 END IF;
3628
3629 IF (l_sbc_on)
3630 THEN
3631 IF (g_debug_mode = 'Y')
3632 THEN
3633 output_debug
3634 (l_full_path,
3635 ' IGCCMPSB -- ************ Before GL Period Validation'
3636 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3637 || ' *************************'
3638 );
3639 END IF;
3640
3641 l_gl_period_open :=
3642 is_gl_period_open (v4_2.cc_det_pf_date, l_sob_id);
3643
3644 IF fnd_api.to_boolean (l_gl_period_open)
3645 THEN
3646 l_source_pf_gl_prd := fnd_api.g_true;
3647
3648 IF (g_debug_mode = 'Y')
3649 THEN
3650 output_debug
3651 (l_full_path,
3652 ' IGCCMPSB -- ************ Source GL Period Open '
3653 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3654 || ' *************************'
3655 );
3656 END IF;
3657 ELSE
3658 l_source_pf_gl_prd := fnd_api.g_false;
3659
3660 IF (g_debug_mode = 'Y')
3661 THEN
3662 output_debug
3663 (l_full_path,
3664 ' IGCCMPSB -- ************ Source GL Period Not Open '
3665 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3666 || ' *************************'
3667 );
3668 END IF;
3669
3670 l_exception := NULL;
3671 fnd_message.set_name ('IGC',
3672 'IGC_CC_SOURCE_PF_DT_NOT_OPEN'
3673 );
3674 fnd_message.set_token ('SOURCE_PF_DT',
3675 TO_CHAR (v4_2.cc_det_pf_date),
3676 TRUE
3677 );
3678
3679 IF (g_excep_level >= g_debug_level)
3680 THEN
3681 fnd_log.MESSAGE (g_excep_level, l_full_path,
3682 FALSE);
3683 END IF;
3684
3685 l_exception := fnd_message.get;
3686
3687 INSERT INTO igc_cc_process_exceptions
3688 (process_type, process_phase,
3689 cc_header_id, cc_acct_line_id,
3690 cc_det_pf_line_id, exception_reason,
3691 org_id, set_of_books_id, request_id
3692 )
3693 VALUES (l_process_type, p_process_phase,
3694 v4_1.cc_header_id, v4_2.cc_acct_line_id,
3695 v4_2.cc_det_pf_line_id, l_exception,
3696 l_org_id, l_sob_id, l_request_id1
3697 );
3698 END IF;
3699 END IF;
3700 END IF;
3701
3702 OPEN c7 (v4_2.cc_acct_line_id, l_target_date, v4_1.cc_header_id);
3703
3704 IF (g_debug_mode = 'Y')
3705 THEN
3706 output_debug (l_full_path,
3707 ' IGCCMPSB -- ************ Open C7'
3708 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3709 || ' *************************'
3710 );
3711 END IF;
3712
3713 FETCH c7
3714 INTO v7;
3715
3716 IF c7%ROWCOUNT = 0
3717 THEN
3718 IF (g_debug_mode = 'Y')
3719 THEN
3720 output_debug
3721 (l_full_path,
3722 ' IGCCMPSB -- ************No Target PF Found'
3723 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3724 || ' *************************'
3725 );
3726 END IF;
3727
3728 l_target_pf_found := fnd_api.g_false;
3729
3730 BEGIN
3731 SELECT 'X'
3732 INTO l_dummy
3733 FROM igc_cc_process_exceptions
3734 WHERE cc_header_id = v4_1.cc_header_id
3735 AND cc_acct_line_id = v4_2.cc_acct_line_id
3736 AND cc_det_pf_line_id IS NULL
3737 AND org_id = l_org_id
3738 AND process_type = l_process_type
3739 AND process_phase = p_process_phase
3740 AND set_of_books_id = l_sob_id
3741 AND request_id = l_request_id1;
3742 EXCEPTION
3743 WHEN NO_DATA_FOUND
3744 THEN
3745 l_exception := NULL;
3746 fnd_message.set_name ('IGC',
3747 'IGC_CC_MPFS_TGT_PF_NOT_FOUND'
3748 );
3749 fnd_message.set_token ('CC_NUM', v4_1.cc_num, TRUE);
3750
3751 IF (g_excep_level >= g_debug_level)
3752 THEN
3753 fnd_log.MESSAGE (g_excep_level, l_full_path,
3754 FALSE);
3755 END IF;
3756
3757 l_exception := fnd_message.get;
3758
3759 INSERT INTO igc_cc_process_exceptions
3760 (process_type, process_phase,
3761 cc_header_id, cc_acct_line_id,
3762 cc_det_pf_line_id, exception_reason,
3763 org_id, set_of_books_id, request_id
3764 )
3765 VALUES (l_process_type, p_process_phase,
3766 v4_1.cc_header_id, v4_2.cc_acct_line_id,
3767 NULL, l_exception,
3768 l_org_id, l_sob_id, l_request_id1
3769 );
3770 END;
3771 ELSE
3772 IF (g_debug_mode = 'Y')
3773 THEN
3774 output_debug
3775 (l_full_path,
3776 ' IGCCMPSB -- ************ Target PF Found'
3777 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3778 || ' *************************'
3779 );
3780 END IF;
3781
3782 -- Bug 1634159 Fixed
3783 l_target_pf_found := fnd_api.g_true;
3784
3785 IF (g_debug_mode = 'Y')
3786 THEN
3787 output_debug
3788 (l_full_path,
3789 ' IGCCMPSB -- ************ Check CC Period Status of target PF'
3790 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3791 || ' *************************'
3792 );
3793 END IF;
3794
3795 l_tgt_cc_open :=
3796 is_cc_period_open (v7.cc_det_pf_date, l_sob_id, l_org_id);
3797
3798 IF fnd_api.to_boolean (l_tgt_cc_open)
3799 THEN
3800 IF (g_debug_mode = 'Y')
3801 THEN
3802 output_debug
3803 (l_full_path,
3804 ' IGCCMPSB -- ************ Target PF CC Period Open'
3805 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3806 || ' *************************'
3807 );
3808 END IF;
3809
3810 l_tgt_cc_open := fnd_api.g_true;
3811 ELSE
3812 IF (g_debug_mode = 'Y')
3813 THEN
3814 output_debug
3815 (l_full_path,
3816 ' IGCCMPSB -- ************ Target PF CC Period Not Open'
3817 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3818 || ' *************************'
3819 );
3820 END IF;
3821
3822 l_tgt_cc_open := fnd_api.g_false;
3823
3824 BEGIN
3825 SELECT 'X'
3826 INTO l_dummy
3827 FROM igc_cc_process_exceptions
3828 WHERE cc_header_id = v4_1.cc_header_id
3829 AND cc_acct_line_id = v4_2.cc_acct_line_id
3830 AND cc_det_pf_line_id = v7.cc_det_pf_line_id
3831 AND org_id = l_org_id
3832 AND process_type = l_process_type
3833 AND process_phase = p_process_phase
3834 AND set_of_books_id = l_sob_id
3835 AND request_id = l_request_id1;
3836 EXCEPTION
3837 WHEN NO_DATA_FOUND
3838 THEN
3839 l_exception := NULL;
3840 fnd_message.set_name
3841 ('IGC',
3842 'IGC_CC_TARGET_PF_DT_NOT_CC_PRD'
3843 );
3844 fnd_message.set_token ('TARGET_PF_DT',
3845 TO_CHAR (v7.cc_det_pf_date),
3846 TRUE
3847 );
3848
3849 IF (g_excep_level >= g_debug_level)
3850 THEN
3851 fnd_log.MESSAGE (g_excep_level,
3852 l_full_path,
3853 FALSE
3854 );
3855 END IF;
3856
3857 l_exception := fnd_message.get;
3858
3859 INSERT INTO igc_cc_process_exceptions
3860 (process_type, process_phase,
3861 cc_header_id, cc_acct_line_id,
3862 cc_det_pf_line_id, exception_reason,
3863 org_id, set_of_books_id, request_id
3864 )
3865 VALUES (l_process_type, p_process_phase,
3866 v4_1.cc_header_id, v4_2.cc_acct_line_id,
3867 v7.cc_det_pf_line_id, l_exception,
3868 l_org_id, l_sob_id, l_request_id1
3869 );
3870 END;
3871 END IF;
3872
3873 -- Bug 1634218 Fixed
3874 IF (g_debug_mode = 'Y')
3875 THEN
3876 output_debug
3877 (l_full_path,
3878 ' IGCCMPSB -- ************ Check GL Period Status of target PF'
3879 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3880 || ' *************************'
3881 );
3882 END IF;
3883
3884 l_tgt_gl_open :=
3885 is_gl_period_open (v7.cc_det_pf_date, l_sob_id);
3886
3887 IF fnd_api.to_boolean (l_tgt_gl_open)
3888 THEN
3889 l_tgt_gl_open := fnd_api.g_true;
3890
3891 IF (g_debug_mode = 'Y')
3892 THEN
3893 output_debug
3894 (l_full_path,
3895 ' IGCCMPSB -- ************ Target GL Period Open '
3896 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3897 || ' *************************'
3898 );
3899 END IF;
3900 ELSE
3901 IF (g_debug_mode = 'Y')
3902 THEN
3903 output_debug
3904 (l_full_path,
3905 ' IGCCMPSB -- ************ Target GL Period Closed '
3906 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
3907 || ' *************************'
3908 );
3909 END IF;
3910
3911 l_tgt_gl_open := fnd_api.g_false;
3912
3913 BEGIN
3914 SELECT 'X'
3915 INTO l_dummy
3916 FROM igc_cc_process_exceptions
3917 WHERE cc_header_id = v4_1.cc_header_id
3918 AND cc_acct_line_id = v4_2.cc_acct_line_id
3919 AND cc_det_pf_line_id = v7.cc_det_pf_line_id
3920 AND org_id = l_org_id
3921 AND process_type = l_process_type
3922 AND process_phase = p_process_phase
3923 AND set_of_books_id = l_sob_id
3924 AND request_id = l_request_id1;
3925 EXCEPTION
3926 WHEN NO_DATA_FOUND
3927 THEN
3928 l_exception := NULL;
3929 fnd_message.set_name
3930 ('IGC',
3931 'IGC_CC_TARGET_PF_DT_NOT_GL_PRD'
3932 );
3933 fnd_message.set_token ('TARGET_PF_DT',
3934 TO_CHAR (v7.cc_det_pf_date),
3935 TRUE
3936 );
3937
3938 IF (g_excep_level >= g_debug_level)
3939 THEN
3940 fnd_log.MESSAGE (g_excep_level,
3941 l_full_path,
3942 FALSE
3943 );
3944 END IF;
3945
3946 l_exception := fnd_message.get;
3947
3948 INSERT INTO igc_cc_process_exceptions
3949 (process_type, process_phase,
3950 cc_header_id, cc_acct_line_id,
3951 cc_det_pf_line_id, exception_reason,
3952 org_id, set_of_books_id, request_id
3953 )
3954 VALUES (l_process_type, p_process_phase,
3955 v4_1.cc_header_id, v4_2.cc_acct_line_id,
3956 v7.cc_det_pf_line_id, l_exception,
3957 l_org_id, l_sob_id, l_request_id1
3958 );
3959 END;
3960 END IF;
3961 END IF;
3962
3963 CLOSE c7;
3964
3965 /*
3966
3967 BEGIN
3968 SELECT 'X'
3969 INTO l_DUMMY
3970 FROM igc_cc_process_exceptions
3971 WHERE cc_header_id = V4.cc_header_id
3972 AND cc_acct_line_id IS NULL
3973 AND org_id = l_org_id
3974 AND process_type = l_process_type
3975 AND process_phase = p_process_phase
3976 AND set_of_books_id = l_sob_id
3977 AND request_id = l_request_id1;
3978
3979 l_target_pf_found := FND_API.G_FALSE;
3980
3981 EXCEPTION
3982 WHEN NO_DATA_FOUND THEN
3983 OPEN C6(V4.cc_header_id,l_target_date,l_org_id,l_sob_id);
3984 FETCH C6 INTO V6;
3985 IF C6%ROWCOUNT=0 THEN
3986 l_target_pf_found := FND_API.G_FALSE;
3987 l_EXCEPTION := NULL;
3988 FND_MESSAGE.SET_NAME('IGC','IGC_CC_MPFS_TGT_PF_NOT_FOUND');
3989 FND_MESSAGE.SET_TOKEN('CC_NUM',V4.cc_num,TRUE);
3990 l_EXCEPTION := FND_MESSAGE.GET;
3991 INSERT INTO igc_cc_process_exceptions
3992 (PROCESS_TYPE ,
3993 PROCESS_PHASE ,
3994 CC_HEADER_ID ,
3995 CC_ACCT_LINE_ID ,
3996 CC_DET_PF_LINE_ID ,
3997 EXCEPTION_REASON ,
3998 ORG_ID ,
3999 SET_OF_BOOKS_ID ,
4000 REQUEST_ID )
4001 values (l_process_type,
4002 p_process_phase,
4003 V4.cc_header_id,
4004 NULL,
4005 NULL,
4006 l_EXCEPTION,
4007 l_org_id,
4008 l_sob_id,
4009 l_request_id1);
4010
4011
4012 ELSE
4013 l_target_pf_found := FND_API.G_TRUE;
4014 END IF;
4015
4016 CLOSE C6;
4017 END;
4018 */
4019
4020 /* Changed FND_API.TO_BOOLEAN(l_cc_inprocess) to NOT NOT FND_API.TO_BOOLEAN(l_cc_inprocess) in the following
4021 Statement to fix bug 1689697 */
4022 IF fnd_api.to_boolean (l_source_pf_cc_prd)
4023 AND fnd_api.to_boolean (l_source_pf_gl_prd)
4024 AND fnd_api.to_boolean (l_target_pf_found)
4025 AND NOT fnd_api.to_boolean (l_cc_inprocess)
4026 AND fnd_api.to_boolean (l_tgt_gl_open)
4027 THEN
4028 IF (g_debug_mode = 'Y')
4029 THEN
4030 output_debug
4031 (l_full_path,
4032 ' IGCCMPSB -- ************ Successfull CC Validation '
4033 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4034 || ' *************************'
4035 );
4036 END IF;
4037
4038 UPDATE igc_cc_process_data ccpd
4039 SET ccpd.validation_status = 'P'
4040 WHERE ccpd.cc_header_id = v4_1.cc_header_id
4041 AND ccpd.org_id = l_org_id
4042 AND ccpd.set_of_books_id = l_sob_id
4043 AND ccpd.request_id = l_request_id1
4044 AND ccpd.process_type = l_process_type;
4045
4046 /* If contract passes the validation phase then change the status to IN-PROCESS */
4047 UPDATE igc_cc_headers cchd
4048 SET cchd.cc_apprvl_status = 'IP'
4049 WHERE cchd.cc_header_id = v4.cc_header_id;
4050
4051 /*Change PO Status */
4052 IF l_previous_apprvl_status = 'AP'
4053 THEN
4054 BEGIN
4055 -- Performance Tuning
4056 -- Replaced the following select with the one below
4057 -- SELECT 'Y'
4058 -- INTO l_DUMMY
4059 -- FROM po_headers pha1
4060 -- WHERE pha1.po_header_id = (SELECT pha2.po_header_id
4061 -- FROM igc_cc_headers cchd,
4062 -- po_headers pha2
4063 -- WHERE cchd.org_id = l_org_id
4064 -- AND cchd.cc_header_id = V4.cc_header_id
4065 -- AND cchd.cc_num = pha2.segment1
4066 -- AND pha2.type_lookup_code = 'STANDARD');
4067 SELECT 'Y'
4068 INTO l_dummy
4069 FROM po_headers_all pha1, igc_cc_headers cchd
4070 WHERE cchd.org_id = l_org_id
4071 AND cchd.cc_header_id = v4_1.cc_header_id
4072 AND cchd.cc_num = pha1.segment1
4073 AND pha1.type_lookup_code = 'STANDARD'
4074 AND pha1.org_id = l_org_id;
4075
4076 -- Performance Tuning
4077 -- Replaced the following update with the one below
4078 -- UPDATE po_headers pha1
4079 -- SET pha1.approved_flag = 'N'
4080 -- WHERE (pha1.segment1,pha1.org_id,pha1.type_lookup_code) IN
4081 -- (SELECT pha2.segment1,pha2.org_id,pha2.type_lookup_code
4082 -- FROM po_headers pha2, igc_cc_headers cchd
4083 -- WHERE cchd.cc_header_id = V4.cc_header_id
4084 -- AND pha2.segment1 = cchd.cc_num
4085 -- AND pha2.org_id = cchd.org_id
4086 -- AND pha2.type_lookup_code = 'STANDARD');
4087 UPDATE po_headers_all pha1
4088 SET pha1.approved_flag = 'N'
4089 WHERE pha1.type_lookup_code = 'STANDARD'
4090 AND pha1.org_id = l_org_id
4091 AND pha1.segment1 =
4092 (SELECT cchd.cc_num
4093 FROM igc_cc_headers cchd
4094 WHERE cchd.cc_header_id = v4_1.cc_header_id);
4095 EXCEPTION
4096 WHEN NO_DATA_FOUND
4097 THEN
4098 NULL;
4099 END;
4100 END IF;
4101 ELSE
4102 l_fail := fnd_api.g_true;
4103
4104 UPDATE igc_cc_process_data ccpd
4105 SET ccpd.validation_status = 'L'
4106 WHERE ccpd.cc_header_id = v4_1.cc_header_id
4107 AND ccpd.org_id = l_org_id
4108 AND ccpd.set_of_books_id = l_sob_id
4109 AND ccpd.request_id = l_request_id1
4110 AND ccpd.process_type = l_process_type;
4111 END IF;
4112 END LOOP;
4113 CLOSE c4_2;
4114 END IF;
4115 ELSE
4116 l_fail := fnd_api.g_true;
4117 -- This ends the lock on PO and CC
4118 END IF;
4119 END LOOP;
4120
4121 CLOSE c4_1;
4122
4123 -- Mark Flag 2;
4124 COMMIT;
4125
4126 IF (g_debug_mode = 'Y')
4127 THEN
4128 output_debug
4129 (l_full_path,
4130 ' IGCCMPSB -- ************ After lock CC and PO '
4131 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4132 || ' *************************'
4133 );
4134 END IF;
4135
4136 OPEN c5;
4137
4138 LOOP
4139 FETCH c5
4140 INTO v5;
4141
4142 EXIT WHEN c5%NOTFOUND;
4143
4144 IF (g_debug_mode = 'Y')
4145 THEN
4146 output_debug (l_full_path,
4147 ' IGCCMPSB -- ************ Before encumber '
4148 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4149 || ' *************************'
4150 );
4151 END IF;
4152
4153 IF (l_sbc_on = TRUE AND l_conf_enc_on = TRUE)
4154 THEN
4155 l_result_of_reservation :=
4156 igc_cc_mpfs_process_pkg.encumber_cc
4157 (l_currency_code,
4158 v5.cc_header_id,
4159 l_sbc_on,
4160 /*Bug No : 6341012. SLA Uptake. Encumbrance Type IDs are not required */
4161 -- l_purch_encumbrance_type_id,
4162 l_start_date,
4163 l_end_date,
4164 l_transfer_date,
4165 l_target_date
4166 );
4167
4168 IF (g_debug_mode = 'Y')
4169 THEN
4170 output_debug
4171 (l_full_path,
4172 ' IGCCMPSB -- ************ After encumber '
4173 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4174 || ' *************************'
4175 );
4176 END IF;
4177 ELSE
4178 l_result_of_reservation := 'P';
4179 END IF;
4180
4181 IF l_result_of_reservation = 'P'
4182 THEN
4183 UPDATE igc_cc_process_data ccpd
4184 SET ccpd.reservation_status = 'P'
4185 WHERE ccpd.cc_header_id = v5.cc_header_id
4186 AND ccpd.request_id = l_request_id1;
4187
4188 COMMIT;
4189 SAVEPOINT s2;
4190
4191 /* Call mpfs Update */
4192 IF (g_debug_mode = 'Y')
4193 THEN
4194 output_debug (l_full_path,
4195 ' IGCCMPSB -- ************ Before Update '
4196 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4197 || ' *************************'
4198 );
4199 END IF;
4200
4201 l_result_mpfs_update :=
4202 mpfs_update (v5.cc_header_id,
4203 l_request_id1,
4204 l_sob_id,
4205 l_org_id,
4206 l_start_date,
4207 l_end_date,
4208 l_target_date,
4209 l_transfer_date,
4210 l_err_mesg
4211 );
4212
4213 IF (g_debug_mode = 'Y')
4214 THEN
4215 output_debug (l_full_path,
4216 ' IGCCMPSB -- ************ After Update '
4217 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4218 || ' *************************'
4219 );
4220 END IF;
4221
4222 IF (l_result_mpfs_update = 'F')
4223 THEN
4224 IF (g_debug_mode = 'Y')
4225 THEN
4226 output_debug
4227 (l_full_path,
4228 ' IGCCMPSB -- ************ Update Failure '
4229 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4230 || ' *************************'
4231 );
4232 END IF;
4233
4234 ROLLBACK TO s2;
4235
4236 INSERT INTO igc_cc_process_exceptions
4237 (process_type, process_phase,
4238 cc_header_id, cc_acct_line_id,
4239 cc_det_pf_line_id, exception_reason, org_id,
4240 set_of_books_id, request_id
4241 )
4242 VALUES (l_process_type, p_process_phase,
4243 v5.cc_header_id, NULL,
4244 NULL, l_err_mesg, l_org_id,
4245 l_sob_id, l_request_id1
4246 );
4247 END IF;
4248 ELSE
4249 IF (g_debug_mode = 'Y')
4250 THEN
4251 output_debug
4252 (l_full_path,
4253 ' IGCCMPSB -- ************ Encumbrance Failure '
4254 || TO_CHAR (SYSDATE, 'DD-MON-YY:MI:SS')
4255 || ' *************************'
4256 );
4257 END IF;
4258
4259 l_exception := NULL;
4260 fnd_message.set_name ('IGC', 'IGC_CC_FAILED_TO_ENCUMBER');
4261 fnd_message.set_token ('CC_NUM', v4.cc_num, TRUE);
4262
4263 IF (g_error_level >= g_debug_level)
4264 THEN
4265 fnd_log.MESSAGE (g_error_level, l_full_path, FALSE);
4266 END IF;
4267
4268 l_exception := fnd_message.get;
4269
4270 INSERT INTO igc_cc_process_exceptions
4271 (process_type, process_phase, cc_header_id,
4272 cc_acct_line_id, cc_det_pf_line_id,
4273 exception_reason, org_id, set_of_books_id,
4274 request_id
4275 )
4276 VALUES (l_process_type, p_process_phase, v5.cc_header_id,
4277 NULL, NULL,
4278 l_exception, l_org_id, l_sob_id,
4279 l_request_id1
4280 );
4281
4282 UPDATE igc_cc_process_data ccpd
4283 SET ccpd.reservation_status = 'F'
4284 WHERE ccpd.cc_header_id = v5.cc_header_id
4285 AND ccpd.set_of_books_id = l_sob_id
4286 AND ccpd.request_id = l_request_id1
4287 AND ccpd.org_id = l_org_id
4288 AND ccpd.process_type = l_process_type;
4289 END IF;
4290
4291 UPDATE igc_cc_headers cchd
4292 SET cchd.cc_apprvl_status = v5.old_approval_status
4293 WHERE cchd.cc_header_id = v5.cc_header_id;
4294
4295 IF v5.old_approval_status = 'AP'
4296 THEN
4297 BEGIN
4298 -- Replaced the following query with the one below
4299 -- to tune the performance
4300 -- SELECT 'Y'
4301 -- INTO l_DUMMY
4302 -- FROM po_headers pha1
4303 -- WHERE pha1.po_header_id = (SELECT pha2.po_header_id
4304 -- FROM igc_cc_headers cchd,
4305 -- po_headers pha2
4306 -- WHERE cchd.org_id = l_org_id
4307 -- AND cchd.cc_header_id = V5.cc_header_id
4308 -- AND cchd.cc_num = pha2.segment1
4309 -- AND pha2.type_lookup_code = 'STANDARD');
4310 SELECT 'Y'
4311 INTO l_dummy
4312 FROM po_headers_all pha1, igc_cc_headers cchd
4313 WHERE cchd.org_id = l_org_id
4314 AND cchd.cc_header_id = v5.cc_header_id
4315 AND cchd.cc_num = pha1.segment1
4316 AND pha1.type_lookup_code = 'STANDARD'
4317 AND pha1.org_id = l_org_id;
4318
4319 -- Performance Tuning
4320 -- Replaced the following update with the one below
4321 -- UPDATE po_headers pha1
4322 -- SET pha1.approved_flag = 'Y'
4323 -- WHERE (pha1.segment1,pha1.org_id,pha1.type_lookup_code) IN
4324 -- (SELECT pha2.segment1,pha2.org_id,pha2.type_lookup_code
4325 -- FROM po_headers pha2, igc_cc_headers cchd
4326 -- WHERE cchd.cc_header_id = V5.cc_header_id
4327 -- AND pha2.segment1 = cchd.cc_num
4328 -- AND pha2.org_id = cchd.org_id
4329 -- AND pha2.type_lookup_code = 'STANDARD');
4330 UPDATE po_headers_all pha1
4331 SET pha1.approved_flag = 'Y'
4332 WHERE pha1.type_lookup_code = 'STANDARD'
4333 AND pha1.org_id = l_org_id
4334 AND pha1.segment1 =
4335 (SELECT cchd.cc_num
4336 FROM igc_cc_headers cchd
4337 WHERE cchd.cc_header_id = v5.cc_header_id);
4338 EXCEPTION
4339 WHEN NO_DATA_FOUND
4340 THEN
4341 NULL;
4342 END;
4343 END IF;
4344 END LOOP;
4345
4346 CLOSE c5;
4347
4348 -- Mark Flag 3
4349 COMMIT;
4350 -- This ends the Final Mode Check
4351 END IF;
4352 -- If Paramer Validation fails the program logic is jumped here.
4353 ELSE
4354 INSERT INTO igc_cc_process_exceptions
4355 (process_type, process_phase, cc_header_id,
4356 cc_acct_line_id, cc_det_pf_line_id, exception_reason,
4357 org_id, set_of_books_id, request_id
4358 )
4359 VALUES (l_process_type, p_process_phase, NULL,
4360 NULL, NULL, l_exception,
4361 l_org_id, l_sob_id, l_request_id1
4362 );
4363
4364 l_exception := NULL;
4365 fnd_message.set_name ('IGC', 'IGC_CC_PARAM_VALID_FAILED');
4366
4367 IF (g_error_level >= g_debug_level)
4368 THEN
4369 fnd_log.MESSAGE (g_error_level, l_full_path, FALSE);
4370 END IF;
4371
4372 l_exception := fnd_message.get;
4373
4374 INSERT INTO igc_cc_process_exceptions
4375 (process_type, process_phase, cc_header_id,
4376 cc_acct_line_id, cc_det_pf_line_id, exception_reason,
4377 org_id, set_of_books_id, request_id
4378 )
4379 VALUES (l_process_type, p_process_phase, NULL,
4380 NULL, NULL, l_exception,
4381 l_org_id, l_sob_id, l_request_id1
4382 );
4383
4384 COMMIT;
4385 END IF;
4386
4387 /*Bug No : 6341012. MOAC Uptake. Set ORG_ID before submitting Request */
4388
4389 fnd_request.set_org_id(l_org_id);
4390
4391 l_request_idt_id2 :=
4392 fnd_request.submit_request ('IGC',
4393 'IGCCMPPR',
4394 NULL,
4395 NULL,
4396 FALSE,
4397 l_sob_id,
4398 l_org_id,
4399 p_process_phase,
4400 'M',
4401 l_request_id1
4402 );
4403 ---------------------------------
4404 ------Run XML Report
4405 ---------------------------------
4406 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
4407 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
4408 l_lang,
4409 l_terr,
4410 'IGCCMPPR_XML',
4411 'IGC',
4412 'IGCCMPPR_XML' );
4413 l_layout := FND_REQUEST.ADD_LAYOUT(
4414 'IGC',
4415 'IGCCMPPR_XML',
4416 l_lang,
4417 l_terr,
4418 'RTF');
4419 IF l_layout then
4420 Fnd_request.set_org_id(l_org_id);
4421 l_request_idt_id2 :=fnd_request.submit_request (
4422 'IGC',
4423 'IGCCMPPR_XML',
4424 NULL,
4425 NULL,
4426 FALSE,
4427 l_sob_id,
4428 l_org_id,
4429 p_process_phase,
4430 'M',
4431 l_request_id1);
4432 END IF;
4433 END IF;
4434 --------------------
4435 -- End of XML Report
4436 --------------------
4437
4438
4439 -- ------------------------------------------------------------------------------------
4440 -- Ensure that any exceptions raised are output into the log file to be reported to
4441 -- the user if any are present.
4442 -- ------------------------------------------------------------------------------------
4443 fnd_msg_pub.count_and_get (p_count => l_msg_count, p_data => l_msg_data);
4444
4445 IF (l_msg_count > 0)
4446 THEN
4447 l_error_text := '';
4448
4449 FOR l_cur IN 1 .. l_msg_count
4450 LOOP
4451 l_error_text :=
4452 l_cur || ' ' || fnd_msg_pub.get (l_cur, fnd_api.g_false);
4453
4454 IF (g_excep_level >= g_debug_level)
4455 THEN
4456 fnd_log.STRING (g_excep_level, l_full_path, l_error_text);
4457 END IF;
4458
4459 fnd_file.put_line (fnd_file.LOG, l_error_text);
4460 END LOOP;
4461 END IF;
4462 EXCEPTION
4463 WHEN OTHERS
4464 THEN
4465 /*IF (g_debug_mode = 'Y') THEN
4466 Output_Debug (l_full_path, ' SQLERRM ' || SQLERRM);
4467 END IF;*/
4468 l_exception := NULL;
4469
4470 IF (fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error))
4471 THEN
4472 fnd_msg_pub.add_exc_msg (g_pkg_name, 'MPFS_MAIN');
4473 END IF;
4474
4475 fnd_msg_pub.count_and_get (p_count => l_msg_count,
4476 p_data => l_msg_data);
4477
4478 IF (l_msg_count > 0)
4479 THEN
4480 l_error_text := '';
4481
4482 FOR l_cur IN 1 .. l_msg_count
4483 LOOP
4484 l_error_text :=
4485 ' Mesg No : '
4486 || l_cur
4487 || ' '
4488 || fnd_msg_pub.get (l_cur, fnd_api.g_false);
4489
4490 /*fnd_file.put_line (FND_FILE.LOG,
4491 l_error_text);*/
4492 IF (g_excep_level >= g_debug_level)
4493 THEN
4494 fnd_log.STRING (g_excep_level, l_full_path, l_error_text);
4495 END IF;
4496 END LOOP;
4497 ELSE
4498 l_error_text := 'Error Returned but Error stack has no data';
4499
4500 -- fnd_file.put_line (FND_FILE.LOG,
4501 -- l_error_text);
4502 IF (g_error_level >= g_debug_level)
4503 THEN
4504 fnd_log.STRING (g_error_level, l_full_path, l_error_text);
4505 END IF;
4506 END IF;
4507 -- ROLLBACK TO S1;
4508 END; /* Procedure MASS_PAYMENT_FORECAST_SHIFT_MAIN End */
4509
4510
4511
4512
4513 --
4514 -- Output_Debug Procedure is the Generic procedure designed for outputting debug
4515 -- information that is required from this procedure.
4516 --
4517 -- Parameters :
4518 --
4519 -- p_debug_msg ==> Record to be output into the debug log file.
4520 --
4521 PROCEDURE Output_Debug (
4522 p_path IN VARCHAR2,
4523 p_debug_msg IN VARCHAR2
4524 ) IS
4525
4526 -- Constants :
4527
4528 /*l_prod VARCHAR2(3) := 'IGC';
4529 l_sub_comp VARCHAR2(6) := 'CC_MPF';
4530 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
4531 l_Return_Status VARCHAR2(1);*/
4532 l_api_name CONSTANT VARCHAR2(30) := 'Output_Debug';
4533
4534 BEGIN
4535
4536 --FND_FILE.put_line( FND_FILE.log, p_debug_msg );
4537 /*IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
4538 p_profile_log_name => l_profile_name,
4539 p_prod => l_prod,
4540 p_sub_comp => l_sub_comp,
4541 p_filename_val => NULL,
4542 x_Return_Status => l_Return_Status
4543 );
4544
4545 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4546 raise FND_API.G_EXC_ERROR;
4547 END IF;*/
4548
4549 IF (g_state_level >= g_debug_level ) THEN
4550 FND_LOG.STRING (g_state_level,p_path,p_debug_msg);
4551 END IF;
4552
4553 RETURN;
4554
4555 -- --------------------------------------------------------------------
4556 -- Exception handler section for the Output_Debug procedure.
4557 -- --------------------------------------------------------------------
4558 EXCEPTION
4559
4560 /* WHEN FND_API.G_EXC_ERROR THEN
4561 RETURN;*/
4562
4563 WHEN OTHERS THEN
4564 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
4565 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
4566 END IF;
4567 RETURN;
4568
4569 END Output_Debug;
4570 BEGIN
4571 g_debug_flag := 'N';
4572 g_line_num := 0;
4573 g_debug_mode := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
4574
4575 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
4576 g_state_level := FND_LOG.LEVEL_STATEMENT;
4577 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
4578 g_event_level := FND_LOG.LEVEL_EVENT;
4579 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
4580 g_error_level := FND_LOG.LEVEL_ERROR;
4581 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
4582 g_path := 'IGC.PLSQL.IGCCMPSB.IGC_CC_MPFS_PROCESS_PKG.';
4583
4584
4585 END IGC_CC_MPFS_PROCESS_PKG; /* Package Ends */