[Home] [Help]
PACKAGE: APPS.PER_ES_SS_REP_ARCHIVE_PKG
Source
1 PACKAGE PER_ES_SS_REP_ARCHIVE_PKG AUTHID CURRENT_USER AS
2 /* $Header: peesssar.pkh 115.6 2004/04/15 03:06:14 viviswan noship $ */
3
4 level_cnt NUMBER;
5 TYPE effective_report_date_list IS TABLE OF per_assignment_extra_info.aei_information2%TYPE;
6 TYPE event_list IS TABLE OF per_assignment_extra_info.aei_information3%TYPE;
7 TYPE value_list IS TABLE OF per_assignment_extra_info.aei_information4%TYPE;
8 TYPE action_type_list IS TABLE OF per_assignment_extra_info.aei_information6%TYPE;
9 TYPE first_changed_date_list IS TABLE OF per_assignment_extra_info.aei_information7%TYPE;
10 TYPE csr_event_values IS REF CURSOR;
11 --------------------------------------------------------------------------------
12 CURSOR CSR_SS_REP_HEADER_FOOTER IS
13 SELECT ('AUTHORIZATION_KEY=P'), LPAD(NVL(pact.action_information1,'0'), 8, '0'),
14 ('SILCON_KEY=P'), RPAD(NVL(pact.action_information2,' '), 8,' '),
15 ('SS_DATE=P'), to_char(fnd_date.canonical_to_date(pact.action_information3), 'YYYYMMDD'),
16 ('SS_TIME=P'), LPAD(NVL(pact.action_information4,'0'), 4, '0'),
17 ('FILE_NAME=P'), to_char(fnd_date.canonical_to_date(pact.action_information5), 'YYYYMMDD'),
18 ('FILE_EXTENSION=P'), RPAD(pact.action_information6, 3),
19 ('TEST_FLAG=P'), pact.action_information8,
20 ('CURRENT_PASSWORD=P'), RPAD(NVL(pact.action_information9,' '), 8),
21 ('NEW_PASSWORD=P'), RPAD(NVL(pact.action_information10,' '), 8)
22 FROM pay_payroll_actions ppa
23 ,pay_action_information pact
24 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
25 AND pact.action_context_id = ppa.payroll_action_id
26 AND pact.action_information_category = 'ES_SS_REPORT_ETI'
27 AND pact.action_context_type = 'PA'
28 AND ROWNUM < 2;
29 --------------------------------------------------------------------------------
30 CURSOR CSR_SS_REP_COMPANY IS
31 SELECT ('SS_SCHEME=P'), LPAD(NVL(pact.action_information1,'0'), 4, '0'),
32 ('PROVINCE=P'), LPAD(NVL(pact.action_information2,'0'), 2, '0'),
33 ('SS_NUMBER=P'), LPAD(NVL(pact.action_information3,'0'), 9, '0'),
34 ('ID_TYPE=P'), NVL(pact.action_information4,' '),
35 ('COUNTRY=P'), RPAD(NVL(pact.action_information5,' '), 3),
36 ('EMPLOYER_ID=P'), RPAD(NVL(pact.action_information6,' '), 14),
37 ('SS_OPEN=P'), RPAD(' ', 2, ' '),
38 ('SS_SCHEME1=P'), LPAD(NVL(pact.action_information7,'0'), 4, '0'),
39 ('PROVINCE1=P'), LPAD(NVL(pact.action_information8,'0'), 2, '0'),
40 ('SS_NUMBER1=P'), LPAD(NVL(pact.action_information9,'0'), 9, '0'),
41 ('ACTION_EVENT=P'), NVL(pact.action_information10,' '),
42 ('COMP_REG_FLAG=P'), NVL(pact.action_information12,' '),
43 ('EMPLOYER_TYPE=P'), NVL(pact.action_information11,' '),
44 ('REGISTERED_NAME=P'), RPAD(NVL(pact.action_information13,' '), 55),
45 ('START_DATE=P'), LPAD(NVL(pact.action_information14,'0'),8,'0'),
46 ('END_DATE=P'), LPAD(NVL(pact.action_information15,'0'),8,'0'),
47 ('ORG_ID=P'),pact.action_information16
48 FROM pay_payroll_actions ppa
49 ,pay_action_information pact
50 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
51 AND pact.action_context_id = ppa.payroll_action_id
52 AND pact.action_information_category = 'ES_SS_REPORT_EMP'
53 AND pact.action_context_type = 'PA'
54 AND EXISTS ( SELECT 1
55 FROM pay_payroll_actions ppa1
56 ,pay_action_information pact_tra
57 ,pay_assignment_actions paa1
58 WHERE ppa1.payroll_action_id = ppa.payroll_action_id
59 AND pact_tra.action_information_category = 'ES_SS_REPORT_TRA'
60 AND pact_tra.action_context_type = 'AAP'
61 AND ppa1.payroll_action_id = paa1.payroll_action_id
62 AND pact_tra.action_context_id = paa1.assignment_action_id
63 AND pact_tra.action_information11 = pact.action_information16);
64
65 --------------------------------------------------------------------------------
66 CURSOR CSR_SS_REP_EMPLOYEE IS
67 SELECT ('PROVINCE=P'), LPAD(NVL(pact_tra.action_information1,'0'),2,'0'),
68 ('SS_NUMBER_EMP=P'),LPAD(NVL(pact_tra.action_information2,'0'),10,'0'),
69 ('ID_TYPE_EMP=P'), NVL(pact_tra.action_information3,' '),
70 ('COUNTRY_EMP=P'), NVL(pact_tra.action_information4,' '),
71 ('EMPLOYER_ID_EMP=P'), RPAD(NVL(pact_tra.action_information5,' '),14,' '),
72 ('NATIONALITY=P'), NVL(pact_tra.action_information6,' '),
73 ('EMPLOYEE_FLAG=P'), ' ',
74 ('FIRST_LAST_NAME=P'), RPAD(pact_tra.action_information7, 20,' '),
75 ('SECOND_LAST_NAME=P'), RPAD(NVL(pact_tra.action_information8,' '), 20,' '),
76 ('SS_NAME=P'), RPAD(NVL(pact_tra.action_information9,' '), 15,' '),
77 ('ACTION=P'), pact_fab.action_information1,
78 ('STATUS_OF_REASON=P'), LPAD(NVL(pact_fab.action_information2,'0'), 2, '0'),
79 ('ACTUAL_DATE=P'), to_char(fnd_date.canonical_to_date(pact_fab.action_information3), 'YYYYMMDD'),
80 ('ASSIGNMENT_STATUS=P'), LPAD(NVL(pact_fab.action_information4,'0'), 2, '0'),
81 ('EMPLOYMENT_CATEGORY=P'), LPAD(NVL(pact_fab.action_information5,'0'), 3, '0'),
82 ('EPIGRAPH_CODE=P'), LPAD(NVL(pact_fab.action_information6,'0'), 3, '0'),
83 ('UNEMPLOYED_FLAG=P'), NVL(pact_fab.action_information7,'0'),
84 ('UNDER_REPRESENTED_FLAG=P'), DECODE(NVL(pact_fab.action_information8,' '),'Y','S','N','N',' '),
85 ('PART_TIME_PERCENT=P'), LPAD(NVL(pact_fab.action_information19,'0'), 3, '0'),
86 ('EMP_COLLECTIVE_AGREEMENT=P'), LPAD(NVL(pact_fab.action_information20,'0'), 3, '0'),
87 ('PRINT_FLAG=P'), ' ',
88 ('PROFESSIONAL_CATEGORY=P'), LPAD(NVL(pact_fab.action_information21,'0'), 7, '0'),
89 ('BIRTH_DATE=P'), NVL(to_char(fnd_date.canonical_to_date(pact_fab.action_information9), 'YYYYMMDD'),'0'),
90 ('SEX=P'), DECODE(NVL(pact_fab.action_information10,'0'),'M','1','F','2','0') ,
91 ('RE_HIRED_DISABLED_FLAG=P'), DECODE(NVL(pact_fab.action_information11,' '),'Y','S','N','N',' '),
92 ('INDEPENDENT_CONTRACTOR=P'), DECODE(NVL(pact_fab.action_information12,' '),'Y','S','N','N',' '),
93 ('DISABILITY_DEGREE=P'), RPAD(NVL(pact_fab.action_information13,' '), 2),
94 ('CONTROL_DATE=P'), LPAD(NVL(pact_fab.action_information14,'0'),8,'0'),
95 ('MINORITY_GROUP_EMP_FLAG=P'), DECODE(NVL(pact_fab.action_information15,' '),'Y','S','N','N',' '),
96 ('EMP_ACTIVE_RENT_FLAG=P'), DECODE(NVL(pact_fab.action_information16,' '),'Y','S','N','N',' '),
97 ('EMP_MATERNITY_LEAVE=P'), DECODE(NVL(pact_fab.action_information17,' '),'Y','S','N','N',' '),
98 ('CONTRIBUTION_GROUP=P'), LPAD(NVL(pact_fab.action_information18,'0'),2,'0'),
99 ('ASS_NUMBER=P'), pact_tra.action_information10,
100 ('CONT_START_DATE=P'),LPAD(NVL(to_char(fnd_date.canonical_to_date(pact_tra.action_information12), 'YYYYMMDD'),'0'),8,'0'),
101 ('CONT_START_FLAG=P'),DECODE(NVL(pact_tra.action_information13,'N'),'N','N','Y'),
102 ('LABOR_RELATIONSHIP_TYPE=P'), LPAD(NVL(pact_tra.action_information14,'0'),4,'0'),
103 ('REPLACED_EMP_SS_NO=P'),LPAD(NVL(pact_tra.action_information15,'0'),12,'0'),
104 ('REPLACEMENT_REASON=P'),LPAD(NVL(pact_tra.action_information16,'0'),2,'0'),
105 ('PERCENT_INTEGER=P'), NVL(pact_tra.action_information17,'0'),
106 ('PERCENT_DECIMAL=P'),LPAD(NVL(pact_tra.action_information18,'0'),2,'0'),
107 ('WORKING_DAYS=P'),LPAD(NVL(pact_tra.action_information19,'0'),3,'0'),
108 ('DAYS_PERCENT_APPLIES=P'),LPAD(NVL(pact_tra.action_information20,'0'),3,'0'),
109 ('RETIREMENT_REDUCTION_PERCENT=P'),LPAD(NVL(pact_tra.action_information21,'0'),2,'0'),
110 ('TEST_FLAG=P'),pact_tra.action_information22 -- not used
111 FROM pay_payroll_actions ppa
112 ,pay_assignment_actions paa
113 ,pay_action_information pact_tra
114 ,pay_action_information pact_fab
115 WHERE ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('TRANSFER_PAYROLL_ACTION_ID')
116 AND paa.payroll_action_id = ppa.payroll_action_id
117 AND pact_tra.action_context_id = paa.assignment_action_id
118 AND pact_fab.action_context_id = paa.assignment_action_id
119 AND pact_tra.action_context_id = pact_fab.action_context_id
120 AND pact_tra.action_information_category = 'ES_SS_REPORT_TRA'
121 AND pact_fab.action_information_category = 'ES_SS_REPORT_FAB'
122 AND pact_tra.action_context_type = 'AAP'
123 AND pact_fab.action_context_type = 'AAP'
124 AND pact_tra.action_information11 = pay_magtape_generic.get_parameter_value('ORG_ID')
125 ORDER BY pact_fab.action_information1;
126 --------------------------------------------------------------------------------
127 FUNCTION get_parameter (
128 p_parameter_string IN VARCHAR2
129 ,p_token IN VARCHAR2) RETURN VARCHAR2;
130 --------------------------------------------------------------------------------
131 PROCEDURE get_all_parameters (
132 p_payroll_action_id IN NUMBER
133 ,p_effective_end_Date OUT NOCOPY DATE
134 ,p_test_flag OUT NOCOPY VARCHAR2
135 ,p_effective_date OUT NOCOPY DATE
136 ,p_business_group_id OUT NOCOPY NUMBER
137 ,p_organization_id OUT NOCOPY NUMBER
138 ,p_assignment_set_id OUT NOCOPY NUMBER);
139 --------------------------------------------------------------------------------
140 PROCEDURE range_cursor_archive(
141 pactid IN NUMBER
142 ,sqlstr OUT NOCOPY VARCHAR);
143 --------------------------------------------------------------------------------
144 PROCEDURE action_creation_archive(
145 pactid IN NUMBER
146 ,stperson IN NUMBER
147 ,endperson IN NUMBER
148 ,chunk IN NUMBER);
149 --------------------------------------------------------------------------------
150 PROCEDURE get_ss_details (
151 p_assignment_id NUMBER
152 ,p_reporting_date DATE
153 ,p_under_repres_women OUT NOCOPY VARCHAR2
154 ,p_rehired_disabled OUT NOCOPY VARCHAR2
155 ,p_unemployment_status OUT NOCOPY VARCHAR2
156 ,p_first_contractor OUT NOCOPY VARCHAR2
157 ,p_after_two_years OUT NOCOPY VARCHAR2
158 ,p_active_rent_flag OUT NOCOPY VARCHAR2
159 ,p_minority_group_flag OUT NOCOPY VARCHAR2);
160 --------------------------------------------------------------------------------
161 PROCEDURE get_disability_degree (
162 p_assignment_id IN NUMBER
163 ,p_reporting_date IN DATE
164 ,p_degree OUT NOCOPY NUMBER);
165 --------------------------------------------------------------------------------
166 PROCEDURE archive_code(
167 p_assignment_id IN NUMBER
168 ,pactid IN NUMBER
169 ,p_assignment_action_id IN NUMBER
170 ,p_effective_end_date IN DATE);
171 --------------------------------------------------------------------------------
172 PROCEDURE get_other_values(
173 p_value IN OUT NOCOPY VARCHAR2
174 ,p_event IN VARCHAR2
175 ,p_assignment_id IN NUMBER
176 ,pactid IN NUMBER
177 ,p_assignment_action_id IN NUMBER
178 ,p_effective_end_date IN DATE
179 ,sql_str IN VARCHAR2);
180 --------------------------------------------------------------------------------
181 PROCEDURE get_all_parameters_lock(
182 p_payroll_action_id IN NUMBER
183 ,p_arch_payroll_action_id OUT NOCOPY NUMBER
184 ,p_effective_end_date OUT NOCOPY DATE);
185 --------------------------------------------------------------------------------
186 PROCEDURE range_cursor_lock(
187 pactid IN NUMBER
188 ,sqlstr OUT NOCOPY VARCHAR);
189 --------------------------------------------------------------------------------
190 PROCEDURE action_creation_lock(
191 pactid IN NUMBER
192 ,stperson IN NUMBER
193 ,endperson IN NUMBER
194 ,chunk IN NUMBER);
195 -------------------------------------------------------------------------------
196 END PER_ES_SS_REP_ARCHIVE_PKG;