[Home] [Help]
PACKAGE BODY: APPS.FV_1219_TRANSACTIONS
Source
1 PACKAGE BODY fv_1219_transactions as
2 /* $Header: FVX1219B.pls 120.29.12010000.1 2008/07/28 06:32:28 appldev ship $ */
3 g_module_name VARCHAR2(100) := 'fv.plsql.fv_1219_transactions.';
4
5 flex_num number;
6 period_type varchar2(25) := NULL;
7 bl_seg_name varchar2(40);
8 gl_seg_name varchar2(40);
9 transaction_count number := 0;
10 p_set_bks_id number;
11 p_gl_period varchar2(25);
12 p_alc_code ce_bank_accounts.agency_location_code%TYPE;
13 p_delete_corrections varchar2(1);
14 p_error_code number;
15 p_error_msg varchar2(150);
16
17 l_start_date1 GL_PERIODS.start_date%TYPE;
18 l_end_date1 GL_PERIODS.end_date%TYPE;
19 l_gl_period GL_PERIODS.period_name%TYPE;
20 l_start_date2 GL_PERIODS.start_date%TYPE;
21 l_end_date2 GL_PERIODS.end_date%TYPE;
22 l_period_year GL_PERIODS.period_year%TYPE;
23
24 l_rowid varchar2(25);
25
26 l_fund_code FV_SF1219_TEMP.fund_code%TYPE;
27 l_name FV_SF1219_TEMP.name%TYPE;
28 l_name_keep FV_SF1219_TEMP.name%TYPE;
29 l_set_of_books_id FV_SF1219_TEMP.set_of_books_id%TYPE;
30 l_sf1219_type_code FV_SF1219_TEMP.sf1219_type_code%TYPE;
31 l_reported_month FV_SF1219_TEMP.reported_month%TYPE;
32 l_posted_date FV_SF1219_TEMP.posted_date%TYPE;
33 l_reported_gl_period FV_SF1219_TEMP.reported_gl_period%TYPE;
34 l_amount FV_SF1219_TEMP.amount%TYPE;
35 l_batch_id FV_SF1219_TEMP.batch_id%TYPE;
36
37 l_je_header_id gl_je_headers.je_header_id%TYPE;
38 l_je_line_num gl_je_lines.je_line_num%TYPE;
39 l_currency_code gl_ledgers_public_v.currency_code%TYPE;
40
41 l_reference_1 FV_SF1219_TEMP.reference_1%TYPE;
42 l_reference_2 FV_SF1219_TEMP.reference_2%TYPE;
43 l_reference_3 FV_SF1219_TEMP.reference_3%TYPE;
44 l_reference_4 FV_SF1219_TEMP.reference_4%TYPE;
45 l_reference_5 FV_SF1219_TEMP.reference_5%TYPE;
46 l_reference_6 FV_SF1219_TEMP.reference_6%TYPE;
47 l_reference_9 FV_SF1219_TEMP.reference_9%TYPE;
48 l_exception_category FV_SF1219_TEMP.exception_category%TYPE;
49 l_accomplish_month FV_SF1219_TEMP.accomplish_month%TYPE;
50 l_default_period_name FV_SF1219_TEMP.default_period_name%TYPE;
51 l_obligation_date FV_SF1219_TEMP.obligation_date%TYPE;
52 l_inter_agency_flag FV_SF1219_TEMP.inter_agency_flag%TYPE;
53 l_treasury_symbol FV_SF1219_TEMP.treasury_symbol%TYPE;
54
55 l_treasury_symbol_id FV_SF1219_TEMP.treasury_symbol_id%TYPE;
56 l_record_type FV_SF1219_TEMP.record_type%TYPE;
57 l_alc_code FV_SF1219_TEMP.alc_code%TYPE;
58 l_temp_alc_code FV_SF1219_TEMP.alc_code%TYPE;
59 l_org_id FV_SF1219_TEMP.org_id%TYPE := mo_global.get_current_org_id;
60 l_group_name FV_SF1219_TEMP.group_name%TYPE;
61 l_accomplish_date FV_SF1219_TEMP.accomplish_date%TYPE;
62 l_ref6_date_check FV_SF1219_TEMP.accomplish_date%TYPE;
63 l_update_type FV_SF1219_TEMP.update_type%TYPE;
64 l_type FV_SF1219_TEMP.type%TYPE;
65 l_gl_period_name FV_SF1219_TEMP.gl_period_name%TYPE;
66 l_processed_flag FV_SF1219_TEMP.processed_flag%TYPE;
67 l_lines_exist FV_SF1219_TEMP.lines_exist%TYPE;
68
69 l_invoice_id AP_INVOICES_ALL.invoice_id%TYPE;
70 l_vendor_id AP_INVOICES_ALL.vendor_id%TYPE;
71 l_payables_ia_paygroup FV_OPERATING_UNITS_ALL.payables_ia_paygroup%TYPE;
72 l_cb_flag FV_INTERAGENCY_FUNDS_ALL.chargeback_flag%TYPE;
73 l_billing_agency_fund FV_INTERAGENCY_FUNDS_ALL.billing_agency_fund%TYPE;
74 l_dit_flag varchar2(2);
75 l_error_stage number;
76 l_inv_amount number;
77 l_yr_start_date date;
78 l_yr_end_date date;
79 l_check_date date;
80 l_void_date date;
81
82 x_amount number;
83 l_cash_receipt_id number;
84 null_var varchar2(2);
85 l_invoice_date date;
86 -- g_debug BOOLEAN := FALSE;
87
88 l_cash_receipt_hist_id NUMBER;
89 l_temp_cr_hist_id NUMBER;
90 p_def_org_id NUMBER(15) := l_org_id;
91 l_je_from_sla_flag VARCHAR2(1);
92 l_appl_reference NUMBER;
93
94 CURSOR temp_cursor IS
95 SELECT rowid,
96 batch_id,
97 fund_code,
98 name,
99 posted_date,
100 gl_period,
101 amount,
102 sf1219_type_code,
103 reference_1,
104 reference_2,
105 reference_3,
106 reference_4,
107 reference_5,
108 reference_6,
109 reference_9,
110 reported_month,
111 exception_category,
112 accomplish_month,
113 accomplish_date,
114 obligation_date,
115 inter_agency_flag,
116 treasury_symbol,
117 treasury_symbol_id,
118 record_type,
119 lines_exist,
120 alc_code,
121 org_id,
122 update_type,
123 type,
124 gl_period_name,
125 processed_flag,
126 je_header_id,
127 je_line_num,
128 NVL(je_from_sla_flag,'N')
129 FROM FV_SF1219_TEMP
130 WHERE record_type not in ('P', 'N')
131 ORDER BY batch_id;
132
133 CURSOR refund_cursor IS
134 SELECT obligation_date, refund_amount
135 FROM fv_refunds_voids_all
136 WHERE cash_receipt_id = l_cash_receipt_id
137 AND type = 'AP_REFUND'
138 AND fund_value = l_fund_code
139 AND org_id = p_def_org_id;
140
141 CURSOR void_cursor IS
142 SELECT name, gl_period, amount, sf1219_type_code,
143 reference_2, reference_3,
144 reported_month, accomplish_date,
145 obligation_date, inter_agency_flag,
146 record_type, lines_exist, alc_code
147 FROM fv_sf1219_temp
148 WHERE name = 'Check for Void';
149
150 PROCEDURE purge_temp_transactions;
151 PROCEDURE get_balance_account_segments;
152 PROCEDURE get_period_info;
153 PROCEDURE insert_batches;
154 PROCEDURE process_1219_transactions;
155 PROCEDURE set_exception_category;
156 PROCEDURE insert_exceptions(x_amount IN NUMBER);
157 PROCEDURE assign_group_name;
158 PROCEDURE process_void_transactions;
159
160 /* PROCEDURE get_reference_column (p_entity_code IN VARCHAR2,
161 p_batch_id IN NUMBER,
162 p_je_header_id IN NUMBER,
163 p_je_line_num IN NUMBER,
164 p_reference OUT NOCOPY NUMBER,
165 p_appl_reference OUT NOCOPY NUMBER,
166 p_history_reference OUT NOCOPY NUMBER,
167 p_application_id IN NUMBER ); */
168
169 -----------------------------------------------------------------------------
170 -- PROCEDURE MAIN
171 -----------------------------------------------------------------------------
172 -- This procedure is called from FMS Form 1219/1220 Process, a concurrent
173 -- program. This procedure calls all the subsequent procedures in the
174 -- 1219/1220 process.
175 ----------------------------------------------------------------------------
176 PROCEDURE MAIN_1219(
177 error_msg OUT NOCOPY VARCHAR2,
178 error_code OUT NOCOPY NUMBER,
179 set_bks_id IN NUMBER,
180 gl_period IN VARCHAR2,
181 alc_code IN VARCHAR2,
182 delete_corrections IN VARCHAR2)
183 IS
184 l_module_name VARCHAR2(200) := g_module_name || 'MAIN_1219';
185 v_alc_count NUMBER;
186 BEGIN
187
188
189 p_set_bks_id := set_bks_id;
190 p_gl_period := gl_period;
191 p_alc_code := alc_code;
192 p_delete_corrections := delete_corrections;
193 p_error_code := 0;
194 p_error_msg := '** FORM 1219 PROCESS COMPLETED SUCCESSFULLY **';
195
196 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
197 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
198 'INPUT PARAMETERS: ');
199 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
200 ' SET OF BOOKS ID: '||P_SET_BKS_ID);
201 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
202 ' GL PERIOD: '||P_GL_PERIOD);
203 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
204 ' ALC CODE : '||P_ALC_CODE);
205 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
206 ' DELETE CORRECTIONS: '||P_DELETE_CORRECTIONS);
207 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
208 END IF;
209
210 -- Check whether alc_code has been defined for all records
211 -- in the fv_1219_definitions_accts table. If not, the abort process.
212 SELECT COUNT(*)
213 INTO v_alc_count
214 FROM fv_sf1219_definitions_accts
215 WHERE agency_location_code IS NULL
216 AND set_of_books_id = p_set_bks_id;
217
218 IF v_alc_count > 0
219 THEN
220 error_code := -1;
221 error_msg := 'Agency Location Code is not defined for all the '||
222 'records FMS Form 1219/1220 Report Definitions. '||
223 'Please provide Agency Location Code '||
224 'for all records and re-submit the process.';
225 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
226 l_module_name||'.error1',error_msg);
227 RETURN;
228 END IF;
229
230 SELECT currency_code
231 INTO l_currency_code
232 FROM gl_ledgers_public_v
233 WHERE ledger_id = p_set_bks_id;
234
235 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
236 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
237 ' CURRENCY CODE: '|| L_CURRENCY_CODE);
238 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
239 'PURGING TEMP TABLE ...');
240 END IF;
241 purge_temp_transactions;
242
243 IF p_error_code = 0 THEN
244 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
246 'GETTING SEGMENT NAMES ...');
247 END IF;
248 get_balance_account_segments;
249 ELSE
250 error_code := p_error_code;
251 error_msg := p_error_msg;
252 RETURN;
253 END IF;
254
255 IF p_error_code = 0 THEN
256 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
257 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
258 'GETTING PERIOD INFO ...');
259 END IF;
260 get_period_info;
261 ELSE
262 error_code := p_error_code;
263 error_msg := p_error_msg;
264 RETURN;
265 END IF;
266
267 IF p_error_code = 0 THEN
268 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
269 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
270 'INSERTING JOURNAL LINES ...');
271 END IF;
272 insert_batches;
273 ELSE
274 error_code := p_error_code;
275 error_msg := p_error_msg;
276 RETURN;
277 END IF;
278
279 IF p_error_code = 0 THEN
280 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
281 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
282 'PROCESSING 1219 TRANSACTIONS ...');
283 END IF;
284 process_1219_transactions;
285 ELSE
286 error_code := p_error_code;
287 error_msg := p_error_msg;
288 RETURN;
289 END IF;
290
291 IF p_error_code = 0 THEN
292 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
293 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
294 'PROCESSING VOID TRANSACTIONS ...');
295 END IF;
296 process_void_transactions ;
297 ELSE
298 error_code := p_error_code;
299 error_msg := p_error_msg;
300 RETURN;
301 END IF;
302
303 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
304 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
305 'ENDING MAIN_1219 ...');
306 END IF;
307 EXCEPTION
308 WHEN OTHERS THEN
309 p_error_code := 2;
310 p_error_msg := SQLERRM || ' -- Error in MAIN procedure.';
311 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
312 '.final_exception',p_error_msg);
313 IF TEMP_CURSOR%ISOPEN THEN
314 CLOSE TEMP_CURSOR;
315 END IF;
316
317 IF REFUND_CURSOR%ISOPEN THEN
318 CLOSE REFUND_CURSOR;
319 END IF;
320
321 IF VOID_CURSOR%ISOPEN THEN
322 CLOSE VOID_CURSOR;
323 END IF;
324
325
326 END MAIN_1219 ;
327
328
329 ----------------------------------------------------------------------------
330 -- PROCEDURE PURGE_TEMP_TRANSACTIONS
331 ----------------------------------------------------------------------------
332 -- If the delete_corrections parameter is 'Y' delete all records of
333 -- FV_SF1219_TEMP as well as FV_SF1219_MANUAL_LINES tables
334 -- Otherwise delete records from FV_SF1219_TEMP other than record
335 -- type 'M' and 'N'. Records types of 'M' and 'N' are not deleted as
336 -- they have been assigned report lines and should be retained.
337 ----------------------------------------------------------------------------
338 PROCEDURE PURGE_TEMP_TRANSACTIONS IS
339 l_module_name VARCHAR2(200) := g_module_name || 'PURGE_TEMP_TRANSACTIONS';
340 BEGIN
341 IF p_delete_corrections = 'Y'
342 THEN
343 DELETE FROM fv_sf1219_temp;
344 DELETE FROM fv_sf1219_manual_lines;
345 ELSE
346 DELETE FROM fv_sf1219_temp
347 WHERE record_type <> 'N';
348
349 DELETE FROM fv_sf1219_manual_lines
350 WHERE temp_record_id NOT IN
351 (SELECT temp_record_id
352 FROM fv_sf1219_temp);
353 END IF;
354 EXCEPTION
355 WHEN NO_DATA_FOUND THEN
356 NULL ;
357 WHEN OTHERS THEN
358 p_error_code := 2;
359 p_error_msg := SQLERRM || ' -- Error in MAIN procedure.';
360 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
361 '.final_exception',p_error_msg);
362 RAISE;
363
364 END PURGE_TEMP_TRANSACTIONS;
365
366
367 ----------------------------------------------------------------------------
368 -- PROCEDURE GET_BALANCE_ACCOUNT_SEGMENTS
369 ----------------------------------------------------------------------------
370 -- Get name of the Balance and Account Segment of the Accounting Flexfield
371 -- for which the Report is generated.
372 ----------------------------------------------------------------------------
373 PROCEDURE GET_BALANCE_ACCOUNT_SEGMENTS IS
374 l_module_name VARCHAR2(200) := g_module_name || 'GET_BALANCE_ACCOUNT_SEGMENTS';
375 l_error_code BOOLEAN;
376 BEGIN
377 SELECT chart_of_accounts_id
378 INTO flex_num
379 FROM gl_ledgers_public_v
380 WHERE ledger_id = p_set_bks_id ;
381
382 fv_utility.get_segment_col_names(flex_num,
383 gl_seg_name ,
384 bl_seg_name ,
385 l_error_code ,
386 p_error_msg );
387
388 IF L_ERROR_CODE then
389 p_error_code := -1;
390 RETURN;
391 END IF;
392
393
394 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
395 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
396 'ACCOUNTING SEGMENT: '||GL_SEG_NAME);
397 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
398 'BALANCING SEGMENT: '||BL_SEG_NAME);
399 END IF;
400
401 EXCEPTION
402 WHEN OTHERS THEN
403 p_error_code := sqlcode ;
404 p_error_msg := SQLERRM || ' -- Error in '||
405 'GET_BALANCE_ACCOUNT_SEGMENTS procedure.';
406 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
407 l_module_name||'.final_exception',p_error_msg);
408 ROLLBACK ;
409 RETURN ;
410 END GET_BALANCE_ACCOUNT_SEGMENTS;
411
412
413 ----------------------------------------------------------------------------
414 -- PROCEDURE GET_PERIOD_INFO
415 ----------------------------------------------------------------------------
416 -- Derive start_date and end_date date for the reporting period. Which is
417 -- used in deriving reported month and exception category.
418 ----------------------------------------------------------------------------
419 PROCEDURE GET_PERIOD_INFO IS
420 l_module_name VARCHAR2(200) := g_module_name || 'GET_PERIOD_INFO';
421 l_error_stage Number:=0;
422 BEGIN
423 l_error_stage := 1;
424
425 SELECT distinct year_start_date
426 INTO l_yr_start_date
427 FROM gl_periods glp,
428 gl_ledgers_public_v gsob
429 WHERE gsob.ledger_id = p_set_bks_id
430 AND gsob.period_set_name = glp.period_set_name
431 AND gsob.chart_of_accounts_id = flex_num
432 AND period_name = p_gl_period;
433
434 l_error_stage := 2;
435
436 SELECT distinct period_type
437 INTO period_type
438 FROM gl_period_statuses
439 WHERE application_id = '101'
440 AND ledger_id = p_set_bks_id;
441
442 l_error_stage := 3;
443
444 SELECT start_date, end_date, period_year
445 INTO l_start_date1, l_end_date1, l_period_year
446 FROM gl_periods glp,
447 gl_ledgers_public_v gsob
448 WHERE glp.period_name = p_gl_period
449 AND glp.period_type = period_type
450 AND gsob.ledger_id = p_set_bks_id
451 AND gsob.chart_of_accounts_id = flex_num
452 AND glp.period_set_name = gsob.period_set_name;
453
454 l_error_stage := 4;
455
456 -- Determine the last date of the period year
457 SELECT MAX(glp.end_date)
458 INTO l_yr_end_date
459 FROM gl_periods glp, gl_ledgers_public_v gsob
460 WHERE glp.period_year = l_period_year
461 AND gsob.ledger_id = p_set_bks_id
462 AND glp.period_set_name = gsob.period_set_name;
463
464 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
465 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
466 'YEAR START DATE: '||L_YR_START_DATE);
467 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
468 'YEAR END DATE: '||L_YR_END_DATE);
469 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
470 'PERIOD START DATE: '||L_START_DATE1);
471 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
472 'PERIOD END DATE: '||L_END_DATE1);
473 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
474 'PERIOD YEAR: '||L_PERIOD_YEAR);
475 END IF;
476 EXCEPTION
477 WHEN OTHERS THEN
478 p_error_code := 2;
479 p_error_msg := SQLERRM || ' -- Error in GET_PERIOD_INFO procedure.';
480 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
481 '.final_exception',p_error_msg);
482 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,
483 'ERROR STAGE: '||L_ERROR_STAGE);
484 RETURN;
485 END GET_PERIOD_INFO;
486
487
488 ----------------------------------------------------------------------------
489 -- PROCEDURE INSERT_BATCHES
490 ----------------------------------------------------------------------------
491 -- JE Batches are inserted into FV_SF1219_TEMP table from JE Batches/Lines.
492 -- JE Batches, which exists in Audit table, are omitted. Also, only those
493 -- accounts of JE lines are selected which have been setup by user in the
494 -- Accounts setup table.
495 -- Update_type and type are required to set 'processed flag' in
496 -- fv_interagency_funds_all and fv_refunds_voids_all tables.
497 ----------------------------------------------------------------------------
498 PROCEDURE INSERT_BATCHES IS
499 l_module_name VARCHAR2(200) := g_module_name || 'INSERT_BATCHES';
500 no_of_tran number := 0;
501 l_string varchar2(10000);
502 l_string1 varchar2(10000);
503 l_string2 varchar2(10000);
504 l_string3 varchar2(1000);
505
506 l_cur number;
507 l_row number;
508
509 BEGIN
510
511
512 l_string1 := 'INSERT INTO fv_sf1219_temp(
513 temp_record_id,
514 batch_id,
515 fund_code,
516 name,
517 set_of_books_id,
518 posted_date,
519 gl_period,
520 reported_gl_period,
521 amount,
522 sf1219_type_code,
523 reference_1,
524 reference_2,
525 reference_3,
526 reference_4,
527 reference_5,
528 reference_6,
529 reference_9,
530 reported_month,
531 default_period_name,
532 exception_category,
533 accomplish_month,
534 accomplish_date,
535 obligation_date,
536 inter_agency_flag,
537 treasury_symbol,
538 treasury_symbol_id,
539 record_type,
540 lines_exist,
541 alc_code,
542 org_id,
543 group_name,
544 update_type,
545 type,
546 gl_period_name,
547 processed_flag,
548 creation_date,
549 created_by,
550 last_update_date,
551 last_updated_by,
552 last_update_login,
553 je_header_id,
554 je_line_num,
555 je_from_sla_flag)';
556
557
558
559 l_string3 := 'AND NOT EXISTS
560 (SELECT ''X''
561 FROM fv_sf1219_audits fvs
562 WHERE fvs.batch_id = glb.je_batch_id
563 AND fvs.je_header_id = gll.je_header_id
564 AND fvs.je_line_num = gll.je_line_num
565 AND fvs.record_type <> ''B'')';
566
567 /* Start for non-sla, upgraded 11i data */
568
569 l_string2 := 'SELECT
570 fv_sf1219_temp_s.NEXTVAL,
571 glb.je_batch_id,
572 ffp.fund_value,
573 NVL(glb.name,''Manual''),
574 --glb.set_of_books_id,
575 --NULL,
576 :b_sob,
577 glb.posted_date,
578 gll.period_name,
579 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
580 NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
581 ''MANUAL'', -- default value
582 LTRIM(RTRIM(gll.reference_1)),
583 LTRIM(RTRIM(gll.reference_2)),
584 LTRIM(RTRIM(gll.reference_3)),
585 LTRIM(RTRIM(gll.reference_4)),
586 LTRIM(RTRIM(gll.reference_5)),
587 LTRIM(RTRIM(gll.reference_6)),
588 LTRIM(RTRIM(gll.reference_9)),
589 NULL, -- reported month used for exceptions
590 glb.default_period_name,
591 NULL, -- exception_category updated when exception occurred
592 NULL, -- accomplish_month derived during the process
593 :b_end_date1, -- accomplish date
594 NULL, -- obligation_date derived during the process
595 NULL, -- ia flag updated during the process
596 fts.treasury_symbol, -- no fund_value for null value(06/15)
597 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
598 ''M'', -- Default record type as Manual
599 ''N'', -- Default value for lines exist
600 fda.agency_location_code,
601 -1, --glb.org_id,
602 NULL, -- Group name assigned during the process
603 NULL, -- update type assigned during the process
604 NULL, -- type assigned during the process
605 :b_gl_period, -- gl period for which process is run
606 ''N'', -- default processed flag
607 SYSDATE,
608 fnd_global.user_id,
609 SYSDATE,
610 fnd_global.user_id,
611 fnd_global.login_id,
612 gll.je_header_id,
613 gll.je_line_num,
614 glh.je_from_sla_flag
615 FROM gl_je_batches glb,
616 gl_je_headers glh,
617 gl_je_lines gll,
618 gl_code_combinations gcc,
619 fv_sf1219_definitions_accts fda,
620 fv_fund_parameters ffp,
621 fv_treasury_symbols fts
622
623 WHERE gll.effective_date <= :b_end_date1
624 AND glh.currency_code = :b_currency_code
625 AND glb.status = ''P''
626 AND glb.actual_flag = ''A''
627 AND glb.je_batch_id = glh.je_batch_id
628 AND glh.je_header_id = gll.je_header_id
629 AND gll.code_combination_id = gcc.code_combination_id
630 --AND gll.set_of_books_id = p_set_bks_id
631 AND gll.ledger_id = :b_sob
632 AND fda.set_of_books_id = :b_sob
633 AND ffp.set_of_books_id = :b_sob
634 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
635 AND fts.set_of_books_id = :b_sob
636 AND NVL(glh.je_from_sla_flag, ''N'') IN (''N'', ''U'')
637 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
638 ''SEGMENT2'', gcc.segment2,
639 ''SEGMENT3'', gcc.segment3,
640 ''SEGMENT4'', gcc.segment4,
641 ''SEGMENT5'', gcc.segment5,
642 ''SEGMENT6'', gcc.segment6,
643 ''SEGMENT7'', gcc.segment7,
644 ''SEGMENT8'', gcc.segment8,
645 ''SEGMENT9'', gcc.segment9,
646 ''SEGMENT10'', gcc.segment10,
647 ''SEGMENT11'', gcc.segment11,
648 ''SEGMENT12'', gcc.segment12,
649 ''SEGMENT13'', gcc.segment13,
650 ''SEGMENT14'', gcc.segment14,
651 ''SEGMENT15'', gcc.segment15,
652 ''SEGMENT16'', gcc.segment16,
653 ''SEGMENT17'', gcc.segment17,
654 ''SEGMENT18'', gcc.segment18,
655 ''SEGMENT19'', gcc.segment19,
656 ''SEGMENT20'', gcc.segment20,
657 ''SEGMENT21'', gcc.segment21,
658 ''SEGMENT22'', gcc.segment22,
659 ''SEGMENT23'', gcc.segment23,
660 ''SEGMENT24'', gcc.segment24,
661 ''SEGMENT25'', gcc.segment25,
662 ''SEGMENT26'', gcc.segment26,
663 ''SEGMENT27'', gcc.segment27,
664 ''SEGMENT28'', gcc.segment28,
665 ''SEGMENT29'', gcc.segment29,
666 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
667 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
668 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
669 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
670 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
671 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
672 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
673 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
674 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
675 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
676 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
677 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
678 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
679 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
680 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
681 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
682 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
683 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
684 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
685 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
686 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
687 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
688 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
689 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
690 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
691 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
692 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
693 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
694 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
695 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
696 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
697 gcc.segment30)';
698
699 l_string := l_string1 || l_string2 || l_string3 ;
700 l_cur:= dbms_sql.open_cursor;
701 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
702
703 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
704 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
705 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
706 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
707 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
708 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
709
710 l_row := dbms_sql.EXECUTE(l_cur);
711 dbms_sql.close_cursor(l_cur);
712 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
713 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
714 l_module_name,'1. l_end_date1 : '||l_end_date1);
715 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
716 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
717 END IF;
718
719 /* END for non-sla, upgraded 11i data */
720
721 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
722 for upgraded 11i data ');
723
724 /* Start for je_source is payables and je_category is non treasury */
725
726 l_string2 := 'SELECT
727 fv_sf1219_temp_s.NEXTVAL,
728 glb.je_batch_id,
729 ffp.fund_value,
730 glb.name,
731 :b_sob,
732 glb.posted_date,
733 gll.period_name,
734 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period
735 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
736 ''MANUAL'', -- default value
737 NULL,
738 LTRIM(RTRIM(aid.invoice_id)),
739 LTRIM(RTRIM(aip.check_id)),
740 NULL,
741 NULL,
742 NULL,
743 LTRIM(RTRIM(aip.invoice_payment_id)),
744 NULL, -- reported month used for exceptions
745 glb.default_period_name,
746 NULL, -- exception_category updated when exception occurred
747 NULL, -- accomplish_month derived during the process
748 :b_end_date1, -- accomplish date
749 NULL, -- obligation_date derived during the process
750 NULL, -- ia flag updated during the process
751 fts.treasury_symbol, -- no fund_value for null value(06/15)
752 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
753 ''M'', -- Default record type as Manual
754 ''N'', -- Default value for lines exist
755 fda.agency_location_code,
756 -1, --glb.org_id,
757 NULL, -- Group name assigned during the process
758 NULL, -- update type assigned during the process
759 NULL, -- type assigned during the process
760 :b_gl_period, -- gl period for which process is run
761 ''N'', -- default processed flag
762 SYSDATE,
763 fnd_global.user_id,
764 SYSDATE,
765 fnd_global.user_id,
766 fnd_global.login_id,
767 gll.je_header_id,
768 gll.je_line_num,
769 glh.je_from_sla_flag
770 FROM gl_je_batches glb,
771 gl_je_headers glh,
772 gl_je_lines gll,
773 gl_code_combinations gcc,
774 fv_sf1219_definitions_accts fda,
775 fv_fund_parameters ffp,
776 fv_treasury_symbols fts,
777 ap_invoices_all ai,
778 ap_invoice_distributions_all aid,
779 ap_invoice_payments_all aip,
780 ap_payment_hist_dists aphd,
781 gl_import_references glir,
782 xla_ae_headers xah,
783 xla_ae_lines xal,
784 xla_events xet,
785 xla_distribution_links xdl
786
787 WHERE gll.effective_date <= :b_end_date1
788 AND glh.currency_code = :b_currency_code
789 AND glb.status = ''P''
790 AND glb.actual_flag = ''A''
791 AND glb.je_batch_id = glh.je_batch_id
792 AND glh.je_header_id = gll.je_header_id
793 AND gll.code_combination_id = gcc.code_combination_id
794 --AND gll.set_of_books_id = p_set_bks_id
795 AND gll.ledger_id = :b_sob
796 AND fda.set_of_books_id = :b_sob
797 AND ffp.set_of_books_id = :b_sob
798 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
799 AND aip.invoice_payment_id = aphd.invoice_payment_id
800 AND fts.set_of_books_id = :b_sob
801 AND glh.je_source=''Payables''
802 AND glh.je_category <> ''Treasury Confirmation''
803 AND glh.je_from_sla_flag = ''Y''
804 AND ai.invoice_id = aid.invoice_id
805 AND aip.invoice_id = ai.invoice_id
806 AND glir.je_header_id = gll.je_header_id
807 AND glir.je_line_num = gll.je_line_num
808 AND xal.gl_sl_link_id = glir.gl_sl_link_id
809 AND xal.gl_sl_link_table = glir.gl_sl_link_table
810 AND xal.ae_header_id = xah.ae_header_id
811 AND xet.event_id = xah.event_id
812 AND xdl.event_id = xet.event_id
813 AND xdl.ae_header_id = xah.ae_header_id
814 AND xdl.ae_line_num = xal.ae_line_num
815 AND xdl.source_distribution_type IN ( ''AP_PMT_DIST'')
816 AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
817 AND aphd.invoice_distribution_id = aid.invoice_distribution_id
818 AND xdl.application_id = 200
819 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
820 ''SEGMENT2'', gcc.segment2,
821 ''SEGMENT3'', gcc.segment3,
822 ''SEGMENT4'', gcc.segment4,
823 ''SEGMENT5'', gcc.segment5,
824 ''SEGMENT6'', gcc.segment6,
825 ''SEGMENT7'', gcc.segment7,
826 ''SEGMENT8'', gcc.segment8,
827 ''SEGMENT9'', gcc.segment9,
828 ''SEGMENT10'', gcc.segment10,
829 ''SEGMENT11'', gcc.segment11,
830 ''SEGMENT12'', gcc.segment12,
831 ''SEGMENT13'', gcc.segment13,
832 ''SEGMENT14'', gcc.segment14,
833 ''SEGMENT15'', gcc.segment15,
834 ''SEGMENT16'', gcc.segment16,
835 ''SEGMENT17'', gcc.segment17,
836 ''SEGMENT18'', gcc.segment18,
837 ''SEGMENT19'', gcc.segment19,
838 ''SEGMENT20'', gcc.segment20,
839 ''SEGMENT21'', gcc.segment21,
840 ''SEGMENT22'', gcc.segment22,
841 ''SEGMENT23'', gcc.segment23,
842 ''SEGMENT24'', gcc.segment24,
843 ''SEGMENT25'', gcc.segment25,
844 ''SEGMENT26'', gcc.segment26,
845 ''SEGMENT27'', gcc.segment27,
846 ''SEGMENT28'', gcc.segment28,
847 ''SEGMENT29'', gcc.segment29,
848 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
849 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
850 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
851 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
852 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
853 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
854 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
855 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
856 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
857 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
858 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
859 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
860 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
861 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
862 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
863 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
864 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
865 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
866 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
867 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
868 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
869 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
870 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
871 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
872 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
873 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
874 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
875 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
876 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
877 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
878 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
879 gcc.segment30)';
880
881 l_string := l_string1 || l_string2 || l_string3 ;
882 l_cur:= dbms_sql.open_cursor;
883 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
884
885 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
886 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
887 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
888 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
889 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
890 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
891
892 l_row := dbms_sql.EXECUTE(l_cur);
893 dbms_sql.close_cursor(l_cur);
894 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
895 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
896 l_module_name,'1. l_end_date1 : '||l_end_date1);
897 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
898 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
899 END IF;
900
901 /* END for je_source is payables and je_category is non treasury*/
902
903 /* Start for je_source is payables and je_category is non treasury */
904
905 l_string2 := 'SELECT
906 fv_sf1219_temp_s.NEXTVAL,
907 glb.je_batch_id,
908 ffp.fund_value,
909 glb.name,
910 :b_sob,
911 glb.posted_date,
912 gll.period_name,
913 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period
914 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
915 ''MANUAL'', -- default value
916 NULL,
917 LTRIM(RTRIM(aid.invoice_id)),
918 LTRIM(RTRIM(aip.check_id)),
919 NULL,
920 NULL,
921 NULL,
922 LTRIM(RTRIM(aip.invoice_payment_id)),
923 NULL, -- reported month used for exceptions
924 glb.default_period_name,
925 NULL, -- exception_category updated when exception occurred
926 NULL, -- accomplish_month derived during the process
927 :b_end_date1, -- accomplish date
928 NULL, -- obligation_date derived during the process
929 NULL, -- ia flag updated during the process
930 fts.treasury_symbol, -- no fund_value for null value(06/15)
931 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
932 ''M'', -- Default record type as Manual
933 ''N'', -- Default value for lines exist
934 fda.agency_location_code,
935 -1, --glb.org_id,
936 NULL, -- Group name assigned during the process
937 NULL, -- update type assigned during the process
938 NULL, -- type assigned during the process
939 :b_gl_period, -- gl period for which process is run
940 ''N'', -- default processed flag
941 SYSDATE,
942 fnd_global.user_id,
943 SYSDATE,
944 fnd_global.user_id,
945 fnd_global.login_id,
946 gll.je_header_id,
947 gll.je_line_num,
948 glh.je_from_sla_flag
949 FROM gl_je_batches glb,
950 gl_je_headers glh,
951 gl_je_lines gll,
952 gl_code_combinations gcc,
953 fv_sf1219_definitions_accts fda,
954 fv_fund_parameters ffp,
955 fv_treasury_symbols fts,
956 ap_invoices_all ai,
957 ap_invoice_distributions_all aid,
958 ap_invoice_payments_all aip,
959 gl_import_references glir,
960 xla_ae_headers xah,
961 xla_ae_lines xal,
962 xla_events xet,
963 xla_distribution_links xdl
964
965 WHERE gll.effective_date <= :b_end_date1
966 AND glh.currency_code = :b_currency_code
967 AND glb.status = ''P''
968 AND glb.actual_flag = ''A''
969 AND glb.je_batch_id = glh.je_batch_id
970 AND glh.je_header_id = gll.je_header_id
971 AND gll.code_combination_id = gcc.code_combination_id
972 --AND gll.set_of_books_id = p_set_bks_id
973 AND gll.ledger_id = :b_sob
974 AND fda.set_of_books_id = :b_sob
975 AND ffp.set_of_books_id = :b_sob
976 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
977 AND fts.set_of_books_id = :b_sob
978 AND glh.je_source=''Payables''
979 AND glh.je_category <> ''Treasury Confirmation''
980 AND glh.je_from_sla_flag = ''Y''
981 AND ai.invoice_id = aid.invoice_id
982 AND aip.invoice_id = ai.invoice_id
983 AND glir.je_header_id = gll.je_header_id
984 AND glir.je_line_num = gll.je_line_num
985 AND xal.gl_sl_link_id = glir.gl_sl_link_id
986 AND xal.gl_sl_link_table = glir.gl_sl_link_table
987 AND xal.ae_header_id = xah.ae_header_id
988 AND xet.event_id = xah.event_id
989 AND xdl.event_id = xet.event_id
990 AND xdl.ae_header_id = xah.ae_header_id
991 AND xdl.ae_line_num = xal.ae_line_num
992 AND xdl.source_distribution_type IN (''AP_INV_DIST'',''AP_PREPAY'')
993 AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
994 AND xdl.application_id = 200
995 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
996 ''SEGMENT2'', gcc.segment2,
997 ''SEGMENT3'', gcc.segment3,
998 ''SEGMENT4'', gcc.segment4,
999 ''SEGMENT5'', gcc.segment5,
1000 ''SEGMENT6'', gcc.segment6,
1001 ''SEGMENT7'', gcc.segment7,
1002 ''SEGMENT8'', gcc.segment8,
1003 ''SEGMENT9'', gcc.segment9,
1004 ''SEGMENT10'', gcc.segment10,
1005 ''SEGMENT11'', gcc.segment11,
1006 ''SEGMENT12'', gcc.segment12,
1007 ''SEGMENT13'', gcc.segment13,
1008 ''SEGMENT14'', gcc.segment14,
1009 ''SEGMENT15'', gcc.segment15,
1010 ''SEGMENT16'', gcc.segment16,
1011 ''SEGMENT17'', gcc.segment17,
1012 ''SEGMENT18'', gcc.segment18,
1013 ''SEGMENT19'', gcc.segment19,
1014 ''SEGMENT20'', gcc.segment20,
1015 ''SEGMENT21'', gcc.segment21,
1016 ''SEGMENT22'', gcc.segment22,
1017 ''SEGMENT23'', gcc.segment23,
1018 ''SEGMENT24'', gcc.segment24,
1019 ''SEGMENT25'', gcc.segment25,
1020 ''SEGMENT26'', gcc.segment26,
1021 ''SEGMENT27'', gcc.segment27,
1022 ''SEGMENT28'', gcc.segment28,
1023 ''SEGMENT29'', gcc.segment29,
1024 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
1025 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
1026 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
1027 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
1028 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
1029 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
1030 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
1031 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
1032 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
1033 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
1034 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
1035 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
1036 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
1037 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
1038 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
1039 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
1040 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
1041 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
1042 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
1043 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
1044 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
1045 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
1046 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
1047 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
1048 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
1049 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
1050 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
1051 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
1052 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
1053 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
1054 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
1055 gcc.segment30)';
1056
1057 l_string := l_string1 || l_string2 || l_string3 ;
1058 l_cur:= dbms_sql.open_cursor;
1059 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1060
1061 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
1062 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
1063 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
1064 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
1065 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
1066 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
1067
1068 l_row := dbms_sql.EXECUTE(l_cur);
1069 dbms_sql.close_cursor(l_cur);
1070 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1071 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1072 l_module_name,'1. l_end_date1 : '||l_end_date1);
1073 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1074 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
1075 END IF;
1076
1077 /* END for je_source is payables and je_category is non treasury*/
1078
1079 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
1080 for je_source is payables and je_category is non treasury');
1081
1082
1083 /* Start for je_source is payables and je_category is treasury confirmation */
1084
1085 l_string2 := 'SELECT
1086 fv_sf1219_temp_s.NEXTVAL,
1087 glb.je_batch_id,
1088 ffp.fund_value,
1089 glb.name,
1090 :b_sob,
1091 glb.posted_date,
1092 gll.period_name,
1093 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
1094 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) -NVL(gll.entered_cr,0),
1095 ''MANUAL'', -- default value
1096 LTRIM(RTRIM(xdl.APPLIED_TO_SOURCE_ID_NUM_1)), --treasury confirmation id
1097 NULL,
1098 LTRIM(RTRIM(AIP.check_id)), --Check_id
1099 LTRIM(RTRIM(aid.invoice_id)), --invoice_id
1100 NULL,
1101 LTRIM(RTRIM(aid.accounting_date)), --Accomplish date
1102 NULL, --invoice_payment_id
1103 NULL, -- reported month used for exceptions
1104 glb.default_period_name,
1105 NULL, -- exception_category updated when exception occurred
1106 NULL, -- accomplish_month derived during the process
1107 :b_end_date1, -- accomplish date
1108 NULL, -- obligation_date derived during the process
1109 NULL, -- ia flag updated during the process
1110 fts.treasury_symbol, -- no fund_value for null value(06/15)
1111 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
1112 ''M'', -- Default record type as Manual
1113 ''N'', -- Default value for lines exist
1114 fda.agency_location_code,
1115 -1, --glb.org_id,
1116 NULL, -- Group name assigned during the process
1117 NULL, -- update type assigned during the process
1118 NULL, -- type assigned during the process
1119 :b_gl_period, -- gl period for which process is run
1120 ''N'', -- default processed flag
1121 SYSDATE,
1122 fnd_global.user_id,
1123 SYSDATE,
1124 fnd_global.user_id,
1125 fnd_global.login_id,
1126 gll.je_header_id,
1127 gll.je_line_num,
1128 glh.je_from_sla_flag
1129 FROM gl_je_batches glb,
1130 gl_je_headers glh,
1131 gl_je_lines gll,
1132 gl_code_combinations gcc,
1133 fv_sf1219_definitions_accts fda,
1134 fv_fund_parameters ffp,
1135 fv_treasury_symbols fts,
1136 ap_invoice_distributions_all aid,
1137 ap_invoice_payments_all aip,
1138 ap_payment_hist_dists aphd,
1139 gl_import_references glir,
1140 xla_ae_headers xah,
1141 xla_ae_lines xal,
1142 xla_distribution_links xdl
1143
1144 WHERE gll.effective_date <= :b_end_date1
1145 AND glh.currency_code = :b_currency_code
1146 AND glb.status = ''P''
1147 AND glb.actual_flag = ''A''
1148 AND glb.je_batch_id = glh.je_batch_id
1149 AND glh.je_header_id = gll.je_header_id
1150 AND gll.code_combination_id = gcc.code_combination_id
1151 --AND gll.set_of_books_id = p_set_bks_id
1152 AND gll.ledger_id = :b_sob
1153 AND fda.set_of_books_id = :b_sob
1154 AND ffp.set_of_books_id = :b_sob
1155 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1156 AND fts.set_of_books_id = :b_sob
1157 AND glh.je_category = ''Treasury Confirmation''
1158 AND glh.je_from_sla_flag = ''Y''
1159 AND aip.invoice_payment_id = aphd.invoice_payment_id
1160 AND glir.je_header_id = gll.je_header_id
1161 AND glir.je_line_num = gll.je_line_num
1162 AND xal.gl_sl_link_id = glir.gl_sl_link_id
1163 AND xal.gl_sl_link_table = glir.gl_sl_link_table
1164 AND xal.ae_header_id = xah.ae_header_id
1165 AND xdl.event_id = xah.event_id
1166 AND xdl.ae_header_id = xah.ae_header_id
1167 AND xdl.ae_line_num = xal.ae_line_num
1168 AND xdl.source_distribution_type = ''FV_TREASURY_CONFIRMATIONS_ALL''
1169 AND xdl.source_distribution_id_num_1 = aphd.payment_hist_dist_id
1170 AND aid.invoice_distribution_id = aphd.invoice_distribution_id
1171 AND xdl.application_id = 8901
1172
1173 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
1174 ''SEGMENT2'', gcc.segment2,
1175 ''SEGMENT3'', gcc.segment3,
1176 ''SEGMENT4'', gcc.segment4,
1177 ''SEGMENT5'', gcc.segment5,
1178 ''SEGMENT6'', gcc.segment6,
1179 ''SEGMENT7'', gcc.segment7,
1180 ''SEGMENT8'', gcc.segment8,
1181 ''SEGMENT9'', gcc.segment9,
1182 ''SEGMENT10'', gcc.segment10,
1183 ''SEGMENT11'', gcc.segment11,
1184 ''SEGMENT12'', gcc.segment12,
1185 ''SEGMENT13'', gcc.segment13,
1186 ''SEGMENT14'', gcc.segment14,
1187 ''SEGMENT15'', gcc.segment15,
1188 ''SEGMENT16'', gcc.segment16,
1189 ''SEGMENT17'', gcc.segment17,
1190 ''SEGMENT18'', gcc.segment18,
1191 ''SEGMENT19'', gcc.segment19,
1192 ''SEGMENT20'', gcc.segment20,
1193 ''SEGMENT21'', gcc.segment21,
1194 ''SEGMENT22'', gcc.segment22,
1195 ''SEGMENT23'', gcc.segment23,
1196 ''SEGMENT24'', gcc.segment24,
1197 ''SEGMENT25'', gcc.segment25,
1198 ''SEGMENT26'', gcc.segment26,
1199 ''SEGMENT27'', gcc.segment27,
1200 ''SEGMENT28'', gcc.segment28,
1201 ''SEGMENT29'', gcc.segment29,
1202 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
1203 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
1204 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
1205 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
1206 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
1207 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
1208 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
1209 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
1210 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
1211 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
1212 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
1213 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
1214 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
1215 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
1216 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
1217 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
1218 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
1219 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
1220 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
1221 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
1222 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
1223 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
1224 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
1225 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
1226 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
1227 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
1228 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
1229 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
1230 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
1231 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
1232 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
1233 gcc.segment30)';
1234
1235 l_string := l_string1 || l_string2 || l_string3 ;
1236 l_cur:= dbms_sql.open_cursor;
1237 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1238
1239 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
1240 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
1241 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
1242 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
1243 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
1244 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
1245
1246 l_row := dbms_sql.EXECUTE(l_cur);
1247 dbms_sql.close_cursor(l_cur);
1248 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1249 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1250 l_module_name,'1. l_end_date1 : '||l_end_date1);
1251 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1252 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
1253 END IF;
1254
1255 /* END for je_source is payables and je_category is treasury confirmation */
1256 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
1257 for je_source is payables and je_category is treasury confirmation');
1258
1259 /* Start for je_source is project and je_category is labour_cost */
1260
1261
1262 l_string2 := 'SELECT
1263 fv_sf1219_temp_s.NEXTVAL,
1264 glb.je_batch_id,
1265 ffp.fund_value,
1266 glb.name,
1267 :b_sob,
1268 glb.posted_date,
1269 gll.period_name,
1270 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
1271 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
1272 ''MANUAL'', -- default value
1273 LTRIM(RTRIM(xte.SOURCE_ID_INT_1)), -- expenditure_item_id
1274 NULL,
1275 NULL,
1276 NULL,
1277 NULL,
1278 NULL,
1279 NULL,
1280 NULL, -- reported month used for exceptions
1281 glb.default_period_name,
1282 NULL, -- exception_category updated when exception occurred
1283 NULL, -- accomplish_month derived during the process
1284 :b_end_date1, -- accomplish date
1285 NULL, -- obligation_date derived during the process
1286 NULL, -- ia flag updated during the process
1287 fts.treasury_symbol, -- no fund_value for null value(06/15)
1288 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
1289 ''M'', -- Default record type as Manual
1290 ''N'', -- Default value for lines exist
1291 fda.agency_location_code,
1292 -1, --glb.org_id,
1293 NULL, -- Group name assigned during the process
1294 NULL, -- update type assigned during the process
1295 NULL, -- type assigned during the process
1296 :b_gl_period, -- gl period for which process is run
1297 ''N'', -- default processed flag
1298 SYSDATE,
1299 fnd_global.user_id,
1300 SYSDATE,
1301 fnd_global.user_id,
1302 fnd_global.login_id,
1303 gll.je_header_id,
1304 gll.je_line_num,
1305 glh.je_from_sla_flag
1306 FROM gl_je_batches glb,
1307 gl_je_headers glh,
1308 gl_je_lines gll,
1309 gl_code_combinations gcc,
1310 fv_sf1219_definitions_accts fda,
1311 fv_fund_parameters ffp,
1312 fv_treasury_symbols fts,
1313 gl_import_references glir,
1314 xla_ae_headers xah,
1315 xla_ae_lines xal,
1316 xla_events xet,
1317 xla_distribution_links xdl,
1318 xla_transaction_entities xte
1319
1320 WHERE gll.effective_date <= :b_end_date1
1321 AND glh.currency_code = :b_currency_code
1322 AND glb.status = ''P''
1323 AND glb.actual_flag = ''A''
1324 AND glb.je_batch_id = glh.je_batch_id
1325 AND glh.je_header_id = gll.je_header_id
1326 AND gll.code_combination_id = gcc.code_combination_id
1327 --AND gll.set_of_books_id = p_set_bks_id
1328 AND gll.ledger_id = :b_sob
1329 AND fda.set_of_books_id = :b_sob
1330 AND ffp.set_of_books_id = :b_sob
1331 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1332 AND fts.set_of_books_id = :b_sob
1333 AND glh.je_source=''Project Accounting''
1334 AND glh.je_category = ''Labor Cost''
1335 AND glh.je_from_sla_flag = ''Y''
1336 AND glir.je_header_id = gll.je_header_id
1337 AND glir.je_line_num = gll.je_line_num
1338 AND xal.gl_sl_link_id = glir.gl_sl_link_id
1339 AND xal.gl_sl_link_table = glir.gl_sl_link_table
1340 AND xal.ae_header_id = xah.ae_header_id
1341 AND xet.event_id = xah.event_id
1342 AND xdl.event_id = xet.event_id
1343 AND xdl.ae_header_id = xah.ae_header_id
1344 AND xdl.ae_line_num = xal.ae_line_num
1345 AND xte.entity_id = xet.entity_id
1346 AND xte.entity_code =''EXPENDITURES''
1347 AND xdl.APPLICATION_ID = 275
1348
1349 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
1350 ''SEGMENT2'', gcc.segment2,
1351 ''SEGMENT3'', gcc.segment3,
1352 ''SEGMENT4'', gcc.segment4,
1353 ''SEGMENT5'', gcc.segment5,
1354 ''SEGMENT6'', gcc.segment6,
1355 ''SEGMENT7'', gcc.segment7,
1356 ''SEGMENT8'', gcc.segment8,
1357 ''SEGMENT9'', gcc.segment9,
1358 ''SEGMENT10'', gcc.segment10,
1359 ''SEGMENT11'', gcc.segment11,
1360 ''SEGMENT12'', gcc.segment12,
1361 ''SEGMENT13'', gcc.segment13,
1362 ''SEGMENT14'', gcc.segment14,
1363 ''SEGMENT15'', gcc.segment15,
1364 ''SEGMENT16'', gcc.segment16,
1365 ''SEGMENT17'', gcc.segment17,
1366 ''SEGMENT18'', gcc.segment18,
1367 ''SEGMENT19'', gcc.segment19,
1368 ''SEGMENT20'', gcc.segment20,
1369 ''SEGMENT21'', gcc.segment21,
1370 ''SEGMENT22'', gcc.segment22,
1371 ''SEGMENT23'', gcc.segment23,
1372 ''SEGMENT24'', gcc.segment24,
1373 ''SEGMENT25'', gcc.segment25,
1374 ''SEGMENT26'', gcc.segment26,
1375 ''SEGMENT27'', gcc.segment27,
1376 ''SEGMENT28'', gcc.segment28,
1377 ''SEGMENT29'', gcc.segment29,
1378 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
1379 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
1380 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
1381 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
1382 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
1383 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
1384 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
1385 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
1386 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
1387 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
1388 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
1389 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
1390 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
1391 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
1392 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
1393 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
1394 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
1395 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
1396 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
1397 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
1398 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
1399 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
1400 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
1401 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
1402 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
1403 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
1404 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
1405 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
1406 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
1407 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
1408 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
1409 gcc.segment30)';
1410
1411 l_string := l_string1 || l_string2 || l_string3 ;
1412 l_cur:= dbms_sql.open_cursor;
1413 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1414
1415 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
1416 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
1417 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
1418 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
1419 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
1420 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
1421
1422 l_row := dbms_sql.EXECUTE(l_cur);
1423 dbms_sql.close_cursor(l_cur);
1424 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1425 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1426 l_module_name,'1. l_end_date1 : '||l_end_date1);
1427 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1428 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
1429 END IF;
1430
1431 /* END for je_source is project and je_category is labour_cost */
1432
1433 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
1434 for je_source is project and je_category is labour_cost');
1435
1436 /* Start for je_source is Receivables, based on ar_cash_receipt_history_all */
1437
1438 l_string2 := 'SELECT
1439 fv_sf1219_temp_s.NEXTVAL,
1440 glb.je_batch_id,
1441 ffp.fund_value,
1442 glb.name,
1443 :b_sob,
1444 glb.posted_date,
1445 gll.period_name,
1446 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
1447 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
1448 ''MANUAL'', -- default value
1449 NULL,
1450 LTRIM(RTRIM(arch.cash_receipt_id)),
1451 NULL,
1452 NULL,
1453 LTRIM(RTRIM(arch.CASH_RECEIPT_HISTORY_ID)),
1454 NULL,
1455 NULL,
1456 NULL, -- reported month used for exceptions
1457 glb.default_period_name,
1458 NULL, -- exception_category updated when exception occurred
1459 NULL, -- accomplish_month derived during the process
1460 :b_end_date1, -- accomplish date
1461 NULL, -- obligation_date derived during the process
1462 NULL, -- ia flag updated during the process
1463 fts.treasury_symbol, -- no fund_value for null value(06/15)
1464 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
1465 ''M'', -- Default record type as Manual
1466 ''N'', -- Default value for lines exist
1467 fda.agency_location_code,
1468 -1, --glb.org_id,
1469 NULL, -- Group name assigned during the process
1470 NULL, -- update type assigned during the process
1471 NULL, -- type assigned during the process
1472 :b_gl_period, -- gl period for which process is run
1473 ''N'', -- default processed flag
1474 SYSDATE,
1475 fnd_global.user_id,
1476 SYSDATE,
1477 fnd_global.user_id,
1478 fnd_global.login_id,
1479 gll.je_header_id,
1480 gll.je_line_num,
1481 glh.je_from_sla_flag
1482 FROM gl_je_batches glb,
1483 gl_je_headers glh,
1484 gl_je_lines gll,
1485 gl_code_combinations gcc,
1486 fv_sf1219_definitions_accts fda,
1487 fv_fund_parameters ffp,
1488 fv_treasury_symbols fts,
1489 ar_distributions_all ard,
1490 ar_cash_receipt_history_all arch,
1491 gl_import_references glir,
1492 xla_ae_headers xah,
1493 xla_ae_lines xal,
1494 xla_events xet,
1495 xla_distribution_links xdl,
1496 xla_transaction_entities xte
1497
1498 WHERE gll.effective_date <= :b_end_date1
1499 AND glh.currency_code = :b_currency_code
1500 AND glb.status = ''P''
1501 AND glb.actual_flag = ''A''
1502 AND glb.je_batch_id = glh.je_batch_id
1503 AND glh.je_header_id = gll.je_header_id
1504 AND gll.code_combination_id = gcc.code_combination_id
1505 --AND gll.set_of_books_id = p_set_bks_id
1506 AND gll.ledger_id = :b_sob
1507 AND fda.set_of_books_id = :b_sob
1508 AND ffp.set_of_books_id = :b_sob
1509 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1510 AND fts.set_of_books_id = :b_sob
1511 AND glh.je_source=''Receivables''
1512 AND glh.je_from_sla_flag = ''Y''
1513 AND glir.je_header_id = gll.je_header_id
1514 AND glir.je_line_num = gll.je_line_num
1515 AND xal.gl_sl_link_id = glir.gl_sl_link_id
1516 AND xal.gl_sl_link_table = glir.gl_sl_link_table
1517 AND xal.ae_header_id = xah.ae_header_id
1518 AND xet.event_id = xah.event_id
1519 AND xte.entity_id = xet.entity_id
1520 AND xdl.event_id = xet.event_id
1521 AND xdl.ae_header_id = xah.ae_header_id
1522 AND xdl.ae_line_num = xal.ae_line_num
1523 AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1524 AND xdl.source_distribution_id_num_1 = ard.line_id
1525 AND ard.source_table=''CRH''
1526 AND ard.source_id = arch.CASH_RECEIPT_HISTORY_ID
1527 AND xdl.APPLICATION_ID = 222
1528
1529 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
1530 ''SEGMENT2'', gcc.segment2,
1531 ''SEGMENT3'', gcc.segment3,
1532 ''SEGMENT4'', gcc.segment4,
1533 ''SEGMENT5'', gcc.segment5,
1534 ''SEGMENT6'', gcc.segment6,
1535 ''SEGMENT7'', gcc.segment7,
1536 ''SEGMENT8'', gcc.segment8,
1537 ''SEGMENT9'', gcc.segment9,
1538 ''SEGMENT10'', gcc.segment10,
1539 ''SEGMENT11'', gcc.segment11,
1540 ''SEGMENT12'', gcc.segment12,
1541 ''SEGMENT13'', gcc.segment13,
1542 ''SEGMENT14'', gcc.segment14,
1543 ''SEGMENT15'', gcc.segment15,
1544 ''SEGMENT16'', gcc.segment16,
1545 ''SEGMENT17'', gcc.segment17,
1546 ''SEGMENT18'', gcc.segment18,
1547 ''SEGMENT19'', gcc.segment19,
1548 ''SEGMENT20'', gcc.segment20,
1549 ''SEGMENT21'', gcc.segment21,
1550 ''SEGMENT22'', gcc.segment22,
1551 ''SEGMENT23'', gcc.segment23,
1552 ''SEGMENT24'', gcc.segment24,
1553 ''SEGMENT25'', gcc.segment25,
1554 ''SEGMENT26'', gcc.segment26,
1555 ''SEGMENT27'', gcc.segment27,
1556 ''SEGMENT28'', gcc.segment28,
1557 ''SEGMENT29'', gcc.segment29,
1558 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
1559 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
1560 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
1561 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
1562 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
1563 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
1564 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
1565 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
1566 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
1567 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
1568 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
1569 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
1570 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
1571 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
1572 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
1573 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
1574 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
1575 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
1576 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
1577 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
1578 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
1579 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
1580 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
1581 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
1582 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
1583 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
1584 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
1585 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
1586 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
1587 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
1588 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
1589 gcc.segment30)';
1590
1591 l_string := l_string1 || l_string2 || l_string3 ;
1592 l_cur:= dbms_sql.open_cursor;
1593 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1594
1595 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
1596 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
1597 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
1598 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
1599 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
1600 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
1601
1602 l_row := dbms_sql.EXECUTE(l_cur);
1603 dbms_sql.close_cursor(l_cur);
1604 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1605 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1606 l_module_name,'1. l_end_date1 : '||l_end_date1);
1607 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1608 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
1609 END IF;
1610
1611 /* END for je_source is Receivables, based on ar_cash_receipt_history_all*/
1612
1613 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
1614 for je_source is Receivables, based on ar_cash_receipt_history_all');
1615
1616 /* Start for je_source is Receivables, based on AR_RECEIVABLE_APPLICATIONS_ALL*/
1617
1618
1619 l_string2 := 'SELECT
1620 fv_sf1219_temp_s.NEXTVAL,
1621 glb.je_batch_id,
1622 ffp.fund_value,
1623 glb.name,
1624 :b_sob,
1625 glb.posted_date,
1626 gll.period_name,
1627 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
1628 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
1629 ''MANUAL'', -- default value
1630 NULL,
1631 LTRIM(RTRIM(arr.cash_receipt_id)),
1632 NULL,
1633 NULL,
1634 LTRIM(RTRIM(arr.receivable_application_id)),
1635 NULL,
1636 NULL,
1637 NULL, -- reported month used for exceptions
1638 glb.default_period_name,
1639 NULL, -- exception_category updated when exception occurred
1640 NULL, -- accomplish_month derived during the process
1641 :b_end_date1, -- accomplish date
1642 NULL, -- obligation_date derived during the process
1643 NULL, -- ia flag updated during the process
1644 fts.treasury_symbol, -- no fund_value for null value(06/15)
1645 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
1646 ''M'', -- Default record type as Manual
1647 ''N'', -- Default value for lines exist
1648 fda.agency_location_code,
1649 -1, --glb.org_id,
1650 NULL, -- Group name assigned during the process
1651 NULL, -- update type assigned during the process
1652 NULL, -- type assigned during the process
1653 :b_gl_period, -- gl period for which process is run
1654 ''N'', -- default processed flag
1655 SYSDATE,
1656 fnd_global.user_id,
1657 SYSDATE,
1658 fnd_global.user_id,
1659 fnd_global.login_id,
1660 gll.je_header_id,
1661 gll.je_line_num,
1662 glh.je_from_sla_flag
1663 FROM gl_je_batches glb,
1664 gl_je_headers glh,
1665 gl_je_lines gll,
1666 gl_code_combinations gcc,
1667 fv_sf1219_definitions_accts fda,
1668 fv_fund_parameters ffp,
1669 fv_treasury_symbols fts,
1670 ar_distributions_all ard,
1671 AR_RECEIVABLE_APPLICATIONS_ALL arr,
1672 gl_import_references glir,
1673 xla_ae_headers xah,
1674 xla_ae_lines xal,
1675 xla_events xet,
1676 xla_distribution_links xdl,
1677 xla_transaction_entities xte
1678
1679 WHERE gll.effective_date <= :b_end_date1
1680 AND glh.currency_code = :b_currency_code
1681 AND glb.status = ''P''
1682 AND glb.actual_flag = ''A''
1683 AND glb.je_batch_id = glh.je_batch_id
1684 AND glh.je_header_id = gll.je_header_id
1685 AND gll.code_combination_id = gcc.code_combination_id
1686 --AND gll.set_of_books_id = p_set_bks_id
1687 AND gll.ledger_id = :b_sob
1688 AND fda.set_of_books_id = :b_sob
1689 AND ffp.set_of_books_id = :b_sob
1690 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1691 AND fts.set_of_books_id = :b_sob
1692 AND glh.je_source=''Receivables''
1693 AND glh.je_from_sla_flag = ''Y''
1694 AND glir.je_header_id = gll.je_header_id
1695 AND glir.je_line_num = gll.je_line_num
1696 AND xal.gl_sl_link_id = glir.gl_sl_link_id
1697 AND xal.gl_sl_link_table = glir.gl_sl_link_table
1698 AND xal.ae_header_id = xah.ae_header_id
1699 AND xet.event_id = xah.event_id
1700 AND xte.entity_id = xet.entity_id
1701 AND xdl.event_id = xet.event_id
1702 AND xdl.ae_header_id = xah.ae_header_id
1703 AND xdl.ae_line_num = xal.ae_line_num
1704 AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1705 AND xdl.source_distribution_id_num_1 = ard.line_id
1706 AND ard.source_table=''RA''
1707 AND ard.source_id = arr.receivable_application_id
1708 AND xdl.APPLICATION_ID = 222
1709
1710 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
1711 ''SEGMENT2'', gcc.segment2,
1712 ''SEGMENT3'', gcc.segment3,
1713 ''SEGMENT4'', gcc.segment4,
1714 ''SEGMENT5'', gcc.segment5,
1715 ''SEGMENT6'', gcc.segment6,
1716 ''SEGMENT7'', gcc.segment7,
1717 ''SEGMENT8'', gcc.segment8,
1718 ''SEGMENT9'', gcc.segment9,
1719 ''SEGMENT10'', gcc.segment10,
1720 ''SEGMENT11'', gcc.segment11,
1721 ''SEGMENT12'', gcc.segment12,
1722 ''SEGMENT13'', gcc.segment13,
1723 ''SEGMENT14'', gcc.segment14,
1724 ''SEGMENT15'', gcc.segment15,
1725 ''SEGMENT16'', gcc.segment16,
1726 ''SEGMENT17'', gcc.segment17,
1727 ''SEGMENT18'', gcc.segment18,
1728 ''SEGMENT19'', gcc.segment19,
1729 ''SEGMENT20'', gcc.segment20,
1730 ''SEGMENT21'', gcc.segment21,
1731 ''SEGMENT22'', gcc.segment22,
1732 ''SEGMENT23'', gcc.segment23,
1733 ''SEGMENT24'', gcc.segment24,
1734 ''SEGMENT25'', gcc.segment25,
1735 ''SEGMENT26'', gcc.segment26,
1736 ''SEGMENT27'', gcc.segment27,
1737 ''SEGMENT28'', gcc.segment28,
1738 ''SEGMENT29'', gcc.segment29,
1739 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
1740 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
1741 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
1742 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
1743 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
1744 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
1745 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
1746 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
1747 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
1748 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
1749 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
1750 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
1751 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
1752 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
1753 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
1754 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
1755 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
1756 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
1757 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
1758 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
1759 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
1760 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
1761 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
1762 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
1763 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
1764 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
1765 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
1766 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
1767 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
1768 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
1769 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
1770 gcc.segment30)';
1771
1772 l_string := l_string1 || l_string2 || l_string3 ;
1773 l_cur:= dbms_sql.open_cursor;
1774 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1775
1776 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
1777 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
1778 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
1779 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
1780 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
1781 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
1782
1783 l_row := dbms_sql.EXECUTE(l_cur);
1784 dbms_sql.close_cursor(l_cur);
1785 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1786 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1787 l_module_name,'1. l_end_date1 : '||l_end_date1);
1788 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1789 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
1790 END IF;
1791
1792 /* END for je_source is Receivables, based on AR_RECEIVABLE_APPLICATIONS_ALL */
1793
1794 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
1795 for je_source is Receivables , based on AR_RECEIVABLE_APPLICATIONS_ALL ');
1796
1797 /* Start for je_source is Receivables, based on AR_MISC_CASH_DISTRIBUTIONS_ALL */
1798
1799 l_string2 := 'SELECT
1800 fv_sf1219_temp_s.NEXTVAL,
1801 glb.je_batch_id,
1802 ffp.fund_value,
1803 glb.name,
1804 :b_sob,
1805 glb.posted_date,
1806 gll.period_name,
1807 TO_CHAR(:b_start_date1,''MMYYYY''), -- reported_gl_period updated
1808 NVL(xal.unrounded_accounted_dr,0) - NVL(xal.unrounded_accounted_cr,0), --NVL(gll.entered_dr,0) - NVL(gll.entered_cr,0),
1809 ''MANUAL'', -- default value
1810 NULL,
1811 LTRIM(RTRIM(arm.cash_receipt_id)),
1812 NULL,
1813 NULL,
1814 LTRIM(RTRIM(arm.MISC_CASH_DISTRIBUTION_ID)),--cash_receipt_hist_id
1815 NULL,
1816 NULL,
1817 NULL, -- reported month used for exceptions
1818 glb.default_period_name,
1819 NULL, -- exception_category updated when exception occurred
1820 NULL, -- accomplish_month derived during the process
1821 :b_end_date1, -- accomplish date
1822 NULL, -- obligation_date derived during the process
1823 NULL, -- ia flag updated during the process
1824 fts.treasury_symbol, -- no fund_value for null value(06/15)
1825 ffp.treasury_symbol_id, -- Added to fix Bug 1575992
1826 ''M'', -- Default record type as Manual
1827 ''N'', -- Default value for lines exist
1828 fda.agency_location_code,
1829 -1, --glb.org_id,
1830 NULL, -- Group name assigned during the process
1831 NULL, -- update type assigned during the process
1832 NULL, -- type assigned during the process
1833 :b_gl_period, -- gl period for which process is run
1834 ''N'', -- default processed flag
1835 SYSDATE,
1836 fnd_global.user_id,
1837 SYSDATE,
1838 fnd_global.user_id,
1839 fnd_global.login_id,
1840 gll.je_header_id,
1841 gll.je_line_num,
1842 glh.je_from_sla_flag
1843 FROM gl_je_batches glb,
1844 gl_je_headers glh,
1845 gl_je_lines gll,
1846 gl_code_combinations gcc,
1847 fv_sf1219_definitions_accts fda,
1848 fv_fund_parameters ffp,
1849 fv_treasury_symbols fts,
1850 ar_distributions_all ard,
1851 AR_MISC_CASH_DISTRIBUTIONS_ALL arm,
1852 gl_import_references glir,
1853 xla_ae_headers xah,
1854 xla_ae_lines xal,
1855 xla_events xet,
1856 xla_distribution_links xdl,
1857 xla_transaction_entities xte
1858
1859 WHERE gll.effective_date <= :b_end_date1
1860 AND glh.currency_code = :b_currency_code
1861 AND glb.status = ''P''
1862 AND glb.actual_flag = ''A''
1863 AND glb.je_batch_id = glh.je_batch_id
1864 AND glh.je_header_id = gll.je_header_id
1865 AND gll.code_combination_id = gcc.code_combination_id
1866 --AND gll.set_of_books_id = p_set_bks_id
1867 AND gll.ledger_id = :b_sob
1868 AND fda.set_of_books_id = :b_sob
1869 AND ffp.set_of_books_id = :b_sob
1870 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1871 AND fts.set_of_books_id = :b_sob
1872 AND glh.je_source=''Receivables''
1873 AND glh.je_from_sla_flag = ''Y''
1874 AND glir.je_header_id = gll.je_header_id
1875 AND glir.je_line_num = gll.je_line_num
1876 AND xal.gl_sl_link_id = glir.gl_sl_link_id
1877 AND xal.gl_sl_link_table = glir.gl_sl_link_table
1878 AND xal.ae_header_id = xah.ae_header_id
1879 AND xet.event_id = xah.event_id
1880 AND xte.entity_id = xet.entity_id
1881 AND xdl.event_id = xet.event_id
1882 AND xdl.ae_header_id = xah.ae_header_id
1883 AND xdl.ae_line_num = xal.ae_line_num
1884 AND xdl.source_distribution_type = ''AR_DISTRIBUTIONS_ALL''
1885 AND xdl.source_distribution_id_num_1 = ard.line_id
1886 AND ard.source_id = arm.MISC_CASH_DISTRIBUTION_ID
1887 AND ard.source_table=''MCD''
1888 AND xdl.APPLICATION_ID = 222
1889
1890 AND decode(:b_bl_seg_name,''SEGMENT1'', gcc.segment1,
1891 ''SEGMENT2'', gcc.segment2,
1892 ''SEGMENT3'', gcc.segment3,
1893 ''SEGMENT4'', gcc.segment4,
1894 ''SEGMENT5'', gcc.segment5,
1895 ''SEGMENT6'', gcc.segment6,
1896 ''SEGMENT7'', gcc.segment7,
1897 ''SEGMENT8'', gcc.segment8,
1898 ''SEGMENT9'', gcc.segment9,
1899 ''SEGMENT10'', gcc.segment10,
1900 ''SEGMENT11'', gcc.segment11,
1901 ''SEGMENT12'', gcc.segment12,
1902 ''SEGMENT13'', gcc.segment13,
1903 ''SEGMENT14'', gcc.segment14,
1904 ''SEGMENT15'', gcc.segment15,
1905 ''SEGMENT16'', gcc.segment16,
1906 ''SEGMENT17'', gcc.segment17,
1907 ''SEGMENT18'', gcc.segment18,
1908 ''SEGMENT19'', gcc.segment19,
1909 ''SEGMENT20'', gcc.segment20,
1910 ''SEGMENT21'', gcc.segment21,
1911 ''SEGMENT22'', gcc.segment22,
1912 ''SEGMENT23'', gcc.segment23,
1913 ''SEGMENT24'', gcc.segment24,
1914 ''SEGMENT25'', gcc.segment25,
1915 ''SEGMENT26'', gcc.segment26,
1916 ''SEGMENT27'', gcc.segment27,
1917 ''SEGMENT28'', gcc.segment28,
1918 ''SEGMENT29'', gcc.segment29,
1919 ''SEGMENT30'', gcc.segment30) = ffp.fund_value
1920 and nvl(fda.segment1,''-1'') = decode(fda.segment1,null, ''-1'', gcc.segment1)
1921 and nvl(fda.segment2,''-1'') = decode(fda.segment2,null, ''-1'', gcc.segment2)
1922 and nvl(fda.segment3,''-1'') = decode(fda.segment3,null, ''-1'', gcc.segment3)
1923 and nvl(fda.segment4,''-1'') = decode(fda.segment4,null, ''-1'', gcc.segment4)
1924 and nvl(fda.segment5,''-1'') = decode(fda.segment5,null, ''-1'', gcc.segment5)
1925 and nvl(fda.segment6,''-1'') = decode(fda.segment6,null, ''-1'', gcc.segment6)
1926 and nvl(fda.segment7,''-1'') = decode(fda.segment7,null, ''-1'', gcc.segment7)
1927 and nvl(fda.segment8,''-1'') = decode(fda.segment8,null, ''-1'', gcc.segment8)
1928 and nvl(fda.segment9,''-1'') = decode(fda.segment9,null, ''-1'', gcc.segment9)
1929 and nvl(fda.segment10,''-1'') = decode(fda.segment10,null,''-1'',gcc.segment10)
1930 and nvl(fda.segment11,''-1'') = decode(fda.segment11,null,''-1'',gcc.segment11)
1931 and nvl(fda.segment12,''-1'') = decode(fda.segment12,null,''-1'',gcc.segment12)
1932 and nvl(fda.segment13,''-1'') = decode(fda.segment13,null,''-1'',gcc.segment13)
1933 and nvl(fda.segment14,''-1'') = decode(fda.segment14,null,''-1'',gcc.segment14)
1934 and nvl(fda.segment15,''-1'') = decode(fda.segment15,null,''-1'',gcc.segment15)
1935 and nvl(fda.segment16,''-1'') = decode(fda.segment16,null,''-1'',gcc.segment16)
1936 and nvl(fda.segment17,''-1'') = decode(fda.segment17,null,''-1'',gcc.segment17)
1937 and nvl(fda.segment18,''-1'') = decode(fda.segment18,null,''-1'',gcc.segment18)
1938 and nvl(fda.segment19,''-1'') = decode(fda.segment19,null,''-1'',gcc.segment19)
1939 and nvl(fda.segment20,''-1'') = decode(fda.segment20,null,''-1'',gcc.segment20)
1940 and nvl(fda.segment21,''-1'') = decode(fda.segment21,null,''-1'',gcc.segment21)
1941 and nvl(fda.segment22,''-1'') = decode(fda.segment22,null,''-1'',gcc.segment22)
1942 and nvl(fda.segment23,''-1'') = decode(fda.segment23,null,''-1'',gcc.segment23)
1943 and nvl(fda.segment24,''-1'') = decode(fda.segment24,null,''-1'',gcc.segment24)
1944 and nvl(fda.segment25,''-1'') = decode(fda.segment25,null,''-1'',gcc.segment25)
1945 and nvl(fda.segment26,''-1'') = decode(fda.segment26,null,''-1'',gcc.segment26)
1946 and nvl(fda.segment27,''-1'') = decode(fda.segment27,null,''-1'',gcc.segment27)
1947 and nvl(fda.segment28,''-1'') = decode(fda.segment28,null,''-1'',gcc.segment28)
1948 and nvl(fda.segment29,''-1'') = decode(fda.segment29,null,''-1'',gcc.segment29)
1949 and nvl(fda.segment30,''-1'') = decode(fda.segment30,null,''-1'',
1950 gcc.segment30)';
1951
1952 l_string := l_string1 || l_string2 || l_string3 ;
1953 l_cur:= dbms_sql.open_cursor;
1954 dbms_sql.parse(l_cur, l_string, DBMS_SQL.V7);
1955
1956 dbms_sql.bind_variable(l_cur,':b_sob',p_set_bks_id);
1957 dbms_sql.bind_variable(l_cur,':b_start_date1',l_start_date1);
1958 dbms_sql.bind_variable(l_cur,':b_end_date1',l_end_date1);
1959 dbms_sql.bind_variable(l_cur,':b_gl_period',p_gl_period);
1960 dbms_sql.bind_variable(l_cur,':b_currency_code',l_currency_code);
1961 dbms_sql.bind_variable(l_cur,':b_bl_seg_name',bl_seg_name);
1962
1963 l_row := dbms_sql.EXECUTE(l_cur);
1964 dbms_sql.close_cursor(l_cur);
1965 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1966 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1967 l_module_name,'1. l_end_date1 : '||l_end_date1);
1968 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1969 l_module_name,'1. NUMBER OF ROWS INSERTED: '||l_row);
1970 END IF;
1971
1972 /* END for je_source is Receivables, based on AR_MISC_CASH_DISTRIBUTIONS_ALL */
1973
1974 fv_utility.debug_mesg(fnd_log.level_statement, l_module_name,'End of Insert
1975 for je_source is Receivables , based on AR_MISC_CASH_DISTRIBUTIONS_ALL ');
1976
1977
1978 no_of_tran := 0;
1979
1980 -- Get the count of the number of records in the temp table.
1981 SELECT count(*)
1982 INTO no_of_tran
1983 FROM fv_sf1219_temp
1984 WHERE set_of_books_id = p_set_bks_id ;
1985
1986 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1987 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1988 '2. l_end_date1 : '||l_end_date1);
1989 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1990 '2. NUMBER OF ROWS INSERTED: '||no_of_tran);
1991 END IF;
1992
1993 EXCEPTION
1994 WHEN OTHERS THEN
1995 p_error_code := 2;
1996 p_error_msg := SQLERRM || ' -- Error in INSERT_BATCHES procedure.';
1997 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
1998 l_module_name||'.final_exception',p_error_msg);
1999 END INSERT_BATCHES;
2000
2001
2002 -----------------------------------------------------------------------------
2003 -- PROCEDURE PROCESS_1219_TRANSACTIONS
2004 -----------------------------------------------------------------------------
2005 -- Each record of temp table is processed to find other relevant information
2006 -- like accomplished date, date of obligation of the transaction required for
2007 -- 1219/1220 reports. SF1219 Record type is arrived for each record. At the
2008 -- end of this procedure this information is updated in fv_sf1219_temp table.
2009 --
2010 -- Different record types used during the process are :
2011 -- A - Auto, records which are assigned with a record type
2012 -- M - Manual, records which could not be assigned to a record type
2013 -- N - New, records entered using Transactions Correction Form with group /
2014 -- line assignments
2015 -- E - Exception, records inserted during the process because of exceptions
2016 -- O - Omitted, records caused the exception and hence not to be reported
2017 -- R - Records with ALC_code as null.
2018 -- P - Contains Reported GL Period and Legal Entity information.
2019 -- Indicates that the pre-process has been run before TCF or 1219 Report.
2020 -----------------------------------------------------------------------------
2021 PROCEDURE PROCESS_1219_TRANSACTIONS IS
2022 l_module_name VARCHAR2(200);
2023 l_reference NUMBER;
2024 l_org_amount NUMBER;
2025 v_je_source gl_je_headers.je_source%TYPE;
2026 v_je_category gl_je_headers.je_category%TYPE;
2027 l_rev_cash_recpt_id gl_je_lines.reference_2%TYPE;
2028 l_exists VARCHAR2(1);
2029 vl_misc_cd_flag VARCHAR2(1) := 'N';
2030 p_def_p_ia_paygroup VARCHAR2(30);
2031 l_inv_pay_id NUMBER(15) := 0;
2032 l_void_incomplete VARCHAR2(1) := 'N';
2033 BEGIN
2034
2035 -- Whenever pre-process is run, one record with reported_gl_period will be
2036 -- inserted, irrespective of whether pre-process produces any record or not.
2037 -- This record will enable to produce appropriate message/output to indicate
2038 -- whether pre-process has been run, in Transaction Correction Form and 1219
2039 -- Report. For convenience, org_id column will be populated with
2040 -- Legal Entity, for this record ONLY.
2041 l_module_name := g_module_name || 'PROCESS_1219_TRANSACTIONS';
2042 INSERT INTO fv_sf1219_temp (
2043 temp_record_id,
2044 batch_id,
2045 fund_code,
2046 name,
2047 set_of_books_id,
2048 gl_period,
2049 reported_gl_period,
2050 reported_month,
2051 record_type,
2052 lines_exist,
2053 alc_code,
2054 creation_date,
2055 created_by,
2056 last_update_date,
2057 last_updated_by,
2058 last_update_login)
2059 VALUES (
2060 fv_sf1219_temp_s.NEXTVAL,
2061 0,
2062 'NO FUND',
2063 'P Batch',
2064 p_set_bks_id,
2065 p_gl_period,
2066 TO_CHAR(l_start_date1,'MMYYYY'),
2067 TO_CHAR(l_start_date1,'MMYYYY'),
2068 'P',
2069 'N',
2070 p_alc_code,
2071 SYSDATE,
2072 FND_GLOBAL.USER_ID,
2073 SYSDATE,
2074 FND_GLOBAL.USER_ID,
2075 FND_GLOBAL.LOGIN_ID);
2076
2077 COMMIT;
2078
2079 -----------------------------------------------------------------------
2080 -- PROCESSING BEGINS HERE WITH POPULATING THE Main Cursor TEMP_CURSOR
2081 -----------------------------------------------------------------------
2082 SELECT count(*)
2083 INTO transaction_count
2084 FROM fv_sf1219_temp
2085 WHERE record_type NOT IN ('P', 'N');
2086
2087 IF transaction_count = 0
2088 THEN
2089 p_error_code := 0;
2090 p_error_msg := 'No transaction activity for this period';
2091 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2092 l_module_name||'.error1',p_error_msg);
2093 RETURN;
2094 ELSE
2095 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2096 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2097 'FOUND '||TRANSACTION_COUNT||' RECORD(S).');
2098 END IF;
2099 END IF;
2100
2101 -- Determine the default paygroup based on the org_id
2102 BEGIN
2103 SELECT payables_ia_paygroup
2104 INTO p_def_p_ia_paygroup
2105 FROM FV_Operating_units_all
2106 WHERE org_id = p_def_org_id;
2107 EXCEPTION
2108 WHEN No_Data_Found THEN
2109 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||
2110 '.error2','PAYABLES IA PAYGROUP NOT FOUND, BASED ON THE ORG_ID '
2111 ||TO_CHAR(P_DEF_ORG_ID));
2112
2113 WHEN OTHERS THEN
2114 p_error_code := 2;
2115 p_error_msg := SQLERRM || '-- Error in '||
2116 'Process_1219_Transactions procedure '||
2117 'while determining the payables ia paygroup.';
2118 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2119 l_module_name||'.error3',p_error_msg);
2120 END ;
2121
2122 OPEN TEMP_CURSOR;
2123 IF (sqlcode < 0) THEN
2124 p_error_code := sqlcode;
2125 p_error_msg := sqlerrm;
2126 END IF;
2127
2128 LOOP
2129 FETCH TEMP_CURSOR INTO
2130 l_rowid,
2131 l_batch_id,
2132 l_fund_code,
2133 l_name,
2134 l_posted_date,
2135 l_gl_period,
2136 l_amount,
2137 l_sf1219_type_code,
2138 l_reference_1,
2139 l_reference_2,
2140 l_reference_3,
2141 l_reference_4,
2142 l_reference_5,
2143 l_reference_6,
2144 l_reference_9,
2145 l_reported_month,
2146 l_exception_category,
2147 l_accomplish_month,
2148 l_accomplish_date,
2149 l_obligation_date,
2150 l_inter_agency_flag,
2151 l_treasury_symbol,
2152 l_treasury_symbol_id,
2153 l_record_type,
2154 l_lines_exist,
2155 l_alc_code,
2156 l_org_id,
2157 l_update_type,
2158 l_type,
2159 l_gl_period_name,
2160 l_processed_flag,
2161 l_je_header_id,
2162 l_je_line_num,
2163 l_je_from_sla_flag ;
2164
2165 IF (TEMP_CURSOR%NOTFOUND)
2166 THEN
2167 EXIT;
2168 END IF;
2169
2170 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2171 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2172 l_module_name,'');
2173 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2174 l_module_name,'-------------------------------------------');
2175 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2176 l_module_name,'L_BATCH_ID: '||L_BATCH_ID);
2177 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2178 l_module_name,'L_FUND_CODE: '||L_FUND_CODE);
2179 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2180 l_module_name,'L_NAME: '||L_NAME);
2181 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2182 l_module_name,'L_POSTED_DATE: '||L_POSTED_DATE);
2183 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2184 l_module_name,'L_GL_PERIOD: '||L_GL_PERIOD);
2185 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2186 l_module_name,'L_AMOUNT: '||L_AMOUNT);
2187 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2188 l_module_name,'L_SF1219_TYPE_CODE: '||L_SF1219_TYPE_CODE);
2189 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2190 l_module_name,'L_REFERENCE_1: '||L_REFERENCE_1);
2191 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2192 l_module_name,'L_REFERENCE_2: '||L_REFERENCE_2);
2193 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2194 l_module_name,'L_REFERENCE_3: '||L_REFERENCE_3);
2195 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2196 l_module_name,'L_REFERENCE_4: '||L_REFERENCE_4);
2197 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2198 l_module_name,'L_REFERENCE_5: '||L_REFERENCE_5);
2199 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2200 l_module_name,'L_REFERENCE_6: '||L_REFERENCE_6);
2201 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2202 l_module_name,'L_REFERENCE_9: '||L_REFERENCE_9);
2203 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2204 l_module_name,'L_REPORTED_MONTH: '||L_REPORTED_MONTH);
2205 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2206 l_module_name,'L_EXCEPTION_CATEGORY: '||L_EXCEPTION_CATEGORY);
2207 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2208 l_module_name,'L_ACCOMPLISH_MONTH: '||L_ACCOMPLISH_MONTH);
2209 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2210 l_module_name,'L_ACCOMPLISH_DATE: '||L_ACCOMPLISH_DATE);
2211 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2212 l_module_name,'L_OBLIGATION_DATE: '||L_OBLIGATION_DATE);
2213 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2214 l_module_name,'L_INTER_AGENCY_FLAG: '||L_INTER_AGENCY_FLAG);
2215 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2216 l_module_name,'L_TREASURY_SYMBOL_ID: '||L_TREASURY_SYMBOL_ID);
2217 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2218 l_module_name,'L_RECORD_TYPE: '||L_RECORD_TYPE);
2219 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2220 l_module_name,'L_LINES_EXIST: '||L_LINES_EXIST);
2221 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2222 l_module_name,'L_ALC_CODE: '||L_ALC_CODE);
2223 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2224 l_module_name,'L_ORG_ID: '||L_ORG_ID);
2225 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2226 l_module_name,'L_UPDATE_TYPE: '||L_UPDATE_TYPE);
2227 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2228 l_module_name,'L_TYPE: '||L_TYPE);
2229 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2230 l_module_name,'L_GL_PERIOD_NAME: '||L_GL_PERIOD_NAME);
2231 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2232 l_module_name,'L_PROCESSED_FLAG: '||L_PROCESSED_FLAG);
2233 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2234 l_module_name,'L_JE_HEADER_ID: '||L_JE_HEADER_ID);
2235 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2236 l_module_name,'L_JE_LINE_NUM: '||L_JE_LINE_NUM);
2237 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2238 l_module_name,'L_JE_FROM_SLA_FLAG: '||L_JE_FROM_SLA_FLAG);
2239 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
2240 END IF;
2241
2242 -----------------------------------------------------------------------
2243 -- Initializing Variables
2244 -----------------------------------------------------------------------
2245 l_name_keep := l_name;
2246 l_name := upper(l_name);
2247 l_processed_flag := 'N';
2248 l_org_amount := l_amount;
2249 l_inter_agency_flag := 'N';
2250 x_amount := abs(l_amount);
2251 l_exception_category := null;
2252 l_billing_agency_fund := null;
2253 l_reported_gl_period := to_char(l_start_date1,'MMYYYY');
2254 l_accomplish_date := l_end_date1;
2255 l_type := null;
2256 l_update_type := null;
2257
2258 -- Get journal source and category
2259 BEGIN
2260 SELECT je_source, je_category
2261 INTO v_je_source, v_je_category
2262 FROM gl_je_headers
2263 WHERE je_header_id = l_je_header_id;
2264 EXCEPTION
2265 WHEN OTHERS THEN
2266 p_error_code := -1;
2267 p_error_msg := SUBSTR(sqlerrm,1,50||
2268 ': while fetching journal source');
2269 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
2270 l_module_name||'.error10',p_error_msg);
2271 RETURN;
2272 END;
2273
2274 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2275 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2276 l_module_name,'SOURCE: '||V_JE_SOURCE);
2277 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2278 l_module_name,'CATEGORY: '||V_JE_CATEGORY);
2279 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
2280 END IF;
2281
2282 -----------------------------------------------------------------------------------
2283 -- Determine if the reference values for each row are not null and valid. Otherwise
2284 -- assign a value of MANUAL to l_name.
2285 -----------------------------------------------------------------------------------
2286 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2287 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2288 l_module_name,'-> CHECK FOR MANUAL ...');
2289 END IF;
2290
2291 -- Accomplish date is obtained for records with batch name like TREASURY
2292
2293 -- Reference Validity Check
2294
2295
2296 -- Adi
2297
2298
2299 -- Check to see if the journal is from SLA
2300 --
2301 IF l_je_from_sla_flag IN ('N', 'U') THEN
2302 IF (v_je_source = 'Budgetary Transaction' AND
2303 v_je_category = 'Treasury Confirmation') -- Budgetary Transaction, = TC
2304 THEN
2305 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2306 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2307 ' PROCESSING SOURCE = Budgetary Transaction, CATEGORY = TREASURY CONFIRMATION');
2308 END IF;
2309
2310 IF l_reference_1 IS NULL AND l_reference_6 IS NULL -- ref1
2311 THEN
2312 l_name := 'MANUAL';
2313 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2314 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2315 l_module_name,' REF_1 AND REF_6 ARE BOTH NULL, HENCE MANUAL');
2316 END IF;
2317 ELSIF l_reference_1 IS NOT NULL THEN
2318 BEGIN
2319 SELECT 'Y'
2320 INTO l_exists
2321 FROM Fv_treasury_confirmations_all
2322 WHERE treasury_confirmation_id = to_number(l_reference_1)
2323 AND org_id = p_def_org_id;
2324
2325 EXCEPTION
2326 WHEN NO_DATA_FOUND THEN
2327 l_name := 'MANUAL';
2328 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2329 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2330 l_module_name,
2331 ' NO_DATA_FOUND WHEN SELECTING FROM '||
2332 'FV_TREASURY_CONFIRMATIONS_ALL WITH REF_1, HENCE MANUAL');
2333 END IF;
2334
2335 When INVALID_NUMBER OR VALUE_ERROR THEN
2336 l_name := 'MANUAL';
2337 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2338 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2339 ' INVALID NUMBER ERROR, HENCE MANUAL');
2340 END IF;
2341 END ;
2342 ELSE
2343 IF l_reference_3 IS NULL -- ref3
2344 THEN
2345 l_name := 'MANUAL';
2346 ELSE
2347 BEGIN
2348 SELECT 'Y'
2349 INTO l_exists
2350 FROM ap_checks_all
2351 WHERE check_id = to_number(l_REFERENCE_3)
2352 AND org_id = p_def_org_id;
2353
2354 EXCEPTION
2355 WHEN NO_DATA_FOUND THEN
2356 l_name := 'MANUAL';
2357 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2358 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2359 ' NO_DATA_FOUND WHEN SELECTING '||
2360 'FROM AP_CHECKS_ALL WITH REF_3, HENCE MANUAL');
2361 END IF;
2362
2363 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2364 l_name := 'MANUAL';
2365 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2366 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2367 l_module_name,
2368 ' INVALID NUMBER ERROR, HENCE MANUAL');
2369 END IF;
2370 END;
2371
2372 IF (l_reference_1 IS NULL) AND (l_reference_6 IS NOT NULL) -- ref1,ref6
2373 THEN
2374 BEGIN
2375 -- If ref_1 is NULL and ref_3 is not NULL,
2376 -- accomplish_date value comes from ref_6.
2377 -- The following check will ensure that ref_6
2378 -- does not get an invalid value and causes
2379 -- 1219/1220 process to error.
2380
2381 l_ref6_date_check := l_reference_6;
2382 EXCEPTION
2383 WHEN OTHERS THEN
2384 l_name := 'MANUAL';
2385 IF (FND_LOG.LEVEL_STATEMENT >=
2386 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2387 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2388 l_module_name,
2389 ' INVALID VALUE '||
2390 ' FOR REF_6, ' ||
2391 ' HENCE MANUAL');
2392 END IF;
2393 END;
2394 END IF; -- ref1, ref6
2395 END IF; -- ref3
2396 END IF; -- ref1
2397
2398 ELSIF (v_je_source = 'Payables'
2399 AND v_je_category <> 'Treasury Confirmation') -- Payables, <> TC
2400 THEN
2401 -- Check if ref_2/3/9 is NULL. If not, does it have a valid value.
2402 -- Else l_name is Manual.
2403 IF (FND_LOG.LEVEL_STATEMENT
2404 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2405 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2406 l_module_name,
2407 ' PROCESSING SOURCE = PAYABLES, '||
2408 ' CATEGORY <> TREASURY CONFIRMATION');
2409 END IF;
2410
2411 IF (l_reference_2 IS NULL) -- ref2
2412 THEN
2413 l_name := 'MANUAL';
2414 IF (FND_LOG.LEVEL_STATEMENT >=
2415 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2416 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2417 l_module_name,' REF_2 IS NULL');
2418 END IF;
2419 ELSE
2420 BEGIN
2421 SELECT 'Y'
2422 INTO l_exists
2423 FROM ap_invoices_all
2424 WHERE invoice_id = TO_NUMBER(l_reference_2)
2425 AND org_id = p_def_org_id;
2426
2427 EXCEPTION
2428 WHEN NO_DATA_FOUND THEN
2429 l_name := 'MANUAL';
2430 IF (FND_LOG.LEVEL_STATEMENT >=
2431 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2432 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2433 l_module_name,
2434 ' NO_DATA_FOUND WHEN SELECTING' ||
2435 ' FROM AP_INVOICES_ALL WITH REF_2, '||
2436 ' HENCE MANUAL');
2437 END IF;
2438
2439 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2440 l_name := 'MANUAL';
2441 IF (FND_LOG.LEVEL_STATEMENT >=
2442 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2443 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2444 l_module_name,
2445 ' INVALID NUMBER ERROR, '||
2446 ' HENCE MANUAL');
2447 END IF;
2448 END;
2449 END IF; -- ref2
2450
2451 IF (l_reference_3 IS NULL) -- ref3
2452 THEN
2453 l_name := 'MANUAL';
2454 IF (FND_LOG.LEVEL_STATEMENT >=
2455 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2456 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2457 l_module_name,' REF_3 IS NULL');
2458 END IF;
2459 ELSE
2460 BEGIN
2461 SELECT 'Y'
2462 INTO l_exists
2463 FROM ap_checks_all
2464 WHERE check_id = to_number(l_REFERENCE_3)
2465 AND org_id = p_def_org_id;
2466
2467 EXCEPTION
2468 WHEN NO_DATA_FOUND THEN
2469 l_name := 'MANUAL';
2470 IF (FND_LOG.LEVEL_STATEMENT >=
2471 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2472 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2473 l_module_name,
2474 ' NO_DATA_FOUND WHEN '||
2475 ' SELECTING FROM AP_CHECKS_ALL '||
2476 ' WITH REF_3, HENCE MANUAL');
2477 END IF;
2478
2479 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2480 l_name := 'MANUAL';
2481 IF (FND_LOG.LEVEL_STATEMENT >=
2482 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2483 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2484 l_module_name,' INVALID '||
2485 'NUMBER ERROR, HENCE MANUAL');
2486 END IF;
2487 END;
2488 END IF; -- ref3
2489
2490 IF (l_reference_9 IS NULL) -- ref9
2491 THEN
2492 l_name := 'MANUAL';
2493 IF (FND_LOG.LEVEL_STATEMENT >=
2494 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2495 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2496 l_module_name,' REF_9 IS NULL');
2497 END IF;
2498 ELSE
2499 BEGIN
2500 SELECT 'Y'
2501 INTO l_exists
2502 FROM ap_invoice_payments_all
2503 WHERE invoice_payment_id = to_number(l_REFERENCE_9)
2504 AND org_id = p_def_org_id;
2505 EXCEPTION
2506 WHEN NO_DATA_FOUND THEN
2507 l_name := 'MANUAL';
2508 IF (FND_LOG.LEVEL_STATEMENT
2509 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2510 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2511 l_module_name,' NO_DATA_FOUND ' ||
2512 ' WHEN SELECTING FROM ' ||
2513 ' AP_INVOICE_PAYMENTS_ALL ' ||
2514 ' WITH REF_9, HENCE MANUAL');
2515 END IF;
2516
2517 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2518 l_name := 'MANUAL';
2519 IF (FND_LOG.LEVEL_STATEMENT >=
2520 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2521 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2522 l_module_name,
2523 ' INVALID NUMBER ERROR, HENCE MANUAL');
2524 END IF;
2525 END;
2526 END IF; -- ref9
2527
2528 ELSIF (v_je_source = 'Receivables') -- Receivables
2529 THEN
2530 vl_misc_cd_flag := 'N';
2531
2532 IF (v_je_category = 'Misc Receipts') -- Misc Receipts
2533 THEN
2534 IF (FND_LOG.LEVEL_STATEMENT >=
2535 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2536 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2537 l_module_name,' PROCESSING A MISC RECEIPT');
2538 END IF;
2539
2540 BEGIN
2541 l_cash_receipt_id := l_reference_2;
2542 l_cash_receipt_hist_id := l_reference_5;
2543 EXCEPTION
2544 WHEN INVALID_NUMBER OR VALUE_ERROR
2545 THEN
2546 l_name := 'MANUAL';
2547 IF (FND_LOG.LEVEL_STATEMENT >=
2548 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2549 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2550 l_module_name,
2551 ' INVALID NUMBER ERROR, HENCE MANUAL');
2552 END IF;
2553 END;
2554
2555 ELSE
2556 IF (FND_LOG.LEVEL_STATEMENT >=
2557 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2558 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2559 l_module_name,
2560 ' PROCESSING A TRADE RECEIPT OR OTHER');
2561 END IF;
2562
2563 BEGIN
2564 l_cash_receipt_id := SUBSTR(l_reference_2,0,
2565 INSTR(l_reference_2,'C')-1);
2566 l_cash_receipt_hist_id := SUBSTR(l_reference_2,
2567 INSTR(l_reference_2,'C')+1,
2568 LENGTH(l_reference_2));
2569 EXCEPTION
2570 WHEN INVALID_NUMBER OR VALUE_ERROR
2571 THEN
2572 l_name := 'MANUAL';
2573 IF (FND_LOG.LEVEL_STATEMENT >=
2574 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2575 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2576 l_module_name,
2577 ' INVALID NUMBER ERROR, HENCE MANUAL');
2578 END IF;
2579 END;
2580 END IF; -- Misc Receipts
2581
2582 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2583 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2584 l_module_name,' REFERENCE_2 = '||L_REFERENCE_2);
2585 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2586 l_module_name,' CASH RECEIPT ID = '||
2587 TO_NUMBER(L_CASH_RECEIPT_ID));
2588 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2589 l_module_name,' CASH RECEIPT HISTORY ID = '
2590 ||TO_NUMBER(L_CASH_RECEIPT_HIST_ID));
2591 END IF;
2592
2593 IF ((l_cash_receipt_id IS NULL)
2594 OR (l_cash_receipt_hist_id IS NULL)) -- Null
2595 THEN
2596 l_name := 'MANUAL';
2597
2598 ELSIF (l_cash_receipt_id IS NOT NULL)
2599 THEN
2600 BEGIN
2601 SELECT 'Y'
2602 INTO l_exists
2603 FROM Ar_Cash_Receipts_All
2604 WHERE cash_receipt_id = to_number(l_cash_receipt_id)
2605 AND org_id = p_def_org_id;
2606
2607 IF (FND_LOG.LEVEL_STATEMENT >=
2608 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2609 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2610 l_module_name,' CASH RECEIPT ID ' ||
2611 ' EXISTS IN AR_CASH_RECEIPTS_ALL.');
2612 END IF;
2613 EXCEPTION
2614 WHEN NO_DATA_FOUND THEN
2615 l_name := 'MANUAL';
2616 IF (FND_LOG.LEVEL_STATEMENT >=
2617 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2618 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2619 l_module_name,' NO_DATA_FOUND'
2620 || 'WHEN SELECTING FROM '||
2621 'AR_CASH_RECEIPTS_ALL, ' ||
2622 ' HENCE MANUAL');
2623 END IF;
2624
2625 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2626 l_name := 'MANUAL';
2627 IF (FND_LOG.LEVEL_STATEMENT >=
2628 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2629 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2630 l_module_name,
2631 ' INVALID NUMBER ERROR, HENCE MANUAL');
2632 END IF;
2633 END;
2634
2635 BEGIN
2636 SELECT 'Y'
2637 INTO l_exists
2638 FROM Ar_Cash_Receipt_History_All
2639 WHERE cash_receipt_history_id =
2640 to_number(l_cash_receipt_hist_id)
2641 AND org_id = p_def_org_id;
2642
2643 IF (FND_LOG.LEVEL_STATEMENT >=
2644 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2645 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2646 l_module_name,' CASH RECEIPT HIST '||
2647 'ID EXISTS IN AR_CASH_RECEIPT_HISTORY_ALL.');
2648 END IF;
2649 EXCEPTION
2650 WHEN NO_DATA_FOUND THEN
2651 IF (v_je_category = 'Misc Receipts') -- Misc Receipts
2652 THEN
2653 IF (FND_LOG.LEVEL_STATEMENT >=
2654 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2655 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2656 l_module_name,' FOR MISC RECEIPT: ' ||
2657 'CASH RECEIPT HIST ID DOES NOT '||
2658 'exist in Ar_Cash_Receipt_History_All table. ' ||
2659 'Checking in Ar_Misc_Cash_Distributions_All table.');
2660 END IF;
2661 l_exists := 'M';
2662
2663 ELSE
2664 IF (FND_LOG.LEVEL_STATEMENT >=
2665 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2666 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2667 l_module_name,' FOR TRADE RECEIPT: ' ||
2668 'CASH RECEIPT HIST ID DOES NOT '||
2669 'exist in Ar_Cash_Receipt_History_All table. ' ||
2670 ' Checking in Ar_Receivable_Applications_All table.');
2671 END IF;
2672 l_exists := 'C';
2673 END IF; -- Misc Receipts
2674
2675 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2676 l_name := 'MANUAL';
2677 IF (FND_LOG.LEVEL_STATEMENT >=
2678 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2679 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2680 l_module_name,' INVALID NUMBER ' ||
2681 'ERROR, HENCE MANUAL');
2682 END IF;
2683 END;
2684
2685 IF ((v_je_category <> 'Misc Receipts')
2686 AND (l_exists = 'C')) -- je_cat, l_exists
2687 THEN
2688 BEGIN
2689 SELECT cash_receipt_history_id
2690 INTO l_temp_cr_hist_id
2691 FROM Ar_Receivable_Applications_All
2692 WHERE receivable_application_id =
2693 TO_NUMBER(l_cash_receipt_hist_id)
2694 AND org_id = p_def_org_id;
2695
2696 l_cash_receipt_hist_id := l_temp_cr_hist_id;
2697
2698 IF (FND_LOG.LEVEL_STATEMENT >=
2699 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2700 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2701 l_module_name,' CASH RECEIPT HIST ID '||
2702 L_CASH_RECEIPT_HIST_ID ||', EXISTS IN '||
2703 'Ar_Receivable_Applications_All. Checking in ' ||
2704 ' Ar_Cash_Receipt_History_All to see' ||
2705 ' if it is a valid id.');
2706 END IF;
2707
2708 BEGIN
2709 SELECT 'Y'
2710 INTO l_exists
2711 FROM Ar_Cash_Receipt_History_All
2712 WHERE cash_receipt_history_id =
2713 TO_NUMBER(l_cash_receipt_hist_id)
2714 AND org_id = p_def_org_id;
2715
2716 IF (FND_LOG.LEVEL_STATEMENT >=
2717 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2718 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2719 l_module_name,' CASH RECEIPT HIST ID ' ||
2720 ' EXISTS IN THE AR_CASH_RECEIPT_HISTORY_ALL '
2721 ||' TABLE.');
2722 END IF;
2723 EXCEPTION
2724 WHEN NO_DATA_FOUND THEN
2725 l_name := 'MANUAL';
2726 IF (FND_LOG.LEVEL_STATEMENT >=
2727 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2728 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2729 l_module_name,' NO_DATA_FOUND ' ||
2730 ' WHEN SELECTING FROM '||
2731 ' AR_CASH_RECEIPT_HISTORY_ALL, ' ||
2732 'HENCE MANUAL');
2733 END IF;
2734
2735 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2736 l_name := 'MANUAL';
2737 IF (FND_LOG.LEVEL_STATEMENT >=
2738 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2739 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2740 l_module_name,
2741 ' INVALID NUMBER ERROR, HENCE MANUAL');
2742 END IF;
2743 END;
2744 EXCEPTION
2745 WHEN NO_DATA_FOUND THEN
2746 l_name := 'MANUAL';
2747 IF (FND_LOG.LEVEL_STATEMENT >=
2748 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2749 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2750 l_module_name,
2751 ' NO_DATA_FOUND WHEN '||
2752 ' SELECTING FROM AR_RECEIVABLE' ||
2753 '_APPLICATIONS_ALL, HENCE MANUAL');
2754 END IF;
2755
2756 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2757 l_name := 'MANUAL';
2758 IF (FND_LOG.LEVEL_STATEMENT
2759 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2760 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2761 l_module_name,
2762 ' INVALID NUMBER ERROR, HENCE MANUAL');
2763 END IF;
2764
2765 END;
2766 ELSIF ((v_je_category = 'Misc Receipts') AND (l_exists = 'M'))
2767 THEN
2768 BEGIN
2769 SELECT 'Y'
2770 INTO l_exists
2771 FROM Ar_Misc_Cash_Distributions_All
2772 WHERE misc_cash_distribution_id =
2773 TO_NUMBER(l_cash_receipt_hist_id)
2774 AND org_id = p_def_org_id;
2775
2776 IF (FND_LOG.LEVEL_STATEMENT >=
2777 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2778 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2779 l_module_name,
2780 ' MISC CASH DISTRIBUTION ID EXISTS IN '||
2781 'Ar_Misc_Cash_Distributions_All table and is '||
2782 TO_NUMBER(l_cash_receipt_hist_id));
2783 END IF;
2784
2785 vl_misc_cd_flag := 'Y';
2786 EXCEPTION
2787 WHEN NO_DATA_FOUND THEN
2788 l_name := 'MANUAL';
2789 IF (FND_LOG.LEVEL_STATEMENT >=
2790 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2791 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2792 l_module_name,
2793 ' NO_DATA_FOUND WHEN '||
2794 ' SELECTING FROM AR_MISC_CASH_' ||
2795 'DISTRIBUTIONS_ALL, HENCE MANUAL');
2796 END IF;
2797
2798 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2799 l_name := 'MANUAL';
2800 IF (FND_LOG.LEVEL_STATEMENT >=
2801 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2802 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2803 l_module_name,
2804 ' INVALID NUMBER ERROR, HENCE MANUAL');
2805 END IF;
2806 END;
2807 END IF; -- je_cat, l_exists
2808 END IF; -- Null
2809 END IF;
2810 -- Reference Validity Check
2811 END IF; -- l_je_from_sla_flag
2812
2813
2814 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2815 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
2816 ' L_NAME: '||L_NAME);
2817 END IF;
2818
2819 -------------------------------------------
2820 -- Find ALC_CODE for each record.
2821 -------------------------------------------
2822 IF (l_name <> 'MANUAL')
2823 THEN -- <> Manual
2824
2825 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2826 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2827 l_module_name,'-> FIND ALC ...');
2828 END IF;
2829
2830 IF (v_je_source = 'Receivables')
2831 THEN -- Source Check to find ALC
2832
2833 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2834 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2835 l_module_name,
2836 ' GETTING ALC FOR SOURCE = ' ||
2837 ' RECEIVABLES ...');
2838 END IF;
2839
2840 l_cash_receipt_id := l_reference_2; --Bug# 640664
2841
2842 -- Adiaaaa
2843
2844 --Bug# 6406646
2845 /* IF l_je_from_sla_flag = 'Y' THEN
2846
2847 get_reference_column ('RECEIPTS',
2848 l_batch_id ,
2849 l_je_header_id ,
2850 l_je_line_num ,
2851 l_cash_receipt_id ,
2852 l_appl_reference ,
2853 l_cash_receipt_hist_id ,
2854 200 );
2855 END IF;
2856
2857 l_reference_2 := l_cash_receipt_id; */
2858
2859 -- Get agency location code
2860 BEGIN
2861 SELECT cba.agency_location_code
2862 INTO l_temp_alc_code
2863 FROM ce_bank_accounts cba,
2864 ar_cash_receipts_all acr,
2865 ce_bank_acct_uses_all cbau
2866 WHERE acr.cash_receipt_id = l_cash_receipt_id --l_reference_2
2867 AND acr.remit_bank_acct_use_id = cbau.bank_acct_use_id
2868 AND cba.bank_account_id = cbau.bank_account_id
2869 AND cbau.org_id = p_def_org_id
2870 AND cba.account_owner_org_id = cbau.org_id
2871 AND cbau.org_id = acr.org_id;
2872
2873 IF l_temp_alc_code IS NOT NULL
2874 THEN
2875 l_alc_code := l_temp_alc_code;
2876 END IF;
2877
2878 EXCEPTION
2879 WHEN NO_DATA_FOUND THEN
2880 IF (FND_LOG.LEVEL_STATEMENT
2881 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2882 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2883 l_module_name,' SINCE NO_DATA_FOUND, USE DEFAULT ALC.');
2884 END IF;
2885 END;
2886
2887 ELSIF v_je_source = 'Budgetary Transaction'
2888 AND v_je_category = 'Treasury Confirmation' THEN
2889
2890 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2891 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2892 l_module_name,
2893 ' GETTING ALC FOR SOURCE = '||
2894 ' Budgetary Transaction, CATEGORY = TREASURY ' ||
2895 'CONFIRMATION ...');
2896 END IF;
2897
2898
2899 -- Adi
2900
2901 --Bug# 6406646
2902 /* IF l_je_from_sla_flag = 'Y' THEN
2903
2904 get_reference_column ('TREASURY_CONFIRMATION',
2905 l_batch_id ,
2906 l_je_header_id ,
2907 l_je_line_num ,
2908 l_reference_1 ,
2909 l_reference_3 ,
2910 l_cash_receipt_hist_id ,
2911 200 );
2912 END IF; */
2913
2914 BEGIN
2915
2916 SELECT cba.agency_location_code
2917 INTO l_temp_alc_code
2918 FROM Fv_Treasury_Confirmations_all ftc,
2919 Ap_Inv_Selection_Criteria_all aisc,
2920 ce_bank_accounts cba,
2921 ce_bank_acct_uses_all cbau
2922 WHERE ftc.treasury_confirmation_id = to_number(l_reference_1)
2923 AND aisc.checkrun_name = ftc.checkrun_name
2924 AND cba.bank_account_id = aisc.bank_account_id
2925 AND cba.bank_account_id = cbau.bank_account_id
2926 AND cbau.org_id = p_def_org_id
2927 AND cba.account_owner_org_id = cbau.org_id
2928 AND cbau.org_id = ftc.org_id
2929 AND ftc.org_id = aisc.org_id;
2930
2931 IF l_temp_alc_code IS NOT NULL
2932 THEN
2933 l_alc_code := l_temp_alc_code;
2934 END IF;
2935
2936 EXCEPTION
2937 WHEN NO_DATA_FOUND THEN
2938 -- IF agency location code cannot be found using
2939 -- reference_1 then use reference_3
2940 BEGIN
2941 SELECT cba.agency_location_code
2942 INTO l_temp_alc_code
2943 FROM ap_checks apa,
2944 ce_bank_accounts cba,
2945 ce_bank_acct_uses_all cbau
2946 WHERE TO_CHAR(apa.check_id) = l_reference_3
2947 AND apa.bank_account_id = cba.bank_account_id
2948 AND apa.ce_bank_acct_use_id = cbau.bank_acct_use_id
2949 AND apa.bank_Account_id = cbau.bank_account_id
2950 AND cbau.org_id = p_def_org_id
2951 AND cba.bank_account_id = cbau.bank_account_id
2952 AND cba.account_owner_org_id = cbau.org_id
2953 AND cbau.org_id = apa.org_id;
2954
2955 IF l_temp_alc_code IS NOT NULL
2956 THEN
2957 l_alc_code := l_temp_alc_code;
2958 END IF;
2959
2960 EXCEPTION
2961 WHEN NO_DATA_FOUND THEN
2962 IF (FND_LOG.LEVEL_STATEMENT >=
2963 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2964 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2965 l_module_name,
2966 ' SINCE NO_DATA_FOUND, USE DEFAULT ALC.');
2967 END IF;
2968 END;
2969 END;
2970
2971 ELSIF (v_je_source = 'Payables'
2972 AND v_je_category <> 'Treasury Confirmation') THEN
2973
2974 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
2975 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
2976 l_module_name,' GETTING ALC FOR SOURCE = ' ||
2977 ' PAYABLES, CATEGORY <> TREASURY CONFIRMATION ...');
2978 END IF;
2979
2980
2981 -- Adi
2982
2983 --Bug# 6406646
2984 /* IF l_je_from_sla_flag = 'Y' THEN
2985
2986 get_reference_column ('AP_PAYMENTS',
2987 l_batch_id ,
2988 l_je_header_id ,
2989 l_je_line_num ,
2990 l_reference_3 ,
2991 l_reference_2 ,
2992 l_cash_receipt_hist_id ,
2993 200 );
2994
2995
2996 END IF; */
2997
2998
2999 BEGIN
3000 SELECT distinct org_id
3001 INTO l_org_id
3002 FROM ap_invoice_payments_all
3003 WHERE invoice_id = to_number(l_reference_2);
3004 EXCEPTION
3005 WHEN OTHERS THEN
3006 p_error_code := 2;
3007 p_error_msg := SQLERRM||'--Error while deriving ' ||
3008 'the org_id, in the '||
3009 'procedure Process_1219_Transactions.';
3010 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3011 l_module_name||'.error20',p_error_msg);
3012 END;
3013
3014
3015 IF (FND_LOG.LEVEL_STATEMENT
3016 >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3017 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3018 ' ORG ID OF THE TXN IS '||TO_CHAR(L_ORG_ID));
3019 END IF;
3020
3021 IF l_org_id IS NULL THEN
3022 l_payables_ia_paygroup := p_def_p_ia_paygroup ;
3023 ELSE
3024 BEGIN
3025 SELECT payables_ia_paygroup
3026 INTO l_payables_ia_paygroup
3027 FROM fv_operating_units_all
3028 WHERE org_id = l_org_id;
3029 EXCEPTION
3030 WHEN OTHERS THEN
3031 p_error_code := 2;
3032 p_error_msg := SQLERRM ||'--Error while deriving the '||
3033 'payables_ia_paygroup in the procedure '||
3034 ' Process_1219_Transactions';
3035 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3036 l_module_name||'.error21',p_error_msg);
3037 END;
3038 END IF;
3039
3040 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3041 then
3042 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3043 l_module_name,
3044 ' PAYBLES PAY GROUP IS '||L_PAYABLES_IA_PAYGROUP);
3045 END IF;
3046
3047 l_sf1219_type_code := 'DISBURSEMENT';
3048
3049 BEGIN /* to process DIT payments */
3050 l_Error_stage := 0;
3051 l_inv_amount := 0;
3052
3053 l_reference := To_Number(l_reference_2) ;
3054 BEGIN
3055 SELECT api.invoice_id,
3056 api.vendor_id,
3057 api.invoice_amount,
3058 nvl(apc.treasury_pay_date,apc.check_date)
3059 INTO l_invoice_id ,
3060 l_vendor_id,
3061 l_inv_amount,
3062 l_check_date
3063 FROM ap_checks_all apc,
3064 ap_invoices_all api
3065 WHERE api.invoice_id = NVL(l_reference, 0)
3066 AND apc.check_id = to_number(l_reference_3)
3067 AND l_payables_ia_paygroup = api.pay_group_lookup_code
3068 AND apc.payment_method_lookup_code = 'CLEARING';
3069
3070 l_inter_agency_flag := 'Y';
3071 EXCEPTION
3072 when too_many_rows THEN
3073 p_error_msg := 'Too many rows in invoice ' ||
3074 'info,dit select';
3075 p_error_code := -1;
3076 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3077 l_module_name||'.error22', p_error_msg) ;
3078 return;
3079
3080 when No_Data_Found THEN
3081 l_inter_agency_flag := 'N' ;
3082 END;
3083
3084 IF ( FND_LOG.LEVEL_STATEMENT >=
3085 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3086 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3087 l_module_name,'VENDOR ID,INVOICE AMT,CHECK DATE ARE: '||
3088 TO_CHAR(l_vendor_id)||' '||TO_CHAR(l_inv_amount)||' '||
3089 TO_CHAR(l_check_date, 'MM/DD/YYYY'));
3090 END IF;
3091
3092 IF ( FND_LOG.LEVEL_STATEMENT >=
3093 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3094 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3095 l_module_name,'INTERAGENCY FLAG IS '
3096 ||l_inter_agency_flag);
3097 END IF;
3098
3099 l_error_stage := 1;
3100
3101 -- Get agency location code
3102 BEGIN
3103 SELECT cba.agency_location_code
3104 INTO l_temp_alc_code
3105 FROM ap_invoice_payments_all aip,
3106 ap_checks_all aca,
3107 ce_bank_accounts cba,
3108 ce_bank_acct_uses_all cbau
3109 WHERE TO_CHAR(aip.invoice_id) = l_reference_2
3110 AND aca.check_id = l_reference_3
3111 AND aip.set_of_books_id = p_set_bks_id
3112 AND aip.check_id = aca.check_id
3113 AND aca.bank_account_id = cba.bank_account_id
3114 AND aca.ce_bank_acct_use_id = cbau.bank_acct_use_id
3115 AND cba.bank_account_id = cbau.bank_account_id
3116 AND cbau.org_id = p_def_org_id
3117 AND cba.account_owner_org_id = cbau.org_id
3118 AND cbau.org_id = aip.org_id
3119 AND aip.org_id = aca.org_id
3120 AND rownum < 2;
3121
3122 IF l_temp_alc_code IS NOT NULL
3123 THEN
3124 l_alc_code := l_temp_alc_code;
3125 END IF;
3126
3127 EXCEPTION
3128 WHEN NO_DATA_FOUND THEN
3129 IF (FND_LOG.LEVEL_STATEMENT >=
3130 FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3131 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3132 l_module_name,
3133 ' SINCE NO_DATA_FOUND, USE DEFAULT ALC.');
3134 END IF;
3135 END;
3136 END;
3137 END IF; -- Source Check to find ALC
3138
3139 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3140 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3141 l_module_name,' ALC: '||L_ALC_CODE);
3142 END IF;
3143 END IF; -- <> Manual
3144
3145 -- Check to see if the derived alc_code is the same
3146 -- as the parameter alc_code. If it is the same then
3147 -- continue, else skip further processing.
3148
3149 IF ((UPPER(p_alc_code) = 'ALL' OR l_alc_code = p_alc_code)
3150 AND l_name <> 'MANUAL')
3151 THEN -- Non-Manual Lines for ALL/any ALC
3152
3153 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3154 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3155 '-> GET ACCOMPLISH_DATE ...');
3156 END IF;
3157
3158 -- Following code derives Accomplish date,
3159 -- Inter Agency flag and Obligation date
3160
3161 -- Source Check to find Accomplish Date
3162 IF (v_je_source = 'Receivables') THEN
3163 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3164 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3165 ' GETTING ACCOMPLISH_DATE FOR SOURCE = RECEIVABLES ...');
3166 END IF;
3167
3168
3169
3170 l_exists := 'N';
3171 l_inter_agency_flag := 'N';
3172 l_sf1219_type_code := 'RECEIPT';
3173 l_record_type := 'A';
3174
3175 BEGIN
3176 SELECT 'X'
3177 INTO null_var
3178 FROM FV_INTERAGENCY_FUNDS_ALL
3179 WHERE cash_receipt_id = l_cash_receipt_id
3180 and org_id = p_def_org_id;
3181
3182 l_inter_agency_flag := 'Y';
3183 l_update_type := 'RECEIPT';
3184
3185 EXCEPTION
3186 WHEN NO_DATA_FOUND THEN
3187 l_inter_agency_flag := 'N';
3188 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3189 then
3190 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3191 l_module_name, ' NO_DATA_FOUND: '||
3192 ' SETTING L_INTER_AGENCY_FLAG = N ... ');
3193 END IF;
3194
3195 WHEN TOO_MANY_ROWS THEN
3196 p_error_code := -1;
3197 p_error_msg :=
3198 'Too many rows in interagnecy select' ||
3199 ' for cash receipt '|| to_char(l_cash_receipt_id)||
3200 ' for Batch id '|| to_char(l_batch_id);
3201 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3202 l_module_name||'.error22',p_error_msg);
3203 RETURN;
3204 END;
3205
3206 BEGIN
3207 SELECT 'Y'
3208 INTO l_exists
3209 FROM ar_cash_receipt_history_all
3210 WHERE reversal_cash_receipt_hist_id = l_cash_receipt_hist_id
3211 AND org_id = p_def_org_id;
3212
3213 EXCEPTION
3214 WHEN NO_DATA_FOUND THEN
3215 NULL;
3216 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL )
3217 then
3218 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3219 l_module_name,
3220 'NO_DATA_FOUND: AR_CASH_RECEIPT_HISTORY_ALL '||
3221 'DOES NOT HAVE DATA FOR REVERSAL_CASH_RECEIPT_HIST_ID'
3222 || ' = '|| L_CASH_RECEIPT_HIST_ID);
3223 END IF;
3224 END;
3225
3226 BEGIN
3227 SELECT DECODE(l_exists,'N',deposit_date,reversal_date)
3228 INTO l_accomplish_date
3229 FROM ar_cash_receipts_all
3230 WHERE cash_receipt_id = l_cash_receipt_id
3231 ANd org_id = p_def_org_id;
3232
3233 EXCEPTION
3234 WHEN OTHERS THEN
3235 p_error_msg := SQLERRM||
3236 '- Error while deriving the accomplish date'
3237 ||' for the cash receipt id '||l_cash_receipt_id;
3238 p_error_code := 1 ;
3239 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3240 l_module_name||'.error22',p_error_msg);
3241 RETURN;
3242 END;
3243
3244 -- Check for Refunded invoice
3245 OPEN refund_cursor;
3246 IF (SQLCODE < 0)
3247 THEN
3248 p_error_code := sqlcode ;
3249 p_error_msg := sqlerrm ;
3250 RETURN ;
3251 END IF;
3252
3253 LOOP
3254 FETCH refund_cursor
3255 INTO l_obligation_date, l_inv_amount ;
3256
3257 IF (refund_cursor%NOTFOUND)
3258 THEN
3259 EXIT;
3260 END IF;
3261
3262 l_type := 'AP_REFUND';
3263 l_update_type := 'RECEIPT';
3264 l_processed_flag := 'Y';
3265 l_name := 'Refunds_and_Voids';
3266
3267 l_sf1219_type_code := 'RECEIPT_REFUND';
3268 l_record_type := 'A';
3269
3270 -- Exception category is being derived before inserting new records
3271 set_exception_category;
3272
3273 -- If it is for Future month with Future accomplish date then
3274 -- it is not reported.
3275 IF l_reported_month = 'FUTURE'
3276 AND l_exception_category IN ('FUTURE_ACCOMPLISH','FUTURE PERIOD')
3277 THEN
3278 l_exception_category := NULL;
3279 l_group_name := NULL;
3280 ELSE
3281
3282 -- Assign Group Name for these records
3283 assign_group_name;
3284
3285 -- Accomplish month is populated for the new records
3286 l_accomplish_month := to_char(l_accomplish_date, 'MMYYYY');
3287
3288 -- Insert new record as record_type 'A'. (changes on 7-Jun-1999)
3289 -- This was being inserted as 'E'
3290 BEGIN
3291 INSERT INTO FV_SF1219_TEMP(
3292 temp_record_id,
3293 batch_id,
3294 fund_code,
3295 name,
3296 set_of_books_id,
3297 posted_date,
3298 gl_period,
3299 reported_gl_period,
3300 amount,
3301 sf1219_type_code,
3302 reference_1,
3303 reference_2,
3304 reference_3,
3305 reference_4,
3306 reference_5,
3307 reference_6,
3308 reported_month,
3309 default_period_name,
3310 exception_category,
3311 accomplish_month,
3312 accomplish_date,
3313 obligation_date,
3314 inter_agency_flag,
3315 treasury_symbol,
3316 treasury_symbol_id,
3317 record_type,
3318 lines_exist,
3319 alc_code,
3320 org_id,
3321 group_name,
3322 update_type,
3323 type,
3324 gl_period_name,
3325 processed_flag,
3326 creation_date,
3327 created_by,
3328 last_update_date,
3329 last_updated_by,
3330 last_update_login,
3331 je_header_id,
3332 je_line_num)
3333 VALUES(
3334 fv_sf1219_temp_s.nextval,
3335 l_batch_id,
3336 l_fund_code,
3337 l_name,
3338 p_set_bks_id,
3339 l_posted_date,
3340 l_gl_period,
3341 l_reported_gl_period,
3342 l_inv_amount,
3343 l_sf1219_type_code,
3344 l_reference_1,
3345 l_reference_2,
3346 l_reference_3,
3347 l_reference_4,
3348 l_reference_5,
3349 l_reference_6,
3350 l_reported_month,
3351 l_default_period_name,
3352 l_exception_category,
3353 l_accomplish_month,
3354 l_accomplish_date,
3355 l_obligation_date,
3356 l_inter_agency_flag,
3357 l_treasury_symbol,
3358 l_treasury_symbol_id,
3359 'A' ,
3360 'N',
3361 l_alc_code,
3362 -1, --l_org_id,
3363 l_group_name,
3364 l_update_type,
3365 l_type,
3366 l_gl_period_name,
3367 l_processed_flag,
3368 sysdate,
3369 FND_GLOBAL.USER_ID,
3370 sysdate,
3371 FND_GLOBAL.USER_ID,
3372 FND_GLOBAL.LOGIN_ID,
3373 l_je_header_id,
3374 l_je_line_num ) ;
3375 COMMIT;
3376 EXCEPTION WHEN OTHERS THEN
3377 p_error_code := sqlcode ;
3378 p_error_msg := sqlerrm ;
3379 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3380 l_module_name||'.error25',p_error_msg);
3381 ROLLBACK ;
3382 RETURN;
3383 END;
3384
3385 l_org_amount := l_org_amount - l_inv_amount ;
3386
3387 END IF ;
3388 END LOOP ;
3389 CLOSE refund_cursor ;
3390
3391 IF l_processed_flag = 'Y' THEN
3392 l_amount := l_org_amount ;
3393 END IF ;
3394
3395 l_sf1219_type_code := 'RECEIPT' ;
3396 l_record_type := 'A' ;
3397
3398 ELSIF (l_name like '%TREASURY%' AND
3399 v_je_source = 'Budgetary Transaction' AND
3400 v_je_category = 'Treasury Confirmation')
3401 THEN
3402 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3403 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3404 ' GETTING ACCOMPLISH_DATE FOR NAME LIKE %TREASURY%, '||
3405 ' SOURCE = Budgetary Transaction, CATEGORY = TREASURY CONFIRMATION ...');
3406 END IF;
3407
3408 IF (l_reference_1 IS NULL) THEN
3409 l_accomplish_date := l_reference_6;
3410 ELSE
3411 BEGIN
3412 SELECT treasury_doc_date
3413 INTO l_accomplish_date
3414 FROM fv_treasury_confirmations_all
3415 WHERE TO_CHAR(treasury_confirmation_id) = l_reference_1
3416 AND org_id = p_def_org_id;
3417 EXCEPTION
3418 WHEN TOO_MANY_ROWS THEN
3419 p_error_code := -1 ;
3420 p_error_msg :=
3421 'Too many rows in treasury_doc_date select for ' ||
3422 'treasury confirmation id '||substr(l_reference_6,1,20)||
3423 ' for Batch id '|| to_char(l_batch_id) ;
3424 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3425 l_module_name||'.error26',p_error_msg);
3426 RETURN ;
3427
3428 WHEN OTHERS THEN
3429 p_error_code := -1 ;
3430 p_error_msg := SQLERRM|| ' - Error when deriving the ' ||
3431 ' treasury_doc_date from ' ||
3432 'fv_treasury_confirmations_all.';
3433 END ;
3434 END IF;
3435
3436 l_sf1219_type_code := 'DISBURSEMENT' ;
3437 l_record_type := 'A' ;
3438
3439 ELSIF (v_je_source = 'Payables' AND
3440 v_je_category <> 'Treasury Confirmation')
3441 THEN
3442 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3443 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3444 ' GETTING ACCOMPLISH_DATE FOR SOURCE = PAYABLES, ' ||
3445 ' CATEGORY <> TREASURY CONFIRMATION ...');
3446 END IF;
3447
3448 --l_sf1219_type_code := 'DISBURSEMENT';
3449 l_record_type := 'A';
3450 l_inv_pay_id := 0;
3451
3452 IF l_inter_agency_flag = 'Y'
3453 THEN
3454 BEGIN
3455 SELECT chargeback_flag,
3456 iaf.billing_agency_fund
3457 INTO l_cb_flag,
3458 l_billing_agency_fund
3459 FROM fv_interagency_funds_all iaf
3460 WHERE iaf.vendor_id = l_vendor_id
3461 AND iaf.invoice_id = l_invoice_id
3462 AND iaf.org_id = p_def_org_id;
3463 EXCEPTION
3464 when no_data_found THEN
3465 l_billing_agency_fund := 'UNDEFINED';
3466 l_exception_category := 'PAYABLES_MISSING_IAF';
3467 l_treasury_symbol := 'UNDEFINED';
3468 l_record_type := 'E' ;
3469
3470 -- Insert the exception transaction
3471 insert_exceptions(l_amount);
3472
3473 -- The record type is set to 'O' to prevent the data
3474 -- record to be shown up the 1219/1220 Report which caused
3475 -- this exception.
3476 l_record_type := 'O';
3477
3478 when too_many_rows THEN
3479 p_error_msg := 'Too many rows in chargeback
3480 flag Prelim select';
3481 p_error_code := -1;
3482 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3483 l_module_name||'.error23', p_error_msg) ;
3484 return;
3485 END;
3486
3487 End If ; --l_inter_agency_flag = 'Y'
3488
3489 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3490 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3491 l_module_name,'CHARGEBACK FLAG AND BILLING AGENCY ' ||
3492 ' FUND ARE :' || L_CB_FLAG ||' '||
3493 L_BILLING_AGENCY_FUND);
3494 END IF;
3495
3496 BEGIN /* Void Date */
3497 SELECT nvl(apc.treasury_pay_date,apc.check_date),
3498 apc.void_date
3499 INTO l_accomplish_date,
3500 l_void_date
3501 FROM ap_checks_all apc,
3502 ap_invoices_all api
3503 WHERE api.invoice_id = Nvl(l_reference, 0)
3504 AND apc.check_id = nvl(l_reference_3,0)
3505 AND apc.org_id = p_def_org_id
3506 AND api.org_id = p_def_org_id;
3507
3508 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3509 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3510 l_module_name,'CHECK DATE/ACCOM DATE AND VOID DATE ARE '||
3511 TO_CHAR(l_accomplish_date, 'MM/DD/YYYY')||' '||
3512 TO_CHAR(l_void_date, 'MM/DD/YYYY'));
3513 END IF;
3514
3515 BEGIN /* VOID */
3516 l_inv_pay_id := 0;
3517
3518 IF (l_void_date IS NOT NULL) THEN
3519
3520 SELECT NVL(MAX(invoice_payment_id),0)
3521 INTO l_inv_pay_id
3522 FROM ap_invoice_payments
3523 WHERE invoice_id = NVL(l_reference, 0)
3524 AND check_id = NVL(l_reference_3,0)
3525 AND invoice_payment_id >l_reference_9;
3526
3527 IF ( FND_LOG.LEVEL_STATEMENT >=
3528 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3529 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3530 l_module_name,'VOID DATE IS NOT ' ||
3531 'NULL AND INVOICE '||
3532 'payment id is '||TO_CHAR(l_inv_pay_id));
3533 END IF;
3534
3535 IF (l_inv_pay_id = 0) THEN
3536 l_accomplish_date := l_void_date ;
3537 l_sf1219_type_code := 'VOID';
3538 l_record_type := 'A';
3539
3540 BEGIN /* V1 */
3541 SELECT obligation_date
3542 INTO l_obligation_date
3543 FROM fv_refunds_voids_all
3544 WHERE type = 'VOID'
3545 AND invoice_id = to_number(l_reference_2)
3546 AND check_id = to_number(l_reference_3)
3547 AND org_id = p_def_org_id;
3548
3549 l_processed_flag := 'Y';
3550 l_update_type := 'VOID_PAYABLE';
3551 l_type := 'VOID';
3552 l_sf1219_type_code := 'VOID';
3553 l_record_type := 'A';
3554
3555 IF ( FND_LOG.LEVEL_STATEMENT >=
3556 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3557 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3558 l_module_name,'OBLIGATION DATE IS '||
3559 TO_CHAR(l_obligation_date, 'MM/DD/YYYY'));
3560 END IF;
3561
3562 EXCEPTION
3563 WHEN No_Data_Found Then
3564 l_error_stage := -1;
3565 l_billing_agency_fund := 'UNDEFINED';
3566 l_exception_category := 'VOID_MISSING_FRV';
3567 l_treasury_symbol := 'UNDEFINED';
3568 l_accomplish_date := NULL;
3569 l_record_type := 'E';
3570
3571 IF ( FND_LOG.LEVEL_STATEMENT >=
3572 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3573 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3574 l_module_name,'RITA GERA 1');
3575 END IF;
3576
3577 -- Insert the exception transaction
3578 INSERT_EXCEPTIONS(l_org_amount);
3579 -- -------------RITA GERA----------------
3580 l_record_type := 'O' ;
3581
3582 WHEN TOO_MANY_ROWS THEN
3583 p_error_msg := 'Too many rows in' ||
3584 ' obligation_date select';
3585 p_error_code := -1;
3586 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3587 l_module_name||'.error25', p_error_msg) ;
3588 return;
3589 END ; /* V1 */
3590
3591 END IF; -- inv_pay_id = 0
3592 END IF; -- l_void_date is not null
3593 --END ; /* VOID */
3594 EXCEPTION
3595 WHEN TOO_MANY_ROWS THEN
3596 p_error_msg := 'Too many rows in void_date' ||
3597 ' disbursement select';
3598 p_error_code := -1;
3599 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3600 l_module_name||'.error26', p_error_msg) ;
3601 return;
3602
3603 WHEN NO_DATA_FOUND THEN
3604 null;
3605
3606 WHEN OTHERS THEN
3607 p_error_msg := sqlerrm ;
3608 p_Error_Code := -1 ;
3609 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3610 l_module_name||'.error27', p_error_msg) ;
3611 RollBack ;
3612 Return ;
3613 END ; /* Void Date */
3614
3615 If (l_inter_agency_flag = 'Y' and l_error_stage <> -1) Then
3616 if (l_cb_flag = 'Y') then -- charge back flag
3617 l_sf1219_type_code := 'RECEIPT';
3618 End if; -- charge back flag = 'Y'
3619 End If ;
3620
3621 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
3622 THEN
3623 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3624 l_module_name,'RECORD TYPE IS '||L_RECORD_TYPE);
3625 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3626 l_module_name,'1219 TYPE CODE IS '||L_SF1219_TYPE_CODE);
3627 END IF;
3628
3629 EXCEPTION
3630 when no_data_found then
3631 If (l_error_stage = 1) then
3632 l_billing_agency_fund := 'UNDEFINED';
3633 l_exception_category := 'PAYABLES_MISSING_IAF';
3634 l_treasury_symbol := 'UNDEFINED';
3635 l_record_type := 'E' ;
3636
3637 -- Insert the exception transaction
3638 insert_exceptions(l_amount);
3639
3640 -- The record type is set to 'O' to prevent the data
3641 -- record to be shown up the 1219/1220 Report which caused
3642 -- this exception.
3643 l_record_type := 'O';
3644
3645 End if;
3646 WHEN others then
3647 p_error_msg := sqlerrm;
3648 p_error_code := -1;
3649 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3650 l_module_name||'.error28', p_error_msg) ;
3651 rollback;
3652 return;
3653 END ; /* End proces DIT */
3654
3655 -- Following code would derive accomplish date for VOID transactions
3656 ELSIF (l_name LIKE '%VOID%'
3657 AND v_je_source = 'Budgetary Transaction'
3658 AND v_je_category = 'Treasury Confirmation')
3659 THEN
3660 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3661 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3662 ' GETTING ACCOMPLISH_DATE FOR NAME LIKE %VOID%, '||
3663 'SOURCE = Budgetary Transaction, CATEGORY = '||
3664 ' TREASURY CONFIRMATION ...');
3665 END IF;
3666
3667 l_sf1219_type_code := 'VOID' ;
3668 l_record_type := 'A' ;
3669 l_void_incomplete := 'N';
3670
3671 BEGIN
3672
3673 SELECT void_date
3674 INTO l_accomplish_date
3675 FROM ap_checks_all
3676 WHERE TO_CHAR(check_id) = NVL(l_reference_3,'0')
3677 ANd org_id = p_def_org_id;
3678
3679 SELECT invoice_date into l_invoice_date
3680 FROM AP_INVOICES_ALL
3681 WHERE TO_CHAR(invoice_id) = NVL(l_reference_4,'0')
3682 ANd org_id = p_def_org_id;
3683
3684 EXCEPTION WHEN NO_DATA_FOUND THEN
3685 l_billing_agency_fund := 'UNDEFINED' ;
3686 l_exception_category := 'VOID_INCOMPLETE' ;
3687 l_treasury_symbol := 'UNDEFINED' ;
3688 l_record_type := 'E' ;
3689
3690 -- Bug# 3528849,
3691 -- if created VOID_INCOMPLETE do not
3692 -- create VOID_MISSING_FRV record
3693 l_void_incomplete := 'Y';
3694
3695 -- Call procedure to insert exception tranasctions
3696 INSERT_EXCEPTIONS(l_org_amount) ;
3697
3698 --The record type is set to 'O' to prevent the data
3699 -- record to be shown up the 1219/1220 Report which
3700 -- caused this exception.
3701
3702 l_record_type := 'M' ;
3703 l_accomplish_date := l_end_date1 ;
3704
3705 UPDATE fv_sf1219_temp
3706 SET record_type = l_record_type,
3707 sf1219_type_code = 'MANUAL',
3708 alc_code = l_alc_code,
3709 accomplish_date = l_accomplish_date
3710 WHERE rowid = l_rowid;
3711
3712 COMMIT;
3713 END ;
3714
3715 -- Re-assigning l_reference_4 to l_reference_2
3716 -- This is because the process is saving invoice_id in reference_2
3717
3718 l_reference_2 := l_reference_4 ;
3719
3720 BEGIN
3721 SELECT obligation_date
3722 INTO l_obligation_date
3723 FROM FV_REFUNDS_VOIDS_ALL
3724 WHERE type = 'VOID'
3725 AND TO_CHAR(invoice_id) = l_reference_2
3726 AND TO_CHAR(check_id) = l_reference_3
3727 AND org_id = p_def_org_id;
3728
3729 l_sf1219_type_code := 'VOID' ;
3730 l_record_type := 'A' ;
3731 l_processed_flag := 'Y' ;
3732 l_update_type := 'VOID_PAYABLE' ;
3733 l_type := 'VOID' ;
3734
3735 EXCEPTION
3736 WHEN NO_DATA_FOUND THEN
3737 IF (l_void_incomplete = 'N')
3738 THEN
3739 l_billing_agency_fund := 'UNDEFINED' ;
3740 l_exception_category := 'VOID_MISSING_FRV' ;
3741 l_treasury_symbol := 'UNDEFINED' ;
3742 l_accomplish_date := null ;
3743 l_record_type := 'E' ;
3744
3745 IF ( FND_LOG.LEVEL_STATEMENT >=
3746 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3747 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
3748 l_module_name,'RITA GERA 2');
3749 END IF;
3750
3751
3752 -- Call procedure to insert exception tranasctions
3753 INSERT_EXCEPTIONS(l_org_amount) ;
3754
3755 -- The record type is set to 'O' to prevent the data
3756 -- record to be shown up the 1219/1220 Report which
3757 -- caused this exception.
3758 l_record_type := 'O' ;
3759
3760 UPDATE fv_sf1219_temp
3761 SET record_type = l_record_type,
3762 sf1219_type_code = 'VOID',
3763 alc_code = l_alc_code
3764 WHERE rowid = l_rowid;
3765
3766 COMMIT;
3767 ELSE
3768 l_sf1219_type_code := 'MANUAL' ;
3769 END IF;
3770
3771 WHEN TOO_MANY_ROWS THEN
3772 p_error_code := -1;
3773 p_error_msg := 'Too many rows in obligation_date select '||
3774 'For JE batch id '||to_char(l_batch_id);
3775 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3776 l_module_name||'.error32',p_error_msg);
3777 RETURN;
3778 END ;
3779 ELSE
3780 -- If l_name does not fall under any of the above
3781 -- Fetch the end date for the period in which txn was entered
3782 BEGIN
3783
3784 SELECT end_date
3785 INTO l_accomplish_date
3786 FROM gl_periods glp, gl_sets_of_books gsob
3787 WHERE glp.period_name = l_gl_period
3788 AND glp.period_type = period_type
3789 AND gsob.set_of_books_id = p_set_bks_id
3790 AND gsob.chart_of_accounts_id = flex_num
3791 AND glp.period_set_name = gsob.period_set_name ;
3792
3793 EXCEPTION WHEN OTHERS THEN
3794 p_error_code := 2;
3795 p_error_msg := substr(sqlerrm,1,50) ||
3796 ' while fetching txn end date into accomplish_date';
3797 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3798 l_module_name||'.error5', p_error_msg) ;
3799 END;
3800 END IF; -- Source Check to find Accomplish Date
3801 END IF; -- Non-Manual Lines for ALL/any ALC
3802
3803
3804 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3805 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
3806 ' ACCOMPLISH DATE: '||L_ACCOMPLISH_DATE);
3807 END IF;
3808
3809 IF l_accomplish_date IS NOT NULL
3810 THEN
3811 IF l_exception_category is null THEN
3812 SET_EXCEPTION_CATEGORY;
3813 END IF;
3814
3815 -- Exception category is not required for 'M' and 'R' records
3816 IF l_record_type in ('M', 'R') THEN
3817 l_exception_category := null ;
3818 END IF;
3819
3820 -- *** Additional code for inserting exception records
3821 IF l_record_type = 'A' THEN
3822 IF l_exception_category = 'PRIOR PERIOD' THEN
3823 INSERT_EXCEPTIONS(x_amount) ;
3824 ELSIF l_reported_month in ('CURRENT','CURRENT / PRIOR') AND
3825 l_exception_category = 'FUTURE PERIOD' THEN
3826 INSERT_EXCEPTIONS(x_amount) ;
3827 ELSIF l_reported_month = 'FUTURE' AND
3828 l_exception_category IN
3829 ('FUTURE_ACCOMPLISH','FUTURE PERIOD') THEN
3830 INSERT_EXCEPTIONS(x_amount) ;
3831 l_record_type := 'O' ;
3832 END IF;
3833 END IF;
3834 -- *** End of additional code
3835
3836 -- Accomplish date is converted to accomplish month
3837 l_accomplish_month := to_char(l_accomplish_date,'MMYYYY') ;
3838
3839 -- Assign Group name based on 1219 Type code
3840 ASSIGN_GROUP_NAME ;
3841
3842 -- set the lines_exist to 'Y' if group name is assigned
3843 IF l_record_type = 'R' AND l_group_name IS NOT NULL THEN
3844 l_lines_exist := 'Y' ;
3845 END IF;
3846 END IF ;
3847
3848 -- Once all the relevant information is ready, update fv_sf1219_temp table
3849 UPDATE fv_sf1219_temp
3850 set sf1219_type_code = l_sf1219_type_code,
3851 reported_gl_period = l_reported_gl_period ,
3852 reported_month = l_reported_month,
3853 exception_category = l_exception_category,
3854 accomplish_month = l_accomplish_month,
3855 accomplish_date = l_accomplish_date,
3856 obligation_date = l_obligation_date,
3857 inter_agency_flag = l_inter_agency_flag,
3858 record_type = l_record_type,
3859 alc_code = l_alc_code,
3860 amount = l_org_amount,
3861 reference_2 = l_reference_2,
3862 lines_exist = l_lines_exist,
3863 --org_id = l_org_id,
3864 group_name = l_group_name,
3865 update_type = l_update_type,
3866 type = l_type,
3867 gl_period_name = p_gl_period,
3868 processed_flag = l_processed_flag
3869 WHERE rowid = l_rowid ;
3870
3871 COMMIT;
3872
3873 IF sqlcode < 0 THEN
3874 p_error_code := -1;
3875 p_error_msg := 'fv_sf1219_temp update failed' ;
3876 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3877 l_module_name||'.error33',p_error_msg);
3878 RETURN ;
3879 END IF;
3880
3881 END LOOP ;
3882
3883 -- Delete all records whose alc_code does not match parameter alc_code
3884 -- only if the parameter passed is not 'ALL' in which case no records
3885 -- are deleted
3886 IF UPPER(p_alc_code) <> 'ALL'
3887 THEN
3888 DELETE from FV_SF1219_TEMP
3889 WHERE record_type not in ('P')
3890 AND alc_code IS NOT NULL
3891 AND alc_code <> p_alc_code;
3892 END IF;
3893
3894 -- Get the supplement Number for the alc_code and period
3895 UPDATE fv_sf1219_temp fst
3896 SET supplement_number =
3897 (SELECT NVL(MAX(supplement_number), -1) + 1
3898 FROM fv_sf1219_audits fsa
3899 WHERE fst.alc_code = fsa.alc_code
3900 AND gl_period = p_gl_period);
3901
3902 COMMIT;
3903
3904 CLOSE TEMP_CURSOR ;
3905
3906 EXCEPTION
3907 WHEN OTHERS THEN
3908 IF TEMP_CURSOR%ISOPEN THEN
3909 CLOSE TEMP_CURSOR ;
3910 END IF;
3911
3912 p_error_code := SQLCODE;
3913 p_error_msg := SQLERRM || ' -- Error in ' ||
3914 'PROCESS_1219_TRANSACTIONS procedure.';
3915 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3916 l_module_name||'.final_exception',p_error_msg);
3917 ROLLBACK;
3918 RETURN;
3919 END PROCESS_1219_TRANSACTIONS;
3920
3921
3922 -----------------------------------------------------------------------------
3923 -- PROCEDURE SET_EXCEPTION_CATEGORY
3924 -----------------------------------------------------------------------------
3925 -- Update REPORTED_MONTH and EXCEPTION_CATEGORY in FV_SF1219_TEMP table
3926 ----------------------------------------------------------------------------
3927 PROCEDURE SET_EXCEPTION_CATEGORY is
3928 l_module_name VARCHAR2(200) := g_module_name || 'SET_EXCEPTION_CATEGORY';
3929 BEGIN
3930
3931 -- start date and end date for the gl_period of the record being processed
3932 -- is obtained in the following code
3933
3934 BEGIN
3935 SELECT start_date, end_date
3936 INTO l_start_date2, l_end_date2
3937 FROM GL_PERIODS glp, GL_SETS_OF_BOOKS gsob
3938 WHERE glp.period_name = l_gl_period_name
3939 AND glp.period_type = period_type
3940 AND gsob.set_of_books_id = p_set_bks_id
3941 AND gsob.chart_of_accounts_id = flex_num
3942 AND glp.period_set_name = gsob.period_set_name ;
3943
3944 EXCEPTION
3945 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
3946 p_error_code := -1 ;
3947 p_error_msg := 'No such period ('||l_gl_period||')
3948 of TEMP exists in GL_PERIODS' ;
3949 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
3950 l_module_name||'.error1',p_error_msg);
3951 ROLLBACK ;
3952 RETURN ;
3953 END ;
3954
3955 IF l_accomplish_date BETWEEN l_start_date1 AND l_end_date1 THEN
3956 l_reported_month := 'CURRENT' ;
3957
3958 IF l_end_date2 = l_end_date1 THEN
3959 l_exception_category := NULL ;
3960 ELSIF (l_end_date2 < l_end_date1) then
3961 l_exception_category := 'PRIOR PERIOD' ;
3962 ELSIF (l_end_date2 > l_end_date1) then
3963 l_exception_category := 'FUTURE PERIOD' ;
3964 END IF ;
3965
3966 ELSIF l_accomplish_date < l_start_date1 THEN
3967 l_reported_month := 'CURRENT / PRIOR' ;
3968
3969 IF l_end_date2 = l_end_date1 THEN
3970 l_exception_category := NULL ;
3971 ELSIF (l_end_date2 < l_end_date1) then
3972 l_exception_category := 'PRIOR PERIOD' ;
3973 ELSIF (l_end_date2 > l_end_date1) then
3974 l_exception_category := 'FUTURE PERIOD' ;
3975 END IF;
3976 ELSE
3977 l_reported_month := 'FUTURE' ;
3978 IF l_end_date2 = l_end_date1 then
3979 l_exception_category := 'FUTURE_ACCOMPLISH' ;
3980 ELSIF (l_end_date2 < l_end_date1) then
3981 l_exception_category := 'FUTURE_ACCOMPLISH' ;
3982 ELSIF (l_end_date2 > l_end_date1) then
3983 l_exception_category := 'FUTURE PERIOD' ;
3984 END IF ;
3985 END IF ;
3986 EXCEPTION -- procedure SET_EXCEPTION_CATEGORY
3987 WHEN OTHERS THEN
3988 p_error_code := SQLCODE;
3989 p_error_msg := SQLERRM || ' -- Error in ' ||
3990 'SET_EXCEPTION_CATEGORY procedure.';
3991 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
3992 l_module_name||'.final_exception',p_error_msg);
3993 ROLLBACK;
3994 RETURN;
3995 END SET_EXCEPTION_CATEGORY;
3996
3997
3998 ----------------------------------------------------------------------------
3999 -- PROCEDURE INSERT_EXCEPTIONS
4000 ----------------------------------------------------------------------------
4001 -- This procedure, being called from procedure PROCESS_1219_TRANSACTIONS
4002 -- inserts new transactions for pre-defined Exceptions. In case any of the
4003 -- pre-defined exception categories occurrs during the process of each
4004 -- record, a new transaction is inserted in the FV_SF1219_TEMP table with
4005 -- record type as 'E'. These exception transactions are not to be reported
4006 -- on Report 1219/1220. All records inserted with record type 'E' are
4007 -- reported on Exception Report.
4008 ----------------------------------------------------------------------------
4009
4010 PROCEDURE INSERT_EXCEPTIONS (x_amount NUMBER) IS
4011 l_module_name VARCHAR2(200) := g_module_name || 'INSERT_EXCEPTIONS';
4012 BEGIN
4013 l_accomplish_month := to_char(l_accomplish_date,'MMYYYY') ;
4014
4015 INSERT INTO fv_sf1219_temp(
4016 temp_record_id,
4017 batch_id,
4018 fund_code,
4019 name,
4020 set_of_books_id,
4021 posted_date,
4022 gl_period,
4023 reported_gl_period,
4024 amount,
4025 sf1219_type_code,
4026 reference_1,
4027 reference_2,
4028 reference_3,
4029 reference_4,
4030 reference_5,
4031 reference_6,
4032 reported_month,
4033 default_period_name,
4034 exception_category,
4035 accomplish_month,
4036 accomplish_date,
4037 obligation_date,
4038 inter_agency_flag,
4039 treasury_symbol,
4040 treasury_symbol_id, --Added to fix Bug. 1575992
4041 record_type,
4042 lines_exist,
4043 alc_code,
4044 org_id,
4045 group_name,
4046 update_type,
4047 type,
4048 gl_period_name,
4049 processed_flag,
4050 creation_date,
4051 created_by,
4052 last_update_date,
4053 last_updated_by,
4054 last_update_login,
4055 je_header_id,
4056 je_line_num)
4057 VALUES(
4058 fv_sf1219_temp_s.nextval,
4059 l_batch_id,
4060 l_fund_code,
4061 l_name_keep,
4062 p_set_bks_id,
4063 l_posted_date,
4064 l_gl_period,
4065 l_reported_gl_period,
4066 x_amount,
4067 l_sf1219_type_code,
4068 l_reference_1,
4069 l_reference_2,
4070 l_reference_3,
4071 l_reference_4,
4072 l_reference_5,
4073 l_reference_6,
4074 l_reported_month,
4075 l_default_period_name,
4076 l_exception_category,
4077 l_accomplish_month,
4078 l_accomplish_date,
4079 l_obligation_date,
4080 l_inter_agency_flag,
4081 l_treasury_symbol,
4082 l_treasury_symbol_id,
4083 'E' ,
4084 'N',
4085 l_alc_code,
4086 --l_org_id,
4087 -1,
4088 null,
4089 l_update_type,
4090 l_type,
4091 l_gl_period_name,
4092 l_processed_flag,
4093 sysdate,
4094 FND_GLOBAL.USER_ID,
4095 sysdate,
4096 FND_GLOBAL.USER_ID,
4097 FND_GLOBAL.LOGIN_ID,
4098 l_je_header_id,
4099 l_je_line_num ) ;
4100
4101 COMMIT;
4102 EXCEPTION
4103 WHEN OTHERS THEN
4104 p_error_code := SQLCODE;
4105 p_error_msg := SQLERRM || ' -- Error in ' ||
4106 'INSERT_EXCEPTIONS procedure.';
4107 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4108 l_module_name||'.final_exception',p_error_msg);
4109 ROLLBACK;
4110 RETURN;
4111 END INSERT_EXCEPTIONS;
4112
4113
4114 -------------------------------------------------------------------------------
4115 -- PROCEDURE ASSIGN_GROUP_NAME
4116 -------------------------------------------------------------------------------
4117 -- Once the 1219 record type is assigned, based on the specified set of rules
4118 -- a group name is assigned to each record, which enables to report the amount
4119 -- against appropriate report line on the 1219/1220 reports.
4120 -- Comment - ensure that type code is stored with upper case.
4121 -------------------------------------------------------------------------------
4122
4123 PROCEDURE ASSIGN_GROUP_NAME IS
4124 l_module_name VARCHAR2(200) := g_module_name || 'ASSIGN_GROUP_NAME';
4125 BEGIN
4126 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
4127 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4128 ' Inside Assign_Group_Name ...');
4129 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4130 ' l_sf1219_type_code : '||l_sf1219_type_code);
4131 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4132 ' l_inter_agency_flag: '||l_inter_agency_flag);
4133 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4134 ' l_obligation_date : '||l_obligation_date);
4135 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4136 ' l_yr_start_date : '||l_yr_start_date);
4137 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
4138 ' l_yr_end_date : '||l_yr_end_date);
4139 END IF;
4140
4141 IF l_sf1219_type_code = 'DISBURSEMENT' AND l_inter_agency_flag = 'N'
4142 THEN
4143 l_group_name := '2103' ;
4144 ELSIF l_sf1219_type_code = 'DISBURSEMENT' AND l_inter_agency_flag = 'Y'
4145 THEN
4146 l_group_name := '2803' ;
4147 ELSIF l_sf1219_type_code = 'RECEIPT' AND l_inter_agency_flag = 'N'
4148 THEN
4149 l_group_name := '4202' ;
4150 ELSIF l_sf1219_type_code = 'RECEIPT' AND l_inter_agency_flag = 'N'
4151 THEN
4152 l_group_name := '4202' ;
4153 ELSIF l_sf1219_type_code = 'RECEIPT' AND l_inter_agency_flag = 'Y'
4154 THEN
4155 l_group_name := '2802' ;
4156 ELSIF l_sf1219_type_code = 'VOID' AND l_inter_agency_flag = 'N'
4157 AND l_obligation_date BETWEEN l_yr_start_date
4158 AND l_yr_end_date THEN
4159 l_group_name := '2103' ;
4160 ELSIF l_sf1219_type_code = 'VOID'
4161 AND l_inter_agency_flag = 'N'
4162 AND l_obligation_date < l_yr_start_date
4163 THEN
4164 l_group_name := '2102' ;
4165 ELSIF l_sf1219_type_code = 'VOID'
4166 AND l_inter_agency_flag = 'Y'
4167 AND l_obligation_date between l_yr_start_date
4168 AND l_yr_end_date THEN
4169 l_group_name := '2803' ;
4170 ELSIF l_sf1219_type_code = 'VOID'
4171 AND l_inter_agency_flag = 'Y'
4172 AND l_obligation_date < l_yr_start_date THEN
4173 l_group_name := '2802' ;
4174 ELSIF l_sf1219_type_code = 'DISBURSEMENT_REFUND'
4175 AND l_inter_agency_flag = 'N'
4176 AND (l_obligation_date BETWEEN l_yr_start_date
4177 AND l_yr_end_date) THEN
4178 l_group_name := '2103' ;
4179 ELSIF l_sf1219_type_code = 'DISBURSEMENT_REFUND'
4180 AND l_inter_agency_flag = 'N'
4181 AND l_obligation_date < l_yr_start_date THEN
4182 l_group_name := '2102' ;
4183 ELSIF l_sf1219_type_code = 'DISBURSEMENT_REFUND'
4184 AND l_inter_agency_flag = 'Y'
4185 AND (l_obligation_date BETWEEN l_yr_start_date
4186 AND l_yr_end_date) THEN
4187 l_group_name := '2803' ;
4188 ELSIF l_sf1219_type_code = 'DISBURSEMENT_REFUND'
4189 AND l_inter_agency_flag = 'Y'
4190 AND l_obligation_date < l_yr_start_date THEN
4191 l_group_name := '2802' ;
4192 ELSIF l_sf1219_type_code = 'RECEIPT_REFUND'
4193 AND l_inter_agency_flag = 'N'
4194 AND (l_obligation_date BETWEEN l_yr_start_date
4195 AND l_yr_end_date) THEN
4196 l_group_name := '4203' ;
4197 ELSIF l_sf1219_type_code = 'RECEIPT_REFUND'
4198 AND l_inter_agency_flag = 'N'
4199 AND (l_obligation_date between l_yr_start_date
4200 AND l_yr_end_date) THEN
4201 l_group_name := '4203' ;
4202 ELSIF l_sf1219_type_code = 'RECEIPT_REFUND'
4203 AND l_inter_agency_flag = 'N'
4204 AND l_obligation_date < l_yr_start_date THEN
4205 l_group_name := '4202' ;
4206 ELSIF l_sf1219_type_code = 'RECEIPT_REFUND'
4207 AND l_inter_agency_flag = 'N'
4208 AND l_obligation_date < l_yr_start_date THEN
4209 l_group_name := '4202' ;
4210 ELSIF l_sf1219_type_code = 'RECEIPT_REFUND'
4211 AND l_inter_agency_flag = 'Y'
4212 AND (l_obligation_date BETWEEN l_yr_start_date
4213 AND l_yr_end_date) THEN
4214 l_group_name := '2803' ;
4215 ELSIF l_sf1219_type_code = 'RECEIPT_REFUND'
4216 AND l_inter_agency_flag = 'Y'
4217 AND l_obligation_date < l_yr_start_date THEN
4218 l_group_name := '2802' ;
4219 ELSIF l_sf1219_type_code = 'MANUAL' THEN
4220 l_group_name := null;
4221 ELSE -- group name could not be assigned
4222 p_error_msg := 'Group Name could not be assigned ' ;
4223 p_error_code := -1 ;
4224 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4225 l_module_name||'.error1',p_error_msg);
4226 RETURN ;
4227 END IF ;
4228 EXCEPTION
4229 WHEN OTHERS THEN
4230 p_error_code := SQLCODE;
4231 p_error_msg := SQLERRM || ' -- Error in ' ||
4232 'ASSIGN_GROUP_NAME procedure.';
4233 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4234 l_module_name||'.final_exception',p_error_msg);
4235 ROLLBACK;
4236 RETURN;
4237 END ASSIGN_GROUP_NAME;
4238
4239
4240 ----------------------------------------------------------------------------
4241 -- PROCEDURE PROCESS_VOID_TRANSACTIONS
4242 ----------------------------------------------------------------------------
4243 PROCEDURE PROCESS_VOID_TRANSACTIONS IS
4244 l_module_name VARCHAR2(200) := g_module_name || 'PROCESS_VOID_TRANSACTIONS';
4245 BEGIN
4246 OPEN void_cursor ;
4247 IF sqlcode < 0 THEN
4248 p_error_code := sqlcode ;
4249 p_error_msg := sqlerrm ;
4250 RETURN;
4251 END IF;
4252 LOOP
4253 FETCH void_cursor INTO
4254 l_name,
4255 l_gl_period,
4256 l_amount,
4257 l_sf1219_type_code,
4258 l_reference_2,
4259 l_reference_3,
4260 l_reported_month,
4261 l_accomplish_date,
4262 l_obligation_date,
4263 l_inter_agency_flag,
4264 l_record_type,
4265 l_lines_exist,
4266 l_alc_code ;
4267
4268 EXIT WHEN void_cursor%NOTFOUND ;
4269
4270 BEGIN
4271 SELECT obligation_date into l_obligation_date
4272 FROM fv_refunds_voids_all
4273 WHERE type = 'VOID'
4274 AND TO_CHAR(invoice_id) = l_reference_2
4275 AND TO_CHAR(check_id) = l_reference_3
4276 AND org_id = p_def_org_id;
4277
4278 l_sf1219_type_code := 'VOID' ;
4279 l_record_type := 'A' ;
4280 l_processed_flag := 'Y' ;
4281 l_update_type := 'VOID_PAYABLE' ;
4282 l_type := 'VOID' ;
4283 l_group_name := null ;
4284
4285 -- The revised accomplish date based on Check for Void transaction,
4286 -- exception category also needs to be checked
4287
4288 set_exception_category;
4289
4290 -- l_name := 'Original Name N/A' ;
4291
4292 IF l_exception_category = 'PRIOR PERIOD' THEN
4293 INSERT_EXCEPTIONS(l_amount);
4294 ELSIF l_reported_month in ('CURRENT','CURRENT / PRIOR')
4295 AND l_exception_category = 'FUTURE PERIOD' THEN
4296 INSERT_EXCEPTIONS(l_amount);
4297 ELSIF l_reported_month = 'FUTURE'
4298 AND l_exception_category IN
4299 ('FUTURE_ACCOMPLISH','FUTURE PERIOD') THEN
4300 INSERT_EXCEPTIONS(l_amount);
4301 l_record_type := 'O';
4302 END IF;
4303
4304 -- Assign Group Name for these Voids records
4305 assign_group_name;
4306
4307 -- set the lines_exist to 'Y' if group name is assigned
4308 IF l_record_type = 'R' AND l_group_name IS NOT NULL THEN
4309 l_lines_exist := 'Y';
4310 END IF;
4311
4312 UPDATE fv_sf1219_temp
4313 SET sf1219_type_code = l_sf1219_type_code,
4314 reported_month = l_reported_month,
4315 exception_category = l_exception_category,
4316 accomplish_month = to_char(l_accomplish_date,'MMYYYY'),
4317 accomplish_date = l_accomplish_date,
4318 obligation_date = l_obligation_date,
4319 record_type = l_record_type,
4320 inter_agency_flag = l_inter_agency_flag,
4321 group_name = l_group_name,
4322 lines_exist = l_lines_exist,
4323 update_type = l_update_type,
4324 type = l_type,
4325 processed_flag = l_processed_flag
4326 WHERE reference_2 = l_reference_2
4327 AND reference_3 = l_reference_3
4328 AND name <> 'Check for Void'
4329 AND record_type = 'A';
4330
4331 DELETE fv_sf1219_temp
4332 WHERE reference_2 = l_reference_2
4333 AND reference_3 = l_reference_3
4334 AND name = 'Check for Void';
4335
4336 EXCEPTION
4337 WHEN NO_DATA_FOUND THEN
4338
4339 -- Record type of existing record is converted to E as
4340 -- it is being done as a
4341 -- mass update. In case of each record processing original
4342 -- record should be
4343 -- made 'O' and new 'E' record should be inserted.
4344
4345 UPDATE fv_sf1219_temp
4346 SET record_type = 'E',
4347 exception_category = 'VOID_MISSING_FRV',
4348 treasury_symbol = 'UNDEFINED'
4349 WHERE reference_2 = l_reference_2
4350 AND reference_3 = l_reference_3
4351 AND name <> 'Check for Void';
4352
4353 WHEN TOO_MANY_ROWS THEN
4354 p_error_code := -1 ;
4355 p_error_msg := 'Too many rows in obligation_date select' ;
4356 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4357 l_module_name||'.error1',p_error_msg);
4358 END ;
4359
4360 END LOOP ;
4361 CLOSE VOID_CURSOR;
4362 EXCEPTION
4363 WHEN OTHERS THEN
4364 p_error_code := SQLCODE;
4365 p_error_msg := SQLERRM || ' -- Error in ' ||
4366 'PROCESS_VOID_TRANSACTIONS procedure.';
4367 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4368 l_module_name||'.final_exception',p_error_msg);
4369 ROLLBACK;
4370 RETURN;
4371 END PROCESS_VOID_TRANSACTIONS;
4372
4373
4374 -------------------------------------------------------------------------------
4375 -- PROCEDURE GROUP_REPORT_LINES
4376 -------------------------------------------------------------------------------
4377 -- The GROUP_REPORT_LINES procedure is called from Before Report trigger of
4378 -- 1219/1220 report. The amount against each record of FV_SF1219_TEMP is rolled
4379 -- up into FV_SF1219_ORG_TEMP table for each org_id and line_id, which was
4380 -- assigned in the form of Group Name during MAIN_1219 procedure. This table is
4381 -- used for reporting 1219/1220. Record Type 'A', 'M' and 'N' are selected for
4382 -- reporting. If the record type is 'M', 'N' or 'R' (without group_name) report
4383 -- line information is selected from Manual Lines tables.
4384
4385 -- Additionally, sub-total and total report lines are inserted. It is assumed
4386 -- that sign would be set as per multiplication rule while displaying these
4387 -- records on the 1219/1220 report, set for each Report Line in the seed process
4388 -- itself, and just the arithmetic sum is sufficient at the time of inserting
4389 -- Total records in ORG TEMP table.
4390 -------------------------------------------------------------------------------
4391
4392 PROCEDURE GROUP_REPORT_LINES IS
4393 l_module_name VARCHAR2(200) := g_module_name || 'GROUP_REPORT_LINES';
4394 last_reported_gl_period varchar2(6) ;
4395 v_legal_entity_id number(15) ;
4396 v_alc_code ce_bank_accounts.agency_location_code%TYPE;
4397 BEGIN
4398 -- Before inserting new records, delete any previous records from
4399 -- FV_SF1219_ORG_TEMP table
4400 DELETE FROM FV_SF1219_ORG_TEMP;
4401
4402 COMMIT;
4403
4404 INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4405 SELECT p_def_org_id, fvt.alc_code,
4406 substr(fvt.group_name,1,3),
4407 sum(fvt.amount * fvr.multiplier)
4408 FROM FV_SF1219_TEMP fvt,
4409 FV_SF1219_REPORT_TEMPLATE fvr
4410 WHERE fvt.alc_code is not null
4411 AND ( fvt.record_type = 'A' OR
4412 (fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
4413 AND substr(fvt.group_name,1,3) = fvr.line_id
4414 GROUP BY fvt.alc_code, substr(fvt.group_name,1,3) ;
4415
4416 INSERT into FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4417 SELECT p_def_org_id, fvt.alc_code, fvm.line_id, sum(fvt.amount *
4418 DECODE(fvt.record_type, 'N', 1,fvr.multiplier))
4419 FROM FV_SF1219_TEMP fvt,
4420 FV_SF1219_MANUAL_LINES fvm,
4421 FV_SF1219_REPORT_TEMPLATE fvr
4422 WHERE fvt.alc_code IS NOT NULL
4423 AND ((fvt.record_type IN ('M', 'N')
4424 OR (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4425 AND fvt.temp_record_id = fvm.temp_record_id )
4426 AND fvm.line_id = fvr.line_id
4427 GROUP BY fvt.alc_code, fvm.line_id;
4428
4429 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4430 SELECT p_def_org_id, fvt.alc_code, '410' line_id, sum(fvt.amount * -1)
4431 FROM FV_SF1219_TEMP fvt
4432 WHERE fvt.alc_code is not null
4433 AND fvt.group_name is not null
4434 AND ( fvt.record_type = 'A' OR
4435 (fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
4436 GROUP BY fvt.alc_code;
4437
4438 COMMIT;
4439
4440 SELECT alc_code INTO v_alc_code
4441 FROM fv_sf1219_temp
4442 WHERE record_type = 'P' ;
4443
4444 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4445 SELECT p_def_org_id, fvam.alc_code, '100'
4446 line_id, fvam.accountability_balance
4447 FROM FV_SF1219_AUDITS fvam
4448 WHERE fvam.reported_gl_period = (
4449 select to_char(max(
4450 to_date(fvas.reported_gl_period,'MM-YYYY')),'MMYYYY')
4451 from fv_sf1219_audits fvas
4452 where fvas.alc_code = fvam.alc_code
4453 and fvas.record_type = 'B' )
4454 AND fvam.record_type = 'B'
4455 AND fvam.alc_code = DECODE(UPPER(v_alc_code),'ALL',alc_code,
4456 v_alc_code);
4457 COMMIT;
4458
4459 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4460 SELECT p_def_org_id, alc_code, '290' line_id, sum(amount)
4461 FROM FV_SF1219_ORG_TEMP
4462 WHERE line_id in ('210','211','212','234','236','237','280')
4463 GROUP BY alc_code;
4464
4465 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4466 SELECT p_def_org_id, alc_code, '300' line_id, sum(amount)
4467 FROM FV_SF1219_ORG_TEMP
4468 WHERE line_id in ('100','290')
4469 GROUP BY alc_code;
4470
4471 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4472 SELECT p_def_org_id, alc_code, '490' line_id, sum(amount)
4473 FROM FV_SF1219_ORG_TEMP
4474 WHERE line_id in ('410','420','434','436','437')
4475 GROUP BY alc_code;
4476
4477 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4478 SELECT p_def_org_id, alc_code, '500' line_id,
4479 sum(decode(line_id, '490',amount * -1, amount))
4480 FROM FV_SF1219_ORG_TEMP
4481 WHERE line_id in ('300','490')
4482 GROUP BY alc_code;
4483
4484 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4485 SELECT p_def_org_id, alc_code, '800' line_id, sum(amount)
4486 FROM FV_SF1219_ORG_TEMP
4487 WHERE line_id in ('610','620','650','700')
4488 GROUP BY alc_code;
4489
4490 INSERT INTO FV_SF1219_ORG_TEMP (org_id, alc_code, line_id, amount)
4491 SELECT p_def_org_id, alc_code, '990' line_id, sum(amount)
4492 FROM FV_SF1219_ORG_TEMP
4493 WHERE line_id in ('800','900')
4494 GROUP BY alc_code;
4495
4496 EXCEPTION
4497 WHEN OTHERS THEN
4498 p_error_code := SQLCODE;
4499 p_error_msg := SQLERRM || ' -- Error in ' ||
4500 ' GROUP_REPORT_LINES procedure.';
4501 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4502 l_module_name||'.final_exception',p_error_msg);
4503 ROLLBACK;
4504 RETURN;
4505 END GROUP_REPORT_LINES;
4506
4507
4508 -------------------------------------------------------------------------------
4509 -- PROCEDURE INSERT_AUDIT_TABLE
4510 -------------------------------------------------------------------------------
4511 -- The INSERT_AUDIT_TABLE procedure is called from After Report trigger of
4512 -- 1219/1220 report, only when report is run in Final mode. The procedure
4513 -- inserts records from FV_SF1219_TEMP table to FV_SF1219_AUDITS table, which
4514 -- have reported_month as 'CURRENT' or 'CURRENT / PRIOR', and org id is not null.
4515 -- These batches are excluded for any subsequent run.
4516 -- Also, it sets the processed_flag to 'Y' for fv_interagency_funds_all and
4517 -- fv_refunds_voids_all tables.
4518
4519 -- For this procedure p_gl_period format needs to be same as gl_period which
4520 -- is varchar2(15)
4521 -------------------------------------------------------------------------------
4522
4523 PROCEDURE INSERT_AUDIT_TABLE(v_alc_code VARCHAR2) IS
4524 l_module_name VARCHAR2(200) := g_module_name || 'INSERT_AUDIT_TABLE';
4525
4526 l2_reported_month FV_SF1219_TEMP.reported_month%TYPE ;
4527 l2_batch_id FV_SF1219_TEMP.batch_id%TYPE ;
4528 l2_reference_2 FV_SF1219_TEMP.reference_2%TYPE ;
4529 l2_reference_3 FV_SF1219_TEMP.reference_3%TYPE ;
4530 l2_inter_agency_flag FV_SF1219_TEMP.inter_agency_flag%TYPE ;
4531 l2_update_type FV_SF1219_TEMP.update_type%TYPE ;
4532 l2_type FV_SF1219_TEMP.type%TYPE ;
4533 l2_gl_period_name FV_SF1219_TEMP.gl_period_name%TYPE ;
4534 l2_processed_flag FV_SF1219_TEMP.processed_flag%TYPE ;
4535 v_supp_number NUMBER;
4536 l_reported_period VARCHAR2(6);
4537 l_end_date DATE;
4538
4539 CURSOR temp2_cursor IS
4540 SELECT batch_id,
4541 reference_2,
4542 reference_3,
4543 reported_month,
4544 inter_agency_flag,
4545 update_type,
4546 type,
4547 gl_period_name,
4548 processed_flag
4549 FROM FV_SF1219_TEMP
4550 WHERE (update_type is not null
4551 OR type is not null )
4552 AND alc_code = v_alc_code
4553 ORDER BY batch_id ;
4554 BEGIN
4555 -- Find the period for which 1219/1220 is being run
4556 BEGIN
4557 SELECT gl_period
4558 INTO p_gl_period
4559 FROM fv_sf1219_temp
4560 WHERE record_type = 'P'
4561 ORDER BY gl_period;
4562 EXCEPTION
4563 WHEN OTHERS THEN
4564 p_error_code := SQLCODE;
4565 p_error_msg := SQLERRM || ' -- Error in ' ||
4566 ' INSERT_AUDIT_TABLE procedure while finding GL period.';
4567 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4568 l_module_name||'.final_exception',p_error_msg);
4569 ROLLBACK;
4570 RETURN;
4571 END;
4572
4573 BEGIN
4574 SELECT set_of_books_id
4575 INTO p_set_bks_id
4576 FROM fv_sf1219_temp
4577 WHERE rownum = 1;
4578
4579 SELECT chart_of_accounts_id
4580 INTO flex_num
4581 FROM gl_sets_of_books
4582 WHERE set_of_books_id = p_set_bks_id;
4583
4584 SELECT end_date
4585 INTO l_end_date
4586 FROM gl_periods glp,
4587 gl_sets_of_books gsob
4588 WHERE glp.period_name = p_gl_period
4589 AND gsob.set_of_books_id = p_set_bks_id
4590 AND gsob.chart_of_accounts_id = flex_num
4591 AND glp.period_set_name = gsob.period_set_name;
4592
4593 l_reported_period := to_char(l_end_date,'MMYYYY');
4594 EXCEPTION
4595 WHEN OTHERS THEN
4596 p_error_code := SQLCODE;
4597 p_error_msg := SQLERRM || ' -- Error in ' ||
4598 ' INSERT_AUDIT_TABLE procedure while ' ||
4599 ' finding SoB, CoA and period end date.';
4600 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4601 l_module_name||'.final_exception',p_error_msg);
4602 ROLLBACK;
4603 RETURN;
4604 END;
4605
4606 -- Increment the supplement number, if the record is not found in the
4607 -- audits table then set the supplement number to 0, otherwise add 1 to it.
4608 -- If the number goes beyond 3 then print a line in the log file
4609 -- indicating that the supplement number has reached 3.
4610 SELECT NVL(MAX(supplement_number),-1) + 1
4611 INTO v_supp_number
4612 FROM fv_sf1219_audits
4613 WHERE alc_code = v_alc_code
4614 AND reported_gl_period = l_reported_period;
4615
4616 IF v_supp_number > 3
4617 THEN
4618 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_EXCEPTION,
4619 l_module_name||'.error211','Supplement number for
4620 Agency Location Code: '||v_alc_code||' has exceeded 3');
4621 v_supp_number := 3;
4622 END IF;
4623
4624 -- Records with alc_code not null and group_name not null
4625 -- are moved to Audit table in final mode.
4626 -- If any one of group_name and alc_code is null that record
4627 -- is not moved to Audit table
4628
4629 INSERT INTO fv_sf1219_audits (
4630 batch_id,
4631 sf1219_type_code,
4632 exception_category,
4633 gl_period,
4634 reported_gl_period,
4635 treasury_symbol_id,
4636 accountability_balance,
4637 org_id,
4638 record_type,
4639 creation_date,
4640 created_by,
4641 last_update_date,
4642 last_updated_by,
4643 last_update_login,
4644 je_header_id,
4645 je_line_num,
4646 inter_agency_flag,
4647 alc_code,
4648 supplement_number)
4649 SELECT fvt.batch_id,
4650 fvt.sf1219_type_code,
4651 fvt.exception_category,
4652 fvt.gl_period,
4653 fvt.reported_gl_period,
4654 fvt.treasury_symbol_id,--Added to fix Bug. 1575992
4655 null,
4656 -- l_org_id,
4657 -1,
4658 fvt.record_type,
4659 sysdate,
4660 FND_GLOBAL.USER_ID,
4661 sysdate,
4662 FND_GLOBAL.USER_ID,
4663 FND_GLOBAL.LOGIN_ID,
4664 fvt.je_header_id,
4665 fvt.je_line_num,
4666 fvt.inter_agency_flag,
4667 alc_code,
4668 v_supp_number --supplement_number
4669 FROM FV_SF1219_TEMP fvt
4670 WHERE fvt.reported_month in ('CURRENT / PRIOR','CURRENT')
4671 AND fvt.alc_code = v_alc_code --is not null
4672 AND ( (fvt.record_type = 'A' OR (fvt.record_type = 'R' AND
4673 fvt.group_name IS NOT NULL))
4674 OR ((fvt.record_type = 'M' OR
4675 (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4676 AND fvt.temp_record_id IN
4677 (SELECT temp_record_id
4678 FROM fv_sf1219_manual_lines)) ) ;
4679 COMMIT;
4680
4681
4682 -- Set processed flag to 'Y'
4683 -- It may be possible to substitue following code with table / columns instead
4684 -- of using a cursor. I am not sure at this stage, I will have to check up.
4685
4686 OPEN TEMP2_CURSOR ;
4687 LOOP
4688 FETCH temp2_cursor INTO
4689 l2_batch_id,
4690 l2_reference_2,
4691 l2_reference_3,
4692 l2_reported_month,
4693 l2_inter_agency_flag,
4694 l2_update_type,
4695 l2_type,
4696 l2_gl_period_name,
4697 l2_processed_flag ;
4698
4699 IF (temp2_cursor%NOTFOUND) THEN
4700 EXIT;
4701 END IF;
4702
4703 BEGIN
4704 IF (l2_inter_agency_flag = 'Y' AND
4705 l2_reported_month LIKE '%CURRENT%') THEN
4706
4707 UPDATE fv_interagency_funds_all
4708 SET processed_flag = 'Y',
4709 period_reported = l2_gl_period_name
4710 WHERE decode(l2_update_type, 'RECEIPT',
4711 cash_receipt_id, invoice_id)
4712 = to_number(l2_reference_2)
4713 AND processed_flag = 'N'
4714 AND org_id = p_def_org_id;
4715
4716 END IF ;
4717
4718 IF (l2_processed_flag = 'Y' AND
4719 l2_reported_month LIKE '%CURRENT%') THEN
4720
4721 UPDATE fv_refunds_voids_all
4722 SET processed_flag = 'Y',
4723 period_reported = l2_gl_period_name
4724 WHERE decode(l2_update_type, 'RECEIPT',
4725 cash_receipt_id, invoice_id)
4726 = to_number(l2_reference_2)
4727 AND type = l2_type
4728 AND nvl(check_id,0) = decode(l2_update_type,'RECEIPT',
4729 nvl(check_id,0),
4730 to_number(l2_reference_3))
4731 AND org_id = p_def_org_id;
4732
4733 END IF ;
4734
4735 IF (sqlcode < 0) THEN
4736 p_error_msg := 'fv_Sf1219_temp table update failed ' ;
4737 p_error_code := -1 ;
4738 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
4739 l_module_name||'.error1',p_error_msg);
4740 RETURN ;
4741 END IF;
4742
4743 END ;
4744
4745 END LOOP;
4746 COMMIT;
4747 CLOSE TEMP2_CURSOR;
4748
4749 -- Delete records from TEMP table which have been copied to AUDIT table. Though,
4750 -- the deletion is taking place as part of MAIN process, following delete is
4751 -- provided to prevent reporting same records again, just in case user runs the
4752 -- report without running pre-process.
4753 -- Keep only 'E' records for exception report. Exception report deletes all.
4754
4755 --DELETE FROM fv_sf1219_temp
4756 --WHERE record_type <> 'E';
4757
4758 --DELETE FROM fv_sf1219_manual_lines;
4759
4760 --DELETE FROM fv_sf1219_org_temp;
4761 --COMMIT;
4762
4763 EXCEPTION
4764 WHEN OTHERS THEN
4765 p_error_code := SQLCODE;
4766 p_error_msg := SQLERRM || ' -- Error in INSERT_AUDIT_TABLE procedure.';
4767 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4768 l_module_name||'.final_exception',p_error_msg);
4769 ROLLBACK;
4770 RETURN;
4771 END INSERT_AUDIT_TABLE;
4772
4773
4774 -----------------------------------------------------------------------------
4775 -- PROCEDURE INSERT_ACCOUNTABILITY_BALANCE
4776 -----------------------------------------------------------------------------
4777 -- The INSERT_ACCOUNTABILITY_BALANCE procedure is called from a formula
4778 -- column of the 1219/1220 report. It inserts record for closing balance
4779 -- of accountability, for each org id, which is used as opening balance
4780 -- for subsequent run.
4781 -----------------------------------------------------------------------------
4782
4783 PROCEDURE INSERT_ACCOUNTABILITY_BALANCE (p_rep_gl_period IN VARCHAR2,
4784 p_cl_balance IN NUMBER,
4785 p_alc_code IN VARCHAR2) IS
4786 l_module_name VARCHAR2(200);
4787 BEGIN
4788 -- Insert a record for closing balance
4789 -- First try to overlay the existing closing balance, if the report is already
4790 -- run earlier for the same period (latest period is derived for this purpose).
4791 -- Otherwise, insert a new record with closing balance for the org id for the
4792 -- period.
4793
4794 -- Parameter p_rep_gl_period needs to be in varchar2(6) 'MMYYYY' format.
4795
4796 l_module_name := g_module_name || 'INSERT_ACCOUNTABILITY_BALANCE';
4797
4798 UPDATE FV_SF1219_AUDITS
4799 set accountability_balance = p_cl_balance,
4800 last_update_date = sysdate,
4801 last_updated_by = FND_GLOBAL.USER_ID,
4802 last_update_login = FND_GLOBAL.LOGIN_ID
4803 where reported_gl_period = p_rep_gl_period
4804 and alc_code = p_alc_code
4805 and record_type = 'B' ;
4806
4807 IF SQL%NOTFOUND then
4808 INSERT into FV_SF1219_AUDITS (
4809 batch_id,
4810 reported_gl_period,
4811 accountability_balance,
4812 alc_code,
4813 record_type,
4814 creation_date,
4815 created_by,
4816 last_update_date,
4817 last_updated_by,
4818 last_update_login,
4819 org_id,
4820 treasury_symbol_id)
4821 values (100, -- some batch id for not null column
4822 p_rep_gl_period, -- gl period in MMYYYY format
4823 p_cl_balance, --amount against line 500 of report
4824 p_alc_code,
4825 'B',
4826 sysdate,
4827 FND_GLOBAL.USER_ID,
4828 sysdate,
4829 FND_GLOBAL.USER_ID,
4830 FND_GLOBAL.LOGIN_ID,
4831 -1, --l_org_id,
4832 -2); --This is a dummy value needed for bug# 3537243
4833
4834 end if;
4835 commit;
4836 EXCEPTION
4837 WHEN OTHERS THEN
4838 p_error_code := SQLCODE;
4839 p_error_msg := SQLERRM || ' -- Error in ' ||
4840 'INSERT_ACCOUNTABILITY_BALANCE procedure.';
4841 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
4842 l_module_name||'.final_exception',p_error_msg);
4843 ROLLBACK;
4844 RETURN;
4845 END INSERT_ACCOUNTABILITY_BALANCE;
4846
4847
4848 -----------------------------------------------------------------------------
4849 -- PROCEDURE GEN_FLAT_FILE
4850 -----------------------------------------------------------------------------
4851 PROCEDURE GEN_FLAT_FILE(v_period IN VARCHAR2,
4852 v_do_name IN VARCHAR2,
4853 v_do_tel_num IN VARCHAR2,
4854 v_alc_code IN VARCHAR2)
4855 IS
4856 l_module_name VARCHAR2(200) := g_module_name || 'GEN_FLAT_FILE';
4857
4858 i NUMBER;
4859 x_line_id fv_sf1219_report_template.line_id%TYPE;
4860 x_amt fv_sf1219_org_temp.amount%TYPE;
4861
4862 TYPE total_rec_type IS RECORD
4863 (line_id fv_sf1219_report_template.line_id%TYPE,
4864 amt fv_sf1219_org_temp.amount%TYPE);
4865
4866 TYPE total_tab_type IS TABLE OF total_rec_type INDEX BY BINARY_INTEGER;
4867
4868 total_tab total_tab_type;
4869
4870 CURSOR total_cur IS
4871 SELECT fvr.line_id v_line_id, SUM(DECODE(fvr.line_type,
4872 'A', fvo.amount,
4873 'T', fvo.amount,
4874 'B',fvo.amount,0)) v_amt
4875 FROM fv_sf1219_report_template fvr,
4876 fv_sf1219_org_temp fvo
4877 WHERE fvr.line_id = fvo.line_id
4878 AND fvo.alc_code = v_alc_code
4879 AND line_type <> 'D'
4880 GROUP BY fvr.line_id
4881 UNION
4882 SELECT line_id,0
4883 FROM fv_sf1219_report_template
4884 WHERE line_id NOT IN
4885 (SELECT line_id FROM fv_sf1219_org_temp
4886 WHERE alc_code = v_alc_code)
4887 AND line_type <> 'D'
4888 GROUP BY line_id;
4889
4890 -- Using the two select statements in the From clause
4891 -- because we need one line the goals for A, M and N records.
4892 CURSOR tc_210 IS
4893 SELECT SUM(grp_amount) group_amount, alc alc_code
4894 FROM (SELECT SUM(decode(fvt.record_type,'N', fvt.amount,
4895 fvt.amount*fvr.multiplier))
4896 grp_amount, fvt.alc_code alc
4897 FROM fv_sf1219_report_template fvr,
4898 fv_sf1219_temp fvt
4899 WHERE substr(fvt.group_name,1,3) = fvr.line_id
4900 AND (fvt.record_type IN ('A') OR
4901 (fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
4902 AND fvt. alc_code = v_alc_code
4903 AND SUBSTR(fvt.group_name,1,3) = '210'
4904 GROUP BY fvt.alc_code, fvr.line_id
4905 UNION
4906 SELECT SUM(decode(fvt.record_type,'N', fvt.amount,
4907 fvt.amount*fvr.multiplier))
4908 grp_amount, fvt.alc_code alc
4909 FROM fv_sf1219_report_template fvr,
4910 fv_sf1219_temp fvt,
4911 fv_sf1219_manual_lines fvm
4912 WHERE fvm.line_id = fvr.line_id
4913 AND fvt.temp_record_id = fvm.temp_record_id
4914 AND (fvt.record_type IN ('M','N') OR
4915 (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4916 AND fvt.alc_code = v_alc_code
4917 AND fvm.line_id = '210'
4918 GROUP BY fvt.alc_code, fvm.line_id)
4919 GROUP BY alc;
4920
4921 -- Using the two select statements in the From clause
4922 -- because we need one line the goals for A, M and N records.
4923 CURSOR tc_211_420 IS
4924 SELECT alc alc_code, l_num line_num, acc_mon accomplish_month,
4925 SUM(grp_amt) group_amount
4926 FROM (SELECT fvt.alc_code alc, SUBSTR(fvt.group_name,1,3) l_num,
4927 to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY') acc_mon,
4928 SUM(decode(fvt.record_type,'N', fvt.amount,
4929 fvt.amount*fvr.multiplier)) grp_amt
4930 FROM fv_sf1219_report_template fvr, fv_sf1219_temp fvt
4931 WHERE substr(fvt.group_name,1,3) = fvr.line_id
4932 AND (fvt.record_type IN ('A') OR
4933 (fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
4934 AND fvt.alc_code = v_alc_code
4935 AND SUBSTR(fvt.group_name,1,3) IN ('211','212','280','420')
4936 GROUP BY fvt.alc_code, SUBSTR(fvt.group_name,1,3), -- fvr.line_id,
4937 to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY')
4938 UNION
4939 SELECT fvt.alc_code alc, fvm.line_id l_num,
4940 to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY') acc_mon,
4941 SUM(decode(fvt.record_type,'N', fvt.amount,
4942 fvt.amount*fvr.multiplier)) grp_amt
4943 FROM fv_sf1219_report_template fvr,
4944 fv_sf1219_temp fvt, fv_sf1219_manual_lines fvm
4945 WHERE fvm.line_id = fvr.line_id
4946 AND fvt.temp_record_id = fvm.temp_record_id
4947 AND (fvt.record_type IN ('M','N') OR
4948 (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4949 AND fvt.alc_code = v_alc_code
4950 AND fvm.line_id IN ('211','212','280','420')
4951 GROUP BY fvt.alc_code, fvm.line_id,
4952 to_char(to_date(fvt.accomplish_month,'MMYYYY'),'MM/YY'))
4953 GROUP BY alc, l_num, acc_mon
4954 ORDER BY 2, 3;
4955
4956 -- Using the two select statements in the From clause because we need
4957 -- one line the goals for A, M and N records.
4958 CURSOR tc_1220 IS
4959 SELECT ts treasury_symbol, SUM(c2) col2_amt,
4960 SUM(c3) col3_amt, alc alc_code
4961 FROM (
4962 SELECT fvt.treasury_symbol ts,
4963 SUM(DECODE(fvt.record_type, 'A',
4964 DECODE(SUBSTR(fvt.group_name,4,1),
4965 2, fvt.amount, 0),
4966 'R', DECODE(SUBSTR(fvt.group_name,4,1), 2, fvt.amount, 0))) c2,
4967 SUM(DECODE(fvt.record_type, 'A',
4968 DECODE(SUBSTR(fvt.group_name,4,1), 3, fvt.amount*-1, 0),
4969 'R', DECODE(SUBSTR(fvt.group_name,4,1), 3, fvt.amount*-1, 0))) c3,
4970 fvt.alc_code alc
4971 FROM fv_sf1219_temp fvt
4972 WHERE (fvt.record_type = 'A' OR
4973 (fvt.record_type = 'R' AND fvt.group_name IS NOT NULL))
4974 AND fvt.alc_code = v_alc_code
4975 GROUP BY fvt.alc_code, fvt.treasury_symbol
4976 UNION
4977 SELECT fvt.treasury_symbol ts,
4978 SUM(DECODE(fvt.record_type, 'M',
4979 DECODE(fvm.column_no, 2, fvt.amount,0),
4980 'N', DECODE(fvm.column_no, 2, fvt.amount*-1,0),
4981 'R', DECODE(fvm.column_no, 2, fvt.amount,0))) c2,
4982 SUM(DECODE(fvt.record_type, 'M', DECODE(fvm.column_no, 3,
4983 fvt.amount*-1,0),
4984 'N', DECODE(fvm.column_no, 3, fvt.amount,0),
4985 'R', DECODE(fvm.column_no, 3, fvt.amount*-1,0))) c3,
4986 fvt.alc_code alc
4987 FROM fv_sf1219_temp fvt, fv_sf1219_manual_lines fvm
4988 WHERE fvm.temp_record_id = fvt.temp_record_id
4989 AND fvt.alc_code = v_alc_code
4990 AND fvm.line_id = '410'
4991 AND (fvt.record_type IN ('M','N') OR
4992 (fvt.record_type = 'R' AND fvt.group_name IS NULL))
4993 GROUP BY fvt.alc_code, fvt.treasury_symbol)
4994 GROUP BY alc, ts
4995 ORDER BY 1;
4996
4997 max_supplement_number NUMBER;
4998 v_stmt VARCHAR2(2000);
4999 v_rec_count NUMBER;
5000 old_tsymbol fv_treasury_symbols.treasury_symbol%TYPE;
5001 old_line_num NUMBER;
5002 v_line_count NUMBER;
5003 v_entry_number NUMBER;
5004 v_total_line_count NUMBER;
5005 l_end_date DATE;
5006 l_amt VARCHAR2(15);
5007 l_amt2 VARCHAR2(15);
5008 l_ts VARCHAR2(19);
5009 l_reported_period VARCHAR2(6);
5010
5011 BEGIN
5012 -- This variable will count the number of rows in the GOALS file.
5013 -- This value will be used in the Trailer Record.
5014 v_total_line_count := 0;
5015
5016 BEGIN
5017 SELECT set_of_books_id
5018 INTO p_set_bks_id
5019 FROM fv_sf1219_temp
5020 WHERE rownum = 1;
5021
5022 SELECT chart_of_accounts_id
5023 INTO flex_num
5024 FROM gl_sets_of_books
5025 WHERE set_of_books_id = p_set_bks_id;
5026
5027 SELECT end_date
5028 INTO l_end_date
5029 FROM gl_periods glp,
5030 gl_sets_of_books gsob
5031 WHERE glp.period_name = v_period
5032 AND gsob.set_of_books_id = p_set_bks_id
5033 AND gsob.chart_of_accounts_id = flex_num
5034 AND glp.period_set_name = gsob.period_set_name;
5035
5036 l_reported_period := to_char(l_end_date,'MMYYYY');
5037 EXCEPTION
5038 WHEN OTHERS THEN
5039 p_error_code := SQLCODE;
5040 p_error_msg := SQLERRM || ' -- Error in GEN_FLAT_FILE ' ||
5041 ' procedure while finding Acct Date ' ||
5042 '(End_Date of the period).';
5043 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name
5044 ||'.final_exception',p_error_msg);
5045 ROLLBACK;
5046 RETURN;
5047 END;
5048
5049
5050 BEGIN
5051 SELECT MAX(supplement_number)
5052 INTO max_supplement_number
5053 FROM fv_sf1219_audits
5054 WHERE alc_code = v_alc_code
5055 AND reported_gl_period = l_reported_period;
5056 EXCEPTION
5057 WHEN OTHERS THEN
5058 p_error_code := SQLCODE;
5059 p_error_msg := SQLERRM || ' -- Error in GEN_FLAT_FILE procedure ' ||
5060 'while finding max_supplement_number.';
5061 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5062 l_module_name||'.final_exception',p_error_msg);
5063 ROLLBACK;
5064 RETURN;
5065 END;
5066
5067 --Print the HEADER Line with following format
5068 --000.000 xxxxxxxx mm/dd/yy xxxxxxxxxxxxxxxxxxxx xxxxxxxxxxxxxx
5069 v_stmt := 'SELECT ''000.00''||'''||max_supplement_number||'''||'' ''||
5070 '''||v_alc_code||'''||'' ''||'''||
5071 to_char(l_end_date,'MM/DD/YY')||'''||'' ''||'''||
5072 to_char(SYSDATE,'MM/DD/YY')||'''||'' ''||'''||
5073 RPAD(v_do_name,20)||'''||'' ''||'''||RPAD(v_do_tel_num, 14)||
5074 RPAD(' ',10)||''''||
5075 ' FROM DUAL';
5076
5077 v_total_line_count := v_total_line_count + 1;
5078 fv_flatfiles.create_flat_file(v_stmt);
5079
5080
5081 --TOTAL lines 1.00 thru 9.90
5082 total_tab.delete;
5083
5084 i := 0;
5085
5086 FOR total in total_cur
5087 LOOP
5088 i := i +1;
5089 total_tab(i).line_id := total.v_line_id;
5090 total_tab(i).amt := total.v_amt;
5091 END LOOP;
5092
5093 FOR i IN 1..total_tab.COUNT
5094 LOOP
5095 x_line_id := total_tab(i).line_id;
5096 x_amt := total_tab(i).amt;
5097
5098 --Print the Total Lines with following format
5099 --001.000 xxxxxxxxxxxxxxx
5100 v_stmt := 'SELECT ''00''||(SUBSTR('||x_line_id||',1,1)||''.''||
5101 SUBSTR('||x_line_id||',2,2))||''0''||'' ''||
5102 replace(replace(to_char('||x_amt||',
5103 decode(sign('||x_amt||'), 1, ''0000000000000.00'', 0,
5104 ''0000000000000.00'', ''000000000000.00'')),
5105 ''.'',''''),'' '','''')|| RPAD('' '',57)
5106 FROM DUAL';
5107
5108 v_total_line_count := v_total_line_count + 1;
5109 fv_flatfiles.create_flat_file(v_stmt);
5110 END LOOP;
5111
5112
5113 -- DETAIL lines for TC 210
5114 v_rec_count := 0;
5115
5116 FOR line_210 IN tc_210
5117 LOOP
5118 v_rec_count := v_rec_count + 1;
5119
5120 SELECT replace(replace(to_char(line_210.group_amount,
5121 decode(sign(line_210.group_amount), 1,
5122 '0000000000000.00', 0,
5123 '0000000000000.00',
5124 '000000000000.00')),'.',''),' ','')
5125 INTO l_amt
5126 FROM DUAL;
5127
5128 --Print 210 Lines with following format
5129 --210.001 xxxxxxxxxxxxxxx xxxxxxxx
5130 v_stmt := 'SELECT ''210.''||'''||LPAD(v_rec_count,3,0)||
5131 '''||'' ''||'''||
5132 l_amt||'''||'' ''||'''||line_210.alc_code||''''||
5133 ' FROM DUAL';
5134
5135 v_total_line_count := v_total_line_count + 1;
5136 fv_flatfiles.create_flat_file(v_stmt);
5137 END LOOP;
5138
5139
5140 -- Detail Lines for TC 211 to TC 420 (excluding TC 410)
5141 v_rec_count := 0;
5142 old_line_num := -1;
5143
5144 FOR line_211_420 IN tc_211_420
5145 LOOP
5146 IF line_211_420.line_num <> old_line_num
5147 THEN
5148 v_rec_count := 0;
5149 END IF;
5150
5151 v_rec_count := v_rec_count + 1;
5152
5153 SELECT replace(replace(to_char(line_211_420.group_amount,
5154 decode(sign(line_211_420.group_amount), 1,
5155 '0000000000000.00', 0,
5156 '0000000000000.00',
5157 '000000000000.00')),'.',''),' ','')
5158 INTO l_amt
5159 FROM DUAL;
5160
5161 --Print 211-420 (excl. 410) Lines with following format
5162 --211.001 xxxxxxxxxxxxxxx xxxxxxxx mm/yy
5163 v_stmt := 'SELECT '''||line_211_420.line_num||'''||'||'''.'''||'||
5164 '''||LPAD(v_rec_count,3,0)||''''||
5165 '||'' ''||'||''''||l_amt||'''||'||''' '''||'||'''||
5166 line_211_420.alc_code||''''||
5167 '||'' ''||'||''''||line_211_420.accomplish_month||''''||
5168 ' FROM DUAL';
5169
5170 v_total_line_count := v_total_line_count + 1;
5171 fv_flatfiles.create_flat_file(v_stmt);
5172 old_line_num := line_211_420.line_num;
5173 END LOOP;
5174
5175
5176 -- Details Lines for TC 410 FMS 1220 Data
5177 v_line_count := 0;
5178 v_entry_number := 0;
5179 old_tsymbol := '-123';
5180
5181 FOR line_1220 IN tc_1220
5182 LOOP
5183
5184 IF old_tsymbol <> line_1220.treasury_symbol
5185 THEN
5186 v_entry_number := 0;
5187 old_tsymbol := line_1220.treasury_symbol;
5188 END IF;
5189
5190 -- If both receipt and disbursement amounts exist then print them
5191 -- as two separate lines in the 1220 bulk output
5192 IF (line_1220.col2_amt <> 0 AND line_1220.col2_amt IS NOT NULL)
5193 AND (line_1220.col3_amt <> 0 AND line_1220.col3_amt IS NOT NULL)
5194 THEN
5195
5196 v_entry_number := v_entry_number + 1;
5197 v_line_count := v_line_count + 1;
5198
5199 SELECT replace(replace(to_char(line_1220.col2_amt,
5200 decode(sign(line_1220.col2_amt), 1,
5201 '0000000000000.00', 0,
5202 '0000000000000.00',
5203 '000000000000.00')),'.',''),' ','')
5204 INTO l_amt
5205 FROM DUAL;
5206
5207 SELECT rpad(nvl(substr(replace(line_1220.treasury_symbol,'-',
5208 ''),1,19),' '),19, ' ')
5209 INTO l_ts
5210 FROM DUAL;
5211
5212 --Print 410 Lines with following format
5213 --410.001 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxx xxx
5214 v_stmt := 'SELECT ''410.''||'''||LPAD(v_line_count,3,0)||'''
5215 ||'||''' '''||
5216 '||'''||l_amt||''''||
5217 '||'' ''||''000000000000000''||'' ''||'||
5218 ''''||l_ts||'''||'||''' ''||'''||
5219 LPAD(v_entry_number,3,0)||''''||
5220 '||RPAD('' '',17)'||
5221 ' FROM DUAL';
5222
5223 v_total_line_count := v_total_line_count + 1;
5224 fv_flatfiles.create_flat_file(v_stmt);
5225
5226 v_entry_number := v_entry_number + 1;
5227 v_line_count := v_line_count + 1;
5228
5229 SELECT replace(replace(to_char(line_1220.col3_amt,
5230 decode(sign(line_1220.col3_amt), 1,
5231 '0000000000000.00', 0,
5232 '0000000000000.00',
5233 '000000000000.00')),'.',''),' ','')
5234 INTO l_amt
5235 FROM DUAL;
5236
5237 SELECT rpad(nvl(substr(replace(line_1220.treasury_symbol,'-',''),
5238 1,19),' '),19, ' ')
5239 INTO l_ts
5240 FROM DUAL;
5241
5242 --Print 410 Lines with following format
5243 --410.001 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxx xxx
5244 v_stmt := 'SELECT ''410.''||'''||LPAD(v_line_count,3,0)||
5245 '''||'||''' '''||
5246 '||''000000000000000''||'' ''||'||
5247 ''''||l_amt||'''||'' '''||
5248 '||'''||l_ts||'''||'||''' ''||'''||
5249 LPAD(v_entry_number,3,0)||''''||
5250 '||RPAD('' '',17)'||
5251 ' FROM DUAL';
5252
5253 v_total_line_count := v_total_line_count + 1;
5254 fv_flatfiles.create_flat_file(v_stmt);
5255
5256 ELSE -- If either receipt or disbursement amount exist
5257
5258 v_entry_number := v_entry_number + 1;
5259 v_line_count := v_line_count + 1;
5260
5261 SELECT replace(replace(to_char(line_1220.col2_amt,
5262 decode(sign(line_1220.col2_amt), 1, '0000000000000.00',
5263 0, '0000000000000.00', '000000000000.00')),
5264 '.',''),' ','')
5265 INTO l_amt
5266 FROM DUAL;
5267
5268 SELECT replace(replace(to_char(line_1220.col3_amt,
5269 decode(sign(line_1220.col3_amt), 1, '0000000000000.00', 0,
5270 '0000000000000.00', '000000000000.00')),'.',''),' ','')
5271 INTO l_amt2
5272 FROM DUAL;
5273
5274 SELECT rpad(nvl(substr(replace(line_1220.treasury_symbol,
5275 '-',''),1,19),' '),19, ' ')
5276 INTO l_ts
5277 FROM DUAL;
5278
5279 --Print 410 Lines with following format
5280 --410.001 xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxxxxxxxxxxxxx xxx
5281 v_stmt := 'SELECT ''410.''||'''||LPAD(v_line_count,3,0)||
5282 '''||'||''' '''||
5283 '||'''||l_amt||'''||'' '''||
5284 '||'''||l_amt2||'''||'' '''||
5285 '||'''||l_ts||'''||'||''' ''||'''||
5286 LPAD(v_entry_number,3,0)||''''||
5287 '||RPAD('' '',17)'||
5288 ' FROM DUAL';
5289
5290 v_total_line_count := v_total_line_count + 1;
5291 fv_flatfiles.create_flat_file(v_stmt);
5292 END IF;
5293 END LOOP;
5294
5295 --Print Trailer Line with following format. v_total_line_count
5296 --counts header and trailer lines too.
5297 --999.999 xxxxxxxx
5298 v_total_line_count := v_total_line_count + 1;
5299
5300 v_stmt := 'SELECT ''999.999''||'' ''||LPAD('||
5301 v_total_line_count||',8,'' '')'
5302 ||'||RPAD('' '',64)'||
5303 ' FROM DUAL';
5304
5305 fv_flatfiles.create_flat_file(v_stmt);
5306
5307 -- For the 'Final' run of the request set, delete all records
5308 -- from fv_sf1219_temp for the ALC
5309 -- but the P record and the M records that do not have any lines
5310 -- associated with them.
5311
5312 DELETE from fv_sf1219_temp t
5313 WHERE t.alc_code = v_alc_code
5314 AND EXISTS (SELECT 'X'
5315 FROM FV_SF1219_MANUAL_LINES m
5316 WHERE m.temp_record_id = t.temp_record_id
5317 AND t.record_type = 'M');
5318
5319 DELETE FROM fv_sf1219_temp
5320 WHERE alc_code = v_alc_code
5321 AND record_type NOT IN ('P', 'M');
5322
5323 -- If there are any records with record_type of M
5324 -- where report lines are not assigned and
5325 -- therefore not reported on the 1219/1220 report, they should
5326 -- also not be deleted.
5327
5328 DELETE FROM fv_sf1219_manual_lines m
5329 WHERE NOT EXISTS (SELECT 'X'
5330 FROM fv_sf1219_temp t
5331 WHERE t.temp_record_id = m.temp_record_id
5332 AND t.record_type = 'M');
5333
5334 DELETE FROM fv_sf1219_org_temp;
5335 COMMIT;
5336
5337 EXCEPTION
5338 WHEN OTHERS THEN
5339 p_error_code := SQLCODE;
5340 p_error_msg := SQLERRM || ' -- Error in GEN_FLAT_FILE procedure.';
5341 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5342 l_module_name||'.final_exception',p_error_msg);
5343 ROLLBACK;
5344 RETURN;
5345 END GEN_FLAT_FILE;
5346
5347
5348 -- Adi
5349
5350 /* PROCEDURE get_reference_column (p_entity_code IN VARCHAR2,
5351 p_batch_id IN NUMBER,
5352 p_je_header_id IN NUMBER,
5353 p_je_line_num IN NUMBER,
5354 p_reference OUT NOCOPY NUMBER,
5355 p_appl_reference OUT NOCOPY NUMBER,
5356 p_history_reference OUT NOCOPY NUMBER,
5357 p_application_id IN NUMBER ) IS
5358
5359 l_event_id NUMBER;
5360 l_ae_header_id NUMBER;
5361 l_ae_line_num NUMBER;
5362
5363
5364 BEGIN
5365
5366 IF l_je_from_sla_flag = 'Y' THEN
5367
5368 -- Get the Treasury Confirmation ID for Treasury Confirmation
5369 -- Check ID for Payments
5370 -- Receipt ID for Receivables
5371
5372 SELECT ent.source_id_int_1 ,
5373 aeh.event_id,aeh.ae_header_id,ael.ae_line_num
5374 INTO p_reference,l_event_id,l_ae_header_id,l_ae_line_num
5375 FROM xla_transaction_entities ent,
5376 xla_events evt,
5377 xla_ae_headers aeh,
5378 xla_ae_lines ael,
5379 gl_import_references gli
5380 WHERE ent.application_id =p_application_id
5381 AND ent.entity_code = p_entity_code
5382 AND ent.entity_id = evt.entity_id
5383 AND evt.event_id = aeh.event_id
5384 AND aeh.ae_header_id = ael.ae_header_id
5385 AND gli.gl_sl_link_id = ael.gl_sl_link_id
5386 AND gli.je_batch_id = p_batch_id
5387 AND gli.je_header_id = p_je_header_id
5388 AND gli.je_line_num = p_je_line_num
5389 AND ael.application_id = p_application_id;
5390
5391 -- Get the invoice/transaction
5392 -- for which the PAYMENT/RECEIPT is applied.
5393
5394 SELECT applied_to_source_id_num_1
5395 INTO p_appl_reference
5396 FROM xla_distribution_links
5397 WHERE ae_header_id = l_ae_header_id
5398 AND ae_line_num = l_ae_line_num
5399 AND application_id = p_application_id
5400 AND applied_to_application_id = p_application_id ;
5401
5402 -- Get the cash receipt history id
5403 IF p_application_id = 222 THEN
5404 SELECT cash_receipt_history_id
5405 INTO l_cash_receipt_hist_id
5406 FROM ar_cash_receipt_history_all
5407 WHERE cash_receipt_id = p_reference
5408 AND event_id = l_event_id;
5409 END IF;
5410
5411
5412
5413 END IF;
5414
5415 --EXCEPTION
5416 -- NULL;
5417
5418 END get_reference_column; */
5419
5420 END FV_1219_TRANSACTIONS ;