1: package body AK_CUSTOM_REGIONS_PKG as
2: /* $Header: AKDCRGNB.pls 120.3 2006/01/25 15:56:23 tshort noship $ */
3: procedure INSERT_ROW (
4: X_ROWID in out NOCOPY VARCHAR2,
5: X_CUSTOMIZATION_APPLICATION_ID in NUMBER,
15: X_LAST_UPDATED_BY in NUMBER,
16: X_LAST_UPDATE_DATE in DATE,
17: X_LAST_UPDATE_LOGIN in NUMBER
18: ) is
19: cursor C is select ROWID from AK_CUSTOM_REGIONS
20: where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
21: and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
22: and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
23: and REGION_CODE = X_REGION_CODE
22: and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
23: and REGION_CODE = X_REGION_CODE
24: and PROPERTY_NAME = X_PROPERTY_NAME;
25: begin
26: insert into AK_CUSTOM_REGIONS (
27: CUSTOMIZATION_APPLICATION_ID,
28: CUSTOMIZATION_CODE,
29: REGION_APPLICATION_ID,
30: REGION_CODE,
60: raise no_data_found;
61: end if;
62: close C;
63:
64: insert into AK_CUSTOM_REGIONS_TL (
65: CUSTOMIZATION_APPLICATION_ID,
66: CUSTOMIZATION_CODE,
67: REGION_APPLICATION_ID,
68: REGION_CODE,
92: from FND_LANGUAGES L
93: where L.INSTALLED_FLAG in ('I', 'B')
94: and not exists
95: (select NULL
96: from AK_CUSTOM_REGIONS_TL T
97: where T.CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
98: and T.CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
99: and T.REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
100: and T.REGION_CODE = X_REGION_CODE
125: PROPERTY_NAME,
126: PROPERTY_VARCHAR2_VALUE,
127: PROPERTY_NUMBER_VALUE,
128: CRITERIA_JOIN_CONDITION
129: from AK_CUSTOM_REGIONS
130: where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
131: and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
132: and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
133: and REGION_CODE = X_REGION_CODE
136: recinfo C%rowtype;
137:
138: cursor C1 is select
139: PROPERTY_VARCHAR2_VALUE
140: from AK_CUSTOM_REGIONS_TL
141: where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
142: and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
143: and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
144: and REGION_CODE = X_REGION_CODE
220: X_LAST_UPDATE_DATE in DATE,
221: X_LAST_UPDATE_LOGIN in NUMBER
222: ) is
223: begin
224: update AK_CUSTOM_REGIONS set
225: CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID,
226: CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE,
227: REGION_APPLICATION_ID = X_REGION_APPLICATION_ID,
228: REGION_CODE = X_REGION_CODE,
242: if (sql%notfound) then
243: raise no_data_found;
244: end if;
245:
246: update AK_CUSTOM_REGIONS_TL set
247: PROPERTY_VARCHAR2_VALUE = X_PROPERTY_VARCHAR2_VALUE,
248: LAST_UPDATED_BY = X_LAST_UPDATED_BY,
249: LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
250: LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
268: X_REGION_CODE in VARCHAR2,
269: X_PROPERTY_NAME in VARCHAR2
270: ) is
271: begin
272: delete from AK_CUSTOM_REGIONS
273: where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
274: and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
275: and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
276: and REGION_CODE = X_REGION_CODE
279: if (sql%notfound) then
280: raise no_data_found;
281: end if;
282:
283: delete from AK_CUSTOM_REGIONS_TL
284: where CUSTOMIZATION_APPLICATION_ID = X_CUSTOMIZATION_APPLICATION_ID
285: and CUSTOMIZATION_CODE = X_CUSTOMIZATION_CODE
286: and REGION_APPLICATION_ID = X_REGION_APPLICATION_ID
287: and REGION_CODE = X_REGION_CODE
300: /* as a quick workaround to fix the time-consuming table handler issue */
301: /* Eventually we'll need to turn them into a separate fix_language procedure */
302: /*
303:
304: delete from AK_CUSTOM_REGIONS_TL T
305: where not exists
306: (select NULL
307: from AK_CUSTOM_REGIONS B
308: where B.CUSTOMIZATION_APPLICATION_ID = T.CUSTOMIZATION_APPLICATION_ID
303:
304: delete from AK_CUSTOM_REGIONS_TL T
305: where not exists
306: (select NULL
307: from AK_CUSTOM_REGIONS B
308: where B.CUSTOMIZATION_APPLICATION_ID = T.CUSTOMIZATION_APPLICATION_ID
309: and B.CUSTOMIZATION_CODE = T.CUSTOMIZATION_CODE
310: and B.REGION_APPLICATION_ID = T.REGION_APPLICATION_ID
311: and B.REGION_CODE = T.REGION_CODE
311: and B.REGION_CODE = T.REGION_CODE
312: and B.PROPERTY_NAME = T.PROPERTY_NAME
313: );
314:
315: update AK_CUSTOM_REGIONS_TL T set (
316: PROPERTY_VARCHAR2_VALUE
317: ) = (select
318: B.PROPERTY_VARCHAR2_VALUE
319: from AK_CUSTOM_REGIONS_TL B
315: update AK_CUSTOM_REGIONS_TL T set (
316: PROPERTY_VARCHAR2_VALUE
317: ) = (select
318: B.PROPERTY_VARCHAR2_VALUE
319: from AK_CUSTOM_REGIONS_TL B
320: where B.CUSTOMIZATION_APPLICATION_ID = T.CUSTOMIZATION_APPLICATION_ID
321: and B.CUSTOMIZATION_CODE = T.CUSTOMIZATION_CODE
322: and B.REGION_APPLICATION_ID = T.REGION_APPLICATION_ID
323: and B.REGION_CODE = T.REGION_CODE
335: SUBT.REGION_APPLICATION_ID,
336: SUBT.REGION_CODE,
337: SUBT.PROPERTY_NAME,
338: SUBT.LANGUAGE
339: from AK_CUSTOM_REGIONS_TL SUBB,
340: AK_CUSTOM_REGIONS_TL SUBT
341: where SUBB.CUSTOMIZATION_APPLICATION_ID = SUBT.CUSTOMIZATION_APPLICATION_ID
342: and SUBB.CUSTOMIZATION_CODE = SUBT.CUSTOMIZATION_CODE
343: and SUBB.REGION_APPLICATION_ID = SUBT.REGION_APPLICATION_ID
336: SUBT.REGION_CODE,
337: SUBT.PROPERTY_NAME,
338: SUBT.LANGUAGE
339: from AK_CUSTOM_REGIONS_TL SUBB,
340: AK_CUSTOM_REGIONS_TL SUBT
341: where SUBB.CUSTOMIZATION_APPLICATION_ID = SUBT.CUSTOMIZATION_APPLICATION_ID
342: and SUBB.CUSTOMIZATION_CODE = SUBT.CUSTOMIZATION_CODE
343: and SUBB.REGION_APPLICATION_ID = SUBT.REGION_APPLICATION_ID
344: and SUBB.REGION_CODE = SUBT.REGION_CODE
350: );
351:
352: */
353:
354: insert /*+ append parallel(tt) */ into AK_CUSTOM_REGIONS_TL tt (
355: CUSTOMIZATION_APPLICATION_ID,
356: CUSTOMIZATION_CODE,
357: REGION_APPLICATION_ID,
358: REGION_CODE,
379: B.CREATION_DATE,
380: B.LAST_UPDATED_BY,
381: B.LAST_UPDATE_DATE,
382: B.LAST_UPDATE_LOGIN
383: from AK_CUSTOM_REGIONS_TL B,
384: FND_LANGUAGES L
385: where L.INSTALLED_FLAG in ('I', 'B')
386: and B.LANGUAGE = userenv('LANG')
387: ) v, AK_CUSTOM_REGIONS_TL T
383: from AK_CUSTOM_REGIONS_TL B,
384: FND_LANGUAGES L
385: where L.INSTALLED_FLAG in ('I', 'B')
386: and B.LANGUAGE = userenv('LANG')
387: ) v, AK_CUSTOM_REGIONS_TL T
388: where T.CUSTOMIZATION_APPLICATION_ID(+) = v.CUSTOMIZATION_APPLICATION_ID
389: and T.CUSTOMIZATION_CODE(+) = v.CUSTOMIZATION_CODE
390: and T.REGION_APPLICATION_ID(+) = v.REGION_APPLICATION_ID
391: and T.REGION_CODE(+) = v.REGION_CODE
398: and T.PROPERTY_NAME is NULL;
399:
400: end ADD_LANGUAGE;
401:
402: end AK_CUSTOM_REGIONS_PKG;