[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
dcasemor 16-OCT-2003 115.2 Added more interoperable select
procedures:
- get_vendor_for_primary_asg
- get_vendor_for_asg
- get_po_for_primary_asg
- get_po_for_asg
dcasemor 17-OCT-2003 115.3 For the above 4 procedures, only
return rows when one of the values
is set to avoid the SQL returning
two rows for multiple assignments.
sramasam 22-OCT-2003 115.4 Added procedure to return url
to launch Self Service CWK Placement
dcasemor 29-JAN-2004 115.5 Added date-effective checking
in get_person_for_po_line and
get_person_for_vendor_site to
prevent a too_many_rows exception.
njaladi 17-mar-2004 115.6 Bug 3512537: gscc fix for sql.47
njaladi 17-mar-2004 115.7 Bug 3512537: gscc fix for sql.47
sbuche 26-mar-2004 115.8 Bug 3391399:
Added function asg_vendor_id_exist
to check the existance of vendor_id
column in HR assignments tables.
sbuche 02-APR-2002 115.9 Changes requested by OTA:
Added parameter p_effective_date to
the procedure get_person_for_po_line
and defaulted the value Trunc(sysdate)
when parameter is null.
Commented following functions and
procedure as they will not work
when more than one person or
assignment exists for a vendor site:
- get_asg_id_for_vendor_site
- get_person_id_for_vendor_site
- get_person_for_vendor_site
Modified the following procedure to
remove the reference of commented
procedure:
- asg_exist_for_vendor_site
svittal 21-APR-2004 115.10 Added code to generate url for PO
Notification.
svittal 13-JUN-2004 115.11 Bug fix 3666156.
njaladi 25-May-2005 115.12 Bug Fix 4323611: Modified procedure
asg_exist_for_po to use bind variables
instead of concatenation.
srannama 16-Nov-2012 115.14 Made changes for Bug 15914681
---------------------------------------------------------------------------|
*/
--
-- Private package constant declarations.
--
g_PACKAGE CONSTANT VARCHAR2(11) := 'hr_po_info.';
SELECT count(dd.column_name)
INTO l_count
FROM all_tab_columns dd, user_synonyms syn
WHERE syn.synonym_name = 'PER_ALL_ASSIGNMENTS_F'
AND syn.table_owner = dd.owner
AND syn.table_name = dd.table_name
AND (dd.column_name = 'VENDOR_ID' OR
dd.column_name = 'VENDOR_SITE_ID' OR
dd.column_name = 'PO_HEADER_ID' OR
dd.column_name = 'PO_LINE_ID')
AND rownum < 5
AND dd.owner = l_per_owner; -- 3512537
SELECT count(dd.column_name)
INTO l_count
FROM all_tab_columns dd, user_synonyms syn
WHERE syn.synonym_name = 'PER_ALL_ASSIGNMENTS_F'
AND syn.table_owner = dd.owner
AND syn.table_name = dd.table_name
AND dd.column_name = 'VENDOR_ID'
AND rownum < 2
AND dd.owner = l_per_owner; -- 3512537
' SELECT paaf.person_id '
||' ,paaf.assignment_id '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.po_line_id IS NOT NULL '
||' AND paaf.po_line_id = '||p_po_line_id
||' AND fnd_date.canonical_to_date('''
|| fnd_date.date_to_canonical(l_effective_date)||''')'
||' BETWEEN paaf.effective_start_date '
||' AND paaf.effective_end_date ';
' SELECT :1 '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.po_header_id IS NOT NULL '
||' AND paaf.po_header_id = :2 '---||p_po_header_id
||' AND rownum = 1';
' SELECT paaf.person_id '
||' ,paaf.assignment_id '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.vendor_site_id IS NOT NULL '
||' AND paaf.vendor_site_id = '||p_vendor_site_id
||' AND fnd_date.canonical_to_date('''
|| fnd_date.date_to_canonical(l_effective_date)||''')'
||' BETWEEN paaf.effective_start_date '
||' AND paaf.effective_end_date ';
' SELECT '''|| g_TRUE || ''''
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.vendor_id IS NOT NULL '
||' AND paaf.vendor_id = '||p_vendor_id
||' AND rownum = 1';
' SELECT '''|| g_TRUE || ''''
||' FROM per_all_assignments_f paaf '
||' WHERE paaf. vendor_site_id = '||p_vendor_site_id
||' AND rownum = 1';
' SELECT paaf.vendor_id '
||' ,paaf.vendor_site_id '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.person_id = '||p_person_id
||' AND paaf.primary_flag = ''Y'''
||' AND fnd_date.canonical_to_date('''
|| fnd_date.date_to_canonical(p_effective_date)||''')'
||' BETWEEN paaf.effective_start_date '
||' AND paaf.effective_end_date '
||' AND (paaf.vendor_id IS NOT NULL OR '
||' paaf.vendor_site_id IS NOT NULL) ';
' SELECT paaf.vendor_id '
||' ,paaf.vendor_site_id '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.assignment_id = '||p_assignment_id
||' AND fnd_date.canonical_to_date('''
|| fnd_date.date_to_canonical(p_effective_date)||''')'
||' BETWEEN paaf.effective_start_date '
||' AND paaf.effective_end_date '
||' AND (paaf.vendor_id IS NOT NULL OR '
||' paaf.vendor_site_id IS NOT NULL) ';
' SELECT paaf.po_header_id '
||' ,paaf.po_line_id '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.person_id = '||p_person_id
||' AND paaf.primary_flag = ''Y'''
||' AND fnd_date.canonical_to_date('''
|| fnd_date.date_to_canonical(p_effective_date)||''')'
||' BETWEEN paaf.effective_start_date '
||' AND paaf.effective_end_date '
||' AND (paaf.po_header_id IS NOT NULL OR '
||' paaf.po_line_id IS NOT NULL) ';
' SELECT paaf.po_header_id '
||' ,paaf.po_line_id '
||' FROM per_all_assignments_f paaf '
||' WHERE paaf.assignment_id = '||p_assignment_id
||' AND fnd_date.canonical_to_date('''
|| fnd_date.date_to_canonical(p_effective_date)||''')'
||' BETWEEN paaf.effective_start_date '
||' AND paaf.effective_end_date '
||' AND (paaf.po_header_id IS NOT NULL OR '
||' paaf.po_line_id IS NOT NULL) ';
select web_html_call || '&' || parameters url from fnd_form_functions
where function_name = p_function_name;