1 PACKAGE BODY PO_VENDORS_AP_PKG AS
2 /* $Header: povendrb.pls 120.2 2005/10/11 01:50:31 bghose noship $ */
3
4 -----------------------------------------------------------------------
5 -- Function get_num_active_pay_sites returns the number of active
6 -- pay sites for a particular vendor
7 --
8 FUNCTION get_num_active_pay_sites(X_vendor_id IN NUMBER,
9 X_ORG_ID IN NUMBER )
10 RETURN NUMBER
11 IS
12 num_active NUMBER;
13
14 BEGIN
15
16
17 SELECT count(*)
18 INTO num_active
19 FROM ap_supplier_sites pvs
20 WHERE pvs.vendor_id = X_vendor_id
21 AND (( X_ORG_ID IS NOT NULL AND
22 pvs.org_id = X_ORG_ID)
23 OR X_ORG_ID IS NULL)
24 AND pvs.pay_site_flag = 'Y'
25 AND nvl(pvs.inactive_date, sysdate+1) > sysdate
26 GROUP BY pvs.vendor_id;
27
28 RETURN(num_active);
29
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN RETURN(0);
32 END get_num_active_pay_sites;
33
34
35 -----------------------------------------------------------------------
36 -- Function get_num_inactive_pay_sites returns the number of active
37 -- pay sites for a particular vendor
38 --
39 FUNCTION get_num_inactive_pay_sites(X_vendor_id IN NUMBER,
40 X_ORG_ID IN NUMBER )
41 RETURN NUMBER
42 IS
43 num_inactive NUMBER;
44
45 BEGIN
46
47
48 SELECT count(*)
49 INTO num_inactive
50 FROM ap_supplier_sites pvs
51 WHERE pvs.vendor_id = X_vendor_id
52 AND (( X_ORG_ID IS NOT NULL AND
53 pvs.org_id = X_ORG_ID)
54 OR X_ORG_ID IS NULL)
55 AND pvs.pay_site_flag = 'Y'
56 AND nvl(pvs.inactive_date, sysdate+1) < sysdate
57 GROUP BY pvs.vendor_id;
58
59 RETURN(num_inactive);
60
61 EXCEPTION
62 WHEN NO_DATA_FOUND THEN RETURN(0);
63 END get_num_inactive_pay_sites;
64
65
66 END PO_VENDORS_AP_PKG;