[Home] [Help]
PACKAGE BODY: APPS.PO_RELEASES_SV2
Source
1 PACKAGE BODY PO_RELEASES_SV2 as
2 /* $Header: POXPOR2B.pls 115.10 2004/03/24 15:40:28 manram ship $ */
3
4
5 /*===========================================================================
6
7 PROCEDURE NAME: get_po_header_details
8
9 ===========================================================================*/
10 PROCEDURE get_po_header_details(
11 X_po_header_id IN NUMBER,
12 X_type_lookup_code IN OUT NOCOPY VARCHAR2,
13 X_revision_num IN OUT NOCOPY NUMBER,
14 X_currency_code IN OUT NOCOPY VARCHAR2,
15 X_supplier_id IN OUT NOCOPY NUMBER,
16 X_supplier_name IN OUT NOCOPY VARCHAR2,
17 X_supplier_site_id IN OUT NOCOPY NUMBER,
18 X_supplier_site_name IN OUT NOCOPY VARCHAR2,
19 X_pay_on_code IN OUT NOCOPY VARCHAR2,
20 X_pay_on_dsp IN OUT NOCOPY VARCHAR2,
21 X_ship_to_location_id IN OUT NOCOPY NUMBER,
22 X_ship_to_location_code IN OUT NOCOPY VARCHAR2,
23 X_organization_id IN OUT NOCOPY NUMBER,
24 X_organization_code IN OUT NOCOPY VARCHAR2,
25 x_shipping_control IN OUT NOCOPY VARCHAR2, -- <INBOUND LOGISITCS FPJ>
26 x_supply_agreement_flag IN OUT NOCOPY VARCHAR2, --Bug#3514141
27 x_confirming_order_flag IN OUT NOCOPY VARCHAR2 --Bug#3514141
28 ) IS
29
30 X_progress varchar2(3) := '';
31
32 /** <UTF8 FPI> **/
33 /** tpoon 9/27/2002 **/
34 /** Changed X_organization_name to use %TYPE **/
35 -- X_organization_name varchar2(60) := '';
36 X_organization_name hr_all_organization_units.name%TYPE := '';
37
38 X_set_of_books_id number := '';
39
40 BEGIN
41
42 po_headers_sv2.get_po_details(X_po_header_id,
43 X_type_lookup_code,
44 X_revision_num,
45 X_currency_code,
46 X_supplier_id,
47 X_supplier_site_id,
48 X_ship_to_location_id);
49
50 -- Get the location code.
51 po_locations_s.get_loc_attributes(X_ship_to_location_id,
52 X_ship_to_location_code,
53 X_organization_id);
54
55 SELECT set_of_books_id
56 INTO X_set_of_books_id
57 FROM financials_system_parameters;
58
59 -- Get the organization code.
60 po_orgs_sv.get_org_info(X_organization_id,
61 X_set_of_books_id,
62 X_organization_code,
63 X_organization_name);
64
65 -- Get the vendor name
66 po_vendors_sv.get_vendor_name(x_supplier_id,x_supplier_name);
67
68 -- Get the vendor site
69 po_vendor_sites_sv.get_vendor_site_name(x_supplier_site_id,
70 x_supplier_site_name);
71
72
73 -- Get pay on code
74 --Bug#3514141: Modified the query to get the supply agreement flag and confirming order flag
75 select
76 ph.pay_on_code,
77 lk.displayed_field,
78 PH.shipping_control, -- <INBOUND LOGISTICS FPJ>
79 PH.SUPPLY_AGREEMENT_FLAG, --Bug#3514141
80 PH.CONFIRMING_ORDER_FLAG --Bug#3514141
81 into
82 x_pay_on_code,
83 x_pay_on_dsp,
84 x_shipping_control, -- <INBOUND LOGISTICS FPJ>
85 x_supply_agreement_flag, --Bug#3514141
86 x_confirming_order_flag --Bug#3514141
87 from
88 po_headers ph,
89 po_lookup_codes lk
90 where
91 ph.po_header_id = X_po_header_id AND
92 lk.lookup_type (+) = 'PAY ON CODE' AND
93 lk.lookup_code (+) = ph.pay_on_code;
94
95 EXCEPTION
96 WHEN OTHERS THEN
97 -- dbms_output.put_line('In exception');
98 po_message_s.sql_error('get_po_header_details', X_progress, sqlcode);
99 raise;
100 END get_po_header_details;
101
102 /*===========================================================================
103
104 FUNCTION NAME: get_rel_total
105
106 ===========================================================================*/
107 FUNCTION get_rel_total
108 (X_release_id number) return number is
109 X_rel_total number;
110 BEGIN
111
112 SELECT nvl(SUM((quantity-quantity_cancelled) * price_override), 0)
113 into X_rel_total
114 FROM po_line_locations
115 WHERE po_release_id = X_release_id;
116
117 RETURN (X_rel_total);
118
119 EXCEPTION
120 WHEN OTHERS then
121 x_rel_total := 0;
122 END get_rel_total;
123
124
125
126 /*===========================================================================
127
128 FUNCTION NAME: get_release_status
129
130
131 ===========================================================================*/
132 FUNCTION get_release_status
133 (X_po_release_id NUMBER) return VARCHAR2 IS
134
135 -- Bug 1186210: increase the length of status.
136
137 X_status VARCHAR2(4000);
138 x_status_code VARCHAR2(80) := '';
139 x_cancel_status VARCHAR2(80) := '';
140 x_closed_status VARCHAR2(80) := '';
141 x_frozen_status VARCHAR2(80) := '';
142 x_hold_status VARCHAR2(80) := '';
143 x_auth_status VARCHAR2(25) := '';
144 x_cancel_flag VARCHAR2(1) := 'N';
145 x_closed_code VARCHAR2(25) := '';
146 x_frozen_flag VARCHAR2(1) := 'N';
147 x_user_hold_flag VARCHAR2(1) := 'N';
148 x_reserved_flag VARCHAR2(1) := 'N';
149 x_reserved_status VARCHAR2(80) := '';
150 x_delimiter VARCHAR2(2) := ', ';
151
152 BEGIN
153
154
155
156 SELECT plc_sta.displayed_field,
157 decode(por.cancel_flag,
158 'Y', plc_can.displayed_field, NULL),
159 decode(nvl(por.closed_code, 'OPEN'), 'OPEN', NULL,
160 plc_clo.displayed_field),
161 decode(por.frozen_flag,
162 'Y', plc_fro.displayed_field, NULL),
163 decode(por.hold_flag,
164 'Y', plc_hld.displayed_field, NULL),
165 por.authorization_status,
166 nvl(por.cancel_flag, 'N'),
167 nvl(por.closed_code, 'OPEN'),
168 nvl(por.frozen_flag, 'N'),
169 nvl(por.hold_flag, 'N')
170 into x_status_code,
171 x_cancel_status,
172 x_closed_status,
173 x_frozen_status,
174 x_hold_status,
175 x_auth_status,
176 x_cancel_flag,
177 x_closed_code,
178 x_frozen_flag,
179 x_user_hold_flag
180 from po_releases por,
181 po_lookup_codes plc_sta,
182 po_lookup_codes plc_can,
183 po_lookup_codes plc_clo,
184 po_lookup_codes plc_fro,
185 po_lookup_codes plc_hld
186 where plc_sta.lookup_code =
187 decode(por.approved_flag,
188 'R', por.approved_flag,
189 nvl(por.authorization_status,'INCOMPLETE'))
190 and plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
191 and plc_can.lookup_code = 'CANCELLED'
192 and plc_can.lookup_type = 'DOCUMENT STATE'
193 and plc_clo.lookup_code = nvl(por.closed_code, 'OPEN')
194 and plc_clo.lookup_type = 'DOCUMENT STATE'
195 and plc_fro.lookup_code = 'FROZEN'
196 and plc_fro.lookup_type = 'DOCUMENT STATE'
197 and plc_hld.lookup_code = 'ON HOLD'
198 and plc_hld.lookup_type = 'DOCUMENT STATE'
199 and por.po_release_id = X_po_release_id;
200
201
202 --<Encumbrance FPJ START>
203 PO_CORE_S.should_display_reserved(
204 p_doc_type => PO_CORE_S.g_doc_type_RELEASE
205 , p_doc_level => PO_CORE_S.g_doc_level_HEADER
206 , p_doc_level_id => x_po_release_id
207 , x_display_reserved_flag => x_reserved_flag
208 );
209
210 IF (x_reserved_flag = 'Y') THEN
211 PO_CORE_S.get_reserved_lookup(x_displayed_field => x_reserved_status);
212 END IF;
213 --<Encumbrance FPJ END>
214
215
216
217 SELECT x_status_code||
218 decode(x_closed_code, 'OPEN', '',
219 x_delimiter||x_closed_status)||
220 decode(x_cancel_flag, 'N', '', '', '',
221 x_delimiter||x_cancel_status)||
222 decode(x_frozen_flag, 'N', '', '', '',
223 x_delimiter||x_frozen_status)||
224 decode(x_user_hold_flag, 'N', '', '', '',
225 x_delimiter||x_hold_status)||
226 decode(x_reserved_flag, 'N', '', '', '',
227 x_delimiter||x_reserved_status)
228 INTO X_status
229 FROM dual;
230
231 RETURN(X_status);
232
233 EXCEPTION
234 WHEN OTHERS THEN
235 X_status := '';
236 RETURN(X_status);
237
238 END get_release_status;
239
240 END PO_RELEASES_SV2;