[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT currency_code FROM gl_sets_of_books
WHERE set_of_books_id = p_sob_id;
SELECT user_id, employee_id
FROM fnd_user
WHERE user_name = p_username;
/* =================== SELECTOR =================== */
PROCEDURE Selector(itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout OUT NOCOPY VARCHAR2) IS
l_session_org_id NUMBER;
DEBUG_LOG_STRING (l_proc_level, 'selector.Msg1',
' ** BEGIN SELECTOR ** ');
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg',
' Getting apps context with userid, respid, applid ' );
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg',
' Setting apps context with userid, respid, applid as '
||l_user_id ||' '|| l_resp_id ||' '|| l_appl_id );
DEBUG_LOG_STRING (l_proc_level, 'Selector.Msg2',
' result --> ' || resultout);
DEBUG_LOG_STRING (l_proc_level, 'selector.Msg3',
' ** END SELECTOR ** ' || resultout);
DEBUG_LOG_UNEXP_ERROR ('selector.unexp1','DEFAULT');
Wf_Core.Context ('IGIDOSL','Selector', itemtype, itemkey,
TO_CHAR(actid),funcmode);
END Selector;
SELECT SUM(NVL(s.funds_available,0) - NVL(s.new_balance,0))
FROM igi_dos_trx_sources s
WHERE trx_id IN (
SELECT trx_id FROM igi_dos_trx_headers
WHERE trx_number = Dossier_num
AND dossier_id = Dossier_id);
SELECT meaning
INTO l_trx_status
FROM igi_lookups
WHERE lookup_type = 'DOSSIER STATUS'
AND lookup_code = 'INPROCESS';
UPDATE igi_dos_trx_headers trx
SET trx.trx_status = l_trx_status,
trx.last_update_date= sysdate
WHERE trx.trx_number = dossier_num;
' updated igi_dos_trx_headers ');
SELECT igi_dos_approval_run_s1.NextVal
INTO l_approval_run
FROM sys.dual;
aname => 'SELECTED_USER_NAME',
avalue => l_picked_role);
' setting l_picked_role to SELECTED_USER_NAME ');
SELECT hap.position_id,
hap.business_group_id,
hap.organization_id,
fu.user_name
FROM hr_all_positions_f hap,
per_all_assignments_f paa,
fnd_user fu ,
per_people_f p,
per_periods_of_service b
WHERE
fu.user_id = g_userid
AND paa.person_id = p.person_id
AND paa.primary_flag = 'Y'
AND paa.period_of_service_id = b.period_of_service_id
AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND (b.actual_termination_date is null OR b.actual_termination_date>= trunc(sysdate) )
AND p.employee_number IS NOT NULL
and fu.start_date <= SYSDATE
and NVL(fu.end_date,SYSDATE) >= SYSDATE
and fu.employee_id IS NOT NULL
and fu.employee_id = P.PERSON_ID
and NVL(b.actual_termination_date,SYSDATE) >= SYSDATE
and P.business_group_id = paa.business_group_id
and paa.assignment_type = 'E'
and paa.business_group_id = hap.business_group_id
and paa.position_id IS NOT NULL
and paa.position_id = hap.position_id
and paa.organization_id = hap.organization_id
and hap.date_effective <= SYSDATE
and NVL(hap.date_end, SYSDATE) >= SYSDATE
and NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID') ;
SELECT hierarchy_id
FROM igi_dos_doc_types
WHERE dossier_id = p_dossier_id;
SELECT pos_structure_version_id
FROM per_pos_structure_versions
WHERE position_structure_id = p_hierarchy_id
AND SYSDATE BETWEEN date_FROM AND NVL(date_to, SYSDATE)
AND business_group_id = p_business_group_id
AND version_number =
(SELECT MAX(version_number)
FROM per_pos_structure_versions
WHERE position_structure_id = p_hierarchy_id
AND SYSDATE BETWEEN date_FROM AND NVL(date_to,SYSDATE)
AND business_group_id = p_business_group_id);
SELECT ppse.parent_position_id
FROM per_pos_structure_elements ppse
WHERE ppse.pos_structure_version_id = p_pos_structure_ver_id
AND business_group_id = p_business_group_id
AND ppse.parent_position_id NOT IN
(SELECT subordinate_position_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_ver_id
AND business_group_id = p_business_group_id);
SELECT parent_position_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_hier_ver_id
AND business_group_id = p_business_group_id
AND subordinate_position_id = p_position_id;
aname => 'SELECTED_USER_NAME');
' GetItemAttrText SELECTED_USER_NAME --> ' || l_current_user_name);
SELECT meaning
FROM igi_lookups
WHERE lookup_type ='DOSSIER STATUS'
AND lookup_code ='COMPLETE';
SELECT dtype.sob_id
INTO l_sob_id
FROM igi_dos_doc_types dtype,
igi_dos_trx_headers thead
WHERE thead.dossier_id = dtype.dossier_id
AND thead.trx_number = l_dossier_num;
select trx_status into l_status
from IGI_DOS_TRX_HEADERS
where trx_status = ( SELECT meaning
FROM igi_lookups
WHERE lookup_type ='DOSSIER STATUS'
and lookup_code ='INPROCESS')
AND trx_number = l_trx_number ;
UPDATE igi_dos_trx_headers trx
SET trx.trx_status = l_trx_status,
trx.last_update_date= sysdate
WHERE trx.trx_number = l_dossier_num;
SELECT message_text
INTO l_fatal_error
FROM fnd_new_messages
WHERE message_name = 'IGI_DOS_ERROR_APPROVED';
SELECT meaning INTO l_trx_status
FROM igi_lookups
WHERE lookup_type ='DOSSIER STATUS'
and lookup_code ='REJECTED';
UPDATE IGI_DOS_TRX_HEADERS trx
SET trx.trx_status = l_trx_status,
trx.last_update_date= sysdate
WHERE trx.trx_number = l_dossier_id;
select fu.user_name user_name
FROM hr_all_positions_f hap,
per_all_assignments_f paa,
fnd_user fu ,
per_people_f p,
per_periods_of_service b
WHERE
paa.person_id = p.person_id
AND paa.primary_flag = 'Y'
AND paa.period_of_service_id = b.period_of_service_id
AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND (b.actual_termination_date >= trunc(sysdate) or b.actual_termination_date is null)
AND p.employee_number IS NOT NULL
and fu.start_date <= SYSDATE
and NVL(fu.end_date,SYSDATE) >= SYSDATE
and fu.employee_id IS NOT NULL
and fu.employee_id = p.person_id
and p.business_group_id = paa.business_group_id
and p.business_group_id = l_business_group_id
-- and paa.organization_id = l_organization_id bug#10237895
and paa.assignment_type = 'E'
and paa.business_group_id = hap.business_group_id
and paa.position_id IS NOT NULL
and paa.position_id = hap.position_id
and paa.organization_id = hap.organization_id
and hap.date_effective <= SYSDATE
and NVL(hap.date_end, SYSDATE) >= SYSDATE
and NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID')
and hap.position_id = l_parent_position_id;
aname => 'SELECTED_USER_NAME',
avalue => l_next_authoriser) ;
' l_next_authoriser - SELECTED_USER_NAME --> ' || l_next_authoriser);
SELECT dtype.SOB_ID
INTO l_sob_id
FROM IGI_DOS_DOC_TYPES dtype,
igi_dos_trx_headers thead
WHERE thead.dossier_id = dtype.dossier_id
and thead.trx_number = l_trx_number;
select trx_status into l_status
from IGI_DOS_TRX_HEADERS
where trx_status = ( SELECT meaning
FROM igi_lookups
WHERE lookup_type ='DOSSIER STATUS'
and lookup_code ='INPROCESS')
AND trx_number = l_trx_number ;
SELECT message_text
INTO l_fatal_error
FROM fnd_new_messages
WHERE message_name = 'IGI_DOS_ERROR_REJECTED';
SELECT meaning INTO l_trx_status
FROM igi_lookups
WHERE lookup_type ='DOSSIER STATUS'
and lookup_code ='REJECTED';
UPDATE IGI_DOS_TRX_HEADERS trx
SET trx.trx_status = l_trx_status,
trx.last_update_date= sysdate
WHERE trx.trx_number = l_trx_number ;
' updated igi_dos_trx_headers ');
SELECT trx_id FROM igi_dos_trx_headers
WHERE trx_number = l_dossier_num
AND dossier_id = l_dossier_id;
SELECT s.budget_name,
NVL(s.funds_available,0) - NVL(s.new_balance,0) amount,
s.visible_segments,
s.period_name,
s.source_id,
s.source_trx_id
FROM igi_dos_trx_sources s
WHERE trx_id = l_dossier_trx_id
AND EXISTS (SELECT budget_name
FROM igi_dos_trx_dest d
WHERE d.trx_id = l_dossier_trx_id
AND source_id = s.source_id
AND source_trx_id = s.source_trx_id);
SELECT budget_name,
ABS(NVL(funds_available,0) - NVL(new_balance,0)) amount,
visible_segments,
period_name,
source_id,
source_trx_id,
destination_id,
dest_trx_id
FROM igi_dos_trx_dest
WHERE trx_id = l_dossier_trx_id
AND source_id = p_source_id
AND source_trx_id = p_source_trx_id
ORDER BY destination_id,
dest_trx_id;
SELECT dtype.SOB_ID
INTO g_sob_id
FROM IGI_DOS_DOC_TYPES dtype,
igi_dos_trx_headers thead
WHERE thead.dossier_id = dtype.dossier_id
and thead.trx_number = l_dossier_num;
' l_trx_detail updated to clob ');
SELECT meaning
FROM igi_lookups
WHERE lookup_type = 'DOSSIER STATUS'
AND lookup_code = 'CREATING';
UPDATE igi_dos_trx_headers trx
SET trx.trx_status = l_trx_status,
trx.last_update_date= sysdate
WHERE trx.trx_number = l_dossier_num;
SELECT employee_id
FROM fnd_user
WHERE user_id = p_user_id;
select hap.position_id,
hap.name,
hap.business_group_id,
hap.organization_id
FROM
hr_all_positions_f hap,
per_all_assignments_f paa,
per_people_f p,
per_periods_of_service b
WHERE
p.person_id = p_emp_id
AND paa.person_id = p.person_id
AND paa.primary_flag = 'Y'
AND paa.period_of_service_id = b.period_of_service_id
AND TRUNC(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND (b.actual_termination_date>= trunc(sysdate) or b.actual_termination_date is null)
AND p.employee_number IS NOT NULL
and p.business_group_id = paa.business_group_id
and paa.assignment_type = 'E'
and paa.business_group_id = hap.business_group_id
and paa.position_id IS NOT NULL
and paa.position_id = hap.position_id
and paa.organization_id = hap.organization_id
and hap.date_effective <= SYSDATE
and NVL(hap.date_end, SYSDATE) >= SYSDATE
and NVL(UPPER(hap.status), 'VALID') NOT IN ('INVALID') ;
SELECT hierarchy_id
FROM igi_dos_doc_types
WHERE dossier_id = p_dossier_id;
SELECT pos_structure_version_id
FROM per_pos_structure_versions
WHERE position_structure_id = p_hierarchy_id
AND SYSDATE BETWEEN date_FROM AND NVL(date_to, SYSDATE)
AND business_group_id = p_business_group_id
AND version_number =
(SELECT MAX(version_number)
FROM per_pos_structure_versions
WHERE position_structure_id = p_hierarchy_id
AND SYSDATE BETWEEN date_FROM AND NVL(date_to,SYSDATE)
AND business_group_id = p_business_group_id);
SELECT pos_structure_element_id
FROM per_pos_structure_elements
WHERE pos_structure_version_id = p_pos_structure_ver_id
AND business_group_id = p_business_group_id
AND (subordinate_position_id = p_position_id OR
parent_position_id = p_position_id);
SELECT message_text
INTO l_fatal_error
FROM fnd_new_messages
WHERE message_name = 'IGI_DOS_VALID_RESP';
l_fatal_error := 'Select a valid responsibilty before approving or rejecting the dossier.';