[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_TRX_REGISTER
Source
1 PACKAGE BODY FV_FACTS_TRX_REGISTER AS
2 /* $Header: FVFCTRGB.pls 120.69.12010000.2 2008/08/04 11:25:48 gnrajago ship $*/
3 -- l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4 g_module_name VARCHAR2(100);
5 -- -------------------------------------------------------------
6 -- GLOBAL VARIABLES
7 -- -------------------------------------------------------------
8 g_error_buf VARCHAR2(600);
9 g_error_code NUMBER := 0;
10 g_set_of_books_id NUMBER;
11 g_treasury_symbol Fv_Treasury_Symbols.treasury_symbol%TYPE;
12 g_treasury_symbol_id Fv_Treasury_Symbols.treasury_symbol_id%TYPE;
13 g_period_year Gl_Balances.period_year%TYPE;
14 g_period_num_low Gl_Balances.period_num%TYPE;
15 g_period_num_high Gl_Balances.period_num%TYPE;
16 g_from_period_name Gl_Period_Statuses.period_name%TYPE;
17 g_to_period_name Gl_Period_Statuses.period_name%TYPE;
18 g_cohort_seg_name FV_FACTS_FEDERAL_ACCOUNTS.cohort_segment_name%TYPE;
19 g_bal_segment_name VARCHAR2(25);
20 g_acct_segment_name VARCHAR2(25);
21 g_acc_value_set_id NUMBER;
22 g_adjustment_flag VARCHAR2(1);
23 g_coa_id Gl_Code_Combinations.chart_of_accounts_id%TYPE;
24 g_apps_id Fnd_Id_Flex_Structures.application_id%TYPE;
25 g_id_flex_code Fnd_Id_Flex_Structures.id_flex_code%TYPE;
26 g_currency_code Gl_Sets_Of_Books.currency_code%TYPE;
27 g_start_date Gl_Period_Statuses.start_date%TYPE;
28 g_end_date Gl_Period_Statuses.end_date%TYPE;
29 g_source VARCHAR2(25);
30 g_category VARCHAR2(25);
31 g_attributes_found VARCHAR2(1);
32 g_req_date_seg VARCHAR2(15) := NULL;
33 g_pur_order_date_seg VARCHAR2(15) := NULL;
34 g_rec_trxn_date_seg VARCHAR2(15) := NULL;
35 g_from_gl_posted_date gl_je_headers.posted_date%TYPE;
36 g_to_gl_posted_date gl_je_headers.posted_date%TYPE;
37
38
39 g_funds_count NUMBER;
40
41 TYPE segment_rec IS RECORD
42 (
43 segment VARCHAR2(10),
44 fund_value VARCHAR2(25),
45 prc_flag VARCHAR2(1),
46 prc_header_id NUMBER,
47 code_type VARCHAR2(1)
48 );
49
50 TYPE segment_tab IS TABLE OF segment_rec INDEX BY BINARY_INTEGER;
51 g_segs_array segment_tab;
52
53 --- FACTS II ATTRIBUTES--
54 g_balance_type_flag VARCHAR2(1) ;
55 g_public_law_code_flag VARCHAR2(1) ;
56 g_reimburseable_flag VARCHAR2(1) ;
57 g_bea_category_flag VARCHAR2(1) ;
58 g_advance_flag VARCHAR2(1) ;
59 g_appor_cat_flag VARCHAR2(1) ;
60 g_borrowing_source_flag VARCHAR2(1) ;
61 g_def_indef_flag VARCHAR2(1) ;
62 g_budget_function_val VARCHAR2(3) ;
63 g_legis_ind_flag VARCHAR2(1) ;
64 g_authority_type_flag VARCHAR2(1) ;
65 g_function_flag VARCHAR2(1) ;
66 g_availability_flag VARCHAR2(1) ;
67 g_def_liquid_flag VARCHAR2(1) ;
68 g_deficiency_flag VARCHAR2(1) ;
69 g_transaction_partner_val VARCHAR2(1) ;
70 g_def_indef_val VARCHAR2(1) ;
71 g_appor_cat_b_dtl VARCHAR2(3) ;
72 g_appor_cat_b_txt VARCHAR2(25) ;
73 g_public_law_code_val VARCHAR2(7) ;
74 g_appor_cat_val VARCHAR2(1) ;
75 g_authority_type_val VARCHAR2(1) ;
76 g_reimburseable_val VARCHAR2(1) ;
77 g_bea_category_val VARCHAR2(5) ;
78 g_borrowing_source_val VARCHAR2(6) ;
79 g_deficiency_val VARCHAR2(1) ;
80 g_legis_ind_val VARCHAR2(1) ;
81 g_balance_type_val VARCHAR2(1) ;
82 g_advance_type_val VARCHAR2(1) ;
83 g_transfer_ind VARCHAR2(1) ;
84 g_year_budget_auth VARCHAR2(6) ;
85 g_transfer_dept_id fv_be_trx_dtls.dept_id%TYPE ;
86 g_transfer_main_acct fv_be_trx_dtls.main_account%TYPE ;
87 g_availability_val VARCHAR2(6) ;
88 g_prn_num VARCHAR2(3);
89 g_prn_txt VARCHAR2(25);
90
91 g_facts_attributes_setup BOOLEAN ;
92 g_src_flag VARCHAR2(1);
93 -- g_fund_category VARCHAR2(1);
94 --- FACTS I ATTRIBUTES--
95
96 g_govt_non_govt_ind VARCHAR2(2);
97 g_govt_non_govt_val VARCHAR2(2);
98 g_exch_non_exch_ind VARCHAR2(1);
99 g_exch_non_exch_val VARCHAR2(1);
100 g_budget_subfunction_ind VARCHAR2(3);
101 g_budget_subfunction_val VARCHAR2(3);
102 g_cust_non_cust_ind VARCHAR2(1);
103 g_cust_non_cust_val VARCHAR2(1);
104
105
106 --------------------------------------------------------------------------------
107 PROCEDURE load_program_seg;
108 PROCEDURE get_prc_val(p_ccid IN NUMBER,
109 p_fund_value IN VARCHAR2,
110 p_catb_val OUT NOCOPY VARCHAR2,
111 p_catb_desc OUT NOCOPY VARCHAR2,
112 p_prn_val OUT NOCOPY VARCHAR2,
113 p_prn_desc OUT NOCOPY VARCHAR2);
114 PROCEDURE populate_table
115 ( p_treasury_symbol_id NUMBER ,
116 p_set_of_books_id NUMBER ,
117 p_code_combination_id NUMBER ,
118 p_fund_value VARCHAR2,
119 p_account_number VARCHAR2,
120 p_document_source VARCHAR2,
121 p_document_category VARCHAR2,
122 p_document_number VARCHAR2,
123 p_transaction_date DATE,
124 p_creation_date_time DATE,
125 p_entry_user VARCHAR2,
126 p_fed_non_fed VARCHAR2,
127 p_trading_partner VARCHAR2,
128 p_exch_non_exch VARCHAR2,
129 p_cust_non_cust VARCHAR2,
130 p_budget_subfunction VARCHAR2,
131 p_debit NUMBER,
132 p_credit NUMBER,
133 p_transfer_dept_id VARCHAR2,
134 p_transfer_main_acct VARCHAR2,
135 p_year_budget_auth VARCHAR2,
136 p_budget_function VARCHAR2,
137 p_advance_flag VARCHAR2,
138 p_cohort VARCHAR2,
139 p_begin_end VARCHAR2,
140 p_indef_def_flag VARCHAR2,
141 p_appor_cat_b_dtl VARCHAR2,
142 p_appor_cat_b_txt VARCHAR2,
143 p_prn_num VARCHAR2,
144 p_prn_txt VARCHAR2,
145 p_public_law VARCHAR2,
146 p_appor_cat_code VARCHAR2,
147 p_authority_type VARCHAR2,
148 p_transaction_partner VARCHAR2,
149 p_reimburseable_flag VARCHAR2,
150 p_bea_category VARCHAR2,
151 p_borrowing_source VARCHAR2,
152 p_def_liquid_flag VARCHAR2,
153 p_deficiency_flag VARCHAR2,
154 p_availability_flag VARCHAR2,
155 p_legislation_flag VARCHAR2,
156 p_je_line_creation_date DATE,
157 p_je_line_modified_date DATE,
158 p_je_line_period_name VARCHAR2,
159 p_gl_date DATE,
160 p_gl_posted_date DATE,
161 p_reversal_flag VARCHAR2,
162 p_sla_hdr_event_id NUMBER,
163 p_sla_hdr_creation_date DATE,
164 p_sla_entity_id NUMBER);
165 PROCEDURE GET_DOC_INFO (p_je_header_id IN Number,
166 p_je_source_name IN Varchar2,
167 p_je_category_name IN Varchar2,
168 p_name IN Varchar2,
169 p_date IN Date,
170 p_creation_date IN Date,
171 p_created_by IN Number,
172 p_reference1 IN Varchar2,
173 p_reference2 IN Varchar2,
174 p_reference3 IN Varchar2,
175 p_reference4 IN Varchar2,
176 p_reference5 IN Varchar2,
177 p_reference9 IN Varchar2,
178 p_ref2 IN Varchar2,
179 p_doc_num OUT NOCOPY Varchar2,
180 p_doc_date OUT NOCOPY Date,
181 p_doc_creation_date OUT NOCOPY Date,
182 p_doc_created_by OUT NOCOPY Number,
183 p_gl_date IN OUT NOCOPY DATE,
184 p_rec_public_law_code_col IN VARCHAR2,
185 p_gl_sl_link_id IN NUMBER,
186 p_rec_public_law_code OUT NOCOPY Varchar2,
187 p_reversed OUT NOCOPY VARCHAR2);
188 --------------------------------------------------------------------------------
189 -- PROCEDURE MAIN
190 --------------------------------------------------------------------------------
191 -- Called from following procedures:
192 -- This is called from the concurrent program to execute FACTS
193 -- transaction register process
194 -- Purpose:
195 -- This calls all subsequent procedures
196 --------------------------------------------------------------------------------
197
198 PROCEDURE MAIN(p_errbuf OUT NOCOPY VARCHAR2,
199 p_retcode OUT NOCOPY NUMBER,
200 p_set_of_books_id NUMBER,
201 p_coa_id NUMBER,
202 p_currency_code VARCHAR2,
203 p_treasury_symbol_low VARCHAR2,
204 p_treasury_symbol_high VARCHAR2,
205 p_from_period_name VARCHAR2,
206 p_to_period_name VARCHAR2,
207 p_from_gl_posted_date VARCHAR2,
208 p_to_gl_posted_date VARCHAR2,
209 p_source VARCHAR2,
210 p_category VARCHAR2,
211 p_report_id VARCHAR2,
212 p_attribute_set VARCHAR2,
213 p_output_format VARCHAR2) IS
214 l_module_name VARCHAR2(200);
215
216 CURSOR treasury_symbol_range_cur IS
217 SELECT treasury_symbol_id,
218 treasury_symbol
219 FROM fv_treasury_symbols
220 WHERE set_of_books_id = g_set_of_books_id
221 AND Treasury_symbol
222 BETWEEN NVL(p_treasury_symbol_low,treasury_symbol)
223 AND NVL(p_treasury_symbol_high,treasury_symbol)
224 ORDER BY Treasury_symbol ;
225
226 l_exists NUMBER;
227 l_req_id NUMBER;
228 l_call_status BOOLEAN;
229 l_rphase VARCHAR2(30);
230 l_rstatus VARCHAR2(30);
231 l_dphase VARCHAR2(30);
232 l_dstatus VARCHAR2(30);
233 l_message VARCHAR2(240);
234 l_count NUMBER;
235
236 l_prc_map_count NUMBER;
237
238 BEGIN
239 l_module_name := g_module_name || 'MAIN';
240 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
241 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'STARTING THE FACTS TRANSACTION REGISTER '||
242 'Main Process ...');
243 END IF;
244
245 g_set_of_books_id := p_set_of_books_id;
246 g_coa_id := p_coa_id;
247 g_currency_code := p_currency_code;
248 g_source := p_source;
249 g_category := p_category;
250 g_from_period_name := p_from_period_name;
251 g_to_period_name := p_to_period_name ;
252 g_from_gl_posted_date := NULL;
253 IF (p_from_gl_posted_date IS NOT NULL) THEN
254 g_from_gl_posted_date := FND_DATE.CANONICAL_TO_DATE(p_from_gl_posted_date);
255 ELSE
256 g_from_gl_posted_date := TO_DATE('01/01/1900', 'DD/MM/RRRR');
257 END IF;
258 g_to_gl_posted_date := NULL;
259 IF (p_to_gl_posted_date IS NOT NULL) THEN
260 g_to_gl_posted_date := TO_DATE(TO_CHAR(FND_DATE.CANONICAL_TO_DATE(p_to_gl_posted_date), 'DD/MM/RRRR')||' 23:59:59', 'DD/MM/RRRR HH24:MI:SS');
261 ELSE
262 g_to_gl_posted_date := TO_DATE('31/12/9999', 'DD/MM/RRRR');
263 END IF;
264
265
266
267
268 -- Check whether program reporting code mapping has
269 -- been done for set of books. If not, then write error
270 -- message and exit process.
271 SELECT count(*)
272 INTO l_prc_map_count
273 FROM fv_facts_prc_hdr
274 WHERE set_of_books_id = g_set_of_books_id;
275
276 IF l_prc_map_count = 0 THEN
277 g_error_code := -1;
278 g_error_buf := 'Program Reporting Code Mapping has not been done! '||
279 'Please map the Program Reporting Code and resubmit!';
280 END IF;
281
282
283 -- Get Period Year
284 IF (g_error_code = 0)
285 THEN
286 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
287 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ');
288 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'DERIVING THE PERIOD YEAR.....');
289 END IF;
290 GET_PERIOD_YEAR (p_from_period_name, p_to_period_name);
291 END IF;
292
293 -- Process Input start_date and end_date
294 IF (g_error_code = 0)
295 THEN
296 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
297 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GET THE PERIOD INFO ...');
298 END IF;
299 PROCESS_PERIOD_INFO;
300 END IF;
301
302 -- Get Account and Balancing Segment values
303 IF (g_error_code = 0)
304 THEN
305 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
306 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'GET THE QUALIFIER SEGMENTS ...');
307 END IF;
308 GET_QUALIFIER_SEGMENTS;
309 END IF;
310
311 -- Purge the data IF any for the Treasury Symbol
312 IF (g_error_code = 0)THEN
313 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
314 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PURGING EXISTING DATA OF ');
315 END IF;
316 PURGE_FACTS_TRANSACTIONS ;
317 END IF ;
318
319 -- Process Journal Lines for each Treasury Symbol
320
321 FOR treasury_symbol_range_rec IN treasury_symbol_range_cur
322 LOOP
323 EXIT WHEN treasury_symbol_range_cur%NOTFOUND;
324 g_treasury_symbol_id :=
325 treasury_symbol_range_rec.treasury_symbol_id;
326 g_treasury_symbol :=
327 treasury_symbol_range_rec.treasury_symbol;
328
329
330 IF (g_error_code = 0) THEN
331 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
332 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ');
333 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESS JOURNAL LINES ...');
334 END IF;
335 JOURNAL_PROCESS;
336 END IF;
337
338 END LOOP;
339
340 IF treasury_symbol_range_cur%ISOPEN THEN
341 CLOSE treasury_symbol_range_cur;
342 END IF;
343
344
345 IF (g_error_code <> 0 ) THEN
346 -- Check for errors
347 p_retcode := g_error_code ;
348 p_errbuf := g_error_buf ;
349 ROLLBACK;
350 RETURN ;
351 END IF;
352
353 -- Submit the RXi Report
354 BEGIN
355 SELECT count(*)
356 INTO l_count
357 FROM FV_FACTS_TRX_TEMP;
358
359 IF l_count >0 THEN
360
361 l_req_id :=
362 FND_REQUEST.SUBMIT_REQUEST ('FV','RXFVFTXR','','',FALSE,
363 'DIRECT', p_report_id,p_attribute_set, p_output_format,
364 p_set_of_books_id,
365 p_currency_code,
366 p_treasury_symbol_low ,
367 p_treasury_symbol_high,
368 p_from_period_name,
369 p_to_period_name,
370 p_source,
371 p_category);
372 COMMIT;
373
374 IF l_req_id = 0 THEN
375 p_errbuf := 'Error submitting RX Report ';
376 p_retcode := -1 ;
377 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,p_errbuf);
378 RETURN;
379 ELSE
380 -- if concurrent request submission failed then abort process
381 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
382 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
383 'Concurrent Request Id for RX Report - ' ||l_req_id);
384 END IF;
385 END IF;
386
387 -- Check status of completed concurrent program
388 -- and if complete exit
389 l_call_status := Fnd_Concurrent.Wait_For_Request(
390 l_req_id, 20, 0, l_rphase, l_rstatus,
391 l_dphase, l_dstatus, l_message);
392
393 IF (l_call_status = FALSE) THEN
394 p_errbuf := 'Cannot wait for the status of RX Report.';
395 p_retcode := 1;
396 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,p_errbuf);
397 PURGE_FACTS_TRANSACTIONS ;
398 END IF;
399
400
401 ELSE
402 p_retcode := 1;
403 p_errbuf := '** No Data Found for the Transaction Register Process **';
404 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,p_errbuf);
405 RETURN;
406 END IF;
407 END;
408
409 IF (g_error_code <> 0 )
410 THEN
411 -- Check for errors
412 p_retcode := g_error_code ;
413 p_errbuf := g_error_buf ;
414 ROLLBACK;
415 RETURN ;
416 ELSE
417 -- if facts attribute columns are not setup in the system
418 -- parameters form then complete the process with a warning.
419 IF NOT g_facts_attributes_setup
420 THEN
421 p_retcode := 1;
422 p_errbuf := 'Transaction Register Process completed with warning because the Public Law, Advance,
423 and Transfer attribute columns are not established on the Define System Parameters Form.';
424 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,p_errbuf);
425 COMMIT;
426 RETURN;
427 ELSE
428 p_retcode := 0;
429 p_errbuf := '** Transaction Register Process completed Successfully **';
430 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
431 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,p_errbuf);
432 END IF;
433 COMMIT;
434 RETURN;
435 END IF ;
436 END IF;
437
438 EXCEPTION
439 WHEN OTHERS
440 THEN
441 p_errbuf := '** Transaction Register Process Failed ** '||SQLERRM;
442 p_retcode := 2;
443 ROLLBACK;
444 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
445 END main;
446
447 -- ------------------------------------------------------------------
448 -- PROCEDURE GET_PERIOD_YEAR
449 -- ------------------------------------------------------------------
450 -- Get_Period_Year procedure is called from the Main procedure.
451 -- This procedure gets the accounting calender name(period set name)
452 -- based on the set of books parameter that is passed and then gets
453 -- the period year based on period from and period to parameters.
454 -- It then gets the start date of the from period and end date of the
455 -- to period, which are used in the Journal_Process Procedure.
456 -- ------------------------------------------------------------------
457 PROCEDURE get_period_year (p_period_from VARCHAR2,
458 p_period_to VARCHAR2)
459 IS
460 l_module_name VARCHAR2(200);
461 l_period_set_name Gl_Periods.period_set_name%TYPE;
462 BEGIN
463 l_module_name := g_module_name || 'get_period_year';
464 BEGIN
465 SELECT period_set_name
466 INTO l_period_set_name
467 FROM gl_sets_of_books
468 WHERE set_of_books_id = g_set_of_books_id;
469 EXCEPTION
470 WHEN NO_DATA_FOUND THEN
471 g_error_code := 2;
472 g_error_buf := 'Period Set name not found for set of books '
473 ||to_char(g_set_of_books_id);
474 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
475 RETURN;
476 WHEN OTHERS THEN
477 g_error_code := SQLCODE ;
478 g_error_buf := SQLERRM ||
479 ' -- Error in Get_Period_Year procedure,while getting the '
480 ||'period set name.' ;
481 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
482 RETURN;
483 END;
484
485 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
486 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD SET NAME IS '||L_PERIOD_SET_NAME);
487 END IF;
488
489 BEGIN
490 SELECT period_year,adjustment_period_flag
491 INTO g_period_year,g_adjustment_flag
492 FROM gl_periods
493 WHERE period_set_name = l_period_set_name
494 AND period_name = p_period_from;
495 EXCEPTION
496 WHEN NO_DATA_FOUND THEN
497 g_error_code := 2;
498 g_error_buf := 'Period Year not found for the set of books '
499 ||to_char(g_set_of_books_id) ||
500 ' and the period set name '||l_period_set_name;
501 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
502 RETURN;
503 WHEN OTHERS THEN
504 g_error_code := SQLCODE ;
505 g_error_buf := SQLERRM ||
506 ' -- Error in Get_Period_Year procedure,'||
507 ' while getting the period year.' ;
508 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
509 RETURN;
510 END;
511
512 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
513 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD YEAR IS '||TO_CHAR(G_PERIOD_YEAR));
514 END IF;
515
516 BEGIN -- From Period Start Date
517 SELECT start_date
518 INTO g_start_date
519 FROM gl_period_statuses
520 WHERE ledger_id = g_set_of_books_id
521 AND application_id = 101
522 AND period_year = g_period_year
523 AND period_name = p_period_from;
524 EXCEPTION
525 WHEN NO_DATA_FOUND THEN
526 g_error_code := 2;
527 g_error_buf := 'Start Date not defined for the period name '
528 ||p_period_from;
529 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
530 RETURN;
531 WHEN OTHERS THEN
532 g_error_code := SQLCODE ;
533 g_error_buf := SQLERRM ||
534 ' -- Error in Get_Period_Year procedure, '||
535 'while getting the start date for the from period '
536 ||p_period_from ;
537 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
538 RETURN;
539 END;
540
541 -- From Period Start Date
542 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
543 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PERIOD START DATE IS '||TO_CHAR(G_START_DATE, 'MM/DD/YYYY'));
544 END IF;
545 BEGIN -- To Period End Date
546 SELECT end_date
547 INTO g_end_date
548 FROM gl_period_statuses
549 WHERE ledger_id = g_set_of_books_id
550 AND application_id = 101
551 AND period_year = g_period_year
552 AND period_name = p_period_to;
553 EXCEPTION
554 WHEN NO_DATA_FOUND THEN
555 g_error_code := 2;
556 g_error_buf := 'End Date not defined for the period name '
557 ||p_period_to;
558 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
559 RETURN;
560 WHEN OTHERS THEN
561 g_error_code := SQLCODE ;
562 g_error_buf := SQLERRM ||
563 ' -- Error in Get_Period_Year procedure, '||
564 'while getting the end date for the to period '||
565 p_period_to ;
566 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
567 RETURN;
568 END; -- To Period End Date
569
570 -- Setting up the retcode
571 g_error_code := 0;
572
573 EXCEPTION
574 WHEN OTHERS THEN
575 g_error_code := SQLCODE ;
576 g_error_buf := SQLERRM ||
577 ' -- Error in Get_Period_Year procedure.' ;
578 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
579 RETURN;
580 END get_period_year;
581
582 -- -------------------------------------------------------------
583 -- PROCEDURE PRCOESS PERIOD INFO
584 -- -------------------------------------------------------------
585 -- Process_Period_Info procedure is called from the Main procedure.
586 -- This procedure loads global variables 'g_period_num_low'
587 -- and 'g_period_num_high' with the derived period num range.
588 -- -------------------------------------------------------------
589 PROCEDURE process_period_info
590 IS
591 l_module_name VARCHAR2(200);
592 BEGIN
593 l_module_name := g_module_name || 'process_period_info';
594 -- IF g_adjustment_flag = 'Y' THEN
595 -- Select Period Information for Beginning Period
596 BEGIN
597 SELECT MIN(period_num)
598 INTO g_period_num_low
599 FROM gl_period_statuses
600 WHERE period_name = g_from_period_name
601 AND application_id = 101
602 AND ledger_id = g_set_of_books_id
603 AND period_year = g_period_year;
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 g_error_code := 2;
607 g_error_buf := 'PROCESS PERIOD INFO - period_num corresponding '||
608 'to From Period Name ' || g_from_period_name ||
609 ' not found.';
610 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
611 RETURN;
612 WHEN OTHERS THEN
613 g_error_code := SQLCODE ;
614 g_error_buf := SQLERRM ||
615 'PROCESS PERIOD INFO - Error when getting '||
616 'min(period_num) from gl_period_statuses '||
617 'for From Period Name '|| g_from_period_name;
618 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
619 RETURN;
620 END;
621
622 BEGIN
623 SELECT max(period_num)
624 INTO g_period_num_high
625 FROM gl_period_statuses
626 WHERE period_name = g_to_period_name
627 AND application_id = 101
628 AND ledger_id = g_set_of_books_id
629 AND period_year = g_period_year;
630 EXCEPTION
631 WHEN NO_DATA_FOUND THEN
632 g_error_code := 2;
633 g_error_buf := 'PROCESS PERIOD INFO - period corresponding '||
634 'to To Period Name ' || g_to_period_name ||
635 ' not found.';
636 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
637 RETURN;
638 WHEN OTHERS THEN
639 g_error_code := SQLCODE ;
640 g_error_buf := SQLERRM ||
641 'PROCESS PERIOD INFO - Error when getting '||
642 'max(period_num) from gl_period_statuses for '||
643 'To Period Name '|| g_to_period_name;
644 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
645 RETURN;
646 END;
647 -- END IF;
648 -- Setting up the retcode
649 g_error_code := 0;
650 EXCEPTION
651 WHEN OTHERS THEN
652 g_error_code := SQLCODE ;
653 g_error_buf := SQLERRM ||
654 ' -- Error in Process_Period_Info procedure.' ;
655 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
656 RETURN;
657 END process_period_info;
658 -- -------------------------------------------------------------
659 -- PROCEDURE GET QUALIFIER SEGMENTS
660 -- -------------------------------------------------------------
661 -- Get_QualIFier_Segments procedure is called from the Main
662 -- procedure.
663 -- This procedure gets the accounting and the balancing segments.
664 -- -------------------------------------------------------------
665 PROCEDURE get_qualifier_segments IS
666 l_module_name VARCHAR2(200);
667 l_error_code BOOLEAN;
668 BEGIN
669 l_module_name := g_module_name || 'get_qualifier_segments';
670
671 fv_utility.get_segment_col_names
672 (
673 chart_of_accounts_id => g_coa_id,
674 acct_seg_name => g_acct_segment_name,
675 balance_seg_name => g_bal_segment_name,
676 error_code => l_error_code,
677 error_message => g_error_buf
678 );
679
680 IF (l_error_code) THEN
681 g_error_code := 2 ;
682 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
683 RETURN;
684 END IF;
685
686 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
687 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BALANCING SEGMENT IS '||G_BAL_SEGMENT_NAME);
688 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NATURAL ACCOUNTING SEGMENT IS '
689 ||g_acct_segment_name);
690 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ');
691 END IF;
692 BEGIN
693 -- Determine the Flex Value Set Id for the Acct segment
694 SELECT flex_value_set_id
695 INTO g_acc_value_set_id
696 FROM fnd_id_flex_segments
697 WHERE application_column_name = g_acct_segment_name
698 AND application_id = g_apps_id
699 AND id_flex_code = g_id_flex_code
700 AND id_flex_num = g_coa_id ;
701 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
702 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FLEX VALUE SET ID IS '||
703 to_char(g_acc_value_set_id));
704 END IF;
705 EXCEPTION
706 WHEN NO_DATA_FOUND THEN
707 g_error_code := 2 ;
708 g_error_buf := 'GET QUALIFIER SEGMENTS - flex_value_set_id not found';
709 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
710 RETURN;
711 WHEN TOO_MANY_ROWS THEN
712 g_error_code := 2 ;
713 g_error_buf := 'GET QUALIFIER SEGMENTS - More than one ' ||
714 'row returned while getting flex_value_set_id';
715 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
716 RETURN;
717 WHEN OTHERS THEN
718 g_error_code := SQLCODE;
719 g_error_buf := SQLERRM ||
720 '-- GET QUALIFIER SEGMENTS Error '||
721 'when getting acct_value_set_id';
722 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
723 RETURN;
724 END;
725
726 -- Setting up the retcode
727 g_error_code := 0;
728 EXCEPTION
729 WHEN OTHERS THEN
730 g_error_code := SQLCODE ;
731 g_error_buf := SQLERRM ||
732 ' -- Error in Get_QualIFier_Segments procedure.' ;
733 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
734 RETURN;
735 END get_qualifier_segments;
736
737
738 -- -------------------------------------------------------------------
739 -- PROCEDURE JOURNAL_PROCESS
740 -- -------------------------------------------------------------------
741 -- Journal_Process procedure is called from the Main procedure.
742 -- Its primary purpose is to derive values to populate
743 -- 'FV_FACTS_TRX_TEMP' table from the rows derived from INVOICES,
744 -- PAYMENTS etc. It uses dynamimic SQL to dynamically set
745 -- the select statement for the cursor.
746 -- It uses the argument 'p_jrnl_type' to find whether the journal
747 -- type is Invoice or payment, etc. The valid journal type values
748 -- INV-Invoice, PMT-Payment, REC-Receivable, ORD-Purchase Order
749 -- -------------------------------------------------------------------
750 PROCEDURE journal_process
751 IS
752 -- TYPE jrnl_cursor IS REF CURSOR ;
753 l_module_name VARCHAR2(200);
754 counter NUMBER;
755 l_ret_val BOOLEAN := TRUE;
756 l_jrnl VARCHAR2(250);
757 l_cat_str VARCHAR2(3000);
758 l_src VARCHAR2(250);
759 l_cat VARCHAR2(250);
760 l_jrnl_cursor INTEGER;
761 l_jrnl_select_gl VARCHAR2(3000);
762 l_jrnl_select_xla VARCHAR2(5000);
763 l_jrnl_select VARCHAR2(10000);
764 l_jrnl_att VARCHAR2(25) := NULL;
765 l_jrnl_fetch INTEGER;
766 l_exec_ret INTEGER;
767 l_vendor_id NUMBER(15);
768 l_vendor_type VARCHAR2(30);
769 l_account_number VARCHAR2(25);
770 l_sgl_acct_num VARCHAR2(25);
771 l_jrnl_att_value VARCHAR2(240);
772 l_entered_dr NUMBER;
773 l_entered_cr NUMBER;
774 l_ccid NUMBER(15);
775 l_eliminations_id VARCHAR2(150);
776 l_je_header_id NUMBER(15);
777 l_date_created DATE;
778 l_doc_num VARCHAR2(240);
779 l_doc_date DATE;
780 l_doc_creation_date DATE;
781 l_doc_created_by NUMBER(15);
782 l_creation_date DATE;
783 l_created_by NUMBER(15);
784 l_entry_user VARCHAR2(100);
785 l_fund_group NUMBER(4);
786 l_dept_id VARCHAR2(2);
787 l_bureau_id VARCHAR2(2);
788 l_bal_segment VARCHAR2(30);
789 l_amount NUMBER;
790 l_reference_1 VARCHAR2(80);
791 l_refer2 VARCHAR2(80);
792 l_reference_2 VARCHAR2(80);
793 l_reference_3 VARCHAR2(80);
794 l_reference_4 VARCHAR2(80);
795 l_reference_5 VARCHAR2(80);
796 l_reference_6 VARCHAR2(80);
797 l_reference_7 VARCHAR2(80);
798 l_reference_8 VARCHAR2(80);
799 l_reference_9 VARCHAR2(80);
800 l_reference_10 VARCHAR2(80);
801 l_gl_sl_link_id gl_je_lines.gl_sl_link_id%TYPE;
802 l_category VARCHAR2(80);
803 l_source VARCHAR2(80);
804 l_name VARCHAR2(150);
805 l_valid_flag VARCHAR2(2);
806 l_feeder_flag VARCHAR2(1);
807 l_stage NUMBER(2);
808 l_balance_type_flag FV_FACTS_ATTRIBUTES.balance_type%TYPE;
809 l_sob NUMBER(15);
810 l_coa NUMBER(15);
811 l_period_num_low NUMBER(15);
812 l_period_num_high NUMBER(15);
813 l_period_year NUMBER(15);
814 l_cohort_year VARCHAR2(10);
815 l_disbursements_flag VARCHAR2(1);
816 l_time_frame fv_treasury_symbols.time_frame%TYPE ;
817 l_financing_acct fv_facts_federal_accounts.financing_account%TYPE ;
818 l_cohort_select VARCHAR2(100) ;
819 l_cohort VARCHAR2(2) ;
820 l_cohort_num_year NUMBER;
821 l_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%TYPE;
822 l_fyr_segment_name fv_pya_fiscalyear_segment.application_column_name%TYPE;
823 l_seg_fiscal_yr fv_pya_fiscalyear_map.fyr_segment_value%type;
824 l_je_from_sla_flag gl_je_headers.je_from_sla_flag%TYPE;
825 l_source_distribution_id_num_1 xla_distribution_links.source_distribution_id_num_1%TYPE;
826 l_applied_to_source_id_num_1 xla_distribution_links.applied_to_source_id_num_1%TYPE;
827 l_applied_to_dist_id_num_1 xla_distribution_links.applied_to_dist_id_num_1%TYPE;
828 l_source_distribution_type xla_distribution_links.source_distribution_type%TYPE;
829 l_event_type_code xla_ae_headers.event_type_code%TYPE;
830 l_ar_source_id ar_distributions_all.source_id%TYPE;
831 l_ar_source_table ar_distributions_all.source_table%TYPE;
832 l_ar_source_type ar_distributions_all.source_type%TYPE;
833
834
835 l_cat_b_seg_val_set_id NUMBER;
836 l_cat_b_seg_value VARCHAR2(200);
837 l_cat_b_seg VARCHAR2(200);
838 l_cat_b_text VARCHAR2(100);
839 l_prn_num VARCHAR2(100);
840 l_prn_text VARCHAR2(100);
841 l_proj VARCHAR2(1000);
842 l_p_cbs VARCHAR2(1000);
843 l_cbs_no NUMBER;
844
845 type rec is RECORD (prog_seg VARCHAR2(30),seq NUMBER);
846 type tab is TABLE of rec index by binary_integer;
847 l_tab tab;
848 l_ctrl NUMBER;
849 l_found NUMBER:=0;
850 l_p_cbs_no NUMBER:=0;
851 l_cbs_num VARCHAR2(3);
852
853 l_exists VARCHAR2(1);
854 i NUMBER := 0;
855 l_tran_type fv_be_trx_dtls.transaction_type_id%TYPE;
856
857 l_pl_code_col VARCHAR2(25);
858 l_advance_type_col VARCHAR2(25);
859 l_tr_dept_id_col VARCHAR2(25);
860 l_tr_main_acct_col VARCHAR2(25);
861 l_pl_code VARCHAR2(150);
862 l_tr_main_acct VARCHAR2(150);
863 l_tr_dept_id VARCHAR2(150);
864 l_advance_type VARCHAR2(150);
865 l_factsii_pub_law_rec_col VARCHAR2(25);
866 l_factsii_pub_law_rec VARCHAR2(150);
867
868
869 l_je_line_creation_date DATE;
870 l_je_line_modified_date DATE;
871 l_je_line_period_name VARCHAR2(15);
872
873 l_fund_value VARCHAR2(25);
874
875 l_gl_date DATE;
876 l_gl_posted_date DATE;
877
878 l_reversal_flag VARCHAR2(1);
879
880 l_sla_hdr_event_id NUMBER;
881 l_sla_hdr_creation_date DATE;
882 l_sla_entity_id NUMBER;
883 l_account_class ra_cust_trx_line_gl_dist_all.account_class%TYPE;
884
885 BEGIN
886 l_module_name := g_module_name || 'journal_process';
887 l_sob := g_set_of_books_id;
888 l_coa := g_coa_id ;
889 l_period_num_low := g_period_num_low;
890 l_period_num_high := g_period_num_high;
891 l_period_year := g_period_year;
892 l_p_cbs :='~';
893 g_error_code := 0 ;
894 g_error_buf := NULL ;
895
896 BEGIN
897 l_jrnl_cursor := DBMS_SQL.OPEN_CURSOR;
898 EXCEPTION
899 WHEN OTHERS THEN
900 g_error_code := SQLCODE;
901 g_error_buf := SQLERRM ||
902 ' -- Error in Journal_Process'||
903 ' procedure due to Open_Cursor.';
904 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
905 RETURN;
906 END;
907 BEGIN
908 SELECT 'X', factsI_journal_attribute,
909 factsII_pub_law_code_attribute,
910 factsII_advance_type_attribute,
911 factsII_tr_main_acct_attribute,
912 factsII_tr_dept_id_attribute,
913 req_date_seg, pur_order_date_seg,
914 rec_trxn_date_seg, factsii_pub_law_rec_attribute
915 INTO l_exists, l_jrnl_att,
916 l_pl_code_col, l_advance_type_col,
917 l_tr_main_acct_col, l_tr_dept_id_col,
918 g_req_date_seg, g_pur_order_date_seg,
919 g_rec_trxn_date_seg, l_factsii_pub_law_rec_col
920 FROM fv_system_parameters;
921 IF (l_jrnl_att IS NULL) THEN
922 l_jrnl := NULL;
923 g_error_code := 1;
924 g_error_buf := 'Warning in Journal_Process procedure ' ||
925 '- Journal Trading Partner not defined on'||
926 ' System Parameter form';
927 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
928 ELSE
929 l_jrnl := ' ,gjl.' || l_jrnl_att;
930 g_error_code := 0;
931 g_error_buf := NULL;
932 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
933 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'JOURNAL ATTRIBUTE COLUMN = '
934 || l_jrnl_att);
935 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PLAW COLUMN = '||L_PL_CODE_COL);
936 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ADV TYPE COLUMN = '||L_ADVANCE_TYPE_COL);
937 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TR MAIN A/C COLUMN = '||L_TR_MAIN_ACCT_COL);
938 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TR DEPT ID COLUMN = '||L_TR_DEPT_ID_COL);
939 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REQ DATE SEG = '||G_REQ_DATE_SEG);
940 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PO DATE SEG = '||G_PUR_ORDER_DATE_SEG);
941 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REC TXN DATE SEG = '||G_REC_TRXN_DATE_SEG);
942 END IF;
943 END IF;
944
945 -- Set the global variable to false if facts
946 -- attributes columns have not been setup else set it to true.
947 IF (l_pl_code_col IS NULL OR
948 l_advance_type_col IS NULL OR
949 l_tr_main_acct_col IS NULL OR
950 l_tr_dept_id_col IS NULL)
951 THEN
952 g_facts_attributes_setup := FALSE ;
953 ELSE
954 g_facts_attributes_setup := TRUE ;
955 END IF;
956
957 EXCEPTION
958 WHEN NO_DATA_FOUND THEN
959 g_error_code := SQLCODE;
960 g_error_buf := 'Error in Journal_Process procedure - Journal '||
961 'Trading Partner and other Parameters not '||
962 'defined on System Parameter form';
963 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,g_error_buf);
964 RETURN;
965 WHEN OTHERS THEN
966 g_error_code := SQLCODE;
967 g_error_buf := SQLERRM ||
968 ' -- Error in Journal_Process procedure.' ;
969 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name,g_error_buf);
970 RETURN;
971 END;
972
973 IF (g_source IS NOT NULL)
974 THEN
975 l_src := ' AND gjh.je_source = '||''''|| g_source ||'''';
976 ELSE
977 l_src := NULL;
978 END IF;
979
980 IF (g_category IS NOT NULL)
981 THEN
982 l_cat := ' AND gjh.je_category = '||''''|| g_category ||'''';
983 ELSE
984 l_cat := NULL;
985 END IF;
986
987 -- Get cohort Info
988 GET_COHORT_INFO ;
989
990 IF g_cohort_seg_name IS NOT NULL Then
991 l_cohort_select := ', GLC.' || g_cohort_seg_name ;
992 Else
993 l_cohort_select := ' ' ;
994 End IF ;
995
996 -- Get Fiscal year segment name from fv_pya_fiscal_year_segment
997 Begin
998
999 SELECT application_column_name
1000 INTO l_fyr_segment_name
1001 FROM fv_pya_fiscalyear_segment
1002 WHERE set_of_books_id = g_set_of_books_id;
1003
1004 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1005 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'FISCAL YR SEGMENT '||L_FYR_SEGMENT_NAME);
1006 END IF;
1007
1008 Exception
1009
1010 WHEN Others THEN
1011 g_error_code := SQLCODE;
1012 g_error_buf := SQLERRM ;
1013 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.select1',g_error_buf);
1014 RETURN;
1015 End;
1016
1017 -- Load segments array table
1018 load_program_seg;
1019 IF g_error_code <> 0 THEN
1020 RETURN;
1021 END IF;
1022
1023 IF l_pl_code_col IS NOT NULL THEN
1024 l_pl_code_col := ', gjl.'||l_pl_code_col;
1025 END IF;
1026 IF l_tr_main_acct_col IS NOT NULL THEN
1027 l_tr_main_acct_col := ', gjl.'||l_tr_main_acct_col;
1028 END IF;
1029 IF l_tr_dept_id_col IS NOT NULL THEN
1030 l_tr_dept_id_col := ', gjl.'||l_tr_dept_id_col;
1031 END IF;
1032 IF l_advance_type_col IS NOT NULL THEN
1033 l_advance_type_col := ', gjl.'||l_advance_type_col;
1034 END IF;
1035
1036 l_jrnl_select_gl:=
1037 'SELECT gjl.entered_dr ENTERED_DR,
1038 gjl.entered_cr ENTERED_CR,
1039 NVL(gjl.reference_1, ''-100''),
1040 NVL(gjl.reference_2, ''-100''),
1041 NVL(gjl.reference_3, ''-100''),
1042 NVL(gjl.reference_4, ''-100''),
1043 NVL(gjl.reference_5, ''-100''),
1044 NVL(gjl.reference_6, ''-100''),
1045 NVL(gjl.reference_7, ''-100''),
1046 NVL(gjl.reference_8, ''-100''),
1047 NVL(gjl.reference_9, ''-100''),
1048 NVL(gjl.reference_10,''-100''),
1049 gjl.gl_sl_link_id,
1050 gjh.je_from_sla_flag,
1051 NULL,
1052 NULL,
1053 NULL,
1054 NULL,
1055 NULL,
1056 gjb.name' || ',
1057 glc.' || g_acct_segment_name ||
1058 ', glc.' ||l_fyr_segment_name ||','||
1059 'gjh.je_category ,
1060 gjh.je_source ,
1061 gjl.code_combination_id,
1062 gjl.je_header_id,
1063 gjl.creation_date,
1064 gjl.last_update_date,
1065 gjl.period_name,
1066 gjh.date_created,
1067 gjh.creation_date,
1068 gjh.created_by ,
1069 ffp.fund_value,
1070 gjl.effective_date,
1071 gjh.posted_date,
1072 gjl.je_header_id,
1073 gjl.creation_date,
1074 NULL '||
1075 l_jrnl ||
1076 l_cohort_select ||
1077 l_pl_code_col || l_advance_type_col ||
1078 l_tr_dept_id_col || l_tr_main_acct_col ||
1079 ' FROM gl_je_batches gjb,
1080 gl_je_headers gjh,
1081 gl_je_lines gjl,
1082 gl_code_combinations glc,
1083 fv_treasury_symbols fts,
1084 fv_fund_parameters ffp
1085 WHERE gjl.code_combination_id = glc.code_combination_id
1086 AND gjl.ledger_id = :sob_id
1087 AND glc.chart_of_accounts_id= :coa_id
1088 AND gjh.je_header_id = gjl.je_header_id
1089 AND gjh.je_batch_id = gjb.je_batch_id
1090 AND gjh.currency_code = :currency_code
1091 AND gjh.actual_flag = :actual_flag
1092 AND gjh.posted_date BETWEEN :from_posted_date AND :to_posted_date
1093 AND gjl.status = :status
1094 AND gjl.period_name IN
1095 (SELECT period_name
1096 FROM gl_period_statuses
1097 WHERE application_id = 101
1098 AND ledger_id = :sob_id
1099 AND period_num BETWEEN :period_num_low
1100 AND :period_num_high
1101 AND period_year = :period_year)
1102 AND glc.template_id IS NULL
1103 AND fts.treasury_symbol_id = :treasury_symbol_id
1104 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1105 AND glc.'||g_bal_segment_name||' = ffp.fund_value
1106 AND ffp.set_of_books_id = :sob_id
1107 AND fts.set_of_books_id = :sob_id
1108 AND NVL(gjh.je_from_sla_flag, ''N'') = ''N''
1109 '|| l_src || l_cat ;
1110
1111 l_jrnl_select_xla :=
1112 'SELECT xdl.unrounded_accounted_dr ENTERED_DR,
1113 xdl.unrounded_accounted_cr ENTERED_CR,
1114 ''-100'',
1115 ''-100'',
1116 ''-100'',
1117 ''-100'',
1118 ''-100'',
1119 ''-100'',
1120 ''-100'',
1121 ''-100'',
1122 ''-100'',
1123 ''-100'',
1124 gjl.gl_sl_link_id,
1125 gjh.je_from_sla_flag,
1126 xdl.source_distribution_id_num_1,
1127 xdl.source_distribution_type,
1128 xdl.applied_to_source_id_num_1,
1129 xdl.applied_to_dist_id_num_1,
1130 xah.event_type_code,
1131 gjb.name' || ',
1132 glc.' || g_acct_segment_name ||
1133 ', glc.' ||l_fyr_segment_name ||','||
1134 'gjh.je_category ,
1135 gjh.je_source ,
1136 gjl.code_combination_id,
1137 gjl.je_header_id,
1138 gjl.creation_date,
1139 gjl.last_update_date,
1140 gjl.period_name,
1141 gjh.date_created,
1142 gjh.creation_date,
1143 gjh.created_by ,
1144 ffp.fund_value,
1145 gjl.effective_date,
1146 gjh.posted_date,
1147 xah.event_id,
1148 xah.creation_date,
1149 xah.entity_id '||
1150 l_jrnl ||
1151 l_cohort_select ||
1152 l_pl_code_col || l_advance_type_col ||
1153 l_tr_dept_id_col || l_tr_main_acct_col ||
1154 ' FROM gl_je_batches gjb,
1155 gl_je_headers gjh,
1156 gl_je_lines gjl,
1157 gl_code_combinations glc,
1158 fv_treasury_symbols fts,
1159 fv_fund_parameters ffp,
1160 xla_ae_lines xal,
1161 xla_ae_headers xah,
1162 xla_distribution_links xdl,
1163 gl_import_references gir
1164 WHERE gjl.code_combination_id = glc.code_combination_id
1165 AND gjl.ledger_id = :sob_id
1166 AND glc.chart_of_accounts_id= :coa_id
1167 AND gjh.je_header_id = gjl.je_header_id
1168 AND gjh.je_batch_id = gjb.je_batch_id
1169 AND gjh.currency_code = :currency_code
1170 AND gjh.actual_flag = :actual_flag
1171 AND gjh.posted_date BETWEEN :from_posted_date AND :to_posted_date
1172 AND gjl.status = :status
1173 AND gjl.period_name IN
1174 (SELECT period_name
1175 FROM gl_period_statuses
1176 WHERE application_id = 101
1177 AND ledger_id = :sob_id
1178 AND period_num BETWEEN :period_num_low
1179 AND :period_num_high
1180 AND period_year = :period_year)
1181 AND glc.template_id IS NULL
1182 AND fts.treasury_symbol_id = :treasury_symbol_id
1183 AND fts.treasury_symbol_id = ffp.treasury_symbol_id
1184 AND glc.'||g_bal_segment_name||' = ffp.fund_value
1185 AND ffp.set_of_books_id = :sob_id
1186 AND fts.set_of_books_id = :sob_id
1187 AND gir.je_batch_id = gjb.je_batch_id
1188 AND gir.je_header_id = gjh.je_header_id
1189 AND gir.je_line_num = gjl.je_line_num
1190 AND xal.gl_sl_link_id = gir.gl_sl_link_id
1191 AND xal.gl_sl_link_table = gir.gl_sl_link_table
1192 AND xdl.ae_line_num = xal.ae_line_num
1193 AND xdl.ae_header_id = xal.ae_header_id
1194 AND xah.ae_header_id = xal.ae_header_id
1195 AND gjh.je_from_sla_flag = ''Y''
1196 '|| l_src || l_cat ||
1197 ' ORDER BY fund_value , ' || g_acct_segment_name ;
1198
1199 fnd_file.put_line (fnd_file.log, ':sob_id='|| l_sob);
1200 fnd_file.put_line (fnd_file.log, ':coa_id='|| l_coa);
1201 fnd_file.put_line (fnd_file.log, ':currency_code='|| g_currency_code);
1202 fnd_file.put_line (fnd_file.log, ':actual_flag='|| 'A');
1203 fnd_file.put_line (fnd_file.log, ':from_posted_date='|| g_from_gl_posted_date);
1204 fnd_file.put_line (fnd_file.log, ':to_posted_date='|| g_to_gl_posted_date);
1205 fnd_file.put_line (fnd_file.log, ':status='|| 'P');
1206 fnd_file.put_line (fnd_file.log, ':period_num_low='|| g_period_num_low);
1207 fnd_file.put_line (fnd_file.log, ':period_num_high='|| g_period_num_high);
1208 fnd_file.put_line (fnd_file.log, ':period_year='|| l_period_year);
1209 fnd_file.put_line (fnd_file.log, ':treasury_symbol_id='|| g_treasury_symbol_id);
1210
1211 BEGIN
1212 l_jrnl_select := l_jrnl_select_gl||' UNION ALL '||l_jrnl_select_xla;
1213 DBMS_SQL.PARSE(l_jrnl_cursor, l_jrnl_select, DBMS_SQL.V7);
1214 EXCEPTION
1215 WHEN OTHERS THEN
1216 g_error_code := SQLCODE;
1217 g_error_buf := SQLERRM ||
1218 ' -- Error in Journal_Process procedure due '||
1219 'to cursor Parse.';
1220 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.select1',g_error_buf);
1221 RETURN;
1222 END;
1223
1224 -- IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1225 -- FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,L_JRNL_SELECT);
1226 -- END IF;
1227
1228 -- Bind the variables
1229 dbms_sql.bind_variable(l_jrnl_cursor, ':sob_id', l_sob);
1230 dbms_sql.bind_variable(l_jrnl_cursor, ':coa_id', l_coa);
1231 dbms_sql.bind_variable(l_jrnl_cursor, ':currency_code', g_currency_code);
1232 dbms_sql.bind_variable(l_jrnl_cursor, ':actual_flag', 'A');
1233 dbms_sql.bind_variable(l_jrnl_cursor, ':from_posted_date', g_from_gl_posted_date);
1234 dbms_sql.bind_variable(l_jrnl_cursor, ':to_posted_date', g_to_gl_posted_date);
1235 dbms_sql.bind_variable(l_jrnl_cursor, ':status', 'P');
1236 dbms_sql.bind_variable(l_jrnl_cursor, ':sob_id', l_sob);
1237 dbms_sql.bind_variable(l_jrnl_cursor, ':period_num_low', g_period_num_low);
1238 dbms_sql.bind_variable(l_jrnl_cursor, ':period_num_high', g_period_num_high);
1239 dbms_sql.bind_variable(l_jrnl_cursor, ':period_year', l_period_year);
1240 dbms_sql.bind_variable(l_jrnl_cursor, ':treasury_symbol_id', g_treasury_symbol_id);
1241 dbms_sql.bind_variable(l_jrnl_cursor, ':sob_id', l_sob);
1242 dbms_sql.bind_variable(l_jrnl_cursor, ':sob_id', l_sob);
1243 dbms_sql.bind_variable(l_jrnl_cursor, ':sob_id', l_sob);
1244
1245 counter := 1;
1246 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_entered_dr);
1247 counter := counter+1;
1248 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_entered_cr);
1249 counter := counter+1;
1250 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_1, 80);
1251 counter := counter+1;
1252 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_2, 80);
1253 counter := counter+1;
1254 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_3, 80);
1255 counter := counter+1;
1256 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_4, 80);
1257 counter := counter+1;
1258 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_5, 80);
1259 counter := counter+1;
1260 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_6, 80);
1261 counter := counter+1;
1262 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter, l_reference_7, 80);
1263 counter := counter+1;
1264 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_reference_8, 80);
1265 counter := counter+1;
1266 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_reference_9, 80);
1267 counter := counter+1;
1268 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_reference_10,80);
1269 counter := counter+1;
1270 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_gl_sl_link_id);
1271 counter := counter+1;
1272 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_je_from_sla_flag, 1);
1273 counter := counter+1;
1274 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_source_distribution_id_num_1);
1275 counter := counter+1;
1276 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_source_distribution_type, 30);
1277 counter := counter+1;
1278 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_applied_to_source_id_num_1);
1279 counter := counter+1;
1280 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_applied_to_dist_id_num_1);
1281 counter := counter+1;
1282 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_event_type_code, 30);
1283 counter := counter+1;
1284 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_name, 150);
1285 counter := counter+1;
1286 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_account_number, 25);
1287 counter := counter+1;
1288 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_seg_fiscal_yr,4);
1289 counter := counter+1;
1290 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_category, 80);
1291 counter := counter+1;
1292 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_source, 80);
1293 counter := counter+1;
1294 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_ccid);
1295 counter := counter+1;
1296 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_je_header_id);
1297 counter := counter+1;
1298 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_je_line_creation_date);
1299 counter := counter+1;
1300 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_je_line_modified_date);
1301 counter := counter+1;
1302 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_je_line_period_name, 15);
1303 counter := counter+1;
1304 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_date_created);
1305 counter := counter+1;
1306 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_creation_date);
1307 counter := counter+1;
1308 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_created_by);
1309 counter := counter+1;
1310 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_fund_value,25);
1311 -- DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, 27,l_proj,90);
1312
1313 counter := counter+1;
1314 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_gl_date);
1315 counter := counter+1;
1316 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_gl_posted_date);
1317 counter := counter+1;
1318
1319 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_sla_hdr_event_id);
1320 counter := counter+1;
1321 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_sla_hdr_creation_date);
1322 counter := counter+1;
1323 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,l_sla_entity_id);
1324 counter := counter+1;
1325
1326
1327 IF (l_jrnl_att IS NOT NULL) THEN
1328 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor, counter,
1329 l_jrnl_att_value, 240);
1330 counter := counter+1;
1331 END IF;
1332
1333 IF g_cohort_seg_name IS NOT NULL Then
1334 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor,
1335 counter,l_cohort_year, 25);
1336 counter := counter+1;
1337 END IF;
1338
1339 IF l_pl_code_col IS NOT NULL Then
1340 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor,
1341 counter,l_pl_code, 150);
1342 counter := counter+1;
1343 END IF;
1344
1345 IF l_advance_type_col IS NOT NULL Then
1346 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor,
1347 counter,l_advance_type, 150);
1348 counter := counter+1;
1349 END IF;
1350
1351 IF l_tr_dept_id_col IS NOT NULL Then
1352 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor,
1353 counter,l_tr_dept_id, 150);
1354 counter := counter+1;
1355 END IF;
1356
1357 IF l_tr_main_acct_col IS NOT NULL Then
1358 DBMS_SQL.DEFINE_COLUMN(l_jrnl_cursor,
1359 counter,l_tr_main_acct, 150);
1360 counter := counter+1;
1361 END IF;
1362
1363 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1364 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SOB_ID: '|| L_SOB);
1365 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'COA_ID: '|| L_COA);
1366 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CURRENCY_CODE: '|| G_CURRENCY_CODE);
1367 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ACTUAL_FLAG: '|| 'A');
1368 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'STATUS: '|| 'P');
1369 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PERIOD_NUM_LOW: '|| G_PERIOD_NUM_LOW);
1370 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PERIOD_NUM_HIGH: '|| G_PERIOD_NUM_HIGH);
1371 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PERIOD_YEAR: '|| L_PERIOD_YEAR);
1372 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TREASURY_SYMBOL_ID: '|| G_TREASURY_SYMBOL_ID);
1373 END IF;
1374
1375 BEGIN
1376 l_exec_ret := dbms_sql.execute(l_jrnl_cursor);
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379 g_error_code := SQLCODE;
1380 g_error_buf := SQLERRM ||
1381 ' -- Error in Journal_Process procedure due '||
1382 'to cursor Execute.';
1383 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.message1',G_ERROR_BUF);
1384 RETURN;
1385 END;
1386 i := 0;
1387 LOOP
1388 -- Reset The FACTS Attributes
1389 RESET_FACTS_ATTRIBUTES ;
1390
1391 l_account_number := NULL;
1392 l_bal_segment := NULL;
1393 l_sgl_acct_num := NULL;
1394 l_fund_group := NULL;
1395 l_dept_id := NULL;
1396 l_bureau_id := NULL;
1397 l_bal_segment := NULL;
1398 l_vendor_id := NULL;
1399 l_vendor_type := NULL;
1400 l_eliminations_id := NULL;
1401 l_entered_dr := NULL;
1402 l_entered_cr := NULL;
1403 l_je_header_id := NULL;
1404 l_source := NULL;
1405 l_category := NULL;
1406 l_name := NULL;
1407 l_date_created := NULL;
1408 l_reference_1 := NULL;
1409 l_refer2 := NULL;
1410 l_reference_2 := NULL;
1411 l_reference_3 := NULL;
1412 l_reference_4 := NULL;
1413 l_reference_5 := NULL;
1414 l_reference_6 := NULL;
1415 l_reference_7 := NULL;
1416 l_reference_8 := NULL;
1417 l_reference_9 := NULL;
1418 l_reference_10 := NULL;
1419 l_doc_num := NULL;
1420 l_doc_date := NULL;
1421 l_doc_creation_date := NULL;
1422 l_doc_created_by := NULL;
1423 l_ccid := NULL;
1424 l_creation_date := NULL;
1425 l_created_by := NULL;
1426 l_entry_user := NULL;
1427 l_cat_b_seg_val_set_id := NULL;
1428 l_cat_b_seg_value := NULL;
1429 l_cat_b_text := NULL;
1430 l_prn_num := NULL;
1431 l_prn_text := NULL;
1432 l_je_line_creation_date := NULL;
1433 l_je_line_modified_date := NULL;
1434 l_je_line_period_name := NULL;
1435
1436 g_public_law_code_val := NULL;
1437 g_src_flag := NULL;
1438
1439 l_fund_value := NULL;
1440 l_gl_date := NULL;
1441 l_gl_posted_date := NULL;
1442
1443 l_sla_hdr_event_id := NULL;
1444 l_sla_hdr_creation_date := NULL;
1445 l_sla_entity_id := NULL;
1446 l_account_class := NULL;
1447
1448 l_jrnl_fetch := DBMS_SQL.FETCH_ROWS(l_jrnl_cursor);
1449
1450 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1451 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'L_JRNL_FETCH '||L_JRNL_FETCH);
1452 END IF;
1453
1454 IF (l_jrnl_fetch = 0)
1455 THEN
1456 IF (i = 0)
1457 THEN
1458 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1459 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO JOURNAL TRANSACTIONS'||
1460 ' to process for '||g_treasury_symbol||' !!!');
1461 END IF;
1462 END IF;
1463 EXIT; -- Exit the loop
1464 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
1465 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'MUST EXIT THE LOOP');
1466 END IF;
1467 END IF;
1468
1469 -- Fetch the records into variables
1470 counter := 1;
1471 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_entered_dr);
1472 counter := counter+1;
1473 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_entered_cr);
1474 counter := counter+1;
1475 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_1);
1476 counter := counter+1;
1477 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_2);
1478 counter := counter+1;
1479 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_3);
1480 counter := counter+1;
1481 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_4);
1482 counter := counter+1;
1483 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_5);
1484 counter := counter+1;
1485 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_6);
1486 counter := counter+1;
1487 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter, l_reference_7);
1488 counter := counter+1;
1489 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_reference_8);
1490 counter := counter+1;
1491 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_reference_9);
1492 counter := counter+1;
1493 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_reference_10);
1494 counter := counter+1;
1495 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_gl_sl_link_id);
1496 counter := counter+1;
1497 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_je_from_sla_flag);
1498 counter := counter+1;
1499 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_source_distribution_id_num_1);
1500 counter := counter+1;
1501 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_source_distribution_type);
1502 counter := counter+1;
1503 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_applied_to_source_id_num_1);
1504 counter := counter+1;
1505 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_applied_to_dist_id_num_1);
1506 counter := counter+1;
1507 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_event_type_code);
1508 counter := counter+1;
1509 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_name);
1510 counter := counter+1;
1511 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_account_number);
1512 counter := counter+1;
1513 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_seg_fiscal_yr);
1514 counter := counter+1;
1515 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_category);
1516 counter := counter+1;
1517 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_source);
1518 counter := counter+1;
1519 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_ccid);
1520 counter := counter+1;
1521 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_je_header_id);
1522 counter := counter+1;
1523 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_je_line_creation_date);
1524 counter := counter+1;
1525 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_je_line_modified_date);
1526 counter := counter+1;
1527 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_je_line_period_name);
1528 counter := counter+1;
1529 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_date_created);
1530 counter := counter+1;
1531 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_creation_date);
1532 counter := counter+1;
1533 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_created_by);
1534 counter := counter+1;
1535 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_fund_value);
1536 counter := counter+1;
1537 -- DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, 27,l_proj);
1538 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_gl_date);
1539 counter := counter+1;
1540 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_gl_posted_date);
1541 counter := counter+1;
1542
1543 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_sla_hdr_event_id);
1544 counter := counter+1;
1545 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_sla_hdr_creation_date);
1546 counter := counter+1;
1547 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor, counter,l_sla_entity_id);
1548 counter := counter+1;
1549
1550
1551
1552 IF (l_jrnl_att IS NOT NULL)
1553 THEN
1554 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor,
1555 counter, l_jrnl_att_value);
1556 counter := counter+1;
1557 ELSE
1558 l_jrnl_att_value := NULL;
1559 END IF;
1560
1561 IF g_cohort_seg_name IS NOT NULL THEN
1562 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor,
1563 counter,l_cohort_year);
1564 counter := counter+1;
1565 ELSE
1566 l_cohort_year := NULL;
1567 END IF;
1568
1569 IF l_pl_code_col IS NOT NULL THEN
1570 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor,
1571 counter,l_pl_code);
1572 counter := counter+1;
1573 END IF;
1574
1575 IF l_advance_type_col IS NOT NULL THEN
1576 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor,
1577 counter,l_advance_type);
1578 counter := counter+1;
1579 END IF;
1580
1581 IF l_tr_dept_id_col IS NOT NULL THEN
1582 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor,
1583 counter,l_tr_dept_id);
1584 counter := counter+1;
1585 END IF;
1586
1587 IF l_tr_main_acct_col IS NOT NULL THEN
1588 DBMS_SQL.COLUMN_VALUE(l_jrnl_cursor,
1589 counter,l_tr_main_acct);
1590 END IF;
1591
1592 l_valid_flag := 'Y';
1593 l_feeder_flag := 'Y';
1594 i := 1;
1595 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1596 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
1597 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PROCESSING ACCOUNT NUMBER - '
1598 || l_account_number);
1599 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'------------------------');
1600 END IF;
1601
1602 -- Get Fund Group, Dept_id, Bureau_code and Balancing Segment.
1603 GET_FUND_GROUP_INFO (l_ccid,
1604 l_fund_group,
1605 l_dept_id,
1606 l_bureau_id,
1607 l_bal_segment);
1608 IF (g_error_code <> 0)
1609 THEN
1610 RETURN;
1611 END IF;
1612
1613 -- Get the account or the parent account
1614 BEGIN
1615 SELECT 'X'
1616 INTO l_exists
1617 FROM FV_FACTS_ATTRIBUTES
1618 WHERE facts_acct_number = l_account_number
1619 AND set_of_books_id = g_set_of_books_id;
1620 -- AND EXISTS (SELECT 'X'
1621 -- FROM fv_facts_ussgl_accounts
1622 -- WHERE ussgl_account = l_account_number);
1623
1624 -- Account Number exists in FV_FACTS_ATTRIBUTES table
1625 -- and can be used to get FACTS attributes.
1626 -- l_sgl_acct_num := l_account_number;
1627
1628 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1629 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LOADING FACTS ATTRIBUTES '||
1630 'for the child account -'||l_account_number);
1631 END IF;
1632 LOAD_FACTS_ATTRIBUTES (l_account_number, l_bal_segment) ;
1633
1634 -- l_sgl_acct_num := Null;
1635 -- GET_SGL_PARENT(l_account_number, l_sgl_acct_num) ;
1636 EXCEPTION
1637 WHEN NO_DATA_FOUND Then
1638
1639 --Reset the SGl Account number
1640
1641 l_sgl_acct_num := Null;
1642 GET_SGL_PARENT(l_account_number, l_sgl_acct_num) ;
1643
1644 IF l_sgl_acct_num IS NOT NULL Then
1645 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1646 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LOADING FACTS ATTRIBUTES '||
1647 'for the parent account -'||l_sgl_acct_num);
1648 END IF;
1649 LOAD_FACTS_ATTRIBUTES (l_sgl_acct_num, l_bal_segment) ;
1650 END IF;
1651 END ;
1652
1653 -- Get the USSGL/Parent account
1654 BEGIN
1655 SELECT 'X'
1656 INTO l_exists
1657 FROM fv_facts_ussgl_accounts
1658 WHERE ussgl_account = l_account_number;
1659
1660 l_sgl_acct_num := l_account_number;
1661 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1662 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'CHILD ACCOUNT IS A USSGL: '|| L_SGL_ACCT_NUM);
1663 END IF;
1664 EXCEPTION
1665 WHEN NO_DATA_FOUND THEN
1666 l_sgl_acct_num := Null;
1667 GET_SGL_PARENT(l_account_number, l_sgl_acct_num);
1668 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1669 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'PARENT ACCOUNT: '|| L_SGL_ACCT_NUM);
1670 END IF;
1671 END;
1672
1673 IF (g_error_code <> 0)
1674 THEN
1675 RETURN;
1676 END IF;
1677
1678 -------------------------------------------------------------
1679 -- Deriving the Cohort Value
1680 -------------------------------------------------------------
1681 BEGIN
1682 IF l_cohort_year IS NOT NULL THEN
1683 BEGIN
1684 l_cohort_num_year := l_cohort_year;
1685 IF l_cohort_num_year < 10 THEN
1686 l_cohort_year := g_period_year;
1687 END IF;
1688
1689 EXCEPTION
1690 WHEN INVALID_NUMBER THEN
1691 l_cohort_year := g_period_year;
1692 WHEN VALUE_ERROR THEN
1693 l_cohort_year := g_period_year;
1694 END;
1695
1696
1697 IF (LENGTH(l_cohort_year) > 2) THEN
1698 l_cohort := substr(l_cohort_year,3,2);
1699 ELSE
1700 l_cohort := substr(l_cohort_year,1,2);
1701 END IF;
1702 END IF;
1703 END ;
1704
1705 ------------------------------------------------------------
1706 -- Deriving the Category Text and Sequence
1707 -------------------------------------------------------------
1708 IF g_appor_cat_val IN ('A', 'B') THEN
1709 get_prc_val(l_ccid, l_fund_value,
1710 l_cbs_num, l_cat_b_text,l_prn_num,l_prn_text);
1711
1712
1713 /* -- 2005 FACTS II Enhancemnt to include category C
1714 ELSIF g_appor_cat_val = 'C' THEN
1715 l_cat_b_text := 'Default Cat B Code';
1716 l_cbs_num := '000';
1717 l_prn_num := '000';
1718 l_prn_text := 'Default PRN Code'; */
1719
1720 ELSE
1721 l_cat_b_text :=' ';
1722 l_cbs_num := '';
1723 l_prn_num := '';
1724 l_prn_text := '';
1725
1726 END IF;
1727 /*
1728 -------------------------------------------------
1729 -- Default the Reporting codes when the
1730 -- Apportionment Category Code is N
1731 -------------------------------------------------
1732 IF NVL(g_appor_cat_flag,'N') = 'N' THEN
1733 IF g_fund_category IN ('A','S','B','T','R','C') THEN
1734
1735 l_cat_b_text := 'Default Cat B Code';
1736 l_cbs_num := '000';
1737 l_prn_num := '000';
1738 l_prn_text := 'Default PRN Code';
1739
1740
1741 IF ( FND_LOG.LEVEL_STATEMENT >=
1742 FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1743 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1744 l_module_name, 'Defaulting the Reporting'
1745 ||'codes as the apportionment '
1746 ||'Category flag is N ') ;
1747 End If ;
1748
1749 END IF;
1750
1751 END IF; */
1752
1753
1754 ---- End FACTS Trial Balance CBT code.
1755
1756 IF (l_je_from_sla_flag = 'Y') THEN
1757 IF (l_source = 'Payables' AND l_category <> 'Treasury Confirmation') THEN
1758 IF (l_source_distribution_type IN ( 'AP_INV_DIST', 'AP_PREPAY')) THEN
1759 BEGIN
1760 SELECT aid.invoice_id,
1761 aid.distribution_line_number
1762 INTO l_reference_2,
1763 l_reference_8
1764 FROM ap_invoice_distributions_all aid
1765 WHERE aid.invoice_distribution_id = l_source_distribution_id_num_1;
1766 EXCEPTION
1767 WHEN NO_DATA_FOUND THEN
1768 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(1) '||l_source_distribution_id_num_1);
1769 END;
1770 ELSIF (l_source_distribution_type IN ('AP_PMT_DIST')) THEN
1771 BEGIN
1772 SELECT aid.invoice_id,
1773 aid.distribution_line_number
1774 INTO l_reference_2,
1775 l_reference_8
1776 FROM ap_invoice_distributions_all aid
1777 WHERE aid.invoice_distribution_id = l_applied_to_dist_id_num_1;
1778 EXCEPTION
1779 WHEN NO_DATA_FOUND THEN
1780 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(1a) '||l_applied_to_dist_id_num_1);
1781 END;
1782
1783 BEGIN
1784 SELECT aip.check_id,
1785 aid.invoice_id,
1786 aid.accounting_date
1787 INTO l_reference_3,
1788 l_reference_4,
1789 l_reference_6
1790 FROM ap_payment_hist_dists aphd,
1791 ap_invoice_distributions_all aid,
1792 ap_invoice_payments_all aip
1793 WHERE aphd.payment_hist_dist_id = l_source_distribution_id_num_1
1794 AND aid.invoice_distribution_id = aphd.invoice_distribution_id
1795 AND aip.invoice_payment_id = aphd.invoice_payment_id;
1796 EXCEPTION
1797 WHEN NO_DATA_FOUND THEN
1798 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(1b) '||l_source_distribution_id_num_1);
1799 END;
1800
1801 END IF;
1802 ELSIF (l_source = 'Purchasing' AND l_category = 'Purchases') THEN
1803 IF (l_source_distribution_type ='PO_DISTRIBUTIONS_ALL') THEN
1804 l_reference_1 := 'PO';
1805 l_reference_3 := l_source_distribution_id_num_1;
1806 BEGIN
1807 SELECT poh.po_header_id,
1808 poh.segment1
1809 INTO l_reference_2,
1810 l_reference_4
1811 FROM po_distributions_all pod,
1812 po_headers_all poh
1813 WHERE pod.po_distribution_id = l_source_distribution_id_num_1
1814 AND pod.po_header_id = poh.po_header_id;
1815 EXCEPTION
1816 WHEN NO_DATA_FOUND THEN
1817 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(2) '||l_source_distribution_id_num_1);
1818 END;
1819 ELSIF (l_source_distribution_type ='PO_REQ_DISTRIBUTIONS_ALL') THEN
1820 l_reference_1 := 'REQ';
1821 l_reference_3 := l_source_distribution_id_num_1;
1822 BEGIN
1823 SELECT poh.requisition_header_id,
1824 poh.segment1
1825 INTO l_reference_2,
1826 l_reference_4
1827 FROM po_req_distributions_all pod,
1828 po_requisition_headers_all poh,
1829 po_requisition_lines_all pol
1830 WHERE pod.distribution_id = l_source_distribution_id_num_1
1831 AND pol.requisition_header_id = poh.requisition_header_id
1832 AND pod.requisition_line_id = pol.requisition_line_id;
1833 EXCEPTION
1834 WHEN NO_DATA_FOUND THEN
1835 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(3) '||l_source_distribution_id_num_1);
1836 END;
1837 END IF;
1838 ELSIF (l_source = 'Purchasing' AND l_category = 'Requisitions') THEN
1839 l_reference_1 := 'REQ';
1840 l_reference_3 := l_source_distribution_id_num_1;
1841 BEGIN
1842 SELECT poh.requisition_header_id,
1843 poh.segment1
1844 INTO l_reference_2,
1845 l_reference_4
1846 FROM po_req_distributions_all pod,
1847 po_requisition_headers_all poh,
1848 po_requisition_lines_all pol
1849 WHERE pod.distribution_id = l_source_distribution_id_num_1
1850 AND pol.requisition_header_id = poh.requisition_header_id
1851 AND pod.requisition_line_id = pol.requisition_line_id;
1852 EXCEPTION
1853 WHEN NO_DATA_FOUND THEN
1854 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(4) '||l_source_distribution_id_num_1);
1855 END;
1856 ELSIF (l_source = 'Budgetary Transaction') THEN
1857 IF (l_source_distribution_type ='FV_TREASURY_CONFIRMATIONS_ALL') THEN
1858 l_source := 'Payables';
1859 l_category := 'Treasury Confirmation';
1860 l_reference_1 := l_applied_to_source_id_num_1;
1861 IF (l_event_type_code = 'TREASURY_VOID') THEN
1862 l_name := 'VOID '||l_name;
1863 END IF;
1864 BEGIN
1865
1866 SELECT aip.check_id,
1867 aid.invoice_id,
1868 aid.accounting_date
1869 INTO l_reference_3,
1870 l_reference_4,
1871 l_reference_6
1872 FROM ap_payment_hist_dists aphd,
1873 ap_invoice_distributions_all aid,
1874 ap_invoice_payments_all aip
1875 WHERE aphd.payment_hist_dist_id = l_source_distribution_id_num_1
1876 AND aid.invoice_distribution_id = aphd.invoice_distribution_id
1877 AND aip.invoice_payment_id = aphd.invoice_payment_id;
1878 EXCEPTION
1879 WHEN NO_DATA_FOUND THEN
1880 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(5) '||l_source_distribution_id_num_1);
1881 END;
1882 ELSE
1883 l_reference_1 := l_source_distribution_id_num_1;
1884 END IF;
1885 ELSIF (l_source = 'Cost Management' AND l_category = 'Receiving') THEN
1886 l_source := 'Purchasing';
1887 l_reference_1 := 'PO';
1888 l_reference_3 := l_source_distribution_id_num_1;
1889 BEGIN
1890 SELECT poh.po_header_id,
1891 poh.segment1
1892 INTO l_reference_2,
1893 l_reference_4
1894 FROM po_distributions_all pod,
1895 po_headers_all poh
1896 WHERE pod.po_distribution_id = l_applied_to_dist_id_num_1
1897 AND pod.po_header_id = poh.po_header_id;
1898 EXCEPTION
1899 WHEN NO_DATA_FOUND THEN
1900 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(6a) '||l_applied_to_dist_id_num_1);
1901 END;
1902 BEGIN
1903 SELECT rcv_transaction_id
1904 INTO l_reference_5
1905 FROM rcv_receiving_sub_ledger
1906 WHERE rcv_sub_ledger_id = l_source_distribution_id_num_1;
1907 EXCEPTION
1908 WHEN NO_DATA_FOUND THEN
1909 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(6b) '||l_source_distribution_id_num_1);
1910 END;
1911 ELSIF (l_source = 'Receivables' AND l_category = 'Receipts') THEN
1912 BEGIN
1913 fnd_file.put_line (fnd_file.log, 'l_source_distribution_id_num_1='||l_source_distribution_id_num_1);
1914 SELECT source_id,
1915 source_table,
1916 source_type
1917 INTO l_ar_source_id,
1918 l_ar_source_table,
1919 l_ar_source_type
1920 FROM ar_distributions_all
1921 WHERE line_id = l_source_distribution_id_num_1;
1922 fnd_file.put_line (fnd_file.log, 'l_ar_source_id='||l_ar_source_id);
1923 fnd_file.put_line (fnd_file.log, 'l_ar_source_table='||l_ar_source_table);
1924 fnd_file.put_line (fnd_file.log, 'l_ar_source_type='||l_ar_source_type);
1925 EXCEPTION
1926 WHEN NO_DATA_FOUND THEN
1927 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7a) '||l_source_distribution_id_num_1);
1928 END;
1929 IF (l_ar_source_table = 'RA') THEN
1930 BEGIN
1931 l_reference_2 := l_ar_source_id;
1932 SELECT receipt_number,
1933 --hca.party_id
1934 hca.cust_account_id
1935 INTO l_reference_4,
1936 l_reference_7
1937 FROM ar_receivable_applications_all ara,
1938 ar_cash_receipts_all acr,
1939 hz_cust_site_uses_all hcsu,
1940 hz_cust_acct_sites_all hcas,
1941 hz_cust_accounts hca
1942 WHERE ara.receivable_application_id = l_ar_source_id
1943 AND ara.cash_receipt_id = acr.cash_receipt_id
1944 AND hcsu.site_use_id = acr.customer_site_use_id
1945 AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
1946 AND hca.cust_account_id = hcas.cust_account_id;
1947
1948 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
1949 fnd_file.put_line (fnd_file.log, 'l_reference_7='||l_reference_7);
1950 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
1951
1952 l_reference_5 := l_reference_4;
1953 l_category := 'Trade Receipts';
1954 EXCEPTION
1955 WHEN NO_DATA_FOUND THEN
1956 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7b) '||l_applied_to_dist_id_num_1);
1957 END;
1958 ELSIF (l_ar_source_table = 'CRH') THEN
1959 l_reference_2 := l_ar_source_id;
1960 BEGIN
1961 SELECT receipt_number,
1962 --hca.party_id
1963 hca.cust_account_id
1964 INTO l_reference_4,
1965 l_reference_7
1966 FROM ar_cash_receipt_history_all ara,
1967 ar_cash_receipts_all acr,
1968 hz_cust_site_uses_all hcsu,
1969 hz_cust_acct_sites_all hcas,
1970 hz_cust_accounts hca
1971 WHERE ara.cash_receipt_history_id = l_ar_source_id
1972 AND ara.cash_receipt_id = acr.cash_receipt_id
1973 AND hcsu.site_use_id = acr.customer_site_use_id
1974 AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
1975 AND hca.cust_account_id = hcas.cust_account_id;
1976
1977 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
1978 fnd_file.put_line (fnd_file.log, 'l_reference_7='||l_reference_7);
1979 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
1980
1981 l_reference_5 := l_reference_4;
1982 l_category := 'Trade Receipts';
1983 EXCEPTION
1984 WHEN NO_DATA_FOUND THEN
1985 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7c) '||l_applied_to_dist_id_num_1);
1986 END;
1987 ELSIF (l_ar_source_table = 'ADJ') THEN
1988 l_reference_2 := l_ar_source_id;
1989 BEGIN
1990 SELECT receipt_number,
1991 --hca.party_id
1992 hca.cust_account_id
1993 INTO l_reference_4,
1994 l_reference_7
1995 FROM ar_adjustments_all ara,
1996 ar_cash_receipts_all acr,
1997 hz_cust_site_uses_all hcsu,
1998 hz_cust_acct_sites_all hcas,
1999 hz_cust_accounts hca
2000 WHERE ara.adjustment_id = l_ar_source_id
2001 AND ara.associated_cash_receipt_id = acr.cash_receipt_id
2002 AND hcsu.site_use_id = acr.customer_site_use_id
2003 AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
2004 AND hca.cust_account_id = hcas.cust_account_id;
2005
2006 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
2007 fnd_file.put_line (fnd_file.log, 'l_reference_7='||l_reference_7);
2008 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
2009
2010 l_reference_5 := l_reference_4;
2011 l_category := 'Trade Receipts';
2012 EXCEPTION
2013 WHEN NO_DATA_FOUND THEN
2014 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7d) '||l_applied_to_dist_id_num_1);
2015 END;
2016 ELSIF (l_ar_source_table = 'MCD') THEN
2017 l_reference_2 := l_ar_source_id;
2018 BEGIN
2019 SELECT receipt_number,
2020 --hca.party_id
2021 hca.cust_account_id
2022 INTO l_reference_4,
2023 l_reference_7
2024 FROM ar_misc_cash_distributions_all ara,
2025 ar_cash_receipts_all acr,
2026 hz_cust_site_uses_all hcsu,
2027 hz_cust_acct_sites_all hcas,
2028 hz_cust_accounts hca
2029 WHERE ara.misc_cash_distribution_id = l_ar_source_id
2030 AND ara.cash_receipt_id = acr.cash_receipt_id
2031 AND hcsu.site_use_id = acr.customer_site_use_id
2032 AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
2033 AND hca.cust_account_id = hcas.cust_account_id;
2034
2035 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
2036 fnd_file.put_line (fnd_file.log, 'l_reference_7='||l_reference_7);
2037 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
2038
2039 l_reference_5 := l_reference_4;
2040 l_category := 'Trade Receipts';
2041 EXCEPTION
2042 WHEN NO_DATA_FOUND THEN
2043 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7e) '||l_applied_to_dist_id_num_1);
2044 END;
2045 ELSIF (l_ar_source_table = 'TH') THEN
2046 l_reference_2 := l_ar_source_id;
2047 BEGIN
2048 SELECT rcth.trx_number,
2049 rcth.bill_to_customer_id
2050 INTO l_reference_4,
2051 l_reference_7
2052 FROM ar_transaction_history_all ara,
2053 ra_customer_trx_all rcth
2054 WHERE ara.transaction_history_id = l_ar_source_id
2055 AND ara.customer_trx_id = rcth.customer_trx_id;
2056
2057 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
2058 fnd_file.put_line (fnd_file.log, 'l_reference_7='||l_reference_7);
2059 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
2060
2061 l_reference_5 := l_reference_4;
2062 l_category := 'Trade Receipts';
2063 EXCEPTION
2064 WHEN NO_DATA_FOUND THEN
2065 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7f) '||l_applied_to_dist_id_num_1);
2066 END;
2067 END IF;
2068 ELSIF (l_source = 'Receivables' AND
2069 (l_category = 'Sales Invoices' OR
2070 l_category = 'Debit Memos')
2071 ) THEN
2072 BEGIN
2073 --Bug 7121539
2074 --customer_trx_line_id will be null in ra_cust_trx_line_gl_dist_all
2075 --for account class 'REC', hence using customer_trx_id to get
2076 --details instead of customer_trx_line_id for these distributions
2077 SELECT account_class
2078 INTO l_account_class
2079 FROM ra_cust_trx_line_gl_dist_all
2080 WHERE cust_trx_line_gl_dist_id = l_source_distribution_id_num_1;
2081
2082 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2083 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module_name,
2084 'Account class: '||l_account_class);
2085 END IF;
2086
2087 IF l_account_class <> 'REC' THEN
2088 SELECT rcth.trx_number,
2089 rcth.bill_to_customer_id,
2090 rcth.customer_trx_id
2091 INTO l_reference_4,
2092 l_reference_7,
2093 l_reference_2
2094 FROM ra_cust_trx_line_gl_dist_all rctgl,
2095 ra_customer_trx_lines_all rctl,
2096 ra_customer_trx_all rcth
2097 WHERE rctgl.cust_trx_line_gl_dist_id = l_source_distribution_id_num_1
2098 AND rctl.customer_trx_line_id = rctgl.customer_trx_line_id
2099 AND rcth.customer_trx_id = rctl.customer_trx_id;
2100 ELSE
2101 SELECT rcth.trx_number,
2102 rcth.bill_to_customer_id,
2103 rcth.customer_trx_id
2104 INTO l_reference_4,
2105 l_reference_7,
2106 l_reference_2
2107 FROM ra_cust_trx_line_gl_dist_all rctgl,
2108 ra_customer_trx_all rcth
2109 WHERE rctgl.cust_trx_line_gl_dist_id = l_source_distribution_id_num_1
2110 AND rcth.customer_trx_id = rctgl.customer_trx_id;
2111 END IF;
2112
2113 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
2114 fnd_file.put_line (fnd_file.log, 'l_reference_7='||l_reference_7);
2115 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
2116
2117 l_reference_5 := l_reference_4;
2118 EXCEPTION
2119 WHEN NO_DATA_FOUND THEN
2120 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(8a) '||l_source_distribution_id_num_1);
2121 END;
2122 ELSIF (l_source = 'Receivables' AND l_category = 'Misc Receipts') THEN
2123 BEGIN
2124 fnd_file.put_line (fnd_file.log, 'l_source_distribution_id_num_1='||l_source_distribution_id_num_1);
2125 SELECT source_id,
2126 source_table,
2127 source_type
2128 INTO l_ar_source_id,
2129 l_ar_source_table,
2130 l_ar_source_type
2131 FROM ar_distributions_all
2132 WHERE line_id = l_source_distribution_id_num_1;
2133 fnd_file.put_line (fnd_file.log, 'l_ar_source_id='||l_ar_source_id);
2134 fnd_file.put_line (fnd_file.log, 'l_ar_source_table='||l_ar_source_table);
2135 fnd_file.put_line (fnd_file.log, 'l_ar_source_type='||l_ar_source_type);
2136 EXCEPTION
2137 WHEN NO_DATA_FOUND THEN
2138 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7a) '||l_source_distribution_id_num_1);
2139 END;
2140
2141 IF (l_ar_source_table = 'MCD' ) THEN
2142 BEGIN
2143 SELECT acr.cash_receipt_id,
2144 acrh.cash_receipt_history_id,
2145 acr.receipt_number
2146 INTO l_reference_2,
2147 l_reference_5,
2148 l_reference_4
2149 FROM ar_misc_cash_distributions_all ara,
2150 ar_cash_receipt_history_all acrh,
2151 ar_cash_receipts_all acr
2152 WHERE ara.misc_cash_distribution_id = l_ar_source_id
2153 AND ara.cash_receipt_id = acr.cash_receipt_id
2154 AND ara.cash_receipt_id = acrh.cash_receipt_id;
2155
2156 fnd_file.put_line (fnd_file.log, 'l_reference_5='||l_reference_5);
2157 fnd_file.put_line (fnd_file.log, 'l_reference_2='||l_reference_2);
2158 fnd_file.put_line (fnd_file.log, 'l_reference_4='||l_reference_4);
2159
2160 EXCEPTION
2161 WHEN NO_DATA_FOUND THEN
2162 fnd_file.put_line (fnd_file.log, 'No data found for distribution id(7e) '||l_applied_to_dist_id_num_1);
2163 END;
2164 END IF;
2165 END IF;
2166 END IF;
2167
2168 l_refer2 := l_reference_2;
2169
2170 SELECT SUBSTR(l_refer2, 0, decode(INSTR(l_refer2, 'C'), 0,
2171 LENGTH(l_refer2),INSTR(l_refer2,'C')-1))
2172 INTO l_reference_2
2173 FROM dual;
2174
2175 -- Added to handle 3131834 for deobligated invoices in final match
2176 -- and reversal requisitions created by autocreate PO.
2177 --
2178 IF (l_source = 'Purchasing' AND l_category = 'Purchases')
2179 THEN
2180 IF l_reference_6 = 'SRCDOC' AND l_reference_10 <> -100
2181 THEN
2182 l_reference_2 := l_reference_10 ;
2183 l_category := 'Purchase Invoices';
2184 l_source := 'Payables';
2185 g_src_flag := '1';
2186 END IF;
2187 END IF;
2188
2189 IF (l_source = 'Purchasing' AND l_category = 'Requisitions')
2190 THEN
2191 IF l_reference_6 = 'SRCDOC' AND l_reference_10 <> -100
2192 THEN
2193 l_reference_2 := l_reference_10 ;
2194 l_category := 'Purchases';
2195 g_src_flag := '2';
2196 END IF;
2197 END IF;
2198
2199 -- Get the Document Name and its Creation Date
2200 GET_DOC_INFO (l_je_header_id, l_source, l_category, l_name,
2201 l_date_created,l_creation_date, l_created_by,
2202 l_reference_1, l_reference_2,l_reference_3,
2203 l_reference_4, l_reference_5, l_reference_9,
2204 l_refer2, l_doc_num, l_doc_date, l_doc_creation_date,
2205 l_doc_created_by, l_gl_date,
2206 l_factsii_pub_law_rec_col,
2207 l_gl_sl_link_id,
2208 l_factsii_pub_law_rec,
2209 l_reversal_flag);
2210
2211 IF (g_error_code <> 0) THEN
2212 RETURN;
2213 END IF;
2214
2215 -- Get the User Name who created the Document
2216 GET_DOC_USER (l_doc_created_by, l_entry_user);
2217
2218 IF (g_error_code <> 0)
2219 THEN
2220 RETURN;
2221 END IF;
2222 IF (g_govt_non_govt_ind = 'N')
2223 THEN
2224 g_govt_non_govt_val := 'N';
2225 l_eliminations_id := '';
2226 ELSIF (NVL(g_govt_non_govt_ind,'X') = 'X')
2227 THEN
2228 g_govt_non_govt_val := '';
2229 l_eliminations_id := '';
2230 ELSE
2231 BEGIN
2232 -------------------------------------------------------------------
2233 -- Get the vendor id from Payables (Includes invoice and Payments)
2234 -------------------------------------------------------------------
2235 IF (l_source = 'Payables' AND l_category <> 'Treasury Confirmation')
2236 THEN
2237 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2238 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SOURCE: '|| L_SOURCE);
2239 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE 2: '|| L_REFERENCE_2);
2240 END IF;
2241 IF (l_reference_2 IS NOT NULL)
2242 THEN
2243 BEGIN
2244 l_feeder_flag := 'Y';
2245 SELECT v.vendor_id vendor_id,
2246 v.vendor_type_lookup_code vendor_type,
2247 fvv.eliminations_id
2248 INTO l_vendor_id, l_vendor_type, l_eliminations_id
2249 FROM ap_invoices_all i,
2250 po_vendors v,
2251 fv_facts_vendors_v fvv
2252 WHERE i.invoice_id = to_number(l_reference_2)
2253 AND i.vendor_id = v.vendor_id
2254 AND fvv.vendor_id = v.vendor_id;
2255 EXCEPTION
2256 WHEN NO_DATA_FOUND THEN
2257 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.select_1',' NO DATA FOUND !!');
2258 WHEN INVALID_NUMBER THEN
2259 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Invalid Number passed to REFERENCE_2');
2260 END;
2261
2262 ELSE
2263 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2264 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE_2 I.E. INVOICE_ID IS NULL');
2265 END IF;
2266 END IF;
2267 -------------------------------------------------------------------
2268 -- Get the Vendor ID for Purchasing Inventory Records
2269 ------------------------------------------------------------------
2270 ELSIF (l_source = 'Purchasing') THEN
2271 IF (l_category = 'Receiving') THEN
2272
2273 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2274 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NAME: '|| L_NAME);
2275 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE 2: '|| L_REFERENCE_2);
2276 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE 5: '|| L_REFERENCE_5);
2277 END IF;
2278 IF (l_reference_2 IS NOT NULL AND
2279 l_reference_5 IS NOT NULL) THEN
2280 BEGIN
2281 l_feeder_flag := 'Y';
2282 SELECT v.vendor_id VENDOR_ID,
2283 v.vendor_type_lookup_code VENDOR_TYPE,
2284 fvv.eliminations_id
2285 INTO l_vendor_id,l_vendor_type,l_eliminations_id
2286 FROM rcv_transactions rt,
2287 po_vendors v,
2288 po_headers_all ph,
2289 fv_facts_vendors_v fvv
2290 WHERE rt.po_header_id = to_number(l_reference_2)
2291 AND rt.transaction_id = to_number(l_reference_5)
2292 AND rt.po_header_id = ph.po_header_id
2293 AND v.vendor_id = ph.vendor_id
2294 AND fvv.vendor_id = ph.vendor_id;
2295 EXCEPTION
2296 WHEN NO_DATA_FOUND THEN
2297 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2298 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO DATA FOUND WHEN SOURCE'||
2299 ' is Purchasing and category'||
2300 ' is Receiving!!');
2301 END IF;
2302
2303 WHEN INVALID_NUMBER THEN
2304 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Invalid Number passed to REFERENCE_5');
2305 END;
2306 ELSE
2307 IF (l_reference_2 IS NULL) THEN
2308 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2309 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE_2 I.E. PO_HEADER_ID '||
2310 'is NULL');
2311 END IF;
2312 ELSE
2313 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2314 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE_5 I.E.'||
2315 ' Transaction_id is NULL');
2316 END IF;
2317 END IF;
2318 END IF;
2319
2320 ELSIF (l_category = 'Purchases') THEN
2321 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2322 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REFERENCE 2: '|| L_REFERENCE_2);
2323 END IF;
2324
2325 IF (l_reference_2 IS NOT NULL) THEN
2326 BEGIN
2327 l_feeder_flag := 'Y';
2328
2329 SELECT pov.vendor_id,
2330 pov.vendor_type_lookup_code,
2331 fvv.eliminations_id
2332 INTO l_vendor_id,
2333 l_vendor_type,
2334 l_eliminations_id
2335 FROM po_vendors pov, po_headers_all poh,
2336 fv_facts_vendors_v fvv
2337 WHERE poh.po_header_id = to_number(l_reference_2)
2338 AND pov.vendor_id = poh.vendor_id
2339 AND fvv.vendor_id = poh.vendor_id;
2340 EXCEPTION
2341 WHEN NO_DATA_FOUND THEN
2342 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2343 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO DATA FOUND WHEN SOURCE IS'||
2344 ' Purchasing and category'||
2345 ' is Purchases!!');
2346 END IF;
2347 WHEN INVALID_NUMBER THEN
2348 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Invalid Number passed to REFERENCE_2');
2349 END;
2350 ELSE
2351
2352 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2353 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, 'REFERENCE_2 I.E. PO HEADER ID'||
2354 ' is NULL');
2355 END IF;
2356 END IF;
2357 END IF;
2358
2359 -----------------------------------------------------------
2360 -- Customer id for Receivables transactions
2361 -----------------------------------------------------------
2362 ELSIF (l_source = 'Receivables') THEN
2363 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2364 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NAME: '||L_NAME);
2365 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE 7: '|| L_REFERENCE_7);
2366 END IF;
2367 IF (l_reference_7 IS NOT NULL) THEN
2368 l_vendor_id := to_number(l_reference_7);
2369 BEGIN
2370 l_feeder_flag := 'Y';
2371 SELECT hzca.customer_class_code, fcv.eliminations_id
2372 INTO l_vendor_type, l_eliminations_id
2373 FROM hz_cust_accounts hzca, fv_facts_customers_v fcv
2374 WHERE hzca.cust_account_id = to_number(l_reference_7)
2375 AND fcv.customer_id = hzca.cust_account_id;
2376 EXCEPTION
2377 WHEN NO_DATA_FOUND THEN
2378 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2379 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' NO DATA FOUND !!');
2380 END IF;
2381 WHEN INVALID_NUMBER THEN
2382 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Invalid Number passed to REFERENCE_7');
2383 END;
2384 ELSE
2385 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2386 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE_7 I.E. '||
2387 'customer_id is NULL');
2388 END IF;
2389 END IF;
2390 --------------------------------------------------------------------
2391 -- Vendor id for TC transactions
2392 --------------------------------------------------------------------
2393 ELSIF (l_source = 'Payables' AND
2394 l_category = 'Treasury Confirmation') THEN
2395 l_stage := 4;
2396 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2397 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SOURCE: '|| L_SOURCE);
2398 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE 3: '|| L_REFERENCE_3);
2399 END IF;
2400 IF (l_reference_3 IS NOT NULL)
2401 THEN
2402 BEGIN
2403 l_feeder_flag := 'Y';
2404 SELECT v.vendor_id vendor_id,
2405 v.vendor_type_lookup_code vendor_type,
2406 fvv.eliminations_id
2407 INTO l_vendor_id,l_vendor_type,l_eliminations_id
2408 FROM ap_checks_all apc,
2409 po_vendors v,
2410 fv_facts_vendors_v fvv
2411 WHERE apc.vendor_id = v.vendor_id
2412 AND apc.check_id = to_number(l_reference_3)
2413 AND fvv.vendor_id = v.vendor_id;
2414 EXCEPTION
2415 WHEN NO_DATA_FOUND THEN
2416 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2417 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name||'.message_22',' NO DATA FOUND !!');
2418 END IF;
2419 WHEN INVALID_NUMBER THEN
2420 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Invalid Number passed to REFERENCE_3');
2421 END;
2422 ELSE
2423 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2424 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REFERENCE_3 I.E. CHECK_ID IS NULL');
2425 END IF;
2426 END IF;
2427 -------------------------------------------------------------
2428 -- Budgetary Transaction
2429 -------------------------------------------------------------
2430 ELSIF l_source = 'Budgetary Transaction' THEN
2431 DECLARE
2432 CURSOR dept_cur IS
2433 SELECT dept_id||main_account
2434 FROM fv_be_trx_dtls
2435 WHERE transaction_id = to_number(l_reference_1);
2436 BEGIN
2437 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
2438 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'BUDGETARY TRANSACTION');
2439 END IF;
2440 l_feeder_flag := 'Y';
2441 OPEN dept_cur ;
2442 FETCH dept_cur INTO l_eliminations_id ;
2443 IF dept_cur%FOUND THEN
2444 IF (l_eliminations_id IS NOT NULL) THEN
2445 l_vendor_id := l_eliminations_id;
2446 l_vendor_tYpe := 'FEDERAL';
2447 END IF;
2448 ELSE
2449 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2450 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'NO DATA FOUND WHEN '||
2451 'source = '||l_source);
2452 END IF;
2453 END IF ;
2454 CLOSE dept_cur ;
2455 END;
2456
2457 ELSE /* Journale entered manually */
2458
2459 IF (g_govt_non_govt_ind = 'Y')
2460 THEN
2461 IF (l_jrnl_att_value is NOT NULL) THEN
2462 l_eliminations_id := l_jrnl_att_value;
2463 g_govt_non_govt_val := 'F';
2464 ELSE
2465 l_eliminations_id := NULL;
2466 g_govt_non_govt_val := 'N';
2467 END IF;
2468
2469 l_feeder_flag := 'N';
2470
2471 ELSIF (g_govt_non_govt_ind = 'F')THEN
2472 IF (l_jrnl_att_value is NOT NULL)
2473 THEN
2474 l_eliminations_id := l_jrnl_att_value;
2475 ELSE
2476 l_eliminations_id := '00';
2477 END IF;
2478 l_feeder_flag := 'N';
2479 g_govt_non_govt_val := 'F';
2480 ELSE
2481 l_valid_flag := 'N';
2482 END IF;
2483
2484 END IF; /* journale source */
2485 EXCEPTION
2486 WHEN NO_DATA_FOUND THEN
2487 l_valid_flag := 'Y';
2488 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2489 l_valid_flag := 'Y';
2490
2491
2492 END;
2493 END IF; /* before BEGIN */
2494
2495 IF l_valid_flag = 'Y' THEN -- valid Flag
2496 IF (l_feeder_flag = 'Y') THEN
2497 IF (l_vendor_id IS NULL) THEN
2498 IF (l_jrnl_att IS NOT NULL) THEN
2499 l_eliminations_id := l_jrnl_att_value;
2500 END IF;
2501 END IF;
2502 END IF;
2503
2504 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2505 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FEEDER FLAG : '||L_FEEDER_FLAG);
2506 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
2507 END IF;
2508 IF (l_vendor_id IS NULL)
2509 THEN
2510 IF ((g_govt_non_govt_ind = 'F' AND l_feeder_flag = 'Y')
2511 OR (g_govt_non_govt_ind = 'F'
2512 AND l_feeder_flag = 'N' AND l_eliminations_id = '00'))
2513 THEN
2514 IF (l_eliminations_id IS NULL OR l_eliminations_id = '00')
2515 THEN
2516 g_govt_non_govt_val := 'F';
2517 l_eliminations_id := '00';
2518 ELSE
2519 g_govt_non_govt_val := 'F';
2520 END IF;
2521 -- Govt Non Govt Indicator = Y
2522 ELSIF ((g_govt_non_govt_ind = 'Y' AND l_feeder_flag = 'Y')
2523 OR (g_govt_non_govt_ind = 'Y' AND l_feeder_flag = 'N'))
2524 THEN
2525 --IF (l_eliminations_id IS NULL)
2526 --Bug 7150443
2527 IF (l_eliminations_id IS NULL or l_eliminations_id = ' ')
2528 THEN
2529 g_govt_non_govt_val := 'N';
2530 l_eliminations_id := ' ';
2531 ELSE
2532 g_govt_non_govt_val := 'F';
2533 END IF;
2534 END IF; -- Govt Non Govt = F or Y
2535 ELSE -- l_vendor_id IS NOT NULL
2536 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2537 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VENDOR ID IS NOT NULL');
2538 END IF;
2539 IF (l_feeder_flag = 'Y')
2540 THEN
2541 IF (g_govt_non_govt_val = 'F' AND UPPER(l_vendor_type) <> 'FEDERAL')
2542 THEN
2543 IF (l_eliminations_id IS NULL)
2544 THEN
2545 l_eliminations_id := '00';
2546 END IF;
2547 g_govt_non_govt_val := 'F';
2548 ELSIF (g_govt_non_govt_ind = 'F') THEN
2549 IF l_eliminations_id IS NULL THEN
2550 l_eliminations_id := '00';
2551 END IF;
2552 g_govt_non_govt_val := 'F';
2553 ELSIF (g_govt_non_govt_ind = 'Y' AND UPPER(l_vendor_type) <> 'FEDERAL')
2554 THEN
2555 g_govt_non_govt_val := 'N';
2556 l_eliminations_id := ' ';
2557 ELSIF (g_govt_non_govt_ind = 'Y') THEN
2558 IF l_eliminations_id IS NULL THEN
2559 l_eliminations_id := '00';
2560 END IF;
2561 g_govt_non_govt_val := 'F';
2562 END IF; /* (L_vendor_type <> FEDERAL */
2563 END IF; -- Feeder Flag
2564 END IF; -- l_vendor_id
2565 --END IF; -- l_feeder_system
2566 END IF; -- l_valid_flag
2567
2568 ------------------------------------------------------------
2569 -- Deriving the Public Law Code value
2570 ------------------------------------------------------------
2571 IF g_public_law_code_flag = 'N' THEN
2572 g_public_law_code_val := ' ';
2573 END IF;
2574
2575 -- If the public law code is required then check the journal source.
2576 -- If the journal source is YE Close and Budgetary Transaction then
2577 -- get the public law code from BE details table. If the journal
2578 -- source is not these two, then get the public law code from the
2579 -- corresponding attribute field on the je line.
2580
2581 IF g_public_law_code_flag = 'Y' THEN
2582 --Bug#3225337
2583 --IF l_reference_1 IS NOT NULL
2584 IF (NVL(l_reference_1, '-100') <> '-100')
2585 THEN
2586 BEGIN
2587 SELECT public_law_code
2588 INTO g_public_law_code_val
2589 FROM fv_be_trx_dtls
2590 WHERE transaction_id = to_number(l_reference_1)
2591 AND set_of_books_id = g_set_of_books_id ;
2592 EXCEPTION
2593 WHEN NO_DATA_FOUND THEN NULL;
2594 WHEN INVALID_NUMBER THEN
2595 NULL;
2596 END;
2597 ELSE -- reference_1 is null
2598 IF l_pl_code_col IS NULL THEN
2599 g_public_law_code_val := ' ' ;
2600 ELSE
2601 g_public_law_code_val := SUBSTR(l_pl_code,1,7);
2602 END IF;
2603 END IF ;
2604
2605 IF l_source = 'Receivables' THEN
2606 IF (l_factsii_pub_law_rec_col IS NOT NULL) THEN
2607 IF (l_factsii_pub_law_rec IS NOT NULL) THEN
2608 g_public_law_code_val := SUBSTR(l_factsii_pub_law_rec,1,7);
2609 ELSE
2610 g_public_law_code_val := ' ' ;
2611 END IF;
2612 END IF;
2613 END IF;
2614
2615 END IF ;
2616 ------------------------------------------------------------
2617 -- Deriving the Legislation Indicator Value
2618 -------------------------------------------------------------
2619 IF g_legis_Ind_flag = 'Y' THEN
2620 BEGIN
2621 SELECT transaction_type_id
2622 INTO l_tran_type
2623 FROM Fv_be_trx_dtls
2624 WHERE transaction_id = to_number(l_reference_1)
2625 AND set_of_books_id = g_set_of_books_id ;
2626
2627 -- Get the Legislation Indicator Value from
2628 -- fv_be_transaction_types table.
2629 SELECT legislative_indicator
2630 INTO g_legis_ind_val
2631 FROM FV_be_transaction_types
2632 WHERE apprn_transaction_type = l_tran_type
2633 AND set_of_books_id = g_set_of_books_id ;
2634 EXCEPTION
2635 WHEN NO_DATA_FOUND THEN
2636 g_legis_ind_val := 'A' ;
2637 WHEN INVALID_NUMBER THEN
2638 g_legis_ind_val := 'A' ;
2639 END ;
2640 END IF;
2641 ------------------------------------------------------------
2642 -- Deriving the Advance Type Value
2643 ------------------------------------------------------------
2644
2645 IF g_advance_flag = 'Y' THEN
2646 --Bug#3225337
2647 --IF l_reference_1 IS NOT NULL
2648 IF (NVL(l_reference_1, '-100') <> '-100')
2649 THEN
2650 BEGIN
2651 SELECT advance_type
2652 INTO g_advance_type_val
2653 FROM fv_be_trx_dtls
2654 WHERE transaction_id = to_number(l_reference_1)
2655 AND set_of_books_id = g_set_of_books_id ;
2656 -- IF the advance_type value is null then set it to 'X'
2657 IF g_advance_type_val IS NULL THEN
2658 g_advance_type_val := 'X';
2659 END IF;
2660 EXCEPTION
2661 WHEN NO_DATA_FOUND THEN
2662 g_advance_type_val := 'X';
2663 WHEN INVALID_NUMBER THEN
2664 g_advance_type_val := 'X';
2665 END;
2666 ELSE
2667 -- l_reference_1 is null
2668 -- If an attribute column is not set up for advance type
2669 -- then report blank. If a column is setup but
2670 -- the journal line does not contain a value, then
2671 -- report 'X'
2672 IF l_advance_type_col IS NULL THEN
2673 g_advance_type_val := 'X';
2674 ELSE
2675 IF l_advance_type IS NULL THEN
2676 g_advance_type_val := 'X';
2677 ELSE
2678 g_advance_type_val := SUBSTR(l_advance_type,1,1);
2679 END IF;
2680 END IF;
2681 END IF;
2682
2683 END IF;
2684 ------------------------------------------------------------
2685 -- Deriving the Dept ID and Main Account
2686 -------------------------------------------------------------
2687 -- Transfer Acct specific processing
2688 IF g_transfer_ind = 'Y' THEN
2689 --Bug#3225337
2690 --IF l_reference_1 IS NOT NULL THEN
2691 IF (NVL(l_reference_1, '-100') <> '-100') THEN
2692 BEGIN
2693 SELECT dept_id,
2694 main_account
2695 INTO g_transfer_dept_id,
2696 g_transfer_main_acct
2697 FROM fv_be_trx_dtls
2698 WHERE transaction_id = to_number(l_reference_1)
2699 AND set_of_books_id = g_set_of_books_id ;
2700
2701 -- IF the Transfer values are null then set default values
2702 -- Since both dept_id and main_acct are null or both have
2703 -- values test IF one of them is null
2704
2705 IF g_transfer_dept_id IS NULL THEN
2706 --bug#3219352
2707 g_transfer_dept_id := ' ';
2708 g_transfer_main_acct := ' ';
2709 --g_transfer_dept_id := '00';
2710 --g_transfer_main_acct := '0000';
2711 END IF;
2712 EXCEPTION
2713 WHEN NO_DATA_FOUND THEN
2714 -- This Exception fires when
2715 -- the transfer info
2716 -- cannot be found.
2717 --bug#3219352
2718 g_transfer_dept_id := ' ';
2719 g_transfer_main_acct := ' ';
2720 --g_transfer_dept_id := '00';
2721 --g_transfer_main_acct := '0000';
2722 WHEN INVALID_NUMBER THEN
2723 g_transfer_dept_id := ' ';
2724 g_transfer_main_acct := ' ';
2725 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,'Invalid Number passed to REFERENCE_1');
2726 END;
2727 ELSE
2728 -- l_reference_1 is null
2729 -- If an attribute column is not set up for transfer
2730 -- info then report blanks.
2731 IF l_tr_main_acct_col IS NULL THEN
2732 g_transfer_dept_id := ' ';
2733 g_transfer_main_acct := ' ';
2734 ELSE
2735 g_transfer_main_acct := SUBSTR(l_tr_main_acct,1,4);
2736 g_transfer_dept_id := SUBSTR(l_tr_dept_id,1,2);
2737 END IF;
2738 END IF;
2739
2740 END IF;
2741
2742 ----------------------------------------------------------------
2743 -- Processing Budget Year Authority attribute
2744 ----------------------------------------------------------------
2745
2746 IF l_sgl_acct_num IS NULL THEN
2747 BEGIN
2748 SELECT 'X'
2749 INTO l_exists
2750 FROM fv_facts_ussgl_accounts
2751 WHERE ussgl_account = l_account_number;
2752 l_sgl_acct_num := l_account_number;
2753 EXCEPTION
2754 WHEN NO_DATA_FOUND THEN
2755 NULL;
2756 END;
2757 END IF;
2758
2759 IF l_sgl_acct_num IS NOT NULL THEN
2760
2761 g_year_budget_auth := NULL;
2762
2763 BEGIN
2764 SELECT disbursements_flag
2765 INTO l_disbursements_flag
2766 FROM fv_facts_ussgl_accounts
2767 WHERE ussgl_account = l_sgl_acct_num;
2768 EXCEPTION
2769 WHEN OTHERS THEN
2770 g_error_code := sqlcode ;
2771 g_error_buf := sqlerrm ||
2772 ' [ JOURNAL_PROCESS '||
2773 ' l_disbursements_flag - ' ||
2774 l_sgl_acct_num||' ] ' ;
2775 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_1',g_error_buf);
2776 RETURN ;
2777 END;
2778
2779 BEGIN
2780 SELECT FTS.Time_Frame, FFFA.financing_account
2781 INTO l_time_frame, l_financing_acct
2782 FROM FV_FACTS_FEDERAL_ACCOUNTS FFFA,
2783 FV_TREASURY_SYMBOLS FTS
2784 WHERE FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
2785 AND FTS.treasury_symbol_id = g_treasury_symbol_id
2786 AND FTS.set_of_books_id = g_set_of_books_id
2787 AND FFFA.set_of_books_id = g_set_of_books_id ;
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 g_error_code := sqlcode ;
2791 g_error_buf := sqlerrm ||
2792 ' [JOURNAL_PROCESS '||
2793 '- v_time_frame ] ' ;
2794 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_2',g_error_buf);
2795 RETURN ;
2796 END;
2797
2798 IF l_time_frame = 'NO_YEAR'
2799 AND l_financing_acct = 'N'
2800 AND l_disbursements_flag = 'Y'
2801 AND (l_entered_dr <> 0 OR l_entered_cr <> 0) THEN
2802 BEGIN
2803 SELECT fyr_segment_value
2804 INTO l_fyr_segment_value
2805 FROM fv_pya_fiscalyear_map
2806 WHERE period_year = g_period_year
2807 AND set_of_books_id = g_set_of_books_id;
2808 EXCEPTION
2809 WHEN OTHERS THEN
2810 g_error_code := sqlcode ;
2811 g_error_buf := sqlerrm ||
2812 ' [JOURNAL_PROCESS '||
2813 ' l_fyr_segment_value - ] ' ;
2814 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_3',g_error_buf);
2815 END;
2816
2817 IF l_fyr_segment_value IS NOT NULL THEN
2818 IF l_fyr_segment_value = l_seg_fiscal_yr THEN
2819 g_year_budget_auth := 'NEW';
2820 ELSE
2821 g_year_budget_auth := 'BAL';
2822 END IF;
2823 END IF;
2824 END IF;
2825
2826 END IF;
2827
2828 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2829 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' IN VIEW');
2830 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' VENDOR ID: L_VENDOR_ID');
2831 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ELIMINATIONS ID: '|| L_ELIMINATIONS_ID);
2832 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GNG: '||G_GOVT_NON_GOVT_VAL);
2833 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CCID: '|| L_CCID);
2834 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCT#: '|| L_ACCOUNT_NUMBER);
2835 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DEBIT: '|| L_ENTERED_DR);
2836 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CREDIT: '|| L_ENTERED_CR);
2837 END IF;
2838
2839 IF g_src_flag = 1 THEN
2840 l_source := 'Purchasing';
2841 END IF;
2842
2843 POPULATE_TABLE ( g_treasury_symbol_id ,
2844 g_set_of_books_id ,
2845 l_ccid ,
2846 l_bal_segment ,
2847 l_account_number ,
2848 l_source ,
2849 l_category ,
2850 l_doc_num ,
2851 l_doc_date ,
2852 l_doc_creation_date ,
2853 l_entry_user ,
2854 g_govt_non_govt_val ,
2855 l_eliminations_id ,
2856 g_exch_non_exch_val ,
2857 g_cust_non_cust_val ,
2858 g_budget_subfunction_val ,
2859 NVL(l_entered_dr,0) ,
2860 NVL(l_entered_cr,0) ,
2861 g_transfer_dept_id ,
2862 g_transfer_main_acct ,
2863 g_year_budget_auth ,
2864 g_budget_function_val ,
2865 g_advance_type_val ,
2866 l_cohort ,
2867 '' ,
2868 -- p_begin_end ,
2869 g_def_indef_val ,
2870 LPAD(l_cbs_num,3,'0'),
2871 l_cat_b_text ,
2872 LPAD(l_prn_num,3,'0'),
2873 l_prn_text ,
2874 g_public_law_code_val ,
2875 g_appor_cat_val ,
2876 g_authority_type_val ,
2877 g_transaction_partner_val ,
2878 g_reimburseable_val ,
2879 g_bea_category_val ,
2880 g_borrowing_source_val ,
2881 g_def_liquid_flag ,
2882 g_deficiency_flag ,
2883 g_availability_val ,
2884 g_legis_ind_val ,
2885 l_je_line_creation_date ,
2886 l_je_line_modified_date ,
2887 l_je_line_period_name ,
2888 l_gl_date ,
2889 l_gl_posted_date,
2890 l_reversal_flag,
2891 l_sla_hdr_event_id ,
2892 l_sla_hdr_creation_date,
2893 l_sla_entity_id );
2894
2895 IF (g_error_code <> 0)
2896 THEN
2897 return;
2898 END IF;
2899 END LOOP;
2900 DBMS_SQL.CLOSE_CURSOR(l_jrnl_cursor);
2901 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2902 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LEAVING JOURNAL PROCESSES ...');
2903 END IF;
2904 EXCEPTION
2905 WHEN OTHERS THEN
2906 DBMS_SQL.CLOSE_CURSOR(l_jrnl_cursor);
2907 g_error_code := 2 ;
2908 g_error_buf := 'JOURNAL PROCESSES - Exception Main (Others) - ' ||
2909 to_char(sqlcode) || ' - ' || SQLERRM;
2910 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
2911 END JOURNAL_PROCESS;
2912
2913 -- -------------------------------------------------------------------
2914 -- PROCEDURE GET_DOC_INFO
2915 -- -------------------------------------------------------------------
2916 -- Get_Doc_Info procedure is called from the Journal_Process procedure.
2917 -- Its purpose is to find the document related information like
2918 -- document number, its creation date and created by.
2919 -- -------------------------------------------------------------------
2920 PROCEDURE GET_DOC_INFO (p_je_header_id IN Number,
2921 p_je_source_name IN Varchar2,
2922 p_je_category_name IN Varchar2,
2923 p_name IN Varchar2,
2924 p_date IN Date,
2925 p_creation_date IN Date,
2926 p_created_by IN Number,
2927 p_reference1 IN Varchar2,
2928 p_reference2 IN Varchar2,
2929 p_reference3 IN Varchar2,
2930 p_reference4 IN Varchar2,
2931 p_reference5 IN Varchar2,
2932 p_reference9 IN Varchar2,
2933 p_ref2 IN Varchar2,
2934 p_doc_num OUT NOCOPY Varchar2,
2935 p_doc_date OUT NOCOPY Date,
2936 p_doc_creation_date OUT NOCOPY Date,
2937 p_doc_created_by OUT NOCOPY Number,
2938 p_gl_date IN OUT NOCOPY DATE,
2939 p_rec_public_law_code_col IN VARCHAR2,
2940 p_gl_sl_link_id IN NUMBER,
2941 p_rec_public_law_code OUT NOCOPY Varchar2,
2942 p_reversed OUT NOCOPY VARCHAR2)
2943 IS
2944 l_module_name VARCHAR2(200);
2945 l_refer2 Varchar2(240);
2946 l_refer4 Varchar2(240);
2947 l_cash_receipt_hist_id Varchar2(240);
2948 l_temp_cr_hist_id Varchar2(240);
2949 l_rev_exists Varchar2(1);
2950 l_document_num Varchar2(240);
2951 l_doc_date Date;
2952 l_doc_creation_date Date;
2953 l_doc_created_by Number;
2954 l_doc_date_d Date;
2955 l_doc_creation_date_d Date;
2956 l_doc_created_by_d Number;
2957 l_void_date Date;
2958 l_check_date Date;
2959 l_inv_payment_id Number;
2960 l_gl_date Date;
2961 l_rec_public_law_code VARCHAR2(150);
2962 l_parent_reversal_id ap_invoice_distributions.parent_reversal_id%TYPE;
2963 l_event_type_code ap_accounting_events.event_type_code%TYPE;
2964 l_receipt_hist_status ar_cash_receipt_history_all.status%TYPE;
2965 l_dummy_rev_exists VARCHAR2(1);
2966
2967
2968 TYPE common_ref_type IS REF CURSOR ;
2969 pur_req common_ref_type;
2970 pur_pur common_ref_type;
2971 pur_rec common_ref_type;
2972 Receivables_Distrib common_ref_type;
2973 Receivables_Misc common_ref_type;
2974
2975
2976 l_select VARCHAR2(1000);
2977 l_temp_doc_date VARCHAR2(25) ;
2978
2979 /*
2980 CURSOR pur_Rec IS
2981 SELECT rt.transaction_date,
2982 rcv.receipt_num,
2983 rt.creation_date,
2984 rt.created_by
2985 FROM rcv_transactions rt,
2986 rcv_shipment_headers rcv
2987 WHERE rt.shipment_header_id = rcv.shipment_header_Id
2988 AND TO_CHAR(rt.transaction_id) = p_reference5;
2989 */
2990 CURSOR Pay_Pur IS
2991 SELECT inv.invoice_num,
2992 inv.invoice_date,
2993 inv.creation_date,
2994 inv.created_by
2995 FROM ap_invoices_all inv
2996 WHERE inv.invoice_id = to_number(p_reference2);
2997 CURSOR Pay_Pay IS
2998 SELECT DISTINCT api.invoice_num,
2999 DECODE(apc.payment_type_flag,'A',apc.check_date,
3000 NVL(apc.treasury_pay_date, apc.check_date)) check_date,
3001 apip.creation_date,
3002 apip.created_by
3003 FROM ap_checks_all apc,
3004 ap_invoices_all api,
3005 ap_invoice_payments_all apip
3006 WHERE apc.check_id = to_number(p_reference3)
3007 AND api.invoice_id = to_number(p_reference2)
3008 AND apc.check_id = apip.check_id
3009 AND api.invoice_id = apip.invoice_id;
3010 CURSOR Receivables IS
3011 SELECT DECODE(l_rev_exists, 'Y', reversal_date, receipt_date),
3012 DECODE(l_rev_exists, 'Y', l_doc_creation_date_d,creation_date),
3013 DECODE(l_rev_exists, 'Y', l_doc_created_by_d, created_by)
3014 FROM ar_cash_receipts_all
3015 WHERE cash_receipt_id = to_number(l_refer2);
3016 CURSOR Receivables_Exists IS
3017 SELECT 'Y'
3018 FROM ar_cash_receipt_history_all
3019 WHERE cash_receipt_history_id = TO_NUMBER(l_cash_receipt_hist_id);
3020 CURSOR Receivables_Applications IS
3021 SELECT cash_receipt_history_id
3022 FROM ar_receivable_applications_all
3023 WHERE receivable_application_id = TO_NUMBER(l_cash_receipt_hist_id);
3024 CURSOR Receivables_Hist
3025 (
3026 c_cash_receipt_hist_id NUMBER,
3027 c_cash_receipt_id VARCHAR2
3028 )
3029 IS
3030 SELECT 'Y', status
3031 FROM ar_cash_receipt_history_all
3032 WHERE cash_receipt_history_id = c_cash_receipt_hist_id
3033 AND cash_receipt_id = c_cash_receipt_id;
3034 CURSOR Receivables_History
3035 (
3036 c_cash_receipt_hist_id NUMBER
3037 )
3038 IS
3039 SELECT 'Y', creation_date, created_by
3040 FROM ar_cash_receipt_history_all
3041 WHERE reversal_cash_receipt_hist_id = c_cash_receipt_hist_id;
3042 --CURSOR Receivables_Misc IS
3043 -- SELECT 'Y', creation_date, created_by
3044 -- FROM ar_misc_cash_distributions_all
3045 -- WHERE misc_cash_distribution_id = l_cash_receipt_hist_id
3046 -- AND created_from = 'ARP_REVERSE_RECEIPT.REVERSE';
3047 --CURSOR Receivables_Distrib IS
3048 -- SELECT 'Y'
3049 -- FROM ar_misc_cash_distributions_all
3050 -- WHERE misc_cash_distribution_id = to_number(l_cash_receipt_hist_id);
3051 CURSOR Pay_Treas_Check IS
3052 SELECT void_date, checkrun_name
3053 FROM ap_checks_all
3054 WHERE check_id = p_reference3;
3055 CURSOR Pay_Treas_Void IS
3056 SELECT creation_date, created_by
3057 FROM ap_invoice_payments_all
3058 WHERE check_id = p_reference3
3059 AND invoice_payment_id = (SELECT max(invoice_payment_id)
3060 FROM ap_invoice_payments_all
3061 WHERE check_id = p_reference3);
3062 CURSOR Pay_Treas IS
3063 SELECT ftc.checkrun_name,
3064 ftc.treasury_doc_date,
3065 ftc.creation_date,
3066 ftc.created_by
3067 FROM fv_treasury_confirmations_all ftc
3068 WHERE ftc.treasury_confirmation_id = to_number(p_reference1);
3069 CURSOR Pay_Pay_Check IS
3070 SELECT void_date,
3071 DECODE(payment_type_flag,'A',check_date,
3072 NVL(treasury_pay_date,check_date)) check_date
3073 FROM ap_checks_all
3074 WHERE check_id = p_reference3;
3075 CURSOR Pay_Pay_Void IS
3076 SELECT NVL(MAX(invoice_payment_id),0)
3077 FROM ap_invoice_payments_all
3078 WHERE invoice_id = NVL(p_reference2, 0)
3079 AND check_id = NVL(p_reference3,0)
3080 AND invoice_payment_id > p_reference9;
3081 CURSOR Pay_Pay_Void_Values IS
3082 SELECT api.invoice_num, apip.creation_date,
3083 apip.created_by
3084 FROM ap_invoice_payments_all apip,
3085 ap_invoices_all api
3086 WHERE api.invoice_id = NVL(p_reference2, 0)
3087 AND api.invoice_id = apip.invoice_id
3088 AND apip.check_id = NVL(p_reference3,0)
3089 AND apip.invoice_payment_id = p_reference9;
3090 CURSOR Pay_Pay_Non_Void IS
3091 SELECT api.invoice_num, apc.creation_date,
3092 apc.created_by
3093 FROM ap_checks_all apc,
3094 ap_invoices_all api,
3095 ap_invoice_payments_all apip
3096 WHERE apc.check_id = to_number(p_reference3)
3097 AND api.invoice_id = to_number(p_reference2)
3098 AND apc.check_id = apip.check_id
3099 AND api.invoice_id = apip.invoice_id;
3100 CURSOR Budget_Transac IS
3101 SELECT h.doc_number, d.gl_date, d.creation_date,
3102 d.created_by
3103 FROM fv_be_trx_dtls d, fv_be_trx_hdrs h
3104 WHERE d.transaction_id = to_number(p_reference1)
3105 AND h.doc_id = d.doc_id;
3106 /*
3107 CURSOR Pur_Req IS
3108 SELECT start_date_active, creation_date, created_by
3109 FROM po_requisition_headers_all
3110 WHERE to_char(requisition_header_id) = p_reference2;
3111
3112 CURSOR Pur_Req IS
3113 SELECT gl_encumbered_date, creation_date, created_by
3114 FROM po_req_distributions
3115 WHERE to_char(distribution_id) = p_reference3;
3116
3117 CURSOR Pur_Pur IS
3118 SELECT start_date, creation_date, created_by
3119 FROM po_headers_all
3120 WHERE segment1 = p_reference2;
3121
3122 CURSOR Pur_Pur IS
3123 SELECT gl_encumbered_date, creation_date, created_by
3124 FROM po_distributions_all
3125 WHERE to_char(po_distribution_id) = p_reference3;
3126 */
3127 CURSOR Manual IS
3128 SELECT default_effective_date
3129 FROM gl_je_headers
3130 WHERE je_header_id = p_je_header_id;
3131 CURSOR Receivables_Adjustment IS
3132 SELECT apply_date, creation_date, created_by
3133 FROM ar_adjustments_all
3134 WHERE adjustment_id = l_refer2;
3135 CURSOR Receivables_CMA IS
3136 SELECT apply_date, creation_date, created_by
3137 FROM ar_receivable_applications_all
3138 WHERE receivable_application_id = l_refer2;
3139 CURSOR Receivables_Memos_Inv IS
3140 SELECT trx_date, creation_date, created_by
3141 FROM ra_customer_trx_all
3142 WHERE customer_trx_id = l_refer2;
3143 BEGIN
3144 fnd_file.put_line (fnd_file.log, 'BEGIN GET_DOC_INFO');
3145 fnd_file.put_line (fnd_file.log, 'p_je_header_id='||p_je_header_id);
3146 fnd_file.put_line (fnd_file.log, 'p_je_source_name='||p_je_source_name);
3147 fnd_file.put_line (fnd_file.log, 'p_je_category_name='||p_je_category_name);
3148 fnd_file.put_line (fnd_file.log, 'p_name='||p_name);
3149 fnd_file.put_line (fnd_file.log, 'p_date='||p_date);
3150 fnd_file.put_line (fnd_file.log, 'p_creation_date='||p_creation_date);
3151 fnd_file.put_line (fnd_file.log, 'p_created_by='||p_created_by);
3152 fnd_file.put_line (fnd_file.log, 'p_reference1='||p_reference1);
3153 fnd_file.put_line (fnd_file.log, 'p_reference2='||p_reference2);
3154 fnd_file.put_line (fnd_file.log, 'p_reference3='||p_reference3);
3155 fnd_file.put_line (fnd_file.log, 'p_reference4='||p_reference4);
3156 fnd_file.put_line (fnd_file.log, 'p_reference5='||p_reference5);
3157 fnd_file.put_line (fnd_file.log, 'p_reference9='||p_reference9);
3158 fnd_file.put_line (fnd_file.log, 'p_ref2='||p_ref2);
3159 fnd_file.put_line (fnd_file.log, 'p_gl_date='||p_gl_date);
3160 fnd_file.put_line (fnd_file.log, 'p_rec_public_law_code_col='||p_rec_public_law_code_col);
3161 fnd_file.put_line (fnd_file.log, 'p_gl_sl_link_id='||p_gl_sl_link_id);
3162
3163
3164 l_module_name := g_module_name || 'GET_DOC_INFO';
3165 l_rev_exists := 'N';
3166 p_reversed := NULL;
3167 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3168 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
3169 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENTERING GET DOC INFO ...');
3170 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF1: '||P_REFERENCE1);
3171 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF2: '||P_REFERENCE2);
3172 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF3: '||P_REFERENCE3);
3173 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF4: '||P_REFERENCE4);
3174 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF5: '||P_REFERENCE5);
3175 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF9: '||P_REFERENCE9);
3176 END IF;
3177 -- Set the values to Null
3178 l_document_num := NULL;
3179 l_doc_date := NULL;
3180 l_doc_creation_date := NULL;
3181 l_doc_created_by := NULL;
3182 p_rec_public_law_code := NULL;
3183 l_rec_public_law_code := NULL;
3184
3185
3186 -- Added to handle 3131834 for deobligated invoices in final match
3187 -- and reversal requisitions created by autocreate PO.
3188 --
3189
3190
3191
3192
3193
3194 -- Code for Purchasing
3195 IF p_je_source_name = 'Purchasing'
3196 THEN
3197 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3198 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PURCHASING ...');
3199 END IF;
3200
3201 IF p_je_category_name = 'Requisitions'
3202 THEN
3203 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3204 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REQUISITIONS ...');
3205 END IF;
3206 l_document_num := p_reference4;
3207 -- If an attribute col has been defined in
3208 -- system parameters form, then select that
3209 -- column's value from req headers. If that value
3210 -- is not a date, then select creation date as the
3211 -- doc date. If an attribute col has not been
3212 -- defined, then select creation date as the doc
3213 -- date.
3214 IF g_req_date_seg IS NOT NULL
3215 THEN
3216 l_select :=
3217 'SELECT '||g_req_date_seg||', creation_date, created_by
3218 FROM po_requisition_headers_all
3219 WHERE requisition_header_id = '||to_number(p_reference2) ;
3220
3221
3222 OPEN pur_req FOR l_select ;
3223 FETCH pur_req INTO l_temp_doc_date,
3224 l_doc_creation_date,
3225 l_doc_created_by;
3226 CLOSE pur_req;
3227 BEGIN
3228 --gscc fix
3229 SELECT to_date(l_temp_doc_date, 'mm/dd/yyrr')
3230 INTO l_doc_date
3231 FROM DUAL;
3232
3233 EXCEPTION WHEN OTHERS THEN
3234 l_select :=
3235 'SELECT creation_date, creation_date, created_by
3236 FROM po_requisition_headers_all
3237 WHERE requisition_header_id = '||to_number(p_reference2) ;
3238
3239 OPEN pur_req FOR l_select ;
3240 FETCH pur_req INTO l_doc_date,
3241 l_doc_creation_date,
3242 l_doc_created_by;
3243 CLOSE pur_req;
3244 END ;
3245
3246 ELSE -- g_req_date_seg is null
3247 l_select :=
3248 'SELECT creation_date, creation_date, created_by
3249 FROM po_requisition_headers_all
3250 WHERE requisition_header_id = '||to_number(p_reference2) ;
3251
3252 OPEN pur_req FOR l_select ;
3253 FETCH pur_req INTO l_doc_date,
3254 l_doc_creation_date,
3255 l_doc_created_by;
3256 CLOSE pur_req;
3257 END IF;
3258
3259 ELSIF p_je_category_name = 'Purchases'
3260 THEN
3261 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3262 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PURCHASES ...');
3263 END IF;
3264 l_document_num := p_reference4;
3265
3266 IF g_pur_order_date_seg IS NOT NULL
3267 THEN
3268 l_gl_date := NULL;
3269 l_select :=
3270 'SELECT h.'||g_pur_order_date_seg||', h.creation_date, h.created_by, d.gl_encumbered_date
3271 FROM po_headers_all h,
3272 po_distributions_all d
3273 WHERE h.po_header_id = '||p_reference2 ||'
3274 AND h.po_header_id = d.po_header_id
3275 AND d.po_distribution_id = '||p_reference3;
3276
3277 OPEN pur_pur FOR l_select ;
3278 FETCH pur_pur INTO l_temp_doc_date,
3279 l_doc_creation_date,
3280 l_doc_created_by,
3281 l_gl_date;
3282 CLOSE pur_pur;
3283
3284 BEGIN
3285 --gscc fix
3286 SELECT to_date(l_temp_doc_date, 'mm/dd/yyrr')
3287 INTO l_doc_date
3288 FROM DUAL;
3289
3290 EXCEPTION WHEN OTHERS THEN
3291 l_gl_date := NULL;
3292 l_select :=
3293 'SELECT h.creation_date, h.creation_date, h.created_by, d.gl_encumbered_date
3294 FROM po_headers_all h,
3295 po_distributions_all d
3296 WHERE h.po_header_id = '||p_reference2 ||'
3297 AND h.po_header_id = d.po_header_id
3298 AND d.po_distribution_id = '||p_reference3;
3299
3300 OPEN pur_pur FOR l_select ;
3301 FETCH pur_pur INTO l_doc_date,
3302 l_doc_creation_date,
3303 l_doc_created_by,
3304 l_gl_date;
3305 CLOSE pur_pur;
3306 END ;
3307
3308 ELSE -- g_pur_order_date_seg is null
3309 l_gl_date := NULL;
3310 l_select :=
3311 'SELECT h.creation_date, h.creation_date, h.created_by, d.gl_encumbered_date
3312 FROM po_headers_all h,
3313 po_distributions_all d
3314 WHERE h.po_header_id = '||p_reference2 ||'
3315 AND h.po_header_id = d.po_header_id
3316 AND d.po_distribution_id = '||p_reference3;
3317
3318 OPEN pur_pur FOR l_select ;
3319 FETCH pur_pur INTO l_doc_date,
3320 l_doc_creation_date,
3321 l_doc_created_by,
3322 l_gl_date;
3323 CLOSE pur_pur;
3324 END IF;
3325 IF (l_gl_date IS NOT NULL) THEN
3326 p_gl_date := l_gl_date;
3327 END IF;
3328
3329 ELSIF p_je_category_name = 'Receiving'
3330 THEN
3331 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3332 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' RECEIVING ...');
3333 END IF;
3334 IF g_rec_trxn_date_seg IS NOT NULL
3335 THEN
3336 l_select :=
3337 'SELECT rt.'||g_rec_trxn_date_seg||',
3338 rcv.receipt_num,
3339 rt.creation_date,
3340 rt.created_by
3341 FROM rcv_transactions rt,
3342 rcv_shipment_headers rcv
3343 WHERE rt.shipment_header_id = rcv.shipment_header_Id
3344 AND rt.transaction_id = '||to_number(p_reference5) ;
3345
3346 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
3347 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,L_SELECT);
3348 END IF;
3349
3350 OPEN pur_rec FOR l_select ;
3351 FETCH pur_rec INTO l_temp_doc_date,
3352 l_document_num,
3353 l_doc_creation_date,
3354 l_doc_created_by;
3355 CLOSE pur_rec;
3356 BEGIN
3357 --gscc fix
3358 SELECT to_date(l_temp_doc_date, 'mm/dd/yyrr')
3359 INTO l_doc_date
3360 FROM DUAL;
3361
3362 EXCEPTION WHEN OTHERS THEN
3363 l_select :=
3364 'SELECT rt.transaction_date,
3365 rcv.receipt_num,
3366 rt.creation_date,
3367 rt.created_by
3368 FROM rcv_transactions rt,
3369 rcv_shipment_headers rcv
3370 WHERE rt.shipment_header_id = rcv.shipment_header_Id
3371 AND rt.transaction_id = '||to_number(p_reference5);
3372
3373 OPEN pur_rec FOR l_select ;
3374 FETCH pur_rec INTO l_doc_date,
3375 l_document_num,
3376 l_doc_creation_date,
3377 l_doc_created_by;
3378 CLOSE pur_rec;
3379 END ;
3380
3381 ELSE -- g_rec_trxn_date_seg is null
3382 l_select :=
3383 'SELECT rt.transaction_date,
3384 rcv.receipt_num,
3385 rt.creation_date,
3386 rt.created_by
3387 FROM rcv_transactions rt,
3388 rcv_shipment_headers rcv
3389 WHERE rt.shipment_header_id = rcv.shipment_header_Id
3390 AND rt.transaction_id = '||to_number(p_reference5) ;
3391 OPEN pur_rec FOR l_select ;
3392 FETCH pur_rec INTO l_doc_date,
3393 l_document_num,
3394 l_doc_creation_date,
3395 l_doc_created_by;
3396 CLOSE pur_rec;
3397 END IF;
3398
3399 ELSE
3400 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3401 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' OTHERS ...');
3402 END IF;
3403 l_document_num := p_name;
3404 l_doc_date := p_date;
3405 l_doc_creation_date := p_creation_date;
3406 l_doc_created_by := p_created_by;
3407 END IF;
3408 -- Code for Payables
3409 ELSIF p_je_source_name = 'Payables'
3410 THEN
3411 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3412 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PAYABLES ...');
3413 END IF;
3414 IF p_je_category_name = 'Purchase Invoices'
3415 THEN
3416 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3417 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PURCHASE INVOICES ...');
3418 END IF;
3419 OPEN Pay_Pur;
3420 FETCH Pay_Pur INTO l_document_num,
3421 l_doc_date,
3422 l_doc_creation_date,
3423 l_doc_created_by;
3424 if g_src_flag = '1' then
3425 l_document_num := p_reference4;
3426 End if;
3427
3428 CLOSE Pay_Pur;
3429
3430 IF (NVL(p_reference3, '-100') = '-100') THEN
3431 BEGIN
3432 l_event_type_code := NULL;
3433 SELECT e.event_type_code
3434 INTO l_event_type_code
3435 FROM ap_ae_lines_all l,
3436 ap_ae_headers_all h,
3437 ap_accounting_events_all e
3438 WHERE l.source_table = 'AP_INVOICES'
3439 AND l.source_id = p_reference2
3440 AND l.ae_header_id = h.ae_header_id
3441 AND l.gl_sl_link_id = p_gl_sl_link_id
3442 AND e.accounting_event_id = h.accounting_event_id;
3443 EXCEPTION
3444 WHEN NO_DATA_FOUND THEN
3445 l_event_type_code := NULL;
3446 END;
3447 IF (l_event_type_code = 'INVOICE CANCELLATION') THEN
3448 p_reversed := 'R';
3449 END IF;
3450 ELSE
3451 BEGIN
3452 l_parent_reversal_id := NULL;
3453 SELECT a.parent_reversal_id
3454 INTO l_parent_reversal_id
3455 FROM ap_invoice_distributions a
3456 WHERE a.invoice_id = p_reference2
3457 AND a.distribution_line_number = p_reference3;
3458 EXCEPTION
3459 WHEN NO_DATA_FOUND THEN
3460 l_parent_reversal_id := NULL;
3461 END;
3462
3463 IF (l_parent_reversal_id IS NOT NULL) THEN
3464 p_reversed := 'R';
3465 END IF;
3466 END IF;
3467
3468 ELSIF p_je_category_name = 'Payments'
3469 THEN
3470 OPEN Pay_Pay_Check;
3471 FETCH Pay_Pay_Check INTO l_void_date, l_check_date;
3472 CLOSE Pay_Pay_Check;
3473 IF (l_void_date IS NULL)
3474 THEN
3475 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3476 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PAYMENTS ...');
3477 END IF;
3478 OPEN Pay_Pay;
3479 FETCH Pay_Pay INTO l_document_num, l_doc_date,
3480 l_doc_creation_date,
3481 l_doc_created_by;
3482 CLOSE Pay_Pay;
3483 ELSE
3484 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3485 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PAYMENTS VOID HANDLING ...');
3486 END IF;
3487 OPEN Pay_Pay_Void;
3488 FETCH Pay_Pay_Void INTO l_inv_payment_id;
3489 CLOSE Pay_Pay_Void;
3490 IF (l_inv_payment_id <> 0)
3491 THEN
3492 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3493 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PAYMENTS NON-VOID ...');
3494 END IF;
3495 OPEN Pay_Pay_Non_Void;
3496 FETCH Pay_Pay_Non_Void INTO l_document_num,
3497 l_doc_creation_date,
3498 l_doc_created_by;
3499 CLOSE Pay_Pay_Non_Void;
3500 l_doc_date := l_check_date;
3501 ELSIF (l_inv_payment_id = 0)
3502 THEN
3503 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3504 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PAYMENTS VOID ...');
3505 END IF;
3506 OPEN Pay_Pay_Void_Values;
3507 FETCH Pay_Pay_Void_Values INTO l_document_num,
3508 l_doc_creation_date,
3509 l_doc_created_by;
3510 CLOSE Pay_Pay_Void_Values;
3511 l_doc_date := l_void_date;
3512 END IF;
3513 END IF;
3514
3515 IF (NVL(p_reference3, '-100') <> '-100') THEN
3516 BEGIN
3517 l_event_type_code := NULL;
3518 SELECT e.event_type_code
3519 INTO l_event_type_code
3520 FROM ap_ae_lines_all l,
3521 ap_ae_headers_all h,
3522 ap_accounting_events_all e
3523 WHERE l.source_table = 'AP_INVOICE_PAYMENTS'
3524 AND l.source_id = p_reference9
3525 AND l.ae_header_id = h.ae_header_id
3526 AND l.gl_sl_link_id = p_gl_sl_link_id
3527 AND e.accounting_event_id = h.accounting_event_id;
3528 EXCEPTION
3529 WHEN NO_DATA_FOUND THEN
3530 l_event_type_code := NULL;
3531 END;
3532 IF (l_event_type_code = 'PAYMENT CANCELLATION') THEN
3533 p_reversed := 'R';
3534 END IF;
3535 END IF;
3536
3537 ELSIF p_je_category_name = 'Treasury Confirmation'
3538 AND upper(p_name) not like '%VOID%' THEN
3539 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3540 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TREASURY CONFIRMATION ...');
3541 END IF;
3542 OPEN Pay_Treas;
3543 FETCH Pay_Treas INTO l_document_num,
3544 l_doc_date,
3545 l_doc_creation_date,
3546 l_doc_created_by;
3547 CLOSE Pay_Treas;
3548 ELSIF p_je_category_name = 'Treasury Confirmation'
3549 AND upper(p_name) like '%VOID%' THEN
3550 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3551 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TREASURY CONFIRMATION VOID ...');
3552 END IF;
3553 OPEN Pay_Treas_Check;
3554 FETCH Pay_Treas_Check INTO l_doc_date, l_document_num;
3555 CLOSE Pay_Treas_Check;
3556 OPEN Pay_Treas_Void;
3557 FETCH Pay_Treas_Void INTO l_doc_creation_date,
3558 l_doc_created_by;
3559 CLOSE Pay_Treas_Void;
3560 ELSE
3561 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3562 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' OTHERS ...');
3563 END IF;
3564 l_document_num := p_name;
3565 l_doc_date := p_date;
3566 l_doc_creation_date := p_creation_date;
3567 l_doc_created_by := p_created_by;
3568 END IF;
3569 -- Code for Receivables
3570 ELSIF p_je_source_name = 'Receivables'
3571 THEN
3572 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3573 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' RECEIVABLES ...');
3574 END IF;
3575 l_refer2 := p_reference2;
3576 l_document_num := p_reference4;
3577 IF (p_reference2 is null)
3578 THEN
3579 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3580 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REF2 IS NULL ...');
3581 END IF;
3582 l_document_num := l_refer4;
3583 ELSE
3584 IF (p_je_category_name = 'Adjustment')
3585 THEN
3586 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3587 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ADJUSTMENT ...');
3588 END IF;
3589 OPEN Receivables_Adjustment;
3590 FETCH Receivables_Adjustment INTO l_doc_date,
3591 l_doc_creation_date,
3592 l_doc_created_by;
3593 CLOSE Receivables_Adjustment;
3594 ELSIF (p_je_category_name = 'Credit Memo Applications')
3595 THEN
3596 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3597 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CREDIT MEMO APPLICATIONS ...');
3598 END IF;
3599 OPEN Receivables_CMA;
3600 FETCH Receivables_CMA INTO l_doc_date,
3601 l_doc_creation_date,
3602 l_doc_created_by;
3603 CLOSE Receivables_CMA;
3604 ELSIF (p_je_category_name IN ('Credit Memos',
3605 'Debit Memos', 'Sales Invoices'))
3606 THEN
3607 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3608 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CREDIT MEMOS/'||
3609 'Debit Memos/ Sales Invoices ...');
3610 END IF;
3611 OPEN Receivables_Memos_Inv;
3612 FETCH Receivables_Memos_Inv INTO l_doc_date,
3613 l_doc_creation_date,
3614 l_doc_created_by;
3615 CLOSE Receivables_Memos_Inv;
3616 ELSE
3617 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3618 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'TRADE RECEIPTS/ MISC RECEIPTS/ '||
3619 'Reversals/ Others ...');
3620 END IF;
3621 l_cash_receipt_hist_id := SUBSTR(p_ref2, INSTR(p_ref2,'C')+1,
3622 LENGTH(p_ref2));
3623
3624 IF (p_je_category_name = 'Misc Receipts')
3625 THEN
3626 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3627 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING A MISC RECEIPT');
3628 END IF;
3629 l_refer2 := p_ref2;
3630 l_cash_receipt_hist_id := p_reference5;
3631 ELSE
3632 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3633 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PROCESSING A TRADE RECEIPT '||
3634 'or Other');
3635 END IF;
3636 l_refer2 := p_reference2;
3637 l_cash_receipt_hist_id := SUBSTR(p_ref2, INSTR(p_ref2,'C')+1,
3638 LENGTH(p_ref2));
3639 END IF;
3640 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3641 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASH RECEIPT ID = '||L_REFER2);
3642 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASH RECEIPT HIST ID = ' ||
3643 l_cash_receipt_hist_id);
3644 END IF;
3645 l_receipt_hist_status := NULL;
3646 OPEN Receivables_Hist (TO_NUMBER(l_cash_receipt_hist_id),
3647 TO_NUMBER(l_refer2));
3648 FETCH Receivables_Hist INTO l_rev_exists, l_receipt_hist_status;
3649 CLOSE Receivables_Hist;
3650 IF (l_receipt_hist_status = 'REVERSED') THEN
3651 p_reversed := 'R';
3652 END IF;
3653
3654 IF (l_rev_exists = 'N')
3655 THEN
3656 l_doc_creation_date_d := NULL;
3657 l_doc_created_by_d := NULL;
3658 IF (p_je_category_name = 'Misc Receipts')
3659 THEN
3660 l_rev_exists := 'M';
3661 ELSE
3662 l_rev_exists := 'C';
3663 END IF;
3664 ELSE
3665 l_rev_exists := 'N';
3666 OPEN Receivables_History (TO_NUMBER(l_cash_receipt_hist_id));
3667 FETCH Receivables_History into l_rev_exists,
3668 l_doc_creation_date_d,
3669 l_doc_created_by_d;
3670 CLOSE Receivables_History;
3671
3672 IF (l_rev_exists = 'Y')
3673 THEN
3674 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3675 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASH RECEIPT HIST ID EXITS IN'||
3676 ' Ar_Cash_Receipt_History_All ... REVERSAL');
3677 END IF;
3678 END IF;
3679 END IF;
3680 IF (p_je_category_name <> 'Misc Receipts') AND (l_rev_exists = 'C')
3681 THEN
3682
3683 -- Find out IF Reference_2 contains Receivable_Application_Id
3684 OPEN Receivables_Applications;
3685 FETCH Receivables_Applications into l_temp_cr_hist_id;
3686 CLOSE Receivables_Applications;
3687 IF (l_temp_cr_hist_id IS NOT NULL)
3688 THEN
3689 l_cash_receipt_hist_id := l_temp_cr_hist_id;
3690 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3691 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASH RECEIPT HIST ID EXITS IN'
3692 ||' Ar_Receivable_Applications_All: '
3693 ||l_cash_receipt_hist_id);
3694 END IF;
3695 -- Use cash_receipt_history_id obtained above to find
3696 -- IF a row exits in Ar_Cash_Receipts_All
3697 OPEN Receivables_Exists;
3698 FETCH Receivables_Exists INTO l_rev_exists;
3699 CLOSE Receivables_Exists;
3700 IF (l_rev_exists = 'Y')
3701 THEN
3702 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3703 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASH RECEIPT HIST ID EXITS IN'
3704 ||' Ar_Cash_Receipt_History_All: '
3705 ||l_cash_receipt_hist_id);
3706 END IF;
3707 l_rev_exists := 'N';
3708
3709 OPEN Receivables_Hist (TO_NUMBER(l_cash_receipt_hist_id),
3710 TO_NUMBER(l_refer2));
3711 FETCH Receivables_Hist INTO l_dummy_rev_exists, l_receipt_hist_status;
3712 CLOSE Receivables_Hist;
3713 IF (l_receipt_hist_status = 'REVERSED') THEN
3714 p_reversed := 'R';
3715 END IF;
3716
3717 -- Select the document info from
3718 -- AR_CASH_RECEIPT_HISTORY_All table
3719 OPEN Receivables_History (TO_NUMBER(l_cash_receipt_hist_id));
3720 FETCH Receivables_History into l_rev_exists,
3721 l_doc_creation_date_d,
3722 l_doc_created_by_d;
3723 CLOSE Receivables_History;
3724 IF (l_rev_exists = 'Y')
3725 THEN
3726 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3727 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REVERSAL CASH RECEIPT '||
3728 'Hist Id'||
3729 ' exists ... REVERSAL');
3730 END IF;
3731 END IF;
3732 END IF;
3733 END IF; -- End IF for l_temp_cr_hist_id
3734 ELSIF (p_je_category_name = 'Misc Receipts')
3735 AND (l_rev_exists = 'M')
3736 THEN
3737 -- Find out IF Reference_2 contains Misc_Cash_Distribution_Id
3738 IF (p_rec_public_law_code_col IS NOT NULL) THEN
3739 l_rec_public_law_code := NULL;
3740 l_select := 'SELECT ''Y'', '||p_rec_public_law_code_col||'
3741 FROM ar_misc_cash_distributions_all
3742 WHERE misc_cash_distribution_id = '||to_number(l_cash_receipt_hist_id);
3743 OPEN Receivables_Distrib FOR l_select;
3744 FETCH Receivables_Distrib into l_rev_exists, l_rec_public_law_code;
3745 CLOSE Receivables_Distrib;
3746 p_rec_public_law_code := l_rec_public_law_code;
3747 ELSE
3748 p_rec_public_law_code := NULL;
3749 l_select := 'SELECT ''Y''
3750 FROM ar_misc_cash_distributions_all
3751 WHERE misc_cash_distribution_id = '||to_number(l_cash_receipt_hist_id);
3752 OPEN Receivables_Distrib FOR l_select;
3753 FETCH Receivables_Distrib into l_rev_exists;
3754 CLOSE Receivables_Distrib;
3755 END IF;
3756
3757 IF (l_rev_exists = 'Y')
3758 THEN
3759 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3760 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CASH RECEIPT HIST ID EXITS IN '
3761 ||'Ar_Misc_Cash_Distributions_All: '
3762 ||l_cash_receipt_hist_id);
3763 END IF;
3764 l_rev_exists := 'N';
3765 -- Select the document info
3766 -- from Ar_Misc_Cash_Distributions_All table
3767 IF (p_rec_public_law_code_col IS NOT NULL) THEN
3768 l_rec_public_law_code := NULL;
3769 l_select := ' SELECT ''Y'', creation_date, created_by, '||p_rec_public_law_code_col||'
3770 FROM ar_misc_cash_distributions_all
3771 WHERE misc_cash_distribution_id = '||l_cash_receipt_hist_id||'
3772 AND created_from = ''ARP_REVERSE_RECEIPT.REVERSE''';
3773 OPEN Receivables_Misc FOR l_select;
3774 FETCH Receivables_Misc into l_rev_exists,
3775 l_doc_creation_date_d,
3776 l_doc_created_by_d,
3777 l_rec_public_law_code;
3778 CLOSE Receivables_Misc;
3779 IF (p_rec_public_law_code IS NULL) THEN
3780 p_rec_public_law_code := l_rec_public_law_code;
3781 END IF;
3782 ELSE
3783 p_rec_public_law_code := NULL;
3784 l_select := ' SELECT ''Y'', creation_date, created_by
3785 FROM ar_misc_cash_distributions_all
3786 WHERE misc_cash_distribution_id = '||l_cash_receipt_hist_id||'
3787 AND created_from = ''ARP_REVERSE_RECEIPT.REVERSE''';
3788 OPEN Receivables_Misc FOR l_select;
3789 FETCH Receivables_Misc into l_rev_exists,
3790 l_doc_creation_date_d,
3791 l_doc_created_by_d;
3792 CLOSE Receivables_Misc;
3793 END IF;
3794
3795 IF (l_rev_exists = 'Y')
3796 THEN
3797 p_reversed := 'R';
3798 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3799 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' MISC CASH DISC ID HAS'||
3800 ' Reverse value in created ' ||
3801 'from ... REVERSAL');
3802 END IF;
3803 END IF;
3804 END IF;
3805 END IF; -- End IF for l_rev_exists = C/M
3806 OPEN Receivables;
3807 FETCH Receivables INTO l_doc_date,
3808 l_doc_creation_date_d,
3809 l_doc_created_by_d;
3810 CLOSE Receivables;
3811 l_doc_creation_date := l_doc_creation_date_d;
3812 l_doc_created_by := l_doc_created_by_d;
3813 END IF; -- End IF for p_je_category_name
3814 END IF; -- End IF for p_reference2
3815 IF (p_je_category_name = 'Misc Receipts') THEN
3816 IF ((p_rec_public_law_code_col IS NOT NULL) AND (p_rec_public_law_code IS NULL)) THEN
3817 l_rec_public_law_code := NULL;
3818 l_select := 'SELECT '||p_rec_public_law_code_col||'
3819 FROM ar_misc_cash_distributions_all
3820 WHERE misc_cash_distribution_id = '||to_number(l_cash_receipt_hist_id);
3821 OPEN Receivables_Distrib FOR l_select;
3822 FETCH Receivables_Distrib into l_rec_public_law_code;
3823
3824 CLOSE Receivables_Distrib;
3825 IF (p_rec_public_law_code IS NULL) THEN
3826 p_rec_public_law_code := l_rec_public_law_code;
3827 END IF;
3828 END IF;
3829 END IF;
3830
3831 -- Code for Budgetary Transaction
3832 ELSIF p_je_source_name = 'Budgetary Transaction'
3833 THEN
3834 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3835 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BUDGETARY TRANSACTION ...');
3836 END IF;
3837 fnd_file.put_line (fnd_file.log, 'Budget p_reference_1 = '||p_reference1);
3838 OPEN Budget_Transac;
3839 FETCH Budget_Transac INTO l_document_num,
3840 l_doc_date,
3841 l_doc_creation_date,
3842 l_doc_created_by;
3843 CLOSE Budget_Transac;
3844 fnd_file.put_line (fnd_file.log, 'Budget l_document_num = '||l_document_num);
3845 p_gl_date := l_doc_date;
3846 -- Code for Manual
3847 ELSIF p_je_source_name = 'Manual'
3848 THEN
3849 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3850 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' MANUAL ...');
3851 END IF;
3852 OPEN Manual;
3853 FETCH Manual INTO l_doc_date;
3854 CLOSE Manual;
3855 --Bug#3225337
3856 --IF (p_reference4 IS NOT NULL)
3857 IF (NVL(p_reference4, '-100') <> '-100')
3858 THEN
3859 l_document_num := p_reference4;
3860 ELSE
3861 l_document_num := p_name;
3862 END IF;
3863 l_doc_creation_date := p_creation_date;
3864 l_doc_created_by := p_created_by;
3865 -- Code for Misc
3866 ELSE
3867 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3868 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' OTHERS ...');
3869 END IF;
3870 --Bug#3225337
3871 --IF (p_reference4 IS NOT NULL)
3872 IF (NVL(p_reference4, '-100') <> '-100')
3873 THEN
3874 l_document_num := p_reference4;
3875 ELSE
3876 l_document_num := p_name;
3877 END IF;
3878 l_doc_date := p_date;
3879 l_doc_creation_date := p_creation_date;
3880 l_doc_created_by := p_created_by;
3881 END IF; -- End IF for p_je_source_name
3882 -- Check for values. IF not put default
3883 IF l_document_num IS NULL
3884 THEN
3885 l_document_num := p_name;
3886 END IF;
3887 IF l_doc_date IS NULL
3888 THEN
3889 l_doc_date := p_date;
3890 END IF;
3891 IF l_doc_creation_date IS NULL
3892 THEN
3893 l_doc_creation_date := p_creation_date;
3894 END IF;
3895 IF l_doc_created_by IS NULL
3896 THEN
3897 l_doc_created_by := p_created_by;
3898 END IF;
3899 -- Set the out varibales
3900 p_doc_num := l_document_num;
3901 p_doc_date := l_doc_date;
3902 p_doc_creation_date := l_doc_creation_date;
3903 p_doc_created_by := l_doc_created_by;
3904 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3905 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOCUMENT NUMBER - '||L_DOCUMENT_NUM);
3906 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOCUMENT DATE - '||L_DOC_DATE);
3907 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOCUMENT CREATION DATE - '||
3908 l_doc_creation_date);
3909 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOCUMENT CREATED BY - '||L_DOC_CREATED_BY);
3910 END IF;
3911
3912 fnd_file.put_line (fnd_file.log, '<<<<<<OUT>>>>>>');
3913 fnd_file.put_line (fnd_file.log, 'p_doc_num='||p_doc_num);
3914 fnd_file.put_line (fnd_file.log, 'p_doc_date='||p_doc_date);
3915 fnd_file.put_line (fnd_file.log, 'p_doc_creation_date='||p_doc_creation_date);
3916 fnd_file.put_line (fnd_file.log, 'p_doc_created_by='||p_doc_created_by);
3917 fnd_file.put_line (fnd_file.log, 'p_gl_date='||p_gl_date);
3918 fnd_file.put_line (fnd_file.log, 'p_rec_public_law_code='||p_rec_public_law_code);
3919 fnd_file.put_line (fnd_file.log, 'p_reversed='||p_reversed);
3920 fnd_file.put_line (fnd_file.log, 'END GET_DOC_INFO');
3921
3922
3923 EXCEPTION
3924 WHEN OTHERS THEN
3925 g_error_code := SQLCODE;
3926 g_error_buf := SQLERRM ||
3927 ' Error in Get_Doc_Info Procedure.' ;
3928 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
3929 RETURN;
3930 END GET_DOC_INFO;
3931 -- -------------------------------------------------------------------
3932 -- PROCEDURE GET_DOC_USER
3933 -- -------------------------------------------------------------------
3934 -- Called from following procedures:
3935 -- Journal_Process
3936 -- Purpose:
3937 -- Determine the user who created the journal line being processed
3938 -- Also format the creation_date
3939 -- -------------------------------------------------------------------
3940 PROCEDURE GET_DOC_USER (p_created_by IN Number,
3941 p_entry_user OUT NOCOPY Varchar2)
3942 IS
3943 l_module_name VARCHAR2(200);
3944 BEGIN
3945 l_module_name := g_module_name || 'GET_DOC_USER';
3946 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3947 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
3948 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENTERING GET DOC USER ...');
3949 END IF;
3950 BEGIN
3951 SELECT user_name
3952 INTO p_entry_user
3953 FROM fnd_user
3954 WHERE user_id = p_created_by;
3955 EXCEPTION
3956 WHEN NO_DATA_FOUND
3957 THEN
3958 p_entry_user := NULL;
3959 END;
3960 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3961 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOCUMENT CREATED BY - '||P_ENTRY_USER);
3962 END IF;
3963 -- Setting up the retcode
3964 g_error_code := 0;
3965 EXCEPTION
3966 WHEN OTHERS THEN
3967 g_error_code := SQLCODE ;
3968 g_error_buf := SQLERRM ||
3969 ' -- Error in Get_Doc_User procedure.' ;
3970 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
3971 RETURN;
3972 END GET_DOC_USER;
3973 -- -------------------------------------------------------------------
3974 -- PROCEDURE RESET_ATTRIBUTES
3975 -- The Process resets the values of all the FACTS Attributes
3976 -- -------------------------------------------------------------------
3977 -- ------------------------------------------------------------------
3978 Procedure RESET_FACTS_ATTRIBUTES IS
3979 l_module_name VARCHAR2(200);
3980 Begin
3981 l_module_name := g_module_name || 'RESET_FACTS_ATTRIBUTES';
3982 -- Reset all the Attribute Variable
3983 g_balance_type_flag := Null ;
3984 g_public_law_code_flag := Null ;
3985 g_reimburseable_flag := Null ;
3986 g_availability_flag := Null ;
3987 g_bea_category_flag := Null ;
3988 g_appor_cat_flag := Null ;
3989 g_transaction_partner_val := Null ;
3990 g_borrowing_source_flag := Null ;
3991 g_def_indef_flag := Null ;
3992 g_legis_ind_flag := Null ;
3993 g_authority_type_flag := Null ;
3994 g_year_budget_auth := Null ;
3995 g_deficiency_flag := Null ;
3996 g_function_flag := Null ;
3997 g_balance_type_val := Null ;
3998 g_def_indef_val := Null ;
3999 g_public_law_code_val := Null ;
4000 g_appor_cat_val := Null ;
4001 g_authority_type_val := Null ;
4002 g_reimburseable_val := Null ;
4003 g_bea_category_val := Null ;
4004 g_borrowing_source_val := Null ;
4005 g_availability_val := Null ;
4006 g_legis_ind_val := Null ;
4007 g_function_flag := NULL ;
4008 g_transfer_ind := NULL ;
4009 g_transfer_dept_id := NULL ;
4010 g_transfer_main_acct := NULL ;
4011 g_budget_function_val := NULL ;
4012 g_advance_type_val := NULL ;
4013 g_govt_non_govt_val := NULL ;
4014 g_govt_non_govt_ind := NULL ;
4015 g_exch_non_exch_val := NULL ;
4016 g_exch_non_exch_ind := NULL ;
4017 g_cust_non_cust_val := NULL ;
4018 g_cust_non_cust_ind := NULL ;
4019 g_budget_subfunction_ind := NULL ;
4020 g_budget_subfunction_val := NULL ;
4021 g_attributes_found := NULL ;
4022
4023 EXCEPTION
4024 WHEN OTHERS THEN
4025 g_error_code := SQLCODE ;
4026 g_error_buf := SQLERRM;
4027 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4028 RAISE;
4029
4030 END reset_facts_attributes ;
4031 -- -------------------------------------------------------------------
4032 -- PROCEDURE GET_USSGL_INFO
4033 -- -------------------------------------------------------------------
4034 -- Gets the information like enabled flag and reporting type
4035 -- for the passed account number.
4036 -- -------------------------------------------------------------------
4037 PROCEDURE GET_USSGL_INFO (p_ussgl_acct_num IN VARCHAR2,
4038 p_enabled_flag IN OUT NOCOPY VARCHAR2,
4039 p_reporting_type IN OUT NOCOPY VARCHAR2)
4040 IS
4041 l_module_name VARCHAR2(200);
4042 l_enabled_flag VARCHAR2(1);
4043 l_reporting_type VARCHAR2(1);
4044 BEGIN
4045 l_module_name := g_module_name || 'GET_USSGL_INFO';
4046 SELECT ussgl_enabled_flag,
4047 reporting_type
4048 INTO l_enabled_flag,
4049 l_reporting_type
4050 FROM fv_facts_ussgl_accounts
4051 WHERE ussgl_account = p_ussgl_acct_num;
4052
4053 p_enabled_flag := l_enabled_flag;
4054 p_reporting_type := l_reporting_type;
4055 EXCEPTION
4056 WHEN NO_DATA_FOUND THEN
4057 -- Account Number not found in FV_FACTS_USSGL_ACCOUNTS table.
4058 -- Return Nulls.
4059 p_enabled_flag := NULL;
4060 p_reporting_type := NULL;
4061 WHEN OTHERS THEN
4062 g_error_code := sqlcode ;
4063 g_error_buf := sqlerrm ;
4064 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4065 RETURN ;
4066 END get_ussgl_info ;
4067 -- --------------------------------------------------------------------
4068 -- PROCEDURE GET_FUND_GROUP_INFO
4069 -- --------------------------------------------------------------------
4070 -- Its primary purpose get the fund Group, Dept Id, bureau Id and
4071 -- balancing segment from the fv_fund_parameters table for the
4072 -- passed Code Combination Id.
4073 -- --------------------------------------------------------------------
4074 PROCEDURE get_fund_group_info (p_ccid gl_balances.code_combination_id%TYPE,
4075 p_fund_group IN OUT NOCOPY VARCHAR2,
4076 p_dept_id IN OUT NOCOPY VARCHAR2,
4077 p_bureau_id IN OUT NOCOPY VARCHAR2,
4078 p_bal_segment IN OUT NOCOPY VARCHAR2)
4079 IS
4080 l_module_name VARCHAR2(200);
4081 l_ret_val BOOLEAN := TRUE;
4082 l_fund_cursor INTEGER;
4083 l_fund_select VARCHAR2(2000);
4084 l_fund_fetchn INTEGER;
4085 l_exec_ret INTEGER;
4086 l_row_exists VARCHAR2(1) := NULL;
4087 BEGIN
4088 l_module_name := g_module_name || 'get_fund_group_info';
4089 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4090 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'');
4091 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'ENTERING GET FUND GROUP INFO ...');
4092 END IF;
4093 g_error_code := 0;
4094 g_error_buf := NULL;
4095
4096 l_fund_select := 'SELECT ''X'', fts.fund_group_code, fts.department_id, ' ||
4097 'fts.bureau_id, ' ||
4098 'glc.' || g_bal_segment_name || ' ' ||
4099 'FROM gl_code_combinations glc, fv_fund_parameters ffp, ' ||
4100 'fv_treasury_symbols fts ' ||
4101 'WHERE glc.code_combination_id = :ccid
4102 AND glc.chart_of_accounts_id = :coa_id
4103 AND ffp.treasury_symbol_id = fts.treasury_symbol_id
4104 AND ffp.set_of_books_id = :set_of_books_id
4105 AND glc.' || g_bal_segment_name || ' = ffp.fund_value';
4106
4107 BEGIN
4108 EXECUTE IMMEDIATE l_fund_select INTO l_row_exists, p_fund_group,
4109 p_dept_id, p_bureau_id,
4110 p_bal_segment
4111 USING p_ccid, g_coa_id, g_set_of_books_id;
4112 EXCEPTION
4113 WHEN NO_DATA_FOUND THEN
4114 NULL;
4115 WHEN OTHERS THEN
4116 g_error_code := sqlcode;
4117 g_error_buf := sqlerrm;
4118 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_1',g_error_buf);
4119 END;
4120
4121 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4122 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' L_ROW_EXISTS: '||L_ROW_EXISTS);
4123 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' P_FUND_GROUP: '||P_FUND_GROUP);
4124 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' P_DEPT_ID: '||P_DEPT_ID);
4125 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' P_BUREAU_ID: '||P_BUREAU_ID);
4126 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' P_BAL_SEGMENT: '||P_BAL_SEGMENT);
4127 END IF;
4128 IF (l_row_exists IS NULL)
4129 THEN
4130 p_fund_group := NULL;
4131 p_dept_id := NULL;
4132 p_bureau_id := NULL;
4133 DECLARE
4134 l_ret_val Boolean := TRUE;
4135 l_bal_select Varchar2(2000);
4136 l_bal_fetch Integer;
4137 l_exec_ret Integer;
4138 BEGIN
4139 l_bal_select := 'SELECT glc.' || g_bal_segment_name || ' '
4140 ||'FROM gl_code_combinations glc '
4141 ||'WHERE glc.code_combination_id = ' || to_char(p_ccid);
4142 BEGIN
4143 EXECUTE IMMEDIATE l_bal_select INTO p_bal_segment;
4144 EXCEPTION
4145 WHEN NO_DATA_FOUND THEN
4146 NULL;
4147 WHEN OTHERS THEN
4148 g_error_code := sqlcode;
4149 g_error_buf := sqlerrm;
4150 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_2',g_error_buf);
4151 END;
4152 EXCEPTION
4153 WHEN OTHERS THEN
4154 g_error_code := sqlcode;
4155 g_error_buf := sqlerrm;
4156 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_3',g_error_buf);
4157 END;
4158 ELSIF p_bureau_id IS NULL THEN
4159 p_bureau_id := '00';
4160 END IF ;
4161 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4162 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'LEAVING GET FUND GROUP INFO ...');
4163 END IF;
4164 EXCEPTION
4165 WHEN NO_DATA_FOUND THEN
4166 g_error_buf := 'Get Fund Group Info: NO DATA FOUND for ccid : ' || p_ccid;
4167 WHEN OTHERS THEN
4168 DBMS_SQL.CLOSE_CURSOR(l_fund_cursor);
4169 g_error_code := 2 ;
4170 g_error_buf := 'GET FUND GROUP INFO - Exception (Others) - ' ||
4171 to_char(sqlcode) || ' - ' || sqlerrm ;
4172 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4173 END get_fund_group_info ;
4174 -- --------------------------------------------------------------------
4175 -- PROCEDURE POPULATE_TABLE
4176 -- --------------------------------------------------------------------
4177 -- This procedure gets called from procedure Journal_Process.
4178 -- Its main purpose is to insert records in FV_FACTS_TRX_TEMP table.
4179 -- --------------------------------------------------------------------
4180 PROCEDURE populate_table
4181 ( p_treasury_symbol_id NUMBER ,
4182 p_set_of_books_id NUMBER ,
4183 p_code_combination_id NUMBER ,
4184 p_fund_value VARCHAR2,
4185 p_account_number VARCHAR2,
4186 p_document_source VARCHAR2,
4187 p_document_category VARCHAR2,
4188 p_document_number VARCHAR2,
4189 p_transaction_date DATE,
4190 p_creation_date_time DATE,
4191 p_entry_user VARCHAR2,
4192 p_fed_non_fed VARCHAR2,
4193 p_trading_partner VARCHAR2,
4194 p_exch_non_exch VARCHAR2,
4195 p_cust_non_cust VARCHAR2,
4196 p_budget_subfunction VARCHAR2,
4197 p_debit NUMBER,
4198 p_credit NUMBER,
4199 p_transfer_dept_id VARCHAR2,
4200 p_transfer_main_acct VARCHAR2,
4201 p_year_budget_auth VARCHAR2,
4202 p_budget_function VARCHAR2,
4203 p_advance_flag VARCHAR2,
4204 p_cohort VARCHAR2,
4205 p_begin_end VARCHAR2,
4206 p_indef_def_flag VARCHAR2,
4207 p_appor_cat_b_dtl VARCHAR2,
4208 p_appor_cat_b_txt VARCHAR2,
4209 p_prn_num VARCHAR2,
4210 p_prn_txt VARCHAR2,
4211 p_public_law VARCHAR2,
4212 p_appor_cat_code VARCHAR2,
4213 p_authority_type VARCHAR2,
4214 p_transaction_partner VARCHAR2,
4215 p_reimburseable_flag VARCHAR2,
4216 P_bea_category VARCHAR2,
4217 p_borrowing_source VARCHAR2,
4218 p_def_liquid_flag VARCHAR2,
4219 p_deficiency_flag VARCHAR2,
4220 p_availability_flag VARCHAR2,
4221 p_legislation_flAg VARCHAR2,
4222 p_je_line_creation_date DATE,
4223 p_je_line_modified_date DATE,
4224 p_je_line_period_name VARCHAR2,
4225 p_gl_date DATE,
4226 p_gl_posted_date DATE,
4227 p_reversal_flag VARCHAR2,
4228 p_sla_hdr_event_id NUMBER,
4229 p_sla_hdr_creation_date DATE,
4230 p_sla_entity_id NUMBER ) IS
4231 l_module_name VARCHAR2(200);
4232 BEGIN
4233 l_module_name := g_module_name || 'populate_table';
4234 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4235 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' POPULATING FV_FACTS_TRX_TEMP TABLE ...');
4236 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TREASURY SYMBOL ID :'||P_TREASURY_SYMBOL_ID||'''');
4237 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SET OF BOOKS ID :'||P_SET_OF_BOOKS_ID||'''');
4238 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CCID :'||P_CODE_COMBINATION_ID||'''');
4239 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FUND VALUE :'||P_FUND_VALUE||'''');
4240 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ACCOUNT NUMBER :'||P_ACCOUNT_NUMBER||'''');
4241 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOC SOURCE :'||P_DOCUMENT_SOURCE||'''');
4242 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DOC NUMBER :'||P_DOCUMENT_NUMBER||'''');
4243 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TXN DATE :'||P_TRANSACTION_DATE||'''');
4244 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CREATION DATE/TIME :'||P_CREATION_DATE_TIME||'''');
4245 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ENTRY USER :'||P_ENTRY_USER||'''');
4246 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' FED/NON-FED :'||P_FED_NON_FED||'''');
4247 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TRADING PARTNER :'||SUBSTR(P_TRADING_PARTNER,1,6)||'''');
4248 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' EXCH/NON-EXCH :'||P_EXCH_NON_EXCH||'''');
4249 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CUST/NON-CUST :'||P_CUST_NON_CUST||'''');
4250 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BUDGET SUB FUNCTION:'||P_BUDGET_SUBFUNCTION||'''');
4251 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DEBIT :'||P_DEBIT||'''');
4252 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' CREDIT :'||P_CREDIT||'''');
4253 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TRANSFER DEPT ID :'||P_TRANSFER_DEPT_ID||'''');
4254 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TRANSFER MAIN ACCT :'||P_TRANSFER_MAIN_ACCT||'''');
4255 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' YEAR BUDGET AUTH :'||P_YEAR_BUDGET_AUTH||'''');
4256 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' ADVANCE FLAG :'||P_BUDGET_FUNCTION||'''');
4257 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' COHORT :'||P_COHORT||'''');
4258 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BEGIN/END :'||P_BEGIN_END||'''');
4259 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' INDEF/DEF FLAG :'||P_INDEF_DEF_FLAG||'''');
4260 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' APPOR CAT B DTL :'||P_APPOR_CAT_B_DTL||'''');
4261 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' APPOR CAT B TXT :'||P_APPOR_CAT_B_TXT||'''');
4262 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PRN NUM :'||P_PRN_NUM||'''');
4263 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PRN TEXT :'||P_PRN_TXT||'''');
4264 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' PUBLIC LAW :'||P_PUBLIC_LAW||'''');
4265 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' APPOR CAT CODE :'||P_APPOR_CAT_CODE||'''');
4266 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AUTHORITY TYPE :'||P_AUTHORITY_TYPE||'''');
4267 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TRANSACTION PARTNER:'||P_TRANSACTION_PARTNER||'''');
4268 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' REIMBURSEABLE FLAG :'||P_REIMBURSEABLE_FLAG||'''');
4269 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BEA CATEGORY :'||P_BEA_CATEGORY||'''');
4270 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' BORROWING SOURCE :'||P_BORROWING_SOURCE||'''');
4271 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DEF LIQUID FLAG :'||P_DEF_LIQUID_FLAG||'''');
4272 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' DEFICIENCY FLAG :'||P_DEFICIENCY_FLAG||'''');
4273 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' AVAILABILITY FLAG :'||P_AVAILABILITY_FLAG||'''');
4274 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LEGISLATION FLAG :'||P_LEGISLATION_FLAG||'''');
4275
4276 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' TRUNCATED APPOR CAT B TXT:'||SUBSTR(P_APPOR_CAT_B_TXT,1,25)||'''');
4277 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LINE CREATION DATE :'||P_JE_LINE_CREATION_DATE);
4278 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LINE MODIFIED DATE :'||P_JE_LINE_MODIFIED_DATE);
4279 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' LINE PERIOD NAME :'||P_JE_LINE_PERIOD_NAME);
4280 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GL DATE :'||P_GL_DATE);
4281 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' GL POSTED DATE :'||P_GL_POSTED_DATE);
4282 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SLA HEADER EVENT ID :'||P_SLA_HDR_EVENT_ID);
4283 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SLA HEADER CREATON DATE :'||P_SLA_HDR_CREATION_DATE);
4284 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' SLA ENTITY ID :'||P_SLA_ENTITY_ID);
4285
4286 END IF;
4287
4288 INSERT INTO fv_facts_trx_temp
4289 (treasury_symbol_id ,
4290 set_of_books_id ,
4291 code_combination_id ,
4292 fund_value ,
4293 account_number ,
4294 document_source ,
4295 document_category ,
4296 document_number ,
4297 transaction_date ,
4298 creation_date_time ,
4299 entry_user ,
4300 fed_non_fed ,
4301 trading_partner ,
4302 exch_non_exch ,
4303 cust_non_cust ,
4304 budget_subfunction ,
4305 debit ,
4306 credit ,
4307 transfer_dept_id ,
4308 transfer_main_acct ,
4309 year_budget_auth ,
4310 budget_function ,
4311 advance_flag ,
4312 cohort ,
4313 begin_end ,
4314 indef_def_flag ,
4315 appor_cat_b_dtl ,
4316 appor_cat_b_txt ,
4317 PROGRAM_RPT_CAT_NUM ,
4318 PROGRAM_RPT_CAT_TXT ,
4319 public_law ,
4320 appor_cat_code ,
4321 authority_type ,
4322 transaction_partner ,
4323 reimburseable_flag ,
4324 bea_category ,
4325 borrowing_source ,
4326 def_liquid_flag ,
4327 deficiency_flag ,
4328 availability_flag ,
4329 legislation_flag ,
4330 journal_creation_date ,
4331 journal_modified_date ,
4332 period_name ,
4333 gl_date ,
4334 gl_posted_date,
4335 reversal_flag ,
4336 sla_hdr_event_id,
4337 sla_hdr_creation_date,
4338 sla_entity_id )
4339 VALUES
4340 ( p_treasury_symbol_id ,
4341 p_set_of_books_id ,
4342 p_code_combination_id ,
4343 p_fund_value ,
4344 p_account_number ,
4345 p_document_source ,
4346 p_document_category ,
4347 p_document_number ,
4348 p_transaction_date ,
4349 p_creation_date_time ,
4350 p_entry_user ,
4351 p_fed_non_fed ,
4352 SUBSTR(p_trading_partner,1,6),
4353 p_exch_non_exch ,
4354 p_cust_non_cust ,
4355 p_budget_subfunction ,
4356 p_debit ,
4357 p_credit ,
4358 p_transfer_dept_id ,
4359 p_transfer_main_acct ,
4360 p_year_budget_auth ,
4361 p_budget_function ,
4362 p_advance_flag ,
4363 p_cohort ,
4364 p_begin_end ,
4365 p_indef_def_flag ,
4366 p_appor_cat_b_dtl ,
4367 SUBSTR(p_appor_cat_b_txt,1,25),
4368 p_prn_num ,
4369 SUBSTR(p_prn_txt,1,25) ,
4370 p_public_law ,
4371 p_appor_cat_code ,
4372 p_authority_type ,
4373 p_transaction_partner ,
4374 p_reimburseable_flag ,
4375 p_bea_category ,
4376 p_borrowing_source ,
4377 p_def_liquid_flag ,
4378 p_deficiency_flag ,
4379 p_availability_flag ,
4380 p_legislation_flag ,
4381 p_je_line_creation_date ,
4382 p_je_line_modified_date ,
4383 p_je_line_period_name ,
4384 p_gl_date ,
4385 p_gl_posted_date,
4386 NVL(p_reversal_flag, ' ') ,
4387 p_sla_hdr_event_id,
4388 p_sla_hdr_creation_date,
4389 p_sla_entity_id );
4390
4391 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4392 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,' POPULATED FV_FACTS_TRX_TEMP TABLE ...');
4393 END IF;
4394 EXCEPTION
4395 WHEN OTHERS THEN
4396 g_error_code := SQLCODE ;
4397 g_error_buf := 'POPULATE TABLE procedure, Error Occured -- ' || SQLERRM;
4398 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4399 END populate_table;
4400
4401
4402 -- -------------------------------------------------------------------
4403 -- PROCEDURE LOAD_FACTS_ATTRIBUTES
4404 -- -------------------------------------------------------------------
4405 -- This procedure selects the attributes for the Account number
4406 -- segment from FV_FACTS_ATTRIBUTES table and load them into global
4407 -- variables for usage in the FACTS Main process. It also calculates
4408 -- one time pull up values for the account number that does not
4409 -- require drill down into GL transactions.
4410 -- ------------------------------------------------------------------
4411 PROCEDURE load_facts_attributes (acct_num VARCHAR2,
4412 fund_val VARCHAR2)
4413 IS
4414 l_module_name VARCHAR2(200);
4415 l_financing_acct_flag VARCHAR2(1) ;
4416 l_established_fy NUMBER ;
4417 l_resource_type VARCHAR2(80) ;
4418 l_fund_category VARCHAR2(1) ;
4419 l_ussgl_enabled VARCHAR2(1) ;
4420 l_reporting_type VARCHAR2(1) ;
4421 l_budget_sub fv_fund_parameters.budget_subfunction %TYPE;
4422 l_cnc fv_treasury_symbols.cust_non_cust%TYPE;
4423 BEGIN
4424 l_module_name := g_module_name || 'load_facts_attributes';
4425 BEGIN
4426 SELECT balance_type,
4427 public_law_code,
4428 reimburseable_flag,
4429 availability_time,
4430 bea_category,
4431 apportionment_category,
4432 SUBSTR(transaction_partner,1,1),
4433 borrowing_source,
4434 definite_indefinite_flag,
4435 legislative_indicator,
4436 authority_type,
4437 deficiency_flag,
4438 function_flag,
4439 advance_flag,
4440 transfer_flag,
4441 govt_non_govt,
4442 exch_non_exch,
4443 cust_non_cust,
4444 budget_subfunction
4445 INTO g_balance_type_flag,
4446 g_public_law_code_flag,
4447 g_reimburseable_flag,
4448 g_availability_flag,
4449 g_bea_category_flag,
4450 g_appor_cat_flag,
4451 g_transaction_partner_val,
4452 g_borrowing_source_flag,
4453 g_def_indef_flag,
4454 g_legis_ind_flag,
4455 g_authority_type_flag,
4456 g_deficiency_flag,
4457 g_function_flag,
4458 g_advance_flag,
4459 g_transfer_ind ,
4460 g_govt_non_govt_ind,
4461 g_exch_non_exch_ind,
4462 g_cust_non_cust_ind,
4463 g_budget_subfunction_ind
4464 FROM FV_FACTS_ATTRIBUTES
4465 WHERE Facts_Acct_Number = acct_num
4466 AND set_of_books_id = g_set_of_books_id;
4467 g_attributes_found :='Y';
4468 EXCEPTION
4469 WHEN NO_DATA_FOUND THEN
4470 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, 'NO ATTRIBUTES DEFINIED FOR THE ACCOUNT - ' ||
4471 acct_num );
4472 g_attributes_found := 'N';
4473 RETURN;
4474 WHEN OTHERS THEN
4475 g_error_code := sqlcode ;
4476 g_error_buf := sqlerrm ;
4477 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_3',g_error_buf);
4478 RETURN;
4479 END ;
4480
4481
4482 IF g_attributes_found ='Y' THEN
4483 BEGIN
4484 SELECT UPPER(fts.resource_type),
4485 def_indef_flag,
4486 ffp.fund_category
4487 INTO l_resource_type,
4488 g_def_indef_val,
4489 l_fund_category
4490 FROM fv_treasury_symbols fts,
4491 fv_fund_parameters ffp
4492 WHERE ffp.treasury_symbol_id = fts.treasury_symbol_id
4493 AND ffp.fund_value = fund_val
4494 AND fts.treasury_symbol_id = g_treasury_symbol_id
4495 AND fts.set_of_books_id = g_set_of_books_id
4496 AND ffp.set_of_books_id = g_set_of_books_id ;
4497
4498 -- g_fund_category := l_fund_category;
4499 EXCEPTION
4500 When NO_DATA_FOUND Then
4501 g_error_code := -1 ;
4502 g_error_buf := 'Error getting Fund Category value for the fund - '||
4503 fund_val || ' [LOAD_FACTS_ATTRIBURES]' ;
4504 RETURN;
4505 WHEN OTHERS THEN
4506 g_error_code := sqlcode ;
4507 g_error_buf := sqlerrm || ' [LOAD_FACTS_ATTRIBURES]' ;
4508 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.exception_4',g_error_buf);
4509 RETURN;
4510 END ;
4511 ------------------------------------------------
4512 -- Deriving Indefinite Definite Flag
4513 ------------------------------------------------
4514 IF NVL(g_def_indef_flag,'X') <> 'Y' THEN
4515 g_def_indef_val := NULL;
4516 END IF ;
4517 ------------------------------------------------
4518 -- Deriving Public Law Code Flag
4519 ------------------------------------------------
4520 IF g_public_law_code_flag = 'N' THEN
4521 g_public_law_code_val := NULL ;
4522 END IF ;
4523 IF g_availability_flag = 'N' THEN
4524 g_availability_val := NULL;
4525 ELSE
4526 g_availability_val := g_availability_flag;
4527 END IF ;
4528 IF g_transaction_partner_val = 'N' THEN
4529 g_transaction_partner_val := NULL;
4530 END IF ;
4531 ------------------------------------------------
4532 -- Deriving Apportionment Category Code
4533 ------------------------------------------------
4534 IF g_appor_cat_flag = 'Y' THEN
4535 IF l_fund_category IN ('A','S') THEN
4536 g_appor_cat_val := 'A' ;
4537 ElSIF l_fund_category IN ('B','T') THEN
4538 g_appor_cat_val := 'B' ;
4539 ElSIF l_fund_category in ('R','C') THEN
4540 g_appor_cat_val := 'C' ;
4541 ElSE
4542 g_appor_cat_val := NUll;
4543 END IF ;
4544 ELSE
4545 g_appor_cat_val := NULL;
4546 END IF ;
4547
4548
4549 ------------------------------------------------
4550 -- Deriving Authority Type
4551 ------------------------------------------------
4552 IF NVL(g_authority_type_flag,'N') <> 'N' THEN
4553 g_authority_type_val := g_authority_type_flag;
4554 ELSE
4555 g_authority_type_val := ' ' ;
4556 END IF ;
4557 --------------------------------------------------------------------
4558 -- Deriving Reimburseable Flag Value
4559 --------------------------------------------------------------------
4560 IF g_reimburseable_flag = 'Y' THEN
4561 IF l_fund_category IN ('A', 'B','C') THEN
4562 g_reimburseable_val := 'D' ;
4563 ELSIF l_fund_category in ('R','S','T') THEN
4564 g_reimburseable_val := 'R' ;
4565 ELSE
4566 g_reimburseable_val := NULL;
4567 END IF ;
4568 ELSE
4569 g_reimburseable_val := NULL;
4570 END IF ;
4571 --------------------------------------------------------------------
4572 -- Deriving BEA Category and Borrowing Source Values
4573 --------------------------------------------------------------------
4574 IF g_bea_category_flag = 'Y' OR g_borrowing_source_flag = 'Y' THEN
4575 BEGIN
4576 SELECT RPAD(SUBSTR(ffba.borrowing_source,1,6), 6)
4577 INTO g_borrowing_source_val
4578 FROM fv_facts_budget_accounts ffba,
4579 fv_facts_federal_accounts fffa,
4580 fv_treasury_symbols fts ,
4581 fv_facts_bud_fed_accts ffbfa
4582 WHERE fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
4583 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
4584 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
4585 AND fts.treasury_symbol_id = g_treasury_symbol_id
4586 AND fts.set_of_books_id = g_set_of_books_id
4587 AND fffa.set_of_books_id = g_set_of_books_id
4588 AND ffbfa.set_of_books_id = g_set_of_books_id
4589 AND ffba.set_of_books_id = g_set_of_books_id ;
4590
4591 SELECT RPAD(SUBSTR(bea_category,1,5), 5)
4592 INTO g_bea_category_val
4593 FROM fv_fund_parameters
4594 WHERE treasury_symbol_id = g_treasury_symbol_id
4595 AND set_of_books_id = g_set_of_books_id
4596 AND fund_category = l_fund_category;
4597
4598 IF g_bea_category_flag = 'N' THEN
4599 g_bea_category_val := NULL;
4600 END IF ;
4601 IF g_borrowing_source_flag = 'N' THEN
4602 g_borrowing_source_val := NULL;
4603 END IF ;
4604 EXCEPTION
4605 WHEN NO_DATA_FOUND THEN
4606 g_bea_category_val := Null;
4607 g_borrowing_source_val := Null;
4608 END ;
4609 ELSE
4610 g_bea_category_val := Null;
4611 g_borrowing_source_val := Null;
4612 END IF ;
4613 g_def_liquid_flag := ' ' ;
4614 g_deficiency_flag := ' ' ;
4615 --------------------------------------------------------------------
4616 -- Deriving Budget Function
4617 --------------------------------------------------------------------
4618 IF g_function_flag = 'Y' THEN
4619 BEGIN
4620 SELECT RPAD(SUBSTR(ffba.budget_function,1,3), 3)
4621 INTO g_budget_function_val
4622 FROM fv_facts_budget_accounts ffba,
4623 fv_facts_federal_accounts fffa,
4624 fv_treasury_symbols fts ,
4625 fv_facts_bud_fed_accts ffbfa
4626 WHERE fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
4627 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
4628 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
4629 AND fts.treasury_symbol_id = g_treasury_symbol_id
4630 AND fts.set_of_books_id = g_set_of_books_id
4631 AND fffa.set_of_books_id = g_set_of_books_id
4632 AND ffbfa.set_of_books_id = g_set_of_books_id
4633 AND ffba.set_of_books_id = g_set_of_books_id ;
4634 EXCEPTION
4635 WHEN NO_DATA_FOUND THEN
4636 g_budget_function_val := RPAD(' ', 3);
4637 END ;
4638 ELSE
4639 g_budget_function_val := RPAD(' ', 3);
4640 END IF ;
4641
4642 GET_USSGL_INFO (acct_num, l_ussgl_enabled, l_reporting_type);
4643 IF g_error_code <> 0 THEN
4644 RETURN;
4645 END IF;
4646
4647 -- Account on USSGL_ACCOUNTS
4648 /*
4649 IF l_ussgl_enabled IS NOT NULL THEN
4650 IF l_ussgl_enabled = 'N' THEN
4651 g_govt_non_govt_ind := 'X';
4652 RETURN;
4653 ELSIF l_reporting_type = '2' THEN
4654 Account Number is not a valid FACTS II Account
4655 skip the transaction and go ahead with the next.
4656 g_govt_non_govt_ind := 'X';
4657 RETURN ;
4658 ELSE
4659 */
4660
4661 BEGIN
4662 -- g_govt_non_govt_val := 'X';
4663 ----------------------------------------------
4664 -- Deriving Budget Sub Function value
4665 ----------------------------------------------
4666 IF (g_budget_subfunction_ind = 'Y') THEN
4667 SELECT budget_subfunction
4668 INTO l_budget_sub
4669 FROM fv_fund_parameters
4670 WHERE fund_value = FUND_VAL
4671 AND set_of_books_id = g_set_of_books_id;
4672
4673 IF (l_budget_sub IS NOT NULL) THEN
4674 g_budget_subfunction_val := l_budget_sub;
4675 END IF;
4676 ELSE
4677 g_budget_subfunction_val := NULL;
4678 END IF;
4679 ---------------------------------------------------
4680 -- Deriving Exchange Non Exchange Indicator value
4681 ---------------------------------------------------
4682 IF (g_exch_non_exch_ind <> 'Y') THEN
4683 IF (g_exch_non_exch_ind = 'N') THEN
4684 g_exch_non_exch_val := NULL;
4685 ELSE
4686 g_exch_non_exch_val := g_exch_non_exch_ind;
4687 END IF;
4688 END IF;
4689 ----------------------------------------------
4690 -- Deriving Custodial Non Custodial Value
4691 ----------------------------------------------
4692 IF (g_cust_non_cust_ind = 'Y') THEN
4693 SELECT fts.cust_non_cust
4694 INTO l_cnc
4695 FROM fv_treasury_symbols fts, fv_fund_parameters ffp
4696 WHERE fts.treasury_symbol_id = ffp.treasury_symbol_id
4697 AND ffp.set_of_books_id = g_set_of_books_id
4698 AND ffp.fund_value = fund_val;
4699
4700 IF (l_cnc IS NOT NULL) THEN
4701 g_cust_non_cust_val := l_cnc ;
4702 ELSE
4703 g_cust_non_cust_val := NULL;
4704 END IF;
4705 END IF;
4706 END;
4707 END IF;
4708 -- END IF; -- -- l_ussgl_enabled
4709 -- END IF;
4710
4711 EXCEPTION
4712 When Others Then
4713 g_error_code := sqlcode ;
4714 g_error_buf := sqlerrm || ' [LOAD_FACTS_ATTRIBUTES]' ;
4715 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4716 return;
4717
4718 END load_facts_attributes ;
4719 -- -------------------------------------------------------------------
4720 -- PROCEDURE PURGE_FACTS_TRANSACTIONS
4721 -- -------------------------------------------------------------------
4722 -- Purges all FACTS transactions from the FV_FACTS_TRX_TEMP table for
4723 -- the passed Treasaury Symbol.
4724 -- ------------------------------------------------------------------
4725 PROCEDURE purge_facts_transactionS
4726 IS
4727 l_module_name VARCHAR2(200);
4728 BEGIN
4729 l_module_name := g_module_name || 'purge_facts_transactionS';
4730 DELETE FROM fv_facts_trx_temp;
4731 COMMIT ;
4732 EXCEPTION
4733 -- Exception Processing
4734 WHEN NO_DATA_FOUND THEN
4735 NULL ;
4736 WHEN OTHERS THEN
4737 g_error_code := sqlcode ;
4738 g_error_buf := sqlerrm ||
4739 'PURGE DATA';
4740 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4741 RETURN ;
4742 END purge_facts_transactions ;
4743 -- -------------------------------------------------------------------
4744 -- PROCEDURE GET_SGL_PARENT
4745 -- -------------------------------------------------------------------
4746 -- Gets the SGL Parent Account for the passed account number
4747 -- ------------------------------------------------------------------
4748 PROCEDURE get_sgl_parent(
4749 Acct_num VARCHAR2,
4750 sgl_acct_num OUT NOCOPY VARCHAR2)
4751 IS
4752 l_module_name VARCHAR2(200);
4753 l_exists VARCHAR2(1) ;
4754 l_acc_val_set_id NUMBER ;
4755 BEGIN
4756 l_module_name := g_module_name || 'get_sgl_parent';
4757 -- Getting the Value Set Id for the Account Segment
4758 Begin
4759 -- Getting the Value set Id for finding hierarchies
4760 SELECT flex_value_set_id
4761 INTO l_acc_val_set_id
4762 FROM fnd_id_flex_segments
4763 WHERE application_column_name = g_acct_segment_name
4764 AND id_flex_code = 'GL#'
4765 AND id_flex_num = g_coa_id ;
4766 EXCEPTION
4767 WHEN NO_DATA_FOUND THEN
4768 g_error_code := -1 ;
4769 g_error_buf := 'Error getting Value Set Id '||
4770 'for segment' ||g_acct_segment_name ||
4771 ' [GET_SGL_PARENT]' ;
4772 RETURN;
4773 END ;
4774
4775 -- Finding the parent of the Account Number in GL
4776 BEGIN
4777 SELECT parent_flex_value
4778 Into sgl_acct_num
4779 From fnd_flex_value_hierarchies
4780 WHERE (acct_num BETWEEN child_flex_value_low
4781 AND child_flex_value_high)
4782 AND parent_flex_value <> 'T'
4783 AND flex_value_set_id = l_acc_val_set_id
4784 AND parent_flex_value IN
4785 (SELECT ussgl_account
4786 FROM fv_facts_ussgl_accounts
4787 WHERE ussgl_account = parent_flex_value);
4788
4789 BEGIN
4790 -- Look for parent in FV_FACTS_ATTRIBUTES table
4791 SELECT 'X'
4792 INTO l_exists
4793 FROM fv_facts_attributes
4794 WHERE facts_acct_number = sgl_acct_num
4795 AND set_of_books_id = g_set_of_books_id;
4796 -- Return the account Number
4797 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4798 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'SGL PARENT ACCOUNT:'||
4799 sgl_acct_num||'-'||
4800 acct_num) ;
4801 END IF;
4802 RETURN ;
4803 EXCEPTION
4804 WHEN NO_DATA_FOUND THEN
4805 sgl_acct_num := NULL ;
4806 RETURN ;
4807 END ;
4808 EXCEPTION
4809 WHEN NO_DATA_FOUND or TOO_MANY_ROWS THEN
4810 -- No Parent Exists or Too Many Parents. Return Nulls
4811 RETURN ;
4812 WHEN OTHERS THEN
4813 g_error_code := SQLCODE ;
4814 g_error_buf := SQLERRM;
4815 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4816 RETURN;
4817 END;
4818 End get_sgl_parent ;
4819 -- -------------------------------------------------------------------
4820 -- PROCEDURE GET_COHORT_INFO
4821 -- -------------------------------------------------------------------
4822 -- Gets the cohort segment name based on the Financing Acct value
4823 -- ------------------------------------------------------------------
4824 PROCEDURE get_cohort_info
4825 IS
4826 l_module_name VARCHAR2(200);
4827 l_financing_acct VARCHAR2(1) ;
4828 BEGIN
4829 l_module_name := g_module_name || 'get_cohort_info';
4830
4831 SELECT FFFA.financing_account,
4832 FFFA.cohort_segment_name
4833 INTO l_financing_acct,
4834 g_cohort_seg_name
4835 FROM FV_FACTS_FEDERAL_ACCOUNTS FFFA,
4836 FV_TREASURY_SYMBOLS FTS
4837 WHERE FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
4838 AND FTS.treasury_symbol_id = g_treasury_symbol_id
4839 AND FTS.set_of_books_id = g_set_of_books_id
4840 AND FFFA.set_of_books_id = g_set_of_books_id ;
4841 ------------------------------------------------
4842 -- Deriving COHORT Value
4843 ------------------------------------------------
4844 IF l_financing_acct NOT IN ('D', 'G') THEN
4845 -- Consider COHORT value only for 'D' and 'G' financing Accounts
4846 g_cohort_seg_name := NULL ;
4847
4848 END IF ;
4849
4850 EXCEPTION
4851 WHEN NO_DATA_FOUND THEN
4852 g_error_code := -1 ;
4853 g_error_buf := 'No Financing Account found for the passed Treasury Symbol [GET_COHORT_INFO] ' ;
4854 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception1',g_error_buf);
4855 RETURN;
4856 WHEN TOO_MANY_ROWS THEN
4857 g_error_code := -1 ;
4858 g_error_buf := 'More than one Financing Account returned for the passed Treasury Symbol [GET_COHORT_INFO]' ;
4859 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name||'.exception2',g_error_buf);
4860 RETURN;
4861 WHEN OTHERS THEN
4862 g_error_code := SQLCODE ;
4863 g_error_buf := 'WHEN OTHERS IN [GET_COHORT_INFO]'||SQLERRM;
4864 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||'.final_exception',g_error_buf);
4865 RETURN;
4866 END get_cohort_info ;
4867 --------------------------------------------------------------------------------
4868 -- Get program segments, for all fund values of the given treasury_symbol, from
4869 -- the prc table.
4870 -- Save the fund values, segment names and prc flag in a table for later use.
4871
4872 PROCEDURE load_program_seg
4873 IS
4874
4875 l_module_name VARCHAR2(200);
4876
4877 CURSOR fund_cur IS
4878 SELECT fund_value,
4879 DECODE(fund_category,'S','A','T','B',fund_category) fund_category
4880 FROM fv_fund_parameters ffp
4881 WHERE ffp.treasury_symbol_id = g_treasury_symbol_id
4882 AND ffp.set_of_books_id = g_set_of_books_id
4883 AND ffp.fund_category IN ('A', 'B', 'S', 'T');
4884
4885 vl_prg_seg fv_facts_prc_hdr.program_segment%TYPE;
4886 vl_prc_flag fv_facts_prc_hdr.prc_mapping_flag%TYPE;
4887 vl_prc_header_id NUMBER;
4888 vl_status VARCHAR2(10);
4889 l_code_type VARCHAR2(1);
4890
4891 BEGIN
4892
4893 l_module_name := g_module_name || 'load_program_seg';
4894 g_funds_count := 0;
4895
4896 FOR fund_rec IN fund_cur
4897 LOOP
4898
4899 FOR Type in 1..2
4900 LOOP
4901 If Type = 1 THEN
4902 l_code_type := 'B';
4903 ELSE
4904 l_code_type := 'N';
4905 END IF;
4906 vl_status := 'PASS';
4907 LOOP
4908
4909 vl_prg_seg := NULL;
4910 vl_prc_flag := NULL;
4911 vl_prc_header_id := NULL;
4912
4913 BEGIN
4914 SELECT program_segment,
4915 prc_mapping_flag, prc_header_id
4916 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4917 FROM fv_facts_prc_hdr ffh
4918 WHERE ffh.treasury_symbol_id = g_treasury_symbol_id
4919 AND ffh.set_of_books_id = g_set_of_books_id
4920 AND ffh.code_type = l_code_type
4921 AND ffh.fund_value = fund_rec.fund_value;
4922 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4923 END;
4924
4925 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
4926
4927 BEGIN
4928 SELECT program_segment,
4929 prc_mapping_flag, prc_header_id
4930 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4931 FROM fv_facts_prc_hdr ffh
4932 WHERE ffh.treasury_symbol_id = g_treasury_symbol_id
4933 AND ffh.set_of_books_id = g_set_of_books_id
4934 AND ffh.code_type = l_code_type
4935 AND ffh.fund_value = 'ALL-A'
4936 AND fund_rec.fund_category = 'A';
4937 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4938 END;
4939
4940 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
4941
4942 BEGIN
4943 SELECT program_segment,
4944 prc_mapping_flag, prc_header_id
4945 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4946 FROM fv_facts_prc_hdr ffh
4947 WHERE ffh.treasury_symbol_id = g_treasury_symbol_id
4948 AND ffh.set_of_books_id = g_set_of_books_id
4949 AND ffh.code_type = l_code_type
4950 AND ffh.fund_value = 'ALL-B'
4951 AND fund_rec.fund_category = 'B';
4952 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4953 END;
4954
4955 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
4956
4957 BEGIN
4958 SELECT program_segment,
4959 prc_mapping_flag, prc_header_id
4960 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4961 FROM fv_facts_prc_hdr ffh
4962 WHERE ffh.treasury_symbol_id = g_treasury_symbol_id
4963 AND ffh.set_of_books_id = g_set_of_books_id
4964 AND ffh.code_type = l_code_type
4965 AND ffh.fund_value = 'ALL-FUNDS';
4966 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4967 END;
4968
4969 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
4970
4971 BEGIN
4972 SELECT program_segment,
4973 prc_mapping_flag, prc_header_id
4974 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4975 FROM fv_facts_prc_hdr ffh
4976 WHERE ffh.treasury_symbol_id = -1
4977 AND ffh.set_of_books_id = g_set_of_books_id
4978 AND ffh.code_type = l_code_type
4979 AND ffh.fund_value = 'ALL-A'
4980 AND fund_rec.fund_category = 'A';
4981 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4982 END;
4983
4984 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
4985
4986 BEGIN
4987 SELECT program_segment,
4988 prc_mapping_flag, prc_header_id
4989 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
4990 FROM fv_facts_prc_hdr ffh
4991 WHERE ffh.treasury_symbol_id = -1
4992 AND ffh.set_of_books_id = g_set_of_books_id
4993 AND ffh.code_type = l_code_type
4994 AND ffh.fund_value = 'ALL-B'
4995 AND fund_rec.fund_category = 'B';
4996 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
4997 END;
4998
4999 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
5000
5001 BEGIN
5002 SELECT program_segment,
5003 prc_mapping_flag, prc_header_id
5004 INTO vl_prg_seg, vl_prc_flag, vl_prc_header_id
5005 FROM fv_facts_prc_hdr ffh
5006 WHERE ffh.treasury_symbol_id = -1
5007 AND ffh.set_of_books_id = g_set_of_books_id
5008 AND ffh.code_type = l_code_type
5009 AND ffh.fund_value = 'ALL-FUNDS';
5010 EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
5011 END;
5012
5013 IF vl_prg_seg IS NOT NULL THEN EXIT; END IF;
5014
5015 vl_status := 'FAIL';
5016 EXIT;
5017
5018 END LOOP;
5019
5020
5021 IF vl_status <> 'FAIL' THEN
5022
5023 g_funds_count := g_funds_count + 1;
5024
5025 g_segs_array(g_funds_count).fund_value := fund_rec.fund_value;
5026 g_segs_array(g_funds_count).segment := vl_prg_seg;
5027 g_segs_array(g_funds_count).prc_flag := vl_prc_flag;
5028 g_segs_array(g_funds_count).prc_header_id := vl_prc_header_id;
5029 g_segs_array(g_funds_count).code_type := l_code_type;
5030 END IF;
5031
5032 END LOOP;
5033 END LOOP;
5034 EXCEPTION
5035 WHEN OTHERS THEN
5036 g_error_buf := SQLERRM;
5037 g_error_code := -1;
5038 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5039 l_module_name||'.final_exception',g_error_buf);
5040
5041 END load_program_seg;
5042 --------------------------------------------------------------------------------
5043 PROCEDURE get_prc_val(p_ccid IN NUMBER,
5044 p_fund_value IN VARCHAR2,
5045 p_catb_val OUT NOCOPY VARCHAR2,
5046 p_catb_desc OUT NOCOPY VARCHAR2,
5047 p_prn_val OUT NOCOPY VARCHAR2,
5048 p_prn_desc OUT NOCOPY VARCHAR2)
5049
5050 IS
5051
5052 l_module_name VARCHAR2(200);
5053
5054 vl_prc_found VARCHAR2(1);
5055 vl_prg_seg_name VARCHAR2(10);
5056 vl_prc_flag VARCHAR2(1);
5057 vl_prc_header_id NUMBER;
5058 vl_program_sel VARCHAR2(150);
5059 vl_program_value VARCHAR2(25);
5060 vl_prg_value_set_id VARCHAR2(25);
5061 vl_code_type VARCHAR2(1);
5062 vl_prc_val VARCHAR2(5);
5063 vl_prc_desc VARCHAR2(100);
5064 BEGIN
5065 l_module_name := g_module_name || 'get_prc_val';
5066 vl_prc_found := 'N';
5067
5068 -- If fund value is found in the pl/sql table, then get
5069 -- the segment name, prc flag and header id.
5070 FOR i IN 1..g_funds_count
5071 LOOP
5072 vl_prc_found := 'N';
5073 IF g_segs_array(i).fund_value = p_fund_value THEN
5074 IF g_segs_array(i).code_type = 'B' THEN
5075 vl_prg_seg_name := g_segs_array(i).segment;
5076 vl_prc_flag := g_segs_array(i).prc_flag;
5077 vl_prc_header_id := g_segs_array(i).prc_header_id;
5078 ELSIF g_segs_array(i).code_type = 'N' THEN
5079 vl_prg_seg_name := g_segs_array(i).segment;
5080 vl_prc_flag := g_segs_array(i).prc_flag;
5081 vl_prc_header_id := g_segs_array(i).prc_header_id;
5082 END IF;
5083
5084
5085 IF vl_prg_seg_name is NOT NULL THEN
5086
5087 -- If program segment name is found in the pl/sql table, then
5088 -- get the program segment value using the ccid
5089 vl_program_sel := 'SELECT gcc.'||vl_prg_seg_name||
5090 ' FROM gl_code_combinations gcc
5091 WHERE gcc.code_combination_id = '||p_ccid;
5092
5093 EXECUTE IMMEDIATE vl_program_sel INTO vl_program_value;
5094
5095 -- IF prc flag is Y, get the program reporting code and
5096 -- program description from the prc mapping form(prc tables).
5097 IF vl_prc_flag = 'Y' THEN
5098
5099 BEGIN
5100
5101 SELECT reporting_code, reporting_desc
5102 INTO vl_prc_val, vl_prc_desc
5103 FROM fv_facts_prc_dtl
5104 WHERE prc_header_id = vl_prc_header_id
5105 AND program_value = vl_program_value
5106 AND set_of_books_id = g_set_of_books_id;
5107
5108 vl_prc_found := 'Y';
5109
5110 EXCEPTION
5111 WHEN NO_DATA_FOUND THEN NULL;
5112 END;
5113
5114 IF vl_prc_found = 'N' THEN
5115 BEGIN
5116
5117 SELECT reporting_code, reporting_desc
5118 INTO vl_prc_val, vl_prc_desc
5119 FROM fv_facts_prc_dtl
5120 WHERE prc_header_id = vl_prc_header_id
5121 AND program_value = 'ALL'
5122 AND set_of_books_id = g_set_of_books_id;
5123
5124 vl_prc_found := 'Y';
5125
5126 EXCEPTION
5127 WHEN NO_DATA_FOUND THEN NULL;
5128 END;
5129 END IF;
5130 END IF;
5131 END IF;
5132
5133
5134
5135 -- IF the prc flag is N
5136 -- get the program reporting code and description from
5137 -- the segment value.
5138
5139 IF vl_prc_flag = 'N' THEN
5140
5141 -- Get the program value set id
5142 SELECT flex_value_set_id
5143 INTO vl_prg_value_set_id
5144 FROM fnd_id_flex_segments
5145 WHERE application_column_name = vl_prg_seg_name
5146 AND application_id = 101
5147 AND id_flex_code = 'GL#'
5148 AND id_flex_num = g_coa_id ;
5149
5150 -- Get the program value description
5151 SELECT SUBSTR(description, 1, 25)
5152 INTO vl_prc_desc
5153 FROM fnd_flex_values_tl ffvt,fnd_flex_values ffv
5154 WHERE ffvt.flex_value_id = ffv.flex_value_id
5155 AND ffv.flex_value_set_id = vl_prg_value_set_id
5156 AND ffv.flex_value = vl_program_value
5157 AND ffvt.language = userenv('LANG');
5158
5159 vl_prc_val := LPAD(TO_CHAR(TO_NUMBER(vl_program_value)),3,'0');
5160
5161
5162 ELSIF vl_prc_flag = 'Y' AND vl_prc_found = 'N' THEN
5163
5164 vl_prc_val := NULL;
5165 vl_prc_desc := NULL;
5166
5167 IF g_segs_array(i).code_type = 'N' THEN
5168 vl_prc_val := '099';
5169 vl_prc_desc := 'PRC not Assigned';
5170 END IF;
5171
5172 END IF;
5173
5174 IF vl_prc_val IS NOT NULL THEN
5175 vl_prc_val := LPAD(TO_CHAR(TO_NUMBER(vl_prc_val)),3,'0');
5176 END IF;
5177
5178 IF g_segs_array(i).code_type = 'B' THEN
5179 IF g_appor_cat_val = 'A' THEN
5180 --p_catb_val := '000';
5181 p_catb_desc := 'Default Cat B Code';
5182 ELSIF g_appor_cat_val = 'B' THEN
5183 p_catb_val := vl_prc_val;
5184 p_catb_desc := vl_prc_desc;
5185 END IF ;
5186 ELSE
5187 p_prn_val := vl_prc_val;
5188 p_prn_desc := vl_prc_desc;
5189 END IF;
5190 END IF;
5191
5192 END LOOP;
5193 IF g_appor_cat_val = 'A' THEN
5194 --p_catb_val := '000';
5195 p_catb_desc := 'Default Cat B Code';
5196
5197 END IF;
5198 EXCEPTION
5199 WHEN OTHERS THEN
5200 g_error_buf := SQLERRM;
5201 g_error_code := -1;
5202 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED,
5203 l_module_name||'.final_exception',g_error_buf);
5204 RAISE;
5205
5206 END get_prc_val;
5207 --------------------------------------------------------------------------------
5208 BEGIN
5209 g_module_name := 'fv.plsql.FV_FACTS_TRANSACTIONS.';
5210 g_apps_id := 101;
5211 g_id_flex_code := 'GL#';
5212 END fv_facts_trx_register;