[Home] [Help]
PACKAGE: APPS.PAY_IE_P35_MAGTAPE
Source
1 PACKAGE PAY_IE_P35_MAGTAPE AS
2 /* $Header: pyiep35m.pkh 120.4.12010000.1 2008/07/27 22:50:10 appldev 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 ('REQUEST_ID=P'), to_char(ppa.request_id),
29 ('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*/
30 FROM pay_payroll_actions ppa
31 ,pay_action_information pact
32
33 WHERE pact.action_context_id = ppa.payroll_action_id
34 AND pact.action_information_category = 'ADDRESS DETAILS'
35 AND pact.action_context_type = 'PA'
36 AND ppa.payroll_action_id =
37 (select max(arc_paa.payroll_action_id) from
38 pay_assignment_actions mag_paa,
39 pay_assignment_actions arc_paa,
40 pay_action_interlocks pai
41 where mag_paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
42 and pai.locking_action_id = mag_paa.assignment_action_id
43 and arc_paa.assignment_action_id= pai.locked_action_id);
44
45 /* Modified the cursor for BUG 2987230 */
46 CURSOR CSR_P35_DETAIL IS
47 SELECT ('PPSN=P')
48 ,nvl(SUBSTR(pact.action_information1,1,9),' '),
49 ('WORKS_NUMBER=P')
50 ,nvl(SUBSTR(pact.action_information2,1,12),' '), -- for bug 5301598
51 ('TOTAL_WEEKS_INSURABLE_EMPLOYMENT=P')
52 ,pact.action_information3,
53 ('INITIAL_CLASS=P')
54 ,pact.action_information4,
55 ('SECOND_CLASS=P')
56 ,pact.action_information5,
57 ('WEEKS_AT_SECOND_CLASS=P')
58 ,pact.action_information6,
59 ('THIRD_CLASS=P')
60 ,pact.action_information7,
61 ('WEEKS_AT_THIRD_CLASS=P')
62 ,pact.action_information8,
63 ('FOURTH_CLASS=P')
64 ,pact.action_information9,
65 ('WEEKS_AT_FOURTH_CLASS=P')
66 ,pact.action_information10,
67 ('FIFTH_CLASS=P')
68 ,pact.action_information11,
69 ('NET_TAX=P')
70 ,pact.action_information12,
71 ('TAX_OR_REFUND=P')
72 ,pact.action_information13,
73 ('EMPLOYEES_PRSI_CONT=P')
74 ,pact.action_information14,
75 ('TOTAL_PRSI_CONT=P')
76 ,pact.action_information15,
77 ('PAY=P')
78 ,pact.action_information16,
79 ('TAX_DEDUCTION_BASIS=P')
80 ,pact.action_information17,
81 ('SURNAME=P')
82 ,pact.action_information18,
83 ('FIRST_NAME=P')
84 ,pact.action_information19,
85 ('DOB=P')
86 ,pact.action_information20,
87 ('ADDRESS_LINE1=P')
88 ,pact.action_information21,
89 ('ADDRESS_LINE2=P')
90 ,pact.action_information22,
91 ('ADDRESS_LINE3=P')
92 ,pact.action_information23,
93 ('HIRE_DATE=P')
94 ,pact.action_information24,
95 ('TERM_DATE=P')
96 ,pact.action_information25,
97 ('ANNUAL_TAX_CREDIT=P')
98 ,pact.action_information26,
99 ('MOTHERS_NAME=P')
100 ,pact.action_information27,
101 ('MEDICAL_INSURANCE=P') -- 5867343
102 ,pact1.action_information17 -- 5867343
103
104 FROM
105 pay_assignment_actions paa
106 ,pay_action_information pact
107 ,pay_action_information pact1
108 ,pay_action_interlocks pai
109 WHERE
110 paa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
111 and pai.locking_action_id = paa.assignment_action_id
112 and paa.source_action_id is null
113 and pact.action_context_id = pai.locked_action_id
114 and pact.action_information_category = 'IE P35 DETAIL'
115 and pact.action_context_type = 'AAP'
116 and pact1.action_context_id = pact.action_context_id
117 and pact1.action_information_category = 'IE P35 ADDITIONAL DETAILS'
118 and pact1.action_context_type = 'AAP'
119 ORDER BY 36,38;
120
121 /*Added for bug fix 3815830*/
122 --FUNCTION replace_xml_symbols(p_string IN VARCHAR2) RETURN VARCHAR2;
123
124 PROCEDURE range_code(p_payroll_action_id IN NUMBER,
125 p_sqlstr OUT NOCOPY VARCHAR2);
126 --
127 PROCEDURE action_creation(pactid IN NUMBER,
128 stperson IN NUMBER,
129 endperson IN NUMBER,
130 chunk IN NUMBER);
131
132 /* Function for getting Pension Details */
133 FUNCTION get_pension_details(emp_rbs IN OUT NOCOPY NUMBER,
134 emp_rbs_bal IN OUT NOCOPY NUMBER,
135 empr_rbs IN OUT NOCOPY NUMBER,
136 empr_rbs_bal IN OUT NOCOPY NUMBER,
137 emp_prsa IN OUT NOCOPY NUMBER,
138 emp_prsa_bal IN OUT NOCOPY NUMBER,
139 empr_prsa IN OUT NOCOPY NUMBER,
140 empr_prsa_bal IN OUT NOCOPY NUMBER,
141 emp_rac IN OUT NOCOPY NUMBER,
142 emp_rac_bal IN OUT NOCOPY NUMBER,
143 p_payroll_action_id NUMBER,
144 p_taxable_benefits IN OUT NOCOPY NUMBER) RETURN NUMBER;
145
146
147 FUNCTION raise_warning(l_flag varchar2) return number;
148
149 -- for bug 6275544
150 FUNCTION test_XML(P_STRING VARCHAR2) RETURN VARCHAR2;
151
152 END pay_ie_p35_magtape;