[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| This procedure fetches data for Contract Financial Report and inserts
| into okl_cntrct_fin_extract_t
|
| CALLED FROM
| Concurrent Program "Child Program -- Contract Financial Report"
|
| CALLS PROCEDURES/FUNCTIONS
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 18-Aug-2008 DJANASWA Created
| 22-Sep-2008 SECHAWLA Enabled for parallel processing
| 11-Dec-2008 RBANERJE Bug7589271: Switched seeded
| function calls for scheduled and
| actual principal balance
|
*=======================================================================*/
PROCEDURE pull_extract_data (
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2,
x_row_count OUT NOCOPY VARCHAR2,
P_OPERATING_UNIT IN NUMBER,
-- P_REPORT_DATE IN VARCHAR2, sechawla 25-sep-09 8890513
P_START_DATE_FROM IN VARCHAR2,
P_START_DATE_TO IN VARCHAR2,
P_AR_INFO_YN IN VARCHAR2,
P_BOOK_CLASS IN VARCHAR2,
P_LEASE_PRODUCT IN VARCHAR2,
P_CONTRACT_STATUS IN VARCHAR2,
P_CUSTOMER_NUMBER IN VARCHAR2,
P_CUSTOMER_NAME IN VARCHAR2,
P_SIC_CODE IN VARCHAR2,
P_VENDOR_NUMBER IN VARCHAR2,
P_VENDOR_NAME IN VARCHAR2,
P_SALES_CHANNEL IN VARCHAR2,
P_GEN_ACCRUAL IN VARCHAR2,
P_END_DATE_FROM IN VARCHAR2,
P_END_DATE_TO IN VARCHAR2,
P_TERMINATE_DATE_FROM IN DATE,
P_TERMINATE_DATE_TO IN DATE,
P_DELETE_DATA_YN IN VARCHAR2,
p_num_processes IN NUMBER,
p_assigned_process IN VARCHAR2
) IS
l_last_updated_by NUMBER := Fnd_Global.USER_ID;
l_last_update_login NUMBER := Fnd_Global.LOGIN_ID;
write_to_log('P_DELETE_DATA_YN '||P_DELETE_DATA_YN);
INSERT INTO OKL_CNTRCT_FIN_EXTRACT_T
(
REQUEST_ID,
CONTRACT_DOCUMENT_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_INVENTORY_ORG_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_TEMPLATE_INDICATOR,
CONTRACT_CUST_PURCH_ORDER_NUM,
CONTRACT_TEMPLATE_NAME,
CONTRACT_APPROVED_DATE,
CONTRACT_CANCELED_TIMESTAMP,
CONTRACT_SIGNED_DATE,
CONTRACT_TERMINATION_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CUSTOMER_ACCOUNT_ID,
CUST_BILL_TO_SITE_USE_ID,
CUST_PAYMENT_TERM_ID,
CONTRACT_ORIG_SYSTEM_SOURCE,
CONTRACT_ID,
CONTRACT_SALES_REGION_ID,
VENDOR_PROGRAM_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_DATE_DEAL_ACTIVATED,
CONTRACT_DATE_DEAL_TRANSFERD,
CONTRACT_TERM_DURATION,
CONTRACT_HDR_DFF_CATEGORY,
CONTRACT_HDR_DFF1,
CONTRACT_HDR_DFF2,
CONTRACT_HDR_DFF3,
CONTRACT_HDR_DFF4,
CONTRACT_HDR_DFF5,
CONTRACT_HDR_DFF6,
CONTRACT_HDR_DFF7,
CONTRACT_HDR_DFF8,
CONTRACT_HDR_DFF9,
CONTRACT_HDR_DFF10,
CONTRACT_HDR_DFF11,
CONTRACT_HDR_DFF12,
CONTRACT_HDR_DFF13,
CONTRACT_HDR_DFF14,
CONTRACT_HDR_DFF15,
CONTRACT_ACCRUAL_STATUS,
CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
CONTRACT_CREDIT_ACT_YN,
CONTRACT_CONVERTED_ACCOUNT_YN,
CONTRACT_AFTER_TAX_YIELD,
CONTRACT_IMPL_INTEREST_RATE,
CONTRACT_LAST_INT_CALC_DATE,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_PRE_TAX_IRR,
CONTRACT_AFTER_TAX_IRR,
CONTRACT_EXP_DELIVERY_DATE,
CONTRACT_ACCEPTANCE_DATE,
CONTRACT_PREFUND_ELIG_YN,
CONTRACT_REVOL_CREDIT_ELIG_YN,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_MULTI_GAAP_ELIG_YN,
CONTRACT_INVESTOR_ASSIGN_YN,
CONTRACT_SECURITIZATION_TYPE,
CONTRACT_SUB_AFTER_TAX_YIELD,
CONTRACT_SUB_IMPL_INT_RATE,
CONTRACT_SUB_PRE_TAX_IRR,
CONTRACT_SUB_AFTER_TAX_IRR,
CONTRACT_CRDTLINE_TRNSFR_AMT,
CONTRACT_CRDTLINE_NETRNSFR_AMT,
CONTRACT_CRDTLINE_LIMIT,
CONTRACT_CRDTLINE_FUNDING_AMT,
CONTRACT_TEMPL_TYPE_CODE,
CONTRACT_TRADEIN_DESCRIPTION,
CONTRACT_TRADEIN_DATE,
CONTRACT_LEGAL_ENTITY_ID,
CONTRACT_FIRST_ACTIVITY_DATE,
-- CONTRACT_SALESTYPE_YN,
--formulas
CONTRACT_ACCUMULATED_DEP,
CONTRACT_CAPITALIZED_REDUCTION,
CONTRACT_CAPITALIZED_FEE,
CONTRACT_CAPITALIZED_INTEREST,
CONTRACT_DISCOUNT,
CONTRACT_ASSET_COST,
CONTRACT_FINANCED_FEE,
CONTRACT_NEXT_PAYMENT_AMT,
CONTRACT_TOTAL_ACCRUED_INT,
CONTRACT_TOTAL_ACTUAL_INT,
CONTRACT_SUBSIDY_AMOUNT,
CONTRACT_UNACCRUED_SUBSIDY,
CONTRACT_UNBILLED_DUE_AMOUNT,
CONTRACT_UNBILLED_RECEIVABLES,
CONTRACT_OEC,
CONTRACT_ACTUAL_PRINC_BAL,
ROLLOVER_FEE_AMOUNT,
CONTRACT_TRADEIN_AMOUNT,
TOTAL_AMOUNT_PAID_TO_DEALER,
TOTAL_DEBITS_FOR_PREFUNDING,
CONTRACT_PREFUNDED_AMOUNT,
CONTRACT_BILLED_RENTS,
CONTRACT_BILLED_RENTS_FUNC,
CONTRACT_BILLED_NON_RENTS,
CONTRACT_BILLED_NON_RENTS_FUNC,
SUPPLIER_RETENTION_FUNDING_AMT,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
CONTRACT_IDC_AMOUNT,
CONTRACT_SCHEDULED_PRINC_BAL,
CONTRACT_INTEREST_RATE,
CONTRACT_DAYS_TO_ACCRUE,
CONTRACT_DAYS_IN_YEAR,
CONTRACT_CAP_AMOUNT,
CONTRACT_FINANCED_AMOUNT,
CONTRACT_VAR_INCOME_ACCRUAL,
TOTAL_PREFUNDING_AMOUNT,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FEE_INCOME,
ACC_ENGINE_TEMPL_SET_ID,
CONTRACT_FIN_PROD_TEMPLATE_ID,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_FIN_PROD_DESC,
REPORTING_PRODUCT_ID,
CONTRACT_FIN_PROD_STATUS_CODE,
CONTRACT_FIN_PROD_EFF_FROM_DT,
CONTRACT_FIN_PROD_EFF_TO_DT,
REPORTING_PRODUCT,
VENDOR_PROGRAM_NAME,
VENDOR_NUMBER,
VENDOR_NAME,
VENDOR_TYPE_CODE,
VENDOR_BILL_TO_SITE_USE_ID,
VENDOR_PARTY_ROLE,
TOTAL_UPFRONT_TAX_ON_ASSETS,
TOTAL_NUMBER_OF_ASSETS,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
CONTRACT_EARLIEST_BILL_DATE,
CONTRACT_SALES_REP,
LEGAL_ENTITY,
CUSTOMER_PARTY_NUMBER,
CUSTOMER_PARTY_NAME,
CUSTOMER_PARTY_TYPE,
CUSTOMER_SIC_CODE,
CUSTOMER_HQ_BRANCH_IND,
CUSTOMER_TAX_REFERENCE,
CUSTOMER_DUNS_NUMBER,
CUSTOMER_ALIAS,
CUSTOMER_COUNTRY,
CUSTOMER_ADDRESS1,
CUSTOMER_ADDRESS2,
CUSTOMER_ADDRESS3,
CUSTOMER_ADDRESS4,
CUSTOMER_CITY,
CUSTOMER_POSTAL_CODE,
CUSTOMER_STATE,
CUSTOMER_PROVINCE,
CUSTOMER_COUNTY,
CUSTOMER_SIC_CODE_TYPE,
CUSTOMER_URL,
FINANCIAL_INFO_FISCAL_YEAR,
CUSTOMER_FISCAL_YEAREND_MONTH,
TOTAL_NUM_OF_EMPLOYEES,
CURR_FISC_YEAR_POTENTIAL_REV,
NEXT_FISC_YEAR_POTENTIAL_REV,
CUSTOMER_YEAR_ESTABLISHED,
GEN_SERV_ADMIN_INDICATOR,
CUSTOMER_ORG_NAME_PHONETIC,
CUSTOMER_COMPETITOR_YN,
CUSTOMER_ALIAS2,
CUSTOMER_ALIAS3,
CUSTOMER_ALIAS4,
CUSTOMER_ALIAS5,
CUSTOMER_PRIM_PHONE_PURPOSE,
CUSTOMER_PRIM_PHONE_TYPE,
CUSTOMER_PRIM_PH_COUNTRY_CODE,
CUSTOMER_PRIM_PH_AREA_CODE,
CUSTOMER_PRIM_PHONE_NUMBER,
CUSTOMER_PRIM_PHONE_EXTN,
CUSTOMER_HOME_COUNTRY,
CUSTOMER_STATUS,
CUSTOMER_PARTY_ID,
CUSTOMER_ACCOUNT_NUMBER,
CUSTOMER_ACC_DFF_CATEGORY,
CUSTOMER_ACC_DFF1,
CUSTOMER_ACC_DFF2,
CUSTOMER_ACC_DFF3,
CUSTOMER_ACC_DFF4,
CUSTOMER_ACC_DFF5,
CUSTOMER_ACC_DFF6,
CUSTOMER_ACC_DFF7,
CUSTOMER_ACC_DFF8,
CUSTOMER_ACC_DFF9,
CUSTOMER_ACC_DFF10,
CUSTOMER_ACC_DFF11,
CUSTOMER_ACC_DFF12,
CUSTOMER_ACC_DFF13,
CUSTOMER_ACC_DFF14,
CUSTOMER_ACC_DFF15,
CUSTOMER_ACC_DFF16,
CUSTOMER_ACC_DFF17,
CUSTOMER_ACC_DFF18,
CUSTOMER_ACC_DFF19,
CUSTOMER_ACC_DFF20,
CUSTOMER_ACCOUNT_STATUS,
CUSTOMER_TYPE,
CUSTOMER_SALES_CHANNEL,
CUSTOMER_ACC_ESTAB_DATE,
CUSTOMER_ACCOUNT_NAME,
CUSTOMER_ACCOUNT_COMMENTS,
CUSTOMER_LAST_STATUS_UPD_DT,
CUSTOMER_PARTY_SITE_ID,
CUSTOMER_PARTY_SITE_NUMBER,
CUSTOMER_PARTY_SITE_NAME,
CUSTOMER_PARTY_SITE_LOC_ID,
CUSTOMER_IDENTIFYING_ADR_FLAG,
CUSTOMER_PARTY_SITE_STATUS,
CUSTOMER_PARTY_SITE_ADDRESSEE,
CUSTOMER_SITE_GLOB_LOC_NUM,
CUSTOMER_PARTY_SITE_USE_ID,
CUSTOMER_PARTY_SITE_USE_TYPE,
CUSTOMER_PARTY_SITE_COMMENTS,
CUSTOMER_PARTY_SITE_USE_STATUS,
CONTRACT_PAST_DUE_AMT_1_30,
CONTRACT_PAST_DUE_AMT_31_60,
CONTRACT_PAST_DUE_AMT_61_90,
CONTRACT_PAST_DUE_AMT_91_120,
CONTRACT_PAST_DUE_AMT_120_Plus,
CONTRACT_TOT_PAST_DUE_AMT,
CONTRACT_TOT_BILLED_RECEIVABLE,
CONTRACT_RESIDUAL_AMOUNT,
CONTRACT_RENT_AMOUNT,
CONTRACT_ACC_DEPRECIATION,
NET_INVESTMENT,
NET_INVESTMENT_FUNCTIONAL,
CONTRACT_AMOUNT_PREFUNDED,
CONTRACT_TOTAL_MISC_FUND,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_UPDATE_DATE
)
select
REQUEST_ID,
CONTRACT_DOCUMENT_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_INVENTORY_ORG_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_TEMPLATE_INDICATOR,
CONTRACT_CUST_PURCH_ORDER_NUM,
CONTRACT_TEMPLATE_NAME,
CONTRACT_APPROVED_DATE,
CONTRACT_CANCELED_TIMESTAMP,
CONTRACT_SIGNED_DATE,
CONTRACT_TERMINATION_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CUSTOMER_ACCOUNT_ID,
CUST_BILL_TO_SITE_USE_ID,
CUST_PAYMENT_TERM_ID,
CONTRACT_ORIG_SYSTEM_SOURCE,
CONTRACT_ID,
CONTRACT_SALES_REGION_ID,
VENDOR_PROGRAM_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_DATE_DEAL_ACTIVATED,
CONTRACT_DATE_DEAL_TRANSFERD,
CONTRACT_TERM_DURATION,
CONTRACT_HDR_DFF_CATEGORY,
CONTRACT_HDR_DFF1,
CONTRACT_HDR_DFF2,
CONTRACT_HDR_DFF3,
CONTRACT_HDR_DFF4,
CONTRACT_HDR_DFF5,
CONTRACT_HDR_DFF6,
CONTRACT_HDR_DFF7,
CONTRACT_HDR_DFF8,
CONTRACT_HDR_DFF9,
CONTRACT_HDR_DFF10,
CONTRACT_HDR_DFF11,
CONTRACT_HDR_DFF12,
CONTRACT_HDR_DFF13,
CONTRACT_HDR_DFF14,
CONTRACT_HDR_DFF15,
CONTRACT_ACCRUAL_STATUS,
CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
CONTRACT_CREDIT_ACT_YN,
CONTRACT_CONVERTED_ACCOUNT_YN,
CONTRACT_AFTER_TAX_YIELD,
CONTRACT_IMPL_INTEREST_RATE,
CONTRACT_LAST_INT_CALC_DATE,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_PRE_TAX_IRR,
CONTRACT_AFTER_TAX_IRR,
CONTRACT_EXP_DELIVERY_DATE,
CONTRACT_ACCEPTANCE_DATE,
CONTRACT_PREFUND_ELIG_YN,
CONTRACT_REVOL_CREDIT_ELIG_YN,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_MULTI_GAAP_ELIG_YN,
CONTRACT_INVESTOR_ASSIGN_YN,
CONTRACT_SECURITIZATION_TYPE,
CONTRACT_SUB_AFTER_TAX_YIELD,
CONTRACT_SUB_IMPL_INT_RATE,
CONTRACT_SUB_PRE_TAX_IRR,
CONTRACT_SUB_AFTER_TAX_IRR,
CONTRACT_CRDTLINE_TRNSFR_AMT,
CONTRACT_CRDTLINE_NETRNSFR_AMT,
CONTRACT_CRDTLINE_LIMIT,
CONTRACT_CRDTLINE_FUNDING_AMT,
CONTRACT_TEMPL_TYPE_CODE,
CONTRACT_TRADEIN_DESCRIPTION,
CONTRACT_TRADEIN_DATE,
CONTRACT_LEGAL_ENTITY_ID,
CONTRACT_FIRST_ACTIVITY_DATE,
--formulas
CONTRACT_ACCUMULATED_DEP,
CONTRACT_CAPITALIZED_REDUCTION,
CONTRACT_CAPITALIZED_FEE,
CONTRACT_CAPITALIZED_INTEREST,
CONTRACT_DISCOUNT,
CONTRACT_ASSET_COST,
CONTRACT_FINANCED_FEE,
CONTRACT_NEXT_PAYMENT_AMT,
CONTRACT_TOTAL_ACCRUED_INT,
CONTRACT_TOTAL_ACTUAL_INT,
CONTRACT_SUBSIDY_AMOUNT,
CONTRACT_UNACCRUED_SUBSIDY,
CONTRACT_UNBILLED_DUE_AMOUNT,
CONTRACT_UNBILLED_RECEIVABLES,
CONTRACT_OEC,
CONTRACT_ACTUAL_PRINC_BAL,
ROLLOVER_FEE_AMOUNT,
CONTRACT_TRADEIN_AMOUNT,
TOTAL_AMOUNT_PAID_TO_DEALER,
TOTAL_DEBITS_FOR_PREFUNDING,
CONTRACT_PREFUNDED_AMOUNT,
CONTRACT_BILLED_RENTS,
(CONTRACT_BILLED_RENTS * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_RENTS_FUNC,
(CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) CONTRACT_BILLED_NON_RENTS,
((CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_NON_RENTS_FUNC,
SUPPLIER_RETENTION_FUNDING_AMT,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
CONTRACT_IDC_AMOUNT,
CONTRACT_SCHEDULED_PRINC_BAL,
CONTRACT_INTEREST_RATE,
CONTRACT_DAYS_TO_ACCRUE,
CONTRACT_DAYS_IN_YEAR,
(CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+CONTRACT_CAPITALIZED_INTEREST) CONTRACT_CAP_AMOUNT,
(CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+ CONTRACT_CAPITALIZED_INTEREST+CONTRACT_FINANCED_FEE) CONTRACT_FINANCED_AMOUNT,
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,
(CONTRACT_PREFUNDED_AMOUNT + TOTAL_DEBITS_FOR_PREFUNDING) TOTAL_PREFUNDING_AMOUNT,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FEE_INCOME,
ACC_ENGINE_TEMPL_SET_ID ,
CONTRACT_FIN_PROD_TEMPLATE_ID ,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_FIN_PROD_DESC ,
REPORTING_PRODUCT_ID ,
CONTRACT_FIN_PROD_STATUS_CODE ,
CONTRACT_FIN_PROD_EFF_FROM_DT ,
CONTRACT_FIN_PROD_EFF_TO_DT ,
REPORTING_PRODUCT,
VENDOR_PROGRAM_NAME,
VENDOR_NUMBER,
VENDOR_NAME,
VENDOR_TYPE_CODE,
VENDOR_BILL_TO_SITE_USE_ID,
VENDOR_PARTY_ROLE,
TOTAL_UPFRONT_TAX_ON_ASSETS,
TOTAL_NUMBER_OF_ASSETS,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
CONTRACT_EARLIEST_BILL_DATE,
CONTRACT_SALES_REP,
LEGAL_ENTITY,
CUSTOMER_PARTY_NUMBER,
CUSTOMER_PARTY_NAME,
CUSTOMER_PARTY_TYPE,
CUSTOMER_SIC_CODE,
CUSTOMER_HQ_BRANCH_IND,
CUSTOMER_TAX_REFERENCE,
CUSTOMER_DUNS_NUMBER,
CUSTOMER_ALIAS,
CUSTOMER_COUNTRY,
CUSTOMER_ADDRESS1,
CUSTOMER_ADDRESS2,
CUSTOMER_ADDRESS3,
CUSTOMER_ADDRESS4,
CUSTOMER_CITY,
CUSTOMER_POSTAL_CODE,
CUSTOMER_STATE,
CUSTOMER_PROVINCE,
CUSTOMER_COUNTY,
CUSTOMER_SIC_CODE_TYPE,
CUSTOMER_URL,
FINANCIAL_INFO_FISCAL_YEAR,
CUSTOMER_FISCAL_YEAREND_MONTH,
TOTAL_NUM_OF_EMPLOYEES,
CURR_FISC_YEAR_POTENTIAL_REV,
NEXT_FISC_YEAR_POTENTIAL_REV,
CUSTOMER_YEAR_ESTABLISHED,
GEN_SERV_ADMIN_INDICATOR,
CUSTOMER_ORG_NAME_PHONETIC,
CUSTOMER_COMPETITOR_YN,
CUSTOMER_ALIAS2,
CUSTOMER_ALIAS3,
CUSTOMER_ALIAS4,
CUSTOMER_ALIAS5,
CUSTOMER_PRIM_PHONE_PURPOSE,
CUSTOMER_PRIM_PHONE_TYPE,
CUSTOMER_PRIM_PH_COUNTRY_CODE,
CUSTOMER_PRIM_PH_AREA_CODE,
CUSTOMER_PRIM_PHONE_NUMBER,
CUSTOMER_PRIM_PHONE_EXTN,
CUSTOMER_HOME_COUNTRY,
CUSTOMER_STATUS,
CUSTOMER_PARTY_ID,
CUSTOMER_ACCOUNT_NUMBER,
CUSTOMER_ACC_DFF_CATEGORY,
CUSTOMER_ACC_DFF1,
CUSTOMER_ACC_DFF2,
CUSTOMER_ACC_DFF3,
CUSTOMER_ACC_DFF4,
CUSTOMER_ACC_DFF5,
CUSTOMER_ACC_DFF6,
CUSTOMER_ACC_DFF7,
CUSTOMER_ACC_DFF8,
CUSTOMER_ACC_DFF9,
CUSTOMER_ACC_DFF10,
CUSTOMER_ACC_DFF11,
CUSTOMER_ACC_DFF12,
CUSTOMER_ACC_DFF13,
CUSTOMER_ACC_DFF14,
CUSTOMER_ACC_DFF15,
CUSTOMER_ACC_DFF16,
CUSTOMER_ACC_DFF17,
CUSTOMER_ACC_DFF18,
CUSTOMER_ACC_DFF19,
CUSTOMER_ACC_DFF20,
CUSTOMER_ACCOUNT_STATUS,
CUSTOMER_TYPE,
CUSTOMER_SALES_CHANNEL,
CUSTOMER_ACC_ESTAB_DATE,
CUSTOMER_ACCOUNT_NAME,
CUSTOMER_ACCOUNT_COMMENTS,
CUSTOMER_LAST_STATUS_UPD_DT,
CUSTOMER_PARTY_SITE_ID,
CUSTOMER_PARTY_SITE_NUMBER,
CUSTOMER_PARTY_SITE_NAME,
CUSTOMER_PARTY_SITE_LOC_ID,
CUSTOMER_IDENTIFYING_ADR_FLAG,
CUSTOMER_PARTY_SITE_STATUS,
CUSTOMER_PARTY_SITE_ADDRESSEE,
CUSTOMER_SITE_GLOB_LOC_NUM,
CUSTOMER_PARTY_SITE_USE_ID,
CUSTOMER_PARTY_SITE_USE_TYPE,
CUSTOMER_PARTY_SITE_COMMENTS,
CUSTOMER_PARTY_SITE_USE_STATUS,
CONTRACT_PAST_DUE_AMT_1_30,
CONTRACT_PAST_DUE_AMT_31_60,
CONTRACT_PAST_DUE_AMT_61_90,
CONTRACT_PAST_DUE_AMT_91_120,
CONTRACT_PAST_DUE_AMT_120_Plus,
NVL( (CONTRACT_PAST_DUE_AMT_1_30 + CONTRACT_PAST_DUE_AMT_31_60 +
CONTRACT_PAST_DUE_AMT_61_90 + CONTRACT_PAST_DUE_AMT_91_120 +
CONTRACT_PAST_DUE_AMT_120_Plus),0) CONTRACT_TOT_PAST_DUE_AMT,
CONTRACT_TOT_BILLED_RECEIVABLE,
CONTRACT_RESIDUAL_AMOUNT,
CONTRACT_RENT_AMOUNT,
CONTRACT_ACC_DEPRECIATION,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
'LOAN', CONTRACT_ACTUAL_PRINC_BAL,
'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL,
'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION + CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT),
0))),0) NET_INVESTMENT,
DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION +
CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
0))) NET_INVESTMENT_FUNCTIONAL,
(CONTRACT_AMOUNT_PREFUNDED + CONTRACT_FUNDED_ADJS ) CONTRACT_AMOUNT_PREFUNDED,
CONTRACT_AMOUNT_MANU_DISB CONTRACT_TOTAL_MISC_FUND,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_UPDATE_DATE
from (
-- create table okl_fin_extract5 as
SELECT
--l_request_id REQUEST_ID,
l_parent_request_id REQUEST_ID,
chr.DOCUMENT_ID CONTRACT_DOCUMENT_ID,
chr.CONTRACT_NUMBER CONTRACT_NUMBER,
chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
chr.INV_ORGANIZATION_ID CONTRACT_INVENTORY_ORG_ID,
chr.STS_CODE CONTRACT_STATUS_CODE,
chr.CURRENCY_CODE CONTRACT_CURRENCY,
chr.TEMPLATE_YN CONTRACT_TEMPLATE_INDICATOR,
chr.CUST_PO_NUMBER CONTRACT_CUST_PURCH_ORDER_NUM,
chr.TEMPLATE_USED CONTRACT_TEMPLATE_NAME,
chr.DATE_APPROVED CONTRACT_APPROVED_DATE,
chr.DATETIME_CANCELLED CONTRACT_CANCELED_TIMESTAMP,
chr.DATE_SIGNED CONTRACT_SIGNED_DATE,
chr.DATE_TERMINATED CONTRACT_TERMINATION_DATE,
chr.START_DATE CONTRACT_START_DATE,
chr.END_DATE CONTRACT_END_DATE,
chr.CUST_ACCT_ID CUSTOMER_ACCOUNT_ID,
chr.BILL_TO_SITE_USE_ID CUST_BILL_TO_SITE_USE_ID,
chr.PAYMENT_TERM_ID CUST_PAYMENT_TERM_ID,
chr.ORIG_SYSTEM_SOURCE_CODE CONTRACT_ORIG_SYSTEM_SOURCE,
khr.ID CONTRACT_ID,
khr.ISG_ID CONTRACT_SALES_REGION_ID,
khr.KHR_ID VENDOR_PROGRAM_ID,
khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
--khr.DATE_CONVERSION_EFFECTIVE CONTRACT_DATE_DEAL_ACTIVATED,
OKL_CNTRCT_FIN_EXTRACT_PVT.first_activation_date(khr.id) CONTRACT_DATE_DEAL_ACTIVATED,
khr.DATE_DEAL_TRANSFERRED CONTRACT_DATE_DEAL_TRANSFERD,
khr.TERM_DURATION CONTRACT_TERM_DURATION,
khr.ATTRIBUTE_CATEGORY CONTRACT_HDR_DFF_CATEGORY,
khr.ATTRIBUTE1 CONTRACT_HDR_DFF1,
khr.ATTRIBUTE2 CONTRACT_HDR_DFF2,
khr.ATTRIBUTE3 CONTRACT_HDR_DFF3,
khr.ATTRIBUTE4 CONTRACT_HDR_DFF4,
khr.ATTRIBUTE5 CONTRACT_HDR_DFF5,
khr.ATTRIBUTE6 CONTRACT_HDR_DFF6,
khr.ATTRIBUTE7 CONTRACT_HDR_DFF7,
khr.ATTRIBUTE8 CONTRACT_HDR_DFF8,
khr.ATTRIBUTE9 CONTRACT_HDR_DFF9,
khr.ATTRIBUTE10 CONTRACT_HDR_DFF10,
khr.ATTRIBUTE11 CONTRACT_HDR_DFF11,
khr.ATTRIBUTE12 CONTRACT_HDR_DFF12,
khr.ATTRIBUTE13 CONTRACT_HDR_DFF13,
khr.ATTRIBUTE14 CONTRACT_HDR_DFF14,
khr.ATTRIBUTE15 CONTRACT_HDR_DFF15,
OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id) CONTRACT_ACCRUAL_STATUS,
khr.GENERATE_ACCRUAL_OVERRIDE_YN CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
khr.CREDIT_ACT_YN CONTRACT_CREDIT_ACT_YN,
khr.CONVERTED_ACCOUNT_YN CONTRACT_CONVERTED_ACCOUNT_YN,
khr.AFTER_TAX_YIELD CONTRACT_AFTER_TAX_YIELD,
khr.IMPLICIT_INTEREST_RATE CONTRACT_IMPL_INTEREST_RATE,
khr.DATE_LAST_INTERIM_INTEREST_CAL CONTRACT_LAST_INT_CALC_DATE,
khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
khr.PRE_TAX_IRR CONTRACT_PRE_TAX_IRR,
khr.AFTER_TAX_IRR CONTRACT_AFTER_TAX_IRR,
khr.EXPECTED_DELIVERY_DATE CONTRACT_EXP_DELIVERY_DATE,
khr.ACCEPTED_DATE CONTRACT_ACCEPTANCE_DATE,
khr.PREFUNDING_ELIGIBLE_YN CONTRACT_PREFUND_ELIG_YN,
khr.REVOLVING_CREDIT_YN CONTRACT_REVOL_CREDIT_ELIG_YN,
khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
khr.MULTI_GAAP_YN CONTRACT_MULTI_GAAP_ELIG_YN ,
khr.ASSIGNABLE_YN CONTRACT_INVESTOR_ASSIGN_YN ,
khr.SECURITIZATION_TYPE CONTRACT_SECURITIZATION_TYPE,
khr.SUB_AFTER_TAX_YIELD CONTRACT_SUB_AFTER_TAX_YIELD,
khr.SUB_IMPL_INTEREST_RATE CONTRACT_SUB_IMPL_INT_RATE,
khr.SUB_PRE_TAX_IRR CONTRACT_SUB_PRE_TAX_IRR,
khr.SUB_AFTER_TAX_IRR CONTRACT_SUB_AFTER_TAX_IRR,
khr.TOT_CL_TRANSFER_AMT CONTRACT_CRDTLINE_TRNSFR_AMT,
khr.TOT_CL_NET_TRANSFER_AMT CONTRACT_CRDTLINE_NETRNSFR_AMT,
khr.TOT_CL_LIMIT CONTRACT_CRDTLINE_LIMIT,
khr.TOT_CL_FUNDING_AMT CONTRACT_CRDTLINE_FUNDING_AMT,
khr.TEMPLATE_TYPE_CODE CONTRACT_TEMPL_TYPE_CODE,
khr.TRADEIN_DESCRIPTION CONTRACT_TRADEIN_DESCRIPTION,
khr.TRADEIN_AMOUNT CONTRACT_TRADEIN_AMOUNT,
khr.DATE_TRADEIN CONTRACT_TRADEIN_DATE,
khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
khr.DATE_FIRST_ACTIVITY CONTRACT_FIRST_ACTIVITY_DATE,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACCUMULATED_DEPRN(chr.id, null),0) CONTRACT_ACCUMULATED_DEP,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_CAPITAL_REDUCTION(chr.id, null),0) CONTRACT_CAPITALIZED_REDUCTION,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FEES_CAPITALIZED(chr.id, null),0) CONTRACT_CAPITALIZED_FEE,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_capitalized_interest(chr.id, null),0) CONTRACT_CAPITALIZED_INTEREST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DISCOUNT(chr.id, null),0) CONTRACT_DISCOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ASSET_COST(chr.id, null),0) CONTRACT_ASSET_COST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FINANCED_FEE(chr.id, null),0) CONTRACT_FINANCED_FEE,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_NEXT_PAYMENT_AMOUNT(chr.id, null),0) CONTRACT_NEXT_PAYMENT_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACCRUED_INT(chr.id, null),0) CONTRACT_TOTAL_ACCRUED_INT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACTUAL_INT(chr.id, null),0) CONTRACT_TOTAL_ACTUAL_INT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_asset_subsidy_amount(chr.id, null),0) CONTRACT_SUBSIDY_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNACCRUED_SUBSIDY(chr.id, null),0) CONTRACT_UNACCRUED_SUBSIDY,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_STREAMS(chr.id, null),0) CONTRACT_UNBILLED_DUE_AMOUNT,
DECODE(khr.deal_type,'LOAN',NULL,'LOAN-REVOLVING',NULL,NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_RECEIVABLES(chr.id, null),0)) CONTRACT_UNBILLED_RECEIVABLES,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_OEC(chr.id, null),0) CONTRACT_OEC,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(chr.id, null),0) CONTRACT_ACTUAL_PRINC_BAL, -- addl para
NVL(OKL_SEEDED_FUNCTIONS_PVT.ROLLOVER_FEE(chr.id, null),0) ROLLOVER_FEE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_funded(chr.id, null),0) TOTAL_AMOUNT_PAID_TO_DEALER,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_debits(chr.id, null),0) TOTAL_DEBITS_FOR_PREFUNDING,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_amount_prefunded(chr.id, null),0) CONTRACT_PREFUNDED_AMOUNT,
-- null CONTRACT_BILLED_RENTS,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd,
okl_strm_type_b sty
where txd.khr_id = khr.id
and txd.sty_id = sty.id
and sty.STREAM_TYPE_PURPOSE = 'RENT') CONTRACT_BILLED_RENTS,
NVL(OKL_FUNDING_PVT.get_total_retention(chr.id),0) SUPPLIER_RETENTION_FUNDING_AMT,
NVL(OKL_FUNDING_PVT.get_chr_oec_hasbeen_funded_amt(chr.id),0) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_chr_exp_hasbeen_funded_amt(chr.id),0) EXPENSE_FUNDED_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.FEE_IDC_AMOUNT(chr.id, null),0) CONTRACT_IDC_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRIN_BALANCE(chr.id, null),0) CONTRACT_SCHEDULED_PRINC_BAL,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_INTEREST_RATE(chr.id, null),0) CONTRACT_INTEREST_RATE, -- addl para
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_TO_ACCRUE(chr.id, null),0) CONTRACT_DAYS_TO_ACCRUE, -- addl para
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_IN_YEAR(chr.id, null),0) CONTRACT_DAYS_IN_YEAR, -- addl para
hr_org.name OPERATING_UNIT,
hr_org.short_code OPERATING_UNIT_SHORT_CODE,
status.ste_code CONTRACT_STATUS_TYPE_CODE,
status_tl.meaning CONTRACT_STATUS,
(select nvl(sum(nvl(kle_fee.amount,0)),0)
FROM okl_k_lines kle_fee, okc_k_lines_b cleb_fee, OKC_STATUSES_B status_fee
WHERE cleb_fee.chr_id = khr.id
AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
and cleb_fee.dnz_chr_id = khr.id
and cleb_fee.sts_code = status_fee.code
and status_fee.ste_code not like 'CANCELLED'
AND kle_fee.fee_type NOT IN ('FINANCED', 'ROLLOVER','SECURITY_DEPOSIT')
and kle_fee.id = cleb_fee.id
and cleb_fee.lse_id = 52) CONTRACT_FEE_INCOME,
pdt.AES_ID ACC_ENGINE_TEMPL_SET_ID ,
pdt.PTL_ID CONTRACT_FIN_PROD_TEMPLATE_ID ,
pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
pdt.DESCRIPTION CONTRACT_FIN_PROD_DESC ,
pdt.REPORTING_PDT_ID REPORTING_PRODUCT_ID ,
pdt.PRODUCT_STATUS_CODE CONTRACT_FIN_PROD_STATUS_CODE ,
pdt.FROM_DATE CONTRACT_FIN_PROD_EFF_FROM_DT ,
pdt.TO_DATE CONTRACT_FIN_PROD_EFF_TO_DT ,
rpt_pdt.name REPORTING_PRODUCT,
vendor_chr.contract_number VENDOR_PROGRAM_NAME,
vendor.segment1 VENDOR_NUMBER,
vendor.vendor_name VENDOR_NAME,
vendor.vendor_type_lookup_code VENDOR_TYPE_CODE,
CPL.bill_to_site_use_id VENDOR_BILL_TO_SITE_USE_ID,
cpl.role VENDOR_PARTY_ROLE,
(select nvl(SUM(NVL(txs.total_tax,0)),0)
from okl_tax_sources txs
where txs.khr_id = khr.id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX') TOTAL_UPFRONT_TAX_ON_ASSETS,
-- null TOTAL_NUMBER_OF_ASSETS,
(select count(cleb.id)
from okc_k_lines_b cleb, okc_statuses_b status_asset
where cleb.chr_id = khr.id and cleb.dnz_chr_id = khr.id
AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
and cleb.sts_code = status_asset.code
and status_asset.ste_code not in ('TERMINATED', 'CANCELLED')
and cleb.lse_id = 33) TOTAL_NUMBER_OF_ASSETS,
ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
ledger.ledger_id LEDGER_ID,
ledger.name LEDGER,
-- min(billing_control.EARLIEST_STRM_BILL_DATE) CONTRACT_EARLIEST_BILL_DATE,
(select min(billing_control.EARLIEST_STRM_BILL_DATE)
from OKL_K_CONTROL billing_control
WHERE billing_control.khr_id = khr.id) CONTRACT_EARLIEST_BILL_DATE,
sales_rep.name CONTRACT_SALES_REP,
legal_entity.name LEGAL_ENTITY,
cust_party.PARTY_NUMBER CUSTOMER_PARTY_NUMBER,
cust_party.party_name CUSTOMER_PARTY_NAME,
cust_party.PARTY_TYPE CUSTOMER_PARTY_TYPE,
cust_party.SIC_CODE CUSTOMER_SIC_CODE,
cust_party.HQ_BRANCH_IND CUSTOMER_HQ_BRANCH_IND,
cust_party.TAX_REFERENCE CUSTOMER_TAX_REFERENCE,
cust_party.DUNS_NUMBER CUSTOMER_DUNS_NUMBER,
cust_party.KNOWN_AS CUSTOMER_ALIAS,
cust_party.COUNTRY CUSTOMER_COUNTRY,
cust_party.ADDRESS1 CUSTOMER_ADDRESS1,
cust_party.ADDRESS2 CUSTOMER_ADDRESS2,
cust_party.ADDRESS3 CUSTOMER_ADDRESS3,
cust_party.ADDRESS4 CUSTOMER_ADDRESS4,
cust_party.CITY CUSTOMER_CITY,
cust_party.POSTAL_CODE CUSTOMER_POSTAL_CODE,
cust_party.STATE CUSTOMER_STATE,
cust_party.PROVINCE CUSTOMER_PROVINCE,
cust_party.COUNTY CUSTOMER_COUNTY,
cust_party.SIC_CODE_TYPE CUSTOMER_SIC_CODE_TYPE,
cust_party.URL CUSTOMER_URL,
cust_party.ANALYSIS_FY FINANCIAL_INFO_FISCAL_YEAR,
cust_party.FISCAL_YEAREND_MONTH CUSTOMER_FISCAL_YEAREND_MONTH,
cust_party.EMPLOYEES_TOTAL TOTAL_NUM_OF_EMPLOYEES,
cust_party.CURR_FY_POTENTIAL_REVENUE CURR_FISC_YEAR_POTENTIAL_REV,
cust_party.NEXT_FY_POTENTIAL_REVENUE NEXT_FISC_YEAR_POTENTIAL_REV,
cust_party.YEAR_ESTABLISHED CUSTOMER_YEAR_ESTABLISHED,
cust_party.GSA_INDICATOR_FLAG GEN_SERV_ADMIN_INDICATOR,
cust_party.ORGANIZATION_NAME_PHONETIC CUSTOMER_ORG_NAME_PHONETIC,
cust_party.COMPETITOR_FLAG CUSTOMER_COMPETITOR_YN,
cust_party.KNOWN_AS2 CUSTOMER_ALIAS2,
cust_party.KNOWN_AS3 CUSTOMER_ALIAS3,
cust_party.KNOWN_AS4 CUSTOMER_ALIAS4,
cust_party.KNOWN_AS5 CUSTOMER_ALIAS5,
cust_party.PRIMARY_PHONE_PURPOSE CUSTOMER_PRIM_PHONE_PURPOSE,
cust_party.PRIMARY_PHONE_LINE_TYPE CUSTOMER_PRIM_PHONE_TYPE,
cust_party.PRIMARY_PHONE_COUNTRY_CODE CUSTOMER_PRIM_PH_COUNTRY_CODE,
cust_party.PRIMARY_PHONE_AREA_CODE CUSTOMER_PRIM_PH_AREA_CODE,
cust_party.PRIMARY_PHONE_NUMBER CUSTOMER_PRIM_PHONE_NUMBER,
cust_party.PRIMARY_PHONE_EXTENSION CUSTOMER_PRIM_PHONE_EXTN,
cust_party.HOME_COUNTRY CUSTOMER_HOME_COUNTRY,
-- cust_party.status CUSTOMER_STATUS,
ar_lookup_status.meaning CUSTOMER_STATUS,
cust_accounts.PARTY_ID CUSTOMER_PARTY_ID ,
cust_accounts.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
cust_accounts.ATTRIBUTE_CATEGORY CUSTOMER_ACC_DFF_CATEGORY,
cust_accounts.ATTRIBUTE1 CUSTOMER_ACC_DFF1 ,
cust_accounts.ATTRIBUTE2 CUSTOMER_ACC_DFF2 ,
cust_accounts.ATTRIBUTE3 CUSTOMER_ACC_DFF3,
cust_accounts.ATTRIBUTE4 CUSTOMER_ACC_DFF4,
cust_accounts.ATTRIBUTE5 CUSTOMER_ACC_DFF5,
cust_accounts.ATTRIBUTE6 CUSTOMER_ACC_DFF6 ,
cust_accounts.ATTRIBUTE7 CUSTOMER_ACC_DFF7,
cust_accounts.ATTRIBUTE8 CUSTOMER_ACC_DFF8,
cust_accounts.ATTRIBUTE9 CUSTOMER_ACC_DFF9,
cust_accounts.ATTRIBUTE10 CUSTOMER_ACC_DFF10,
cust_accounts.ATTRIBUTE11 CUSTOMER_ACC_DFF11,
cust_accounts.ATTRIBUTE12 CUSTOMER_ACC_DFF12,
cust_accounts.ATTRIBUTE13 CUSTOMER_ACC_DFF13,
cust_accounts.ATTRIBUTE14 CUSTOMER_ACC_DFF14,
cust_accounts.ATTRIBUTE15 CUSTOMER_ACC_DFF15,
cust_accounts.ATTRIBUTE16 CUSTOMER_ACC_DFF16,
cust_accounts.ATTRIBUTE17 CUSTOMER_ACC_DFF17,
cust_accounts.ATTRIBUTE18 CUSTOMER_ACC_DFF18,
cust_accounts.ATTRIBUTE19 CUSTOMER_ACC_DFF19,
cust_accounts.ATTRIBUTE20 CUSTOMER_ACC_DFF20,
ar_lookup_acc_status.meaning CUSTOMER_ACCOUNT_STATUS,
-- cust_accounts.STATUS CUSTOMER_ACCOUNT_STATUS,
-- cust_accounts.CUSTOMER_TYPE CUSTOMER_TYPE ,
ar_lookup_cust_type.meaning CUSTOMER_TYPE ,
cust_accounts.SALES_CHANNEL_CODE CUSTOMER_SALES_CHANNEL,
cust_accounts.ACCOUNT_ESTABLISHED_DATE CUSTOMER_ACC_ESTAB_DATE,
cust_accounts.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
cust_accounts.COMMENTS CUSTOMER_ACCOUNT_COMMENTS,
cust_accounts.STATUS_UPDATE_DATE CUSTOMER_LAST_STATUS_UPD_DT,
cust_party_site.PARTY_SITE_ID CUSTOMER_PARTY_SITE_ID,
cust_party_site.PARTY_SITE_NUMBER CUSTOMER_PARTY_SITE_NUMBER,
cust_party_site.PARTY_SITE_NAME CUSTOMER_PARTY_SITE_NAME,
cust_party_site.LOCATION_ID CUSTOMER_PARTY_SITE_LOC_ID,
cust_party_site.IDENTIFYING_ADDRESS_FLAG CUSTOMER_IDENTIFYING_ADR_FLAG,
-- cust_party_site.STATUS CUSTOMER_PARTY_SITE_STATUS,
ar_lookup_cust_site.meaning CUSTOMER_PARTY_SITE_STATUS,
cust_party_site.ADDRESSEE CUSTOMER_PARTY_SITE_ADDRESSEE,
cust_party_site.GLOBAL_LOCATION_NUMBER CUSTOMER_SITE_GLOB_LOC_NUM,
--null CUSTOMER_PARTY_SITE_ID,
--null CUSTOMER_PARTY_SITE_NUMBER,
--null CUSTOMER_PARTY_SITE_NAME,
--null CUSTOMER_PARTY_SITE_LOC_ID,
-- null CUSTOMER_IDENTIFYING_ADR_FLAG,
--null CUSTOMER_PARTY_SITE_STATUS,
--null CUSTOMER_PARTY_SITE_ADDRESSEE,
--null CUSTOMER_SITE_GLOB_LOC_NUM,
cust_party_site_use.cpsu_PARTY_SITE_USE_ID CUSTOMER_PARTY_SITE_USE_ID,
cust_party_site_use.cpsu_SITE_USE_TYPE CUSTOMER_PARTY_SITE_USE_TYPE,
cust_party_site_use.cpsu_COMMENTS CUSTOMER_PARTY_SITE_COMMENTS,
--cust_party_site_use.cpsu_STATUS CUSTOMER_PARTY_SITE_USE_STATUS,
--null CUSTOMER_PARTY_SITE_USE_ID,
--null CUSTOMER_PARTY_SITE_USE_TYPE,
--null CUSTOMER_PARTY_SITE_COMMENTS,
ar_lookup_cust_site_use.meaning CUSTOMER_PARTY_SITE_USE_STATUS,
-- null CUSTOMER_PARTY_SITE_USE_STATUS,
--null CONTRACT_PAST_DUE_AMT_1_30,
--null CONTRACT_PAST_DUE_AMT_31_60,
--null CONTRACT_PAST_DUE_AMT_61_90,
--null CONTRACT_PAST_DUE_AMT_91_120,
-- null CONTRACT_PAST_DUE_AMT_120_Plus,
NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,1,30),0) CONTRACT_PAST_DUE_AMT_1_30,
NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,31,60),0) CONTRACT_PAST_DUE_AMT_31_60,
NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,61,90),0) CONTRACT_PAST_DUE_AMT_61_90,
NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,91,120),0) CONTRACT_PAST_DUE_AMT_91_120,
NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,120,12000),0) CONTRACT_PAST_DUE_AMT_120_Plus,
null CONTRACT_TOT_PAST_DUE_AMT,
-- null CONTRACT_TOT_BILLED_RECEIVABLE,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd
where txd.khr_id = khr.id ) CONTRACT_TOT_BILLED_RECEIVABLE,
/*
-- for Net Investment
DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
0 ) CONTRACT_UNEARNED_INCOME,
DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
0) CONTRACT_RESIDUAL_AMOUNT,
DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
'LEASEST',NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
0) CONTRACT_RENT_AMOUNT,
DECODE(khr.deal_type, 'LEASEOP', NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0),
0) CONTRACT_ACC_DEPRECIATION, --Duplicate of CONTRACT_ACCUMULATED_DEP
*/
--sechawla : calculate CONTRACT_UNEARNED_INCOME, CONTRACT_RESIDUAL_AMOUNT, CONTRACT_RENT_AMOUNT, CONTRACT_ACC_DEPRECIATION for all types of leases
NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0) CONTRACT_UNEARNED_INCOME,
NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0) CONTRACT_RESIDUAL_AMOUNT,
NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0) CONTRACT_RENT_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0) CONTRACT_ACC_DEPRECIATION,
-- OKL_ACCOUNTING_UTIL.get_curr_con_rate(chr.CURRENCY_CODE, ledger.CURRENCY_CODE, trunc(sysdate), khr.CURRENCY_CONVERSION_TYPE) currency_convertion_rate
NVL(OKL_FUNDING_PVT.get_amount_prefunded(chr.id),0) CONTRACT_AMOUNT_PREFUNDED,
NVL(OKL_FUNDING_PVT.get_chr_funded_adjs(chr.id),0) CONTRACT_FUNDED_ADJS,
NVL(OKL_FUNDING_PVT.get_amount_manu_disb(chr.id),0) CONTRACT_AMOUNT_MANU_DISB,
l_last_updated_by CREATED_BY,
sysdate CREATION_DATE,
l_last_updated_by LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
l_last_update_login LAST_UPDATE_LOGIN,
l_program_app_id PROGRAM_APPLICATION_ID,
l_program_id PROGRAM_ID,
l_program_login_id PROGRAM_LOGIN_ID,
sysdate PROGRAM_UPDATE_DATE
FROM
OKC_K_HEADERS_ALL_B chr,
OKL_K_HEADERS khr,
okl_parallel_processes opp,
HR_OPERATING_UNITS hr_org,
OKC_STATUSES_B status,
OKC_STATUSES_TL status_tl,
OKC_K_PARTY_ROLES_B cust_party_roles,
fnd_lookup_values_vl lookup_book_class,
HZ_PARTIES cust_party,
HZ_CUST_ACCOUNTS cust_accounts,
OKL_PRODUCTS pdt,
OKL_PRODUCTS rpt_pdt,
OKC_K_HEADERS_ALL_B vendor_chr,
okc_rule_groups_b rgd,
okc_k_party_roles_v cpl,
po_vendors vendor,
OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
GL_LEDGERS ledger,
okc_contacts sales_rep_contact,
okx_salesreps_v sales_rep,
xle_entity_profiles legal_entity,
hz_party_sites cust_party_site,
--hz_party_site_uses cust_party_site_use,
--hz_cust_site_uses_all cust_cust_site_use,
hz_cust_acct_sites_all cust_cust_acct_site,
-- hz_party_site_uses cust_party_site_use,
(select cpsu_party_site_use_id,
cpsu_party_site_id,
cpsu_SITE_USE_TYPE,
cpsu_status,
cpsu_comments
from (
select
cust_party_site_use_n.party_site_use_id cpsu_party_site_use_id, --sechawla changed party_site_id to party_site_use_id
cust_party_site_use_n.party_site_id cpsu_party_site_id,
cust_party_site_use_n.SITE_USE_TYPE cpsu_site_use_type,
cust_party_site_use_n.status cpsu_status,
cust_party_site_use_n.comments cpsu_comments,
row_number() over ( partition by cust_party_site_use_n.party_site_id
order by NVL(cust_party_site_use_n.status,'A') ASC) cpsu_stat_priority
from hz_party_site_uses cust_party_site_use_n
where cust_party_site_use_n.SITE_USE_TYPE = 'BILL_TO'
) where cpsu_stat_priority = 1) cust_party_site_use,
hz_cust_site_uses_all cust_cust_site_use,
ar_lookups ar_lookup_status,
ar_lookups ar_lookup_acc_status,
ar_lookups ar_lookup_cust_type,
ar_lookups ar_lookup_cust_site,
ar_lookups ar_lookup_cust_site_use
WHERE chr.id = khr.id
AND chr.SCS_CODE = 'LEASE'
AND opp.object_type = 'CONTRACT_FIN_EXTRACT'
AND opp.object_value = chr.contract_number
AND opp.assigned_process = p_assigned_process -- assigned_process is unique for each run
AND opp.khr_id = chr.id
AND hr_org.organization_id = chr.authoring_org_id
AND status.code = chr.sts_code
AND status_tl.code = status.code
AND status_tl.language = USERENV('LANG')
AND cust_party.party_type in ( 'PERSON','ORGANIZATION')
AND cust_party.party_id = cust_party_roles.object1_id1
AND cust_party_roles.object1_id2 = '#'
AND cust_party_roles.jtot_object1_code = 'OKX_PARTY'
AND cust_party_roles.rle_code = 'LESSEE'
AND cust_party_roles.chr_id = chr.id
AND cust_party_roles.dnz_chr_id = chr.id
AND cust_accounts.cust_account_id = chr.cust_acct_id
AND cust_accounts.party_id = cust_party.party_id
AND khr.pdt_id = pdt.id
AND pdt.REPORTING_PDT_ID = rpt_pdt.id(+)
AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
AND vendor_chr.id (+) = khr.khr_id
AND vendor_chr.scs_code (+) = 'PROGRAM'
AND rgd.chr_id(+)= vendor_chr.id
AND rgd.dnz_chr_id(+) = vendor_chr.id
AND rgd.cle_id IS NULL
AND rgd.rgd_code(+) = 'LAVENB'
AND vendor_chr.id = cpl.chr_id (+)
AND vendor_chr.id = cpl.dnz_chr_id(+)
AND cpl.rle_code (+) = 'OKL_VENDOR'
and cpl.object1_id1 = vendor.vendor_id (+)
AND acct_sys_opts.org_id = chr.authoring_org_id
AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
AND sales_rep_contact.DNZ_CHR_ID (+) = khr.id
AND sales_rep.id1 (+) = sales_rep_contact.object1_id1
AND sales_rep.id2 (+) = sales_rep_contact.object1_id2
and sales_rep_contact.cro_code (+) = 'SALESPERSON'
AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
and cust_party.party_id = cust_party_site.party_id
AND cust_cust_site_use.site_use_id = chr.bill_to_site_use_id
AND cust_cust_site_use.cust_acct_site_id = cust_cust_acct_site.cust_acct_site_id
-- and cust_cust_site_use.SITE_USE_CODE = 'BILL_TO'
and cust_cust_acct_site.party_site_id = cust_party_site.party_site_id
and cust_party_site.party_site_id = cust_party_site_use.cpsu_party_site_id
-- and cust_party_site_use.SITE_USE_TYPE = 'BILL_TO'
AND ar_lookup_status.lookup_type(+) = 'HZ_CPUI_REGISTRY_STATUS'
AND ar_lookup_status.lookup_code(+) = cust_party.status
AND ar_lookup_acc_status.lookup_type(+) = 'CODE_STATUS'
AND ar_lookup_acc_status.lookup_code(+) = cust_accounts.STATUS
AND ar_lookup_cust_type.lookup_type(+) = 'CUSTOMER_TYPE'
AND ar_lookup_cust_type.lookup_code(+) = cust_accounts.CUSTOMER_TYPE
AND ar_lookup_cust_site.lookup_type(+) = 'REGISTRY_STATUS'
AND ar_lookup_cust_site.lookup_code(+) = cust_party_site.STATUS
AND ar_lookup_cust_site_use.lookup_type(+) = 'REGISTRY_STATUS'
AND ar_lookup_cust_site_use.lookup_code(+) = cust_party_site_use.cpsu_STATUS
-- parameters section begin
/*
--sechawla : don't need the following as these paramater matching has been already done in
--Master Program - OKL_CNTRCT_FIN_EXT_MASTER_PVT
AND chr.AUTHORING_ORG_ID = P_OPERATING_UNIT
AND chr.START_DATE >= P_START_DATE_FROM
AND chr.START_DATE <= P_START_DATE_TO
AND khr.DEAL_TYPE like nvl(P_BOOK_CLASS, khr.DEAL_TYPE)
AND pdt.ID like nvl(P_LEASE_PRODUCT, pdt.ID)
AND chr.sts_code like nvl(P_CONTRACT_STATUS, chr.sts_code)
AND ( (lp_k_end_date_from IS NULL) OR (chr.END_DATE IS NOT NULL AND chr.END_DATE >= lp_k_end_date_from) )
AND ( (lp_k_end_date_to IS NULL ) OR (chr.END_DATE IS NOT NULL AND chr.END_DATE <= lp_k_end_date_to) )
*/
--there is no ID defined in the value set OKS_CUSTOMER_NUMBER. value is 'party_number'.
AND cust_party.PARTY_NUMBER like nvl(P_CUSTOMER_NUMBER, cust_party.PARTY_NUMBER)
--sechawla : P_CUSTOMER_NAME is hz_parties.party_id. value set: OKL_CUSTOMERS
AND cust_party.party_id = nvl(P_CUSTOMER_NAME, cust_party.party_id)
AND nvl(cust_party.SIC_CODE,'XXX') like nvl(P_SIC_CODE, nvl(cust_party.SIC_CODE,'XXX'))
--sechawla : p_vendor_number -> value set 'OKL_VENDORS' -> returns PO_VENDORS.VENDOR_ID
--Need nvl, as every contract may not have vendor_id (program vendor)
AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NUMBER,nvl(vendor.vendor_id,-9999))
--sechawla : P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NAME,nvl(vendor.vendor_id,-9999))
-- P_SALES_CHANNEL -> value set QP_SALES_CHANNEL_CODE -> returns oe_lookups.lookup_code
AND nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX') like nvl(P_SALES_CHANNEL, nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX'))
-- AND CONTRACT_ACCRUAL_STATUS like nvl(P_GEN_ACCRUAL, CONTRACT_ACCRUAL_STATUS)
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))
AND ( (P_TERMINATE_DATE_FROM IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED >= P_TERMINATE_DATE_FROM) )
AND ( (P_TERMINATE_DATE_TO IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED <= P_TERMINATE_DATE_TO) )
-- parameters section end
);
INSERT INTO OKL_CNTRCT_FIN_EXTRACT_T
(
REQUEST_ID,
CONTRACT_DOCUMENT_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_INVENTORY_ORG_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_TEMPLATE_INDICATOR,
CONTRACT_CUST_PURCH_ORDER_NUM,
CONTRACT_TEMPLATE_NAME,
CONTRACT_APPROVED_DATE,
CONTRACT_CANCELED_TIMESTAMP,
CONTRACT_SIGNED_DATE,
CONTRACT_TERMINATION_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CUSTOMER_ACCOUNT_ID,
CUST_BILL_TO_SITE_USE_ID,
CUST_PAYMENT_TERM_ID,
CONTRACT_ORIG_SYSTEM_SOURCE,
CONTRACT_ID,
CONTRACT_SALES_REGION_ID,
VENDOR_PROGRAM_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_DATE_DEAL_ACTIVATED,
CONTRACT_DATE_DEAL_TRANSFERD,
CONTRACT_TERM_DURATION,
CONTRACT_HDR_DFF_CATEGORY,
CONTRACT_HDR_DFF1,
CONTRACT_HDR_DFF2,
CONTRACT_HDR_DFF3,
CONTRACT_HDR_DFF4,
CONTRACT_HDR_DFF5,
CONTRACT_HDR_DFF6,
CONTRACT_HDR_DFF7,
CONTRACT_HDR_DFF8,
CONTRACT_HDR_DFF9,
CONTRACT_HDR_DFF10,
CONTRACT_HDR_DFF11,
CONTRACT_HDR_DFF12,
CONTRACT_HDR_DFF13,
CONTRACT_HDR_DFF14,
CONTRACT_HDR_DFF15,
CONTRACT_ACCRUAL_STATUS,
CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
CONTRACT_CREDIT_ACT_YN,
CONTRACT_CONVERTED_ACCOUNT_YN,
CONTRACT_AFTER_TAX_YIELD,
CONTRACT_IMPL_INTEREST_RATE,
CONTRACT_LAST_INT_CALC_DATE,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_PRE_TAX_IRR,
CONTRACT_AFTER_TAX_IRR,
CONTRACT_EXP_DELIVERY_DATE,
CONTRACT_ACCEPTANCE_DATE,
CONTRACT_PREFUND_ELIG_YN,
CONTRACT_REVOL_CREDIT_ELIG_YN,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_MULTI_GAAP_ELIG_YN,
CONTRACT_INVESTOR_ASSIGN_YN,
CONTRACT_SECURITIZATION_TYPE,
CONTRACT_SUB_AFTER_TAX_YIELD,
CONTRACT_SUB_IMPL_INT_RATE,
CONTRACT_SUB_PRE_TAX_IRR,
CONTRACT_SUB_AFTER_TAX_IRR,
CONTRACT_CRDTLINE_TRNSFR_AMT,
CONTRACT_CRDTLINE_NETRNSFR_AMT,
CONTRACT_CRDTLINE_LIMIT,
CONTRACT_CRDTLINE_FUNDING_AMT,
CONTRACT_TEMPL_TYPE_CODE,
CONTRACT_TRADEIN_DESCRIPTION,
CONTRACT_TRADEIN_DATE,
CONTRACT_LEGAL_ENTITY_ID,
CONTRACT_FIRST_ACTIVITY_DATE,
--formulas
CONTRACT_ACCUMULATED_DEP,
CONTRACT_CAPITALIZED_REDUCTION,
CONTRACT_CAPITALIZED_FEE,
CONTRACT_CAPITALIZED_INTEREST,
CONTRACT_DISCOUNT,
CONTRACT_ASSET_COST,
CONTRACT_FINANCED_FEE,
CONTRACT_NEXT_PAYMENT_AMT,
CONTRACT_TOTAL_ACCRUED_INT,
CONTRACT_TOTAL_ACTUAL_INT,
CONTRACT_SUBSIDY_AMOUNT,
CONTRACT_UNACCRUED_SUBSIDY,
CONTRACT_UNBILLED_DUE_AMOUNT,
CONTRACT_UNBILLED_RECEIVABLES,
CONTRACT_OEC,
CONTRACT_ACTUAL_PRINC_BAL,
ROLLOVER_FEE_AMOUNT,
CONTRACT_TRADEIN_AMOUNT,
TOTAL_AMOUNT_PAID_TO_DEALER,
TOTAL_DEBITS_FOR_PREFUNDING,
CONTRACT_PREFUNDED_AMOUNT,
CONTRACT_BILLED_RENTS,
CONTRACT_BILLED_RENTS_FUNC,
CONTRACT_BILLED_NON_RENTS,
CONTRACT_BILLED_NON_RENTS_FUNC,
SUPPLIER_RETENTION_FUNDING_AMT,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
CONTRACT_IDC_AMOUNT,
CONTRACT_SCHEDULED_PRINC_BAL,
CONTRACT_INTEREST_RATE,
CONTRACT_DAYS_TO_ACCRUE,
CONTRACT_DAYS_IN_YEAR,
CONTRACT_CAP_AMOUNT,
CONTRACT_FINANCED_AMOUNT,
CONTRACT_VAR_INCOME_ACCRUAL,
TOTAL_PREFUNDING_AMOUNT,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FEE_INCOME,
ACC_ENGINE_TEMPL_SET_ID,
CONTRACT_FIN_PROD_TEMPLATE_ID,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_FIN_PROD_DESC,
REPORTING_PRODUCT_ID,
CONTRACT_FIN_PROD_STATUS_CODE,
CONTRACT_FIN_PROD_EFF_FROM_DT,
CONTRACT_FIN_PROD_EFF_TO_DT,
REPORTING_PRODUCT,
VENDOR_PROGRAM_NAME,
VENDOR_NUMBER,
VENDOR_NAME,
VENDOR_TYPE_CODE,
VENDOR_BILL_TO_SITE_USE_ID,
VENDOR_PARTY_ROLE,
TOTAL_UPFRONT_TAX_ON_ASSETS,
TOTAL_NUMBER_OF_ASSETS,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
CONTRACT_EARLIEST_BILL_DATE,
CONTRACT_SALES_REP,
LEGAL_ENTITY,
CUSTOMER_PARTY_NUMBER,
CUSTOMER_PARTY_NAME,
CUSTOMER_PARTY_TYPE,
CUSTOMER_SIC_CODE,
CUSTOMER_HQ_BRANCH_IND,
CUSTOMER_TAX_REFERENCE,
CUSTOMER_DUNS_NUMBER,
CUSTOMER_ALIAS,
CUSTOMER_COUNTRY,
CUSTOMER_ADDRESS1,
CUSTOMER_ADDRESS2,
CUSTOMER_ADDRESS3,
CUSTOMER_ADDRESS4,
CUSTOMER_CITY,
CUSTOMER_POSTAL_CODE,
CUSTOMER_STATE,
CUSTOMER_PROVINCE,
CUSTOMER_COUNTY,
CUSTOMER_SIC_CODE_TYPE,
CUSTOMER_URL,
FINANCIAL_INFO_FISCAL_YEAR,
CUSTOMER_FISCAL_YEAREND_MONTH,
TOTAL_NUM_OF_EMPLOYEES,
CURR_FISC_YEAR_POTENTIAL_REV,
NEXT_FISC_YEAR_POTENTIAL_REV,
CUSTOMER_YEAR_ESTABLISHED,
GEN_SERV_ADMIN_INDICATOR,
CUSTOMER_ORG_NAME_PHONETIC,
CUSTOMER_COMPETITOR_YN,
CUSTOMER_ALIAS2,
CUSTOMER_ALIAS3,
CUSTOMER_ALIAS4,
CUSTOMER_ALIAS5,
CUSTOMER_PRIM_PHONE_PURPOSE,
CUSTOMER_PRIM_PHONE_TYPE,
CUSTOMER_PRIM_PH_COUNTRY_CODE,
CUSTOMER_PRIM_PH_AREA_CODE,
CUSTOMER_PRIM_PHONE_NUMBER,
CUSTOMER_PRIM_PHONE_EXTN,
CUSTOMER_HOME_COUNTRY,
CUSTOMER_STATUS,
CUSTOMER_PARTY_ID,
CUSTOMER_ACCOUNT_NUMBER,
CUSTOMER_ACC_DFF_CATEGORY,
CUSTOMER_ACC_DFF1,
CUSTOMER_ACC_DFF2,
CUSTOMER_ACC_DFF3,
CUSTOMER_ACC_DFF4,
CUSTOMER_ACC_DFF5,
CUSTOMER_ACC_DFF6,
CUSTOMER_ACC_DFF7,
CUSTOMER_ACC_DFF8,
CUSTOMER_ACC_DFF9,
CUSTOMER_ACC_DFF10,
CUSTOMER_ACC_DFF11,
CUSTOMER_ACC_DFF12,
CUSTOMER_ACC_DFF13,
CUSTOMER_ACC_DFF14,
CUSTOMER_ACC_DFF15,
CUSTOMER_ACC_DFF16,
CUSTOMER_ACC_DFF17,
CUSTOMER_ACC_DFF18,
CUSTOMER_ACC_DFF19,
CUSTOMER_ACC_DFF20,
CUSTOMER_ACCOUNT_STATUS,
CUSTOMER_TYPE,
CUSTOMER_SALES_CHANNEL,
CUSTOMER_ACC_ESTAB_DATE,
CUSTOMER_ACCOUNT_NAME,
CUSTOMER_ACCOUNT_COMMENTS,
CUSTOMER_LAST_STATUS_UPD_DT,
CUSTOMER_PARTY_SITE_ID,
CUSTOMER_PARTY_SITE_NUMBER,
CUSTOMER_PARTY_SITE_NAME,
CUSTOMER_PARTY_SITE_LOC_ID,
CUSTOMER_IDENTIFYING_ADR_FLAG,
CUSTOMER_PARTY_SITE_STATUS,
CUSTOMER_PARTY_SITE_ADDRESSEE,
CUSTOMER_SITE_GLOB_LOC_NUM,
CUSTOMER_PARTY_SITE_USE_ID,
CUSTOMER_PARTY_SITE_USE_TYPE,
CUSTOMER_PARTY_SITE_COMMENTS,
CUSTOMER_PARTY_SITE_USE_STATUS,
CONTRACT_PAST_DUE_AMT_1_30,
CONTRACT_PAST_DUE_AMT_31_60,
CONTRACT_PAST_DUE_AMT_61_90,
CONTRACT_PAST_DUE_AMT_91_120,
CONTRACT_PAST_DUE_AMT_120_Plus,
CONTRACT_TOT_PAST_DUE_AMT,
CONTRACT_TOT_BILLED_RECEIVABLE,
CONTRACT_RESIDUAL_AMOUNT,
CONTRACT_RENT_AMOUNT,
CONTRACT_ACC_DEPRECIATION,
NET_INVESTMENT,
NET_INVESTMENT_FUNCTIONAL,
CONTRACT_AMOUNT_PREFUNDED,
CONTRACT_TOTAL_MISC_FUND,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_UPDATE_DATE
)
select
REQUEST_ID,
CONTRACT_DOCUMENT_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_INVENTORY_ORG_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_TEMPLATE_INDICATOR,
CONTRACT_CUST_PURCH_ORDER_NUM,
CONTRACT_TEMPLATE_NAME,
CONTRACT_APPROVED_DATE,
CONTRACT_CANCELED_TIMESTAMP,
CONTRACT_SIGNED_DATE,
CONTRACT_TERMINATION_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CUSTOMER_ACCOUNT_ID,
CUST_BILL_TO_SITE_USE_ID,
CUST_PAYMENT_TERM_ID,
CONTRACT_ORIG_SYSTEM_SOURCE,
CONTRACT_ID,
CONTRACT_SALES_REGION_ID,
VENDOR_PROGRAM_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_DATE_DEAL_ACTIVATED,
CONTRACT_DATE_DEAL_TRANSFERD,
CONTRACT_TERM_DURATION,
CONTRACT_HDR_DFF_CATEGORY,
CONTRACT_HDR_DFF1,
CONTRACT_HDR_DFF2,
CONTRACT_HDR_DFF3,
CONTRACT_HDR_DFF4,
CONTRACT_HDR_DFF5,
CONTRACT_HDR_DFF6,
CONTRACT_HDR_DFF7,
CONTRACT_HDR_DFF8,
CONTRACT_HDR_DFF9,
CONTRACT_HDR_DFF10,
CONTRACT_HDR_DFF11,
CONTRACT_HDR_DFF12,
CONTRACT_HDR_DFF13,
CONTRACT_HDR_DFF14,
CONTRACT_HDR_DFF15,
CONTRACT_ACCRUAL_STATUS,
CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
CONTRACT_CREDIT_ACT_YN,
CONTRACT_CONVERTED_ACCOUNT_YN,
CONTRACT_AFTER_TAX_YIELD,
CONTRACT_IMPL_INTEREST_RATE,
CONTRACT_LAST_INT_CALC_DATE,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_PRE_TAX_IRR,
CONTRACT_AFTER_TAX_IRR,
CONTRACT_EXP_DELIVERY_DATE,
CONTRACT_ACCEPTANCE_DATE,
CONTRACT_PREFUND_ELIG_YN,
CONTRACT_REVOL_CREDIT_ELIG_YN,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_MULTI_GAAP_ELIG_YN,
CONTRACT_INVESTOR_ASSIGN_YN,
CONTRACT_SECURITIZATION_TYPE,
CONTRACT_SUB_AFTER_TAX_YIELD,
CONTRACT_SUB_IMPL_INT_RATE,
CONTRACT_SUB_PRE_TAX_IRR,
CONTRACT_SUB_AFTER_TAX_IRR,
CONTRACT_CRDTLINE_TRNSFR_AMT,
CONTRACT_CRDTLINE_NETRNSFR_AMT,
CONTRACT_CRDTLINE_LIMIT,
CONTRACT_CRDTLINE_FUNDING_AMT,
CONTRACT_TEMPL_TYPE_CODE,
CONTRACT_TRADEIN_DESCRIPTION,
CONTRACT_TRADEIN_DATE,
CONTRACT_LEGAL_ENTITY_ID,
CONTRACT_FIRST_ACTIVITY_DATE,
--formulas
CONTRACT_ACCUMULATED_DEP,
CONTRACT_CAPITALIZED_REDUCTION,
CONTRACT_CAPITALIZED_FEE,
CONTRACT_CAPITALIZED_INTEREST,
CONTRACT_DISCOUNT,
CONTRACT_ASSET_COST,
CONTRACT_FINANCED_FEE,
CONTRACT_NEXT_PAYMENT_AMT,
CONTRACT_TOTAL_ACCRUED_INT,
CONTRACT_TOTAL_ACTUAL_INT,
CONTRACT_SUBSIDY_AMOUNT,
CONTRACT_UNACCRUED_SUBSIDY,
CONTRACT_UNBILLED_DUE_AMOUNT,
CONTRACT_UNBILLED_RECEIVABLES,
CONTRACT_OEC,
CONTRACT_ACTUAL_PRINC_BAL,
ROLLOVER_FEE_AMOUNT,
CONTRACT_TRADEIN_AMOUNT,
TOTAL_AMOUNT_PAID_TO_DEALER,
TOTAL_DEBITS_FOR_PREFUNDING,
CONTRACT_PREFUNDED_AMOUNT,
CONTRACT_BILLED_RENTS,
(CONTRACT_BILLED_RENTS * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_RENTS_FUNC,
(CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) CONTRACT_BILLED_NON_RENTS,
((CONTRACT_TOT_BILLED_RECEIVABLE - CONTRACT_BILLED_RENTS) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) CONTRACT_BILLED_NON_RENTS_FUNC,
SUPPLIER_RETENTION_FUNDING_AMT,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
CONTRACT_IDC_AMOUNT,
CONTRACT_SCHEDULED_PRINC_BAL,
CONTRACT_INTEREST_RATE,
CONTRACT_DAYS_TO_ACCRUE,
CONTRACT_DAYS_IN_YEAR,
(CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+CONTRACT_CAPITALIZED_INTEREST) CONTRACT_CAP_AMOUNT,
(CONTRACT_OEC-nvl(CONTRACT_TRADEIN_AMOUNT,0)-CONTRACT_CAPITALIZED_REDUCTION+CONTRACT_CAPITALIZED_FEE+ CONTRACT_CAPITALIZED_INTEREST+CONTRACT_FINANCED_FEE) CONTRACT_FINANCED_AMOUNT,
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,
(CONTRACT_PREFUNDED_AMOUNT + TOTAL_DEBITS_FOR_PREFUNDING) TOTAL_PREFUNDING_AMOUNT,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FEE_INCOME,
ACC_ENGINE_TEMPL_SET_ID ,
CONTRACT_FIN_PROD_TEMPLATE_ID ,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_FIN_PROD_DESC ,
REPORTING_PRODUCT_ID ,
CONTRACT_FIN_PROD_STATUS_CODE ,
CONTRACT_FIN_PROD_EFF_FROM_DT ,
CONTRACT_FIN_PROD_EFF_TO_DT ,
REPORTING_PRODUCT,
VENDOR_PROGRAM_NAME,
VENDOR_NUMBER,
VENDOR_NAME,
VENDOR_TYPE_CODE,
VENDOR_BILL_TO_SITE_USE_ID,
VENDOR_PARTY_ROLE,
TOTAL_UPFRONT_TAX_ON_ASSETS,
TOTAL_NUMBER_OF_ASSETS,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
CONTRACT_EARLIEST_BILL_DATE,
CONTRACT_SALES_REP,
LEGAL_ENTITY,
CUSTOMER_PARTY_NUMBER,
CUSTOMER_PARTY_NAME,
CUSTOMER_PARTY_TYPE,
CUSTOMER_SIC_CODE,
CUSTOMER_HQ_BRANCH_IND,
CUSTOMER_TAX_REFERENCE,
CUSTOMER_DUNS_NUMBER,
CUSTOMER_ALIAS,
CUSTOMER_COUNTRY,
CUSTOMER_ADDRESS1,
CUSTOMER_ADDRESS2,
CUSTOMER_ADDRESS3,
CUSTOMER_ADDRESS4,
CUSTOMER_CITY,
CUSTOMER_POSTAL_CODE,
CUSTOMER_STATE,
CUSTOMER_PROVINCE,
CUSTOMER_COUNTY,
CUSTOMER_SIC_CODE_TYPE,
CUSTOMER_URL,
FINANCIAL_INFO_FISCAL_YEAR,
CUSTOMER_FISCAL_YEAREND_MONTH,
TOTAL_NUM_OF_EMPLOYEES,
CURR_FISC_YEAR_POTENTIAL_REV,
NEXT_FISC_YEAR_POTENTIAL_REV,
CUSTOMER_YEAR_ESTABLISHED,
GEN_SERV_ADMIN_INDICATOR,
CUSTOMER_ORG_NAME_PHONETIC,
CUSTOMER_COMPETITOR_YN,
CUSTOMER_ALIAS2,
CUSTOMER_ALIAS3,
CUSTOMER_ALIAS4,
CUSTOMER_ALIAS5,
CUSTOMER_PRIM_PHONE_PURPOSE,
CUSTOMER_PRIM_PHONE_TYPE,
CUSTOMER_PRIM_PH_COUNTRY_CODE,
CUSTOMER_PRIM_PH_AREA_CODE,
CUSTOMER_PRIM_PHONE_NUMBER,
CUSTOMER_PRIM_PHONE_EXTN,
CUSTOMER_HOME_COUNTRY,
CUSTOMER_STATUS,
CUSTOMER_PARTY_ID,
CUSTOMER_ACCOUNT_NUMBER,
CUSTOMER_ACC_DFF_CATEGORY,
CUSTOMER_ACC_DFF1,
CUSTOMER_ACC_DFF2,
CUSTOMER_ACC_DFF3,
CUSTOMER_ACC_DFF4,
CUSTOMER_ACC_DFF5,
CUSTOMER_ACC_DFF6,
CUSTOMER_ACC_DFF7,
CUSTOMER_ACC_DFF8,
CUSTOMER_ACC_DFF9,
CUSTOMER_ACC_DFF10,
CUSTOMER_ACC_DFF11,
CUSTOMER_ACC_DFF12,
CUSTOMER_ACC_DFF13,
CUSTOMER_ACC_DFF14,
CUSTOMER_ACC_DFF15,
CUSTOMER_ACC_DFF16,
CUSTOMER_ACC_DFF17,
CUSTOMER_ACC_DFF18,
CUSTOMER_ACC_DFF19,
CUSTOMER_ACC_DFF20,
CUSTOMER_ACCOUNT_STATUS,
CUSTOMER_TYPE,
CUSTOMER_SALES_CHANNEL,
CUSTOMER_ACC_ESTAB_DATE,
CUSTOMER_ACCOUNT_NAME,
CUSTOMER_ACCOUNT_COMMENTS,
CUSTOMER_LAST_STATUS_UPD_DT,
CUSTOMER_PARTY_SITE_ID,
CUSTOMER_PARTY_SITE_NUMBER,
CUSTOMER_PARTY_SITE_NAME,
CUSTOMER_PARTY_SITE_LOC_ID,
CUSTOMER_IDENTIFYING_ADR_FLAG,
CUSTOMER_PARTY_SITE_STATUS,
CUSTOMER_PARTY_SITE_ADDRESSEE,
CUSTOMER_SITE_GLOB_LOC_NUM,
CUSTOMER_PARTY_SITE_USE_ID,
CUSTOMER_PARTY_SITE_USE_TYPE,
CUSTOMER_PARTY_SITE_COMMENTS,
CUSTOMER_PARTY_SITE_USE_STATUS,
CONTRACT_PAST_DUE_AMT_1_30,
CONTRACT_PAST_DUE_AMT_31_60,
CONTRACT_PAST_DUE_AMT_61_90,
CONTRACT_PAST_DUE_AMT_91_120,
CONTRACT_PAST_DUE_AMT_120_Plus,
(CONTRACT_PAST_DUE_AMT_1_30 + CONTRACT_PAST_DUE_AMT_31_60 +
CONTRACT_PAST_DUE_AMT_61_90 + CONTRACT_PAST_DUE_AMT_91_120 +
CONTRACT_PAST_DUE_AMT_120_Plus) CONTRACT_TOT_PAST_DUE_AMT,
CONTRACT_TOT_BILLED_RECEIVABLE,
CONTRACT_RESIDUAL_AMOUNT,
CONTRACT_RENT_AMOUNT,
CONTRACT_ACC_DEPRECIATION,
DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME),
'LOAN', CONTRACT_ACTUAL_PRINC_BAL,
'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL,
'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION + CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT),
0))) NET_INVESTMENT,
DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEST', (CONTRACT_RENT_AMOUNT + CONTRACT_RESIDUAL_AMOUNT - CONTRACT_UNEARNED_INCOME) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN-REVOLVING', CONTRACT_ACTUAL_PRINC_BAL * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEOP', (CONTRACT_OEC - nvl(CONTRACT_TRADEIN_AMOUNT,0) - CONTRACT_CAPITALIZED_REDUCTION +
CONTRACT_CAPITALIZED_FEE + CONTRACT_CAPITALIZED_INTEREST - CONTRACT_ACC_DEPRECIATION - CONTRACT_SUBSIDY_AMOUNT) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
0))) NET_INVESTMENT_FUNCTIONAL,
(CONTRACT_AMOUNT_PREFUNDED + CONTRACT_FUNDED_ADJS ) CONTRACT_AMOUNT_PREFUNDED,
CONTRACT_AMOUNT_MANU_DISB CONTRACT_TOTAL_MISC_FUND,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_LOGIN_ID,
PROGRAM_UPDATE_DATE
from (
SELECT
--l_request_id REQUEST_ID,
l_parent_request_id REQUEST_ID,
chr.DOCUMENT_ID CONTRACT_DOCUMENT_ID,
chr.CONTRACT_NUMBER CONTRACT_NUMBER,
chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
chr.INV_ORGANIZATION_ID CONTRACT_INVENTORY_ORG_ID,
chr.STS_CODE CONTRACT_STATUS_CODE,
chr.CURRENCY_CODE CONTRACT_CURRENCY,
chr.TEMPLATE_YN CONTRACT_TEMPLATE_INDICATOR,
chr.CUST_PO_NUMBER CONTRACT_CUST_PURCH_ORDER_NUM,
chr.TEMPLATE_USED CONTRACT_TEMPLATE_NAME,
chr.DATE_APPROVED CONTRACT_APPROVED_DATE,
chr.DATETIME_CANCELLED CONTRACT_CANCELED_TIMESTAMP,
chr.DATE_SIGNED CONTRACT_SIGNED_DATE,
chr.DATE_TERMINATED CONTRACT_TERMINATION_DATE,
chr.START_DATE CONTRACT_START_DATE,
chr.END_DATE CONTRACT_END_DATE,
chr.CUST_ACCT_ID CUSTOMER_ACCOUNT_ID,
chr.BILL_TO_SITE_USE_ID CUST_BILL_TO_SITE_USE_ID,
chr.PAYMENT_TERM_ID CUST_PAYMENT_TERM_ID,
chr.ORIG_SYSTEM_SOURCE_CODE CONTRACT_ORIG_SYSTEM_SOURCE,
khr.ID CONTRACT_ID,
khr.ISG_ID CONTRACT_SALES_REGION_ID,
khr.KHR_ID VENDOR_PROGRAM_ID,
khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
--khr.DATE_CONVERSION_EFFECTIVE CONTRACT_DATE_DEAL_ACTIVATED,
OKL_CNTRCT_FIN_EXTRACT_PVT.first_activation_date(khr.id) CONTRACT_DATE_DEAL_ACTIVATED,
khr.DATE_DEAL_TRANSFERRED CONTRACT_DATE_DEAL_TRANSFERD,
khr.TERM_DURATION CONTRACT_TERM_DURATION,
khr.ATTRIBUTE_CATEGORY CONTRACT_HDR_DFF_CATEGORY,
khr.ATTRIBUTE1 CONTRACT_HDR_DFF1,
khr.ATTRIBUTE2 CONTRACT_HDR_DFF2,
khr.ATTRIBUTE3 CONTRACT_HDR_DFF3,
khr.ATTRIBUTE4 CONTRACT_HDR_DFF4,
khr.ATTRIBUTE5 CONTRACT_HDR_DFF5,
khr.ATTRIBUTE6 CONTRACT_HDR_DFF6,
khr.ATTRIBUTE7 CONTRACT_HDR_DFF7,
khr.ATTRIBUTE8 CONTRACT_HDR_DFF8,
khr.ATTRIBUTE9 CONTRACT_HDR_DFF9,
khr.ATTRIBUTE10 CONTRACT_HDR_DFF10,
khr.ATTRIBUTE11 CONTRACT_HDR_DFF11,
khr.ATTRIBUTE12 CONTRACT_HDR_DFF12,
khr.ATTRIBUTE13 CONTRACT_HDR_DFF13,
khr.ATTRIBUTE14 CONTRACT_HDR_DFF14,
khr.ATTRIBUTE15 CONTRACT_HDR_DFF15,
OKL_CNTRCT_FIN_EXTRACT_PVT.accrual_status_yn(khr.id) CONTRACT_ACCRUAL_STATUS,
khr.GENERATE_ACCRUAL_OVERRIDE_YN CONTRACT_GEN_ACCRUAL_OVRIDE_YN,
khr.CREDIT_ACT_YN CONTRACT_CREDIT_ACT_YN,
khr.CONVERTED_ACCOUNT_YN CONTRACT_CONVERTED_ACCOUNT_YN,
khr.AFTER_TAX_YIELD CONTRACT_AFTER_TAX_YIELD,
khr.IMPLICIT_INTEREST_RATE CONTRACT_IMPL_INTEREST_RATE,
khr.DATE_LAST_INTERIM_INTEREST_CAL CONTRACT_LAST_INT_CALC_DATE,
khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
khr.PRE_TAX_IRR CONTRACT_PRE_TAX_IRR,
khr.AFTER_TAX_IRR CONTRACT_AFTER_TAX_IRR,
khr.EXPECTED_DELIVERY_DATE CONTRACT_EXP_DELIVERY_DATE,
khr.ACCEPTED_DATE CONTRACT_ACCEPTANCE_DATE,
khr.PREFUNDING_ELIGIBLE_YN CONTRACT_PREFUND_ELIG_YN,
khr.REVOLVING_CREDIT_YN CONTRACT_REVOL_CREDIT_ELIG_YN,
khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
khr.MULTI_GAAP_YN CONTRACT_MULTI_GAAP_ELIG_YN ,
khr.ASSIGNABLE_YN CONTRACT_INVESTOR_ASSIGN_YN ,
khr.SECURITIZATION_TYPE CONTRACT_SECURITIZATION_TYPE,
khr.SUB_AFTER_TAX_YIELD CONTRACT_SUB_AFTER_TAX_YIELD,
khr.SUB_IMPL_INTEREST_RATE CONTRACT_SUB_IMPL_INT_RATE,
khr.SUB_PRE_TAX_IRR CONTRACT_SUB_PRE_TAX_IRR,
khr.SUB_AFTER_TAX_IRR CONTRACT_SUB_AFTER_TAX_IRR,
khr.TOT_CL_TRANSFER_AMT CONTRACT_CRDTLINE_TRNSFR_AMT,
khr.TOT_CL_NET_TRANSFER_AMT CONTRACT_CRDTLINE_NETRNSFR_AMT,
khr.TOT_CL_LIMIT CONTRACT_CRDTLINE_LIMIT,
khr.TOT_CL_FUNDING_AMT CONTRACT_CRDTLINE_FUNDING_AMT,
khr.TEMPLATE_TYPE_CODE CONTRACT_TEMPL_TYPE_CODE,
khr.TRADEIN_DESCRIPTION CONTRACT_TRADEIN_DESCRIPTION,
khr.TRADEIN_AMOUNT CONTRACT_TRADEIN_AMOUNT,
khr.DATE_TRADEIN CONTRACT_TRADEIN_DATE,
khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
khr.DATE_FIRST_ACTIVITY CONTRACT_FIRST_ACTIVITY_DATE,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACCUMULATED_DEPRN(chr.id, null),0) CONTRACT_ACCUMULATED_DEP,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_CAPITAL_REDUCTION(chr.id, null),0) CONTRACT_CAPITALIZED_REDUCTION,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FEES_CAPITALIZED(chr.id, null),0) CONTRACT_CAPITALIZED_FEE,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_capitalized_interest(chr.id, null),0) CONTRACT_CAPITALIZED_INTEREST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DISCOUNT(chr.id, null),0) CONTRACT_DISCOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ASSET_COST(chr.id, null),0) CONTRACT_ASSET_COST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_FINANCED_FEE(chr.id, null),0) CONTRACT_FINANCED_FEE,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_NEXT_PAYMENT_AMOUNT(chr.id, null),0) CONTRACT_NEXT_PAYMENT_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACCRUED_INT(chr.id, null),0) CONTRACT_TOTAL_ACCRUED_INT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_TOTAL_ACTUAL_INT(chr.id, null),0) CONTRACT_TOTAL_ACTUAL_INT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_asset_subsidy_amount(chr.id, null),0) CONTRACT_SUBSIDY_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNACCRUED_SUBSIDY(chr.id, null),0) CONTRACT_UNACCRUED_SUBSIDY,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_STREAMS(chr.id, null),0) CONTRACT_UNBILLED_DUE_AMOUNT,
DECODE(khr.deal_type,'LOAN',NULL,'LOAN-REVOLVING',NULL,NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_UNBILLED_RECEIVABLES(chr.id, null),0)) CONTRACT_UNBILLED_RECEIVABLES,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_OEC(chr.id, null),0) CONTRACT_OEC,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(chr.id, null),0) CONTRACT_ACTUAL_PRINC_BAL, -- addl para
NVL(OKL_SEEDED_FUNCTIONS_PVT.ROLLOVER_FEE(chr.id, null),0) ROLLOVER_FEE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_funded(chr.id, null),0) TOTAL_AMOUNT_PAID_TO_DEALER,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_total_debits(chr.id, null),0) TOTAL_DEBITS_FOR_PREFUNDING,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_amount_prefunded(chr.id, null),0) CONTRACT_PREFUNDED_AMOUNT,
-- null CONTRACT_BILLED_RENTS,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd,
okl_strm_type_b sty
where txd.khr_id = khr.id
and txd.sty_id = sty.id
and sty.STREAM_TYPE_PURPOSE = 'RENT') CONTRACT_BILLED_RENTS,
NVL(OKL_FUNDING_PVT.get_total_retention(chr.id),0) SUPPLIER_RETENTION_FUNDING_AMT,
NVL(OKL_FUNDING_PVT.get_chr_oec_hasbeen_funded_amt(chr.id),0) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_chr_exp_hasbeen_funded_amt(chr.id),0) EXPENSE_FUNDED_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.FEE_IDC_AMOUNT(chr.id, null),0) CONTRACT_IDC_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRIN_BALANCE(chr.id, null),0) CONTRACT_SCHEDULED_PRINC_BAL,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_INTEREST_RATE(chr.id, null),0) CONTRACT_INTEREST_RATE, -- addl para
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_TO_ACCRUE(chr.id, null),0) CONTRACT_DAYS_TO_ACCRUE, -- addl para
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_DAYS_IN_YEAR(chr.id, null),0) CONTRACT_DAYS_IN_YEAR, -- addl para
hr_org.name OPERATING_UNIT,
hr_org.short_code OPERATING_UNIT_SHORT_CODE,
status.ste_code CONTRACT_STATUS_TYPE_CODE,
status_tl.meaning CONTRACT_STATUS,
(select sum(kle_fee.amount)
FROM okl_k_lines kle_fee, okc_k_lines_b cleb_fee, OKC_STATUSES_B status_fee
WHERE cleb_fee.chr_id = khr.id
AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
and cleb_fee.dnz_chr_id = khr.id
and cleb_fee.sts_code = status_fee.code
and status_fee.ste_code not like 'CANCELLED'
AND kle_fee.fee_type NOT IN ('FINANCED', 'ROLLOVER','SECURITY_DEPOSIT')
and kle_fee.id = cleb_fee.id
and cleb_fee.lse_id = 52) CONTRACT_FEE_INCOME,
pdt.AES_ID ACC_ENGINE_TEMPL_SET_ID ,
pdt.PTL_ID CONTRACT_FIN_PROD_TEMPLATE_ID ,
pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
pdt.DESCRIPTION CONTRACT_FIN_PROD_DESC ,
pdt.REPORTING_PDT_ID REPORTING_PRODUCT_ID ,
pdt.PRODUCT_STATUS_CODE CONTRACT_FIN_PROD_STATUS_CODE ,
pdt.FROM_DATE CONTRACT_FIN_PROD_EFF_FROM_DT ,
pdt.TO_DATE CONTRACT_FIN_PROD_EFF_TO_DT ,
rpt_pdt.name REPORTING_PRODUCT,
vendor_chr.contract_number VENDOR_PROGRAM_NAME,
vendor.segment1 VENDOR_NUMBER,
vendor.vendor_name VENDOR_NAME,
vendor.vendor_type_lookup_code VENDOR_TYPE_CODE,
CPL.bill_to_site_use_id VENDOR_BILL_TO_SITE_USE_ID,
cpl.role VENDOR_PARTY_ROLE,
(select nvl(SUM(NVL(txs.total_tax,0)),0)
from okl_tax_sources txs
where txs.khr_id = khr.id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX') TOTAL_UPFRONT_TAX_ON_ASSETS,
-- null TOTAL_NUMBER_OF_ASSETS,
(select count(cleb.id)
from okc_k_lines_b cleb, okc_statuses_b status_asset
where cleb.chr_id = khr.id and cleb.dnz_chr_id = khr.id
AND khr.DEAL_TYPE NOT LIKE 'LOAN-REVOLVING'
and cleb.sts_code = status_asset.code
and status_asset.ste_code not in ('TERMINATED', 'CANCELLED')
and cleb.lse_id = 33) TOTAL_NUMBER_OF_ASSETS,
ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
ledger.ledger_id LEDGER_ID,
ledger.name LEDGER,
-- min(billing_control.EARLIEST_STRM_BILL_DATE) CONTRACT_EARLIEST_BILL_DATE,
(select min(billing_control.EARLIEST_STRM_BILL_DATE)
from OKL_K_CONTROL billing_control
WHERE billing_control.khr_id = khr.id) CONTRACT_EARLIEST_BILL_DATE,
sales_rep.name CONTRACT_SALES_REP,
legal_entity.name LEGAL_ENTITY,
cust_party.PARTY_NUMBER CUSTOMER_PARTY_NUMBER,
cust_party.party_name CUSTOMER_PARTY_NAME,
cust_party.PARTY_TYPE CUSTOMER_PARTY_TYPE,
cust_party.SIC_CODE CUSTOMER_SIC_CODE,
cust_party.HQ_BRANCH_IND CUSTOMER_HQ_BRANCH_IND,
cust_party.TAX_REFERENCE CUSTOMER_TAX_REFERENCE,
cust_party.DUNS_NUMBER CUSTOMER_DUNS_NUMBER,
cust_party.KNOWN_AS CUSTOMER_ALIAS,
cust_party.COUNTRY CUSTOMER_COUNTRY,
cust_party.ADDRESS1 CUSTOMER_ADDRESS1,
cust_party.ADDRESS2 CUSTOMER_ADDRESS2,
cust_party.ADDRESS3 CUSTOMER_ADDRESS3,
cust_party.ADDRESS4 CUSTOMER_ADDRESS4,
cust_party.CITY CUSTOMER_CITY,
cust_party.POSTAL_CODE CUSTOMER_POSTAL_CODE,
cust_party.STATE CUSTOMER_STATE,
cust_party.PROVINCE CUSTOMER_PROVINCE,
cust_party.COUNTY CUSTOMER_COUNTY,
cust_party.SIC_CODE_TYPE CUSTOMER_SIC_CODE_TYPE,
cust_party.URL CUSTOMER_URL,
cust_party.ANALYSIS_FY FINANCIAL_INFO_FISCAL_YEAR,
cust_party.FISCAL_YEAREND_MONTH CUSTOMER_FISCAL_YEAREND_MONTH,
cust_party.EMPLOYEES_TOTAL TOTAL_NUM_OF_EMPLOYEES,
cust_party.CURR_FY_POTENTIAL_REVENUE CURR_FISC_YEAR_POTENTIAL_REV,
cust_party.NEXT_FY_POTENTIAL_REVENUE NEXT_FISC_YEAR_POTENTIAL_REV,
cust_party.YEAR_ESTABLISHED CUSTOMER_YEAR_ESTABLISHED,
cust_party.GSA_INDICATOR_FLAG GEN_SERV_ADMIN_INDICATOR,
cust_party.ORGANIZATION_NAME_PHONETIC CUSTOMER_ORG_NAME_PHONETIC,
cust_party.COMPETITOR_FLAG CUSTOMER_COMPETITOR_YN,
cust_party.KNOWN_AS2 CUSTOMER_ALIAS2,
cust_party.KNOWN_AS3 CUSTOMER_ALIAS3,
cust_party.KNOWN_AS4 CUSTOMER_ALIAS4,
cust_party.KNOWN_AS5 CUSTOMER_ALIAS5,
cust_party.PRIMARY_PHONE_PURPOSE CUSTOMER_PRIM_PHONE_PURPOSE,
cust_party.PRIMARY_PHONE_LINE_TYPE CUSTOMER_PRIM_PHONE_TYPE,
cust_party.PRIMARY_PHONE_COUNTRY_CODE CUSTOMER_PRIM_PH_COUNTRY_CODE,
cust_party.PRIMARY_PHONE_AREA_CODE CUSTOMER_PRIM_PH_AREA_CODE,
cust_party.PRIMARY_PHONE_NUMBER CUSTOMER_PRIM_PHONE_NUMBER,
cust_party.PRIMARY_PHONE_EXTENSION CUSTOMER_PRIM_PHONE_EXTN,
cust_party.HOME_COUNTRY CUSTOMER_HOME_COUNTRY,
-- cust_party.status CUSTOMER_STATUS,
ar_lookup_status.meaning CUSTOMER_STATUS,
cust_accounts.PARTY_ID CUSTOMER_PARTY_ID ,
cust_accounts.ACCOUNT_NUMBER CUSTOMER_ACCOUNT_NUMBER,
cust_accounts.ATTRIBUTE_CATEGORY CUSTOMER_ACC_DFF_CATEGORY,
cust_accounts.ATTRIBUTE1 CUSTOMER_ACC_DFF1 ,
cust_accounts.ATTRIBUTE2 CUSTOMER_ACC_DFF2 ,
cust_accounts.ATTRIBUTE3 CUSTOMER_ACC_DFF3,
cust_accounts.ATTRIBUTE4 CUSTOMER_ACC_DFF4,
cust_accounts.ATTRIBUTE5 CUSTOMER_ACC_DFF5,
cust_accounts.ATTRIBUTE6 CUSTOMER_ACC_DFF6 ,
cust_accounts.ATTRIBUTE7 CUSTOMER_ACC_DFF7,
cust_accounts.ATTRIBUTE8 CUSTOMER_ACC_DFF8,
cust_accounts.ATTRIBUTE9 CUSTOMER_ACC_DFF9,
cust_accounts.ATTRIBUTE10 CUSTOMER_ACC_DFF10,
cust_accounts.ATTRIBUTE11 CUSTOMER_ACC_DFF11,
cust_accounts.ATTRIBUTE12 CUSTOMER_ACC_DFF12,
cust_accounts.ATTRIBUTE13 CUSTOMER_ACC_DFF13,
cust_accounts.ATTRIBUTE14 CUSTOMER_ACC_DFF14,
cust_accounts.ATTRIBUTE15 CUSTOMER_ACC_DFF15,
cust_accounts.ATTRIBUTE16 CUSTOMER_ACC_DFF16,
cust_accounts.ATTRIBUTE17 CUSTOMER_ACC_DFF17,
cust_accounts.ATTRIBUTE18 CUSTOMER_ACC_DFF18,
cust_accounts.ATTRIBUTE19 CUSTOMER_ACC_DFF19,
cust_accounts.ATTRIBUTE20 CUSTOMER_ACC_DFF20,
ar_lookup_acc_status.meaning CUSTOMER_ACCOUNT_STATUS,
-- cust_accounts.STATUS CUSTOMER_ACCOUNT_STATUS,
-- cust_accounts.CUSTOMER_TYPE CUSTOMER_TYPE ,
ar_lookup_cust_type.meaning CUSTOMER_TYPE ,
cust_accounts.SALES_CHANNEL_CODE CUSTOMER_SALES_CHANNEL,
cust_accounts.ACCOUNT_ESTABLISHED_DATE CUSTOMER_ACC_ESTAB_DATE,
cust_accounts.ACCOUNT_NAME CUSTOMER_ACCOUNT_NAME,
cust_accounts.COMMENTS CUSTOMER_ACCOUNT_COMMENTS,
cust_accounts.STATUS_UPDATE_DATE CUSTOMER_LAST_STATUS_UPD_DT,
cust_party_site.PARTY_SITE_ID CUSTOMER_PARTY_SITE_ID,
cust_party_site.PARTY_SITE_NUMBER CUSTOMER_PARTY_SITE_NUMBER,
cust_party_site.PARTY_SITE_NAME CUSTOMER_PARTY_SITE_NAME,
cust_party_site.LOCATION_ID CUSTOMER_PARTY_SITE_LOC_ID,
cust_party_site.IDENTIFYING_ADDRESS_FLAG CUSTOMER_IDENTIFYING_ADR_FLAG,
-- cust_party_site.STATUS CUSTOMER_PARTY_SITE_STATUS,
ar_lookup_cust_site.meaning CUSTOMER_PARTY_SITE_STATUS,
cust_party_site.ADDRESSEE CUSTOMER_PARTY_SITE_ADDRESSEE,
cust_party_site.GLOBAL_LOCATION_NUMBER CUSTOMER_SITE_GLOB_LOC_NUM,
--null CUSTOMER_PARTY_SITE_ID,
--null CUSTOMER_PARTY_SITE_NUMBER,
--null CUSTOMER_PARTY_SITE_NAME,
--null CUSTOMER_PARTY_SITE_LOC_ID,
--null CUSTOMER_IDENTIFYING_ADR_FLAG,
--null CUSTOMER_PARTY_SITE_STATUS,
--null CUSTOMER_PARTY_SITE_ADDRESSEE,
--null CUSTOMER_SITE_GLOB_LOC_NUM,
cust_party_site_use.cpsu_PARTY_SITE_USE_ID CUSTOMER_PARTY_SITE_USE_ID,
cust_party_site_use.cpsu_SITE_USE_TYPE CUSTOMER_PARTY_SITE_USE_TYPE,
cust_party_site_use.cpsu_COMMENTS CUSTOMER_PARTY_SITE_COMMENTS,
-- null CUSTOMER_PARTY_SITE_USE_ID,
-- null CUSTOMER_PARTY_SITE_USE_TYPE,
-- null CUSTOMER_PARTY_SITE_COMMENTS,
-- cust_party_site_use.STATUS CUSTOMER_PARTY_SITE_USE_STATUS,
ar_lookup_cust_site_use.meaning CUSTOMER_PARTY_SITE_USE_STATUS,
-- null CUSTOMER_PARTY_SITE_USE_STATUS,
null CONTRACT_PAST_DUE_AMT_1_30,
null CONTRACT_PAST_DUE_AMT_31_60,
null CONTRACT_PAST_DUE_AMT_61_90,
null CONTRACT_PAST_DUE_AMT_91_120,
null CONTRACT_PAST_DUE_AMT_120_Plus,
-- NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,1,30),0) CONTRACT_PAST_DUE_AMT_1_30,
--NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,31,60),0) CONTRACT_PAST_DUE_AMT_31_60,
--NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,61,90),0) CONTRACT_PAST_DUE_AMT_61_90,
--NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,91,120),0) CONTRACT_PAST_DUE_AMT_91_120,
--NVL(OKL_CNTRCT_FIN_EXTRACT_PVT.AMOUNT_DUE_REMAINING (chr.CONTRACT_NUMBER,120,12000),0) CONTRACT_PAST_DUE_AMT_120_Plus,
null CONTRACT_TOT_PAST_DUE_AMT,
-- null CONTRACT_TOT_BILLED_RECEIVABLE,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd
where txd.khr_id = khr.id ) CONTRACT_TOT_BILLED_RECEIVABLE,
/*
-- for Net Investment
DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0),
0 ) CONTRACT_UNEARNED_INCOME,
DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
'LEASEST', NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0),
0) CONTRACT_RESIDUAL_AMOUNT,
DECODE(khr.deal_type, 'LEASEDF', NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
'LEASEST',NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0),
0) CONTRACT_RENT_AMOUNT,
DECODE(khr.deal_type, 'LEASEOP', NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0),
0) CONTRACT_ACC_DEPRECIATION, --Duplicate of CONTRACT_ACCUMULATED_DEP
*/
--sechawla : calculate CONTRACT_UNEARNED_INCOME, CONTRACT_RESIDUAL_AMOUNT, CONTRACT_RENT_AMOUNT, CONTRACT_ACC_DEPRECIATION for all types of leases
NVL(Okl_Seeded_Functions_Pvt.contract_unearned_income(chr.id, null),0) CONTRACT_UNEARNED_INCOME,
NVL(Okl_Seeded_Functions_Pvt.contract_residual_amount(chr.id, null),0) CONTRACT_RESIDUAL_AMOUNT,
NVL(Okl_Seeded_Functions_Pvt.contract_rent_amount(chr.id, null),0) CONTRACT_RENT_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_ACC_DEPRECIATION(chr.id, null),0) CONTRACT_ACC_DEPRECIATION,
NVL(OKL_FUNDING_PVT.get_amount_prefunded(chr.id),0) CONTRACT_AMOUNT_PREFUNDED,
NVL(OKL_FUNDING_PVT.get_chr_funded_adjs(chr.id),0) CONTRACT_FUNDED_ADJS,
NVL(OKL_FUNDING_PVT.get_amount_manu_disb(chr.id),0) CONTRACT_AMOUNT_MANU_DISB,
l_last_updated_by CREATED_BY,
sysdate CREATION_DATE,
l_last_updated_by LAST_UPDATED_BY,
sysdate LAST_UPDATE_DATE,
l_last_update_login LAST_UPDATE_LOGIN,
l_program_app_id PROGRAM_APPLICATION_ID,
l_program_id PROGRAM_ID,
l_program_login_id PROGRAM_LOGIN_ID,
sysdate PROGRAM_UPDATE_DATE
FROM
OKC_K_HEADERS_ALL_B chr,
OKL_K_HEADERS khr,
OKL_PARALLEL_PROCESSES opp,
HR_OPERATING_UNITS hr_org,
OKC_STATUSES_B status,
OKC_STATUSES_TL status_tl,
OKC_K_PARTY_ROLES_B cust_party_roles,
fnd_lookup_values_vl lookup_book_class,
HZ_PARTIES cust_party,
HZ_CUST_ACCOUNTS cust_accounts,
OKL_PRODUCTS pdt,
OKL_PRODUCTS rpt_pdt,
OKC_K_HEADERS_ALL_B vendor_chr,
okc_rule_groups_b rgd,
okc_k_party_roles_v cpl,
po_vendors vendor,
OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
GL_LEDGERS ledger,
okc_contacts sales_rep_contact,
okx_salesreps_v sales_rep,
xle_entity_profiles legal_entity,
hz_party_sites cust_party_site,
-- hz_party_site_uses cust_party_site_use,
(select cpsu_party_site_use_id,
cpsu_party_site_id,
cpsu_SITE_USE_TYPE,
cpsu_status,
cpsu_comments
from (
select
cust_party_site_use_n.party_site_use_id cpsu_party_site_use_id, --sechawla changed party_site_id to party_site_use_id
cust_party_site_use_n.party_site_id cpsu_party_site_id,
cust_party_site_use_n.SITE_USE_TYPE cpsu_site_use_type,
cust_party_site_use_n.status cpsu_status,
cust_party_site_use_n.comments cpsu_comments,
row_number() over ( partition by cust_party_site_use_n.party_site_id
order by NVL(cust_party_site_use_n.status,'A') ASC) cpsu_stat_priority
from hz_party_site_uses cust_party_site_use_n
where cust_party_site_use_n.SITE_USE_TYPE = 'BILL_TO'
) where cpsu_stat_priority = 1) cust_party_site_use,
hz_cust_site_uses_all cust_cust_site_use,
hz_cust_acct_sites_all cust_cust_acct_site,
ar_lookups ar_lookup_status,
ar_lookups ar_lookup_acc_status,
ar_lookups ar_lookup_cust_type,
ar_lookups ar_lookup_cust_site,
ar_lookups ar_lookup_cust_site_use
WHERE chr.id = khr.id
AND chr.SCS_CODE = 'LEASE'
AND opp.object_type = 'CONTRACT_FIN_EXTRACT'
AND opp.object_value = chr.contract_number
AND opp.assigned_process = p_assigned_process
AND opp.khr_id = chr.id
AND hr_org.organization_id = chr.authoring_org_id
AND status.code = chr.sts_code
AND status_tl.code = status.code
AND status_tl.language = USERENV('LANG')
AND cust_party.party_type in ( 'PERSON','ORGANIZATION')
AND cust_party.party_id = cust_party_roles.object1_id1
AND cust_party_roles.object1_id2 = '#'
AND cust_party_roles.jtot_object1_code = 'OKX_PARTY'
AND cust_party_roles.rle_code = 'LESSEE'
AND cust_party_roles.chr_id = chr.id
AND cust_party_roles.dnz_chr_id = chr.id
AND cust_accounts.cust_account_id = chr.cust_acct_id
AND cust_accounts.party_id = cust_party.party_id
AND khr.pdt_id = pdt.id
AND pdt.REPORTING_PDT_ID = rpt_pdt.id(+)
AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
AND vendor_chr.id (+) = khr.khr_id
AND vendor_chr.scs_code (+) = 'PROGRAM'
AND rgd.chr_id(+)= vendor_chr.id
AND rgd.dnz_chr_id(+) = vendor_chr.id
AND rgd.cle_id IS NULL
AND rgd.rgd_code(+) = 'LAVENB'
AND vendor_chr.id = cpl.chr_id (+)
AND vendor_chr.id = cpl.dnz_chr_id(+)
AND cpl.rle_code (+) = 'OKL_VENDOR'
and cpl.object1_id1 = vendor.vendor_id (+)
AND acct_sys_opts.org_id = chr.authoring_org_id
AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
AND sales_rep_contact.DNZ_CHR_ID (+) = khr.id
AND sales_rep.id1 (+) = sales_rep_contact.object1_id1
AND sales_rep.id2 (+) = sales_rep_contact.object1_id2
and sales_rep_contact.cro_code (+) = 'SALESPERSON'
AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
and cust_party.party_id = cust_party_site.party_id
AND cust_cust_site_use.site_use_id = chr.bill_to_site_use_id
AND cust_cust_site_use.cust_acct_site_id = cust_cust_acct_site.cust_acct_site_id
--and cust_cust_site_use.SITE_USE_CODE = 'BILL_TO'
and cust_cust_acct_site.party_site_id = cust_party_site.party_site_id
and cust_party_site.party_site_id = cust_party_site_use.cpsu_party_site_id
--and cust_party_site_use.SITE_USE_TYPE = 'BILL_TO'
AND ar_lookup_status.lookup_type(+) = 'HZ_CPUI_REGISTRY_STATUS'
AND ar_lookup_status.lookup_code(+) = cust_party.status
AND ar_lookup_acc_status.lookup_type(+) = 'CODE_STATUS'
AND ar_lookup_acc_status.lookup_code(+) = cust_accounts.STATUS
AND ar_lookup_cust_type.lookup_type(+) = 'CUSTOMER_TYPE'
AND ar_lookup_cust_type.lookup_code(+) = cust_accounts.CUSTOMER_TYPE
AND ar_lookup_cust_site.lookup_type(+) = 'REGISTRY_STATUS'
AND ar_lookup_cust_site.lookup_code(+) = cust_party_site.STATUS
AND ar_lookup_cust_site_use.lookup_type(+) = 'REGISTRY_STATUS'
AND ar_lookup_cust_site_use.lookup_code(+) = cust_party_site_use.cpsu_STATUS
-- parameters section begin
/*
--sechawla : don't need the following as these paramater matching has been already
--been done in Master program - OKL_CNTRCT_FIN_EXT_MASTER_PVT
AND chr.AUTHORING_ORG_ID = P_OPERATING_UNIT
AND chr.START_DATE >= P_START_DATE_FROM
AND chr.START_DATE <= P_START_DATE_TO
AND khr.DEAL_TYPE like nvl(P_BOOK_CLASS, khr.DEAL_TYPE)
AND pdt.ID like nvl(P_LEASE_PRODUCT, pdt.ID)
AND chr.sts_code like nvl(P_CONTRACT_STATUS, chr.sts_code)
AND (P_END_DATE_FROM IS NULL OR chr.END_DATE >= P_END_DATE_FROM)
AND (P_END_DATE_TO IS NULL OR chr.END_DATE <= P_END_DATE_TO)
*/
--there is no ID defined in the value set OKS_CUSTOMER_NUMBER. value is 'party_number'.
AND cust_party.PARTY_NUMBER like nvl(P_CUSTOMER_NUMBER, cust_party.PARTY_NUMBER)
--sechawla : P_CUSTOMER_NAME is hz_parties.party_id. value set: OKL_CUSTOMERS
AND cust_party.party_id = nvl(P_CUSTOMER_NAME, cust_party.party_id)
AND nvl(cust_party.SIC_CODE,'XXX') like nvl(P_SIC_CODE, nvl(cust_party.SIC_CODE,'XXX'))
--sechawla : p_vendor_number -> value set 'OKL_VENDORS' -> returns PO_VENDORS.VENDOR_ID
--Need nvl, as every contract may not have vendor_id (program vendor)
AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NUMBER,nvl(vendor.vendor_id,-9999))
--sechawla : P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
AND nvl(vendor.vendor_id,-9999) = nvl(P_VENDOR_NAME,nvl(vendor.vendor_id,-9999))
-- P_SALES_CHANNEL -> value set QP_SALES_CHANNEL_CODE -> returns oe_lookups.lookup_code
AND nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX') like nvl(P_SALES_CHANNEL, nvl(cust_accounts.SALES_CHANNEL_CODE,'XXX'))
-- AND CONTRACT_ACCRUAL_STATUS like nvl(P_GEN_ACCRUAL, CONTRACT_ACCRUAL_STATUS)
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))
AND ( (P_TERMINATE_DATE_FROM IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED >= P_TERMINATE_DATE_FROM) )
AND ( (P_TERMINATE_DATE_TO IS NULL) OR (chr.DATE_TERMINATED IS NOT NULL AND chr.DATE_TERMINATED <= P_TERMINATE_DATE_TO) )
-- parameters section end
);
P_DELETE_DATA_YN IN VARCHAR2,
p_num_processes IN NUMBER,
p_assigned_process IN VARCHAR2
)
IS
l_api_version NUMBER := 1;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID) --sechawla : why Null if -1 ?
FROM dual;
SELECT NAME
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID;--MOAC- Concurrent request
write_to_log('P_DELETE_DATA_YN '||P_DELETE_DATA_YN);
P_DELETE_DATA_YN => P_DELETE_DATA_YN,
p_num_processes => p_num_processes,
p_assigned_process => p_assigned_process);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
write_to_log('Deleted '||l_del_row_count||' rows from OKL_PARALLEL_PROCESSES, for assigned process '||p_assigned_process);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
write_to_log('Deleted '||sql%rowcount||' rows from OKL_PARALLEL_PROCESSES, for assigned process '||p_assigned_process);
SELECT
SUM(NVL(ps.AMOUNT_DUE_REMAINING,0))
From
ar_payment_schedules_all ps,
ra_customer_trx_lines_all ctl
WHERE
ctl.INTERFACE_LINE_ATTRIBUTE6 = p_CONTRACT_NUMBER
AND ctl.CUSTOMER_TRX_ID = ps.CUSTOMER_TRX_ID
AND ps.AMOUNT_DUE_REMAINING > 0
AND trunc(sysdate) > ps.DUE_DATE
AND (trunc(sysdate) - ps.DUE_DATE) between p_from_days and p_to_days;
SELECT
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
FROM ra_customer_trx_all cust_trx,
ra_customer_trx_lines_all cust_trx_lines,
ar_payment_schedules_all ar
WHERE cust_trx.customer_trx_id = cust_trx_lines.customer_trx_id
AND cust_trx_lines.line_type = 'LINE'
AND cust_trx_lines.interface_line_attribute1 IS NULL
AND cust_trx_lines.customer_trx_id = ar.customer_trx_id
AND cust_trx.customer_trx_id = ar.customer_trx_id
AND ar.CLASS = 'INV'
and ar.status = 'OP'
AND cust_trx_lines.interface_line_context IN ( 'OKL_CONTRACTS','OKL_MANUAL')
and cust_trx_lines.interface_line_attribute6 = p_contract_number
and trunc(sysdate) > ar.DUE_DATE
AND (trunc(sysdate) - ar.DUE_DATE) between p_from_days and p_to_days;
SELECT transaction_date
FROM okl_trx_contracts_all
WHERE khr_id = cp_khr_id
AND try_id = 252879000775029305619701078073046135725 --'Booking' transaction id
AND tcn_type = 'BKG'
ORDER BY transaction_date;
SELECT trx.accrual_status_yn accrual_status
FROM OKL_TRX_CONTRACTS trx, OKL_REPRESENTATIONS_V rep
WHERE trx.tcn_type = 'ACL'
AND trx.khr_id = p_chr_id
AND trx.tsu_code = 'PROCESSED'
AND trx.representation_code = rep.representation_code
AND rep.representation_type='PRIMARY'
AND trx.date_transaction_occurred = (SELECT MAX(tcn.date_transaction_occurred)
FROM OKL_TRX_CONTRACTS tcn
WHERE tcn.khr_id = trx.khr_id
AND tcn.tcn_type = trx.tcn_type
AND tcn.tsu_code = trx.tsu_code)
AND trx.trx_number = (select to_char(max(to_number(t.trx_number)))
from okl_trx_contracts t
where t.khr_id = trx.khr_id
and t.tsu_code=trx.tsu_code
and t.tcn_type=trx.tcn_type);
FUNCTION delete_report_data return BOOLEAN
IS
l_row_count NUMBER;
fnd_file.put_line(fnd_file.log,'P_DELETE_DATA_YN :'||P_DELETE_DATA_YN);
IF P_DELETE_DATA_YN = 'YES' THEN
DELETE FROM OKL_CNTRCT_FIN_EXTRACT_T
WHERE REQUEST_ID = l_parent_request_id;
END delete_report_data;