175: l_address_line_3 hr_locations.address_line_3%TYPE ;
176: l_postal_code hr_locations.postal_code%TYPE ;
177: l_town_or_city hr_locations.town_or_city%TYPE ;
178: l_country hr_locations.country%TYPE ;
179: l_business_id hr_organization_information.org_information1%TYPE ;
180: l_Sal_subject_Wt NUMBER;
181: l_Sal_subject_Ts NUMBER;
182: l_Pay_subject_Wt NUMBER;
183: l_Pay_subject_ts NUMBER;
187: l_exem_ss_fee NUMBER;
188: l_ins_ss_fee NUMBER;
189: l_ts_deduction NUMBER;
190: l_vat_bal NUMBER;
191: l_y_number hr_organization_information.org_information1%TYPE ;
192: l_contact_person hr_organization_information.org_information1%TYPE ;
193: l_phone hr_organization_information.org_information1%TYPE ;
194: l_fax hr_organization_information.org_information1%TYPE ;
195: l_tax_office_ba pay_external_accounts.segment3%TYPE ;
188: l_ins_ss_fee NUMBER;
189: l_ts_deduction NUMBER;
190: l_vat_bal NUMBER;
191: l_y_number hr_organization_information.org_information1%TYPE ;
192: l_contact_person hr_organization_information.org_information1%TYPE ;
193: l_phone hr_organization_information.org_information1%TYPE ;
194: l_fax hr_organization_information.org_information1%TYPE ;
195: l_tax_office_ba pay_external_accounts.segment3%TYPE ;
196: l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
189: l_ts_deduction NUMBER;
190: l_vat_bal NUMBER;
191: l_y_number hr_organization_information.org_information1%TYPE ;
192: l_contact_person hr_organization_information.org_information1%TYPE ;
193: l_phone hr_organization_information.org_information1%TYPE ;
194: l_fax hr_organization_information.org_information1%TYPE ;
195: l_tax_office_ba pay_external_accounts.segment3%TYPE ;
196: l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
197: l_tax_office_name hr_organization_units.name%TYPE ;
190: l_vat_bal NUMBER;
191: l_y_number hr_organization_information.org_information1%TYPE ;
192: l_contact_person hr_organization_information.org_information1%TYPE ;
193: l_phone hr_organization_information.org_information1%TYPE ;
194: l_fax hr_organization_information.org_information1%TYPE ;
195: l_tax_office_ba pay_external_accounts.segment3%TYPE ;
196: l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
197: l_tax_office_name hr_organization_units.name%TYPE ;
198: l_employer_ba pay_external_accounts.segment3%TYPE ;
192: l_contact_person hr_organization_information.org_information1%TYPE ;
193: l_phone hr_organization_information.org_information1%TYPE ;
194: l_fax hr_organization_information.org_information1%TYPE ;
195: l_tax_office_ba pay_external_accounts.segment3%TYPE ;
196: l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
197: l_tax_office_name hr_organization_units.name%TYPE ;
198: l_employer_ba pay_external_accounts.segment3%TYPE ;
199: l_employer_pay_meth_id hr_organization_information.org_information1%TYPE ;
200: l_tax_office_id hr_organization_information.organization_id%TYPE ;
195: l_tax_office_ba pay_external_accounts.segment3%TYPE ;
196: l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
197: l_tax_office_name hr_organization_units.name%TYPE ;
198: l_employer_ba pay_external_accounts.segment3%TYPE ;
199: l_employer_pay_meth_id hr_organization_information.org_information1%TYPE ;
200: l_tax_office_id hr_organization_information.organization_id%TYPE ;
201: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
202: l_counter number := 0;
203: l_archive VARCHAR2(3);
196: l_tax_office_pay_meth_id hr_organization_information.org_information2%TYPE ;
197: l_tax_office_name hr_organization_units.name%TYPE ;
198: l_employer_ba pay_external_accounts.segment3%TYPE ;
199: l_employer_pay_meth_id hr_organization_information.org_information1%TYPE ;
200: l_tax_office_id hr_organization_information.organization_id%TYPE ;
201: l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
202: l_counter number := 0;
203: l_archive VARCHAR2(3);
204: l_reporting_date DATE;
216:
217:
218: /* Cursors */
219:
220: Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
221: IS
222: SELECT o1.name , hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION7
223: FROM hr_organization_units o1
224: , hr_organization_information hoi1
220: Cursor csr_Local_Unit_Details ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
221: IS
222: SELECT o1.name , hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION7
223: FROM hr_organization_units o1
224: , hr_organization_information hoi1
225: , hr_organization_information hoi2
226: WHERE o1.business_group_id =l_business_group_id
227: AND hoi1.organization_id = o1.organization_id
228: AND hoi1.organization_id = csr_v_local_unit_id
221: IS
222: SELECT o1.name , hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION7
223: FROM hr_organization_units o1
224: , hr_organization_information hoi1
225: , hr_organization_information hoi2
226: WHERE o1.business_group_id =l_business_group_id
227: AND hoi1.organization_id = o1.organization_id
228: AND hoi1.organization_id = csr_v_local_unit_id
229: AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
232: AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
233:
234: rg_Local_Unit_Details csr_Local_Unit_Details%rowtype;
235:
236: Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
237: IS
238: SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION11
239: FROM hr_organization_units o1
240: , hr_organization_information hoi1
236: Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
237: IS
238: SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION11
239: FROM hr_organization_units o1
240: , hr_organization_information hoi1
241: , hr_organization_information hoi2
242: WHERE o1.business_group_id =l_business_group_id
243: AND hoi1.organization_id = o1.organization_id
244: AND hoi1.organization_id = csr_v_legal_emp_id
237: IS
238: SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION11
239: FROM hr_organization_units o1
240: , hr_organization_information hoi1
241: , hr_organization_information hoi2
242: WHERE o1.business_group_id =l_business_group_id
243: AND hoi1.organization_id = o1.organization_id
244: AND hoi1.organization_id = csr_v_legal_emp_id
245: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
249:
250:
251: rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
252:
253: Cursor csr_Legal_Emp_Contact ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
254: IS
255: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
256: FROM hr_organization_units o1
257: , hr_organization_information hoi1
253: Cursor csr_Legal_Emp_Contact ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
254: IS
255: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
256: FROM hr_organization_units o1
257: , hr_organization_information hoi1
258: , hr_organization_information hoi2
259: , hr_organization_information hoi3
260: , hr_organization_information hoi4
261: WHERE o1.business_group_id =l_business_group_id
254: IS
255: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
256: FROM hr_organization_units o1
257: , hr_organization_information hoi1
258: , hr_organization_information hoi2
259: , hr_organization_information hoi3
260: , hr_organization_information hoi4
261: WHERE o1.business_group_id =l_business_group_id
262: AND hoi1.organization_id = o1.organization_id
255: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
256: FROM hr_organization_units o1
257: , hr_organization_information hoi1
258: , hr_organization_information hoi2
259: , hr_organization_information hoi3
260: , hr_organization_information hoi4
261: WHERE o1.business_group_id =l_business_group_id
262: AND hoi1.organization_id = o1.organization_id
263: AND hoi1.organization_id = csr_v_legal_emp_id
256: FROM hr_organization_units o1
257: , hr_organization_information hoi1
258: , hr_organization_information hoi2
259: , hr_organization_information hoi3
260: , hr_organization_information hoi4
261: WHERE o1.business_group_id =l_business_group_id
262: AND hoi1.organization_id = o1.organization_id
263: AND hoi1.organization_id = csr_v_legal_emp_id
264: AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
274: AND hoi4.org_information1 (+)= 'PERSON' ;
275:
276: rg_Legal_Emp_Contact csr_Legal_Emp_Contact%rowtype;
277:
278: Cursor csr_Local_Unit_contact ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
279: IS
280: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
281: FROM hr_organization_units o1
282: , hr_organization_information hoi1
278: Cursor csr_Local_Unit_contact ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
279: IS
280: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
281: FROM hr_organization_units o1
282: , hr_organization_information hoi1
283: , hr_organization_information hoi2
284: , hr_organization_information hoi3
285: , hr_organization_information hoi4
286: WHERE o1.business_group_id =l_business_group_id
279: IS
280: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
281: FROM hr_organization_units o1
282: , hr_organization_information hoi1
283: , hr_organization_information hoi2
284: , hr_organization_information hoi3
285: , hr_organization_information hoi4
286: WHERE o1.business_group_id =l_business_group_id
287: AND hoi1.organization_id = o1.organization_id
280: SELECT hoi4.ORG_INFORMATION2 contact_person , hoi3.ORG_INFORMATION2 phone ,hoi2.ORG_INFORMATION2 fax
281: FROM hr_organization_units o1
282: , hr_organization_information hoi1
283: , hr_organization_information hoi2
284: , hr_organization_information hoi3
285: , hr_organization_information hoi4
286: WHERE o1.business_group_id =l_business_group_id
287: AND hoi1.organization_id = o1.organization_id
288: AND hoi1.organization_id = csr_v_local_unit_id
281: FROM hr_organization_units o1
282: , hr_organization_information hoi1
283: , hr_organization_information hoi2
284: , hr_organization_information hoi3
285: , hr_organization_information hoi4
286: WHERE o1.business_group_id =l_business_group_id
287: AND hoi1.organization_id = o1.organization_id
288: AND hoi1.organization_id = csr_v_local_unit_id
289: AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
299: AND hoi4.org_information1 (+)= 'PERSON' ;
300:
301: rg_Local_Unit_contact csr_Local_Unit_contact%rowtype;
302:
303: Cursor csr_Local_Unit_addr ( csr_v_local_unit_id hr_organization_information.ORGANIZATION_ID%TYPE)
304: IS
305: SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
306: hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
307: FROM hr_organization_units o1
305: SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
306: hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
307: FROM hr_organization_units o1
308: , hr_locations hoi1
309: ,hr_organization_information hoi2
310: WHERE o1.business_group_id = l_business_group_id
311: AND hoi1.location_id = o1.location_id
312: AND hoi2.organization_id = o1.organization_id
313: AND hoi2.organization_id = csr_v_local_unit_id
315: AND hoi2.org_information_context = 'CLASS' ;
316:
317: rg_Local_Unit_addr csr_Local_Unit_addr%rowtype;
318:
319: Cursor csr_Legal_Emp_addr ( csr_v_legal_emp_id hr_organization_information.ORGANIZATION_ID%TYPE)
320: IS
321: SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
322: hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
323: FROM hr_organization_units o1
321: SELECT hoi1.ADDRESS_LINE_1 , hoi1.ADDRESS_LINE_2 , hoi1.ADDRESS_LINE_3 ,
322: hoi1.POSTAL_CODE , hoi1.TOWN_OR_CITY , hoi1.COUNTRY
323: FROM hr_organization_units o1
324: , hr_locations hoi1
325: ,hr_organization_information hoi2
326: WHERE o1.business_group_id = l_business_group_id
327: AND hoi1.location_id = o1.location_id
328: AND hoi2.organization_id = o1.organization_id
329: AND hoi2.organization_id = csr_v_legal_emp_id
332:
333:
334: rg_Legal_Emp_addr csr_Legal_Emp_addr%rowtype;
335:
336: Cursor csr_Tax_Office_Details ( csr_v_tax_office_id hr_organization_information.ORGANIZATION_ID%TYPE)
337: IS
338: SELECT o1.name ,hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION2
339: FROM hr_organization_units o1
340: , hr_organization_information hoi1
336: Cursor csr_Tax_Office_Details ( csr_v_tax_office_id hr_organization_information.ORGANIZATION_ID%TYPE)
337: IS
338: SELECT o1.name ,hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION2
339: FROM hr_organization_units o1
340: , hr_organization_information hoi1
341: , hr_organization_information hoi2
342: , hr_organization_information hoi3
343: WHERE o1.business_group_id =l_business_group_id
344: AND hoi1.organization_id = o1.organization_id
337: IS
338: SELECT o1.name ,hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION2
339: FROM hr_organization_units o1
340: , hr_organization_information hoi1
341: , hr_organization_information hoi2
342: , hr_organization_information hoi3
343: WHERE o1.business_group_id =l_business_group_id
344: AND hoi1.organization_id = o1.organization_id
345: AND hoi1.organization_id = csr_v_tax_office_id
338: SELECT o1.name ,hoi2.ORG_INFORMATION1 ,hoi2.ORG_INFORMATION2
339: FROM hr_organization_units o1
340: , hr_organization_information hoi1
341: , hr_organization_information hoi2
342: , hr_organization_information hoi3
343: WHERE o1.business_group_id =l_business_group_id
344: AND hoi1.organization_id = o1.organization_id
345: AND hoi1.organization_id = csr_v_tax_office_id
346: AND hoi1.org_information1 = 'PROV_TAX_OFFICE'
395: pay_assignment_actions act1,
396: pay_action_interlocks pai,
397: per_all_assignments_f paaf,
398: hr_soft_coding_keyflex hsck,
399: hr_organization_information hoi--,
400: -- pay_payrolls_f ppa
401: where appa.action_type in ('R', 'Q')
402: and act.payroll_action_id = appa.payroll_action_id
403: and act.source_action_id is null -- Master Action