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.PSA_TRX_TYPES_ALL_PKG

Source


1 package body PSA_TRX_TYPES_ALL_PKG AS
2 /* $Header: PSAMFTTB.pls 120.5 2006/09/13 13:57:31 agovil ship $ */
3 
4 --===========================FND_LOG.START=====================================
5 g_state_level NUMBER	:=	FND_LOG.LEVEL_STATEMENT;
6 g_proc_level  NUMBER	:=	FND_LOG.LEVEL_PROCEDURE;
7 g_event_level NUMBER	:=	FND_LOG.LEVEL_EVENT;
8 g_excep_level NUMBER	:=	FND_LOG.LEVEL_EXCEPTION;
9 g_error_level NUMBER	:=	FND_LOG.LEVEL_ERROR;
10 g_unexp_level NUMBER	:=	FND_LOG.LEVEL_UNEXPECTED;
11 g_path        VARCHAR2(50)  := 'PSA.PLSQL.PSAMFTTB.PSA_TRX_TYPES_ALL_PKG.';
12 --===========================FND_LOG.END=======================================
13 
14 procedure INSERT_ROW (
15   X_ROWID in out NOCOPY VARCHAR2,
16   X_PSA_TRX_TYPE_ID in NUMBER,
17   X_ATTRIBUTE_CATEGORY in VARCHAR2,
18   X_ATTRIBUTE1 in VARCHAR2,
19   X_ATTRIBUTE2 in VARCHAR2,
20   X_ATTRIBUTE3 in VARCHAR2,
21   X_ATTRIBUTE4 in VARCHAR2,
22   X_ATTRIBUTE5 in VARCHAR2,
23   X_ATTRIBUTE6 in VARCHAR2,
24   X_ATTRIBUTE7 in VARCHAR2,
25   X_ATTRIBUTE8 in VARCHAR2,
26   X_ATTRIBUTE9 in VARCHAR2,
27   X_ATTRIBUTE10 in VARCHAR2,
28   X_ATTRIBUTE11 in VARCHAR2,
29   X_ATTRIBUTE12 in VARCHAR2,
30   X_ATTRIBUTE13 in VARCHAR2,
31   X_ATTRIBUTE14 in VARCHAR2,
32   X_ATTRIBUTE15 in VARCHAR2,
33   X_MODE in VARCHAR2
34   ) is
35     cursor C is select ROWID from PSA_TRX_TYPES_ALL
36       where PSA_TRX_TYPE_ID = X_PSA_TRX_TYPE_ID;
37     X_LAST_UPDATE_DATE DATE;
38     X_LAST_UPDATED_BY NUMBER;
39     X_LAST_UPDATE_LOGIN NUMBER;
40 
41   CURSOR c_check_dup(l_trx_type_id NUMBER) IS
42   SELECT 'Exist'
43   FROM psa_trx_types_all
44   WHERE psa_trx_type_id = l_trx_type_id;
45   l_exist VARCHAR2(60);
46   -- ========================= FND LOG ===========================
47   l_full_path VARCHAR2(100) := g_path || 'INSERT_ROW';
48   -- ========================= FND LOG ===========================
49 begin
50   X_LAST_UPDATE_DATE := SYSDATE;
51   if(X_MODE = 'I') then
52     X_LAST_UPDATED_BY := 1;
53     X_LAST_UPDATE_LOGIN := 0;
54   elsif (X_MODE = 'R') then
55     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
56     if X_LAST_UPDATED_BY is NULL then
57       X_LAST_UPDATED_BY := -1;
58     end if;
59     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
60     if X_LAST_UPDATE_LOGIN is NULL then
61       X_LAST_UPDATE_LOGIN := -1;
62     end if;
63   else
64     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
65     -- ========================= FND LOG ===========================
66     psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
67     -- ========================= FND LOG ===========================
68     app_exception.raise_exception;
69   end if;
70 
71   OPEN c_check_dup(X_PSA_TRX_TYPE_ID);
72   FETCH c_check_dup INTO l_exist;
73 
74   IF (c_check_dup%FOUND) THEN
75     IF (c_check_dup%ISOPEN) THEN
76       CLOSE c_check_dup;
77     END IF;
78 
79     Fnd_Message.Set_Name('PSA', 'PSA_TRX_TYPE_DUP');
80     -- ========================= FND LOG ===========================
81     psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
82     -- ========================= FND LOG ===========================
83     app_exception.raise_exception;
84   END IF;
85 
86   IF (c_check_dup%ISOPEN) THEN
87     CLOSE c_check_dup;
88   END IF;
89 
90   insert into PSA_TRX_TYPES_ALL (
91     PSA_TRX_TYPE_ID,
92     ATTRIBUTE_CATEGORY,
93     ATTRIBUTE1,
94     ATTRIBUTE2,
95     ATTRIBUTE3,
96     ATTRIBUTE4,
97     ATTRIBUTE5,
98     ATTRIBUTE6,
99     ATTRIBUTE7,
100     ATTRIBUTE8,
101     ATTRIBUTE9,
102     ATTRIBUTE10,
103     ATTRIBUTE11,
104     ATTRIBUTE12,
105     ATTRIBUTE13,
106     ATTRIBUTE14,
107     ATTRIBUTE15,
108     CREATION_DATE,
109     CREATED_BY,
110     LAST_UPDATE_DATE,
111     LAST_UPDATED_BY,
112     LAST_UPDATE_LOGIN
113   ) values (
114     X_PSA_TRX_TYPE_ID,
115     X_ATTRIBUTE_CATEGORY,
116     X_ATTRIBUTE1,
117     X_ATTRIBUTE2,
118     X_ATTRIBUTE3,
119     X_ATTRIBUTE4,
120     X_ATTRIBUTE5,
121     X_ATTRIBUTE6,
122     X_ATTRIBUTE7,
123     X_ATTRIBUTE8,
124     X_ATTRIBUTE9,
125     X_ATTRIBUTE10,
126     X_ATTRIBUTE11,
127     X_ATTRIBUTE12,
128     X_ATTRIBUTE13,
129     X_ATTRIBUTE14,
130     X_ATTRIBUTE15,
131     X_LAST_UPDATE_DATE,
132     X_LAST_UPDATED_BY,
133     X_LAST_UPDATE_DATE,
134     X_LAST_UPDATED_BY,
135     X_LAST_UPDATE_LOGIN
136   );
137 
138   open c;
139   fetch c into X_ROWID;
140   if (c%notfound) then
141     close c;
142     raise no_data_found;
143   end if;
144   close c;
145 
146 end INSERT_ROW;
147 
148 procedure LOCK_ROW (
149   X_PSA_TRX_TYPE_ID in NUMBER,
150   X_ATTRIBUTE_CATEGORY in VARCHAR2,
151   X_ATTRIBUTE1 in VARCHAR2,
152   X_ATTRIBUTE2 in VARCHAR2,
153   X_ATTRIBUTE3 in VARCHAR2,
154   X_ATTRIBUTE4 in VARCHAR2,
155   X_ATTRIBUTE5 in VARCHAR2,
156   X_ATTRIBUTE6 in VARCHAR2,
157   X_ATTRIBUTE7 in VARCHAR2,
158   X_ATTRIBUTE8 in VARCHAR2,
159   X_ATTRIBUTE9 in VARCHAR2,
160   X_ATTRIBUTE10 in VARCHAR2,
161   X_ATTRIBUTE11 in VARCHAR2,
162   X_ATTRIBUTE12 in VARCHAR2,
163   X_ATTRIBUTE13 in VARCHAR2,
164   X_ATTRIBUTE14 in VARCHAR2,
165   X_ATTRIBUTE15 in VARCHAR2
166 ) is
167   cursor c1 is select
168       ATTRIBUTE_CATEGORY,
169       ATTRIBUTE1,
170       ATTRIBUTE2,
171       ATTRIBUTE3,
172       ATTRIBUTE4,
173       ATTRIBUTE5,
174       ATTRIBUTE6,
175       ATTRIBUTE7,
176       ATTRIBUTE8,
177       ATTRIBUTE9,
178       ATTRIBUTE10,
179       ATTRIBUTE11,
180       ATTRIBUTE12,
181       ATTRIBUTE13,
182       ATTRIBUTE14,
183       ATTRIBUTE15
184     from PSA_TRX_TYPES_ALL
185     where PSA_TRX_TYPE_ID = X_PSA_TRX_TYPE_ID
186     for update of PSA_TRX_TYPE_ID nowait;
187   tlinfo c1%rowtype;
188   -- ========================= FND LOG ===========================
189   l_full_path VARCHAR2(100) := g_path || 'LOCK_ROW';
190   -- ========================= FND LOG ===========================
191 
192 begin
193   open c1;
194   fetch c1 into tlinfo;
195   if (c1%notfound) then
196     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
197     -- ========================= FND LOG ===========================
198     psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
199     -- ========================= FND LOG ===========================
200     app_exception.raise_exception;
201     close c1;
202     return;
203   end if;
204   close c1;
205 
206       if ( ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
207            OR ((tlinfo.ATTRIBUTE_CATEGORY is null)
208                AND (X_ATTRIBUTE_CATEGORY is null)))
209       AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
210            OR ((tlinfo.ATTRIBUTE1 is null)
211                AND (X_ATTRIBUTE1 is null)))
212       AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
213            OR ((tlinfo.ATTRIBUTE2 is null)
214                AND (X_ATTRIBUTE2 is null)))
215       AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
216            OR ((tlinfo.ATTRIBUTE3 is null)
217                AND (X_ATTRIBUTE3 is null)))
218       AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
219            OR ((tlinfo.ATTRIBUTE4 is null)
220                AND (X_ATTRIBUTE4 is null)))
221       AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
222            OR ((tlinfo.ATTRIBUTE5 is null)
223                AND (X_ATTRIBUTE5 is null)))
224       AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
225            OR ((tlinfo.ATTRIBUTE6 is null)
226                AND (X_ATTRIBUTE6 is null)))
227       AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
228            OR ((tlinfo.ATTRIBUTE7 is null)
229                AND (X_ATTRIBUTE7 is null)))
230       AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
231            OR ((tlinfo.ATTRIBUTE8 is null)
232                AND (X_ATTRIBUTE8 is null)))
233       AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
234            OR ((tlinfo.ATTRIBUTE9 is null)
235                AND (X_ATTRIBUTE9 is null)))
236       AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
237            OR ((tlinfo.ATTRIBUTE10 is null)
238                AND (X_ATTRIBUTE10 is null)))
239       AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
240            OR ((tlinfo.ATTRIBUTE11 is null)
241                AND (X_ATTRIBUTE11 is null)))
242       AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
243            OR ((tlinfo.ATTRIBUTE12 is null)
244                AND (X_ATTRIBUTE12 is null)))
245       AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
246            OR ((tlinfo.ATTRIBUTE13 is null)
247                AND (X_ATTRIBUTE13 is null)))
248       AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
249            OR ((tlinfo.ATTRIBUTE14 is null)
250                AND (X_ATTRIBUTE14 is null)))
251       AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
252            OR ((tlinfo.ATTRIBUTE15 is null)
253                AND (X_ATTRIBUTE15 is null)))
254   ) then
255     null;
256   else
257     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
258     -- ========================= FND LOG ===========================
259     psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
260     -- ========================= FND LOG ===========================
261     app_exception.raise_exception;
262   end if;
263   return;
264 end LOCK_ROW;
265 
266 procedure UPDATE_ROW (
267   X_PSA_TRX_TYPE_ID in NUMBER,
268   X_ATTRIBUTE_CATEGORY in VARCHAR2,
269   X_ATTRIBUTE1 in VARCHAR2,
270   X_ATTRIBUTE2 in VARCHAR2,
271   X_ATTRIBUTE3 in VARCHAR2,
272   X_ATTRIBUTE4 in VARCHAR2,
273   X_ATTRIBUTE5 in VARCHAR2,
274   X_ATTRIBUTE6 in VARCHAR2,
275   X_ATTRIBUTE7 in VARCHAR2,
276   X_ATTRIBUTE8 in VARCHAR2,
277   X_ATTRIBUTE9 in VARCHAR2,
278   X_ATTRIBUTE10 in VARCHAR2,
279   X_ATTRIBUTE11 in VARCHAR2,
280   X_ATTRIBUTE12 in VARCHAR2,
281   X_ATTRIBUTE13 in VARCHAR2,
282   X_ATTRIBUTE14 in VARCHAR2,
283   X_ATTRIBUTE15 in VARCHAR2,
284   X_MODE in VARCHAR2
285   ) is
286     X_LAST_UPDATE_DATE DATE;
287     X_LAST_UPDATED_BY NUMBER;
288     X_LAST_UPDATE_LOGIN NUMBER;
289     -- ========================= FND LOG ===========================
290     l_full_path VARCHAR2(100) := g_path || 'UPDATE_ROW';
291     -- ========================= FND LOG ===========================
292 begin
293   X_LAST_UPDATE_DATE := SYSDATE;
294   if(X_MODE = 'I') then
295     X_LAST_UPDATED_BY := 1;
296     X_LAST_UPDATE_LOGIN := 0;
297   elsif (X_MODE = 'R') then
298     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
299     if X_LAST_UPDATED_BY is NULL then
300       X_LAST_UPDATED_BY := -1;
301     end if;
302     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
303     if X_LAST_UPDATE_LOGIN is NULL then
304       X_LAST_UPDATE_LOGIN := -1;
305     end if;
306   else
307     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
308     -- ========================= FND LOG ===========================
309     psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
310     -- ========================= FND LOG ===========================
311     app_exception.raise_exception;
312   end if;
313   update PSA_TRX_TYPES_ALL set
314     ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
315     ATTRIBUTE1 = X_ATTRIBUTE1,
316     ATTRIBUTE2 = X_ATTRIBUTE2,
317     ATTRIBUTE3 = X_ATTRIBUTE3,
318     ATTRIBUTE4 = X_ATTRIBUTE4,
319     ATTRIBUTE5 = X_ATTRIBUTE5,
320     ATTRIBUTE6 = X_ATTRIBUTE6,
321     ATTRIBUTE7 = X_ATTRIBUTE7,
322     ATTRIBUTE8 = X_ATTRIBUTE8,
323     ATTRIBUTE9 = X_ATTRIBUTE9,
324     ATTRIBUTE10 = X_ATTRIBUTE10,
325     ATTRIBUTE11 = X_ATTRIBUTE11,
326     ATTRIBUTE12 = X_ATTRIBUTE12,
327     ATTRIBUTE13 = X_ATTRIBUTE13,
328     ATTRIBUTE14 = X_ATTRIBUTE14,
329     ATTRIBUTE15 = X_ATTRIBUTE15,
330     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
331     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
332     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
333   where PSA_TRX_TYPE_ID = X_PSA_TRX_TYPE_ID
334   ;
335   if (sql%notfound) then
336     raise no_data_found;
337   end if;
338 end UPDATE_ROW;
339 
340 procedure DELETE_ROW (
341   X_PSA_TRX_TYPE_ID in NUMBER
342 ) is
343 cursor c2 is
344 select distinct 1
345 from RA_CUSTOMER_TRX_ALL a
346 where a.cust_trx_type_id = X_PSA_TRX_TYPE_ID ;
347 num number;
348 x number;
349 -- ========================= FND LOG ===========================
350 l_full_path VARCHAR2(100) := g_path || 'DELETE_ROW';
351 -- ========================= FND LOG ===========================
352 
353 begin
354    open c2;
355   fetch c2 into num;
356    if (c2%notfound) then
357   delete from PSA_TRX_TYPES_ALL
358   where PSA_TRX_TYPE_ID = X_PSA_TRX_TYPE_ID;
359 
360   else
361 
362     FND_MESSAGE.SET_NAME('PSA','PSA_TRX_TYPE_DEL');
363     -- ========================= FND LOG ===========================
364     psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
365     -- ========================= FND LOG ===========================
366     app_exception.raise_exception;
367   if (sql%notfound) then
368     raise no_data_found;
369   end if;
370   end if;
371   close c2;
372  end DELETE_ROW;
373 
374 end PSA_TRX_TYPES_ALL_PKG;