[Home] [Help]
PACKAGE BODY: APPS.FV_GTAS_GL_PKG
Source
1 PACKAGE BODY FV_GTAS_GL_PKG AS
2 /* $Header: FVGTJCRB.pls 120.0.12020000.23 2013/03/08 19:50:44 snama noship $ */
3 g_module_name VARCHAR2(100);
4 gbl_period_name gl_period_statuses.period_name%TYPE;
5 gbl_period_year gl_period_statuses.period_year%TYPE;
6 gbl_period_num_low gl_period_statuses.period_num%TYPE;
7 gbl_period_num_high gl_period_statuses.period_num%TYPE;
8 gbl_sob_id gl_ledgers_public_v.ledger_id%TYPE;
9 gbl_coa_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
10 gbl_bal_segment VARCHAR2(10);
11 gbl_acc_segment VARCHAR2(10);
12 gbl_err_code NUMBER := 0;
13 gbl_err_buff VARCHAR2(250);
14 gbl_jrnl_attribute VARCHAR2(15);
15 gbl_cust_attribute fv_system_parameters.gtas_customer_attribute%TYPE;
16 gbl_cust_col_exists VARCHAR2(1) := 'N';
17 gbl_acc_value_set_id NUMBER;
18 gbl_other_journal VARCHAR2(1);
19
20 TYPE party_info_rec
21 IS
22 RECORD
23 (
24 party_id fv_gtas_activity_balances.trading_partner_id%TYPE,
25 party_type fv_gtas_activity_balances.trading_partner_type%TYPE,
26 agency_id fv_gtas_activity_balances.trading_partner_agency_id%TYPE,
27 main_account fv_gtas_activity_balances.trading_partner_main_account%TYPE,
28 party_name fv_gtas_activity_balances.trading_partner_name%TYPE,
29 reported_status VARCHAR2(1),
30 record_category fv_gtas_activity_balances.record_category%TYPE,
31 fed_nonfed_code fv_gtas_activity_balances.fed_non_fed%TYPE,
32 party_line_amount NUMBER,
33 doc_num xla_transaction_entities.transaction_number%TYPE);
34 TYPE party_info_table
35 IS TABLE OF party_info_rec INDEX BY BINARY_INTEGER;
36
37 PROCEDURE get_system_attributes;
38 PROCEDURE get_process_dates;
39 PROCEDURE get_segment_names;
40 PROCEDURE process_gl_lines;
41 PROCEDURE log(module IN VARCHAR2,
42 message_line IN VARCHAR2);
43
44 PROCEDURE get_authority_type_code
45 (p_sob_id IN VARCHAR2, p_fund_value IN VARCHAR2,
46 p_account_number IN VARCHAR2,
47 p_sgl_account_number IN VARCHAR2,
48 p_amount IN NUMBER,
49 p_je_source IN VARCHAR2,
50 p_je_category IN VARCHAR2,
51 p_je_batch_id IN NUMBER,
52 p_je_header_id IN NUMBER,
53 p_je_line_num IN NUMBER,
54 p_je_from_sla_flag IN VARCHAR2,
55 p_reference1 IN VARCHAR2,
56 p_ccid IN NUMBER,
57 p_party_name IN VARCHAR2, p_agency_id IN VARCHAR2,
58 p_main_account IN VARCHAR2,
59 p_doc_num IN VARCHAR2, p_authority_type_code OUT NOCOPY VARCHAR2);
60 PROCEDURE delete_exception_recs;
61 PROCEDURE insert_exception_rec
62 (p_set_of_books_id IN NUMBER, p_fund_value IN VARCHAR2,
63 p_account_number IN VARCHAR2,
64 p_sgl_account_number IN VARCHAR2,
65 p_je_source IN VARCHAR2,
66 p_je_category IN VARCHAR2,
67 p_je_header_id IN NUMBER, p_je_line_num IN NUMBER,
68 p_amount IN NUMBER, p_ccid IN NUMBER, p_exception_category IN VARCHAR2,
69 p_doc_num in VARCHAR2, p_party_name IN VARCHAR2,
70 p_agency_id IN VARCHAR2, p_main_acct IN VARCHAR2);
71 PROCEDURE get_tp_attributes(p_je_from_sla_flag IN VARCHAR2, p_je_batch_id IN NUMBER,
72 p_je_header_id IN NUMBER, p_je_line_num IN NUMBER,
73 p_je_source IN VARCHAR2,
74 p_je_category IN VARCHAR2,p_fed_nonfed_flag IN VARCHAR2,
75 p_fund_value IN VARCHAR2,
76 p_reference1 IN VARCHAR2,
77 p_reference2 IN VARCHAR2,
78 p_reference3 IN VARCHAR2,
79 p_reference5 IN VARCHAR2,
80 p_reference7 IN VARCHAR2,
81 p_je_attrib_val IN VARCHAR2,
82 p_party_info_tab OUT NOCOPY party_info_table);
83 PROCEDURE get_party_info_r12(p_je_source IN VARCHAR2,
84 p_je_category IN VARCHAR2,
85 p_je_batch_id IN NUMBER,
86 p_je_header_id IN NUMBER,
87 p_je_line_num IN NUMBER,
88 p_fund_value IN VARCHAR2,
89 p_application_id IN NUMBER,
90 p_party_info_tab OUT NOCOPY party_info_table);
91 PROCEDURE get_party_info_11i(p_je_source IN VARCHAR2, p_je_category IN VARCHAR2,
92 p_reference1 IN VARCHAR2,
93 p_reference2 IN VARCHAR2,
94 p_reference3 IN VARCHAR2,
95 p_reference5 IN VARCHAR2,
96 p_reference7 IN VARCHAR2,
97 p_party_info_tab OUT NOCOPY party_info_table);
98 PROCEDURE get_budget_impact_ind(p_je_batch_id IN NUMBER,
99 p_je_header_id IN NUMBER,
100 p_je_line_num IN NUMBER,
101 p_account_number IN VARCHAR2,
102 p_sgl_account_number IN VARCHAR2,
103 p_fund_value IN VARCHAR2,
104 p_je_source IN VARCHAR2,
105 p_je_category IN VARCHAR2,
106 p_ccid IN NUMBER, p_amount NUMBER,
107 p_doc_num IN VARCHAR2,
108 p_party_name IN VARCHAR2,
109 p_agency_id IN VARCHAR2,
110 p_main_account IN VARCHAR2,
111 p_budget_impact_indicator OUT NOCOPY VARCHAR2);
112 PROCEDURE get_fed_nonfed_code(p_fund_value IN VARCHAR2,
113 p_account_number IN VARCHAR2,
114 p_sgl_account_number IN VARCHAR2,
115 p_party_fed_nonfed_code IN VARCHAR2,
116 p_je_source IN VARCHAR2,
117 p_je_category IN VARCHAR2,
118 p_ccid IN NUMBER, p_amount NUMBER,
119 p_doc_num IN VARCHAR2,
120 p_party_name IN VARCHAR2,
121 p_agency_id IN VARCHAR2,
122 p_main_account IN VARCHAR2,
123 p_fed_nonfed_code OUT NOCOPY VARCHAR2);
124 PROCEDURE check_fed_nonfed_code(p_account_number IN VARCHAR2,
125 p_fed_nonfed_type IN VARCHAR2,
126 p_je_source IN VARCHAR2,
127 p_code_exists OUT NOCOPY VARCHAR2);
128 PROCEDURE get_account_type(p_account_number IN VARCHAR2,
129 p_account_type OUT NOCOPY VARCHAR2);
130 PROCEDURE update_gtas_run;
131 PROCEDURE submit_exception_report;
132 PROCEDURE delete_prior_gtas_activity;
133 FUNCTION get_exception_count RETURN NUMBER;
134 PROCEDURE get_ussgl_acct(p_gtas_acct_num IN VARCHAR2,
135 p_sgl_acct_num IN VARCHAR2,
136 p_account_num OUT NOCOPY VARCHAR2);
137 -------------------------------------------------------------------------------
138 PROCEDURE MAIN(p_err_buff OUT NOCOPY VARCHAR2,
139 p_err_code OUT NOCOPY NUMBER,
140 p_sob_id IN NUMBER,
141 p_to_period_name IN VARCHAR2,
142 p_re_create_activity_bal IN VARCHAR2 DEFAULT 'N')
143 IS
144 l_module_name VARCHAR2(200):= g_module_name||'MAIN';
145 l_no_fed_account NUMBER(15);
146
147 v_statement VARCHAR2(1000);
148 BEGIN
149
150 log(l_module_name,'Begin: '||l_module_name);
151 log(l_module_name, 'Parameters:');
152 log(l_module_name, 'p_sob_id: '||p_sob_id);
153 log(l_module_name, 'p_to_period_name: '||p_to_period_name);
154 log(l_module_name, 'p_re_create_activity_bal: '||p_re_create_activity_bal);
155
156 gbl_sob_id := p_sob_id;
157 gbl_period_name := p_to_period_name;
158 gbl_err_code := 0;
159
160 get_system_attributes;
161
162 IF gbl_err_code = 0 THEN
163 get_process_dates;
164 END IF;
165 log(l_module_name,'after get process dates');
166
167 IF gbl_err_code = 0 THEN
168 SELECT COUNT(*)
169 INTO l_no_fed_account
170 FROM fv_gtas_fed_accounts
171 WHERE set_of_books_id = p_sob_id
172 AND fiscal_year = gbl_period_year;
173
174 log(l_module_name,'gbl_period_year: '||gbl_period_year);
175
176 IF l_no_fed_account = 0 THEN
177 p_err_buff := 'Please run the GTAS Federal Accounts Creation process '||
178 ' for the current ledger and year '||gbl_period_year;
179 p_err_code := -1;
180 RETURN;
181 END IF;
182 END IF;
183
184 --Delete all prior exception recs
185 delete_exception_recs;
186
187 IF p_re_create_activity_bal = 'Y' THEN
188 delete_prior_gtas_activity;
189 END IF;
190
191 --Get the balancing and account segment names
192 IF gbl_err_code = 0 THEN
193 get_segment_names;
194 END IF;
195
196 IF gbl_err_code = 0 THEN
197 process_gl_lines;
198 END IF;
199
200 IF gbl_err_code = 0 THEN
201 update_gtas_run;
202 END IF;
203
204 --If any exceptions exist, then rollback all transactions
205 --and submit the exception report
206 IF gbl_err_code = 0 THEN
207 IF get_exception_count > 0 THEN
208 gbl_err_buff := 'Exceptions have occurred! '||
209 'Please fix all exceptions and re run the process.';
210 gbl_err_code := 2;
211 fv_utility.log_mesg(fnd_log.level_error, l_module_name,gbl_err_buff);
212 ROLLBACK;
213 submit_exception_report;
214 END IF;
215 END IF;
216
217 IF gbl_err_code <> 0 THEN
218 p_err_code := gbl_err_code;
219 p_err_buff := gbl_err_buff;
220 ROLLBACK;
221 RETURN;
222 END IF;
223
224 COMMIT;
225
226 log(l_module_name,'GTAS Journal Process completed successfully.');
227 log(l_module_name, 'End: '||l_module_name);
228
229 --End temporary code
230 EXCEPTION WHEN OTHERS THEN
231 p_err_code := SQLCODE;
232 p_err_buff := 'When others exception in Main - '||SQLERRM;
233 ROLLBACK;
234 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
235 END MAIN;
236 --------------------------------------------------------------------------------
237 -- Get the period num for the parameter period and also the first period num
238 -- for the year.
239 --------------------------------------------------------------------------------
240 PROCEDURE GET_PROCESS_DATES
241 IS
242 l_module_name VARCHAR2(200);
243 l_temp_mesg VARCHAR2(250);
244 BEGIN
245 l_module_name := g_module_name || 'GET_PROCESS_DATES';
246 FV_UTILITY.LOG_MESG('Begin: '||l_module_name);
247
248 -- Get the period year for the period parameter passed.
249 l_temp_mesg := 'getting period year.';
250 SELECT period_year
251 INTO gbl_period_year
252 FROM gl_period_statuses p
253 WHERE p.application_id = 101
254 AND p.ledger_id = gbl_sob_id
255 AND p.period_name = gbl_period_name;
256 -- Get the first period of the year
257 l_temp_mesg := 'getting first period number of the year.';
258 SELECT MIN(period_num)
259 INTO gbl_period_num_low
260 FROM gl_period_statuses
261 WHERE period_year = gbl_period_year
262 AND application_id = 101
263 AND closing_status <> 'F'
264 AND closing_status <> 'N'
265 AND adjustment_period_flag = 'N'
266 AND ledger_id = gbl_sob_id;
267 -- Get the period num for the parameter to period
268 l_temp_mesg := 'getting period number of the parameter to period.';
269 SELECT period_num
270 INTO gbl_period_num_high
271 FROM gl_period_statuses p
272 WHERE period_name = gbl_period_name
273 AND p.application_id = 101
274 AND p.ledger_id = gbl_sob_id
275 AND p.period_year = gbl_period_year;
276
277 IF (gbl_period_num_low = 0 OR gbl_period_num_high = 0) THEN
278 gbl_err_code := 2 ;
279 gbl_err_buff := l_module_name||' Period number '|| 'found zero for the passed fiscal year.' ;
280 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
281 RETURN;
282 END IF;
283 log(l_module_name, 'Period Year: '||gbl_period_year);
284 log(l_module_name, 'Period Number Low: '||gbl_period_num_low);
285 log(l_module_name, 'Period Number High: '||gbl_period_num_high);
286
287 log(l_module_name, 'End');
288 EXCEPTION
289 WHEN NO_DATA_FOUND THEN
290 gbl_err_code := 2;
291 gbl_err_buff := l_module_name||' - No data found when '||l_temp_mesg;
292 FV_UTILITY.LOG_MESG(gbl_err_buff);
293 WHEN OTHERS THEN
294 gbl_err_code := 2;
295 gbl_err_buff := l_module_name||' - When others error when '||l_temp_mesg||':'||SQLERRM;
296 FV_UTILITY.LOG_MESG(gbl_err_buff);
297 END get_process_dates;
298 --------------------------------------------------------------------------------
299 -- Get balancing and accounting segments
300 --------------------------------------------------------------------------------
301 PROCEDURE GET_SEGMENT_NAMES
302 IS
303 l_module_name VARCHAR2(200);
304 l_app_id NUMBER := 101;
305 l_flex_code VARCHAR2(10) := 'GL#';
306 l_segment_found BOOLEAN := FALSE;
307 invalid_bal_segment EXCEPTION;
308 invalid_acc_segment EXCEPTION;
309
310 BEGIN
311 l_module_name := g_module_name || 'GET_SEGMENT_NAMES';
312 log(l_module_name, 'Begin: '||l_module_name);
313
314 SELECT chart_of_accounts_id
315 INTO gbl_coa_id
316 FROM gl_ledgers_public_v
317 WHERE ledger_id = gbl_sob_id;
318
319
320 log(l_module_name, 'COA ID: '||gbl_coa_id);
321 -- Get Balancing Segment Name
322 l_segment_found :=
323 FND_FLEX_APIS.get_segment_column (l_app_id, l_flex_code, gbl_coa_id,
324 'GL_BALANCING', gbl_bal_segment) ;
325 IF NOT l_segment_found THEN
326 RAISE invalid_bal_segment;
327 END IF;
328 log(l_module_name, 'Balancing Segment: '||gbl_bal_segment);
329
330 -- Get Accounting Segment Name
331 l_segment_found :=
332 FND_FLEX_APIS.get_segment_column (l_app_id, l_flex_code, gbl_coa_id,
333 'GL_ACCOUNT', gbl_acc_segment);
334
335 IF NOT l_segment_found THEN
336 RAISE invalid_acc_segment;
337 END IF;
338 log(l_module_name, 'Accounting Segment: '||gbl_acc_segment);
339
340 IF (gbl_bal_segment IS NULL OR gbl_acc_segment IS NULL) THEN
341 RAISE NO_DATA_FOUND;
342 END IF;
343
344 --Get value set id of accounting segment
345 SELECT flex_value_set_id
346 INTO gbl_acc_value_set_id
347 FROM fnd_id_flex_segments
348 WHERE application_column_name = gbl_acc_segment
349 AND id_flex_code = 'GL#'
350 AND id_flex_num = gbl_coa_id;
351
352 log(l_module_name, 'Accounting Segment value set id: '||gbl_acc_value_set_id);
353
354 log(l_module_name, 'End: '||l_module_name);
355 EXCEPTION
356 WHEN NO_DATA_FOUND THEN
357 gbl_err_code := -1 ;
358 gbl_err_buff := 'Balancing or Accounting segment not found.';
359 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
360 WHEN invalid_bal_segment THEN
361 gbl_err_code := -1 ;
362 gbl_err_buff := 'Error while fetching balancing segment.';
363 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
364 WHEN invalid_acc_segment THEN
365 gbl_err_code := -1 ;
366 gbl_err_buff := 'Error while fetching accounting segment.';
367 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
368 WHEN OTHERS THEN
369 gbl_err_code := -1 ;
370 gbl_err_buff := 'When others error while getting
371 Balancing or Accounting segment - '||SQLERRM;
372 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
373 END get_segment_names;
374 --------------------------------------------------------------------------------
375 -- Select all journal lines from gl_je_lines from the beginning of the year upto
376 -- the period being run, for all accounts existing in fv_gtas_fed_accounts and
377 -- all journal lines not existing in fv_gtas_line_balances.
378 -- Retreives party info like party type, eliminations dept, etc and insert into
379 -- fv_gtas_line_balances.
380 --------------------------------------------------------------------------------
381 PROCEDURE PROCESS_GL_LINES
382 IS
383 l_module_name VARCHAR2(200);
384 l_select_stmt VARCHAR2(20000);
385 l_last_fetch BOOLEAN;
386 l_exception_category fv_gtas_exceptions.exception_category%TYPE;
387 l_authority_type_code fv_gtas_attributes.authority_type1%TYPE;
388 l_budget_impact_indicator VARCHAR2(1);
389 l_fed_nonfed_code fv_gtas_attributes.fed_non_fed1%TYPE;
390 l_dummy_num NUMBER;
391 --l_account_number fv_gtas_attributes.gtas_acct_number%TYPE;
392 --l_project_accounting VARCHAR2(50):='Project Accounting';
393 TYPE t_ref_cur IS REF CURSOR ;
394 l_gl_lines_cur t_ref_cur ;
395 TYPE ccid_t IS TABLE OF gl_je_lines.code_combination_id%TYPE;
396 TYPE sob_id_t IS TABLE OF gl_je_lines.ledger_id%TYPE;
397 TYPE account_number_t IS TABLE OF fv_gtas_activity_balances.account_number%TYPE;
398 TYPE amount_t IS TABLE OF fv_gtas_activity_balances.amount%TYPE;
399 TYPE d_c_indicator_t IS TABLE OF fv_gtas_activity_balances.d_c_indicator%TYPE;
400 TYPE je_header_id_t IS TABLE OF gl_je_headers.je_header_id%TYPE;
401 TYPE je_line_num_t IS TABLE OF gl_je_lines.je_line_num%TYPE;
402 TYPE je_category_t IS TABLE OF gl_je_headers.je_category%TYPE;
403 TYPE je_source_t IS TABLE OF gl_je_headers.je_source%TYPE;
404 TYPE reference_t IS TABLE OF gl_je_lines.reference_1%TYPE;
405 TYPE je_attribute_val_t IS TABLE OF gl_je_lines.attribute1%TYPE;
406 TYPE fund_value_t IS TABLE OF fv_fund_parameters.fund_value%TYPE;
407 TYPE gl_period_t IS TABLE OF gl_je_lines.period_name%TYPE;
408 TYPE party_id_t IS TABLE OF fv_gtas_activity_balances.trading_partner_id%TYPE;
409 TYPE party_type_t IS TABLE OF fv_gtas_activity_balances.trading_partner_type%TYPE;
410 TYPE party_name_t IS TABLE OF fv_gtas_activity_balances.trading_partner_name%TYPE;
411 TYPE agency_id_t IS TABLE OF VARCHAR2(3);
412 TYPE fed_nonfed_code_t IS TABLE OF fv_gtas_attributes.fed_non_fed1%TYPE;
413 TYPE varchar_1_t IS TABLE OF VARCHAR2(1);
414 TYPE record_category_t IS TABLE OF fv_gtas_activity_balances.record_category%TYPE;
415 TYPE period_num_t IS TABLE OF gl_period_statuses.period_num%TYPE;
416 TYPE je_from_sla_flag_t IS TABLE OF VARCHAR2(1);
417 TYPE je_batch_id_t IS TABLE OF gl_je_headers.je_batch_id%TYPE;
418 TYPE authority_type_flag_t IS TABLE OF VARCHAR2(1);
419 TYPE fed_nonfed_flag_list_t IS TABLE OF VARCHAR2(1);
420 TYPE budget_impact_ind_flag_list_t IS TABLE OF VARCHAR2(1);
421 TYPE trading_partner_flag_list_t IS TABLE OF VARCHAR2(1);
422 TYPE sgl_account_number_list_t IS TABLE OF fv_gtas_fed_accounts.sgl_account_number%TYPE;
423 --TYPE fed_nonfed_code_list_t IS TABLE OF fv_gtas_attributes.fed_non_fed1%TYPE;
424 TYPE authority_type_code_t IS TABLE OF fv_gtas_attributes.authority_type1%TYPE;
425 TYPE budget_impact_ind_t IS TABLE OF fv_gtas_attributes.bud_impact_ind1%TYPE;
426 TYPE doc_num_t IS TABLE OF fv_gtas_exceptions.doc_num%TYPE;
427 TYPE main_account_t IS TABLE OF
428 fv_gtas_activity_balances.trading_partner_main_account%TYPE;
429
430
431 ccid_list ccid_t;
432 sob_id_list sob_id_t;
433 account_number_list account_number_t;
434 amount_list amount_t;
435 d_c_indicator_list d_c_indicator_t;
436 je_header_id_list je_header_id_t;
437 je_line_num_list je_line_num_t;
438 je_category_list je_category_t;
439 je_source_list je_source_t;
440 reference_1_list reference_t;
441 reference_2_list reference_t;
442 reference_3_list reference_t;
443 reference_5_list reference_t;
444 reference_7_list reference_t;
445 je_attribute_value_list je_attribute_val_t;
446 fund_value_list fund_value_t;
447 gl_period_list gl_period_t;
448 party_id_list party_id_t;
449 party_type_list party_type_t;
450 party_name_list party_name_t;
451 agency_id_list agency_id_t;
452 --fed_nonfed_code_list fed_nonfed_code_t;
453 reported_status_list varchar_1_t;
454 record_category_list record_category_t;
455 feeder_flag_list varchar_1_t;
456 period_num_list period_num_t;
457 je_from_sla_flag_list je_from_sla_flag_t;
458 je_batch_id_list je_batch_id_t;
459 authority_type_flag_list authority_type_flag_t;
460 fed_nonfed_flag_list fed_nonfed_flag_list_t;
461 budget_impact_ind_flag_list budget_impact_ind_flag_list_t;
462 trading_partner_flag_list trading_partner_flag_list_t;
463 sgl_account_number_list sgl_account_number_list_t;
464 --doc_num_list doc_num_t;
465 main_account_list main_account_t;
466
467 ccid_list_2 ccid_t;
468 period_num_list_2 period_num_t;
469 account_number_list_2 account_number_t;
470 fund_value_list_2 fund_value_t;
471 amount_list_2 amount_t;
472 d_c_indicator_list_2 d_c_indicator_t;
473 fed_nonfed_code_list_2 fed_nonfed_code_t;
474 agency_id_list_2 agency_id_t;
475 record_category_list_2 record_category_t;
476 party_name_list_2 party_name_t;
477 je_header_id_list_2 je_header_id_t;
478 je_line_num_list_2 je_line_num_t;
479 je_category_list_2 je_category_t;
480 je_source_list_2 je_source_t;
481 party_id_list_2 party_id_t;
482 party_type_list_2 party_type_t;
483 je_attribute_value_list_2 je_attribute_val_t;
484 --feeder_flag_list_2 varchar_1_t;
485 gl_period_list_2 gl_period_t;
486 authority_type_code_list_2 authority_type_code_t;
487 budget_impact_ind_list_2 budget_impact_ind_t;
488 doc_num_list_2 doc_num_t;
489 l_party_info_tab party_info_table;
490 main_account_list_2 main_account_t;
491
492 k BINARY_INTEGER := 1;
493
494 BEGIN
495 l_module_name := g_module_name || 'PROCESS_GL_LINES';
496 log(l_module_name,'Begin');
497
498 --Insert into temp table all the je headers ids which need to be processed
499 --This is to help improve performance
500 INSERT INTO fv_gtas_header_id_gt
501 (je_header_id,set_of_books_id)
502 SELECT gjh.je_header_id, gjh.ledger_id
503 FROM
504 (SELECT period_num,period_name
505 FROM gl_period_statuses
506 WHERE application_id = 101
507 AND ledger_id = gbl_sob_id
508 AND period_num BETWEEN gbl_period_num_low AND gbl_period_num_high
509 AND period_year = gbl_period_year
510 ) gps,
511 gl_je_headers gjh
512 WHERE gjh.period_name = gps.period_name
513 AND gjh.ledger_id = gbl_sob_id
514 AND gjh.status = 'P'
515 AND gjh.actual_flag = 'A'
516 AND NOT EXISTS
517 (SELECT 'x'
518 FROM fv_gtas_processed_je_hdrs e
519 WHERE e.set_of_books_id = gjh.ledger_id
520 AND e.je_header_id = gjh.je_header_id
521 ) ;
522
523 log(l_module_name,'Inserted ' || SQL%ROWCOUNT);
524
525 --This commit is to gather table stats
526 --COMMIT;
527 --Fnd_Stats.GATHER_TABLE_STATS(ownname=>'FV',tabname=>'FV_GTAS_HEADER_ID_GT');
528
529 SELECT factsI_journal_attribute
530 INTO gbl_jrnl_attribute
531 FROM fv_system_parameters;
532
533 log(l_module_name, 'gbl_jrnl_attribute: '||gbl_jrnl_attribute);
534
535 l_select_stmt :=
536 ' gjl.code_combination_id,
537 gjh.ledger_id,
538 glcc.'||gbl_acc_segment|| ',
539 (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0) ) amount,
540 DECODE( SIGN (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0)) , -1, ''C'', ''D'') d_c_indicator,
541 gjh.je_header_id,
542 gjl.je_line_num,
543 gjh.je_category,
544 gjh.je_source,
545 gjl.reference_1,
546 gjl.reference_2,
547 gjl.reference_3,
548 gjl.reference_5,
549 gjl.reference_7,
550 gjl.'||gbl_jrnl_attribute|| ',
551 glcc.'||gbl_bal_segment|| ',
552 gjl.period_name,
553 NULL party_id,
554 NULL party_type,
555 NULL party_name,
556 NULL agency_id,
557 NULL main_account,
558 NVL(fgf.fed_non_fed_flag,''N''),
559 NULL reported_status,
560 NULL record_category,
561 NULL feeder_flag,
562 gps.period_num,
563 --NULL g_ng_indicator,
564 NVL(gjh.je_from_sla_flag, ''N''),
565 gjh.je_batch_id,
566 NVL(fgf.authority_type_flag,''N''),
567 NVL(fgf.budget_impact_ind_flag,''N''),
568 NVL(fgf.trading_partner_flag,''N''),
569 fgf.sgl_account_number
570 ';
571
572 l_select_stmt :=
573 'SELECT /*+ ORDERED INDEX(gjh GL_JE_HEADERS_U1, ftt
574 FV_GTAS_HEADER_ID_GT_U1, gjl GL_JE_LINES_U1,
575 glcc GL_CODE_COMBINATIONS_U1,
576 fgf FV_GTAS_FED_ACCOUNTS_U1) */ ' ||
577 l_select_stmt ||
578 ' FROM fv_gtas_header_id_gt ftt,
579 gl_je_headers gjh,
580 gl_je_lines gjl,
581 (SELECT period_num, period_name
582 FROM gl_period_statuses ps
583 WHERE application_id = 101
584 AND ledger_id = :gbl_sob_id
585 AND period_num BETWEEN :gbl_period_num_low AND :gbl_period_num_high
586 AND period_year = :gbl_period_year) gps,
587 gl_code_combinations glcc,
588 fv_gtas_fed_accounts fgf
589 , fv_fund_parameters ffp,
590 fv_treasury_symbols fts
591 WHERE gjh.period_name = gps.period_name
592 AND gjl.ledger_id = :gbl_sob_id
593 AND gjl.je_header_id = ftt.je_header_id
594 AND gjh.currency_code <> ''STAT''
595 AND gjh.status = ''P''
596 AND gjh.actual_flag = ''A''
597 AND gjh.je_header_id = ftt.je_header_id
598 AND ftt.set_of_books_id = :gbl_sob_id
599 AND gjh.ledger_id = :gbl_sob_id
600 AND gjh.je_header_id = gjl.je_header_id
601 AND glcc.code_combination_id = gjl.code_combination_id
602 AND glcc.chart_of_accounts_id = :gbl_coa_id
603 AND fgf.account_number = glcc.'||gbl_acc_segment || '
604 AND fgf.set_of_books_id = :gbl_sob_id
605 AND fgf.fiscal_year = :gbl_period_year
606 AND ffp.fund_value = glcc.'||gbl_bal_segment || '
607 AND ffp.treasury_symbol_id = fts.treasury_symbol_id
608 AND fts.gtas_reportable_indicator = ''Y''
609 AND fts.set_of_books_id = :gbl_sob_id
610 AND (fgf.authority_type_flag = ''Y''
611 OR fgf.fed_non_fed_flag = ''Y''
612 OR fgf.trading_partner_flag = ''Y''
613 OR fgf.budget_impact_ind_flag = ''Y'')
614 ';
615
616 log(l_module_name, l_select_stmt);
617 log(l_module_name, 'gbl_sob_id: '||gbl_sob_id);
618 log(l_module_name, 'gbl_coa_id: '||gbl_coa_id);
619 log(l_module_name, 'gbl_period_num_low: '||gbl_period_num_low);
620 log(l_module_name, 'gbl_period_num_high: '||gbl_period_num_high);
621 log(l_module_name, 'gbl_period_year: '||gbl_period_year);
622
623 OPEN l_gl_lines_cur
624 FOR l_select_stmt
625 USING gbl_sob_id,
626 gbl_period_num_low ,
627 gbl_period_num_high,
628 gbl_period_year,
629 gbl_sob_id,
630 gbl_sob_id,
631 gbl_sob_id,
632 gbl_coa_id,
633 gbl_sob_id,
634 gbl_period_year,
635 gbl_sob_id;
636
637
638 log(l_module_name, 'Initializing second list');
639 ccid_list_2 := ccid_t();
640 period_num_list_2 := period_num_t();
641 account_number_list_2 := account_number_t();
642 fund_value_list_2 := fund_value_t();
643 amount_list_2 := amount_t();
644 d_c_indicator_list_2 := d_c_indicator_t();
645 fed_nonfed_code_list_2 := fed_nonfed_code_t();
646 agency_id_list_2 := agency_id_t();
647 record_category_list_2 := record_category_t();
648 party_name_list_2 := party_name_t();
649 je_header_id_list_2 := je_header_id_t();
650 je_line_num_list_2 := je_line_num_t();
651 je_category_list_2 := je_category_t();
652 je_source_list_2 := je_source_t();
653 party_id_list_2 := party_id_t();
654 party_type_list_2 := party_type_t();
655 je_attribute_value_list_2 := je_attribute_val_t();
656 --feeder_flag_list_2 := varchar_1_t();
657 gl_period_list_2 := gl_period_t();
658 authority_type_code_list_2 := authority_type_code_t();
659 budget_impact_ind_list_2 := budget_impact_ind_t();
660 doc_num_list_2 := doc_num_t();
661 main_account_list_2 := main_account_t();
662
663 log(l_module_name, 'Done initializing second list');
664
665
666 LOOP
667 FETCH l_gl_lines_cur BULK COLLECT
668 INTO ccid_list ,
669 sob_id_list ,
670 account_number_list ,
671 amount_list ,
672 d_c_indicator_list ,
673 je_header_id_list ,
674 je_line_num_list ,
675 je_category_list ,
676 je_source_list ,
677 reference_1_list ,
678 reference_2_list ,
679 reference_3_list ,
680 reference_5_list ,
681 reference_7_list ,
682 je_attribute_value_list ,
683 fund_value_list ,
684 gl_period_list ,
685 party_id_list ,
686 party_type_list ,
687 party_name_list ,
688 agency_id_list,
689 main_account_list ,
690 fed_nonfed_flag_list ,
691 reported_status_list ,
692 record_category_list ,
693 feeder_flag_list ,
694 period_num_list ,
695 je_from_sla_flag_list ,
696 je_batch_id_list ,
697 authority_type_flag_list,
698 budget_impact_ind_flag_list,
699 trading_partner_flag_list,
700 sgl_account_number_list
701 LIMIT 10000;
702
703 IF l_gl_lines_cur%NOTFOUND THEN
704 l_last_fetch := TRUE;
705 END IF;
706
707 IF (je_header_id_list.count = 0 AND l_last_fetch) THEN
708 EXIT;
709 END IF;
710
711 log(l_module_name,'je count: '||je_header_id_list.last);
712
713 FOR i IN je_header_id_list.first .. je_header_id_list.last
714 LOOP
715 log(l_module_name,'----------------------------');
716 log(l_module_name,'Start Process loop:');
717
718 log(l_module_name, 'source: '||je_source_list(i));
719 log(l_module_name, 'cat: '||je_category_list(i));
720 log(l_module_name, 'reference1: '||reference_1_list(i));
721 log(l_module_name, 'reference2: '||reference_2_list(i));
722 log(l_module_name, 'reference3: '||reference_3_list(i));
723 log(l_module_name, 'reference5: '||reference_5_list(i));
724 log(l_module_name, 'reference7: '||reference_7_list(i));
725 log(l_module_name, 'je batch id '||je_batch_id_list(i));
726 log(l_module_name, 'je header id '||je_header_id_list(i));
727 log(l_module_name, 'je line num '||je_line_num_list(i));
728 log(l_module_name, 'je from sla flag '||je_from_sla_flag_list(i));
729 log(l_module_name,'account: '||account_number_list(i));
730 log(l_module_name,'fund: '||fund_value_list(i));
731 log(l_module_name,'je_header_id_list: '||je_header_id_list(i));
732 log(l_module_name,'sgl: '||sgl_account_number_list(i));
733
734 --Initialize variables
735 gbl_other_journal := 'N';
736 l_authority_type_code := NULL;
737 l_budget_impact_indicator := NULL;
738 l_party_info_tab.delete();
739 --l_account_number := NULL;
740 /*
741 --Check if the account number exists in fv gtas attributes table.
742 --If it does not, then use the ussgl account for getting/checking
743 --all gtas attributes
744 BEGIN
745
746 SELECT gtas_acct_number
747 INTO l_account_number
748 FROM fv_gtas_attributes
749 WHERE gtas_acct_number = account_number_list(i);
750
751 EXCEPTION WHEN NO_DATA_FOUND THEN
752 log(l_module_name,account_number_list(i)||' not found in gtas attributes,
753 using: '||sgl_account_number_list(i));
754 l_account_number := sgl_account_number_list(i);
755 END;
756 */
757 --Get Trading Partner info: Fed non fed code, TP Type, TP Name, TP Agency ID,
758 --TP Main Acct Code into l_party_info_tab
759 get_tp_attributes(je_from_sla_flag_list(i), je_batch_id_list(i),
760 je_header_id_list(i), je_line_num_list(i),
761 je_source_list(i), je_category_list(i),
762 fed_nonfed_flag_list(i), fund_value_list(i),
763 reference_1_list(i),
764 reference_2_list(i),reference_3_list(i),
765 reference_5_list(i),reference_7_list(i),
766 je_attribute_value_list(i),
767 l_party_info_tab);
768
769 IF gbl_err_code <> 0 THEN
770 RETURN;
771 END IF;
772
773 log(l_module_name,'party info tab contains: '||l_party_info_tab.COUNT||' row/s');
774 IF l_party_info_tab.COUNT > 0 THEN
775 ccid_list_2.extend(l_party_info_tab.COUNT);
776 period_num_list_2.extend(l_party_info_tab.COUNT);
777 account_number_list_2.extend(l_party_info_tab.COUNT);
778 fund_value_list_2.extend(l_party_info_tab.COUNT);
779 amount_list_2.extend(l_party_info_tab.COUNT);
780 d_c_indicator_list_2 .extend(l_party_info_tab.COUNT);
781 je_source_list_2.extend(l_party_info_tab.COUNT);
782 je_category_list_2 .extend(l_party_info_tab.COUNT);
783 je_header_id_list_2.extend(l_party_info_tab.COUNT);
784 je_line_num_list_2.extend(l_party_info_tab.COUNT);
785 gl_period_list_2.extend(l_party_info_tab.COUNT);
786 je_attribute_value_list_2.extend(l_party_info_tab.COUNT);
787 party_id_list_2.extend(l_party_info_tab.COUNT);
788 party_type_list_2.extend(l_party_info_tab.COUNT);
789 party_name_list_2.extend(l_party_info_tab.COUNT);
790 fed_nonfed_code_list_2.extend(l_party_info_tab.COUNT);
791 agency_id_list_2.extend(l_party_info_tab.COUNT);
792 main_account_list_2.extend(l_party_info_tab.COUNT);
793 authority_type_code_list_2.extend(l_party_info_tab.COUNT);
794 budget_impact_ind_list_2.extend(l_party_info_tab.COUNT);
795 record_category_list_2.extend(l_party_info_tab.COUNT);
796 doc_num_list_2.extend(l_party_info_tab.COUNT);
797
798
799 FOR j IN l_party_info_tab.FIRST .. l_party_info_tab.LAST
800 LOOP
801
802 log(l_module_name,'party_id: '||l_party_info_tab(j).party_id);
803 log(l_module_name,'party_type: '||l_party_info_tab(j).party_type);
804 log(l_module_name,'party_name: '||l_party_info_tab(j).party_name);
805 log(l_module_name,'fed_nonfed_code: '||l_party_info_tab(j).fed_nonfed_code);
806 log(l_module_name,'agency_id: '||l_party_info_tab(j).agency_id);
807 log(l_module_name,'l_authority_type_code: '||l_authority_type_code);
808 log(l_module_name,'budget_impact_ind: '||l_budget_impact_indicator);
809 log(l_module_name,'transaction_number: '||l_party_info_tab(j).doc_num);
810
811
812 ccid_list_2(k) := ccid_list(i);
813 period_num_list_2(k) := period_num_list(i);
814 account_number_list_2(k) := account_number_list(i);
815 fund_value_list_2(k) := fund_value_list(i);
816
817 --If journal is "Other" or Manual then set the amount to the
818 --journal amount
819 --IF je_source_list(i) = 'Manual' THEN
820 IF gbl_other_journal = 'Y' THEN
821 amount_list_2(k) := amount_list(i);
822 ELSE
823 amount_list_2(k) := l_party_info_tab(j).party_line_amount;
824 END IF;
825
826 --Get authority type code
827 IF authority_type_flag_list(i) = 'Y' THEN
828 get_authority_type_code(
829 sob_id_list(i), fund_value_list(i),
830 --l_account_number,
831 account_number_list(i),
832 sgl_account_number_list(i),
833 amount_list(i), je_source_list(i),
834 je_category_list(i), je_batch_id_list(i),
835 je_header_id_list(i), je_line_num_list(i), je_from_sla_flag_list(i),
836 reference_1_list(i), ccid_list(i), l_party_info_tab(j).party_name,
837 l_party_info_tab(j).agency_id, l_party_info_tab(j).main_account,
838 l_party_info_tab(j).doc_num, l_authority_type_code);
839 END IF;
840
841 --Get Budgetary Impact Indicator
842 IF budget_impact_ind_flag_list(i) = 'Y' THEN
843 get_budget_impact_ind(--l_account_number,
844 je_batch_id_list(i), je_header_id_list(i),
845 je_line_num_list(i),
846 account_number_list(i),
847 sgl_account_number_list(i),
848 fund_value_list(i), je_source_list(i),
849 je_category_list(i),ccid_list(i),amount_list(i),
850 l_party_info_tab(j).doc_num,
851 l_party_info_tab(j).party_name,
852 l_party_info_tab(j).agency_id,
853 l_party_info_tab(j).main_account,
854 l_budget_impact_indicator);
855 END IF;
856
857 --If fed non fed flag is Y then get the proper fed nonfed code
858 --and overwrite it appropriately
859 l_fed_nonfed_code := NULL;
860 IF fed_nonfed_flag_list(i) = 'Y' THEN
861
862 get_fed_nonfed_code(fund_value_list(i), account_number_list(i),
863 sgl_account_number_list(i),
864 l_party_info_tab(j).fed_nonfed_code,
865 je_source_list(i), je_category_list(i),
866 ccid_list(i), amount_list_2(k),
867 l_party_info_tab(j).doc_num,
868 l_party_info_tab(j).party_name,
869 l_party_info_tab(j).agency_id,
870 l_party_info_tab(j).main_account,
871 l_fed_nonfed_code);
872
873 fed_nonfed_code_list_2(k) := l_fed_nonfed_code;
874
875 END IF;
876
877 --If the trading partner flag is Y and fed non-fed code of cust/supp
878 --is F then agency id should exist, if it does not then raise exception
879 --Change Request 10: If fed non fed is F and agency id is not between 000
880 --and 999 then raise exception
881 l_exception_category := NULL;
882 IF (trading_partner_flag_list(i) = 'Y'
883 AND l_fed_nonfed_code = 'F') THEN
884 IF (l_party_info_tab(j).agency_id IS NULL) THEN
885 l_exception_category := 'FED_TP_MUST_HAVE_AGENCY_ID';
886 ELSE
887 BEGIN
888 SELECT TO_NUMBER(l_party_info_tab(j).agency_id)
889 INTO l_dummy_num
890 FROM dual
891 WHERE TO_NUMBER(l_party_info_tab(j).agency_id)
892 BETWEEN 0 AND 999;
893 EXCEPTION WHEN OTHERS THEN
894 l_exception_category := 'FED_TP_MUST_HAVE_AGENCY_ID';
895 END;
896 END IF;
897 IF l_exception_category IS NOT NULL THEN
898 log(l_module_name,'Agency ID is null or not valid, inserting exception.');
899 insert_exception_rec(gbl_sob_id, fund_value_list(i), account_number_list(i),
900 sgl_account_number_list(i), je_source_list(i), je_category_list(i), NULL, NULL,
901 amount_list_2(k), ccid_list(i), l_exception_category,
902 l_party_info_tab(j).doc_num, l_party_info_tab(j).party_name,
903 l_party_info_tab(j).agency_id, l_party_info_tab(j).main_account);
904 END IF;
905 END IF;
906
907 --If fed non fed is G then set agency id to 099
908 --if it is not G, then set the agency id to whatever
909 --is on the party record
910 --If the source is manual, get the agency id from je attribute
911 --If fed non fed is E or N then agency id should be blank
912 -- Bug 14332166 issue 3
913 --Bug15930636 (CR 37)
914 --IF fed_nonfed_flag_list(i) = 'Y' THEN
915 IF trading_partner_flag_list(i) = 'Y' THEN
916 IF fed_nonfed_code_list_2(k) = 'G' THEN
917 agency_id_list_2(k) := '099';
918 main_account_list_2(k) := '0000';
919 ELSE
920 IF fed_nonfed_code_list_2(k) in ('E','N') THEN
921 agency_id_list_2(k) := null;
922 main_account_list_2(k) := NULL;
923 ELSE
924 agency_id_list_2(k) := l_party_info_tab(j).agency_id;
925 main_account_list_2(k) := l_party_info_tab(j).main_account;
926 END IF;
927 END IF;
928 END IF;
929 log(l_module_name,'amount: '|| amount_list_2(k));
930 d_c_indicator_list_2(k) := d_c_indicator_list(i);
931 je_source_list_2(k) := je_source_list(i);
932 je_category_list_2(k) := je_category_list(i);
933 je_header_id_list_2(k) := je_header_id_list(i);
934 je_line_num_list_2(k) := je_line_num_list(i);
935 gl_period_list_2(k) := gl_period_list(i);
936 party_id_list_2(k) := l_party_info_tab(j).party_id;
937 party_type_list_2(k) := l_party_info_tab(j).party_type;
938 party_name_list_2(k) := l_party_info_tab(j).party_name;
939
940 authority_type_code_list_2(k) := l_authority_type_code;
941 budget_impact_ind_list_2(k) := l_budget_impact_indicator;
942 record_category_list_2(k) := 'REPORTED';
943
944 k := k+1;
945 END LOOP;
946 END IF;
947
948 END LOOP;
949
950 END LOOP;
951 log(l_module_name,'*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-');
952
953 IF (je_header_id_list_2.count > 0) THEN
954 log(l_module_name,'Inserting into activity balances');
955
956 FORALL i IN je_header_id_list_2.first .. je_header_id_list_2.last
957 INSERT
958 --INTO fv_gtas_line_balances
959 INTO fv_gtas_activity_balances
960 (
961 ccid ,
962 period_num ,
963 set_of_books_id ,
964 period_year ,
965 account_number ,
966 fund_value ,
967 amount ,
968 d_c_indicator ,
969 fed_non_fed ,
970 trading_partner_agency_id ,
971 trading_partner_main_account ,
972 record_category ,
973 trading_partner_name ,
974 period_name ,
975 je_header_id ,
976 je_line_num ,
977 je_category ,
978 je_source ,
979 trading_partner_id ,
980 trading_partner_type ,
981 --party_classification,
982 --attribute_value ,
983 balance_type ,
984 --feeder_flag ,
985 gl_period ,
986 creation_date ,
987 authority_type_code ,
988 budget_impact_ind
989 )
990 VALUES
991 (
992 ccid_list_2(i) ,
993 period_num_list_2(i) ,
994 gbl_sob_id ,
995 gbl_period_year ,
996 account_number_list_2(i) ,
997 fund_value_list_2(i) ,
998 amount_list_2(i) ,
999 d_c_indicator_list_2(i) ,
1000 fed_nonfed_code_list_2(i) ,
1001 agency_id_list_2(i),
1002 main_account_list_2(i),
1003 record_category_list_2(i) ,
1004 party_name_list_2(i) ,
1005 gbl_period_name ,
1006 je_header_id_list_2(i) ,
1007 je_line_num_list_2(i) ,
1008 je_category_list_2(i) ,
1009 je_source_list_2(i) ,
1010 party_id_list_2(i) ,
1011 party_type_list_2(i) ,
1012 -- vendor_type_list_2(i) ,
1013 --attribute_value_list_2(i) ,
1014 'L' ,
1015 --feeder_flag_list_2(i) ,
1016 gl_period_list_2(i) ,
1017 sysdate ,
1018 authority_type_code_list_2(i),
1019 budget_impact_ind_list_2(i)
1020 );
1021 ELSE
1022 log(l_module_name,'No rows found for GTAS Activity Balance Processing!');
1023
1024 end if;
1025 -- COMMIT;
1026 log(l_module_name,'End');
1027 EXCEPTION WHEN
1028 OTHERS THEN
1029 gbl_err_code := SQLCODE;
1030 gbl_err_buff := l_module_name||' - When others error: '||SQLERRM;
1031 FV_UTILITY.LOG_MESG (FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
1032 END PROCESS_GL_LINES;
1033 --------------------------------------------------------------------------------
1034 PROCEDURE LOG
1035 (module IN VARCHAR2,
1036 message_line IN VARCHAR2) IS
1037
1038 l_module_name VARCHAR2(1000) := g_module_name||'LOG';
1039
1040 BEGIN
1041 --log(l_module_name,message_line);
1042
1043 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1044 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, module, message_line);
1045 END IF;
1046
1047 EXCEPTION
1048 WHEN OTHERS THEN
1049 gbl_err_code := SQLCODE;
1050 gbl_err_buff := l_module_name||' - When others error: '||SQLERRM;
1051 FV_UTILITY.LOG_MESG (FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
1052 END log;
1053 --------------------------------------------------------------------------------
1054 --Authority type code is required for budget level 1 transactions or
1055 --reprogramming transactions.
1056 --Get authority type code from be sub-type if there is one, else
1057 --get it from BE transaction type
1058 PROCEDURE GET_AUTHORITY_TYPE_CODE
1059 (p_sob_id IN VARCHAR2, p_fund_value IN VARCHAR2,
1060 p_account_number IN VARCHAR2,
1061 p_sgl_account_number IN VARCHAR2,
1062 p_amount IN NUMBER,p_je_source IN VARCHAR2,
1063 p_je_category IN VARCHAR2,
1064 p_je_batch_id IN NUMBER,
1065 p_je_header_id IN NUMBER,
1066 p_je_line_num IN NUMBER,
1067 p_je_from_sla_flag IN VARCHAR2,
1068 p_reference1 IN VARCHAR2,
1069 p_ccid IN NUMBER, p_party_name IN VARCHAR2,
1070 p_agency_id IN VARCHAR2, p_main_account IN VARCHAR2,
1071 p_doc_num IN VARCHAR2,
1072 p_authority_type_code OUT NOCOPY VARCHAR2) IS
1073
1074 CURSOR be_cursor IS
1075 SELECT xd.source_distribution_id_num_1 transaction_id,
1076 xte.transaction_number
1077 --, (NVL(xd.unrounded_accounted_dr,0) - NVL(xd.unrounded_accounted_cr,0)) amount
1078 FROM gl_import_references gli,
1079 xla_ae_lines xl ,
1080 xla_ae_headers xh ,
1081 xla_distribution_links xd,
1082 xla_transaction_entities xte
1083 WHERE gli.je_batch_id = p_je_batch_id
1084 AND gli.je_header_id = p_je_header_id
1085 AND gli.je_line_num = p_je_line_num
1086 AND xl.gl_sl_link_id = gli.gl_sl_link_id
1087 AND xl.application_id = 8901
1088 AND xh.ae_header_id = xl.ae_header_id
1089 AND xl.ledger_id = gbl_sob_id
1090 AND xd.event_id = xh.event_id
1091 AND xd.ae_header_id = xh.ae_header_id
1092 AND xd.ae_line_num = xl.ae_line_num
1093 AND xh.entity_id = xte.entity_id;
1094
1095 l_be_trx_id NUMBER;
1096 l_trx_type_id NUMBER;
1097 l_sub_type fv_be_trx_sub_types.sub_type%TYPE;
1098 l_authority_type_code fv_gtas_attributes.authority_type1%TYPE;
1099 l_authority_type_code_sub fv_gtas_attributes.authority_type1%TYPE;
1100 l_authority_type_code_trx fv_gtas_attributes.authority_type1%TYPE;
1101 l_authority_type_code_tmp fv_gtas_attributes.authority_type1%TYPE;
1102 l_authority_type_found VARCHAR2(1);
1103 l_exception_category fv_gtas_exceptions.exception_category%TYPE;
1104 l_module_name VARCHAR2(200) := g_module_name||'GET_AUTHORITY_TYPE_CODE';
1105 l_auth_select VARCHAR2(200);
1106 l_auth_type_code_count NUMBER;
1107 l_be_doc_num xla_transaction_entities.transaction_number%TYPE;
1108 l_account_number fv_gtas_attributes.gtas_acct_number%TYPE;
1109
1110
1111 BEGIN
1112 log(l_module_name,'Begin: '||l_module_name);
1113
1114 --Authority type is required only for budgetary transactions
1115 --Modified for CR21. If source is Manual, then we need to
1116 --check whether there is only one authority type code in attributes
1117 --else raise exception
1118 IF ((p_je_source NOT IN ('Budgetary Transaction', 'Manual')) OR
1119 (p_je_source = 'Budgetary Transaction' AND
1120 p_je_category = 'Treasury Confirmation'))THEN
1121 log(l_module_name,'Not a Budgetary Trx. No need for Auth Type Code.');
1122 RETURN;
1123 END IF;
1124
1125 IF p_je_source = 'Budgetary Transaction' THEN
1126 IF p_je_from_sla_flag = 'Y' THEN
1127 OPEN be_cursor;
1128 FETCH be_cursor INTO l_be_trx_id, l_be_doc_num;
1129 CLOSE be_cursor;
1130 ELSE
1131 l_be_trx_id := TO_NUMBER(p_reference1);
1132 END IF;
1133
1134 log(l_module_name,'l_be_trx_id: '||l_be_trx_id);
1135 BEGIN
1136 SELECT bd.TRANSACTION_TYPE_ID, bd.SUB_TYPE
1137 INTO l_trx_type_id, l_sub_type
1138 FROM fv_be_trx_dtls bd, fv_be_trx_hdrs bh, fv_budget_levels bl
1139 WHERE bd.transaction_id = l_be_trx_id
1140 AND bd.doc_id = bh.doc_id
1141 AND bd.set_of_books_id = bh.set_of_books_id
1142 AND bl.budget_level_id = bh.budget_level_id
1143 AND bl.set_of_books_id = bh.set_of_books_id
1144 AND bh.set_of_books_id = gbl_sob_id
1145 AND (
1146 (bl.budget_level_id = 1 and bh.source <> 'RPR') OR
1147 (bh.source = 'RPR')
1148 );
1149 EXCEPTION WHEN NO_DATA_FOUND THEN
1150 log(l_module_name,'Not a level 1 or a reprog Budgetary Trx.'||
1151 ' No need for Auth Type Code.');
1152 RETURN;
1153 END;
1154
1155 IF l_sub_type IS NOT NULL THEN
1156 SELECT authority_type
1157 INTO l_authority_type_code_sub
1158 FROM fv_be_trx_sub_types bs
1159 WHERE bs.be_tt_id = l_trx_type_id
1160 AND bs.sub_type = l_sub_type
1161 AND bs.ledger_id = gbl_sob_id;
1162 log(l_module_name,'Sub Type Authority Type: '||l_authority_type_code_sub);
1163 ELSE
1164 SELECT authority_type
1165 INTO l_authority_type_code_trx
1166 FROM fv_be_transaction_types bt
1167 WHERE bt.be_tt_id = l_trx_type_id
1168 AND bt.set_of_books_id = gbl_sob_id;
1169 log(l_module_name,'Trx Type Authority Type: '||l_authority_type_code_trx);
1170 END IF;
1171
1172 IF l_authority_type_code_sub IS NOT NULL THEN
1173 l_authority_type_code := l_authority_type_code_sub;
1174 ELSIF
1175 l_authority_type_code_trx IS NOT NULL THEN
1176 l_authority_type_code := l_authority_type_code_trx;
1177 END IF;
1178 END IF;
1179 --IF account number does not exist in gtas attributes, then
1180 --we have to check for attributes of the sgl account
1181 GET_USSGL_ACCT(p_account_number, p_sgl_account_number, l_account_number);
1182
1183 --The authority type code must match one value in fv gtas attributes
1184 --If it does not, then insert exception row
1185 IF (p_je_source = 'Budgetary Transaction' AND
1186 l_authority_type_code IS NOT NULL) THEN
1187 BEGIN
1188 SELECT 'Y'
1189 INTO l_authority_type_found
1190 FROM fv_gtas_attributes
1191 WHERE set_of_books_id = gbl_sob_id
1192 AND gtas_acct_number = l_account_number --p_account_number
1193 AND (authority_type1 = l_authority_type_code
1194 OR authority_type2 = l_authority_type_code
1195 OR authority_type3 = l_authority_type_code
1196 OR authority_type4 = l_authority_type_code
1197 OR authority_type5 = l_authority_type_code
1198 OR authority_type6 = l_authority_type_code);
1199
1200 log(l_module_name,'Returning Authority Type: '||l_authority_type_code);
1201 p_authority_type_code := l_authority_type_code;
1202 --Singe authority type found return
1203 RETURN;
1204
1205 EXCEPTION WHEN NO_DATA_FOUND THEN
1206 log(l_module_name,'Authority type: '||l_authority_type_code||' not found in gtas attributes.');
1207 IF l_authority_type_code_sub IS NOT NULL THEN
1208 l_exception_category := 'INVALID_AUTH_TYPE_CODE_SUB';
1209 ELSIF
1210 l_authority_type_code_trx IS NOT NULL THEN
1211 l_exception_category := 'INVALID_AUTH_TYPE_CODE_TRX';
1212 END IF;
1213 insert_exception_rec(p_sob_id, p_fund_value, p_account_number,p_sgl_account_number,
1214 p_je_source, p_je_category, p_je_header_id,
1215 p_je_line_num, p_amount, p_ccid, l_exception_category,
1216 l_be_doc_num, p_party_name, p_agency_id, p_main_account);
1217 RETURN;
1218 END;
1219
1220 ELSE
1221 --If authority type code is not found, then get authority type
1222 --from gtas attributes. If there is more than one value or there
1223 --is no value, then raise an exception
1224 log(l_module_name,'Trying to find Authority type from gtas attributes');
1225 l_auth_type_code_count := 0;
1226 l_authority_type_code_tmp := NULL;
1227 l_authority_type_code := NULL;
1228 --Set the doc num to p_doc_num if the source is Manual
1229 IF p_je_source = 'Manual' THEN
1230 l_be_doc_num := p_doc_num;
1231 END IF;
1232
1233 FOR i in 1..6 LOOP
1234 begin
1235
1236 l_auth_select := 'SELECT authority_type'||i||
1237 ' from fv_gtas_attributes
1238 where gtas_acct_number = '||l_account_number; --p_account_number;
1239
1240 EXECUTE IMMEDIATE l_auth_select INTO l_authority_type_code_tmp;
1241 log(l_module_name,'l_authority_type_code_tmp: '||l_authority_type_code_tmp);
1242
1243
1244 IF l_authority_type_code_tmp IS NOT NULL THEN
1245 l_auth_type_code_count := l_auth_type_code_count+1;
1246 l_authority_type_code := l_authority_type_code_tmp;
1247 END IF;
1248 exception when no_data_found then null;
1249 end;
1250
1251 END LOOP;
1252
1253 IF l_auth_type_code_count > 1 THEN
1254 log(l_module_name,'Found more then one authority type, '||
1255 'this is an exception');
1256 l_exception_category := 'NO_SINGLE_AUTH_TYPE_CODE';
1257
1258 insert_exception_rec(p_sob_id, p_fund_value, p_account_number,p_sgl_account_number,
1259 p_je_source, p_je_category, p_je_header_id,
1260 p_je_line_num, p_amount, p_ccid, l_exception_category,
1261 l_be_doc_num, p_party_name, p_agency_id,
1262 p_main_account);
1263 ELSIF l_auth_type_code_count = 0 THEN
1264 log(l_module_name,'Authority type not found, '||
1265 'this is an exception');
1266 l_exception_category := 'NO_SINGLE_AUTH_TYPE_CODE';
1267 insert_exception_rec(p_sob_id, p_fund_value, p_account_number,p_sgl_account_number,
1268 p_je_source, p_je_category, p_je_header_id,
1269 p_je_line_num, p_amount, p_ccid, l_exception_category,
1270 l_be_doc_num, p_party_name, p_agency_id,
1271 p_main_account);
1272 ELSE
1273 p_authority_type_code := l_authority_type_code;
1274 END IF;
1275
1276 END IF;
1277 log(l_module_name,'End: '||l_module_name);
1278 EXCEPTION WHEN OTHERS THEN
1279 gbl_err_code := SQLCODE;
1280 gbl_err_buff := l_module_name||' - When others error: '||SQLERRM;
1281 FV_UTILITY.LOG_MESG (FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
1282 END GET_AUTHORITY_TYPE_CODE;
1283 --------------------------------------------------------------------------------
1284 PROCEDURE DELETE_EXCEPTION_RECS IS
1285 l_module_name VARCHAR2(200) := g_module_name||'DELETE_EXCEPTION_RECS';
1286
1287 BEGIN
1288 log(l_module_name, 'Begin: '||l_module_name);
1289
1290 DELETE FROM fv_gtas_exceptions
1291 WHERE set_of_books_id = gbl_sob_id
1292 AND period_year = gbl_period_year;
1293 --AND record_category = 'EXCEPTION';
1294 log(l_module_name, 'Deleted Exception Rows.');
1295 log(l_module_name, 'End: '||l_module_name);
1296
1297 COMMIT;
1298 EXCEPTION WHEN OTHERS THEN
1299 gbl_err_code := 2;
1300 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
1301 FV_UTILITY.LOG_MESG(gbl_err_buff);
1302 END DELETE_EXCEPTION_RECS;
1303 --------------------------------------------------------------------------------
1304 PROCEDURE INSERT_EXCEPTION_REC(
1305 p_set_of_books_id IN NUMBER, p_fund_value IN VARCHAR2,
1306 p_account_number IN VARCHAR2,
1307 p_sgl_account_number IN VARCHAR2,
1308 p_je_source IN VARCHAR2,
1309 p_je_category IN VARCHAR2,
1310 p_je_header_id IN NUMBER, p_je_line_num IN NUMBER,
1311 p_amount IN NUMBER, p_ccid IN NUMBER, p_exception_category IN VARCHAR2,
1312 p_doc_num IN VARCHAR2, p_party_name IN VARCHAR2,
1313 p_agency_id IN VARCHAR2, p_main_acct IN VARCHAR2) IS
1314 l_module_name VARCHAR2(200) := g_module_name||'INSERT_EXCEPTION_REC';
1315 --l_dc_indicator VARCHAR2(1);
1316 PRAGMA AUTONOMOUS_TRANSACTION;
1317
1318 BEGIN
1319 log(l_module_name,'Begin: '||l_module_name);
1320 /*
1321 IF NVL(p_amount,0) <= 0 THEN
1322 l_dc_indicator := 'C';
1323 ELSE
1324 l_dc_indicator := 'D';
1325 END IF;
1326 */
1327 log(l_module_name, '******p_account_number: '||p_account_number);
1328 log(l_module_name, 'p_sgl_account_number: '||p_sgl_account_number);
1329 INSERT INTO fv_gtas_exceptions
1330 (set_of_books_id, fund_value, period_year, period_num, account_number,
1331 sgl_acct_num,
1332 je_source, je_category,
1333 je_header_id, je_line_num, amount, ccid,
1334 exception_category, creation_date, doc_num, trading_partner_name,
1335 trading_partner_agency_id, trading_partner_main_account)
1336 VALUES
1337 (p_set_of_books_id, p_fund_value, gbl_period_year, gbl_period_num_high,
1338 p_account_number , p_sgl_account_number, p_je_source, p_je_category,
1339 p_je_header_id, p_je_line_num, p_amount,p_ccid,
1340 p_exception_category, sysdate, p_doc_num, p_party_name,
1341 p_agency_id, p_main_acct);
1342
1343 log(l_module_name,'Inserted Acct Num: '||p_account_number||'-Exception: '||p_exception_category);
1344 log(l_module_name,'End: '||l_module_name);
1345 COMMIT;
1346
1347 EXCEPTION WHEN OTHERS THEN
1348 gbl_err_code := 2;
1349 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
1350 log(l_module_name,'-When others error: '||SQLERRM);
1351 FV_UTILITY.LOG_MESG(gbl_err_buff);
1352 END INSERT_EXCEPTION_REC;
1353
1354 --------------------------------------------------------------------------------
1355 -- For suppliers use GDF, for customers(TCA)
1356 --use named columns, if they exist, else get from customer type.
1357 PROCEDURE GET_TP_ATTRIBUTES(p_je_from_sla_flag IN VARCHAR2, p_je_batch_id IN NUMBER,
1358 p_je_header_id IN NUMBER, p_je_line_num IN NUMBER,
1359 p_je_source IN VARCHAR2,
1360 p_je_category IN VARCHAR2,p_fed_nonfed_flag IN VARCHAR2,
1361 p_fund_value IN VARCHAR2,
1362 --p_account_num IN VARCHAR2, p_sgl_account_num IN VARCHAR2,
1363 p_reference1 IN VARCHAR2,
1364 p_reference2 IN VARCHAR2,
1365 p_reference3 IN VARCHAR2,
1366 p_reference5 IN VARCHAR2,
1367 p_reference7 IN VARCHAR2,
1368 p_je_attrib_val IN VARCHAR2,
1369 p_party_info_tab OUT NOCOPY party_info_table) IS
1370 l_module_name VARCHAR2(200) := g_module_name||'GET_TP_ATTRIBUTES';
1371 --l_fund_type fv_treasury_symbols.fund_type%TYPE;
1372 l_tp_type VARCHAR2(1);
1373 --l_tp_id NUMBER;
1374 --l_tp_fed_nonfed_code fv_gtas_attributes.fed_non_fed1%TYPE;
1375 l_tp_name po_vendors.vendor_name%TYPE;
1376 --l_tp_agency_id VARCHAR2(3);
1377 --l_tp_main_acct VARCHAR2(4);
1378 --l_fed_nonfed_code_exists VARCHAR2(1);
1379 l_appl_id NUMBER;
1380 i INTEGER := 1;
1381 BEGIN
1382 log(l_module_name, 'Begin: '||l_module_name);
1383
1384 --Get party attributes for all journal sources
1385 IF p_je_source = 'Payables' THEN
1386
1387 --For R12 data
1388 IF p_je_from_sla_flag = 'Y' THEN
1389 get_party_info_r12 (p_je_source, p_je_category, p_je_batch_id ,
1390 p_je_header_id ,
1391 p_je_line_num, p_fund_value, 200, p_party_info_tab);
1392 IF gbl_err_code <> 0 THEN
1393 RETURN;
1394 END IF;
1395 --For 11i data
1396 ELSIF (p_reference2 IS NOT NULL) THEN
1397 get_party_info_11i(p_je_source, p_je_category, null, p_reference2,
1398 null, null, null,
1399 p_party_info_tab);
1400 ELSE
1401 l_tp_name := 'Other';
1402 log(l_module_name,'REFERENCE_2 I.E. INVOICE_ID IS NULL');
1403 END IF;
1404
1405 ELSIF (p_je_source IN ('Purchasing','Cost Management') AND
1406 p_je_category <> 'Requisitions')THEN
1407 IF (p_je_category IN ('Receiving','Inventory')) THEN
1408 log(l_module_name,'REFERENCE 2: '|| P_REFERENCE2);
1409 log(l_module_name,'REFERENCE 5: '|| P_REFERENCE5);
1410 --For R12 data
1411 IF p_je_from_sla_flag = 'Y' THEN
1412 IF (p_je_source = 'Cost Management') THEN
1413 l_appl_id := 707;
1414 ELSE
1415 l_appl_id := 201;
1416 END IF;
1417 get_party_info_r12 (p_je_source, p_je_category, p_je_batch_id ,
1418 p_je_header_id ,
1419 p_je_line_num, p_fund_value, l_appl_id, p_party_info_tab);
1420 IF gbl_err_code <> 0 THEN
1421 RETURN;
1422 END IF;
1423
1424 ELSIF (p_reference2 IS NOT NULL AND p_reference5 IS NOT NULL) THEN
1425 get_party_info_11i(p_je_source, p_je_category, null, p_reference2,
1426 null, p_reference5, null,
1427 p_party_info_tab);
1428 ELSIF (p_reference2 IS NULL) THEN
1429 l_tp_name := 'Other';
1430 l_tp_type := 'S';
1431 log(l_module_name, 'REFERENCE 2 I.E. PO_HEADER_ID IS NULL');
1432 ELSE
1433 l_tp_name := 'Other';
1434 l_tp_type := 'S';
1435 log(l_module_name, 'REFERENCE 5 I.E. TRANSACTION_ID IS NULL');
1436 END IF;
1437
1438 ELSIF (p_je_category IN ('Purchases', 'Release')) THEN
1439 IF p_je_from_sla_flag = 'Y' THEN
1440 get_party_info_r12 (p_je_source, p_je_category, p_je_batch_id ,
1441 p_je_header_id ,
1442 p_je_line_num , p_fund_value, 201, p_party_info_tab);
1443 IF gbl_err_code <> 0 THEN
1444 RETURN;
1445 END IF;
1446 ELSIF (p_reference2 IS NOT NULL) THEN
1447 get_party_info_11i(p_je_source, p_je_category, null, p_reference2,
1448 null, null, null,
1449 p_party_info_tab);
1450 ELSE
1451 l_tp_name := 'Other';
1452 l_tp_type := 'S';
1453 log(l_module_name, 'REFERENCE 2 I.E. PO HEADER ID IS NULL');
1454 END IF;
1455 END IF;
1456
1457 ELSIF p_je_source = 'Receivables' THEN
1458 log(l_module_name, 'REFERENCE 7: '|| P_REFERENCE7);
1459 --For R12 data
1460 IF p_je_from_sla_flag = 'Y' THEN
1461 get_party_info_r12 (p_je_source, p_je_category, p_je_batch_id ,
1462 p_je_header_id ,
1463 p_je_line_num, p_fund_value, 222, p_party_info_tab);
1464 IF gbl_err_code <> 0 THEN
1465 RETURN;
1466 END IF;
1467 --For 11i data
1468 ELSIF (p_reference7 IS NOT NULL) THEN
1469 get_party_info_11i(p_je_source, p_je_category, null, p_reference2,
1470 null, null, p_reference7,
1471 p_party_info_tab);
1472 IF gbl_err_code <> 0 THEN
1473 RETURN;
1474 END IF;
1475 ELSE
1476 l_tp_name := 'Other';
1477 l_tp_type := 'C';
1478 log(l_module_name, 'REFERENCE 7 I.E. CUSTOMER_ID IS NULL');
1479 END IF;
1480
1481 --R12.0 has je source as "Budgetary Transaction" for treasury confirmation
1482 --where R11i has "Payables"
1483 ELSIF ((p_je_source = 'Budgetary Transaction' OR
1484 p_je_source = 'Payables') AND
1485 p_je_category = 'Treasury Confirmation') THEN
1486
1487 IF p_je_from_sla_flag = 'Y' THEN
1488 get_party_info_r12(p_je_source, p_je_category, p_je_batch_id ,
1489 p_je_header_id ,
1490 p_je_line_num , p_fund_value, 8901, p_party_info_tab);
1491 IF gbl_err_code <> 0 THEN
1492 RETURN;
1493 END IF;
1494 ELSIF (p_reference3 IS NOT NULL) THEN
1495 get_party_info_11i('Payables', p_je_category, null, null,
1496 p_reference3, null, null,
1497 p_party_info_tab);
1498
1499 IF gbl_err_code <> 0 THEN
1500 RETURN;
1501 END IF;
1502 ELSE
1503 l_tp_name := 'Other';
1504 l_tp_type := 'C';
1505 log(l_module_name, 'REFERENCE 3 I.E. Check ID IS NULL');
1506 END IF;
1507 --Section for budgetary transactions
1508 ELSIF (p_je_source = 'Budgetary Transaction' AND
1509 p_je_category <> 'Treasury Confirmation') THEN
1510 IF p_je_from_sla_flag = 'Y' THEN
1511 get_party_info_r12(p_je_source, p_je_category, p_je_batch_id ,
1512 p_je_header_id ,
1513 p_je_line_num , p_fund_value, 8901, p_party_info_tab);
1514 IF gbl_err_code <> 0 THEN
1515 RETURN;
1516 END IF;
1517 END IF;
1518 --Modified for change request 18
1519 --Modified for change request 42 bug16343345
1520 ELSE
1521 log(l_module_name, 'Manual or Other journal');
1522
1523 IF (p_je_source = 'Manual') THEN
1524 p_party_info_tab(i).party_type := 'M';
1525 ELSE
1526 p_party_info_tab(i).party_type := 'O';
1527 END IF;
1528
1529 p_party_info_tab(i).party_id := NULL;
1530 p_party_info_tab(i).party_name := NULL;
1531
1532 p_party_info_tab(i).agency_id := SUBSTR(p_je_attrib_val,1,3);
1533 p_party_info_tab(i).main_account := SUBSTR(p_je_attrib_val,4,4);
1534 --If agency id is '099', then fed nonfed code is G
1535 --if it is other than '099' then F, else N.
1536 --First 3 digits of je attrib val contains the agency id
1537 IF p_party_info_tab(i).agency_id IS NOT NULL THEN
1538 IF p_party_info_tab(i).agency_id = '099' THEN
1539 p_party_info_tab(i).fed_nonfed_code := 'G';
1540 ELSE
1541 p_party_info_tab(i).fed_nonfed_code := 'F';
1542 END IF;
1543 ELSE
1544 p_party_info_tab(i).fed_nonfed_code := 'N';
1545 END IF;
1546 --Get journal name as doc num
1547 SELECT SUBSTR(name,1,30)
1548 INTO p_party_info_tab(i).doc_num
1549 FROM gl_je_headers
1550 WHERE je_header_id = p_je_header_id;
1551
1552 gbl_other_journal := 'Y';
1553
1554 END IF;
1555 log(l_module_name,'End: '||l_module_name);
1556 EXCEPTION WHEN OTHERS THEN
1557 gbl_err_code := 2;
1558 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
1559 FV_UTILITY.LOG_MESG(gbl_err_buff);
1560 END GET_TP_ATTRIBUTES;
1561 --------------------------------------------------------------------------------
1562 PROCEDURE GET_PARTY_INFO_R12(p_je_source IN VARCHAR2,
1563 p_je_category IN VARCHAR2,
1564 p_je_batch_id IN NUMBER,
1565 p_je_header_id IN NUMBER,
1566 p_je_line_num IN NUMBER,
1567 p_fund_value IN VARCHAR2,
1568 p_application_id IN NUMBER,
1569 p_party_info_tab OUT NOCOPY party_info_table) IS
1570 l_select VARCHAR2(300);
1571 CURSOR other_source_cur
1572 IS
1573 SELECT ael.party_id,
1574 NVL(ael.accounted_dr,0) - NVL(ael.accounted_cr,0) amount,
1575 xte.transaction_number
1576 FROM gl_import_references i,
1577 xla_ae_lines ael,
1578 xla_ae_headers aeh,
1579 xla_transaction_entities xte
1580 WHERE i.je_batch_id = p_je_batch_id
1581 AND i.je_header_id = p_je_header_id
1582 AND i.je_line_num = p_je_line_num
1583 AND i.gl_sl_link_id = ael.gl_sl_link_id
1584 AND ael.application_id = p_application_id
1585 AND ael.ledger_id = gbl_sob_id
1586 and ael.ae_header_id = aeh.ae_header_id
1587 and aeh.entity_id = xte.entity_id;
1588
1589
1590 CURSOR project_source_cur
1591 IS
1592 SELECT pc.customer_id party_id,
1593 NVL(ael.accounted_dr,0) - NVL(ael.accounted_cr,0) amount,
1594 xte.transaction_number
1595 FROM gl_je_lines je ,
1596 gl_import_references gir ,
1597 xla_ae_lines ael ,
1598 xla_ae_headers aeh ,
1599 pa_draft_revenues_all pdr,
1600 pa_agreements_all agr ,
1601 pa_project_customers pc,
1602 xla_transaction_entities xte
1603 WHERE je.je_header_id = gir.je_header_id
1604 AND je.je_line_num = gir.je_line_num
1605 AND ael.gl_sl_link_id = gir.gl_sl_link_id
1606 AND ael.ae_header_id = aeh.ae_header_id
1607 AND aeh.event_id = pdr.event_id
1608 AND agr.agreement_id = pdr.agreement_id
1609 AND pc.project_id = pdr.project_id
1610 AND pc.customer_id = agr.customer_id
1611 AND pc.customer_id IS NOT NULL
1612 AND gir.je_header_id =p_je_header_id
1613 AND ael.application_id = p_application_id
1614 AND gir.je_line_num = p_je_line_num
1615 AND gir.je_batch_id = p_je_batch_id
1616 and xte.entity_id = aeh.entity_id;
1617
1618 CURSOR be_trx_cursor IS
1619 SELECT (NVL(xd.unrounded_accounted_dr,0) - NVL(xd.unrounded_accounted_cr,0)) amount,
1620 bd.transaction_id,
1621 bd.dept_id, bd.main_account,
1622 bh.doc_number
1623 FROM gl_import_references gli,
1624 xla_ae_lines xl ,
1625 xla_ae_headers xh ,
1626 xla_distribution_links xd,
1627 xla_transaction_entities xte,
1628 fv_be_trx_dtls bd,
1629 fv_be_trx_hdrs bh
1630 WHERE gli.je_batch_id = p_je_batch_id
1631 AND gli.je_header_id = p_je_header_id
1632 AND gli.je_line_num = p_je_line_num
1633 AND xl.gl_sl_link_id = gli.gl_sl_link_id
1634 AND xl.application_id = 8901
1635 AND xh.ae_header_id = xl.ae_header_id
1636 AND xl.ledger_id = gbl_sob_id
1637 AND xd.event_id = xh.event_id
1638 AND xd.ae_header_id = xh.ae_header_id
1639 AND xd.ae_line_num = xl.ae_line_num
1640 AND xh.entity_id = xte.entity_id
1641 and bd.transaction_id = xd.source_distribution_id_num_1
1642 and bh.doc_id = bd.doc_id;
1643
1644 CURSOR cst_cursor IS
1645 SELECT
1646 (NVL(xd.unrounded_accounted_dr,0)-NVL(xd.unrounded_accounted_cr,0)) amount,
1647 reference4 doc_num, poh.vendor_id, rc.rcv_transaction_id
1648 FROM gl_import_references gli,
1649 xla_ae_lines xl ,
1650 xla_ae_headers xh ,
1651 xla_distribution_links xd,
1652 rcv_receiving_sub_ledger rc,
1653 po_headers_all poh
1654 WHERE gli.je_batch_id = p_je_batch_id
1655 AND gli.je_header_id = p_je_header_id
1656 AND gli.je_line_num = p_je_line_num
1657 AND xl.gl_sl_link_id = gli.gl_sl_link_id
1658 AND xl.application_id = 707
1659 AND xh.ae_header_id = xl.ae_header_id
1660 AND xl.ledger_id = gbl_sob_id
1661 AND xd.event_id = xh.event_id
1662 AND xd.ae_header_id = xh.ae_header_id
1663 AND xd.ae_line_num = xl.ae_line_num
1664 AND rc.rcv_sub_ledger_id = xd.source_distribution_id_num_1
1665 AND poh.po_header_id = rc.reference2;
1666
1667 CURSOR trs_confirm_cursor IS
1668 SELECT
1669 (NVL(xd.unrounded_accounted_dr,0)-NVL(xd.unrounded_accounted_cr,0)) amount,
1670 aip.check_id
1671 FROM gl_import_references gli,
1672 xla_ae_lines xl ,
1673 xla_ae_headers xh ,
1674 xla_distribution_links xd,
1675 ap_invoice_payments_all aip,
1676 ap_payment_hist_dists aphd
1677 WHERE gli.je_batch_id = p_je_batch_id
1678 AND gli.je_header_id = p_je_header_id
1679 AND gli.je_line_num = p_je_line_num
1680 AND xl.gl_sl_link_id = gli.gl_sl_link_id
1681 AND xl.application_id = 8901
1682 AND xh.ae_header_id = xl.ae_header_id
1683 AND xl.ledger_id = gbl_sob_id
1684 AND xd.event_id = xh.event_id
1685 AND xd.ae_header_id = xh.ae_header_id
1686 AND xd.ae_line_num = xl.ae_line_num
1687 AND aphd.payment_hist_dist_id = xd.source_distribution_id_num_1
1688 AND aip.invoice_payment_id = aphd.invoice_payment_id;
1689
1690 l_module_name VARCHAR2(200) := g_module_name||'GET_PARTY_INFO_R12';
1691 i INTEGER := 1;
1692 --l_be_trx_id NUMBER;
1693 l_rcv_transaction_id rcv_transactions.transaction_id%TYPE;
1694
1695 BEGIN
1696 log(l_module_name, 'Begin');
1697
1698 IF p_je_source IN ('Purchasing', 'Payables', 'Receivables') THEN
1699 FOR other_source_rec IN other_source_cur
1700 LOOP
1701
1702 log(l_module_name,'other_source_rec.party_id: '||other_source_rec.party_id);
1703 log(l_module_name,'other_source_rec.amount: '||other_source_rec.amount);
1704
1705 IF other_source_rec.party_id IS NOT NULL THEN
1706 IF p_je_source IN ('Purchasing', 'Payables') THEN
1707 BEGIN
1708 SELECT vendor_id, 'S',
1709 v.vendor_name,
1710 NVL(v.global_attribute4,'N') "fed_nonfed_code",
1711 v.global_attribute5 "agency_id" ,
1712 other_source_rec.amount,
1713 other_source_rec.transaction_number
1714 INTO p_party_info_tab(i).party_id,
1715 p_party_info_tab(i).party_type ,
1716 p_party_info_tab(i).party_name,
1717 p_party_info_tab(i).fed_nonfed_code ,
1718 P_party_info_tab(i).agency_id ,
1719 P_party_info_tab(i).party_line_amount,
1720 P_party_info_tab(i).doc_num
1721 FROM ap_suppliers v
1722 WHERE v.vendor_id = other_source_rec.party_id;
1723 EXCEPTION
1724 WHEN NO_DATA_FOUND THEN
1725 log(l_module_name,'No data found in ap_suppliers for vendor_id: '|| other_source_rec.party_id);
1726 p_party_info_tab(i).party_id := other_source_rec.party_id;
1727 p_party_info_tab(i).party_type := 'S';
1728 p_party_info_tab(i).party_name := 'Other';
1729 P_party_info_tab(i).party_line_amount := other_source_rec.amount;
1730 END;
1731 ELSIF (p_je_source = 'Receivables' AND
1732 p_je_category <> 'Misc Receipts') THEN
1733 --For 12.2 and above, have to use dynamic select
1734 --Column will not exist for 12.1 and below, if dynamic select is not used
1735 --compilation will error
1736 IF gbl_cust_col_exists = 'Y' THEN
1737 log(l_module_name,'12.2 or above, using dynamic select');
1738 l_select := ' SELECT cust_account_id, account_name,
1739 NVL(federal_entity_type,''N''),
1740 trading_partner_agency_id
1741 FROM hz_cust_accounts_all
1742 WHERE cust_account_id = :party_id';
1743 EXECUTE IMMEDIATE l_select
1744 INTO p_party_info_tab(i).party_id,
1745 p_party_info_tab(i).party_name,
1746 p_party_info_tab(i).fed_nonfed_code,
1747 p_party_info_tab(i).agency_id
1748 USING other_source_rec.party_id;
1749 p_party_info_tab(i).party_type := 'S';
1750 p_party_info_tab(i).party_line_amount := other_source_rec.amount;
1751 p_party_info_tab(i).doc_num := other_source_rec.transaction_number;
1752
1753 ELSE
1754 --For 12.1 and below
1755 SELECT c.cust_account_id, 'C', c.account_name,
1756 DECODE(c.customer_class_code, 'FEDERAL','F',
1757 'GENERAL FUND', 'G',
1758 'NON-FEDERAL EXCEPTION', 'E',
1759 'N') "fed_nonfed_code",
1760 DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1,
1761 'ATTRIBUTE2', C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3,
1762 'ATTRIBUTE4', C.ATTRIBUTE4, 'ATTRIBUTE5', C.ATTRIBUTE5,
1763 'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7', C.ATTRIBUTE7,
1764 'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
1765 'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11,
1766 'ATTRIBUTE12', C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13,
1767 'ATTRIBUTE14', C.ATTRIBUTE14, 'ATTRIBUTE15', C.ATTRIBUTE15) "agency_id",
1768 other_source_rec.amount,
1769 other_source_rec.transaction_number
1770 INTO p_party_info_tab(i).party_id,
1771 p_party_info_tab(i).party_type,
1772 p_party_info_tab(i).party_name,
1773 p_party_info_tab(i).fed_nonfed_code ,
1774 p_party_info_tab(i).agency_id ,
1775 P_party_info_tab(i).party_line_amount,
1776 p_party_info_tab(i).doc_num
1777 FROM hz_cust_accounts_all c
1778 WHERE c.cust_account_id = other_source_rec.party_id;
1779
1780 END IF;
1781 END IF;
1782 --party id is null
1783 ELSE
1784 IF (p_je_source = 'Receivables' AND
1785 p_je_category = 'Misc Receipts') THEN
1786 p_party_info_tab(i).party_id := NULL;
1787 p_party_info_tab(i).party_type := 'C';
1788 p_party_info_tab(i).party_name := 'Misc Recpt';
1789 p_party_info_tab(i).fed_nonfed_code := 'N';
1790 p_party_info_tab(i).agency_id := NULL;
1791 P_party_info_tab(i).party_line_amount := other_source_rec.amount;
1792 p_party_info_tab(i).doc_num := other_source_rec.transaction_number;
1793
1794 ELSE
1795
1796 p_party_info_tab(i).party_line_amount := other_source_rec.amount;
1797 p_party_info_tab(i).doc_num := other_source_rec.transaction_number;
1798
1799 END IF;
1800 END IF;
1801 log(l_module_name,'party_id: '||p_party_info_tab(i).party_id);
1802 log(l_module_name,'party_type: '||p_party_info_tab(i).party_type);
1803 log(l_module_name,'agency id: '|| p_party_info_tab(i).agency_id);
1804 log(l_module_name,'party_name: '||p_party_info_tab(i).party_name);
1805 log(l_module_name,'fed nonfed: '||p_party_info_tab(i).fed_nonfed_code);
1806 log(l_module_name,'line_amount: '||p_party_info_tab(i).party_line_amount);
1807 log(l_module_name,'Trx Num: '||p_party_info_tab(i).doc_num);
1808 i := i + 1;
1809 END LOOP;
1810
1811 ELSIF p_je_source = 'Projects' THEN
1812 FOR project_source_rec IN project_source_cur
1813 LOOP
1814 --p_party_info_tab(i).feeder_flag := 'Y';
1815
1816 IF project_source_rec.party_id IS NOT NULL THEN
1817 --For 12.2 and above
1818 IF gbl_cust_col_exists = 'Y' THEN
1819 l_select := ' SELECT cust_account_id, account_name,
1820 federal_entity_type, trading_partner_agency_id
1821 FROM hz_cust_accunts
1822 WEHRE cust_account_id = :party_id';
1823
1824 EXECUTE IMMEDIATE l_select
1825 INTO p_party_info_tab(i).party_id,
1826 p_party_info_tab(i).party_name,
1827 p_party_info_tab(i).fed_nonfed_code,
1828 p_party_info_tab(i).agency_id
1829 USING project_source_rec.party_id;
1830 p_party_info_tab(i).party_type := 'C';
1831 p_party_info_tab(i).party_line_amount := project_source_rec.amount;
1832 p_party_info_tab(i).doc_num := project_source_rec.transaction_number;
1833 ELSE
1834 --For 12.1 and below
1835 SELECT c.cust_account_id, 'C', c.account_name,
1836 DECODE(c.customer_class_code, 'FEDERAL','F',
1837 'GENERAL FUND', 'G',
1838 'NON-FEDERAL EXCEPTION', 'E',
1839 'N') "fed_nonfed_code",
1840 DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1, 'ATTRIBUTE2',
1841 C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3, 'ATTRIBUTE4', C.ATTRIBUTE4,
1842 'ATTRIBUTE5', C.ATTRIBUTE5, 'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7',
1843 C.ATTRIBUTE7, 'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
1844 'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11,
1845 'ATTRIBUTE12', C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13,
1846 'ATTRIBUTE14', C.ATTRIBUTE14, 'ATTRIBUTE15', C.ATTRIBUTE15) "agency_id",
1847 project_source_rec.amount,
1848 project_source_rec.transaction_number
1849 INTO p_party_info_tab(i).party_id,
1850 p_party_info_tab(i).party_type,
1851 p_party_info_tab(i).party_name,
1852 p_party_info_tab(i).fed_nonfed_code ,
1853 p_party_info_tab(i).agency_id ,
1854 p_party_info_tab(i).party_line_amount,
1855 p_party_info_tab(i).doc_num
1856 FROM hz_cust_accounts_all c
1857 WHERE c.cust_account_id = project_source_rec.party_id;
1858 END IF;
1859 ELSE
1860 p_party_info_tab(i).party_line_amount := project_source_rec.amount;
1861 p_party_info_tab(i).doc_num := project_source_rec.transaction_number;
1862 END IF;
1863 log(l_module_name,'party_id: '||p_party_info_tab(i).party_id);
1864 log(l_module_name,'party_type: '||p_party_info_tab(i).fed_nonfed_code);
1865 log(l_module_name,'agency id: '|| p_party_info_tab(i).agency_id);
1866 log(l_module_name,'party_name: '||p_party_info_tab(i).party_name);
1867 log(l_module_name,'cust or vend: '||p_party_info_tab(i).party_type);
1868 log(l_module_name,'line_amount: '||p_party_info_tab(i).party_line_amount);
1869
1870 i := i + 1;
1871 END LOOP;
1872 --Bug14515465/CR
1873 ELSIF (p_je_source = 'Budgetary Transaction' AND
1874 p_je_category <> 'Treasury Confirmation') THEN
1875 FOR be_trx_rec IN be_trx_cursor
1876 LOOP
1877 p_party_info_tab(i).party_id := NULL;
1878 p_party_info_tab(i).party_type := 'B';
1879 p_party_info_tab(i).party_line_amount := be_trx_rec.amount;
1880 p_party_info_tab(i).agency_id := be_trx_rec.dept_id;
1881 p_party_info_tab(i).main_account := be_trx_rec.main_account;
1882 p_party_info_tab(i).doc_num := be_trx_rec.doc_number;
1883
1884 --Bug14515465/CR
1885 --If dept id is null then set fed nonfed code to N
1886 --If dept id and main account are not null
1887 --get fed acct symb title for the party name for bud trxns
1888 --if fed acct symb title not found, then raise exception
1889 IF be_trx_rec.dept_id IS NULL THEN
1890 p_party_info_tab(i).fed_nonfed_code := 'N';
1891
1892 ELSIF (be_trx_rec.dept_id IS NOT NULL AND
1893 be_trx_rec.main_account IS NOT NULL) THEN
1894 BEGIN
1895 SELECT federal_acct_symbol_name, 'F'
1896 INTO p_party_info_tab(i).party_name,
1897 p_party_info_tab(i).fed_nonfed_code
1898 FROM fv_tp_treasury_symbols
1899 WHERE agency_id = be_trx_rec.dept_id
1900 AND main_acct_code = be_trx_rec.main_account
1901 AND rownum = 1;
1902
1903 EXCEPTION WHEN NO_DATA_FOUND THEN
1904 log(l_module_name, 'Fed Acct Sym Name not found for Agency ID: '||
1905 be_trx_rec.dept_id||': Main Acct: '||be_trx_rec.main_account);
1906
1907 insert_exception_rec(
1908 gbl_sob_id, p_fund_value, '**NULL**',NULL,
1909 p_je_source, p_je_category, p_je_header_id,
1910 p_je_line_num, be_trx_rec.amount, 0, 'FED_ACCT_SYM_NOT_FOUND',
1911 be_trx_rec.doc_number, NULL, be_trx_rec.dept_id,
1912 be_trx_rec.main_account);
1913 --Since dept id is not null, set fed nonfed code to F
1914 p_party_info_tab(i).fed_nonfed_code := 'F';
1915 --RETURN;
1916 END;
1917 END IF;
1918
1919 log(l_module_name,'trx id: '||be_trx_rec.transaction_id);
1920 log(l_module_name,'agency_id: '||p_party_info_tab(i).agency_id);
1921 log(l_module_name,'main_account: '||p_party_info_tab(i).main_account);
1922 i := i + 1;
1923 END LOOP;
1924
1925 ELSIF (p_je_source = 'Cost Management' AND
1926 p_je_category = 'Receiving') THEN
1927 FOR cst_rec IN cst_cursor
1928 LOOP
1929 l_rcv_transaction_id := NULL;
1930 BEGIN
1931 SELECT aps.vendor_id, 'S', aps.vendor_name,
1932 NVL(aps.global_attribute4,'N') "fed_nonfed_code",
1933 aps.global_attribute5 "agency_id" ,
1934 cst_rec.amount, cst_rec.rcv_transaction_id
1935 --, cst_rec.doc_num
1936 INTO p_party_info_tab(i).party_id,
1937 p_party_info_tab(i).party_type ,
1938 p_party_info_tab(i).party_name,
1939 p_party_info_tab(i).fed_nonfed_code ,
1940 P_party_info_tab(i).agency_id ,
1941 P_party_info_tab(i).party_line_amount,
1942 l_rcv_transaction_id
1943 --,P_party_info_tab(i).doc_num
1944 FROM --rcv_transactions rt,
1945 --rcv_shipment_headers rcv,
1946 ap_suppliers aps
1947 WHERE aps.vendor_id = cst_rec.vendor_id;
1948 --rt.shipment_header_id = rcv.shipment_header_Id
1949 --AND rt.transaction_id = cst_rec.rcv_transaction_id;
1950 --IF transaction id = 0, it is an accrual and
1951 --there will be no receipts, so use the po num as doc num
1952 --If transaction id <> 0, then get the receipt num
1953 IF l_rcv_transaction_id = 0 THEN
1954 log(l_module_name, 'Accrual, using po num as doc num');
1955 p_party_info_tab(i).doc_num := cst_rec.doc_num;
1956 ELSE
1957
1958 SELECT h.receipt_num
1959 INTO p_party_info_tab(i).doc_num
1960 FROM rcv_shipment_headers h, rcv_transactions t
1961 WHERE t.transaction_id = l_rcv_transaction_id
1962 AND h.shipment_header_id = t.shipment_header_id;
1963 END IF;
1964
1965 EXCEPTION WHEN NO_DATA_FOUND THEN
1966 log(l_module_name, 'Party info not found for Cost Mgmt');
1967
1968 insert_exception_rec(
1969 gbl_sob_id, p_fund_value, '**NULL**',NULL,
1970 p_je_source, p_je_category, p_je_header_id,
1971 p_je_line_num, cst_rec.amount, 0, 'PARTY_INFO_NOT_FOUND',
1972 cst_rec.doc_num, NULL, NULL, NULL);
1973 END;
1974
1975 i := i + 1;
1976 END LOOP;
1977
1978 ELSIF (p_je_source = 'Budgetary Transaction' AND
1979 p_je_category = 'Treasury Confirmation') THEN
1980
1981 FOR trs_confirm_rec IN trs_confirm_cursor LOOP
1982 BEGIN
1983
1984 SELECT v.vendor_id, 'S', v.vendor_name,
1985 v.global_attribute4 "fed_nonfed_code",
1986 v.global_attribute5 "agency_id",
1987 trs_confirm_rec.amount,
1988 apc.checkrun_name
1989 INTO p_party_info_tab(i).party_id,
1990 p_party_info_tab(i).party_type ,
1991 p_party_info_tab(i).party_name,
1992 p_party_info_tab(i).fed_nonfed_code ,
1993 p_party_info_tab(i).agency_id,
1994 p_party_info_tab(i).party_line_amount,
1995 p_party_info_tab(i).doc_num
1996 FROM ap_checks_all apc,
1997 ap_suppliers v
1998 WHERE apc.vendor_id = v.vendor_id
1999 AND apc.check_id = trs_confirm_rec.check_id;
2000 EXCEPTION WHEN NO_DATA_FOUND THEN
2001 log(l_module_name, 'Party info not found for Treas Conf');
2002
2003 insert_exception_rec(
2004 gbl_sob_id, p_fund_value, '**NULL**',NULL,
2005 p_je_source, p_je_category, p_je_header_id,
2006 p_je_line_num, trs_confirm_rec.amount, 0, 'PARTY_INFO_NOT_FOUND',
2007 p_party_info_tab(i).doc_num, NULL, NULL, NULL);
2008 END;
2009
2010
2011 i := i + 1;
2012 END LOOP;
2013
2014 END IF;
2015
2016 fv_utility.log_mesg('End: '||l_module_name);
2017 EXCEPTION WHEN OTHERS THEN
2018 gbl_err_code := 2;
2019 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2020 FV_UTILITY.LOG_MESG(gbl_err_buff);
2021 END GET_PARTY_INFO_R12;
2022 --------------------------------------------------------------------------------
2023 PROCEDURE GET_PARTY_INFO_11I(p_je_source IN VARCHAR2, p_je_category IN VARCHAR2,
2024 p_reference1 IN VARCHAR2,
2025 p_reference2 IN VARCHAR2,
2026 p_reference3 IN VARCHAR2,
2027 p_reference5 IN VARCHAR2,
2028 p_reference7 IN VARCHAR2,
2029 p_party_info_tab OUT NOCOPY party_info_table)
2030 -- p_tp_id OUT NOCOPY NUMBER, p_tp_name OUT NOCOPY VARCHAR2,
2031 -- p_tp_type OUT NOCOPY VARCHAR2,
2032 -- p_tp_fed_nonfed_code OUT NOCOPY VARCHAR2,
2033 -- p_tp_agency_id OUT NOCOPY VARCHAR2)
2034 IS
2035
2036 l_module_name VARCHAR2(200) := g_module_name||'GET_11I_PARTY_INFO';
2037 l_select VARCHAR2(250);
2038 l_col_name all_tab_columns.column_name%TYPE;
2039 l_vendor_id ap_suppliers.vendor_id%TYPE;
2040 i INTEGER := 1;
2041 l_temp_reference_2 gl_je_lines.reference_2%TYPE;
2042
2043 CURSOR projects_vendor_cur (p_ref1 IN VARCHAR2) IS
2044 SELECT e.vendor_id ven_id
2045 FROM gl_je_lines je ,
2046 gl_je_headers jh ,
2047 pa_cost_distribution_lines_all cdl,
2048 pa_expenditure_items_all ei ,
2049 pa_expenditures_all e
2050 WHERE je.je_header_id = jh.je_header_id
2051 AND je.reference_1 IS NOT NULL
2052 AND e.vendor_id IS NOT NULL
2053 AND je.reference_1 = p_ref1
2054 AND je.reference_1 = cdl.batch_name
2055 AND cdl.expenditure_item_id = ei.expenditure_item_id
2056 AND ei.expenditure_id = e.expenditure_id;
2057
2058 BEGIN
2059 fv_utility.log_mesg('Begin: '||l_module_name);
2060 IF (p_je_source = 'Payables' AND
2061 p_je_category <> 'Treasury Confirmation') THEN
2062 SELECT v.vendor_id, 'S', v.vendor_name,
2063 v.global_attribute4 "fed_nonfed_code",
2064 v.global_attribute5 "agency_id",
2065 i.invoice_num
2066 INTO p_party_info_tab(i).party_id,
2067 p_party_info_tab(i).party_type ,
2068 p_party_info_tab(i).party_name,
2069 p_party_info_tab(i).fed_nonfed_code ,
2070 p_party_info_tab(i).agency_id,
2071 p_party_info_tab(i).doc_num
2072 FROM ap_invoices_all i,
2073 ap_suppliers v
2074 WHERE i.invoice_id = to_number(p_reference2)
2075 AND i.vendor_id = v.vendor_id;
2076
2077 ELSIF (p_je_source = 'Purchasing' AND
2078 p_je_category IN ('Receiving','Inventory')) THEN
2079 SELECT v.vendor_id, 'S', v.vendor_name,
2080 v.global_attribute4 "fed_nonfed_code" ,
2081 v.global_attribute5 "agency_id",
2082 ph.segment1
2083 INTO p_party_info_tab(i).party_id,
2084 p_party_info_tab(i).party_type ,
2085 p_party_info_tab(i).party_name,
2086 p_party_info_tab(i).fed_nonfed_code ,
2087 p_party_info_tab(i).agency_id,
2088 p_party_info_tab(i).doc_num
2089 FROM rcv_transactions rt,
2090 ap_suppliers v ,
2091 po_headers_all ph
2092 WHERE rt.po_header_id = to_number(p_reference2)
2093 AND rt.transaction_id = to_number(p_reference5)
2094 AND rt.po_header_id = ph.po_header_id
2095 AND v.vendor_id = ph.vendor_id;
2096
2097 ELSIF (p_je_source = 'Purchasing' AND
2098 p_je_category IN ('Purchases', 'Release')) THEN
2099 SELECT v.vendor_id, 'S', v.vendor_name,
2100 v.global_attribute4 "fed_nonfed_code",
2101 v.global_attribute5 "agency_id",
2102 poh.segment1
2103 INTO p_party_info_tab(i).party_id,
2104 p_party_info_tab(i).party_type ,
2105 p_party_info_tab(i).party_name,
2106 p_party_info_tab(i).fed_nonfed_code ,
2107 p_party_info_tab(i).agency_id,
2108 p_party_info_tab(i).doc_num
2109 FROM ap_suppliers v,
2110 po_headers_all poh
2111 WHERE poh.po_header_id = to_number(p_reference2)
2112 AND v.vendor_id = poh.vendor_id;
2113
2114 ELSIF p_je_source = 'Receivables' THEN
2115 --For 12.2 and above we have to use the following columns
2116 --hz_cust_accounts.federal_entity_type, trading_partner_agency_id
2117 --For 12.1 and below we have to use class code and dffs.
2118 --For 12.2 and above
2119 IF gbl_cust_col_exists = 'Y' THEN
2120 l_select := ' SELECT cust_account_id, ''S'', account_name,
2121 federal_entity_type, trading_partner_agency_id
2122 FROM hz_cust_accunts
2123 WEHRE cust_account_id = :reference ';
2124
2125 EXECUTE IMMEDIATE l_select
2126 INTO p_party_info_tab(i).party_id,
2127 p_party_info_tab(i).party_type ,
2128 p_party_info_tab(i).party_name,
2129 p_party_info_tab(i).fed_nonfed_code ,
2130 p_party_info_tab(i).agency_id
2131 USING p_reference7;
2132
2133 ELSE
2134 --For 12.1 and below
2135 SELECT c.cust_account_id, 'C', c.account_name,
2136 DECODE(c.customer_class_code, 'FEDERAL','F',
2137 'GENERAL FUND', 'G',
2138 'NON-FEDERAL EXCEPTION', 'E',
2139 'N') "fed_nonfed_code",
2140 DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1, 'ATTRIBUTE2',
2141 C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3, 'ATTRIBUTE4', C.ATTRIBUTE4,
2142 'ATTRIBUTE5', C.ATTRIBUTE5, 'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7',
2143 C.ATTRIBUTE7, 'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
2144 'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11, 'ATTRIBUTE12',
2145 C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13, 'ATTRIBUTE14', C.ATTRIBUTE14,
2146 'ATTRIBUTE15', C.ATTRIBUTE15) "agency_id"
2147 INTO p_party_info_tab(i).party_id,
2148 p_party_info_tab(i).party_type ,
2149 p_party_info_tab(i).party_name,
2150 p_party_info_tab(i).fed_nonfed_code ,
2151 p_party_info_tab(i).agency_id
2152 FROM hz_cust_accounts_all c
2153 WHERE c.cust_account_id = to_number(p_reference7);
2154 END IF;
2155 --Get the receivables doc num
2156 IF UPPER(p_je_category) like '%RECEIPTS%' THEN
2157
2158 SELECT SUBSTR(p_reference2, 0, decode(INSTR(p_reference2, 'C'), 0,
2159 LENGTH(p_reference2),INSTR(p_reference2,'C')-1))
2160 INTO l_temp_reference_2
2161 FROM dual;
2162 log(l_module_name, 'l_temp_reference_2: '||l_temp_reference_2);
2163
2164 SELECT receipt_number
2165 INTO p_party_info_tab(i).doc_num
2166 FROM ar_cash_receipts_all
2167 WHERE cash_receipt_id = to_number(l_temp_reference_2);
2168 ELSE
2169 --This might be an AR invoice
2170 SELECT trx_number
2171 INTO p_party_info_tab(i).doc_num
2172 FROM ra_customer_trx_all
2173 WHERE customer_trx_id = to_number(p_reference2);
2174 END IF;
2175
2176 ELSIF (p_je_source = 'Payables' AND
2177 p_je_category = 'Treasury Confirmation') THEN
2178
2179 SELECT v.vendor_id, 'S', v.vendor_name,
2180 v.global_attribute4 "fed_nonfed_code",
2181 v.global_attribute5 "agency_id",
2182 apc.checkrun_name
2183 INTO p_party_info_tab(i).party_id,
2184 p_party_info_tab(i).party_type ,
2185 p_party_info_tab(i).party_name,
2186 p_party_info_tab(i).fed_nonfed_code ,
2187 p_party_info_tab(i).agency_id,
2188 p_party_info_tab(i).doc_num
2189 FROM ap_checks_all apc,
2190 ap_suppliers v
2191 WHERE apc.vendor_id = v.vendor_id
2192 AND apc.check_id = to_number(p_reference3);
2193 END IF;
2194 fv_utility.log_mesg('End: '||l_module_name);
2195 EXCEPTION WHEN OTHERS THEN
2196 gbl_err_code := 2;
2197 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2198 FV_UTILITY.LOG_MESG(gbl_err_buff);
2199 END GET_PARTY_INFO_11I;
2200 --------------------------------------------------------------------------------
2201 PROCEDURE GET_SYSTEM_ATTRIBUTES IS
2202 l_module_name VARCHAR2(200) := g_module_name||'GET_SYSTEM_ATTRIBUTES';
2203 l_ar_schema fnd_application.application_short_name%TYPE;
2204 BEGIN
2205 log(l_module_name, 'Begin: '||l_module_name);
2206
2207 --For 12.2 and above we have to use columns
2208 --hz_cust_accounts.federal_entity_type, trading_partner_agency_id
2209 --For 12.1 and below we have to use class code and dffs.
2210 --Check if the new columns exist, if not use the dffs.
2211 BEGIN
2212
2213 SELECT application_short_name
2214 INTO l_ar_schema
2215 FROM fnd_application
2216 WHERE application_id = 222;
2217
2218 SELECT 'Y'
2219 INTO gbl_cust_col_exists
2220 FROM all_tab_columns
2221 WHERE table_name = 'HZ_CUST_ACCOUNTS'
2222 AND owner = l_ar_schema
2223 AND column_name = 'FEDERAL_ENTITY_TYPE';
2224 log(l_module_name, 'gbl_cust_cols_available: '||gbl_cust_col_exists);
2225
2226 EXCEPTION WHEN NO_DATA_FOUND THEN
2227 log(l_module_name, 'gbl_cust_cols_available: '||gbl_cust_col_exists);
2228
2229 SELECT gtas_customer_attribute
2230 INTO gbl_cust_attribute
2231 FROM fv_system_parameters;
2232 log(l_module_name, 'gtas_customer_attribute: '||gbl_cust_attribute);
2233
2234 IF gbl_cust_attribute IS NULL THEN
2235 RAISE NO_DATA_FOUND;
2236 END IF;
2237 END;
2238
2239 log(l_module_name, 'End: '||l_module_name);
2240 EXCEPTION
2241 WHEN NO_DATA_FOUND THEN
2242 gbl_err_code := 2;
2243 gbl_err_buff := 'Please select GTAS Customer Trading Partner Attribute in the '||
2244 'Federal System Parameters form.';
2245 log(l_module_name, gbl_err_buff);
2246 WHEN OTHERS THEN
2247 gbl_err_code := 2;
2248 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2249 FV_UTILITY.LOG_MESG(gbl_err_buff);
2250 END GET_SYSTEM_ATTRIBUTES;
2251 --------------------------------------------------------------------------------
2252 PROCEDURE GET_BUDGET_IMPACT_IND(p_je_batch_id IN NUMBER,
2253 p_je_header_id IN NUMBER,
2254 p_je_line_num IN NUMBER,
2255 p_account_number IN VARCHAR2,
2256 p_sgl_account_number IN VARCHAR2,
2257 p_fund_value IN VARCHAR2,
2258 p_je_source IN VARCHAR2,
2259 p_je_category IN VARCHAR2,
2260 p_ccid IN NUMBER, p_amount NUMBER,
2261 p_doc_num IN VARCHAR2,
2262 p_party_name IN VARCHAR2,
2263 p_agency_id IN VARCHAR2,
2264 p_main_account IN VARCHAR2,
2265 p_budget_impact_indicator OUT NOCOPY VARCHAR2)
2266 IS
2267 l_module_name VARCHAR2(200) := g_module_name||'GET_BUDGET_IMPACT_IND';
2268 --l_budget_impact_indicator VARCHAR2(1);
2269 l_account_type VARCHAR2(1);
2270 l_bud_imp_count NUMBER := 0;
2271 l_account_number fv_gtas_attributes.gtas_acct_number%TYPE;
2272 TYPE acct_type_cur IS REF CURSOR;
2273 l_acct_type_cur acct_type_cur;
2274
2275 l_acct_select VARCHAR2(1000);
2276 l_sla_event_id NUMBER;
2277 CURSOR event_lines_cur IS
2278 SELECT event_id
2279 INTO l_sla_event_id
2280 FROM gl_import_references gli,
2281 xla_ae_lines l,
2282 xla_ae_headers h
2283 WHERE gli.je_batch_id = p_je_batch_id
2284 AND gli.je_header_id = p_je_header_id
2285 AND gli.je_line_num = p_je_line_num
2286 AND l.gl_sl_link_id = gli.gl_sl_link_id
2287 AND h.ae_header_id = l.ae_header_id;
2288
2289 BEGIN
2290 log(l_module_name, 'Begin: '||l_module_name);
2291 --log(l_module_name, 'p_account_number: '||p_account_number);
2292 --log(l_module_name, 'p_sgl_account_number: '||p_sgl_account_number);
2293
2294 --Get account type of the account. If account is budgetary
2295 --Dr or Cr then return D else E
2296 --Modified for change request 11
2297 --If account type in D or C and bud impact id includes D then
2298 --return D else raise exception
2299 --If account type is NOT D or C and bud impact id includes E then
2300 --return E else raise exception
2301 --Change request 11 modified again
2302 --If any of the journal lines in this event is a budgetary account
2303 --then this logic applies
2304 /*
2305 SELECT event_id
2306 INTO l_sla_event_id
2307 FROM gl_import_references gli,
2308 xla_ae_lines l,
2309 xla_ae_headers h
2310 WHERE gli.je_batch_id = p_je_batch_id
2311 AND gli.je_header_id = p_je_header_id
2312 AND gli.je_line_num = p_je_line_num;
2313 AND l.gl_sl_link_id = gli.gl_sl_link_id
2314 AND h.ae_header_id = l.ae_header_id;
2315 */
2316 FOR sla_event in event_lines_cur LOOP
2317
2318 l_acct_select := 'SELECT SUBSTR(ffv.compiled_value_attributes,5,1)
2319 FROM xla_ae_headers xh,
2320 xla_ae_lines xl,
2321 gl_code_combinations gcc,
2322 fnd_flex_values ffv
2323 WHERE xh.event_id = '||sla_event.event_id||
2324 ' AND xh.ae_header_id = xl.ae_header_id
2325 AND gcc.code_combination_id = xl.code_combination_id
2326 AND gcc.'||gbl_acc_segment||' = ffv.flex_value
2327 AND ffv.flex_value_set_id = '||gbl_acc_value_set_id
2328 ;
2329
2330 OPEN l_acct_type_cur FOR l_acct_select;
2331 LOOP
2332 FETCH l_acct_type_cur INTO l_account_type;
2333 log(l_module_name, 'l_account_type: '||l_account_type);
2334 EXIT WHEN l_acct_type_cur%NOTFOUND;
2335 --If any budetary dr or cr account then exit
2336 IF l_account_type IN ('D','C') THEN
2337 EXIT;
2338 END IF;
2339 END LOOP;
2340 --If any budetary dr or cr account then exit second loop
2341 IF l_account_type IN ('D','C') THEN
2342 EXIT;
2343 END IF;
2344 END LOOP;
2345
2346 --get_account_type(l_temp_acct_num, l_account_type);
2347 --get_account_type(p_account_number, l_account_type);
2348
2349 --Check if the account number exists in fv gtas attributes table.
2350 --If it does not, then use the ussgl account for getting/checking
2351 --bud impact ind
2352 GET_USSGL_ACCT(p_account_number, p_sgl_account_number, l_account_number);
2353
2354 IF l_account_type IN ('D','C') THEN
2355 SELECT COUNT(*)
2356 INTO l_bud_imp_count
2357 FROM fv_gtas_attributes
2358 WHERE gtas_acct_number = l_account_number--p_account_number
2359 AND (NVL(bud_impact_ind1,'-X') = 'D'
2360 OR NVL(bud_impact_ind2,'-X') = 'D');
2361
2362 IF l_bud_imp_count > 0 THEN
2363 p_budget_impact_indicator := 'D';
2364 ELSE
2365 log(l_module_name,'Budget impact ind not found with D'||
2366 ' inserting exception');
2367 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2368 p_sgl_account_number, p_je_source,
2369 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_BUD_IMPACT_IND',
2370 p_doc_num, p_party_name, p_agency_id, p_main_account
2371 );
2372 END IF;
2373 ELSE
2374 SELECT COUNT(*)
2375 INTO l_bud_imp_count
2376 FROM fv_gtas_attributes
2377 WHERE gtas_acct_number = l_account_number--p_account_number
2378 AND (NVL(bud_impact_ind1,'-X') = 'E'
2379 OR NVL(bud_impact_ind2,'-X') = 'E');
2380
2381 IF l_bud_imp_count > 0 THEN
2382 p_budget_impact_indicator := 'E';
2383 ELSE
2384 log(l_module_name,'Budget impact ind not found with E'||
2385 ' inserting exception');
2386 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2387 p_sgl_account_number, p_je_source,
2388 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_BUD_IMPACT_IND',
2389 p_doc_num, p_party_name, p_agency_id, p_main_account
2390 );
2391 END IF;
2392
2393 END IF;
2394 log(l_module_name, 'p_budget_impact_indicator: '||p_budget_impact_indicator);
2395
2396 log(l_module_name, 'End: '||l_module_name);
2397 EXCEPTION
2398 WHEN NO_DATA_FOUND THEN
2399 gbl_err_code := 2;
2400 gbl_err_buff := 'No Data Found error in: '||l_module_name||':'||SQLERRM;
2401 log(l_module_name, gbl_err_buff);
2402 WHEN OTHERS THEN
2403 gbl_err_code := 2;
2404 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2405 FV_UTILITY.LOG_MESG(gbl_err_buff);
2406 END GET_BUDGET_IMPACT_IND;
2407 --------------------------------------------------------------------------------
2408 --Change request has been changed multiple times
2409 --This change is based on bug14255268.
2410 --Need to update these comments if it changes again
2411 --If gtas attribute equals G and only G then return fed nonfed code of G.
2412 --If gtas attribute equals N and only n then return fed nonfed code of N.
2413 --Else get fed nonfed code based on customer/supplier fed nonfed code.
2414 --If cust/supp fed nonfed code is:
2415 --F or N and gtas attributes includes F or N, return F or N respectively
2416 --E and gtas attributes includes E and non-fed excptn checkbox on TAS is Y,
2417 --return E
2418 --E and gtas attributes includes N and non-fed excptn checkbox on TAS
2419 --is N, return N
2420 --If any of the above fails, then create an exception
2421 PROCEDURE GET_FED_NONFED_CODE(p_fund_value IN VARCHAR2,
2422 p_account_number IN VARCHAR2,
2423 p_sgl_account_number IN VARCHAR2,
2424 p_party_fed_nonfed_code IN VARCHAR2,
2425 p_je_source IN VARCHAR2,
2426 p_je_category IN VARCHAR2,
2427 p_ccid IN NUMBER, p_amount NUMBER,
2428 p_doc_num IN VARCHAR2,
2429 p_party_name IN VARCHAR2,
2430 p_agency_id IN VARCHAR2,
2431 p_main_account IN VARCHAR2,
2432 p_fed_nonfed_code OUT NOCOPY VARCHAR2)
2433 IS
2434 l_module_name VARCHAR2(200) := g_module_name||'GET_FED_NONFED_CODE';
2435 --l_fund_type fv_treasury_symbols.fund_type%TYPE;
2436 --l_fed_nonfed_code_exists VARCHAR2(1);
2437 l_non_fed_exc_flag fv_facts_federal_accounts.non_fed_exc_flag%TYPE;
2438 --l_dummy NUMBER;
2439 --l_account_type VARCHAR2(1);
2440 l_code_exists VARCHAR2(1);
2441 l_account_number fv_gtas_attributes.gtas_acct_number%TYPE;
2442 l_fednonfed_count NUMBER := 0;
2443 BEGIN
2444 log(l_module_name, 'Begin: '||l_module_name);
2445
2446 log(l_module_name,' p_fund_value: '||p_fund_value);
2447 log(l_module_name,' p_account_number: '||p_account_number);
2448 log(l_module_name,' p_sgl_account_number: '||p_sgl_account_number);
2449 log(l_module_name,' p_party_fed_nonfed_code: '||p_party_fed_nonfed_code);
2450
2451 --Check if the account number exists in fv gtas attributes table.
2452 --If it does not, then use the ussgl account for getting/checking
2453 --fed non fed code.
2454 GET_USSGL_ACCT(p_account_number, p_sgl_account_number, l_account_number);
2455
2456 --Changed request 12 has been changed multiple times.
2457 --Modifying again according to bug 14255268
2458 --IF G is the only value in gtas attributes, return G
2459 check_fed_nonfed_code(l_account_number, 'G', p_je_source, l_code_exists);
2460 IF l_code_exists = 'Y' THEN
2461 SELECT COUNT(*)
2462 INTO l_fednonfed_count
2463 FROM (
2464 SELECT fed_non_fed1
2465 FROM fv_gtas_attributes
2466 WHERE gtas_acct_number = l_account_number
2467 AND fed_non_fed1 IS NOT NULL
2468 AND set_of_books_id = gbl_sob_id
2469 UNION
2470 SELECT fed_non_fed2
2471 FROM fv_gtas_attributes
2472 WHERE gtas_acct_number = l_account_number
2473 AND fed_non_fed2 IS NOT NULL
2474 AND set_of_books_id = gbl_sob_id
2475 UNION
2476 SELECT fed_non_fed3
2477 FROM fv_gtas_attributes
2478 WHERE gtas_acct_number = l_account_number
2479 AND fed_non_fed3 IS NOT NULL
2480 AND set_of_books_id = gbl_sob_id);
2481
2482 IF l_fednonfed_count = 1 THEN
2483 log(l_module_name,'Returning fed nonfed code: G');
2484 p_fed_nonfed_code := 'G';
2485 RETURN;
2486 END IF;
2487 END IF;
2488
2489 --IF N is the only value in gtas attributes, return N
2490 check_fed_nonfed_code(l_account_number, 'N', p_je_source, l_code_exists);
2491 IF l_code_exists = 'Y' THEN
2492 SELECT COUNT(*)
2493 INTO l_fednonfed_count
2494 FROM (
2495 SELECT fed_non_fed1
2496 FROM fv_gtas_attributes
2497 WHERE gtas_acct_number = l_account_number
2498 AND fed_non_fed1 IS NOT NULL
2499 AND set_of_books_id = gbl_sob_id
2500 UNION
2501 SELECT fed_non_fed2
2502 FROM fv_gtas_attributes
2503 WHERE gtas_acct_number = l_account_number
2504 AND fed_non_fed2 IS NOT NULL
2505 AND set_of_books_id = gbl_sob_id
2506 UNION
2507 SELECT fed_non_fed3
2508 FROM fv_gtas_attributes
2509 WHERE gtas_acct_number = l_account_number
2510 AND fed_non_fed3 IS NOT NULL
2511 AND set_of_books_id = gbl_sob_id);
2512
2513 IF l_fednonfed_count = 1 THEN
2514 log(l_module_name,'Returning fed nonfed code: N');
2515 p_fed_nonfed_code := 'N';
2516 RETURN;
2517 END IF;
2518 END IF;
2519
2520 --Change request 12 changed again
2521 --If code is not found above,
2522 --use the customer/supplier fed nonfed code to get the correct value
2523 --for fed nonfed code
2524 IF p_party_fed_nonfed_code = 'G' THEN
2525
2526 check_fed_nonfed_code(l_account_number, 'G', p_je_source, l_code_exists);
2527 IF l_code_exists = 'Y' THEN
2528 log(l_module_name,'Returning fed nonfed code: G');
2529 p_fed_nonfed_code := 'G';
2530
2531 /* removed for change request 12 changed again
2532 --If G does not exist, then check for F
2533 check_fed_nonfed_code(l_account_number, 'F', p_je_source, l_code_exists);
2534 IF l_code_exists = 'Y' THEN
2535 log(l_module_name,'Returning fed nonfed code: F');
2536 p_fed_nonfed_code := 'F';
2537 RETURN;
2538 END IF;
2539 */
2540 --IF G is not found, then insert exception and return
2541 ELSE
2542 log(l_module_name,'F or G not found for account with party code of G, '||
2543 ' inserting exception');
2544 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2545 p_sgl_account_number, p_je_source,
2546 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
2547 p_doc_num, p_party_name, p_agency_id, p_main_account);
2548 END IF;
2549 ELSIF p_party_fed_nonfed_code = 'F' THEN
2550 check_fed_nonfed_code(l_account_number, 'F', p_je_source, l_code_exists);
2551 IF l_code_exists = 'Y' THEN
2552 log(l_module_name,'Returning fed nonfed code: F');
2553 p_fed_nonfed_code := 'F';
2554 ELSE
2555 --IF F is not found, then insert exception and return
2556 log(l_module_name,'F not found for account with party code of F, '||
2557 ' inserting exception');
2558 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2559 p_sgl_account_number, p_je_source,
2560 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
2561 p_doc_num, p_party_name, p_agency_id, p_main_account);
2562 END IF;
2563
2564 ELSIF p_party_fed_nonfed_code = 'N' THEN
2565 check_fed_nonfed_code(l_account_number, 'N', p_je_source, l_code_exists);
2566 IF l_code_exists = 'Y' THEN
2567 log(l_module_name,'Returning fed nonfed code: N');
2568 p_fed_nonfed_code := 'N';
2569 ELSE
2570 --IF n is not found, then insert exception and return
2571 log(l_module_name,'N not found for account with party code of N, '||
2572 ' inserting exception');
2573 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2574 p_sgl_account_number, p_je_source,
2575 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
2576 p_doc_num, p_party_name, p_agency_id, p_main_account);
2577 END IF;
2578 ELSIF p_party_fed_nonfed_code = 'E' THEN
2579 --change request 12 change again
2580 --If nonfederal exception is checked and gtas attribute incldes E return E
2581 --else error
2582 SELECT NVL(ffa.non_fed_exc_flag,'N')
2583 INTO l_non_fed_exc_flag
2584 FROM fv_facts_federal_accounts ffa,
2585 fv_treasury_symbols fts,
2586 fv_fund_parameters ffp
2587 WHERE ffp.set_of_books_id = gbl_sob_id
2588 AND ffp.fund_value = p_fund_value
2589 AND ffp.treasury_symbol_id = fts.treasury_symbol_id
2590 AND fts.federal_acct_symbol_id = ffa.federal_acct_symbol_id;
2591
2592 --Change order changed again bug 14255268
2593 --IF TAS is checked and attribute inclues E, return E
2594 --if it does not include E, check if it includes N and return N
2595 IF l_non_fed_exc_flag = 'Y' THEN
2596 check_fed_nonfed_code(l_account_number, 'E', p_je_source, l_code_exists);
2597 IF l_code_exists = 'Y' THEN
2598 log(l_module_name,'Returning fed nonfed code: E');
2599 p_fed_nonfed_code := 'E';
2600 ELSE
2601 --If E does not exist, check if N exists, else error
2602 log(l_module_name, 'TAS Checkbox checked, E not included, checking N');
2603 check_fed_nonfed_code(l_account_number, 'N', p_je_source, l_code_exists);
2604 IF l_code_exists = 'Y' THEN
2605 log(l_module_name,'Returning fed nonfed code: N');
2606 p_fed_nonfed_code := 'N';
2607 ELSE
2608 log(l_module_name,'exception flag checked but attribute does not include E or N,'||
2609 ' inserting exception');
2610 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2611 p_sgl_account_number, p_je_source,
2612 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE_TAS',
2613 p_doc_num, p_party_name, p_agency_id, p_main_account);
2614 END IF;
2615 END IF;
2616 ELSE
2617 --checkbox is not checked, if E OR N is included then return N,
2618 --if not, then error
2619 log(l_module_name, 'TAS Checkbox not checked, checking N');
2620 check_fed_nonfed_code(l_account_number, 'N', p_je_source, l_code_exists);
2621 IF l_code_exists = 'Y' THEN
2622 log(l_module_name,'Returning fed nonfed code: N');
2623 p_fed_nonfed_code := 'N';
2624 ELSE
2625 log(l_module_name,'Exception flag unchecked but attribute does not include N,'||
2626 ' inserting exception');
2627 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2628 p_sgl_account_number, p_je_source,
2629 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE_TAS',
2630 p_doc_num, p_party_name, p_agency_id, p_main_account);
2631 END IF;
2632 END IF;
2633 ELSE
2634 log(l_module_name,'Fed nonfed not found inserting exception');
2635 insert_exception_rec(gbl_sob_id, p_fund_value, p_account_number,
2636 p_sgl_account_number, p_je_source,
2637 p_je_category, NULL, NULL, p_amount, p_ccid, 'INVALID_FED_NONFED_CODE',
2638 p_doc_num, p_party_name, p_agency_id, p_main_account);
2639 END IF;
2640
2641
2642 log(l_module_name, 'End: '||l_module_name);
2643 EXCEPTION
2644 WHEN NO_DATA_FOUND THEN
2645 gbl_err_code := 2;
2646 gbl_err_buff := 'No Data Found error in: '||l_module_name||':'||SQLERRM;
2647 log(l_module_name, gbl_err_buff);
2648 WHEN OTHERS THEN
2649 gbl_err_code := 2;
2650 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2651 FV_UTILITY.LOG_MESG(gbl_err_buff);
2652 END GET_FED_NONFED_CODE;
2653 --------------------------------------------------------------------------------
2654 PROCEDURE GET_ACCOUNT_TYPE(p_account_number IN VARCHAR2,
2655 p_account_type OUT NOCOPY VARCHAR2)
2656 IS
2657 l_module_name VARCHAR2(200) := g_module_name||'GET_ACCOUNT_TYPE';
2658
2659 BEGIN
2660 log(l_module_name, 'Begin: '||l_module_name);
2661 --log(l_module_name, 'p_account_number: '||p_account_number);
2662
2663 SELECT SUBSTR(compiled_value_attributes,5,1)
2664 INTO p_account_type
2665 FROM fnd_flex_values
2666 WHERE flex_value = p_account_number
2667 AND flex_value_set_id = gbl_acc_value_set_id;
2668
2669 log(l_module_name, 'p_account_type: '||p_account_type);
2670
2671
2672 log(l_module_name, 'End: '||l_module_name);
2673 EXCEPTION
2674 WHEN NO_DATA_FOUND THEN
2675 gbl_err_code := 2;
2676 gbl_err_buff := 'No Data Found error in: '||l_module_name||':'||SQLERRM;
2677 log(l_module_name, gbl_err_buff);
2678 WHEN OTHERS THEN
2679 gbl_err_code := 2;
2680 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2681 FV_UTILITY.LOG_MESG(gbl_err_buff);
2682 END GET_ACCOUNT_TYPE;
2683 --------------------------------------------------------------------------------
2684 PROCEDURE SUBMIT_EXCEPTION_REPORT
2685 IS
2686 l_req_id NUMBER(15);
2687 call_status BOOLEAN;
2688 rphase VARCHAR2(80);
2689 rstatus VARCHAR2(80);
2690 dphase VARCHAR2(80);
2691 dstatus VARCHAR2(80);
2692 MESSAGE VARCHAR2(80);
2693 l_module_name VARCHAR2(80) ;
2694 l_run_mode VARCHAR2(80) ;
2695 lb_layout BOOLEAN;
2696 lc_language VARCHAR2(80);
2697 lc_territory VARCHAR2(80);
2698 BEGIN
2699 l_module_name := g_module_name||'SUBMIT_EXCEPTION_REPORT';
2700 log(l_module_name, 'Begin '||l_module_name);
2701 --l_run_mode := 'Fiscal Year';
2702 log(l_module_name, ' Launching GTAS Activity Bal exception report ...');
2703 log(l_module_name, ' gbl_period_year: '||gbl_period_year);
2704 log(l_module_name, ' gbl_sob_id: '||gbl_sob_id);
2705 log(l_module_name, ' gbl_period_name: '||gbl_period_name);
2706
2707 BEGIN
2708 SELECT LOWER(fnl.iso_language), fnl.iso_territory
2709 INTO lc_language, lc_territory
2710 FROM fnd_languages fnl
2711 WHERE fnl.language_code = USERENV ('LANG');
2712 EXCEPTION
2713 WHEN NO_DATA_FOUND
2714 THEN
2715 NULL;
2716 END;
2717
2718 --ADD_LAYOUT needs to be initialize to get the out put in the corresponding Format
2719
2720 lb_layout:= fnd_request.add_layout('FV','FVGTACTBALEXC',lc_language,lc_territory,'PDF');
2721
2722 l_req_id := FND_REQUEST.SUBMIT_REQUEST ('FV','FVGTACTBALEXC','','',FALSE,gbl_sob_id,gbl_period_name);
2723 -- If concurrent request submission failed, abort process
2724 log(l_module_name, ' Request ID for exception report = '|| TO_CHAR(L_REQ_ID));
2725 IF (l_req_id = 0) THEN
2726 gbl_err_code := '2';
2727 gbl_err_buff := 'Cannot submit GTAS Activity Bal exception report';
2728 log(l_module_name,gbl_err_buff);
2729 RETURN;
2730 ELSE
2731 COMMIT;
2732 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0, rphase, rstatus, dphase, dstatus, MESSAGE);
2733 IF call_status = FALSE THEN
2734 gbl_err_buff := 'Cannot wait for the status of GTAS Activity Bal exception report';
2735 gbl_err_code := -1;
2736 log(l_module_name, gbl_err_buff) ;
2737 RETURN;
2738 END IF;
2739 END IF;
2740
2741 log(l_module_name, 'End: '||l_module_name);
2742 END SUBMIT_EXCEPTION_REPORT;
2743 --------------------------------------------------------------------------------
2744 PROCEDURE UPDATE_GTAS_RUN
2745 IS
2746 l_module_name VARCHAR2(80) ;
2747 BEGIN
2748 l_module_name := g_module_name||'UPDATE_GTAS_RUN';
2749 log(l_module_name, 'Begin '||l_module_name);
2750
2751 INSERT INTO fv_gtas_processed_je_hdrs
2752 (je_header_id,set_of_books_id)
2753 SELECT DISTINCT je_header_id,set_of_books_id FROM fv_gtas_header_id_gt;
2754
2755 log(l_module_name,'Inserted in fv_gtas_processed_je_hdrs: '||SQL%ROWCOUNT);
2756 log(l_module_name,'Updating GTAS run ' );
2757
2758 UPDATE fv_gtas_run
2759 SET process_date = SYSDATE,
2760 jc_run_month = gbl_period_num_high,
2761 run_fed_flag = 'J'
2762 WHERE set_of_books_id = gbl_sob_id
2763 AND fiscal_year = gbl_period_year;
2764 --Update the fv_gtas_fed_accounts as processed
2765 log(l_module_name, 'Updating fv_gtas_fed_accounts ' );
2766 UPDATE fv_gtas_fed_accounts
2767 SET jc_flag = 'Y'
2768 WHERE set_of_books_id = gbl_sob_id
2769 AND fiscal_year = gbl_period_year;
2770
2771 log(l_module_name, 'End: '||l_module_name);
2772
2773 EXCEPTION
2774 WHEN NO_DATA_FOUND THEN
2775 NULL;
2776 WHEN OTHERS THEN
2777 gbl_err_code := 2;
2778 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2779 FV_UTILITY.LOG_MESG(gbl_err_buff);
2780 END UPDATE_GTAS_RUN;
2781 --------------------------------------------------------------------------------
2782 --Modified for change request 12
2783 --Checks whether a passed value exists in fv_gtas_attributes.fed_non_fed1
2784 --2 or 3 columns
2785 PROCEDURE CHECK_FED_NONFED_CODE(p_account_number IN VARCHAR2,
2786 p_fed_nonfed_type IN VARCHAR2,
2787 p_je_source IN VARCHAR2,
2788 p_code_exists OUT NOCOPY VARCHAR2)
2789 IS
2790 l_module_name VARCHAR2(80) := g_module_name||'CHECK_FED_NONFED_CODE';
2791 l_fednonfed_code fv_gtas_attributes.fed_non_fed1%TYPE;
2792 l_fednonfed_code_tmp fv_gtas_attributes.fed_non_fed1%TYPE;
2793 l_fednonfed_select VARCHAR2(200);
2794 l_fednonfed_code_count NUMBER;
2795 BEGIN
2796
2797 log(l_module_name, 'Begin');
2798 log(l_module_name, 'p_account_number: '||p_account_number);
2799
2800 SELECT 'Y'
2801 INTO p_code_exists
2802 FROM fv_gtas_attributes
2803 WHERE gtas_acct_number = p_account_number
2804 AND set_of_books_id = gbl_sob_id
2805 AND (fed_non_fed1 = p_fed_nonfed_type OR
2806 fed_non_fed2 = p_fed_nonfed_type OR
2807 fed_non_fed3 = p_fed_nonfed_type );
2808
2809 log(l_module_name,'p_code_exists: '||p_code_exists);
2810 log(l_module_name, 'End');
2811 EXCEPTION
2812 WHEN NO_DATA_FOUND THEN
2813 p_code_exists := 'N';
2814 log(l_module_name,'p_code_exists: '||p_code_exists);
2815 WHEN OTHERS THEN
2816 gbl_err_code := 2;
2817 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2818 FV_UTILITY.LOG_MESG(gbl_err_buff);
2819 END CHECK_FED_NONFED_CODE;
2820 --------------------------------------------------------------------------------
2821 PROCEDURE DELETE_PRIOR_GTAS_ACTIVITY
2822 IS
2823 l_module_name VARCHAR2(80) := g_module_name||'DELETE_PRIOR_GTAS_ACTIVITY';
2824 BEGIN
2825 log(l_module_name, 'Begin '||l_module_name);
2826 BEGIN
2827 log(l_module_name, 'Deleting from fv_gtas_activity_balances');
2828 DELETE FROM fv_gtas_activity_balances
2829 WHERE period_year = gbl_period_year
2830 AND set_of_books_id = gbl_sob_id;
2831 log(l_module_name, 'Deleted : '||sql%rowcount||' row/s.');
2832 EXCEPTION WHEN NO_DATA_FOUND THEN
2833 log(l_module_name, 'No rows found to dete from fv_gtas_activity_balances.');
2834 END;
2835
2836 BEGIN
2837 log(l_module_name, 'Deleting from fv_gtas_processed_je_hdrs');
2838 DELETE fv_gtas_processed_je_hdrs
2839 WHERE set_of_books_id = gbl_sob_id
2840 AND je_header_id IN (SELECT je_header_id
2841 FROM gl_je_headers
2842 WHERE ledger_id = gbl_sob_id
2843 AND period_name IN
2844 (SELECT period_name
2845 FROM gl_period_statuses
2846 WHERE ledger_id = gbl_sob_id
2847 AND period_year = gbl_period_year));
2848 log(l_module_name, 'Deleted : '||sql%rowcount||' row/s.');
2849 EXCEPTION WHEN NO_DATA_FOUND THEN
2850 log(l_module_name, 'No rows found to dete from fv_gtas_processed_je_hdrs.');
2851 END;
2852
2853 log(l_module_name, 'Updatin fv_gtas_run');
2854 UPDATE fv_gtas_run
2855 SET process_date = NULL,
2856 jc_run_month = NULL,
2857 run_fed_flag = 'A'
2858 WHERE set_of_books_id = gbl_sob_id
2859 AND fiscal_year = gbl_period_year;
2860
2861
2862 log(l_module_name, 'End: '||l_module_name);
2863 EXCEPTION
2864 WHEN OTHERS THEN
2865 gbl_err_code := 2;
2866 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2867 FV_UTILITY.LOG_MESG(gbl_err_buff);
2868 END DELETE_PRIOR_GTAS_ACTIVITY;
2869 --------------------------------------------------------------------------------
2870 FUNCTION GET_EXCEPTION_COUNT RETURN NUMBER IS
2871 l_module_name VARCHAR2(80) := g_module_name||'GET_EXCEPTION_COUNT';
2872 l_exception_count NUMBER := 0;
2873 BEGIN
2874 log(l_module_name, 'Begin '||l_module_name);
2875
2876 SELECT COUNT(*)
2877 INTO l_exception_count
2878 FROM fv_gtas_exceptions
2879 WHERE set_of_books_id = gbl_sob_id
2880 AND period_year = gbl_period_year;
2881 log(l_module_name, 'Exception Count: '||l_exception_count);
2882
2883 RETURN l_exception_count;
2884
2885 log(l_module_name, 'End: '||l_module_name);
2886 EXCEPTION
2887 WHEN OTHERS THEN
2888 gbl_err_code := 2;
2889 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2890 FV_UTILITY.LOG_MESG(gbl_err_buff);
2891 END GET_EXCEPTION_COUNT;
2892 --------------------------------------------------------------------------------
2893 --Check whether the account exists in gtas attributes
2894 --If not, then we have to use the sgl account for getting the attributes
2895 PROCEDURE GET_USSGL_ACCT(p_gtas_acct_num IN VARCHAR2,
2896 p_sgl_acct_num IN VARCHAR2,
2897 p_account_num OUT NOCOPY VARCHAR2)
2898 IS
2899 l_module_name VARCHAR2(80) := g_module_name||'GET_USSGL_ACCT';
2900 l_account_number fv_gtas_attributes.gtas_acct_number%TYPE;
2901 BEGIN
2902 log(l_module_name, 'Begin '||l_module_name);
2903 SELECT gtas_acct_number
2904 INTO p_account_num
2905 FROM fv_gtas_attributes
2906 WHERE set_of_books_id = gbl_sob_id
2907 AND gtas_acct_number = p_gtas_acct_num;
2908
2909 log(l_module_name, 'End: '||l_module_name);
2910 EXCEPTION
2911 WHEN NO_DATA_FOUND THEN
2912 log(l_module_name, 'Account num: '||p_gtas_acct_num||' does not exist in gtas attributes.'||
2913 ' Using sgl account num: '||p_sgl_acct_num);
2914 p_account_num := p_sgl_acct_num;
2915
2916 WHEN OTHERS THEN
2917 gbl_err_code := 2;
2918 gbl_err_buff := l_module_name||'-When others error: '||SQLERRM;
2919 FV_UTILITY.LOG_MESG(gbl_err_buff);
2920 END GET_USSGL_ACCT;
2921 --------------------------------------------------------------------------------
2922 BEGIN
2923 g_module_name := 'fv.plsql.FV_GTAS_GL_PKG.';
2924 --------------------------------------------------------------------------------
2925 END FV_GTAS_GL_PKG;