[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ORG_INFORMATION5
FROM HR_ORGANIZATION_INFORMATION
WHERE ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_INFORMATION'
AND ORGANIZATION_ID = p_business_group_id;
SELECT DECODE(p_segment,'SEGMENT1',SEGMENT1,
'SEGMENT2',SEGMENT2,
'SEGMENT3',SEGMENT3,
'SEGMENT4',SEGMENT4,
'SEGMENT5',SEGMENT5,
'SEGMENT6',SEGMENT6,
'SEGMENT7',SEGMENT7,
'SEGMENT8',SEGMENT8,
'SEGMENT9',SEGMENT9,
'SEGMENT10',SEGMENT10,
'SEGMENT11',SEGMENT11,
'SEGMENT12',SEGMENT12,
'SEGMENT13',SEGMENT13,
'SEGMENT14',SEGMENT14,
'SEGMENT15',SEGMENT15,
'SEGMENT16',SEGMENT16,
'SEGMENT17',SEGMENT17,
'SEGMENT18',SEGMENT18,
'SEGMENT19',SEGMENT19,
'SEGMENT20',SEGMENT20,
'SEGMENT21',SEGMENT21,
'SEGMENT22',SEGMENT22,
'SEGMENT23',SEGMENT23,
'SEGMENT24',SEGMENT24,
'SEGMENT25',SEGMENT25,
'SEGMENT26',SEGMENT26,
'SEGMENT27',SEGMENT27,
'SEGMENT28',SEGMENT28,
'SEGMENT29',SEGMENT29,
'SEGMENT30',SEGMENT30) AGCY
FROM PER_POSITION_DEFINITIONS
WHERE DECODE(p_segment,'SEGMENT1',segment1
,'SEGMENT2',segment2
,'SEGMENT3',segment3
,'SEGMENT4',segment4
,'SEGMENT5',segment5
,'SEGMENT6',segment6
,'SEGMENT7',segment7
,'SEGMENT8',segment8
,'SEGMENT9',segment9
,'SEGMENT10',segment10
,'SEGMENT11',segment11
,'SEGMENT12',segment12
,'SEGMENT13',segment13
,'SEGMENT14',segment14
,'SEGMENT15',segment15
,'SEGMENT16',segment16
,'SEGMENT17',segment17
,'SEGMENT18',segment18
,'SEGMENT19',segment19
,'SEGMENT20',segment20
,'SEGMENT21',segment21
,'SEGMENT22',segment22
,'SEGMENT23',segment23
,'SEGMENT24',segment24
,'SEGMENT25',segment25
,'SEGMENT26',segment26
,'SEGMENT27',segment27
,'SEGMENT28',segment28
,'SEGMENT29',segment29
,'SEGMENT30',segment30) like ''||p_agcy||'';
SELECT v.person_id,
v.status,
v.effective_start_date start_date,
v.effective_end_date end_date,
v.assignment_id,
v.position_id pos_id,
v.grade_id,
v.location_id loc_id,
pgd.segment1,
SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
hds.msa_code,
pp.date_effective,
pp.date_end ,
pp.permanent_temporary_flag
FROM GHR_SF113_V v,
GHR_DUTY_STATIONS_F hds,
HR_LOCATION_EXTRA_INFO hlei,
PER_POSITION_DEFINITIONS ppd,
HR_ALL_POSITIONS_F pp,
PER_GRADES pg,
PER_GRADE_DEFINITIONS pgd
WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
,'SEGMENT2',ppd.segment2
,'SEGMENT3',ppd.segment3
,'SEGMENT4',ppd.segment4
,'SEGMENT5',ppd.segment5
,'SEGMENT6',ppd.segment6
,'SEGMENT7',ppd.segment7
,'SEGMENT8',ppd.segment8
,'SEGMENT9',ppd.segment9
,'SEGMENT10',ppd.segment10
,'SEGMENT11',ppd.segment11
,'SEGMENT12',ppd.segment12
,'SEGMENT13',ppd.segment13
,'SEGMENT14',ppd.segment14
,'SEGMENT15',ppd.segment15
,'SEGMENT16',ppd.segment16
,'SEGMENT17',ppd.segment17
,'SEGMENT18',ppd.segment18
,'SEGMENT19',ppd.segment19
,'SEGMENT20',ppd.segment20
,'SEGMENT21',ppd.segment21
,'SEGMENT22',ppd.segment22
,'SEGMENT23',ppd.segment23
,'SEGMENT24',ppd.segment24
,'SEGMENT25',ppd.segment25
,'SEGMENT26',ppd.segment26
,'SEGMENT27',ppd.segment27
,'SEGMENT28',ppd.segment28
,'SEGMENT29',ppd.segment29
,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
AND (p_rpt_date between v.effective_start_date and v.effective_end_date)
AND (trunc(p_rpt_date) between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
AND hlei.information_type = 'GHR_US_LOC_INFORMATION'
AND pp.position_definition_id = ppd.position_definition_id
AND pg.grade_id = v.grade_id
AND pg.grade_definition_id = pgd.grade_definition_id
AND pp.position_id = v.position_id
AND TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date
AND v.location_id = hlei.location_id
AND to_number(hlei.lei_information3) = hds.duty_station_id;
select 'X' from
per_assignments_f asg,
per_assignment_status_types ast
where asg.assignment_id = l_assignment_id
and p_empl_as_of_date between asg.effective_start_date
and asg.effective_end_date
and asg.primary_flag = 'Y'
and asg.assignment_type <> 'B'
and asg.assignment_status_type_id = ast.assignment_status_type_id
and ast.per_system_status = 'SUSP_ASSIGN';
SELECT gp.total_salary_amount,
gp.lump_sum_amount,
SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
hds.msa_code,
pp.position_id,
pa.effective_start_date,
past.per_system_status
FROM per_assignments_f pa,
per_assignment_status_types past,
GHR_PAYROLL gp,
HR_ALL_POSITIONS_F pp,
PER_POSITION_DEFINITIONS ppd,
GHR_DUTY_STATIONS_F hds,
HR_LOCATION_EXTRA_INFO hlei,
PER_GRADES pg,
PER_GRADE_DEFINITIONS pgd
WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
,'SEGMENT2',ppd.segment2
,'SEGMENT3',ppd.segment3
,'SEGMENT4',ppd.segment4
,'SEGMENT5',ppd.segment5
,'SEGMENT6',ppd.segment6
,'SEGMENT7',ppd.segment7
,'SEGMENT8',ppd.segment8
,'SEGMENT9',ppd.segment9
,'SEGMENT10',ppd.segment10
,'SEGMENT11',ppd.segment11
,'SEGMENT12',ppd.segment12
,'SEGMENT13',ppd.segment13
,'SEGMENT14',ppd.segment14
,'SEGMENT15',ppd.segment15
,'SEGMENT16',ppd.segment16
,'SEGMENT17',ppd.segment17
,'SEGMENT18',ppd.segment18
,'SEGMENT19',ppd.segment19
,'SEGMENT20',ppd.segment20
,'SEGMENT21',ppd.segment21
,'SEGMENT22',ppd.segment22
,'SEGMENT23',ppd.segment23
,'SEGMENT24',ppd.segment24
,'SEGMENT25',ppd.segment25
,'SEGMENT26',ppd.segment26
,'SEGMENT27',ppd.segment27
,'SEGMENT28',ppd.segment28
,'SEGMENT29',ppd.segment29
,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
AND (p_rpt_date between pa.effective_start_date and pa.effective_end_date)
AND (trunc(p_rpt_date) between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
AND (gp.date_from between p_pay_from and p_pay_to)
AND TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date
AND pa.location_id = hlei.location_id
AND hlei.information_type = 'GHR_US_LOC_INFORMATION'
AND pp.position_definition_id = ppd.position_definition_id
AND to_number(hlei.lei_information3) = hds.duty_station_id
AND gp.person_id = pa.person_id
AND pa.assignment_status_type_id = past.assignment_status_type_id
AND pp.position_id = pa.position_id
AND (pa.assignment_type <> 'B')
AND pa.primary_flag = 'Y'
AND pg.grade_id = pa.grade_id +0
AND pg.grade_definition_id = pgd.grade_definition_id
AND (past.per_system_status IN ('ACTIVE_ASSIGN', 'TERM_ASSIGN'))
AND SUBSTR (pgd.segment1, 1, 2) NOT IN ('CC', 'NA', 'NL', 'NS');
SELECT v.status,
v.position_id pos_id,
v.effective_start_date start_date,
v.effective_end_date end_date,
v.assignment_id, -- Bug 3264666 Anil
gpr.person_id,
gpr.noa_family_code,
gpr.first_action_la_code1,
gnoa.code,
SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
hds.msa_code,
gpr.effective_date
FROM GHR_SF113_V v,
GHR_PA_REQUESTS gpr,
GHR_NATURE_OF_ACTIONS gnoa,
HR_ALL_POSITIONS_F pp,
PER_POSITION_DEFINITIONS ppd,
GHR_DUTY_STATIONS_F hds
WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
,'SEGMENT2',ppd.segment2
,'SEGMENT3',ppd.segment3
,'SEGMENT4',ppd.segment4
,'SEGMENT5',ppd.segment5
,'SEGMENT6',ppd.segment6
,'SEGMENT7',ppd.segment7
,'SEGMENT8',ppd.segment8
,'SEGMENT9',ppd.segment9
,'SEGMENT10',ppd.segment10
,'SEGMENT11',ppd.segment11
,'SEGMENT12',ppd.segment12
,'SEGMENT13',ppd.segment13
,'SEGMENT14',ppd.segment14
,'SEGMENT15',ppd.segment15
,'SEGMENT16',ppd.segment16
,'SEGMENT17',ppd.segment17
,'SEGMENT18',ppd.segment18
,'SEGMENT19',ppd.segment19
,'SEGMENT20',ppd.segment20
,'SEGMENT21',ppd.segment21
,'SEGMENT22',ppd.segment22
,'SEGMENT23',ppd.segment23
,'SEGMENT24',ppd.segment24
,'SEGMENT25',ppd.segment25
,'SEGMENT26',ppd.segment26
,'SEGMENT27',ppd.segment27
,'SEGMENT28',ppd.segment28
,'SEGMENT29',ppd.segment29
,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
AND gpr.effective_date between p_last_rpt_date and p_rpt_date
AND (trunc(p_rpt_date) between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
AND gpr.effective_date between v.effective_start_date AND v.effective_end_date+1
AND gpr.pa_notification_id is not null
AND gpr.noa_family_code IN ('SEPARATION','NON_PAY_DUTY_STATUS')
AND NVL(gpr.first_noa_cancel_or_correct,'CORR') <> 'CANCEL'
AND gpr.duty_station_code = hds.duty_station_code
AND (gnoa.nature_of_action_id = gpr.first_noa_id)
AND (nvl(gpr.to_position_id,gpr.from_position_id) = pp.position_id)
AND (TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date)
AND (pp.position_definition_id = ppd.position_definition_id)
AND (gpr.person_id = v.person_id);
SELECT 'X'
FROM per_assignments_f asg,
per_assignment_status_types ast
WHERE asg.assignment_id = l_assignment_id
AND p_empl_as_of_date between asg.effective_start_date
AND asg.effective_end_date
AND asg.primary_flag = 'Y'
AND asg.assignment_type <> 'B'
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND ast.per_system_status = 'SUSP_ASSIGN';
SELECT v.status,
v.position_id pos_id,
v.effective_start_date start_date,
v.effective_end_date end_date,
gpr.person_id,
gpr.noa_family_code,
gpr.first_action_la_code1,
gnoa.code,
SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
hds.msa_code,
gpr.effective_date
FROM GHR_SF113_V v,
GHR_PA_REQUESTS gpr,
GHR_NATURE_OF_ACTIONS gnoa,
HR_ALL_POSITIONS_F pp,
PER_POSITION_DEFINITIONS ppd,
GHR_DUTY_STATIONS_F hds
WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
,'SEGMENT2',ppd.segment2
,'SEGMENT3',ppd.segment3
,'SEGMENT4',ppd.segment4
,'SEGMENT5',ppd.segment5
,'SEGMENT6',ppd.segment6
,'SEGMENT7',ppd.segment7
,'SEGMENT8',ppd.segment8
,'SEGMENT9',ppd.segment9
,'SEGMENT10',ppd.segment10
,'SEGMENT11',ppd.segment11
,'SEGMENT12',ppd.segment12
,'SEGMENT13',ppd.segment13
,'SEGMENT14',ppd.segment14
,'SEGMENT15',ppd.segment15
,'SEGMENT16',ppd.segment16
,'SEGMENT17',ppd.segment17
,'SEGMENT18',ppd.segment18
,'SEGMENT19',ppd.segment19
,'SEGMENT20',ppd.segment20
,'SEGMENT21',ppd.segment21
,'SEGMENT22',ppd.segment22
,'SEGMENT23',ppd.segment23
,'SEGMENT24',ppd.segment24
,'SEGMENT25',ppd.segment25
,'SEGMENT26',ppd.segment26
,'SEGMENT27',ppd.segment27
,'SEGMENT28',ppd.segment28
,'SEGMENT29',ppd.segment29
,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
AND gpr.effective_date between ''||p_pay_from||'' and ''||p_pay_to||''
AND (''||trunc(p_rpt_date)||'' between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
AND gpr.pa_notification_id is not null
AND gpr.duty_station_code = hds.duty_station_code
AND (gnoa.nature_of_action_id = gpr.first_noa_id)
AND (nvl(gpr.to_position_id,gpr.from_position_id) = pp.position_id)
AND (TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date)
AND (pp.position_definition_id = ppd.position_definition_id)
AND (gpr.person_id = v.person_id);
SELECT int.DATE_FROM pay_from,
int.DATE_TO pay_to,
int.INFORMATION1 person_id,
int.INFORMATION2 total_pay,
int.INFORMATION3 lump_sum
FROM GHR_INTERFACE int
WHERE int.SOURCE_NAME = 'PAYROLL'
AND int.date_from BETWEEN p_pay_from and p_pay_to;
SELECT count(*)
FROM ghr_payroll
WHERE date_from = p_pay_from
AND person_id = l_person_id;
INSERT INTO ghr_payroll
(PAYROLL_ID, PERSON_ID,
DATE_FROM, DATE_TO,
TOTAL_SALARY_AMOUNT, LUMP_SUM_AMOUNT)
VALUES (ghr_payroll_s.nextval, to_number(cr_rec.person_id),
cr_rec.pay_from, cr_rec.pay_to,
to_number(cr_rec.total_pay), to_number(cr_rec.lump_sum));
UPDATE ghr_payroll
SET total_salary_amount = to_number(cr_rec.total_pay),
lump_sum_amount = to_number(cr_rec.lump_sum)
WHERE date_from = p_pay_from
AND person_id = to_number(cr_rec.person_id);