[Home] [Help]
PACKAGE BODY: APPS.PV_TAP_ACCESS_TERRS_PKG
Source
1 PACKAGE BODY PV_TAP_ACCESS_TERRS_PKG as
2 /* $Header: pvxttrab.pls 115.0 2003/10/15 04:20:26 rdsharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- PV_TAP_ACCESS_TERRS_PKG
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- ===============================================================
18
19
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'PV_TAP_ACCESS_TERRS_PKG';
21 G_FILE_NAME CONSTANT VARCHAR2(12) := 'pvxttrab.pls';
22
23
24
25
26 -- ========================================================
27 --
28 -- NAME
29 -- Insert_Row
30 --
31 -- PURPOSE
32 --
33 -- NOTES
34 --
35 -- HISTORY
36 --
37 -- ========================================================
38 PROCEDURE Insert_Row(
39 p_partner_access_id NUMBER,
40 p_terr_id NUMBER,
41 p_last_update_date DATE,
42 p_last_updated_by NUMBER,
43 p_creation_date DATE,
44 p_created_by NUMBER,
45 p_last_update_login NUMBER,
46 p_object_version_number NUMBER,
47 p_request_id NUMBER,
48 p_program_application_id NUMBER,
49 p_program_id NUMBER,
50 p_program_update_date DATE,
51 x_return_status IN OUT NOCOPY VARCHAR2)
52 IS
53 x_rowid VARCHAR2(30);
54 l_object_version_number NUMBER;
55 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Row';
56
57
58 BEGIN
59
60 -- Standard Start of API savepoint
61 SAVEPOINT Insert_PAccesses_Row;
62
63 l_object_version_number := nvl(p_object_version_number, 1);
64
65 -- Initialize API return status to SUCCESS
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67
68 INSERT INTO pv_TAP_ACCESS_TERRS(
69 partner_access_id,
70 terr_id,
71 last_update_date,
72 last_updated_by,
73 creation_date,
74 created_by,
75 last_update_login,
76 object_version_number,
77 request_id,
78 program_application_id,
79 program_id,
80 program_update_date
81 ) VALUES (
82 DECODE( p_partner_access_id, FND_API.G_MISS_NUM, NULL, p_partner_access_id),
83 DECODE( p_terr_id, FND_API.G_MISS_NUM, NULL, p_terr_id),
84 DECODE( p_last_update_date, FND_API.G_MISS_DATE, SYSDATE,NULL, SYSDATE, p_last_update_date),
85 DECODE( p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID, NULL, FND_GLOBAL.USER_ID, p_last_updated_by),
86 DECODE( p_creation_date, FND_API.G_MISS_DATE, SYSDATE,NULL, SYSDATE, p_creation_date),
87 DECODE( p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.USER_ID,NULL, FND_GLOBAL.USER_ID, p_created_by),
88 DECODE( p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.CONC_LOGIN_ID, NULL, FND_GLOBAL.CONC_LOGIN_ID, p_last_update_login),
89 DECODE( p_object_version_number,FND_API.G_MISS_NUM, l_object_version_number, NULL, l_object_version_number, p_object_version_number),
90 DECODE( p_request_id, FND_API.G_MISS_NUM, NULL, p_request_id),
91 DECODE( p_program_application_id, FND_API.G_MISS_NUM, NULL, p_program_application_id),
92 DECODE( p_program_id, FND_API.G_MISS_NUM, NULL, p_program_id),
93 DECODE( p_program_update_date, FND_API.G_MISS_DATE, NULL, p_program_update_date));
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 ROLLBACK TO Insert_PAccesses_Row;
98 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
99
100 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
101 THEN
102 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
103 END IF;
104 END Insert_Row;
105
106 -- ========================================================
107 --
108 -- NAME
109 -- Update_Row
110 --
111 -- PURPOSE
112 --
113 -- NOTES
114 --
115 -- HISTORY
116 --
117 -- ========================================================
118 PROCEDURE Update_Row(
119 p_partner_access_id NUMBER,
120 p_terr_id NUMBER,
121 p_last_update_date DATE,
122 p_last_updated_by NUMBER,
123 p_last_update_login NUMBER,
124 p_object_version_number NUMBER,
125 p_request_id NUMBER,
126 p_program_application_id NUMBER,
127 p_program_id NUMBER,
128 p_program_update_date DATE,
129 x_return_status IN OUT NOCOPY VARCHAR2)
130 IS
131 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Row';
132 BEGIN
133
134 -- Standard Start of API savepoint
135 SAVEPOINT Update_TAccesses_Row;
136
137 -- Initialize API return status to SUCCESS
138 x_return_status := FND_API.G_RET_STS_SUCCESS;
139
140 Update PV_TAP_ACCESS_TERRS
141 SET
142 partner_access_id = DECODE( p_partner_access_id, null, partner_access_id, FND_API.G_MISS_NUM, null, p_partner_access_id),
143 terr_id = DECODE( p_terr_id, null, terr_id, FND_API.G_MISS_NUM, null, p_terr_id),
144 last_update_date = DECODE( p_last_update_date, null, last_update_date, FND_API.G_MISS_DATE, null, p_last_update_date),
145 last_updated_by = DECODE( p_last_updated_by, null, last_updated_by, FND_API.G_MISS_NUM, null, p_last_updated_by),
146 last_update_login = DECODE( p_last_update_login, null, last_update_login, FND_API.G_MISS_NUM, null, p_last_update_login),
147 object_version_number = nvl(p_object_version_number,0) + 1 ,
148 request_id = DECODE( p_request_id, null, request_id, FND_API.G_MISS_NUM, null, p_request_id),
149 program_application_id = DECODE( p_program_application_id, null, program_application_id, FND_API.G_MISS_NUM, null, p_program_application_id),
150 program_id = DECODE( p_program_id, null, program_id, FND_API.G_MISS_NUM, null, p_program_id),
151 program_update_date = DECODE( p_program_update_date, null, program_update_date, FND_API.G_MISS_DATE, null, p_program_update_date)
152 WHERE partner_access_id = p_partner_access_id
153 AND terr_id = p_terr_id
154 AND object_version_number = p_object_version_number;
155
156
157 IF (SQL%NOTFOUND) THEN
158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
159 END IF;
160
161 EXCEPTION
162
163 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
164 ROLLBACK TO Update_TAccesses_Row;
165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
166
167 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
168 THEN
169 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
170 END IF;
171
172 WHEN OTHERS THEN
173 ROLLBACK TO Update_TAccesses_Row;
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175
176 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
177 THEN
178 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
179 END IF;
180
181 END Update_Row;
182
183
184
185
186 -- ========================================================
187 --
188 -- NAME
189 -- Delete_Row
190 --
191 -- PURPOSE
192 --
193 -- NOTES
194 --
195 -- HISTORY
196 --
197 -- ========================================================
198 PROCEDURE Delete_Row(
199 p_partner_access_id NUMBER,
200 p_terr_id NUMBER,
201 p_object_version_number NUMBER,
202 x_return_status IN OUT NOCOPY VARCHAR2)
203 IS
204 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Row';
205 BEGIN
206
207 -- Standard Start of API savepoint
208 SAVEPOINT Delete_TAccesses_Row;
209
210 -- Initialize API return status to SUCCESS
211 x_return_status := FND_API.G_RET_STS_SUCCESS;
212
213 DELETE FROM PV_TAP_ACCESS_TERRS
214 WHERE partner_access_id = p_partner_access_id
215 AND terr_id = p_terr_id
216 AND object_version_number = p_object_version_number;
217 If (SQL%NOTFOUND) then
218 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
219 End If;
220
221 EXCEPTION
222 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
223 ROLLBACK TO Delete_TAccesses_Row;
224 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
225
226 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
227 THEN
228 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
229 END IF;
230 WHEN OTHERS THEN
231 ROLLBACK TO Delete_TAccesses_Row;
232 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
233
234 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
235 THEN
236 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
237 END IF;
238
239 END Delete_Row ;
240
241 -- ========================================================
242 --
243 -- NAME
244 -- Lock_Row
245 --
246 -- PURPOSE
247 --
248 -- NOTES
249 --
250 -- HISTORY
251 --
252 -- ========================================================
253 PROCEDURE Lock_Row(
254 p_partner_access_id NUMBER,
255 p_terr_id NUMBER,
256 p_object_version_number NUMBER,
257 x_return_status IN OUT NOCOPY VARCHAR2)
258 IS
259 CURSOR C IS
260 SELECT *
261 FROM PV_TAP_ACCESS_TERRS
262 WHERE partner_access_id = p_partner_access_id
263 AND terr_id = p_terr_id
264 AND object_version_number = p_object_version_number
265 FOR UPDATE OF partner_access_id,terr_id NOWAIT;
266 Recinfo C%ROWTYPE;
267
268 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Row';
269
270 BEGIN
271
272 -- Standard Start of API savepoint
273 SAVEPOINT Lock_TAccesses_Row;
274
275 -- Initialize API return status to SUCCESS
276 x_return_status := FND_API.G_RET_STS_SUCCESS;
277
278 OPEN c;
279 FETCH c INTO Recinfo;
280 IF (c%NOTFOUND) THEN
281 CLOSE c;
282 PVX_Utility_PVT.error_message ('PV_API_RECORD_NOT_FOUND');
283 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
284 END IF;
285 CLOSE c;
286 EXCEPTION
287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
288 ROLLBACK TO Lock_TAccesses_Row;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290
291 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
292 THEN
293 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
294 END IF;
295 WHEN OTHERS THEN
296 ROLLBACK TO Lock_TAccesses_Row;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298
299 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
300 THEN
301 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
302 END IF;
303
304 END Lock_Row;
305
306 END PV_TAP_ACCESS_TERRS_PKG;