[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AP_VALIDATE_COLLECTION
Source
1 package body JL_BR_AP_VALIDATE_COLLECTION as
2 /* $Header: jlbrpvcb.pls 120.10.12020000.2 2013/02/21 17:42:09 mbarrett ship $ */
3
4 /*---------------------------------------------------------------------------*/
5 /*<<<<< JL_BR_AP_VALIDATE_COLL_DOC >>>>>*/
6 /*---------------------------------------------------------------------------*/
7 PROCEDURE jl_br_ap_validate_coll_doc (
8 e_cnab_currency_code IN VARCHAR2,
9 e_arrears_code IN VARCHAR2,
10 e_accounting_balancing_segment IN VARCHAR2,
11 e_set_of_books_id IN NUMBER,
12 e_drawee_name IN VARCHAR2,
13 e_drawee_inscription_type IN NUMBER,
14 e_drawee_inscription_number IN VARCHAR2,
15 e_drawee_bank_code IN VARCHAR2,
16 e_drawee_branch_code IN VARCHAR2,
17 e_drawee_account IN VARCHAR2,
18 e_transferor_name IN VARCHAR2,
19 e_transf_inscription_type IN NUMBER,
20 e_transf_inscription_number IN VARCHAR2,
21 e_transferor_bank_code IN VARCHAR2,
22 e_transferor_branch_code IN VARCHAR2,
23 e_arrears_date IN DATE,
24 e_arrears_interest IN NUMBER,
25 e_barcode IN VARCHAR2,
26 e_electronic_format_flag IN VARCHAR2,
27 s_currency_code OUT NOCOPY VARCHAR2,
28 s_vendor_site_id OUT NOCOPY NUMBER,
29 s_error_code IN OUT NOCOPY VARCHAR2
30 )IS
31 x_aux VARCHAR2(1);
32 x_name jl_br_ap_int_collect.drawee_name%TYPE;
33 x_register_type jl_br_ap_int_collect.drawee_inscription_type%TYPE;
34 x_inscription_number jl_br_ap_int_collect.drawee_inscription_number%TYPE;
35 x_bank_branch_id ce_bank_branches_v.branch_party_id%TYPE;
36 x_vendor_name po_vendors.vendor_name%TYPE;
37 x_aux1 NUMBER;
38 x_aux2 NUMBER;
39
40 l_return_status VARCHAR2(100);
41 l_msg_data VARCHAR2(1000);
42 -- l_ledger_info xle_businessinfo_grp.le_ledger_rec_type;
43 x_comp_name varchar2(200);
44 x_registration_number varchar2(100);
45
46 CURSOR Comp IS
47 Select etb.establishment_name compname
48 ,etb.registration_number
49 ,etb.legal_entity_id
50 From
51 xle_establishment_v etb
52 ,xle_bsv_associations bsv
53 ,gl_ledger_le_v gl
54 Where
55 etb.legal_entity_id = gl.legal_entity_id
56 And bsv.legal_parent_id = etb.legal_entity_id
57 And etb.establishment_id = bsv.legal_construct_id
58 And bsv.entity_name = e_accounting_balancing_segment
59 And gl.ledger_id = e_set_of_books_id
60 AND etb.establishment_name = e_drawee_name;
61
62 BEGIN
63 s_error_code := '00';
64 /*--------------------------------------------------------------------*/
65 /* Validate the currency code */
66 /*--------------------------------------------------------------------*/
67 /* Modified for Brazil:Bank Transfer Currency Issue On 09/03/99 */
68 /* BEGIN
69 SELECT currency_code
70 INTO s_currency_code
71 FROM FND_CURRENCIES_VL
72 WHERE substr(global_attribute1,1,15) = e_cnab_currency_code;
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 s_error_code:='01';
76 GOTO fim;
77 END; */
78
79 -- Bug 4715379
80 s_currency_code := jl_zz_sys_options_pkg.get_bank_transfer_currency;
81 /*--------------------------------------------------------------------*/
82 /* Validate arrears code */
83 /*--------------------------------------------------------------------*/
84 BEGIN
85 SELECT 'Y'
86 INTO x_aux
87 FROM sys.dual
88 WHERE EXISTS (
89 SELECT null
90 FROM FND_LOOKUPS
91 WHERE lookup_type = 'JLBR_ARREARS_CODE'
92 AND lookup_code = e_arrears_code );
93 EXCEPTION
94 WHEN NO_DATA_FOUND THEN
95 s_error_code:='02';
96 GOTO fim;
97 END;
98 /*--------------------------------------------------------------------*/
99 /* Get the drawee information (company) using */
100 /* accounting_balancing_segment and set_of_books_id */
101 /*--------------------------------------------------------------------*/
102
103
104 BEGIN
105 /*
106 XLE_BUSINESSINFO_GRP.Get_Ledger_Info
107 (x_return_status =>l_return_status, --OUT VARCHAR2
108 x_msg_data =>l_msg_data, --OUT VARCHAR2
109 P_Ledger_id =>e_set_of_books_id, --IN NUMBER
110 P_BSV =>e_accounting_balancing_segment, --IN VARCHAR2
111 x_ledger_info =>l_ledger_info); --OUT LE_ledger_Rec_Type
112 */
113
114 For Cinfo In Comp Loop
115 x_comp_name := Cinfo.compname;
116 x_registration_number := Cinfo.registration_number;
117 End Loop;
118 --bug8808666 Start
119 /*if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
120 s_error_code:='03';
121 GOTO fim;*/
122 --bug8808666 End
123 IF UPPER(e_drawee_name) <> UPPER(x_comp_name)
124 THEN
125 s_error_code:= '04';
126 GOTO fim;
127 -- ELSIF e_drawee_inscription_type <> x_register_type
128 -- THEN
129 -- s_error_code:= '05';
130 -- GOTO fim;
131 ELSIF e_drawee_inscription_number <> x_registration_number
132 THEN
133 s_error_code:= '06';
134 GOTO fim;
135 END IF;
136
137 BEGIN
138 SELECT '1'
139 INTO x_register_type
140 FROM fnd_lookups
141 WHERE lookup_code = e_drawee_inscription_type
142 AND lookup_type = 'JLBR_INSCRIPTION_TYPE'
143 AND NVL(end_date_active,sysdate + 1) > sysdate;
144 EXCEPTION
145 WHEN OTHERS THEN
146 s_error_code := '05';
147 GOTO fim;
148 END;
149 END;
150
151
152 /*--------------------------------------------------------------------*/
153 /* Check the drawee bank and branch numbers */
154 /*--------------------------------------------------------------------*/
155
156 BEGIN
157 SELECT branch_party_id
158 INTO x_bank_branch_id
159 FROM ce_bank_branches_v
160 WHERE bank_number = e_drawee_bank_code
161 AND branch_number = e_drawee_branch_code
162 AND bank_home_country = 'BR';
163 EXCEPTION
164 WHEN NO_DATA_FOUND THEN
165 s_error_code:='07';
166 GOTO fim;
167 END;
168
169 /*----------------------------------------------------------------------*/
170 /* Check the drawee bank account number */
171 /* Drawee Account is considered to be Company account, that is Internal */
172 /* account. */
173 /*----------------------------------------------------------------------*/
174
175 BEGIN
176 SELECT 'Y'
177 INTO x_aux
178 FROM sys.dual
179 WHERE EXISTS (
180 SELECT null
181 FROM ce_bank_accounts
182 WHERE bank_branch_id = x_bank_branch_id
183 AND bank_account_num = e_drawee_account );
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 s_error_code:='08';
187 GOTO fim;
188 END;
189 /*--------------------------------------------------------------------*/
190 /* Using transferor inscription type and transferor inscription number*/
191 /* get and validate the transferor data */
192 /*--------------------------------------------------------------------*/
193 BEGIN
194 IF e_transf_inscription_type = 1 /* CPF */
195 THEN /* Considering 3 zeros on the left */
196 SELECT povs.vendor_site_id,
197 substr(pov.vendor_name,1,240)
198 INTO s_vendor_site_id,
199 x_vendor_name
200 FROM po_vendor_sites povs,
201 po_vendors pov
202 WHERE povs.global_attribute9 = e_transf_inscription_type
203 AND povs.global_attribute10 = substr(e_transf_inscription_number,4,9)
204 AND povs.global_attribute12 = substr(e_transf_inscription_number,13,2)
205 AND pov.vendor_id = povs.vendor_id
206 -- Bug 16343574 Start
207 AND trunc(nvl(povs.inactive_date,sysdate+1)) > trunc(sysdate);
208 -- Bug 16343574 End;
209
210 ELSIF e_transf_inscription_type = 2 /* CGC */
211 OR e_transf_inscription_type = 99 /* Others */
212 THEN /* Include one zero on the left */
213 SELECT povs.vendor_site_id,
214 substr(pov.vendor_name,1,240)
215 INTO s_vendor_site_id,
216 x_vendor_name
217 FROM po_vendor_sites povs,
218 po_vendors pov
219 WHERE povs.global_attribute9 = e_transf_inscription_type
220 AND povs.global_attribute10 = '0'||substr(e_transf_inscription_number,1,8)
221 AND povs.global_attribute11 = substr(e_transf_inscription_number,9,4)
222 AND povs.global_attribute12 = substr(e_transf_inscription_number,13,2)
223 AND pov.vendor_id = povs.vendor_id
224 -- Bug 16343574 Start
225 AND trunc(nvl(povs.inactive_date,sysdate+1)) > trunc(sysdate);
226 -- Bug 16343574 End;
227
228 ELSE
229 s_error_code:='09';
230 GOTO fim;
231
232 END IF;
233 EXCEPTION
234 WHEN NO_DATA_FOUND THEN
235 s_error_code:='10';
236 GOTO fim;
237 WHEN TOO_MANY_ROWS THEN
238 s_error_code:='14';
239 GOTO fim;
240 END;
241
242 IF e_transferor_name <> x_vendor_name
243 THEN
244 s_error_code:='11';
245 GOTO fim;
246 END IF;
247
248 /*--------------------------------------------------------------------*/
249 /* Check the transferor bank and branch number */
250 /*--------------------------------------------------------------------*/
251 BEGIN
252 SELECT 'Y'
253 INTO x_aux
254 FROM sys.dual
255 WHERE EXISTS (
256 SELECT null
257 FROM ce_bank_branches_v
258 WHERE bank_number = e_transferor_bank_code
259 AND branch_number = e_transferor_branch_code
260 AND bank_home_country = 'BR' );
261 EXCEPTION
262 WHEN NO_DATA_FOUND THEN
263 s_error_code:='12';
264 GOTO fim;
265 END;
266
267 /*--------------------------------------------------------------------*/
268 /* Check ARREARS_DATE, ARREARS_CODE and ARREARS_INTEREST fields */
269 /*--------------------------------------------------------------------*/
270 BEGIN
271 SELECT (decode(e_arrears_code,NULL,0,1)+
272 decode(e_arrears_date,NULL,0,1)+
273 abs(nvl(e_arrears_interest,0))),
274 (decode(e_arrears_code,NULL,0,1)*
275 decode(e_arrears_date,NULL,0,1)*
276 nvl(e_arrears_interest,0))
277 INTO x_aux1,x_aux2
278 FROM sys.dual;
279
280 IF x_aux1 <> 0 AND x_aux2 = 0
281 THEN
282 s_error_code:='13';
283 GOTO fim;
284 END IF;
285 END;
286
287 /*--------------------------------------------------------------------*/
288 /* Check ELECTRONIC_FORMAT_FLAG fields */
289 /*--------------------------------------------------------------------*/
290 BEGIN
291 IF NVL(e_electronic_format_flag,'N') not in ('Y','N')
292 THEN
293 s_error_code:='15';
294 GOTO fim;
295 END IF;
296 END;
297
298 /*--------------------------------------------------------------------*/
299 /* Check BARCODE fields */
300 /*--------------------------------------------------------------------*/
301 BEGIN
302
303 jl_br_ap_validate_collect_pub.validate_barcode(e_barcode,x_aux1);
304
305 IF x_aux1 <> 0
306 THEN
307 s_error_code:='16';
308 GOTO fim;
309 END IF;
310 END;
311
312
313 /*--------------------------------------------------------------------*/
314 <<fim>>
315 NULL;
316 END jl_br_ap_validate_coll_doc;
317
318 END JL_BR_AP_VALIDATE_COLLECTION;