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