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