1 PACKAGE hr_po_info AUTHID CURRENT_USER AS
2 /* $Header: hrpoinfo.pkh 115.5 2004/04/07 10:50:17 sbuche noship $ */
3 /*
4 +==========================================================================+
5 | Copyright (c) 2003 Oracle Corporation |
6 | Redwood Shores, California, USA |
7 | All rights reserved. |
8 +==========================================================================+
9 Name
10 Container for procedures / functions that return HR information to
11 PO.
12 Purpose
13 This package, at least initially, is base 11i interoperable
14 so that other non-HRMS application teams can include this in
15 any of their patches. Care should be taken when maintaining.
16
17 Apps teams call this as a black box, which can include (but is
18 not limited to) calling functions from within SQL. This means
19 that parameter ordering should not be adjusted and new parameters
20 should be optional or overloaded.
21
22 These procedures will initially be used by PO (Oracle Purchasing)
23 and iExpenses.
24
25 Change History
26 -------+--------+-----------+-------+--------------------------------------|
27 dcasemor 10-SEP-2003 115.0 Created for bug 3120074.
28 dcasemor 15-SEP-2003 115.1 Added wrapper functions to return
29 varchar2s instead of booleans.
30 Added the full_cwk_enabled pair of
31 functions. Added procedures to return
32 PL/SQL tables for PO bulk APIs.
33 dcasemor 16-OCT-2003 115.2 Added more interoperable select
34 procedures:
35 - get_vendor_for_primary_asg
36 - get_vendor_for_asg
37 - get_po_for_primary_asg
38 - get_po_for_asg
39 sramasam 22-OCT-2003 115.3 Added procedure to return url
40 to launch Self Service CWK Placement
41 sbuche 26-mar-2004 115.4 Bug 3391399:
42 Added function asg_vendor_id_exist
43 to check the existance of vendor_id
44 column in HR assignments tables.
45 sbuche 02-APR-2002 115.5 Change requested by OTA:
46 Added parameter p_effective_date to
47 the procedure get_person_for_po_line.
48
49 Commented following functions and
50 procedure as they will not work
51 when more than one person or
52 assignment exists for a vendor site:
53
54 - get_asg_id_for_vendor_site
55 - get_person_id_for_vendor_site
56 - get_person_for_vendor_site
57
58 ---------------------------------------------------------------------------|
59 */
60
61 --
62 -- Global package variable declarations.
63 --
64 g_TRUE CONSTANT VARCHAR2(4) := 'TRUE';
65 g_FALSE CONSTANT VARCHAR2(5) := 'FALSE';
66 --
67 -- Ideally g_debug would be better as a package private but it needs
68 -- to be public so that it can be set in an NDS block.
69 --
70 g_debug BOOLEAN := TRUE;
71
72 --
73 -- Public global type declarations.
74 --
75 TYPE g_table_numbers_t IS TABLE OF NUMBER INDEX BY binary_integer;
76
77 --
78 ---------------------------------------------------------------------------|
79 ------------------------< DEBUG_ENABLED >----------------------------------|
80 ---------------------------------------------------------------------------|
81 --
82 -- This function determines whether HR debugging is enabled.
83 -- This is a wrapper to hr_utility.debug_enabled. In this wrapper, if
84 -- hr_utility.debug_enabled does not exist the function assumes TRUE.
85 -- This is compatible with base 11i.
86 --
87 FUNCTION debug_enabled RETURN BOOLEAN;
88 --
89 ---------------------------------------------------------------------------|
90 ------------------------< FULL_CWK_ENABLED >-------------------------------|
91 ---------------------------------------------------------------------------|
92 --
93 -- This function determines whether the contingent worker
94 -- cross-applications functionality is enabled. To do this, it checks
95 -- a PO profile option.
96 --
97 FUNCTION full_cwk_enabled RETURN BOOLEAN;
98 --
99 ---------------------------------------------------------------------------|
100 ------------------------< FULL_CWK_ENABLED_CHAR >--------------------------|
101 ---------------------------------------------------------------------------|
102 --
103 -- This function determines whether the contingent worker
104 -- cross-applications functionality is enabled. To do this, it checks
105 -- a PO profile option. The function returns 'TRUE' or 'FALSE' as a
106 -- VARCHAR2 instead of a boolean type so that the function can be used in
107 -- SQL.
108 --
109 FUNCTION full_cwk_enabled_char RETURN VARCHAR2;
110 --
111 ---------------------------------------------------------------------------|
112 ------------------------< ASG_PO_COLS_EXIST >------------------------------|
113 ---------------------------------------------------------------------------|
114 --
115 -- This function determines whether the PO columns exist on the HR
116 -- assignments table or not.
117 --
118 FUNCTION asg_po_cols_exist RETURN BOOLEAN;
119 --
120 ---------------------------------------------------------------------------|
121 ------------------------< ASG_VENDOR_ID_EXIST >----------------------------|
122 ---------------------------------------------------------------------------|
123 --
124 -- This function determines whether the VENDOR_ID column exist on the HR
125 -- assignments table or not.
126 --
127 FUNCTION asg_vendor_id_exist RETURN BOOLEAN;
128 --
129 ---------------------------------------------------------------------------|
130 ------------------------< GET_PERSON_FOR_PO_LINE >-------------------------|
131 ---------------------------------------------------------------------------|
132 --
133 -- This procedure returns the person and assignment IDs given the ID of a
134 -- PO Line. If no match is found, the IDs returned are NULL.
135 --
136 PROCEDURE get_person_for_po_line
137 (p_po_line_id IN NUMBER
138 ,p_person_id OUT NOCOPY NUMBER
139 ,p_assignment_id OUT NOCOPY NUMBER
140 ,p_effective_date IN DATE DEFAULT NULL);
141 --
142 ---------------------------------------------------------------------------|
143 ------------------------< GET_PERSON_ID_FOR_PO_LINE >----------------------|
144 ---------------------------------------------------------------------------|
145 --
146 -- This function returns the person ID given a specific PO Line.
147 -- If no match is found, it returns NULL.
148 --
149 FUNCTION get_person_id_for_po_line
150 (p_po_line_id IN NUMBER) RETURN NUMBER;
151 --
152 ---------------------------------------------------------------------------|
153 ------------------------< GET_ASG_ID_FOR_PO_LINE >-------------------------|
154 ---------------------------------------------------------------------------|
155 --
156 -- This function returns the assignment ID given a specific PO Line.
157 -- If no match is found, it returns NULL.
158 --
159 FUNCTION get_asg_id_for_po_line
160 (p_po_line_id IN NUMBER) RETURN NUMBER;
161 --
162 ---------------------------------------------------------------------------|
163 ------------------------< ASG_EXIST_FOR_PO >-------------------------------|
164 ---------------------------------------------------------------------------|
165 --
166 -- This function determines whether any assignments are assigned to a
167 -- given PO Header.
168 --
169 FUNCTION asg_exist_for_po
170 (p_po_header_id IN NUMBER) RETURN BOOLEAN;
171 --
172 ---------------------------------------------------------------------------|
173 ------------------------< ASG_EXIST_FOR_PO_LINE >--------------------------|
174 ---------------------------------------------------------------------------|
175 --
176 -- This function determines whether any assignments are assigned to a
177 -- specific PO Line.
178 --
179 FUNCTION asg_exist_for_po_line
180 (p_po_line_id IN NUMBER) RETURN BOOLEAN;
181 --
182 ---------------------------------------------------------------------------|
183 ------------------------< ASG_EXIST_FOR_PO_CHAR >--------------------------|
184 ---------------------------------------------------------------------------|
185 --
186 -- This function determines whether any assignments are assigned to a
187 -- given PO Header. It returns 'TRUE' or 'FALSE' as a VARCHAR2 instead
188 -- of a boolean type so that the function can be used in SQL.
189 --
190 FUNCTION asg_exist_for_po_char
191 (p_po_header_id IN NUMBER) RETURN VARCHAR2;
192 --
193 ---------------------------------------------------------------------------|
194 ------------------------< ASG_EXIST_FOR_PO_LINE_CHAR >---------------------|
195 ---------------------------------------------------------------------------|
196 --
197 -- This function determines whether any assignments are assigned to a
198 -- specific PO Line. It returns 'TRUE' or 'FALSE' as a VARCHAR2 instead
199 -- of a boolean type so that the function can be used in SQL.
200 --
201 FUNCTION asg_exist_for_po_line_char
202 (p_po_line_id IN NUMBER) RETURN VARCHAR2;
203 --
204 ---------------------------------------------------------------------------|
205 --
206 ---------------------------------------------------------------------------|
207 ------------------------< ASGS_EXIST_FOR_POS >-----------------------------|
208 ---------------------------------------------------------------------------|
209 --
210 -- This procedure takes in a PL/SQL table of numbers. The numbers are
211 -- PO IDs and these are used to check whether any assignments are
212 -- assigned to each PO.
213 -- The same PL/SQL table type is returned, only the IDs returned are
214 -- restricted to POs that have assignments attached.
215 --
216 PROCEDURE asgs_exist_for_pos
217 (p_po_in_tbl IN g_table_numbers_t
218 ,p_po_out_tbl OUT NOCOPY g_table_numbers_t);
219 --
220 ---------------------------------------------------------------------------|
221 ------------------------< ASGS_EXIST_FOR_PO_LINES >------------------------|
222 ---------------------------------------------------------------------------|
223 --
224 -- This procedure takes in a PL/SQL table of numbers. The numbers are
225 -- PO Line IDs and these are used to check whether any assignments are
226 -- assigned to each PO Line.
227 -- The same PL/SQL table type is returned, only the IDs returned are
228 -- restricted to lines that have assignments attached.
229 --
230 PROCEDURE asgs_exist_for_po_lines
231 (p_po_lines_in_tbl IN g_table_numbers_t
232 ,p_po_lines_out_tbl OUT NOCOPY g_table_numbers_t);
233 --
234 ---------------------------------------------------------------------------|
235 ------------------------< GET_PERSON_FOR_VENDOR_SITE >---------------------|
236 ---------------------------------------------------------------------------|
237 /*
238 ** For a vendor site, more than one Person could exist and in
239 ** that case this procedure will not work.
240 **
241 --
242 -- This procedure returns the person and assignment IDs given the ID of a
243 -- vendor site. If no match is found, the IDs returned are NULL.
244 --
245 PROCEDURE get_person_for_vendor_site
246 (p_vendor_site_id IN NUMBER
247 ,p_person_id OUT NOCOPY NUMBER
248 ,p_assignment_id OUT NOCOPY NUMBER
249 ,p_effective_date IN DATE DEFAULT NULL);
250 */
251 --
252 ---------------------------------------------------------------------------|
253 ------------------------< GET_PERSON_ID_FOR_VENDOR_SITE >------------------|
254 ---------------------------------------------------------------------------|
255 /*
256 ** For a vendor site, more than one Person could exist and in
257 ** that case this function will not work.
258 **
259 --
260 -- This function returns the person ID given a specific Vendor Site.
261 -- If no match is found, it returns NULL.
262 --
263 FUNCTION get_person_id_for_vendor_site
264 (p_vendor_site_id IN NUMBER) RETURN NUMBER;
265 */
266 --
267 ---------------------------------------------------------------------------|
268 ------------------------< GET_ASG_ID_FOR_VENDOR_SITE >---------------------|
269 ---------------------------------------------------------------------------|
270 /*
271 ** For a vendor site, more than one Assignment could exist and in
272 ** that case this function will not work.
273 **
274 --
275 -- This function returns the assignment ID given a specific Vendor Site.
276 -- If no match is found, it returns NULL.
277 --
278 FUNCTION get_asg_id_for_vendor_site
279 (p_vendor_site_id IN NUMBER) RETURN NUMBER;
280 */
281 --
282 ---------------------------------------------------------------------------|
283 ------------------------< ASG_EXIST_FOR_VENDOR >---------------------------|
284 ---------------------------------------------------------------------------|
285 --
286 -- This function determines whether any assignments belong o a given
287 -- Vendor.
288 --
289 FUNCTION asg_exist_for_vendor
290 (p_vendor_id IN NUMBER) RETURN BOOLEAN;
291 --
292 ---------------------------------------------------------------------------|
293 ------------------------< ASG_EXIST_FOR_VENDOR_SITE >----------------------|
294 ---------------------------------------------------------------------------|
295 --
296 -- This function determines whether any assignments belong to a given
297 -- Vendor Site.
298 --
299 FUNCTION asg_exist_for_vendor_site
300 (p_vendor_site_id IN NUMBER) RETURN BOOLEAN;
301 --
302 ---------------------------------------------------------------------------|
303 ------------------------< ASG_EXIST_FOR_VENDOR_CHAR >----------------------|
304 ---------------------------------------------------------------------------|
305 --
309 --
306 -- This function determines whether any assignments belong to a given
307 -- Vendor. The function returns 'TRUE' or 'FALSE' as a VARCHAR2 instead
308 -- of a boolean type so that it can be used in SQL.
310 FUNCTION asg_exist_for_vendor_char
311 (p_vendor_id IN NUMBER) RETURN VARCHAR2;
312 --
313 ---------------------------------------------------------------------------|
314 ------------------------< ASG_EXIST_FOR_VENDOR_SITE_CHAR >-----------------|
315 ---------------------------------------------------------------------------|
316 --
317 -- This function determines whether any assignments belong to a given
318 -- Vendor Site. The function returns 'TRUE' or 'FALSE' as a VARCHAR2
319 -- instead of a boolean type so that it can be used in SQL.
320 --
321 FUNCTION asg_exist_for_vendor_site_char
322 (p_vendor_site_id IN NUMBER) RETURN VARCHAR2;
323 --
324 ---------------------------------------------------------------------------|
325 ------------------------< ASGS_EXIST_FOR_VENDORS >-------------------------|
326 ---------------------------------------------------------------------------|
327 --
328 -- This procedure takes in a PL/SQL table of numbers. The numbers are
329 -- Vendor IDs and these are used to check whether any assignments are
330 -- assigned to each vendor.
331 -- The same PL/SQL table type is returned, only the IDs returned are
332 -- restricted to vendors that have assignments.
333 --
334 PROCEDURE asgs_exist_for_vendors
335 (p_vendors_in_tbl IN g_table_numbers_t
336 ,p_vendors_out_tbl OUT NOCOPY g_table_numbers_t);
337 --
338 ---------------------------------------------------------------------------|
339 ------------------------< ASGS_EXIST_FOR_VENDOR_SITES >--------------------|
340 ---------------------------------------------------------------------------|
341 --
342 -- This procedure takes in a PL/SQL table of numbers. The numbers are
343 -- Vendor Site IDs and these are used to check whether any assignments are
344 -- assigned to each vendor site.
345 -- The same PL/SQL table type is returned, only the IDs returned are
346 -- restricted to vendor sites that have assignments.
347 --
348 PROCEDURE asgs_exist_for_vendor_sites
349 (p_vendor_sites_in_tbl IN g_table_numbers_t
350 ,p_vendor_sites_out_tbl OUT NOCOPY g_table_numbers_t);
351 --
352 ---------------------------------------------------------------------------|
353 ------------------------< GET_VENDOR_FOR_PRIMARY_ASG >---------------------|
354 ---------------------------------------------------------------------------|
355 --
356 -- Given a person, this procedure returns the Supplier and Supplier Site
357 -- for the primary assignment. If the assignment does not have the
358 -- Supplier set, then both the Supplier and Supplier Site will return null
359 -- (because the site cannot be set without the supplier). If the site
360 -- is not set but the supplier is, then only the site will return null.
361 --
362 -- If the person does not exist, or there is no primary assignment as of
363 -- the effective date, then both the Supplier and Supplier Site return
364 -- null; the procedure does not raise an error.
365 --
366 PROCEDURE get_vendor_for_primary_asg
367 (p_person_id IN NUMBER
368 ,p_effective_date IN DATE
369 ,p_vendor_id OUT NOCOPY NUMBER
370 ,p_vendor_site_id OUT NOCOPY NUMBER);
371 --
372 ---------------------------------------------------------------------------|
373 ------------------------< GET_VENDOR_FOR_ASG >-----------------------------|
374 ---------------------------------------------------------------------------|
375 --
376 -- Given an assignment, this procedure returns the Supplier and Supplier
377 -- Site for that assignment. If the assignment does not have the
378 -- Supplier set, then both the Supplier and Supplier Site will return null
379 -- (because the site cannot be set without the supplier). If the site
380 -- is not set but the supplier is, then only the site will return null.
381 --
382 -- If the assignment does not exist, or the assignment is not effective
383 -- as of the effective date, then both the Supplier and Supplier Site
384 -- return null; the procedure does not raise an error.
385 --
386 PROCEDURE get_vendor_for_asg
387 (p_assignment_id IN NUMBER
388 ,p_effective_date IN DATE
389 ,p_vendor_id OUT NOCOPY NUMBER
390 ,p_vendor_site_id OUT NOCOPY NUMBER);
391 --
392 ---------------------------------------------------------------------------|
393 ------------------------< GET_PO_FOR_PRIMARY_ASG >-------------------------|
394 ---------------------------------------------------------------------------|
395 --
396 -- Given a person, this procedure returns the PO Header and Line
397 -- for the primary assignment. If the assignment does not have the
398 -- PO set, then both the Header and Line will return null (because
399 -- the line cannot be set without a header). If the line is not set
400 -- but the header is, then only the line will return null.
401 --
402 -- If the person does not exist, or there is no primary assignment as of
403 -- the effective date, then both the PO Header and Line will return
404 -- null; the procedure does not raise an error.
405 --
406 PROCEDURE get_po_for_primary_asg
407 (p_person_id IN NUMBER
408 ,p_effective_date IN DATE
409 ,p_po_header_id OUT NOCOPY NUMBER
410 ,p_po_line_id OUT NOCOPY NUMBER);
411 --
412 ---------------------------------------------------------------------------|
413 ------------------------< GET_PO_FOR_ASG >---------------------------------|
414 ---------------------------------------------------------------------------|
415 --
416 -- Given an assignment, this procedure returns the PO Header and Line
417 -- for the assignment. If the assignment does not have the PO set, then
418 -- both the Header and Line will return null (because the line cannot be
419 -- set without a header). If the line is not set but the header is, then
420 -- only the line will return null.
421 --
422 -- If the assignment does not exist, or the assignment is not effective
423 -- as of the effective date, then both the PO Header and Line will return
424 -- null; the procedure does not raise an error.
425 --
426 PROCEDURE get_po_for_asg
427 (p_assignment_id IN NUMBER
428 ,p_effective_date IN DATE
429 ,p_po_header_id OUT NOCOPY NUMBER
430 ,p_po_line_id OUT NOCOPY NUMBER);
431
432 ---------------------------------------------------------------------------|
433 ------------------------< GET_URL_PLACE_CWK >------------------------------|
434 ---------------------------------------------------------------------------|
435 --
436 -- Given a po_line_id this procedure will return the url destination which
437 -- will be rendered in PO notification.
438 -- On launching this url the user will be taken through the CWK Placement
439 -- flow of pages.
440 --
441 PROCEDURE get_url_place_cwk
442 (p_po_line_id IN NUMBER
443 ,p_destination OUT NOCOPY VARCHAR2);
444
445 END hr_po_info;
[Home] [Help]