[Home] [Help]
PACKAGE BODY: APPS.EDW_POA_SPLRITEM_M_C
Source
1 Package Body EDW_POA_SPLRITEM_M_C AS
2 /* $Header: poaphsib.pls 120.1 2005/06/13 13:10:11 sriswami noship $ */
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
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_POA_SPLRITEM_M' ;
14 l_temp_date Date:=Null;
15 l_date1 Date:=Null;
16 l_date2 Date:=Null;
17 l_rows_inserted Number:=0;
18 l_duration Number:=0;
19 l_exception_msg Varchar2(2000):=Null;
20
21 -- -------------------------------------------
22 -- Put any additional developer variables here
23 -- -------------------------------------------
24 l_from_date date;
25 l_to_date date;
26
27 Begin
28 Errbuf :=NULL;
29 Retcode:=0;
30 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
31 errbuf := fnd_message.get;
32 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
33 END IF;
34
35 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
36 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
37
38 g_push_date_range1 := nvl(l_from_date,
39 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
40 g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
41
42 l_date1 := g_push_date_range1;
43 l_date2 := g_push_date_range2;
44 edw_log.put_line( 'The collection range is from '||
45 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
46 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
47 edw_log.put_line(' ');
48
49 -- -----------------------------------------------------------------------------
50 -- Start of Collection , Developer Customizable Section
51 -- -----------------------------------------------------------------------------
52
53 edw_log.put_line(' ');
54 edw_log.put_line('Pushing data');
55
56 l_temp_date := sysdate;
57
58
59 Push_POA_SPIM_SPLRITEM_LSTG(EDW_POA_SPLRITEM_M_C.g_push_date_range1, EDW_POA_SPLRITEM_M_C.g_push_date_range2);
60
61
62 l_duration := sysdate - l_temp_date;
63
64 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
65 edw_log.put_line(' ');
66 -- ---------------------------------------------------------------------------
67 -- END OF Collection , Developer Customizable Section
68 -- ---------------------------------------------------------------------------
69 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, P_PERIOD_START => l_date1,
70 P_PERIOD_END => l_date2);
71 commit;
72
73
74 Exception When others then
75 Errbuf:=sqlerrm;
76 Retcode:=sqlcode;
77 l_exception_msg := Retcode || ':' || Errbuf;
78 EDW_POA_SPLRITEM_M_C.g_exception_msg := l_exception_msg;
79 rollback;
80 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_POA_SPLRITEM_M_C.g_exception_msg,
81 g_push_date_range1, g_push_date_range2);
82
83 End;
84
85
86 Procedure Push_POA_SPIM_SPLRITEM_LSTG(p_from_date IN date, p_to_date IN DATE) IS
87 l_date1 DATE;
88 l_date2 DATE;
89 l_rows_inserted NUMBER :=0;
90 BEGIN
91 edw_log.put_line('Starting Push_EDW_POA_SPIM_SPLRITEM_LSTG');
92 l_date1 := p_from_date;
93 l_date2 := p_to_date;
94 Insert Into
95 EDW_POA_SPIM_SPLRITEM_LSTG@EDW_APPS_TO_WH(
96 ALL_FK,
97 CREATION_DATE,
98 INSTANCE,
99 LAST_UPDATE_DATE,
100 NAME,
101 SUPPLIER_ITEM_DP,
102 SUPPLIER_ITEM_PK,
103 SUPPLIER_NAME,
104 SUPPLIER_SITE_CODE,
105 SUPPLIER_SITE_ITEM_DP,
106 USER_ATTRIBUTE1,
107 USER_ATTRIBUTE2,
108 USER_ATTRIBUTE3,
109 USER_ATTRIBUTE4,
110 USER_ATTRIBUTE5,
111 OPERATION_CODE,
112 COLLECTION_STATUS)
113 select NVL(ALL_FK, 'NA_EDW'),
114 CREATION_DATE,
115 INSTANCE,
116 LAST_UPDATE_DATE,
117 NAME,
118 SUPPLIER_ITEM_DP,
119 SUPPLIER_ITEM_PK,
120 SUPPLIER_NAME,
121 SUPPLIER_SITE_CODE,
122 SUPPLIER_SITE_ITEM_DP,
123 USER_ATTRIBUTE1,
124 USER_ATTRIBUTE2,
125 USER_ATTRIBUTE3,
126 USER_ATTRIBUTE4,
127 USER_ATTRIBUTE5,
128 NULL, -- OPERATION_CODE
129 'READY'
130 from EDW_POA_SPIM_SPLRITEM_LCV@APPS_TO_APPS
131 where last_update_date between l_date1 and l_date2
132 OR last_update_date is NULL;
133
134 l_rows_inserted := sql%rowcount;
135 EDW_POA_SPLRITEM_M_C.g_row_count := EDW_POA_SPLRITEM_M_C.g_row_count + l_rows_inserted ;
136
137 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
138 ' rows into the staging table');
139
140 edw_log.put_line('Completed Push_EDW_POA_SPIM_SPLRITEM_LSTG');
141
142 Exception When others then
143 rollback;
144 raise;
145 END;
146 End EDW_POA_SPLRITEM_M_C;