[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPM_RES_UTIL_F_C
Source
1 Package Body OPI_EDW_OPM_RES_UTIL_F_C AS
2 /* $Header: OPIMORUB.pls 120.1 2005/06/07 03:29:53 appldev $ */
3 g_errbuf VARCHAR2(2000) := NULL;
4 g_retcode VARCHAR2(200) := NULL;
5 g_row_count NUMBER:=0;
6 g_push_from_date DATE := NULL;
7 g_push_to_date DATE := NULL;
8 g_seq_id NUMBER:=0;
9 -- ---------------------------------
10 -- PRIVATE PROCEDURES AND FUNCTIONS
11 -- ---------------------------------
12 -----------------------------------------------------------
13 --PROCEDURE PUSH_TO_LOCAL
14 -----------------------------------------------------------
15 FUNCTION PUSH_TO_LOCAL(L_FROM_DATE DATE,L_TO_DATE DATE) RETURN NUMBER IS
16 l_no_rows number;
17 BEGIN
18 -- ------------------------------------------------
19 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
20 -- In case of source=target, we need to separate
21 -- out the records in progress vs the records which
22 -- is ready to be picked up by collection enginee.
23 -- In our case, we consider the records to be in
24 -- progress until the push_to_local procedure for
25 -- all view types has completed successfully.
26 -- ------------------------------------------------
27 INSERT INTO OPI_EDW_RES_UTIL_FSTG(
28 RES_UTIL_PK,
29 LOCATOR_FK,
30 RES_FK,
31 TRX_DATE_FK,
32 UOM_FK,
33 INSTANCE_FK,
34 USER_FK1,
35 USER_FK2,
36 USER_FK3,
37 USER_FK4,
38 USER_FK5,
39 ACT_RES_USAGE,
40 AVAIL_RES,
41 DEPARTMENT,
42 TRX_DATE,
43 USER_MEASURE1,
44 USER_MEASURE2,
45 USER_MEASURE3,
46 USER_MEASURE4,
47 USER_MEASURE5,
48 LAST_UPDATE_DATE,
49 USER_ATTRIBUTE1,
50 USER_ATTRIBUTE2,
51 USER_ATTRIBUTE3,
52 USER_ATTRIBUTE4,
53 USER_ATTRIBUTE5,
54 USER_ATTRIBUTE6,
55 USER_ATTRIBUTE7,
56 USER_ATTRIBUTE8,
57 USER_ATTRIBUTE9,
58 USER_ATTRIBUTE10,
59 USER_ATTRIBUTE11,
60 USER_ATTRIBUTE12,
61 USER_ATTRIBUTE13,
62 USER_ATTRIBUTE14,
63 USER_ATTRIBUTE15,
64 COLLECTION_STATUS)
65 SELECT /*+ ALL_ROWS */
66 RES_UTIL_PK,
67 LOCATOR_FK,
68 RES_FK,
69 TRX_DATE_FK,
70 UOM_FK,
71 INSTANCE_FK,
72 USER_FK1,
73 USER_FK2,
74 USER_FK3,
75 USER_FK4,
76 USER_FK5,
77 ACT_RES_USAGE,
78 AVAIL_RES,
79 DEPARTMENT,
80 TRX_DATE,
81 USER_MEASURE1,
82 USER_MEASURE2,
83 USER_MEASURE3,
84 USER_MEASURE4,
85 USER_MEASURE5,
86 LAST_UPDATE_DATE,
87 USER_ATTRIBUTE1,
88 USER_ATTRIBUTE2,
89 USER_ATTRIBUTE3,
90 USER_ATTRIBUTE4,
91 USER_ATTRIBUTE5,
92 USER_ATTRIBUTE6,
93 USER_ATTRIBUTE7,
94 USER_ATTRIBUTE8,
95 USER_ATTRIBUTE9,
96 USER_ATTRIBUTE10,
97 USER_ATTRIBUTE11,
98 USER_ATTRIBUTE12,
99 USER_ATTRIBUTE13,
100 USER_ATTRIBUTE14,
101 USER_ATTRIBUTE15,
102 'LOCAL READY'
103 FROM OPI_EDW_OPM_RES_UTIL_FCV
104 WHERE LAST_UPDATE_DATE BETWEEN L_FROM_DATE AND L_TO_DATE;
105 l_no_rows := sql%rowcount;
106 RETURN l_no_rows;
107 EXCEPTION
108 WHEN OTHERS THEN
109 g_errbuf:=sqlerrm;
110 g_retcode:=sqlcode;
111 RETURN(-1);
112 END;
113 -- ---------------------------------
114 -- PUBLIC PROCEDURES
115 -- ---------------------------------
116 -----------------------------------------------------------
117 -- PROCEDURE PUSH
118 -----------------------------------------------------------
119 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
120 Retcode in out NOCOPY Varchar2,
121 p_from_date IN Varchar2,
122 p_to_date IN Varchar2) IS
123 l_fact_name Varchar2(30) ;
124 l_staging_table Varchar2(30) ;
125 l_exception_msg Varchar2(2000);
126 l_from_date Date;
127 l_to_date Date;
128 l_seq_id1 NUMBER ;
129 l_seq_id2 NUMBER ;
130 l_row_count NUMBER ;
131 l_row_count1 NUMBER ;
132 l_row_count2 NUMBER ;
133 l_pmi_schema VARCHAR2(30);
134 l_status VARCHAR2(30);
135 l_industry VARCHAR2(30);
136 l_push_local_failure EXCEPTION;
137 l_iden_change_failure EXCEPTION;
138 -- -------------------------------------------
139 -- Put any additional developer variables here
140 -- -------------------------------------------
141 BEGIN
142 l_fact_name :='OPI_EDW_RES_UTIL_F';
143 l_staging_table :='OPI_EDW_RES_UTIL_FSTG';
144 l_exception_msg :=Null;
145 l_from_date :=Null;
146 l_to_date :=Null;
147 l_seq_id1 := -1;
148 l_seq_id2 := -1;
149 l_row_count := 0;
150 l_row_count1 := 0;
151 l_row_count2 := 0;
152
153 Errbuf :=NULL;
154 Retcode:=0;
155 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
156 l_to_date :=to_date(p_to_date,'YYYY/MM/DD HH24:MI:SS');
157 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,l_staging_table,l_staging_table,l_exception_msg)) THEN
158 errbuf := fnd_message.get;
159 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
160 Return;
161 END IF;
162 -- --------------------------------------------
163 -- Taking care of cases where the input from/to
164 -- date is NULL.
165 -- --------------------------------------------
166 g_push_from_date := nvl(l_from_date,
167 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
168 EDW_COLLECTION_UTIL.g_offset);
169 g_push_to_date := nvl(l_to_date,
170 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
171 edw_log.put_line( 'The collection range is from '||
172 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
173 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
174 edw_log.put_line(' ');
175 -- --------------------------------------------
176 -- Push to local staging table
177 -- --------------------------------------------
178 edw_log.put_line(' ');
179 edw_log.put_line('Inserting into local staging table ');
180 l_row_count1 := PUSH_TO_LOCAL(g_push_from_date,g_push_to_date);
181 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
182 edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
183 ' rows into the local staging table ');
184 edw_log.put_line(' ');
185 g_row_count:= l_row_count1;
186 edw_log.put_line(' ');
187 edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
188 ' rows into local staging table ');
189 -- --------------------------------------------
190 -- No exception raised so far. Call wrapup to transport
191 -- data to target database, and insert messages into logs
192 -- -----------------------------------------------
193 edw_log.put_line(' ');
194 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
195 ' rows into the staging table');
196 edw_log.put_line(' ');
197 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
198 g_push_from_date, g_push_to_date);
199 -- ---------------------------------------------------------------------------
200 -- END OF Collection , Developer Customizable Section
201 -- ---------------------------------------------------------------------------
202 EXCEPTION
203 WHEN L_PUSH_LOCAL_FAILURE THEN
204 Errbuf:=g_errbuf;
205 Retcode:=g_retcode;
206 l_exception_msg := Retcode || ':' || Errbuf;
207 rollback; -- Rollback insert into local staging
208 edw_log.put_line('Inserting into local staging have failed');
209 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
210 raise;
211 WHEN L_IDEN_CHANGE_FAILURE THEN
212 Errbuf:=g_errbuf;
213 Retcode:=g_retcode;
214 l_exception_msg := Retcode || ':' || Errbuf;
215 edw_log.put_line('Identifying changed records have Failed');
216 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
217 raise;
218 WHEN OTHERS THEN
219 Errbuf:=g_errbuf;
220 Retcode:=g_retcode;
221 l_exception_msg := Retcode || ':' || Errbuf;
222 rollback;
223 edw_log.put_line('Other errors');
224 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
225 g_push_from_date, g_push_to_date);
226 raise;
227 END;
228 END OPI_EDW_OPM_RES_UTIL_F_C ;