1 PACKAGE BODY fv_gtas_agency_id_upgrade AS
2 /* $Header: FVGTAIDUPB.pls 120.13.12020000.5 2013/03/04 15:36:42 snama noship $ */
3
4 g_err_code NUMBER := 0;
5 g_err_buff VARCHAR2(500);
6 g_module VARCHAR2(200);
7
8
9 PROCEDURE upgrade_party_agency_id;
10 PROCEDURE upgrade_fed_accounts_agency_id;
11 PROCEDURE upgrade_budget_codes;
12 PROCEDURE upgrade_fednonfed_code;
13 PROCEDURE migrate_gtas_data;
14 PROCEDURE log(module IN VARCHAR2,
15 message_line IN VARCHAR2);
16 PROCEDURE delete_facts_prc_dtl;
17
18 PROCEDURE MAIN(p_err_buff OUT NOCOPY VARCHAR2,
19 p_err_code OUT NOCOPY NUMBER) IS
20
21 l_module VARCHAR2(200) := g_module||'MAIN';
22
23 BEGIN
24 log(l_module, 'Begin');
25 --Upgrade vendor/customer agency id
26 upgrade_party_agency_id;
27
28 --Upgrade agency id in the TAS form
29 IF g_err_code = 0 THEN
30 upgrade_fed_accounts_agency_id;
31 END IF;
32
33 --Upgrade fed nonfed code in suppliers table
34 IF g_err_code = 0 THEN
35 upgrade_fednonfed_code;
36 END IF;
37
38 --Migrate GTAS data
39 IF g_err_code = 0 THEN
40 migrate_gtas_data;
41 END IF;
42
43 --Delete facts prc dtl data
44 IF g_err_code = 0 THEN
45 delete_facts_prc_dtl;
46 END IF;
47
48 --No Treasury guidance on this, so not using this
49 --ugrade now.
50 --Upgrade budget account codes
51 --IF g_err_code = 0 THEN
52 -- upgrade_budget_codes;
53 --END IF;
54
55 IF g_err_code <> 0 THEN
56 p_err_code := g_err_code;
57 p_err_buff := g_err_buff;
58 ROLLBACK;
59 RETURN;
60 END IF;
61
62 COMMIT;
63
64 log(l_module, 'End');
65 EXCEPTION WHEN OTHERS THEN
66 fnd_file.put_line(fnd_file.log, 'When others error: '||sqlerrm);
67 p_err_buff := 'When others error: '||sqlerrm;
68 p_err_code := sqlcode;
69 END main;
70 -------------------------------------------------------------------------
71 PROCEDURE UPGRADE_PARTY_AGENCY_ID IS
72 TYPE aid_select_cur IS REF CURSOR;
73 c_aid_select aid_select_cur;
74 l_cust_col_exists varchar2(1) := 'N';
75 l_blank_line VARCHAR2(1) := ' ';
76 l_gtas_cust_attribute fv_system_parameters.gtas_customer_attribute%TYPE;
77 l_facts1_cust_attribute fv_system_parameters.factsI_customer_attribute%TYPE;
78 l_facts1_vend_attribute fv_system_parameters.gtas_customer_attribute%TYPE;
79 NO_GTAS_ATTR_COL_FOUND EXCEPTION;
80 NO_FACTS1_ATTR_COL_FOUND EXCEPTION;
81 l_col_select varchar2(5000);
82 l_where_clause varchar2(5000);
83 l_attrib varchar2(30);
84 l_table varchar2(30);
85 l_party_type VARCHAR2(25);
86 l_hdr_printed BOOLEAN := FALSE;
87 l_sub_hdr_printed BOOLEAN := FALSE;
88 l_aid_select varchar2(5000);
89 l_account_id hz_cust_accounts_all.cust_account_id%TYPE;
90 l_account_name hz_cust_accounts_all.account_name%TYPE;
91 l_agency_id VARCHAR2(2);
92 l_main_acct VARCHAR2(4);
93 l_update_statement varchar2(5000);
94 l_err_string varchar2(500);
95 l_tab_owner all_tab_columns.owner%TYPE;
96 l_party_name VARCHAR2(80);
97
98
99 BEGIN
100 --For 12.2 hz_cust_accounts table should contain two new columns
101 --FEDERAL_ENTITY_TYPE and TRADING_PARTNER_AGENCY_ID
102 --If the columns do not exist, then the env is 12.0 or 12.1, in which
103 --case we have to use the new gtas_customer_attribute column
104 BEGIN
105
106 --Get the owner of the table hz_cust_accounts
107 --App id of AR is 222
108 SELECT product_code
109 INTO l_tab_owner
110 FROM fnd_application
111 WHERE application_id = 222;
112 fnd_file.put_line(fnd_file.log, 'Table owner: '||l_tab_owner);
113
114 SELECT 'Y'
115 INTO l_cust_col_exists
116 FROM all_tab_columns
117 WHERE table_name = 'HZ_CUST_ACCOUNTS'
118 AND column_name = 'TRADING_PARTNER_AGENCY_ID'
119 AND owner = l_tab_owner;
120 fnd_file.put_line(fnd_file.log, l_blank_line);
121 fnd_file.put_line(fnd_file.log, 'New customer column exists.');
122
123 EXCEPTION WHEN NO_DATA_FOUND THEN
124 fnd_file.put_line(fnd_file.log, l_blank_line);
125 fnd_file.put_line(fnd_file.log, 'New customer column does not exist.');
126
127 SELECT gtas_customer_attribute
128 INTO l_gtas_cust_attribute
129 FROM fv_system_parameters;
130 fnd_file.put_line(fnd_file.log, l_blank_line);
131 fnd_file.put_line(fnd_file.log,'gtas_customer_attribute: '||l_gtas_cust_attribute);
132
133 IF l_gtas_cust_attribute IS NULL THEN
134 fnd_file.put_line(fnd_file.log, l_blank_line);
135 fnd_file.put_line(fnd_file.log,'GTAS Customer Attribute is null');
136 l_err_string := 'GTAS Customer Attribute is null. Please set this up'||
137 ' in the Define Federal System Parameters form and'||
138 ' rerun this process.';
139 fnd_file.put_line(fnd_file.log, l_err_string);
140 g_err_buff := l_err_string;
141 g_err_code := 2;
142 RETURN;
143 END IF;
144 END;
145
146 BEGIN
147 SELECT factsI_customer_attribute, factsI_vendor_attribute
148 INTO l_facts1_cust_attribute, l_facts1_vend_attribute
149 FROM fv_system_parameters;
150
151 fnd_file.put_line(fnd_file.log, l_blank_line);
152 fnd_file.put_line(fnd_file.log,'l_facts1_cust_attribute: '||l_facts1_cust_attribute);
153 fnd_file.put_line(fnd_file.log,'l_facts1_vend_attribute: '||l_facts1_vend_attribute);
154
155 IF (l_facts1_cust_attribute IS NULL OR l_facts1_vend_attribute IS NULL) THEN
156 fnd_file.put_line(fnd_file.log, l_blank_line);
157 fnd_file.put_line(fnd_file.log,'GTAS Customer Attribute is null');
158 l_err_string := 'FACTS1 Customer or Vendor Attribute is null. Please set this up'||
159 ' in the Define Federal System Parameters form and'||
160 ' rerun this process.';
161 fnd_file.put_line(fnd_file.log,l_err_string);
162 g_err_buff := l_err_string;
163 g_err_code := 2;
164 RETURN;
165 END IF;
166
167 END;
168 --Need to print out the customer/supplier id, name where update
169 --cannot be done because agency id is not found
170 --in the mapping table
171 fv_utility.log_mesg('Checking if agency is not found.');
172 FOR i in 1..2 LOOP
173
174 IF i = 1 THEN
175 l_col_select := ' select c.cust_account_id, c.account_name,';
176 l_where_clause := 'and c.customer_class_code = ''FEDERAL'' ';
177 l_attrib := l_facts1_cust_attribute;
178 l_table := 'hz_cust_accounts_all';
179 l_party_type := '-----CUSTOMERS-----';
180 l_sub_hdr_printed := FALSE;
181 ELSE
182 l_col_select := ' select c.vendor_id, c.vendor_name,';
183 l_where_clause := 'and vendor_type_lookup_code = ''FEDERAL'' ';
184 l_attrib := l_facts1_vend_attribute;
185 l_table := 'ap_suppliers';
186 l_party_type := '-----SUPPLIERS-----';
187 l_sub_hdr_printed := FALSE;
188 END IF;
189
190 --Select all vendors where the agency id does not match star_dept_reg
191 --and star_dept_xfer is blank, in the mapping table
192 l_aid_select := l_col_select ||
193 ' SUBSTR(c.'||l_attrib||',1,2),
194 SUBSTR(c.'||l_attrib||',3,4)
195 FROM '|| l_table||' c
196 WHERE NOT EXISTS (SELECT 1 from fv_agency_id_map m
197 where m.star_dept_reg = substr(c.'||l_attrib||',1,2)
198 and m.star_dept_reg is not null
199 and m.star_dept_xfer is null
200 )
201 and substr(c.'||l_attrib||',1,2) is not null '||
202 l_where_clause ;
203
204 --fnd_file.put_line(fnd_file.log, l_aid_select);
205 IF c_aid_select%ISOPEN THEN close c_aid_select; END IF;
206 OPEN c_aid_select FOR l_aid_select;
207 LOOP
208 FETCH c_aid_select INTO l_account_id, l_account_name, l_agency_id, l_main_acct;
209 EXIT WHEN c_aid_select%NOTFOUND;
210
211 IF NOT l_hdr_printed THEN
212 fnd_file.put_line(fnd_file.output,'Agency ID not found for the following Customers/Suppliers:');
213 fnd_file.put_line(fnd_file.output,'Please update them manually.');
214 fnd_file.put_line(fnd_file.output, rpad('-',75,'-'));
215 fnd_file.put_line(fnd_file.output,'Customer Customer/Supplier Agency ID');
216 fnd_file.put_line(fnd_file.output,'Account ID/ Name ');
217 fnd_file.put_line(fnd_file.output,'Supplier ID');
218 fnd_file.put_line(fnd_file.output,rpad('-',75,'-'));
219 l_hdr_printed := TRUE;
220 END IF;
221
222 IF NOT l_sub_hdr_printed THEN
223 fnd_file.put_line(fnd_file.output,l_party_type);
224 l_sub_hdr_printed := TRUE;
225 END IF;
226
227 fnd_file.put_line(fnd_file.output, RPAD(l_account_id,15,' ')||RPAD(NVL(l_account_name,' '),40,' ')||
228 l_agency_id);
229
230
231 END LOOP;
232 END LOOP;
233
234
235 --Print the customer/supplier name where the agency id is 48 or 95
236 fv_utility.log_mesg('Checking if agency in 48 or 95.');
237 l_hdr_printed := FALSE;
238 FOR i in 1..2 LOOP
239 IF i = 1 THEN
240 l_col_select := ' SELECT SUBSTR(c.account_name,1,80)
241 FROM hz_cust_accounts_all c ';
242 l_where_clause := 'and c.customer_class_code = ''FEDERAL'' ';
243 l_attrib := l_facts1_cust_attribute;
244
245 l_party_type := '-----CUSTOMERS-----';
246 l_sub_hdr_printed := FALSE;
247 ELSE
248 l_col_select := ' SELECT SUBSTR(c.vendor_name,1,80)
249 FROM ap_suppliers c ';
250 l_where_clause := 'and c.vendor_type_lookup_code = ''FEDERAL'' ';
251 l_attrib := l_facts1_vend_attribute;
252
253 l_party_type := '-----SUPPLIERS-----';
254 l_sub_hdr_printed := FALSE;
255 END IF;
256
257 l_aid_select := l_col_select ||
258 ' WHERE NVL(substr(c.'||l_attrib||',1,2),-99) IN (''48'',''95'') '||
259 l_where_clause ;
260
261 --fnd_file.put_line(fnd_file.log, l_aid_select);
262
263 IF c_aid_select%ISOPEN THEN close c_aid_select; END IF;
264 OPEN c_aid_select FOR l_aid_select;
265 LOOP
266 FETCH c_aid_select INTO l_party_name;
267 EXIT WHEN c_aid_select%NOTFOUND;
268
269 IF NOT l_hdr_printed THEN
270 fnd_file.put_line(fnd_file.output,' ');
271 fnd_file.put_line(fnd_file.output,' ');
272 fnd_file.put_line(fnd_file.output,' ');
273 fnd_file.put_line(fnd_file.output,'Agency ID not updated for the following.');
274 fnd_file.put_line(fnd_file.output,'Please check and rectify as necessary.');
275 fnd_file.put_line(fnd_file.output, rpad('-',80,'-'));
276 fnd_file.put_line(fnd_file.output,'Supplier/Customer Name');
277 fnd_file.put_line(fnd_file.output,rpad('-',80,'-'));
278 l_hdr_printed := TRUE;
279 END IF;
280
281 IF NOT l_sub_hdr_printed THEN
282 fnd_file.put_line(fnd_file.output,' ');
283 fnd_file.put_line(fnd_file.output,l_party_type);
284
285 l_sub_hdr_printed := TRUE;
286 END IF;
287
288 fnd_file.put_line(fnd_file.output, l_party_name);
289
290
291 END LOOP;
292
293 END LOOP;
294
295
296 --Update customer/supplier rows with the 3 digit agency id
297 --from the mapping table
298 fv_utility.log_mesg('Updating cust/supp with 3 digit agency id.');
299 FOR j in 1..2 LOOP
300 IF j = 1 THEN
301 --Update new column in hz_cust_accounts_all
302 l_attrib := l_facts1_cust_attribute;
303 IF l_cust_col_exists = 'Y' THEN
304
305 l_update_statement := '
306 update hz_cust_accounts_all c
307 set TRADING_PARTNER_AGENCY_ID =
308 (select f.aid
309 from fv_agency_id_map f
310 where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
311 and f.star_dept_reg is not null
312 and f.star_dept_xfer is null
313 and rownum = 1
314 )
315 where exists
316 (select 1
317 from fv_agency_id_map m
318 where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
319 and m.star_dept_reg is not null
320 and m.star_dept_xfer IS NULL )
321 and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
322 and c.customer_class_code = ''FEDERAL''
323 and SUBSTR(c.'||l_attrib||',1,2) NOT IN (''48'', ''95'') ';
324
325 fnd_file.put_line(fnd_file.log,' ');
326 fnd_file.put_line(fnd_file.log,' ');
327 fnd_file.put_line(fnd_file.log,'------------------------------------------------------');
328 fnd_file.put_line(fnd_file.log,'Updated hz_cust_accounts_all.TRADING_PARTNER_AGENCY_ID');
329
330 ELSE
331 --Update dff column in hz_cust_accounts_all
332 l_update_statement :=
333 'update hz_cust_accounts_all c
334 set '||l_gtas_cust_attribute||' =
335 (select f.aid
336 from fv_agency_id_map f
337 where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
338 and f.star_dept_reg is not null
339 and f.star_dept_xfer IS NULL
340 and rownum = 1
341 )
342 where exists
343 (select 1
344 from fv_agency_id_map m
345 where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
346 and m.star_dept_reg is not null
347 and m.star_dept_xfer IS NULL)
348 and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
349 and c.customer_class_code = ''FEDERAL''
350 and SUBSTR(c.'||l_attrib||',1,2) NOT IN (''48'', ''95'') ';
351
352 fnd_file.put_line(fnd_file.log,' ');
353 fnd_file.put_line(fnd_file.log,' ');
354 fnd_file.put_line(fnd_file.log,'------------------------------------------------------');
355 fnd_file.put_line(fnd_file.log,'Updated hz_cust_accounts_all.'||l_gtas_cust_attribute);
356 END IF;
357
358
359 EXECUTE IMMEDIATE l_update_statement;
360 ELSE
361 l_attrib := l_facts1_vend_attribute;
362 l_update_statement :=
363 'update ap_suppliers c
364 set global_attribute5 =
365 (select f.aid
366 from fv_agency_id_map f
367 where f.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
368 and f.star_dept_reg is not null
369 and f.star_dept_xfer is null
370 and rownum = 1
371 )
372 where exists
373 (select 1
374 from fv_agency_id_map m
375 where m.star_dept_reg = SUBSTR(c.'||l_attrib||',1,2)
376 and m.star_dept_reg is not null
377 and m.star_dept_xfer IS NULL )
378 and SUBSTR(c.'||l_attrib||',1,2) IS NOT NULL
379 and vendor_type_lookup_code = ''FEDERAL''
380 and SUBSTR(c.'||l_attrib||',1,2) NOT IN (''48'', ''95'') ';
381
382 fnd_file.put_line(fnd_file.log,'------------------------------------------------------');
383 fnd_file.put_line(fnd_file.log,'Updated ap_suppliers.global_attribute5');
384 fnd_file.put_line(fnd_file.log,'------------------------------------------------------');
385
386 EXECUTE IMMEDIATE l_update_statement;
387 END IF;
388 END LOOP;
389
390 EXCEPTION WHEN OTHERS THEN
391 g_err_buff := 'When others error: '||sqlerrm;
392 g_err_code := sqlcode;
393
394 END upgrade_party_agency_id;
395 -----------------------------------------------------------------------------------
396 --Upgrade agency id FROM 2 to 3 digits in TAS fORm
397 PROCEDURE UPGRADE_FED_ACCOUNTS_AGENCY_ID IS
398 l_hdr_printed BOOLEAN := FALSE;
399 l_federal_seq NUMBER;
400 l_UPDATE_fed_tas BOOLEAN := TRUE;
401 cnt NUMBER :=0;
402 cnt1 NUMBER :=0;
403 l_FEDERAL_ACCT_SYMBOL_ID FV_FACTS_FEDERAL_ACCOUNTS.FEDERAL_ACCT_SYMBOL_ID%TYPE;
404
405 --Modified the following cursor to display
406 --sob id, sub acct and dept xfer for matching
407 --rows NOT found in the the mapping table
408 /*
409 CURSOR c_aid_NOT_exISts IS
410 SELECT f.treasury_dept_code, f.treasury_acct_code
411 FROM fv_facts_federal_accounts f
412 WHERE NOT EXISTS
413 (SELECT *
414 FROM fv_agency_id_map m, fv_treasury_symbols tas
415 WHERE tas.FEDERAL_ACCT_SYMBOL_ID = f.FEDERAL_ACCT_SYMBOL_ID
416 AND m.star_dept_reg = f.treasury_dept_code
417 AND m.star_main_acct = f.treasury_acct_code
418 AND m.star_dept_reg = tas.department_id
419 AND m.star_main_acct = tas.fund_group_code
420 AND m.star_dept_xfer = tas.dept_transfer
421 AND m.SUB = tas.TAFS_SUB_ACCT
422 AND m.star_dept_reg IS NOT NULL
423 AND m.star_main_acct IS NOT NULL
424 AND m.star_dept_xfer IS NOT NULL
425 AND m.SUB IS NOT NULL)
426 AND f.treasury_dept_code IS NOT NULL
427 AND f.treasury_acct_code IS NOT NULL;
428 */
429 CURSOR c_aid_NOT_exISts IS
430 SELECT DISTINCT f.set_of_books_id, f.treasury_dept_code, f.treasury_acct_code,
431 t.TAFS_SUB_ACCT, t.dept_transfer
432 FROM fv_facts_federal_accounts f, fv_treasury_symbols t
433 WHERE f.federal_acct_symbol_id = t.federal_acct_symbol_id
434 AND NOT EXISTS
435 (SELECT 1
436 FROM fv_agency_id_map m
437 WHERE m.star_dept_reg = f.treasury_dept_code
438 AND m.star_main_acct = f.treasury_acct_code
439 AND NVL(m.star_dept_xfer,'-XX') = NVL(t.dept_transfer,'-XX')
440 AND NVL(m.SUB,'-ZZ') = NVL(t.TAFS_SUB_ACCT,'-ZZ')
441 )
442 ORDER BY 1, 2,3,4;
443
444 CURSOR c_aid_fed_tas_map IS
445 SELECT star_dept_reg, star_main_acct, star_dept_xfer, sub,
446 treasury_dept_code, treasury_acct_code, f.federal_acct_symbol_id,
447 treasury_symbol_id, tafs_sub_acct, department_id, dept_transfer,
448 fund_group_code, aid,ata, f.start_date start_date, f.SET_of_books_id SET_of_books_id,
449 federal_acct_symbol_name
450 FROM fv_agency_id_map m, fv_facts_federal_accounts f, fv_treasury_symbols t
451 WHERE m.star_dept_reg = f.treasury_dept_code
452 AND m.star_main_acct = f.treasury_acct_code
453 AND f.federal_acct_symbol_id = t.federal_acct_symbol_id(+)
454 AND ((t.tafs_sub_acct IS NULL) OR (t.tafs_sub_acct IS NOT NULL AND t.tafs_sub_acct=sub));
455 BEGIN
456
457 FOR no_aid_row IN c_aid_NOT_exISts
458 LOOP
459 IF NOT l_hdr_printed THEN
460 fnd_file.put_line(fnd_file.output,' ');
461 fnd_file.put_line(fnd_file.output,' ');
462 fnd_file.put_line(fnd_file.output,'Agency ID/Main Acct NOT found in mapping table for the following:');
463 fnd_file.put_line(fnd_file.output,'Please verify.');
464 fnd_file.put_line(fnd_file.output, rpad('-',75,'-'));
465 fnd_file.put_line(fnd_file.output,'Ledger ID Agency ID Main Acct SUB Acct Dept Xfer');
466 fnd_file.put_line(fnd_file.output,rpad('-',75,'-'));
467 l_hdr_printed := TRUE;
468 END IF;
469
470 fnd_file.put_line(fnd_file.output, RPAD(no_aid_row.set_of_books_id,15,' ')||
471 RPAD(no_aid_row.treasury_dept_code,15,' ')||RPAD(no_aid_row.treasury_acct_code,15,' ')||
472 RPAD(no_aid_row.TAFS_SUB_ACCT,15,' ')||no_aid_row.dept_transfer);
473
474
475 END LOOP;
476
477 FOR rec IN c_aid_fed_tas_map
478 LOOP
479 fnd_file.put_line(fnd_file.log, 'rec.star_dept_reg '|| rec.star_dept_reg);
480 fnd_file.put_line(fnd_file.log, 'rec.star_main_acct '|| rec.star_main_acct);
481 fnd_file.put_line(fnd_file.log, 'rec.star_dept_xfer '|| rec.star_dept_xfer);
482 fnd_file.put_line(fnd_file.log, 'rec.sub '|| rec.sub);
483 fnd_file.put_line(fnd_file.log, 'rec.treasury_dept_code '|| rec.treasury_dept_code);
484 fnd_file.put_line(fnd_file.log, 'rec.treasury_acct_code '|| rec.treasury_acct_code);
485 fnd_file.put_line(fnd_file.log, 'rec.treasury_symbol_id '|| rec.treasury_symbol_id);
486 fnd_file.put_line(fnd_file.log, 'rec.aid '|| rec.aid);
487 fnd_file.put_line(fnd_file.log, 'rec.ata '|| rec.ata);
488
489 l_UPDATE_fed_tas:= TRUE;
490
491 IF rec.star_dept_xfer IS NOT NULL THEN
492 IF rec.sub IS NULL THEN
493 -- 1)Upgrade to new allocation agency id when there is a match between
494 -- star_dept_reg and agency id, star_main_acct and main account,
495 -- star_dept_xfer and allocation transfer agency id, sub and sub account code
496 UPDATE fv_treasury_symbols
497 SET dept_transfer = rec.ata
498 WHERE treasury_symbol_id = rec.treasury_symbol_id
499 AND dept_transfer = rec.star_dept_xfer
500 AND department_id = rec.star_dept_reg
501 AND fund_group_code = rec.star_main_acct;
502 ELSE
503 UPDATE fv_treasury_symbols
504 SET dept_transfer = rec.ata
505 WHERE treasury_symbol_id = rec.treasury_symbol_id
506 AND dept_transfer = rec.star_dept_xfer
507 AND department_id = rec.star_dept_reg
508 AND fund_group_code = rec.star_main_acct
509 AND tafs_sub_acct = rec.sub;
510 END IF;
511 ELSE
512 -- 2) Insert new record into fv_facts_federal_accounts when there is a match
513 -- for agency id and main account
514 -- and if multiple new mappings exist for a single agency id with different sub account codes
515 -- and atleast 2 TASs exist with different sub account codes
516
517 -- 3)Update record to new agency id when there is a match and if only one
518 -- TAS exists for that mapping
519 IF rec.sub IS NOT NULL THEN
520 SELECT count(*) INTO cnt
521 FROM fv_agency_id_map m
522 WHERE m.star_Dept_reg = rec.star_Dept_reg
523 AND m.star_main_acct = rec.star_main_acct
524 AND m.aid <> rec.aid
525 AND (m.sub IS NOT NULL AND m.sub <> rec.sub);
526
527 IF cnt > 0 THEN
528 SELECT count(*) INTO cnt1 FROM fv_Treasury_symbols
529 WHERE treasury_symbol_id <> rec.treasury_symbol_id
530 AND department_id = rec.star_dept_reg
531 AND fund_group_code = rec.star_main_acct
532 AND tafs_sub_acct <> rec.sub;
533
534 IF cnt1 > 1 THEN
535 BEGIN
536 select FEDERAL_ACCT_SYMBOL_ID into l_FEDERAL_ACCT_SYMBOL_ID
537 from fv_facts_federal_accounts
538 where treasury_dept_code = rec.aid
539 and treasury_acct_code = rec.star_main_acct;
540
541 UPDATE fv_treasury_symbols
542 SET department_id = rec.aid,
543 federal_acct_symbol_id = l_FEDERAL_ACCT_SYMBOL_ID
544 WHERE treasury_symbol_id = rec.treasury_symbol_id;
545 l_UPDATE_fed_tas := false;
546 EXCEPTION
547 WHEN NO_DATA_FOUND THEN
548 SELECT FV_FACTS_FEDERAL_ACCOUNTS_S.nextval INTO l_federal_seq FROM dual;
549 BEGIN
550 INSERT INTO FV_FACTS_FEDERAL_ACCounTS
551 (FEDERAL_ACCT_SYMBOL_ID,
552 SET_OF_BOOKS_ID,
553 TREASURY_DEPT_CODE,
554 TREASURY_ACCT_CODE,
555 FEDERAL_ACCT_SYMBOL_NAME,
556 START_DATE,
557 CREATION_DATE,
558 CREATED_BY,
559 LAST_UPDATE_DATE,
560 LAST_UPDATED_BY)
561 VALUES
562 (l_federal_seq,
563 rec.SET_OF_BOOKS_ID,
564 rec.aid,
565 rec.star_main_acct,
566 rec.FEDERAL_ACCT_SYMBOL_NAME ,
567 rec.START_DATE,
568 sysdate,
569 fnd_global.user_id,
570 sysdate,
571 fnd_global.user_id);
572 EXCEPTION
573 WHEN OTHERS THEN
574 fnd_file.put_line(fnd_file.log,SQLERRM);
575 END;
576
577 UPDATE fv_treasury_symbols
578 SET department_id = rec.aid,
579 federal_acct_symbol_id = l_federal_seq
580 WHERE treasury_symbol_id = rec.treasury_symbol_id;
581
582 l_UPDATE_fed_tas := false;
583 END;
584
585 END IF;
586 END IF;
587 END IF;
588 END IF;
589
590 IF (l_UPDATE_fed_tas) THEN
591
592 SELECT count(*) INTO cnt
593 FROM fv_facts_federal_accounts
594 WHERE treasury_dept_code = rec.aid
595 AND treasury_acct_code = rec.treasury_acct_code
596 AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
597
598 IF cnt > 0 THEN
599 SELECT federal_acct_symbol_id INTO l_federal_acct_symbol_id
600 FROM fv_facts_federal_accounts
601 WHERE treasury_dept_code = rec.aid
602 AND treasury_acct_code = rec.treasury_acct_code
603 AND federal_acct_symbol_id <> rec.federal_acct_symbol_id;
604
605 UPDATE Fv_Treasury_Symbols
606 SET federal_acct_symbol_id = l_federal_acct_symbol_id,
607 department_id = rec.aid
608 WHERE Treasury_Symbol_Id = rec.Treasury_Symbol_Id;
609
610 ELSE
611 UPDATE fv_facts_federal_accounts
612 SET treasury_dept_code = rec.aid
613 WHERE federal_acct_symbol_id = rec.federal_acct_symbol_id;
614
615
616 IF rec.treasury_symbol_id IS NOT NULL THEN
617 UPDATE fv_treasury_symbols
618 SET department_id = rec.aid
619 WHERE federal_acct_symbol_id = rec.federal_acct_symbol_id
620 AND treasury_symbol_id = rec.treasury_symbol_id;
621
622 END IF;
623 END IF;
624 END IF;
625 END LOOP;
626
627 fnd_file.put_line(fnd_file.log, 'Updating fv_facts_federal_accounts with new agency id');
628 fnd_file.put_line(fnd_file.log, 'Updating fv_treasury_symbols with new allocation transfer agency id');
629 fnd_file.put_line(fnd_file.log, 'Updated: '||SQL%ROWCOUNT||' rows.');
630
631 EXCEPTION WHEN OTHERS THEN
632 g_err_buff := 'When OTHERS ERROR in upgrade_fed_accounts_agency_id: '||sqlerrm;
633 g_err_code := sqlcode;
634 END;
635 -----------------------------------------------------------------------------------
636 PROCEDURE UPGRADE_BUDGET_CODES IS
637
638 BEGIN
639 DELETE FROM fv_facts_bud_fed_accts;
640 DELETE FROM fv_facts_budget_accounts;
641
642 fnd_file.put_line(fnd_file.log, 'Deleted all records FROM fv_facts_bud_fed_accts AND fv_facts_budget_accounts');
643 EXCEPTION
644 WHEN OTHERS THEN
645 g_err_buff := 'When Others Error: '||SQLERRM;
646 g_err_code := sqlcode;
647 END upgrade_budget_codes;
648 -----------------------------------------------------------------------------------
649 --Update ap_suppliers.global_attribute4 (which is fed nonfed code GDF)
650 PROCEDURE UPGRADE_FEDNONFED_CODE IS
651 BEGIN
652 fnd_file.put_line(fnd_file.log, 'Updating Fed Non-Fed Code in ap_suppliers.');
653
654 UPDATE ap_suppliers
655 SET global_attribute4 = 'F',
656 global_attribute_category = 'FV.US.APXVDMVD.GTAS_DATA',
657 global_attribute20 = 1
658 WHERE vendor_type_lookup_code = 'FEDERAL';
659
660 UPDATE ap_suppliers
661 SET global_attribute4 = 'N',
662 global_attribute_category = 'FV.US.APXVDMVD.GTAS_DATA',
663 global_attribute20 = 1
664 WHERE vendor_type_lookup_code <> 'FEDERAL'
665 AND global_attribute_category IS NULL;
666
667 fnd_file.put_line(fnd_file.log, 'Updated Fed Non-Fed Code in ap_suppliers.');
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 g_err_buff := 'When Others Error: '||SQLERRM;
672 g_err_code := sqlcode;
673 END upgrade_fednonfed_code;
674 --------------------------------------------------------------------------------
675 --This script is used to upgrade data to be compliant to GTAS
676 PROCEDURE MIGRATE_GTAS_DATA IS
677
678 tpTASSeq NUMBER DEFAULT -1;
679 sob NUMBER DEFAULT 1;
680 l_module VARCHAR2(200) := g_module||'migrate_gtas_data';
681 l_org_id NUMBER;
682 l_ledger_name gl_ledgers_v.name%TYPE;
683
684 BEGIN
685 log(l_module, 'Begin');
686
687 log(l_module, 'Upgrading pre GTAS data');
688
689
690
691 -- 1) Upgrade code "E" in lookup APPORTIONMENT_CATEGORY_CODE to
692 -- "C" of lookup FV_APPOR_CATEGORY.
693 -- Fund Category field of Define Fund Attributes form
694
695 BEGIN
696 UPDATE fv_fund_parameters
697 SET fund_category = 'E'
698 WHERE fund_category = 'C';
699 EXCEPTION
700 WHEN OTHERS THEN
701 FND_FILE.PUT_LINE(FND_FILE.LOG,
702 'When others error: '||l_module||' Fund Category upgrade'||' '||SQLERRM);
703 END;
704 -- 2) Upgrade Fund Type in Define Treasury Account Symbols form
705 BEGIN
706
707 BEGIN
708 UPDATE fv_treasury_symbols
709 SET fund_type = 'EG'
710 WHERE (fund_group_code BETWEEN 0000 AND 3499 OR
711 fund_group_code BETWEEN 3600 AND 3799)
712 AND (receipt_account_indicator = 'N' OR
713 receipt_account_indicator IS NULL);
714 EXCEPTION
715 WHEN OTHERS THEN
716 FND_FILE.PUT_LINE(FND_FILE.LOG,
717 'When others error: '||l_module||' F Fund Type 1 upgrade'||' '||SQLERRM);
718 END;
719
720 BEGIN
721 UPDATE fv_treasury_symbols
722 SET fund_type = 'UG'
723 WHERE (fund_group_code BETWEEN 0000 AND 3499 OR
724 fund_group_code BETWEEN 3600 AND 3799)
725 AND receipt_account_indicator = 'Y';
726 EXCEPTION
727 WHEN OTHERS THEN
728 FND_FILE.PUT_LINE(FND_FILE.LOG,
729 'When others error: '||l_module||' Fund Type 2 upgrade'||' '||SQLERRM);
730 END;
731
732 BEGIN
733 UPDATE fv_treasury_symbols
734 SET fund_type = 'ES'
735 WHERE fund_group_code BETWEEN 5000 AND 5999
736 AND (receipt_account_indicator = 'N' OR receipt_account_indicator IS NULL);
737 EXCEPTION
738 WHEN OTHERS THEN
739 FND_FILE.PUT_LINE(FND_FILE.LOG,
740 'When others error: '||l_module||' Fund Type 3 upgrade'||' '||SQLERRM);
741 END;
742
743 BEGIN
744 UPDATE fv_treasury_symbols
745 SET fund_type = 'US'
746 WHERE fund_group_code BETWEEN 5000 AND 5999
747 AND receipt_account_indicator = 'Y';
748 EXCEPTION
749 WHEN OTHERS THEN
750 FND_FILE.PUT_LINE(FND_FILE.LOG,
751 'When others error: '||l_module||' Fund Type 4 upgrade'||' '||SQLERRM);
752 END;
753
754 BEGIN
755 UPDATE fv_treasury_symbols
756 SET fund_type = 'EP'
757 WHERE fund_group_code BETWEEN 4000 AND 4499;
758 EXCEPTION
759 WHEN OTHERS THEN
760 FND_FILE.PUT_LINE(FND_FILE.LOG,
761 'When others error: '||l_module||' Fund Type 5 upgrade'||' '||SQLERRM);
762 END;
763
764 BEGIN
765 UPDATE fv_treasury_symbols
766 SET fund_type = 'ER'
767 WHERE fund_group_code BETWEEN 4500 AND 4999;
768 EXCEPTION
769 WHEN OTHERS THEN
770 FND_FILE.PUT_LINE(FND_FILE.LOG,
771 'When others error: '||l_module||' Fund Type 6 upgrade'||' '||SQLERRM);
772 END;
773
774 BEGIN
775 UPDATE fv_treasury_symbols
776 SET fund_type = 'EM'
777 WHERE fund_group_code BETWEEN 3900 AND 3959;
778 EXCEPTION
779 WHEN OTHERS THEN
780 FND_FILE.PUT_LINE(FND_FILE.LOG,
781 'When others error: '||l_module||' Fund Type 7 upgrade'||' '||SQLERRM);
782 END;
783
784 BEGIN
785 UPDATE fv_treasury_symbols
786 SET fund_type = 'EC'
787 WHERE fund_group_code BETWEEN 3960 AND 3999;
788 EXCEPTION
789 WHEN OTHERS THEN
790 FND_FILE.PUT_LINE(FND_FILE.LOG,
791 'When others error: '||l_module||' Fund Type 8 upgrade'||' '||SQLERRM);
792 END;
793
794 BEGIN
795 UPDATE fv_treasury_symbols
796 SET fund_type = 'DF'
797 WHERE fund_group_code BETWEEN 6000 AND 6999;
798 EXCEPTION
799 WHEN OTHERS THEN
800 FND_FILE.PUT_LINE(FND_FILE.LOG,
801 'When others error: '||l_module||' Fund Type 9 upgrade'||' '||SQLERRM);
802 END;
803
804 BEGIN
805 UPDATE fv_treasury_symbols
806 SET fund_type = 'ET'
807 WHERE (fund_group_code BETWEEN 8000 AND 8399)
808 OR (fund_group_code BETWEEN 8500 AND 8999)
809 AND (receipt_account_indicator = 'N' OR receipt_account_indicator IS NULL);
810 EXCEPTION
811 WHEN OTHERS THEN
812 FND_FILE.PUT_LINE(FND_FILE.LOG,
813 'When others error: '||l_module||' Fund Type 10 upgrade'||' '||SQLERRM);
814 END;
815
816 BEGIN
817 UPDATE fv_treasury_symbols
818 SET fund_type = 'UT'
819 WHERE (fund_group_code BETWEEN 8000 AND 8399)
820 OR (fund_group_code BETWEEN 8500 AND 8999)
821 AND receipt_account_indicator = 'Y';
822 EXCEPTION
823 WHEN OTHERS THEN
824 FND_FILE.PUT_LINE(FND_FILE.LOG,
825 'When others error: '||l_module||' Fund Type 11 upgrade'||' '||SQLERRM);
826 END;
827
828 BEGIN
829 UPDATE fv_treasury_symbols
830 SET fund_type = 'TR'
831 WHERE fund_group_code BETWEEN 8400 AND 8499;
832 EXCEPTION
833 WHEN OTHERS THEN
834 FND_FILE.PUT_LINE(FND_FILE.LOG,
835 'When others error: '||l_module||' Fund Type 12 upgrade'||' '||SQLERRM);
836 END;
837
838 BEGIN
839 UPDATE fv_treasury_symbols
840 SET fund_type = 'CF'
841 WHERE (fund_group_code BETWEEN 3500 AND 3599 OR
842 fund_group_code BETWEEN 3800 AND 3899);
843 EXCEPTION
844 WHEN OTHERS THEN
845 FND_FILE.PUT_LINE(FND_FILE.LOG,
846 'When others error: '||l_module||' Fund Type 13 upgrade'||' '||SQLERRM);
847 END;
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 FND_FILE.PUT_LINE(FND_FILE.LOG,
852 'When others error: '||l_module||'Fund Group Upgrade'||' '||SQLERRM);
853 END;
854
855 -- Add treasury_symbol_id to all the treasury symbols, without id, defined in
856 -- fv_tp_treasury_symbols
857 BEGIN
858
859 --Get org id to get the sob id
860 l_org_id := MO_GLOBAL.get_current_org_id;
861 log(l_module, 'Org id: '||l_org_id);
862 --Get sob id
863 mo_utils.get_ledger_info(l_org_id ,
864 sob,l_ledger_name);
865 log(l_module, 'sob id: '||sob);
866
867 FOR rec IN (SELECT TREASURY_SYMBOL
868 FROM FV_TP_TREASURY_SYMBOLS
869 WHERE treasury_symbol_id IS NULL)
870 LOOP
871 SELECT fv_treasury_symbols_s.nextval INTO tpTASSeq from dual;
872 --sob := to_number(fnd_profile.value('GL_SET_OF_BKS_ID'));
873
874 log(l_module, 'TP TAS Id:'||tpTASSeq);
875 log(l_module, 'TP TAS:'||rec.treasury_symbol);
876
877 UPDATE fv_tp_treasury_symbols
878 SET treasury_symbol_id = tpTASSeq,
879 set_of_books_id = sob,
880 last_update_date = sysdate,
881 last_updated_by = fnd_global.user_id
882 WHERE treasury_symbol = rec.treasury_symbol;
883
884 END LOOP;
885 EXCEPTION
886 WHEN NO_DATA_FOUND THEN
887 FND_FILE.PUT_LINE(FND_FILE.LOG,
888 'When NO_DATA_FOUND error: '||l_module||
889 'Trading Partner - Treasury Symbol Id addition'||' '||SQLERRM);
890
891 WHEN OTHERS THEN
892 FND_FILE.PUT_LINE(FND_FILE.LOG,
893 'When others error: '||l_module||'Trading Partner - Treasury Symbol Id addition'||' '||SQLERRM);
894
895 END;
896
897 -- 3) Migrate Trading TAS BETC Mappings from FV_TP_TAS_BETC_MAP to
898 -- FV_TAS_BETC_MAP
899 BEGIN
900
901 FOR rec IN (SELECT TREASURY_SYMBOL,
902 BETC_CODE,
903 DEFAULT_FLAG,
904 ATTRIBUTE1,
905 ATTRIBUTE2,
906 ATTRIBUTE3,
907 ATTRIBUTE4,
908 ATTRIBUTE5,
909 ATTRIBUTE6,
910 ATTRIBUTE7,
911 ATTRIBUTE8,
912 ATTRIBUTE9,
913 ATTRIBUTE10,
914 ATTRIBUTE11,
915 ATTRIBUTE12,
916 ATTRIBUTE13,
917 ATTRIBUTE14,
918 ATTRIBUTE15,
919 ATTRIBUTE_CATEGORY
920 FROM FV_TP_TAS_BETC_MAP)
921 LOOP
922 SELECT treasury_symbol_id INTO tpTASSeq FROM fv_tp_treasury_symbols
923 WHERE treasury_symbol = rec.treasury_symbol;
924
925 INSERT INTO fv_tas_betc_map(TREASURY_SYMBOL_ID,
926 SET_OF_BOOKS_ID,
927 BETC_CODE,
928 DEFAULT_FLAG,
929 ATTRIBUTE1,
930 ATTRIBUTE2,
931 ATTRIBUTE3,
932 ATTRIBUTE4,
933 ATTRIBUTE5,
934 ATTRIBUTE6,
935 ATTRIBUTE7,
936 ATTRIBUTE8,
937 ATTRIBUTE9,
938 ATTRIBUTE10,
939 ATTRIBUTE11,
940 ATTRIBUTE12,
941 ATTRIBUTE13,
942 ATTRIBUTE14,
943 ATTRIBUTE15,
944 ATTRIBUTE_CATEGORY,
945 CREATED_BY,
946 CREATION_DATE,
947 LAST_UPDATE_LOGIN,
948 LAST_UPDATED_BY,
949 LAST_UPDATE_DATE,
950 TAS_TYPE)
951 VALUES(tpTASSeq,
952 sob,
953 rec.BETC_CODE,
954 rec.DEFAULT_FLAG,
955 rec.ATTRIBUTE1,
956 rec.ATTRIBUTE2,
957 rec.ATTRIBUTE3,
958 rec.ATTRIBUTE4,
959 rec.ATTRIBUTE5,
960 rec.ATTRIBUTE6,
961 rec.ATTRIBUTE7,
962 rec.ATTRIBUTE8,
963 rec.ATTRIBUTE9,
964 rec.ATTRIBUTE10,
965 rec.ATTRIBUTE11,
966 rec.ATTRIBUTE12,
967 rec.ATTRIBUTE13,
968 rec.ATTRIBUTE14,
969 rec.ATTRIBUTE15,
970 rec.ATTRIBUTE_CATEGORY,
971 fnd_global.user_id,
972 sysdate,
973 fnd_global.user_id,
974 fnd_global.user_id,
975 sysdate,
976 'T');
977
978 log(l_module, 'TP TAS:'||rec.treasury_symbol||
979 ' migrated succesfully');
980 END LOOP;
981 EXCEPTION
982 WHEN NO_DATA_FOUND THEN
983 FND_FILE.PUT_LINE(FND_FILE.LOG,
984 'When NO_DATA_FOUND error: '||l_module||
985 'Trading Partner BETC Mappings Migration'||' '||SQLERRM);
986
987 WHEN OTHERS THEN
988 FND_FILE.PUT_LINE(FND_FILE.LOG,
989 'When others error: '||l_module||
990 'Trading Partner BETC Mappings Migration'||' '||SQLERRM);
991 END;
992
993 -- 4) Upgrade Authority Duration Code and Availability Type in Define
994 -- Treasury Account Symbols form
995 BEGIN
996
997 UPDATE fv_treasury_symbols
998 SET time_frame = 'R',
999 availability_type_code = NULL
1000 WHERE receipt_account_indicator = 'Y';
1001
1002 UPDATE fv_treasury_symbols
1003 SET time_frame = 'F',
1004 availability_type_code = 'F'
1005 WHERE time_frame = 'NO_YEAR'
1006 AND (fund_group_code BETWEEN 3500 AND 3599 OR
1007 fund_group_code BETWEEN 3800 AND 3899);
1008
1009 UPDATE fv_treasury_symbols
1010 SET time_frame = 'X',
1011 availability_type_code = 'X'
1012 WHERE (receipt_account_indicator = 'N' OR
1013 receipt_account_indicator IS NULL)
1014 AND time_frame = 'NO_YEAR'
1015 AND (fund_group_code BETWEEN 0000 AND 3499 OR
1016 fund_group_code BETWEEN 3600 AND 3799);
1017
1018 EXCEPTION
1019 WHEN OTHERS THEN
1020 FND_FILE.PUT_LINE(FND_FILE.LOG,
1021 'When others error: '||l_module||
1022 'Availability Type and Authority Duration Code Upgrade'||
1023 ' '||SQLERRM);
1024 END;
1025
1026 --5)Update Outlays BEA Category Code.
1027 --New required field added in GTAS
1028 BEGIN
1029 UPDATE fv_fund_parameters
1030 SET outlays_bea_category_code = bea_category
1031 WHERE outlays_bea_category_code IS NULL;
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 FND_FILE.PUT_LINE(FND_FILE.LOG,
1035 'When others error: '||l_module||
1036 'Outlays BEA Category Code upgrade '||' '||SQLERRM);
1037 END;
1038
1039 --6)Update GTAS Reportable Indicator to Y for all old TASs
1040 BEGIN
1041 UPDATE fv_treasury_symbols
1042 SET gtas_reportable_indicator ='Y'
1043 WHERE FACTS_REPORTABLE_INDICATOR IS NOT NULL;
1044 EXCEPTION
1045 WHEN OTHERS THEN
1046 FND_FILE.PUT_LINE(FND_FILE.LOG,
1047 'When others error: '||l_module||
1048 'GTAS Reportable Indicator upgrade '||' '||SQLERRM);
1049 END;
1050
1051 --7)Update Budget Codes Table
1052 BEGIN
1053 DELETE FROM fv_facts_bud_fed_accts;
1054 DELETE FROM fv_facts_budget_accounts;
1055 EXCEPTION
1056 WHEN OTHERS THEN
1057 FND_FILE.PUT_LINE(FND_FILE.LOG,
1058 'When others error: '||l_module||
1059 'Budget Codes Table upgrade '||' '||SQLERRM);
1060 END;
1061
1062
1063 log(l_module, 'Updating pre-GTAS data is complete.');
1064
1065 COMMIT;
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 FND_FILE.PUT_LINE(FND_FILE.LOG,'When others error: '||
1069 l_module||' '||SQLERRM);
1070 END;
1071 --------------------------------------------------------------------------------
1072 PROCEDURE LOG
1073 (module IN VARCHAR2,
1074 message_line IN VARCHAR2) IS
1075
1076 l_module VARCHAR2(1000) := g_module||'LOG';
1077
1078 BEGIN
1079
1080 IF ( FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1081 FV_UTILITY.DEBUG_MESG(FND_LOG.LEVEL_STATEMENT, module, message_line);
1082 END IF;
1083
1084 EXCEPTION
1085 WHEN OTHERS THEN
1086 g_err_code := SQLCODE;
1087 g_err_buff := l_module||' - When others error: '||SQLERRM;
1088 FV_UTILITY.LOG_MESG (FND_LOG.LEVEL_ERROR, l_module,g_err_buff);
1089 END log;
1090 --------------------------------------------------------------------------------
1091 PROCEDURE DELETE_FACTS_PRC_DTL IS
1092 l_module VARCHAR2(1000) := g_module||'DELETE_FACTS_PRC_DTL';
1093 l_module_name VARCHAR2(30) := 'FV_GTAS_AGENCY_ID_UPGRADE';
1094 l_event_owner VARCHAR2(30);
1095 l_event_name VARCHAR2(50);
1096
1097 BEGIN
1098
1099 log(l_module, 'Begin');
1100
1101 SELECT product_code
1102 INTO l_event_owner
1103 FROM fnd_application
1104 WHERE application_id = 8901;
1105
1106 l_event_name := 'FV_DEL_FACTS_PRC_DTL';
1107
1108 IF (NOT (ad_event_registry_pkg.is_event_done
1109 (p_owner =>l_event_owner,
1110 p_event_name =>l_event_name))) THEN
1111
1112 DELETE FROM FV_FACTS_PRC_DTL;
1113
1114 ad_event_registry_pkg.set_event_as_done
1115 (
1116 p_owner =>l_event_owner,
1117 p_event_name =>l_event_name,
1118 p_module_name =>l_module_name
1119 );
1120
1121 ELSE
1122 FND_FILE.PUT_LINE(FND_FILE.LOG,'This event has already been run!');
1123 END IF;
1124
1125 log(l_module, 'End');
1126
1127 EXCEPTION
1128 WHEN OTHERS THEN
1129 g_err_code := SQLCODE;
1130 g_err_buff := l_module||' - When others error: '||SQLERRM;
1131 FV_UTILITY.LOG_MESG (FND_LOG.LEVEL_ERROR, l_module,g_err_buff);
1132 END DELETE_FACTS_PRC_DTL;
1133 --------------------------------------------------------------------------------
1134 BEGIN
1135 g_module := 'fv.plsql.FV_GTAS_AGENCY_ID_UPGRADE.';
1136 --------------------------------------------------------------------------------
1137 END fv_gtas_agency_id_upgrade;
[Home] [Help]