[Home] [Help]
PACKAGE: APPS.PAY_NL_PAYFILE_SEPA
Source
1 PACKAGE PAY_NL_PAYFILE_SEPA AUTHID CURRENT_USER as
2 /* $Header: pynlsepa.pkh 120.1.12020000.2 2012/11/09 11:56:06 sgmaram ship $ */
3 level_cnt NUMBER;
4 --
5
6 FUNCTION RAISE_WARNING(p_flag IN VARCHAR2, p_message IN VARCHAR2) return number;
7
8 FUNCTION NL_GET_MESSAGE_IN_LOG(p_message IN VARCHAR2) RETURN VARCHAR2;
9
10
11 FUNCTION get_employer_address
12 (p_org_id IN NUMBER,
13 p_bg_id IN NUMBER,
14 p_house_number IN OUT NOCOPY VARCHAR2,
15 p_house_no_add IN OUT NOCOPY VARCHAR2,
16 p_street_name IN OUT NOCOPY VARCHAR2,
17 p_line1 IN OUT NOCOPY VARCHAR2,
18 p_line2 IN OUT NOCOPY VARCHAR2,
19 p_line3 IN OUT NOCOPY VARCHAR2,
20 p_city IN OUT NOCOPY VARCHAR2,
21 p_country IN OUT NOCOPY VARCHAR2,
22 p_postal_code IN OUT NOCOPY VARCHAR2
23 )
24 RETURN NUMBER;
25
26 FUNCTION get_payee_details(p_assignment_id IN NUMBER
27 ,p_business_group_id IN NUMBER
28 ,p_per_pay_method_id IN NUMBER
29 ,p_date_earned IN DATE
30 ,p_house_number OUT NOCOPY Varchar2
31 ,p_house_no_add OUT NOCOPY Varchar2
32 ,p_street_name OUT NOCOPY Varchar2
33 ,p_line1 OUT NOCOPY Varchar2
34 ,p_line2 OUT NOCOPY Varchar2
35 ,p_line3 OUT NOCOPY Varchar2
36 ,p_postal_code OUT NOCOPY Varchar2
37 ,p_city OUT NOCOPY Varchar2
38 ,p_country OUT NOCOPY Varchar2
39 ) RETURN VARCHAR2;
40
41 PROCEDURE cache_formula(p_formula_name IN VARCHAR2
42 ,p_business_group_id IN NUMBER
43 ,p_effective_date IN DATE
44 ,p_formula_id IN OUT NOCOPY NUMBER
45 ,p_formula_exists IN OUT NOCOPY BOOLEAN
46 ,p_formula_cached IN OUT NOCOPY BOOLEAN
47 );
48
49 PROCEDURE run_formula(p_formula_id IN NUMBER
50 ,p_effective_date IN DATE
51 ,p_formula_name IN VARCHAR2
52 ,p_inputs IN ff_exec.inputs_t
53 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t);
54
55 FUNCTION GET_REPORT_TOTAL(p_report_total OUT NOCOPY NUMBER
56 ,p_report_count OUT NOCOPY NUMBER)
57 RETURN VARCHAR2;
58
59 --14779885
60 FUNCTION to_char_fm(p_input_text IN varchar2, p_input_format IN varchar2) RETURN varchar2;
61
62 /********************************************************
63 * Cursor to fetch header record information *
64 ********************************************************/
65 CURSOR CSR_NL_HDR_FTR IS
66 SELECT 'TRANSFER_PARA_PMNT1=P'
67 --To print the closing tag </PstlAdr> correctlty in case of multiple Employers.
68 ,NULL
69 ,'CREATION_DATE=P'
70 ,to_char(ppa.effective_date, 'RRRR-MM-DD"T"HH24:MI:SS')
71 ,'PROCESS_DATE=P'
72 ,to_char(fnd_date.canonical_to_date(pay_nl_general.get_parameter(
73 ppa.legislative_parameters,
74 'PROCESS_DATE')), 'RRRR-MM-DD')
75 ,'BATCH_BOOKING=P'
76 ,decode(pay_nl_general.get_parameter(ppa.legislative_parameters,'BATCH_BOOKING'),'Y','TRUE','N','FALSE','TRUE')
77 ,'TRANSFER_ORG_PAY_METHOD_ID=P'
78 ,to_char(ppa.org_payment_method_id)
79 ,'DATE_EARNED=C'
80 ,to_char(ppa.effective_date, 'RRRR/MM/DD HH24:MI:SS')
81 ,'ORG_PAY_METHOD_ID=C'
82 ,ppa.org_payment_method_id
83 ,'TRANSFER_ER_COUNT=P'
84 ,'0'
85 ,'TRANSFER_ER_TOTAL=P'
86 ,'0'
87 ,'TRANSFER_ERROR_FLAG=P'
88 ,'N'
89 ,'INSTR_IDENT=P'
90 ,NVL(pay_nl_general.get_parameter(ppa.legislative_parameters,'INSTR_IDENT'),' ')
91 FROM pay_payroll_actions ppa
92 WHERE ppa.payroll_action_id =
93 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
94 AND EXISTS (
95 SELECT 1
96 FROM pay_assignment_actions pas
97 ,pay_pre_payments ppp
98 WHERE pas.payroll_action_id = ppa.payroll_action_id
99 AND ppp.pre_payment_id = pas.pre_payment_id
100 AND ppp.value > 0 );
101
102 /********************************************************
103 * Cursor to fetch payment record information *
104 ********************************************************/
105 CURSOR CSR_NL_PMNT_INFO IS
106 SELECT 'PARA_PMNT1=P'
107 --To print the closing tag </PstlAdr> correctlty in case of multiple Employers.
108 ,to_char(hou.organization_id)||to_char(pea.external_account_id)||to_char(hoi.org_information4)
109 ,'REQ_EXEC_DATE=P'
110 ,to_char(fnd_date.canonical_to_date(pay_nl_general.get_parameter(
111 ppa.legislative_parameters,
112 'PROCESS_DATE')), 'RRRR-MM-DD')
113 ,'DBTR_NAME=P'
114 ,hou.name
115 ,'TRANSFER_ORGANIZATION_ID=P'
116 ,to_char(hou.organization_id)
117 ,'EXTERNAL_ACCOUNT_ID=P'
118 ,to_char(pea.external_account_id)
119 ,'IBAN=P'
120 ,NVL(pea.SEGMENT10,' ')
121 ,'BIC=P'
122 ,nvl(pea.SEGMENT9,' ')
123 ,'BG_ID=P'
124 ,to_char(ppa.business_group_id)
125 ,'TAX_REG_NO=P'
126 ,hoi.org_information4
127 ,'TRANSFER_ER_COUNT_LAST=P'
128 ,'0'
129 ,'TRANSFER_ER_TOTAL_LAST=P'
130 ,'0'
131 ,'TRANSFER_ERROR_FLAG1=P'
132 ,'N'
133 from
134 hr_all_organization_units hou,
135 hr_organization_information hoi,
136 pay_org_payment_methods_f popmf,
137 PAY_ORG_PAY_METHOD_USAGES_F popmu,
138 PAY_ALL_PAYROLLS_F papf,
139 PAY_EXTERNAL_ACCOUNTS pea,
140 PAY_PAYROLL_ACTIONS ppa
141 where
142 ppa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID') and
143 hou.business_group_id = ppa.business_group_id and
144 hoi.org_information_context = 'NL_ORG_INFORMATION' and
145 hou.organization_id = hoi.organization_id and
146 popmu.org_payment_method_id = pay_magtape_generic.get_parameter_value('TRANSFER_ORG_PAY_METHOD_ID') and
147 ppa.effective_date between popmu.effective_start_date and popmu.effective_end_date and
148 popmf.org_payment_method_id = popmu.org_payment_method_id and
149 popmf.business_group_id = hou.business_group_id and
150 ppa.effective_date between popmf.effective_start_date and popmf.effective_end_date and
151 papf.payroll_id = NVL(ppa.payroll_id,papf.payroll_id) and
152 papf.payroll_id = popmu.payroll_id and
153 papf.business_group_id = ppa.business_group_id and
154 papf.prl_information_category = 'NL' and
155 papf.prl_information1 = hou.organization_id and
156 ppa.effective_date between papf.effective_start_date and papf.effective_end_date and
157 pea.external_account_id = popmf.external_account_id and
158 --restricting employer who doesn't have any assignment to pay.
159 EXISTS
160 (
161 SELECT 1
162 FROM
163 pay_assignment_actions pas
164 ,pay_pre_payments ppp
165 ,per_all_assignments_f paaf
166 ,pay_all_payrolls_f ppf
167 WHERE pas.payroll_action_id = ppa.payroll_action_id
168 AND ppp.pre_payment_id = pas.pre_payment_id
169 AND ppp.value > 0
170 AND paaf.assignment_id = pas.assignment_id
171 AND ppf.payroll_id = paaf.payroll_id
172 AND ppf.prl_information1 = papf.prl_information1
173 AND ppf.prl_information_category = 'NL'
174 AND ppa.effective_date between paaf.effective_start_date and paaf.effective_end_date
175 AND ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date
176 )
177 Group by to_char(fnd_date.canonical_to_date(pay_nl_general.get_parameter(
178 ppa.legislative_parameters,
179 'PROCESS_DATE')), 'RRRR-MM-DD')
180 ,hou.name
181 ,hou.organization_id
182 ,pea.external_account_id
183 ,NVL(pea.SEGMENT10,' ')
184 ,nvl(pea.SEGMENT9,' ')
185 ,ppa.business_group_id
186 ,hoi.org_information4;
187
188 /********************************************************
189 * Cursor to fetch transaction record information *
190 ********************************************************/
191 CURSOR CSR_NL_TRAN_INFO IS
192 SELECT 'SEPA_CATG=P'
193 ,NVL(min(pay_nl_general.get_parameter(ppa.legislative_parameters,'SEPA_CATEGORY')),'SALA')
194 ,'AMOUNT=P'
195 ,to_char (sum (ppp.value * 100), 'FM999999999999999999999009')
196 ,'FIRST_NAME=P'
197 , substr(min(pef.first_name),1,70)
198 ,'LAST_NAME=P'
199 , substr(min(pef.last_name),1,70)
200 ,'INITIALS=P'
201 ,substr(min(pef.per_information1),1,70)
202 ,'EMP_NO=P'
203 , nvl(min(pef.employee_number),' ')
204 ,'ASG_NO=P'
205 ,decode(min(pay_nl_general.chk_multiple_assignments(ppa.effective_date,paf.person_id))
206 ,'Y',nvl(min(paf.assignment_number),' ')
207 ,'N', ' ')
208 ,'ASSIGNMENT_ID=C'
209 , min(paf.assignment_id)
210 ,'BUSINESS_GROUP_ID=C'
211 , min(paf.business_group_id)
212 ,'PER_PAY_METHOD_ID=C'
213 ,min(ppp.personal_payment_method_id)
214 ,'DATE_EARNED=C'
215 ,to_char(min(ppa.effective_date), 'RRRR/MM/DD HH24:MI:SS')
216 ,'PRE_PAYMENT_ID=P'
217 ,min(ppp.pre_payment_id)
218 ,'IBAN_EE=P'
219 ,NVL(min(pea.SEGMENT10),' ')
220 ,'BIC_EE=P'
221 ,nvl(min(pea.SEGMENT9),' ')
222 ,'NATIONAL_IDENTIFIER=P'
223 ,min(pef.NATIONAL_IDENTIFIER)
224 FROM per_all_assignments_f paf
225 ,per_all_people_f pef
226 ,pay_all_payrolls_f ppf
227 ,pay_pre_payments ppp
228 ,pay_assignment_actions paa
229 ,pay_payroll_actions ppa
230 ,pay_personal_payment_methods_f ppmf
231 ,pay_external_accounts pea
232 WHERE paa.payroll_action_id = pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
233 AND paa.pre_payment_id = ppp.pre_payment_id
234 AND paa.payroll_action_id = ppa.payroll_action_id
235 AND PPP.personal_payment_method_id = ppmf.personal_payment_method_id
236 AND paa.assignment_id = paf.assignment_id
237 AND paf.payroll_id = NVL(ppa.payroll_id,paf.payroll_id)
238 AND paf.person_id = pef.person_id
239 AND ppf.payroll_id = paf.payroll_id
240 AND ppa.effective_date BETWEEN ppf.effective_start_date
241 AND ppf.effective_end_date
242 AND ppf.prl_information_category = 'NL'
243 AND ppf.prl_information1 = pay_magtape_generic.get_parameter_value('TRANSFER_ORGANIZATION_ID')
244 AND ppp.value > 0
245 AND pea.external_account_id = ppmf.external_account_id
246 AND ppa.effective_date BETWEEN paf.effective_start_date
247 AND paf.effective_end_date
248 AND ppa.effective_date BETWEEN pef.effective_start_date
249 AND pef.effective_end_date
250 AND ppa.effective_date BETWEEN ppmf.effective_start_date
251 AND ppmf.effective_end_date
252 GROUP BY
253 pef.person_id, pea.SEGMENT10, pea.SEGMENT9
254 ORDER BY decode(min(pay_nl_general.get_parameter(legislative_parameters,
255 'SORT_ORDER')),
256 'NAME', substr(min(pef.last_name) || ' ' || min(pef.first_name),1,30),
257 'NUMBER', min(pef.employee_number), null);
258
259 END PAY_NL_PAYFILE_SEPA;