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