[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX (
TRUNC (rslt.eff_dt)
) -- highest effective date of all prev runs
FROM pqp_extract_attributes pqea,
ben_ext_rslt rslt,
ben_ext_rslt_dtl rdtl
-- ben_ext_rcd drcd
WHERE pqea.ext_dfn_type = g_extract_type
AND rslt.ext_dfn_id = pqea.ext_dfn_id
AND rslt.business_group_id = g_business_group_id
AND rslt.ext_stat_cd NOT IN ('F' -- Job Failure
,
'R' -- Rejected By User
,
'X' -- Executing
)
AND rdtl.ext_rslt_id = rslt.ext_rslt_id
AND rdtl.ext_rcd_id = p_ext_rcd_id
-- AND drcd.ext_rcd_id = rdtl.ext_rcd_id
-- AND drcd.rcd_type_cd = 'H'
AND SUBSTR (
rdtl.val_01,
1,
INSTR (g_header_system_element, ':', 1)
) = SUBSTR (
g_header_system_element,
1,
INSTR (g_header_system_element, ':', 1)
)
AND rslt.eff_dt < g_effective_date;
SELECT MIN (
TRUNC (rslt.eff_dt)
) -- least effective date of all future runs
FROM pqp_extract_attributes pqea,
ben_ext_rslt rslt,
ben_ext_rslt_dtl rdtl
-- ben_ext_rcd drcd
WHERE pqea.ext_dfn_type = g_extract_type
AND rslt.ext_dfn_id = pqea.ext_dfn_id
AND rslt.business_group_id = g_business_group_id
AND rdtl.ext_rslt_id = rslt.ext_rslt_id
AND rdtl.ext_rcd_id = p_ext_rcd_id
-- AND drcd.ext_rcd_id = rdtl.ext_rcd_id
-- AND drcd.rcd_type_cd = 'H'
AND SUBSTR (
rdtl.val_01,
1,
INSTR (g_header_system_element, ':', 1)
) = SUBSTR (
g_header_system_element,
1,
INSTR (g_header_system_element, ':', 1)
)
AND rslt.eff_dt >= g_effective_date; -- include any runs on the same day
SELECT TO_DATE (
'01-04-'
|| DECODE (
SIGN (
TO_NUMBER (TO_CHAR (g_effective_date, 'MM'))
- 04
),
-1, TO_CHAR (
ADD_MONTHS (g_effective_date, -12),
'YYYY'
),
TO_CHAR (g_effective_date, 'YYYY')
),
'DD-MM-YYYY'
)
FROM DUAL;
SELECT TO_DATE (
'01-04-'
|| DECODE (
SIGN (
TO_NUMBER (TO_CHAR (g_effective_date, 'MM'))
- 04
),
-1, TO_CHAR (
ADD_MONTHS (g_effective_date, -12),
'YYYY'
),
TO_CHAR (g_effective_date, 'YYYY')
),
'DD-MM-YYYY'
)
INTO g_effective_start_date
FROM DUAL;
p_tab_ele_ids.DELETE;
l_row_name.DELETE;
l_value.DELETE;
p_secondary_asg_ids.DELETE;
p_secondary_asg_ids.DELETE;
SELECT fnd_number.canonical_to_number (
SUBSTR (
MAX (
LPAD (paa.action_sequence, 15, '0')
|| paa.assignment_action_id
),
16
)
)
FROM pay_assignment_actions paa, pay_payroll_actions ppa
WHERE paa.assignment_id = c_assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND paa.source_action_id IS NOT NULL
AND ppa.effective_date <= c_effective_date
AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B');
g_pension_ele_ids.DELETE;
g_ele_entry_details.DELETE;
g_secondary_asg_ids.DELETE;
g_asg_details.DELETE;
g_ni_cont_out_ele_ids.DELETE;
g_ni_ele_details.DELETE;
g_pen_ele_details.DELETE;
g_pen_ele_details.DELETE;
SELECT pee.element_entry_id, pee.effective_start_date
,pee.effective_end_date
FROM pay_element_entries_f pee, pay_element_links_f pel
WHERE pee.assignment_id = p_assignment_id
AND pee.entry_type = 'E'
AND pee.element_link_id = pel.element_link_id
AND c_effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND pel.element_type_id = g_ni_ele_type_id
AND c_effective_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
ORDER BY pee.effective_start_date;
SELECT DECODE (
c_column_name,
'P = Reduced Rate Conts but now Full Rate', 'P',
'Y = Reduced Rate Conts Current', 'Y'
)
FROM DUAL;
SELECT COUNT (*)
FROM ben_ext_rslt_dtl dtl
-- ,ben_ext_rcd rcd
WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
AND dtl.ext_rcd_id = p_ext_rcd_id
-- AND rcd.ext_rcd_id = dtl.ext_rcd_id
-- AND rcd.rcd_type_cd = 'D'
AND DECODE (
NVL (TRIM (p_type), hr_api.g_varchar2),
hr_api.g_varchar2, hr_api.g_varchar2,
dtl.val_01
) = NVL (TRIM (p_type), hr_api.g_varchar2)
AND dtl.val_01 <> 'DELETE';
SELECT NVL (SUM (TO_NUMBER(DECODE (p_val_seq,
23, VAL_23,
25, VAL_25,
27, VAL_27,
29, VAL_29,
31, VAL_31,
33, VAL_33,
35, VAL_35,
42, VAL_42,
44, VAL_44
)
)), 0) total_value
FROM ben_ext_rslt_dtl dtl
-- ,ben_ext_rcd rcd
WHERE dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
AND dtl.ext_rcd_id = p_ext_rcd_id;
g_pension_ele_ids.DELETE;
g_ele_entry_details.DELETE;
g_secondary_asg_ids.DELETE;
g_asg_details.DELETE;
g_ni_ele_details.DELETE;
g_pen_ele_details.DELETE;
g_pen_ele_details.DELETE;
SELECT pee.element_entry_id, pee.effective_start_date
,pee.effective_end_date, pel.element_type_id
FROM pay_element_entries_f pee, pay_element_links_f pel
WHERE pee.assignment_id = c_assignment_id
AND pee.entry_type = 'E'
AND pee.element_link_id = pel.element_link_id
AND (
c_effective_start_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
OR c_effective_end_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
OR pee.effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
OR pee.effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
AND pel.element_type_id = g_ni_ele_type_id
AND (
c_effective_start_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
OR c_effective_end_date BETWEEN pel.effective_start_date
AND pel.effective_end_date
OR pel.effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
OR pel.effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
)
ORDER BY pee.effective_start_date DESC;
SELECT screen_entry_value, effective_start_date, effective_end_date
FROM pay_element_entry_values_f
WHERE element_entry_id = c_element_entry_id
AND (
effective_start_date BETWEEN c_effective_start_date
AND c_effective_end_date
OR effective_end_date BETWEEN c_effective_start_date
AND c_effective_end_date
OR c_effective_start_date BETWEEN effective_start_date
AND effective_end_date
OR c_effective_end_date BETWEEN effective_start_date
AND effective_end_date
)
AND input_value_id = c_input_value_id;
SELECT DISTINCT (ptp.end_date) end_date
FROM per_time_periods ptp,
pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE ptp.time_period_id = ppa.time_period_id
AND ppa.effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND ppa.payroll_action_id = paa.payroll_action_id
AND c_effective_date BETWEEN ptp.start_date
AND ptp.end_date
AND ppa.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND NVL (ppa.business_group_id, g_business_group_id) =
g_business_group_id
AND paa.assignment_id = c_assignment_id
ORDER BY ptp.end_date;
g_pension_ele_ids.DELETE;
g_ele_entry_details.DELETE;
g_secondary_asg_ids.DELETE;
g_asg_details.DELETE;
g_ni_ele_details.DELETE;
g_pen_ele_details.DELETE;
g_pen_ele_details.DELETE;
l_event_details.DELETE;
l_event_details.DELETE;