1 PACKAGE BODY PO_VENDORS_AP_PKG AS
2 /* $Header: povendrb.pls 120.2.12010000.2 2009/10/12 08:35:08 sjetti ship $ */
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 -- Bug 8674710 - Added the join to HZ_PARTY_SITES as site should be
17 -- inactive when the Address is inactive.
18
19 SELECT count(pvs.vendor_site_id)
20 INTO num_active
21 FROM ap_supplier_sites pvs,
22 hz_party_sites H,
23 ap_suppliers pv
24 WHERE pvs.vendor_id = X_vendor_id
25 AND (( X_ORG_ID IS NOT NULL AND
26 pvs.org_id = X_ORG_ID)
27 OR X_ORG_ID IS NULL)
28 AND pvs.pay_site_flag = 'Y'
29 AND nvl(pvs.inactive_date, sysdate+1) > sysdate
30 AND H.party_site_id (+) = pvs.party_site_id
31 AND PV.vendor_id = pvs.vendor_id
32 AND DECODE(PV.vendor_type_lookup_code,'EMPLOYEE', 'A',NVL(H.status, 'I')) = ('A')
33 GROUP BY pvs.vendor_id;
34
35 RETURN(num_active);
36
37 EXCEPTION
38 WHEN NO_DATA_FOUND THEN RETURN(0);
39 END get_num_active_pay_sites;
40
41
42 -----------------------------------------------------------------------
43 -- Function get_num_inactive_pay_sites returns the number of active
44 -- pay sites for a particular vendor
45 --
46 FUNCTION get_num_inactive_pay_sites(X_vendor_id IN NUMBER,
47 X_ORG_ID IN NUMBER )
48 RETURN NUMBER
49 IS
50 num_inactive NUMBER;
51
52 BEGIN
53
54
55 SELECT count(*)
56 INTO num_inactive
57 FROM ap_supplier_sites pvs
58 WHERE pvs.vendor_id = X_vendor_id
59 AND (( X_ORG_ID IS NOT NULL AND
60 pvs.org_id = X_ORG_ID)
61 OR X_ORG_ID IS NULL)
62 AND pvs.pay_site_flag = 'Y'
63 AND nvl(pvs.inactive_date, sysdate+1) < sysdate
64 GROUP BY pvs.vendor_id;
65
66 RETURN(num_inactive);
67
68 EXCEPTION
69 WHEN NO_DATA_FOUND THEN RETURN(0);
70 END get_num_inactive_pay_sites;
71
72
73 END PO_VENDORS_AP_PKG;
[Home] [Help]