[Home] [Help]
PACKAGE BODY: APPS.PO_TOTALS_PO_SV
Source
1 PACKAGE BODY PO_TOTALS_PO_SV as
2 /* $Header: ICXPOTOB.pls 115.1 2001/05/02 21:08:02 pkm ship $ */
3
4 FUNCTION get_po_total
5 (X_header_id number) return number is
6 X_po_total number;
7
8
9 x_min_unit NUMBER;
10 x_precision NUMBER;
11
12 BEGIN
13
14 SELECT nvl(fc.minimum_accountable_unit,1),
15 fc.precision
16 INTO x_min_unit,
17 x_precision
18 FROM fnd_currencies fc,
19 po_headers ph
20 WHERE ph.po_header_id = X_header_id
21 AND fc.currency_code = ph.currency_code;
22
23
24
25 SELECT round(((nvl(SUM((quantity - quantity_cancelled) * price_override),0)
26 * x_min_unit) / x_min_unit),
27 x_precision)
28 into X_po_total
29 FROM po_line_locations
30 WHERE po_header_id = X_header_id
31 AND shipment_type IN ('STANDARD','PLANNED','BLANKET');
32
33
34 RETURN (X_po_total);
35
36 EXCEPTION
37 WHEN OTHERS then
38 x_po_total := 0;
39 return(x_po_total);
40 END get_po_total;
41
42
43 FUNCTION get_po_archive_total
44 (X_header_id number,
45 X_revision_num number) return number is
46 X_po_total number;
47
48
49 x_min_unit NUMBER;
50 x_precision NUMBER;
51
52 BEGIN
53
54 SELECT nvl(fc.minimum_accountable_unit,1),
55 fc.precision
56 INTO x_min_unit,
57 x_precision
58 FROM fnd_currencies fc,
59 po_headers_archive pha
60 WHERE pha.po_header_id = X_header_id
61 AND pha.revision_num = X_revision_num
62 AND fc.currency_code = pha.currency_code;
63
64
65
66 SELECT round(((nvl(SUM((quantity - quantity_cancelled) * price_override),0)
67 * x_min_unit) / x_min_unit),
68 x_precision)
69 into X_po_total
70 FROM po_line_locations_archive plla1
71 WHERE po_header_id = X_header_id
72 AND shipment_type IN ('STANDARD','PLANNED','BLANKET')
73 AND revision_num = (
74 SELECT
75 max( plla2.revision_num )
76 FROM
77 po_line_locations_archive plla2
78 WHERE
79 plla2.revision_num <= X_revision_num
80 AND plla2.line_location_id = plla1.line_location_id )
81 ;
82
83
84 RETURN (X_po_total);
85 EXCEPTION
86 WHEN OTHERS then
87 x_po_total := 0;
88 return (X_po_total);
89
90 END get_po_archive_total;
91
92 FUNCTION get_release_archive_total
93 (X_release_id number,
94 X_revision_num number) return number is
95 X_po_total number;
96
97
98 x_min_unit NUMBER;
99 x_precision NUMBER;
100
101 BEGIN
102
103 -- Bug# 1666579 Added rownum < 2 to avoid too many rows error.
104 SELECT nvl(fc.minimum_accountable_unit,1),
105 fc.precision
106 INTO x_min_unit,
107 x_precision
108 FROM fnd_currencies fc,
109 po_headers_archive pha,
110 po_releases_archive pra
111 WHERE pha.po_header_id = pra.po_header_id
112 AND pra.po_release_id = X_release_id
113 AND pha.revision_num = X_revision_num
114 AND fc.currency_code = pha.currency_code
115 AND rownum < 2 ;
116
117
118
119 SELECT round(((nvl(SUM((quantity - quantity_cancelled) * price_override),0)
120 * x_min_unit) / x_min_unit),
121 x_precision)
122 into X_po_total
123 FROM po_line_locations_archive plla1
124 WHERE po_release_id = X_release_id
125 AND shipment_type IN ('STANDARD','PLANNED','BLANKET')
126 AND revision_num = (
127 SELECT
128 max( plla2.revision_num )
129 FROM
130 po_line_locations_archive plla2
131 WHERE
132 plla2.revision_num <= X_revision_num
133 AND plla2.line_location_id = plla1.line_location_id )
134 ;
135
136
137 RETURN (X_po_total);
138
139 EXCEPTION
140 WHEN OTHERS then
141 x_po_total := 0;
142 return (X_po_total);
143
144 END GET_RELEASE_ARCHIVE_TOTAL;
145
146
147 FUNCTION get_release_total
148 (X_release_id number) return number is
149 X_release_total number;
150
151 x_min_unit NUMBER;
152 x_precision NUMBER;
153
154 BEGIN
155
156 SELECT nvl(fc.minimum_accountable_unit,1),
157 fc.precision
158 INTO x_min_unit,
159 x_precision
160 FROM fnd_currencies fc,
161 po_headers ph,
162 po_releases pr
163 WHERE ph.po_header_id = pr.po_header_id
164 AND pr.po_release_id = X_release_id
165 AND fc.currency_code = ph.currency_code;
166
167
168 SELECT round(((nvl(SUM((quantity - quantity_cancelled) * price_override),0)
169 * x_min_unit) / x_min_unit),
170 x_precision)
171 into X_release_total
172 FROM po_line_locations
173 WHERE po_release_id = X_release_id;
174
175
176 RETURN (X_release_total);
177
178 EXCEPTION
179 WHEN OTHERS then
180 x_release_total := 0;
181 return(x_release_total);
182 END get_release_total;
183
184
185
186 END PO_TOTALS_PO_SV;