1 PACKAGE BODY EDW_ITEMS_PKG AS
2 /* $Header: ENIITEMB.pls 115.4 2004/01/30 21:44:36 sbag noship $ */
3
4 PROCEDURE initialize_parents;
5
6 TYPE parent_org_r_t IS RECORD (
7 parent_org NUMBER,
8 child_org NUMBER
9 );
10
11 TYPE parent_org_t_t is TABLE OF parent_org_r_t INDEX BY BINARY_INTEGER;
12 parent_org_t parent_org_t_t;
13
14 g_initialized number := 0;
15
16 Function Item_Org_FK(
17 p_inventory_item_id in NUMBER,
18 p_organization_id in NUMBER,
19 p_item_description in varchar2 DEFAULT null,
20 p_item_category in varchar2 DEFAULT null,
21 p_instance_code in varchar2 DEFAULT null)
22 return VARCHAR2 IS
23
24 l_instance_code edw_local_instance.instance_code%TYPE;
25 l_item_category mtl_categories_kfv.category_id%TYPE;
26
27 cursor c1 is
28 select instance_code
29 from edw_local_instance;
30
31 cursor c2 is
32 select category_id
33 from mtl_categories_kfv
34 where concatenated_segments = p_item_category;
35
36 BEGIN
37
38 IF p_organization_id = -1 THEN
39
40 return 'NA_EDW';
41
42 END IF;
43
44 OPEN c2;
45 FETCH c2 INTO l_item_category;
46 CLOSE c2;
47
48 if p_instance_code is null then
49
50 OPEN c1;
51 FETCH c1 INTO l_instance_code;
52 CLOSE c1;
53
54 else
55
56 l_instance_code := p_instance_code;
57
58 end if;
59
60 if p_inventory_item_id is null and p_item_description is not null
61 and p_organization_id is not null then
62
63 return (p_item_description||'-'||l_item_category||'-'||
64 p_organization_id||'-'||l_instance_code||'-ONETIME-IORG');
65
66 else
67
68 return (p_inventory_item_id||'-'||p_organization_id||'-'||l_instance_code||'-IORG');
69
70 end if;
71
72 EXCEPTION
73
74 when others then
75 if c1%ISOPEN then
76 CLOSE c1;
77 end if;
78
79 if c2%ISOPEN then
80 CLOSE c2;
81 end if;
82
83 END Item_Org_FK;
84
85 Function Item_Org_FK(
86 p_inventory_item_id in NUMBER,
87 p_organization_id in NUMBER,
88 p_item_description in varchar2 DEFAULT null,
89 p_item_category_id in NUMBER DEFAULT null,
90 p_instance_code in varchar2 DEFAULT null)
91 return VARCHAR2 IS
92
93 l_instance_code edw_local_instance.instance_code%TYPE;
94
95 cursor c1 is
96 select instance_code
97 from edw_local_instance;
98
99 BEGIN
100
101 if p_organization_id = -1 then
102
103 return 'NA_EDW';
104
105 end if;
106
107 if p_instance_code is null then
108
109 OPEN c1;
110 FETCH c1 INTO l_instance_code;
111 CLOSE c1;
112
113 else
114
115 l_instance_code := p_instance_code;
116
117 end if;
118
119 if p_inventory_item_id is null and p_item_description is not null
120 and p_organization_id is not null then
121
122 return (p_item_description||'-'||p_item_category_id||'-'||
123 p_organization_id||'-'||l_instance_code||'-ONETIME-IORG');
124
125 else
126
127 return (p_inventory_item_id||'-'||p_organization_id||'-'||l_instance_code||'-IORG');
128
129 end if;
130
131 EXCEPTION
132
133 when others then
134 if c1%ISOPEN then
135 CLOSE c1;
136 end if;
137
138 END Item_Org_FK;
139
140
141 Function Item_Rev_FK(
142 p_inventory_item_id in NUMBER,
143 p_organization_id in NUMBER,
144 p_revision in VARCHAR2,
145 p_instance_code in VARCHAR2 := null)
146 return VARCHAR2 IS
147
148 l_instance_code edw_local_instance.instance_code%TYPE;
149
150 cursor c1 is
151 select instance_code
152 from edw_local_instance;
153
154 BEGIN
155
156 if p_instance_code is null then
157
158 OPEN c1;
159 FETCH c1 INTO l_instance_code;
160 CLOSE c1;
161
162 return (p_revision||'-'||p_inventory_item_id||'-'||p_organization_id||'-'||l_instance_code);
163
164 else
165
166 return (p_revision||'-'||p_inventory_item_id||'-'||p_organization_id||'-'||p_instance_code);
167
168 end if;
169
170
171 EXCEPTION
172
173 when others then
174 if c1%ISOPEN then
175 CLOSE c1;
176 end if;
177 END Item_Rev_FK;
178
179 FUNCTION category_fk(
180 p_functional_area IN NUMBER,
181 p_control IN NUMBER,
182 p_category_id IN NUMBER,
183 p_instance_code IN VARCHAR2 DEFAULT NULL)
184 RETURN VARCHAR2 IS
185
186 l_instance_code edw_local_instance.instance_code%TYPE;
187
188 CURSOR c1 IS
189 SELECT instance_code
190 FROM edw_local_instance;
191
192 BEGIN
193
194 IF p_instance_code IS NULL THEN
195
196 OPEN c1;
197 FETCH c1 INTO l_instance_code;
198 CLOSE c1;
199 ELSE /* Bugfix for 3289525 */
200 l_instance_code := p_instance_code;
201 END IF;
202
203 RETURN(to_char(p_control)||'-'||to_char(p_category_id) || '-' || l_instance_code || '-PCAT');
204
205 EXCEPTION
206
207 WHEN OTHERS THEN
208
209 IF c1%ISOPEN THEN
210 CLOSE c1;
211 END IF;
212
213 RAISE;
214
215 END Category_FK;
216
217
218 FUNCTION GET_PROD_GRP_FK(
219 p_item_id in NUMBER,
220 p_organization_id in NUMBER,
221 p_instance_code in VARCHAR2 )
222 RETURN VARCHAR2 IS
223
224 CURSOR c_get_ids IS
225 SELECT TO_NUMBER(MC.SEGMENT1) INTEREST_TYPE_ID,
226 TO_NUMBER(MC.SEGMENT2) PRIMARY_INTEREST_CODE_ID,
227 TO_NUMBER(MC.SEGMENT3) SECONDARY_INTEREST_CODE_ID
228 FROM MTL_ITEM_CATEGORIES MIC,
229 MTL_CATEGORIES_B MC,
230 FND_ID_FLEX_STRUCTURES FIFS
231 WHERE FIFS.ID_FLEX_CODE = 'MCAT'
232 AND FIFS.APPLICATION_ID = 401
233 AND FIFS.ID_FLEX_STRUCTURE_CODE = 'SALES_CATEGORIES'
234 AND MC.STRUCTURE_ID = FIFS.ID_FLEX_NUM
235 AND MIC.CATEGORY_SET_ID = 5
236 AND MIC.CATEGORY_ID = MC.CATEGORY_ID
237 AND MIC.INVENTORY_ITEM_ID = p_item_id
238 AND MIC.ORGANIZATION_ID = p_organization_id ;
239
240 v_int_type_id NUMBER := NULL;
241 v_prim_code_id NUMBER := NULL;
242 v_secn_code_id NUMBER := NULL;
243 v_secn_code_fk VARCHAR2(240) := 'NA_EDW';
244 v_instance_code VARCHAR2(240) := 'NA_EDW' ;
245
246 BEGIN
247 IF (p_item_id IS NOT NULL and
248 p_organization_id IS NOT NULL )
249 THEN
250 OPEN c_get_ids;
251 FETCH c_get_ids INTO v_int_type_id, v_prim_code_id,
252 v_secn_code_id ;
253 CLOSE c_get_ids;
254 IF v_secn_code_id IS NOT NULL
255 THEN
256 v_secn_code_fk := v_secn_code_id || '-' || p_instance_code
257 || '-SECN_CODE' ;
258 ELSIF v_prim_code_id IS NOT NULL
259 THEN
260 v_secn_code_fk := v_prim_code_id || '-' || p_instance_code
261 || '-PRIM_CODE-PCTG' ;
262 ELSIF v_int_type_id IS NOT NULL
263 THEN
264 v_secn_code_fk := v_int_type_id || '-' || p_instance_code ||
265 '-INTR_TYPE-PLIN' ;
266 END IF;
267 END IF;
268
269 RETURN(v_secn_code_fk);
270 EXCEPTION
271 WHEN OTHERS THEN
272 raise;
273 END GET_PROD_GRP_FK;
274
275 FUNCTION GET_ITEM_FK(
276 p_item_id in NUMBER,
277 p_inv_org_id in NUMBER,
278 p_interest_type_id in NUMBER,
279 p_primary_code_id in NUMBER,
280 p_secondary_code_id in NUMBER,
281 p_instance_code in VARCHAR2 )
282 RETURN VARCHAR2 IS
283
284 v_item_fk VARCHAR2(240) := 'NA_EDW';
285
286 BEGIN
287
288 IF (p_item_id IS NOT NULL and
289 p_inv_org_id IS NOT NULL )
290 THEN
291 v_item_fk := p_item_id || '-' || p_inv_org_id || '-' ||
292 p_instance_code || '-IORG' ;
293 ELSIF p_secondary_code_id IS NOT NULL
294 THEN
295 v_item_fk := p_secondary_code_id || '-' || p_instance_code
296 || '-SECN_CODE-PGRP';
297 ELSIF p_primary_code_id IS NOT NULL
298 THEN
299 v_item_fk := p_primary_code_id || '-' || p_instance_code ||
300 '-PRIM_CODE-PCTG' ;
301 ELSIF p_interest_type_id IS NOT NULL
302 THEN
303 v_item_fk := p_interest_type_id || '-' || p_instance_code ||
304 '-INTR_TYPE-PLIN' ;
305 END IF;
306
307 RETURN(v_item_fk);
308 EXCEPTION
309 WHEN OTHERS THEN
310 raise;
311 END GET_ITEM_FK;
312
313 PROCEDURE INITIALIZE_PARENTS IS
314
315 CURSOR parent_organizations_c IS
316 SELECT organization_id, master_organization_id
317 FROM mtl_parameters;
318
319 parent_organizations_r parent_organizations_c%ROWTYPE;
320
321 l_parent parent_org_r_t;
322
323 BEGIN
324
325 OPEN parent_organizations_c;
326
327 LOOP
328
329 FETCH parent_organizations_c INTO parent_organizations_r;
330
331 EXIT WHEN parent_organizations_c%NOTFOUND;
332
333 l_parent.parent_org := parent_organizations_r.master_organization_id;
334 l_parent.child_org := parent_organizations_r.organization_id;
335
336 parent_org_t(parent_organizations_r.organization_id) := l_parent;
337
338 IF parent_organizations_r.organization_id = parent_organizations_r.master_organization_id THEN
339
340 parent_org_t(parent_organizations_r.organization_id).parent_org := NULL;
341
342 END IF;
343
344 END LOOP;
345
346 CLOSE parent_organizations_c;
347
348 EXCEPTION
349
350 WHEN OTHERS THEN
351 IF parent_organizations_c%ISOPEN THEN
352 CLOSE parent_organizations_c;
353 END IF;
354
355 END INITIALIZE_PARENTS;
356
357 FUNCTION GET_MASTER_PARENT(
358 p_organization_id IN NUMBER) RETURN NUMBER
359 IS
360
361 l_current_parent NUMBER := 0;
362
363 BEGIN
364
365 IF g_initialized = 0 THEN
366
367 initialize_parents;
368 g_initialized := 1;
369
370 END IF;
371
372 l_current_parent := parent_org_t(p_organization_id).parent_org;
373
374 IF l_current_parent IS NULL THEN
375
376 RETURN p_organization_id;
377
378 ELSE
379
380 l_current_parent := get_master_parent(l_current_parent);
381
382 END IF;
383
384 RETURN l_current_parent;
385
386 EXCEPTION
387
388 WHEN NO_DATA_FOUND THEN
389 RETURN 0;
390 WHEN OTHERS THEN NULL;
391
392 END GET_MASTER_PARENT;
393
394 END EDW_ITEMS_PKG;
[Home] [Help]