[Home] [Help]
PACKAGE BODY: APPS.PO_GA_ORG_ASSIGN_PVT
Source
1 PACKAGE BODY PO_GA_ORG_ASSIGN_PVT AS
2 /* $Header: POXPORGB.pls 120.3 2005/09/21 00:45:23 arudas noship $ */
3
4 --< Shared Proc FPJ Start >
5 -- Read the profile option that enables/disables the debug log
6 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
7
8 g_pkg_name CONSTANT VARCHAR2(20) := 'PO_GA_ORG_ASSIGN_PVT';
9 g_module_prefix CONSTANT VARCHAR2(30) := 'po.plsql.' || g_pkg_name || '.';
10 --< Shared Proc FPJ End >
11
12 --------------------------------------------------------------------------------
13 --Start of Comments
14 --Name: insert_row
15 --Pre-reqs:
16 -- None.
17 --Modifies:
18 -- PO_GA_ORG_ASSIGNMENTS.
19 -- FND_MSG_PUB on error.
20 --Locks:
21 -- None.
22 --Function:
23 -- Inserts the record p_org_assign_rec into PO_GA_ORG_ASSIGNMENTS. Shows
24 -- message PO_GA_ORG_ASSIGN_DUPLICATE on error.
25 --Parameters:
26 --IN:
27 --p_init_msg_list
28 -- Standard API parameter to initialize the API message list.
29 --p_org_assign_rec
30 -- Record of the entire row of PO_GA_ORG_ASSIGNMENTS table.
31 --OUT:
32 --x_return_status
33 -- FND_API.g_ret_sts_success - on success
34 -- FND_API.g_ret_sts_error - if duplicate row is inserted
35 -- FND_API.g_ret_sts_unexp_error - unexpected error
36 --x_row_id
37 -- The rowid of the record inserted into the table.
38 --Testing:
39 --End of Comments
40 --------------------------------------------------------------------------------
41 PROCEDURE insert_row
42 ( p_init_msg_list IN VARCHAR2, --< Shared Proc FPJ >
43 x_return_status OUT NOCOPY VARCHAR2, --< Shared Proc FPJ >
44 p_org_assign_rec IN PO_GA_ORG_ASSIGNMENTS%ROWTYPE,
45 x_row_id OUT NOCOPY ROWID
46 )
47 IS
48 l_org_assignment_id PO_GA_ORG_ASSIGNMENTS.ORG_ASSIGNMENT_ID%TYPE; --<HTML Agreement R12>
49 BEGIN
50 --< Shared Proc FPJ Start >
51 -- Start standard API initialization
52 IF FND_API.to_boolean(p_init_msg_list) THEN
53 FND_MSG_PUB.initialize;
54 END IF;
55 x_return_status := FND_API.g_ret_sts_success;
56 -- End standard API initialization
57 --< Shared Proc FPJ End >
58
59 --<HTML Agreement R12 Start>
60 -- SQL What:retrieve the value of org_assignment_id
61 -- SQL Why: to insert into po_ga_org_assignments
62 SELECT PO_GA_ORG_ASSIGNMENTS_S.nextval
63 INTO l_org_assignment_id
64 FROM dual;
65 --<HTML Agreement R12 End>
66
67 INSERT INTO po_ga_org_assignments
68 ( org_assignment_id , --<HTML Agreement R12>
69 po_header_id ,
70 organization_id ,
71 enabled_flag ,
72 vendor_site_id ,
73 last_update_date ,
74 last_updated_by ,
75 creation_date ,
76 created_by ,
77 last_update_login ,
78 purchasing_org_id ) --< Shared Proc FPJ >
79 VALUES
80 ( l_org_assignment_id , --<HTML Agreement R12>
81 p_org_assign_rec.po_header_id ,
82 p_org_assign_rec.organization_id ,
83 p_org_assign_rec.enabled_flag ,
84 p_org_assign_rec.vendor_site_id ,
85 p_org_assign_rec.last_update_date ,
86 p_org_assign_rec.last_updated_by ,
87 p_org_assign_rec.creation_date ,
88 p_org_assign_rec.created_by ,
89 p_org_assign_rec.last_update_login ,
90 p_org_assign_rec.purchasing_org_id ) --< Shared Proc FPJ >
91 RETURNING
92 rowid
93 INTO
94 x_row_id;
95
96 EXCEPTION
97 --< Shared Proc FPJ Start >
98 WHEN DUP_VAL_ON_INDEX THEN
99 x_return_status := FND_API.g_ret_sts_error;
100 FND_MESSAGE.set_name(application => 'PO',
101 name => 'PO_GA_ORG_ASSIGN_DUPLICATE');
102 FND_MSG_PUB.add;
103 WHEN OTHERS THEN
104 x_return_status := FND_API.g_ret_sts_unexp_error;
105 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
106 p_procedure_name => 'insert_row');
107 --< Shared Proc FPJ End >
108 END insert_row;
109
110 --< Shared Proc FPJ Start > Rewrote update_row procedure
111 --------------------------------------------------------------------------------
112 --Start of Comments
113 --Name: update_row
114 --Pre-reqs:
115 -- None.
116 --Modifies:
117 -- PO_GA_ORG_ASSIGNMENTS
118 -- FND_MSG_PUB on error.
119 --Locks:
120 -- PO_GA_ORG_ASSIGNMENTS
121 --Function:
122 -- Updates the PO_GA_ORG_ASSIGNMENTS row with rowid p_row_id. Only the
123 -- following columns are updated:
124 -- organization_id
125 -- purchasing_org_id
126 -- enabled_flag
127 -- vendor_site_id
128 -- last_update_date
129 -- last_updated_by
130 -- last_update_login
131 -- Shows message PO_GA_ORG_ASSIGN_DUPLICATE on duplicate rows error.
132 --Parameters:
133 --IN:
134 --p_init_msg_list
135 -- Standard API parameter to initialize the API message list.
136 --p_org_assign_rec
137 -- Record of the entire row of PO_GA_ORG_ASSIGNMENTS table.
138 --p_row_id
139 --OUT:
140 --x_return_status
141 -- FND_API.g_ret_sts_success - on success
142 -- FND_API.g_ret_sts_error - if update causes duplicate rows
143 -- FND_API.g_ret_sts_unexp_error - unexpected error
144 --Testing:
145 --End of Comments
146 --------------------------------------------------------------------------------
147 PROCEDURE update_row
148 ( p_init_msg_list IN VARCHAR2,
149 x_return_status OUT NOCOPY VARCHAR2,
150 p_org_assign_rec IN PO_GA_ORG_ASSIGNMENTS%ROWTYPE,
151 p_row_id IN ROWID
152 )
153 IS
154
155 BEGIN
156 -- Start standard API initialization
157 IF FND_API.to_boolean(p_init_msg_list) THEN
158 FND_MSG_PUB.initialize;
159 END IF;
160 x_return_status := FND_API.g_ret_sts_success;
161 -- End standard API initialization
162
163 UPDATE po_ga_org_assignments
164 SET organization_id = p_org_assign_rec.organization_id,
165 purchasing_org_id = p_org_assign_rec.purchasing_org_id,
166 enabled_flag = p_org_assign_rec.enabled_flag,
167 vendor_site_id = p_org_assign_rec.vendor_site_id,
168 last_update_date = p_org_assign_rec.last_update_date,
169 last_updated_by = p_org_assign_rec.last_updated_by,
170 last_update_login = p_org_assign_rec.last_update_login
171 WHERE rowid = p_row_id;
172
173 EXCEPTION
174 WHEN DUP_VAL_ON_INDEX THEN
175 x_return_status := FND_API.g_ret_sts_error;
176 FND_MESSAGE.set_name(application => 'PO',
177 name => 'PO_GA_ORG_ASSIGN_DUPLICATE');
178 FND_MSG_PUB.add;
179 WHEN OTHERS THEN
180 x_return_status := FND_API.g_ret_sts_unexp_error;
181 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
182 p_procedure_name => 'update_row');
183 END update_row;
184 --< Shared Proc FPJ End >
185
186 /*==============================================================================
187
188 PROCEDURE: delete_row
189
190 DESCRIPTION: Deletes a row from PO_GA_ORG_ASSIGNMENTS.
191
192 ==============================================================================*/
193 PROCEDURE delete_row
194 (
195 p_po_header_id IN PO_GA_ORG_ASSIGNMENTS.po_header_id%TYPE
196 )
197 IS
198 BEGIN
199
200 DELETE FROM po_ga_org_assignments
201 WHERE po_header_id = p_po_header_id;
202
203 EXCEPTION
204
205 WHEN OTHERS THEN
206 PO_MESSAGE_S.sql_error('delete_row','000',SQLCODE);
207 RAISE;
208
209 END delete_row;
210
211
212 --< Shared Proc FPJ Start >
213 --------------------------------------------------------------------------------
214 --Start of Comments
215 --Name: delete_row
216 --Pre-reqs:
217 -- None.
218 --Modifies:
219 -- PO_GA_ORG_ASSIGNMENTS.
220 --Locks:
221 -- PO_GA_ORG_ASSIGNMENTS
222 --Function:
223 -- Deletes the row in PO_GA_ORG_ASSIGNMENTS with p_po_header_id and
224 -- p_organization_id. Uses APP_EXCEPTION.raise_exception when error occurs.
225 --Parameters:
226 --IN:
227 --p_po_header_id
228 --p_organization_id
229 --Testing:
230 --End of Comments
231 --------------------------------------------------------------------------------
232 PROCEDURE delete_row
233 ( p_po_header_id IN NUMBER,
234 p_organization_id IN NUMBER
235 )
236 IS
237 BEGIN
238
239 DELETE FROM po_ga_org_assignments
240 WHERE po_header_id = p_po_header_id
241 AND organization_id = p_organization_id;
242
243 EXCEPTION
244 WHEN OTHERS THEN
245 PO_MESSAGE_S.sql_error(routine => 'PO_GA_ORG_ASSIGN_PVT.delete_row',
246 location => '200',
247 error_code => SQLCODE);
248 APP_EXCEPTION.raise_exception;
249 END delete_row;
250
251
252 --Rewrote lock_row procedure
253 --------------------------------------------------------------------------------
254 --Start of Comments
255 --Name: lock_row
256 --Pre-reqs:
257 -- None.
258 --Modifies:
259 -- None.
260 --Locks:
261 -- PO_GA_ORG_ASSIGNMENTS
262 --Function:
263 -- Locks the row in PO_GA_ORG_ASSIGNMENTS with rowid p_row_id. If the DB lock
264 -- cannot be acquired, then standard FND record lock exception handling is
265 -- used. The following columns are compared with the database values:
266 -- organization_id
267 -- purchasing_org_id
268 -- vendor_site_id
269 -- enabled_flag
270 -- If any of these are different than the database value, the procedure will
271 -- error out after setting the FND message FORM_RECORD_CHANGED. If the matching
272 -- record in the database cannot be found, the FND message FORM_RECORD_DELETED
273 -- is set. Uses APP_EXCEPTION.raise_exception when errors occur.
274 --Parameters:
275 --IN:
276 --p_org_assign_rec
277 --p_row_id
278 --End of Comments
279 --------------------------------------------------------------------------------
280 PROCEDURE lock_row
281 ( p_org_assign_rec IN PO_GA_ORG_ASSIGNMENTS%ROWTYPE,
282 p_row_id IN ROWID
283 )
284 IS
285 l_pgoa_rec PO_GA_ORG_ASSIGNMENTS%ROWTYPE;
286 BEGIN
287
288 --SQL What: Lock the record with rowid p_row_id.
289 --SQL Why: Obtain the lock and compare values to see if there's a difference
290 SELECT *
291 INTO l_pgoa_rec
292 FROM po_ga_org_assignments
293 WHERE rowid = p_row_id
294 FOR UPDATE NOWAIT;
295
296 IF (l_pgoa_rec.organization_id <> p_org_assign_rec.organization_id) OR
297 (l_pgoa_rec.purchasing_org_id <> p_org_assign_rec.purchasing_org_id) OR
298 (l_pgoa_rec.vendor_site_id <> p_org_assign_rec.vendor_site_id) OR
299 (l_pgoa_rec.enabled_flag <> p_org_assign_rec.enabled_flag)
300 THEN
301 RAISE FND_API.g_exc_error;
302 END IF;
303
304 EXCEPTION
305 WHEN APP_EXCEPTION.record_lock_exception THEN
306 -- The record could not be locked, so raise it to calling procedure
307 RAISE APP_EXCEPTION.record_lock_exception;
308 WHEN NO_DATA_FOUND THEN
309 -- Could not find the record
310 FND_MESSAGE.set_name(application => 'FND',
311 name => 'FORM_RECORD_DELETED');
312 APP_EXCEPTION.raise_exception;
313 WHEN FND_API.g_exc_error THEN
314 -- The individual value comparisons failed
315 FND_MESSAGE.set_name(application => 'FND',
316 name => 'FORM_RECORD_CHANGED');
317 APP_EXCEPTION.raise_exception;
318 WHEN OTHERS THEN
319 FND_MSG_PUB.build_exc_msg
320 (p_pkg_name => g_pkg_name,
321 p_procedure_name => 'lock_row',
322 p_error_text => NULL);
323 APP_EXCEPTION.raise_exception;
324 END lock_row;
325
326 --------------------------------------------------------------------------------
327 --Start of Comments
328 --Name: copy_rows
329 --Pre-reqs:
330 -- None.
331 --Modifies:
332 -- PO_GA_ORG_ASSIGNMENTS
333 -- FND_MSG_PUB on error.
334 --Locks:
335 -- None.
336 --Function:
337 -- Copy all the org assignments of p_from_po_header_id to new org assignments
338 -- for p_to_po_header_id. The WHO columns are not copied over; they are updated
339 -- with the input parameters.
340 --Parameters:
341 --IN:
342 --p_init_msg_list
343 -- Standard param initializes API message list if FND_API.g_true;
344 --p_from_po_header_id
345 -- The header ID of the original GA whose org assignments will be copied.
346 --p_to_po_header_id
347 -- The header ID of the new GA for the new org assignments.
348 --p_last_update_date
349 --p_last_updated_by
350 --p_creation_date
351 --p_created_by
352 --p_last_update_login
353 --OUT:
354 --x_return_status
355 -- FND_API.g_ret_sts_success - success.
356 -- FND_API.g_ret_sts_error - duplicate rows inserted. Appends message
357 -- PO_GA_ORG_ASSIGN_DUPLICATE.
358 -- FND_API.g_ret_sts_unexp_error - Unexpected error occurred, or no rows were
359 -- inserted. Appends unexpected error message.
360 --End of Comments
361 --------------------------------------------------------------------------------
362 PROCEDURE copy_rows
363 (
364 p_init_msg_list IN VARCHAR2,
365 x_return_status OUT NOCOPY VARCHAR2,
366 p_from_po_header_id IN NUMBER,
370 p_creation_date IN DATE,
367 p_to_po_header_id IN NUMBER,
368 p_last_update_date IN DATE,
369 p_last_updated_by IN NUMBER,
371 p_created_by IN NUMBER,
372 p_last_update_login IN NUMBER
373 )
374 IS
375 BEGIN
376 -- Start standard API initialization
377 IF FND_API.to_boolean(p_init_msg_list) THEN
378 FND_MSG_PUB.initialize;
379 END IF;
380 x_return_status := FND_API.g_ret_sts_success;
381 -- End standard API initialization
382
383 --<Bug4623941 Start>
384 -- Used PO_GA_ORG_ASSIGNMENTS_S.nextval directly inside insert statement
385 --<Bug4623941 End>
386
387 INSERT INTO PO_GA_ORG_ASSIGNMENTS
388 (
389 org_assignment_id, --<HTML Agreement R12>
390 po_header_id,
391 organization_id,
392 enabled_flag,
393 vendor_site_id,
394 last_update_date,
395 last_updated_by,
396 creation_date,
397 created_by,
398 last_update_login,
399 purchasing_org_id
400 )
401 SELECT PO_GA_ORG_ASSIGNMENTS_S.nextval ,--Bug#4623941
402 p_to_po_header_id,
403 pgoa.organization_id,
404 pgoa.enabled_flag,
405 pgoa.vendor_site_id,
406 p_last_update_date,
407 p_last_updated_by,
408 p_creation_date,
409 p_created_by,
410 p_last_update_login,
411 pgoa.purchasing_org_id
412 FROM po_ga_org_assignments pgoa
413 WHERE pgoa.po_header_id = p_from_po_header_id;
414
415 IF (SQL%ROWCOUNT = 0) THEN
416 -- Return error if nothing was inserted
417 RAISE NO_DATA_FOUND;
418 END IF;
419
420 EXCEPTION
421 WHEN DUP_VAL_ON_INDEX THEN
422 x_return_status := FND_API.g_ret_sts_error;
423 FND_MESSAGE.set_name(application => 'PO',
424 name => 'PO_GA_ORG_ASSIGN_DUPLICATE');
425 FND_MSG_PUB.add;
426 WHEN OTHERS THEN
427 x_return_status := FND_API.g_ret_sts_unexp_error;
428 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
429 p_procedure_name => 'copy_rows',
430 p_error_text => NULL);
431 END copy_rows;
432
433 --< Shared Proc FPJ End >
434
435 END PO_GA_ORG_ASSIGN_PVT;