[Home] [Help]
PACKAGE BODY: APPS.PO_SOURCING_GRP
Source
1 PACKAGE BODY PO_SOURCING_GRP AS
2 /* $Header: POXGCPAB.pls 120.0 2005/06/02 00:57:38 appldev noship $*/
3
4
5 ---
6 --- +=======================================================================+
7 --- | Copyright (c) 2004 Oracle Corporation, Redwood Shores, CA, USA |
8 --- | All rights reserved. |
9 --- +=======================================================================+
10 --- |
11 --- | FILENAME
12 --- | POXGCPAB.pls
13 --- |
14 --- |
15 --- | DESCRIPTION
16 --- |
17 --- | This package contains procedures called from the sourcing
18 --- | to create CPA in PO
19 --- |
20 --- | HISTORY
21 --- |
22 --- | 30-Sep-2004 rbairraj Initial version
23 --- |
24 --- +=======================================================================+
25 ---
26
27 --------------------------------------------------------------------------------
28
29 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_SOURCING_GRP';
30 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
31 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
32 g_log_head CONSTANT VARCHAR2(30) := 'po.plsql.PO_SOURCING_GRP.';
33
34 -------------------------------------------------------------------------------
35 --Start of Comments
36 --Name: create_cpa
37 --Pre-reqs:
38 -- None
39 --Modifies:
40 -- Transaction tables for the requested document
41 --Locks:
42 -- None.
43 --Function:
44 -- Creates Contract Purchase Agreement from Sourcing document
45 --Parameters:
46 --IN:
47 --p_api_version
48 -- API standard IN parameter
49 --p_init_msg_list
50 -- True/False parameter to initialize message list
51 --p_commit
52 -- Standard parameter which dictates whether or not data should be commited in the api
53 --p_validation_level
54 -- The p_validation_level parameter to determine which validation steps should
55 -- be executed and which steps should be skipped
56 --p_interface_header_id
57 -- The id that will be used to uniquely identify a row in the PO_HEADERS_INTERFACE table
58 --p_auction_header_id
59 -- Id of the negotiation
60 --p_bid_number
61 -- Bid Number for which is negotiation is awarded
62 --p_sourcing_k_doc_type
63 -- Represents the OKC document type that would be created into a CPA
64 -- The document type that Sourcing has seeded in Contracts.
65 --p_conterms_exist_flag
66 -- Whether the sourcing document has contract template attached.
67 --p_document_creation_method
68 -- Column specific to DBI. Sourcing will pass a value of AWARD_SOURCING
69 --OUT:
70 --x_document_id
71 -- The unique identifier for the newly created document.
72 --x_document_number
73 -- The document number that would uniquely identify a document in a given organization.
74 --x_return_status
75 -- The standard OUT parameter giving return status of the API call.
76 -- FND_API.G_RET_STS_ERROR - for expected error
77 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
78 -- FND_API.G_RET_STS_SUCCESS - for success
79 --x_msg_count
80 -- The count of number of messages added to the message list in this call
81 --x_msg_data
82 -- If the count is 1 then x_msg_data contains the message returned
83 --Notes:
84 -- None
85 --Testing:
86 -- None
87 --End of Comments
88 -------------------------------------------------------------------------------
89 PROCEDURE create_cpa (
90 p_api_version IN NUMBER,
91 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
92 p_commit IN VARCHAR2 := FND_API.G_FALSE,
93 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
94 x_return_status OUT NOCOPY VARCHAR2,
95 x_msg_count OUT NOCOPY NUMBER,
96 x_msg_data OUT NOCOPY VARCHAR2,
97 p_interface_header_id IN PO_HEADERS_INTERFACE.interface_header_id%TYPE,
98 p_auction_header_id IN PON_AUCTION_HEADERS_ALL.auction_header_id%TYPE,
99 p_bid_number IN PON_BID_HEADERS.bid_number%TYPE,
100 p_sourcing_k_doc_type IN VARCHAR2,
101 p_conterms_exist_flag IN PO_HEADERS_ALL.conterms_exist_flag%TYPE,
102 p_document_creation_method IN PO_HEADERS_ALL.document_creation_method%TYPE,
103 x_document_id OUT NOCOPY PO_HEADERS_ALL.po_header_id%TYPE,
104 x_document_number OUT NOCOPY PO_HEADERS_ALL.segment1%TYPE
105 ) IS
106
107 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_CPA';
108 l_api_version CONSTANT NUMBER := 1.0;
109 l_progress VARCHAR2(2000) := '000';
110 l_return_status VARCHAR2(1);
111
112 BEGIN
113 l_progress := 'PO_SOURCING_GRP: 001';
114 IF g_debug_stmt THEN
115 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
116 p_token => l_progress,
117 p_message => 'PO_SOURCING_GRP: Entered');
118 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
119 p_progress => l_progress,
120 p_name => 'p_api_version',
121 p_value => p_api_version);
122 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
123 p_progress => l_progress,
124 p_name => 'p_init_msg_list',
125 p_value => p_init_msg_list);
126 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
127 p_progress => l_progress,
128 p_name => 'p_commit',
129 p_value => p_commit);
130 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
131 p_progress => l_progress,
132 p_name => 'p_validation_level',
133 p_value => p_validation_level);
134 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
135 p_progress => l_progress,
136 p_name => 'p_interface_header_id',
137 p_value => p_interface_header_id);
138 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
139 p_progress => l_progress,
140 p_name => 'p_auction_header_id',
141 p_value => p_auction_header_id);
142 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
143 p_progress => l_progress,
144 p_name => 'p_bid_number',
145 p_value => p_bid_number);
146 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
147 p_progress => l_progress,
148 p_name => 'p_sourcing_k_doc_type',
149 p_value => p_sourcing_k_doc_type);
150 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
151 p_progress => l_progress,
152 p_name => 'p_conterms_exist_flag',
153 p_value => p_conterms_exist_flag);
154 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
155 p_progress => l_progress,
156 p_name => 'p_document_creation_method',
157 p_value => p_document_creation_method);
158 END IF;
159
160 -- Standard Start of API savepoint
161 SAVEPOINT CREATE_CPA_GRP;
162
163 -- Standard call to check for call compatibility.
164 IF NOT FND_API.compatible_api_call (
165 p_current_version_number => l_api_version,
166 p_caller_version_number => p_api_version,
167 p_api_name => l_api_name,
168 p_pkg_name => g_pkg_name
169 )
170 THEN
171 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
172 END IF;
173
174 -- Initialize message list if p_init_msg_list is set to TRUE.
175 IF FND_API.to_Boolean(p_init_msg_list ) THEN
176 FND_MSG_PUB.initialize;
177 END IF;
178
179 -- Initialize API return status to success
180 x_return_status := FND_API.G_RET_STS_SUCCESS;
181
182 l_progress := 'PO_SOURCING_GRP: 002';
183 IF g_debug_stmt THEN
184 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
185 p_token => l_progress,
186 p_message => 'Before calling po_sourcing_pvt.create_cpa');
187 END IF;
188
189 PO_SOURCING_PVT.create_cpa (
190 x_return_status => x_return_status,
191 x_msg_count => x_msg_count,
192 x_msg_data => x_msg_data,
193 p_interface_header_id => p_interface_header_id,
194 p_auction_header_id => p_auction_header_id,
195 p_bid_number => p_bid_number,
196 p_sourcing_k_doc_type => p_sourcing_k_doc_type,
197 p_conterms_exist_flag => p_conterms_exist_flag,
198 p_document_creation_method => p_document_creation_method,
199 x_document_id => x_document_id,
200 x_document_number => x_document_number
201 );
202
203 l_progress := 'PO_SOURCING_GRP: 003';
204 IF g_debug_stmt THEN
205 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
206 p_token => l_progress,
207 p_message => 'After calling po_sourcing_pvt.create_cpa');
208 END IF;
209
210 l_progress := 'PO_SOURCING_GRP: 004';
211 IF g_debug_stmt THEN
212 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
213 p_token => l_progress,
214 p_message => 'PO_SOURCING_GRP: After calling PO_SOURCING_PVT.create_cpa');
215 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
216 p_progress => l_progress,
217 p_name => 'x_document_id',
218 p_value => x_document_id);
219 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
220 p_progress => l_progress,
221 p_name => 'x_document_number',
222 p_value => x_document_number);
223 PO_DEBUG.debug_var (p_log_head => g_log_head||l_api_name,
224 p_progress => l_progress,
225 p_name => 'x_return_status',
226 p_value => x_return_status);
227 END IF;
228
229
230 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
231 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
232 RAISE FND_API.G_EXC_ERROR;
233 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
234 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
235 END IF;
236 END IF;
237
238 --Commit the data if p_commit is passed to the API is true
239 IF p_commit = FND_API.G_TRUE THEN
240 COMMIT;
241 END IF;
242 EXCEPTION
243 WHEN FND_API.G_EXC_ERROR THEN
244 l_progress := 'PO_SOURCING_GRP: 004';
245 x_return_status := FND_API.G_RET_STS_ERROR;
246 IF g_debug_stmt THEN
247 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
248 p_token => l_progress,
249 p_message => x_msg_data);
250 END IF;
251 FND_MSG_PUB.Count_And_Get
252 (p_count => x_msg_count,
253 p_data => x_msg_data );
254 ROLLBACK TO CREATE_CPA_GRP;
255 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
256 l_progress := 'PO_SOURCING_GRP: 005';
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
258 IF g_debug_unexp THEN
259 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
260 p_token => l_progress,
261 p_message => SQLERRM);
262 END IF;
263 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
264 ,p_data => x_msg_data);
265 ROLLBACK TO CREATE_CPA_GRP;
266 WHEN OTHERS THEN
267 l_progress := 'PO_SOURCING_GRP: 006';
268 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
269 IF g_debug_unexp THEN
270 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
271 p_token => l_progress,
272 p_message => SQLERRM);
273 END IF;
274 FND_MSG_PUB.add_exc_msg(
275 p_pkg_name => 'PO_SOURCING_GRP',
276 p_procedure_name => l_api_name,
277 p_error_text => NULL);
278
279 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
280 ,p_data => x_msg_data);
281 ROLLBACK TO CREATE_CPA_GRP;
282 END create_cpa;
283
284 -------------------------------------------------------------------------------
285 --Start of Comments
286 --Name: DELETE_INTERFACE_HEADER
287 --Pre-reqs:
288 -- None
289 --Modifies:
290 -- po_headers_interface
291 --Locks:
292 -- None.
293 --Function:
294 -- This deletes the interface header row from interface table
295 --Parameters:
296 --IN:
297 --p_api_version
298 -- API standard IN parameter
299 --p_init_msg_list
300 -- True/False parameter to initialize message list
301 --p_commit
302 -- Standard parameter which dictates whether or not data should be commited in the api
303 --p_validation_level
304 -- The p_validation_level parameter to determine which validation steps should
305 -- be executed and which steps should be skipped
306 --p_interface_header_id
307 -- The id that will be used to uniquely identify a row in the PO_HEADERS_INTERFACE table
308 --OUT:
309 --x_return_status
310 -- The standard OUT parameter giving return status of the API call.
311 -- FND_API.G_RET_STS_UNEXP_ERROR - for unexpected error
312 -- FND_API.G_RET_STS_SUCCESS - for success
313 --x_msg_count
314 -- The count of number of messages added to the message list in this call
315 --x_msg_data
316 -- If the count is 1 then x_msg_data contains the message returned
317 --Notes:
318 -- None
319 --Testing:
320 -- None
321 --End of Comments
322 -------------------------------------------------------------------------------
323
324 PROCEDURE DELETE_INTERFACE_HEADER (
325 p_api_version IN NUMBER,
326 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
327 p_commit IN VARCHAR2 := FND_API.G_FALSE,
328 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
329 x_return_status OUT NOCOPY VARCHAR2,
330 x_msg_count OUT NOCOPY NUMBER,
331 x_msg_data OUT NOCOPY VARCHAR2,
332 p_interface_header_id IN PO_HEADERS_INTERFACE.INTERFACE_HEADER_ID%TYPE
333 ) IS
334 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INTERFACE_HEADER';
335 l_api_version CONSTANT NUMBER := 1.0;
336 l_progress VARCHAR2(2000) := '000';
337
338 BEGIN
339 l_progress := 'PO_SOURCING_GRP.DELETE_INTERFACE_HEADER: 001';
340 -- Standard call to check for call compatibility.
341 IF NOT FND_API.compatible_api_call (
342 p_current_version_number => l_api_version,
343 p_caller_version_number => p_api_version,
344 p_api_name => l_api_name,
345 p_pkg_name => g_pkg_name
346 )
347 THEN
348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
349 END IF;
350
351 -- Initialize message list if p_init_msg_list is set to TRUE.
352 IF FND_API.to_Boolean(p_init_msg_list ) THEN
353 FND_MSG_PUB.initialize;
354 END IF;
355
356 -- Initialize API return status to success
357 x_return_status := FND_API.G_RET_STS_SUCCESS;
358
359 l_progress := 'PO_SOURCING_GRP.DELETE_INTERFACE_HEADER: 002';
360 IF g_debug_stmt THEN
361 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
362 p_token => l_progress,
363 p_message => 'Before calling po_sourcing_pvt.delete_interface_header');
364 END IF;
365
366 PO_SOURCING_PVT.DELETE_INTERFACE_HEADER (
367 x_return_status => x_return_status,
368 p_interface_header_id => p_interface_header_id
369 );
370
371 l_progress := 'PO_SOURCING_GRP.DELETE_INTERFACE_HEADER: 003';
372 IF g_debug_stmt THEN
373 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
374 p_token => l_progress,
375 p_message => 'After calling po_sourcing_pvt.delete_interface_header');
376 END IF;
377
378 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
379 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END IF;
382 END IF;
383
384 EXCEPTION
385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
386 l_progress := 'PO_SOURCING_GRP.DELETE_INTERFACE_HEADER: 004';
387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
388 IF g_debug_unexp THEN
389 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
390 p_token => l_progress,
391 p_message => x_msg_data);
392 END IF;
393 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
394 ,p_data => x_msg_data);
395 WHEN OTHERS THEN
396 l_progress := 'PO_SOURCING_GRP.DELETE_INTERFACE_HEADER: 005';
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 IF g_debug_unexp THEN
399 PO_DEBUG.debug_stmt(p_log_head => g_log_head||l_api_name,
400 p_token => l_progress,
401 p_message => SQLERRM);
402 END IF;
403 FND_MSG_PUB.add_exc_msg(
404 p_pkg_name => 'PO_SOURCING_GRP',
405 p_procedure_name => l_api_name,
406 p_error_text => NULL);
407
408 FND_MSG_PUB.Count_and_Get(p_count => x_msg_count
409 ,p_data => x_msg_data);
410 END DELETE_INTERFACE_HEADER;
411
412 END PO_SOURCING_GRP;