1 PACKAGE BODY JL_AR_DOC_NUMBERING_PKG as
2 /* $Header: jlarrdnb.pls 120.14.12010000.4 2009/09/15 14:23:15 mbarrett ship $ */
3
4 /*----------------------------------------------------------------------------*
5 | PUBLIC FUNCTIONS/PROCEDURES |
6 *----------------------------------------------------------------------------*/
7
8 PG_DEBUG varchar2(1);
9
10 FUNCTION validate_trx_type (p_batch_source_id IN NUMBER,
11 p_trx_type IN NUMBER,
12 p_invoice_class IN VARCHAR2,
13 p_document_letter IN VARCHAR2,
14 p_interface_line_id IN NUMBER,
15 p_created_from IN VARCHAR2) RETURN VARCHAR2 IS
16
17 l_dummy_code VARCHAR2(15);
18 error_condition EXCEPTION;
19 l_return_code VARCHAR2(30);
20 l_document_letter VARCHAR2(1);
21 l_count NUMBER;
22 l_branch_number VARCHAR2(4);
23 l_org_id NUMBER;
24
25 BEGIN
26 l_return_code := 'SUCCESS';
27 IF PG_DEBUG in ('Y', 'C') THEN
28 arp_standard.debug('validate_trx_type: ' || '-- validate trx type ');
29 END IF;
30 -- Bug 8825457 Start
31 l_org_id := MO_GLOBAL.get_current_org_id;
32 -- Bug 8825457 End
33 BEGIN
34 SELECT 'Success'
35 INTO l_dummy_code
36 FROM jg_zz_ar_src_trx_ty st, ra_batch_sources_all src
37 WHERE st.cust_trx_type_id = p_trx_type
38 AND st.batch_source_id = p_batch_source_id
39 AND invoice_class = p_invoice_class
40 AND st.batch_source_id = src.batch_source_id
41 AND src.global_attribute3 = p_document_letter
42 AND st.enable_flag = 'Y'
43 -- Bug 8825457 Start
44 AND src.org_id = l_org_id;
45 -- Bug 8825457 End
46 IF PG_DEBUG in ('Y', 'C') THEN
47 arp_standard.debug('validate_trx_type: ' || '-- First validate trx type check');
48 END IF;
49 EXCEPTION
50 WHEN NO_DATA_FOUND THEN
51 IF p_created_from = 'RAXTRX' THEN
52 l_return_code := 'ERROR';
53 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
54 'JL_AR_AR_INVALID_TRANS_TYPE',
55 'p_interface_line_id',
56 'p_trx_type') THEN
57 RAISE error_condition;
58 END IF;
59 ELSIF p_created_from = 'ARXTWMAI' THEN
60 l_return_code := 'JL_AR_AR_TRXTYP_BTSRC_NOT_ASSO';
61 RAISE error_condition;
62 END IF;
63 WHEN TOO_MANY_ROWS THEN
64 IF p_created_from = 'RAXTRX' THEN
65 l_return_code := 'ERROR';
66 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
67 'JL_AR_AR_BT_SRC_MORE_FOUND',
68 'p_interface_line_id',
69 'p_trx_type') THEN
70 RAISE error_condition;
71 END IF;
72 ELSIF p_created_from = 'ARXTWMAI' THEN
73 l_return_code := 'JL_AR_AR_BT_SRC_MORE_FOUND';
74 RAISE error_condition;
75 END IF;
76 END;
77 BEGIN
78 --
79 SELECT substr(global_attribute2,1,4)
80 INTO l_branch_number
81 FROM ra_batch_sources a
82 WHERE a.batch_source_id = p_batch_source_id;
83 --
84 SELECT count(*)
85 INTO l_count
86 FROM jg_zz_ar_src_trx_ty ty, ra_batch_sources_all src
87 WHERE ty.cust_trx_type_id = p_trx_type
88 AND ty.batch_source_id = src.batch_source_id
89 AND substr(src.global_attribute3,1,1) <> p_document_letter
90 AND substr(src.global_attribute2,1,4) <> l_branch_number
91 AND ty.enable_flag = 'Y';
92 --
93 IF PG_DEBUG in ('Y', 'C') THEN
94 arp_standard.debug('validate_trx_type: ' || '-- Second validate trx type check');
95 END IF;
96 IF l_count > 0 THEN
97 IF p_created_from = 'RAXTRX' THEN
98 l_return_code := 'ERROR';
99 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
100 'JL_AR_AR_BT_SRC_MORE_FOUND',
101 'p_interface_line_id',
102 'p_trx_type') THEN
103 RAISE error_condition;
104 END IF;
105 ELSIF p_created_from = 'ARXTWMAI' THEN
106 l_return_code := 'JL_AR_AR_BT_SRC_MORE_FOUND';
107 RAISE error_condition;
108 END IF;
109 END IF;
110 END;
111 RETURN l_return_code;
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF l_return_code is null then
115 IF p_created_from = 'ARXTWMAI' THEN
116 l_return_code := 'JL_AR_AR_TRXTYP_BTSRC_NOT_ASSO';
117 RAISE error_condition;
118 ELSE
119 l_return_code := 'JL_AR_AR_INVALID_TRANS_TYPE';
120 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
121 'JL_AR_AR_INVALID_TRANS_TYPE',
122 'p_interface_line_id',
123 'p_trx_type') THEN
124 RAISE error_condition;
125 END IF;
126 END IF;
127 END IF;
128 RETURN l_return_code;
129 END validate_trx_type;
130
131 FUNCTION validate_four_digit (p_batch_source_id IN NUMBER,
132 p_interface_line_id IN NUMBER,
133 p_created_from IN VARCHAR2,
134 p_inventory_item_id IN NUMBER,
135 p_memo_line_id IN NUMBER,
136 p_so_org_id IN VARCHAR2) RETURN VARCHAR2
137 IS
138
139 l_dummy_code VARCHAR2(15);
140 error_condition EXCEPTION;
141 l_return_code VARCHAR2(30);
142 l_so_org_id NUMBER(15);
143 l_four_digit_code VARCHAR2(20);
144 l_point_of_sale_code VARCHAR2(4);
145 l_product_line_code VARCHAR2(4);
146 l_loc_id NUMBER(15);
147 l_org_id NUMBER;
148
149 BEGIN
150 l_return_code := 'SUCCESS';
151
152 --Bug 1404824 removed check to see if p_so_org_id is not null since it will
153 --be mandatory.
154 --Commented following line for bug 1612359
155 -- l_so_org_id := to_number(p_so_org_id);
156 l_org_id := mo_global.get_current_org_id;
157 l_so_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID',l_org_id);
158
159 BEGIN
160 SELECT global_attribute1
161 INTO l_four_digit_code
162 FROM ar_system_parameters;
163 END;
164
165 --Bug 1612359 - removed branch number validation for point of sale
166
167 IF l_four_digit_code = 'PRODUCT_LINE' THEN
168 l_product_line_code := NULL;
169 IF p_inventory_item_id IS NOT NULL THEN
170 BEGIN
171 SELECT substr(global_attribute10,1,4)
172 INTO l_product_line_code
173 FROM mtl_system_items
174 WHERE inventory_item_id = p_inventory_item_id
175 AND organization_id = l_so_org_id;
176 EXCEPTION
177 WHEN NO_DATA_FOUND THEN
178 IF p_created_from = 'RAXTRX' THEN
179 l_return_code := 'ERROR';
180 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
181 'JL_AR_AR_INVALID_ITEM_CODE',
182 p_interface_line_id,
183 p_inventory_item_id) THEN
184 RAISE error_condition;
185 END IF;
186 ELSIF p_created_from = 'ARXTWMAI' THEN
187 l_return_code := 'JL_AR_AR_INVALID_ITEM_CODE';
188 RAISE error_condition;
189 END IF;
190 END;
191 ELSE
192 IF p_memo_line_id IS NOT NULL THEN
193 BEGIN
194 SELECT substr(global_attribute7,1,4)
195 INTO l_product_line_code
196 FROM ar_memo_lines
197 WHERE memo_line_id = p_memo_line_id;
198 EXCEPTION
199 WHEN NO_DATA_FOUND THEN
200 IF p_created_from = 'RAXTRX' THEN
201 l_return_code := 'ERROR';
202 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
203 'JL_AR_AR_INVALID_MEMO_LINE',
204 p_interface_line_id,
205 p_memo_line_id) THEN
206 RAISE error_condition;
207 END IF;
208 ELSIF p_created_from = 'ARXTWMAI' THEN
209 l_return_code := 'JL_AR_AR_INVALID_MEMO_LINE';
210 RAISE error_condition;
211 END IF;
212 END;
213 END IF;
214 END IF;
215 BEGIN
216 SELECT 'Success'
217 INTO l_dummy_code
218 FROM ra_batch_sources
219 WHERE substr(global_attribute2,1,4) = l_product_line_code
220 AND batch_source_id = p_batch_source_id;
221 EXCEPTION
222 WHEN NO_DATA_FOUND THEN
223 IF p_created_from = 'RAXTRX' THEN
224 l_return_code := 'ERROR';
225 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
226 'JL_AR_AR_INV_PROD_LINE_CODE',
227 p_interface_line_id,
228 l_product_line_code) THEN
229 RAISE error_condition;
230 END IF;
231 ELSIF p_created_from = 'ARXTWMAI' THEN
232 l_return_code := 'JL_AR_AR_INV_PROD_LINE_CODE';
233 RAISE error_condition;
234 END IF;
235 END;
236 END IF;
237 RETURN l_return_code;
238 EXCEPTION
239 WHEN OTHERS THEN
240 IF l_return_code is null then
241 IF p_created_from = 'RAXTRX' THEN
242 l_return_code := 'JL_AR_AR_AI_BR_NUM_NOT_DEF';
243 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
244 'JL_AR_AR_AI_BR_NUM_NOT_DEF',
245 p_interface_line_id,
246 p_batch_source_id) THEN
247 RAISE error_condition;
248 END IF;
249 ELSIF p_created_from = 'ARXTWMAI' THEN
250 l_return_code := 'JL_AR_AR_BRANCH_NUM_NOT_DEF';
251 END IF;
252 END IF;
253 RETURN l_return_code;
254 END validate_four_digit;
255
256 FUNCTION get_num_bar_code (p_batch_source_id IN NUMBER, p_trx_type_id IN NUMBER,p_legal_entity_id IN NUMBER) RETURN VARCHAR2 IS
257 l_comp_cuit VARCHAR2(15);
258 l_dgi_code VARCHAR2(4);
259 l_branch_num VARCHAR2(4);
260 l_cai_num VARCHAR2(30);
261 l_cai_date VARCHAR2(12);
262 l_inv_org_id NUMBER;
263 l_doc_letter VARCHAR2(2);
264 l_num_bar_code VARCHAR2(100);
265 l_valid_digit VARCHAR2(2);
266 l_odd_temp NUMBER;
267 l_even_temp NUMBER;
268 l_temp NUMBER;
269 l_leng NUMBER;
270 l_count NUMBER;
271 BEGIN
272
273 --oe_profile.get('MASTER_ORGANIZATION_ID',l_org_id);
274 -- Fetching the company Cuit from hr locations
275 BEGIN
276 SELECT registration_number
277 INTO l_comp_cuit
278 FROM xle_firstparty_information_v
279 WHERE legal_entity_id = p_legal_entity_id;
280 EXCEPTION
281 WHEN OTHERS THEN
282 l_comp_cuit := NULL;
283 END;
284
285 -- Fetching the document letter,branch number, cai number, cai date
286 BEGIN
287 SELECT substr(global_attribute3,1,1),substr(global_attribute2,1,4)
288 ,global_attribute8, to_char(fnd_date.canonical_to_date(global_attribute9),'YYYY/MM/DD')
289 INTO l_doc_letter, l_branch_num, l_cai_num, l_cai_date
290 FROM ra_batch_sources
291 WHERE batch_source_id = p_batch_source_id;
292
293 SELECT REPLACE(l_cai_date,'/') INTO l_cai_date from dual;
294
295 SELECT REPLACE(l_comp_cuit,'-') INTO l_comp_cuit from dual;
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 l_branch_num := NULL;
300 END;
301
302 BEGIN
303
304 -- Fetching the dgi code
305 SELECT lpad(substr(dgi_code,1,2),2,'0')
306 INTO l_dgi_code
307 FROM jl_ar_ap_trx_dgi_codes dgi,
308 ra_cust_trx_types rctt
309 WHERE trx_letter = l_doc_letter
310 AND rctt.cust_trx_type_id = p_trx_type_id
311 AND trx_category = (select type from ra_cust_trx_types where
312 cust_trx_type_id = p_trx_type_id);
313 EXCEPTION
314 WHEN OTHERS THEN
315 l_doc_letter := NULL;
316 l_dgi_code := NULL;
317 END;
318
319 -- IF any of the components of the bar code is null then the entire bar code is
320 -- null
321
322 IF l_comp_cuit is NULL or l_dgi_code is NULL or l_branch_num is NULL or l_cai_num is NULL or l_cai_date is NULL THEN
323 RETURN NULL;
324 END IF;
325
326 l_num_bar_code := l_comp_cuit||l_dgi_code||l_branch_num||l_cai_num||l_cai_date;
327
328 Select length(l_num_bar_code) into l_leng from dual;
329
330 l_count := 1;
331 l_odd_temp := 0;
332
333 -- Sum up the digits at the odd positions
334 Loop
335
336 l_odd_temp := l_odd_temp + to_number(substr(l_num_bar_code,l_count,1));
337 l_count := l_count + 2;
338
339 exit when l_count > l_leng;
340
341 End Loop;
342
343 l_odd_temp := l_odd_temp * 3;
344
345 l_count := 2;
346 l_even_temp := 0;
347
348 -- Sum up the digits at the even positions
349 Loop
350
351 l_even_temp := l_even_temp + to_number(substr(l_num_bar_code,l_count,1));
352 l_count := l_count + 2;
353
354 exit when l_count > l_leng;
355
356 End Loop;
357
358 l_temp := l_odd_temp + l_even_temp;
359
360 -- Get the modulus of the sum
361 SELECT MOD(l_temp,10) into l_temp FROM dual;
362 -- Bug 8727000 FW Port of 11i Bug 8279519
363 -- MOD will return 0 if l_temp passed is multiple of 10
364 --In that case l_valid_digit will be 10-0 = 10 which is incorrect
365 IF l_temp = 0 then
366 l_valid_digit := to_char(l_temp);
367 ELSE
368
369 -- Validation Digit is equivalent to 10 - modulus
370 l_valid_digit := to_char(10 - l_temp);
371 END IF;
372
373 l_num_bar_code := l_num_bar_code || l_valid_digit;
374
375 RETURN l_num_bar_code;
376
377 END get_num_bar_code;
378
379
380 FUNCTION validate_document_letter
381 (p_batch_source_id IN NUMBER,
382 p_interface_line_id IN NUMBER,
383 p_created_from IN VARCHAR2,
384 p_ship_to_address_id IN NUMBER,
385 p_document_letter IN OUT NOCOPY VARCHAR2,
386 p_so_org_id IN VARCHAR2) RETURN VARCHAR2 IS
387
388 l_dummy_code VARCHAR2(15);
389 error_condition EXCEPTION;
390 l_return_code VARCHAR2(30);
391 l_match_flag VARCHAR2(1) := 'N';
392 l_so_org_id NUMBER;
393 l_auto_trx_numbering_flag VARCHAR2(1);
394 l_organization_class_code VARCHAR2(150);
395 l_contributor_class_code VARCHAR2(150);
396 l_loc_id NUMBER(15);
397 l_cus_cls_flag VARCHAR2(1);
398 l_org_id NUMBER;
399
400 CURSOR doc_letter_cursor IS
401 SELECT tax_category_id,
402 org_tax_attribute_name,
403 org_tax_attribute_value,
404 con_tax_attribute_name,
405 con_tax_attribute_value
406 FROM jl_ar_ar_doc_letter
407 WHERE document_letter = p_document_letter
408 AND sysdate between start_date_active and nvl(end_date_active,sysdate);
409
410 BEGIN
411 -- Commented out the following line, will be assigned 'SUCCESS' at the end of successful completion of the loop
412 -- l_return_code := 'SUCCESS';
413
414 IF p_created_from = 'ARXTWMAI' THEN
415 BEGIN
416 SELECT global_attribute3
417 INTO p_document_letter
418 FROM ra_batch_sources
419 WHERE batch_source_id = p_batch_source_id;
420 EXCEPTION
421 WHEN OTHERS THEN
422 l_return_code := 'JL_AR_AR_INVALID_DOC_LETTER';
423 RAISE error_condition;
424 END;
425 END IF;
426
427 --Bug 1404824 - l_so_org_id variable will be assigned using the parameter
428 --passed
429 --Commented the below line for bug 1612359
430 -- l_so_org_id := to_number(p_so_org_id);
431 l_org_id := mo_global.get_current_org_id;
432 l_so_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID',l_org_id);
433
434 BEGIN
435 l_loc_id := null;
436 SELECT hou.location_id
437 INTO l_loc_id
438 FROM hr_organization_units hou
439 WHERE hou.organization_id = l_so_org_id;
440 EXCEPTION
441 WHEN NO_DATA_FOUND THEN
442 IF p_created_from = 'RAXTRX' THEN
443 l_return_code := 'ERROR';
444 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
445 'JL_AR_AR_INVALID_ORGANIZATION',
446 p_interface_line_id,
447 l_so_org_id) THEN
448 RAISE error_condition;
449 END IF;
450 ELSIF p_created_from = 'ARXTWMAI' THEN
451 l_return_code := 'JL_AR_AR_INVALID_ORGANIZATION';
452 RAISE error_condition;
453 END IF;
454 END;
455 IF l_loc_id is not null then
456 BEGIN
457 SELECT hrl.global_attribute1
458 INTO l_organization_class_code
459 FROM hr_locations hrl,
460 hr_organization_units hrou
461 WHERE hrou.organization_id = l_so_org_id
462 AND hrl.location_id = hrou.location_id;
463 EXCEPTION WHEN NO_DATA_FOUND THEN
464 IF p_created_from = 'RAXTRX' THEN
465 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
466 'JL_AR_AR_INVALID_ORGANIZATION',
467 p_interface_line_id,
468 l_so_org_id) THEN
469 RAISE error_condition;
470 END IF;
471 ELSIF p_created_from = 'ARXTWMAI' THEN
472 l_return_code := 'JL_AR_AR_INVALID_ORGANIZATION';
473 RAISE error_condition;
474 END IF;
475 END;
476 ELSE
477 l_return_code := 'JL_AR_AR_LOCATION_NULL';
478 RAISE error_condition;
479 END IF;
480 IF l_organization_class_code is null then
481 IF p_created_from = 'RAXTRX' THEN
482 l_return_code := 'JL_AR_AR_AI_ORG_CLS_NOT_DEF';
483 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
484 'JL_AR_AR_AI_ORG_CLS_NOT_DEF',
485 p_interface_line_id,
486 l_so_org_id) THEN
487 RAISE error_condition;
488 END IF;
489 ELSIF p_created_from = 'ARXTWMAI' THEN
490 l_return_code := 'JL_AR_AR_ORG_CLS_CODE_NOT_DEF';
491 RAISE error_condition;
492 END IF;
493 END IF;
494
495 l_contributor_class_code := NULL;
496
497 IF p_ship_to_address_id IS NOT NULL THEN
498 IF p_created_from = 'ARXTWMAI' THEN
499 BEGIN
500 SELECT adr.global_attribute8
501 INTO l_contributor_class_code
502 FROM hz_cust_acct_sites_all adr,
503 hz_cust_site_uses rsu
504 WHERE rsu.site_use_id = p_ship_to_address_id
505 AND rsu.cust_acct_site_id = adr.cust_acct_site_id;
506 END;
507 IF l_contributor_class_code is null then
508 l_return_code := 'JL_AR_AR_CONT_CLS_CODE_NOT_DEF';
509 RAISE error_condition;
510 END IF;
511 ELSIF p_created_from = 'RAXTRX' THEN
512 BEGIN
513 SELECT cas.global_attribute8
514 INTO l_contributor_class_code
515 FROM hz_cust_acct_sites cas
516 WHERE cas.cust_acct_site_id = p_ship_to_address_id;
517 END;
518 IF l_contributor_class_code is null then
519 l_return_code := 'ERROR';
520 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
521 'JL_AR_AR_AI_CONT_CLS_NOT_DEF',
522 p_interface_line_id,
523 p_ship_to_address_id) THEN
524 RAISE error_condition;
525 END IF;
526 END IF;
527 END IF;
528 END IF;
529
530 FOR doc_letter_rec IN doc_letter_cursor
531 LOOP
532 l_match_flag := 'N';
533
534 BEGIN
535 l_dummy_code := NULL;
536
537 SELECT 'Success'
538 INTO l_dummy_code
539 FROM jl_zz_ar_tx_att_cls
540 WHERE tax_attr_class_code = l_organization_class_code
541 AND tax_attr_class_type = 'ORGANIZATION_CLASS'
542 AND tax_category_id = doc_letter_rec.tax_category_id
543 AND tax_attribute_name = doc_letter_rec.org_tax_attribute_name
544 AND tax_attribute_value = doc_letter_rec.org_tax_attribute_value;
545 EXCEPTION
546 WHEN NO_DATA_FOUND THEN
547 IF p_created_from = 'RAXTRX' THEN
548 l_return_code := 'JL_AR_AR_AI_ATT_CLS_NOT_DEF';
549 ELSIF p_created_from = 'ARXTWMAI' THEN
550 l_return_code := 'JL_AR_AR_ATT_CLS_NOT_DEF';
551 END IF;
552 END;
553
554 IF l_dummy_code = 'Success' THEN
555 l_match_flag := 'Y';
556 END IF;
557
558 l_dummy_code := NULL;
559 BEGIN
560 IF PG_DEBUG in ('Y', 'C') THEN
561 arp_standard.debug('validate_document_letter: ' || '-- validate doc letter contributor class code ' || l_contributor_class_code);
562 arp_standard.debug('validate_document_letter: ' || '-- validate doc letter ship to address id '|| to_char(p_ship_to_address_id));
563 arp_standard.debug('validate_document_letter: ' || '-- validate doc letter tax category id '||to_char(doc_letter_rec.tax_category_id));
564 arp_standard.debug('validate_document_letter: ' || '-- validate doc letter tax att name '||doc_letter_rec.con_tax_attribute_name);
565 arp_standard.debug('validate_document_letter: ' || '-- validate doc letter tax att value '||doc_letter_rec.con_tax_attribute_value);
566 END IF;
567 IF p_created_from = 'ARXTWMAI' THEN
568 SELECT nvl(cas.global_attribute9,'N')
569 INTO l_cus_cls_flag
570 FROM hz_cust_acct_sites_all cas, hz_cust_site_uses rsu
571 WHERE rsu.site_use_id = p_ship_to_address_id
572 AND rsu.cust_acct_site_id = cas.cust_acct_site_id;
573 --
574 IF l_cus_cls_flag = 'Y' THEN
575 SELECT 'Success'
576 INTO l_dummy_code
577 FROM jl_zz_ar_tx_cus_cls_all cus, hz_cust_acct_sites_all cas, hz_cust_site_uses rsu
578 WHERE tax_attr_class_code = l_contributor_class_code
579 AND rsu.site_use_id = p_ship_to_address_id
580 AND cas.cust_acct_site_id = cus.address_id
581 AND rsu.cust_acct_site_id = cas.cust_acct_site_id
582 AND tax_category_id = doc_letter_rec.tax_category_id
583 AND tax_attribute_name = doc_letter_rec.con_tax_attribute_name
584 AND tax_attribute_value = doc_letter_rec.con_tax_attribute_value;
585 ELSE
586 SELECT 'Success'
587 INTO l_dummy_code
588 FROM jl_zz_ar_tx_att_cls att
589 WHERE tax_attr_class_type = 'CONTRIBUTOR_CLASS'
590 AND tax_attr_class_code = l_contributor_class_code
591 AND tax_category_id = doc_letter_rec.tax_category_id
592 AND tax_attribute_name = doc_letter_rec.con_tax_attribute_name
593 AND tax_attribute_value = doc_letter_rec.con_tax_attribute_value;
594
595 END IF;
596
597 ELSE
598 SELECT nvl(cas.global_attribute9,'N')
599 INTO l_cus_cls_flag
600 FROM hz_cust_acct_sites cas
601 WHERE cas.cust_acct_site_id = p_ship_to_address_id;
602 IF l_cus_cls_flag = 'Y' THEN
603 SELECT 'Success'
604 INTO l_dummy_code
605 FROM jl_zz_ar_tx_cus_cls_all cus, hz_cust_acct_sites cas
606 WHERE tax_attr_class_code = l_contributor_class_code
607 AND cas.cust_acct_site_id = cus.address_id
608 AND cas.cust_acct_site_id = p_ship_to_address_id
609 AND tax_category_id = doc_letter_rec.tax_category_id
610 AND tax_attribute_name = doc_letter_rec.con_tax_attribute_name
611 AND tax_attribute_value = doc_letter_rec.con_tax_attribute_value;
612 ELSE
613 SELECT 'Success'
614 INTO l_dummy_code
615 FROM jl_zz_ar_tx_att_cls att
616 WHERE tax_attr_class_type = 'CONTRIBUTOR_CLASS'
617 AND tax_attr_class_code = l_contributor_class_code
618 AND tax_category_id = doc_letter_rec.tax_category_id
619 AND tax_attribute_name = doc_letter_rec.con_tax_attribute_name
620 AND tax_attribute_value = doc_letter_rec.con_tax_attribute_value;
621
622 END IF;
623 END IF;
624
625 EXCEPTION
626 WHEN NO_DATA_FOUND THEN
627 IF p_created_from = 'RAXTRX' THEN
628 l_return_code := 'JL_AR_AR_AI_CU_SIT_PRO_NOT_DEF';
629 ELSIF p_created_from = 'ARXTWMAI' THEN
630 l_return_code := 'JL_AR_AR_CUS_SITE_PROF_NOT_DEF';
631 END IF;
632 l_dummy_code := NULL;
633 l_match_flag := 'N';
634 END;
635
636 IF l_dummy_code = 'Success' AND l_match_flag = 'Y' THEN
637 IF PG_DEBUG in ('Y', 'C') THEN
638 arp_standard.debug('validate_document_letter: ' || '-- Successfully found a combination ');
639 END IF;
640 -- Assigned SUCCESS to l_return_code Bug 1323607
641 l_return_code := 'SUCCESS';
642 EXIT;
643 END IF;
644
645 END LOOP;
646
647 IF l_match_flag = 'N' THEN
648 IF PG_DEBUG in ('Y', 'C') THEN
649 arp_standard.debug('validate_document_letter: ' || '-- match flag is n');
650 END IF;
651 -- Added the following if condition Bug 1323607
652
653 IF l_return_code IS NULL THEN -- Cursor contained no records
654 l_return_code := 'JL_AR_AR_DOC_LET_NOT_FOUND';
655 END IF;
656
657 IF p_created_from = 'RAXTRX' THEN
658 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
659 l_return_code,
660 p_interface_line_id,
661 p_batch_source_id) THEN
662
663 RAISE error_condition;
664 END IF;
665 ELSIF p_created_from = 'ARXTWMAI' THEN
666 RAISE error_condition;
667 END IF;
668 END IF;
669
670 RETURN l_return_code;
671
672 EXCEPTION
673 WHEN OTHERS THEN
674 IF l_return_code is null then
675 l_return_code := 'JL_AR_AR_INVALID_DOC_LETTER';
676 END IF;
677 RETURN l_return_code;
678
679 END validate_document_letter;
680
681 FUNCTION validate_interface_lines ( p_request_id IN NUMBER
682 ,p_interface_line_id IN NUMBER
683 ,p_trx_type IN NUMBER
684 ,p_inventory_item_id IN NUMBER
685 ,p_memo_line_id IN NUMBER
686 ,p_trx_date IN DATE
687 ,p_orig_system_address_id IN NUMBER
688 ,p_so_org_id IN VARCHAR2)
689 RETURN BOOLEAN IS
690
691 l_dummy_code VARCHAR2(15);
692 error_condition EXCEPTION;
693 l_return_code NUMBER;
694 l_max_trx_date DATE;
695 l_batch_source_id NUMBER(15);
696 l_auto_trx_numbering_flag VARCHAR2(1);
697 l_last_trx_date DATE;
698 l_advance_days NUMBER;
699 l_document_letter VARCHAR2(1);
700 l_invoice_class VARCHAR2(20);
701
702 BEGIN
703 IF PG_DEBUG in ('Y', 'C') THEN
704 arp_standard.debug('validate_interface_lines: ' || '-- JL_AR_DOC_NUMBERING_PKG validation begins');
705 END IF;
706
707 l_return_code := 1;
708 IF PG_DEBUG in ('Y', 'C') THEN
709 arp_standard.debug('validate_interface_lines: ' || '-- trying to get the batch source id for the request');
710 END IF;
711 BEGIN
712 SELECT to_number(argument3)
713 INTO l_batch_source_id
714 FROM fnd_concurrent_requests
715 WHERE request_id = p_request_id;
716 IF PG_DEBUG in ('Y', 'C') THEN
717 arp_standard.debug('validate_interface_lines: ' || '-- the batch source id for the request is '||to_char(l_batch_source_id));
718 END IF;
719 END;
720
721 IF PG_DEBUG in ('Y', 'C') THEN
722 arp_standard.debug('validate_interface_lines: ' || '-- trying to get the batch source gdf segments');
723 END IF;
724 BEGIN
725 SELECT auto_trx_numbering_flag
726 ,substr(global_attribute3,1,1)
727 ,to_date(global_attribute4,'YYYY/MM/DD HH24:MI:SS')
728 ,to_number(global_attribute5)
729 INTO l_auto_trx_numbering_flag
730 ,l_document_letter
731 ,l_last_trx_date
732 ,l_advance_days
733 FROM ra_batch_sources
734 WHERE batch_source_id = l_batch_source_id;
735 END;
736 IF PG_DEBUG in ('Y', 'C') THEN
737 arp_standard.debug('validate_interface_lines: ' || '-- the batch source gdf segments found');
738 END IF;
739
740 IF l_auto_trx_numbering_flag ='N' THEN
741 IF PG_DEBUG in ('Y', 'C') THEN
742 arp_standard.debug('validate_interface_lines: ' || '-- auto trx numbering flag is N');
743 END IF;
744 l_return_code := 1;
745 RAISE error_condition;
746 END IF;
747 IF PG_DEBUG in ('Y', 'C') THEN
748 arp_standard.debug('validate_interface_lines: ' || '-- auto trx numbering flag is Y');
749 arp_standard.debug('validate_interface_lines: ' || '-- validate document letter begins');
750 END IF;
751
752 IF validate_document_letter (l_batch_source_id
753 ,p_interface_line_id
754 ,'RAXTRX'
755 ,p_orig_system_address_id
756 ,l_document_letter
757 ,p_so_org_id ) <> 'SUCCESS' THEN
758 IF PG_DEBUG in ('Y', 'C') THEN
759 arp_standard.debug('validate_interface_lines: ' || '-- validate document letter set up problem');
760 END IF;
761 l_return_code := 1;
762 END IF;
763
764 IF PG_DEBUG in ('Y', 'C') THEN
765 arp_standard.debug('validate_interface_lines: ' || '-- trying to get the type for the trx type');
766 END IF;
767 BEGIN
768 SELECT type
769 INTO l_invoice_class
770 FROM ra_cust_trx_types
771 WHERE cust_trx_type_id = p_trx_type;
772 IF PG_DEBUG in ('Y', 'C') THEN
773 arp_standard.debug('validate_interface_lines: ' || '-- the type for the trx type found');
774 END IF;
775 END;
776 IF PG_DEBUG in ('Y', 'C') THEN
777 arp_standard.debug('validate_interface_lines: ' || '-- validate trx type begins');
778 END IF;
779
780 IF validate_trx_type (l_batch_source_id
781 ,p_trx_type
782 ,l_invoice_class
783 ,l_document_letter
784 ,p_interface_line_id
785 ,'RAXTRX' ) <> 'SUCCESS' THEN
786 IF PG_DEBUG in ('Y', 'C') THEN
787 arp_standard.debug('validate_interface_lines: ' || '-- validate trx types and sources are not set');
788 END IF;
789 l_return_code := 1;
790 END IF;
791
792 IF PG_DEBUG in ('Y', 'C') THEN
793 arp_standard.debug('validate_interface_lines: ' || '-- validate branch number begins');
794 END IF;
795 IF validate_four_digit (l_batch_source_id
796 ,p_interface_line_id
797 ,'RAXTRX'
798 ,p_inventory_item_id
799 ,p_memo_line_id
800 ,p_so_org_id ) <> 'SUCCESS' THEN
801 IF PG_DEBUG in ('Y', 'C') THEN
802 arp_standard.debug('validate_interface_lines: ' || '-- branch number validation problem');
803 END IF;
804 l_return_code := 1;
805 END IF;
806
807 IF PG_DEBUG in ('Y', 'C') THEN
808 arp_standard.debug('validate_interface_lines: ' || '-- validate trx date begins');
809 END IF;
810 IF p_trx_date BETWEEN
811 l_last_trx_date AND (sysdate+l_advance_days) THEN
812 IF p_trx_date <> l_last_trx_date THEN
813 l_max_trx_date := p_trx_date;
814 END IF;
815 IF PG_DEBUG in ('Y', 'C') THEN
816 arp_standard.debug('validate_interface_lines: ' || '-- validate trx date ok');
817 END IF;
818 ELSE
819 BEGIN
820 IF PG_DEBUG in ('Y', 'C') THEN
821 arp_standard.debug('validate_interface_lines: ' || '-- date validation problem');
822 END IF;
823 l_return_code := 1;
824 IF p_trx_date < l_last_trx_date THEN
825 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
826 'JL_AR_AR_INVALID_TRANS_DATE',
827 to_char(p_interface_line_id),
828 to_char(l_batch_source_id)) THEN
829 RAISE error_condition;
830 END IF;
831 ELSE
832 IF NOT JG_ZZ_AUTO_INVOICE.put_error_message ('JL',
833 'JL_AR_AR_INVALID_TRX_DT_AFT',
834 to_char(p_interface_line_id),
835 to_char(l_batch_source_id)) THEN
836 RAISE error_condition;
837 END IF;
838 END IF;
839 END;
840 END IF;
841
842 IF PG_DEBUG in ('Y', 'C') THEN
843 arp_standard.debug('validate_interface_lines: ' || '-- update the dates');
844 END IF;
845 IF l_last_trx_date <> l_max_trx_date THEN
846 UPDATE ra_batch_sources
847 SET global_attribute4 = fnd_date.date_to_canonical(l_max_trx_date)
848 WHERE batch_source_id = l_batch_source_id;
849 END IF;
850
851 IF l_return_code = 0 THEN
852 RETURN FALSE;
853 ELSE
854 RETURN TRUE;
855 END IF;
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 IF PG_DEBUG in ('Y', 'C') THEN
860 arp_standard.debug('validate_interface_lines: ' || '-- validate interface lines problem - in others1');
861 END IF;
862 IF l_auto_trx_numbering_flag ='N' THEN
863 l_return_code := 1;
864 ELSE
865 l_return_code := 0;
866 END IF;
867 IF PG_DEBUG in ('Y', 'C') THEN
868 arp_standard.debug('validate_interface_lines: ' || '-- validate interface lines problem - in others2');
869 END IF;
870 IF l_return_code = 0 THEN
871 RETURN FALSE;
872 ELSE
873 RETURN TRUE;
874 END IF;
875
876 END validate_interface_lines;
877
878 FUNCTION get_imported_batch_source (p_batch_source_id IN NUMBER)
879 RETURN NUMBER IS
880 l_imp_batch_src NUMBER;
881 l_imp_batch_type varchar2(30);
882 BEGIN
883 l_imp_batch_src := NULL;
884 BEGIN
885 SELECT decode(batch_source_type, 'INV',to_number(global_attribute1),
886 batch_source_id)
887 INTO l_imp_batch_src
888 FROM ra_batch_sources
889 WHERE batch_source_id = p_batch_source_id;
890 EXCEPTION
891 WHEN OTHERS THEN
892 l_imp_batch_src := NULL;
893 END;
894 RETURN l_imp_batch_src;
895 END;
896
897 FUNCTION get_flex_value(p_concat_segs IN VARCHAR2,p_flex_delimiter IN VARCHAR2)
898 RETURN VARCHAR2 IS
899 l_num_code VARCHAR2(50);
900 BEGIN
901
902 SELECT substr(p_concat_segs,instr(p_concat_segs,''||p_flex_delimiter||'',1,4)+1) INTO l_num_code FROM dual;
903
904 RETURN l_num_code;
905
906 EXCEPTION
907 WHEN OTHERS THEN
908 RETURN NULL;
909 END;
910
911 FUNCTION get_flex_delimiter RETURN VARCHAR2 IS
912 l_dfinfo_rec FND_FLEX_SERVER1.DESCFLEXINFO;
913 l_success_bln BOOLEAN;
914 BEGIN
915 l_success_bln:= FND_FLEX_SERVER2.GET_DESCSTRUCT('JG','JG_RA_CUSTOMER_TRX',l_dfinfo_rec);
916 IF l_success_bln = TRUE THEN
917 RETURN l_dfinfo_rec.segment_delimiter;
918 ELSE
919 RETURN NULL;
920 END IF;
921 EXCEPTION
922 WHEN OTHERS THEN
923 NULL;
924 END;
925
926 FUNCTION get_batch_source_type (p_batch_source_id IN NUMBER)
927 RETURN VARCHAR2 IS
928 l_imp_batch_src_type varchar2(30);
929 BEGIN
930 l_imp_batch_src_type := NULL;
931 BEGIN
932 SELECT batch_source_type
933 INTO l_imp_batch_src_type
934 FROM ra_batch_sources
935 WHERE batch_source_id = p_batch_source_id;
936 EXCEPTION
937 WHEN OTHERS THEN
938 l_imp_batch_src_type := NULL;
939 END;
940 RETURN l_imp_batch_src_type;
941 END get_batch_source_type;
942
943 FUNCTION validate_transaction_date
944 (p_trx_date IN DATE,
945 p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
946 l_return_code varchar2(30);
947 BEGIN
948 l_return_code := 'SUCCESS';
949 BEGIN
950 SELECT 'SUCCESS'
951 INTO l_return_code
952 FROM ra_batch_sources
953 WHERE batch_source_id = p_batch_source_id
954 AND batch_source_type = 'FOREIGN'
955 AND p_trx_date BETWEEN
956 to_date(global_attribute4,'YYYY/MM/DD HH24:MI:SS')
957 AND SYSDATE + to_number(global_attribute5);
958 EXCEPTION
959 WHEN OTHERS THEN
960 l_return_code := 'JL_AR_AR_INVALID_TRX_DATE';
961 END;
962 RETURN l_return_code;
963 END validate_transaction_date;
964
965
966 FUNCTION get_printing_count (p_cust_trx_id IN VARCHAR2) RETURN NUMBER IS
967 l_return_code number(38) := 0;
968 BEGIN
969 BEGIN
970 SELECT nvl(printing_count,0)
971 INTO l_return_code
972 FROM ra_customer_trx
973 WHERE customer_trx_id = p_cust_trx_id;
974 EXCEPTION
975 WHEN OTHERS THEN
976 l_return_code := 0;
977 END;
978 RETURN l_return_code;
979 END get_printing_count;
980
981 FUNCTION get_branch_number_method RETURN VARCHAR2 IS
982 l_br_numb_method varchar2(20);
983 BEGIN
984 BEGIN
985 SELECT global_attribute1
986 INTO l_br_numb_method
987 FROM ar_system_parameters;
988 EXCEPTION
989 WHEN OTHERS THEN
990 l_br_numb_method := null;
991 END;
992 RETURN l_br_numb_method;
993 END get_branch_number_method;
994
995 FUNCTION get_point_of_sale_code(p_inv_org_id IN VARCHAR2) RETURN VARCHAR2 IS
996 l_br_numb_code varchar2(4);
997 BEGIN
998 BEGIN
999 SELECT hl.global_attribute7
1000 INTO l_br_numb_code
1001 FROM hr_locations hl, hr_organization_units hou
1002 WHERE hl.location_id = hou.location_id
1003 AND hou.organization_id = p_inv_org_id;
1004 EXCEPTION
1005 WHEN OTHERS THEN
1006 l_br_numb_code := -1;
1007 END;
1008 IF l_br_numb_code IS NULL THEN
1009 l_br_numb_code := -1;
1010 END IF;
1011 RETURN l_br_numb_code;
1012 END get_point_of_sale_code;
1013
1014 FUNCTION get_doc_letter(p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
1015 l_doc_letter varchar2(4);
1016 BEGIN
1017 BEGIN
1018 SELECT substr(rbs.global_attribute3,1,1)
1019 INTO l_doc_letter
1020 FROM ra_batch_sources rbs
1021 WHERE rbs.batch_source_id = p_batch_source_id;
1022 EXCEPTION
1023 WHEN OTHERS THEN
1024 l_doc_letter := null;
1025 END;
1026 RETURN l_doc_letter;
1027 END get_doc_letter;
1028
1029 FUNCTION get_branch_number(p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
1030 l_br_number varchar2(4);
1031 BEGIN
1032 BEGIN
1033 SELECT lpad(substr(rbs.global_attribute2,1,4),4,'0')
1034 INTO l_br_number
1035 FROM ra_batch_sources rbs
1036 WHERE rbs.batch_source_id = p_batch_source_id;
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 l_br_number := null;
1040 END;
1041 RETURN l_br_number;
1042 END get_branch_number;
1043
1044 FUNCTION get_last_trx_date(p_batch_source_id IN NUMBER) RETURN DATE IS
1045 l_last_trx_date date;
1046 BEGIN
1047 BEGIN
1048 SELECT to_date(rbs.global_attribute4,'YYYY/MM/DD HH24:MI:SS')
1049 INTO l_last_trx_date
1050 FROM ra_batch_sources rbs
1051 WHERE rbs.batch_source_id = p_batch_source_id;
1052 EXCEPTION
1053 WHEN OTHERS THEN
1054 l_last_trx_date := null;
1055 END;
1056 RETURN l_last_trx_date;
1057 END get_last_trx_date;
1058
1059 FUNCTION get_adv_days(p_batch_source_id IN NUMBER) RETURN VARCHAR2 IS
1060 l_adv_days varchar2(5);
1061 BEGIN
1062 BEGIN
1063 SELECT substr(rbs.global_attribute5,1,3)
1064 INTO l_adv_days
1065 FROM ra_batch_sources rbs
1066 WHERE rbs.batch_source_id = p_batch_source_id;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 l_adv_days := null;
1070 END;
1071 RETURN l_adv_days;
1072 END get_adv_days;
1073
1074 FUNCTION get_hr_branch_number(p_location_id IN NUMBER) RETURN VARCHAR2 IS
1075 l_hr_br_number varchar2(4);
1076 BEGIN
1077 BEGIN
1078 SELECT lpad(substr(hl.global_attribute7,1,4),4,'0')
1079 INTO l_hr_br_number
1080 FROM hr_locations hl
1081 WHERE hl.location_id = p_location_id;
1082 EXCEPTION
1083 WHEN OTHERS THEN
1084 l_hr_br_number := null;
1085 END;
1086 RETURN l_hr_br_number;
1087 END get_hr_branch_number;
1088
1089 FUNCTION trx_num_gen(p_batch_source_id IN NUMBER,
1090 p_trx_number IN VARCHAR2) RETURN VARCHAR2 IS
1091 l_country_code VARCHAR2(2);
1092 l_document_letter VARCHAR2(1);
1093 l_branch_number VARCHAR2(4);
1094 l_auto_trx_numbering_flag ra_batch_sources_all.auto_trx_numbering_flag%TYPE;
1095 l_trx_number ra_customer_trx_all.trx_number%TYPE;
1096 l_org_id NUMBER;
1097 l_ledger_id NUMBER;
1098 l_batch_src_type ra_batch_sources_all.batch_source_type%TYPE;
1099 l_source_id ra_batch_sources_all.batch_source_id%TYPE;
1100 l_seq_name VARCHAR2(100);
1101 -- This function is being called from auto invoice.
1102 l_trx_num_cursor INTEGER;
1103 --l_seq_name VARCHAR2(100);
1104 seq_no number;
1105 l_count number;
1106 l_string varchar2(1000);
1107
1108 BEGIN
1109 l_org_id := mo_global.get_current_org_id;
1110 l_ledger_id := NULL;
1111 l_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY(l_org_id,l_ledger_id);
1112 -- MOAC changes
1113 --l_country_code := FND_PROFILE.VALUE('JGZZ_COUNTRY_CODE');
1114 l_trx_number := p_trx_number;
1115
1116 IF l_country_code = 'AR' THEN
1117
1118 SELECT batch_source_type into l_batch_src_type
1119 FROM ra_batch_sources_all
1120 WHERE batch_source_id = p_batch_source_id;
1121
1122 IF l_batch_src_type <> 'FOREIGN' THEN
1123
1124 SELECT global_attribute1 into l_source_id
1125 FROM ra_batch_sources_all
1126 WHERE batch_source_id = p_batch_source_id;
1127
1128 SELECT substr(global_attribute2,1,4),
1129 substr(global_attribute3,1,1),
1130 auto_trx_numbering_flag
1131 INTO l_branch_number,
1132 l_document_letter,
1133 l_auto_trx_numbering_flag
1134 FROM ra_batch_sources_all
1135 WHERE batch_source_id = l_source_id;
1136 --Bug#7697795
1137 --Start
1138 l_seq_name := 'RA_TRX_NUMBER_'
1139 || to_char(l_source_id)
1140 || '_'
1141 || l_org_id
1142 || '_S';
1143
1144
1145 l_string := 'select '||
1146 l_seq_name||'.nextval seq_number '||
1147 'from dual' ;
1148
1149 execute immediate l_string into seq_no;
1150 l_trx_number := seq_no;
1151
1152 --End
1153
1154 ELSE
1155
1156 SELECT substr(global_attribute2,1,4),
1157 substr(global_attribute3,1,1),
1158 auto_trx_numbering_flag
1159 INTO l_branch_number,
1160 l_document_letter,
1161 l_auto_trx_numbering_flag
1162 FROM ra_batch_sources_all
1163 WHERE batch_source_id = p_batch_source_id;
1164
1165 END IF;
1166
1167 IF l_auto_trx_numbering_flag = 'Y' AND
1168 substr(p_trx_number,1,6) <> l_document_letter || '-' ||
1169 l_branch_number THEN
1170 l_trx_number := l_document_letter || '-' || l_branch_number || '-'
1171 || lpad(l_trx_number,8,'0');
1172 END IF;
1173 END IF;
1174
1175 RETURN l_trx_number;
1176 EXCEPTION
1177 WHEN OTHERS THEN
1178 RAISE;
1179 END;
1180
1181 BEGIN
1182
1183 PG_DEBUG := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1184
1185 END JL_AR_DOC_NUMBERING_PKG;
[Home] [Help]