[Home] [Help]
PACKAGE BODY: APPS.OKL_PAY_INVOICES_MAN_PVT
Source
1 PACKAGE BODY OKL_PAY_INVOICES_MAN_PVT AS
2 /* $Header: OKLRPIMB.pls 120.5 2007/02/08 11:55:53 sjalasut noship $ */
3
4 --------------------------------------------------------------------
5 -- PROCEDURE manual_entry
6 --------------------------------------------------------------------
7
8 PROCEDURE manual_entry (
9 p_api_version IN NUMBER
10 ,p_init_msg_list IN VARCHAR2 --DEFAULT OKC_API.G_FALSE
11 ,x_return_status OUT NOCOPY VARCHAR2
12 ,x_msg_count OUT NOCOPY NUMBER
13 ,x_msg_data OUT NOCOPY VARCHAR2
14 ,p_man_inv_rec IN man_inv_rec_type
15 ,x_man_inv_rec OUT NOCOPY man_inv_rec_type) IS
16
17 ------------------------------------------------------------
18 -- Declare variables required by APIs
19 ------------------------------------------------------------
20 l_api_version CONSTANT NUMBER := 1;
21 l_api_name CONSTANT VARCHAR2(30) := 'MANUAL_ENTRY';
22 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
23
24 -----------------------------------------------------------------
25 -- Declare Process Variable
26 -----------------------------------------------------------------
27 l_okl_application_id NUMBER(3) := 540;
28 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
29 lX_dbseqnm VARCHAR2(2000):= '';
30 lX_dbseqid NUMBER(38):= NULL;
31
32 ------------------------------------------------------------
33 -- Declare records: Payable Invoice Headers, Lines and Distributions
34 ------------------------------------------------------------
35 lp_tapv_rec okl_tap_pvt.tapv_rec_type;
36 lx_tapv_rec okl_tap_pvt.tapv_rec_type;
37 lp_tplv_rec okl_tpl_pvt.tplv_rec_type;
38 lx_tplv_rec okl_tpl_pvt.tplv_rec_type;
39
40 /* ankushar 23-JAN-2007
41 added table definitions
42 start changes
43 */
44 lp_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
45 lx_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
46 /* ankushar end changes*/
47
48 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
49
50 CURSOR org_id_csr ( p_khr_id NUMBER ) IS
51 SELECT chr.authoring_org_id
52 FROM okc_k_headers_b chr
53 WHERE id = p_khr_id;
54
55 CURSOR sob_csr ( p_org_id NUMBER ) IS
56 SELECT hru.set_of_books_id
57 FROM HR_OPERATING_UNITS HRU
58 WHERE ORGANIZATION_ID = p_org_id;
59
60 CURSOR try_id_csr IS
61 SELECT id
62 FROM okl_trx_types_tl
63 WHERE name = 'Disbursement'
64 AND language= 'US';
65
66 CURSOR pdt_id_csr ( p_khr_id NUMBER ) IS
67 SELECT khr.pdt_id
68 FROM okl_k_headers khr
69 WHERE khr.id = p_khr_id;
70
71 -- Temp Variable
72 l_kle_id NUMBER;
73
74 -- Multi Currency Compliance
75 l_currency_code okl_ext_sell_invs_b.currency_code%type;
76 l_currency_conversion_type okl_ext_sell_invs_b.currency_conversion_type%type;
77 l_currency_conversion_rate okl_ext_sell_invs_b.currency_conversion_rate%type;
78 l_currency_conversion_date okl_ext_sell_invs_b.currency_conversion_date%type;
79
80 CURSOR l_curr_conv_csr( cp_khr_id NUMBER ) IS
81 SELECT currency_code
82 ,currency_conversion_type
83 ,currency_conversion_rate
84 ,currency_conversion_date
85 FROM okl_k_headers_full_v
86 WHERE id = cp_khr_id;
87
88 BEGIN
89
90 ------------------------------------------------------------
91 -- Start processing
92 ------------------------------------------------------------
93
94 x_return_status := OKL_API.G_RET_STS_SUCCESS;
95
96 l_return_status := OKL_API.START_ACTIVITY (
97 p_api_name => l_api_name,
98 p_pkg_name => g_pkg_name,
99 p_init_msg_list => p_init_msg_list,
100 l_api_version => l_api_version,
101 p_api_version => p_api_version,
102 p_api_type => '_PVT',
103 x_return_status => x_return_status);
104
105 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
106 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
107 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
108 RAISE OKL_API.G_EXCEPTION_ERROR;
109 END IF;
110
111 IF (p_man_inv_rec.vendor_id IS NULL OR
112 p_man_inv_rec.khr_id IS NULL OR
113 p_man_inv_rec.sty_id IS NULL OR
114 p_man_inv_rec.invoice_date IS NULL OR
115 p_man_inv_rec.amount IS NULL) THEN
116 OKL_Api.SET_MESSAGE
117 ( p_app_name => 'OKL',
118 p_msg_name => 'OKL_ENTER_REQD_FIELDS'
119 ) ;
120 x_return_status := OKL_Api.G_RET_STS_ERROR;
121 RAISE G_EXCEPTION_HALT_VALIDATION;
122 END IF;
123
124 ------------------------------------------------------------
125 -- Derive Organization and Set of Books
126 ------------------------------------------------------------
127
128 lp_tapv_rec.org_id := NULL;
129
130 OPEN org_id_csr ( p_man_inv_rec.khr_id) ;
131 FETCH org_id_csr INTO lp_tapv_rec.org_id;
132 CLOSE org_id_csr;
133
134 SELECT hru.set_of_books_id
135 INTO lp_tapv_rec.set_of_books_id
136 FROM HR_OPERATING_UNITS HRU
137 WHERE ORGANIZATION_ID = lp_tapv_rec.org_id;
138
139 lp_tapv_rec.set_of_books_id := NULL;
140
141 OPEN sob_csr ( lp_tapv_rec.org_id );
142 FETCH sob_csr INTO lp_tapv_rec.set_of_books_id;
143 CLOSE sob_csr;
144
145 ------------------------------------------------------------
146 -- Derive Invoice Number
147 ------------------------------------------------------------
148
149 IF p_man_inv_rec.Vendor_Invoice_Number IS NULL
150 OR p_man_inv_rec.Vendor_Invoice_Number = OKL_API.G_MISS_CHAR THEN
151
152 lp_tapv_rec.invoice_number := NULL;
153
154 lp_tapv_rec.invoice_number := fnd_seqnum.get_next_sequence
155 (appid => l_okl_application_id,
156 cat_code => l_document_category,
157 sobid => lp_tapv_rec.set_of_books_id,
158 met_code => 'A',
159 trx_date => SYSDATE,
160 dbseqnm => lx_dbseqnm,
161 dbseqid => lx_dbseqid);
162
163 lp_tapv_rec.vendor_invoice_number := lp_tapv_rec.invoice_number;
164
165 ELSE
166
167 lp_tapv_rec.vendor_invoice_number := p_man_inv_rec.vendor_invoice_number;
168 lp_tapv_rec.invoice_number := p_man_inv_rec.vendor_invoice_number;
169
170 END IF;
171
172 ------------------------------------------------------------
173 -- FETCH try_id
174 ------------------------------------------------------------
175
176 lp_tapv_rec.try_id := NULL;
177
178 OPEN try_id_csr;
179 FETCH try_id_csr INTO lp_tapv_rec.try_id;
180 CLOSE try_id_csr;
181
182 ------------------------------------------------------------
183 -- Populate internal AP invoice header Record
184 ------------------------------------------------------------
185
186 lp_tapv_rec.amount := p_man_inv_rec.amount;
187
188 IF NVL(p_man_inv_rec.invoice_type, 'STANDARD') = 'CREDIT' THEN
189 lp_tapv_rec.amount := - lp_tapv_rec.amount;
190 END IF;
191
192 lp_tapv_rec.vendor_id := p_man_inv_rec.vendor_id;
193 lp_tapv_rec.ipvs_id := p_man_inv_rec.ipvs_id;
194 -- sjalasut, commented the khr_id assignment at the internal transaction table
195 -- header level. khr_id would be referred at the internal transaction lines table
196 -- changes made as part of OKLR12B disbursements project
197 -- lp_tapv_rec.khr_id := p_man_inv_rec.khr_id;
198 lp_tapv_rec.khr_id := NULL;
199 lp_tapv_rec.currency_code := p_man_inv_rec.currency;
200 lp_tapv_rec.payment_method_code := p_man_inv_rec.payment_method_code;
201 lp_tapv_rec.date_entered := sysdate;
202 lp_tapv_rec.date_invoiced := p_man_inv_rec.invoice_date;
203 lp_tapv_rec.invoice_category_code := p_man_inv_rec.invoice_category_code;
204 lp_tapv_rec.ippt_id := p_man_inv_rec.pay_terms;
205 lp_tapv_rec.invoice_type := p_man_inv_rec.invoice_type;
206 lp_tapv_rec.Pay_Group_lookup_code := p_man_inv_rec.Pay_Group_lookup_code;
207 lp_tapv_rec.trx_status_code := 'ENTERED';
208 lp_tapv_rec.nettable_yn := 'N';
209
210 -- 02-NOV-2006 ANSETHUR R12B - Legal Entity
211 lp_tapv_rec.legal_entity_id := okl_legal_entity_util.get_khr_le_id
212 (p_man_inv_rec.khr_id);
213
214 -- Multi Currency Code, stmathew
215
216 l_currency_code := NULL;
217 l_currency_conversion_type := NULL;
218 l_currency_conversion_rate := NULL;
219 l_currency_conversion_date := NULL;
220
221 OPEN l_curr_conv_csr (p_man_inv_rec.khr_id);
222 FETCH l_curr_conv_csr INTO l_currency_code,
223 l_currency_conversion_type,
224 l_currency_conversion_rate,
225 l_currency_conversion_date;
226 CLOSE l_curr_conv_csr;
227
228 lp_tapv_rec.currency_code := l_currency_code;
229 lp_tapv_rec.CURRENCY_CONVERSION_TYPE := l_currency_conversion_type;
230 lp_tapv_rec.CURRENCY_CONVERSION_RATE := l_currency_conversion_rate;
231 lp_tapv_rec.CURRENCY_CONVERSION_DATE := l_currency_conversion_date;
232
233
234 -- If the type were not captured in authoring
235 IF lp_tapv_rec.currency_conversion_type IS NULL THEN
236 lp_tapv_rec.currency_conversion_type := 'User';
237 lp_tapv_rec.currency_conversion_rate := 1;
238 lp_tapv_rec.currency_conversion_date := SYSDATE;
239 END IF;
240
241 -- For date
242 IF lp_tapv_rec.currency_conversion_date IS NULL THEN
243 lp_tapv_rec.currency_conversion_date := SYSDATE;
244 END IF;
245
246 -- For rate -- Work out the rate in a Spot or Corporate
247 IF (lp_tapv_rec.currency_conversion_type = 'User') THEN
248 IF lp_tapv_rec.currency_conversion_rate IS NULL THEN
249 lp_tapv_rec.currency_conversion_rate := 1;
250 END IF;
251 END IF;
252 IF (lp_tapv_rec.currency_conversion_type = 'Spot'
253 OR lp_tapv_rec.currency_conversion_type = 'Corporate') THEN
254
255 lp_tapv_rec.currency_conversion_rate
256 := okl_accounting_util.get_curr_con_rate
257 (p_from_curr_code => lp_tapv_rec.currency_code,
258 p_to_curr_code => okl_accounting_util.get_func_curr_code,
259 p_con_date => lp_tapv_rec.currency_conversion_date,
260 p_con_type => lp_tapv_rec.currency_conversion_type);
261 END IF;
262
263 ----------------------------------------------------
264 -- Populate internal AP invoice Lines Record
265 ----------------------------------------------------
266
267 -- sjalasut, added assignment of khr_id to the lines table. changes made as part
268 -- of OKLR12B disbursements project
269 lp_tplv_rec.khr_id := p_man_inv_rec.khr_id;
270 lp_tplv_rec.amount := lp_tapv_rec.amount;
271 lp_tplv_rec.sty_id := p_man_inv_rec.sty_id;
272 lp_tplv_rec.inv_distr_line_code := 'MANUAL';
273 lp_tplv_rec.line_number := 1;
274 lp_tplv_rec.org_id := lp_tapv_rec.org_id;
275 lp_tplv_rec.disbursement_basis_code := 'BILL_DATE';
276
277 -- ----------------------------------------
278 -- added sel_id to record def 14-sep-2004
279 -- ----------------------------------------
280 lp_tplv_rec.id := p_man_inv_rec.sel_id;
281
282
283 /* ankushar 23-JAN-2007
284 Call to the common Disbursement API
285 start changes
286 */
287
288 -- Add tpl_rec to table
289 lp_tplv_tbl(1) := lp_tplv_rec;
290
291 --Call the commong disbursement API to create transactions
292 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
293 p_api_version => p_api_version
294 ,p_init_msg_list => p_init_msg_list
295 ,x_return_status => x_return_status
296 ,x_msg_count => x_msg_count
297 ,x_msg_data => x_msg_data
298 ,p_tapv_rec => lp_tapv_rec
299 ,p_tplv_tbl => lp_tplv_tbl
300 ,x_tapv_rec => lx_tapv_rec
301 ,x_tplv_tbl => lx_tplv_tbl);
302
303 /* ankushar end changes */
304
305 IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
306
307 --------------------------------------------
308 -- Populate inserted record details back
309 --------------------------------------------
310 x_man_inv_rec := p_man_inv_rec;
311 --cklee..bug# 5012438..08-Mar-2006
312 --populating invoice number into OUT param
313 x_man_inv_rec.invoice_number := lp_tapv_rec.invoice_number;
314 x_man_inv_rec.vendor_invoice_number := lp_tapv_rec.vendor_invoice_number;
315
316 END IF; -- disbursement API call
317
318 EXCEPTION
319
320 ------------------------------------------------------------
321 -- Exception handling
322 ------------------------------------------------------------
323
324 WHEN G_EXCEPTION_HALT_VALIDATION THEN
325 null;
326 WHEN OKL_API.G_EXCEPTION_ERROR THEN
327 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
328 p_api_name => l_api_name,
329 p_pkg_name => G_PKG_NAME,
330 p_exc_name => 'OKL_API.G_RET_STS_ERROR',
331 x_msg_count => x_msg_count,
332 x_msg_data => x_msg_data,
333 p_api_type => '_PVT');
334
335 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
336 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
337 p_api_name => l_api_name,
338 p_pkg_name => G_PKG_NAME,
339 p_exc_name => 'OKL_API.G_RET_STS_UNEXP_ERROR',
340 x_msg_count => x_msg_count,
341 x_msg_data => x_msg_data,
342 p_api_type => '_PVT');
343
344 WHEN OTHERS THEN
345 x_return_status := OKL_API.HANDLE_EXCEPTIONS (
346 p_api_name => l_api_name,
347 p_pkg_name => G_PKG_NAME,
348 p_exc_name => 'OTHERS',
349 x_msg_count => x_msg_count,
350 x_msg_data => x_msg_data,
351 p_api_type => '_PVT');
352 END manual_entry;
353
354 --------------------------------------------------------------------
355 -- PROCEDURE manual_entry
356 --------------------------------------------------------------------
357
358 PROCEDURE manual_entry (
359 p_api_version IN NUMBER
360 ,p_init_msg_list IN VARCHAR2 --DEFAULT OKC_API.G_FALSE
361 ,x_return_status OUT NOCOPY VARCHAR2
362 ,x_msg_count OUT NOCOPY NUMBER
363 ,x_msg_data OUT NOCOPY VARCHAR2
364 ,p_man_inv_tbl IN man_inv_tbl_type
365 ,x_man_inv_tbl OUT NOCOPY man_inv_tbl_type) IS
366
367 l_api_version CONSTANT NUMBER := 1;
368 l_api_name CONSTANT VARCHAR2(30) := 'MANUAL_ENTRY';
369 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
370 l_overall_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
371 i NUMBER := 0;
372
373 BEGIN
374
375 -- Enter further code below as specified in the Package spec.
376 -- Make sure PL/SQL table has records in it before passing
377
378 IF (p_man_inv_tbl.COUNT > 0) THEN
379
380 i := p_man_inv_tbl.FIRST;
381
382 LOOP
383
384 manual_entry (
385 p_api_version => l_api_version,
386 p_init_msg_list => OKL_API.G_FALSE,
387 x_return_status => x_return_status,
388 x_msg_count => x_msg_count,
389 x_msg_data => x_msg_data,
390 p_man_inv_rec => p_man_inv_tbl(i),
391 x_man_inv_rec => x_man_inv_tbl(i));
392
393 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
394 IF l_overall_status <> OKL_API.G_RET_STS_UNEXP_ERROR THEN
395 l_overall_status := x_return_status;
396 END IF;
397 END IF;
398
399 EXIT WHEN (i = p_man_inv_tbl.LAST);
400 i := p_man_inv_tbl.NEXT(i);
401
402 END LOOP;
403
404 x_return_status := l_overall_status;
405
406 END IF;
407
408 END manual_entry;
409
410 END OKL_PAY_INVOICES_MAN_PVT;