[Home] [Help]
PACKAGE BODY: APPS.OKL_STREAM_BILLING_PVT
Source
1 PACKAGE BODY Okl_Stream_Billing_Pvt AS
2 /* $Header: OKLRBSTB.pls 120.57.12020000.6 2013/01/28 12:48:24 venkatho ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 -- Start of wraper code generated automatically by Debug code generator
8 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
9 L_DEBUG_ENABLED VARCHAR2(10);
10 -- L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := Okl_Debug_Pub.CHECK_LOG_ENABLED;
11 L_LEVEL_PROCEDURE NUMBER;
12 IS_DEBUG_PROCEDURE_ON BOOLEAN;
13 -- End of wraper code generated automatically by Debug code generator
14
15 -- Bug 4546873, Global variable for warning status
16 l_warning_status VARCHAR2(1);
17 -- End Bug 4546873, Global variable for warning status
18
19 -- Contract or Investor Agreement type
20 L_IA_TYPE VARCHAR2(10) :='IA';
21 L_CONTRACT_TYPE VARCHAR2(10) :='CONTRACT';
22
23 ----------------------------------------------
24 -- Global variables for bulk processing
25 ----------------------------------------------
26
27 l_tai_cnt NUMBER := 0;
28 l_til_cnt NUMBER := 0;
29 l_tld_cnt NUMBER := 0;
30 l_xsi_cnt NUMBER := 0;
31 l_xls_cnt NUMBER := 0;
32
33 TYPE tai_tbl_type IS TABLE OF OKL_TRX_AR_INVOICES_B%ROWTYPE INDEX BY BINARY_INTEGER;
34 TYPE til_tbl_type IS TABLE OF OKL_TXL_AR_INV_LNS_B%ROWTYPE INDEX BY BINARY_INTEGER;
35 TYPE tld_tbl_type IS TABLE OF OKL_TXD_AR_LN_DTLS_B%ROWTYPE INDEX BY BINARY_INTEGER;
36 TYPE xsi_tbl_type IS TABLE OF OKL_EXT_SELL_INVS_B%ROWTYPE INDEX BY BINARY_INTEGER;
37 TYPE xls_tbl_type IS TABLE OF OKL_XTL_SELL_INVS_B%ROWTYPE INDEX BY BINARY_INTEGER;
38
39 tai_tbl tai_tbl_type;
40 til_tbl til_tbl_type;
41 tld_tbl tld_tbl_type;
42 xsi_tbl xsi_tbl_type;
43 xls_tbl xls_tbl_type;
44
45 l_taitl_cnt NUMBER := 0;
46 l_tiltl_cnt NUMBER := 0;
47 l_tldtl_cnt NUMBER := 0;
48 l_xsitl_cnt NUMBER := 0;
49 l_xlstl_cnt NUMBER := 0;
50
51 TYPE taitl_tbl_type IS TABLE OF OKL_TRX_AR_INVOICES_TL%ROWTYPE INDEX BY BINARY_INTEGER;
52 TYPE tiltl_tbl_type IS TABLE OF OKL_TXL_AR_INV_LNS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
53 TYPE tldtl_tbl_type IS TABLE OF OKL_TXD_AR_LN_DTLS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
54 TYPE xsitl_tbl_type IS TABLE OF OKL_EXT_SELL_INVS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
55 TYPE xlstl_tbl_type IS TABLE OF OKL_XTL_SELL_INVS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
56
57 taitl_tbl taitl_tbl_type;
58 tiltl_tbl tiltl_tbl_type;
59 tldtl_tbl tldtl_tbl_type;
60 xsitl_tbl xsitl_tbl_type;
61 xlstl_tbl xlstl_tbl_type;
62
63 TYPE all_rec_type IS RECORD (
64 tai_id NUMBER,
65 til_id NUMBER,
66 tld_id NUMBER,
67 sel_id NUMBER,
68 xsi_id NUMBER,
69 xls_id NUMBER,
70 contract_number okc_k_headers_b.contract_number%TYPE,
71 stream_name okl_strm_type_v.name%TYPE,
72 bill_date DATE,
73 error_status VARCHAR2(10)
74 );
75
76 TYPE all_rec_tbl_type IS TABLE OF all_rec_type
77 INDEX BY BINARY_INTEGER;
78
79 all_rec_tbl all_rec_tbl_type;
80
81 l_art_index NUMBER := 0;
82 l_commit_cnt NUMBER := 0;
83 l_khr_id okl_trx_ar_invoices_v.khr_id%TYPE := -1;
84 l_bill_date okl_trx_ar_invoices_v.date_invoiced%TYPE;
85 --Bug# 9690217
86 l_bill_amount okl_trx_ar_invoices_v.amount%TYPE := 0;
87 l_kle_id okl_txl_ar_inv_lns_v.kle_id%TYPE := -1;
88 l_header_amount okl_trx_ar_invoices_v.amount%TYPE;
89 l_line_amount okl_txl_ar_inv_lns_v.amount%TYPE;
90 l_header_id okl_trx_ar_invoices_v.id%TYPE;
91 l_line_id okl_txl_ar_inv_lns_v.id%TYPE;
92
93 -- Bug 4524095 -- make break variables global
94 l_line_number okl_txl_ar_inv_lns_v.line_number%TYPE;
95 l_detail_number okl_txd_ar_ln_dtls_v.line_detail_number%TYPE;
96 -- End Code; Bug 4524095 -- make break variables global
97
98 -- To enforce commit frequency
99 -- Bug 4540379
100 l_max_commit_cnt NUMBER := 500;
101
102 l_ext_customer_id Okl_Ext_Sell_Invs_V.customer_id%TYPE;
103 l_ext_receipt_method_id Okl_Ext_Sell_Invs_V.receipt_method_id%TYPE;
104 l_ext_term_id Okl_Ext_Sell_Invs_V.term_id%TYPE;
105 l_ext_sob_id Okl_Ext_Sell_Invs_V.set_of_books_id%TYPE;
106 l_ext_trx_type_id Okl_Ext_Sell_Invs_V.cust_trx_type_id%TYPE;
107 l_ext_addr_id Okl_Ext_Sell_Invs_V.customer_address_id%TYPE;
108 l_ext_cust_bank_id Okl_Ext_Sell_Invs_V.customer_bank_account_id%TYPE;
109 l_addr_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
110 l_pmth_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
111 l_bank_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
112 l_rct_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
113 l_asst_tax OKC_RULES_B.rule_information1%TYPE;
114 l_asst_line_tax OKC_RULES_B.rule_information1%TYPE;
115 l_product_id okl_k_headers_full_v.pdt_id%TYPE;
116
117 -- Multi Currency Compliance
118 l_currency_code OKL_EXT_SELL_INVS_B.currency_code%TYPE;
119 l_currency_conversion_type OKL_EXT_SELL_INVS_B.currency_conversion_type%TYPE;
120 l_currency_conversion_rate OKL_EXT_SELL_INVS_B.currency_conversion_rate%TYPE;
121 l_currency_conversion_date OKL_EXT_SELL_INVS_B.currency_conversion_date%TYPE;
122 l_func_curr_code OKL_EXT_SELL_INVS_B.currency_code%TYPE;
123
124
125 l_inf_id okl_invoice_formats_v.id%TYPE;
126 l_private_label okc_rules_b.Rule_information1%type;
127 -- End Bug 4540379
128
129 -- For PPD process error reporting
130 l_overall_err_sts VARCHAR2(1);
131
132 -- -------------------------------------------------
133 -- To print log messages for tai_rec
134 -- -------------------------------------------------
135 PROCEDURE PRINT_TAI_REC(i_taiv_rec IN Okl_Tai_Pvt.taiv_rec_type)
136 IS
137 BEGIN
138 IF (G_DEBUG_ENABLED = 'Y') THEN
139 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
140 END IF;
141 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
142 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start TAI Record (+)');
143 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.khr_id ' || i_taiv_rec.khr_id);
144 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.date_invoiced ' || i_taiv_rec.date_invoiced);
145 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.try_id ' || i_taiv_rec.try_id);
146 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.date_entered ' || i_taiv_rec.date_entered);
147 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.description ' || i_taiv_rec.description);
148 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.trx_status_code ' || i_taiv_rec.trx_status_code);
149 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.amount ' || i_taiv_rec.amount);
150
151 -- rmunjulu R12 Fixes -- added code for debug
152 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.ixx_id ' || i_taiv_rec.ixx_id);
153 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.irm_id ' || i_taiv_rec.irm_id);
154 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.irt_id ' || i_taiv_rec.irt_id);
155 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.ibt_id ' || i_taiv_rec.ibt_id);
156 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.cust_trx_type_id ' || i_taiv_rec.cust_trx_type_id);
157 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.customer_bank_account_id ' || i_taiv_rec.customer_bank_account_id);
158 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.org_id ' || i_taiv_rec.org_id);
159 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.inf_id ' || i_taiv_rec.inf_id);
160 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.invoice_pull_yn ' || i_taiv_rec.invoice_pull_yn);
161 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.tax_exempt_flag ' || i_taiv_rec.tax_exempt_flag);
162 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.currency_code ' || i_taiv_rec.currency_code);
163 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.currency_conversion_type ' || i_taiv_rec.currency_conversion_type);
164 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.currency_conversion_rate ' || i_taiv_rec.currency_conversion_rate);
165 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.currency_conversion_date ' || i_taiv_rec.currency_conversion_date);
166 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.private_label ' || i_taiv_rec.private_label);
167 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_taiv_rec.set_of_books_id ' || i_taiv_rec.set_of_books_id);
168
169 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End TAI Record (-)');
170 END IF;
171 END PRINT_TAI_REC;
172
173 -- -------------------------------------------------
174 -- To print log messages for til_rec
175 -- -------------------------------------------------
176 PROCEDURE PRINT_TIL_REC(i_tilv_rec IN Okl_Til_Pvt.tilv_rec_type)
177 IS
178 BEGIN
179 IF (G_DEBUG_ENABLED = 'Y') THEN
180 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
181 END IF;
182 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
183 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start TIL Record (+)');
184 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.kle_id ' || i_tilv_rec.kle_id);
185 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.line_number ' || i_tilv_rec.line_number);
186 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.tai_id ' || i_tilv_rec.tai_id);
187 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.description ' || i_tilv_rec.description);
188 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.inv_receiv_line_code ' || i_tilv_rec.inv_receiv_line_code);
189 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.amount ' || i_tilv_rec.amount);
190
191 -- rmunjulu R12 Fixes -- added code for debug
192 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.ISL_ID ' || i_tilv_rec.ISL_ID);
193 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.ORG_ID ' || i_tilv_rec.ORG_ID);
194 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.inv_receiv_line_code ' || i_tilv_rec.inv_receiv_line_code);
195 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tilv_rec.QUANTITY ' || i_tilv_rec.QUANTITY);
196
197 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End TIL Record (-)');
198 END IF;
199 END PRINT_TIL_REC;
200
201 -- -------------------------------------------------
202 -- To print log messages for tld_rec
203 -- -------------------------------------------------
204 PROCEDURE PRINT_TLD_REC(i_tldv_rec IN Okl_Tld_Pvt.tldv_rec_type)
205 IS
206 BEGIN
207 IF (G_DEBUG_ENABLED = 'Y') THEN
208 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
209 END IF;
210 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
211 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start TLD Record (+)');
212 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.amount ' || i_tldv_rec.amount);
213 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.description ' || i_tldv_rec.description);
214 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.sel_id ' || i_tldv_rec.sel_id);
215 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.sty_id ' || i_tldv_rec.sty_id);
216 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.til_id_details ' || i_tldv_rec.til_id_details);
217 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.line_detail_number ' || i_tldv_rec.line_detail_number);
218
219 -- rmunjulu R12 Fixes -- added code for debug
220 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.invoice_format_type ' || i_tldv_rec.invoice_format_type);
221 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_tldv_rec.invoice_format_line_type ' || i_tldv_rec.invoice_format_line_type);
222
223 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End TLD Record (-)');
224 END IF;
225 END PRINT_TLD_REC;
226
227 -- -------------------------------------------------
228 -- To print log messages for xsi_rec
229 -- -------------------------------------------------
230 PROCEDURE PRINT_XSI_REC(i_xsiv_rec IN Okl_Xsi_Pvt.xsiv_rec_type)
231 IS
232 BEGIN
233 IF (G_DEBUG_ENABLED = 'Y') THEN
234 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
235 END IF;
236 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
237 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start XSI Record (+)');
238 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.trx_date ' || i_xsiv_rec.trx_date);
239 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.customer_id ' || i_xsiv_rec.customer_id);
240 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.receipt_method_id ' || i_xsiv_rec.receipt_method_id);
241 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.term_id ' || i_xsiv_rec.term_id);
242 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.currency_code ' || i_xsiv_rec.currency_code);
243 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.currency_conversion_type ' || i_xsiv_rec.currency_conversion_type);
244 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.currency_conversion_rate ' || i_xsiv_rec.currency_conversion_rate);
245 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.currency_conversion_date ' || i_xsiv_rec.currency_conversion_date);
246 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.customer_address_id ' || i_xsiv_rec.customer_address_id);
247 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.set_of_books_id ' || i_xsiv_rec.set_of_books_id);
248 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.cust_trx_type_id ' || i_xsiv_rec.cust_trx_type_id);
249 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.description ' || i_xsiv_rec.description);
250 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.CUSTOMER_BANK_ACCOUNT_ID ' || i_xsiv_rec.CUSTOMER_BANK_ACCOUNT_ID);
251 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.org_id ' || i_xsiv_rec.org_id);
252 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.trx_status_code ' || i_xsiv_rec.trx_status_code);
253 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.tax_exempt_flag ' || i_xsiv_rec.tax_exempt_flag);
254 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xsiv_rec.tax_exempt_reason_code ' || i_xsiv_rec.tax_exempt_reason_code);
255 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End XSI Record (-)');
256 END IF;
257 END PRINT_XSI_REC;
258
259 -- -------------------------------------------------
260 -- To print log messages for xls_rec
261 -- -------------------------------------------------
262 PROCEDURE PRINT_XLS_REC(i_xlsv_rec IN Okl_Xls_Pvt.xlsv_rec_type)
263 IS
264 BEGIN
265 IF (G_DEBUG_ENABLED = 'Y') THEN
266 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
267 END IF;
268
269 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
270 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start XLS Record (+)');
271
272 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.TLD_ID ' || i_xlsv_rec.TLD_ID);
273 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.XSI_ID_DETAILS ' || i_xlsv_rec.XSI_ID_DETAILS);
274 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.LINE_TYPE ' || i_xlsv_rec.LINE_TYPE);
275 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.DESCRIPTION ' || i_xlsv_rec.DESCRIPTION);
276 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.AMOUNT ' || i_xlsv_rec.AMOUNT);
277 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.ORG_ID ' || i_xlsv_rec.ORG_ID);
278 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_xlsv_rec.SEL_ID ' || i_xlsv_rec.SEL_ID);
279
280 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End XLS Record (-)');
281 END IF;
282 END PRINT_XLS_REC;
283
284 -- -------------------------------------------------
285 -- To print log messages for esd_rec
286 -- -------------------------------------------------
287 PROCEDURE PRINT_ESD_REC(i_esdv_rec IN Okl_Esd_Pvt.esdv_rec_type)
288 IS
289 BEGIN
290 IF (G_DEBUG_ENABLED = 'Y') THEN
291 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
292 END IF;
293
294 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
295 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start ESD Record (+)');
296 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_esdv_rec.code_combination_id ' || i_esdv_rec.code_combination_id);
297 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_esdv_rec.xls_id ' || i_esdv_rec.xls_id);
298 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_esdv_rec.amount ' || i_esdv_rec.amount);
299 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_esdv_rec.percent ' || i_esdv_rec.percent);
300 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'i_esdv_rec.account_class ' || i_esdv_rec.account_class);
301 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End ESD Record (-)');
302
303 END IF;
304 END PRINT_ESD_REC;
305
306 ------------------------------------------------------------------
307 -- Function GET_TRX_TYPE to extract transaction type
308 ------------------------------------------------------------------
309
310
311 FUNCTION get_trx_type
312 (p_name VARCHAR2,
313 p_language VARCHAR2)
314 RETURN NUMBER IS
315
316 CURSOR c_trx_type (cp_name VARCHAR2, cp_language VARCHAR2) IS
317 SELECT id
318 FROM OKL_TRX_TYPES_TL
319 WHERE name = cp_name
320 AND LANGUAGE = cp_language;
321
322 -- Replace with following query
323 CURSOR c_trx_id( p_sob_id NUMBER, p_org_id NUMBER ) IS
324 SELECT ID1
325 FROM OKX_CUST_TRX_TYPES_V
326 WHERE name = 'Invoice-OKL' AND
327 set_of_books_id = p_sob_id AND
328 org_id = p_org_id;
329
330 l_trx_type okl_trx_types_v.id%TYPE;
331
332 BEGIN
333
334 l_trx_type := NULL;
335
336 OPEN c_trx_type (p_name, p_language);
337 FETCH c_trx_type INTO l_trx_type;
338 CLOSE c_trx_type;
339
340 RETURN l_trx_type;
341
342 END get_trx_type;
343
344 ------------------------------------------------------------------------------
345 -- Function GET_PRINTING_LEAD_DAYS to extract lead days for invoice generation
346 ------------------------------------------------------------------------------
347 FUNCTION get_printing_lead_days
348 (p_khr_id NUMBER)
349 RETURN NUMBER IS
350
351 -- Derive print lead days from the rules
352 CURSOR c_lead_days(p_khr_id IN NUMBER) IS
353 SELECT rule_information3
354 FROM okc_rules_b rule,
355 okc_rule_groups_b rgp
356 WHERE rgp.id = rule.rgp_id
357 AND rgp.dnz_chr_id = p_khr_id
358 AND rgd_code = 'LABILL'
359 AND rule_information_category = 'LAINVD';
360
361 -- nikshah -- Bug # 5484903 Fixed,
362 -- Changed CURSOR c_default_lead_days SQL definition
363 --Derive print lead days from receivables setup
364 CURSOR c_default_lead_days(p_khr_id IN NUMBER) IS
365 SELECT term.printing_lead_days
366 FROM okc_k_headers_b khr
367 ,hz_customer_profiles cp
368 ,ra_terms_b term
369 WHERE khr.id = p_khr_id
370 AND khr.bill_to_site_use_id = cp.site_use_id
371 AND cp.standard_terms = term.term_id;
372
373 l_printing_lead_days NUMBER := 0;
374 BEGIN
375 OPEN c_lead_days(p_khr_id);
376 FETCH c_lead_days INTO l_printing_lead_days;
377 CLOSE c_lead_days;
378
379 IF (l_printing_lead_days IS NULL) THEN
380 OPEN c_default_lead_days(p_khr_id);
381 FETCH c_default_lead_days INTO l_printing_lead_days;
382 CLOSE c_default_lead_days;
383 END IF;
384
385 RETURN NVL(l_printing_lead_days, 0);
386 END get_printing_lead_days;
387
388 ------------------------------------------------------------------------------
389 -- Function GET_BANKRUPTCY_STATUS to get the bankruptcy status of a contract.
390 -- It also returns the disposition code.
391 ------------------------------------------------------------------------------
392 FUNCTION get_bankruptcy_status
393 (p_khr_id NUMBER
394 ,x_disposition_code OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
395
396 l_bankruptcy_status VARCHAR2(10) := 'N';
397
398 CURSOR l_bankrupt_csr(cp_khr_id NUMBER) IS
399 SELECT DECODE(disposition_code, 'NEGOTIATION', 'Y', 'GRANTED', 'Y', NULL, 'Y', 'N') bankruptcy_status
400 , disposition_code
401 FROM iex_bankruptcies ban
402 WHERE EXISTS (SELECT 1 FROM okc_k_party_roles_b rle
403 WHERE rle.dnz_chr_id = cp_khr_id
404 AND rle.rle_code = 'LESSEE'
405 AND TO_NUMBER(rle.object1_id1) = ban.party_id);
406
407 BEGIN
408 OPEN l_bankrupt_csr(p_khr_id);
409 FETCH l_bankrupt_csr INTO l_bankruptcy_status, x_disposition_code;
410 CLOSE l_bankrupt_csr;
411
412 RETURN NVL(l_bankruptcy_status, 'N');
413 END get_bankruptcy_status;
414
415
416 -- Below Function is added by Venkatho as part of Bug# 14119181
417 ------------------------------------------------------------------------------
418 -- Function GET_BANKRUPTCY_STATUS to get the bankruptcy status of a Customer.
419 ------------------------------------------------------------------------------
420 FUNCTION get_bankruptcy_status_new ( p_cust_acct_id NUMBER,
421 p_cust_party_id NUMBER
422 ) RETURN VARCHAR2
423 IS
424
425 l_bankruptcy_status VARCHAR2(10);
426
427 BEGIN
428 --------------------------------------
429 -- Check if the Customer is Bankrupted
430 --------------------------------------
431 l_bankruptcy_status := NULL;
432 l_bankruptcy_status := iex_wf_ban_status_pub.get_turninvoff(p_cust_party_id,p_cust_acct_id,SYSDATE);
433
434 RETURN l_bankruptcy_status;
435
436 END get_bankruptcy_status_new;
437 -- End of Function code added by Venkatho as part of Bug# 14119181
438
439 -- Below Procedure is added by Venkatho as part of Bug# 14119181
440 ---------------------------------------------------------------------------------------
441 -- Procedure GET_CUSTOMER_DTLS to fetch Customer Account ID and Party ID on a Contract
442 ---------------------------------------------------------------------------------------
443 PROCEDURE get_customer_dtls (p_khr_id IN NUMBER,
444 p_cust_acct_id OUT NOCOPY NUMBER,
445 p_cust_party_id OUT NOCOPY NUMBER)
446 IS
447 BEGIN
448
449 SELECT okh.cust_acct_id,
450 hca.party_id
451 INTO p_cust_acct_id,
452 p_cust_party_id
453 FROM okc_k_headers_b okh,
454 hz_cust_accounts hca
455 WHERE hca.status = 'A'
456 AND okh.cust_acct_id = hca.cust_account_id
457 AND id = p_khr_id;
458
459 EXCEPTION
460 WHEN OTHERS THEN
461 IF (G_IS_DEBUG_STATEMENT_ON = TRUE)
462 THEN
463 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'(Exception): When retrieving Customer ACCT Details.'||SQLERRM );
464 END IF;
465
466 FND_FILE.PUT_LINE (Fnd_File.LOG,'(Exception): When retrieving Customer ACCT Details.'||SQLERRM );
467 p_cust_party_id := NULL;
468 p_cust_acct_id := NULL;
469
470 END get_customer_dtls;
471
472 ------------------------------------------------------------------
473 -- Procedure BULK_PROCESS to bulk insert and bulk update
474 ------------------------------------------------------------------
475 PROCEDURE bulk_process
476 (p_api_version IN NUMBER
477 ,p_init_msg_list IN VARCHAR2
478 ,x_return_status OUT NOCOPY VARCHAR2
479 ,x_msg_count OUT NOCOPY NUMBER
480 ,x_msg_data OUT NOCOPY VARCHAR2
481 ,p_commit IN VARCHAR2
482 ,p_source IN VARCHAR2
483 ) IS
484
485 l_api_name CONSTANT VARCHAR2(30) := 'BULK_PROCESS';
486 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
487 l_api_version CONSTANT NUMBER := 1;
488
489 CURSOR acc_dstrs_csr( p_source_id IN NUMBER, p_source_table IN VARCHAR2 ) IS
490 SELECT CR_DR_FLAG,
491 CODE_COMBINATION_ID,
492 SOURCE_ID,
493 AMOUNT,
494 PERCENTAGE,
495 NVL(COMMENTS,'-99') COMMENTS
496 FROM OKL_TRNS_ACC_DSTRS
497 WHERE SOURCE_ID = p_source_id AND
498 SOURCE_TABLE = p_source_table;
499
500 CURSOR get_languages IS
501 SELECT *
502 FROM FND_LANGUAGES
503 WHERE INSTALLED_FLAG IN ('I', 'B');
504
505
506 -- Start : Bug#5964007 : PRASJAIN
507 -- Cursor to check if 3 level credit memo is on-account
508 CURSOR c_3level_cm(p_tld_id OKL_TXD_AR_LN_DTLS_B.ID%TYPE) IS
509 SELECT 'X' FROM
510 OKL_TXD_AR_LN_DTLS_B
511 WHERE ID = p_tld_id
512 AND TLD_ID_REVERSES IS NULL;
513
514 l_on_acc_cm BOOLEAN;
515 l_chk VARCHAR2(1);
516 -- End : Bug#5964007 : PRASJAIN
517
518 l_xtd_cnt NUMBER := 0;
519 l_xtdtl_cnt NUMBER := 0;
520 l_code_combination_id NUMBER;
521 l_acc_dist_status VARCHAR2(1);
522
523
524 TYPE xtd_tbl_type IS TABLE OF OKL_XTD_SELL_INVS_B%ROWTYPE INDEX BY BINARY_INTEGER;
525 TYPE xtdtl_tbl_type IS TABLE OF OKL_XTD_SELL_INVS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
526 xtd_tbl xtd_tbl_type;
527 xtdtl_tbl xtdtl_tbl_type;
528 l_xtd_id okl_xtd_sell_invs_v.id%TYPE;
529 l_prev_tai_id OKL_TRX_AR_INVOICES_B.ID%TYPE := NULL;
530
531 -------------------------------------
532 -- Variables for bulk updates
533 -------------------------------------
534
535 l_tai_id_cnt NUMBER := 0;
536 l_xsi_id_cnt NUMBER := 0;
537 l_sel_id_cnt NUMBER := 0;
538
539 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
540
541 tai_id_tbl num_tbl;
542 xsi_id_tbl num_tbl;
543 sel_id_tbl num_tbl;
544
545 ------------------------------------------------------------
546 -- Declare variables to call Accounting Engine.
547 ------------------------------------------------------------
548 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
549
550 ------------------------------------------------------------
551 -- For errors in Stream Elements Table
552 ------------------------------------------------------------
553 l_distr_cnt NUMBER := 0;
554
555 ------------------------------------------------------------
556 -- Variables for Error Processing
557 ------------------------------------------------------------
558
559 l_error_status VARCHAR2(1);
560 l_error_message VARCHAR2(2000);
561 l_err_tai_id OKL_TRX_AR_INVOICES_B.ID%TYPE := NULL;
562 l_rec_status VARCHAR2(10) := NULL;
563
564 l_request_id NUMBER(15);
565 l_program_application_id NUMBER(15);
566 l_program_id NUMBER(15);
567 l_program_update_date DATE;
568
569 -- Start Bug 4520466
570 dist_khr_id_tbl num_tbl;
571
572 dist_khr_indx number;
573
574 -- ----------------------
575 -- Std Who columns
576 -- ----------------------
577 lx_last_updated_by okl_k_control.last_updated_by%TYPE := Fnd_Global.USER_ID;
578 lx_last_update_login okl_k_control.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
579 lx_request_id okl_k_control.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
580 lx_program_id okl_k_control.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
581 -- End Bug 4520466
582
583 ---- Added by Vpanwar --- Code for new accounting API uptake
584 l_tmpl_identify_rec Okl_Account_Dist_Pub.TMPL_IDENTIFY_REC_TYPE;
585 l_dist_info_rec Okl_Account_Dist_Pub.DIST_INFO_REC_TYPE;
586 l_ctxt_val_tbl Okl_Account_Dist_Pub.CTXT_VAL_TBL_TYPE;
587 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.ACC_GEN_PRIMARY_KEY;
588
589 l_tmpl_identify_tbl Okl_Account_Dist_Pvt.TMPL_IDENTIFY_TBL_TYPE;
590 l_dist_info_tbl Okl_Account_Dist_Pvt.DIST_INFO_TBL_TYPE;
591 l_ctxt_tbl Okl_Account_Dist_Pvt.CTXT_TBL_TYPE;
592 l_acc_gen_tbl Okl_Account_Dist_Pvt.ACC_GEN_TBL_TYPE;
593 l_template_out_tbl Okl_Account_Dist_Pvt.avlv_out_tbl_type;
594 l_amount_out_tbl Okl_Account_Dist_Pvt.amount_out_tbl_type;
595 l_trx_header_id NUMBER;
596 l_trx_header_tbl VARCHAR2(50);
597 l_curr_tai_id NUMBER;
598 l_prev_tld_counter NUMBER;
599 l_curr_til_counter NUMBER;
600 l_next_til_counter NUMBER;
601 l_accounting_dist_flag BOOLEAN;
602 ---- End Added by Vpanwar --- Code for new accounting API uptake
603
604
605 BEGIN
606
607 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
608 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE,'okl_stream_billing_pvt'
609 ,'Begin(+)');
610 END IF;
611
612 -- ------------------------
613 -- Print Input variables
614 -- ------------------------
615 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
616 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_commit '||p_commit);
617
618 END IF;
619 l_return_status := Okl_Api.START_ACTIVITY(
620 p_api_name => l_api_name,
621 p_pkg_name => G_PKG_NAME,
622 p_init_msg_list => p_init_msg_list,
623 l_api_version => l_api_version,
624 p_api_version => p_api_version,
625 p_api_type => '_PVT',
626 x_return_status => x_return_status); -- rmunjulu bug 6736148 use x_return_status as out param
627
628 --------------------------------------------
629 -- Initialize request/program variables
630 --------------------------------------------
631 BEGIN
632 SELECT
633 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
634 DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
635 DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
636 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
637 INTO
638 l_request_id,
639 l_program_application_id,
640 l_program_id,
641 l_program_update_date
642 FROM dual;
643 EXCEPTION
644 WHEN OTHERS THEN
645 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
646 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'(Exception): When resolving request_id'||SQLERRM );
647 END IF;
648 Fnd_File.PUT_LINE (Fnd_File.LOG,'(Exception): When resolving request_id'||SQLERRM );
649 END;
650
651 --------------------------------------------------
652 -- Bulk insert TAI, TIL, TLD, XSI and XLS records
653 --------------------------------------------------
654
655 -- --------------------------------------
656 -- Transfer Tai records to the Tai table
657 -- --------------------------------------
658 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
659 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_trx_ar_invoices_b');
660
661 END IF;
662 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Transfering TAI records to TAI table...');
663
664 Fnd_File.PUT_LINE (Fnd_File.LOG, 'tai_tbl.COUNT : ' || tai_tbl.COUNT);
665
666 IF tai_tbl.COUNT > 0 THEN
667 FORALL indx IN tai_tbl.first..tai_tbl.LAST
668 INSERT INTO OKL_TRX_AR_INVOICES_B
669 VALUES tai_tbl(indx);
670
671 -- Start Bug 4520466
672 -- update okl_k_control table
673
674 -- Clear all table entries, if any
675 dist_khr_id_tbl.delete;
676
677 for i in tai_tbl.first..tai_tbl.last loop
678
679 FND_FILE.PUT_LINE (FND_FILE.LOG, 'tai_tbl(i).khr_id: '||tai_tbl(i).khr_id);
680
681 if dist_khr_id_tbl.count = 0 then
682
683 FND_FILE.PUT_LINE (FND_FILE.LOG, 'dist_khr_id_tbl.count: '||dist_khr_id_tbl.count);
684
685 dist_khr_indx := dist_khr_id_tbl.count + 1;
686 dist_khr_id_tbl(dist_khr_indx) := tai_tbl(i).khr_id;
687
688 else
689
690 FND_FILE.PUT_LINE (FND_FILE.LOG, 'dist_khr_id_tbl(dist_khr_indx): '||dist_khr_id_tbl(dist_khr_indx));
691 FND_FILE.PUT_LINE (FND_FILE.LOG, 'tai_tbl(i).khr_id: '||tai_tbl(i).khr_id);
692
693 if ( dist_khr_id_tbl(dist_khr_indx) <> tai_tbl(i).khr_id ) then
694
695 FND_FILE.PUT_LINE (FND_FILE.LOG, 'dist_khr_id_tbl(dist_khr_indx) <> tai_tbl(i).khr_id');
696
697 dist_khr_indx := dist_khr_id_tbl.count + 1;
698 dist_khr_id_tbl(dist_khr_indx) := tai_tbl(i).khr_id;
699
700 end if;
701 end if;
702 end loop; -- for tai_tbl records
703 -- End Bug 4520466
704
705 END IF; -- if tai_tbl.COUNT > 0
706
707 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
708 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_trx_ar_invoices_b');
709
710 END IF;
711 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done transfering TAI records to TAI table...');
712 -- --------------------------------------
713 -- Transfer TaiTl records to the TaiTl table
714 -- --------------------------------------
715 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
716 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_trx_ar_invoices_tl');
717
718 END IF;
719 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Transfering TAI_TL records to TAI_TL table...');
720
721 Fnd_File.PUT_LINE (Fnd_File.LOG, 'taitl_tbl.COUNT : ' || taitl_tbl.COUNT);
722
723 IF taitl_tbl.COUNT > 0 THEN
724 FORALL indx IN taitl_tbl.first..taitl_tbl.LAST
725 INSERT INTO OKL_TRX_AR_INVOICES_TL
726 VALUES taitl_tbl(indx);
727 END IF;
728
729 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
730 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_trx_ar_invoices_tl');
731
732 END IF;
733 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done transfering TAI_TL records to TAI_TL table...');
734 -- --------------------------------------
735 -- Transfer Til records to the Til table
736 -- --------------------------------------
737 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
738 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txl_ar_inv_lns_b');
739
740 END IF;
741 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Transfering TIL records to TIL table...');
742
743 Fnd_File.PUT_LINE (Fnd_File.LOG, 'til_tbl.COUNT : ' || til_tbl.COUNT);
744
745 IF til_tbl.COUNT > 0 THEN
746 FORALL indx IN til_tbl.first..til_tbl.LAST
747 INSERT INTO OKL_TXL_AR_INV_LNS_B
748 VALUES til_tbl(indx);
749 END IF;
750
751 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
752 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txl_ar_inv_lns_b');
753
754 END IF;
755 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done transfering TIL records to TIL table...');
756 -- --------------------------------------
757 -- Transfer TilTl records to the TilTl table
758 -- --------------------------------------
759 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
760 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txl_ar_inv_lns_tl');
761
762 END IF;
763 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Transfering TIL_TL records to TIL_TL table...');
764
765 Fnd_File.PUT_LINE (Fnd_File.LOG, 'tiltl_tbl.COUNT : ' || tiltl_tbl.COUNT);
766
767 IF tiltl_tbl.COUNT > 0 THEN
768 FORALL indx IN tiltl_tbl.first..tiltl_tbl.LAST
769 INSERT INTO OKL_TXL_AR_INV_LNS_TL
770 VALUES tiltl_tbl(indx);
771 END IF;
772
773 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
774 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txl_ar_inv_lns_tl');
775
776 END IF;
777 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done transfering TIL_TL records to TIL_TL table...');
778 -- --------------------------------------
779 -- Transfer Tld records to the Tld table
780 -- --------------------------------------
781 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
782 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txd_ar_ln_dtls_b');
783
784 END IF;
785 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Transfering TLD records to TLD table...');
786
787 Fnd_File.PUT_LINE (Fnd_File.LOG, 'tld_tbl.COUNT : ' || tld_tbl.COUNT);
788
789 IF tld_tbl.COUNT > 0 THEN
790 FORALL indx IN tld_tbl.first..tld_tbl.LAST
791 INSERT INTO OKL_TXD_AR_LN_DTLS_B
792 VALUES tld_tbl(indx);
793 END IF;
794
795 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
796 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txd_ar_ln_dtls_b');
797
798 END IF;
799 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done transfering TLD records to TLD table...');
800 -- --------------------------------------
801 -- Transfer TldTl records to the TldTl table
802 -- --------------------------------------
803 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
804 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txd_ar_ln_dtls_tl');
805
806 END IF;
807 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Transfering TLD_TL records to TLD_TL table...');
808
809 Fnd_File.PUT_LINE (Fnd_File.LOG, 'tldtl_tbl.COUNT : ' || tldtl_tbl.COUNT);
810
811 IF tldtl_tbl.COUNT > 0 THEN
812 FORALL indx IN tldtl_tbl.first..tldtl_tbl.LAST
813 INSERT INTO OKL_TXD_AR_LN_DTLS_TL
814 VALUES tldtl_tbl(indx);
815 END IF;
816
817 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
818 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txd_ar_ln_dtls_tl');
819
820 END IF;
821 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done transfering TLD_TL records to TLD_TL table...');
822 -- --------------------------------------
823 -- Transfer Xsi records to the Xsi table
824 -- --------------------------------------
825 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
826 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_ext_sell_invs_b');
827
828 END IF;
829 -- ---------------------------------------------
830 -- Create distributions using Accounting Enginge
831 -- ---------------------------------------------
832
833 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Creating Acct Distributions...'); -- and XTD, XTD_TL records...'); -- rmunjulu R12 Fixes commented
834
835 IF tld_tbl.COUNT > 0 THEN -- rmunjulu R12 Fixes changed to tld
836 ---- Added by Vpanwar --- Code for new accounting API uptake
837 l_curr_til_counter := til_tbl.FIRST;
838 l_prev_tld_counter := tld_tbl.FIRST;
839
840 LOOP
841 l_curr_tai_id := til_tbl(l_curr_til_counter).tai_id;
842 FOR i IN l_prev_tld_counter..tld_tbl.LAST LOOP -- rmunjulu R12 Fixes changed to tld
843 ---- End Added by Vpanwar --- Code for new accounting API uptake
844 l_acc_dist_status := 'S';
845 l_error_message := NULL;
846 p_bpd_acc_rec.id := tld_tbl(i).id; --xls_tbl(i).tld_id; -- rmunjulu R12 Fixes changed to tld
847 p_bpd_acc_rec.source_table := 'OKL_TXD_AR_LN_DTLS_B';
848
849 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
850 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Transaction Line Id '||p_bpd_acc_rec.id);
851 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Transaction Source Table '||p_bpd_acc_rec.source_table);
852 END IF;
853
854 ---- Added by Vpanwar --- Code for new accounting API uptake
855 l_prev_tld_counter := i;
856
857 IF tld_tbl(i).TIL_ID_DETAILS = til_tbl(l_curr_til_counter).id THEN
858 Okl_Acc_Call_Pub.CREATE_ACC_TRANS_NEW(p_api_version => p_api_version,
859 p_init_msg_list => p_init_msg_list,
860 x_return_status => l_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 p_bpd_acc_rec => p_bpd_acc_rec,
864 x_tmpl_identify_rec => l_tmpl_identify_rec,
865 x_dist_info_rec => l_dist_info_rec,
866 x_ctxt_val_tbl => l_ctxt_val_tbl,
867 x_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl);
868
869 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
870 IF (x_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
871 x_return_status := l_return_status;
872 END IF;
873 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
874 END IF;
875
876 --- populate the tables for passing to Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST
877
878 l_acc_gen_tbl(i).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
879 l_acc_gen_tbl(i).source_id := l_dist_info_rec.source_id;
880
881 l_ctxt_tbl(i).ctxt_val_tbl := l_ctxt_val_tbl;
882 l_ctxt_tbl(i).source_id := l_dist_info_rec.source_id;
883
884 l_tmpl_identify_tbl(i) := l_tmpl_identify_rec;
885
886 l_dist_info_tbl(i) := l_dist_info_rec;
887
888 ---- End populate the tables for passing to Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST
889
890 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
891 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'AE Call: x_return_status '||x_return_status);
892 END IF;
893
894 l_accounting_dist_flag := TRUE;
895
896 ELSE
897 IF l_accounting_dist_flag THEN
898 EXIT;
899 END IF;
900 END IF;
901 END LOOP;------ end loop for tld_tbl
902 ---- End Added by Vpanwar --- Code for new accounting API uptake
903
904 ---- Added by Vpanwar --- Code for new accounting API uptake
905
906
907 l_next_til_counter := til_tbl.next(l_curr_til_counter);
908 if (l_next_til_counter is null) OR ((til_tbl(l_next_til_counter).tai_id <> l_curr_tai_id)) THEN
909 l_trx_header_tbl:= 'okl_trx_ar_invoices_b';
910 l_trx_header_id := l_curr_tai_id; --- need to be correct
911 --Call accounting with new signature
912 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
913 p_api_version => p_api_version,
914 p_init_msg_list => p_init_msg_list,
915 x_return_status => x_return_status,
916 x_msg_count => x_msg_count,
917 x_msg_data => x_msg_data,
918 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
919 p_dist_info_tbl => l_dist_info_tbl,
920 p_ctxt_val_tbl => l_ctxt_tbl,
921 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
922 x_template_tbl => l_template_out_tbl,
923 x_amount_tbl => l_amount_out_tbl,
924 p_trx_header_id => l_trx_header_id,
925 p_trx_header_table => l_trx_header_tbl);
926
927 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
928 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
929 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
930 RAISE OKL_API.G_EXCEPTION_ERROR;
931 END IF;
932
933 --DELETE local tables
934 l_acc_gen_tbl.DELETE;
935 l_ctxt_tbl.DELETE;
936 l_tmpl_identify_tbl.DELETE;
937 l_dist_info_tbl.DELETE;
938
939 END IF;
940 l_curr_til_counter := l_next_til_counter;
941
942 EXIT WHEN l_curr_til_counter is null;
943
944 END LOOP; -- Accounting Engine Loop
945 ---- End Added by Vpanwar --- Code for new accounting API uptake
946
947 END IF;
948
949 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done creating Acct Distributions...'); -- and XTD, XTD_TL records...'); -- rmunjulu R12 Fixes commented
950
951 -----------------------------------------------------
952 -- Move tai, xsi and sel records to different tables
953 -- for bulk updates
954 -----------------------------------------------------
955
956 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
957 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'all_rec_tbl.count : ' || all_rec_tbl.COUNT);
958 END IF;
959 IF all_rec_tbl.COUNT > 0 THEN
960 FOR i IN all_rec_tbl.first..all_rec_tbl.last LOOP
961 IF l_prev_tai_id IS NULL OR all_rec_tbl(i).tai_id <> l_prev_tai_id THEN
962 l_rec_status := all_rec_tbl(i).error_status;
963 IF l_rec_status IS NULL THEN
964 tai_id_tbl(l_tai_id_cnt) := all_rec_tbl(i).tai_id;
965 l_tai_id_cnt := l_tai_id_cnt + 1;
966 END IF;
967 END IF;
968 -- rmunjulu R12 Fixes comment XSI
969 IF l_rec_status IS NULL THEN
970 -- xsi_id_tbl(l_xsi_id_cnt) := all_rec_tbl(i).xsi_id;
971 -- l_xsi_id_cnt := l_xsi_id_cnt + 1;
972 sel_id_tbl(l_sel_id_cnt) := all_rec_tbl(i).sel_id;
973 l_sel_id_cnt := l_sel_id_cnt + 1;
974 END IF;
975
976 l_prev_tai_id := all_rec_tbl(i).tai_id;
977 END LOOP;
978 END IF;
979
980 -- ---------------------------------------------------
981 -- Flag internal transaction to status of Processed
982 -- ---------------------------------------------------
983 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
984 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'tai_id_tbl.count : ' || tai_id_tbl.COUNT);
985 END IF;
986 IF tai_id_tbl.COUNT > 0 THEN
987 FORALL indx IN tai_id_tbl.FIRST..tai_id_tbl.LAST
988 UPDATE okl_trx_ar_invoices_b
989 SET trx_status_code = 'SUBMITTED' -- 'PROCESSED' -- rmunjulu R12 Fixes changed to submitted IS THIS CORRECT
990 WHERE id = tai_id_tbl(indx);
991 END IF;
992 -- ---------------------------------------------------
993 -- Set stream elements to billed status
994 -- ---------------------------------------------------
995 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
996 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'sel_id_tbl.count : ' || sel_id_tbl.COUNT);
997 END IF;
998 IF sel_id_tbl.COUNT > 0 THEN
999 FORALL indx IN sel_id_tbl.FIRST..sel_id_tbl.LAST
1000 UPDATE okl_strm_elements
1001 SET date_billed = SYSDATE
1002 ,last_updated_by = FND_GLOBAL.USER_ID -- BUG 14742784 change starts here
1003 ,last_update_date = sysdate
1004 ,last_update_login = FND_GLOBAL.LOGIN_ID -- BUG 14742784 change ends here
1005 WHERE id = sel_id_tbl(indx);
1006 END IF;
1007
1008 -- Start Bug 4520466 stmathew update okl_k_control
1009
1010 if dist_khr_id_tbl.count > 0 then
1011 forall indx in dist_khr_id_tbl.first..dist_khr_id_tbl.last
1012 save exceptions
1013 UPDATE okl_k_control nbd
1014 set EARLIEST_STRM_BILL_DATE = (
1015 SELECT MIN(ste.stream_element_date)
1016 FROM OKL_STRM_ELEMENTS ste,
1017 OKL_STREAMS stm,
1018 okl_strm_type_v sty,
1019 okc_k_headers_b khr,
1020 OKL_K_HEADERS khl,
1021 okc_k_lines_b kle,
1022 okc_statuses_b khs,
1023 okc_statuses_b kls
1024 WHERE ste.amount <> 0
1025 AND stm.id = ste.stm_id
1026 AND ste.date_billed IS NULL
1027 AND stm.active_yn = 'Y'
1028 AND stm.say_code = 'CURR'
1029 AND sty.id = stm.sty_id
1030 AND sty.billable_yn = 'Y'
1031 AND khr.id = stm.khr_id
1032 AND khr.scs_code IN ('LEASE', 'LOAN', 'INVESTOR')
1033 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED', 'ACTIVE') -- bug 6472228 added EXPIRED status
1034 AND khr.id = nbd.khr_id
1035 AND khl.id = stm.khr_id
1036 AND (khl.deal_type IS NOT NULL OR khr.sts_code = 'ACTIVE')
1037 AND khs.code = khr.sts_code
1038 AND kle.id (+) = stm.kle_id
1039 AND kls.code (+) = kle.sts_code
1040 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') ), -- bug 6472228 added EXPIRED status
1041 last_update_date = sysdate,
1042 last_updated_by = lx_last_updated_by,
1043 last_update_login = lx_last_update_login,
1044 request_id = lx_request_id,
1045 program_update_date = sysdate,
1046 program_id = lx_program_id
1047 where nbd.khr_id = dist_khr_id_tbl(indx);
1048
1049 if sql%bulk_exceptions.count > 0 then
1050 for i in 1..sql%bulk_exceptions.count loop
1051 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1052 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'while fetching, error ' || i || ' occurred during '||
1053 'iteration ' || sql%bulk_exceptions(i).error_index);
1054 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'oracle error is ' ||
1055 sqlerrm(sql%bulk_exceptions(i).error_code));
1056 END IF;
1057 end loop;
1058 end if; -- if sql bulk_exceptions > 0
1059 end if; -- if dist_khr_id_tbl count > 0
1060
1061 dist_khr_id_tbl.delete;
1062
1063 -- End Bug 4520466 stmathew update okl_k_control
1064
1065 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1066 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done updating status of records in tai and sel database tables'); -- rmunjulu R12 Fixes REMOVED XSI FROM COMMENTS
1067
1068 END IF;
1069 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Done updating status of records in tai and sel database tables'); -- rmunjulu R12 Fixes REMOVED XSI FROM COMMENTS
1070
1071 -----------------------
1072 -- Commit
1073 -----------------------
1074 IF Fnd_Api.To_Boolean( p_commit ) THEN
1075 COMMIT;
1076 END IF;
1077
1078 ------------------------------------------
1079 -- Clean up the tables after processing
1080 ------------------------------------------
1081
1082 tai_tbl.DELETE;
1083 til_tbl.DELETE;
1084 tld_tbl.DELETE;
1085
1086 taitl_tbl.DELETE;
1087 tiltl_tbl.DELETE;
1088 tldtl_tbl.DELETE;
1089
1090 tai_id_tbl.DELETE;
1091 sel_id_tbl.DELETE;
1092
1093 all_rec_tbl.DELETE;
1094 l_art_index := 0;
1095
1096 l_tai_cnt := 1;
1097 l_til_cnt := 0;
1098 l_tld_cnt := 0;
1099
1100 l_taitl_cnt := 0;
1101 l_tiltl_cnt := 0;
1102 l_tldtl_cnt := 0;
1103
1104 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1105 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE,'okl_stream_billing_pvt'
1106 ,'End(-)');
1107 END IF;
1108
1109 Okl_Api.END_ACTIVITY (
1110 x_msg_count => x_msg_count,
1111 x_msg_data => x_msg_data);
1112
1113
1114 EXCEPTION
1115
1116 ------------------------------------------------------------
1117 -- Exception handling
1118 ------------------------------------------------------------
1119
1120 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1121 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (EXCP) => '||SQLERRM);
1122
1123 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1124 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
1125 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
1126 END IF;
1127
1128 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1129 p_api_name => l_api_name,
1130 p_pkg_name => G_PKG_NAME,
1131 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1132 x_msg_count => x_msg_count,
1133 x_msg_data => x_msg_data,
1134 p_api_type => '_PVT');
1135
1136 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1137 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
1138
1139 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1140 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
1141 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
1142 END IF;
1143
1144 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1145 p_api_name => l_api_name,
1146 p_pkg_name => G_PKG_NAME,
1147 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1148 x_msg_count => x_msg_count,
1149 x_msg_data => x_msg_data,
1150 p_api_type => '_PVT');
1151
1152 WHEN OTHERS THEN
1153 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (OTHERS 1) => '||SQLERRM);
1154
1155 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
1156 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
1157 'EXCEPTION :'||'OTHERS');
1158 END IF;
1159
1160 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1161 p_api_name => l_api_name,
1162 p_pkg_name => G_PKG_NAME,
1163 p_exc_name => 'OTHERS',
1164 x_msg_count => x_msg_count,
1165 x_msg_data => x_msg_data,
1166 p_api_type => '_PVT');
1167
1168 END bulk_process;
1169
1170 ------------------------------------------------------------------
1171 -- Procedure Process_bill_tbl to bill outstanding stream elements
1172 ------------------------------------------------------------------
1173 PROCEDURE Process_bill_tbl
1174 (p_api_version IN NUMBER
1175 ,p_init_msg_list IN VARCHAR2
1176 ,x_return_status OUT NOCOPY VARCHAR2
1177 ,x_msg_count OUT NOCOPY NUMBER
1178 ,x_msg_data OUT NOCOPY VARCHAR2
1179 ,p_commit IN VARCHAR2
1180 ,p_contract_number IN VARCHAR2
1181 ,p_from_bill_date IN DATE
1182 ,p_to_bill_date IN DATE
1183 ,p_bill_tbl IN bill_tbl_type
1184 ,p_source IN VARCHAR2
1185 ,p_end_of_records IN VARCHAR2)
1186 IS
1187
1188 --cklee: start 3/16/07
1189 cursor c_seq is
1190 SELECT OKL_TXD_AR_LN_DTLS_B_S.NEXTVAL
1191 FROM dual;
1192 l_seq number;
1193 --cklee: start 3/16/07
1194
1195 ------------------------------------------------------------
1196 -- Get trx_id for Invoice
1197 ------------------------------------------------------------
1198 CURSOR c_trx_id( p_sob_id NUMBER, p_org_id NUMBER ) IS
1199 SELECT ID1
1200 FROM OKX_CUST_TRX_TYPES_V
1201 WHERE name = 'Invoice-OKL' AND
1202 set_of_books_id = p_sob_id AND
1203 org_id = p_org_id;
1204
1205 ------------------------------------------------------------
1206 -- Get trx_id for Credit Memo
1207 ------------------------------------------------------------
1208 CURSOR c_trx_id1( p_sob_id NUMBER, p_org_id NUMBER ) IS
1209 SELECT ID1
1210 FROM OKX_CUST_TRX_TYPES_V
1211 WHERE name = 'Credit Memo-OKL' AND
1212 set_of_books_id = p_sob_id AND
1213 org_id = p_org_id;
1214
1215 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_BILL_TBL';
1216
1217 ------------------------------------------------------------
1218 -- Initialise constants
1219 ------------------------------------------------------------
1220 l_def_desc CONSTANT VARCHAR2(30) := 'Regular Stream Billing';
1221 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
1222 l_init_status CONSTANT VARCHAR2(30) := 'ENTERED';
1223 l_final_status CONSTANT VARCHAR2(30) := 'PROCESSED';
1224 l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
1225 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
1226 l_date_entered CONSTANT DATE := SYSDATE;
1227 l_zero_amount CONSTANT NUMBER := 0;
1228 l_first_line CONSTANT NUMBER := 1;
1229 l_line_step CONSTANT NUMBER := 1;
1230 l_def_no_val CONSTANT NUMBER := -1;
1231 l_null_kle_id CONSTANT NUMBER := -2;
1232
1233 ------------------------------------------------------------
1234 -- Declare records: i - insert, u - update, r - result
1235 ------------------------------------------------------------
1236
1237 -- Transaction headers
1238 i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1239 u_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1240 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1241
1242 -- Transaction lines
1243 i_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
1244 u_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
1245 r_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
1246
1247 -- Transaction line details
1248 i_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
1249 u_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
1250 l_init_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
1251 r_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
1252
1253 -- Ext Transaction Header
1254 i_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1255 l_init_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1256 r_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1257
1258 -- Ext Transaction Lines
1259 i_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1260 l_init_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1261 r_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1262
1263 -- Ext Transaction Details
1264 i_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
1265 l_init_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
1266 r_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
1267
1268
1269 ------------------------------------------------------------
1270 -- Declare local variables used in the program
1271 ------------------------------------------------------------
1272
1273 l_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
1274 l_use_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
1275 l_legal_entity_id okl_trx_ar_invoices_v.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
1276 l_x_legal_entity_id okl_ext_sell_invs_b.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
1277
1278 -- Bug 4524095
1279 --l_line_number okl_txl_ar_inv_lns_v.line_number%TYPE;
1280 --l_detail_number okl_txd_ar_ln_dtls_v.line_detail_number%TYPE;
1281 l_tld_id okl_txd_ar_ln_dtls_v.id%TYPE;
1282 l_xsi_id okl_ext_sell_invs_v.id%TYPE;
1283 l_xls_id okl_xtl_sell_invs_v.id%TYPE;
1284
1285 ------------------------------------------------------------
1286 -- Declare variables required by APIs
1287 ------------------------------------------------------------
1288
1289 l_api_version CONSTANT NUMBER := 1;
1290 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1291
1292 ------------------------------------------------------------
1293 -- Variables for Error Processing and Committing Stream Billing
1294 -- Transactions
1295 ------------------------------------------------------------
1296
1297 l_error_status VARCHAR2(1);
1298 l_error_message VARCHAR2(2000);
1299 l_trx_status_code Okl_trx_ar_invoices_v.trx_status_code%TYPE;
1300
1301
1302 -- For currecy precision rounded amount
1303 l_ste_amount OKL_STRM_ELEMENTS.amount%TYPE := 0;
1304 l_curr_code okc_k_headers_b.currency_code%TYPE;
1305
1306 -------------------------------------------------------------------------
1307 -- Account Builder Code
1308 -------------------------------------------------------------------------
1309 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
1310 l_init_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
1311
1312
1313 TYPE sel_err_rec_type IS RECORD (
1314 sel_id NUMBER,
1315 tld_id NUMBER,
1316 xsi_id NUMBER,
1317 bill_date DATE,
1318 contract_number okc_k_headers_b.contract_number%TYPE,
1319 stream_name okl_strm_type_v.name%TYPE,
1320 amount OKL_STRM_ELEMENTS.amount%TYPE,
1321 error_message VARCHAR2(2000)
1322 );
1323
1324 TYPE sel_err_tbl_type IS TABLE OF sel_err_rec_type
1325 INDEX BY BINARY_INTEGER;
1326
1327 sel_error_log_table sel_err_tbl_type;
1328 l_init_sel_table sel_err_tbl_type;
1329
1330 l_sel_tab_index NUMBER;
1331
1332 ------------------------------------------------------------
1333 -- Cursors for Rule based values
1334 ------------------------------------------------------------
1335 --added for rules migration
1336 CURSOR cust_id_csr(p_khr_id NUMBER) IS
1337 SELECT cust_acct_id
1338 FROM okc_k_headers_v
1339 WHERE id = p_khr_id;
1340
1341 --added for rules migration
1342 CURSOR cust_acct_csr (p_khr_id NUMBER) IS
1343 SELECT cs.cust_acct_site_id
1344 , cp.standard_terms payment_term_id
1345 FROM okc_k_headers_v khr
1346 , okx_cust_site_uses_v cs
1347 , hz_customer_profiles cp
1348 WHERE khr.id = p_khr_id
1349 AND khr.bill_to_site_use_id = cs.id1
1350 AND khr.bill_to_site_use_id = cp.site_use_id(+);
1351
1352 -- nikshah -- Bug # 5484903 Fixed,
1353 -- Changed CURSOR line_bill_to_csr SQL definition
1354 CURSOR line_bill_to_csr(p_khr_id NUMBER, p_kle_id NUMBER) IS
1355 SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
1356 FROM okc_k_headers_b khr
1357 , okx_cust_site_uses_v cs
1358 , okc_k_lines_b cle
1359 , hz_customer_profiles cp
1360 WHERE khr.id = p_khr_id
1361 AND cle.dnz_chr_id = khr.id
1362 AND cle.chr_id IS NOT NULL
1363 AND cle.id = p_kle_id
1364 AND cle.BILL_TO_SITE_USE_ID = cs.id1
1365 AND khr.bill_to_site_use_id = cp.site_use_id(+)
1366 UNION
1367 SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
1368 FROM okc_k_headers_b khr
1369 , okc_k_lines_b cle
1370 , okc_k_items item
1371 , okc_k_lines_b linked_asset
1372 , okx_cust_site_uses_v cs
1373 , hz_customer_profiles cp
1374 WHERE khr.id = p_khr_id
1375 AND cle.dnz_chr_id = khr.id
1376 AND cle.id = p_kle_id
1377 AND cle.chr_id IS NULL
1378 AND cle.id = item.cle_id
1379 AND item.object1_id1 = linked_asset.id
1380 AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
1381 AND khr.bill_to_site_use_id = cp.site_use_id(+);
1382
1383 -- Receipt Method Or Payment method
1384 CURSOR cust_pmth_csr ( p_khr_id NUMBER ) IS
1385 SELECT object1_id1
1386 FROM OKC_RULES_B rul,
1387 Okc_rule_groups_B rgp
1388 WHERE rul.rgp_id = rgp.id AND
1389 rgp.rgd_code = 'LABILL' AND
1390 rgp.dnz_chr_id = rgp.chr_id AND
1391 rul.rule_information_category = 'LAPMTH' AND
1392 rgp.dnz_chr_id = p_khr_id;
1393
1394 CURSOR cust_line_pmth_csr ( p_khr_id NUMBER, p_kle_id NUMBER ) IS
1395 SELECT object1_id1
1396 FROM OKC_RULES_B rul,
1397 Okc_rule_groups_B rgp
1398 WHERE rul.rgp_id = rgp.id AND
1399 rgp.rgd_code = 'LABILL' AND
1400 rgp.cle_id = p_kle_id AND
1401 rul.rule_information_category = 'LAPMTH' AND
1402 rgp.dnz_chr_id = p_khr_id
1403 UNION
1404 SELECT rul.object1_id1
1405 FROM okc_k_lines_b cle
1406 , okc_k_items_v item
1407 , okc_k_lines_b linked_asset
1408 , OKC_RULES_B rul
1409 , Okc_rule_groups_B rgp
1410 WHERE cle.dnz_chr_id = p_khr_id AND
1411 cle.id = p_kle_id AND
1412 cle.chr_id IS NULL AND
1413 cle.id = item.cle_id AND
1414 item.object1_id1 = linked_asset.id AND
1415 linked_asset.id = rgp.cle_id AND
1416 linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
1417 rgp.rgd_code = 'LABILL' AND
1418 rul.rgp_id = rgp.id AND
1419 rul.rule_information_category = 'LAPMTH';
1420
1421
1422 CURSOR rcpt_mthd_csr(p_cust_rct_mthd NUMBER) IS
1423 SELECT C.RECEIPT_METHOD_ID
1424 FROM RA_CUST_RECEIPT_METHODS C
1425 WHERE C.cust_receipt_method_id = p_cust_rct_mthd;
1426
1427 -- Bank Account Cursor
1428 CURSOR rcpt_method_csr ( p_rct_method_id NUMBER) IS
1429 SELECT C.CREATION_METHOD_CODE
1430 FROM AR_RECEIPT_METHODS M,
1431 AR_RECEIPT_CLASSES C
1432 WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
1433 M.receipt_method_id = p_rct_method_id;
1434
1435 CURSOR cust_bank_csr ( p_khr_id NUMBER ) IS
1436 SELECT object1_id1
1437 FROM OKC_RULES_B rul,
1438 Okc_rule_groups_B rgp
1439 WHERE rul.rgp_id = rgp.id AND
1440 rgp.rgd_code = 'LABILL' AND
1441 rgp.dnz_chr_id = rgp.chr_id AND
1442 rul.rule_information_category = 'LABACC' AND
1443 rgp.dnz_chr_id = p_khr_id;
1444
1445 CURSOR cust_line_bank_csr ( p_khr_id NUMBER, p_kle_id NUMBER ) IS
1446 SELECT object1_id1
1447 FROM OKC_RULES_B rul,
1448 Okc_rule_groups_B rgp
1449 WHERE rul.rgp_id = rgp.id AND
1450 rgp.cle_id = p_kle_id AND
1451 rgp.rgd_code = 'LABILL' AND
1452 rul.rule_information_category = 'LABACC' AND
1453 rgp.dnz_chr_id = p_khr_id
1454 UNION
1455 SELECT rul.object1_id1
1456 FROM okc_k_lines_b cle
1457 , okc_k_items_v item
1458 , okc_k_lines_b linked_asset
1459 , OKC_RULES_B rul
1460 , Okc_rule_groups_B rgp
1461 WHERE cle.dnz_chr_id = p_khr_id AND
1462 cle.id = p_kle_id AND
1463 cle.chr_id IS NULL AND
1464 cle.id = item.cle_id AND
1465 item.object1_id1 = linked_asset.id AND
1466 linked_asset.id = rgp.cle_id AND
1467 linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
1468 rgp.rgd_code = 'LABILL' AND
1469 rul.rgp_id = rgp.id AND
1470 rul.rule_information_category = 'LABACC';
1471
1472 CURSOR bank_acct_csr(p_id1 NUMBER) IS
1473 SELECT bank_account_id
1474 FROM OKX_RCPT_METHOD_ACCOUNTS_V
1475 WHERE id1 = p_id1;
1476
1477 --Get currency conversion attributes for a contract
1478 CURSOR l_curr_conv_csr( cp_khr_id NUMBER ) IS
1479 SELECT currency_code
1480 ,currency_conversion_type
1481 ,currency_conversion_rate
1482 ,currency_conversion_date
1483 FROM okl_k_headers_full_v
1484 WHERE id = cp_khr_id;
1485
1486 -- nikshah -- Bug # 5484903 Fixed,
1487 -- Changed CURSOR std_terms_csr SQL definition
1488 -- Default term Id
1489 CURSOR std_terms_csr IS
1490 SELECT B.term_id
1491 FROM ra_terms_b b, ra_terms_tl t
1492 WHERE t.name = 'IMMEDIATE'
1493 and B.TERM_ID = T.TERM_ID
1494 and T.LANGUAGE = userenv('LANG');
1495
1496 --Get product Id
1497 CURSOR pdt_id_csr( p_khr_id NUMBER ) IS
1498 SELECT pdt_id
1499 FROM okl_k_headers_full_v
1500 WHERE id = p_khr_id;
1501 -- Variables for XTD Accounting Distributions
1502
1503 l_esd_acc_cc_id OKL_AE_TMPT_LNES.code_combination_id%TYPE;
1504 l_esd_ae_ln_type OKL_AE_TMPT_LNES.ae_line_type%TYPE;
1505 l_esd_crd_code OKL_AE_TMPT_LNES.crd_code%TYPE;
1506 l_esd_acc_bldr_yn OKL_AE_TMPT_LNES.account_builder_yn%TYPE;
1507 l_esd_percentage OKL_AE_TMPT_LNES.percentage%TYPE;
1508 l_esd_amount OKL_XTD_SELL_INVS_V.amount%TYPE;
1509
1510 -- Create Distributions
1511 CURSOR dstrs_csr( p_pdt_id NUMBER, p_try_id NUMBER, p_sty_id NUMBER, p_date DATE) IS
1512 SELECT
1513 C.CODE_COMBINATION_ID,
1514 C.AE_LINE_TYPE,
1515 C.CRD_CODE,
1516 C.ACCOUNT_BUILDER_YN,
1517 C.PERCENTAGE
1518 FROM OKL_AE_TEMPLATES A,
1519 OKL_PRODUCTS_V B,
1520 OKL_AE_TMPT_LNES C
1521 WHERE A.aes_id = b.aes_id AND
1522 A.start_date <= p_date AND
1523 (A.end_date IS NULL OR A.end_date >= p_date) AND
1524 A.memo_yn = 'N' AND
1525 b.id = p_pdt_id AND
1526 a.sty_id = p_sty_id AND
1527 a.try_id = p_try_id AND
1528 C.avl_id = A.id;
1529
1530 --added variables for bankruptcy hold
1531 l_turnoff_inv_on_bankruptcy VARCHAR2(1) := 'N';
1532 l_disposition_code iex_bankruptcies.disposition_code%TYPE;
1533 -- l_bankruptcy_flag VARCHAR2(1) := 'N'; --Commented by Venkatho as part of Bug# 14119181
1534 l_bankruptcy_flag VARCHAR2(1); --Added by Venkatho as part of Bug# 14119181
1535 l_previous_khr OKL_K_HEADERS.khr_id%TYPE;
1536
1537 -- -------------------------------------------
1538 -- To support new fields in XSI and XLS
1539 -- -------------------------------------------
1540 -- rseela BUG# 4733028 Start: fetching review invoice flag
1541 CURSOR inv_frmt_csr(cp_khr_id IN NUMBER) IS
1542 SELECT to_number(rul.rule_information1), --inf.id, --sechawla 26-may-09 6826580
1543 rul.rule_information4 review_invoice_yn
1544 FROM okc_rule_groups_v rgp,
1545 okc_rules_v rul
1546 -- ,okl_invoice_formats_v inf --sechawla 26-may-09 6826580
1547 WHERE rgp.dnz_chr_id = cp_khr_id AND
1548 rgp.chr_id = rgp.dnz_chr_id AND
1549 rgp.id = rul.rgp_id AND
1550 rgp.cle_id IS NULL AND
1551 rgp.rgd_code = 'LABILL' AND
1552 rul.rule_information_category = 'LAINVD' ;
1553 -- AND rul.rule_information1 = inf.name;
1554
1555 -- Bug 4540379
1556 --l_inf_id okl_invoice_formats_v.id%TYPE;
1557 -- End Bug 4540379
1558
1559 CURSOR get_languages IS
1560 SELECT *
1561 FROM FND_LANGUAGES
1562 WHERE INSTALLED_FLAG IN ('I', 'B');
1563
1564 l_request_id NUMBER(15);
1565 l_program_application_id NUMBER(15);
1566 l_program_id NUMBER(15);
1567 l_program_update_date DATE;
1568 l_review_invoice_yn okc_rules_v.rule_information4%type;
1569
1570 -- -------------------------------------------
1571 -- To support private label transfers to
1572 -- AR. Bug 4525643
1573 -- -------------------------------------------
1574 CURSOR pvt_label_csr(cp_khr_id IN NUMBER) IS
1575 SELECT Rule_information1 PRIVATE_LABEL
1576 FROM okc_rule_groups_b A,
1577 okc_rules_b B
1578 WHERE A.DNZ_CHR_ID = CP_KHR_ID
1579 AND A.rgd_code = 'LALABL'
1580 AND A.id = B.rgp_id
1581 AND B.rule_information_category = 'LALOGO';
1582
1583 -- Bug 4540379
1584 --l_private_label okc_rules_b.Rule_information1%type;
1585 -- End Bug 4540379
1586
1587 -- to get inventory_org_id bug 4890024 begin
1588 CURSOR inv_org_id_csr ( p_khr_id NUMBER ) IS
1589 SELECT nvl(inv_organization_id,-99)
1590 FROM okc_k_headers_b
1591 WHERE id = p_khr_id;
1592 -- bug 4890024 end
1593
1594 -- modified by zrehman for Bug#6788005 on 07-Feb-2008 start
1595 CURSOR check_if_inv(p_khr_id NUMBER ) IS
1596 SELECT 1
1597 FROM okc_k_headers_all_b
1598 WHERE id = p_khr_id
1599 AND scs_code = 'INVESTOR';
1600 l_is_inv NUMBER := 0;
1601 -- get cust_acct_id and cust_acct_site_id for investor fees
1602 CURSOR get_inv_cust_info(p_khr_id NUMBER, p_kle_id NUMBER) IS
1603 SELECT cle_inv.cust_acct_id, cs.cust_acct_site_id, hz.party_name
1604 ,hz.party_id -- Added by Venkatho as part of Bug# 15959293
1605 FROM okc_k_headers_b khr
1606 , okx_cust_site_uses_v cs
1607 , okc_k_lines_b cle
1608 , okc_k_lines_b cle_inv
1609 , okc_k_party_roles_b cpl
1610 , okc_k_party_roles_b cpl_inv
1611 , hz_parties hz
1612 WHERE khr.id = p_khr_id
1613 AND cle.dnz_chr_id = khr.id
1614 AND cle_inv.dnz_chr_id = khr.id
1615 AND cpl.rle_code = 'INVESTOR'
1616 AND cpl_inv.rle_code = 'INVESTOR'
1617 and cpl.cle_id = cle.id
1618 AND cpl.object1_id1 = cpl_inv.object1_id1
1619 AND cpl_inv.cle_id = cle_inv.id
1620 AND cle_inv.lse_id = 65
1621 AND cle.chr_id IS NOT NULL
1622 AND cle_inv.chr_id IS NOT NULL
1623 AND cle.id = p_kle_id
1624 AND cle_inv.BILL_TO_SITE_USE_ID = cs.id1
1625 and hz.party_id = cpl_inv.object1_id1;
1626 l_inv_cust_acct_id NUMBER;
1627 l_inv_cust_acct_site_id NUMBER;
1628 l_investor_name VARCHAR2(360);
1629 -- modified by zrehman for Bug#6788005 on 07-Feb-2008 end
1630
1631
1632 -- rmunjulu R12 Fixes -- added new variables
1633 l_temp_return_status VARCHAR2(3);
1634 lx_msg_count NUMBER;
1635 lx_msg_data VARCHAR2(3000);
1636 lx_invoice_format_type okl_invoice_types_v.name%TYPE;
1637 lx_invoice_format_line_type okl_invc_line_types_v.name%TYPE;
1638
1639 --Added by Venkatho as part of Bug# 14119181
1640 l_cust_acct_id NUMBER;
1641 l_cust_party_id NUMBER;
1642
1643 -- Added by Venkatho as part of Bug 15959293
1644 l_inv_party_id NUMBER;
1645
1646 BEGIN
1647
1648 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1649 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start PROCESS_BILL_TBL (+)');
1650 END IF;
1651 ------------------------------------------------------------
1652 -- Start processing
1653 ------------------------------------------------------------
1654 l_return_status := Okl_Api.START_ACTIVITY(
1655 p_api_name => l_api_name,
1656 p_pkg_name => G_PKG_NAME,
1657 p_init_msg_list => p_init_msg_list,
1658 l_api_version => l_api_version,
1659 p_api_version => p_api_version,
1660 p_api_type => '_PVT',
1661 x_return_status => l_return_status);
1662
1663 IF l_overall_err_sts IS NULL THEN
1664 l_overall_err_sts := Okl_Api.G_RET_STS_SUCCESS;
1665 END IF;
1666
1667 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1668 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1669 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start time Process_Bill_Tbl : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
1670 END IF;
1671 ------------------------------------------------------------
1672 -- If all records are processd, do bulk insert and update
1673 ------------------------------------------------------------
1674 IF p_end_of_records = 'Y' THEN
1675
1676 Fnd_File.PUT_LINE (Fnd_File.LOG, ' Done building TAI, TIL, TLD, XSI and XLS records ...');
1677
1678 bulk_process
1679 (p_api_version
1680 ,p_init_msg_list
1681 ,x_return_status
1682 ,x_msg_count
1683 ,x_msg_data
1684 ,p_commit
1685 ,p_source);
1686
1687 Fnd_File.PUT_LINE (Fnd_File.LOG, '=========================================================================================');
1688 Fnd_File.PUT_LINE (Fnd_File.LOG, ' ** End Processing. Please See Error Log for any errored transactions ** ');
1689 Fnd_File.PUT_LINE (Fnd_File.LOG, '=========================================================================================');
1690
1691 ELSE
1692
1693 ------------------------------------
1694 -- Process records
1695 ------------------------------------
1696
1697 ------------------------------------------------------------
1698 -- Initialise local variables
1699 ------------------------------------------------------------
1700 l_trx_type := get_trx_type (l_trx_type_name, l_trx_type_lang);
1701 l_func_curr_code := Okl_Accounting_Util.get_func_curr_code;
1702 l_ext_sob_id := Okl_Accounting_Util.GET_SET_OF_BOOKS_ID;
1703
1704 BEGIN
1705 SELECT
1706 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
1707 DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
1708 DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
1709 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
1710 INTO
1711 l_request_id,
1712 l_program_application_id,
1713 l_program_id,
1714 l_program_update_date
1715 FROM dual;
1716 EXCEPTION
1717 WHEN OTHERS THEN
1718 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1719 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'(Exception): When resolving request_id'||SQLERRM );
1720 END IF;
1721 Fnd_File.PUT_LINE (Fnd_File.LOG,'(Exception): When resolving request_id'||SQLERRM );
1722 END;
1723
1724 ------------------------------------------------------------
1725 -- Initialise table Index for error tables
1726 ------------------------------------------------------------
1727 sel_error_log_table := l_init_sel_table;
1728 l_sel_tab_index := 0;
1729
1730 ------------------------------------------------------------
1731 -- Process every stream to be billed
1732 ------------------------------------------------------------
1733
1734 Fnd_File.PUT_LINE (Fnd_File.LOG, '=========================================================================================');
1735 Fnd_File.PUT_LINE (Fnd_File.LOG, ' ** Start Processing. Please See Error Log for any errored transactions ** ');
1736 Fnd_File.PUT_LINE (Fnd_File.LOG, '=========================================================================================');
1737
1738 -- Start of code commmented by Venkatho as part of Bug# 14119181
1739
1740 /* --check if invoicing is to be turned off if a party is bankrupt
1741 Fnd_Profile.get('IEX_TURNOFF_INVOICE_BANKRUPTCY' , l_turnoff_inv_on_bankruptcy);
1742 l_turnoff_inv_on_bankruptcy := NVL(l_turnoff_inv_on_bankruptcy, 'N');
1743 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1744 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Turnoff invoice on bankruptcy => ' || l_turnoff_inv_on_bankruptcy);
1745 END IF; */
1746
1747 -- End of code commented by Venkatho as part of Bug# 14119181
1748
1749
1750 -- Build table records for bulk processing
1751
1752 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Building TAI, TIL, TLD, XSI and XLS records ...');
1753
1754 FOR k IN p_bill_tbl.FIRST..p_bill_tbl.LAST LOOP
1755 -- ******************************************************
1756 -- Below IF logic is commented by Venkatho as part of Bug# 14119181
1757
1758 /* IF (l_turnoff_inv_on_bankruptcy = 'Y') THEN
1759 IF (l_previous_khr IS NULL OR l_previous_khr <> p_bill_tbl(k).khr_id)THEN
1760 l_bankruptcy_flag := get_bankruptcy_status
1761 (p_bill_tbl(k).khr_id
1762 , l_disposition_code);
1763
1764 l_previous_khr := p_bill_tbl(k).khr_id;
1765
1766 IF (l_bankruptcy_flag = 'Y') THEN
1767 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1768 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices of contract => ' || p_bill_tbl(k).contract_number || ' not billed due to bankruptcy.');
1769 END IF;
1770 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Invoices of contract => ' || p_bill_tbl(k).contract_number || ' not billed due to bankruptcy.');
1771 END IF;
1772 END IF;
1773 END IF; */
1774
1775 -- Start of code added by Venkatho as part of Bug# 14119181
1776 -- Check for Inv Agreement, if yes then check if Investor is on Bankruptcy
1777 l_is_inv := NULL;
1778
1779 OPEN check_if_inv(p_bill_tbl(k).khr_id);
1780 FETCH check_if_inv INTO l_is_inv;
1781
1782 IF ( check_if_inv%NOTFOUND ) THEN
1783 l_is_inv := 0;
1784 ELSE
1785 l_is_inv := 1;
1786 --l_bankruptcy_flag := 'N'; -- Commented as part of Bug: 15959293
1787
1788 -- Added as part of Bug: 15959293 - START
1789 l_inv_cust_acct_id := NULL;
1790 l_inv_cust_acct_site_id := NULL;
1791 l_investor_name := NULL;
1792 l_inv_party_id := NULL;
1793
1794 OPEN get_inv_cust_info(p_bill_tbl(k).khr_id, p_bill_tbl(k).kle_id);
1795 FETCH get_inv_cust_info INTO l_inv_cust_acct_id, l_inv_cust_acct_site_id, l_investor_name, l_inv_party_id;
1796 CLOSE get_inv_cust_info;
1797
1798 -- Added as part of Bug: 15959293 - END
1799
1800 END IF;
1801
1802 CLOSE check_if_inv;
1803
1804 IF ( (l_previous_khr IS NULL OR l_previous_khr <> p_bill_tbl(k).khr_id) ) -- Changed as part of Bug: 15959293
1805 THEN
1806
1807
1808 l_bankruptcy_flag := NULL; -- Added as part of Bug: 15959293
1809
1810 IF (l_is_inv = 0) -- Added as part of Bug: 15959293
1811 THEN -- Added as part of Bug: 15959293
1812
1813 l_cust_acct_id := NULL;
1814 l_cust_party_id := NULL;
1815
1816 IF (p_bill_tbl(k).khr_id IS NOT NULL )
1817 THEN
1818
1819 Okl_Stream_Billing_Pvt.get_customer_dtls (p_khr_id => p_bill_tbl(k).khr_id,
1820 p_cust_acct_id => l_cust_acct_id,
1821 p_cust_party_id => l_cust_party_id);
1822 END IF;
1823
1824 IF (l_cust_acct_id IS NOT NULL AND l_cust_party_id IS NOT NULL)
1825 THEN
1826
1827 l_bankruptcy_flag := Okl_Stream_Billing_Pvt.get_bankruptcy_status_new (p_cust_acct_id => l_cust_acct_id,
1828 p_cust_party_id => l_cust_party_id
1829 );
1830 END IF;
1831
1832 --l_previous_khr := p_bill_tbl(k).khr_id; -- Commented as part of Bug: 15959293
1833
1834 IF (l_bankruptcy_flag = 'Y') THEN
1835
1836 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
1837 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices of contract => ' || p_bill_tbl(k).contract_number || ' not billed due to bankruptcy.');
1838 END IF;
1839 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoices of contract => ' || p_bill_tbl(k).contract_number || ' not billed due to bankruptcy.');
1840
1841 END IF;
1842
1843 -- Added as part of Bug: 15959293 -- START
1844 ELSIF (l_is_inv = 1 AND l_inv_cust_acct_id IS NOT NULL AND l_inv_party_id IS NOT NULL )
1845 THEN
1846
1847 l_bankruptcy_flag := Okl_Stream_Billing_Pvt.get_bankruptcy_status_new (p_cust_acct_id => l_inv_cust_acct_id,
1848 p_cust_party_id => l_inv_party_id
1849 );
1850 IF (l_bankruptcy_flag = 'Y') THEN
1851
1852 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
1853 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices of contract => ' || p_bill_tbl(k).contract_number || ' not billed due to bankruptcy of Investor.');
1854 END IF;
1855 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoices of contract => ' || p_bill_tbl(k).contract_number || ' not billed due to bankruptcy of Investor.');
1856
1857 END IF;
1858
1859 END IF;
1860
1861 l_previous_khr := p_bill_tbl(k).khr_id;
1862 -- Added as part of Bug: 15959293 -- END
1863
1864 END IF;
1865 -- End of code added by Venkatho as part of Bug# 14119181
1866
1867 IF (l_bankruptcy_flag = 'N') THEN
1868 l_commit_cnt := l_commit_cnt + 1;
1869 ---------------------------------------------------------------
1870 -- Start with a Clean State
1871 ---------------------------------------------------------------
1872 l_error_message := NULL;
1873 ----------------------------------------------------
1874 -- Create new transaction header for every
1875 -- contract and bill_date combination
1876 ----------------------------------------------------
1877
1878 IF l_khr_id <> p_bill_tbl(k).khr_id
1879 OR l_bill_date <> p_bill_tbl(k).bill_date
1880 --Bug# 9690217
1881 OR SIGN(l_bill_amount) <> SIGN(p_bill_tbl(k).amount) THEN
1882
1883 Fnd_File.PUT_LINE (Fnd_File.LOG, ' Contract Number : '||p_bill_tbl(k).contract_number||', Bill Date : '||p_bill_tbl(k).bill_date);
1884
1885 l_tai_cnt := l_tai_cnt + 1;
1886
1887 -- Check if commit point reached
1888 IF l_commit_cnt > l_max_commit_cnt THEN
1889
1890 Fnd_File.PUT_LINE (Fnd_File.LOG, ' Done building TAI, TIL, TLD, XSI and XLS records ...');
1891
1892 -- Bulk insert/update records, Commit and restart
1893 bulk_process
1894 (p_api_version
1895 ,p_init_msg_list
1896 ,x_return_status
1897 ,x_msg_count
1898 ,x_msg_data
1899 ,p_commit
1900 ,p_source);
1901
1902 l_commit_cnt := 0;
1903
1904 END IF;
1905 ------------------------------------------------
1906 -- Reset the flag to S whenever creating a new
1907 -- Transaction Header
1908 ------------------------------------------------
1909 l_error_status := 'S';
1910
1911 -- Bulk insert
1912 ---------------------------------------------
1913 -- Populate required columns
1914 ---------------------------------------------
1915 -- for LE Uptake project 07-11-2006
1916 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_bill_tbl(k).khr_id); -- for LE Uptake project 08-11-2006
1917 tai_tbl(l_tai_cnt).legal_entity_id := l_legal_entity_id; -- for LE Uptake project 08-11-2006
1918 -- for LE Uptake project 07-11-2006
1919 tai_tbl(l_tai_cnt).khr_id := p_bill_tbl(k).khr_id;
1920 tai_tbl(l_tai_cnt).date_invoiced := p_bill_tbl(k).bill_date;
1921 --start: cklee 3/8/07
1922 -- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
1923 -- for Investor, the value of OKL_SOURCE_BILLING_TRX is 'INVESTOR_STAKE'
1924 OPEN check_if_inv(p_bill_tbl(k).khr_id);
1925 FETCH check_if_inv INTO l_is_inv;
1926 if check_if_inv%NOTFOUND THEN
1927 l_is_inv := 0;
1928 else
1929 l_is_inv := 1;
1930 end if;
1931 CLOSE check_if_inv;
1932 IF (nvl(l_is_inv,0) =1 ) THEN
1933 tai_tbl(l_tai_cnt).OKL_SOURCE_BILLING_TRX := 'INVESTOR_STAKE';
1934 OPEN get_inv_cust_info(p_bill_tbl(k).khr_id, p_bill_tbl(k).kle_id);
1935 FETCH get_inv_cust_info INTO l_inv_cust_acct_id, l_inv_cust_acct_site_id, l_investor_name, l_inv_party_id; -- l_inv_party_id is added as part of Bug:15959293
1936 CLOSE get_inv_cust_info;
1937 tai_tbl(l_tai_cnt).INVESTOR_AGREEMENT_NUMBER := p_bill_tbl(k).contract_number;
1938 tai_tbl(l_tai_cnt).INVESTOR_NAME := l_investor_name;
1939 ELSE
1940 tai_tbl(l_tai_cnt).OKL_SOURCE_BILLING_TRX := 'STREAM';
1941 END IF;
1942 -- modified by zrehman for Bug#6788005 on 04-Feb-2008 end
1943
1944 l_private_label := NULL;
1945 OPEN pvt_label_csr( p_bill_tbl(k).khr_id );
1946 FETCH pvt_label_csr INTO l_private_label;
1947 CLOSE pvt_label_csr;
1948 tai_tbl(l_tai_cnt).private_label := l_private_label;
1949 --end: cklee 3/8/07
1950
1951 IF p_bill_tbl(k).sts_code = 'EVERGREEN' THEN
1952 l_trx_type := get_trx_type ('Evergreen', 'US');
1953 tai_tbl(l_tai_cnt).try_id := l_trx_type;
1954 ELSE
1955 l_trx_type := get_trx_type ('Billing', 'US');
1956 tai_tbl(l_tai_cnt).try_id := l_trx_type;
1957 END IF;
1958
1959 tai_tbl(l_tai_cnt).date_entered := l_date_entered;
1960 tai_tbl(l_tai_cnt).trx_status_code := 'ERROR';
1961 tai_tbl(l_tai_cnt).amount := l_zero_amount;
1962
1963 ---------------------------------------------
1964 -- Columns to be populated later based on CONTRACT_ID
1965 ---------------------------------------------
1966 tai_tbl(l_tai_cnt).currency_code := NULL;
1967 tai_tbl(l_tai_cnt).currency_conversion_type := NULL;
1968 tai_tbl(l_tai_cnt).currency_conversion_rate := NULL;
1969 tai_tbl(l_tai_cnt).currency_conversion_date := NULL;
1970
1971 tai_tbl(l_tai_cnt).set_of_books_id := NULL;
1972 tai_tbl(l_tai_cnt).ibt_id := NULL;
1973 tai_tbl(l_tai_cnt).ixx_id := NULL;
1974 tai_tbl(l_tai_cnt).irm_id := NULL;
1975 tai_tbl(l_tai_cnt).irt_id := NULL;
1976 tai_tbl(l_tai_cnt).org_id := NULL;
1977 ---------------------------------------------
1978 -- Columns which are not used by stream billing
1979 ---------------------------------------------
1980 tai_tbl(l_tai_cnt).cra_id := NULL;
1981 tai_tbl(l_tai_cnt).tap_id := NULL;
1982 tai_tbl(l_tai_cnt).qte_id := NULL;
1983 tai_tbl(l_tai_cnt).tcn_id := NULL;
1984 tai_tbl(l_tai_cnt).svf_id := NULL;
1985 tai_tbl(l_tai_cnt).ipy_id := NULL;
1986 tai_tbl(l_tai_cnt).tai_id_reverses := NULL;
1987 tai_tbl(l_tai_cnt).amount_applied := NULL;
1988 tai_tbl(l_tai_cnt).pox_id := NULL;
1989 tai_tbl(l_tai_cnt).cpy_id := NULL;
1990 tai_tbl(l_tai_cnt).clg_id := NULL;
1991 ---------------------------------------------
1992 -- Other Mandatory Columns
1993 ---------------------------------------------
1994 tai_tbl(l_tai_cnt).CREATION_DATE := SYSDATE;
1995 tai_tbl(l_tai_cnt).CREATED_BY := Fnd_Global.USER_ID;
1996 tai_tbl(l_tai_cnt).LAST_UPDATE_DATE := SYSDATE;
1997 tai_tbl(l_tai_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
1998 tai_tbl(l_tai_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1999 tai_tbl(l_tai_cnt).OBJECT_VERSION_NUMBER := 1;
2000 l_header_id := Okc_P_Util.raw_to_number(sys_guid());
2001 tai_tbl(l_tai_cnt).ID := l_header_id;
2002 tai_tbl(l_tai_cnt).trx_number := SUBSTR(TO_CHAR(l_header_id),-6);
2003 tai_tbl(l_tai_cnt).request_id := l_request_id;
2004 tai_tbl(l_tai_cnt).program_application_id := l_program_application_id;
2005 tai_tbl(l_tai_cnt).program_id := l_program_id;
2006 tai_tbl(l_tai_cnt).program_update_date := l_program_update_date;
2007
2008 --gkhuntet start 02-Nov-2007
2009 tai_tbl(l_tai_cnt).transaction_date := SYSDATE;
2010 --gkhuntet end 02-Nov-2007
2011
2012 ---------------------------------------------
2013 -- Create TAI_TL records
2014 ---------------------------------------------
2015
2016 FOR l_lang_rec IN get_languages LOOP
2017 taitl_tbl(l_taitl_cnt).ID := l_header_id;
2018 taitl_tbl(l_taitl_cnt).LANGUAGE := l_lang_rec.language_code;
2019 taitl_tbl(l_taitl_cnt).SOURCE_LANG := USERENV('LANG');
2020 taitl_tbl(l_taitl_cnt).SFWT_FLAG := 'N';
2021 taitl_tbl(l_taitl_cnt).DESCRIPTION := l_def_desc;
2022
2023 taitl_tbl(l_taitl_cnt).CREATION_DATE := SYSDATE;
2024 taitl_tbl(l_taitl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2025 taitl_tbl(l_taitl_cnt).LAST_UPDATE_DATE := SYSDATE;
2026 taitl_tbl(l_taitl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2027 taitl_tbl(l_taitl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2028
2029 l_taitl_cnt := l_taitl_cnt + 1;
2030 END LOOP;
2031 ------------------------------------------------
2032 -- Resolve T and C
2033 ------------------------------------------------
2034 l_ext_receipt_method_id := NULL;
2035 l_ext_term_id := NULL;
2036 l_ext_customer_id := NULL;
2037 --l_ext_trx_type_id := NULL;
2038 l_ext_cust_bank_id := NULL;
2039 l_ext_addr_id := NULL;
2040 l_addr_id1 := NULL;
2041 l_pmth_id1 := NULL;
2042 l_bank_id1 := NULL;
2043 l_rct_method_code := NULL;
2044 -- 5162232 Start
2045 -- l_asst_tax := NULL;
2046 -- 5162232 End
2047 l_product_id := NULL;
2048
2049 -- Newly added fields
2050 l_inf_id := NULL;
2051
2052 -- Multi-Currency Compliance
2053 l_currency_code := NULL;
2054 l_currency_conversion_type := NULL;
2055 l_currency_conversion_rate := NULL;
2056 l_currency_conversion_date := NULL;
2057
2058 -- Customer Id
2059 OPEN cust_id_csr ( p_bill_tbl(k).khr_id );
2060 FETCH cust_id_csr INTO l_ext_customer_id;
2061 CLOSE cust_id_csr;
2062
2063 OPEN cust_acct_csr ( p_bill_tbl(k).khr_id );
2064 FETCH cust_acct_csr INTO l_ext_addr_id, l_ext_term_id;
2065 CLOSE cust_acct_csr;
2066
2067 -- Force Term Id to be Immediate, ignoring the site
2068 -- level set up
2069 l_ext_term_id := NULL;
2070 OPEN std_terms_csr;
2071 FETCH std_terms_csr INTO l_ext_term_id;
2072 CLOSE std_terms_csr;
2073
2074 -- Payment Or Receipt Method Id
2075 OPEN cust_pmth_csr ( p_bill_tbl(k).khr_id );
2076 FETCH cust_pmth_csr INTO l_pmth_id1;
2077 CLOSE cust_pmth_csr;
2078
2079 OPEN rcpt_mthd_csr( l_pmth_id1 );
2080 FETCH rcpt_mthd_csr INTO l_ext_receipt_method_id;
2081 CLOSE rcpt_mthd_csr;
2082
2083 -- Evaluate Bank Account Id
2084 OPEN rcpt_method_csr (l_ext_receipt_method_id);
2085 FETCH rcpt_method_csr INTO l_rct_method_code;
2086 CLOSE rcpt_method_csr;
2087
2088 IF (l_rct_method_code <> 'MANUAL') THEN
2089 OPEN cust_bank_csr( p_bill_tbl(k).khr_id );
2090 FETCH cust_bank_csr INTO l_bank_id1;
2091 CLOSE cust_bank_csr;
2092
2093 OPEN bank_acct_csr( l_bank_id1 );
2094 FETCH bank_acct_csr INTO l_ext_cust_bank_id;
2095 CLOSE bank_acct_csr;
2096 END IF;
2097
2098 -- Multi Currency Compliance
2099 FOR cur IN l_curr_conv_csr( p_bill_tbl(k).khr_id ) LOOP
2100 l_currency_code := cur.currency_code;
2101 l_currency_conversion_type := cur.currency_conversion_type;
2102 l_currency_conversion_rate := cur.currency_conversion_rate;
2103 l_currency_conversion_date := cur.currency_conversion_date;
2104 END LOOP;
2105
2106 -- To support old contracts without multi-currency fields
2107 IF l_currency_conversion_type IS NULL THEN
2108 l_currency_conversion_type := 'User';
2109 l_currency_conversion_rate := 1;
2110 l_currency_conversion_date := SYSDATE;
2111 END IF;
2112
2113 -- Product Id CSR
2114 OPEN pdt_id_csr( p_bill_tbl(k).khr_id );
2115 FETCH pdt_id_csr INTO l_product_id;
2116 CLOSE pdt_id_csr;
2117
2118 --
2119 OPEN inv_frmt_csr ( p_bill_tbl(k).khr_id );
2120 -- 4733028
2121 FETCH inv_frmt_csr INTO l_inf_id, l_review_invoice_yn;
2122 CLOSE inv_frmt_csr;
2123
2124 ---------------------------------------------
2125 -- Adjust header variables
2126 ---------------------------------------------
2127 l_line_number := l_first_line;
2128 l_header_amount := l_zero_amount;
2129
2130 -- l_header_id := r_taiv_rec.id;
2131 -- Replace for non-TAPI code testing
2132 -- l_header_id := i_taiv_rec.ID
2133
2134 END IF;
2135
2136 ----------------------------------------------------
2137 -- Create new transaction line for every
2138 -- contract line and bill_date combination
2139 ----------------------------------------------------
2140
2141 -- rmunjulu - Bug# 5715349 - Added one condition in IF
2142 -- Need to create billing TIL line every time a new
2143 -- contract is processed. This is more relevant in cases
2144 -- of contract level streams across contracts billed on
2145 -- same dates. In this case l_khr_id check will ensure TIL
2146 -- record creation
2147 IF l_khr_id <> p_bill_tbl(k).khr_id
2148 OR l_kle_id <> NVL (p_bill_tbl(k).kle_id, l_null_kle_id)
2149 -- IF l_kle_id <> NVL (p_bill_tbl(k).kle_id, l_null_kle_id)
2150 OR l_bill_date <> p_bill_tbl(k).bill_date
2151 --Bug# 9690217
2152 OR SIGN(l_bill_amount) <> SIGN(p_bill_tbl(k).amount) THEN
2153
2154 l_til_cnt := l_til_cnt + 1;
2155
2156 ---------------------------------------------
2157 -- Populate required columns
2158 ---------------------------------------------
2159 til_tbl(l_til_cnt).kle_id := p_bill_tbl(k).kle_id;
2160 til_tbl(l_til_cnt).line_number := l_line_number;
2161 til_tbl(l_til_cnt).tai_id := l_header_id;
2162 til_tbl(l_til_cnt).inv_receiv_line_code := l_line_code;
2163 til_tbl(l_til_cnt).amount := l_zero_amount;
2164
2165 ---------------------------------------------
2166 -- Columns which are not used by stream billing
2167 ---------------------------------------------
2168 til_tbl(l_til_cnt).til_id_reverses := NULL;
2169 til_tbl(l_til_cnt).tpl_id := NULL;
2170 til_tbl(l_til_cnt).acn_id_cost := NULL;
2171 til_tbl(l_til_cnt).sty_id := NULL;
2172 til_tbl(l_til_cnt).quantity := NULL;
2173 til_tbl(l_til_cnt).amount_applied := NULL;
2174 til_tbl(l_til_cnt).org_id := NULL;
2175 til_tbl(l_til_cnt).date_bill_period_end := NULL;
2176 til_tbl(l_til_cnt).date_bill_period_start := NULL;
2177 til_tbl(l_til_cnt).receivables_invoice_id := NULL;
2178
2179 --Bug# 4488818: Sales Tax changes
2180 til_tbl(l_til_cnt).qte_line_id := NULL;
2181 til_tbl(l_til_cnt).txs_trx_id := NULL;
2182
2183 l_line_id := Okc_P_Util.raw_to_number(sys_guid());
2184 til_tbl(l_til_cnt).ID := l_line_id;
2185
2186 til_tbl(l_til_cnt).OBJECT_VERSION_NUMBER := 1;
2187 til_tbl(l_til_cnt).CREATION_DATE := SYSDATE;
2188 til_tbl(l_til_cnt).CREATED_BY := Fnd_Global.USER_ID;
2189 til_tbl(l_til_cnt).LAST_UPDATE_DATE := SYSDATE;
2190 til_tbl(l_til_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2191 til_tbl(l_til_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2192
2193 til_tbl(l_til_cnt).request_id := l_request_id;
2194 til_tbl(l_til_cnt).program_application_id := l_program_application_id;
2195 til_tbl(l_til_cnt).program_id := l_program_id;
2196 til_tbl(l_til_cnt).program_update_date := l_program_update_date;
2197
2198 ---------------------------------------------
2199 -- Create TIL_TL records
2200 ---------------------------------------------
2201
2202 FOR l_lang_rec IN get_languages LOOP
2203 tiltl_tbl(l_tiltl_cnt).ID := l_line_id;
2204 tiltl_tbl(l_tiltl_cnt).LANGUAGE := l_lang_rec.language_code;
2205 tiltl_tbl(l_tiltl_cnt).SOURCE_LANG := USERENV('LANG');
2206 tiltl_tbl(l_tiltl_cnt).SFWT_FLAG := 'N';
2207 tiltl_tbl(l_tiltl_cnt).DESCRIPTION := l_def_desc;
2208
2209 tiltl_tbl(l_tiltl_cnt).CREATION_DATE := SYSDATE;
2210 tiltl_tbl(l_tiltl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2211 tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_DATE := SYSDATE;
2212 tiltl_tbl(l_tiltl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2213 tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2214
2215 l_tiltl_cnt := l_tiltl_cnt + 1;
2216 END LOOP;
2217
2218 ---------------------------------------------
2219 -- Adjust line variables
2220 ---------------------------------------------
2221 l_detail_number := l_first_line;
2222 l_line_amount := l_zero_amount;
2223
2224 -- l_line_id := r_tilv_rec.id;
2225 -- Replace for non-TAPI code testing
2226 -- l_line_id := i_tilv_rec.id;
2227
2228 l_line_number := l_line_number + l_line_step;
2229
2230 END IF;
2231
2232 ----------------------------------------------------
2233 -- Create new transaction line detail for every stream
2234 ----------------------------------------------------
2235
2236 ----------------------------------------------------
2237 -- Populate required columns
2238 ----------------------------------------------------
2239 -- Round to the Currency precision and rounding rules
2240 l_ste_amount := p_bill_tbl(k).amount;
2241 l_curr_code := p_bill_tbl(k).currency_code;
2242 l_ste_amount := Okl_Accounting_Util.cross_currency_round_amount
2243 (p_amount => l_ste_amount
2244 ,p_currency_code => l_curr_code);
2245
2246 tld_tbl(l_tld_cnt).amount := l_ste_amount;
2247 tld_tbl(l_tld_cnt).sel_id := p_bill_tbl(k).sel_id;
2248 tld_tbl(l_tld_cnt).sty_id := p_bill_tbl(k).sty_id;
2249 tld_tbl(l_tld_cnt).til_id_details := l_line_id;
2250 tld_tbl(l_tld_cnt).line_detail_number := l_detail_number;
2251
2252 ----------------------------------------------------
2253 -- Columns which are not used by stream billing
2254 ----------------------------------------------------
2255 tld_tbl(l_tld_cnt).tld_id_reverses := NULL;
2256 tld_tbl(l_tld_cnt).idx_id := NULL;
2257 tld_tbl(l_tld_cnt).late_charge_yn := NULL;
2258 tld_tbl(l_tld_cnt).date_calculation := NULL;
2259 tld_tbl(l_tld_cnt).fixed_rate_yn := NULL;
2260 tld_tbl(l_tld_cnt).receivables_invoice_id := NULL;
2261 tld_tbl(l_tld_cnt).amount_applied := NULL;
2262 tld_tbl(l_tld_cnt).bch_id := NULL;
2263 tld_tbl(l_tld_cnt).bgh_id := NULL;
2264 tld_tbl(l_tld_cnt).bcl_id := NULL;
2265 tld_tbl(l_tld_cnt).bsl_id := NULL;
2266 tld_tbl(l_tld_cnt).org_id := NULL;
2267 ----------------------------------------------------
2268 -- Other Columns
2269 ----------------------------------------------------
2270 --cklee: start 3/16/07
2271 open c_seq;
2272 fetch c_seq into l_seq;
2273 close c_seq;
2274 -- l_tld_id := Okc_P_Util.raw_to_number(sys_guid());
2275 l_tld_id := l_seq;
2276 --cklee: start 3/16/07
2277 tld_tbl(l_tld_cnt).ID := l_tld_id;
2278 tld_tbl(l_tld_cnt).OBJECT_VERSION_NUMBER := 1;
2279 tld_tbl(l_tld_cnt).CREATION_DATE := SYSDATE;
2280 tld_tbl(l_tld_cnt).CREATED_BY := Fnd_Global.USER_ID;
2281 tld_tbl(l_tld_cnt).LAST_UPDATE_DATE := SYSDATE;
2282 tld_tbl(l_tld_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2283 tld_tbl(l_tld_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2284
2285 tld_tbl(l_tld_cnt).request_id := l_request_id;
2286 tld_tbl(l_tld_cnt).program_application_id := l_program_application_id;
2287 tld_tbl(l_tld_cnt).program_id := l_program_id;
2288 tld_tbl(l_tld_cnt).program_update_date := l_program_update_date;
2289
2290 -- l_tld_cnt := l_tld_cnt + 1; -- rmunjulu R12 Fixes -- increment at the end
2291
2292 ---------------------------------------------
2293 -- Create TLD_TL records
2294 ---------------------------------------------
2295
2296 FOR l_lang_rec IN get_languages LOOP
2297 tldtl_tbl(l_tldtl_cnt).ID := l_tld_id;
2298 tldtl_tbl(l_tldtl_cnt).LANGUAGE := l_lang_rec.language_code;
2299 tldtl_tbl(l_tldtl_cnt).SOURCE_LANG := USERENV('LANG');
2300 tldtl_tbl(l_tldtl_cnt).SFWT_FLAG := 'N';
2301 tldtl_tbl(l_tldtl_cnt).DESCRIPTION := p_bill_tbl(k).sty_name;
2302
2303 tldtl_tbl(l_tldtl_cnt).CREATION_DATE := SYSDATE;
2304 tldtl_tbl(l_tldtl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2305 tldtl_tbl(l_tldtl_cnt).LAST_UPDATE_DATE := SYSDATE;
2306 tldtl_tbl(l_tldtl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2307 tldtl_tbl(l_tldtl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2308
2309 l_tldtl_cnt := l_tldtl_cnt + 1;
2310 END LOOP;
2311
2312 -- If negative, create a credit memo
2313 l_ext_trx_type_id := NULL;
2314 IF l_ste_amount > 0 THEN
2315 -- Trx Type Id
2316 OPEN c_trx_id ( l_ext_sob_id , p_bill_tbl(k).authoring_org_id );
2317 FETCH c_trx_id INTO l_ext_trx_type_id;
2318 CLOSE c_trx_id;
2319 ELSE
2320 -- Trx Type Id
2321 OPEN c_trx_id1 ( l_ext_sob_id , p_bill_tbl(k).authoring_org_id );
2322 FETCH c_trx_id1 INTO l_ext_trx_type_id;
2323 CLOSE c_trx_id1;
2324 -- Added for bug 9816211 - start
2325 l_trx_type := get_trx_type ('Credit Memo', 'US');
2326 tai_tbl(l_tai_cnt).try_id := l_trx_type;
2327 -- Added for bug 9816211 - End
2328
2329 END IF;
2330
2331 l_khr_id := p_bill_tbl(k).khr_id;
2332 l_bill_date := p_bill_tbl(k).bill_date;
2333 --Bug# 9690217
2334 l_bill_amount := p_bill_tbl(k).amount;
2335 l_kle_id := NVL (p_bill_tbl(k).kle_id, l_null_kle_id);
2336 l_header_amount := l_header_amount + l_ste_amount;
2337 l_line_amount := l_line_amount + l_ste_amount;
2338 l_detail_number := l_detail_number + l_line_step;
2339
2340 tai_tbl(l_tai_cnt).amount := l_header_amount;
2341 til_tbl(l_til_cnt).amount := l_line_amount;
2342
2343 -- rmunjulu R12 Fixes -- Populate NEW columns in tai_tbl -- start
2344 tai_tbl(l_tai_cnt).inf_id := l_inf_id; -- okl consolidate invoice format id
2345 tai_tbl(l_tai_cnt).invoice_pull_yn := l_review_invoice_yn;
2346 --tai_tbl(l_tai_cnt).due_date := ; rmunjulu R12 Fixes -- not need as used for legacy data
2347 tai_tbl(l_tai_cnt).isi_id := NULL;
2348 tai_tbl(l_tai_cnt).receivables_invoice_id := NULL;
2349 tai_tbl(l_tai_cnt).cust_trx_type_id := l_ext_trx_type_id;
2350 tai_tbl(l_tai_cnt).customer_bank_account_id := l_ext_cust_bank_id;
2351 tai_tbl(l_tai_cnt).tax_exempt_flag := 'S';
2352 tai_tbl(l_tai_cnt).tax_exempt_reason_code := NULL;
2353 tai_tbl(l_tai_cnt).reference_line_id := NULL;
2354 tai_tbl(l_tai_cnt).private_label := l_private_label;
2355 -- rmunjulu R12 Fixes -- Populate NEW columns in tai_tbl -- end
2356
2357 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns of tai_tbl -- start
2358 -- modified by zrehman for Bug#6788005 on 12-Feb-2008 start
2359 IF(nvl(l_is_inv,0) <>1 ) THEN
2360 tai_tbl(l_tai_cnt).ixx_id := l_ext_customer_id;
2361 ELSE
2362 tai_tbl(l_tai_cnt).ixx_id := l_inv_cust_acct_id;
2363 END IF;
2364 -- modified by zrehman for Bug#6788005 on 12-Feb-2008 end
2365 tai_tbl(l_tai_cnt).irm_id := l_ext_receipt_method_id;
2366 IF l_ste_amount > 0 THEN
2367 tai_tbl(l_tai_cnt).irt_id := l_ext_term_id;
2368 END IF;
2369 -- modified by zrehman for Bug#6788005 on 12-Feb-2008 start
2370 IF(nvl(l_is_inv,0) <>1) THEN
2371 tai_tbl(l_tai_cnt).ibt_id := l_ext_addr_id;
2372 ELSE
2373 tai_tbl(l_tai_cnt).ibt_id := l_inv_cust_acct_site_id;
2374 END IF;
2375 -- modified by zrehman for Bug#6788005 on 12-Feb-2008 end
2376 tai_tbl(l_tai_cnt).set_of_books_id := l_ext_sob_id;
2377 tai_tbl(l_tai_cnt).currency_code := l_currency_code;
2378 tai_tbl(l_tai_cnt).currency_conversion_type := l_currency_conversion_type;
2379
2380 --DO currency conversion rate and date based on type
2381 IF (l_currency_conversion_type = 'User') THEN
2382 IF (l_currency_code = l_func_curr_code) THEN
2383 l_currency_conversion_rate := 1;
2384 ELSE
2385 l_currency_conversion_rate := l_currency_conversion_rate;
2386 END IF;
2387 --Check for currency conversion date - forward port bug 5466577
2388 l_currency_conversion_date := l_currency_conversion_date;
2389 ELSE
2390 l_currency_conversion_rate := NULL;
2391 --Check for currency conversion date - forward port bug 5466577
2392 l_currency_conversion_date := p_bill_tbl(k).bill_date;
2393 END IF;
2394
2395 tai_tbl(l_tai_cnt).currency_conversion_rate := l_currency_conversion_rate;
2396 tai_tbl(l_tai_cnt).currency_conversion_date := l_currency_conversion_date;
2397 tai_tbl(l_tai_cnt).ORG_ID := p_bill_tbl(k).authoring_org_id;
2398 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns of tai_tbl -- end
2399
2400 -- rmunjulu R12 Fixes -- Populate NEW columns in til_tbl
2401 -- not needed for the 2 new columns that were added to OKL_TXL_AR_INV_LNS
2402
2403 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns in til_tbl -- start
2404 til_tbl(l_til_cnt).ISL_ID := 1;
2405 til_tbl(l_til_cnt).ORG_ID := p_bill_tbl(k).authoring_org_id;
2406 til_tbl(l_til_cnt).inv_receiv_line_code := l_line_code;
2407 til_tbl(l_til_cnt).QUANTITY := 1;
2408 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns in til_tbl -- end
2409
2410 -- rmunjulu R12 Fixes -- Populate NEW columns in tld_tbl -- start
2411 -- tld_tbl(l_tld_cnt).CONSOLIDATED_INVOICE_NUMBER := ; -- no need to populate as used for legacy data
2412 tld_tbl(l_tld_cnt).KHR_ID := p_bill_tbl(k).khr_id; -- need to populate this khr_id which is the denormalized one
2413 -- rmunjulu R12 Fixes -- Populate NEW columns in tld_tbl -- end
2414 tld_tbl(l_tld_cnt).KLE_ID := p_bill_tbl(k).kle_id; -- cklee 3/22/07 - added okc_k_lines_b.id as FK directly
2415
2416 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns in tld_tbl -- start
2417
2418 -- make call to get invoice format API to get the formats
2419 OKL_INTERNAL_BILLING_PVT.Get_Invoice_format(
2420 p_api_version => p_api_version
2421 ,p_init_msg_list => OKL_API.G_FALSE
2422 ,x_return_status => l_return_status
2423 ,x_msg_count => lx_msg_count
2424 ,x_msg_data => lx_msg_data
2425 ,p_inf_id => tai_tbl(l_tai_cnt).inf_id
2426 ,p_sty_id => tld_tbl(l_tld_cnt).sty_id
2427 ,x_invoice_format_type => lx_invoice_format_type
2428 ,x_invoice_format_line_type => lx_invoice_format_line_type);
2429
2430 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2431 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2432 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2433 RAISE OKL_API.G_EXCEPTION_ERROR;
2434 END IF;
2435
2436 tld_tbl(l_tld_cnt).invoice_format_type := lx_invoice_format_type;
2437 tld_tbl(l_tld_cnt).invoice_format_line_type := lx_invoice_format_line_type;
2438 -- rmunjulu R12 Fixes -- Populate ADDITIONAL columns in tld_tbl -- end
2439
2440 ------------------------------------------------------------
2441 -- Create record with all record id
2442 ------------------------------------------------------------
2443 all_rec_tbl(l_art_index).tai_id := l_header_id;
2444 all_rec_tbl(l_art_index).til_id := l_line_id;
2445 all_rec_tbl(l_art_index).tld_id := l_tld_id;
2446 all_rec_tbl(l_art_index).sel_id := p_bill_tbl(k).sel_id;
2447 all_rec_tbl(l_art_index).xsi_id := l_xsi_id;
2448 all_rec_tbl(l_art_index).xls_id := l_xls_id;
2449 all_rec_tbl(l_art_index).contract_number := p_bill_tbl(k).contract_number;
2450 all_rec_tbl(l_art_index).stream_name := p_bill_tbl(k).sty_name;
2451 all_rec_tbl(l_art_index).bill_date := p_bill_tbl(k).bill_date;
2452
2453 l_art_index := l_art_index + 1;
2454 l_tld_cnt := l_tld_cnt + 1; -- rmunjulu R12 Fixes -- moved here
2455
2456 END IF;
2457 END LOOP; -- Loop thru bill_tbl of records
2458
2459 IF p_source = 'PRINCIPAL_PAYDOWN' THEN
2460 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2461 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Overall Error Status for PPD ' || l_overall_err_sts);
2462 END IF;
2463 x_return_status := l_overall_err_sts;
2464 END IF;
2465 -- ******************************************************
2466
2467 END IF;
2468
2469 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2470 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End time Process_Bill_Tbl : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
2471
2472 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End PROCESS_BILL_TBL (-)');
2473
2474
2475 END IF;
2476 Okl_Api.END_ACTIVITY (
2477 x_msg_count => x_msg_count,
2478 x_msg_data => x_msg_data);
2479
2480 EXCEPTION
2481 ------------------------------------------------------------
2482 -- Exception handling
2483 ------------------------------------------------------------
2484
2485 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2486 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (EXCP) => '||SQLERRM);
2487
2488 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2489 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
2490 'EXCEPTION (Process_bill_tbl):'||'OKL_API.G_EXCEPTION_ERROR');
2491 END IF;
2492
2493 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2494 p_api_name => l_api_name,
2495 p_pkg_name => G_PKG_NAME,
2496 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
2497 x_msg_count => x_msg_count,
2498 x_msg_data => x_msg_data,
2499 p_api_type => '_PVT');
2500
2501 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2502 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
2503
2504 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2505 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
2506 'EXCEPTION (Process_bill_tbl):'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
2507 END IF;
2508
2509 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2510 p_api_name => l_api_name,
2511 p_pkg_name => G_PKG_NAME,
2512 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2513 x_msg_count => x_msg_count,
2514 x_msg_data => x_msg_data,
2515 p_api_type => '_PVT');
2516
2517 WHEN OTHERS THEN
2518 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (OTHERS 3) => '||SQLERRM);
2519
2520 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
2521 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
2522 'EXCEPTION (Process_bill_tbl):'||'OTHERS');
2523 END IF;
2524
2525 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2526 p_api_name => l_api_name,
2527 p_pkg_name => G_PKG_NAME,
2528 p_exc_name => 'OTHERS',
2529 x_msg_count => x_msg_count,
2530 x_msg_data => x_msg_data,
2531 p_api_type => '_PVT');
2532 END Process_bill_tbl;
2533
2534
2535 -- ----------------------------------------------------------------
2536 -- Procedure bill_streams_master to bill outstanding stream elements
2537 -- ----------------------------------------------------------------
2538 PROCEDURE bill_streams_master
2539 (p_api_version IN NUMBER
2540 ,p_init_msg_list IN VARCHAR2
2541 ,x_return_status OUT NOCOPY VARCHAR2
2542 ,x_msg_count OUT NOCOPY NUMBER
2543 ,x_msg_data OUT NOCOPY VARCHAR2
2544 ,p_commit IN VARCHAR2
2545 ,p_ia_contract_type IN VARCHAR2 DEFAULT NULL --modified by zrehman for Bug#6788005 on 01-Feb-2008
2546 ,p_contract_number IN VARCHAR2
2547 ,p_from_bill_date IN DATE
2548 ,p_to_bill_date IN DATE
2549 ,p_cust_acct_id IN NUMBER
2550 ,p_inv_cust_acct_id IN NUMBER DEFAULT NULL --modified by zrehman for Bug#6788005 on 01-Feb-2008
2551 ,p_assigned_process IN VARCHAR2
2552 ,p_source IN VARCHAR2
2553 ,p_request_id IN NUMBER --Bug 7584183
2554 ) IS
2555
2556 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS_MASTER';
2557 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2558 l_api_version CONSTANT NUMBER := 1;
2559
2560 --Bug# 9690217: Added stream element amount to ORDER BY clause
2561 -- to support grouping positive amounts into invoices
2562 -- and negative amounts into credit memos
2563 CURSOR c1 IS
2564 --1st select for all streams other than Property tax streams
2565 SELECT stm.khr_id khr_id,
2566 TRUNC (ste.stream_element_date) bill_date,
2567 stm.kle_id kle_id,
2568 ste.id sel_id,
2569 stm.sty_id sty_id,
2570 khr.contract_number contract_number,
2571 khr.currency_code currency_code,
2572 khr.authoring_org_id authoring_org_id,
2573 sty.name comments,
2574 sty.taxable_default_yn taxable_default_yn,
2575 ste.amount amount,
2576 khr.sts_code sts_code
2577 FROM OKL_STRM_ELEMENTS ste,
2578 OKL_STREAMS stm,
2579 okl_strm_type_v sty,
2580 okc_k_headers_b khr,
2581 OKL_K_HEADERS khl,
2582 okc_k_lines_b kle,
2583 okc_statuses_b khs,
2584 okc_statuses_b kls
2585 WHERE TRUNC(ste.stream_element_date) >=
2586 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2587 AND TRUNC(ste.stream_element_date) <=
2588 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2589 AND ste.amount <> 0
2590 AND stm.id = ste.stm_id
2591 AND ste.date_billed IS NULL
2592 AND stm.active_yn = 'Y'
2593 AND stm.say_code = 'CURR'
2594 AND sty.id = stm.sty_id
2595 AND sty.billable_yn = 'Y'
2596 AND khr.id = stm.khr_id
2597 AND khr.scs_code IN ('LEASE', 'LOAN')
2598 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
2599 AND khr.contract_number = p_contract_number
2600 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
2601 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
2602
2603 AND khl.id = stm.khr_id
2604 AND khl.deal_type IS NOT NULL
2605 AND khs.code = khr.sts_code
2606 -- AND khs.ste_code = 'ACTIVE'
2607 AND kle.id (+) = stm.kle_id
2608 AND kls.code (+) = kle.sts_code
2609 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED' , 'EXPIRED') -- bug 6472228 added EXPIRED status
2610 AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
2611 AND ( p_source <> 'PRINCIPAL_PAYDOWN' OR (p_source = 'PRINCIPAL_PAYDOWN'
2612 AND sty.stream_type_purpose
2613 in ('UNSCHEDULED_PRINCIPAL_PAYMENT','UNSCHEDULED_LOAN_PAYMENT')
2614 )
2615 )
2616 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
2617 UNION
2618 SELECT stm.khr_id khr_id,
2619 TRUNC (ste.stream_element_date) bill_date,
2620 stm.kle_id kle_id,
2621 ste.id sel_id,
2622 stm.sty_id sty_id,
2623 khr.contract_number contract_number,
2624 khr.currency_code currency_code,
2625 khr.authoring_org_id authoring_org_id,
2626 sty.name comments,
2627 sty.taxable_default_yn taxable_default_yn,
2628 ste.amount amount,
2629 khr.sts_code sts_code
2630 FROM OKL_STRM_ELEMENTS ste,
2631 OKL_STREAMS stm,
2632 okl_strm_type_v sty,
2633 okc_k_headers_b khr,
2634 OKL_K_HEADERS khl,
2635 okc_k_lines_b kle,
2636 okc_statuses_b khs,
2637 okc_statuses_b kls,
2638 okc_k_lines_b cle
2639 WHERE TRUNC(ste.stream_element_date) >= TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2640 AND TRUNC(ste.stream_element_date) <= TRUNC((NVL (p_to_bill_date, SYSDATE) + nvl(get_printing_lead_days(stm.khr_id), 0)))
2641 AND ste.amount <> 0
2642 AND stm.id = ste.stm_id
2643 AND ste.date_billed IS NULL
2644 AND stm.active_yn = 'Y'
2645 AND stm.say_code = 'CURR'
2646 AND sty.id = stm.sty_id
2647 AND sty.billable_yn = 'Y'
2648 AND khr.id = stm.khr_id
2649 AND khr.scs_code = 'INVESTOR'
2650 AND khr.sts_code = ( 'ACTIVE') -- bug 6472228 added EXPIRED status
2651 AND khr.contract_number = p_contract_number
2652 AND nvl(p_ia_contract_type,L_IA_TYPE) = L_IA_TYPE
2653 AND cle.dnz_chr_id = khr.id
2654 AND (p_inv_cust_acct_id IS NULL OR (p_inv_cust_acct_id IS NOT NULL AND (cle.cust_acct_id IS NOT NULL and cle.cust_acct_id = p_inv_cust_acct_id)))
2655 AND khl.id = stm.khr_id
2656 AND khs.code = khr.sts_code
2657 AND kle.id = stm.kle_id
2658 AND kls.code = kle.sts_code
2659 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED' , 'EXPIRED') -- bug 6472228 added EXPIRED status
2660 AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
2661 ORDER BY 1, 2, 11, 3;
2662 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
2663
2664 --2nd select for Actual property tax streams
2665 CURSOR c2 IS
2666 SELECT stm.khr_id khr_id,
2667 TRUNC (ste.stream_element_date) bill_date,
2668 stm.kle_id kle_id,
2669 ste.id sel_id,
2670 stm.sty_id sty_id,
2671 khr.contract_number contract_number,
2672 khr.currency_code currency_code,
2673 khr.authoring_org_id authoring_org_id,
2674 sty.name comments,
2675 sty.taxable_default_yn taxable_default_yn,
2676 ste.amount amount,
2677 khr.sts_code sts_code
2678 FROM OKL_STRM_ELEMENTS ste,
2679 OKL_STREAMS stm,
2680 okl_strm_type_v sty,
2681 okc_k_headers_b khr,
2682 OKL_K_HEADERS khl,
2683 okc_k_lines_b kle,
2684 okc_statuses_b khs,
2685 okc_statuses_b kls
2686 WHERE TRUNC(ste.stream_element_date) >=
2687 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2688 AND TRUNC(ste.stream_element_date) <=
2689 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2690 AND ste.amount <> 0
2691 AND stm.id = ste.stm_id
2692 AND ste.date_billed IS NULL
2693 AND stm.active_yn = 'Y'
2694 AND stm.say_code = 'CURR'
2695 AND sty.id = stm.sty_id
2696 AND sty.billable_yn = 'Y'
2697 AND khr.id = stm.khr_id
2698 AND khr.scs_code IN ('LEASE', 'LOAN')
2699 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
2700 AND khr.contract_number = p_contract_number
2701 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
2702
2703 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
2704 AND khl.id = stm.khr_id
2705 AND khl.deal_type IS NOT NULL
2706 AND khs.code = khr.sts_code
2707 AND khs.ste_code = 'ACTIVE'
2708 AND kle.id (+) = stm.kle_id
2709 AND kls.code (+) = kle.sts_code
2710 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
2711 AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
2712 , okc_rules_b rul
2713 WHERE rgp.dnz_chr_id = kle.dnz_chr_id
2714 AND rgp.cle_id = kle.id
2715 AND rgp.rgd_code = 'LAASTX'
2716 AND rgp.id = rul.rgp_id
2717 AND rul.rule_information_category = 'LAPRTX'
2718 AND rul.rule_information1 = 'Y'
2719 AND (rul.rule_information3 = 'ACTUAL')
2720 )
2721 AND sty.stream_type_purpose = 'ACTUAL_PROPERTY_TAX'
2722 AND (p_source <> 'PRINCIPAL_PAYDOWN')
2723 ORDER BY 1, 2, 3;
2724
2725 --3rd select for Estimated property tax streams
2726 CURSOR c3 IS
2727 SELECT stm.khr_id khr_id,
2728 TRUNC (ste.stream_element_date) bill_date,
2729 stm.kle_id kle_id,
2730 ste.id sel_id,
2731 stm.sty_id sty_id,
2732 khr.contract_number contract_number,
2733 khr.currency_code currency_code,
2734 khr.authoring_org_id authoring_org_id,
2735 sty.name comments,
2736 sty.taxable_default_yn taxable_default_yn,
2737 ste.amount amount,
2738 khr.sts_code sts_code
2739 FROM OKL_STRM_ELEMENTS ste,
2740 OKL_STREAMS stm,
2741 okl_strm_type_v sty,
2742 okc_k_headers_b khr,
2743 OKL_K_HEADERS khl,
2744 okc_k_lines_b kle,
2745 okc_statuses_b khs,
2746 okc_statuses_b kls
2747 WHERE TRUNC(ste.stream_element_date) >=
2748 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2749 AND TRUNC(ste.stream_element_date) <=
2750 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2751 AND ste.amount <> 0
2752 AND stm.id = ste.stm_id
2753 AND ste.date_billed IS NULL
2754 AND stm.active_yn = 'Y'
2755 AND stm.say_code = 'CURR'
2756 AND sty.id = stm.sty_id
2757 AND sty.billable_yn = 'Y'
2758 AND khr.id = stm.khr_id
2759 AND khr.scs_code IN ('LEASE', 'LOAN')
2760 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
2761 AND khr.contract_number = p_contract_number
2762 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
2763 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
2764 AND khl.id = stm.khr_id
2765 AND khl.deal_type IS NOT NULL
2766 AND khs.code = khr.sts_code
2767 AND khs.ste_code = 'ACTIVE'
2768 AND kle.id (+) = stm.kle_id
2769 AND kls.code (+) = kle.sts_code
2770 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
2771 AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
2772 , okc_rules_b rul
2773 WHERE rgp.dnz_chr_id = kle.dnz_chr_id
2774 AND rgp.cle_id = kle.id
2775 AND rgp.rgd_code = 'LAASTX'
2776 AND rgp.id = rul.rgp_id
2777 AND rul.rule_information_category = 'LAPRTX'
2778 AND rul.rule_information1 = 'Y'
2779 AND (rul.rule_information3 = 'ESTIMATED' OR rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
2780 )
2781 AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
2782 AND (p_source <> 'PRINCIPAL_PAYDOWN')
2783 ORDER BY 1, 2, 3;
2784
2785 --Bug# 9690217: Added stream element amount to ORDER BY clause
2786 -- to support grouping positive amounts into invoices
2787 -- and negative amounts into credit memos
2788 CURSOR c4 IS
2789 --1st select for all streams other than Property tax streams
2790 SELECT stm.khr_id khr_id,
2791 TRUNC (ste.stream_element_date) bill_date,
2792 stm.kle_id kle_id,
2793 ste.id sel_id,
2794 stm.sty_id sty_id,
2795 khr.contract_number contract_number,
2796 khr.currency_code currency_code,
2797 khr.authoring_org_id authoring_org_id,
2798 sty.name comments,
2799 sty.taxable_default_yn taxable_default_yn,
2800 ste.amount amount,
2801 khr.sts_code sts_code
2802 FROM OKL_STRM_ELEMENTS ste,
2803 OKL_STREAMS stm,
2804 okl_strm_type_v sty,
2805 okc_k_headers_b khr,
2806 OKL_K_HEADERS khl,
2807 okc_k_lines_b kle,
2808 okc_statuses_b khs,
2809 okc_statuses_b kls
2810 WHERE TRUNC(ste.stream_element_date) >=
2811 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2812 AND TRUNC(ste.stream_element_date) <=
2813 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2814 AND ste.amount <> 0
2815 AND stm.id = ste.stm_id
2816 AND ste.date_billed IS NULL
2817 AND stm.active_yn = 'Y'
2818 AND stm.say_code = 'CURR'
2819 AND sty.id = stm.sty_id
2820 AND sty.billable_yn = 'Y'
2821 AND khr.id = stm.khr_id
2822 AND khr.scs_code IN ('LEASE', 'LOAN')
2823 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
2824 -- AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
2825 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
2826 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
2827 AND khl.id = stm.khr_id
2828 AND khl.deal_type IS NOT NULL
2829 AND khs.code = khr.sts_code
2830 -- AND khs.ste_code = 'ACTIVE'
2831 AND kle.id (+) = stm.kle_id
2832 AND kls.code (+) = kle.sts_code
2833 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
2834 AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
2835 AND ( p_source <> 'PRINCIPAL_PAYDOWN' OR (p_source = 'PRINCIPAL_PAYDOWN'
2836 AND sty.stream_type_purpose
2837 = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
2838 )
2839 )
2840 -- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
2841 UNION
2842 SELECT stm.khr_id khr_id,
2843 TRUNC (ste.stream_element_date) bill_date,
2844 stm.kle_id kle_id,
2845 ste.id sel_id,
2846 stm.sty_id sty_id,
2847 khr.contract_number contract_number,
2848 khr.currency_code currency_code,
2849 khr.authoring_org_id authoring_org_id,
2850 sty.name comments,
2851 sty.taxable_default_yn taxable_default_yn,
2852 ste.amount amount,
2853 khr.sts_code sts_code
2854 FROM OKL_STRM_ELEMENTS ste,
2855 OKL_STREAMS stm,
2856 okl_strm_type_v sty,
2857 okc_k_headers_b khr,
2858 OKL_K_HEADERS khl,
2859 okc_k_lines_b kle,
2860 okc_statuses_b khs,
2861 okc_statuses_b kls,
2862 okc_k_lines_b cle
2863 WHERE TRUNC(ste.stream_element_date) >=
2864 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2865 AND TRUNC(ste.stream_element_date) <= TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2866 AND ste.amount <> 0
2867 AND stm.id = ste.stm_id
2868 AND ste.date_billed IS NULL
2869 AND stm.active_yn = 'Y'
2870 AND stm.say_code = 'CURR'
2871 AND sty.id = stm.sty_id
2872 AND sty.billable_yn = 'Y'
2873 AND khr.id = stm.khr_id
2874 AND khr.scs_code = 'INVESTOR'
2875 AND khr.sts_code = 'ACTIVE'
2876 AND nvl(p_ia_contract_type,L_IA_TYPE) = L_IA_TYPE
2877 AND cle.dnz_chr_id = khr.id
2878 AND (p_inv_cust_acct_id IS NULL OR (p_inv_cust_acct_id IS NOT NULL AND (cle.cust_acct_id IS NOT NULL and cle.cust_acct_id = p_inv_cust_acct_id)))
2879 AND khl.id = stm.khr_id
2880 AND khs.code = khr.sts_code
2881 -- AND khs.ste_code = 'ACTIVE'
2882 AND kle.id = stm.kle_id
2883 AND kls.code = kle.sts_code
2884 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
2885 AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
2886 ORDER BY 1, 2, 11, 3;
2887 -- modified by zrehman for Bug#6788005 on 04-Feb-2008 end
2888
2889 --2nd select for Actual property tax streams
2890 CURSOR c5 IS
2891 SELECT stm.khr_id khr_id,
2892 TRUNC (ste.stream_element_date) bill_date,
2893 stm.kle_id kle_id,
2894 ste.id sel_id,
2895 stm.sty_id sty_id,
2896 khr.contract_number contract_number,
2897 khr.currency_code currency_code,
2898 khr.authoring_org_id authoring_org_id,
2899 sty.name comments,
2900 sty.taxable_default_yn taxable_default_yn,
2901 ste.amount amount,
2902 khr.sts_code sts_code
2903 FROM OKL_STRM_ELEMENTS ste,
2904 OKL_STREAMS stm,
2905 okl_strm_type_v sty,
2906 okc_k_headers_b khr,
2907 OKL_K_HEADERS khl,
2908 okc_k_lines_b kle,
2909 okc_statuses_b khs,
2910 okc_statuses_b kls
2911 WHERE TRUNC(ste.stream_element_date) >=
2912 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2913 AND TRUNC(ste.stream_element_date) <=
2914 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2915 AND ste.amount <> 0
2916 AND stm.id = ste.stm_id
2917 AND ste.date_billed IS NULL
2918 AND stm.active_yn = 'Y'
2919 AND stm.say_code = 'CURR'
2920 AND sty.id = stm.sty_id
2921 AND sty.billable_yn = 'Y'
2922 AND khr.id = stm.khr_id
2923 AND khr.scs_code IN ('LEASE', 'LOAN')
2924 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
2925 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
2926 -- AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
2927 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
2928 AND khl.id = stm.khr_id
2929 AND khl.deal_type IS NOT NULL
2930 AND khs.code = khr.sts_code
2931 AND khs.ste_code = 'ACTIVE'
2932 AND kle.id (+) = stm.kle_id
2933 AND kls.code (+) = kle.sts_code
2934 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
2935 AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
2936 , okc_rules_b rul
2937 WHERE rgp.dnz_chr_id = kle.dnz_chr_id
2938 AND rgp.cle_id = kle.id
2939 AND rgp.rgd_code = 'LAASTX'
2940 AND rgp.id = rul.rgp_id
2941 AND rul.rule_information_category = 'LAPRTX'
2942 AND rul.rule_information1 = 'Y'
2943 AND (rul.rule_information3 = 'ACTUAL')
2944 )
2945 AND sty.stream_type_purpose = 'ACTUAL_PROPERTY_TAX'
2946 AND (p_source <> 'PRINCIPAL_PAYDOWN')
2947 ORDER BY 1, 2, 3;
2948
2949 --3rd select for Estimated property tax streams
2950 CURSOR c6 IS
2951 SELECT stm.khr_id khr_id,
2952 TRUNC (ste.stream_element_date) bill_date,
2953 stm.kle_id kle_id,
2954 ste.id sel_id,
2955 stm.sty_id sty_id,
2956 khr.contract_number contract_number,
2957 khr.currency_code currency_code,
2958 khr.authoring_org_id authoring_org_id,
2959 sty.name comments,
2960 sty.taxable_default_yn taxable_default_yn,
2961 ste.amount amount,
2962 khr.sts_code sts_code
2963 FROM OKL_STRM_ELEMENTS ste,
2964 OKL_STREAMS stm,
2965 okl_strm_type_v sty,
2966 okc_k_headers_b khr,
2967 OKL_K_HEADERS khl,
2968 okc_k_lines_b kle,
2969 okc_statuses_b khs,
2970 okc_statuses_b kls
2971 WHERE TRUNC(ste.stream_element_date) >=
2972 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
2973 AND TRUNC(ste.stream_element_date) <=
2974 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
2975 AND ste.amount <> 0
2976 AND stm.id = ste.stm_id
2977 AND ste.date_billed IS NULL
2978 AND stm.active_yn = 'Y'
2979 AND stm.say_code = 'CURR'
2980 AND sty.id = stm.sty_id
2981 AND sty.billable_yn = 'Y'
2982 AND khr.id = stm.khr_id
2983 AND khr.scs_code IN ('LEASE', 'LOAN')
2984 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
2985 -- AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
2986 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
2987 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
2988 AND khl.id = stm.khr_id
2989 AND khl.deal_type IS NOT NULL
2990 AND khs.code = khr.sts_code
2991 AND khs.ste_code = 'ACTIVE'
2992 AND kle.id (+) = stm.kle_id
2993 AND kls.code (+) = kle.sts_code
2994 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
2995 AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
2996 , okc_rules_b rul
2997 WHERE rgp.dnz_chr_id = kle.dnz_chr_id
2998 AND rgp.cle_id = kle.id
2999 AND rgp.rgd_code = 'LAASTX'
3000 AND rgp.id = rul.rgp_id
3001 AND rul.rule_information_category = 'LAPRTX'
3002 AND rul.rule_information1 = 'Y'
3003 AND (rul.rule_information3 = 'ESTIMATED' OR rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
3004 )
3005 AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
3006 AND (p_source <> 'PRINCIPAL_PAYDOWN')
3007 ORDER BY 1, 2, 3;
3008
3009 --Bug# 9690217: Added stream element amount to ORDER BY clause
3010 -- to support grouping positive amounts into invoices
3011 -- and negative amounts into credit memos
3012 CURSOR c7 IS
3013 --1st select for all streams other than Property tax streams
3014 SELECT stm.khr_id khr_id,
3015 TRUNC (ste.stream_element_date) bill_date,
3016 stm.kle_id kle_id,
3017 ste.id sel_id,
3018 stm.sty_id sty_id,
3019 khr.contract_number contract_number,
3020 khr.currency_code currency_code,
3021 khr.authoring_org_id authoring_org_id,
3022 sty.name comments,
3023 sty.taxable_default_yn taxable_default_yn,
3024 ste.amount amount,
3025 khr.sts_code sts_code
3026 FROM OKL_STRM_ELEMENTS ste,
3027 OKL_STREAMS stm,
3028 okl_strm_type_v sty,
3029 okc_k_headers_b khr,
3030 OKL_K_HEADERS khl,
3031 okc_k_lines_b kle,
3032 okc_statuses_b khs,
3033 okc_statuses_b kls,
3034 OKL_PARALLEL_PROCESSES pws
3035 WHERE TRUNC(ste.stream_element_date) >=
3036 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
3037 AND TRUNC(ste.stream_element_date) <=
3038 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
3039 AND ste.amount <> 0
3040 AND stm.id = ste.stm_id
3041 AND ste.date_billed IS NULL
3042 AND stm.active_yn = 'Y'
3043 AND stm.say_code = 'CURR'
3044 AND sty.id = stm.sty_id
3045 AND sty.billable_yn = 'Y'
3046 AND khr.id = stm.khr_id
3047 AND khr.scs_code IN ('LEASE', 'LOAN')
3048 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
3049 AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
3050 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
3051 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
3052 AND khl.id = stm.khr_id
3053 AND khl.deal_type IS NOT NULL
3054 AND khs.code = khr.sts_code
3055 AND kle.id (+) = stm.kle_id
3056 AND kls.code (+) = kle.sts_code
3057 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
3058 AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
3059 AND ( p_source <> 'PRINCIPAL_PAYDOWN' OR (p_source = 'PRINCIPAL_PAYDOWN'
3060 AND sty.stream_type_purpose
3061 = 'UNSCHEDULED_PRINCIPAL_PAYMENT'
3062 )
3063 )
3064 AND pws.object_type = 'CONTRACT'
3065 AND pws.object_value = khr.contract_number
3066 AND pws.assigned_process = p_assigned_process
3067 -- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
3068 UNION
3069 SELECT stm.khr_id khr_id,
3070 TRUNC (ste.stream_element_date) bill_date,
3071 stm.kle_id kle_id,
3072 ste.id sel_id,
3073 stm.sty_id sty_id,
3074 khr.contract_number contract_number,
3075 khr.currency_code currency_code,
3076 khr.authoring_org_id authoring_org_id,
3077 sty.name comments,
3078 sty.taxable_default_yn taxable_default_yn,
3079 ste.amount amount,
3080 khr.sts_code sts_code
3081 FROM OKL_STRM_ELEMENTS ste,
3082 OKL_STREAMS stm,
3083 okl_strm_type_v sty,
3084 okc_k_headers_b khr,
3085 OKL_K_HEADERS khl,
3086 okc_k_lines_b kle,
3087 okc_statuses_b khs,
3088 okc_statuses_b kls,
3089 okc_k_lines_b cle,
3090 OKL_PARALLEL_PROCESSES pws
3091 WHERE TRUNC(ste.stream_element_date) >=
3092 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
3093 AND TRUNC(ste.stream_element_date) <=
3094 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
3095 AND ste.amount <> 0
3096 AND stm.id = ste.stm_id
3097 AND ste.date_billed IS NULL
3098 AND stm.active_yn = 'Y'
3099 AND stm.say_code = 'CURR'
3100 AND sty.id = stm.sty_id
3101 AND sty.billable_yn = 'Y'
3102 AND khr.id = stm.khr_id
3103 AND khr.scs_code = 'INVESTOR'
3104 AND khr.sts_code = 'ACTIVE' -- bug 6472228 added EXPIRED status
3105 AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
3106 AND nvl(p_ia_contract_type,L_IA_TYPE) = L_IA_TYPE
3107 AND cle.dnz_chr_id = khr.id
3108 AND (p_inv_cust_acct_id IS NULL OR (p_inv_cust_acct_id IS NOT NULL AND (cle.cust_acct_id IS NOT NULL and cle.cust_acct_id = p_inv_cust_acct_id)))
3109 AND khl.id = stm.khr_id
3110 AND khs.code = khr.sts_code
3111 AND kle.id = stm.kle_id
3112 AND kls.code = kle.sts_code
3113 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
3114 AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
3115 AND pws.object_type = 'CONTRACT'
3116 AND pws.object_value = khr.contract_number
3117 AND pws.assigned_process = p_assigned_process
3118 ORDER BY 1, 2, 11, 3;
3119 -- modified by zrehman for Bug#6788005 on 04-Feb-2008 end
3120
3121 CURSOR c8 IS
3122 SELECT stm.khr_id khr_id,
3123 TRUNC (ste.stream_element_date) bill_date,
3124 stm.kle_id kle_id,
3125 ste.id sel_id,
3126 stm.sty_id sty_id,
3127 khr.contract_number contract_number,
3128 khr.currency_code currency_code,
3129 khr.authoring_org_id authoring_org_id,
3130 sty.name comments,
3131 sty.taxable_default_yn taxable_default_yn,
3132 ste.amount amount,
3133 khr.sts_code sts_code
3134 FROM OKL_STRM_ELEMENTS ste,
3135 OKL_STREAMS stm,
3136 okl_strm_type_v sty,
3137 okc_k_headers_b khr,
3138 OKL_K_HEADERS khl,
3139 okc_k_lines_b kle,
3140 okc_statuses_b khs,
3141 okc_statuses_b kls,
3142 OKL_PARALLEL_PROCESSES pws
3143 WHERE TRUNC(ste.stream_element_date) >=
3144 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
3145 AND TRUNC(ste.stream_element_date) <=
3146 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
3147 AND ste.amount <> 0
3148 AND stm.id = ste.stm_id
3149 AND ste.date_billed IS NULL
3150 AND stm.active_yn = 'Y'
3151 AND stm.say_code = 'CURR'
3152 AND sty.id = stm.sty_id
3153 AND sty.billable_yn = 'Y'
3154 AND khr.id = stm.khr_id
3155 AND khr.scs_code IN ('LEASE', 'LOAN')
3156 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED','EXPIRED') -- Bug#7475594
3157 AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
3158 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
3159 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
3160 AND khl.id = stm.khr_id
3161 AND khl.deal_type IS NOT NULL
3162 AND khs.code = khr.sts_code
3163 -- AND khs.ste_code = 'ACTIVE'
3164 AND kle.id (+) = stm.kle_id
3165 AND kls.code (+) = kle.sts_code
3166 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- Bug# 7475594
3167 AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
3168 , okc_rules_b rul
3169 WHERE rgp.dnz_chr_id = kle.dnz_chr_id
3170 AND rgp.cle_id = kle.id
3171 AND rgp.rgd_code = 'LAASTX'
3172 AND rgp.id = rul.rgp_id
3173 AND rul.rule_information_category = 'LAPRTX'
3174 AND rul.rule_information1 = 'Y'
3175 AND (rul.rule_information3 = 'ACTUAL')
3176 )
3177 AND sty.stream_type_purpose = 'ACTUAL_PROPERTY_TAX'
3178 AND (p_source <> 'PRINCIPAL_PAYDOWN')
3179 AND pws.object_type = 'CONTRACT'
3180 AND pws.object_value = khr.contract_number
3181 AND pws.assigned_process = p_assigned_process
3182 ORDER BY 1, 2, 3;
3183
3184 CURSOR c9 IS
3185 SELECT stm.khr_id khr_id,
3186 TRUNC (ste.stream_element_date) bill_date,
3187 stm.kle_id kle_id,
3188 ste.id sel_id,
3189 stm.sty_id sty_id,
3190 khr.contract_number contract_number,
3191 khr.currency_code currency_code,
3192 khr.authoring_org_id authoring_org_id,
3193 sty.name comments,
3194 sty.taxable_default_yn taxable_default_yn,
3195 ste.amount amount,
3196 khr.sts_code sts_code
3197 FROM OKL_STRM_ELEMENTS ste,
3198 OKL_STREAMS stm,
3199 okl_strm_type_v sty,
3200 okc_k_headers_b khr,
3201 OKL_K_HEADERS khl,
3202 okc_k_lines_b kle,
3203 okc_statuses_b khs,
3204 okc_statuses_b kls,
3205 OKL_PARALLEL_PROCESSES pws
3206 WHERE TRUNC(ste.stream_element_date) >=
3207 TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
3208 AND TRUNC(ste.stream_element_date) <=
3209 TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
3210 AND ste.amount <> 0
3211 AND stm.id = ste.stm_id
3212 AND ste.date_billed IS NULL
3213 AND stm.active_yn = 'Y'
3214 AND stm.say_code = 'CURR'
3215 AND sty.id = stm.sty_id
3216 AND sty.billable_yn = 'Y'
3217 AND khr.id = stm.khr_id
3218 AND khr.scs_code IN ('LEASE', 'LOAN')
3219 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
3220 AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
3221 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
3222 AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
3223 AND khl.id = stm.khr_id
3224 AND khl.deal_type IS NOT NULL
3225 AND khs.code = khr.sts_code
3226 AND khs.ste_code = 'ACTIVE'
3227 AND kle.id (+) = stm.kle_id
3228 AND kls.code (+) = kle.sts_code
3229 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
3230 AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
3231 , okc_rules_b rul
3232 WHERE rgp.dnz_chr_id = kle.dnz_chr_id
3233 AND rgp.cle_id = kle.id
3234 AND rgp.rgd_code = 'LAASTX'
3235 AND rgp.id = rul.rgp_id
3236 AND rul.rule_information_category = 'LAPRTX'
3237 AND rul.rule_information1 = 'Y'
3238 AND (rul.rule_information3 = 'ESTIMATED' OR rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
3239 )
3240 AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
3241 AND (p_source <> 'PRINCIPAL_PAYDOWN')
3242 AND pws.object_type = 'CONTRACT'
3243 AND pws.object_value = khr.contract_number
3244 AND pws.assigned_process = p_assigned_process
3245
3246 ORDER BY 1, 2, 3;
3247
3248 bill_tbl bill_tbl_type;
3249
3250 L_FETCH_SIZE NUMBER := 5000;
3251
3252 -- --------------------------------------------------------
3253 -- To Print log messages
3254 -- --------------------------------------------------------
3255
3256 l_request_id NUMBER; --Bug 7584183
3257
3258 CURSOR txd_cnt_succ_csr( p_req_id NUMBER, p_sts VARCHAR2 ) IS
3259 SELECT COUNT(*)
3260 FROM okl_trx_ar_invoices_v a,
3261 okl_txl_ar_inv_lns_v b,
3262 okl_txd_ar_ln_dtls_v c
3263 WHERE a.id = b.tai_id AND
3264 b.id = c.til_id_details AND
3265 a.trx_status_code = p_sts AND
3266 a.request_id = p_req_id ;
3267
3268 CURSOR txd_cnt_err_csr( p_req_id NUMBER, p_sts VARCHAR2 ) IS
3269 SELECT COUNT(*)
3270 FROM okl_trx_ar_invoices_v a,
3271 okl_txl_ar_inv_lns_v b,
3272 okl_txd_ar_ln_dtls_v c
3273 WHERE a.id = b.tai_id AND
3274 b.id = c.til_id_details AND
3275 a.trx_status_code = p_sts AND
3276 a.request_id = p_req_id ;
3277
3278 ------------------------------------------------------------
3279 -- Operating Unit
3280 ------------------------------------------------------------
3281 CURSOR op_unit_csr IS
3282 SELECT NAME
3283 FROM hr_operating_units
3284 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
3285
3286
3287 l_succ_cnt NUMBER;
3288 l_err_cnt NUMBER;
3289 l_op_unit_name hr_operating_units.name%TYPE;
3290 lx_msg_data VARCHAR2(450);
3291 l_msg_index_out NUMBER :=0;
3292 processed_sts okl_trx_ar_invoices_v.trx_status_code%TYPE;
3293 error_sts okl_trx_ar_invoices_v.trx_status_code%TYPE;
3294 l_end_of_records VARCHAR2(1);
3295
3296
3297 BEGIN
3298
3299 l_return_status := Okl_Api.START_ACTIVITY(
3300 p_api_name => l_api_name,
3301 p_pkg_name => G_PKG_NAME,
3302 p_init_msg_list => p_init_msg_list,
3303 l_api_version => l_api_version,
3304 p_api_version => p_api_version,
3305 p_api_type => '_PVT',
3306 x_return_status => l_return_status);
3307
3308 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
3309
3310 l_end_of_records := 'N';
3311
3312 IF p_assigned_process IS NOT NULL THEN
3313
3314 -- Cursors 7,8 and 9
3315
3316 OPEN C7;
3317 LOOP
3318 -- ----------------------------
3319 -- Clear table contents
3320 -- ----------------------------
3321 bill_tbl.DELETE;
3322 FETCH C7 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3323 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C7 Bill_Tbl count is: '||bill_tbl.COUNT);
3324 IF bill_tbl.COUNT > 0 THEN
3325 Process_bill_tbl
3326 (p_api_version
3327 ,p_init_msg_list
3328 ,x_return_status
3329 ,x_msg_count
3330 ,x_msg_data
3331 ,p_commit
3332 ,p_contract_number
3333 ,p_from_bill_date
3334 ,p_to_bill_date
3335 ,bill_tbl
3336 ,p_source
3337 ,l_end_of_records);
3338 END IF;
3339 EXIT WHEN C7%NOTFOUND;
3340 END LOOP;
3341 CLOSE C7;
3342 --
3343 OPEN C8;
3344 LOOP
3345 -- ----------------------------
3346 -- Clear table contents
3347 -- ----------------------------
3348 bill_tbl.DELETE;
3349 FETCH C8 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3350 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C8 Bill_Tbl count is: '||bill_tbl.COUNT);
3351 IF bill_tbl.COUNT > 0 THEN
3352 Process_bill_tbl
3353 (p_api_version
3354 ,p_init_msg_list
3355 ,x_return_status
3356 ,x_msg_count
3357 ,x_msg_data
3358 ,p_commit
3359 ,p_contract_number
3360 ,p_from_bill_date
3361 ,p_to_bill_date
3362 ,bill_tbl
3363 ,p_source
3364 ,l_end_of_records);
3365 END IF;
3366 EXIT WHEN C8%NOTFOUND;
3367 END LOOP;
3368 CLOSE C8;
3369 --
3370 OPEN C9;
3371 LOOP
3372 -- ----------------------------
3373 -- Clear table contents
3374 -- ----------------------------
3375 bill_tbl.DELETE;
3376 FETCH C9 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3377 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C9 Bill_Tbl count is: '||bill_tbl.COUNT);
3378 IF bill_tbl.COUNT > 0 THEN
3379 Process_bill_tbl
3380 (p_api_version
3381 ,p_init_msg_list
3382 ,x_return_status
3383 ,x_msg_count
3384 ,x_msg_data
3385 ,p_commit
3386 ,p_contract_number
3387 ,p_from_bill_date
3388 ,p_to_bill_date
3389 ,bill_tbl
3390 ,p_source
3391 ,l_end_of_records);
3392 END IF;
3393 EXIT WHEN C9%NOTFOUND;
3394 END LOOP;
3395 CLOSE C9;
3396
3397
3398 ELSE -- Assigned Process Id
3399 IF (p_source <> 'PRINCIPAL_PAYDOWN') THEN
3400 IF p_contract_number IS NOT NULL THEN
3401
3402 -- Cursors 1, 2 and 3
3403 OPEN C1;
3404 LOOP
3405 -- ----------------------------
3406 -- Clear table contents
3407 -- ----------------------------
3408 bill_tbl.DELETE;
3409 FETCH C1 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3410 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C1 Bill_Tbl count is: '||bill_tbl.COUNT);
3411 IF bill_tbl.COUNT > 0 THEN
3412 Process_bill_tbl
3413 (p_api_version
3414 ,p_init_msg_list
3415 ,x_return_status
3416 ,x_msg_count
3417 ,x_msg_data
3418 ,p_commit
3419 ,p_contract_number
3420 ,p_from_bill_date
3421 ,p_to_bill_date
3422 ,bill_tbl
3423 ,p_source
3424 ,l_end_of_records);
3425 END IF;
3426 EXIT WHEN C1%NOTFOUND;
3427 END LOOP;
3428 CLOSE C1;
3429 --
3430 OPEN C2;
3431 LOOP
3432 -- ----------------------------
3433 -- Clear table contents
3434 -- ----------------------------
3435 bill_tbl.DELETE;
3436 FETCH C2 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3437 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C2 Bill_Tbl count is: '||bill_tbl.COUNT);
3438 IF bill_tbl.COUNT > 0 THEN
3439 Process_bill_tbl
3440 (p_api_version
3441 ,p_init_msg_list
3442 ,x_return_status
3443 ,x_msg_count
3444 ,x_msg_data
3445 ,p_commit
3446 ,p_contract_number
3447 ,p_from_bill_date
3448 ,p_to_bill_date
3449 ,bill_tbl
3450 ,p_source
3451 ,l_end_of_records);
3452 END IF;
3453 EXIT WHEN C2%NOTFOUND;
3454 END LOOP;
3455 CLOSE C2;
3456 --
3457 OPEN C3;
3458 LOOP
3459 -- ----------------------------
3460 -- Clear table contents
3461 -- ----------------------------
3462 bill_tbl.DELETE;
3463 FETCH C3 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3464 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C3 Bill_Tbl count is: '||bill_tbl.COUNT);
3465 IF bill_tbl.COUNT > 0 THEN
3466 Process_bill_tbl
3467 (p_api_version
3468 ,p_init_msg_list
3469 ,x_return_status
3470 ,x_msg_count
3471 ,x_msg_data
3472 ,p_commit
3473 ,p_contract_number
3474 ,p_from_bill_date
3475 ,p_to_bill_date
3476 ,bill_tbl
3477 ,p_source
3478 ,l_end_of_records);
3479 END IF;
3480 EXIT WHEN C3%NOTFOUND;
3481 END LOOP;
3482 CLOSE C3;
3483 --*******************
3484
3485 ELSE -- p_contract_number supplied or not
3486 -- Cursors 4, 5 and 6
3487
3488 OPEN C4;
3489 LOOP
3490 -- ----------------------------
3491 -- Clear table contents
3492 -- ----------------------------
3493 bill_tbl.DELETE;
3494 FETCH C4 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3495 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C4 Bill_Tbl count is: '||bill_tbl.COUNT);
3496 IF bill_tbl.COUNT > 0 THEN
3497 Process_bill_tbl
3498 (p_api_version
3499 ,p_init_msg_list
3500 ,x_return_status
3501 ,x_msg_count
3502 ,x_msg_data
3503 ,p_commit
3504 ,p_contract_number
3505 ,p_from_bill_date
3506 ,p_to_bill_date
3507 ,bill_tbl
3508 ,p_source
3509 ,l_end_of_records);
3510 END IF;
3511 EXIT WHEN C4%NOTFOUND;
3512 END LOOP;
3513 CLOSE C4;
3514 --
3515 OPEN C5;
3516 LOOP
3517 -- ----------------------------
3518 -- Clear table contents
3519 -- ----------------------------
3520 bill_tbl.DELETE;
3521 FETCH C5 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3522 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C5 Bill_Tbl count is: '||bill_tbl.COUNT);
3523 IF bill_tbl.COUNT > 0 THEN
3524 Process_bill_tbl
3525 (p_api_version
3526 ,p_init_msg_list
3527 ,x_return_status
3528 ,x_msg_count
3529 ,x_msg_data
3530 ,p_commit
3531 ,p_contract_number
3532 ,p_from_bill_date
3533 ,p_to_bill_date
3534 ,bill_tbl
3535 ,p_source
3536 ,l_end_of_records);
3537 END IF;
3538 EXIT WHEN C5%NOTFOUND;
3539 END LOOP;
3540 CLOSE C5;
3541 --
3542 OPEN C6;
3543 LOOP
3544 -- ----------------------------
3545 -- Clear table contents
3546 -- ----------------------------
3547 bill_tbl.DELETE;
3548 FETCH C6 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3549 Fnd_File.PUT_LINE (Fnd_File.LOG, 'C6 Bill_Tbl count is: '||bill_tbl.COUNT);
3550 IF bill_tbl.COUNT > 0 THEN
3551 Process_bill_tbl
3552 (p_api_version
3553 ,p_init_msg_list
3554 ,x_return_status
3555 ,x_msg_count
3556 ,x_msg_data
3557 ,p_commit
3558 ,p_contract_number
3559 ,p_from_bill_date
3560 ,p_to_bill_date
3561 ,bill_tbl
3562 ,p_source
3563 ,l_end_of_records);
3564 END IF;
3565 EXIT WHEN C6%NOTFOUND;
3566 END LOOP;
3567 CLOSE C6;
3568
3569 END IF; -- Contract Number null or not null
3570 ELSE -- Source of principal paydown
3571 -- ---------------------------------------------------
3572 -- Assumption is that the Principal Paydown process
3573 -- always supplies a contract number
3574 -- ---------------------------------------------------
3575 -- Principal Paydown Code
3576 --*******************
3577 IF p_contract_number IS NOT NULL THEN
3578 -- --------------------------------------------------
3579 OPEN C1;
3580 LOOP
3581 -- ----------------------------
3582 -- Clear table contents
3583 -- ----------------------------
3584 bill_tbl.DELETE;
3585 FETCH C1 BULK COLLECT INTO bill_tbl LIMIT L_FETCH_SIZE;
3586 IF bill_tbl.COUNT > 0 THEN
3587 Process_bill_tbl
3588 (p_api_version
3589 ,p_init_msg_list
3590 ,x_return_status
3591 ,x_msg_count
3592 ,x_msg_data
3593 ,p_commit
3594 ,p_contract_number
3595 ,p_from_bill_date
3596 ,p_to_bill_date
3597 ,bill_tbl
3598 ,p_source
3599 ,l_end_of_records);
3600 END IF;
3601 EXIT WHEN C1%NOTFOUND;
3602 END LOOP;
3603 CLOSE C1;
3604 END IF; -- Contract Number not supplied and principal paydown
3605
3606 --*******************
3607 END IF; -- Source Of Principal Paydown
3608 END IF; -- Assigned Process Id
3609
3610 ------------------------------------------------
3611 -- Call Process_bill_tbl to mark end of process
3612 ------------------------------------------------
3613 l_end_of_records := 'Y';
3614
3615 Process_bill_tbl
3616 (p_api_version
3617 ,p_init_msg_list
3618 ,x_return_status
3619 ,x_msg_count
3620 ,x_msg_data
3621 ,p_commit
3622 ,p_contract_number
3623 ,p_from_bill_date
3624 ,p_to_bill_date
3625 ,bill_tbl
3626 ,p_source
3627 ,l_end_of_records);
3628
3629 -----------------------------------------------------------
3630 -- Print log and output messages
3631 ------------------------------------------------------------
3632
3633 -- Get the request Id
3634 l_request_id := p_request_id; --Bug 7584183
3635
3636 processed_sts := 'SUBMITTED'; -- 'PROCESSED'; -- rmunjulu R12 Fixes, check for submitted status not processed
3637 error_sts := 'ERROR';
3638
3639 l_succ_cnt := 0;
3640 l_err_cnt := 0;
3641
3642 -- Success Count
3643 OPEN txd_cnt_succ_csr( l_request_id, processed_sts );
3644 FETCH txd_cnt_succ_csr INTO l_succ_cnt;
3645 CLOSE txd_cnt_succ_csr;
3646
3647 -- Error Count
3648 OPEN txd_cnt_err_csr( l_request_id, error_sts );
3649 FETCH txd_cnt_err_csr INTO l_err_cnt;
3650 CLOSE txd_cnt_err_csr;
3651
3652 if (l_err_cnt > 0) and (l_warning_status is null) then
3653 l_warning_status := 'W';
3654 end if;
3655
3656 ----------------------------------------
3657 -- Get Operating unit name
3658 ----------------------------------------
3659 l_op_unit_name := NULL;
3660 OPEN op_unit_csr;
3661 FETCH op_unit_csr INTO l_op_unit_name;
3662 CLOSE op_unit_csr;
3663
3664 -- Start New Out File stmathew 15-OCT-2004
3665 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'Oracle Leasing and Finance Management'||LPAD(' ', 55, ' '));
3666 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3667 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'Process Billable Streams'||LPAD(' ', 54, ' '));
3668 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'------------------------'||LPAD(' ', 54, ' '));
3669 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3670 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3671 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Operating Unit: '||l_op_unit_name);
3672 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Request Id: '||l_request_id||LPAD(' ',74,' ') ||'Run Date: '||TO_CHAR(SYSDATE));
3673 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Currency: '||Okl_Accounting_Util.get_func_curr_code);
3674 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
3675 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'From Bill Date : ' ||p_from_bill_date);
3676 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'To Bill Date : ' ||p_to_bill_date);
3677 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Contract Number : ' ||p_contract_number);
3678 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
3679 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3680 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3681
3682 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Processing Details:'||LPAD(' ', 113, ' '));
3683 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3684 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Successful Stream Elements: '||l_succ_cnt);
3685 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Errored Stream Elements: '||l_err_cnt);
3686 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Total: '||(l_succ_cnt+l_err_cnt));
3687 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3688
3689 -- End New Out File stmathew 15-OCT-2004
3690 IF x_msg_count > 0 THEN
3691 FOR i IN 1..x_msg_count LOOP
3692 IF i = 1 THEN
3693 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Details of Errored Stream Elements:'||LPAD(' ', 97, ' '));
3694 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3695 END IF;
3696 Fnd_Msg_Pub.get (p_msg_index => i,
3697 p_encoded => 'F',
3698 p_data => lx_msg_data,
3699 p_msg_index_out => l_msg_index_out);
3700
3701 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,TO_CHAR(i) || ': ' || lx_msg_data);
3702
3703 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3704 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3705 TO_CHAR(i) || ': ' || lx_msg_data);
3706 END IF;
3707
3708 END LOOP;
3709 END IF;
3710
3711
3712 Okl_Api.END_ACTIVITY (
3713 x_msg_count => x_msg_count,
3714 x_msg_data => x_msg_data);
3715
3716 EXCEPTION
3717 ------------------------------------------------------------
3718 -- Exception handling
3719 ------------------------------------------------------------
3720 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3721 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (EXCP) => '||SQLERRM);
3722
3723 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3724 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3725 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
3726 END IF;
3727
3728 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3729 p_api_name => l_api_name,
3730 p_pkg_name => G_PKG_NAME,
3731 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
3732 x_msg_count => x_msg_count,
3733 x_msg_data => x_msg_data,
3734 p_api_type => '_PVT');
3735
3736 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3737 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
3738
3739 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3740 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3741 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
3742 END IF;
3743
3744 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3745 p_api_name => l_api_name,
3746 p_pkg_name => G_PKG_NAME,
3747 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3748 x_msg_count => x_msg_count,
3749 x_msg_data => x_msg_data,
3750 p_api_type => '_PVT');
3751
3752 WHEN OTHERS THEN
3753 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (OTHERS 2) => '||SQLERRM);
3754
3755 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3756 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3757 'EXCEPTION :'||'OTHERS');
3758 END IF;
3759
3760 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3761 p_api_name => l_api_name,
3762 p_pkg_name => G_PKG_NAME,
3763 p_exc_name => 'OTHERS',
3764 x_msg_count => x_msg_count,
3765 x_msg_data => x_msg_data,
3766 p_api_type => '_PVT');
3767 END bill_streams_master;
3768
3769 ------------------------------------------------------------------
3770 -- Procedure BIL_STREAMS to bill outstanding stream elements
3771 ------------------------------------------------------------------
3772 PROCEDURE bill_streams
3773 (p_api_version IN NUMBER
3774 ,p_init_msg_list IN VARCHAR2
3775 ,x_return_status OUT NOCOPY VARCHAR2
3776 ,x_msg_count OUT NOCOPY NUMBER
3777 ,x_msg_data OUT NOCOPY VARCHAR2
3778 ,p_commit IN VARCHAR2
3779 ,p_ia_contract_type IN VARCHAR2 --modified by zrehman for Bug#6788005 on 01-Feb-2008
3780 ,p_contract_number IN VARCHAR2
3781 ,p_from_bill_date IN DATE
3782 ,p_to_bill_date IN DATE
3783 ,p_cust_acct_id IN NUMBER
3784 ,p_inv_cust_acct_id IN NUMBER --modified by zrehman for Bug#6788005 on 01-Feb-2008
3785 ,p_assigned_process IN VARCHAR2
3786 ,p_source IN VARCHAR2
3787 ) IS
3788
3789 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
3790 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3791 l_api_version CONSTANT NUMBER := 1;
3792 --Bug 7584183-Added by kkorrapo
3793 l_request_id NUMBER := -1;
3794 CURSOR req_id_csr IS
3795 SELECT
3796 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
3797 FROM dual;
3798 --Bug 7584183-Addition end
3799
3800 BEGIN
3801
3802 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3803 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE,'okl_stream_billing_pvt'
3804 ,'Begin(+)');
3805 END IF;
3806 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3807 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Start time Bill_Streams : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
3808 END IF;
3809 -- ------------------------
3810 -- Print Input variables
3811 -- ------------------------
3812 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3813 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_commit '||p_commit);
3814 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_contract_number '||p_contract_number);
3815 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_from_bill_date '||p_from_bill_date);
3816 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_to_bill_date '||p_to_bill_date);
3817 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_source '||p_source);
3818
3819 END IF;
3820 l_return_status := Okl_Api.START_ACTIVITY(
3821 p_api_name => l_api_name,
3822 p_pkg_name => G_PKG_NAME,
3823 p_init_msg_list => p_init_msg_list,
3824 l_api_version => l_api_version,
3825 p_api_version => p_api_version,
3826 p_api_type => '_PVT',
3827 x_return_status => l_return_status);
3828
3829 --Bug 7584183-Added by kkorrapo
3830 OPEN req_id_csr;
3831 FETCH req_id_csr INTO l_request_id;
3832 CLOSE req_id_csr;
3833 --Bug 7584183-Addition end
3834
3835 bill_streams_master
3836 (p_api_version => p_api_version
3837 ,p_init_msg_list => p_init_msg_list
3838 ,x_return_status => x_return_status
3839 ,x_msg_count => x_msg_count
3840 ,x_msg_data => x_msg_data
3841 ,p_commit => p_commit
3842 ,p_ia_contract_type => p_ia_contract_type --modified by zrehman for Bug#6788005 on 01-Feb-2008
3843 ,p_contract_number => p_contract_number
3844 ,p_from_bill_date => p_from_bill_date
3845 ,p_to_bill_date => p_to_bill_date
3846 ,p_cust_acct_id => p_cust_acct_id
3847 ,p_inv_cust_acct_id => p_inv_cust_acct_id --modified by zrehman for Bug#6788005 on 01-Feb-2008
3848 ,p_assigned_process => p_assigned_process
3849 ,p_source => p_source
3850 ,p_request_id => l_request_id); -- Bug 7584183
3851
3852
3853 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3854 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE,'okl_stream_billing_pvt'
3855 ,'End(-)');
3856 END IF;
3857
3858 -- -------------------------------------------
3859 -- Purge data from the Parallel process Table
3860 -- -------------------------------------------
3861 IF p_assigned_process IS NOT NULL THEN
3862
3863 DELETE OKL_PARALLEL_PROCESSES
3864 WHERE assigned_process = p_assigned_process;
3865
3866 COMMIT;
3867
3868 END IF;
3869
3870 Okl_Api.END_ACTIVITY (
3871 x_msg_count => x_msg_count,
3872 x_msg_data => x_msg_data);
3873
3874 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3875 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End time Bill_Streams : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
3876
3877 END IF;
3878 if l_warning_status = 'W' then
3879 x_return_status := 'W';
3880 end if;
3881
3882 EXCEPTION
3883
3884 ------------------------------------------------------------
3885 -- Exception handling
3886 ------------------------------------------------------------
3887
3888 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3889 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (EXCP) => '||SQLERRM);
3890
3891 IF p_assigned_process IS NOT NULL THEN
3892 DELETE OKL_PARALLEL_PROCESSES
3893 WHERE assigned_process = p_assigned_process;
3894 COMMIT;
3895 END IF;
3896
3897 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3898 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3899 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
3900 END IF;
3901
3902 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3903 p_api_name => l_api_name,
3904 p_pkg_name => G_PKG_NAME,
3905 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
3906 x_msg_count => x_msg_count,
3907 x_msg_data => x_msg_data,
3908 p_api_type => '_PVT');
3909
3910 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3911 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
3912
3913 IF p_assigned_process IS NOT NULL THEN
3914 DELETE OKL_PARALLEL_PROCESSES
3915 WHERE assigned_process = p_assigned_process;
3916 COMMIT;
3917 END IF;
3918
3919 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3920 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3921 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
3922 END IF;
3923
3924 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3925 p_api_name => l_api_name,
3926 p_pkg_name => G_PKG_NAME,
3927 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3928 x_msg_count => x_msg_count,
3929 x_msg_data => x_msg_data,
3930 p_api_type => '_PVT');
3931
3932 WHEN OTHERS THEN
3933 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (OTHERS 1) => '||SQLERRM);
3934
3935 IF p_assigned_process IS NOT NULL THEN
3936 DELETE OKL_PARALLEL_PROCESSES
3937 WHERE assigned_process = p_assigned_process;
3938 COMMIT;
3939 END IF;
3940
3941 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3942 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_stream_billing_pvt',
3943 'EXCEPTION :'||'OTHERS');
3944 END IF;
3945
3946 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3947 p_api_name => l_api_name,
3948 p_pkg_name => G_PKG_NAME,
3949 p_exc_name => 'OTHERS',
3950 x_msg_count => x_msg_count,
3951 x_msg_data => x_msg_data,
3952 p_api_type => '_PVT');
3953
3954 END bill_streams;
3955
3956 END Okl_Stream_Billing_Pvt;