[Home] [Help]
PACKAGE: APPS.PAY_CA_T4_AMEND_MAG
Source
1 PACKAGE pay_ca_t4_amend_mag AUTHID CURRENT_USER AS
2 /* $Header: pycat4amag.pkh 120.0 2010/11/20 09:49:47 abraghun noship $ */
3
4 level_cnt NUMBER;
5
6 CURSOR T4_amend_transmitter_record IS
7 SELECT
8 'TAX_UNIT_ID=C',
9 hoi.organization_id,
10 'PAYROLL_ACTION_ID=P',
11 max(ppa.payroll_action_id),
12 'SBMT_REF_ID=P',
13 to_char(max(ppa.payroll_action_id))
14 FROM
15 hr_organization_information hoi,
16 pay_payroll_actions ppa,
17 pay_ca_legislation_info pcli,
18 pay_ca_legislation_info pcli1,
19 pay_ca_legislation_info pcli2
20 WHERE hoi.organization_id = pay_magtape_generic.get_parameter_value('TRANSMITTER_GRE')
21 AND hoi.org_information_context = 'Fed Magnetic Reporting'
22 AND ppa.report_type = 'CAEOY_T4_AMEND_PP'
23 AND hoi.organization_id = pycadar_pkg.get_parameter('TRANSFER_GRE'
24 ,ppa.legislative_parameters)
25 AND to_char(ppa.effective_date
26 ,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
27 AND to_char(ppa.effective_date
28 ,'DD-MM') = '31-12'
29 GROUP BY
30 'TAX_UNIT_ID=C',
31 hoi.organization_id,
32 'PAYROLL_ACTION_ID=P';
33 /**************************************************************************/
34 CURSOR mag_T4_amend_employer IS
35 SELECT
36 'TAX_UNIT_ID=P',
37 hoi1.organization_id,
38 'TAX_UNIT_NAME=P',
39 fai.value,
40 'PAYROLL_ACTION_ID=P',
41 to_char(max(ppa.payroll_action_id)),
42 'TRANSFER_EI_ER_RATE=P',
43 pcli2.information_value
44 FROM
45 ff_archive_items fai,
46 ff_database_items fdi,
47 pay_payroll_actions ppa,
48 pay_assignment_actions aa,
49 hr_organization_information hoi1,
50 hr_organization_information hoi2,
51 pay_ca_legislation_info pcli2
52 WHERE aa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
53 AND hoi1.org_information11 = aa.tax_unit_id
54 AND hoi1.org_information11 = hoi2.organization_id
55 AND hoi2.org_information_context = 'Fed Magnetic Reporting'
56 AND hoi1.org_information5 = 'T4/RL1'
57 AND hoi1.org_information_context = 'Canada Employer Identification'
58 AND ppa.report_type = 'CAEOY_T4_AMEND_PP'
59 AND to_char(ppa.effective_date
60 ,'YYYY') = pay_magtape_generic.get_parameter_value('REPORTING_YEAR')
61 AND to_char(ppa.effective_date
62 ,'DD-MM') = '31-12'
63 AND hoi1.organization_id = pycadar_pkg.get_parameter('TRANSFER_GRE'
64 ,ppa.legislative_parameters)
65 AND fdi.user_name = 'CAEOY_EMPLOYER_NAME'
66 AND ppa.payroll_action_id = fai.context1
67 AND fdi.user_entity_id = fai.user_entity_id
68 AND pcli2.information_type = 'EI_ER_RATE'
69 AND pcli2.jurisdiction_code IS NULL
70 AND ppa.effective_date BETWEEN pcli2.start_date AND pcli2.end_date
71 GROUP BY
72 'TAX_UNIT_ID=P',
73 hoi1.organization_id,
74 'TAX_UNIT_NAME=P',
75 fai.value,
76 'PAYROLL_ACTION_ID=P',
77 'TRANSFER_EI_ER_RATE=P',
78 pcli2.information_value
79 ORDER BY
80 fai.value;
81 /**************************************************************************/
82 CURSOR mag_T4_amend_employee IS
83 SELECT
84 'ASSIGNMENT_ACTION_ID=C',
85 pai.locked_action_id,
86 'ASSIGNMENT_ID=C',
87 paa.assignment_id,
88 'DATE_EARNED=C',
89 fnd_date.date_to_canonical(pay_magtape_generic.date_earned(ppa.effective_date
90 ,paa.assignment_id)),
91 'TRANSFER_ACT_ID=P',
92 pai.locking_action_id
93 FROM
94 per_all_people_f ppf,
95 per_all_assignments_f paf,
96 pay_action_interlocks pai,
97 pay_assignment_actions paa,
98 pay_payroll_actions ppa,
99 pay_assignment_actions paa_arch,
100 pay_payroll_actions ppa_arch
101 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
102 AND paa.payroll_action_id = ppa.payroll_action_id
103 AND paa.tax_unit_id = pay_magtape_generic.get_parameter_value('TAX_UNIT_ID')
104 AND pai.locking_action_id = paa.assignment_action_id
105 AND paf.assignment_id = paa.assignment_id
106 AND ppf.person_id = paf.person_id
107 AND pay_magtape_generic.date_earned(ppa.effective_date
108 ,paa.assignment_id) BETWEEN paf.effective_start_date AND paf.effective_end_date
109 AND pay_magtape_generic.date_earned(ppa.effective_date
110 ,paa.assignment_id) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
111 AND paa_arch.assignment_action_id = pai.locked_action_id
112 AND paa_arch.payroll_action_id = ppa_arch.payroll_action_id
113 AND ppa_arch.report_type = 'CAEOY_T4_AMEND_PP'
114 ORDER BY
115 ppf.last_name,
116 ppf.first_name,
117 ppf.middle_names;
118 /**************************************************************************/
119 CURSOR mag_T4_amend_asg_actions IS
120 SELECT
121 'TRANSFER_ACT_ID=P',
122 pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
123 FROM
124 dual;
125 /**************************************************************************/
126 PROCEDURE get_report_parameters(
127 p_pactid IN NUMBER,
128 p_legislative_parameters IN OUT NOCOPY VARCHAR2,
129 p_year_end IN OUT NOCOPY DATE,
130 p_report_type IN OUT NOCOPY VARCHAR2,
131 p_business_group_id IN OUT NOCOPY NUMBER);
132 /**************************************************************************/
133 PROCEDURE range_cursor (
134 p_pactid IN NUMBER,
135 p_sqlstr OUT NOCOPY VARCHAR2);
136 /**************************************************************************/
137 PROCEDURE action_creation(
138 p_pactid IN NUMBER,
139 p_stperson IN NUMBER,
140 p_endperson IN NUMBER,
141 p_chunk IN NUMBER );
142 /**************************************************************************/
143 FUNCTION validate_gre_data (p_trans IN VARCHAR2,
144 p_year IN VARCHAR2) RETURN VARCHAR2;
145 /**************************************************************************/
146 FUNCTION get_parameter(name in VARCHAR2,
147 parameter_list VARCHAR2)
148 RETURN VARCHAR2;
149 /**************************************************************************/
150 FUNCTION get_arch_val(p_context_id IN NUMBER,
151 p_user_name IN VARCHAR2)
152 RETURN VARCHAR2;
153 /**************************************************************************/
154 FUNCTION convert_2_xml(p_data IN VARCHAR2,
155 p_tag IN VARCHAR2,
156 p_datatype IN CHAR DEFAULT 'T',
157 p_format IN VARCHAR2 DEFAULT NULL,
158 p_null_allowed IN VARCHAR2 DEFAULT 'N' )
159 RETURN VARCHAR2;
160 /**************************************************************************/
161 FUNCTION get_T4_pp_regno(p_pactid IN NUMBER,
162 p_tax_unit_id IN NUMBER,
163 p_pp_regno1 OUT NOCOPY VARCHAR2,
164 p_pp_regno2 OUT NOCOPY VARCHAR2,
165 p_pp_regno3 OUT NOCOPY VARCHAR2)
166 RETURN VARCHAR2;
167 /**************************************************************************/
168
169 PROCEDURE T4_amend_mag_transmitter;
170
171 PROCEDURE end_of_file;
172
173 PROCEDURE T4_amend_employer_record;
174
175 PROCEDURE T4_amend_employer_start;
176
177 PROCEDURE T4_amend_employee_record;
178
179 END pay_ca_T4_amend_mag;