[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'LEGAL_EMPLOYER_NAME')
,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'PENSION_INS_NUM')
,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'MONTH_RPT')
,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'YEAR_RPT')
,PAY_FI_ARCHIVE_LTFA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
,effective_date
,business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT o1.NAME
FROM hr_organization_units o1
, hr_organization_information hoi1
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_pension_provider_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'FR_PENSION' ;
SELECT o1.name , hoi2.ORG_INFORMATION1
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi3.ORG_INFORMATION4 , hoi3.ORG_INFORMATION6, hoi3.ORG_INFORMATION8
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
, hr_organization_information hoi3
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS'
AND o1.organization_id =hoi3.organization_id
AND hoi3.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
AND hoi3.org_information6=csr_v_pension_ins_num
AND csr_v_effective_date BETWEEN fnd_date.canonical_to_date(hoi3.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi3.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
SELECT hoi2.ORG_INFORMATION2
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_local_unit_id
AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS'
AND hoi2.org_information1=csr_v_pension_ins_num ;
SELECT hoi2.ORG_INFORMATION6 , hoi2.ORG_INFORMATION8
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_legal_emp_id
AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
AND hoi1.org_information_context = 'CLASS'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
AND hoi2.org_information3=csr_v_pension_type
AND hoi2.org_information4=csr_v_pension_provider
AND csr_v_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1) AND
nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
SELECT hoi2.ORG_INFORMATION3
FROM hr_organization_units o1
, hr_organization_information hoi1
, hr_organization_information hoi2
WHERE o1.business_group_id =l_business_group_id
AND hoi1.organization_id = o1.organization_id
AND hoi1.organization_id = csr_v_pension_provider_id
AND hoi1.org_information_context = 'CLASS'
AND hoi1.org_information1 = 'FR_PENSION'
AND o1.organization_id =hoi2.organization_id
AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_DEPARTMENT_CODES'
AND hoi2.ORG_INFORMATION1 = csr_v_legal_emp_id
AND hoi2.ORG_INFORMATION2 = csr_v_Local_unit_id;
p_sql := 'SELECT DISTINCT person_id
FROM per_people_f ppf
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = :payroll_action_id
AND ppa.business_group_id = ppf.business_group_id
ORDER BY ppf.person_id';
SELECT count(*)
INTO l_count
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT DETAILS'
AND action_information1 = 'PYFILTFA'
AND action_context_id = p_payroll_action_id;
SELECT MAX(ASSIGNMENT_ACTION_ID)
INTO g_lu_assignment_action_id
FROM pay_run_balances
WHERE local_unit_id = g_local_unit_id
AND organization_id =g_pension_provider
AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
SELECT MAX(ASSIGNMENT_ACTION_ID)
INTO g_le_assignment_action_id
FROM pay_run_balances
WHERE tax_unit_id =g_legal_employer_id
AND organization_id =g_pension_provider
AND TO_CHAR(effective_date,'MMYYYY')=l_month||l_year ;
SELECT act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa
,pay_payroll_actions appa
,pay_payroll_actions appa2
,pay_assignment_actions act
,pay_assignment_actions act1
,pay_action_interlocks pai
,per_all_assignments_f as1
,hr_soft_coding_keyflex hsck
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.person_id = pap.person_id
AND pap.per_information24 = p_pension_ins_num
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND hsck.SOFT_CODING_KEYFLEX_ID=as1.SOFT_CODING_KEYFLEX_ID
AND hsck.segment2 = p_local_unit_id
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
ORDER BY act.assignment_id;
SELECT act.assignment_id assignment_id,
act.assignment_action_id run_action_id,
act1.assignment_action_id prepaid_action_id
FROM pay_payroll_actions ppa,
pay_payroll_actions appa,
pay_payroll_actions appa2,
pay_assignment_actions act,
pay_assignment_actions act1,
pay_action_interlocks pai,
per_all_assignments_f as1
, per_all_people_f pap
WHERE ppa.payroll_action_id = p_payroll_action_id
AND appa.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
AND as1.person_id BETWEEN p_start_person
AND p_end_person
AND appa.action_type IN ('R','Q')
-- Payroll Run or Quickpay Run
AND act.payroll_action_id = appa.payroll_action_id
AND act.source_action_id IS NULL -- Master Action
AND as1.assignment_id = act.assignment_id
AND as1.person_id = pap.person_id
AND pap.per_information24 = p_pension_ins_num
AND ppa.effective_date BETWEEN as1.effective_start_date
AND as1.effective_end_date
AND ppa.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND act.action_status = 'C' -- Completed
AND act.assignment_action_id = pai.locked_action_id
AND act1.assignment_action_id = pai.locking_action_id
AND act1.action_status = 'C' -- Completed
AND act1.payroll_action_id = appa2.payroll_action_id
AND appa2.action_type IN ('P','U')
AND appa2.effective_date BETWEEN l_canonical_start_date
AND l_canonical_end_date
-- Prepayments or Quickpay Prepayments
AND act.TAX_UNIT_ID = act1.TAX_UNIT_ID
AND act.TAX_UNIT_ID = p_legal_employer_id
ORDER BY act.assignment_id;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pap.first_name, pap.last_name, pap.pre_name_adjunct, pap.national_identifier , pap.person_id , pac.assignment_id
FROM
pay_assignment_actions pac,
per_all_assignments assign,
per_all_people pap
WHERE pac.assignment_action_id = p_asg_act_id
AND assign.assignment_id = pac.assignment_id
AND assign.person_id = pap.person_id
AND pap.per_information_category = 'FI';
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';
SELECT 1
INTO l_flag
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYFILTFA'
AND action_information2 = 'PER'
AND action_context_id = p_assignment_action_id;
SELECT COUNT(*) emp_count , sum (FND_NUMBER.CANONICAL_TO_NUMBER(action_information7) + FND_NUMBER.CANONICAL_TO_NUMBER(action_information8) + FND_NUMBER.CANONICAL_TO_NUMBER(action_information9)) emp_sal
,sum (FND_NUMBER.CANONICAL_TO_NUMBER(action_information11)) pension , sum (FND_NUMBER.CANONICAL_TO_NUMBER(action_information12)) emp_pension
FROM pay_action_information
WHERE action_information_category = 'EMEA REPORT INFORMATION'
AND action_information1 = 'PYFILTFA'
AND action_information2 = 'PER'
AND action_information10 = p_payroll_action_id ;
SELECT ue.creator_id
FROM ff_user_entities ue,
ff_database_items di
WHERE di.user_name = csr_v_Balance_Name
AND ue.user_entity_id = di.user_entity_id
AND ue.legislation_code = 'FI'
AND ue.business_group_id is NULL
AND ue.creator_type = 'B';