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