[Home] [Help]
PACKAGE BODY: APPS.IGI_ITR_GL_INTERFACE_PKG
Source
1 PACKAGE BODY IGI_ITR_GL_INTERFACE_PKG AS
2 -- $Header: igiitrpb.pls 120.10.12020000.1 2012/06/27 10:56:36 appldev ship $
3 Cursor C_period(
4 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type,
5 p_period_name IN igi_itr_charge_headers.it_period_name%type) IS
6 Select
7 period_type,
8 period_year,
9 period_num
10 From gl_period_statuses
11 Where period_name = p_period_name
12 And set_of_books_id = p_set_of_books_id
13 And nvl(adjustment_period_flag,'N') = 'N'
14 And application_id = (Select application_id
15 From fnd_application
16 Where application_short_name = 'SQLGL');
17
18 Cursor C_cat_name IS
19 Select user_je_category_name
20 From gl_je_categories
21 Where je_category_name = 'IGIITRCC';
22
23 Cursor C_source_name Is
24 Select user_je_source_name
25 From gl_je_sources
26 Where je_source_name = 'Internal Trading';
27
28 Cursor C_charge_lines(
29 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type,
30 p_period_type IN gl_period_statuses.period_type%type,
31 p_start_period_year IN gl_period_statuses.period_year%type,
32 p_start_period_num IN gl_period_statuses.period_num%type,
33 p_end_period_year IN gl_period_statuses.period_year%type,
34 p_end_period_num IN gl_period_statuses.period_num%type) IS
35
36 --Bug 2885987. Modified to remove Multi Join Cartesian.
37
38 select
39 h.it_header_id,
40 l.it_service_line_id,
41 l.it_line_num,
42 decode(nvl(l.entered_dr,0),0,l.creation_code_combination_id,l.receiving_code_combination_id) ccid_dr,
43 decode(nvl(l.entered_dr,0),0,l.entered_cr,l.entered_dr) amount,
44 decode(nvl(l.entered_cr,0),0,l.creation_code_combination_id,l.receiving_code_combination_id) ccid_cr,
45 l.encumbrance_flag,
46 l.encumbered_amount,
47 l.unencumbered_amount,
48 p1.start_date gl_encumbered_date,
49 h.it_period_name,
50 h.currency_code,
51 p1.period_num,
52 p1.period_year,
53 p1.quarter_num,
54 h.name, --shsaxena for bug 2948237
55 l.description,
56 l.charge_service_id,
57 h.it_originator_id,
58 ssv.name
59 from
60 igi_itr_charge_headers h,
61 igi_itr_charge_lines l,
62 igi_itr_charge_service_ss_v ssv,
63 gl_period_statuses p1
64 where p1.period_type = p_period_type
65 and p1.set_of_books_id = p_set_of_books_id
66 and nvl(p1.adjustment_period_flag,'N') = 'N'
67 and p1.application_id = (select application_id
68 from fnd_application
69 where application_short_name = 'SQLGL')
70 and (p1.period_year >= p_start_period_year
71 and p1.period_year <= p_end_period_year)
72 and (p1.period_num >= p_start_period_num
73 and p1.period_num <= p_end_period_num)
74 and h.it_period_name = p1.period_name
75 and h.set_of_books_id = p1.set_of_books_id
76 and l.it_header_id = h.it_header_id
77 and l.status_flag = 'A'
78 and nvl(l.posting_flag,'N') = 'N'
79 and l.charge_service_id = ssv.charge_service_id;
80
81 Cursor C_current_period(
82 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type ) IS
83 Select
84 period_name,
85 period_num,
86 period_year,
87 quarter_num
88 From gl_period_statuses
89 Where trunc(sysdate) Between trunc(start_date) And trunc(end_date)
90 And set_of_books_id = p_set_of_books_id
91 And NVL(adjustment_period_flag,'N') = 'N'
92 And application_id = (Select application_id
93 From fnd_application
94 Where application_short_name = 'SQLGL');
95
96 l_debug_level number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
97 l_state_level number := FND_LOG.LEVEL_STATEMENT;
98 l_proc_level number := FND_LOG.LEVEL_PROCEDURE;
99 l_event_level number := FND_LOG.LEVEL_EVENT;
100 l_excep_level number := FND_LOG.LEVEL_EXCEPTION;
101 l_error_level number := FND_LOG.LEVEL_ERROR;
102 l_unexp_level number := FND_LOG.LEVEL_UNEXPECTED;
103
104 Procedure Writelog(
105 p_mesg IN varchar2,
106 p_debug_mode IN boolean ) IS
107 Begin
108 If p_debug_mode Then
109 fnd_file.put_line( fnd_file.log , p_mesg ) ;
110 Else
111 null;
112 End if;
113 End Writelog;
114
115 Procedure Gl_Interface_Insert(
116 p_status IN gl_interface.status%type,
117 p_set_of_books_id IN gl_interface.set_of_books_id%type,
118 p_accounting_date IN gl_interface.accounting_date%type,
119 p_currency_code IN gl_interface.currency_code%type,
120 p_date_created IN gl_interface.date_created%type,
121 p_created_by IN gl_interface.created_by%type,
122 p_actual_flag IN gl_interface.actual_flag%type,
123 p_user_je_category_name IN gl_interface.user_je_category_name%type,
124 p_user_je_source_name IN gl_interface.user_je_source_name%type,
125 p_entered_dr IN gl_interface.entered_dr%type,
126 p_entered_cr IN gl_interface.entered_cr%type,
127 p_accounted_dr IN gl_interface.accounted_dr%type,
128 p_accounted_cr IN gl_interface.accounted_cr%type,
129 p_transaction_date IN gl_interface.transaction_date%type,
130 p_reference1 IN gl_interface.reference1%type,
131 p_reference4 IN gl_interface.reference4%type,
132 p_reference6 IN gl_interface.reference6%type,
133 p_reference10 IN gl_interface.reference10%type,
134 p_reference21 IN gl_interface.reference21%type,
135 p_reference22 IN gl_interface.reference22%type,
136 p_period_name IN gl_interface.period_name%type,
137 p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%type,
138 p_functional_currency_code IN gl_interface.functional_currency_code%type,
139 p_code_combination_id IN gl_interface.code_combination_id%type,
140 p_group_id IN gl_interface.group_id%type);
141
142 PROCEDURE Init_Gl_Interface(
143 p_int_control IN OUT NOCOPY glcontrol,
144 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%type);
145
146 PROCEDURE Insert_Control_Rec(
147 p_int_control in glcontrol );
148
149 PROCEDURE Create_Actuals (
150 errbuf OUT NOCOPY varchar2,
151 retcode OUT NOCOPY number,
152 p_set_of_books_id IN igi_itr_charge_headers.set_of_books_id%type,
153 p_start_period IN igi_itr_charge_headers.it_period_name%type,
154 p_end_period IN igi_itr_charge_headers.it_period_name%type) IS
155 l_int_control glcontrol;
156 l_chart_of_accounts_id number;
157 l_itr_enc_type_id number;
158 l_gl_user_id number;
159 l_current_period_name igi_itr_charge_headers.it_period_name%type;
160 l_current_period_num gl_period_statuses.period_num%type;
161 l_current_period_year gl_period_statuses.period_year%type;
162 l_current_quarter_num gl_period_statuses.quarter_num%type;
163 l_period_name igi_itr_charge_headers.it_period_name%type;
164 l_period_type gl_period_statuses.period_type%type;
165 l_start_period_year gl_period_statuses.period_year%type;
166 l_start_period_num gl_period_statuses.period_num%type;
167 l_end_period_year gl_period_statuses.period_year%type;
168 l_end_period_num gl_period_statuses.period_num%type;
169 l_it_header_id igi_itr_charge_lines.it_header_id%type;
170 l_it_service_line_id igi_itr_charge_lines.it_service_line_id%type;
171 l_it_line_num igi_itr_charge_lines.it_line_num%type;
172 l_ccid_dr igi_itr_charge_lines.creation_code_combination_id%type;
173 l_amount igi_itr_charge_lines.entered_dr%type;
174 l_ccid_cr igi_itr_charge_lines.creation_code_combination_id%type;
175 l_encumbrance_flag igi_itr_charge_lines.encumbrance_flag%type;
176 l_encumbered_amount igi_itr_charge_lines.encumbered_amount%type;
177 l_unencumbered_amount igi_itr_charge_lines.unencumbered_amount%type;
178 l_gl_encumbered_date igi_itr_charge_lines.gl_encumbered_date%type;
179 l_currency_code igi_itr_charge_headers.currency_code%type;
180 l_period_num gl_period_statuses.period_num%type;
181 l_period_year gl_period_statuses.period_year%type;
182 l_quarter_num gl_period_statuses.quarter_num%type;
183 l_charge_name igi_itr_charge_headers.name%type; --shsaxena for bug 294823
184 l_description igi_itr_charge_lines.description%type;
185 l_reference_10 gl_interface.reference10%TYPE; --shsaxena for bug 2948237
186 l_charge_service_id igi_itr_charge_lines.charge_service_id%type;
187 l_originator_id igi_itr_charge_headers.it_originator_id%type;
188 l_service_name igi_itr_service.name%type;
189 l_status_code varchar2(1);
190 l_packet_id number;
191 l_fundschk_mode varchar2(1);
192 l_partial_reserv_flag varchar2(1);
193 l_return_code varchar2(4);
194 l_je_category_name gl_je_categories.user_je_category_name%type;
195 l_je_source_name gl_je_sources.user_je_source_name%type;
196 l_import_request_id number;
197 l_debug_loc varchar2(30) := 'Create_Actuals';
198 l_debug_info varchar2(100);
199 l_curr_calling_sequence varchar2(2000);
200
201 Begin
202 --------------------------------------------------------------------
203 l_curr_calling_sequence := 'IGI_ITR_GL_INTERFACE_PKG.'||l_debug_loc;
204 -- Retrieve system variables to be used by fundschecker --
205 --------------------------------------------------------------------
206 IGI_ITR_FUNDS_CONTROL_PKG.Fundscheck_Init(
207 l_chart_of_accounts_id,
208 p_set_of_books_id,
209 l_itr_enc_type_id,
210 l_gl_user_id,
211 l_curr_calling_sequence);
212
213 --------------------------------------------------------
214 l_debug_info := 'Open C_period cursor for start period';
215 --------------------------------------------------------
216 Open C_period(
217 p_set_of_books_id,
218 p_start_period);
219 Fetch C_period into
220 l_period_type,
221 l_start_period_year,
222 l_start_period_num;
223 Close C_period;
224
225 ------------------------------------------------------
226 l_debug_info := 'Open C_period cursor for end period';
227 ------------------------------------------------------
228 Open C_period (
229 p_set_of_books_id,
230 p_end_period);
231 Fetch C_period into
232 l_period_type,
233 l_end_period_year,
234 l_end_period_num;
235 Close C_period;
236
237 -----------------------------------------
238 l_debug_info := 'Open C_cat_name cursor';
239 -----------------------------------------
240 Open C_cat_name;
241 Fetch C_cat_name into l_je_category_name;
242 Close C_cat_name;
243
244 -----------------------------------------
245 l_debug_info := 'Open C_source_name cursor';
246 -----------------------------------------
247 Open C_source_name;
248 Fetch C_source_name into l_je_source_name;
249 Close C_source_name;
250
251 --------------------------------------------------
252 l_debug_info := 'Opening C_current_period Cursor';
253 --------------------------------------------------
254 Open C_current_period (p_set_of_books_id);
255 Fetch C_current_period Into
256 l_current_period_name,
257 l_current_period_num,
258 l_current_period_year,
259 l_current_quarter_num;
260 Close C_current_period;
261
262 If (l_current_period_name = l_period_name And
263 l_current_period_num = l_period_num And
264 l_current_period_year = l_period_year And
265 l_current_quarter_num = l_quarter_num) Then
266 -- Current period gl_encumbered date is sysdate else the first date of the period
267 l_gl_encumbered_date := sysdate;
268 End if;
269
270 ---------------------------------------------
271 l_debug_info := 'Open C_charge_lines cursor';
272 ---------------------------------------------
273 Open C_charge_lines (
274 p_set_of_books_id,
275 l_period_type,
276 l_start_period_year,
277 l_start_period_num,
278 l_end_period_year,
279 l_end_period_num);
280 Loop
281 --------------------------------------------
282 l_debug_info := 'Fetch from C_charge_lines';
283 --------------------------------------------
284 Fetch C_charge_lines Into
285 l_it_header_id,
286 l_it_service_line_id,
287 l_it_line_num,
288 l_ccid_dr,
289 l_amount,
290 l_ccid_cr,
291 l_encumbrance_flag,
292 l_encumbered_amount,
293 l_unencumbered_amount,
294 l_gl_encumbered_date,
295 l_period_name,
296 l_currency_code,
297 l_period_num,
298 l_period_year,
299 l_quarter_num,
300 l_charge_name,
301 l_description, -- shsaxena for bug 2948237
302 l_charge_service_id,
303 l_originator_id,
304 l_service_name;
305 Exit When C_charge_lines%notfound;
306
307 -- ITR Encumbrance is Enabled
308
309 If IGI_ITR_FUNDS_CONTROL_PKG.Encumbrance_Enabled(p_set_of_books_id)
310 and l_encumbrance_flag = 'Y'
311 and nvl(l_encumbered_amount,0) > 0
312 and nvl(l_unencumbered_amount,0) = 0 Then
313 l_status_code := 'P';
314 --------------------------------------------------------
315 -- Encumbrance enabled, setup GL Fundschecker parameters
316 --------------------------------------------------------
317 IGI_ITR_FUNDS_CONTROL_PKG.Setup_Gl_Fundschk_Params(
318 l_packet_id,
319 l_fundschk_mode,
320 l_partial_reserv_flag,
321 'A', --Approval
322 l_curr_calling_sequence);
323
324 --------------------------------------------------------------
325 -- Unencumbrance the funds before creating the actual journal
326 --------------------------------------------------------------
327 IGI_ITR_FUNDS_CONTROL_PKG.Bc_Packets_Insert(
328 l_packet_id,
329 p_set_of_books_id,
330 l_ccid_dr,
331 l_amount,
332 l_period_year,
333 l_period_num,
334 l_quarter_num,
335 l_gl_user_id,
336 l_itr_enc_type_id,
337 l_it_service_line_id,
338 l_charge_service_id,
339 l_originator_id,
340 'Internal Trading',
341 'IGIITRCC',
342 'E',
343 l_period_name,
344 l_currency_code,
345 l_status_code, -- 'C'or 'P'
346 'Y', --l_reversal_flag,
347 'R', -- l_status_flag,
348 'Y', --l_prevent_encumbrance_flag,
349 l_charge_name,--shsaxena for bug 2948237
350 -- l_description,
351 l_curr_calling_sequence);
352
353 ---------------------------------------
354 l_debug_info := 'Call GL_Fundschecker';
355 ---------------------------------------
356 /* Commented below code and added another call
357 since the GL funds checker has changed in R12. Changed during r12 uptake for bug#602857
358 /* If (Not GL_FUNDS_CHECKER_PKG.glxfck(
359 p_set_of_books_id,
360 l_packet_id,
361 l_fundschk_mode,
362 l_partial_reserv_flag,
363 'N',
364 'N',
365 NULL,
366 NULL,
367 l_return_code)) Then
368 APP_EXCEPTION.Raise_Exception;
369 End If; */
370 If (Not PSA_FUNDS_CHECKER_PKG.GLXFCK(p_set_of_books_id,
371 l_packet_id,
372 l_fundschk_mode,
373 'N',
374 'N',
375 NULL,
376 NULL,
377 'G',
378 l_return_code)) Then
379 APP_EXCEPTION.Raise_Exception;
380 End If;
381 --------------------------------------
382 l_debug_info := 'Process Return Code';
383 --------------------------------------
384 If (l_return_code in ('T', 'F', 'P')) Then
385 -------------------------------------------------------------------------
386 l_debug_info := 'Fundscheck failed, updating audit table - packet id';
387 -------------------------------------------------------------------------
388 null;
389 /*
390 Update igi_itr_charge_lines_audit
391 Set packet_id = l_packet_id
392 Where it_header_id = l_it_header_id
393 And it_service_line_id = l_it_service_line_id
394 And reversal_flag = 'N';
395 */
396 Else
397 -------------------------------------------------------------------------------
398 l_debug_info := 'Updating Charge Lines and Audit tables - unencumbered amount';
399 -------------------------------------------------------------------------------
400 Update igi_itr_charge_lines
401 Set unencumbered_amount = l_amount,
402 packet_id = l_packet_id
403 Where it_header_id = l_it_header_id
404 And it_service_line_id = l_it_service_line_id;
405
406 Update igi_itr_charge_lines_audit
407 Set unencumbered_amount = l_amount,
408 packet_id = l_packet_id
409 Where it_header_id = l_it_header_id
410 And it_service_line_id = l_it_service_line_id
411 And reversal_flag = 'N';
412
413 l_reference_10 := substr(l_charge_name || ' ' || l_it_line_num || ' ' || l_service_name,1,100);
414
415 Gl_interface_insert(
416 'NEW',
417 p_set_of_books_id,
418 l_gl_encumbered_date,
419 l_currency_code,
420 sysdate,
421 l_gl_user_id,
422 'A',
423 l_je_category_name,
424 l_je_source_name,
425 l_amount,
426 NULL,
427 l_amount,
428 NULL,
429 l_gl_encumbered_date,
430 l_je_category_name, -- reference1
431 l_charge_name, -- reference4 shsaxena for bug 2948237
432 --l_description, -- reference4
433 l_je_source_name, -- reference6
434 l_reference_10, -- reference10 shsaxena for bug 2948237
435 -- l_description || ' ' || l_it_line_num || ' ' || l_service_name, -- reference10
436 l_it_header_id, -- reference21
437 l_it_service_line_id, -- reference22
438 l_period_name,
439 l_chart_of_accounts_id,
440 l_currency_code,
441 l_ccid_dr,
442 null );
443
444 Gl_Interface_Insert(
445 'NEW',
446 p_set_of_books_id,
447 l_gl_encumbered_date,
448 l_currency_code,
449 sysdate,
450 l_gl_user_id,
451 'A',
452 l_je_category_name,
453 l_je_source_name,
454 NULL,
455 l_amount,
456 NULL,
457 l_amount,
458 l_gl_encumbered_date,
459 l_je_category_name, -- reference1
460 l_charge_name, --reference4 shsaxena for bug 2948237
461 --l_description, -- reference4
462 l_je_source_name, -- reference6
463 l_reference_10, -- reference10 --shsaxena for bug 2948237
464 -- l_description || ' ' || l_it_line_num || ' ' ||l_service_name, -- reference10
465 l_it_header_id, -- reference21
466 l_it_service_line_id, -- reference22
467 l_period_name,
468 l_chart_of_accounts_id,
469 l_currency_code,
470 l_ccid_cr,
471 null);
472
473 -----------------------------------------------------------------------
474 l_debug_info := 'Updating Charge Lines and Audit Table - posting flag';
475 -----------------------------------------------------------------------
476 Update igi_itr_charge_lines
477 Set posting_flag = 'Y'
478 Where it_header_id = l_it_header_id
479 And it_service_line_id = l_it_service_line_id;
480
481 Update igi_itr_charge_lines_audit
482 Set posting_flag = 'Y'
483 Where it_header_id = l_it_header_id
484 And reversal_flag = 'N';
485 End If;
486
487 Else -- ITR Encumbrance is not enabled
488
489 /*
490 --------------------------------------------------
491 l_debug_info := 'Opening C_current_period Cursor';
492 --------------------------------------------------
493 Open C_current_period (p_set_of_books_id);
494 Fetch C_current_period Into
495 l_current_period_name,
496 l_current_period_num,
497 l_current_period_year,
498 l_current_quarter_num;
499 Close C_current_period;
500
501 If (l_current_period_name = l_period_name And
502 l_current_period_num = l_period_num And
503 l_current_period_year = l_period_year And
504 l_current_quarter_num = l_quarter_num) Then
505 -- Current period gl_encumbered date is sysdate else the first date of the period
506 l_gl_encumbered_date := sysdate;
507 End if;
508 */
509
510 Gl_interface_insert(
511 'NEW',
512 p_set_of_books_id,
513 l_gl_encumbered_date,
514 l_currency_code,
515 sysdate,
516 l_gl_user_id,
517 'A',
518 l_je_category_name,
519 l_je_source_name,
520 l_amount,
521 NULL,
522 l_amount,
523 NULL,
524 l_gl_encumbered_date,
525 l_je_category_name, -- reference1
526 l_charge_name, --reference4 shsaxena for bug 2948237
527 --l_description, -- reference4
528 l_je_source_name, -- reference6
529 l_reference_10, -- reference 10 --shsaxena for bug 2948237
530 -- l_description || ' ' || l_it_line_num || ' ' || l_service_name, -- reference10
531 l_it_header_id, -- reference21
532 l_it_service_line_id, -- reference22
533 l_period_name,
534 l_chart_of_accounts_id,
535 l_currency_code,
536 l_ccid_dr,
537 null );
538
539 Gl_Interface_Insert(
540 'NEW',
541 p_set_of_books_id,
542 l_gl_encumbered_date,
543 l_currency_code,
544 sysdate,
545 l_gl_user_id,
546 'A',
547 l_je_category_name,
548 l_je_source_name,
549 NULL,
550 l_amount,
551 NULL,
552 l_amount,
553 l_gl_encumbered_date,
554 l_je_category_name, -- reference1
555 l_charge_name, --reference4 shsaxena for bug 2948237
556 --l_description, -- reference4
557 l_je_source_name, -- reference6
558 l_reference_10, --shsaxena for bug 2948237
559 --l_description || ' ' || l_it_line_num || ' ' ||l_service_name, -- reference10
560 l_it_header_id, -- reference21
561 l_it_service_line_id, -- reference22
562 l_period_name,
563 l_chart_of_accounts_id,
564 l_currency_code,
565 l_ccid_cr,
566 null);
567
568 -----------------------------------------------------------------------
569 l_debug_info := 'Updating Charge Lines and Audit Table - posting flag';
570 -----------------------------------------------------------------------
571 Update igi_itr_charge_lines
572 Set posting_flag = 'Y'
573 Where it_header_id = l_it_header_id
574 And it_service_line_id = l_it_service_line_id;
575
576 Update igi_itr_charge_lines_audit
577 Set posting_flag = 'Y'
578 Where it_header_id = l_it_header_id
579 And it_service_line_id = l_it_service_line_id
580 And reversal_flag = 'N';
581 End If;
582
583 End Loop;
584 Commit;
585
586
587 Init_Gl_Interface(
588 l_int_control,
589 p_set_of_books_id);
590
591 Insert_Control_Rec(l_int_control);
592
593 ----------------------------------------------------
594 l_debug_info := 'Submitting Journal Import Program';
595 ----------------------------------------------------
596 l_import_request_id := Fnd_Request.Submit_Request(
597 'SQLGL'
598 ,'GLLEZL'
599 ,NULL
600 ,NULL
601 ,FALSE
602 ,l_int_control.interface_run_id
603 ,p_set_of_books_id
604 ,'N'
605 ,NULL
606 ,NULL
607 ,'N'
608 ,'N');
609 Exception
610 When Others Then
611 If (SQLCODE <> -20001) Then
612 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
613 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
614 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
615 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Set of Books Id = ' || to_char(p_set_of_books_id)
616 ||', Period Start = '|| p_start_period
617 ||', Period End = '|| p_end_period);
618 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
619 IF ( l_unexp_level >= l_debug_level) THEN
620 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
621 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
622 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
623 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrpb.IGI_ITR_GL_INTERFACE_PKG.Create_Actuals.msg1',TRUE);
624 END IF;
625 End If;
626 APP_EXCEPTION.RAISE_EXCEPTION;
627 End Create_Actuals ;
628
629 Procedure Init_Gl_Interface(
630 p_int_control IN OUT NOCOPY glcontrol,
631 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%type) IS
632 l_debug_loc varchar2(30) := 'Init_Gl_Interface';
633 l_curr_calling_sequence varchar2(2000);
634 l_debug_info varchar2(100);
635 l_int_control_old glcontrol;
636 Begin
637 --------------------------------------------------------------------
638 l_curr_calling_sequence := 'IGI_ITR_GL_INTERFACE_PKG.'||l_debug_loc;
639 l_debug_info := 'Initializing GL Interface control variables';
640 --------------------------------------------------------------------
641
642 /* ssemwal for NOCOPY */
643 /* added l_int_control_old */
644
645 l_int_control_old := p_int_control;
646
647 Select gl_journal_import_s.Nextval,
648 p_set_of_books_id,
649 NULL, -- Narayanan said comment it (GL_INTERFACE_CONTROL_S.nextval,)
650 'S',
651 'Internal Trading'
652 Into
653 p_int_control.interface_run_id,
654 p_int_control.set_of_books_id,
655 p_int_control.group_id,
656 p_int_control.status,
657 p_int_control.je_source_name
658 From sys.dual ;
659 Exception
660 When Others Then
661 p_int_control := l_int_control_old;
662 If (SQLCODE <> -20001) Then
663 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
664 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
665 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
666 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
667 IF ( l_unexp_level >= l_debug_level) THEN
668 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
669 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
670 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
671 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrpb.IGI_ITR_GL_INTERFACE_PKG.Init_Gl_Interface.msg2',TRUE);
672 END IF;
673 End If;
674 APP_EXCEPTION.RAISE_EXCEPTION;
675 End Init_Gl_Interface;
676
677 PROCEDURE Insert_Control_Rec(
678 p_int_control in glcontrol) IS
679 l_debug_loc varchar2(30) := 'Insert_Control_Rec';
680 l_curr_calling_sequence varchar2(2000);
681 l_debug_info varchar2(100);
682 BEGIN
683 --------------------------------------------------------------------
684 l_curr_calling_sequence := 'IGI_ITR_GL_INTERFACE_PKG.'||l_debug_loc;
685 l_debug_info := 'Inserting into gl_interface_control';
686 --------------------------------------------------------------------
687 Insert Into gl_interface_control(
688 je_source_name,
689 status,
690 interface_run_id,
691 group_id,
692 set_of_books_id)
693 Values(
694 p_int_control.je_source_name,
695 p_int_control.status,
696 p_int_control.interface_run_id,
697 p_int_control.group_id,
698 p_int_control.set_of_books_id);
699 Exception
700 When Others Then
701 If (SQLCODE <> -20001) Then
702 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
703 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
704 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
705 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
706 IF ( l_unexp_level >= l_debug_level) THEN
707 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
708 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
709 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
710 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrpb.IGI_ITR_GL_INTERFACE_PKG.Insert_Control_Rec.msg3',TRUE);
711 END IF;
712 EnD If;
713 APP_EXCEPTION.RAISE_EXCEPTION;
714 End Insert_Control_Rec;
715
716 Procedure Gl_Interface_Insert(
717 p_status IN gl_interface.status%type,
718 p_set_of_books_id IN gl_interface.set_of_books_id%type,
719 p_accounting_date IN gl_interface.accounting_date%type,
720 p_currency_code IN gl_interface.currency_code%type,
721 p_date_created IN gl_interface.date_created%type,
722 p_created_by IN gl_interface.created_by%type,
723 p_actual_flag IN gl_interface.actual_flag%type,
724 p_user_je_category_name IN gl_interface.user_je_category_name%type,
725 p_user_je_source_name IN gl_interface.user_je_source_name%type,
726 p_entered_dr IN gl_interface.entered_dr%type,
727 p_entered_cr IN gl_interface.entered_cr%type,
728 p_accounted_dr IN gl_interface.accounted_dr%type,
729 p_accounted_cr IN gl_interface.accounted_cr%type,
730 p_transaction_date IN gl_interface.transaction_date%type,
731 p_reference1 IN gl_interface.reference1%type,
732 p_reference4 IN gl_interface.reference4%type,
733 p_reference6 IN gl_interface.reference6%type,
734 p_reference10 IN gl_interface.reference10%type,
735 p_reference21 IN gl_interface.reference21%type,
736 p_reference22 IN gl_interface.reference22%type,
737 p_period_name IN gl_interface.period_name%type,
738 p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%type,
739 p_functional_currency_code IN gl_interface.functional_currency_code%type,
740 p_code_combination_id IN gl_interface.code_combination_id%type,
741 p_group_id IN gl_interface.group_id%type) IS
742 l_debug_loc varchar2(30) := 'GL_interface';
743 l_curr_calling_sequence varchar2(2000);
744 l_debug_info varchar2(100);
745 Begin
746 ----------------------------------------------------------------------
747 l_curr_calling_sequence := 'IGI_ITR_GL_INTERFACE_PKG.' || l_debug_loc;
748 l_debug_info := 'Inserting record into gl_interface';
749 ----------------------------------------------------------------------
750 Insert Into gl_interface(
751 status,
752 set_of_books_id,
753 accounting_date,
754 currency_code,
755 date_created,
756 created_by,
757 actual_flag,
758 user_je_category_name,
759 user_je_source_name,
760 entered_dr,
761 entered_cr,
762 accounted_dr,
763 accounted_cr,
764 transaction_date,
765 reference1,
766 reference4,
767 reference6,
768 reference10,
769 reference21,
770 reference22,
771 period_name,
772 chart_of_accounts_id,
773 functional_currency_code,
774 code_combination_id,
775 group_id)
776 Values(
777 p_status,
778 p_set_of_books_id,
779 p_accounting_date,
780 p_currency_code,
781 p_date_created,
782 p_created_by,
783 p_actual_flag,
784 p_user_je_category_name,
785 p_user_je_source_name,
786 p_entered_dr,
787 p_entered_cr,
788 p_accounted_dr,
789 p_accounted_cr,
790 p_transaction_date,
791 p_reference1,
792 p_reference4,
793 p_reference6,
794 p_reference10,
795 p_reference21,
796 p_reference22,
797 p_period_name,
798 p_chart_of_accounts_id,
799 p_currency_code,
800 p_code_combination_id,
801 p_group_id );
802 Exception
803 When Others Then
804 If (SQLCODE <> -20001) Then
805 FND_MESSAGE.SET_NAME('IGI','IGI_ITR_DEBUG');
806 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
807 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE', l_curr_calling_sequence);
808 FND_MESSAGE.SET_TOKEN('PARAMETERS', 'Status = ' || p_status
809 ||', Set_of_books_id = '|| to_char(p_set_of_books_id)
810 ||', Accounting_date = '|| to_char(p_accounting_date,'DD-MON-YYYY')
811 ||', Currency Code = '|| p_currency_code
812 ||', Date Created = '|| to_char(p_date_created,'DD-MON-YYYY')
813 ||', Created By = '|| to_char(p_created_by)
814 ||', Actual flag = '|| p_actual_flag
815 ||', User Je Category Name = '|| p_user_je_category_name
816 ||', User Je Source Name = '|| p_user_je_source_name
817 ||', Entered Dr = '|| to_char(p_entered_dr)
818 ||', Entered Cr = '|| to_char(p_entered_cr)
819 ||', Accounted Dr = '|| to_char(p_accounted_dr)
820 ||', Accounted Cr = '|| to_char(p_accounted_cr)
821 ||', Transaction Date = '|| to_char(p_transaction_date,'DD-MON-YYYY')
822 ||', Reference1 = '|| p_reference1
823 ||', Reference4 = '|| p_reference4
824 ||', Reference6 = '|| p_reference6
825 ||', Reference10 = '|| p_reference10
826 ||', Reference21 = '|| p_reference21
827 ||', Reference22 = '|| p_reference22
828 ||', Period Name = '|| p_period_name
829 ||', Chart of Accounts Id = '|| to_char(p_chart_of_accounts_id)
830 ||', Functional Currency Code = '|| p_currency_code
831 ||', Code Combination Id = '|| to_char(p_code_combination_id)
832 ||', Group Id = '|| to_char(p_group_id));
833 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',l_debug_info);
834 IF ( l_unexp_level >= l_debug_level) THEN
835 FND_MESSAGE.SET_NAME('IGI','IGI_LOGGING_UNEXP_ERROR');
836 FND_MESSAGE.SET_TOKEN('CODE',SQLCODE);
837 FND_MESSAGE.SET_TOKEN('MSG', SQLERRM);
838 FND_LOG.MESSAGE (l_unexp_level,'igi.plsql.igiitrpb.IGI_ITR_GL_INTERFACE_PKG.Gl_Interface_Insert.msg4',TRUE);
839 END IF;
840 End If;
841 APP_EXCEPTION.RAISE_EXCEPTION;
842 End Gl_Interface_Insert;
843
844 END IGI_ITR_GL_INTERFACE_PKG;