[Home] [Help]
PACKAGE BODY: APPS.FV_CCR_BPN_LOAD_PKG
Source
1 package body FV_CCR_BPN_LOAD_PKG as
2 /* $Header: FVBPNLDB.pls 120.5.12020000.5 2013/02/12 21:06:30 snama ship $*/
3
4 --type new_data is varray(235) of varchar2(200);
5 function insert_ccr_flags(p_duns in number,p_no_flags in number,p_flag_str in varchar2)
6 return boolean
7 is
8 l_flags_counter integer :=length(p_flag_str)/4;
9 l_flags_str varchar2(100) :=p_flag_str;
10 l_flag varchar2(3);
11 l_flag_val varchar2(1);
12 l_from_ind number :=1;
13 l_length number :=3;
14 l_module varchar2(50) := 'FV_CCR_BPN_LOAD_PKG.CCR_FLAGS';
15 l_errbuff varchar2(2000);
16
17
18 begin
19 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module, 'Flag String: ' || l_flags_str);
20 if p_no_flags <> l_flags_counter then
21 fv_utility.log_mesg(FND_LOG.LEVEL_STATEMENT,l_module,'Count mismatch between flags present in string and counter passed.');
22 return false;
23 end if;
24
25 for i in 1..l_flags_counter loop
26 l_flag := substr(l_flags_str,l_from_ind, l_length);
27 l_flag_val := substr(l_flags_str,l_from_ind +l_length,1);
28 l_from_ind := i*4+1;
29 --dbms_output.put_line('Flag: ' || l_flag || '. Value : '|| l_flag_val);
30 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,l_module, 'Flag: ' || l_flag || '. Value : '|| l_flag_val);
31 insert into fv_ccr_flags(duns, flagtype, flagval) values(p_duns,
32 l_flag,
33 l_flag_val);
34 end loop;
35 return true;
36 Exception
37 when others then
38 return false;
39 end insert_ccr_flags;
40
41
42 procedure insert_ccr_codes(clob_buff in clob,
43 field_count_low in number,
44 field_count_high in number,
45 delimiter in varchar2,
46 proc_count in number,
47 from_index in number,
48 duns_num in varchar2,
49 retpos out nocopy number)
50 is
51 --clob_buff clob;
52 --delimiter varchar2(1) := '"';
53 type codeTypeTab is table of varchar2(30) index by binary_integer;
54 codeType codeTypeTab;
55 field_counter number :=0;
56 --offset_low number :=1;
57 ext_from number :=0;
58 ext_to number :=0;
59 code_count varchar2(4);
60 code varchar2(158);
61 countmax number :=0;
62 code_counter number :=0;
63 count_offset number :=0;
64 l_module_name varchar2(1000);
65 l_errbuf varchar2(1000);
66 l_code_delim varchar2(1) := '.';
67 begin
68 l_module_name := 'Insert CCR Codes';
69 l_errbuf := 'Start. DUNS Number: '|| duns_num;
70 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, l_errbuf);
71 --codeType(1) := 'BUS TYPE';
72 --codeType(2) := 'SIC CODE';
73 --codeType(3) := 'NAICS CODE';
74 --codeType(4) := 'FSC CODE';
75 --codeType(5) := 'PSC CODE';
76 codeType(1) := 'FV_BUSINESS_TYPE';
77 codeType(2) := 'FV_SIC_TYPE';
78 codeType(3) := 'FV_NAICS_TYPE';
79 codeType(4) := 'FV_FSC_TYPE';
80 codeType(5) := 'FV_PSC_TYPE';
81
82 codeType(6) := 'NAICS Exception';
83 codeType(7) := 'External Certification';
84 codeType(8) := 'SBA Certification';
85 codeType(9) := 'CCR Numerics';
86 codeType(10) := 'Disaster Response';
87 --select ccr_info into clob_buff from junk_tab where rownum < 2;
88 --clob_length := dbms_lob.getlength(clob_buff);
89 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','BEGIN.');
90 for field_counter in field_count_low..field_count_high
91 loop
92 --insert into fv_ccr_bpn_log_messages values('Inside CCR_CODES','Looking from occurrence no: '||2*(field_counter+count_offset)+1);commit;
93 ext_from := dbms_lob.instr(clob_buff, delimiter, from_index, 2*(field_counter+count_offset)+1);--count_offset)+1); --(txt, delim, from (1), 2*(field_counter+count_offset)+1 is the nth occurrence of delimiter)
94 ext_to := dbms_lob.instr(clob_buff, delimiter, from_index, 2*(field_counter+count_offset)+2);--count_offset)+2);
95 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Entering for loop. DUNS received is ' || duns_num);
96 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Looking for counter b/w ' || ext_from || ' &' || ext_to);
97 if (ext_from is NULL or ext_to is NULL) then
98 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','ext_from or ext_to is NULL');
99 return;
100 end if;
101 if(ext_to-ext_from > 1) then
102 code_count := dbms_lob.substr(clob_buff,ext_to-ext_from-1,ext_from+1);
103 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Extracted counter is ' || code_count);
104 else
105 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Extracted counter is NULL');
106 end if;
107
108 select to_number(nvl(code_count,0)) into countmax from dual;
109 if countmax > 0 then
110 ext_from := ext_to +3;
111 end if;
112 for code_counter in 1..countmax loop
113 --FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','from_index, field_counter, count_offset, code_counter: ' || from_index || ' , ' || field_counter || ' , ' || count_offset || ' , ' || code_counter);
114 --ext_from := dbms_lob.instr(clob_buff, l_code_delim, from_index, 2*(field_counter+count_offset+code_counter)+1); --From_index in place of 1
115
116 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','from_index, field_counter, count_offset, code_counter: ' || from_index || ' , ' || field_counter|| ' , ' || count_offset|| ' , ' ||code_counter);
117 -- ext_to := dbms_lob.instr(clob_buff,l_code_delim,from_index,code_counter);--2*(field_counter+count_offset+code_counter)+2);--From_index in place of 1
118 ext_to := dbms_lob.instr(clob_buff,l_code_delim,ext_from,1);--code_counter);
119 if code_counter = countmax then
120 ext_to := dbms_lob.instr(clob_buff,delimiter,ext_from,1);--2*(field_counter+count_offset+code_counter)+2);--From_index in place of 1
121 end if;
122 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Looking for code b/w ' || ext_from || ' &' || ext_to);
123 if(ext_to-ext_from > 1) then
124 code := dbms_lob.substr(clob_buff,ext_to-ext_from,ext_from);
125 --field := dbsm_lob.substr(clob_buff,
126 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Extracted code is ' || code);
127 insert into fv_ccr_class_codes(duns, codetype, code) values(duns_num,
128 codeType(field_counter+proc_count+1),
129 code);
130 else
131 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_CODES','Extracted code is NULL');
132 end if;
133 ext_from :=ext_to +1;
134 end loop;
135 -- if countmax > 0 then
136 count_offset := count_offset + 1; --countmax;
137 -- end if;
138 end loop;
139 if countmax=0 then
140 ext_to := ext_to+3;
141 end if;
142 retpos := ext_to+2;
143 l_errbuf := 'End. DUNS Number: '|| duns_num;
144 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, l_errbuf);
145 exception
146 when others then
147 l_errbuf := ' Exception: '||l_module_name||'->'||SQLERRM;
148 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside CCR_CODES',l_errbuf);
149 raise_application_error(-20100, l_errbuf);
150 end insert_ccr_codes;
151
152 procedure insert_ccr_data(clob_buff in clob,
153 field_count_low in number,
154 field_count_high in number,
155 delimiter in varchar2,
156 proc_count in number,
157 from_index in number,
158 ccr_data in out nocopy new_data,
159 retpos out nocopy number
160 )
161 is
162 --clob_buff clob;
163 --delimiter varchar2(1) := '"';
164 --field_counter number :=1;
165 ext_from number :=0;
166 ext_to number :=0;
167 l_module_name varchar2(1000);
168 l_errbuf varchar2(1000);
169 begin
170 l_module_name := 'CCR_DATA';
171 l_errbuf := 'Start';
172 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
173
174 for field_counter in field_count_low..field_count_high
175 loop
176 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','Entering for loop.');
177 ext_from := dbms_lob.instr(clob_buff, delimiter, from_index, 2*field_counter+1);
178 ext_to := dbms_lob.instr(clob_buff,delimiter,from_index,2*field_counter+2);
179 if (ext_from is NULL or ext_to is NULL) then
180 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','ext_from or ext_to is NULL');
181 return;
182 end if;
183 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','Extract from ' ||ext_from);
184 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','Extract till ' ||ext_to);
185 if(ext_to-ext_from > 1) then
186 ccr_data(proc_count+field_counter+1) := dbms_lob.substr(clob_buff,ext_to-ext_from-1,ext_from+1);
187 --field := dbsm_lob.substr(clob_buff,
188 if(ccr_data(proc_count+field_counter+1)='!end')then return; end if;
189 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','Extracted field ccr_data( ' || to_char(proc_count+field_counter+1) || ') is ' || ccr_data(proc_count+field_counter+1));
190 else
191 ccr_data(proc_count+field_counter+1) := null;
192 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA','Extracted field is NULL');
193 end if;
194 ext_from := ext_to;
195 --dbms_output.put_line(substr(buff,1,10));
196 end loop;
197 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside CCR_DATA', 'End');
198 retpos := ext_to + 2;
199 exception
200 when others then
201 l_errbuf := ' Exception: Inside CCR_DATA ->'||SQLERRM;
202 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside CCR_DATA',l_errbuf);
203 raise_application_error(-20200, l_errbuf);
204
205 /*l_errbuf := 'Exception occurred '||SQLERRM;
206 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside CCR_DATA','Unexpected exception: ' ||l_errbuf);
207 */
208 end insert_ccr_data;
209
210
211
212 procedure main is
213 --Get rows
214 cursor c_clob_data is select * from fv_ccr_bpn_clob_data where ccr_info not like '%BOF BPN COMPLETE%' and ccr_info not like '%EOF BPN COMPLETE%';
215 loop_counter number :=0;
216 field_counter number :=235; --Number of fields in fv_ccr_file_temp
217 from_index number :=1;
218 to_index number :=0;
219 clob_buff clob;
220 ccr_data new_data;
221 --ccr_codes new_codes;
222 proc_count number :=0;
223 l_success boolean;
224 l_module_name varchar2(1000);
225 l_errbuf varchar2(1000);
226 l_no_flags number;
227 l_dummy NUMBER;
228 begin
229 l_module_name := 'CCR BPN Data Load';
230 l_errbuf := 'Start of transfer';
231 ccr_data := new_data();
232 ccr_data.EXTEND(235);
233 --ccr_codes := new_codes();
234 --ccr_codes.EXTEND(235);
235 delete from fv_ccr_file_temp;
236 -- delete from fv_ccr_class_codes;
237 -- delete from fv_ccr_flags;
238 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_PROCEDURE, l_module_name,l_errbuf);
239 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','Entering for loop');
240 for rec in c_clob_data loop
241 clob_buff := rec.ccr_info;
242 --insert into fv_ccr_bpn_log_messages values('Clob text is', dbms_lob.substr(clob_buff,25,1));
243 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_PROCEDURE, 'Inside Main','Calling ccr_data. Identifying DUNS number.');
244 insert_ccr_data(clob_buff,0,23,'"',0,from_index,ccr_data,to_index); -- 0 to 23 for BPN. 0 to 26 for BSD.
245 from_index := to_index;
246 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','Returned from first call. Continue processing from '|| to_index);
247 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','Calling ccr_codes to process DUNS ' || ccr_data(1));
248
249 begin
250 select 1
251 into l_dummy
252 from fv_ccr_vendors
253 where duns = ccr_data(1)
254 and rownum = 1;
255 exception when no_data_found then
256 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'duns: '||ccr_data(1)||' not found,
257 skipping to next duns');
258 end;
259 if l_dummy = 1 then
260
261 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside Main','Calling delete_ccr_codes, delete_ccr_flags for DUNS '||ccr_data(1));
262 delete_ccr_codes(ccr_data(1));
263 delete_ccr_flags(ccr_data(1));
264
265 --FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','Calling CCR_CODES with 24|28|"|'||from_index || '|'|| ccr_data(1));
266 --insert_ccr_codes(clob_buff,24,28,'"',from_index,ccr_data(1),ccr_codes,to_index); Correct for BPN. 27 to 31 for BSD Layout
267
268 insert_ccr_codes(clob_buff,0,4,'"',proc_count,from_index,ccr_data(1),to_index);
269 --FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside Main','Continue processing from '||to_index);commit;
270 from_index :=to_index;
271 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_PROCEDURE, 'Inside Main','Calling ccr_data for DUNS ' || ccr_data(1));
272 insert_ccr_data(clob_buff,0,195,'"',29,from_index,ccr_data,to_index); --From field 29 for BPN Layout. 32 to 236 for BSD.
273 from_index :=to_index;
274 --FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside Main','Returned from 3rd call');
275 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','Calling ccr_codes for DUNS ' || ccr_data(1));
276 insert_ccr_codes(clob_buff,0,3,'"',5,from_index,ccr_data(1),to_index);
277 --FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT,'Inside Main','Returned from last call. Processed till '|| to_index);
278 from_index :=to_index;
279 to_index :=dbms_lob.instr(clob_buff,'"',from_index+1,1);
280 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','From_index:To_index ' ||from_index||':'||to_index);
281 l_no_flags := dbms_lob.substr(clob_buff,to_index-from_index-1,from_index+1);
282 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','No of flags ' ||l_no_flags);
283 if l_no_flags > 0 then
284 from_index :=to_index+2;
285 to_index :=dbms_lob.instr(clob_buff,'"',from_index+1,1);
286 l_success := insert_ccr_flags(ccr_data(1),l_no_flags, dbms_lob.substr(clob_buff,to_index-from_index-1,from_index+1));
287 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_PROCEDURE,'Inside Main','Finished parsing. Inserting record with DUNS' || ccr_data(1));
288 end if;
289
290 --Adding call to parse Disaster Response String.
291 if l_no_flags > 0 then
292 from_index :=to_index+2;
293 else
294 from_index :=to_index+4;
295 end if;
296 insert_ccr_codes(clob_buff,0,0,'"',9,from_index,ccr_data(1),to_index);
297
298 /*for i in ccr_data.first..24 loop
299 dbms_output.put_line('ccr_data(' ||i||') is :' ||ccr_data(i)||'::');
300 end loop;
301 for i in 30..ccr_data.last loop
302 dbms_output.put_line('ccr_data(' ||i||') is :' ||ccr_data(i)||'::');
303 end loop;*/
304 INSERT INTO FV_CCR_FILE_TEMP(DUNS
305 ,PLUS_FOUR
306 ,CAGE_CODE
307 ,EXTRACT_CODE
308 ,REGISTRATION_DATE
309 ,RENEWAL_DATE
310 ,LEGAL_BUS_NAME
311 ,DBA_NAME
312 ,DIVISION_NAME
313 ,DIVISION_NUMBER
314 ,ST_ADDRESS1
315 ,ST_ADDRESS2
316 ,CITY
317 ,STATE
318 ,POSTAL_CODE
319 ,COUNTRY
320 ,BUSINESS_START_DATE
321 ,FISCAL_YR_CLOSE_DATE
322 ,CORP_SECURITY_LEVEL
323 ,EMP_SECURITY_LEVEL
324 ,WEB_SITE
325 ,ORGANIZATIONAL_TYPE
326 ,STATE_OF_INC
327 ,COUNTRY_OF_INC
328 ,CREDIT_CARD_FLAG
329 ,CORRESPONDENCE_FLAG
330 ,MAIL_POC
331 ,MAIL_ADD1
332 ,MAIL_ADD2
333 ,MAIL_CITY
334 ,MAIL_POSTAL_CODE
335 ,MAIL_COUNTRY
336 ,MAIL_STATE
337 ,PREV_BUS_POC
338 ,PREV_BUS_ADD1
339 ,PREV_BUS_ADD2
340 ,PREV_BUS_CITY
341 ,PREV_BUS_POSTAL_CODE
342 ,PREV_BUS_COUNTRY
343 ,PREV_BUS_STATE
344 ,PARENT_POC
345 ,PARENT_DUNS
346 ,PARENT_ADD1
347 ,PARENT_ADD2
348 ,PARENT_CITY
349 ,PARENT_POSTAL_CODE
350 ,PARENT_COUNTRY
351 ,PARENT_STATE
352 ,GOV_PARENT_POC
353 ,GOV_PARENT_ADD1
354 ,GOV_PARENT_ADD2
355 ,GOV_PARENT_CITY
356 ,GOV_PARENT_POSTAL_CODE
357 ,GOV_PARENT_COUNTRY
358 ,GOV_PARENT_STATE
359 ,GOV_BUS_POC
360 ,GOV_BUS_ADD1
361 ,GOV_BUS_ADD2
362 ,GOV_BUS_CITY
363 ,GOV_BUS_POSTAL_CODE
364 ,GOV_BUS_COUNTRY
365 ,GOV_BUS_STATE
366 ,GOV_BUS_US_PHONE
367 ,GOV_BUS_US_PHONE_EX
368 ,GOV_BUS_NON_US_PHONE
369 ,GOV_BUS_FAX
370 ,GOV_BUS_EMAIL
371 ,ALT_GOV_BUS_POC
372 ,ALT_GOV_BUS_ADD1
373 ,ALT_GOV_BUS_ADD2
374 ,ALT_GOV_BUS_CITY
375 ,ALT_GOV_BUS_POSTAL_CODE
376 ,ALT_GOV_BUS_COUNTRY
377 ,ALT_GOV_BUS_STATE
378 ,ALT_GOV_BUS_US_PHONE
379 ,ALT_GOV_BUS_US_PHONE_EX
380 ,ALT_GOV_BUS_NON_US_PHONE
381 ,ALT_GOV_BUS_FAX
382 ,ALT_GOV_BUS_EMAIL
383 ,PAST_PERF_POC
384 ,PAST_PERF_ADD1
385 ,PAST_PERF_ADD2
386 ,PAST_PERF_CITY
387 ,PAST_PERF_POSTAL_CODE
388 ,PAST_PERF_COUNTRY
389 ,PAST_PERF_STATE
390 ,PAST_PERF_US_PHONE
391 ,PAST_PERF_US_PHONE_EX
392 ,PAST_PERF_NON_US_PHONE
393 ,PAST_PERF_FAX
394 ,PAST_PERF_EMAIL
395 ,ALT_PAST_PERF_POC
396 ,ALT_PAST_PERF_ADD1
397 ,ALT_PAST_PERF_ADD2
398 ,ALT_PAST_PERF_CITY
399 ,ALT_PAST_PERF_POSTAL_CODE
400 ,ALT_PAST_PERF_COUNTRY
401 ,ALT_PAST_PERF_STATE
402 ,ALT_PAST_PERF_US_PHONE
403 ,ALT_PAST_PERF_US_PHONE_EX
404 ,ALT_PAST_PERF_NON_US_PHONE
405 ,ALT_PAST_PERF_FAX
406 ,ALT_PAST_PERF_EMAIL
407 ,ELEC_BUS_POC
408 ,ELEC_BUS_ADD1
409 ,ELEC_BUS_ADD2
410 ,ELEC_BUS_CITY
411 ,ELEC_BUS_POSTAL_CODE
412 ,ELEC_BUS_COUNTRY
413 ,ELEC_BUS_STATE
414 ,ELEC_BUS_US_PHONE
415 ,ELEC_BUS_US_PHONE_EX
416 ,ELEC_BUS_NON_US_PHONE
417 ,ELEC_BUS_FAX
418 ,ELEC_BUS_EMAIL
419 ,ALT_ELEC_BUS_POC
420 ,ALT_ELEC_BUS_ADD1
421 ,ALT_ELEC_BUS_ADD2
422 ,ALT_ELEC_BUS_CITY
423 ,ALT_ELEC_BUS_POSTAL_CODE
424 ,ALT_ELEC_BUS_COUNTRY
425 ,ALT_ELEC_BUS_STATE
426 ,ALT_ELEC_BUS_US_PHONE
427 ,ALT_ELEC_BUS_US_PHONE_EX
428 ,ALT_ELEC_BUS_NON_US_PHONE
429 ,ALT_ELEC_BUS_FAX
430 ,ALT_ELEC_BUS_EMAIL
431 ,CERTIFIER_POC
432 ,CERTIFIER_US_PHONE
433 ,CERTIFIER_US_PHONE_EX
434 ,CERTIFIER_NON_US_PHONE
435 ,CERTIFIER_FAX
436 ,CERTIFIER_EMAIL
437 ,ALT_CERTIFIER_POC
438 ,ALT_CERTIFIER_US_PHONE
439 ,ALT_CERTIFIER_US_PHONE_EX
440 ,ALT_CERTIFIER_NON_US_PHONE
441 ,CORP_INFO_POC
442 ,CORP_INFO_US_PHONE
443 ,CORP_INFO_US_PHONE_EX
444 ,CORP_INFO_NON_US_PHONE
445 ,CORP_INFO_FAX
446 ,CORP_INFO_EMAIL
447 ,OWNER_INFO_POC
448 ,OWNER_INFO_US_PHONE
449 ,OWNER_INFO_US_PHONE_EX
450 ,OWNER_INFO_NON_US_PHONE
451 ,OWNER_INFO_FAX
452 ,OWNER_INFO_EMAIL
453 ,HQ_PARENT_POC
454 ,HQ_PARENT_DUNS
455 ,HQ_PARENT_ADD1
456 ,HQ_PARENT_ADD2
457 ,HQ_PARENT_CITY
458 ,HQ_PARENT_POSTAL_CODE
459 ,HQ_PARENT_COUNTRY
460 ,HQ_PARENT_STATE
461 ,HQ_PARENT_PHONE
462 ,DOMESTIC_PARENT_POC
463 ,DOMESTIC_PARENT_DUNS
464 ,DOMESTIC_PARENT_ADD1
465 ,DOMESTIC_PARENT_ADD2
466 ,DOMESTIC_PARENT_CITY
467 ,DOMESTIC_PARENT_POSTAL_CODE
468 ,DOMESTIC_PARENT_COUNTRY
469 ,DOMESTIC_PARENT_STATE
470 ,DOMESTIC_PARENT_PHONE
471 ,GLOBAL_PARENT_POC
472 ,GLOBAL_PARENT_DUNS
473 ,GLOBAL_PARENT_ADD1
474 ,GLOBAL_PARENT_ADD2
475 ,GLOBAL_PARENT_CITY
476 ,GLOBAL_PARENT_POSTAL_CODE
477 ,GLOBAL_PARENT_COUNTRY
478 ,GLOBAL_PARENT_STATE
479 ,GLOBAL_PARENT_PHONE
480 ,DNB_MONITOR_LAST_UPDATED
481 ,DNB_MONITOR_STATUS
482 ,DNB_MONITOR_CORP_NAME
483 ,DNB_MONITOR_DBA
484 ,DNB_MONITOR_ST_ADD1
485 ,DNB_MONITOR_ST_ADD2
486 ,DNB_MONITOR_CITY
487 ,DNB_MONITOR_POSTAL_CODE
488 ,DNB_MONITOR_COUNTRY_CODE
489 ,DNB_MONITOR_STATE
490 ,EDI
491 ,TAXPAYER_ID
492 ,AVG_NUM_EMPLOYEES
493 ,ANNUAL_RECEIPTS
494 ,SOCIAL_SECURITY_NUMBER
495 ,AUSTIN_TETRA_NUMBER
496 ,AUSTIN_TETRA_PARENT_NUMBER
497 ,AUSTIN_TETRA_ULTIMATE_NUMBER
498 ,AUSTIN_TETRA_PCARD_FLAG
499 ,FINANCIAL_INSTITUTE
500 ,BANK_ACCT_NUMBER
501 ,ABA_ROUTING
502 ,PAYMENT_TYPE
503 ,LOCKBOX_NUMBER
504 ,AUTHORIZATION_DATE
505 ,EFT_WAIVER
506 ,ACH_US_PHONE
507 ,ACH_NON_US_PHONE
508 ,ACH_FAX
509 ,ACH_EMAIL
510 ,REMIT_POC
511 ,REMIT_ADD1
512 ,REMIT_ADD2
513 ,REMIT_CITY
514 ,REMIT_STATE
515 ,REMIT_POSTAL_CODE
516 ,REMIT_COUNTRY
517 ,AR_POC
518 ,AR_US_PHONE
519 ,AR_US_PHONE_EX
520 ,AR_NON_US_PHONE
521 ,AR_FAX
522 ,AR_EMAIL
523 ,MPIN
524 ,FILE_DATE)
525 values(ccr_data(1)
526 ,ccr_data(2)
527 ,ccr_data(3)
528 ,ccr_data(4)
529 ,to_date(ccr_data(5),'mmddrryy')
530 ,to_date(ccr_data(6),'mmddrryy')
531 ,ccr_data(7)
532 ,ccr_data(8)
533 ,ccr_data(9)
534 ,ccr_data(10)
535 ,ccr_data(11)
536 ,ccr_data(12)
537 ,ccr_data(13)
538 ,ccr_data(14)
539 ,ccr_data(15)
540 ,ccr_data(16)
541 ,to_date(ccr_data(17),'mmddrryy')
542 ,ccr_data(18)
543 ,ccr_data(19)
544 ,ccr_data(20)
545 ,ccr_data(21)
546 ,ccr_data(22)
547 ,ccr_data(23)
548 ,ccr_data(24)
549 ,ccr_data(30)
550 ,ccr_data(31)
551 ,ccr_data(32)
552 ,ccr_data(33)
553 ,ccr_data(34)
554 ,ccr_data(35)
555 ,ccr_data(36)
556 ,ccr_data(37)
557 ,ccr_data(38)
558 ,ccr_data(39)
559 ,ccr_data(40)
560 ,ccr_data(41)
561 ,ccr_data(42)
562 ,ccr_data(43)
563 ,ccr_data(44)
564 ,ccr_data(45)
565 ,ccr_data(46)
566 ,ccr_data(47)
567 ,ccr_data(48)
568 ,ccr_data(49)
569 ,ccr_data(50)
570 ,ccr_data(51)
571 ,ccr_data(52)
572 ,ccr_data(53)
573 ,ccr_data(54)
574 ,ccr_data(55)
575 ,ccr_data(56)
576 ,ccr_data(57)
577 ,ccr_data(58)
578 ,ccr_data(59)
579 ,ccr_data(60)
580 ,ccr_data(61)
581 ,ccr_data(62)
582 ,ccr_data(63)
583 ,ccr_data(64)
584 ,ccr_data(65)
585 ,ccr_data(66)
586 ,ccr_data(67)
587 ,ccr_data(68)
588 ,ccr_data(69)
589 ,ccr_data(70)
590 ,ccr_data(71)
591 ,ccr_data(72)
592 ,ccr_data(73)
593 ,ccr_data(74)
594 ,ccr_data(75)
595 ,ccr_data(76)
596 ,ccr_data(77)
597 ,ccr_data(78)
598 ,ccr_data(79)
599 ,ccr_data(80)
600 ,ccr_data(81)
601 ,ccr_data(82)
602 ,ccr_data(83)
603 ,ccr_data(84)
604 ,ccr_data(85)
605 ,ccr_data(86)
606 ,ccr_data(87)
607 ,ccr_data(88)
608 ,ccr_data(89)
609 ,ccr_data(90)
610 ,ccr_data(91)
611 ,ccr_data(92)
612 ,ccr_data(93)
613 ,ccr_data(94)
614 ,ccr_data(95)
615 ,ccr_data(96)
616 ,ccr_data(97)
617 ,ccr_data(98)
618 ,ccr_data(99)
619 ,ccr_data(100)
620 ,ccr_data(101)
621 ,ccr_data(102)
622 ,ccr_data(103)
623 ,ccr_data(104)
624 ,ccr_data(105)
625 ,ccr_data(106)
626 ,ccr_data(107)
627 ,ccr_data(108)
628 ,ccr_data(109)
629 ,ccr_data(110)
630 ,ccr_data(111)
631 ,ccr_data(112)
632 ,ccr_data(113)
633 ,ccr_data(114)
634 ,ccr_data(115)
635 ,ccr_data(116)
636 ,ccr_data(117)
637 ,ccr_data(118)
638 ,ccr_data(119)
639 ,ccr_data(120)
640 ,ccr_data(121)
641 ,ccr_data(122)
642 ,ccr_data(123)
643 ,ccr_data(124)
644 ,ccr_data(125)
645 ,ccr_data(126)
646 ,ccr_data(127)
647 ,ccr_data(128)
648 ,ccr_data(129)
649 ,ccr_data(130)
650 ,ccr_data(131)
651 ,ccr_data(132)
652 ,ccr_data(133)
653 ,ccr_data(134)
654 ,ccr_data(135)
655 ,ccr_data(136)
656 ,ccr_data(137)
657 ,ccr_data(138)
658 ,ccr_data(139)
659 ,ccr_data(140)
660 ,ccr_data(141)
661 ,ccr_data(142)
662 ,ccr_data(143)
663 ,ccr_data(144)
664 ,ccr_data(145)
665 ,ccr_data(146)
666 ,ccr_data(147)
667 ,ccr_data(148)
668 ,ccr_data(149)
669 ,ccr_data(150)
670 ,ccr_data(151)
671 ,ccr_data(152)
672 ,ccr_data(153)
673 ,ccr_data(154)
674 ,ccr_data(155)
675 ,ccr_data(156)
676 ,ccr_data(157)
677 ,ccr_data(158)
678 ,ccr_data(159)
679 ,ccr_data(160)
680 ,ccr_data(161)
681 ,ccr_data(162)
682 ,ccr_data(163)
683 ,ccr_data(164)
684 ,ccr_data(165)
685 ,ccr_data(166)
686 ,ccr_data(167)
687 ,ccr_data(168)
688 ,ccr_data(169)
689 ,ccr_data(170)
690 ,ccr_data(171)
691 ,ccr_data(172)
692 ,ccr_data(173)
693 ,ccr_data(174)
694 ,ccr_data(175)
695 ,ccr_data(176)
696 ,ccr_data(177)
697 ,ccr_data(178)
698 ,ccr_data(179)
699 ,ccr_data(180)
700 ,ccr_data(181)
701 ,ccr_data(182)
702 ,ccr_data(183)
703 ,ccr_data(184)
704 ,ccr_data(185)
705 ,ccr_data(186)
706 ,ccr_data(187)
707 ,ccr_data(188)
708 ,ccr_data(189)
709 ,ccr_data(190)
710 ,ccr_data(191)
711 ,ccr_data(192)
712 ,ccr_data(193)
713 ,ccr_data(194)
714 ,ccr_data(195)
715 ,ccr_data(196)
716 ,ccr_data(197)
717 ,ccr_data(198)
718 ,ccr_data(199)
719 ,ccr_data(200)
720 ,ccr_data(201)
721 ,ccr_data(202)
722 ,ccr_data(203)
723 ,ccr_data(204)
724 ,ccr_data(205)
725 ,to_date(ccr_data(206),'mmddrryy')
726 ,ccr_data(207)
727 ,ccr_data(208)
728 ,ccr_data(209)
729 ,ccr_data(210)
730 ,ccr_data(211)
731 ,ccr_data(212)
732 ,ccr_data(213)
733 ,ccr_data(214)
734 ,ccr_data(215)
735 ,ccr_data(216)
736 ,ccr_data(217)
737 ,ccr_data(218)
738 ,ccr_data(219)
739 ,ccr_data(220)
740 ,ccr_data(221)
741 ,ccr_data(222)
742 ,ccr_data(223)
743 ,ccr_data(224)
744 ,ccr_data(225)
745 ,sysdate); --Temporary substitute for file_date
746 end if;
747 from_index :=1;
748 to_index :=0;
749 end loop;
750 commit;
751 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside Main','Committed changes. End of Transfer');
752 exception
753 when others then
754 l_errbuf := ' Exception: Inside BPN MAIN ->'|| SQLERRM;
755 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, 'Inside BPN MAIN',l_errbuf);
756 raise_application_error(-20300, l_errbuf);
757 /* l_errbuf := 'Exception occurred '||SQLERRM;
758 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name,l_errbuf);
759 rollback;*/
760
761 end main;
762
763 procedure delete_ccr_codes(p_duns in varchar2)
764 is
765 l_module_name varchar2(60);
766 l_errbuf varchar2(500);
767 begin
768 l_module_name := 'delete_ccr_codes';
769 l_errbuf := 'Deleting the entries from fv_ccr_codes for Duns '||p_duns;
770
771 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, l_errbuf);
772 begin
773 delete from fv_ccr_class_codes where duns = p_duns;
774 exception when others then
775 l_errbuf := 'Following exception encountered during deletion of codes and is ignored:'||SQLERRM;
776 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, l_errbuf);
777 null;
778 end;
779 end delete_ccr_codes;
780
781
782 procedure delete_ccr_flags(p_duns in varchar2)
783 is
784 l_module_name varchar2(60);
785 l_errbuf varchar2(500);
786 begin
787 l_module_name := 'delete_ccr_flags';
788 l_errbuf := 'Deleting the entries from fv_ccr_flags for Duns '||p_duns;
789
790 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, l_errbuf);
791 begin
792 delete from fv_ccr_flags where duns = p_duns;
793 exception when others then
794 l_errbuf := 'Following exception encountered during deletion of flags and is ignored:'||SQLERRM;
795 FV_UTILITY.LOG_MESG(FND_LOG.LEVEL_STATEMENT, l_module_name, l_errbuf);
796 null;
797 end;
798 end delete_ccr_flags;
799
800 end fv_ccr_bpn_load_pkg;
801