[Home] [Help]
PACKAGE: APPS.PAY_IE_P35_MAGTAPE
Source
1 PACKAGE PAY_IE_P35_MAGTAPE AUTHID CURRENT_USER AS
2 /* $Header: pyiep35m.pkh 120.8.12020000.4 2013/02/04 11:46:54 rsahai ship $ */
3
4 l_arc_payroll_action_id pay_payroll_actions.payroll_action_id%TYPE;
5 level_cnt NUMBER;
6
7 FUNCTION get_parameter(p_payroll_action_id IN NUMBER,
8 p_token_name IN VARCHAR2) RETURN VARCHAR2;
9
10 FUNCTION get_start_date RETURN DATE;
11
12 FUNCTION get_end_date RETURN DATE;
13
14 CURSOR CSR_P35_HEADER_FOOTER IS
15 SELECT ('P35_MODE=P'), trim(pay_ie_p35.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'MODE')),
16 ('END_DATE=P'), pay_ie_p35.get_parameter(ppa.payroll_action_id,'END_DATE'),
17 ('EFFECTIVE_DATE=P'),to_char(ppa.effective_date,'dd-mm-yyyy'),
18 ('EMPLOYER_NUMBER=P'), pact.action_information1,
19 ('EMPLOYER_NAME=P'), pact.action_information26 ,
20 ('TRADE_NAME=P'), pact.action_information9,
21 ('EMPLOYER_ADDRESS1=P'),pact.action_information5 ,
22 ('EMPLOYER_ADDRESS2=P'),pact.action_information6 ,
23 ('EMPLOYER_ADDRESS3=P'),pact.action_information7 ,
24 ('CONTACT_NAME=P'), pact.action_information27 ,
25 ('CONTACT_NUMBER=P'), pact.action_information28 ,
26 ('FAX_NO=P'), pact.action_information10,
27 --('WEEKS_53=P') , decode(trim(pay_ie_p35.get_parameter(ppa.payroll_action_id,'WEEKS')),'Y','1','0'),
28 ('WEEKS_53=P') , decode(trim(pay_ie_p35.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'WEEKS')),'Y','1','0'), --8233782
29 ('REQUEST_ID=P'), to_char(ppa.request_id),
30 ('SUBMISSION_TYPE=P'),pay_ie_p35.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'SUB_TYPE') /*Added for bug fix 3815830*/
31 FROM pay_payroll_actions ppa
32 ,pay_action_information pact
33
34 WHERE pact.action_context_id = ppa.payroll_action_id
35 AND pact.action_information_category = 'ADDRESS DETAILS'
36 AND pact.action_context_type = 'PA'
37 AND ppa.payroll_action_id =
38 (select max(arc_paa.payroll_action_id) from
39 pay_assignment_actions mag_paa,
40 pay_assignment_actions arc_paa,
41 pay_action_interlocks pai
42 where mag_paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
43 and pai.locking_action_id = mag_paa.assignment_action_id
44 and arc_paa.assignment_action_id= pai.locked_action_id)
45 --16210193
46 AND EXISTS ( SELECT 1 FROM PAY_ASSIGNMENT_ACTIONS PAA
47 WHERE PAA.payroll_action_id = ppa.payroll_action_id
48 AND PAA.TAX_UNIT_ID = pay_ie_p35.get_parameter(pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID'),'EMP_NO'));
49 --16210193
50
51 /* Modified the cursor for BUG 2987230 */
52 CURSOR CSR_P35_DETAIL IS
53 SELECT ('PPSN=P')
54 ,nvl(SUBSTR(pact.action_information1,1,9),' '),
55 ('WORKS_NUMBER=P')
56 ,nvl(SUBSTR(pact.action_information2,1,12),' '), -- for bug 5301598
57 ('TOTAL_WEEKS_INSURABLE_EMPLOYMENT=P')
58 ,pact.action_information3,
59 ('INITIAL_CLASS=P')
60 ,pact.action_information4,
61 ('SECOND_CLASS=P')
62 ,pact.action_information5,
63 ('WEEKS_AT_SECOND_CLASS=P')
64 ,pact.action_information6,
65 ('THIRD_CLASS=P')
66 ,pact.action_information7,
67 ('WEEKS_AT_THIRD_CLASS=P')
68 ,pact.action_information8,
69 ('FOURTH_CLASS=P')
70 ,pact.action_information9,
71 ('WEEKS_AT_FOURTH_CLASS=P')
72 ,pact.action_information10,
73 ('FIFTH_CLASS=P')
74 ,pact.action_information11,
75 ('NET_TAX=P')
76 ,pact.action_information12, --14656910 --14656910V9
77 ('TAX_OR_REFUND=P')
78 ,pact.action_information13,
79 ('EMPLOYEES_PRSI_CONT=P')
80 ,pact.action_information14, --14656910 --14656910V9
81 ('TOTAL_PRSI_CONT=P')
82 ,pact.action_information15, --14656910 --14656910V9
83 ('PAY=P')
84 ,pact.action_information16, --14656910 --14656910V9
85 ('TAX_DEDUCTION_BASIS=P')
86 ,pact.action_information17,
87 ('SURNAME=P')
88 ,pact.action_information18,
89 ('FIRST_NAME=P')
90 ,pact.action_information19,
91 ('DOB=P')
92 ,pact.action_information20,
93 ('ADDRESS_LINE1=P')
94 ,pact.action_information21,
95 ('ADDRESS_LINE2=P')
96 ,pact.action_information22,
97 ('ADDRESS_LINE3=P')
98 ,pact.action_information23,
99 ('HIRE_DATE=P')
100 ,pact.action_information24,
101 ('TERM_DATE=P')
102 ,pact.action_information25,
103 ('ANNUAL_TAX_CREDIT=P')
104 ,pact.action_information26,
105 ('MOTHERS_NAME=P')
106 ,pact.action_information27,
107 ('MEDICAL_INSURANCE=P') -- 5867343
108 ,TRUNC(pact1.action_information17), -- 5867343 --14656910
109 ('GROSS_INCOME=P')
110 ,pact1.action_information18, /* knadhan */
111 ('INCOME_LEVY=P')
112 ,pact1.action_information19
113 --12382953
114 ,('GROSS_INCOME_USC=P')
115 ,pact1.action_information27 --14656910 --14656910V9
116 ,('USC=P')
117 ,pact1.action_information7 --14656910 --14656910V9
118 ,('EXCLUSION_ORDER=P')
119 ,pact1.action_information28
120 --12382953
121 --14656910
122 ,('ILLNESS_BENEFIT=P')
123 ,pact1.action_information22 --14656910V9
124 --14656910
125
126 FROM
127 pay_assignment_actions paa
128 ,pay_action_information pact
129 ,pay_action_information pact1
130 ,pay_action_interlocks pai
131 WHERE
132 paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
133 and pai.locking_action_id = paa.assignment_action_id
134 and paa.source_action_id is null
135 and pact.action_context_id = pai.locked_action_id
136 and pact.action_information_category = 'IE P35 DETAIL'
137 and pact.action_context_type = 'AAP'
138 and pact1.action_context_id = pact.action_context_id
139 and pact1.action_information_category = 'IE P35 ADDITIONAL DETAILS'
140 and pact1.action_context_type = 'AAP'
141 ORDER BY 36,38;
142
143 /*Added for bug fix 3815830*/
144 --FUNCTION replace_xml_symbols(p_string IN VARCHAR2) RETURN VARCHAR2;
145
146 PROCEDURE range_code(p_payroll_action_id IN NUMBER,
147 p_sqlstr OUT NOCOPY VARCHAR2);
148 --
149 PROCEDURE action_creation(pactid IN NUMBER,
150 stperson IN NUMBER,
151 endperson IN NUMBER,
152 chunk IN NUMBER);
153
154 /* Function for getting Pension Details */
155 FUNCTION get_pension_details(emp_rbs IN OUT NOCOPY NUMBER,
156 emp_rbs_bal IN OUT NOCOPY NUMBER,
157 empr_rbs IN OUT NOCOPY NUMBER,
158 empr_rbs_bal IN OUT NOCOPY NUMBER,
159 emp_prsa IN OUT NOCOPY NUMBER,
160 emp_prsa_bal IN OUT NOCOPY NUMBER,
161 empr_prsa IN OUT NOCOPY NUMBER,
162 empr_prsa_bal IN OUT NOCOPY NUMBER,
163 emp_rac IN OUT NOCOPY NUMBER,
164 emp_rac_bal IN OUT NOCOPY NUMBER,
165 p_payroll_action_id NUMBER,
166 p_taxable_benefits IN OUT NOCOPY NUMBER) RETURN NUMBER;
167 /* knadhan */
168 FUNCTION get_car_park_details( emp_parking IN OUT NOCOPY NUMBER,
169 emp_parking_bal IN OUT NOCOPY NUMBER,
170 p_payroll_action_id NUMBER,
171 empr_income_band IN OUT NOCOPY NUMBER) RETURN NUMBER;
172
173
174 FUNCTION raise_warning(l_flag varchar2) return number;
175
176 -- for bug 6275544
177 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2;
178
179 END pay_ie_p35_magtape;