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