eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CN_SYIN_RULESETS_PKG

Source


4 
1 PACKAGE BODY cn_syin_rulesets_pkg AS
2 -- $Header: cnsyinib.pls 120.9 2006/01/13 03:55:37 hanaraya ship $
3 
5 -- =================================================================================
6 -- Procedure Name : Populate_Fields
7 -- History
8 --			Tony Lower		Created
9 -- =================================================================================
10 PROCEDURE Populate_fields (X_column_id		number,
11 			   X_column_name IN OUT NOCOPY	varchar2) IS
12   BEGIN
13 
17     END IF;
14     IF X_column_id IS NOT NULL THEN
15       SELECT name INTO X_column_name FROM cn_obj_columns_v
16 	WHERE column_id = X_column_id;
18 
19   END Populate_Fields;
20 
21 
22 
23 -- =================================================================================
24 -- Procedure Name : insert_row
25 -- History
26 --			Tony Lower		Created
27 --  Feb-25-99         Harlen Chen             Change to new insert_row for MLS
28 -- =================================================================================
29 
30 procedure INSERT_ROW
31    (
32    X_ROWID in out NOCOPY VARCHAR2,
33    X_RULESET_ID in NUMBER := FND_API.G_MISS_NUM,
34    X_RULESET_STATUS in VARCHAR2 := FND_API.G_MISS_CHAR,
35    X_DESTINATION_COLUMN_ID in NUMBER := FND_API.G_MISS_NUM,
36    X_REPOSITORY_ID in NUMBER := FND_API.G_MISS_NUM,
37    X_NAME in VARCHAR2 := FND_API.G_MISS_CHAR,
38    x_module_type IN VARCHAR2 := fnd_api.g_miss_char,
39    x_start_date IN DATE := fnd_api.g_miss_date,
40    x_end_date IN DATE := fnd_api.g_miss_date,
41    X_CREATION_DATE in DATE := FND_API.G_MISS_DATE,
42    X_CREATED_BY in NUMBER := FND_API.G_MISS_NUM,
43    X_LAST_UPDATE_DATE in DATE := FND_API.G_MISS_DATE,
44    X_LAST_UPDATED_BY in NUMBER := FND_API.G_MISS_NUM,
45    X_LAST_UPDATE_LOGIN in NUMBER := FND_API.G_MISS_NUM,
46    X_ORG_ID in NUMBER := FND_API.G_MISS_NUM)
47   IS
48 
49 
50      l_rowid ROWID;
51 
52 BEGIN
53 
54 
55   insert into CN_RULESETS_ALL_B
56     (
57      RULESET_STATUS,
58      RULESET_ID,
59      DESTINATION_COLUMN_ID,
60      REPOSITORY_ID,
61      start_date,
62      end_date,
63      module_type,
64      CREATION_DATE,
65      CREATED_BY,
66      LAST_UPDATE_DATE,
67      LAST_UPDATED_BY,
68      LAST_UPDATE_LOGIN,
69      OBJECT_VERSION_NUMBER,
70      ORG_ID
71      ) values
72     (
73      x_RULESET_STATUS,
74      x_RULESET_ID,
75      x_DESTINATION_COLUMN_ID,
76      x_REPOSITORY_ID,
77      x_start_date,
78      x_end_date,
79      x_module_type,
80      x_CREATION_DATE,
81      x_CREATED_BY,
82      x_LAST_UPDATE_DATE,
83      x_LAST_UPDATED_BY,
84      x_LAST_UPDATE_LOGIN,
85      1,
86      x_org_id
87      );
88 
89   insert into CN_RULESETS_ALL_TL (
90     CREATED_BY,
91     LAST_UPDATE_DATE,
92     LAST_UPDATED_BY,
93     LAST_UPDATE_LOGIN,
94     CREATION_DATE,
95     NAME,
96     RULESET_ID,
97     LANGUAGE,
98     SOURCE_LANG,
99     ORG_ID
100   ) select
101     x_CREATED_BY,
102     x_LAST_UPDATE_DATE,
103     x_LAST_UPDATED_BY,
104     x_LAST_UPDATE_LOGIN,
105     x_CREATION_DATE,
106     x_NAME,
107     x_RULESET_ID,
108     L.LANGUAGE_CODE,
109     userenv('LANG'),
110     x_org_id
111   from FND_LANGUAGES L
112   where L.INSTALLED_FLAG in ('I', 'B')
113  and not exists
114     (select NULL
115     from CN_RULESETS_ALL_TL T
116     where T.RULESET_ID = x_RULESET_ID
117     and T.LANGUAGE = L.language_code AND
118     T.ORG_ID=x_org_id);
119 
120 
121 
122 -- 04-MAR-99 RC Commented out following code since rule_id
123 -- for base rule should be -1002
124 --  SELECT cn_rules_s.NEXTVAL
125 --    INTO  l_new_rule_id
126 --    FROM dual;
127 
128  cn_syin_rules_pkg.insert_row_into_cn_rules_only(
129   x_rowid => l_rowid,
130   X_RULE_ID => -1002,
131   X_RULESET_ID => X_ruleset_id,
132   X_NAME => 'BASE_RULE',
133   X_ORG_ID => X_ORG_ID);
134 
135 
136 end INSERT_ROW;
137 
138 
139 
140 procedure UPDATE_ROW
141   (
142   X_RULESET_ID in NUMBER,
143   X_OBJECT_VERSION_NUMBER IN NUMBER,
144   X_RULESET_STATUS in VARCHAR2,
145   X_DESTINATION_COLUMN_ID in NUMBER,
146   X_REPOSITORY_ID in NUMBER,
147   x_start_date IN DATE,
148   x_end_date IN DATE,
149    X_NAME in VARCHAR2,
150    x_module_type IN VARCHAR2,
151   X_LAST_UPDATE_DATE in DATE,
152   X_LAST_UPDATED_BY in NUMBER,
153   X_LAST_UPDATE_LOGIN in NUMBER,
154   X_ORG_ID in NUMBER
155 ) is
156 BEGIN
157 
158   update CN_RULESETS_ALL_B set
159     RULESET_STATUS = X_RULESET_STATUS,
160     OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER,
161     DESTINATION_COLUMN_ID = X_DESTINATION_COLUMN_ID,
162     REPOSITORY_ID = X_REPOSITORY_ID,
163     start_date = x_start_date,
164     end_date = x_end_date,
165     module_type = x_module_type,
166     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
167     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
168     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
169   where RULESET_ID = x_ruleset_id  AND
170   ORG_ID=X_ORG_ID;
171 
172   if (sql%notfound) then
173     raise no_data_found;
174   end if;
175 
176   update CN_RULESETS_ALL_TL set
177     NAME = X_NAME,
178     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
179     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
180     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
181     SOURCE_LANG = userenv('LANG')
182   where RULESET_ID = X_RULESET_ID
183   and userenv('LANG') in (LANGUAGE, SOURCE_LANG)  AND
184    ORG_ID=X_ORG_ID;
185 
186   if (sql%notfound) then
187     raise no_data_found;
188   end if;
189 end UPDATE_ROW;
190 
191 procedure DELETE_ROW (
192   X_RULESET_ID in NUMBER,
193   X_ORG_ID IN NUMBER
194 ) is
195 begin
196   delete from CN_RULESETS_ALL_TL
200   if (sql%notfound) then
197   where RULESET_ID = x_ruleset_id  AND
198   ORG_ID=X_ORG_ID;
199 
201     raise no_data_found;
202   end if;
203 
204   delete from CN_RULESETS_ALL_B
205   where RULESET_ID = x_ruleset_id  AND
206   ORG_ID=X_ORG_ID;
207 
208   if (sql%notfound) then
209     raise no_data_found;
210   end if;
211 end DELETE_ROW;
212 
213 procedure ADD_LANGUAGE
214 is
215 begin
216   delete from CN_RULESETS_ALL_TL T
217   where not exists
218     (select NULL
219     from CN_RULESETS_ALL_B B
220     where B.RULESET_ID = T.ruleset_id
221     and   B.ORG_ID = T.ORG_ID
222     );
223 
227       B.NAME
224   update CN_RULESETS_ALL_TL T set (
225       NAME
226     ) = (select
228     from CN_RULESETS_ALL_TL B
229     where B.RULESET_ID = T.RULESET_ID
230     and B.LANGUAGE = T.source_lang
231     and   B.ORG_ID = T.ORG_ID 	 )
232   where (
233       T.RULESET_ID,
234       T.LANGUAGE
235   ) in (select
236       SUBT.RULESET_ID,
237       SUBT.LANGUAGE
238     from CN_RULESETS_ALL_TL SUBB, CN_RULESETS_ALL_TL SUBT
239     where SUBB.RULESET_ID = SUBT.RULESET_ID
240     and SUBB.LANGUAGE = SUBT.source_lang
241     and   SUBB.ORG_ID = SUBT.ORG_ID
242     and (SUBB.NAME <> SUBT.name
243       or (SUBB.NAME is null and SUBT.NAME is not null)
244       or (SUBB.NAME is not null and SUBT.NAME is null)
245   ));
246 
247   insert into CN_RULESETS_ALL_TL (
248     ORG_ID,
249     CREATED_BY,
250     LAST_UPDATE_DATE,
251     LAST_UPDATED_BY,
252     LAST_UPDATE_LOGIN,
253     CREATION_DATE,
254     NAME,
255     RULESET_ID,
256     LANGUAGE,
257     SOURCE_LANG
258   ) select
259     B.ORG_ID,
260     B.CREATED_BY,
261     B.LAST_UPDATE_DATE,
262     B.LAST_UPDATED_BY,
263     B.LAST_UPDATE_LOGIN,
264     B.CREATION_DATE,
265     B.NAME,
266     B.RULESET_ID,
267     L.LANGUAGE_CODE,
268     B.SOURCE_LANG
269   from CN_RULESETS_ALL_TL B, FND_LANGUAGES L
270   where L.INSTALLED_FLAG in ('I', 'B')
271   and B.LANGUAGE = userenv('LANG')
272   and not exists
273     (select NULL
274     from CN_RULESETS_ALL_TL T
275     where T.RULESET_ID = B.RULESET_ID
276     and T.LANGUAGE = L.LANGUAGE_CODE
277     and   T.ORG_ID = B.ORG_ID );
278 end ADD_LANGUAGE;
279 
280 -- --------------------------------------------------------------------+
281 -- Procedure : LOAD_ROW
282 -- Description : Called by FNDLOAD to upload seed datas, this procedure
283 --    only handle seed datas. ORG_ID = -3113
284 -- --------------------------------------------------------------------+
285 
286 PROCEDURE LOAD_ROW
287   ( x_ruleset_id IN NUMBER,
288     x_destination_column_id  IN NUMBER,
289     x_repository_id   IN NUMBER,
290     x_name IN VARCHAR2,
291     x_ruleset_status in VARCHAR2,
292     x_start_date IN DATE,
293     x_end_date IN DATE,
294     x_owner IN VARCHAR2,
295     x_org_id IN NUMBER) IS
296 
297        user_id NUMBER;
298 
299 BEGIN
300    -- Validate input data
301    IF (x_ruleset_id IS NULL) OR (x_name IS NULL)
302      OR (x_destination_column_id IS NULL) OR (x_repository_id IS NULL)
303        OR (x_ruleset_status IS NULL) THEN
304       GOTO end_load_row;
305    END IF;
306 
307    IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
308       user_id := 1;
309     ELSE
310       user_id := 0;
311    END IF;
312    -- Load The record to _B table
313    UPDATE cn_rulesets_all_b SET
314      destination_column_id = x_destination_column_id,
315      repository_id = x_repository_id,
319      last_update_login = 0
316      ruleset_status = x_ruleset_status,
317      last_update_date = sysdate,
318      last_updated_by = user_id,
320      WHERE ruleset_id = x_ruleset_id and org_id=x_org_id ;
321 
322    IF (SQL%NOTFOUND) THEN
323       -- Insert new record to _B table
324       INSERT INTO cn_rulesets_all_b
325 	(RULESET_ID,
326 	 DESTINATION_COLUMN_ID,
327 	 REPOSITORY_ID,
328 	 RULESET_STATUS,
329 	 CREATION_DATE,
330 	 CREATED_BY,
331 	 LAST_UPDATE_DATE,
332 	 LAST_UPDATED_BY,
333 	 LAST_UPDATE_LOGIN,
334 	 ORG_ID
335 	 ) VALUES
336 	(X_RULESET_ID,
337 	 X_DESTINATION_COLUMN_ID,
338 	 X_REPOSITORY_ID,
339 	 X_RULESET_STATUS,
340 	 sysdate,
341 	 user_id,
342 	 sysdate,
343 	 user_id,
344 	 0,
345 	 x_org_id
346 	 );
347    END IF;
348    -- Load The record to _TL table
349    UPDATE cn_rulesets_all_tl  SET
350      name = x_name,
351      last_update_date = sysdate,
352      last_updated_by = user_id,
353      last_update_login = 0,
354      source_lang = userenv('LANG')
355      WHERE ruleset_id = x_ruleset_id AND org_id=x_org_id
356      AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
357 
358    IF (SQL%NOTFOUND) THEN
359       -- Insert new record to _TL table
360       INSERT INTO cn_rulesets_all_tl
361 	(ruleset_id,
362 	 name,
363 	 creation_date,
364 	 created_by,
365 	 last_update_date,
366 	 last_updated_by,
367 	 last_update_login,
368 	 language,
369 	 source_lang,
370 	 org_id)
371 	SELECT
372 	x_ruleset_id,
373 	x_name,
374 	sysdate,
375 	user_id,
376 	sysdate,
377 	user_id,
378 	0,
379 	l.language_code,
380 	userenv('LANG'),
381 	x_org_id
382 	FROM fnd_languages l
383 	WHERE l.installed_flag IN ('I', 'B')
384 	AND NOT EXISTS
385 	(SELECT NULL
386 	 FROM cn_rulesets_all_tl t
387 	 WHERE t.ruleset_id = x_ruleset_id and t.org_id=x_org_id
388 	 AND t.language = l.language_code);
389    END IF;
390    << end_load_row >>
391      NULL;
392 END LOAD_ROW ;
393 
394 -- --------------------------------------------------------------------+
395 -- Procedure : TRANSLATE_ROW
396 -- Description : Called by FNDLOAD to translate seed datas, this procedure
397 --    only handle seed datas. ORG_ID = -3113
398 -- --------------------------------------------------------------------+
399 PROCEDURE TRANSLATE_ROW
400   ( x_ruleset_id IN NUMBER,
401     x_name IN VARCHAR2,
402     x_owner IN VARCHAR2,
403     x_org_id in number) IS
404     user_id NUMBER;
405 BEGIN
406     -- Validate input data
407    IF (x_ruleset_id IS NULL) OR (x_name IS NULL) THEN
408       GOTO end_translate_row;
409    END IF;
410 
411    IF (x_owner IS NOT NULL) AND (x_owner = 'SEED') THEN
412       user_id := 1;
413     ELSE
414       user_id := 0;
415    END IF;
416    -- Update the translation
417    UPDATE cn_rulesets_all_tl  SET
418      name = x_name,
419      last_update_date = sysdate,
420      last_updated_by = user_id,
421      last_update_login = 0,
422      source_lang = userenv('LANG')
423      WHERE ruleset_id = x_ruleset_id and org_id=x_org_id
424      AND userenv('LANG') IN (LANGUAGE, SOURCE_LANG);
425 
426    << end_translate_row >>
427      NULL;
428 END TRANSLATE_ROW ;
429 
430 
431 END cn_syin_rulesets_pkg;