[Home] [Help]
PACKAGE: APPS.PAY_IE_PAYPATH_TAPE
Source
1 PACKAGE pay_ie_paypath_tape AS
2 /* $Header: pyiepppk.pkh 120.1 2006/12/11 13:49:59 sgajula noship $ */
3
4 level_cnt NUMBER;
5 c_credit_type Varchar2(10) :='CREDIT';
6 c_xxxxx_type Varchar2(10) :='XXXXX';
7 g_pathid varchar2(150);
8
9 -- FUNCTION GET_PAYPATHID return varchar2; -- Bug No 3060464
10
11 /*Bug no. 3813140*/
12 CURSOR CSR_PPH_HEADER
13 IS
14 SELECT 'DUMMY=P' , '1' --pay_ie_paypath_tape.get_paypathid pathid
15 FROM dual;
16
17 CURSOR CSR_PPH_HEADER_RECORD
18 IS
19 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
20 paa PAY_ASSIGNMENT_ACTIONS_N50,
21 ppp PAY_PRE_PAYMENTS_PK,
22 popm PAY_ORG_PAYMENT_METHODS_F_PK,
23 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
24 a PER_ASSIGNMENTS_F_PK,
25 p PER_PEOPLE_F_PK ,
26 oea PAY_EXTERNAL_ACCOUNTS_PK,
27 pea PAY_EXTERNAL_ACCOUNTS_PK)
28 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,hou,org) */
29 'VOLUME_ID=P' ,pay_magtape_generic.get_parameter_value('PAYPATH_VOLUME')
30 ,'PAYPATH_ID=P' ,pay_ie_archive_detail_pkg.get_paypathid --Bug no. 3813140
31 ,'PROCESSING_DATE=P',to_char(to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYDDD')
32 ,'FILE_NUMBER=P' ,pay_magtape_generic.get_parameter_value('FILE_NUMBER')
33 ,'RECEIVER_ID=P' ,org.org_information9
34 ,'SES_DATE=P' ,to_char(to_date(pay_magtape_generic.get_parameter_value('EFFECTIVE_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYMMDD')
35 ,'FILE_FORMAT=P' ,nvl(org.org_information11, 'AIB')
36 ,'EXPIRATION_DATE=P',to_char(to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE'),'YYYY/MM/DD HH24:MI:SS'),'YYMMDD')
37 FROM
38 pay_payroll_actions ppa
39 , pay_assignment_actions paa
40 , pay_pre_payments ppp
41 , pay_org_payment_methods_f popm
42 , pay_personal_payment_methods_f pppm
43 , per_all_assignments_f a
44 , per_all_people_f p
45 , pay_external_accounts oea
46 , pay_external_accounts pea
47 , hr_organization_units hou
48 , hr_organization_information org
49
50 -- , (select pay_ie_paypath_tape.get_paypathid pathid from dual) paypath -- Bug No 3060464
51 where
52 ppa.payroll_action_id = -- Bug No 3513042
53 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
54 and paa.payroll_action_id = ppa.payroll_action_id
55 and ppp.pre_payment_id = paa.pre_payment_id
56 and oea.external_account_id = popm.external_account_id
57 and popm.org_payment_method_id = ppp.org_payment_method_id
58 and pea.external_account_id = pppm.external_account_id
59 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
60 and paa.assignment_id = a.assignment_id
61 and a.person_id = p.person_id
62 -- Added nvl function for bug fix 3649139
63 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
64 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
65 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
66 and ppa.effective_date between a.effective_start_date and a.effective_end_date
67 and ppa.effective_date between p.effective_start_date and p.effective_end_date
68 and org.organization_id (+) = p.business_group_id
69 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
70 -- Added for bug fix 3649139
71 and org.organization_id = hou.organization_id
72 and pppm.business_group_id = hou.business_group_id
73 and popm.business_group_id = hou.business_group_id
74 -- Added for bug fix 5696117
75 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid, pay_ie_archive_detail_pkg.get_paypathid)
76 in (org.org_information8,'Error',' ')
77 and rownum < 2;
78
79 /* and (org.org_information8 = pay_ie_paypath_tape.g_pathid --Bug no. 3813140
80 or 'Error'=pay_ie_paypath_tape.g_pathid --Bug no. 3813140
81 or ' '=pay_ie_paypath_tape.g_pathid ) --Bug no. 3813140 and 3060464
82 and rownum < 2;*/
83
84
85 CURSOR CSR_PPH_CREDIT_RECORD
86 IS
87 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
88 paa PAY_ASSIGNMENT_ACTIONS_N50,
89 ppp PAY_PRE_PAYMENTS_PK,
90 popm PAY_ORG_PAYMENT_METHODS_F_PK,
91 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
92 a PER_ASSIGNMENTS_F_PK,
93 p PER_PEOPLE_F_PK ,
94 oea PAY_EXTERNAL_ACCOUNTS_PK,
95 pea PAY_EXTERNAL_ACCOUNTS_PK,
96 org HR_ORGANIZATION_INFORMATIO_FK2)
97 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,org) */
98 'TYPE=P', c_credit_type
99 ,'EMP_NAME=P' , p.full_name
100 ,'EMPLOYEE_NSC=P', pea.segment1
101 ,'EMPLOYEE_ACC_NO=P', pea.segment4
102 ,'TRANSACTION_CODE=P', '99'
103 ,'EMPLOYER_NSC=P', oea.segment1
104 ,'EMPLOYER_ACC_NO=P', oea.segment4
105 ,'PAY_AMOUNT=P', (ppp.value * 100)
106 ,'USER_NAME=P', oea.segment5
107 ,'USER_REFERNCE=P', org.org_information10
108 ,'ACC_CREDIT_NAME=P', pea.segment5
109 ,'FILE_FORMAT=P', nvl(org.org_information11, 'AIB')
110 from
111 pay_payroll_actions ppa
112 , pay_assignment_actions paa
113 , pay_pre_payments ppp
114 , pay_org_payment_methods_f popm
115 , pay_personal_payment_methods_f pppm
116 , per_all_assignments_f a -- Bug No 3513042
117 , per_all_people_f p
118 , pay_external_accounts oea
119 , pay_external_accounts pea
120 , hr_organization_information org
121 where ppa.payroll_action_id = -- Bug No 3513042
122 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
123 and ppp.pre_payment_id = paa.pre_payment_id
124 and paa.payroll_action_id = ppa.payroll_action_id
125 and oea.external_account_id = popm.external_account_id
126 and popm.org_payment_method_id = ppp.org_payment_method_id
127 and pea.external_account_id = pppm.external_account_id
128 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
129 and paa.assignment_id = a.assignment_id
130 and a.person_id = p.person_id
131 --Added nvl function code for bug fix 3649139
132 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
133 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
134 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
135 and ppa.effective_date between a.effective_start_date and a.effective_end_date
136 and ppa.effective_date between p.effective_start_date and p.effective_end_date
137 and org.organization_id (+) = p.business_group_id
138 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
139 -- Added for bug fix 5696117
140 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid,
141 pay_ie_archive_detail_pkg.get_paypathid) in (org.org_information8,' ')
142
143 /*and (org.org_information8= pay_ie_paypath_tape.g_pathid
144 or ' '=pay_ie_paypath_tape.g_pathid) --Bug No 3086034*/
145 union all
146 SELECT 'TYPE=P', c_xxxxx_type
147 ,'EMP_NAME=P' , 'null'
148 ,'EMPLOYEE_NSC=P', 'null'
149 ,'EMPLOYEE_ACC_NO=P', 'null'
150 ,'TRANSACTION_CODE=P','null'
151 ,'EMPLOYER_NSC=P', 'null'
152 ,'EMPLOYER_ACC_NO=P', 'null'
153 ,'PAY_AMOUNT=P', 0
154 ,'USER_NAME=P', 'null'
155 ,'USER_REFERNCE=P', 'null'
156 ,'ACC_CREDIT_NAME=P', 'null'
157 ,'FILE_FORMAT=P','null'
158 from dual
159 order by 1,2;
160
161
162 CURSOR CSR_PPH_CONTRA_RECORD
163 IS
164 SELECT /*+ ORDERED INDEX (ppa PAY_PAYROLL_ACTIONS_PK,
165 paa PAY_ASSIGNMENT_ACTIONS_N50,
166 ppp PAY_PRE_PAYMENTS_PK,
167 popm PAY_ORG_PAYMENT_METHODS_F_PK,
168 pppm PAY_PERSONAL_PAYMENT_METHO_PK,
169 a PER_ASSIGNMENTS_F_PK,
170 p PER_PEOPLE_F_PK ,
171 oea PAY_EXTERNAL_ACCOUNTS_PK,
172 pea PAY_EXTERNAL_ACCOUNTS_PK,
173 org HR_ORGANIZATION_INFORMATIO_FK2)
174 USE_NL(ppa,paa,ppp,popm,pppm,a,p,oea,pea,org) */
175 'TYPE=P', 'Z'
176 ,'EMP_NAME=P' , 'null'
177 ,'EMPLOYEE_ACC_NO=P', oea.segment4
178 ,'EMPLOYEE_NSC=P', oea.segment1
179 ,'TRANSACTION_CODE=P','17'
180 ,'PAY_AMOUNT=P', (sum(ppp.value)* 100)
181 ,'USER_NAME=P', oea.segment5
182 ,'USER_REFERNCE=P', org.org_information10
183 ,'EMPLOYER_NSC=P', oea.segment1
184 ,'EMPLOYER_ACC_NO=P', oea.segment4
185 ,'ACC_CREDIT_NAME=P', oea.segment5
186 ,'FILE_FORMAT=P', nvl(org.org_information11, 'AIB')
187 from
188 pay_payroll_actions ppa
189 , pay_assignment_actions paa
190 , pay_pre_payments ppp
191 , pay_org_payment_methods_f popm
192 , pay_personal_payment_methods_f pppm
193 , per_all_assignments_f a -- Bug No 3513042
194 , per_all_people_f p
195 , pay_external_accounts oea
196 , pay_external_accounts pea
197 , hr_organization_information org
198 where ppa.payroll_action_id = -- Bug No 3513042
199 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
200 and ppp.pre_payment_id = paa.pre_payment_id
201 and paa.payroll_action_id = ppa.payroll_action_id
202 and oea.external_account_id = popm.external_account_id
203 and popm.org_payment_method_id = ppp.org_payment_method_id
204 and pea.external_account_id = pppm.external_account_id
205 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
206 and paa.assignment_id = a.assignment_id
207 and a.person_id = p.person_id
208 --Added nvl function code for bug fix 3649139
209 and a.payroll_id = nvl(ppa.payroll_id,a.payroll_id ) -- Bug No 3513042
210 and ppa.effective_date between popm.effective_start_date and popm.effective_end_date
211 and ppa.effective_date between pppm.effective_start_date and pppm.effective_end_date
212 and ppa.effective_date between a.effective_start_date and a.effective_end_date
213 and ppa.effective_date between p.effective_start_date and p.effective_end_date
214 and org.organization_id (+) = p.business_group_id
215 and org.org_information_context(+) = 'IE_PAYPATH_INFORMATION'
216 -- Added for bug fix 5696117
217 and decode (org.org_information8,' ', pay_ie_archive_detail_pkg.get_paypathid,
218 pay_ie_archive_detail_pkg.get_paypathid) in (org.org_information8,' ')
219
220 /* and (org.org_information8= pay_ie_paypath_tape.g_pathid
221 or ' '=pay_ie_paypath_tape.g_pathid) --Bug No 3086034*/
222 group by 'TYPE=P', 'Z'
223 ,'EMPLOYEE_ACC_NO=P', oea.segment4
224 ,'EMPLOYEE_NSC=P', oea.segment1
225 ,'USER_NAME=P', oea.segment5
226 ,'USER_REFERNCE=P', org.org_information10
227 ,'FILE_FORMAT=P', nvl(org.org_information11, 'AIB');
228
229
230 /*PROCEDURE get_parameters(p_payroll_action_id IN NUMBER,
231 p_token_name IN VARCHAR2,
232 p_token_value OUT NOCOPY VARCHAR2);
233
234 */
235
236 PROCEDURE range_code(
237 p_payroll_action_id IN NUMBER,
238 p_sqlstr OUT NOCOPY VARCHAR2);
239 --
240 PROCEDURE assignment_action_code(
241 p_payroll_action_id IN NUMBER,
242 p_start_person_id IN NUMBER,
243 p_end_person_id IN NUMBER,
244 p_chunk_number IN NUMBER);
245
246
247 END pay_ie_paypath_tape;