[Home] [Help]
PACKAGE BODY: APPS.PSA_IMPLEMENTATION_ALL_PKG
Source
1 package body PSA_IMPLEMENTATION_ALL_PKG as
2 /* $Header: PSAIMPLB.pls 120.5 2006/09/13 12:05:59 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.PSAIMPLB.PSA_IMPLEMENTATION_ALL_PKG.';
12 --===========================FND_LOG.END=======================================
13
14 procedure INSERT_ROW (
15 X_ROWID in out NOCOPY VARCHAR2,
16 X_PSA_FEATURE in VARCHAR2,
17 X_ORG_ID in NUMBER,
18 X_STATUS in VARCHAR2,
19 X_ALLOCATION_METHOD in VARCHAR2,
20 X_MAPPING_REQUIRED in VARCHAR2,
21 X_PROGRAM_INSTALLED in VARCHAR2,
22 X_MODE in VARCHAR2
23 ) is
24 cursor C is select ROWID from PSA_IMPLEMENTATION_ALL
25 where PSA_FEATURE = X_PSA_FEATURE
26 and ORG_ID = X_ORG_ID;
27 X_LAST_UPDATE_DATE DATE;
28 X_LAST_UPDATED_BY NUMBER;
29 X_LAST_UPDATE_LOGIN NUMBER;
30 l_rowid NUMBER;
31 -- ======================== FND LOG ======================
32 l_full_path VARCHAR2(100) := g_path || 'INSERT_ROW';
33 -- ======================== FND LOG ======================
34 begin
35
36 l_rowid := X_ROWID;
37
38 X_LAST_UPDATE_DATE := SYSDATE;
39 if(X_MODE = 'I') then
40 X_LAST_UPDATED_BY := 1;
41 X_LAST_UPDATE_LOGIN := 0;
42 elsif (X_MODE = 'R') then
43 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
44 if X_LAST_UPDATED_BY is NULL then
45 X_LAST_UPDATED_BY := -1;
46 end if;
47 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
48 if X_LAST_UPDATE_LOGIN is NULL then
49 X_LAST_UPDATE_LOGIN := -1;
50 end if;
51 else
52 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
53 -- ========================= FND LOG ===========================
54 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
55 -- ========================= FND LOG ===========================
56 app_exception.raise_exception;
57 end if;
58 insert into PSA_IMPLEMENTATION_ALL (
59 PSA_FEATURE,
60 STATUS,
61 ORG_ID,
62 ALLOCATION_METHOD,
63 MAPPING_REQUIRED,
64 PROGRAM_INSTALLED,
65 CREATION_DATE,
66 CREATED_BY,
67 LAST_UPDATE_DATE,
68 LAST_UPDATED_BY,
69 LAST_UPDATE_LOGIN
70 ) values (
71 X_PSA_FEATURE,
72 X_STATUS,
73 X_ORG_ID,
74 X_ALLOCATION_METHOD,
75 X_MAPPING_REQUIRED,
76 X_PROGRAM_INSTALLED,
77 X_LAST_UPDATE_DATE,
78 X_LAST_UPDATED_BY,
79 X_LAST_UPDATE_DATE,
80 X_LAST_UPDATED_BY,
81 X_LAST_UPDATE_LOGIN
82 );
83
84 open c;
85 fetch c into X_ROWID;
86 if (c%notfound) then
87 close c;
88 raise no_data_found;
89 end if;
90 close c;
91
92 exception
93 when others then
94 X_ROWID := l_rowid;
95 raise;
96
97 end INSERT_ROW;
98
99 procedure LOCK_ROW (
100 X_PSA_FEATURE in VARCHAR2,
101 X_ORG_ID in NUMBER,
102 X_STATUS in VARCHAR2,
103 X_ALLOCATION_METHOD in VARCHAR2,
104 X_MAPPING_REQUIRED in VARCHAR2,
105 X_PROGRAM_INSTALLED in VARCHAR2
106 ) is
107 cursor c1 is select
108 STATUS,
109 ALLOCATION_METHOD,
110 MAPPING_REQUIRED,
111 PROGRAM_INSTALLED
112 from PSA_IMPLEMENTATION_ALL
113 where PSA_FEATURE = X_PSA_FEATURE
114 and ORG_ID = X_ORG_ID
115 for update of PSA_FEATURE nowait;
116 tlinfo c1%rowtype;
117 -- ========================= FND LOG ===========================
118 l_full_path VARCHAR2(100) := g_path || 'LOCK_ROW';
119 -- ========================= FND LOG ===========================
120 begin
121 open c1;
122 fetch c1 into tlinfo;
123 if (c1%notfound) then
124 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
125 -- ========================= FND LOG ===========================
126 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
127 -- ========================= FND LOG ===========================
128 app_exception.raise_exception;
129 close c1;
130 return;
131 end if;
132 close c1;
133
134 if ( ((tlinfo.STATUS = X_STATUS)
135 OR ((tlinfo.STATUS is null)
136 AND (X_STATUS is null)))
137 AND ((tlinfo.ALLOCATION_METHOD = X_ALLOCATION_METHOD)
138 OR ((tlinfo.ALLOCATION_METHOD is null)
139 AND (X_ALLOCATION_METHOD is null)))
140 AND ((tlinfo.MAPPING_REQUIRED = X_MAPPING_REQUIRED)
141 OR ((tlinfo.MAPPING_REQUIRED is null)
142 AND (X_MAPPING_REQUIRED is null)))
143 AND ((tlinfo.PROGRAM_INSTALLED = X_PROGRAM_INSTALLED)
144 OR ((tlinfo.PROGRAM_INSTALLED is null)
145 AND (X_PROGRAM_INSTALLED is null)))
146 ) then
147 null;
148 else
149 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
150 -- ========================= FND LOG ===========================
151 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
152 -- ========================= FND LOG ===========================
153 app_exception.raise_exception;
154 end if;
155 return;
156 end LOCK_ROW;
157
158 procedure UPDATE_ROW (
159 X_PSA_FEATURE in VARCHAR2,
160 X_ORG_ID in NUMBER,
161 X_STATUS in VARCHAR2,
162 X_ALLOCATION_METHOD in VARCHAR2,
163 X_MAPPING_REQUIRED in VARCHAR2,
164 X_PROGRAM_INSTALLED in VARCHAR2,
165 X_MODE in VARCHAR2
166 ) is
167 X_LAST_UPDATE_DATE DATE;
168 X_LAST_UPDATED_BY NUMBER;
169 X_LAST_UPDATE_LOGIN NUMBER;
170 -- ========================= FND LOG ===========================
171 l_full_path VARCHAR2(100) := g_path || 'UPDATE_ROW';
172 -- ========================= FND LOG ===========================
173 begin
174 X_LAST_UPDATE_DATE := SYSDATE;
175 if(X_MODE = 'I') then
176 X_LAST_UPDATED_BY := 1;
177 X_LAST_UPDATE_LOGIN := 0;
178 elsif (X_MODE = 'R') then
179 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
180 if X_LAST_UPDATED_BY is NULL then
181 X_LAST_UPDATED_BY := -1;
182 end if;
183 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
184 if X_LAST_UPDATE_LOGIN is NULL then
185 X_LAST_UPDATE_LOGIN := -1;
186 end if;
187 else
188 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
189 -- ========================= FND LOG ===========================
190 psa_utils.debug_other_msg(g_error_level,l_full_path,FALSE);
191 -- ========================= FND LOG ===========================
192 app_exception.raise_exception;
193 end if;
194 update PSA_IMPLEMENTATION_ALL set
195 STATUS = X_STATUS,
196 ALLOCATION_METHOD = X_ALLOCATION_METHOD,
197 MAPPING_REQUIRED = X_MAPPING_REQUIRED,
198 PROGRAM_INSTALLED = X_PROGRAM_INSTALLED,
199 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
200 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
201 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
202 where PSA_FEATURE = X_PSA_FEATURE
203 and ORG_ID = X_ORG_ID
204 ;
205 if (sql%notfound) then
206 raise no_data_found;
207 end if;
208 end UPDATE_ROW;
209
210 procedure ADD_ROW (
211 X_ROWID in out NOCOPY VARCHAR2,
212 X_PSA_FEATURE in VARCHAR2,
213 X_ORG_ID in NUMBER,
214 X_STATUS in VARCHAR2,
215 X_ALLOCATION_METHOD in VARCHAR2,
216 X_MAPPING_REQUIRED in VARCHAR2,
217 X_PROGRAM_INSTALLED in VARCHAR2,
218 X_MODE in VARCHAR2
219 ) is
220 cursor c1 is select rowid from PSA_IMPLEMENTATION_ALL
221 where PSA_FEATURE = X_PSA_FEATURE
222 and ORG_ID = X_ORG_ID
223 ;
224 dummy c1%rowtype;
225 l_rowid NUMBER;
226 begin
227 l_rowid := X_ROWID;
228 open c1;
229 fetch c1 into dummy;
230 if (c1%notfound) then
231 close c1;
232 INSERT_ROW (
233 X_ROWID,
234 X_PSA_FEATURE,
235 X_ORG_ID,
236 X_STATUS,
237 X_ALLOCATION_METHOD,
238 X_MAPPING_REQUIRED,
239 X_PROGRAM_INSTALLED,
240 X_MODE);
241 return;
242 end if;
243 close c1;
244 UPDATE_ROW (
245 X_PSA_FEATURE,
246 X_ORG_ID,
247 X_STATUS,
248 X_ALLOCATION_METHOD,
249 X_MAPPING_REQUIRED,
250 X_PROGRAM_INSTALLED,
251 X_MODE);
252
253 exception
254 when others then
255 X_ROWID := l_rowid;
256 raise;
257 end ADD_ROW;
258
259 procedure DELETE_ROW (
260 X_PSA_FEATURE in VARCHAR2,
261 X_ORG_ID in NUMBER
262 ) is
263 begin
264 delete from PSA_IMPLEMENTATION_ALL
265 where PSA_FEATURE = X_PSA_FEATURE
266 and ORG_ID = X_ORG_ID;
267 if (sql%notfound) then
268 raise no_data_found;
269 end if;
270 end DELETE_ROW;
271
272 end PSA_IMPLEMENTATION_ALL_PKG;