[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 pay_assignment_actions_s.NEXTVAL
FROM dual;
SELECT to_date('01-04-'|| to_char(to_number(pay_core_utils.get_parameter('REPORTING_YEAR',legislative_parameters))
-1),'DD-MM-YYYY') FINANCIAL_YEAR_START
,to_date('31-03-'||pay_core_utils.get_parameter('REPORTING_YEAR',legislative_parameters),'DD-MM-YYYY')
FINANCIAL_YEAR_END
,pay_core_utils.get_parameter('BUSINESS_GROUP_ID',legislative_parameters) BUSINESS_GROUP_ID
,pay_core_utils.get_parameter('LEGAL_ENTITY_ID',legislative_parameters) LEGAL_ENTITY_ID
,pay_core_utils.get_parameter('REPORTING_YEAR',legislative_parameters) REPORTING_YEAR
FROM pay_payroll_actions
WHERE payroll_action_id = c_payroll_Action_id;
SELECT distinct pac.assignment_id
FROM pay_payroll_actions ppa,
pay_payrolls_f pay, /* Added for Bug 3916743 - performance fix. */
pay_assignment_actions pac
WHERE ppa.action_type in ('R','B','I','Q')
AND ppa.payroll_action_id = pac.payroll_action_id
AND ppa.business_group_id = c_business_group_id
AND pac.tax_unit_id = c_legal_entity_id
AND ppa.effective_date between c_fin_start_date and c_fin_end_date
AND ppa.action_status = 'C'
AND pac.action_status = 'C'
AND ppa.payroll_id = pay.payroll_id /* This and next line added for Bug 3916743 */
AND pay.business_group_id = c_business_group_id
ORDER BY pac.assignment_id;
'HK_IR56B_ARCHIVE' and action status with 'C' and action type with 'X'.Selection of assignments ids
is now done through a cursor check_run and hence that part of the query has been removed from process
assignments */
/*Bug No : 2778848 - Modified cursor process_assignments, the select statement is changed to
eliminate the terminated employee details, by including per_periods_of_service and checking
for the existance of assignment as of 31 of march. Since terminated employees are not selected, the
check for whether IR56F or IR56G is run becomes invalid hence this check is removed. */
CURSOR process_assignments
(c_payroll_action_id in pay_payroll_actions.payroll_action_id%TYPE,
c_start_person_id in per_all_people_f.person_id%TYPE,
c_end_person_id in per_all_people_f.person_id%TYPE,
c_fin_start_date in date,
c_fin_end_date in date,
c_business_group_id pay_payroll_actions.business_group_id%TYPE,
c_legal_entity_id hr_organization_units.organization_id%TYPE,
c_reporting_year NUMBER)
IS
SELECT DISTINCT a.assignment_id assignment_id
FROM per_assignments_f a,
pay_payroll_actions pa,
per_periods_of_service pps
WHERE pa.payroll_action_id = c_payroll_action_id
AND a.person_id BETWEEN c_start_person_id and c_end_person_id
AND a.business_group_id = pa.business_group_id
AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') between a.effective_start_date and a.effective_end_date
AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') between pps.date_start
and NVL(pps.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY'))
AND NVL(pps.actual_termination_date, TO_DATE('31-12-4712', 'DD-MM-YYYY')) <> TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY') -- Bug: 3055512
AND pps.period_of_service_id = a.period_of_service_id
AND pps.person_id = a.person_id
AND NOT EXISTS -- don't produce if they've had ir56b report produced.
(SELECT NULL
FROM pay_action_interlocks pai,
pay_payroll_actions ppai,
pay_payroll_actions ppaa,
pay_assignment_actions paa
WHERE paa.assignment_id = a.assignment_id
AND ppaa.action_type='X'
AND ppaa.report_type = 'HK_IR56B_ARCHIVE'
AND ppai.action_type='X'
AND ppai.action_status='C'
AND ppaa.action_status='C'
AND ppai.report_type = 'HK_IR56B_REPORT'
AND paa.assignment_action_id = pai.locking_action_id
AND ppai.payroll_action_id = paa.payroll_action_id
AND ppaa.payroll_action_id = pay_core_utils.get_parameter('ARCHIVE_ACTION_ID',
ppai.legislative_parameters)
/* Start of Bug No : 3059915 */
AND ppaa.business_group_id = c_business_group_id
AND ppaa.business_group_id = ppai.business_group_id
AND to_char(ppaa.effective_date,'YYYY') = c_reporting_year
AND ppaa.effective_date = ppai.effective_date
/* End of Bug No : 3059915 */
AND pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppaa.legislative_parameters) =
c_legal_entity_id
)
;
SELECT DISTINCT
papf.national_identifier hk_id_card_no,
DECODE(papf.marital_status, 'M',
DECODE(sex, 'F', NVL(previous_last_name, last_name), last_name)
,last_name) last_name,
TRIM(papf.first_name||' '||papf.middle_names) other_name,
papf.per_information5 chinese_full_name, /* Bug 2945151 */
SUBSTR(papf.sex,1,1) sex,
DECODE(papf.marital_status,'M',2,1) marital_status,
papf.per_information1
||DECODE(papf.per_information2,NULL, NULL, ' '
|| ftv.territory_short_name) passport_info,
papf.employee_number employee_number,
pad.address_type address_type, /* Start of Bug No : 2701921, 4396794 */
substrb(decode(pad.address_line1,null,'', pad.address_line1 ||
decode(pad.address_line2,null,decode(pad.address_line3,null,'',', '),', ')) ||
decode(pad.address_line2,null,'', pad.address_line2 || decode(pad.address_line3,null,'',', ')) ||
pad.address_line3,1,240) address_lines, /* End of Bug : 2701921,4396794 */
pad.town_or_city town_or_city,
pad.country country,
DECODE(pad.style, 'HK', ','||hrl.meaning, NULL) area_code,
DECODE(pad.style, 'HK', hrl.meaning, NULL) area_code_res,/*Added for bug 2762276 to store residential address area code*/
paei.aei_information1 capacity_employed,
hsck.segment2 principal_emp_name,
TO_CHAR(GREATEST(TO_DATE('01/04/'||
TO_CHAR(TO_NUMBER(c_reporting_year)-1),'DD/MM/YYYY')
,pps.date_start), 'YYYYMMDD') employment_start_date,
c_reporting_year||'0331' employment_end_date,
papf.per_information9 employee_tfn,
hsck.segment5 remarks,
pcr.primary_contact_flag primary_contact_flag,
NVL(pcr.contact_person_id,0) person_id, -- used in spouse cursor
pcr.contact_type contact_type, -- used in spouse cursor
pcr.date_start date_start
FROM per_people_f papf,
per_assignments_f paaf,
fnd_territories_tl ftv, /* Bug No : 3059915 */
per_contact_relationships pcr,
per_addresses pad,
per_assignment_extra_info paei,
per_periods_of_service pps,
hr_soft_coding_keyflex hsck,
hr_lookups hrl
WHERE paaf.person_id = papf.person_id
AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
BETWEEN paaf.effective_start_date and paaf.effective_end_date
AND papf.effective_end_date = NVL(pps.actual_termination_date,TO_DATE('31-12-4712','dd-mm-yyyy')) /* Bug No : 3642506*/
AND papf.per_information2 = ftv.territory_code(+)
AND ftv.language(+) = userenv('LANG') /* Bug No : 3059915 */
AND pps.period_of_service_id = paaf.period_of_service_id /* Bug No : 3059915 */
AND papf.person_id = pcr.person_id(+)
AND papf.business_group_id = p_business_group_id
AND pcr.business_group_id(+) = p_business_group_id -- watch this condition
AND NVL(pcr.date_end(+),TO_DATE('31-12-4712','dd-mm-yyyy')) =TO_DATE('31-12-4712','dd-mm-yyyy') /* Bug No : 3642506*/
AND paaf.assignment_id = c_assignment_id
AND papf.person_id = pad.person_id(+)
AND NVL(pad.date_to(+),TO_DATE('31-12-4712','dd-mm-yyyy')) = TO_DATE('31-12-4712','dd-mm-yyyy') /* Bug No : 3642506*/
AND pad.region_1 = hrl.lookup_code(+)
AND pad.business_group_id(+) = p_business_group_id
AND hrl.lookup_type(+)= 'HK_AREA_CODES'
AND paei.assignment_id(+) = paaf.assignment_id
AND paei.aei_information_category(+) = 'HR_EMPLOYMENT_INFO_HK'
AND hsck.soft_coding_keyflex_id(+) = paaf.soft_coding_keyflex_id
AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
BETWEEN nvl(hsck.start_date_active(+),to_date('01-01-1900','dd-mm-yyyy'))
AND NVL(hsck.end_date_active(+),TO_DATE('31-12-4712','dd-mm-yyyy'))
AND pps.person_id = paaf.person_id
AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY')
BETWEEN pps.date_start
AND NVL(pps.actual_termination_date,TO_DATE('31-12-4712','dd-mm-yyyy'))
ORDER BY papf.national_identifier ASC,pcr.primary_contact_flag DESC,pcr.date_start DESC;
SELECT papf_spouse.last_name
|| DECODE(papf_spouse.first_name, null, null, ', '
|| papf_spouse.first_name)
|| DECODE(papf_spouse.middle_names, null, null, ', '
|| papf_spouse.middle_names) spouse_name,
papf_spouse.national_identifier spouse_hk_id,
papf_spouse.per_information1
||DECODE(papf_spouse.per_information2,NULL, NULL, ' '
|| ftv.territory_short_name) passport_info
FROM per_people_f papf_spouse,
fnd_territories_tl ftv /* Bug No : 3059915 */
WHERE papf_spouse.person_id = c_person_id
AND papf_spouse.business_group_id = p_business_group_id
AND papf_spouse.per_information2 = ftv.territory_code(+)
AND ftv.language(+) = userenv('LANG') /* Bug No : 3059915 */
AND papf_spouse.effective_end_date = TO_DATE('31-12-4712','dd-mm-yyyy'); /* Bug No : 3642506 */
SELECT DISTINCT
hou.name employer_name,
hoi.org_information1 employer_tfn,
hoi.org_information2 designation,
p_legal_entity_id legal_employer_id,
hoi.org_information3 contact_name,
p_reporting_year reporting_year,
TO_CHAR(SYSDATE,'YYYYMMDD') issue_date
FROM hr_organization_information hoi,
hr_organization_units hou
WHERE hoi.org_information_context = 'HK_LEGAL_EMPLOYER'
AND hoi.organization_id = hou.organization_id
AND hoi.organization_id = c_legal_entity_id ;
SELECT paa.assignment_action_id
FROM pay_assignment_actions paa
WHERE paa.assignment_id = c_assignment_id
and paa.action_sequence = (select max(paa2.action_sequence)
from pay_assignment_actions paa2,
pay_payroll_actions ppa
where paa2.assignment_id = c_assignment_id
and ppa.payroll_action_id = paa2.payroll_action_id
and paa2.action_status = 'C'
and ppa.action_type in ('R', 'Q', 'B', 'I', 'V')
and ppa.effective_date BETWEEN TO_DATE('01-04-'|| TO_CHAR(TO_NUMBER(c_reporting_year)-1), 'DD-MM-YYYY')
and TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY'));
so that quarters details for that particular financial year is selected*/
/* Bug No : 2853776 - Cursor quarters_info modified -
The sub query included for Bug No: 2853776 has been modified to join max action_sequence instead of
max assignment action id.*/
/* Bug 7184102 Added ORDER BY clause into cursor quarters_info */
CURSOR quarters_info
(c_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
c_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE)
IS
SELECT b.assignment_id assignment_id,
b.SOURCE_ID source_id,
b.QUARTERS_ADDRESS quarters_address,
b.QUARTERS_NATURE quarters_nature,
to_char(decode(to_date(max(b.quarters_period_start),'DD/MM/YYYY'), null,
greatest(max(pps.date_start),to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD')),
decode(greatest(to_date(max(b.quarters_period_start),'DD/MM/YYYY'), to_date(p_reporting_year||'0331', 'YYYYMMDD')),
to_date(max(b.quarters_period_start),'DD/MM/YYYY'),
greatest(max(pps.date_start),to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD')),
greatest(max(pps.date_start),to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD'), to_date(max(b.quarters_period_start),'DD/MM/YYYY')))), 'YYYYMMDD') quarters_period_start,
to_char(decode(to_date(max(b.quarters_period_end),'DD/MM/YYYY'), null,
least(to_date(p_reporting_year||'0331', 'YYYYMMDD'), nvl(max(pps.actual_termination_date), to_date('31-12-4712', 'DD-MM-YYYY'))),
decode(least(to_date(max(b.quarters_period_end),'DD/MM/YYYY'), to_date(to_char(to_number(p_reporting_year)-1)||'0401', 'YYYYMMDD')),
to_date(max(b.quarters_period_end),'DD/MM/YYYY'),
least(to_date(p_reporting_year||'0331', 'YYYYMMDD'), nvl(max(pps.actual_termination_date), to_date('31-12-4712', 'DD-MM-YYYY'))),
least(to_date(max(b.quarters_period_end),'DD/MM/YYYY'), nvl(max(pps.actual_termination_date), to_date('31-12-4712', 'DD-MM-YYYY')), to_date(p_reporting_year||'0331', 'YYYYMMDD')))), 'YYYYMMDD') quarters_period_end,
max(b.QUARTERS_ER_TO_LANDLORD) QUARTERS_ER_TO_LANDLORD,
max(b.QUARTERS_EE_TO_LANDLORD) QUARTERS_EE_TO_LANDLORD,
max(b.QUARTERS_REFUND_TO_EE) QUARTERS_REFUND_TO_EE,
max(b.QUARTERS_EE_TO_ER) QUARTERS_EE_TO_ER
FROM pay_hk_ir56_quarters_info_v b,
per_periods_of_service pps,
per_assignments_f paa
WHERE b.assignment_id = c_assignment_id
AND paa.assignment_id = b.assignment_id
AND paa.person_id = pps.person_id
AND paa.period_of_service_id = pps.period_of_service_id /* Bug No : 2824718 */
AND b.action_sequence = (SELECT max(action_sequence)
FROM pay_hk_ir56_quarters_info_v
WHERE assignment_id = b.assignment_id
AND source_id = b.source_id /* Bug No : 2853776 */
AND start_date between
to_date('01/04/'||to_char(to_number(p_reporting_year)-1),'DD/MM/YYYY')
AND to_date('31/03/'||p_reporting_year,'DD/MM/YYYY')
AND end_date between
to_date('01/04/'||to_char(to_number(p_reporting_year)-1),'DD/MM/YYYY')
AND to_date('31/03/'||p_reporting_year,'DD/MM/YYYY'))
GROUP BY
b.assignment_id,
b.SOURCE_ID,
b.QUARTERS_ADDRESS,
b.QUARTERS_NATURE,
quarters_period_start,
quarters_period_end
ORDER BY quarters_period_start,quarters_period_end; /* Bug 7184102 */
SELECT *
FROM pay_hk_ir56_overseas_concern_v
WHERE assignment_id = c_assignment_id
AND tax_reporting_year = p_reporting_year;
SELECT DISTINCT
hou.name employer_name,
hoi.org_information1 employer_tfn,
p_reporting_year reporting_year
FROM hr_organization_information hoi,
hr_organization_units hou
WHERE hoi.org_information_context = 'HK_LEGAL_EMPLOYER'
AND hoi.organization_id = hou.organization_id
AND hoi.organization_id = c_legal_entity_id ;
SELECT DISTINCT
papf.national_identifier hk_id_card_no,
DECODE(papf.marital_status, 'M',
DECODE(sex, 'F', NVL(previous_last_name, last_name), last_name)
,last_name) last_name,
TRIM(papf.first_name||' '||papf.middle_names) other_name
FROM per_people_f papf,
per_assignments_f paaf,
per_periods_of_service pps
WHERE paaf.person_id = papf.person_id
AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
BETWEEN paaf.effective_start_date and paaf.effective_end_date
AND TO_DATE('31-03-'|| c_reporting_year, 'DD-MM-YYYY')
BETWEEN papf.effective_start_date and papf.effective_end_date
AND papf.business_group_id = p_business_group_id
AND paaf.assignment_id = c_assignment_id
AND pps.person_id = paaf.person_id
AND pps.period_of_service_id = paaf.period_of_service_id /* Bug No : 3059915 */
AND TO_DATE('31-03-'||c_reporting_year, 'DD-MM-YYYY')
BETWEEN pps.date_start
AND NVL(pps.actual_termination_date,TO_DATE('31-12-4712','dd-mm-yyyy'));
SELECT pap.employee_number
FROM per_assignments_f a,
hr_soft_coding_keyflex sck,
per_people_f pap
WHERE a.assignment_id = p_assignment_id
AND a.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
AND pap.person_id = a.person_id
AND TO_DATE('31/03'||p_reporting_year,'DD/MM/YYYY')
BETWEEN a.effective_start_date AND a.effective_end_date
AND NVL(sck.segment3, 'Y') = 'N';
SELECT paa.person_id,
pac.assignment_id,
pay_core_utils.get_parameter('BUSINESS_GROUP_ID',ppa.legislative_parameters),
pay_core_utils.get_parameter('LEGAL_ENTITY_ID',ppa.legislative_parameters),
pay_core_utils.get_parameter('REPORTING_YEAR',ppa.legislative_parameters),
effective_date
FROM pay_payroll_actions ppa,
pay_assignment_actions pac,
per_assignments_f paa
WHERE pac.assignment_action_id = c_assignment_action_id
AND ppa.payroll_action_id = pac.payroll_action_id
AND paa.assignment_id = pac.assignment_id;
SELECT user_entity_id
FROM ff_user_entities
WHERE user_entity_name = c_user_entity_name;
SELECT printer,
print_style,
decode(save_output_flag, 'Y', 'TRUE', 'N', 'FALSE') save_output
FROM pay_payroll_actions pact,
fnd_concurrent_requests fcr
WHERE fcr.request_id = pact.request_id
AND pact.payroll_action_id = p_payroll_action_id;
select distinct ppa3.payroll_action_id
from
pay_payroll_actions ppa, -- Magtape payroll action
pay_payroll_actions ppa2, -- Report payroll action
pay_payroll_actions ppa3 -- Archive payroll action
where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
and ppa2.payroll_action_id =pay_core_utils.get_parameter('REPORT_ACTION_ID', ppa.legislative_parameters)
and ppa3.payroll_action_id =pay_core_utils.get_parameter('ARCHIVE_ACTION_ID', ppa2.legislative_parameters);