1 package body CR_RSRC_MST_PKG as
2 /* $Header: GMPRSMSB.pls 120.4.12020000.1 2012/06/27 15:08:49 appldev ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_RESOURCES in VARCHAR2,
6 X_RESOURCE_CLASS in VARCHAR2,
7 X_TRANS_CNT in NUMBER,
8 X_DELETE_MARK in NUMBER,
9 X_TEXT_CODE in NUMBER,
10 X_MIN_CAPACITY in NUMBER,
11 X_MAX_CAPACITY in NUMBER,
12 X_CAPACITY_CONSTRAINT in NUMBER,
13 X_CAPACITY_UOM in VARCHAR2,
14 X_STD_USAGE_UM in VARCHAR2,
15 X_COST_CMPNTCLS_ID in NUMBER,
16 X_RESOURCE_DESC in VARCHAR2,
17 X_CREATION_DATE in DATE,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATE_DATE in DATE,
20 X_LAST_UPDATED_BY in NUMBER,
21 X_LAST_UPDATE_LOGIN in NUMBER,
22 X_CAPACITY_TOLERANCE in NUMBER,
23 X_UTILIZATION in NUMBER,
24 X_EFFICIENCY in NUMBER
25 ) is
26 cursor C is select ROWID from CR_RSRC_MST_B
27 where RESOURCES = X_RESOURCES
28 ;
29 begin
30 insert into CR_RSRC_MST_B (
31 RESOURCE_CLASS,
32 TRANS_CNT,
33 DELETE_MARK,
34 TEXT_CODE,
35 MIN_CAPACITY,
36 MAX_CAPACITY,
37 CAPACITY_CONSTRAINT,
38 CAPACITY_UM, /*sowsubra - resource model changes*/
39 RESOURCES,
40 STD_USAGE_UOM, /*sowsubra - resource model changes*/
41 COST_CMPNTCLS_ID,
42 CREATION_DATE,
43 CREATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATED_BY,
46 LAST_UPDATE_LOGIN,
47 CAPACITY_TOLERANCE,
48 UTILIZATION,
49 EFFICIENCY
50 ) values (
51 X_RESOURCE_CLASS,
52 X_TRANS_CNT,
53 X_DELETE_MARK,
54 X_TEXT_CODE,
55 X_MIN_CAPACITY,
56 X_MAX_CAPACITY,
57 X_CAPACITY_CONSTRAINT,
58 X_CAPACITY_UOM,
59 X_RESOURCES,
60 X_STD_USAGE_UM,
61 X_COST_CMPNTCLS_ID,
62 X_CREATION_DATE,
63 X_CREATED_BY,
64 X_LAST_UPDATE_DATE,
65 X_LAST_UPDATED_BY,
66 X_LAST_UPDATE_LOGIN,
67 X_CAPACITY_TOLERANCE,
68 X_UTILIZATION,
69 X_EFFICIENCY
70 );
71
72 insert into CR_RSRC_MST_TL (
73 RESOURCES,
74 RESOURCE_DESC,
75 CREATION_DATE,
76 CREATED_BY,
77 LAST_UPDATE_DATE,
78 LAST_UPDATED_BY,
79 LAST_UPDATE_LOGIN,
80 LANGUAGE,
81 SOURCE_LANG
82 ) select
83 X_RESOURCES,
84 X_RESOURCE_DESC,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_LAST_UPDATE_DATE,
88 X_LAST_UPDATED_BY,
89 X_LAST_UPDATE_LOGIN,
90 L.LANGUAGE_CODE,
91 userenv('LANG')
92 from FND_LANGUAGES L
93 where L.INSTALLED_FLAG in ('I', 'B')
94 and not exists
95 (select NULL
96 from CR_RSRC_MST_TL T
97 where T.RESOURCES = X_RESOURCES
98 and T.LANGUAGE = L.LANGUAGE_CODE);
99
100 open c;
101 fetch c into X_ROWID;
102 if (c%notfound) then
103 close c;
104 raise no_data_found;
105 end if;
106 close c;
107
108 end INSERT_ROW;
109
110 procedure LOCK_ROW (
111 X_RESOURCES in VARCHAR2,
112 X_RESOURCE_CLASS in VARCHAR2,
113 X_TRANS_CNT in NUMBER,
114 X_DELETE_MARK in NUMBER,
115 X_TEXT_CODE in NUMBER,
116 X_MIN_CAPACITY in NUMBER,
117 X_MAX_CAPACITY in NUMBER,
118 X_CAPACITY_CONSTRAINT in NUMBER,
119 X_CAPACITY_UOM in VARCHAR2,
120 X_STD_USAGE_UM in VARCHAR2,
121 X_COST_CMPNTCLS_ID in NUMBER,
122 X_RESOURCE_DESC in VARCHAR2,
123 X_CAPACITY_TOLERANCE in NUMBER,
124 X_UTILIZATION in NUMBER,
125 X_EFFICIENCY in NUMBER
126 ) is
127 cursor c is select
128 RESOURCE_CLASS,
129 TRANS_CNT,
130 DELETE_MARK,
131 TEXT_CODE,
132 MIN_CAPACITY,
133 MAX_CAPACITY,
134 CAPACITY_CONSTRAINT,
135 CAPACITY_UM,/*sowsubra - resource model changes*/
136 STD_USAGE_UOM, /*sowsubra - resource model changes*/
137 COST_CMPNTCLS_ID,
138 CAPACITY_TOLERANCE,
139 UTILIZATION,
140 EFFICIENCY
141 from CR_RSRC_MST_B
142 where RESOURCES = X_RESOURCES
143 for update of RESOURCES nowait;
144 recinfo c%rowtype;
145
146 cursor c1 is select
147 RESOURCE_DESC,
148 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
149 from CR_RSRC_MST_TL
150 where RESOURCES = X_RESOURCES
151 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
152 for update of RESOURCES nowait;
153 begin
154 open c;
155 fetch c into recinfo;
156 if (c%notfound) then
157 close c;
158 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
159 app_exception.raise_exception;
160 end if;
161 close c;
162 if ( ((recinfo.RESOURCE_CLASS = X_RESOURCE_CLASS)
163 OR ((recinfo.RESOURCE_CLASS is null) AND (X_RESOURCE_CLASS is null)))
164 AND ((recinfo.TRANS_CNT = X_TRANS_CNT)
165 OR ((recinfo.TRANS_CNT is null) AND (X_TRANS_CNT is null)))
166 AND (recinfo.DELETE_MARK = X_DELETE_MARK)
167 AND ((recinfo.TEXT_CODE = X_TEXT_CODE)
168 OR ((recinfo.TEXT_CODE is null) AND (X_TEXT_CODE is null)))
169 AND ((recinfo.MIN_CAPACITY = X_MIN_CAPACITY)
170 OR ((recinfo.MIN_CAPACITY is null) AND (X_MIN_CAPACITY is null)))
171 AND ((recinfo.MAX_CAPACITY = X_MAX_CAPACITY)
172 OR ((recinfo.MAX_CAPACITY is null) AND (X_MAX_CAPACITY is null)))
173 AND ((recinfo.CAPACITY_CONSTRAINT = X_CAPACITY_CONSTRAINT)
174 OR ((recinfo.CAPACITY_CONSTRAINT is null) AND (X_CAPACITY_CONSTRAINT is null)))
175 AND ((recinfo.CAPACITY_UM = X_CAPACITY_UOM)
176 OR ((recinfo.CAPACITY_UM is null) AND (X_CAPACITY_UOM is null)))
177 AND (recinfo.STD_USAGE_UOM = X_STD_USAGE_UM)
178 AND (recinfo.COST_CMPNTCLS_ID = X_COST_CMPNTCLS_ID)
179 AND ((recinfo.CAPACITY_TOLERANCE = X_CAPACITY_TOLERANCE)
180 OR ((recinfo.CAPACITY_TOLERANCE is null) AND (X_CAPACITY_TOLERANCE is null)))
181 AND ((recinfo.UTILIZATION = X_UTILIZATION)
182 OR ((recinfo.UTILIZATION is null) AND (X_UTILIZATION is null)))
183 AND ((recinfo.EFFICIENCY = X_EFFICIENCY)
184 OR ((recinfo.EFFICIENCY is null) AND (X_EFFICIENCY is null)))
185 ) then
186 null;
187 else
188 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
189 app_exception.raise_exception;
190 end if;
191
192 for tlinfo in c1 loop
193 if (tlinfo.BASELANG = 'Y') then
194 if ( (tlinfo.RESOURCE_DESC = X_RESOURCE_DESC)
195 ) then
196 null;
197 else
198 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
199 app_exception.raise_exception;
200 end if;
201 end if;
202 end loop;
203 return;
204 end LOCK_ROW;
205
206 procedure UPDATE_ROW (
207 X_RESOURCES in VARCHAR2,
208 X_RESOURCE_CLASS in VARCHAR2,
209 X_TRANS_CNT in NUMBER,
210 X_DELETE_MARK in NUMBER,
211 X_TEXT_CODE in NUMBER,
212 X_MIN_CAPACITY in NUMBER,
213 X_MAX_CAPACITY in NUMBER,
214 X_CAPACITY_CONSTRAINT in NUMBER,
215 X_CAPACITY_UOM in VARCHAR2,
216 X_STD_USAGE_UM in VARCHAR2,
217 X_COST_CMPNTCLS_ID in NUMBER,
218 X_RESOURCE_DESC in VARCHAR2,
219 X_LAST_UPDATE_DATE in DATE,
220 X_LAST_UPDATED_BY in NUMBER,
221 X_LAST_UPDATE_LOGIN in NUMBER,
222 X_CAPACITY_TOLERANCE in NUMBER,
223 X_UTILIZATION in NUMBER,
224 X_EFFICIENCY in NUMBER
225 ) is
226 begin
227 update CR_RSRC_MST_B set
228 RESOURCE_CLASS = X_RESOURCE_CLASS,
229 TRANS_CNT = X_TRANS_CNT,
230 DELETE_MARK = X_DELETE_MARK,
231 TEXT_CODE = X_TEXT_CODE,
232 MIN_CAPACITY = X_MIN_CAPACITY,
233 MAX_CAPACITY = X_MAX_CAPACITY,
234 CAPACITY_CONSTRAINT = X_CAPACITY_CONSTRAINT,
235 CAPACITY_UM = X_CAPACITY_UOM, /*sowsubra - resource model changes*/
236 STD_USAGE_UOM = X_STD_USAGE_UM, /*sowsubra - resource model changes*/
237 COST_CMPNTCLS_ID = X_COST_CMPNTCLS_ID,
238 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
239 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
240 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
241 CAPACITY_TOLERANCE = X_CAPACITY_TOLERANCE,
242 UTILIZATION = X_UTILIZATION,
243 EFFICIENCY = X_EFFICIENCY
244 where RESOURCES = X_RESOURCES;
245
246 if (sql%notfound) then
247 raise no_data_found;
248 end if;
249
250 update CR_RSRC_MST_TL set
251 RESOURCE_DESC = X_RESOURCE_DESC,
252 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
253 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
254 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
255 SOURCE_LANG = userenv('LANG')
256 where RESOURCES = X_RESOURCES
257 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
258
259 if (sql%notfound) then
260 raise no_data_found;
261 end if;
262 end UPDATE_ROW;
263
264 procedure DELETE_ROW (
265 X_RESOURCES in VARCHAR2
266 ) is
267 begin
268 /******************
269
270 delete from CR_RSRC_MST_TL
271 where RESOURCES = X_RESOURCES;
272
273 if (sql%notfound) then
274 raise no_data_found;
275 end if;
276
277 ******************/
278 update CR_RSRC_MST_B set delete_mark = 1
279 where RESOURCES = X_RESOURCES;
280
281 if (sql%notfound) then
282 raise no_data_found;
283 end if;
284 end DELETE_ROW;
285
286 procedure ADD_LANGUAGE
287 is
288 begin
289 delete from CR_RSRC_MST_TL T
290 where not exists
291 (select NULL
292 from CR_RSRC_MST_B B
293 where B.RESOURCES = T.RESOURCES
294 );
295
296 update CR_RSRC_MST_TL T set (
297 RESOURCE_DESC
298 ) = (select
299 B.RESOURCE_DESC
300 from CR_RSRC_MST_TL B
301 where B.RESOURCES = T.RESOURCES
302 and B.LANGUAGE = T.SOURCE_LANG)
303 where (
304 T.RESOURCES,
305 T.LANGUAGE
306 ) in (select
307 SUBT.RESOURCES,
308 SUBT.LANGUAGE
309 from CR_RSRC_MST_TL SUBB, CR_RSRC_MST_TL SUBT
310 where SUBB.RESOURCES = SUBT.RESOURCES
311 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
312 and (SUBB.RESOURCE_DESC <> SUBT.RESOURCE_DESC
313 ));
314
315 insert into CR_RSRC_MST_TL (
316 RESOURCES,
317 RESOURCE_DESC,
318 CREATION_DATE,
319 CREATED_BY,
320 LAST_UPDATE_DATE,
321 LAST_UPDATED_BY,
322 LAST_UPDATE_LOGIN,
323 LANGUAGE,
324 SOURCE_LANG
325 ) select
326 B.RESOURCES,
327 B.RESOURCE_DESC,
328 B.CREATION_DATE,
329 B.CREATED_BY,
330 B.LAST_UPDATE_DATE,
331 B.LAST_UPDATED_BY,
332 B.LAST_UPDATE_LOGIN,
333 L.LANGUAGE_CODE,
334 B.SOURCE_LANG
335 from CR_RSRC_MST_TL B, FND_LANGUAGES L
336 where L.INSTALLED_FLAG in ('I', 'B')
337 and B.LANGUAGE = userenv('LANG')
338 and not exists
339 (select NULL
340 from CR_RSRC_MST_TL T
341 where T.RESOURCES = B.RESOURCES
342 and T.LANGUAGE = L.LANGUAGE_CODE);
343 end ADD_LANGUAGE;
344
345 end CR_RSRC_MST_PKG;
[Home] [Help]