[Home] [Help]
PACKAGE: APPS.PAY_CA_T4_CANCEL_MAG
Source
1 PACKAGE pay_ca_t4_cancel_mag AUTHID CURRENT_USER AS
2 /* $Header: pycat4cmag.pkh 120.0 2011/01/10 10:09:09 emunisek noship $ */
3
4 level_cnt NUMBER;
5
6 CURSOR t4_cancel_transmitter_record IS
7 SELECT distinct
8 'PAYROLL_ACTION_ID=P',
9 ppa.payroll_action_id,
10 'TRANSMITTER_GRE_ID=P',
11 to_char(hoi.organization_id)
12 FROM
13 pay_payroll_actions ppa,
14 pay_assignment_actions paa,
15 hr_organization_information hoi
16 WHERE
17 ppa.payroll_action_id = paa.payroll_action_id
18 AND hoi.org_information_context = 'Fed Magnetic Reporting'
19 AND hoi.organization_id = paa.tax_unit_id
20 AND hoi.org_information1 = 'Y'
21 AND ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
22 AND ppa.action_status = 'C';
23
24 /**************************************************************************/
25 CURSOR mag_t4_cancel_employer IS
26 SELECT
27 'TAX_UNIT_ID=P',
28 paa1.tax_unit_id,
29 'TAX_UNIT_NAME=P',
30 fai.value,
31 'PAYROLL_ACTION_ID=P',
32 to_char(max(ppa1.payroll_action_id))
33 FROM
34 ff_archive_items fai,
35 ff_database_items fdi,
36 pay_payroll_actions ppa,
37 pay_assignment_actions paa,
38 pay_payroll_actions ppa1,
39 pay_assignment_actions paa1,
40 pay_action_interlocks int,
41 hr_organization_information hoi1
42 WHERE paa.payroll_action_id = ppa.payroll_action_id
43 AND hoi1.organization_id = paa1.tax_unit_id
44 AND ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
45 AND int.locking_action_id = paa.assignment_action_id
46 AND paa1.assignment_action_id = int.locked_action_id
47 AND ppa1.payroll_action_id = paa1.payroll_action_id
48 AND hoi1.org_information11 =pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE_ID')
49 AND hoi1.org_information5 like '%T4%'
50 AND hoi1.org_information_context = 'Canada Employer Identification'
51 AND ppa1.report_type in ('T4','CAEOY_T4_AMEND_PP')
52 AND to_char(ppa.effective_date
53 ,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
54 AND to_char(ppa.effective_date
55 ,'DD-MM') = '31-12'
56 AND fdi.user_name = 'CAEOY_EMPLOYER_NAME'
57 AND ppa1.payroll_action_id = fai.context1
58 AND fdi.user_entity_id = fai.user_entity_id
59 GROUP BY
60 'TAX_UNIT_ID=P',
61 paa1.tax_unit_id,
62 'TAX_UNIT_NAME=P',
63 fai.value,
64 'PAYROLL_ACTION_ID=P'
65 ORDER BY
66 fai.value;
67 /**************************************************************************/
68 CURSOR mag_t4_cancel_employee IS
69 SELECT 'TRANSFER_ACT_ID=P',
70 paa.assignment_action_id
71 FROM
72 per_all_people_f ppf,
73 per_all_assignments_f paf,
74 pay_action_interlocks pai,
75 pay_assignment_actions paa,
76 pay_payroll_actions ppa,
77 pay_assignment_actions paa_mag
78 WHERE ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'))
79 AND paa.payroll_action_id = ppa.payroll_action_id
80 AND pai.locking_action_id = paa.assignment_action_id
81 AND paf.assignment_id = paa.assignment_id
82 AND ppf.person_id = paf.person_id
83 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
84 BETWEEN paf.effective_start_date and paf.effective_end_date
85 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
86 BETWEEN ppf.effective_start_date and ppf.effective_end_date
87 AND paa_mag.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
88 AND paa_mag.assignment_action_id = pai.locked_action_id
89 AND paa.action_status = 'C'
90 AND paa_mag.action_status = 'C'
91 AND paa.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
92 ORDER BY ppf.last_name,
93 ppf.first_name,
94 ppf.middle_names;
95 /**************************************************************************/
96 CURSOR mag_t4_cancel_asg_actions IS
97 SELECT
98 'TRANSFER_ACT_ID=P',
99 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
100 FROM
101 dual;
102 /**************************************************************************/
103 PROCEDURE get_report_parameters(
104 p_pactid IN NUMBER,
105 p_legislative_parameters IN OUT NOCOPY VARCHAR2,
106 p_year_end IN OUT NOCOPY DATE,
107 p_report_type IN OUT NOCOPY VARCHAR2,
108 p_business_group_id IN OUT NOCOPY NUMBER);
109 /**************************************************************************/
110 PROCEDURE range_cursor (
111 p_pactid IN NUMBER,
112 p_sqlstr OUT NOCOPY VARCHAR2);
113 /**************************************************************************/
114 PROCEDURE action_creation(
115 p_pactid IN NUMBER,
116 p_stperson IN NUMBER,
117 p_endperson IN NUMBER,
118 p_chunk IN NUMBER );
119 /**************************************************************************/
120 FUNCTION validate_gre_data (p_trans IN VARCHAR2,
121 p_year IN VARCHAR2) RETURN VARCHAR2;
122 /**************************************************************************/
123 FUNCTION get_parameter(name in VARCHAR2,
124 parameter_list VARCHAR2)
125 RETURN VARCHAR2;
126 /**************************************************************************/
127 FUNCTION get_arch_val(p_context_id IN NUMBER,
128 p_user_name IN VARCHAR2)
129 RETURN VARCHAR2;
130 /**************************************************************************/
131 FUNCTION convert_2_xml(p_data IN VARCHAR2,
132 p_tag IN VARCHAR2,
133 p_datatype IN CHAR DEFAULT 'T',
134 p_format IN VARCHAR2 DEFAULT NULL,
135 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
136 RETURN VARCHAR2;
137 /**************************************************************************/
138 FUNCTION get_t4_pp_regno(p_pactid IN NUMBER,
139 p_tax_unit_id IN NUMBER,
140 p_pp_regno1 OUT NOCOPY VARCHAR2,
141 p_pp_regno2 OUT NOCOPY VARCHAR2,
142 p_pp_regno3 OUT NOCOPY VARCHAR2)
143 RETURN VARCHAR2;
144 /**************************************************************************/
145
146 PROCEDURE t4_cancel_mag_transmitter;
147
148 PROCEDURE end_of_file;
149
150 PROCEDURE t4_cancel_employer_record;
151
152 PROCEDURE t4_cancel_employer_start;
153
154 PROCEDURE t4_cancel_employee_record;
155
156 END pay_ca_t4_cancel_mag;