[Home] [Help]
PACKAGE: APPS.PAY_CA_T4A_CANCEL_MAG
Source
1 PACKAGE pay_ca_t4a_cancel_mag AUTHID CURRENT_USER AS
2 /* $Header: pycat4acmag.pkh 120.1 2011/01/04 14:14:30 nkjaladi noship $ */
3
4 level_cnt NUMBER;
5
6 CURSOR t4a_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 /*SELECT distinct
24 'PAYROLL_ACTION_ID=P',
25 ppa1.payroll_action_id,
26 'TRANSMITTER_GRE_ID=P',
27 to_char(hoi.organization_id)
28 FROM
29 pay_payroll_actions ppa,
30 pay_payroll_actions ppa1,
31 pay_assignment_actions paa,
32 pay_assignment_actions paa1,
33 pay_action_interlocks int,
34 hr_organization_information hoi
35 WHERE
36 ppa.payroll_action_id = paa.payroll_action_id
37 AND hoi.org_information_context = 'Fed Magnetic Reporting'
38 AND hoi.organization_id = paa1.tax_unit_id
39 AND hoi.org_information1 = 'Y'
40 AND ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
41 AND int.locking_action_id = paa.assignment_action_id
42 AND paa1.assignment_action_id = int.locked_action_id
43 AND ppa1.payroll_action_id = paa1.payroll_action_id
44 AND ppa1.report_type in ('T4A','CAEOY_T4A_AMEND_PP')
45 AND ppa1.action_status = 'C'; */
46
47
48 /**************************************************************************/
49 CURSOR mag_t4a_cancel_employer IS
50 SELECT
51 'TAX_UNIT_ID=P',
52 paa1.tax_unit_id,
53 'TAX_UNIT_NAME=P',
54 fai.value,
55 'PAYROLL_ACTION_ID=P',
56 to_char(max(ppa1.payroll_action_id))
57 FROM
58 ff_archive_items fai,
59 ff_database_items fdi,
60 pay_payroll_actions ppa,
61 pay_assignment_actions paa,
62 pay_payroll_actions ppa1,
63 pay_assignment_actions paa1,
64 pay_action_interlocks int,
65 hr_organization_information hoi1
66 WHERE paa.payroll_action_id = ppa.payroll_action_id
67 AND hoi1.organization_id = paa1.tax_unit_id
68 AND ppa.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
69 AND int.locking_action_id = paa.assignment_action_id
70 AND paa1.assignment_action_id = int.locked_action_id
71 AND ppa1.payroll_action_id = paa1.payroll_action_id
72 AND hoi1.org_information11 =pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE_ID')
73 AND hoi1.org_information5 like '%T4A%'
74 AND hoi1.org_information_context = 'Canada Employer Identification'
75 AND ppa1.report_type in ('T4A','CAEOY_T4A_AMEND_PP')
76 AND to_char(ppa.effective_date
77 ,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
78 AND to_char(ppa.effective_date
79 ,'DD-MM') = '31-12'
80 AND fdi.user_name = 'CAEOY_EMPLOYER_NAME'
81 AND ppa1.payroll_action_id = fai.context1
82 AND fdi.user_entity_id = fai.user_entity_id
83 GROUP BY
84 'TAX_UNIT_ID=P',
85 paa1.tax_unit_id,
86 'TAX_UNIT_NAME=P',
87 fai.value,
88 'PAYROLL_ACTION_ID=P'
89 ORDER BY
90 fai.value;
91
92 /**************************************************************************/
93 CURSOR mag_t4a_cancel_employee IS
94 SELECT 'TRANSFER_ACT_ID=P',
95 paa.assignment_action_id
96 FROM
97 per_all_people_f ppf,
98 per_all_assignments_f paf,
99 pay_action_interlocks pai,
100 pay_assignment_actions paa,
101 pay_payroll_actions ppa,
102 pay_assignment_actions paa_mag
103 WHERE
104 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
105 AND paa.payroll_action_id = ppa.payroll_action_id
106 AND pai.locking_action_id = paa.assignment_action_id
107 AND paf.assignment_id = paa.assignment_id
108 AND ppf.person_id = paf.person_id
109 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
110 between paf.effective_start_date and paf.effective_end_date
111 AND pay_magtape_generic.date_earned(ppa.effective_date,paa.assignment_id)
112 between ppf.effective_start_date and ppf.effective_end_date
113 AND paa_mag.payroll_action_id = to_number(pay_magtape_generic.get_parameter_value('PAY_ACT'))
114 AND paa_mag.assignment_action_id = pai.locked_action_id
115 AND paa.action_status = 'C'
116 AND paa_mag.action_status = 'C'
117 AND paa.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
118 ORDER BY
119 ppf.last_name,
120 ppf.first_name,
121 ppf.middle_names;
122
123 /**************************************************************************/
124 CURSOR mag_t4a_cancel_asg_actions IS
125 SELECT
126 'TRANSFER_ACT_ID=P',
127 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
128 FROM
129 dual;
130
131 /**************************************************************************/
132 PROCEDURE get_report_parameters(
133 p_pactid IN NUMBER,
134 p_legislative_parameters IN OUT NOCOPY VARCHAR2,
135 p_year_end IN OUT NOCOPY DATE,
136 p_report_type IN OUT NOCOPY VARCHAR2,
137 p_business_group_id IN OUT NOCOPY NUMBER);
138 /**************************************************************************/
139 PROCEDURE range_cursor (
140 p_pactid IN NUMBER,
141 p_sqlstr OUT NOCOPY VARCHAR2);
142 /**************************************************************************/
143 PROCEDURE action_creation(
144 p_pactid IN NUMBER,
145 p_stperson IN NUMBER,
146 p_endperson IN NUMBER,
147 p_chunk IN NUMBER );
148 /**************************************************************************/
149 FUNCTION validate_gre_data (p_trans IN VARCHAR2,
150 p_year IN VARCHAR2) RETURN VARCHAR2;
151 /**************************************************************************/
152 FUNCTION get_parameter(name in VARCHAR2,
153 parameter_list VARCHAR2)
154 RETURN VARCHAR2;
155 /**************************************************************************/
156 FUNCTION get_arch_val(p_context_id IN NUMBER,
157 p_user_name IN VARCHAR2)
158 RETURN VARCHAR2;
159 /**************************************************************************/
160 FUNCTION convert_2_xml(p_data IN VARCHAR2,
161 p_tag IN VARCHAR2,
162 p_datatype IN CHAR DEFAULT 'T',
163 p_format IN VARCHAR2 DEFAULT NULL,
164 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
165 RETURN VARCHAR2;
166 /**************************************************************************/
167 FUNCTION get_t4a_pp_regno(p_pactid IN NUMBER,
168 p_tax_unit_id IN NUMBER,
169 p_pp_regno1 OUT NOCOPY VARCHAR2,
170 p_pp_regno2 OUT NOCOPY VARCHAR2,
171 p_pp_regno3 OUT NOCOPY VARCHAR2)
172 RETURN VARCHAR2;
173 /**************************************************************************/
174
175 PROCEDURE t4a_cancel_mag_transmitter;
176
177 PROCEDURE end_of_file;
178
179 PROCEDURE t4a_cancel_employer_record;
180
181 PROCEDURE t4a_cancel_employer_start;
182
183 PROCEDURE t4a_cancel_employee_record;
184
185 END pay_ca_t4a_cancel_mag;