[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_REQUEST_PUB
Source
1 PACKAGE BODY PA_PROJECT_REQUEST_PUB as
2 /* $Header: PAYRPUBB.pls 120.2 2005/08/19 17:24:56 mwasowic noship $ */
3
4
5 PROCEDURE create_project_request
6 ( p_request_name IN pa_project_requests.request_name%TYPE,
7 p_request_type IN pa_project_requests.request_type%TYPE,
8 p_request_status_code IN pa_project_requests.status_code%TYPE,
9 p_request_status_name IN pa_project_statuses.project_status_name%TYPE,
10 p_description IN pa_project_requests.description%TYPE,
11 p_expected_proj_approval_date IN pa_project_requests.expected_project_approval_date%TYPE,
12 p_closed_date IN pa_project_requests.closed_date%TYPE,
13 p_source_type IN pa_project_requests.source_type%TYPE :='ORACLE_APPLICATION',
14 p_application_id IN pa_project_requests.application_id%TYPE,
15 p_source_id IN NUMBER,
16 p_source_object IN pa_object_relationships.object_type_from%TYPE,
17 p_source_reference IN pa_project_requests.source_reference%TYPE,
18 p_value IN pa_project_requests.value%TYPE,
19 p_currency_code IN pa_project_requests.currency_code%TYPE,
20 p_cust_party_id IN pa_project_requests.cust_party_id%TYPE,
21 p_cust_party_name IN hz_parties.party_name%TYPE,
22 p_cust_party_site_id IN pa_project_requests.cust_party_site_id%TYPE,
23 p_cust_party_site_name IN hz_party_sites.party_site_name%TYPE,
24 p_cust_account_id IN pa_project_requests.cust_account_id%TYPE,
25 p_cust_account_name IN hz_cust_accounts.account_name%TYPE,
26 p_source_org_id IN pa_project_requests.source_org_id%TYPE,
27 p_attribute_category IN pa_project_requests.attribute_category%TYPE,
28 p_attribute1 IN pa_project_requests.attribute1%TYPE,
29 p_attribute2 IN pa_project_requests.attribute2%TYPE,
30 p_attribute3 IN pa_project_requests.attribute3%TYPE,
31 p_attribute4 IN pa_project_requests.attribute4%TYPE,
32 p_attribute5 IN pa_project_requests.attribute5%TYPE,
33 p_attribute6 IN pa_project_requests.attribute6%TYPE,
34 p_attribute7 IN pa_project_requests.attribute7%TYPE,
35 p_attribute8 IN pa_project_requests.attribute8%TYPE,
36 p_attribute9 IN pa_project_requests.attribute9%TYPE,
37 p_attribute10 IN pa_project_requests.attribute10%TYPE,
38 p_attribute11 IN pa_project_requests.attribute11%TYPE,
39 p_attribute12 IN pa_project_requests.attribute12%TYPE,
40 p_attribute13 IN pa_project_requests.attribute13%TYPE,
41 p_attribute14 IN pa_project_requests.attribute14%TYPE,
42 p_attribute15 IN pa_project_requests.attribute15%TYPE,
43 p_create_rel_flag IN VARCHAR2,
44 p_api_version IN NUMBER := 1.0,
45 p_init_msg_list IN VARCHAR2,
46 p_commit IN VARCHAR2,
47 p_validate_only IN VARCHAR2,
48 p_max_msg_count IN NUMBER,
49 x_request_id OUT NOCOPY pa_project_requests.request_id%TYPE, --File.Sql.39 bug 4440895
50 x_request_number OUT NOCOPY pa_project_requests.request_number%TYPE, --File.Sql.39 bug 4440895
51 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
52 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
53 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
54
55 IS
56 --Declare local variables
57 l_check_status_err EXCEPTION;
58 l_request_status_code pa_project_requests.status_code%TYPE;
59 l_msg_index_out NUMBER;
60 l_lead_id as_leads_all.lead_id%TYPE;
61 l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
62 l_new_obj_rel_id2 PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
63 l_return_status VARCHAR2(1);
64 l_error_message_code fnd_new_messages.message_name%TYPE;
65 -- added for Bug: 4537865
66 l_new_msg_data VARCHAR2(2000);
67 -- added for Bug: 4537865
68
69
70 BEGIN
71
72 PA_PROJECT_REQUEST_PVT.debug('PA_PROJECT_REQUESTS_PUB.create_project_request.begin');
73
74 -- Initialize the Error Stack
75 PA_DEBUG.init_err_stack('PA_PROJECT_REQUEST_PUB.create_project_request');
76
77
78 -- Initialize the return status to success
79 x_return_status := FND_API.G_RET_STS_SUCCESS;
80
81 --Issue API savepoint if the transaction is to be committed
82 IF p_commit = FND_API.G_TRUE THEN
83 SAVEPOINT PROJ_REQ_PUB_CREATE_REQ;
84 END IF;
85
86 --Clear the global PL/SQL message table
87 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
88 FND_MSG_PUB.initialize;
89 END IF;
90
91 -- Validate Status
92
93 PA_PROJECT_STUS_UTILS.Check_Status_Name_Or_Code ( p_status_code => p_request_status_code
94 ,p_status_name => p_request_status_name
95 ,p_status_type => 'PROJ_REQ'
96 ,p_check_id_flag => PA_STARTUP.G_Check_ID_Flag
97 ,x_status_code => l_request_status_code
98 ,x_return_status => x_return_status
99 ,x_error_message_code => l_error_message_code);
100
101 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
102 RAISE l_check_status_err;
103 END IF;
104
105
106 --Check duplicate request name.
107 PA_PROJECT_REQUEST_PVT.debug('PA_PROJECT_REQUESTS_PUB.create_project_request: Calling PA_PROJECT_REQUEST_PVT.Req_Name_Duplicate');
108
109 PA_PROJECT_REQUEST_PVT.Req_Name_Duplicate
110 (p_request_name => p_request_name,
111 x_return_status => x_return_status,
112 x_msg_count => x_msg_count,
113 x_msg_data => x_msg_data );
114
115
116 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
117
118
119 PA_PROJECT_REQUEST_PVT.debug('PA_PROJECT_REQUESTS_PUB.create_project_request: Calling PA_PROJECT_REQUEST_PKG.insert_row');
120
121 PA_PROJECT_REQUEST_PKG.insert_row
122 ( p_request_name => p_request_name ,
123 p_request_type => p_request_type,
124 p_request_status_code => l_request_status_code,
125 p_description => p_description,
126 p_expected_proj_approval_date => p_expected_proj_approval_date,
127 p_closed_date => null,
128 p_source_type => p_source_type ,
129 p_application_id => p_application_id,
130 p_source_reference => p_source_reference,
131 p_value => p_value,
132 p_currency_code => p_currency_code,
133 p_cust_party_id => p_cust_party_id,
134 p_cust_party_site_id => p_cust_party_site_id,
135 p_cust_account_id => p_cust_account_id,
136 p_source_org_id => p_source_org_id,
137 p_record_version_number => 1 ,
138 p_attribute_category => p_attribute_category,
139 p_attribute1 => p_attribute1,
140 p_attribute2 => p_attribute2,
141 p_attribute3 => p_attribute3,
142 p_attribute4 => p_attribute4,
143 p_attribute5 => p_attribute5,
144 p_attribute6 => p_attribute6,
145 p_attribute7 => p_attribute7,
146 p_attribute8 => p_attribute8,
147 p_attribute9 => p_attribute9,
148 p_attribute10 => p_attribute10,
149 p_attribute11 => p_attribute11,
150 p_attribute12 => p_attribute12,
151 p_attribute13 => p_attribute13,
152 p_attribute14 => p_attribute14,
153 p_attribute15 => p_attribute15,
154 x_request_id => x_request_id,
155 x_request_number => x_request_number,
156 x_return_status => x_return_status,
157 x_msg_count => x_msg_count,
158 x_msg_data => x_msg_data );
159 END IF;
160
161 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
162 IF NVL(p_create_rel_flag, 'N') = 'Y' AND
163 p_source_id IS NOT NULL AND
164 p_source_object IS NOT NULL THEN
165
166 -- Form the relationship: from the source opportunity to the created project request.
167
168 PA_PROJECT_REQUEST_PVT.debug('PA_PROJECT_REQUESTS_PUB.create_project_request: Calling PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW to form the relationship.' );
169
170 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
171 p_user_id => FND_GLOBAL.USER_ID
172 ,p_object_type_from => p_source_object
173 ,p_object_id_from1 => p_source_id
174 ,p_object_id_from2 => NULL
175 ,p_object_id_from3 => NULL
176 ,p_object_id_from4 => NULL
177 ,p_object_id_from5 => NULL
178 ,p_object_type_to => 'PA_PROJECT_REQUESTS'
179 ,p_object_id_to1 => x_request_id
180 ,p_object_id_to2 => NULL
181 ,p_object_id_to3 => NULL
182 ,p_object_id_to4 => NULL
183 ,p_object_id_to5 => NULL
184 ,p_relationship_type => 'A'
185 ,p_relationship_subtype => 'PROJECT_REQUEST'
186 ,p_lag_day => NULL
187 ,p_imported_lag => NULL
188 ,p_priority => NULL
189 ,p_pm_product_code => NULL
190 ,x_object_relationship_id => l_new_obj_rel_id
191 ,x_return_status => x_return_status
192 );
193
194 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
195 -- Form the relationship: from the created project request to the source opportunity.
196
197 PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
198 p_user_id => FND_GLOBAL.USER_ID
199 ,p_object_type_from => 'PA_PROJECT_REQUESTS'
200 ,p_object_id_from1 => x_request_id
201 ,p_object_id_from2 => NULL
202 ,p_object_id_from3 => NULL
203 ,p_object_id_from4 => NULL
204 ,p_object_id_from5 => NULL
205 ,p_object_type_to => p_source_object
206 ,p_object_id_to1 => p_source_id
207 ,p_object_id_to2 => NULL
208 ,p_object_id_to3 => NULL
209 ,p_object_id_to4 => NULL
210 ,p_object_id_to5 => NULL
211 ,p_relationship_type => 'A'
212 ,p_relationship_subtype => 'PROJECT_REQUEST'
213 ,p_lag_day => NULL
214 ,p_imported_lag => NULL
215 ,p_priority => NULL
216 ,p_pm_product_code => NULL
217 ,x_object_relationship_id => l_new_obj_rel_id2
218 ,x_return_status => x_return_status
219 );
220
221 END IF;
222 END IF;
223 END IF;
224
225
226 -- Reset the error stack when returning to the calling program
227
228 PA_DEBUG.Reset_Err_Stack;
229
230 EXCEPTION
231 WHEN l_check_status_err THEN
232 PA_UTILS.Add_Message('PA', l_error_message_code);
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 x_msg_data := l_error_message_code;
235 x_msg_count := FND_MSG_PUB.Count_Msg;
236 IF x_msg_count = 1 THEN
237 pa_interface_utils_pub.get_messages
238 (p_encoded => FND_API.G_TRUE,
239 p_msg_index => 1,
240 p_msg_count => x_msg_count,
241 p_msg_data => x_msg_data,
242 --p_data => x_msg_data, * commented for Bug: 4537865
243 p_data => l_new_msg_data, -- added for Bug: 4537865
244 p_msg_index_out => l_msg_index_out );
245
246 -- added for Bug: 4537865
247 x_msg_data := l_new_msg_data;
248 -- added for Bug: 4537865
249
250 END IF;
251
252 WHEN OTHERS THEN
253 IF p_commit = FND_API.G_TRUE THEN
254 ROLLBACK TO PROJ_REQ_PUB_CREATE_REQ ;
255 END IF;
256
257 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
258 x_msg_count := FND_MSG_PUB.Count_Msg;
259 x_msg_data := substr(SQLERRM,1,240);
260
261 -- Set the excetption Message and the stack
262 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PUB.create_project_request'
263 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
264 IF x_msg_count = 1 THEN
265 pa_interface_utils_pub.get_messages
266 (p_encoded => FND_API.G_TRUE,
267 p_msg_index => 1,
268 p_msg_count => x_msg_count,
269 p_msg_data => x_msg_data,
270 --p_data => x_msg_data, * commented for Bug: 4537865
271 p_data => l_new_msg_data, -- added for bug fix : 4537865
272 p_msg_index_out => l_msg_index_out );
273 -- added for bug fix : 4537865
274 x_msg_data := l_new_msg_data;
275 -- added for bug fix : 4537865
276 END IF;
277
278 RAISE; -- This is optional depending on the needs
279
280 END create_project_request;
281
282
283 --
284 --
285
286 PROCEDURE cancel_project_request
287 (p_request_id IN pa_project_requests.request_id%TYPE,
288 p_request_name IN pa_project_requests.request_name%TYPE,
289 p_request_sys_status IN pa_project_statuses.project_system_status_code%TYPE,
290 p_record_version_number IN NUMBER DEFAULT NULL,
291 p_api_version IN NUMBER :=1.0,
292 p_init_msg_list IN VARCHAR2,
293 p_commit IN VARCHAR2,
294 p_validate_only IN VARCHAR2,
295 p_max_msg_count IN NUMBER,
296 x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
297 x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
298 x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
299 IS
300 cancel_req_not_allowed EXCEPTION;
301 l_msg_index_out NUMBER;
302 -- added for bug: 4537865
303 l_new_msg_data VARCHAR2(2000);
304 -- added for bug: 4537865
305
306 BEGIN
307
308
309 -- Initialize the Error Stack
310 PA_DEBUG.init_err_stack('PA_PROJECT_REQUEST_PUB.cancel_project_request');
311
312 -- Initialize the return status to success
313 x_return_status := FND_API.G_RET_STS_SUCCESS;
314
315 --Issue API savepoint if the transaction is to be committed
316 IF p_commit = FND_API.G_TRUE THEN
317 SAVEPOINT PROJ_REQ_PUB_CANCEL_REQUEST;
318 END IF;
319
320 --Clear the global PL/SQL message table
321 IF FND_API.TO_BOOLEAN( p_init_msg_list ) THEN
322 FND_MSG_PUB.initialize;
323 END IF;
324
325 PA_PROJECT_REQUEST_PVT.debug('PA_PROJECT_REQUESTS_PUB.cancel_project_request.begin');
326
327
328 -- Check if the user is allowed to cancel the project request.
329 -- For any project request with a status of 'CANCELED' or 'CLOSED',
330 -- user is not allowed to cancel it.
331
332 IF p_request_sys_status = 'PROJ_REQ_CANCELED' OR
333 p_request_sys_status = 'PROJ_REQ_CLOSED' THEN
334
335 RAISE cancel_req_not_allowed;
336 END IF;
337
338 --Log Message
339
340 PA_PROJECT_REQUEST_PVT.debug('PA_PROJECT_REQUESTS_PUB.cancel_project_request: Calling PA_PROJECT_REQUEST_PKG.update_row');
341
342
343 -- Call the table handler
344
345 PA_PROJECT_REQUEST_PKG.update_row
346 ( p_request_id =>p_request_id,
347 p_request_status_code =>'123',
348 p_closed_date => null,
349 p_record_version_number =>p_record_version_number,
350 x_return_status =>x_return_status,
351 x_msg_count =>x_msg_count,
352 x_msg_data =>x_msg_data );
353
354
355 -- Reset the error stack when returning to the calling program
356 PA_DEBUG.Reset_Err_Stack;
357
358 EXCEPTION
359 WHEN cancel_req_not_allowed THEN
360 PA_UTILS.add_message(p_app_short_name => 'PA',
361 p_msg_name => 'PA_CANNOT_CANCEL_REQ');
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 x_msg_count := FND_MSG_PUB.Count_Msg;
364 x_msg_data := 'PA_CANNOT_CANCEL_REQ';
365
366 IF x_msg_count = 1 THEN
367 pa_interface_utils_pub.get_messages
368 (p_encoded => FND_API.G_TRUE,
369 p_msg_index => 1,
370 p_msg_count => x_msg_count,
371 p_msg_data => x_msg_data,
372 --p_data => x_msg_data, * commented for Bug: 4537865
373 p_data => l_new_msg_data, -- added for bug: 4537865
374 p_msg_index_out => l_msg_index_out );
375 -- added for bug: 4537865
376 x_msg_data := l_new_msg_data;
377 -- added for bug: 4537865
378 END IF;
379
380
381 WHEN OTHERS THEN
382 IF p_commit = FND_API.G_TRUE THEN
383 ROLLBACK TO PROJ_REQ_PUB_CANCEL_REQUEST;
384 END IF;
385
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
387 x_msg_count := FND_MSG_PUB.Count_Msg;
388 x_msg_data := substr(SQLERRM,1,240);
389
390 -- Set the excetption Message and the stack
391 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUEST_PUB.cancel_project_request'
392 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
393 IF x_msg_count = 1 THEN
394 pa_interface_utils_pub.get_messages
395 (p_encoded => FND_API.G_TRUE,
396 p_msg_index => 1,
397 p_msg_count => x_msg_count,
398 p_msg_data => x_msg_data,
399 --p_data => x_msg_data, * commented for Bug: 4537865
400 p_data => l_new_msg_data, -- added for Bug: 4537865
401 p_msg_index_out => l_msg_index_out );
402 -- added for Bug: 4537865
403 x_msg_data := l_new_msg_data;
404 -- added for Bug: 4537865
405 END IF;
406
407 RAISE; -- This is optional depending on the needs
408
409
410 END cancel_project_request;
411
412 -- Update_PC_PARTY_MAERGE (PUBLIC)
413 -- This is the procedure being called during the Party Merge.
414 -- The input/output arguments format matches the document PartyMergeDD.doc.
415 -- The goal is to fix CUST_PARTY_ID in pa_project_requests table to point to the
416 -- same party when two parties are begin merged.
417 --
418 -- Usage example in pl/sql
419 -- This procedure should only be called from the PartyMerge utility.
420
421 procedure Party_Merge(
422 p_entity_name IN varchar2
423 ,p_from_id IN number
424 ,p_to_id in OUT nocopy number
425 ,p_from_fk_id IN number
426 ,p_to_fk_id IN number
427 ,p_parent_entity_name IN varchar2
428 ,p_batch_id IN number
429 ,p_batch_party_id IN number
430 ,p_return_status IN OUT nocopy varchar2
431 ) IS
432 BEGIN
433
434 p_return_status := FND_API.G_RET_STS_SUCCESS;
435
436 if (p_from_fk_id <> p_to_fk_id) then
437
438 update PA_PROJECT_REQUESTS
439 set CUST_PARTY_ID = p_to_fk_id,
440 last_update_date = hz_utility_pub.last_update_date,
441 last_updated_by = hz_utility_pub.user_id,
442 last_update_login = hz_utility_pub.last_update_login,
443 record_version_number = nvl(record_Version_number,0) +1
444 where CUST_PARTY_ID = p_from_fk_id;
445
446 p_to_id := p_from_id;
447
448 end if;
449
450 END Party_Merge;
451
452 END pa_project_request_pub;