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