eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKE_BILLING_METHODS_PKG

Source


1 package body OKE_BILLING_METHODS_PKG as
2 /* $Header: OKEOBMXB.pls 115.5 2002/11/21 23:02:51 ybchen ship $ */
3 procedure INSERT_ROW (
4   X_ROWID 			in out NOCOPY VARCHAR2
5 , X_BILLING_METHOD_CODE 	in 	VARCHAR2
6 , X_CREATION_DATE 		in 	DATE
7 , X_CREATED_BY 			in 	NUMBER
8 , X_LAST_UPDATE_DATE 		in 	DATE
9 , X_LAST_UPDATED_BY 		in 	NUMBER
10 , X_LAST_UPDATE_LOGIN 		in 	NUMBER
11 , X_BILLING_METHOD_NAME 	in 	VARCHAR2
12 , X_INCLUDE_PRICE_FLAG 		in 	VARCHAR2
13 , X_INCLUDE_COST_FLAG  		in 	VARCHAR2
14 , X_INCLUDE_FEE_FLAG  		in 	VARCHAR2
15 , X_DESCRIPTION 		in 	VARCHAR2
16 , X_START_DATE_ACTIVE 		in 	DATE
17 , X_END_DATE_ACTIVE   		in 	DATE
18 , X_ATTRIBUTE_CATEGORY 		in 	VARCHAR2
19 , X_ATTRIBUTE1 			in 	VARCHAR2
20 , X_ATTRIBUTE2 			in 	VARCHAR2
21 , X_ATTRIBUTE3 			in 	VARCHAR2
22 , X_ATTRIBUTE4 			in 	VARCHAR2
23 , X_ATTRIBUTE5 			in 	VARCHAR2
24 , X_ATTRIBUTE6 			in 	VARCHAR2
25 , X_ATTRIBUTE7 			in 	VARCHAR2
26 , X_ATTRIBUTE8 			in 	VARCHAR2
27 , X_ATTRIBUTE9 			in 	VARCHAR2
28 , X_ATTRIBUTE10 		in 	VARCHAR2
29 , X_ATTRIBUTE11 		in 	VARCHAR2
30 , X_ATTRIBUTE12 		in 	VARCHAR2
31 , X_ATTRIBUTE13 		in 	VARCHAR2
32 , X_ATTRIBUTE14 		in 	VARCHAR2
33 , X_ATTRIBUTE15 		in 	VARCHAR2
34 ) is
35   cursor C is select ROWID from OKE_BILLING_METHODS_B
36     where BILLING_METHOD_CODE = X_BILLING_METHOD_CODE
37     ;
38 begin
39   insert into OKE_BILLING_METHODS_B (
40   BILLING_METHOD_CODE
41 , CREATION_DATE
42 , CREATED_BY
43 , LAST_UPDATE_DATE
44 , LAST_UPDATED_BY
45 , LAST_UPDATE_LOGIN
46 , INCLUDE_PRICE_FLAG
47 , INCLUDE_COST_FLAG
48 , INCLUDE_FEE_FLAG
49 , START_DATE_ACTIVE
50 , END_DATE_ACTIVE
51 , ATTRIBUTE_CATEGORY
52 , ATTRIBUTE1
53 , ATTRIBUTE2
54 , ATTRIBUTE3
55 , ATTRIBUTE4
56 , ATTRIBUTE5
57 , ATTRIBUTE6
58 , ATTRIBUTE7
59 , ATTRIBUTE8
60 , ATTRIBUTE9
61 , ATTRIBUTE10
62 , ATTRIBUTE11
63 , ATTRIBUTE12
64 , ATTRIBUTE13
65 , ATTRIBUTE14
66 , ATTRIBUTE15
67   ) values (
68   X_BILLING_METHOD_CODE
69 , X_CREATION_DATE
70 , X_CREATED_BY
71 , X_LAST_UPDATE_DATE
72 , X_LAST_UPDATED_BY
73 , X_LAST_UPDATE_LOGIN
74 , X_INCLUDE_PRICE_FLAG
75 , X_INCLUDE_COST_FLAG
76 , X_INCLUDE_FEE_FLAG
77 , X_START_DATE_ACTIVE
78 , X_END_DATE_ACTIVE
79 , X_ATTRIBUTE_CATEGORY
80 , X_ATTRIBUTE1
81 , X_ATTRIBUTE2
82 , X_ATTRIBUTE3
83 , X_ATTRIBUTE4
84 , X_ATTRIBUTE5
85 , X_ATTRIBUTE6
86 , X_ATTRIBUTE7
87 , X_ATTRIBUTE8
88 , X_ATTRIBUTE9
89 , X_ATTRIBUTE10
90 , X_ATTRIBUTE11
91 , X_ATTRIBUTE12
92 , X_ATTRIBUTE13
93 , X_ATTRIBUTE14
94 , X_ATTRIBUTE15
95 );
96 
97   insert into OKE_BILLING_METHODS_TL (
98   BILLING_METHOD_CODE
99 , LANGUAGE
100 , CREATION_DATE
101 , CREATED_BY
102 , LAST_UPDATE_DATE
103 , LAST_UPDATED_BY
104 , LAST_UPDATE_LOGIN
105 , BILLING_METHOD_NAME
106 , DESCRIPTION
107 , SOURCE_LANG
108   ) select
109   X_BILLING_METHOD_CODE
110 , L.LANGUAGE_CODE
111 , X_CREATION_DATE
112 , X_CREATED_BY
113 , X_LAST_UPDATE_DATE
114 , X_LAST_UPDATED_BY
115 , X_LAST_UPDATE_LOGIN
116 , X_BILLING_METHOD_NAME
117 , X_DESCRIPTION
118 , userenv('LANG')
119   from FND_LANGUAGES L
120  where L.INSTALLED_FLAG in ('I', 'B')
121    and not exists
122     (select NULL
123     from OKE_BILLING_METHODS_TL T
124    where T.BILLING_METHOD_CODE = X_BILLING_METHOD_CODE
125      and T.LANGUAGE = L.LANGUAGE_CODE)
126 ;
127 
128   open c;
129   fetch c into X_ROWID;
130   if (c%notfound) then
131     close c;
132     raise no_data_found;
133   end if;
134   close c;
135 
136 end INSERT_ROW;
137 
138 procedure LOCK_ROW (
139   X_BILLING_METHOD_CODE		in 	VARCHAR2
140 , X_BILLING_METHOD_NAME 	in 	VARCHAR2
141 , X_INCLUDE_PRICE_FLAG 		in 	VARCHAR2
142 , X_INCLUDE_COST_FLAG  		in 	VARCHAR2
143 , X_INCLUDE_FEE_FLAG  		in 	VARCHAR2
144 , X_DESCRIPTION 		in 	VARCHAR2
145 , X_START_DATE_ACTIVE 		in 	DATE
146 , X_END_DATE_ACTIVE   		in 	DATE
147 , X_ATTRIBUTE_CATEGORY 		in 	VARCHAR2
148 , X_ATTRIBUTE1 			in 	VARCHAR2
149 , X_ATTRIBUTE2 			in 	VARCHAR2
150 , X_ATTRIBUTE3 			in 	VARCHAR2
151 , X_ATTRIBUTE4 			in 	VARCHAR2
152 , X_ATTRIBUTE5 			in 	VARCHAR2
153 , X_ATTRIBUTE6 			in 	VARCHAR2
154 , X_ATTRIBUTE7 			in 	VARCHAR2
155 , X_ATTRIBUTE8 			in 	VARCHAR2
156 , X_ATTRIBUTE9 			in 	VARCHAR2
157 , X_ATTRIBUTE10 		in 	VARCHAR2
158 , X_ATTRIBUTE11 		in 	VARCHAR2
159 , X_ATTRIBUTE12 		in 	VARCHAR2
160 , X_ATTRIBUTE13 		in 	VARCHAR2
161 , X_ATTRIBUTE14 		in 	VARCHAR2
162 , X_ATTRIBUTE15 		in 	VARCHAR2
163 ) is
164   cursor c is select
165        BILLING_METHOD_CODE
166      , INCLUDE_PRICE_FLAG
167      , INCLUDE_COST_FLAG
168      , INCLUDE_FEE_FLAG
169      , START_DATE_ACTIVE
170      , END_DATE_ACTIVE
171      , ATTRIBUTE_CATEGORY
172      , ATTRIBUTE1
173      , ATTRIBUTE2
174      , ATTRIBUTE3
175      , ATTRIBUTE4
176      , ATTRIBUTE5
177      , ATTRIBUTE6
178      , ATTRIBUTE7
179      , ATTRIBUTE8
180      , ATTRIBUTE9
181      , ATTRIBUTE10
182      , ATTRIBUTE11
183      , ATTRIBUTE12
184      , ATTRIBUTE13
185      , ATTRIBUTE14
186      , ATTRIBUTE15
187     from OKE_BILLING_METHODS_B
188     where BILLING_METHOD_CODE = X_BILLING_METHOD_CODE
189     for update of BILLING_METHOD_CODE nowait;
190   recinfo c%rowtype;
191 
192   cursor c1 is select
193       BILLING_METHOD_NAME
194      ,DESCRIPTION
195      ,decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
196     from OKE_BILLING_METHODS_TL
197     where BILLING_METHOD_CODE= X_BILLING_METHOD_CODE
198     and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
199     for update of BILLING_METHOD_CODE nowait;
200   tlinfo c1%rowtype;
201 
202 begin
203   open c;
204   fetch c into recinfo;
205   if (c%notfound) then
206     close c;
207     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
208     app_exception.raise_exception;
209   end if;
210   close c;
211 
212   if (    (recinfo.BILLING_METHOD_CODE = X_BILLING_METHOD_CODE)
213       AND ((recinfo.INCLUDE_PRICE_FLAG = X_INCLUDE_PRICE_FLAG)
214            OR ((recinfo.INCLUDE_PRICE_FLAG is null) AND (X_INCLUDE_PRICE_FLAG is null)))
215       AND ((recinfo.INCLUDE_COST_FLAG = X_INCLUDE_COST_FLAG)
216            OR ((recinfo.INCLUDE_COST_FLAG is null) AND (X_INCLUDE_COST_FLAG is null)))
217       AND ((recinfo.INCLUDE_FEE_FLAG = X_INCLUDE_FEE_FLAG)
218            OR ((recinfo.INCLUDE_FEE_FLAG is null) AND (X_INCLUDE_FEE_FLAG is null)))
219       AND ((recinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
220            OR ((recinfo.START_DATE_ACTIVE is null) AND (X_START_DATE_ACTIVE is null)))
221       AND ((recinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
222            OR ((recinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
223       AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
224            OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
225       AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
226            OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
227       AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
228            OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
229       AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
230            OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
231       AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
232            OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
233       AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
234            OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
235       AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
236            OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
237       AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
238            OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
239       AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
240            OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
241       AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
242            OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
243       AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
244            OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
245       AND ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
246            OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
247       AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
248            OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
249       AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
250            OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
251       AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
252            OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
253       AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
254            OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
255   ) then
256     null;
257   else
258     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
259     app_exception.raise_exception;
260   end if;
261 
262   for tlinfo in c1 loop
263     if (tlinfo.BASELANG = 'Y') then
264       if (    ((tlinfo.BILLING_METHOD_NAME = X_BILLING_METHOD_NAME)
265                OR ((tlinfo.BILLING_METHOD_NAME is null) AND (X_BILLING_METHOD_NAME is null)))
266           AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
267                OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
268       ) then
269         null;
270       else
271         fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
272         app_exception.raise_exception;
273       end if;
274     end if;
275   end loop;
276 
277   return;
278 end LOCK_ROW;
279 
280 
281 
282 procedure UPDATE_ROW (
283   X_BILLING_METHOD_CODE 	in 	VARCHAR2
284 , X_LAST_UPDATE_DATE 		in 	DATE
285 , X_LAST_UPDATED_BY 		in 	NUMBER
286 , X_LAST_UPDATE_LOGIN 		in 	NUMBER
287 , X_BILLING_METHOD_NAME 	in 	VARCHAR2
288 , X_INCLUDE_PRICE_FLAG 		in 	VARCHAR2
289 , X_INCLUDE_COST_FLAG  		in 	VARCHAR2
290 , X_INCLUDE_FEE_FLAG  		in 	VARCHAR2
291 , X_DESCRIPTION 		in 	VARCHAR2
292 , X_START_DATE_ACTIVE 		in 	DATE
293 , X_END_DATE_ACTIVE   		in 	DATE
294 , X_ATTRIBUTE_CATEGORY 		in 	VARCHAR2
295 , X_ATTRIBUTE1 			in 	VARCHAR2
296 , X_ATTRIBUTE2 			in 	VARCHAR2
297 , X_ATTRIBUTE3 			in 	VARCHAR2
298 , X_ATTRIBUTE4 			in 	VARCHAR2
299 , X_ATTRIBUTE5 			in 	VARCHAR2
300 , X_ATTRIBUTE6 			in 	VARCHAR2
301 , X_ATTRIBUTE7 			in 	VARCHAR2
302 , X_ATTRIBUTE8 			in 	VARCHAR2
303 , X_ATTRIBUTE9 			in 	VARCHAR2
304 , X_ATTRIBUTE10 		in 	VARCHAR2
305 , X_ATTRIBUTE11 		in 	VARCHAR2
306 , X_ATTRIBUTE12 		in 	VARCHAR2
307 , X_ATTRIBUTE13 		in 	VARCHAR2
308 , X_ATTRIBUTE14 		in 	VARCHAR2
309 , X_ATTRIBUTE15 		in 	VARCHAR2
310 ) is
311 begin
312   update OKE_BILLING_METHODS_B set
313   LAST_UPDATE_DATE      	= X_LAST_UPDATE_DATE
314 , LAST_UPDATED_BY 		= X_LAST_UPDATED_BY
315 , LAST_UPDATE_LOGIN   		= X_LAST_UPDATE_LOGIN
316 , INCLUDE_PRICE_FLAG 		= X_INCLUDE_PRICE_FLAG
317 , INCLUDE_COST_FLAG	        = X_INCLUDE_COST_FLAG
318 , INCLUDE_FEE_FLAG	        = X_INCLUDE_FEE_FLAG
319 , START_DATE_ACTIVE 		= X_START_DATE_ACTIVE
320 , END_DATE_ACTIVE   		= X_END_DATE_ACTIVE
321 , ATTRIBUTE_CATEGORY		= X_ATTRIBUTE_CATEGORY
322 , ATTRIBUTE1            	= X_ATTRIBUTE1
323 , ATTRIBUTE2			= X_ATTRIBUTE2
324 , ATTRIBUTE3    		= X_ATTRIBUTE3
325 , ATTRIBUTE4        		= X_ATTRIBUTE4
326 , ATTRIBUTE5            	= X_ATTRIBUTE5
327 , ATTRIBUTE6			= X_ATTRIBUTE6
328 , ATTRIBUTE7    		= X_ATTRIBUTE7
329 , ATTRIBUTE8        		= X_ATTRIBUTE8
330 , ATTRIBUTE9            	= X_ATTRIBUTE9
331 , ATTRIBUTE10           	= X_ATTRIBUTE10
332 , ATTRIBUTE11			= X_ATTRIBUTE11
333 , ATTRIBUTE12    		= X_ATTRIBUTE12
334 , ATTRIBUTE13        		= X_ATTRIBUTE13
335 , ATTRIBUTE14           	= X_ATTRIBUTE14
336 , ATTRIBUTE15           	= X_ATTRIBUTE15
337 where BILLING_METHOD_CODE 	= X_BILLING_METHOD_CODE
338 ;
339 
340   if (sql%notfound) then
341     raise no_data_found;
342   end if;
343 
344   update OKE_BILLING_METHODS_TL set
345   LAST_UPDATE_DATE      = X_LAST_UPDATE_DATE
346 , LAST_UPDATED_BY 	= X_LAST_UPDATED_BY
347 , LAST_UPDATE_LOGIN   	= X_LAST_UPDATE_LOGIN
348 , BILLING_METHOD_NAME 	= X_BILLING_METHOD_NAME
349 , DESCRIPTION       	= X_DESCRIPTION
350 , SOURCE_LANG 		= userenv('LANG')
351 where BILLING_METHOD_CODE       = X_BILLING_METHOD_CODE
352   and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
353 ;
354 
358 
355   if (sql%notfound) then
356     raise no_data_found;
357   end if;
359 end UPDATE_ROW;
360 
361 procedure ADD_LANGUAGE
362 is
363 begin
364   delete from OKE_BILLING_METHODS_TL T
365   where not exists
366     (select NULL
367     from OKE_BILLING_METHODS_B B
368     where B.BILLING_METHOD_CODE = T.BILLING_METHOD_CODE
369     );
370 
371   update OKE_BILLING_METHODS_TL T set (
372       BILLING_METHOD_NAME,
373       DESCRIPTION
374     ) = (select
375       B.BILLING_METHOD_NAME,
376       B.DESCRIPTION
377     from OKE_BILLING_METHODS_TL B
378     where B.BILLING_METHOD_CODE = T.BILLING_METHOD_CODE
379     and B.LANGUAGE = T.SOURCE_LANG)
380   where (
381       T.BILLING_METHOD_CODE,
382       T.LANGUAGE
383   ) in (select
384       SUBT.BILLING_METHOD_CODE,
385       SUBT.LANGUAGE
386     from OKE_BILLING_METHODS_TL SUBB, OKE_BILLING_METHODS_TL SUBT
387     where SUBB.BILLING_METHOD_CODE = SUBT.BILLING_METHOD_CODE
388     and SUBB.LANGUAGE = SUBT.SOURCE_LANG
389     and (SUBB.BILLING_METHOD_NAME <> SUBT.BILLING_METHOD_NAME
390       or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
391       or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
392       or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
393   ));
394 
395   insert into OKE_BILLING_METHODS_TL (
396     DESCRIPTION,
397     BILLING_METHOD_CODE,
398     CREATION_DATE,
399     CREATED_BY,
400     LAST_UPDATE_DATE,
401     LAST_UPDATED_BY,
402     LAST_UPDATE_LOGIN,
403     BILLING_METHOD_NAME,
404     LANGUAGE,
405     SOURCE_LANG
406   ) select
407     B.DESCRIPTION,
408     B.BILLING_METHOD_CODE,
409     B.CREATION_DATE,
410     B.CREATED_BY,
411     B.LAST_UPDATE_DATE,
412     B.LAST_UPDATED_BY,
413     B.LAST_UPDATE_LOGIN,
414     B.BILLING_METHOD_NAME,
415     L.LANGUAGE_CODE,
416     B.SOURCE_LANG
417   from OKE_BILLING_METHODS_TL B, FND_LANGUAGES L
418   where L.INSTALLED_FLAG in ('I', 'B')
419   and B.LANGUAGE = userenv('LANG')
420   and not exists
421     (select NULL
422     from OKE_BILLING_METHODS_TL T
423     where T.BILLING_METHOD_CODE = B.BILLING_METHOD_CODE
424     and T.LANGUAGE = L.LANGUAGE_CODE);
425 end ADD_LANGUAGE;
426 
427 end OKE_BILLING_METHODS_PKG;