[Home] [Help]
336: cp_report_type varchar2,
337: cp_tax_unit_id number) is
338: SELECT DISTINCT asg.assignment_id ass_id
339: FROM per_all_assignments_f asg,
340: pay_assignment_actions paa,
341: pay_payroll_actions ppa,
342: per_all_people_f ppf
343: WHERE ppa.effective_date BETWEEN cp_effective_date
344: AND add_months(cp_effective_date, 12) - 1
353: AND ppa.effective_date between asg.effective_start_date AND asg.effective_end_date
354: AND ppa.effective_date between ppf.effective_start_date AND ppf.effective_end_date
355: AND NOT EXISTS ( SELECT 1
356: FROM pay_payroll_actions ppa1,
357: pay_assignment_actions paa1
358: WHERE ppa1.report_type = cp_report_type
359: AND ppa1.report_qualifier = 'CAEOY'
360: AND ppa1.effective_date = add_months(cp_effective_date, 12) - 1
361: AND get_parameter('TRANSFER_GRE',ppa1.legislative_parameters) = to_char(cp_tax_unit_id)
401: and fc.context_name = 'JURISDICTION_CODE'
402: and pac.context_value = 'QC')
403: AND NOT EXISTS (SELECT 1
404: FROM pay_payroll_actions ppa,
405: pay_assignment_actions paa
406: WHERE ppa.report_type = cp_report_type
407: AND ppa.report_qualifier = 'CAEOYRL1'
408: AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
409: AND get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters) = to_char(cp_pre_id)
441: and fc.context_name = 'JURISDICTION_CODE'
442: and pac.context_value = 'QC')
443: AND NOT EXISTS (SELECT 1
444: FROM pay_payroll_actions ppa,
445: pay_assignment_actions paa
446: WHERE ppa.report_type = cp_report_type
447: AND ppa.report_qualifier = 'CAEOYRL2'
448: AND ppa.effective_date = add_months(cp_effective_date, 12) - 1
449: AND get_parameter('PRE_ORGANIZATION_ID',ppa.legislative_parameters)= to_char(cp_pre_id)
464: WHERE EXISTS (SELECT 1
465: FROM pay_run_results prr,
466: pay_run_result_values prrv,
467: pay_input_values_f piv,
468: pay_assignment_actions paa,
469: pay_payroll_actions ppa,
470: pay_all_payrolls_f ppf
471: WHERE ppa.business_group_id+0 = cp_business_group
472: AND paa.assignment_id = cp_assignment_id