[Home] [Help]
PACKAGE BODY: APPS.FV_FACTS1_GL_PKG
Source
1 PACKAGE BODY FV_FACTS1_GL_PKG AS
2 /* $Header: FVFCJCRB.pls 120.19 2008/02/14 14:56:19 snama ship $ */
3
4 g_module_name VARCHAR2(100);
5
6 gbl_bal_segment VARCHAR2(10);
7 gbl_acc_segment VARCHAR2(10);
8 gbl_jrnl_attribute VARCHAR2(15);
9 gbl_vend_attribute VARCHAR2(15);
10 gbl_cust_attribute VARCHAR2(15);
11 gbl_currency_code gl_ledgers_public_v.currency_code%TYPE;
12 gbl_period_name gl_period_statuses.period_name%TYPE;
13 gbl_sob_id gl_ledgers_public_v.ledger_id%TYPE;
14 gbl_coa_id gl_ledgers_public_v.chart_of_accounts_id%TYPE;
15 gbl_err_code NUMBER := 0;
16 gbl_err_buff VARCHAR2(250);
17 gbl_period_num_low gl_period_statuses.period_num%TYPE;
18 gbl_period_num_high gl_period_statuses.period_num%TYPE;
19 gbl_period_year gl_period_statuses.period_year%TYPE;
20 gbl_exception_rec_count NUMBER;
21 gbl_called_from_main VARCHAR2(1);
22 gbl_header_id gl_je_headers.je_header_id%type := 0;
23 gbl_trading_partner_att fv_be_trx_dtls.attribute1%TYPE;
24 gbl_manual_source_flag VARCHAR2(1) := 'N';
25
26
27
28 TYPE party_info_rec IS RECORD (
29 party_id fv_facts1_line_balances.Party_Id%TYPE,
30 cust_vend_type fv_facts1_line_balances.party_type%TYPE,
31 vendor_type fv_facts1_line_balances.party_classification%TYPE,
32 elim_dept fv_facts1_line_balances.eliminations_dept%TYPE,
33 recipient_name fv_facts1_line_balances.recipient_name%TYPE,
34 reported_status VARCHAR2(1),
35 record_category fv_facts1_line_balances.record_category%TYPE,
36 feeder_flag fv_facts1_line_balances.feeder_flag%TYPE,
37 g_ng_indicator fv_facts1_line_balances.g_ng_indicator%TYPE,
38 party_line_amount NUMBER);
39 TYPE party_info_table IS TABLE OF party_info_rec
40 INDEX BY BINARY_INTEGER;
41
42
43 --------------------------------------------------------------------------------
44 PROCEDURE CLEANUP;
45 PROCEDURE GET_SEGMENT_NAMES;
46 PROCEDURE GET_SYSTEM_ATTRIBUTES;
47 PROCEDURE GET_PROCESS_DATES;
48 PROCEDURE PROCESS_GL_LINES;
49 PROCEDURE GET_PARTY_INFO(p_category IN VARCHAR2,
50 p_source IN VARCHAR2,
51 p_reference_1 IN VARCHAR2,
52 p_reference_2 IN VARCHAR2,
53 p_reference_3 IN VARCHAR2,
54 p_reference_5 IN VARCHAR2,
55 p_reference_7 IN VARCHAR2,
56 p_jrnl_attribute IN VARCHAR2,
57 p_fed_nonfed IN VARCHAR2,
58 p_je_from_sla_flag IN VARCHAR2,
59 p_je_batch_id IN NUMBER,
60 p_je_header_id IN NUMBER,
61 p_je_line_num IN NUMBER,
62 p_jrnl_dc_ind IN VARCHAR2,
63 p_party_info_tab OUT NOCOPY party_info_table);
64 PROCEDURE INSERT_EXCEPTION_RECS;
65 PROCEDURE SUBMIT_EXCEPTION_REPORT;
66 PROCEDURE UPDATE_FACTS1_RUN;
67 PROCEDURE get_reference_column (p_entity_code IN VARCHAR2,
68 p_je_batch_id IN NUMBER,
69 p_je_header_id IN NUMBER,
70 p_je_line_num IN NUMBER,
71 p_application_id IN NUMBER,
72 p_jrnl_dc_indicator IN VARCHAR2,
73 p_party_info_tab OUT NOCOPY party_info_table);
74 PROCEDURE log(module IN VARCHAR2,
75 message_line IN VARCHAR2);
76 --------------------------------------------------------------------------------
77 PROCEDURE MAIN(p_err_buff OUT NOCOPY VARCHAR2,
78 p_err_code OUT NOCOPY NUMBER,
79 p_sob_id IN NUMBER,
80 p_period_name IN VARCHAR2,
81 p_called_from_main IN VARCHAR2,
82 p_trading_partner_att IN VARCHAR2)
83 IS
84
85 l_period_year number(15);
86 l_no_fed_account number(15);
87 l_module_name VARCHAR2(200);
88
89 BEGIN
90
91 l_module_name := g_module_name || 'MAIN';
92 FV_UTILITY.LOG_MESG('In '||l_module_name);
93
94 FV_UTILITY.LOG_MESG('Parameters:');
95 FV_UTILITY.LOG_MESG('p_sob_id: '||p_sob_id);
96 FV_UTILITY.LOG_MESG('p_period_name: '||p_period_name);
97 FV_UTILITY.LOG_MESG('p_called_from_main: '||p_called_from_main);
98 FV_UTILITY.LOG_MESG('p_trading_partner_att: '||p_trading_partner_att);
99
100 gbl_trading_partner_att := upper(p_trading_partner_att);
101
102 gbl_called_from_main := p_called_from_main;
103 if (gbl_called_from_main = 'I') then
104 gbl_called_from_main := 'N';
105 End if;
106
107 p_err_code := 0;
108 gbl_err_code := 0;
109
110 IF gbl_err_code <> 0 THEN
111 p_err_code := gbl_err_code;
112 p_err_buff := gbl_err_buff;
113 RETURN;
114 END IF;
115
116 SELECT period_year
117 INTO l_period_year
118 FROM gl_period_statuses
119 WHERE application_id = 101
120 AND ledger_id = p_sob_id
121 AND period_name = p_period_name;
122
123
124
125 SELECT count(*)
126 INTO l_no_fed_account
127 FROM fv_facts1_fed_accounts
128 WHERE set_of_books_id = p_sob_id
129 AND fiscal_year = l_period_year;
130
131
132 if l_no_fed_account = 0 then
133 p_err_buff := 'Please run the FACTS-1 Federal Accounts Creation process for this SOB
134 and period ' || p_period_name;
135 p_err_code := -1;
136 commit;
137 return;
138 End if;
139
140 gbl_sob_id := p_sob_id;
141 gbl_period_name := p_period_name;
142 gbl_err_code := 0;
143
144
145
146 IF gbl_err_code = 0 THEN
147 get_system_attributes;
148 END IF;
149
150 IF gbl_err_code = 0 THEN
151 get_segment_names;
152 END IF;
153
154 IF gbl_err_code = 0 THEN
155 get_process_dates;
156 END IF;
157
158 IF gbl_err_code = 0 THEN
159 process_gl_lines;
160 END IF;
161
162 IF gbl_err_code = 0 THEN
163 insert_exception_recs;
164 END IF;
165
166 IF gbl_err_code = 0 THEN
167 update_facts1_run;
168 END IF;
169
170 IF gbl_err_code <> 0 THEN
171 p_err_code := gbl_err_code;
172 p_err_buff := gbl_err_buff;
173 ROLLBACK;
174 RETURN;
175 END IF;
176
177 COMMIT;
178
179 FV_UTILITY.LOG_MESG('Facts I Journal Process completed successfully.');
180
181 EXCEPTION WHEN OTHERS THEN
182 p_err_code := SQLCODE;
183 p_err_buff := 'When others exception in Main - '||SQLERRM;
184 ROLLBACK;
185 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
186
187 END main;
188 --------------------------------------------------------------------------------
189 PROCEDURE CLEANUP
190 IS
191
192 l_module_name VARCHAR2(200);
193
194 BEGIN
195
196 l_module_name := g_module_name || 'CLEANUP';
197 FV_UTILITY.LOG_MESG('In '||l_module_name);
198
199 DELETE FROM fv_facts_report_t2
200 WHERE set_of_books_id = gbl_sob_id;
201
202 EXCEPTION WHEN OTHERS THEN
203 gbl_err_code := SQLCODE;
204 gbl_err_buff := l_module_name||' - When others exception - '||SQLERRM;
205 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
206
207 END CLEANUP;
208
209 --------------------------------------------------------------------------------
210 -- Get balancing and accounting segments
211 --------------------------------------------------------------------------------
212 PROCEDURE GET_SEGMENT_NAMES
213 IS
214
215 l_module_name VARCHAR2(200);
216 l_app_id NUMBER := 101;
217 l_flex_code VARCHAR2(10) := 'GL#';
218 l_segment_found BOOLEAN := FALSE;
219 invalid_bal_segment EXCEPTION;
220 invalid_acc_segment EXCEPTION;
221
222 BEGIN
223
224 l_module_name := g_module_name || 'GET_SEGMENT_NAMES';
225 FV_UTILITY.LOG_MESG('In '||l_module_name);
226
227
228 FV_UTILITY.LOG_MESG('COA ID: '||gbl_coa_id);
229
230 -- Get Balancing Segment Name
231 -----------------------------
232 l_segment_found := FND_FLEX_APIS.get_segment_column
233 (l_app_id,
234 l_flex_code,
235 gbl_coa_id,
236 'GL_BALANCING',
237 gbl_bal_segment) ;
238
239 IF NOT l_segment_found THEN
240 RAISE invalid_bal_segment;
241 END IF;
242
243 -- Get Accounting Segment Name
244 ------------------------------
245 l_segment_found := FND_FLEX_APIS.get_segment_column
246 (l_app_id,
247 l_flex_code,
248 gbl_coa_id,
249 'GL_ACCOUNT',
250 gbl_acc_segment);
251 IF NOT l_segment_found THEN
252 RAISE invalid_acc_segment;
253 END IF;
254
255 IF (gbl_bal_segment IS NULL OR
256 gbl_acc_segment IS NULL) THEN
257 RAISE NO_DATA_FOUND;
258 END IF;
259
260 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
261 'Balancing Segment: '||gbl_bal_segment);
262 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
263 'Accounting Segment: '||gbl_acc_segment);
264
265 EXCEPTION
266 WHEN NO_DATA_FOUND THEN
267 gbl_err_code := -1 ;
268 gbl_err_buff := 'Balancing or Accounting segment not found.';
269 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
270 WHEN invalid_bal_segment THEN
271 gbl_err_code := -1 ;
272 gbl_err_buff := 'Error while fetching balancing segment.';
273 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
274 WHEN invalid_acc_segment THEN
275 gbl_err_code := -1 ;
276 gbl_err_buff := 'Error while fetching accounting segment.';
277 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
278 WHEN OTHERS THEN
279 gbl_err_code := -1 ;
280 gbl_err_buff := 'When others error while getting
281 Balancing or Accounting segment - '||SQLERRM;
282 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
283 END get_segment_names;
284 --------------------------------------------------------------------------------
285 -- Get the period num for the parameter period and also the first period num
286 -- for the year.
287 --------------------------------------------------------------------------------
288 PROCEDURE GET_PROCESS_DATES
289 IS
290
291 l_module_name VARCHAR2(200);
292 l_temp_mesg VARCHAR2(250);
293
294 BEGIN
295
296 l_module_name := g_module_name || 'GET_PROCESS_DATES';
297 FV_UTILITY.LOG_MESG('In '||l_module_name);
298
299 -- Get the period year for the period parameter passed.
300 l_temp_mesg := 'getting period year.';
301 SELECT period_year
302 INTO gbl_period_year
303 FROM gl_period_statuses p
304 WHERE p.application_id = 101
305 AND p.ledger_id = gbl_sob_id
306 AND p.period_name = gbl_period_name;
307
308 -- Get the first period of the year
309 l_temp_mesg := 'getting first period number of the year.';
310 SELECT MIN(period_num)
311 INTO gbl_period_num_low
312 FROM gl_period_statuses
313 WHERE period_year = gbl_period_year
314 AND application_id = 101
315 AND closing_status <> 'F'
316 AND closing_status <> 'N'
317 AND adjustment_period_flag = 'N'
318 AND ledger_id = gbl_sob_id;
319
320 -- Get the period num for the parameter period
321 l_temp_mesg := 'getting period number of the parameter period.';
322 SELECT period_num
323 INTO gbl_period_num_high
324 FROM gl_period_statuses p
325 WHERE period_name = gbl_period_name
326 AND p.application_id = 101
327 AND p.ledger_id = gbl_sob_id
328 AND p.period_year = gbl_period_year;
329
330 IF (gbl_period_num_low = 0 OR
331 gbl_period_num_high = 0) THEN
332 gbl_err_code := 2 ;
333 gbl_err_buff := l_module_name||' Period number '||
334 'found zero for the passed fiscal year.' ;
335 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
336 RETURN;
337 END IF;
338
339 FV_UTILITY.LOG_MESG('Period Year: '||gbl_period_year);
340 FV_UTILITY.LOG_MESG('Period Number Low: '||gbl_period_num_low);
341 FV_UTILITY.LOG_MESG('Period Number High: '||gbl_period_num_high);
342
343 EXCEPTION
344 WHEN NO_DATA_FOUND THEN
345 gbl_err_code := 2;
346 gbl_err_buff := l_module_name||' - No data found when '||l_temp_mesg;
347 FV_UTILITY.LOG_MESG(gbl_err_buff);
348
349 WHEN OTHERS THEN
350 gbl_err_code := 2;
351 gbl_err_buff := l_module_name||' - When others error when '||l_temp_mesg;
352 FV_UTILITY.LOG_MESG(gbl_err_buff);
353 END get_process_dates;
354 --------------------------------------------------------------------------------
355 -- Get Facts I Journal, Vendor and Customer attributes
356 --------------------------------------------------------------------------------
357 PROCEDURE GET_SYSTEM_ATTRIBUTES
358 IS
359 l_module_name VARCHAR2(200);
360
361 l_temp_mesg VARCHAR2(50);
362 BEGIN
363
364 l_module_name := g_module_name || 'GET_SYSTEM_ATTRIBUTES';
365 FV_UTILITY.LOG_MESG('In '||l_module_name);
366
367 l_temp_mesg := 'getting Journal Attribute.';
368 SELECT factsI_journal_attribute
369 INTO gbl_jrnl_attribute
370 FROM fv_system_parameters;
371
372 l_temp_mesg := 'getting Vendor/Customer Attribute.';
373 SELECT factsI_vendor_attribute, factsI_customer_attribute
374 INTO gbl_vend_attribute, gbl_cust_attribute
375 FROM fv_system_parameters;
376
377 IF (gbl_jrnl_attribute IS NULL OR
378 gbl_vend_attribute IS NULL OR
379 gbl_cust_attribute IS NULL) THEN
380 RAISE NO_DATA_FOUND;
381 END IF;
382
383 l_temp_mesg := 'getting Currency Code/Chart of Accounts Id.';
384 SELECT currency_code, chart_of_accounts_id
385 INTO gbl_currency_code, gbl_coa_id
386 FROM gl_ledgers_public_v
387 WHERE ledger_id = gbl_sob_id;
388
389
390 FV_UTILITY.LOG_MESG('Journal Attribute: '||gbl_jrnl_attribute);
391 FV_UTILITY.LOG_MESG('Vendor Attribute: '||gbl_vend_attribute);
392 FV_UTILITY.LOG_MESG('Customer Attribute: '||gbl_cust_attribute);
393 FV_UTILITY.LOG_MESG('Currency: '||gbl_currency_code);
394 FV_UTILITY.LOG_MESG('Chart of Accounts Id: '||gbl_coa_id);
395
396 EXCEPTION
397 WHEN NO_DATA_FOUND THEN
398 gbl_err_code := 2;
399 gbl_err_buff := l_module_name||' - Null values/No data found when '||
400 l_temp_mesg;
401 FV_UTILITY.LOG_MESG(l_module_name,gbl_err_buff);
402
403 WHEN OTHERS THEN
404 gbl_err_code := SQLCODE;
405 gbl_err_buff := l_module_name||' - When others error '||l_temp_mesg||
406 ' - '||SQLERRM;
407 FV_UTILITY.LOG_MESG(l_module_name,gbl_err_buff);
408
409 END get_system_attributes;
410 --------------------------------------------------------------------------------
411 PROCEDURE log(module IN VARCHAR2,
412 message_line IN VARCHAR2) IS
413 --------------------------------------------------------------------------------
414 l_module_name VARCHAR2(1000);
415 BEGIN
416
417 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
418 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
419 module, message_line);
420
421 END IF;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 FV_UTILITY.LOG_MESG('When others error in module: log: '||sqlerrm);
426 END log;
427 --------------------------------------------------------------------------------
428 -- Select all journal lines from gl_je_lines from the beginning of the year upto
429 -- the period being run, for all accounts existing in fv_facts1_fed_accounts and
430 -- all journal lines not existing in fv_facts1_line_balances.
431 -- Retreives party info like party type, eliminations dept, etc and insert into
432 -- fv_facts1_line_balances.
433 --------------------------------------------------------------------------------
434 PROCEDURE PROCESS_GL_LINES
435 IS
436 l_module_name VARCHAR2(200);
437
438 TYPE t_ref_cur IS REF CURSOR ;
439 l_gl_lines_cur t_ref_cur ;
440
441 TYPE je_header_id_t IS TABLE OF gl_je_headers.je_header_id%TYPE;
442 TYPE je_category_t IS TABLE OF gl_je_headers.je_category%TYPE;
443 TYPE je_source_t IS TABLE OF gl_je_headers.je_source%TYPE;
444 TYPE je_line_num_t IS TABLE OF gl_je_lines.je_line_num%TYPE;
445 TYPE sob_id_t IS TABLE OF gl_je_lines.ledger_id%TYPE;
446 TYPE ccid_t IS TABLE OF gl_je_lines.code_combination_id%TYPE;
447 TYPE attribute_val_t IS TABLE OF gl_je_lines.attribute1%TYPE;
448 TYPE amount_t IS TABLE OF fv_facts1_line_balances.amount%TYPE;
449 TYPE d_c_indicator_t IS TABLE OF fv_facts1_line_balances.d_c_indicator%TYPE;
450 TYPE gl_period_t IS TABLE OF gl_je_lines.period_name%TYPE;
451 TYPE party_id_t IS TABLE OF fv_facts1_line_balances.party_id%TYPE;
452 TYPE party_type_t IS TABLE OF fv_facts1_line_balances.party_type%TYPE;
453 TYPE vendor_type_t IS TABLE OF fv_facts1_line_balances.party_classification%TYPE;
454 TYPE recipient_name_t IS TABLE OF fv_facts1_line_balances.recipient_name%TYPE;
455 TYPE eliminations_dept_t IS TABLE OF fv_facts1_line_balances.eliminations_dept%TYPE;
456 TYPE reference_t IS TABLE OF gl_je_lines.reference_1%TYPE;
457 TYPE fund_value_t IS TABLE OF fv_fund_parameters.fund_value%TYPE;
458 TYPE account_number_t IS TABLE OF fv_facts1_line_balances.account_number%TYPE;
459 TYPE fed_nonfed_t IS TABLE OF fv_facts1_fed_accounts.fed_non_fed%TYPE;
460 TYPE g_ng_indicator_t IS TABLE OF fv_facts1_line_balances.g_ng_indicator%TYPE;
461 TYPE record_category_t IS TABLE OF fv_facts1_line_balances.record_category%TYPE;
462 TYPE varchar_1_t IS TABLE OF VARCHAR2(1);
463 TYPE period_num_t IS TABLE OF gl_period_statuses.period_num%TYPE;
464 TYPE je_from_sla_flag_t IS TABLE OF VARCHAR2(1);
465 TYPE je_batch_id_t IS TABLE OF gl_je_headers.je_batch_id%TYPE;
466
467 je_header_id_list_new je_header_id_t;
468 je_header_id_list je_header_id_t;
469 je_category_list je_category_t;
470 je_source_list je_source_t;
471 je_line_num_list je_line_num_t;
472 sob_id_list sob_id_t;
473 ccid_list ccid_t;
474 attribute_value_list attribute_val_t;
475 amount_list amount_t;
476 d_c_indicator_list d_c_indicator_t;
477 gl_period_list gl_period_t;
478 party_id_list party_id_t;
479 party_type_list party_type_t;
480 vendor_type_list vendor_type_t;
481 recipient_name_list recipient_name_t;
482 eliminations_dept_list eliminations_dept_t;
483 reference_1_list reference_t;
484 reference_2_list reference_t;
485 reference_3_list reference_t;
486 reference_5_list reference_t;
487 reference_7_list reference_t;
488 fund_value_list fund_value_t;
489 account_number_list account_number_t;
490 fed_nonfed_list fed_nonfed_t;
491 g_ng_indicator_list g_ng_indicator_t;
492 reported_status_list varchar_1_t;
493 record_category_list record_category_t;
494 feeder_flag_list varchar_1_t;
495 period_num_list period_num_t;
496 je_from_sla_flag_list je_from_sla_flag_t;
497 je_batch_id_list je_batch_id_t;
498
499
500 ccid_list_2 ccid_t;
501 period_num_list_2 period_num_t;
502 account_number_list_2 account_number_t;
503 fund_value_list_2 fund_value_t;
504 amount_list_2 amount_t;
505 d_c_indicator_list_2 d_c_indicator_t;
506 g_ng_indicator_list_2 g_ng_indicator_t;
507 eliminations_dept_list_2 eliminations_dept_t;
508 record_category_list_2 record_category_t;
509 recipient_name_list_2 recipient_name_t;
510 je_header_id_list_2 je_header_id_t;
511 je_line_num_list_2 je_line_num_t;
512 je_category_list_2 je_category_t;
513 je_source_list_2 je_source_t;
514 party_id_list_2 party_id_t;
515 party_type_list_2 party_type_t;
516 vendor_type_list_2 vendor_type_t;
517 attribute_value_list_2 attribute_val_t;
518 feeder_flag_list_2 varchar_1_t;
519 gl_period_list_2 gl_period_t;
520
521
522 l_select_stmt VARCHAR2(20000);
523 l_last_fetch BOOLEAN;
524
525 l_party_info_tab party_info_table;
526
527 flg boolean;
528
529 k BINARY_INTEGER := 1;
530
531 BEGIN
532
533 l_module_name := g_module_name || 'PROCESS_GL_LINES';
534 FV_UTILITY.LOG_MESG('In '||l_module_name);
535
536
537 FV_UTILITY.LOG_MESG('Inserting into fv_facts1_header_id_gt');
538
539 if gbl_called_from_main = 'N' then
540
541 INSERT INTO fv_facts1_header_id_gt(je_header_id,set_of_books_id)
542 select gjh.je_header_id , gjh.ledger_id
543 from
544 (SELECT period_num, period_name
545 FROM gl_period_statuses
546 WHERE application_id = 101
547 AND ledger_id = gbl_sob_id
548 AND period_num BETWEEN gbl_period_num_low AND gbl_period_num_high
549 AND period_year = gbl_period_year) gps,
550 gl_je_headers gjh
551 where gjh.period_name = gps.period_name
552 and gjh.ledger_id = gbl_sob_id
553 AND gjh.status = 'P'
554 AND gjh.actual_flag = 'A'
555 and not exists (select 'x' from fv_facts1_processed_je_hdrs e
556 where e.set_of_books_id = gjh.ledger_id
557 and e.je_header_id = gjh.je_header_id) ;
558
559
560 FV_UTILITY.LOG_MESG('Inserted ' || SQL%ROWCOUNT);
561
562 COMMIT;
563 Fnd_Stats.GATHER_TABLE_STATS(ownname=>'FV',tabname=>'FV_FACTS1_HEADER_ID_GT');
564
565 End if;
566
567 l_select_stmt :=
568 ' gjl.code_combination_id,
569 gjh.ledger_id,
570 glcc.'||gbl_acc_segment|| ',
571 (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0) ) amount,
572 DECODE( SIGN (NVL(gjl.accounted_dr,0) - NVL(gjl.accounted_cr,0)) , -1, ''C'', ''D'') d_c_indicator,
573 gjh.je_header_id,
574 gjl.je_line_num,
575 gjh.je_category,
576 gjh.je_source,
577 gjl.reference_1,
578 gjl.reference_2,
579 gjl.reference_3,
580 gjl.reference_5,
581 gjl.reference_7,
582 gjl.'||gbl_jrnl_attribute|| ',
583 glcc.'||gbl_bal_segment|| ',
584 gjl.period_name,
585 NULL party_id,
586 NULL party_type,
587 NULL party_classification,
588 NULL recipient_name,
589 NULL eliminations_dept,
590 fff.fed_non_fed,
591 NULL reported_status,
592 NULL record_category,
593 NULL feeder_flag,
594 gps.period_num,
595 NULL g_ng_indicator,
596 NVL(gjh.je_from_sla_flag, ''N''),
597 gjh.je_batch_id ';
598
599 if gbl_called_from_main = 'Y' then
600
601 l_select_stmt := 'SELECT ' || l_select_stmt ||
602 ' FROM
603 fv_facts1_fed_accounts fff,
604 gl_code_combinations glcc,
605 ( SELECT period_num, period_name
606 FROM gl_period_statuses
607 WHERE application_id = 101
608 AND ledger_id = :gbl_sob_id
609 AND period_num BETWEEN :gbl_period_num_low AND :gbl_period_num_high
610 AND period_year = :gbl_period_year ) gps,
611 gl_je_lines gjl,
612 gl_je_headers gjh
613 WHERE fff.jc_flag = ''N''
614 AND fff.set_of_books_id = :gbl_sob_id
615 AND fff.fiscal_year = :gbl_period_year
616 AND glcc.'||gbl_acc_segment || ' = fff.account_number
617 AND glcc.chart_of_accounts_id = :gbl_coa_id
618 AND gjl.code_combination_id = glcc.code_combination_id
619 AND gjl.period_name = gps.period_name
620 AND gjl.ledger_id = :gbl_sob_id
621 AND gjl.je_header_id = gjh.je_header_id
622 AND gjh.currency_code <> ''STAT''
623 AND gjh.ledger_id = :gbl_sob_id
624 AND gjh.status = ''P''
625 AND gjh.actual_flag = ''A'' ' ;
626
627
628 OPEN l_gl_lines_cur FOR l_select_stmt using
629 gbl_sob_id,gbl_period_num_low ,gbl_period_num_high,gbl_period_year,
630 gbl_sob_id,gbl_period_year ,gbl_coa_id,gbl_sob_id,gbl_sob_id;
631
632 fv_utility.log_mesg('Running in full mode');
633 fv_utility.log_mesg(l_select_stmt);
634
635 else
636
637
638 l_select_stmt := 'SELECT /*+ ORDERED INDEX(gjh GL_JE_HEADERS_U1, ftt
639 FV_FACTS1_HEADER_ID_GT_U1, gjl GL_JE_LINES_U1,
640 glcc GL_CODE_COMBINATIONS_U1,
641 fff FV_FACTS1_FED_ACCOUNTS_U1) */ ' ||l_select_stmt ||
642 ' FROM fv_facts1_header_id_gt ftt
643 , gl_je_headers gjh
644 , gl_je_lines gjl
645 , (SELECT period_num
646 , period_name
647 FROM gl_period_statuses ps
648 WHERE application_id = 101
649 AND ledger_id = :gbl_sob_id
650 AND period_num BETWEEN :gbl_period_num_low AND :gbl_period_num_high
651 AND period_year = :gbl_period_year) gps
652 , gl_code_combinations glcc
653 , fv_facts1_fed_accounts fff
654 WHERE gjh.period_name = gps.period_name
655 AND gjl.ledger_id = :gbl_sob_id
656 AND gjl.je_header_id = ftt.je_header_id
657 AND gjh.currency_code <> ''STAT''
658 AND gjh.status = ''P''
659 AND gjh.actual_flag = ''A''
660 and gjh.je_header_id = ftt.je_header_id
661 and ftt.set_of_books_id = :gbl_sob_id
662 AND gjh.ledger_id = :gbl_sob_id
663 AND glcc.code_combination_id = gjl.code_combination_id
664 AND glcc.chart_of_accounts_id = :gbl_coa_id
665 AND fff.account_number = glcc.' ||gbl_acc_segment || '
666 AND fff.set_of_books_id = :gbl_sob_id
667 AND fff.fiscal_year = :gbl_period_year' ;
668
669
670 fv_utility.log_mesg('Running in cumulative mode');
671 fv_utility.log_mesg(l_select_stmt);
672 fv_utility.log_mesg('gbl_sob_id: '||gbl_sob_id);
673 fv_utility.log_mesg('gbl_coa_id: '||gbl_coa_id);
674 fv_utility.log_mesg('gbl_period_num_low: '||gbl_period_num_low);
675 fv_utility.log_mesg('gbl_period_num_high: '||gbl_period_num_high);
676 fv_utility.log_mesg('gbl_period_year: '||gbl_period_year);
677 fv_utility.log_mesg('gbl_currency_code: '||gbl_currency_code);
678
679
680 OPEN l_gl_lines_cur FOR l_select_stmt using
681 gbl_sob_id,gbl_period_num_low ,gbl_period_num_high,gbl_period_year,
682 gbl_sob_id,gbl_sob_id, gbl_sob_id, gbl_coa_id,
683 gbl_sob_id, gbl_period_year;
684
685 END If;
686
687
688 LOOP
689
690 FETCH l_gl_lines_cur BULK COLLECT INTO
691 ccid_list,
692 sob_id_list,
693 account_number_list,
694 amount_list,
695 d_c_indicator_list,
696 je_header_id_list,
697 je_line_num_list,
698 je_category_list,
699 je_source_list,
700 reference_1_list,
701 reference_2_list,
702 reference_3_list,
703 reference_5_list,
704 reference_7_list,
705 attribute_value_list,
706 fund_value_list,
707 gl_period_list,
708 party_id_list,
709 party_type_list,
710 vendor_type_list,
711 recipient_name_list,
712 eliminations_dept_list,
713 fed_nonfed_list,
714 reported_status_list,
715 record_category_list,
716 feeder_flag_list,
717 period_num_list,
718 g_ng_indicator_list,
719 je_from_sla_flag_list,
720 je_batch_id_list
721 LIMIT 10000;
722
723 IF l_gl_lines_cur%NOTFOUND THEN
724 l_last_fetch := TRUE;
725 END IF;
726
727 IF (je_header_id_list.count = 0 AND
728 l_last_fetch) THEN
729 EXIT;
730 END IF;
731
732
733 ccid_list_2 := ccid_t();
734 period_num_list_2 := period_num_t();
735 account_number_list_2 := account_number_t();
736 fund_value_list_2 := fund_value_t();
737 amount_list_2 := amount_t();
738 d_c_indicator_list_2 := d_c_indicator_t();
739 g_ng_indicator_list_2 := g_ng_indicator_t();
740 eliminations_dept_list_2 := eliminations_dept_t();
741 record_category_list_2 := record_category_t();
742 recipient_name_list_2 := recipient_name_t();
743 je_header_id_list_2 := je_header_id_t();
744 je_line_num_list_2 := je_line_num_t();
745 je_category_list_2 := je_category_t();
746 je_source_list_2 := je_source_t();
747 party_id_list_2 := party_id_t();
748 party_type_list_2 := party_type_t();
749 vendor_type_list_2 := vendor_type_t();
750 attribute_value_list_2 := attribute_val_t();
751 feeder_flag_list_2 := varchar_1_t();
752 gl_period_list_2 := gl_period_t();
753
754 FOR i IN je_header_id_list.first .. je_header_id_list.last
755 LOOP
756 log(l_module_name,'----------------------------');
757 log(l_module_name, 'Calling get_party_info with....');
758 log(l_module_name, 'source: '||je_source_list(i));
759 log(l_module_name, 'cat: '||je_category_list(i));
760 log(l_module_name, 'reference1: '||reference_1_list(i));
761 log(l_module_name, 'reference2: '||reference_2_list(i));
762 log(l_module_name, 'reference3: '||reference_3_list(i));
763 log(l_module_name, 'reference5: '||reference_5_list(i));
764 log(l_module_name, 'reference7: '||reference_7_list(i));
765 log(l_module_name, 'g ng ind: '||g_ng_indicator_list(i));
766 log(l_module_name, 'je batch id '||je_batch_id_list(i));
767 log(l_module_name, 'je header id '||je_header_id_list(i));
768 log(l_module_name, 'je line num '||je_line_num_list(i));
769 log(l_module_name, 'je from sla flag '||je_from_sla_flag_list(i));
770
771 IF gbl_header_id < je_header_id_list(i) THEN
772 gbl_header_id := je_header_id_list(i);
773 END IF;
774
775 get_party_info(
776 je_category_list(i), je_source_list(i),
777 reference_1_list(i), reference_2_list(i),
778 reference_3_list(i), reference_5_list(i),
779 reference_7_list(i),
780 substr(attribute_value_list(i),1,6), -- bug 5505974
781 fed_nonfed_list(i),
782 je_from_sla_flag_list(i),
783 je_batch_id_list(i),
784 je_header_id_list(i),
785 je_line_num_list(i),
786 d_c_indicator_list(i),
787 l_party_info_tab);
788
789 log(l_module_name,'records in party_info_tab: '||l_party_info_tab.COUNT);
790
791 ccid_list_2.extend(l_party_info_tab.COUNT);
792 period_num_list_2.extend(l_party_info_tab.COUNT);
793 account_number_list_2.extend(l_party_info_tab.COUNT);
794 fund_value_list_2.extend(l_party_info_tab.COUNT);
795 amount_list_2.extend(l_party_info_tab.COUNT);
796 d_c_indicator_list_2 .extend(l_party_info_tab.COUNT);
797 g_ng_indicator_list_2.extend(l_party_info_tab.COUNT);
798 eliminations_dept_list_2.extend(l_party_info_tab.COUNT);
799 record_category_list_2.extend(l_party_info_tab.COUNT);
800 recipient_name_list_2.extend(l_party_info_tab.COUNT);
801 je_header_id_list_2.extend(l_party_info_tab.COUNT);
802 je_line_num_list_2.extend(l_party_info_tab.COUNT);
803 je_category_list_2 .extend(l_party_info_tab.COUNT);
804 je_source_list_2.extend(l_party_info_tab.COUNT);
805 party_id_list_2.extend(l_party_info_tab.COUNT);
806 party_type_list_2.extend(l_party_info_tab.COUNT);
807 vendor_type_list_2.extend(l_party_info_tab.COUNT);
808 attribute_value_list_2.extend(l_party_info_tab.COUNT);
809 feeder_flag_list_2.extend(l_party_info_tab.COUNT);
810 gl_period_list_2.extend(l_party_info_tab.COUNT);
811
812 FOR j IN l_party_info_tab.FIRST .. l_party_info_tab.LAST LOOP
813 --FOR j IN 1 .. party_info_tab.COUNT LOOP
814 ccid_list_2(k) := ccid_list(i);
815 period_num_list_2(k) := period_num_list(i);
816 account_number_list_2(k) := account_number_list(i);
817 fund_value_list_2(k) := fund_value_list(i);
818
819 IF (NVL(je_from_sla_flag_list(i),'N') <> 'Y' OR
820 gbl_manual_source_flag = '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 d_c_indicator_list_2(k) := d_c_indicator_list(i);
827 g_ng_indicator_list_2(k) := l_party_info_tab(j).g_ng_indicator;
828 eliminations_dept_list_2(k) := l_party_info_tab(j).elim_dept;
829 record_category_list_2(k) := l_party_info_tab(j).record_category;
830 recipient_name_list_2(k) := l_party_info_tab(j).recipient_name;
831 je_header_id_list_2(k) := je_header_id_list(i);
832
833 fv_utility.log_mesg('je_header_id_list(i): '||je_header_id_list(i));
834
835 je_line_num_list_2(k) := je_line_num_list(i);
836 je_category_list_2(k) := je_category_list(i);
837 je_source_list_2(k) := je_source_list(i);
838 party_id_list_2(k) := l_party_info_tab(j).party_id;
839 party_type_list_2(k) := l_party_info_tab(j).cust_vend_type;
840 vendor_type_list_2(k) := l_party_info_tab(j).vendor_type;
841 attribute_value_list_2(k) := attribute_value_list(i);
842 feeder_flag_list_2(k) := l_party_info_tab(j).feeder_flag;
843 gl_period_list_2(k) := gl_period_list(i);
844
845 k := k+1;
846
847
848 END LOOP;
849
850 IF gbl_err_code <> 0 THEN
851 RETURN;
852 END IF;
853 END LOOP;
854
855 FORALL i IN je_header_id_list_2.first .. je_header_id_list_2.last
856
857
858 INSERT INTO fv_facts1_line_balances
859 ( ccid,
860 period_num,
861 set_of_books_id,
862 period_year,
863 account_number,
864 fund_value,
865 amount,
866 d_c_indicator,
867 g_ng_indicator,
868 eliminations_dept,
869 record_category,
870 recipient_name,
871 period_name,
872 je_header_id,
873 je_line_num,
874 je_category,
875 je_source,
876 party_id,
877 party_type,
878 party_classification,
879 attribute_value,
880 balance_type,
881 feeder_flag,
882 gl_period,
883 creation_date)
884 VALUES (
885 ccid_list_2(i),
886 period_num_list_2(i),
887 gbl_sob_id,
888 gbl_period_year,
889 account_number_list_2(i),
890 fund_value_list_2(i),
891 amount_list_2(i),
892 d_c_indicator_list_2(i),
893 g_ng_indicator_list_2(i),
894 eliminations_dept_list_2(i),
895 record_category_list_2(i),
896 recipient_name_list_2(i),
897 gbl_period_name,
898 je_header_id_list_2(i),
899 je_line_num_list_2(i),
900 je_category_list_2(i),
901 je_source_list_2(i),
902 party_id_list_2(i),
903 party_type_list_2(i),
904 vendor_type_list_2(i),
905 attribute_value_list_2(i),
906 'L',
907 feeder_flag_list_2(i),
908 gl_period_list_2(i),
909 sysdate);
910
911
912 IF gbl_called_from_main = 'Y' THEN
913 -- Eliminating the duplicate JE_HEADER_IDs
914 je_header_id_list_new := je_header_id_t();
915 for i in je_header_id_list_2.first .. je_header_id_list_2.last loop
916 flg := false;
917 for j in 1 .. je_header_id_list_new.count loop
918 if je_header_id_list_2(i) =je_header_id_list_new(j) then
919 flg := true;
920 end if;
921 end loop;
922 if flg <> true then
923 je_header_id_list_new.extend;
924 je_header_id_list_new(je_header_id_list_new.count) := je_header_id_list_2(i);
925 end if;
926 end loop;
927
928 FORALL i IN je_header_id_list_new.first .. je_header_id_list_new.last
929 INSERT INTO fv_facts1_header_id_gt
930 ( je_header_id,
931 set_of_books_id)
932 VALUES
933 (je_header_id_list_new(i),
934 gbl_sob_id);
935 END IF;
936
937 END LOOP;
938
939 close l_gl_lines_cur ;
940
941
942 EXCEPTION
943 WHEN OTHERS THEN
944 gbl_err_code := SQLCODE;
945 gbl_err_buff := l_module_name||' - When others error: '||SQLERRM;
946 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
947 END process_gl_lines;
948 --------------------------------------------------------------------------------
949 PROCEDURE GET_PARTY_INFO(p_category IN VARCHAR2,
950 p_source IN VARCHAR2,
951 p_reference_1 IN VARCHAR2,
952 p_reference_2 IN VARCHAR2,
953 p_reference_3 IN VARCHAR2,
954 p_reference_5 IN VARCHAR2,
955 p_reference_7 IN VARCHAR2,
956 p_jrnl_attribute IN VARCHAR2,
957 p_fed_nonfed IN VARCHAR2,
958 p_je_from_sla_flag IN VARCHAR2,
959 p_je_batch_id IN NUMBER,
960 p_je_header_id IN NUMBER,
961 p_je_line_num IN NUMBER,
962 p_jrnl_dc_ind IN VARCHAR2,
963 p_party_info_tab OUT NOCOPY party_info_table)
964 IS
965
966 l_module_name VARCHAR2(200);
967 ln_jrnl_att Varchar2(240);
968
969 i Integer;
970 l_vendor_id Number;
971 l_appl_id Number(15);
972 l_vendor_type Varchar2(30);
973 l_reported_status Varchar2(2);
974 l_record_category Varchar2(25);
975
976 l_valid_flag Varchar2(2);
977 l_feeder_flag Varchar2(1);
978
979
980
981 -- Variables for Dynamic Cursor
982
983
984 l_recipient_name po_vendors.vendor_name%type ;
985 l_elim_dept VARCHAR2(6);
986
987 l_govt_non_govt_ind VARCHAR2(1);
988
989 --l_reference_2 gl_je_lines.reference_1%TYPE;
990 --l_reference_5 gl_je_lines.reference_1%TYPE;
991 --l_reference_7 gl_je_lines.reference_1%TYPE;
992
993 l_be_trx_id NUMBER;
994
995 party_info_tab party_info_table;
996 l_cust_vend_type VARCHAR2(1);
997
998 BEGIN
999
1000 l_module_name := g_module_name || 'GET_PARTY_INFO';
1001
1002 log(l_module_name,'IN: '||l_module_name);
1003
1004 log(l_module_name,'***Parameters*** ');
1005 log(l_module_name,'p_source: '||p_source);
1006 log(l_module_name,'p_reference_1: '||p_reference_1);
1007 log(l_module_name,'p_reference_2: '||p_reference_2);
1008 log(l_module_name,'p_reference_3: '||p_reference_3);
1009 log(l_module_name,'p_reference_5: '||p_reference_5);
1010 log(l_module_name,'p_reference_7: '||p_reference_7);
1011 log(l_module_name,'p_jrnl_attribute: '||p_jrnl_attribute);
1012 log(l_module_name,'p_fed_nonfed: '||p_fed_nonfed);
1013 log(l_module_name,'p_je_from_sla_flag: '||p_je_from_sla_flag);
1014 log(l_module_name,'p_je_batch_id: '||p_je_batch_id);
1015 log(l_module_name,'p_je_header_id: '||p_je_header_id);
1016 log(l_module_name,'p_je_line_num: '||p_je_line_num);
1017 log(l_module_name,'p_jrnl_dc_ind: '||p_jrnl_dc_ind);
1018
1019
1020 ln_jrnl_att := p_jrnl_attribute;
1021 l_govt_non_govt_ind := p_fed_nonfed;
1022 l_reported_status := 'R';
1023 l_valid_flag := 'Y';
1024 l_feeder_flag := 'Y';
1025 gbl_manual_source_flag := 'N';
1026
1027 BEGIN
1028
1029 -------------------------------------------------------------------
1030 -- get the vendor id from Payables (Includes invoice and Payments)
1031 ------------------------------------------------------------------
1032 IF (p_source = 'Payables' ) THEN
1033 --AND p_category <> 'Treasury Confirmation') THEN
1034 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1035 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,P_SOURCE);
1036 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REFERENCE 2: '|| P_REFERENCE_2);
1037 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1038 'p_je_from_sla_flag: '||p_je_from_sla_flag);
1039 END IF;
1040
1041 BEGIN
1042
1043 IF p_je_from_sla_flag = 'Y' THEN
1044 get_reference_column ('AP_PAYMENTS',
1045 p_je_batch_id ,
1046 p_je_header_id ,
1047 p_je_line_num ,
1048 200,
1049 p_jrnl_dc_ind,
1050 party_info_tab);
1051
1052 IF gbl_err_code <> 0 THEN
1053 RETURN;
1054 END IF;
1055
1056 ELSIF (p_reference_2 IS NOT NULL) THEN
1057 SELECT v.vendor_id, v.vendor_type_lookup_code,
1058 DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
1059 'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
1060 'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
1061 'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
1062 'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
1063 'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
1064 'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
1065 'ATTRIBUTE14', V.ATTRIBUTE14, 'ATTRIBUTE15', V.ATTRIBUTE15)
1066 eliminations_id,
1067 v.vendor_name
1068 INTO l_vendor_id, l_vendor_type,
1069 l_elim_dept,
1070 l_recipient_name
1071 FROM ap_invoices_all i,
1072 po_vendors v
1073 WHERE i.invoice_id = to_number(p_reference_2)
1074 AND i.vendor_id = v.vendor_id;
1075
1076 l_cust_vend_type := 'V';
1077
1078 ELSE
1079 l_recipient_name := 'Other';
1080 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1081 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1082 'REFERENCE_2 I.E. INVOICE_ID IS NULL');
1083 END IF;
1084
1085
1086 END IF;
1087
1088 EXCEPTION
1089 WHEN NO_DATA_FOUND THEN
1090 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1091 'NO DATA FOUND FOR SOURCE = PAYABLES !!');
1092 l_recipient_name := 'Other';
1093
1094 END;
1095
1096 -------------------------------------------------------------------
1097 -- Get the Vendor ID for Purchasing Inventory Records
1098 ------------------------------------------------------------------
1099 ELSIF (p_source in ('Purchasing' , 'Cost Management')) THEN
1100 IF (p_category = 'Receiving') THEN
1101 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1102 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REFERENCE 2: '|| P_REFERENCE_2);
1103 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REFERENCE 5: '|| P_REFERENCE_5);
1104 END IF;
1105 BEGIN
1106
1107 IF p_je_from_sla_flag = 'Y' THEN
1108 l_appl_id := 201;
1109 if (p_source = 'Cost Management') then
1110 l_appl_id := 707;
1111 End if;
1112 get_reference_column ('PURCHASE_ORDER',
1113 p_je_batch_id ,
1114 p_je_header_id ,
1115 p_je_line_num ,
1116 l_appl_id,
1117 p_jrnl_dc_ind,
1118 party_info_tab);
1119 IF gbl_err_code <> 0 THEN
1120 RETURN;
1121 END IF;
1122
1123 ELSIF (p_reference_2 IS NOT NULL AND p_reference_5 IS NOT NULL) THEN
1124 SELECT v.vendor_id,
1125 v.vendor_type_lookup_code,
1126 DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
1127 'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
1128 'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
1129 'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
1130 'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
1131 'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
1132 'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
1133 'ATTRIBUTE14', V.ATTRIBUTE14, 'ATTRIBUTE15', V.ATTRIBUTE15)
1134 eliminations_id, v.vendor_name
1135 INTO l_vendor_id, l_vendor_type, l_elim_dept,
1136 l_recipient_name
1137 FROM rcv_transactions rt,
1138 po_vendors v,
1139 po_headers_all ph
1140 WHERE rt.po_header_id = to_number(p_reference_2)
1141 AND rt.transaction_id = to_number(p_reference_5)
1142 AND rt.po_header_id = ph.po_header_id
1143 AND v.vendor_id = ph.vendor_id;
1144
1145 l_cust_vend_type := 'V';
1146
1147 ELSE
1148 IF (p_reference_2 IS NULL) THEN
1149 l_recipient_name := 'Other';
1150 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1151 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1152 'REFERENCE_2 I.E. PO_HEADER_ID IS NULL');
1153 END IF;
1154 ELSE
1155 l_recipient_name := 'Other';
1156 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1157 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1158 'REFERENCE_5 I.E. TRANSACTION_ID IS NULL');
1159 END IF;
1160 END IF;
1161
1162 END IF;
1163 EXCEPTION
1164 WHEN NO_DATA_FOUND THEN
1165 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1166 'NO DATA FOUND WHEN SOURCE IS PURCHASING AND CATEGORY IS RECEIVING!!');
1167
1168 l_recipient_name := 'Other';
1169 END;
1170
1171 ELSIF (p_category = 'Purchases') THEN
1172 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1173 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,'REFERENCE 2: '|| P_REFERENCE_2);
1174 END IF;
1175 BEGIN
1176 IF p_je_from_sla_flag = 'Y' THEN
1177 get_reference_column ('PURCHASE_ORDER',
1178 p_je_batch_id ,
1179 p_je_header_id ,
1180 p_je_line_num ,
1181 201,
1182 p_jrnl_dc_ind,
1183 party_info_tab);
1184 IF gbl_err_code <> 0 THEN
1185 RETURN;
1186 END IF;
1187
1188 ELSIF (p_reference_2 IS NOT NULL) THEN
1189 SELECT v.vendor_id,
1190 v.vendor_type_lookup_code,
1191 DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
1192 'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
1193 'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
1194 'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
1195 'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
1196 'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
1197 'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
1198 'ATTRIBUTE14', V.ATTRIBUTE14,
1199 'ATTRIBUTE15', V.ATTRIBUTE15) eliminations_id,
1200 v.vendor_name
1201 INTO l_vendor_id,
1202 l_vendor_type,
1203 l_elim_dept,
1204 l_recipient_name
1205 FROM po_vendors v, po_headers_all poh
1206 WHERE poh.po_header_id = to_number(p_reference_2)
1207 AND v.vendor_id = poh.vendor_id;
1208
1209 l_cust_vend_type := 'V';
1210
1211 ELSE
1212 l_recipient_name := 'Other';
1213
1214 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1215 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1216 'REFERENCE_2 I.E. PO HEADER ID IS NULL');
1217 END IF;
1218
1219 END IF;
1220
1221 EXCEPTION
1222 WHEN NO_DATA_FOUND THEN
1223 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1224 'NO DATA FOUND WHEN SOURCE IS PURCHASING AND CATEGORY IS PURCHASES!!');
1225
1226 l_recipient_name := 'Other';
1227 END;
1228
1229 end if;
1230 -----------------------------------------------------------
1231 -- Customer id for Receivables transactions
1232 -----------------------------------------------------------
1233 ELSIF (p_source = 'Receivables') THEN
1234 log(l_module_name, 'REFERENCE 7: '|| P_REFERENCE_7);
1235 BEGIN
1236 IF p_je_from_sla_flag = 'Y' THEN
1237 get_reference_column ('RECEIPTS',
1238 p_je_batch_id ,
1239 p_je_header_id ,
1240 p_je_line_num ,
1241 222,
1242 p_jrnl_dc_ind,
1243 party_info_tab);
1244 IF gbl_err_code <> 0 THEN
1245 RETURN;
1246 END IF;
1247
1248 ELSIF (p_reference_7 IS NOT NULL) THEN
1249 l_vendor_id := p_reference_7;
1250 SELECT c.customer_class_code,
1251 DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1,
1252 'ATTRIBUTE2', C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3,
1253 'ATTRIBUTE4', C.ATTRIBUTE4, 'ATTRIBUTE5', C.ATTRIBUTE5,
1254 'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7', C.ATTRIBUTE7,
1255 'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
1256 'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11,
1257 'ATTRIBUTE12', C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13,
1258 'ATTRIBUTE14', C.ATTRIBUTE14,
1259 'ATTRIBUTE15', C.ATTRIBUTE15) eliminations_id,
1260 c.account_name
1261 INTO l_vendor_type, l_elim_dept, l_recipient_name
1262 FROM hz_cust_accounts_all c
1263 WHERE c.cust_account_id = to_number(p_reference_7);
1264
1265 l_cust_vend_type := 'C';
1266 ELSE
1267 l_recipient_name := 'Other';
1268 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1269 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1270 'REFERENCE_7 I.E. CUSTOMER_ID IS NULL');
1271 END IF;
1272 END IF;
1273 EXCEPTION
1274 WHEN NO_DATA_FOUND THEN
1275 l_recipient_name := 'Other';
1276 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR,
1277 l_module_name,'NO DATA FOUND WHEN SOURCE IS RECEIVABLES!!');
1278 END;
1279 --------------------------------------------------------------------
1280 -- Vendor id for TC transactions
1281 --------------------------------------------------------------------
1282 ELSIF (p_source = 'Budgetary Transaction' AND
1283 p_category = 'Treasury Confirmation') THEN
1284
1285 log(l_module_name, 'Source: '||P_SOURCE);
1286 log(l_module_name, 'REFERENCE 3: '|| P_REFERENCE_3);
1287 BEGIN
1288 IF p_je_from_sla_flag = 'Y' THEN
1289 get_reference_column ('TR_CONFIRM',
1290 p_je_batch_id ,
1291 p_je_header_id ,
1292 p_je_line_num ,
1293 8901,
1294 p_jrnl_dc_ind,
1295 party_info_tab);
1296 IF gbl_err_code <> 0 THEN
1297 RETURN;
1298 END IF;
1299 ELSIF (p_reference_3 IS NOT NULL) THEN
1300 l_feeder_flag := 'Y';
1301 SELECT v.vendor_id,
1302 v.vendor_type_lookup_code,
1303 DECODE(gbl_vend_attribute, 'ATTRIBUTE1', V.ATTRIBUTE1,
1304 'ATTRIBUTE2', V.ATTRIBUTE2, 'ATTRIBUTE3', V.ATTRIBUTE3,
1305 'ATTRIBUTE4', V.ATTRIBUTE4, 'ATTRIBUTE5', V.ATTRIBUTE5,
1306 'ATTRIBUTE6', V.ATTRIBUTE6, 'ATTRIBUTE7', V.ATTRIBUTE7,
1307 'ATTRIBUTE8', V.ATTRIBUTE8, 'ATTRIBUTE9', V.ATTRIBUTE9,
1308 'ATTRIBUTE10', V.ATTRIBUTE10, 'ATTRIBUTE11', V.ATTRIBUTE11,
1309 'ATTRIBUTE12', V.ATTRIBUTE12, 'ATTRIBUTE13', V.ATTRIBUTE13,
1310 'ATTRIBUTE14', V.ATTRIBUTE14,
1311 'ATTRIBUTE15', V.ATTRIBUTE15) eliminations_id,
1312 v.vendor_name
1313 INTO l_vendor_id, l_vendor_type, l_elim_dept,
1314 l_recipient_name
1315 FROM ap_checks_all apc,
1316 po_vendors v
1317 WHERE apc.vendor_id = v.vendor_id
1318 AND apc.check_id = to_number(p_reference_3);
1319
1320 l_cust_vend_type := 'V';
1321
1322 ELSE
1323 l_recipient_name := 'Other';
1324 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1325 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1326 'REFERENCE_3 I.E. CHECK_ID IS NULL');
1327 END IF;
1328 END IF;
1329
1330 EXCEPTION
1331 WHEN NO_DATA_FOUND THEN
1332 l_recipient_name := 'Other';
1333 log(l_module_name,
1334 'NO DATA FOUND WHEN SOURCE = PAYABLES AND
1335 CATEGORY = TREASURY CONFIRMATION !!');
1336 END;
1337 --------------------------------------------------------------------
1338 -- Vendor id for Budgetary transactions
1339 --------------------------------------------------------------------
1340 ELSIF (p_source = 'Budgetary Transaction' AND
1341 p_category <> 'Treasury Confirmation') THEN
1342 log(l_module_name,'Source: '||p_source);
1343 BEGIN
1344 l_feeder_flag := 'Y';
1345 IF p_je_from_sla_flag = 'Y' THEN
1346 get_reference_column ('BE_TRANSACTIONS',
1347 p_je_batch_id ,
1348 p_je_header_id ,
1349 p_je_line_num ,
1350 8901,
1351 p_jrnl_dc_ind,
1352 party_info_tab);
1353 IF gbl_err_code <> 0 THEN
1354 RETURN;
1355 END IF;
1356
1357 ELSE
1358 SELECT decode(gbl_trading_partner_att,
1359 null , dept_id|| main_account,
1360 SUBSTR(DECODE(upper(gbl_trading_partner_att),
1361 'ATTRIBUTE1', attribute1,
1362 'ATTRIBUTE2', attribute2,
1363 'ATTRIBUTE3', attribute3,
1364 'ATTRIBUTE4', attribute4,
1365 'ATTRIBUTE5', attribute5,
1366 'ATTRIBUTE6', attribute6,
1367 'ATTRIBUTE7', attribute7,
1368 'ATTRIBUTE8', attribute8,
1369 'ATTRIBUTE9', attribute9,
1370 'ATTRIBUTE10', attribute10,
1371 'ATTRIBUTE11', attribute11,
1372 'ATTRIBUTE12', attribute12,
1373 'ATTRIBUTE13', attribute13,
1374 'ATTRIBUTE14', attribute14,
1375 'ATTRIBUTE15', attribute15),1,6))
1376 INTO l_elim_dept
1377 FROM fv_be_trx_dtls
1378 WHERE transaction_id = l_be_trx_id;
1379
1380 IF (l_elim_dept IS NOT NULL) THEN
1381 l_vendor_id := l_elim_dept;
1382 l_vendor_type := 'FEDERAL';
1383 END IF;
1384
1385 l_recipient_name := 'Other';
1386 END IF;
1387 EXCEPTION
1388 WHEN NO_DATA_FOUND THEN
1389 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
1390 'NO DATA FOUND WHEN SOURCE = BUDGETARY TRANSACTION');
1391 END;
1392
1393 ELSIF (l_govt_non_govt_ind = 'Y') THEN
1394 log(l_module_name , 'Falls on Manual source as govt non govt = Y');
1395 IF (ln_jrnl_att is NOT NULL) THEN
1396 l_elim_dept := ln_jrnl_att;
1397 ELSE
1398 l_elim_dept := NULL;
1399 END IF;
1400 l_feeder_flag := 'N';
1401 gbl_manual_source_flag := 'Y';
1402
1403 ELSIF (l_govt_non_govt_ind = 'F') THEN
1404 log(l_module_name , 'Falls on Manual source as govt non govt = F');
1405 IF (ln_jrnl_att is NOT NULL) THEN
1406 l_elim_dept := ln_jrnl_att;
1407 ELSE
1408 l_elim_dept := '00';
1409 END IF;
1410 l_feeder_flag := 'N';
1411 gbl_manual_source_flag := 'Y';
1412
1413 ELSE
1414 l_valid_flag := 'N'; -- all the process ends here
1415 END IF;
1416
1417 EXCEPTION
1418 WHEN NO_DATA_FOUND THEN
1419 l_valid_flag := 'Y';
1420 WHEN Invalid_Number OR Value_Error THEN
1421 l_valid_flag := 'Y';
1422 END;
1423
1424 IF l_recipient_name IS NULL THEN
1425 l_recipient_name := 'Other';
1426 END IF;
1427
1428 -- If the record is not from sla (ie. upgraded records)
1429 -- OR if the record is from a manual journal source -- for bug 6782416
1430 IF (NVL(p_je_from_sla_flag,'N') <> 'Y' OR
1431 gbl_manual_source_flag = 'Y') THEN
1432 log(l_module_name, 'Not sla journal or it is a manual journal - creating 1 record');
1433 party_info_tab.delete;
1434 party_info_tab(1).party_id := l_vendor_id;
1435 party_info_tab(1).vendor_type := l_vendor_type;
1436 party_info_tab(1).recipient_name := l_recipient_name;
1437 if (l_elim_dept is not null) then
1438 party_info_tab(1).elim_dept := substr(l_elim_dept,1,6);
1439 else
1440 party_info_tab(1).elim_dept := null;
1441 End if;
1442 party_info_tab(1).feeder_flag := l_feeder_flag;
1443 party_info_tab(1).cust_vend_type := l_cust_vend_type;
1444 END IF;
1445
1446 IF l_valid_flag = 'Y' THEN -- valid Flag
1447
1448 FOR i IN party_info_tab.FIRST..party_info_tab.LAST LOOP
1449
1450 -- If there is a standard journal and we don't find the vendor/customer and
1451 -- therefore cannot determine the eliminations id, we should use the journal
1452 -- trading partner descriptive flexfield value.
1453 IF (party_info_tab(i).feeder_flag = 'Y') THEN
1454 IF (party_info_tab(i).party_id IS NULL) THEN
1455 IF (gbl_jrnl_attribute IS NOT NULL) THEN
1456 party_info_tab(i).elim_dept := ln_jrnl_att;
1457 END IF;
1458 END IF;
1459 END IF;
1460
1461 IF (party_info_tab(i).party_id IS NULL) THEN
1462 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1463 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT,
1464 l_module_name, 'VENDOR ID IS NULL');
1465 END IF;
1466 IF ((l_govt_non_govt_ind = 'F' AND l_feeder_flag = 'Y') OR
1467 (l_govt_non_govt_ind = 'F' AND l_feeder_flag = 'N'
1468 AND party_info_tab(i).elim_dept = '00')) THEN
1469 IF (party_info_tab(i).elim_dept IS NULL OR
1470 party_info_tab(i).elim_dept = '00') THEN
1471 party_info_tab(i).reported_status := 'W';
1472 party_info_tab(i).record_category := 'NO_VENDOR';
1473 party_info_tab(i).elim_dept := '00';
1474 ELSE
1475 party_info_tab(i).record_category := 'REPORTED';
1476 END IF;
1477
1478 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1479 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1480 'Recipient Name: ' || party_info_tab(i).recipient_name);
1481 END IF;
1482
1483 -- Govt Non Govt Indicator = Y
1484 ELSIF ((l_govt_non_govt_ind = 'Y' AND l_feeder_flag = 'Y') OR
1485 (l_govt_non_govt_ind = 'Y' AND l_feeder_flag = 'N')) THEN
1486 IF (party_info_tab(i).elim_dept IS NULL) THEN
1487 party_info_tab(i).reported_status := 'W';
1488 party_info_tab(i).record_category := 'NO_VENDOR';
1489 party_info_tab(i).elim_dept := ' ';
1490 party_info_tab(i).g_ng_indicator := 'N';
1491 ELSE
1492 party_info_tab(i).reported_status := 'R';
1493 party_info_tab(i).record_category := 'REPORTED';
1494 party_info_tab(i).g_ng_indicator := 'F';
1495 END IF;
1496
1497 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1498 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1499 'Recipient Name: ' || party_info_tab(i).recipient_name);
1500 END IF;
1501
1502 END IF; -- Govt Non Govt = F or Y
1503
1504 ELSE -- l_vendor_id IS NOT NULL
1505
1506 IF (l_feeder_flag = 'Y') THEN
1507 IF (l_govt_non_govt_ind = 'F' AND
1508 (UPPER(party_info_tab(i).vendor_type) <> 'FEDERAL' OR
1509 party_info_tab(i).vendor_type IS NULL)) THEN
1510 IF (party_info_tab(i).elim_dept IS NULL) THEN
1511 party_info_tab(i).reported_status := 'W';
1512 party_info_tab(i).record_category := 'G_NONFED_VENDOR';
1513 party_info_tab(i).elim_dept := '00';
1514 ELSE
1515 party_info_tab(i).reported_status := 'W';
1516 party_info_tab(i).record_category := 'G_NONFED_VENDOR';
1517 END IF;
1518
1519 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1520 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1521 ' RECIPIENT NAME: ' || party_info_tab(i).RECIPIENT_NAME);
1522 END IF;
1523
1524 ELSIF (l_govt_non_govt_ind = 'F' and party_info_tab(i).elim_dept IS NULL) THEN
1525
1526 party_info_tab(i).reported_status := 'R';
1527 party_info_tab(i).elim_dept := '00';
1528 --l_govt_non_govt_ind := 'F';
1529 party_info_tab(i).g_ng_indicator := 'F';
1530 party_info_tab(i).record_category := 'REPORTED';
1531
1532 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1533 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1534 ' RECIPIENT NAME: ' || party_info_tab(i).RECIPIENT_NAME);
1535 END IF;
1536
1537 ELSIF (l_govt_non_govt_ind = 'F' and party_info_tab(i).elim_dept IS NOT NULL) THEN
1538 party_info_tab(i).reported_status := 'R';
1539 party_info_tab(i).record_category := 'REPORTED';
1540 --l_govt_non_govt_ind := 'F';
1541 party_info_tab(i).g_ng_indicator := 'F';
1542 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1543 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1544 ' GNG: F');
1545 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1546 ' RECIPIENT NAME: ' || party_info_tab(i).RECIPIENT_NAME);
1547 END IF;
1548
1549 ELSIF (l_govt_non_govt_ind = 'Y' AND
1550 (UPPER(party_info_tab(i).vendor_type) <> 'FEDERAL' OR
1551 party_info_tab(i).vendor_type IS NULL)) THEN
1552 party_info_tab(i).reported_status := 'R';
1553 party_info_tab(i).record_category := 'REPORTED';
1554 --l_govt_non_govt_ind := 'N';
1555 party_info_tab(i).g_ng_indicator := 'N';
1556 party_info_tab(i).elim_dept := ' ';
1557
1558 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1559 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1560 ' RECIPIENT NAME: ' || party_info_tab(i).RECIPIENT_NAME);
1561 END IF;
1562
1563 ELSIF (l_govt_non_govt_ind = 'Y' and party_info_tab(i).elim_dept IS NULL) THEN
1564
1565 party_info_tab(i).reported_status := 'R';
1566 party_info_tab(i).record_category := 'REPORTED';
1567 --l_govt_non_govt_ind := 'F';
1568 party_info_tab(i).g_ng_indicator := 'F';
1569 party_info_tab(i).elim_dept := '00';
1570
1571 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1572 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1573 ' RECIPIENT NAME: ' || party_info_tab(i).RECIPIENT_NAME);
1574 END IF;
1575
1576 ELSIF (l_govt_non_govt_ind = 'Y' and party_info_tab(i).elim_dept IS NOT NULL) THEN
1577
1578 party_info_tab(i).reported_status := 'R';
1579 party_info_tab(i).record_category := 'REPORTED';
1580 --l_govt_non_govt_ind := 'F';
1581 party_info_tab(i).g_ng_indicator := 'F';
1582
1583 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1584 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1585 ' IN VIEW');
1586 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1587 ' VENDOR ID IS NOT NULL (' || party_info_tab(i).party_ID || ')');
1588 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1589 ' ELIMINATIONS ID IS NOT NULL');
1590 END IF;
1591
1592 END IF; -- l_govt_non_govt_ind
1593 END IF; -- l_feeder_flag
1594 END IF; -- l_vendor_id
1595
1596 IF (l_feeder_flag = 'N') THEN
1597 IF (l_govt_non_govt_ind = 'F' AND
1598 party_info_tab(i).elim_dept <> '00' AND
1599 party_info_tab(i).elim_dept IS NOT NULL)
1600 THEN
1601 party_info_tab(i).reported_status := 'R';
1602 --l_govt_non_govt_ind := 'F';
1603 party_info_tab(i).g_ng_indicator := 'F';
1604 party_info_tab(i).record_category := 'REPORTED';
1605
1606 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1607 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1608 ' IN VIEW');
1609 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1610 ' ELIMINATIONS ID IS NOT NULL');
1611 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,
1612 ' RECIPIENT NAME: ' || party_info_tab(i).RECIPIENT_NAME);
1613 END IF;
1614
1615 END IF; -- l_govt_non_govt_ind
1616 END IF; -- l_feeder_system
1617
1618 log(l_module_name, 'Ending get_party_info with....');
1619 log(l_module_name, 'party_id :'|| party_info_tab(i).party_id);
1620 log(l_module_name, 'p_vendor_type :'||party_info_tab(i).vendor_type);
1621 log(l_module_name, 'p_recipient_name :'||party_info_tab(i).recipient_name);
1622 log(l_module_name, 'p_elim :'||party_info_tab(i).elim_dept);
1623 log(l_module_name, 'p_reported_status :'||party_info_tab(i).reported_status);
1624 log(l_module_name, 'p_record_category :'||party_info_tab(i).record_category);
1625 log(l_module_name, 'p_feeder_flag :'||party_info_tab(i).feeder_flag);
1626
1627 p_party_info_tab := party_info_tab;
1628
1629 END LOOP;
1630 END IF; -- l_valid_flag
1631 /*
1632 p_party_id := l_vendor_id;
1633 p_vendor_type := l_vendor_type;
1634 p_recipient_name := l_recipient_name;
1635 p_elim_dept := substr(l_elim_dept,1,6);
1636 p_reported_status := l_reported_status;
1637 p_record_category := l_record_category;
1638 p_feeder_flag := l_feeder_flag;
1639 p_g_ng_indicator := l_govt_non_govt_ind;
1640 */
1641
1642 EXCEPTION
1643 WHEN OTHERS THEN
1644 gbl_err_code := 2 ;
1645 gbl_err_buff := 'GET_PARTY_INFO - Exception (Others) - ' ||
1646 to_char(SQLCODE) || ' - ' || SQLERRM;
1647
1648 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1649 '.final_exception',gbl_err_buff);
1650
1651 END get_party_info;
1652 --------------------------------------------------------------------------------
1653 PROCEDURE INSERT_EXCEPTION_RECS
1654 IS
1655
1656 l_module_name VARCHAR2(100);
1657 l_stmt varchar2(5000);
1658 BEGIN
1659
1660 l_module_name := g_module_name||'INSERT_EXCEPTION_RECS';
1661 FV_UTILITY.LOG_MESG('In '||l_module_name);
1662 gbl_exception_rec_count := 0;
1663
1664 fnd_file.put_line(fnd_file.output , 'Set of books id ' || gbl_sob_id || rpad(' ', 70) || ' Period : ' || gbl_period_name);
1665
1666 fnd_file.put_line(fnd_file.output , ' Fact-1 Journals that do not have Vendor/Supplier information or Vendor/Customer type is NON FEDERAL ');
1667 fnd_file.put_line(fnd_file.output , '---------------------------------------- ----------------------------------------------------------');
1668 fnd_file.put_line(fnd_file.output ,rpad('Exception' , 15) || rpad('Batch Name', 41) || rpad('Category' , 16) || rpad('Account',10) || rpad('Fund ', 21) || ' Amount' );
1669
1670 fnd_file.put_line(fnd_file.output,' ');
1671
1672 l_stmt := ' select substr(rpad(l.record_category, 14,'' ''),1,14) || '' '' ||
1673 substr(rpad(b.name, 40, '' ''),1,40) || '' '' ||
1674 substr(rpad(h.je_category, 15, '' ''),1,15) || '' '' ||
1675 substr(rpad(l.account_number, 9, '' ''),1,9) || '' '' ||
1676 substr(rpad(l.fund_value, 20, '' ''),1,20) || '' '' ||
1677 to_char( amount,''99,999,999,999,999,999.99'') ' ||
1678 ' FROM fv_facts1_header_id_gt f ,
1679 gl_je_headers h,
1680 fv_facts1_line_balances l,
1681 gl_je_batches b
1682 where h.je_header_id = f.je_header_id
1683 and l.je_header_id = h.je_header_id
1684 and l.record_category IN (''NO_VENDOR'', ''G_NONFED_VENDOR'')
1685 AND l.set_of_books_id = ' || gbl_sob_id || '
1686 AND l.period_year = ' || gbl_period_year || '
1687 and b.je_batch_id = h.je_batch_id
1688 ORDER BY l.record_category, h.je_category ';
1689
1690 fv_flatfiles.create_flat_file(l_stmt);
1691
1692 EXCEPTION WHEN OTHERS THEN
1693 gbl_err_code := 2 ;
1694 gbl_err_buff := l_module_name||' - When others exception - ' ||
1695 to_char(SQLCODE) || ' - ' || SQLERRM;
1696 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_UNEXPECTED, l_module_name||
1697 gbl_err_buff);
1698
1699 END insert_exception_recs;
1700 --------------------------------------------------------------------------------
1701 PROCEDURE SUBMIT_EXCEPTION_REPORT
1702 IS
1703 l_req_id NUMBER(15);
1704
1705
1706 call_status BOOLEAN;
1707
1708 rphase VARCHAR2(80);
1709 rstatus VARCHAR2(80);
1710 dphase VARCHAR2(80);
1711 dstatus VARCHAR2(80);
1712 message VARCHAR2(80);
1713 l_module_name VARCHAR2(80) ;
1714 l_run_mode VARCHAR2(80) ;
1715
1716 BEGIN
1717
1718 l_module_name := g_module_name||'SUBMIT_EXCEPTION_REPORT';
1719 FV_UTILITY.LOG_MESG('In '||l_module_name);
1720
1721 l_run_mode := 'Fiscal Year';
1722
1723
1724
1725 FV_UTILITY.LOG_MESG(l_module_name|| ' Launching FACTS I exception report ...');
1726 FV_UTILITY.LOG_MESG(l_module_name|| ' l_run_mode: '||l_run_mode);
1727 FV_UTILITY.LOG_MESG(l_module_name|| ' gbl_period_year: '||gbl_period_year);
1728 FV_UTILITY.LOG_MESG(l_module_name|| ' gbl_sob_id: '||gbl_sob_id);
1729 FV_UTILITY.LOG_MESG(l_module_name|| ' gbl_period_name: '||gbl_period_name);
1730
1731 l_req_id := FND_REQUEST.SUBMIT_REQUEST
1732 ('FV','FVFACTSE','','',FALSE, l_run_mode, gbl_period_year,
1733 gbl_sob_id, gbl_period_name);
1734
1735 -- If concurrent request submission failed, abort process
1736 FV_UTILITY.LOG_MESG(l_module_name|| ' Request ID for exception report = '|| TO_CHAR(L_REQ_ID));
1737
1738 IF (l_req_id = 0) THEN
1739 gbl_err_code := '2';
1740 gbl_err_buff := 'Cannot submit FACTS Exception report';
1741 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,gbl_err_buff);
1742 RETURN;
1743 ELSE
1744 COMMIT;
1745 call_status := Fnd_concurrent.Wait_for_request(l_req_id, 20, 0,
1746 rphase, rstatus,
1747 dphase, dstatus, message);
1748 IF call_status = FALSE THEN
1749 gbl_err_buff := 'Cannot wait for the status of FACTS Exception Report';
1750 gbl_err_code := -1;
1751 FV_UTILITY.LOG_MESG(l_module_name||'.error4', gbl_err_buff) ;
1752 RETURN;
1753 END IF;
1754 END IF;
1755
1756 END submit_exception_report;
1757 --------------------------------------------------------------------------------
1758
1759 PROCEDURE UPDATE_FACTS1_RUN IS
1760 l_module_name VARCHAR2(200);
1761
1762
1763 BEGIN
1764
1765 l_module_name := g_module_name || 'UPDATE_FACTS1_RUN';
1766 FV_UTILITY.LOG_MESG('In '||l_module_name);
1767
1768 fv_utility.log_mesg('Inserting processed headers ' );
1769
1770 IF gbl_called_from_main = 'Y' THEN
1771 INSERT INTO fv_facts1_processed_je_hdrs(je_header_id,set_of_books_id)
1772 SELECT DISTINCT je_header_id,set_of_books_id
1773 FROM fv_facts1_header_id_gt;
1774
1775 ELSE
1776 Insert into fv_facts1_processed_je_hdrs(je_header_id,set_of_books_id)
1777 select je_header_id,set_of_books_id
1778 from fv_facts1_header_id_gt;
1779
1780 END IF;
1781
1782 fv_utility.log_mesg('Inserted ' || SQL%ROWCOUNT);
1783
1784
1785 fv_utility.log_mesg('Updating Facts1 run ' );
1786
1787 UPDATE fv_facts1_run
1788 SET process_date = sysdate,
1789 jc_run_month = gbl_period_num_high,
1790 run_fed_flag = 'J'
1791 WHERE set_of_books_id = gbl_sob_id
1792 AND fiscal_year = gbl_period_year
1793 AND table_indicator = 'N';
1794
1795
1796 --Update the fv_facts1_fed_account as processed as of date.
1797
1798 fv_utility.log_mesg('Updating fv_facts1_fed_accounts ' );
1799 UPDATE fv_facts1_fed_accounts
1800 SET jc_flag = 'Y'
1801 WHERE set_of_books_id = gbl_sob_id
1802 AND fiscal_year = gbl_period_year;
1803
1804
1805 EXCEPTION
1806 WHEN NO_DATA_FOUND THEN
1807 NULL;
1808
1809 WHEN OTHERS THEN
1810 gbl_err_code := SQLCODE;
1811 gbl_err_buff := SQLERRM ||
1812 'When others error in UPDATE_FACTS1_RUN - '||SQLERRM;
1813 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_err_buff);
1814
1815 END UPDATE_FACTS1_RUN;
1816 --------------------------------------------------------------------------------
1817 PROCEDURE get_reference_column (p_entity_code IN VARCHAR2,
1818 p_je_batch_id IN NUMBER,
1819 p_je_header_id IN NUMBER,
1820 p_je_line_num IN NUMBER,
1821 p_application_id IN NUMBER,
1822 p_jrnl_dc_indicator IN VARCHAR2,
1823 p_party_info_tab OUT NOCOPY party_info_table) IS
1824
1825
1826 CURSOR be_cursor IS
1827 SELECT xd.source_distribution_id_num_1 transaction_id,
1828 (NVL(xd.unrounded_accounted_dr,0) -
1829 NVL(xd.unrounded_accounted_cr,0)) amount
1830 FROM gl_import_references gli,
1831 xla_ae_lines xl,
1832 xla_ae_headers xh,
1833 xla_distribution_links xd
1834 WHERE gli.je_batch_id = p_je_batch_id
1835 AND gli.je_header_id = p_je_header_id
1836 AND gli.je_line_num = p_je_line_num
1837 AND xl.gl_sl_link_id = gli.gl_sl_link_id
1838 AND xl.application_id = 8901
1839 AND xh.ae_header_id = xl.ae_header_id
1840 AND xl.ledger_id = gbl_sob_id
1841 AND xd.event_id = xh.event_id
1842 and xd.ae_header_id = xh.ae_header_id
1843 and xd.ae_line_num = xl.ae_line_num;
1844
1845
1846 CURSOR other_source_cur IS
1847 SELECT ael.party_id,
1848 NVL(ael.accounted_dr,0) -
1849 NVL(ael.accounted_cr,0) amount
1850 FROM gl_import_references i,
1851 xla_ae_lines ael
1852 WHERE i.je_batch_id = p_je_batch_id
1853 AND i.je_header_id = p_je_header_id
1854 AND i.je_line_num = p_je_line_num
1855 AND i.gl_sl_link_id = ael.gl_sl_link_id
1856 AND ael.application_id = p_application_id
1857 AND ael.ledger_id = gbl_sob_id;
1858
1859 i INTEGER := 1;
1860
1861 l_module_name VARCHAR2(200) := g_module_name || 'GET_REFERENCE_COLUMN';
1862
1863 BEGIN
1864 log(l_module_name, 'In '||l_module_name);
1865 log(l_module_name,'Source: '||p_entity_code);
1866 log(l_module_name,'Je Batch ID: '||p_je_batch_id);
1867 log(l_module_name,'Je Header ID: '||p_je_header_id);
1868 log(l_module_name,'Je Line Num: '||p_je_line_num);
1869 log(l_module_name,'Application ID: '||p_application_id);
1870
1871 IF p_entity_code <> ('BE_TRANSACTIONS') THEN
1872 FOR other_source_rec IN other_source_cur
1873 LOOP
1874
1875 --log(l_module_name,'p_party_id: '||other_source_rec.party_id);
1876
1877 p_party_info_tab(i).feeder_flag := 'Y';
1878
1879 IF other_source_rec.party_id IS NOT NULL THEN
1880 IF p_entity_code IN ('PURCHASE_ORDER', 'AP_PAYMENTS',
1881 'TR_CONFIRM') THEN
1882 BEGIN
1883 SELECT vendor_id, vendor_type_lookup_code,
1884 DECODE(gbl_vend_attribute, 'ATTRIBUTE1', ATTRIBUTE1,
1885 'ATTRIBUTE2', ATTRIBUTE2, 'ATTRIBUTE3', ATTRIBUTE3,
1886 'ATTRIBUTE4', ATTRIBUTE4, 'ATTRIBUTE5', ATTRIBUTE5,
1887 'ATTRIBUTE6', ATTRIBUTE6, 'ATTRIBUTE7', ATTRIBUTE7,
1888 'ATTRIBUTE8', ATTRIBUTE8, 'ATTRIBUTE9', ATTRIBUTE9,
1889 'ATTRIBUTE10', ATTRIBUTE10, 'ATTRIBUTE11', ATTRIBUTE11,
1890 'ATTRIBUTE12', ATTRIBUTE12, 'ATTRIBUTE13', ATTRIBUTE13,
1891 'ATTRIBUTE14', ATTRIBUTE14, 'ATTRIBUTE15', ATTRIBUTE15)
1892 eliminations_id, vendor_name, other_source_rec.amount
1893 INTO p_party_info_tab(i).party_id, p_party_info_tab(i).vendor_type,
1894 P_party_info_tab(i).elim_dept, p_party_info_tab(i).recipient_name,
1895 P_party_info_tab(i).party_line_amount
1896 FROM ap_suppliers
1897 WHERE vendor_id = other_source_rec.party_id;
1898
1899 p_party_info_tab(i).cust_vend_type := 'V';
1900
1901 EXCEPTION WHEN NO_DATA_FOUND THEN
1902 FV_UTILITY.LOG_MESG('No data found in ap_suppliers for vendor_id: '||
1903 other_source_rec.party_id);
1904 p_party_info_tab(i).party_id := other_source_rec.party_id;
1905 p_party_info_tab(i).cust_vend_type := 'V';
1906 P_party_info_tab(i).party_line_amount := other_source_rec.amount;
1907 p_party_info_tab(i).recipient_name := 'Other';
1908 END;
1909
1910 ELSIF p_entity_code = 'RECEIPTS' THEN
1911 BEGIN
1912 SELECT c.party_id, c.customer_class_code,
1913 DECODE(gbl_cust_attribute, 'ATTRIBUTE1', C.ATTRIBUTE1,
1914 'ATTRIBUTE2', C.ATTRIBUTE2, 'ATTRIBUTE3', C.ATTRIBUTE3,
1915 'ATTRIBUTE4', C.ATTRIBUTE4, 'ATTRIBUTE5', C.ATTRIBUTE5,
1916 'ATTRIBUTE6', C.ATTRIBUTE6, 'ATTRIBUTE7', C.ATTRIBUTE7,
1917 'ATTRIBUTE8', C.ATTRIBUTE8, 'ATTRIBUTE9', C.ATTRIBUTE9,
1918 'ATTRIBUTE10', C.ATTRIBUTE10, 'ATTRIBUTE11', C.ATTRIBUTE11,
1919 'ATTRIBUTE12', C.ATTRIBUTE12, 'ATTRIBUTE13', C.ATTRIBUTE13,
1920 'ATTRIBUTE14', C.ATTRIBUTE14,
1921 'ATTRIBUTE15', C.ATTRIBUTE15) eliminations_id,
1922 c.account_name, other_source_rec.amount
1923 INTO p_party_info_tab(i).party_id, p_party_info_tab(i).vendor_type,
1924 p_party_info_tab(i).elim_dept, p_party_info_tab(i).recipient_name,
1925 P_party_info_tab(i).party_line_amount
1926 FROM hz_cust_accounts_all c
1927 WHERE c.cust_account_id = other_source_rec.party_id;
1928
1929 p_party_info_tab(i).cust_vend_type := 'C';
1930 EXCEPTION WHEN NO_DATA_FOUND THEN
1931 FV_UTILITY.LOG_MESG('No data found in hz_cust_accounts_all for party_id: '||
1932 other_source_rec.party_id);
1933 p_party_info_tab(i).party_id := other_source_rec.party_id;
1934 p_party_info_tab(i).cust_vend_type := 'C';
1935 P_party_info_tab(i).party_line_amount := other_source_rec.amount;
1936 p_party_info_tab(i).recipient_name := 'Other';
1937 END;
1938
1939 END IF;
1940
1941 ELSE
1942 p_party_info_tab(i).party_line_amount := other_source_rec.amount;
1943
1944 END IF;
1945
1946 log(l_module_name,'party_id: '||p_party_info_tab(i).party_id);
1947 log(l_module_name,'party_type: '||p_party_info_tab(i).vendor_type);
1948 log(l_module_name,'elim_dept: '|| p_party_info_tab(i).elim_dept);
1949 log(l_module_name,'party_name: '||p_party_info_tab(i).recipient_name);
1950 log(l_module_name,'cust or vend: '||p_party_info_tab(i).cust_vend_type);
1951 log(l_module_name,'line_amount: '||p_party_info_tab(i).party_line_amount);
1952
1953 i := i + 1;
1954 END LOOP;
1955
1956 ELSIF p_entity_code = 'BE_TRANSACTIONS' THEN
1957 -- using party_id for storing transaction id
1958 FOR be_rec IN be_cursor
1959 LOOP
1960
1961 p_party_info_tab(i).feeder_flag := 'Y';
1962 BEGIN
1963 SELECT decode(gbl_trading_partner_att, null,
1964 dept_id|| main_account,
1965 SUBSTR(DECODE(UPPER(gbl_trading_partner_att),
1966 'ATTRIBUTE1', attribute1,
1967 'ATTRIBUTE2', attribute2,
1968 'ATTRIBUTE3', attribute3,
1969 'ATTRIBUTE4', attribute4,
1970 'ATTRIBUTE5', attribute5,
1971 'ATTRIBUTE6', attribute6,
1972 'ATTRIBUTE7', attribute7,
1973 'ATTRIBUTE8', attribute8,
1974 'ATTRIBUTE9', attribute9,
1975 'ATTRIBUTE10', attribute10,
1976 'ATTRIBUTE11', attribute11,
1977 'ATTRIBUTE12', attribute12,
1978 'ATTRIBUTE13', attribute13,
1979 'ATTRIBUTE14', attribute14,
1980 'ATTRIBUTE15', attribute15),1,6))
1981 INTO p_party_info_tab(i).elim_dept
1982 FROM fv_be_trx_dtls
1983 WHERE transaction_id = be_rec.transaction_id;
1984
1985 p_party_info_tab(i).party_id := p_party_info_tab(i).elim_dept;
1986 p_party_info_tab(i).party_line_amount := be_rec.amount;
1987 p_party_info_tab(i).recipient_name := 'Other';
1988
1989 IF (p_party_info_tab(i).elim_dept IS NOT NULL) THEN
1990 p_party_info_tab(i).party_id := p_party_info_tab(i).elim_dept;
1991 p_party_info_tab(i).vendor_type := 'FEDERAL';
1992 END IF;
1993
1994 log(l_module_name,'elim_dept: '||p_party_info_tab(i).elim_dept);
1995 log(l_module_name,'party_id: '||p_party_info_tab(i).party_id);
1996 log(l_module_name,'line_amount: '||p_party_info_tab(i).party_line_amount);
1997 log(l_module_name,'vendor_type: '||p_party_info_tab(i).vendor_type);
1998
1999 EXCEPTION WHEN NO_DATA_FOUND THEN
2000 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name,
2001 'NO DATA FOUND WHEN SOURCE = :'||p_entity_code);
2002 p_party_info_tab(i).party_line_amount := be_rec.amount;
2003 p_party_info_tab(i).recipient_name := 'Other';
2004 END;
2005 i := i + 1;
2006 END LOOP;
2007 END IF;
2008 EXCEPTION
2009 WHEN NO_DATA_FOUND THEN
2010 gbl_err_code := SQLCODE;
2011 gbl_err_buff :=
2012 'No data found error in get_reference_column - '||SQLERRM;
2013 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_err_buff);
2014 WHEN OTHERS THEN
2015 gbl_err_code := SQLCODE;
2016 gbl_err_buff :=
2017 'When others error in get_reference_column - '||SQLERRM;
2018 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_ERROR, l_module_name, gbl_err_buff);
2019 END get_reference_column;
2020 --------------------------------------------------------------------------------
2021 BEGIN
2022 g_module_name := 'fv.plsql.FV_FACTS1_GL_PKG.';
2023 --------------------------------------------------------------------------------
2024 END fv_facts1_gl_pkg;