[Home] [Help]
PACKAGE BODY: APPS.PO_HEADERS_SV3
Source
1 PACKAGE BODY PO_HEADERS_SV3 as
2 /* $Header: POXPOH3B.pls 115.9 2003/12/12 02:27:15 sbull ship $*/
3
4 /*===========================================================================
5
6 PROCEDURE NAME: get_security_level_code()
7
8 ===========================================================================*/
9
10 procedure get_security_level_code (X_po_type in varchar2,
11 X_po_sub_type in varchar2,
12 X_security_level_code IN OUT NOCOPY varchar2) is
13
14 X_progress varchar2(3) := '';
15
16 begin
17 X_progress := '010';
18
19 if X_po_sub_type is not null then
20
21
22 select security_level_code
23 into X_security_level_code
24 from po_document_types
25 where document_type_code = X_po_type
26 and document_subtype = X_po_sub_type;
27 end if;
28
29 return;
30 exception
31 when others then
32 po_message_s.sql_error('get_security_level_code', X_progress, sqlcode);
33 raise;
34 end get_security_level_code;
35
36
37 /*==========================================================================
38
39 PROCEDURE NAME: test_get_security_level_code()
40
41 =============================================================================*/
42
43
44 procedure test_get_security_level_code is
45 X_security_level_code varchar2(80);
46 begin
47
48 get_security_level_code('PO', 'STANDARD', X_security_level_code );
49 -- dbms_output.put_line('Security Code is ' || X_security_level_code );
50
51 get_security_level_code('PA', 'BLANKET', X_security_level_code );
52 -- dbms_output.put_line('Security Code is ' || X_security_level_code );
53
54 end test_get_security_level_code;
55
56 /*==========================================================================
57
58 FUNCTION NAME: get_currency_code()
59
60 =============================================================================*/
61
62 function get_currency_code(X_po_header_id IN NUMBER)
63 return varchar2 is
64 X_currency_code varchar2(15) := '';
65 X_Progress varchar2(3) := '';
66 begin
67 X_Progress := '010';
68
69 /* Select the Currency Code the given PO is in */
70
71 select currency_code
72 into X_currency_code
73 from po_headers_all -- FPI GA
74 where po_header_id = X_po_header_id;
75
76
77 return(X_currency_code);
78
79 exception
80
81 when others then
82 /* Cannot have this because the procedure sql_error does not
83 ** have the compiler directive - restrict_references.
84 ** We will not be handling this error gracefully if it
85 ** ever occured. To avoid that, we are ignoring this
86 ** error here. Based on the return value, the calling
87 ** program should interpret it appropriately. */
88 -- po_message_s.sql_error('get_currency_code', X_progress, sqlcode);
89 return(X_currency_code);
90 end get_currency_code;
91
92
93 --=============================================================================
94 -- PROCEDURE : get_currency_info <2694908>
95 -- TYPE : Private
96 --
97 -- PRE-REQS : p_po_header_id must refer to an existing document.
98 -- MODIFIES : -
99 --
100 -- DESCRIPTION : Retrieves all currency-related info for the document
101 -- (i.e. currency_code, rate_type, rate_date, rate).
102 --
103 -- PARAMETERS : p_po_header_id - document ID
104 --
105 -- RETURNS : x_currency_code - Currency
106 -- x_rate_type - Rate Type
107 -- x_rate_date - Rate Date
108 -- x_rate - Rate
109 --
110 -- EXCEPTIONS : -
111 --=============================================================================
112 PROCEDURE get_currency_info
113 (
114 p_po_header_id IN PO_HEADERS_ALL.po_header_id%TYPE ,
115 x_currency_code OUT NOCOPY PO_HEADERS_ALL.currency_code%TYPE ,
116 x_rate_type OUT NOCOPY PO_HEADERS_ALL.rate_type%TYPE,
117 x_rate_date OUT NOCOPY PO_HEADERS_ALL.rate_date%TYPE,
118 x_rate OUT NOCOPY PO_HEADERS_ALL.rate%TYPE
119 )
120 IS
121 BEGIN
122
123 SELECT currency_code,
124 rate_type,
125 rate_date,
126 rate
127 INTO x_currency_code,
128 x_rate_type,
129 x_rate_date,
130 x_rate
131 FROM po_headers_all
132 WHERE po_header_id = p_po_header_id;
133
134 EXCEPTION
135
136 WHEN OTHERS THEN
137 x_currency_code := NULL;
138 x_rate_type := NULL;
139 x_rate_date := NULL;
140 x_rate := NULL;
141
142 END get_currency_info;
143
144
145 /*===========================================================================
146
147 PROCEDURE NAME: get_doc_num()
148
149 ===========================================================================*/
150
151 PROCEDURE get_doc_num( X_doc_num IN OUT NOCOPY VARCHAR2,
152 X_header_id IN NUMBER) IS
153
154 x_progress VARCHAR2(3) := NULL;
155
156 BEGIN
157
158 x_progress := '010';
159
160 SELECT segment1
161 INTO x_doc_num
162 FROM po_headers_all -- FPI GA
163 WHERE po_header_id = x_header_id;
164
165 EXCEPTION
166 WHEN NO_DATA_FOUND THEN
167 x_doc_num := '';
168 WHEN OTHERS THEN
169 po_message_s.sql_error('get_doc_num', x_progress, sqlcode);
170 RAISE;
171
172 END get_doc_num;
173
174 /*===========================================================================
175
176 PROCEDURE NAME: get_po_header_id()
177
178 ===========================================================================*/
179 -- Moved this package to RCVTISVB.pls because of globalization issues
180
181 /* PROCEDURE get_po_header_id
182 (X_po_header_id_record IN OUT rcv_shipment_line_sv.document_num_record_type) is
183
184 BEGIN
185
186 select max(po_header_id)
187 into x_po_header_id_record.po_header_id
188 from po_headers
189 where segment1 = X_po_header_id_record.document_num;
190
191 if (x_po_header_id_record.po_header_id is null) then
192 x_po_header_id_record.error_record.error_status := 'F';
193 x_po_header_id_record.error_record.error_message := 'RCV_ITEM_PO_ID';
194 end if;
195
196 exception
197 when others then
198 x_po_header_id_record.error_record.error_status := 'U';
199
200 END get_po_header_id; */
201
202 /*===========================================================================
203
204 FUNCTION NAME: get_po_status
205
206
207 ===========================================================================*/
208
209 FUNCTION get_po_status (X_po_header_id IN NUMBER)
210 RETURN VARCHAR2 IS
211
212
213 -- Bug 1186210: increase the length of status.
214
215 X_status VARCHAR2(4000) := '';
216 x_status_code VARCHAR2(80) := '';
217 x_cancel_status VARCHAR2(80) := '';
218 x_closed_status VARCHAR2(80) := '';
219 x_frozen_status VARCHAR2(80) := '';
220 x_hold_status VARCHAR2(80) := '';
221 x_auth_status VARCHAR2(25) := '';
222 x_cancel_flag VARCHAR2(1) := 'N';
223 x_closed_code VARCHAR2(25) := '';
224 x_frozen_flag VARCHAR2(1) := 'N';
225 x_user_hold_flag VARCHAR2(1) := 'N';
226 x_reserved_flag VARCHAR2(1) := 'N';
227 x_reserved_status VARCHAR2(80) := '';
228 x_delimiter VARCHAR2(2) := ', ';
229
230 X_progress VARCHAR2(3) := '';
231
232 X_type_lookup_code PO_HEADERS_ALL.TYPE_LOOKUP_CODE%TYPE; -- <ENCUMBRANCE FPJ>
233 l_org_id PO_HEADERS_ALL.org_id%TYPE; -- Bug 3208853
234
235 --<Encumbrance FPJ>
236 l_doc_type PO_DOCUMENT_TYPES.document_type_code%TYPE;
237
238 BEGIN
239 X_progress := '010';
240
241 -- Bug 3208853 Enhanced to work across operating units.
242
243 SELECT plc_sta.displayed_field,
244 decode(poh.cancel_flag,
245 'Y', plc_can.displayed_field, NULL),
246 decode(nvl(poh.closed_code, 'OPEN'), 'OPEN', NULL,
247 plc_clo.displayed_field),
248 decode(poh.frozen_flag,
249 'Y', plc_fro.displayed_field, NULL),
250 decode(poh.user_hold_flag,
251 'Y', plc_hld.displayed_field, NULL),
252 poh.authorization_status,
253 nvl(poh.cancel_flag, 'N'),
254 poh.closed_code,
255 nvl(poh.frozen_flag, 'N'),
256 nvl(poh.user_hold_flag, 'N'),
257 poh.type_lookup_code,
258 poh.org_id -- Bug 3208853
259 into x_status_code,
260 x_cancel_status,
261 x_closed_status,
262 x_frozen_status,
263 x_hold_status,
264 x_auth_status,
265 x_cancel_flag,
266 x_closed_code,
267 x_frozen_flag,
268 x_user_hold_flag,
269 x_type_lookup_code,
270 l_org_id -- Bug 3208853
271 from po_lookup_codes plc_sta,
272 po_lookup_codes plc_can,
273 po_lookup_codes plc_clo,
274 po_lookup_codes plc_fro,
275 po_lookup_codes plc_hld,
276 po_headers_all poh -- Bug 3208853
277 where plc_sta.lookup_code =
278 decode(poh.approved_flag,
279 'R', poh.approved_flag,
280 nvl(poh.authorization_status,'INCOMPLETE'))
281 and plc_sta.lookup_type in ('PO APPROVAL', 'DOCUMENT STATE')
282 and plc_can.lookup_code = 'CANCELLED'
283 and plc_can.lookup_type = 'DOCUMENT STATE'
284 and plc_clo.lookup_code = nvl(poh.closed_code, 'OPEN')
285 and plc_clo.lookup_type = 'DOCUMENT STATE'
286 and plc_fro.lookup_code = 'FROZEN'
287 and plc_fro.lookup_type = 'DOCUMENT STATE'
288 and plc_hld.lookup_code = 'ON HOLD'
289 and plc_hld.lookup_type = 'DOCUMENT STATE'
290 and poh.po_header_id = X_po_header_id;
291
292
293 X_progress := '015';
294
295 --<Encumbrance FPJ START>
296 IF (x_type_lookup_code = 'BLANKET') THEN
297 l_doc_type := PO_CORE_S.g_doc_type_PA;
298 ELSE
299 l_doc_type := PO_CORE_S.g_doc_type_PO;
300 END IF;
301
302 IF PO_CORE_S.is_encumbrance_on(
303 p_doc_type => l_doc_type
304 , p_org_id => l_org_id
305 )
306 THEN
307
308 PO_CORE_S.should_display_reserved(
309 p_doc_type => l_doc_type
310 , p_doc_level => PO_CORE_S.g_doc_level_header
311 , p_doc_level_id => x_po_header_id
312 , x_display_reserved_flag => x_reserved_flag
313 );
314
315 IF (x_reserved_flag = 'Y') THEN
316 PO_CORE_S.get_reserved_lookup(x_displayed_field => x_reserved_status);
317 END IF;
318
319 END IF;
320 --<Encumbrance FPJ END>
321
322 X_progress := '030';
323
324 SELECT x_status_code||
325 decode(x_closed_code, 'OPEN', '', '', '',
326 x_delimiter||x_closed_status)||
327 decode(x_cancel_flag, 'N', '', '', '',
328 x_delimiter||x_cancel_status)||
329 decode(x_frozen_flag, 'N', '', '', '',
330 x_delimiter||x_frozen_status)||
331 decode(x_user_hold_flag, 'N', '', '', '',
332 x_delimiter||x_hold_status)||
333 decode(x_reserved_flag, 'N', '', '', '',
334 x_delimiter||x_reserved_status)
335 INTO x_status
336 FROM dual;
337
338 RETURN (X_status);
339
340 EXCEPTION
341 WHEN OTHERS THEN
342 RETURN (NULL);
343 RAISE;
344
345 END get_po_status;
346
347
348 END PO_HEADERS_SV3;