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