[Home] [Help]
PACKAGE: APPS.PAY_AE_EFT
Source
1 package PAY_AE_EFT as
2 /* $Header: pyaeeftp.pkh 120.0 2005/10/03 22:31:04 spendhar noship $ */
3 level_cnt NUMBER;
4 l_id_header number;
5 l_id_body number;
6 l_id_footer number;
7 l_payment_method_id number(15);
8 --
9 FUNCTION get_customer_formula_header (
10 p_Date_Earned IN DATE
11 ,p_payment_method_id IN number
12 ,p_business_group_id IN number
13 ,p_payroll_id IN number
14 ,p_payroll_action_id IN number
15 ,p_creation_date IN VARCHAR2
16 ,p_process_date IN VARCHAR2
17 ,p_count IN VARCHAR2
18 ,p_sumt IN VARCHAR2
19 ,p_sumc IN VARCHAR2
20 ,p_sumd IN VARCHAR2
21 ,p_write_text1 OUT NOCOPY VARCHAR2
22 ,p_write_text2 OUT NOCOPY VARCHAR2
23 ,p_write_text3 OUT NOCOPY VARCHAR2
24 ,p_write_text4 OUT NOCOPY VARCHAR2
25 ,p_write_text5 OUT NOCOPY VARCHAR2
26 ,p_report_text1 OUT NOCOPY VARCHAR2
27 ,p_report_text2 OUT NOCOPY VARCHAR2
28 ,p_report_text3 OUT NOCOPY VARCHAR2
29 ,p_report_text4 OUT NOCOPY VARCHAR2
30 ,p_report_text5 OUT NOCOPY VARCHAR2
31 ,p_report_text6 OUT NOCOPY VARCHAR2
32 ,p_report_text7 OUT NOCOPY VARCHAR2
33 ,p_report_text8 OUT NOCOPY VARCHAR2
34 ,p_report_text9 OUT NOCOPY VARCHAR2
35 ,p_report_text10 OUT NOCOPY VARCHAR2
36 ,p_bank_code IN VARCHAR2
37 ,p_employer_code IN VARCHAR2
38 ,p_tape_type IN VARCHAR2
39 ) return varchar2;
40 --
41 --
42 FUNCTION get_customer_formula_footer (
43 p_Date_Earned IN DATE
44 ,p_payment_method_id IN number
45 ,p_business_group_id IN number
46 ,p_payroll_id IN number
47 ,p_payroll_action_id IN number
48 ,p_creation_date IN VARCHAR2
49 ,p_process_date IN VARCHAR2
50 ,p_count IN VARCHAR2
51 ,p_sumt IN VARCHAR2
52 ,p_sumc IN VARCHAR2
53 ,p_sumd IN VARCHAR2
54 ,p_write_text1 OUT NOCOPY VARCHAR2
55 ,p_write_text2 OUT NOCOPY VARCHAR2
56 ,p_write_text3 OUT NOCOPY VARCHAR2
57 ,p_write_text4 OUT NOCOPY VARCHAR2
58 ,p_write_text5 OUT NOCOPY VARCHAR2
59 ,p_report_text1 OUT NOCOPY VARCHAR2
60 ,p_report_text2 OUT NOCOPY VARCHAR2
61 ,p_report_text3 OUT NOCOPY VARCHAR2
62 ,p_report_text4 OUT NOCOPY VARCHAR2
63 ,p_report_text5 OUT NOCOPY VARCHAR2
64 ,p_report_text6 OUT NOCOPY VARCHAR2
65 ,p_report_text7 OUT NOCOPY VARCHAR2
66 ,p_report_text8 OUT NOCOPY VARCHAR2
67 ,p_report_text9 OUT NOCOPY VARCHAR2
68 ,p_report_text10 OUT NOCOPY VARCHAR2
69 ,p_bank_code IN VARCHAR2
70 ,p_employer_code IN VARCHAR2
71 ,p_tape_type IN VARCHAR2
72 ) return varchar2;
73 --
74 --
75 FUNCTION get_customer_formula_body (
76 p_assignment_id IN number,
77 p_business_group_id IN number,
78 p_per_pay_method_id IN number,
79 p_date_earned IN date,
80 p_payroll_id IN number,
81 p_payroll_action_id IN number,
82 p_assignment_action_id IN number,
83 p_organization_id IN number,
84 p_tax_unit_id IN number,
85 p_amount IN varchar2,
86 p_first_name IN varchar2,
87 p_last_name IN varchar2,
88 p_initials IN varchar2,
89 p_emp_no IN varchar2,
90 p_asg_no IN varchar2,
91 p_count IN varchar2,
92 p_sumt IN varchar2
93 ,p_sumc IN VARCHAR2
94 ,p_sumd IN VARCHAR2
95 ,p_write_text1 OUT NOCOPY VARCHAR2
96 ,p_write_text2 OUT NOCOPY VARCHAR2
97 ,p_write_text3 OUT NOCOPY VARCHAR2
98 ,p_write_text4 OUT NOCOPY VARCHAR2
99 ,p_write_text5 OUT NOCOPY VARCHAR2
100 ,p_report_text1 OUT NOCOPY VARCHAR2
101 ,p_report_text2 OUT NOCOPY VARCHAR2
102 ,p_report_text3 OUT NOCOPY VARCHAR2
103 ,p_report_text4 OUT NOCOPY VARCHAR2
104 ,p_report_text5 OUT NOCOPY VARCHAR2
105 ,p_report_text6 OUT NOCOPY VARCHAR2
106 ,p_report_text7 OUT NOCOPY VARCHAR2
107 ,p_report_text8 OUT NOCOPY VARCHAR2
108 ,p_report_text9 OUT NOCOPY VARCHAR2
109 ,p_report_text10 OUT NOCOPY VARCHAR2
110 ,p_local_nationality IN VARCHAR2
111 ,p_bank_code IN VARCHAR2
112 ,p_employer_code IN VARCHAR2
113 ,p_trancode IN VARCHAR2
114 ,p_tape_type IN VARCHAR2
115 ) return varchar2;
116 --
117 PROCEDURE run_formula(p_formula_id IN NUMBER
118 ,p_effective_date IN DATE
119 ,p_inputs IN ff_exec.inputs_t
120 ,p_outputs IN OUT NOCOPY ff_exec.outputs_t);
121
122
123 /********************************************************
124 * Cursor to fetch header record information *
125 ********************************************************/
126 CURSOR CSR_AE_EFT_HEADER IS
127 SELECT 'CREATION_DATE=P'
128 ,to_char(ppa.effective_date, 'DDMMYYYY')
129 ,'PROCESS_DATE=P'
130 ,to_char(fnd_date.canonical_to_date(pay_ae_general.get_parameter(
131 ppa.legislative_parameters,
132 'PROCESS_DATE')), 'DDMMYYYY')
133 ,'DATE_EARNED=C'
134 ,to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
135 ,'ORG_PAY_METHOD_ID=C'
136 ,ppa.org_payment_method_id
137 ,'BUSINESS_GROUP_ID=C'
138 ,ppa.business_group_id
139 ,'PAYROLL_ID=C'
140 ,ppa.payroll_id
141 ,'PAYROLL_ACTION_ID=C'
142 ,ppa.payroll_action_id
143 ,'COUNT1=P'
144 ,pay_ae_general.get_count
145 ,'SUMT=P'
146 ,nvl(to_char(pay_ae_general.get_total_sum * 100),'0')
147 ,'SUMC=P'
148 ,nvl(to_char(pay_ae_general.get_credit_sum * 100),'0')
149 ,'SUMD=P'
150 ,nvl(to_char(pay_ae_general.get_debit_sum * 100),'0')
151 ,'TAPE_TYPE=P'
152 ,nvl(pay_ae_general.get_parameter(ppa.legislative_parameters,'TAPE_TYPE'),' ')
153 FROM pay_payroll_actions ppa
154 WHERE ppa.payroll_action_id =
155 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
156 AND EXISTS (
157 SELECT 1
158 FROM pay_assignment_actions pas
159 ,pay_pre_payments ppp
160 WHERE pas.payroll_action_id = ppa.payroll_action_id
161 AND ppp.pre_payment_id = pas.pre_payment_id
162 AND ppp.value <> 0 );
163
164
165 /********************************************************
166 * Cursor to fetch batch/payment record information *
167 ********************************************************/
168 CURSOR CSR_AE_EFT_BODY IS
169 SELECT 'AMOUNT=P'
170 ,ppp.value * 100
171 ,'TRANCODE=P'
172 ,decode(pay_ae_general.chk_tran_code(ppp.value)
173 ,'Y','C'
174 ,'N', 'D')
175 ,'FIRST_NAME=P'
176 , substr(pef.full_name,1,40)
177 ,'LAST_NAME=P'
178 , substr(pef.last_name,1,35)
179 ,'INITIALS=P'
180 ,substr(pef.per_information1,1,35)
181 ,'EMP_NO=P'
182 , nvl(pef.employee_number,' ')
183 ,'ASG_NO=P'
184 ,decode(pay_ae_general.chk_multiple_assignments(ppa.effective_date,paf.person_id)
185 ,'Y',nvl(paf.assignment_number,' ')
186 ,'N', ' ')
187 ,'ASSIGNMENT_ID=C' , paf.assignment_id
188 ,'BUSINESS_GROUP_ID=C' , paf.business_group_id
189 ,'PER_PAY_METHOD_ID=C' , ppp.personal_payment_method_id
190 ,'ORG_PAY_METHOD_ID=C' , ppa.org_payment_method_id
191 ,'DATE_EARNED=C' , to_char(ppa.effective_date, 'YYYY/MM/DD HH24:MI:SS')
192 ,'PAYROLL_ID=C' , ppa.payroll_id
193 ,'PAYROLL_ACTION_ID=C' , ppa.payroll_action_id
194 ,'ASSIGNMENT_ACTION_ID=C', ppa.org_payment_method_id
195 ,'ORGANIZATION_ID=C' , paf.organization_id
196 ,'TAX_UNIT_ID=C' , paa.tax_unit_id
197 ,'LOCAL_NATIONALITY=P'
198 ,pay_ae_general.get_parameter(ppa.legislative_parameters,
199 'LOCAL_NATIONALITY')
200 FROM per_assignments_f paf
201 ,per_people_f pef
202 ,pay_pre_payments ppp
203 ,pay_assignment_actions paa
204 ,pay_payroll_actions ppa
205 WHERE paa.payroll_action_id =
206 pay_magtape_generic.get_parameter_value('PAYROLL_ACTION_ID')
207 AND paa.pre_payment_id = ppp.pre_payment_id
208 AND paa.payroll_action_id = ppa.payroll_action_id
209 AND paa.assignment_id = paf.assignment_id
210 AND paf.person_id = pef.person_id
211 AND ppp.value <> 0
212 AND ppa.effective_date BETWEEN paf.effective_start_date
213 AND paf.effective_end_date
214 AND ppa.effective_date BETWEEN pef.effective_start_date
215 AND pef.effective_end_date
216 ORDER BY decode(pay_ae_general.get_parameter(legislative_parameters,
217 'SORT_ORDER'),
218 'NAME', substr(pef.last_name || ' ' || pef.first_name,1,50),
219 'NUMBER', pef.employee_number, null);
220 END PAY_AE_EFT;
221