1 PACKAGE BODY PO_LINE_LOCATIONS_AP_PKG AS
2 /* $Header: POLNLOCB.pls 120.0.12010000.1 2008/09/18 12:21:11 appldev noship $ */
3
4 -----------------------------------------------------------------------
5 -- Function get_last_receipt returns the last receipt date for the
6 -- shipment line.
7 --
8 FUNCTION get_last_receipt(l_line_location_id IN NUMBER)
9 RETURN DATE
10 IS
11 last_receipt_date DATE;
12
13 BEGIN
14
15 SELECT MAX(transaction_date)
16 INTO last_receipt_date
17 FROM rcv_transactions
18 WHERE po_line_location_id = l_line_location_id
19 AND transaction_type = 'RECEIVE';
20
21 RETURN(last_receipt_date);
22
23 END get_last_receipt;
24
25
26 -----------------------------------------------------------------------
27 -- Function get_requestors returns a concatenated list of requestors
28 -- from the distributions for the shipment line. If the list is longer
29 -- than 2000 characters, the function will insert trailing ellipses ...
30 --
31 FUNCTION get_requestors(l_line_location_id IN NUMBER)
32 RETURN VARCHAR2
33 IS
34 requestor_list VARCHAR2(2000) := NULL;
35 requestor VARCHAR2(240);
36
37 ---------------------------------------------------------------------
38 -- Declare cursor to fetch the requestor names
39 --
40 CURSOR requestor_cursor IS
41 SELECT HE.full_name
42 FROM hr_employees HE,
43 po_distributions PD
44 WHERE PD.line_location_id = l_line_location_id
45 AND PD.deliver_to_person_id = HE.employee_id;
46
47 BEGIN
48
49 OPEN requestor_cursor;
50
51 LOOP
52 FETCH requestor_cursor INTO requestor;
53
54 -- bug 643248
55 -- need to account for '; ' AND ' ...' when calculating
56 -- the limit of characters allowed in the requestor list
57 EXIT WHEN requestor_cursor%NOTFOUND or
58 (NVL(LENGTH(requestor_list),0) +
59 NVL(LENGTH(requestor),0) + 2 + 4 > 2000);
60
61 IF (requestor_list IS NOT NULL) THEN
62 requestor_list := requestor_list || '; ';
63 END IF;
64
65 requestor_list := requestor_list || requestor;
66 END LOOP;
67
68 -- bug 643248
69 -- attach trailing elipses outside loop, so that it is only performed
70 -- ONCE
71 IF (NVL(LENGTH(requestor_list),0) +
72 NVL(LENGTH(requestor),0) + 2 + 4 > 2000) then
73 requestor_list := requestor_list || ' ...';
74 END IF;
75
76
77 CLOSE requestor_cursor;
78
79 RETURN(requestor_list);
80
81 END get_requestors;
82
83
84 -----------------------------------------------------------------------
85 -- Function get_num_distributions returns the number of distributions
86 -- lines for the shipment line.
87 --
88 FUNCTION get_num_distributions(l_line_location_id IN NUMBER)
89 RETURN NUMBER
90 IS
91 num_distributions NUMBER := 0;
92 BEGIN
93
94 SELECT count(*)
95 INTO num_distributions
96 FROM po_distributions PD
97 WHERE PD.line_location_id = l_line_location_id;
98
99 RETURN(num_distributions);
100
101 END get_num_distributions;
102
103
104 END PO_LINE_LOCATIONS_AP_PKG;
[Home] [Help]