[Home] [Help]
PACKAGE BODY: APPS.POA_EUL_UTILS
Source
1 PACKAGE BODY POA_EUL_UTILS AS
2 /* $Header: POAEULB.pls 115.10 2003/04/30 22:50:24 jhou ship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(200) := NULL;
6
7 g_eulOwner VARCHAR2(30);
8 g_EulBA_ID NUMBER(10);
9 g_EulBA_NAME VARCHAR2(100);
10 g_DiscoVersion VARCHAR2(30);
11
12 TYPE g_FolderNamesTable is TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER;
13 g_FolderNames g_FolderNamesTable;
14
15 G_EUL_OWNER_DOES_NOT_EXIST EXCEPTION;
16 PRAGMA EXCEPTION_INIT(G_EUL_OWNER_DOES_NOT_EXIST, -942);
17
18 G_BUSINESS_AREA_DOES_NOT_EXIST EXCEPTION;
19 PRAGMA EXCEPTION_INIT(G_BUSINESS_AREA_DOES_NOT_EXIST, 100);
20
21 /******************************************************************************
22 Procedure InitBusArea3i
23 ******************************************************************************/
24
25 PROCEDURE InitBusArea3i (pBusAreaName IN VARCHAR2) IS
26
27 l_stmt VARCHAR2(1000) := NULL;
28
29 BEGIN
30
31 l_stmt := 'SELECT ba.ba_id, '||
32 ' ba.ba_name '||
33 'FROM '||g_EulOwner||'.EUL_BUSINESS_AREAS ba '||
34 'WHERE ba.ba_name = :p_ba';
35
36 edw_log.put_line('Procedure initBusAreas3i');
37 edw_log.put_line('Going to execute statement:');
38 edw_log.put_line(l_stmt);
39
40 EXECUTE IMMEDIATE l_stmt INTO g_EulBA_ID, g_eulba_name using pBusAreaName;
41
42 EXCEPTION
43 WHEN NO_DATA_FOUND THEN
44 g_EulBA_Name := pBusAreaName;
45 g_errbuf:=sqlerrm;
46 g_retcode:=sqlcode;
47 raise G_BUSINESS_AREA_DOES_NOT_EXIST;
48 WHEN OTHERS THEN
49 g_errbuf:=sqlerrm;
50 g_retcode:=sqlcode;
51 if g_retcode = '-942' then
52 edw_log.put_line('*** Table: ' || g_EulOwner ||
53 '.EUL_BUSINESS_AREAS does not exist***');
54 edw_log.put_line('Check EUL ('|| g_EulOwner ||
55 ') and EUL version (DISCO3I)');
56 raise G_EUL_OWNER_DOES_NOT_EXIST;
57 else
58 raise;
59 end if;
60 END InitBusArea3i;
61
62
63 /******************************************************************************
64 Procedure InitBusArea4i
65 ******************************************************************************/
66
67 PROCEDURE InitBusArea4i (pBusAreaName IN VARCHAR2) IS
68
69 l_stmt VARCHAR2(1000) := NULL;
70
71 BEGIN
72
73 l_stmt := 'SELECT ba.ba_id, '||
74 ' ba.ba_name '||
75 'FROM '||g_EulOwner||'.EUL4_BAS ba '||
76 'WHERE ba.ba_name = :p_ba';
77
78 edw_log.put_line('Procedure initBusAreas4i');
79 edw_log.put_line('Going to execute statement:');
80 edw_log.put_line(l_stmt);
81
82 EXECUTE IMMEDIATE l_stmt INTO g_EulBA_ID, g_eulba_name using pBusAreaName;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 g_EulBA_Name := pBusAreaName;
87 g_errbuf:=sqlerrm;
88 g_retcode:=sqlcode;
89 raise G_BUSINESS_AREA_DOES_NOT_EXIST;
90 WHEN OTHERS THEN
91 g_errbuf:=sqlerrm;
92 g_retcode:=sqlcode;
93 if g_retcode = '-942' then
94 edw_log.put_line('*** Table: ' || g_EulOwner ||
95 '.EUL4_BAS does not exist***');
96 edw_log.put_line('Check EUL ('|| g_EulOwner ||
97 ') and EUL version (DISCO4I)');
98 raise G_EUL_OWNER_DOES_NOT_EXIST;
99 else
100 raise;
101 end if;
102 END InitBusArea4i;
103
104 /******************************************************************************
105 Procedure hideFolder3i
106 ******************************************************************************/
107 PROCEDURE hideFolder3i(pFolderName IN VARCHAR2)
108 IS
109
110 l_stmt VARCHAR2(1000);
111
112 BEGIN
113
114 l_stmt :=
115 'UPDATE ' || g_EulOwner || '.eul_objs obj '||
116 'SET obj.obj_hidden = 1 '||
117 'WHERE obj.obj_name = :pFolderName ' ||
118 ' AND obj.obj_type = ''SOBJ''' ||
119 ' AND EXISTS (select 1 from ' || g_EulOwner || '.eul_ba_obj_links boj' ||
120 ' where boj.BOL_BA_ID = :EulBA_ID' ||
121 ' and boj.BOL_OBJ_ID = obj.OBJ_ID)';
122
123 EXECUTE IMMEDIATE l_stmt using pfoldername, g_EulBA_ID;
124
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 g_errbuf:=sqlerrm;
129 g_retcode:=sqlcode;
130 raise;
131
132 END hideFolder3i;
133
134
135 /******************************************************************************
136 Procedure hideFolder4i
137 ******************************************************************************/
138 PROCEDURE hideFolder4i (pFolderName IN VARCHAR2)
139 IS
140
141 l_stmt VARCHAR2(1000);
142
143 BEGIN
144
145 l_stmt :=
146 'UPDATE ' || g_EulOwner || '.eul4_objs obj '||
147 'SET obj.obj_hidden = 1 '||
148 'WHERE obj.obj_name = :pFolderName ' ||
149 ' AND obj.obj_type = ''SOBJ''' ||
150 ' AND EXISTS (select 1 from ' || g_EulOwner || '.eul4_ba_obj_links boj' ||
151 ' where boj.BOL_BA_ID = :EulBA_ID' ||
152 ' and boj.BOL_OBJ_ID = obj.OBJ_ID)';
153
154 EXECUTE IMMEDIATE l_stmt using pfoldername, g_EulBA_ID;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 g_errbuf:=sqlerrm;
159 g_retcode:=sqlcode;
160 raise;
161
162 END hideFolder4i;
163
164 /******************************************************************************
165 Procedure hideUserAttributes3i
166 This is for bug 1806629 and 1798665
167 ******************************************************************************/
168 PROCEDURE hideUserAttributes3i
169 IS
170
171 l_stmt VARCHAR2(1000);
172
173 BEGIN
174
175 l_stmt :=
176 'UPDATE ' || g_EulOwner || '.eul_expressions exp '||
177 'SET exp.it_hidden = 1 '||
178 'WHERE (exp.it_ext_column like ''%USER_ATTRIBUTE%'' OR ' ||
179 ' exp.it_ext_column like ''USER_FK__KEY%'' OR ' ||
180 ' exp.it_ext_column like ''USER_MEASURE%'') ' ||
181 ' AND EXISTS (select 1 from ' || g_EulOwner || '.eul_ba_obj_links boj' ||
182 ' where boj.BOL_BA_ID = :EulBA_ID ' ||
183 ' and boj.BOL_OBJ_ID = exp.ite_obj_id)';
184
185 EXECUTE IMMEDIATE l_stmt using g_EulBA_ID;
186
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 g_errbuf:=sqlerrm;
191 g_retcode:=sqlcode;
192 raise;
193
194 END hideUserAttributes3i;
195
196
197 /******************************************************************************
198 Procedure hideUserAttributes4i
199 This is for bug 1806629 and 1798665
200 ******************************************************************************/
201 PROCEDURE hideUserAttributes4i
202 IS
203
204 l_stmt VARCHAR2(1000);
205
206 BEGIN
207
208 l_stmt :=
209 'UPDATE ' || g_EulOwner || '.eul4_expressions exp '||
210 'SET exp.it_hidden = 1 '||
211 'WHERE (exp.it_ext_column like ''%USER_ATTRIBUTE%'' OR ' ||
212 ' exp.it_ext_column like ''USER_FK__KEY%'' OR ' ||
213 ' exp.it_ext_column like ''USER_MEASURE%'') ' ||
214 ' AND EXISTS (select 1 from ' || g_EulOwner || '.eul4_ba_obj_links boj' ||
215 ' where boj.BOL_BA_ID = :EulBA_ID' ||
216 ' and boj.BOL_OBJ_ID = exp.it_obj_id)';
217
218 EXECUTE IMMEDIATE l_stmt using g_EulBA_ID;
219
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 g_errbuf:=sqlerrm;
224 g_retcode:=sqlcode;
225 raise;
226
227 END hideUserAttributes4i;
228
229
230 /******************************************************************************
231 Procedure EULMain
232 ******************************************************************************/
233
234 PROCEDURE EULMain (Errbuf IN OUT NOCOPY VARCHAR2,
235 Retcode IN OUT NOCOPY VARCHAR2,
236 pEulOwnerName IN VARCHAR2,
237 pBusAreaName IN VARCHAR2,
238 pDiscoVersion IN VARCHAR2)
239 IS
240
241 l_stmt VARCHAR2(2000) := NULL;
242 l_index BINARY_INTEGER;
243
244 BEGIN
245
246 g_FolderNames (1) := 'Lookup Dimension: Acceptance Required';
247 g_FolderNames (2) := 'Lookup Dimension: Accrued';
248 g_FolderNames (3) := 'Lookup Dimension: Allow Substitute Receipts';
249 g_FolderNames (4) := 'Lookup Dimension: Approved';
250 g_FolderNames (5) := 'Lookup Dimension: Approved Supplier';
251 g_FolderNames (6) := 'Lookup Dimension: Canceled';
252 g_FolderNames (7) := 'Lookup Dimension: Confirmed';
253 g_FolderNames (8) := 'Lookup Dimension: Contract in Effect';
254 g_FolderNames (9) := 'Lookup Dimension: Contract Type';
255 g_FolderNames (10) := 'Lookup Dimension: Custom Measure';
256 g_FolderNames (11) := 'Lookup Dimension: Confirming Order';
257 g_FolderNames (12) := 'Lookup Dimension: Destination Type';
258 g_FolderNames (13) := 'Lookup Dimension: Distribution Encumbered';
259 g_FolderNames (14) := 'Lookup Dimension: Document Type';
260 g_FolderNames (15) := 'Lookup Dimension: Freight On Board Term';
261 g_FolderNames (16) := 'Lookup Dimension: Freight Term';
262 g_FolderNames (17) := 'Lookup Dimension: Frozen';
263 g_FolderNames (18) := 'Lookup Dimension: Line Closed';
264 g_FolderNames (19) := 'Lookup Dimension: Negotiated By Preparer';
265 g_FolderNames (20) := 'Lookup Dimension: Online Requisition';
266 g_FolderNames (21) := 'Lookup Dimension: Parent Transaction Type';
267 g_FolderNames (22) := 'Lookup Dimension: Price Break';
268 g_FolderNames (23) := 'Lookup Dimension: Price Type';
269 g_FolderNames (24) := 'Lookup Dimension: Purchase Classification';
270 g_FolderNames (25) := 'Lookup Dimension: Receipt Exception';
271 g_FolderNames (26) := 'Lookup Dimension: Release Hold';
272 g_FolderNames (27) := 'Lookup Dimension: Shipment Approved';
273 g_FolderNames (28) := 'Lookup Dimension: Shipment Canceled';
274 g_FolderNames (29) := 'Lookup Dimension: Shipment Status';
275 g_FolderNames (30) := 'Lookup Dimension: Shipment Taxable';
276 g_FolderNames (31) := 'Lookup Dimension: Shipment Type';
277 g_FolderNames (32) := 'Lookup Dimension: Source Type';
278 g_FolderNames (33) := 'Lookup Dimension: Supply Agreement';
279 g_FolderNames (34) := 'Lookup Dimension: Transaction Reason';
280 g_FolderNames (35) := 'Lookup Dimension: User Entered';
281
282 g_FolderNames (46) := 'Person Dimension: Approver';
283 g_FolderNames (47) := 'Person Dimension: Deliver To Person';
284 g_FolderNames (48) := 'Person Dimension: Held By Employee';
285 g_FolderNames (49) := 'Person Dimension: Requestor';
286
287 g_FolderNames (51) := 'Time Dimension: Acceptance Date';
288 g_FolderNames (52) := 'Time Dimension: Acceptance Due Date';
289 g_FolderNames (53) := 'Time Dimension: Distribution Creation Date';
290 g_FolderNames (54) := 'Time Dimension: Due Date';
291 ---------- g_FolderNames (55) := 'Time Dimension: End Date';
292 g_FolderNames (56) := 'Time Dimension: Expected Arrival Date';
293 g_FolderNames (57) := 'Time Dimension: First Receipt Date';
294 g_FolderNames (58) := 'Time Dimension: Invoice Creation Date';
295 g_FolderNames (59) := 'Time Dimension: Invoice Received Date';
296 g_FolderNames (60) := 'Time Dimension: Last Accept Date';
297 g_FolderNames (61) := 'Time Dimension: Line Creation Date';
298 g_FolderNames (62) := 'Time Dimension: Parent Transaction Date';
299 g_FolderNames (63) := 'Time Dimension: Printed Date';
300 g_FolderNames (64) := 'Time Dimension: Purchase Creation Date';
301 g_FolderNames (65) := 'Time Dimension: Release Date';
302 g_FolderNames (66) := 'Time Dimension: Revised Date';
303 g_FolderNames (67) := 'Time Dimension: Shipment Approval Date';
304 g_FolderNames (68) := 'Time Dimension: Shipment Creation Date';
305 g_FolderNames (69) := 'Time Dimension: Shipped Date';
306 g_FolderNames (70) := 'Time Dimension: Source Shipment Creation Date';
307 --------- g_FolderNames (71) := 'Time Dimension: Start Date';
308 g_FolderNames (72) := 'Time Dimension: Transaction Currency Date';
309
310 g_FolderNames (80) := 'Inventory Locator Dimension: Subinventory Locator';
311 g_FolderNames (81) := 'Unit of Measure Dimension: Transaction Unit of Measure';
312 --------- (used by ISC) g_FolderNames (82) := 'Set of Books Dimension: Set of Books';
313 g_FolderNames (83) := 'Supplier Item Dimension: Supplier Line Item Number';
314
315
316 g_EulOwner := UPPER(pEulOwnerName);
317 g_DiscoVersion := UPPER(pDiscoVersion);
318
319 /* Set Bus Area */
320 IF pDiscoVersion = 'DISCO3I' THEN
321 InitBusArea3i (pBusAreaName);
322 ELSIF pDiscoVersion = 'DISCO4I' THEN
323 InitBusArea4i (pBusAreaName);
324 ELSE
325 RETURN;
326 END IF;
327
328
329 IF pDiscoVersion = 'DISCO3I' THEN
330 l_index := g_FolderNames.FIRST;
331 LOOP
332 hideFolder3i (g_FolderNames (l_index));
333 EXIT WHEN l_index = g_FolderNames.LAST;
334 l_index := g_FolderNames.NEXT (l_index);
335 END LOOP;
336 ELSIF pDiscoVersion = 'DISCO4I' THEN
337 l_index := g_FolderNames.FIRST;
338 LOOP
339 hideFolder4i (g_FolderNames (l_index));
340 EXIT WHEN l_index = g_FolderNames.LAST;
341 l_index := g_FolderNames.NEXT (l_index);
342 END LOOP;
343 END IF;
344
345 /* Hide User Attributes */
346 IF pDiscoVersion = 'DISCO3I' THEN
347 hideUserAttributes3i;
348 ELSIF pDiscoVersion = 'DISCO4I' THEN
349 hideUserAttributes4i;
350 END IF;
351
352 ---------------------------------------------------------------
353
354 EXCEPTION
355
356 WHEN G_BUSINESS_AREA_DOES_NOT_EXIST THEN
357 edw_log.put_line('Business Area Name: ' || g_EulBA_Name || ' not found');
358 Errbuf := g_errbuf;
359 Retcode := g_retcode;
360 raise;
361
362 WHEN G_EUL_OWNER_DOES_NOT_EXIST THEN
366 raise;
363 edw_log.put_line('End User Layer (EUL) Owner: ' || g_EulOwner || ' not found OR wrong EUL version: ' || pDiscoVersion);
364 Errbuf := g_errbuf;
365 Retcode := g_retcode;
367
368 WHEN OTHERS THEN
369 edw_log.put_line('pEulOwnerName: '|| g_EulOwner);
370 edw_log.put_line('pBusAreaName: '|| pBusAreaName);
371 edw_log.put_line('l_stmt : ' || l_stmt);
372 Errbuf := g_errbuf;
373 Retcode := g_retcode;
374 raise;
375
376 END EulMain;
377
378 END POA_EUL_UTILS;