[Home] [Help]
PACKAGE BODY: APPS.PON_EMD_PAYMENT_TYPES_UTIL
Source
1 PACKAGE BODY PON_EMD_PAYMENT_TYPES_UTIL AS
2 /* $Header: ponemdutilb.pls 120.2 2010/04/13 09:41:41 irasoolm noship $ */
3
4 PROCEDURE Insert_Row(
5 X_PAYMENT_TYPE_CODE IN VARCHAR2,
6 X_ORG_ID IN NUMBER,
7 X_NAME IN VARCHAR2,
8 X_DESCRIPTION IN VARCHAR2,
9 X_START_DATE_ACTIVE IN DATE,
10 X_END_DATE_ACTIVE IN DATE,
11 X_ENABLED_FLAG IN VARCHAR2,
12 X_RECEIPT_METHOD_ID IN NUMBER,
13 X_REFUND_PAYMENT_METHOD IN VARCHAR2,
14 X_CREATION_DATE IN VARCHAR2,
15 X_CREATED_BY IN NUMBER,
16 X_LAST_UPDATE_DATE IN DATE,
17 X_LAST_UPDATED_BY IN NUMBER,
18 X_LAST_UPDATE_LOGIN IN NUMBER,
19 X_REQUEST_ID IN NUMBER,
20 X_PROGRAM_APPLICATION_ID IN NUMBER,
21 X_PROGRAM_ID IN NUMBER,
22 X_PROGRAM_UPDATE_DATE IN DATE
23 ) IS
24 -- CURSOR C IS SELECT rowid FROM oe_system_parameters_all
25 /** WHERE nvl(org_id, -99) = nvl(X_Organization_Id, -99); **/
26 -- NVL of -99 is removed as per SSA
27 -- WHERE org_id = X_Organization_Id;
28 -- WHERE nvl(org_id, -99) = nvl(X_Organization_Id, -99);
29
30 L_LANGUAGE VARCHAR2(4);
31 L_SOURCE_LANG VARCHAR2(4);
32 L_ORG_ID NUMBER := 0;
33
34 BEGIN
35
36 L_ORG_ID := X_ORG_ID;
37
38
39 INSERT INTO PON_EMD_PAYMENT_TYPES_ALL
40 (
41 PAYMENT_TYPE_CODE ,
42 ORG_ID ,
43 START_DATE_ACTIVE ,
44 END_DATE_ACTIVE ,
45 ENABLED_FLAG ,
46 RECEIPT_METHOD_ID ,
47 REFUND_PAYMENT_METHOD ,
48 CREATION_DATE ,
49 CREATED_BY ,
50 LAST_UPDATE_DATE ,
51 LAST_UPDATED_BY ,
52 LAST_UPDATE_LOGIN ,
53 REQUEST_ID ,
54 PROGRAM_APPLICATION_ID,
55 PROGRAM_ID ,
56 PROGRAM_UPDATE_DATE
57 )
58 VALUES
59 (
60 X_PAYMENT_TYPE_CODE ,
61 X_ORG_ID ,
62 X_START_DATE_ACTIVE ,
63 X_END_DATE_ACTIVE ,
64 X_ENABLED_FLAG ,
65 X_RECEIPT_METHOD_ID ,
66 X_REFUND_PAYMENT_METHOD ,
67 X_CREATION_DATE ,
68 X_CREATED_BY ,
69 X_LAST_UPDATE_DATE ,
70 X_LAST_UPDATED_BY ,
71 X_LAST_UPDATE_LOGIN ,
72 X_REQUEST_ID ,
73 X_PROGRAM_APPLICATION_ID,
74 X_PROGRAM_ID ,
75 X_PROGRAM_UPDATE_DATE
76 );
77
78 INSERT INTO PON_EMD_PAYMENT_TYPES_TL
79 (
80 PAYMENT_TYPE_CODE ,
81 ORG_ID ,
82 NAME ,
83 DESCRIPTION ,
84 CREATION_DATE ,
85 CREATED_BY ,
86 LAST_UPDATE_DATE ,
87 LAST_UPDATED_BY ,
88 LAST_UPDATE_LOGIN ,
89 REQUEST_ID ,
90 PROGRAM_APPLICATION_ID,
91 PROGRAM_ID ,
92 PROGRAM_UPDATE_DATE ,
93 LANGUAGE ,
94 SOURCE_LANG
95 )
96 SELECT
97 X_PAYMENT_TYPE_CODE,
98 X_ORG_ID,
99 X_NAME,
100 X_DESCRIPTION,
101 X_CREATION_DATE,
102 X_CREATED_BY,
103 X_LAST_UPDATE_DATE,
104 X_LAST_UPDATED_BY,
105 X_LAST_UPDATE_LOGIN,
106 X_PROGRAM_APPLICATION_ID,
107 X_PROGRAM_ID,
108 X_REQUEST_ID,
109 X_PROGRAM_UPDATE_DATE,
110 L.LANGUAGE_CODE,
111 USERENV('LANG')
112 FROM FND_LANGUAGES L
113 WHERE
114 L.INSTALLED_FLAG IN ('I', 'B')
115 AND NOT EXISTS
116 (
117 SELECT 1
118 FROM
119 PON_EMD_PAYMENT_TYPES_TL T
120 WHERE
121 T.PAYMENT_TYPE_CODE = X_PAYMENT_TYPE_CODE
122 AND
123 T.LANGUAGE = L.LANGUAGE_CODE
124 AND
125 ORG_ID=L_ORG_ID
126 );
127
128 END Insert_Row;
129
130
131 PROCEDURE Update_Row(
132 X_PAYMENT_TYPE_CODE IN VARCHAR2,
133 X_ORG_ID IN NUMBER,
134 X_NAME IN VARCHAR2,
135 X_DESCRIPTION IN VARCHAR2,
136 X_START_DATE_ACTIVE IN DATE,
137 X_END_DATE_ACTIVE IN DATE,
138 X_ENABLED_FLAG IN VARCHAR2,
139 X_RECEIPT_METHOD_ID IN NUMBER,
140 X_REFUND_PAYMENT_METHOD IN VARCHAR2,
141 X_LAST_UPDATE_DATE IN DATE,
142 X_LAST_UPDATED_BY IN NUMBER,
143 X_LAST_UPDATE_LOGIN IN NUMBER,
144 X_REQUEST_ID IN NUMBER,
145 X_PROGRAM_APPLICATION_ID IN NUMBER,
146 X_PROGRAM_ID IN NUMBER,
147 X_PROGRAM_UPDATE_DATE IN DATE
148 ) IS
149 BEGIN
150
151
152 UPDATE PON_EMD_PAYMENT_TYPES_TL
153 SET
154 NAME = X_NAME,
155 DESCRIPTION = X_DESCRIPTION,
156 REFUND_PAYMENT_METHOD = X_REFUND_PAYMENT_METHOD,
157 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
158 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
159 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
160 SOURCE_LANG = USERENV('LANG')
161 WHERE
162 NVL(ORG_ID,-1) = NVL(X_ORG_ID, -1)
163 AND
164 PAYMENT_TYPE_CODE = X_PAYMENT_TYPE_CODE
165 AND
166 USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
167
168 UPDATE PON_EMD_PAYMENT_TYPES_ALL
169 SET
170 PAYMENT_TYPE_CODE = X_PAYMENT_TYPE_CODE,
171 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
172 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
173 ENABLED_FLAG = X_ENABLED_FLAG,
174 RECEIPT_METHOD_ID = X_RECEIPT_METHOD_ID,
175 REFUND_PAYMENT_METHOD = X_REFUND_PAYMENT_METHOD,
176 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
177 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
178 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
179 REQUEST_ID = X_REQUEST_ID,
180 PROGRAM_ID = X_PROGRAM_ID,
181 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
182 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
183 WHERE
184 NVL(ORG_ID,-1) = NVL(X_ORG_ID, -1)
185 AND
186 PAYMENT_TYPE_CODE = X_PAYMENT_TYPE_CODE;
187
188 IF (SQL%NOTFOUND) then
189 Raise NO_DATA_FOUND;
190 END IF;
191 END Update_Row;
192
193
194 PROCEDURE Translate_Row(
195 X_PAYMENT_TYPE_CODE IN VARCHAR2,
196 X_ORG_ID IN NUMBER,
197 X_NAME IN VARCHAR2,
198 X_DESCRIPTION IN VARCHAR2,
199 X_OWNER IN VARCHAR2
200 ) IS
201 L_USER_ID NUMBER :=0;
202 BEGIN
203 L_USER_ID :=FND_LOAD_UTIL.OWNER_ID(X_OWNER); --SEED DATA VERSION CHANGES
204
205 UPDATE PON_EMD_PAYMENT_TYPES_TL
206 SET
207 ORG_ID = X_ORG_ID,
208 PAYMENT_TYPE_CODE = X_PAYMENT_TYPE_CODE,
209 NAME = X_NAME,
210 DESCRIPTION = X_DESCRIPTION,
211 LAST_UPDATE_DATE = SYSDATE,
212 LAST_UPDATED_BY = L_USER_ID,
213 LAST_UPDATE_LOGIN = L_USER_ID,
214 SOURCE_LANG = USERENV('LANG')
215 WHERE
216 NVL(ORG_ID,-1) = NVL(X_ORG_ID, -1)
217 AND
218 PAYMENT_TYPE_CODE = X_PAYMENT_TYPE_CODE
219 AND
220 USERENV('LANG') IN (LANGUAGE, SOURCE_LANG);
221
222 END Translate_Row;
223
224
225 PROCEDURE LOAD_ROW(
226 X_PAYMENT_TYPE_CODE IN VARCHAR2,
227 X_ORG_ID IN NUMBER,
228 X_OWNER IN VARCHAR2,
229 X_NAME IN VARCHAR2,
230 X_DESCRIPTION IN VARCHAR2,
231 X_START_DATE_ACTIVE IN DATE,
232 X_END_DATE_ACTIVE IN DATE,
233 X_ENABLED_FLAG IN VARCHAR2,
234 X_RECEIPT_METHOD_ID IN NUMBER,
235 X_REFUND_PAYMENT_METHOD IN VARCHAR2,
236 X_LAST_UPDATE_DATE IN DATE,
237 X_LAST_UPDATED_BY IN NUMBER,
238 X_LAST_UPDATE_LOGIN IN NUMBER,
239 X_REQUEST_ID IN NUMBER,
240 X_PROGRAM_APPLICATION_ID IN NUMBER,
241 X_PROGRAM_ID IN NUMBER,
242 X_PROGRAM_UPDATE_DATE IN DATE
243 ) IS
244
245 L_USER_ID NUMBER := 0;
246 L_ORG_ID NUMBER := 0;
247 L_TRANSACTION_TYPE_ID NUMBER := 0;
248 L_ROWID VARCHAR2(240) := NULL;
249 L_DB_USER_ID NUMBER := 0;
250 L_VALID_RELEASE BOOLEAN :=FALSE;
251
252 BEGIN
253
254 L_USER_ID :=FND_LOAD_UTIL.OWNER_ID(X_OWNER);
255
256 SELECT ORG_ID,LAST_UPDATED_BY INTO L_ORG_ID,L_DB_USER_ID
257 FROM
258 PON_EMD_PAYMENT_TYPES_ALL
259 WHERE
260 PAYMENT_TYPE_CODE=X_PAYMENT_TYPE_CODE
261 AND
262 Nvl(ORG_ID,-1)=Nvl(X_ORG_ID,-1)
263 AND
264 ROWNUM=1;
265
266 --seed data version start
267 IF (L_DB_USER_ID <= L_USER_ID)
268 OR (L_DB_USER_ID IN (0,1,2)
269 AND L_USER_ID IN (0,1,2))
270 THEN
271 L_VALID_RELEASE :=TRUE ;
272 END IF;
273 IF L_VALID_RELEASE THEN
274 --seed data version end
275 Update_Row(
276 X_PAYMENT_TYPE_CODE => X_PAYMENT_TYPE_CODE,
277 X_ORG_ID => X_ORG_ID,
278 X_NAME => X_NAME,
279 X_DESCRIPTION => X_DESCRIPTION,
280 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
281 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
282 X_ENABLED_FLAG => X_ENABLED_FLAG,
283 X_RECEIPT_METHOD_ID => X_RECEIPT_METHOD_ID,
284 X_REFUND_PAYMENT_METHOD => X_REFUND_PAYMENT_METHOD,
285 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
286 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
287 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
288 X_REQUEST_ID => X_REQUEST_ID,
289 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
290 X_PROGRAM_ID => X_PROGRAM_ID,
291 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE
292 );
293 end if;
294 exception
295
296 when no_data_found then
297
298 Begin
299
300 Insert_Row(
301 X_PAYMENT_TYPE_CODE => X_PAYMENT_TYPE_CODE,
302 X_ORG_ID => X_ORG_ID,
303 X_NAME => X_NAME,
304 X_DESCRIPTION => X_DESCRIPTION,
305 X_START_DATE_ACTIVE => X_START_DATE_ACTIVE,
306 X_END_DATE_ACTIVE => X_END_DATE_ACTIVE,
307 X_ENABLED_FLAG => X_ENABLED_FLAG,
308 X_RECEIPT_METHOD_ID => X_RECEIPT_METHOD_ID,
309 X_REFUND_PAYMENT_METHOD => X_REFUND_PAYMENT_METHOD,
310 X_CREATION_DATE => SYSDATE,
311 X_CREATED_BY => l_user_id,
312 X_LAST_UPDATE_DATE => X_LAST_UPDATE_DATE,
313 X_LAST_UPDATED_BY => X_LAST_UPDATED_BY,
314 X_LAST_UPDATE_LOGIN => X_LAST_UPDATE_LOGIN,
315 X_REQUEST_ID => X_REQUEST_ID,
316 X_PROGRAM_APPLICATION_ID => X_PROGRAM_APPLICATION_ID,
317 X_PROGRAM_ID => X_PROGRAM_ID,
318 X_PROGRAM_UPDATE_DATE => X_PROGRAM_UPDATE_DATE
319 );
320 Exception
321
322 when others then
323 raise;
324
325 END;
326
327
328 END LOAD_ROW;
329
330 PROCEDURE add_language IS
331
332 BEGIN
333
334 INSERT INTO PON_EMD_PAYMENT_TYPES_TL
335 (
336 PAYMENT_TYPE_CODE ,
337 ORG_ID ,
338 NAME ,
339 DESCRIPTION ,
340 CREATION_DATE ,
341 CREATED_BY ,
342 LAST_UPDATE_DATE ,
343 LAST_UPDATED_BY ,
344 LAST_UPDATE_LOGIN ,
345 REQUEST_ID ,
346 PROGRAM_APPLICATION_ID,
347 PROGRAM_ID ,
348 PROGRAM_UPDATE_DATE ,
349 LANGUAGE ,
350 SOURCE_LANG
351 )
352 SELECT
353 EMDTL1.PAYMENT_TYPE_CODE,
354 EMDTL1.ORG_ID,
355 EMDTL1.NAME,
356 EMDTL1.DESCRIPTION,
357 EMDTL1.CREATION_DATE,
358 EMDTL1.CREATED_BY,
359 EMDTL1.LAST_UPDATE_DATE,
360 EMDTL1.LAST_UPDATED_BY,
361 EMDTL1.LAST_UPDATE_LOGIN,
362 EMDTL1.PROGRAM_APPLICATION_ID,
363 EMDTL1.PROGRAM_ID,
364 EMDTL1.REQUEST_ID,
365 EMDTL1.PROGRAM_UPDATE_DATE,
366 LANG.LANGUAGE_CODE,
367 EMDTL1.SOURCE_LANG
368 FROM PON_EMD_PAYMENT_TYPES_TL EMDTL1,
369 FND_LANGUAGES LANG
370 WHERE
371 EMDTL1.LANGUAGE=UserEnv('LANG')
372 AND
373 LANG.INSTALLED_FLAG IN ('I', 'B')
374 AND NOT EXISTS (SELECT NULL FROM PON_EMD_PAYMENT_TYPES_TL EMDTL2
375 WHERE
376 EMDTL2.ORG_ID=EMDTL1.ORG_ID
377 AND
378 EMDTL2.PAYMENT_TYPE_CODE=EMDTL1.PAYMENT_TYPE_CODE
379 AND
380 EMDTL2.LANGUAGE=LANG.LANGUAGE_CODE
381 );
382
383 END;
384
385
386 END PON_EMD_PAYMENT_TYPES_UTIL;