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