[Home] [Help]
PACKAGE: APPS.PAY_SE_EFT
Source
1 PACKAGE PAY_SE_EFT AUTHID CURRENT_USER AS
2 /* $Header: pyseeftp.pkh 120.5.12000000.3 2007/07/23 05:37:14 psingla noship $ */
3 level_cnt NUMBER;
4
5 FUNCTION get_parameter(p_payroll_action_id NUMBER,
6 p_token_name VARCHAR2) RETURN VARCHAR2;
7 /********************************************************
8 * Cursor to fetch Header1 record information *
9 ********************************************************/
10 CURSOR CSR_SE_PP_HEADER1 is
11 SELECT
12 'START=P'
13 ,1
14 ,'PROCESS_DATE=P'
15 ,fnd_date.canonical_to_date(pay_se_eft.get_parameter(ppa.payroll_action_id,
16 'PROCESS_DATE'))
17 from pay_payroll_actions ppa
18 where ppa.payroll_action_id =pay_magtape_generic.get_parameter_value
19 ('PAYROLL_ACTION_ID');
20
21 /********************************************************
22 * Cursor to fetch Footer1 record information *
23 ********************************************************/
24
25 /*CURSOR CSR_SE_PP_FOOTER1 is
26 SELECT
27 'END=P', 1 from dual;*/
28
29 /********************************************************
30 * Cursor to fetch Header record information *
31 ********************************************************/
32
33 CURSOR CSR_SE_PP_HEADER IS
34 select distinct 'CREATION_DATE=P'
35 ,to_char(ppa.creation_date, 'YYMMDD')
36 ,'CUSTOMER_NO=P'
37 ,pay_se_eft.get_parameter(ppa.payroll_action_id,'CUSTOMER_NO')
38 ,'ADDRESS1=P'
39 , HL.ADDRESS_LINE_1
40 ,'ADDRESS2=P'
41 , HL.ADDRESS_LINE_2
42 ,'ADDRESS3=P'
43 , HL.ADDRESS_LINE_3
44 ,'COUNTRY=P'
45 ,hr_general.DECODE_TERRITORY(hl.style)
46 ,'POSTAL_CODE=P'
47 , hl.postal_code
48 ,'BANK_NAME=P'
49 ,pea.segment1
50 ,'ACCOUNT_NO=P'
51 ,pea.segment2
52 ,'ORGANIZATION_NO=P'
53 ,hoi2.org_information2
54 ,'ORGANIZATION_NAME=P'
55 ,ou.Name
56 ,'TRANSFER_ORGANIZATION_ID=P'
57 ,ou.organization_id
58 ,'PHONE=P'
59 ,RPAD(NVL(substr(hoi4.org_information3,1,10),' '),10,' ')
60 ,'PAYROLL=P'
61 ,pap.payroll_Name
62 ,'PAYMENT_PERIOD=P'
63 ,fnd_date.canonical_to_date(pay_se_eft.get_parameter(ppa.payroll_action_id,'START_DATE'))||' - '
64 ||fnd_date.canonical_to_date(pay_se_eft.get_parameter(ppa.payroll_action_id,'PROCESS_DATE'))
65 ,'BANKGIRO_NO=P'
66 ,pay_se_eft.get_parameter(ppa.payroll_action_id,'BANKGIRO_NO')
67 from
68 pay_payroll_actions ppa,
69 pay_assignment_actions paa,
70 per_all_assignments_f paf,
71 pay_pre_payments ppp,
72 hr_organization_units ou,
73 hr_locations_all hl,
74 PAY_ORG_PAYMENT_METHODS_F ppm,
75 pay_external_Accounts pea,
76 hr_soft_coding_keyflex hsk,
77 pay_all_payrolls_f pap,
78 hr_organization_information hoi2,
79 hr_organization_information hoi3,
80 hr_organization_information hoi4
81 where ppa.payroll_action_id =pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
82 and ppa.business_group_id =ou.business_group_id
83 and paf.business_group_id=ou.business_group_id
84 and ppp.pre_payment_id = paa.pre_payment_id
85 and paa.payroll_action_id = ppa.payroll_action_id
86 and paa.assignment_id = paf.assignment_id
87 and ou.location_id=hl.location_id
88 --and paf.location_id=hl.location_id
89 and ppm.external_account_id =pea.external_account_id
90 and ppm.org_payment_method_id = ppa.org_payment_method_id
91 and ppa.effective_date between
92 paf.effective_start_date and paf.effective_end_date
93 and ppa.effective_date between
94 ppm.effective_start_date and ppm.effective_end_date
95 and ou.business_group_id=pay_se_eft.get_parameter(ppa.payroll_action_id,
96 'BUSINESS_GROUP_ID')
97 and hoi2.organization_id=ou.organization_id
98 and hoi2.ORG_INFORMATION_CONTEXT='SE_LEGAL_EMPLOYER_DETAILS'
99 and hoi2.organization_id = hoi3.organization_id
100 and hoi3.ORG_INFORMATION_CONTEXT='CLASS'
101 and hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
102 and hoi3.organization_id=hoi4.organization_id
103 and hoi4.organization_id = ou.organization_id
104 and hoi4.ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
105 and hoi4.org_information1='PHONE'
106 and hoi4.org_information_id=(select
107 min(org_information_id)from
108 hr_organization_information
109 where organization_id = ou.organization_id
110 and ORG_INFORMATION_CONTEXT = 'SE_ORG_CONTACT_DETAILS'
111 and org_information1='PHONE' )
112 and hsk.SOFT_CODING_KEYFLEX_ID = paf.SOFT_CODING_KEYFLEX_ID
113 and hsk.enabled_flag = 'Y'
114 and ppa.payroll_id=pap.payroll_id
115 and ppa.payroll_id=paf.payroll_id;
116
117 /********************************************************
118 * Cursor to fetch Body record information *
119 ********************************************************/
120
121 CURSOR CSR_SE_PP_BODY IS
122 SELECT
123 'AMOUNT=P',ppp.value*100
124 ,'PAYEE_CODE=P',pef.NATIONAL_IDENTIFIER
125 ,'PAYEE_ACT_NO=P',pea.segment2
126 ,'PAYEE_NAME=P',pef.full_name
127 ,'PROCESS_DATE=P' ,pay_se_eft.get_parameter(ppa.payroll_action_id,'PROCESS_DATE')
128 ,'EMPLOYEE_NO=P',pef.Employee_Number
129 FROM pay_pre_payments ppp,
130 pay_org_payment_methods_f pop,
131 pay_personal_payment_methods_f ppm,
132 pay_external_accounts pea,
133 pay_payroll_actions ppa,
134 pay_assignment_actions paa,
135 per_all_assignments_f paf,
136 per_all_people_f pef,
137 hr_soft_coding_keyflex hsk
138 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value(
139 'PAYROLL_ACTION_ID')
140 and ppa.payroll_action_id = paa.payroll_action_id
141 and paa.assignment_id = paf.assignment_id
142 and paf.payroll_id = nvl(ppa.payroll_id,paf.payroll_id )
143 and ppa.effective_date between paf.effective_start_date and paf.effective_end_date
144 and paf.person_id = pef.person_id
145 and ppa.effective_date between pef.effective_start_date and pef.effective_end_date
146 and ppp.pre_payment_id = paa.pre_payment_id
147 and ppp.org_payment_method_id = pop.org_payment_method_id
148 and ppa.effective_date between pop.effective_start_date and pop.effective_end_date
149 and ppp.personal_payment_method_id = ppm.personal_payment_method_id
150 and ppm.external_account_id = pea.external_account_id
151 and ppa.effective_date between ppm.effective_start_date and ppm.effective_end_date
152 and hsk.SOFT_CODING_KEYFLEX_ID = paf.SOFT_CODING_KEYFLEX_ID
153 and hsk.enabled_flag = 'Y'
154 and hsk.segment2 in
155 (
156 select hoi2.org_information1
157 from HR_ORGANIZATION_UNITS o1
158 , HR_ORGANIZATION_INFORMATION hoi1
159 , HR_ORGANIZATION_INFORMATION hoi2
160 WHERE o1.business_group_id = ppa.business_group_id
161 and o1.organization_id = hoi1.organization_id
162 and hoi1.ORG_INFORMATION_CONTEXT='CLASS'
163 and hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
164 and hoi1.organization_id=pay_magtape_generic.get_parameter_value(
165 'TRANSFER_ORGANIZATION_ID')
166 and hoi1.organization_id = hoi2.organization_id
167 and hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
168 );
169
170
171 END PAY_SE_EFT;