[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_LOG_VALIDATION
Source
1 PACKAGE BODY JL_BR_AR_LOG_VALIDATION as
2 /* $Header: jlbrrvlb.pls 120.18.12020000.2 2012/07/20 11:12:30 rshergil ship $ */
3
4 PROCEDURE logical_validation(
5 p_file_control IN jl_br_ar_ret_interface_all.file_control%TYPE,
6 p_ent_seq_num IN jl_br_ar_ret_interface_all.ENTRY_SEQUENTIAL_NUMBER%TYPE, -- Bug#8331293
7 p_called_from IN VARCHAR2, -- Bug#8331293
8 p_bank_number IN jl_br_ar_ret_interface_all.bank_number%TYPE,
9 p_company_code IN jl_br_ar_ret_interface_all.company_code%TYPE,
10 p_inscription_number IN jl_br_ar_ret_interface_all.inscription_number%TYPE,
11 p_bank_occurrence_code IN jl_br_ar_ret_interface_all.bank_occurrence_code%TYPE,
12 p_occurrence_date IN jl_br_ar_ret_interface_all.occurrence_date%TYPE,
13 p_company_use IN jl_br_ar_ret_interface_all.company_use%TYPE,
14 p_your_number IN jl_br_ar_ret_interface_all.your_number%TYPE,
15 p_customer_name IN jl_br_ar_ret_interface_all.customer_name%TYPE,
16 p_trade_note_amount IN jl_br_ar_ret_interface_all.trade_note_amount%TYPE,
17 p_credit_amount IN jl_br_ar_ret_interface_all.credit_amount%TYPE,
18 p_interest_amount_received IN jl_br_ar_ret_interface_all.interest_amount_received%TYPE,
19 p_discount_amount IN jl_br_ar_ret_interface_all.discount_amount%TYPE,
20 p_abatement_amount IN jl_br_ar_ret_interface_all.abatement_amount%TYPE,
21 p_bank_party_id OUT NOCOPY NUMBER,
22 p_error_code IN OUT NOCOPY varchar2)
23 IS
24 X_bank_number jl_br_ar_ret_interface_all.bank_number%TYPE;
25 X_jlbr_bank_number jl_br_ar_ret_interface_all.bank_number%TYPE;
26 X_remittance_bank jl_br_ar_ret_interface_all.bank_number%TYPE;
27 X_company_code jl_br_ar_ret_interface_all.company_code%TYPE;
28 X_inscription_number jl_br_ar_ret_interface_all.inscription_number%TYPE;
29 X_cgc jl_br_ar_ret_interface_all.inscription_number%TYPE;
30 X_bank_occurrence_code jl_br_ar_ret_interface_all.bank_occurrence_code%TYPE;
31 X_company_use jl_br_ar_ret_interface_all.company_use%TYPE;
32 X_your_number jl_br_ar_ret_interface_all.your_number%TYPE;
33 X_bank_occurrence_code_std jl_br_ar_bank_occurrences.std_occurrence_code%TYPE;
34 X_document_status jl_br_ar_collection_docs_all.document_status%TYPE;
35 X_customer_name jl_br_ar_ret_interface_all.customer_name%TYPE;
36 X_payment_schedule_id jl_br_ar_collection_docs_all.payment_schedule_id%TYPE;
37 X_trade_note_amount jl_br_ar_ret_interface_all.trade_note_amount%TYPE;
38 X_document_amount jl_br_ar_ret_interface_all.trade_note_amount%TYPE;
39 X_credit_amount jl_br_ar_ret_interface_all.credit_amount%TYPE;
40 X_interest_amount_received jl_br_ar_ret_interface_all.interest_amount_received%TYPE;
41 X_discount_amount jl_br_ar_ret_interface_all.discount_amount%TYPE;
42 X_abatement_amount jl_br_ar_ret_interface_all.abatement_amount%TYPE;
43 X_customer_name1 jl_br_ar_ret_interface_all.customer_name%TYPE;
44 X_dual_num number;
45 X_remittance_bank_id NUMBER;
46 error_validation EXCEPTION;
47 valid_date BOOLEAN;
48 ------------------------------------------------------------------------
49 --Bug 4192066: Added required variables for parameters required to call
50 -- XLE API to get the tax registration number
51 ------------------------------------------------------------------------
52
53 l_init_msg_list VARCHAR2(2000);
54 l_commit VARCHAR2(2000);
55 l_validation_level NUMBER;
56 l_msg_count NUMBER;
57 l_msg_data VARCHAR2(2000);
58 l_return_status VARCHAR2(2000);
59 l_ledger_id ar_system_parameters.set_of_books_id%TYPE;
60 l_acct_balancing_segment ar_system_parameters.global_attribute1%TYPE;
61 l_ledger_info xle_businessinfo_grp.le_ledger_rec_type;
62 l_legal_entity_id xle_entity_profiles.legal_entity_id%TYPE;
63 l_legal_entity_name xle_entity_profiles.name%TYPE;
64 l_party_id hz_parties.party_id%TYPE;
65 l_party_type hz_parties.party_type%TYPE;
66
67
68 -- Bug#8331293 Replaced SQL query with cursor
69 Cursor Comp_JLBRRVFD ( cp_ledger_id ar_system_parameters.set_of_books_id%TYPE
70 ,cp_lacct_balancing_segment ar_system_parameters.global_attribute1%TYPE
71 ,cp_file_control jl_br_ar_ret_interface_all.file_control%TYPE
72 ,cp_ent_seq_num jl_br_ar_ret_interface_all.ENTRY_SEQUENTIAL_NUMBER%TYPE)
73 IS
74 Select etb.registration_number
75 From
76 xle_establishment_v etb
77 ,xle_bsv_associations bsv
78 ,gl_ledger_le_v gl
79 ,jl_br_ar_ret_interface jlint
80 Where
81 etb.legal_entity_id = gl.legal_entity_id
82 And bsv.legal_parent_id = etb.legal_entity_id
83 And etb.establishment_id = bsv.legal_construct_id
84 And bsv.entity_name = cp_lacct_balancing_segment
85 And gl.ledger_id = cp_ledger_id
86 -- Bug 10334411 Start
87 And rtrim(upper(substr(translate(etb.establishment_name,
88 '????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
89 'aAoOcCuUaAeEoOaAaAeEiIoOuU '),1,80))) --bug 12853583 Increased length to 80
90 --bug 12853583 = rtrim(upper(jlint.company_name)) --bug 8412707 + bug8527766
91 like rtrim(upper(translate(jlint.company_name,
92 '????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
93 'aAoOcCuUaAeEoOaAaAeEiIoOuU ')))||'%' --bug 12853583 Introduced translate
94 --bug 13910114 Introduced LIKE
95 -- Bug 10334411 End
96 And file_control = cp_file_control
97 And ENTRY_SEQUENTIAL_NUMBER = cp_ent_seq_num
98 AND trunc(SYSDATE) between trunc(nvl(bsv.effective_from, SYSDATE))
99 and trunc(nvl(bsv.effective_to,SYSDATE)); --bug 9239401
100
101 Cursor Comp_JLBRRCDB ( cp_ledger_id ar_system_parameters.set_of_books_id%TYPE
102 ,cp_lacct_balancing_segment ar_system_parameters.global_attribute1%TYPE
103 ,cp_file_control jl_br_ar_ret_interface_all.file_control%TYPE
104 ,cp_ent_seq_num jl_br_ar_ret_interface_all.ENTRY_SEQUENTIAL_NUMBER%TYPE)
105 IS
106 Select etb.registration_number
107 From
108 xle_establishment_v etb
109 ,xle_bsv_associations bsv
110 ,gl_ledger_le_v gl
111 ,JL_BR_AR_RET_INTERFACE_EXT jlint
112 Where
113 etb.legal_entity_id = gl.legal_entity_id
114 And bsv.legal_parent_id = etb.legal_entity_id
115 And etb.establishment_id = bsv.legal_construct_id
116 And bsv.entity_name = cp_lacct_balancing_segment
117 And gl.ledger_id = cp_ledger_id
118 -- Bug 14204545 Start
119 And rtrim(upper(substr(translate(etb.establishment_name,
120 '????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
121 'aAoOcCuUaAeEoOaAaAeEiIoOuU '),1,80))) --bug 12853583 Increased length to 80
122 --bug 12853583 = rtrim(upper(jlint.company_name)) --bug 8412707 + bug8527766
123 like rtrim(upper(translate(jlint.company_name,
124 '????cCuUaAeEoOaAaAeEiIoOuU!@#$%&*()_+=[]{}/\?:<>|.',
125 'aAoOcCuUaAeEoOaAaAeEiIoOuU ')))||'%' --bug 12853583 Introduced translate
126 --bug 13910114 Introduced LIKE
127 -- Bug 14204545 End
128 And file_control = cp_file_control
129 And ENTRY_SEQUENTIAL_NUMBER = cp_ent_seq_num
130 AND trunc(SYSDATE) between trunc(nvl(bsv.effective_from, SYSDATE))
131 and trunc(nvl(bsv.effective_to,SYSDATE)); --bug 9239401
132
133 BEGIN
134 fnd_file.put_line(FND_FILE.LOG,'Parameters passed to JL_BR_AR_LOG_VALIDATION.logical_validation');
135 fnd_file.put_line(FND_FILE.LOG,'File Control : ' || p_file_control );
136 fnd_file.put_line(FND_FILE.LOG,'Sequence Number : ' || p_ent_seq_num );
137 fnd_file.put_line(FND_FILE.LOG,'Called From : ' || p_called_from);
138
139
140
141 X_bank_number := p_bank_number;
142 X_company_code := p_company_code;
143 X_inscription_number := nvl(p_inscription_number,0);
144 X_bank_occurrence_code := p_bank_occurrence_code;
145 X_company_use := p_company_use;
146 X_your_number := p_your_number;
147 X_customer_name := p_customer_name;
148 X_trade_note_amount := p_trade_note_amount;
149 X_credit_amount := p_credit_amount;
150 X_interest_amount_received := p_interest_amount_received;
151 X_discount_amount := p_discount_amount;
152 X_abatement_amount := p_abatement_amount;
153
154 fnd_file.put_line(FND_FILE.LOG,'Bank Number : ' || X_bank_number);
155 fnd_file.put_line(FND_FILE.LOG,'Company Code : ' || X_company_code);
156 fnd_file.put_line(FND_FILE.LOG,'Inscription Number :' || X_inscription_number );
157
158 /* Bug 8527766 */
159 /* Becoming 1st validation as BANK_PARTY_ID is NOT NULL in INTERFACE_EXT table */
160
161 /****************************/
162 /* Validate Bank Number */
163 /****************************/
164
165 fnd_file.put_line(FND_FILE.LOG,'Validating Bank Number...');
166
167 BEGIN
168 /* CE uptake - Bug#2932986
169 SELECT distinct bank_number
170 INTO X_jlbr_bank_number
171 FROM jl_br_ar_bank_occurrences
172 WHERE bank_number = X_bank_number
173 AND ROWNUM < 2;
174 */
175
176 SELECT prof.bank_or_branch_number,
177 nvl(occ.bank_party_id,0) --bug 8527766 - can be NULL in occ table
178 INTO X_jlbr_bank_number,
179 p_bank_party_id
180 FROM jl_br_ar_bank_occurrences occ,
181 hz_organization_profiles prof
182 WHERE prof.bank_or_branch_number = X_bank_number
183 AND occ.bank_party_id = prof.party_id
184 AND prof.home_country ='BR'
185 AND rownum = 1;
186
187 fnd_file.put_line(FND_FILE.LOG,'x_jlbr_bank_number: ' || X_jlbr_bank_number);
188 fnd_file.put_line(FND_FILE.LOG,'p_bank_party_id : ' || p_bank_party_id);
189
190 EXCEPTION
191 WHEN NO_DATA_FOUND THEN
192 p_error_code := 'INVALID_BANK_NUMBER';
193 p_bank_party_id := 0; --bug 8527766 - ensure value is NOT NULL
194 RAISE error_validation;
195 END;
196
197 /*******************************/
198 /* Validate Occurrence Code */
199 /*******************************/
200
201 fnd_file.put_line(FND_FILE.LOG,'Validating Occurrence Code...');
202
203 BEGIN
204 -- CE uptake - Bug#2932986
205 SELECT occ.std_occurrence_code
206 INTO X_bank_occurrence_code_std
207 FROM jl_br_ar_bank_occurrences occ,
208 hz_organization_profiles prof
209 WHERE occ.bank_occurrence_code = X_bank_occurrence_code
210 AND prof.bank_or_branch_number = X_bank_number
211 AND occ.bank_party_id = prof.party_id
212 AND prof.home_country ='BR'
213 AND occ.bank_occurrence_type = 'RETURN_OCCURRENCE'
214 AND SYSDATE between TRUNC(prof.effective_start_date)
215 AND NVL(TRUNC(prof.effective_end_date), SYSDATE+1);
216
217 fnd_file.put_line(FND_FILE.LOG,'x_bank_occurrence_code_std : ' || x_bank_occurrence_code_std);
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 p_error_code := 'INVALID_BANK_OCCURRENCE_CODE';
222 RAISE error_validation;
223 END;
224
225 /****************************/
226 /* Validate Occurrence Date */
227 /****************************/
228
229 fnd_file.put_line(FND_FILE.LOG,'Validating Occurrence Date...');
230
231 BEGIN
232 valid_date := ARP_UTIL.IS_GL_DATE_VALID(p_occurrence_date);
233 IF NOT valid_date THEN
234 p_error_code := 'INVALID_OCCURRENCE_DATE';
235 RAISE error_validation;
236 END IF;
237 END;
238
239
240 /****************************/
241 /* Validate Company Code */
242 /****************************/
243
244 fnd_file.put_line(FND_FILE.LOG,'Validating Company Code...');
245
246 BEGIN
247 /*
248 SELECT distinct aba.global_attribute7
249 INTO X_dual_num
250 FROM ap_bank_accounts_all aba,
251 jl_br_ar_collection_docs cd,
252 ar_payment_schedules_all arps
253 WHERE cd.document_id = X_company_use
254 AND aba.bank_account_id = cd.bank_account_id
255 AND arps.payment_schedule_id = cd.payment_schedule_id
256 -- bug 1892303
257 -- AND arps.trx_number||'-'||to_char(arps.terms_sequence_number) = X_your_number
258 AND to_number(aba.global_attribute7) = X_company_code;
259 */
260
261 SELECT distinct acct.secondary_account_reference
262 INTO X_dual_num
263 FROM ce_bank_accounts acct,
264 ce_bank_acct_uses_all acctUse,
265 jl_br_ar_collection_docs cd,
266 ar_payment_schedules_all arps
267 WHERE cd.document_id = X_company_use
268 AND acctUse.bank_acct_use_id = cd.bank_acct_use_id
269 AND acct.bank_account_id = acctUse.bank_account_id
270 AND arps.payment_schedule_id = cd.payment_schedule_id
271 -- bug 1892303
272 -- AND arps.trx_number||'-'||to_char(arps.terms_sequence_number) = trim(X_your_number)
273 AND to_number(acct.secondary_account_reference) = X_company_code;
274
275 fnd_file.put_line(FND_FILE.LOG,'x_dual_num : ' || x_dual_num);
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 p_error_code := 'INVALID_COMPANY_CODE_FOR_BANK';
280 RAISE error_validation;
281 WHEN TOO_MANY_ROWS THEN
282 null;
283 END;
284
285 /********************/
286 /* Validate Document*/
287 /********************/
288
289 fnd_file.put_line(FND_FILE.LOG,'Validating Document...');
290
291 IF X_company_use IS NOT null THEN
292 BEGIN
293
294 /* CE uptake - Bug#2932986
295 SELECT doc.payment_schedule_id,
296 doc.document_status,
297 bra.bank_number
298 INTO X_payment_schedule_id,
299 X_document_status,
300 X_remittance_bank
301 FROM jl_br_ar_collection_docs doc,
302 ap_bank_accounts_all acc,
303 ap_bank_branches bra
304 WHERE doc.document_id = X_company_use
305 AND doc.bank_account_id = acc.bank_account_id
306 AND acc.bank_branch_id = bra.bank_branch_id;
307 */
308
309 SELECT doc.payment_schedule_id,
310 doc.document_status,
311 HzPartyBank.party_id bank_id
312 INTO X_payment_schedule_id,
313 X_document_status,
314 X_remittance_bank_id
315 FROM jl_br_ar_collection_docs doc,
316 ce_bank_accounts CeBankAccount,
317 ce_bank_acct_uses_all CeBankAcctUse,
318 hz_parties HzPartyBank
319 Where doc.bank_acct_use_id = CeBankAcctUse.bank_acct_use_id
320 And CeBankAcctUse.bank_account_id = CeBankAccount.bank_account_id
321 And CeBankAccount.BANK_ID = HzPartyBank.PARTY_ID
322 --And HzPartyBank.Country = 'BR'
323 And doc.document_id = X_company_use;
324 --End of CE uptake
325
326 fnd_file.put_line(FND_FILE.LOG,'x_payment_schedule_id : ' || x_payment_schedule_id);
327 fnd_file.put_line(FND_FILE.LOG,'x_document_status : ' || x_document_status);
328 fnd_file.put_line(FND_FILE.LOG,'x_remittance_bank_id : ' || x_remittance_bank_id);
329
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 p_error_code := 'COLL_DOC_NOT_EXIST';
334 RAISE error_validation;
335 WHEN others THEN
336 null;
337 END;
338
339 fnd_file.put_line(FND_FILE.LOG,'Validating Trade Note...');
340
341 BEGIN
342 SELECT payment_schedule_id
343 INTO X_payment_schedule_id
344 FROM ar_payment_schedules
345 WHERE payment_schedule_id = X_payment_schedule_id;
346
347 EXCEPTION
348 WHEN NO_DATA_FOUND THEN
349 p_error_code := 'TRADE_NOTE_NOT_EXIST';
350 RAISE error_validation;
351 WHEN TOO_MANY_ROWS THEN
352 null;
353 END;
354
355 /*
356 ELSE
357
358 BEGIN
359 SELECT payment_schedule_id
360 INTO X_payment_schedule_id
361 FROM ar_payment_schedules
362 WHERE trx_number|| '-' ||to_char(terms_sequence_number)=X_your_number;
363
364 EXCEPTION
365 WHEN NO_DATA_FOUND THEN
366 p_error_code := 'TRADE_NOTE_NOT_EXIST';
367 RAISE error_validation;
368 WHEN others THEN
369 null;
370 END;
371
372 BEGIN
373 SELECT doc.document_status,
374 bra.bank_number
375 INTO X_document_status,
376 X_remittance_bank
377 FROM jl_br_ar_collection_docs doc,
378 ap_bank_accounts_all acc,
379 ap_bank_branches bra
380 WHERE doc.payment_schedule_id = X_payment_schedule_id
381 AND doc.bank_account_id = acc.bank_account_id
382 AND acc.bank_branch_id = bra.bank_branch_id;
383
384 EXCEPTION
385 WHEN NO_DATA_FOUND THEN
386 p_error_code := 'COLL_DOC_NOT_EXIST';
387 RAISE error_validation;
388 WHEN others THEN
389 null;
390 END;
391
392 */
393 END IF;
394
395 /****************************/
396 /* Validate Bank */
397 /****************************/
398
399 -- CE uptake - Bug#2932986
400 IF X_remittance_bank_id <> p_bank_party_id THEN
401 p_error_code := 'INVALID_BANK_NUMBER';
402 fnd_file.put_line(FND_FILE.LOG,'X_remittance_bank_id <> p_bank_party_id');
403 RAISE error_validation;
404 END IF;
405
406 /****************/
407 /* Validate CGC */
408 /****************/
409
410 fnd_file.put_line(FND_FILE.LOG,'Validating Inscription Number...');
411
412 BEGIN
413
414 ----------------------------------------------------------------------
415 -- BUG 4192066. JL_BR_COMPANY_INFOS is being obsoleted. To obtaint the
416 -- registration number a new API is being called.
417 -- To obtain the Reg Number we need to pass the party_id
418 -- party_type and effective_date.
419 ----------------------------------------------------------------------
420
421 -----------------------------------------------------------
422 -- Retrieve the set_of_books_id and acct_balancing_segment
423 -- from ar_system_parameters
424 -----------------------------------------------------------
425 SELECT set_of_books_id,
426 global_attribute1
427 INTO l_ledger_id,
428 l_acct_balancing_segment
429 FROM ar_system_parameters;
430
431 fnd_file.put_line(FND_FILE.LOG,'Legder Id : ' || l_ledger_id);
432 fnd_file.put_line(FND_FILE.LOG,'Balancing Segment : ' || l_acct_balancing_segment);
433
434 ------------------------------------------------------------
435 -- Proceed to retrieve the Ledger Information which contains
436 -- the Legal Entity Id as well
437 ------------------------------------------------------------
438 /* XLE_BUSINESSINFO_GRP.Get_Ledger_Info(
439 x_return_status => l_return_status ,
440 x_msg_data => l_msg_data ,
441 P_Ledger_ID => l_ledger_id ,
442 P_BSV => l_acct_balancing_segment,
443 x_Ledger_info => l_ledger_info
444 );
445 -------------------------------------------------------------
446 -- Retrieve the Name of the Legal Entity
447 -------------------------------------------------------------
448 l_legal_entity_name := l_ledger_info(1).Name;
449 l_party_id := l_ledger_info(1).Party_id;
450 l_legal_entity_id := l_ledger_info(1).legal_entity_id;
451
452
453 -------------------------------------------------------------
454 -- Retrieve the Registration Number
455 -------------------------------------------------------------
456 XLE_UTILITIES_GRP.get_fp_vatregistration_LEID
457 (p_api_version => 1.0,
458 p_init_msg_list => l_init_msg_list,
459 p_commit => l_commit,
460 p_effective_date => sysdate,
461 x_return_status => l_return_status,
462 x_msg_count => l_msg_count,
463 x_msg_data => l_msg_data,
464 p_legal_entity_id => l_legal_entity_id,
465 x_registration_number => x_cgc
466 );*/
467 -- Bug#8331293 Start
468 -- Commenting below sql query.This is replaced by cursor Comp
469 /* Select etb.registration_number
470 INTO x_cgc
471 From
472 xle_establishment_v etb
473 ,xle_bsv_associations bsv
474 ,gl_ledger_le_v gl
475 Where
476 etb.legal_entity_id = gl.legal_entity_id
477 And bsv.legal_parent_id = etb.legal_entity_id
478 And etb.establishment_id = bsv.legal_construct_id
479 And bsv.entity_name = l_acct_balancing_segment
480 And gl.ledger_id = l_ledger_id; */
481
482 IF p_called_from = 'JLBRRVFD' then
483 fnd_file.put_line(FND_FILE.LOG,'Called From JLBRRVFD');
484
485 -- Called from program Brazilian Receivables Import of Bank Return Program(JLBRRVFD.sql)
486
487 For Comp_JLBRRVFD_Rec in Comp_JLBRRVFD ( l_ledger_id
488 ,l_acct_balancing_segment
489 ,p_file_control
490 ,p_ent_seq_num )
491 Loop
492 fnd_file.put_line(FND_FILE.LOG,'in the Comp_JLBRRVFD Cursor');
493 fnd_file.put_line(FND_FILE.LOG,'Registration Number Fetched : ' || Comp_JLBRRVFD_Rec.registration_number);
494 x_cgc := Comp_JLBRRVFD_Rec.registration_number;
495 End Loop;
496
497 ELSE
498
499 -- It can be called from form Correct Bank Returns (JLBRRCDB.fmb)
500
501 For Comp_JLBRRCDB_Rec in Comp_JLBRRCDB ( l_ledger_id
502 ,l_acct_balancing_segment
503 ,p_file_control
504 ,p_ent_seq_num )
505 Loop
506 fnd_file.put_line(FND_FILE.LOG,'in the Comp_JLBRRCDB Cursor');
507 fnd_file.put_line(FND_FILE.LOG,'Registration Number is : ' || Comp_JLBRRCDB_Rec.registration_number);
508 x_cgc := Comp_JLBRRCDB_Rec.registration_number;
509 End Loop;
510
511 END IF;
512
513
514 -- Bug#8331293 End
515
516 /* SELECT distinct (decode(inf.register_type, 3, '00000000000000',
517 to_number(lpad(inf.register_number,9,'0')||
518 lpad(inf.register_subsidiary,4,'0')||
519 lpad(inf.register_digit,2,'0'))))
520 INTO X_cgc
521 FROM jl_br_company_infos inf,
522 hz_cust_acct_sites_all adr,
523 ra_customer_trx_all trx,
524 ar_payment_schedules pay
525 WHERE pay.payment_schedule_id = X_payment_schedule_id
526 AND trx.customer_trx_id = pay.customer_trx_id
527 AND adr.cust_acct_site_id = trx.remit_to_address_id
528 AND inf.accounting_balancing_segment = adr.global_attribute1
529 AND inf.set_of_books_id = trx.set_of_books_id; */
530 fnd_file.put_line(FND_FILE.LOG,'Comapring value of X_cgc variable ' || X_cgc);
531 fnd_file.put_line(FND_FILE.LOG,'Comparing value of X_inscription_number ' || X_inscription_number);
532 IF nvl(X_cgc,0) <> X_inscription_number THEN
533 p_error_code := 'INVALID_COMPANY_INSCRIPT_NUM';
534 fnd_file.put_line(FND_FILE.LOG,'Raising exception since the values are not matching');
535 RAISE error_validation;
536 END IF;
537
538 EXCEPTION
539 --WHEN NO_DATA_FOUND THEN
540 WHEN OTHERS THEN
541 p_error_code := 'INVALID_COMPANY_INSCRIPT_NUM';
542 RAISE error_validation;
543 /*WHEN TOO_MANY_ROWS THEN
544 null;*/
545 END;
546
547 /**************************************/
548 /* Validate Occurrence Code */
549 /**************************************/
550
551 IF X_bank_occurrence_code_std = 'CONFIRMED_ENTRY'
552 AND X_document_status = 'CANCELED'
553 THEN p_error_code := 'COLL_DOC_CANCELED';
554 RAISE error_validation;
555 ELSIF X_bank_occurrence_code_std = 'CONFIRMED_ENTRY'
556 AND X_document_status = 'REFUSED'
557 THEN p_error_code := 'COLL_DOC_REFUSED';
558 RAISE error_validation;
559 ELSIF X_bank_occurrence_code_std = 'CONFIRMED_ENTRY'
560 AND X_document_status = 'WRITTEN_OFF'
561 THEN p_error_code := 'COLL_DOC_WRITTEN_OFF';
562 RAISE error_validation;
563 ELSIF X_bank_occurrence_code_std = 'PARTIAL_SETTLEMENT'
564 AND X_document_status = 'PARTIALLY_RECEIVED'
565 THEN p_error_code := 'COLL_DOC_PARTIAL_RECEIVED';
566 RAISE error_validation;
567 ELSIF X_bank_occurrence_code_std = 'CONFIRMED_ENTRY'
568 AND X_document_status = 'PARTIALLY_RECEIVED'
569 THEN p_error_code := 'COLL_DOC_PARTIAL_RECEIVED';
570 RAISE error_validation;
571 ELSIF X_bank_occurrence_code_std = 'CONFIRMED_ENTRY'
572 AND X_document_status = 'TOTALLY_RECEIVED'
573 THEN p_error_code := 'COLL_DOC_FULLY_RECEIVED';
574 RAISE error_validation;
575 ELSIF X_bank_occurrence_code_std = 'REJECTED_ENTRY'
576 AND X_document_status = 'CANCELED'
577 THEN p_error_code := 'COLL_DOC_CANCELED';
578 RAISE error_validation;
579 ELSIF X_bank_occurrence_code_std = 'REJECTED_ENTRY'
580 AND X_document_status <> 'FORMATTED'
581 THEN p_error_code := 'COLL_DOC_NOT_REJECTED';
582 RAISE error_validation;
583 ELSIF X_bank_occurrence_code_std in
584 ('FULL_SETTLEMENT','PARTIAL_SETTLEMENT')
585 AND X_document_status = 'TOTALLY_RECEIVED'
586 THEN p_error_code := 'COLL_DOC_FULLY_RECEIVED';
587 RAISE error_validation;
588 ELSIF X_bank_occurrence_code_std in
589 ('FULL_SETTLEMENT','PARTIAL_SETTLEMENT')
590 AND X_document_status = 'REFUSED'
591 THEN p_error_code := 'COLL_DOC_REFUSED';
592 RAISE error_validation;
593 ELSIF X_bank_occurrence_code_std in
594 ('FULL_SETTLEMENT','PARTIAL_SETTLEMENT')
595 AND X_document_status = 'WRITTEN_OFF'
596 THEN p_error_code := 'COLL_DOC_WRITTEN_OFF';
597 RAISE error_validation;
598 ELSIF X_bank_occurrence_code_std in
599 ('FULL_SETTLEMENT','PARTIAL_SETTLEMENT')
600 AND X_document_status = 'CANCELED'
601 THEN p_error_code := 'COLL_DOC_CANCELED';
602 RAISE error_validation;
603 /*
604 ELSIF X_bank_occurrence_code_std = 'DEBIT_BALANCE_SETTLEMENT'
605 AND X_document_status = 'TOTALLY_RECEIVED'
606 THEN p_error_code := 'COLL_DOC_FULLY_RECEIVED';
607 RAISE error_validation;
608 ELSIF X_bank_occurrence_code_std = 'DEBIT_BALANCE_SETTLEMENT'
609 AND X_document_status = 'REFUSED'
610 THEN p_error_code := 'COLL_DOC_REFUSED';
611 RAISE error_validation;
612 ELSIF X_bank_occurrence_code_std = 'DEBIT_BALANCE_SETTLEMENT'
613 AND X_document_status = 'WRITTEN_OFF'
614 THEN p_error_code := 'COLL_DOC_WRITTEN_OFF';
615 RAISE error_validation;
616 ELSIF X_bank_occurrence_code_std = 'DEBIT_BALANCE_SETTLEMENT'
617 AND X_document_status = 'CANCELED'
618 THEN p_error_code := 'COLL_DOC_CANCELED';
619 RAISE error_validation;
620 */
621 ELSIF X_bank_occurrence_code_std = 'AUTOMATIC_WRITE_OFF'
622 AND X_document_status = 'WRITTEN_OFF'
623 THEN p_error_code := 'COLL_DOC_WRITTEN_OFF';
624 RAISE error_validation;
625 ELSIF X_bank_occurrence_code_std = 'AUMOMATIC_WRITE_OFF'
626 AND X_document_status = 'REFUSED'
627 THEN p_error_code := 'COLL_DOC_REFUSED';
628 RAISE error_validation;
629 ELSIF X_bank_occurrence_code_std = 'AUMOMATIC_WRITE_OFF'
630 AND X_document_status = 'PARTIALLY_RECEIVED'
631 THEN p_error_code := 'COLL_DOC_PARTIAL_RECEIVED';
632 RAISE error_validation;
633 ELSIF X_bank_occurrence_code_std = 'AUMOMATIC_WRITE_OFF'
634 AND X_document_status = 'TOTALLY_RECEIVED'
635 THEN p_error_code := 'COLL_DOC_FULLY_RECEIVED';
636 RAISE error_validation;
637 ELSIF X_bank_occurrence_code_std = 'AUMOMATIC_WRITE_OFF'
638 AND X_document_status = 'CANCELED'
639 THEN p_error_code := 'COLL_DOC_CANCELED';
640 RAISE error_validation;
641 ELSIF X_bank_occurrence_code_std = 'PAYMENT_AFTER_WRITE_OFF'
642 AND X_document_status = 'REFUSED'
643 THEN p_error_code := 'COLL_DOC_REFUSED';
644 RAISE error_validation;
645 ELSIF X_bank_occurrence_code_std = 'PAYMENT_AFTER_WRITE_OFF'
646 AND X_document_status = 'PARTIALLY_RECEIVED'
647 THEN p_error_code := 'COLL_DOC_PARTIAL_RECEIVED';
648 RAISE error_validation;
649 ELSIF X_bank_occurrence_code_std = 'PAYMENT_AFTER_WRITE_OFF'
650 AND X_document_status = 'TOTALLY_RECEIVED'
651 THEN p_error_code := 'COLL_DOC_FULLY_RECEIVED';
652 RAISE error_validation;
653 ELSIF X_bank_occurrence_code_std = 'PAYMENT_AFTER_WRITE_OFF'
654 AND X_document_status = 'CANCELED'
655 THEN p_error_code := 'COLL_DOC_CANCELED';
656 RAISE error_validation;
657 ELSIF X_bank_occurrence_code_std = 'PAYMENT_AFTER_WRITE_OFF'
658 AND X_document_status <> 'WRITTEN_OFF'
659 THEN p_error_code := 'COLL_DOC_NOT_WRITTEN_OFF';
660 RAISE error_validation;
661 END IF;
662
663 /******************************************/
664 /* Validate Customer Name */
665 /******************************************/
666
667 /* Old code replaced because of translation issues */
668
669 /* IF X_customer_name IS NOT null THEN
670 BEGIN
671 SELECT substr(pty.party_name, 1,50)
672 INTO X_customer_name1
673 FROM hz_cust_accounts_all cst,
674 hz_parties pty,
675 ra_customer_trx trx
676 WHERE trx.bill_to_customer_id = cst.cust_account_id
677 AND cst.party_id = pty.party_id
678 AND trx.customer_trx_id = (SELECT customer_trx_id
679 FROM ar_payment_schedules
680 WHERE payment_schedule_id =
681 X_payment_schedule_id);
682 EXCEPTION
683 WHEN NO_DATA_FOUND THEN
684 p_error_code := 'INVALID_CUSTOMER_NAME';
685 RAISE error_validation;
686 WHEN TOO_MANY_ROWS THEN
687 null;
688 END;
689
690 IF rpad(substr(X_customer_name1,1,30),30) <>
691 rpad(substr(X_customer_name,1,30),30)
692 THEN p_error_code := 'INVALID_CUSTOMER_NAME';
693 RAISE error_validation;
694 END IF;
695
696 END IF; */
697
698 /* New Code for Validate customer name because of translation issues */
699
700 /* Bug 1329486 Customer name validation removed from logical val procedure */
701 /* because of special characters */
702
703 IF X_bank_occurrence_code_std = 'FULL_SETTLEMENT' THEN
704
705 /*************************/
706 /* Validate Credit Amount*/
707 /*************************/
708
709 IF nvl(X_trade_note_amount,0) <> nvl(X_credit_amount,0) -
710 nvl(X_interest_amount_received,0) +
711 nvl(X_discount_amount,0) +
712 nvl(X_abatement_amount,0)
713 THEN
714 p_error_code := 'INCORRECT_AMOUNT';
715 RAISE error_validation;
716 END IF;
717
718 END IF;
719
720 /*************************************************/
721 /* Validate Document Amount with Original Amount */
722 /*************************************************/
723
724 BEGIN
725 SELECT nvl(doc.document_amount,0)
726 INTO X_document_amount
727 FROM jl_br_ar_bank_occurrences ban,
728 jl_br_ar_occurrence_docs_all doc,
729 jl_br_ar_collection_docs cob
730 WHERE ban.std_occurrence_code = 'REMITTANCE'
731 AND ban.bank_occurrence_type = 'REMITTANCE_OCCURRENCE'
732 --AND ban.bank_number = X_bank_number
733 AND ban.bank_party_id = p_bank_party_id
734 AND doc.bank_occurrence_code = ban.bank_occurrence_code
735 AND doc.occurrence_status in ('CONFIRMED')
736 --AND doc.bank_number = ban.bank_number
737 AND doc.bank_party_id = ban.bank_party_id
738 AND doc.bank_occurrence_type = ban.bank_occurrence_type
739 AND doc.document_id = cob.document_id
740 /*
741 AND cob.document_status in ('FORMATTED','CONFIRMED')
742 AND cob.payment_schedule_id = X_payment_schedule_id;
743 */
744 AND cob.document_id = X_company_use;
745
746 EXCEPTION
747 WHEN others THEN
748 X_document_amount := -1;
749 END;
750
751 IF X_document_amount <> X_trade_note_amount THEN
752 p_error_code := 'COLL_DOC_AMOUNT_NOT_MATCH';
753 RAISE error_validation;
754 END IF;
755
756
757 /****************************/
758 /* No Validation Error */
759 /****************************/
760
761 p_error_code := 'SUCCESS';
762
763 EXCEPTION
764 WHEN error_validation THEN null;
765
766 END logical_validation;
767
768 END JL_BR_AR_LOG_VALIDATION;
769