[Home] [Help]
PACKAGE: APPS.PAY_FI_EFT
Source
1 package PAY_FI_EFT AUTHID CURRENT_USER as
2 /* $Header: pyfieftp.pkh 120.1 2006/03/14 01:12:44 dbehera noship $ */
3
4 level_cnt NUMBER;
5
6 FUNCTION get_parameter (
7 p_parameter_string in varchar2
8 ,p_token in varchar2
9 ,p_segment_number in number default null) RETURN varchar2;
10
11
12 /********************************************************
13 * Cursor to fetch Header record information *
14 ********************************************************/
15
16 CURSOR CSR_FI_EFT_HEADER IS
17 select 'PROCESS_DATE=P',
18 to_char(fnd_date.canonical_to_date(pay_fi_eft.get_parameter
19 (ppa.legislative_parameters,'PROCESS_DATE')),'YYYYMMDD')
20 FROM pay_payroll_actions ppa
21 WHERE ppa.payroll_action_id =
22 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
23 AND EXISTS (
24 SELECT 1
25 FROM pay_assignment_actions pas
26 ,pay_pre_payments ppp
27 WHERE pas.payroll_action_id = ppa.payroll_action_id
28 AND ppp.pre_payment_id = pas.pre_payment_id );
29
30
31 /********************************************************
32 * Cursor to fetch Body record information *
33 ********************************************************/
34 CURSOR CSR_FI_EFT_BODY IS
35 SELECT /*+ INDEX( hsck , HR_SOFT_CODING_KEYFLEX_PK) */ 'PAYER_CODE=P',hoi2.org_information1
36 ,'PAY_DATE=P',to_char(fnd_date.canonical_to_date(pay_fi_eft.get_parameter
37 (ppa.legislative_parameters,'PROCESS_DATE')),'YYYYMMDD')
38 ,'PAYER_NAME=P',substr(o1.name,1,16)
39 ,'PAYER_INFO=P',lpad(nvl(pay_fi_eft.get_parameter(ppa.legislative_parameters,'PAYER_INFO'),' '),10,' ')
40 ,'PAYEE_CODE=P',pef.national_identifier
41 ,'PAYEE_NAME=P',substr(pef.full_name,1,19)
42 ,'PAYEE_ACT_NO=P',pea.segment3
43 ,'PAY_AMOUNT=P' , ppp.value * 100
44 ,'ASSIGNMENT_ID=C' , paf.assignment_id
45 ,'BUSINESS_GROUP_ID=C' , paf.business_group_id
46 ,'PER_PAY_METHOD_ID=C' , ppp.personal_payment_method_id
47 ,'ORG_PAY_METHOD_ID=C' , ppa.org_payment_method_id
48 ,'DATE_EARNED=C' , to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
49 ,'PAYROLL_ID=C' , ppa.payroll_id
50 ,'PAYROLL_ACTION_ID=C' , ppa.payroll_action_id
51 ,'ASSIGNMENT_ACTION_ID=C', paa.assignment_action_id
52 ,'ORGANIZATION_ID=C' , paf.organization_id
53 ,'TAX_UNIT_ID=C' , paa.tax_unit_id
54 from
55 pay_payroll_actions ppa
56 ,pay_assignment_actions paa
57 ,per_all_assignments_f paf
58 ,pay_pre_payments ppp
59 ,pay_personal_payment_methods_f pppm
60 ,per_all_people_f pef
61 ,pay_external_accounts pea
62 ,hr_soft_coding_keyflex hsck
63 ,hr_all_organization_units o1
64 ,hr_organization_information hoi1
65 ,hr_organization_information hoi2
66 ,hr_organization_information hoi3
67 ,hr_organization_information hoi4
68 where paa.payroll_action_id =
69 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
70 and ppa.business_group_id =o1.business_group_id
71 and paf.business_group_id=o1.business_group_id
72 and paa.pre_payment_id = ppp.pre_payment_id
73 and paa.payroll_action_id = ppa.payroll_action_id
74 and paa.assignment_id = paf.assignment_id
75 and paf.person_id = pef.person_id
76 and pppm.personal_payment_method_id = ppp.personal_payment_method_id
77 and pppm.external_account_id = pea.external_account_id
78 and pppm.assignment_id = paf.assignment_id
79 and ppa.effective_date between pppm.effective_start_date
80 and pppm.effective_end_date
81 and ppa.effective_date between paf.effective_start_date
82 and paf.effective_end_date
83 and ppa.effective_date between pef.effective_start_date
84 and pef.effective_end_date
85 and hoi1.organization_id = o1.organization_id
86 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
87 and hoi1.org_information_context = 'CLASS'
88 and o1.organization_id = hoi2.organization_id
89 and hoi2.org_information_context='FI_LEGAL_EMPLOYER_DETAILS'
90 and hoi3.org_information1 = 'FI_LOCAL_UNIT'
91 and hoi3.org_information_context = 'CLASS'
92 and hoi3.organization_id = hoi4.org_information1
93 and hoi4.org_information_context='FI_LOCAL_UNITS'
94 and hoi4.organization_id = hoi1.organization_id
95 and paf.SOFT_CODING_KEYFLEX_ID = hsck.SOFT_CODING_KEYFLEX_ID
96 and hsck.segment2 = TO_CHAR(hoi3.organization_id)
97 order by decode(pay_fi_eft.get_parameter(legislative_parameters,
98 'SORT_ORDER'),
99 'NAME', substr(pef.last_name || ' ' || pef.first_name,1,50),
100 'NUMBER', pef.employee_number, null);
101
102 END PAY_FI_EFT;