1 PACKAGE BODY AP_PO_GAPLESS_SBI_PKG AS
2 /* $Header: apposbib.pls 120.1 2006/05/04 19:27:25 bghose noship $ */
3
4 function this_is_dup_inv_num(
5 p_invoice_num IN VARCHAR2,
6 p_selling_co_id IN VARCHAR2)
7
8 RETURN BOOLEAN IS
9
10 l_vendor_id AP_INVOICES_ALL.vendor_id%TYPE;
11 l_org_id number(15);
12 l_dup_invoices AP_INVOICES_ALL.invoice_num%TYPE;
13 l_dup_interface AP_INVOICES_INTERFACE.invoice_num%TYPE;
14 l_dup_history AP_HISTORY_INVOICES_ALL.invoice_num%TYPE;
15
16 BEGIN
17
18 -- Deriving the Value of the Vendor_Id
19
20 SELECT vendor_id,
21 org_id
22 INTO l_vendor_id,
23 l_org_id
24 FROM PO_VENDOR_SITES
25 WHERE selling_company_identifier = p_selling_co_id
26 AND rownum = 1;
27
28 -- Checking for duplicates in AP_INVOICES_ALL table
29
30 Begin
31 SELECT invoice_num
32 INTO l_dup_invoices
33 FROM AP_INVOICES_ALL
34 WHERE invoice_num = p_invoice_num
35 AND vendor_id = l_vendor_id
36 AND org_id = l_org_id;
37 Exception
38 When NO_DATA_FOUND Then
39 l_dup_invoices := Null;
40 End;
41
42 -- Checking for duplicates in AP_INVOICES_INTERFACE table
43
44 IF l_dup_invoices is Null THEN
45 Begin
46
47 SELECT invoice_num
48 INTO l_dup_interface
49 FROM AP_INVOICES_INTERFACE
50 WHERE invoice_num = p_invoice_num
51 AND vendor_id = l_vendor_id
52 AND status <> 'PROCESSED'
53 AND rownum = 1;
54 Exception
55 When NO_DATA_FOUND Then
56 l_dup_interface := NULL;
57 End;
58
59 END IF;
60
61 -- Checking for duplicates in AP_HISTORY_INVOICES_ALL table
62
63 IF (l_dup_interface is Null AND l_dup_invoices is Null) THEN
64 Begin
65
66 SELECT invoice_num
67 INTO l_dup_history
68 FROM AP_HISTORY_INVOICES_ALL
69 WHERE invoice_num = p_invoice_num
70 AND vendor_id = l_vendor_id
71 AND org_id = l_org_id;
72 Exception
73 When NO_DATA_FOUND Then
74 l_dup_history := Null;
75 END;
76
77 END IF;
78
79 -- Setting the values of the out variables.
80
81 IF ((l_dup_invoices IS NOT NULL) OR
82 (l_dup_interface IS NOT NULL) OR
83 (l_dup_history IS NOT NULL)) THEN
84 RETURN(TRUE);
85 ELSE
86 RETURN(FALSE);
87 END IF;
88
89 EXCEPTION
90 -- Trap unknown error
91 WHEN OTHERS THEN
92 RETURN(FALSE);
93 END this_is_dup_inv_num;
94
95
96 PROCEDURE site_uses_gapless_num(
97 p_site_id IN NUMBER,
98 x_gapless_inv_num_flag OUT NOCOPY VARCHAR2,
99 x_selling_company_id OUT NOCOPY VARCHAR2
100 )
101
102 IS
103
104 l_gapless_inv_num VARCHAR2(1);
105 l_selling_co_id VARCHAR2(10);
106 l_alt_site_id NUMBER; --Bug 3628373
107 l_source_site_id NUMBER; --Bug 3628373
108
109 BEGIN
110
111 Select default_pay_site_id
112 Into l_alt_site_id
113 From PO_VENDOR_SITES_ALL
114 Where vendor_site_id = p_site_id; --Bug 3628373
115
116 If l_alt_site_id is Not Null THEN
117 l_source_site_id := l_alt_site_id;
118 Else l_source_site_id := p_site_id;
119 End If; --Bug 3628373
120
121 SELECT NVL(gapless_inv_num_flag, 'N'), /* Bug 5197828 */
122 selling_company_identifier
123 INTO x_gapless_inv_num_flag,
124 x_selling_company_id
125 FROM PO_VENDOR_SITES_ALL
126 WHERE vendor_site_id = l_source_site_id;
127
128 /* Bug 5197828 */
129 --If l_gapless_inv_num is Not Null Then
130 -- x_gapless_inv_num_flag := 'Y';
131 --Else x_gapless_inv_num_flag := 'N';
132 --End If;
133 --x_selling_company_id := l_selling_co_id;
134
135 EXCEPTION
136 -- Trap unknown error
137 WHEN OTHERS THEN
138 x_gapless_inv_num_flag := 'N';
139 x_selling_company_id := Null;
140
141 END site_uses_gapless_num;
142
143 END AP_PO_GAPLESS_SBI_PKG;
144
[Home] [Help]