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

Source


1 package body QPR_POLICIES_PKG as
2 /* $Header: QPRUPLCB.pls 120.0 2007/12/24 20:03:24 vinnaray noship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out nocopy VARCHAR2,
5   X_POLICY_ID in NUMBER,
6   X_ACTIVE_FLAG in VARCHAR2,
7   X_PROGRAM_LOGIN_ID in NUMBER,
8   X_REQUEST_ID in NUMBER,
9   X_NAME in VARCHAR2,
10   X_DESCRIPTION in VARCHAR2,
11   X_CREATION_DATE in DATE,
12   X_CREATED_BY in NUMBER,
13   X_LAST_UPDATE_DATE in DATE,
14   X_LAST_UPDATED_BY in NUMBER,
15   X_LAST_UPDATE_LOGIN in NUMBER
16 ) is
17   cursor C is select ROWID from QPR_POLICIES_B
18     where POLICY_ID = X_POLICY_ID
19     ;
20 begin
21   insert into QPR_POLICIES_B (
22     POLICY_ID,
23     ACTIVE_FLAG,
24     PROGRAM_LOGIN_ID,
25     REQUEST_ID,
26     CREATION_DATE,
27     CREATED_BY,
28     LAST_UPDATE_DATE,
29     LAST_UPDATED_BY,
30     LAST_UPDATE_LOGIN
31   ) values (
32     X_POLICY_ID,
33     X_ACTIVE_FLAG,
34     X_PROGRAM_LOGIN_ID,
35     X_REQUEST_ID,
36     X_CREATION_DATE,
37     X_CREATED_BY,
38     X_LAST_UPDATE_DATE,
39     X_LAST_UPDATED_BY,
40     X_LAST_UPDATE_LOGIN
41   );
42 
43   insert into QPR_POLICIES_TL (
44     DESCRIPTION,
45     POLICY_ID,
46     NAME,
47     CREATION_DATE,
48     CREATED_BY,
49     LAST_UPDATE_DATE,
50     LAST_UPDATED_BY,
51     LAST_UPDATE_LOGIN,
52     --PROGRAM_ID,
53     PROGRAM_LOGIN_ID,
54     --PROGRAM_APPLICATION_ID,
55     REQUEST_ID,
56     LANGUAGE,
57     SOURCE_LANG
58   ) select
59     X_DESCRIPTION,
60     X_POLICY_ID,
61     X_NAME,
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_PROGRAM_ID,
68     X_PROGRAM_LOGIN_ID,
69     --X_PROGRAM_APPLICATION_ID,
70     X_REQUEST_ID,
71     L.LANGUAGE_CODE,
72     userenv('LANG')
73   from FND_LANGUAGES L
74   where L.INSTALLED_FLAG in ('I', 'B')
75   and not exists
76     (select NULL
77     from QPR_POLICIES_TL T
78     where T.POLICY_ID = X_POLICY_ID
79     and T.LANGUAGE = L.LANGUAGE_CODE);
80 
81   open c;
82   fetch c into X_ROWID;
83   if (c%notfound) then
84     close c;
85     raise no_data_found;
86   end if;
87   close c;
88 
89 end INSERT_ROW;
90 
91 procedure LOCK_ROW (
92   X_POLICY_ID in NUMBER,
93   X_ACTIVE_FLAG in VARCHAR2,
94   X_PROGRAM_LOGIN_ID in NUMBER,
95   X_REQUEST_ID in NUMBER,
96   X_NAME in VARCHAR2
97 ) is
98   cursor c is select
99       ACTIVE_FLAG,
100       PROGRAM_LOGIN_ID,
101       REQUEST_ID
102     from QPR_POLICIES_B
103     where POLICY_ID = X_POLICY_ID
104     for update of POLICY_ID nowait;
105   recinfo c%rowtype;
106 
107   cursor c1 is select
108       NAME,
109       decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
110     from QPR_POLICIES_TL
111     where POLICY_ID = X_POLICY_ID
112     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
113     for update of POLICY_ID nowait;
114 begin
115   open c;
116   fetch c into recinfo;
117   if (c%notfound) then
118     close c;
119     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
120     app_exception.raise_exception;
121   end if;
122   close c;
123   if (    ((recinfo.ACTIVE_FLAG = X_ACTIVE_FLAG)
124            OR ((recinfo.ACTIVE_FLAG is null) AND (X_ACTIVE_FLAG is null)))
125       AND ((recinfo.PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID)
126            OR ((recinfo.PROGRAM_LOGIN_ID is null) AND (X_PROGRAM_LOGIN_ID is null)))
127       AND ((recinfo.REQUEST_ID = X_REQUEST_ID)
128            OR ((recinfo.REQUEST_ID is null) AND (X_REQUEST_ID is null)))
129   ) then
130     null;
131   else
132     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
133     app_exception.raise_exception;
134   end if;
135 
136   for tlinfo in c1 loop
137     if (tlinfo.BASELANG = 'Y') then
138       if (    (tlinfo.NAME = X_NAME)
139       ) then
140         null;
141       else
142         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
143         app_exception.raise_exception;
144       end if;
145     end if;
146   end loop;
147   return;
148 end LOCK_ROW;
149 
150 procedure UPDATE_ROW (
151   X_POLICY_ID in NUMBER,
152   X_ACTIVE_FLAG in VARCHAR2,
153   X_PROGRAM_LOGIN_ID in NUMBER,
154   X_REQUEST_ID in NUMBER,
155   X_NAME in VARCHAR2,
156   X_DESCRIPTION in VARCHAR2,
157   X_LAST_UPDATE_DATE in DATE,
158   X_LAST_UPDATED_BY in NUMBER,
159   X_LAST_UPDATE_LOGIN in NUMBER
160 ) is
161 begin
162   update QPR_POLICIES_B set
163     ACTIVE_FLAG = X_ACTIVE_FLAG,
164     PROGRAM_LOGIN_ID = X_PROGRAM_LOGIN_ID,
165     REQUEST_ID = X_REQUEST_ID,
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 POLICY_ID = X_POLICY_ID;
170 
171   if (sql%notfound) then
172     raise no_data_found;
173   end if;
174 
175   update QPR_POLICIES_TL set
176     NAME = X_NAME,
177     DESCRIPTION = X_DESCRIPTION,
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 POLICY_ID = X_POLICY_ID
183   and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
184 
185   if (sql%notfound) then
186     raise no_data_found;
187   end if;
188 end UPDATE_ROW;
189 
190 procedure DELETE_ROW (
191   X_POLICY_ID in NUMBER
192 ) is
193 begin
194   delete from QPR_POLICIES_TL
195   where POLICY_ID = X_POLICY_ID;
196 
197   if (sql%notfound) then
198     raise no_data_found;
199   end if;
200 
201   delete from QPR_POLICIES_B
202   where POLICY_ID = X_POLICY_ID;
203 
204   if (sql%notfound) then
205     raise no_data_found;
206   end if;
207 end DELETE_ROW;
208 
209 procedure ADD_LANGUAGE
210 is
211 begin
212   delete from QPR_POLICIES_TL T
213   where not exists
214     (select NULL
215     from QPR_POLICIES_B B
216     where B.POLICY_ID = T.POLICY_ID
217     );
218 
219   update QPR_POLICIES_TL T set (
220       NAME
221     ) = (select
222       B.NAME
223     from QPR_POLICIES_TL B
224     where B.POLICY_ID = T.POLICY_ID
225     and B.LANGUAGE = T.SOURCE_LANG)
226   where (
227       T.POLICY_ID,
228       T.LANGUAGE
229   ) in (select
230       SUBT.POLICY_ID,
231       SUBT.LANGUAGE
232     from QPR_POLICIES_TL SUBB, QPR_POLICIES_TL SUBT
233     where SUBB.POLICY_ID = SUBT.POLICY_ID
234     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
235     and (SUBB.NAME <> SUBT.NAME
236   ));
237 
238   insert into QPR_POLICIES_TL (
239     DESCRIPTION,
240     POLICY_ID,
241     NAME,
242     CREATION_DATE,
243     CREATED_BY,
244     LAST_UPDATE_DATE,
245     LAST_UPDATED_BY,
246     LAST_UPDATE_LOGIN,
247     PROGRAM_ID,
248     PROGRAM_LOGIN_ID,
249     PROGRAM_APPLICATION_ID,
250     REQUEST_ID,
251     LANGUAGE,
252     SOURCE_LANG
253   ) select /*+ ORDERED */
254     B.DESCRIPTION,
255     B.POLICY_ID,
256     B.NAME,
257     B.CREATION_DATE,
258     B.CREATED_BY,
259     B.LAST_UPDATE_DATE,
260     B.LAST_UPDATED_BY,
261     B.LAST_UPDATE_LOGIN,
262     B.PROGRAM_ID,
263     B.PROGRAM_LOGIN_ID,
264     B.PROGRAM_APPLICATION_ID,
265     B.REQUEST_ID,
266     L.LANGUAGE_CODE,
267     B.SOURCE_LANG
268   from QPR_POLICIES_TL B, FND_LANGUAGES L
269   where L.INSTALLED_FLAG in ('I', 'B')
270   and B.LANGUAGE = userenv('LANG')
271   and not exists
272     (select NULL
273     from QPR_POLICIES_TL T
274     where T.POLICY_ID = B.POLICY_ID
275     and T.LANGUAGE = L.LANGUAGE_CODE);
276 end ADD_LANGUAGE;
277 
278 end QPR_POLICIES_PKG;