[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX(asg.effective_end_date)
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_asg_id
AND asg.payroll_id IS NOT NULL
AND asg.effective_end_date BETWEEN TRUNC(p_date, p_date_mask)
AND p_date;
SELECT 'X'
FROM per_all_assignments_f asg,
pay_all_payrolls_f pay
WHERE asg.assignment_id = p_asg_id
AND p_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.payroll_id = pay.payroll_id
AND p_date BETWEEN pay.effective_start_date
AND pay.effective_end_date;
SELECT MAX(pay.effective_end_date)
FROM pay_all_payrolls_f pay,
per_all_assignments_f asg
WHERE asg.assignment_id = p_asg_id
AND asg.payroll_id = pay.payroll_id
AND pay.effective_end_date BETWEEN TRUNC(p_date, p_date_mask)
AND p_date;
SELECT dbl.defined_balance_id
FROM pay_defined_balances dbl
WHERE dbl.balance_type_id = (SELECT balance_type_id
FROM pay_balance_types blt
WHERE blt.balance_name = p_bal_name
AND (blt.legislation_code = 'CA'
OR blt.business_group_id = p_bus_grp_id))
AND dbl.balance_dimension_id =(SELECT balance_dimension_id
FROM pay_balance_dimensions bld
WHERE bld.database_item_suffix =
'_'|| p_dimension
AND (bld.legislation_code = 'CA'
OR bld.business_group_id = p_bus_grp_id))
AND (dbl.legislation_code = 'CA'
OR dbl.business_group_id = p_bus_grp_id);
* Select all the assignment actions in PAY_ACTION_CONTEXTS for a given GRE
* which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
CURSOR csr_get_asg_gre_add(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_start_date DATE,
p_end_date DATE) IS
SELECT DISTINCT asa.assignment_action_id
FROM pay_payroll_actions pya,
pay_assignment_actions asa,
per_all_assignments_f asg
WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND asa.assignment_id = asg.assignment_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date;
SELECT DISTINCT asa.assignment_action_id
FROM per_all_assignments_f asg,
pay_payroll_actions pya,
pay_assignment_actions asa,
pay_action_contexts acx,
ff_contexts cxt
WHERE cxt.context_name = 'SOURCE_ID'
AND cxt.context_id = acx.context_id
AND acx.context_value = TO_CHAR(p_src_id)
AND asa.assignment_action_id = acx.assignment_action_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND acx.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
* jurisdiction which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
CURSOR csr_get_asg_jd_gre_add(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_jd VARCHAR2,
p_start_date DATE,
p_end_date DATE) IS
SELECT DISTINCT asa.assignment_action_id
FROM per_all_assignments_f asg,
pay_payroll_actions pya,
pay_assignment_actions asa,
pay_action_contexts acx,
ff_contexts cxt
WHERE cxt.context_name = 'JURISDICTION_CODE'
AND cxt.context_id = acx.context_id
AND acx.context_value = p_jd
AND acx.assignment_action_id = asa.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND acx.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT DISTINCT asa.assignment_action_id
FROM per_all_assignments_f asg,
pay_payroll_actions pya,
pay_assignment_actions asa,
pay_action_contexts acx2,
ff_contexts cxt2,
pay_action_contexts acx1,
ff_contexts cxt1
WHERE cxt1.context_name = 'SOURCE_ID'
AND cxt1.context_id = acx1.context_id
AND acx1.context_value = TO_CHAR(p_src_id)
AND cxt2.context_name = 'JURISDICTION_CODE'
AND cxt2.context_id = acx2.context_id
AND acx2.context_value = p_jd
AND asa.assignment_action_id = acx1.assignment_action_id
AND asa.assignment_action_id = acx2.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND acx1.assignment_id = asg.assignment_id
AND acx2.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
* Select all the assignment actions in PAY_ACTION_CONTEXTS for a given GRE
* which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
-- NEW EBRA CURSOR csr_get_asg_gre_add definition with pay_run_balances
-- validation.
CURSOR csr_get_asg_gre_add_rb(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_start_date DATE,
p_end_date DATE,
p_def_bal_id NUMBER) IS
SELECT DISTINCT prb.assignment_action_id
FROM pay_run_balances prb,
per_all_assignments_f asg
WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND prb.assignment_id = asg.assignment_id
AND prb.tax_unit_id = p_gre_id
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date;
SELECT DISTINCT prb.assignment_action_id
FROM per_all_assignments_f asg,
pay_run_balances prb
WHERE prb.source_id = p_src_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date
AND prb.assignment_id = asg.assignment_id
AND prb.defined_balance_id = p_def_bal_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
* jurisdiction which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
-- NEW EBRA CURSOR csr_get_asg_jd_gre_add definition with
-- pay_run_balances validation
CURSOR csr_get_asg_jd_gre_add_rb(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_jd VARCHAR2,
p_start_date DATE,
p_end_date DATE,
p_def_bal_id NUMBER) IS
SELECT DISTINCT prb.assignment_action_id
FROM per_all_assignments_f asg,
pay_run_balances prb
WHERE prb.jurisdiction_code = p_jd
AND prb.tax_unit_id = p_gre_id
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date
AND prb.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT DISTINCT asa.assignment_action_id
FROM per_all_assignments_f asg,
pay_payroll_actions pya,
pay_assignment_actions asa,
pay_action_contexts acx2,
ff_contexts cxt2,
pay_action_contexts acx1,
ff_contexts cxt1
WHERE cxt1.context_name = 'SOURCE_ID'
AND cxt1.context_id = acx1.context_id
AND acx1.context_value = TO_CHAR(p_src_id)
AND cxt2.context_name = 'JURISDICTION_CODE'
AND cxt2.context_id = acx2.context_id
AND acx2.context_value = p_jd
AND asa.assignment_action_id = acx1.assignment_action_id
AND asa.assignment_action_id = acx2.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND acx1.assignment_id = asg.assignment_id
AND acx2.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT 'X'
FROM SYS.DUAL
WHERE EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa,
pay_assignment_latest_balances alb
WHERE alb.assignment_id = p_asg_id
AND alb.defined_balance_id = p_def_bal_id
AND alb.assignment_action_id = asa.assignment_action_id
AND asa.payroll_action_id = pya.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
* which have been run in a given time period.
* These are for the cases where all the additional parameter ie. location_id,
* organization_id, payroll_id, pay_basis_type are NULL
*/
CURSOR csr_get_asg_gre(p_gre_id NUMBER,
p_start_date DATE,
p_end_date DATE) IS
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments asg
WHERE EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_id = asg.assignment_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
SELECT DISTINCT(acx.assignment_id) assignment_id
FROM pay_action_contexts acx,
ff_contexts cxt
WHERE cxt.context_name = 'SOURCE_ID'
AND cxt.context_id = acx.context_id
AND acx.context_value = TO_CHAR(p_src_id)
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx.assignment_action_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
* jurisdiction which have been run in a given time period.
* These are for the cases where all the additional parameter ie. location_id,
* organization_id, payroll_id, pay_basis_type are NULL
*/
CURSOR csr_get_asg_jd_gre(p_gre_id NUMBER,
p_jd VARCHAR2,
p_start_date DATE,
p_end_date DATE) IS
SELECT DISTINCT(acx.assignment_id) assignment_id
FROM pay_action_contexts acx,
ff_contexts cxt
WHERE cxt.context_name = 'JURISDICTION_CODE'
AND cxt.context_id = acx.context_id
AND acx.context_value = p_jd
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
SELECT DISTINCT(acx1.assignment_id) assignment_id
FROM pay_action_contexts acx1,
pay_action_contexts acx2,
ff_contexts cxt1,
ff_contexts cxt2
WHERE cxt1.context_name = 'SOURCE_ID'
AND cxt1.context_id = acx1.context_id
AND acx1.context_value = TO_CHAR(p_src_id)
AND cxt2.context_name = 'JURISDICTION_CODE'
AND cxt2.context_id = acx2.context_id
AND acx2.context_value = p_jd
AND acx1.assignment_action_id = acx2.assignment_action_id
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx1.assignment_action_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
* which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
CURSOR csr_get_asg_gre_add(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_start_date DATE,
p_end_date DATE) IS
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments_f asg
WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_id = asg.assignment_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
SELECT DISTINCT(acx.assignment_id) assignment_id
FROM per_all_assignments_f asg,
pay_action_contexts acx,
ff_contexts cxt
WHERE cxt.context_name = 'SOURCE_ID'
AND cxt.context_id = acx.context_id
AND acx.context_value = TO_CHAR(p_src_id)
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx.assignment_action_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date)
AND acx.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
* jurisdiction which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
CURSOR csr_get_asg_jd_gre_add(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_jd VARCHAR2,
p_start_date DATE,
p_end_date DATE) IS
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments_f asg,
pay_action_contexts acx,
ff_contexts cxt
WHERE cxt.context_name = 'JURISDICTION_CODE'
AND cxt.context_id = acx.context_id
AND acx.context_value = p_jd
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE acx.assignment_action_id = asa.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date)
AND acx.assignment_id = asg.assignment_id
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id+0 IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT DISTINCT(acx1.assignment_id) assignment_id
FROM per_all_assignments_f asg,
pay_action_contexts acx2,
pay_action_contexts acx1,
ff_contexts cxt2,
ff_contexts cxt1
WHERE cxt1.context_name = 'SOURCE_ID'
AND cxt1.context_id = acx1.context_id
AND acx1.context_value = TO_CHAR(p_src_id)
AND acx1.assignment_id = asg.assignment_id
AND cxt2.context_name = 'JURISDICTION_CODE'
AND cxt2.context_id = acx2.context_id
AND acx2.context_value = p_jd
AND acx2.assignment_id = asg.assignment_id
AND acx1.assignment_action_id = acx2.assignment_action_id
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx1.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date)
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT 'X'
FROM SYS.DUAL
WHERE EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa,
pay_assignment_latest_balances alb
WHERE alb.assignment_id = p_asg_id
AND alb.defined_balance_id = p_def_bal_id
AND alb.assignment_action_id = asa.assignment_action_id
AND asa.payroll_action_id = pya.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
* which have been run in a given time period.
* These are for the cases where all the additional parameter ie. location_id,
* organization_id, payroll_id, pay_basis_type are NULL
*/
-- NEW EBRA CURSOR csr_get_asg_gre definition with
-- pay_run_balances validation.
CURSOR csr_get_asg_gre_rb(p_gre_id NUMBER,
p_start_date DATE,
p_end_date DATE,
p_def_bal_id NUMBER) IS
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments_f asg
WHERE EXISTS (SELECT 'X'
FROM pay_run_balances prb
WHERE prb.assignment_id = asg.assignment_id
AND prb.tax_unit_id = p_gre_id
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date);
SELECT DISTINCT(prb.assignment_id) assignment_id
FROM pay_run_balances prb
WHERE prb.source_id = p_src_id
AND prb.effective_date BETWEEN p_start_date AND p_end_date;
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
* jurisdiction which have been run in a given time period.
* These are for the cases where all the additional parameter ie. location_id,
* organization_id, payroll_id, pay_basis_type are NULL
*/
-- NEW EBRA CURSOR csr_get_asg_jd_gre definition with
-- pay_run_balances validation.
CURSOR csr_get_asg_jd_gre_rb(p_gre_id NUMBER,
p_jd VARCHAR2,
p_start_date DATE,
p_end_date DATE,
p_def_bal_id NUMBER) IS
SELECT DISTINCT(prb.assignment_id) assignment_id
FROM pay_run_balances prb
WHERE prb.tax_unit_id = p_gre_id
AND prb.jurisdiction_code = p_jd
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date AND p_end_date;
SELECT DISTINCT(acx1.assignment_id) assignment_id
FROM pay_action_contexts acx1,
pay_action_contexts acx2,
ff_contexts cxt1,
ff_contexts cxt2
WHERE cxt1.context_name = 'SOURCE_ID'
AND cxt1.context_id = acx1.context_id
AND acx1.context_value = TO_CHAR(p_src_id)
AND cxt2.context_name = 'JURISDICTION_CODE'
AND cxt2.context_id = acx2.context_id
AND acx2.context_value = p_jd
AND acx1.assignment_action_id = acx2.assignment_action_id
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx1.assignment_action_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date);
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
* which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
-- NEW CURSOR csr_get_asg_gre_add definition with
-- pay_run_balances validation.
CURSOR csr_get_asg_gre_add_rb(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_start_date DATE,
p_end_date DATE,
p_def_bal_id NUMBER) IS
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments_f asg
WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND EXISTS (SELECT 'X'
FROM pay_run_balances prb
WHERE prb.assignment_id = asg.assignment_id
AND prb.tax_unit_id = p_gre_id
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date);
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments_f asg
WHERE EXISTS (SELECT 'X'
FROM pay_run_balances prb
WHERE asg.assignment_id = prb.assignment_id
AND prb.source_id = p_src_id
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date)
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
* Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
* jurisdiction which have been run in a given time period.
* These are for the cases where at least one of the additional parameter is
* NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
*/
-- NEW EBRA CURSOR csr_get_asg_jd_gre_add definition with
-- pay_run_balances validation.
CURSOR csr_get_asg_jd_gre_add_rb(p_org_id NUMBER,
p_loc_id NUMBER,
p_pay_id NUMBER,
p_basis_type VARCHAR2,
p_gre_id NUMBER,
p_jd VARCHAR2,
p_start_date DATE,
p_end_date DATE,
p_def_bal_id NUMBER) IS
SELECT DISTINCT(asg.assignment_id) assignment_id
FROM per_all_assignments_f asg
WHERE EXISTS (SELECT 'X'
FROM pay_run_balances prb
WHERE prb.assignment_id = asg.assignment_id
AND prb.tax_unit_id = p_gre_id
AND prb.jurisdiction_code = p_jd
AND prb.defined_balance_id = p_def_bal_id
AND prb.effective_date BETWEEN p_start_date
AND p_end_date)
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT DISTINCT(acx1.assignment_id) assignment_id
FROM per_all_assignments_f asg,
pay_action_contexts acx2,
pay_action_contexts acx1,
ff_contexts cxt2,
ff_contexts cxt1
WHERE cxt1.context_name = 'SOURCE_ID'
AND cxt1.context_id = acx1.context_id
AND acx1.context_value = TO_CHAR(p_src_id)
AND acx1.assignment_id = asg.assignment_id
AND cxt2.context_name = 'JURISDICTION_CODE'
AND cxt2.context_id = acx2.context_id
AND acx2.context_value = p_jd
AND acx2.assignment_id = asg.assignment_id
AND acx1.assignment_action_id = acx2.assignment_action_id
AND EXISTS (SELECT 'X'
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.assignment_action_id = acx1.assignment_action_id
AND asa.tax_unit_id = p_gre_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date)
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL));
SELECT asg.assignment_id,
asg.business_group_id
FROM per_all_assignments_f asg,
hr_soft_coding_keyflex sck
WHERE decode(p_gre_type, 'T4A/RL1', sck.segment11, 'T4A/RL2', sck.segment12,
sck.segment1) = TO_CHAR(p_gre)
AND asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND EXISTS(SELECT 'X'
FROM pay_assignment_actions asa,
pay_payroll_actions pya
WHERE asa.tax_unit_id = p_gre
AND asg.assignment_id = asa.assignment_id
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND pya.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND pya.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date)
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND ROWNUM=1;
select business_group_id
from hr_all_organization_units
where organization_id = cp_gre_id;
select prb.assignment_id,paf.business_group_id
from pay_run_balances prb,per_all_assignments_f paf
where prb.defined_balance_id = cp_def_bal_id
and prb.tax_unit_id = p_gre
and prb.effective_date between p_start_date AND p_end_date
and prb.assignment_id = paf.assignment_id
and prb.effective_date between paf.effective_start_date AND
paf.effective_end_date
and rownum = 1;
SELECT asa.assignment_id,pya.business_group_id
FROM pay_payroll_actions pya,
pay_assignment_actions asa
WHERE asa.tax_unit_id = p_gre
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND pya.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND rownum = 1;
SELECT asg.assignment_id,
asg.business_group_id
FROM pay_payroll_actions pya,
per_all_assignments_f asg,
pay_assignment_actions asa,
pay_action_contexts acx,
ff_contexts cxt
WHERE acx.context_value = TO_CHAR(p_src)
AND cxt.context_id = acx.context_id
AND cxt.context_name = 'SOURCE_ID'
AND asa.assignment_action_id = acx.assignment_action_id
AND asg.assignment_id = asa.assignment_id
AND asg.organization_id = NVL(p_organization_id, asg.organization_id)
AND asg.location_id = NVL(p_location_id, asg.location_id)
AND asg.payroll_id = NVL(p_payroll_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND pya.payroll_action_id = asa.payroll_action_id
AND pya.effective_date BETWEEN p_start_date
AND p_end_date
AND pya.action_type IN ('R', 'Q', 'I', 'V', 'B')
AND pya.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND ROWNUM = 1;
select org_information5
into lv_gre_type
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.organization_id = p_gre_id;
SELECT asg.assignment_id,
asg.business_group_id
FROM per_all_assignments_f asg,
pay_run_balances prb
WHERE prb.defined_balance_id = cp_def_bal_id
and prb.effective_date between p_start_date AND p_end_date
and prb.tax_unit_id = p_gre
and prb.assignment_id = asg.assignment_id
and prb.effective_date between asg.effective_start_date
AND asg.effective_end_date
and exists (select 1
from hr_soft_coding_keyflex sck
where asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND decode(p_gre_type, 'T4A/RL1', sck.segment11,
'T4A/RL2', sck.segment12,
sck.segment1) = TO_CHAR(p_gre))
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY')
)
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD'))
)
OR (p_basis_type IS NULL)
)
AND ROWNUM=1;
select prb.assignment_id,paf.business_group_id
from pay_run_balances prb,per_all_assignments_f paf
where prb.defined_balance_id = cp_def_bal_id
and prb.tax_unit_id = p_gre
and prb.effective_date between p_start_date AND p_end_date
and prb.assignment_id = paf.assignment_id
and prb.effective_date between paf.effective_start_date AND
paf.effective_end_date
and rownum = 1;
SELECT prb.assignment_id, asg.business_group_id
FROM pay_run_balances prb,
per_all_assignments_f asg
WHERE prb.defined_balance_id = cp_def_bal_id
and asg.assignment_id = prb.assignment_id
and prb.source_id = TO_CHAR(p_src)
AND asg.organization_id = NVL(p_org_id, asg.organization_id)
AND asg.location_id = NVL(p_loc_id, asg.location_id)
AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
AND ((p_basis_type = 'OTHER'
AND asg.pay_basis_id IS NULL)
OR (p_basis_type = 'HOURLY'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis = 'HOURLY'))
OR (p_basis_type = 'SALARIED'
AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
FROM per_pay_bases pyb
WHERE pyb.pay_basis IN
('ANNUAL','MONTHLY','PERIOD')))
OR (p_basis_type IS NULL))
AND prb.effective_date BETWEEN p_start_date AND p_end_date
AND prb.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND ROWNUM = 1;
select business_group_id
from hr_all_organization_units
where organization_id = cp_gre_id;
select org_information5
into lv_gre_type
from hr_organization_information hoi
where hoi.org_information_context = 'Canada Employer Identification'
and hoi.organization_id = p_gre_id;