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