[Home] [Help]
PACKAGE BODY: APPS.IGIPREC
Source
1 PACKAGE BODY IGIPREC AS
2 -- $Header: igiprecb.pls 115.7 2002/11/18 14:08:30 panaraya ship $
3
4 --------------------------------------------------------------------
5 --To fetch the Cash_Set_of_Books_Id Chart Of Accounts
6 --------------------------------------------------------------------
7 CURSOR C_system_parameters IS
8 SELECT ap.secondary_set_of_books_id,
9 gl.chart_of_accounts_id
10 FROM ap_system_parameters ap,
11 gl_sets_of_books gl
12 WHERE ap.set_of_books_id = gl.set_of_books_id;
13 --------------------------------------------------------------------
14 --To fetch the Concurrent Request Id
15 --Use function FND_GLOBAL.CONC_REQUEST_ID
16 --------------------------------------------------------------------
17 --------------------------------------------------------------------
18 --To fetch the Source (Payables)
19 --------------------------------------------------------------------
20 CURSOR C_source_name IS
21 SELECT je_source_name , user_je_source_name
22 FROM gl_je_sources
23 WHERE je_source_name = 'Payables'
24 AND LANGUAGE = USERENV('LANG');
25 --------------------------------------------------------------------
26 --To fetch the Category (Manual adjustments)
27 --------------------------------------------------------------------
28 CURSOR C_category_name IS
29 SELECT je_category_name , user_je_category_name
30 FROM gl_je_categories
31 WHERE je_category_name = '41'
32 AND LANGUAGE = USERENV('LANG');
33 --------------------------------------------------------------------
34 --To set the start_date and the end_date sources from parameters From Period and To Period.
35 --------------------------------------------------------------------
36 CURSOR C_period_startdate (p_period gl_period_statuses.period_name%type, p_secondary_set_of_books_id gl_interface.set_of_books_id%type ) IS
37 SELECT start_date
38 FROM gl_period_statuses
39 WHERE period_name = p_period
40 AND set_of_books_id = p_secondary_set_of_books_id
41 AND application_id = (SELECT application_id FROM fnd_application WHERE application_short_name ='SQLGL');
42
43 CURSOR C_period_enddate (p_period gl_period_statuses.period_name%type , p_secondary_set_of_books_id gl_interface.set_of_books_id%type ) IS
44 SELECT end_date
45 FROM gl_period_statuses
46 WHERE period_name = p_period
47 AND set_of_books_id = p_secondary_set_of_books_id
48 AND application_id =
49 (SELECT application_id FROM fnd_application WHERE application_short_name = 'SQLGL');
50
51 --------------------------------------------------------------------
52 -- To fetch the non recoverable tax lines to be modified
53 --------------------------------------------------------------------
54 CURSOR C_get_tax_lines ( start_date date, end_date date) IS
55 SELECT
56 inv.invoice_id INVOICE_ID ,
57 inv.invoice_date INVOICE_DATE,
58 inv_dist1.set_of_books_id ACCURAL_SET_OF_BOOKS_ID,
59 inv_dist1.invoice_distribution_id TAX_DIST_ID,
60 inv_dist1.dist_code_combination_id TAX_CCID,
61 chrg.allocated_base_amount TAX_AMOUNT,
62 inv_dist2.invoice_distribution_id ITEM_DIST_ID,
63 inv_dist2.dist_code_combination_id ITEM_CCID ,
64 inv_dist2.org_id ORG_ID,
68 ap_chrg_allocations chrg,
65 inv.invoice_currency_code INV_CURRENCY_CODE
66 FROM
67 ap_invoice_distributions inv_dist1,
69 ap_invoice_distributions inv_dist2,
70 ap_invoices inv
71 WHERE
72 inv_dist1.cash_posted_flag = 'Y' AND
73 inv_dist1.tax_recoverable_flag = 'Y' AND inv_dist1.line_type_lookup_code = 'TAX'
74 AND inv_dist1.invoice_distribution_id = chrg.charge_dist_id
75 AND inv_dist2.invoice_distribution_id = chrg.item_dist_id
76 AND inv.invoice_id = inv_dist1.invoice_id
77 AND inv.invoice_date BETWEEN start_date AND end_date
78 AND NOT EXISTS
79 ( SELECT 'Y' FROM IGI_RECOVERABLE_LINES WHERE tax_distribution_id = chrg.charge_dist_id);
80
81 Procedure Writelog(
82 p_mesg IN varchar2
83 )
84 is
85 Begin
86 fnd_file.put_line( fnd_file.log , p_mesg ) ;
87 End Writelog;
88
89 Procedure Gl_Interface_Insert(
90 p_status IN gl_interface.status%type,
91 p_set_of_books_id IN gl_interface.set_of_books_id%type,
92 p_accounting_date IN gl_interface.accounting_date%type,
93 p_currency_code IN gl_interface.currency_code%type,
94 p_date_created IN gl_interface.date_created%type,
95 p_created_by IN gl_interface.created_by%type,
96 p_actual_flag IN gl_interface.actual_flag%type,
97 p_user_je_category_name IN gl_interface.user_je_category_name%type,
98 p_user_je_source_name IN gl_interface.user_je_source_name%type,
99 p_entered_dr IN gl_interface.entered_dr%type,
100 p_entered_cr IN gl_interface.entered_cr%type,
101 p_accounted_dr IN gl_interface.accounted_dr%type,
102 p_accounted_cr IN gl_interface.accounted_cr%type,
103 p_transaction_date IN gl_interface.transaction_date%type,
104 p_reference1 IN gl_interface.reference1%type,
105 p_reference4 IN gl_interface.reference4%type,
106 p_reference6 IN gl_interface.reference6%type,
107 p_reference10 IN gl_interface.reference10%type,
108 p_reference21 IN gl_interface.reference21%type,
109 p_reference22 IN gl_interface.reference22%type,
110 p_period_name IN gl_interface.period_name%type,
111 p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%type,
112 p_functional_currency_code IN gl_interface.functional_currency_code%type,
113 p_code_combination_id IN gl_interface.code_combination_id%type,
114 p_group_id IN gl_interface.group_id%type);
115
116 PROCEDURE Init_Gl_Interface(
117 p_int_control IN OUT NOCOPY glcontrol,
118 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%type);
119
120 PROCEDURE Insert_Control_Rec(
121 p_int_control in glcontrol );
122
123
124
125
126 PROCEDURE Submit(
127 errbuf OUT NOCOPY VARCHAR2,
128 retcode OUT NOCOPY NUMBER,
129 p_gl_from_period in gl_period_statuses.period_name%type,
130 p_gl_to_period in gl_period_statuses.period_name%type
131 ) is
132 l_request_id number ;
133 l_secondary_set_of_books_id NUMBER;
134 l_chart_of_accounts_id NUMBER;
135 l_je_source_name gl_je_sources.je_source_name%type;
136 l_user_je_source_name gl_je_sources.user_je_source_name%type;
137 l_je_category_name gl_je_categories.je_category_name%type;
138 l_user_je_category_name gl_je_categories.user_je_category_name%type;
139 l_start_date DATE;
140 l_end_date DATE;
141 l_processed Boolean;
142 l_int_control glcontrol;
143 l_import_request_id number;
144 l_report_request_id number;
145 -------------------------------------------------------------------
146 --To fetch the Cash_Set_of_Books_Id and Chart Of Accounts
147 --------------------------------------------------------------------
148
149
150
151 -- l_tax_lines C_get_tax_lines%rowtype;
152 begin
153 --------------------------------------------------------------------
154 -- get the secondary Secondary set of books and Chart of accounts
155 --------------------------------------------------------------------
156 fnd_profile.get('ORG_ID',l_secondary_set_of_books_id);
157
158 WriteLog( '>> Initialized org_id '||l_secondary_set_of_books_id);
159 Open C_system_parameters;
160
161 Fetch C_system_parameters into l_secondary_set_of_books_id,l_chart_of_accounts_id;
162 Close C_system_parameters;
163
164 WriteLog( '>> Initialized Secondary set of books and Chart of accounts '||l_secondary_set_of_books_id||' '||l_chart_of_accounts_id );
165
166 Open C_period_startdate (p_gl_from_period,l_secondary_set_of_books_id );
167 Fetch C_period_startdate into l_start_date;
168 Close C_period_startdate;
169 WriteLog( '>> Initialized Start period ' || l_start_date );
170 Open C_period_enddate (p_gl_to_period,l_secondary_set_of_books_id);
171 Fetch C_period_enddate into l_end_date;
172 Close C_period_enddate;
173 WriteLog( '>> Initialized end period ' || l_end_date);
174
175 Open C_source_name ;
176 Fetch C_source_name into l_je_source_name,l_user_je_source_name;
177 Close C_source_name;
178
179 WriteLog( '>> Initialized user journal name <<<');
180
181 Open C_category_name ;
182 Fetch C_category_name into l_je_category_name,l_user_je_category_name;
186
183 Close C_category_name ;
184
185 WriteLog( '>> Initialized user journal category name <<<');
187 --------------------------------------------------------------------
188 -- Fetch the recoverable tax lines from the invoices in the given dates
189 --------------------------------------------------------------------
190 l_processed := false;
191
192 For l_tax_lines in C_get_tax_lines ( l_start_date,l_end_date) loop
193
194 WriteLog( '>> Processing Invoice '|| l_tax_lines.invoice_id ||' Distribution Num '||l_tax_lines.tax_dist_id);
195
196 if not l_processed then
197 l_processed := true;
198 end if;
199
200 --------------------------------------------------------------------
201 --insert into IGI_RECOVERABLE_LINES
202 --------------------------------------------------------------------
203
204 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
205
206 insert into IGI_RECOVERABLE_LINES
207
208 ( Invoice_id,
209 Accounting_date,
210 Invoice_date,
211 Inv_Currency_Code,
212 Accrual_Set_of_books_id,
213 Request_Id,
214 Tax_distribution_id,
215 Tax_ccid,
216 Tax_amount,
217 Item_distribution_id,
218 Item_ccid,
219 last_updated_by,
220 last_update_date,
221 created_by,
222 Created_date,
223 Last_update_login)
224 values ( l_tax_lines.invoice_id,
225 sysdate,
226 l_tax_lines.invoice_date,
227 l_tax_lines.INV_CURRENCY_CODE,
228 l_tax_lines.ACCURAL_SET_OF_BOOKS_ID,
229 l_request_id,
230 l_tax_lines.tax_dist_id,
231 l_tax_lines.tax_ccid,
232 l_tax_lines.tax_amount,
233 l_tax_lines.item_dist_id,
234 l_tax_lines.item_ccid,
235 to_number(fnd_profile.value('USER_ID')),
236 sysdate,
237 to_number(fnd_profile.value('LOGIN_ID')),
238 sysdate,
239 to_number(fnd_profile.value('USER_ID')));
240
241 --------------------------------------------------------------------
242 /* For each recoverable tax line identified insert two line into GL Interface
243 IF the tax amount is positive THEN
244 Debit the Item Line Code Combination Id with the Tax Line Amount
245 Credit the Tax Line Code Combination Id with the Tax Line Amount
246 ELSEIF the tax amount is negative THEN
247 Debit Tax Line ccid with absolute Tax Line Amount
248 Credit Item Line ccid with absolute Tax Line Amount
249 END IF*/
250 --------------------------------------------------------------------
251
252 -- Start(1) bug 2119400 vgadde 23-NOV-2001
253
254 IF ( l_tax_lines.tax_amount > 0 ) THEN
255
256 -- End(1) bug 2119400 vgadde 23-NOV-2001
257
258 -----------------------------------------------------
259 -- Debit entry for item ccid for positive tax amount
260 -----------------------------------------------------
261 Gl_interface_insert(
262 'NEW',
263 l_secondary_set_of_books_id,
264 sysdate,
265 l_tax_lines.INV_CURRENCY_CODE,
266 sysdate,
267 to_number(fnd_profile.value('USER_ID')),
268 'A',
269 l_user_je_category_name,
270 l_user_je_source_name,
271 abs(l_tax_lines.tax_amount),
272 NULL,
273 abs(l_tax_lines.tax_amount),
274 NULL,
275 sysdate,
276 l_je_category_name, -- reference1
277 NULL, -- reference4
278 l_je_source_name, -- reference6
279 NULL, -- reference10
280 l_tax_lines.invoice_id, -- reference21
281 l_tax_lines.tax_dist_id, -- reference22
282 NULL,
283 l_chart_of_accounts_id,
284 l_tax_lines.inv_currency_code,
285 l_tax_lines.item_ccid,
286 null );
287 --------------------------------------------------------------------
288 -- credit entry for tax ccid for positive tax amount
289 --------------------------------------------------------------------
290 Gl_Interface_Insert(
291 'NEW',
292 l_secondary_set_of_books_id,
293 sysdate,
294 l_tax_lines.INV_CURRENCY_CODE,
295 sysdate,
296 to_number(fnd_profile.value('USER_ID')),
297 'A',
298 l_user_je_category_name,
299 l_user_je_source_name,
300 NULL,
301 abs(l_tax_lines.tax_amount),
302 NULL,
303 abs(l_tax_lines.tax_amount),
304 sysdate,
305 l_je_category_name, -- reference1
306 NULL, -- reference4
307 l_je_source_name, -- reference6
308 NULL, -- reference10
309 l_tax_lines.invoice_id, -- reference21
310 l_tax_lines.tax_dist_id, -- reference22
311 NULL,
312 l_chart_of_accounts_id,
313 l_tax_lines.inv_currency_code,
314 l_tax_lines.tax_ccid,
315 null );
316
317 --Start(2) bug 2119400 vgadde 23-NOV-2001
318
319 ELSIF ( l_tax_lines.tax_amount < 0 ) THEN
320 -----------------------------------------------------------------
321 -- Debit entry for tax ccid for negative tax amount
325 l_secondary_set_of_books_id,
322 -----------------------------------------------------------------
323 Gl_interface_insert(
324 'NEW',
326 sysdate,
327 l_tax_lines.INV_CURRENCY_CODE,
328 sysdate,
329 to_number(fnd_profile.value('USER_ID')),
330 'A',
331 l_user_je_category_name,
332 l_user_je_source_name,
333 abs(l_tax_lines.tax_amount),
334 NULL,
335 abs(l_tax_lines.tax_amount),
336 NULL,
337 sysdate,
338 l_je_category_name, -- reference1
339 NULL, -- reference4
340 l_je_source_name, -- reference6
341 NULL, -- reference10
342 l_tax_lines.invoice_id, -- reference21
343 l_tax_lines.tax_dist_id, -- reference22
344 NULL,
345 l_chart_of_accounts_id,
346 l_tax_lines.inv_currency_code,
347 l_tax_lines.tax_ccid,
348 null );
349 --------------------------------------------------------------------
350 -- credit entry for item ccid for negative amount
351 --------------------------------------------------------------------
352 Gl_Interface_Insert(
353 'NEW',
354 l_secondary_set_of_books_id,
355 sysdate,
356 l_tax_lines.INV_CURRENCY_CODE,
357 sysdate,
358 to_number(fnd_profile.value('USER_ID')),
359 'A',
360 l_user_je_category_name,
361 l_user_je_source_name,
362 NULL,
363 abs(l_tax_lines.tax_amount),
364 NULL,
365 abs(l_tax_lines.tax_amount),
366 sysdate,
367 l_je_category_name, -- reference1
368 NULL, -- reference4
369 l_je_source_name, -- reference6
370 NULL, -- reference10
371 l_tax_lines.invoice_id, -- reference21
372 l_tax_lines.tax_dist_id, -- reference22
373 NULL,
374 l_chart_of_accounts_id,
375 l_tax_lines.inv_currency_code,
376 l_tax_lines.item_ccid,
377 null );
378 END IF;
379
380 -- End(2) bug 2119400 vgadde 23-NOV-2001
381
382 End loop;
383 WriteLog( '>> IGI_RECOVERABLE_LINES records transferred to GL Interface');
384
385 l_report_request_id := fnd_request.submit_request
386 ('IGI',
387 'IGIPRECL',
388 null,
389 null,
390 false,
391 p_gl_from_period,
392 p_gl_to_period,
393 l_request_id);
394 /* ' ' , ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ',
395 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
396 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
397 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
398 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
399 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
400 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
401 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
402 ' ', ' ', ' ', ' ', ' ', ' ', ' ',' ', ' ', ' ',
403 ' ',' ',' ', ' ',' ',' ',' ',' ' );*/
404
405
406
407 WriteLog( '>> report request '||l_report_request_id);
408 Init_Gl_Interface(
409 l_int_control,
410 l_secondary_set_of_books_id);
411 Insert_Control_Rec(l_int_control);
412 ----------------------------------------------------
413 -- 'Submitting Journal Import Program';
414 ----------------------------------------------------
415 WriteLog( '>> Submitting GL Import routine ');
416 if l_processed then -- records found in gl_interface
417 WriteLog( '>> Adjustment records transferred to GL Interface');
418 l_import_request_id := Fnd_Request.Submit_Request(
419 'SQLGL'
420 ,'GLLEZL'
421 ,NULL
422 ,NULL
423 ,FALSE
424 ,l_int_control.interface_run_id
425 ,l_secondary_set_of_books_id
426 ,'N'
427 ,NULL
428 ,NULL
429 ,'N'
430 ,'N');
431 errbuf := 'Submitted import request '||l_request_id;
432 retcode := 0;
433 commit;
434 else
435 errbuf := 'No records found to process No records inserted into GL INTERFACE';
436 retcode := 0;
440 --------------------------------------------------------------------
437 rollback;
438 end if;
439
441 -- update all the rows after inserting to gl_interface table
442 -- so that the lines are not consider when run next time
443 --------------------------------------------------------------------
444 if l_import_request_id = 0 then
445 update IGI_RECOVERABLE_LINES
446 set je_created_flag = 'Y'
447 where request_id =FND_GLOBAL.CONC_REQUEST_ID ;
448 WriteLog( '>> IGI_RECOVERABLE_LINES updated ');
449 end if;
450
451 end;
452
453 Procedure Init_Gl_Interface(
454 p_int_control IN OUT NOCOPY glcontrol,
455 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%type) IS
456 l_debug_loc varchar2(30) := 'Init_Gl_Interface';
457 l_curr_calling_sequence varchar2(2000);
458 l_debug_info varchar2(100);
459 Begin
460 --------------------------------------------------------------------
461 -- 'Initializing GL Interface control variables';
462 --------------------------------------------------------------------
463 Select gl_journal_import_s.Nextval,
464 p_set_of_books_id,
465 NULL,
466 'S',
467 'Payables'
468 Into
469 p_int_control.interface_run_id,
470 p_int_control.set_of_books_id,
471 p_int_control.group_id,
472 p_int_control.status,
473 p_int_control.je_source_name
474 From sys.dual ;
475 Exception
476 When Others Then
477 Null;
478 End Init_Gl_Interface;
479 PROCEDURE Insert_Control_Rec(
480 p_int_control in glcontrol) IS
481 l_debug_loc varchar2(30) := 'Insert_Control_Rec';
482 l_curr_calling_sequence varchar2(2000);
483 l_debug_info varchar2(100);
484 BEGIN
485 --------------------------------------------------------------------
486 --l_debug_info := 'Inserting into gl_interface_control';
487 --------------------------------------------------------------------
488 Insert Into gl_interface_control(
489 je_source_name,
490 status,
491 interface_run_id,
492 group_id,
493 set_of_books_id)
494 Values(
495 p_int_control.je_source_name,
496 p_int_control.status,
497 p_int_control.interface_run_id,
498 p_int_control.group_id,
499 p_int_control.set_of_books_id);
500 Exception
501 When Others Then
502 Null;
503 End Insert_Control_Rec;
504
505 Procedure Gl_Interface_Insert(
506 p_status IN gl_interface.status%type,
507 p_set_of_books_id IN gl_interface.set_of_books_id%type,
508 p_accounting_date IN gl_interface.accounting_date%type,
509 p_currency_code IN gl_interface.currency_code%type,
510 p_date_created IN gl_interface.date_created%type,
511 p_created_by IN gl_interface.created_by%type,
512 p_actual_flag IN gl_interface.actual_flag%type,
513 p_user_je_category_name IN gl_interface.user_je_category_name%type,
514 p_user_je_source_name IN gl_interface.user_je_source_name%type,
515 p_entered_dr IN gl_interface.entered_dr%type,
516 p_entered_cr IN gl_interface.entered_cr%type,
517 p_accounted_dr IN gl_interface.accounted_dr%type,
518 p_accounted_cr IN gl_interface.accounted_cr%type,
519 p_transaction_date IN gl_interface.transaction_date%type,
520 p_reference1 IN gl_interface.reference1%type,
521 p_reference4 IN gl_interface.reference4%type,
522 p_reference6 IN gl_interface.reference6%type,
523 p_reference10 IN gl_interface.reference10%type,
524 p_reference21 IN gl_interface.reference21%type,
525 p_reference22 IN gl_interface.reference22%type,
526 p_period_name IN gl_interface.period_name%type,
527 p_chart_of_accounts_id IN gl_interface.chart_of_accounts_id%type,
528 p_functional_currency_code IN gl_interface.functional_currency_code%type,
529 p_code_combination_id IN gl_interface.code_combination_id%type,
530 p_group_id IN gl_interface.group_id%type) IS
531 l_debug_loc varchar2(30) := 'GL_interface';
532 l_curr_calling_sequence varchar2(2000);
533 l_debug_info varchar2(100);
534 Begin
535 ----------------------------------------------------------------------
536 l_curr_calling_sequence := 'IGI_ITR_GL_INTERFACE_PKG.' || l_debug_loc;
537 l_debug_info := 'Inserting record into gl_interface';
538 ----------------------------------------------------------------------
539 Insert Into gl_interface(
540 status,
541 set_of_books_id,
542 accounting_date,
543 currency_code,
544 date_created,
545 created_by,
546 actual_flag,
547 user_je_category_name,
548 user_je_source_name,
549 entered_dr,
550 entered_cr,
551 accounted_dr,
552 accounted_cr,
553 transaction_date,
554 reference1,
555 reference4,
556 reference6,
557 reference10,
558 reference21,
559 reference22,
560 period_name,
561 chart_of_accounts_id,
562 functional_currency_code,
563 code_combination_id,
564 group_id)
565 Values(
566 p_status,
567 p_set_of_books_id,
568 p_accounting_date,
569 p_currency_code,
570 p_date_created,
571 p_created_by,
572 p_actual_flag,
573 p_user_je_category_name,
574 p_user_je_source_name,
575 p_entered_dr,
576 p_entered_cr,
577 p_accounted_dr,
578 p_accounted_cr,
579 p_transaction_date,
580 p_reference1,
581 p_reference4 ,
582 p_reference6,
583 p_reference10,
584 p_reference21,
585 p_reference22,
586 p_period_name,
587 p_chart_of_accounts_id,
588 p_currency_code,
589 p_code_combination_id,
590 p_group_id );
591 Exception
592 When Others Then
593 writelog ( '>>>' || sqlerrm(sqlcode) || '<<<<<');
594 Null;
595 End Gl_Interface_Insert;
596 END IGIPREC;