1 PACKAGE BODY JL_ZZ_AP_LIBRARY_1_PKG AS
2 /* $Header: jlzzpl1b.pls 120.11 2006/05/05 22:21:29 dbetanco ship $ */
3
4 Procedure get_state_valid(vscountry IN Varchar2,
5 vsstate IN Varchar2,
6 row_number IN Number,
7 errcd IN OUT NOCOPY Number) Is
8 l_exists VARCHAR2(6);
9 Begin
10 errcd := 0;
11 IF vscountry = 'US' THEN
12 Begin
13 select 'exists'
14 into l_exists
15 from ap_income_tax_regions
16 where sysdate < nvl(inactive_date, sysdate+1)
17 and region_short_name = vsstate;
18 Exception
19 When No_Data_Found Then
20 errcd := 1;
21 When Others Then
22 errcd := sqlcode;
23 End;
24
25 ELSIF vscountry = 'BR' THEN
26 Begin
27 select 'exists'
28 into l_exists
29 from hz_geographies geo
30 where geo.country_code = 'BR'
31 and nvl(start_date, sysdate) <= sysdate
32 and nvl(end_date, sysdate) >= sysdate
33 and geo.geography_type = 'STATE'
34 and geo.geography_code = vsstate;
35
36 Exception
37 When No_Data_Found Then
38 errcd := 1;
39 When Others Then
40 errcd := sqlcode;
41 End;
42
43 END IF;
44 Exception
45 When Others Then
46 errcd := sqlcode;
47 End get_state_valid;
48
49 Procedure get_context_name(vdesc IN OUT NOCOPY Varchar2,
50 row_number IN Number,
51 errcd IN OUT NOCOPY Number) Is
52 Begin
53 errcd := 0;
54 select substr(description,1,30)
55 into vdesc
56 from fnd_descr_flex_contexts_vl
57 where application_id = 7003
58 and descriptive_flexfield_name = 'JG_AP_SYSTEM_PARAMETERS'
59 and descriptive_flex_context_code = 'JL.BR.APXCUMSP.SYS_PARAMETER'
60 and enabled_flag = 'Y' and rownum = row_number;
61 Exception
62 When Others Then
63 errcd := sqlcode;
64 End get_context_name;
65
66 Procedure get_interest_type(vndstid IN Number,
67 inttyp IN OUT NOCOPY Varchar2,
68 row_number IN Number,
69 errcd IN OUT NOCOPY Number) Is
70 Begin
71 errcd := 0;
72 SELECT L.LOOKUP_CODE
73 Into inttyp
74 FROM FND_LOOKUPS L,PO_VENDOR_SITES V
75 WHERE L.LOOKUP_CODE = V.GLOBAL_ATTRIBUTE2
76 AND L.LOOKUP_TYPE = 'JLBR_INTEREST_PENALTY_TYPE'
77 AND V.VENDOR_SITE_ID = vndstid and rownum = row_number;
78 Exception
79 When Others Then
80 errcd := sqlcode;
81 End get_interest_type;
82
83 Procedure get_interest_penalty_details(vndstid IN Number,
84 pntamt IN OUT NOCOPY Varchar2,
85 intgrd IN OUT NOCOPY Varchar2,
86 intprd IN OUT NOCOPY Varchar2,
87 intamt IN OUT NOCOPY Varchar2,
88 row_number IN Number,
89 errcd IN OUT NOCOPY Number) Is
90 Begin
91 errcd := 0;
92 SELECT GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE6,
93 GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE3
94 INTO pntamt, intgrd, intprd, intamt
95 FROM PO_VENDOR_SITES
96 WHERE VENDOR_SITE_ID = vndstid and rownum = row_number;
97 Exception
98 When Others Then
99 errcd := sqlcode;
100 End get_interest_penalty_details;
101
102 Procedure get_interest_formula(vndstid IN Number,
103 intfml IN OUT NOCOPY Varchar2,
104 row_number IN Number,
105 errcd IN OUT NOCOPY Number) Is
106 Begin
107 errcd := 0;
108 SELECT L.LOOKUP_CODE
109 INTO intfml
110 FROM FND_LOOKUPS L, PO_VENDOR_SITES V
111 WHERE L.LOOKUP_CODE = V.GLOBAL_ATTRIBUTE5
112 AND L.LOOKUP_TYPE = 'JLBR_INTEREST_FORMULA'
113 AND V.VENDOR_SITE_ID = vndstid and rownum = row_number;
114 Exception
115 When Others Then
116 errcd := sqlcode;
117 End get_interest_formula;
118
119 Procedure get_penalty_type(vndstid IN Number,
120 pnttyp IN OUT NOCOPY Varchar2,
121 row_number IN Number,
122 errcd IN OUT NOCOPY Number) Is
123 Begin
124 errcd := 0;
125 SELECT L.LOOKUP_CODE
126 INTO pnttyp
127 FROM FND_LOOKUPS L, PO_VENDOR_SITES V
128 WHERE L.LOOKUP_CODE = V.GLOBAL_ATTRIBUTE7
129 AND L.LOOKUP_TYPE = 'JLBR_INTEREST_PENALTY_TYPE'
130 AND V.VENDOR_SITE_ID = vndstid and rownum = row_number;
131 Exception
132 When Others Then
133 errcd := sqlcode;
134 End get_penalty_type;
135
136 Procedure get_cons_inv_num(invoiceid IN Number,
137 cons_inv_num IN OUT NOCOPY Number,
138 row_number IN Number,
139 errcd IN OUT NOCOPY Number) Is
140 Begin
141 errcd := 0;
142 SELECT substr(global_attribute10,1,38)
143 INTO cons_inv_num
144 FROM ap_invoices
145 WHERE invoice_id = invoiceid and rownum = row_number;
146 Exception
147 When Others Then
148 errcd := sqlcode;
149 End get_cons_inv_num;
150
151 Procedure get_payment_status_flag(invoiceid IN Number,
152 pay_stat IN OUT NOCOPY Varchar2,
153 row_number IN Number,
154 errcd IN OUT NOCOPY Number) Is
155 Begin
156 errcd := 0;
157 select payment_status_flag
158 into pay_stat
159 from ap_invoices
160 where invoice_id = invoiceid and rownum = row_number;
161 Exception
162 When Others Then
163 errcd := sqlcode;
164 End get_payment_status_flag;
165
166 Procedure get_associated_payment_count(invoiceid IN Number,
167 tot_recs IN OUT NOCOPY Number,
168 row_number IN Number,
169 errcd IN OUT NOCOPY Number) Is
170 Begin
171 errcd := 0;
172 select count(*)
173 into tot_recs
174 from ap_payment_schedules
175 where invoice_id = invoiceid
176 and global_attribute11 is not null;
177 Exception
178 When Others Then
179 errcd := sqlcode;
180 End get_associated_payment_count;
181
182 Procedure get_podist_ccid(line_locn_id IN Number,
183 ccid IN OUT NOCOPY Number,
184 row_number IN Number,
185 errcd IN OUT NOCOPY Number) Is
186 Begin
187 errcd := 0;
188 select code_combination_id
189 into ccid
190 from po_distributions_ap2_v
191 where line_location_id = line_locn_id
192 and rownum < 2 ;
193 Exception
194 When No_data_found Then
195 errcd := sqlcode;
196 When Others Then
197 errcd := sqlcode;
198 End get_podist_ccid;
199
200 Procedure get_account_type(ccid IN Number,
201 account_type IN OUT NOCOPY Varchar2,
202 row_number IN Number,
203 errcd IN OUT NOCOPY Number) Is
204 Begin
205 errcd := 0;
206 select decode(account_type,'A','Y','N')
207 Into account_type
208 from gl_code_combinations
209 where code_combination_id = ccid and rownum = row_number;
210 Exception
211 When Others Then
212 errcd := sqlcode;
213 End get_account_type;
214
215 Procedure get_tax_ccid(tax_name IN Varchar2,
216 ccid IN OUT NOCOPY Number,
217 row_number IN Number,
218 errcd IN OUT NOCOPY Number,
219 p_val_date IN Date) Is
220 Begin
221 errcd := 0;
222 select tax_code_combination_id
223 Into ccid
224 from ap_tax_codes
225 where name = tax_name
226 and nvl(start_date,p_val_date) <= p_val_date
227 and nvl(inactive_date,p_val_date+1) > p_val_date
228 and nvl(enabled_flag,'Y') = 'Y';
229 Exception
230 When Others Then
231 errcd := sqlcode;
232 End get_tax_ccid;
233
234 Procedure get_hr_loc_distccid(locn_id IN Number,
235 ccid IN OUT NOCOPY Number,
236 row_number IN Number,
237 errcd IN OUT NOCOPY Number) Is
238 Begin
239 errcd := 0;
240 select to_number(global_attribute2)
241 Into ccid
242 from hr_locations_all
243 where location_id = locn_id and rownum = row_number;
244 Exception
245 When Others Then
246 errcd := sqlcode;
247 End get_hr_loc_distccid;
248
249 Procedure get_max_dist_line_num(invoiceid IN Number,
250 max_dist_line_num IN OUT NOCOPY Number,
251 row_number IN Number,
252 errcd IN OUT NOCOPY Number) Is
253 Begin
254 errcd := 0;
255 select nvl(max(distribution_line_number),0)
256 into max_dist_line_num
257 from ap_invoice_distributions
258 where invoice_id = invoiceid
259 -- Commented out as unnecessary - iswillia (08-OCT-1999)
260 --and rownum = row_number
261 -- End of Commented out portion - iswillia (08-OCT-1999)
262 ;
263 Exception
264 When Others Then
265 errcd := sqlcode;
266 End get_max_dist_line_num;
267
268 Procedure get_po_dist_id(line_locn_id IN Number,
269 distid IN OUT NOCOPY Number,
270 row_number IN Number,
271 errcd IN OUT NOCOPY Number) Is
272 Begin
273 errcd := 0;
274 SELECT po_distribution_id
275 INTO distid
276 FROM po_distributions_ap2_v
277 WHERE line_location_id = line_locn_id
278 AND rownum < 2 ;
279 Exception
280 When No_data_found Then
281 errcd := sqlcode;
282 When Others Then
283 errcd := sqlcode;
284 End get_po_dist_id;
285
286 FUNCTION Get_Inv_Distrib_ID RETURN number IS
287
288 l_invoice_distribution_id number(38);
289
290 BEGIN
291
292 Begin
293 SELECT ap_invoice_distributions_s.nextval
294 INTO l_invoice_distribution_id
295 FROM dual;
296
297 Exception
298 WHEN others then
299 null;
300 End;
301
302 return(l_invoice_distribution_id);
303
304 END Get_Inv_Distrib_ID;
305
306 PROCEDURE Get_Invoice_Information(p_invoice_id IN number,
307 p_last_update_date IN OUT NOCOPY Date,
308 p_Last_Updated_By IN OUT NOCOPY number,
309 p_Set_Of_Books_Id IN OUT NOCOPY number,
310 p_Type_1099 IN OUT NOCOPY varchar2,
311 p_Last_Update_Login IN OUT NOCOPY number,
312 p_Creation_Date IN OUT NOCOPY Date,
313 p_Created_By IN OUT NOCOPY number,
314 p_exchange_rate IN OUT NOCOPY Number,
315 p_exchange_rate_type IN OUT NOCOPY Varchar2,
316 p_exchange_date IN OUT NOCOPY Date,
317 p_invoice_amount_limit IN OUT NOCOPY number,
318 p_amount_hold_flag IN OUT NOCOPY varchar2,
319 p_cfo_code IN OUT NOCOPY varchar2
320 ) IS
321 l_last_update_date Date;
322 l_Last_Updated_By number;
323 l_Set_Of_Books_Id number;
324 l_Type_1099 varchar2(10);
325 l_Last_Update_Login number;
326 l_Creation_Date Date;
327 l_Created_By number;
328 l_exchange_rate Number(38);
329 l_exchange_rate_type Varchar2(30);
330 l_exchange_date Date;
331 l_invoice_amount_limit number(38);
332 l_amount_hold_flag varchar2(1);
333 l_cfo_code varchar2(5);
334
335 BEGIN
336
337 Begin
338 SELECT set_of_books_id,
339 type_1099,
340 exchange_rate,
341 exchange_rate_type,
342 exchange_date,
343 invoice_amount_limit,
344 substr(amount_hold_flag,1,1),
345 rtrim(substr(global_attribute2,1,5)),
346 last_update_date,
347 last_Updated_By,
348 last_Update_Login,
349 creation_date,
350 created_by
351 INTO l_Set_Of_Books_Id,
352 l_Type_1099,
353 l_exchange_rate ,
354 l_exchange_rate_type ,
355 l_exchange_date,
356 l_invoice_amount_limit,
357 l_amount_hold_flag ,
358 l_cfo_code,
359 l_last_update_date ,
360 l_last_Updated_By ,
361 l_last_Update_Login ,
362 l_creation_date,
363 l_created_by
364 FROM ap_invoices_v
365 where invoice_id = P_Invoice_id;
366 Exception When no_data_found then
367 null;
368 When others then
369 null;
370 End;
371
372 p_set_of_books_id := l_set_of_books_id;
373 p_type_1099 := l_type_1099;
374 p_exchange_rate := l_exchange_rate;
375 p_exchange_rate_type := l_exchange_rate_type;
376 p_exchange_date := l_exchange_date;
377 p_invoice_amount_limit := l_invoice_amount_limit;
378 p_amount_hold_flag := l_amount_hold_flag;
379 p_cfo_code := l_cfo_code;
380 p_last_update_date := l_last_update_date;
381 p_last_Updated_By := l_last_updated_by;
382 p_last_Update_Login := l_last_update_login;
383 p_creation_date := l_creation_date;
384 p_created_by := l_created_by;
385
386 END Get_Invoice_Information;
387
388
389 Procedure get_tax_count(invoiceid IN Number,
390 tot_rec IN OUT NOCOPY Number,
391 row_number IN Number,
392 errcd IN OUT NOCOPY Number) Is
393 Begin
394 errcd := 0;
395 SELECT count(*)
396 Into tot_rec
397 FROM ap_invoice_distributions
398 WHERE invoice_id = invoiceid
399 AND line_type_lookup_code IN ('ICMS','IPI');
400 Exception
401 When Others Then
402 errcd := sqlcode;
403 End get_tax_count;
404
405 Procedure get_ship_from(vendsite_id IN Number,
406 shp_frm IN OUT NOCOPY Varchar2,
407 row_number IN Number,
408 errcd IN OUT NOCOPY Number) Is
409 Begin
410 errcd := 0;
411 select state ship_from
412 into shp_frm
413 from po_vendor_sites
414 where vendor_site_id = vendsite_id
415 and nvl(inactive_date,sysdate + 1) > sysdate and rownum = row_number;
416 Exception
417 When Others Then
418 errcd := sqlcode;
419 End get_ship_from;
420
421 Procedure get_ship_to(locn_id IN Number,
422 shp_to IN OUT NOCOPY Varchar2,
423 row_number IN Number,
424 errcd IN OUT NOCOPY Number) Is
425 Begin
426 errcd := 0;
427 select substr(region_2,1,30)
428 into shp_to
429 from hr_locations_all
430 where location_id = locn_id and rownum = row_number;
431 Exception
432 When Others Then
433 errcd := sqlcode;
434 End get_ship_to;
435
436 Procedure get_tax_rate_diff(cfocd IN Varchar2,
437 tax_flag IN OUT NOCOPY Varchar2,
438 row_number IN Number,
439 errcd IN OUT NOCOPY Number) Is
440 Begin
441 errcd := 0;
442 select tax_rate_differential
443 into tax_flag
444 from jl_br_ap_operations
445 where cfo_code = cfocd and rownum = row_number;
446 Exception
447 When Others Then
448 errcd := sqlcode;
449 End get_tax_rate_diff;
450
451 Procedure get_trans_reason_code(polineid IN Number,
452 trans_reason_code IN OUT NOCOPY Varchar2,
453 fiscal_class_code IN OUT NOCOPY Varchar2,
454 row_number IN Number,
455 errcd IN OUT NOCOPY Number) Is
456 Begin
457 errcd := 0;
458 SELECT transaction_reason_code, substr(global_attribute1,1,15)
459 INTO trans_reason_code, fiscal_class_code
460 FROM po_lines
461 WHERE po_line_id = polineid and rownum = row_number;
462 Exception
463 When Others Then
464 errcd := sqlcode;
465 End get_trans_reason_code;
466
467 Procedure get_ship_to_org_id(inv_org_id IN OUT NOCOPY Number,
468 p_po_line_id IN Number,
469 errcd IN OUT NOCOPY Number) Is
470 Begin
471 errcd := 0;
472 SELECT ship_to_organization_id
473 INTO inv_org_id
474 FROM po_line_locations
475 Where po_line_id = p_po_line_id ;
476 Exception
477 When Others Then
478 errcd := sqlcode;
479 End get_ship_to_org_id;
480
481 Procedure get_inv_org_id(inv_org_id IN OUT NOCOPY Number,
482 row_number IN Number,
483 errcd IN OUT NOCOPY Number) Is
484 Begin
485 errcd := 0;
486 SELECT inventory_organization_id
487 INTO inv_org_id
488 FROM financials_system_parameters Where rownum = row_number;
489 Exception
490 When Others Then
491 errcd := sqlcode;
492 End get_inv_org_id;
493
494 Procedure get_Association_Method(asson_method IN OUT NOCOPY Varchar2,
495 row_number IN Number,
496 errcd IN OUT NOCOPY Number) Is
497 Begin
498 errcd := 0;
499 select substr(Global_Attribute3,1,25)
500 into asson_method
501 from ap_System_Parameters Where rownum = row_number;
502 Exception
503 When Others Then
504 errcd := sqlcode;
505 End get_Association_Method;
506
507 Procedure get_tcc_id(tax_reco_flg IN Varchar2,
508 vatcode IN Varchar2,
509 tcc_id IN OUT NOCOPY Number,
510 description IN OUT NOCOPY Varchar2,
511 account_type IN OUT NOCOPY Varchar2,
512 row_number IN Number,
513 errcd IN OUT NOCOPY Number) Is
514 tcc_id1 Number;
515 tcc_id2 Number;
516 Begin
517 errcd := 0;
518 select ATC.tax_code_combination_id,
519 to_number(substr(ATC.global_attribute1,1,15)),
520 ATC.description,
521 decode(GL.account_type,'A','Y','N')
522 into tcc_id1, tcc_id2, description, account_type
523 from ap_tax_codes ATC, gl_code_combinations GL
524 where ATC.name = vatcode
525 and ATC.tax_code_combination_id = GL.code_combination_id
526 and nvl(ATC.inactive_date,sysdate + 1) > sysdate and rownum = row_number;
527 If Nvl(Tax_Reco_flg,'N') = 'Y' Then
528 tcc_id := tcc_id1;
529 Else
530 tcc_id := tcc_id2;
531 End If;
532 Exception
533 When Others Then
534 errcd := sqlcode;
535 End get_tcc_id;
536
537 Procedure get_dist_count(invoiceid IN Number,
538 tot_rec IN OUT NOCOPY Number,
539 row_number IN Number,
540 errcd IN OUT NOCOPY Number) Is
541 Begin
542 errcd := 0;
543 SELECT count(*)
544 Into tot_rec
545 FROM ap_invoice_distributions
546 WHERE invoice_id = invoiceid
547 AND line_type_lookup_code = 'ITEM';
548 Exception
549 When Others Then
550 errcd := sqlcode;
551 End get_dist_count;
552
553 Procedure get_Tax_Recoverable(cfocd IN Varchar2,
554 tax_recoverable IN OUT NOCOPY Varchar2,
555 row_number IN Number,
556 errcd IN OUT NOCOPY Number) Is
557 Begin
558 errcd := 0;
559 select nvl(tax_recoverable,'N')
560 into tax_recoverable
561 from jl_br_ap_operations
562 where cfo_code = cfocd and rownum = row_number;
563 Exception
564 When Others Then
565 errcd := sqlcode;
566 End get_Tax_Recoverable;
567
568 Procedure get_payment_schedule_count(invoiceid IN Number,
569 tot_rec IN OUT NOCOPY Number,
570 row_number IN Number,
571 errcd IN OUT NOCOPY Number) Is
572 Begin
573 errcd := 0;
574 select count(*)
575 into tot_rec
576 from ap_payment_schedules
577 where invoice_id = invoiceid;
578 Exception
579 When Others Then
580 errcd := sqlcode;
581 End get_payment_schedule_count;
582
583 Procedure get_distribution_count(invoiceid IN Number,
584 tot_rec IN OUT NOCOPY Number,
585 row_number IN Number,
586 errcd IN OUT NOCOPY Number) Is
587 Begin
588 errcd := 0;
589 select count(*)
590 into tot_rec
591 from ap_invoice_distributions
592 where invoice_id = invoiceid
593 and awt_invoice_id is not null;
594 Exception
595 When Others Then
596 errcd := sqlcode;
597 End get_distribution_count;
598
599 Procedure get_vendor_site_id(invoiceid IN Number,
600 vendsite_id IN OUT NOCOPY Number,
601 row_number IN Number,
602 errcd IN OUT NOCOPY Number) Is
603 Begin
604 errcd := 0;
605 select vendor_site_id
606 into vendsite_id
607 from ap_invoices
608 where invoice_id = invoiceid and rownum = row_number;
609 Exception
610 When Others Then
611 errcd := sqlcode;
612 End get_vendor_site_id;
613
614 Procedure get_tax_calendar_name(vendsite_id IN Number,
615 tax_cal_name IN OUT NOCOPY Varchar2,
616 row_number IN Number,
617 errcd IN OUT NOCOPY Number) Is
618 Begin
619 errcd := 0;
620 select substr(global_attribute16,1,25)
621 into tax_cal_name
622 from po_vendor_sites
623 where vendor_site_id = vendsite_id
624 and nvl(inactive_date,sysdate + 1) > sysdate and rownum = row_number;
625 Exception
626 When Others Then
627 errcd := sqlcode;
628 End get_tax_calendar_name;
629
630 Procedure get_tax_type(tax_code_id IN Number,
631 tax_type IN OUT NOCOPY Varchar2,
632 row_number IN Number,
633 errcd IN OUT NOCOPY Number) Is
634 Begin
635 errcd := 0;
636 select substr(global_attribute2,1,25)
637 into tax_type
638 from ap_tax_codes
639 where tax_id = tax_code_id
640 and nvl(inactive_date,sysdate + 1) > sysdate and rownum = row_number;
641 Exception
642 When Others Then
643 errcd := sqlcode;
644 End get_tax_type;
645
646 Procedure get_base_date(taxcal_name IN Varchar2,
647 taxtype IN Varchar2,
648 basedt IN OUT NOCOPY Varchar2,
649 row_number IN Number,
650 errcd IN OUT NOCOPY Number) Is
651 Begin
652 errcd := 0;
653 select base_date
654 Into basedt
655 from jl_br_ap_tax_calendar_headers
656 where tax_calendar_name = taxcal_name
657 and tax_type = taxtype and rownum = row_number;
658 Exception
659 When Others Then
660 errcd := sqlcode;
661 End get_base_date;
662
663 Procedure get_terms_due_date(invoiceid IN Number,
664 taxcal_name IN Varchar2,
665 duedt IN OUT NOCOPY Date,
666 row_number IN Number,
667 errcd IN OUT NOCOPY Number) Is
668 Begin
669 errcd := 0;
670 select j.due_date
671 into duedt
672 from jl_br_ap_tax_calendar_lines j, ap_invoices a
673 where a.invoice_id = invoiceid
674 and j.tax_calendar_name = taxcal_name
675 and a.terms_date between j.start_date and j.end_date
676 and sysdate < nvl(j.inactive_date,sysdate+1) and rownum = row_number;
677 Exception
678 When Others Then
679 errcd := sqlcode;
680 End get_terms_due_date;
681
682 Procedure get_inv_due_date(invoiceid IN Number,
683 taxcal_name IN Varchar2,
684 duedt IN OUT NOCOPY Date,
685 row_number IN Number,
686 errcd IN OUT NOCOPY Number) Is
687 Begin
688 errcd := 0;
689 select j.due_date
690 into duedt
691 from jl_br_ap_tax_calendar_lines j, ap_invoices a
692 where a.invoice_id = invoiceid
693 and j.tax_calendar_name = taxcal_name
694 and a.invoice_date between j.start_date and j.end_date
695 and sysdate < nvl(j.inactive_date,sysdate+1) and rownum = row_number;
696 Exception
697 When Others Then
698 errcd := sqlcode;
699 End get_inv_due_date;
700
701 Procedure get_gl_due_date(invoiceid IN Number,
702 taxcal_name IN Varchar2,
703 duedt IN OUT NOCOPY Date,
704 row_number IN Number,
705 errcd IN OUT NOCOPY Number) Is
706 Begin
707 errcd := 0;
708 select j.due_date
709 into duedt
710 from jl_br_ap_tax_calendar_lines j, ap_invoice_distributions a
711 where a.invoice_id = invoiceid
712 and j.tax_calendar_name = taxcal_name
713 and a.accounting_date between j.start_date and j.end_date
714 and sysdate < nvl(j.inactive_date,sysdate+1) and rownum = row_number;
715 Exception
716 When Others Then
717 errcd := sqlcode;
718 End get_gl_due_date;
719
720 Procedure get_city_frm_sys(vcity IN OUT NOCOPY Varchar2,
721 row_number IN Number,
722 errcd IN OUT NOCOPY Number) Is
723
724 l_return_status VARCHAR2(100);
725 l_msg_data VARCHAR2(1000);
726 --l_ledger_info xle_businessinfo_grp.le_ledger_rec_type;
727 l_ledger_id NUMBER;
728 l_BSV VARCHAR2(30);
729
730 Cursor CityT Is
731 Select etb.town_or_city
732 From
733 xle_establishment_v etb
734 ,xle_bsv_associations bsv
735 ,gl_ledger_le_v gl
736 Where
737 etb.legal_entity_id = gl.legal_entity_id
738 And bsv.legal_parent_id = etb.legal_entity_id
739 And etb.establishment_id = bsv.legal_construct_id
740 And bsv.entity_name = l_BSV
741 And gl.ledger_id = l_ledger_id;
742
743 Begin
744
745 select set_of_books_id,substr(global_attribute4,1,25)
746 into l_ledger_id,l_BSV
747 from ap_system_parameters;
748
749 /* bug 5206517
750 XLE_BUSINESSINFO_GRP.Get_Ledger_Info
751 (x_return_status => l_return_status, --OUT VARCHAR2,
752 x_msg_data => l_msg_data , --OUT VARCHAR2,
753 P_Ledger_ID => l_ledger_id , --IN NUMBER,
754 P_BSV => l_BSV , --IN VARCHAR2,
755 x_Ledger_info => l_ledger_info); --OUT LE_Ledger_Rec_Type
756 */
757
758 For City IN CityT Loop
759 vcity := City.TOWN_OR_CITY;
760 END LOOP;
761 End get_city_frm_sys;
762
763 Procedure get_city_frm_povend(vendsite_id IN Number,
764 vcity IN OUT NOCOPY Varchar2,
765 row_number IN Number,
766 errcd IN OUT NOCOPY Number) Is
767 Begin
768 errcd := 0;
769 select city
770 into vcity
771 from po_vendor_sites
772 where vendor_site_id = vendsite_id
773 and nvl(inactive_date,sysdate + 1) > sysdate and rownum = row_number;
774 Exception
775 When Others Then
776 errcd := sqlcode;
777 End get_city_frm_povend;
778
779 PROCEDURE get_count_cnab(cnab IN VARCHAR2,
780 curr_code IN VARCHAR2,
781 total_rec IN OUT NOCOPY NUMBER,
782 row_number IN NUMBER,
783 Errcd IN OUT NOCOPY NUMBER) IS
784 BEGIN
785 Errcd := 0;
786 SELECT COUNT (*)
787 INTO total_rec
788 FROM fnd_currencies_vl c
789 WHERE c.global_attribute1 = cnab
790 AND c.currency_code <> curr_code;
791 EXCEPTION
792 WHEN OTHERS THEN
793 Errcd := SQLCODE;
794 END get_count_cnab;
795
796 PROCEDURE get_old_cnab_code(curr_code IN VARCHAR2,
797 cnab IN OUT NOCOPY VARCHAR2,
798 row_number IN NUMBER,
799 Errcd IN OUT NOCOPY NUMBER) IS
800 BEGIN
801 Errcd := 0;
802 SELECT SUBSTR (c.global_attribute1, 1, 15)
803 INTO cnab
804 FROM fnd_currencies_vl c
805 WHERE c.currency_code = curr_code
806 AND rownum = row_number;
807 EXCEPTION
808 WHEN OTHERS THEN
809 Errcd := SQLCODE;
810 END get_old_cnab_code;
811
812 PROCEDURE get_tax_code_id(p_tax_name IN Varchar2,
813 p_val_date IN Date,
814 p_tax_id IN OUT NOCOPY Number) Is
815 Begin
816
817 select tax_id
818 into p_tax_id
819 from AP_TAX_CODES
820 where name = p_tax_name
821 and nvl(start_date,p_val_date) <= p_val_date
822 and nvl(inactive_date,p_val_date+1) > p_val_date
823 and nvl(enabled_flag,'Y') = 'Y';
824
825 Exception
826 When Others Then
827 null;
828 End get_tax_code_id;
829
830 Procedure get_city_frm_sys(vcity IN OUT NOCOPY Varchar2,
831 row_number IN Number,
832 errcd IN OUT NOCOPY Number,
833 vstate IN OUT NOCOPY Varchar2) Is --Bug # 2319552
834
835 l_return_status VARCHAR2(100);
836 l_msg_data VARCHAR2(1000);
837 --l_ledger_info xle_businessinfo_grp.le_ledger_rec_type;
838 l_ledger_id NUMBER;
839 l_BSV VARCHAR2(30);
840
841 Cursor CityTReg Is
842 Select etb.town_or_city, etb.region_2
843 From
844 xle_establishment_v etb
845 ,xle_bsv_associations bsv
846 ,gl_ledger_le_v gl
847 Where
848 etb.legal_entity_id = gl.legal_entity_id
849 And bsv.legal_parent_id = etb.legal_entity_id
850 And etb.establishment_id = bsv.legal_construct_id
851 And bsv.entity_name = l_BSV
852 And gl.ledger_id = l_ledger_id;
853
854 BEGIN
855
856 select set_of_books_id,substr(global_attribute4,1,25)
857 into l_ledger_id,l_BSV
858 from ap_system_parameters;
859
860 /* Bug# 5206517
861 XLE_BUSINESSINFO_GRP.Get_Ledger_Info
862 (x_return_status => l_return_status, --OUT VARCHAR2,
863 x_msg_data => l_msg_data , --OUT VARCHAR2,
864 P_Ledger_ID => l_ledger_id , --IN NUMBER,
865 P_BSV => l_BSV , --IN VARCHAR2,
866 x_Ledger_info => l_ledger_info); --OUT LE_Ledger_Rec_Type
867 */
868
869 For CityReg2 IN CityTReg Loop
870 vcity := CityReg2.town_or_city;
871 vstate := CityReg2.region_2;
872 End Loop;
873 End get_city_frm_sys;
874
875 Procedure get_city_frm_povend(vendsite_id IN Number,
876 vcity IN OUT NOCOPY Varchar2,
877 row_number IN Number,
878 errcd IN OUT NOCOPY Number,
879 vstate IN OUT NOCOPY Varchar2) Is --Bug # 2319552
880
881 Begin
882 errcd := 0;
883 select city,
884 state --Bug # 2319552
885 into vcity,
886 vstate --Bug # 2319552
887 from po_vendor_sites
888 where vendor_site_id = vendsite_id
889 and nvl(inactive_date,sysdate + 1) > sysdate and rownum = row_number;
890 Exception
891 When Others Then
892 errcd := sqlcode;
893 End get_city_frm_povend;
894 -- Bug 3609925
895 Procedure get_vendor_id(invoiceid IN Number,
896 vendor_id IN OUT NOCOPY Number,
897 row_number IN Number,
898 errcd IN OUT NOCOPY Number) Is
899 Begin
900 errcd := 0;
901 select vendor_id
902 into vendor_id
903 from ap_invoices
904 where invoice_id = invoiceid and rownum = row_number;
905 Exception
906 When Others Then
907 errcd := sqlcode;
908 End get_vendor_id;
909
910 --Bug 3740729
911 PROCEDURE upd_inwkb_br_def_ps_segmts_1
912 (v_inttyp IN VARCHAR2,
913 v_intamt IN VARCHAR2,
914 v_intprd IN VARCHAR2,
915 v_intfml IN VARCHAR2,
916 v_intgrd IN VARCHAR2,
917 v_pnttyp IN VARCHAR2,
918 v_pntamt IN VARCHAR2,
919 v_glbattctg IN VARCHAR2,
920 v_invid IN NUMBER,
921 errcd IN OUT NOCOPY NUMBER) IS
922
923 l_exists VARCHAR2(6);
924
925 BEGIN
926 errcd := 0;
927
928 UPDATE AP_PAYMENT_SCHEDULES
929 SET GLOBAL_ATTRIBUTE1 = v_inttyp,
930 GLOBAL_ATTRIBUTE2 = v_intamt,
931 GLOBAL_ATTRIBUTE3 = v_intprd,
932 GLOBAL_ATTRIBUTE4 = v_intfml,
933 GLOBAL_ATTRIBUTE5 = v_intgrd,
934 GLOBAL_ATTRIBUTE6 = v_pnttyp,
935 GLOBAL_ATTRIBUTE7 = v_pntamt,
936 GLOBAL_ATTRIBUTE8 = 'N',
937 GLOBAL_ATTRIBUTE_CATEGORY = v_glbattctg
938 WHERE INVOICE_ID = v_invid;
939
940 EXCEPTION
941 WHEN OTHERS THEN
942 errcd := sqlcode;
943
944 END upd_inwkb_br_def_ps_segmts_1;
945
946
947 PROCEDURE upd_inwkb_br_def_ps_segmts_2
948 (v_inttyp IN VARCHAR2,
949 v_intamt IN VARCHAR2,
950 v_intprd IN VARCHAR2,
951 v_intfml IN VARCHAR2,
952 v_intgrd IN VARCHAR2,
953 v_pnttyp IN VARCHAR2,
954 v_pntamt IN VARCHAR2,
955 v_glbattctg IN VARCHAR2,
956 v_invid IN NUMBER,
957 v_pmtno IN NUMBER,
958 errcd IN OUT NOCOPY NUMBER) IS
959
960 l_exists VARCHAR2(6);
961
962 BEGIN
963 errcd := 0;
964
965 UPDATE AP_PAYMENT_SCHEDULES
966 SET GLOBAL_ATTRIBUTE1 = v_inttyp,
967 GLOBAL_ATTRIBUTE2 = v_intamt,
968 GLOBAL_ATTRIBUTE3 = v_intprd,
969 GLOBAL_ATTRIBUTE4 = v_intfml,
970 GLOBAL_ATTRIBUTE5 = v_intgrd,
971 GLOBAL_ATTRIBUTE6 = v_pnttyp,
972 GLOBAL_ATTRIBUTE7 = v_pntamt,
973 GLOBAL_ATTRIBUTE8 = 'N',
974 GLOBAL_ATTRIBUTE_CATEGORY = v_glbattctg
975 WHERE INVOICE_ID = v_invid
976 AND PAYMENT_NUM = v_pmtno;
977
978 EXCEPTION
979 WHEN OTHERS THEN
980 errcd := sqlcode;
981
982 END upd_inwkb_br_def_ps_segmts_2;
983
984
985 PROCEDURE upd_inwkb_br_upd_due_date1
986 (l_due_date_char IN VARCHAR2,
987 l_invoice_id IN NUMBER,
988 l_payment_num IN NUMBER,
989 errcd IN OUT NOCOPY NUMBER) IS
990
991 l_exists VARCHAR2(6);
992
993 BEGIN
994 errcd := 0;
995
996 UPDATE AP_PAYMENT_SCHEDULES
997 set due_date = to_date (l_due_date_char,'YYYY/MM/DD HH24:MI:SS')
998 where invoice_id = l_invoice_id
999 and payment_num = l_payment_num;
1000
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 errcd := sqlcode;
1004
1005 END upd_inwkb_br_upd_due_date1;
1006
1007 PROCEDURE upd_inwkb_br_upd_due_date2
1008 (l_discount_date_char IN VARCHAR2,
1009 l_invoice_id IN NUMBER,
1010 l_payment_num IN NUMBER,
1011 errcd IN OUT NOCOPY NUMBER) IS
1012
1013 l_exists VARCHAR2(6);
1014
1015 BEGIN
1016 errcd := 0;
1017
1018 UPDATE AP_PAYMENT_SCHEDULES
1019 SET discount_date = to_date (l_discount_date_char,'YYYY/MM/DD HH24:MI:SS')
1020 WHERE invoice_id = l_invoice_id
1021 AND payment_num = l_payment_num;
1022
1023 EXCEPTION
1024 WHEN OTHERS THEN
1025 errcd := sqlcode;
1026
1027 END upd_inwkb_br_upd_due_date2;
1028
1029 PROCEDURE upd_inwkb_br_up_wh_due_date1
1030 (l_due_date_char IN VARCHAR2,
1031 l_new_invoice_id IN NUMBER,
1032 errcd IN OUT NOCOPY NUMBER) IS
1033
1034 l_exists VARCHAR2(6);
1035
1036 BEGIN
1037 errcd := 0;
1038
1039 UPDATE AP_PAYMENT_SCHEDULES
1040 SET due_date = to_date (l_due_date_char,'YYYY/MM/DD HH24:MI:SS')
1041 where invoice_id = l_new_invoice_id;
1042
1043 EXCEPTION
1044 WHEN OTHERS THEN
1045 errcd := sqlcode;
1046
1047 END upd_inwkb_br_up_wh_due_date1;
1048
1049 PROCEDURE upd_inwkb_br_val_pay_sched1
1050 (l_new_date1 IN DATE,
1051 s_invoice_id IN NUMBER,
1052 s_payment_num IN NUMBER,
1053 errcd IN OUT NOCOPY NUMBER) IS
1054
1055 l_exists VARCHAR2(6);
1056
1057 BEGIN
1058 errcd := 0;
1059
1060 UPDATE AP_PAYMENT_SCHEDULES
1061 SET due_date = l_new_date1
1062 WHERE invoice_id = s_invoice_id
1063 AND payment_num = s_payment_num;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 errcd := sqlcode;
1068
1069 END upd_inwkb_br_val_pay_sched1;
1070
1071 PROCEDURE upd_inwkb_br_val_pay_sched2
1072 (l_new_date1 IN DATE,
1073 s_invoice_id IN NUMBER,
1074 s_payment_num IN NUMBER,
1075 errcd IN OUT NOCOPY NUMBER) IS
1076
1077 l_exists VARCHAR2(6);
1078
1079 BEGIN
1080 errcd := 0;
1081
1082 UPDATE AP_PAYMENT_SCHEDULES
1083 SET discount_date = l_new_date1
1084 WHERE invoice_id = s_invoice_id
1085 AND payment_num = s_payment_num;
1086
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 errcd := sqlcode;
1090
1091 END upd_inwkb_br_val_pay_sched2;
1092
1093 PROCEDURE upd_inwkb_br_val_pay_sched3
1094 (s_bank_collection_id IN jl_br_ap_collection_docs.bank_collection_id%Type,
1095 s_invoice_id IN NUMBER,
1096 s_payment_num IN NUMBER,
1097 errcd IN OUT NOCOPY NUMBER) IS
1098
1099 l_exists VARCHAR2(6);
1100
1101 BEGIN
1102 errcd := 0;
1103
1104 UPDATE AP_PAYMENT_SCHEDULES
1105 set global_attribute11 = s_bank_collection_id
1106 where invoice_id = s_invoice_id
1107 and payment_num = s_payment_num;
1108
1109 EXCEPTION
1110 WHEN OTHERS THEN
1111 errcd := sqlcode;
1112
1113 END upd_inwkb_br_val_pay_sched3;
1114
1115 END JL_ZZ_AP_LIBRARY_1_PKG;
[Home] [Help]