[Home] [Help]
PACKAGE BODY: APPS.OKL_CASH_APPL_RULES
Source
1 PACKAGE BODY OKL_CASH_APPL_RULES AS
2 /* $Header: OKLRCAPB.pls 120.20 2007/10/05 06:52:27 varangan noship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.SETUP';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9 ---------------------------------------------------------------------------
10 -- Function get_okl_installed
11 ---------------------------------------------------------------------------
12
13 FUNCTION okl_installed (p_org_id IN NUMBER) RETURN BOOLEAN IS
14
15 RULES NUMBER DEFAULT NULL;
16 okl_ins BOOLEAN DEFAULT FALSE;
17
18 BEGIN
19
20 SELECT ID INTO RULES
21 FROM OKL_CASH_ALLCTN_RLS_ALL
22 WHERE DEFAULT_RULE = 'YES'
23 AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE)
24 AND org_id = p_org_id;
25
26
27 IF RULES IS NOT NULL THEN
28 okl_ins := TRUE;
29 END IF;
30
31 RETURN okl_ins;
32
33 EXCEPTION
34
35 WHEN OTHERS THEN
36
37 okl_ins := FALSE;
38
39 -- abindal added for bug 4992891 --
40 RETURN okl_ins;
41
42 END okl_installed;
43
44 ---------------------------------------------------------------------------
45 -- PROCEDURE handle_manual_pay
46 ---------------------------------------------------------------------------
47
48 PROCEDURE handle_manual_pay ( p_api_version IN NUMBER
49 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
50 ,x_return_status OUT NOCOPY VARCHAR2
51 ,x_msg_count OUT NOCOPY NUMBER
52 ,x_msg_data OUT NOCOPY VARCHAR2
53 ,p_cons_bill_id IN OKL_CNSLD_AR_HDRS_V.ID%TYPE DEFAULT NULL
54 ,p_cons_bill_num IN OKL_CNSLD_AR_HDRS_V.CONSOLIDATED_INVOICE_NUMBER%TYPE DEFAULT NULL
55 ,p_currency_code IN OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL
56 ,p_currency_conv_type IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT NULL
57 ,p_currency_conv_date IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT NULL
58 ,p_currency_conv_rate IN OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT NULL
59 ,p_irm_id IN OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE DEFAULT NULL
60 ,p_check_number IN OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT NULL
61 ,p_rcpt_amount IN OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL
62 ,p_contract_id IN OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL
63 ,p_contract_num IN OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL
64 ,p_customer_id IN OKL_TRX_CSH_RECEIPT_V.ILE_id%TYPE DEFAULT NULL
65 ,p_customer_num IN AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT NULL
66 ,p_gl_date IN OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT NULL
67 ,p_receipt_date IN OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT NULL
68 ,p_bank_account_id IN OKL_TRX_CSH_RECEIPT_V.IBA_ID%TYPE DEFAULT NULL
69 ,p_comments IN AR_CASH_RECEIPTS_ALL.COMMENTS%TYPE DEFAULT NULL
70 ,p_create_receipt_flag IN VARCHAR2
71 ) IS
72
73 ---------------------------
74 -- DECLARE Local Variables
75 ---------------------------
76
77 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_id%TYPE DEFAULT p_customer_id;
78 l_customer_num AR_CASH_RECEIPTS_ALL.PAY_FROM_CUSTOMER%TYPE DEFAULT p_customer_num;
79
80 l_cons_bill_id OKL_CNSLD_AR_HDRS_V.ID%TYPE DEFAULT p_cons_bill_id;
81 l_cons_bill_num OKL_CNSLD_AR_HDRS_V.CONSOLIDATED_INVOICE_NUMBER%TYPE DEFAULT p_cons_bill_num;
82 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT p_contract_id;
83 l_last_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT 1;
84 l_contract_num OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT p_contract_num;
85 l_contract_number_start_date OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
86 l_contract_start_date OKC_K_HEADERS_V.CONTRACT_NUMBER%TYPE DEFAULT NULL;
87 l_contract_number_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
88
89 --
90 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT p_currency_conv_type;
91 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT p_currency_conv_date;
92 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT p_currency_conv_rate;
93 --
94
95 l_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
96 l_functional_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
97 l_inverse_conversion_rate GL_DAILY_RATES_V.INVERSE_CONVERSION_RATE%TYPE DEFAULT 0;
98 l_functional_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
99 l_invoice_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
100 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_currency_code;
101 l_irm_id OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE DEFAULT p_irm_id; -- receipt method id
102 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT p_check_number;
103 l_rcpt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_rcpt_amount;
104 l_rcpt_amount_orig OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_rcpt_amount;
105 l_converted_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
106 l_rcpt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT TRUNC(p_receipt_date);
107 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT p_gl_date;
108 l_comments AR_CASH_RECEIPTS_ALL.COMMENTS%TYPE DEFAULT p_comments;
109
110 l_cash_receipt_id AR_CASH_RECEIPTS_ALL.CASH_RECEIPT_ID%TYPE DEFAULT NULL;
111 l_bank_account_id OKL_TRX_CSH_RECEIPT_V.IBA_ID%TYPE DEFAULT p_bank_account_id;
112 l_receivables_invoice_num NUMBER DEFAULT NULL;
113
114 l_over_pay VARCHAR(1) DEFAULT NULL;
115 l_conc_proc VARCHAR(2) DEFAULT p_create_receipt_flag;
116 l_ordered CONSTANT VARCHAR2(3) := 'ODD';
117 l_prorate CONSTANT VARCHAR2(3) := 'PRO';
118
119 l_start_date DATE;
120 l_same_cash_app_rule VARCHAR(1) DEFAULT NULL;
121 l_same_date VARCHAR(1) DEFAULT NULL;
122
123
124 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT MO_GLOBAL.GET_CURRENT_ORG_ID();
125 i NUMBER DEFAULT NULL;
126 d NUMBER DEFAULT NULL;
127 t NUMBER DEFAULT NULL;
128 l_first_prorate_rec NUMBER DEFAULT NULL;
129 l_order_count NUMBER DEFAULT NULL;
130
131 l_appl_tolerance NUMBER := 0;
132 l_temp_val NUMBER := 0;
133 l_inv_tot NUMBER := 0;
134 l_cont_tot NUMBER := 0;
135 l_stream_tot NUMBER := 0;
136 l_pro_rate_inv_total NUMBER := 0;
137
138 l_rct_id OKL_TRX_CSH_RECEIPT_B.ID%TYPE;
139 l_rca_id OKL_TXL_RCPT_APPS_V.ID%TYPE;
140 l_xcr_id NUMBER;
141
142 l_sty_id OKL_CNSLD_AR_STRMS_V.STY_ID%TYPE;
143
144 l_rule_name OKL_CASH_ALLCTN_RLS.NAME%TYPE DEFAULT NULL;
145
146 l_check_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
147 l_cau_id OKL_CSH_ALLCTN_RL_HDR.ID%TYPE DEFAULT NULL;
148 l_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
149 l_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
150 l_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
151 l_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
152 l_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
153 l_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
154 l_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
155
156 l_dflt_name OKL_CASH_ALLCTN_RLS.NAME%TYPE DEFAULT NULL;
157
158 l_dflt_cat_id OKL_CASH_ALLCTN_RLS.ID%TYPE DEFAULT NULL;
159 l_dflt_tolerance OKL_CASH_ALLCTN_RLS.AMOUNT_TOLERANCE_PERCENT%TYPE DEFAULT NULL;
160 l_dflt_days_past_quote_valid OKL_CASH_ALLCTN_RLS.DAYS_PAST_QUOTE_VALID_TOLERANC%TYPE DEFAULT NULL;
161 l_dflt_months_to_bill_ahead OKL_CASH_ALLCTN_RLS.MONTHS_TO_BILL_AHEAD%TYPE DEFAULT NULL;
162 l_dflt_under_payment OKL_CASH_ALLCTN_RLS.UNDER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
163 l_dflt_over_payment OKL_CASH_ALLCTN_RLS.OVER_PAYMENT_ALLOCATION_CODE%TYPE DEFAULT NULL;
164 l_dflt_receipt_msmtch OKL_CASH_ALLCTN_RLS.RECEIPT_MSMTCH_ALLOCATION_CODE%TYPE DEFAULT NULL;
165 l_sequence_number OKL_STRM_TYP_ALLOCS.SEQUENCE_NUMBER%TYPE DEFAULT NULL;
166
167 l_dup_rcpt_flag NUMBER DEFAULT NULL;
168 l_create_receipt_flag VARCHAR2(2) DEFAULT p_create_receipt_flag;
169 l_cash_applied_flag VARCHAR2(1) DEFAULT NULL;
170 l_cont_applic VARCHAR2(1) DEFAULT 'N';
171 l_cons_bill_applic VARCHAR2(1) DEFAULT 'N';
172
173
174 l_api_version NUMBER := 1.0;
175 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
176 l_return_status VARCHAR2(1);
177 l_msg_count NUMBER;
178 l_msg_data VARCHAR2(2000);
179
180
181 l_api_name CONSTANT VARCHAR2(30) := 'handle_manual_pay';
182
183 ------------------------------
184 -- DECLARE Record/Table Types
185 ------------------------------
186
187 -- Internal Trans
188
189 l_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
190 l_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
191
192 l_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
193 l_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
194
195 x_rctv_rec Okl_Rct_Pvt.rctv_rec_type;
196 x_rctv_tbl Okl_Rct_Pvt.rctv_tbl_type;
197
198 x_rcav_rec Okl_Rca_Pvt.rcav_rec_type;
199 x_rcav_tbl Okl_Rca_Pvt.rcav_tbl_type;
200
201 ----------
202
203 -- External Trans
204
205 l_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
206 l_xcrv_tbl Okl_Xcr_Pvt.xcrv_tbl_type;
207
208 l_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
209 l_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
210
211 x_xcrv_rec Okl_Xcr_Pvt.xcrv_rec_type;
212 x_xcrv_tbl Okl_Xcr_Pvt.xcrv_tbl_type;
213
214 x_xcav_rec Okl_Xca_Pvt.xcav_rec_type;
215 x_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
216
217 t_xcav_tbl Okl_Xca_Pvt.xcav_tbl_type;
218
219
220 -------------------
221 -- DECLARE Cursors
222 -------------------
223 -- Updated the cursor to fetch with consolidated bill number, to exclude NVL condition
224 -- for performance issue - bug#5484903
225 CURSOR c_open_invs ( cp_cons_bill_num IN VARCHAR2
226 ,cp_contract_num IN VARCHAR2
227 ,cp_customer_num IN VARCHAR2
228 ,cp_stream_type_id IN NUMBER) IS
229 SELECT lpt.stream_type_id
230 ,lpt.amount_due_remaining
231 ,lpt.currency_code
232 ,lpt.receivables_invoice_number
233 ,lpt.stream_id
234 ,lpt.trx_date
235 FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
236 WHERE lpt.consolidated_invoice_number = cp_cons_bill_num
237 AND NVL (lpt.contract_number, 999) = NVL (DECODE (cp_contract_num, NULL, lpt.contract_number, cp_contract_num), 999)
238 AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number)
239 AND lpt.stream_type_id = NVL (cp_stream_type_id, lpt.stream_type_id)
240 AND lpt.status = 'OP'
241 AND lpt.amount_due_remaining > 0;
242
243 c_open_invs_rec c_open_invs%ROWTYPE;
244
245 ----------
246 --Included another cursor to exclude the NVL condition on contract number
247 -- Peformance Issue -bug#5484903
248
249 CURSOR c_open_invs_cont ( cp_cons_bill_num IN VARCHAR2
250 ,cp_contract_num IN VARCHAR2
251 ,cp_customer_num IN VARCHAR2
252 ,cp_stream_type_id IN NUMBER) IS
253 SELECT lpt.stream_type_id
254 ,lpt.amount_due_remaining
255 ,lpt.currency_code
256 ,lpt.receivables_invoice_number
257 ,lpt.stream_id
258 ,lpt.trx_date
259 FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
260 WHERE lpt.consolidated_invoice_number = NVL (cp_cons_bill_num, lpt.consolidated_invoice_number)
261 AND lpt.contract_number = cp_contract_num
262 AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number)
263 AND lpt.stream_type_id = NVL (cp_stream_type_id, lpt.stream_type_id)
264 AND lpt.status = 'OP'
265 AND lpt.amount_due_remaining > 0;
266
267 ---
268
269
270
271 CURSOR c_inv_date ( cp_cons_bill_num IN VARCHAR2
272 ,cp_customer_num IN VARCHAR2) IS
273 SELECT DISTINCT(lpt.contract_number)
274 ,lpt.start_date, lpt.contract_id
275 FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
276 WHERE lpt.consolidated_invoice_number = cp_cons_bill_num -- for performance issue #5484903
277 --NVL (cp_cons_bill_num, lpt.consolidated_invoice_number)
278 AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number);
279
280 c_inv_date_rec c_inv_date%ROWTYPE;
281
282 ----------
283
284 -- get stream application order
285 -- bug 5038588, select streams relevant to contract only
286 CURSOR c_stream_alloc ( cp_str_all_type IN VARCHAR2
287 ,cp_cat_id IN NUMBER
288 ,cp_khr_id IN NUMBER) IS
289 SELECT distinct sta.sty_id, sta.sequence_number
290 FROM OKL_STRM_TYP_ALLOCS sta, OKL_CNSLD_AR_STRMS_B st
291 WHERE sta.stream_allc_type = cp_str_all_type
292 AND sta.cat_id = cp_cat_id
293 AND st.khr_id = cp_khr_id
294 AND sta.sty_id = st.sty_id
295 ORDER BY sta.sequence_number;
296
297 ----------
298 /*
299 -- get current exchange rates
300 CURSOR c_get_conversion_rate( cp_from_currency IN VARCHAR2
301 ,cp_to_currency IN VARCHAR2) IS
302 SELECT conversion_rate, inverse_conversion_rate
303 FROM GL_DAILY_RATES_V
304 WHERE conversion_type = 'Corporate'
305 AND status_code = 'C'
306 AND from_currency = cp_from_currency
307 AND to_currency = cp_to_currency
308 ORDER BY conversion_date DESC;
309 */
310 ----------
311
312 -- get a contract number if not known
313 -- Added distinct clause as per bug 4510824
314
315 CURSOR c_get_contract_num (cp_cons_bill_num IN VARCHAR2) IS
316
317 select distinct(ST.KHR_ID) CONTRACT_ID ,
318 CN.CONTRACT_NUMBER CONTRACT_NUMBER ,
319 CN.START_DATE START_DATE
320 from
321 OKC_K_HEADERS_ALL_B CN,
322 OKL_CNSLD_AR_STRMS_B ST,
323 OKL_CNSLD_AR_HDRS_B HD,
324 HZ_CUST_ACCOUNTS CA,
325 OKL_CNSLD_AR_LINES_B LN,
326 AR_PAYMENT_SCHEDULES_ALL PS
327 WHERE PS.CLASS IN ('INV', 'CM')
328 AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID AND LN.ID = ST.LLN_ID
329 AND HD.ID = LN.CNR_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
330 AND CN.ID = ST.KHR_ID
331 and HD.CONSOLIDATED_INVOICE_NUMBER = cp_cons_bill_num
332 and ps.status = 'OP'
333 and ps.amount_due_remaining > 0
334 ORDER BY CN.start_date; -- fixed for performance bug#5484903 - varangan- 19-9-06
335
336 /* commented for bug#5484903 - varangan-19-9-06
337 SELECT distinct (lpt.contract_id), lpt.contract_number, lpt.start_date
338 FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
339 WHERE lpt.consolidated_invoice_number = cp_cons_bill_num
340 AND lpt.status = 'OP'
341 AND lpt.amount_due_remaining > 0*/
342
343
344 ----------
345
346 -- get a contract id if not known
347 CURSOR c_get_contract_id (cp_contract_num IN VARCHAR2) IS
348 select ST.KHR_ID CONTRACT_ID
349 from
350 OKC_K_HEADERS_ALL_B CN,
351 OKL_CNSLD_AR_STRMS_B ST,
352 AR_PAYMENT_SCHEDULES_ALL PS
353 WHERE PS.CLASS IN ('INV', 'CM')
354 AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
355 AND CN.ID = ST.KHR_ID
356 and CN.CONTRACT_NUMBER = cp_contract_num
357 and ps.status = 'OP'
358 and ps.amount_due_remaining > 0
359 ORDER BY CN.start_date; -- fixed for performance bug#5484903 - varangan- 19-9-06
360
361 /* commented for bug#5484903 - varangan-19-9-06
362 SELECT lpt.contract_id
363 FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
364 WHERE lpt.contract_number = cp_contract_num
365 AND lpt.status = 'OP'
366 AND lpt.amount_due_remaining > 0
367 ORDER BY lpt.start_date; */
368
369 ----------
370
371 -- get org_id for contract
372 CURSOR c_get_org_id (cp_contract_num IN VARCHAR2) IS
373 SELECT authoring_org_id
374 FROM OKC_K_HEADERS_B
375 WHERE contract_number = cp_contract_num;
376
377 ----------
378
379 -- check for duplicate receipt numbers
380 CURSOR c_dup_rcpt( cp_customer_id IN NUMBER
381 ,cp_check_num IN VARCHAR2
382 --,cp_amount IN NUMBER
383 ,cp_receipt_date IN DATE
384 ) IS
385 SELECT '1'
386 FROM OKL_TRX_CSH_RECEIPT_V
387 WHERE ile_id = cp_customer_id
388 AND check_number = cp_check_num
389 -- AND amount = cp_amount
390 AND TRUNC(date_effective) = TRUNC(cp_receipt_date);
391
392 ----------
393
394 -- get header and line id's for consolidated invoice reference
395 CURSOR c_get_int_id_cons ( cp_customer_id IN NUMBER
396 ,cp_check_num IN VARCHAR2
397 ,cp_amount IN NUMBER
398 ,cp_cons_bill_id IN NUMBER) IS
399 SELECT a.id, b.id
400 FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b
401 WHERE a.id = b.rct_id_details
402 AND a.ile_id = cp_customer_id
403 AND a.check_number = cp_check_num
404 AND a.amount = cp_amount
405 AND b.cnr_id = NVL(cp_cons_bill_id, NULL);
406
407 ----------
408
409 -- get header and line id's for contract reference
410 CURSOR c_get_int_id_cont ( cp_customer_id IN NUMBER
411 ,cp_check_num IN VARCHAR2
412 ,cp_amount IN NUMBER
413 ,cp_contract_id IN NUMBER) IS
414 SELECT a.id, b.id
415 FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b
416 WHERE a.id = b.rct_id_details
417 AND a.ile_id = cp_customer_id
418 AND a.check_number = cp_check_num
419 AND a.amount = cp_amount
420 AND b.khr_id = NVL(cp_contract_id, NULL);
421
422 ----------
423
424 -- get bank details
425 CURSOR c_get_remit_bnk_dtls ( cp_irm_id IN NUMBER ) IS
426 SELECT bank_name, bank_account_num
427 FROM OKL_BPD_RCPT_MTHDS_UV
428 WHERE receipt_method_id = cp_irm_id;
429
430 ----------
431
432 -- get cash applic rule id
433 CURSOR c_cash_rle_id_csr ( cp_khr_id IN NUMBER) IS
434 SELECT to_number(a.object1_id1)
435 FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
436 WHERE a.rgp_id = b.id
437 AND b.rgd_code = 'LABILL'
438 AND a.rule_information_category = 'LAINVD'
439 AND a.dnz_chr_id = b.chr_id
440 AND a.dnz_chr_id = cp_khr_id;
441
442 ----------
443
444 -- get cash applic rule for contract
445 CURSOR c_cash_rule_csr ( cp_cau_id IN NUMBER ) IS
446 SELECT ID
447 ,NAME
448 ,AMOUNT_TOLERANCE_PERCENT
449 ,DAYS_PAST_QUOTE_VALID_TOLERANC
450 ,MONTHS_TO_BILL_AHEAD
451 ,UNDER_PAYMENT_ALLOCATION_CODE
452 ,OVER_PAYMENT_ALLOCATION_CODE
453 ,RECEIPT_MSMTCH_ALLOCATION_CODE
454 FROM OKL_CASH_ALLCTN_RLS
455 WHERE CAU_ID = cp_cau_id
456 AND START_DATE <= trunc(SYSDATE)
457 AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
458
459 ----------
460
461 -- get default cash applic rule for organization
462 CURSOR c_dflt_cash_applic_rule IS
463 SELECT ID
464 ,NAME
465 ,AMOUNT_TOLERANCE_PERCENT
466 ,DAYS_PAST_QUOTE_VALID_TOLERANC
467 ,MONTHS_TO_BILL_AHEAD
468 ,UNDER_PAYMENT_ALLOCATION_CODE
469 ,OVER_PAYMENT_ALLOCATION_CODE
470 ,RECEIPT_MSMTCH_ALLOCATION_CODE
471 FROM OKL_CASH_ALLCTN_RLS
472 WHERE default_rule = 'YES'
473 AND TRUNC(end_date) IS NULL;
474
475
476 --start code by pgomes on 03/05/2003
477 CURSOR l_khr_curr_csr(cp_contract_id IN NUMBER) IS SELECT currency_code
478 FROM okl_k_headers_full_v
479 WHERE id = cp_contract_id;
480
481 CURSOR l_inv_curr_csr(cp_cons_bill_id IN NUMBER) IS SELECT currency_code
482 FROM okl_cnsld_ar_hdrs_b
483 WHERE id = cp_cons_bill_id;
484
485 l_currency_code okl_k_headers_full_v.currency_code%type;
486 --end code by pgomes on 03/05/2003
487 BEGIN
488
489 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
490 G_PKG_NAME,
491 p_init_msg_list,
492 l_api_version,
493 p_api_version,
494 '_PVT',
495 l_return_status);
496
497 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
498 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
499 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
500 RAISE OKC_API.G_EXCEPTION_ERROR;
501 END IF;
502
503 l_functional_currency := okl_accounting_util.get_func_curr_code;
504
505 -- check for mandatory fields
506
507 IF l_customer_num = '0' OR
508 l_cons_bill_num = '0' OR
509 l_contract_num = '0' OR
510 l_receipt_currency_code = '0' OR
511 l_irm_id = '0' OR
512 l_check_number = '0' OR
513 l_rcpt_date IS NULL OR
514 l_rcpt_date = '' OR
515 l_rcpt_amount = '0' THEN
516
517 -- Message Text: Please enter all mandatory fields
518 x_return_status := OKC_API.G_RET_STS_ERROR;
519 OKC_API.set_message( p_app_name => G_APP_NAME,
520 p_msg_name =>'OKL_BPD_MISSING_FIELDS');
521
522 RAISE G_EXCEPTION_HALT_VALIDATION;
523
524 END IF;
525
526 --start code by pgomes on 03/05/2003
527 IF (p_contract_id IS NOT NULL) THEN
528 OPEN l_khr_curr_csr(p_contract_id);
529 FETCH l_khr_curr_csr INTO l_currency_code;
530 CLOSE l_khr_curr_csr;
531
532 IF (l_currency_code <> p_currency_code) THEN
533 OKC_API.set_message( p_app_name => G_APP_NAME,
534 p_msg_name =>'OKL_BPD_RCPT_KHR_CURR_ERROR');
535
536 RAISE G_EXCEPTION_HALT_VALIDATION;
537 END IF;
538 ELSIF (p_cons_bill_id IS NOT NULL) THEN
539 OPEN l_inv_curr_csr(p_cons_bill_id);
540 FETCH l_inv_curr_csr INTO l_currency_code;
541 CLOSE l_inv_curr_csr;
542
543 IF (l_currency_code <> p_currency_code) THEN
544 OKC_API.set_message( p_app_name => G_APP_NAME,
545 p_msg_name =>'OKL_BPD_RCPT_INV_CURR_ERROR');
546
547 RAISE G_EXCEPTION_HALT_VALIDATION;
548 END IF;
549 END IF;
550 --end code by pgomes on 03/05/2003
551
552 -- check receipt date and gl_date
553 /*
554 IF l_rcpt_date IS NULL OR
555 l_rcpt_date = '' THEN
556
557 l_rcpt_date := TRUNC(SYSDATE);
558
559 END IF;
560
561 IF l_gl_date IS NULL OR
562 l_gl_date = '' THEN
563
564 l_gl_date := TRUNC(SYSDATE);
565
566 END IF;
567 */
568
569 -- check to see if customer is delinquent.
570 -- IF customer IS delinqient THEN
571 -- call Collections process
572 -- END IF;
573
574 -- get default cash app rule for organization ...
575
576 OPEN c_dflt_cash_applic_rule;
577 FETCH c_dflt_cash_applic_rule INTO l_dflt_cat_id
578 ,l_dflt_name
579 ,l_dflt_tolerance
580 ,l_dflt_days_past_quote_valid
581 ,l_dflt_months_to_bill_ahead
582 ,l_dflt_under_payment
583 ,l_dflt_over_payment
584 ,l_dflt_receipt_msmtch;
585 CLOSE c_dflt_cash_applic_rule;
586
587 -- get invoice amount due remaining and invoice currency
588
589 FOR c_open_invs_rec IN c_open_invs (l_cons_bill_num, l_contract_num, l_customer_num, NULL)
590 LOOP
591 l_invoice_currency_code := c_open_invs_rec.currency_code;
592 l_inv_tot := l_inv_tot + c_open_invs_rec.amount_due_remaining; -- changed from remaining to original
593 END LOOP;
594
595 -- IF l_invoice_currency_code <> l_receipt_currency_code AND --bv
596 IF l_functional_currency <> l_receipt_currency_code AND
597 l_currency_conv_type IN ('NONE') THEN
598
599
600 -- Message Text: Please enter a currency type.
601 x_return_status := OKC_API.G_RET_STS_ERROR;
602 OKC_API.set_message( p_app_name => G_APP_NAME,
603 p_msg_name => 'OKL_BPD_PLS_ENT_CUR_TYPE');
604
605 RAISE G_EXCEPTION_HALT_VALIDATION;
606
607 END IF;
608
609 -- IF l_invoice_currency_code = l_receipt_currency_code THEN -- bv
610 IF l_functional_currency = l_receipt_currency_code THEN
611
612 IF l_currency_conv_type IN ('CORPORATE', 'SPOT', 'USER') OR
613 l_currency_conv_rate <> '0' THEN
614
615 -- Message Text: Currency conversion values are not required when the receipt and invoice currency's are the same.
616 x_return_status := OKC_API.G_RET_STS_ERROR;
617 OKC_API.set_message( p_app_name => G_APP_NAME,
618 p_msg_name => 'OKL_BPD_SAME_CURRENCY');
619
620 RAISE G_EXCEPTION_HALT_VALIDATION;
621
622 END IF;
623
624 END IF;
625
626 -- IF l_invoice_currency_code <> l_receipt_currency_code AND -- bv
627 IF l_functional_currency <> l_receipt_currency_code AND
628 l_currency_conv_type NOT IN ('USER') THEN
629
630 IF l_currency_conv_date IS NULL OR l_currency_conv_date = '' THEN
631
632 l_currency_conv_date := trunc(l_rcpt_date);
633
634 END IF;
635
636 IF l_currency_conv_type = 'CORPORATE' THEN
637
638 l_currency_conv_type := 'Corporate';
639 ELSE
640 l_currency_conv_type := 'Spot';
641 END IF;
642
643 l_functional_conversion_rate := okl_accounting_util.get_curr_con_rate( l_receipt_currency_code
644 ,l_functional_currency
645 ,l_currency_conv_date
646 ,l_currency_conv_type
647 );
648
649 l_inverse_conversion_rate := okl_accounting_util.get_curr_con_rate( l_functional_currency
650 ,l_receipt_currency_code
651 ,l_currency_conv_date
652 ,l_currency_conv_type
653 );
654
655
656 IF l_functional_conversion_rate IN (0,-1) THEN
657
658 -- Message Text: No exchange rate defined
659 x_return_status := OKC_API.G_RET_STS_ERROR;
660 OKC_API.set_message( p_app_name => G_APP_NAME,
661 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
662
663 RAISE G_EXCEPTION_HALT_VALIDATION;
664
665 END IF;
666
667 l_currency_conv_rate := l_functional_conversion_rate;
668
669 -- ELSIF l_invoice_currency_code <> l_receipt_currency_code AND --bv
670 ELSIF l_functional_currency <> l_receipt_currency_code AND
671 l_currency_conv_type IN ('USER') THEN
672
673 IF l_currency_conv_rate IS NULL OR l_currency_conv_rate = '0' THEN
674
675 -- Message Text: No exchange rate defined for currency conversion type USER.
676 x_return_status := OKC_API.G_RET_STS_ERROR;
677 OKC_API.set_message( p_app_name => G_APP_NAME,
678 p_msg_name => 'OKL_BPD_USR_RTE_SUPPLIED');
679
680 RAISE G_EXCEPTION_HALT_VALIDATION;
681
682 ELSE
683
684 l_functional_conversion_rate := l_currency_conv_rate;
685 l_inverse_conversion_rate := l_functional_conversion_rate / 1;
686
687 END IF;
688
689 l_currency_conv_type := 'User';
690 l_currency_conv_date := trunc(SYSDATE);
691
692 ELSE
693 -- no currency conversion required
694 l_currency_conv_date := NULL;
695 l_currency_conv_type := NULL;
696 l_currency_conv_rate := NULL;
697
698 END IF;
699
700 -- need to obtain the exchange rate for invoice currency and receipt currency ...
701 IF l_invoice_currency_code <> l_receipt_currency_code THEN
702
703 l_conversion_rate := okl_accounting_util.get_curr_con_rate( l_receipt_currency_code
704 ,l_invoice_currency_code
705 ,trunc(SYSDATE)
706 ,'Corporate'
707 );
708
709
710 IF l_conversion_rate IN (0,-1) THEN
711
712 -- Message Text: No exchange rate defined
713 x_return_status := OKC_API.G_RET_STS_ERROR;
714 OKC_API.set_message( p_app_name => G_APP_NAME,
715 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
716
717 RAISE G_EXCEPTION_HALT_VALIDATION;
718
719 END IF;
720
721 -- convert receipt amount to the transaction currency ...
722 l_converted_receipt_amount := (l_rcpt_amount * l_conversion_rate);
723 l_rcpt_amount := l_converted_receipt_amount;
724
725 END IF;
726
727 -- Check for exceptions
728
729 IF l_rcpt_amount = 0 OR l_rcpt_amount IS NULL THEN
730
731 -- Message Text: The receipt cannot have a value of zero
732 x_return_status := OKC_API.G_RET_STS_ERROR;
733 OKC_API.set_message( p_app_name => G_APP_NAME,
734 p_msg_name => 'OKL_BPD_ZERO_RECEIPT');
735
736 RAISE G_EXCEPTION_HALT_VALIDATION;
737
738 ELSIF l_inv_tot = 0 OR l_inv_tot IS NULL THEN
739
740 -- Message Text: The invoice has an amount of zero
741 x_return_status := OKC_API.G_RET_STS_ERROR;
742 OKC_API.set_message( p_app_name => G_APP_NAME,
743 p_msg_name => 'OKL_BPD_ZERO_INVOICE');
744
745 RAISE G_EXCEPTION_HALT_VALIDATION;
746
747
748 ELSIF l_dflt_tolerance IS NULL OR l_dflt_under_payment IS NULL
749 OR l_dflt_over_payment IS NULL OR l_dflt_receipt_msmtch IS NULL THEN
750
751 -- Message Text: No DEFAULT cash application rule defined
752 x_return_status := OKC_API.G_RET_STS_ERROR;
753 OKC_API.set_message( p_app_name => G_APP_NAME,
754 p_msg_name => 'OKL_BPD_NO_CSH_RLS_DEF');
755
756 RAISE G_EXCEPTION_HALT_VALIDATION;
757
758 ELSIF l_rcpt_amount >= l_inv_tot AND
759 l_create_receipt_flag = 'N' THEN -- allocate receipt
760
761 -- Message Text: No cash allocation required
762 x_return_status := OKC_API.G_RET_STS_ERROR;
763 OKC_API.set_message( p_app_name => G_APP_NAME,
764 p_msg_name => 'OKL_BPD_NO_ALLOC_REQ');
765
766 RAISE G_EXCEPTION_HALT_VALIDATION;
767
768 END IF;
769
770 -- check for duplicate check number for customer as validation has been taken out of the
771 -- tapi. this is for collections.
772
773 IF l_create_receipt_flag <> 'YC' THEN -- for concurrent transactions, dup check number check already done.
774
775 OPEN c_dup_rcpt(l_customer_id, l_check_number, TRUNC(l_rcpt_date));
776 FETCH c_dup_rcpt INTO l_dup_rcpt_flag;
777 CLOSE c_dup_rcpt;
778
779
780 IF l_dup_rcpt_flag = 1 THEN
781
782 -- Message Text: Duplicate receipt number for customer
783 x_return_status := OKC_API.G_RET_STS_ERROR;
784 OKC_API.set_message( p_app_name => G_APP_NAME,
785 p_msg_name => 'OKL_BPD_DUP_RECEIPT');
786
787 RAISE G_EXCEPTION_HALT_VALIDATION;
788
789 END IF;
790
791 END IF;
792
793 -- Create record in Internal Transaction Table.
794
795 -- CREATE HEADER REC
796
797 l_rctv_rec.IRM_ID := l_irm_id;
798 l_rctv_rec.IBA_ID := l_bank_account_id;
799 l_rctv_rec.ILE_ID := l_customer_id;
800 l_rctv_rec.CHECK_NUMBER := l_check_number;
801 l_rctv_rec.AMOUNT := l_rcpt_amount_orig; -- in receipt amount
802 l_rctv_rec.CURRENCY_CODE := l_receipt_currency_code; -- entered currency
803
804 l_rctv_rec.EXCHANGE_RATE := l_currency_conv_rate;
805 l_rctv_rec.EXCHANGE_RATE_TYPE := l_currency_conv_type;
806 l_rctv_rec.EXCHANGE_RATE_DATE := trunc(l_currency_conv_date);
807
808 l_rctv_rec.DATE_EFFECTIVE := trunc(l_rcpt_date);
809 l_rctv_rec.GL_DATE := trunc(l_gl_date);
810 l_rctv_rec.ORG_ID := l_org_id;
811
812 i := 1;
813
814 l_rcav_tbl(i).CNR_ID := l_cons_bill_id;
815 l_rcav_tbl(i).KHR_ID := l_contract_id;
816 -- l_rcav_tbl(i).LLN_ID := l_lln_id; -- consolidated ar lines id
817 -- l_rcav_tbl(i).LSM_ID := l_lsm_id; -- consolidated ar streams id
818 l_rcav_tbl(i).ILE_ID := l_customer_id;
819 l_rcav_tbl(i).AMOUNT := l_rcpt_amount_orig;
820 l_rcav_tbl(i).LINE_NUMBER := i;
821 l_rcav_tbl(i).ORG_ID := l_org_id;
822
823 IF l_create_receipt_flag <> 'YC' THEN -- not needed for concurrent process.
824
825 -- Start of wraper code generated automatically by Debug code generator for Okl_Rct_Pub.create_internal_trans
826 IF(L_DEBUG_ENABLED='Y') THEN
827 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
828 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
829 END IF;
830 IF(IS_DEBUG_PROCEDURE_ON) THEN
831 BEGIN
832 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCAPB.pls call Okl_Rct_Pub.create_internal_trans ');
833 END;
834 END IF;
835 Okl_Rct_Pub.create_internal_trans (l_api_version
836 ,l_init_msg_list
837 ,l_return_status
838 ,l_msg_count
839 ,l_msg_data
840 ,l_rctv_rec
841 ,l_rcav_tbl
842 ,x_rctv_rec
843 ,x_rcav_tbl
844 );
845 IF(IS_DEBUG_PROCEDURE_ON) THEN
846 BEGIN
847 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCAPB.pls call Okl_Rct_Pub.create_internal_trans ');
848 END;
849 END IF;
850 -- End of wraper code generated automatically by Debug code generator for Okl_Rct_Pub.create_internal_trans
851
852 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
853 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
854 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
855 RAISE OKL_API.G_EXCEPTION_ERROR;
856 END IF;
857
858 l_rct_id := x_rctv_rec.ID;
859 l_rca_id := x_rcav_tbl(x_rcav_tbl.FIRST).ID;
860
861 ELSE
862
863 IF l_cons_bill_id IS NOT NULL THEN
864 OPEN c_get_int_id_cons(l_customer_id, l_check_number, l_rcpt_amount_orig, l_cons_bill_id);
865 FETCH c_get_int_id_cons INTO l_rct_id, l_rca_id;
866 CLOSE c_get_int_id_cons;
867 ELSE
868 OPEN c_get_int_id_cont(l_customer_id, l_check_number, l_rcpt_amount_orig, l_contract_id);
869 FETCH c_get_int_id_cont INTO l_rct_id, l_rca_id;
870 CLOSE c_get_int_id_cont;
871 END IF;
872
873 END IF;
874
875 -- Internal Record created.
876 -- **************************************************
877 -- Contract level cash application processing BEGINS
878 -- **************************************************
879
880 IF l_contract_num IS NOT NULL THEN -- (1)
881
882 l_cont_applic := 'Y';
883
884 IF l_contract_id IS NULL THEN
885 OPEN c_get_contract_id(l_contract_num);
886 FETCH c_get_contract_id INTO l_contract_id;
887 CLOSE c_get_contract_id;
888 END IF;
889
890 -- get cash application rule
891 OPEN c_cash_rle_id_csr (l_contract_id);
892 FETCH c_cash_rle_id_csr INTO l_cau_id;
893 CLOSE c_cash_rle_id_csr;
894
895 -- don't do cash application if CAR is 'On Account' -- varao start
896 IF NVL(l_cau_id, 0) = -1 THEN
897 l_over_pay := 'O'; -- Customer's account
898 ELSE -- varao end
899
900 IF l_cau_id IS NOT NULL THEN
901
902 OPEN c_cash_rule_csr (l_cau_id);
903 FETCH c_cash_rule_csr INTO l_cat_id
904 ,l_rule_name
905 ,l_tolerance
906 ,l_days_past_quote_valid
907 ,l_months_to_bill_ahead
908 ,l_under_payment
909 ,l_over_payment
910 ,l_receipt_msmtch;
911 CLOSE c_cash_rule_csr;
912
913 IF l_tolerance IS NULL THEN
914
915 l_rule_name := l_dflt_name;
916 l_cat_id := l_dflt_cat_id;
917 l_tolerance := l_dflt_tolerance;
918 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
919 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
920 l_under_payment := l_dflt_under_payment;
921 l_over_payment := l_dflt_over_payment;
922 l_receipt_msmtch := l_dflt_receipt_msmtch;
923 END IF;
924
925 ELSE -- use default rule
926
927 l_rule_name := l_dflt_name;
928 l_cat_id := l_dflt_cat_id;
929 l_tolerance := l_dflt_tolerance;
930 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
931 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
932 l_under_payment := l_dflt_under_payment;
933 l_over_payment := l_dflt_over_payment;
934 l_receipt_msmtch := l_dflt_receipt_msmtch;
935
936 END IF;
937
938 -- TOLERANCE CHECK
939
940 IF l_inv_tot > l_rcpt_amount THEN
941 l_appl_tolerance := l_inv_tot * (1 - l_tolerance / 100);
942 ELSE
943 l_appl_tolerance := l_inv_tot;
944 END IF;
945
946 IF l_inv_tot > l_rcpt_amount AND l_appl_tolerance > l_rcpt_amount THEN -- UNDERPAYMENT (2)
947
948 IF l_under_payment IN ('T','t') THEN -- ORDERED (3)
949
950 -- i := 1;
951 i := 0; --bv
952
953 l_order_count := 0;
954
955 OPEN c_stream_alloc (l_ordered, l_cat_id, l_contract_id);
956 LOOP
957
958 FETCH c_stream_alloc INTO l_sty_id, l_sequence_number;
959 EXIT WHEN c_stream_alloc%NOTFOUND
960 OR l_rcpt_amount = 0
961 OR l_rcpt_amount IS NULL;
962
963 OPEN c_open_invs_cont (NULL, l_contract_num, l_customer_num, l_sty_id);
964 LOOP
965 FETCH c_open_invs_cont INTO c_open_invs_rec;
966
967 EXIT WHEN c_open_invs_cont%NOTFOUND
968 OR l_rcpt_amount = 0
969 OR l_rcpt_amount IS NULL;
970
971 l_order_count := l_order_count + 1;
972
973 i := i + 1;
974
975 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
976 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
977 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
978 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
979 l_xcav_tbl(i).CAT_ID := l_cat_id;
980
981 IF l_xcav_tbl(i).AMOUNT_APPLIED > l_rcpt_amount THEN
982
983 l_xcav_tbl(i).AMOUNT_APPLIED := l_rcpt_amount;
984
985 l_rcpt_amount := 0;
986
987 ELSE
988
989 l_rcpt_amount := l_rcpt_amount - l_xcav_tbl(i).AMOUNT_APPLIED;
990
991 END IF;
992
993 IF l_receipt_currency_code <> l_invoice_currency_code THEN
994 -- convert back to receipt currency
995 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
996 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
997 END IF;
998
999 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1000 l_xcav_tbl(i).RCA_ID := l_rca_id;
1001 l_xcav_tbl(i).ORG_ID := l_org_id;
1002 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1003 l_xcav_tbl(i).CAT_ID := l_cat_id;
1004
1005 -- i := i + 1; --bv
1006
1007 END LOOP;
1008 CLOSE c_open_invs_cont;
1009
1010 END LOOP;
1011 CLOSE c_stream_alloc;
1012
1013
1014 IF l_order_count = 0 THEN
1015
1016 -- Message Text: No order transaction types for contract
1017 x_return_status := OKC_API.G_RET_STS_ERROR;
1018
1019 OKC_API.set_message( p_app_name => G_APP_NAME
1020 ,p_msg_name => 'OKL_BPD_NO_ORDER_STRMS'
1021 ,p_token1 => 'RULE_NAME'
1022 ,p_token1_value => l_rule_name
1023 ,p_token2 => 'CONTRACT_NUMBER'
1024 ,p_token2_value => l_contract_num);
1025
1026 RAISE G_EXCEPTION_HALT_VALIDATION;
1027
1028 END IF;
1029
1030 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
1031
1032 -- i := 1;
1033 i := 0; --bv
1034
1035 -- obtain all the streams that are part of the pro rate user defined list.
1036
1037 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id, l_contract_id) LOOP
1038
1039
1040 l_sty_id := c_stream_alloc_rec.sty_id;
1041
1042 FOR c_open_invs_rec IN c_open_invs_cont (NULL, l_contract_num, l_customer_num, l_sty_id)
1043 LOOP
1044 i := i + 1; --bv
1045 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1046
1047 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1048 -- convert back to receipt currency
1049 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1050 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1051 END IF;
1052
1053 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1054 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1055 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1056 l_xcav_tbl(i).RCA_ID := l_rca_id;
1057 l_xcav_tbl(i).ORG_ID := l_org_id;
1058 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1059 l_xcav_tbl(i).CAT_ID := l_cat_id;
1060 l_pro_rate_inv_total := l_pro_rate_inv_total + l_xcav_tbl(i).AMOUNT_APPLIED;
1061 -- i := i + 1;
1062
1063 END LOOP; -- c_open_invs
1064
1065 END LOOP; -- c_stream_alloc
1066
1067 -- Calc Pro Ration
1068 -- only if total amount of prorated invoices is greater than receipt
1069
1070 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
1071
1072 -- Message Text: No prorated transaction types for contract
1073 x_return_status := OKC_API.G_RET_STS_ERROR;
1074 OKC_API.set_message( p_app_name => G_APP_NAME
1075 ,p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS'
1076 ,p_token1 => 'RULE_NAME'
1077 ,p_token1_value => l_rule_name
1078 ,p_token2 => 'CONTRACT_NUMBER'
1079 ,p_token2_value => l_contract_num);
1080
1081 RAISE G_EXCEPTION_HALT_VALIDATION;
1082
1083 END IF;
1084
1085 IF (l_pro_rate_inv_total > l_rcpt_amount) THEN
1086 i := l_xcav_tbl.FIRST;
1087 l_temp_val := l_rcpt_amount / l_pro_rate_inv_total;
1088 LOOP
1089 l_xcav_tbl(i).AMOUNT_APPLIED := l_temp_val * l_xcav_tbl(i).AMOUNT_APPLIED;
1090
1091 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1092 -- convert back to receipt currency
1093 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1094 END IF;
1095
1096 EXIT WHEN (i = l_xcav_tbl.LAST);
1097 i := i + 1;
1098 END LOOP;
1099 END IF;
1100
1101 ELSIF l_under_payment IN ('U','u') THEN --(3)
1102
1103 l_over_pay := 'U'; -- UNAPPLIED
1104
1105 END IF; -- (3)
1106
1107 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
1108
1109 -- CREATE LINES TABLE
1110
1111 i := 0;
1112
1113 OPEN c_open_invs_cont (NULL, l_contract_num, l_customer_num, NULL);
1114
1115 LOOP
1116 FETCH c_open_invs_cont INTO c_open_invs_rec;
1117 EXIT WHEN c_open_invs_cont%NOTFOUND
1118 OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
1119
1120 i := i + 1;
1121
1122 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1123 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1124
1125 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1126 -- convert back to receipt currency
1127 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1128 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1129 END IF;
1130
1131 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1132 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1133 l_xcav_tbl(i).RCA_ID := l_rca_id;
1134 l_xcav_tbl(i).ORG_ID := l_org_id;
1135 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1136 l_xcav_tbl(i).CAT_ID := l_cat_id;
1137
1138 IF l_rcpt_amount < l_xcav_tbl(i).AMOUNT_APPLIED THEN
1139 -- TOLERANCE
1140 l_xcav_tbl(i).AMOUNT_APPLIED := l_rcpt_amount;
1141
1142 l_rcpt_amount := 0;
1143 --i := i + 1;
1144
1145 ELSE
1146
1147 l_rcpt_amount := l_rcpt_amount - l_xcav_tbl(i).AMOUNT_APPLIED;
1148 --i := i + 1;
1149
1150 END IF;
1151
1152 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1153 -- convert back to receipt currency
1154 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1155 END IF;
1156
1157 END LOOP;
1158
1159 CLOSE c_open_invs_cont;
1160
1161 END IF; -- under payment.
1162
1163 END IF; -- 'On Account' CAR
1164
1165 ELSIF l_cons_bill_num IS NOT NULL THEN
1166
1167 l_cons_bill_applic := 'Y';
1168
1169 i := 0;
1170
1171 -- ************************************************
1172 -- Check for same start date
1173 -- ************************************************
1174
1175 OPEN c_inv_date(l_cons_bill_num, l_customer_num);
1176 FETCH c_inv_date INTO l_contract_number_start_date, l_start_date, l_contract_number_id;
1177 CLOSE c_inv_date;
1178
1179 d := 0;
1180 FOR c_inv_date_rec IN c_inv_date(l_cons_bill_num, l_customer_num)
1181 LOOP
1182
1183 IF TRUNC(l_start_date) = TRUNC(c_inv_date_rec.start_date) THEN
1184 l_same_date := 'Y';
1185 d := d + 1;
1186 ELSE
1187 l_same_date := 'N';
1188 EXIT;
1189 END IF;
1190
1191 END LOOP;
1192
1193 IF d = 1 THEN
1194 l_same_date := 'N';
1195 END IF;
1196
1197 -- ************************************************
1198 -- Check for same cash application rule
1199 -- ************************************************
1200
1201 OPEN c_cash_rle_id_csr (l_contract_number_id);
1202 FETCH c_cash_rle_id_csr INTO l_cau_id;
1203 CLOSE c_cash_rle_id_csr;
1204
1205 d := 0;
1206 FOR c_inv_date_rec IN c_inv_date(l_cons_bill_num, l_customer_num)
1207 LOOP
1208
1209 l_check_cau_id := NULL;
1210
1211 OPEN c_cash_rle_id_csr (c_inv_date_rec.contract_id);
1212 FETCH c_cash_rle_id_csr INTO l_check_cau_id;
1213 CLOSE c_cash_rle_id_csr;
1214
1215 IF l_check_cau_id IS NULL THEN
1216 l_same_cash_app_rule := 'N';
1217 EXIT;
1218 END IF;
1219
1220 IF l_cau_id = l_check_cau_id THEN
1221 l_same_cash_app_rule := 'Y';
1222 d := d + 1;
1223 ELSE
1224 l_same_cash_app_rule := 'N';
1225 EXIT;
1226 END IF;
1227
1228 END LOOP;
1229
1230 IF d = 1 THEN
1231 l_same_cash_app_rule := 'N';
1232 END IF;
1233
1234 IF l_same_date = 'Y' AND l_same_cash_app_rule = 'Y' THEN
1235
1236 -- don't do cash application if CAR is 'On Account' -- varao start
1237 IF NVL(l_cau_id, 0) = -1 THEN
1238 l_over_pay := 'O'; -- Customer's account
1239 ELSE -- varao end
1240
1241 -- *******************************************************
1242 -- Start stream level cash application using default cash
1243 -- application rule for all
1244 -- *******************************************************
1245
1246 l_rule_name := l_dflt_name;
1247 l_cat_id := l_dflt_cat_id;
1248 l_tolerance := l_dflt_tolerance;
1249 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1250 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1251 l_under_payment := l_dflt_under_payment;
1252 l_over_payment := l_dflt_over_payment;
1253 l_receipt_msmtch := l_dflt_receipt_msmtch;
1254
1255
1256 -- ************************************************
1257 -- Stream level cash application processing BEGINS
1258 -- ************************************************
1259
1260 -- get stream total
1261
1262 l_stream_tot := 0;
1263 FOR c_open_invs_rec IN c_open_invs (l_cons_bill_num, NULL, l_customer_num, NULL)
1264 LOOP
1265 l_invoice_currency_code := c_open_invs_rec.currency_code;
1266 l_stream_tot := l_stream_tot + c_open_invs_rec.amount_due_remaining;
1267
1268 -- changed from remaining to original
1269 END LOOP;
1270
1271 -- calculate tolerance
1272 IF l_stream_tot > l_rcpt_amount THEN
1273 l_appl_tolerance := l_stream_tot * (1 - l_tolerance / 100);
1274 ELSE
1275 l_appl_tolerance := l_stream_tot;
1276 END IF;
1277
1278 IF l_stream_tot > l_rcpt_amount AND l_appl_tolerance > l_rcpt_amount THEN -- UNDERPAYMENT (2)
1279
1280 IF l_under_payment IN ('T','t') THEN -- ORDERED (3)
1281
1282 l_order_count := 0;
1283
1284 OPEN c_stream_alloc (l_ordered, l_cat_id, l_contract_number_id);
1285 LOOP
1286 FETCH c_stream_alloc INTO l_sty_id, l_sequence_number;
1287 EXIT WHEN c_stream_alloc%NOTFOUND
1288 OR l_rcpt_amount = 0
1289 OR l_rcpt_amount IS NULL;
1290
1291 OPEN c_open_invs (l_cons_bill_num, NULL, l_customer_num, l_sty_id);
1292 LOOP
1293
1294 FETCH c_open_invs INTO c_open_invs_rec;
1295
1296 EXIT WHEN c_open_invs%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
1297
1298 i := i + 1;
1299
1300 l_order_count := l_order_count + 1;
1301
1302 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1303 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1304 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1305 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1306 l_xcav_tbl(i).CAT_ID := l_cat_id;
1307
1308 IF l_xcav_tbl(i).AMOUNT_APPLIED > l_rcpt_amount THEN
1309
1310 l_xcav_tbl(i).AMOUNT_APPLIED := l_rcpt_amount;
1311
1312 l_rcpt_amount := 0;
1313
1314 ELSE
1315
1316 l_rcpt_amount := l_rcpt_amount - l_xcav_tbl(i).AMOUNT_APPLIED;
1317
1318 END IF;
1319
1320
1321 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1322 -- convert back to receipt currency
1323 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1324 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1325 END IF;
1326
1327 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1328 l_xcav_tbl(i).RCA_ID := l_rca_id;
1329 l_xcav_tbl(i).ORG_ID := l_org_id;
1330 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1331 l_xcav_tbl(i).CAT_ID := l_cat_id;
1332
1333 -- i := i + 1;
1334
1335 END LOOP;
1336 CLOSE c_open_invs;
1337
1338 END LOOP;
1339 CLOSE c_stream_alloc;
1340
1341 IF l_order_count = 0 THEN
1342
1343 -- Message Text: No prorated transaction types for rule.
1344 x_return_status := OKC_API.G_RET_STS_ERROR;
1345
1346 OKC_API.set_message( p_app_name => G_APP_NAME
1347 ,p_msg_name => 'OKL_BPD_DEF_NO_ORD'
1348 );
1349
1350 RAISE G_EXCEPTION_HALT_VALIDATION;
1351
1352 END IF;
1353
1354 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
1355
1356 l_first_prorate_rec := i + 1;
1357
1358 -- i := 1;
1359 -- obtain all the streams that are part of the pro rate default rule.
1360
1361 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id, l_contract_number_id)
1362 LOOP
1363
1364 l_sty_id := c_stream_alloc_rec.sty_id;
1365 FOR c_open_invs_rec IN c_open_invs ( l_cons_bill_num
1366 ,NULL
1367 ,l_customer_num
1368 ,l_sty_id
1369 )
1370 LOOP
1371
1372 i := i + 1;
1373
1374 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1375
1376 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1377 -- convert back to receipt currency
1378 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1379 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1380 END IF;
1381
1382 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1383 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1384 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1385 l_xcav_tbl(i).RCA_ID := l_rca_id;
1386 l_xcav_tbl(i).ORG_ID := l_org_id;
1387 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1388 l_xcav_tbl(i).CAT_ID := l_cat_id;
1389
1390 l_pro_rate_inv_total := l_pro_rate_inv_total + l_xcav_tbl(i).AMOUNT_APPLIED;
1391 -- i := i + 1;
1392
1393 END LOOP; -- c_open_invs
1394
1395 END LOOP; -- c_stream_alloc
1396
1397 -- Calc Pro Ration
1398 -- only if total amount of prorated invoices is greater than receipt
1399
1400 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
1401
1402 -- Message Text: No prorated transaction types for contract.
1403 x_return_status := OKC_API.G_RET_STS_ERROR;
1404
1405 OKC_API.set_message( p_app_name => G_APP_NAME
1406 ,p_msg_name => 'OKL_BPD_DEF_NO_PRO'
1407 );
1408
1409 RAISE G_EXCEPTION_HALT_VALIDATION;
1410
1411 END IF;
1412
1413 IF (l_pro_rate_inv_total > l_rcpt_amount) THEN
1414 i := l_first_prorate_rec;
1415 l_temp_val := l_rcpt_amount / l_pro_rate_inv_total;
1416
1417 l_rcpt_amount := 0;
1418
1419 LOOP
1420 l_xcav_tbl(i).AMOUNT_APPLIED := l_temp_val * l_xcav_tbl(i).AMOUNT_APPLIED;
1421
1422 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1423 -- convert back to receipt currency
1424 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1425 END IF;
1426
1427 EXIT WHEN (i = l_xcav_tbl.LAST);
1428 i := i + 1;
1429 END LOOP;
1430
1431 ELSE -- added by BV
1432 -- Message Text: No prorated transaction types for contract
1433 x_return_status := OKC_API.G_RET_STS_ERROR;
1434
1435 OKC_API.set_message( p_app_name => G_APP_NAME
1436 ,p_msg_name => 'OKL_BPD_DEF_NO_PRO'
1437 );
1438
1439 RAISE G_EXCEPTION_HALT_VALIDATION;
1440
1441 END IF;
1442
1443 ELSIF l_under_payment IN ('U','u') THEN --(3)
1444
1445 l_over_pay := 'U'; -- UNAPPLIED
1446
1447 END IF; -- (3)
1448
1449 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
1450
1451 -- CREATE LINES TABLE
1452
1453 -- i := 1;
1454
1455 OPEN c_open_invs (l_cons_bill_num, NULL, l_customer_num, NULL);
1456
1457 LOOP
1458
1459 FETCH c_open_invs INTO c_open_invs_rec;
1460 EXIT WHEN c_open_invs%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
1461
1462 i := i + 1;
1463
1464 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1465 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1466
1467 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1468 -- convert back to receipt currency
1469 l_xcav_tbl(i).AMOUNT_APPLIED_FROM :=
1470 l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1471 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1472 END IF;
1473
1474 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1475 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1476 l_xcav_tbl(i).RCA_ID := l_rca_id;
1477 l_xcav_tbl(i).ORG_ID := l_org_id;
1478 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1479 l_xcav_tbl(i).CAT_ID := l_cat_id;
1480
1481
1482 IF l_rcpt_amount < l_xcav_tbl(i).AMOUNT_APPLIED THEN
1483 -- TOLERANCE
1484 l_xcav_tbl(i).AMOUNT_APPLIED := l_rcpt_amount;
1485
1486 l_rcpt_amount := 0;
1487 --i := i + 1;
1488
1489 ELSE
1490 l_rcpt_amount := l_rcpt_amount - l_xcav_tbl(i).AMOUNT_APPLIED;
1491 --i := i + 1;
1492
1493 END IF;
1494
1495 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1496 -- convert back to receipt currency
1497 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1498 END IF;
1499
1500 END LOOP;
1501
1502 CLOSE c_open_invs;
1503
1504 END IF; -- under payment.
1505
1506 END IF; -- 'On Account' CAR
1507
1508 -- **********************************************
1509 -- Stream level cash application processing ENDS
1510 -- **********************************************
1511
1512 ELSE
1513
1514 -- ******************************************************
1515 -- Per/Contract level cash application processing BEGINS
1516 -- ******************************************************
1517
1518
1519 OPEN c_get_contract_num(l_cons_bill_num);
1520 LOOP
1521
1522 FETCH c_get_contract_num INTO l_contract_id, l_contract_num, l_contract_start_date;
1523 EXIT WHEN c_get_contract_num%NOTFOUND
1524 OR l_rcpt_amount = 0
1525 OR l_rcpt_amount IS NULL;
1526
1527 IF l_last_contract_id <> l_contract_id THEN
1528
1529 l_last_contract_id := l_contract_id;
1530
1531 IF l_contract_num IS NOT NULL THEN
1532
1533 OPEN c_cash_rle_id_csr (l_contract_id);
1534 FETCH c_cash_rle_id_csr INTO l_cau_id;
1535 CLOSE c_cash_rle_id_csr;
1536
1537 -- don't do cash application if CAR is 'On Account' -- varao start
1538 IF NVL(l_cau_id, 0) = -1 THEN
1539 l_over_pay := 'O'; -- Customer's account
1540 ELSE -- varao end
1541
1542 -- IF l_cau_id IS NOT NULL AND l_same_date = 'N' THEN -- removed by bv
1543 IF l_cau_id IS NOT NULL THEN
1544
1545 OPEN c_cash_rule_csr (l_cau_id);
1546 FETCH c_cash_rule_csr INTO l_cat_id
1547 ,l_rule_name
1548 ,l_tolerance
1549 ,l_days_past_quote_valid
1550 ,l_months_to_bill_ahead
1551 ,l_under_payment
1552 ,l_over_payment
1553 ,l_receipt_msmtch;
1554 CLOSE c_cash_rule_csr;
1555
1556 IF l_tolerance IS NULL THEN
1557 l_rule_name := l_dflt_name;
1558 l_cat_id := l_dflt_cat_id;
1559 l_tolerance := l_dflt_tolerance;
1560 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1561 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1562 l_under_payment := l_dflt_under_payment;
1563 l_over_payment := l_dflt_over_payment;
1564 l_receipt_msmtch := l_dflt_receipt_msmtch;
1565 END IF;
1566
1567 ELSE -- use default rule
1568
1569 l_rule_name := l_dflt_name;
1570 l_cat_id := l_dflt_cat_id;
1571 l_tolerance := l_dflt_tolerance;
1572 l_days_past_quote_valid := l_dflt_days_past_quote_valid;
1573 l_months_to_bill_ahead := l_dflt_months_to_bill_ahead;
1574 l_under_payment := l_dflt_under_payment;
1575 l_over_payment := l_dflt_over_payment;
1576 l_receipt_msmtch := l_dflt_receipt_msmtch;
1577
1578 END IF;
1579
1580 -- CASH APPLICATION ON CONTRACT PER CONTRACT BASIS --
1581
1582 -- get contract total
1583 l_cont_tot := 0;
1584 FOR c_open_invs_rec IN c_open_invs (l_cons_bill_num, l_contract_num, l_customer_num, NULL)
1585 LOOP
1586 l_invoice_currency_code := c_open_invs_rec.currency_code;
1587 l_cont_tot := l_cont_tot + c_open_invs_rec.amount_due_remaining;
1588 -- changed from remaining to original
1589 END LOOP;
1590
1591 -- calculate tolerance
1592 IF l_cont_tot > l_rcpt_amount THEN
1593 l_appl_tolerance := l_cont_tot * (1 - l_tolerance / 100);
1594 ELSE
1595 l_appl_tolerance := l_cont_tot;
1596 END IF;
1597
1598 -- **************************************************
1599 -- Contract level cash application processing begins.
1600 -- **************************************************
1601
1602 IF l_cont_tot > l_rcpt_amount AND l_appl_tolerance > l_rcpt_amount THEN -- UNDERPAYMENT (2)
1603
1604 IF l_under_payment IN ('T','t') THEN -- ORDERED (3)
1605
1606 l_order_count := 0;
1607
1608 OPEN c_stream_alloc (l_ordered, l_cat_id, l_contract_id);
1609 LOOP
1610
1611 FETCH c_stream_alloc INTO l_sty_id, l_sequence_number;
1612 EXIT WHEN c_stream_alloc%NOTFOUND
1613 OR l_rcpt_amount = 0
1614 OR l_rcpt_amount IS NULL;
1615
1616 OPEN c_open_invs (l_cons_bill_num, l_contract_num, l_customer_num, l_sty_id);
1617 LOOP
1618
1619 FETCH c_open_invs INTO c_open_invs_rec;
1620
1621 EXIT WHEN c_open_invs%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
1622
1623 i := i + 1;
1624
1625 l_order_count := l_order_count + 1;
1626
1627 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1628 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1629 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1630 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1631 l_xcav_tbl(i).CAT_ID := l_cat_id;
1632
1633 IF l_xcav_tbl(i).AMOUNT_APPLIED > l_rcpt_amount THEN
1634
1635 l_xcav_tbl(i).AMOUNT_APPLIED := l_rcpt_amount;
1636
1637 l_rcpt_amount := 0;
1638
1639 ELSE
1640
1641 l_rcpt_amount := l_rcpt_amount - l_xcav_tbl(i).AMOUNT_APPLIED;
1642
1643 END IF;
1644
1645 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1646 -- convert back to receipt currency
1647 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1648 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1649 END IF;
1650
1651 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1652 l_xcav_tbl(i).RCA_ID := l_rca_id;
1653 l_xcav_tbl(i).ORG_ID := l_org_id;
1654 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1655 l_xcav_tbl(i).CAT_ID := l_cat_id;
1656
1657 -- i := i + 1;
1658
1659 END LOOP;
1660 CLOSE c_open_invs;
1661
1662 END LOOP;
1663 CLOSE c_stream_alloc;
1664
1665 IF l_order_count = 0 THEN
1666
1667 -- Message Text: No prorated transaction types for contract.
1668 x_return_status := OKC_API.G_RET_STS_ERROR;
1669
1670 OKC_API.set_message( p_app_name => G_APP_NAME
1671 ,p_msg_name => 'OKL_BPD_NO_ORDER_STRMS'
1672 ,p_token1 => 'RULE_NAME'
1673 ,p_token1_value => l_rule_name
1674 ,p_token2 => 'CONTRACT_NUMBER'
1675 ,p_token2_value => l_contract_num);
1676
1677 RAISE G_EXCEPTION_HALT_VALIDATION;
1678
1679 END IF;
1680
1681 ELSIF l_under_payment IN ('P','p') THEN -- PRO RATE (3)
1682
1683 l_first_prorate_rec := i + 1;
1684 -- i := 1;
1685
1686 -- obtain all the streams that are part of the pro rate user defined list.
1687
1688 FOR c_stream_alloc_rec IN c_stream_alloc (l_prorate, l_cat_id, l_contract_id)
1689 LOOP
1690
1691 l_sty_id := c_stream_alloc_rec.sty_id;
1692
1693 FOR c_open_invs_rec IN c_open_invs ( l_cons_bill_num
1694 ,l_contract_num
1695 ,l_customer_num
1696 ,l_sty_id
1697 )
1698 LOOP
1699
1700 i := i + 1;
1701
1702 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1703
1704 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1705 -- convert back to receipt currency
1706 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1707 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1708 END IF;
1709
1710 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1711 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1712 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1713 l_xcav_tbl(i).RCA_ID := l_rca_id;
1714 l_xcav_tbl(i).ORG_ID := l_org_id;
1715 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1716 l_xcav_tbl(i).CAT_ID := l_cat_id;
1717
1718 l_pro_rate_inv_total := l_pro_rate_inv_total + l_xcav_tbl(i).AMOUNT_APPLIED;
1719
1720 -- i := i + 1;
1721
1722 END LOOP; -- c_open_invs
1723
1724 END LOOP; -- c_stream_alloc
1725
1726 -- Calc Pro Ration
1727 -- only if total amount of prorated invoices is greater than receipt
1728
1729 IF l_pro_rate_inv_total IS NULL OR l_pro_rate_inv_total = 0 THEN
1730
1731 -- Message Text: No prorated transaction types for contract.
1732 x_return_status := OKC_API.G_RET_STS_ERROR;
1733
1734 OKC_API.set_message( p_app_name => G_APP_NAME
1735 ,p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS'
1736 ,p_token1 => 'RULE_NAME'
1737 ,p_token1_value => l_rule_name
1738 ,p_token2 => 'CONTRACT_NUMBER'
1739 ,p_token2_value => l_contract_num);
1740
1741
1742 RAISE G_EXCEPTION_HALT_VALIDATION;
1743
1744 END IF;
1745
1746 IF (l_pro_rate_inv_total > l_rcpt_amount) THEN
1747 i := l_first_prorate_rec;
1748 l_temp_val := l_rcpt_amount / l_pro_rate_inv_total;
1749
1750 l_rcpt_amount := 0;
1751
1752 LOOP
1753 l_xcav_tbl(i).AMOUNT_APPLIED := l_temp_val * l_xcav_tbl(i).AMOUNT_APPLIED;
1754
1755 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1756 -- convert back to receipt currency
1757 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1758 END IF;
1759
1760 EXIT WHEN (i = l_xcav_tbl.LAST);
1761 i := i + 1;
1762 END LOOP;
1763
1764 ELSE -- added by BV
1765 -- Message Text: No prorated transaction types for contract
1766 x_return_status := OKC_API.G_RET_STS_ERROR;
1767
1768 OKC_API.set_message( p_app_name => G_APP_NAME
1769 ,p_msg_name => 'OKL_BPD_NO_PRORATED_STRMS'
1770 ,p_token1 => 'RULE_NAME'
1771 ,p_token1_value => l_rule_name
1772 ,p_token2 => 'CONTRACT_NUMBER'
1773 ,p_token2_value => l_contract_num);
1774
1775 RAISE G_EXCEPTION_HALT_VALIDATION;
1776
1777 END IF;
1778
1779 ELSIF l_under_payment IN ('U','u') THEN --(3)
1780
1781 l_over_pay := 'U'; -- UNAPPLIED
1782
1783 END IF; -- (3)
1784
1785 ELSE -- EXACT or OVERPAYMENT or TOLERANCE (2)
1786
1787 -- CREATE LINES TABLE
1788
1789 -- i := 1;
1790
1791 OPEN c_open_invs (l_cons_bill_num, l_contract_num, l_customer_num, NULL);
1792
1793 LOOP
1794
1795 FETCH c_open_invs INTO c_open_invs_rec;
1796 EXIT WHEN c_open_invs%NOTFOUND OR l_rcpt_amount = 0 OR l_rcpt_amount IS NULL;
1797
1798 i := i + 1;
1799
1800 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1801 l_xcav_tbl(i).AMOUNT_APPLIED := c_open_invs_rec.amount_due_remaining;
1802
1803 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1804 -- convert back to receipt currency
1805 l_xcav_tbl(i).AMOUNT_APPLIED_FROM :=
1806 l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1807 l_xcav_tbl(i).TRANS_TO_RECEIPT_RATE := l_conversion_rate;
1808 END IF;
1809
1810 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1811 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1812 l_xcav_tbl(i).RCA_ID := l_rca_id;
1813 l_xcav_tbl(i).ORG_ID := l_org_id;
1814 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1815 l_xcav_tbl(i).CAT_ID := l_cat_id;
1816
1817
1818 IF l_rcpt_amount < l_xcav_tbl(i).AMOUNT_APPLIED THEN
1819 -- TOLERANCE
1820 l_xcav_tbl(i).AMOUNT_APPLIED := l_rcpt_amount;
1821
1822 l_rcpt_amount := 0;
1823 --i := i + 1;
1824
1825 ELSE
1826
1827 l_rcpt_amount := l_rcpt_amount - l_xcav_tbl(i).AMOUNT_APPLIED;
1828 --i := i + 1;
1829
1830 END IF;
1831
1832 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1833 -- convert back to receipt currency
1834 l_xcav_tbl(i).AMOUNT_APPLIED_FROM := l_xcav_tbl(i).AMOUNT_APPLIED / l_conversion_rate;
1835 END IF;
1836
1837 END LOOP;
1838
1839 CLOSE c_open_invs;
1840
1841 END IF; -- under payment.
1842
1843 END IF; -- 'On Account' CAR
1844
1845 -- ****************************************************
1846 -- Per/Contract level cash application processing ENDS
1847 -- ****************************************************
1848
1849 END IF;
1850
1851 ELSE
1852
1853 NULL; -- duplicate contract number from cursor ...
1854
1855 END IF; -- end
1856
1857 END LOOP;
1858 CLOSE c_get_contract_num;
1859
1860 END IF;
1861
1862 END IF; -- for cons bill and contract.
1863
1864 -- OVERPAYMENT
1865 IF l_rcpt_amount > 0 THEN -- OVERPAYMENT
1866
1867 IF l_over_payment IN ('M','m') THEN
1868
1869 l_over_pay := 'U'; -- UNAPPLIED;
1870 -- just create money against customer and thats it...
1871
1872 ELSIF l_over_payment IN ('B','b') THEN
1873
1874 l_over_pay := 'O'; -- CUSTOMERS ACCOUNT
1875 -- apply money to customers account...
1876
1877 ELSIF l_over_payment IN ('F','f') THEN
1878
1879 -- KICK OFF PROCESS FOR FUTURE AMOUNTS DUE
1880 l_over_pay := 'O'; -- CUSTOMERS ACCOUNT
1881
1882 END IF;
1883
1884 END IF;
1885
1886 -- CREATE HEADER REC
1887
1888 -- obtain remittance bank details.
1889 OPEN c_get_remit_bnk_dtls(l_irm_id);
1890 FETCH c_get_remit_bnk_dtls INTO
1891 l_xcrv_rec.REMITTANCE_BANK_NAME
1892 ,l_xcrv_rec.ACCOUNT;
1893 CLOSE c_get_remit_bnk_dtls;
1894
1895 l_xcrv_rec.RCT_ID := l_rct_id;
1896
1897 IF l_check_number IS NULL THEN
1898 l_xcrv_rec.CHECK_NUMBER := to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS');
1899 ELSE
1900 l_xcrv_rec.CHECK_NUMBER := l_check_number;
1901 END IF;
1902
1903 l_xcrv_rec.RECEIPT_METHOD := NULL; -- prefer IRM_ID !
1904 l_xcrv_rec.RECEIPT_DATE := trunc(l_rcpt_date);
1905 l_xcrv_rec.GL_DATE := trunc(l_gl_date);
1906 l_xcrv_rec.CURRENCY_CODE := l_invoice_currency_code;
1907 -- store the functional currency at header lvl
1908
1909
1910 IF l_receipt_currency_code <> l_functional_currency THEN
1911
1912 l_xcrv_rec.EXCHANGE_RATE_TYPE := l_currency_conv_type;
1913 l_xcrv_rec.EXCHANGE_RATE_DATE := l_currency_conv_date;
1914 l_xcrv_rec.ATTRIBUTE1 := l_functional_conversion_rate;
1915 -- in functional currency ...
1916 END IF;
1917
1918 IF l_receipt_currency_code <> l_invoice_currency_code THEN
1919 l_xcrv_rec.EXCHANGE_RATE := l_conversion_rate;
1920 l_xcrv_rec.REMITTANCE_AMOUNT := l_converted_receipt_amount;
1921 -- in transaction currency ...
1922 ELSE
1923 l_xcrv_rec.REMITTANCE_AMOUNT := l_rcpt_amount_orig;
1924 -- in receipt currency ...
1925 END IF;
1926
1927 l_xcrv_rec.CUSTOMER_NUMBER := l_customer_num;
1928 l_xcrv_rec.COMMENTS := l_comments;
1929 l_xcrv_rec.ORG_ID := l_org_id;
1930
1931 -- LINES TABLE ALREADY BUILT
1932
1933 -- BUT WE ALSO WANT TO STORE ALL LINES THAT DID NOT GET ANY MONEY APPLIED ....
1934
1935 t_xcav_tbl := l_xcav_tbl;
1936
1937 --- open for a second time without sty_id restriction ...
1938
1939 IF l_cont_applic = 'Y' AND l_create_receipt_flag NOT IN ('Y','YC') THEN
1940
1941 OPEN c_open_invs_cont (NULL, l_contract_num, l_customer_num, NULL);
1942 LOOP
1943
1944 FETCH c_open_invs_cont INTO c_open_invs_rec;
1945 EXIT WHEN c_open_invs_cont%NOTFOUND;
1946
1947 l_cash_applied_flag := 'N';
1948
1949 IF t_xcav_tbl.COUNT > 0 THEN -- varao
1950
1951 t := t_xcav_tbl.FIRST;
1952
1953 LOOP
1954
1955 IF c_open_invs_rec.stream_id = t_xcav_tbl(t).LSM_ID THEN
1956 l_cash_applied_flag := 'Y';
1957 EXIT;
1958 END IF;
1959
1960 EXIT WHEN (t = t_xcav_tbl.LAST);
1961 t := t + 1;
1962
1963 END LOOP;
1964
1965 END IF;
1966
1967 IF l_cash_applied_flag = 'N' THEN -- not part of cash application ....
1968
1969 i := i + 1;
1970
1971 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
1972 l_xcav_tbl(i).AMOUNT_APPLIED := 0;
1973 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
1974 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
1975
1976 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
1977 l_xcav_tbl(i).RCA_ID := l_rca_id;
1978 l_xcav_tbl(i).ORG_ID := l_org_id;
1979
1980 END IF;
1981
1982 END LOOP;
1983 CLOSE c_open_invs_cont;
1984
1985 ELSIF l_cons_bill_applic = 'Y' AND l_create_receipt_flag NOT IN ('Y','YC') THEN
1986
1987 OPEN c_open_invs (l_cons_bill_num, NULL, l_customer_num, NULL);
1988 LOOP
1989
1990 FETCH c_open_invs INTO c_open_invs_rec;
1991 EXIT WHEN c_open_invs%NOTFOUND;
1992
1993 l_cash_applied_flag := 'N';
1994
1995 IF t_xcav_tbl.COUNT > 0 THEN -- varao
1996
1997 t := t_xcav_tbl.FIRST;
1998
1999 LOOP
2000
2001 IF c_open_invs_rec.stream_id = t_xcav_tbl(t).LSM_ID THEN
2002 l_cash_applied_flag := 'Y';
2003 EXIT;
2004 END IF;
2005
2006 EXIT WHEN (t = t_xcav_tbl.LAST);
2007 t := t + 1;
2008
2009 END LOOP;
2010
2011 END IF;
2012
2013 IF l_cash_applied_flag = 'N' THEN -- not part of cash application ....
2014
2015 i := i + 1;
2016
2017 l_xcav_tbl(i).INVOICE_NUMBER := c_open_invs_rec.receivables_invoice_number;
2018 l_xcav_tbl(i).AMOUNT_APPLIED := 0;
2019 l_xcav_tbl(i).LSM_ID := c_open_invs_rec.stream_id;
2020 l_xcav_tbl(i).TRX_DATE := c_open_invs_rec.trx_date;
2021
2022 l_xcav_tbl(i).INVOICE_CURRENCY_CODE := l_receipt_currency_code;
2023 l_xcav_tbl(i).RCA_ID := l_rca_id;
2024 l_xcav_tbl(i).ORG_ID := l_org_id;
2025
2026 END IF;
2027
2028 END LOOP;
2029 CLOSE c_open_invs;
2030
2031 END IF;
2032 ---
2033
2034 -- Start of wraper code generated automatically by Debug code generator for Okl_Xcr_Pub.create_ext_ar_txns
2035 IF(IS_DEBUG_PROCEDURE_ON) THEN
2036 BEGIN
2037 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCAPB.pls call Okl_Xcr_Pub.create_ext_ar_txns ');
2038 END;
2039 END IF;
2040
2041 Okl_Xcr_Pub.create_ext_ar_txns ( l_api_version
2042 ,l_init_msg_list
2043 ,l_return_status
2044 ,l_msg_count
2045 ,l_msg_data
2046 ,l_xcrv_rec
2047 ,l_xcav_tbl
2048 ,x_xcrv_rec
2049 ,x_xcav_tbl
2050 );
2051
2052 IF(IS_DEBUG_PROCEDURE_ON) THEN
2053 BEGIN
2054 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCAPB.pls call Okl_Xcr_Pub.create_ext_ar_txns ');
2055 END;
2056 END IF;
2057 -- End of wraper code generated automatically by Debug code generator for Okl_Xcr_Pub.create_ext_ar_txns
2058
2059 x_return_status := l_return_status;
2060 x_msg_data := l_msg_data;
2061 x_msg_count := l_msg_count;
2062
2063 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2064 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2065 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2066 RAISE OKL_API.G_EXCEPTION_ERROR;
2067 END IF;
2068
2069 -- CREATE RECEIPT IN AR ONCE EVERYTHING IS OKAY AT THIS POINT
2070
2071 IF l_create_receipt_flag IN ('Y','YC') THEN
2072
2073 IF l_rctv_rec.IBA_ID IS NULL THEN
2074
2075 okl_cash_receipt.CASH_RECEIPT (p_api_version => l_api_version
2076 ,p_init_msg_list => l_init_msg_list
2077 ,x_return_status => l_return_status
2078 ,x_msg_count => l_msg_count
2079 ,x_msg_data => l_msg_data
2080 ,p_over_pay => l_over_pay
2081 ,p_conc_proc => l_conc_proc
2082 ,p_xcrv_rec => l_xcrv_rec
2083 ,p_xcav_tbl => l_xcav_tbl
2084 ,x_cash_receipt_id => l_cash_receipt_id
2085 );
2086
2087 ELSE
2088
2089
2090 okl_cash_receipt.PAYMENT_RECEIPT (p_api_version => l_api_version
2091 ,p_init_msg_list => l_init_msg_list
2092 ,x_return_status => l_return_status
2093 ,x_msg_count => l_msg_count
2094 ,x_msg_data => l_msg_data
2095 ,p_over_pay => l_over_pay
2096 ,p_conc_proc => 'N'
2097 ,p_xcrv_rec => l_xcrv_rec
2098 ,p_xcav_tbl => l_xcav_tbl
2099 ,x_cash_receipt_id => l_cash_receipt_id
2100 );
2101
2102 END IF;
2103
2104 x_return_status := l_return_status;
2105
2106 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2107
2108 -- Message Text: Error creating receipt in AR
2109 x_return_status := OKC_API.G_RET_STS_ERROR;
2110 OKC_API.set_message( p_app_name => G_APP_NAME,
2111 p_msg_name => 'OKL_BPD_ERR_CRT_RCT_AR');
2112
2113 RAISE G_EXCEPTION_HALT_VALIDATION;
2114
2115 END IF;
2116
2117 -- UPDATE EXT HEADER WITH CASH RECEIPT ID
2118
2119 SELECT ID INTO l_xcr_id
2120 FROM okl_ext_csh_rcpts_b
2121 WHERE rct_id = l_rct_id;
2122
2123 l_xcrv_rec.id := l_xcr_id;
2124 l_xcrv_rec.icr_id := l_cash_receipt_id;
2125 l_xcrv_rec.attribute1 := NULL;
2126
2127 -- Start of wraper code generated automatically by Debug code generator for Okl_Xcr_Pub.update_ext_csh_txns
2128 IF(IS_DEBUG_PROCEDURE_ON) THEN
2129 BEGIN
2130 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCAPB.pls call Okl_Xcr_Pub.update_ext_csh_txns ');
2131 END;
2132 END IF;
2133 Okl_Xcr_Pub.update_ext_csh_txns( p_api_version
2134 ,p_init_msg_list
2135 ,x_return_status
2136 ,x_msg_count
2137 ,x_msg_data
2138 ,l_xcrv_rec
2139 ,x_xcrv_rec
2140 );
2141 IF(IS_DEBUG_PROCEDURE_ON) THEN
2142 BEGIN
2143 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCAPB.pls call Okl_Xcr_Pub.update_ext_csh_txns ');
2144 END;
2145 END IF;
2146 -- End of wraper code generated automatically by Debug code generator for Okl_Xcr_Pub.update_ext_csh_txns
2147
2148 x_return_status := l_return_status;
2149 x_msg_data := l_msg_data;
2150 x_msg_count := l_msg_count;
2151
2152 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2153 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2154 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2155 RAISE OKL_API.G_EXCEPTION_ERROR;
2156 END IF;
2157
2158 END IF;
2159
2160 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2161
2162 x_return_status := l_return_status;
2163 x_msg_data := l_msg_data;
2164 x_msg_count := l_msg_count;
2165
2166 EXCEPTION
2167
2168 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2169 x_return_status := OKC_API.G_RET_STS_ERROR;
2170
2171
2172 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2173 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2174 (
2175 l_api_name,
2176 G_PKG_NAME,
2177 'OKC_API.G_RET_STS_ERROR',
2178 x_msg_count,
2179 x_msg_data,
2180 '_PVT'
2181 );
2182
2183 WHEN OTHERS THEN
2184 Okl_api.set_message( p_app_name => g_app_name
2185 , p_msg_name => g_unexpected_error
2186 , p_token1 => g_sqlcode_token
2187 , p_token1_value => SQLCODE
2188 , p_token2 => g_sqlerrm_token
2189 , p_token2_value => SQLERRM
2190 ) ;
2191
2192 END handle_manual_pay;
2193
2194 PROCEDURE create_manual_receipt ( p_api_version IN NUMBER
2195 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
2196 ,x_return_status OUT NOCOPY VARCHAR2
2197 ,x_msg_count OUT NOCOPY NUMBER
2198 ,x_msg_data OUT NOCOPY VARCHAR2
2199 ,p_cons_bill_id IN OKL_CNSLD_AR_HDRS_V.ID%TYPE DEFAULT NULL
2200 ,p_ar_inv_id IN RA_CUSTOMER_TRX_ALL.CUSTOMER_TRX_ID%TYPE DEFAULT NULL
2201 ,p_contract_id IN OKC_K_HEADERS_ALL_B.ID%TYPE DEFAULT NULL
2202 ,p_rcpt_rec IN rcpt_rec_type
2203 ,x_cash_receipt_id OUT NOCOPY NUMBER
2204 ) IS
2205
2206 l_currency_code okl_k_headers_full_v.currency_code%type;
2207 l_customer_id OKL_TRX_CSH_RECEIPT_V.ILE_id%TYPE DEFAULT p_rcpt_rec.customer_id;
2208 l_customer_num HZ_CUST_ACCOUNTS.ACCOUNT_NUMBER%TYPE DEFAULT p_rcpt_rec.customer_number;
2209 l_contract_id OKC_K_HEADERS_V.ID%TYPE DEFAULT NULL;
2210 l_currency_conv_type OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_TYPE%TYPE DEFAULT p_rcpt_rec.exchange_rate_type;
2211 l_currency_conv_date OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE_DATE%TYPE DEFAULT p_rcpt_rec.exchange_date;
2212 l_currency_conv_rate OKL_TRX_CSH_RECEIPT_V.EXCHANGE_RATE%TYPE DEFAULT p_rcpt_rec.exchange_rate;
2213 l_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
2214 l_functional_conversion_rate GL_DAILY_RATES_V.CONVERSION_RATE%TYPE DEFAULT 0;
2215 l_inverse_conversion_rate GL_DAILY_RATES_V.INVERSE_CONVERSION_RATE%TYPE DEFAULT 0;
2216 l_functional_currency OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT NULL;
2217 l_receipt_currency_code OKL_TRX_CSH_RECEIPT_V.CURRENCY_CODE%TYPE DEFAULT p_rcpt_rec.currency_code;
2218 l_irm_id OKL_TRX_CSH_RECEIPT_V.IRM_ID%TYPE DEFAULT p_rcpt_rec.receipt_method_id;
2219 l_check_number OKL_TRX_CSH_RECEIPT_V.CHECK_NUMBER%TYPE DEFAULT p_rcpt_rec.receipt_number;
2220 l_rcpt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT p_rcpt_rec.amount;
2221 l_converted_receipt_amount OKL_TRX_CSH_RECEIPT_V.AMOUNT%TYPE DEFAULT NULL;
2222 l_rcpt_date OKL_TRX_CSH_RECEIPT_V.DATE_EFFECTIVE%TYPE DEFAULT TRUNC(p_rcpt_rec.receipt_date);
2223 l_gl_date OKL_TRX_CSH_RECEIPT_V.GL_DATE%TYPE DEFAULT p_rcpt_rec.gl_date;
2224 l_org_id OKL_TRX_CSH_RECEIPT_V.ORG_ID%TYPE DEFAULT p_rcpt_rec.org_id;
2225 l_dup_rcpt_flag NUMBER DEFAULT NULL;
2226 l_api_version NUMBER := 1.0;
2227 l_init_msg_list VARCHAR2(1) := Okc_Api.g_false;
2228 l_return_status VARCHAR2(1);
2229 l_msg_count NUMBER;
2230 l_msg_data VARCHAR2(2000);
2231 l_api_name CONSTANT VARCHAR2(30) := 'create_manual_receipt';
2232 l_cash_receipt_id AR_CASH_RECEIPTS.CASH_RECEIPT_ID%TYPE;
2233
2234
2235 l_rcpt_rec rcpt_rec_type := p_rcpt_rec;
2236
2237
2238 -- check for duplicate receipt numbers
2239 CURSOR c_dup_rcpt( cp_customer_id IN NUMBER
2240 ,cp_check_num IN VARCHAR2
2241 ,cp_receipt_date IN DATE
2242 ) IS
2243 SELECT '1'
2244 FROM AR_CASH_RECEIPTS
2245 WHERE PAY_FROM_CUSTOMER = cp_customer_id
2246 AND receipt_number = cp_check_num
2247 AND TRUNC(RECEIPT_DATE) = TRUNC(cp_receipt_date);
2248
2249 BEGIN
2250 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2251 G_PKG_NAME,
2252 p_init_msg_list,
2253 l_api_version,
2254 p_api_version,
2255 '_PVT',
2256 l_return_status);
2257 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2258 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2259 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2260 RAISE OKC_API.G_EXCEPTION_ERROR;
2261 END IF;
2262 l_functional_currency := okl_accounting_util.get_func_curr_code;
2263
2264 -- check for mandatory fields
2265
2266 IF l_customer_id IS NULL OR
2267 l_customer_id = OKC_API.G_MISS_NUM OR
2268 l_receipt_currency_code IS NULL OR
2269 l_receipt_currency_code = OKC_API.G_MISS_CHAR OR
2270 l_irm_id IS NULL OR
2271 l_irm_id = OKC_API.G_MISS_NUM OR
2272 l_rcpt_date IS NULL OR
2273 l_rcpt_date = OKC_API.G_MISS_DATE OR
2274 l_gl_date IS NULL OR
2275 l_gl_date = OKC_API.G_MISS_DATE OR
2276 l_org_id IS NULL OR
2277 l_org_id = OKC_API.G_MISS_NUM OR
2278 l_rcpt_amount = 0 OR
2279 l_rcpt_amount = OKC_API.G_MISS_NUM OR
2280 l_rcpt_amount IS NULL THEN
2281
2282 -- Message Text: Please enter all mandatory fields
2283 x_return_status := OKC_API.G_RET_STS_ERROR;
2284 OKC_API.set_message( p_app_name => G_APP_NAME,
2285 p_msg_name =>'OKL_BPD_MISSING_FIELDS');
2286
2287 RAISE G_EXCEPTION_HALT_VALIDATION;
2288
2289 END IF;
2290 --start code by pgomes on 03/05/2003
2291
2292 IF l_functional_currency <> l_receipt_currency_code AND
2293 l_currency_conv_type IN ('NONE') THEN
2294 -- Message Text: Please enter a currency type.
2295 x_return_status := OKC_API.G_RET_STS_ERROR;
2296 OKC_API.set_message( p_app_name => G_APP_NAME,
2297 p_msg_name => 'OKL_BPD_PLS_ENT_CUR_TYPE');
2298 RAISE G_EXCEPTION_HALT_VALIDATION;
2299 END IF;
2300 IF l_functional_currency = l_receipt_currency_code THEN
2301 IF l_currency_conv_type IN ('CORPORATE', 'SPOT', 'USER') OR
2302 l_currency_conv_rate <> '0' THEN
2303 -- Message Text: Currency conversion values are not required when the receipt and invoice currency's are the same.
2304 x_return_status := OKC_API.G_RET_STS_ERROR;
2305 OKC_API.set_message( p_app_name => G_APP_NAME,
2306 p_msg_name => 'OKL_BPD_SAME_CURRENCY');
2307 RAISE G_EXCEPTION_HALT_VALIDATION;
2308 END IF;
2309 END IF;
2310 IF l_functional_currency <> l_receipt_currency_code AND
2311 l_currency_conv_type NOT IN ('USER') THEN
2312 IF l_currency_conv_date IS NULL OR l_currency_conv_date = '' THEN
2313 l_currency_conv_date := trunc(l_rcpt_date);
2314 END IF;
2315 IF l_currency_conv_type = 'CORPORATE' THEN
2316 l_currency_conv_type := 'Corporate';
2317 ELSE
2318 l_currency_conv_type := 'Spot';
2319 END IF;
2320
2321 l_functional_conversion_rate := okl_accounting_util.get_curr_con_rate( l_receipt_currency_code
2322 ,l_functional_currency
2323 ,l_currency_conv_date
2324 ,l_currency_conv_type
2325 );
2326
2327 l_inverse_conversion_rate := okl_accounting_util.get_curr_con_rate( l_functional_currency
2328 ,l_receipt_currency_code
2329 ,l_currency_conv_date
2330 ,l_currency_conv_type
2331 );
2332
2333 IF l_functional_conversion_rate IN (0,-1) THEN
2334 --No exchange rate defined
2335 x_return_status := OKC_API.G_RET_STS_ERROR;
2336 OKC_API.set_message( p_app_name => G_APP_NAME,
2337 p_msg_name => 'OKL_BPD_NO_EXCHANGE_RATE');
2338 RAISE G_EXCEPTION_HALT_VALIDATION;
2339 END IF;
2340
2341 l_currency_conv_rate := l_functional_conversion_rate;
2342
2343 ELSIF l_functional_currency <> l_receipt_currency_code AND
2344 l_currency_conv_type IN ('USER') THEN
2345 IF l_currency_conv_rate IS NULL OR l_currency_conv_rate = '0' THEN
2346 -- Message Text: No exchange rate defined for currency conversion type USER.
2347 x_return_status := OKC_API.G_RET_STS_ERROR;
2348 OKC_API.set_message( p_app_name => G_APP_NAME,
2349 p_msg_name => 'OKL_BPD_USR_RTE_SUPPLIED');
2350 RAISE G_EXCEPTION_HALT_VALIDATION;
2351 ELSE
2352 l_functional_conversion_rate := l_currency_conv_rate;
2353 l_inverse_conversion_rate := l_functional_conversion_rate / 1;
2354 END IF;
2355
2356 l_currency_conv_type := 'User';
2357 l_currency_conv_date := trunc(SYSDATE);
2358 ELSE
2359 -- no currency conversion required
2360 l_currency_conv_date := NULL;
2361 l_currency_conv_type := NULL;
2362 l_currency_conv_rate := NULL;
2363 END IF;
2364 -- Check for exceptions
2365 IF l_rcpt_amount = 0 OR l_rcpt_amount IS NULL THEN
2366 -- Message Text: The receipt cannot have a value of zero
2367 x_return_status := OKC_API.G_RET_STS_ERROR;
2368 OKC_API.set_message( p_app_name => G_APP_NAME,
2369 p_msg_name => 'OKL_BPD_ZERO_RECEIPT');
2370 RAISE G_EXCEPTION_HALT_VALIDATION;
2371 END IF;
2372 OPEN c_dup_rcpt(l_customer_id, l_check_number, TRUNC(l_rcpt_date));
2373 FETCH c_dup_rcpt INTO l_dup_rcpt_flag;
2374 CLOSE c_dup_rcpt;
2375
2376
2377 IF l_dup_rcpt_flag = 1 THEN
2378 --Message Text: Duplicate receipt number for customer
2379 x_return_status := OKC_API.G_RET_STS_ERROR;
2380 OKC_API.set_message( p_app_name => G_APP_NAME,
2381 p_msg_name => 'OKL_BPD_DUP_RECEIPT');
2382
2383 RAISE G_EXCEPTION_HALT_VALIDATION;
2384 END IF;
2385
2386
2387 IF l_check_number IS NULL THEN
2388 l_rcpt_rec.RECEIPT_NUMBER := to_char(sysdate, 'MM/DD/YYYY HH24:MI:SS');
2389 END IF;
2390 -- store the functional currency at header lvl
2391
2392 IF l_receipt_currency_code <> l_functional_currency THEN
2393
2394 l_rcpt_rec.EXCHANGE_RATE_TYPE := l_currency_conv_type;
2395 l_rcpt_rec.EXCHANGE_DATE := l_currency_conv_date;
2396 l_rcpt_rec.EXCHANGE_RATE := l_functional_conversion_rate;
2397 -- in functional currency ...
2398 END IF;
2399
2400 okl_cash_receipt.CREATE_RECEIPT(p_api_version => l_api_version
2401 ,p_init_msg_list => l_init_msg_list
2402 ,x_return_status => l_return_status
2403 ,x_msg_count => l_msg_count
2404 ,x_msg_data => l_msg_data
2405 ,p_rcpt_rec => l_rcpt_rec
2406 ,x_cash_receipt_id => l_cash_receipt_id
2407 );
2408 x_cash_receipt_id := l_cash_receipt_id;
2409 x_return_status := l_return_status;
2410
2411 IF x_return_status <> OKL_API.G_RET_STS_SUCCESS THEN
2412 -- Message Text: Error creating receipt in AR
2413 x_return_status := OKC_API.G_RET_STS_ERROR;
2414 OKC_API.set_message( p_app_name => G_APP_NAME,
2415 p_msg_name => 'OKL_BPD_ERR_CRT_RCT_AR');
2416 RAISE G_EXCEPTION_HALT_VALIDATION;
2417 END IF;
2418
2419 -- Start of wraper code generated automatically by Debug code generator for Okl_Xcr_Pub.update_ext_csh_txns
2420 IF(IS_DEBUG_PROCEDURE_ON) THEN
2421 BEGIN
2422 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCAPB.pls call Okl_Xcr_Pub.update_ext_csh_txns ');
2423 END;
2424 END IF;
2425
2426 okl_api.END_ACTIVITY(x_msg_count, x_msg_data);
2427
2428 x_msg_data := l_msg_data;
2429 x_msg_count := l_msg_count;
2430
2431 EXCEPTION
2432
2433 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2434 x_return_status := OKL_API.G_RET_STS_ERROR;
2435
2436
2437 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2438 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2439 (
2440 l_api_name,
2441 G_PKG_NAME,
2442 'OKC_API.G_RET_STS_ERROR',
2443 x_msg_count,
2444 x_msg_data,
2445 '_PVT'
2446 );
2447
2448 WHEN OTHERS THEN
2449 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2450 Okl_api.set_message( p_app_name => g_app_name
2451 , p_msg_name => g_unexpected_error
2452 , p_token1 => g_sqlcode_token
2453 , p_token1_value => SQLCODE
2454 , p_token2 => g_sqlerrm_token
2455 , p_token2_value => SQLERRM
2456 ) ;
2457 END create_manual_receipt;
2458
2459
2460 END Okl_Cash_Appl_Rules;