[Home] [Help]
PACKAGE BODY: APPS.IGC_CC_OPEN_INTERFACE_PKG
Source
1 PACKAGE BODY IGC_CC_OPEN_INTERFACE_PKG AS
2 /* $Header: IGCCOPIB.pls 120.15.12010000.3 2008/11/13 04:53:57 schakkin ship $ */
3
4 --Bug 3199488 Start Block
5 l_debug_level number:=FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 l_state_level number:=FND_LOG.LEVEL_STATEMENT;
8 l_proc_level number:=FND_LOG.LEVEL_PROCEDURE;
9 l_event_level number:=FND_LOG.LEVEL_EVENT;
10 l_excep_level number:=FND_LOG.LEVEL_EXCEPTION;
11 l_error_level number:=FND_LOG.LEVEL_ERROR;
12 l_unexp_level number:=FND_LOG.LEVEL_UNEXPECTED;
13 --Bug 3199488 End Block
14
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IGC_CC_OPEN_INTERFACE_PKG';
16
17 -- The flag determines whether to print debug information or not.
18 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.VALUE('IGC_DEBUG_ENABLED'),'N');
19 g_high_date DATE := Add_Months(Sysdate, 1200);
20
21 g_process_phase VARCHAR2(1);
22 g_batch_id NUMBER;
23
24 g_cc_bc_enable_flag VARCHAR2(1);
25 g_sbc_enable_flag VARCHAR2(1);
26 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
27 -- g_cc_prov_encmbrnc_flag VARCHAR2(1);
28 -- g_cc_conf_encmbrnc_flag VARCHAR2(1);
29 g_sb_prov_encmbrnc_flag VARCHAR2(1);
30 g_sb_conf_encmbrnc_flag VARCHAR2(1);
31
32 g_cc_state igc_cc_headers.cc_state%TYPE;
33 g_cc_apprvl_status igc_cc_headers.cc_apprvl_status%TYPE;
34
35
36 -- Bug 2871052, created the following PLSQL tables for inserting into
37 -- the pa_bc_packets table.
38 -- We use PA PLSQL table definition PA_CC_ENC_IMPORT_FCK.FC_Rec_Table, which has
39 -- the following structure :-
40 -- TYPE fc_rec_table IS RECORD (
41 -- packet_id pa_bc_packets.packet_id%type,
42 -- bc_packet_id pa_bc_packets.bc_packet_id%type,
43 -- parent_bc_packet_id pa_bc_packets.parent_bc_packet_id%type,
44 -- ext_budget_type varchar2(100),
45 -- bc_commitment_id pa_bc_packets.bc_commitment_id%type,
46 -- project_id pa_bc_packets.project_id%type,
47 -- task_id pa_bc_packets.task_id%type,
48 -- expenditure_type pa_bc_packets.expenditure_type%type,
49 -- expenditure_item_date pa_bc_packets.expenditure_item_date%type,
50 -- set_of_books_id pa_bc_packets.set_of_books_id%type,
51 -- je_category_name pa_bc_packets.je_category_name%type,
52 -- je_source_name pa_bc_packets.je_source_name%type,
53 -- status_code pa_bc_packets.status_code%type,
54 -- document_type pa_bc_packets.document_type%type,
55 -- funds_process_mode pa_bc_packets.funds_process_mode%type ,
56 -- expenditure_organization_id pa_bc_packets.expenditure_organization_id%type,
57 -- document_header_id pa_bc_packets.document_header_id%type,
58 -- document_distribution_id pa_bc_packets.document_distribution_id%type,
59 -- budget_version_id pa_bc_packets.budget_version_id%type,
60 -- burden_cost_flag pa_bc_packets.burden_cost_flag%type ,
61 -- balance_posted_flag pa_bc_packets.balance_posted_flag%type,
62 -- actual_flag pa_bc_packets.actual_flag%type,
63 -- gl_date pa_bc_packets.gl_date%type,
64 -- period_name pa_bc_packets.period_name%type,
65 -- period_year pa_bc_packets.period_year%type,
66 -- period_num pa_bc_packets.period_num%type,
67 -- encumbrance_type_id pa_bc_packets.encumbrance_type_id%type,
68 -- proj_encumbrance_type_id pa_bc_packets.proj_encumbrance_type_id%type,
69 -- top_task_id pa_bc_packets.top_task_id%type,
70 -- parent_resource_id pa_bc_packets.parent_resource_id%type,
71 -- resource_list_member_id pa_bc_packets.resource_list_member_id%type,
72 -- entered_dr pa_bc_packets.entered_dr%type,
73 -- entered_cr pa_bc_packets.entered_cr%type,
74 -- accounted_dr pa_bc_packets.accounted_dr%type,
75 -- accounted_cr pa_bc_packets.accounted_cr%type,
76 -- result_code pa_bc_packets.result_code%type,
77 -- old_budget_ccid pa_bc_packets.old_budget_ccid%type,
78 -- txn_ccid pa_bc_packets.txn_ccid%type,
79 -- org_id pa_bc_packets.org_id%type,
80 -- last_update_date pa_bc_packets.last_update_date%type,
81 -- last_updated_by pa_bc_packets.last_updated_by%type,
82 -- created_by pa_bc_packets.created_by%type,
83 -- creation_date pa_bc_packets.creation_date%type,
84 -- last_update_login pa_bc_packets.last_update_login%type );
85
86 -- This is a table of records to check funds in the commitment budget.
87 g_pa_fc_com_rec_tab PA_CC_ENC_IMPORT_FCK.FC_Rec_Table;
88
89 -- This is a table of records to check funds in the payment budget.
90 g_pa_fc_pay_rec_tab PA_CC_ENC_IMPORT_FCK.FC_Rec_Table;
91
92 g_pa_fc_com_counter NUMBER := 0;
93 g_pa_fc_pay_counter NUMBER := 0;
94 g_bc_packet_id_com NUMBER;
95 g_bc_packet_id_pay NUMBER;
96 g_pa_cb_funds_check_required BOOLEAN := FALSE;
97 g_pa_sb_funds_check_required BOOLEAN := FALSE;
98
99 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
100 -- g_prov_encumbrance_type_id NUMBER;
101 -- g_conf_encumbrance_type_id NUMBER;
102 -- g_req_encumbrance_type_id NUMBER;
103 -- g_purch_encumbrance_type_id NUMBER;
104 -- g_inv_encumbrance_type_id NUMBER;
105 -- Bug 2871052, End
106 --
107 --
108 -- Generic Procedure for putting out debug information
109 --
110 /* Commented out as per bug 3199488
111 PROCEDURE Output_Debug (
112 p_debug_msg IN VARCHAR2
113 );
114 */
115 /***************************************************************************/
116 -- Get the value for Header_Id
117 /***************************************************************************/
118 PROCEDURE HEADER_INTERFACE_DERIVE
119 ( P_Header_Id OUT NOCOPY NUMBER)
120 IS
121 BEGIN
122 SELECT igc_cc_headers_s.nextval
123 INTO P_Header_Id FROM DUAL;
124 END;
125
126 /***************************************************************************/
127 -- Get the value for Acct_Line_Id
128 /***************************************************************************/
129 PROCEDURE ACCT_LINE_INTERFACE_DERIVE
130 ( P_Acct_Line_Id OUT NOCOPY NUMBER)
131 IS
132 BEGIN
133 SELECT igc_cc_acct_lines_s.nextval
134 INTO P_Acct_Line_Id FROM DUAL;
135 END;
136
137 /***************************************************************************/
138 -- Get the value for Det_Pf_Line_Id
139 /***************************************************************************/
140 PROCEDURE DET_PF_INTERFACE_DERIVE
141 ( P_Det_Pf_Line_Id OUT NOCOPY NUMBER)
142 IS
143 BEGIN
144 SELECT igc_cc_det_pf_s.nextval
145 INTO P_Det_Pf_Line_Id FROM DUAL;
146 END;
147
148 /***************************************************************************/
149 -- Insert the errors into the error table
150 /***************************************************************************/
151 PROCEDURE INTERFACE_HANDLE_ERRORS
152 ( P_Interface_Header_Id IN NUMBER,
153 P_Interface_Acct_Line_Id IN NUMBER,
154 P_Interface_Det_Pf_Line_Id IN NUMBER,
155 P_Org_Id IN NUMBER,
156 P_Set_of_Books_Id IN NUMBER,
157 P_Error_Message IN VARCHAR2,
158 P_X_Error_Status IN OUT NOCOPY VARCHAR2)
159 IS
160 BEGIN
161 IF P_X_Error_Status = 'N' THEN
162 ROLLBACK;
163 P_X_Error_Status := 'E';
164 END IF;
165 INSERT INTO IGC_CC_INTERFACE_ERRORS
166 ( batch_id,
167 interface_header_id,
168 interface_acct_line_id,
169 interface_det_pf_line_id,
170 org_id,
171 set_of_books_id,
172 error_message )
173 VALUES ( g_batch_id,
174 P_Interface_Header_Id,
175 P_Interface_Acct_Line_Id,
176 P_Interface_Det_Pf_Line_Id,
177 P_Org_Id,
178 P_Set_of_Books_Id,
179 P_Error_Message);
180 END;
181
182 /***************************************************************************/
183 -- Insert the Acct Line and Det Pf records into igc_cc_interface_errors
184 -- for which the header or acct line record does not exists in the batch.
185 /***************************************************************************/
186 PROCEDURE INSERT_ORPHAN_RECORDS
187 ( P_X_Error_Status IN OUT NOCOPY VARCHAR2 )
188 IS
189 l_interface_header_id NUMBER;
190 l_interface_acct_line_id NUMBER;
191 l_interface_det_pf_id NUMBER;
192 l_error_message igc_cc_interface_errors.error_message%TYPE;
193
194 CURSOR c_interface_orphan_acct_lines IS
195 SELECT ICALI.interface_header_id,
196 ICALI.interface_acct_line_id
197 FROM igc_cc_acct_lines_interface ICALI
198 WHERE ICALI.batch_id = g_batch_Id
199 AND NOT EXISTS (SELECT ICALI1.interface_header_id
200 FROM igc_cc_headers_interface ICALI1
201 WHERE ICALI1.batch_id = g_batch_id
202 AND ICALI.interface_header_id = ICALI1.interface_header_id);
203
204 CURSOR c_interface_orphan_det_pf IS
205 SELECT ICDPI.interface_acct_line_id,
206 ICDPI.interface_det_pf_line_id
207 FROM igc_cc_det_pf_interface ICDPI
208 WHERE ICDPI.batch_id = g_batch_id
209 AND NOT EXISTS (SELECT ICALI.interface_acct_line_id
210 FROM igc_cc_headers_interface ICHI,
211 igc_cc_acct_lines_interface ICALI
212 WHERE ICHI.batch_id = g_batch_id
213 AND ICHI.batch_id = ICALI.batch_id
214 AND ICHI.interface_header_id = ICALI.interface_header_id
215 AND ICDPI.interface_acct_line_id = ICALI.interface_acct_line_id);
216
217 BEGIN
218 OPEN c_interface_orphan_acct_lines;
219 LOOP
220 FETCH c_interface_orphan_acct_lines INTO l_interface_header_id, l_interface_acct_line_id;
221 EXIT WHEN c_interface_orphan_acct_lines%NOTFOUND;
222
223 IF P_X_Error_Status = 'N' THEN
224 ROLLBACK;
225 P_X_Error_Status := 'E';
226 END IF;
227
228 l_error_message := NULL;
229 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_HDR_REC_NOT_FOUND');
230 l_error_message := FND_MESSAGE.GET;
231 INTERFACE_HANDLE_ERRORS
232 ( l_interface_header_id,
233 l_interface_acct_line_id,
234 NULL,
235 NULL,
236 NULL,
237 l_error_message,
238 P_X_Error_Status);
239 END LOOP;
240 CLOSE c_interface_orphan_acct_lines;
241
242 OPEN c_interface_orphan_det_pf;
243 LOOP
244 FETCH c_interface_orphan_det_pf INTO l_interface_acct_line_id, l_interface_det_pf_id;
245 EXIT WHEN c_interface_orphan_det_pf%NOTFOUND;
246
247 IF P_X_Error_Status = 'N' THEN
248 ROLLBACK;
249 P_X_Error_Status := 'E';
250 END IF;
251
252 l_error_message := NULL;
253 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_HDR_NOT_FOUND');
254 l_error_message := FND_MESSAGE.GET;
255 INTERFACE_HANDLE_ERRORS
256 ( NULL,
257 l_interface_acct_line_id,
258 l_interface_det_pf_id,
259 NULL,
260 NULL,
261 l_error_message,
262 P_X_Error_Status);
263 END LOOP;
264 CLOSE c_interface_orphan_det_pf;
265 END;
266
267 /***************************************************************************/
268 -- Get the Parent Ids
269 /***************************************************************************/
270 PROCEDURE GET_PARENT_ID
271 ( P_Interface_Parent_Header_Id IN NUMBER,
272 P_Interface_Parent_AcctLine_Id IN NUMBER,
273 P_Interface_Parent_Det_Pf_Id IN NUMBER,
274 P_Parent_Header_Id IN OUT NOCOPY NUMBER,
275 P_Parent_Acct_Line_Id IN OUT NOCOPY NUMBER,
276 P_Parent_Det_Pf_Id OUT NOCOPY NUMBER)
277 IS
278
279 BEGIN
280 IF P_Interface_Parent_Header_Id IS NOT NULL AND P_Parent_Header_Id IS NULL THEN
281 BEGIN
282 SELECT cch.cc_header_id INTO P_Parent_Header_Id
283 FROM igc_cc_headers cch, igc_cc_headers_interface cchi
284 WHERE cchi.interface_header_id = P_Interface_Parent_Header_Id
285 AND cchi.cc_num = cch.cc_num
286 AND cchi.org_id = cch.org_id;
287 EXCEPTION WHEN OTHERS THEN RAISE;
288 END;
289 END IF;
290 IF P_Interface_Parent_AcctLine_Id IS NOT NULL AND P_Parent_Acct_Line_Id IS NULL THEN
291 BEGIN
292 SELECT cca.cc_acct_line_id INTO P_Parent_Acct_Line_Id
293 FROM igc_cc_acct_lines cca, igc_cc_acct_lines_interface ccai
294 WHERE ccai.interface_acct_line_id = P_Interface_Parent_AcctLine_Id
295 AND cca.cc_header_id = P_Parent_Header_Id
296 AND ccai.cc_acct_line_num = cca.cc_acct_line_num;
297 EXCEPTION WHEN OTHERS THEN RAISE;
298 END;
299 END IF;
300 IF P_Interface_Parent_Det_Pf_Id IS NOT NULL AND P_Parent_Det_Pf_Id IS NULL THEN
301 BEGIN
302 SELECT ccd.cc_det_pf_line_id INTO P_Parent_Det_Pf_Id
303 FROM igc_cc_det_pf ccd, igc_cc_det_pf_interface ccdi
304 WHERE ccdi.interface_det_pf_line_id = P_Interface_Parent_Det_Pf_Id
305 AND ccd.cc_acct_line_id = P_Parent_Acct_Line_Id
306 AND ccdi.cc_det_pf_line_num = ccd.cc_det_pf_line_num;
307 EXCEPTION WHEN OTHERS THEN RAISE;
308 END;
309 END IF;
310 END;
311
312 -- 1833267, Additional Date Validations changes
313 -- Bidisha S , 23 Aug 2001 - Start
314
315 -- This procedure gets the setup flags
316 -- Added the 5 encumbrance_type_ids for bug 2871052
317 PROCEDURE get_setup_flags (p_set_of_books_id IN NUMBER,
318 p_org_id IN NUMBER,
319 p_cc_bc_enable_flag IN OUT NOCOPY VARCHAR2,
320 p_sbc_enable_flag IN OUT NOCOPY VARCHAR2,
321 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
322 -- p_cc_prov_encmbrnc_flag IN OUT NOCOPY VARCHAR2,
323 -- p_cc_conf_encmbrnc_flag IN OUT NOCOPY VARCHAR2,
324 p_sb_prov_encmbrnc_flag IN OUT NOCOPY VARCHAR2,
325 p_sb_conf_encmbrnc_flag IN OUT NOCOPY VARCHAR2
326 -- ,
327 -- p_prov_encumbrance_type_id IN OUT NOCOPY NUMBER,
328 -- p_conf_encumbrance_type_id IN OUT NOCOPY NUMBER,
329 -- p_req_encumbrance_type_id IN OUT NOCOPY NUMBER,
330 -- p_purch_encumbrance_type_id IN OUT NOCOPY NUMBER,
331 -- p_inv_encumbrance_type_id IN OUT NOCOPY NUMBER
332 )
333 IS
334
335 l_ap_req_encmbrnc_type_id NUMBER;
336 l_ap_purch_encmbrnc_type_id NUMBER;
337
338 BEGIN
339
340 BEGIN
341 SELECT Nvl(cc_bc_enable_flag,'N')
342 INTO p_cc_bc_enable_flag
343 FROM igc_cc_bc_enable
344 WHERE set_of_books_id = p_set_of_books_id;
345
346 EXCEPTION
347 WHEN NO_DATA_FOUND
348 THEN
349 p_cc_bc_enable_flag := 'N';
350 END ;
351
352 BEGIN
353 SELECT NVL(enable_budgetary_control_flag,'N')
354 INTO p_sbc_enable_flag
355 FROM gl_sets_of_books
356 WHERE set_of_books_id = p_set_of_books_id;
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND
360 THEN
361 p_sbc_enable_flag := 'N';
362 END;
363
364 p_sb_prov_encmbrnc_flag := 'N';
365 p_sb_conf_encmbrnc_flag := 'N';
366
367 IF (NVL(p_sbc_enable_flag,'N') = 'Y')
368 THEN
369 BEGIN
370 /* Bug No : 6341012. SLA uptake. Encumbrance_flag can be directly retrieved from the table and Encumbrance_type_ids are not required*/
371 SELECT
372 -- req_encumbrance_type_id,
373 -- purch_encumbrance_type_id,
374 -- inv_encumbrance_type_id,
375 req_encumbrance_flag,
376 purch_encumbrance_flag
377 INTO
378 -- l_ap_req_encmbrnc_type_id,
379 -- l_ap_purch_encmbrnc_type_id,
380 -- p_inv_encumbrance_type_id,
381 p_sb_prov_encmbrnc_flag,
382 p_sb_conf_encmbrnc_flag
383 FROM financials_system_parameters;
384
385 /* IF l_ap_req_encmbrnc_type_id IS NOT NULL
386 THEN
387 p_req_encumbrance_type_id := l_ap_req_encmbrnc_type_id;
388 END IF;
389
390 IF l_ap_purch_encmbrnc_type_id IS NOT NULL
391 THEN
392 p_purch_encumbrance_type_id := l_ap_purch_encmbrnc_type_id;
393 END IF;
394 */
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND
398 THEN
399 p_sb_prov_encmbrnc_flag := 'N';
400 p_sb_conf_encmbrnc_flag := 'N';
401 END;
402
403 END IF;
404 END get_setup_flags;
405
406 -- This function validates that the date is within an Open or Future Entry
407 FUNCTION date_in_valid_period (p_date IN DATE,
408 p_org_id IN NUMBER,
409 p_set_of_books_id IN NUMBER)
410 RETURN BOOLEAN
411 IS
412
413 l_count NUMBER;
414
415 BEGIN
416 -- Validate that the date is within an Open or Future Entry
417 -- GL / CC Period
418 -- Performance Tuning, replaced gl_period_statuses_v
419 -- gl_period_statuses_v gl,
420 SELECT count(*)
421 INTO l_COUNT
422 FROM fnd_application app,
423 gl_sets_of_books sob,
424 gl_period_statuses gl,
425 igc_cc_periods cp
426 WHERE sob.set_of_books_id = p_set_of_books_id
427 AND gl.set_of_books_id = sob.set_of_books_id
428 AND gl.application_id = app.application_id
429 AND app.application_short_name = 'SQLGL'
430 AND cp.org_id = p_org_id
431 AND cp.period_set_name = sob.period_set_name
432 AND cp.period_name = gl.period_name
433 AND cp.cc_period_status IN ('O', 'F')
434 AND gl.closing_status IN ('O', 'F')
435 AND gl.adjustment_period_flag = 'N'
436 AND (p_date BETWEEN gl.start_date AND gl.end_date);
437
438 IF l_count = 0
439 THEN
440 RETURN FALSE;
441 ELSE
442 RETURN TRUE;
443 END IF;
444
445 EXCEPTION
446 WHEN OTHERS THEN
447 RETURN FALSE;
448 END date_in_valid_period;
449
450 -- This procedure Validates Start Date
451 PROCEDURE validate_start_date (p_interface_header_id IN NUMBER,
452 /* Bug No : 6341012. p_interface_parent_header_id, p_cc_encmbrnc_status,p_sbc_enable_flag,p_cbc_enable_flag, are not used in this procedure*/
453 -- p_interface_parent_header_id IN NUMBER,
454 p_org_id IN NUMBER,
455 p_set_of_books_id IN NUMBER,
456 p_cc_type IN igc_cc_headers.cc_type%TYPE,
457 -- p_cc_encmbrnc_status IN igc_cc_headers.cc_encmbrnc_status%TYPE,
458 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
459 -- p_cc_prov_encmbrnc_flag IN VARCHAR2,
460 -- p_cc_conf_encmbrnc_flag IN VARCHAR2,
461 -- p_sbc_enable_flag IN VARCHAR2,
462 -- p_cbc_enable_flag IN VARCHAR2,
463 p_cc_start_date IN igc_cc_headers.cc_start_date%TYPE,
464 p_cc_end_date IN igc_cc_headers.cc_start_date%TYPE,
465 p_x_error_status IN OUT NOCOPY VARCHAR2)
466 IS
467 l_error_message VARCHAR2(2000);
468 l_valid BOOLEAN := TRUE;
469 l_min_rel_start_date DATE;
470
471 BEGIN
472
473 -- Start Date cannot be null
474 IF p_cc_start_date IS NULL
475 THEN
476 l_valid := FALSE;
477 l_error_message := NULL;
478 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_START_DATE_REQD');
479 l_error_message := FND_MESSAGE.GET;
480
481 INTERFACE_HANDLE_ERRORS
482 ( P_Interface_Header_Id,
483 NULL,
484 NULL,
485 P_Org_Id,
486 P_Set_of_Books_Id,
487 l_error_message,
488 P_X_Error_Status);
489
490 END IF; -- start date cannot be null
491
492 -- Start Date must be lesser than End Date
493 IF p_cc_end_date IS NOT NULL
494 AND p_cc_start_date > p_cc_end_date
495 AND l_valid
496 THEN
497 l_valid := FALSE;
498 l_error_message := NULL;
499 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_START_DT_GREATER_END_DT');
500 FND_MESSAGE.SET_TOKEN('START_DT', TO_CHAR(P_Cc_Start_Date, 'DD-MON-YYYY'), TRUE);
501 FND_MESSAGE.SET_TOKEN('END_DT', TO_CHAR(P_Cc_End_Date, 'DD-MON-YYYY'), TRUE);
502 l_error_message := FND_MESSAGE.GET;
503
504 INTERFACE_HANDLE_ERRORS
505 ( P_Interface_Header_Id,
506 NULL,
507 NULL,
508 P_Org_Id,
509 P_Set_of_Books_Id,
510 l_error_message,
511 P_X_Error_Status);
512 END IF; -- Start Date must be lesser than End Date
513
514 -- Start Date must be within open/ future entry CC / GL Period
515 IF NOT date_in_valid_period (p_cc_start_date,
516 p_org_id,
517 p_set_of_books_id)
518 AND p_cc_type <> 'R'
519 AND l_valid
520 THEN
521 l_valid := FALSE;
522 l_error_message := NULL;
523 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_START_DATE_OF');
524 l_error_message := FND_MESSAGE.GET;
525
526 INTERFACE_HANDLE_ERRORS
527 ( P_Interface_Header_Id,
528 NULL,
529 NULL,
530 P_Org_Id,
531 P_Set_of_Books_Id,
532 l_error_message,
533 P_X_Error_Status);
534
535 END IF; -- date not in valid period
536
537
538 -- For Cover CC's
539 -- Start >= Start Date of related CC's
540 IF p_cc_type = 'C'
541 AND l_valid
542 THEN
543 SELECT MIN(cc_start_date)
544 INTO l_min_rel_start_date
545 FROM igc_cc_headers_interface
546 WHERE interface_parent_header_id = p_interface_header_id;
547
548 -- Check Start date of cover < earliest release start date
549 IF NVL(l_min_rel_start_date, p_cc_start_date) < p_cc_start_date
550 THEN
551 l_valid := FALSE;
552 l_error_message := NULL;
553 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_SD_COV_GT_SD_REL');
554 FND_MESSAGE.SET_TOKEN('START_DATE_REL',
555 TO_CHAR(l_min_rel_start_date, 'DD-MON-YYYY'), TRUE);
556 FND_MESSAGE.SET_TOKEN('START_DATE_COV',
557 TO_CHAR(p_cc_start_date, 'DD-MON-YYYY'), TRUE);
558 l_error_message := FND_MESSAGE.GET;
559
560 INTERFACE_HANDLE_ERRORS
561 ( P_Interface_Header_Id,
562 NULL,
563 NULL,
564 P_Org_Id,
565 P_Set_of_Books_Id,
566 l_error_message,
567 P_X_Error_Status);
568
569 END IF;
570 END IF; -- Start date for Cover CC
571
572 -- For Release CC's
573 -- Start >= Start Date of Cover CC's
574 -- This is done in the header_interface_validate procedure
575
576 END validate_start_date;
577
578
579 -- This procedure Validates End Date
580 PROCEDURE validate_end_date (p_interface_header_id IN NUMBER,
581 /* Bug No : 6341012. p_interface_parent_header_id, p_cc_encmbrnc_status,p_sbc_enable_flag,
582 p_cbc_enable_flag,p_cbc_start_date are not used in this procedure*/
583 -- p_interface_parent_header_id IN NUMBER,
584 p_org_id IN NUMBER,
585 p_set_of_books_id IN NUMBER,
586 p_cc_type IN igc_cc_headers.cc_type%TYPE,
587 -- p_cc_encmbrnc_status IN igc_cc_headers.cc_encmbrnc_status%TYPE,
588 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
589 -- p_cc_prov_encmbrnc_flag IN VARCHAR2,
590 -- p_cc_conf_encmbrnc_flag IN VARCHAR2,
591 -- p_sbc_enable_flag IN VARCHAR2,
592 -- p_cbc_enable_flag IN VARCHAR2,
593 -- p_cc_start_date IN igc_cc_headers.cc_start_date%TYPE,
594 p_cc_end_date IN igc_cc_headers.cc_start_date%TYPE,
595 p_x_error_status IN OUT NOCOPY VARCHAR2)
596 IS
597 l_error_message VARCHAR2(2000);
598 l_valid BOOLEAN := TRUE;
599 l_max_rel_end_date DATE;
600
601 BEGIN
602
603 -- For release commitments,
604 -- end date >= date of related cover commitment
605 -- Done in main procedure
606
607 -- For cover commitments,
608 -- end date >= date of related release commitment
609 IF p_cc_type = 'C'
610 THEN
611 SELECT MAX(cc_end_date)
612 INTO l_max_rel_end_date
613 FROM igc_cc_headers_interface
614 WHERE interface_parent_header_id = p_interface_header_id;
615
616 -- Check End date of cover > latest release end date
617 IF NVL(l_max_rel_end_date, p_cc_end_date) > p_cc_end_date
618 THEN
619 l_valid := FALSE;
620 l_error_message := NULL;
621 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ED_COV_LESS_ED_REL');
622 FND_MESSAGE.SET_TOKEN('END_DATE_REL',
623 TO_CHAR(l_max_rel_end_date, 'DD-MON-YYYY'), TRUE);
624 FND_MESSAGE.SET_TOKEN('END_DATE_COV',
625 TO_CHAR(p_cc_end_date, 'DD-MON-YYYY'), TRUE);
626 l_error_message := FND_MESSAGE.GET;
627
628 INTERFACE_HANDLE_ERRORS
629 ( P_Interface_Header_Id,
630 NULL,
631 NULL,
632 P_Org_Id,
633 P_Set_of_Books_Id,
634 l_error_message,
635 P_X_Error_Status);
636
637 END IF; -- End Date Check
638 END IF; -- CC Type
639
640 END validate_end_date;
641
642
643 -- This procedure Validates Accountant Date
644 PROCEDURE validate_acct_date(p_interface_header_id IN NUMBER,
645 p_interface_parent_header_id IN NUMBER,
646 p_interface_acct_line_id IN NUMBER,
647 p_org_id IN NUMBER,
648 p_set_of_books_id IN NUMBER,
649 p_cc_type IN igc_cc_headers.cc_type%TYPE,
650 p_cc_state IN igc_cc_headers.cc_state%TYPE,
651 p_cc_encmbrnc_status IN igc_cc_headers.cc_encmbrnc_status%TYPE,
652 p_cc_apprvl_status IN igc_cc_headers.cc_apprvl_status%TYPE,
653 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
654 -- p_cc_prov_encmbrnc_flag IN VARCHAR2,
655 -- p_cc_conf_encmbrnc_flag IN VARCHAR2,
656 p_sbc_enable_flag IN VARCHAR2,
657 p_cbc_enable_flag IN VARCHAR2,
658 p_cc_start_date IN igc_cc_headers.cc_start_date%TYPE,
659 p_cc_end_date IN igc_cc_headers.cc_start_date%TYPE,
660 p_cc_acct_date IN DATE,
661 p_x_error_status IN OUT NOCOPY VARCHAR2)
662 IS
663 l_error_message VARCHAR2(2000);
664 l_valid BOOLEAN := TRUE;
665 BEGIN
666
667 IF p_cc_apprvl_status = 'AP'
668 AND p_sbc_enable_flag = 'Y' AND p_cbc_enable_flag = 'Y'
669 AND ((
670 -- p_cc_conf_encmbrnc_flag = 'Y' Bug No : 6341012. SLA uptake. CC_CONF_ENCUMBRANCE_FLAG no more exists
671 p_cc_state = 'CM'
672 AND p_cc_encmbrnc_status = 'C')
673 OR (
674 -- p_cc_prov_encmbrnc_flag = 'Y' Bug No : 6341012. SLA uptake. CC_PROV_ENCUMBRANCE_FLAG no more exists
675 p_cc_state = 'PR'
676 AND p_cc_encmbrnc_status = 'P'))
677 AND p_cc_type <> 'R'
678 THEN
679 -- Account date must not be null if Approved and encumbered
680 IF p_cc_acct_date IS NULL
681 THEN
682 l_valid := FALSE;
683 l_error_message := NULL;
684 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACCT_DATE_NULL');
685 l_error_message := FND_MESSAGE.GET;
686
687 INTERFACE_HANDLE_ERRORS
688 ( P_Interface_Header_Id,
689 P_Interface_Acct_Line_Id,
690 NULL,
691 P_Org_Id,
692 P_Set_of_Books_Id,
693 l_error_message,
694 P_X_Error_Status);
695
696 END IF;
697 END IF; -- Account Date must not be null
698
699 -- For others
700 -- Accounting Date should be null
701
702 IF Nvl(p_cbc_enable_flag,'N') = 'N'
703 OR p_cc_type = 'R'
704 /* Bug No : 6341012. SLA uptake. CC_PROV_ENCUMBRANCE_FLAG,CC_PROV_ENCUMBRANCE_FLAG no more exists
705 OR (p_cc_state = 'CM' AND Nvl(p_cc_conf_encmbrnc_flag,'N') = 'N')
706 OR (p_cc_state = 'PR' AND Nvl(p_cc_prov_encmbrnc_flag,'N') = 'N') */
707 THEN
708 -- Accounting Date must be null
709 IF p_cc_acct_date IS NOT NULL
710 THEN
711 l_valid := FALSE;
712 l_error_message := NULL;
713 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACCT_DATE_NOT_NULL');
714 l_error_message := FND_MESSAGE.GET;
715
716 INTERFACE_HANDLE_ERRORS
717 ( P_Interface_Header_Id,
718 P_Interface_Acct_Line_Id,
719 NULL,
720 P_Org_Id,
721 P_Set_of_Books_Id,
722 l_error_message,
723 P_X_Error_Status);
724
725 END IF;
726 END IF ; -- Accounting Date Must be null
727
728
729 IF p_cc_acct_date IS NOT NULL
730 AND l_valid
731 THEN
732 -- Acct Date should be between Start Date and End Date
733 IF p_cc_acct_date NOT BETWEEN p_cc_start_date
734 AND Nvl(p_cc_end_date, g_high_date)
735 THEN
736 l_valid := FALSE;
737 l_error_message := NULL;
738 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACCOUNT_DT_OUT_OF_RANGE');
739 FND_MESSAGE.SET_TOKEN('ACCT_DT',
740 TO_CHAR(p_cc_acct_date, 'DD-MON-YYYY'), TRUE);
741 FND_MESSAGE.SET_TOKEN('START_DATE',
742 TO_CHAR(p_cc_start_date, 'DD-MON-YYYY'), TRUE);
743 FND_MESSAGE.SET_TOKEN('END_DATE',
744 TO_CHAR(p_cc_end_date, 'DD-MON-YYYY'), TRUE);
745 l_error_message := FND_MESSAGE.GET;
746
747 INTERFACE_HANDLE_ERRORS
748 ( P_Interface_Header_Id,
749 P_Interface_Acct_Line_Id,
750 NULL,
751 P_Org_Id,
752 P_Set_of_Books_Id,
753 l_error_message,
754 P_X_Error_Status);
755 END IF; -- acct date within start and end dates
756
757
758 -- Acct Date should be within Open / Future Entry CC / GL Period
759 IF NOT date_in_valid_period (p_cc_acct_date,
760 p_org_id,
761 p_set_of_books_id)
762 AND l_valid
763 THEN
764 l_valid := FALSE;
765 l_error_message := NULL;
766 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACCT_DATE_OF');
767 l_error_message := FND_MESSAGE.GET;
768
769 INTERFACE_HANDLE_ERRORS
770 ( P_Interface_Header_Id,
771 P_Interface_Acct_Line_Id,
772 NULL,
773 P_Org_Id,
774 P_Set_of_Books_Id,
775 l_error_message,
776 P_X_Error_Status);
777
778 END IF; -- date not in valid period
779
780 End IF ; -- Acct Date not null
781
782
783 END validate_acct_date;
784
785
786 PROCEDURE validate_enc_acct_date (
787 p_interface_header_id IN NUMBER,
788 p_interface_acct_Line_Id IN NUMBER,
789 p_org_id IN NUMBER,
790 p_set_of_books_id IN NUMBER,
791 p_cc_type IN igc_cc_headers.cc_type%TYPE,
792 p_cc_state IN igc_cc_headers.cc_state%TYPE,
793 p_cc_encmbrnc_status IN igc_cc_headers.cc_encmbrnc_status%TYPE,
794 p_cc_apprvl_status IN igc_cc_headers.cc_apprvl_status%TYPE,
795 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
796 -- p_cc_prov_encmbrnc_flag IN VARCHAR2,
797 -- p_cc_conf_encmbrnc_flag IN VARCHAR2,
798 p_sbc_enable_flag IN VARCHAR2,
799 p_cbc_enable_flag IN VARCHAR2,
800 p_cc_start_date IN igc_cc_headers.cc_start_date%TYPE,
801 p_cc_end_date IN igc_cc_headers.cc_start_date%TYPE,
802 p_cc_acct_date IN DATE,
803 p_cc_encmbrnc_acct_date IN DATE,
804 p_x_error_status IN OUT NOCOPY VARCHAR2)
805 IS
806 l_error_message VARCHAR2(2000);
807 l_valid BOOLEAN := TRUE;
808 BEGIN
809
810 IF p_cc_apprvl_status = 'AP'
811 AND p_sbc_enable_flag = 'Y' AND p_cbc_enable_flag = 'Y'
812 AND ((
813 -- p_cc_conf_encmbrnc_flag = 'Y' Bug No : 6341012. SLA uptake. CC_CONF_ENCUMBRANCE_FLAG no more exists
814 p_cc_state = 'CM'
815 AND p_cc_encmbrnc_status = 'C')
816 OR (
817 -- p_cc_prov_encmbrnc_flag = 'Y' Bug No : 6341012. SLA uptake. CC_PROV_ENCUMBRANCE_FLAG no more exists
818 p_cc_state = 'PR'
819 AND p_cc_encmbrnc_status = 'P'))
820 AND p_cc_type <> 'R'
821 THEN
822 -- Encumbrance Account date must not be null
823 IF p_cc_encmbrnc_acct_date IS NULL
824 THEN
825 l_valid := FALSE;
826 l_error_message := NULL;
827 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACNT_ENCUM_DT_IS_NULL');
828 l_error_message := FND_MESSAGE.GET;
829
830 INTERFACE_HANDLE_ERRORS
831 ( P_Interface_Header_Id,
832 P_Interface_Acct_Line_Id,
833 NULL,
834 P_Org_Id,
835 P_Set_of_Books_Id,
836 l_error_message,
837 P_X_Error_Status);
838
839 END IF;
840 END IF; -- Enc Account Date must not be null
841
842 -- For others
843 -- Encumbrance Accounting Date should be null
844 IF Nvl(p_cbc_enable_flag,'N') = 'N'
845 OR p_cc_type = 'R'
846 /* Bug No : 6341012. SLA uptake. CC_PROV_ENCUMBRANCE_FLAG,CC_PROV_ENCUMBRANCE_FLAG no more exists
847 OR (p_cc_state = 'CM' AND Nvl(p_cc_conf_encmbrnc_flag,'N') = 'N')
848 OR (p_cc_state = 'PR' AND Nvl(p_cc_prov_encmbrnc_flag,'N') = 'N') */
849 THEN
850 -- Accounting Date must be null
851 IF p_cc_encmbrnc_acct_date IS NOT NULL
852 THEN
853 l_valid := FALSE;
854 l_error_message := NULL;
855 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENC_ACCT_DATE_NOT_NULL');
856 l_error_message := FND_MESSAGE.GET;
857
858 INTERFACE_HANDLE_ERRORS
859 ( P_Interface_Header_Id,
860 P_Interface_Acct_Line_Id,
861 NULL,
862 P_Org_Id,
863 P_Set_of_Books_Id,
864 l_error_message,
865 P_X_Error_Status);
866
867 END IF;
868 END IF ; -- Enc Accounting Date Must be null
869
870
871 IF p_cc_encmbrnc_acct_date IS NOT NULL
872 AND l_valid
873 THEN
874 -- Enc Acct Date must be <= Acct Date
875 IF p_cc_encmbrnc_acct_date > p_cc_acct_date
876 THEN
877 l_valid := FALSE;
878 l_error_message := NULL;
879 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENC_DT_GT_ACCT_DT');
880 l_error_message := FND_MESSAGE.GET;
881
882 INTERFACE_HANDLE_ERRORS
883 ( P_Interface_Header_Id,
884 P_Interface_Acct_Line_Id,
885 NULL,
886 P_Org_Id,
887 P_Set_of_Books_Id,
888 l_error_message,
889 P_X_Error_Status);
890 END IF; -- acct date > Enc acct date
891
892 End IF ; -- Acct Date not null
893
894 END validate_enc_acct_date;
895
896
897 -- This procedure validates the Payment Forecast Date
898 PROCEDURE validate_pf_date(
899 p_interface_header_id IN NUMBER,
900 p_org_id IN NUMBER,
901 p_set_of_books_id IN NUMBER,
902 p_cc_type IN igc_cc_headers.cc_type%TYPE,
903 p_cc_encmbrnc_status IN igc_cc_headers.cc_encmbrnc_status%TYPE,
904 /* Bug No : 6341012. SLA uptake. cc_flags no more exists */
905 -- p_cc_prov_encmbrnc_flag IN VARCHAR2,
906 -- p_cc_conf_encmbrnc_flag IN VARCHAR2,
907 p_sbc_enable_flag IN VARCHAR2,
908 p_cbc_enable_flag IN VARCHAR2,
909 p_interface_acct_line_id IN VARCHAR2,
910 p_interface_det_pf_id IN VARCHAR2,
911 p_interface_parent_det_pf_id IN NUMBER,
912 p_cc_det_pf_date IN DATE,
913 p_cc_start_date IN igc_cc_headers.cc_start_date%TYPE,
914 p_cc_end_date IN igc_cc_headers.cc_start_date%TYPE,
915 p_x_error_status IN OUT NOCOPY VARCHAR2)
916 IS
917 l_cover_cc_det_pf_date DATE;
918 l_error_message VARCHAR2(2000);
919 l_valid BOOLEAN := TRUE;
920 BEGIN
921
922 -- Pf Date cannot be null
923 IF p_cc_det_pf_date IS NULL
924 THEN
925 l_valid := FALSE;
926 l_error_message := NULL;
927 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PF_DATE_NULL');
928 l_error_message := FND_MESSAGE.GET;
929
930 INTERFACE_HANDLE_ERRORS
931 ( P_Interface_Header_Id,
932 P_Interface_Acct_Line_Id,
933 P_Interface_Det_Pf_Id,
934 P_Org_Id,
935 P_Set_of_Books_Id,
936 l_error_message,
937 P_X_Error_Status);
938 END IF; -- PF Date cannot be null
939
940 -- PF Date should be between Start Date and End Date
941 IF p_cc_det_pf_date NOT BETWEEN p_cc_start_date
942 AND Nvl(p_cc_end_date, g_high_date)
943 AND l_valid
944 THEN
945 l_valid := FALSE;
946 l_error_message := NULL;
947 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_FRCT_DT_NOT_IN_START_DT');
948 FND_MESSAGE.SET_TOKEN('PF_DATE',
949 TO_CHAR(p_cc_det_pf_date, 'DD-MON-YYYY'), TRUE);
950 FND_MESSAGE.SET_TOKEN('START_DT',
951 TO_CHAR(p_cc_start_date, 'DD-MON-YYYY'), TRUE);
952 FND_MESSAGE.SET_TOKEN('END_DT',
953 TO_CHAR(p_cc_end_date, 'DD-MON-YYYY'), TRUE);
954 l_error_message := FND_MESSAGE.GET;
955
956 INTERFACE_HANDLE_ERRORS
957 ( P_Interface_Header_Id,
958 P_Interface_Acct_Line_Id,
959 P_Interface_Det_Pf_Id,
960 P_Org_Id,
961 P_Set_of_Books_Id,
962 l_error_message,
963 P_X_Error_Status);
964 END IF; -- acct date within start and end dates
965
966
967 -- PF Date should be within Open / Future Entry CC / GL Period
968 IF p_cc_type <> 'R'
969 AND l_valid
970 AND NOT date_in_valid_period (p_cc_det_pf_date,
971 p_org_id,
972 p_set_of_books_id)
973 THEN
974 l_valid := FALSE;
975 l_error_message := NULL;
976 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PAY_FRCT_DT_NOT_IN_LMT');
977 FND_MESSAGE.SET_TOKEN('DET_PF_DATE',
978 TO_CHAR(p_cc_det_pf_date, 'DD-MON-YYYY'), TRUE);
979 l_error_message := FND_MESSAGE.GET;
980
981 INTERFACE_HANDLE_ERRORS
982 ( P_Interface_Header_Id,
983 P_Interface_Acct_Line_Id,
984 P_Interface_Det_Pf_Id,
985 P_Org_Id,
986 P_Set_of_Books_Id,
987 l_error_message,
988 P_X_Error_Status);
989
990 END IF; -- date not in valid period
991
992 IF p_cc_type = 'R'
993 AND l_valid
994 THEN
995 -- Get the PF date of the Cover CC
996 BEGIN
997 SELECT cc_det_pf_date
998 INTO l_cover_cc_det_pf_date
999 FROM igc_cc_det_pf_interface
1000 WHERE interface_det_pf_line_id = p_interface_parent_det_pf_id;
1001
1002 EXCEPTION
1003 WHEN NO_DATA_FOUND
1004 THEN
1005 l_cover_cc_det_pf_date := NULL;
1006 END;
1007
1008 IF l_cover_cc_det_pf_date <> p_cc_det_pf_date
1009 THEN
1010 l_valid := FALSE;
1011 l_error_message := NULL;
1012 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PAY_FCT_REL_DIFFERS_COV');
1013 FND_MESSAGE.SET_TOKEN('REL_PF_DATE',
1014 TO_CHAR(P_Cc_Det_Pf_Date, 'DD-MON-YYYY'), TRUE);
1015 FND_MESSAGE.SET_TOKEN('COV_PF_DATE',
1016 TO_CHAR(l_cover_cc_det_pf_date, 'DD-MON-YYYY'), TRUE);
1017 l_error_message := FND_MESSAGE.GET;
1018
1019 INTERFACE_HANDLE_ERRORS
1020 ( P_Interface_Header_Id,
1021 P_Interface_Acct_Line_Id,
1022 P_Interface_Det_Pf_Id,
1023 P_Org_Id,
1024 P_Set_of_Books_Id,
1025 l_error_message,
1026 P_X_Error_Status);
1027
1028 END IF;
1029
1030 END IF;
1031
1032 END validate_pf_date;
1033
1034
1035 -- This procedure validates the encumbrance payment forecast date.
1036 PROCEDURE validate_enc_pf_date (
1037 p_interface_header_id IN NUMBER,
1038 p_org_id IN NUMBER,
1039 p_set_of_books_id IN NUMBER,
1040 p_cc_type IN igc_cc_headers.cc_type%TYPE,
1041 p_cc_state IN igc_cc_headers.cc_state%TYPE,
1042 p_cc_encmbrnc_status IN igc_cc_headers.cc_encmbrnc_status%TYPE,
1043 p_cc_apprvl_status IN igc_cc_headers.cc_apprvl_status%TYPE,
1044 /* Bug No : 6341012. SLA uptake. cc_flags no more exists */
1045 -- p_cc_prov_encmbrnc_flag IN VARCHAR2,
1046 -- p_cc_conf_encmbrnc_flag IN VARCHAR2,
1047 p_sb_prov_encmbrnc_flag IN VARCHAR2,
1048 p_sb_conf_encmbrnc_flag IN VARCHAR2,
1049 p_sbc_enable_flag IN VARCHAR2,
1050 p_cbc_enable_flag IN VARCHAR2,
1051 p_interface_acct_line_id IN VARCHAR2,
1052 p_interface_det_pf_id IN VARCHAR2,
1053 p_interface_parent_det_pf_id IN NUMBER,
1054 p_cc_det_pf_date IN DATE,
1055 p_cc_det_pf_encmbrnc_date IN DATE,
1056 p_cc_start_date IN igc_cc_headers.cc_start_date%TYPE,
1057 p_cc_end_date IN igc_cc_headers.cc_start_date%TYPE,
1058 p_x_error_status IN OUT NOCOPY VARCHAR2)
1059 IS
1060 l_error_message VARCHAR2(2000);
1061 l_valid BOOLEAN := TRUE;
1062
1063 BEGIN
1064
1065 IF p_cc_apprvl_status = 'AP'
1066 AND ((p_sbc_enable_flag = 'Y' AND p_cbc_enable_flag = 'Y' -- Dual Bdgt Control
1067 AND ((
1068 -- p_cc_conf_encmbrnc_flag = 'Y' Bug No : 6341012. SLA uptake. CC_CONF_ENCUMBRANCE_FLAG no more exists
1069 p_cc_state = 'CM'
1070 AND p_cc_encmbrnc_status = 'C')
1071 OR (
1072 -- p_cc_prov_encmbrnc_flag = 'Y' Bug No : 6341012. SLA uptake. CC_PROV_ENCUMBRANCE_FLAG no more exists
1073 p_cc_state = 'PR'
1074 AND p_cc_encmbrnc_status = 'P')))
1075 OR
1076 (p_sbc_enable_flag = 'Y' AND p_cbc_enable_flag = 'N' -- Single Bdgt Control
1077 AND ((p_sb_conf_encmbrnc_flag = 'Y' AND p_cc_state = 'CM' )
1078 OR ( p_sb_prov_encmbrnc_flag = 'Y' AND p_cc_state = 'PR' ))))
1079
1080 THEN
1081
1082 -- CC Det Pf Enc date cannot be null
1083 IF p_cc_det_pf_encmbrnc_date IS NULL
1084 THEN
1085 l_valid := FALSE;
1086 l_error_message := NULL;
1087 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DTL_PAY_FRCT_ENC_DT_NUL');
1088 l_error_message := FND_MESSAGE.GET;
1089
1090 INTERFACE_HANDLE_ERRORS
1091 ( P_Interface_Header_Id,
1092 P_Interface_Acct_Line_Id,
1093 P_Interface_Det_Pf_Id,
1094 P_Org_Id,
1095 P_Set_of_Books_Id,
1096 l_error_message,
1097 P_X_Error_Status);
1098
1099 END IF;
1100
1101
1102 IF p_cc_det_pf_encmbrnc_date IS NOT NULL
1103 AND p_cc_det_pf_encmbrnc_date <> p_cc_det_pf_date
1104 AND l_valid
1105 THEN
1106
1107 l_valid := FALSE;
1108 l_error_message := NULL;
1109 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DT_PF_ENC_DT_DIFF_PFDT');
1110 FND_MESSAGE.SET_TOKEN('PF_ENCUM_DT', p_cc_det_pf_encmbrnc_date, TRUE);
1111 FND_MESSAGE.SET_TOKEN('PF_DATE', p_cc_det_pf_date, TRUE);
1112 l_error_message := FND_MESSAGE.GET;
1113
1114 INTERFACE_HANDLE_ERRORS
1115 ( P_Interface_Header_Id,
1116 P_Interface_Acct_Line_Id,
1117 P_Interface_Det_Pf_Id,
1118 P_Org_Id,
1119 P_Set_of_Books_Id,
1120 l_error_message,
1121 P_X_Error_Status);
1122 END IF; -- PF date <> PF Enc Date
1123
1124 ELSE
1125 -- Enc Pf Date must be null
1126 IF p_cc_det_pf_encmbrnc_date IS NOT NULL
1127 THEN
1128 l_valid := FALSE;
1129 l_error_message := NULL;
1130 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DTL_PF_ENC_DT_NOT_NUL');
1131 l_error_message := FND_MESSAGE.GET;
1132
1133 INTERFACE_HANDLE_ERRORS
1134 ( P_Interface_Header_Id,
1135 P_Interface_Acct_Line_Id,
1136 P_Interface_Det_Pf_Id,
1137 P_Org_Id,
1138 P_Set_of_Books_Id,
1139 l_error_message,
1140 P_X_Error_Status);
1141
1142 END IF;
1143
1144 END IF; -- Check for state of CC
1145
1146 END validate_enc_pf_date;
1147
1148
1149 -- 1833267, Additional Date Validations changes
1150 -- Bidisha S , 23 Aug 2001 - End
1151
1152
1153 -- Bug 2871052, Bidisha S Start
1154 -- Populate the PA plsql tables for Commitment bduget
1155 PROCEDURE populate_pa_table
1156 (p_budget_type IN VARCHAR2,
1157 p_cc_header_id IN igc_cc_headers.cc_header_id%TYPE,
1158 p_cc_acct_line_id IN igc_cc_acct_lines.cc_acct_line_id%TYPE,
1159 p_cc_det_pf_line_id IN igc_cc_det_pf.cc_det_pf_line_id%TYPE,
1160 p_cc_state IN igc_cc_headers.cc_state%TYPE,
1161 p_project_id IN igc_cc_acct_lines.project_id%TYPE,
1162 p_task_id IN igc_cc_acct_lines.task_id%TYPE,
1163 p_expenditure_type IN igc_cc_acct_lines.expenditure_type%TYPE,
1164 p_expenditure_item_date IN igc_cc_acct_lines.expenditure_item_date%TYPE,
1165 p_expenditure_org_id IN igc_cc_acct_lines.expenditure_org_id%TYPE,
1166 p_transaction_date IN DATE,
1167 p_encumbered_amt IN NUMBER,
1168 p_billed_amt IN NUMBER,
1169 p_txn_ccid IN NUMBER,
1170 p_sob_id IN NUMBER,
1171 p_org_id IN NUMBER)
1172 IS
1173
1174 CURSOR c_period_details (p_date DATE,
1175 p_sob_id NUMBER)
1176 IS
1177 SELECT gp.period_name,
1178 gp.period_num,
1179 gp.period_year
1180 FROM gl_periods gp,
1181 gl_sets_of_books sob
1182 WHERE gp.period_set_name = sob.period_set_name
1183 AND gp.period_type = sob.accounted_period_type
1184 AND sob.set_of_books_id = p_sob_id
1185 AND p_date BETWEEN gp.start_date AND gp.end_date
1186 AND gp.adjustment_period_flag = 'N';
1187
1188 l_index NUMBER;
1189
1190 BEGIN
1191 IF p_budget_type = 'CBC'
1192 THEN
1193 g_pa_fc_com_counter := g_pa_fc_com_counter + 1;
1194 l_index := g_pa_fc_com_counter;
1195 ELSE -- p_budget_type = 'GL'
1196 g_pa_fc_pay_counter := g_pa_fc_pay_counter + 1;
1197 l_index := g_pa_fc_pay_counter;
1198 END IF;
1199
1200 -- Set variables for Commitment Budget.
1201 IF p_budget_type = 'CBC'
1202 THEN
1203 g_pa_fc_com_rec_tab( l_index ).packet_id := g_bc_packet_id_com;
1204 g_pa_fc_com_rec_tab( l_index ).ext_budget_type := 'CC';
1205
1206 IF p_cc_state = 'PR' -- Provisional
1207 THEN
1208 g_pa_fc_com_rec_tab( l_index ).document_type := 'CC_P_CO';
1209 g_pa_fc_com_rec_tab( l_index ).je_category_name := 'Provisional';
1210 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs not required*/
1211 g_pa_fc_com_rec_tab( l_index ).encumbrance_type_id := null; --g_prov_encumbrance_type_id;
1212 g_pa_fc_com_rec_tab( l_index ).entered_dr := p_encumbered_amt;
1213 g_pa_fc_com_rec_tab( l_index ).accounted_dr := p_encumbered_amt;
1214 ELSIF p_cc_state = 'CM' -- Confirmed
1215 THEN
1216 g_pa_fc_com_rec_tab( l_index ).document_type := 'CC_C_CO';
1217 g_pa_fc_com_rec_tab( l_index ).je_category_name := 'Confirmed';
1218 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs not required*/
1219 g_pa_fc_com_rec_tab( l_index ).encumbrance_type_id := null; --g_conf_encumbrance_type_id;
1220 g_pa_fc_com_rec_tab( l_index ).entered_dr := p_encumbered_amt;
1221 g_pa_fc_com_rec_tab( l_index ).accounted_dr := p_encumbered_amt;
1222 END IF;
1223
1224 g_pa_fc_com_rec_tab( l_index ).document_distribution_id := p_cc_acct_line_id;
1225 OPEN c_period_details (p_transaction_date,
1226 p_sob_id);
1227 FETCH c_period_details INTO
1228 g_pa_fc_com_rec_tab( l_index ).period_name,
1229 g_pa_fc_com_rec_tab( l_index ).period_num,
1230 g_pa_fc_com_rec_tab( l_index ).period_year;
1231 CLOSE c_period_details;
1232
1233 g_pa_fc_com_rec_tab( l_index ).project_id := p_project_id;
1234 g_pa_fc_com_rec_tab( l_index ).task_id := p_task_id;
1235 g_pa_fc_com_rec_tab( l_index ).expenditure_type := p_expenditure_type;
1236 g_pa_fc_com_rec_tab( l_index ).expenditure_item_date := p_expenditure_item_date;
1237 g_pa_fc_com_rec_tab( l_index ).expenditure_organization_id := p_expenditure_org_id;
1238 g_pa_fc_com_rec_tab( l_index ).set_of_books_id := p_sob_id;
1239 g_pa_fc_com_rec_tab( l_index ).je_source_name := 'Contract Commitment';
1240 g_pa_fc_com_rec_tab( l_index ).status_code := 'P';
1241 g_pa_fc_com_rec_tab( l_index ).funds_process_mode := 'T';
1242 g_pa_fc_com_rec_tab( l_index ).document_header_id := p_cc_header_id;
1243 g_pa_fc_com_rec_tab( l_index ).burden_cost_flag := 'N';
1244 g_pa_fc_com_rec_tab( l_index ).balance_posted_flag := 'N';
1245 g_pa_fc_com_rec_tab( l_index ).actual_flag := 'E';
1246 g_pa_fc_com_rec_tab( l_index ).accounted_cr := 0;
1247 g_pa_fc_com_rec_tab( l_index ).entered_cr := 0;
1248 g_pa_fc_com_rec_tab( l_index ).txn_ccid := p_txn_ccid;
1249 g_pa_fc_com_rec_tab( l_index ).org_id := p_org_id;
1250 g_pa_fc_com_rec_tab( l_index ).last_update_date := SYSDATE;
1251 g_pa_fc_com_rec_tab( l_index ).last_updated_by := FND_GLOBAL.user_id;
1252 g_pa_fc_com_rec_tab( l_index ).created_by := FND_GLOBAL.user_id;
1253 g_pa_fc_com_rec_tab( l_index ).creation_date := SYSDATE;
1254 g_pa_fc_com_rec_tab( l_index ).last_update_login := FND_GLOBAL.login_id;
1255
1256 END IF; -- Commitment Budget
1257
1258 -- Set variables for Standard Budget.
1259 IF p_budget_type = 'GL'
1260 THEN
1261 g_pa_fc_pay_rec_tab( l_index ).packet_id := g_bc_packet_id_pay;
1262 g_pa_fc_pay_rec_tab( l_index ).ext_budget_type := 'GL';
1263
1264 IF p_cc_state = 'PR' -- Provisional
1265 THEN
1266 g_pa_fc_pay_rec_tab( l_index ).document_type := 'CC_P_PAY';
1267 g_pa_fc_pay_rec_tab( l_index ).je_category_name := 'Provisional';
1268 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs not required*/
1269 g_pa_fc_pay_rec_tab( l_index ).encumbrance_type_id := null; --g_req_encumbrance_type_id;
1270 g_pa_fc_pay_rec_tab( l_index ).entered_dr := p_encumbered_amt;
1271 g_pa_fc_pay_rec_tab( l_index ).accounted_dr := p_encumbered_amt;
1272 ELSIF p_cc_state = 'CM' -- Confirmed
1273 THEN
1274 g_pa_fc_pay_rec_tab( l_index ).document_type := 'CC_C_PAY';
1275 g_pa_fc_pay_rec_tab( l_index ).je_category_name := 'Confirmed';
1276 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs not required*/
1277 g_pa_fc_pay_rec_tab( l_index ).encumbrance_type_id := null; --g_purch_encumbrance_type_id;
1278 /* IF g_purch_encumbrance_type_id = g_inv_encumbrance_type_id
1279 THEN
1280 g_pa_fc_pay_rec_tab( l_index ).entered_dr := p_encumbered_amt;
1281 g_pa_fc_pay_rec_tab( l_index ).accounted_dr := p_encumbered_amt;
1282 ELSE */
1283 g_pa_fc_pay_rec_tab( l_index ).entered_dr := Nvl(p_encumbered_amt,0)-
1284 Nvl(p_billed_amt,0);
1285 g_pa_fc_pay_rec_tab( l_index ).accounted_dr := Nvl(p_encumbered_amt,0)-
1286 Nvl(p_billed_amt,0);
1287 -- END IF;
1288 END IF;
1289
1290 g_pa_fc_pay_rec_tab( l_index ).document_distribution_id := p_cc_det_pf_line_id;
1291 OPEN c_period_details (p_transaction_date,
1292 p_sob_id);
1293 FETCH c_period_details INTO
1294 g_pa_fc_pay_rec_tab( l_index ).period_name,
1295 g_pa_fc_pay_rec_tab( l_index ).period_num,
1296 g_pa_fc_pay_rec_tab( l_index ).period_year;
1297 CLOSE c_period_details;
1298
1299 g_pa_fc_pay_rec_tab( l_index ).project_id := p_project_id;
1300 g_pa_fc_pay_rec_tab( l_index ).task_id := p_task_id;
1301 g_pa_fc_pay_rec_tab( l_index ).expenditure_type := p_expenditure_type;
1302 g_pa_fc_pay_rec_tab( l_index ).expenditure_item_date := p_expenditure_item_date;
1303 g_pa_fc_pay_rec_tab( l_index ).expenditure_organization_id := p_expenditure_org_id;
1304 g_pa_fc_pay_rec_tab( l_index ).set_of_books_id := p_sob_id;
1305 g_pa_fc_pay_rec_tab( l_index ).je_source_name := 'Contract Commitment';
1306 g_pa_fc_pay_rec_tab( l_index ).status_code := 'P';
1307 g_pa_fc_pay_rec_tab( l_index ).funds_process_mode := 'T';
1308 g_pa_fc_pay_rec_tab( l_index ).document_header_id := p_cc_header_id;
1309 g_pa_fc_pay_rec_tab( l_index ).burden_cost_flag := 'N';
1310 g_pa_fc_pay_rec_tab( l_index ).balance_posted_flag := 'N';
1311 g_pa_fc_pay_rec_tab( l_index ).actual_flag := 'E';
1312 g_pa_fc_pay_rec_tab( l_index ).accounted_cr := 0;
1313 g_pa_fc_pay_rec_tab( l_index ).entered_cr := 0;
1314 g_pa_fc_pay_rec_tab( l_index ).txn_ccid := p_txn_ccid;
1315 g_pa_fc_pay_rec_tab( l_index ).org_id := p_org_id;
1316 g_pa_fc_pay_rec_tab( l_index ).last_update_date := SYSDATE;
1317 g_pa_fc_pay_rec_tab( l_index ).last_updated_by := FND_GLOBAL.user_id;
1318 g_pa_fc_pay_rec_tab( l_index ).created_by := FND_GLOBAL.user_id;
1319 g_pa_fc_pay_rec_tab( l_index ).creation_date := SYSDATE;
1320 g_pa_fc_pay_rec_tab( l_index ).last_update_login := FND_GLOBAL.login_id;
1321
1322 --Output_Debug('Project Id ' || g_pa_fc_pay_rec_tab( l_index ).project_id);
1323 --Output_Debug('task Id ' || g_pa_fc_pay_rec_tab( l_index ).task_id);
1324 --OUtput_Debug('Exp Type ' || g_pa_fc_pay_rec_tab( l_index ).expenditure_type);
1325 --Output_debug('Ecp date ' || g_pa_fc_pay_rec_tab( l_index ).expenditure_item_date);
1326 --Output_Debug('Exp Org ' || g_pa_fc_pay_rec_tab( l_index ).expenditure_organization_id);
1327
1328 -- bug 3199488, start block
1329 IF (l_state_level >= l_debug_level) THEN
1330 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.populate_pa_table.Msg1',
1331 'Project Id ' || g_pa_fc_pay_rec_tab( l_index ).project_id);
1332 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.populate_pa_table.Msg2',
1333 'task Id ' || g_pa_fc_pay_rec_tab( l_index ).task_id);
1334 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.populate_pa_table.Msg3',
1335 'Exp Type ' || g_pa_fc_pay_rec_tab( l_index ).expenditure_type);
1336 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.populate_pa_table.Msg4',
1337 'Ecp date ' || g_pa_fc_pay_rec_tab( l_index ).expenditure_item_date);
1338 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.populate_pa_table.Msg5',
1339 'Exp Org ' || g_pa_fc_pay_rec_tab( l_index ).expenditure_organization_id);
1340 END IF;
1341 -- bug 3199488, end block
1342
1343 END IF; -- Standard Budget.
1344
1345 END populate_pa_table;
1346
1347 -- Bug 2871052, Bidisha S End
1348
1349 /***************************************************************************/
1350 -- Main program which selects all the records from Header Interface table
1351 -- and calls other programs for processing
1352 /***************************************************************************/
1353 PROCEDURE HEADER_INTERFACE_MAIN
1354 ( ERRBUF OUT NOCOPY VARCHAR2,
1355 RETCODE OUT NOCOPY VARCHAR2,
1356 P_Process_Phase IN VARCHAR2,
1357 P_Batch_Id IN NUMBER)
1358 IS
1359 l_error_status VARCHAR2(1) DEFAULT 'N';
1360 l_current_org_id NUMBER;
1361 l_current_user_id NUMBER;
1362 l_current_login_id NUMBER;
1363 l_current_set_of_books_id NUMBER;
1364 /* Bug No : 6341012. MOAC uptake. Local variable for Set_of_books name*/
1365 l_sob_name VARCHAR2(30);
1366 l_row_id VARCHAR2(18);
1367 l_flag VARCHAR2(1);
1368 l_header_id NUMBER;
1369 l_parent_header_id NUMBER;
1370 l_parent_acct_line_id NUMBER;
1371 l_parent_det_pf_id NUMBER;
1372 l_cbc_enable_flag VARCHAR2(1);
1373 l_func_currency_code VARCHAR2(15);
1374 l_return_status VARCHAR2(1);
1375 l_msg_count NUMBER;
1376 l_msg_data VARCHAR2(12000);
1377 l_error_text VARCHAR2(12000);
1378 l_msg_buf VARCHAR2(2000);
1379 l_request_id NUMBER;
1380 l_interface_header_record igc_cc_headers_interface%ROWTYPE;
1381 l_start_date gl_periods.start_date%TYPE;
1382 l_end_date gl_periods.end_date%TYPE;
1383 l_curr_year_pf_lines NUMBER;
1384 l_error_message igc_cc_interface_errors.error_message%TYPE;
1385 l_history_message VARCHAR2(240);
1386 l_wait_for_request BOOLEAN;
1387 l_phase VARCHAR2(240);
1388 l_status VARCHAR2(240);
1389 l_dev_phase VARCHAR2(240);
1390 l_dev_status VARCHAR2(240);
1391 l_message VARCHAR2(240);
1392
1393 -- 01/03/02, CC enabled in IGI
1394 l_option_name VARCHAR2(80);
1395 lv_message VARCHAR2(1000);
1396
1397 -- For Bug 2871052
1398 l_cbc_return_code VARCHAR2(1) := 'S';
1399 l_pa_cb_funds_check_pass BOOLEAN := TRUE;
1400 l_pa_sb_funds_check_pass BOOLEAN := TRUE;
1401 l_error_msg VARCHAR2(2000);
1402
1403 --variables related to XML Report
1404 l_terr VARCHAR2(10):='US';
1405 l_lang VARCHAR2(10):='en';
1406 l_layout BOOLEAN;
1407
1408 CURSOR c_interface_header_records IS
1409 SELECT * FROM igc_cc_headers_interface
1410 WHERE batch_id = P_Batch_Id
1411 ORDER BY cc_type DESC;
1412
1413 -- Start Date and End Date of current fiscal year for set of books
1414 -- indicated by P_Sob_Id
1415
1416 CURSOR c_fiscal_year_dates(P_Sob_Id NUMBER)
1417 IS
1418 SELECT MIN(start_date) start_date, MAX(end_date) end_date
1419 FROM GL_PERIODS GP,
1420 GL_SETS_OF_BOOKS GB
1421 WHERE
1422 GP.period_set_name = GB.period_set_name AND
1423 GP.period_type = GB.accounted_period_type AND
1424 GB.set_of_books_id = P_Sob_Id AND
1425 TO_CHAR(start_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
1426 TO_CHAR(end_date, 'YYYY') = to_char(sysdate, 'YYYY') AND
1427 GP.adjustment_period_flag = 'N';
1428
1429 CURSOR c_cur_packet IS
1430 SELECT gl_bc_packets_s.nextval
1431 FROM dual;
1432
1433 BEGIN
1434
1435 -- 01/03/02, check to see if CC is installed
1436 IF NOT igi_gen.is_req_installed('CC') THEN
1437
1438 SELECT meaning
1439 INTO l_option_name
1440 FROM igi_lookups
1441 WHERE lookup_code = 'CC'
1442 AND lookup_type = 'GCC_DESCRIPTION';
1443
1444 FND_MESSAGE.SET_NAME('IGI', 'IGI_GEN_PROD_NOT_INSTALLED');
1445 FND_MESSAGE.SET_TOKEN('OPTION_NAME', l_option_name);
1446 lv_message := fnd_message.get;
1447 errbuf := lv_message;
1448 retcode := 2;
1449 return;
1450 END IF;
1451
1452
1453 --
1454 -- IF g_debug_flag = 'Y'
1455 -- THEN
1456 -- Output_Debug('Starting Open Interface Import process');
1457 -- bug 3199488, start block
1458 IF (l_state_level >= l_debug_level) THEN
1459 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg1',
1460 'Starting Open Interface Import process');
1461 END IF;
1462 -- bug 3199488, end block
1463 -- END IF;
1464 RETCODE := '0';
1465 g_process_phase := P_Process_Phase;
1466 g_batch_id := P_Batch_Id;
1467
1468 -- Delete all the old records from IGC_CC_INTERFACE_ERRORS
1469 DELETE IGC_CC_INTERFACE_ERRORS;
1470
1471 COMMIT;
1472
1473 -- Bug 2871052
1474 -- Clear out the PLSQL tables
1475 g_pa_fc_com_rec_tab.DELETE;
1476 g_pa_fc_pay_rec_tab.DELETE;
1477
1478 -- Generate 2 gl bc packet sequence
1479 -- 1 packet will contain all the records for the commitment budget
1480 -- The other packet will contain all records for the standard budget.
1481 OPEN c_cur_packet;
1482 FETCH c_cur_packet INTO g_bc_packet_id_com;
1483 CLOSE c_cur_packet;
1484
1485 OPEN c_cur_packet;
1486 FETCH c_cur_packet INTO g_bc_packet_id_pay;
1487 CLOSE c_cur_packet;
1488 -- 2871052, End
1489
1490 -- Get the profile values
1491 /* Bug No : 6341012. MOAC uptake. ORG_ID,SOB_ID are retrieved from packages rather than from profiles*/
1492 -- l_current_org_id := TO_NUMBER(FND_PROFILE.VALUE('ORG_ID'));
1493 l_current_org_id := MO_GLOBAL.get_current_org_id;
1494 l_current_user_id := TO_NUMBER(FND_PROFILE.VALUE('USER_ID'));
1495 l_current_login_id := TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'));
1496 -- l_current_set_of_books_id := TO_NUMBER(FND_PROFILE.VALUE('GL_SET_OF_BKS_ID'));
1497 MO_UTILS.get_ledger_info(l_current_org_id,l_current_set_of_books_id,l_sob_name);
1498
1499
1500 -- Get the Dual Budgetary Control Enable Flag
1501 BEGIN
1502 SELECT NVL(cc_bc_enable_flag,'N') INTO l_cbc_enable_flag
1503 FROM igc_cc_bc_enable
1504 WHERE set_of_books_id = l_current_set_of_books_id;
1505 EXCEPTION WHEN NO_DATA_FOUND THEN l_cbc_enable_flag := 'N';
1506 END;
1507
1508 -- Get the Functional Currency Code
1509 BEGIN
1510 SELECT currency_code INTO l_func_currency_code
1511 FROM gl_sets_of_books
1512 WHERE set_of_books_id = l_current_set_of_books_id;
1513 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
1514 END;
1515
1516 -- Get the start date and end date of current fiscal year
1517 OPEN c_fiscal_year_dates(l_current_set_of_books_id);
1518 FETCH c_fiscal_year_dates INTO l_start_date, l_end_date;
1519 CLOSE c_fiscal_year_dates;
1520
1521 -- get the setup flags. 1833267
1522 -- Added the 5 encumbrance type ids for 2871052
1523 get_setup_flags (p_set_of_books_id => l_current_set_of_books_id,
1524 p_org_id => l_current_org_id,
1525 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
1526 p_cc_bc_enable_flag => g_cc_bc_enable_flag,
1527 p_sbc_enable_flag => g_sbc_enable_flag,
1528 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
1529 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag,
1530 p_sb_prov_encmbrnc_flag => g_sb_prov_encmbrnc_flag,
1531 p_sb_conf_encmbrnc_flag => g_sb_conf_encmbrnc_flag
1532 -- ,
1533 -- p_prov_encumbrance_type_id => g_prov_encumbrance_type_id,
1534 -- p_conf_encumbrance_type_id => g_conf_encumbrance_type_id,
1535 -- p_req_encumbrance_type_id => g_req_encumbrance_type_id,
1536 -- p_purch_encumbrance_type_id => g_purch_encumbrance_type_id,
1537 -- p_inv_encumbrance_type_id => g_inv_encumbrance_type_id
1538 );
1539
1540 -- Process the header records one by one
1541 OPEN c_interface_header_records;
1542 LOOP
1543 FETCH c_interface_header_records INTO l_interface_header_record;
1544 EXIT WHEN c_interface_header_records%NOTFOUND;
1545
1546 HEADER_INTERFACE_VALIDATE
1547 ( l_interface_header_record.Interface_Header_Id,
1548 l_interface_header_record.Org_Id,
1549 l_interface_header_record.Cc_Type,
1550 l_interface_header_record.Cc_Num,
1551 l_interface_header_record.Cc_Version_Num,
1552 l_interface_header_record.Interface_Parent_Header_Id,
1553 l_interface_header_record.Cc_State,
1554 l_interface_header_record.Cc_Ctrl_Status,
1555 l_interface_header_record.Cc_Encmbrnc_Status,
1556 l_interface_header_record.Cc_Apprvl_Status,
1557 l_interface_header_record.Vendor_Id,
1558 l_interface_header_record.Vendor_Site_Id,
1559 l_interface_header_record.Vendor_Contact_Id,
1560 l_interface_header_record.Term_Id,
1561 l_interface_header_record.Location_Id,
1562 l_interface_header_record.Set_of_Books_Id,
1563 l_interface_header_record.Cc_Acct_Date,
1564 l_interface_header_record.Cc_Start_Date,
1565 l_interface_header_record.Cc_End_Date,
1566 l_interface_header_record.Cc_Owner_User_Id,
1567 l_interface_header_record.Cc_Preparer_User_Id,
1568 l_interface_header_record.Currency_Code,
1569 l_interface_header_record.Conversion_Type,
1570 l_interface_header_record.Conversion_Rate,
1571 l_interface_header_record.Conversion_Date,
1572 l_interface_header_record.Created_By,
1573 l_interface_header_record.CC_Guarantee_Flag,
1574 l_interface_header_record.cc_current_user_id,
1575 --Bug 2373685 l_current_user_id,
1576 l_error_status,
1577 l_current_org_id,
1578 l_current_set_of_books_id,
1579 l_func_currency_code,
1580 l_cbc_enable_flag);
1581
1582 -- If validation succeeds, get the derived values and insert header record.
1583 IF UPPER(g_process_phase) = 'F' AND UPPER(l_error_status) = 'N' THEN
1584
1585 HEADER_INTERFACE_DERIVE( l_header_id );
1586
1587 -- fix bug 2197872 start(1),
1588 -- l_parent_header_id, l_parent_acct_line_id and l_parent_det_pf_id should be
1589 -- null before procedure GET_PARENT_ID is called else their values will
1590 -- always remain the same
1591
1592 l_parent_header_id := NULL;
1593 l_parent_acct_line_id := NULL;
1594 l_parent_det_pf_id := NULL;
1595
1596 -- fix bug 2197872 end (1)
1597
1598 IF (l_interface_header_record.Cc_Type = 'R') THEN
1599 GET_PARENT_ID( l_interface_header_record.Interface_Parent_Header_Id,
1600 NULL,
1601 NULL,
1602 l_parent_header_id,
1603 l_parent_acct_line_id,
1604 l_parent_det_pf_id);
1605
1606 -- fix bug 2197872 start(2)
1607 -- ELSE
1608 -- l_parent_header_id := NULL;
1609 -- l_parent_acct_line_id := NULL;
1610 -- l_parent_det_pf_id := NULL;
1611 -- fix bug 2197872 end(2)
1612
1613 END IF;
1614
1615 IGC_CC_HEADERS_PKG.Insert_Row(
1616 1.0,
1617 FND_API.G_TRUE,
1618 FND_API.G_FALSE,
1619 FND_API.G_VALID_LEVEL_FULL,
1620 l_return_status,
1621 l_msg_count,
1622 l_msg_data,
1623 l_row_id,
1624 l_header_id,
1625 l_interface_header_record.Org_Id,
1626 l_interface_header_record.CC_Type,
1627 l_interface_header_record.CC_Num,
1628 NVL(l_interface_header_record.CC_Version_num, 0) + 1,
1629 l_parent_header_id,
1630 l_interface_header_record.CC_State,
1631 l_interface_header_record.CC_Ctrl_status,
1632 l_interface_header_record.CC_Encmbrnc_Status,
1633 l_interface_header_record.CC_Apprvl_Status,
1634 l_interface_header_record.Vendor_Id,
1635 l_interface_header_record.Vendor_Site_Id,
1636 l_interface_header_record.Vendor_Contact_Id,
1637 l_interface_header_record.Term_Id,
1638 l_interface_header_record.Location_Id,
1639 l_interface_header_record.Set_Of_Books_Id,
1640 l_interface_header_record.CC_Acct_Date,
1641 l_interface_header_record.CC_Desc,
1642 l_interface_header_record.CC_Start_Date,
1643 l_interface_header_record.CC_End_Date,
1644 l_interface_header_record.CC_Owner_User_Id,
1645 l_interface_header_record.CC_Preparer_User_Id,
1646 l_interface_header_record.Currency_Code,
1647 l_interface_header_record.Conversion_Type,
1648 l_interface_header_record.Conversion_Date,
1649 l_interface_header_record.Conversion_Rate,
1650 sysdate,
1651 l_current_user_id,
1652 l_current_login_id,
1653 NVL(l_interface_header_record.Created_By, l_current_user_id),
1654 NVL(l_interface_header_record.Creation_Date, sysdate),
1655 l_interface_header_record.CC_Current_User_Id,
1656 l_interface_header_record.Wf_Item_Type,
1657 l_interface_header_record.Wf_Item_Key,
1658 l_interface_header_record.Attribute1,
1659 l_interface_header_record.Attribute2,
1660 l_interface_header_record.Attribute3,
1661 l_interface_header_record.Attribute4,
1662 l_interface_header_record.Attribute5,
1663 l_interface_header_record.Attribute6,
1664 l_interface_header_record.Attribute7,
1665 l_interface_header_record.Attribute8,
1666 l_interface_header_record.Attribute9,
1667 l_interface_header_record.Attribute10,
1668 l_interface_header_record.Attribute11,
1669 l_interface_header_record.Attribute12,
1670 l_interface_header_record.Attribute13,
1671 l_interface_header_record.Attribute14,
1672 l_interface_header_record.Attribute15,
1673 l_interface_header_record.Context,
1674 Nvl(l_interface_header_record.CC_Guarantee_Flag,'N'),
1675 l_flag);
1676 IF l_return_status IN ('E','U') THEN
1677 l_msg_buf := ' ';
1678 FOR j IN 1..NVL(l_msg_count,0) LOOP
1679 BEGIN
1680 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
1681 p_encoded => 'F');
1682 INTERFACE_HANDLE_ERRORS
1683 ( l_interface_header_record.Interface_Header_id,
1684 NULL,
1685 NULL,
1686 l_interface_header_record.Org_Id,
1687 l_interface_header_record.Set_Of_Books_Id,
1688 l_msg_buf,
1689 l_error_status);
1690 END;
1691 END LOOP;
1692 END IF;
1693 END IF;
1694
1695 g_cc_state := l_interface_header_record.Cc_State;
1696 g_cc_apprvl_status := l_interface_header_record.Cc_apprvl_status;
1697
1698 -- Process the corresponding acct line records for the header record.
1699 ACCT_LINE_INTERFACE_MAIN
1700 ( l_interface_header_record.Interface_Header_Id,
1701 l_header_id,
1702 l_interface_header_record.Interface_Parent_Header_Id,
1703 l_parent_header_id,
1704 l_interface_header_record.Org_Id,
1705 l_interface_header_record.Set_of_Books_Id,
1706 l_interface_header_record.Cc_Type,
1707 l_interface_header_record.Cc_Encmbrnc_Status,
1708 l_interface_header_record.Cc_Start_Date,
1709 l_interface_header_record.Cc_End_Date,
1710 l_interface_header_record.Cc_Acct_Date,
1711 l_current_user_id,
1712 l_current_login_id,
1713 l_interface_header_record.CC_State,
1714 l_interface_header_record.CC_Apprvl_Status,
1715 l_error_status);
1716
1717 -- Create PO if all the reqd conditions are met.
1718 IF UPPER(g_process_phase) = 'F' AND UPPER(l_error_status) = 'N'
1719 AND l_interface_header_record.CC_State = 'CM'
1720 AND l_interface_header_record.CC_Apprvl_Status = 'AP'
1721 AND l_interface_header_record.CC_Type IN ('S','R') THEN
1722
1723 l_curr_year_pf_lines := 0;
1724
1725 -- Check whether current fiscal year payment forecast lines exist in CC
1726
1727 BEGIN
1728 -- Bug 2124447, payment forecast records for date < current fiscal year.
1729 -- Need not be only for current year
1730 -- WHERE ( b.cc_det_pf_date >= l_start_date
1731 -- AND b.cc_det_pf_date <= l_end_date)
1732
1733
1734 SELECT count(interface_det_pf_line_id)
1735 INTO l_curr_year_pf_lines
1736 FROM igc_cc_det_pf_interface b
1737 WHERE b.cc_det_pf_date <= l_end_date
1738 AND b.interface_acct_line_id IN (SELECT interface_acct_line_id
1739 FROM igc_cc_acct_lines_interface a
1740 WHERE a.interface_header_id = l_interface_header_record.Interface_Header_Id);
1741 EXCEPTION
1742 WHEN NO_DATA_FOUND
1743 THEN
1744 l_curr_year_pf_lines := 0;
1745 END;
1746
1747 IF (l_curr_year_pf_lines > 0) THEN
1748
1749 IGC_CC_PO_INTERFACE_PKG.CONVERT_CC_TO_PO
1750 ( 1.0,
1751 FND_API.G_TRUE,
1752 FND_API.G_FALSE,
1753 FND_API.G_VALID_LEVEL_FULL,
1754 l_return_status,
1755 l_msg_count,
1756 l_msg_data,
1757 l_header_id);
1758 IF l_return_status IN ('E','U') THEN
1759 l_msg_buf := ' ';
1760 FOR j IN 1..NVL(l_msg_count,0) LOOP
1761 BEGIN
1762 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
1763 p_encoded => 'F');
1764 INTERFACE_HANDLE_ERRORS
1765 ( l_interface_header_record.Interface_Header_id,
1766 NULL,
1767 NULL,
1768 l_interface_header_record.Org_Id,
1769 l_interface_header_record.Set_Of_Books_Id,
1770 l_msg_buf,
1771 l_error_status);
1772 END;
1773 END LOOP;
1774 END IF;
1775
1776 IF l_interface_header_record.CC_Ctrl_Status = 'O' AND UPPER(l_error_status) = 'N' THEN
1777 IGC_CC_PO_INTERFACE_PKG.UPDATE_PO_APPROVED_FLAG
1778 ( 1.0,
1779 FND_API.G_TRUE,
1780 FND_API.G_FALSE,
1781 FND_API.G_VALID_LEVEL_FULL,
1782 l_return_status,
1783 l_msg_count,
1784 l_msg_data,
1785 l_header_id);
1786 IF l_return_status IN ('E','U') THEN
1787 l_msg_buf := ' ';
1788 FOR j IN 1..NVL(l_msg_count,0) LOOP
1789 BEGIN
1790 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
1791 p_encoded => 'F');
1792 INTERFACE_HANDLE_ERRORS
1793 ( l_interface_header_record.Interface_Header_id,
1794 NULL,
1795 NULL,
1796 l_interface_header_record.Org_Id,
1797 l_interface_header_record.Set_Of_Books_Id,
1798 l_msg_buf,
1799 l_error_status);
1800 END;
1801 END LOOP;
1802 END IF;
1803 END IF;
1804 END IF;
1805 END IF;
1806
1807 -- Insert record into table IGC_CC_ACTIONS
1808 IF UPPER(g_process_phase) = 'F' AND UPPER(l_error_status) = 'N' THEN
1809 l_history_message := NULL;
1810 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENTERED_THRU_CCOI_PROG');
1811 l_history_message := FND_MESSAGE.GET;
1812 IGC_CC_ACTIONS_PKG.Insert_Row(
1813 1.0,
1814 FND_API.G_TRUE,
1815 FND_API.G_FALSE,
1816 FND_API.G_VALID_LEVEL_FULL,
1817 l_return_status,
1818 l_msg_count,
1819 l_msg_data,
1820 l_row_id,
1821 l_header_id,
1822 NVL(l_interface_header_record.CC_Version_num, 0) + 1,
1823 'EN',
1824 l_interface_header_record.CC_State,
1825 l_interface_header_record.CC_ctrl_status,
1826 l_interface_header_record.CC_Apprvl_Status,
1827 l_history_message,
1828 sysdate,
1829 l_current_user_id,
1830 l_current_login_id,
1831 sysdate,
1832 l_current_user_id );
1833 IF l_return_status IN ('E','U') THEN
1834 l_msg_buf := ' ';
1835 FOR j IN 1..NVL(l_msg_count,0) LOOP
1836 BEGIN
1837 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
1838 p_encoded => 'F');
1839 INTERFACE_HANDLE_ERRORS
1840 ( l_interface_header_record.Interface_Header_id,
1841 NULL,
1842 NULL,
1843 l_interface_header_record.Org_Id,
1844 l_interface_header_record.Set_Of_Books_Id,
1845 l_msg_buf,
1846 l_error_status);
1847 END;
1848 END LOOP;
1849 END IF;
1850
1851 IF UPPER(l_interface_header_record.CC_Encmbrnc_Status) IN ('P', 'C') AND UPPER(l_error_status) = 'N' THEN
1852 l_history_message := NULL;
1853 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENCMBRED_THRU_CCOI_PROG');
1854 l_history_message := FND_MESSAGE.GET;
1855 IGC_CC_ACTIONS_PKG.Insert_Row(
1856 1.0,
1857 FND_API.G_TRUE,
1858 FND_API.G_FALSE,
1859 FND_API.G_VALID_LEVEL_FULL,
1860 l_return_status,
1861 l_msg_count,
1862 l_msg_data,
1863 l_row_id,
1864 l_header_id,
1865 NVL(l_interface_header_record.CC_Version_num, 0) + 1,
1866 'EC',
1867 l_interface_header_record.CC_State,
1868 l_interface_header_record.CC_ctrl_status,
1869 l_interface_header_record.CC_Apprvl_Status,
1870 l_history_message,
1871 sysdate,
1872 l_current_user_id,
1873 l_current_login_id,
1874 sysdate,
1875 l_current_user_id );
1876 IF l_return_status IN ('E','U') THEN
1877 l_msg_buf := ' ';
1878 FOR j IN 1..NVL(l_msg_count,0) LOOP
1879 BEGIN
1880 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
1881 p_encoded => 'F');
1882 INTERFACE_HANDLE_ERRORS
1883 ( l_interface_header_record.Interface_Header_id,
1884 NULL,
1885 NULL,
1886 l_interface_header_record.Org_Id,
1887 l_interface_header_record.Set_Of_Books_Id,
1888 l_msg_buf,
1889 l_error_status);
1890 END;
1891 END LOOP;
1892 END IF;
1893
1894 END IF;
1895 END IF;
1896
1897 END LOOP;
1898 CLOSE c_interface_header_records;
1899
1900 -- Insert the orphan interface acct lines and det pf lines into
1901 -- igc_cc_interface_errors, if any.
1902 INSERT_ORPHAN_RECORDS
1903 ( l_error_status );
1904
1905 -- If process phase is final and no errors encountered
1906 -- then call the PA funds checker to check funds in PA Budget
1907 -- Bug 2871052
1908 IF UPPER(g_process_phase) = 'F' AND UPPER(l_error_status) = 'N'
1909 THEN
1910 IF g_pa_cb_funds_check_required
1911 THEN
1912 l_pa_cb_funds_check_pass := TRUE;
1913
1914 -- IF g_debug_flag = 'Y'
1915 -- THEN
1916 -- Output_Debug('Calling PA_cc_enc_import_fck.Load_pkts for ' ||
1917 -- ' packet id '|| to_char( g_bc_packet_id_com) ||
1918 -- ' for commitment budget');
1919 -- END IF;
1920
1921 -- bug 3199488, start block
1922 IF (l_state_level >= l_debug_level) THEN
1923 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg2',
1924 'Calling PA_cc_enc_import_fck.Load_pkts for ' ||
1925 ' packet id '|| to_char( g_bc_packet_id_com) ||
1926 ' for commitment budget');
1927 END IF;
1928 -- bug 3199488, end block
1929
1930 -- Call PA API to insert rows in pa_bc_packets
1931 -- This API loads the rows in an autonomous mode.
1932 PA_cc_enc_import_fck.Load_pkts(
1933 p_calling_module => 'CCTRXIMPORT',
1934 p_ext_budget_type => 'CC',
1935 p_packet_id => g_bc_packet_id_com,
1936 p_fc_rec_tab => g_pa_fc_com_rec_tab,
1937 x_return_status => l_return_status,
1938 x_error_msg => l_error_msg);
1939
1940 IF l_return_status = 'S'
1941 THEN
1942 -- IF g_debug_flag = 'Y'
1943 -- THEN
1944 -- Output_Debug('Calling PA_cc_enc_import_fck.Pa_enc_import_fck for ' ||
1945 -- ' packet id '|| to_char( g_bc_packet_id_com) ||
1946 -- ' for commitment budget');
1947 -- END IF;
1948
1949 -- bug 3199488, start block
1950 IF (l_state_level >= l_debug_level) THEN
1951 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg3',
1952 'Calling PA_cc_enc_import_fck.Pa_enc_import_fck for ' ||
1953 ' packet id '|| to_char( g_bc_packet_id_com) ||
1954 ' for commitment budget');
1955 END IF;
1956 -- bug 3199488, end block
1957
1958 -- Call PA Funds checker for all commitment records.
1959 pa_cc_enc_import_fck.Pa_enc_import_fck(
1960 p_calling_module => 'CCTRXIMPORT',
1961 p_ext_budget_type => 'CC',
1962 p_conc_flag => 'Y',
1963 p_set_of_book_id => l_current_set_of_books_id,
1964 p_packet_id => g_bc_packet_id_com,
1965 p_mode =>'R',
1966 p_partial_flag => 'N',
1967 x_return_status => l_return_status,
1968 x_error_msg => l_error_msg);
1969
1970
1971 IF l_return_status <> 'S'
1972 THEN
1973 l_pa_cb_funds_check_pass := FALSE;
1974
1975 -- IF g_debug_flag = 'Y'
1976 -- THEN
1977 -- Output_Debug('PA_cc_enc_import_fck.Pa_enc_import_fck ' ||
1978 -- ' failed for packet id '||
1979 -- to_char( g_bc_packet_id_com) ||
1980 -- ' for commitment budget with error - ' ||
1981 -- l_error_msg);
1982 -- END IF;
1983
1984 -- bug 3199488, start block
1985 IF (l_state_level >= l_debug_level) THEN
1986 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg4',
1987 'PA_cc_enc_import_fck.Pa_enc_import_fck ' ||
1988 ' failed for packet id '||
1989 ' for commitment budget with error - ' ||
1990 l_error_msg);
1991 END IF;
1992 -- bug 3199488, end block
1993
1994 Fnd_Message.Set_Name('IGC', 'IGC_OPI_FAIL_PA_FC_COM');
1995 Fnd_Message.Set_Token('ERROR',l_error_msg);
1996 l_msg_buf := Fnd_Message.Get;
1997 -- Log Interface error
1998 INTERFACE_HANDLE_ERRORS
1999 (NULL,
2000 NULL,
2001 NULL,
2002 l_current_org_id,
2003 l_current_set_of_books_id,
2004 l_msg_buf,
2005 l_error_status);
2006 END IF;
2007 ELSE -- insert into pa_bc_packet unsuccessfull.
2008 l_pa_cb_funds_check_pass := FALSE;
2009 -- IF g_debug_flag = 'Y'
2010 -- THEN
2011 -- Output_Debug('PA_cc_enc_import_fck.Load_Pkt ' ||
2012 -- ' failed for packet id '||
2013 -- to_char( g_bc_packet_id_com) ||
2014 -- ' for commitment budget with error - ' ||
2015 -- l_error_msg);
2016 -- END IF;
2017
2018 -- bug 3199488, start block
2019 IF (l_state_level >= l_debug_level) THEN
2020 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg5',
2021 'PA_cc_enc_import_fck.Load_Pkt ' ||
2022 ' failed for packet id '||
2023 ' for commitment budget with error - ' ||
2024 l_error_msg);
2025 END IF;
2026 -- bug 3199488, end block
2027
2028 -- Log Interface error
2029 Fnd_Message.Set_Name('IGC', 'IGC_OPI_ERR_INS_PA_COM');
2030 Fnd_Message.Set_Token('ERROR',l_error_msg);
2031 l_msg_buf := Fnd_Message.Get;
2032 INTERFACE_HANDLE_ERRORS
2033 (NULL,
2034 NULL,
2035 NULL,
2036 l_current_org_id,
2037 l_current_set_of_books_id,
2038 l_msg_buf,
2039 l_error_status);
2040
2041 END IF;
2042 END IF ; -- PA funds check required in CBC budget
2043
2044 -- If the funds check in commitment budget was successfull
2045 -- then call the PA funds checker for the standard budget.
2046 IF g_pa_sb_funds_check_required
2047 AND l_pa_cb_funds_check_pass
2048 THEN
2049 l_pa_sb_funds_check_pass := TRUE;
2050 -- IF g_debug_flag = 'Y'
2051 -- THEN
2052 -- Output_Debug('Calling PA_cc_enc_import_fck.Load_pkts for ' ||
2053 -- ' packet id '|| to_char( g_bc_packet_id_pay) ||
2054 -- ' for standard budget');
2055 -- END IF;
2056
2057 -- bug 3199488, start block
2058 IF (l_state_level >= l_debug_level) THEN
2059 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg6',
2060 'Calling PA_cc_enc_import_fck.Load_pkts for ' ||
2061 ' packet id '|| to_char( g_bc_packet_id_pay) ||
2062 ' for standard budget');
2063 END IF;
2064 -- bug 3199488, end block
2065
2066 -- Call PA API to insert rows in pa_bc_packets
2067 -- This is done in autonomous mode
2068 PA_cc_enc_import_fck.Load_pkts(
2069 p_calling_module => 'CCTRXIMPORT',
2070 p_ext_budget_type => 'GL',
2071 p_packet_id => g_bc_packet_id_pay,
2072 p_fc_rec_tab => g_pa_fc_pay_rec_tab,
2073 x_return_status => l_return_status,
2074 x_error_msg => l_error_msg);
2075
2076 IF l_return_status = 'S'
2077 THEN
2078 -- IF g_debug_flag = 'Y'
2079 -- THEN
2080 -- Output_Debug('Calling PA_cc_enc_import_fck.Pa_enc_import_fck for ' ||
2081 -- ' packet id '|| to_char( g_bc_packet_id_pay) ||
2082 -- ' for standard budget');
2083 -- END IF;
2084
2085 -- bug 3199488, start block
2086 IF (l_state_level >= l_debug_level) THEN
2087 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg7',
2088 'Calling PA_cc_enc_import_fck.Pa_enc_import_fck for ' ||
2089 ' packet id '|| to_char( g_bc_packet_id_pay) ||
2090 ' for standard budget');
2091 END IF;
2092 -- bug 3199488, end block
2093
2094 -- Call PA Funds checker for all payment records.
2095 pa_cc_enc_import_fck.Pa_enc_import_fck(
2096 p_calling_module => 'CCTRXIMPORT',
2097 p_ext_budget_type => 'GL',
2098 p_conc_flag => 'Y',
2099 p_set_of_book_id => l_current_set_of_books_id,
2100 p_packet_id => g_bc_packet_id_pay,
2101 p_mode =>'R',
2102 p_partial_flag => 'N',
2103 x_return_status => l_return_status,
2104 x_error_msg => l_error_msg);
2105
2106 IF l_return_status <> 'S'
2107 THEN
2108 l_pa_sb_funds_check_pass := FALSE;
2109
2110 -- IF g_debug_flag = 'Y'
2111 -- THEN
2112 -- Output_Debug('PA_cc_enc_import_fck.Pa_enc_import_fck ' ||
2113 -- ' failed for packet id '||
2114 -- to_char( g_bc_packet_id_pay) ||
2115 -- ' for standard budget with error - ' ||
2116 -- l_error_msg);
2117 -- END IF;
2118
2119 -- bug 3199488, start block
2120 IF (l_state_level >= l_debug_level) THEN
2121 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg8',
2122 'PA_cc_enc_import_fck.Pa_enc_import_fck ' ||
2123 ' failed for packet id '||
2124 to_char( g_bc_packet_id_pay) ||
2125 ' for standard budget with error - ' ||
2126 l_error_msg);
2127 END IF;
2128 -- bug 3199488, end block
2129
2130 -- Log Interface error
2131 Fnd_Message.Set_Name('IGC', 'IGC_OPI_FAIL_PA_FC_STD');
2132 Fnd_Message.Set_Token('ERROR',l_error_msg);
2133 l_msg_buf := Fnd_Message.Get;
2134 INTERFACE_HANDLE_ERRORS
2135 (NULL,
2136 NULL,
2137 NULL,
2138 l_current_org_id,
2139 l_current_set_of_books_id,
2140 l_msg_buf,
2141 l_error_status);
2142
2143 END IF;
2144 ELSE -- insert into pa_bc_packet unsuccessfull.
2145 -- Log Interface error;
2146 l_pa_sb_funds_check_pass := FALSE;
2147
2148 -- IF g_debug_flag = 'Y'
2149 -- THEN
2150 -- Output_Debug('PA_cc_enc_import_fck.Load_Pkt ' ||
2151 -- ' failed for packet id '||
2152 -- to_char( g_bc_packet_id_pay) ||
2153 -- ' for standard budget with error - ' ||
2154 -- l_error_msg);
2155 -- END IF;
2156
2157 -- bug 3199488, start block
2158 IF (l_state_level >= l_debug_level) THEN
2159 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg9',
2160 'PA_cc_enc_import_fck.Load_Pkt ' ||
2161 ' failed for packet id '||
2162 to_char( g_bc_packet_id_pay) ||
2163 ' for standard budget with error - ' ||
2164 l_error_msg);
2165 END IF;
2166 -- bug 3199488, end block
2167
2168 Fnd_Message.Set_Name('IGC', 'IGC_OPI_ERR_INS_PA_STD');
2169 Fnd_Message.Set_Token('ERROR',l_error_msg);
2170 l_msg_buf := Fnd_Message.Get;
2171 INTERFACE_HANDLE_ERRORS
2172 (NULL,
2173 NULL,
2174 NULL,
2175 l_current_org_id,
2176 l_current_set_of_books_id,
2177 l_msg_buf,
2178 l_error_status);
2179 END IF;
2180 END IF; -- PA funds check required in Standard Budget
2181
2182 -- Check if any of the funds check failed.
2183 -- If so, records need to be updated accordingly
2184 IF NOT l_pa_cb_funds_check_pass
2185 OR NOT l_pa_sb_funds_check_pass
2186 THEN
2187 l_error_status := 'Y';
2188 l_cbc_return_code := 'F';
2189 ELSE
2190 l_cbc_return_code := 'S';
2191 END IF;
2192
2193 -- Call PA tie back API for the commitment budget
2194 -- whether sucessfull or fail, it needs to be called.
2195 IF g_pa_cb_funds_check_required
2196 THEN
2197 -- IF g_debug_flag = 'Y'
2198 -- THEN
2199 -- Output_Debug('Calling PA_cc_enc_import_fck.Pa_enc_import_fck_tieback for ' ||
2200 -- ' packet id '|| to_char( g_bc_packet_id_com) ||
2201 -- ' for commitment budget');
2202 -- END IF;
2203
2204 -- bug 3199488, start block
2205 IF (l_state_level >= l_debug_level) THEN
2206 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg10',
2207 'Calling PA_cc_enc_import_fck.Pa_enc_import_fck_tieback for ' ||
2208 ' packet id '|| to_char( g_bc_packet_id_com) ||
2209 ' for commitment budget');
2210 END IF;
2211 -- bug 3199488, end block
2212
2213 PA_CC_enc_import_fck.Pa_enc_import_fck_tieback
2214 (p_calling_module => 'CCTRXIMPORT',
2215 p_ext_budget_type => 'CC',
2216 p_packet_id => g_bc_packet_id_com,
2217 p_mode => 'R',
2218 p_partial_flag => 'N',
2219 p_cbc_return_code => l_cbc_return_code,
2220 x_return_status => l_return_status);
2221
2222 IF l_return_status <> 'S'
2223 THEN
2224 -- IF g_debug_flag = 'Y'
2225 -- THEN
2226 -- Output_Debug('PA_cc_enc_import_fck.Pa_enc_import_fck_tieback '||
2227 -- ' failed for ' ||
2228 -- ' packet id '|| to_char( g_bc_packet_id_com) ||
2229 -- ' for commitment budget');
2230 -- END IF;
2231
2232 -- bug 3199488, start block
2233 IF (l_state_level >= l_debug_level) THEN
2234 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg11',
2235 'PA_cc_enc_import_fck.Pa_enc_import_fck_tieback '||
2236 ' failed for ' ||
2237 ' packet id '|| to_char( g_bc_packet_id_com) ||
2238 ' for commitment budget');
2239 END IF;
2240 -- bug 3199488, end block
2241
2242 Fnd_Message.Set_Name('IGC', 'IGC_OPI_ERR_COM_TIEBACK');
2243 l_msg_buf := Fnd_Message.Get;
2244 INTERFACE_HANDLE_ERRORS
2245 (NULL,
2246 NULL,
2247 NULL,
2248 l_current_org_id,
2249 l_current_set_of_books_id,
2250 l_msg_buf,
2251 l_error_status);
2252 END IF;
2253 END IF; -- Call PA API for commitment budget tieback
2254
2255 IF g_pa_sb_funds_check_required
2256 THEN
2257 -- IF g_debug_flag = 'Y'
2258 -- THEN
2259 -- Output_Debug('Calling PA_cc_enc_import_fck.Pa_enc_import_fck_tieback for ' ||
2260 -- ' packet id '|| to_char( g_bc_packet_id_pay) ||
2261 -- ' for standard budget');
2262 -- END IF;
2263
2264 -- bug 3199488, start block
2265 IF (l_state_level >= l_debug_level) THEN
2266 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg12',
2267 'Calling PA_cc_enc_import_fck.Pa_enc_import_fck_tieback for ' ||
2268 ' packet id '|| to_char( g_bc_packet_id_pay) ||
2269 ' for standard budget');
2270 END IF;
2271 -- bug 3199488, end block
2272
2273 PA_CC_enc_import_fck.Pa_enc_import_fck_tieback
2274 (p_calling_module => 'CCTRXIMPORT',
2275 p_ext_budget_type => 'GL',
2276 p_packet_id => g_bc_packet_id_pay,
2277 p_mode => 'R',
2278 p_partial_flag => 'N',
2279 p_cbc_return_code => l_cbc_return_code,
2280 x_return_status => l_return_status);
2281
2282 IF l_return_status <> 'S'
2283 THEN
2284 -- IF g_debug_flag = 'Y'
2285 -- THEN
2286 -- Output_Debug('PA_cc_enc_import_fck.Pa_enc_import_fck_tieback '||
2287 -- ' failed for ' ||
2288 -- ' packet id '|| to_char( g_bc_packet_id_pay) ||
2289 -- ' for payment budget');
2290 -- END IF;
2291
2292 -- bug 3199488, start block
2293 IF (l_state_level >= l_debug_level) THEN
2294 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg13',
2295 'PA_cc_enc_import_fck.Pa_enc_import_fck_tieback '||
2296 ' failed for ' ||
2297 ' packet id '|| to_char( g_bc_packet_id_pay) ||
2298 ' for payment budget');
2299 END IF;
2300 -- bug 3199488, end block
2301
2302 Fnd_Message.Set_Name('IGC', 'IGC_OPI_ERR_STD_TIEBACK');
2303 Fnd_Message.Set_Token('ERROR',l_error_msg);
2304 l_msg_buf := Fnd_Message.Get;
2305 INTERFACE_HANDLE_ERRORS
2306 (NULL,
2307 NULL,
2308 NULL,
2309 l_current_org_id,
2310 l_current_set_of_books_id,
2311 l_msg_buf,
2312 l_error_status);
2313 END IF;
2314 END IF ; -- Call API to tie back Standard budget
2315 END IF ; -- call PA funds checker , Bug 2871052
2316
2317 -- Submit the request for running the errors report.
2318 /*Bug No : 6341012. MOAC Uptake. Set ORG_ID before submitting request */
2319
2320 Fnd_request.set_org_id(l_current_org_id);
2321 l_request_id := FND_REQUEST.SUBMIT_REQUEST
2322 ('IGC',
2323 'IGCCLDER',
2324 NULL,
2325 NULL,
2326 FALSE,
2327 l_current_set_of_books_id,
2328 l_current_org_id,
2329 P_Process_Phase,
2330 P_Batch_Id);
2331 IF l_request_id = 0 THEN
2332 l_error_message := NULL;
2333 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ERR_SUBMIT_EXCPTION_RPT');
2334 l_error_message := FND_MESSAGE.GET;
2335 ERRBUF := ERRBUF || l_error_message;
2336 ELSE
2337 COMMIT;
2338 l_wait_for_request := FND_CONCURRENT.WAIT_FOR_REQUEST(l_request_id,
2339 5,
2340 0,
2341 l_phase,
2342 l_status,
2343 l_dev_phase,
2344 l_dev_status,
2345 l_message);
2346 END IF;
2347 ---------------------
2348 -- Run the xml report
2349 ---------------------
2350
2351 IF IGC_CC_COMMON_UTILS_PVT.xml_report_enabled THEN
2352 IGC_CC_COMMON_UTILS_PVT.get_xml_layout_info(
2353 l_lang,
2354 l_terr,
2355 'IGCCLDER_XML',
2356 'IGC',
2357 'IGCCLDER_XML' );
2358 l_layout := FND_REQUEST.ADD_LAYOUT(
2359 'IGC',
2360 'IGCCLDER_XML',
2361 l_lang,
2362 l_terr,
2363 'RTF');
2364 IF l_layout then
2365 Fnd_request.set_org_id(l_current_org_id);
2366 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
2367 'IGC',
2368 'IGCCLDER_XML',
2369 NULL,
2370 NULL,
2371 FALSE,
2372 l_current_set_of_books_id,
2373 l_current_org_id,
2374 P_Process_Phase,
2375 P_Batch_Id);
2376 IF l_request_id = 0 THEN
2377 l_error_message := NULL;
2378 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ERR_SUBMIT_EXCPTION_RPT');
2379 l_error_message := FND_MESSAGE.GET;
2380 ERRBUF := ERRBUF || l_error_message;
2381 ELSE
2382 COMMIT;
2383 l_wait_for_request := FND_CONCURRENT.WAIT_FOR_REQUEST(
2384 l_request_id,
2385 5,
2386 0,
2387 l_phase,
2388 l_status,
2389 l_dev_phase,
2390 l_dev_status,
2391 l_message);
2392 END IF;
2393 END IF;
2394
2395 END IF;
2396 -----------------------------
2397 -- End Of Run the xml report
2398 -----------------------------
2399
2400
2401 -- If all the records are inserted into CC tables without errors,
2402 -- Delete all the successfully processed records from Interface Tables
2403 IF UPPER(g_process_phase) = 'F' AND UPPER(l_error_status) = 'N' THEN
2404
2405 --IF g_debug_flag = 'Y'
2406 --THEN
2407 -- Output_Debug('Deleting rows from interface tables ..');
2408 --END IF;
2409
2410 -- bug 3199488, start block
2411 IF (l_state_level >= l_debug_level) THEN
2412 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Msg14',
2413 'Deleting rows from interface tables ..');
2414 END IF;
2415 -- bug 3199488, end block
2416
2417 /*
2418 DELETE IGC.igc_cc_headers_interface
2419 WHERE batch_id = P_Batch_Id;
2420 DELETE IGC.igc_cc_acct_lines_interface
2421 WHERE batch_id = P_Batch_Id;
2422 DELETE IGC.igc_cc_det_pf_interface
2423 WHERE batch_id = P_Batch_Id;
2424
2425 */
2426 END IF;
2427
2428 -- Make sure that if there are any messages on the stack that they are written out
2429 -- to the concurrent request log.
2430 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2431 p_data => l_msg_data );
2432
2433 IF (l_msg_count > 0) THEN
2434
2435 l_error_text := '';
2436 FOR l_cur IN 1..l_msg_count LOOP
2437 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2438 -- fnd_file.put_line (FND_FILE.LOG,
2439 -- l_error_text);
2440 -- bug 3199488 start block
2441 IF (l_state_level >= l_debug_level) THEN
2442 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Excp1',
2443 l_error_text);
2444 END IF;
2445 -- bug 3199488, end block
2446 END LOOP;
2447
2448 END IF;
2449
2450 COMMIT;
2451
2452 EXCEPTION WHEN OTHERS THEN
2453 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2454 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'HEADER_INTERFACE_MAIN');
2455 END IF;
2456
2457 -- bug 3199488, start block
2458 IF (l_unexp_level >= l_debug_level) THEN
2459 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
2460 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
2461 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
2462 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Unexp1',TRUE);
2463 END IF;
2464 -- bug 3199488, end block
2465
2466 FND_MSG_PUB.Count_And_Get ( p_count => l_msg_count,
2467 p_data => l_msg_data );
2468
2469 IF (l_msg_count > 0) THEN
2470
2471 l_error_text := '';
2472 FOR l_cur IN 1..l_msg_count LOOP
2473 l_error_text := ' Mesg No : '|| l_cur ||' '|| FND_MSG_PUB.GET(l_cur, FND_API.G_FALSE);
2474 -- fnd_file.put_line (FND_FILE.LOG,
2475 -- l_error_text);
2476 -- bug 3199488 start block
2477 IF (l_state_level >= l_debug_level) THEN
2478 FND_LOG.STRING(l_state_level, 'igc.plsql.igc_cc_open_interface_pkg.header_interface_main.Excp2',
2479 l_error_text);
2480 END IF;
2481 -- bug 3199488, end block
2482 END LOOP;
2483
2484 END IF;
2485 ROLLBACK;
2486 l_error_status := 'U';
2487 l_msg_data := TO_CHAR(SQLCODE)||': '||SQLERRM;
2488
2489 -- Call the PA tieback API to make sure we revert all our changes
2490 -- Bug 2871052
2491 IF g_pa_cb_funds_check_required
2492 THEN
2493 -- Call PA tieback API.
2494 PA_CC_enc_import_fck.Pa_enc_import_fck_tieback
2495 (p_calling_module => 'CCTRXIMPORT',
2496 p_ext_budget_type => 'CC',
2497 p_packet_id => g_bc_packet_id_com,
2498 p_mode => 'R',
2499 p_partial_flag => 'N',
2500 p_cbc_return_code => 'T',
2501 x_return_status => l_return_status);
2502 END IF;
2503
2504 IF g_pa_sb_funds_check_required
2505 THEN
2506 PA_CC_enc_import_fck.Pa_enc_import_fck_tieback
2507 (p_calling_module => 'CCTRXIMPORT',
2508 p_ext_budget_type => 'GL',
2509 p_packet_id => g_bc_packet_id_pay,
2510 p_mode => 'R',
2511 p_partial_flag => 'N',
2512 p_cbc_return_code => 'T',
2513 x_return_status => l_return_status);
2514
2515 END IF;
2516
2517 RETCODE := '2';
2518 ERRBUF := l_msg_data;
2519
2520 END;
2521
2522 /***************************************************************************/
2523 -- Validate the interface header record and return the result
2524 /***************************************************************************/
2525 PROCEDURE HEADER_INTERFACE_VALIDATE
2526 ( P_Interface_Header_Id IN NUMBER,
2527 P_Org_Id IN NUMBER,
2528 P_Cc_Type IN VARCHAR2,
2529 P_Cc_Num IN VARCHAR2,
2530 P_Cc_Version_Num IN NUMBER,
2531 P_Interface_Parent_Header_Id IN NUMBER,
2532 P_Cc_State IN VARCHAR2,
2533 P_Cc_Ctrl_Status IN VARCHAR2,
2534 P_Cc_Encmbrnc_Status IN VARCHAR2,
2535 P_Cc_Apprvl_Status IN VARCHAR2,
2536 P_Vendor_Id IN NUMBER,
2537 P_Vendor_Site_Id IN NUMBER,
2538 P_Vendor_Contact_Id IN NUMBER,
2539 P_Term_Id IN NUMBER,
2540 P_Location_Id IN NUMBER,
2541 P_Set_of_Books_Id IN NUMBER,
2542 P_Cc_Acct_Date IN DATE,
2543 P_Cc_Start_Date IN DATE,
2544 P_Cc_End_Date IN DATE,
2545 P_Cc_Owner_User_Id IN NUMBER,
2546 P_Cc_Preparer_User_Id IN NUMBER,
2547 P_Currency_Code IN VARCHAR2,
2548 P_Conversion_Type IN VARCHAR2,
2549 P_Conversion_Rate IN NUMBER,
2550 P_Conversion_Date IN DATE,
2551 P_Created_By IN NUMBER,
2552 P_CC_Guarantee_Flag IN VARCHAR2,
2553 P_CC_Current_User_Id IN NUMBER,
2554 P_X_Error_Status IN OUT NOCOPY VARCHAR2,
2555 P_Current_Org_Id IN NUMBER,
2556 P_Current_Set_of_Books_Id IN NUMBER,
2557 P_Func_Currency_Code IN VARCHAR2,
2558 P_Cbc_Enable_Flag IN VARCHAR2)
2559 IS
2560 l_error_message igc_cc_interface_errors.error_message%TYPE;
2561 l_count NUMBER;
2562 l_interface_parent_header_id NUMBER;
2563 l_vendor_id NUMBER;
2564 l_vendor_site_id NUMBER;
2565 l_vendor_contact_id NUMBER;
2566 l_term_id NUMBER;
2567 l_location_id NUMBER;
2568 l_currency_code VARCHAR2(15);
2569 l_curr_code VARCHAR2(15);
2570 l_conversion_type VARCHAR2(30);
2571 l_conversion_rate NUMBER;
2572 l_conversion_date DATE;
2573 l_set_of_books_id NUMBER;
2574 l_user_id NUMBER;
2575 l_start_date DATE;
2576 l_end_date DATE;
2577 BEGIN
2578
2579 -- Check the combination of CC_Type, CC_State, CC_Encmbrnc_Status,
2580 -- Cc_Apprvl_Status and Cc_Ctrl_Status is valid
2581
2582 IF P_Cbc_Enable_Flag = 'N' THEN
2583 IF ((P_CC_State = 'PR' AND P_CC_Encmbrnc_Status = 'C') OR
2584 (P_CC_State = 'PR' AND P_CC_Encmbrnc_Status = 'P') OR
2585 (P_CC_State = 'PR' AND P_Cc_Ctrl_Status = 'O') OR
2586 (P_CC_State = 'CT' AND P_Cc_Ctrl_Status = 'O') OR
2587 (P_Cc_Apprvl_Status = 'IN' AND P_Cc_Ctrl_Status = 'O')) THEN
2588
2589 l_error_message := NULL;
2590 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_COMBINATION');
2591 FND_MESSAGE.SET_TOKEN('CC_TYPE', P_Cc_Type, TRUE);
2592 FND_MESSAGE.SET_TOKEN('CC_STATE', P_Cc_State, TRUE);
2593 FND_MESSAGE.SET_TOKEN('CC_ENCUM_STATUS', P_Cc_Encmbrnc_Status, TRUE);
2594 FND_MESSAGE.SET_TOKEN('APPR_STATUS', P_Cc_Apprvl_Status, TRUE);
2595 FND_MESSAGE.SET_TOKEN('CTRL_STATUS', P_Cc_Ctrl_Status, TRUE);
2596 l_error_message := FND_MESSAGE.GET;
2597 INTERFACE_HANDLE_ERRORS
2598 ( P_Interface_Header_Id,
2599 NULL,
2600 NULL,
2601 P_Org_Id,
2602 P_Set_of_Books_Id,
2603 l_error_message,
2604 P_X_Error_Status);
2605 END IF;
2606 ELSE
2607 IF ((P_CC_State = 'PR' AND P_CC_Encmbrnc_Status = 'C') OR
2608 (P_CC_State = 'CM' AND P_CC_Encmbrnc_Status = 'P') OR
2609 (P_CC_State = 'PR' AND P_Cc_Ctrl_Status = 'O') OR
2610 (P_CC_State = 'CT' AND P_Cc_Ctrl_Status = 'O') OR
2611 (P_Cc_Apprvl_Status = 'IN' AND P_Cc_Ctrl_Status = 'O')) THEN
2612
2613
2614 l_error_message := NULL;
2615 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_COMBINATION');
2616 FND_MESSAGE.SET_TOKEN('CC_TYPE', P_Cc_Type, TRUE);
2617 FND_MESSAGE.SET_TOKEN('CC_STATE', P_Cc_State, TRUE);
2618 FND_MESSAGE.SET_TOKEN('CC_ENCUM_STATUS', P_Cc_Encmbrnc_Status, TRUE);
2619 FND_MESSAGE.SET_TOKEN('APPR_STATUS', P_Cc_Apprvl_Status, TRUE);
2620 FND_MESSAGE.SET_TOKEN('CTRL_STATUS', P_Cc_Ctrl_Status, TRUE);
2621 l_error_message := FND_MESSAGE.GET;
2622 INTERFACE_HANDLE_ERRORS
2623 ( P_Interface_Header_Id,
2624 NULL,
2625 NULL,
2626 P_Org_Id,
2627 P_Set_of_Books_Id,
2628 l_error_message,
2629 P_X_Error_Status);
2630 END IF;
2631 END IF;
2632
2633 -- Validate the Org Id.
2634 IF P_Org_Id <> P_Current_Org_Id THEN
2635 l_error_message := NULL;
2636 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ORGID_NO_MATCH');
2637 FND_MESSAGE.SET_TOKEN('ORGID', TO_CHAR(P_Org_Id), TRUE);
2638 FND_MESSAGE.SET_TOKEN('CURR_ORGID', TO_CHAR(P_Current_Org_Id), TRUE);
2639 l_error_message := FND_MESSAGE.GET;
2640 INTERFACE_HANDLE_ERRORS
2641 ( P_Interface_Header_Id,
2642 NULL,
2643 NULL,
2644 P_Org_Id,
2645 P_Set_of_Books_Id,
2646 l_error_message,
2647 P_X_Error_Status);
2648 END IF;
2649
2650 -- Validate the CC type.
2651 IF UPPER(P_Cc_Type) NOT IN ('S', 'C', 'R') THEN
2652 l_error_message := NULL;
2653 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CCTYPE_INVALID');
2654 FND_MESSAGE.SET_TOKEN('CC_TYPE', P_Cc_Type, TRUE);
2655 l_error_message := FND_MESSAGE.GET;
2656 INTERFACE_HANDLE_ERRORS
2657 ( P_Interface_Header_Id,
2658 NULL,
2659 NULL,
2660 P_Org_Id,
2661 P_Set_of_Books_Id,
2662 l_error_message,
2663 P_X_Error_Status);
2664 END IF;
2665
2666 -- Validate that the guarantee flag is not set for a Cover or a Release CC
2667 -- 2043221, Bidisha S, 24 Oct 2001
2668 IF UPPER(P_Cc_Type) <> 'S'
2669 AND Nvl(P_CC_Guarantee_Flag,'N') = 'Y'
2670 THEN
2671 l_error_message := NULL;
2672 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_OPI_INV_TYPE_FOR_GCC');
2673 l_error_message := FND_MESSAGE.GET;
2674 INTERFACE_HANDLE_ERRORS
2675 ( P_Interface_Header_Id,
2676 NULL,
2677 NULL,
2678 P_Org_Id,
2679 P_Set_of_Books_Id,
2680 l_error_message,
2681 P_X_Error_Status);
2682 END IF;
2683
2684 -- Check whether the CC Number already exists in the database.
2685 SELECT COUNT(*) INTO l_count
2686 FROM igc_cc_headers
2687 WHERE org_id = P_Org_Id
2688 AND cc_num = P_Cc_Num;
2689
2690 IF l_count > 0 THEN
2691 l_error_message := NULL;
2692 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DUP_CC_NUMBER');
2693 FND_MESSAGE.SET_TOKEN('CC_NUMBER', P_Cc_Num);
2694 l_error_message := FND_MESSAGE.GET;
2695 INTERFACE_HANDLE_ERRORS
2696 ( P_Interface_Header_Id,
2697 NULL,
2698 NULL,
2699 P_Org_Id,
2700 P_Set_of_Books_Id,
2701 l_error_message,
2702 P_X_Error_Status);
2703 END IF;
2704
2705 -- 1833267, 23 Aug 2001
2706 validate_start_date (p_interface_header_id => p_interface_header_id,
2707 /* Bug No : 6341012. p_interface_parent_header_id, p_cc_encmbrnc_status,p_sbc_enable_flag,
2708 p_cbc_enable_flag, are not used in this procedure*/
2709 -- p_interface_parent_header_id => p_interface_parent_header_id,
2710 p_org_id => p_org_id,
2711 p_set_of_books_id => p_set_of_books_id,
2712 p_cc_type => p_cc_type,
2713 -- p_cc_encmbrnc_status => p_cc_encmbrnc_status,
2714 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
2715 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
2716 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag,
2717 -- p_sbc_enable_flag => g_sbc_enable_flag ,
2718 -- p_cbc_enable_flag => g_cc_bc_enable_flag,
2719 p_cc_start_date => p_cc_start_date,
2720 p_cc_end_date => p_cc_end_date,
2721 p_x_error_status => p_x_error_status);
2722
2723 -- This procedure Validates End Date
2724 validate_end_date (p_interface_header_id => p_interface_header_id,
2725 /* Bug No : 6341012. p_interface_parent_header_id, p_cc_encmbrnc_status,p_sbc_enable_flag,
2726 p_cbc_enable_flag,p_cbc_start_date are not used in this procedure*/
2727 -- p_interface_parent_header_id => p_interface_parent_header_id,
2728 p_org_id => p_org_id,
2729 p_set_of_books_id => p_set_of_books_id,
2730 p_cc_type => p_cc_type,
2731 -- p_cc_encmbrnc_status => p_cc_encmbrnc_status,
2732 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
2733 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
2734 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag,
2735 -- p_sbc_enable_flag => g_sbc_enable_flag ,
2736 -- p_cbc_enable_flag => g_cc_bc_enable_flag,
2737 -- p_cc_start_date => p_cc_start_date,
2738 p_cc_end_date => p_cc_end_date,
2739 p_x_error_status => p_x_error_status);
2740 -- End , 1833267
2741
2742 -- Interface_parent_header_id should not be null and should be a valid value
2743 -- for CC type 'R'
2744
2745 IF P_Cc_Type = 'R' THEN
2746 IF P_Interface_Parent_Header_Id IS NULL THEN
2747 l_error_message := NULL;
2748 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_PARENT_HDR_ID_REQD');
2749 l_error_message := FND_MESSAGE.GET;
2750 INTERFACE_HANDLE_ERRORS
2751 ( P_Interface_Header_Id,
2752 NULL,
2753 NULL,
2754 P_Org_Id,
2755 P_Set_of_Books_Id,
2756 l_error_message,
2757 P_X_Error_Status);
2758 ELSE
2759 BEGIN
2760 SELECT interface_header_id, cc_start_date, cc_end_date, currency_code, conversion_type, conversion_rate, conversion_date
2761 INTO l_interface_parent_header_id, l_start_date, l_end_date, l_curr_code, l_conversion_type, l_conversion_rate, l_conversion_date
2762 FROM igc_cc_headers_interface
2763 WHERE interface_header_id = P_Interface_Parent_Header_Id
2764 AND cc_type = 'C';
2765 IF P_Cc_Start_Date < l_start_date THEN
2766 l_error_message := NULL;
2767 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_SD_REL_LESS_SD_COV');
2768 FND_MESSAGE.SET_TOKEN('START_DATE_REL', TO_CHAR(P_Cc_Start_Date, 'DD-MON-YYYY'), TRUE);
2769 FND_MESSAGE.SET_TOKEN('START_DATE_COV', TO_CHAR(l_start_date, 'DD-MON-YYYY'), TRUE);
2770 l_error_message := FND_MESSAGE.GET;
2771 INTERFACE_HANDLE_ERRORS
2772 ( P_Interface_Header_Id,
2773 NULL,
2774 NULL,
2775 P_Org_Id,
2776 P_Set_of_Books_Id,
2777 l_error_message,
2778 P_X_Error_Status);
2779 END IF;
2780 IF P_Cc_End_Date > l_end_date THEN
2781 l_error_message := NULL;
2782 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ED_REL_GRET_ED_COV');
2783 FND_MESSAGE.SET_TOKEN('END_DATE_REL', TO_CHAR(P_Cc_End_Date, 'DD-MON-YYYY'), TRUE);
2784 FND_MESSAGE.SET_TOKEN('END_DATE_COV', TO_CHAR(l_end_date, 'DD-MON-YYYY'), TRUE);
2785 l_error_message := FND_MESSAGE.GET;
2786 INTERFACE_HANDLE_ERRORS
2787 ( P_Interface_Header_Id,
2788 NULL,
2789 NULL,
2790 P_Org_Id,
2791 P_Set_of_Books_Id,
2792 l_error_message,
2793 P_X_Error_Status);
2794 END IF;
2795 EXCEPTION WHEN NO_DATA_FOUND THEN
2796 l_error_message := NULL;
2797 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_NOT_VALID_COV');
2798 FND_MESSAGE.SET_TOKEN('PARENT_HEADER_ID', TO_CHAR(P_Interface_Parent_Header_Id), TRUE);
2799 l_error_message := FND_MESSAGE.GET;
2800 INTERFACE_HANDLE_ERRORS
2801 ( P_Interface_Header_Id,
2802 NULL,
2803 NULL,
2804 P_Org_Id,
2805 P_Set_of_Books_Id,
2806 l_error_message,
2807 P_X_Error_Status);
2808 END;
2809 END IF;
2810 ELSE
2811 IF P_Interface_Parent_Header_Id IS NOT NULL THEN
2812 l_error_message := NULL;
2813 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_ID_NULL');
2814 l_error_message := FND_MESSAGE.GET;
2815 INTERFACE_HANDLE_ERRORS
2816 ( P_Interface_Header_Id,
2817 NULL,
2818 NULL,
2819 P_Org_Id,
2820 P_Set_of_Books_Id,
2821 l_error_message,
2822 P_X_Error_Status);
2823 END IF;
2824 END IF;
2825
2826 -- Valid CC States are PR - Provisional, CM - Confirmed and CT- Completed
2827 IF UPPER(P_Cc_State) NOT IN ('PR', 'CM', 'CT') THEN
2828 l_error_message := NULL;
2829 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_STATE');
2830 FND_MESSAGE.SET_TOKEN('CC_STATE', P_Cc_State, TRUE);
2831 l_error_message := FND_MESSAGE.GET;
2832 INTERFACE_HANDLE_ERRORS
2833 ( P_Interface_Header_Id,
2834 NULL,
2835 NULL,
2836 P_Org_Id,
2837 P_Set_of_Books_Id,
2838 l_error_message,
2839 P_X_Error_Status);
2840 END IF;
2841
2842 -- Validate CC Control Status
2843 IF UPPER(P_Cc_Ctrl_Status) NOT IN ('C', 'E', 'O') THEN
2844 l_error_message := NULL;
2845 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_CONTROL_STATUS');
2846 FND_MESSAGE.SET_TOKEN('CTRL_STATUS', P_Cc_Ctrl_Status, TRUE);
2847 l_error_message := FND_MESSAGE.GET;
2848 INTERFACE_HANDLE_ERRORS
2849 ( P_Interface_Header_Id,
2850 NULL,
2851 NULL,
2852 P_Org_Id,
2853 P_Set_of_Books_Id,
2854 l_error_message,
2855 P_X_Error_Status);
2856 END IF;
2857
2858 -- Validate CC Encumbrace Status
2859 IF UPPER(P_Cc_Encmbrnc_Status) NOT IN ('C', 'N', 'P') THEN
2860 l_error_message := NULL;
2861 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_ENCUM_STATUS');
2862 FND_MESSAGE.SET_TOKEN('ENCUMBRANCE_STATUS', P_Cc_Encmbrnc_Status, TRUE);
2863 l_error_message := FND_MESSAGE.GET;
2864 INTERFACE_HANDLE_ERRORS
2865 ( P_Interface_Header_Id,
2866 NULL,
2867 NULL,
2868 P_Org_Id,
2869 P_Set_of_Books_Id,
2870 l_error_message,
2871 P_X_Error_Status);
2872 END IF;
2873
2874 -- Validate CC Approval Status
2875 IF UPPER(P_Cc_Apprvl_Status) NOT IN ('IN', 'AP') THEN
2876 l_error_message := NULL;
2877 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_APPROVAL_STATUS');
2878 FND_MESSAGE.SET_TOKEN('APPRVL_STATUS', P_Cc_Apprvl_Status, TRUE);
2879 l_error_message := FND_MESSAGE.GET;
2880 INTERFACE_HANDLE_ERRORS
2881 ( P_Interface_Header_Id,
2882 NULL,
2883 NULL,
2884 P_Org_Id,
2885 P_Set_of_Books_Id,
2886 l_error_message,
2887 P_X_Error_Status);
2888 END IF;
2889
2890 -- Validate Vendor Id
2891 IF UPPER(P_Cc_State) = 'CM' AND
2892 P_Vendor_Id IS NULL AND
2893 UPPER(P_CC_Type) <> 'C' -- Added for 2119450
2894 THEN
2895 l_error_message := NULL;
2896 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NULL_VENDOR_ID');
2897 l_error_message := FND_MESSAGE.GET;
2898 INTERFACE_HANDLE_ERRORS
2899 ( P_Interface_Header_Id,
2900 NULL,
2901 NULL,
2902 P_Org_Id,
2903 P_Set_of_Books_Id,
2904 l_error_message,
2905 P_X_Error_Status);
2906 ELSIF P_Vendor_Id IS NOT NULL THEN
2907 BEGIN
2908 SELECT vendor_id INTO l_vendor_id
2909 FROM po_vendors
2910 WHERE vendor_id = P_Vendor_Id
2911 AND enabled_flag = 'Y'
2912 AND sysdate BETWEEN NVL(start_date_active, sysdate-1)
2913 AND NVL(end_date_active, sysdate+1);
2914 EXCEPTION WHEN NO_DATA_FOUND THEN
2915 l_error_message := NULL;
2916 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_VENDOR_ID');
2917 FND_MESSAGE.SET_TOKEN('VENDOR_ID', TO_CHAR(P_Vendor_Id), TRUE);
2918 l_error_message := FND_MESSAGE.GET;
2919 INTERFACE_HANDLE_ERRORS
2920 ( P_Interface_Header_Id,
2921 NULL,
2922 NULL,
2923 P_Org_Id,
2924 P_Set_of_Books_Id,
2925 l_error_message,
2926 P_X_Error_Status);
2927 WHEN TOO_MANY_ROWS THEN NULL;
2928 END;
2929 END IF;
2930
2931 -- Validate Vendor Site Id
2932 IF UPPER(P_Cc_State) = 'CM' AND
2933 UPPER(P_CC_Type) <> 'C' AND -- Added for 2119450
2934 P_Vendor_Site_Id IS NULL THEN
2935 l_error_message := NULL;
2936 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_NULL_VENDOR_SITE_ID');
2937 l_error_message := FND_MESSAGE.GET;
2938 INTERFACE_HANDLE_ERRORS
2939 ( P_Interface_Header_Id,
2940 NULL,
2941 NULL,
2942 P_Org_Id,
2943 P_Set_of_Books_Id,
2944 l_error_message,
2945 P_X_Error_Status);
2946 ELSIF P_Vendor_Site_Id IS NOT NULL THEN
2947 BEGIN
2948 SELECT vendor_site_id INTO l_vendor_site_id
2949 FROM po_vendor_sites_all
2950 WHERE vendor_site_id = P_Vendor_Site_Id
2951 AND vendor_id = P_Vendor_Id
2952 AND purchasing_site_flag = 'Y'
2953 AND NVL(inactive_date, sysdate+1) > sysdate;
2954 EXCEPTION WHEN NO_DATA_FOUND THEN
2955 l_error_message := NULL;
2956 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_VENDOR_SITE_ID');
2957 FND_MESSAGE.SET_TOKEN('VENDOR_SITE_ID', TO_CHAR(P_Vendor_Site_Id), TRUE);
2958 l_error_message := FND_MESSAGE.GET;
2959 INTERFACE_HANDLE_ERRORS
2960 ( P_Interface_Header_Id,
2961 NULL,
2962 NULL,
2963 P_Org_Id,
2964 P_Set_of_Books_Id,
2965 l_error_message,
2966 P_X_Error_Status);
2967 WHEN TOO_MANY_ROWS THEN NULL;
2968 END;
2969 END IF;
2970
2971 -- Validate Vendor Contact Id
2972 IF P_Vendor_Id IS NULL AND P_Vendor_Contact_Id IS NOT NULL THEN
2973 l_error_message := NULL;
2974 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_VENDOR_CONTACT_ID_NULL');
2975 l_error_message := FND_MESSAGE.GET;
2976 INTERFACE_HANDLE_ERRORS
2977 ( P_Interface_Header_Id,
2978 NULL,
2979 NULL,
2980 P_Org_Id,
2981 P_Set_of_Books_Id,
2982 l_error_message,
2983 P_X_Error_Status);
2984 ELSIF P_Vendor_Id IS NOT NULL AND P_Vendor_Contact_Id IS NOT NULL THEN
2985 BEGIN
2986 SELECT vendor_contact_id INTO l_vendor_contact_id
2987 FROM po_vendor_contacts
2988 WHERE vendor_site_id = P_Vendor_Site_Id
2989 AND vendor_contact_id = P_Vendor_Contact_Id
2990 AND NVL(inactive_date, sysdate+1) > sysdate;
2991 EXCEPTION WHEN NO_DATA_FOUND THEN
2992 l_error_message := NULL;
2993 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_VEDR_CONTACT_ID');
2994 FND_MESSAGE.SET_TOKEN('VENDOR_CONTACT_ID', TO_CHAR(P_Vendor_Contact_Id), TRUE);
2995 l_error_message := FND_MESSAGE.GET;
2996 INTERFACE_HANDLE_ERRORS
2997 ( P_Interface_Header_Id,
2998 NULL,
2999 NULL,
3000 P_Org_Id,
3001 P_Set_of_Books_Id,
3002 l_error_message,
3003 P_X_Error_Status);
3004 WHEN TOO_MANY_ROWS THEN NULL;
3005 END;
3006 END IF;
3007
3008 -- Validate Term Id
3009 IF P_Term_Id IS NOT NULL THEN
3010 BEGIN
3011 SELECT term_id INTO l_term_id
3012 FROM ap_terms_val_v
3013 WHERE term_id = P_Term_Id;
3014 EXCEPTION WHEN NO_DATA_FOUND THEN
3015 l_error_message := NULL;
3016 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_TERM_ID');
3017 FND_MESSAGE.SET_TOKEN('TERM_ID', TO_CHAR(P_Term_Id), TRUE);
3018 l_error_message := FND_MESSAGE.GET;
3019 INTERFACE_HANDLE_ERRORS
3020 ( P_Interface_Header_Id,
3021 NULL,
3022 NULL,
3023 P_Org_Id,
3024 P_Set_of_Books_Id,
3025 l_error_message,
3026 P_X_Error_Status);
3027 WHEN TOO_MANY_ROWS THEN NULL;
3028 END;
3029 END IF;
3030
3031 -- Validate Location Id
3032 IF P_Vendor_Id IS NULL AND P_Location_Id IS NOT NULL THEN
3033 l_error_message := NULL;
3034 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_LOCATION_ID_NULL');
3035 l_error_message := FND_MESSAGE.GET;
3036 INTERFACE_HANDLE_ERRORS
3037 ( P_Interface_Header_Id,
3038 NULL,
3039 NULL,
3040 P_Org_Id,
3041 P_Set_of_Books_Id,
3042 l_error_message,
3043 P_X_Error_Status);
3044 ELSIF P_Vendor_Id IS NOT NULL AND P_Location_Id IS NOT NULL THEN
3045 BEGIN
3046 SELECT location_id INTO l_location_id
3047 FROM hr_locations
3048 WHERE location_id = P_location_Id
3049 AND bill_to_site_flag = 'Y'
3050 AND NVL(inactive_date, sysdate+1) > sysdate;
3051 EXCEPTION WHEN NO_DATA_FOUND THEN
3052 l_error_message := NULL;
3053 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_LOCATION_ID');
3054 FND_MESSAGE.SET_TOKEN('LOCATION_ID', TO_CHAR(P_Location_Id), TRUE);
3055 l_error_message := FND_MESSAGE.GET;
3056 INTERFACE_HANDLE_ERRORS
3057 ( P_Interface_Header_Id,
3058 NULL,
3059 NULL,
3060 P_Org_Id,
3061 P_Set_of_Books_Id,
3062 l_error_message,
3063 P_X_Error_Status);
3064 WHEN TOO_MANY_ROWS THEN NULL;
3065 END;
3066 END IF;
3067
3068 -- Validate Set of Books Id
3069 IF P_Set_of_Books_Id <> P_Current_Set_of_Books_Id THEN
3070 l_error_message := NULL;
3071 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_SOB_NO_MATCH_USER_SOB');
3072 FND_MESSAGE.SET_TOKEN('SOB_ID', TO_CHAR(P_Set_of_Books_Id), TRUE);
3073 FND_MESSAGE.SET_TOKEN('CURRENT_SOB_ID', TO_CHAR(P_Current_Set_of_Books_Id), TRUE);
3074 l_error_message := FND_MESSAGE.GET;
3075 INTERFACE_HANDLE_ERRORS
3076 ( P_Interface_Header_Id,
3077 NULL,
3078 NULL,
3079 P_Org_Id,
3080 P_Set_of_Books_Id,
3081 l_error_message,
3082 P_X_Error_Status);
3083 END IF;
3084
3085 -- Validate Cc Acct Date
3086 IF P_Cc_Acct_Date IS NOT NULL THEN
3087 IF (P_Cc_Acct_Date < P_Cc_Start_Date) OR (P_Cc_Acct_Date > P_Cc_End_Date) THEN
3088 l_error_message := NULL;
3089 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACCOUNT_DT_OUT_OF_RANGE');
3090 FND_MESSAGE.SET_TOKEN('ACCT_DT', TO_CHAR(P_Cc_Acct_Date, 'DD-MON-YYYY'), TRUE);
3091 FND_MESSAGE.SET_TOKEN('START_DATE', TO_CHAR(P_Cc_Start_Date, 'DD-MON-YYYY'), TRUE);
3092 FND_MESSAGE.SET_TOKEN('END_DATE', TO_CHAR(P_Cc_End_Date, 'DD-MON-YYYY'), TRUE);
3093 l_error_message := FND_MESSAGE.GET;
3094 INTERFACE_HANDLE_ERRORS
3095 ( P_Interface_Header_Id,
3096 NULL,
3097 NULL,
3098 P_Org_Id,
3099 P_Set_of_Books_Id,
3100 l_error_message,
3101 P_X_Error_Status);
3102 ELSE
3103 BEGIN
3104 SELECT 1 INTO l_count
3105 FROM igc_cc_periods ccp, gl_sets_of_books sob, gl_periods glp
3106 WHERE sob.set_of_books_id = P_Set_of_Books_Id
3107 AND sob.period_set_name = glp.period_set_name
3108 AND sob.accounted_period_type = glp.period_type
3109 AND glp.adjustment_period_flag = 'N'
3110 AND ccp.period_set_name = glp.period_set_name
3111 AND ccp.period_name = glp.period_name
3112 AND ccp.org_id = P_Org_Id
3113 AND P_Cc_Acct_Date BETWEEN glp.start_date AND glp.end_date
3114 AND ccp.cc_period_status IN ('O','F');
3115 EXCEPTION WHEN NO_DATA_FOUND THEN
3116 l_error_message := NULL;
3117 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACCT_DT_NOTIN_OPEN_PRD');
3118 FND_MESSAGE.SET_TOKEN('ACCT_DT', TO_CHAR(P_Cc_Acct_Date, 'DD-MON-YYYY'), TRUE);
3119 l_error_message := FND_MESSAGE.GET;
3120 INTERFACE_HANDLE_ERRORS
3121 ( P_Interface_Header_Id,
3122 NULL,
3123 NULL,
3124 P_Org_Id,
3125 P_Set_of_Books_Id,
3126 l_error_message,
3127 P_X_Error_Status);
3128 WHEN TOO_MANY_ROWS THEN NULL;
3129 END;
3130 END IF;
3131 END IF;
3132
3133 -- Validate Cc Owner User Id
3134 BEGIN
3135 -- Performance Tuning, Replaced the following query
3136 -- with the one below.
3137 -- SELECT fu.user_id INTO l_user_id
3138 -- FROM fnd_user fu, hr_employees he
3139 -- WHERE fu.user_id = P_Cc_Owner_User_Id
3140 -- AND sysdate BETWEEN NVL(fu.start_date, sysdate)
3141 -- AND NVL(fu.end_date, sysdate)
3142 -- AND fu.employee_id IS NOT NULL
3143 -- AND fu.employee_id = he.employee_id;
3144
3145 SELECT fu.user_id
3146 INTO l_user_id
3147 FROM fnd_user fu,
3148 per_all_people_f p,
3149 per_all_assignments_f a,
3150 per_assignment_status_types past
3151 WHERE fu.user_id = P_Cc_Owner_User_Id
3152 AND sysdate BETWEEN NVL(fu.start_date, sysdate)
3153 AND NVL(fu.end_date, sysdate)
3154 AND fu.employee_id IS NOT NULL
3155 AND fu.employee_id = p.person_id
3156 /* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) */
3157 AND p.business_group_id = (Decode (FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'Y' , p.business_group_id , (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp)))
3158 AND p.employee_number is not null
3159 AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
3160 AND a.person_id = p.person_id
3161 AND a.primary_flag = 'Y'
3162 AND trunc(sysdate) between a.effective_start_date
3163 AND a.effective_end_date
3164 AND a.assignment_status_type_id = past.assignment_status_type_id
3165 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
3166 AND a.assignment_type = 'E';
3167
3168 EXCEPTION WHEN NO_DATA_FOUND THEN
3169 l_error_message := NULL;
3170 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_OWNER_UID_INVALID');
3171 FND_MESSAGE.SET_TOKEN('OWNER_UID', FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP'), TRUE);
3172 /* FND_MESSAGE.SET_TOKEN('OWNER_UID', TO_CHAR(P_Cc_Owner_User_Id), TRUE);*/
3173 l_error_message := FND_MESSAGE.GET;
3174 INTERFACE_HANDLE_ERRORS
3175 ( P_Interface_Header_Id,
3176 NULL,
3177 NULL,
3178 P_Org_Id,
3179 P_Set_of_Books_Id,
3180 l_error_message,
3181 P_X_Error_Status);
3182 WHEN TOO_MANY_ROWS THEN NULL;
3183 END;
3184
3185 -- Validate Cc Preparer User Id
3186 BEGIN
3187 -- SELECT fu.user_id INTO l_user_id
3188 -- FROM fnd_user fu, hr_employees he
3189 -- WHERE fu.user_id = P_Cc_Preparer_User_Id
3190 -- AND sysdate BETWEEN NVL(fu.start_date, sysdate)
3191 -- AND NVL(fu.end_date, sysdate)
3192 -- AND fu.employee_id IS NOT NULL
3193 -- AND fu.employee_id = he.employee_id;
3194
3195 SELECT fu.user_id
3196 INTO l_user_id
3197 FROM fnd_user fu,
3198 per_all_people_f p,
3199 per_all_assignments_f a,
3200 per_assignment_status_types past
3201 WHERE fu.user_id = P_Cc_Preparer_User_Id
3202 AND sysdate BETWEEN NVL(fu.start_date, sysdate)
3203 AND NVL(fu.end_date, sysdate)
3204 AND fu.employee_id IS NOT NULL
3205 AND fu.employee_id = p.person_id
3206 /* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) */
3207 AND p.business_group_id = (Decode (FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'Y' , p.business_group_id , (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp)))
3208 AND p.employee_number is not null
3209 AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
3210 AND a.person_id = p.person_id
3211 AND a.primary_flag = 'Y'
3212 AND trunc(sysdate) between a.effective_start_date
3213 AND a.effective_end_date
3214 AND a.assignment_status_type_id = past.assignment_status_type_id
3215 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
3216 AND a.assignment_type = 'E';
3217
3218 EXCEPTION WHEN NO_DATA_FOUND THEN
3219 l_error_message := NULL;
3220 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PREPARER_UID_INVALID');
3221 FND_MESSAGE.SET_TOKEN('PREPARER_UID', TO_CHAR(P_Cc_Preparer_User_Id), TRUE);
3222 l_error_message := FND_MESSAGE.GET;
3223 INTERFACE_HANDLE_ERRORS
3224 ( P_Interface_Header_Id,
3225 NULL,
3226 NULL,
3227 P_Org_Id,
3228 P_Set_of_Books_Id,
3229 l_error_message,
3230 P_X_Error_Status);
3231 WHEN TOO_MANY_ROWS THEN NULL;
3232 END;
3233
3234 -- Validate Currency Code and the conversion columns
3235 IF P_Currency_Code IS NULL THEN
3236 l_error_message := NULL;
3237 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CODE_REQD');
3238 l_error_message := FND_MESSAGE.GET;
3239 INTERFACE_HANDLE_ERRORS
3240 ( P_Interface_Header_Id,
3241 NULL,
3242 NULL,
3243 P_Org_Id,
3244 P_Set_of_Books_Id,
3245 l_error_message,
3246 P_X_Error_Status);
3247 ELSE
3248 BEGIN
3249 SELECT currency_code INTO l_currency_code
3250 FROM fnd_currencies_vl
3251 WHERE enabled_flag = 'Y'
3252 AND currency_flag = 'Y'
3253 AND currency_code = P_Currency_Code;
3254
3255 IF P_Currency_Code <> P_Func_Currency_Code AND
3256 (P_Conversion_Type IS NULL OR P_Conversion_Rate IS NULL OR P_Conversion_Date IS NULL) THEN
3257 l_error_message := NULL;
3258 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CONV_TYPE_RATE_DT_REQD');
3259 l_error_message := FND_MESSAGE.GET;
3260 INTERFACE_HANDLE_ERRORS
3261 ( P_Interface_Header_Id,
3262 NULL,
3263 NULL,
3264 P_Org_Id,
3265 P_Set_of_Books_Id,
3266 l_error_message,
3267 P_X_Error_Status);
3268 END IF;
3269 IF l_curr_code <> P_Func_Currency_Code AND P_Cc_Type = 'R' AND
3270 (P_Currency_Code <> l_curr_code OR P_Conversion_Type <> l_conversion_type OR P_Conversion_Rate <> l_conversion_rate OR P_Conversion_Date <> l_conversion_date) THEN
3271 l_error_message := NULL;
3272 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CD_CT_CR_CD_SAME');
3273 l_error_message := FND_MESSAGE.GET;
3274 INTERFACE_HANDLE_ERRORS
3275 ( P_Interface_Header_Id,
3276 NULL,
3277 NULL,
3278 P_Org_Id,
3279 P_Set_of_Books_Id,
3280 l_error_message,
3281 P_X_Error_Status);
3282 END IF;
3283
3284 EXCEPTION WHEN NO_DATA_FOUND THEN
3285 l_error_message := NULL;
3286 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CURR_CODE_INVALID');
3287 FND_MESSAGE.SET_TOKEN('CURR_CODE', P_Currency_Code, TRUE);
3288 l_error_message := FND_MESSAGE.GET;
3289 INTERFACE_HANDLE_ERRORS
3290 ( P_Interface_Header_Id,
3291 NULL,
3292 NULL,
3293 P_Org_Id,
3294 P_Set_of_Books_Id,
3295 l_error_message,
3296 P_X_Error_Status);
3297 WHEN TOO_MANY_ROWS THEN NULL;
3298 END;
3299 END IF;
3300
3301 -- Validate Created By
3302 IF P_Created_By IS NOT NULL THEN
3303 BEGIN
3304 SELECT user_id INTO l_user_id
3305 FROM fnd_user
3306 WHERE user_id = P_Created_By;
3307 EXCEPTION WHEN NO_DATA_FOUND THEN
3308 l_error_message := NULL;
3309 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_CREATED_BY');
3310 FND_MESSAGE.SET_TOKEN('CREATED_BY', TO_CHAR(P_Created_By), TRUE);
3311 l_error_message := FND_MESSAGE.GET;
3312 INTERFACE_HANDLE_ERRORS
3313 ( P_Interface_Header_Id,
3314 NULL,
3315 NULL,
3316 P_Org_Id,
3317 P_Set_of_Books_Id,
3318 l_error_message,
3319 P_X_Error_Status);
3320 WHEN TOO_MANY_ROWS THEN NULL;
3321 END;
3322 END IF;
3323
3324 -- Validate Cc Current User Id
3325 IF P_Cc_Current_User_Id IS NOT NULL THEN
3326 BEGIN
3327 -- SELECT fu.user_id INTO l_user_id
3328 -- FROM fnd_user fu, hr_employees he
3329 -- WHERE fu.user_id = P_Cc_Current_User_Id
3330 -- AND sysdate BETWEEN NVL(fu.start_date, sysdate)
3331 -- AND NVL(fu.end_date, sysdate)
3332 -- AND fu.employee_id IS NOT NULL
3333 -- AND fu.employee_id = he.employee_id;
3334
3335 SELECT fu.user_id
3336 INTO l_user_id
3337 FROM fnd_user fu,
3338 per_all_people_f p,
3339 per_all_assignments_f a,
3340 per_assignment_status_types past
3341 WHERE fu.user_id = P_Cc_Current_User_Id
3342 AND sysdate BETWEEN NVL(fu.start_date, sysdate)
3343 AND NVL(fu.end_date, sysdate)
3344 AND fu.employee_id IS NOT NULL
3345 AND fu.employee_id = p.person_id
3346 /* AND p.business_group_id = (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp) */
3347 AND p.business_group_id = (Decode (FND_PROFILE.VALUE('HR_CROSS_BUSINESS_GROUP') , 'Y' , p.business_group_id , (select nvl(max(fsp.business_group_id),0) from financials_system_parameters fsp)))
3348 AND p.employee_number is not null
3349 AND trunc(sysdate) between p.effective_start_date and p.effective_end_date
3350 AND a.person_id = p.person_id
3351 AND a.primary_flag = 'Y'
3352 AND trunc(sysdate) between a.effective_start_date
3353 AND a.effective_end_date
3354 AND a.assignment_status_type_id = past.assignment_status_type_id
3355 AND past.per_system_status in ('ACTIVE_ASSIGN','SUSP_ASSIGN')
3356 AND a.assignment_type = 'E';
3357
3358 EXCEPTION WHEN NO_DATA_FOUND THEN
3359 l_error_message := NULL;
3360 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_USER_ID');
3361 FND_MESSAGE.SET_TOKEN('CURR_UID', TO_CHAR(P_Cc_Current_User_Id), TRUE);
3362 l_error_message := FND_MESSAGE.GET;
3363 INTERFACE_HANDLE_ERRORS
3364 ( P_Interface_Header_Id,
3365 NULL,
3366 NULL,
3367 P_Org_Id,
3368 P_Set_of_Books_Id,
3369 l_error_message,
3370 P_X_Error_Status);
3371 WHEN TOO_MANY_ROWS THEN NULL;
3372 END;
3373 END IF;
3374
3375 EXCEPTION WHEN OTHERS THEN RAISE;
3376
3377 END;
3378
3379 /***************************************************************************/
3380 -- Program which selects all the records from Acct Lines Interface table for
3381 -- a particular Header record and calls other programs for processing
3382 /***************************************************************************/
3383 PROCEDURE ACCT_LINE_INTERFACE_MAIN
3384 ( P_Interface_Header_Id IN NUMBER,
3385 P_Header_Id IN NUMBER,
3386 P_Int_Head_Parent_Header_Id IN NUMBER,
3387 P_Parent_Header_Id IN NUMBER,
3388 P_Org_Id IN NUMBER,
3389 P_Set_of_Books_Id IN NUMBER,
3390 P_Cc_Type IN VARCHAR2,
3391 P_Cc_Encmbrnc_Status IN VARCHAR2,
3392 P_Cc_Start_Date IN DATE,
3393 P_Cc_End_Date IN DATE,
3394 P_Cc_Acct_Date IN DATE,
3395 P_User_Id IN NUMBER,
3396 P_Login_Id IN NUMBER,
3397 P_CC_State IN VARCHAR2,
3398 P_CC_Apprvl_Status IN VARCHAR2,
3399 P_X_Error_Status IN OUT NOCOPY VARCHAR2)
3400 IS
3401 l_acct_line_id NUMBER;
3402 l_parent_header_id NUMBER;
3403 l_parent_acct_line_id NUMBER;
3404 l_parent_det_pf_id NUMBER;
3405 l_row_id VARCHAR2(18);
3406 l_flag VARCHAR2(1);
3407 l_return_status VARCHAR2(1);
3408 l_msg_count NUMBER;
3409 l_msg_data VARCHAR2(2000);
3410 l_msg_buf VARCHAR2(2000);
3411 l_interface_acct_line_record igc_cc_acct_lines_interface%ROWTYPE;
3412
3413 CURSOR c_interface_acct_line_records IS
3414 SELECT * FROM igc_cc_acct_lines_interface
3415 WHERE batch_id = g_batch_id
3416 AND interface_header_id = P_Interface_Header_Id;
3417 BEGIN
3418 -- Process the acct line records one by one
3419 l_parent_header_id := P_Parent_Header_Id;
3420
3421 OPEN c_interface_acct_line_records;
3422 LOOP
3423 FETCH c_interface_acct_line_records INTO l_interface_acct_line_record;
3424 EXIT WHEN c_interface_acct_line_records%NOTFOUND;
3425
3426 ACCT_LINE_INTERFACE_VALIDATE
3427 ( l_interface_acct_line_record.Interface_Header_Id,
3428 P_Int_Head_Parent_Header_Id,
3429 l_interface_acct_line_record.Interface_Acct_Line_Id,
3430 P_Org_Id,
3431 P_Set_of_Books_Id,
3432 P_Cc_Type,
3433 P_Cc_Encmbrnc_Status,
3434 P_Cc_Start_Date,
3435 P_Cc_End_Date,
3436 P_Cc_Acct_Date,
3437 l_interface_acct_line_record.Interface_Parent_Header_Id,
3438 l_interface_acct_line_record.Interface_Parent_Acct_Line_Id,
3439 l_interface_acct_line_record.Cc_Charge_Code_Combination_Id,
3440 l_interface_acct_line_record.Cc_Budget_Code_Combination_Id,
3441 l_interface_acct_line_record.CC_Acct_Entered_Amt,
3442 l_interface_acct_line_record.CC_Acct_Func_Amt,
3443 l_interface_acct_line_record.CC_Acct_Encmbrnc_Amt,
3444 l_interface_acct_line_record.CC_Acct_Encmbrnc_Date,
3445 l_interface_acct_line_record.CC_Acct_Encmbrnc_Status,
3446 l_interface_acct_line_record.Project_Id,
3447 l_interface_acct_line_record.Task_Id,
3448 l_interface_acct_line_record.Expenditure_Type,
3449 l_interface_acct_line_record.Expenditure_Org_Id,
3450 l_interface_acct_line_record.Expenditure_Item_Date,
3451 l_interface_acct_line_record.Created_By,
3452 l_interface_acct_line_record.cc_ent_withheld_amt,
3453 l_interface_acct_line_record.cc_func_withheld_amt,
3454 P_CC_State,
3455 P_CC_Apprvl_Status,
3456 P_X_Error_Status);
3457
3458 -- If validation succeeds, get the derived values and insert acct line record.
3459 IF UPPER(g_process_phase) = 'F' AND UPPER(P_X_Error_Status) = 'N' THEN
3460
3461 ACCT_LINE_INTERFACE_DERIVE( l_acct_line_id );
3462
3463 IF P_Cc_Type = 'R' THEN
3464 GET_PARENT_ID( NULL,
3465 l_interface_acct_line_record.Interface_Parent_Acct_Line_Id,
3466 NULL,
3467 l_parent_header_id,
3468 l_parent_acct_line_id,
3469 l_parent_det_pf_id );
3470 ELSE
3471 l_parent_header_id := NULL;
3472 l_parent_acct_line_id := NULL;
3473 l_parent_det_pf_id := NULL;
3474 END IF;
3475
3476 IGC_CC_ACCT_LINES_PKG.Insert_Row(
3477 1.0,
3478 FND_API.G_TRUE,
3479 FND_API.G_FALSE,
3480 FND_API.G_VALID_LEVEL_FULL,
3481 l_return_status,
3482 l_msg_count,
3483 l_msg_data,
3484 l_row_id,
3485 l_acct_line_id,
3486 P_Header_Id,
3487 P_Parent_Header_Id,
3488 l_parent_acct_line_id,
3489 l_interface_acct_line_record.CC_Charge_Code_Combination_Id,
3490 l_interface_acct_line_record.CC_Acct_Line_Num,
3491 l_interface_acct_line_record.CC_Budget_Code_Combination_Id,
3492 l_interface_acct_line_record.CC_Acct_Entered_Amt,
3493 l_interface_acct_line_record.CC_Acct_Func_Amt,
3494 l_interface_acct_line_record.CC_Acct_Desc,
3495 l_interface_acct_line_record.CC_Acct_Billed_Amt,
3496 l_interface_acct_line_record.CC_Acct_Unbilled_Amt,
3497 l_interface_acct_line_record.CC_Acct_Taxable_Flag,
3498 NULL,-- modified for Ebtax uptake for CC (Bug No-6472296) l_interface_acct_line_record.Tax_Id
3499 l_interface_acct_line_record.CC_Acct_Encmbrnc_Amt,
3500 l_interface_acct_line_record.CC_Acct_Encmbrnc_Date,
3501 l_interface_acct_line_record.CC_Acct_Encmbrnc_Status,
3502 l_interface_acct_line_record.Project_Id,
3503 l_interface_acct_line_record.Task_Id,
3504 l_interface_acct_line_record.Expenditure_Type,
3505 l_interface_acct_line_record.Expenditure_Org_Id,
3506 l_interface_acct_line_record.Expenditure_Item_Date,
3507 sysdate,
3508 P_User_Id,
3509 P_Login_Id,
3510 NVL(l_interface_acct_line_record.Creation_Date, sysdate),
3511 NVL(l_interface_acct_line_record.Created_By, P_User_Id),
3512 l_interface_acct_line_record.Attribute1,
3513 l_interface_acct_line_record.Attribute2,
3514 l_interface_acct_line_record.Attribute3,
3515 l_interface_acct_line_record.Attribute4,
3516 l_interface_acct_line_record.Attribute5,
3517 l_interface_acct_line_record.Attribute6,
3518 l_interface_acct_line_record.Attribute7,
3519 l_interface_acct_line_record.Attribute8,
3520 l_interface_acct_line_record.Attribute9,
3521 l_interface_acct_line_record.Attribute10,
3522 l_interface_acct_line_record.Attribute11,
3523 l_interface_acct_line_record.Attribute12,
3524 l_interface_acct_line_record.Attribute13,
3525 l_interface_acct_line_record.Attribute14,
3526 l_interface_acct_line_record.Attribute15,
3527 l_interface_acct_line_record.Context,
3528 Nvl(l_interface_acct_line_record.CC_Func_Withheld_Amt,0),
3529 Nvl(l_interface_acct_line_record.CC_Ent_Withheld_Amt,0),
3530 l_flag,
3531 l_interface_acct_line_record.tax_classif_code); -- modified for Ebtax uptake (Bug No-6472296)
3532
3533 IF l_return_status IN ('E','U') THEN
3534 l_msg_buf := ' ';
3535 FOR j IN 1..NVL(l_msg_count,0) LOOP
3536 BEGIN
3537 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
3538 p_encoded => 'F');
3539 INTERFACE_HANDLE_ERRORS
3540 ( l_interface_acct_line_record.Interface_Header_Id,
3541 l_interface_acct_line_record.Interface_Acct_Line_Id,
3542 NULL,
3543 P_Org_Id,
3544 P_Set_Of_Books_Id,
3545 l_msg_buf,
3546 P_X_Error_Status);
3547 END;
3548 END LOOP;
3549
3550 ELSE -- Insert was successfull.
3551 -- The insert into igc_cc_acct_lines has been sucessfull
3552 -- Call procedure to populate PLSQL table for PA
3553 -- The call should be made -
3554 -- If budgetary control is enabled in the commitment budget
3555 -- And ((CC is provisIonal,
3556 -- CC is already encumbered,
3557 -- Provisional CCs are being encumbered)
3558 -- Or (CC is confirmed,
3559 -- CC is already encumbered,
3560 -- Confirmed CCs are being encumbered))
3561 -- And (CC acct line is attached to a project
3562 -- And project is budgetary controlled)
3563 -- And cc is of type Cover or Standard
3564 -- Bug 2871052
3565 IF g_cc_bc_enable_flag = 'Y'
3566 AND ((g_cc_state = 'PR'
3567 AND l_interface_acct_line_record.CC_Acct_Encmbrnc_Status = 'P'
3568 /* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_prov_encmbrnc_flag = 'Y' */ )
3569 OR (g_cc_state = 'CM'
3570 AND l_interface_acct_line_record.CC_Acct_Encmbrnc_Status = 'C'
3571 /* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_conf_encmbrnc_flag = 'Y' */
3572 ))
3573 AND p_cc_type IN ('C', 'S')
3574 AND (l_interface_acct_line_record.project_id IS NOT NULL
3575 AND PA_BUDGET_FUND_PKG.Is_bdgt_intg_enabled
3576 (p_project_id => l_interface_acct_line_record.project_id,
3577 p_mode => 'C' ))
3578 THEN
3579 g_pa_cb_funds_check_required := TRUE;
3580
3581 -- Call procedure to populate the PA plsql table
3582 populate_pa_table
3583 (p_budget_type => 'CBC',
3584 p_cc_header_id => p_header_id,
3585 p_cc_acct_line_id => l_acct_line_id,
3586 p_cc_det_pf_line_id => NULL,
3587 p_cc_state => g_cc_state,
3588 p_project_id => l_interface_acct_line_record.project_id,
3589 p_task_id => l_interface_acct_line_record.task_id,
3590 p_expenditure_type => l_interface_acct_line_record.expenditure_type,
3591 p_expenditure_item_date => l_interface_acct_line_record.expenditure_item_date,
3592 p_expenditure_org_id => l_interface_acct_line_record.expenditure_org_id,
3593 p_transaction_date => l_interface_acct_line_record.cc_acct_encmbrnc_date,
3594 p_encumbered_amt => l_interface_acct_line_record.cc_acct_encmbrnc_amt,
3595 p_billed_amt => l_interface_acct_line_record.cc_acct_billed_amt,
3596 p_txn_ccid => l_interface_acct_line_record.cc_budget_code_combination_id,
3597 p_sob_id => p_set_of_books_id,
3598 p_org_id => p_org_id);
3599
3600 END IF; -- PA funds check required
3601 END IF; -- insert into igc_cc_acct_line successfull
3602 END IF; -- Process phase is final and no errors encountered.
3603
3604 -- Process the corresponding det pf line records for the acct line record.
3605 DET_PF_INTERFACE_MAIN
3606 ( l_interface_acct_line_record.Interface_Header_Id,
3607 l_interface_acct_line_record.Interface_Acct_Line_Id,
3608 l_acct_line_id,
3609 l_interface_acct_line_record.Interface_Parent_Acct_Line_Id,
3610 l_parent_acct_line_id,
3611 P_Org_Id,
3612 P_Set_of_Books_Id,
3613 P_Cc_Type,
3614 P_Cc_Encmbrnc_Status,
3615 P_Cc_Start_Date,
3616 P_Cc_End_Date,
3617 P_User_Id,
3618 P_Login_Id,
3619 p_header_id,
3620 l_interface_acct_line_record.project_id,
3621 l_interface_acct_line_record.task_id,
3622 l_interface_acct_line_record.expenditure_type,
3623 l_interface_acct_line_record.expenditure_item_date,
3624 l_interface_acct_line_record.expenditure_org_id,
3625 l_interface_acct_line_record.cc_budget_code_combination_id,
3626 P_X_Error_Status );
3627 END LOOP;
3628 CLOSE c_interface_acct_line_records;
3629
3630 EXCEPTION WHEN OTHERS THEN
3631 ROLLBACK;
3632 l_msg_data := TO_CHAR(SQLCODE)||': '||SQLERRM;
3633 -- bug 3199488, start block
3634 IF (l_unexp_level >= l_debug_level) THEN
3635 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
3636 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
3637 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
3638 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_open_interface_pkg.acct_line_interface_main.Unexp1',TRUE);
3639 END IF;
3640 -- bug 3199488, end block
3641 P_X_Error_Status := 'U';
3642
3643 END;
3644
3645 /***************************************************************************/
3646 -- Validate the interface acct line record and return the result
3647 /***************************************************************************/
3648 PROCEDURE ACCT_LINE_INTERFACE_VALIDATE
3649 ( P_Interface_Header_Id IN NUMBER,
3650 P_Int_Head_Parent_Header_Id IN NUMBER,
3651 P_Interface_Acct_Line_Id IN NUMBER,
3652 P_Org_Id IN NUMBER,
3653 P_Set_of_Books_Id IN NUMBER,
3654 P_Cc_Type IN VARCHAR2,
3655 P_Cc_Encmbrnc_Status IN VARCHAR2,
3656 P_Cc_Start_Date IN DATE,
3657 P_Cc_End_Date IN DATE,
3658 P_Cc_Acct_Date IN DATE,
3659 P_Interface_Parent_Header_Id IN NUMBER,
3660 P_Interface_Parent_AcctLine_Id IN NUMBER,
3661 P_Charge_Code_Combination_Id IN NUMBER,
3662 P_Budget_Code_Combination_Id IN NUMBER,
3663 P_Cc_Acct_Entered_Amt IN NUMBER,
3664 P_Cc_Acct_Func_Amt IN NUMBER,
3665 P_Cc_Acct_Encmbrnc_Amt IN NUMBER,
3666 P_Cc_Acct_Encmbrnc_Date IN DATE,
3667 P_Cc_Acct_Encmbrnc_Status IN VARCHAR2,
3668 P_Project_Id IN NUMBER,
3669 P_Task_Id IN NUMBER,
3670 P_Expenditure_Type IN VARCHAR2,
3671 P_Expenditure_Org_Id IN NUMBER,
3672 P_Expenditure_Item_Date IN DATE,
3673 P_Created_By IN NUMBER,
3674 P_CC_Ent_Withheld_Amt IN NUMBER,
3675 P_CC_Func_Withheld_Amt IN NUMBER,
3676 P_CC_State IN VARCHAR2,
3677 P_CC_Apprvl_Status IN VARCHAR2,
3678 P_X_Error_Status IN OUT NOCOPY VARCHAR2)
3679 IS
3680 l_interface_parent_header_id NUMBER;
3681 l_interface_parent_acctline_id NUMBER;
3682 l_error_message igc_cc_interface_errors.error_message%TYPE;
3683 l_code_combination_id NUMBER;
3684 l_entered_amt NUMBER;
3685 l_func_amt NUMBER;
3686 l_project_id NUMBER;
3687 l_task_id NUMBER;
3688 l_expenditure_type VARCHAR2(30);
3689 l_expenditure_org_id NUMBER;
3690 l_charge_ccid NUMBER;
3691 l_budget_ccid NUMBER;
3692 l_cov_project_id NUMBER;
3693 l_cov_task_id NUMBER;
3694 l_cov_expenditure_type VARCHAR2(30);
3695 l_cov_expenditure_org_id NUMBER;
3696 l_cov_expenditure_item_date DATE;
3697 l_user_id NUMBER;
3698 BEGIN
3699
3700 -- Validate Interface Parent Header Id. Should not be null and should be a valid
3701 -- valid one for CC type 'R'
3702 IF P_Cc_Type = 'R' THEN
3703 IF P_Interface_Parent_Header_Id IS NULL THEN
3704 l_error_message := NULL;
3705 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_PARENT_HDR_ID_NULL');
3706 l_error_message := FND_MESSAGE.GET;
3707 INTERFACE_HANDLE_ERRORS
3708 ( P_Interface_Header_Id,
3709 P_Interface_Acct_Line_Id,
3710 NULL,
3711 P_Org_Id,
3712 P_Set_of_Books_Id,
3713 l_error_message,
3714 P_X_Error_Status);
3715 ELSE
3716 IF P_Int_Head_Parent_Header_Id IS NOT NULL AND
3717 P_Int_Head_Parent_Header_Id <> P_Interface_Parent_Header_Id THEN
3718 l_error_message := NULL;
3719 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_PARENT_HDR_ID_INVLD');
3720 FND_MESSAGE.SET_TOKEN('INT_HDR_PARENT_ID', TO_CHAR(P_Interface_Parent_Header_Id), TRUE);
3721 l_error_message := FND_MESSAGE.GET;
3722 INTERFACE_HANDLE_ERRORS
3723 ( P_Interface_Header_Id,
3724 P_Interface_Acct_Line_Id,
3725 NULL,
3726 P_Org_Id,
3727 P_Set_of_Books_Id,
3728 l_error_message,
3729 P_X_Error_Status);
3730 END IF;
3731 END IF;
3732 ELSE
3733 IF P_Interface_Parent_Header_Id IS NOT NULL THEN
3734 l_error_message := NULL;
3735 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_HDR_ID_NUL_CC_REL');
3736 l_error_message := FND_MESSAGE.GET;
3737 INTERFACE_HANDLE_ERRORS
3738 ( P_Interface_Header_Id,
3739 P_Interface_Acct_Line_Id,
3740 NULL,
3741 P_Org_Id,
3742 P_Set_of_Books_Id,
3743 l_error_message,
3744 P_X_Error_Status);
3745 END IF;
3746 END IF;
3747
3748 -- Validate Interface Parent Acct Line Id. Should not be null and should be a valid
3749 -- one for CC type 'R'
3750 IF P_Cc_Type = 'R' THEN
3751 IF P_Interface_Parent_AcctLine_Id IS NULL THEN
3752 l_error_message := NULL;
3753 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_PRNT_ACNT_LINE_NULL');
3754 l_error_message := FND_MESSAGE.GET;
3755 INTERFACE_HANDLE_ERRORS
3756 ( P_Interface_Header_Id,
3757 P_Interface_Acct_Line_Id,
3758 NULL,
3759 P_Org_Id,
3760 P_Set_of_Books_Id,
3761 l_error_message,
3762 P_X_Error_Status);
3763 ELSE
3764 BEGIN
3765 SELECT interface_acct_line_id
3766 INTO l_interface_parent_acctline_id
3767 FROM igc_cc_acct_lines_interface
3768 WHERE interface_acct_line_id = P_Interface_Parent_AcctLine_Id
3769 AND interface_header_id = P_Interface_Parent_Header_Id;
3770 EXCEPTION WHEN NO_DATA_FOUND THEN
3771 l_error_message := NULL;
3772 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_PRNT_ACNT_LINE_INVL');
3773 FND_MESSAGE.SET_TOKEN('INT_PRNT_ACNT_LINE_ID', TO_CHAR(P_Interface_Parent_AcctLine_Id), TRUE);
3774 l_error_message := FND_MESSAGE.GET;
3775 INTERFACE_HANDLE_ERRORS
3776 ( P_Interface_Header_Id,
3777 P_Interface_Acct_Line_Id,
3778 NULL,
3779 P_Org_Id,
3780 P_Set_of_Books_Id,
3781 l_error_message,
3782 P_X_Error_Status);
3783 END;
3784 END IF;
3785 ELSE
3786 IF P_Interface_Parent_AcctLine_Id IS NOT NULL THEN
3787 l_error_message := NULL;
3788 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INT_LINE_ID_NULL');
3789 l_error_message := FND_MESSAGE.GET;
3790 INTERFACE_HANDLE_ERRORS
3791 ( P_Interface_Header_Id,
3792 P_Interface_Acct_Line_Id,
3793 NULL,
3794 P_Org_Id,
3795 P_Set_of_Books_Id,
3796 l_error_message,
3797 P_X_Error_Status);
3798 END IF;
3799 END IF;
3800
3801
3802 -- Start, 1833267
3803 validate_acct_date(p_interface_header_id => p_interface_header_id,
3804 p_interface_parent_header_id => p_interface_parent_header_id,
3805 p_interface_acct_line_id => p_interface_acct_line_id,
3806 p_org_id => p_org_id,
3807 p_set_of_books_id => p_set_of_books_id,
3808 p_cc_type => p_cc_type ,
3809 p_cc_state => g_cc_state,
3810 p_cc_encmbrnc_status => p_cc_encmbrnc_status,
3811 p_cc_apprvl_status => g_cc_apprvl_status,
3812 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
3813 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
3814 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag,
3815 p_sbc_enable_flag => g_sbc_enable_flag,
3816 p_cbc_enable_flag => g_cc_bc_enable_flag,
3817 p_cc_start_date => p_cc_start_date ,
3818 p_cc_end_date => p_cc_end_date,
3819 p_cc_acct_date => p_cc_acct_date,
3820 p_x_error_status => p_x_error_status);
3821
3822 validate_enc_acct_date (
3823 p_interface_header_id => p_interface_header_id,
3824 p_interface_acct_line_id => p_interface_acct_line_id,
3825 p_org_id => p_org_id,
3826 p_set_of_books_id => p_set_of_books_id,
3827 p_cc_type => p_cc_type ,
3828 p_cc_state => g_cc_state,
3829 p_cc_encmbrnc_status => p_cc_encmbrnc_status,
3830 p_cc_apprvl_status => g_cc_apprvl_status,
3831 /* Bug No : 6341012. SLA uptake. Encumbrance_Type_IDs and cc_flags are not required*/
3832 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
3833 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag,
3834 p_sbc_enable_flag => g_sbc_enable_flag,
3835 p_cbc_enable_flag => g_cc_bc_enable_flag,
3836 p_cc_start_date => p_cc_start_date ,
3837 p_cc_end_date => p_cc_end_date,
3838 p_cc_acct_date => p_cc_acct_date,
3839 p_cc_encmbrnc_acct_date => p_cc_acct_encmbrnc_date,
3840 p_x_error_status => p_x_error_status);
3841 -- End, 1833267
3842
3843 -- Validate the Charge Code Combination Id
3844 BEGIN
3845 IF P_Cc_Acct_Date IS NOT NULL THEN
3846 SELECT code_combination_id INTO l_code_combination_id
3847 FROM gl_code_combinations
3848 WHERE code_combination_id = P_Charge_Code_Combination_Id
3849 AND enabled_flag = 'Y'
3850 AND P_Cc_Acct_Date BETWEEN NVL(start_date_active, P_Cc_Acct_Date)
3851 AND NVL(end_date_active, P_Cc_Acct_Date);
3852 ELSE
3853 SELECT code_combination_id INTO l_code_combination_id
3854 FROM gl_code_combinations
3855 WHERE code_combination_id = P_Charge_Code_Combination_Id
3856 AND enabled_flag = 'Y';
3857 END IF;
3858 EXCEPTION WHEN NO_DATA_FOUND THEN
3859 l_error_message := NULL;
3860 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CHARGE_CCID_NOT_VALID');
3861 FND_MESSAGE.SET_TOKEN('CHARGE_CCID', TO_CHAR(P_Charge_Code_Combination_Id), TRUE);
3862 l_error_message := FND_MESSAGE.GET;
3863 INTERFACE_HANDLE_ERRORS
3864 ( P_Interface_Header_Id,
3865 P_Interface_Acct_Line_Id,
3866 NULL,
3867 P_Org_Id,
3868 P_Set_of_Books_Id,
3869 l_error_message,
3870 P_X_Error_Status);
3871 WHEN TOO_MANY_ROWS THEN NULL;
3872 END;
3873
3874 -- Validate the Budget Code Combination Id
3875 BEGIN
3876 IF P_Cc_Acct_Date IS NOT NULL THEN
3877 SELECT code_combination_id INTO l_code_combination_id
3878 FROM gl_code_combinations
3879 WHERE code_combination_id = P_Budget_Code_Combination_Id
3880 AND enabled_flag = 'Y'
3881 AND P_Cc_Acct_Date BETWEEN NVL(start_date_active, P_Cc_Acct_Date)
3882 AND NVL(end_date_active, P_Cc_Acct_Date);
3883 ELSE
3884 SELECT code_combination_id INTO l_code_combination_id
3885 FROM gl_code_combinations
3886 WHERE code_combination_id = P_Budget_Code_Combination_Id
3887 AND enabled_flag = 'Y';
3888 END IF;
3889 EXCEPTION WHEN NO_DATA_FOUND THEN
3890 l_error_message := NULL;
3891 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_BUD_CCID_NOT_VALID');
3892 FND_MESSAGE.SET_TOKEN('BUD_CCID', TO_CHAR(P_Budget_Code_Combination_Id), TRUE);
3893 l_error_message := FND_MESSAGE.GET;
3894 INTERFACE_HANDLE_ERRORS
3895 ( P_Interface_Header_Id,
3896 P_Interface_Acct_Line_Id,
3897 NULL,
3898 P_Org_Id,
3899 P_Set_of_Books_Id,
3900 l_error_message,
3901 P_X_Error_Status);
3902 WHEN TOO_MANY_ROWS THEN NULL;
3903 END;
3904
3905 -- Validate that the withheld amount exists only for
3906 -- Standard CC's.
3907 -- 2043221, Bidisha , 24 Oct 2001
3908 IF UPPER(P_CC_Type) <> 'S'
3909 AND Nvl(P_CC_Ent_Withheld_Amt,0) <> 0
3910 THEN
3911 l_error_message := NULL;
3912 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_OPI_INV_TYPE_FOR_WHAMT');
3913 l_error_message := FND_MESSAGE.GET;
3914 INTERFACE_HANDLE_ERRORS
3915 ( P_Interface_Header_Id,
3916 P_Interface_Acct_Line_Id,
3917 NULL,
3918 P_Org_Id,
3919 P_Set_of_Books_Id,
3920 l_error_message,
3921 P_X_Error_Status);
3922 END IF;
3923
3924 -- Validate that the withheld amount is positive
3925 -- 2043221, Bidisha , 24 Oct 2001
3926 IF Nvl(P_CC_Ent_Withheld_Amt,0) < 0
3927 THEN
3928 l_error_message := NULL;
3929 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_WHLD_AMT_NEGATIVE');
3930 l_error_message := FND_MESSAGE.GET;
3931 INTERFACE_HANDLE_ERRORS
3932 ( P_Interface_Header_Id,
3933 P_Interface_Acct_Line_Id,
3934 NULL,
3935 P_Org_Id,
3936 P_Set_of_Books_Id,
3937 l_error_message,
3938 P_X_Error_Status);
3939 END IF;
3940
3941 -- Validate that the withheld amount is set to 0 for status CT
3942 -- 2043221, Bidisha S , 25 Oct 2001
3943 IF UPPER(P_CC_State) = 'CT'
3944 AND UPPER(P_CC_Apprvl_Status) = 'AP'
3945 AND Nvl(P_CC_Ent_Withheld_Amt,0) <> 0
3946 THEN
3947 l_error_message := NULL;
3948 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INV_WHLD_AMT_FOR_CT');
3949 FND_MESSAGE.SET_TOKEN('WHLD_AMT', to_char(p_cc_ent_withheld_amt),TRUE);
3950 l_error_message := FND_MESSAGE.GET;
3951 INTERFACE_HANDLE_ERRORS
3952 ( P_Interface_Header_Id,
3953 P_Interface_Acct_Line_Id,
3954 NULL,
3955 P_Org_Id,
3956 P_Set_of_Books_Id,
3957 l_error_message,
3958 P_X_Error_Status);
3959 END IF;
3960 -- Validate Acct Entered Amt. Sum of Det_Pf_Entered_Amt + Withheld Amount should be
3961 -- equal to Acct Entered Amt.
3962 BEGIN
3963 SELECT NVL(SUM(cc_det_pf_entered_amt), 0) INTO l_entered_amt
3964 FROM igc_cc_det_pf_interface
3965 WHERE interface_acct_line_id = P_Interface_Acct_Line_Id;
3966
3967 -- Validation changed for 2043221, Bidisha S , 24 Oct 2001
3968 -- Ent Amount = SUM (PF Amount) + Withheld Amount
3969 IF NVL(P_Cc_Acct_Entered_Amt, 0) <> (NVL(l_entered_amt, 0)
3970 + Nvl(P_CC_Func_Withheld_Amt,0))
3971 THEN
3972 l_error_message := NULL;
3973 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENT_AMT_DIFFERS');
3974 FND_MESSAGE.SET_TOKEN('ACCT_ENT_AMOUNT', TO_CHAR(P_Cc_Acct_Entered_Amt), TRUE);
3975 FND_MESSAGE.SET_TOKEN('ENT_AMOUNT', TO_CHAR(l_entered_amt), TRUE);
3976 FND_MESSAGE.SET_TOKEN('WHLD_AMOUNT', TO_CHAR(P_CC_Ent_Withheld_Amt), TRUE);
3977 l_error_message := FND_MESSAGE.GET;
3978 INTERFACE_HANDLE_ERRORS
3979 ( P_Interface_Header_Id,
3980 P_Interface_Acct_Line_Id,
3981 NULL,
3982 P_Org_Id,
3983 P_Set_of_Books_Id,
3984 l_error_message,
3985 P_X_Error_Status);
3986 END IF;
3987 END;
3988
3989 -- If Cc Type is 'C' (Cover), then the Acct_Func_Amt of Cover should not
3990 -- be less than the sum of Acct_Func_Amt of its Releases.
3991 BEGIN
3992 IF P_Cc_Type = 'C' THEN
3993 SELECT NVL(SUM(cc_acct_func_amt), 0) INTO l_func_amt
3994 FROM igc_cc_acct_lines_interface
3995 WHERE interface_parent_acct_line_id = P_Interface_Acct_Line_Id;
3996 IF NVL(P_Cc_Acct_Func_Amt, 0) < NVL(l_func_amt, 0) THEN
3997 l_error_message := NULL;
3998 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_FUNC_AMT_COV_DIFFERS');
3999 FND_MESSAGE.SET_TOKEN('FUNC_AMT', TO_CHAR(P_Cc_Acct_Func_Amt), TRUE);
4000 FND_MESSAGE.SET_TOKEN('FUNC_REL_AMT', TO_CHAR(l_func_amt), TRUE);
4001 l_error_message := FND_MESSAGE.GET;
4002 INTERFACE_HANDLE_ERRORS
4003 ( P_Interface_Header_Id,
4004 P_Interface_Acct_Line_Id,
4005 NULL,
4006 P_Org_Id,
4007 P_Set_of_Books_Id,
4008 l_error_message,
4009 P_X_Error_Status);
4010 END IF;
4011 END IF;
4012 END;
4013
4014 -- Validate the Encumbrance Status
4015 IF NVL(P_Cc_Encmbrnc_Status, 'N') <> NVL(P_Cc_Acct_Encmbrnc_Status, 'N') THEN
4016 l_error_message := NULL;
4017 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENCU_STATUS_DIFFERS');
4018 FND_MESSAGE.SET_TOKEN('ACCT_ENCUM_STATUS', P_Cc_Acct_Encmbrnc_Status, TRUE);
4019 FND_MESSAGE.SET_TOKEN('ENCUM_STATUS', P_Cc_Encmbrnc_Status, TRUE);
4020 l_error_message := FND_MESSAGE.GET;
4021 INTERFACE_HANDLE_ERRORS
4022 ( P_Interface_Header_Id,
4023 P_Interface_Acct_Line_Id,
4024 NULL,
4025 P_Org_Id,
4026 P_Set_of_Books_Id,
4027 l_error_message,
4028 P_X_Error_Status);
4029 END IF;
4030
4031 -- Validate the Encumbrance colunmns
4032 IF NVL(P_Cc_Acct_Encmbrnc_Status,'N') IN ('C','P') THEN
4033 IF NVL(P_Cc_Acct_Func_Amt, 0) <> NVL(P_Cc_Acct_Encmbrnc_Amt, 0) THEN
4034 l_error_message := NULL;
4035 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENC_AMT_EQUAL_FUNC_AMT');
4036 FND_MESSAGE.SET_TOKEN('ENC_AMT', TO_CHAR(P_Cc_Acct_Encmbrnc_Amt), TRUE);
4037 FND_MESSAGE.SET_TOKEN('FUNC_AMT', TO_CHAR(P_Cc_Acct_Func_Amt), TRUE);
4038 l_error_message := FND_MESSAGE.GET;
4039 INTERFACE_HANDLE_ERRORS
4040 ( P_Interface_Header_Id,
4041 P_Interface_Acct_Line_Id,
4042 NULL,
4043 P_Org_Id,
4044 P_Set_of_Books_Id,
4045 l_error_message,
4046 P_X_Error_Status);
4047 END IF;
4048
4049 IF P_Cc_Acct_Encmbrnc_Date IS NOT NULL
4050 THEN
4051
4052 IF NVL(P_Cc_Start_Date, P_Cc_Acct_Encmbrnc_Date) > P_Cc_Acct_Encmbrnc_Date OR
4053 NVL(P_Cc_End_Date, P_Cc_Acct_Encmbrnc_Date) < P_Cc_Acct_Encmbrnc_Date THEN
4054 l_error_message := NULL;
4055 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ACNT_ENCUM_DT_NOT_LMT');
4056 FND_MESSAGE.SET_TOKEN('ACNT_ENCUM_DT', TO_CHAR(P_Cc_Acct_Encmbrnc_Date, 'DD-MON-YYYY'), TRUE);
4057 FND_MESSAGE.SET_TOKEN('START_DT', TO_CHAR(P_Cc_Start_Date, 'DD-MON-YYYY'), TRUE);
4058 FND_MESSAGE.SET_TOKEN('END_DT', TO_CHAR(P_Cc_End_Date, 'DD-MON-YYYY'), TRUE);
4059 l_error_message := FND_MESSAGE.GET;
4060 INTERFACE_HANDLE_ERRORS
4061 ( P_Interface_Header_Id,
4062 P_Interface_Acct_Line_Id,
4063 NULL,
4064 P_Org_Id,
4065 P_Set_of_Books_Id,
4066 l_error_message,
4067 P_X_Error_Status);
4068 END IF;
4069 END IF;
4070 END IF;
4071
4072 -- Validate the Project related columns
4073 IF P_Project_Id IS NOT NULL OR P_Task_Id IS NOT NULL OR P_Expenditure_Type IS NOT NULL OR
4074 P_Expenditure_Org_Id IS NOT NULL OR P_Expenditure_Item_Date IS NOT NULL THEN
4075
4076 -- Validate the Project Id
4077 IF P_Project_Id IS NOT NULL THEN
4078 BEGIN
4079 -- Performance Tuning, replaced view pa_projects_v
4080 -- FROM pa_projects_expend_v
4081 SELECT project_id INTO l_project_id
4082 FROM pa_projects
4083 WHERE project_id = P_Project_Id;
4084 EXCEPTION WHEN NO_DATA_FOUND THEN
4085 l_error_message := NULL;
4086 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_PROJECT_ID');
4087 FND_MESSAGE.SET_TOKEN('PROJECT_ID', TO_CHAR(P_Project_Id), TRUE);
4088 l_error_message := FND_MESSAGE.GET;
4089 INTERFACE_HANDLE_ERRORS
4090 ( P_Interface_Header_Id,
4091 P_Interface_Acct_Line_Id,
4092 NULL,
4093 P_Org_Id,
4094 P_Set_of_Books_Id,
4095 l_error_message,
4096 P_X_Error_Status);
4097 WHEN TOO_MANY_ROWS THEN NULL;
4098 END;
4099 ELSE
4100 l_error_message := NULL;
4101 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PROJECT_ID_REQD');
4102 l_error_message := FND_MESSAGE.GET;
4103 INTERFACE_HANDLE_ERRORS
4104 ( P_Interface_Header_Id,
4105 P_Interface_Acct_Line_Id,
4106 NULL,
4107 P_Org_Id,
4108 P_Set_of_Books_Id,
4109 l_error_message,
4110 P_X_Error_Status);
4111 END IF;
4112
4113 -- Validate the Task Id
4114 IF P_Task_Id is NULL THEN
4115 l_error_message := NULL;
4116 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_TASK_ID_REQD');
4117 l_error_message := FND_MESSAGE.GET;
4118 INTERFACE_HANDLE_ERRORS
4119 ( P_Interface_Header_Id,
4120 P_Interface_Acct_Line_Id,
4121 NULL,
4122 P_Org_Id,
4123 P_Set_of_Books_Id,
4124 l_error_message,
4125 P_X_Error_Status);
4126 ELSIF P_Project_Id IS NOT NULL AND P_Task_Id IS NOT NULL THEN
4127 BEGIN
4128 -- Performance Tuning, Replaced pa_tasks_expend_v
4129 -- FROM pa_tasks_expend_v
4130 SELECT task_id INTO l_task_id
4131 FROM pa_tasks
4132 WHERE task_id = P_Task_Id
4133 AND project_id = P_Project_Id;
4134 EXCEPTION WHEN NO_DATA_FOUND THEN
4135 l_error_message := NULL;
4136 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_TASK_ID');
4137 FND_MESSAGE.SET_TOKEN('TASK_ID', TO_CHAR(P_Task_Id), TRUE);
4138 l_error_message := FND_MESSAGE.GET;
4139 INTERFACE_HANDLE_ERRORS
4140 ( P_Interface_Header_Id,
4141 P_Interface_Acct_Line_Id,
4142 NULL,
4143 P_Org_Id,
4144 P_Set_of_Books_Id,
4145 l_error_message,
4146 P_X_Error_Status);
4147 WHEN TOO_MANY_ROWS THEN NULL;
4148 END;
4149 END IF;
4150
4151 -- Validate the Expenditure_Type
4152 IF P_Expenditure_Type is NULL THEN
4153 l_error_message := NULL;
4154 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXPENDITURE_TYPE_REQD');
4155 l_error_message := FND_MESSAGE.GET;
4156 INTERFACE_HANDLE_ERRORS
4157 ( P_Interface_Header_Id,
4158 P_Interface_Acct_Line_Id,
4159 NULL,
4160 P_Org_Id,
4161 P_Set_of_Books_Id,
4162 l_error_message,
4163 P_X_Error_Status);
4164 ELSIF P_Project_Id IS NOT NULL AND P_Expenditure_Type IS NOT NULL THEN
4165 BEGIN
4166 -- Performance Tuning, Replaced he following query
4167 -- with the one below.
4168 -- SELECT expenditure_type INTO l_expenditure_type
4169 -- FROM pa_expenditure_types_expend_v
4170 -- WHERE expenditure_type IN ( select expenditure_type from pa_expenditure_types_expend_v et
4171 -- where system_linkage_function = 'VI'
4172 -- and et.project_id = P_Project_Id
4173 -- and et.expenditure_type = P_Expenditure_Type
4174 -- and (sysdate between expnd_typ_start_date_active and
4175 -- nvl(expnd_typ_end_date_active, sysdate))
4176 -- and (sysdate between sys_link_start_date_active and
4177 -- nvl(sys_link_end_date_active,sysdate))
4178 -- union
4179 -- select expenditure_type from pa_expenditure_types_expend_v et
4180 -- where system_linkage_function = 'VI'
4181 -- and et.project_id is null
4182 -- and et.expenditure_type = P_Expenditure_Type
4183 -- and (sysdate between expnd_typ_start_date_active and
4184 -- nvl(expnd_typ_end_date_active, sysdate))
4185 -- and (sysdate between sys_link_start_date_active and
4186 -- nvl(sys_link_end_date_active,sysdate))
4187 -- );
4188
4189
4190 SELECT expenditure_type
4191 INTO l_expenditure_type
4192 FROM pa_expenditure_types_expend_v et
4193 WHERE system_linkage_function = 'VI'
4194 AND (sysdate between expnd_typ_start_date_active
4195 AND nvl(expnd_typ_end_date_active, sysdate))
4196 AND (sysdate between sys_link_start_date_active
4197 AND nvl(sys_link_end_date_active,sysdate))
4198 AND expenditure_type = p_expenditure_type;
4199 EXCEPTION WHEN NO_DATA_FOUND THEN
4200 l_error_message := NULL;
4201 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXP_TYPE_INVALID');
4202 FND_MESSAGE.SET_TOKEN('EXP_TYPE', P_Expenditure_Type, TRUE);
4203 l_error_message := FND_MESSAGE.GET;
4204 INTERFACE_HANDLE_ERRORS
4205 ( P_Interface_Header_Id,
4206 P_Interface_Acct_Line_Id,
4207 NULL,
4208 P_Org_Id,
4209 P_Set_of_Books_Id,
4210 l_error_message,
4211 P_X_Error_Status);
4212 WHEN TOO_MANY_ROWS THEN NULL;
4213 END;
4214 END IF;
4215
4216 -- Validate the Expenditure Org Id
4217 IF P_Expenditure_Org_Id is NULL THEN
4218 l_error_message := NULL;
4219 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXPENDITURE_ORG_ID_REQD');
4220 l_error_message := FND_MESSAGE.GET;
4221 INTERFACE_HANDLE_ERRORS
4222 ( P_Interface_Header_Id,
4223 P_Interface_Acct_Line_Id,
4224 NULL,
4225 P_Org_Id,
4226 P_Set_of_Books_Id,
4227 l_error_message,
4228 P_X_Error_Status);
4229 ELSE
4230 BEGIN
4231 -- Performance Tuning, replaced the query
4232 -- with the one below
4233 -- SELECT organization_id INTO l_expenditure_org_id
4234 -- FROM pa_organizations_expend_v
4235 -- WHERE active_flag = 'Y'
4236 -- AND organization_id = P_Expenditure_Org_Id
4237 -- AND sysdate between date_from and nvl(date_to, sysdate);
4238
4239 SELECT a.organization_id INTO l_expenditure_org_id
4240 FROM hr_all_organization_units a,
4241 pa_all_organizations b
4242 WHERE a.organization_id = b.organization_id
4243 AND sysdate between a.date_from and nvl(a.date_to, sysdate)
4244 AND b.pa_org_use_type = 'EXPENDITURES'
4245 AND b.inactive_date IS NULL
4246 AND b.organization_id = P_Expenditure_Org_Id;
4247
4248 EXCEPTION WHEN NO_DATA_FOUND THEN
4249 l_error_message := NULL;
4250 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXP_ORG_ID_INVALID');
4251 FND_MESSAGE.SET_TOKEN('EXP_ORG_ID', TO_CHAR(P_Expenditure_Org_Id), TRUE);
4252 l_error_message := FND_MESSAGE.GET;
4253 INTERFACE_HANDLE_ERRORS
4254 ( P_Interface_Header_Id,
4255 P_Interface_Acct_Line_Id,
4256 NULL,
4257 P_Org_Id,
4258 P_Set_of_Books_Id,
4259 l_error_message,
4260 P_X_Error_Status);
4261 WHEN TOO_MANY_ROWS THEN NULL;
4262 END;
4263 END IF;
4264
4265 -- Validate Expenditure Item Date
4266 IF P_Expenditure_Item_Date is NULL THEN
4267 l_error_message := NULL;
4268 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXPENDITURE_ITEM_DT_REQ');
4269 l_error_message := FND_MESSAGE.GET;
4270 INTERFACE_HANDLE_ERRORS
4271 ( P_Interface_Header_Id,
4272 P_Interface_Acct_Line_Id,
4273 NULL,
4274 P_Org_Id,
4275 P_Set_of_Books_Id,
4276 l_error_message,
4277 P_X_Error_Status);
4278 ELSE
4279 IF NVL(P_Cc_Start_Date, P_Expenditure_Item_Date) > P_Expenditure_Item_Date OR
4280 NVL(P_Cc_End_Date, P_Expenditure_Item_Date) < P_Expenditure_Item_Date THEN
4281 l_error_message := NULL;
4282 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXP_ITEM_DT_NOT_IN_LMT');
4283 FND_MESSAGE.SET_TOKEN('EXP_ITEM_DATE', TO_CHAR(P_Expenditure_Item_Date, 'DD-MON-YYYY'), TRUE);
4284 FND_MESSAGE.SET_TOKEN('START_DATE', TO_CHAR(P_Cc_Start_Date, 'DD-MON-YYYY'), TRUE);
4285 FND_MESSAGE.SET_TOKEN('END_DATE', TO_CHAR(P_Cc_End_Date, 'DD-MON-YYYY'), TRUE);
4286 l_error_message := FND_MESSAGE.GET;
4287 INTERFACE_HANDLE_ERRORS
4288 ( P_Interface_Header_Id,
4289 P_Interface_Acct_Line_Id,
4290 NULL,
4291 P_Org_Id,
4292 P_Set_of_Books_Id,
4293 l_error_message,
4294 P_X_Error_Status);
4295 END IF;
4296 END IF;
4297
4298 END IF;
4299
4300 -- Validate Charge CCID, Budget CCID, Project ID, Task ID, Expenditure Type,
4301 -- Expenditure Org ID and Expenditure Item Date of Release with Cover.
4302 BEGIN
4303 IF P_Cc_Type = 'R' THEN
4304 SELECT cc_charge_code_combination_id, cc_budget_code_combination_id,
4305 project_id, task_id, expenditure_type,
4306 expenditure_org_id, expenditure_item_date
4307 INTO l_charge_ccid, l_budget_ccid, l_cov_project_id, l_cov_task_id,
4308 l_cov_expenditure_type, l_cov_expenditure_org_id, l_cov_expenditure_item_date
4309 FROM igc_cc_acct_lines_interface
4310 WHERE interface_acct_line_id = P_Interface_Parent_AcctLine_Id;
4311 IF (P_Charge_Code_Combination_Id IS NULL AND l_charge_ccid IS NOT NULL) OR
4312 (P_Charge_Code_Combination_Id IS NOT NULL AND l_charge_ccid IS NULL) OR
4313 (P_Charge_Code_Combination_Id <> l_charge_ccid) THEN
4314 l_error_message := NULL;
4315 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_CCCID_MISMATCH');
4316 FND_MESSAGE.SET_TOKEN('R_CHARGE_CCID', TO_CHAR(P_Charge_Code_Combination_Id), TRUE);
4317 FND_MESSAGE.SET_TOKEN('C_CHARGE_CCID', TO_CHAR(l_charge_ccid), TRUE);
4318 l_error_message := FND_MESSAGE.GET;
4319 INTERFACE_HANDLE_ERRORS
4320 ( P_Interface_Header_Id,
4321 P_Interface_Acct_Line_Id,
4322 NULL,
4323 P_Org_Id,
4324 P_Set_of_Books_Id,
4325 l_error_message,
4326 P_X_Error_Status);
4327 END IF;
4328 IF (P_Budget_Code_Combination_Id IS NULL AND l_budget_ccid IS NOT NULL) OR
4329 (P_Budget_Code_Combination_Id IS NOT NULL AND l_budget_ccid IS NULL) OR
4330 (P_Budget_Code_Combination_Id <> l_budget_ccid) THEN
4331 l_error_message := NULL;
4332 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_BCCID_MISMATCH');
4333 FND_MESSAGE.SET_TOKEN('R_BUDGET_CCID', TO_CHAR(P_Budget_Code_Combination_Id), TRUE);
4334 FND_MESSAGE.SET_TOKEN('C_BUDGET_CCID', TO_CHAR(l_budget_ccid), TRUE);
4335 l_error_message := FND_MESSAGE.GET;
4336 INTERFACE_HANDLE_ERRORS
4337 ( P_Interface_Header_Id,
4338 P_Interface_Acct_Line_Id,
4339 NULL,
4340 P_Org_Id,
4341 P_Set_of_Books_Id,
4342 l_error_message,
4343 P_X_Error_Status);
4344 END IF;
4345 IF (P_Project_Id IS NULL AND l_cov_project_id IS NOT NULL) OR
4346 (P_Project_Id IS NOT NULL AND l_cov_project_id IS NULL) OR
4347 (P_Project_Id <> l_cov_project_id) THEN
4348 l_error_message := NULL;
4349 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PROJECT_ID_MISMATCH');
4350 FND_MESSAGE.SET_TOKEN('R_PROJECT_ID', TO_CHAR(P_Project_Id), TRUE);
4351 FND_MESSAGE.SET_TOKEN('C_PROJECT_ID', TO_CHAR(l_cov_project_id), TRUE);
4352 l_error_message := FND_MESSAGE.GET;
4353 INTERFACE_HANDLE_ERRORS
4354 ( P_Interface_Header_Id,
4355 P_Interface_Acct_Line_Id,
4356 NULL,
4357 P_Org_Id,
4358 P_Set_of_Books_Id,
4359 l_error_message,
4360 P_X_Error_Status);
4361 END IF;
4362 IF (P_Task_Id IS NULL AND l_cov_task_id IS NOT NULL) OR
4363 (P_Task_Id IS NOT NULL AND l_cov_task_id IS NULL) OR
4364 (P_Task_Id <> l_cov_task_id) THEN
4365 l_error_message := NULL;
4366 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_TASK_ID_MISMATCH');
4367 FND_MESSAGE.SET_TOKEN('R_TASK_ID', TO_CHAR(P_Task_Id), TRUE);
4368 FND_MESSAGE.SET_TOKEN('C_TASK_ID', TO_CHAR(l_cov_task_id), TRUE);
4369 l_error_message := FND_MESSAGE.GET;
4370 INTERFACE_HANDLE_ERRORS
4371 ( P_Interface_Header_Id,
4372 P_Interface_Acct_Line_Id,
4373 NULL,
4374 P_Org_Id,
4375 P_Set_of_Books_Id,
4376 l_error_message,
4377 P_X_Error_Status);
4378 END IF;
4379 IF (P_Expenditure_Type IS NULL AND l_cov_expenditure_type IS NOT NULL) OR
4380 (P_Expenditure_Type IS NOT NULL AND l_cov_expenditure_type IS NULL) OR
4381 (P_Expenditure_Type <> l_cov_expenditure_type) THEN
4382 l_error_message := NULL;
4383 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXPEND_TYPE_MISMATCH');
4384 FND_MESSAGE.SET_TOKEN('R_EXPEND_TYPE', P_Expenditure_Type, TRUE);
4385 FND_MESSAGE.SET_TOKEN('C_EXPEND_TYPE', l_cov_expenditure_type, TRUE);
4386 l_error_message := FND_MESSAGE.GET;
4387 INTERFACE_HANDLE_ERRORS
4388 ( P_Interface_Header_Id,
4389 P_Interface_Acct_Line_Id,
4390 NULL,
4391 P_Org_Id,
4392 P_Set_of_Books_Id,
4393 l_error_message,
4394 P_X_Error_Status);
4395 END IF;
4396 IF (P_Expenditure_Org_Id IS NULL AND l_cov_expenditure_org_id IS NOT NULL) OR
4397 (P_Expenditure_Org_Id IS NOT NULL AND l_cov_expenditure_org_id IS NULL) OR
4398 (P_Expenditure_Org_Id <> l_cov_expenditure_org_id) THEN
4399 l_error_message := NULL;
4400 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXPEND_ORG_ID_MISMATCH');
4401 FND_MESSAGE.SET_TOKEN('R_EXPEND_ORG_ID', TO_CHAR(P_Expenditure_Org_Id), TRUE);
4402 FND_MESSAGE.SET_TOKEN('C_EXPEND_ORG_ID', TO_CHAR(l_cov_expenditure_org_id), TRUE);
4403 l_error_message := FND_MESSAGE.GET;
4404 INTERFACE_HANDLE_ERRORS
4405 ( P_Interface_Header_Id,
4406 P_Interface_Acct_Line_Id,
4407 NULL,
4408 P_Org_Id,
4409 P_Set_of_Books_Id,
4410 l_error_message,
4411 P_X_Error_Status);
4412 END IF;
4413 IF (P_Expenditure_Item_Date IS NULL AND l_cov_expenditure_item_date IS NOT NULL) OR
4414 (P_Expenditure_Item_Date IS NOT NULL AND l_cov_expenditure_item_date IS NULL) OR
4415 (P_Expenditure_Item_Date <> l_cov_expenditure_item_date) THEN
4416 l_error_message := NULL;
4417 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_EXPEND_ITEM_DT_MISMATCH');
4418 FND_MESSAGE.SET_TOKEN('R_EXPEND_ITEM_DT', TO_CHAR(P_Expenditure_Item_Date, 'DD-MON-YYYY'), TRUE);
4419 FND_MESSAGE.SET_TOKEN('C_EXPEND_ITEM_DT', TO_CHAR(l_cov_expenditure_item_date, 'DD-MON-YYYY'), TRUE);
4420 l_error_message := FND_MESSAGE.GET;
4421 INTERFACE_HANDLE_ERRORS
4422 ( P_Interface_Header_Id,
4423 P_Interface_Acct_Line_Id,
4424 NULL,
4425 P_Org_Id,
4426 P_Set_of_Books_Id,
4427 l_error_message,
4428 P_X_Error_Status);
4429 END IF;
4430 END IF;
4431 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4432 END;
4433
4434 -- Validate Created By
4435 IF P_Created_By IS NOT NULL THEN
4436 BEGIN
4437 SELECT user_id INTO l_user_id
4438 FROM fnd_user
4439 WHERE user_id = P_Created_By;
4440 EXCEPTION WHEN NO_DATA_FOUND THEN
4441 l_error_message := NULL;
4442 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_CREATED_BY');
4443 FND_MESSAGE.SET_TOKEN('CREATED_BY', TO_CHAR(P_Created_By), TRUE);
4444 l_error_message := FND_MESSAGE.GET;
4445 INTERFACE_HANDLE_ERRORS
4446 ( P_Interface_Header_Id,
4447 P_Interface_Acct_Line_Id,
4448 NULL,
4449 P_Org_Id,
4450 P_Set_of_Books_Id,
4451 l_error_message,
4452 P_X_Error_Status);
4453 WHEN TOO_MANY_ROWS THEN NULL;
4454 END;
4455 END IF;
4456
4457 END;
4458 /***************************************************************************/
4459 -- Program which selects all the records from Det Pf Interface table for
4460 -- a particular acct line and calls other programs for processing
4461 /***************************************************************************/
4462 PROCEDURE DET_PF_INTERFACE_MAIN
4463 ( P_Interface_Header_Id IN NUMBER,
4464 P_Interface_Acct_Line_Id IN NUMBER,
4465 P_Acct_Line_Id IN NUMBER,
4466 P_Int_Acct_Parent_AcctLine_Id IN NUMBER,
4467 P_Parent_Acct_Line_Id IN NUMBER,
4468 P_Org_Id IN NUMBER,
4469 P_Set_of_Books_Id IN NUMBER,
4470 P_Cc_Type IN VARCHAR2,
4471 P_Cc_Encmbrnc_Status IN VARCHAR2,
4472 P_Cc_Start_Date IN DATE,
4473 P_Cc_End_Date IN DATE,
4474 P_User_Id IN NUMBER,
4475 P_Login_Id IN NUMBER,
4476 P_header_id IN NUMBER,
4477 P_Project_Id IN NUMBER,
4478 p_task_id IN NUMBER,
4479 p_expenditure_type IN VARCHAR2,
4480 p_expenditure_item_date IN DATE,
4481 p_expenditure_org_id IN NUMBER,
4482 p_cc_budget_ccid IN NUMBER,
4483 P_X_Error_Status IN OUT NOCOPY VARCHAR2)
4484 IS
4485 l_parent_header_id NUMBER;
4486 l_parent_acct_line_id NUMBER;
4487 l_parent_det_pf_id NUMBER;
4488 l_det_pf_id NUMBER;
4489 l_row_id VARCHAR2(18);
4490 l_flag VARCHAR2(1);
4491 l_return_status VARCHAR2(1);
4492 l_msg_count NUMBER;
4493 l_msg_data VARCHAR2(2000);
4494 l_msg_buf VARCHAR2(2000);
4495 l_interface_det_pf_record igc_cc_det_pf_interface%ROWTYPE;
4496
4497 CURSOR c_interface_det_pf_records IS
4498 SELECT * FROM igc_cc_det_pf_interface
4499 WHERE batch_id = g_batch_id
4500 AND interface_acct_line_id = P_Interface_Acct_Line_Id;
4501 BEGIN
4502 -- Process the det pf line records one by one
4503 l_parent_acct_line_id := P_Parent_Acct_Line_Id;
4504
4505 OPEN c_interface_det_pf_records;
4506 LOOP
4507 FETCH c_interface_det_pf_records INTO l_interface_det_pf_record;
4508 EXIT WHEN c_interface_det_pf_records%NOTFOUND;
4509
4510 DET_PF_INTERFACE_VALIDATE
4511 ( P_Interface_Header_Id,
4512 l_interface_det_pf_record.Interface_Acct_Line_Id,
4513 P_Int_Acct_Parent_AcctLine_Id,
4514 l_interface_det_pf_record.Interface_Det_Pf_Line_Id,
4515 P_Org_Id,
4516 P_Set_of_Books_Id,
4517 P_Cc_Type,
4518 P_Cc_Encmbrnc_Status,
4519 P_Cc_Start_Date,
4520 P_Cc_End_Date,
4521 l_interface_det_pf_record.Interface_Parent_Acct_Line_Id,
4522 l_interface_det_pf_record.Interface_Par_Det_Pf_Line_Id,
4523 l_interface_det_pf_record.Cc_Det_Pf_Date,
4524 l_interface_det_pf_record.Cc_Det_Pf_Entered_Amt,
4525 l_interface_det_pf_record.Cc_Det_Pf_Func_Amt,
4526 l_interface_det_pf_record.Cc_Det_Pf_Encmbrnc_Amt,
4527 l_interface_det_pf_record.Cc_Det_Pf_Encmbrnc_Date,
4528 l_interface_det_pf_record.Cc_Det_Pf_Encmbrnc_Status,
4529 l_interface_det_pf_record.Created_By,
4530 P_X_Error_Status);
4531
4532 -- If validation succeeds, get the derived values and insert det pf line record.
4533 IF UPPER(g_process_phase) = 'F' AND UPPER(P_X_Error_Status) = 'N' THEN
4534
4535 DET_PF_INTERFACE_DERIVE( l_det_pf_id );
4536
4537 IF P_Cc_Type = 'R' THEN
4538 GET_PARENT_ID( NULL,
4539 NULL,
4540 l_interface_det_pf_record.Interface_Par_Det_Pf_Line_Id,
4541 l_parent_header_id,
4542 l_parent_acct_line_id,
4543 l_parent_det_pf_id );
4544 END IF;
4545
4546 IGC_CC_DET_PF_PKG.Insert_Row(
4547 1.0,
4548 FND_API.G_TRUE,
4549 FND_API.G_FALSE,
4550 FND_API.G_VALID_LEVEL_FULL,
4551 l_return_status,
4552 l_msg_count,
4553 l_msg_data,
4554 l_row_id,
4555 l_det_pf_id,
4556 l_interface_det_pf_record.CC_Det_PF_Line_Num,
4557 P_Acct_Line_Id,
4558 l_parent_acct_line_id,
4559 l_parent_det_pf_id ,
4560 l_interface_det_pf_record.CC_Det_PF_Entered_Amt,
4561 l_interface_det_pf_record.CC_Det_PF_Func_Amt,
4562 l_interface_det_pf_record.CC_Det_PF_Date,
4563 l_interface_det_pf_record.CC_Det_PF_Billed_Amt,
4564 l_interface_det_pf_record.CC_Det_PF_Unbilled_Amt,
4565 l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Amt,
4566 l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Date,
4567 l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Status,
4568 sysdate,
4569 P_User_Id,
4570 P_Login_Id,
4571 NVL(l_interface_det_pf_record.Creation_Date, sysdate),
4572 NVL(l_interface_det_pf_record.Created_By, P_User_Id),
4573 l_interface_det_pf_record.Attribute1,
4574 l_interface_det_pf_record.Attribute2,
4575 l_interface_det_pf_record.Attribute3,
4576 l_interface_det_pf_record.Attribute4,
4577 l_interface_det_pf_record.Attribute5,
4578 l_interface_det_pf_record.Attribute6,
4579 l_interface_det_pf_record.Attribute7,
4580 l_interface_det_pf_record.Attribute8,
4581 l_interface_det_pf_record.Attribute9,
4582 l_interface_det_pf_record.Attribute10,
4583 l_interface_det_pf_record.Attribute11,
4584 l_interface_det_pf_record.Attribute12,
4585 l_interface_det_pf_record.Attribute13,
4586 l_interface_det_pf_record.Attribute14,
4587 l_interface_det_pf_record.Attribute15,
4588 l_interface_det_pf_record.Context,
4589 l_flag);
4590
4591 IF l_return_status IN ('E','U') THEN
4592 l_msg_buf := ' ';
4593 FOR j IN 1..NVL(l_msg_count,0) LOOP
4594 BEGIN
4595 l_msg_buf := FND_MSG_PUB.Get(p_msg_index => j,
4596 p_encoded => 'F');
4597 INTERFACE_HANDLE_ERRORS
4598 ( P_Interface_Header_Id,
4599 l_interface_det_pf_record.Interface_Acct_Line_Id,
4600 l_interface_det_pf_record.Interface_Det_Pf_Line_Id,
4601 P_Org_Id,
4602 P_Set_Of_Books_Id,
4603 l_msg_buf,
4604 P_X_Error_Status);
4605 END;
4606 END LOOP;
4607 ELSE -- Insert was successfull.
4608 -- The insert into igc_cc_acct_lines has been sucessfull
4609 -- Call procedure to populate PLSQL table for PA
4610 -- The call should be made -
4611 -- If budgetary control is enabled in the standard budget
4612 -- And ((CC is provisIonal,
4613 -- CC is already encumbered,
4614 -- Provisional CCs are being encumbered)
4615 -- Or (CC is confirmed,
4616 -- CC is already encumbered,
4617 -- Confirmed CCs are being encumbered))
4618 -- And (CC acct line is attached to a project
4619 -- And project is budgetary controlled)
4620 -- And cc is of type Cover or Standard
4621 -- Bug 2871052
4622 IF g_sbc_enable_flag = 'Y'
4623 AND ((g_cc_state = 'PR' AND l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Status = 'P')
4624 /* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_prov_encmbrnc_flag = 'Y' */
4625 OR (g_cc_state = 'CM' AND l_interface_det_pf_record.CC_Det_PF_Encmbrnc_Status = 'C'))
4626 /* Bug No : 6341012. SLA uptake. cc_flags no more exists AND g_cc_conf_encmbrnc_flag = 'Y' */
4627 AND p_cc_type IN ('C', 'S')
4628 AND (p_project_id IS NOT NULL
4629 AND PA_BUDGET_FUND_PKG.Is_bdgt_intg_enabled
4630 (p_project_id => p_project_id,
4631 p_mode => 'S' ))
4632 THEN
4633 g_pa_sb_funds_check_required := TRUE;
4634
4635 -- Call procedure to populate PA tables for the standard budget
4636 populate_pa_table
4637 (p_budget_type => 'GL',
4638 p_cc_header_id => p_header_id,
4639 p_cc_acct_line_id => NULL,
4640 p_cc_det_pf_line_id => l_det_pf_id,
4641 p_cc_state => g_cc_state,
4642 p_project_id => p_project_id,
4643 p_task_id => p_task_id,
4644 p_expenditure_type => p_expenditure_type,
4645 p_expenditure_item_date => p_expenditure_item_date,
4646 p_expenditure_org_id => p_expenditure_org_id,
4647 p_transaction_date => l_interface_det_pf_record.cc_det_pf_date,
4648 p_encumbered_amt => l_interface_det_pf_record.cc_det_pf_encmbrnc_amt,
4649 p_billed_amt => l_interface_det_pf_record.cc_det_pf_billed_amt,
4650 p_txn_ccid => p_cc_budget_ccid,
4651 p_sob_id => p_set_of_books_id,
4652 p_org_id => p_org_id);
4653
4654 END IF; -- PA Funds check required
4655 END IF; -- Insert into igc_cc_det_pf was sucessfull.
4656 END IF; -- Phase is final and no validation errors were found
4657 END LOOP;
4658 CLOSE c_interface_det_pf_records;
4659
4660 EXCEPTION WHEN OTHERS THEN
4661 ROLLBACK;
4662 l_msg_data := TO_CHAR(SQLCODE)||': '||SQLERRM;
4663 -- bug 3199488, start block
4664 IF (l_unexp_level >= l_debug_level) THEN
4665 FND_MESSAGE.SET_NAME('IGC','IGC_LOGGING_UNEXP_ERROR');
4666 FND_MESSAGE.SET_TOKEN('CODE',sqlcode);
4667 FND_MESSAGE.SET_TOKEN('MSG',sqlerrm);
4668 FND_LOG.MESSAGE(l_unexp_level, 'igc.plsql.igc_cc_open_interface_pkg.det_pf_interface_main.Unexp1',TRUE);
4669 END IF;
4670 -- bug 3199488, end block
4671 P_X_Error_Status := 'U';
4672 END;
4673
4674
4675 /***************************************************************************/
4676 -- Validate the interface det pf record and return the result
4677 /***************************************************************************/
4678 PROCEDURE DET_PF_INTERFACE_VALIDATE
4679 ( P_Interface_Header_Id IN NUMBER,
4680 P_Interface_Acct_Line_Id IN NUMBER,
4681 P_Int_Acct_Parent_AcctLine_Id IN NUMBER,
4682 P_Interface_Det_Pf_Id IN NUMBER,
4683 P_Org_Id IN NUMBER,
4684 P_Set_of_Books_Id IN NUMBER,
4685 P_Cc_Type IN VARCHAR2,
4686 P_Cc_Encmbrnc_Status IN VARCHAR2,
4687 P_Cc_Start_Date IN DATE,
4688 P_Cc_End_Date IN DATE,
4689 P_Interface_Parent_AcctLine_Id IN NUMBER,
4690 P_Interface_Parent_Det_Pf_Id IN NUMBER,
4691 P_Cc_Det_Pf_Date IN DATE,
4692 P_Cc_Det_Pf_Entered_Amt IN NUMBER,
4693 P_Cc_Det_Pf_Func_Amt IN NUMBER,
4694 P_Cc_Det_Pf_Encmbrnc_Amt IN NUMBER,
4695 P_Cc_Det_Pf_Encmbrnc_Date IN DATE,
4696 P_Cc_Det_Pf_Encmbrnc_Status IN VARCHAR2,
4697 P_Created_By IN NUMBER,
4698 P_X_Error_Status IN OUT NOCOPY VARCHAR2)
4699 IS
4700 l_interface_parent_det_pf_id NUMBER;
4701 l_error_message igc_cc_interface_errors.error_message%TYPE;
4702 l_count NUMBER;
4703 l_func_amt NUMBER;
4704 l_det_pf_date DATE;
4705 l_user_id NUMBER;
4706 l_gl_application_id fnd_application.application_id%TYPE;
4707
4708 BEGIN
4709
4710 -- --------------------------------------------------------------------
4711 -- Obtain the application ID that will be used throughout this process.
4712 -- --------------------------------------------------------------------
4713 SELECT application_id
4714 INTO l_gl_application_id
4715 FROM fnd_application
4716 WHERE application_short_name = 'SQLGL';
4717
4718 -- Validate Interface Parent Acct Line Id. Should not be null and should be a valid
4719 -- valid one for CC type 'R'
4720 IF P_Cc_Type = 'R' THEN
4721 IF P_Interface_Parent_AcctLine_Id IS NULL THEN
4722 l_error_message := NULL;
4723 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PARENT_ACNT_ID_NULL');
4724 l_error_message := FND_MESSAGE.GET;
4725 INTERFACE_HANDLE_ERRORS
4726 ( P_Interface_Header_Id,
4727 P_Interface_Acct_Line_Id,
4728 P_Interface_Det_Pf_Id,
4729 P_Org_Id,
4730 P_Set_of_Books_Id,
4731 l_error_message,
4732 P_X_Error_Status);
4733 ELSE
4734 IF P_Int_Acct_Parent_AcctLine_Id IS NOT NULL AND
4735 P_Int_Acct_Parent_AcctLine_Id <> P_Interface_Parent_AcctLine_Id THEN
4736 l_error_message := NULL;
4737 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PRENT_ACCT_LINE_ID_INV');
4738 FND_MESSAGE.SET_TOKEN('INT_PAR_ACCT_LINE_INVALID', TO_CHAR(P_Interface_Parent_AcctLine_Id), TRUE);
4739 l_error_message := FND_MESSAGE.GET;
4740 INTERFACE_HANDLE_ERRORS
4741 ( P_Interface_Header_Id,
4742 P_Interface_Acct_Line_Id,
4743 P_Interface_Det_Pf_Id,
4744 P_Org_Id,
4745 P_Set_of_Books_Id,
4746 l_error_message,
4747 P_X_Error_Status);
4748 END IF;
4749 END IF;
4750 ELSE
4751 IF P_Interface_Parent_AcctLine_Id IS NOT NULL THEN
4752 l_error_message := NULL;
4753 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PRENT_ACCT_LINE_ID_NULL');
4754 l_error_message := FND_MESSAGE.GET;
4755 INTERFACE_HANDLE_ERRORS
4756 ( P_Interface_Header_Id,
4757 P_Interface_Acct_Line_Id,
4758 P_Interface_Det_Pf_Id,
4759 P_Org_Id,
4760 P_Set_of_Books_Id,
4761 l_error_message,
4762 P_X_Error_Status);
4763 END IF;
4764 END IF;
4765
4766 -- Validate Interface Parent Det Pf Line Id. Should not be null and should be a valid
4767 -- valid one for CC type 'R'
4768 IF P_Cc_Type = 'R' THEN
4769 IF P_Interface_Parent_Det_Pf_Id IS NULL THEN
4770 l_error_message := NULL;
4771 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PRENT_DET_PF_ID_NULL');
4772 l_error_message := FND_MESSAGE.GET;
4773 INTERFACE_HANDLE_ERRORS
4774 ( P_Interface_Header_Id,
4775 P_Interface_Acct_Line_Id,
4776 P_Interface_Det_Pf_Id,
4777 P_Org_Id,
4778 P_Set_of_Books_Id,
4779 l_error_message,
4780 P_X_Error_Status);
4781 ELSE
4782 BEGIN
4783 SELECT interface_det_pf_line_id
4784 INTO l_interface_parent_det_pf_id
4785 FROM igc_cc_det_pf_interface
4786 WHERE interface_det_pf_line_id = P_Interface_Parent_Det_Pf_Id;
4787 EXCEPTION WHEN NO_DATA_FOUND THEN
4788 l_error_message := NULL;
4789 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DET_PF_LINE_NOT_EXISTS');
4790 FND_MESSAGE.SET_TOKEN('INT_PARENT_DET_PF_LINE_ID', TO_CHAR(P_Interface_Parent_Det_Pf_Id), TRUE);
4791 l_error_message := FND_MESSAGE.GET;
4792 INTERFACE_HANDLE_ERRORS
4793 ( P_Interface_Header_Id,
4794 P_Interface_Acct_Line_Id,
4795 P_Interface_Det_Pf_Id,
4796 P_Org_Id,
4797 P_Set_of_Books_Id,
4798 l_error_message,
4799 P_X_Error_Status);
4800 END;
4801 END IF;
4802 ELSE
4803 IF P_Interface_Parent_Det_Pf_Id IS NOT NULL THEN
4804 l_error_message := NULL;
4805 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DET_PF_LINE_ID_NULL');
4806 l_error_message := FND_MESSAGE.GET;
4807 INTERFACE_HANDLE_ERRORS
4808 ( P_Interface_Header_Id,
4809 P_Interface_Acct_Line_Id,
4810 P_Interface_Det_Pf_Id,
4811 P_Org_Id,
4812 P_Set_of_Books_Id,
4813 l_error_message,
4814 P_X_Error_Status);
4815 END IF;
4816 END IF;
4817
4818 -- Validate the Cc_Det_Pf_Date
4819 -- Start, 1833267
4820 validate_pf_date( p_interface_header_id => p_interface_header_id,
4821 p_org_id => p_org_id,
4822 p_set_of_books_id => p_set_of_books_id,
4823 p_cc_type => p_cc_type,
4824 p_cc_encmbrnc_status => p_cc_encmbrnc_status,
4825 /* Bug No : 6341012. SLA uptake. cc_flags no more exists */
4826 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
4827 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag ,
4828 p_sbc_enable_flag => g_sbc_enable_flag,
4829 p_cbc_enable_flag => g_cc_bc_enable_flag,
4830 p_interface_acct_line_id => p_interface_acct_line_id,
4831 p_interface_det_pf_id => p_interface_det_pf_id ,
4832 p_interface_parent_det_pf_id => p_interface_parent_det_pf_id,
4833 p_cc_det_pf_date => p_cc_det_pf_date,
4834 p_cc_start_date => p_cc_start_date,
4835 p_cc_end_date => p_cc_end_date,
4836 p_x_error_status => p_x_error_status);
4837
4838 validate_enc_pf_date ( p_interface_header_id => p_interface_header_id,
4839 p_org_id => p_org_id,
4840 p_set_of_books_id => p_set_of_books_id,
4841 p_cc_type => p_cc_type,
4842 p_cc_state => g_cc_state,
4843 p_cc_encmbrnc_status => p_cc_encmbrnc_status,
4844 p_cc_apprvl_status => g_cc_apprvl_status,
4845 /* Bug No : 6341012. SLA uptake. cc_flags no more exists */
4846 -- p_cc_prov_encmbrnc_flag => g_cc_prov_encmbrnc_flag,
4847 -- p_cc_conf_encmbrnc_flag => g_cc_conf_encmbrnc_flag ,
4848 p_sb_prov_encmbrnc_flag => g_sb_prov_encmbrnc_flag,
4849 p_sb_conf_encmbrnc_flag => g_sb_conf_encmbrnc_flag ,
4850 p_sbc_enable_flag => g_sbc_enable_flag,
4851 p_cbc_enable_flag => g_cc_bc_enable_flag,
4852 p_interface_acct_line_id => p_interface_acct_line_id,
4853 p_interface_det_pf_id => p_interface_det_pf_id ,
4854 p_interface_parent_det_pf_id => p_interface_parent_det_pf_id,
4855 p_cc_det_pf_date => p_cc_det_pf_date,
4856 p_cc_det_pf_encmbrnc_date => p_cc_det_pf_encmbrnc_date,
4857 p_cc_start_date => p_cc_start_date,
4858 p_cc_end_date => p_cc_end_date,
4859 p_x_error_status => p_x_error_status);
4860 -- End, 1833267
4861
4862
4863 -- If Cc Type is 'C' (Cover), then the Det_Pf_Func_Amt of Cover should not
4864 -- be less than the sum of Det_Pf_Func_Amt of its Releases.
4865 BEGIN
4866 IF P_Cc_Type = 'C' THEN
4867 SELECT NVL(SUM(cc_det_pf_func_amt), 0) INTO l_func_amt
4868 FROM igc_cc_det_pf_interface
4869 WHERE interface_par_det_pf_line_id = P_Interface_Det_Pf_Id;
4870 IF NVL(P_Cc_Det_Pf_Func_Amt, 0) < NVL(l_func_amt, 0) THEN
4871 l_error_message := NULL;
4872 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_FUNC_AMT_COV_DIFFERS');
4873 FND_MESSAGE.SET_TOKEN('FUNC_AMT', TO_CHAR(P_Cc_Det_Pf_Func_Amt), TRUE);
4874 FND_MESSAGE.SET_TOKEN('FUNC_REL_AMT', TO_CHAR(l_func_amt), TRUE);
4875 l_error_message := FND_MESSAGE.GET;
4876 INTERFACE_HANDLE_ERRORS
4877 ( P_Interface_Header_Id,
4878 P_Interface_Acct_Line_Id,
4879 P_Interface_Det_Pf_Id,
4880 P_Org_Id,
4881 P_Set_of_Books_Id,
4882 l_error_message,
4883 P_X_Error_Status);
4884 END IF;
4885 END IF;
4886 END;
4887
4888 -- Validate the Encumbrance Status
4889 IF NVL(P_Cc_Encmbrnc_Status, 'N') <> NVL(P_Cc_Det_Pf_Encmbrnc_Status, 'N') THEN
4890 l_error_message := NULL;
4891 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENCUM_STATUS_DIFFERS');
4892 FND_MESSAGE.SET_TOKEN('LINE_ENCUM_STATUS', P_Cc_Det_Pf_Encmbrnc_Status, TRUE);
4893 FND_MESSAGE.SET_TOKEN('HDR_ENCUM_STATUS', P_Cc_Encmbrnc_Status, TRUE);
4894 l_error_message := FND_MESSAGE.GET;
4895 INTERFACE_HANDLE_ERRORS
4896 ( P_Interface_Header_Id,
4897 P_Interface_Acct_Line_Id,
4898 P_Interface_Det_Pf_Id,
4899 P_Org_Id,
4900 P_Set_of_Books_Id,
4901 l_error_message,
4902 P_X_Error_Status);
4903 END IF;
4904
4905 -- Validate the Encumbrance colunmns
4906 IF NVL(P_Cc_Det_Pf_Encmbrnc_Status,'N') IN ('C','P') THEN
4907 IF NVL(P_Cc_Det_Pf_Func_Amt, 0) <> NVL(P_Cc_Det_Pf_Encmbrnc_Amt, 0) THEN
4908 l_error_message := NULL;
4909 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_ENC_AMT_EQUAL_FUNC_AMT');
4910 FND_MESSAGE.SET_TOKEN('ENC_AMT', TO_CHAR(P_Cc_Det_Pf_Encmbrnc_Amt), TRUE);
4911 FND_MESSAGE.SET_TOKEN('FUNC_AMT', TO_CHAR(P_Cc_Det_Pf_Func_Amt), TRUE);
4912 l_error_message := FND_MESSAGE.GET;
4913 INTERFACE_HANDLE_ERRORS
4914 ( P_Interface_Header_Id,
4915 P_Interface_Acct_Line_Id,
4916 P_Interface_Det_Pf_Id,
4917 P_Org_Id,
4918 P_Set_of_Books_Id,
4919 l_error_message,
4920 P_X_Error_Status);
4921 END IF;
4922
4923 IF P_Cc_Det_Pf_Encmbrnc_Date IS NULL THEN
4924 l_error_message := NULL;
4925 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DTL_PAY_FRCT_ENC_DT_NUL');
4926 l_error_message := FND_MESSAGE.GET;
4927 INTERFACE_HANDLE_ERRORS
4928 ( P_Interface_Header_Id,
4929 P_Interface_Acct_Line_Id,
4930 P_Interface_Det_Pf_Id,
4931 P_Org_Id,
4932 P_Set_of_Books_Id,
4933 l_error_message,
4934 P_X_Error_Status);
4935 ELSIF P_Cc_Det_Pf_Encmbrnc_Date <> P_Cc_Det_Pf_Date THEN
4936 l_error_message := NULL;
4937 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_DT_PF_ENC_DT_DIFF_PFDT');
4938 FND_MESSAGE.SET_TOKEN('PF_ENCUM_DT', TO_CHAR(P_Cc_Det_Pf_Encmbrnc_Date, 'DD-MON-YYYY'), TRUE);
4939 FND_MESSAGE.SET_TOKEN('PF_DATE', TO_CHAR(P_Cc_Det_Pf_Date, 'DD-MON-YYYY'), TRUE);
4940 l_error_message := FND_MESSAGE.GET;
4941 INTERFACE_HANDLE_ERRORS
4942 ( P_Interface_Header_Id,
4943 P_Interface_Acct_Line_Id,
4944 P_Interface_Det_Pf_Id,
4945 P_Org_Id,
4946 P_Set_of_Books_Id,
4947 l_error_message,
4948 P_X_Error_Status);
4949 END IF;
4950 END IF;
4951
4952 -- Validate the Cc_Det_Pf_Encmbrnc_Date
4953 IF P_Cc_Det_Pf_Encmbrnc_Date IS NOT NULL THEN
4954 BEGIN
4955 SELECT 1 INTO l_count
4956 FROM igc_cc_periods ccp, gl_sets_of_books sob, gl_period_statuses glp
4957 WHERE sob.set_of_books_id = P_Set_of_Books_Id
4958 AND sob.set_of_books_id = glp.set_of_books_id
4959 AND sob.accounted_period_type = glp.period_type
4960 AND sob.period_set_name = ccp.period_set_name
4961 AND glp.adjustment_period_flag = 'N'
4962 AND glp.application_id = l_gl_application_id
4963 AND ccp.period_name = glp.period_name
4964 AND ccp.org_id = P_Org_Id
4965 AND P_Cc_Det_Pf_Encmbrnc_Date BETWEEN glp.start_date AND glp.end_date
4966 AND ccp.cc_period_status IN ('O','F')
4967 AND glp.closing_status IN ('O','F');
4968 EXCEPTION WHEN NO_DATA_FOUND THEN
4969 l_error_message := NULL;
4970 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PAY_FRCT_ENCUM_DT_LMT');
4971 FND_MESSAGE.SET_TOKEN('PF_ENCUM_DT', TO_CHAR(P_Cc_Det_Pf_Encmbrnc_Date, 'DD-MON-YYYY'), TRUE);
4972 l_error_message := FND_MESSAGE.GET;
4973 INTERFACE_HANDLE_ERRORS
4974 ( P_Interface_Header_Id,
4975 P_Interface_Acct_Line_Id,
4976 P_Interface_Det_Pf_Id,
4977 P_Org_Id,
4978 P_Set_of_Books_Id,
4979 l_error_message,
4980 P_X_Error_Status);
4981 WHEN TOO_MANY_ROWS THEN NULL;
4982 END;
4983
4984 IF P_Cc_Type = 'R' THEN
4985 IF P_Interface_Parent_Det_Pf_Id IS NOT NULL THEN
4986 BEGIN
4987 SELECT cc_det_pf_encmbrnc_date INTO l_det_pf_date
4988 FROM igc_cc_det_pf_interface
4989 WHERE interface_det_pf_line_id = P_Interface_Parent_Det_Pf_Id;
4990 IF l_det_pf_date <> P_Cc_Det_Pf_Encmbrnc_Date THEN
4991 l_error_message := NULL;
4992 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PF_ENC_RELDT_MORE_COVDT');
4993 FND_MESSAGE.SET_TOKEN('REL_DATE', TO_CHAR(P_Cc_Det_Pf_Encmbrnc_Date, 'DD-MON-YYYY'), TRUE);
4994 FND_MESSAGE.SET_TOKEN('COV_DATE', TO_CHAR(l_det_pf_date, 'DD-MON-YYYY'), TRUE);
4995 l_error_message := FND_MESSAGE.GET;
4996 INTERFACE_HANDLE_ERRORS
4997 ( P_Interface_Header_Id,
4998 P_Interface_Acct_Line_Id,
4999 P_Interface_Det_Pf_Id,
5000 P_Org_Id,
5001 P_Set_of_Books_Id,
5002 l_error_message,
5003 P_X_Error_Status);
5004 END IF;
5005 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
5006 END;
5007 END IF;
5008 ELSE
5009 IF P_Cc_Det_Pf_Encmbrnc_Date < P_Cc_Start_Date OR P_Cc_Det_Pf_Encmbrnc_Date > P_Cc_End_Date THEN
5010 l_error_message := NULL;
5011 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_PF_ENC_DT_NOTIN_SD_ED');
5012 FND_MESSAGE.SET_TOKEN('PF_ENCUM_DT', TO_CHAR(P_Cc_Det_Pf_Encmbrnc_Date, 'DD-MON-YYYY'), TRUE);
5013 FND_MESSAGE.SET_TOKEN('START_DT', TO_CHAR(P_Cc_Start_Date, 'DD-MON-YYYY'), TRUE);
5014 FND_MESSAGE.SET_TOKEN('END_DT', TO_CHAR(P_Cc_End_Date, 'DD-MON-YYYY'), TRUE);
5015 l_error_message := FND_MESSAGE.GET;
5016 INTERFACE_HANDLE_ERRORS
5017 ( P_Interface_Header_Id,
5018 P_Interface_Acct_Line_Id,
5019 P_Interface_Det_Pf_Id,
5020 P_Org_Id,
5021 P_Set_of_Books_Id,
5022 l_error_message,
5023 P_X_Error_Status);
5024 END IF;
5025 END IF;
5026 END IF;
5027
5028 -- Validate Created By
5029 IF P_Created_By IS NOT NULL THEN
5030 BEGIN
5031 SELECT user_id INTO l_user_id
5032 FROM fnd_user
5033 WHERE user_id = P_Created_By;
5034 EXCEPTION WHEN NO_DATA_FOUND THEN
5035 l_error_message := NULL;
5036 FND_MESSAGE.SET_NAME('IGC', 'IGC_CC_INVALID_CREATED_BY');
5037 FND_MESSAGE.SET_TOKEN('CREATED_BY', TO_CHAR(P_Created_By), TRUE);
5038 l_error_message := FND_MESSAGE.GET;
5039 INTERFACE_HANDLE_ERRORS
5040 ( P_Interface_Header_Id,
5041 P_Interface_Acct_Line_Id,
5042 P_Interface_Det_Pf_Id,
5043 P_Org_Id,
5044 P_Set_of_Books_Id,
5045 l_error_message,
5046 P_X_Error_Status);
5047 WHEN TOO_MANY_ROWS THEN NULL;
5048 END;
5049 END IF;
5050 EXCEPTION WHEN OTHERS THEN RAISE;
5051 END;
5052
5053 /* Commented out as per bug 3199488
5054 PROCEDURE Output_Debug (
5055 p_debug_msg IN VARCHAR2
5056 ) IS
5057
5058 -- Constants :
5059
5060 l_prod VARCHAR2(3) := 'IGC';
5061 l_sub_comp VARCHAR2(6) := 'CC_OIP';
5062 l_profile_name VARCHAR2(255) := 'IGC_DEBUG_LOG_DIRECTORY';
5063 l_Return_Status VARCHAR2(1);
5064 l_api_name CONSTANT VARCHAR2(30) := 'Output_Debug';
5065
5066 BEGIN
5067
5068 IGC_MSGS_PKG.Put_Debug_Msg (p_debug_message => p_debug_msg,
5069 p_profile_log_name => l_profile_name,
5070 p_prod => l_prod,
5071 p_sub_comp => l_sub_comp,
5072 p_filename_val => NULL,
5073 x_Return_Status => l_Return_Status
5074 );
5075
5076 IF (l_Return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
5077 raise FND_API.G_EXC_ERROR;
5078 END IF;
5079
5080 RETURN;
5081
5082 -- --------------------------------------------------------------------
5083 -- Exception handler section for the Output_Debug procedure.
5084 -- --------------------------------------------------------------------
5085 EXCEPTION
5086
5087 WHEN FND_API.G_EXC_ERROR THEN
5088 RETURN;
5089
5090 WHEN OTHERS THEN
5091 IF (FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
5092 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
5093 END IF;
5094
5095 RETURN;
5096
5097 END Output_Debug;
5098 */
5099 END IGC_CC_OPEN_INTERFACE_PKG;