DBA Data[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;