[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT extv.value
FROM pay_user_tables tbls
,pay_user_columns asgc
,pay_user_columns extc
,pay_user_rows_f urws
,pay_user_column_instances_f asgv
,pay_user_column_instances_f extv
WHERE tbls.user_table_name =
'PQP_GB_TP_EMPLOYMENT_CATEGORY_TRANSALATION_TABLE'
AND asgc.user_table_id = tbls.user_table_id
AND extc.user_table_id = tbls.user_table_id
AND asgc.user_column_name = 'Assignment Employment Category Lookup Code'
AND extc.user_column_name = 'Pension Extracts Employment Category Code'
AND urws.user_table_id = tbls.user_table_id
AND (urws.business_group_id = g_business_group_id
OR
(urws.business_group_id IS NULL
AND urws.legislation_code = g_legislation_code)
OR
(urws.business_group_id IS NULL AND urws.legislation_code IS NULL)
)
AND p_effective_date BETWEEN urws.effective_start_date
AND urws.effective_end_date
AND asgv.user_column_id = asgc.user_column_id
AND p_effective_date BETWEEN asgv.effective_start_date
AND asgv.effective_end_date
AND extv.user_column_id = extc.user_column_id
AND p_effective_date BETWEEN extv.effective_start_date
AND extv.effective_end_date
AND asgv.user_row_id = urws.user_row_id
AND extv.user_row_id = asgv.user_row_id
AND asgv.value = p_asg_emp_cat_cd;
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 drcd.ext_rcd_id = rdtl.ext_rcd_id
AND drcd.rcd_type_cd = 'H'
AND SUBSTR(rdtl.val_01
,1
,INSTR(rdtl.val_01,':',1,3)--upto third occurence
)
=
SUBSTR(g_header_system_element
,1
,INSTR(g_header_system_element,':',1,3)
)
AND rslt.eff_dt < g_effective_date
-- The following part check the successful runs only for the LEA
-- for which the report is run currently.
-- the g_lea_number has already been set by the set_extract_globals.
AND SUBSTR(rdtl.val_01
,INSTR(rdtl.val_01,':',1,1)+1 --lea Number
,INSTR(rdtl.val_01,':',1,2)-INSTR(rdtl.val_01,':',1,1)-1) = g_lea_number ;
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
-- even failed future runs are to be considered
-- since pay process events might have completed
-- 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 drcd.ext_rcd_id = rdtl.ext_rcd_id
AND drcd.rcd_type_cd = 'H'
AND SUBSTR(rdtl.val_01
,1
,INSTR(rdtl.val_01,':',1,3) --upto third occurence of
)
=
SUBSTR(g_header_system_element
,1
,INSTR(g_header_system_element,':',1,3)
)
AND rslt.eff_dt >= g_effective_date -- include any runs on the same day
-- The following part check the successful runs only for the LEA
-- for which the report is run currently.
-- the g_lea_number has already been set by the set_extract_globals.
AND SUBSTR(rdtl.val_01
,INSTR(rdtl.val_01,':',1,1)+1 --lea Number
,INSTR(rdtl.val_01,':',1,2)-INSTR(rdtl.val_01,':',1,1)-1) = g_lea_number ;
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_last_effective_date
FROM DUAL;
SELECT user_table_id
FROM pay_user_tables
WHERE user_table_name = p_udt_name
AND ( ( business_group_id IS NULL
AND legislation_code = g_legislation_code
)
OR ( business_group_id IS NOT NULL
AND business_group_id = g_business_group_id
)
);
SELECT row_low_range_or_name
FROM pay_user_rows_f
WHERE user_table_id = c_udt_id
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date
ORDER BY display_sequence;
g_tab_mng_aln_eles.DELETE;
g_tab_ret_aln_eles.DELETE;
g_tab_tlr_aln_eles.DELETE;
UPDATE pay_process_events
SET retroactive_status = p_status
,status = p_status
WHERE assignment_id = p_assignment_id
AND change_type = 'REPORTS'
AND creation_date -- allow all events as of and on last eff dt
BETWEEN GREATEST(NVL(p_start_date,g_last_effective_date)
,g_last_effective_date)
AND LEAST(NVL(p_end_date,g_effective_run_date)
,g_effective_run_date)
; -- allow all events upto end of day (eff_dt - 1)
debug(fnd_number.number_to_canonical(SQL%ROWCOUNT)||' PPE row(s) updated.');
SELECT *
FROM fnd_descriptive_flexs_vl
WHERE descriptive_flexfield_name = UPPER(p_desc_flex_name);
debug('SELECT '||p_column_name);
OPEN base_table_csr FOR 'SELECT '||p_column_name||
l_table_specific_clause;
'SELECT '||p_segment_col_name||' '||
'FROM '||p_entity_name||' '||
'WHERE rowid = :b_rowid ';
SELECT 'Y'
FROM DUAL
WHERE LENGTH(NVL(p_sal_grade,'x')) <= 3
AND ASCII( SUBSTR(p_sal_grade,1,1))
BETWEEN 65 AND 90
AND TO_NUMBER(SUBSTR(p_sal_grade,2)) <= 99;
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 rcd.ext_rcd_id = dtl.ext_rcd_id
--AND rcd.rcd_type_cd = 'D'
AND dtl.ext_rcd_id = p_ext_rcd_id
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';
IF csr_pqp_asg_attributes_dn%NOTFOUND -- Insert
OR
l_last_pqp_asg_attributes.tp_is_teacher = 'NONT' -- Update
THEN
--
debug('This assignment HAS become a teacher.', 110);
l_proration_dates.DELETE;
l_proration_changes.DELETE;
l_proration_dates.DELETE;
l_proration_changes.DELETE;
l_proration_dates.DELETE;
l_proration_changes.DELETE;