1 PACKAGE BODY PO_PROJECT_DETAILS_SV AS
2 /* $Header: POXPROJB.pls 120.1 2005/11/09 12:43:29 vinokris noship $ */
3
4 --< Bug 3265539 Start >
5 -- Debugging booleans used to bypass logging when turned off
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
8
9 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_PROJECT_DETAILS_SV';
10 g_module_prefix CONSTANT VARCHAR2(40) := 'po.plsql.' || g_pkg_name || '.';
11
12 ---------------------------------------------------------------------------
13 --Start of Comments
14 --Name: get_project_task_num
15 --Pre-reqs:
16 -- None.
17 --Modifies:
18 -- FND_MSG_PUB
19 --Locks:
20 -- None.
21 --Function:
22 -- Gets the project number and task number given their ID's. Appends to the API
23 -- message list upon error.
24 --Parameters:
25 --IN:
26 --p_project_id
27 --p_task_id
28 --OUT:
29 --x_return_status
30 -- FND_API.g_ret_sts_success - if the procedure completed successfully
31 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
32 --x_project_num
33 -- The project number. If not found, this will be NULL.
34 --x_task_num
35 -- The task number. If not found, this will be NULL.
36 --End of Comments
37 --------------------------------------------------------------------------------
38 PROCEDURE get_project_task_num
39 (
40 x_return_status OUT NOCOPY NUMBER,
41 p_project_id IN NUMBER,
42 p_task_id IN NUMBER,
43 x_project_num OUT NOCOPY VARCHAR2,
44 x_task_num OUT NOCOPY VARCHAR2
45 )
46 IS
47
48 l_progress VARCHAR2(3) := '000';
49 l_return_status VARCHAR2(1);
50
51 BEGIN
52 IF (p_project_id IS NOT NULL) THEN
53
54 l_progress := '010';
55 all_proj_idtonum_wpr(x_return_status => l_return_status,
56 p_project_id => p_project_id,
57 x_project_number => x_project_num);
58
59 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
60 RAISE FND_API.g_exc_unexpected_error;
61 END IF;
62
63 IF (p_task_id IS NOT NULL) THEN
64 l_progress := '020';
65 BEGIN
66 SELECT task_number
67 INTO x_task_num
68 FROM pa_tasks
69 WHERE task_id = p_task_id
70 AND project_id = p_project_id;
71 EXCEPTION
72 WHEN NO_DATA_FOUND THEN
73 NULL;
74 END;
75 END IF; --< if task id not null >
76
77 END IF; --< if project id not null >
78
79 EXCEPTION
80 WHEN FND_API.g_exc_unexpected_error THEN
81 x_return_status := FND_API.g_ret_sts_unexp_error;
82 IF g_debug_unexp THEN
83 PO_DEBUG.debug_exc
84 (p_log_head => g_module_prefix||'get_project_task_num',
85 p_progress => l_progress);
86 END IF;
87 WHEN OTHERS THEN
88 x_return_status := FND_API.g_ret_sts_unexp_error;
89 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
90 p_procedure_name => 'get_project_task_num',
91 p_error_text => 'Progress: '||l_progress||
92 ' Error: '||SUBSTRB(SQLERRM,1,215));
93 IF g_debug_unexp THEN
94 PO_DEBUG.debug_exc
95 (p_log_head => g_module_prefix||'get_project_task_num',
96 p_progress => l_progress);
97 END IF;
98 END get_project_task_num ;
99 --< Bug 3265539 End >
100
101
102 procedure get_project_details(
103 p_receipt_source_code in varchar2,
104 p_po_distribution_id in number,
105 p_req_distribution_id in number,
106 p_oe_order_line_id in number,
107 p_project_id in out NOCOPY number,
108 p_task_id in out NOCOPY number,
109 p_project_num in out NOCOPY varchar2,
110 p_task_num in out NOCOPY varchar2
111 ) IS
112
113 x_progress varchar2(3) := '000' ;
114 l_return_status VARCHAR2(1); --< Bug 3265539 >
115
116 BEGIN
117
118
119 IF (p_receipt_source_code <> 'CUSTOMER') THEN
120
121 IF (p_po_distribution_id IS NOT NULL ) THEN
122
123 x_progress := '010';
124
125 SELECT project_id, task_id
126 INTO p_project_id, p_task_id
127 FROM po_distributions
128 WHERE po_distribution_id = p_po_distribution_id;
129
130 ELSIF (p_req_distribution_id IS NOT NULL) THEN
131
132 x_progress := '020';
133
134 SELECT project_id, task_id
135 INTO p_project_id, p_task_id
136 FROM po_req_distributions
137 WHERE distribution_id = p_req_distribution_id;
138
139 END IF;
140
141 ELSE
142
143 IF (p_oe_order_line_id IS NOT NULL ) THEN
144
145 x_progress := '030';
146
147 SELECT project_id, task_id
148 INTO p_project_id,p_task_id
149 FROM oe_order_lines
150 WHERE line_id = p_oe_order_line_id;
151
152 END IF;
153
154 END IF;
155
156 IF p_project_id is not null THEN
157
158 -- Bug#1965131.Commented the following.
159
160 /**
161 AND
162 p_task_id is not null) THEN
163 **/
164
165 x_progress := '040';
166
167 --< Bug 3265539 Start > Changed signature
168 get_project_task_num(x_return_status => l_return_status,
169 p_project_id => p_project_id,
170 p_task_id => p_task_id,
171 x_project_num => p_project_num,
172 x_task_num => p_task_num);
173
174 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
175 RAISE APP_EXCEPTION.application_exception;
176 END IF;
177 --< Bug 3265539 End >
178
179 END IF ;
180
181 EXCEPTION
182
183 WHEN OTHERS THEN
184 po_message_s.sql_error('get_project_details',x_progress,sqlcode);
185 raise ;
186
187 END get_project_details ;
188
189 --< Bug 3265539 Start >
190 ---------------------------------------------------------------------------
191 --Start of Comments
192 --Name: all_proj_idtonum_wpr
193 --Pre-reqs:
194 -- None.
195 --Modifies:
196 -- FND_MSG_PUB
197 --Locks:
198 -- None.
199 --Function:
200 -- This is a wrapper gets the project number of the given project ID using the
201 -- PJM function PJM_PROJECT.all_proj_idtonum. Appends to the API message list
202 -- upon error.
203 --Parameters:
204 --IN:
205 --p_project_id
206 --OUT:
207 --x_return_status
208 -- FND_API.g_ret_sts_success - if the procedure completed successfully
209 -- FND_API.g_ret_sts_unexp_error - unexpected error occurred
210 --x_project_number
211 -- The project number. If not found, this will be NULL.
212 --End of Comments
213 --------------------------------------------------------------------------------
214 PROCEDURE all_proj_idtonum_wpr
215 (
216 x_return_status OUT NOCOPY VARCHAR2,
217 p_project_id IN NUMBER,
218 x_project_number OUT NOCOPY VARCHAR2
219 )
220 IS
221 BEGIN
222 x_return_status := FND_API.g_ret_sts_success;
223
224 x_project_number := PJM_PROJECT.all_proj_idtonum
225 (x_project_id => p_project_id);
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 x_project_number := NULL;
230 WHEN OTHERS THEN
231 x_return_status := FND_API.g_ret_sts_unexp_error;
232 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
233 p_procedure_name => 'all_proj_idtonum_wpr');
234 IF g_debug_unexp THEN
235 PO_DEBUG.debug_exc
236 (p_log_head => g_module_prefix||'all_proj_idtonum_wpr',
237 p_progress => '000');
238 END IF;
239 END all_proj_idtonum_wpr;
240
241 ---------------------------------------------------------------------------
242 --Start of Comments
243 --Name: validate_proj_references_wpr
244 --Pre-reqs:
245 -- None.
246 --Modifies:
247 -- FND_MESSAGE
248 -- FND_MSG_PUB
249 --Locks:
250 -- None.
251 --Function:
252 -- This is a wrapper that validates the project by calling the PJM API
253 -- PJM_PROJECT.validate_proj_references. The parameter p_operating_unit gets
254 -- defaulted if it is NULL. Appends to the API message list upon error.
255 --Parameters:
256 --IN:
257 --p_inventory_org_id
258 -- Typically the destination inventory org ID of the Req line, or the ship-to
259 -- org ID of the PO shipment.
260 --p_operating_unit
261 -- The operating unit to validate in. For standard behavior, this should be the
262 -- operating unit associated with p_inventory_org_id. If this param is NULL,
263 -- then it is defaulted according to the standard.
264 --p_project_id
265 --p_task_id
266 --p_date1
267 -- Typically the need by date of the Req line/PO shipment. This is passed into
268 -- the PJM API truncated.
269 --p_date2
270 -- Typically the promised date of the PO shipment, and NULL for a Req line.
271 -- This is passed into the PJM API truncated.
272 --p_calling_function
273 -- Used for any custom validations in PJM_PROJECT_EXT.val_project_references.
274 -- Typically, this is a module name like 'POXPOEPO' or 'PDOI'.
275 --OUT:
276 --x_error_code
277 -- Error code returned by PJM API.
278 --x_return_code
279 -- Return code of the PJM API function call. Valid values are:
280 -- PO_PROJECT_DETAILS_SV.pjm_validate_success
281 -- PO_PROJECT_DETAILS_SV.pjm_validate_warning
282 -- PO_PROJECT_DETAILS_SV.pjm_validate_failure
283 --Notes:
284 -- This is a simple wrapper for the PJM API that should be used for all calls
285 -- within PO to provide visibility and flexibility.
286 --End of Comments
287 --------------------------------------------------------------------------------
288 PROCEDURE validate_proj_references_wpr
289 (
290 p_inventory_org_id IN NUMBER,
291 p_operating_unit IN NUMBER,
292 p_project_id IN NUMBER,
293 p_task_id IN NUMBER,
294 p_date1 IN DATE,
295 p_date2 IN DATE,
296 p_calling_function IN VARCHAR2,
297 x_error_code OUT NOCOPY VARCHAR2,
298 x_return_code OUT NOCOPY VARCHAR2
299 )
300 IS
301
302 l_progress VARCHAR2(3);
303 l_return_status VARCHAR2(1);
304 l_pjm_org_id FINANCIALS_SYSTEM_PARAMS_ALL.org_id%TYPE;
305
306 BEGIN
307 l_progress := '000';
308
309 IF g_debug_stmt THEN
310 PO_DEBUG.debug_stmt
311 (p_log_head => g_module_prefix||'validate_proj_references_wpr',
312 p_token => 'BEGIN',
313 p_message => 'inv org ID: '||p_inventory_org_id||' ou ID: '||
314 p_operating_unit||' project ID: '||p_project_id||' task ID: '||
315 p_task_id||' date1: '||TO_CHAR(p_date1,'DD-MON-RRRR HH24:MI:SS')
316 ||' date2: '||TO_CHAR(p_date2,'DD-MON-RRRR HH24:MI:SS')||
317 ' calling function: '||p_calling_function);
318 END IF;
319
320 IF (p_operating_unit IS NULL) THEN
321 l_progress := '010';
322
323 -- Default the operating unit if not passed in
324 PO_CORE_S.get_inv_org_ou_id(x_return_status => l_return_status,
325 p_inv_org_id => p_inventory_org_id,
326 x_ou_id => l_pjm_org_id);
327
328 IF (l_return_status <> FND_API.g_ret_sts_success) THEN
329 RAISE FND_API.g_exc_unexpected_error;
330 END IF;
331 ELSE
332 l_pjm_org_id := p_operating_unit;
333 END IF;
334
335 l_progress := '020';
336
337 --< NBD TZ/Timestamp FPJ >
338 -- Truncate the timestamped dates. Inside the API they would be used in a
339 -- comparison with effectivity dates that are not timestamped.
340 x_return_code :=
341 PJM_PROJECT.validate_proj_references
342 (x_inventory_org_id => p_inventory_org_id,
343 x_operating_unit => l_pjm_org_id,
344 x_project_id => p_project_id,
345 x_task_id => p_task_id,
346 x_date1 => TRUNC(p_date1),
347 x_date2 => TRUNC(p_date2),
348 x_calling_function => p_calling_function,
349 x_error_code => x_error_code);
350
351 IF g_debug_stmt THEN
352 PO_DEBUG.debug_stmt
353 (p_log_head => g_module_prefix||'validate_proj_references_wpr',
354 p_token => 'END',
355 p_message => 'return code: '||x_return_code||' error code: '||
356 x_error_code);
357 END IF;
358 EXCEPTION
359 WHEN FND_API.g_exc_unexpected_error THEN
360 x_return_code := pjm_validate_failure;
361 IF g_debug_unexp THEN
362 PO_DEBUG.debug_exc
363 (p_log_head => g_module_prefix||'validate_proj_references_wpr',
364 p_progress => l_progress);
365 END IF;
366 WHEN OTHERS THEN
367 x_return_code := pjm_validate_failure;
368 FND_MSG_PUB.add_exc_msg(p_pkg_name => g_pkg_name,
369 p_procedure_name => 'validate_proj_references_wpr',
370 p_error_text => 'Progress: '||l_progress||
371 ' Error: '||SUBSTRB(SQLERRM,1,215));
372 IF g_debug_unexp THEN
373 PO_DEBUG.debug_exc
374 (p_log_head => g_module_prefix||'validate_proj_references_wpr',
375 p_progress => l_progress);
376 END IF;
377 END validate_proj_references_wpr;
378
379 --------------------------------------------------------------------------------
380 --Start of Comments
381 --Name: pjm_validate_success
382 --Pre-reqs:
383 -- None.
384 --Modifies:
385 -- None.
386 --Locks:
387 -- None.
388 --Function:
389 -- Returns the global package variable PJM_PROJECT.g_validate_success.
390 --Returns:
391 -- PJM_PROJECT.g_validate_success
392 --End of Comments
393 --------------------------------------------------------------------------------
394 FUNCTION pjm_validate_success RETURN VARCHAR2
395 IS
396 BEGIN
397 RETURN PJM_PROJECT.g_validate_success;
398 END;
399
400 --------------------------------------------------------------------------------
401 --Start of Comments
402 --Name: pjm_validate_warning
403 --Pre-reqs:
404 -- None.
405 --Modifies:
406 -- None.
407 --Locks:
408 -- None.
409 --Function:
410 -- Returns the global package variable PJM_PROJECT.g_validate_warning.
411 --Returns:
412 -- PJM_PROJECT.g_validate_warning
413 --End of Comments
414 --------------------------------------------------------------------------------
415 FUNCTION pjm_validate_warning RETURN VARCHAR2
416 IS
417 BEGIN
418 RETURN PJM_PROJECT.g_validate_warning;
419 END;
420
421 --------------------------------------------------------------------------------
422 --Start of Comments
423 --Name: pjm_validate_failure
424 --Pre-reqs:
425 -- None.
426 --Modifies:
427 -- None.
428 --Locks:
429 -- None.
430 --Function:
431 -- Returns the global package variable PJM_PROJECT.g_validate_failure.
432 --Returns:
433 -- PJM_PROJECT.g_validate_failure
434 --End of Comments
435 --------------------------------------------------------------------------------
436 FUNCTION pjm_validate_failure RETURN VARCHAR2
437 IS
438 BEGIN
439 RETURN PJM_PROJECT.g_validate_failure;
440 END;
441
442 --< Bug 3265539 End >
443
444 --< Bug 4338241 Start >
445 --Adding these 2 functions for Unit Number
446 FUNCTION pjm_unit_eff_enabled RETURN VARCHAR2
447 IS
448 BEGIN
449 RETURN PJM_UNIT_EFF.ENABLED;
450 END;
451
452 FUNCTION pjm_unit_eff_item
453 (
454 p_item_id IN NUMBER,
455 p_org_id IN NUMBER
456 ) RETURN VARCHAR2
457 IS
458 BEGIN
459 RETURN PJM_UNIT_EFF.UNIT_EFFECTIVE_ITEM(p_item_id, p_org_id);
460 END;
461 --< Bug 4338241 END >
462
463 END PO_PROJECT_DETAILS_SV ;
[Home] [Help]