[Home] [Help]
PACKAGE BODY: APPS.EDW_DUNS_M_C
Source
1 Package Body EDW_DUNS_M_C AS
2 /* $Header: poaphtpb.pls 115.20 2004/02/26 13:53:00 apalorka ship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7 g_start_time Date:=Null;
8
9 Procedure Push(Errbuf in out NOCOPY Varchar2,
10 Retcode in out NOCOPY Varchar2,
11 p_from_date IN Varchar2,
12 p_to_date IN Varchar2) IS
13 l_dimension_name Varchar2(30) :='EDW_DUNS_M' ;
14 l_temp_date Date:=Null;
15 l_rows_inserted Number:=0;
16 l_duration Number:=0;
17 l_exception_msg Varchar2(2000):=Null;
18
19 -- -------------------------------------------
20 -- Put any additional developer variables here
21 -- -------------------------------------------
22 l_from_date date;
23 l_to_date date;
24
25 Begin
26 Errbuf :=NULL;
27 Retcode:=0;
28
29 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
30 errbuf := fnd_message.get;
31 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
32 END IF;
33
34 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
35 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
36
37 EDW_DUNS_M_C.g_push_date_range1 := nvl(l_from_date,
38 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
39 EDW_COLLECTION_UTIL.g_offset);
40
41 EDW_DUNS_M_C.g_push_date_range2 := nvl(l_to_date,
42 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
43
44 edw_log.put_line( 'The collection range is from '||
45 to_char(EDW_DUNS_M_C.g_push_date_range1,
46 'MM/DD/YYYY HH24:MI:SS')||' to '||
47 to_char(EDW_DUNS_M_C.g_push_date_range2,
48 'MM/DD/YYYY HH24:MI:SS'));
49
50 edw_log.put_line(' ');
51 edw_log.put_line('Pushing data');
52
53 Push_EDW_DNB_TPRT();
54 Push_EDW_DUNS_NUMBER_LSTG(EDW_DUNS_M_C.g_push_date_range1,
55 EDW_DUNS_M_C.g_push_date_range2);
56
57 Push_EDW_DUNS_PARENT_LSTG(EDW_DUNS_M_C.g_push_date_range1,
58 EDW_DUNS_M_C.g_push_date_range2);
59
60 Push_EDW_DUNS_DOMESTIC_LSTG(EDW_DUNS_M_C.g_push_date_range1,
61 EDW_DUNS_M_C.g_push_date_range2);
62
63 Push_EDW_DUNS_HEADQTR_LSTG(EDW_DUNS_M_C.g_push_date_range1,
64 EDW_DUNS_M_C.g_push_date_range2);
65
66 Push_EDW_DUNS_GLOBAL_LSTG(EDW_DUNS_M_C.g_push_date_range1,
67 EDW_DUNS_M_C.g_push_date_range2);
68
69 Push_EDW_SICM_SIC_LSTG(EDW_DUNS_M_C.g_push_date_range1,
70 EDW_DUNS_M_C.g_push_date_range2);
71
72 l_duration := sysdate - l_temp_date;
73
74 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
75 edw_log.put_line(' ');
76
77 -- ---------------------------------------------------------------------------
78 -- END OF Collection , Developer Customizable Section
79 -- ---------------------------------------------------------------------------
80 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, EDW_DUNS_M_C.g_exception_msg,
81 g_push_date_range1, g_push_date_range2);
82 commit;
83
84 Exception When others then
85 Errbuf:=sqlerrm;
86 Retcode:=sqlcode;
87 l_exception_msg := Retcode || ':' || Errbuf;
88 EDW_DUNS_M_C.g_exception_msg := l_exception_msg;
89 rollback;
90
91 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_DUNS_M_C.g_exception_msg,
92 g_push_date_range1, g_push_date_range2);
93
94 commit;
95 End Push;
96
97 Procedure Push_EDW_DNB_TPRT IS
98 CURSOR c_vendors IS
99 select distinct Trade_Partner_FK, Name
100 from EDW_TPRT_TPARTNER_LOC_LTC TPRT,
101 POA_TPRT_INTERFACE dnb
102 where (TPRT.TPartner_Loc_PK = dnb.Trading_Partner_PK);
103
104 CURSOR c_dnb_failure IS
105 select distinct FAILURE_IND
106 from POA_TPRT_INTERFACE dnb
107 where (FAILURE_IND IS NOT NULL);
108
109 CURSOR c_dnb_high_risk IS
110 select distinct HIGH_RISK_INDCATOR
111 from POA_TPRT_INTERFACE dnb
112 where (HIGH_RISK_INDCATOR IS NOT NULL);
113
114 CURSOR c_dnb_minority_owned IS
115 select distinct MINORITY_OWNED_IND
116 from POA_TPRT_INTERFACE dnb
117 where (MINORITY_OWNED_IND IS NOT NULL);
118
119 CURSOR c_dnb_current_statement IS
120 select distinct CURRENT_STATEMENT_TYPE
121 from POA_TPRT_INTERFACE dnb
122 where (CURRENT_STATEMENT_TYPE IS NOT NULL);
123
124 CURSOR c_dnb_legal_status IS
125 select distinct LEGAL_STATUS
126 from POA_TPRT_INTERFACE dnb
127 where (LEGAL_STATUS IS NOT NULL);
128
129 CURSOR c_dnb_fire_disaster IS
130 select distinct FIRE_DISASTER_IND
131 from POA_TPRT_INTERFACE dnb
132 where (FIRE_DISASTER_IND IS NOT NULL);
133
134 CURSOR c_dnb_owns_rents IS
135 select distinct OWNS_RENTS_IND
136 from POA_TPRT_INTERFACE dnb
137 where (OWNS_RENTS_IND IS NOT NULL);
138
139 CURSOR c_dnb_history IS
140 select distinct HISTORY
141 from POA_TPRT_INTERFACE dnb
142 where (HISTORY IS NOT NULL);
143
144 CURSOR c_dnb_location_status IS
145 select distinct LOCATION_STATUS_IND
146 from POA_TPRT_INTERFACE dnb
147 where (LOCATION_STATUS_IND IS NOT NULL);
148
149 CURSOR c_dnb_oob IS
150 select distinct OOB_IND
151 from POA_TPRT_INTERFACE dnb
152 where (OOB_IND IS NOT NULL);
153
154 CURSOR c_dnb_criminal_proceedings IS
155 select distinct CRIMINAL_PROCEEDINGS_IND
156 from POA_TPRT_INTERFACE dnb
157 where (CRIMINAL_PROCEEDINGS_IND IS NOT NULL);
158
159 CURSOR c_dnb_bankruptcy IS
160 select distinct BANKRUPTCY_IND
161 from POA_TPRT_INTERFACE dnb
162 where (BANKRUPTCY_IND IS NOT NULL);
163
164 CURSOR c_dnb_business_moved IS
165 select distinct BUSINESS_MOVED_IND
166 from POA_TPRT_INTERFACE dnb
167 where (BUSINESS_MOVED_IND IS NOT NULL);
168
169 BEGIN
170
171 -- Update the lookup values in the Interface table
172 edw_log.put_line('Updating dnb failure code');
173 For lfailure in c_dnb_failure loop
174 Update POA_TPRT_INTERFACE dnb
175 set (FAILURE_VALUE) =
176 (select Description
177 from fnd_lookups fnd
178 where ((fnd.lookup_code = lfailure.FAILURE_IND) and
179 (fnd.lookup_type = 'POA_DNB_FAILURE_IND')))
180 where (dnb.FAILURE_IND = lfailure.FAILURE_IND);
181 end loop;
182
183 Update POA_TPRT_INTERFACE dnb
184 set FAILURE_VALUE = FAILURE_IND
185 where (FAILURE_VALUE IS NULL);
186
187 edw_log.put_line('Updating Dnb High Risk Code');
188 For lhigh_risk in c_dnb_high_risk loop
189 Update POA_TPRT_INTERFACE dnb
190 set (HIGH_RISK_VALUE) =
191 (select Description
192 from fnd_lookups fnd
193 where ((fnd.lookup_code = lhigh_risk.HIGH_RISK_INDCATOR) and
194 (fnd.lookup_type = 'POA_DNB_HIGH_RISK_IND')))
195 where (dnb.HIGH_RISK_INDCATOR = lhigh_risk.HIGH_RISK_INDCATOR);
196 end loop;
197
198 Update POA_TPRT_INTERFACE dnb
199 set HIGH_RISK_VALUE = HIGH_RISK_INDCATOR
200 where (HIGH_RISK_VALUE IS NULL);
201
202 edw_log.put_line('Updating Minority Code');
203 For lminority_owned in c_dnb_minority_owned loop
204 Update POA_TPRT_INTERFACE dnb
205 set (MINORITY_OWNED_VALUE) =
206 (select Description
207 from fnd_lookups fnd
208 where ((fnd.lookup_code = lminority_owned.MINORITY_OWNED_IND) and
209 (fnd.lookup_type = 'POA_DNB_MINORITY_OWNED_TYPE')))
210 where (dnb.MINORITY_OWNED_IND = lminority_owned.MINORITY_OWNED_IND);
211 end loop;
212
213 Update POA_TPRT_INTERFACE dnb
214 set MINORITY_OWNED_VALUE = MINORITY_OWNED_IND
215 where (MINORITY_OWNED_VALUE IS NULL);
216
217 edw_log.put_line('Updating DNB Current Statement');
218 For lcurrent_statement in c_dnb_current_statement loop
219 Update POA_TPRT_INTERFACE dnb
220 set (CURRENT_STATEMENT_VALUE) =
221 (select Description
222 from fnd_lookups fnd
223 where ((fnd.lookup_code =
224 lcurrent_statement.CURRENT_STATEMENT_TYPE) and
225 (fnd.lookup_type = 'POA_DNB_CURRENT_STMT_TYPE')))
226 where (dnb.CURRENT_STATEMENT_TYPE =
227 lcurrent_statement.CURRENT_STATEMENT_TYPE);
228 end loop;
229
230 Update POA_TPRT_INTERFACE dnb
231 set CURRENT_STATEMENT_VALUE = CURRENT_STATEMENT_TYPE
232 where (CURRENT_STATEMENT_VALUE IS NULL);
233
234 edw_log.put_line('Updating Legal Status');
235 For llegal_status in c_dnb_legal_status loop
236 Update POA_TPRT_INTERFACE dnb
237 set (LEGAL_STATUS_VALUE) =
238 (select Description
239 from fnd_lookups fnd
240 where ((fnd.lookup_code = llegal_status.LEGAL_STATUS) and
241 (fnd.lookup_type = 'POA_DNB_LEGAL_STATUS')))
242 where (dnb.LEGAL_STATUS = llegal_status.LEGAL_STATUS);
243 end loop;
244
245 Update POA_TPRT_INTERFACE dnb
246 set LEGAL_STATUS_VALUE = LEGAL_STATUS
247 where (LEGAL_STATUS_VALUE IS NULL);
248
249 edw_log.put_line('Updating Fire Disaster Code');
250 For lfire_disaster in c_dnb_fire_disaster loop
251 Update POA_TPRT_INTERFACE dnb
252 set (FIRE_DISASTER_VALUE) =
253 (select Description
254 from fnd_lookups fnd
255 where ((fnd.lookup_code = lfire_disaster.FIRE_DISASTER_IND) and
256 (fnd.lookup_type = 'POA_DNB_FIRE_DISASTER_IND')))
257 where (dnb.FIRE_DISASTER_IND = lfire_disaster.FIRE_DISASTER_IND);
258 end loop;
259
260 Update POA_TPRT_INTERFACE dnb
261 set FIRE_DISASTER_VALUE = FIRE_DISASTER_IND
262 where (FIRE_DISASTER_VALUE IS NULL);
263
264 edw_log.put_line('Updating DNB owns/Rents Code');
265 For lowns_rents in c_dnb_owns_rents loop
266 Update POA_TPRT_INTERFACE dnb
267 set (OWNS_RENTS_VALUE) =
268 (select Description
269 from fnd_lookups fnd
270 where ((fnd.lookup_code = lowns_rents.OWNS_RENTS_IND) and
271 (fnd.lookup_type = 'POA_DNB_OWNS_RENTS_IND')))
272 where (dnb.OWNS_RENTS_IND = lowns_rents.OWNS_RENTS_IND);
273 end loop;
274
275 Update POA_TPRT_INTERFACE dnb
276 set OWNS_RENTS_VALUE = OWNS_RENTS_IND
277 where (OWNS_RENTS_VALUE IS NULL);
278
279 edw_log.put_line('Updating History Code');
280 For lhistory in c_dnb_history loop
281 Update POA_TPRT_INTERFACE dnb
282 set (HISTORY_VALUE) =
283 (select Description
284 from fnd_lookups fnd
285 where ((fnd.lookup_code = lhistory.HISTORY) and
286 (fnd.lookup_type = 'POA_DNB_HISTORY_IND')))
287 where (dnb.HISTORY = lhistory.HISTORY);
288 end loop;
289
290 Update POA_TPRT_INTERFACE dnb
291 set HISTORY_VALUE = HISTORY
292 where (HISTORY IS NULL);
293
294 edw_log.put_line('Updating Location Status Code');
295 For llocation_status in c_dnb_location_status loop
296 Update POA_TPRT_INTERFACE dnb
297 set (LOCATION_STATUS_VALUE) =
298 (select Description
299 from fnd_lookups fnd
300 where ((fnd.lookup_code = llocation_status.LOCATION_STATUS_IND) and
301 (fnd.lookup_type = 'POA_DNB_LOCATION_STATUS')))
302 where (dnb.LOCATION_STATUS_IND = llocation_status.LOCATION_STATUS_IND);
303 end loop;
304
305 Update POA_TPRT_INTERFACE dnb
306 set LOCATION_STATUS_VALUE = LOCATION_STATUS_IND
307 where (LOCATION_STATUS_VALUE IS NULL);
308
309 edw_log.put_line('Updating DNB OOB');
310 For loob in c_dnb_oob loop
311 Update POA_TPRT_INTERFACE dnb
312 set (OOB_VALUE) =
313 (select Description
314 from fnd_lookups fnd
315 where ((fnd.lookup_code = loob.OOB_IND) and
316 (fnd.lookup_type = 'POA_DNB_OOB_IND')))
317 where (dnb.OOB_IND = loob.OOB_IND);
318 end loop;
319
320 Update POA_TPRT_INTERFACE dnb
321 set OOB_VALUE = OOB_IND
322 where (OOB_VALUE IS NULL);
323
324 edw_log.put_line('Updating Criminal Proceedings Code');
325 For lcriminal_proceedings in c_dnb_criminal_proceedings loop
326 Update POA_TPRT_INTERFACE dnb
327 set (CRIMINAL_PROCEEDINGS_VALUE) =
328 (select Description
329 from fnd_lookups fnd
330 where ((fnd.lookup_code =
331 lcriminal_proceedings.CRIMINAL_PROCEEDINGS_IND) and
332 (fnd.lookup_type = 'POA_DNB_CRIMINAL_PROCDN_IND')))
333 where (dnb.CRIMINAL_PROCEEDINGS_IND =
334 lcriminal_proceedings.CRIMINAL_PROCEEDINGS_IND);
335 end loop;
336
337 Update POA_TPRT_INTERFACE dnb
338 set CRIMINAL_PROCEEDINGS_VALUE = CRIMINAL_PROCEEDINGS_IND
339 where (CRIMINAL_PROCEEDINGS_VALUE IS NULL);
340
341 edw_log.put_line('Updating Bankruptcy Code');
342 For lbankruptcy in c_dnb_bankruptcy loop
343 Update POA_TPRT_INTERFACE dnb
344 set (BANKRUPTCY_VALUE) =
345 (select Description
346 from fnd_lookups fnd
347 where ((fnd.lookup_code = lbankruptcy.BANKRUPTCY_IND) and
348 (fnd.lookup_type = 'POA_DNB_BANKRUPTCY_IND')))
349 where (dnb.BANKRUPTCY_IND = lbankruptcy.BANKRUPTCY_IND);
350 end loop;
351
352 Update POA_TPRT_INTERFACE dnb
353 set BANKRUPTCY_VALUE = BANKRUPTCY_IND
354 where (BANKRUPTCY_VALUE IS NULL);
355
356 edw_log.put_line('Updating Business Moved Code');
357 For lbusiness_moved in c_dnb_business_moved loop
358 Update POA_TPRT_INTERFACE dnb
359 set (BUSINESS_MOVED_VALUE) =
360 (select Description
361 from fnd_lookups fnd
362 where ((fnd.lookup_code = lbusiness_moved.BUSINESS_MOVED_IND) and
363 (fnd.lookup_type = 'POA_DNB_BUSINESS_MOVED_IND')))
364 where (dnb.BUSINESS_MOVED_IND = lbusiness_moved.BUSINESS_MOVED_IND);
365 end loop;
366
367 Update POA_TPRT_INTERFACE dnb
368 set BUSINESS_MOVED_VALUE = BUSINESS_MOVED_IND
369 where (BUSINESS_MOVED_VALUE IS NULL);
370
371 -- Update Trading Partner Vendor Sites (Lowest Level)
372 edw_log.put_line('Starting Push_EDW_DNB_TPRT');
373 edw_log.put_line('Update Trading Partner Vendor Sites (Lowest Level)');
374
375 g_start_time := sysdate;
376
377 Update POA_DNB_TRD_PRTNR poa
378 set (TRADING_PARTNER_PK,
379 TRADING_PARTNER_NAME,
380 DUNS,
381 SIC_CODE,
382 DNB_Update_Date,
383 LAST_UPDATE_DATE) =
384 (select TRADING_PARTNER_PK,
385 TRADING_PARTNER_NAME,
386 DUNS,
387 SIC_CODE_1,
388 sysdate,
389 sysdate from POA_TPRT_INTERFACE dnb
390 where (poa.TRADING_PARTNER_PK = dnb.TRADING_PARTNER_PK))
391 where TRADING_PARTNER_PK IN
392 (select TRADING_PARTNER_PK
393 from POA_TPRT_INTERFACE dnb
394 where ((poa.TRADING_PARTNER_PK = dnb.TRADING_PARTNER_PK) and
395 ((poa.DUNS <> dnb.DUNS) OR
396 (poa.SIC_CODE <> dnb.SIC_CODE_1))));
397
398 edw_log.put_line('Update of Trading Partner Vendor Sites complete');
399 edw_log.put_line('Insert Trading Partner Vendor Sites (Lowest Level)');
400 insert into POA_DNB_TRD_PRTNR poa (
401 TRADING_PARTNER_PK,
402 TRADING_PARTNER_NAME,
403 DUNS,
404 SIC_CODE,
405 DNB_Update_Date,
406 LAST_UPDATE_DATE,
407 CREATION_DATE)
408 select TRADING_PARTNER_PK,
409 TRADING_PARTNER_NAME,
410 DUNS,
411 SIC_CODE_1,
412 sysdate,
413 sysdate,
414 sysdate from POA_TPRT_INTERFACE dnb
415 where NOT EXISTS
416 (select 'X' FROM
417 POA_DNB_TRD_PRTNR pdtp
418 where ltrim(rtrim(pdtp.TRADING_PARTNER_PK)) IS NOT NULL
419 and dnb.TRADING_PARTNER_PK = pdtp.TRADING_PARTNER_PK);
420
421 edw_log.put_line('Insert of Trading Partner Vendor Sites complete');
422
423 /*
424 -- Commenting this portion out for bug 2377655 (Ford)
425 -- This cursor is taking the maximum amount of time
426 -- and not returning any rows in their trace file
427 -- Looks like the vendor_site cursor is not going to return any
428 -- rows since tprt.tpartner_loc_pk and lvendor.trade_partner_fk
429 -- are not going to match because of the way there are constructed
430 -- Also, the duns interface table poa_tprt_interface has only
431 -- supplier site level records
432
433 -- Update Trading Partner Vendors (Higher levels)
434 edw_log.put_line('Update Trading Partner Vendors (Higher levels)');
435 BEGIN
436 For lvendor in c_vendors loop
437 DECLARE
438 CURSOR c_vendor_site IS
439 select Trade_Partner_FK, Global_Ult_Duns
440 from EDW_TPRT_TPARTNER_LOC_LTC TPRT,
441 POA_TPRT_INTERFACE dnb
442 where ((TPRT.TPartner_Loc_PK = lvendor.Trade_Partner_FK) and
443 (dnb.Trading_Partner_PK = TPRT.Trade_Partner_FK));
444 BEGIN
445 For lvsite in c_vendor_site loop
446 Update POA_DNB_TRD_PRTNR poa
447 set TRADING_PARTNER_PK = lvendor.Trade_Partner_FK,
448 TRADING_PARTNER_NAME = lvendor.Name,
449 DUNS = lvsite.Global_Ult_Duns,
450 DNB_Update_Date = sysdate
451 where Trading_Partner_PK IN
452 (select Trade_Partner_FK
453 from EDW_TPRT_TPARTNER_LOC_LTC TPRT,
454 POA_TPRT_INTERFACE dnb
455 where ((poa.TRADING_PARTNER_PK = lvendor.Trade_Partner_FK) and
456 (poa.DUNS <> lvsite.Global_Ult_Duns)));
457
458 edw_log.put_line('Update of Trading Partner Vendor complete');
459 edw_log.put_line('Insert Trading Partner Vendors (Higher levels)');
460
461 Insert into POA_DNB_TRD_PRTNR poa (
462 TRADING_PARTNER_PK,
463 TRADING_PARTNER_NAME,
464 DUNS,
465 DNB_Update_Date)
466 select lvendor.Trade_Partner_FK,
467 lvendor.Name,
468 lvsite.Global_Ult_Duns,
469 sysdate
470 from POA_DNB_TRD_PRTNR
471 where NOT EXISTS
472 (select 'X'
473 from POA_DNB_TRD_PRTNR pdtp
474 where ltrim(rtrim(Trading_Partner_PK)) IS NOT NULL
475 and pdtp.Trading_Partner_PK = lvendor.Trade_Partner_FK);
476 edw_log.put_line('Insert of Trading Partner Vendor complete');
477 exit;
478 END LOOP;
479 END;
480 END LOOP;
481 END Push_EDW_DNB_TPRT;
482 */
483 -- Update the SIC Code Combination Table
484 edw_log.put_line('Updating SIC Code');
485 Update POA_DNB_SIC_CODE poa
486 set (SIC_Code,
487 SIC_Description,
488 DNB_Update_Date) =
489 (select distinct SIC_CODE_1,
490 SIC_1_DESCRIPTION,
491 sysdate from POA_TPRT_INTERFACE dnb
492 where (poa.SIC_Code = dnb.SIC_CODE_1))
493 where SIC_Code IN
494 (select SIC_CODE_1
495 from POA_TPRT_INTERFACE dnb
496 where ((poa.SIC_Code = dnb.SIC_CODE_1) and
497 (poa.SIC_Description <> dnb.SIC_1_DESCRIPTION)));
498
499 edw_log.put_line('Inserting SIC Code');
500 insert into POA_DNB_SIC_CODE poa (
501 SIC_Code,
502 SIC_Description,
503 DNB_Update_Date)
504 (select distinct SIC_CODE_1,
505 SIC_1_DESCRIPTION,
506 sysdate from POA_TPRT_INTERFACE dnb
507 where NOT EXISTS
508 (select 'X'
509 from POA_DNB_SIC_CODE pdsc
510 where ltrim(rtrim(SIC_Code)) IS NOT NULL
511 and dnb.sic_code_1 = pdsc.sic_code));
512
513
514 edw_log.put_line('Completed Push_EDW_DNB_TPRT');
515 Exception When others then
516 raise;
517 commit;
518
519 END Push_EDW_DNB_TPRT;
520
521
522
523 Procedure Push_EDW_DUNS_NUMBER_LSTG(p_from_date IN date, p_to_date IN DATE) IS
524 l_date1 DATE;
525 l_date2 DATE;
526 l_rows_inserted NUMBER :=0;
527 BEGIN
528 edw_log.put_line('Starting Push_EDW__DUNS_NUMBER_LSTG');
529 l_date1 := p_from_date;
530 l_date2 := p_to_date;
531
532 Insert Into
533 EDW_DUNS_NUMBER_LSTG(
534 DUNS_NUM_PK,
535 DUNS_NUM_DP,
536 NAME,
537 DUNS_NUMBER,
538 COMPANY_NAME,
539 ADDRESS,
540 CITY,
541 STATE_PROV,
542 ZIP_CODE,
543 TELEPHONE,
544 COUNTRY,
545 CEO_NAME,
546 CEO_TITLE,
547 LEGAL_STATUS,
548 LOCATION_STATUS,
549 HQ_FLAG,
550 EMPLOYEES_TOTAL,
551 CONTROL_YEAR,
552 SIC_CODE1,
553 SIC_CODE2,
554 SIC1_DESCRIPTION,
555 OOB_IND,
556 CONG_DIST_CODE1,
557 CONG_DIST_CODE2,
558 CONG_DIST_CODE3,
559 IMPORT_FLAG,
560 EXPORT_FLAG,
561 FAILURE_IND,
562 BANKRUPTCY_IND,
563 HIGH_RISK_IND,
564 SUITS_COUNT,
565 LIENS_COUNT,
566 JUDGMENTS_COUNT,
567 HQ_DUNS,
568 HQ_NAME,
569 HQ_ADDRESS,
570 HQ_CITY,
571 HQ_STATE_PROV,
572 HQ_POSTAL_CODE,
573 HQ_COUNTRY,
574 HQ_TELEPHONE,
575 PARENT_DUNS,
576 PARENT_NAME,
577 PARENT_ADDRESS,
578 PARENT_CITY,
579 PARENT_STATE_PROV,
580 PARENT_POSTAL_CODE,
581 PARENT_COUNTRY,
582 PARENT_TELEPHONE,
583 GLOBAL_ULT_DUNS,
584 GLOBAL_ULT_NAME,
585 GLOBAL_ULT_ADDRESS,
586 GLOBAL_ULT_CITY,
587 GLOBAL_ULT_STATE_PROV,
588 GLOBAL_ULT_POSTAL_CODE,
589 GLOBAL_ULT_COUNTRY,
590 GLOBAL_ULT_TELEPHONE,
591 DOMESTIC_ULT_DUNS,
592 DOMESTIC_ULT_NAME,
593 DOMESTIC_ULT_ADDRESS,
594 DOMESTIC_ULT_CITY,
595 DOMESTIC_ULT_STATE_PROV,
596 DOMESTIC_ULT_POSTAL_CODE,
597 DOMESTIC_ULT_COUNTRY,
598 DOMESTIC_ULT_TELEPHONE,
599 DNB_RATING,
600 DELINQUENCY_SCORE,
601 FAILURE_SCORE,
602 FAILURE_NATL_PCNT_RANK,
603 FAILURE_INDU_PCNT_RANK,
604 FAILURE_NATL_INCE_DFT,
605 FAILURE_INDU_INCE_DFT,
606 PAYDEX_CURRENT,
607 LABOR_SURPLUS_FLAG,
608 DEBARMENT_FLAG,
609 MINORITY_OWNED_FLAG,
610 MINORITY_OWNED_TYPE,
611 WOMAN_OWNED_FLAG,
612 DISADVANTAGED_FLAG,
613 SMALL_BUSINESS_FLAG,
614 SDB_ENTRANCE_DATE,
615 SDB_EXIT_DATE,
616 ISO9000_REGISTRATION,
617 FEDERAL_TAX_ID,
618 CURRENT_STATEMENT_DATE,
619 CURRENT_STATEMENT_TYPE,
620 SALES,
621 CASH,
622 ACCOUNTS_RECEIVABLES,
623 INVENTORY,
624 CURRENT_ASSETS,
625 TOTAL_ASSETS,
626 CURRENT_LIABILITIES,
627 TOTAL_DEBT,
628 NET_WORTH,
629 REPORT_BASE_DATE,
630 DNB_LAST_UPDATE_DATE,
631 HIGH_CREDIT,
632 AVERAGE_HIGH_CREDIT,
633 BUSINESS_MOVED_IND,
634 CRIMINAL_PROCEEDINGS_IND,
635 FIRE_DISASTER_IND,
636 OWNS_RENTS_IND,
637 HISTORY,
638 NEGATIVE_PAYMENTS,
639 PAYDEX_NORM,
640 PAYDEX_PRIOR_Q1,
641 PAYDEX_PRIOR_Q2,
642 PAYDEX_PRIOR_Q3,
643 SLOW_PAYMENTS,
644 TOTAL_PAYMENTS,
645 NET_PROFIT,
646 PREV_CURRENT_ASSETS,
647 PREV_CURRENT_LIABILITIES,
648 PREV_NET_WORTH,
649 PREV_SALES,
650 PREV_STATEMENT_DATE,
651 PREV_STATEMENT_TYPE,
652 PREV_TOTAL_ASSETS,
653 TRADE_STYLE,
654 DOMESTIC_ULT_FK,
655 HQ_FK,
656 PARENT_FK,
657 INSTANCE,
658 LAST_UPDATE_DATE,
659 COLLECTION_STATUS,
660 UPDATE_FACT_FLAG)
661 select
662 distinct dnb.DUNS,
663 COMPANY_NAME || '-' || dnb.DUNS,
664 COMPANY_NAME || '-' || dnb.DUNS,
665 dnb.DUNS,
666 COMPANY_NAME,
667 ADDRESS,
668 CITY,
669 STATE,
670 ZIP,
671 TELEPHONE,
672 COUNTRY,
673 CEO_NAME,
674 CEO_TITLE,
675 LEGAL_STATUS_VALUE,
676 LOCATION_STATUS_VALUE,
677 HQ_IND,
678 EMPLOYEES_TOTAL,
679 CONTROL_YEAR,
680 SIC_CODE_1,
681 SIC_CODE_2,
682 SIC_1_DESCRIPTION,
683 OOB_VALUE,
684 CONG_DIST_CODE_1,
685 CONG_DIST_CODE_2,
686 CONG_DIST_CODE_3,
687 IMPORT_IND,
688 EXPORT_IND,
689 FAILURE_VALUE,
690 BANKRUPTCY_IND,
691 HIGH_RISK_VALUE,
692 SUITS_COUNT,
693 LIENS_COUNT,
694 JUDGMENTS_COUNT,
695 HQ_DUNS,
696 HQ_NAME,
697 HQ_ADDRESS,
698 HQ_CITY,
699 HQ_STATE,
700 HQ_POSTAL_CODE,
701 HQ_COUNTRY,
702 HQ_TELEPHONE,
703 PARENT_DUNS,
704 PARENT_NAME,
705 PARENT_ADDRESS,
706 PARENT_CITY,
707 PARENT_STATE,
708 PARENT_POSTAL_CODE,
709 PARENT_COUNTRY,
710 PARENT_TELEPHONE,
711 GLOBAL_ULT_DUNS,
712 GLOBAL_ULT_NAME,
713 GLOBAL_ULT_ADDRESS,
714 GLOBAL_ULT_CITY,
715 GLOBAL_ULT_STATE,
716 GLOBAL_ULT_POSTAL_CODE,
717 GLOBAL_ULT_COUNTRY,
718 GLOBAL_ULT_TELEPHONE,
719 DOMESTIC_ULT_DUNS,
720 DOMESTIC_ULT_NAME,
721 DOMESTIC_ULT_ADDRESS,
722 DOMESTIC_ULT_CITY,
723 DOMESTIC_ULT_STATE,
724 DOMESTIC_ULT_POSTAL_CODE,
725 DOMESTIC_ULT_COUNTRY,
726 DOMESTIC_ULT_TELEPHONE,
727 DNB_RATING,
728 CREDIT_SCORE,
729 FSS_SER_SCORE,
730 FSS_NATIONAL_RANK,
731 FSS_INDSTRY_RANK,
732 FSS_NATL_INC_OF_DEFAULT,
733 FSS_IND_INC_OF_DEFAULT,
734 PAYDEX_CURRENT,
735 LABOR_SURPLUS_IND,
736 DEBARMENT_IND,
737 MINORITY_OWNED_VALUE,
738 dnb.Minority_Owned_Type,
739 WOMAN_OWNED_IND,
740 DISADVANTAGED_IND,
741 SMALL_BUSINESS_IND,
742 decode(ltrim(rtrim(SDB_ENTRANCE_DATE)),
743 '', NULL,
744 to_date(SDB_ENTRANCE_DATE, 'YYYYMMDD')),
745 decode(ltrim(rtrim(SDB_EXIT_DATE)),
746 '', NULL,
747 to_date(SDB_EXIT_DATE, 'YYYYMMDD')),
748 ISO9000_REGISTRATION,
749 FEDERAL_TAX_ID,
750 decode(ltrim(rtrim(CURRENT_STATEMENT_DATE)),
751 '', NULL,
752 to_date(CURRENT_STATEMENT_DATE, 'YYMMDD')),
753 CURRENT_STATEMENT_VALUE,
754 SALES,
755 CASH,
756 ACCOUNTS_REC,
757 INVENTORY,
758 CURRENT_ASSETS,
759 TOTAL_ASSETS,
760 CURRENT_LIABILITIES,
761 TOTAL_DEBT,
762 NET_WORTH,
763 decode(ltrim(rtrim(REPORT_BASE_DATE)),
764 '', NULL,
765 to_date(REPORT_BASE_DATE, 'YYMMDD')),
766 decode(ltrim(rtrim(DNB_LAST_UPDATE_DATE)),
767 '', NULL,
768 to_date(DNB_LAST_UPDATE_DATE, 'YYMMDD')),
769 HIGH_CREDIT,
770 AVERAGE_HIGH_CREDIT,
771 BUSINESS_MOVED_IND,
772 CRIMINAL_PROCEEDINGS_VALUE,
773 FIRE_DISASTER_VALUE,
774 OWNS_RENTS_VALUE,
775 HISTORY_VALUE,
776 NEGATIVE_PAYMENTS,
777 PAYDEX_NORM,
778 PAYDEX_PRIOR_Q1,
779 PAYDEX_PRIOR_Q2,
780 PAYDEX_PRIOR_Q3,
781 SLOW_PAYMENTS,
782 TOTAL_PAYMENTS,
783 NET_PROFIT,
784 PREVIOUS_CURRENT_ASSETS,
785 PREV_CURRENT_LIABILITIES,
786 PREV_NET_WORTH,
787 PREV_SALES,
788 decode(ltrim(rtrim(PREV_STATEMENT_DATE)),
789 '', NULL,
790 to_date(PREV_STATEMENT_DATE, 'YYMMDD')),
791 dnb.Prev_Statement_Type,
792 PREV_TOTAL_ASSETS,
793 TRADE_STYLE,
794 NVL(ltrim(rtrim(DOMESTIC_ULT_DUNS)), GLOBAL_ULT_DUNS),
795 NVL(ltrim(rtrim(HQ_DUNS)), GLOBAL_ULT_DUNS),
796 NVL(ltrim(rtrim(PARENT_DUNS)), GLOBAL_ULT_DUNS),
797 NULL,
798 sysdate,
799 'READY',
800 decode(greatest(poa.dnb_update_date,g_start_time), poa.dnb_update_date, decode(greatest(poa.dnb_update_date, sysdate), sysdate, 'Y', 'N'), 'N')
801 from POA_TPRT_INTERFACE dnb,
802 POA_DNB_TRD_PRTNR poa
803 where (dnb.TRADING_PARTNER_PK = poa.TRADING_PARTNER_PK);
804
805 l_rows_inserted := sql%rowcount;
806
807 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
808
809 edw_log.put_line('Commiting records for EDW_DUNS_NUMBER_LSTG');
810 commit;
811
812 edw_log.put_line('Completed Push_EDW_DUNS_NUMBER_LSTG');
813 Exception When others then
814 raise;
815 commit;
816 END Push_EDW_DUNS_NUMBER_LSTG;
817
818
819
820
821
822 Procedure Push_EDW_DUNS_PARENT_LSTG(p_from_date IN date,
823 p_to_date IN DATE) IS
824 l_date1 DATE;
825 l_date2 DATE;
826 l_rows_inserted NUMBER :=0;
827 BEGIN
828 edw_log.put_line('Starting Push_DUNS_PARENT_LSTG');
829 l_date1 := p_from_date;
830 l_date2 := p_to_date;
831 Insert Into
832 EDW_DUNS_PARENT_LSTG(
833 PARENT_PK,
834 PARENT_DP,
835 NAME,
836 DUNS_NUMBER,
837 ADDRESS,
838 CITY,
839 STATE_PROV,
840 POSTAL_CODE,
841 COUNTRY,
842 TELEPHONE,
843 GLOBAL_ULT_FK,
844 INSTANCE,
845 LAST_UPDATE_DATE,
846 COLLECTION_STATUS)
847 select
848 distinct PARENT_DUNS,
849 PARENT_NAME || '-' || PARENT_DUNS,
850 PARENT_NAME || '-' || PARENT_DUNS,
851 PARENT_DUNS,
852 PARENT_ADDRESS,
853 PARENT_CITY,
854 PARENT_STATE,
855 PARENT_POSTAL_CODE,
856 PARENT_COUNTRY,
857 PARENT_TELEPHONE,
858 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
859 NULL,
860 sysdate,
861 'READY'
862 from POA_TPRT_INTERFACE dnb
863 where EXISTS (select 'X'
864 from POA_TPRT_INTERFACE pti
865 where ltrim(rtrim(PARENT_DUNS)) IS NOT NULL
866 and dnb.PARENT_DUNS = pti.PARENT_DUNS);
867
868 l_rows_inserted := sql%rowcount;
869
870 -- Push up the DUNS No. if its doesnt have any Parent
871 Insert Into
872 EDW_DUNS_PARENT_LSTG(
873 PARENT_PK,
874 PARENT_DP,
875 NAME,
876 DUNS_NUMBER,
877 ADDRESS,
878 CITY,
879 STATE_PROV,
880 POSTAL_CODE,
881 COUNTRY,
882 TELEPHONE,
883 GLOBAL_ULT_FK,
884 INSTANCE,
885 LAST_UPDATE_DATE,
886 COLLECTION_STATUS)
887 select
888 distinct dnb.DUNS,
889 COMPANY_NAME || '-' || dnb.DUNS,
890 COMPANY_NAME || '-' || dnb.DUNS,
891 dnb.DUNS,
892 ADDRESS,
893 CITY,
894 STATE,
895 ZIP,
896 COUNTRY,
897 TELEPHONE,
898 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
899 NULL,
900 sysdate,
901 'READY'
902 from POA_TPRT_INTERFACE dnb
903 where (ltrim(rtrim(PARENT_DUNS)) IS NULL);
904
905 l_rows_inserted := l_rows_inserted + sql%rowcount;
906 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
907 edw_log.put_line('Commiting records for EDW_DUNS_PARENT_LSTG');
908 commit;
909
910 edw_log.put_line('Completed Push_EDW_DUNS_PARENT_LSTG');
911 Exception When others then
912 raise;
913 commit;
914 END Push_EDW_DUNS_PARENT_LSTG;
915
916
917
918 Procedure Push_EDW_DUNS_DOMESTIC_LSTG(p_from_date IN date,
919 p_to_date IN DATE) IS
920 l_date1 DATE;
921 l_date2 DATE;
922 l_rows_inserted NUMBER :=0;
923 BEGIN
924 edw_log.put_line('Starting Push_DUNS_DOMESTIC_LSTG');
925 l_date1 := p_from_date;
926 l_date2 := p_to_date;
927 Insert Into
928 EDW_DUNS_DOMESTIC_LSTG(
929 DOMESTIC_ULT_PK,
930 DOMESTIC_ULT_DP,
931 NAME,
932 DUNS_NUMBER,
933 ADDRESS,
934 CITY,
935 STATE_PROV,
936 POSTAL_CODE,
937 COUNTRY,
938 TELEPHONE,
939 GLOBAL_ULT_FK,
940 INSTANCE,
941 LAST_UPDATE_DATE ,
942 COLLECTION_STATUS)
943 select
944 distinct DOMESTIC_ULT_DUNS,
945 DOMESTIC_ULT_NAME || '-' || DOMESTIC_ULT_DUNS,
946 DOMESTIC_ULT_NAME || '-' || DOMESTIC_ULT_DUNS,
947 DOMESTIC_ULT_DUNS,
948 DOMESTIC_ULT_ADDRESS,
949 DOMESTIC_ULT_CITY,
950 DOMESTIC_ULT_STATE,
951 DOMESTIC_ULT_POSTAL_CODE,
952 DOMESTIC_ULT_COUNTRY,
953 DOMESTIC_ULT_TELEPHONE,
954 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
955 NULL,
956 sysdate,
957 'READY'
958 from POA_TPRT_INTERFACE dnb
959 where EXISTS (select 'X'
960 from POA_TPRT_INTERFACE pti
961 where ltrim(rtrim(DOMESTIC_ULT_DUNS)) IS NOT NULL
962 and dnb.DOMESTIC_ULT_DUNS = pti.DOMESTIC_ULT_DUNS);
963
964 l_rows_inserted := sql%rowcount;
965
966 Insert Into
967 EDW_DUNS_DOMESTIC_LSTG(
968 DOMESTIC_ULT_PK,
969 DOMESTIC_ULT_DP,
970 NAME,
971 DUNS_NUMBER,
972 ADDRESS,
973 CITY,
974 STATE_PROV,
975 POSTAL_CODE,
976 COUNTRY,
977 TELEPHONE,
978 GLOBAL_ULT_FK,
979 INSTANCE,
980 LAST_UPDATE_DATE ,
981 COLLECTION_STATUS)
982 select
983 distinct dnb.DUNS,
984 COMPANY_NAME || '-' || dnb.DUNS,
985 COMPANY_NAME || '-' || dnb.DUNS,
986 dnb.DUNS,
987 ADDRESS,
988 CITY,
989 STATE,
990 ZIP,
991 COUNTRY,
992 TELEPHONE,
993 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
994 NULL,
995 sysdate,
996 'READY'
997 from POA_TPRT_INTERFACE dnb
998 where (ltrim(rtrim(DOMESTIC_ULT_DUNS)) IS NULL);
999
1000 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1001 edw_log.put_line('Commiting records for EDW_DUNS_DOMESTIC_LSTG');
1002 commit;
1003
1004 edw_log.put_line('Completed Push_EDW_DUNS_DOMESTIC_LSTG');
1005 Exception When others then
1006 raise;
1007 commit;
1008 END Push_EDW_DUNS_DOMESTIC_LSTG;
1009
1010
1011
1012 Procedure Push_EDW_DUNS_GLOBAL_LSTG(p_from_date IN date,
1013 p_to_date IN DATE) IS
1014 l_date1 DATE;
1015 l_date2 DATE;
1016 l_rows_inserted NUMBER :=0;
1017 BEGIN
1018 edw_log.put_line('Starting Push_DUNS_GLOBAL_LSTG');
1019 l_date1 := p_from_date;
1020 l_date2 := p_to_date;
1021 Insert Into
1022 EDW_DUNS_GLOBAL_LSTG(
1023 GLOBAL_ULT_PK,
1024 GLOBAL_ULT_DP,
1025 NAME,
1026 DUNS_NUMBER,
1027 ADDRESS,
1028 CITY,
1029 STATE_PROV,
1030 POSTAL_CODE,
1031 COUNTRY,
1032 TELEPHONE,
1033 ALL_FK,
1034 INSTANCE,
1035 LAST_UPDATE_DATE,
1036 COLLECTION_STATUS)
1037 select
1038 distinct GLOBAL_ULT_DUNS,
1039 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1040 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1041 GLOBAL_ULT_DUNS,
1042 GLOBAL_ULT_ADDRESS,
1043 GLOBAL_ULT_CITY,
1044 GLOBAL_ULT_STATE,
1045 GLOBAL_ULT_POSTAL_CODE,
1046 GLOBAL_ULT_COUNTRY,
1047 GLOBAL_ULT_TELEPHONE,
1048 'ALL',
1049 NULL,
1050 sysdate,
1051 'READY'
1052 from POA_TPRT_INTERFACE dnb;
1053
1054 l_rows_inserted := sql%rowcount;
1055 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1056 edw_log.put_line('Commiting records for EDW_DUNS_GLOBAL_LSTG');
1057 commit;
1058
1059 -- Push Global Ultimate to all lower level if needed
1060 Insert into EDW_DUNS_DOMESTIC_LSTG(
1061 DOMESTIC_ULT_PK,
1062 DOMESTIC_ULT_DP,
1063 NAME,
1064 DUNS_NUMBER,
1065 ADDRESS,
1066 CITY,
1067 STATE_PROV,
1068 POSTAL_CODE,
1069 COUNTRY,
1070 TELEPHONE,
1071 GLOBAL_ULT_FK,
1072 INSTANCE,
1073 LAST_UPDATE_DATE ,
1074 COLLECTION_STATUS)
1075 select
1076 distinct GLOBAL_ULT_DUNS,
1077 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1078 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1079 GLOBAL_ULT_DUNS,
1080 GLOBAL_ULT_ADDRESS,
1081 GLOBAL_ULT_CITY,
1082 GLOBAL_ULT_STATE,
1083 GLOBAL_ULT_POSTAL_CODE,
1084 GLOBAL_ULT_COUNTRY,
1085 GLOBAL_ULT_TELEPHONE,
1086 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1087 NULL,
1088 sysdate,
1089 'READY'
1090 from POA_TPRT_INTERFACE dnb
1091 where (NOT EXISTS (select 'X'
1092 from POA_TPRT_INTERFACE pti
1093 where ltrim(rtrim(DOMESTIC_ULT_DUNS)) IS NOT NULL
1094 and dnb.GLOBAL_ULT_DUNS = pti.DOMESTIC_ULT_DUNS
1095 UNION ALL
1096 select 'X'
1097 from POA_TPRT_INTERFACE pti
1098 where ltrim(rtrim(DUNS)) IS NOT NULL
1099 and dnb.GLOBAL_ULT_DUNS = pti.DUNS));
1100
1101 l_rows_inserted := sql%rowcount;
1102 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1103 edw_log.put_line('Commiting records for EDW_DUNS_DOMESTIC_LSTG');
1104 commit;
1105
1106 Insert into EDW_DUNS_HEADQTR_LSTG(
1107 HQ_PK,
1108 HQ_DP,
1109 NAME,
1110 DUNS_NUMBER,
1111 ADDRESS,
1112 CITY,
1113 STATE_PROV,
1114 POSTAL_CODE,
1115 COUNTRY,
1116 TELEPHONE,
1117 GLOBAL_ULT_FK,
1118 INSTANCE,
1119 LAST_UPDATE_DATE,
1120 COLLECTION_STATUS)
1121 select
1122 distinct GLOBAL_ULT_DUNS,
1123 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1124 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1125 GLOBAL_ULT_DUNS,
1126 GLOBAL_ULT_ADDRESS,
1127 GLOBAL_ULT_CITY,
1128 GLOBAL_ULT_STATE,
1129 GLOBAL_ULT_POSTAL_CODE,
1130 GLOBAL_ULT_COUNTRY,
1131 GLOBAL_ULT_TELEPHONE,
1132 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1133 NULL,
1134 sysdate,
1135 'READY'
1136 from POA_TPRT_INTERFACE dnb
1137 where (NOT EXISTS (select 'X'
1138 from POA_TPRT_INTERFACE pti
1139 where (ltrim(rtrim(HQ_DUNS)) IS NOT NULL)
1140 and dnb.GLOBAL_ULT_DUNS = pti.HQ_DUNS
1141 UNION ALL
1142 select 'X'
1143 from POA_TPRT_INTERFACE pti
1144 where (ltrim(rtrim(DUNS)) IS NOT NULL)
1145 and dnb.GLOBAL_ULT_DUNS = pti.DUNS));
1146
1147 l_rows_inserted := sql%rowcount;
1148 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1149 edw_log.put_line('Commiting records for EDW_DUNS_HEADQTR_LSTG');
1150 commit;
1151
1152 Insert into EDW_DUNS_PARENT_LSTG(
1153 PARENT_PK,
1154 PARENT_DP,
1155 NAME,
1156 DUNS_NUMBER,
1157 ADDRESS,
1158 CITY,
1159 STATE_PROV,
1160 POSTAL_CODE,
1161 COUNTRY,
1162 TELEPHONE,
1163 GLOBAL_ULT_FK,
1164 INSTANCE,
1165 LAST_UPDATE_DATE,
1166 COLLECTION_STATUS)
1167 select
1168 distinct GLOBAL_ULT_DUNS,
1169 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1170 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1171 GLOBAL_ULT_DUNS,
1172 GLOBAL_ULT_ADDRESS,
1173 GLOBAL_ULT_CITY,
1174 GLOBAL_ULT_STATE,
1175 GLOBAL_ULT_POSTAL_CODE,
1176 GLOBAL_ULT_COUNTRY,
1177 GLOBAL_ULT_TELEPHONE,
1178 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1179 NULL,
1180 sysdate,
1181 'READY'
1182 from POA_TPRT_INTERFACE dnb
1183 where (NOT EXISTS (select 'X'
1184 from POA_TPRT_INTERFACE pti
1185 where (ltrim(rtrim(PARENT_DUNS)) IS NOT NULL)
1186 and dnb.GLOBAL_ULT_DUNS = pti.PARENT_DUNS
1187 UNION ALL
1188 select 'X'
1189 from POA_TPRT_INTERFACE pti
1190 where (ltrim(rtrim(DUNS)) IS NOT NULL)
1191 and dnb.GLOBAL_ULT_DUNS = pti.DUNS));
1192
1193 l_rows_inserted := sql%rowcount;
1194 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1195 edw_log.put_line('Commiting records for EDW_DUNS_PARENT_LSTG');
1196 commit;
1197
1198 Insert into EDW_DUNS_NUMBER_LSTG(
1199 DUNS_NUM_PK,
1200 DUNS_NUM_DP,
1201 NAME,
1202 DUNS_NUMBER,
1203 COMPANY_NAME,
1204 ADDRESS,
1205 CITY,
1206 STATE_PROV,
1207 ZIP_CODE,
1208 TELEPHONE,
1209 COUNTRY,
1210 HQ_DUNS,
1211 HQ_NAME,
1212 HQ_ADDRESS,
1213 HQ_CITY,
1214 HQ_STATE_PROV,
1215 HQ_POSTAL_CODE,
1216 HQ_COUNTRY,
1217 HQ_TELEPHONE,
1218 PARENT_DUNS,
1219 PARENT_NAME,
1220 PARENT_ADDRESS,
1221 PARENT_CITY,
1222 PARENT_STATE_PROV,
1223 PARENT_POSTAL_CODE,
1224 PARENT_COUNTRY,
1225 PARENT_TELEPHONE,
1226 GLOBAL_ULT_DUNS,
1227 GLOBAL_ULT_NAME,
1228 GLOBAL_ULT_ADDRESS,
1229 GLOBAL_ULT_CITY,
1230 GLOBAL_ULT_STATE_PROV,
1231 GLOBAL_ULT_POSTAL_CODE,
1232 GLOBAL_ULT_COUNTRY,
1233 GLOBAL_ULT_TELEPHONE,
1234 DOMESTIC_ULT_DUNS,
1235 DOMESTIC_ULT_NAME,
1236 DOMESTIC_ULT_ADDRESS,
1237 DOMESTIC_ULT_CITY,
1238 DOMESTIC_ULT_STATE_PROV,
1239 DOMESTIC_ULT_POSTAL_CODE,
1240 DOMESTIC_ULT_COUNTRY,
1241 DOMESTIC_ULT_TELEPHONE,
1242 DOMESTIC_ULT_FK,
1243 HQ_FK,
1244 PARENT_FK,
1245 INSTANCE,
1246 LAST_UPDATE_DATE,
1247 COLLECTION_STATUS,
1248 UPDATE_FACT_FLAG)
1249 select
1250 distinct GLOBAL_ULT_DUNS,
1251 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1252 GLOBAL_ULT_NAME || '-' || GLOBAL_ULT_DUNS,
1253 GLOBAL_ULT_DUNS,
1254 GLOBAL_ULT_NAME,
1255 GLOBAL_ULT_ADDRESS,
1256 GLOBAL_ULT_CITY,
1257 GLOBAL_ULT_STATE,
1258 GLOBAL_ULT_POSTAL_CODE,
1259 GLOBAL_ULT_TELEPHONE,
1260 GLOBAL_ULT_COUNTRY,
1261 GLOBAL_ULT_DUNS,
1262 GLOBAL_ULT_NAME,
1263 GLOBAL_ULT_ADDRESS,
1264 GLOBAL_ULT_CITY,
1265 GLOBAL_ULT_STATE,
1266 GLOBAL_ULT_POSTAL_CODE,
1267 GLOBAL_ULT_COUNTRY,
1268 GLOBAL_ULT_TELEPHONE,
1269 GLOBAL_ULT_DUNS,
1270 GLOBAL_ULT_NAME,
1271 GLOBAL_ULT_ADDRESS,
1272 GLOBAL_ULT_CITY,
1273 GLOBAL_ULT_STATE,
1274 GLOBAL_ULT_POSTAL_CODE,
1275 GLOBAL_ULT_COUNTRY,
1276 GLOBAL_ULT_TELEPHONE,
1277 GLOBAL_ULT_DUNS,
1278 GLOBAL_ULT_NAME,
1279 GLOBAL_ULT_ADDRESS,
1280 GLOBAL_ULT_CITY,
1281 GLOBAL_ULT_STATE,
1282 GLOBAL_ULT_POSTAL_CODE,
1283 GLOBAL_ULT_COUNTRY,
1284 GLOBAL_ULT_TELEPHONE,
1285 GLOBAL_ULT_DUNS,
1286 GLOBAL_ULT_NAME,
1287 GLOBAL_ULT_ADDRESS,
1288 GLOBAL_ULT_CITY,
1289 GLOBAL_ULT_STATE,
1290 GLOBAL_ULT_POSTAL_CODE,
1291 GLOBAL_ULT_COUNTRY,
1292 GLOBAL_ULT_TELEPHONE,
1293 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1294 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1295 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1296 NULL,
1297 sysdate,
1298 'READY',
1299 'N'
1300 from POA_TPRT_INTERFACE dnb,
1301 POA_DNB_TRD_PRTNR poa
1302 where ((dnb.TRADING_PARTNER_PK = poa.TRADING_PARTNER_PK) and
1303 (NOT EXISTS (select 'X'
1304 from POA_TPRT_INTERFACE pti
1305 where (ltrim(rtrim(DUNS)) IS NOT NULL)
1306 and dnb.GLOBAL_ULT_DUNS = pti.DUNS)));
1307
1308 l_rows_inserted := sql%rowcount;
1309 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1310 edw_log.put_line('Commiting records for EDW_DUNS_NUMBER_LSTG');
1311 commit;
1312
1313 edw_log.put_line('Completed Push_EDW_DUNS_GLOBAL_LSTG');
1314 Exception When others then
1315 raise;
1316 commit;
1317 END Push_EDW_DUNS_GLOBAL_LSTG;
1318
1319
1320
1321 Procedure Push_EDW_DUNS_HEADQTR_LSTG(p_from_date IN date,
1322 p_to_date IN DATE) IS
1323 l_date1 DATE;
1324 l_date2 DATE;
1325 l_rows_inserted NUMBER :=0;
1326 BEGIN
1327 edw_log.put_line('Starting Push_EDW_HEADQTR_LSTG');
1328 l_date1 := p_from_date;
1329 l_date2 := p_to_date;
1330 Insert Into
1331 EDW_DUNS_HEADQTR_LSTG(
1332 HQ_PK,
1333 HQ_DP,
1334 NAME,
1335 DUNS_NUMBER,
1336 ADDRESS,
1337 CITY,
1338 STATE_PROV,
1339 POSTAL_CODE,
1340 COUNTRY,
1341 TELEPHONE,
1342 GLOBAL_ULT_FK,
1343 INSTANCE,
1344 LAST_UPDATE_DATE,
1345 COLLECTION_STATUS)
1346 select
1347 distinct HQ_DUNS,
1348 HQ_NAME || '-' || HQ_DUNS,
1349 HQ_NAME || '-' || HQ_DUNS,
1350 HQ_DUNS,
1351 HQ_ADDRESS,
1352 HQ_CITY,
1353 HQ_STATE,
1354 HQ_POSTAL_CODE,
1355 HQ_COUNTRY,
1356 HQ_TELEPHONE,
1357 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1358 NULL,
1359 sysdate,
1360 'READY'
1361 from POA_TPRT_INTERFACE dnb
1362 where EXISTS (select 'X'
1363 from POA_TPRT_INTERFACE pti
1364 where (ltrim(rtrim(HQ_DUNS)) IS NOT NULL)
1365 and dnb.HQ_DUNS = pti.HQ_DUNS);
1366
1367 l_rows_inserted := sql%rowcount;
1368
1369 Insert Into
1370 EDW_DUNS_HEADQTR_LSTG(
1371 HQ_PK,
1372 HQ_DP,
1373 NAME,
1374 DUNS_NUMBER,
1375 ADDRESS,
1376 CITY,
1377 STATE_PROV,
1378 POSTAL_CODE,
1379 COUNTRY,
1380 TELEPHONE,
1381 GLOBAL_ULT_FK,
1382 INSTANCE,
1383 LAST_UPDATE_DATE,
1384 COLLECTION_STATUS)
1385 select
1386 distinct dnb.DUNS,
1387 COMPANY_NAME || '-' || dnb.DUNS,
1388 COMPANY_NAME || '-' || dnb.DUNS,
1389 dnb.DUNS,
1390 ADDRESS,
1391 CITY,
1392 STATE,
1393 ZIP,
1394 COUNTRY,
1395 TELEPHONE,
1396 NVL(ltrim(rtrim(GLOBAL_ULT_DUNS)), 'NA_EDW'),
1397 NULL,
1398 sysdate,
1399 'READY'
1400 from POA_TPRT_INTERFACE dnb
1401 where (ltrim(rtrim(HQ_DUNS)) IS NULL);
1402
1403 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1404 edw_log.put_line('Commiting records for EDW_DUNS_HEADQTR_LSTG');
1405 commit;
1406
1407 edw_log.put_line('Completed Push_EDW_DUNS_HEADQTR_LSTG');
1408 Exception When others then
1409 raise;
1410 commit;
1411 END Push_EDW_DUNS_HEADQTR_LSTG;
1412
1413
1414 Procedure Push_EDW_SICM_SIC_LSTG(p_from_date IN date,
1415 p_to_date IN DATE) IS
1416 l_date1 DATE;
1417 l_date2 DATE;
1418 l_rows_inserted NUMBER :=0;
1419 BEGIN
1420 edw_log.put_line('Starting Push_SICM_SIC_LSTG');
1421 l_date1 := p_from_date;
1422 l_date2 := p_to_date;
1423 Insert Into
1424 EDW_SICM_SIC_LSTG(
1425 SIC_CODE_PK,
1426 SIC_CODE_DP,
1427 SIC_CODE,
1428 DESCRIPTION,
1429 ALL_FK,
1430 COLLECTION_STATUS,
1431 UPDATE_FACT_FLAG)
1432 select
1433 distinct SIC_CODE,
1434 SIC_CODE,
1435 SIC_CODE,
1436 SIC_DESCRIPTION,
1437 'ALL',
1438 'READY',
1439 'Y'
1440 from POA_DNB_SIC_CODE poa;
1441
1442 l_rows_inserted := sql%rowcount;
1443 EDW_DUNS_M_C.g_row_count := EDW_DUNS_M_C.g_row_count + l_rows_inserted ;
1444 edw_log.put_line('Commiting records for EDW_SICM_SIC_LSTG');
1445 Exception When others then
1446 raise;
1447 commit;
1448 END Push_EDW_SICM_SIC_LSTG;
1449
1450
1451 END EDW_DUNS_M_C;
1452