[Home] [Help]
PACKAGE BODY: APPS.MTL_BILLING_SOURCE_PKG
Source
1 package body mtl_billing_source_pkg AS
2 /* $Header: INVBSRCB.pls 120.1 2010/05/25 10:57:54 damahaja noship $ */
3
4 procedure INSERT_ROW(
5 x_billing_source_id IN NUMBER ,
6 x_name IN VARCHAR2 ,
7 x_description IN VARCHAR2 ,
8 x_procedure_code IN NUMBER,
9 x_billing_source_code IN VARCHAR2 ,
10 x_creation_date IN DATE,
11 x_created_by IN NUMBER,
12 x_last_update_date IN DATE,
13 x_last_updated_by IN NUMBER ,
14 x_last_update_login IN NUMBER
15 ) AS
16
17 BEGIN
18
19 INSERT INTO mtl_billing_sources_b
20 ( billing_source_id ,
21 procedure_code ,
22 billing_source_code ,
23 creation_date ,
24 created_by ,
25 last_update_date ,
26 last_updated_by ,
27 last_update_login
28 )
29 VALUES
30 ( x_billing_source_id ,
31 x_procedure_code ,
32 x_billing_source_code ,
33 x_creation_date ,
34 x_created_by ,
35 x_last_update_date ,
36 x_last_updated_by ,
37 x_last_update_login
38 );
39
40 -- INSERT INTO dp_debug VALUES('2');
41 -- commit;
42
43 INSERT INTO mtl_billing_sources_tl
44 ( billing_source_id ,
45 name ,
46 description ,
47 creation_date ,
48 created_by ,
49 last_update_date ,
50 last_updated_by ,
51 last_update_login ,
52 LANGUAGE ,
53 source_lang
54 )
55 SELECT
56 x_billing_source_id ,
57 x_name ,
58 x_description ,
59 x_creation_date ,
60 x_created_by ,
61 x_last_update_date ,
62 x_last_updated_by ,
63 x_last_update_login ,
64 L.LANGUAGE_CODE,
65 userenv('LANG')
66 from FND_LANGUAGES L
67 where L.INSTALLED_FLAG in ('I', 'B')
68 and not exists
69 (select NULL
70 from mtl_billing_sources_tl T
71 where T.billing_source_id = x_billing_source_id
72 and T.LANGUAGE = L.LANGUAGE_CODE);
73
74 END INSERT_ROW;
75
76 procedure UPDATE_ROW(
77 x_billing_source_id IN NUMBER ,
78 x_name IN VARCHAR2 ,
79 x_description IN VARCHAR2 ,
80 x_procedure_code IN NUMBER,
81 x_billing_source_code IN VARCHAR2,
82 x_creation_date IN DATE,
83 x_created_by IN NUMBER,
84 x_last_update_date IN DATE,
85 x_last_updated_by IN NUMBER ,
86 x_last_update_login IN NUMBER
87 ) AS
88
89
90 CURSOR cur_sources IS SELECT 1 FROM mtl_billing_sources_b
91 WHERE billing_Source_id = x_billing_source_id;
92 l_dummy NUMBER;
93
94
95 BEGIN
96
97 OPEN cur_sources;
98 FETCH cur_sources INTO l_dummy;
99
100 update mtl_billing_sources_b set
101 procedure_code = x_procedure_code,
102 billing_source_code = x_billing_source_code ,
103 last_update_date = x_last_update_date,
104 last_updated_by = x_last_updated_by,
105 last_update_login = x_last_update_login
106 where billing_source_id = x_billing_source_id;
107
108 if (sql%notfound) then
109 raise no_data_found;
110 end if;
111
112 update mtl_billing_sources_tl set
113 name = x_name,
114 description = x_description,
115 last_update_date = x_last_update_date,
116 last_updated_by = x_last_updated_by,
117 last_update_login = x_last_update_login,
118 SOURCE_LANG = userenv('LANG')
119 where billing_source_id = x_billing_source_id
120 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
121
122 if (sql%notfound) then
123 raise no_data_found;
124 end if;
125
126 CLOSE cur_sources;
127
128 END UPDATE_ROW;
129
130
131 procedure DELETE_ROW (
132 x_billing_source_id in NUMBER
133 ) is
134 BEGIN
135
136
137 delete from mtl_billing_sources_tl
138 where billing_source_id = x_billing_source_id;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143
144 delete from mtl_billing_sources_b
145 where billing_source_id = x_billing_source_id;
146
147 if (sql%notfound) THEN
148 raise no_data_found;
149 end if;
150 end DELETE_ROW;
151
152
153 procedure LOCK_ROW (
154 x_billing_source_id IN NUMBER ,
155 x_name IN VARCHAR2 ,
156 x_description IN VARCHAR2 ,
157 x_procedure_code IN NUMBER,
158 x_billing_source_code IN VARCHAR2 ,
159 x_creation_date IN DATE,
160 x_created_by IN NUMBER,
161 x_last_update_date IN DATE,
162 x_last_updated_by IN NUMBER ,
163 x_last_update_login IN NUMBER
164 ) is
165 cursor c is select
166 billing_source_id ,
167 procedure_code ,
168 billing_source_code ,
169 creation_date ,
170 created_by ,
171 last_update_date ,
172 last_updated_by ,
173 last_update_login
174 from mtl_billing_sources_b
175 where billing_source_id = x_billing_source_id
176 for update of billing_source_id nowait;
177 recinfo c%rowtype;
178
179 cursor c1 is select
180 name,
181 DESCRIPTION,
182 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
183 from mtl_billing_sources_tl
184 where billing_source_id = x_billing_source_id
185 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
186 for update of billing_source_id nowait;
187 begin
188 open c;
189 fetch c into recinfo;
190 if (c%notfound) then
191 close c;
192 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
193 app_exception.raise_exception;
194 end if;
195 close c;
196 if (
197 (recinfo.billing_source_id = X_billing_source_id)
198 )
199 then
200 null;
201 else
202 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
203 app_exception.raise_exception;
204 end if;
205
206 for tlinfo in c1 loop
207 if (tlinfo.BASELANG = 'Y') then
208 if ( (tlinfo.name = X_name)
209 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
210 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
211 ) then
212 null;
213 else
214 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
215 app_exception.raise_exception;
216 end if;
217 end if;
218 end loop;
219 return;
220 end LOCK_ROW;
221
222
223 /* Added following procedure for bug 9447716 */
224
225 procedure ADD_LANGUAGE
226 is
227 begin
228
229 delete from mtl_billing_sources_tl T
230 where not exists
231 (select NULL
232 from mtl_billing_sources_b B
233 where B.billing_source_id = T.billing_source_id
234 );
235
236 update mtl_billing_sources_tl T
237 set (
238 name,
239 description
240 ) = (select
241 B.name,
242 B.description
243 from mtl_billing_sources_tl B
244 where B.billing_source_id = T.billing_source_id
245 and B.language = T.source_lang)
246 where (
247 T.billing_source_id,
248 T.language
249 )
250 in (select
251 blsrct.billing_source_id,
252 blsrct.language
253 from mtl_billing_sources_tl blsrcb, mtl_billing_sources_tl blsrct
254 where blsrcb.billing_source_id = blsrct.billing_source_id
255 and blsrcb.language = blsrct.source_lang
256 and (blsrcb.name <> blsrct.name
257 or blsrcb.description <> blsrct.description
258 or (blsrcb.description is null and blsrct.description is not null)
259 or (blsrcb.description is not null and blsrct.description is null)
260 )
261 );
262
263 insert into mtl_billing_sources_tl
264 (
265 billing_source_id,
266 name,
267 description,
268 creation_date,
269 created_by,
270 last_update_date,
271 last_updated_by,
272 last_update_login,
273 language,
274 source_lang
275 ) select /*+ ORDERED */
276 B.billing_source_id,
277 B.name,
278 B.description,
279 B.creation_date,
280 B.created_by,
281 B.last_update_date,
282 B.last_updated_by,
283 B.last_update_login,
284 L.language_code,
285 B.source_lang
286 from mtl_billing_sources_tl B, fnd_languages L
287 where L.installed_flag in ('I', 'B')
288 and B.language = userenv('LANG')
289 and not exists
290 (select NULL
291 from mtl_billing_sources_tl T
292 where T.billing_source_id = B.billing_source_id
293 and T.language = L.language_code);
294
295 end ADD_LANGUAGE;
296
297
298 END;