[Home] [Help]
PACKAGE BODY: APPS.EDW_TRD_PARTNER_M_C
Source
1 Package Body EDW_TRD_PARTNER_M_C AS
2 /* $Header: poapptpb.pls 120.1 2005/06/13 13:16:57 sriswami noship $ */
3
4 g_row_count Number:=0;
5 g_row_count_m Number:=0;
6 g_exception_message varchar2(10000):=NULL;
7
8
9 -- ---------------------------------
10 -- PRIVATE PROCEDURES
11 -- ---------------------------------
12
13 Procedure populate_hierarchies (p_from_date IN DATE,
14 p_to_date IN DATE) IS
15
16 /**********************************************************************
17 * *
18 * This procedure will populate the EDW_PO_VENDOR_HIERARCHIES table *
19 * *
20 * Author: tom.olick Date: April 3, 2002 *
21 * *
22 **********************************************************************/
23
24 l_push_date_range1 DATE := p_from_date;
25 l_push_date_range2 DATE := p_to_date;
26 l_rows_inserted NUMBER := 0;
27 l_duration NUMBER := 0;
28 l_temp_date DATE;
29 Errbuf VARCHAR2(1000) := NULL;
30 Retcode NUMBER := 0;
31 g_stmt VARCHAR2(200);
32 g_schema VARCHAR2(30);
33 g_status VARCHAR2(30);
34 g_industry VARCHAR2(30);
35
36 Begin
37
38 IF (NOT FND_INSTALLATION.GET_APP_INFO('PO', g_status, g_industry, g_schema)) THEN
39 RAISE_APPLICATION_ERROR (-20001, '***There is not POA schema set up***');
40 END IF;
41
42 edw_log.put_line(' ');
43 edw_log.put_line('Truncating Vendor Hierarchies table...');
44
45 g_stmt := 'TRUNCATE TABLE ' || g_schema || '.EDW_PO_VENDOR_HIERARCHIES';
46 EXECUTE IMMEDIATE g_stmt;
47
48 edw_log.put_line(' ');
49 edw_log.put_line('Populating Vendor Hierachies table...');
50
51 l_temp_date := sysdate;
52
53 Insert Into EDW_PO_VENDOR_HIERARCHIES (
54 hierarchy_level,
55 last_update_date,
56 vendor_id,
57 parent_vendor_id)
58 select
59 level,
60 last_update_date,
61 vendor_id,
62 parent_vendor_id
63 from po_vendors pov
64 start with
65 EXISTS (select 1
66 from po_vendors pv_np
67 where pv_np.parent_vendor_id is NULL
68 and pv_np.vendor_id = pov.parent_vendor_id)
69 connect by parent_vendor_id = PRIOR vendor_id;
70
71 l_rows_inserted := sql%rowcount;
72 l_duration := sysdate - l_temp_date;
73
74 edw_log.put_line('Inserted ' || to_char(nvl(l_rows_inserted, 0))||
75 ' rows into the HIERARCHY table: EDW_PO_VENDOR_HIERARCHIES');
76 edw_log.put_line('Process Time: ' || edw_log.duration(l_duration));
77 edw_log.put_line(' ');
78
79 Exception When others then
80 Errbuf := sqlerrm;
81 Retcode := sqlcode;
82
83 EDW_TRD_PARTNER_M_C.g_exception_message := Retcode || ':' || Errbuf;
84
85 rollback;
86 raise;
87
88 End populate_hierarchies;
89
90
91
92 Procedure update_hierarchy5 (p_from_date IN DATE,
93 p_to_date IN DATE) IS
94
95 /**********************************************************************
96 * *
97 * This procedure updates the PL/SQL table t_hierarchyTable. *
98 * It will move all vendors with 5 or more levels parents to the *
99 * 4th level, their new parents will be the parents of their (grand) *
100 * parents who have 4 levels parents originally. *
101 * *
102 * Author: phu Date: Sep 23, 2000 *
103 **********************************************************************/
104
105 updated_flag BOOLEAN;
106 level NUMBER;
107 id NUMBER;
108 pid NUMBER;
109 v_pid NUMBER;
110 v_index NUMBER;
111 v_change BOOLEAN;
112 v_instance VARCHAR2(30);
113
114 TYPE t_hierarchyRecord IS RECORD (
115 to_change BOOLEAN,
116 hierarchy_level NUMBER,
117 vendor_id NUMBER,
118 parent_vendor_id NUMBER);
119
120 TYPE t_hierarchyTable IS TABLE of t_hierarchyRecord
121 INDEX BY BINARY_INTEGER;
122
123 v_hierarchyTable t_hierarchyTable;
124
125 CURSOR h4_cur IS
126 SELECT hierarchy_level, vendor_id, parent_vendor_id
127 FROM EDW_PO_VENDOR_HIERARCHIES_V
128 WHERE hierarchy_level >= 4
129 AND last_update_date between p_from_date and p_to_date
130 ORDER BY hierarchy_level;
131
132 BEGIN
133
134 updated_flag := FALSE;
135
136 /* Fill in the PL/SQL table v_hierarchyTable */
137 FOR h4_rec IN h4_cur LOOP
138 v_index := h4_rec.vendor_id;
139
140 if h4_rec.hierarchy_level > 4 then
141 v_hierarchyTable(v_index).to_change := TRUE;
142 else
143 v_hierarchyTable(v_index).to_change := FALSE;
144 end if;
145
146 v_hierarchyTable(v_index).hierarchy_level := h4_rec.hierarchy_level;
147 v_hierarchyTable(v_index).vendor_id := h4_rec.vendor_id ;
148 v_hierarchyTable(v_index).parent_vendor_id := h4_rec.parent_vendor_id;
149 END LOOP;
150
151
152 FOR h4_rec IN h4_cur LOOP
153
154 IF h4_rec.hierarchy_level >= 5 THEN
155 id := h4_rec.vendor_id;
156 pid := h4_rec.parent_vendor_id;
157
158 /* update this record in (PL/SQL) table v_hierarchyTable */
159
160 v_hierarchyTable(id).parent_vendor_id := v_hierarchyTable(pid).parent_vendor_id;
161 v_hierarchyTable(id).hierarchy_level := v_hierarchyTable(pid).hierarchy_level;
162
163 /* set flag to TRUE indicating that the hierarchy is changed */
164 updated_flag := TRUE;
165
166 END IF;
167
168 END LOOP;
169
170 /* Now, update the staging table */
171 IF updated_flag THEN
172
173 select instance_code into v_instance
174 from edw_local_instance;
175
176 v_index := v_hierarchyTable.FIRST;
177
178 LOOP
179 v_change := v_hierarchyTable(v_index).to_change;
180 id := v_hierarchyTable(v_index).vendor_id;
181 pid := v_hierarchyTable(v_index).parent_vendor_id;
182
183 IF v_change THEN
184
185 UPDATE EDW_TPRT_TRADE_PARTNER_LSTG
186 SET PARENT_TPARTNER_FK = pid ||'-'|| v_instance ||'-'|| 'SUPPLIER'
187 WHERE TRADE_PARTNER_PK = id ||'-'|| v_instance ||'-'|| 'SUPPLIER';
188
189 END IF;
190
191 EXIT WHEN v_index = v_hierarchyTable.LAST;
192
193 v_index := v_hierarchyTable.NEXT(v_index);
194 END LOOP;
195
196 END IF;
197
198 EXCEPTION
199 when others then
200 edw_log.put_line('***Exceptions in update_hierarchy5 : ' ||
201 sqlerrm || ' ***');
202 return;
203 END update_hierarchy5;
204
205 ---------------------------------------------------------------------------
206
207 -- ---------------------------------
208 -- PUBLIC PROCEDURES
209 -- ---------------------------------
210
211 Procedure Push_TPartner_Loc(Errbuf out NOCOPY Varchar2,
212 Retcode out NOCOPY Varchar2,
213 p_from_date Date := NULL,
214 p_to_date Date := NULL) IS
215 l_staging_table_name Varchar2(30) := 'EDW_TPRT_TPARTNER_LOC_LSTG';
216 l_push_date_range1 Date := NULL;
217 l_push_date_range2 Date := NULL;
218 l_temp_date Date := NULL;
219 l_rows_inserted Number := 0;
220 l_duration Number := 0;
221 l_exception_msg Varchar2(2000) := Null;
222 l_tmp_str1 VARCHAR2(120) := NULL;
223
224
225 -- -------------------------------------------
226 -- Put any additional developer variables here
227 -- -------------------------------------------
228
229 Begin
230
231 Errbuf :=NULL;
232 Retcode:=0;
233
234 l_push_date_range1 := p_from_date;
235 l_push_date_range2 := p_to_date;
236
237 -- -----------------------------------------------------------------------------
238 -- Start of Collection , Developer Customizable Section
239 -- -----------------------------------------------------------------------------
240 edw_log.put_line(' ');
241 edw_log.put_line('Pushing data for TP Location Staging Table...');
242
243 l_temp_date := sysdate;
244
245 l_tmp_str1 := EDW_COLLECTION_UTIL.get_lookup_value ('EDW_LEVEL_PUSH_DOWN',
246 'EDW_TRD_PARTNER_M_TPLO');
247
248 if (l_tmp_str1 IS NULL) THEN
249 edw_log.put_line('***Warning*** : No Look Code Found From GET_LEVEL_DP in Pushing TP Location');
250 end if;
251
252 Insert Into EDW_TPRT_TPARTNER_LOC_LSTG(
253 TPARTNER_LOC_PK,
254 TRADE_PARTNER_FK,
255 ADDRESS_LINE1,
256 ADDRESS_LINE2,
257 ADDRESS_LINE3,
258 ADDRESS_LINE4,
259 CITY,
260 COUNTY,
261 STATE,
262 POSTAL_CODE,
263 PROVINCE,
264 COUNTRY,
265 BUSINESS_TYPE,
266 TPARTNER_LOC_DP,
267 NAME,
268 DATE_FROM,
269 DATE_TO,
270 VNDR_PURCH_SITE,
271 VNDR_RFQ_ONLY,
272 VNDR_PAY_SITE,
273 VNDR_PAY_TERMS,
274 CUST_SITE_USE,
275 CUST_LOCATION,
276 CUST_PRIMARY_FLAG,
277 CUST_STATUS,
278 CUST_ORIG_SYS_REF,
279 CUST_SIC_CODE,
280 CUST_PAY_TERMS,
281 CUST_GSA_IND,
282 CUST_SHIP_PARTIAL,
283 CUST_SHIP_VIA,
284 CUST_FOB_POINT,
285 CUST_ORDER_TYPE,
286 CUST_PRICE_LIST,
287 CUST_FREIGHT,
288 CUST_TERRITORY,
289 CUST_TAX_REF,
290 CUST_SORT_PRTY,
291 CUST_TAX_CODE,
292 CUST_DEMAND_CLASS,
293 CUST_TAX_CLASSFN,
294 CUST_TAX_HDR_FLAG,
295 CUST_TAX_ROUND,
296 CUST_SALES_REP,
297 INSTANCE,
298 USER_ATTRIBUTE1,
299 USER_ATTRIBUTE2,
300 USER_ATTRIBUTE3,
301 USER_ATTRIBUTE4,
302 USER_ATTRIBUTE5,
303 OPERATION_CODE,
304 COLLECTION_STATUS,
305 LAST_UPDATE_DATE,
306 LEVEL_NAME)
307 select
308 TPARTNER_LOC_PK,
309 nvl(TRADE_PARTNER_FK, 'NA_EDW'),
310 ADDRESS_LINE1,
311 ADDRESS_LINE2,
312 ADDRESS_LINE3,
313 ADDRESS_LINE4,
314 CITY,
315 COUNTY,
316 STATE,
317 POSTAL_CODE,
318 PROVINCE,
319 COUNTRY,
320 BUSINESS_TYPE,
321 decode(UPPER(level_name),
322 'TRADE PARTNER', l_tmp_str1 || ' (' || TPARTNER_LOC_DP || ')',
323 TPARTNER_LOC_DP),
324 decode(UPPER(level_name),
325 'TRADE PARTNER', l_tmp_str1 || ' (' || NAME || ')',
326 NAME),
327 DATE_FROM,
328 DATE_TO,
329 VNDR_PURCH_SITE,
330 VNDR_RFQ_ONLY,
331 VNDR_PAY_SITE,
332 VNDR_PAY_TERMS,
333 CUST_SITE_USE,
334 CUST_LOCATION,
335 CUST_PRIMARY_FLAG,
336 CUST_STATUS,
337 CUST_ORIG_SYS_REF,
338 CUST_SIC_CODE,
339 CUST_PAY_TERMS,
340 CUST_GSA_IND,
341 CUST_SHIP_PARTIAL,
342 CUST_SHIP_VIA,
343 CUST_FOB_POINT,
344 CUST_ORDER_TYPE,
345 CUST_PRICE_LIST,
346 CUST_FREIGHT,
347 CUST_TERRITORY,
348 CUST_TAX_REF,
349 CUST_SORT_PRTY,
350 CUST_TAX_CODE,
351 CUST_DEMAND_CLASS,
352 CUST_TAX_CLASSFN,
353 CUST_TAX_HDR_FLAG,
354 CUST_TAX_ROUND,
355 CUST_SALES_REP,
356 INSTANCE,
357 USER_ATTRIBUTE1,
358 USER_ATTRIBUTE2,
359 USER_ATTRIBUTE3,
360 USER_ATTRIBUTE4,
361 USER_ATTRIBUTE5,
362 NULL,
363 'READY',
364 LAST_UPDATE_DATE,
365 LEVEL_NAME
366 from EDW_TPRT_TPARTNER_LOC_LCV
367 where last_update_date between l_push_date_range1 and l_push_date_range2;
368
369 l_rows_inserted := sql%rowcount;
370 l_duration := sysdate - l_temp_date;
371
372 edw_log.put_line('Inserted ' || to_char(nvl(l_rows_inserted, 0))||
373 ' rows into the staging table: ' || l_staging_table_name);
374 edw_log.put_line('Process Time: ' || edw_log.duration(l_duration));
375 edw_log.put_line(' ');
376
377 -- ---------------------------------------------------------------------------
378 -- END OF Collection , Developer Customizable Section
379 -- ---------------------------------------------------------------------------
380
381 EDW_TRD_PARTNER_M_C.g_row_count := EDW_TRD_PARTNER_M_C.g_row_count+l_rows_inserted;
382
383 EDW_TRD_PARTNER_M_C.g_row_count_m := l_rows_inserted;
384
385 Exception When others then
386 Errbuf := sqlerrm;
387 Retcode := sqlcode;
388
389 EDW_TRD_PARTNER_M_C.g_exception_message := Retcode || ':' || Errbuf;
390
391 rollback;
392 raise;
393
394 End Push_TPartner_Loc;
395
396
397
398
399 Procedure Push_Trade_Partner(Errbuf out NOCOPY Varchar2,
400 Retcode out NOCOPY Varchar2,
401 p_from_date Date := NULL,
402 p_to_date Date := NULL) IS
403 l_staging_table_name Varchar2(30) := 'EDW_TPRT_TRADE_PARTNER_LSTG';
404 l_push_date_range1 Date := NULL;
405 l_push_date_range2 Date := NULL;
406 l_temp_date Date := NULL;
407 l_rows_inserted Number := 0;
408 l_duration Number := 0;
409 l_exception_msg Varchar2(2000) := Null;
410
411 -- -------------------------------------------
412 -- Put any additional developer variables here
413 -- -------------------------------------------
414
415 Begin
416 Errbuf :=NULL;
417 Retcode:=0;
418
419
420 -- -----------------------------------------------------------------------------
421 -- Start of Collection , Developer Customizable Section
422 -- -----------------------------------------------------------------------------
423 edw_log.put_line(' ');
424 edw_log.put_line('Pushing data for Trading Partner Staging Table...');
425
426 l_push_date_range1 := p_from_date;
427 l_push_date_range2 := p_to_date;
428
429 l_temp_date := sysdate;
430 Insert Into EDW_TPRT_TRADE_PARTNER_LSTG(
431 ALTERNATE_NAME,
432 CUST_ACCESS_TMPL,
433 CUST_ANALYSIS_FY,
434 CUST_CAT_CODE,
435 CUST_CLASS,
436 CUST_COMPETITOR,
437 CUST_COTERM_DATE,
438 CUST_DO_NOT_MAIL,
439 CUST_FISCAL_END,
440 CUST_FOB_POINT,
441 CUST_FREIGHT,
442 CUST_GSA_IND,
443 CUST_KEY,
444 CUST_NUMBER,
445 CUST_NUM_EMP,
446 CUST_ORDER_TYPE,
447 CUST_ORIG_SYS,
448 CUST_ORIG_SYS_REF,
449 CUST_PRICE_LIST,
450 CUST_PROSPECT,
451 CUST_REF_USE_FLAG,
452 CUST_REVENUE_CURR,
453 CUST_REVENUE_NEXT,
454 CUST_SALES_CHNL,
455 CUST_SALES_REP,
456 CUST_SHIP_PARTIAL,
457 CUST_SHIP_VIA,
458 CUST_STATUS,
459 CUST_TAX_CODE,
460 CUST_TAX_HDR_FLAG,
461 CUST_TAX_ROUND,
462 CUST_THIRD_PARTY,
463 CUST_TYPE,
464 CUST_YEAR_EST,
465 END_ACTIVE_DATE,
466 INSTANCE,
467 LAST_UPDATE_DATE,
468 NAME,
469 PARENT_TPARTNER_FK,
470 PAYMENT_TERMS,
471 SIC_CODE,
472 START_ACTIVE_DATE,
473 TAXPAYER_ID,
474 TAX_REG_NUM,
475 TRADE_PARTNER_DP,
476 TRADE_PARTNER_PK,
477 USER_ATTRIBUTE1,
478 USER_ATTRIBUTE2,
479 USER_ATTRIBUTE3,
480 USER_ATTRIBUTE4,
481 USER_ATTRIBUTE5,
482 VNDR_HOLD_FLAG,
483 VNDR_INSPECT_REQ,
484 VNDR_MINORITY_GRP,
485 VNDR_NUMBER,
486 VNDR_ONE_TIME,
487 VNDR_RECEIPT_REQ,
488 VNDR_SMALL_BUS,
489 VNDR_SUB_RECEIPT,
490 VNDR_TYPE,
491 VNDR_UNORDER_RCV,
492 VNDR_WOMEN_OWNED,
493 OPERATION_CODE,
494 COLLECTION_STATUS)
495 select
496 ALTERNATE_NAME,
497 CUST_ACCESS_TMPL,
498 CUST_ANALYSIS_FY,
499 CUST_CAT_CODE,
500 CUST_CLASS,
501 CUST_COMPETITOR,
502 CUST_COTERM_DATE,
503 CUST_DO_NOT_MAIL,
504 CUST_FISCAL_END,
505 CUST_FOB_POINT,
506 CUST_FREIGHT,
507 CUST_GSA_IND,
508 CUST_KEY,
509 CUST_NUMBER,
510 CUST_NUM_EMP,
511 CUST_ORDER_TYPE,
512 CUST_ORIG_SYS,
513 CUST_ORIG_SYS_REF,
514 CUST_PRICE_LIST,
515 CUST_PROSPECT,
516 CUST_REF_USE_FLAG,
517 CUST_REVENUE_CURR,
518 CUST_REVENUE_NEXT,
519 CUST_SALES_CHNL,
520 CUST_SALES_REP,
521 CUST_SHIP_PARTIAL,
522 CUST_SHIP_VIA,
523 CUST_STATUS,
524 CUST_TAX_CODE,
525 CUST_TAX_HDR_FLAG,
526 CUST_TAX_ROUND,
527 CUST_THIRD_PARTY,
528 CUST_TYPE,
529 CUST_YEAR_EST,
530 END_ACTIVE_DATE,
531 INSTANCE,
532 LAST_UPDATE_DATE,
533 NAME,
534 nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
535 PAYMENT_TERMS,
536 SIC_CODE,
537 START_ACTIVE_DATE,
538 TAXPAYER_ID,
539 TAX_REG_NUM,
540 TRADE_PARTNER_DP,
541 TRADE_PARTNER_PK,
542 USER_ATTRIBUTE1,
543 USER_ATTRIBUTE2,
544 USER_ATTRIBUTE3,
545 USER_ATTRIBUTE4,
546 USER_ATTRIBUTE5,
547 VNDR_HOLD_FLAG,
548 VNDR_INSPECT_REQ,
549 VNDR_MINORITY_GRP,
550 VNDR_NUMBER,
551 VNDR_ONE_TIME,
552 VNDR_RECEIPT_REQ,
553 VNDR_SMALL_BUS,
554 VNDR_SUB_RECEIPT,
555 VNDR_TYPE,
556 VNDR_UNORDER_RCV,
557 VNDR_WOMEN_OWNED,
558 NULL, -- OPERATION_CODE
559 'READY'
560 from EDW_TPRT_TRADE_PARTNER_LCV
561 where last_update_date between l_push_date_range1 and l_push_date_range2;
562
563 -------------------------------------------------------------------------
564 -- to populate the partent_tpartner_fk of vendors with >4 levels parents
565 --
566
567 update_hierarchy5 (l_push_date_range1, l_push_date_range2);
568
569 --
570 -------------------------------------------------------------------------
571
572 l_rows_inserted := sql%rowcount;
573 l_duration := sysdate - l_temp_date;
574
575 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
576 ' rows into the staging table: ' || l_staging_table_name);
577 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
578 edw_log.put_line(' ');
579
580 -- ---------------------------------------------------------------------------
581 -- END OF Collection , Developer Customizable Section
582 -- ---------------------------------------------------------------------------
583
584 EDW_TRD_PARTNER_M_C.G_row_count :=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;
585
586 Exception When others then
587 Errbuf := sqlerrm;
588 Retcode := sqlcode;
589 EDW_TRD_PARTNER_M_C.g_exception_message := Retcode || ':' || Errbuf;
590 rollback;
591 raise;
592
593 End Push_Trade_Partner;
594
595
596 Procedure Push_P1_TPartner(Errbuf out NOCOPY Varchar2,
597 Retcode out NOCOPY Varchar2,
598 p_from_date Date := NULL,
599 p_to_date Date := NULL) IS
600 l_staging_table_name Varchar2(30) := 'EDW_TPRT_P1_TPARTNER_LSTG';
601 l_push_date_range1 Date := NULL;
602 l_push_date_range2 Date := NULL;
603 l_temp_date Date := NULL;
604 l_rows_inserted Number := 0;
605 l_duration Number := 0;
606 l_exception_msg Varchar2(2000) := Null;
607
608 -- -------------------------------------------
609 -- Put any additional developer variables here
610 -- -------------------------------------------
611
612 Begin
613 Errbuf :=NULL;
614 Retcode:=0;
615
616
617 -- -----------------------------------------------------------------------------
618 -- Start of Collection , Developer Customizable Section
619 -- -----------------------------------------------------------------------------
620
621 l_push_date_range1 := p_from_date;
622 l_push_date_range2 := p_to_date;
623
624 edw_log.put_line(' ');
625 edw_log.put_line('Pushing data for TP Parent 1 Staging Table...');
626
627 l_temp_date := sysdate;
628 Insert Into EDW_TPRT_P1_TPARTNER_LSTG(
629 ALLOW_SUB_RECEIPT,
630 ALLOW_UNORDER_RCV,
631 ALTERNATE_NAME,
632 END_ACTIVE_DATE,
633 HOLD_FLAG,
634 INSPECT_REQUIRED,
635 INSTANCE,
636 LAST_UPDATE_DATE,
637 MINORITY_GROUP,
638 NAME,
639 ONE_TIME_FLAG,
640 PARENT_TPARTNER_FK,
641 PAYMENT_TERMS,
642 RECEIPT_REQUIRED,
643 SIC_CODE,
644 SMALL_BUSINESS,
645 START_ACTIVE_DATE,
646 TAXPAYER_ID,
647 TAX_REG_NUM,
648 USER_ATTRIBUTE1,
649 USER_ATTRIBUTE2,
650 USER_ATTRIBUTE3,
651 USER_ATTRIBUTE4,
652 USER_ATTRIBUTE5,
653 TPARTNER_DP,
654 VENDOR_NUMBER,
655 TPARTNER_PK,
656 VENDOR_TYPE,
657 WOMEN_OWNED,
658 OPERATION_CODE,
659 COLLECTION_STATUS)
660 select
661 ALLOW_SUB_RECEIPT,
662 ALLOW_UNORDER_RCV,
663 ALTERNATE_NAME,
664 END_ACTIVE_DATE,
665 HOLD_FLAG,
666 INSPECT_REQUIRED,
667 INSTANCE,
668 LAST_UPDATE_DATE,
669 MINORITY_GROUP,
670 NAME,
671 ONE_TIME_FLAG,
672 nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
673 PAYMENT_TERMS,
674 RECEIPT_REQUIRED,
675 SIC_CODE,
676 SMALL_BUSINESS,
677 START_ACTIVE_DATE,
678 TAXPAYER_ID,
679 TAX_REG_NUM,
680 USER_ATTRIBUTE1,
681 USER_ATTRIBUTE2,
682 USER_ATTRIBUTE3,
683 USER_ATTRIBUTE4,
684 USER_ATTRIBUTE5,
685 TPARTNER_DP,
686 VENDOR_NUMBER,
687 TPARTNER_PK,
688 VENDOR_TYPE,
689 WOMEN_OWNED,
690 NULL, -- OPERATION_CODE
691 'READY'
692 from EDW_TPRT_P1_TPARTNER_LCV
693 where last_update_date between l_push_date_range1 and l_push_date_range2;
694
695 l_rows_inserted := sql%rowcount;
696 l_duration := sysdate - l_temp_date;
697
698 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
699 ' rows into the staging table: ' || l_staging_table_name);
700 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
701 edw_log.put_line(' ');
702
703 -- ---------------------------------------------------------------------------
704 -- END OF Collection , Developer Customizable Section
705 -- ---------------------------------------------------------------------------
706
707 EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;
708
709 Exception When others then
710 Errbuf := sqlerrm;
711 Retcode := sqlcode;
712 EDW_TRD_PARTNER_M_C.g_exception_message := Retcode || ':' || Errbuf;
713 rollback;
714 raise;
715
716 End Push_P1_TPartner;
717
718
719 Procedure Push_P2_TPartner(Errbuf out NOCOPY Varchar2,
720 Retcode out NOCOPY Varchar2,
721 p_from_date Date := NULL,
722 p_to_date Date := NULL) IS
723 l_staging_table_name Varchar2(30) := 'EDW_TPRT_P2_TPARTNER_LSTG';
724 l_push_date_range1 Date := NULL;
725 l_push_date_range2 Date := NULL;
726 l_temp_date Date := NULL;
727 l_rows_inserted Number := 0;
728 l_duration Number := 0;
729 l_exception_msg Varchar2(2000) := Null;
730
731 -- -------------------------------------------
732 -- Put any additional developer variables here
733 -- -------------------------------------------
734
735 Begin
736 Errbuf :=NULL;
737 Retcode:=0;
738
739 l_push_date_range1 := p_from_date;
740 l_push_date_range2 := p_to_date;
741
742 -- -----------------------------------------------------------------------------
743 -- Start of Collection , Developer Customizable Section
744 -- -----------------------------------------------------------------------------
745 edw_log.put_line(' ');
746 edw_log.put_line('Pushing data for TP Parent 2 Staging Table...');
747
748 l_temp_date := sysdate;
749 Insert Into EDW_TPRT_P2_TPARTNER_LSTG(
750 ALLOW_SUB_RECEIPT,
751 ALLOW_UNORDER_RCV,
752 ALTERNATE_NAME,
753 END_ACTIVE_DATE,
754 HOLD_FLAG,
755 INSPECT_REQUIRED,
756 INSTANCE,
757 LAST_UPDATE_DATE,
758 MINORITY_GROUP,
759 NAME,
760 ONE_TIME_FLAG,
761 PARENT_TPARTNER_FK,
762 PAYMENT_TERMS,
763 RECEIPT_REQUIRED,
764 SIC_CODE,
765 SMALL_BUSINESS,
766 START_ACTIVE_DATE,
767 TAXPAYER_ID,
768 TAX_REG_NUM,
769 USER_ATTRIBUTE1,
770 USER_ATTRIBUTE2,
771 USER_ATTRIBUTE3,
772 USER_ATTRIBUTE4,
773 USER_ATTRIBUTE5,
774 TPARTNER_DP,
775 VENDOR_NUMBER,
776 TPARTNER_PK,
777 VENDOR_TYPE,
778 WOMEN_OWNED,
779 OPERATION_CODE,
780 COLLECTION_STATUS)
781 select
782 ALLOW_SUB_RECEIPT,
783 ALLOW_UNORDER_RCV,
784 ALTERNATE_NAME,
785 END_ACTIVE_DATE,
786 HOLD_FLAG,
787 INSPECT_REQUIRED,
788 INSTANCE,
789 LAST_UPDATE_DATE,
790 MINORITY_GROUP,
791 NAME,
792 ONE_TIME_FLAG,
793 nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
794 PAYMENT_TERMS,
795 RECEIPT_REQUIRED,
796 SIC_CODE,
797 SMALL_BUSINESS,
798 START_ACTIVE_DATE,
799 TAXPAYER_ID,
800 TAX_REG_NUM,
801 USER_ATTRIBUTE1,
802 USER_ATTRIBUTE2,
803 USER_ATTRIBUTE3,
804 USER_ATTRIBUTE4,
805 USER_ATTRIBUTE5,
806 TPARTNER_DP,
807 VENDOR_NUMBER,
808 TPARTNER_PK,
809 VENDOR_TYPE,
810 WOMEN_OWNED,
811 NULL, -- OPERATION_CODE
812 'READY'
813 from EDW_TPRT_P2_TPARTNER_LCV
814 where last_update_date between l_push_date_range1 and l_push_date_range2;
815
816 l_rows_inserted := sql%rowcount;
817 l_duration := sysdate - l_temp_date;
818
819 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
820 ' rows into the staging table: ' || l_staging_table_name);
821 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
822 edw_log.put_line(' ');
823
824 -- ---------------------------------------------------------------------------
825 -- END OF Collection , Developer Customizable Section
826 -- ---------------------------------------------------------------------------
827 EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;
828
829 Exception When others then
830 Errbuf := sqlerrm;
831 Retcode := sqlcode;
832 EDW_TRD_PARTNER_M_C.g_exception_message := Retcode || ':' || Errbuf;
833 rollback;
834 raise;
835
836
837 End Push_P2_TPartner;
838
839
840 Procedure Push_P3_TPartner(Errbuf out NOCOPY Varchar2,
841 Retcode out NOCOPY Varchar2,
842 p_from_date Date := NULL,
843 p_to_date Date := NULL) IS
844 l_staging_table_name Varchar2(30) := 'EDW_TPRT_P3_TPARTNER_LSTG';
845 l_push_date_range1 Date := NULL;
846 l_push_date_range2 Date := NULL;
847 l_temp_date Date := NULL;
848 l_rows_inserted Number := 0;
849 l_duration Number := 0;
850 l_exception_msg Varchar2(2000) := Null;
851
852 -- -------------------------------------------
853 -- Put any additional developer variables here
854 -- -------------------------------------------
855
856 Begin
857 Errbuf :=NULL;
858 Retcode:=0;
859
860 l_push_date_range1 := p_from_date;
861 l_push_date_range2 := p_to_date;
862
863 -- -----------------------------------------------------------------------------
864 -- Start of Collection , Developer Customizable Section
865 -- -----------------------------------------------------------------------------
866 edw_log.put_line(' ');
867 edw_log.put_line('Pushing data for TP Parent 3 Staging Table...');
868
869 l_temp_date := sysdate;
870 Insert Into EDW_TPRT_P3_TPARTNER_LSTG(
871 ALLOW_SUB_RECEIPT,
872 ALLOW_UNORDER_RCV,
873 ALTERNATE_NAME,
874 END_ACTIVE_DATE,
875 HOLD_FLAG,
876 INSPECT_REQUIRED,
877 INSTANCE,
878 LAST_UPDATE_DATE,
879 MINORITY_GROUP,
880 NAME,
881 ONE_TIME_FLAG,
882 PARENT_TPARTNER_FK,
883 PAYMENT_TERMS,
884 RECEIPT_REQUIRED,
885 SIC_CODE,
886 SMALL_BUSINESS,
887 START_ACTIVE_DATE,
888 TAXPAYER_ID,
889 TAX_REG_NUM,
890 USER_ATTRIBUTE1,
891 USER_ATTRIBUTE2,
892 USER_ATTRIBUTE3,
893 USER_ATTRIBUTE4,
894 USER_ATTRIBUTE5,
895 TPARTNER_DP,
896 VENDOR_NUMBER,
897 TPARTNER_PK,
898 VENDOR_TYPE,
899 WOMEN_OWNED,
900 OPERATION_CODE,
901 COLLECTION_STATUS)
902 select
903 ALLOW_SUB_RECEIPT,
904 ALLOW_UNORDER_RCV,
905 ALTERNATE_NAME,
906 END_ACTIVE_DATE,
907 HOLD_FLAG,
908 INSPECT_REQUIRED,
909 INSTANCE,
910 LAST_UPDATE_DATE,
911 MINORITY_GROUP,
912 NAME,
913 ONE_TIME_FLAG,
914 nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
915 PAYMENT_TERMS,
916 RECEIPT_REQUIRED,
917 SIC_CODE,
918 SMALL_BUSINESS,
919 START_ACTIVE_DATE,
920 TAXPAYER_ID,
921 TAX_REG_NUM,
922 USER_ATTRIBUTE1,
923 USER_ATTRIBUTE2,
924 USER_ATTRIBUTE3,
925 USER_ATTRIBUTE4,
926 USER_ATTRIBUTE5,
927 TPARTNER_DP,
928 VENDOR_NUMBER,
929 TPARTNER_PK,
930 VENDOR_TYPE,
931 WOMEN_OWNED,
932 NULL, -- OPERATION_CODE
933 'READY'
934 from EDW_TPRT_P3_TPARTNER_LCV
935 where last_update_date between l_push_date_range1 and l_push_date_range2;
936
937
938 l_rows_inserted := sql%rowcount;
939 l_duration := sysdate - l_temp_date;
940
941 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted, 0))||
942 ' rows into the staging table: ' || l_staging_table_name);
943 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
944 edw_log.put_line(' ');
945
946 -- ---------------------------------------------------------------------------
947 -- END OF Collection , Developer Customizable Section
948 -- ---------------------------------------------------------------------------
949 EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;
950
951 Exception When others then
952 Errbuf := sqlerrm;
953 Retcode := sqlcode;
954 EDW_TRD_PARTNER_M_C.g_exception_message:=Retcode || ':' || Errbuf;
955 rollback;
956 raise;
957
958
959 End Push_P3_TPartner;
960
961
962 Procedure Push_P4_TPartner(Errbuf out NOCOPY Varchar2,
963 Retcode out NOCOPY Varchar2,
964 p_from_date Date := NULL,
965 p_to_date Date := NULL) IS
966 l_staging_table_name Varchar2(30) := 'EDW_TPRT_P4_TPARTNER_LSTG';
967 l_push_date_range1 Date := NULL;
968 l_push_date_range2 Date := NULL;
969 l_temp_date Date := NULL;
970 l_rows_inserted Number := 0;
971 l_duration Number := 0;
972 l_exception_msg Varchar2(2000) := Null;
973
974 -- -------------------------------------------
975 -- Put any additional developer variables here
976 -- -------------------------------------------
977
978 Begin
979 Errbuf :=NULL;
980 Retcode:=0;
981
982 l_push_date_range1 := p_from_date;
983 l_push_date_range2 := p_to_date;
984
985 -- -----------------------------------------------------------------------------
986 -- Start of Collection , Developer Customizable Section
987 -- -----------------------------------------------------------------------------
988 edw_log.put_line(' ');
989 edw_log.put_line('Pushing data for TP Parent 4 Staging Table...');
990
991 l_temp_date := sysdate;
992 Insert Into EDW_TPRT_P4_TPARTNER_LSTG(
993 ALLOW_SUB_RECEIPT,
994 ALLOW_UNORDER_RCV,
995 PARENT_TPARTNER_FK,
996 ALTERNATE_NAME,
997 END_ACTIVE_DATE,
998 HOLD_FLAG,
999 INSPECT_REQUIRED,
1000 INSTANCE,
1001 LAST_UPDATE_DATE,
1002 MINORITY_GROUP,
1003 NAME,
1004 ONE_TIME_FLAG,
1005 PAYMENT_TERMS,
1006 RECEIPT_REQUIRED,
1007 SIC_CODE,
1008 SMALL_BUSINESS,
1009 START_ACTIVE_DATE,
1010 TAXPAYER_ID,
1011 TAX_REG_NUM,
1012 USER_ATTRIBUTE1,
1013 USER_ATTRIBUTE2,
1014 USER_ATTRIBUTE3,
1015 USER_ATTRIBUTE4,
1016 USER_ATTRIBUTE5,
1017 TPARTNER_DP,
1018 VENDOR_NUMBER,
1019 TPARTNER_PK,
1020 VENDOR_TYPE,
1021 WOMEN_OWNED,
1022 OPERATION_CODE,
1023 COLLECTION_STATUS)
1024 select
1025 ALLOW_SUB_RECEIPT,
1026 ALLOW_UNORDER_RCV,
1027 nvl(PARENT_TPARTNER_FK, 'NA_EDW'),
1028 ALTERNATE_NAME,
1029 END_ACTIVE_DATE,
1030 HOLD_FLAG,
1031 INSPECT_REQUIRED,
1032 INSTANCE,
1033 LAST_UPDATE_DATE,
1034 MINORITY_GROUP,
1035 NAME,
1036 ONE_TIME_FLAG,
1037 PAYMENT_TERMS,
1038 RECEIPT_REQUIRED,
1039 SIC_CODE,
1040 SMALL_BUSINESS,
1041 START_ACTIVE_DATE,
1042 TAXPAYER_ID,
1043 TAX_REG_NUM,
1044 USER_ATTRIBUTE1,
1045 USER_ATTRIBUTE2,
1046 USER_ATTRIBUTE3,
1047 USER_ATTRIBUTE4,
1048 USER_ATTRIBUTE5,
1049 TPARTNER_DP,
1050 VENDOR_NUMBER,
1051 TPARTNER_PK,
1052 VENDOR_TYPE,
1053 WOMEN_OWNED,
1054 NULL, -- OPERATION_CODE
1055 'READY'
1056 from EDW_TPRT_P4_TPARTNER_LCV
1057 where last_update_date between l_push_date_range1 and l_push_date_range2;
1058
1059 l_rows_inserted := sql%rowcount;
1060 l_duration := sysdate - l_temp_date;
1061
1062 edw_log.put_line('Inserted '||to_char(nvl(l_rows_inserted,0))||
1063 ' rows into the staging table: ' || l_staging_table_name);
1064 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
1065 edw_log.put_line(' ');
1066
1067 -- ---------------------------------------------------------------------------
1068 -- END OF Collection , Developer Customizable Section
1069 -- ---------------------------------------------------------------------------
1070 EDW_TRD_PARTNER_M_C.G_row_count:=EDW_TRD_PARTNER_M_C.G_row_count+l_rows_inserted;
1071 Exception When others then
1072 Errbuf := sqlerrm;
1073 Retcode := sqlcode;
1074 EDW_TRD_PARTNER_M_C.g_exception_message:= Retcode || ':' || Errbuf;
1075 rollback;
1076 raise;
1077
1078
1079 End Push_P4_TPartner;
1080
1081
1082 Procedure push( Errbuf out NOCOPY Varchar2,
1083 Retcode out NOCOPY Varchar2,
1084 p_from_date IN Varchar2,
1085 p_to_date IN Varchar2) IS
1086 L_PUSH_DATE_RANGE1 Date:=NULL;
1087 L_PUSH_DATE_RANGE2 Date:=NULL;
1088 l_proc_name varchar2(60);
1089
1090 l_from_date date;
1091 l_to_date date;
1092
1093 Begin
1094
1095 Errbuf := NULL;
1096 Retcode := 0;
1097
1098 IF (Not EDW_COLLECTION_UTIL.setup('EDW_TRD_PARTNER_M')) THEN
1099 errbuf := fnd_message.get;
1100 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
1101 END IF;
1102
1103 fnd_date.initialize('YYYY/MM/DD', 'YYYY/MM/DD HH24:MI:SS');
1104
1105 l_from_date := fnd_date.displayDT_to_date(p_from_date);
1106 l_to_date := fnd_date.displayDT_to_date(p_to_date);
1107
1108 L_PUSH_DATE_RANGE1 := nvl (l_from_date,
1109 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
1110 L_PUSH_DATE_RANGE2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
1111 edw_log.put_line('The collection range is from ' ||
1112 to_char(l_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to ' ||
1113 to_char(l_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
1114 edw_log.put_line(' ');
1115
1116
1117 l_proc_name := 'EDW_TRD_PARTNER_M_C.populate_hierarchies';
1118 EDW_TRD_PARTNER_M_C.populate_hierarchies(L_PUSH_DATE_RANGE1,L_PUSH_DATE_RANGE2);
1119
1120 l_proc_name := 'EDW_TRD_PARTNER_M_C.Push_TPartner_Loc';
1121 EDW_TRD_PARTNER_M_C.Push_TPartner_Loc(Errbuf,
1122 Retcode,
1123 L_PUSH_DATE_RANGE1,
1124 L_PUSH_DATE_RANGE2);
1125 l_proc_name := 'EDW_TRD_PARTNER_M_C.Push_Trade_Partner';
1126 EDW_TRD_PARTNER_M_C.Push_Trade_Partner(Errbuf,
1127 Retcode,
1128 L_PUSH_DATE_RANGE1,
1129 L_PUSH_DATE_RANGE2);
1130 l_proc_name := 'EDW_TRD_PARTNER_M_C.Push_P1_TPartner';
1131 EDW_TRD_PARTNER_M_C.Push_P1_TPartner(Errbuf,
1132 Retcode,
1133 L_PUSH_DATE_RANGE1,
1134 L_PUSH_DATE_RANGE2);
1135 l_proc_name := 'EDW_TRD_PARTNER_M_C.Push_P2_TPartner';
1136 EDW_TRD_PARTNER_M_C.Push_P2_TPartner(Errbuf,
1137 Retcode,
1138 L_PUSH_DATE_RANGE1,
1139 L_PUSH_DATE_RANGE2);
1140 l_proc_name := 'EDW_TRD_PARTNER_M_C.Push_P3_TPartner';
1141 EDW_TRD_PARTNER_M_C.Push_P3_TPartner(Errbuf,
1142 Retcode,
1143 L_PUSH_DATE_RANGE1,
1144 L_PUSH_DATE_RANGE2);
1145 l_proc_name := 'EDW_TRD_PARTNER_M_C.Push_P4_TPartner';
1146 EDW_TRD_PARTNER_M_C.Push_P4_TPartner(Errbuf,
1147 Retcode,
1148 L_PUSH_DATE_RANGE1,
1149 L_PUSH_DATE_RANGE2);
1150
1151 EDW_COLLECTION_UTIL.wrapup(TRUE, EDW_TRD_PARTNER_M_C.g_row_count_m,
1152 EDW_TRD_PARTNER_M_C.g_exception_message,
1153 L_PUSH_DATE_RANGE1, L_PUSH_DATE_RANGE2);
1154
1155
1156 Exception When others then
1157 Errbuf := sqlerrm;
1158 Retcode := sqlcode;
1159
1160
1161 EDW_TRD_PARTNER_M_C.g_exception_message :=
1162 EDW_TRD_PARTNER_M_C.g_exception_message || ' <> ' || Retcode ||
1163 ' : ' || Errbuf;
1164
1165 EDW_COLLECTION_UTIL.wrapup(FALSE,0,EDW_TRD_PARTNER_M_C.g_exception_message,
1166 l_push_date_range1, l_push_date_range2);
1167
1168 FND_FILE.PUT_LINE(FND_FILE.LOG, l_proc_name || ' failed');
1169
1170 raise;
1171
1172 End push;
1173
1174 End EDW_TRD_PARTNER_M_C;