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