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.IEX_STRATEGY_TEMPLATES_PKG

Source


1 package body IEX_STRATEGY_TEMPLATES_PKG as
2 /* $Header: iextsttb.pls 120.4.12010000.3 2010/01/29 20:18:07 ehuh ship $ */
3 
4 PG_DEBUG NUMBER(2) := TO_NUMBER(NVL(FND_PROFILE.value('IEX_DEBUG_LEVEL'), '20'));
5 
6 procedure ADD_LANGUAGE
7 is
8 begin
9   delete from IEX_STRATEGY_TEMPLATES_TL T
10   where not exists
11     (select NULL
12      from IEX_STRATEGY_TEMPLATES_B B
13      where B.STRATEGY_TEMP_ID = T.STRATEGY_TEMP_ID
14     );
15 
16   update IEX_STRATEGY_TEMPLATES_TL T
17         set (STRATEGY_NAME) =
18              (select B.STRATEGY_NAME
19               from IEX_STRATEGY_TEMPLATES_TL B
20               where B.STRATEGY_TEMP_ID = T.STRATEGY_TEMP_ID
21               and B.LANGUAGE = T.SOURCE_LANG)
22         where (
23               T.STRATEGY_TEMP_ID,T.LANGUAGE
24                ) in (select
25                        SUBT.STRATEGY_TEMP_ID,
26                        SUBT.LANGUAGE
27                      from IEX_STRATEGY_TEMPLATES_TL SUBB,
28                           IEX_STRATEGY_TEMPLATES_TL SUBT
29                      where SUBB.STRATEGY_TEMP_ID = SUBT.STRATEGY_TEMP_ID
30                      and SUBB.LANGUAGE = SUBT.SOURCE_LANG
31                      and SUBB.STRATEGY_NAME<> SUBT.STRATEGY_NAME
32                      OR (SUBB.STRATEGY_NAME IS NULL AND SUBT.STRATEGY_NAME IS NOT NULL)
33                      OR (SUBB.STRATEGY_NAME IS NOT NULL AND SUBT.STRATEGY_NAME IS NULL)
34                 );
35 
36   insert into IEX_STRATEGY_TEMPLATES_TL (
37     STRATEGY_TEMP_ID,
38     STRATEGY_NAME,
39     LAST_UPDATE_DATE,
40     LAST_UPDATED_BY,
41     CREATION_DATE,
42     CREATED_BY,
43     LAST_UPDATE_LOGIN,
44     LANGUAGE,
45     SOURCE_LANG
46   ) select
47     B.STRATEGY_TEMP_ID,
48     B.STRATEGY_NAME,
49     B.LAST_UPDATE_DATE,
50     B.LAST_UPDATED_BY,
51     B.CREATION_DATE,
52     B.CREATED_BY,
53     B.LAST_UPDATE_LOGIN,
54     L.LANGUAGE_CODE,
55     B.SOURCE_LANG
56   from IEX_STRATEGY_TEMPLATES_TL B, FND_LANGUAGES L
57   where L.INSTALLED_FLAG in ('I', 'B')
58   and B.LANGUAGE = userenv('LANG')
59   and not exists
60     (select NULL
61      from IEX_STRATEGY_TEMPLATES_TL T
62      where T.STRATEGY_TEMP_ID = B.STRATEGY_TEMP_ID
63      and T.LANGUAGE = L.LANGUAGE_CODE);
64 
65 end ADD_LANGUAGE;
66 
67 
68 procedure TRANSLATE_ROW (
69   X_STRATEGY_TEMP_ID in NUMBER,
70   X_STRATEGY_NAME in VARCHAR2,
71   X_OBJECT_VERSION_NUMBER in NUMBER,
72   X_OWNER in VARCHAR2
73 ) IS
74 
75 begin
76 	UPDATE IEX_STRATEGY_TEMPLATES_TL SET
77 		STRATEGY_NAME=X_STRATEGY_NAME,
78 		last_update_date = sysdate,
79 		last_updated_by = decode(X_OWNER, 'SEED', 1, 0),
80 		last_update_login = 0,
81 		source_lang = userenv('LANG')
82 	WHERE userenv('LANG') in (LANGUAGE, SOURCE_LANG) AND
83 		 STRATEGY_TEMP_id = X_STRATEGY_TEMP_ID;
84 end TRANSLATE_ROW;
85 
86 
87 PROCEDURE COPY_STRATEGY_GROUP(P_GROUP_ID in NUMBER, RETURN_STATUS OUT NOCOPY VARCHAR2) IS
88  nNextGroupID number;
89   nNextTempID number;
90   nNextXrefID NUMBER;
91 
92   CURSOR c_templates(vGROUP_ID NUMBER) is
93         SELECT
94         STRATEGY_TEMP_ID,
95         STRATEGY_TEMP_GROUP_ID,
96         STRATEGY_ORDER_ID
97         LAST_UPDATE_LOGIN,
98         REQUEST_ID,
99         STRATEGY_RANK,
100         ENABLED_FLAG,
101         CATEGORY_TYPE,
102         CHANGE_STRATEGY_YN,
103         CHECK_LIST_YN,
104         CHECK_LIST_TEMP_ID ,
105         VALID_FROM_DT,
106         VALID_TO_DT,
107         OBJECT_FILTER_ID,
108         STRATEGY_LEVEL,
109         SCORE_TOLERANCE,
110         STRATEGY_NAME,
111         STRATEGY_ORDER_ID
112      FROM  IEX_STRATEGY_TEMPLATES_VL
113         WHERE STRATEGY_TEMP_GROUP_ID = vGROUP_ID;
114 
115   CURSOR c_Xref(vTemplate_ID number) IS
116      SELECT
117         WORK_TEMP_XREF_ID,
118         STRATEGY_TEMP_ID,
119         WORK_ITEM_TEMP_ID,
120         WORK_ITEM_ORDER,
121         REQUIRED_YN,
122         CREATED_BY,
123         CREATION_DATE,
124         LAST_UPDATE_DATE,
125         LAST_UPDATED_BY,
126         LAST_UPDATE_LOGIN,
127         REQUEST_ID,
128         OBJECT_VERSION_NUMBER
129     FROM IEX_STRATEGY_WORK_TEMP_XREF
130       WHERE STRATEGY_TEMP_ID = vTemplate_ID;
131 
132 BEGIN
133 
134     SAVEPOINT IEX_STRY_TEMP_PKG_COPYSTRGRP;
135 
136     RETURN_STATUS := 'A';
137 
138     IEX_DEBUG_PUB.logmessage('Copy Strategy Group ');
139     select IEX_STRATEGY_TEMPLATE_GROUPS_S.NEXTVAL
140       into nNextGroupID from DUAL;
141 
142     IEX_DEBUG_PUB.logmessage('Next Strategy GROUP_ID ID = ' || nNextGroupID);
143     IEX_DEBUG_PUB.logmessage('AAAAAAAAAAAAA = ' || nNextGroupID);
144     RETURN_STATUS := 'B';
145 
146     INSERT INTO IEX_STRATEGY_TEMPLATE_GROUPS (
147         OBJECT_VERSION_NUMBER,
148         CREATED_BY,
149         CREATION_DATE,
150         LAST_UPDATE_DATE,
151         LAST_UPDATED_BY,
152         LAST_UPDATE_LOGIN,
153         REQUEST_ID,
154         GROUP_ID,
155         GROUP_NAME,
156         STRATEGY_RANK,
157         ENABLED_FLAG,
158         CATEGORY_TYPE,
159         CHANGE_STRATEGY_YN,
160         CHECK_LIST_YN,
161         CHECK_LIST_TEMP_ID,
162         VALID_FROM_DT,
163         VALID_TO_DT,
164         OBJECT_FILTER_ID,
165         STRATEGY_LEVEL,
166         SCORE_TOLERANCE,
167         STRATEGY_TEMP_ID
168       )
169       SELECT
170         1,
171         FND_GLOBAL.USER_ID,
172         SYSDATE,
173         SYSDATE,
174         FND_GLOBAL.USER_ID,
175         ISTL.LAST_UPDATE_LOGIN,
176         ISTL.REQUEST_ID,
177         nNextGroupID,
178         'Copy of ' || ISTL.GROUP_NAME,
179         ISTL.STRATEGY_RANK,
180         ISTL.ENABLED_FLAG,
181         ISTL.CATEGORY_TYPE,
182         ISTL.CHANGE_STRATEGY_YN,
183         ISTL.CHECK_LIST_YN,
184         ISTL.CHECK_LIST_TEMP_ID ,
185         ISTL.VALID_FROM_DT,
186         ISTL.VALID_TO_DT,
187         ISTL.OBJECT_FILTER_ID,
188         ISTL.STRATEGY_LEVEL,
189         ISTL.SCORE_TOLERANCE,
190         ISTL.STRATEGY_TEMP_ID
191      FROM  IEX_STRATEGY_TEMPLATE_GROUPS istl
192         WHERE istl.GROUP_ID = p_GROUP_ID AND
193            NOT EXISTS (SELECT 1 FROM IEX_STRATEGY_TEMPLATE_GROUPS istg
194               WHERE istg.GROUP_NAME  = 'Copy of ' || istl.GROUP_NAME );
195 
196    IEX_DEBUG_PUB.logmessage('Done copying the Group ');
197    IEX_DEBUG_PUB.logmessage('BBBBBBBBBBBB = ' || nNextGroupID);
198    IEX_DEBUG_PUB.logmessage('P_GROUP_ID = ' || p_group_id);
199 
200     FOR tempCur IN c_templates(P_GROUP_ID)  LOOP
201 
202        IEX_DEBUG_PUB.logmessage('Copy Strategy Template ' || tempCur.STRATEGY_TEMP_ID);
203 
204         select IEX_STRATEGY_TEMPLATES_S.NEXTVAL
205            into nNextTempID from DUAL;
206 
207        IEX_DEBUG_PUB.logmessage('Next Strategy Template ID = ' || nNextTempID);
208        IEX_DEBUG_PUB.logmessage('Copy FND_GLOBAL.USER_ID ' || FND_GLOBAL.USER_ID);
209        IEX_DEBUG_PUB.logmessage('Copy LAST_UPDATE_LOGIN ' || tempCur.LAST_UPDATE_LOGIN);
210        IEX_DEBUG_PUB.logmessage('Copy REQUEST_ID Template ' || tempCur.REQUEST_ID);
211        IEX_DEBUG_PUB.logmessage('Copy STRATEGY_RANK  Template ' || tempCur.STRATEGY_RANK);
212        IEX_DEBUG_PUB.logmessage('Copy ENABLED_FLAG Template ' || tempCur.ENABLED_FLAG);
213        IEX_DEBUG_PUB.logmessage('Copy Strategy Template ' || tempCur.STRATEGY_TEMP_ID);
214 
215        INSERT INTO IEX_STRATEGY_TEMPLATES_B (
216           OBJECT_VERSION_NUMBER,
217           CREATED_BY,
218           CREATION_DATE,
219           LAST_UPDATE_DATE,
220           LAST_UPDATED_BY,
221           LAST_UPDATE_LOGIN,
222           REQUEST_ID,
223           STRATEGY_TEMP_ID,
224           STRATEGY_RANK,
225           ENABLED_FLAG,
226           CATEGORY_TYPE,
227           CHANGE_STRATEGY_YN,
228           CHECK_LIST_YN,
229           CHECK_LIST_TEMP_ID,
230           VALID_FROM_DT,
231           VALID_TO_DT,
232           OBJECT_FILTER_ID,
233           STRATEGY_LEVEL,
234           SCORE_TOLERANCE,
235           STRATEGY_TEMP_GROUP_ID,
236           STRATEGY_ORDER_ID
237       )
238       VALUES(
239         1,
240         FND_GLOBAL.USER_ID,
241         SYSDATE,
242         SYSDATE,
243         FND_GLOBAL.USER_ID,
244         FND_GLOBAL.USER_ID,-- bug 6671798 tempCur.LAST_UPDATE_LOGIN,
245         tempCur.REQUEST_ID,
246         nNextTempID,
247         tempCur.STRATEGY_RANK,
248         tempCur.ENABLED_FLAG,
249         tempCur.CATEGORY_TYPE,
250         tempCur.CHANGE_STRATEGY_YN,
251         tempCur.CHECK_LIST_YN,
252         tempCur.CHECK_LIST_TEMP_ID ,
253         tempCur.VALID_FROM_DT,
254         tempCur.VALID_TO_DT,
255         tempCur.OBJECT_FILTER_ID,
256         tempCur.STRATEGY_LEVEL,
257         tempCur.SCORE_TOLERANCE,
258         nNextGroupID,
259         tempCur.STRATEGY_ORDER_ID
260      );
261 
262 
263      IEX_DEBUG_PUB.logmessage('Done inserting... IEX_STRATEGY_TEMPLATES_B');
264 
265      INSERT INTO IEX_STRATEGY_TEMPLATES_tl (
266           CREATED_BY,
267           CREATION_DATE,
268           LAST_UPDATE_DATE,
269           LAST_UPDATED_BY,
270           LAST_UPDATE_LOGIN,
271           STRATEGY_TEMP_ID,
272           STRATEGY_NAME,
273           SOURCE_LANG,
274           LANGUAGE
275           )
276         VALUES (
277         FND_GLOBAL.USER_ID,
278         SYSDATE,
279         SYSDATE,
280         FND_GLOBAL.USER_ID,
281         FND_GLOBAL.USER_ID,-- bug 6671798 tempCur.LAST_UPDATE_LOGIN,
282         nNextTempID,
283         'Copy of ' || tempCur.strategy_name,
284         userenv('LANG'),
285         userenv('LANG')
286       );
287       IEX_DEBUG_PUB.logmessage('End Copy Strategy Template ' || nNextTempID);
288 
289   -- begin added for a Bug 9305366
290   insert into IEX_STRATEGY_TEMPLATES_TL (
291     STRATEGY_TEMP_ID,
292     STRATEGY_NAME,
293     LAST_UPDATE_DATE,
294     LAST_UPDATED_BY,
295     CREATION_DATE,
296     CREATED_BY,
297     LAST_UPDATE_LOGIN,
298     LANGUAGE,
299     SOURCE_LANG
300   ) select
301     B.STRATEGY_TEMP_ID,
302     B.STRATEGY_NAME,
303     B.LAST_UPDATE_DATE,
304     B.LAST_UPDATED_BY,
305     B.CREATION_DATE,
306     B.CREATED_BY,
307     B.LAST_UPDATE_LOGIN,
308     L.LANGUAGE_CODE,
309     B.SOURCE_LANG
310   from IEX_STRATEGY_TEMPLATES_TL B, FND_LANGUAGES L
311   where L.INSTALLED_FLAG in ('I', 'B')
312   and B.LANGUAGE = userenv('LANG')
313   and not exists
314     (select NULL
315      from IEX_STRATEGY_TEMPLATES_TL T
316      where T.STRATEGY_TEMP_ID = B.STRATEGY_TEMP_ID
317      and T.LANGUAGE = L.LANGUAGE_CODE);
318 
319 IEX_DEBUG_PUB.logmessage('End Copy Multi-Lang Strategy Template ' || nNextTempID);
320 
321   -- end added for a Bug 9305366
322 
323 
324 
325       FOR xrefCur IN c_Xref(tempCur.STRATEGY_TEMP_ID)  LOOP
326 
327        IEX_DEBUG_PUB.logmessage('Copy XRef for Template = ' || tempCur.STRATEGY_TEMP_ID ||
328            ' and Xref ID = ' || xrefCur.WORK_TEMP_XREF_ID );
329 
330         select IEX_STRATEGY_WORK_TEMP_XREF_S.NEXTVAL
331            into nNextXrefID FROM DUAL;
332 
333        IEX_DEBUG_PUB.logmessage('Next Work Item Xref ID from seq = ' || nNextXrefID);
334 
335         INSERT INTO IEX_STRATEGY_WORK_TEMP_XREF(
336           WORK_TEMP_XREF_ID,
337           STRATEGY_TEMP_ID,
338           WORK_ITEM_TEMP_ID,
339           WORK_ITEM_ORDER,
340           REQUIRED_YN,
341           CREATED_BY,
342           CREATION_DATE,
343           LAST_UPDATE_DATE,
344           LAST_UPDATED_BY,
345           LAST_UPDATE_LOGIN,
346           OBJECT_VERSION_NUMBER )
347         VALUES (nNextXrefID,
348           nNextTempID,
349           xrefCur.WORK_ITEM_TEMP_ID,
350           xrefCur.WORK_ITEM_ORDER,
351           xrefCur.REQUIRED_YN,
352           FND_GLOBAL.USER_ID,
353           SYSDATE,
354           SYSDATE,
355           FND_GLOBAL.USER_ID,
356           FND_GLOBAL.USER_ID,--  bug 6671798 xrefCur.LAST_UPDATE_LOGIN,
357           1);
358 
359       END LOOP;  /* xrefCur */
360 
361    END LOOP;  /* tempCur */
362 
363 
364 
365    --RETURN_STATUS := 'S';
366    RETURN_STATUS := nNextGroupID;  -- bug 7705188
367    Commit Work;
368 
369 EXCEPTION
370    WHEN OTHERS THEN
371     ROLLBACK TO IEX_STRY_TEMP_PKG_COPYSTRGRP;
372     RETURN_STATUS := 'E';
373 END COPY_STRATEGY_GROUP;
374 
375 
376 end IEX_STRATEGY_TEMPLATES_PKG;