1 PACKAGE BODY PO_CLM_INTG_GRP AS
2 -- $Header: PO_CLM_INTG_GRP.plb 120.6 2011/01/21 07:55:30 athammin ship $
3 ------------------------------------------------------------------------------
4 --Start of Comments
5 --Name: is_clm_po
6 --Pre-reqs:
7 -- None
8 --Modifies:
9 -- None.
10 --Locks:
11 -- None.
12 --Function:
13 -- This function will determine whether a PO is a clm PO.
14 --Parameters:
15 --IN:
16 -- p_po_header_id
17 -- Header ID of the PO to check whether or not it's a clm PO
18 -- p_po_line_id
19 -- Line ID of the PO to check whether or not it's a clm PO
23 -- Distribution ID of the PO to check whether or not it's a clm PO
20 -- p_po_line_location_id
21 -- Line Location ID of the PO to check whether or not it's a clm PO
22 -- p_po_distribution_id
24 --RETURNS:
25 -- Y: The PO is a CLM PO
26 -- N: The PO is not a CLM PO
27 --End of Comments
28 -------------------------------------------------------------------------------
29 FUNCTION Is_clm_po
30 ( p_po_header_id IN NUMBER DEFAULT NULL,
31 p_po_line_id IN NUMBER DEFAULT NULL,
32 p_po_line_location_id IN NUMBER DEFAULT NULL,
33 p_po_distribution_id IN NUMBER DEFAULT NULL
34 )
35 RETURN VARCHAR2
36 IS
37 d_module VARCHAR2(70) := 'po.plsql.PO_CLM_INTG_GRP.is_CLM_po';
38 d_progress NUMBER;
39 l_style_id po_headers_all.style_id%TYPE;
40 l_po_header_id po_headers_all.po_header_id%TYPE;
41 l_is_clm_po VARCHAR2(1) := 'N';
42 l_count NUMBER := 0;
43 BEGIN
44 d_progress := 0;
45
46 IF (po_log.d_proc) THEN
47 po_log.Proc_begin(d_module);
48 po_log.Proc_begin(d_module,'p_po_header_id',p_po_header_id);
49 po_log.Proc_begin(d_module,'p_po_line_id',p_po_line_id);
50 po_log.Proc_begin(d_module,'p_line_location_id',p_po_line_location_id);
51 po_log.Proc_begin(d_module,'p_po_distribution_id',p_po_distribution_id);
52 END IF;
53
54 IF p_po_header_id IS NOT NULL THEN
55 l_po_header_id := p_po_header_id;
56 elsif p_po_line_id is not null then
57 select l.po_header_id into l_po_header_id from po_lines_all l where l.po_line_id = p_po_line_id;
58 elsif p_po_line_location_id is not null then
59 select s.po_header_id into l_po_header_id from po_line_locations_all s where s.Line_Location_id = p_po_line_location_id;
60 else
61 select d.po_header_id into l_po_header_id from po_distributions_all d where d.po_distribution_id = p_po_distribution_id;
62 end if;
63
64
65 SELECT 'Y'
66 INTO l_is_clm_po
67 FROM po_headers_all h,
68 po_doc_style_headers pdsh
69 WHERE h.po_header_id = l_po_header_id
70 AND h.style_id = pdsh.style_id
71 AND NVL(pdsh.clm_flag,'N') = 'Y';
72
73
74
75 d_progress := 10;
76
77 RETURN l_is_clm_po;
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 RETURN l_is_clm_po;
81 WHEN OTHERS THEN
82 IF (po_log.d_exc) THEN
83 po_log.Exc(d_module,d_progress,SQLCODE
84 ||sqlerrm);
85 po_log.Proc_end(d_module);
86 END IF;
87 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
88 END is_clm_po;
89
90 ------------------------------------------------------------------------------
91 --Start of Comments
92 --Name: is_clm_instance
93 --Pre-reqs:
94 -- None
95 --Modifies:
96 -- None.
97 --Locks:
98 -- None.
99 --Function:
100 -- This function will check if its a CLM instance
101 --Parameters:
102 --IN:
103 --RETURNS:
104 -- Y: This is a CLM Instance
105 -- N: This is Not a CLM Instance
106 --End of Comments
107 -------------------------------------------------------------------------------
108 FUNCTION is_clm_installed
109 RETURN VARCHAR2
110 IS
111 d_module VARCHAR2(70) := 'po.plsql.PO_CLM_INTG_GRP.is_clm_installed';
112 d_progress NUMBER;
113 is_clm_installed VARCHAR2(1) := 'N';
114 BEGIN
115 d_progress := 0;
116 -- CLM Intg
117 --This new function takes care of clm installation details
118 is_clm_installed := NVL(FND_PROFILE.VALUE('PO_CLM_INSTALLED'),'N');
119 --po_clm_clo_util.getCLMStatus;
120
121 IF (po_log.d_proc) THEN
122 po_log.Proc_begin(d_module);
123 END IF;
124
125 d_progress := 10;
126
127 RETURN is_clm_installed;
128 EXCEPTION
129 WHEN OTHERS THEN
130 IF (po_log.d_exc) THEN
131 po_log.Exc(d_module,d_progress,SQLCODE
132 ||sqlerrm);
133
134 po_log.Proc_end(d_module);
135 END IF;
136
137 RAISE;
138 END is_clm_installed;
139
140
141
142 PROCEDURE get_po_dist_values(
143 p_min_unit_meas_lookup_code IN VARCHAR2,
144 p_min_matching_basis IN VARCHAR2,
145 p_min_distribution_type IN VARCHAR2,
146 p_min_accrue_on_receipt_flag IN VARCHAR2,
147 p_min_code_combination_id IN NUMBER,
148 p_min_budget_account_id IN NUMBER,
149 p_min_partial_funded_flag IN VARCHAR2,
150 p_max_unit_meas_lookup_code IN VARCHAR2,
151 p_max_matching_basis IN VARCHAR2,
152 p_max_distribution_type IN VARCHAR2,
153 p_max_accrue_on_receipt_flag IN VARCHAR2,
154 p_max_code_combination_id IN NUMBER,
155 p_max_budget_account_id IN NUMBER,
156 p_max_partial_funded_flag IN VARCHAR2,
157 x_unit_meas_lookup_code OUT NOCOPY VARCHAR2,
158 x_matching_basis OUT NOCOPY VARCHAR2,
159 x_distribution_type OUT NOCOPY VARCHAR2,
160 x_accrue_on_receipt_flag OUT NOCOPY VARCHAR2,
161 x_code_combination_id OUT NOCOPY NUMBER,
162 x_budget_account_id OUT NOCOPY NUMBER,
163 x_partial_funded_flag OUT NOCOPY VARCHAR2)
164 IS
165 BEGIN
166
167 IF (p_min_unit_meas_lookup_code = p_max_unit_meas_lookup_code AND p_min_unit_meas_lookup_code IS NOT NULL AND
168 p_max_unit_meas_lookup_code IS NOT null) THEN
169 x_unit_meas_lookup_code := p_min_unit_meas_lookup_code;
170 ELSE
171 x_unit_meas_lookup_code := NULL;
172 END IF;
173
174 IF (p_min_matching_basis = p_max_matching_basis AND p_min_matching_basis IS NOT NULL AND
175 p_max_matching_basis IS NOT null) THEN
176 x_matching_basis := p_min_matching_basis;
177 ELSE
178 x_matching_basis := NULL;
179 END IF;
180
181 IF (p_min_distribution_type = p_max_distribution_type AND p_min_distribution_type IS NOT NULL AND
182 p_max_distribution_type IS NOT null) THEN
183 x_distribution_type := p_min_distribution_type;
184 ELSE
185 x_distribution_type := NULL;
186 END IF;
187
188 IF (p_min_accrue_on_receipt_flag = p_max_accrue_on_receipt_flag AND p_min_accrue_on_receipt_flag IS NOT NULL AND
189 p_max_accrue_on_receipt_flag IS NOT null) THEN
190 x_accrue_on_receipt_flag := p_min_accrue_on_receipt_flag;
191 ELSE
192 x_accrue_on_receipt_flag := NULL;
193 END IF;
194
195 IF (p_min_code_combination_id = p_max_code_combination_id AND p_min_code_combination_id IS NOT NULL AND
196 p_max_code_combination_id IS NOT null) THEN
197 x_code_combination_id := p_min_code_combination_id;
198 ELSE
199 x_code_combination_id := NULL;
200 END IF;
201
202 IF (p_min_budget_account_id = p_max_budget_account_id AND p_min_budget_account_id IS NOT NULL AND
203 p_max_budget_account_id IS NOT null) THEN
204 x_budget_account_id := p_min_budget_account_id;
205 ELSE
206 x_budget_account_id := NULL;
207 END IF;
208
209 IF (p_min_partial_funded_flag = p_max_partial_funded_flag AND p_min_partial_funded_flag IS NOT NULL AND
210 p_max_partial_funded_flag IS NOT null) THEN
211 x_partial_funded_flag := p_min_partial_funded_flag;
212 ELSE
213 x_partial_funded_flag := 'N';
214 END IF;
215 END get_po_dist_values;
216
217
218 ------------------------------------------------------------------------------
219 --Start of Comments
220 --Name: Get_funding_info
221 --Pre-reqs:
222 -- None
223 --Modifies:
224 -- None.
225 --Locks:
226 -- None.
227 --Procedure:
228 -- This procedure returns the PO Funding Information for a given entity id
229 -- Used by Invoicing and Receiving
230 --Parameters:
231 --IN:
232 -- p_po_header_id - Header ID of the PO
233 -- p_po_line_id - Line ID of the PO
234 -- p_po_line_location_id - Line Location ID of the PO
235 -- p_po_distribution_id - Distribution ID of the PO
236 --OUT:
237 -- x_distribution_type - Distribution Type
238 -- x_matching_basis - Mathcing Basis
239 -- x_accrue_on_receipt_flag - Accrue on Receipt Flag
240 -- x_code_combination_id - Code Combination Id
241 -- x_budget_account_id - Budget Account Id
242 -- x_partial_funded_flag - Partial Funded Flag
243 -- x_unit_meas_lookup_code - UOM
244 -- x_funded_value - Funded Value
245 -- x_quantity_funded - Quantity Funded
246 -- x_amount_funded - Amount Funded
247 -- x_quantity_received - Quantity Received
248 -- x_amount_received - Amount Received
249 -- x_quantity_delivered - Quantity Delivered
250 -- x_amount_delivered - Amount Delivered
251 -- x_quantity_billed - Quantity Billed
252 -- x_amount_billed - Amount Billed
253 -- x_quantity_cancelled - Quantity cancelled
254 -- x_amount_cancelled - Amount cancelled
255 -- x_return_status - Success/Error
256 -- fnd_api.g_ret_sts_success (or) fnd_api.g_ret_sts_unexp_error
257
258 --End of Comments
259 -------------------------------------------------------------------------------
260
261 PROCEDURE Get_funding_info
262 (p_po_header_id IN NUMBER DEFAULT NULL,
263 p_po_line_id IN NUMBER DEFAULT NULL,
264 p_line_location_id IN NUMBER DEFAULT NULL,
265 p_po_distribution_id IN NUMBER DEFAULT NULL,
266 x_distribution_type OUT NOCOPY VARCHAR2,
267 x_matching_basis OUT NOCOPY VARCHAR2,
268 x_accrue_on_receipt_flag OUT NOCOPY VARCHAR2,
269 x_code_combination_id OUT NOCOPY NUMBER,
270 x_budget_account_id OUT NOCOPY NUMBER,
271 x_partial_funded_flag OUT NOCOPY VARCHAR2,
272 x_unit_meas_lookup_code OUT NOCOPY VARCHAR2,
273 x_funded_value OUT NOCOPY NUMBER,
274 x_quantity_funded OUT NOCOPY NUMBER,
275 x_amount_funded OUT NOCOPY NUMBER,
276 x_quantity_received OUT NOCOPY NUMBER,
277 x_amount_received OUT NOCOPY NUMBER,
278 x_quantity_delivered OUT NOCOPY NUMBER,
279 x_amount_delivered OUT NOCOPY NUMBER,
280 x_quantity_billed OUT NOCOPY NUMBER,
281 x_amount_billed OUT NOCOPY NUMBER,
282 x_quantity_cancelled OUT NOCOPY NUMBER,
283 x_amount_cancelled OUT NOCOPY NUMBER,
284 x_return_status OUT NOCOPY VARCHAR2)
285 IS
286 d_module VARCHAR2(70) := 'po.plsql.PO_CLM_INTG_GRP.Get_Funding_Info';
287 d_progress NUMBER;
288 l_min_distribution_type po_distributions_all.distribution_type%TYPE;
289 l_min_matching_basis po_line_locations_all.matching_basis%TYPE;
290 l_min_accrue_on_receipt_flag po_distributions_all.accrue_on_receipt_flag%TYPE;
291 l_min_code_combination_id po_distributions_all.code_combination_id%TYPE;
292 l_min_budget_account_id po_distributions_all.budget_account_id%TYPE;
293 l_min_partial_funded_flag po_distributions_all.partial_funded_flag%TYPE;
294 l_min_unit_meas_lookup_code po_line_locations_all.unit_meas_lookup_code%TYPE;
295 l_max_distribution_type po_distributions_all.distribution_type%TYPE;
296 l_max_matching_basis po_line_locations_all.matching_basis%TYPE;
297 l_max_accrue_on_receipt_flag po_distributions_all.accrue_on_receipt_flag%TYPE;
298 l_max_code_combination_id po_distributions_all.code_combination_id%TYPE;
299 l_max_budget_account_id po_distributions_all.budget_account_id%TYPE;
300 l_max_partial_funded_flag po_distributions_all.partial_funded_flag%TYPE;
301 l_max_unit_meas_lookup_code po_line_locations_all.unit_meas_lookup_code%TYPE;
302
303 BEGIN
304
305 d_progress := 0;
306 IF (po_log.d_proc) THEN
307 po_log.Proc_begin(d_module);
308 po_log.Proc_begin(d_module,'p_po_header_id',p_po_header_id);
309 po_log.Proc_begin(d_module,'p_po_line_id',p_po_line_id);
310 po_log.Proc_begin(d_module,'p_line_location_id',p_line_location_id);
311 po_log.Proc_begin(d_module,'p_po_distribution_id',p_po_distribution_id);
312 END IF;
313
314 IF p_po_distribution_id IS NOT NULL THEN
315
316 d_progress := 10;
317
318 SELECT pod.distribution_type,
319 pll.matching_basis,
320 pod.accrue_on_receipt_flag,
321 pod.code_combination_id,
322 pod.budget_account_id,
323 pod.partial_funded_flag,
324 pl.unit_meas_lookup_code,
325 pod.funded_value,
326 pod.quantity_funded,
327 pod.amount_funded,
328 pll.quantity_received,
329 pll.amount_received,
330 pod.quantity_delivered,
331 pod.amount_delivered,
332 pod.quantity_billed,
333 pod.amount_billed,
334 pod.quantity_cancelled,
335 pod.amount_cancelled
336 INTO x_distribution_type,x_matching_basis,x_accrue_on_receipt_flag,x_code_combination_id,
337 x_budget_account_id,x_partial_funded_flag,x_unit_meas_lookup_code,x_funded_value,
338 x_quantity_funded,x_amount_funded,x_quantity_received,x_amount_received,
339 x_quantity_delivered,x_amount_delivered,x_quantity_billed,x_amount_billed,
340 x_quantity_cancelled, x_amount_cancelled
341 FROM po_distributions_all pod,
342 po_line_locations_all pll,
343 po_lines_all pl
344 WHERE pll.line_location_id = pod.line_location_id
345 AND pod.po_distribution_id = p_po_distribution_id
346 AND pl.po_line_id = pod.po_line_id;
347
348 ELSIF p_line_location_id IS NOT NULL THEN
349
350 d_progress := 20;
351
352 SELECT Sum(pod.funded_value),
353 Sum(pod.quantity_funded),
354 Sum(pod.amount_funded),
355 Sum(pod.quantity_delivered),
356 Sum(pod.amount_delivered),
357 Sum(pod.quantity_billed),
358 Sum(pod.amount_billed),
359 Min(pod.distribution_type),
360 Max(pod.distribution_type),
361 Min(pod.accrue_on_receipt_flag),
362 Max(pod.accrue_on_receipt_flag),
363 Min(pod.code_combination_id),
364 Max(pod.code_combination_id),
365 Min(pod.budget_account_id),
366 Max(pod.budget_account_id),
367 Min(pod.partial_funded_flag),
368 MAx(pod.partial_funded_flag),
369 Sum(pod.quantity_cancelled),
370 Sum(pod.amount_cancelled)
371 INTO x_funded_value,x_quantity_funded,x_amount_funded,x_quantity_delivered,
372 x_amount_delivered,x_quantity_billed,x_amount_billed,
373 l_min_distribution_type,l_max_distribution_type,
374 l_min_accrue_on_receipt_flag,l_max_accrue_on_receipt_flag,
375 l_min_code_combination_id,l_max_code_combination_id,
376 l_min_budget_account_id,l_max_budget_account_id,
377 l_min_partial_funded_flag,l_max_partial_funded_flag,
378 x_quantity_cancelled,x_amount_cancelled
379 FROM po_distributions_all pod
380 WHERE pod.line_location_id = p_line_location_id;
381
382
383 d_progress := 30;
384
385 get_po_dist_values(
386 NULL,
387 NULL,
388 l_min_distribution_type,
389 l_min_accrue_on_receipt_flag,
390 l_min_code_combination_id,
391 l_min_budget_account_id,
392 l_min_partial_funded_flag,
393 NULL,
394 NULL,
395 l_max_distribution_type,
396 l_max_accrue_on_receipt_flag,
397 l_max_code_combination_id,
398 l_max_budget_account_id,
399 l_max_partial_funded_flag,
400 x_unit_meas_lookup_code,
401 x_matching_basis,
402 x_distribution_type,
403 x_accrue_on_receipt_flag,
404 x_code_combination_id,
405 x_budget_account_id,
406 x_partial_funded_flag);
407
408 d_progress := 40;
409
410 SELECT pll.unit_meas_lookup_code,
411 pll.matching_basis,
412 pll.quantity_received,
413 pll.amount_received
414 INTO x_unit_meas_lookup_code,x_matching_basis,x_quantity_received,x_amount_received
415 FROM po_line_locations_all pll
416 WHERE pll.line_location_id = p_line_location_id;
417
418
419 ELSIF p_po_line_id IS NOT NULL THEN
420
421 d_progress := 50;
422
423 SELECT Sum(pod.funded_value),
424 Sum(pod.quantity_funded),
425 Sum(pod.amount_funded),
426 Sum(pod.quantity_delivered),
427 Sum(pod.amount_delivered),
428 Sum(pod.quantity_billed),
429 Sum(pod.amount_billed),
430 Min(pod.distribution_type),
431 Max(pod.distribution_type),
432 Min(pod.accrue_on_receipt_flag),
433 Max(pod.accrue_on_receipt_flag),
434 Min(pod.code_combination_id),
435 Max(pod.code_combination_id),
436 Min(pod.budget_account_id),
437 Max(pod.budget_account_id),
438 Min(pod.partial_funded_flag),
439 Max(pod.partial_funded_flag)
440 INTO x_funded_value,x_quantity_funded,x_amount_funded,x_quantity_delivered,
441 x_amount_delivered,x_quantity_billed,x_amount_billed,
442 l_min_distribution_type,l_max_distribution_type,
443 l_min_accrue_on_receipt_flag,l_max_accrue_on_receipt_flag,
444 l_min_code_combination_id,l_max_code_combination_id,
445 l_min_budget_account_id,l_max_budget_account_id,
446 l_min_partial_funded_flag,l_max_partial_funded_flag
447 FROM po_distributions_all pod
448 WHERE pod.po_line_id = p_po_line_id;
449
450 d_progress := 60;
451
452 SELECT Sum(pll.quantity_received),
453 Sum(pll.amount_received),
454 Min(pll.unit_meas_lookup_code),
455 Max(pll.unit_meas_lookup_code),
456 Min(pll.matching_basis),
457 Max(pll.matching_basis)
458 INTO x_quantity_received,x_amount_received,
459 l_min_unit_meas_lookup_code,l_max_unit_meas_lookup_code,
460 l_min_matching_basis,l_max_matching_basis
461 FROM po_line_locations_all pll
462 WHERE pll.po_line_id = p_po_line_id;
463
464 d_progress := 70;
465
466 get_po_dist_values(
467 l_min_unit_meas_lookup_code,
468 l_min_matching_basis,
469 l_min_distribution_type,
473 l_min_partial_funded_flag,
470 l_min_accrue_on_receipt_flag,
471 l_min_code_combination_id,
472 l_min_budget_account_id,
474 l_max_unit_meas_lookup_code,
475 l_max_matching_basis,
476 l_max_distribution_type,
477 l_max_accrue_on_receipt_flag,
478 l_max_code_combination_id,
479 l_max_budget_account_id,
480 l_max_partial_funded_flag,
481 x_unit_meas_lookup_code,
482 x_matching_basis,
483 x_distribution_type,
484 x_accrue_on_receipt_flag,
485 x_code_combination_id,
486 x_budget_account_id,
487 x_partial_funded_flag);
488
489 ELSIF p_po_header_id IS NOT NULL THEN
490
491 d_progress := 80;
492
493 SELECT Sum(pod.funded_value),
494 Sum(pod.quantity_funded),
495 Sum(pod.amount_funded),
496 Sum(pod.quantity_delivered),
497 Sum(pod.amount_delivered),
498 Sum(pod.quantity_billed),
499 Sum(pod.amount_billed),
500 Min(pod.distribution_type),
501 Max(pod.distribution_type),
502 Min(pod.accrue_on_receipt_flag),
503 Max(pod.accrue_on_receipt_flag),
504 Min(pod.code_combination_id),
505 Max(pod.code_combination_id),
506 Min(pod.budget_account_id),
507 Max(pod.budget_account_id),
508 Min(pod.partial_funded_flag),
509 Max(pod.partial_funded_flag)
510 INTO x_funded_value,x_quantity_funded,x_amount_funded,x_quantity_delivered,
511 x_amount_delivered,x_quantity_billed,x_amount_billed,
512 l_min_distribution_type,l_max_distribution_type,
513 l_min_accrue_on_receipt_flag,l_max_accrue_on_receipt_flag,
514 l_min_code_combination_id,l_max_code_combination_id,
515 l_min_budget_account_id,l_max_budget_account_id,
516 l_min_partial_funded_flag,l_max_partial_funded_flag
517 FROM po_distributions_all pod
518 WHERE pod.po_header_id = p_po_header_id;
519
520 d_progress := 90;
521
522 SELECT Sum(pll.quantity_received),
523 Sum(pll.amount_received),
524 Min(pll.unit_meas_lookup_code),
525 Max(pll.unit_meas_lookup_code),
526 Min(pll.matching_basis),
527 Max(pll.matching_basis)
528 INTO x_quantity_received,x_amount_received,
529 l_min_unit_meas_lookup_code,l_max_unit_meas_lookup_code,
530 l_min_matching_basis,l_max_matching_basis
531 FROM po_line_locations_all pll
532 WHERE pll.po_header_id = p_po_header_id;
533
534 d_progress := 100;
535
536 get_po_dist_values(
537 l_min_unit_meas_lookup_code,
538 l_min_matching_basis,
539 l_min_distribution_type,
540 l_min_accrue_on_receipt_flag,
541 l_min_code_combination_id,
542 l_min_budget_account_id,
543 l_min_partial_funded_flag,
544 l_max_unit_meas_lookup_code,
545 l_max_matching_basis,
546 l_max_distribution_type,
547 l_max_accrue_on_receipt_flag,
548 l_max_code_combination_id,
549 l_max_budget_account_id,
550 l_max_partial_funded_flag,
551 x_unit_meas_lookup_code,
552 x_matching_basis,
553 x_distribution_type,
554 x_accrue_on_receipt_flag,
555 x_code_combination_id,
556 x_budget_account_id,
557 x_partial_funded_flag);
558
559 ELSE
560 x_return_status := fnd_api.g_ret_sts_unexp_error;
561 d_progress := 110;
562 IF (po_log.d_stmt) THEN
563 po_log.Stmt(d_module,d_progress,'p_po_header_id, p_po_line_id, p_po_line_location_id, p_po_distribution_id are NULL!');
564 END IF;
565 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
566 END IF;
567
568 x_return_status := fnd_api.g_ret_sts_success;
569 IF (po_log.d_proc) THEN
570 po_log.Proc_end(d_module);
571 END IF;
572 EXCEPTION
573 WHEN OTHERS THEN
574 IF (po_log.d_exc) THEN
575 po_log.Exc(d_module,d_progress,SQLCODE
576 ||sqlerrm);
577 po_log.Proc_end(d_module);
578 END IF;
579 x_return_status := fnd_api.g_ret_sts_unexp_error;
580 END get_funding_info;
581
582
583 /* Hide NonFunded
584 -- To hide the shipments and distributions which are not funded
585 p_doc_type : 'REQUISITION', 'PO'
586 p_header_id : Requisition_Header_id/po_header_id
587 p_line_id : Requisition_Line_id/po_line_id
588 p_line_location_id : Line_Location_id
589 p_distribution_id : Distribution_id/Po_Distribution_Id
590 */
591 FUNCTION Hide_Nonfunded
592 (
593 p_doc_type IN VARCHAR2,
594 p_header_id IN NUMBER,
595 p_line_id IN NUMBER DEFAULT NULL,
596 p_line_location_id IN NUMBER DEFAULT NULL,
597 p_distribution_id IN NUMBER DEFAULT NULL
598 )
599 RETURN VARCHAR2
600 IS
601 d_module VARCHAR2(70) := 'po.plsql.PO_CLM_INTG_GRP.Hide_Clm_Rec';
602 d_progress NUMBER;
603 l_hide_record VARCHAR2(1) := 'N';
604 l_funded NUMBER := 0;
608 IF PO_CLM_INTG_GRP.is_clm_installed = 'N' THEN
605 l_nsp_nc_line NUMBER := 0;
606 BEGIN
607 -- If CLM is not installed, Need not hide any Records
609 RETURN l_hide_record;
610 END IF;
611 -- If CLM is Installed and Encumbrance is Not ON need not hide any Records
612 IF NOT
613 (
614 PO_CORE_S.is_encumbrance_on( p_doc_type => p_doc_type, p_org_id => NULL)
615 )
616 THEN
617 RETURN l_hide_record;
618 END IF;
619 -- If not a CLM document need not hide any records
620 IF PO_CLM_INTG_GRP.is_clm_document(p_doc_type => p_doc_type, p_document_id => p_header_id ) = 'N' THEN
624 IF p_doc_type = 'REQUISITION' THEN
621 RETURN l_hide_record;
622 END IF;
623 -- CLM enabled, Encumbrance Used, CLM Document
625 IF p_distribution_id IS NOT NULL THEN
626 SELECT NVL(d.funded_value,0)
627 INTO l_funded
628 FROM po_req_distributions_all d
629 WHERE d.distribution_id = p_distribution_id;
630
631 ELSE -- Distribution Id is null
632 IF p_line_id IS NOT NULL THEN
633 SELECT SUM(NVL(d.funded_value,0))
634 INTO l_funded
635 FROM po_req_distributions_all d
636 WHERE d.requisition_line_id = p_line_id;
637
638 ELSE -- Line Id is Null
639 SELECT SUM(NVL(d.funded_value,0))
640 INTO l_funded
641 FROM po_req_distributions_all d,
642 po_requisition_lines_all l
643 WHERE d.requisition_line_id = l.requisition_line_id
644 AND l.requisition_header_id = p_header_id;
645
646 END IF;
647 END IF; -- REQUISITION Distribution Id Check
648 ELSE
649 IF p_distribution_id IS NOT NULL THEN
650 SELECT NVL(d.funded_value,0)
651 INTO l_funded
652 FROM po_distributions_all d
653 WHERE d.po_distribution_id = p_distribution_id;
654
655 IF NOT (l_funded > 0) THEN
656 BEGIN
657 SELECT 1
658 INTO l_nsp_nc_line
659 FROM po_lines_all l,
660 po_distributions_all d
661 WHERE l.po_line_id = d.po_line_id AND
662 l.cost_constraint IN ('NSP', 'NC') AND
663 d.po_distribution_id = p_distribution_id;
664 EXCEPTION
665 WHEN No_Data_Found THEN
666 l_nsp_nc_line := 0;
667 END;
668 END IF;
669
670 ELSE
671 IF p_line_location_id IS NOT NULL THEN
672 SELECT SUM(NVL(d.funded_value,0))
673 INTO l_funded
674 FROM po_distributions_all d
675 WHERE d.line_location_id = p_line_location_id;
676
677 IF NOT (l_funded > 0) THEN
678 BEGIN
679 SELECT 1
680 INTO l_nsp_nc_line
681 FROM po_lines_all l,
682 po_line_locations_all ll
683 WHERE l.po_line_id = ll.po_line_id AND
684 l.cost_constraint IN ('NSP', 'NC') AND
685 ll.line_location_id = p_line_location_id;
686 EXCEPTION
687 WHEN No_Data_Found THEN
688 l_nsp_nc_line := 0;
689 END;
690 END IF;
691
692 ELSE
693 IF p_line_id IS NOT NULL THEN
694 SELECT SUM(NVL(d.funded_value,0))
695 INTO l_funded
696 FROM po_distributions_all d
697 WHERE d.po_line_id = p_line_id;
698
699 IF NOT (l_funded > 0) THEN
700 BEGIN
701 SELECT 1
702 INTO l_nsp_nc_line
703 FROM po_lines_all l
704 WHERE l.po_line_id = p_line_id AND
705 l.cost_constraint IN ('NSP', 'NC');
706 EXCEPTION
707 WHEN No_Data_Found THEN
708 l_nsp_nc_line := 0;
709 END;
710 END IF;
711
712 ELSE
713 SELECT SUM(NVL(d.funded_value,0))
714 INTO l_funded
715 FROM po_distributions_all d
716 WHERE d.po_header_id = p_header_id;
717
718 END IF; -- Line Location Id is Null
719 END IF;
720 END IF;
721 END IF; --DOC TYPE
722 IF l_funded > 0 OR l_nsp_nc_line = 1 THEN
723 RETURN l_hide_record;
724 ELSE
725 l_hide_record := 'Y';
726 RETURN l_hide_record;
727 END IF;
728 EXCEPTION
729 WHEN OTHERS THEN
730 RETURN l_hide_record;
731 END Hide_Nonfunded;
732
733
734 /* Is_CLM_Document
735 -- p_doc_type
736 -- 'REQUISITION'
737 -- 'PO'
738 -- 'PA'
739 -- P_DOCUMENT_ID
740 -- PO or Requisition Header Id
741 */
742 FUNCTION is_clm_document
743 (
744 p_doc_type IN VARCHAR2,
745 p_document_id IN NUMBER
746 )
747 RETURN VARCHAR2
748 IS
749 l_clm_document VARCHAR2(1) := 'N';
750 BEGIN
751 IF p_doc_type = 'REQUISITION' THEN
752 SELECT 'Y'
753 INTO l_clm_document
754 FROM po_requisition_headers_all
755 WHERE requisition_header_id = p_document_id
756 AND federal_flag = 'Y';
757
758 ELSE
759 SELECT 'Y'
760 INTO l_clm_document
761 FROM po_headers_all h,
762 po_doc_style_headers pdsh
763 WHERE h.po_header_id = p_document_id
764 AND h.style_id = pdsh.style_id
765 AND NVL(pdsh.clm_flag,'N') = 'Y';
766
767 END IF;
768 IF l_clm_document = 'Y' THEN
769 RETURN 'Y';
770 ELSE
771 RETURN 'N';
772 END IF;
773 EXCEPTION
774 WHEN No_Data_Found THEN
775 RETURN 'N';
776 WHEN OTHERS THEN
777 RETURN 'N';
778 END is_clm_document;
779
780
781
782
783 END PO_CLM_INTG_GRP;
[Home] [Help]