1 PACKAGE BODY PAY_US_PPM_HOOK AS
2 /* $Header: payusppmhook.pkb 120.0.12010000.5 2010/01/20 07:03:16 mikarthi noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : PAY_US_PPM_HOOK
21 File Name : payusppmhook.pkb
22
23 Description : This package is called from the BEFORE INSERT/UPDATE
24 User Hooks. The following are the functionalities present
25 in User Hook
26
27 1. Validates the Bank Account Number in the Bank Details KFF
28
29 2. Validates the IAT Transit Code in the Further Information DFF
30
31 3. Validates the Tranks Code in the Bank Details KFF
32
33
34 Change List
35 -----------
36 Name Date Version Bug Text
37 -------------- ----------- ------- ------- -----------------------------
38 mikarthi 05-Dec-2008 115.0 8806003 Initial Version
39 mikarthi 20-Jan-2010 115.4 8904560 Included validation for DFI
40 Qualifier and Country Code.
41
42 *****************************************************************************/
43
44 /* *****************************************************************************
45 Name : VALIDATE_BANK_DETAILS
46 Scope : LOCAL
47 Description : This Function validates the Transit Code and Bank AccountNum
48 During Insert operation
49 ******************************************************************************/
50
51 procedure VALIDATE_BANK_DETAILS(P_ORG_PAYMENT_METHOD_ID in NUMBER
52 ,P_SEGMENT3 in VARCHAR2
53 ,P_SEGMENT4 in VARCHAR2
54 ,p_ppm_information1 in VARCHAR2
55 ,p_ppm_information2 in VARCHAR2
56 ,p_ppm_information3 in VARCHAR2
57 ,p_effective_date in date) is
58
59 l_is_foreign_trans VARCHAR2(30);
60 transit_code NUMBER;
61
62 begin
63
64 select PMETH_INFORMATION9
65 INTO l_is_foreign_trans
66 from pay_org_payment_methods_f where ORG_PAYMENT_METHOD_ID = P_ORG_PAYMENT_METHOD_ID
67 AND p_effective_date BETWEEN effective_start_date
68 AND effective_end_date;
69
70 hr_utility.trace('l_is_foreign_trans : ' || l_is_foreign_trans);
71
72 if l_is_foreign_trans <> 'Y' or l_is_foreign_trans is null then
73 --Not IAT transaction
74 if length(P_SEGMENT3) > 17 then
75 hr_utility.trace('Test 1');
76 hr_utility.set_message(801, 'PAY_US_NACHA_INVALD_BANK_ACCNT');
77 hr_utility.raise_error;
78 end if;
79
80 else
81 --IAT transaction
82 if P_SEGMENT4 <> '000000000' then
83
84 hr_utility.set_message(801, 'PAY_US_IAT_INVALD_TRANSIT_CODE');
85 hr_utility.raise_error;
86
87 end if;
88
89 if (p_ppm_information1 is null) or
90 (p_ppm_information2 is null) or
91 (p_ppm_information3 is null) then
92
93 hr_utility.set_message(801, 'PAY_US_IAT_NO_TRANSIT_CODE');
94 hr_utility.raise_error;
95 end if;
96
97 end if;
98
99
100 end VALIDATE_BANK_DETAILS;
101
102 /* *****************************************************************************
103 Name : UPDATE_BANK_DETAILS
104 Scope : LOCAL
105 Description : This Function validates the Transit Code and Bank AccountNum
106 During Update operation
107 ******************************************************************************/
108
109 procedure UPDATE_BANK_DETAILS(P_PERSONAL_PAYMENT_METHOD_ID in NUMBER
110 ,P_SEGMENT3 in VARCHAR2
111 ,P_SEGMENT4 in VARCHAR2
112 ,p_ppm_information1 in VARCHAR2
113 ,p_ppm_information2 in VARCHAR2
114 ,p_ppm_information3 in VARCHAR2
115 ,p_effective_date in date) is
116
117 l_is_foreign_trans VARCHAR2(30);
118 transit_code NUMBER;
119 L_SEGMENT3 PAY_EXTERNAL_ACCOUNTS.SEGMENT3%TYPE;
120 L_SEGMENT4 PAY_EXTERNAL_ACCOUNTS.SEGMENT4%TYPE;
121 L_PPM_INFORMATION1 PAY_PERSONAL_PAYMENT_METHODS_F.PPM_INFORMATION1%TYPE;
122 L_PPM_INFORMATION2 PAY_PERSONAL_PAYMENT_METHODS_F.PPM_INFORMATION2%TYPE;
123 L_PPM_INFORMATION3 PAY_PERSONAL_PAYMENT_METHODS_F.PPM_INFORMATION3%TYPE;
124
125 CURSOR CSR_BANK_SEGMENTS IS
126 SELECT EXT.SEGMENT3, EXT.SEGMENT4
127 FROM PAY_PERSONAL_PAYMENT_METHODS_F PPM, PAY_EXTERNAL_ACCOUNTS EXT
128 WHERE PPM.PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
129 AND EXT.EXTERNAL_ACCOUNT_ID = PPM.EXTERNAL_ACCOUNT_ID
130 AND P_EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
131 AND PPM.EFFECTIVE_END_DATE;
132
133 CURSOR CSR_PPM_INFORMATION IS
134 SELECT PPM.PPM_INFORMATION1,
135 PPM.PPM_INFORMATION2,
136 PPM.PPM_INFORMATION3
137 FROM PAY_PERSONAL_PAYMENT_METHODS_F PPM
138 WHERE PPM.PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
139 AND P_EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE
140 AND PPM.EFFECTIVE_END_DATE;
141
142 begin
143
144 select popm.PMETH_INFORMATION9
145 INTO l_is_foreign_trans
146 from pay_org_payment_methods popm, pay_Personal_payment_methods pppm
147 where popm.ORG_PAYMENT_METHOD_ID = pppm.ORG_PAYMENT_METHOD_ID
148 and pppm.PERSONAL_PAYMENT_METHOD_ID = P_PERSONAL_PAYMENT_METHOD_ID
149 AND p_effective_date BETWEEN popm.effective_start_date
150 AND popm.effective_end_date;
151
152 IF (p_segment3 = hr_api.g_varchar2 or p_segment4 = hr_api.g_varchar2) then
153
154 OPEN CSR_BANK_SEGMENTS;
155 FETCH CSR_BANK_SEGMENTS INTO L_SEGMENT3, L_SEGMENT4;
156 CLOSE CSR_BANK_SEGMENTS;
157
158 if (p_segment3 <> hr_api.g_varchar2) then
159 l_segment3 := p_segment3;
160 end if;
161
162 if (p_segment4 <> hr_api.g_varchar2) then
163 l_segment4 := p_segment4;
164 end if;
165
166 end if;
167
168
169 if l_is_foreign_trans <> 'Y' or l_is_foreign_trans is null then
170 --Not IAT transaction
171 if length(l_segment3) > 17 then
172 hr_utility.trace('Test 1');
173 hr_utility.set_message(801, 'PAY_US_NACHA_INVALD_BANK_ACCNT');
174 hr_utility.raise_error;
175 end if;
176
177 else
178 --IAT transaction
179 if l_segment4 <> '000000000' then
180
181 hr_utility.set_message(801, 'PAY_US_IAT_INVALD_TRANSIT_CODE');
182 hr_utility.raise_error;
183
184 end if;
185
186 if (p_ppm_information1 = hr_api.g_varchar2) then
187 OPEN CSR_PPM_INFORMATION;
188 FETCH CSR_PPM_INFORMATION INTO L_PPM_INFORMATION1, L_PPM_INFORMATION2, L_PPM_INFORMATION3;
189 CLOSE CSR_PPM_INFORMATION;
190 ELSE
191 L_PPM_INFORMATION1 := P_PPM_INFORMATION1;
192 L_PPM_INFORMATION2 := P_PPM_INFORMATION2;
193 L_PPM_INFORMATION3 := P_PPM_INFORMATION3;
194 end if;
195
196 if (L_PPM_INFORMATION1 is null) or
197 (L_PPM_INFORMATION2 is null) or
198 (L_PPM_INFORMATION3 is null) then
199
200 hr_utility.set_message(801, 'PAY_US_IAT_NO_TRANSIT_CODE');
201 hr_utility.raise_error;
202 end if;
203
204 end if;
205
206 end UPDATE_BANK_DETAILS;
207
208 end PAY_US_PPM_HOOK;
[Home] [Help]