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