[Home] [Help]
PACKAGE: APPS.OKL_BILLING_UTIL_PVT
Source
1 PACKAGE OKL_BILLING_UTIL_PVT AUTHID CURRENT_USER AS
2 /* $Header: OKLRBULS.pls 120.7 2011/06/15 06:50:48 rgooty ship $ */
3 ----------------------------------------------------------------------------
4 -- GLOBAL VARIABLES
5 ----------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_BILLING_UTIL_PVT';
7 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
8
9 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
10 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
11 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
12 G_EXCEPTION_ERROR EXCEPTION;
13 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
14
15 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKL_UNEXPECTED_ERROR';
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLERRM';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(30) := 'OKL_SQLCODE';
18
19 G_EXC_NAME_OTHERS CONSTANT VARCHAR2(6) := 'OTHERS';
20 G_API_TYPE CONSTANT VARCHAR(4) := '_PVT';
21 G_UI_DATE_MASK VARCHAR2(15) := fnd_profile.value('ICX_DATE_FORMAT_MASK');
22 G_OKL_LLA_INVALID_DATE_FORMAT CONSTANT VARCHAR2(30) := 'OKL_LLA_INVALID_DATE_FORMAT';
23 G_NOT_UNIQUE CONSTANT VARCHAR2(30) := 'OKL_LLA_NOT_UNIQUE';
24 G_REQUIRED_VALUE CONSTANT VARCHAR2(30) := 'OKL_REQUIRED_VALUE';
25 G_LLA_RANGE_CHECK CONSTANT VARCHAR2(30) := 'OKL_LLA_RANGE_CHECK';
26 G_INVALID_VALUE CONSTANT VARCHAR2(30) := OKL_API.G_INVALID_VALUE;
27 G_COL_NAME_TOKEN CONSTANT VARCHAR2(30) := OKL_API.G_COL_NAME_TOKEN;
28
29 TYPE contract_invoice_rec IS RECORD (
30 khr_id NUMBER := Okl_Api.G_MISS_NUM,
31 AMOUNT NUMBER := Okl_Api.G_MISS_NUM
32 );
33
34 TYPE contract_invoice_tbl IS TABLE OF contract_invoice_rec INDEX BY BINARY_INTEGER;
35 ----------------------------------------------------------------------------
36 -- Data Structures
37 ----------------------------------------------------------------------------
38 ----------------------------------------------------------------------------
39 -- Global Exception
40 ----------------------------------------------------------------------------
41 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
42
43 ----------------------------------------------------------------------------
44 -- Procedures and Functions
45 ------------------------------------------------------------------------------
46
47 -- **** Authoring requirement APIs ****
48 -------------------------------------------------------------------------------
49 -- Start of comments
50 --
51 -- Procedure Name : LAST_INVOICE_DATE
52 -- Description : api to return last(max) invoice date for a contract
53 -- Business Rules :
54 -- Parameters :
55 -- p_contract_id - Contract ID
56 --
57 -- x_invoice_date - Last invoice date
58 --
59 -- Version : 1.0
60 -- End of comments
61 --select max(ractrx.trx_date) from RA_CUSTOMER_TRX_LINES ractrx
62 --where exists (
63 --select 'x' from RA_CUSTOMER_TRX_LINES_ALL ractrl
64 --where ractrx.customer_trx_id = ractrl.customer_trx_id
65 --and ractrl.interface_line_attribute6 = (select contract_number from
66 -- okc_k_headers_b where id = p_contract_id)
67 --)
68 -------------------------------------------------------------------------------
69 PROCEDURE LAST_INVOICE_DATE(
70 p_api_version IN NUMBER
71 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
72 ,x_return_status OUT NOCOPY VARCHAR2
73 ,x_msg_count OUT NOCOPY NUMBER
74 ,x_msg_data OUT NOCOPY VARCHAR2
75 ,p_contract_id IN NUMBER
76 ,x_invoice_date OUT NOCOPY DATE
77 );
78
79 -------------------------------------------------------------------------------
80 -- Start of comments
81 --
82 -- Procedure Name : INVOICE_AMOUNT_FOR_STREAM
83 -- Description : api to return all the the total invoice line amount for
84 -- stream type purpose = p_stream_purpose ('UNSCHEDULED_PRINCIPAL_PAYMENT') for
85 -- each contract.
86 -- Select khr_id, sum(line_amount) group by khr_id where OKL invoice
87 -- and stream type purpose matches with invice line context field.
88 -- Business Rules :
89 -- Parameters :
90 -- p_stream_purpose - Stream type purpose
91 --
92 -- x_contract_invoice_tbl - table containing contract_id
93 -- and invoice amount
94 --
95 -- Version : 1.0
96 -- End of comments
97 --select a.id, sum(amount_due_original) from okl_bpd_ar_inv_lines_v ractrl,
98 --okc_k_headers_b a
99 --where a.contract_number = ractrl.interface_line_attribute6
100 --and ractrl.interface_line_attribute13(this is stream_type_purpose) = ( -- Join with okl_strm_type_b to match with stream type purpose)
101 --group by id;
102 -------------------------------------------------------------------------------
103 PROCEDURE INVOICE_AMOUNT_FOR_STREAM(
104 p_api_version IN NUMBER
105 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
106 ,x_return_status OUT NOCOPY VARCHAR2
107 ,x_msg_count OUT NOCOPY NUMBER
108 ,x_msg_data OUT NOCOPY VARCHAR2
109 ,p_stream_purpose IN VARCHAR2
110 ,x_contract_invoice_tbl OUT NOCOPY contract_invoice_tbl
111 );
112
113
114 FUNCTION INVOICE_LINE_AMOUNT_ORIG(
115 p_customer_trx_id IN NUMBER,
116 p_customer_trx_line_id NUMBER) RETURN NUMBER;
117
118 FUNCTION INVOICE_LINE_AMOUNT_APPLIED(
119 p_customer_trx_id IN NUMBER,
120 p_customer_trx_line_id NUMBER) RETURN NUMBER;
121
122 FUNCTION INVOICE_LINE_AMOUNT_CREDITED(
123 p_customer_trx_id IN NUMBER,
124 p_customer_trx_line_id NUMBER) RETURN NUMBER;
125
126 FUNCTION INVOICE_LINE_AMOUNT_REMAINING(
127 p_customer_trx_id IN NUMBER,
128 p_customer_trx_line_id NUMBER) RETURN NUMBER;
129
130 FUNCTION INVOICE_AMOUNT_ORIG(
131 p_customer_trx_id IN NUMBER) RETURN NUMBER;
132
133 FUNCTION INVOICE_AMOUNT_APPLIED(
134 p_customer_trx_id IN NUMBER) RETURN NUMBER;
135
136 FUNCTION INVOICE_AMOUNT_CREDITED(
137 p_customer_trx_id IN NUMBER) RETURN NUMBER;
138
139 FUNCTION INVOICE_AMOUNT_REMAINING(
140 p_customer_trx_id IN NUMBER) RETURN NUMBER;
141
142 FUNCTION LINE_ID_APPLIED(p_cash_receipt_id IN NUMBER,
143 p_customer_trx_id IN NUMBER) RETURN NUMBER;
144
145 FUNCTION LINE_NUMBER_APPLIED(p_cash_receipt_id IN NUMBER,
146 p_customer_trx_id IN NUMBER) RETURN NUMBER;
147
148 --FUNCTION DEBUG_PROC(msg varchar2) RETURN VARCHAR2;
149
150 FUNCTION get_tld_amount_orig( p_tld_id IN NUMBER ) RETURN NUMBER;
151 FUNCTION get_tld_amount_applied( p_tld_id IN NUMBER ) RETURN NUMBER;
152 FUNCTION get_tld_amount_credited( p_tld_id IN NUMBER ) RETURN NUMBER;
153 FUNCTION get_tld_amount_remaining( p_tld_id IN NUMBER ) RETURN NUMBER;
154
155 PROCEDURE get_tld_balance(
156 p_api_version IN NUMBER
157 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
158 ,p_tld_id IN NUMBER
159 ,x_return_status OUT NOCOPY VARCHAR2
160 ,x_msg_count OUT NOCOPY NUMBER
161 ,x_msg_data OUT NOCOPY VARCHAR2
162 ,x_orig_amount OUT NOCOPY NUMBER
163 ,x_applied_amount OUT NOCOPY NUMBER
164 ,x_credited_amount OUT NOCOPY NUMBER
165 ,x_remaining_amount OUT NOCOPY NUMBER
166 ,x_tax_amount OUT NOCOPY NUMBER
167 );
168
169 PROCEDURE get_contract_invoice_balance(
170 p_api_version IN NUMBER
171 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
172 ,p_contract_number IN VARCHAR2
173 ,p_trx_number IN VARCHAR2
174 ,x_return_status OUT NOCOPY VARCHAR2
175 ,x_msg_count OUT NOCOPY NUMBER
176 ,x_msg_data OUT NOCOPY VARCHAR2
177 ,x_remaining_amount OUT NOCOPY NUMBER
178 );
179
180
181 FUNCTION INVOICE_LINE_TAX_AMOUNT(p_customer_trx_line_id NUMBER) RETURN NUMBER;
182
183
184 -------------------------------------------------------------------------------
185 -- PROCEDURE CHECK_PREUPGRADE_DATA
186 -------------------------------------------------------------------------------
187 -- Start of comments
188 --
189 -- Procedure Name : CHECK_PREUPGRADE_DATA
190 -- Description : Procedure to list In Process Billing Transactions
191 -- pre-upgrade script
192 -- Business Rules :
193 -- Parameters :x_errbuf,x_retcode,x_any_data_exists:Standard out parameters
194 -- Version : 1.0
195 -- History : 05-Sep-2007 VPANWAR created
196 -- End of comments
197 -------------------------------------------------------------------------------
198 PROCEDURE CHECK_PREUPGRADE_DATA(x_errbuf OUT NOCOPY VARCHAR2,
199 x_retcode OUT NOCOPY NUMBER,
200 x_any_data_exists OUT NOCOPY BOOLEAN );
201
202 FUNCTION get_tld_amt_remaining_WOTAX( p_tld_id IN NUMBER ) RETURN NUMBER;
203
204 FUNCTION INV_LN_AMT_APPLIED_WOTAX(
205 p_customer_trx_id IN NUMBER,
206 p_customer_trx_line_id NUMBER) RETURN NUMBER ;
207
208 FUNCTION INV_LN_AMT_CREDITED_WOTAX(
209 p_customer_trx_id IN NUMBER,
210 p_customer_trx_line_id NUMBER) RETURN NUMBER;
211
212 FUNCTION INV_LN_AMT_ORIG_WOTAX(
213 p_customer_trx_id IN NUMBER,
214 p_customer_trx_line_id NUMBER) RETURN NUMBER;
215
216 FUNCTION INV_AMT_REMAINING_WOTAX(
217 p_customer_trx_id IN NUMBER) RETURN NUMBER;
218
219
220 FUNCTION INV_LN_AMT_REMAINING_WOTAX(
221 p_customer_trx_id IN NUMBER,
222 p_customer_trx_line_id NUMBER) RETURN NUMBER;
223
224 --dkagrawa added function to get adjusted amount at header level
225 FUNCTION INVOICE_AMOUNT_ADJUSTED(
226 p_customer_trx_id IN NUMBER) RETURN NUMBER;
227
228 --dkagrawa added function to get adjusted amount at line level
229 FUNCTION INVOICE_LINE_AMOUNT_ADJUSTED(
230 p_customer_trx_id IN NUMBER,
231 p_customer_trx_line_id IN NUMBER) RETURN NUMBER;
232
233 --Bug# 7720775
234 FUNCTION INV_LN_AMT_ADJUSTED_WOTAX(
235 p_customer_trx_id IN NUMBER,
236 p_customer_trx_line_id IN NUMBER) RETURN NUMBER;
237
238 --Bug# 7720775
239 -- Functions to return Invoice Line amount with Inclusive Tax Line amount
240 FUNCTION INV_LN_AMT_APPLIED_W_INCTAX(
241 p_customer_trx_id IN NUMBER,
242 p_customer_trx_line_id NUMBER) RETURN NUMBER ;
243
244 FUNCTION INV_LN_AMT_CREDITED_W_INCTAX(
245 p_customer_trx_id IN NUMBER,
246 p_customer_trx_line_id NUMBER) RETURN NUMBER;
247
248 FUNCTION INV_LN_AMT_ORIG_W_INCTAX(
249 p_customer_trx_id IN NUMBER,
250 p_customer_trx_line_id NUMBER) RETURN NUMBER;
251
252 FUNCTION INV_LN_AMT_REMAINING_W_INCTAX(
253 p_customer_trx_id IN NUMBER,
254 p_customer_trx_line_id NUMBER) RETURN NUMBER;
255
256 FUNCTION INV_LN_AMT_ADJUSTED_W_INCTAX(
257 p_customer_trx_id IN NUMBER,
258 p_customer_trx_line_id IN NUMBER) RETURN NUMBER;
259
260 --Added by bkatraga for bug 12378106
261 PROCEDURE CR_INVOICE_AMOUNT_FOR_STREAM(
262 p_api_version IN NUMBER
263 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
264 ,x_return_status OUT NOCOPY VARCHAR2
265 ,x_msg_count OUT NOCOPY NUMBER
266 ,x_msg_data OUT NOCOPY VARCHAR2
267 ,p_stream_purpose IN VARCHAR2
268 ,p_cr_contract_id IN NUMBER
269 ,x_tot_inv_line_amount OUT NOCOPY NUMBER
270 );
271
272 END OKL_BILLING_UTIL_PVT;