[Home] [Help]
PACKAGE BODY: APPS.WF_WL_RULES_PKG
Source
1 package body WF_WL_RULES_PKG as
2 /* $Header: wfwlrulb.pls 120.0.12020000.5 2013/05/15 21:29:38 alsosa noship $ */
3
4 function FIND_RULE_ID (X_KEY in VARCHAR2) return number is
5 l_rule_id number;
6 begin
7 select RULE_ID into l_rule_id
8 from WF_WL_RULES
9 where RULE_KEY=X_KEY;
10 return l_rule_id;
11 exception
12 when others then
13 raise_application_error (-20000, 'WF_WL_RULES_PKG.FIND_RULE_ID: unable to find rule key '||X_KEY);
14 end FIND_RULE_ID;
15
16
17 procedure INSERT_RULE_BY_KEY (X_RULE_KEY in VARCHAR2,
18 X_RULE_NAME in VARCHAR2,
19 X_RULE_TYPE in VARCHAR2,
20 --X_ITEM_TYPE in VARCHAR2,
21 X_CRITERION in VARCHAR2,
22 X_OPERATOR in VARCHAR2,
23 X_OPERAND in VARCHAR2,
24 X_OWNER IN VARCHAR2,
25 X_LAST_UPDATE_DATE IN VARCHAR2)
26 is
27 --cursor c_newrow is select ROWID from WF_WL_RULES
28 cursor c_newrow is select ROWID from WF_WL_RULES
29 where RULE_KEY = X_RULE_KEY;
30 l_rowid VARCHAR2(30);
31 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
32 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
33 begin
34 insert into WF_WL_RULES (RULE_ID,
35 RULE_KEY,
36 RULE_TYPE,
37 --ITEM_TYPE,
38 CRITERION,
39 OPERATOR,
40 OPERAND,
41 CREATION_DATE,
42 CREATED_BY,
43 LAST_UPDATE_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_LOGIN,
46 SECURITY_GROUP_ID
47 )
48 values (WF_WL_RULES_S.NEXTVAL,
49 X_RULE_KEY,
50 X_RULE_TYPE,
51 --X_ITEM_TYPE,
52 X_CRITERION,
53 X_OPERATOR,
54 X_OPERAND,
55 l_ludate, --CREATION_DATE,
56 l_luby, --CREATED_BY,
57 l_ludate, --LAST_UPDATE_DATE,
58 l_luby, --LAST_UPDATED_BY,
59 0, --LAST_UPDATE_LOGIN,
60 FND_GLOBAL.SECURITY_GROUP_ID); --LAST_UPDATE_LOGIN,
61
62 insert into WF_WL_RULES_TL (RULE_ID,
63 RULE_NAME,
64 LANGUAGE,
65 SOURCE_LANG,
66 CREATION_DATE,
67 CREATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATED_BY,
70 LAST_UPDATE_LOGIN,
71 SECURITY_GROUP_ID)
72 select WF_WL_RULES_S.CURRVAL,
73 X_RULE_NAME,
74 L.LANGUAGE_CODE,
75 userenv('LANG'),
76 l_ludate, --CREATION_DATE,
77 l_luby, --CREATED_BY,
78 l_ludate, --LAST_UPDATE_DATE,
79 l_luby, --LAST_UPDATED_BY,
80 0, --LAST_UPDATE_LOGIN,
81 FND_GLOBAL.SECURITY_GROUP_ID
82 from FND_LANGUAGES L
83 where L.INSTALLED_FLAG in ('I', 'B')
84 and not exists
85 (select NULL
86 from WF_WL_RULES_TL TL, WF_WL_RULES B
87 where B.RULE_ID = TL.RULE_ID and
88 B.RULE_KEY = X_RULE_KEY and
89 TL.LANGUAGE = L.LANGUAGE_CODE);
90
91 open c_newrow;
92 fetch c_newrow into l_rowid;
93 if (c_newrow%notfound) then
94 close c_newrow;
95 raise_application_error (-20000, 'WF_WL_RULES_PKG.INSERT_RULE_BY_KEY: unable to create new rule '||X_RULE_KEY);
96 end if;
97 close c_newrow;
98 end INSERT_RULE_BY_KEY;
99
100 procedure ADD_LANGUAGE is
101 begin
102 insert into WF_WL_RULES_TL (RULE_ID,
103 RULE_NAME,
104 LANGUAGE,
105 SOURCE_LANG,
106 CREATION_DATE,
107 CREATED_BY,
108 LAST_UPDATE_DATE,
109 LAST_UPDATED_BY,
110 LAST_UPDATE_LOGIN,
111 SECURITY_GROUP_ID)
112 select B.RULE_ID,
113 B.RULE_NAME,
114 L.LANGUAGE_CODE,
115 B.SOURCE_LANG,
116 B.CREATION_DATE,
117 B.CREATED_BY,
118 B.LAST_UPDATE_DATE,
119 B.LAST_UPDATED_BY,
120 B.LAST_UPDATE_LOGIN,
121 B.SECURITY_GROUP_ID
122 from WF_WL_RULES_TL B, FND_LANGUAGES L
123 where L.INSTALLED_FLAG in ('I', 'B')
124 and B.LANGUAGE=userenv('LANG')
125 and not exists
126 (select NULL
127 from WF_WL_RULES_TL TL
128 where B.RULE_ID = TL.RULE_ID and
129 TL.LANGUAGE = L.LANGUAGE_CODE);
130 exception
131 when others then
132 raise_application_error (-20000, 'WF_WL_RULES_PKG.ADD_LANGUAGE: unable to add '||userenv('LANG')||' translation due to error '||sqlcode);
133 end ADD_LANGUAGE;
134
135 procedure TRANSLATE_RULE_BY_KEY (X_RULE_KEY in VARCHAR2,
136 X_RULE_NAME in VARCHAR2,
137 X_OWNER IN VARCHAR2,
138 X_LAST_UPDATE_DATE IN VARCHAR2) is
139 l_rule_id number := FIND_RULE_ID(X_RULE_KEY);
140 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
141 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
142 BEGIN
143 UPDATE WF_WL_RULES_TL TL
144 SET TL.RULE_NAME = X_RULE_NAME,
145 TL.SOURCE_LANG = userenv('LANG'),
146 TL.LAST_UPDATE_DATE = l_ludate,
147 TL.LAST_UPDATED_BY = l_luby,
148 TL.LAST_UPDATE_LOGIN = 0
149 WHERE RULE_ID = l_rule_id and
150 userenv('LANG') in (LANGUAGE,SOURCE_LANG);
151 IF (sql%rowcount=0) THEN
152 raise_application_error (-20000, 'WF_WL_RULES_PKG.TRANSLATE_RULE_BY_KEY: unable to find rule '
153 ||X_RULE_KEY||'. Ensure base language is uploaded first');
154 END IF;
155 end TRANSLATE_RULE_BY_KEY;
156
157 procedure UPDATE_RULE_BY_KEY (X_RULE_KEY in VARCHAR2,
158 X_RULE_NAME in VARCHAR2,
159 X_RULE_TYPE in VARCHAR2,
160 --X_ITEM_TYPE in VARCHAR2,
161 X_CRITERION in VARCHAR2,
162 X_OPERATOR in VARCHAR2,
163 X_OPERAND in VARCHAR2,
164 X_OWNER IN VARCHAR2,
165 X_LAST_UPDATE_DATE IN VARCHAR2)
166 is
167 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
168 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
169 begin
170 update WF_WL_RULES B
171 set --ITEM_TYPE = X_ITEM_TYPE,
172 RULE_TYPE = X_RULE_TYPE,
173 CRITERION = X_CRITERION,
174 OPERATOR = X_OPERATOR,
175 OPERAND = X_OPERAND,
176 LAST_UPDATE_DATE = l_ludate,
177 LAST_UPDATED_BY = l_luby,
178 LAST_UPDATE_LOGIN = 0
179 where B.RULE_KEY = X_RULE_KEY;
180
181 if (sql%rowcount=0) then
182 INSERT_RULE_BY_KEY (X_RULE_KEY => UPDATE_RULE_BY_KEY.X_RULE_KEY,
183 X_RULE_NAME => UPDATE_RULE_BY_KEY.X_RULE_NAME,
184 X_RULE_TYPE => UPDATE_RULE_BY_KEY.X_RULE_TYPE,
185 --X_ITEM_TYPE => UPDATE_RULE_BY_KEY.X_ITEM_TYPE,
186 X_CRITERION => UPDATE_RULE_BY_KEY.X_CRITERION,
187 X_OPERATOR => UPDATE_RULE_BY_KEY.X_OPERATOR,
188 X_OPERAND => UPDATE_RULE_BY_KEY.X_OPERAND,
189 X_OWNER => UPDATE_RULE_BY_KEY.X_OWNER,
190 X_LAST_UPDATE_DATE => UPDATE_RULE_BY_KEY.X_LAST_UPDATE_DATE);
191 else
192 TRANSLATE_RULE_BY_KEY (X_RULE_KEY => UPDATE_RULE_BY_KEY.X_RULE_KEY ,
193 X_RULE_NAME => UPDATE_RULE_BY_KEY.X_RULE_NAME,
194 X_OWNER => UPDATE_RULE_BY_KEY.X_OWNER,
195 X_LAST_UPDATE_DATE => UPDATE_RULE_BY_KEY.X_LAST_UPDATE_DATE);
196 end if;
197 end UPDATE_RULE_BY_KEY;
198
199 procedure INSERT_REGION_RULE_BY_KEY (X_REGION_KEY in VARCHAR2,
200 X_RULE_KEY in VARCHAR2,
201 X_OWNER IN VARCHAR2,
202 X_LAST_UPDATE_DATE IN VARCHAR2) is
203 l_region_id number := WF_WL_REGIONS_PKG.FIND_REGION_ID(X_REGION_KEY);
204 l_rule_id number := WF_WL_RULES_PKG.FIND_RULE_ID(X_RULE_KEY);
205 l_luby number := FND_LOAD_UTIL.OWNER_ID(X_OWNER);
206 l_ludate date := nvl(to_date(X_LAST_UPDATE_DATE, 'YYYY/MM/DD'), sysdate);
207 begin
208 begin
209 insert into WF_WL_REGION_RULES (REGION_ID,
210 RULE_ID,
211 CREATION_DATE,
212 CREATED_BY,
213 LAST_UPDATE_DATE,
214 LAST_UPDATED_BY,
215 LAST_UPDATE_LOGIN,
216 SECURITY_GROUP_ID)
217 values (l_region_id,
218 l_rule_id,
219 l_ludate, --CREATION_DATE,
220 l_luby, --CREATED_BY,
221 l_ludate, --LAST_UPDATE_DATE,
222 l_luby, --LAST_UPDATED_BY,
223 0, --LAST_UPDATE_LOGIN,
224 FND_GLOBAL.SECURITY_GROUP_ID); --LAST_UPDATE_LOGIN,
225 exception
226 when DUP_VAL_ON_INDEX then
227 null; --The combination already exists. Nothing else to do
228 when others then
229 raise_application_error (-20000, 'WF_WL_RULES_PKG.INSERT_REGION_RULE_BY_KEY: unable to create region rule '||X_REGION_KEY||'/'||X_RULE_KEY);
230 end;
231 end INSERT_REGION_RULE_BY_KEY;
232 end WF_WL_RULES_PKG;