[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select payroll_id, effective_date
From Pay_Payroll_Actions
Where Payroll_Action_Id = p_payroll_action_id;
SELECT NVL(MAX(action_information2) + 1,'60')
FROM PAY_ACTION_INFORMATION
WHERE action_information_category = p_action_information_category
AND action_information1 = p_payroll_id
AND action_information3 = p_transfer_info_type
AND action_information4 = p_transfer_total_amount
AND action_information5 = p_transfer_info_disposal_date
AND action_information9 = p_transfer_payer_cvr_no
AND to_date(action_information11,'RRRRMMDD') BETWEEN TO_DATE(l_QTR_START,'RRRRMMDD') AND TO_DATE(l_QTR_END,'RRRRMMDD');
Select payroll_id
From Pay_Payroll_Actions
Where Payroll_Action_Id = p_payroll_action_id;
SELECT max(paa.ASSIGNMENT_ACTION_ID)
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status IN ('C','S') -- 10229494
AND ppa.action_type IN('R','Q','I','B','V','P','U');
SELECT pdb.defined_balance_id
FROM pay_balance_dimensions pbd,
pay_balance_types pbt,
pay_defined_balances pdb
WHERE pbd.dimension_name = p_dimension_name
AND pbd.business_group_id IS NULL
AND pbd.legislation_code = 'DK'
AND pbt.balance_name = p_balance_name
AND pbt.business_group_id IS NULL
AND pbt.legislation_code = 'DK'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pdb.business_group_id IS NULL
AND pdb.legislation_code = 'DK';
SELECT legislative_parameters
FROM PAY_PAYROLL_ACTIONS
WHERE payroll_action_id = p_payroll_action_id;
select meaning
from hr_lookups
where lookup_type = p_lookup_type
and lookup_code = p_lookup_code
and enabled_flag ='Y';
SELECT max(paa.ASSIGNMENT_ACTION_ID)
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN('P','U');
SELECT max(ppa.DATE_EARNED)
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN('P','U');
SELECT max(nvl(ppa.date_earned,ppar.date_earned))
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_interlocks pail
, pay_payroll_actions ppar
, pay_assignment_actions paar
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paar.assignment_action_id = pail.locked_action_id
AND pail.locking_action_id = paa.assignment_action_id
AND paar.payroll_action_id = ppar.payroll_action_id
AND ppa.action_type IN('P','U')
AND ppar.action_type IN('Q','R');
SELECT ppp.assignment_action_id
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_interlocks pai
, pay_pre_payments ppp
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'M'
AND ppa.action_status = 'C'
AND paa.action_status = 'C'
AND paa.pre_payment_id = ppp.pre_payment_id
AND pai.locking_action_id = paa.assignment_action_id
AND pai.locked_action_id = ppp.assignment_action_id;
SELECT pdb.defined_balance_id
FROM pay_defined_balances pdb
,pay_balance_types pbt
,pay_balance_dimensions pbd
WHERE pbt.legislation_code='DK'
AND pbt.balance_name = p_balance_name
AND pbd.legislation_code = 'DK'
--AND pbd.database_item_suffix = '_PAYMENTS'
AND pbd.database_item_suffix = '_PP_PAYMENTS'
AND pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id;
SELECT ppa.date_earned
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_action_interlocks pai
WHERE paa.assignment_id = p_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type = 'M'
AND pai.locking_action_id = paa.assignment_action_id
AND pai.locked_action_id = get_ass_action_context(p_assignment_id);
SELECT '1'
FROM per_addresses pad
/* Modified for bug fix 4593682 */
, per_all_people_f pap
, pay_payroll_actions ppa
WHERE /*pad.person_id = p_person_id*/
pad.person_id (+)= pap.person_id
AND pad.primary_flag = 'Y' --9403004
AND ppa.effective_date BETWEEN nvl(pad.date_from,ppa.effective_date) AND nvl(pad.date_to,to_date('31-12-4712','dd-mm-rrrr')) --9403004
AND pap.person_id = p_person_id
AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
/* Modified for bug fix 7664874 */
AND ppa.payroll_action_id=pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date;
SELECT '1'
FROM pay_run_results prr1
, pay_run_result_values prrv1
, pay_run_result_values prrv3
, pay_element_types_f pet1
, pay_input_values_f piv1
, pay_input_values_f piv3
, pay_run_results prr2
, pay_run_result_values prrv2
, pay_run_result_values prrv4
, pay_element_types_f pet2
, pay_input_values_f piv2
, pay_input_values_f piv4
, pay_assignment_actions paa
, pay_payroll_actions ppa
, pay_element_entries_f pee
WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND prr1.ELEMENT_TYPE_ID = pet1.ELEMENT_TYPE_ID
AND prrv1.RUN_RESULT_ID = prr1.RUN_RESULT_ID
AND prrv3.RUN_RESULT_ID = prr1.RUN_RESULT_ID
AND pee.ELEMENT_ENTRY_ID = prr1.ELEMENT_ENTRY_ID
AND pet1.element_name = 'Retro Pension'
AND pet1.legislation_code ='DK'
AND piv1.ELEMENT_TYPE_ID = pet1.element_type_id
AND piv1.NAME ='Pay Value'
AND prrv1.input_value_id = piv1.input_value_id
AND piv3.ELEMENT_TYPE_ID = pet1.element_type_id
AND piv3.NAME ='Third Party Payee'
AND prrv3.input_value_id = piv3.input_value_id
AND prrv3.RESULT_VALUE = p_pp_id
AND prr2.ELEMENT_TYPE_ID =pet2.ELEMENT_TYPE_ID
AND prrv2.RUN_RESULT_ID = prr2.RUN_RESULT_ID
AND prrv4.RUN_RESULT_ID = prr2.RUN_RESULT_ID
AND prrv4.RESULT_VALUE = prrv3.RESULT_VALUE
AND pet2.element_name = 'Retro Employer Pension'
AND pet2.legislation_code ='DK'
AND piv2.ELEMENT_TYPE_ID = pet2.element_type_id
AND piv2.NAME ='Pay Value'
AND prrv2.input_value_id = piv2.input_value_id
AND piv4.ELEMENT_TYPE_ID = pet2.element_type_id
AND piv4.NAME ='Third Party Payee'
AND prrv4.input_value_id = piv4.input_value_id
AND prrv4.RESULT_VALUE = p_pp_id
AND prr1.assignment_action_id = paa.assignment_action_id
AND prr1.assignment_action_id=prr2.assignment_action_id
AND prr1.start_date = prr2.start_date
AND prr1.end_date = prr2.end_date
AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
AND paa.assignment_id = p_assignment_id
AND ppa.effective_date BETWEEN pet1.effective_start_date and pet1.effective_end_date
AND ppa.effective_date BETWEEN pet2.effective_start_date and pet2.effective_end_date
AND ppa.effective_date BETWEEN piv1.effective_start_date and piv1.effective_end_date
AND ppa.effective_date BETWEEN piv2.effective_start_date and piv2.effective_end_date
AND ppa.effective_date BETWEEN piv3.effective_start_date and piv3.effective_end_date
AND ppa.effective_date BETWEEN piv4.effective_start_date and piv4.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date and pee.effective_end_date;
SELECT '1'
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_element_entries_f pee1
, pay_element_types_f pet
, pay_element_entries_f pee2
, hr_organization_units hou /*bug fix 4551283*/
/* Added for Pension changes */
, pay_input_values_f pivf
, pay_element_entry_values_f peev1
, pay_element_entry_values_f peev2
WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.payroll_action_id = ppa.payroll_action_id
AND pee1.assignment_id = paa.assignment_id
AND pet.element_name = 'Pension'
AND pet.legislation_code ='DK'
AND pee1.entry_type ='E'
AND pee1.element_type_id = pet.element_type_id
AND pee2.assignment_id = paa.assignment_id
AND pee2.entry_type ='E'
AND pee2.element_type_id = pet.element_type_id
/* Added for Pension changes -start */
AND pivf.element_type_id = pet.element_type_id
AND pivf.name= 'Third Party Payee'
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND peev1.input_value_id = pivf.input_value_id
AND peev1.element_entry_id = pee1.element_entry_id
AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
AND peev2.input_value_id = pivf.input_value_id
AND peev2.element_entry_id = pee2.element_entry_id
AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
/* Added for Pension changes -end */
AND paa.assignment_id = p_assignment_id
AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
AND pee1.effective_start_date >= ppa.start_date
AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
AND hou.organization_id = pay_magtape_generic.get_parameter_value('TRANSFER_LE_ID') /*bug fix 4551283*/
AND ppa.effective_date BETWEEN hou.date_from AND nvl(hou.date_to, ppa.effective_date) /*bug fix 4551283*/
GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id,hou.name;
SELECT '1'
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_element_entries_f pee1
, pay_element_types_f pet
, pay_element_entries_f pee2
/* Added for Pension changes */
, pay_input_values_f pivf
, pay_element_entry_values_f peev1
, pay_element_entry_values_f peev2
WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.payroll_action_id = ppa.payroll_action_id
AND pee1.assignment_id = paa.assignment_id
AND pet.element_name = 'Pension'
AND pet.legislation_code ='DK'
AND pee1.entry_type ='E'
AND pee1.element_type_id = pet.element_type_id
AND pee2.assignment_id = paa.assignment_id
AND pee2.entry_type ='E'
AND pee2.element_type_id = pet.element_type_id
/* Added for Pension changes -start */
AND pivf.element_type_id = pet.element_type_id
AND pivf.name= 'Third Party Payee'
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND peev1.input_value_id = pivf.input_value_id
AND peev1.element_entry_id = pee1.element_entry_id
AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
AND peev2.input_value_id = pivf.input_value_id
AND peev2.element_entry_id = pee2.element_entry_id
AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
/* Added for Pension changes -end */
AND paa.assignment_id = p_assignment_id
AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
AND 1=2
GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
SELECT '1'
FROM pay_payroll_actions ppa
, pay_assignment_actions paa
, pay_element_entries_f pee1
, pay_element_types_f pet
, pay_element_entries_f pee2
/* Added for Pension changes */
, pay_input_values_f pivf
, pay_element_entry_values_f peev1
, pay_element_entry_values_f peev2
WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
AND paa.payroll_action_id = ppa.payroll_action_id
AND pee1.assignment_id = paa.assignment_id
AND pet.element_name = 'Pension'
AND pet.legislation_code ='DK'
AND pee1.entry_type ='E'
AND pee1.element_type_id = pet.element_type_id
AND pee2.assignment_id = paa.assignment_id
AND pee2.entry_type ='E'
AND pee2.element_type_id = pet.element_type_id
/* Added for Pension changes -start */
AND pivf.element_type_id = pet.element_type_id
AND pivf.name= 'Third Party Payee'
AND ppa.effective_date BETWEEN pivf.effective_start_date AND pivf.effective_end_date
AND peev1.input_value_id = pivf.input_value_id
AND peev1.element_entry_id = pee1.element_entry_id
AND peev1.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
AND peev2.input_value_id = pivf.input_value_id
AND peev2.element_entry_id = pee2.element_entry_id
AND peev2.screen_entry_value = PAY_DK_PAYMENT_PROCESS_PKG.get_pension_provider(pay_magtape_generic.get_parameter_value('TRANSFER_RECEIVER_NAME'))
/* Added for Pension changes -end */
AND pee2.effective_start_date < ppa.start_date
AND paa.assignment_id = p_assignment_id
AND pay_magtape_generic.get_parameter_value('TRANSFER_INFO_TYPE') NOT IN (300, 400, 800, 900)
AND ppa.effective_date BETWEEN pet.effective_start_date and pet.effective_end_date
AND ppa.effective_date BETWEEN pee1.effective_start_date and pee1.effective_end_date
AND 1=2
GROUP BY pee1.effective_start_date,paa.assignment_id,pee1.element_entry_id,pet.element_type_id;
SELECT to_char(hou.organization_id)
FROM hr_organization_units hou
WHERE hou.name = p_org_name;
SELECT hoi2.ORG_INFORMATION2 TAX_RC
,hoi2.ORG_INFORMATION3 AMB_RC
,hoi2.ORG_INFORMATION4 SP_RC
,hoi2.ORG_INFORMATION5 HOL_DAYS_RC
FROM HR_ORGANIZATION_UNITS hou
, HR_ORGANIZATION_INFORMATION hoi1
, HR_ORGANIZATION_INFORMATION hoi2
WHERE hou.business_group_id = p_business_group_id
and hoi1.organization_id = hou.organization_id
and hoi1.organization_id = p_business_group_id
and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
and hoi1.org_information1 = 'HR_BG'
and hoi1.ORG_INFORMATION2 = 'Y'
and hoi2.ORG_INFORMATION_CONTEXT='DK_IDENTIFICATION_CODES'
and hoi2.organization_id = hoi1.organization_id
and p_effective_date BETWEEN hou.DATE_FROM and nvl(hou.DATE_TO, p_effective_date);
SELECT nvl(screen_entry_value,0)
FROM pay_element_entry_values_f
WHERE element_entry_id = p_ee_id
AND input_value_id = p_iv_id
AND p_eff_date BETWEEN effective_start_date and effective_end_date ;
SELECT input_value_id
FROM pay_input_values_f
WHERE name = p_iv_name
AND element_type_id = p_ele_type_id
AND legislation_code ='DK'
AND p_eff_date BETWEEN effective_start_date and effective_end_date ;
SELECT PRL_INFORMATION1
FROM pay_payrolls_f ppf
WHERE PAYROLL_ID = p_payroll_id
AND p_date_earned BETWEEN ppf.EFFECTIVE_START_DATE AND ppf.EFFECTIVE_END_DATE;
SELECT PAYROLL_ID
FROM PAY_PAYROLL_ACTIONS ppa
WHERE payroll_action_id = p_payroll_action_id;
Select
TPTYPE.number_per_fiscal_year
from
pay_payroll_actions PACTION
, per_time_periods TPERIOD
, per_time_period_types TPTYPE
where PACTION.payroll_action_id = p_payroll_action_id
and TPERIOD.payroll_id = PACTION.payroll_id
and p_date_earned between TPERIOD.start_date and TPERIOD.end_date
and TPTYPE.period_type = TPERIOD.period_type;
SELECT NAME
FROM
PER_COLLECTIVE_AGREEMENTS PCG,
PER_ALL_ASSIGNMENTS_F PAAF
WHERE
PAAF.COLLECTIVE_AGREEMENT_ID = PCG.COLLECTIVE_AGREEMENT_ID
AND PAAF.BUSINESS_GROUP_ID = PCG.BUSINESS_GROUP_ID
AND P_DATE_EARNED BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND P_DATE_EARNED BETWEEN PCG.START_DATE AND NVL(PCG.END_DATE, TO_DATE('31-12-4712','DD-MM-RRRR'))
AND PAAF.ASSIGNMENT_ID = P_ASG_ID
AND PAAF.BUSINESS_GROUP_ID = P_BG_ID;