[Home] [Help]
PACKAGE BODY: APPS.OKL_CNTRCT_FIN_EXTRACT_PVT
Source
1 PACKAGE BODY OKL_CNTRCT_FIN_EXTRACT_PVT AS
2 /* $Header: OKLRCFEB.pls 120.2.12010000.19 2010/02/16 04:49:37 rpillay noship $ */
3
4 PROCEDURE write_to_log(
5 p_message IN VARCHAR2
6 ) IS
7 BEGIN
8 fnd_file.put_line(fnd_file.log, p_message);
9 END write_to_log;
10
11
12 PROCEDURE write_to_output(
13 p_message IN VARCHAR2
14 ) IS
15 BEGIN
16 fnd_file.put_line(fnd_file.output, p_message);
17 END write_to_output;
18
19
20
21 /*========================================================================
22 | PUBLIC PROCEDURE Pull_extract_data
23 |
24 | DESCRIPTION
25 | This procedure fetches data for Contract Financial Report and inserts
26 | into okl_cntrct_fin_extract_t
27 |
28 | CALLED FROM
29 | Concurrent Program "Child Program -- Contract Financial Report"
30 |
31 | CALLS PROCEDURES/FUNCTIONS
32 |
33 | KNOWN ISSUES
34 |
35 | NOTES
36 |
37 |
38 | MODIFICATION HISTORY
39 | Date Author Description of Changes
40 | 18-Aug-2008 DJANASWA Created
41 | 22-Sep-2008 SECHAWLA Enabled for parallel processing
42 | 11-Dec-2008 RBANERJE Bug7589271: Switched seeded
43 | function calls for scheduled and
44 | actual principal balance
45 |
46 *=======================================================================*/
47
48 PROCEDURE pull_extract_data (
49 p_api_version IN NUMBER
50 ,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
51 ,x_return_status OUT NOCOPY VARCHAR2
52 ,x_msg_count OUT NOCOPY NUMBER
53 ,x_msg_data OUT NOCOPY VARCHAR2,
54 x_row_count OUT NOCOPY VARCHAR2,
55 P_OPERATING_UNIT IN NUMBER,
56 -- P_REPORT_DATE IN VARCHAR2, sechawla 25-sep-09 8890513
57 P_START_DATE_FROM IN VARCHAR2,
58 P_START_DATE_TO IN VARCHAR2,
59 P_AR_INFO_YN IN VARCHAR2,
60 P_BOOK_CLASS IN VARCHAR2,
61 P_LEASE_PRODUCT IN VARCHAR2,
62 P_CONTRACT_STATUS IN VARCHAR2,
63 P_CUSTOMER_NUMBER IN VARCHAR2,
64 P_CUSTOMER_NAME IN VARCHAR2,
65 P_SIC_CODE IN VARCHAR2,
66 P_VENDOR_NUMBER IN VARCHAR2,
67 P_VENDOR_NAME IN VARCHAR2,
68 P_SALES_CHANNEL IN VARCHAR2,
69 P_GEN_ACCRUAL IN VARCHAR2,
70 P_END_DATE_FROM IN VARCHAR2,
71 P_END_DATE_TO IN VARCHAR2,
72 P_TERMINATE_DATE_FROM IN DATE,
73 P_TERMINATE_DATE_TO IN DATE,
74 P_DELETE_DATA_YN IN VARCHAR2,
75 p_num_processes IN NUMBER,
76 p_assigned_process IN VARCHAR2
77 ) IS
78 l_last_updated_by NUMBER := Fnd_Global.USER_ID;
79 l_last_update_login NUMBER := Fnd_Global.LOGIN_ID;
80 l_request_id NUMBER := Fnd_Global.CONC_REQUEST_ID; --Request ID of this (Child) program
81 l_parent_request_id NUMBER := Fnd_Global.conc_priority_request; --Request ID of Master program
82 l_program_id NUMBER := Fnd_Global.CONC_PROGRAM_ID;
83 l_program_app_id NUMBER := Fnd_Global.PROG_APPL_ID ;
84 l_program_login_id NUMBER := Fnd_Global.conc_login_id ;
85
86 l_api_name CONSTANT VARCHAR2(30) := 'pull_extract_data';
87 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
88 l_api_version CONSTANT NUMBER := 1;
89 -- Start fix for bug 7646416
90 -- l_report_date DATE; sechawla 25-sep-09 8890513
91 -- End fix for bug 7646416
92
93 BEGIN
94
95 l_return_status := Okl_Api.START_ACTIVITY(
96 p_api_name => l_api_name,
97 p_pkg_name => G_PKG_NAME,
98 p_init_msg_list => p_init_msg_list,
99 l_api_version => l_api_version,
100 p_api_version => p_api_version,
101 p_api_type => '_PVT',
102 x_return_status => x_return_status);
103
104
105 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
106 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
107 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
108 RAISE OKC_API.G_EXCEPTION_ERROR;
109 END IF;
110
111 write_to_log('Parameters in pull_extract_data ...');
112 write_to_log('P_OPERATING_UNIT = '||P_OPERATING_UNIT);
113 -- write_to_log('P_REPORT_DATE = '||P_REPORT_DATE); sechawla 25-sep-09 8890513
114
115 write_to_log('P_START_DATE_FROM = '||P_START_DATE_FROM);
116 write_to_log('P_START_DATE_TO = '||P_START_DATE_TO);
117 write_to_log('P_AR_INFO_YN = '||P_AR_INFO_YN);
118 write_to_log('P_BOOK_CLASS = '||P_BOOK_CLASS);
119 write_to_log('P_LEASE_PRODUCT = '||P_LEASE_PRODUCT);
120 write_to_log('P_CONTRACT_STATUS = '||P_CONTRACT_STATUS);
121 write_to_log('P_CUSTOMER_NUMBER = '||P_CUSTOMER_NUMBER);
122 write_to_log('P_CUSTOMER_NAME = '||P_CUSTOMER_NAME);
123 write_to_log('P_SIC_CODE = '||P_SIC_CODE);
124 write_to_log('P_VENDOR_NUMBER = '||P_VENDOR_NUMBER);
125 write_to_log('P_VENDOR_NAME = '||P_VENDOR_NAME);
126 write_to_log('P_SALES_CHANNEL = '||P_SALES_CHANNEL);
127 write_to_log('P_GEN_ACCRUAL = '||P_GEN_ACCRUAL);
128 write_to_log('P_END_DATE_FROM = '||P_END_DATE_FROM);
129 write_to_log('P_END_DATE_TO = '||P_END_DATE_TO);
130 write_to_log('P_TERMINATE_DATE_FROM = '||P_TERMINATE_DATE_FROM);
131 write_to_log('P_TERMINATE_DATE_TO = '||P_TERMINATE_DATE_TO);
132 write_to_log('P_DELETE_DATA_YN '||P_DELETE_DATA_YN);
133 write_to_log('p_num_processes = '||p_num_processes);
134 write_to_log('p_assigned_process = '||p_assigned_process);
135
136
137 --g_request_id := l_parent_request_id;
138
139 --set additional paramaters needed by formulas
140
141 Okl_Execute_Formula_Pub.g_additional_parameters(1).name := 'p_accrual_date';
142 --Okl_Execute_Formula_Pub.g_additional_parameters(1).value := P_REPORT_DATE ; sechawla 25-sep-09 8890513
143 --Bug# 7646324
144 Okl_Execute_Formula_Pub.g_additional_parameters(1).value := to_char(sysdate,'MM/DD/YYYY'); --sechawla 25-sep-09 8890513
145
146 Okl_Execute_Formula_Pub.g_additional_parameters(2).name := 'p_provision_date';
147 --Okl_Execute_Formula_Pub.g_additional_parameters(2).value := P_REPORT_DATE ; sechawla 25-sep-09 8890513
148 --Bug# 7646324
149 Okl_Execute_Formula_Pub.g_additional_parameters(2).value := to_char(sysdate,'MM/DD/YYYY'); --sechawla 25-sep-09 8890513
150
151 Okl_Execute_Formula_Pub.g_additional_parameters(3).name := 'p_last_int_calc_date';
152 /*
153 -- Start fix for bug 7646416
154 --Okl_Execute_Formula_Pub.g_additional_parameters(3).value := P_REPORT_DATE ;
155 l_report_date := FND_DATE.CANONICAL_TO_DATE(P_REPORT_DATE);
156 Okl_Execute_Formula_Pub.g_additional_parameters(3).value := to_char(l_report_date, 'MM/DD/YYYY');
157 -- End fix for bug 7646416
158 */
159 --Bug# 7646324
160 Okl_Execute_Formula_Pub.g_additional_parameters(3).value := to_char(sysdate,'MM/DD/YYYY'); --sechawla 25-sep-09 8890513
161
162 Okl_Execute_Formula_Pub.g_additional_parameters(4).name := 'p_period_end_date';
163 --Okl_Execute_Formula_Pub.g_additional_parameters(4).value := P_REPORT_DATE ;sechawla 25-sep-09 8890513
164 --Bug# 7646324
165 Okl_Execute_Formula_Pub.g_additional_parameters(4).value := to_char(sysdate,'MM/DD/YYYY'); --sechawla 25-sep-09 8890513
166
167 --Bug# 7646324
168 Okl_Execute_Formula_Pub.g_additional_parameters(5).name := 'p_rep_type';
169 Okl_Execute_Formula_Pub.g_additional_parameters(5).value := 'PRIMARY';
170
171 --------------------------------------------------------------
172 -- debug section
173 -- select khr_CONTRACT_ID, count(*) from okl_cntrct_fin_extract_t group by contract_id having count(*) > 1
174 -- query to pull NON-OKL contracts
175 -- select * from okl_cntrct_fin_extract_t where scs_code in (select code from okc_subclasses_b where cls_code <> 'OKL')
176 ------------------------------------------------------------------
177 MO_GLOBAL.init('OKL');
178 MO_GLOBAL.set_policy_context('S',P_OPERATING_UNIT);
179
180
181 IF P_AR_INFO_YN like 'YES' THEN
182
183 INSERT INTO OKL_CNTRCT_FIN_EXTRACT_T
184 (
185 REQUEST_ID,
186 CONTRACT_DOCUMENT_ID,
187 CONTRACT_NUMBER,
188 CONTRACT_OPERATING_UNIT_ID,
189 CONTRACT_INVENTORY_ORG_ID,
190 CONTRACT_STATUS_CODE,
191 CONTRACT_CURRENCY,
192 CONTRACT_TEMPLATE_INDICATOR,
193 CONTRACT_CUST_PURCH_ORDER_NUM,
194 CONTRACT_TEMPLATE_NAME,
195 CONTRACT_APPROVED_DATE,
196 CONTRACT_CANCELED_TIMESTAMP,
197 CONTRACT_SIGNED_DATE,
198 CONTRACT_TERMINATION_DATE,
199 CONTRACT_START_DATE,
200 CONTRACT_END_DATE,
201 CUSTOMER_ACCOUNT_ID,
202 CUST_BILL_TO_SITE_USE_ID,
203 CUST_PAYMENT_TERM_ID,
204 CONTRACT_ORIG_SYSTEM_SOURCE,
205 CONTRACT_ID,
206 CONTRACT_SALES_REGION_ID,
207 VENDOR_PROGRAM_ID,
208 CONTRACT_FINANCIAL_PRODUCT_ID,
209 CONTRACT_DATE_DEAL_ACTIVATED,
210 CONTRACT_DATE_DEAL_TRANSFERD,
211 CONTRACT_TERM_DURATION,
212 CONTRACT_HDR_DFF_CATEGORY,
213 CONTRACT_HDR_DFF1,
214 CONTRACT_HDR_DFF2,
215 CONTRACT_HDR_DFF3,
216 CONTRACT_HDR_DFF4,
217 CONTRACT_HDR_DFF5,
218 CONTRACT_HDR_DFF6,
219 CONTRACT_HDR_DFF7,
220 CONTRACT_HDR_DFF8,
221 CONTRACT_HDR_DFF9,
222 CONTRACT_HDR_DFF10,
223 CONTRACT_HDR_DFF11,
224 CONTRACT_HDR_DFF12,
225 CONTRACT_HDR_DFF13,
226 CONTRACT_HDR_DFF14,
227 CONTRACT_HDR_DFF15,
228 CONTRACT_ACCRUAL_STATUS,
229 CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
230 CONTRACT_CREDIT_ACT_YN,
231 CONTRACT_CONVERTED_ACCOUNT_YN,
232 CONTRACT_AFTER_TAX_YIELD,
233 CONTRACT_IMPL_INTEREST_RATE,
234 CONTRACT_LAST_INT_CALC_DATE,
235 CONTRACT_BOOK_CLASS_CODE,
236 CONTRACT_BOOK_CLASSIFICATION,
237 CONTRACT_PRE_TAX_IRR,
238 CONTRACT_AFTER_TAX_IRR,
239 CONTRACT_EXP_DELIVERY_DATE,
240 CONTRACT_ACCEPTANCE_DATE,
241 CONTRACT_PREFUND_ELIG_YN,
242 CONTRACT_REVOL_CREDIT_ELIG_YN,
243 CONTRACT_CURRENCY_CONV_TYPE,
244 CONTRACT_CURRENCY_CONV_RATE,
245 CONTRACT_CURRENCY_CONV_DATE,
246 CONTRACT_MULTI_GAAP_ELIG_YN,
247 CONTRACT_INVESTOR_ASSIGN_YN,
248 CONTRACT_SECURITIZATION_TYPE,
249 CONTRACT_SUB_AFTER_TAX_YIELD,
250 CONTRACT_SUB_IMPL_INT_RATE,
251 CONTRACT_SUB_PRE_TAX_IRR,
252 CONTRACT_SUB_AFTER_TAX_IRR,
253 CONTRACT_CRDTLINE_TRNSFR_AMT,
254 CONTRACT_CRDTLINE_NETRNSFR_AMT,
255 CONTRACT_CRDTLINE_LIMIT,
256 CONTRACT_CRDTLINE_FUNDING_AMT,
257 CONTRACT_TEMPL_TYPE_CODE,
258 CONTRACT_TRADEIN_DESCRIPTION,
259 CONTRACT_TRADEIN_DATE,
260 CONTRACT_LEGAL_ENTITY_ID,
261 CONTRACT_FIRST_ACTIVITY_DATE,
262 -- CONTRACT_SALESTYPE_YN,
263 --formulas
264 CONTRACT_ACCUMULATED_DEP,
265 CONTRACT_CAPITALIZED_REDUCTION,
266 CONTRACT_CAPITALIZED_FEE,
267 CONTRACT_CAPITALIZED_INTEREST,
268 CONTRACT_DISCOUNT,
269 CONTRACT_ASSET_COST,
270 CONTRACT_FINANCED_FEE,
271 CONTRACT_NEXT_PAYMENT_AMT,
272 CONTRACT_TOTAL_ACCRUED_INT,
273 CONTRACT_TOTAL_ACTUAL_INT,
274 CONTRACT_SUBSIDY_AMOUNT,
275 CONTRACT_UNACCRUED_SUBSIDY,
276 CONTRACT_UNBILLED_DUE_AMOUNT,
277 CONTRACT_UNBILLED_RECEIVABLES,
278 CONTRACT_OEC,
279 CONTRACT_ACTUAL_PRINC_BAL,
280 ROLLOVER_FEE_AMOUNT,
281 CONTRACT_TRADEIN_AMOUNT,
282 TOTAL_AMOUNT_PAID_TO_DEALER,
283 TOTAL_DEBITS_FOR_PREFUNDING,
284 CONTRACT_PREFUNDED_AMOUNT,
285 CONTRACT_BILLED_RENTS,
286 CONTRACT_BILLED_RENTS_FUNC,
287 CONTRACT_BILLED_NON_RENTS,
288 CONTRACT_BILLED_NON_RENTS_FUNC,
289 SUPPLIER_RETENTION_FUNDING_AMT,
290 ASSETS_FUNDED_AMOUNT,
291 EXPENSE_FUNDED_AMOUNT,
292 CONTRACT_IDC_AMOUNT,
293 CONTRACT_SCHEDULED_PRINC_BAL,
294 CONTRACT_INTEREST_RATE,
295 CONTRACT_DAYS_TO_ACCRUE,
296 CONTRACT_DAYS_IN_YEAR,
297 CONTRACT_CAP_AMOUNT,
298 CONTRACT_FINANCED_AMOUNT,
299 CONTRACT_VAR_INCOME_ACCRUAL,
300 TOTAL_PREFUNDING_AMOUNT,
301 OPERATING_UNIT,
302 OPERATING_UNIT_SHORT_CODE,
303 CONTRACT_STATUS_TYPE_CODE,
304 CONTRACT_STATUS,
305 CONTRACT_FEE_INCOME,
306 ACC_ENGINE_TEMPL_SET_ID,
307 CONTRACT_FIN_PROD_TEMPLATE_ID,
308 CONTRACT_FINANCIAL_PRODUCT,
309 CONTRACT_FIN_PROD_DESC,
310 REPORTING_PRODUCT_ID,
311 CONTRACT_FIN_PROD_STATUS_CODE,
312 CONTRACT_FIN_PROD_EFF_FROM_DT,
313 CONTRACT_FIN_PROD_EFF_TO_DT,
314 REPORTING_PRODUCT,
315 VENDOR_PROGRAM_NAME,
316 VENDOR_NUMBER,
317 VENDOR_NAME,
318 VENDOR_TYPE_CODE,
319 VENDOR_BILL_TO_SITE_USE_ID,
320 VENDOR_PARTY_ROLE,
321 TOTAL_UPFRONT_TAX_ON_ASSETS,
322 TOTAL_NUMBER_OF_ASSETS,
323 FUNCTIONAL_CURRENCY,
324 LEDGER_ID,
325 LEDGER,
326 CONTRACT_EARLIEST_BILL_DATE,
327 CONTRACT_SALES_REP,
328 LEGAL_ENTITY,
329 CUSTOMER_PARTY_NUMBER,
330 CUSTOMER_PARTY_NAME,
331 CUSTOMER_PARTY_TYPE,
332 CUSTOMER_SIC_CODE,
333 CUSTOMER_HQ_BRANCH_IND,
334 CUSTOMER_TAX_REFERENCE,
335 CUSTOMER_DUNS_NUMBER,
336 CUSTOMER_ALIAS,
337 CUSTOMER_COUNTRY,
338 CUSTOMER_ADDRESS1,
339 CUSTOMER_ADDRESS2,
340 CUSTOMER_ADDRESS3,
341 CUSTOMER_ADDRESS4,
342 CUSTOMER_CITY,
343 CUSTOMER_POSTAL_CODE,
344 CUSTOMER_STATE,
345 CUSTOMER_PROVINCE,
346 CUSTOMER_COUNTY,
347 CUSTOMER_SIC_CODE_TYPE,
348 CUSTOMER_URL,
349 FINANCIAL_INFO_FISCAL_YEAR,
350 CUSTOMER_FISCAL_YEAREND_MONTH,
351 TOTAL_NUM_OF_EMPLOYEES,
352 CURR_FISC_YEAR_POTENTIAL_REV,
353 NEXT_FISC_YEAR_POTENTIAL_REV,
354 CUSTOMER_YEAR_ESTABLISHED,
355 GEN_SERV_ADMIN_INDICATOR,
356 CUSTOMER_ORG_NAME_PHONETIC,
357 CUSTOMER_COMPETITOR_YN,
358 CUSTOMER_ALIAS2,
359 CUSTOMER_ALIAS3,
360 CUSTOMER_ALIAS4,
361 CUSTOMER_ALIAS5,
362 CUSTOMER_PRIM_PHONE_PURPOSE,
363 CUSTOMER_PRIM_PHONE_TYPE,
364 CUSTOMER_PRIM_PH_COUNTRY_CODE,
365 CUSTOMER_PRIM_PH_AREA_CODE,
366 CUSTOMER_PRIM_PHONE_NUMBER,
367 CUSTOMER_PRIM_PHONE_EXTN,
368 CUSTOMER_HOME_COUNTRY,
369 CUSTOMER_STATUS,
370 CUSTOMER_PARTY_ID,
371 CUSTOMER_ACCOUNT_NUMBER,
372 CUSTOMER_ACC_DFF_CATEGORY,
373 CUSTOMER_ACC_DFF1,
374 CUSTOMER_ACC_DFF2,
375 CUSTOMER_ACC_DFF3,
376 CUSTOMER_ACC_DFF4,
377 CUSTOMER_ACC_DFF5,
378 CUSTOMER_ACC_DFF6,
379 CUSTOMER_ACC_DFF7,
380 CUSTOMER_ACC_DFF8,
381 CUSTOMER_ACC_DFF9,
382 CUSTOMER_ACC_DFF10,
383 CUSTOMER_ACC_DFF11,
384 CUSTOMER_ACC_DFF12,
385 CUSTOMER_ACC_DFF13,
386 CUSTOMER_ACC_DFF14,
387 CUSTOMER_ACC_DFF15,
388 CUSTOMER_ACC_DFF16,
389 CUSTOMER_ACC_DFF17,
390 CUSTOMER_ACC_DFF18,
391 CUSTOMER_ACC_DFF19,
392 CUSTOMER_ACC_DFF20,
393 CUSTOMER_ACCOUNT_STATUS,
394 CUSTOMER_TYPE,
395 CUSTOMER_SALES_CHANNEL,
396 CUSTOMER_ACC_ESTAB_DATE,
397 CUSTOMER_ACCOUNT_NAME,
398 CUSTOMER_ACCOUNT_COMMENTS,
399 CUSTOMER_LAST_STATUS_UPD_DT,
400 CUSTOMER_PARTY_SITE_ID,
401 CUSTOMER_PARTY_SITE_NUMBER,
402 CUSTOMER_PARTY_SITE_NAME,
403 CUSTOMER_PARTY_SITE_LOC_ID,
404 CUSTOMER_IDENTIFYING_ADR_FLAG,
405 CUSTOMER_PARTY_SITE_STATUS,
406 CUSTOMER_PARTY_SITE_ADDRESSEE,
407 CUSTOMER_SITE_GLOB_LOC_NUM,
408 CUSTOMER_PARTY_SITE_USE_ID,
409 CUSTOMER_PARTY_SITE_USE_TYPE,
410 CUSTOMER_PARTY_SITE_COMMENTS,
411 CUSTOMER_PARTY_SITE_USE_STATUS,
412
413 CONTRACT_PAST_DUE_AMT_1_30,
414 CONTRACT_PAST_DUE_AMT_31_60,
415 CONTRACT_PAST_DUE_AMT_61_90,
416 CONTRACT_PAST_DUE_AMT_91_120,
417 CONTRACT_PAST_DUE_AMT_120_Plus,
418 CONTRACT_TOT_PAST_DUE_AMT,
419 CONTRACT_TOT_BILLED_RECEIVABLE,
420
421 CONTRACT_RESIDUAL_AMOUNT,
422 CONTRACT_RENT_AMOUNT,
423 CONTRACT_ACC_DEPRECIATION,
424 NET_INVESTMENT,
425 NET_INVESTMENT_FUNCTIONAL,
426
427 CONTRACT_AMOUNT_PREFUNDED,
428 CONTRACT_TOTAL_MISC_FUND,
429
430 CREATED_BY,
431 CREATION_DATE,
432 LAST_UPDATED_BY,
433 LAST_UPDATE_DATE,
434 LAST_UPDATE_LOGIN,
435 PROGRAM_APPLICATION_ID,
436 PROGRAM_ID,
437 PROGRAM_LOGIN_ID,
438 PROGRAM_UPDATE_DATE
439 )
440
441 select
442 REQUEST_ID,
443 CONTRACT_DOCUMENT_ID,
444 CONTRACT_NUMBER,
445 CONTRACT_OPERATING_UNIT_ID,
446 CONTRACT_INVENTORY_ORG_ID,
447 CONTRACT_STATUS_CODE,
448 CONTRACT_CURRENCY,
449 CONTRACT_TEMPLATE_INDICATOR,
450 CONTRACT_CUST_PURCH_ORDER_NUM,
451 CONTRACT_TEMPLATE_NAME,
452 CONTRACT_APPROVED_DATE,
453 CONTRACT_CANCELED_TIMESTAMP,
454 CONTRACT_SIGNED_DATE,
455 CONTRACT_TERMINATION_DATE,
456 CONTRACT_START_DATE,
457 CONTRACT_END_DATE,
458 CUSTOMER_ACCOUNT_ID,
459 CUST_BILL_TO_SITE_USE_ID,
460 CUST_PAYMENT_TERM_ID,
461 CONTRACT_ORIG_SYSTEM_SOURCE,
462 CONTRACT_ID,
463 CONTRACT_SALES_REGION_ID,
464 VENDOR_PROGRAM_ID,
465 CONTRACT_FINANCIAL_PRODUCT_ID,
466 CONTRACT_DATE_DEAL_ACTIVATED,
467 CONTRACT_DATE_DEAL_TRANSFERD,
468 CONTRACT_TERM_DURATION,
469 CONTRACT_HDR_DFF_CATEGORY,
470 CONTRACT_HDR_DFF1,
471 CONTRACT_HDR_DFF2,
472 CONTRACT_HDR_DFF3,
473 CONTRACT_HDR_DFF4,
474 CONTRACT_HDR_DFF5,
475 CONTRACT_HDR_DFF6,
476 CONTRACT_HDR_DFF7,
477 CONTRACT_HDR_DFF8,
478 CONTRACT_HDR_DFF9,
479 CONTRACT_HDR_DFF10,
480 CONTRACT_HDR_DFF11,
481 CONTRACT_HDR_DFF12,
482 CONTRACT_HDR_DFF13,
483 CONTRACT_HDR_DFF14,
484 CONTRACT_HDR_DFF15,
485 CONTRACT_ACCRUAL_STATUS,
486 CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
487 CONTRACT_CREDIT_ACT_YN,
488 CONTRACT_CONVERTED_ACCOUNT_YN,
489 CONTRACT_AFTER_TAX_YIELD,
490 CONTRACT_IMPL_INTEREST_RATE,
491 CONTRACT_LAST_INT_CALC_DATE,
492 CONTRACT_BOOK_CLASS_CODE,
493 CONTRACT_BOOK_CLASSIFICATION,
494 CONTRACT_PRE_TAX_IRR,
495 CONTRACT_AFTER_TAX_IRR,
496 CONTRACT_EXP_DELIVERY_DATE,
497 CONTRACT_ACCEPTANCE_DATE,
498 CONTRACT_PREFUND_ELIG_YN,
499 CONTRACT_REVOL_CREDIT_ELIG_YN,
500 CONTRACT_CURRENCY_CONV_TYPE,
501 CONTRACT_CURRENCY_CONV_RATE,
502 CONTRACT_CURRENCY_CONV_DATE,
503 CONTRACT_MULTI_GAAP_ELIG_YN,
504 CONTRACT_INVESTOR_ASSIGN_YN,
505 CONTRACT_SECURITIZATION_TYPE,
506 CONTRACT_SUB_AFTER_TAX_YIELD,
507 CONTRACT_SUB_IMPL_INT_RATE,
508 CONTRACT_SUB_PRE_TAX_IRR,
509 CONTRACT_SUB_AFTER_TAX_IRR,
510 CONTRACT_CRDTLINE_TRNSFR_AMT,
511 CONTRACT_CRDTLINE_NETRNSFR_AMT,
512 CONTRACT_CRDTLINE_LIMIT,
513 CONTRACT_CRDTLINE_FUNDING_AMT,
514 CONTRACT_TEMPL_TYPE_CODE,
515 CONTRACT_TRADEIN_DESCRIPTION,
516 CONTRACT_TRADEIN_DATE,
517 CONTRACT_LEGAL_ENTITY_ID,
518 CONTRACT_FIRST_ACTIVITY_DATE,
519 --formulas
520 CONTRACT_ACCUMULATED_DEP,
521 CONTRACT_CAPITALIZED_REDUCTION,
522 CONTRACT_CAPITALIZED_FEE,
523 CONTRACT_CAPITALIZED_INTEREST,
524 CONTRACT_DISCOUNT,
525 CONTRACT_ASSET_COST,
526 CONTRACT_FINANCED_FEE,
527 CONTRACT_NEXT_PAYMENT_AMT,
528 CONTRACT_TOTAL_ACCRUED_INT,
529 CONTRACT_TOTAL_ACTUAL_INT,
530 CONTRACT_SUBSIDY_AMOUNT,
531 CONTRACT_UNACCRUED_SUBSIDY,
532 CONTRACT_UNBILLED_DUE_AMOUNT,
533 CONTRACT_UNBILLED_RECEIVABLES,
534 CONTRACT_OEC,
535 CONTRACT_ACTUAL_PRINC_BAL,
536 ROLLOVER_FEE_AMOUNT,
537 CONTRACT_TRADEIN_AMOUNT,
538 TOTAL_AMOUNT_PAID_TO_DEALER,
539 TOTAL_DEBITS_FOR_PREFUNDING,
540 CONTRACT_PREFUNDED_AMOUNT,
541 CONTRACT_BILLED_RENTS,
542 (CONTRACT_BILLED_RENTS * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_RENTS_FUNC,
543 (CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) CONTRACT_BILLED_NON_RENTS,
544 ((CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_NON_RENTS_FUNC,
545 SUPPLIER_RETENTION_FUNDING_AMT,
546 ASSETS_FUNDED_AMOUNT,
547 EXPENSE_FUNDED_AMOUNT,
548 CONTRACT_IDC_AMOUNT,
549 CONTRACT_SCHEDULED_PRINC_BAL,
550 CONTRACT_INTEREST_RATE,
551 CONTRACT_DAYS_TO_ACCRUE,
552 CONTRACT_DAYS_IN_YEAR,
553 (CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+CONTRACT_CAPITALIZED_INTEREST) CONTRACT_CAP_AMOUNT,
554 (CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+ CONTRACT_CAPITALIZED_INTEREST+CONTRACT_FINANCED_FEE) CONTRACT_FINANCED_AMOUNT,
555 decode(CONTRACT_DAYS_IN_YEAR,0,0,CONTRACT_SCHEDULED_PRINC_BAL * CONTRACT_INTEREST_RATE * CONTRACT_DAYS_TO_ACCRUE/CONTRACT_DAYS_IN_YEAR) CONTRACT_VAR_INCOME_ACCRUAL,
556 (CONTRACT_PREFUNDED_AMOUNT + TOTAL_DEBITS_FOR_PREFUNDING) TOTAL_PREFUNDING_AMOUNT,
557 OPERATING_UNIT,
558 OPERATING_UNIT_SHORT_CODE,
559 CONTRACT_STATUS_TYPE_CODE,
560 CONTRACT_STATUS,
561 CONTRACT_FEE_INCOME,
562 ACC_ENGINE_TEMPL_SET_ID ,
563 CONTRACT_FIN_PROD_TEMPLATE_ID ,
564 CONTRACT_FINANCIAL_PRODUCT,
565 CONTRACT_FIN_PROD_DESC ,
566 REPORTING_PRODUCT_ID ,
567 CONTRACT_FIN_PROD_STATUS_CODE ,
568 CONTRACT_FIN_PROD_EFF_FROM_DT ,
569 CONTRACT_FIN_PROD_EFF_TO_DT ,
570 REPORTING_PRODUCT,
571 VENDOR_PROGRAM_NAME,
572 VENDOR_NUMBER,
573 VENDOR_NAME,
574 VENDOR_TYPE_CODE,
575 VENDOR_BILL_TO_SITE_USE_ID,
576 VENDOR_PARTY_ROLE,
577 TOTAL_UPFRONT_TAX_ON_ASSETS,
578 TOTAL_NUMBER_OF_ASSETS,
579 FUNCTIONAL_CURRENCY,
580 LEDGER_ID,
581 LEDGER,
582 CONTRACT_EARLIEST_BILL_DATE,
583 CONTRACT_SALES_REP,
584 LEGAL_ENTITY,
585 CUSTOMER_PARTY_NUMBER,
586 CUSTOMER_PARTY_NAME,
587 CUSTOMER_PARTY_TYPE,
588 CUSTOMER_SIC_CODE,
589 CUSTOMER_HQ_BRANCH_IND,
590 CUSTOMER_TAX_REFERENCE,
591 CUSTOMER_DUNS_NUMBER,
592 CUSTOMER_ALIAS,
593 CUSTOMER_COUNTRY,
594 CUSTOMER_ADDRESS1,
595 CUSTOMER_ADDRESS2,
596 CUSTOMER_ADDRESS3,
597 CUSTOMER_ADDRESS4,
598 CUSTOMER_CITY,
599 CUSTOMER_POSTAL_CODE,
600 CUSTOMER_STATE,
601 CUSTOMER_PROVINCE,
602 CUSTOMER_COUNTY,
603 CUSTOMER_SIC_CODE_TYPE,
604 CUSTOMER_URL,
605 FINANCIAL_INFO_FISCAL_YEAR,
606 CUSTOMER_FISCAL_YEAREND_MONTH,
607 TOTAL_NUM_OF_EMPLOYEES,
608 CURR_FISC_YEAR_POTENTIAL_REV,
609 NEXT_FISC_YEAR_POTENTIAL_REV,
610 CUSTOMER_YEAR_ESTABLISHED,
611 GEN_SERV_ADMIN_INDICATOR,
612 CUSTOMER_ORG_NAME_PHONETIC,
613 CUSTOMER_COMPETITOR_YN,
614 CUSTOMER_ALIAS2,
615 CUSTOMER_ALIAS3,
616 CUSTOMER_ALIAS4,
617 CUSTOMER_ALIAS5,
618 CUSTOMER_PRIM_PHONE_PURPOSE,
619 CUSTOMER_PRIM_PHONE_TYPE,
620 CUSTOMER_PRIM_PH_COUNTRY_CODE,
621 CUSTOMER_PRIM_PH_AREA_CODE,
622 CUSTOMER_PRIM_PHONE_NUMBER,
623 CUSTOMER_PRIM_PHONE_EXTN,
624 CUSTOMER_HOME_COUNTRY,
625 CUSTOMER_STATUS,
626 CUSTOMER_PARTY_ID,
627 CUSTOMER_ACCOUNT_NUMBER,
628 CUSTOMER_ACC_DFF_CATEGORY,
629 CUSTOMER_ACC_DFF1,
630 CUSTOMER_ACC_DFF2,
631 CUSTOMER_ACC_DFF3,
632 CUSTOMER_ACC_DFF4,
633 CUSTOMER_ACC_DFF5,
634 CUSTOMER_ACC_DFF6,
635 CUSTOMER_ACC_DFF7,
636 CUSTOMER_ACC_DFF8,
637 CUSTOMER_ACC_DFF9,
638 CUSTOMER_ACC_DFF10,
639 CUSTOMER_ACC_DFF11,
640 CUSTOMER_ACC_DFF12,
641 CUSTOMER_ACC_DFF13,
642 CUSTOMER_ACC_DFF14,
643 CUSTOMER_ACC_DFF15,
644 CUSTOMER_ACC_DFF16,
645 CUSTOMER_ACC_DFF17,
646 CUSTOMER_ACC_DFF18,
647 CUSTOMER_ACC_DFF19,
648 CUSTOMER_ACC_DFF20,
649 CUSTOMER_ACCOUNT_STATUS,
650 CUSTOMER_TYPE,
651 CUSTOMER_SALES_CHANNEL,
652 CUSTOMER_ACC_ESTAB_DATE,
653 CUSTOMER_ACCOUNT_NAME,
654 CUSTOMER_ACCOUNT_COMMENTS,
655 CUSTOMER_LAST_STATUS_UPD_DT,
656 CUSTOMER_PARTY_SITE_ID,
657 CUSTOMER_PARTY_SITE_NUMBER,
658 CUSTOMER_PARTY_SITE_NAME,
659 CUSTOMER_PARTY_SITE_LOC_ID,
660 CUSTOMER_IDENTIFYING_ADR_FLAG,
661 CUSTOMER_PARTY_SITE_STATUS,
662 CUSTOMER_PARTY_SITE_ADDRESSEE,
663 CUSTOMER_SITE_GLOB_LOC_NUM,
664 CUSTOMER_PARTY_SITE_USE_ID,
665 CUSTOMER_PARTY_SITE_USE_TYPE,
666 CUSTOMER_PARTY_SITE_COMMENTS,
667 CUSTOMER_PARTY_SITE_USE_STATUS,
668
669 CONTRACT_PAST_DUE_AMT_1_30,
670 CONTRACT_PAST_DUE_AMT_31_60,
671 CONTRACT_PAST_DUE_AMT_61_90,
672 CONTRACT_PAST_DUE_AMT_91_120,
673 CONTRACT_PAST_DUE_AMT_120_Plus,
674 NVL( (CONTRACT_PAST_DUE_AMT_1_30 + CONTRACT_PAST_DUE_AMT_31_60 +
675 CONTRACT_PAST_DUE_AMT_61_90 + CONTRACT_PAST_DUE_AMT_91_120 +
676 CONTRACT_PAST_DUE_AMT_120_Plus),0) CONTRACT_TOT_PAST_DUE_AMT,
677 CONTRACT_TOT_BILLED_RECEIVABLE,
678
679 CONTRACT_RESIDUAL_AMOUNT,
680 CONTRACT_RENT_AMOUNT,
681 CONTRACT_ACC_DEPRECIATION,
682 NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
683 (DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
684 'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
685 'LOAN', CONTRACT_ACTUAL_PRINC_BAL,
686 'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL,
687 'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION + CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT),
688 0))),0) NET_INVESTMENT,
689
690 DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
691 (DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
692 'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
693 'LOAN', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
694 'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
695 'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION +
696 CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
697 0))) NET_INVESTMENT_FUNCTIONAL,
698 (CONTRACT_AMOUNT_PREFUNDED + CONTRACT_FUNDED_ADJS ) CONTRACT_AMOUNT_PREFUNDED,
699 CONTRACT_AMOUNT_MANU_DISB CONTRACT_TOTAL_MISC_FUND,
700
701 CREATED_BY,
702 CREATION_DATE,
703 LAST_UPDATED_BY,
704 LAST_UPDATE_DATE,
705 LAST_UPDATE_LOGIN,
706 PROGRAM_APPLICATION_ID,
707 PROGRAM_ID,
708 PROGRAM_LOGIN_ID,
709 PROGRAM_UPDATE_DATE
710
711 from (
712 -- create table okl_fin_extract5 as
713 SELECT
714 --l_request_id REQUEST_ID,
715 l_parent_request_id REQUEST_ID,
716 chr.DOCUMENT_ID CONTRACT_DOCUMENT_ID,
717 chr.CONTRACT_NUMBER CONTRACT_NUMBER,
718 chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
719 chr.INV_ORGANIZATION_ID CONTRACT_INVENTORY_ORG_ID,
720 chr.STS_CODE CONTRACT_STATUS_CODE,
721 chr.CURRENCY_CODE CONTRACT_CURRENCY,
722 chr.TEMPLATE_YN CONTRACT_TEMPLATE_INDICATOR,
723 chr.CUST_PO_NUMBER CONTRACT_CUST_PURCH_ORDER_NUM,
724 chr.TEMPLATE_USED CONTRACT_TEMPLATE_NAME,
725 chr.DATE_APPROVED CONTRACT_APPROVED_DATE,
726 chr.DATETIME_CANCELLED CONTRACT_CANCELED_TIMESTAMP,
727 chr.DATE_SIGNED CONTRACT_SIGNED_DATE,
728 chr.DATE_TERMINATED CONTRACT_TERMINATION_DATE,
729 chr.START_DATE CONTRACT_START_DATE,
730 chr.END_DATE CONTRACT_END_DATE,
731 chr.CUST_ACCT_ID CUSTOMER_ACCOUNT_ID,
732 chr.BILL_TO_SITE_USE_ID CUST_BILL_TO_SITE_USE_ID,
733 chr.PAYMENT_TERM_ID CUST_PAYMENT_TERM_ID,
734 chr.ORIG_SYSTEM_SOURCE_CODE CONTRACT_ORIG_SYSTEM_SOURCE,
735 khr.ID CONTRACT_ID,
736 khr.ISG_ID CONTRACT_SALES_REGION_ID,
737 khr.KHR_ID VENDOR_PROGRAM_ID,
738 khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
739 --khr.DATE_CONVERSION_EFFECTIVE CONTRACT_DATE_DEAL_ACTIVATED,
740 OKL_CNTRCT_FIN_EXTRACT_PVT.first_activation_date(khr.id) CONTRACT_DATE_DEAL_ACTIVATED,
741 khr.DATE_DEAL_TRANSFERRED CONTRACT_DATE_DEAL_TRANSFERD,
742 khr.TERM_DURATION CONTRACT_TERM_DURATION,
743 khr.ATTRIBUTE_CATEGORY CONTRACT_HDR_DFF_CATEGORY,
744 khr.ATTRIBUTE1 CONTRACT_HDR_DFF1,
745 khr.ATTRIBUTE2 CONTRACT_HDR_DFF2,
746 khr.ATTRIBUTE3 CONTRACT_HDR_DFF3,
747 khr.ATTRIBUTE4 CONTRACT_HDR_DFF4,
748 khr.ATTRIBUTE5 CONTRACT_HDR_DFF5,
749 khr.ATTRIBUTE6 CONTRACT_HDR_DFF6,
750 khr.ATTRIBUTE7 CONTRACT_HDR_DFF7,
751 khr.ATTRIBUTE8 CONTRACT_HDR_DFF8,
752 khr.ATTRIBUTE9 CONTRACT_HDR_DFF9,
753 khr.ATTRIBUTE10 CONTRACT_HDR_DFF10,
754 khr.ATTRIBUTE11 CONTRACT_HDR_DFF11,
755 khr.ATTRIBUTE12 CONTRACT_HDR_DFF12,
756 khr.ATTRIBUTE13 CONTRACT_HDR_DFF13,
757 khr.ATTRIBUTE14 CONTRACT_HDR_DFF14,
758 khr.ATTRIBUTE15 CONTRACT_HDR_DFF15,
759 OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id) CONTRACT_ACCRUAL_STATUS,
760 khr.GENERATE_ACCRUAL_OVERRIDE_YN CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
761 khr.CREDIT_ACT_YN CONTRACT_CREDIT_ACT_YN,
762 khr.CONVERTED_ACCOUNT_YN CONTRACT_CONVERTED_ACCOUNT_YN,
763 khr.AFTER_TAX_YIELD CONTRACT_AFTER_TAX_YIELD,
764 khr.IMPLICIT_INTEREST_RATE CONTRACT_IMPL_INTEREST_RATE,
765 khr.DATE_LAST_INTERIM_INTEREST_CAL CONTRACT_LAST_INT_CALC_DATE,
766 khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
767 lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
768 khr.PRE_TAX_IRR CONTRACT_PRE_TAX_IRR,
769 khr.AFTER_TAX_IRR CONTRACT_AFTER_TAX_IRR,
770 khr.EXPECTED_DELIVERY_DATE CONTRACT_EXP_DELIVERY_DATE,
771 khr.ACCEPTED_DATE CONTRACT_ACCEPTANCE_DATE,
772 khr.PREFUNDING_ELIGIBLE_YN CONTRACT_PREFUND_ELIG_YN,
773 khr.REVOLVING_CREDIT_YN CONTRACT_REVOL_CREDIT_ELIG_YN,
774 khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
775 khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
776 khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
777 khr.MULTI_GAAP_YN CONTRACT_MULTI_GAAP_ELIG_YN ,
778 khr.ASSIGNABLE_YN CONTRACT_INVESTOR_ASSIGN_YN ,
779 khr.SECURITIZATION_TYPE CONTRACT_SECURITIZATION_TYPE,
780 khr.SUB_AFTER_TAX_YIELD CONTRACT_SUB_AFTER_TAX_YIELD,
781 khr.SUB_IMPL_INTEREST_RATE CONTRACT_SUB_IMPL_INT_RATE,
782 khr.SUB_PRE_TAX_IRR CONTRACT_SUB_PRE_TAX_IRR,
783 khr.SUB_AFTER_TAX_IRR CONTRACT_SUB_AFTER_TAX_IRR,
784 khr.TOT_CL_TRANSFER_AMT CONTRACT_CRDTLINE_TRNSFR_AMT,
785 khr.TOT_CL_NET_TRANSFER_AMT CONTRACT_CRDTLINE_NETRNSFR_AMT,
786 khr.TOT_CL_LIMIT CONTRACT_CRDTLINE_LIMIT,
787 khr.TOT_CL_FUNDING_AMT CONTRACT_CRDTLINE_FUNDING_AMT,
788 khr.TEMPLATE_TYPE_CODE CONTRACT_TEMPL_TYPE_CODE,
789 khr.TRADEIN_DESCRIPTION CONTRACT_TRADEIN_DESCRIPTION,
790 khr.TRADEIN_AMOUNT CONTRACT_TRADEIN_AMOUNT,
791 khr.DATE_TRADEIN CONTRACT_TRADEIN_DATE,
792 khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
793 khr.DATE_FIRST_ACTIVITY CONTRACT_FIRST_ACTIVITY_DATE,
794 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACCUMULATED_DEPRN(chr.id, null),0) CONTRACT_ACCUMULATED_DEP,
795 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_CAPITAL_REDUCTION(chr.id, null),0) CONTRACT_CAPITALIZED_REDUCTION,
796 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FEES_CAPITALIZED(chr.id, null),0) CONTRACT_CAPITALIZED_FEE,
797 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_capitalized_interest(chr.id, null),0) CONTRACT_CAPITALIZED_INTEREST,
798 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DISCOUNT(chr.id, null),0) CONTRACT_DISCOUNT,
799 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ASSET_COST(chr.id, null),0) CONTRACT_ASSET_COST,
800 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FINANCED_FEE(chr.id, null),0) CONTRACT_FINANCED_FEE,
801 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_NEXT_PAYMENT_AMOUNT(chr.id, null),0) CONTRACT_NEXT_PAYMENT_AMT,
802 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACCRUED_INT(chr.id, null),0) CONTRACT_TOTAL_ACCRUED_INT,
803 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACTUAL_INT(chr.id, null),0) CONTRACT_TOTAL_ACTUAL_INT,
804 NVL(OKL_SEEDED_FUNCTIONS_PVT.get_asset_subsidy_amount(chr.id, null),0) CONTRACT_SUBSIDY_AMOUNT,
805 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNACCRUED_SUBSIDY(chr.id, null),0) CONTRACT_UNACCRUED_SUBSIDY,
806 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_STREAMS(chr.id, null),0) CONTRACT_UNBILLED_DUE_AMOUNT,
807 DECODE(khr.deal_type,'LOAN',NULL,'LOAN-REVOLVING',NULL,NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_RECEIVABLES(chr.id, null),0)) CONTRACT_UNBILLED_RECEIVABLES,
808 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_OEC(chr.id, null),0) CONTRACT_OEC,
809 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(chr.id, null),0) CONTRACT_ACTUAL_PRINC_BAL, -- addl para
810 NVL(OKL_SEEDED_FUNCTIONS_PVT.ROLLOVER_FEE(chr.id, null),0) ROLLOVER_FEE_AMOUNT,
811 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_funded(chr.id, null),0) TOTAL_AMOUNT_PAID_TO_DEALER,
812 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_debits(chr.id, null),0) TOTAL_DEBITS_FOR_PREFUNDING,
813 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_amount_prefunded(chr.id, null),0) CONTRACT_PREFUNDED_AMOUNT,
814 -- null CONTRACT_BILLED_RENTS,
815 (select SUM(NVL(txd.amount,0))
816 from OKL_TXD_AR_LN_DTLS_B txd,
817 okl_strm_type_b sty
818 where txd.khr_id = khr.id
819 and txd.sty_id = sty.id
820 and sty.STREAM_TYPE_PURPOSE = 'RENT') CONTRACT_BILLED_RENTS,
821
822 NVL(OKL_FUNDING_PVT.get_total_retention(chr.id),0) SUPPLIER_RETENTION_FUNDING_AMT,
823 NVL(OKL_FUNDING_PVT.get_chr_oec_hasbeen_funded_amt(chr.id),0) ASSETS_FUNDED_AMOUNT,
824 NVL(OKL_FUNDING_PVT.get_chr_exp_hasbeen_funded_amt(chr.id),0) EXPENSE_FUNDED_AMOUNT,
825 NVL(OKL_SEEDED_FUNCTIONS_PVT.FEE_IDC_AMOUNT(chr.id, null),0) CONTRACT_IDC_AMOUNT,
826 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRIN_BALANCE(chr.id, null),0) CONTRACT_SCHEDULED_PRINC_BAL,
827 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_INTEREST_RATE(chr.id, null),0) CONTRACT_INTEREST_RATE, -- addl para
828 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_TO_ACCRUE(chr.id, null),0) CONTRACT_DAYS_TO_ACCRUE, -- addl para
829 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_IN_YEAR(chr.id, null),0) CONTRACT_DAYS_IN_YEAR, -- addl para
830
831 hr_org.name OPERATING_UNIT,
832 hr_org.short_code OPERATING_UNIT_SHORT_CODE,
833 status.ste_code CONTRACT_STATUS_TYPE_CODE,
834 status_tl.meaning CONTRACT_STATUS,
835
836 (select nvl(sum(nvl(kle_fee.amount,0)),0)
837 FROM okl_k_lines kle_fee, okc_k_lines_b cleb_fee, OKC_STATUSES_B status_fee
838 WHERE cleb_fee.chr_id = khr.id
839 AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
840 and cleb_fee.dnz_chr_id = khr.id
841 and cleb_fee.sts_code = status_fee.code
842 and status_fee.ste_code not like 'CANCELLED'
843 AND kle_fee.fee_type NOT IN ('FINANCED', 'ROLLOVER','SECURITY_DEPOSIT')
844 and kle_fee.id = cleb_fee.id
845 and cleb_fee.lse_id = 52) CONTRACT_FEE_INCOME,
846 pdt.AES_ID ACC_ENGINE_TEMPL_SET_ID ,
847 pdt.PTL_ID CONTRACT_FIN_PROD_TEMPLATE_ID ,
848 pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
849 pdt.DESCRIPTION CONTRACT_FIN_PROD_DESC ,
850 pdt.REPORTING_PDT_ID REPORTING_PRODUCT_ID ,
851 pdt.PRODUCT_STATUS_CODE CONTRACT_FIN_PROD_STATUS_CODE ,
852 pdt.FROM_DATE CONTRACT_FIN_PROD_EFF_FROM_DT ,
853 pdt.TO_DATE CONTRACT_FIN_PROD_EFF_TO_DT ,
854 rpt_pdt.name REPORTING_PRODUCT,
855 vendor_chr.contract_number VENDOR_PROGRAM_NAME,
856 vendor.segment1 VENDOR_NUMBER,
857 vendor.vendor_name VENDOR_NAME,
858 vendor.vendor_type_lookup_code VENDOR_TYPE_CODE,
859 CPL.bill_to_site_use_id VENDOR_BILL_TO_SITE_USE_ID,
860 cpl.role VENDOR_PARTY_ROLE,
861 (select nvl(SUM(NVL(txs.total_tax,0)),0)
862 from okl_tax_sources txs
863 where txs.khr_id = khr.id
864 AND txs.tax_line_status_code = 'ACTIVE'
865 AND txs.tax_call_type_code = 'UPFRONT_TAX') TOTAL_UPFRONT_TAX_ON_ASSETS,
866 -- null TOTAL_NUMBER_OF_ASSETS,
867 (select count(cleb.id)
868 from okc_k_lines_b cleb, okc_statuses_b status_asset
869 where cleb.chr_id = khr.id and cleb.dnz_chr_id = khr.id
870 AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
871 and cleb.sts_code = status_asset.code
872 and status_asset.ste_code not in ('TERMINATED', 'CANCELLED')
873 and cleb.lse_id = 33) TOTAL_NUMBER_OF_ASSETS,
874
875 ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
876 ledger.ledger_id LEDGER_ID,
877 ledger.name LEDGER,
878 -- min(billing_control.EARLIEST_STRM_BILL_DATE) CONTRACT_EARLIEST_BILL_DATE,
879 (select min(billing_control.EARLIEST_STRM_BILL_DATE)
880 from OKL_K_CONTROL billing_control
881 WHERE billing_control.khr_id = khr.id) CONTRACT_EARLIEST_BILL_DATE,
882
883 sales_rep.name CONTRACT_SALES_REP,
884 legal_entity.name LEGAL_ENTITY,
885
886 cust_party.PARTY_NUMBER CUSTOMER_PARTY_NUMBER,
887 cust_party.party_name CUSTOMER_PARTY_NAME,
888 cust_party.PARTY_TYPE CUSTOMER_PARTY_TYPE,
889 cust_party.SIC_CODE CUSTOMER_SIC_CODE,
890 cust_party.HQ_BRANCH_IND CUSTOMER_HQ_BRANCH_IND,
891 cust_party.TAX_REFERENCE CUSTOMER_TAX_REFERENCE,
892 cust_party.DUNS_NUMBER CUSTOMER_DUNS_NUMBER,
893 cust_party.KNOWN_AS CUSTOMER_ALIAS,
894 cust_party.COUNTRY CUSTOMER_COUNTRY,
895 cust_party.ADDRESS1 CUSTOMER_ADDRESS1,
896 cust_party.ADDRESS2 CUSTOMER_ADDRESS2,
897 cust_party.ADDRESS3 CUSTOMER_ADDRESS3,
898 cust_party.ADDRESS4 CUSTOMER_ADDRESS4,
899 cust_party.CITY CUSTOMER_CITY,
900 cust_party.POSTAL_CODE CUSTOMER_POSTAL_CODE,
901 cust_party.STATE CUSTOMER_STATE,
902 cust_party.PROVINCE CUSTOMER_PROVINCE,
903 cust_party.COUNTY CUSTOMER_COUNTY,
904 cust_party.SIC_CODE_TYPE CUSTOMER_SIC_CODE_TYPE,
905 cust_party.URL CUSTOMER_URL,
906 cust_party.ANALYSIS_FY FINANCIAL_INFO_FISCAL_YEAR,
907 cust_party.FISCAL_YEAREND_MONTH CUSTOMER_FISCAL_YEAREND_MONTH,
908 cust_party.EMPLOYEES_TOTAL TOTAL_NUM_OF_EMPLOYEES,
909 cust_party.CURR_FY_POTENTIAL_REVENUE CURR_FISC_YEAR_POTENTIAL_REV,
910 cust_party.NEXT_FY_POTENTIAL_REVENUE NEXT_FISC_YEAR_POTENTIAL_REV,
911 cust_party.YEAR_ESTABLISHED CUSTOMER_YEAR_ESTABLISHED,
912 cust_party.GSA_INDICATOR_FLAG GEN_SERV_ADMIN_INDICATOR,
913 cust_party.ORGANIZATION_NAME_PHONETIC CUSTOMER_ORG_NAME_PHONETIC,
914 cust_party.COMPETITOR_FLAG CUSTOMER_COMPETITOR_YN,
915 cust_party.KNOWN_AS2 CUSTOMER_ALIAS2,
916 cust_party.KNOWN_AS3 CUSTOMER_ALIAS3,
917 cust_party.KNOWN_AS4 CUSTOMER_ALIAS4,
918 cust_party.KNOWN_AS5 CUSTOMER_ALIAS5,
919 cust_party.PRIMARY_PHONE_PURPOSE CUSTOMER_PRIM_PHONE_PURPOSE,
920 cust_party.PRIMARY_PHONE_LINE_TYPE CUSTOMER_PRIM_PHONE_TYPE,
921 cust_party.PRIMARY_PHONE_COUNTRY_CODE CUSTOMER_PRIM_PH_COUNTRY_CODE,
922 cust_party.PRIMARY_PHONE_AREA_CODE CUSTOMER_PRIM_PH_AREA_CODE,
923 cust_party.PRIMARY_PHONE_NUMBER CUSTOMER_PRIM_PHONE_NUMBER,
924 cust_party.PRIMARY_PHONE_EXTENSION CUSTOMER_PRIM_PHONE_EXTN,
925 cust_party.HOME_COUNTRY CUSTOMER_HOME_COUNTRY,
926
927 -- cust_party.status CUSTOMER_STATUS,
928 ar_lookup_status.meaning CUSTOMER_STATUS,
929
930 cust_accounts.PARTY_ID CUSTOMER_PARTY_ID ,
931 cust_accounts.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
932 cust_accounts.ATTRIBUTE_CATEGORY CUSTOMER_ACC_DFF_CATEGORY,
933 cust_accounts.ATTRIBUTE1 CUSTOMER_ACC_DFF1 ,
934 cust_accounts.ATTRIBUTE2 CUSTOMER_ACC_DFF2 ,
935 cust_accounts.ATTRIBUTE3 CUSTOMER_ACC_DFF3,
936 cust_accounts.ATTRIBUTE4 CUSTOMER_ACC_DFF4,
937 cust_accounts.ATTRIBUTE5 CUSTOMER_ACC_DFF5,
938 cust_accounts.ATTRIBUTE6 CUSTOMER_ACC_DFF6 ,
939 cust_accounts.ATTRIBUTE7 CUSTOMER_ACC_DFF7,
940 cust_accounts.ATTRIBUTE8 CUSTOMER_ACC_DFF8,
941 cust_accounts.ATTRIBUTE9 CUSTOMER_ACC_DFF9,
942 cust_accounts.ATTRIBUTE10 CUSTOMER_ACC_DFF10,
943 cust_accounts.ATTRIBUTE11 CUSTOMER_ACC_DFF11,
944 cust_accounts.ATTRIBUTE12 CUSTOMER_ACC_DFF12,
945 cust_accounts.ATTRIBUTE13 CUSTOMER_ACC_DFF13,
946 cust_accounts.ATTRIBUTE14 CUSTOMER_ACC_DFF14,
947 cust_accounts.ATTRIBUTE15 CUSTOMER_ACC_DFF15,
948 cust_accounts.ATTRIBUTE16 CUSTOMER_ACC_DFF16,
949 cust_accounts.ATTRIBUTE17 CUSTOMER_ACC_DFF17,
950 cust_accounts.ATTRIBUTE18 CUSTOMER_ACC_DFF18,
951 cust_accounts.ATTRIBUTE19 CUSTOMER_ACC_DFF19,
952 cust_accounts.ATTRIBUTE20 CUSTOMER_ACC_DFF20,
953 ar_lookup_acc_status.meaning CUSTOMER_ACCOUNT_STATUS,
954 -- cust_accounts.STATUS CUSTOMER_ACCOUNT_STATUS,
955 -- cust_accounts.CUSTOMER_TYPE CUSTOMER_TYPE ,
956 ar_lookup_cust_type.meaning CUSTOMER_TYPE ,
957 cust_accounts.SALES_CHANNEL_CODE CUSTOMER_SALES_CHANNEL,
958 cust_accounts.ACCOUNT_ESTABLISHED_DATE CUSTOMER_ACC_ESTAB_DATE,
959 cust_accounts.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
960 cust_accounts.COMMENTS CUSTOMER_ACCOUNT_COMMENTS,
961 cust_accounts.STATUS_UPDATE_DATE CUSTOMER_LAST_STATUS_UPD_DT,
962
963 cust_party_site.PARTY_SITE_ID CUSTOMER_PARTY_SITE_ID,
964 cust_party_site.PARTY_SITE_NUMBER CUSTOMER_PARTY_SITE_NUMBER,
965 cust_party_site.PARTY_SITE_NAME CUSTOMER_PARTY_SITE_NAME,
966 cust_party_site.LOCATION_ID CUSTOMER_PARTY_SITE_LOC_ID,
967 cust_party_site.IDENTIFYING_ADDRESS_FLAG CUSTOMER_IDENTIFYING_ADR_FLAG,
968 -- cust_party_site.STATUS CUSTOMER_PARTY_SITE_STATUS,
969 ar_lookup_cust_site.meaning CUSTOMER_PARTY_SITE_STATUS,
970 cust_party_site.ADDRESSEE CUSTOMER_PARTY_SITE_ADDRESSEE,
971 cust_party_site.GLOBAL_LOCATION_NUMBER CUSTOMER_SITE_GLOB_LOC_NUM,
972
973
974 --null CUSTOMER_PARTY_SITE_ID,
975 --null CUSTOMER_PARTY_SITE_NUMBER,
976 --null CUSTOMER_PARTY_SITE_NAME,
977 --null CUSTOMER_PARTY_SITE_LOC_ID,
978 -- null CUSTOMER_IDENTIFYING_ADR_FLAG,
979 --null CUSTOMER_PARTY_SITE_STATUS,
980 --null CUSTOMER_PARTY_SITE_ADDRESSEE,
981 --null CUSTOMER_SITE_GLOB_LOC_NUM,
982
983 cust_party_site_use.cpsu_PARTY_SITE_USE_ID CUSTOMER_PARTY_SITE_USE_ID,
984 cust_party_site_use.cpsu_SITE_USE_TYPE CUSTOMER_PARTY_SITE_USE_TYPE,
985 cust_party_site_use.cpsu_COMMENTS CUSTOMER_PARTY_SITE_COMMENTS,
986 --cust_party_site_use.cpsu_STATUS CUSTOMER_PARTY_SITE_USE_STATUS,
987 --null CUSTOMER_PARTY_SITE_USE_ID,
988 --null CUSTOMER_PARTY_SITE_USE_TYPE,
989 --null CUSTOMER_PARTY_SITE_COMMENTS,
990 ar_lookup_cust_site_use.meaning CUSTOMER_PARTY_SITE_USE_STATUS,
991 -- null CUSTOMER_PARTY_SITE_USE_STATUS,
992
993 --null CONTRACT_PAST_DUE_AMT_1_30,
994 --null CONTRACT_PAST_DUE_AMT_31_60,
995 --null CONTRACT_PAST_DUE_AMT_61_90,
996 --null CONTRACT_PAST_DUE_AMT_91_120,
997 -- null CONTRACT_PAST_DUE_AMT_120_Plus,
998
999 NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,1,30),0) CONTRACT_PAST_DUE_AMT_1_30,
1000 NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,31,60),0) CONTRACT_PAST_DUE_AMT_31_60,
1001 NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,61,90),0) CONTRACT_PAST_DUE_AMT_61_90,
1002 NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,91,120),0) CONTRACT_PAST_DUE_AMT_91_120,
1003 NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,120,12000),0) CONTRACT_PAST_DUE_AMT_120_Plus,
1004
1005
1006 null CONTRACT_TOT_PAST_DUE_AMT,
1007 -- null CONTRACT_TOT_BILLED_RECEIVABLE,
1008 (select SUM(NVL(txd.amount,0))
1009 from OKL_TXD_AR_LN_DTLS_B txd
1010 where txd.khr_id = khr.id ) CONTRACT_TOT_BILLED_RECEIVABLE,
1011
1012 /*
1013 -- for Net Investment
1014 DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
1015 'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
1016 0 ) CONTRACT_UNEARNED_INCOME,
1017 DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
1018 'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
1019 0) CONTRACT_RESIDUAL_AMOUNT,
1020 DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
1021 'LEASEST',NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
1022 0) CONTRACT_RENT_AMOUNT,
1023 DECODE(khr.deal_type, 'LEASEOP', NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0),
1024 0) CONTRACT_ACC_DEPRECIATION, --Duplicate of CONTRACT_ACCUMULATED_DEP
1025 */
1026 --sechawla : calculate CONTRACT_UNEARNED_INCOME, CONTRACT_RESIDUAL_AMOUNT, CONTRACT_RENT_AMOUNT, CONTRACT_ACC_DEPRECIATION for all types of leases
1027 NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0) CONTRACT_UNEARNED_INCOME,
1028 NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0) CONTRACT_RESIDUAL_AMOUNT,
1029 NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0) CONTRACT_RENT_AMOUNT,
1030 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0) CONTRACT_ACC_DEPRECIATION,
1031
1032
1033 -- OKL_ACCOUNTING_UTIL.get_curr_con_rate(chr.CURRENCY_CODE, ledger.CURRENCY_CODE, trunc(sysdate), khr.CURRENCY_CONVERSION_TYPE) currency_convertion_rate
1034
1035 NVL(OKL_FUNDING_PVT.get_amount_prefunded(chr.id),0) CONTRACT_AMOUNT_PREFUNDED,
1036 NVL(OKL_FUNDING_PVT.get_chr_funded_adjs(chr.id),0) CONTRACT_FUNDED_ADJS,
1037 NVL(OKL_FUNDING_PVT.get_amount_manu_disb(chr.id),0) CONTRACT_AMOUNT_MANU_DISB,
1038
1039 l_last_updated_by CREATED_BY,
1040 sysdate CREATION_DATE,
1041 l_last_updated_by LAST_UPDATED_BY,
1042 sysdate LAST_UPDATE_DATE,
1043 l_last_update_login LAST_UPDATE_LOGIN,
1044 l_program_app_id PROGRAM_APPLICATION_ID,
1045 l_program_id PROGRAM_ID,
1046 l_program_login_id PROGRAM_LOGIN_ID,
1047 sysdate PROGRAM_UPDATE_DATE
1048
1049 FROM
1050 OKC_K_HEADERS_ALL_B chr,
1051 OKL_K_HEADERS khr,
1052 okl_parallel_processes opp,
1053 HR_OPERATING_UNITS hr_org,
1054 OKC_STATUSES_B status,
1055 OKC_STATUSES_TL status_tl,
1056 OKC_K_PARTY_ROLES_B cust_party_roles,
1057 fnd_lookup_values_vl lookup_book_class,
1058 HZ_PARTIES cust_party,
1059 HZ_CUST_ACCOUNTS cust_accounts,
1060 OKL_PRODUCTS pdt,
1061 OKL_PRODUCTS rpt_pdt,
1062 OKC_K_HEADERS_ALL_B vendor_chr,
1063
1064 okc_rule_groups_b rgd,
1065 okc_k_party_roles_v cpl,
1066 po_vendors vendor,
1067 OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
1068 GL_LEDGERS ledger,
1069 okc_contacts sales_rep_contact,
1070 okx_salesreps_v sales_rep,
1071 xle_entity_profiles legal_entity,
1072
1073 hz_party_sites cust_party_site,
1074 --hz_party_site_uses cust_party_site_use,
1075 --hz_cust_site_uses_all cust_cust_site_use,
1076 hz_cust_acct_sites_all cust_cust_acct_site,
1077 -- hz_party_site_uses cust_party_site_use,
1078 (select cpsu_party_site_use_id,
1079 cpsu_party_site_id,
1080 cpsu_SITE_USE_TYPE,
1081 cpsu_status,
1082 cpsu_comments
1083 from (
1084 select
1085 cust_party_site_use_n.party_site_use_id cpsu_party_site_use_id, --sechawla changed party_site_id to party_site_use_id
1086 cust_party_site_use_n.party_site_id cpsu_party_site_id,
1087 cust_party_site_use_n.SITE_USE_TYPE cpsu_site_use_type,
1088 cust_party_site_use_n.status cpsu_status,
1089 cust_party_site_use_n.comments cpsu_comments,
1090 row_number() over ( partition by cust_party_site_use_n.party_site_id
1091 order by NVL(cust_party_site_use_n.status,'A') ASC) cpsu_stat_priority
1092 from hz_party_site_uses cust_party_site_use_n
1093 where cust_party_site_use_n.SITE_USE_TYPE = 'BILL_TO'
1094 ) where cpsu_stat_priority = 1) cust_party_site_use,
1095
1096 hz_cust_site_uses_all cust_cust_site_use,
1097
1098 ar_lookups ar_lookup_status,
1099 ar_lookups ar_lookup_acc_status,
1100 ar_lookups ar_lookup_cust_type,
1101 ar_lookups ar_lookup_cust_site,
1102 ar_lookups ar_lookup_cust_site_use
1103
1104 WHERE chr.id = khr.id
1105 AND chr.SCS_CODE = 'LEASE'
1106 AND opp.object_type = 'CONTRACT_FIN_EXTRACT'
1107 AND opp.object_value = chr.contract_number
1108 AND opp.assigned_process = p_assigned_process -- assigned_process is unique for each run
1109 AND opp.khr_id = chr.id
1110 AND hr_org.organization_id = chr.authoring_org_id
1111 AND status.code = chr.sts_code
1112 AND status_tl.code = status.code
1113 AND status_tl.language = USERENV('LANG')
1114 AND cust_party.party_type in ( 'PERSON','ORGANIZATION')
1115 AND cust_party.party_id = cust_party_roles.object1_id1
1116 AND cust_party_roles.object1_id2 = '#'
1117 AND cust_party_roles.jtot_object1_code = 'OKX_PARTY'
1118 AND cust_party_roles.rle_code = 'LESSEE'
1119 AND cust_party_roles.chr_id = chr.id
1120 AND cust_party_roles.dnz_chr_id = chr.id
1121 AND cust_accounts.cust_account_id = chr.cust_acct_id
1122 AND cust_accounts.party_id = cust_party.party_id
1123
1124 AND khr.pdt_id = pdt.id
1125
1126 AND pdt.REPORTING_PDT_ID = rpt_pdt.id(+)
1127 AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
1128 AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
1129
1130 AND vendor_chr.id (+) = khr.khr_id
1131 AND vendor_chr.scs_code (+) = 'PROGRAM'
1132
1133 AND rgd.chr_id(+)= vendor_chr.id
1134 AND rgd.dnz_chr_id(+) = vendor_chr.id
1135 AND rgd.cle_id IS NULL
1136 AND rgd.rgd_code(+) = 'LAVENB'
1137 AND vendor_chr.id = cpl.chr_id (+)
1138 AND vendor_chr.id = cpl.dnz_chr_id(+)
1139
1140 AND cpl.rle_code (+) = 'OKL_VENDOR'
1141 and cpl.object1_id1 = vendor.vendor_id (+)
1142
1143 AND acct_sys_opts.org_id = chr.authoring_org_id
1144 AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
1145
1146 AND sales_rep_contact.DNZ_CHR_ID (+) = khr.id
1147 AND sales_rep.id1 (+) = sales_rep_contact.object1_id1
1148 AND sales_rep.id2 (+) = sales_rep_contact.object1_id2
1149 and sales_rep_contact.cro_code (+) = 'SALESPERSON'
1150
1151
1152 AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
1153
1154 and cust_party.party_id = cust_party_site.party_id
1155 AND cust_cust_site_use.site_use_id = chr.bill_to_site_use_id
1156 AND cust_cust_site_use.cust_acct_site_id = cust_cust_acct_site.cust_acct_site_id
1157 -- and cust_cust_site_use.SITE_USE_CODE = 'BILL_TO'
1158 and cust_cust_acct_site.party_site_id = cust_party_site.party_site_id
1159 and cust_party_site.party_site_id = cust_party_site_use.cpsu_party_site_id
1160 -- and cust_party_site_use.SITE_USE_TYPE = 'BILL_TO'
1161
1162 AND ar_lookup_status.lookup_type(+) = 'HZ_CPUI_REGISTRY_STATUS'
1163 AND ar_lookup_status.lookup_code(+) = cust_party.status
1164 AND ar_lookup_acc_status.lookup_type(+) = 'CODE_STATUS'
1165 AND ar_lookup_acc_status.lookup_code(+) = cust_accounts.STATUS
1166 AND ar_lookup_cust_type.lookup_type(+) = 'CUSTOMER_TYPE'
1167 AND ar_lookup_cust_type.lookup_code(+) = cust_accounts.CUSTOMER_TYPE
1168 AND ar_lookup_cust_site.lookup_type(+) = 'REGISTRY_STATUS'
1169 AND ar_lookup_cust_site.lookup_code(+) = cust_party_site.STATUS
1170 AND ar_lookup_cust_site_use.lookup_type(+) = 'REGISTRY_STATUS'
1171 AND ar_lookup_cust_site_use.lookup_code(+) = cust_party_site_use.cpsu_STATUS
1172
1173 -- parameters section begin
1174 /*
1175 --sechawla : don't need the following as these paramater matching has been already done in
1176 --Master Program - OKL_CNTRCT_FIN_EXT_MASTER_PVT
1177 AND chr.AUTHORING_ORG_ID = P_OPERATING_UNIT
1178 AND chr.START_DATE >= P_START_DATE_FROM
1179 AND chr.START_DATE <= P_START_DATE_TO
1180 AND khr.DEAL_TYPE like nvl(P_BOOK_CLASS, khr.DEAL_TYPE)
1181 AND pdt.ID like nvl(P_LEASE_PRODUCT, pdt.ID)
1182 AND chr.sts_code like nvl(P_CONTRACT_STATUS, chr.sts_code)
1183 AND ( (lp_k_end_date_from IS NULL) OR (chr.END_DATE IS NOT NULL AND chr.END_DATE >= lp_k_end_date_from) )
1184 AND ( (lp_k_end_date_to IS NULL ) OR (chr.END_DATE IS NOT NULL AND chr.END_DATE <= lp_k_end_date_to) )
1185 */
1186
1187 --there is no ID defined in the value set OKS_CUSTOMER_NUMBER. value is 'party_number'.
1188 AND cust_party.PARTY_NUMBER like nvl(P_CUSTOMER_NUMBER, cust_party.PARTY_NUMBER)
1189
1190 --sechawla : P_CUSTOMER_NAME is hz_parties.party_id. value set: OKL_CUSTOMERS
1191 AND cust_party.party_id = nvl(P_CUSTOMER_NAME, cust_party.party_id)
1192 AND nvl(cust_party.SIC_CODE,'XXX') like nvl(P_SIC_CODE, nvl(cust_party.SIC_CODE,'XXX'))
1193
1194 --sechawla : p_vendor_number -> value set 'OKL_VENDORS' -> returns PO_VENDORS.VENDOR_ID
1195 --Need nvl, as every contract may not have vendor_id (program vendor)
1196 AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NUMBER,nvl(vendor.vendor_id,-9999))
1197
1198 --sechawla : P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
1199 AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NAME,nvl(vendor.vendor_id,-9999))
1200
1201 -- P_SALES_CHANNEL -> value set QP_SALES_CHANNEL_CODE -> returns oe_lookups.lookup_code
1202 AND nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX') like nvl(P_SALES_CHANNEL, nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX'))
1203
1204 -- AND CONTRACT_ACCRUAL_STATUS like nvl(P_GEN_ACCRUAL, CONTRACT_ACCRUAL_STATUS)
1205 AND OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id) like nvl(P_GEN_ACCRUAL, OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id))
1206
1207 AND ( (P_TERMINATE_DATE_FROM IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED >= P_TERMINATE_DATE_FROM) )
1208 AND ( (P_TERMINATE_DATE_TO IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED <= P_TERMINATE_DATE_TO) )
1209
1210 -- parameters section end
1211 );
1212
1213 ELSE -- IF P_AR_INFO_YN like 'NO'
1214
1215
1216 INSERT INTO OKL_CNTRCT_FIN_EXTRACT_T
1217 (
1218 REQUEST_ID,
1219 CONTRACT_DOCUMENT_ID,
1220 CONTRACT_NUMBER,
1221 CONTRACT_OPERATING_UNIT_ID,
1222 CONTRACT_INVENTORY_ORG_ID,
1223 CONTRACT_STATUS_CODE,
1224 CONTRACT_CURRENCY,
1225 CONTRACT_TEMPLATE_INDICATOR,
1226 CONTRACT_CUST_PURCH_ORDER_NUM,
1227 CONTRACT_TEMPLATE_NAME,
1228 CONTRACT_APPROVED_DATE,
1229 CONTRACT_CANCELED_TIMESTAMP,
1230 CONTRACT_SIGNED_DATE,
1231 CONTRACT_TERMINATION_DATE,
1232 CONTRACT_START_DATE,
1233 CONTRACT_END_DATE,
1234 CUSTOMER_ACCOUNT_ID,
1235 CUST_BILL_TO_SITE_USE_ID,
1236 CUST_PAYMENT_TERM_ID,
1237 CONTRACT_ORIG_SYSTEM_SOURCE,
1238 CONTRACT_ID,
1239 CONTRACT_SALES_REGION_ID,
1240 VENDOR_PROGRAM_ID,
1241 CONTRACT_FINANCIAL_PRODUCT_ID,
1242 CONTRACT_DATE_DEAL_ACTIVATED,
1243 CONTRACT_DATE_DEAL_TRANSFERD,
1244 CONTRACT_TERM_DURATION,
1245 CONTRACT_HDR_DFF_CATEGORY,
1246 CONTRACT_HDR_DFF1,
1247 CONTRACT_HDR_DFF2,
1248 CONTRACT_HDR_DFF3,
1249 CONTRACT_HDR_DFF4,
1250 CONTRACT_HDR_DFF5,
1251 CONTRACT_HDR_DFF6,
1252 CONTRACT_HDR_DFF7,
1253 CONTRACT_HDR_DFF8,
1254 CONTRACT_HDR_DFF9,
1255 CONTRACT_HDR_DFF10,
1256 CONTRACT_HDR_DFF11,
1257 CONTRACT_HDR_DFF12,
1258 CONTRACT_HDR_DFF13,
1259 CONTRACT_HDR_DFF14,
1260 CONTRACT_HDR_DFF15,
1261 CONTRACT_ACCRUAL_STATUS,
1262 CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
1263 CONTRACT_CREDIT_ACT_YN,
1264 CONTRACT_CONVERTED_ACCOUNT_YN,
1265 CONTRACT_AFTER_TAX_YIELD,
1266 CONTRACT_IMPL_INTEREST_RATE,
1267 CONTRACT_LAST_INT_CALC_DATE,
1268 CONTRACT_BOOK_CLASS_CODE,
1269 CONTRACT_BOOK_CLASSIFICATION,
1270 CONTRACT_PRE_TAX_IRR,
1271 CONTRACT_AFTER_TAX_IRR,
1272 CONTRACT_EXP_DELIVERY_DATE,
1273 CONTRACT_ACCEPTANCE_DATE,
1274 CONTRACT_PREFUND_ELIG_YN,
1275 CONTRACT_REVOL_CREDIT_ELIG_YN,
1276 CONTRACT_CURRENCY_CONV_TYPE,
1277 CONTRACT_CURRENCY_CONV_RATE,
1278 CONTRACT_CURRENCY_CONV_DATE,
1279 CONTRACT_MULTI_GAAP_ELIG_YN,
1280 CONTRACT_INVESTOR_ASSIGN_YN,
1281 CONTRACT_SECURITIZATION_TYPE,
1282 CONTRACT_SUB_AFTER_TAX_YIELD,
1283 CONTRACT_SUB_IMPL_INT_RATE,
1284 CONTRACT_SUB_PRE_TAX_IRR,
1285 CONTRACT_SUB_AFTER_TAX_IRR,
1286 CONTRACT_CRDTLINE_TRNSFR_AMT,
1287 CONTRACT_CRDTLINE_NETRNSFR_AMT,
1288 CONTRACT_CRDTLINE_LIMIT,
1289 CONTRACT_CRDTLINE_FUNDING_AMT,
1290 CONTRACT_TEMPL_TYPE_CODE,
1291 CONTRACT_TRADEIN_DESCRIPTION,
1292 CONTRACT_TRADEIN_DATE,
1293 CONTRACT_LEGAL_ENTITY_ID,
1294 CONTRACT_FIRST_ACTIVITY_DATE,
1295 --formulas
1296 CONTRACT_ACCUMULATED_DEP,
1297 CONTRACT_CAPITALIZED_REDUCTION,
1298 CONTRACT_CAPITALIZED_FEE,
1299 CONTRACT_CAPITALIZED_INTEREST,
1300 CONTRACT_DISCOUNT,
1301 CONTRACT_ASSET_COST,
1302 CONTRACT_FINANCED_FEE,
1303 CONTRACT_NEXT_PAYMENT_AMT,
1304 CONTRACT_TOTAL_ACCRUED_INT,
1305 CONTRACT_TOTAL_ACTUAL_INT,
1306 CONTRACT_SUBSIDY_AMOUNT,
1307 CONTRACT_UNACCRUED_SUBSIDY,
1308 CONTRACT_UNBILLED_DUE_AMOUNT,
1309 CONTRACT_UNBILLED_RECEIVABLES,
1310 CONTRACT_OEC,
1311 CONTRACT_ACTUAL_PRINC_BAL,
1312 ROLLOVER_FEE_AMOUNT,
1313 CONTRACT_TRADEIN_AMOUNT,
1314 TOTAL_AMOUNT_PAID_TO_DEALER,
1315 TOTAL_DEBITS_FOR_PREFUNDING,
1316 CONTRACT_PREFUNDED_AMOUNT,
1317 CONTRACT_BILLED_RENTS,
1318 CONTRACT_BILLED_RENTS_FUNC,
1319 CONTRACT_BILLED_NON_RENTS,
1320 CONTRACT_BILLED_NON_RENTS_FUNC,
1321 SUPPLIER_RETENTION_FUNDING_AMT,
1322 ASSETS_FUNDED_AMOUNT,
1323 EXPENSE_FUNDED_AMOUNT,
1324 CONTRACT_IDC_AMOUNT,
1325 CONTRACT_SCHEDULED_PRINC_BAL,
1326 CONTRACT_INTEREST_RATE,
1327 CONTRACT_DAYS_TO_ACCRUE,
1328 CONTRACT_DAYS_IN_YEAR,
1329 CONTRACT_CAP_AMOUNT,
1330 CONTRACT_FINANCED_AMOUNT,
1331 CONTRACT_VAR_INCOME_ACCRUAL,
1332 TOTAL_PREFUNDING_AMOUNT,
1333 OPERATING_UNIT,
1334 OPERATING_UNIT_SHORT_CODE,
1335 CONTRACT_STATUS_TYPE_CODE,
1336 CONTRACT_STATUS,
1337 CONTRACT_FEE_INCOME,
1338 ACC_ENGINE_TEMPL_SET_ID,
1339 CONTRACT_FIN_PROD_TEMPLATE_ID,
1340 CONTRACT_FINANCIAL_PRODUCT,
1341 CONTRACT_FIN_PROD_DESC,
1342 REPORTING_PRODUCT_ID,
1343 CONTRACT_FIN_PROD_STATUS_CODE,
1344 CONTRACT_FIN_PROD_EFF_FROM_DT,
1345 CONTRACT_FIN_PROD_EFF_TO_DT,
1346 REPORTING_PRODUCT,
1347 VENDOR_PROGRAM_NAME,
1348 VENDOR_NUMBER,
1349 VENDOR_NAME,
1350 VENDOR_TYPE_CODE,
1351 VENDOR_BILL_TO_SITE_USE_ID,
1352 VENDOR_PARTY_ROLE,
1353 TOTAL_UPFRONT_TAX_ON_ASSETS,
1354 TOTAL_NUMBER_OF_ASSETS,
1355 FUNCTIONAL_CURRENCY,
1356 LEDGER_ID,
1357 LEDGER,
1358 CONTRACT_EARLIEST_BILL_DATE,
1359 CONTRACT_SALES_REP,
1360 LEGAL_ENTITY,
1361 CUSTOMER_PARTY_NUMBER,
1362 CUSTOMER_PARTY_NAME,
1363 CUSTOMER_PARTY_TYPE,
1364 CUSTOMER_SIC_CODE,
1365 CUSTOMER_HQ_BRANCH_IND,
1366 CUSTOMER_TAX_REFERENCE,
1367 CUSTOMER_DUNS_NUMBER,
1368 CUSTOMER_ALIAS,
1369 CUSTOMER_COUNTRY,
1370 CUSTOMER_ADDRESS1,
1371 CUSTOMER_ADDRESS2,
1372 CUSTOMER_ADDRESS3,
1373 CUSTOMER_ADDRESS4,
1374 CUSTOMER_CITY,
1375 CUSTOMER_POSTAL_CODE,
1376 CUSTOMER_STATE,
1377 CUSTOMER_PROVINCE,
1378 CUSTOMER_COUNTY,
1379 CUSTOMER_SIC_CODE_TYPE,
1380 CUSTOMER_URL,
1381 FINANCIAL_INFO_FISCAL_YEAR,
1382 CUSTOMER_FISCAL_YEAREND_MONTH,
1383 TOTAL_NUM_OF_EMPLOYEES,
1384 CURR_FISC_YEAR_POTENTIAL_REV,
1385 NEXT_FISC_YEAR_POTENTIAL_REV,
1386 CUSTOMER_YEAR_ESTABLISHED,
1387 GEN_SERV_ADMIN_INDICATOR,
1388 CUSTOMER_ORG_NAME_PHONETIC,
1389 CUSTOMER_COMPETITOR_YN,
1390 CUSTOMER_ALIAS2,
1391 CUSTOMER_ALIAS3,
1392 CUSTOMER_ALIAS4,
1393 CUSTOMER_ALIAS5,
1394 CUSTOMER_PRIM_PHONE_PURPOSE,
1395 CUSTOMER_PRIM_PHONE_TYPE,
1396 CUSTOMER_PRIM_PH_COUNTRY_CODE,
1397 CUSTOMER_PRIM_PH_AREA_CODE,
1398 CUSTOMER_PRIM_PHONE_NUMBER,
1399 CUSTOMER_PRIM_PHONE_EXTN,
1400 CUSTOMER_HOME_COUNTRY,
1401 CUSTOMER_STATUS,
1402 CUSTOMER_PARTY_ID,
1403 CUSTOMER_ACCOUNT_NUMBER,
1404 CUSTOMER_ACC_DFF_CATEGORY,
1405 CUSTOMER_ACC_DFF1,
1406 CUSTOMER_ACC_DFF2,
1407 CUSTOMER_ACC_DFF3,
1408 CUSTOMER_ACC_DFF4,
1409 CUSTOMER_ACC_DFF5,
1410 CUSTOMER_ACC_DFF6,
1411 CUSTOMER_ACC_DFF7,
1412 CUSTOMER_ACC_DFF8,
1413 CUSTOMER_ACC_DFF9,
1414 CUSTOMER_ACC_DFF10,
1415 CUSTOMER_ACC_DFF11,
1416 CUSTOMER_ACC_DFF12,
1417 CUSTOMER_ACC_DFF13,
1418 CUSTOMER_ACC_DFF14,
1419 CUSTOMER_ACC_DFF15,
1420 CUSTOMER_ACC_DFF16,
1421 CUSTOMER_ACC_DFF17,
1422 CUSTOMER_ACC_DFF18,
1423 CUSTOMER_ACC_DFF19,
1424 CUSTOMER_ACC_DFF20,
1425 CUSTOMER_ACCOUNT_STATUS,
1426 CUSTOMER_TYPE,
1427 CUSTOMER_SALES_CHANNEL,
1428 CUSTOMER_ACC_ESTAB_DATE,
1429 CUSTOMER_ACCOUNT_NAME,
1430 CUSTOMER_ACCOUNT_COMMENTS,
1431 CUSTOMER_LAST_STATUS_UPD_DT,
1432 CUSTOMER_PARTY_SITE_ID,
1433 CUSTOMER_PARTY_SITE_NUMBER,
1434 CUSTOMER_PARTY_SITE_NAME,
1435 CUSTOMER_PARTY_SITE_LOC_ID,
1436 CUSTOMER_IDENTIFYING_ADR_FLAG,
1437 CUSTOMER_PARTY_SITE_STATUS,
1438 CUSTOMER_PARTY_SITE_ADDRESSEE,
1439 CUSTOMER_SITE_GLOB_LOC_NUM,
1440 CUSTOMER_PARTY_SITE_USE_ID,
1441 CUSTOMER_PARTY_SITE_USE_TYPE,
1442 CUSTOMER_PARTY_SITE_COMMENTS,
1443 CUSTOMER_PARTY_SITE_USE_STATUS,
1444
1445 CONTRACT_PAST_DUE_AMT_1_30,
1446 CONTRACT_PAST_DUE_AMT_31_60,
1447 CONTRACT_PAST_DUE_AMT_61_90,
1448 CONTRACT_PAST_DUE_AMT_91_120,
1449 CONTRACT_PAST_DUE_AMT_120_Plus,
1450 CONTRACT_TOT_PAST_DUE_AMT,
1451 CONTRACT_TOT_BILLED_RECEIVABLE,
1452
1453 CONTRACT_RESIDUAL_AMOUNT,
1454 CONTRACT_RENT_AMOUNT,
1455 CONTRACT_ACC_DEPRECIATION,
1456 NET_INVESTMENT,
1457 NET_INVESTMENT_FUNCTIONAL,
1458
1459 CONTRACT_AMOUNT_PREFUNDED,
1460 CONTRACT_TOTAL_MISC_FUND,
1461
1462 CREATED_BY,
1463 CREATION_DATE,
1464 LAST_UPDATED_BY,
1465 LAST_UPDATE_DATE,
1466 LAST_UPDATE_LOGIN,
1467 PROGRAM_APPLICATION_ID,
1468 PROGRAM_ID,
1469 PROGRAM_LOGIN_ID,
1470 PROGRAM_UPDATE_DATE
1471 )
1472
1473 select
1474 REQUEST_ID,
1475 CONTRACT_DOCUMENT_ID,
1476 CONTRACT_NUMBER,
1477 CONTRACT_OPERATING_UNIT_ID,
1478 CONTRACT_INVENTORY_ORG_ID,
1479 CONTRACT_STATUS_CODE,
1480 CONTRACT_CURRENCY,
1481 CONTRACT_TEMPLATE_INDICATOR,
1482 CONTRACT_CUST_PURCH_ORDER_NUM,
1483 CONTRACT_TEMPLATE_NAME,
1484 CONTRACT_APPROVED_DATE,
1485 CONTRACT_CANCELED_TIMESTAMP,
1486 CONTRACT_SIGNED_DATE,
1487 CONTRACT_TERMINATION_DATE,
1488 CONTRACT_START_DATE,
1489 CONTRACT_END_DATE,
1490 CUSTOMER_ACCOUNT_ID,
1491 CUST_BILL_TO_SITE_USE_ID,
1492 CUST_PAYMENT_TERM_ID,
1493 CONTRACT_ORIG_SYSTEM_SOURCE,
1494 CONTRACT_ID,
1495 CONTRACT_SALES_REGION_ID,
1496 VENDOR_PROGRAM_ID,
1497 CONTRACT_FINANCIAL_PRODUCT_ID,
1498 CONTRACT_DATE_DEAL_ACTIVATED,
1499 CONTRACT_DATE_DEAL_TRANSFERD,
1500 CONTRACT_TERM_DURATION,
1501 CONTRACT_HDR_DFF_CATEGORY,
1502 CONTRACT_HDR_DFF1,
1503 CONTRACT_HDR_DFF2,
1504 CONTRACT_HDR_DFF3,
1505 CONTRACT_HDR_DFF4,
1506 CONTRACT_HDR_DFF5,
1507 CONTRACT_HDR_DFF6,
1508 CONTRACT_HDR_DFF7,
1509 CONTRACT_HDR_DFF8,
1510 CONTRACT_HDR_DFF9,
1511 CONTRACT_HDR_DFF10,
1512 CONTRACT_HDR_DFF11,
1513 CONTRACT_HDR_DFF12,
1514 CONTRACT_HDR_DFF13,
1515 CONTRACT_HDR_DFF14,
1516 CONTRACT_HDR_DFF15,
1517 CONTRACT_ACCRUAL_STATUS,
1518 CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
1519 CONTRACT_CREDIT_ACT_YN,
1520 CONTRACT_CONVERTED_ACCOUNT_YN,
1521 CONTRACT_AFTER_TAX_YIELD,
1522 CONTRACT_IMPL_INTEREST_RATE,
1523 CONTRACT_LAST_INT_CALC_DATE,
1524 CONTRACT_BOOK_CLASS_CODE,
1525 CONTRACT_BOOK_CLASSIFICATION,
1526 CONTRACT_PRE_TAX_IRR,
1527 CONTRACT_AFTER_TAX_IRR,
1528 CONTRACT_EXP_DELIVERY_DATE,
1529 CONTRACT_ACCEPTANCE_DATE,
1530 CONTRACT_PREFUND_ELIG_YN,
1531 CONTRACT_REVOL_CREDIT_ELIG_YN,
1532 CONTRACT_CURRENCY_CONV_TYPE,
1533 CONTRACT_CURRENCY_CONV_RATE,
1534 CONTRACT_CURRENCY_CONV_DATE,
1535 CONTRACT_MULTI_GAAP_ELIG_YN,
1536 CONTRACT_INVESTOR_ASSIGN_YN,
1537 CONTRACT_SECURITIZATION_TYPE,
1538 CONTRACT_SUB_AFTER_TAX_YIELD,
1539 CONTRACT_SUB_IMPL_INT_RATE,
1540 CONTRACT_SUB_PRE_TAX_IRR,
1541 CONTRACT_SUB_AFTER_TAX_IRR,
1542 CONTRACT_CRDTLINE_TRNSFR_AMT,
1543 CONTRACT_CRDTLINE_NETRNSFR_AMT,
1544 CONTRACT_CRDTLINE_LIMIT,
1545 CONTRACT_CRDTLINE_FUNDING_AMT,
1546 CONTRACT_TEMPL_TYPE_CODE,
1547 CONTRACT_TRADEIN_DESCRIPTION,
1548 CONTRACT_TRADEIN_DATE,
1549 CONTRACT_LEGAL_ENTITY_ID,
1550 CONTRACT_FIRST_ACTIVITY_DATE,
1551 --formulas
1552 CONTRACT_ACCUMULATED_DEP,
1553 CONTRACT_CAPITALIZED_REDUCTION,
1554 CONTRACT_CAPITALIZED_FEE,
1555 CONTRACT_CAPITALIZED_INTEREST,
1556 CONTRACT_DISCOUNT,
1557 CONTRACT_ASSET_COST,
1558 CONTRACT_FINANCED_FEE,
1559 CONTRACT_NEXT_PAYMENT_AMT,
1560 CONTRACT_TOTAL_ACCRUED_INT,
1561 CONTRACT_TOTAL_ACTUAL_INT,
1562 CONTRACT_SUBSIDY_AMOUNT,
1563 CONTRACT_UNACCRUED_SUBSIDY,
1564 CONTRACT_UNBILLED_DUE_AMOUNT,
1565 CONTRACT_UNBILLED_RECEIVABLES,
1566 CONTRACT_OEC,
1567 CONTRACT_ACTUAL_PRINC_BAL,
1568 ROLLOVER_FEE_AMOUNT,
1569 CONTRACT_TRADEIN_AMOUNT,
1570 TOTAL_AMOUNT_PAID_TO_DEALER,
1571 TOTAL_DEBITS_FOR_PREFUNDING,
1572 CONTRACT_PREFUNDED_AMOUNT,
1573 CONTRACT_BILLED_RENTS,
1574 (CONTRACT_BILLED_RENTS * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_RENTS_FUNC,
1575 (CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) CONTRACT_BILLED_NON_RENTS,
1576 ((CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_NON_RENTS_FUNC,
1577 SUPPLIER_RETENTION_FUNDING_AMT,
1578 ASSETS_FUNDED_AMOUNT,
1579 EXPENSE_FUNDED_AMOUNT,
1580 CONTRACT_IDC_AMOUNT,
1581 CONTRACT_SCHEDULED_PRINC_BAL,
1582 CONTRACT_INTEREST_RATE,
1583 CONTRACT_DAYS_TO_ACCRUE,
1584 CONTRACT_DAYS_IN_YEAR,
1585 (CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+CONTRACT_CAPITALIZED_INTEREST) CONTRACT_CAP_AMOUNT,
1586 (CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+ CONTRACT_CAPITALIZED_INTEREST+CONTRACT_FINANCED_FEE) CONTRACT_FINANCED_AMOUNT,
1587 decode(CONTRACT_DAYS_IN_YEAR,0,0,CONTRACT_SCHEDULED_PRINC_BAL * CONTRACT_INTEREST_RATE * CONTRACT_DAYS_TO_ACCRUE/CONTRACT_DAYS_IN_YEAR) CONTRACT_VAR_INCOME_ACCRUAL,
1588 (CONTRACT_PREFUNDED_AMOUNT + TOTAL_DEBITS_FOR_PREFUNDING) TOTAL_PREFUNDING_AMOUNT,
1589 OPERATING_UNIT,
1590 OPERATING_UNIT_SHORT_CODE,
1591 CONTRACT_STATUS_TYPE_CODE,
1592 CONTRACT_STATUS,
1593 CONTRACT_FEE_INCOME,
1594 ACC_ENGINE_TEMPL_SET_ID ,
1595 CONTRACT_FIN_PROD_TEMPLATE_ID ,
1596 CONTRACT_FINANCIAL_PRODUCT,
1597 CONTRACT_FIN_PROD_DESC ,
1598 REPORTING_PRODUCT_ID ,
1599 CONTRACT_FIN_PROD_STATUS_CODE ,
1600 CONTRACT_FIN_PROD_EFF_FROM_DT ,
1601 CONTRACT_FIN_PROD_EFF_TO_DT ,
1602 REPORTING_PRODUCT,
1603 VENDOR_PROGRAM_NAME,
1604 VENDOR_NUMBER,
1605 VENDOR_NAME,
1606 VENDOR_TYPE_CODE,
1607 VENDOR_BILL_TO_SITE_USE_ID,
1608 VENDOR_PARTY_ROLE,
1609 TOTAL_UPFRONT_TAX_ON_ASSETS,
1610 TOTAL_NUMBER_OF_ASSETS,
1611 FUNCTIONAL_CURRENCY,
1612 LEDGER_ID,
1613 LEDGER,
1614 CONTRACT_EARLIEST_BILL_DATE,
1615 CONTRACT_SALES_REP,
1616 LEGAL_ENTITY,
1617 CUSTOMER_PARTY_NUMBER,
1618 CUSTOMER_PARTY_NAME,
1619 CUSTOMER_PARTY_TYPE,
1620 CUSTOMER_SIC_CODE,
1621 CUSTOMER_HQ_BRANCH_IND,
1622 CUSTOMER_TAX_REFERENCE,
1623 CUSTOMER_DUNS_NUMBER,
1624 CUSTOMER_ALIAS,
1625 CUSTOMER_COUNTRY,
1626 CUSTOMER_ADDRESS1,
1627 CUSTOMER_ADDRESS2,
1628 CUSTOMER_ADDRESS3,
1629 CUSTOMER_ADDRESS4,
1630 CUSTOMER_CITY,
1631 CUSTOMER_POSTAL_CODE,
1632 CUSTOMER_STATE,
1633 CUSTOMER_PROVINCE,
1634 CUSTOMER_COUNTY,
1635 CUSTOMER_SIC_CODE_TYPE,
1636 CUSTOMER_URL,
1637 FINANCIAL_INFO_FISCAL_YEAR,
1638 CUSTOMER_FISCAL_YEAREND_MONTH,
1639 TOTAL_NUM_OF_EMPLOYEES,
1640 CURR_FISC_YEAR_POTENTIAL_REV,
1641 NEXT_FISC_YEAR_POTENTIAL_REV,
1642 CUSTOMER_YEAR_ESTABLISHED,
1643 GEN_SERV_ADMIN_INDICATOR,
1644 CUSTOMER_ORG_NAME_PHONETIC,
1645 CUSTOMER_COMPETITOR_YN,
1646 CUSTOMER_ALIAS2,
1647 CUSTOMER_ALIAS3,
1648 CUSTOMER_ALIAS4,
1649 CUSTOMER_ALIAS5,
1650 CUSTOMER_PRIM_PHONE_PURPOSE,
1651 CUSTOMER_PRIM_PHONE_TYPE,
1652 CUSTOMER_PRIM_PH_COUNTRY_CODE,
1653 CUSTOMER_PRIM_PH_AREA_CODE,
1654 CUSTOMER_PRIM_PHONE_NUMBER,
1655 CUSTOMER_PRIM_PHONE_EXTN,
1656 CUSTOMER_HOME_COUNTRY,
1657 CUSTOMER_STATUS,
1658 CUSTOMER_PARTY_ID,
1659 CUSTOMER_ACCOUNT_NUMBER,
1660 CUSTOMER_ACC_DFF_CATEGORY,
1661 CUSTOMER_ACC_DFF1,
1662 CUSTOMER_ACC_DFF2,
1663 CUSTOMER_ACC_DFF3,
1664 CUSTOMER_ACC_DFF4,
1665 CUSTOMER_ACC_DFF5,
1666 CUSTOMER_ACC_DFF6,
1667 CUSTOMER_ACC_DFF7,
1668 CUSTOMER_ACC_DFF8,
1669 CUSTOMER_ACC_DFF9,
1670 CUSTOMER_ACC_DFF10,
1671 CUSTOMER_ACC_DFF11,
1672 CUSTOMER_ACC_DFF12,
1673 CUSTOMER_ACC_DFF13,
1674 CUSTOMER_ACC_DFF14,
1675 CUSTOMER_ACC_DFF15,
1676 CUSTOMER_ACC_DFF16,
1677 CUSTOMER_ACC_DFF17,
1678 CUSTOMER_ACC_DFF18,
1679 CUSTOMER_ACC_DFF19,
1680 CUSTOMER_ACC_DFF20,
1681 CUSTOMER_ACCOUNT_STATUS,
1682 CUSTOMER_TYPE,
1683 CUSTOMER_SALES_CHANNEL,
1684 CUSTOMER_ACC_ESTAB_DATE,
1685 CUSTOMER_ACCOUNT_NAME,
1686 CUSTOMER_ACCOUNT_COMMENTS,
1687 CUSTOMER_LAST_STATUS_UPD_DT,
1688 CUSTOMER_PARTY_SITE_ID,
1689 CUSTOMER_PARTY_SITE_NUMBER,
1690 CUSTOMER_PARTY_SITE_NAME,
1691 CUSTOMER_PARTY_SITE_LOC_ID,
1692 CUSTOMER_IDENTIFYING_ADR_FLAG,
1693 CUSTOMER_PARTY_SITE_STATUS,
1694 CUSTOMER_PARTY_SITE_ADDRESSEE,
1695 CUSTOMER_SITE_GLOB_LOC_NUM,
1696 CUSTOMER_PARTY_SITE_USE_ID,
1697 CUSTOMER_PARTY_SITE_USE_TYPE,
1698 CUSTOMER_PARTY_SITE_COMMENTS,
1699 CUSTOMER_PARTY_SITE_USE_STATUS,
1700
1701 CONTRACT_PAST_DUE_AMT_1_30,
1702 CONTRACT_PAST_DUE_AMT_31_60,
1703 CONTRACT_PAST_DUE_AMT_61_90,
1704 CONTRACT_PAST_DUE_AMT_91_120,
1705 CONTRACT_PAST_DUE_AMT_120_Plus,
1706 (CONTRACT_PAST_DUE_AMT_1_30 + CONTRACT_PAST_DUE_AMT_31_60 +
1707 CONTRACT_PAST_DUE_AMT_61_90 + CONTRACT_PAST_DUE_AMT_91_120 +
1708 CONTRACT_PAST_DUE_AMT_120_Plus) CONTRACT_TOT_PAST_DUE_AMT,
1709 CONTRACT_TOT_BILLED_RECEIVABLE,
1710
1711 CONTRACT_RESIDUAL_AMOUNT,
1712 CONTRACT_RENT_AMOUNT,
1713 CONTRACT_ACC_DEPRECIATION,
1714 DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
1715 (DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
1716 'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
1717 'LOAN', CONTRACT_ACTUAL_PRINC_BAL,
1718 'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL,
1719 'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION + CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT),
1720 0))) NET_INVESTMENT,
1721
1722 DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
1723 (DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
1724 'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
1725 'LOAN', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
1726 'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
1727 'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION +
1728 CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
1729 0))) NET_INVESTMENT_FUNCTIONAL,
1730 (CONTRACT_AMOUNT_PREFUNDED + CONTRACT_FUNDED_ADJS ) CONTRACT_AMOUNT_PREFUNDED,
1731 CONTRACT_AMOUNT_MANU_DISB CONTRACT_TOTAL_MISC_FUND,
1732
1733 CREATED_BY,
1734 CREATION_DATE,
1735 LAST_UPDATED_BY,
1736 LAST_UPDATE_DATE,
1737 LAST_UPDATE_LOGIN,
1738 PROGRAM_APPLICATION_ID,
1739 PROGRAM_ID,
1740 PROGRAM_LOGIN_ID,
1741 PROGRAM_UPDATE_DATE
1742
1743 from (
1744
1745
1746 SELECT
1747 --l_request_id REQUEST_ID,
1748 l_parent_request_id REQUEST_ID,
1749 chr.DOCUMENT_ID CONTRACT_DOCUMENT_ID,
1750 chr.CONTRACT_NUMBER CONTRACT_NUMBER,
1751 chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
1752 chr.INV_ORGANIZATION_ID CONTRACT_INVENTORY_ORG_ID,
1753 chr.STS_CODE CONTRACT_STATUS_CODE,
1754 chr.CURRENCY_CODE CONTRACT_CURRENCY,
1755 chr.TEMPLATE_YN CONTRACT_TEMPLATE_INDICATOR,
1756 chr.CUST_PO_NUMBER CONTRACT_CUST_PURCH_ORDER_NUM,
1757 chr.TEMPLATE_USED CONTRACT_TEMPLATE_NAME,
1758 chr.DATE_APPROVED CONTRACT_APPROVED_DATE,
1759 chr.DATETIME_CANCELLED CONTRACT_CANCELED_TIMESTAMP,
1760 chr.DATE_SIGNED CONTRACT_SIGNED_DATE,
1761 chr.DATE_TERMINATED CONTRACT_TERMINATION_DATE,
1762 chr.START_DATE CONTRACT_START_DATE,
1763 chr.END_DATE CONTRACT_END_DATE,
1764 chr.CUST_ACCT_ID CUSTOMER_ACCOUNT_ID,
1765 chr.BILL_TO_SITE_USE_ID CUST_BILL_TO_SITE_USE_ID,
1766 chr.PAYMENT_TERM_ID CUST_PAYMENT_TERM_ID,
1767 chr.ORIG_SYSTEM_SOURCE_CODE CONTRACT_ORIG_SYSTEM_SOURCE,
1768 khr.ID CONTRACT_ID,
1769 khr.ISG_ID CONTRACT_SALES_REGION_ID,
1770 khr.KHR_ID VENDOR_PROGRAM_ID,
1771 khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
1772 --khr.DATE_CONVERSION_EFFECTIVE CONTRACT_DATE_DEAL_ACTIVATED,
1773 OKL_CNTRCT_FIN_EXTRACT_PVT.first_activation_date(khr.id) CONTRACT_DATE_DEAL_ACTIVATED,
1774 khr.DATE_DEAL_TRANSFERRED CONTRACT_DATE_DEAL_TRANSFERD,
1775 khr.TERM_DURATION CONTRACT_TERM_DURATION,
1776 khr.ATTRIBUTE_CATEGORY CONTRACT_HDR_DFF_CATEGORY,
1777 khr.ATTRIBUTE1 CONTRACT_HDR_DFF1,
1778 khr.ATTRIBUTE2 CONTRACT_HDR_DFF2,
1779 khr.ATTRIBUTE3 CONTRACT_HDR_DFF3,
1780 khr.ATTRIBUTE4 CONTRACT_HDR_DFF4,
1781 khr.ATTRIBUTE5 CONTRACT_HDR_DFF5,
1782 khr.ATTRIBUTE6 CONTRACT_HDR_DFF6,
1783 khr.ATTRIBUTE7 CONTRACT_HDR_DFF7,
1784 khr.ATTRIBUTE8 CONTRACT_HDR_DFF8,
1785 khr.ATTRIBUTE9 CONTRACT_HDR_DFF9,
1786 khr.ATTRIBUTE10 CONTRACT_HDR_DFF10,
1787 khr.ATTRIBUTE11 CONTRACT_HDR_DFF11,
1788 khr.ATTRIBUTE12 CONTRACT_HDR_DFF12,
1789 khr.ATTRIBUTE13 CONTRACT_HDR_DFF13,
1790 khr.ATTRIBUTE14 CONTRACT_HDR_DFF14,
1791 khr.ATTRIBUTE15 CONTRACT_HDR_DFF15,
1792 OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id) CONTRACT_ACCRUAL_STATUS,
1793 khr.GENERATE_ACCRUAL_OVERRIDE_YN CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
1794 khr.CREDIT_ACT_YN CONTRACT_CREDIT_ACT_YN,
1795 khr.CONVERTED_ACCOUNT_YN CONTRACT_CONVERTED_ACCOUNT_YN,
1796 khr.AFTER_TAX_YIELD CONTRACT_AFTER_TAX_YIELD,
1797 khr.IMPLICIT_INTEREST_RATE CONTRACT_IMPL_INTEREST_RATE,
1798 khr.DATE_LAST_INTERIM_INTEREST_CAL CONTRACT_LAST_INT_CALC_DATE,
1799 khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
1800 lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
1801 khr.PRE_TAX_IRR CONTRACT_PRE_TAX_IRR,
1802 khr.AFTER_TAX_IRR CONTRACT_AFTER_TAX_IRR,
1803 khr.EXPECTED_DELIVERY_DATE CONTRACT_EXP_DELIVERY_DATE,
1804 khr.ACCEPTED_DATE CONTRACT_ACCEPTANCE_DATE,
1805 khr.PREFUNDING_ELIGIBLE_YN CONTRACT_PREFUND_ELIG_YN,
1806 khr.REVOLVING_CREDIT_YN CONTRACT_REVOL_CREDIT_ELIG_YN,
1807 khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
1808 khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
1809 khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
1810 khr.MULTI_GAAP_YN CONTRACT_MULTI_GAAP_ELIG_YN ,
1811 khr.ASSIGNABLE_YN CONTRACT_INVESTOR_ASSIGN_YN ,
1812 khr.SECURITIZATION_TYPE CONTRACT_SECURITIZATION_TYPE,
1813 khr.SUB_AFTER_TAX_YIELD CONTRACT_SUB_AFTER_TAX_YIELD,
1814 khr.SUB_IMPL_INTEREST_RATE CONTRACT_SUB_IMPL_INT_RATE,
1815 khr.SUB_PRE_TAX_IRR CONTRACT_SUB_PRE_TAX_IRR,
1816 khr.SUB_AFTER_TAX_IRR CONTRACT_SUB_AFTER_TAX_IRR,
1817 khr.TOT_CL_TRANSFER_AMT CONTRACT_CRDTLINE_TRNSFR_AMT,
1818 khr.TOT_CL_NET_TRANSFER_AMT CONTRACT_CRDTLINE_NETRNSFR_AMT,
1819 khr.TOT_CL_LIMIT CONTRACT_CRDTLINE_LIMIT,
1820 khr.TOT_CL_FUNDING_AMT CONTRACT_CRDTLINE_FUNDING_AMT,
1821 khr.TEMPLATE_TYPE_CODE CONTRACT_TEMPL_TYPE_CODE,
1822 khr.TRADEIN_DESCRIPTION CONTRACT_TRADEIN_DESCRIPTION,
1823 khr.TRADEIN_AMOUNT CONTRACT_TRADEIN_AMOUNT,
1824 khr.DATE_TRADEIN CONTRACT_TRADEIN_DATE,
1825 khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
1826 khr.DATE_FIRST_ACTIVITY CONTRACT_FIRST_ACTIVITY_DATE,
1827 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACCUMULATED_DEPRN(chr.id, null),0) CONTRACT_ACCUMULATED_DEP,
1828 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_CAPITAL_REDUCTION(chr.id, null),0) CONTRACT_CAPITALIZED_REDUCTION,
1829 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FEES_CAPITALIZED(chr.id, null),0) CONTRACT_CAPITALIZED_FEE,
1830 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_capitalized_interest(chr.id, null),0) CONTRACT_CAPITALIZED_INTEREST,
1831 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DISCOUNT(chr.id, null),0) CONTRACT_DISCOUNT,
1832 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ASSET_COST(chr.id, null),0) CONTRACT_ASSET_COST,
1833 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FINANCED_FEE(chr.id, null),0) CONTRACT_FINANCED_FEE,
1834 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_NEXT_PAYMENT_AMOUNT(chr.id, null),0) CONTRACT_NEXT_PAYMENT_AMT,
1835 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACCRUED_INT(chr.id, null),0) CONTRACT_TOTAL_ACCRUED_INT,
1836 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACTUAL_INT(chr.id, null),0) CONTRACT_TOTAL_ACTUAL_INT,
1837 NVL(OKL_SEEDED_FUNCTIONS_PVT.get_asset_subsidy_amount(chr.id, null),0) CONTRACT_SUBSIDY_AMOUNT,
1838 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNACCRUED_SUBSIDY(chr.id, null),0) CONTRACT_UNACCRUED_SUBSIDY,
1839 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_STREAMS(chr.id, null),0) CONTRACT_UNBILLED_DUE_AMOUNT,
1840 DECODE(khr.deal_type,'LOAN',NULL,'LOAN-REVOLVING',NULL,NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_RECEIVABLES(chr.id, null),0)) CONTRACT_UNBILLED_RECEIVABLES,
1841 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_OEC(chr.id, null),0) CONTRACT_OEC,
1842 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(chr.id, null),0) CONTRACT_ACTUAL_PRINC_BAL, -- addl para
1843 NVL(OKL_SEEDED_FUNCTIONS_PVT.ROLLOVER_FEE(chr.id, null),0) ROLLOVER_FEE_AMOUNT,
1844 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_funded(chr.id, null),0) TOTAL_AMOUNT_PAID_TO_DEALER,
1845 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_debits(chr.id, null),0) TOTAL_DEBITS_FOR_PREFUNDING,
1846 NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_amount_prefunded(chr.id, null),0) CONTRACT_PREFUNDED_AMOUNT,
1847 -- null CONTRACT_BILLED_RENTS,
1848 (select SUM(NVL(txd.amount,0))
1849 from OKL_TXD_AR_LN_DTLS_B txd,
1850 okl_strm_type_b sty
1851 where txd.khr_id = khr.id
1852 and txd.sty_id = sty.id
1853 and sty.STREAM_TYPE_PURPOSE = 'RENT') CONTRACT_BILLED_RENTS,
1854
1855 NVL(OKL_FUNDING_PVT.get_total_retention(chr.id),0) SUPPLIER_RETENTION_FUNDING_AMT,
1856 NVL(OKL_FUNDING_PVT.get_chr_oec_hasbeen_funded_amt(chr.id),0) ASSETS_FUNDED_AMOUNT,
1857 NVL(OKL_FUNDING_PVT.get_chr_exp_hasbeen_funded_amt(chr.id),0) EXPENSE_FUNDED_AMOUNT,
1858 NVL(OKL_SEEDED_FUNCTIONS_PVT.FEE_IDC_AMOUNT(chr.id, null),0) CONTRACT_IDC_AMOUNT,
1859 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRIN_BALANCE(chr.id, null),0) CONTRACT_SCHEDULED_PRINC_BAL,
1860 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_INTEREST_RATE(chr.id, null),0) CONTRACT_INTEREST_RATE, -- addl para
1861 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_TO_ACCRUE(chr.id, null),0) CONTRACT_DAYS_TO_ACCRUE, -- addl para
1862 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_IN_YEAR(chr.id, null),0) CONTRACT_DAYS_IN_YEAR, -- addl para
1863
1864 hr_org.name OPERATING_UNIT,
1865 hr_org.short_code OPERATING_UNIT_SHORT_CODE,
1866 status.ste_code CONTRACT_STATUS_TYPE_CODE,
1867 status_tl.meaning CONTRACT_STATUS,
1868
1869 (select sum(kle_fee.amount)
1870 FROM okl_k_lines kle_fee, okc_k_lines_b cleb_fee, OKC_STATUSES_B status_fee
1871 WHERE cleb_fee.chr_id = khr.id
1872 AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
1873 and cleb_fee.dnz_chr_id = khr.id
1874 and cleb_fee.sts_code = status_fee.code
1875 and status_fee.ste_code not like 'CANCELLED'
1876 AND kle_fee.fee_type NOT IN ('FINANCED', 'ROLLOVER','SECURITY_DEPOSIT')
1877 and kle_fee.id = cleb_fee.id
1878 and cleb_fee.lse_id = 52) CONTRACT_FEE_INCOME,
1879 pdt.AES_ID ACC_ENGINE_TEMPL_SET_ID ,
1880 pdt.PTL_ID CONTRACT_FIN_PROD_TEMPLATE_ID ,
1881 pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
1882 pdt.DESCRIPTION CONTRACT_FIN_PROD_DESC ,
1883 pdt.REPORTING_PDT_ID REPORTING_PRODUCT_ID ,
1884 pdt.PRODUCT_STATUS_CODE CONTRACT_FIN_PROD_STATUS_CODE ,
1885 pdt.FROM_DATE CONTRACT_FIN_PROD_EFF_FROM_DT ,
1886 pdt.TO_DATE CONTRACT_FIN_PROD_EFF_TO_DT ,
1887 rpt_pdt.name REPORTING_PRODUCT,
1888 vendor_chr.contract_number VENDOR_PROGRAM_NAME,
1889 vendor.segment1 VENDOR_NUMBER,
1890 vendor.vendor_name VENDOR_NAME,
1891 vendor.vendor_type_lookup_code VENDOR_TYPE_CODE,
1892 CPL.bill_to_site_use_id VENDOR_BILL_TO_SITE_USE_ID,
1893 cpl.role VENDOR_PARTY_ROLE,
1894 (select nvl(SUM(NVL(txs.total_tax,0)),0)
1895 from okl_tax_sources txs
1896 where txs.khr_id = khr.id
1897 AND txs.tax_line_status_code = 'ACTIVE'
1898 AND txs.tax_call_type_code = 'UPFRONT_TAX') TOTAL_UPFRONT_TAX_ON_ASSETS,
1899 -- null TOTAL_NUMBER_OF_ASSETS,
1900 (select count(cleb.id)
1901 from okc_k_lines_b cleb, okc_statuses_b status_asset
1902 where cleb.chr_id = khr.id and cleb.dnz_chr_id = khr.id
1903 AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
1904 and cleb.sts_code = status_asset.code
1905 and status_asset.ste_code not in ('TERMINATED', 'CANCELLED')
1906 and cleb.lse_id = 33) TOTAL_NUMBER_OF_ASSETS,
1907
1908 ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
1909 ledger.ledger_id LEDGER_ID,
1910 ledger.name LEDGER,
1911 -- min(billing_control.EARLIEST_STRM_BILL_DATE) CONTRACT_EARLIEST_BILL_DATE,
1912 (select min(billing_control.EARLIEST_STRM_BILL_DATE)
1913 from OKL_K_CONTROL billing_control
1914 WHERE billing_control.khr_id = khr.id) CONTRACT_EARLIEST_BILL_DATE,
1915
1916 sales_rep.name CONTRACT_SALES_REP,
1917 legal_entity.name LEGAL_ENTITY,
1918
1919 cust_party.PARTY_NUMBER CUSTOMER_PARTY_NUMBER,
1920 cust_party.party_name CUSTOMER_PARTY_NAME,
1921 cust_party.PARTY_TYPE CUSTOMER_PARTY_TYPE,
1922 cust_party.SIC_CODE CUSTOMER_SIC_CODE,
1923 cust_party.HQ_BRANCH_IND CUSTOMER_HQ_BRANCH_IND,
1924 cust_party.TAX_REFERENCE CUSTOMER_TAX_REFERENCE,
1925 cust_party.DUNS_NUMBER CUSTOMER_DUNS_NUMBER,
1926 cust_party.KNOWN_AS CUSTOMER_ALIAS,
1927 cust_party.COUNTRY CUSTOMER_COUNTRY,
1928 cust_party.ADDRESS1 CUSTOMER_ADDRESS1,
1929 cust_party.ADDRESS2 CUSTOMER_ADDRESS2,
1930 cust_party.ADDRESS3 CUSTOMER_ADDRESS3,
1931 cust_party.ADDRESS4 CUSTOMER_ADDRESS4,
1932 cust_party.CITY CUSTOMER_CITY,
1933 cust_party.POSTAL_CODE CUSTOMER_POSTAL_CODE,
1934 cust_party.STATE CUSTOMER_STATE,
1935 cust_party.PROVINCE CUSTOMER_PROVINCE,
1936 cust_party.COUNTY CUSTOMER_COUNTY,
1937 cust_party.SIC_CODE_TYPE CUSTOMER_SIC_CODE_TYPE,
1938 cust_party.URL CUSTOMER_URL,
1939 cust_party.ANALYSIS_FY FINANCIAL_INFO_FISCAL_YEAR,
1940 cust_party.FISCAL_YEAREND_MONTH CUSTOMER_FISCAL_YEAREND_MONTH,
1941 cust_party.EMPLOYEES_TOTAL TOTAL_NUM_OF_EMPLOYEES,
1942 cust_party.CURR_FY_POTENTIAL_REVENUE CURR_FISC_YEAR_POTENTIAL_REV,
1943 cust_party.NEXT_FY_POTENTIAL_REVENUE NEXT_FISC_YEAR_POTENTIAL_REV,
1944 cust_party.YEAR_ESTABLISHED CUSTOMER_YEAR_ESTABLISHED,
1945 cust_party.GSA_INDICATOR_FLAG GEN_SERV_ADMIN_INDICATOR,
1946 cust_party.ORGANIZATION_NAME_PHONETIC CUSTOMER_ORG_NAME_PHONETIC,
1947 cust_party.COMPETITOR_FLAG CUSTOMER_COMPETITOR_YN,
1948 cust_party.KNOWN_AS2 CUSTOMER_ALIAS2,
1949 cust_party.KNOWN_AS3 CUSTOMER_ALIAS3,
1950 cust_party.KNOWN_AS4 CUSTOMER_ALIAS4,
1951 cust_party.KNOWN_AS5 CUSTOMER_ALIAS5,
1952 cust_party.PRIMARY_PHONE_PURPOSE CUSTOMER_PRIM_PHONE_PURPOSE,
1953 cust_party.PRIMARY_PHONE_LINE_TYPE CUSTOMER_PRIM_PHONE_TYPE,
1954 cust_party.PRIMARY_PHONE_COUNTRY_CODE CUSTOMER_PRIM_PH_COUNTRY_CODE,
1955 cust_party.PRIMARY_PHONE_AREA_CODE CUSTOMER_PRIM_PH_AREA_CODE,
1956 cust_party.PRIMARY_PHONE_NUMBER CUSTOMER_PRIM_PHONE_NUMBER,
1957 cust_party.PRIMARY_PHONE_EXTENSION CUSTOMER_PRIM_PHONE_EXTN,
1958 cust_party.HOME_COUNTRY CUSTOMER_HOME_COUNTRY,
1959
1960 -- cust_party.status CUSTOMER_STATUS,
1961 ar_lookup_status.meaning CUSTOMER_STATUS,
1962
1963 cust_accounts.PARTY_ID CUSTOMER_PARTY_ID ,
1964 cust_accounts.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
1965 cust_accounts.ATTRIBUTE_CATEGORY CUSTOMER_ACC_DFF_CATEGORY,
1966 cust_accounts.ATTRIBUTE1 CUSTOMER_ACC_DFF1 ,
1967 cust_accounts.ATTRIBUTE2 CUSTOMER_ACC_DFF2 ,
1968 cust_accounts.ATTRIBUTE3 CUSTOMER_ACC_DFF3,
1969 cust_accounts.ATTRIBUTE4 CUSTOMER_ACC_DFF4,
1970 cust_accounts.ATTRIBUTE5 CUSTOMER_ACC_DFF5,
1971 cust_accounts.ATTRIBUTE6 CUSTOMER_ACC_DFF6 ,
1972 cust_accounts.ATTRIBUTE7 CUSTOMER_ACC_DFF7,
1973 cust_accounts.ATTRIBUTE8 CUSTOMER_ACC_DFF8,
1974 cust_accounts.ATTRIBUTE9 CUSTOMER_ACC_DFF9,
1975 cust_accounts.ATTRIBUTE10 CUSTOMER_ACC_DFF10,
1976 cust_accounts.ATTRIBUTE11 CUSTOMER_ACC_DFF11,
1977 cust_accounts.ATTRIBUTE12 CUSTOMER_ACC_DFF12,
1978 cust_accounts.ATTRIBUTE13 CUSTOMER_ACC_DFF13,
1979 cust_accounts.ATTRIBUTE14 CUSTOMER_ACC_DFF14,
1980 cust_accounts.ATTRIBUTE15 CUSTOMER_ACC_DFF15,
1981 cust_accounts.ATTRIBUTE16 CUSTOMER_ACC_DFF16,
1982 cust_accounts.ATTRIBUTE17 CUSTOMER_ACC_DFF17,
1983 cust_accounts.ATTRIBUTE18 CUSTOMER_ACC_DFF18,
1984 cust_accounts.ATTRIBUTE19 CUSTOMER_ACC_DFF19,
1985 cust_accounts.ATTRIBUTE20 CUSTOMER_ACC_DFF20,
1986 ar_lookup_acc_status.meaning CUSTOMER_ACCOUNT_STATUS,
1987 -- cust_accounts.STATUS CUSTOMER_ACCOUNT_STATUS,
1988 -- cust_accounts.CUSTOMER_TYPE CUSTOMER_TYPE ,
1989 ar_lookup_cust_type.meaning CUSTOMER_TYPE ,
1990 cust_accounts.SALES_CHANNEL_CODE CUSTOMER_SALES_CHANNEL,
1991 cust_accounts.ACCOUNT_ESTABLISHED_DATE CUSTOMER_ACC_ESTAB_DATE,
1992 cust_accounts.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
1993 cust_accounts.COMMENTS CUSTOMER_ACCOUNT_COMMENTS,
1994 cust_accounts.STATUS_UPDATE_DATE CUSTOMER_LAST_STATUS_UPD_DT,
1995
1996 cust_party_site.PARTY_SITE_ID CUSTOMER_PARTY_SITE_ID,
1997 cust_party_site.PARTY_SITE_NUMBER CUSTOMER_PARTY_SITE_NUMBER,
1998 cust_party_site.PARTY_SITE_NAME CUSTOMER_PARTY_SITE_NAME,
1999 cust_party_site.LOCATION_ID CUSTOMER_PARTY_SITE_LOC_ID,
2000 cust_party_site.IDENTIFYING_ADDRESS_FLAG CUSTOMER_IDENTIFYING_ADR_FLAG,
2001 -- cust_party_site.STATUS CUSTOMER_PARTY_SITE_STATUS,
2002 ar_lookup_cust_site.meaning CUSTOMER_PARTY_SITE_STATUS,
2003 cust_party_site.ADDRESSEE CUSTOMER_PARTY_SITE_ADDRESSEE,
2004 cust_party_site.GLOBAL_LOCATION_NUMBER CUSTOMER_SITE_GLOB_LOC_NUM,
2005
2006 --null CUSTOMER_PARTY_SITE_ID,
2007 --null CUSTOMER_PARTY_SITE_NUMBER,
2008 --null CUSTOMER_PARTY_SITE_NAME,
2009 --null CUSTOMER_PARTY_SITE_LOC_ID,
2010 --null CUSTOMER_IDENTIFYING_ADR_FLAG,
2011 --null CUSTOMER_PARTY_SITE_STATUS,
2012 --null CUSTOMER_PARTY_SITE_ADDRESSEE,
2013 --null CUSTOMER_SITE_GLOB_LOC_NUM,
2014
2015 cust_party_site_use.cpsu_PARTY_SITE_USE_ID CUSTOMER_PARTY_SITE_USE_ID,
2016 cust_party_site_use.cpsu_SITE_USE_TYPE CUSTOMER_PARTY_SITE_USE_TYPE,
2017 cust_party_site_use.cpsu_COMMENTS CUSTOMER_PARTY_SITE_COMMENTS,
2018 -- null CUSTOMER_PARTY_SITE_USE_ID,
2019 -- null CUSTOMER_PARTY_SITE_USE_TYPE,
2020 -- null CUSTOMER_PARTY_SITE_COMMENTS,
2021 -- cust_party_site_use.STATUS CUSTOMER_PARTY_SITE_USE_STATUS,
2022 ar_lookup_cust_site_use.meaning CUSTOMER_PARTY_SITE_USE_STATUS,
2023 -- null CUSTOMER_PARTY_SITE_USE_STATUS,
2024
2025 null CONTRACT_PAST_DUE_AMT_1_30,
2026 null CONTRACT_PAST_DUE_AMT_31_60,
2027 null CONTRACT_PAST_DUE_AMT_61_90,
2028 null CONTRACT_PAST_DUE_AMT_91_120,
2029 null CONTRACT_PAST_DUE_AMT_120_Plus,
2030
2031 -- NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,1,30),0) CONTRACT_PAST_DUE_AMT_1_30,
2032 --NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,31,60),0) CONTRACT_PAST_DUE_AMT_31_60,
2033 --NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,61,90),0) CONTRACT_PAST_DUE_AMT_61_90,
2034 --NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,91,120),0) CONTRACT_PAST_DUE_AMT_91_120,
2035 --NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,120,12000),0) CONTRACT_PAST_DUE_AMT_120_Plus,
2036
2037
2038 null CONTRACT_TOT_PAST_DUE_AMT,
2039 -- null CONTRACT_TOT_BILLED_RECEIVABLE,
2040 (select SUM(NVL(txd.amount,0))
2041 from OKL_TXD_AR_LN_DTLS_B txd
2042 where txd.khr_id = khr.id ) CONTRACT_TOT_BILLED_RECEIVABLE,
2043
2044 /*
2045 -- for Net Investment
2046 DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
2047 'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
2048 0 ) CONTRACT_UNEARNED_INCOME,
2049 DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
2050 'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
2051 0) CONTRACT_RESIDUAL_AMOUNT,
2052 DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
2053 'LEASEST',NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
2054 0) CONTRACT_RENT_AMOUNT,
2055 DECODE(khr.deal_type, 'LEASEOP', NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0),
2056 0) CONTRACT_ACC_DEPRECIATION, --Duplicate of CONTRACT_ACCUMULATED_DEP
2057 */
2058 --sechawla : calculate CONTRACT_UNEARNED_INCOME, CONTRACT_RESIDUAL_AMOUNT, CONTRACT_RENT_AMOUNT, CONTRACT_ACC_DEPRECIATION for all types of leases
2059 NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0) CONTRACT_UNEARNED_INCOME,
2060 NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0) CONTRACT_RESIDUAL_AMOUNT,
2061 NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0) CONTRACT_RENT_AMOUNT,
2062 NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0) CONTRACT_ACC_DEPRECIATION,
2063
2064 NVL(OKL_FUNDING_PVT.get_amount_prefunded(chr.id),0) CONTRACT_AMOUNT_PREFUNDED,
2065 NVL(OKL_FUNDING_PVT.get_chr_funded_adjs(chr.id),0) CONTRACT_FUNDED_ADJS,
2066 NVL(OKL_FUNDING_PVT.get_amount_manu_disb(chr.id),0) CONTRACT_AMOUNT_MANU_DISB,
2067
2068 l_last_updated_by CREATED_BY,
2069 sysdate CREATION_DATE,
2070 l_last_updated_by LAST_UPDATED_BY,
2071 sysdate LAST_UPDATE_DATE,
2072 l_last_update_login LAST_UPDATE_LOGIN,
2073 l_program_app_id PROGRAM_APPLICATION_ID,
2074 l_program_id PROGRAM_ID,
2075 l_program_login_id PROGRAM_LOGIN_ID,
2076 sysdate PROGRAM_UPDATE_DATE
2077
2078 FROM
2079 OKC_K_HEADERS_ALL_B chr,
2080 OKL_K_HEADERS khr,
2081 OKL_PARALLEL_PROCESSES opp,
2082 HR_OPERATING_UNITS hr_org,
2083 OKC_STATUSES_B status,
2084 OKC_STATUSES_TL status_tl,
2085 OKC_K_PARTY_ROLES_B cust_party_roles,
2086 fnd_lookup_values_vl lookup_book_class,
2087
2088 HZ_PARTIES cust_party,
2089 HZ_CUST_ACCOUNTS cust_accounts,
2090 OKL_PRODUCTS pdt,
2091 OKL_PRODUCTS rpt_pdt,
2092 OKC_K_HEADERS_ALL_B vendor_chr,
2093
2094 okc_rule_groups_b rgd,
2095 okc_k_party_roles_v cpl,
2096 po_vendors vendor,
2097 OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
2098 GL_LEDGERS ledger,
2099 okc_contacts sales_rep_contact,
2100 okx_salesreps_v sales_rep,
2101 xle_entity_profiles legal_entity,
2102
2103
2104 hz_party_sites cust_party_site,
2105 -- hz_party_site_uses cust_party_site_use,
2106 (select cpsu_party_site_use_id,
2107 cpsu_party_site_id,
2108 cpsu_SITE_USE_TYPE,
2109 cpsu_status,
2110 cpsu_comments
2111 from (
2112 select
2113 cust_party_site_use_n.party_site_use_id cpsu_party_site_use_id, --sechawla changed party_site_id to party_site_use_id
2114 cust_party_site_use_n.party_site_id cpsu_party_site_id,
2115 cust_party_site_use_n.SITE_USE_TYPE cpsu_site_use_type,
2116 cust_party_site_use_n.status cpsu_status,
2117 cust_party_site_use_n.comments cpsu_comments,
2118 row_number() over ( partition by cust_party_site_use_n.party_site_id
2119 order by NVL(cust_party_site_use_n.status,'A') ASC) cpsu_stat_priority
2120 from hz_party_site_uses cust_party_site_use_n
2121 where cust_party_site_use_n.SITE_USE_TYPE = 'BILL_TO'
2122 ) where cpsu_stat_priority = 1) cust_party_site_use,
2123
2124 hz_cust_site_uses_all cust_cust_site_use,
2125
2126 hz_cust_acct_sites_all cust_cust_acct_site,
2127
2128 ar_lookups ar_lookup_status,
2129 ar_lookups ar_lookup_acc_status,
2130 ar_lookups ar_lookup_cust_type,
2131 ar_lookups ar_lookup_cust_site,
2132 ar_lookups ar_lookup_cust_site_use
2133
2134 WHERE chr.id = khr.id
2135 AND chr.SCS_CODE = 'LEASE'
2136 AND opp.object_type = 'CONTRACT_FIN_EXTRACT'
2137 AND opp.object_value = chr.contract_number
2138 AND opp.assigned_process = p_assigned_process
2139 AND opp.khr_id = chr.id
2140
2141 AND hr_org.organization_id = chr.authoring_org_id
2142 AND status.code = chr.sts_code
2143 AND status_tl.code = status.code
2144 AND status_tl.language = USERENV('LANG')
2145 AND cust_party.party_type in ( 'PERSON','ORGANIZATION')
2146 AND cust_party.party_id = cust_party_roles.object1_id1
2147 AND cust_party_roles.object1_id2 = '#'
2148 AND cust_party_roles.jtot_object1_code = 'OKX_PARTY'
2149 AND cust_party_roles.rle_code = 'LESSEE'
2150 AND cust_party_roles.chr_id = chr.id
2151 AND cust_party_roles.dnz_chr_id = chr.id
2152 AND cust_accounts.cust_account_id = chr.cust_acct_id
2153 AND cust_accounts.party_id = cust_party.party_id
2154
2155 AND khr.pdt_id = pdt.id
2156
2157 AND pdt.REPORTING_PDT_ID = rpt_pdt.id(+)
2158 AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
2159 AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
2160
2161 AND vendor_chr.id (+) = khr.khr_id
2162 AND vendor_chr.scs_code (+) = 'PROGRAM'
2163
2164 AND rgd.chr_id(+)= vendor_chr.id
2165 AND rgd.dnz_chr_id(+) = vendor_chr.id
2166 AND rgd.cle_id IS NULL
2167 AND rgd.rgd_code(+) = 'LAVENB'
2168 AND vendor_chr.id = cpl.chr_id (+)
2169 AND vendor_chr.id = cpl.dnz_chr_id(+)
2170
2171 AND cpl.rle_code (+) = 'OKL_VENDOR'
2172 and cpl.object1_id1 = vendor.vendor_id (+)
2173
2174 AND acct_sys_opts.org_id = chr.authoring_org_id
2175 AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
2176
2177 AND sales_rep_contact.DNZ_CHR_ID (+) = khr.id
2178 AND sales_rep.id1 (+) = sales_rep_contact.object1_id1
2179 AND sales_rep.id2 (+) = sales_rep_contact.object1_id2
2180 and sales_rep_contact.cro_code (+) = 'SALESPERSON'
2181
2182
2183 AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
2184
2185 and cust_party.party_id = cust_party_site.party_id
2186 AND cust_cust_site_use.site_use_id = chr.bill_to_site_use_id
2187 AND cust_cust_site_use.cust_acct_site_id = cust_cust_acct_site.cust_acct_site_id
2188 --and cust_cust_site_use.SITE_USE_CODE = 'BILL_TO'
2189 and cust_cust_acct_site.party_site_id = cust_party_site.party_site_id
2190 and cust_party_site.party_site_id = cust_party_site_use.cpsu_party_site_id
2191 --and cust_party_site_use.SITE_USE_TYPE = 'BILL_TO'
2192
2193 AND ar_lookup_status.lookup_type(+) = 'HZ_CPUI_REGISTRY_STATUS'
2194 AND ar_lookup_status.lookup_code(+) = cust_party.status
2195 AND ar_lookup_acc_status.lookup_type(+) = 'CODE_STATUS'
2196 AND ar_lookup_acc_status.lookup_code(+) = cust_accounts.STATUS
2197 AND ar_lookup_cust_type.lookup_type(+) = 'CUSTOMER_TYPE'
2198 AND ar_lookup_cust_type.lookup_code(+) = cust_accounts.CUSTOMER_TYPE
2199 AND ar_lookup_cust_site.lookup_type(+) = 'REGISTRY_STATUS'
2200 AND ar_lookup_cust_site.lookup_code(+) = cust_party_site.STATUS
2201 AND ar_lookup_cust_site_use.lookup_type(+) = 'REGISTRY_STATUS'
2202 AND ar_lookup_cust_site_use.lookup_code(+) = cust_party_site_use.cpsu_STATUS
2203
2204 -- parameters section begin
2205 /*
2206 --sechawla : don't need the following as these paramater matching has been already
2207 --been done in Master program - OKL_CNTRCT_FIN_EXT_MASTER_PVT
2208
2209 AND chr.AUTHORING_ORG_ID = P_OPERATING_UNIT
2210 AND chr.START_DATE >= P_START_DATE_FROM
2211 AND chr.START_DATE <= P_START_DATE_TO
2212 AND khr.DEAL_TYPE like nvl(P_BOOK_CLASS, khr.DEAL_TYPE)
2213 AND pdt.ID like nvl(P_LEASE_PRODUCT, pdt.ID)
2214 AND chr.sts_code like nvl(P_CONTRACT_STATUS, chr.sts_code)
2215 AND (P_END_DATE_FROM IS NULL OR chr.END_DATE >= P_END_DATE_FROM)
2216 AND (P_END_DATE_TO IS NULL OR chr.END_DATE <= P_END_DATE_TO)
2217 */
2218
2219 --there is no ID defined in the value set OKS_CUSTOMER_NUMBER. value is 'party_number'.
2220 AND cust_party.PARTY_NUMBER like nvl(P_CUSTOMER_NUMBER, cust_party.PARTY_NUMBER)
2221
2222 --sechawla : P_CUSTOMER_NAME is hz_parties.party_id. value set: OKL_CUSTOMERS
2223 AND cust_party.party_id = nvl(P_CUSTOMER_NAME, cust_party.party_id)
2224 AND nvl(cust_party.SIC_CODE,'XXX') like nvl(P_SIC_CODE, nvl(cust_party.SIC_CODE,'XXX'))
2225
2226 --sechawla : p_vendor_number -> value set 'OKL_VENDORS' -> returns PO_VENDORS.VENDOR_ID
2227 --Need nvl, as every contract may not have vendor_id (program vendor)
2228 AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NUMBER,nvl(vendor.vendor_id,-9999))
2229
2230 --sechawla : P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
2231 AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NAME,nvl(vendor.vendor_id,-9999))
2232
2233 -- P_SALES_CHANNEL -> value set QP_SALES_CHANNEL_CODE -> returns oe_lookups.lookup_code
2234 AND nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX') like nvl(P_SALES_CHANNEL, nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX'))
2235
2236 -- AND CONTRACT_ACCRUAL_STATUS like nvl(P_GEN_ACCRUAL, CONTRACT_ACCRUAL_STATUS)
2237 AND OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id) like nvl(P_GEN_ACCRUAL, OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id))
2238
2239 AND ( (P_TERMINATE_DATE_FROM IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED >= P_TERMINATE_DATE_FROM) )
2240 AND ( (P_TERMINATE_DATE_TO IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED <= P_TERMINATE_DATE_TO) )
2241
2242 -- parameters section end
2243
2244 );
2245
2246
2247
2248
2249
2250 END IF; -- IF P_AR_INFO_YN like 'YES'
2251 x_row_count := sql%rowcount;
2252 commit;
2253
2254 IF x_row_count IS NOT NULL THEN
2255 write_to_log(x_row_count||' rows extracted in OKL_CNTRCT_FIN_EXTRACT_T .');
2256 END IF;
2257
2258
2259 Okl_Api.END_ACTIVITY (
2260 x_msg_count => x_msg_count,
2261 x_msg_data => x_msg_data);
2262 EXCEPTION
2263 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2264
2265 write_to_log('G_EXCEPTION_ERROR was raised..');
2266 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2267 (
2268 l_api_name,
2269 G_PKG_NAME,
2270 'OKC_API.G_RET_STS_ERROR',
2271 x_msg_count,
2272 x_msg_data,
2273 '_PVT'
2274 );
2275
2276 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2277 write_to_log('G_EXCEPTION_UNEXPECTED_ERROR was raised..');
2278 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2279 (
2280 l_api_name,
2281 G_PKG_NAME,
2282 'OKC_API.G_RET_STS_UNEXP_ERROR',
2283 x_msg_count,
2284 x_msg_data,
2285 '_PVT'
2286 );
2287 WHEN OTHERS THEN
2288
2289 write_to_log('Unhandled Exception '||sqlcode||':'||sqlerrm);
2290
2291 okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2292 p_msg_name => G_UNEXPECTED_ERROR,
2293 p_token1 => G_SQLCODE_TOKEN,
2294 p_token1_value => SQLCODE,
2295 p_token2 => G_SQLERRM_TOKEN,
2296 p_token2_value => SQLERRM);
2297
2298 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2299 (
2300 l_api_name,
2301 G_PKG_NAME,
2302 'OTHERS',
2303 x_msg_count,
2304 x_msg_data,
2305 '_PVT'
2306 );
2307
2308
2309
2310 END pull_extract_data;
2311
2312
2313 PROCEDURE pull_extract_data_conc (
2314 errbuf OUT NOCOPY VARCHAR2,
2315 retcode OUT NOCOPY NUMBER,
2316 P_OPERATING_UNIT IN NUMBER,
2317 --P_REPORT_DATE IN VARCHAR2, --sechawla 25-sep-09 8890513
2318
2319 P_START_DATE_FROM IN VARCHAR2,
2320 P_START_DATE_TO IN VARCHAR2,
2321 P_AR_INFO_YN IN VARCHAR2,
2322 P_BOOK_CLASS IN VARCHAR2,
2323 P_LEASE_PRODUCT IN VARCHAR2,
2324 P_CONTRACT_STATUS IN VARCHAR2,
2325 P_CUSTOMER_NUMBER IN VARCHAR2,
2326 P_CUSTOMER_NAME IN VARCHAR2,
2327 P_SIC_CODE IN VARCHAR2,
2328 P_VENDOR_NUMBER IN VARCHAR2,
2329 P_VENDOR_NAME IN VARCHAR2,
2330 P_SALES_CHANNEL IN VARCHAR2,
2331 P_GEN_ACCRUAL IN VARCHAR2,
2332 P_END_DATE_FROM IN VARCHAR2,
2333 P_END_DATE_TO IN VARCHAR2,
2334 P_TERMINATE_DATE_FROM IN VARCHAR2,
2335 P_TERMINATE_DATE_TO IN VARCHAR2,
2336 P_DELETE_DATA_YN IN VARCHAR2,
2337 p_num_processes IN NUMBER,
2338 p_assigned_process IN VARCHAR2
2339 )
2340 IS
2341
2342 l_api_version NUMBER := 1;
2343
2344 lx_msg_count NUMBER;
2345
2346 l_msg_index_out NUMBER :=0;
2347 lx_msg_data VARCHAR2(450);
2348 lx_return_status VARCHAR2(1);
2349
2350 l_request_id NUMBER;
2351 l_row_count NUMBER;
2352 l_msg_idx INTEGER := FND_MSG_PUB.G_FIRST;
2353 lx_error_rec OKL_API.error_rec_type;
2354
2355 CURSOR req_id_csr IS
2356 SELECT
2357 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID) --sechawla : why Null if -1 ?
2358 FROM dual;
2359
2360 ------------------------------------------------------------
2361 -- Operating Unit
2362 ------------------------------------------------------------
2363 CURSOR op_unit_csr IS
2364 SELECT NAME
2365 FROM hr_operating_units
2366 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID;--MOAC- Concurrent request
2367
2368
2369 l_del_row_count NUMBER;
2370 l_op_unit_name hr_operating_units.name%TYPE;
2371
2372 lp_k_term_date_from DATE;
2373 lp_k_term_date_to DATE;
2374
2375 BEGIN
2376
2377 -- Add couple of blank lines
2378 fnd_file.new_line(fnd_file.log,2);
2379 fnd_file.new_line(fnd_file.output,2);
2380
2381 MO_GLOBAL.set_policy_context('S',p_operating_unit);
2382
2383 -- Get the request Id
2384 l_request_id := NULL;
2385 OPEN req_id_csr;
2386 FETCH req_id_csr INTO l_request_id;
2387 CLOSE req_id_csr;
2388
2389 write_to_log('Child Request ID:'||l_request_id);
2390
2391 ----------------------------------------
2392 -- Get Operating unit name
2393 ----------------------------------------
2394 l_op_unit_name := NULL;
2395 OPEN op_unit_csr;
2396 FETCH op_unit_csr INTO l_op_unit_name;
2397 CLOSE op_unit_csr;
2398
2399
2400 write_to_log('l_op_unit_name :'||l_op_unit_name);
2401
2402 IF P_TERMINATE_DATE_FROM IS NOT NULL THEN
2403 lp_k_term_date_from := FND_DATE.CANONICAL_TO_DATE(P_TERMINATE_DATE_FROM);
2404
2405 END IF;
2406
2407 IF P_TERMINATE_DATE_TO IS NOT NULL THEN
2408 lp_k_term_date_to := FND_DATE.CANONICAL_TO_DATE(P_TERMINATE_DATE_TO);
2409 END IF;
2410
2411
2412
2413 write_to_log('Parameters in pull_extract_data_conc ...');
2414 write_to_log('P_OPERATING_UNIT = '||P_OPERATING_UNIT);
2415 --write_to_log('P_REPORT_DATE = '||P_REPORT_DATE); --sechawla 25-sep-09 8890513
2416
2417 write_to_log('P_START_DATE_FROM = '||P_START_DATE_FROM);
2418 write_to_log('P_START_DATE_TO = '||P_START_DATE_TO);
2419 write_to_log('P_AR_INFO_YN = '||P_AR_INFO_YN);
2420 write_to_log('P_BOOK_CLASS = '||P_BOOK_CLASS);
2421 write_to_log('P_LEASE_PRODUCT = '||P_LEASE_PRODUCT);
2422 write_to_log('P_CONTRACT_STATUS = '||P_CONTRACT_STATUS);
2423 write_to_log('P_CUSTOMER_NUMBER = '||P_CUSTOMER_NUMBER);
2424 write_to_log('P_CUSTOMER_NAME = '||P_CUSTOMER_NAME);
2425 write_to_log('P_SIC_CODE = '||P_SIC_CODE);
2426 write_to_log('P_VENDOR_NUMBER = '||P_VENDOR_NUMBER);
2427 write_to_log('P_VENDOR_NAME = '||P_VENDOR_NAME);
2428 write_to_log('P_SALES_CHANNEL = '||P_SALES_CHANNEL);
2429 write_to_log('P_GEN_ACCRUAL = '||P_GEN_ACCRUAL);
2430 write_to_log('P_END_DATE_FROM = '||P_END_DATE_FROM);
2431 write_to_log('P_END_DATE_TO = '||P_END_DATE_TO);
2432 write_to_log('P_TERMINATE_DATE_FROM = '||P_TERMINATE_DATE_FROM);
2433 write_to_log('P_TERMINATE_DATE_TO = '||P_TERMINATE_DATE_TO);
2434 write_to_log('P_DELETE_DATA_YN '||P_DELETE_DATA_YN);
2435 write_to_log('p_num_processes = '||p_num_processes);
2436 write_to_log('p_assigned_process = '||p_assigned_process);
2437
2438 pull_extract_data (
2439 p_api_version => l_api_version,
2440 p_init_msg_list => Okl_Api.G_FALSE,
2441 x_return_status => lx_return_status,
2442 x_msg_count => lx_msg_count,
2443 x_msg_data => errbuf,
2444 x_row_count => l_row_count,
2445 P_OPERATING_UNIT => P_OPERATING_UNIT,
2446 --P_REPORT_DATE => P_REPORT_DATE, --sechawla 25-sep-09 8890513
2447
2448 P_START_DATE_FROM => P_START_DATE_FROM,
2449 P_START_DATE_TO => P_START_DATE_TO,
2450 P_AR_INFO_YN => P_AR_INFO_YN,
2451 P_BOOK_CLASS => P_BOOK_CLASS,
2452 P_LEASE_PRODUCT => P_LEASE_PRODUCT,
2453 P_CONTRACT_STATUS => P_CONTRACT_STATUS,
2454 P_CUSTOMER_NUMBER => P_CUSTOMER_NUMBER,
2455 P_CUSTOMER_NAME => P_CUSTOMER_NAME,
2456 P_SIC_CODE => P_SIC_CODE,
2457 P_VENDOR_NUMBER => P_VENDOR_NUMBER,
2458 P_VENDOR_NAME => P_VENDOR_NAME,
2459 P_SALES_CHANNEL => P_SALES_CHANNEL,
2460 P_GEN_ACCRUAL => P_GEN_ACCRUAL,
2461 P_END_DATE_FROM => P_END_DATE_FROM,
2462 P_END_DATE_TO => P_END_DATE_TO,
2463 P_TERMINATE_DATE_FROM => lp_k_term_date_from,
2464 P_TERMINATE_DATE_TO => lp_k_term_date_to,
2465 P_DELETE_DATA_YN => P_DELETE_DATA_YN,
2466 p_num_processes => p_num_processes,
2467 p_assigned_process => p_assigned_process);
2468
2469
2470 write_to_log('lx_return_status of pull_extract_data :'||lx_return_status);
2471
2472 --sechawla : added
2473 -- Get the messages in the log
2474 LOOP
2475
2476 fnd_msg_pub.get(
2477 p_msg_index => l_msg_idx,
2478 p_encoded => FND_API.G_FALSE,
2479 p_data => lx_error_rec.msg_data,
2480 p_msg_index_out => lx_error_rec.msg_count);
2481
2482 IF (lx_error_rec.msg_count IS NOT NULL) THEN
2483
2484 fnd_file.put_line(fnd_file.log, lx_error_rec.msg_data);
2485 fnd_file.put_line(fnd_file.output, lx_error_rec.msg_data);
2486
2487 END IF;
2488
2489 EXIT WHEN ((lx_error_rec.msg_count = FND_MSG_PUB.COUNT_MSG)
2490 OR (lx_error_rec.msg_count IS NULL));
2491
2492 l_msg_idx := FND_MSG_PUB.G_NEXT;
2493 END LOOP;
2494
2495 fnd_file.new_line(fnd_file.log,2);
2496
2497 IF lx_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2498 write_to_log('Extraction failed for Contract Financial Extract.');
2499
2500 DELETE OKL_PARALLEL_PROCESSES
2501 WHERE assigned_process = p_assigned_process;
2502
2503 l_del_row_count := sql%rowcount;
2504
2505 write_to_log('Deleted '||l_del_row_count||' rows from OKL_PARALLEL_PROCESSES, for assigned process '||p_assigned_process);
2506 COMMIT;
2507
2508 retcode := 2;
2509 ELSIF lx_return_status = Okl_Api.G_RET_STS_SUCCESS THEN
2510 -- purge data
2511 IF p_assigned_process IS NOT NULL THEN
2512
2513 DELETE OKL_PARALLEL_PROCESSES
2514 WHERE assigned_process = p_assigned_process;
2515
2516 l_del_row_count := sql%rowcount;
2517 write_to_log('Purged '||l_del_row_count||' rows from OKL_PARALLEL_PROCESSES, for assigned process '||p_assigned_process);
2518
2519 COMMIT;
2520
2521 END IF;
2522
2523 END IF;
2524
2525 IF l_row_count IS NULL OR l_row_count = 0 THEN
2526 write_to_log('There was no data extracted for Contract Financial Extract.');
2527
2528 ELSE
2529 write_to_log( l_row_count||' rows extracted in OKL_CNTRCT_FIN_EXTRACT_T for Contract Financial Extract.');
2530
2531 END IF;
2532
2533
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536
2537 write_to_log('Unhandled Exception in pull_extract_data_conc '||sqlcode||':'||sqlerrm);
2538
2539
2540 IF p_assigned_process IS NOT NULL THEN
2541 DELETE OKL_PARALLEL_PROCESSES
2542 WHERE assigned_process = p_assigned_process;
2543
2544 write_to_log('Deleted '||sql%rowcount||' rows from OKL_PARALLEL_PROCESSES, for assigned process '||p_assigned_process);
2545
2546
2547 COMMIT;
2548
2549
2550 END IF;
2551
2552 OKL_API.set_message(p_app_name => 'OKC',
2553 p_msg_name => g_unexpected_error,
2554 p_token1 => g_sqlcode_token,
2555 p_token1_value => sqlcode,
2556 p_token2 => g_sqlerrm_token,
2557 p_token2_value => sqlerrm);
2558
2559 END pull_extract_data_conc;
2560
2561 ---------------------------------------------------------------------------------------------------------------------
2562
2563 -- Start of Comments
2564 -- Created By: Durga Janaswamy(djanaswa)
2565 -- Function Name AMOUNT_DUE_REMAINING
2566 -- Description: returns AMOUNT_DUE_REMAINING
2567 -- Dependencies:
2568 -- Parameters: contract number, from days, to days
2569 -- Version: 1.0
2570 -- End of Commnets
2571 ----------------------------------------------------------------------------------------------------
2572
2573
2574 FUNCTION amount_due_remaining(
2575 p_CONTRACT_NUMBER IN VARCHAR2,
2576 p_from_days IN number,
2577 p_to_days IN number)
2578 RETURN NUMBER IS
2579
2580 l_api_name CONSTANT VARCHAR2(60) := 'AMOUNT_DUE_REMAINING';
2581 l_api_version CONSTANT NUMBER := 1;
2582 x_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2583 x_msg_count NUMBER;
2584 x_msg_data VARCHAR2(256);
2585 -- Start fix for Bug 7586827
2586 /*
2587 CURSOR l_AMOUNT_DUE_REMAINING_csr (
2588 p_CONTRACT_NUMBER VARCHAR2,
2589 p_from_days number,
2590 p_to_days number) IS
2591 SELECT
2592 SUM(NVL(ps.AMOUNT_DUE_REMAINING,0))
2593 From
2594 ar_payment_schedules_all ps,
2595 ra_customer_trx_lines_all ctl
2596 WHERE
2597 ctl.INTERFACE_LINE_ATTRIBUTE6 = p_CONTRACT_NUMBER
2598 AND ctl.CUSTOMER_TRX_ID = ps.CUSTOMER_TRX_ID
2599 AND ps.AMOUNT_DUE_REMAINING > 0
2600 AND trunc(sysdate) > ps.DUE_DATE
2601 AND (trunc(sysdate) - ps.DUE_DATE) between p_from_days and p_to_days;
2602 */
2603 CURSOR l_AMOUNT_DUE_REMAINING_csr (
2604 p_CONTRACT_NUMBER VARCHAR2,
2605 p_from_days number,
2606 p_to_days number) IS
2607 SELECT
2608 sum(nvl(okl_billing_util_pvt.invoice_line_amount_remaining(cust_trx.customer_trx_id, cust_trx_lines.customer_trx_line_id), 0)) Amount_Due_remaining
2609 FROM ra_customer_trx_all cust_trx,
2610 ra_customer_trx_lines_all cust_trx_lines,
2611 ar_payment_schedules_all ar
2612 WHERE cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id
2613 AND cust_trx_lines.line_type = 'LINE'
2614 AND cust_trx_lines.interface_line_attribute1 IS NULL
2615 AND cust_trx_lines.customer_trx_id = ar.customer_trx_id
2616 AND cust_trx.customer_trx_id = ar.customer_trx_id
2617 AND ar.CLASS = 'INV'
2618 and ar.status = 'OP'
2619 AND cust_trx_lines.interface_line_context IN ( 'OKL_CONTRACTS','OKL_MANUAL')
2620 and cust_trx_lines.interface_line_attribute6 = p_contract_number
2621 and trunc(sysdate) > ar.DUE_DATE
2622 AND (trunc(sysdate) - ar.DUE_DATE) between p_from_days and p_to_days;
2623 -- End fix for bug 7586827
2624
2625 l_AMOUNT_DUE_REMAINING NUMBER := 0;
2626
2627
2628 BEGIN
2629
2630 IF (p_CONTRACT_NUMBER IS NULL OR p_from_days IS NULL OR p_to_days IS NULL) THEN
2631 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2632 END IF;
2633
2634
2635 OPEN l_AMOUNT_DUE_REMAINING_csr (p_CONTRACT_NUMBER, p_from_days, p_to_days);
2636 FETCH l_AMOUNT_DUE_REMAINING_csr INTO l_AMOUNT_DUE_REMAINING;
2637 CLOSE l_AMOUNT_DUE_REMAINING_csr;
2638
2639 IF l_AMOUNT_DUE_REMAINING IS NULL THEN
2640 l_AMOUNT_DUE_REMAINING := 0;
2641 END IF;
2642
2643
2644 RETURN l_AMOUNT_DUE_REMAINING;
2645
2646
2647 EXCEPTION
2648
2649 WHEN OTHERS THEN
2650 IF l_AMOUNT_DUE_REMAINING_csr%ISOPEN THEN
2651 CLOSE l_AMOUNT_DUE_REMAINING_csr;
2652 END IF;
2653
2654 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2655 p_msg_name => G_UNEXPECTED_ERROR,
2656 p_token1 => G_SQLCODE_TOKEN,
2657 p_token1_value => SQLCODE,
2658 p_token2 => G_SQLERRM_TOKEN,
2659 p_token2_value => SQLERRM);
2660 RETURN 0;
2661
2662 END amount_due_remaining;
2663
2664 FUNCTION first_activation_date (p_chr_id NUMBER) return DATE IS
2665
2666 CURSOR l_okl_trx_contracts_csr(cp_khr_id IN NUMBER) IS
2667 SELECT transaction_date
2668 FROM okl_trx_contracts_all
2669 WHERE khr_id = cp_khr_id
2670 AND try_id = 252879000775029305619701078073046135725 --'Booking' transaction id
2671 AND tcn_type = 'BKG'
2672 ORDER BY transaction_date;
2673
2674
2675 l_first_booking_dt DATE := NULL;
2676
2677 BEGIN
2678
2679 IF (p_chr_id IS NULL) THEN
2680 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2681 END IF;
2682
2683 --Each time contract is Rebooked, a Booking transaction is created again. Hence
2684 --This cursor may return multiple rows. The first row identifies the first Booking transaction.
2685 OPEN l_okl_trx_contracts_csr (p_chr_id );
2686 FETCH l_okl_trx_contracts_csr INTO l_first_booking_dt;
2687 CLOSE l_okl_trx_contracts_csr;
2688
2689 RETURN l_first_booking_dt;
2690
2691
2692 EXCEPTION
2693
2694 WHEN OTHERS THEN
2695 IF l_okl_trx_contracts_csr%ISOPEN THEN
2696 CLOSE l_okl_trx_contracts_csr;
2697 END IF;
2698
2699 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2700 p_msg_name => G_UNEXPECTED_ERROR,
2701 p_token1 => G_SQLCODE_TOKEN,
2702 p_token1_value => SQLCODE,
2703 p_token2 => G_SQLERRM_TOKEN,
2704 p_token2_value => SQLERRM);
2705 RETURN NULL;
2706
2707 END first_activation_date;
2708
2709 ----------------------------------------------------------------------------------------------------
2710
2711
2712 -- Start of Comments
2713 -- Created By: Durga Janaswamy(djanaswa)
2714 -- Function Name accrual_status_yn
2715 -- Description: returns accrual_status_yn
2716 -- Dependencies:
2717 -- Parameters: contract id,
2718 -- Version: 1.0
2719 -- End of Commnets
2720
2721 ----------------------------------------------------------------------------------------------------
2722
2723 FUNCTION accrual_status_yn (p_chr_id NUMBER) return VARCHAR2
2724 IS
2725
2726 CURSOR accrual_status_csr (p_chr_id NUMBER) IS
2727 SELECT trx.accrual_status_yn accrual_status
2728 FROM OKL_TRX_CONTRACTS trx, OKL_REPRESENTATIONS_V rep
2729 WHERE trx.tcn_type = 'ACL'
2730 AND trx.khr_id = p_chr_id
2731 AND trx.tsu_code = 'PROCESSED'
2732 AND trx.representation_code = rep.representation_code
2733 AND rep.representation_type='PRIMARY'
2734 AND trx.date_transaction_occurred = (SELECT MAX(tcn.date_transaction_occurred)
2735 FROM OKL_TRX_CONTRACTS tcn
2736 WHERE tcn.khr_id = trx.khr_id
2737 AND tcn.tcn_type = trx.tcn_type
2738 AND tcn.tsu_code = trx.tsu_code)
2739 AND trx.trx_number = (select to_char(max(to_number(t.trx_number)))
2740 from okl_trx_contracts t
2741 where t.khr_id = trx.khr_id
2742 and t.tsu_code=trx.tsu_code
2743 and t.tcn_type=trx.tcn_type);
2744
2745
2746 l_accrual_status OKL_TRX_CONTRACTS.accrual_status_yn%TYPE := NULL;
2747
2748 BEGIN
2749
2750 IF (p_chr_id IS NULL) THEN
2751 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2752 END IF;
2753
2754
2755 OPEN accrual_status_csr (p_chr_id );
2756 FETCH accrual_status_csr INTO l_accrual_status;
2757 CLOSE accrual_status_csr;
2758
2759 IF (l_accrual_status IS NULL or l_accrual_status = 'Y') THEN
2760 l_accrual_status := 'YES';
2761 ELSIF l_accrual_status = 'N' THEN
2762 l_accrual_status := 'NO';
2763 END IF;
2764
2765
2766 RETURN l_accrual_status;
2767
2768
2769 EXCEPTION
2770
2771 WHEN OTHERS THEN
2772 IF accrual_status_csr%ISOPEN THEN
2773 CLOSE accrual_status_csr;
2774 END IF;
2775
2776 Okl_Api.SET_MESSAGE(p_app_name => G_APP_NAME,
2777 p_msg_name => G_UNEXPECTED_ERROR,
2778 p_token1 => G_SQLCODE_TOKEN,
2779 p_token1_value => SQLCODE,
2780 p_token2 => G_SQLERRM_TOKEN,
2781 p_token2_value => SQLERRM);
2782 RETURN 'E';
2783
2784 END accrual_status_yn;
2785
2786
2787
2788 ----------------------------------------------------------------------------------------------------
2789
2790
2791 FUNCTION delete_report_data return BOOLEAN
2792
2793 IS
2794 l_row_count NUMBER;
2795 l_parent_request_id NUMBER := Fnd_Global.conc_priority_request; --Request ID of Master program
2796 BEGIN
2797 fnd_file.put_line(fnd_file.log,'P_DELETE_DATA_YN :'||P_DELETE_DATA_YN);
2798 fnd_file.put_line(fnd_file.log,'l_parent_request_id :'||l_parent_request_id);
2799
2800 IF P_DELETE_DATA_YN = 'YES' THEN
2801
2802 DELETE FROM OKL_CNTRCT_FIN_EXTRACT_T
2803 WHERE REQUEST_ID = l_parent_request_id;
2804
2805 l_row_count := sql%rowcount;
2806
2807 If l_row_count > 0 then
2808 write_to_log('Purged '||l_row_count||' rows from OKL_CNTRCT_FIN_EXTRACT_T after the report was rendered.');
2809 end if;
2810
2811 COMMIT;
2812
2813 END IF;
2814
2815 RETURN TRUE;
2816
2817 END delete_report_data;
2818
2819 END okl_cntrct_fin_extract_pvt;