[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS_TBAL_TRANSACTIONS
Source
1 PACKAGE BODY FV_FACTS_TBAL_TRANSACTIONS AS
2 --$Header: FVFCTRGB.pls 115.45 2002/11/11 17:32:25 ksriniva ship $
3 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('FV_DEBUG_FLAG'),'N');
4
5 -- ======================================================================
6 -- Variable Naming Conventions
7 -- ======================================================================
8 -- Parameter variables have the format "vp_<Variable Name>"
9 -- FACTS Attribute Flags have the format "va_<Variable Name>_flag"
10 -- FACTS Attribute values have the format "va_<Variable Name>_val"
11 -- Constant values for the FACTS record
12 -- have the format "vc_<Variable Name>"
13 -- Other Global Variables have the format "v_<Variable_Name>"
14 -- Procedure Level local variables have
15 -- the format "vl_<Variable_Name>"
16 --
17 -- ======================================================================
18 -- Parameters
19 -- ======================================================================
20
21 vp_errbuf Varchar2(600) ;
22 vp_retcode number ;
23 vp_set_of_books_id number ;
24 vp_treasury_symbol Varchar2(35) ;
25 vp_start_date Date ;
26 vp_end_date Date ;
27 vp_source GL_JE_HEADERS.JE_SOURCE%TYPE := NULL ;
28 vp_category GL_JE_HEADERS.JE_CATEGORY%TYPE := NULL ;
29 vp_currency_code Varchar2(15) ;
30 p_jE_header_id GL_JE_HEADERS.JE_HEADER_ID%TYPE := NULL ;
31 -- ======================================================================
32 -- FACTS Attributes
33 -- ======================================================================
34
35 va_balance_type_flag Varchar2(1) ;
36 va_public_law_code_flag Varchar2(1) ;
37 va_reimburseable_flag Varchar2(1) ;
38 va_bea_category_flag Varchar2(1) ;
39 va_appor_cat_flag Varchar2(1) ;
40 va_borrowing_source_flag Varchar2(1) ;
41 va_def_indef_flag Varchar2(1) ;
42 va_legis_ind_flag Varchar2(1) ;
43 va_authority_type_flag Varchar2(1) ;
44 va_function_flag Varchar2(1) ;
45 va_availability_flag Varchar2(1) ;
46 va_def_liquid_flag Varchar2(1) ;
47 va_deficiency_flag Varchar2(1) ;
48 va_transaction_partner_val Varchar2(1) ;
49 va_cohort Varchar2(2) ;
50 va_def_indef_val Varchar2(1) ;
51 va_appor_cat_b_dtl Varchar2(3) ;
52 va_appor_cat_b_txt Varchar2(25) ;
53 va_public_law_code_val Varchar2(7) ;
54 va_appor_cat_val Varchar2(1) ;
55 va_authority_type_val Varchar2(1) ;
56 va_reimburseable_val Varchar2(1) ;
57 va_bea_category_val Varchar2(5) ;
58 va_borrowing_source_val Varchar2(6) ;
59 va_deficiency_val Varchar2(1) ;
60 va_legis_ind_val Varchar2(1) ;
61 va_balance_type_val Varchar2(1) ;
62
63 /* Bug No: 2494754 */
64
65 va_budget_function VARCHAR2(3) ;
66 va_advance_flag VARCHAR2(1) ;
67 va_transfer_ind VARCHAR2(1) ;
68 va_advance_type_val VARCHAR2(1) ;
69 va_transfer_dept_id VARCHAR2(2) ;
70 va_transfer_main_acct VARCHAR2(4) ;
71
72
73 -- Added New Variables for the Document Number and Date
74 --
75 va_document_number Varchar2(240) ;
76 va_document_Date Date ;
77 va_source GL_JE_HEADERS.JE_SOURCE%TYPE ;
78 va_category GL_JE_HEADERS.JE_CATEGORY%TYPE;
79 va_user_category GL_JE_CATEGORIES.USER_JE_CATEGORY_NAME%TYPE;
80 v_doc_created_by NUMBER(15) ;
81 v_doc_creation_date DATE ;
82 v_ccid GL_CODE_COMBINATIONS.CODE_COMBINATION_ID%TYPE;
83 v_seg_fiscal_yr fv_pya_fiscalyear_map.fyr_segment_value%type;
84
85 -- ======================================================================
86 -- FACTS File Constants
87 -- ======================================================================
88 vc_fiscal_yr Varchar2(4) ;
89 vc_rpt_fiscal_yr Varchar2(4) ;
90 vc_rpt_fiscal_month Varchar2(2) ;
91 -- ======================================================================
92 -- Variables for Transaction Register report
93 -- ======================================================================
94 v_pagebreak1 VARCHAR2(30) ;
95 v_pagebreak1_low VARCHAR2(30) ;
96 v_pagebreak1_high VARCHAR2(30) ;
97 v_pagebreak2 VARCHAR2(30) ;
98 v_pagebreak2_low VARCHAR2(30) ;
99 v_pagebreak2_high VARCHAR2(30) ;
100 v_pagebreak3 VARCHAR2(30) ;
101 v_pagebreak3_low VARCHAR2(30) ;
102 v_pagebreak3_high VARCHAR2(30) ;
103
104
105 -- ======================================================================
106 -- Other GLOBAL Variables
107 -- ======================================================================
108 -- ------------------------------
109 -- Period Declarations
110 -- -----------------------------
111 v_begin_period_name Varchar2(20) ;
112 v_begin_period_start_dt date ;
113 v_begin_period_end_dt date ;
114 v_begin_period_num Number ;
115 v_begin_quarter_num Number ;
116 v_period_name Varchar2(20) ;
117 v_fiscal_year Number ;
118 v_period_start_dt date ;
119 v_period_end_dt date ;
120 v_period_num Number ;
121 v_quarter_num Number ;
122 v_bal_seg_name Varchar2(20) ;
123 v_acc_seg_name Varchar2(20) ;
124 v_prg_seg_name Varchar2(20) ;
125 v_cohort_seg_name Varchar2(20) ;
126 v_acc_val_set_id Number ;
127 v_prg_val_set_id Number ;
128 v_cohort_select Varchar2(20) ;
129 v_cohort_stmt Varchar2(500) ;
130 v_cohort_where Varchar2(120) ;
131 v_chart_of_accounts_id Varchar2(20) ;
132 v_prg_seg_val_set_id Number(10) ;
133 v_acc_seg_val_set_id Number(10) ;
134 v_acct_num Varchar2(25) ;
135 v_fund_val Varchar2(25) ;
136 v_sgl_acct_num number ;
137
138 -- This flag is set to 'Y' when a account number is found
139 -- in FV_FACTS_ATTRIBUTES table and is not an USSGL account
140 v_acct_attr_flag Varchar2(1) ;
141 --Start Bug 2464961--
142 v_amount_dr NUMBER ;
143 v_amount_cr NUMBER ;
144 v_amount Number ;
145 --End Bug 2464961--
146 v_begin_amount number ;
147 v_treasury_symbol_id Number ;
148 v_record_category Varchar2(30) ;
149
150 /* Bug No: 2494754 */
151
152 -- Declare a Flag to determine Debug Mode
153 v_debug Boolean := TRUE ;
154
155 v_fiscal_yr Varchar2(25) ;
156 v_year_gtn2001 BOOLEAN ;
157 v_time_frame fv_treasury_symbols.time_frame%TYPE ;
158 v_financing_acct fv_facts_federal_accounts.financing_account%TYPE ;
159 v_year_budget_auth VARCHAR2(3);
160
161
162 -- ------------------------------------------------------------------
163 -- PROCEDURE MAIN
164 -- ------------------------------------------------------------------
165 -- Main procedure that is called to execute FACTS process.
166 -- This calls all subsequent procedures that are pare of the FACTS
167 -- process.
168 -- ------------------------------------------------------------------
169 Procedure MAIN(
170 Errbuf OUT NOCOPY Varchar2,
171 retcode OUT NOCOPY Varchar2,
172 Set_Of_Books_Id Number,
173 p_coa Number,
174 Treasury_Symbol Varchar2,
175 Start_Date Date,
176 End_Date Date,
177 --Source_Name varchar2 DEFAULT NULL ,
178 --Category_Name varchar2 DEFAULT NULL,
179 Source_Name varchar2 := NULL ,
180 Category_Name varchar2 := NULL,
181 currency_code Varchar2,
182 p_pagebreak1 VARCHAR2,
183 p_pagebreak1_low VARCHAR2,
184 p_pagebreak1_high VARCHAR2,
185 p_pagebreak2 VARCHAR2,
186 p_pagebreak2_low VARCHAR2,
187 p_pagebreak2_high VARCHAR2,
188 p_pagebreak3 VARCHAR2,
189 p_pagebreak3_low VARCHAR2,
190 p_pagebreak3_high VARCHAR2)
191
192
193 IS
194 BEGIN
195
196 -- Load FACTS Parameters into Global Variables
197 vp_set_of_books_id := set_of_books_id ;
198 vp_treasury_symbol := treasury_symbol ;
199 vp_start_date := Start_Date ;
200 vp_end_date := End_Date ;
201 vp_retcode := 0 ;
202 vp_source := Source_Name ;
203 vp_category := Category_Name ;
204 vp_currency_code := currency_code ;
205
206
207 -- Load Transaction Register Report Parameters into Global Variables
208 v_pagebreak1 := p_pagebreak1 ;
209 v_pagebreak1_low := p_pagebreak1_low ;
210 v_pagebreak1_high := p_pagebreak1_high ;
211 v_pagebreak2 := p_pagebreak2 ;
212 v_pagebreak2_low := p_pagebreak2_low ;
213 v_pagebreak2_high := p_pagebreak2_high ;
214 v_pagebreak3 := p_pagebreak3 ;
215 v_pagebreak3_low := p_pagebreak3_low ;
216 v_pagebreak3_high := p_pagebreak3_high ;
217
218
219 -- Get the Treasury Symbol Id for the passed Treasury Symbol
220 fnd_message.set_Name('FV', 'Deriving Treasury Symbol...') ;
221
222 Begin --TS
223
224 Select Treasury_Symbol_id
225 Into v_treasury_symbol_id
226 From fv_treasury_symbols
227 where treasury_symbol = vp_treasury_symbol
228 And set_of_books_id = vp_set_of_books_id;
229
230
231 Exception
232 WHEN NO_DATA_FOUND Then
233 vp_retcode := -1 ;
234 vp_errbuf := 'Treasury Symbol Id cannot be found for the Treasury
235 Symbol - '|| vp_treasury_symbol || ' [ MAIN ] ' ;
236 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
237 -- fv_utility.debug_mesg('[TS MAIN]'||SQLERRM);
238 Return ;
239 End ; -- TS
240
241
242 If vp_retcode = 0 Then
243 -- fv_utility.debug_mesg('PURGING TEMP TABLE');
244 fnd_message.set_Name('FV', 'Purging FACTS Temp....') ;
245 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
246 PURGE_FACTS_TRANSACTIONS ;
247 End If ;
248
249 If vp_retcode = 0 Then
250 -- fv_utility.debug_mesg('DERVING QUALIFIER');
251 fnd_message.set_Name('FV', 'Deriving Qualifier Seg....') ;
252 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
253 GET_QUALIFIER_SEGMENTS ;
254 End If ;
255
256 If vp_retcode = 0 Then
257 -- fv_utility.debug_mesg('GET COHORT INFO');
258 fnd_message.set_Name('FV','Deriving Treasury Sym Info');
259 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
260 GET_COHORT_INFO ;
261 End If ;
262
263
264 If vp_retcode = 0 Then
265 -- fv_utility.debug_mesg('GET PERIOD INFO');
266 fnd_message.set_Name('FV', 'Deriving Period Info') ;
267 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
268 GET_PERIOD_INFO ;
269 End If ;
270
271 If vp_retcode = 0 Then
272 -- fv_utility.debug_mesg('START MAIN PROCESS');
273 fnd_message.set_Name('FV', 'FACTS Main Process.....') ;
274 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
275 PROCESS_TBAL_TRANSACTIONS ;
276 COMMIT;
277 Else
278 ROLLBACK;
279 End If ;
280
281 retcode := vp_retcode;
282 errbuf := vp_errbuf;
283
284 EXCEPTION
285 -- Exception Processing
286 When Others Then
287 -- fv_utility.debug_mesg('WHEN OTHERS ERROR: IN [MAIN]'||SQLERRM||SQLCODE);
288 vp_retcode := sqlcode ;
289 vp_errbuf := sqlerrm || ' [MAIN] ' ;
290 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
291 END MAIN ;
292
293 -- -------------------------------------------------------------------
294 -- PROCEDURE PURGE_FACTS_TRANSACTIONS
295 -- -------------------------------------------------------------------
296 -- Purges all FACTS transactions from the FV_FACTS_TEMP table for
297 -- the passed Treasaury Symbol.
298 -- ------------------------------------------------------------------
299
300 Procedure PURGE_FACTS_TRANSACTIONS
301 IS
302 BEGIN
303 DELETE FROM FV_FACTS_TEMP
304 Where treasury_symbol_id = v_treasury_symbol_id
305 And fct_int_record_category = 'TRIAL_BALANCE';
306 Commit ;
307
308 EXCEPTION
309
310 -- Exception Processing
311 When NO_DATA_FOUND Then
312 Null ;
313 When Others Then
314 vp_retcode := sqlcode ;
315 vp_errbuf := sqlerrm ;
316 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
317 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN PURGE DATA:'||SQLERRM||SQLCODE);
318 Return ;
319 END PURGE_FACTS_TRANSACTIONS ;
320
321 -- -------------------------------------------------------------------
322 -- PROCEDURE GET_QUALIFIER_SEGMENTS
323 -- -------------------------------------------------------------------
324 -- Gets the Accounting and Balancing segment names for the Chart
325 -- Of Accounts associated with the passed set of Books. This is done
326 -- by calling to FND procedures.
327 -- ------------------------------------------------------------------
328
329 Procedure GET_QUALIFIER_SEGMENTS
330 is
331 num_boolean BOOLEAN ;
332 apps_id Number := 101 ;
333 flex_code Varchar2(25) := 'GL#' ;
334 seg_number Number ;
335 seg_app_name Varchar2(40) ;
336 seg_prompt Varchar2(25) ;
337 seg_value_set_name Varchar2(40) ;
338 Invalid_segment exception ;
339
340 BEGIN
341 SELECT chart_of_accounts_id
342 INTO v_chart_of_accounts_id
343 FROM gl_sets_of_books
344 WHERE set_of_books_id = vp_set_of_books_id ;
345
346 num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
347 ( apps_id,
348 flex_code,
349 v_chart_of_accounts_id,
350 'GL_ACCOUNT',
351 seg_number);
352
353 if(num_boolean) then
354 num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
355 ( apps_id,
356 flex_code,
357 v_chart_of_accounts_id,
358 seg_number,
359 v_acc_seg_name,
360 seg_app_name,
361 seg_prompt,
362 seg_value_set_name);
363 else
364 raise invalid_segment;
365 End if;
366
367 num_boolean := FND_FLEX_APIS.GET_QUALIFIER_SEGNUM
368 (apps_id,
369 flex_code,
370 v_chart_of_accounts_id,
371 'GL_BALANCING',
372 seg_number);
373
374 if(num_boolean) then
375 num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
376 (apps_id,
377 flex_code,
378 v_chart_of_accounts_id,
379 seg_number,
380 v_bal_seg_name,
381 seg_app_name,
382 seg_prompt,
383 seg_value_set_name);
384 else
385 raise invalid_segment;
386 end if;
387
388 v_acc_seg_name := upper(v_acc_seg_name) ;
389 v_bal_seg_name := upper(v_bal_seg_name) ;
390
391 -- fv_utility.debug_mesg('ACCOUNT:'||v_acc_seg_Name||' BAL SEGMENT:'||v_bal_seg_name);
392
393
394 EXCEPTION
395 when invalid_segment then
396 vp_retcode := -1;
397 vp_errbuf := 'Cannot Read the Balancing and account segments';
398 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
399 -- fv_utility.debug_mesg(' INVALID SEGMENT [GET_SEGMENT_INFO]');
400
401 rollback;
402 return;
403
404 when others then
405 vp_retcode := sqlcode;
406 vp_errbuf := sqlerrm ;
407 fnd_file.put_line(fnd_file.log,vp_errbuf) ;
408 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN [GET_QUALIFIER_SEGMENT]'||SQLERRM);
409 return;
410 END GET_QUALIFIER_SEGMENTS ;
411
412 -- -------------------------------------------------------------------
413 -- PROCEDURE GET_PERIOD_INFO
414 -- -------------------------------------------------------------------
415 -- Gets the Period infomation like Period Number, Period_year,
416 -- quarter number and other corresponding period information based on
417 -- the quarter number passed to the Main Procedure
418 -- ------------------------------------------------------------------
419
420 Procedure GET_PERIOD_INFO
421 IS
422
423 BEGIN
424
425 Begin
426 -- Select Period Information for Beginning Period
427 Select period_name,
428 period_year,
429 period_num
430 Into v_begin_period_name,
431 v_fiscal_year,
432 v_begin_period_num
433 From gl_period_statuses
434 Where trunc(start_date) = trunc(vp_start_date)
435 and application_id = 101
436 and adjustment_period_flag = 'N'
437 and set_of_books_id = vp_set_of_books_id ;
438
439 Exception
440 When NO_DATA_FOUND Then
441 vp_retcode := -1 ;
442 vp_errbuf := 'Error Getting Beginning Period Information
443 [GET_PERIOD_INFO]' ;
444 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
445 -- fv_utility.debug_mesg('NO DATA FOUND ERROR IN [GET PERIOD_INFO-1]');
446
447 Return ;
448
449 When TOO_MANY_ROWS Then
450 vp_retcode := -1 ;
451 vp_errbuf := 'More than one Beginning Period Returned !!
452 [GET_PERIOD_INFO]' ;
453 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
454 -- fv_utility.debug_mesg('TOO MANY ROWS ERROR IN [GET PERIOD_INFO-1]');
455 Return ;
456 End ;
457 EXCEPTION
458
459 -- Exception Processing
460 When Others Then
461 vp_retcode := sqlcode ;
462 vp_errbuf := sqlerrm || ' [GET_PERIOD_INFO] ' ;
463 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
464 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN [GET PERIOD_INFO-MAIN]'||SQLERRM);
465 Return ;
466 END GET_PERIOD_INFO ;
467
468
469 -- -------------------------------------------------------------------
470 -- PROCEDURE GET_COHORT_INFO
471 -- -------------------------------------------------------------------
472 -- Gets the cohort segment name based on the Financing Acct value
473 -- ------------------------------------------------------------------
474
475 Procedure GET_COHORT_INFO
476 IS
477 vl_financing_acct Varchar2(1) ;
478 BEGIN
479
480 Select FFFA.financing_account,
481 FFFA.cohort_segment_name
482 Into vl_financing_acct,
483 v_cohort_seg_name
484 From FV_FACTS_FEDERAL_ACCOUNTS FFFA,
485 FV_TREASURY_SYMBOLS FTS
486 Where FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
487 AND FTS.treasury_symbol = vp_treasury_symbol
488 AND FTS.set_of_books_id = vp_set_of_books_id
489 AND FFFA.set_of_books_id = vp_set_of_books_id ;
490
491 ------------------------------------------------
492 -- Deriving COHORT Value
493 ------------------------------------------------
494 If vl_financing_acct NOT IN ('D', 'G') Then
495 -- Consider COHORT value only for 'D' and 'G' financing Accounts
496 v_cohort_seg_name := NULL ;
497 End If ;
498
499 EXCEPTION
500
501 When NO_DATA_FOUND Then
502 vp_retcode := -1 ;
503 vp_errbuf := 'No Financing Account found for the passed
504 Treasury Symbol [GET_COHORT_INFO] ' ;
505 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
506 -- fv_utility.debug_mesg('NO DATA FOUND ERROR IN [GET_COHORT_INFO]'||SQLERRM);
507 return;
508 When TOO_MANY_ROWS Then
509 vp_retcode := -1 ;
510 vp_errbuf := 'More than one Financing Account returned for the
511 passed Treasury Symbol [GET_COHORT_INFO]' ;
512 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
513 -- fv_utility.debug_mesg('TOO MANY ROWS ERROR IN [GET_COHORT_INFO]'||SQLERRM);
514 return;
515 When OTHERS Then
516 vp_retcode := SQLCODE ;
517 vp_errbuf := 'WHEN OTHERS IN [GET_COHORT_INFO]'||SQLERRM;
518 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
519 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN [GET_COHORT_INFO]'||SQLERRM);
520 return;
521
522 END GET_COHORT_INFO ;
523
524 -- -------------------------------------------------------------------
525 -- PROCEDURE PROCESS_TBAL_TRANSACTIONS
526 -- -------------------------------------------------------------------
527 -- This procedure selets all the transactions that needs to be
528 -- analyzed for reporting in the FACTS Trial Balance report. After getting the
529 -- list of trasnactions that needs to be reported, it applies all the
530 -- FACTS attributes for the account number and perform further
531 -- processing for Legislative Indicator. It populates the table FV_FACTS_TEMP
532 -- for using in the Trial balance report
533 -- ------------------------------------------------------------------
534 PROCEDURE PROCESS_TBAL_TRANSACTIONS
535 IS
536 vl_ret_va Boolean := TRUE ;
537 vl_exec_ret Integer ;
538 vl_main_cursor Integer ;
539 vl_main_select Varchar2(2000) ;
540 vl_main_fetch Integer ;
541 vl_main_amount Number ;
542 vl_legis_cursor Integer ;
543 vl_legis_select Varchar2(2000) ;
544 vl_legis_ref Varchar2(240) ;
545 vl_legis_ref1 Varchar2(240) ;
546 vl_legis_ref2 Varchar2(240) ;
547 vl_legis_ref3 Varchar2(240) ;
548 vl_legis_ref4 Varchar2(240) ;
549 vl_legis_ref5 Varchar2(240) ;
550 vl_legis_ref9 Varchar2(240) ;
551 vl_je_date Date ;
552 vl_je_category GL_JE_HEADERS.JE_CATEGORY%TYPE;
553 vl_je_Source GL_JE_HEADERS.JE_SOURCE%TYPE;
554 --Start Bug 2464961--
555 -- vl_legis_amount Number ;
556 vl_legis_amount_dr NUMBER ;
557 vl_legis_amount_cr NUMBER ;
558
559 --End Bug 2464961--
560
561 vl_je_name Varchar2(100) ;
562 vl_program Varchar2(25) ;
563 vl_cohort_yr Varchar2(25) ;
564 vl_sgl_acct_num Varchar2(25) ;
565 vl_count Number:=0 ;
566 vl_tran_type Varchar2(25) ;
567 vl_exception Varchar2(30) ;
568
569 -- Will have 'Y' when account has facts attributes otherwise 'N'.
570 vl_attributes_found Varchar2(1) ;
571
572 -- Cohort Segment Local Variables
573 vl_cohort_select Varchar2(25) ;
574 vl_cohort_group Varchar2(25) ;
575
576 -- Requisition Id for FACTS file processes
577 vl_req_id Number ;
578 vl_print_option BOOLEAN ;
579 vl_printer_name Varchar2(240) ;
580 vl_exists varchar2(1) ;
581 vl_actual_Flag Varchar2(1) := 'A' ;
582
583 -- New Test Variable
584 vll_count Number := 0 ;
585 vll_inner_count Number := 0 ;
586
587 -- Char Varibale to hold Date in DD-MON-YYYY Format
588 vl_period_start_dt Varchar2(20) ;
589 vl_period_end_dt Varchar2(20) ;
590
591 -- New Variables for the new modification
592 vl_amount_cursor Number ;
593 vl_Amount_select Varchar2(2000) ;
594 vl_amount_ret Integer ;
595 vl_new_amount Number ;
596 vl_amount_fetch Integer ;
597
598 -- Variables to Capture documnet detiails
599 vl_doc_created_by Number ;
600 vl_doc_creation_date DATE ;
601 vl_pagebreak1_seg VARCHAR2(40) ;
602 vl_pagebreak2_seg VARCHAR2(40) ;
603 vl_pagebreak3_seg VARCHAR2(40) ;
604 vl_num_boolean BOOLEAN ;
605 vl_apps_id NUMBER := 101 ;
606 vl_flex_code VARCHAR2(25) := 'GL#';
607 vl_seg_app_name VARCHAR2(40) ;
608 vl_seg_prompt VARCHAR2(25) ;
609 vl_seg_value_set_name VARCHAR2(40) ;
610 vl_disbursements_flag VARCHAR2(1);
611 v_ussgl_acct fv_facts_ussgl_accounts.ussgl_account%TYPE;
612 v_excptn_cat fv_facts_temp.fct_int_record_category%TYPE;
613 vl_fyr_segment_value fv_pya_fiscalyear_map.fyr_segment_value%type;
614 v_fyr_segment_name VARCHAR2(20) ;
615 --v_financing_acct VARCHAR2(1);
616 P_refer2 VARCHAR2(80);
617
618
619
620
621 BEGIN
622 -- Get all the transction balances for the combinations that have
623 -- fund values which are associated with the passed Treasury
624 -- Symbol Sum all the amounts and group the data by Account Number
625 -- and Fund Value.
626
627 -- Dynamic SQL is used for declaring the following cursor and to
628 -- fetch the values.
629
630 IF v_pagebreak1 IS NOT NULL THEN
631 vl_num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
632 (vl_apps_id,
633 vl_flex_code,
634 v_chart_of_accounts_id,
635 v_pagebreak1,
636 vl_pagebreak1_seg,
637 vl_seg_app_name,
638 vl_seg_prompt,
639 vl_seg_value_set_name);
640 END IF;
641
642 IF v_pagebreak2 IS NOT NULL THEN
643 vl_num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
644 (vl_apps_id,
645 vl_flex_code,
646 v_chart_of_accounts_id,
647 v_pagebreak2,
648 vl_pagebreak2_seg,
649 vl_seg_app_name,
650 vl_seg_prompt,
651 vl_seg_value_set_name);
652 END IF;
653
654 IF v_pagebreak3 IS NOT NULL THEN
655 vl_num_boolean := FND_FLEX_APIS.GET_SEGMENT_INFO
656 (vl_apps_id,
657 vl_flex_code,
658 v_chart_of_accounts_id,
659 v_pagebreak3,
660 vl_pagebreak3_seg,
661 vl_seg_app_name,
662 vl_seg_prompt,
663 vl_seg_value_set_name);
664 END IF;
665
666
667 fnd_message.set_Name('FV', 'Selecting FACTS Trans...') ;
668 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
669 -- fv_utility.debug_mesg('**** START MAIN PROCESS ****');
670
671 Begin
672 vl_main_cursor := DBMS_SQL.OPEN_CURSOR ;
673 Exception
674 When Others Then
675 vp_retcode := sqlcode ;
676 VP_ERRBUF := sqlerrm ;
677 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
678 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN MAIN CURSOR OPEN'||SQLERRM);
679 Return ;
680 End ;
681
682 If v_cohort_seg_name IS NOT NULL Then
683 v_cohort_select := ', GLCC.' || v_cohort_seg_name ;
684 Else
685 v_cohort_select := ' ' ;
686 End If ;
687
688
689 -- Get the balances for the Account Number and Fund Value
690 vl_main_select := 'SELECT DISTINCT GLCC.'||v_acc_seg_name||
691 ', GLCC.'||v_bal_seg_name||v_cohort_select||
692 ' FROM GL_CODE_COMBINATIONS GLCC,
693 FV_FUND_PARAMETERS FFP,
694 FV_TREASURY_SYMBOLS FTS
695 WHERE FTS.TREASURY_SYMBOL = ' ||''''||vp_treasury_symbol||'''' ||
696 --pkpatel :changed to fix Bug 1575992
697 ' AND FTS.TREASURY_SYMBOL_ID = FFP.TREASURY_SYMBOL_ID
698 AND GLCC.'|| v_bal_seg_name ||'= FFP.FUND_VALUE
699 AND FFP.SET_OF_BOOKS_ID = ' || vp_set_of_books_id ||
700 ' AND FTS.SET_OF_BOOKS_ID = ' || vp_set_of_books_id ;
701
702
703 IF v_pagebreak1 IS NOT NULL THEN
704 vl_main_select := vl_main_select ||
705 ' AND EXISTS (SELECT 1 FROM gl_code_combinations glcc2
706 WHERE glcc.code_combination_id = glcc2.code_combination_id '
707 || ' AND glcc2.' || vl_pagebreak1_seg ||
708 ' BETWEEN '|| ''''|| v_pagebreak1_low || '''' || ' AND '
709 || ''''|| v_pagebreak1_high || '''' ;
710 END IF;
711
712 IF v_pagebreak2 IS NOT NULL THEN
713 vl_main_select := vl_main_select || ' AND glcc2.' || vl_pagebreak2_seg ||
714 ' BETWEEN '|| ''''|| v_pagebreak2_low || '''' || ' AND '
715 || ''''|| v_pagebreak2_high || '''' ;
716 END IF;
717
718 IF v_pagebreak3 IS NOT NULL THEN
719 vl_main_select := vl_main_select || ' AND glcc2.' || vl_pagebreak3_seg ||
720 ' BETWEEN '|| ''''|| v_pagebreak3_low || '''' || ' AND '
721 || ''''|| v_pagebreak3_high || '''' ;
722 END IF;
723
724 IF v_pagebreak1 IS NOT NULL THEN
725 vl_main_select := vl_main_select || ')';
726 END IF;
727
728 vl_main_select := vl_main_select || ' ORDER BY GLCC.' || v_acc_seg_name;
729
730 fnd_file.put_line(fnd_file.log,vl_main_select);
731
732 Begin
733 dbms_sql.parse(vl_main_cursor, vl_main_select, DBMS_SQL.V7) ;
734 Exception
735 When Others Then
736 vp_retcode := sqlcode ;
737 VP_ERRBUF := sqlerrm ;
738 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
739 -- fv_utility.debug_mesg('ERROR IN MAIN CURSOR PARSE'||SQLERRM);
740
741 Return ;
742 End ;
743
744 dbms_sql.define_column(vl_main_cursor, 1, v_acct_num, 25);
745 dbms_sql.define_column(vl_main_cursor, 2, v_fund_val, 25);
746 If v_cohort_Seg_name is not null Then
747 dbms_sql.define_column(vl_main_cursor, 3, vl_cohort_yr, 25);
748 End If ;
749
750 Begin
751 vl_exec_ret := dbms_sql.execute(vl_main_cursor);
752 Exception
753 When Others Then
754 vp_retcode := sqlcode ;
755 VP_ERRBUF := sqlerrm ;
756 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
757 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN MAIN CURSOR EXECUTE:'||SQLERRM);
758 Return ;
759 End ;
760
761 fnd_message.set_Name('FV', 'Processing starts.') ;
762 fnd_file.put_line(fnd_file.log, fnd_message.get) ;
763
764 LOOP --Main Select Loop
765
766 -- Reset the Cursor Variable
767 v_fund_val := Null ;
768 vl_cohort_yr := Null ;
769 v_acct_num := Null ;
770 vl_main_amount := 0 ;
771 vl_new_amount := Null ;
772 -- Fetch rows
773 vl_main_fetch := dbms_sql.fetch_rows(vl_main_cursor) ;
774
775 IF vl_main_fetch = 0 then
776 IF vl_count = 0 Then
777 VP_ERRBUF := 'No Data found for FACTS II process';
778 fnd_file.put_line(fnd_file.log, vp_errbuf);
779 VP_ERRBUF := 'No Data found for FACTS II Transaction Register' ||':'||v_begin_period_num||'Year:'||v_fiscal_year
780 ||'SOB:'||vp_set_of_books_id||'TS:'||vp_treasury_Symbol;
781 -- fv_utility.debug_mesg(vp_errbuf);
782 END IF;
783 exit;
784 ELSE
785 vl_count := vl_count + 1;
786 -- Fetch the Records into Variables
787 dbms_sql.column_value(vl_main_cursor, 1, v_acct_num);
788 dbms_sql.column_value(vl_main_cursor, 2, v_fund_val);
789
790 If v_cohort_Seg_name is not null Then
791 dbms_sql.column_value(vl_main_cursor, 3, vl_cohort_yr);
792 End If ;
793 -- Start Process for the Amount in the main Cursor
794 --
795 ------------------------------------------------------------
796 Begin
797 Begin
798 vl_amount_cursor := DBMS_SQL.OPEN_CURSOR ;
799 Exception
800 When Others Then
801 vp_retcode := sqlcode ;
802 VP_ERRBUF := sqlerrm || '[From Main Cursor ]';
803 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
804 Return ;
805 End ;
806
807
808 If v_cohort_Seg_name is not null Then
809 v_cohort_stmt := ' and glcc.' || v_cohort_seg_name ||
810 ' = nvl(' || ''''||vl_cohort_yr|| ''''||
811 ' ,glcc.'|| v_cohort_seg_name || ')';
812 else
813 v_cohort_stmt := '';
814 End if;
815
816 vl_amount_select :=
817 'SELECT NVL(SUM(nvl(BEGIN_BALANCE_DR,0) - nvl(BEGIN_BALANCE_CR,0)),0)
818 FROM GL_BALANCES GLB,
819 GL_CODE_COMBINATIONS GLCC
820 WHERE GLB.code_combination_id = GLCC.code_combination_id
821 AND GLB.TEMPLATE_ID IS NULL
822 AND GLB.actual_flag = '||''''||vl_actual_flag||''''
823 || ' AND GLB.SET_OF_BOOKS_ID = ' || vp_set_of_books_id
824 || ' AND GLB.PERIOD_NUM = ' || v_begin_period_num ||
825 ' AND GLB.PERIOD_YEAR = ' || v_fiscal_year||
826 ' AND GLCC.'||v_acc_seg_name||' = '||''''||v_acct_num||'''' ||
827 ' AND GLCC.'||v_bal_seg_name||' = '||''''||v_fund_val||'''' ||
828 v_cohort_stmt||
829
830 -- Start Added Where condintion for Bug 1553095 by SMBHAGAV on 19-04-2001
831 ' AND glb.currency_code = ' || '''' || vp_currency_code || '''' ;
832 -- End Added Where condintion for Bug 1553095 by SMBHAGAV on 19-04-2001
833
834 --pkpatel :Bug 1553095(Solution Altered)
835 -- ' AND glb.currency_code = '|| '''' || g_currency_code || '''';
836
837
838 fnd_file.put_line(fnd_file.log, vl_amount_select) ;
839 Begin
840 dbms_sql.parse(vl_amount_cursor,vl_amount_select,DBMS_SQL.V7);
841
842 Exception
843 When Others Then
844 vp_retcode := sqlcode ;
845 VP_ERRBUF := sqlerrm || '[ Parsing vl_amount_cursor ]' ;
846 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
847 Return ;
848 End ;
849
850 dbms_sql.define_column(vl_amount_cursor, 1, vl_new_amount);
851
852 Begin
853 vl_amount_ret := dbms_sql.execute(vl_amount_cursor);
854 Exception
855 When Others Then
856 vp_retcode := sqlcode ;
857 VP_ERRBUF := sqlerrm || '[ Open vl_amount_cursor]' ;
858 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
859 Return ;
860 End ;
861
862 vl_amount_fetch := dbms_sql.fetch_rows(vl_amount_cursor) ;
863 dbms_sql.column_Value(vl_amount_cursor, 1, vl_new_amount );
864 End;
865 vl_main_amount := vl_new_amount;
866
867 fnd_file.put_line(fnd_file.log,v_acct_num || ' amount: ' || to_char(vl_main_amount)) ;
868 -----------------------------------------------------------------------------
869 -- fv_utility.debug_mesg('Account:'||v_acct_num);
870 -- fv_utility.debug_mesg('Fund Va:'||v_fund_val);
871 -- fv_utility.debug_mesg('Amount :'||vl_main_amount);
872 -----------------------------------------------------------------------------
873 vl_attributes_found := 'N' ;
874
875 RESET_ATTRIBUTES ;
876
877 END IF; -- vl_main_fetch
878
879
880 -- Process Account Number Validation . Just get the Parent acct.
881 Begin
882 Select 'X'
883 into vl_exists
884 from FV_FACTS_ATTRIBUTES
885 where facts_acct_number = v_acct_num
886 and set_of_books_id = vp_set_of_books_id;
887
888
889 -- Account Number exists in FV_FACTS_ATTRIBUTES table
890 -- and can be used to get FACTS attributes.
891 LOAD_FACTS_ATTRIBUTES (v_acct_num, v_fund_val) ;
892 vl_attributes_found := 'Y' ;
893 --VP_ERRBUF := 'LOAD ATTRIBUTES FOR ACCT:'||v_acct_num||'-'||v_fund_val;
894 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
895 Exception
896 WHEN NO_DATA_FOUND Then
897 -- fv_utility.debug_mesg('NO PROBLEM*** GET PARENT ATTRIBUTES');
898 --Reset the vl_sgl_acct_num
899 vl_sgl_acct_num := Null;
900 GET_SGL_PARENT(v_acct_num, vl_sgl_acct_num) ;
901 -- VP_ERRBUF := 'LOAD ATTRIBUTES NO DATA:'||v_acct_num||'-'||vl_sgl_acct_num;
902 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
903 IF vl_sgl_acct_num IS NULL Then
904 vl_attributes_found := 'N' ;
905 ELSE
906 vl_attributes_found := 'Y' ;
907 LOAD_FACTS_ATTRIBUTES (vl_sgl_acct_num, v_fund_val) ;
908 END IF;
909 End ;
910
911 If vp_retcode <> 0 Then
912 -- fv_utility.debug_mesg('******** IN RET CODE 2 *********');
913 Return ;
914 End If ;
915
916 -- Creating FACTS Record with Beginning Balance
917 va_balance_type_val := 'B' ;
918 va_legis_ind_val := ' ' ;
919 v_amount := vl_main_amount ;
920 --Bug 2464961 assigning NULL to vl_cohort_yr --
921 If v_cohort_Seg_name is null Then
922 vl_cohort_yr := NULL ;
923 End If ;
924 if (length(vl_cohort_yr) > 2) then
925 va_cohort := substr(vl_cohort_yr,3,2);
926 else
927 va_cohort := substr(vl_cohort_yr,1,2);
928 End if;
929
930
931 CREATE_TBAL_RECORD ;
932
933 If vp_retcode <> 0 Then
934 -- fv_utility.debug_mesg('******** IN RET CODE 3 *********');
935 Return ;
936 End If ;
937
938 End loop;
939
940 /* End of begining balance calculation */
941
942
943 BEGIN
944
945 -- Select the records for other Legislative Indicator values,
946 -- derived from Budget Execution tables and store them in a
947 -- cursor. Then roll them up and insert the summarized record
948 -- into the temp table. Dynamic SQL used for implementation.
949
950 Begin
951 vl_legis_cursor := DBMS_SQL.OPEN_CURSOR ;
952 Exception
953 When Others Then
954 vp_retcode := sqlcode ;
955 VP_ERRBUF := sqlerrm ;
956 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
957 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN SUB CURSOR OPEN'||SQLERRM);
958 Return ;
959 End ;
960 -- Change the Date format to have DD-MON-YYYY
961 vl_period_start_dt := to_char(vp_start_date,'DD-MON-YYYY');
962 vl_period_end_dt := to_char(vp_end_date,'DD-MON-YYYY');
963
964 -- fv_utility.debug_mesg('PERIOD START DATE:'||vl_period_start_dt);
965 -- fv_utility.debug_mesg('PERIOD END DATE:'||vl_period_end_dt);
966
967 /* This is the cursor we need to look into for the dates conflict */
968
969 /* and glh.Je_Source = nvl(:vp_source,glh.Je_Source)
970 and glc.Je_Category_name = nvl(:vp_category,glc.Je_Category_Name)
971 */
972
973 /*Bug #2469438
974 Modified the selct statement to read column user_je_source_name from table
975 GL_JE_SOURCES instead of table GL_JE_HEADERS*/
976 /*Bug #2469438 Undo the above change*/
977
978 /* Bug 2464961
979 Modified the select statement to select gjl.entered_dr , gjl.entered_cr
980 Seperately. these two column values would be stored in the
981 table fv_facts_temp in AMOUNT1 and AMOUNT2 columns respectively */
982
983 -- Bug 2512646 START
984
985 Begin
986
987 /* Getting Fiscal year segment name frmo fv_pya_fiscal_year_segment */
988
989 SELECT application_column_name
990 INTO v_fyr_segment_name
991 FROM fv_pya_fiscalyear_segment
992 WHERE set_of_books_id = vp_set_of_books_id;
993
994 fnd_file.put_line(FND_FILE.LOG, 'Fiscal yr Segment '||v_fyr_segment_name);
995
996 Exception
997 WHEN Others THEN
998 vp_retcode := sqlcode ;
999 VP_ERRBUF := sqlerrm ;
1000 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1001
1002 End;
1003
1004 -- Bug 2512646 END
1005
1006
1007 vl_legis_select :=
1008 'Select gjl.je_header_id,substr(gjl.reference_6,1,7),
1009 gjl.reference_1,
1010 gjl.reference_2 ,gjl.reference_3, gjl.reference_4,
1011 gjl.reference_5, gjl.reference_9,
1012 glh.date_created ,glc.user_Je_Category_name,
1013 glh.Je_Source,
1014 gjl.entered_dr entered_dr,
1015 gjl.entered_cr entered_cr,
1016 glb.Name, '||
1017 'GLCC.'||v_acc_seg_name||
1018 ',GLCC.'||v_bal_seg_name||
1019 ',glcc.code_combination_id,glh.created_by,glh.creation_date'||
1020 ',GLCC.' ||v_fyr_segment_name ||
1021 v_cohort_select||
1022 ' From gl_je_lines gjl,
1023 gl_je_headers glh,
1024 gl_je_batches glb,
1025 gl_je_categories glc,
1026 gl_code_combinations glcc,
1027 fv_fund_parameters ffp
1028 Where gjl.code_combination_id = glcc.code_combination_id
1029 and gjl.Period_Name NOT IN (Select Period_Name From GL_Period_Statuses
1030 Where Adjustment_Period_Flag = '||''''||'Y'||''''||
1031 ' And set_of_books_id = ' || vp_set_of_books_id || ')
1032 AND gjl.Je_Header_Id = glh.Je_Header_Id
1033 and glh.je_Category = glc.Je_Category_Name
1034 and glb.je_batch_id = glh.je_batch_id
1035 and glh.Je_Source = nvl('||''''||vp_source||''''||',glh.Je_Source)
1036 and glc.Je_Category_name = nvl('||''''||vp_category||''''||',glc.Je_Category_Name)
1037 AND gjl.status = ' || '''' || 'P' || '''' ||
1038 ' AND (gjl.effective_date between to_date('||''''
1039 ||vl_period_start_dt|| ''''||') AND to_date('|| ''''
1040 ||vl_period_end_dt || '''' ||
1041 ')) AND gjl.set_of_books_id = ' || vp_set_of_books_id ||
1042 ' AND glcc.' || v_bal_seg_name || ' = ffp.fund_value ' ||
1043 ' and ffp.treasury_symbol_id = ' || v_treasury_symbol_id ||
1044 ' AND glh.currency_code = ' || '''' || vp_currency_code || '''' ;
1045
1046 IF v_pagebreak1 IS NOT NULL THEN
1047 vl_legis_select := vl_legis_select ||
1048 ' AND EXISTS (SELECT 1 FROM gl_code_combinations glcc2
1049 WHERE glcc.code_combination_id = glcc2.code_combination_id '
1050 || ' AND glcc2.' || vl_pagebreak1_seg ||
1051 ' BETWEEN '|| ''''|| v_pagebreak1_low || '''' || ' AND '
1052 || ''''|| v_pagebreak1_high || '''';
1053 END IF;
1054
1055 IF v_pagebreak2 IS NOT NULL THEN
1056 vl_legis_select := vl_legis_select || ' AND glcc2.' || vl_pagebreak2_seg ||
1057 ' BETWEEN '|| ''''|| v_pagebreak2_low || '''' || ' AND '
1058 || ''''|| v_pagebreak2_high || '''';
1059 END IF;
1060
1061 IF v_pagebreak3 IS NOT NULL THEN
1062 vl_legis_select := vl_legis_select || ' AND glcc2.' || vl_pagebreak3_seg ||
1063 ' BETWEEN '|| ''''|| v_pagebreak3_low || '''' || ' AND '
1064 || ''''|| v_pagebreak3_high || '''';
1065 END IF;
1066
1067 IF v_pagebreak1 IS NOT NULL THEN
1068 vl_legis_select := vl_legis_select || ')';
1069 END IF;
1070 fnd_file.put_line(fnd_file.log,vl_legis_select);
1071 Begin
1072 dbms_sql.parse(vl_legis_cursor,vl_legis_select,DBMS_SQL.V7);
1073 Exception
1074 When Others Then
1075 vp_retcode := sqlcode ;
1076 VP_ERRBUF := sqlerrm ;
1077 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1078 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN SUB CURSOR PARSE:'||SQLERRM);
1079 Return ;
1080 End ;
1081
1082 dbms_sql.define_column(vl_legis_cursor, 1, p_je_header_id);
1083 dbms_sql.define_column(vl_legis_cursor, 2, vl_legis_ref, 240 );
1084 dbms_sql.define_column(vl_legis_cursor, 3, vl_legis_ref1, 240 );
1085 dbms_sql.define_column(vl_legis_cursor, 4, vl_legis_ref2, 240 );
1086 dbms_sql.define_column(vl_legis_cursor, 5, vl_legis_ref3, 240 );
1087 dbms_sql.define_column(vl_legis_cursor, 6, vl_legis_ref4, 240 );
1088 dbms_sql.define_column(vl_legis_cursor, 7, vl_legis_ref5, 240 );
1089 dbms_sql.define_column(vl_legis_cursor, 8, vl_legis_ref9, 240 );
1090 dbms_sql.define_column(vl_legis_cursor, 9, vl_je_date);
1091 dbms_sql.define_column(vl_legis_cursor, 10, vl_je_Category,25);
1092 dbms_sql.define_column(vl_legis_cursor, 11, vl_je_Source,25);
1093
1094 --Start Bug 2464961--
1095 dbms_sql.define_column(vl_legis_cursor, 12, vl_legis_amount_dr );
1096 dbms_sql.define_column(vl_legis_cursor, 13, vl_legis_amount_cr );
1097 --dbms_sql.define_column(vl_legis_cursor, 11, vl_legis_amount );
1098 --End Bug 2464961--
1099
1100 dbms_sql.define_column(vl_legis_cursor, 14, vl_je_name, 100 );
1101 dbms_sql.define_column(vl_legis_cursor, 15, v_acct_num, 25);
1102 dbms_sql.define_column(vl_legis_cursor, 16, v_fund_val, 25);
1103 dbms_sql.define_column(vl_legis_cursor, 17, v_ccid);
1104 dbms_sql.define_column(vl_legis_cursor, 18, vl_doc_created_by);
1105 dbms_sql.define_column(vl_legis_cursor, 19, vl_doc_creation_date);
1106 dbms_sql.define_column(vl_legis_cursor, 20, v_seg_fiscal_yr,4);
1107
1108 If v_cohort_Seg_name is not null Then
1109 -- Bug 2464961 changed 15 to 19 in the following line--
1110 dbms_sql.define_column(vl_legis_cursor, 21, vl_cohort_yr, 25);
1111 End If ;
1112 Begin
1113 vl_exec_ret := dbms_sql.execute(vl_legis_cursor);
1114 Exception
1115 When Others Then
1116 vp_retcode := sqlcode ;
1117 VP_ERRBUF := 'BAB'||sqlerrm ;
1118 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1119 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN SUB CURSOR EXECUTE'||SQLERRM);
1120 Return ;
1121 End ;
1122 vll_inner_count := 0;
1123 LOOP -- Innner Loop
1124
1125 -- Reset the Cursor Variable
1126 vl_legis_ref := Null ;
1127 vl_legis_ref1 := Null ;
1128 vl_legis_ref2 := Null ;
1129 vl_legis_ref3 := Null ;
1130 vl_legis_ref4 := Null ;
1131 vl_legis_ref5 := Null ;
1132 vl_legis_ref9 := Null ;
1133 vl_je_date := Null ;
1134 vl_je_Category := Null ;
1135 vl_je_source := Null ;
1136 --vl_legis_amount := 0 ;
1137 --Start Bug No. 2464961--
1138 vl_legis_amount_dr := 0 ;
1139 vl_legis_amount_cr := 0 ;
1140 --End Bug No. 2464961--
1141 vl_je_name := Null ;
1142 vl_cohort_yr := Null ;
1143 va_cohort := Null ;
1144 va_source := Null ;
1145 va_category := Null ;
1146 va_user_category := Null ;
1147 -- Bug 2532729
1148 p_refer2 := Null ;
1149
1150
1151 RESET_ATTRIBUTES ;
1152
1153 IF dbms_sql.fetch_rows(vl_legis_cursor) = 0 then
1154 -- fv_utility.debug_mesg('***EXITING SUB LOOP***');
1155 exit;
1156 ELSE
1157 -- Fetch the Records into Variables
1158 dbms_sql.column_value(vl_legis_cursor, 1, p_je_header_id );
1159 dbms_sql.column_value(vl_legis_cursor, 2 , vl_legis_ref );
1160 dbms_sql.column_value(vl_legis_cursor, 3 , vl_legis_ref1 );
1161 dbms_sql.column_value(vl_legis_cursor, 4, vl_legis_ref2 );
1162 dbms_sql.column_value(vl_legis_cursor, 5, vl_legis_ref3 );
1163 dbms_sql.column_value(vl_legis_cursor, 6, vl_legis_ref4 );
1164 dbms_sql.column_value(vl_legis_cursor, 7, vl_legis_ref5 );
1165 dbms_sql.column_value(vl_legis_cursor, 8, vl_legis_ref9 );
1166 dbms_sql.column_value(vl_legis_cursor, 9, vl_je_date );
1167 dbms_sql.column_value(vl_legis_cursor, 10, vl_je_category );
1168 dbms_sql.column_value(vl_legis_cursor, 11, vl_je_source );
1169 --Bug No. 2464961--
1170 dbms_sql.column_Value(vl_legis_cursor, 12, vl_legis_amount_dr );
1171 dbms_sql.column_Value(vl_legis_cursor, 13, vl_legis_amount_cr );
1172 --dbms_sql.column_Value(vl_legis_cursor, 11, vl_legis_amount );
1173 --Bug No. 2464961--
1174
1175 dbms_sql.column_value(vl_legis_cursor, 14, vl_je_name );
1176 dbms_sql.column_value(vl_legis_cursor, 15, v_acct_num);
1177 dbms_sql.column_value(vl_legis_cursor, 16, v_fund_val);
1178 dbms_sql.column_value(vl_legis_cursor, 17, v_ccid);
1179 dbms_sql.column_value(vl_legis_cursor, 18, vl_doc_created_by);
1180 dbms_sql.column_value(vl_legis_cursor, 19, vl_doc_creation_date);
1181 dbms_sql.column_value(vl_legis_cursor, 20, v_seg_fiscal_yr);
1182
1183
1184
1185 -- Process Account Number Validation . Just get the Parent acct.
1186 Begin
1187 Select 'X'
1188 into vl_exists
1189 from FV_FACTS_ATTRIBUTES
1190 where facts_acct_number = v_acct_num
1191 and set_of_books_id = vp_set_of_books_id;
1192
1193
1194 -- Account Number exists in FV_FACTS_ATTRIBUTES table
1195 -- and can be used to get FACTS attributes.
1196 LOAD_FACTS_ATTRIBUTES (v_acct_num, v_fund_val) ;
1197 vl_attributes_found := 'Y' ;
1198 /* VP_ERRBUF := 'LOAD ATTRIBUTES FOR ACCT:'||v_acct_num||
1199 '-'||v_fund_val; fnd_file.put_line(fnd_file.log, vp_errbuf) ; */
1200 Exception
1201 WHEN NO_DATA_FOUND Then
1202 -- fv_utility.debug_mesg('NO PROBLEM*** GET PARENT ATTRIBUTES');
1203 --Reset the vl_sgl_acct_num
1204 vl_sgl_acct_num := Null;
1205 GET_SGL_PARENT(v_acct_num, vl_sgl_acct_num) ;
1206 -- VP_ERRBUF := 'LOAD ATTRIBUTES NO DATA:'||v_acct_num||'-'||vl_sgl_acct_num;
1207 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1208 IF vl_sgl_acct_num IS NULL Then
1209 vl_attributes_found := 'N' ;
1210 ELSE
1211 vl_attributes_found := 'Y' ;
1212 LOAD_FACTS_ATTRIBUTES (vl_sgl_acct_num, v_fund_val) ;
1213 END IF;
1214 End ;
1215
1216
1217
1218 If v_cohort_Seg_name is not null Then
1219 --Bug 2598741 Changed to 21 in the following column value
1220 dbms_sql.column_value(vl_legis_cursor, 21, vl_cohort_yr);
1221 End If ;
1222 --Bug 2464961 assigning Null to vl_cohort_yr --
1223 If v_cohort_Seg_name is null Then
1224 vl_cohort_yr := NULL;
1225 End If ;
1226 if (length(vl_cohort_yr) > 2) then
1227 va_cohort := substr(vl_cohort_yr,3,2);
1228 else
1229 va_cohort := substr(vl_cohort_yr,1,2);
1230 End if;
1231
1232 -- fv_utility.debug_mesg('Inside Amount:'||to_char(vl_legis_amount));
1233 -- fv_utility.debug_mesg('Cohort Year :'||vl_cohort_yr);
1234 -- Add the Code call the doc Info Procedure
1235 -- Create 2 new varbles to hold the info
1236 -- Reset the variables and finally add the variable to put in the temp table.
1237 -- bganesan
1238 -- Reset the Variables
1239
1240 va_document_number := NULL;
1241 va_document_date := NULL;
1242 va_source := vl_je_source;
1243 va_category := vl_je_category;
1244 /* Commented OUT NOCOPY as the logic has been added to the select statement
1245 IF upper(va_source) = 'MANUAL' THEN
1246 Begin
1247 Select User_Je_Category_Name
1248 Into va_user_category
1249 From GL_JE_Categories
1250 Where Je_Category_Name = va_category;
1251 va_category := va_user_category;
1252 Exception
1253 When Others then
1254 Null;
1255 End ;
1256 END IF;
1257 */
1258
1259
1260 -- Bug 2532729 Start
1261
1262 IF vl_je_source = 'Receivables' THEN
1263 p_refer2 := vl_legis_ref2;
1264
1265 SELECT SUBSTR(p_refer2, 0, decode(INSTR(p_refer2, 'C'), 0, LENGTH(p_refer2),
1266 INSTR(p_refer2,'C')-1))
1267 INTO vl_legis_ref2
1268 FROM dual;
1269 END IF;
1270
1271 -- Bug 2532729 End
1272
1273
1274 -- Call the Procedure to get the Document Info
1275 GET_DOC_INFO (p_je_header_id => p_je_header_id,
1276 P_je_source_name => vl_je_source
1277 ,P_je_category_name => vl_je_Category
1278 ,P_Name => vl_je_name
1279 ,P_Date => vl_je_Date
1280 ,P_created_by => vl_doc_created_by
1281 ,P_creation_date => vl_doc_creation_date
1282 ,P_Reference1 => vl_legis_ref1
1283 ,P_Reference2 => vl_legis_ref2
1284 ,P_Reference3 => vl_legis_ref3
1285 ,P_Reference4 => vl_legis_ref4
1286 ,P_Reference5 => vl_legis_ref5
1287 ,P_Reference9 => vl_legis_ref9
1288 ,P_Ref2 => p_refer2
1289 ,P_Doc_Num => va_document_number
1290 ,P_Doc_Date => va_document_date
1291 ,P_doc_created_by => v_doc_created_by
1292 ,P_doc_creation_date => v_doc_creation_date);
1293
1294 -- bganesan
1295 -- Fixed Bug # 1326774
1296 -- June 13, 2000
1297
1298 fnd_file.put_line(FND_FILE.LOG, ' BEFORE Acct - ' || v_acct_num || 'Fund - '
1299 || v_fund_val || ' Flag ' || va_public_law_code_flag
1300 || ' Value '|| va_public_law_code_val) ;
1301
1302 If vl_attributes_found = 'Y' then
1303 If va_public_law_code_flag = 'N' Then
1304 va_public_law_code_val := NULL ;
1305 Else
1306 -- Bug 2588376
1307 -- Pulic law code is retrived from BE table.
1308 Begin
1309
1310 SELECT public_law_code
1311 INTO va_public_law_code_val
1312 FROM fv_be_trx_dtls
1313 WHERE transaction_id = vl_legis_ref1
1314 AND set_of_books_id = vp_set_of_books_id ;
1315
1316 End;
1317
1318 va_public_law_code_val := NVL(va_public_law_code_val,'000-000');
1319
1320 End If ;
1321 Else
1322 va_public_law_code_val := NULL ;
1323 End If ;
1324
1325 END IF; -- dbms
1326
1327 -------------- Legislation Indicator Processing Starts ----------------
1328 IF va_legis_Ind_flag = 'Y'
1329 OR va_advance_flag = 'Y' OR va_transfer_ind = 'Y' Then
1330
1331 -- Get the Transaction Type Value
1332 Begin
1333
1334 -- fnd_file.put_line(fnd_file.log, 'Legis1') ;
1335 Select transaction_type_id
1336 Into vl_tran_type
1337 From Fv_be_trx_dtls
1338 where transaction_id = vl_legis_ref1
1339 and set_of_books_id = vp_set_of_books_id ;
1340
1341
1342 --fnd_file.put_line(fnd_file.log, 'legis2') ;
1343 -- Get the Legislation Indicator Value from
1344 -- fv_be_transaction_types table.
1345 Select legislative_indicator
1346 into va_legis_ind_val
1347 From FV_be_transaction_types
1348 where apprn_transaction_type = vl_tran_type
1349 and set_of_books_id = vp_set_of_books_id ;
1350
1351
1352 Exception
1353 When NO_DATA_FOUND Then
1354 -- fnd_file.put_line(fnd_file.log, 'Legis No data') ;
1355 va_legis_ind_val := 'A' ;
1356 When INVALID_NUMBER Then
1357 va_legis_ind_val := 'A' ;
1358 End ;
1359
1360
1361 /* Bug No: 2494754 , Added the 'IF' statement below START */
1362
1363 IF va_advance_flag = 'Y' THEN
1364
1365
1366 -- Get the Advance Type Value
1367 BEGIN
1368
1369
1370 SELECT advance_type
1371 INTO va_advance_type_val
1372 FROM fv_be_trx_dtls
1373 WHERE transaction_id = vl_legis_ref1
1374 AND set_of_books_id = vp_set_of_books_id ;
1375
1376 IF v_debug THEN
1377 fnd_file.put_line(fnd_file.log,
1378 'Advance Type - '||
1379 nvl(va_advance_type_val, 'Advance Type Null')) ;
1380 END IF ;
1381
1382
1383 -- If the advance_type value is null then set it to 'X'
1384 IF va_advance_type_val IS NULL THEN
1385 va_advance_type_val := 'X';
1386 END IF;
1387
1388 EXCEPTION
1389 WHEN NO_DATA_FOUND THEN
1390
1391 -- This Exception fires when
1392 -- the advance type
1393 -- cannot be found.
1394 va_advance_type_val := 'X';
1395 WHEN INVALID_NUMBER THEN
1396 va_advance_type_val := 'X';
1397 END;
1398
1399 END IF; -- Advance Type processing
1400
1401
1402 -- Transfer Acct specific processing
1403 IF va_transfer_ind = 'Y' THEN
1404
1405
1406 -- Get the Transfer Values
1407 BEGIN
1408
1409
1410 SELECT dept_id,
1411 main_account
1412 INTO va_transfer_dept_id,
1413 va_transfer_main_acct
1414 FROM fv_be_trx_dtls
1415 WHERE transaction_id = vl_legis_ref1
1416 AND set_of_books_id = vp_set_of_books_id ;
1417
1418 IF v_debug THEN
1419 fnd_file.put_line(fnd_file.log,
1420 'Transfer Dept ID - '||
1421 nvl(va_transfer_dept_id, 'Transfer Dept ID Null')) ;
1422 fnd_file.put_line(fnd_file.log,
1423 'Transfer Main Acct - '||
1424 nvl(va_transfer_main_acct, 'Transfer Main Acct Null')) ;
1425
1426 END IF ;
1427
1428 -- If the Transfer values are null then set default values
1429 -- Since both dept_id and main_acct are null or both have
1430 -- values test if one of them is null
1431
1432 IF va_transfer_dept_id IS NULL THEN
1433 va_transfer_dept_id := '00';
1434 va_transfer_main_acct := '0000';
1435 END IF;
1436 EXCEPTION
1437 WHEN NO_DATA_FOUND THEN
1438 -- This Exception fires when
1439 -- the transfer info
1440 -- cannot be found.
1441 va_transfer_dept_id := '00';
1442 va_transfer_main_acct := '0000';
1443 END;
1444
1445 END IF; -- Transfer Acct processing
1446
1447 -- Processing Budget Year Authority attribute
1448
1449 /* Bug No : 2494754 END */
1450 /* Bug No : 2512646 START */
1451
1452 IF vl_sgl_acct_num IS NOT NULL then
1453
1454 BEGIN
1455
1456 SELECT balance_type
1457 INTO va_balance_type_flag
1458 FROM FV_FACTS_ATTRIBUTES
1459 WHERE Facts_Acct_Number = vl_sgl_acct_num
1460 and set_of_books_id = vp_set_of_books_id ;
1461
1462 EXCEPTION
1463 When Others Then
1464
1465 vp_retcode := sqlcode ;
1466 vp_errbuf := ' Error! No Attributes Definied for the Account - ' ||
1467 vl_sgl_acct_num || ' [PROCESS_TBAL_TRANSCTIONS]'||sqlerrm ;
1468
1469 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1470 Return ;
1471
1472 END;
1473
1474 IF va_balance_type_flag In ('S', 'E') Then
1475 va_balance_type_val := 'E' ;
1476 v_record_category := 'REPORTED' ;
1477 End if;
1478
1479 IF va_balance_type_flag IN ('S', 'B') Then
1480 va_balance_type_val := 'B' ;
1481 v_record_category := 'REPORTED' ;
1482 End if;
1483
1484 v_year_budget_auth := NULL;
1485
1486
1487 IF v_record_category = 'REPORTED' AND vl_sgl_acct_num IS NOT NULL THEN
1488
1489
1490 IF v_excptn_cat IS NULL THEN
1491
1492 BEGIN
1493
1494
1495 SELECT disbursements_flag
1496 INTO vl_disbursements_flag
1497 FROM fv_facts_ussgl_accounts
1498 WHERE ussgl_account = v_acct_num;
1499 --ussgl_account = v_ussgl_acct;
1500
1501 EXCEPTION
1502 When Others Then
1503 vp_retcode := sqlcode ;
1504 vp_errbuf := sqlerrm ||
1505 ' [ PROCESS_TBAL_TRANSCTIONS vl_disbursements_flag - ] ' ;
1506 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1507 Return ;
1508 END;
1509
1510 END IF;
1511
1512 BEGIN
1513 Select FTS.Time_Frame, FFFA.financing_account
1514 INTO v_time_frame, v_financing_acct
1515 From FV_FACTS_FEDERAL_ACCOUNTS FFFA,
1516 FV_TREASURY_SYMBOLS FTS
1517 Where FFFA.Federal_acct_symbol_id = FTS.Federal_acct_symbol_id
1518 AND FTS.treasury_symbol = vp_treasury_symbol
1519 AND FTS.set_of_books_id = vp_set_of_books_id
1520 AND FFFA.set_of_books_id = vp_set_of_books_id ;
1521
1522 EXCEPTION
1523 When Others Then
1524 vp_retcode := sqlcode ;
1525 vp_errbuf := sqlerrm ||
1526 ' [ PROCESS_TBAL_TRANSCTIONS - v_time_frame ] ' ;
1527 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1528 --Return ;
1529 END;
1530
1531 IF v_time_frame = 'NO_YEAR'
1532 AND v_financing_acct = 'N'
1533 AND vl_disbursements_flag = 'Y'
1534 AND (v_amount_dr > 0 OR v_amount_cr > 0) THEN
1535
1536
1537
1538 BEGIN
1539
1540 SELECT fyr_segment_value
1541 INTO vl_fyr_segment_value
1542 FROM fv_pya_fiscalyear_map
1543 WHERE period_year = v_fiscal_year
1544 AND set_of_books_id = vp_set_of_books_id;
1545
1546 EXCEPTION
1547 When Others Then
1548 vp_retcode := sqlcode ;
1549 vp_errbuf := sqlerrm ||
1550 ' [ PROCESS_TBAL_TRANSCTIONS vl_fyr_segment_value - ] ' ;
1551 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1552 --Return ;
1553 END;
1554
1555 IF vl_fyr_segment_value IS NOT NULL THEN
1556 IF vl_fyr_segment_value = v_seg_fiscal_yr THEN
1557 v_year_budget_auth := 'NEW';
1558 ELSE
1559 v_year_budget_auth := 'BAL';
1560 END IF;
1561 END IF;
1562 END IF;
1563 END IF;
1564
1565 END IF;
1566
1567 /* Bug No : 2512646 END */
1568
1569
1570 va_balance_type_val := 'E' ;
1571 --Start Bug 2464961--
1572 --v_amount := vl_legis_amount ;
1573 v_amount_dr := vl_legis_amount_dr ;
1574 v_amount_cr := vl_legis_amount_cr ;
1575 --End Bug 2464961--
1576
1577 If vl_attributes_found = 'N' then
1578 RESET_ATTRIBUTES ;
1579 End If ;
1580
1581 CREATE_TBAL_RECORD ;
1582
1583 If vp_retcode <> 0 Then
1584 -- fv_utility.debug_mesg('******** IN RET CODE 4 *********');
1585 Return ;
1586 End If ;
1587 -------------- Normal Processing ----------------
1588 ELSE -- Legis Flag
1589
1590 va_legis_ind_val := ' ' ;
1591 va_balance_type_val := 'E' ;
1592 --Start Bug 2464961--
1593 --v_amount := vl_legis_amount ;
1594 v_amount_dr := vl_legis_amount_dr ;
1595 v_amount_cr := vl_legis_amount_cr ;
1596 --End Bug 2464961--
1597
1598 /* Commented for Bug 2539852
1599 If vl_attributes_found = 'N' then
1600 RESET_ATTRIBUTES ;
1601 End If ;
1602 */
1603
1604 CREATE_TBAL_RECORD ;
1605 If vp_retcode <> 0 Then
1606 -- fv_utility.debug_mesg('******** IN RET CODE 5 *********');
1607 Return ;
1608 End If ;
1609 END IF; -- Legis Flag
1610 END LOOP; -- Inner Loop
1611 -- fv_utility.debug_mesg('***DONE SUB LOOP ****');
1612 EXCEPTION
1613 -- Process any Exceptions in Legislative Indicator
1614 -- Processing
1615 When Others Then
1616 vp_retcode := sqlcode ;
1617 vp_errbuf := sqlerrm ||
1618 ' [ PROCESS_TBAL_TRANSCTIONS- ] ' ;
1619 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1620 -- fv_utility.debug_mesg('WHEN OTHERS IN SUB LOOP'||SQLERRM);
1621 Return ;
1622 End ;
1623 -----------------------------------------------------------------
1624 -- Submitting Transaction Registar Report
1625 -----------------------------------------------------------------
1626 vl_printer_name := FND_PROFILE.VALUE('PRINTER');
1627 vl_print_option := FND_REQUEST.SET_PRINT_OPTIONS( printer =>vl_printer_name
1628 ,copies => 1);
1629
1630 vl_req_id := FND_REQUEST.SUBMIT_REQUEST ('FV','FVFCTRGR','','',FALSE,
1631 vp_set_of_books_id,v_chart_of_accounts_id,vp_start_date,vp_end_date, v_fiscal_year,
1632 vp_treasury_symbol, v_treasury_symbol_id,vp_source,vp_category,
1633 v_pagebreak1,v_pagebreak1_low,v_pagebreak1_high,
1634 v_pagebreak2,v_pagebreak2_low,v_pagebreak2_high,
1635 v_pagebreak3,v_pagebreak3_low,v_pagebreak3_high,
1636 vp_currency_code) ;
1637
1638 -- if concurrent request submission failed then abort process
1639 -- fv_utility.debug_mesg('Concurrent Request Id For FACTS Transaction
1640 -- Register Report : ' || vl_req_id);
1641
1642 if vl_req_id = 0 then
1643 vp_errbuf := 'Error submitting Transaction Register Balance with Attributes Report ';
1644 vp_retcode := -1 ;
1645 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1646 Else
1647 vp_errbuf:= 'Transaction Register Report submitted successfully with the Request ID : '||
1648 to_char(vl_req_id) ;
1649 fnd_file.put_line(fnd_file.log, vp_errbuf);
1650 IF vl_print_option THEN
1651 vp_errbuf:= 'Transaction Register Report will be send for printing to printer: '||vl_printer_name;
1652 fnd_file.put_line(fnd_file.log,vp_errbuf);
1653 END IF;
1654 end if;
1655
1656 EXCEPTION
1657
1658 When Others Then
1659 vp_retcode := sqlcode ;
1660 vp_errbuf := 'WHEN OTHERS IN PROCESS TBAL TRANSACTION:'||sqlerrm ;
1661 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1662 -- fv_utility.debug_mesg('WHEN OTHERS IN PROCESS TBAL TRANSACTION:'||SQLERRM);
1663
1664 END PROCESS_TBAL_TRANSACTIONS ;
1665
1666 -- -------------------------------------------------------------------
1667 -- PROCEDURE LOAD_FACTS_ATTRIBUTES
1668 -- -------------------------------------------------------------------
1669 -- This procedure selects the attributes for the Account number
1670 -- segment from FV_FACTS_ATTRIBUTES table and load them into global
1671 -- variables for usage in the FACTS Main process. It also calculates
1672 -- one time pull up values for the account number that does not
1673 -- require drill down into GL transactions.
1674 -- ------------------------------------------------------------------
1675 PROCEDURE LOAD_FACTS_ATTRIBUTES (acct_num varchar2,
1676 fund_val Varchar2)
1677 IS
1678 vl_financing_acct_flag Varchar2(1) ;
1679 vl_established_fy number ;
1680 vl_resource_type Varchar2(80) ;
1681 vl_fund_category Varchar2(1) ;
1682 BEGIN
1683
1684
1685 Begin
1686
1687 SELECT balance_type,
1688 public_law_code,
1689 reimburseable_flag,
1690 availability_time,
1691 bea_category,
1692 apportionment_category,
1693 substr(transaction_partner,1,1),
1694 borrowing_source,
1695 definite_indefinite_flag,
1696 legislative_indicator,
1697 authority_type,
1698 deficiency_flag,
1699 function_flag,
1700 advance_flag, /* Bug No: 2494754 */
1701 transfer_flag
1702 INTO va_balance_type_flag,
1703 va_public_law_code_flag,
1704 va_reimburseable_flag,
1705 va_availability_flag,
1706 va_bea_category_flag,
1707 va_appor_cat_flag,
1708 va_transaction_partner_val,
1709 va_borrowing_source_flag,
1710 va_def_indef_flag,
1711 va_legis_ind_flag,
1712 va_authority_type_flag,
1713 va_deficiency_flag,
1714 va_function_flag,
1715 va_advance_flag, /* Bug No: 2494754 */
1716 va_transfer_ind
1717 FROM FV_FACTS_ATTRIBUTES
1718 WHERE Facts_Acct_Number = acct_num
1719 AND set_of_books_id = vp_set_of_books_id;
1720 Exception
1721
1722 When NO_DATA_FOUND Then
1723 vp_retcode := -1 ;
1724 vp_errbuf := 'Error! No Attributes Definied for the Account - ' ||
1725 v_acct_num || ' [LOAD_FACTS_ATTRIBURES]' ;
1726 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1727 -- fv_utility.debug_mesg('ERROR NO DATA FOUND [LOAD_FACTS_ATTRIBUTES]'||vp_errbuf);
1728 return;
1729 When Others Then
1730 vp_retcode := sqlcode ;
1731 vp_errbuf := sqlerrm ;
1732 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1733 -- fv_utility.debug_mesg('WHEN OTHERS ERROR IN [LOAD_FACTS_ATTRIBUTES]'||SQLERRM);
1734 return;
1735 End ;
1736
1737
1738 -- Getting the One time Pull up Values
1739 --pkpatel :Changed to fix Bug 1575992
1740 Begin
1741
1742 Select UPPER(fts.resource_type),
1743 def_indef_flag,
1744 ffp.fund_category
1745 INTO vl_resource_type,
1746 va_def_indef_val,
1747 vl_fund_category
1748 From fv_treasury_symbols fts,
1749 fv_fund_parameters ffp
1750 WHERE ffp.treasury_symbol_id = fts.treasury_symbol_id
1751 AND ffp.fund_value = fund_val
1752 AND fts.treasury_symbol = vp_treasury_symbol
1753 AND fts.set_of_books_id = vp_set_of_books_id
1754 AND ffp.set_of_books_id = vp_set_of_books_id ;
1755 Exception
1756
1757 When NO_DATA_FOUND Then
1758 --vp_retcode := -1 ;
1759 vp_errbuf := 'Error getting Fund Category value for the fund - '||
1760 fund_val || ' [LOAD_FACTS_ATTRIBURES]' ;
1761 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1762 -- fv_utility.debug_mesg('NO DATA FOUND '||vp_errbuf);
1763 --return;
1764
1765 When Others Then
1766 vp_retcode := sqlcode ;
1767 vp_errbuf := sqlerrm || ' [LOAD_FACTS_ATTRIBURES]' ;
1768 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1769 -- fv_utility.debug_mesg('WHEN OTHERS'||vp_errbuf);
1770 return;
1771 End ;
1772
1773
1774
1775 ------------------------------------------------
1776 -- Deriving Indefinite Definite Flag
1777 ------------------------------------------------
1778 If nvl(va_def_indef_flag,'X') <> 'Y' Then
1779 va_def_indef_val := NULL;
1780 End If ;
1781
1782 ------------------------------------------------
1783 -- Deriving Public Law Code Flag
1784 ------------------------------------------------
1785 If va_public_law_code_flag = 'N' Then
1786 va_public_law_code_val := NULL ;
1787 End If ;
1788
1789
1790 IF va_availability_flag = 'N' Then
1791 va_availability_flag := NULL;
1792 End If ;
1793
1794 IF va_transaction_partner_val = 'N' Then
1795 va_transaction_partner_val := NULL;
1796 End If ;
1797 ------------------------------------------------
1798 -- Deriving Apportionment Category Code
1799 ------------------------------------------------
1800
1801
1802 If va_appor_cat_flag = 'Y' Then
1803 If vl_fund_category IN ('A','S') Then
1804 va_appor_cat_val := 'A' ;
1805 ElsIf vl_fund_category IN ('B','T') Then
1806 va_appor_cat_val := 'B' ;
1807 ElsIf vl_fund_category in ('R','C') Then
1808 va_appor_cat_val := 'C' ;
1809 Else
1810 va_appor_cat_val := NUll;
1811 End If ;
1812 Else
1813 va_appor_cat_val := NULL;
1814 End If ;
1815
1816 ------------------------------------------------
1817 -- Deriving Authority Type
1818 ------------------------------------------------
1819 If nvl(va_authority_type_flag,'N') <> 'N' then
1820 va_authority_type_val := va_authority_type_flag;
1821 Else
1822 va_authority_type_val := ' ' ;
1823 End If ;
1824
1825 --------------------------------------------------------------------
1826 -- Deriving Reimburseable Flag Value
1827 --------------------------------------------------------------------
1828
1829 If va_reimburseable_flag = 'Y' Then
1830 If vl_fund_category IN ('A', 'B','C') Then
1831 va_reimburseable_val := 'D' ;
1832 ElsIf vl_fund_category in ('R','S','T') then
1833 va_reimburseable_val := 'R' ;
1834 Else
1835 va_reimburseable_val := NULL;
1836 End If ;
1837 Else
1838 va_reimburseable_val := NULL;
1839 End If ;
1840
1841
1842 --------------------------------------------------------------------
1843 -- Deriving BEA Category and Borrowing Source Values
1844 --------------------------------------------------------------------
1845 If va_bea_category_flag = 'Y' OR va_borrowing_source_flag = 'Y' then
1846 Begin
1847 Select RPAD(substr(ffba.bea_category,1,5), 5),
1848 RPAD(substr(ffba.borrowing_source,1,6), 6)
1849 Into va_bea_category_val,
1850 va_borrowing_source_val
1851 From fv_facts_budget_accounts ffba,
1852 fv_facts_federal_accounts fffa,
1853 fv_treasury_symbols fts ,
1854 fv_facts_bud_fed_accts ffbfa
1855 Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
1856 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
1857 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
1858 AND fts.treasury_symbol = vp_treasury_symbol
1859 AND fts.set_of_books_id = vp_set_of_books_id
1860 AND fffa.set_of_books_id = vp_set_of_books_id
1861 AND ffbfa.set_of_books_id = vp_set_of_books_id
1862 AND ffba.set_of_books_id = vp_set_of_books_id ;
1863
1864 If va_bea_category_flag = 'N' then
1865 va_bea_category_val := NULL;
1866 End If ;
1867
1868 If va_borrowing_source_flag = 'N' then
1869 va_borrowing_source_val := NULL;
1870 End If ;
1871
1872 Exception
1873 When NO_DATA_FOUND then
1874 -- vp_retcode := -1 ;
1875 -- vp_errbuf := 'Error Getting BEA Category/Borrowing Source
1876 -- values [LOAD_FACTS_ATTRIBUTES]' ;
1877 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1878 -- -- fv_utility.debug_mesg('NO DATA FOUND'||vp_errbuf);
1879 -- return;
1880 va_bea_category_val := Null;
1881 va_borrowing_source_val := Null;
1882 End ;
1883 Else
1884 va_bea_category_val := Null;
1885 va_borrowing_source_val := Null;
1886 End If ;
1887
1888
1889 va_def_liquid_flag := ' ' ;
1890 va_deficiency_flag := ' ' ;
1891
1892 --------------------------------------------------------------------
1893 -- Deriving Budget Function
1894 --------------------------------------------------------------------
1895 If va_function_flag = 'Y' then
1896 Begin
1897 Select RPAD(substr(ffba.budget_function,1,3), 3)
1898 Into va_budget_function
1899 From fv_facts_budget_accounts ffba,
1900 fv_facts_federal_accounts fffa,
1901 fv_treasury_symbols fts ,
1902 fv_facts_bud_fed_accts ffbfa
1903 Where fts.federal_acct_symbol_id = fffa.federal_acct_symbol_id
1904 AND fffa.federal_acct_symbol_id = ffbfa.federal_acct_symbol_id
1905 AND ffbfa.budget_acct_code_id = ffba.budget_acct_code_id
1906 AND fts.treasury_symbol = vp_treasury_symbol
1907 AND fts.set_of_books_id = vp_set_of_books_id
1908 AND fffa.set_of_books_id = vp_set_of_books_id
1909 AND ffbfa.set_of_books_id = vp_set_of_books_id
1910 AND ffba.set_of_books_id = vp_set_of_books_id ;
1911 Exception
1912 When NO_DATA_FOUND then
1913
1914 -- Create Exception Record for Budget Function
1915 -- v_record_category := 'BUDGET_FNCTN_NOT_DEFINED' ;
1916 Create_tbal_Record ;
1917 End ;
1918 Else
1919 va_budget_function := RPAD(' ', 3);
1920 End If ;
1921
1922
1923 EXCEPTION
1924 When Others Then
1925 vp_retcode := sqlcode ;
1926 vp_errbuf := sqlerrm || ' [LOAD_FACTS_ATTRIBUTES]' ;
1927 -- fv_utility.debug_mesg('WHEN OTHERS'||vp_errbuf);
1928 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
1929
1930 END LOAD_FACTS_ATTRIBUTES ;
1931
1932
1933 -- -------------------------------------------------------------------
1934 -- PROCEDURE RESET_ATTRIBUTES
1935 -- -------------------------------------------------------------------
1936 -- ------------------------------------------------------------------
1937 Procedure RESET_ATTRIBUTES
1938 is
1939 Begin
1940
1941 -- Reset all the Attribute Variable
1942 va_balance_type_flag := Null ;
1943 va_public_law_code_flag := Null ;
1944 va_reimburseable_flag := Null ;
1945 va_availability_flag := Null ;
1946 va_bea_category_flag := Null ;
1947 va_appor_cat_flag := Null ;
1948 va_transaction_partner_val := Null ;
1949 va_borrowing_source_flag := Null ;
1950 va_def_indef_flag := Null ;
1951 va_legis_ind_flag := Null ;
1952 va_authority_type_flag := Null ;
1953 va_deficiency_flag := Null ;
1954 va_function_flag := Null ;
1955
1956 va_balance_type_val := Null ;
1957 va_def_indef_val := Null ;
1958 va_public_law_code_val := Null ;
1959 va_appor_cat_val := Null ;
1960 va_authority_type_val := Null ;
1961 va_reimburseable_val := Null ;
1962 va_bea_category_val := Null ;
1963 va_borrowing_source_val := Null ;
1964 va_availability_flag := Null ;
1965 va_legis_ind_val := Null ;
1966 va_document_number := NULL ;
1967 va_document_date := NULL ;
1968
1969 --Bug No : 2494754
1970
1971 va_availability_flag := ' ';
1972 va_function_flag := ' ';
1973 va_budget_function := ' ';
1974 va_advance_type_val := ' ';
1975 va_transfer_dept_id := ' ';
1976 va_transfer_main_acct := ' ';
1977
1978 End Reset_Attributes ;
1979
1980
1981
1982
1983
1984
1985
1986
1987
1988
1989 -- -------------------------------------------------------------------
1990 -- PROCEDURE GET_SGL_PARENT
1991 -- -------------------------------------------------------------------
1992 -- Gets the SGL Parent Account for the passed account number
1993 -- ------------------------------------------------------------------
1994 Procedure GET_SGL_PARENT(
1995 Acct_num Varchar2,
1996 sgl_acct_num OUT NOCOPY Varchar2)
1997 is
1998 vl_exists varchar2(1) ;
1999 vl_acc_val_set_id Number ;
2000 Begin
2001 -- Getting the Value Set Id for the Account Segment
2002 Begin /* Value Set Id */
2003 -- Getting the Value set Id for finding hierarchies
2004 select flex_value_set_id
2005 into vl_acc_val_set_id
2006 from fnd_id_flex_segments
2007 where application_column_name = v_acc_seg_name
2008 and id_flex_code = 'GL#'
2009 and id_flex_num = v_chart_of_accounts_id;
2010
2011 --vp_errbuf := 'SGL Value Set Id :'||to_Char(vl_acc_val_set_id);
2012 --fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2013
2014 Exception
2015 When NO_DATA_FOUND Then
2016 vp_retcode := -1 ;
2017 vp_errbuf := 'Error getting Value Set Id for segment'
2018 ||v_acc_seg_name||' [GET_SGL_PARENT]' ;
2019 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2020 -- fv_utility.debug_mesg('WHEN NO DATA FOUND'||vp_errbuf);
2021
2022 return;
2023 End ; /* Value Set Id */
2024
2025 -- Finding the parent of the Account Number in GL
2026 Begin /* Finding Parent From GL */
2027 -- Finding the parent
2028 --vp_errbuf := 'SGL Parent Account Before:'||sgl_acct_num||'-'||acct_num;
2029 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2030 Select parent_flex_value
2031 Into sgl_acct_num
2032 From fnd_flex_value_hierarchies
2033 where (ACCT_NUM Between child_flex_value_low
2034 and child_flex_value_high)
2035 and parent_flex_value <> 'T'
2036 AND flex_value_set_id = vl_acc_val_set_id
2037 and parent_flex_value in
2038 (Select ussgl_account
2039 From fv_facts_ussgl_accounts
2040 Where ussgl_account = parent_flex_value);
2041
2042 --vp_errbuf := 'SGL Parent Account:'||sgl_acct_num||'-'||acct_num;
2043 --fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2044 Begin
2045 -- Look for parent in FV_FACTS_ATTRIBUTES table
2046 Select 'X'
2047 Into vl_exists
2048 From fv_facts_attributes
2049 where facts_acct_number = sgl_acct_num
2050 and set_of_books_id = vp_set_of_books_id;
2051
2052 --vp_errbuf := 'SQL Account Exists:'||vl_exists;
2053 --fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2054 -- Return the account Number
2055 Return ;
2056 Exception
2057 When NO_DATA_FOUND then
2058 sgl_acct_num := NULL ;
2059 -- vp_errbuf := 'SGL NO DATA'||vl_exists;
2060 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2061 Return ;
2062 End ;
2063 Exception
2064 When NO_DATA_FOUND or TOO_MANY_ROWS Then
2065 -- No Parent Exists or Too Many Parents. Return Nulls
2066 -- vp_errbuf := sqlerrm || ' [GET SGL ACCOUNT]' ;
2067 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2068 Return ;
2069 When OTHERS Then
2070 -- vp_errbuf := sqlerrm || ' [GET SGL ACCOUNT]' ;
2071 -- fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2072 Return;
2073 End ;
2074
2075 End GET_SGL_PARENT ;
2076
2077 -- -------------------------------------------------------------------
2078 -- PROCEDURE CREATE_TBAL_RECORD
2079 -- -------------------------------------------------------------------
2080 -- Inserts a new record into FV_FACTS_TEMP table with the current
2081 -- values from the global variables with record category TRIAL_BALANCE
2082 -- ------------------------------------------------------------------
2083 PROCEDURE CREATE_TBAL_RECORD
2084 IS
2085 BEGIN
2086
2087 -- If the Balance Type is 'B' then the amount is taken from the
2088 -- V_BEGIN_AMOUNT, otherwise in case of 'E', the amount is taken
2089 -- from V_AMOUNT
2090
2091 /*Bug 2464961
2092 The following insert statement would store data into two columns.
2093 i.e. amount1 and amount2 for v_amount_dr and v_amount_cr respectively*/
2094
2095 /* Bug No : 2494754 */
2096
2097 INSERT INTO FV_FACTS_TEMP
2098 (TBAL_ACCT_NUM ,
2099 TBAL_FUND_VALUE ,
2100 COHORT ,
2101 BEGIN_END ,
2102 INDEF_DEF_FLAG ,
2103 PUBLIC_LAW ,
2104 APPOR_CAT_CODE ,
2105 AUTHORITY_TYPE ,
2106 TRANSACTION_PARTNER ,
2107 REIMBURSEABLE_FLAG ,
2108 BEA_CATEGORY ,
2109 BORROWING_SOURCE ,
2110 AVAILABILITY_FLAG ,
2111 LEGISLATION_FLAG ,
2112 AMOUNT ,
2113 TREASURY_SYMBOL_ID ,
2114 FCT_INT_RECORD_CATEGORY ,
2115 DOCUMENT_NUMBER ,
2116 DOCUMENT_DATE ,
2117 SGL_ACCT_NUMBER ,
2118 APPOR_CAT_B_TXT ,
2119 BUDGET_FUNCTION ,
2120 ADVANCE_FLAG ,
2121 TRANSFER_DEPT_ID ,
2122 TRANSFER_MAIN_ACCT ,
2123 YEAR_BUDGET_AUTH ,
2124 CODE_COMBINATION_ID ,
2125 DOCUMENT_CREATED_BY ,
2126 DOCUMENT_CREATION_DATE ,
2127 DOCUMENT_SOURCE ,
2128 AMOUNT1 ,
2129 AMOUNT2 )
2130 Values (v_acct_num ,
2131 v_fund_val ,
2132 va_cohort ,
2133 va_balance_type_val ,
2134 va_def_indef_val ,
2135 va_public_law_code_val ,
2136 va_appor_cat_val ,
2137 va_authority_type_val ,
2138 va_transaction_partner_val,
2139 va_reimburseable_val ,
2140 va_bea_category_val ,
2141 va_borrowing_source_val ,
2142 va_availability_flag ,
2143 va_legis_ind_val ,
2144 v_amount ,
2145 v_treasury_symbol_id ,
2146 'TRIAL_BALANCE' ,
2147 va_document_number ,
2148 va_document_date ,
2149 va_source ,
2150 va_category ,
2151 va_budget_function ,
2152 va_advance_type_val ,
2153 va_transfer_dept_id ,
2154 va_transfer_main_acct ,
2155 v_year_budget_auth ,
2156 v_ccid ,
2157 v_doc_created_by ,
2158 v_doc_creation_date ,
2159 va_source ,
2160 v_amount_dr ,
2161 v_amount_cr ) ;
2162
2163 EXCEPTION
2164 When Others Then
2165 vp_retcode := sqlcode ;
2166 vp_errbuf := sqlerrm || ' [CREATE_TBAL_RECORD] ' ;
2167 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2168 -- fv_utility.debug_mesg('WHEN OTHERS'||vp_errbuf);
2169 return;
2170 END CREATE_TBAL_RECORD ;
2171
2172 ----
2173
2174
2175
2176 Procedure GET_DOC_INFO (
2177 p_je_header_id in number,
2178 P_je_source_name IN Varchar2
2179 ,P_je_category_name IN Varchar2
2180 ,P_Name IN Varchar2
2181 ,P_Date IN Date
2182 ,P_created_by IN Number
2183 ,P_creation_date IN Date
2184 ,P_Reference1 IN Varchar2
2185 ,P_Reference2 IN Varchar2
2186 ,P_Reference3 IN Varchar2
2187 ,P_Reference4 IN Varchar2
2188 ,P_Reference5 IN Varchar2
2189 ,P_Reference9 IN Varchar2
2190 ,P_Ref2 IN Varchar2
2191 ,P_Doc_Num OUT NOCOPY Varchar2
2192 ,P_Doc_Date OUT NOCOPY Date
2193 ,P_doc_created_by OUT NOCOPY Number
2194 ,P_doc_creation_date OUT NOCOPY Date) IS
2195
2196 p_refer2 Varchar2(240);
2197 p_refer4 Varchar2(240);
2198
2199 -- Bug 2606958 Start
2200 l_temp_cr_hist_id Varchar2(240);
2201 l_rev_exists Varchar2(1) := 'N';
2202 lv_document_num Varchar2(240);
2203 lv_doc_date Date;
2204 lv_doc_creation_date Date;
2205 lv_doc_created_by Number;
2206
2207 l_doc_date_d Date;
2208 l_doc_creation_date_d Date;
2209 l_doc_created_by_d Number;
2210 l_void_date Date;
2211 l_check_date Date;
2212 l_inv_payment_id Number;
2213
2214 -- Bug 2606958 End
2215
2216 -- Bug 2532729
2217 l_cash_receipt_hist_id varchar2(240);
2218 Cursor Pur_Rec is
2219 Select rt.Transaction_Date
2220 ,rcv.Receipt_Num,rcv.created_by,rcv.creation_date
2221 From RCV_Transactions rt
2222 ,RCV_Shipment_Headers rcv
2223 Where rt.Shipment_Header_Id = rcv.Shipment_Header_Id
2224 And to_char(rt.Transaction_ID) = P_Reference5;
2225
2226 Cursor Pay_Pur is
2227 Select inv.Invoice_Num
2228 ,inv.INvoice_Date,inv.created_by,inv.creation_date
2229 From AP_Invoices_all inv
2230 Where to_char(inv.Invoice_Id) = P_Reference2;
2231
2232 Cursor Pay_Pay is
2233 Select Distinct api.invoice_num,
2234 apc.check_date,api.created_by,api.creation_date
2235 From ap_checks_all apc,
2236 ap_invoices_all api,
2237 ap_invoice_payments_all apip
2238 where to_char(apc.check_id) = p_reference3
2239 and to_char(api.invoice_id) = p_reference2
2240 and apc.check_id = apip.check_id
2241 and api.invoice_id = apip.invoice_id ;
2242
2243 Cursor Receivables is
2244 Select
2245 DECODE(l_rev_exists, 'Y', reversal_date, receipt_date),
2246 DECODE(l_rev_exists, 'Y', l_doc_created_by_d, created_by),
2247 DECODE(l_rev_exists, 'Y', l_doc_creation_date_d, creation_date)
2248 From AR_Cash_Receipts_All
2249 Where to_char(Cash_Receipt_Id) = p_refer2;
2250
2251 --Bug 2532729 Start
2252
2253 -- Bug 2606958 Start
2254 Cursor Receivables_Exists is
2255 SELECT 'Y'
2256 FROM ar_cash_receipt_history_all
2257 WHERE cash_receipt_history_id = to_number(l_cash_receipt_hist_id);
2258
2259 Cursor Receivables_Applications is
2260 SELECT cash_receipt_history_id
2261 FROM ar_receivable_applications_all
2262 WHERE receivable_application_id = to_number(l_cash_receipt_hist_id);
2263
2264 Cursor Receivables_Hist is
2265 SELECT 'Y'
2266 FROM ar_cash_receipt_history_all
2267 WHERE cash_receipt_history_id = to_number(l_cash_receipt_hist_id);
2268
2269
2270 Cursor Receivables_History is
2271 SELECT 'Y', creation_date, created_by
2272 FROM ar_cash_receipt_history_all
2273 WHERE reversal_cash_receipt_hist_id = to_number(l_cash_receipt_hist_id);
2274
2275 Cursor Receivables_Misc is
2276 SELECT 'Y', creation_date, created_by
2277 FROM ar_misc_cash_distributions_all
2278 WHERE misc_cash_distribution_id = l_cash_receipt_hist_id
2279 AND created_from = 'ARP_REVERSE_RECEIPT.REVERSE';
2280
2281 Cursor Receivables_Distrib is
2282 SELECT 'Y'
2283 FROM ar_misc_cash_distributions_all
2284 WHERE misc_cash_distribution_id = to_number(l_cash_receipt_hist_id);
2285
2286 -- Bug 2606958 End
2287
2288 Cursor Pay_Treas_Check is
2289 SELECT void_date, checkrun_name
2290 FROM ap_checks_all
2291 WHERE check_id = p_reference3;
2292
2293 Cursor Pay_Treas_Void is
2294 SELECT creation_date, created_by
2295 FROM ap_invoice_payments_all
2296 WHERE check_id = p_reference3
2297 AND invoice_payment_id = (SELECT max(invoice_payment_id)
2298 FROM ap_invoice_payments_all
2299 WHERE check_id = p_reference3);
2300
2301 Cursor Pay_Treas is
2302 SELECT ftc.checkrun_name,
2303 ftc.treasury_doc_date,
2304 ftc.creation_date,
2305 ftc.created_by
2306 FROM fv_treasury_confirmations_all ftc
2307 WHERE to_char(ftc.treasury_confirmation_id) = p_reference1;
2308
2309 Cursor Pay_Pay_Check is
2310 SELECT void_date, check_date
2311 FROM ap_checks_all
2312 WHERE check_id = p_reference3;
2313
2314 Cursor Pay_Pay_Void is
2315 SELECT NVL(MAX(invoice_payment_id),0)
2316 FROM ap_invoice_payments_all
2317 WHERE invoice_id = NVL(p_reference2, 0)
2318 AND check_id = NVL(p_reference3,0)
2319 AND invoice_payment_id > p_reference9;
2320
2321 Cursor Pay_Pay_Void_Values is
2322 SELECT api.invoice_num, apip.creation_date, apip.created_by
2323 FROM ap_invoice_payments_all apip,
2324 ap_invoices_all api
2325 WHERE api.invoice_id = NVL(p_reference2, 0)
2326 AND api.invoice_id = apip.invoice_id
2327 AND apip.check_id = NVL(p_reference3,0)
2328 AND apip.invoice_payment_id = p_reference9;
2329
2330 Cursor Pay_Pay_Non_Void is
2331 SELECT api.invoice_num, apc.creation_date, apc.created_by
2332 FROM ap_checks_all apc,
2333 ap_invoices_all api,
2334 ap_invoice_payments_all apip
2335 WHERE to_char(apc.check_id) = p_reference3
2336 AND to_char(api.invoice_id) = p_reference2
2337 AND apc.check_id = apip.check_id
2338 AND api.invoice_id = apip.invoice_id;
2339
2340
2341 --Bug 2532729 End
2342 --Start Bug 2464961--
2343 --Modified the following Budget_Transac cursor definition--
2344
2345 Cursor Budget_Transac is
2346 SELECT h.doc_number, d.gl_date, d.creation_date, d.created_by
2347 FROM fv_be_trx_dtls d, fv_be_trx_hdrs h
2348 WHERE to_char(d.transaction_id) = p_reference1
2349 AND h.doc_id = d.doc_id;
2350
2351 --End Bug 2464961--
2352
2353
2354 -- Check this Later
2355 Cursor Pur_Req is
2356 Select Start_Date_Active
2357 ,created_by
2358 ,creation_date
2359 From PO_Requisition_Headers_All
2360 Where to_Char(Requisition_Header_Id) = P_Reference2;
2361
2362 Cursor Pur_Pur is
2363 Select Start_Date
2364 ,created_by
2365 ,creation_date
2366 From PO_Headers_all
2367 Where Segment1 = P_Reference4;
2368
2369 --Start Bug 2464961--
2370 Cursor manual_csr is
2371 SELECT default_effective_date
2372 FROM gl_je_headers
2373 WHERE je_header_id = p_je_header_id;
2374
2375 --End Bug 2464961--
2376
2377 -- Bug 2532729 Start
2378
2379 Cursor Receivables_Adjustment is
2380 SELECT apply_date, creation_date, created_by
2381 FROM ar_adjustments_all
2382 WHERE adjustment_id = p_refer2;
2383
2384 Cursor Receivables_CMA is
2385 SELECT apply_date, creation_date, created_by
2386 FROM ar_receivable_applications_all
2387 WHERE receivable_application_id = p_refer2;
2388
2389 Cursor Receivables_Memos_Inv is
2390 SELECT trx_date, creation_date, created_by
2391 FROM ra_customer_trx_all
2392 WHERE customer_trx_id = p_refer2;
2393
2394
2395 -- Bug 2532729 End
2396
2397 BEGIN
2398 -- Set the values to Null
2399 --
2400
2401 lv_document_num := NULL;
2402 lv_doc_Date := NULL;
2403 lv_doc_created_by := P_created_by ;
2404 lv_doc_creation_date := P_creation_date ;
2405
2406 -- Code for Purchasing
2407 --
2408 IF P_Je_Source_Name = 'Purchasing' THEN
2409
2410 IF P_Je_Category_Name = 'Requisitions' THEN
2411 lv_document_num := P_Reference4;
2412 OPEN Pur_Req;
2413 FETCH Pur_Req INTO lv_doc_date,lv_doc_created_by,lv_doc_creation_date;
2414 CLOSE Pur_Req;
2415 ELSIF P_Je_Category_Name = 'Purchases' THEN
2416 lv_document_num := P_Reference4;
2417 OPEN Pur_Pur;
2418 FETCH Pur_Pur INTO lv_doc_date,lv_doc_created_by,lv_doc_creation_date;
2419 CLOSE Pur_Pur;
2420 ELSIF P_Je_Category_Name = 'Receiving' THEN
2421 OPEN Pur_Rec;
2422 FETCH Pur_Rec INTO lv_doc_date,lv_document_num,lv_doc_created_by,lv_doc_creation_date;
2423 CLOSE Pur_Rec;
2424 ELSE
2425 lv_document_num := P_Name;
2426 lv_doc_date := P_Date;
2427 END IF;
2428 -- Code for Payables
2429 --
2430 ELSIF P_Je_Source_Name = 'Payables' THEN
2431
2432 IF P_Je_Category_Name = 'Purchase Invoices' THEN
2433 OPEN Pay_Pur;
2434 FETCH Pay_Pur INTO lv_document_num,lv_doc_date,lv_doc_created_by,lv_doc_creation_date;
2435 CLOSE Pay_Pur;
2436 ELSIF P_Je_Category_Name = 'Payments' THEN
2437
2438 -- Bug 2532729 Start
2439 OPEN Pay_Pay_Check;
2440 FETCH Pay_Pay_Check INTO l_void_date, l_check_date;
2441 CLOSE Pay_Pay_Check;
2442
2443 IF l_void_date IS NULL THEN
2444 OPEN Pay_Pay;
2445 FETCH Pay_Pay INTO lv_document_num,lv_doc_date,lv_doc_created_by,lv_doc_creation_date;
2446 CLOSE Pay_Pay;
2447 ELSE
2448 OPEN Pay_Pay_Void;
2449 FETCH Pay_Pay_Void INTO l_inv_payment_id;
2450 CLOSE Pay_Pay_Void;
2451
2452 IF (l_inv_payment_id <> 0) THEN
2453 OPEN Pay_Pay_Non_Void;
2454 FETCH Pay_Pay_Non_Void INTO lv_document_num, lv_doc_creation_date, lv_doc_created_by;
2455 CLOSE Pay_Pay_Non_Void;
2456
2457 lv_doc_date := l_check_date;
2458 ELSIF (l_inv_payment_id = 0) THEN
2459 OPEN Pay_Pay_Void_Values;
2460 Fetch Pay_Pay_Void_Values INTO lv_document_num, lv_doc_creation_date, lv_doc_created_by;
2461 CLOSE Pay_Pay_Void_Values;
2462
2463 lv_doc_date := l_void_date;
2464 END IF;
2465 END IF;
2466
2467 ELSIF P_Je_Category_Name = 'Treasury Confirmation' AND UPPER(p_name) NOT LIKE '%VOID%' THEN
2468
2469 OPEN Pay_Treas;
2470 FETCH Pay_Treas INTO lv_document_num, lv_doc_date, lv_doc_creation_date,lv_doc_created_by;
2471 CLOSE Pay_Treas;
2472
2473 ELSIF P_Je_Category_Name = 'Treasury Confirmation' AND UPPER(p_name) LIKE '%VOID%' THEN
2474
2475 OPEN Pay_Treas_Check;
2476 FETCH Pay_Treas_Check INTO lv_doc_date, lv_document_num;
2477 CLOSE Pay_Treas_Check;
2478
2479 OPEN Pay_Treas_Void;
2480 FETCH Pay_Treas_Void INTO lv_doc_creation_date, lv_doc_created_by;
2481 CLOSE Pay_Treas_Void;
2482
2483 -- Bug 2532729 End
2484
2485 ELSE
2486 lv_document_num := P_Name;
2487 lv_doc_date := P_Date;
2488 END IF;
2489 -- Code for Receivables
2490 --
2491 ELSIF P_Je_Source_Name = 'Receivables' THEN
2492
2493 -- Bug 2606958
2494 p_refer2 := p_reference2;
2495 lv_document_num := p_reference4;
2496
2497 --LGOEL: Added exception handler for statement below
2498 IF (p_reference2 is null) then
2499 IF (l_debug = 'Y') THEN
2500 fv_utility.debug_mesg(' Ref2 is NULL ...');
2501 END IF;
2502 lv_document_num := p_refer4;
2503 ELSE
2504 -- Bug 2532729 Start
2505
2506 IF (p_je_category_name = 'Adjustment') THEN
2507 OPEN Receivables_Adjustment;
2508 FETCH Receivables_Adjustment INTO lv_doc_date, lv_doc_creation_date, lv_doc_created_by;
2509 CLOSE Receivables_Adjustment;
2510
2511 ELSIF (p_je_category_name = 'Credit Memo Applications') THEN
2512 OPEN Receivables_CMA;
2513 FETCH Receivables_CMA INTO lv_doc_date, lv_doc_creation_date, lv_doc_created_by;
2514 CLOSE Receivables_CMA;
2515
2516 ELSIF (p_je_category_name IN ('Credit Memos', 'Debit Memos', 'Sales Invoices')) THEN
2517 OPEN Receivables_Memos_Inv;
2518 FETCH Receivables_Memos_Inv INTO lv_doc_date, lv_doc_creation_date, lv_doc_created_by;
2519 CLOSE Receivables_Memos_Inv;
2520
2521 ELSE
2522 -- Bug 2606958 Start
2523 l_cash_receipt_hist_id := SUBSTR(p_ref2, INSTR(p_ref2,'C')+1, LENGTH(p_ref2));
2524
2525 IF (p_je_category_name = 'Misc Receipts')
2526 THEN
2527 IF (l_debug = 'Y') THEN
2528 fv_utility.debug_mesg(' Processing a Misc Receipt');
2529 END IF;
2530 p_refer2 := p_ref2;
2531 l_cash_receipt_hist_id := p_reference5;
2532 ELSE
2533 IF (l_debug = 'Y') THEN
2534 fv_utility.debug_mesg(' Processing a Trade Receipt or Other');
2535 END IF;
2536 p_refer2 := p_reference2;
2537 l_cash_receipt_hist_id := SUBSTR(p_ref2, INSTR(p_ref2,'C')+1, LENGTH(p_ref2));
2538 END IF;
2539
2540 IF (l_debug = 'Y') THEN
2541 fv_utility.debug_mesg(' Cash receipt id = '||p_refer2);
2542 fv_utility.debug_mesg(' Cash receipt hist id = ' ||l_cash_receipt_hist_id);
2543 END IF;
2544
2545 OPEN Receivables_Hist;
2546 FETCH Receivables_Hist INTO l_rev_exists;
2547 CLOSE Receivables_Hist;
2548
2549 IF (l_rev_exists = 'N')
2550 THEN
2551 l_doc_creation_date_d := NULL;
2552 l_doc_created_by_d := NULL;
2553
2554 IF (p_je_category_name = 'Misc Receipts')
2555 THEN
2556 l_rev_exists := 'M';
2557 ELSE
2558 l_rev_exists := 'C';
2559 END IF;
2560
2561 ELSE
2562 l_rev_exists := 'N';
2563
2564 OPEN Receivables_History;
2565 FETCH Receivables_History into l_rev_exists, l_doc_creation_date_d, l_doc_created_by_d;
2566 CLOSE Receivables_History;
2567
2568 IF (l_rev_exists = 'Y')
2569 THEN
2570 IF (l_debug = 'Y') THEN
2571 fv_utility.debug_mesg(' Cash Receipt Hist Id exits in Ar_Cash_Receipt_History_All ... REVERSAL');
2572 END IF;
2573 END IF;
2574 END IF;
2575
2576 IF (p_je_category_name <> 'Misc Receipts') AND (l_rev_exists = 'C')
2577 THEN
2578 -- Find out if Reference_2 contains Receivable_Application_Id
2579 OPEN Receivables_Applications;
2580 FETCH Receivables_Applications into l_temp_cr_hist_id;
2581 CLOSE Receivables_Applications;
2582
2583 IF (l_temp_cr_hist_id IS NOT NULL)
2584 THEN
2585 l_cash_receipt_hist_id := l_temp_cr_hist_id;
2586
2587 IF (l_debug = 'Y') THEN
2588 fv_utility.debug_mesg(' Cash Receipt Hist Id exits in Ar_Receivable_Applications_All: ' ||l_cash_receipt_hist_id);
2589 END IF;
2590
2591 -- Use cash_receipt_history_id obtained above to find if a row exits in Ar_Cash_Receipts_All
2592 OPEN Receivables_Exists;
2593 FETCH Receivables_Exists INTO l_rev_exists;
2594 CLOSE Receivables_Exists;
2595
2596 IF (l_rev_exists = 'Y')
2597 THEN
2598 IF (l_debug = 'Y') THEN
2599 fv_utility.debug_mesg(' Cash Receipt Hist Id exits in Ar_Cash_Receipt_History_All: ' ||l_cash_receipt_hist_id);
2600 END IF;
2601
2602 l_rev_exists := 'N';
2603
2604 -- Select the document info from Ar_Cash_Receipt_History_All table
2605 OPEN Receivables_History;
2606 FETCH Receivables_History into l_rev_exists, l_doc_creation_date_d, l_doc_created_by_d;
2607 CLOSE Receivables_History;
2608
2609 IF (l_rev_exists = 'Y')
2610 THEN
2611 IF (l_debug = 'Y') THEN
2612 fv_utility.debug_mesg(' Reversal Cash Receipt Hist Id exists ... REVERSAL');
2613 END IF;
2614 END IF;
2615
2616 END IF;
2617 END IF; -- End If for l_temp_cr_hist_id
2618
2619 ELSIF (p_je_category_name = 'Misc Receipts') AND (l_rev_exists = 'M')
2620 THEN
2621 -- Find out if Reference_2 contains Misc_Cash_Distribution_Id
2622 OPEN Receivables_Distrib;
2623 FETCH Receivables_Distrib into l_rev_exists;
2624 CLOSE Receivables_Distrib;
2625
2626 IF (l_rev_exists = 'Y')
2627 THEN
2628 IF (l_debug = 'Y') THEN
2629 fv_utility.debug_mesg(' Cash Receipt Hist Id exits in Ar_Misc_Cash_Distributions_All: ' ||l_cash_receipt_hist_id);
2630 END IF;
2631
2632 l_rev_exists := 'N';
2633
2634 -- Select the document info from Ar_Misc_Cash_Distributions_All table
2635 OPEN Receivables_Misc;
2636 FETCH Receivables_Misc into l_rev_exists, l_doc_creation_date_d, l_doc_created_by_d;
2637 CLOSE Receivables_Misc;
2638
2639 IF (l_rev_exists = 'Y')
2640 THEN
2641 IF (l_debug = 'Y') THEN
2642 fv_utility.debug_mesg(' Misc Cash Disc Id has Reverse value in created from ... REVERSAL');
2643 END IF;
2644 END IF;
2645
2646 END IF;
2647 END IF; -- End If for l_rev_exists = C/M
2648
2649
2650
2651 OPEN Receivables;
2652 FETCH Receivables INTO lv_doc_date, l_doc_created_by_d, l_doc_creation_date_d;
2653 CLOSE Receivables;
2654
2655 lv_doc_creation_date := l_doc_creation_date_d;
2656 lv_doc_created_by := l_doc_created_by_d;
2657
2658 END IF; -- End if for p_je_category_name
2659 END IF; -- End if for p_reference2
2660
2661 -- Bug 2606958 End
2662 -- Bug 2532729 End
2663
2664 ELSIF P_Je_Source_Name = 'Budgetary Transaction' THEN
2665 OPEN Budget_Transac;
2666 FETCH Budget_Transac INTO lv_document_num, lv_doc_date,
2667 lv_doc_creation_date,lv_doc_created_by ;
2668 CLOSE Budget_Transac;
2669
2670 --Start Bug No. 2464961--
2671 ELSIF p_je_source_name = 'Manual' THEN
2672
2673 OPEN Manual_csr ;
2674 FETCH Manual_csr INTO lv_doc_date;
2675 CLOSE Manual_csr;
2676
2677 IF (p_reference4 IS NOT NULL)
2678 THEN
2679 lv_document_num := p_reference4;
2680 ELSE
2681 lv_document_num := p_name;
2682 END IF;
2683
2684 -- Code for Misc
2685 --
2686 ELSE
2687 IF (p_reference4 IS NOT NULL)
2688 THEN
2689 lv_document_num := p_reference4;
2690 ELSE
2691 lv_document_num := p_name;
2692 END IF;
2693
2694 lv_doc_date := p_date;
2695 lv_doc_creation_date := p_creation_date;
2696 lv_doc_created_by := p_created_by;
2697
2698 --End Bug No. 2464961--
2699 END IF ;
2700
2701
2702 -- Check for values
2703 -- If not put default
2704
2705 -- Test
2706 -- fv_utility.debug_mesg('P_Doc_Num 11:'||lv_document_num);
2707 -- fv_utility.debug_mesg('P_Doc_Date11:'||lv_Doc_Date);
2708 --
2709 IF lv_document_num IS NULL THEN
2710 lv_document_num := P_Name;
2711 END IF;
2712
2713 IF lv_doc_date IS NULL THEN
2714 lv_doc_Date := P_Date;
2715 END IF;
2716
2717 IF lv_doc_created_by IS NULL THEN
2718 lv_doc_created_by := P_created_by;
2719 END IF;
2720
2721 IF lv_doc_creation_date IS NULL THEN
2722 lv_doc_creation_date := P_creation_date;
2723 END IF;
2724
2725
2726 -- Set the out varibales
2727 --
2728
2729 P_Doc_Num := lv_document_num;
2730 P_Doc_Date:= lv_Doc_Date;
2731 P_doc_created_by := lv_doc_created_by;
2732 P_doc_creation_date := lv_doc_creation_date ;
2733
2734 -- fv_utility.debug_mesg('Je_Source_Name:'||P_Je_Source_Name);
2735 -- fv_utility.debug_mesg('Je_Category_Name:'||P_Je_Category_Name);
2736 -- fv_utility.debug_mesg('P_Reference1:'||P_Reference1);
2737 -- fv_utility.debug_mesg('P_Reference2:'||P_Reference2);
2738 -- fv_utility.debug_mesg('P_Reference3:'||P_Reference3);
2739 -- fv_utility.debug_mesg('P_Reference4:'||P_Reference4);
2740 -- fv_utility.debug_mesg('P_Reference5:'||P_Reference5);+
2741 -- fv_utility.debug_mesg('P_Name:'||P_Name);
2742 -- fv_utility.debug_mesg('P_Date:'||P_Date);
2743 -- fv_utility.debug_mesg('P_Doc_Num:'||P_Doc_Num);
2744 -- fv_utility.debug_mesg('P_Doc_Date:'||P_Doc_Date);
2745 -- fv_utility.debug_mesg('********************************');
2746
2747
2748
2749 EXCEPTION
2750 When Others Then
2751 vp_retcode := sqlcode ;
2752 vp_errbuf := sqlerrm || ' [GET_DOC_INFO] ' ;
2753 fnd_file.put_line(fnd_file.log, vp_errbuf) ;
2754 -- fv_utility.debug_mesg('WHEN OTHERS'||vp_errbuf);
2755 return;
2756 END GET_DOC_INFO;
2757
2758 -- -------------------------------------------------------------------
2759 -- End Of the Package Body
2760 -- -------------------------------------------------------------------
2761 END FV_FACTS_TBAL_TRANSACTIONS ;
2762