[Home] [Help]
PACKAGE: APPS.PAY_IE_PAYFILE_SEPA
Source
1 PACKAGE PAY_IE_PAYFILE_SEPA AUTHID CURRENT_USER as
2 /* $Header: pyiesepa.pkh 120.1.12020000.2 2013/02/27 06:07:44 rsahai ship $ */
3 level_cnt NUMBER;
4 --
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 FUNCTION BIC(p_bic_code IN VARCHAR2
12 ,p_bic_meaning OUT NOCOPY VARCHAR2)
13 RETURN VARCHAR2;
14
15
16 /* Function To Fetch Address */
17 FUNCTION get_employer_address
18 (p_org_id IN NUMBER,
19 p_bg_id IN NUMBER,
20 p_line1 IN OUT NOCOPY VARCHAR2,
21 p_line2 IN OUT NOCOPY VARCHAR2,
22 p_line3 IN OUT NOCOPY VARCHAR2,
23 p_country IN OUT NOCOPY VARCHAR2,
24 p_postal_code IN OUT NOCOPY VARCHAR2,
25 p_geo_code IN OUT NOCOPY VARCHAR2
26 )
27 RETURN NUMBER;
28
29
30 FUNCTION get_payee_details(p_assignment_id IN NUMBER
31 ,p_business_group_id IN NUMBER
32 ,p_per_pay_method_id IN NUMBER
33 ,p_date_earned IN DATE
34 ,p_line1 OUT NOCOPY Varchar2
35 ,p_line2 OUT NOCOPY Varchar2
36 ,p_line3 OUT NOCOPY Varchar2
37 ,p_country OUT NOCOPY Varchar2
38 ,p_postal_code OUT NOCOPY Varchar2
39 ,p_geo_code OUT NOCOPY Varchar2
40 )
41 RETURN VARCHAR2;
42
43
44 FUNCTION GET_REPORT_TOTAL(p_report_total OUT NOCOPY NUMBER
45 ,p_report_count OUT NOCOPY NUMBER)
46 RETURN VARCHAR2;
47
48
49 /********************************************************
50 * Cursor to fetch header record information *
51 ********************************************************/
52 CURSOR CSR_IE_HDR_FTR IS
53 SELECT DISTINCT 'TRANSFER_PARA_PMNT1=P',NULL
54 --To print the closing tag </PstlAdr> correctlty in case of multiple Employers.
55 --,'CREATION_DATE=P', to_char(ppa.effective_date, 'RRRR-MM-DD"T"HH24:MI:SS')
56 ,'CREATION_DATE=P', to_char(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE')), 'RRRR-MM-DD"T"HH24:MI:SS')
57 ,'PROCESS_DATE=P' ,to_char(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE')), 'RRRR-MM-DD')
58 ,'BATCH_BOOKING=P',decode(pay_ie_payfile_sepa.get_parameter(ppa.legislative_parameters,'BATCH_BOOKING'),'Y','TRUE','N','FALSE','TRUE')
59 ,'TRANSFER_ORG_PAY_METHOD_ID=P',to_char(ppa.org_payment_method_id)
60 ,'DATE_EARNED=C',to_char(ppa.effective_date, 'RRRR/MM/DD HH24:MI:SS')
61 ,'ORG_PAY_METHOD_ID=C',ppa.org_payment_method_id
62 ,'TRANSFER_ER_COUNT=P','0'
63 ,'TRANSFER_ER_TOTAL=P','0'
64 ,'CONSOLIDATION_SET_NAME=P', PAY_IE_PAYPATH_TAPE.get_consolidation_set
65 --15971893
66 ,'TRANSFER_SENDER_ID=P', popmf.PMETH_INFORMATION1
67 ,'TRANSFER_BANK_FORMAT=P', NVL(popmf.PMETH_INFORMATION2,'NO_FORMAT')
68 ,'TRANSFER_BATCH_REF_NO=P', pay_ie_payfile_sepa.get_parameter(ppa.legislative_parameters,'BATCH_REF_NO')
69 ,'TRANSFER_PARTY_NAME=P', hou.name
70 ,'TRANSFER_CATG_PURP=P', NVL(pay_ie_payfile_sepa.get_parameter(ppa.legislative_parameters,'SEPA_CATEGORY'),'SALA')
71 --15971893
72 FROM pay_payroll_actions ppa
73 ,pay_org_payment_methods_f popmf --15971893
74 ,hr_all_organization_units hou --15971893
75 WHERE ppa.payroll_action_id =
76 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
77 AND ppa.org_payment_method_id = popmf.org_payment_method_id --15971893
78 AND hou.business_group_id = ppa.business_group_id --15971893
79 AND hou.organization_id = ppa.business_group_id --15971893
80 AND EXISTS (SELECT 1
81 FROM pay_assignment_actions pas
82 ,pay_pre_payments ppp
83 WHERE pas.payroll_action_id = ppa.payroll_action_id
84 AND ppp.pre_payment_id = pas.pre_payment_id
85 AND ppp.value > 0 );
86
87 /********************************************************
88 * Cursor to fetch payment record information *
89 ********************************************************/
90 CURSOR CSR_IE_PMNT_INFO IS
91 SELECT distinct 'PARA_PMNT1=P' ,to_char(pea.external_account_id)
92 --To print the closing tag </PstlAdr> correctlty in case of multiple Employers.
93 ,'REQ_EXEC_DATE=P',to_char(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE')), 'RRRR-MM-DD')
94 ,'DBTR_NAME=P',hou.name
95 ,'TRANSFER_ORGANIZATION_ID=P',to_char(hou.organization_id)
96 ,'EXTERNAL_ACCOUNT_ID=P',to_char(pea.external_account_id)
97 ,'IBAN=P',NVL(pea.SEGMENT7,' ')
98 ,'BIC=P',nvl(pea.SEGMENT6,' ')
99 ,'BG_ID=P',to_char(ppa.business_group_id)
100 ,'TRANSFER_ER_COUNT_LAST=P' ,'0'
101 ,'TRANSFER_ER_TOTAL_LAST=P' ,'0'
102 ,'TRANSFER_ORG_PAY_METH_ID=P',popmf.org_payment_method_id
103 ,'TRANSFER_ORG_PAY_METH_N=P',popmf.org_payment_method_name
104 from hr_all_organization_units hou,
105 --hr_organization_information hoi, --15971893
106 pay_org_payment_methods_f popmf,
107 PAY_ORG_PAY_METHOD_USAGES_F popmu,
111 pay_payment_types ppt
108 PAY_ALL_PAYROLLS_F papf,
109 PAY_EXTERNAL_ACCOUNTS pea,
110 PAY_PAYROLL_ACTIONS ppa,
112 -- hr_soft_coding_keyflex hsck
113 where ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
114 and hou.business_group_id = ppa.business_group_id
115 --and hoi.org_information_context(+) = 'IE_PAYPATH_INFORMATION' --15971893
116 --and hou.organization_id = hoi.organization_id --15971893
117 and hou.organization_id = ppa.business_group_id --15971893
118 and popmu.org_payment_method_id = nvl(pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD_ID'),popmu.org_payment_method_id)
119 and popmf.payment_type_id = ppt.payment_type_id
120 and ppt.payment_type_name = 'SEPA'
121 and ppa.effective_date between popmu.effective_start_date and popmu.effective_end_date
122 and popmf.org_payment_method_id = popmu.org_payment_method_id
123 and popmf.business_group_id = hou.business_group_id
124 and ppa.effective_date between popmf.effective_start_date and popmf.effective_end_date
125 and papf.payroll_id = NVL(ppa.payroll_id,papf.payroll_id)
126 and papf.payroll_id = popmu.payroll_id
127 and papf.business_group_id = ppa.business_group_id
128 --and papf.prl_information_category = 'NL'
129 --and papf.prl_information1 = hou.organization_id
130 --and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
131 --and hsck.segment4 = hou.organization_id
132 and ppa.effective_date between papf.effective_start_date and papf.effective_end_date
133 and pea.external_account_id = popmf.external_account_id
134 and --restricting employer who doesn't have any assignment to pay.
135 EXISTS
136 (
137 SELECT 1
138 FROM pay_assignment_actions pas
139 ,pay_pre_payments ppp
140 ,per_all_assignments_f paaf
141 ,pay_all_payrolls_f ppf
142 -- ,hr_soft_coding_keyflex hsck
143 WHERE pas.payroll_action_id = ppa.payroll_action_id
144 AND ppp.pre_payment_id = pas.pre_payment_id
145 AND ppp.value > 0
146 AND paaf.assignment_id = pas.assignment_id
147 AND ppf.payroll_id = paaf.payroll_id
148 --AND ppf.prl_information1 = papf.prl_information1
149 --AND ppf.prl_information_category = 'NL'
150 -- and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
151 -- and hsck.segment4 = hou.organization_id
152 AND ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
153 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
154 )
155 Group by to_char(fnd_date.canonical_to_date(pay_magtape_generic.get_parameter_value('PROCESSING_DATE')), 'RRRR-MM-DD')
156 ,hou.name
157 ,hou.organization_id
158 ,pea.external_account_id
159 ,NVL(pea.SEGMENT7,' ')
160 ,nvl(pea.SEGMENT6,' ')
161 ,ppa.business_group_id
162 ,popmf.org_payment_method_id
163 ,popmf.org_payment_method_name;
164
165
166 /********************************************************
167 * Cursor to fetch transaction record information *
168 ********************************************************/
169 CURSOR CSR_IE_TRAN_INFO IS
170 SELECT distinct 'SEPA_CATG=P',NVL(min(pay_ie_payfile_sepa.get_parameter(ppa.legislative_parameters,'SEPA_CATEGORY')),'SALA')
171 ,'AMOUNT=P' ,sum(ppp.value*100)
172 ,'FIRST_NAME=P', substr(min(pef.first_name),1,70)
173 ,'LAST_NAME=P', substr(min(pef.last_name),1,70)
174 ,'INITIALS=P',substr(min(pef.per_information1),1,70)
175 ,'EMP_NO=P', nvl(min(pef.employee_number),' ')
176 ,'ASSIGNMENT_ID=C', min(paf.assignment_id)
177 ,'BUSINESS_GROUP_ID=C', min(paf.business_group_id)
178 ,'PER_PAY_METHOD_ID=C',min(ppp.personal_payment_method_id)
179 ,'DATE_EARNED=C',to_char(min(ppa.effective_date), 'RRRR/MM/DD HH24:MI:SS')
180 ,'PRE_PAYMENT_ID=P',min(ppp.pre_payment_id)
181 ,'IBAN_EE=P',NVL(min(pea.SEGMENT7),' ')
182 ,'BIC_EE=P',nvl(min(pea.SEGMENT6),' ')
183 ,'NATIONAL_IDENTIFIER=P',min(pef.NATIONAL_IDENTIFIER)
184 FROM per_all_assignments_f paf
185 ,per_all_people_f pef
186 ,pay_all_payrolls_f ppf
187 ,pay_pre_payments ppp
188 ,pay_assignment_actions paa
189 ,pay_payroll_actions ppa
190 ,pay_personal_payment_methods_f ppmf
191 ,pay_external_accounts pea
192 --,hr_soft_coding_keyflex hsck
193 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
194 AND paa.pre_payment_id = ppp.pre_payment_id
195 AND paa.payroll_action_id = ppa.payroll_action_id
196 AND PPP.personal_payment_method_id = ppmf.personal_payment_method_id
197 AND ppmf.org_payment_method_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METH_ID')
198 AND paa.assignment_id = paf.assignment_id
199 AND paf.payroll_id = NVL(ppa.payroll_id,paf.payroll_id)
200 AND paf.person_id = pef.person_id
201 AND ppf.payroll_id = paf.payroll_id
202 AND ppa.effective_date BETWEEN ppf.effective_start_date
203 AND ppf.effective_end_date
204 --AND ppf.prl_information_category = 'NL'
205 --AND ppf.prl_information1 = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
206 --and ppf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
207 --and hsck.segment4 = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
208 AND ppp.value > 0
209 AND pea.external_account_id = ppmf.external_account_id
210 AND ppa.effective_date BETWEEN paf.effective_start_date
211 AND paf.effective_end_date
212 AND ppa.effective_date BETWEEN pef.effective_start_date
216 GROUP BY pef.person_id,
213 AND pef.effective_end_date
214 AND ppa.effective_date BETWEEN ppmf.effective_start_date
215 AND ppmf.effective_end_date
217 pea.SEGMENT7,
218 pea.SEGMENT6;
219
220
221 END PAY_IE_PAYFILE_SEPA;