[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
16363434 to update serial number
16364978 accordingly and also xml_header
to display the sort option
meaning.
nkjaladi 26-Feb-2013 120.5 16364431 Modified procedure
generate_xml_header
jeisaac 05-Mar-2013 120.6 16405183 Modified procedure generate_xml.
Changed balance 'ISR Tax to Charge' to 'ISR Withheld'
*************************************************************************/
TYPE rec_entity_details IS RECORD
( user_entity_name ff_user_entities.user_entity_name%TYPE
,def_bal_id ff_user_entities.creator_id%TYPE
,bal_value NUMBER);
SELECT legislative_parameters
INTO leg_param
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT ppa.legislative_parameters,
pay_payrg_pkg.get_parameter('C_ST_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('PY_ID', ppa.legislative_parameters),
pay_payrg_pkg.get_parameter('O_ID', ppa.legislative_parameters),
ppa.start_date,
ppa.effective_date,
ppa.business_group_id
INTO leg_param,
l_consolidation_set_id,
l_payroll_id,
l_organization_id,
l_leg_start_date,
l_leg_end_date,
l_business_group_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
'SELECT distinct asg.person_id
FROM pay_payroll_actions ppa,
pay_payroll_actions pa1,
pay_assignment_actions act,
per_assignments_f asg,
pay_payrolls_f ppf
WHERE ppa.payroll_action_id = :payroll_action_id
'||l_consolidation_set_text||'
'||l_payroll_text||'
and pa1.effective_date between ppa.start_date
and ppa.effective_date
and pa1.effective_date between asg.effective_start_date
and asg.effective_end_date
and pa1.action_type in (''P'',''U'',''V'')
and pa1.payroll_action_id = act.payroll_action_id
and asg.assignment_id = act.assignment_id
and act.action_status = ''C''
and asg.organization_id = nvl('''||l_organization_id||''',
asg.organization_id)
/*and asg.location_id = nvl('''||l_location_id||''',
asg.location_id)
and asg.person_id = nvl('''||l_person_id||''',
asg.person_id)*/
and asg.business_group_id +0 = ppa.business_group_id
and asg.payroll_id = ppf.payroll_id
and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
and ppf.payroll_id >=0
order by asg.person_id';
SELECT pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id
,pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id
,pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id
,pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id
,pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id
,pay_payrg_pkg.get_parameter('PASID',ppa.legislative_parameters) assignment_set_id
,pay_payrg_pkg.get_parameter('L_E_ID',ppa.legislative_parameters) legal_employer_id
,ppa.start_date start_date
,ppa.effective_date effective_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT /*+ ORDERED */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa.action_type,
ppa.effective_date,
act.source_action_id,
nvl(ppa.start_date,ppa.effective_date)
FROM pay_payrolls_f ppf,
pay_payroll_actions ppa, /* pre-payments and reversals
payroll action id */
pay_assignment_actions act,
per_assignments_f paf
WHERE (c_payroll_id is NULL
OR ppa.payroll_id = c_payroll_id)
AND ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
AND ppa.effective_date >= c_start_date
AND nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
AND ppa.action_type IN ('P','U','V')
AND act.action_status = 'C'
AND act.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id +0 = c_business_group_id
AND paf.assignment_id = act.assignment_id
AND (c_tax_unit_id IS NULL
OR act.tax_unit_id = c_tax_unit_id)
AND act.tax_unit_id IN (SELECT tax_unit_id
FROM per_mx_gres_legal_employer_v
WHERE legal_employer_id = c_legal_emp_id)
AND (c_organization_id IS NULL
OR paf.organization_id = c_organization_id)
AND (c_person_id IS NULL
OR paf.person_id = c_person_id)
AND paf.person_id BETWEEN c_stperson AND c_endperson
AND paf.business_group_id +0 = c_business_group_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.payroll_id >= 0
ORDER BY act.assignment_action_id, act.assignment_id DESC;
SELECT /*+ ORDERED */
act.assignment_action_id,
act.assignment_id,
act.tax_unit_id,
ppa.action_type,
ppa.effective_date,
act.source_action_id,
nvl(ppa.start_date,ppa.effective_date)
FROM hr_assignment_set_amendments hasa ,
per_assignments_f paf ,
pay_assignment_actions act ,
pay_payroll_actions ppa ,
pay_payrolls_f ppf
WHERE (c_payroll_id IS NULL
OR ppa.payroll_id = c_payroll_id)
AND ppa.consolidation_set_id +0 = nvl(c_consolidation_set_id,
ppa.consolidation_set_id)
AND ppa.effective_date >= c_start_date
AND nvl(ppa.start_date,ppa.effective_date) <= c_effective_date
AND ppa.action_type in ('P','U','V')
AND act.action_status = 'C'
AND act.payroll_action_id = ppa.payroll_action_id
AND ppa.business_group_id +0 = c_business_group_id
AND paf.assignment_id = act.assignment_id
AND (c_tax_unit_id IS NULL
OR act.tax_unit_id = c_tax_unit_id)
AND act.tax_unit_id IN (SELECT tax_unit_id
FROM per_mx_gres_legal_employer_v
WHERE legal_employer_id = c_legal_emp_id)
AND (c_organization_id IS NULL
OR paf.organization_id = c_organization_id)
AND (c_person_id IS NULL
OR paf.person_id = c_person_id)
AND hasa.assignment_set_id = c_assignment_set_id
AND hasa.assignment_id = paf.assignment_id
AND paf.person_id BETWEEN c_stperson AND c_endperson
AND paf.business_group_id +0 = c_business_group_id
AND ppa.effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.payroll_id >= 0
ORDER BY act.assignment_action_id, act.assignment_id;
SELECT DISTINCT ppp.source_action_id
FROM pay_pre_payments ppp
WHERE ppp.assignment_action_id = cp_pre_pymt_action_id
ORDER BY ppp.source_action_id;
SELECT DISTINCT nvl(ppp.source_action_id,-999)
FROM pay_payment_information_v ppp
WHERE ppp.assignment_action_id = cp_pre_pymt_action_id
AND ppp.action_status = 'C'
ORDER BY 1;
SELECT ppa.effective_date,
ppa.action_type
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_pre_pymt_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type in ('R', 'Q', 'B');
SELECT assignment_action_id, ppa.action_type
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_pre_pymt_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ((paa.run_type_id IS NULL and paa.source_action_id IS NULL) OR
(paa.run_type_id IS NOT NULL AND paa.source_action_id IS NOT NULL
AND paa.run_type_id IN
(SELECT prt.run_type_id
FROM pay_run_types_f prt
WHERE prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
AND prt.legislation_code = 'MX'
AND ppa.effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date)
)
)
ORDER BY assignment_action_id DESC;
SELECT assignment_action_id, ppa.action_type
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_pre_pymt_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ((paa.run_type_id IS NULL AND paa.source_action_id IS NULL) OR
(paa.run_type_id IS NOT NULL AND paa.source_action_id IS NOT NULL
AND paa.run_type_id IN
(SELECT prt.run_type_id
FROM pay_run_types_f prt
WHERE prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
AND prt.legislation_code = 'MX'
AND ppa.effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date)
)
)
AND ppa.action_type = 'B'
ORDER BY assignment_action_id DESC;
SELECT multi_assignments_flag
FROM pay_payrolls_f ppf,
per_assignments_f paf
WHERE paf.payroll_id = ppf.payroll_id
AND cp_effective_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
AND cp_effective_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND paf.assignment_id = cp_assignment_id;
SELECT max(paa2.assignment_action_id)
FROM pay_assignment_actions paa2, -- assignment_actions for slave payroll runs.
pay_assignment_actions paa1, -- assignment_action for master payroll run
pay_run_Types_f prt,
pay_payroll_actions ppa,
pay_action_interlocks pai
WHERE pai.locking_action_id = cp_pre_pymt_action_id
AND pai.locked_action_id = paa1.assignment_action_id
AND paa1.source_action_id IS NULL -- master assignment_action
AND paa1.assignment_action_id = paa2.source_action_id
AND paa1.payroll_action_id = paa2.payroll_action_id
AND paa2.run_type_id = prt.run_type_id
AND prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
AND prt.legislation_code = 'MX'
AND paa2.payroll_action_id = ppa.payroll_action_id
AND ppa.effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date;
SELECT DISTINCT max(paa2.assignment_action_id)
FROM pay_assignment_actions paa2,
-- assignment_actions for slave payroll runs.
pay_assignment_actions paa1,
-- assignment_action for master payroll run
pay_run_Types_f prt,
pay_payroll_actions ppa,
pay_action_interlocks pai
WHERE pai.locking_action_id = cp_pre_pymt_action_id
AND pai.locked_action_id = paa1.assignment_action_id
AND paa1.source_action_id is null -- master assignment_action
AND paa1.assignment_action_id = paa2.source_action_id
AND paa1.payroll_action_id = paa2.payroll_action_id
AND paa2.run_type_id = prt.run_type_id
AND prt.shortname NOT IN ('REG_SEPPAY','NP_SEPPAY')
AND prt.legislation_code = 'MX'
AND paa2.payroll_action_id = ppa.payroll_action_id
AND ppa.effective_date BETWEEN prt.effective_start_date
AND prt.effective_end_date
GROUP BY paa1.assignment_action_id;
SELECT 'Y'
FROM pay_action_interlocks pai,
pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE pai.locking_action_id = cp_pre_pymt_action_id
AND paa.assignment_action_id = pai.locked_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND action_type = 'V';
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE assignment_id = cp_assig_id
AND payroll_action_id = pactid;
l_action_insert VARCHAR2(1);
SELECT legislative_parameters
INTO l_leg_param
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = serialno
WHERE assignment_action_id = lockingactid
AND tax_unit_id = greid;
INSERT INTO pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2,
attribute30)
values(pactid,
pactid,
lockingactid,
ln_master_action_id,
assignid,
'MX_PYR');
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
** we need to insert one action for each of the rows that we
** return from the cursor (i.e. one for each assignment/pre-payment source).
**************************************************************/
hr_nonrun_asact.insint(lockingactid,lockedactid);
UPDATE pay_assignment_actions
SET serial_number = serialno
WHERE assignment_action_id = lockingactid
AND tax_unit_id = greid;
INSERT INTO pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2,
attribute1,
attribute30)
VALUES(pactid,
pactid,
lockingactid,
runactid,
assignid,
lv_sep_check,
'MX_PYR');
SELECT pay_assignment_actions_s.nextval
INTO lockingactid
FROM dual;
UPDATE pay_assignment_actions
SET serial_number = serialno
WHERE assignment_action_id = lockingactid
AND tax_unit_id = greid;
INSERT INTO pay_us_rpt_totals
(session_id,
tax_unit_id,
location_id,
value1,
value2,
attribute30
)
VALUES(pactid,
pactid,
lockingactid,
lockedactid,
assignid,
'MX_PYR'
);
SELECT pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
pay_payrg_pkg.get_parameter('B_G_ID',ppa.legislative_parameters) business_group_id,
pay_payrg_pkg.get_parameter('RP_DM',ppa.legislative_parameters) dimension_id
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT balance_dimension_id
FROM pay_balance_dimensions
WHERE database_item_suffix = dimtype
AND legislation_code = 'MX';
SELECT fnd_number.canonical_to_number(value1)
FROM pay_us_rpt_totals
WHERE session_id = fnd_number.number_to_canonical(pactid)
AND location_id = fnd_number.number_to_canonical(assigactid)
AND attribute30 = 'MX_PYR';
SELECT DISTINCT fnd_number.canonical_to_number(value2)
FROM pay_us_rpt_totals
WHERE session_id = fnd_number.number_to_canonical(pactid)
AND location_id = fnd_number.number_to_canonical(assigactid)
AND attribute30 = 'MX_PYR';
SELECT DISTINCT fue_live.user_entity_name,
fue_live.creator_id,
0 tmp_bal_value
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
ff_user_entities fue_live
WHERE pbt.balance_name IN ('Earnings'
,'Deductions'
,'Net Pay'
,'Gross Earnings'
,'Tax Deductions'
,'ISR Withheld'
,'ISR Calculated'
,'ISR Exempt'
,'ISR Subsidy for Employment'
,'ISR Subsidy for Employment Paid'
,'Employer State Tax Withheld'
,'Social Security Quota EE'
,'Social Security Quota ER'
)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix = '_ASG_GRE_RUN'
AND pbd.legislation_code = pbt.legislation_code
AND pbt.legislation_code = 'MX'
AND fue_live.creator_id = pdb_call.defined_balance_id
AND fue_live.creator_type = 'B'
ORDER BY fue_live.user_entity_name;
SELECT DISTINCT fue_live.user_entity_name,
fue_live.creator_id,
0 tmp_bal_value
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
ff_user_entities fue_live
WHERE pbt.balance_name IN ('Earnings'
,'Deductions'
,'Net Pay'
,'Gross Earnings'
,'Tax Deductions'
,'ISR Withheld'
,'ISR Calculated'
,'ISR Exempt'
,'ISR Subsidy for Employment'
,'ISR Subsidy for Employment Paid'
,'Employer State Tax Withheld'
,'Social Security Quota EE'
,'Social Security Quota ER'
)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix IN ('_ASG_GRE_MTD','_ASG_GRE_RUN')
AND pbd.legislation_code = pbt.legislation_code
AND pbt.legislation_code = 'MX'
AND fue_live.creator_id = pdb_call.defined_balance_id
AND fue_live.creator_type = 'B'
ORDER BY fue_live.user_entity_name;
SELECT DISTINCT fue_live.user_entity_name,
fue_live.creator_id,
0 tmp_bal_value
FROM pay_defined_balances pdb_call,
pay_balance_dimensions pbd,
pay_balance_types pbt,
ff_user_entities fue_live
WHERE pbt.balance_name IN ('Earnings'
,'Deductions'
,'Net Pay'
,'Gross Earnings'
,'Tax Deductions'
,'ISR Withheld'
,'ISR Calculated'
,'ISR Exempt'
,'ISR Subsidy for Employment'
,'ISR Subsidy for Employment Paid'
,'Employer State Tax Withheld'
,'Social Security Quota EE'
,'Social Security Quota ER'
)
AND pbt.balance_type_id = pdb_call.balance_type_id
AND pdb_call.balance_dimension_id = pbd.balance_dimension_id
AND pbd.database_item_suffix IN ('_ASG_GRE_YTD','_ASG_GRE_RUN')
AND pbd.legislation_code = pbt.legislation_code
AND pbt.legislation_code = 'MX'
AND fue_live.creator_id = pdb_call.defined_balance_id
AND fue_live.creator_type = 'B'
ORDER BY fue_live.user_entity_name;
SELECT pap.full_name
,paf.assignment_number
FROM per_assignments_f paf,
per_all_people_f pap
WHERE paf.assignment_id = assigid
AND pap.person_id = paf.person_id;
SELECT ptp.start_date
,ptp.end_date
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,per_time_periods ptp
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND ptp.time_period_id = ppa.time_period_id
AND paa.assignment_action_id = asg_action_id;
SELECT ppf.payroll_name
,pcs.consolidation_set_name
FROM pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_payrolls_f ppf
,pay_consolidation_sets pcs
WHERE ppa.payroll_action_id = paa.payroll_action_id
AND ppa.payroll_id = ppf.payroll_id
AND ppa.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND ppf.consolidation_set_id = pcs.consolidation_set_id
AND paa.assignment_action_id = asg_action_id;
SELECT tax_unit_id
INTO ln_tax_unit_id
FROM pay_assignment_actions paa
WHERE paa.payroll_Action_id = ln_payroll_act_id
AND assignment_action_id = ln_curr_assign_act_id;
INSERT INTO pay_us_rpt_totals (session_id
,business_group_id
,tax_unit_id
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,value1
,attribute30
)
VALUES (ln_payroll_act_id
,ln_business_group_id
,ln_tax_unit_id
,ln_payroll_act_id
,ln_curr_assign_act_id
,ln_assig_id
,ln_assignment_act_id
,payroll_reg_xml_tbl(i).name
,payroll_reg_xml_tbl(i).value
,'MX_PYR'
);
payroll_reg_xml_tbl.DELETE;
SELECT pay_payrg_pkg.get_parameter('PY_ID',ppa.legislative_parameters) payroll_id,
pay_payrg_pkg.get_parameter('C_ST_ID',ppa.legislative_parameters) consolidation_set_id,
pay_payrg_pkg.get_parameter('T_U_ID',ppa.legislative_parameters) tax_unit_id,
pay_payrg_pkg.get_parameter('L_E_ID',ppa.legislative_parameters) legal_employer_id,
pay_payrg_pkg.get_parameter('O_ID',ppa.legislative_parameters) organization_id,
pay_payrg_pkg.get_parameter('PASID',ppa.legislative_parameters) assignment_set_id,
pay_payrg_pkg.get_parameter('P_S1', ppa.legislative_parameters) sort1,
pay_payrg_pkg.get_parameter('RP_DM',ppa.legislative_parameters) dimension_id,
ppa.start_date start_date,
ppa.effective_date effective_date
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = pactid;
SELECT payroll_name
FROM pay_all_payrolls_f
WHERE payroll_id = cp_payroll_id;
SELECT consolidation_set_name
FROM pay_consolidation_sets
WHERE consolidation_set_id = cp_consolidation_set_id;
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id = cp_tax_unit_id;
SELECT hou.name
FROM hr_organization_units hou,
per_gen_hierarchy_nodes greghn,
per_gen_hierarchy_nodes leghn
WHERE greghn.entity_id = fnd_number.number_to_canonical(cp_tax_unit_id)
AND leghn.hierarchy_node_id = greghn.parent_hierarchy_node_id
AND hou.organization_id = leghn.entity_id;
SELECT assignment_set_name
FROM hr_assignment_sets
WHERE assignment_set_id = cp_assignment_set_id;
SELECT flv.meaning
FROM fnd_lookup_types flt ,
fnd_lookup_values flv
WHERE flt.lookup_type = 'MX_DIMENSION'
AND flt.lookup_type = flv.lookup_type
AND flv.lookup_code = cp_dimension_type
AND flv.enabled_flag = 'Y'
AND flv.language = USERENV('LANG'); -- #16364978 Added this condition
SELECT flv.meaning
FROM fnd_lookup_types flt ,
fnd_lookup_values flv
WHERE flt.lookup_type = 'PAY_MX_REG_SORT_CODE'
AND flt.lookup_type = flv.lookup_type
AND flv.lookup_code = cp_sort_option
AND flv.enabled_flag = 'Y'
AND flv.language = USERENV('LANG');
SELECT attribute5 balance_name
,sum(value1) total
FROM pay_us_rpt_totals
WHERE attribute1 = fnd_number.number_to_canonical(c_payroll_action_id)
AND attribute30 = 'MX_PYR'
GROUP BY attribute5;
payroll_reg_xml_tbl.delete;
payroll_reg_xml_tbl.DELETE;
SELECT get_parameter('REMOVE_ACT', pa1.legislative_parameters)
INTO l_remove_act
FROM pay_payroll_actions pa1
WHERE pa1.payroll_action_id = ln_payroll_action_id;
DELETE FROM pay_us_rpt_totals
WHERE session_id = ln_payroll_action_id
AND attribute30 = 'MX_PYR';
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id = cp_tax_unit_id;
SELECT attribute5 balance_name
,sum(value1) total
FROM pay_us_rpt_totals
WHERE tax_unit_id = c_tax_unit_id
AND attribute1 = fnd_number.number_to_canonical(c_payroll_action_id)
AND attribute30 = 'MX_PYR'
GROUP BY attribute5;
payroll_reg_xml_tbl.delete;
payroll_reg_xml_tbl.DELETE;
SELECT pay_payrg_pkg.get_parameter('P_S1', ppa.legislative_parameters)
INTO l_sort_1
FROM pay_payroll_actions ppa
WHERE ppa.payroll_action_id = payactid;
'SELECT paa.rowid
FROM pay_assignment_actions paa,
pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payactid
and paa.payroll_action_id = ppa.payroll_action_id
order by(pay_mx_payreg_pkg.sort_option (
''GRE'',
paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id)),
(decode('''||l_sort_1||''',
null, null,
pay_mx_payreg_pkg.sort_option (
'''||l_sort_1||''',
paa.assignment_id,
ppa.effective_date,
paa.tax_unit_id))),
(SELECT ppa2.effective_date
FROM pay_payroll_actions ppa2,
pay_assignment_actions paa2
WHERE paa2.assignment_action_id = to_number(substr(paa.serial_number,2))
and paa2.payroll_action_id = ppa2.payroll_action_id
and ppa2.action_type in (''R'', ''Q'', ''V'', ''B'')
)
for update of paa.assignment_id';
SELECT hou1.name
INTO return_val
FROM hr_all_organization_units hou1
WHERE hou1.organization_id = c_tax_unit_id
AND rownum = 1;
SELECT DECODE(c_option_name,
'Organization',hou.name,
'Location',loc.location_code,
NULL)
INTO return_val
FROM hr_all_organization_units hou,
hr_locations_all loc,
per_assignments_f paf
WHERE paf.assignment_id = c_assignment_id
AND c_effective_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND hou.organization_id = paf.organization_id
AND loc.location_id = paf.location_id
AND rownum = 1;
SELECT ppf.full_name
INTO return_val
FROM per_all_assignments_f paf
,per_all_people_f ppf
WHERE paf.person_id = ppf.person_id
AND c_effective_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND c_effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND paf.assignment_id = c_assignment_id
AND rownum = 1;
SELECT ppf.payroll_name||' '||papf.full_name
INTO return_val
FROM per_all_assignments_f paf
,pay_payrolls_f ppf
,per_all_people_f papf
WHERE paf.payroll_id = ppf.payroll_id
AND c_effective_date BETWEEN
paf.effective_start_date AND paf.effective_end_date
AND c_effective_date BETWEEN
ppf.effective_start_date AND ppf.effective_end_date
AND paf.assignment_id = c_assignment_id
AND paf.person_id = papf.person_id
AND c_effective_date BETWEEN
papf.effective_start_date AND papf.effective_end_date;
SELECT hou1.name
INTO return_val
FROM hr_all_organization_units hou1
WHERE hou1.organization_id = c_tax_unit_id
AND rownum = 1;