1 PACKAGE BODY AP_TE_EIS_PKG AS
2 /* $Header: apteeisb.pls 120.2 2004/10/25 22:39:11 pjena noship $ */
3
4 DEBUG CONSTANT BOOLEAN := FALSE;
5
6
7 PROCEDURE GetInvoiceTotal(
8 P_Start_Date IN Date,
9 P_End_Date IN Date,
10 P_Result IN OUT NOCOPY NUMBER) IS
11 BEGIN
12
13 select nvl(SUM(d.amount),0)
14 into P_Result
15 from AP_INVOICES i,
16 AP_INVOICE_DISTRIBUTIONS d
17 where i.invoice_id = d.invoice_id
18 and i.invoice_type_lookup_code = 'EXPENSE REPORT'
19 and d.accounting_date >= P_Start_Date
20 and d.accounting_date <= P_End_Date;
21 EXCEPTION
22 when NO_DATA_FOUND then
23 BEGIN
24 P_Result := 0;
25 END;
26 END GetInvoiceTotal;
27
28 PROCEDURE GetInvoiceCount(
29 P_Payables IN NUMBER,
30 P_Project IN NUMBER,
31 P_Selfserve IN NUMBER,
32 P_Result IN OUT NOCOPY NUMBER) IS
33 BEGIN
34
35 P_Result := P_Payables + P_Project + P_Selfserve;
36
37 END GetInvoiceCount;
38
39
40 PROCEDURE GetInvoiceAverage(
41 P_Total IN NUMBER,
42 P_Count IN NUMBER,
43 P_Result IN OUT NOCOPY NUMBER) IS
44
45 BEGIN
46 IF (P_Count = 0) THEN
47 P_Result := 0;
48 ELSE
49 P_Result := P_Total / P_Count;
50 END IF;
51
52 END GetInvoiceAverage;
53
54
55 PROCEDURE GetPaymentTotal(
56 P_Set_Of_Books_Id IN VARCHAR2 ,
57 P_Start_Date IN Date,
58 P_End_Date IN Date,
59 P_Result IN OUT NOCOPY NUMBER) IS
60
61 BEGIN
62 select SUM(P.amount)
63 into P_Result
64 from AP_INVOICE_PAYMENTS p,
65 AP_INVOICES i
66 where p.invoice_id = i.invoice_id
67 and p.set_of_books_id = P_Set_Of_Books_ID
68 and i.invoice_type_lookup_code = 'EXPENSE REPORT'
69 and p.accounting_date >= P_Start_Date
70 and p.accounting_date <= P_End_Date;
71 EXCEPTION
72 when OTHERS then
73 BEGIN
74 P_Result := 0;
75 END;
76
77 END GetPaymentTotal;
78
79
80 PROCEDURE GetPaymentCount(
81 P_Set_Of_Books_Id IN VARCHAR2 ,
82 P_Start_Date IN Date,
83 P_End_Date IN Date,
84 P_Result IN OUT NOCOPY NUMBER) IS
85
86 BEGIN
87 select COUNT(p.invoice_payment_id)
88 into P_Result
89 from AP_INVOICE_PAYMENTS p,
90 AP_INVOICES i
91 where p.invoice_id = i.invoice_id
92 and p.set_of_books_id = P_Set_Of_Books_ID
93 and i.invoice_type_lookup_code = 'EXPENSE REPORT'
94 and p.accounting_date >= P_Start_Date
95 and p.accounting_date <= P_End_Date;
96 EXCEPTION
97 when OTHERS then
98 BEGIN
99 P_Result := 0;
100 END;
101 END GetPaymentCount;
102
103 PROCEDURE GetPaymentAverage(
104 P_Set_Of_Books_Id IN VARCHAR2 ,
105 P_Start_Date IN Date,
106 P_End_Date IN Date,
107 P_Result IN OUT NOCOPY NUMBER) IS
108
109 BEGIN
110 select AVG(p.amount)
111 into P_Result
112 from AP_INVOICE_PAYMENTS p,
113 AP_INVOICES i
114 where p.invoice_id = i.invoice_id
115 and p.set_of_books_id = P_Set_Of_Books_ID
116 and i.invoice_type_lookup_code = 'EXPENSE REPORT'
117 and p.accounting_date >= P_Start_Date
118 and p.accounting_date <= P_End_Date;
119
120 EXCEPTION
121 when OTHERS then
122 BEGIN
123 P_Result := 0;
124 END;
125 END GetPaymentAverage;
126
127
128
129 PROCEDURE GetPayablesInvoiceCount(
130 P_Set_Of_Books_Id IN VARCHAR2 ,
131 P_Start_Date IN Date,
132 P_End_Date IN Date,
133 P_Result IN OUT NOCOPY NUMBER) IS
134 BEGIN
135
136 select COUNT(DISTINCT(I.invoice_id))
137 into P_Result
138 from AP_INVOICES I,
139 AP_INVOICE_DISTRIBUTIONS D
140 where I.invoice_id = D.invoice_id
141 and D.Set_Of_Books_Id = P_Set_Of_Books_Id
142 and I.invoice_type_lookup_code = 'EXPENSE REPORT'
143 and I.source in ('XpenseXpress', 'Manual Invoice Entry')
144 and D.accounting_date >= P_Start_Date
145 and D.accounting_date <= P_End_Date;
146
147 EXCEPTION
148 when OTHERS then
149 BEGIN
150 P_Result := 0;
151 END;
152
153 END GetPayablesInvoiceCount;
154
155 PROCEDURE GetProjectInvoiceCount(
156 P_Set_Of_Books_Id IN VARCHAR2 ,
157 P_Start_Date IN Date,
158 P_End_Date IN Date,
159 P_Result IN OUT NOCOPY NUMBER) IS
160 BEGIN
161
162 select COUNT(DISTINCT(I.invoice_id))
163 into P_Result
164 from AP_INVOICES I,
165 AP_INVOICE_DISTRIBUTIONS D
166 where I.invoice_id = D.invoice_id
167 and I.source = 'Oracle Project Accounting'
168 and D.set_of_books_id = P_Set_Of_Books_Id
169 and D.accounting_date >= P_Start_Date
170 and D.accounting_date <= P_End_Date;
171 EXCEPTION
172 when OTHERS then
173 BEGIN
174 P_Result := 0;
175 END;
176 END GetProjectInvoiceCount;
177
178 PROCEDURE GetSelfServeInvoiceCount(
179 P_Set_Of_Books_Id IN VARCHAR2 ,
180 P_Start_Date IN Date,
181 P_End_Date IN Date,
182 P_Result IN OUT NOCOPY NUMBER) IS
183
184 BEGIN
185
186 select COUNT(DISTINCT(I.invoice_id))
187 into P_Result
188 from AP_INVOICES I,
189 AP_INVOICE_DISTRIBUTIONS D
190 where I.invoice_id = D.invoice_id
191 and D.Set_Of_Books_Id = P_Set_Of_Books_Id
192 and I.source = 'SelfService'
193 and D.accounting_date >= P_Start_Date
194 and D.accounting_date <= P_End_Date;
195
196 EXCEPTION
197 when OTHERS then
198 BEGIN
199 P_Result := 0;
200 END;
201
202 END GetSelfServeInvoiceCount;
203
204
205 END AP_TE_EIS_PKG;
[Home] [Help]