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