[Home] [Help]
PACKAGE: APPS.PAY_CA_T4_XML
Source
1 package pay_ca_t4_xml AUTHID CURRENT_USER as
2 /* $Header: pycat4xml.pkh 120.10.12020000.3 2012/07/09 09:16:31 emunisek ship $ */
3 /*
4
5 rem +======================================================================+
6 rem | Copyright (c) 1993 Oracle Corporation |
7 rem | Redwood Shores, California, USA |
8 rem | All rights reserved. |
9 rem +======================================================================+
10 Change List
11 -----------
12 Date Name Vers Description
13 ----------- ---------- ----- -----------------------------------
14 05-APR-2005 ssouresr 115.0 Created.
15 30-NOV-2005 ssouresr 115.4 Changed fetch_t4_xml to be a procedure
16 22-DEC-2009 sneelapa 115.6 Bug 7835218. Added get_ei_earnings_display_flag
17 27-OCT-2010 emunisek 115.8 Modified cursor transfer_block to ensure sorting
18 of employees in the Report as expected.
19 05-Jan-2012 pkoduri 115.10 11066853 Added code to introduce a blank page in case the
20 data an employee pdf end on an odd page.
21 06-Jun-2012 rgottipa 115.11 Added the new function 'get_final_xml' which
22 is used to call from online.
23 18-Jun-2012 rgottipa 115.12 Passing extra parameter for get_final_xml
24 function.
25 */
26
27 type other_info_rec is record (code varchar2(3),
28 amount varchar2(50));
29
30 type other_info_tab is table of other_info_rec index by binary_integer;
31
32 g_other_info_list other_info_tab;
33
34 procedure store_other_information(p_aa_id in number,
35 p_prov in varchar2);
36
37 procedure get_other_information(p_index in number,
38 p_code in out nocopy varchar2,
39 p_amount in out nocopy varchar2);
40
41 function get_ei_earnings_display_flag(p_aa_id in number)
42 return number;
43
44 procedure get_asg_xml;
45 procedure get_header_xml;
46 procedure get_trailer_xml;
47
48 FUNCTION get_final_xml (p_assignment_action_id Number,
49 p_province VARCHAR2,
50 p_output_location VARCHAR2)
51 RETURN BLOB;
52
53 procedure fetch_t4_xml(p_aa_id in number,
54 p_pa_id in number,
55 p_type in varchar2,
56 p_print in varchar2,
57 p_prov in varchar2
58 ,p_print_inst_flg varchar2 /*11066853 */
59 ,called_from in varchar2
60 ,p_xml_blob out NOCOPY BLOB
61 );
62
63 function create_xml_string (p_employer_name varchar2,
64 p_employer_bn varchar2,
65 p_employer_addr varchar2,
66 p_employee_name varchar2,
67 p_employee_last_name varchar2,
68 p_employee_init varchar2,
69 p_employee_addr varchar2,
70 p_sin varchar2,
71 p_cpp_qpp_exempt varchar2,
72 p_ei_exempt varchar2,
73 p_employment_prov varchar2,
74 p_employment_code varchar2,
75 p_registration_number varchar2,
76 p_employment_income varchar2,
77 p_cpp_contributions varchar2,
78 p_qpp_contributions varchar2,
79 p_ei_contributions varchar2,
80 p_rpp_contributions varchar2,
81 p_pension_adjustment varchar2,
82 p_tax_deducted varchar2,
83 p_ei_earnings varchar2,
84 p_cpp_qpp_earnings varchar2,
85 p_union_dues varchar2,
86 p_charitable_donations varchar2,
87 p_other_code1 varchar2,
88 p_other_amount1 varchar2,
89 p_other_code2 varchar2,
90 p_other_amount2 varchar2,
91 p_other_code3 varchar2,
92 p_other_amount3 varchar2,
93 p_other_code4 varchar2,
94 p_other_amount4 varchar2,
95 p_other_code5 varchar2,
96 p_other_amount5 varchar2,
97 p_other_code6 varchar2,
98 p_other_amount6 varchar2,
99 p_year varchar2,
100 p_ppip_exempt varchar2,
101 p_ppip_contributions varchar2,
102 p_ppip_earnings varchar2,
103 p_gre_name varchar2
104 ,p_blank_page_flg varchar2) /*11066853 */
105 return varchar2;
106
107 function get_outfile return varchar2;
108
109 function get_IANA_charset return varchar2;
110
111 cursor main_block is
112 select 'Version_Number=X' ,'Version 1.1'
113 from sys.dual;
114
115 cursor transfer_block is
116 select 'TRANSFER_ACT_ID=P', assignment_action_id
117 from hr_all_organization_units hou,
118 hr_all_organization_units hou1,
119 hr_locations_all loc,
120 per_all_people_f ppf,
121 per_all_assignments_f paf,
122 pay_assignment_actions paa1,
123 pay_payroll_actions ppa1
124 where ppa1.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
125 and paa1.payroll_action_id = ppa1.payroll_action_id
126 and paa1.assignment_id = paf.assignment_id
127 and paf.effective_start_date =
128 (select max(paf2.effective_start_date)
129 from per_all_assignments_f paf2
130 where paf2.assignment_id= paf.assignment_id
131 and paf2.effective_start_date
132 <= ppa1.effective_date)
133 and paf.effective_end_date >= ppa1.start_date
134 and paf.assignment_type = 'E'
135 and hou1.organization_id = paa1.tax_unit_id
136 and hou.organization_id = paf.organization_id
137 and loc.location_id = paf.location_id
138 and ppf.person_id = paf.person_id
139 and ppf.effective_start_date =
140 (select max(ppf2.effective_start_date)
141 from per_all_people_f ppf2
142 where ppf2.person_id= paf.person_id
143 and ppf2.effective_start_date
144 <= ppa1.effective_date)
145 and ppf.effective_end_date >= ppa1.start_date
146 order by
147 decode(pay_ca_t4_reg.get_parameter
148 ('P_S1',ppa1.legislative_parameters),
149 'GRE',hou1.name,
150 'ORGANIZATION',hou.name,
151 'LOCATION',loc.location_code,null),
152 decode(pay_ca_t4_reg.get_parameter('P_S2',ppa1.legislative_parameters),
153 'GRE',hou1.name,
154 'ORGANIZATION',hou.name,
155 'LOCATION',loc.location_code,null),
156 decode(pay_ca_t4_reg.get_parameter('P_S3',ppa1.legislative_parameters),
157 'GRE',hou1.name,
158 'ORGANIZATION',hou.name,
159 'LOCATION',loc.location_code,null),
160 ppf.last_name,first_name;
161
162 cursor assignment_block is
163 select 'TRANSFER_ACT_ID=P',pay_magtape_generic.get_parameter_value('TRANSFER_ACT_ID')
164 from sys.dual;
165
166 level_cnt NUMBER :=0;
167 g_temp_dir VARCHAR2(512);
168
169 end pay_ca_t4_xml;