[Home] [Help]
PACKAGE BODY: APPS.CSD_FLWSTS_TRANS_PKG
Source
1 package body CSD_FLWSTS_TRANS_PKG as
2 /* $Header: csdtfltb.pls 120.1 2005/07/29 16:35:56 vkjain noship $ */
3
4 procedure INSERT_ROW (
5 -- P_ROWID in out nocopy VARCHAR2,
6 PX_FLWSTS_TRAN_ID in out nocopy NUMBER,
7 P_REPAIR_TYPE_ID in NUMBER,
8 P_FROM_FLOW_STATUS_ID in NUMBER,
9 P_TO_FLOW_STATUS_ID in NUMBER,
10 P_WF_ITEM_TYPE in VARCHAR2,
11 P_WF_PROCESS_NAME in VARCHAR2,
12 P_REASON_REQUIRED_FLAG in VARCHAR2,
13 P_CAPTURE_ACTIVITY_FLAG in VARCHAR2,
14 P_ALLOW_ALL_RESP_FLAG in VARCHAR2,
15 P_OBJECT_VERSION_NUMBER in NUMBER,
16 P_DESCRIPTION in VARCHAR2,
17 P_CREATION_DATE in DATE,
18 P_CREATED_BY in NUMBER,
19 P_LAST_UPDATE_DATE in DATE,
20 P_LAST_UPDATED_BY in NUMBER,
21 P_LAST_UPDATE_LOGIN in NUMBER
22 ) is
23
24 P_ROWID ROWID;
25
26 cursor C is select ROWID from CSD_FLWSTS_TRANS_B
27 where FLWSTS_TRAN_ID = PX_FLWSTS_TRAN_ID
28 ;
29
30 begin
31
32 select CSD_FLWSTS_TRANS_S1.nextval
33 into PX_FLWSTS_TRAN_ID
34 from dual;
35
36 insert into CSD_FLWSTS_TRANS_B (
37 REPAIR_TYPE_ID,
38 FROM_FLOW_STATUS_ID,
39 TO_FLOW_STATUS_ID,
40 WF_ITEM_TYPE,
41 WF_PROCESS_NAME,
42 REASON_REQUIRED_FLAG,
43 CAPTURE_ACTIVITY_FLAG,
44 ALLOW_ALL_RESP_FLAG,
45 OBJECT_VERSION_NUMBER,
46 FLWSTS_TRAN_ID,
47 CREATION_DATE,
48 CREATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATED_BY,
51 LAST_UPDATE_LOGIN
52 ) values (
53 P_REPAIR_TYPE_ID,
54 P_FROM_FLOW_STATUS_ID,
55 P_TO_FLOW_STATUS_ID,
56 P_WF_ITEM_TYPE,
57 P_WF_PROCESS_NAME,
58 P_REASON_REQUIRED_FLAG,
59 P_CAPTURE_ACTIVITY_FLAG,
60 P_ALLOW_ALL_RESP_FLAG,
61 P_OBJECT_VERSION_NUMBER,
62 PX_FLWSTS_TRAN_ID,
63 P_CREATION_DATE,
64 P_CREATED_BY,
65 P_LAST_UPDATE_DATE,
66 P_LAST_UPDATED_BY,
67 P_LAST_UPDATE_LOGIN
68 );
69
70 insert into CSD_FLWSTS_TRANS_TL (
71 FLWSTS_TRAN_ID,
72 DESCRIPTION,
73 CREATED_BY,
74 CREATION_DATE,
75 LAST_UPDATED_BY,
76 LAST_UPDATE_DATE,
77 LAST_UPDATE_LOGIN,
78 LANGUAGE,
79 SOURCE_LANG
80 ) select
81 PX_FLWSTS_TRAN_ID,
82 P_DESCRIPTION,
83 P_CREATED_BY,
84 P_CREATION_DATE,
85 P_LAST_UPDATED_BY,
86 P_LAST_UPDATE_DATE,
87 P_LAST_UPDATE_LOGIN,
88 L.LANGUAGE_CODE,
89 userenv('LANG')
90 from FND_LANGUAGES L
91 where L.INSTALLED_FLAG in ('I', 'B')
92 and not exists
93 (select NULL
94 from CSD_FLWSTS_TRANS_TL T
95 where T.FLWSTS_TRAN_ID = PX_FLWSTS_TRAN_ID
96 and T.LANGUAGE = L.LANGUAGE_CODE);
97
98 open c;
99 fetch c into P_ROWID;
100 if (c%notfound) then
101 close c;
102 raise no_data_found;
103 end if;
104 close c;
105
106 end INSERT_ROW;
107
108 procedure LOCK_ROW (
109 P_FLWSTS_TRAN_ID in NUMBER,
110 P_OBJECT_VERSION_NUMBER in NUMBER
111 /*
112 P_REPAIR_TYPE_ID in NUMBER,
113 P_FROM_FLOW_STATUS_ID in NUMBER,
114 P_TO_FLOW_STATUS_ID in NUMBER,
115 P_WF_ITEM_TYPE in VARCHAR2,
116 P_WF_PROCESS_NAME in VARCHAR2,
117 P_REASON_REQUIRED_FLAG in VARCHAR2,
118 P_CAPTURE_ACTIVITY_FLAG in VARCHAR2,
119 P_ALLOW_ALL_RESP_FLAG in VARCHAR2,
120 P_DESCRIPTION in VARCHAR2
121 */
122 ) is
123 cursor c is select
124 OBJECT_VERSION_NUMBER
125 from CSD_FLWSTS_TRANS_B
126 where FLWSTS_TRAN_ID = P_FLWSTS_TRAN_ID
127 for update of FLWSTS_TRAN_ID nowait;
128 recinfo c%rowtype;
129
130 begin
131
132 open c;
133 fetch c into recinfo;
134 if (c%notfound) then
135 close c;
136 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
137 app_exception.raise_exception;
138 end if;
139 close c;
140
141 if (recinfo.OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER) then
142 null;
143 else
144 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
145 app_exception.raise_exception;
146 end if;
147
148 /*
149 for tlinfo in c1 loop
150 if (tlinfo.BASELANG = 'Y') then
151 if ( ((tlinfo.DESCRIPTION = P_DESCRIPTION)
152 OR ((tlinfo.DESCRIPTION is null) AND (P_DESCRIPTION is null)))
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 */
162
163 return;
164 end LOCK_ROW;
165
166 procedure UPDATE_ROW (
167 P_FLWSTS_TRAN_ID in NUMBER,
168 P_REPAIR_TYPE_ID in NUMBER,
169 P_FROM_FLOW_STATUS_ID in NUMBER,
170 P_TO_FLOW_STATUS_ID in NUMBER,
171 P_WF_ITEM_TYPE in VARCHAR2,
172 P_WF_PROCESS_NAME in VARCHAR2,
173 P_REASON_REQUIRED_FLAG in VARCHAR2,
174 P_CAPTURE_ACTIVITY_FLAG in VARCHAR2,
175 P_ALLOW_ALL_RESP_FLAG in VARCHAR2,
176 P_OBJECT_VERSION_NUMBER in NUMBER,
177 P_DESCRIPTION in VARCHAR2,
178 P_LAST_UPDATE_DATE in DATE,
179 P_LAST_UPDATED_BY in NUMBER,
180 P_LAST_UPDATE_LOGIN in NUMBER
181 ) is
182 begin
183 update CSD_FLWSTS_TRANS_B set
184 REPAIR_TYPE_ID = P_REPAIR_TYPE_ID,
185 FROM_FLOW_STATUS_ID = P_FROM_FLOW_STATUS_ID,
186 TO_FLOW_STATUS_ID = P_TO_FLOW_STATUS_ID,
187 WF_ITEM_TYPE = P_WF_ITEM_TYPE,
188 WF_PROCESS_NAME = P_WF_PROCESS_NAME,
189 REASON_REQUIRED_FLAG = P_REASON_REQUIRED_FLAG,
190 CAPTURE_ACTIVITY_FLAG = P_CAPTURE_ACTIVITY_FLAG,
191 ALLOW_ALL_RESP_FLAG = P_ALLOW_ALL_RESP_FLAG,
192 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER + 1,
193 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
194 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
195 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN
196 where FLWSTS_TRAN_ID = P_FLWSTS_TRAN_ID AND
197 OBJECT_VERSION_NUMBER = P_OBJECT_VERSION_NUMBER;
198
199 if (sql%notfound) then
200 raise no_data_found;
201 end if;
202
203 update CSD_FLWSTS_TRANS_TL set
204 DESCRIPTION = P_DESCRIPTION,
205 LAST_UPDATE_DATE = P_LAST_UPDATE_DATE,
206 LAST_UPDATED_BY = P_LAST_UPDATED_BY,
207 LAST_UPDATE_LOGIN = P_LAST_UPDATE_LOGIN,
208 SOURCE_LANG = userenv('LANG')
209 where FLWSTS_TRAN_ID = P_FLWSTS_TRAN_ID
210 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
211
212 if (sql%notfound) then
213 raise no_data_found;
214 end if;
215 end UPDATE_ROW;
216
217 procedure DELETE_ROW (
218 P_FLWSTS_TRAN_ID in NUMBER
219 ) is
220 begin
221 delete from CSD_FLWSTS_TRANS_TL
222 where FLWSTS_TRAN_ID = P_FLWSTS_TRAN_ID;
223
224 if (sql%notfound) then
225 raise no_data_found;
226 end if;
227
228 delete from CSD_FLWSTS_TRANS_B
229 where FLWSTS_TRAN_ID = P_FLWSTS_TRAN_ID;
230
231 if (sql%notfound) then
232 raise no_data_found;
233 end if;
234 end DELETE_ROW;
235
236 procedure ADD_LANGUAGE
237 is
238 begin
239 delete from CSD_FLWSTS_TRANS_TL T
240 where not exists
241 (select NULL
242 from CSD_FLWSTS_TRANS_B B
243 where B.FLWSTS_TRAN_ID = T.FLWSTS_TRAN_ID
244 );
245
246 update CSD_FLWSTS_TRANS_TL T set (
247 DESCRIPTION
248 ) = (select
249 B.DESCRIPTION
250 from CSD_FLWSTS_TRANS_TL B
251 where B.FLWSTS_TRAN_ID = T.FLWSTS_TRAN_ID
252 and B.LANGUAGE = T.SOURCE_LANG)
253 where (
254 T.FLWSTS_TRAN_ID,
255 T.LANGUAGE
256 ) in (select
257 SUBT.FLWSTS_TRAN_ID,
258 SUBT.LANGUAGE
259 from CSD_FLWSTS_TRANS_TL SUBB, CSD_FLWSTS_TRANS_TL SUBT
260 where SUBB.FLWSTS_TRAN_ID = SUBT.FLWSTS_TRAN_ID
261 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
262 and (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
263 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
264 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
265 ));
266
267 insert into CSD_FLWSTS_TRANS_TL (
268 FLWSTS_TRAN_ID,
269 DESCRIPTION,
270 CREATED_BY,
271 CREATION_DATE,
272 LAST_UPDATED_BY,
273 LAST_UPDATE_DATE,
274 LAST_UPDATE_LOGIN,
275 LANGUAGE,
276 SOURCE_LANG
277 ) select /*+ ORDERED */
278 B.FLWSTS_TRAN_ID,
279 B.DESCRIPTION,
280 B.CREATED_BY,
281 B.CREATION_DATE,
282 B.LAST_UPDATED_BY,
283 B.LAST_UPDATE_DATE,
284 B.LAST_UPDATE_LOGIN,
285 L.LANGUAGE_CODE,
286 B.SOURCE_LANG
287 from CSD_FLWSTS_TRANS_TL B, FND_LANGUAGES L
288 where L.INSTALLED_FLAG in ('I', 'B')
289 and B.LANGUAGE = userenv('LANG')
290 and not exists
291 (select NULL
292 from CSD_FLWSTS_TRANS_TL T
293 where T.FLWSTS_TRAN_ID = B.FLWSTS_TRAN_ID
294 and T.LANGUAGE = L.LANGUAGE_CODE);
295 end ADD_LANGUAGE;
296
297 end CSD_FLWSTS_TRANS_PKG;