[Home] [Help]
PACKAGE BODY: APPS.WF_WL_RESULT_ATTRIBUTES_PKG
Source
1 package body WF_WL_RESULT_ATTRIBUTES_PKG as
2 /* $Header: wfwlratb.pls 120.0.12020000.3 2013/05/15 21:28:12 alsosa noship $ */
3 procedure INSERT_RESULT_ATTRIBUTE (X_MESSAGE_TYPE in VARCHAR2,
4 X_MESSAGE_NAME in VARCHAR2,
5 X_CATEGORY in VARCHAR2,
6 X_LOOKUP_TYPE in VARCHAR2,
7 X_LOOKUP_CODE in VARCHAR2,
8 X_LABEL in VARCHAR2,
9 X_OWNER in VARCHAR2,
10 X_LAST_UPDATE_DATE in VARCHAR2)
11 is
12 l_row_id varchar2(50);
13 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
14 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
15 cursor c_newrow is
16 select ROWID from WF_WL_RESULT_ATTRIBUTES
17 where MESSAGE_TYPE = X_MESSAGE_TYPE and
18 MESSAGE_NAME = X_MESSAGE_NAME and
19 CATEGORY = X_CATEGORY;
20 begin
21 insert into WF_WL_RESULT_ATTRIBUTES (MESSAGE_TYPE,
22 MESSAGE_NAME,
23 CATEGORY ,
24 LOOKUP_TYPE ,
25 LOOKUP_CODE ,
26 CREATION_DATE,
27 CREATED_BY,
28 LAST_UPDATE_DATE,
29 LAST_UPDATED_BY,
30 LAST_UPDATE_LOGIN,
31 SECURITY_GROUP_ID)
32 values (X_MESSAGE_TYPE,
33 X_MESSAGE_NAME,
34 X_CATEGORY ,
35 X_LOOKUP_TYPE ,
36 X_LOOKUP_CODE ,
37 l_ludate, --CREATION_DATE,
38 l_luby, --CREATED_BY,
39 l_ludate, --LAST_UPDATE_DATE,
40 l_luby, --LAST_UPDATED_BY,
41 0, --LAST_UPDATE_LOGIN,
42 FND_GLOBAL.SECURITY_GROUP_ID);
43
44 insert into WF_WL_RESULT_ATTRIBUTES_TL (MESSAGE_TYPE,
45 MESSAGE_NAME,
46 CATEGORY ,
47 LABEL ,
48 LANGUAGE ,
49 SOURCE_LANG ,
50 CREATION_DATE,
51 CREATED_BY,
52 LAST_UPDATE_DATE,
53 LAST_UPDATED_BY,
54 LAST_UPDATE_LOGIN,
55 SECURITY_GROUP_ID)
56 select X_MESSAGE_TYPE,
57 X_MESSAGE_NAME,
58 X_CATEGORY,
59 X_LABEL,
60 L.LANGUAGE_CODE,
61 userenv('LANG'),
62 l_ludate, --CREATION_DATE,
63 l_luby, --CREATED_BY,
64 l_ludate, --LAST_UPDATE_DATE,
65 l_luby, --LAST_UPDATED_BY,
66 0, --LAST_UPDATE_LOGIN,
67 FND_GLOBAL.SECURITY_GROUP_ID
68 from FND_LANGUAGES L
69 where L.INSTALLED_FLAG in ('I', 'B')
70 and not exists
71 (select NULL
72 from WF_WL_RESULT_ATTRIBUTES_TL TL
73 where TL.MESSAGE_TYPE = X_MESSAGE_TYPE and
74 TL.MESSAGE_NAME = X_MESSAGE_NAME and
75 TL.CATEGORY = X_CATEGORY and
76 TL.LANGUAGE = L.LANGUAGE_CODE);
77
78 open c_newrow;
79 fetch c_newrow into l_row_id;
80 if (c_newrow%notfound) then
81 close c_newrow;
82 raise_application_error (-20000, 'WF_WL_RESULT_ATTRIBUTES_PKG.INSERT_RESULT_ATTRIBUTE: unable to create attribute'||X_CATEGORY||' for '||X_MESSAGE_NAME);
83 end if;
84 close c_newrow;
85 end INSERT_RESULT_ATTRIBUTE;
86
87 procedure UPDATE_RESULT_ATTRIBUTE (X_MESSAGE_TYPE in VARCHAR2,
88 X_MESSAGE_NAME in VARCHAR2,
89 X_CATEGORY in VARCHAR2,
90 X_LOOKUP_TYPE in VARCHAR2,
91 X_LOOKUP_CODE in VARCHAR2,
92 X_LABEL in VARCHAR2,
93 X_OWNER IN VARCHAR2,
94 X_LAST_UPDATE_DATE IN VARCHAR2)
95 is
96 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
97 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
98 begin
99 update WF_WL_RESULT_ATTRIBUTES B
100 set B.LOOKUP_TYPE = X_LOOKUP_TYPE,
101 B.LOOKUP_CODE = X_LOOKUP_CODE,
102 B.LAST_UPDATE_DATE = l_ludate,
103 B.LAST_UPDATED_BY = l_luby,
104 B.LAST_UPDATE_LOGIN = 0
105 where MESSAGE_TYPE = X_MESSAGE_TYPE and
106 MESSAGE_NAME = X_MESSAGE_NAME and
107 CATEGORY = X_CATEGORY;
108
109 if (sql%rowcount=0) then
110 --The new record does not exist, thus create it
111 INSERT_RESULT_ATTRIBUTE (X_MESSAGE_TYPE => UPDATE_RESULT_ATTRIBUTE.X_MESSAGE_TYPE,
112 X_MESSAGE_NAME => UPDATE_RESULT_ATTRIBUTE.X_MESSAGE_NAME,
113 X_CATEGORY => UPDATE_RESULT_ATTRIBUTE.X_CATEGORY ,
114 X_LOOKUP_TYPE => UPDATE_RESULT_ATTRIBUTE.X_LOOKUP_TYPE ,
115 X_LOOKUP_CODE => UPDATE_RESULT_ATTRIBUTE.X_LOOKUP_CODE ,
116 X_LABEL => UPDATE_RESULT_ATTRIBUTE.X_LABEL ,
117 X_OWNER => UPDATE_RESULT_ATTRIBUTE.X_OWNER ,
118 X_LAST_UPDATE_DATE => UPDATE_RESULT_ATTRIBUTE.X_LAST_UPDATE_DATE);
119 else
120 TRANSLATE_RESULT_ATTRIBUTE (X_MESSAGE_TYPE => UPDATE_RESULT_ATTRIBUTE.X_MESSAGE_TYPE,
121 X_MESSAGE_NAME => UPDATE_RESULT_ATTRIBUTE.X_MESSAGE_NAME,
122 X_CATEGORY => UPDATE_RESULT_ATTRIBUTE.X_CATEGORY ,
123 X_LABEL => UPDATE_RESULT_ATTRIBUTE.X_LABEL ,
124 X_OWNER => UPDATE_RESULT_ATTRIBUTE.X_OWNER ,
125 X_LAST_UPDATE_DATE => UPDATE_RESULT_ATTRIBUTE.X_LAST_UPDATE_DATE);
126 end if;
127 end UPDATE_RESULT_ATTRIBUTE;
128
129 procedure ADD_LANGUAGE is
130 begin
131 insert into WF_WL_RESULT_ATTRIBUTES_TL (MESSAGE_TYPE,
132 MESSAGE_NAME,
133 CATEGORY ,
134 LABEL ,
135 LANGUAGE ,
136 SOURCE_LANG ,
137 CREATION_DATE,
138 CREATED_BY,
139 LAST_UPDATE_DATE,
140 LAST_UPDATED_BY,
141 LAST_UPDATE_LOGIN,
142 SECURITY_GROUP_ID)
143 select B.MESSAGE_TYPE,
144 B.MESSAGE_NAME,
145 B.CATEGORY ,
146 B.LABEL ,
147 L.LANGUAGE_CODE,
148 B.SOURCE_LANG ,
149 B.CREATION_DATE,
150 B.CREATED_BY,
151 B.LAST_UPDATE_DATE,
152 B.LAST_UPDATED_BY,
153 B.LAST_UPDATE_LOGIN,
154 B.SECURITY_GROUP_ID
155 from WF_WL_RESULT_ATTRIBUTES_TL B, FND_LANGUAGES L
156 where L.INSTALLED_FLAG in ('I', 'B')
157 and B.LANGUAGE = userenv('LANG')
158 and not exists
159 (select NULL
160 from WF_WL_RESULT_ATTRIBUTES_TL TL
161 where TL.MESSAGE_TYPE = B.MESSAGE_TYPE and
162 TL.MESSAGE_NAME = B.MESSAGE_NAME and
163 TL.CATEGORY = B.CATEGORY and
164 TL.LANGUAGE = L.LANGUAGE_CODE);
165 exception
166 when others then
167 raise_application_error (-20000, 'WF_WL_RESULT_ATTRIBUTES_PKG.ADD_LANGUAGE: unable to add '||userenv('LANG')||' translation due to error '||sqlcode);
168 end ADD_LANGUAGE;
169
170 procedure TRANSLATE_RESULT_ATTRIBUTE (X_MESSAGE_TYPE in VARCHAR2,
171 X_MESSAGE_NAME in VARCHAR2,
172 X_CATEGORY in VARCHAR2,
173 X_LABEL in VARCHAR2,
174 X_OWNER IN VARCHAR2,
175 X_LAST_UPDATE_DATE IN VARCHAR2)
176 is
177 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
178 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
179 begin
180 update WF_WL_RESULT_ATTRIBUTES_TL TL
181 set TL.LABEL = X_LABEL,
182 TL.SOURCE_LANG = userenv('LANG'),
183 TL.LAST_UPDATE_DATE = l_ludate,
184 TL.LAST_UPDATED_BY = l_luby,
185 TL.LAST_UPDATE_LOGIN = 0
186 where TL.MESSAGE_TYPE = X_MESSAGE_TYPE and
187 TL.MESSAGE_NAME = X_MESSAGE_NAME and
188 TL.CATEGORY = X_CATEGORY and
189 userenv('LANG') in (TL.LANGUAGE, TL.SOURCE_LANG);
190 if (sql%rowcount=0) then
191 raise_application_error (-20000, 'WF_WL_RESULT_ATTRIBUTES_PKG.TRANSLATE_RESULT_ATTRIBUTE: unable to update attribute'
192 ||X_CATEGORY||' for '||X_MESSAGE_NAME||'. Ensure base language is uploaded first');
193 end if;
194 end TRANSLATE_RESULT_ATTRIBUTE;
195 end WF_WL_RESULT_ATTRIBUTES_PKG;