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