[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| This procedure fetches data for Contract Line Financial Report and inserts
| into table OKL_CNTRCT_LINE_FIN_EXTRACT_T
|
| CALLED FROM
| Concurrent Program "Child Program -- Contract Line Financial Report"
|
| CALLS PROCEDURES/FUNCTIONS
|
| KNOWN ISSUES
|
| NOTES Total 3 INSERT statements.
| IF P_FA_INFO_YN = 'Y' THEN
| INSERT for ACTIVE contracts
| INSERT for INACTIVE contracts
| ELSE i.e P_FA_INFO_YN = 'N' THEN
| INSERT for ACTIVE AND INACTIVE contracts
| END IF
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 30-Oct-2008 DJANASWA Created
|
*=======================================================================*/
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,
P_START_DATE_FROM IN VARCHAR2,
P_START_DATE_TO IN VARCHAR2,
P_BOOK_CLASS IN VARCHAR2,
P_LEASE_PRODUCT IN VARCHAR2,
P_CONTRACT_NUMBER IN VARCHAR2,
P_CONTRACT_STATUS IN VARCHAR2,
P_CONTRACT_LINE_STATUS IN VARCHAR2,
P_CONTRACT_LINE_TYPE IN VARCHAR2,
P_CUSTOMER_NAME IN VARCHAR2,
P_CUSTOMER_NUMBER IN VARCHAR2,
P_VENDOR_NAME IN VARCHAR2,
P_VENDOR_NUMBER IN VARCHAR2,
P_FA_INFO_YN IN VARCHAR2,
P_TAX_BOOK IN VARCHAR2,
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);
write_to_log('Inserting ACTIVE contract lines P_FA_INFO_YN like Y');
INSERT INTO OKL_CNTRCT_LINE_FIN_EXTRACT_T (
REQUEST_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_TERM_DURATION,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_LEGAL_ENTITY_ID,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_REPORTING_PRODUCT_ID,
CONTRACT_REPORTING_PRODUCT,
INTEREST_CALCLATION_BASIS_CODE,
REVENUE_RECOGNTION_METHOD_CODE,
INTEREST_CALCULATION_BASIS,
REVENUE_RECOGNITION_METHOD,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
LEGAL_ENTITY,
CNTRCT_CUST_PARTY_NUMBER,
CNTRCT_CUSTOMER_PARTY_NAME,
CNTRCT_CUST_PARTY_ID,
CNTRCT_CUST_ACOUNT_ID,
CNTRCT_CUST_ACCT_NUMBER,
CNTRCT_CUST_PARTY_SITE_ID,
CNTRCT_CUST_PARTY_SITE_NUM,
CNTRCT_CUST_PARTY_SITE_NAME,
CNTRCT_CUST_PARTY_SITE_LOC_ID,
CNTRCT_CUST_PARTY_SITE_USE_ID,
CONTRACT_INTEREST_INDEX_ID,
CONTRACT_INTEREST_INDEX,
CONTRACT_LINE_ID,
CONTRACT_LINE_TYPE_ID,
CONTRACT_LINE_TYPE_CODE,
CONTRACT_LINE_TYPE_NAME,
CONTRACT_LINE_STATUS_CODE,
CONTRACT_LINE_STATUS,
CONTRACT_LINE_DFF_CATEGORY,
CONTRACT_LINE_DFF1,
CONTRACT_LINE_DFF2,
CONTRACT_LINE_DFF3,
CONTRACT_LINE_DFF4,
CONTRACT_LINE_DFF5,
CONTRACT_LINE_DFF6,
CONTRACT_LINE_DFF7,
CONTRACT_LINE_DFF8,
CONTRACT_LINE_DFF9,
CONTRACT_LINE_DFF10,
CONTRACT_LINE_DFF11,
CONTRACT_LINE_DFF12,
CONTRACT_LINE_DFF13,
CONTRACT_LINE_DFF14,
CONTRACT_LINE_DFF15,
ASSET_DATE_DELIVERY_EXPECTED,
ASSET_DATE_FUNDING_EXPECTED,
ASSET_RESIDUAL_PERCENT,
ASSET_RES_GUARANTOR_TYPE_CODE,
ASSET_RES_GUARANTOR_TYPE,
ASSET_RESIDUAL_AMT_GUARANTEE,
ASSET_RESIDUAL_AMOUNT,
CONTRACT_LINE_NAME,
ASSET_UPFRONT_TAX,
ASSET_BILL_TO_SITE_USE_ID,
ASSET_BILL_TO_ADDRESS,
ASSET_BILLING_PAYMENT_METHOD,
ASSET_TERMINATION_DATE,
-- FA
FIXED_ASSET_LINE_ID,
FA_MODEL_NUMBER,
FA_MANUFACTURER_NAME,
FA_ASSET_LOCATION_NAME,
FA_ASSET_LOCATION_ID,
FA_ASSET_KEY_ID,
FA_ASSET_ID,
FA_ASSET_KEY,
ASSET_BILLING_BANK_ACCOUNT,
ASSET_BILLING_BANK,
INV_SUPPLIER_INVOICE_NUMBER,
INV_SUPPLIER_INVOICE_LINE_ID,
INV_SUPPLIER_INVOICE_DATE,
INV_SUPPLR_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE,
--SUPPLIER_VENDOR_NAME,
--SUPPLIER_VENDOR_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
--FEE
FEE_TYPE_CODE,
FEE_TYPE,
FEE_OR_SERVICE_NAME,
--FEE_SUPPLIER_ID,
--FEE_SUPPLIER,
LINE_SUPPLIER_ID,
LINE_SUPPLIER,
LINE_SUPPLIER_NUMBER,
FEE_OR_SERVICE_EFFECTIVE_FROM,
FEE_OR_SERVICE_EFFECTIVE_TO,
FEE_OR_SERVICE_TOTAL_AMOUNT,
FEE_OR_SERVICE_FREQUENCY_CODE,
FEE_OR_SERVICE_FREQUENCY,
FEE_OR_SERV_NUMBER_OF_PERIODS,
FEE_OR_SERV_AMOUNT_PER_PERIOD,
FEE_ROLLOVER_QUOTE_ID,
FEE_ROLLOVER_QUOTE_NUMBER,
FEE_ROLLOVER_CONTRACT_NUMBER,
FEE_INITIAL_DIRECT_COST,
FEE_TRX_TAX_YN,
MODEL_LINE_ID,
INVENTORY_UNIT_COST,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_ORG_ID,
INVENTORY_TOTAL_UNITS,
INVENTORY_ITEM_NAME,
INVENTORY_ITEM_DESCRIPTION,
IB_INSTALL_SITE_ID,
IB_INSTALL_SITE_NUMBER,
IB_INSTALL_SITE_USE_ID,
IB_INSTALL_SITE,
IB_SERIAL_NUMBER,
LINE_PTH_EVG_PAYOUT_BASIS_CODE,
LINE_PTH_EVG_PAYOUT_BASIS,
--LINE_PTH_EVG_START_DATE,
LINE_PTH_EVG_PYOUT_FORMULA_ID,
LINE_PTH_EVG_PYOUT_FORMULA,
LINE_PTH_EVG_STREAM_TYPE_ID,
LINE_PTH_EVG_STREAM_TYPE_CODE,
LINE_PTH_EVG_STREAM_TYPE,
LINE_PTH_BASE_START_DATE,
LINE_PTH_BASE_PAY_BASIS_CODE,
LINE_PTH_BASE_PAYOUT_BASIS,
LINE_PTH_BASE_STREAM_TYPE_ID,
LINE_PTH_BASE_STREAM_TYPE_CODE,
LINE_PTH_BASE_STREAM_TYPE,
USAGE_TYPE_CODE,
USAGE_FIXED_QUANTITY,
USAGE_PRICE_LIST_ID,
USAGE_PRICE_LIST_NAME,
USAGE_PERIOD_CODE,
USAGE_PERIOD,
USAGE_NO_OF_PERIOD,
USAGE_MINIMUM_QTY,
USAGE_LEVEL_FLAG,
USAGE_DEFAULT_QUANTITY,
USAGE_BASE_READING_UOM_CODE,
USAGE_BASE_READING_UOM,
USAGE_BASE_READING,
USAGE_AVG_MONTHLY_COUNTER_FLAG,
USAGE_TYPE,
SERVICE_CONTRACT_NUMBER,
--SERVICE_SUPPLIER,
SERVICE_PAYMENT_TYPE,
INSURANCE_QUOTE_NUMBER,
INSURANCE_POLICY_ID,
INSURANCE_POLICY_NUMBER,
INSURANCE_POLICY_TYPE_CODE,
INSURANCE_POLICY_TYPE,
INSURANCE_POLICY_STATUS_CODE,
INSURANCE_POLICY_STATUS,
INSURANCE_PROVIDER,
INSURANCE_POLICY_LOCATION,
INSURANCE_PAYMENT_FREQUENCY,
INS_POLICY_LESSOR_INSURED_YN,
INS_POLICY_LESSOR_PAYEE_YN,
INSURANCE_POLICY_EFF_FROM,
INSURANCE_POLICY_EFF_TO,
INSURANCE_PRODUCT_ID,
INSURANCE_PRODUCT,
--INSURANCE_FACTOR_CODE,
-- INSURANCE_FACTOR,
INSURANCE_FACTOR_VALUE,
INSURANCE_NAME_OF_INSURED,
INSURANCE_COVERED_AMOUNT,
INSURANCE_CALC_PREMIUM,
INSURANCE_ACTIVATION_DATE,
INSURANCE_Cancellation_date,
INSURANCE_SALES_REP,
INSURANCE_TOT_PREMIUM,
--corp_book
FA_CORP_BOOK_TYPE_CODE,
FA_CORPORATE_BOOK,
FA_CORP_SALVAGE_VALUE,
FA_CORP_SALVAGE_VALUE_PERCENT,
FA_CORP_SAL_VALUE_BASIS_CODE,
FA_CORP_SAL_VALUE_BASIS,
FA_CORP_DEP_METHOD_ID,
FA_CORP_DEP_METHOD_CODE,
FA_CORP_DEP_METHOD_NAME,
FA_CORP_DEP_LIFE_IN_MONTHS,
FA_CORP_DEP_RATE,
FA_CORP_CATEGORY_ID,
FA_CORP_CATEGORY,
FA_CORP_ORIG_COST,
FA_CORP_DEP_COST,
FA_CORP_DATE_IN_SERVICE,
--taxbook
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_DEP_RATE,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
--mg_bbok
FA_MG_BOOK_TYPE_CODE,
FA_MG_BOOK,
FA_MG_SAL_VALUE_BASIS_CODE,
FA_MG_SAL_VALUE_BASIS,
FA_MG_SALVAGE_VALUE,
FA_MG_SALVAGE_VALUE_PERCENT,
FA_MG_DEP_RATE,
FA_MG_DEP_LIFE_IN_MONTHS,
FA_MG_DEP_METHOD_CODE,
FA_MG_DEP_METHOD_ID,
FA_MG_DEP_METHOD_NAME,
FA_MG_ORIG_COST,
FA_MG_DEP_COST,
FA_MG_DATE_IN_SERVICE,
--salestax
ASSET_ST_TRANSFER_OF_TITLE,
ASSET_ST_SALE_AND_LEASE_BACK,
ASSET_ST_PURCHASE_OF_LEASE,
ASSET_ST_INTENDED_USE,
ASSET_ST_EQUIPMENT_AGE,
ASSET_ST_ASSET_UPFRONT_TAX,
--propertytax
ASSET_PROP_TAX_APPLICABLE,
ASSET_PROP_TAX_LESSEE_REPORT,
ASSET_PROP_TAX_BILL_METHOD,
--asset_filing_lien
ASSET_FILING_LIEN_TYPE_CODE,
ASSET_FILING_LIEN_TYPE,
ASSET_FILING_LIEN_NUMBER,
ASSET_FILING_LIEN_DATE,
ASSET_FILING_LIEN_STATUS_CODE,
ASSET_FILING_LIEN_STATUS,
ASSET_FILING_JURISDICTION,
ASSET_FILING_SUB_JURISDICTION,
ASSET_FILING_LIEN_EXP_DATE,
ASSET_FILING_LIEN_CONT_NUMBER,
ASSET_FILING_LIEN_CONT_DATE,
ASSET_FILING_LIENHOLDER,
--ASSET_FILING_TITLE
ASSET_FILING_TITLE_TYPE_CODE,
ASSET_FILING_TITLE_TYPE,
ASSET_FILING_TITLE_NUMBER,
ASSET_FILING_TITLE_ISSUER,
ASSET_FILING_TITLE_DATE,
ASSET_FILING_TITLE_CUSTODIAN,
ASSET_FILING_TITLE_REGIS_NUM,
ASSET_FILING_TITLE_LOCATION,
ASSET_FILING_TITLE_REGIS_LOC,
ASSET_FILING_PAYEE_SITE,
ASSET_FILING_REGIS_EXP_DATE,
--formulas
TOTAL_ASSET_CAP_SERVICE_AMOUNT,
LINE_ACCUMULATED_DEPRECIATION,
LINE_CAPITALIZED_REDUCTION,
TOTAL_ASSET_CAP_FEE_AMOUNT,
LINE_CAPITALIZED_INTEREST,
LINE_DISCOUNT,
LINE_ASSET_COST,
LINE_UNBILLED_DUE_AMOUNT,
LINE_OEC,
TOTAL_ASSET_TRADEIN_AMOUNT,
TOTAL_ACTIVE_LINE_FINANCED_AMT,
LINE_RESIDUAL_AMOUNT,
LINE_RENT_AMOUNT,
LINE_BILLED_RENTS,
LINE_BILLED_RENTS_FUNC,
LINE_BILLED_NON_RENTS,
LINE_BILLED_NON_RENTS_FUNC,
TOTAL_ASSET_FINANCED_FEE_AMT,
TOTAL_ASSET_ROLLOVER_FEE_AMT,
LINE_CAP_AMOUNT,
LINE_NET_INVESTMENT,
LINE_NET_INVESTMENT_FUNC,
-- LINE_TOT_BILLED_RECEIVABLE,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
TOTAL_ASSET_ADDON_COST,
TOTAL_ASSET_SUBSIDY_AMOUNT,
TOTAL_ASSET_SUBSIDY_OVRD_AMT,
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_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_TERM_DURATION,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_LEGAL_ENTITY_ID,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_REPORTING_PRODUCT_ID,
CONTRACT_REPORTING_PRODUCT,
INTEREST_CALCLATION_BASIS_CODE,
REVENUE_RECOGNTION_METHOD_CODE,
INTEREST_CALCULATION_BASIS,
REVENUE_RECOGNITION_METHOD,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
LEGAL_ENTITY,
CNTRCT_CUST_PARTY_NUMBER,
CNTRCT_CUSTOMER_PARTY_NAME,
CNTRCT_CUST_PARTY_ID,
CNTRCT_CUST_ACOUNT_ID,
CNTRCT_CUST_ACCT_NUMBER,
CNTRCT_CUST_PARTY_SITE_ID,
CNTRCT_CUST_PARTY_SITE_NUM,
CNTRCT_CUST_PARTY_SITE_NAME,
CNTRCT_CUST_PARTY_SITE_LOC_ID,
CNTRCT_CUST_PARTY_SITE_USE_ID,
CONTRACT_INTEREST_INDEX_ID,
CONTRACT_INTEREST_INDEX,
CONTRACT_LINE_ID,
CONTRACT_LINE_TYPE_ID,
CONTRACT_LINE_TYPE_CODE,
CONTRACT_LINE_TYPE_NAME,
CONTRACT_LINE_STATUS_CODE,
CONTRACT_LINE_STATUS,
CONTRACT_LINE_DFF_CATEGORY,
CONTRACT_LINE_DFF1,
CONTRACT_LINE_DFF2,
CONTRACT_LINE_DFF3,
CONTRACT_LINE_DFF4,
CONTRACT_LINE_DFF5,
CONTRACT_LINE_DFF6,
CONTRACT_LINE_DFF7,
CONTRACT_LINE_DFF8,
CONTRACT_LINE_DFF9,
CONTRACT_LINE_DFF10,
CONTRACT_LINE_DFF11,
CONTRACT_LINE_DFF12,
CONTRACT_LINE_DFF13,
CONTRACT_LINE_DFF14,
CONTRACT_LINE_DFF15,
ASSET_DATE_DELIVERY_EXPECTED,
ASSET_DATE_FUNDING_EXPECTED,
ASSET_RESIDUAL_PERCENT,
ASSET_RES_GUARANTOR_TYPE_CODE,
ASSET_RES_GUARANTOR_TYPE,
ASSET_RESIDUAL_AMT_GUARANTEE,
ASSET_RESIDUAL_AMOUNT,
CONTRACT_LINE_NAME,
ASSET_UPFRONT_TAX,
ASSET_BILL_TO_SITE_USE_ID,
ASSET_BILL_TO_ADDRESS,
ASSET_BILLING_PAYMENT_METHOD,
ASSET_TERMINATION_DATE,
-- FA
FIXED_ASSET_LINE_ID,
FA_MODEL_NUMBER,
FA_MANUFACTURER_NAME,
FA_ASSET_LOCATION_NAME,
FA_ASSET_LOCATION_ID,
FA_ASSET_KEY_ID,
FA_ASSET_ID,
FA_ASSET_KEY,
ASSET_BILLING_BANK_ACCOUNT,
ASSET_BILLING_BANK,
INV_SUPPLIER_INVOICE_NUMBER,
INV_SUPPLIER_INVOICE_LINE_ID,
INV_SUPPLIER_INVOICE_DATE,
INV_SUPPLR_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE,
--SUPPLIER_VENDOR_NAME,
--SUPPLIER_VENDOR_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
--FEE
FEE_TYPE_CODE,
FEE_TYPE,
FEE_OR_SERVICE_NAME,
--FEE_SUPPLIER_ID,
--FEE_SUPPLIER,
LINE_SUPPLIER_ID,
LINE_SUPPLIER,
LINE_SUPPLIER_NUMBER,
FEE_OR_SERVICE_EFFECTIVE_FROM,
FEE_OR_SERVICE_EFFECTIVE_TO,
FEE_OR_SERVICE_TOTAL_AMOUNT,
FEE_OR_SERVICE_FREQUENCY_CODE,
FEE_OR_SERVICE_FREQUENCY,
FEE_OR_SERV_NUMBER_OF_PERIODS,
FEE_OR_SERV_AMOUNT_PER_PERIOD,
FEE_ROLLOVER_QUOTE_ID,
FEE_ROLLOVER_QUOTE_NUMBER,
FEE_ROLLOVER_CONTRACT_NUMBER,
FEE_INITIAL_DIRECT_COST,
FEE_TRX_TAX_YN,
MODEL_LINE_ID,
INVENTORY_UNIT_COST,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_ORG_ID,
INVENTORY_TOTAL_UNITS,
INVENTORY_ITEM_NAME,
INVENTORY_ITEM_DESCRIPTION,
IB_INSTALL_SITE_ID,
IB_INSTALL_SITE_NUMBER,
IB_INSTALL_SITE_USE_ID,
IB_INSTALL_SITE,
IB_SERIAL_NUMBER,
LINE_PTH_EVG_PAYOUT_BASIS_CODE,
LINE_PTH_EVG_PAYOUT_BASIS,
--LINE_PTH_EVG_START_DATE,
LINE_PTH_EVG_PYOUT_FORMULA_ID,
LINE_PTH_EVG_PYOUT_FORMULA,
LINE_PTH_EVG_STREAM_TYPE_ID,
LINE_PTH_EVG_STREAM_TYPE_CODE,
LINE_PTH_EVG_STREAM_TYPE,
LINE_PTH_BASE_START_DATE,
LINE_PTH_BASE_PAY_BASIS_CODE,
LINE_PTH_BASE_PAYOUT_BASIS,
LINE_PTH_BASE_STREAM_TYPE_ID,
LINE_PTH_BASE_STREAM_TYPE_CODE,
LINE_PTH_BASE_STREAM_TYPE,
USAGE_TYPE_CODE,
USAGE_FIXED_QUANTITY,
USAGE_PRICE_LIST_ID,
USAGE_PRICE_LIST_NAME,
USAGE_PERIOD_CODE,
USAGE_PERIOD,
USAGE_NO_OF_PERIOD,
USAGE_MINIMUM_QTY,
USAGE_LEVEL_FLAG,
USAGE_DEFAULT_QUANTITY,
USAGE_BASE_READING_UOM_CODE,
USAGE_BASE_READING_UOM,
USAGE_BASE_READING,
USAGE_AVG_MONTHLY_COUNTER_FLAG,
USAGE_TYPE,
SERVICE_CONTRACT_NUMBER,
--SERVICE_SUPPLIER,
SERVICE_PAYMENT_TYPE,
INSURANCE_QUOTE_NUMBER,
INSURANCE_POLICY_ID,
INSURANCE_POLICY_NUMBER,
INSURANCE_POLICY_TYPE_CODE,
INSURANCE_POLICY_TYPE,
INSURANCE_POLICY_STATUS_CODE,
INSURANCE_POLICY_STATUS,
INSURANCE_PROVIDER,
INSURANCE_POLICY_LOCATION,
INSURANCE_PAYMENT_FREQUENCY,
INS_POLICY_LESSOR_INSURED_YN,
INS_POLICY_LESSOR_PAYEE_YN,
INSURANCE_POLICY_EFF_FROM,
INSURANCE_POLICY_EFF_TO,
INSURANCE_PRODUCT_ID,
INSURANCE_PRODUCT,
--INSURANCE_FACTOR_CODE,
-- INSURANCE_FACTOR,
INSURANCE_FACTOR_VALUE,
INSURANCE_NAME_OF_INSURED,
INSURANCE_COVERED_AMOUNT,
INSURANCE_CALC_PREMIUM,
INSURANCE_ACTIVATION_DATE,
INSURANCE_Cancellation_date,
INSURANCE_SALES_REP,
INSURANCE_TOT_PREMIUM,
--corp_book
FA_CORP_BOOK_TYPE_CODE,
FA_CORPORATE_BOOK,
FA_CORP_SALVAGE_VALUE,
FA_CORP_SALVAGE_VALUE_PERCENT,
FA_CORP_SAL_VALUE_BASIS_CODE,
FA_CORP_SAL_VALUE_BASIS,
FA_CORP_DEP_METHOD_ID,
FA_CORP_DEP_METHOD_CODE,
FA_CORP_DEP_METHOD_NAME,
FA_CORP_DEP_LIFE_IN_MONTHS,
FA_CORP_DEP_RATE,
FA_CORP_CATEGORY_ID,
FA_CORP_CATEGORY,
FA_CORP_ORIG_COST,
FA_CORP_DEP_COST,
FA_CORP_DATE_IN_SERVICE,
--taxbook
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_DEP_RATE,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
--mg_bbok
FA_MG_BOOK_TYPE_CODE,
FA_MG_BOOK,
FA_MG_SAL_VALUE_BASIS_CODE,
FA_MG_SAL_VALUE_BASIS,
FA_MG_SALVAGE_VALUE,
FA_MG_SALVAGE_VALUE_PERCENT,
FA_MG_DEP_RATE,
FA_MG_DEP_LIFE_IN_MONTHS,
FA_MG_DEP_METHOD_CODE,
FA_MG_DEP_METHOD_ID,
FA_MG_DEP_METHOD_NAME,
FA_MG_ORIG_COST,
FA_MG_DEP_COST,
FA_MG_DATE_IN_SERVICE,
--salestax
ASSET_ST_TRANSFER_OF_TITLE,
ASSET_ST_SALE_AND_LEASE_BACK,
ASSET_ST_PURCHASE_OF_LEASE,
ASSET_ST_INTENDED_USE,
ASSET_ST_EQUIPMENT_AGE,
ASSET_ST_ASSET_UPFRONT_TAX,
--propertytax
ASSET_PROP_TAX_APPLICABLE,
ASSET_PROP_TAX_LESSEE_REPORT,
ASSET_PROP_TAX_BILL_METHOD,
--asset_filing_lien
ASSET_FILING_LIEN_TYPE_CODE,
ASSET_FILING_LIEN_TYPE,
ASSET_FILING_LIEN_NUMBER,
ASSET_FILING_LIEN_DATE,
ASSET_FILING_LIEN_STATUS_CODE,
ASSET_FILING_LIEN_STATUS,
ASSET_FILING_JURISDICTION,
ASSET_FILING_SUB_JURISDICTION,
ASSET_FILING_LIEN_EXP_DATE,
ASSET_FILING_LIEN_CONT_NUMBER,
ASSET_FILING_LIEN_CONT_DATE,
ASSET_FILING_LIENHOLDER,
--ASSET_FILING_TITLE
ASSET_FILING_TITLE_TYPE_CODE,
ASSET_FILING_TITLE_TYPE,
ASSET_FILING_TITLE_NUMBER,
ASSET_FILING_TITLE_ISSUER,
ASSET_FILING_TITLE_DATE,
ASSET_FILING_TITLE_CUSTODIAN,
ASSET_FILING_TITLE_REGIS_NUM,
ASSET_FILING_TITLE_LOCATION,
ASSET_FILING_TITLE_REGIS_LOC,
ASSET_FILING_PAYEE_SITE,
ASSET_FILING_REGIS_EXP_DATE,
--formulas
TOTAL_ASSET_CAP_SERVICE_AMOUNT,
LINE_ACCUMULATED_DEPRECIATION,
LINE_CAPITALIZED_REDUCTION,
TOTAL_ASSET_CAP_FEE_AMOUNT,
LINE_CAPITALIZED_INTEREST,
LINE_DISCOUNT,
LINE_ASSET_COST,
LINE_UNBILLED_DUE_AMOUNT,
LINE_OEC,
TOTAL_ASSET_TRADEIN_AMOUNT,
TOTAL_ACTIVE_LINE_FINANCED_AMT,
LINE_RESIDUAL_AMOUNT,
LINE_RENT_AMOUNT,
LINE_BILLED_RENTS,
(NVL(LINE_BILLED_RENTS,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) LINE_BILLED_RENTS_FUNC,
(NVL(LINE_TOTAL_BILLED,0) - NVL(LINE_BILLED_RENTS,0) ) LINE_BILLED_NON_RENTS,
((NVL(LINE_TOTAL_BILLED,0) - NVL(LINE_BILLED_RENTS,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) LINE_BILLED_NON_RENTS_FUNC,
TOTAL_ASSET_FINANCED_FEE_AMT,
TOTAL_ASSET_ROLLOVER_FEE_AMT,
(LINE_OEC - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - LINE_CAPITALIZED_REDUCTION + TOTAL_ASSET_CAP_FEE_AMOUNT
+ LINE_CAPITALIZED_INTEREST) LINE_CAP_AMOUNT,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF',
(nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)),
'LEASEST', (nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)),
'LOAN', nvl(LINE_PRINCIPAL_BALANCE,0),
'LOAN-REVOLVING', nvl(LINE_PRINCIPAL_BALANCE,0),
'LEASEOP', (nvl(LINE_OEC,0) - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - nvl(LINE_CAPITALIZED_REDUCTION,0) +
nvl(TOTAL_ASSET_CAP_FEE_AMOUNT,0) + nvl(LINE_CAPITALIZED_INTEREST,0) - nvl(LINE_ACCUMULATED_DEPRECIATION,0) -
nvl(TOTAL_ASSET_SUBSIDY_AMOUNT,0)),
0))),0) LINE_NET_INVESTMENT,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF',
(nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0))* nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEST', (nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN', nvl(LINE_PRINCIPAL_BALANCE,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN-REVOLVING', nvl(LINE_PRINCIPAL_BALANCE,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEOP', (nvl(LINE_OEC,0) - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - nvl(LINE_CAPITALIZED_REDUCTION,0) +
nvl(TOTAL_ASSET_CAP_FEE_AMOUNT,0) + nvl(LINE_CAPITALIZED_INTEREST,0) - nvl(LINE_ACCUMULATED_DEPRECIATION,0) -
nvl(TOTAL_ASSET_SUBSIDY_AMOUNT,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
0))),0) LINE_NET_INVESTMENT_FUNC,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
TOTAL_ASSET_ADDON_COST,
TOTAL_ASSET_SUBSIDY_AMOUNT,
TOTAL_ASSET_SUBSIDY_OVRD_AMT,
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_parent_request_id REQUEST_ID,
-- 1 REQUEST_ID,
chr.CONTRACT_NUMBER CONTRACT_NUMBER,
chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
chr.STS_CODE CONTRACT_STATUS_CODE,
chr.CURRENCY_CODE CONTRACT_CURRENCY,
khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
chr.START_DATE CONTRACT_START_DATE,
chr.END_DATE CONTRACT_END_DATE,
khr.ID CONTRACT_ID,
khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
khr.TERM_DURATION CONTRACT_TERM_DURATION,
khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
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,
pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
pdt.REPORTING_PDT_ID CONTRACT_REPORTING_PRODUCT_ID,
pdt.REPORTING_PRODUCT CONTRACT_REPORTING_PRODUCT,
pdt.INTEREST_CALCULATION_BASIS INTEREST_CALCLATION_BASIS_CODE,
pdt.REVENUE_RECOGNITION_METHOD REVENUE_RECOGNTION_METHOD_CODE,
pdt.INTEREST_CALCULATION_MEANING INTEREST_CALCULATION_BASIS,
pdt.REVENUE_RECOGNITION_MEANING REVENUE_RECOGNITION_METHOD,
ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
ledger.ledger_id LEDGER_ID,
ledger.name LEDGER,
legal_entity.name LEGAL_ENTITY,
cust_party.PARTY_NUMBER CNTRCT_CUST_PARTY_NUMBER,
cust_party.party_name CNTRCT_CUSTOMER_PARTY_NAME,
cust_party.PARTY_ID CNTRCT_CUST_PARTY_ID,
chr.CUST_ACCT_ID CNTRCT_CUST_ACOUNT_ID,
cust_accounts.ACCOUNT_NUMBER CNTRCT_CUST_ACCT_NUMBER,
cust_party_site.PARTY_SITE_ID CNTRCT_CUST_PARTY_SITE_ID,
cust_party_site.PARTY_SITE_NUMBER CNTRCT_CUST_PARTY_SITE_NUM,
cust_party_site.PARTY_SITE_NAME CNTRCT_CUST_PARTY_SITE_NAME,
cust_party_site.LOCATION_ID CNTRCT_CUST_PARTY_SITE_LOC_ID,
cust_party_site_use.cpsu_PARTY_SITE_USE_ID CNTRCT_CUST_PARTY_SITE_USE_ID,
krp.INTEREST_INDEX_ID CONTRACT_INTEREST_INDEX_ID,
indx.NAME CONTRACT_INTEREST_INDEX,
cle.id CONTRACT_LINE_ID,
cle.lse_id CONTRACT_LINE_TYPE_ID,
lse.LTY_CODE CONTRACT_LINE_TYPE_CODE,
lse_tl.name CONTRACT_LINE_TYPE_NAME,
cle.sts_code CONTRACT_LINE_STATUS_CODE,
cle_status_tl.meaning CONTRACT_LINE_STATUS,
kle.ATTRIBUTE_CATEGORY CONTRACT_LINE_DFF_CATEGORY,
kle.ATTRIBUTE1 CONTRACT_LINE_DFF1,
kle.ATTRIBUTE2 CONTRACT_LINE_DFF2,
kle.ATTRIBUTE3 CONTRACT_LINE_DFF3,
kle.ATTRIBUTE4 CONTRACT_LINE_DFF4,
kle.ATTRIBUTE5 CONTRACT_LINE_DFF5,
kle.ATTRIBUTE6 CONTRACT_LINE_DFF6,
kle.ATTRIBUTE7 CONTRACT_LINE_DFF7,
kle.ATTRIBUTE8 CONTRACT_LINE_DFF8,
kle.ATTRIBUTE9 CONTRACT_LINE_DFF9,
kle.ATTRIBUTE10 CONTRACT_LINE_DFF10,
kle.ATTRIBUTE11 CONTRACT_LINE_DFF11,
kle.ATTRIBUTE12 CONTRACT_LINE_DFF12,
kle.ATTRIBUTE13 CONTRACT_LINE_DFF13,
kle.ATTRIBUTE14 CONTRACT_LINE_DFF14,
kle.ATTRIBUTE15 CONTRACT_LINE_DFF15,
kle.DATE_DELIVERY_EXPECTED ASSET_DATE_DELIVERY_EXPECTED,
kle.DATE_FUNDING_EXPECTED ASSET_DATE_FUNDING_EXPECTED,
kle.RESIDUAL_PERCENTAGE ASSET_RESIDUAL_PERCENT,
kle.RESIDUAL_CODE ASSET_RES_GUARANTOR_TYPE_CODE,
lookup_res_guarantor.meaning ASSET_RES_GUARANTOR_TYPE,
kle.RESIDUAL_GRNTY_AMOUNT ASSET_RESIDUAL_AMT_GUARANTEE,
kle.RESIDUAL_VALUE ASSET_RESIDUAL_AMOUNT,
cle_TL.NAME CONTRACT_LINE_NAME,
decode(cle.lse_id, 33,(select SUM(NVL(txs.total_tax,0))
from okl_tax_sources txs
where txs.khr_id = khr.id
and txs.kle_id = cle.id
AND 33 = cle.lse_id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'),null) ASSET_UPFRONT_TAX,
decode(cle.lse_id,33, cle.bill_to_site_use_id,null) ASSET_BILL_TO_SITE_USE_ID,
decode(cle.lse_id,33, cust_cust_site_use.location, null) ASSET_BILL_TO_ADDRESS,
(SELECT
orm.name
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okx_receipt_methods_v orm
WHERE rgp.dnz_chr_id = khr.id
AND rgp.cle_id = cle.id
AND cle.lse_id = 33
AND rgp.rgd_code = 'LABILL'
AND rgp.id = rul.rgp_id
AND rgp.dnz_chr_id = rul.dnz_chr_id
AND rul.rule_information_category = 'LAPMTH'
AND orm.id1 = rul.object1_id1) ASSET_BILLING_PAYMENT_METHOD,
decode (cle.lse_id,33, cle.date_terminated, null) ASSET_TERMINATION_DATE,
-- lse_id 42 fixed asset , contract active
decode(khr.DEAL_TYPE, 'LOAN',fa_line_loan.FIXED_ASSET_LINE_ID,
fa_line_non_loan.FIXED_ASSET_LINE_ID) FIXED_ASSET_LINE_ID,
decode(khr.DEAL_TYPE, 'LOAN',fa_line_loan.MODEL,
fa_line_non_loan.MODEL) FA_MODEL_NUMBER,
decode(khr.DEAL_TYPE, 'LOAN',fa_line_loan.MANUFACTURER_NAME,
fa_line_non_loan.MANUFACTURER_NAME) FA_MANUFACTURER_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.FA_ASSET_LOCATION_NAME) FA_ASSET_LOCATION_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.FA_ASSET_LOCATION_ID ) FA_ASSET_LOCATION_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.ASSET_KEY_ID) FA_ASSET_KEY_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.ASSET_ID ) FA_ASSET_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.ASSET_KEY ) FA_ASSET_KEY,
-- bank info , lse_id = 33
decode (cle.lse_id,33, orma_bank_info.name, null) ASSET_BILLING_BANK_ACCOUNT,
decode (cle.lse_id,33, orma_bank_info.bank_name, null) ASSET_BILLING_BANK,
-- supplier 34
supplier.SUPPLIER_INVOICE_NUMBER INV_SUPPLIER_INVOICE_NUMBER,
supplier.SUPPLIER_INV_LINE_ID INV_SUPPLIER_INVOICE_LINE_ID,
supplier.SUPPLIER_INVOICE_DATE INV_SUPPLIER_INVOICE_DATE,
supplier.SUPPLIER_SHIP_TO_SITE_USE_ID INV_SUPPLR_SHIP_TO_SITE_USE_ID,
supplier.SUPPLIER_SHIP_TO_SITE INV_SUPPLIER_SHIP_TO_SITE,
--supplier.SUPPLIER_VENDOR_NAME SUPPLIER_VENDOR_NAME,
--supplier.SUPPLIER_VENDOR_ID SUPPLIER_VENDOR_ID,
supplier.INV_SUPPLIER_SHIP_TO_SITE_ID INV_SUPPLIER_SHIP_TO_SITE_ID,
supplier.INV_SUPPLIER_SHIP_TO_SITE_NUM INV_SUPPLIER_SHIP_TO_SITE_NUM,
-- fee line 52
decode(cle.lse_id,52,KLE.FEE_TYPE, null) FEE_TYPE_CODE,
decode(cle.lse_id,52, LKP_FEE_TYPE.MEANING,null) FEE_TYPE,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_NAME,
decode(lse_id,48,service_line.FEE_OR_SERVICE_NAME,NULL)) FEE_OR_SERVICE_NAME,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER_ID,
48,service_k_line.SUPPLIER_ID,
33,supplier.SUPPLIER_VENDOR_ID,null) LINE_SUPPLIER_ID,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER_NUMBER,
48,service_k_line.SUPPLIER_NUMBER,
33, supplier.SUPPLIER_NUMBER, NULL) LINE_SUPPLIER_NUMBER,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER,
48,service_k_line.SUPPLIER_NAME,
33, supplier.SUPPLIER_VENDOR_NAME, NULL) LINE_SUPPLIER,
decode(cle.lse_id,52,CLE.START_DATE ,
decode(lse_id,48,service_line.FEE_OR_SERVICE_EFFECTIVE_FROM,NULL)) FEE_OR_SERVICE_EFFECTIVE_FROM,
decode(cle.lse_id,52,CLE.END_DATE,
decode(lse_id,48,service_line.FEE_OR_SERVICE_EFFECTIVE_TO,NULL)) FEE_OR_SERVICE_EFFECTIVE_TO,
decode(cle.lse_id,52,KLE.AMOUNT ,
decode(lse_id,48,service_line.TOTAL_FEE_OR_SERVICE_AMOUNT,NULL)) FEE_OR_SERVICE_TOTAL_AMOUNT,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_FREQUENCY_CODE,
decode(lse_id,48,service_line.FEE_OR_SERVICE_FREQUENCY_CODE,NULL)) FEE_OR_SERVICE_FREQUENCY_CODE,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_FREQUENCY,
decode(lse_id,48,service_line.FEE_OR_SERVICE_FREQUENCY,NULL)) FEE_OR_SERVICE_FREQUENCY,
decode(cle.lse_id,52,fee_line.FEE_OR_SERV_NUMBER_OF_PERIODS,
decode(lse_id,48,service_line.FEE_OR_SERV_NUMBER_OF_PERIODS,NULL)) FEE_OR_SERV_NUMBER_OF_PERIODS,
decode(cle.lse_id,52,fee_line.FEE_OR_SERV_AMOUNT_PER_PERIOD,
decode(lse_id,48,service_line.FEE_OR_SERV_AMOUNT_PER_PERIOD,NULL)) FEE_OR_SERV_AMOUNT_PER_PERIOD,
decode(cle.lse_id,52,KLE.QTE_ID,null) FEE_ROLLOVER_QUOTE_ID,
(select tq.quote_number
from okl_trx_quotes_all_b tq
where tq.id = KLE.QTE_ID
and cle.lse_id = 52 ) FEE_ROLLOVER_QUOTE_NUMBER,
(select chr_tq.contract_number
from okl_trx_quotes_all_b tq, okc_k_headers_b chr_tq
where tq.id = KLE.QTE_ID
and tq.khr_id = chr_tq.id
and cle.lse_id = 52) FEE_ROLLOVER_CONTRACT_NUMBER,
decode(cle.lse_id,52, KLE.INITIAL_DIRECT_COST,null) FEE_INITIAL_DIRECT_COST,
/*
decode(cle.lse_id,52,
decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'),null) FEE_TRX_TAX_YN,
*/
decode(cle.lse_id,52,
(decode(kle.FEE_type, 'FINANCED', decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'), 'CAPITALIZED', decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'),null)),
null) FEE_TRX_TAX_YN,
-- item line = 34
item_line.MODEL_LINE_ID MODEL_LINE_ID,
item_line.UNIT_COST INVENTORY_UNIT_COST,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_ID,
item_line.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_ORG_ID,
item_line.INVENTORY_ITEM_ORG_ID) INVENTORY_ITEM_ORG_ID,
item_line.TOTAL_UNITS INVENTORY_TOTAL_UNITS,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_NAME,
item_line.INVENTORY_ITEM_NAME) INVENTORY_ITEM_NAME,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_DESCRIPTION,
item_line.INVENTORY_ITEM_DESCRIPTION) INVENTORY_ITEM_DESCRIPTION,
-- instal line 43, 45
instal_line.INSTALL_SITE_ID IB_INSTALL_SITE_ID,
instal_line.INSTALL_SITE_NUMBER IB_INSTALL_SITE_NUMBER,
instal_line.INSTALL_SITE_USE_ID IB_INSTALL_SITE_USE_ID,
instal_line.INSTALL_SITE_NAME IB_INSTALL_SITE,
-- instal_line.SERIAL_NUMBER IB_SERIAL_NUMBER, -- bug 7631324 for active contract
-- bug 7631324
(case when instal_line_number_of_assets.total > 1 THEN
instal_line.MULTIPLE else instal_line.SERIAL_NUMBER
end) IB_SERIAL_NUMBER,
-- passthrough evergreen 33,52,48
--(case when cle.lse_id in (33,52,48) then
-- to_date(PPH_pth_eveg.PASSTHRU_START_DATE) else null end) LINE_PTH_EVG_START_DATE, -- problem
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PAYOUT_BASIS else null end) LINE_PTH_EVG_PAYOUT_BASIS_CODE,
(case when cle.lse_id in (33,52,48) then
LKP_PYT_BASIS_pth_eveg.MEANING else null end) LINE_PTH_EVG_PAYOUT_BASIS,
(case when cle.lse_id in (33,52,48) then
fmla_pth_eveg.id else to_number(null) end) LINE_PTH_EVG_PYOUT_FORMULA_ID,
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PAYOUT_BASIS_FORMULA else null end) LINE_PTH_EVG_PYOUT_FORMULA,
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PASSTHRU_STREAM_TYPE_ID else null end) LINE_PTH_EVG_STREAM_TYPE_ID,
(case when cle.lse_id in (33,52,48) then
sty_pth_eveg.code else null end) LINE_PTH_EVG_STREAM_TYPE_CODE,
(case when cle.lse_id in (33,52,48) then
STYT_pth_eveg.NAME else null end) LINE_PTH_EVG_STREAM_TYPE,
-- passthrough base 52, 48
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PASSTHRU_START_DATE else null end) LINE_PTH_BASE_START_DATE,
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PAYOUT_BASIS else null end) LINE_PTH_BASE_PAY_BASIS_CODE,
(case when cle.lse_id in (52,48) then
LKP_PTH_BASE.MEANING else null end) LINE_PTH_BASE_PAYOUT_BASIS,
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PASSTHRU_STREAM_TYPE_ID else null end) LINE_PTH_BASE_STREAM_TYPE_ID,
(case when cle.lse_id in (52,48) then
sty_PTH_BASE.code else null end) LINE_PTH_BASE_STREAM_TYPE_CODE,
(case when cle.lse_id in (52,48) then
STYT_PTH_BASE.NAME else null end) LINE_PTH_BASE_STREAM_TYPE,
-- usage line 56
usage_line.USAGE_TYPE_CODE USAGE_TYPE_CODE,
usage_line.FIXED_USAGE_QUANTITY USAGE_FIXED_QUANTITY,
usage_line.PRICE_LIST_ID USAGE_PRICE_LIST_ID,
usage_line.PRICE_LIST_NAME USAGE_PRICE_LIST_NAME,
usage_line.USAGE_PERIOD_CODE USAGE_PERIOD_CODE,
usage_line.USAGE_PERIOD USAGE_PERIOD,
usage_line.USAGE_NO_OF_PERIOD USAGE_NO_OF_PERIOD,
usage_line.MINIMUM_QTY_USAGE USAGE_MINIMUM_QTY,
usage_line.LEVEL_FLAG USAGE_LEVEL_FLAG,
usage_line.DEFAULT_QTY_USAGE USAGE_DEFAULT_QUANTITY,
usage_line.BASE_READING_UOM_CODE USAGE_BASE_READING_UOM_CODE,
usage_line.BASE_READING_UOM USAGE_BASE_READING_UOM,
usage_line.BASE_READING USAGE_BASE_READING,
usage_line.AMCV_FLAG USAGE_AVG_MONTHLY_COUNTER_FLAG,
usage_line.USAGE_TYPE USAGE_TYPE,
-- service contract
decode(cle.lse_id, 48, service_k_line.Service_Contract_Number,
null) SERVICE_CONTRACT_NUMBER,
--decode(cle.lse_id, 48, service_k_line.SUPPLIER_NAME,
-- null) SERVICE_SUPPLIER,
decode(cle.lse_id, 48, service_k_line.ITEM_NAME,
null) SERVICE_PAYMENT_TYPE,
-- insurance line 47
(select IPYB.policy_number
from OKL_INS_POLICIES_B IPYB,
OKL_INS_POLICIES_B IPYB1
where IPYB.khr_id = khr.id
and IPYB.iss_code = 'QUOTE'
and IPYB.ipy_id = IPYB1.id
and IPYB1.policy_number = nvl(insurance.policy_number,'XXX')) INSURANCE_QUOTE_NUMBER,
insurance.ID INSURANCE_POLICY_ID,
insurance.policy_number INSURANCE_POLICY_NUMBER,
insurance.ipy_type INSURANCE_POLICY_TYPE_CODE,
insurance.policy_type INSURANCE_POLICY_TYPE,
insurance.ISS_CODE INSURANCE_POLICY_STATUS_CODE,
insurance.INSURANCE_STATUS INSURANCE_POLICY_STATUS,
-- insurance.ISU_ID INSURANCE_PROVIDER_ID,
insurance.INSURANCE_provider INSURANCE_PROVIDER,
insurance.country INSURANCE_POLICY_LOCATION,
-- insurance.IPF_CODE INSURANCE_PAYMENT_FREQ_CODE,
insurance.payment_frequency1 INSURANCE_PAYMENT_FREQUENCY,
insurance.lessor_insured_yn INS_POLICY_LESSOR_INSURED_YN,
insurance.lessor_payee_yn INS_POLICY_LESSOR_PAYEE_YN,
insurance.date_from INSURANCE_POLICY_EFF_FROM,
insurance.date_to INSURANCE_POLICY_EFF_TO,
insurance.ipt_id INSURANCE_PRODUCT_ID,
insurance.insurance_product_name INSURANCE_PRODUCT,
-- insurance.factor_code INSURANCE_FACTOR_CODE,
--insurance.insurance_factor INSURANCE_FACTOR,
insurance.factor_value INSURANCE_FACTOR_VALUE,
insurance.name_of_insured INSURANCE_NAME_OF_INSURED,
insurance.covered_amount INSURANCE_COVERED_AMOUNT,
insurance.calculated_premium INSURANCE_CALC_PREMIUM,
insurance.activation_date INSURANCE_ACTIVATION_DATE,
insurance.cancellation_date INSURANCE_Cancellation_date,
-- insurance.sales_rep_id INSURANCE_SALES_REP_ID,
insurance.sales_rep_name INSURANCE_SALES_REP,
(CASE WHEN insurance.IPF_CODE IS NULL THEN NULL
WHEN NVL(insurance.PREMIUM,0) = 0 THEN NULL
WHEN insurance.DATE_TO IS NULL THEN NULL
WHEN insurance.DATE_FROM IS NULL THEN NULL
WHEN ROUND(MONTHS_BETWEEN( insurance.DATE_TO,insurance.DATE_FROM)) <= 0 THEN NULL
WHEN insurance.IPF_CODE NOT IN ('HALF_YEARLY', 'QUARTERLY','YEARLY','MONTHLY') THEN NULL
ELSE (insurance.PREMIUM * (ROUND(MONTHS_BETWEEN( insurance.DATE_TO,insurance.DATE_FROM))/
decode(insurance.IPF_CODE, 'HALF_YEARLY', 6, 'QUARTERLY',3,'YEARLY',12,'MONTHLY',1)))
END) INSURANCE_TOT_PREMIUM,
-- corp_book
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_BOOK_TYPE_CODE) FA_CORP_BOOK_TYPE_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORPORATE_BOOK) FA_CORPORATE_BOOK,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SALVAGE_VALUE) FA_CORP_SALVAGE_VALUE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SALVAGE_VALUE_PERCENT) FA_CORP_SALVAGE_VALUE_PERCENT,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SAL_VALUE_BASIS_CODE) FA_CORP_SAL_VALUE_BASIS_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SAL_VALUE_BASIS) FA_CORP_SAL_VALUE_BASIS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_METHOD_ID) FA_CORP_DEP_METHOD_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_METHOD_CODE) FA_CORP_DEP_METHOD_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_METHOD_NAME) FA_CORP_DEP_METHOD_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_LIFE_IN_MONTHS) FA_CORP_DEP_LIFE_IN_MONTHS ,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_RATE) FA_CORP_DEP_RATE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_CATEGORY_ID) FA_CORP_CATEGORY_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_CATEGORY) FA_CORP_CATEGORY,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_ORIG_COST) FA_CORP_ORIG_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_COST) FA_CORP_DEP_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DATE_IN_SERVICE) FA_CORP_DATE_IN_SERVICE,
-- tax book
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_BOOK_TYPE_CODE) FA_TAX_BOOK_TYPE_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_BOOK ) FA_TAX_BOOK,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_SAL_VALUE_BASIS_CODE) FA_TAX_SAL_VALUE_BASIS_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_SAL_VALUE_BASIS) FA_TAX_SAL_VALUE_BASIS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_SALVAGE_VALUE ) FA_TAX_SALVAGE_VALUE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_SALVAGE_VALUE_PERCENT) FA_TAX_SALVAGE_VALUE_PERCENT,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_RATE ) FA_TAX_DEP_RATE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_LIFE_IN_MONTHS ) FA_TAX_DEP_LIFE_IN_MONTHS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_METHOD_CODE ) FA_TAX_DEP_METHOD_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_METHOD_ID ) FA_TAX_DEP_METHOD_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_METHOD_NAME ) FA_TAX_DEP_METHOD_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_ORIG_COST ) FA_TAX_ORIG_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_COST) FA_TAX_DEP_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DATE_IN_SERVICE ) FA_TAX_DATE_IN_SERVICE,
-- mg_bbok
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_BOOK_TYPE_CODE) FA_MG_BOOK_TYPE_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_BOOK ) FA_MG_BOOK,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_SAL_VALUE_BASIS_CODE ) FA_MG_SAL_VALUE_BASIS_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_SAL_VALUE_BASIS ) FA_MG_SAL_VALUE_BASIS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_SALVAGE_VALUE ) FA_MG_SALVAGE_VALUE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_SALVAGE_VALUE_PERCENT) FA_MG_SALVAGE_VALUE_PERCENT,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_RATE ) FA_MG_DEP_RATE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_LIFE_IN_MONTHS ) FA_MG_DEP_LIFE_IN_MONTHS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_METHOD_CODE ) FA_MG_DEP_METHOD_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_METHOD_ID ) FA_MG_DEP_METHOD_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_METHOD_NAME ) FA_MG_DEP_METHOD_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_ORIG_COST ) FA_MG_ORIG_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_COST ) FA_MG_DEP_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DATE_IN_SERVICE ) FA_MG_DATE_IN_SERVICE,
-- sales tax
sales_tax.ST_TRANSFER_OF_TITLE ASSET_ST_TRANSFER_OF_TITLE,
sales_tax.ST_SALE_AND_LEASE_BACK ASSET_ST_SALE_AND_LEASE_BACK,
sales_tax.ST_PURCHASE_OF_LEASE ASSET_ST_PURCHASE_OF_LEASE,
sales_tax.ST_INTENDED_USE ASSET_ST_INTENDED_USE,
sales_tax.ST_EQUIPMENT_AGE ASSET_ST_EQUIPMENT_AGE,
sales_tax.ST_ASSET_UPFRONT_TAX ASSET_ST_ASSET_UPFRONT_TAX,
-- property tax
property_tax.PROP_TAX_APPLICABLE ASSET_PROP_TAX_APPLICABLE,
property_tax.PROP_TAX_LESSEE_REPORT ASSET_PROP_TAX_LESSEE_REPORT,
property_tax.PROP_TAX_BILL_METHOD ASSET_PROP_TAX_BILL_METHOD,
-- asset_filing_lien
rl_asset_filing_lien.RULE_INFORMATION1 ASSET_FILING_LIEN_TYPE_CODE,
lookups_asset_filing_lien.meaning ASSET_FILING_LIEN_TYPE,
rl_asset_filing_lien.RULE_INFORMATION2 ASSET_FILING_LIEN_NUMBER,
rl_asset_filing_lien.RULE_INFORMATION3 ASSET_FILING_LIEN_DATE,
rl_asset_filing_lien.RULE_INFORMATION4 ASSET_FILING_LIEN_STATUS_CODE,
--rl_asset_filing_lien.RULE_INFORMATION4 ASSET_FILING_LIEN_STATUS,
lookups_asset_filing_lien_sts.meaning ASSET_FILING_LIEN_STATUS,
rl_asset_filing_lien.RULE_INFORMATION5 ASSET_FILING_JURISDICTION,
rl_asset_filing_lien.RULE_INFORMATION6 ASSET_FILING_SUB_JURISDICTION,
rl_asset_filing_lien.RULE_INFORMATION7 ASSET_FILING_LIEN_EXP_DATE,
rl_asset_filing_lien.RULE_INFORMATION8 ASSET_FILING_LIEN_CONT_NUMBER,
rl_asset_filing_lien.RULE_INFORMATION9 ASSET_FILING_LIEN_CONT_DATE,
hz_asset_filing_lien.party_name ASSET_FILING_LIENHOLDER,
-- ASSET_FILING_TITLE
rl_ASSET_FILING_TITLE.rule_information1 ASSET_FILING_TITLE_TYPE_CODE,
lookups_ASSET_FILING_TITLE.meaning ASSET_FILING_TITLE_TYPE,
rl_ASSET_FILING_TITLE.rule_information3 ASSET_FILING_TITLE_NUMBER,
hz_1_ASSET_FILING_TITLE.party_name ASSET_FILING_TITLE_ISSUER,
rl_ASSET_FILING_TITLE.rule_information2 ASSET_FILING_TITLE_DATE,
hz_2_ASSET_FILING_TITLE.party_name ASSET_FILING_TITLE_CUSTODIAN,
rl_ASSET_FILING_TITLE.rule_information4 ASSET_FILING_TITLE_REGIS_NUM,
rl_ASSET_FILING_TITLE.rule_information5 ASSET_FILING_TITLE_LOCATION,
rl_ASSET_FILING_TITLE.rule_information7 ASSET_FILING_TITLE_REGIS_LOC,
rl_ASSET_FILING_TITLE.rule_information6 ASSET_FILING_PAYEE_SITE,
rl_ASSET_FILING_TITLE.rule_information8 ASSET_FILING_REGIS_EXP_DATE,
-- formulas
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_service_capitalized(cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_CAP_SERVICE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_accumulated_deprn(cle.dnz_chr_id,cle.id),0) LINE_ACCUMULATED_DEPRECIATION,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_capital_reduction(cle.dnz_chr_id,cle.id),0),null) LINE_CAPITALIZED_REDUCTION,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_fees_capitalized(cle.dnz_chr_id,cle.id),0) TOTAL_ASSET_CAP_FEE_AMOUNT,
decode(cle.lse_id,33, NVL( kle.capitalized_interest, 0),null) LINE_CAPITALIZED_INTEREST,
-- NVL(OKL_SEEDED_FUNCTIONS_PVT.line_capitalized_interest(chr.id,cle.id),0) LINE_CAPITALIZED_INTEREST,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_discount(cle.dnz_chr_id,cle.id),0),null) LINE_DISCOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_asset_cost(cle.dnz_chr_id,cle.id),0) LINE_ASSET_COST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_unbilled_streams(cle.dnz_chr_id,cle.id),0) LINE_UNBILLED_DUE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_oec(cle.dnz_chr_id,cle.id),0) LINE_OEC,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_tradein(cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_TRADEIN_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_financed_amount(cle.dnz_chr_id,cle.id),0) TOTAL_ACTIVE_LINE_FINANCED_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_residual_amount(cle.dnz_chr_id,cle.id),0) LINE_RESIDUAL_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_rent_amount(cle.dnz_chr_id,cle.id),0) LINE_RENT_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_unearned_income (cle.dnz_chr_id,cle.id),0) LINE_UNEARNED_INCOME,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(cle.dnz_chr_id, cle.id),0) LINE_PRINCIPAL_BALANCE, -- addl para
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd,
okl_strm_type_b sty
where txd.kle_id = kle.id
and txd.sty_id = sty.id
and sty.STREAM_TYPE_PURPOSE = 'RENT') LINE_BILLED_RENTS,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd
where txd.kle_id = kle.id ) LINE_TOTAL_BILLED,
NVL(OKL_SEEDED_FUNCTIONS_PVT.Total_Asset_Financed_Fee_Amt(cle.dnz_chr_id, cle.id),0) TOTAL_ASSET_FINANCED_FEE_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.Total_Asset_Rollover_Fee_Amt(cle.dnz_chr_id, cle.id),0) TOTAL_ASSET_ROLLOVER_FEE_AMT,
--LINE_BILLED_NON_RENTS_FUNC,
--LINE_CAP_AMOUNT,
-- NET_INVESTMENT,
-- 17-Dec-08 Seema Chawla - bug 7635398 : changed Named parameter passing notation to Positional notation
/*
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(p_khr_id => cle.dnz_chr_id,
p_kle_id => cle.id, p_ref_type_code => 'ASSET'),null) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(p_khr_id =>cle.dnz_chr_id,
p_kle_id => cle.id, p_ref_type_code => 'EXPENSE'),null) EXPENSE_FUNDED_AMOUNT,
*/
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt( cle.dnz_chr_id,
cle.id, 'ASSET'),null) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(cle.dnz_chr_id,
cle.id, 'EXPENSE'),null) EXPENSE_FUNDED_AMOUNT,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.total_asset_addon_cost (cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_ADDON_COST,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_line_subsidy_amount (cle.dnz_chr_id,cle.id,NULL),0),null) TOTAL_ASSET_SUBSIDY_AMOUNT,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_line_subsidy_ovrd_amount (cle.dnz_chr_id,cle.id,NULL),0),null) TOTAL_ASSET_SUBSIDY_OVRD_AMT,
l_last_updated_by CREATED_BY,
-- -1 CREATED_BY,
sysdate CREATION_DATE,
l_last_updated_by LAST_UPDATED_BY,
---1 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,
-- -1 LAST_UPDATE_LOGIN,
-- -1 PROGRAM_APPLICATION_ID,
-- -1 PROGRAM_ID,
-- -1 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,
OKL_PRODUCT_PARAMETERS_V pdt,
OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
GL_LEDGERS ledger,
xle_entity_profiles legal_entity,
okc_k_lines_b cle,
okl_k_lines kle,
okc_k_lines_tl cle_tl,
okc_line_styles_b lse,
okc_line_styles_tl lse_tl,
OKC_STATUSES_TL cle_status_tl,
fnd_lookup_values_vl lookup_res_guarantor,
OKL_K_RATE_PARAMS krp,
OKL_INDICES indx,
-- cusotmer
HZ_PARTIES cust_party,
HZ_CUST_ACCOUNTS cust_accounts,
hz_party_sites cust_party_site,
(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,
-- sales tax
(select
cle_id,
dnz_chr_id,
ST_TRANSFER_OF_TITLE,
ST_SALE_AND_LEASE_BACK,
ST_PURCHASE_OF_LEASE,
ST_INTENDED_USE,
ST_EQUIPMENT_AGE,
ST_ASSET_UPFRONT_TAX,
st_row_num
from
(select
rgp.cle_id cle_id,
rgp.dnz_chr_id dnz_chr_id,
decode(RULE_INFORMATION6,'Y','Yes','No') ST_TRANSFER_OF_TITLE,
decode(RULE_INFORMATION7,'Y','Yes','No') ST_SALE_AND_LEASE_BACK,
decode(RULE_INFORMATION8,'Y','Yes','No') ST_PURCHASE_OF_LEASE,
zxf.classification_name ST_INTENDED_USE,
RULE_INFORMATION10 ST_EQUIPMENT_AGE,
lookups.meaning ST_ASSET_UPFRONT_TAX,
row_number() over ( partition by rgp.cle_id
order by rl.RULE_INFORMATION11, rl.rule_information9 nulls last ) st_row_num
from okc_rule_groups_b rgp,
okc_rules_b rl,
fnd_lookup_values_vl lookups,
zx_fc_intended_use_v zxf
where rgp.rgd_code = 'LAASTX'
and rl.rgp_id = rgp.id
and rl.RULE_INFORMATION_CATEGORY = 'LAASTX'
AND lookups.lookup_type (+) = 'OKL_ASSET_UPFRONT_TAX'
and lookups.lookup_code (+) = RULE_INFORMATION11
AND zxf.classification_code (+) = rule_information9 )
where st_row_num = 1 ) sales_tax,
-- property tax
(select
cle_id,
dnz_chr_id,
PROP_TAX_APPLICABLE,
PROP_TAX_LESSEE_REPORT,
PROP_TAX_BILL_METHOD,
prop_tax_row_num
FROM
(select
rgp.cle_id cle_id,
rgp.dnz_chr_id dnz_chr_id,
decode(RULE_INFORMATION1,'Y','Yes','No') PROP_TAX_APPLICABLE,
decode(RULE_INFORMATION2,'Y','Yes','No') PROP_TAX_LESSEE_REPORT,
lookups.meaning PROP_TAX_BILL_METHOD,
row_number() over ( partition by rgp.cle_id
order by rl.RULE_INFORMATION11, rl.rule_information3 nulls last ) prop_tax_row_num
from
okc_rule_groups_b rgp,
okc_rules_b rl,
fnd_lookup_values_vl lookups
where rgp.rgd_code = 'LAASTX'
and rl.rgp_id = rgp.id
and rl.RULE_INFORMATION_CATEGORY = 'LAPRTX'
AND lookups.lookup_type (+) = 'OKL_PROP_TAX_BILL_METHOD'
and lookups.lookup_code (+) = RULE_INFORMATION3)
where prop_tax_row_num = 1 ) property_tax,
-- asset_filing_lien
okc_rule_groups_b rgp_asset_filing_lien,
okc_rules_b rl_asset_filing_lien,
hz_parties hz_asset_filing_lien,
fnd_lookup_values_vl lookups_asset_filing_lien,
fnd_lookup_values_vl lookups_asset_filing_lien_sts,
-- ASSET_FILING_TITLE
okc_rule_groups_b rgp_ASSET_FILING_TITLE,
okc_rules_b rl_ASSET_FILING_TITLE,
fnd_lookup_values_vl lookups_ASSET_FILING_TITLE,
hz_parties hz_1_ASSET_FILING_TITLE,
hz_parties hz_2_ASSET_FILING_TITLE,
-- fa_line, contract active and non active, loan
( SELECT cleb_fa.id FIXED_ASSET_LINE_ID,
cleb_fa.cle_id CLE_ID,
cleb_fa.DNZ_CHR_ID DNZ_CHR_ID,
kle_fa.model_number MODEL,
kle_fa.manufacturer_name MANUFACTURER_NAME
FROM okl_k_lines kle_fa,
okc_k_lines_b cleb_fa
WHERE cleb_fa.id = kle_fa.id
AND cleb_fa.lse_id = 42) fa_line_loan,
-- fa_line, contract active, non-loan
( SELECT
cleb_fa.id FIXED_ASSET_LINE_ID,
cleb_fa.cle_id CLE_ID,
cleb_fa.DNZ_CHR_ID DNZ_CHR_ID,
fa_add.asset_number FA_ASSET_NUMBER,
fa_add.model_number MODEL,
fa_add.manufacturer_name MANUFACTURER_NAME,
loc_kfv_fa.CONCATENATED_SEGMENTS FA_ASSET_LOCATION_NAME,
loc_kfv_fa.LOCATION_ID FA_ASSET_LOCATION_ID,
fa_add.asset_key_ccid ASSET_KEY_ID,
fa_add.asset_id ASSET_ID,
decode(asset_kfv_fa.segment1,null,null,ltrim(rtrim(asset_kfv_fa.segment1,' '),' '))||
decode(asset_kfv_fa.segment2,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment2,' '),' '))||
decode(asset_kfv_fa.segment3,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment3,' '),' '))||
decode(asset_kfv_fa.segment4,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment4,' '),' '))||
decode(asset_kfv_fa.segment5,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment5,' '),' '))||
decode(asset_kfv_fa.segment6,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment6,' '),' '))||
decode(asset_kfv_fa.segment7,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment7,' '),' '))||
decode(asset_kfv_fa.segment8,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment8,' '),' '))||
decode(asset_kfv_fa.segment9,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment9,' '),' '))||
decode(asset_kfv_fa.segment10,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment10,' '),' ')) Asset_Key
FROM
okc_k_lines_b cleb_fa,
fa_distribution_history fa_hist,
fa_additions_b fa_add,
--fa_asset_keywords_kfv asset_kfv_fa, -- 21-jan-2008 sechawla 7830362
fa_asset_keywords asset_kfv_fa, -- 21-jan-2008 sechawla 7830362
fa_locations_kfv loc_kfv_fa,
okc_k_items item
WHERE ITEM.cle_id = cleb_fa.id
AND fa_add.asset_id = to_number(item.object1_id1)
and cleb_fa.lse_id = 42
AND fa_add.asset_id = fa_hist.asset_id
and fa_hist.location_id = loc_kfv_fa.location_id
and fa_hist.transaction_header_id_out IS NULL
and fa_hist.retirement_id IS NULL
AND asset_kfv_fa.CODE_COMBINATION_ID(+) = fa_add.asset_key_ccid
) fa_line_non_loan,
-- fa_line, contract not active, non-loan
-- bank info, lse_id 33
okc_rules_b rul_bank_info,
okc_rule_groups_b rgp_bank_info,
okx_rcpt_method_accounts_v orma_bank_info,
-- supplier 34
(SELECT
cle_id,
dnz_chr_id,
SUPPLIER_INVOICE_NUMBER,
SUPPLIER_INV_LINE_ID,
SUPPLIER_INVOICE_DATE,
SUPPLIER_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
SUPPLIER_SHIP_TO_SITE,
SUPPLIER_VENDOR_NAME,
SUPPLIER_VENDOR_ID,
SUPPLIER_NUMBER,
inv_row_num
FROM
(SELECT
cle.cle_id cle_id,
cle.dnz_chr_id dnz_chr_id,
inv.invoice_number SUPPLIER_INVOICE_NUMBER,
inv.id SUPPLIER_INV_LINE_ID,
inv.date_invoiced SUPPLIER_INVOICE_DATE,
inv.shipping_address_id1 SUPPLIER_SHIP_TO_SITE_USE_ID,
HPS.PARTY_SITE_ID INV_SUPPLIER_SHIP_TO_SITE_ID,
HPS.PARTY_SITE_NUMBER INV_SUPPLIER_SHIP_TO_SITE_NUM,
HPS.PARTY_SITE_NUMBER, SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) SUPPLIER_SHIP_TO_SITE,
pov.vendor_name SUPPLIER_VENDOR_NAME,
cpl.object1_id1 SUPPLIER_VENDOR_ID,
pov.segment1 SUPPLIER_NUMBER,
row_number() over ( partition by inv.cle_id
order by inv.id desc, inv.invoice_number nulls last ) inv_row_num
FROM okl_supp_invoice_dtls inv,
okc_k_party_roles_b cpl,
po_vendors pov,
okc_k_lines_b cle,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all csu
WHERE inv.cle_id (+)= cpl.cle_id
AND cpl.rle_code = 'OKL_VENDOR'
AND cpl.chr_id is NULL
AND pov.vendor_id = cpl.object1_id1
AND cle.id = cpl.cle_id
AND cle.lse_id = 34
AND csu.site_use_id (+) = inv.shipping_address_id1
AND cas.cust_acct_site_id (+) = csu.cust_acct_site_id
AND hps.party_site_id (+) = cas.party_site_id
AND hl.location_id (+) = hps.location_id)
where inv_row_num = 1 ) supplier,
-- fee_line 52
(SELECT
CIM_FEE.CLE_ID CLE_ID,
CIM_FEE.DNZ_CHR_ID DNZ_CHR_ID,
STYT.NAME FEE_OR_SERVICE_NAME,
to_number(CPLB_FEE.OBJECT1_ID1) FEE_SUPPLIER_ID,
pov.vendor_name FEE_SUPPLIER,
pov.segment1 FEE_SUPPLIER_NUMBER,
TO_CHAR(RUL_LAFREQ.OBJECT1_ID1) FEE_OR_SERVICE_FREQUENCY_CODE,
TUOM.NAME FEE_OR_SERVICE_FREQUENCY,
RUL_LAFEXP.RULE_INFORMATION1 FEE_OR_SERV_NUMBER_OF_PERIODS,
RUL_LAFEXP.RULE_INFORMATION2 FEE_OR_SERV_AMOUNT_PER_PERIOD
FROM
OKC_K_ITEMS CIM_FEE,
OKL_STRM_TYPE_TL STYT,
OKC_K_PARTY_ROLES_B CPLB_FEE,
PO_VENDORS POV,
OKC_RULE_GROUPS_B RGP_LAFEXP,
OKC_RULES_B RUL_LAFREQ,
OKC_RULES_B RUL_LAFEXP,
OKL_TIME_UNITS_V TUOM
WHERE
CIM_FEE.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
AND STYT.ID = CIM_FEE.OBJECT1_ID1
AND STYT.LANGUAGE = USERENV('LANG')
AND CPLB_FEE.CLE_ID (+) = CIM_FEE.CLE_ID
AND CPLB_FEE.DNZ_CHR_ID (+) = CIM_FEE.DNZ_CHR_ID
AND cplb_fee.rle_code (+) = 'OKL_VENDOR'
AND POV.VENDOR_ID (+) = CPLB_FEE.OBJECT1_ID1
AND RGP_LAFEXP.DNZ_CHR_ID (+) = CIM_FEE.DNZ_CHR_ID
AND RGP_LAFEXP.CLE_ID (+) = CIM_FEE.CLE_ID
AND RGP_LAFEXP.RGD_CODE (+) = 'LAFEXP'
AND RUL_LAFREQ.RGP_ID (+) = RGP_LAFEXP.ID
AND RUL_LAFREQ.RULE_INFORMATION_CATEGORY (+) = 'LAFREQ'
AND RUL_LAFEXP.RGP_ID (+) = RGP_LAFEXP.ID
AND RUL_LAFEXP.RULE_INFORMATION_CATEGORY (+) = 'LAFEXP'
AND TUOM.ID1 (+) = RUL_LAFREQ.OBJECT1_ID1 ) fee_line,
fnd_lookup_values_vl LKP_FEE_TYPE,
-- item line_id=34
(SELECT
cleb_item.cle_id cle_id,
cleb_item.dnz_chr_id dnz_chr_id,
cleb_item.id MODEL_LINE_ID,
cleb_item.price_unit UNIT_COST,
to_number(item.object1_id1) INVENTORY_ITEM_ID,
to_number(item.object1_id2) INVENTORY_ITEM_ORG_ID,
item.number_of_items TOTAL_UNITS,
msi.segment1 INVENTORY_ITEM_NAME,
msi.description INVENTORY_ITEM_DESCRIPTION
FROM okc_k_items item,
okc_k_lines_b cleb_item,
MTL_SYSTEM_ITEMS_B msi
WHERE item.cle_id = cleb_item.id
AND cleb_item.lse_id = 34
AND msi.inventory_item_id = item.object1_id1
AND msi.organization_id = item.object1_id2) item_line,
-- instal line 43, 45 contract active
(SELECT
cle_id,
dnz_chr_id,
INSTALL_SITE_USE_ID,
INSTALL_SITE_NAME,
INSTALL_SITE_ID,
INSTALL_SITE_NUMBER,
SERIAL_NUMBER,
MULTIPLE,
instal_site_row_num
from
(select
cle_inst.cle_id,
cle_inst.dnz_chr_id,
to_number(iti.object_id1_new) INSTALL_SITE_USE_ID,
hps.party_site_id INSTALL_SITE_ID,
hps.party_site_number INSTALL_SITE_NUMBER,
substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,
hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,
hl.postal_code,null,hl.country,null, null,null,null,null,null,
null,'n','n',80,1,1),1,80) INSTALL_SITE_NAME,
--(case when NVL(cim_ib.NUMBER_OF_ITEMS,0) > 1 THEN lookup.MEANING
-- else csi.serial_number end) SERIAL_NUMBER ,
csi.serial_number SERIAL_NUMBER ,
lookup.MEANING MULTIPLE,
row_number() over ( partition by cle_inst.cle_id
order by iti.object_id1_new nulls last ) instal_site_row_num
from hz_locations hl,
hz_party_sites hps,
okl_txl_itm_insts iti,
csi_item_instances csi,
okc_k_items cim_ib,
okc_k_lines_b cle_ib,
okc_k_lines_b cle_inst,
fnd_lookup_values_vl lookup
where cle_inst.lse_id = 43
and cle_ib.cle_id = cle_inst.id
and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
AND iti.kle_id = cle_ib.id
and cle_ib.lse_id = 45
and cim_ib.cle_id = cle_ib.id
and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
and cim_ib.object1_id1 = csi.instance_id
and cim_ib.object1_id2 = '#'
and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
and csi.install_location_id = hps.party_site_id
and csi.install_location_type_code = 'HZ_PARTY_SITES'
and hps.location_id = hl.location_id
and lookup.lookup_type like 'OKL_ECC_VALUE_TYPE'
and lookup.lookup_code like 'MULTIPLE')
where instal_site_row_num = 1
) instal_line, -- contract active
-- bug 7631324
( select
NVL(count(cleb_inst.cle_id),0) total,
cleb_inst.cle_id,
cleb_inst.dnz_chr_id
FROM okc_k_lines_b cleb_inst,
okc_k_lines_b cleb_ib,
okl_txl_itm_insts iti
WHERE cleb_inst.lse_id = 43
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = 45
AND iti.kle_id = cleb_ib.id
group by cleb_inst.cle_id, cleb_inst.dnz_chr_id) instal_line_number_of_assets,
-- instal_line 43,45 -- contract not active
-- service line 48
(SELECT
cleb_svc.id cle_id,
msit.description FEE_OR_SERVICE_NAME,
cleb_svc.start_date FEE_OR_SERVICE_EFFECTIVE_FROM,
cleb_svc.end_date FEE_OR_SERVICE_EFFECTIVE_TO,
kle_svc.amount TOTAL_FEE_OR_SERVICE_AMOUNT,
rul_lafreq.object1_id1 FEE_OR_SERVICE_FREQUENCY_CODE,
tuom.name FEE_OR_SERVICE_FREQUENCY,
rul_lafexp.rule_information1 FEE_OR_SERV_NUMBER_OF_PERIODS,
rul_lafexp.rule_information2 FEE_OR_SERV_AMOUNT_PER_PERIOD
FROM
OKC_K_LINES_B CLEB_SVC,
OKL_K_LINES KLE_SVC,
OKC_K_ITEMS CIM_SVC,
MTL_SYSTEM_ITEMS_TL MSIT,
OKC_RULE_GROUPS_B RGP_LAFEXP,
OKC_RULES_B RUL_LAFREQ,
OKC_RULES_B RUL_LAFEXP,
OKL_TIME_UNITS_V TUOM,
OKC_LINE_STYLES_B LNSTYLE
WHERE
cleb_svc.id = kle_svc.id
AND cim_svc.cle_id = cleb_svc.id
AND cim_svc.dnz_chr_id = cleb_svc.dnz_chr_id
AND cim_svc.jtot_object1_code = 'OKX_SERVICE'
AND msit.inventory_item_id = cim_svc.object1_id1
AND msit.organization_id = cim_svc.object1_id2
AND msit.language = USERENV('LANG')
AND rgp_lafexp.dnz_chr_id (+) = cleb_svc.dnz_chr_id
AND rgp_lafexp.cle_id (+) = cleb_svc.id
AND rgp_lafexp.rgd_code (+) = 'LAFEXP'
AND rul_lafreq.rgp_id (+) = rgp_lafexp.id
AND rul_lafreq.rule_information_category (+) = 'LAFREQ'
AND rul_lafexp.rgp_id (+) = rgp_lafexp.id
AND rul_lafexp.rule_information_category (+) = 'LAFEXP'
AND tuom.id1 (+) = rul_lafreq.object1_id1
AND LNSTYLE.LTY_CODE='SOLD_SERVICE'
-- and cleb_svc.dnz_chr_id = 139068
) service_line, -- lse_id 48
-- passthrough_evergreen lse_id 33,48,52
okl_strm_type_b sty_pth_eveg,
OKL_STRM_TYPE_TL STYT_pth_eveg,
OKL_PARTY_PAYMENT_HDR PPH_pth_eveg,
fnd_lookup_values_vl LKP_PYT_BASIS_pth_eveg,
okl_formulae_v fmla_pth_eveg,
-- passthrough_base 52, 48
okl_strm_type_b sty_PTH_BASE,
OKL_STRM_TYPE_TL STYT_PTH_BASE,
OKL_PARTY_PAYMENT_HDR PPH_PTH_BASE,
fnd_lookup_values_vl LKP_PTH_BASE,
-- usage line 56
(SELECT
CLE.ID CLE_ID,
mtl.inventory_item_id INVENTORY_ITEM_ID,
mtl.segment1 INVENTORY_ITEM_NAME,
mtl_tl.DESCRIPTION INVENTORY_ITEM_DESCRIPTION,
OKX_PRICE.id1 PRICE_LIST_ID,
OKX_PRICE.NAME PRICE_LIST_NAME,
to_number(RUL.RULE_INFORMATION1) MINIMUM_QTY_USAGE,
to_number(RUL.RULE_INFORMATION2) DEFAULT_QTY_USAGE,
to_number(RUL.RULE_INFORMATION7) FIXED_USAGE_QUANTITY,
RUL.RULE_INFORMATION8 USAGE_PERIOD_CODE,
--RUL.RULE_INFORMATION8 USAGE_PERIOD, --sechawla 8-jan-09 7628760
uom.UNIT_OF_MEASURE USAGE_PERIOD, --sechawla 8-jan-09 7628760
to_number(RUL.RULE_INFORMATION9) USAGE_NO_OF_PERIOD,
RUL.RULE_INFORMATION4 LEVEL_FLAG,
RUL.RULE_INFORMATION5 BASE_READING,
OBJECT3_ID1 BASE_READING_UOM_CODE,
RUL.RULE_INFORMATION3 AMCV_FLAG,
RUL.RULE_INFORMATION6 USAGE_TYPE_CODE,
RUL.DNZ_CHR_ID CHR_ID,
RG.CLE_ID usage_LINE_ID,
mtl.ORGANIZATION_ID INVENTORY_ITEM_ORG_ID,
lookup_read.meaning BASE_READING_UOM,
lookup_usage.meaning USAGE_TYPE
FROM
OKC_RULES_B RUL,
OKC_RULE_GROUPS_B RG,
mtl_system_items_b mtl,
mtl_system_items_tl mtl_tl,
OKX_LIST_HEADERS_V OKX_PRICE,
OKC_K_LINES_B CLE,
fnd_lookup_values_vl lookup_read,
fnd_lookup_values_vl lookup_usage,
OKX_UNITS_OF_MEASURE_V uom --sechawla 8-jan-09 7628760
WHERE
RULE_INFORMATION_CATEGORY = 'LAUSBB'
AND RUL.RGP_ID = RG.ID
AND RG.RGD_CODE = 'LAUSBB'
AND mtl.USAGE_ITEM_FLAG = 'Y'
AND mtl.inventory_item_id = RUL.OBJECT1_ID1
AND to_char(mtl.organization_id) = RUL.OBJECT1_ID2
AND OKX_PRICE.LIST_TYPE_CODE = 'PRL'
AND OKX_PRICE.ID1 = RUL.OBJECT2_ID1
AND OKX_PRICE.ID2 = RUL.OBJECT2_ID2
AND CLE.ID = RG.CLE_ID
AND CLE.DNZ_CHR_ID = RG.DNZ_CHR_ID
AND CLE.LSE_ID = 56
AND CLE.STS_CODE <> 'ABANDONED'
-- and CLE.DNZ_CHR_ID = 73036
and mtl.INVENTORY_ITEM_ID = mtl_tl.INVENTORY_ITEM_ID
and mtl.ORGANIZATION_ID = mtl_tl.ORGANIZATION_ID
and mtl_tl.LANGUAGE = userenv('LANG')
AND lookup_read.lookup_type (+) = 'UNIT'
AND lookup_read.LOOKUP_CODE (+) = OBJECT3_ID1
AND lookup_usage.lookup_type (+) = 'OKS_USAGE_TYPE'
AND lookup_usage.LOOKUP_CODE (+) = RUL.RULE_INFORMATION6
and uom.UOM_CODE(+) = RUL.RULE_INFORMATION8 --sechawla 8-jan-09 7628760
) usage_line,
OKL_LA_SERV_INTGR_UV service_k_line,
OKL_INS_POLICIES_UV insurance,
-- corporate book -- contract is active
( SELECT
cle.cle_id cle_id,
cle.dnz_chr_id dnz_chr_id,
fa.asset_number ASSET_NUMBER,
fb.book_type_code FA_CORP_BOOK_TYPE_CODE,
fbc.book_type_name FA_CORPORATE_BOOK,
decode(fb.percent_salvage_value,null,fb.salvage_value,null) FA_CORP_SALVAGE_VALUE,
decode(fb.percent_salvage_value,null,null,
fb.percent_salvage_value * 100) FA_CORP_SALVAGE_VALUE_PERCENT,
fb.salvage_type FA_CORP_SAL_VALUE_BASIS_CODE,
decode(fb.salvage_type,'AMT','Amount','PCT','Percent',null) FA_CORP_SAL_VALUE_BASIS,
FM.METHOD_ID FA_CORP_DEP_METHOD_ID,
fb.deprn_method_code FA_CORP_DEP_METHOD_CODE,
FM.NAME FA_CORP_DEP_METHOD_NAME,
fb.life_in_months FA_CORP_DEP_LIFE_IN_MONTHS ,
decode(fb.adjusted_rate,null,null,
fb.adjusted_rate * 100) FA_CORP_DEP_RATE,
fa.asset_category_id FA_CORP_CATEGORY_ID,
decode(cat.segment1,null,null,ltrim(rtrim(cat.segment1,' '),' '))||
decode(cat.segment2,null,null,'.'||ltrim(rtrim(cat.segment2,' '),' '))||
decode(cat.segment3,null,null,'.'||ltrim(rtrim(cat.segment3,' '),' '))||
decode(cat.segment4,null,null,'.'||ltrim(rtrim(cat.segment4,' '),' '))||
decode(cat.segment5,null,null,'.'||ltrim(rtrim(cat.segment5,' '),' '))||
decode(cat.segment6,null,null,'.'||ltrim(rtrim(cat.segment6,' '),' '))||
decode(cat.segment7,null,null,'.'||ltrim(rtrim(cat.segment7,' '),' '))
FA_CORP_CATEGORY,
FB.ORIGINAL_COST FA_CORP_ORIG_COST,
fb.cost FA_CORP_DEP_COST,
fb.date_placed_in_service FA_CORP_DATE_IN_SERVICE
FROM FA_CATEGORIES_B CAT,
FA_BOOK_CONTROLS FBC,
FA_BOOKS FB,
FA_ADDITIONS_B FA,
FA_METHODS FM,
okc_k_lines_b cle,
OKC_K_ITEMS item
WHERE
ITEM.cle_id = cle.id
AND fa.asset_id = to_number(item.object1_id1)
AND cat.category_id = fa.asset_category_id
and fbc.book_type_code = fb.book_type_code
and nvl(trunc(fbc.date_ineffective),trunc(sysdate)+1) > trunc(sysdate)
and fb.asset_id = fa.asset_id
and fb.transaction_header_id_out is null
and fb.date_ineffective is null
AND FB.deprn_method_code = FM.METHOD_CODE
AND FB.LIFE_IN_MONTHS = FM.LIFE_IN_MONTHS
AND book_class = 'CORPORATE') corp_book, -- contract active
-- corporate book -- contract not active
-- tax book, contract active
(select
cle_id,
dnz_chr_id,
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_RATE,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
book_type_code_row_num
FROM
(SELECT
cle.cle_id cle_id,
cle.dnz_chr_id dnz_chr_id,
fb.book_type_code FA_TAX_BOOK_TYPE_CODE,
fbc.book_type_name FA_TAX_BOOK,
decode(fb.percent_salvage_value,null,fb.salvage_value,null) FA_TAX_SALVAGE_VALUE,
decode(fb.percent_salvage_value,null,null,
fb.percent_salvage_value * 100) FA_TAX_SALVAGE_VALUE_PERCENT,
fb.salvage_type FA_TAX_SAL_VALUE_BASIS_CODE,
decode(fb.salvage_type,'AMT','Amount','PCT','Percent',null) FA_TAX_SAL_VALUE_BASIS,
FM.METHOD_ID FA_TAX_DEP_METHOD_ID,
fb.deprn_method_code FA_TAX_DEP_METHOD_CODE,
FM.NAME FA_TAX_DEP_METHOD_NAME,
fb.life_in_months FA_TAX_DEP_LIFE_IN_MONTHS,
decode(fb.adjusted_rate, null, null, to_char(fb.adjusted_rate*100)) FA_TAX_DEP_RATE,
FB.ORIGINAL_COST FA_TAX_ORIG_COST,
fb.cost FA_TAX_DEP_COST,
fb.date_placed_in_service FA_TAX_DATE_IN_SERVICE,
row_number() over ( partition by cle.cle_id
order by fb.book_type_code nulls last ) book_type_code_row_num
FROM
FA_CATEGORIES_B CAT,
FA_BOOK_CONTROLS FBC,
FA_BOOKS FB,
FA_ADDITIONS_B FA,
FA_METHODS FM,
okc_k_lines_b cle,
okc_k_headers_all_b chr,
okl_system_params_all sys_param,
OKC_K_ITEMS item
WHERE
ITEM.cle_id = cle.id
AND fa.asset_id = to_number(item.object1_id1)
AND cat.category_id = fa.asset_category_id
and fbc.book_type_code = fb.book_type_code
and nvl(trunc(fbc.date_ineffective),trunc(sysdate)+1) > trunc(sysdate)
and fb.asset_id = fa.asset_id
and fb.transaction_header_id_out is null
and fb.date_ineffective is null
AND FB.deprn_method_code = FM.METHOD_CODE
AND FB.LIFE_IN_MONTHS = FM.LIFE_IN_MONTHS
AND fbc.book_class = 'TAX'
and cle.dnz_chr_id = chr.id
and sys_param.org_id = chr.authoring_org_id
and fb.book_type_code <> NVL(sys_param.RPT_PROD_BOOK_TYPE_CODE,'XXX')
)
where book_type_code_row_num = 1 ) tax_book, -- contract active
-- tax book, contract not active
-- mg_book -- contract active
(SELECT
cle.cle_id cle_id,
cle.dnz_chr_id dnz_chr_id,
fb.book_type_code FA_MG_BOOK_TYPE_CODE,
fbc.book_type_name FA_MG_BOOK,
decode(fb.percent_salvage_value,null,fb.salvage_value,null) FA_MG_SALVAGE_VALUE,
decode(fb.percent_salvage_value,null,null,
fb.percent_salvage_value * 100) FA_MG_SALVAGE_VALUE_PERCENT,
fb.salvage_type FA_MG_SAL_VALUE_BASIS_CODE,
decode(fb.salvage_type,'AMT','Amount','PCT','Percent',null) FA_MG_SAL_VALUE_BASIS,
FM.METHOD_ID FA_MG_DEP_METHOD_ID,
fb.deprn_method_code FA_MG_DEP_METHOD_CODE,
FM.NAME FA_MG_DEP_METHOD_NAME,
fb.life_in_months FA_MG_DEP_LIFE_IN_MONTHS,
decode(fb.adjusted_rate, null, null,
to_char(fb.adjusted_rate*100)) FA_MG_DEP_RATE,
FB.ORIGINAL_COST FA_MG_ORIG_COST,
fb.cost FA_MG_DEP_COST,
fb.date_placed_in_service FA_MG_DATE_IN_SERVICE
FROM
FA_CATEGORIES_B CAT,
FA_BOOK_CONTROLS FBC,
FA_BOOKS FB,
FA_ADDITIONS_B FA,
FA_METHODS FM,
okc_k_lines_b cle,
okc_k_headers_all_b chr,
okl_system_params_all sys_param,
OKC_K_ITEMS item
WHERE
ITEM.cle_id = cle.id
AND fa.asset_id = to_number(item.object1_id1)
and cle.dnz_chr_id = chr.id
and sys_param.org_id = chr.authoring_org_id
and cat.category_id = fa.asset_category_id
and fbc.book_type_code = fb.book_type_code
and fb.book_type_code = sys_param.RPT_PROD_BOOK_TYPE_CODE
and nvl(trunc(fbc.date_ineffective),trunc(sysdate)+1) > trunc(sysdate)
and fb.asset_id = fa.asset_id
and fb.transaction_header_id_out is null
and fb.date_ineffective is null
AND FB.deprn_method_code = FM.METHOD_CODE
AND FB.LIFE_IN_MONTHS = FM.LIFE_IN_MONTHS
AND fbc.book_class = 'TAX'
) mg_book -- contract active
-- mg_book -- contract not active
WHERE
chr.id = khr.id
AND chr.SCS_CODE = 'LEASE'
AND opp.object_type LIKE 'CONTRACT_FIN_LINE_EXT-CONTRACT'
AND opp.object_value LIKE chr.contract_number
AND opp.assigned_process like P_ASSIGNED_PROCESS -- assigned_process is unique for each run
AND opp.khr_id = chr.id
AND opp.object_status like 'ACTIVE' -- INACTIVE
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 khr.pdt_id = pdt.id
AND pdt.aes_org_id = chr.authoring_org_id
AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
AND acct_sys_opts.org_id = chr.authoring_org_id
AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
-- customer
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 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_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 indx.ID(+) = krp.INTEREST_INDEX_ID
and krp.khr_id (+) = khr.id
and krp.parameter_type_code (+) = 'ACTUAL'
and krp.EFFECTIVE_TO_DATE (+) is null
AND cle.chr_id = chr.id
-- AND cle.LSE_ID in (33, 52,48, 56, 47)
AND cle.id = kle.id
AND cle_tl.id = cle.id
AND cle_tl.language = USERENV('LANG')
AND lse.id = cle.lse_id
AND lse.id = lse_tl.id
AND lse_tl.language = USERENV('LANG')
AND cle_status_tl.code = cle.sts_code
AND cle_status_tl.language = USERENV('LANG')
AND lookup_res_guarantor.lookup_type (+) = 'OKL_RESIDUAL_CODE'
AND lookup_res_guarantor.lookup_code (+) = kle.RESIDUAL_CODE
AND sales_tax.cle_id (+) = cle.id
AND sales_tax.dnz_chr_id (+) = cle.dnz_chr_id
AND property_tax.cle_id (+) = cle.id
AND property_tax.dnz_chr_id (+) = cle.dnz_chr_id
-- asset_filing_lien
AND rgp_asset_filing_lien.cle_id (+) = cle.id
AND rgp_asset_filing_lien.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
and rgp_asset_filing_lien.rgd_code (+) = 'LAAFLG'
and rl_asset_filing_lien.rgp_id (+) = rgp_asset_filing_lien.id
and rl_asset_filing_lien.RULE_INFORMATION_CATEGORY (+) = 'LAFLLN'
and rl_asset_filing_lien.OBJECT1_ID1 = hz_asset_filing_lien.party_id (+)
AND lookups_asset_filing_lien.lookup_type (+) = 'OKL_FILING_TYPE'
--and lookups_asset_filing_lien.lookup_code (+) = rl_ASSET_FILING_TITLE.RULE_INFORMATION1
and lookups_asset_filing_lien.lookup_code (+) = rl_asset_filing_lien.RULE_INFORMATION1
AND lookups_asset_filing_lien_sts.lookup_type (+) = 'OKL_FILING_STATUS'
and lookups_asset_filing_lien_sts.lookup_code (+) = rl_asset_filing_lien.RULE_INFORMATION4
-- ASSET_FILING_TITLE
AND rgp_ASSET_FILING_TITLE.cle_id (+) = cle.id
AND rgp_ASSET_FILING_TITLE.rgd_code (+) = 'LAAFLG'
and rgp_ASSET_FILING_TITLE.dnz_chr_id (+) = cle.DNZ_CHR_ID
and rl_ASSET_FILING_TITLE.rgp_id (+) = rgp_ASSET_FILING_TITLE.id
and rl_ASSET_FILING_TITLE.RULE_INFORMATION_CATEGORY (+) = 'LAFLTL'
AND lookups_ASSET_FILING_TITLE.lookup_type (+) = 'OKL_FILING_TYPE'
and lookups_ASSET_FILING_TITLE.lookup_code (+) = rl_ASSET_FILING_TITLE.RULE_INFORMATION1
AND hz_1_ASSET_FILING_TITLE.party_id (+) = rl_ASSET_FILING_TITLE.object1_id1
AND hz_2_ASSET_FILING_TITLE.party_id (+) = rl_ASSET_FILING_TITLE.object2_id1
AND fa_line_loan.CLE_ID (+) = cle.id
AND fa_line_loan.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND fa_line_non_loan.CLE_ID (+) = cle.id
AND fa_line_non_loan.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
-- AND bank_info cle.lse_id = 33
AND rgp_bank_info.cle_id (+) = cle.id
AND rgp_bank_info.rgd_code (+) = 'LABILL'
AND rgp_bank_info.id = rul_bank_info.rgp_id (+)
AND rgp_bank_info.dnz_chr_id = rul_bank_info.dnz_chr_id (+)
AND rul_bank_info.rule_information_category (+)= 'LABACC'
AND orma_bank_info.id1 (+) = rul_bank_info.object1_id1
AND supplier.cle_id (+) = cle.id
AND supplier.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND fee_line.CLE_ID (+) = cle.id
AND fee_line.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND LKP_FEE_TYPE.LOOKUP_CODE (+) = nvl(KLE.FEE_TYPE, 'XXX')
AND LKP_FEE_TYPE.LOOKUP_TYPE (+) = 'OKL_FEE_TYPES'
AND item_line.cle_id (+) = cle.id
AND item_line.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND instal_line.cle_id(+) = cle.id
AND instal_line.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
-- bug 7631324 begin for active contracts
AND instal_line_number_of_assets.cle_id(+) = cle.id
AND instal_line_number_of_assets.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
-- bug 7631324 end for active contracts
AND service_line.cle_id(+) = cle.id
-- AND passthrough_evergreen cle.lse_id in (33,52,48)
AND PPH_pth_eveg.CLE_ID (+) = cle.id
AND STYT_pth_eveg.ID (+) = PPH_pth_eveg.PASSTHRU_STREAM_TYPE_ID
AND STYT_pth_eveg.LANGUAGE(+) = USERENV('LANG')
and sty_pth_eveg.id (+) = styt_pth_eveg.id
AND fmla_pth_eveg.name (+) = PPH_pth_eveg.PAYOUT_BASIS_FORMULA
AND LKP_PYT_BASIS_pth_eveg.LOOKUP_CODE (+) = PPH_pth_eveg.PAYOUT_BASIS
AND LKP_PYT_BASIS_pth_eveg.LOOKUP_TYPE (+) = 'OKL_PAYOUT_BASIS'
and PPH_pth_eveg.PASSTHRU_TERM (+) = 'EVERGREEN'
-- passthrough_base 48, 52
AND cle.id = PPH_PTH_BASE.CLE_ID (+)
AND STYT_PTH_BASE.ID (+) = PPH_PTH_BASE.PASSTHRU_STREAM_TYPE_ID
AND STYT_PTH_BASE.LANGUAGE (+) = USERENV('LANG')
AND LKP_PTH_BASE.LOOKUP_CODE (+) = PPH_PTH_BASE.PAYOUT_BASIS
AND LKP_PTH_BASE.LOOKUP_TYPE (+) = 'OKL_PAYOUT_BASIS'
and sty_PTH_BASE.id (+) = styt_PTH_BASE.id
and PPH_PTH_BASE.PASSTHRU_TERM (+)= 'BASE'
AND usage_line.cle_id(+) = cle.id
AND service_k_line.cle_id (+) = cle.id
AND insurance.kle_id (+) = cle.id
AND corp_book.cle_id(+) = cle.id
AND corp_book.dnz_chr_id(+) = cle.dnz_chr_id
AND tax_book.cle_id (+) = cle.id
AND tax_book.dnz_chr_id (+) = cle.dnz_chr_id
AND mg_book.cle_id (+) = cle.id
AND mg_book.dnz_chr_id (+) = cle.dnz_chr_id
-- parameters section begin
--don't need the following as these paramater matching has been already
--been done in Master program - OKL_K_LINE_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 cle.sts_code like nvl(P_CONTRACT_LINE_STATUS, cle.sts_code)
AND CHR.CONTRACT_NUMBER like NVL(P_CONTRACT_NUMBER, CHR.CONTRACT_NUMBER)
*/
-- OKL_LINE_STYLES
AND lse.LTY_CODE like NVL(P_CONTRACT_LINE_TYPE, lse.LTY_CODE)
--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)
-- 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)
-- 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(fee_line.FEE_SUPPLIER_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(fee_line.FEE_SUPPLIER_ID,-9999))
OR nvl(supplier.SUPPLIER_VENDOR_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(supplier.SUPPLIER_VENDOR_ID,-9999))
OR nvl(service_k_line.SUPPLIER_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(service_k_line.SUPPLIER_ID,-9999))
)
-- P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
AND ( nvl(fee_line.FEE_SUPPLIER_ID,-9999) = nvl(P_VENDOR_NAME,nvl(fee_line.FEE_SUPPLIER_ID,-9999))
OR nvl(supplier.SUPPLIER_VENDOR_ID,-9999) = nvl(P_VENDOR_NAME,nvl(supplier.SUPPLIER_VENDOR_ID,-9999))
OR nvl(service_k_line.SUPPLIER_ID,-9999) = nvl(P_VENDOR_NAME,nvl(service_k_line.SUPPLIER_ID,-9999))
)
AND NVL(tax_book.FA_TAX_BOOK_TYPE_CODE, 'XXX') LIKE NVL(P_TAX_BOOK, NVL(tax_book.FA_TAX_BOOK_TYPE_CODE, 'XXX'))
);
write_to_log('Inserting INACTIVE contract lines P_FA_INFO_YN like Y');
INSERT INTO OKL_CNTRCT_LINE_FIN_EXTRACT_T (
REQUEST_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_TERM_DURATION,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_LEGAL_ENTITY_ID,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_REPORTING_PRODUCT_ID,
CONTRACT_REPORTING_PRODUCT,
INTEREST_CALCLATION_BASIS_CODE,
REVENUE_RECOGNTION_METHOD_CODE,
INTEREST_CALCULATION_BASIS,
REVENUE_RECOGNITION_METHOD,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
LEGAL_ENTITY,
CNTRCT_CUST_PARTY_NUMBER,
CNTRCT_CUSTOMER_PARTY_NAME,
CNTRCT_CUST_PARTY_ID,
CNTRCT_CUST_ACOUNT_ID,
CNTRCT_CUST_ACCT_NUMBER,
CNTRCT_CUST_PARTY_SITE_ID,
CNTRCT_CUST_PARTY_SITE_NUM,
CNTRCT_CUST_PARTY_SITE_NAME,
CNTRCT_CUST_PARTY_SITE_LOC_ID,
CNTRCT_CUST_PARTY_SITE_USE_ID,
CONTRACT_INTEREST_INDEX_ID,
CONTRACT_INTEREST_INDEX,
CONTRACT_LINE_ID,
CONTRACT_LINE_TYPE_ID,
CONTRACT_LINE_TYPE_CODE,
CONTRACT_LINE_TYPE_NAME,
CONTRACT_LINE_STATUS_CODE,
CONTRACT_LINE_STATUS,
CONTRACT_LINE_DFF_CATEGORY,
CONTRACT_LINE_DFF1,
CONTRACT_LINE_DFF2,
CONTRACT_LINE_DFF3,
CONTRACT_LINE_DFF4,
CONTRACT_LINE_DFF5,
CONTRACT_LINE_DFF6,
CONTRACT_LINE_DFF7,
CONTRACT_LINE_DFF8,
CONTRACT_LINE_DFF9,
CONTRACT_LINE_DFF10,
CONTRACT_LINE_DFF11,
CONTRACT_LINE_DFF12,
CONTRACT_LINE_DFF13,
CONTRACT_LINE_DFF14,
CONTRACT_LINE_DFF15,
ASSET_DATE_DELIVERY_EXPECTED,
ASSET_DATE_FUNDING_EXPECTED,
ASSET_RESIDUAL_PERCENT,
ASSET_RES_GUARANTOR_TYPE_CODE,
ASSET_RES_GUARANTOR_TYPE,
ASSET_RESIDUAL_AMT_GUARANTEE,
ASSET_RESIDUAL_AMOUNT,
CONTRACT_LINE_NAME,
ASSET_UPFRONT_TAX,
ASSET_BILL_TO_SITE_USE_ID,
ASSET_BILL_TO_ADDRESS,
ASSET_BILLING_PAYMENT_METHOD,
ASSET_TERMINATION_DATE,
-- FA
FIXED_ASSET_LINE_ID,
FA_MODEL_NUMBER,
FA_MANUFACTURER_NAME,
FA_ASSET_LOCATION_NAME,
FA_ASSET_LOCATION_ID,
FA_ASSET_KEY_ID,
FA_ASSET_ID,
FA_ASSET_KEY,
ASSET_BILLING_BANK_ACCOUNT,
ASSET_BILLING_BANK,
INV_SUPPLIER_INVOICE_NUMBER,
INV_SUPPLIER_INVOICE_LINE_ID,
INV_SUPPLIER_INVOICE_DATE,
INV_SUPPLR_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE,
--SUPPLIER_VENDOR_NAME,
--SUPPLIER_VENDOR_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
--FEE
FEE_TYPE_CODE,
FEE_TYPE,
FEE_OR_SERVICE_NAME,
--FEE_SUPPLIER_ID,
--FEE_SUPPLIER,
LINE_SUPPLIER_ID,
LINE_SUPPLIER,
LINE_SUPPLIER_NUMBER,
FEE_OR_SERVICE_EFFECTIVE_FROM,
FEE_OR_SERVICE_EFFECTIVE_TO,
FEE_OR_SERVICE_TOTAL_AMOUNT,
FEE_OR_SERVICE_FREQUENCY_CODE,
FEE_OR_SERVICE_FREQUENCY,
FEE_OR_SERV_NUMBER_OF_PERIODS,
FEE_OR_SERV_AMOUNT_PER_PERIOD,
FEE_ROLLOVER_QUOTE_ID,
FEE_ROLLOVER_QUOTE_NUMBER,
FEE_ROLLOVER_CONTRACT_NUMBER,
FEE_INITIAL_DIRECT_COST,
FEE_TRX_TAX_YN,
MODEL_LINE_ID,
INVENTORY_UNIT_COST,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_ORG_ID,
INVENTORY_TOTAL_UNITS,
INVENTORY_ITEM_NAME,
INVENTORY_ITEM_DESCRIPTION,
IB_INSTALL_SITE_ID,
IB_INSTALL_SITE_NUMBER,
IB_INSTALL_SITE_USE_ID,
IB_INSTALL_SITE,
IB_SERIAL_NUMBER,
LINE_PTH_EVG_PAYOUT_BASIS_CODE,
LINE_PTH_EVG_PAYOUT_BASIS,
--LINE_PTH_EVG_START_DATE,
LINE_PTH_EVG_PYOUT_FORMULA_ID,
LINE_PTH_EVG_PYOUT_FORMULA,
LINE_PTH_EVG_STREAM_TYPE_ID,
LINE_PTH_EVG_STREAM_TYPE_CODE,
LINE_PTH_EVG_STREAM_TYPE,
LINE_PTH_BASE_START_DATE,
LINE_PTH_BASE_PAY_BASIS_CODE,
LINE_PTH_BASE_PAYOUT_BASIS,
LINE_PTH_BASE_STREAM_TYPE_ID,
LINE_PTH_BASE_STREAM_TYPE_CODE,
LINE_PTH_BASE_STREAM_TYPE,
USAGE_TYPE_CODE,
USAGE_FIXED_QUANTITY,
USAGE_PRICE_LIST_ID,
USAGE_PRICE_LIST_NAME,
USAGE_PERIOD_CODE,
USAGE_PERIOD,
USAGE_NO_OF_PERIOD,
USAGE_MINIMUM_QTY,
USAGE_LEVEL_FLAG,
USAGE_DEFAULT_QUANTITY,
USAGE_BASE_READING_UOM_CODE,
USAGE_BASE_READING_UOM,
USAGE_BASE_READING,
USAGE_AVG_MONTHLY_COUNTER_FLAG,
USAGE_TYPE,
SERVICE_CONTRACT_NUMBER,
--SERVICE_SUPPLIER,
SERVICE_PAYMENT_TYPE,
INSURANCE_QUOTE_NUMBER,
INSURANCE_POLICY_ID,
INSURANCE_POLICY_NUMBER,
INSURANCE_POLICY_TYPE_CODE,
INSURANCE_POLICY_TYPE,
INSURANCE_POLICY_STATUS_CODE,
INSURANCE_POLICY_STATUS,
INSURANCE_PROVIDER,
INSURANCE_POLICY_LOCATION,
INSURANCE_PAYMENT_FREQUENCY,
INS_POLICY_LESSOR_INSURED_YN,
INS_POLICY_LESSOR_PAYEE_YN,
INSURANCE_POLICY_EFF_FROM,
INSURANCE_POLICY_EFF_TO,
INSURANCE_PRODUCT_ID,
INSURANCE_PRODUCT,
--INSURANCE_FACTOR_CODE,
-- INSURANCE_FACTOR,
INSURANCE_FACTOR_VALUE,
INSURANCE_NAME_OF_INSURED,
INSURANCE_COVERED_AMOUNT,
INSURANCE_CALC_PREMIUM,
INSURANCE_ACTIVATION_DATE,
INSURANCE_Cancellation_date,
INSURANCE_SALES_REP,
INSURANCE_TOT_PREMIUM,
--corp_book
FA_CORP_BOOK_TYPE_CODE,
FA_CORPORATE_BOOK,
FA_CORP_SALVAGE_VALUE,
FA_CORP_SALVAGE_VALUE_PERCENT,
FA_CORP_SAL_VALUE_BASIS_CODE,
FA_CORP_SAL_VALUE_BASIS,
FA_CORP_DEP_METHOD_ID,
FA_CORP_DEP_METHOD_CODE,
FA_CORP_DEP_METHOD_NAME,
FA_CORP_DEP_LIFE_IN_MONTHS,
FA_CORP_DEP_RATE,
FA_CORP_CATEGORY_ID,
FA_CORP_CATEGORY,
FA_CORP_ORIG_COST,
FA_CORP_DEP_COST,
FA_CORP_DATE_IN_SERVICE,
--taxbook
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_DEP_RATE,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
--mg_bbok
FA_MG_BOOK_TYPE_CODE,
FA_MG_BOOK,
FA_MG_SAL_VALUE_BASIS_CODE,
FA_MG_SAL_VALUE_BASIS,
FA_MG_SALVAGE_VALUE,
FA_MG_SALVAGE_VALUE_PERCENT,
FA_MG_DEP_RATE,
FA_MG_DEP_LIFE_IN_MONTHS,
FA_MG_DEP_METHOD_CODE,
FA_MG_DEP_METHOD_ID,
FA_MG_DEP_METHOD_NAME,
FA_MG_ORIG_COST,
FA_MG_DEP_COST,
FA_MG_DATE_IN_SERVICE,
--salestax
ASSET_ST_TRANSFER_OF_TITLE,
ASSET_ST_SALE_AND_LEASE_BACK,
ASSET_ST_PURCHASE_OF_LEASE,
ASSET_ST_INTENDED_USE,
ASSET_ST_EQUIPMENT_AGE,
ASSET_ST_ASSET_UPFRONT_TAX,
--propertytax
ASSET_PROP_TAX_APPLICABLE,
ASSET_PROP_TAX_LESSEE_REPORT,
ASSET_PROP_TAX_BILL_METHOD,
--asset_filing_lien
ASSET_FILING_LIEN_TYPE_CODE,
ASSET_FILING_LIEN_TYPE,
ASSET_FILING_LIEN_NUMBER,
ASSET_FILING_LIEN_DATE,
ASSET_FILING_LIEN_STATUS_CODE,
ASSET_FILING_LIEN_STATUS,
ASSET_FILING_JURISDICTION,
ASSET_FILING_SUB_JURISDICTION,
ASSET_FILING_LIEN_EXP_DATE,
ASSET_FILING_LIEN_CONT_NUMBER,
ASSET_FILING_LIEN_CONT_DATE,
ASSET_FILING_LIENHOLDER,
--ASSET_FILING_TITLE
ASSET_FILING_TITLE_TYPE_CODE,
ASSET_FILING_TITLE_TYPE,
ASSET_FILING_TITLE_NUMBER,
ASSET_FILING_TITLE_ISSUER,
ASSET_FILING_TITLE_DATE,
ASSET_FILING_TITLE_CUSTODIAN,
ASSET_FILING_TITLE_REGIS_NUM,
ASSET_FILING_TITLE_LOCATION,
ASSET_FILING_TITLE_REGIS_LOC,
ASSET_FILING_PAYEE_SITE,
ASSET_FILING_REGIS_EXP_DATE,
--formulas
TOTAL_ASSET_CAP_SERVICE_AMOUNT,
LINE_ACCUMULATED_DEPRECIATION,
LINE_CAPITALIZED_REDUCTION,
TOTAL_ASSET_CAP_FEE_AMOUNT,
LINE_CAPITALIZED_INTEREST,
LINE_DISCOUNT,
LINE_ASSET_COST,
LINE_UNBILLED_DUE_AMOUNT,
LINE_OEC,
TOTAL_ASSET_TRADEIN_AMOUNT,
TOTAL_ACTIVE_LINE_FINANCED_AMT,
LINE_RESIDUAL_AMOUNT,
LINE_RENT_AMOUNT,
LINE_BILLED_RENTS,
LINE_BILLED_RENTS_FUNC,
LINE_BILLED_NON_RENTS,
LINE_BILLED_NON_RENTS_FUNC,
TOTAL_ASSET_FINANCED_FEE_AMT,
TOTAL_ASSET_ROLLOVER_FEE_AMT,
LINE_CAP_AMOUNT,
LINE_NET_INVESTMENT,
LINE_NET_INVESTMENT_FUNC,
-- LINE_TOT_BILLED_RECEIVABLE,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
TOTAL_ASSET_ADDON_COST,
TOTAL_ASSET_SUBSIDY_AMOUNT,
TOTAL_ASSET_SUBSIDY_OVRD_AMT,
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_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_TERM_DURATION,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_LEGAL_ENTITY_ID,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_REPORTING_PRODUCT_ID,
CONTRACT_REPORTING_PRODUCT,
INTEREST_CALCLATION_BASIS_CODE,
REVENUE_RECOGNTION_METHOD_CODE,
INTEREST_CALCULATION_BASIS,
REVENUE_RECOGNITION_METHOD,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
LEGAL_ENTITY,
CNTRCT_CUST_PARTY_NUMBER,
CNTRCT_CUSTOMER_PARTY_NAME,
CNTRCT_CUST_PARTY_ID,
CNTRCT_CUST_ACOUNT_ID,
CNTRCT_CUST_ACCT_NUMBER,
CNTRCT_CUST_PARTY_SITE_ID,
CNTRCT_CUST_PARTY_SITE_NUM,
CNTRCT_CUST_PARTY_SITE_NAME,
CNTRCT_CUST_PARTY_SITE_LOC_ID,
CNTRCT_CUST_PARTY_SITE_USE_ID,
CONTRACT_INTEREST_INDEX_ID,
CONTRACT_INTEREST_INDEX,
CONTRACT_LINE_ID,
CONTRACT_LINE_TYPE_ID,
CONTRACT_LINE_TYPE_CODE,
CONTRACT_LINE_TYPE_NAME,
CONTRACT_LINE_STATUS_CODE,
CONTRACT_LINE_STATUS,
CONTRACT_LINE_DFF_CATEGORY,
CONTRACT_LINE_DFF1,
CONTRACT_LINE_DFF2,
CONTRACT_LINE_DFF3,
CONTRACT_LINE_DFF4,
CONTRACT_LINE_DFF5,
CONTRACT_LINE_DFF6,
CONTRACT_LINE_DFF7,
CONTRACT_LINE_DFF8,
CONTRACT_LINE_DFF9,
CONTRACT_LINE_DFF10,
CONTRACT_LINE_DFF11,
CONTRACT_LINE_DFF12,
CONTRACT_LINE_DFF13,
CONTRACT_LINE_DFF14,
CONTRACT_LINE_DFF15,
ASSET_DATE_DELIVERY_EXPECTED,
ASSET_DATE_FUNDING_EXPECTED,
ASSET_RESIDUAL_PERCENT,
ASSET_RES_GUARANTOR_TYPE_CODE,
ASSET_RES_GUARANTOR_TYPE,
ASSET_RESIDUAL_AMT_GUARANTEE,
ASSET_RESIDUAL_AMOUNT,
CONTRACT_LINE_NAME,
ASSET_UPFRONT_TAX,
ASSET_BILL_TO_SITE_USE_ID,
ASSET_BILL_TO_ADDRESS,
ASSET_BILLING_PAYMENT_METHOD,
ASSET_TERMINATION_DATE,
-- FA
FIXED_ASSET_LINE_ID,
FA_MODEL_NUMBER,
FA_MANUFACTURER_NAME,
FA_ASSET_LOCATION_NAME,
FA_ASSET_LOCATION_ID,
FA_ASSET_KEY_ID,
FA_ASSET_ID,
FA_ASSET_KEY,
ASSET_BILLING_BANK_ACCOUNT,
ASSET_BILLING_BANK,
INV_SUPPLIER_INVOICE_NUMBER,
INV_SUPPLIER_INVOICE_LINE_ID,
INV_SUPPLIER_INVOICE_DATE,
INV_SUPPLR_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE,
--SUPPLIER_VENDOR_NAME,
--SUPPLIER_VENDOR_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
--FEE
FEE_TYPE_CODE,
FEE_TYPE,
FEE_OR_SERVICE_NAME,
--FEE_SUPPLIER_ID,
--FEE_SUPPLIER,
LINE_SUPPLIER_ID,
LINE_SUPPLIER,
LINE_SUPPLIER_NUMBER,
FEE_OR_SERVICE_EFFECTIVE_FROM,
FEE_OR_SERVICE_EFFECTIVE_TO,
FEE_OR_SERVICE_TOTAL_AMOUNT,
FEE_OR_SERVICE_FREQUENCY_CODE,
FEE_OR_SERVICE_FREQUENCY,
FEE_OR_SERV_NUMBER_OF_PERIODS,
FEE_OR_SERV_AMOUNT_PER_PERIOD,
FEE_ROLLOVER_QUOTE_ID,
FEE_ROLLOVER_QUOTE_NUMBER,
FEE_ROLLOVER_CONTRACT_NUMBER,
FEE_INITIAL_DIRECT_COST,
FEE_TRX_TAX_YN,
MODEL_LINE_ID,
INVENTORY_UNIT_COST,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_ORG_ID,
INVENTORY_TOTAL_UNITS,
INVENTORY_ITEM_NAME,
INVENTORY_ITEM_DESCRIPTION,
IB_INSTALL_SITE_ID,
IB_INSTALL_SITE_NUMBER,
IB_INSTALL_SITE_USE_ID,
IB_INSTALL_SITE,
IB_SERIAL_NUMBER,
LINE_PTH_EVG_PAYOUT_BASIS_CODE,
LINE_PTH_EVG_PAYOUT_BASIS,
--LINE_PTH_EVG_START_DATE,
LINE_PTH_EVG_PYOUT_FORMULA_ID,
LINE_PTH_EVG_PYOUT_FORMULA,
LINE_PTH_EVG_STREAM_TYPE_ID,
LINE_PTH_EVG_STREAM_TYPE_CODE,
LINE_PTH_EVG_STREAM_TYPE,
LINE_PTH_BASE_START_DATE,
LINE_PTH_BASE_PAY_BASIS_CODE,
LINE_PTH_BASE_PAYOUT_BASIS,
LINE_PTH_BASE_STREAM_TYPE_ID,
LINE_PTH_BASE_STREAM_TYPE_CODE,
LINE_PTH_BASE_STREAM_TYPE,
USAGE_TYPE_CODE,
USAGE_FIXED_QUANTITY,
USAGE_PRICE_LIST_ID,
USAGE_PRICE_LIST_NAME,
USAGE_PERIOD_CODE,
USAGE_PERIOD,
USAGE_NO_OF_PERIOD,
USAGE_MINIMUM_QTY,
USAGE_LEVEL_FLAG,
USAGE_DEFAULT_QUANTITY,
USAGE_BASE_READING_UOM_CODE,
USAGE_BASE_READING_UOM,
USAGE_BASE_READING,
USAGE_AVG_MONTHLY_COUNTER_FLAG,
USAGE_TYPE,
SERVICE_CONTRACT_NUMBER,
--SERVICE_SUPPLIER,
SERVICE_PAYMENT_TYPE,
INSURANCE_QUOTE_NUMBER,
INSURANCE_POLICY_ID,
INSURANCE_POLICY_NUMBER,
INSURANCE_POLICY_TYPE_CODE,
INSURANCE_POLICY_TYPE,
INSURANCE_POLICY_STATUS_CODE,
INSURANCE_POLICY_STATUS,
INSURANCE_PROVIDER,
INSURANCE_POLICY_LOCATION,
INSURANCE_PAYMENT_FREQUENCY,
INS_POLICY_LESSOR_INSURED_YN,
INS_POLICY_LESSOR_PAYEE_YN,
INSURANCE_POLICY_EFF_FROM,
INSURANCE_POLICY_EFF_TO,
INSURANCE_PRODUCT_ID,
INSURANCE_PRODUCT,
--INSURANCE_FACTOR_CODE,
-- INSURANCE_FACTOR,
INSURANCE_FACTOR_VALUE,
INSURANCE_NAME_OF_INSURED,
INSURANCE_COVERED_AMOUNT,
INSURANCE_CALC_PREMIUM,
INSURANCE_ACTIVATION_DATE,
INSURANCE_Cancellation_date,
INSURANCE_SALES_REP,
INSURANCE_TOT_PREMIUM,
--corp_book
FA_CORP_BOOK_TYPE_CODE,
FA_CORPORATE_BOOK,
FA_CORP_SALVAGE_VALUE,
FA_CORP_SALVAGE_VALUE_PERCENT,
FA_CORP_SAL_VALUE_BASIS_CODE,
FA_CORP_SAL_VALUE_BASIS,
FA_CORP_DEP_METHOD_ID,
FA_CORP_DEP_METHOD_CODE,
FA_CORP_DEP_METHOD_NAME,
FA_CORP_DEP_LIFE_IN_MONTHS,
FA_CORP_DEP_RATE,
FA_CORP_CATEGORY_ID,
FA_CORP_CATEGORY,
FA_CORP_ORIG_COST,
FA_CORP_DEP_COST,
FA_CORP_DATE_IN_SERVICE,
--taxbook
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_DEP_RATE,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
--mg_bbok
FA_MG_BOOK_TYPE_CODE,
FA_MG_BOOK,
FA_MG_SAL_VALUE_BASIS_CODE,
FA_MG_SAL_VALUE_BASIS,
FA_MG_SALVAGE_VALUE,
FA_MG_SALVAGE_VALUE_PERCENT,
FA_MG_DEP_RATE,
FA_MG_DEP_LIFE_IN_MONTHS,
FA_MG_DEP_METHOD_CODE,
FA_MG_DEP_METHOD_ID,
FA_MG_DEP_METHOD_NAME,
FA_MG_ORIG_COST,
FA_MG_DEP_COST,
FA_MG_DATE_IN_SERVICE,
--salestax
ASSET_ST_TRANSFER_OF_TITLE,
ASSET_ST_SALE_AND_LEASE_BACK,
ASSET_ST_PURCHASE_OF_LEASE,
ASSET_ST_INTENDED_USE,
ASSET_ST_EQUIPMENT_AGE,
ASSET_ST_ASSET_UPFRONT_TAX,
--propertytax
ASSET_PROP_TAX_APPLICABLE,
ASSET_PROP_TAX_LESSEE_REPORT,
ASSET_PROP_TAX_BILL_METHOD,
--asset_filing_lien
ASSET_FILING_LIEN_TYPE_CODE,
ASSET_FILING_LIEN_TYPE,
ASSET_FILING_LIEN_NUMBER,
ASSET_FILING_LIEN_DATE,
ASSET_FILING_LIEN_STATUS_CODE,
ASSET_FILING_LIEN_STATUS,
ASSET_FILING_JURISDICTION,
ASSET_FILING_SUB_JURISDICTION,
ASSET_FILING_LIEN_EXP_DATE,
ASSET_FILING_LIEN_CONT_NUMBER,
ASSET_FILING_LIEN_CONT_DATE,
ASSET_FILING_LIENHOLDER,
--ASSET_FILING_TITLE
ASSET_FILING_TITLE_TYPE_CODE,
ASSET_FILING_TITLE_TYPE,
ASSET_FILING_TITLE_NUMBER,
ASSET_FILING_TITLE_ISSUER,
ASSET_FILING_TITLE_DATE,
ASSET_FILING_TITLE_CUSTODIAN,
ASSET_FILING_TITLE_REGIS_NUM,
ASSET_FILING_TITLE_LOCATION,
ASSET_FILING_TITLE_REGIS_LOC,
ASSET_FILING_PAYEE_SITE,
ASSET_FILING_REGIS_EXP_DATE,
--formulas
TOTAL_ASSET_CAP_SERVICE_AMOUNT,
LINE_ACCUMULATED_DEPRECIATION,
LINE_CAPITALIZED_REDUCTION,
TOTAL_ASSET_CAP_FEE_AMOUNT,
LINE_CAPITALIZED_INTEREST,
LINE_DISCOUNT,
LINE_ASSET_COST,
LINE_UNBILLED_DUE_AMOUNT,
LINE_OEC,
TOTAL_ASSET_TRADEIN_AMOUNT,
TOTAL_ACTIVE_LINE_FINANCED_AMT,
LINE_RESIDUAL_AMOUNT,
LINE_RENT_AMOUNT,
LINE_BILLED_RENTS,
(NVL(LINE_BILLED_RENTS,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) LINE_BILLED_RENTS_FUNC,
(NVL(LINE_TOTAL_BILLED,0) - NVL(LINE_BILLED_RENTS,0) ) LINE_BILLED_NON_RENTS,
((NVL(LINE_TOTAL_BILLED,0) - NVL(LINE_BILLED_RENTS,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) LINE_BILLED_NON_RENTS_FUNC,
TOTAL_ASSET_FINANCED_FEE_AMT,
TOTAL_ASSET_ROLLOVER_FEE_AMT,
(LINE_OEC - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - LINE_CAPITALIZED_REDUCTION + TOTAL_ASSET_CAP_FEE_AMOUNT
+ LINE_CAPITALIZED_INTEREST) LINE_CAP_AMOUNT,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF',
(nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)),
'LEASEST', (nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)),
'LOAN', nvl(LINE_PRINCIPAL_BALANCE,0),
'LOAN-REVOLVING', nvl(LINE_PRINCIPAL_BALANCE,0),
'LEASEOP', (nvl(LINE_OEC,0) - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - nvl(LINE_CAPITALIZED_REDUCTION,0) +
nvl(TOTAL_ASSET_CAP_FEE_AMOUNT,0) + nvl(LINE_CAPITALIZED_INTEREST,0) - nvl(LINE_ACCUMULATED_DEPRECIATION,0) -
nvl(TOTAL_ASSET_SUBSIDY_AMOUNT,0)),
0))),0) LINE_NET_INVESTMENT,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF',
(nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0))* nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEST', (nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN', nvl(LINE_PRINCIPAL_BALANCE,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN-REVOLVING', nvl(LINE_PRINCIPAL_BALANCE,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEOP', (nvl(LINE_OEC,0) - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - nvl(LINE_CAPITALIZED_REDUCTION,0) +
nvl(TOTAL_ASSET_CAP_FEE_AMOUNT,0) + nvl(LINE_CAPITALIZED_INTEREST,0) - nvl(LINE_ACCUMULATED_DEPRECIATION,0) -
nvl(TOTAL_ASSET_SUBSIDY_AMOUNT,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
0))),0) LINE_NET_INVESTMENT_FUNC,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
TOTAL_ASSET_ADDON_COST,
TOTAL_ASSET_SUBSIDY_AMOUNT,
TOTAL_ASSET_SUBSIDY_OVRD_AMT,
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_parent_request_id REQUEST_ID,
-- 1 REQUEST_ID,
chr.CONTRACT_NUMBER CONTRACT_NUMBER,
chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
chr.STS_CODE CONTRACT_STATUS_CODE,
chr.CURRENCY_CODE CONTRACT_CURRENCY,
khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
chr.START_DATE CONTRACT_START_DATE,
chr.END_DATE CONTRACT_END_DATE,
khr.ID CONTRACT_ID,
khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
khr.TERM_DURATION CONTRACT_TERM_DURATION,
khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
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,
pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
pdt.REPORTING_PDT_ID CONTRACT_REPORTING_PRODUCT_ID,
pdt.REPORTING_PRODUCT CONTRACT_REPORTING_PRODUCT,
pdt.INTEREST_CALCULATION_BASIS INTEREST_CALCLATION_BASIS_CODE,
pdt.REVENUE_RECOGNITION_METHOD REVENUE_RECOGNTION_METHOD_CODE,
pdt.INTEREST_CALCULATION_MEANING INTEREST_CALCULATION_BASIS,
pdt.REVENUE_RECOGNITION_MEANING REVENUE_RECOGNITION_METHOD,
ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
ledger.ledger_id LEDGER_ID,
ledger.name LEDGER,
legal_entity.name LEGAL_ENTITY,
cust_party.PARTY_NUMBER CNTRCT_CUST_PARTY_NUMBER,
cust_party.party_name CNTRCT_CUSTOMER_PARTY_NAME,
cust_party.PARTY_ID CNTRCT_CUST_PARTY_ID,
chr.CUST_ACCT_ID CNTRCT_CUST_ACOUNT_ID,
cust_accounts.ACCOUNT_NUMBER CNTRCT_CUST_ACCT_NUMBER,
cust_party_site.PARTY_SITE_ID CNTRCT_CUST_PARTY_SITE_ID,
cust_party_site.PARTY_SITE_NUMBER CNTRCT_CUST_PARTY_SITE_NUM,
cust_party_site.PARTY_SITE_NAME CNTRCT_CUST_PARTY_SITE_NAME,
cust_party_site.LOCATION_ID CNTRCT_CUST_PARTY_SITE_LOC_ID,
cust_party_site_use.cpsu_PARTY_SITE_USE_ID CNTRCT_CUST_PARTY_SITE_USE_ID,
krp.INTEREST_INDEX_ID CONTRACT_INTEREST_INDEX_ID,
indx.NAME CONTRACT_INTEREST_INDEX,
cle.id CONTRACT_LINE_ID,
cle.lse_id CONTRACT_LINE_TYPE_ID,
lse.LTY_CODE CONTRACT_LINE_TYPE_CODE,
lse_tl.name CONTRACT_LINE_TYPE_NAME,
cle.sts_code CONTRACT_LINE_STATUS_CODE,
cle_status_tl.meaning CONTRACT_LINE_STATUS,
kle.ATTRIBUTE_CATEGORY CONTRACT_LINE_DFF_CATEGORY,
kle.ATTRIBUTE1 CONTRACT_LINE_DFF1,
kle.ATTRIBUTE2 CONTRACT_LINE_DFF2,
kle.ATTRIBUTE3 CONTRACT_LINE_DFF3,
kle.ATTRIBUTE4 CONTRACT_LINE_DFF4,
kle.ATTRIBUTE5 CONTRACT_LINE_DFF5,
kle.ATTRIBUTE6 CONTRACT_LINE_DFF6,
kle.ATTRIBUTE7 CONTRACT_LINE_DFF7,
kle.ATTRIBUTE8 CONTRACT_LINE_DFF8,
kle.ATTRIBUTE9 CONTRACT_LINE_DFF9,
kle.ATTRIBUTE10 CONTRACT_LINE_DFF10,
kle.ATTRIBUTE11 CONTRACT_LINE_DFF11,
kle.ATTRIBUTE12 CONTRACT_LINE_DFF12,
kle.ATTRIBUTE13 CONTRACT_LINE_DFF13,
kle.ATTRIBUTE14 CONTRACT_LINE_DFF14,
kle.ATTRIBUTE15 CONTRACT_LINE_DFF15,
kle.DATE_DELIVERY_EXPECTED ASSET_DATE_DELIVERY_EXPECTED,
kle.DATE_FUNDING_EXPECTED ASSET_DATE_FUNDING_EXPECTED,
kle.RESIDUAL_PERCENTAGE ASSET_RESIDUAL_PERCENT,
kle.RESIDUAL_CODE ASSET_RES_GUARANTOR_TYPE_CODE,
lookup_res_guarantor.meaning ASSET_RES_GUARANTOR_TYPE,
kle.RESIDUAL_GRNTY_AMOUNT ASSET_RESIDUAL_AMT_GUARANTEE,
kle.RESIDUAL_VALUE ASSET_RESIDUAL_AMOUNT,
cle_TL.NAME CONTRACT_LINE_NAME,
decode(cle.lse_id, 33,(select SUM(NVL(txs.total_tax,0))
from okl_tax_sources txs
where txs.khr_id = khr.id
and txs.kle_id = cle.id
AND 33 = cle.lse_id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'),null) ASSET_UPFRONT_TAX,
decode(cle.lse_id,33, cle.bill_to_site_use_id,null) ASSET_BILL_TO_SITE_USE_ID,
decode(cle.lse_id,33, cust_cust_site_use.location, null) ASSET_BILL_TO_ADDRESS,
(SELECT
orm.name
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okx_receipt_methods_v orm
WHERE rgp.dnz_chr_id = khr.id
AND rgp.cle_id = cle.id
AND cle.lse_id = 33
AND rgp.rgd_code = 'LABILL'
AND rgp.id = rul.rgp_id
AND rgp.dnz_chr_id = rul.dnz_chr_id
AND rul.rule_information_category = 'LAPMTH'
AND orm.id1 = rul.object1_id1) ASSET_BILLING_PAYMENT_METHOD,
decode (cle.lse_id,33, cle.date_terminated, null) ASSET_TERMINATION_DATE,
-- lse_id 42 fixed asset , contract active
decode(khr.DEAL_TYPE, 'LOAN',fa_line_loan.FIXED_ASSET_LINE_ID,
fa_line_non_loan.FIXED_ASSET_LINE_ID) FIXED_ASSET_LINE_ID,
-- CLE_ID CLE_ID,
decode(khr.DEAL_TYPE, 'LOAN',fa_line_loan.MODEL,
fa_line_non_loan.MODEL) FA_MODEL_NUMBER,
decode(khr.DEAL_TYPE, 'LOAN',fa_line_loan.MANUFACTURER_NAME,
fa_line_non_loan.MANUFACTURER_NAME) FA_MANUFACTURER_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.FA_ASSET_LOCATION_NAME ) FA_ASSET_LOCATION_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.FA_ASSET_LOCATION_ID ) FA_ASSET_LOCATION_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.ASSET_KEY_ID ) FA_ASSET_KEY_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.ASSET_ID ) FA_ASSET_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
fa_line_non_loan.ASSET_KEY) FA_ASSET_KEY,
-- bank info , lse_id = 33
decode (cle.lse_id,33, orma_bank_info.name, null) ASSET_BILLING_BANK_ACCOUNT,
decode (cle.lse_id,33, orma_bank_info.bank_name, null) ASSET_BILLING_BANK,
-- supplier 34
supplier.SUPPLIER_INVOICE_NUMBER INV_SUPPLIER_INVOICE_NUMBER,
supplier.SUPPLIER_INV_LINE_ID INV_SUPPLIER_INVOICE_LINE_ID,
supplier.SUPPLIER_INVOICE_DATE INV_SUPPLIER_INVOICE_DATE,
supplier.SUPPLIER_SHIP_TO_SITE_USE_ID INV_SUPPLR_SHIP_TO_SITE_USE_ID,
supplier.SUPPLIER_SHIP_TO_SITE INV_SUPPLIER_SHIP_TO_SITE,
--supplier.SUPPLIER_VENDOR_NAME SUPPLIER_VENDOR_NAME,
--supplier.SUPPLIER_VENDOR_ID SUPPLIER_VENDOR_ID,
supplier.INV_SUPPLIER_SHIP_TO_SITE_ID INV_SUPPLIER_SHIP_TO_SITE_ID,
supplier.INV_SUPPLIER_SHIP_TO_SITE_NUM INV_SUPPLIER_SHIP_TO_SITE_NUM,
-- fee line 52
decode(cle.lse_id,52,KLE.FEE_TYPE, null) FEE_TYPE_CODE,
decode(cle.lse_id,52, LKP_FEE_TYPE.MEANING,null) FEE_TYPE,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_NAME,
decode(lse_id,48,service_line.FEE_OR_SERVICE_NAME,NULL)) FEE_OR_SERVICE_NAME,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER_ID,
48,service_k_line.SUPPLIER_ID,
33,supplier.SUPPLIER_VENDOR_ID,null) LINE_SUPPLIER_ID,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER_NUMBER,
48,service_k_line.SUPPLIER_NUMBER,
33, supplier.SUPPLIER_NUMBER, NULL) LINE_SUPPLIER_NUMBER,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER,
48,service_k_line.SUPPLIER_NAME,
33, supplier.SUPPLIER_VENDOR_NAME, NULL) LINE_SUPPLIER,
decode(cle.lse_id,52,CLE.START_DATE ,
decode(lse_id,48,service_line.FEE_OR_SERVICE_EFFECTIVE_FROM,NULL)) FEE_OR_SERVICE_EFFECTIVE_FROM,
decode(cle.lse_id,52,CLE.END_DATE,
decode(lse_id,48,service_line.FEE_OR_SERVICE_EFFECTIVE_TO,NULL)) FEE_OR_SERVICE_EFFECTIVE_TO,
decode(cle.lse_id,52,KLE.AMOUNT ,
decode(lse_id,48,service_line.TOTAL_FEE_OR_SERVICE_AMOUNT,NULL)) FEE_OR_SERVICE_TOTAL_AMOUNT,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_FREQUENCY_CODE,
decode(lse_id,48,service_line.FEE_OR_SERVICE_FREQUENCY_CODE,NULL)) FEE_OR_SERVICE_FREQUENCY_CODE,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_FREQUENCY,
decode(lse_id,48,service_line.FEE_OR_SERVICE_FREQUENCY,NULL)) FEE_OR_SERVICE_FREQUENCY,
decode(cle.lse_id,52,fee_line.FEE_OR_SERV_NUMBER_OF_PERIODS,
decode(lse_id,48,service_line.FEE_OR_SERV_NUMBER_OF_PERIODS,NULL)) FEE_OR_SERV_NUMBER_OF_PERIODS,
decode(cle.lse_id,52,fee_line.FEE_OR_SERV_AMOUNT_PER_PERIOD,
decode(lse_id,48,service_line.FEE_OR_SERV_AMOUNT_PER_PERIOD,NULL)) FEE_OR_SERV_AMOUNT_PER_PERIOD,
decode(cle.lse_id,52,KLE.QTE_ID,null) FEE_ROLLOVER_QUOTE_ID,
(select tq.quote_number
from okl_trx_quotes_all_b tq
where tq.id = KLE.QTE_ID
and cle.lse_id = 52 ) FEE_ROLLOVER_QUOTE_NUMBER,
(select chr_tq.contract_number
from okl_trx_quotes_all_b tq, okc_k_headers_b chr_tq
where tq.id = KLE.QTE_ID
and tq.khr_id = chr_tq.id
and cle.lse_id = 52) FEE_ROLLOVER_CONTRACT_NUMBER,
decode(cle.lse_id,52, KLE.INITIAL_DIRECT_COST,null) FEE_INITIAL_DIRECT_COST,
/*
decode(cle.lse_id,52,
decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'),null) FEE_TRX_TAX_YN,
*/
decode(cle.lse_id,52,
(decode(kle.FEE_type, 'FINANCED', decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'), 'CAPITALIZED', decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'),null)),
null) FEE_TRX_TAX_YN,
-- item line = 34
item_line.MODEL_LINE_ID MODEL_LINE_ID,
item_line.UNIT_COST INVENTORY_UNIT_COST,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_ID,
item_line.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_ORG_ID,
item_line.INVENTORY_ITEM_ORG_ID) INVENTORY_ITEM_ORG_ID,
item_line.TOTAL_UNITS INVENTORY_TOTAL_UNITS,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_NAME,
item_line.INVENTORY_ITEM_NAME) INVENTORY_ITEM_NAME,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_DESCRIPTION,
item_line.INVENTORY_ITEM_DESCRIPTION) INVENTORY_ITEM_DESCRIPTION,
-- instal line 43, 45
instal_line.INSTALL_SITE_ID IB_INSTALL_SITE_ID,
instal_line.INSTALL_SITE_NUMBER IB_INSTALL_SITE_NUMBER,
instal_line.INSTALL_SITE_USE_ID IB_INSTALL_SITE_USE_ID,
instal_line.INSTALL_SITE_NAME IB_INSTALL_SITE,
-- instal_line.SERIAL_NUMBER IB_SERIAL_NUMBER, -- for active contract
(case when instal_line_number_of_assets.total > 1 THEN
instal_line.MULTIPLE else instal_line.SERIAL_NUMBER
end) IB_SERIAL_NUMBER, -- for non active contract
-- passthrough evergreen 33,52,48
--(case when cle.lse_id in (33,52,48) then
-- to_date(PPH_pth_eveg.PASSTHRU_START_DATE) else null end) LINE_PTH_EVG_START_DATE, -- problem
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PAYOUT_BASIS else null end) LINE_PTH_EVG_PAYOUT_BASIS_CODE,
(case when cle.lse_id in (33,52,48) then
LKP_PYT_BASIS_pth_eveg.MEANING else null end) LINE_PTH_EVG_PAYOUT_BASIS,
(case when cle.lse_id in (33,52,48) then
fmla_pth_eveg.id else to_number(null) end) LINE_PTH_EVG_PYOUT_FORMULA_ID,
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PAYOUT_BASIS_FORMULA else null end) LINE_PTH_EVG_PYOUT_FORMULA,
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PASSTHRU_STREAM_TYPE_ID else null end) LINE_PTH_EVG_STREAM_TYPE_ID,
(case when cle.lse_id in (33,52,48) then
sty_pth_eveg.code else null end) LINE_PTH_EVG_STREAM_TYPE_CODE,
(case when cle.lse_id in (33,52,48) then
STYT_pth_eveg.NAME else null end) LINE_PTH_EVG_STREAM_TYPE,
-- passthrough base 52, 48
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PASSTHRU_START_DATE else null end) LINE_PTH_BASE_START_DATE,
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PAYOUT_BASIS else null end) LINE_PTH_BASE_PAY_BASIS_CODE,
(case when cle.lse_id in (52,48) then
LKP_PTH_BASE.MEANING else null end) LINE_PTH_BASE_PAYOUT_BASIS,
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PASSTHRU_STREAM_TYPE_ID else null end) LINE_PTH_BASE_STREAM_TYPE_ID,
(case when cle.lse_id in (52,48) then
sty_PTH_BASE.code else null end) LINE_PTH_BASE_STREAM_TYPE_CODE,
(case when cle.lse_id in (52,48) then
STYT_PTH_BASE.NAME else null end) LINE_PTH_BASE_STREAM_TYPE,
-- usage line 56
usage_line.USAGE_TYPE_CODE USAGE_TYPE_CODE,
usage_line.FIXED_USAGE_QUANTITY USAGE_FIXED_QUANTITY,
usage_line.PRICE_LIST_ID USAGE_PRICE_LIST_ID,
usage_line.PRICE_LIST_NAME USAGE_PRICE_LIST_NAME,
usage_line.USAGE_PERIOD_CODE USAGE_PERIOD_CODE,
usage_line.USAGE_PERIOD USAGE_PERIOD,
usage_line.USAGE_NO_OF_PERIOD USAGE_NO_OF_PERIOD,
usage_line.MINIMUM_QTY_USAGE USAGE_MINIMUM_QTY,
usage_line.LEVEL_FLAG USAGE_LEVEL_FLAG,
usage_line.DEFAULT_QTY_USAGE USAGE_DEFAULT_QUANTITY,
usage_line.BASE_READING_UOM_CODE USAGE_BASE_READING_UOM_CODE,
usage_line.BASE_READING_UOM USAGE_BASE_READING_UOM,
usage_line.BASE_READING USAGE_BASE_READING,
usage_line.AMCV_FLAG USAGE_AVG_MONTHLY_COUNTER_FLAG,
usage_line.USAGE_TYPE USAGE_TYPE,
-- service contract
decode(cle.lse_id, 48, service_k_line.Service_Contract_Number,
null) SERVICE_CONTRACT_NUMBER,
--decode(cle.lse_id, 48, service_k_line.SUPPLIER_NAME,
-- null) SERVICE_SUPPLIER,
decode(cle.lse_id, 48, service_k_line.ITEM_NAME,
null) SERVICE_PAYMENT_TYPE,
-- insurance line 47
(select IPYB.policy_number
from OKL_INS_POLICIES_B IPYB,
OKL_INS_POLICIES_B IPYB1
where IPYB.khr_id = khr.id
and IPYB.iss_code = 'QUOTE'
and IPYB.ipy_id = IPYB1.id
and IPYB1.policy_number = nvl(insurance.policy_number,'XXX')) INSURANCE_QUOTE_NUMBER,
insurance.ID INSURANCE_POLICY_ID,
insurance.policy_number INSURANCE_POLICY_NUMBER,
insurance.ipy_type INSURANCE_POLICY_TYPE_CODE,
insurance.policy_type INSURANCE_POLICY_TYPE,
insurance.ISS_CODE INSURANCE_POLICY_STATUS_CODE,
insurance.INSURANCE_STATUS INSURANCE_POLICY_STATUS,
-- insurance.ISU_ID INSURANCE_PROVIDER_ID,
insurance.INSURANCE_provider INSURANCE_PROVIDER,
insurance.country INSURANCE_POLICY_LOCATION,
-- insurance.IPF_CODE INSURANCE_PAYMENT_FREQ_CODE,
insurance.payment_frequency1 INSURANCE_PAYMENT_FREQUENCY,
insurance.lessor_insured_yn INS_POLICY_LESSOR_INSURED_YN,
insurance.lessor_payee_yn INS_POLICY_LESSOR_PAYEE_YN,
insurance.date_from INSURANCE_POLICY_EFF_FROM,
insurance.date_to INSURANCE_POLICY_EFF_TO,
insurance.ipt_id INSURANCE_PRODUCT_ID,
insurance.insurance_product_name INSURANCE_PRODUCT,
-- insurance.factor_code INSURANCE_FACTOR_CODE,
--insurance.insurance_factor INSURANCE_FACTOR,
insurance.factor_value INSURANCE_FACTOR_VALUE,
insurance.name_of_insured INSURANCE_NAME_OF_INSURED,
insurance.covered_amount INSURANCE_COVERED_AMOUNT,
insurance.calculated_premium INSURANCE_CALC_PREMIUM,
insurance.activation_date INSURANCE_ACTIVATION_DATE,
insurance.cancellation_date INSURANCE_Cancellation_date,
-- insurance.sales_rep_id INSURANCE_SALES_REP_ID,
insurance.sales_rep_name INSURANCE_SALES_REP,
(CASE WHEN insurance.IPF_CODE IS NULL THEN NULL
WHEN NVL(insurance.PREMIUM,0) = 0 THEN NULL
WHEN insurance.DATE_TO IS NULL THEN NULL
WHEN insurance.DATE_FROM IS NULL THEN NULL
WHEN ROUND(MONTHS_BETWEEN( insurance.DATE_TO,insurance.DATE_FROM)) <= 0 THEN NULL
WHEN insurance.IPF_CODE NOT IN ('HALF_YEARLY', 'QUARTERLY','YEARLY','MONTHLY') THEN NULL
ELSE (insurance.PREMIUM * (ROUND(MONTHS_BETWEEN( insurance.DATE_TO,insurance.DATE_FROM))/
decode(insurance.IPF_CODE, 'HALF_YEARLY', 6, 'QUARTERLY',3,'YEARLY',12,'MONTHLY',1)))
END) INSURANCE_TOT_PREMIUM,
-- corp_book
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_BOOK_TYPE_CODE) FA_CORP_BOOK_TYPE_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORPORATE_BOOK ) FA_CORPORATE_BOOK,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SALVAGE_VALUE ) FA_CORP_SALVAGE_VALUE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SALVAGE_VALUE_PERCENT) FA_CORP_SALVAGE_VALUE_PERCENT,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SAL_VALUE_BASIS_CODE) FA_CORP_SAL_VALUE_BASIS_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_SAL_VALUE_BASIS) FA_CORP_SAL_VALUE_BASIS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_METHOD_ID ) FA_CORP_DEP_METHOD_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_METHOD_CODE) FA_CORP_DEP_METHOD_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_METHOD_NAME) FA_CORP_DEP_METHOD_NAME,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_LIFE_IN_MONTHS) FA_CORP_DEP_LIFE_IN_MONTHS ,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_RATE ) FA_CORP_DEP_RATE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_CATEGORY_ID ) FA_CORP_CATEGORY_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_CATEGORY) FA_CORP_CATEGORY,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_ORIG_COST) FA_CORP_ORIG_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DEP_COST ) FA_CORP_DEP_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
corp_book.FA_CORP_DATE_IN_SERVICE) FA_CORP_DATE_IN_SERVICE,
-- tax book
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_BOOK_TYPE_CODE) FA_TAX_BOOK_TYPE_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_BOOK ) FA_TAX_BOOK,
NULL FA_TAX_SAL_VALUE_BASIS_CODE,
NULL FA_TAX_SAL_VALUE_BASIS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_SALVAGE_VALUE ) FA_TAX_SALVAGE_VALUE,
NULL FA_TAX_SALVAGE_VALUE_PERCENT,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_RATE ) FA_TAX_DEP_RATE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_LIFE_IN_MONTHS ) FA_TAX_DEP_LIFE_IN_MONTHS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_METHOD_CODE ) FA_TAX_DEP_METHOD_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_METHOD_ID ) FA_TAX_DEP_METHOD_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_METHOD_NAME ) FA_TAX_DEP_METHOD_NAME,
NULL FA_TAX_ORIG_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
tax_book.FA_TAX_DEP_COST ) FA_TAX_DEP_COST,
NULL FA_TAX_DATE_IN_SERVICE,
-- mg_bbok
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_BOOK_TYPE_CODE ) FA_MG_BOOK_TYPE_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_BOOK ) FA_MG_BOOK,
NULL FA_MG_SAL_VALUE_BASIS_CODE,
NULL FA_MG_SAL_VALUE_BASIS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_SALVAGE_VALUE ) FA_MG_SALVAGE_VALUE,
NULL FA_MG_SALVAGE_VALUE_PERCENT,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_RATE ) FA_MG_DEP_RATE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_LIFE_IN_MONTHS ) FA_MG_DEP_LIFE_IN_MONTHS,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_METHOD_CODE ) FA_MG_DEP_METHOD_CODE,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_METHOD_ID ) FA_MG_DEP_METHOD_ID,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_METHOD_NAME ) FA_MG_DEP_METHOD_NAME,
NULL FA_MG_ORIG_COST,
DECODE(khr.DEAL_TYPE, 'LOAN',NULL, 'LOAN-REVOLVING', NULL,
mg_book.FA_MG_DEP_COST ) FA_MG_DEP_COST,
NULL FA_MG_DATE_IN_SERVICE,
-- sales tax
sales_tax.ST_TRANSFER_OF_TITLE ASSET_ST_TRANSFER_OF_TITLE,
sales_tax.ST_SALE_AND_LEASE_BACK ASSET_ST_SALE_AND_LEASE_BACK,
sales_tax.ST_PURCHASE_OF_LEASE ASSET_ST_PURCHASE_OF_LEASE,
sales_tax.ST_INTENDED_USE ASSET_ST_INTENDED_USE,
sales_tax.ST_EQUIPMENT_AGE ASSET_ST_EQUIPMENT_AGE,
sales_tax.ST_ASSET_UPFRONT_TAX ASSET_ST_ASSET_UPFRONT_TAX,
-- property tax
property_tax.PROP_TAX_APPLICABLE ASSET_PROP_TAX_APPLICABLE,
property_tax.PROP_TAX_LESSEE_REPORT ASSET_PROP_TAX_LESSEE_REPORT,
property_tax.PROP_TAX_BILL_METHOD ASSET_PROP_TAX_BILL_METHOD,
-- asset_filing_lien
rl_asset_filing_lien.RULE_INFORMATION1 ASSET_FILING_LIEN_TYPE_CODE,
lookups_asset_filing_lien.meaning ASSET_FILING_LIEN_TYPE,
rl_asset_filing_lien.RULE_INFORMATION2 ASSET_FILING_LIEN_NUMBER,
rl_asset_filing_lien.RULE_INFORMATION3 ASSET_FILING_LIEN_DATE,
rl_asset_filing_lien.RULE_INFORMATION4 ASSET_FILING_LIEN_STATUS_CODE,
--rl_asset_filing_lien.RULE_INFORMATION4 ASSET_FILING_LIEN_STATUS,
lookups_asset_filing_lien_sts.meaning ASSET_FILING_LIEN_STATUS,
rl_asset_filing_lien.RULE_INFORMATION5 ASSET_FILING_JURISDICTION,
rl_asset_filing_lien.RULE_INFORMATION6 ASSET_FILING_SUB_JURISDICTION,
rl_asset_filing_lien.RULE_INFORMATION7 ASSET_FILING_LIEN_EXP_DATE,
rl_asset_filing_lien.RULE_INFORMATION8 ASSET_FILING_LIEN_CONT_NUMBER,
rl_asset_filing_lien.RULE_INFORMATION9 ASSET_FILING_LIEN_CONT_DATE,
hz_asset_filing_lien.party_name ASSET_FILING_LIENHOLDER,
-- ASSET_FILING_TITLE
rl_ASSET_FILING_TITLE.rule_information1 ASSET_FILING_TITLE_TYPE_CODE,
lookups_ASSET_FILING_TITLE.meaning ASSET_FILING_TITLE_TYPE,
rl_ASSET_FILING_TITLE.rule_information3 ASSET_FILING_TITLE_NUMBER,
hz_1_ASSET_FILING_TITLE.party_name ASSET_FILING_TITLE_ISSUER,
rl_ASSET_FILING_TITLE.rule_information2 ASSET_FILING_TITLE_DATE,
hz_2_ASSET_FILING_TITLE.party_name ASSET_FILING_TITLE_CUSTODIAN,
rl_ASSET_FILING_TITLE.rule_information4 ASSET_FILING_TITLE_REGIS_NUM,
rl_ASSET_FILING_TITLE.rule_information5 ASSET_FILING_TITLE_LOCATION,
rl_ASSET_FILING_TITLE.rule_information7 ASSET_FILING_TITLE_REGIS_LOC,
rl_ASSET_FILING_TITLE.rule_information6 ASSET_FILING_PAYEE_SITE,
rl_ASSET_FILING_TITLE.rule_information8 ASSET_FILING_REGIS_EXP_DATE,
-- formulas
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_service_capitalized(cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_CAP_SERVICE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_accumulated_deprn(cle.dnz_chr_id,cle.id),0) LINE_ACCUMULATED_DEPRECIATION,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_capital_reduction(cle.dnz_chr_id,cle.id),0),null) LINE_CAPITALIZED_REDUCTION,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_fees_capitalized(cle.dnz_chr_id,cle.id),0) TOTAL_ASSET_CAP_FEE_AMOUNT,
decode(cle.lse_id,33, NVL( kle.capitalized_interest, 0),null) LINE_CAPITALIZED_INTEREST,
-- NVL(OKL_SEEDED_FUNCTIONS_PVT.line_capitalized_interest(chr.id,cle.id),0) LINE_CAPITALIZED_INTEREST,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_discount(cle.dnz_chr_id,cle.id),0),null) LINE_DISCOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_asset_cost(cle.dnz_chr_id,cle.id),0) LINE_ASSET_COST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_unbilled_streams(cle.dnz_chr_id,cle.id),0) LINE_UNBILLED_DUE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_oec(cle.dnz_chr_id,cle.id),0) LINE_OEC,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_tradein(cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_TRADEIN_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_financed_amount(cle.dnz_chr_id,cle.id),0) TOTAL_ACTIVE_LINE_FINANCED_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_residual_amount(cle.dnz_chr_id,cle.id),0) LINE_RESIDUAL_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_rent_amount(cle.dnz_chr_id,cle.id),0) LINE_RENT_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_unearned_income (cle.dnz_chr_id,cle.id),0) LINE_UNEARNED_INCOME,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(cle.dnz_chr_id, cle.id),0) LINE_PRINCIPAL_BALANCE, -- addl para
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd,
okl_strm_type_b sty
where txd.kle_id = kle.id
and txd.sty_id = sty.id
and sty.STREAM_TYPE_PURPOSE = 'RENT') LINE_BILLED_RENTS,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd
where txd.kle_id = kle.id ) LINE_TOTAL_BILLED,
NVL(OKL_SEEDED_FUNCTIONS_PVT.Total_Asset_Financed_Fee_Amt(cle.dnz_chr_id, cle.id),0) TOTAL_ASSET_FINANCED_FEE_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.Total_Asset_Rollover_Fee_Amt(cle.dnz_chr_id, cle.id),0) TOTAL_ASSET_ROLLOVER_FEE_AMT,
--LINE_BILLED_NON_RENTS_FUNC,
--LINE_CAP_AMOUNT,
-- NET_INVESTMENT,
-- 17-Dec-08 Seema Chawla - bug 7635398 : changed Named parameter passing notation to Positional notation
/* NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(p_khr_id => cle.dnz_chr_id,
p_kle_id => cle.id, p_ref_type_code => 'ASSET'),null) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(p_khr_id =>cle.dnz_chr_id,
p_kle_id => cle.id, p_ref_type_code => 'EXPENSE'),null) EXPENSE_FUNDED_AMOUNT,
*/
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt( cle.dnz_chr_id,
cle.id, 'ASSET'),null) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(cle.dnz_chr_id,
cle.id, 'EXPENSE'),null) EXPENSE_FUNDED_AMOUNT,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.total_asset_addon_cost (cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_ADDON_COST,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_line_subsidy_amount (cle.dnz_chr_id,cle.id,NULL),0),null) TOTAL_ASSET_SUBSIDY_AMOUNT,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_line_subsidy_ovrd_amount (cle.dnz_chr_id,cle.id,NULL),0),null) TOTAL_ASSET_SUBSIDY_OVRD_AMT,
l_last_updated_by CREATED_BY,
-- -1 CREATED_BY,
sysdate CREATION_DATE,
l_last_updated_by LAST_UPDATED_BY,
---1 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,
-- -1 LAST_UPDATE_LOGIN,
-- -1 PROGRAM_APPLICATION_ID,
-- -1 PROGRAM_ID,
-- -1 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,
OKL_PRODUCT_PARAMETERS_V pdt,
OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
GL_LEDGERS ledger,
xle_entity_profiles legal_entity,
okc_k_lines_b cle,
okl_k_lines kle,
okc_k_lines_tl cle_tl,
okc_line_styles_b lse,
okc_line_styles_tl lse_tl,
OKC_STATUSES_TL cle_status_tl,
fnd_lookup_values_vl lookup_res_guarantor,
OKL_K_RATE_PARAMS krp,
OKL_INDICES indx,
-- cusotmer
HZ_PARTIES cust_party,
HZ_CUST_ACCOUNTS cust_accounts,
hz_party_sites cust_party_site,
(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,
-- sales tax
(select
cle_id,
dnz_chr_id,
ST_TRANSFER_OF_TITLE,
ST_SALE_AND_LEASE_BACK,
ST_PURCHASE_OF_LEASE,
ST_INTENDED_USE,
ST_EQUIPMENT_AGE,
ST_ASSET_UPFRONT_TAX,
st_row_num
from
(select
rgp.cle_id cle_id,
rgp.dnz_chr_id dnz_chr_id,
decode(RULE_INFORMATION6,'Y','Yes','No') ST_TRANSFER_OF_TITLE,
decode(RULE_INFORMATION7,'Y','Yes','No') ST_SALE_AND_LEASE_BACK,
decode(RULE_INFORMATION8,'Y','Yes','No') ST_PURCHASE_OF_LEASE,
zxf.classification_name ST_INTENDED_USE,
RULE_INFORMATION10 ST_EQUIPMENT_AGE,
lookups.meaning ST_ASSET_UPFRONT_TAX,
row_number() over ( partition by rgp.cle_id
order by rl.RULE_INFORMATION11, rl.rule_information9 nulls last ) st_row_num
from okc_rule_groups_b rgp,
okc_rules_b rl,
fnd_lookup_values_vl lookups,
zx_fc_intended_use_v zxf
where rgp.rgd_code = 'LAASTX'
and rl.rgp_id = rgp.id
and rl.RULE_INFORMATION_CATEGORY = 'LAASTX'
AND lookups.lookup_type (+) = 'OKL_ASSET_UPFRONT_TAX'
and lookups.lookup_code (+) = RULE_INFORMATION11
AND zxf.classification_code (+) = rule_information9 )
where st_row_num = 1 ) sales_tax,
-- property tax
(select
cle_id,
dnz_chr_id,
PROP_TAX_APPLICABLE,
PROP_TAX_LESSEE_REPORT,
PROP_TAX_BILL_METHOD,
prop_tax_row_num
FROM
(select
rgp.cle_id cle_id,
rgp.dnz_chr_id dnz_chr_id,
decode(RULE_INFORMATION1,'Y','Yes','No') PROP_TAX_APPLICABLE,
decode(RULE_INFORMATION2,'Y','Yes','No') PROP_TAX_LESSEE_REPORT,
lookups.meaning PROP_TAX_BILL_METHOD,
row_number() over ( partition by rgp.cle_id
order by rl.RULE_INFORMATION11, rl.rule_information3 nulls last ) prop_tax_row_num
from
okc_rule_groups_b rgp,
okc_rules_b rl,
fnd_lookup_values_vl lookups
where rgp.rgd_code = 'LAASTX'
and rl.rgp_id = rgp.id
and rl.RULE_INFORMATION_CATEGORY = 'LAPRTX'
AND lookups.lookup_type (+) = 'OKL_PROP_TAX_BILL_METHOD'
and lookups.lookup_code (+) = RULE_INFORMATION3)
where prop_tax_row_num = 1 ) property_tax,
-- asset_filing_lien
okc_rule_groups_b rgp_asset_filing_lien,
okc_rules_b rl_asset_filing_lien,
hz_parties hz_asset_filing_lien,
fnd_lookup_values_vl lookups_asset_filing_lien,
fnd_lookup_values_vl lookups_asset_filing_lien_sts,
-- ASSET_FILING_TITLE
okc_rule_groups_b rgp_ASSET_FILING_TITLE,
okc_rules_b rl_ASSET_FILING_TITLE,
fnd_lookup_values_vl lookups_ASSET_FILING_TITLE,
hz_parties hz_1_ASSET_FILING_TITLE,
hz_parties hz_2_ASSET_FILING_TITLE,
-- fa_line, contract active and non active, loan
( SELECT cleb_fa.id FIXED_ASSET_LINE_ID,
cleb_fa.cle_id CLE_ID,
cleb_fa.DNZ_CHR_ID DNZ_CHR_ID,
kle_fa.model_number MODEL,
kle_fa.manufacturer_name MANUFACTURER_NAME
FROM okl_k_lines kle_fa,
okc_k_lines_b cleb_fa
WHERE cleb_fa.id = kle_fa.id
AND cleb_fa.lse_id = 42) fa_line_loan,
-- fa_line, contract active, non-loan
-- fa_line, contract not active, non-loan
(SELECT cleb_fa.id FIXED_ASSET_LINE_ID,
cleb_fa.cle_id CLE_ID,
cleb_fa.DNZ_CHR_ID DNZ_CHR_ID,
txl.model_number MODEL,
txl.manufacturer_name MANUFACTURER_NAME,
loc_kfv_fa.CONCATENATED_SEGMENTS FA_ASSET_LOCATION_NAME,
txl.fa_location_id FA_ASSET_LOCATION_ID,
txl.asset_key_id ASSET_KEY_ID,
to_number(cim_fa.object1_id1) ASSET_ID,
decode(asset_kfv_fa.segment1,null,null,ltrim(rtrim(asset_kfv_fa.segment1,' '),' '))||
decode(asset_kfv_fa.segment2,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment2,' '),' '))||
decode(asset_kfv_fa.segment3,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment3,' '),' '))||
decode(asset_kfv_fa.segment4,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment4,' '),' '))||
decode(asset_kfv_fa.segment5,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment5,' '),' '))||
decode(asset_kfv_fa.segment6,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment6,' '),' '))||
decode(asset_kfv_fa.segment7,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment7,' '),' '))||
decode(asset_kfv_fa.segment8,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment8,' '),' '))||
decode(asset_kfv_fa.segment9,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment9,' '),' '))||
decode(asset_kfv_fa.segment10,null,null,'.'||ltrim(rtrim(asset_kfv_fa.segment10,' '),' ')) Asset_Key
FROM okl_txl_assets_b txl,
okc_k_lines_b cleb_fa,
okc_k_items cim_fa,
--fa_asset_keywords_kfv asset_kfv_fa, -- 21-jan-2008 sechawla 7830362
fa_asset_keywords asset_kfv_fa, -- 21-jan-2008 sechawla 7830362
fa_locations_kfv loc_kfv_fa
WHERE cleb_fa.lse_id = 42
AND cleb_fa.id = txl.kle_id
AND txl.fa_location_id = loc_kfv_fa.location_id(+)
and cleb_fa.id = cim_fa.cle_id
AND cleb_fa.dnz_chr_id = cim_fa.dnz_chr_id
AND asset_kfv_fa.CODE_COMBINATION_ID(+) = txl.asset_key_id
AND loc_kfv_fa.location_id = txl.fa_location_id) fa_line_non_loan, -- contract not active
-- bank info, lse_id 33
okc_rules_b rul_bank_info,
okc_rule_groups_b rgp_bank_info,
okx_rcpt_method_accounts_v orma_bank_info,
-- supplier 34
(SELECT
cle_id,
dnz_chr_id,
SUPPLIER_INVOICE_NUMBER,
SUPPLIER_INV_LINE_ID,
SUPPLIER_INVOICE_DATE,
SUPPLIER_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
SUPPLIER_SHIP_TO_SITE,
SUPPLIER_VENDOR_NAME,
SUPPLIER_VENDOR_ID,
SUPPLIER_NUMBER,
inv_row_num
FROM
(SELECT
cle.cle_id cle_id,
cle.dnz_chr_id dnz_chr_id,
inv.invoice_number SUPPLIER_INVOICE_NUMBER,
inv.id SUPPLIER_INV_LINE_ID,
inv.date_invoiced SUPPLIER_INVOICE_DATE,
inv.shipping_address_id1 SUPPLIER_SHIP_TO_SITE_USE_ID,
HPS.PARTY_SITE_ID INV_SUPPLIER_SHIP_TO_SITE_ID,
HPS.PARTY_SITE_NUMBER INV_SUPPLIER_SHIP_TO_SITE_NUM,
HPS.PARTY_SITE_NUMBER, SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) SUPPLIER_SHIP_TO_SITE,
pov.vendor_name SUPPLIER_VENDOR_NAME,
cpl.object1_id1 SUPPLIER_VENDOR_ID,
pov.segment1 SUPPLIER_NUMBER,
row_number() over ( partition by inv.cle_id
order by inv.id desc, inv.invoice_number nulls last ) inv_row_num
FROM okl_supp_invoice_dtls inv,
okc_k_party_roles_b cpl,
po_vendors pov,
okc_k_lines_b cle,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all csu
WHERE inv.cle_id (+)= cpl.cle_id
AND cpl.rle_code = 'OKL_VENDOR'
AND cpl.chr_id is NULL
AND pov.vendor_id = cpl.object1_id1
AND cle.id = cpl.cle_id
AND cle.lse_id = 34
AND csu.site_use_id (+) = inv.shipping_address_id1
AND cas.cust_acct_site_id (+) = csu.cust_acct_site_id
AND hps.party_site_id (+) = cas.party_site_id
AND hl.location_id (+) = hps.location_id)
where inv_row_num = 1 ) supplier,
-- fee_line 52
(SELECT
CIM_FEE.CLE_ID CLE_ID,
CIM_FEE.DNZ_CHR_ID DNZ_CHR_ID,
STYT.NAME FEE_OR_SERVICE_NAME,
to_number(CPLB_FEE.OBJECT1_ID1) FEE_SUPPLIER_ID,
pov.vendor_name FEE_SUPPLIER,
pov.segment1 FEE_SUPPLIER_NUMBER,
TO_CHAR(RUL_LAFREQ.OBJECT1_ID1) FEE_OR_SERVICE_FREQUENCY_CODE,
TUOM.NAME FEE_OR_SERVICE_FREQUENCY,
RUL_LAFEXP.RULE_INFORMATION1 FEE_OR_SERV_NUMBER_OF_PERIODS,
RUL_LAFEXP.RULE_INFORMATION2 FEE_OR_SERV_AMOUNT_PER_PERIOD
FROM
OKC_K_ITEMS CIM_FEE,
OKL_STRM_TYPE_TL STYT,
OKC_K_PARTY_ROLES_B CPLB_FEE,
PO_VENDORS POV,
OKC_RULE_GROUPS_B RGP_LAFEXP,
OKC_RULES_B RUL_LAFREQ,
OKC_RULES_B RUL_LAFEXP,
OKL_TIME_UNITS_V TUOM
WHERE
CIM_FEE.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
AND STYT.ID = CIM_FEE.OBJECT1_ID1
AND STYT.LANGUAGE = USERENV('LANG')
AND CPLB_FEE.CLE_ID (+) = CIM_FEE.CLE_ID
AND CPLB_FEE.DNZ_CHR_ID (+) = CIM_FEE.DNZ_CHR_ID
AND cplb_fee.rle_code (+) = 'OKL_VENDOR'
AND POV.VENDOR_ID (+) = CPLB_FEE.OBJECT1_ID1
AND RGP_LAFEXP.DNZ_CHR_ID (+) = CIM_FEE.DNZ_CHR_ID
AND RGP_LAFEXP.CLE_ID (+) = CIM_FEE.CLE_ID
AND RGP_LAFEXP.RGD_CODE (+) = 'LAFEXP'
AND RUL_LAFREQ.RGP_ID (+) = RGP_LAFEXP.ID
AND RUL_LAFREQ.RULE_INFORMATION_CATEGORY (+) = 'LAFREQ'
AND RUL_LAFEXP.RGP_ID (+) = RGP_LAFEXP.ID
AND RUL_LAFEXP.RULE_INFORMATION_CATEGORY (+) = 'LAFEXP'
AND TUOM.ID1 (+) = RUL_LAFREQ.OBJECT1_ID1 ) fee_line,
fnd_lookup_values_vl LKP_FEE_TYPE,
-- item line_id=34
(SELECT
cleb_item.cle_id cle_id,
cleb_item.dnz_chr_id dnz_chr_id,
cleb_item.id MODEL_LINE_ID,
cleb_item.price_unit UNIT_COST,
to_number(item.object1_id1) INVENTORY_ITEM_ID,
to_number(item.object1_id2) INVENTORY_ITEM_ORG_ID,
item.number_of_items TOTAL_UNITS,
msi.segment1 INVENTORY_ITEM_NAME,
msi.description INVENTORY_ITEM_DESCRIPTION
FROM okc_k_items item,
okc_k_lines_b cleb_item,
MTL_SYSTEM_ITEMS_B msi
WHERE item.cle_id = cleb_item.id
AND cleb_item.lse_id = 34
AND msi.inventory_item_id = item.object1_id1
AND msi.organization_id = item.object1_id2) item_line,
-- instal line 43, 45 contract active
-- instal_line 43,45 -- contract not active
(SELECT
cle_id,
dnz_chr_id,
INSTALL_SITE_USE_ID,
INSTALL_SITE_NAME,
INSTALL_SITE_ID,
INSTALL_SITE_NUMBER,
SERIAL_NUMBER,
MULTIPLE,
instal_site_row_num
from
(SELECT
cleb_inst.cle_id cle_id,
cleb_inst.dnz_chr_id dnz_chr_id,
iti.id,
to_number(iti.object_id1_new) INSTALL_SITE_USE_ID,
SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) INSTALL_SITE_NAME,
hps.party_site_id INSTALL_SITE_ID,
hps.party_site_number INSTALL_SITE_NUMBER,
iti.serial_number SERIAL_NUMBER,
lookup.MEANING MULTIPLE,
row_number() over ( partition by cleb_inst.cle_id
order by iti.object_id1_new nulls last ) instal_site_row_num
FROM okc_k_lines_b cleb_inst,
okc_k_lines_b cleb_ib,
okl_txl_itm_insts iti,
hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpu,
fnd_lookup_values_vl lookup
WHERE cleb_inst.lse_id = 43
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = 45
AND iti.kle_id = cleb_ib.id
AND hpu.party_site_use_id = iti.object_id1_new
AND hps.party_site_id = hpu.party_site_id
AND hl.location_id = hps.location_id
and lookup.lookup_type like 'OKL_ECC_VALUE_TYPE'
and lookup.lookup_code like 'MULTIPLE')
where instal_site_row_num = 1
) instal_line, -- contract not active
-- this is for inactive contract instal_line 43,45
( select
NVL(count(cleb_inst.cle_id),0) total,
cleb_inst.cle_id,
cleb_inst.dnz_chr_id
FROM okc_k_lines_b cleb_inst,
okc_k_lines_b cleb_ib,
okl_txl_itm_insts iti
WHERE cleb_inst.lse_id = 43
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = 45
AND iti.kle_id = cleb_ib.id
group by cleb_inst.cle_id, cleb_inst.dnz_chr_id) instal_line_number_of_assets, -- this is for inactive contract
-- service line 48
(SELECT
cleb_svc.id cle_id,
msit.description FEE_OR_SERVICE_NAME,
cleb_svc.start_date FEE_OR_SERVICE_EFFECTIVE_FROM,
cleb_svc.end_date FEE_OR_SERVICE_EFFECTIVE_TO,
kle_svc.amount TOTAL_FEE_OR_SERVICE_AMOUNT,
rul_lafreq.object1_id1 FEE_OR_SERVICE_FREQUENCY_CODE,
tuom.name FEE_OR_SERVICE_FREQUENCY,
rul_lafexp.rule_information1 FEE_OR_SERV_NUMBER_OF_PERIODS,
rul_lafexp.rule_information2 FEE_OR_SERV_AMOUNT_PER_PERIOD
FROM
OKC_K_LINES_B CLEB_SVC,
OKL_K_LINES KLE_SVC,
OKC_K_ITEMS CIM_SVC,
MTL_SYSTEM_ITEMS_TL MSIT,
OKC_RULE_GROUPS_B RGP_LAFEXP,
OKC_RULES_B RUL_LAFREQ,
OKC_RULES_B RUL_LAFEXP,
OKL_TIME_UNITS_V TUOM,
OKC_LINE_STYLES_B LNSTYLE
WHERE
cleb_svc.id = kle_svc.id
AND cim_svc.cle_id = cleb_svc.id
AND cim_svc.dnz_chr_id = cleb_svc.dnz_chr_id
AND cim_svc.jtot_object1_code = 'OKX_SERVICE'
AND msit.inventory_item_id = cim_svc.object1_id1
AND msit.organization_id = cim_svc.object1_id2
AND msit.language = USERENV('LANG')
AND rgp_lafexp.dnz_chr_id (+) = cleb_svc.dnz_chr_id
AND rgp_lafexp.cle_id (+) = cleb_svc.id
AND rgp_lafexp.rgd_code (+) = 'LAFEXP'
AND rul_lafreq.rgp_id (+) = rgp_lafexp.id
AND rul_lafreq.rule_information_category (+) = 'LAFREQ'
AND rul_lafexp.rgp_id (+) = rgp_lafexp.id
AND rul_lafexp.rule_information_category (+) = 'LAFEXP'
AND tuom.id1 (+) = rul_lafreq.object1_id1
AND LNSTYLE.LTY_CODE='SOLD_SERVICE'
-- and cleb_svc.dnz_chr_id = 139068
) service_line, -- lse_id 48
-- passthrough_evergreen lse_id 33,48,52
okl_strm_type_b sty_pth_eveg,
OKL_STRM_TYPE_TL STYT_pth_eveg,
OKL_PARTY_PAYMENT_HDR PPH_pth_eveg,
fnd_lookup_values_vl LKP_PYT_BASIS_pth_eveg,
okl_formulae_v fmla_pth_eveg,
-- passthrough_base 52, 48
okl_strm_type_b sty_PTH_BASE,
OKL_STRM_TYPE_TL STYT_PTH_BASE,
OKL_PARTY_PAYMENT_HDR PPH_PTH_BASE,
fnd_lookup_values_vl LKP_PTH_BASE,
-- usage line 56
(SELECT
CLE.ID CLE_ID,
mtl.inventory_item_id INVENTORY_ITEM_ID,
mtl.segment1 INVENTORY_ITEM_NAME,
mtl_tl.DESCRIPTION INVENTORY_ITEM_DESCRIPTION,
OKX_PRICE.id1 PRICE_LIST_ID,
OKX_PRICE.NAME PRICE_LIST_NAME,
to_number(RUL.RULE_INFORMATION1) MINIMUM_QTY_USAGE,
to_number(RUL.RULE_INFORMATION2) DEFAULT_QTY_USAGE,
to_number(RUL.RULE_INFORMATION7) FIXED_USAGE_QUANTITY,
RUL.RULE_INFORMATION8 USAGE_PERIOD_CODE,
--RUL.RULE_INFORMATION8 USAGE_PERIOD, --sechawla 8-jan-09 7628760
uom.UNIT_OF_MEASURE USAGE_PERIOD,--sechawla 8-jan-09 7628760
to_number(RUL.RULE_INFORMATION9) USAGE_NO_OF_PERIOD,
RUL.RULE_INFORMATION4 LEVEL_FLAG,
RUL.RULE_INFORMATION5 BASE_READING,
OBJECT3_ID1 BASE_READING_UOM_CODE,
RUL.RULE_INFORMATION3 AMCV_FLAG,
RUL.RULE_INFORMATION6 USAGE_TYPE_CODE,
RUL.DNZ_CHR_ID CHR_ID,
RG.CLE_ID usage_LINE_ID,
mtl.ORGANIZATION_ID INVENTORY_ITEM_ORG_ID,
lookup_read.meaning BASE_READING_UOM,
lookup_usage.meaning USAGE_TYPE
FROM
OKC_RULES_B RUL,
OKC_RULE_GROUPS_B RG,
mtl_system_items_b mtl,
mtl_system_items_tl mtl_tl,
OKX_LIST_HEADERS_V OKX_PRICE,
OKC_K_LINES_B CLE,
fnd_lookup_values_vl lookup_read,
fnd_lookup_values_vl lookup_usage,
OKX_UNITS_OF_MEASURE_V uom --sechawla 8-jan-09 7628760
WHERE
RULE_INFORMATION_CATEGORY = 'LAUSBB'
AND RUL.RGP_ID = RG.ID
AND RG.RGD_CODE = 'LAUSBB'
AND mtl.USAGE_ITEM_FLAG = 'Y'
AND mtl.inventory_item_id = RUL.OBJECT1_ID1
AND to_char(mtl.organization_id) = RUL.OBJECT1_ID2
AND OKX_PRICE.LIST_TYPE_CODE = 'PRL'
AND OKX_PRICE.ID1 = RUL.OBJECT2_ID1
AND OKX_PRICE.ID2 = RUL.OBJECT2_ID2
AND CLE.ID = RG.CLE_ID
AND CLE.DNZ_CHR_ID = RG.DNZ_CHR_ID
AND CLE.LSE_ID = 56
AND CLE.STS_CODE <> 'ABANDONED'
-- and CLE.DNZ_CHR_ID = 73036
and mtl.INVENTORY_ITEM_ID = mtl_tl.INVENTORY_ITEM_ID
and mtl.ORGANIZATION_ID = mtl_tl.ORGANIZATION_ID
and mtl_tl.LANGUAGE = userenv('LANG')
AND lookup_read.lookup_type (+) = 'UNIT'
AND lookup_read.LOOKUP_CODE (+) = OBJECT3_ID1
AND lookup_usage.lookup_type (+) = 'OKS_USAGE_TYPE'
AND lookup_usage.LOOKUP_CODE (+) = RUL.RULE_INFORMATION6
and uom.UOM_CODE(+) = RUL.RULE_INFORMATION8 --sechawla 8-jan-09 7628760
) usage_line,
OKL_LA_SERV_INTGR_UV service_k_line,
OKL_INS_POLICIES_UV insurance,
-- corporate book -- contract is active
-- corporate book -- contract not active
( select
cleb_fa.DNZ_CHR_ID DNZ_CHR_ID,
cleb_fa.cle_id cle_id,
okl_asset.CORPORATE_BOOK FA_CORP_BOOK_TYPE_CODE,
fbc.book_type_name FA_CORPORATE_BOOK,
okl_asset.SALVAGE_VALUE FA_CORP_SALVAGE_VALUE,
nvl(okl_asset.PERCENT_SALVAGE_VALUE,'') FA_CORP_SALVAGE_VALUE_PERCENT,
decode(okl_asset.SALVAGE_VALUE,null, decode(okl_asset.PERCENT_SALVAGE_VALUE,null,null,'PCT') ,'AMT') FA_CORP_SAL_VALUE_BASIS_CODE,
decode(okl_asset.SALVAGE_VALUE,null, decode(okl_asset.PERCENT_SALVAGE_VALUE,null,null,'Percent') ,'Amount') FA_CORP_SAL_VALUE_BASIS,
fm.method_id FA_CORP_DEP_METHOD_ID,
okl_asset.DEPRN_METHOD FA_CORP_DEP_METHOD_CODE,
fm.name FA_CORP_DEP_METHOD_NAME,
okl_asset.LIFE_IN_MONTHS FA_CORP_DEP_LIFE_IN_MONTHS,
decode(okl_asset.DEPRN_RATE,null,null,
okl_asset.DEPRN_RATE) FA_CORP_DEP_RATE,
okl_asset.DEPRECIATION_ID FA_CORP_CATEGORY_ID,
decode(cat.segment1,null,null,ltrim(rtrim(cat.segment1,' '),' '))||
decode(cat.segment2,null,null,'.'||ltrim(rtrim(cat.segment2,' '),' '))||
decode(cat.segment3,null,null,'.'||ltrim(rtrim(cat.segment3,' '),' '))||
decode(cat.segment4,null,null,'.'||ltrim(rtrim(cat.segment4,' '),' '))||
decode(cat.segment5,null,null,'.'||ltrim(rtrim(cat.segment5,' '),' '))||
decode(cat.segment6,null,null,'.'||ltrim(rtrim(cat.segment6,' '),' '))||
decode(cat.segment7,null,null,'.'||ltrim(rtrim(cat.segment7,' '),' '))
FA_CORP_CATEGORY,
okl_asset.original_cost FA_CORP_ORIG_COST,
okl_asset.DEPRECIATION_COST FA_CORP_DEP_COST,
okl_asset.IN_SERVICE_DATE FA_CORP_DATE_IN_SERVICE
FROM okc_k_lines_b cleb_fa,
OKL_TXL_ASSETS_B okl_asset,
fa_book_controls fbc,
fa_methods fm,
fa_categories cat
where
cleb_fa.id = okl_asset.kle_id
AND fbc.book_type_code(+) = okl_asset.CORPORATE_BOOK
AND okl_asset.DEPRN_METHOD = FM.METHOD_CODE(+)
and okl_asset.LIFE_IN_MONTHS = FM.LIFE_IN_MONTHS(+)
and cat.category_id(+) = okl_asset.DEPRECIATION_ID
) corp_book, -- contract not active
-- tax book, contract active
-- tax book, contract not active
(SELECT
cle_id,
dnz_chr_id,
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SALVAGE_VALUE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_RATE,
FA_TAX_DEP_COST,
book_type_code_row_num
FROM
(
select
cleb_fa.cle_id cle_id,
cleb_fa.dnz_chr_id dnz_chr_id,
okl_asset.tax_BOOK FA_TAX_BOOK_TYPE_CODE,
fbc.book_type_name FA_TAX_BOOK,
okl_asset.SALVAGE_VALUE FA_TAX_SALVAGE_VALUE,
fm.method_id FA_TAX_DEP_METHOD_ID,
okl_asset.DEPRN_METHOD_tax FA_TAX_DEP_METHOD_CODE,
fm.name FA_TAX_DEP_METHOD_NAME,
okl_asset.LIFE_IN_MONTHS_tax FA_TAX_DEP_LIFE_IN_MONTHS,
nvl(okl_asset.DEPRN_RATE_tax,'') FA_TAX_DEP_RATE,
okl_asset.COST FA_TAX_DEP_COST,
row_number() over ( partition by okl_asset_l.kle_id
order by okl_asset.tax_BOOK nulls last ) book_type_code_row_num
FROM
okc_k_lines_b cleb_fa,
OKL_TXd_assets_b okl_asset,
okl_txl_assets_b okl_asset_l,
okc_k_headers_all_b chr,
okl_system_params_all sys_param,
fa_book_controls fbc,
fa_methods fm
where
cleb_fa.id = okl_asset_l.kle_id
AND okl_asset.tal_id = okl_asset_l.ID
AND okl_asset_l.DNZ_KHR_ID = chr.id
AND sys_param.org_id = chr.authoring_org_id
AND fbc.book_type_code(+) = okl_asset.tax_BOOK
AND okl_asset.DEPRN_METHOD_tax = FM.METHOD_CODE(+)
and okl_asset.LIFE_IN_MONTHS_tax = FM.LIFE_IN_MONTHS(+)
and okl_asset.tax_BOOK <> NVL(sys_param.RPT_PROD_BOOK_TYPE_CODE,'XXX')
)
where book_type_code_row_num = 1) tax_book, -- contract not active
-- mg_book -- contract active
-- mg_book -- contract not active
(select
cleb_fa.cle_id cle_id,
cleb_fa.dnz_chr_id dnz_chr_id,
okl_asset_D.tax_BOOK FA_MG_BOOK_TYPE_CODE,
fbc.book_type_name FA_MG_BOOK,
okl_asset_D.SALVAGE_VALUE FA_MG_SALVAGE_VALUE,
fm.method_id FA_MG_DEP_METHOD_ID,
okl_asset_D.DEPRN_METHOD_tax FA_MG_DEP_METHOD_CODE,
fm.name FA_MG_DEP_METHOD_NAME,
okl_asset_D.LIFE_IN_MONTHS_tax FA_MG_DEP_LIFE_IN_MONTHS,
nvl(okl_asset_D.DEPRN_RATE_tax,'') FA_MG_DEP_RATE,
okl_asset_D.COST FA_MG_DEP_COST
FROM
okc_k_lines_b cleb_fa,
OKL_TXd_assets_b okl_asset_d,
okl_txl_assets_b okl_asset_l,
okc_k_headers_all_b chr,
okl_system_params_all sys_param,
fa_book_controls fbc,
fa_methods fm
where
cleb_fa.id = okl_asset_l.kle_id
AND okl_asset_d.tal_id = okl_asset_l.ID
AND okl_asset_l.DNZ_KHR_ID = chr.id
AND sys_param.org_id = chr.authoring_org_id
AND fbc.book_type_code(+) = okl_asset_D.tax_BOOK
AND okl_asset_D.DEPRN_METHOD_tax = FM.METHOD_CODE(+)
and okl_asset_D.LIFE_IN_MONTHS_tax = FM.LIFE_IN_MONTHS(+)
and okl_asset_D.tax_BOOK = sys_param.RPT_PROD_BOOK_TYPE_CODE
-- AND rownum = 1
) MG_book -- contract not active
WHERE
chr.id = khr.id
AND chr.SCS_CODE = 'LEASE'
AND opp.object_type LIKE 'CONTRACT_FIN_LINE_EXT-CONTRACT'
AND opp.object_value LIKE chr.contract_number
AND opp.assigned_process like P_ASSIGNED_PROCESS -- assigned_process is unique for each run
AND opp.khr_id = chr.id
AND opp.object_status like 'INACTIVE' -- ACTIVE
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 khr.pdt_id = pdt.id
AND pdt.aes_org_id = chr.authoring_org_id
AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
AND acct_sys_opts.org_id = chr.authoring_org_id
AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
-- customer
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 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_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 indx.ID(+) = krp.INTEREST_INDEX_ID
and krp.khr_id (+) = khr.id
and krp.parameter_type_code (+) = 'ACTUAL'
and krp.EFFECTIVE_TO_DATE (+) is null
AND cle.chr_id = chr.id
-- AND cle.LSE_ID in (33, 52,48, 56, 47)
AND cle.id = kle.id
AND cle_tl.id = cle.id
AND cle_tl.language = USERENV('LANG')
AND lse.id = cle.lse_id
AND lse.id = lse_tl.id
AND lse_tl.language = USERENV('LANG')
AND cle_status_tl.code = cle.sts_code
AND cle_status_tl.language = USERENV('LANG')
AND lookup_res_guarantor.lookup_type (+) = 'OKL_RESIDUAL_CODE'
AND lookup_res_guarantor.lookup_code (+) = kle.RESIDUAL_CODE
AND sales_tax.cle_id (+) = cle.id
AND property_tax.cle_id (+) = cle.id
-- asset_filing_lien
AND rgp_asset_filing_lien.cle_id (+) = cle.id
AND rgp_asset_filing_lien.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
and rgp_asset_filing_lien.rgd_code (+) = 'LAAFLG'
and rl_asset_filing_lien.rgp_id (+) = rgp_asset_filing_lien.id
and rl_asset_filing_lien.RULE_INFORMATION_CATEGORY (+) = 'LAFLLN'
and rl_asset_filing_lien.OBJECT1_ID1 = hz_asset_filing_lien.party_id (+)
AND lookups_asset_filing_lien.lookup_type (+) = 'OKL_FILING_TYPE'
--and lookups_asset_filing_lien.lookup_code (+) = rl_ASSET_FILING_TITLE.RULE_INFORMATION1
and lookups_asset_filing_lien.lookup_code (+) = rl_asset_filing_lien.RULE_INFORMATION1
AND lookups_asset_filing_lien_sts.lookup_type (+) = 'OKL_FILING_STATUS'
and lookups_asset_filing_lien_sts.lookup_code (+) = rl_asset_filing_lien.RULE_INFORMATION4
-- ASSET_FILING_TITLE
AND rgp_ASSET_FILING_TITLE.cle_id (+) = cle.id
AND rgp_ASSET_FILING_TITLE.rgd_code (+) = 'LAAFLG'
and rgp_ASSET_FILING_TITLE.dnz_chr_id (+) = cle.DNZ_CHR_ID
and rl_ASSET_FILING_TITLE.rgp_id (+) = rgp_ASSET_FILING_TITLE.id
and rl_ASSET_FILING_TITLE.RULE_INFORMATION_CATEGORY (+) = 'LAFLTL'
AND lookups_ASSET_FILING_TITLE.lookup_type (+) = 'OKL_FILING_TYPE'
and lookups_ASSET_FILING_TITLE.lookup_code (+) = rl_ASSET_FILING_TITLE.RULE_INFORMATION1
AND hz_1_ASSET_FILING_TITLE.party_id (+) = rl_ASSET_FILING_TITLE.object1_id1
AND hz_2_ASSET_FILING_TITLE.party_id (+) = rl_ASSET_FILING_TITLE.object2_id1
AND fa_line_loan.CLE_ID (+) = cle.id
AND fa_line_loan.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND fa_line_non_loan.CLE_ID (+) = cle.id
AND fa_line_non_loan.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
-- AND bank_info cle.lse_id = 33
AND rgp_bank_info.cle_id (+) = cle.id
AND rgp_bank_info.rgd_code (+) = 'LABILL'
AND rgp_bank_info.id = rul_bank_info.rgp_id (+)
AND rgp_bank_info.dnz_chr_id = rul_bank_info.dnz_chr_id (+)
AND rul_bank_info.rule_information_category (+)= 'LABACC'
AND orma_bank_info.id1 (+) = rul_bank_info.object1_id1
AND supplier.cle_id (+) = cle.id
AND supplier.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND fee_line.CLE_ID (+) = cle.id
AND fee_line.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND LKP_FEE_TYPE.LOOKUP_CODE (+) = nvl(KLE.FEE_TYPE, 'XXX')
AND LKP_FEE_TYPE.LOOKUP_TYPE (+) = 'OKL_FEE_TYPES'
AND item_line.cle_id (+) = cle.id
AND item_line.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND instal_line.cle_id(+) = cle.id
AND instal_line.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
-- begin only for non active contracts
AND instal_line_number_of_assets.cle_id(+) = cle.id
AND instal_line_number_of_assets.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
-- end only for non active contracts
AND service_line.cle_id(+) = cle.id
-- AND passthrough_evergreen cle.lse_id in (33,52,48)
AND PPH_pth_eveg.CLE_ID (+) = cle.id
AND STYT_pth_eveg.ID (+) = PPH_pth_eveg.PASSTHRU_STREAM_TYPE_ID
AND STYT_pth_eveg.LANGUAGE(+) = USERENV('LANG')
and sty_pth_eveg.id (+) = styt_pth_eveg.id
AND fmla_pth_eveg.name (+) = PPH_pth_eveg.PAYOUT_BASIS_FORMULA
AND LKP_PYT_BASIS_pth_eveg.LOOKUP_CODE (+) = PPH_pth_eveg.PAYOUT_BASIS
AND LKP_PYT_BASIS_pth_eveg.LOOKUP_TYPE (+) = 'OKL_PAYOUT_BASIS'
and PPH_pth_eveg.PASSTHRU_TERM (+) = 'EVERGREEN'
-- passthrough_base 48, 52
AND cle.id = PPH_PTH_BASE.CLE_ID (+)
AND STYT_PTH_BASE.ID (+) = PPH_PTH_BASE.PASSTHRU_STREAM_TYPE_ID
AND STYT_PTH_BASE.LANGUAGE (+) = USERENV('LANG')
AND LKP_PTH_BASE.LOOKUP_CODE (+) = PPH_PTH_BASE.PAYOUT_BASIS
AND LKP_PTH_BASE.LOOKUP_TYPE (+) = 'OKL_PAYOUT_BASIS'
and sty_PTH_BASE.id (+) = styt_PTH_BASE.id
and PPH_PTH_BASE.PASSTHRU_TERM (+)= 'BASE'
AND usage_line.cle_id(+) = cle.id
AND service_k_line.cle_id (+) = cle.id
AND insurance.kle_id (+) = cle.id
AND corp_book.cle_id (+) = cle.id
AND corp_book.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND tax_book.cle_id (+) = cle.id
AND tax_book.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND mg_book.cle_id (+) = cle.id
AND mg_book.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
-- parameters section begin
--don't need the following as these paramater matching has been already
--been done in Master program - OKL_K_LINE_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 cle.sts_code like nvl(P_CONTRACT_LINE_STATUS, cle.sts_code)
AND CHR.CONTRACT_NUMBER like NVL(P_CONTRACT_NUMBER, CHR.CONTRACT_NUMBER)
*/
-- OKL_LINE_STYLES
AND lse.LTY_CODE like NVL(P_CONTRACT_LINE_TYPE, lse.LTY_CODE)
--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)
-- 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)
-- 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(fee_line.FEE_SUPPLIER_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(fee_line.FEE_SUPPLIER_ID,-9999))
OR nvl(supplier.SUPPLIER_VENDOR_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(supplier.SUPPLIER_VENDOR_ID,-9999))
OR nvl(service_k_line.SUPPLIER_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(service_k_line.SUPPLIER_ID,-9999))
)
-- P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
AND ( nvl(fee_line.FEE_SUPPLIER_ID,-9999) = nvl(P_VENDOR_NAME,nvl(fee_line.FEE_SUPPLIER_ID,-9999))
OR nvl(supplier.SUPPLIER_VENDOR_ID,-9999) = nvl(P_VENDOR_NAME,nvl(supplier.SUPPLIER_VENDOR_ID,-9999))
OR nvl(service_k_line.SUPPLIER_ID,-9999) = nvl(P_VENDOR_NAME,nvl(service_k_line.SUPPLIER_ID,-9999))
)
AND NVL(tax_book.FA_TAX_BOOK_TYPE_CODE, 'XXX') LIKE NVL(P_TAX_BOOK, NVL(tax_book.FA_TAX_BOOK_TYPE_CODE, 'XXX'))
);
write_to_log('Inserting contract lines P_FA_INFO_YN like N');
INSERT INTO OKL_CNTRCT_LINE_FIN_EXTRACT_T (
REQUEST_ID,
CONTRACT_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_TERM_DURATION,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_LEGAL_ENTITY_ID,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_REPORTING_PRODUCT_ID,
CONTRACT_REPORTING_PRODUCT,
INTEREST_CALCLATION_BASIS_CODE,
REVENUE_RECOGNTION_METHOD_CODE,
INTEREST_CALCULATION_BASIS,
REVENUE_RECOGNITION_METHOD,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
LEGAL_ENTITY,
CNTRCT_CUST_PARTY_NUMBER,
CNTRCT_CUSTOMER_PARTY_NAME,
CNTRCT_CUST_PARTY_ID,
CNTRCT_CUST_ACOUNT_ID,
CNTRCT_CUST_ACCT_NUMBER,
CNTRCT_CUST_PARTY_SITE_ID,
CNTRCT_CUST_PARTY_SITE_NUM,
CNTRCT_CUST_PARTY_SITE_NAME,
CNTRCT_CUST_PARTY_SITE_LOC_ID,
CNTRCT_CUST_PARTY_SITE_USE_ID,
CONTRACT_INTEREST_INDEX_ID,
CONTRACT_INTEREST_INDEX,
CONTRACT_LINE_ID,
CONTRACT_LINE_TYPE_ID,
CONTRACT_LINE_TYPE_CODE,
CONTRACT_LINE_TYPE_NAME,
CONTRACT_LINE_STATUS_CODE,
CONTRACT_LINE_STATUS,
CONTRACT_LINE_DFF_CATEGORY,
CONTRACT_LINE_DFF1,
CONTRACT_LINE_DFF2,
CONTRACT_LINE_DFF3,
CONTRACT_LINE_DFF4,
CONTRACT_LINE_DFF5,
CONTRACT_LINE_DFF6,
CONTRACT_LINE_DFF7,
CONTRACT_LINE_DFF8,
CONTRACT_LINE_DFF9,
CONTRACT_LINE_DFF10,
CONTRACT_LINE_DFF11,
CONTRACT_LINE_DFF12,
CONTRACT_LINE_DFF13,
CONTRACT_LINE_DFF14,
CONTRACT_LINE_DFF15,
ASSET_DATE_DELIVERY_EXPECTED,
ASSET_DATE_FUNDING_EXPECTED,
ASSET_RESIDUAL_PERCENT,
ASSET_RES_GUARANTOR_TYPE_CODE,
ASSET_RES_GUARANTOR_TYPE,
ASSET_RESIDUAL_AMT_GUARANTEE,
ASSET_RESIDUAL_AMOUNT,
CONTRACT_LINE_NAME,
ASSET_UPFRONT_TAX,
ASSET_BILL_TO_SITE_USE_ID,
ASSET_BILL_TO_ADDRESS,
ASSET_BILLING_PAYMENT_METHOD,
ASSET_TERMINATION_DATE,
-- FA
FIXED_ASSET_LINE_ID,
FA_MODEL_NUMBER,
FA_MANUFACTURER_NAME,
FA_ASSET_LOCATION_NAME,
FA_ASSET_LOCATION_ID,
FA_ASSET_KEY_ID,
FA_ASSET_ID,
FA_ASSET_KEY,
ASSET_BILLING_BANK_ACCOUNT,
ASSET_BILLING_BANK,
INV_SUPPLIER_INVOICE_NUMBER,
INV_SUPPLIER_INVOICE_LINE_ID,
INV_SUPPLIER_INVOICE_DATE,
INV_SUPPLR_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE,
--SUPPLIER_VENDOR_NAME,
--SUPPLIER_VENDOR_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
--FEE
FEE_TYPE_CODE,
FEE_TYPE,
FEE_OR_SERVICE_NAME,
--FEE_SUPPLIER_ID,
--FEE_SUPPLIER,
LINE_SUPPLIER_ID,
LINE_SUPPLIER,
LINE_SUPPLIER_NUMBER,
FEE_OR_SERVICE_EFFECTIVE_FROM,
FEE_OR_SERVICE_EFFECTIVE_TO,
FEE_OR_SERVICE_TOTAL_AMOUNT,
FEE_OR_SERVICE_FREQUENCY_CODE,
FEE_OR_SERVICE_FREQUENCY,
FEE_OR_SERV_NUMBER_OF_PERIODS,
FEE_OR_SERV_AMOUNT_PER_PERIOD,
FEE_ROLLOVER_QUOTE_ID,
FEE_ROLLOVER_QUOTE_NUMBER,
FEE_ROLLOVER_CONTRACT_NUMBER,
FEE_INITIAL_DIRECT_COST,
FEE_TRX_TAX_YN,
MODEL_LINE_ID,
INVENTORY_UNIT_COST,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_ORG_ID,
INVENTORY_TOTAL_UNITS,
INVENTORY_ITEM_NAME,
INVENTORY_ITEM_DESCRIPTION,
IB_INSTALL_SITE_ID,
IB_INSTALL_SITE_NUMBER,
IB_INSTALL_SITE_USE_ID,
IB_INSTALL_SITE,
IB_SERIAL_NUMBER,
LINE_PTH_EVG_PAYOUT_BASIS_CODE,
LINE_PTH_EVG_PAYOUT_BASIS,
--LINE_PTH_EVG_START_DATE,
LINE_PTH_EVG_PYOUT_FORMULA_ID,
LINE_PTH_EVG_PYOUT_FORMULA,
LINE_PTH_EVG_STREAM_TYPE_ID,
LINE_PTH_EVG_STREAM_TYPE_CODE,
LINE_PTH_EVG_STREAM_TYPE,
LINE_PTH_BASE_START_DATE,
LINE_PTH_BASE_PAY_BASIS_CODE,
LINE_PTH_BASE_PAYOUT_BASIS,
LINE_PTH_BASE_STREAM_TYPE_ID,
LINE_PTH_BASE_STREAM_TYPE_CODE,
LINE_PTH_BASE_STREAM_TYPE,
USAGE_TYPE_CODE,
USAGE_FIXED_QUANTITY,
USAGE_PRICE_LIST_ID,
USAGE_PRICE_LIST_NAME,
USAGE_PERIOD_CODE,
USAGE_PERIOD,
USAGE_NO_OF_PERIOD,
USAGE_MINIMUM_QTY,
USAGE_LEVEL_FLAG,
USAGE_DEFAULT_QUANTITY,
USAGE_BASE_READING_UOM_CODE,
USAGE_BASE_READING_UOM,
USAGE_BASE_READING,
USAGE_AVG_MONTHLY_COUNTER_FLAG,
USAGE_TYPE,
SERVICE_CONTRACT_NUMBER,
--SERVICE_SUPPLIER,
SERVICE_PAYMENT_TYPE,
INSURANCE_QUOTE_NUMBER,
INSURANCE_POLICY_ID,
INSURANCE_POLICY_NUMBER,
INSURANCE_POLICY_TYPE_CODE,
INSURANCE_POLICY_TYPE,
INSURANCE_POLICY_STATUS_CODE,
INSURANCE_POLICY_STATUS,
INSURANCE_PROVIDER,
INSURANCE_POLICY_LOCATION,
INSURANCE_PAYMENT_FREQUENCY,
INS_POLICY_LESSOR_INSURED_YN,
INS_POLICY_LESSOR_PAYEE_YN,
INSURANCE_POLICY_EFF_FROM,
INSURANCE_POLICY_EFF_TO,
INSURANCE_PRODUCT_ID,
INSURANCE_PRODUCT,
--INSURANCE_FACTOR_CODE,
-- INSURANCE_FACTOR,
INSURANCE_FACTOR_VALUE,
INSURANCE_NAME_OF_INSURED,
INSURANCE_COVERED_AMOUNT,
INSURANCE_CALC_PREMIUM,
INSURANCE_ACTIVATION_DATE,
INSURANCE_Cancellation_date,
INSURANCE_SALES_REP,
INSURANCE_TOT_PREMIUM,
--corp_book
FA_CORP_BOOK_TYPE_CODE,
FA_CORPORATE_BOOK,
FA_CORP_SALVAGE_VALUE,
FA_CORP_SALVAGE_VALUE_PERCENT,
FA_CORP_SAL_VALUE_BASIS_CODE,
FA_CORP_SAL_VALUE_BASIS,
FA_CORP_DEP_METHOD_ID,
FA_CORP_DEP_METHOD_CODE,
FA_CORP_DEP_METHOD_NAME,
FA_CORP_DEP_LIFE_IN_MONTHS,
FA_CORP_DEP_RATE,
FA_CORP_CATEGORY_ID,
FA_CORP_CATEGORY,
FA_CORP_ORIG_COST,
FA_CORP_DEP_COST,
FA_CORP_DATE_IN_SERVICE,
--taxbook
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_DEP_RATE,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
--mg_bbok
FA_MG_BOOK_TYPE_CODE,
FA_MG_BOOK,
FA_MG_SAL_VALUE_BASIS_CODE,
FA_MG_SAL_VALUE_BASIS,
FA_MG_SALVAGE_VALUE,
FA_MG_SALVAGE_VALUE_PERCENT,
FA_MG_DEP_RATE,
FA_MG_DEP_LIFE_IN_MONTHS,
FA_MG_DEP_METHOD_CODE,
FA_MG_DEP_METHOD_ID,
FA_MG_DEP_METHOD_NAME,
FA_MG_ORIG_COST,
FA_MG_DEP_COST,
FA_MG_DATE_IN_SERVICE,
--salestax
ASSET_ST_TRANSFER_OF_TITLE,
ASSET_ST_SALE_AND_LEASE_BACK,
ASSET_ST_PURCHASE_OF_LEASE,
ASSET_ST_INTENDED_USE,
ASSET_ST_EQUIPMENT_AGE,
ASSET_ST_ASSET_UPFRONT_TAX,
--propertytax
ASSET_PROP_TAX_APPLICABLE,
ASSET_PROP_TAX_LESSEE_REPORT,
ASSET_PROP_TAX_BILL_METHOD,
--asset_filing_lien
ASSET_FILING_LIEN_TYPE_CODE,
ASSET_FILING_LIEN_TYPE,
ASSET_FILING_LIEN_NUMBER,
ASSET_FILING_LIEN_DATE,
ASSET_FILING_LIEN_STATUS_CODE,
ASSET_FILING_LIEN_STATUS,
ASSET_FILING_JURISDICTION,
ASSET_FILING_SUB_JURISDICTION,
ASSET_FILING_LIEN_EXP_DATE,
ASSET_FILING_LIEN_CONT_NUMBER,
ASSET_FILING_LIEN_CONT_DATE,
ASSET_FILING_LIENHOLDER,
--ASSET_FILING_TITLE
ASSET_FILING_TITLE_TYPE_CODE,
ASSET_FILING_TITLE_TYPE,
ASSET_FILING_TITLE_NUMBER,
ASSET_FILING_TITLE_ISSUER,
ASSET_FILING_TITLE_DATE,
ASSET_FILING_TITLE_CUSTODIAN,
ASSET_FILING_TITLE_REGIS_NUM,
ASSET_FILING_TITLE_LOCATION,
ASSET_FILING_TITLE_REGIS_LOC,
ASSET_FILING_PAYEE_SITE,
ASSET_FILING_REGIS_EXP_DATE,
--formulas
TOTAL_ASSET_CAP_SERVICE_AMOUNT,
LINE_ACCUMULATED_DEPRECIATION,
LINE_CAPITALIZED_REDUCTION,
TOTAL_ASSET_CAP_FEE_AMOUNT,
LINE_CAPITALIZED_INTEREST,
LINE_DISCOUNT,
LINE_ASSET_COST,
LINE_UNBILLED_DUE_AMOUNT,
LINE_OEC,
TOTAL_ASSET_TRADEIN_AMOUNT,
TOTAL_ACTIVE_LINE_FINANCED_AMT,
LINE_RESIDUAL_AMOUNT,
LINE_RENT_AMOUNT,
LINE_BILLED_RENTS,
LINE_BILLED_RENTS_FUNC,
LINE_BILLED_NON_RENTS,
LINE_BILLED_NON_RENTS_FUNC,
TOTAL_ASSET_FINANCED_FEE_AMT,
TOTAL_ASSET_ROLLOVER_FEE_AMT,
LINE_CAP_AMOUNT,
LINE_NET_INVESTMENT,
LINE_NET_INVESTMENT_FUNC,
-- LINE_TOT_BILLED_RECEIVABLE,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
TOTAL_ASSET_ADDON_COST,
TOTAL_ASSET_SUBSIDY_AMOUNT,
TOTAL_ASSET_SUBSIDY_OVRD_AMT,
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_NUMBER,
CONTRACT_OPERATING_UNIT_ID,
CONTRACT_STATUS_CODE,
CONTRACT_CURRENCY,
CONTRACT_CURRENCY_CONV_TYPE,
CONTRACT_CURRENCY_CONV_RATE,
CONTRACT_CURRENCY_CONV_DATE,
CONTRACT_START_DATE,
CONTRACT_END_DATE,
CONTRACT_ID,
CONTRACT_FINANCIAL_PRODUCT_ID,
CONTRACT_TERM_DURATION,
CONTRACT_BOOK_CLASS_CODE,
CONTRACT_BOOK_CLASSIFICATION,
CONTRACT_LEGAL_ENTITY_ID,
OPERATING_UNIT,
OPERATING_UNIT_SHORT_CODE,
CONTRACT_STATUS_TYPE_CODE,
CONTRACT_STATUS,
CONTRACT_FINANCIAL_PRODUCT,
CONTRACT_REPORTING_PRODUCT_ID,
CONTRACT_REPORTING_PRODUCT,
INTEREST_CALCLATION_BASIS_CODE,
REVENUE_RECOGNTION_METHOD_CODE,
INTEREST_CALCULATION_BASIS,
REVENUE_RECOGNITION_METHOD,
FUNCTIONAL_CURRENCY,
LEDGER_ID,
LEDGER,
LEGAL_ENTITY,
CNTRCT_CUST_PARTY_NUMBER,
CNTRCT_CUSTOMER_PARTY_NAME,
CNTRCT_CUST_PARTY_ID,
CNTRCT_CUST_ACOUNT_ID,
CNTRCT_CUST_ACCT_NUMBER,
CNTRCT_CUST_PARTY_SITE_ID,
CNTRCT_CUST_PARTY_SITE_NUM,
CNTRCT_CUST_PARTY_SITE_NAME,
CNTRCT_CUST_PARTY_SITE_LOC_ID,
CNTRCT_CUST_PARTY_SITE_USE_ID,
CONTRACT_INTEREST_INDEX_ID,
CONTRACT_INTEREST_INDEX,
CONTRACT_LINE_ID,
CONTRACT_LINE_TYPE_ID,
CONTRACT_LINE_TYPE_CODE,
CONTRACT_LINE_TYPE_NAME,
CONTRACT_LINE_STATUS_CODE,
CONTRACT_LINE_STATUS,
CONTRACT_LINE_DFF_CATEGORY,
CONTRACT_LINE_DFF1,
CONTRACT_LINE_DFF2,
CONTRACT_LINE_DFF3,
CONTRACT_LINE_DFF4,
CONTRACT_LINE_DFF5,
CONTRACT_LINE_DFF6,
CONTRACT_LINE_DFF7,
CONTRACT_LINE_DFF8,
CONTRACT_LINE_DFF9,
CONTRACT_LINE_DFF10,
CONTRACT_LINE_DFF11,
CONTRACT_LINE_DFF12,
CONTRACT_LINE_DFF13,
CONTRACT_LINE_DFF14,
CONTRACT_LINE_DFF15,
ASSET_DATE_DELIVERY_EXPECTED,
ASSET_DATE_FUNDING_EXPECTED,
ASSET_RESIDUAL_PERCENT,
ASSET_RES_GUARANTOR_TYPE_CODE,
ASSET_RES_GUARANTOR_TYPE,
ASSET_RESIDUAL_AMT_GUARANTEE,
ASSET_RESIDUAL_AMOUNT,
CONTRACT_LINE_NAME,
ASSET_UPFRONT_TAX,
ASSET_BILL_TO_SITE_USE_ID,
ASSET_BILL_TO_ADDRESS,
ASSET_BILLING_PAYMENT_METHOD,
ASSET_TERMINATION_DATE,
-- FA
FIXED_ASSET_LINE_ID,
FA_MODEL_NUMBER,
FA_MANUFACTURER_NAME,
FA_ASSET_LOCATION_NAME,
FA_ASSET_LOCATION_ID,
FA_ASSET_KEY_ID,
FA_ASSET_ID,
FA_ASSET_KEY,
ASSET_BILLING_BANK_ACCOUNT,
ASSET_BILLING_BANK,
INV_SUPPLIER_INVOICE_NUMBER,
INV_SUPPLIER_INVOICE_LINE_ID,
INV_SUPPLIER_INVOICE_DATE,
INV_SUPPLR_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE,
--SUPPLIER_VENDOR_NAME,
--SUPPLIER_VENDOR_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
--FEE
FEE_TYPE_CODE,
FEE_TYPE,
FEE_OR_SERVICE_NAME,
--FEE_SUPPLIER_ID,
--FEE_SUPPLIER,
LINE_SUPPLIER_ID,
LINE_SUPPLIER,
LINE_SUPPLIER_NUMBER,
FEE_OR_SERVICE_EFFECTIVE_FROM,
FEE_OR_SERVICE_EFFECTIVE_TO,
FEE_OR_SERVICE_TOTAL_AMOUNT,
FEE_OR_SERVICE_FREQUENCY_CODE,
FEE_OR_SERVICE_FREQUENCY,
FEE_OR_SERV_NUMBER_OF_PERIODS,
FEE_OR_SERV_AMOUNT_PER_PERIOD,
FEE_ROLLOVER_QUOTE_ID,
FEE_ROLLOVER_QUOTE_NUMBER,
FEE_ROLLOVER_CONTRACT_NUMBER,
FEE_INITIAL_DIRECT_COST,
FEE_TRX_TAX_YN,
MODEL_LINE_ID,
INVENTORY_UNIT_COST,
INVENTORY_ITEM_ID,
INVENTORY_ITEM_ORG_ID,
INVENTORY_TOTAL_UNITS,
INVENTORY_ITEM_NAME,
INVENTORY_ITEM_DESCRIPTION,
IB_INSTALL_SITE_ID,
IB_INSTALL_SITE_NUMBER,
IB_INSTALL_SITE_USE_ID,
IB_INSTALL_SITE,
IB_SERIAL_NUMBER,
LINE_PTH_EVG_PAYOUT_BASIS_CODE,
LINE_PTH_EVG_PAYOUT_BASIS,
--LINE_PTH_EVG_START_DATE,
LINE_PTH_EVG_PYOUT_FORMULA_ID,
LINE_PTH_EVG_PYOUT_FORMULA,
LINE_PTH_EVG_STREAM_TYPE_ID,
LINE_PTH_EVG_STREAM_TYPE_CODE,
LINE_PTH_EVG_STREAM_TYPE,
LINE_PTH_BASE_START_DATE,
LINE_PTH_BASE_PAY_BASIS_CODE,
LINE_PTH_BASE_PAYOUT_BASIS,
LINE_PTH_BASE_STREAM_TYPE_ID,
LINE_PTH_BASE_STREAM_TYPE_CODE,
LINE_PTH_BASE_STREAM_TYPE,
USAGE_TYPE_CODE,
USAGE_FIXED_QUANTITY,
USAGE_PRICE_LIST_ID,
USAGE_PRICE_LIST_NAME,
USAGE_PERIOD_CODE,
USAGE_PERIOD,
USAGE_NO_OF_PERIOD,
USAGE_MINIMUM_QTY,
USAGE_LEVEL_FLAG,
USAGE_DEFAULT_QUANTITY,
USAGE_BASE_READING_UOM_CODE,
USAGE_BASE_READING_UOM,
USAGE_BASE_READING,
USAGE_AVG_MONTHLY_COUNTER_FLAG,
USAGE_TYPE,
SERVICE_CONTRACT_NUMBER,
--SERVICE_SUPPLIER,
SERVICE_PAYMENT_TYPE,
INSURANCE_QUOTE_NUMBER,
INSURANCE_POLICY_ID,
INSURANCE_POLICY_NUMBER,
INSURANCE_POLICY_TYPE_CODE,
INSURANCE_POLICY_TYPE,
INSURANCE_POLICY_STATUS_CODE,
INSURANCE_POLICY_STATUS,
INSURANCE_PROVIDER,
INSURANCE_POLICY_LOCATION,
INSURANCE_PAYMENT_FREQUENCY,
INS_POLICY_LESSOR_INSURED_YN,
INS_POLICY_LESSOR_PAYEE_YN,
INSURANCE_POLICY_EFF_FROM,
INSURANCE_POLICY_EFF_TO,
INSURANCE_PRODUCT_ID,
INSURANCE_PRODUCT,
--INSURANCE_FACTOR_CODE,
-- INSURANCE_FACTOR,
INSURANCE_FACTOR_VALUE,
INSURANCE_NAME_OF_INSURED,
INSURANCE_COVERED_AMOUNT,
INSURANCE_CALC_PREMIUM,
INSURANCE_ACTIVATION_DATE,
INSURANCE_Cancellation_date,
INSURANCE_SALES_REP,
INSURANCE_TOT_PREMIUM,
--corp_book
FA_CORP_BOOK_TYPE_CODE,
FA_CORPORATE_BOOK,
FA_CORP_SALVAGE_VALUE,
FA_CORP_SALVAGE_VALUE_PERCENT,
FA_CORP_SAL_VALUE_BASIS_CODE,
FA_CORP_SAL_VALUE_BASIS,
FA_CORP_DEP_METHOD_ID,
FA_CORP_DEP_METHOD_CODE,
FA_CORP_DEP_METHOD_NAME,
FA_CORP_DEP_LIFE_IN_MONTHS,
FA_CORP_DEP_RATE,
FA_CORP_CATEGORY_ID,
FA_CORP_CATEGORY,
FA_CORP_ORIG_COST,
FA_CORP_DEP_COST,
FA_CORP_DATE_IN_SERVICE,
--taxbook
FA_TAX_BOOK_TYPE_CODE,
FA_TAX_BOOK,
FA_TAX_SAL_VALUE_BASIS_CODE,
FA_TAX_SAL_VALUE_BASIS,
FA_TAX_SALVAGE_VALUE,
FA_TAX_SALVAGE_VALUE_PERCENT,
FA_TAX_DEP_RATE,
FA_TAX_DEP_LIFE_IN_MONTHS,
FA_TAX_DEP_METHOD_CODE,
FA_TAX_DEP_METHOD_ID,
FA_TAX_DEP_METHOD_NAME,
FA_TAX_ORIG_COST,
FA_TAX_DEP_COST,
FA_TAX_DATE_IN_SERVICE,
--mg_bbok
FA_MG_BOOK_TYPE_CODE,
FA_MG_BOOK,
FA_MG_SAL_VALUE_BASIS_CODE,
FA_MG_SAL_VALUE_BASIS,
FA_MG_SALVAGE_VALUE,
FA_MG_SALVAGE_VALUE_PERCENT,
FA_MG_DEP_RATE,
FA_MG_DEP_LIFE_IN_MONTHS,
FA_MG_DEP_METHOD_CODE,
FA_MG_DEP_METHOD_ID,
FA_MG_DEP_METHOD_NAME,
FA_MG_ORIG_COST,
FA_MG_DEP_COST,
FA_MG_DATE_IN_SERVICE,
--salestax
ASSET_ST_TRANSFER_OF_TITLE,
ASSET_ST_SALE_AND_LEASE_BACK,
ASSET_ST_PURCHASE_OF_LEASE,
ASSET_ST_INTENDED_USE,
ASSET_ST_EQUIPMENT_AGE,
ASSET_ST_ASSET_UPFRONT_TAX,
--propertytax
ASSET_PROP_TAX_APPLICABLE,
ASSET_PROP_TAX_LESSEE_REPORT,
ASSET_PROP_TAX_BILL_METHOD,
--asset_filing_lien
ASSET_FILING_LIEN_TYPE_CODE,
ASSET_FILING_LIEN_TYPE,
ASSET_FILING_LIEN_NUMBER,
ASSET_FILING_LIEN_DATE,
ASSET_FILING_LIEN_STATUS_CODE,
ASSET_FILING_LIEN_STATUS,
ASSET_FILING_JURISDICTION,
ASSET_FILING_SUB_JURISDICTION,
ASSET_FILING_LIEN_EXP_DATE,
ASSET_FILING_LIEN_CONT_NUMBER,
ASSET_FILING_LIEN_CONT_DATE,
ASSET_FILING_LIENHOLDER,
--ASSET_FILING_TITLE
ASSET_FILING_TITLE_TYPE_CODE,
ASSET_FILING_TITLE_TYPE,
ASSET_FILING_TITLE_NUMBER,
ASSET_FILING_TITLE_ISSUER,
ASSET_FILING_TITLE_DATE,
ASSET_FILING_TITLE_CUSTODIAN,
ASSET_FILING_TITLE_REGIS_NUM,
ASSET_FILING_TITLE_LOCATION,
ASSET_FILING_TITLE_REGIS_LOC,
ASSET_FILING_PAYEE_SITE,
ASSET_FILING_REGIS_EXP_DATE,
--formulas
TOTAL_ASSET_CAP_SERVICE_AMOUNT,
LINE_ACCUMULATED_DEPRECIATION,
LINE_CAPITALIZED_REDUCTION,
TOTAL_ASSET_CAP_FEE_AMOUNT,
LINE_CAPITALIZED_INTEREST,
LINE_DISCOUNT,
LINE_ASSET_COST,
LINE_UNBILLED_DUE_AMOUNT,
LINE_OEC,
TOTAL_ASSET_TRADEIN_AMOUNT,
TOTAL_ACTIVE_LINE_FINANCED_AMT,
LINE_RESIDUAL_AMOUNT,
LINE_RENT_AMOUNT,
LINE_BILLED_RENTS,
(NVL(LINE_BILLED_RENTS,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) LINE_BILLED_RENTS_FUNC,
(NVL(LINE_TOTAL_BILLED,0) - NVL(LINE_BILLED_RENTS,0) ) LINE_BILLED_NON_RENTS,
((NVL(LINE_TOTAL_BILLED,0) - NVL(LINE_BILLED_RENTS,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1)) LINE_BILLED_NON_RENTS_FUNC,
TOTAL_ASSET_FINANCED_FEE_AMT,
TOTAL_ASSET_ROLLOVER_FEE_AMT,
(LINE_OEC - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - LINE_CAPITALIZED_REDUCTION + TOTAL_ASSET_CAP_FEE_AMOUNT
+ LINE_CAPITALIZED_INTEREST) LINE_CAP_AMOUNT,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF',
(nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)),
'LEASEST', (nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)),
'LOAN', nvl(LINE_PRINCIPAL_BALANCE,0),
'LOAN-REVOLVING', nvl(LINE_PRINCIPAL_BALANCE,0),
'LEASEOP', (nvl(LINE_OEC,0) - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - nvl(LINE_CAPITALIZED_REDUCTION,0) +
nvl(TOTAL_ASSET_CAP_FEE_AMOUNT,0) + nvl(LINE_CAPITALIZED_INTEREST,0) - nvl(LINE_ACCUMULATED_DEPRECIATION,0) -
nvl(TOTAL_ASSET_SUBSIDY_AMOUNT,0)),
0))),0) LINE_NET_INVESTMENT,
NVL(DECODE(CONTRACT_STATUS_CODE, 'EXPIRED',0,
(DECODE(CONTRACT_BOOK_CLASS_CODE, 'LEASEDF',
(nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0))* nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEST', (nvl(LINE_RENT_AMOUNT,0) + nvl(LINE_RESIDUAL_AMOUNT,0) - nvl(LINE_UNEARNED_INCOME,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN', nvl(LINE_PRINCIPAL_BALANCE,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LOAN-REVOLVING', nvl(LINE_PRINCIPAL_BALANCE,0) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
'LEASEOP', (nvl(LINE_OEC,0) - nvl(TOTAL_ASSET_TRADEIN_AMOUNT,0) - nvl(LINE_CAPITALIZED_REDUCTION,0) +
nvl(TOTAL_ASSET_CAP_FEE_AMOUNT,0) + nvl(LINE_CAPITALIZED_INTEREST,0) - nvl(LINE_ACCUMULATED_DEPRECIATION,0) -
nvl(TOTAL_ASSET_SUBSIDY_AMOUNT,0)) * nvl(CONTRACT_CURRENCY_CONV_RATE,1),
0))),0) LINE_NET_INVESTMENT_FUNC,
ASSETS_FUNDED_AMOUNT,
EXPENSE_FUNDED_AMOUNT,
TOTAL_ASSET_ADDON_COST,
TOTAL_ASSET_SUBSIDY_AMOUNT,
TOTAL_ASSET_SUBSIDY_OVRD_AMT,
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_parent_request_id REQUEST_ID,
-- 1 REQUEST_ID,
chr.CONTRACT_NUMBER CONTRACT_NUMBER,
chr.AUTHORING_ORG_ID CONTRACT_OPERATING_UNIT_ID,
chr.STS_CODE CONTRACT_STATUS_CODE,
chr.CURRENCY_CODE CONTRACT_CURRENCY,
khr.CURRENCY_CONVERSION_TYPE CONTRACT_CURRENCY_CONV_TYPE,
khr.CURRENCY_CONVERSION_RATE CONTRACT_CURRENCY_CONV_RATE,
khr.CURRENCY_CONVERSION_DATE CONTRACT_CURRENCY_CONV_DATE,
chr.START_DATE CONTRACT_START_DATE,
chr.END_DATE CONTRACT_END_DATE,
khr.ID CONTRACT_ID,
khr.PDT_ID CONTRACT_FINANCIAL_PRODUCT_ID,
khr.TERM_DURATION CONTRACT_TERM_DURATION,
khr.DEAL_TYPE CONTRACT_BOOK_CLASS_CODE,
lookup_book_class.meaning CONTRACT_BOOK_CLASSIFICATION,
khr.LEGAL_ENTITY_ID CONTRACT_LEGAL_ENTITY_ID,
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,
pdt.NAME CONTRACT_FINANCIAL_PRODUCT,
pdt.REPORTING_PDT_ID CONTRACT_REPORTING_PRODUCT_ID,
pdt.REPORTING_PRODUCT CONTRACT_REPORTING_PRODUCT,
pdt.INTEREST_CALCULATION_BASIS INTEREST_CALCLATION_BASIS_CODE,
pdt.REVENUE_RECOGNITION_METHOD REVENUE_RECOGNTION_METHOD_CODE,
pdt.INTEREST_CALCULATION_MEANING INTEREST_CALCULATION_BASIS,
pdt.REVENUE_RECOGNITION_MEANING REVENUE_RECOGNITION_METHOD,
ledger.CURRENCY_CODE FUNCTIONAL_CURRENCY,
ledger.ledger_id LEDGER_ID,
ledger.name LEDGER,
legal_entity.name LEGAL_ENTITY,
cust_party.PARTY_NUMBER CNTRCT_CUST_PARTY_NUMBER,
cust_party.party_name CNTRCT_CUSTOMER_PARTY_NAME,
cust_party.PARTY_ID CNTRCT_CUST_PARTY_ID,
chr.CUST_ACCT_ID CNTRCT_CUST_ACOUNT_ID,
cust_accounts.ACCOUNT_NUMBER CNTRCT_CUST_ACCT_NUMBER,
cust_party_site.PARTY_SITE_ID CNTRCT_CUST_PARTY_SITE_ID,
cust_party_site.PARTY_SITE_NUMBER CNTRCT_CUST_PARTY_SITE_NUM,
cust_party_site.PARTY_SITE_NAME CNTRCT_CUST_PARTY_SITE_NAME,
cust_party_site.LOCATION_ID CNTRCT_CUST_PARTY_SITE_LOC_ID,
cust_party_site_use.cpsu_PARTY_SITE_USE_ID CNTRCT_CUST_PARTY_SITE_USE_ID,
krp.INTEREST_INDEX_ID CONTRACT_INTEREST_INDEX_ID,
indx.NAME CONTRACT_INTEREST_INDEX,
cle.id CONTRACT_LINE_ID,
cle.lse_id CONTRACT_LINE_TYPE_ID,
lse.LTY_CODE CONTRACT_LINE_TYPE_CODE,
lse_tl.name CONTRACT_LINE_TYPE_NAME,
cle.sts_code CONTRACT_LINE_STATUS_CODE,
cle_status_tl.meaning CONTRACT_LINE_STATUS,
kle.ATTRIBUTE_CATEGORY CONTRACT_LINE_DFF_CATEGORY,
kle.ATTRIBUTE1 CONTRACT_LINE_DFF1,
kle.ATTRIBUTE2 CONTRACT_LINE_DFF2,
kle.ATTRIBUTE3 CONTRACT_LINE_DFF3,
kle.ATTRIBUTE4 CONTRACT_LINE_DFF4,
kle.ATTRIBUTE5 CONTRACT_LINE_DFF5,
kle.ATTRIBUTE6 CONTRACT_LINE_DFF6,
kle.ATTRIBUTE7 CONTRACT_LINE_DFF7,
kle.ATTRIBUTE8 CONTRACT_LINE_DFF8,
kle.ATTRIBUTE9 CONTRACT_LINE_DFF9,
kle.ATTRIBUTE10 CONTRACT_LINE_DFF10,
kle.ATTRIBUTE11 CONTRACT_LINE_DFF11,
kle.ATTRIBUTE12 CONTRACT_LINE_DFF12,
kle.ATTRIBUTE13 CONTRACT_LINE_DFF13,
kle.ATTRIBUTE14 CONTRACT_LINE_DFF14,
kle.ATTRIBUTE15 CONTRACT_LINE_DFF15,
kle.DATE_DELIVERY_EXPECTED ASSET_DATE_DELIVERY_EXPECTED,
kle.DATE_FUNDING_EXPECTED ASSET_DATE_FUNDING_EXPECTED,
kle.RESIDUAL_PERCENTAGE ASSET_RESIDUAL_PERCENT,
kle.RESIDUAL_CODE ASSET_RES_GUARANTOR_TYPE_CODE,
lookup_res_guarantor.meaning ASSET_RES_GUARANTOR_TYPE,
kle.RESIDUAL_GRNTY_AMOUNT ASSET_RESIDUAL_AMT_GUARANTEE,
kle.RESIDUAL_VALUE ASSET_RESIDUAL_AMOUNT,
cle_TL.NAME CONTRACT_LINE_NAME,
decode(cle.lse_id, 33,(select SUM(NVL(txs.total_tax,0))
from okl_tax_sources txs
where txs.khr_id = khr.id
and txs.kle_id = cle.id
AND 33 = cle.lse_id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'),null) ASSET_UPFRONT_TAX,
decode(cle.lse_id,33, cle.bill_to_site_use_id,null) ASSET_BILL_TO_SITE_USE_ID,
decode(cle.lse_id,33, cust_cust_site_use.location, null) ASSET_BILL_TO_ADDRESS,
(SELECT
orm.name
FROM okc_rules_b rul,
okc_rule_groups_b rgp,
okx_receipt_methods_v orm
WHERE rgp.dnz_chr_id = khr.id
AND rgp.cle_id = cle.id
AND cle.lse_id = 33
AND rgp.rgd_code = 'LABILL'
AND rgp.id = rul.rgp_id
AND rgp.dnz_chr_id = rul.dnz_chr_id
AND rul.rule_information_category = 'LAPMTH'
AND orm.id1 = rul.object1_id1) ASSET_BILLING_PAYMENT_METHOD,
decode (cle.lse_id,33, cle.date_terminated, null) ASSET_TERMINATION_DATE,
-- lse_id 42 fixed asset
NULL FIXED_ASSET_LINE_ID,
NULL FA_MODEL_NUMBER,
NULL FA_MANUFACTURER_NAME,
NULL FA_ASSET_LOCATION_NAME,
NULL FA_ASSET_LOCATION_ID,
NULL FA_ASSET_KEY_ID,
NULL FA_ASSET_ID,
NULL FA_ASSET_KEY,
-- bank info , lse_id = 33
decode (cle.lse_id,33, orma_bank_info.name, null) ASSET_BILLING_BANK_ACCOUNT,
decode (cle.lse_id,33, orma_bank_info.bank_name, null) ASSET_BILLING_BANK,
-- supplier 34
supplier.SUPPLIER_INVOICE_NUMBER INV_SUPPLIER_INVOICE_NUMBER,
supplier.SUPPLIER_INV_LINE_ID INV_SUPPLIER_INVOICE_LINE_ID,
supplier.SUPPLIER_INVOICE_DATE INV_SUPPLIER_INVOICE_DATE,
supplier.SUPPLIER_SHIP_TO_SITE_USE_ID INV_SUPPLR_SHIP_TO_SITE_USE_ID,
supplier.SUPPLIER_SHIP_TO_SITE INV_SUPPLIER_SHIP_TO_SITE,
--supplier.SUPPLIER_VENDOR_NAME SUPPLIER_VENDOR_NAME,
--supplier.SUPPLIER_VENDOR_ID SUPPLIER_VENDOR_ID,
supplier.INV_SUPPLIER_SHIP_TO_SITE_ID INV_SUPPLIER_SHIP_TO_SITE_ID,
supplier.INV_SUPPLIER_SHIP_TO_SITE_NUM INV_SUPPLIER_SHIP_TO_SITE_NUM,
-- fee line 52
decode(cle.lse_id,52,KLE.FEE_TYPE, null) FEE_TYPE_CODE,
decode(cle.lse_id,52, LKP_FEE_TYPE.MEANING,null) FEE_TYPE,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_NAME,
decode(lse_id,48,service_line.FEE_OR_SERVICE_NAME,NULL)) FEE_OR_SERVICE_NAME,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER_ID,
48,service_k_line.SUPPLIER_ID,
33,supplier.SUPPLIER_VENDOR_ID,null) LINE_SUPPLIER_ID,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER_NUMBER,
48,service_k_line.SUPPLIER_NUMBER,
33, supplier.SUPPLIER_NUMBER, NULL) LINE_SUPPLIER_NUMBER,
decode(cle.lse_id,52,fee_line.FEE_SUPPLIER,
48,service_k_line.SUPPLIER_NAME,
33, supplier.SUPPLIER_VENDOR_NAME, NULL) LINE_SUPPLIER,
decode(cle.lse_id,52,CLE.START_DATE ,
decode(lse_id,48,service_line.FEE_OR_SERVICE_EFFECTIVE_FROM,NULL)) FEE_OR_SERVICE_EFFECTIVE_FROM,
decode(cle.lse_id,52,CLE.END_DATE,
decode(lse_id,48,service_line.FEE_OR_SERVICE_EFFECTIVE_TO,NULL)) FEE_OR_SERVICE_EFFECTIVE_TO,
decode(cle.lse_id,52,KLE.AMOUNT ,
decode(lse_id,48,service_line.TOTAL_FEE_OR_SERVICE_AMOUNT,NULL)) FEE_OR_SERVICE_TOTAL_AMOUNT,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_FREQUENCY_CODE,
decode(lse_id,48,service_line.FEE_OR_SERVICE_FREQUENCY_CODE,NULL)) FEE_OR_SERVICE_FREQUENCY_CODE,
decode(cle.lse_id,52,fee_line.FEE_OR_SERVICE_FREQUENCY,
decode(lse_id,48,service_line.FEE_OR_SERVICE_FREQUENCY,NULL)) FEE_OR_SERVICE_FREQUENCY,
decode(cle.lse_id,52,fee_line.FEE_OR_SERV_NUMBER_OF_PERIODS,
decode(lse_id,48,service_line.FEE_OR_SERV_NUMBER_OF_PERIODS,NULL)) FEE_OR_SERV_NUMBER_OF_PERIODS,
decode(cle.lse_id,52,fee_line.FEE_OR_SERV_AMOUNT_PER_PERIOD,
decode(lse_id,48,service_line.FEE_OR_SERV_AMOUNT_PER_PERIOD,NULL)) FEE_OR_SERV_AMOUNT_PER_PERIOD,
decode(cle.lse_id,52,KLE.QTE_ID,null) FEE_ROLLOVER_QUOTE_ID,
(select tq.quote_number
from okl_trx_quotes_all_b tq
where tq.id = KLE.QTE_ID
and cle.lse_id = 52 ) FEE_ROLLOVER_QUOTE_NUMBER,
(select chr_tq.contract_number
from okl_trx_quotes_all_b tq, okc_k_headers_b chr_tq
where tq.id = KLE.QTE_ID
and tq.khr_id = chr_tq.id
and cle.lse_id = 52) FEE_ROLLOVER_CONTRACT_NUMBER,
decode(cle.lse_id,52, KLE.INITIAL_DIRECT_COST,null) FEE_INITIAL_DIRECT_COST,
/*
decode(cle.lse_id,52,
decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'),null) FEE_TRX_TAX_YN,
*/
decode(cle.lse_id,52,
(decode(kle.FEE_type, 'FINANCED', decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'), 'CAPITALIZED', decode(kle.FEE_PURPOSE_CODE ,'SALESTAX','Yes','No'),null)),
null) FEE_TRX_TAX_YN,
-- item line = 34
item_line.MODEL_LINE_ID MODEL_LINE_ID,
item_line.UNIT_COST INVENTORY_UNIT_COST,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_ID,
item_line.INVENTORY_ITEM_ID) INVENTORY_ITEM_ID,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_ORG_ID,
item_line.INVENTORY_ITEM_ORG_ID) INVENTORY_ITEM_ORG_ID,
item_line.TOTAL_UNITS INVENTORY_TOTAL_UNITS,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_NAME,
item_line.INVENTORY_ITEM_NAME) INVENTORY_ITEM_NAME,
decode(cle.lse_id,56,usage_line.INVENTORY_ITEM_DESCRIPTION,
item_line.INVENTORY_ITEM_DESCRIPTION) INVENTORY_ITEM_DESCRIPTION,
-- instal line 43, 45
decode (opp.object_status,'ACTIVE', instal_line_active.INSTALL_SITE_ID,
'INACTIVE', instal_line_not_active.INSTALL_SITE_ID,NULL) IB_INSTALL_SITE_ID,
decode (opp.object_status,'ACTIVE', instal_line_active.INSTALL_SITE_NUMBER,
'INACTIVE', instal_line_not_active.INSTALL_SITE_NUMBER,NULL) IB_INSTALL_SITE_NUMBER,
decode (opp.object_status,'ACTIVE', instal_line_active.INSTALL_SITE_USE_ID,
'INACTIVE', instal_line_not_active.INSTALL_SITE_USE_ID,NULL) IB_INSTALL_SITE_USE_ID,
decode (opp.object_status,'ACTIVE', instal_line_active.INSTALL_SITE_NAME,
'INACTIVE', instal_line_not_active.INSTALL_SITE_NAME,NULL) IB_INSTALL_SITE,
(case
when (opp.object_status like 'ACTIVE' AND instal_line_number_of_assets.total > 1)
THEN instal_line_active.MULTIPLE
when (opp.object_status like 'ACTIVE' AND instal_line_number_of_assets.total = 1)
THEN instal_line_active.SERIAL_NUMBER
when (opp.object_status like 'INACTIVE' AND instal_line_number_of_assets.total > 1)
THEN instal_line_not_active.MULTIPLE
when (opp.object_status like 'INACTIVE' AND instal_line_number_of_assets.total = 1)
THEN instal_line_not_active.SERIAL_NUMBER
ELSE NULL END) IB_SERIAL_NUMBER,
-- passthrough evergreen 33,52,48
--(case when cle.lse_id in (33,52,48) then
-- to_date(PPH_pth_eveg.PASSTHRU_START_DATE) else null end) LINE_PTH_EVG_START_DATE, -- problem
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PAYOUT_BASIS else null end) LINE_PTH_EVG_PAYOUT_BASIS_CODE,
(case when cle.lse_id in (33,52,48) then
LKP_PYT_BASIS_pth_eveg.MEANING else null end) LINE_PTH_EVG_PAYOUT_BASIS,
(case when cle.lse_id in (33,52,48) then
fmla_pth_eveg.id else to_number(null) end) LINE_PTH_EVG_PYOUT_FORMULA_ID,
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PAYOUT_BASIS_FORMULA else null end) LINE_PTH_EVG_PYOUT_FORMULA,
(case when cle.lse_id in (33,52,48) then
PPH_pth_eveg.PASSTHRU_STREAM_TYPE_ID else null end) LINE_PTH_EVG_STREAM_TYPE_ID,
(case when cle.lse_id in (33,52,48) then
sty_pth_eveg.code else null end) LINE_PTH_EVG_STREAM_TYPE_CODE,
(case when cle.lse_id in (33,52,48) then
STYT_pth_eveg.NAME else null end) LINE_PTH_EVG_STREAM_TYPE,
-- passthrough base 52, 48
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PASSTHRU_START_DATE else null end) LINE_PTH_BASE_START_DATE,
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PAYOUT_BASIS else null end) LINE_PTH_BASE_PAY_BASIS_CODE,
(case when cle.lse_id in (52,48) then
LKP_PTH_BASE.MEANING else null end) LINE_PTH_BASE_PAYOUT_BASIS,
(case when cle.lse_id in (52,48) then
PPH_PTH_BASE.PASSTHRU_STREAM_TYPE_ID else null end) LINE_PTH_BASE_STREAM_TYPE_ID,
(case when cle.lse_id in (52,48) then
sty_PTH_BASE.code else null end) LINE_PTH_BASE_STREAM_TYPE_CODE,
(case when cle.lse_id in (52,48) then
STYT_PTH_BASE.NAME else null end) LINE_PTH_BASE_STREAM_TYPE,
-- usage line 56
usage_line.USAGE_TYPE_CODE USAGE_TYPE_CODE,
usage_line.FIXED_USAGE_QUANTITY USAGE_FIXED_QUANTITY,
usage_line.PRICE_LIST_ID USAGE_PRICE_LIST_ID,
usage_line.PRICE_LIST_NAME USAGE_PRICE_LIST_NAME,
usage_line.USAGE_PERIOD_CODE USAGE_PERIOD_CODE,
usage_line.USAGE_PERIOD USAGE_PERIOD,
usage_line.USAGE_NO_OF_PERIOD USAGE_NO_OF_PERIOD,
usage_line.MINIMUM_QTY_USAGE USAGE_MINIMUM_QTY,
usage_line.LEVEL_FLAG USAGE_LEVEL_FLAG,
usage_line.DEFAULT_QTY_USAGE USAGE_DEFAULT_QUANTITY,
usage_line.BASE_READING_UOM_CODE USAGE_BASE_READING_UOM_CODE,
usage_line.BASE_READING_UOM USAGE_BASE_READING_UOM,
usage_line.BASE_READING USAGE_BASE_READING,
usage_line.AMCV_FLAG USAGE_AVG_MONTHLY_COUNTER_FLAG,
usage_line.USAGE_TYPE USAGE_TYPE,
-- service contract
decode(cle.lse_id, 48, service_k_line.Service_Contract_Number,
null) SERVICE_CONTRACT_NUMBER,
--decode(cle.lse_id, 48, service_k_line.SUPPLIER_NAME,
-- null) SERVICE_SUPPLIER,
decode(cle.lse_id, 48, service_k_line.ITEM_NAME,
null) SERVICE_PAYMENT_TYPE,
-- insurance line 47
(select IPYB.policy_number
from OKL_INS_POLICIES_B IPYB,
OKL_INS_POLICIES_B IPYB1
where IPYB.khr_id = khr.id
and IPYB.iss_code = 'QUOTE'
and IPYB.ipy_id = IPYB1.id
and IPYB1.policy_number = nvl(insurance.policy_number,'XXX')) INSURANCE_QUOTE_NUMBER,
insurance.ID INSURANCE_POLICY_ID,
insurance.policy_number INSURANCE_POLICY_NUMBER,
insurance.ipy_type INSURANCE_POLICY_TYPE_CODE,
insurance.policy_type INSURANCE_POLICY_TYPE,
insurance.ISS_CODE INSURANCE_POLICY_STATUS_CODE,
insurance.INSURANCE_STATUS INSURANCE_POLICY_STATUS,
-- insurance.ISU_ID INSURANCE_PROVIDER_ID,
insurance.INSURANCE_provider INSURANCE_PROVIDER,
insurance.country INSURANCE_POLICY_LOCATION,
-- insurance.IPF_CODE INSURANCE_PAYMENT_FREQ_CODE,
insurance.payment_frequency1 INSURANCE_PAYMENT_FREQUENCY,
insurance.lessor_insured_yn INS_POLICY_LESSOR_INSURED_YN,
insurance.lessor_payee_yn INS_POLICY_LESSOR_PAYEE_YN,
insurance.date_from INSURANCE_POLICY_EFF_FROM,
insurance.date_to INSURANCE_POLICY_EFF_TO,
insurance.ipt_id INSURANCE_PRODUCT_ID,
insurance.insurance_product_name INSURANCE_PRODUCT,
-- insurance.factor_code INSURANCE_FACTOR_CODE,
--insurance.insurance_factor INSURANCE_FACTOR,
insurance.factor_value INSURANCE_FACTOR_VALUE,
insurance.name_of_insured INSURANCE_NAME_OF_INSURED,
insurance.covered_amount INSURANCE_COVERED_AMOUNT,
insurance.calculated_premium INSURANCE_CALC_PREMIUM,
insurance.activation_date INSURANCE_ACTIVATION_DATE,
insurance.cancellation_date INSURANCE_Cancellation_date,
-- insurance.sales_rep_id INSURANCE_SALES_REP_ID,
insurance.sales_rep_name INSURANCE_SALES_REP,
(CASE WHEN insurance.IPF_CODE IS NULL THEN NULL
WHEN NVL(insurance.PREMIUM,0) = 0 THEN NULL
WHEN insurance.DATE_TO IS NULL THEN NULL
WHEN insurance.DATE_FROM IS NULL THEN NULL
WHEN ROUND(MONTHS_BETWEEN( insurance.DATE_TO,insurance.DATE_FROM)) <= 0 THEN NULL
WHEN insurance.IPF_CODE NOT IN ('HALF_YEARLY', 'QUARTERLY','YEARLY','MONTHLY') THEN NULL
ELSE (insurance.PREMIUM * (ROUND(MONTHS_BETWEEN( insurance.DATE_TO,insurance.DATE_FROM))/
decode(insurance.IPF_CODE, 'HALF_YEARLY', 6, 'QUARTERLY',3,'YEARLY',12,'MONTHLY',1)))
END) INSURANCE_TOT_PREMIUM,
-- corp_book
NULL FA_CORP_BOOK_TYPE_CODE,
NULL FA_CORPORATE_BOOK,
NULL FA_CORP_SALVAGE_VALUE,
NULL FA_CORP_SALVAGE_VALUE_PERCENT,
NULL FA_CORP_SAL_VALUE_BASIS_CODE,
NULL FA_CORP_SAL_VALUE_BASIS,
NULL FA_CORP_DEP_METHOD_ID,
NULL FA_CORP_DEP_METHOD_CODE,
NULL FA_CORP_DEP_METHOD_NAME,
NULL FA_CORP_DEP_LIFE_IN_MONTHS ,
NULL FA_CORP_DEP_RATE,
NULL FA_CORP_CATEGORY_ID,
NULL FA_CORP_CATEGORY,
NULL FA_CORP_ORIG_COST,
NULL FA_CORP_DEP_COST,
NULL FA_CORP_DATE_IN_SERVICE,
-- tax book
NULL FA_TAX_BOOK_TYPE_CODE,
NULL FA_TAX_BOOK,
null FA_TAX_SAL_VALUE_BASIS_CODE,
NULL FA_TAX_SAL_VALUE_BASIS,
NULL FA_TAX_SALVAGE_VALUE,
NULL FA_TAX_SALVAGE_VALUE_PERCENT,
NULL FA_TAX_DEP_RATE,
NULL FA_TAX_DEP_LIFE_IN_MONTHS,
NULL FA_TAX_DEP_METHOD_CODE,
NULL FA_TAX_DEP_METHOD_ID,
NULL FA_TAX_DEP_METHOD_NAME,
NULL FA_TAX_ORIG_COST,
NULL FA_TAX_DEP_COST,
NULL FA_TAX_DATE_IN_SERVICE,
-- mg_bbok
NULL FA_MG_BOOK_TYPE_CODE,
NULL FA_MG_BOOK,
NULL FA_MG_SAL_VALUE_BASIS_CODE,
NULL FA_MG_SAL_VALUE_BASIS,
NULL FA_MG_SALVAGE_VALUE,
NULL FA_MG_SALVAGE_VALUE_PERCENT,
NULL FA_MG_DEP_RATE,
NULL FA_MG_DEP_LIFE_IN_MONTHS,
NULL FA_MG_DEP_METHOD_CODE,
NULL FA_MG_DEP_METHOD_ID,
NULL FA_MG_DEP_METHOD_NAME,
NULL FA_MG_ORIG_COST,
NULL FA_MG_DEP_COST,
NULL FA_MG_DATE_IN_SERVICE,
-- sales tax
sales_tax.ST_TRANSFER_OF_TITLE ASSET_ST_TRANSFER_OF_TITLE,
sales_tax.ST_SALE_AND_LEASE_BACK ASSET_ST_SALE_AND_LEASE_BACK,
sales_tax.ST_PURCHASE_OF_LEASE ASSET_ST_PURCHASE_OF_LEASE,
sales_tax.ST_INTENDED_USE ASSET_ST_INTENDED_USE,
sales_tax.ST_EQUIPMENT_AGE ASSET_ST_EQUIPMENT_AGE,
sales_tax.ST_ASSET_UPFRONT_TAX ASSET_ST_ASSET_UPFRONT_TAX,
-- property tax
property_tax.PROP_TAX_APPLICABLE ASSET_PROP_TAX_APPLICABLE,
property_tax.PROP_TAX_LESSEE_REPORT ASSET_PROP_TAX_LESSEE_REPORT,
property_tax.PROP_TAX_BILL_METHOD ASSET_PROP_TAX_BILL_METHOD,
-- asset_filing_lien
rl_asset_filing_lien.RULE_INFORMATION1 ASSET_FILING_LIEN_TYPE_CODE,
lookups_asset_filing_lien.meaning ASSET_FILING_LIEN_TYPE,
rl_asset_filing_lien.RULE_INFORMATION2 ASSET_FILING_LIEN_NUMBER,
rl_asset_filing_lien.RULE_INFORMATION3 ASSET_FILING_LIEN_DATE,
rl_asset_filing_lien.RULE_INFORMATION4 ASSET_FILING_LIEN_STATUS_CODE,
--rl_asset_filing_lien.RULE_INFORMATION4 ASSET_FILING_LIEN_STATUS,
lookups_asset_filing_lien_sts.meaning ASSET_FILING_LIEN_STATUS,
rl_asset_filing_lien.RULE_INFORMATION5 ASSET_FILING_JURISDICTION,
rl_asset_filing_lien.RULE_INFORMATION6 ASSET_FILING_SUB_JURISDICTION,
rl_asset_filing_lien.RULE_INFORMATION7 ASSET_FILING_LIEN_EXP_DATE,
rl_asset_filing_lien.RULE_INFORMATION8 ASSET_FILING_LIEN_CONT_NUMBER,
rl_asset_filing_lien.RULE_INFORMATION9 ASSET_FILING_LIEN_CONT_DATE,
hz_asset_filing_lien.party_name ASSET_FILING_LIENHOLDER,
-- ASSET_FILING_TITLE
rl_ASSET_FILING_TITLE.rule_information1 ASSET_FILING_TITLE_TYPE_CODE,
lookups_ASSET_FILING_TITLE.meaning ASSET_FILING_TITLE_TYPE,
rl_ASSET_FILING_TITLE.rule_information3 ASSET_FILING_TITLE_NUMBER,
hz_1_ASSET_FILING_TITLE.party_name ASSET_FILING_TITLE_ISSUER,
rl_ASSET_FILING_TITLE.rule_information2 ASSET_FILING_TITLE_DATE,
hz_2_ASSET_FILING_TITLE.party_name ASSET_FILING_TITLE_CUSTODIAN,
rl_ASSET_FILING_TITLE.rule_information4 ASSET_FILING_TITLE_REGIS_NUM,
rl_ASSET_FILING_TITLE.rule_information5 ASSET_FILING_TITLE_LOCATION,
rl_ASSET_FILING_TITLE.rule_information7 ASSET_FILING_TITLE_REGIS_LOC,
rl_ASSET_FILING_TITLE.rule_information6 ASSET_FILING_PAYEE_SITE,
rl_ASSET_FILING_TITLE.rule_information8 ASSET_FILING_REGIS_EXP_DATE,
-- formulas
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_service_capitalized(cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_CAP_SERVICE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_accumulated_deprn(cle.dnz_chr_id,cle.id),0) LINE_ACCUMULATED_DEPRECIATION,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_capital_reduction(cle.dnz_chr_id,cle.id),0),null) LINE_CAPITALIZED_REDUCTION,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_fees_capitalized(cle.dnz_chr_id,cle.id),0) TOTAL_ASSET_CAP_FEE_AMOUNT,
decode(cle.lse_id,33, NVL( kle.capitalized_interest, 0),null) LINE_CAPITALIZED_INTEREST,
-- NVL(OKL_SEEDED_FUNCTIONS_PVT.line_capitalized_interest(chr.id,cle.id),0) LINE_CAPITALIZED_INTEREST,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_discount(cle.dnz_chr_id,cle.id),0),null) LINE_DISCOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_asset_cost(cle.dnz_chr_id,cle.id),0) LINE_ASSET_COST,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_unbilled_streams(cle.dnz_chr_id,cle.id),0) LINE_UNBILLED_DUE_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_oec(cle.dnz_chr_id,cle.id),0) LINE_OEC,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_tradein(cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_TRADEIN_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_financed_amount(cle.dnz_chr_id,cle.id),0) TOTAL_ACTIVE_LINE_FINANCED_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_residual_amount(cle.dnz_chr_id,cle.id),0) LINE_RESIDUAL_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.contract_rent_amount(cle.dnz_chr_id,cle.id),0) LINE_RENT_AMOUNT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.line_unearned_income (cle.dnz_chr_id,cle.id),0) LINE_UNEARNED_INCOME,
NVL(OKL_SEEDED_FUNCTIONS_PVT.CONTRACT_PRINCIPAL_BALANCE(cle.dnz_chr_id, cle.id),0) LINE_PRINCIPAL_BALANCE, -- addl para
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd,
okl_strm_type_b sty
where txd.kle_id = kle.id
and txd.sty_id = sty.id
and sty.STREAM_TYPE_PURPOSE = 'RENT') LINE_BILLED_RENTS,
(select SUM(NVL(txd.amount,0))
from OKL_TXD_AR_LN_DTLS_B txd
where txd.kle_id = kle.id ) LINE_TOTAL_BILLED,
NVL(OKL_SEEDED_FUNCTIONS_PVT.Total_Asset_Financed_Fee_Amt(cle.dnz_chr_id, cle.id),0) TOTAL_ASSET_FINANCED_FEE_AMT,
NVL(OKL_SEEDED_FUNCTIONS_PVT.Total_Asset_Rollover_Fee_Amt(cle.dnz_chr_id, cle.id),0) TOTAL_ASSET_ROLLOVER_FEE_AMT,
--LINE_BILLED_NON_RENTS_FUNC,
--LINE_CAP_AMOUNT,
-- NET_INVESTMENT,
-- 17-Dec-08 Seema Chawla - bug 7635398 : changed Named parameter passing notation to Positional notation
/* NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(p_khr_id => cle.dnz_chr_id,
p_kle_id => cle.id, p_ref_type_code => 'ASSET'),null) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(p_khr_id =>cle.dnz_chr_id,
p_kle_id => cle.id, p_ref_type_code => 'EXPENSE'),null) EXPENSE_FUNDED_AMOUNT,
*/
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt( cle.dnz_chr_id,
cle.id, 'ASSET'),null) ASSETS_FUNDED_AMOUNT,
NVL(OKL_FUNDING_PVT.get_contract_line_funded_amt(cle.dnz_chr_id,
cle.id, 'EXPENSE'),null) EXPENSE_FUNDED_AMOUNT,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.total_asset_addon_cost (cle.dnz_chr_id,cle.id),0),null) TOTAL_ASSET_ADDON_COST,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_line_subsidy_amount (cle.dnz_chr_id,cle.id,NULL),0),null) TOTAL_ASSET_SUBSIDY_AMOUNT,
decode(cle.lse_id,33,
NVL(OKL_SEEDED_FUNCTIONS_PVT.get_line_subsidy_ovrd_amount (cle.dnz_chr_id,cle.id,NULL),0),null) TOTAL_ASSET_SUBSIDY_OVRD_AMT,
l_last_updated_by CREATED_BY,
-- -1 CREATED_BY,
sysdate CREATION_DATE,
l_last_updated_by LAST_UPDATED_BY,
---1 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,
-- -1 LAST_UPDATE_LOGIN,
-- -1 PROGRAM_APPLICATION_ID,
-- -1 PROGRAM_ID,
-- -1 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,
OKL_PRODUCT_PARAMETERS_V pdt,
OKL_SYS_ACCT_OPTS_ALL acct_sys_opts,
GL_LEDGERS ledger,
xle_entity_profiles legal_entity,
okc_k_lines_b cle,
okl_k_lines kle,
okc_k_lines_tl cle_tl,
okc_line_styles_b lse,
okc_line_styles_tl lse_tl,
OKC_STATUSES_TL cle_status_tl,
fnd_lookup_values_vl lookup_res_guarantor,
OKL_K_RATE_PARAMS krp,
OKL_INDICES indx,
-- cusotmer
HZ_PARTIES cust_party,
HZ_CUST_ACCOUNTS cust_accounts,
hz_party_sites cust_party_site,
(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,
-- sales tax
(select
cle_id,
dnz_chr_id,
ST_TRANSFER_OF_TITLE,
ST_SALE_AND_LEASE_BACK,
ST_PURCHASE_OF_LEASE,
ST_INTENDED_USE,
ST_EQUIPMENT_AGE,
ST_ASSET_UPFRONT_TAX,
st_row_num
from
(select
rgp.cle_id cle_id,
rgp.dnz_chr_id dnz_chr_id,
decode(RULE_INFORMATION6,'Y','Yes','No') ST_TRANSFER_OF_TITLE,
decode(RULE_INFORMATION7,'Y','Yes','No') ST_SALE_AND_LEASE_BACK,
decode(RULE_INFORMATION8,'Y','Yes','No') ST_PURCHASE_OF_LEASE,
zxf.classification_name ST_INTENDED_USE,
RULE_INFORMATION10 ST_EQUIPMENT_AGE,
lookups.meaning ST_ASSET_UPFRONT_TAX,
row_number() over ( partition by rgp.cle_id
order by rl.RULE_INFORMATION11, rl.rule_information9 nulls last ) st_row_num
from okc_rule_groups_b rgp,
okc_rules_b rl,
fnd_lookup_values_vl lookups,
zx_fc_intended_use_v zxf
where rgp.rgd_code = 'LAASTX'
and rl.rgp_id = rgp.id
and rl.RULE_INFORMATION_CATEGORY = 'LAASTX'
AND lookups.lookup_type (+) = 'OKL_ASSET_UPFRONT_TAX'
and lookups.lookup_code (+) = RULE_INFORMATION11
AND zxf.classification_code (+) = rule_information9 )
where st_row_num = 1 ) sales_tax,
-- property tax
(select
cle_id,
dnz_chr_id,
PROP_TAX_APPLICABLE,
PROP_TAX_LESSEE_REPORT,
PROP_TAX_BILL_METHOD,
prop_tax_row_num
FROM
(select
rgp.cle_id cle_id,
rgp.dnz_chr_id dnz_chr_id,
decode(RULE_INFORMATION1,'Y','Yes','No') PROP_TAX_APPLICABLE,
decode(RULE_INFORMATION2,'Y','Yes','No') PROP_TAX_LESSEE_REPORT,
lookups.meaning PROP_TAX_BILL_METHOD,
row_number() over ( partition by rgp.cle_id
order by rl.RULE_INFORMATION11, rl.rule_information3 nulls last ) prop_tax_row_num
from
okc_rule_groups_b rgp,
okc_rules_b rl,
fnd_lookup_values_vl lookups
where rgp.rgd_code = 'LAASTX'
and rl.rgp_id = rgp.id
and rl.RULE_INFORMATION_CATEGORY = 'LAPRTX'
AND lookups.lookup_type (+) = 'OKL_PROP_TAX_BILL_METHOD'
and lookups.lookup_code (+) = RULE_INFORMATION3)
where prop_tax_row_num = 1 ) property_tax,
-- asset_filing_lien
okc_rule_groups_b rgp_asset_filing_lien,
okc_rules_b rl_asset_filing_lien,
hz_parties hz_asset_filing_lien,
fnd_lookup_values_vl lookups_asset_filing_lien,
fnd_lookup_values_vl lookups_asset_filing_lien_sts,
-- ASSET_FILING_TITLE
okc_rule_groups_b rgp_ASSET_FILING_TITLE,
okc_rules_b rl_ASSET_FILING_TITLE,
fnd_lookup_values_vl lookups_ASSET_FILING_TITLE,
hz_parties hz_1_ASSET_FILING_TITLE,
hz_parties hz_2_ASSET_FILING_TITLE,
-- fa_line, contract active and non active, loan
-- fa_line, contract active, non-loan
-- fa_line, contract not active, non-loan
-- bank info, lse_id 33
okc_rules_b rul_bank_info,
okc_rule_groups_b rgp_bank_info,
okx_rcpt_method_accounts_v orma_bank_info,
-- supplier 34
(SELECT
cle_id,
dnz_chr_id,
SUPPLIER_INVOICE_NUMBER,
SUPPLIER_INV_LINE_ID,
SUPPLIER_INVOICE_DATE,
SUPPLIER_SHIP_TO_SITE_USE_ID,
INV_SUPPLIER_SHIP_TO_SITE_ID,
INV_SUPPLIER_SHIP_TO_SITE_NUM,
SUPPLIER_SHIP_TO_SITE,
SUPPLIER_VENDOR_NAME,
SUPPLIER_VENDOR_ID,
SUPPLIER_NUMBER,
inv_row_num
FROM
(SELECT
cle.cle_id cle_id,
cle.dnz_chr_id dnz_chr_id,
inv.invoice_number SUPPLIER_INVOICE_NUMBER,
inv.id SUPPLIER_INV_LINE_ID,
inv.date_invoiced SUPPLIER_INVOICE_DATE,
inv.shipping_address_id1 SUPPLIER_SHIP_TO_SITE_USE_ID,
HPS.PARTY_SITE_ID INV_SUPPLIER_SHIP_TO_SITE_ID,
HPS.PARTY_SITE_NUMBER INV_SUPPLIER_SHIP_TO_SITE_NUM,
HPS.PARTY_SITE_NUMBER, SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) SUPPLIER_SHIP_TO_SITE,
pov.vendor_name SUPPLIER_VENDOR_NAME,
cpl.object1_id1 SUPPLIER_VENDOR_ID,
pov.segment1 SUPPLIER_NUMBER,
row_number() over ( partition by inv.cle_id
order by inv.id desc, inv.invoice_number nulls last ) inv_row_num
FROM okl_supp_invoice_dtls inv,
okc_k_party_roles_b cpl,
po_vendors pov,
okc_k_lines_b cle,
hz_locations hl,
hz_party_sites hps,
hz_cust_acct_sites_all cas,
hz_cust_site_uses_all csu
WHERE inv.cle_id (+)= cpl.cle_id
AND cpl.rle_code = 'OKL_VENDOR'
AND cpl.chr_id is NULL
AND pov.vendor_id = cpl.object1_id1
AND cle.id = cpl.cle_id
AND cle.lse_id = 34
AND csu.site_use_id (+) = inv.shipping_address_id1
AND cas.cust_acct_site_id (+) = csu.cust_acct_site_id
AND hps.party_site_id (+) = cas.party_site_id
AND hl.location_id (+) = hps.location_id)
where inv_row_num = 1 ) supplier,
-- fee_line 52
(SELECT
CIM_FEE.CLE_ID CLE_ID,
CIM_FEE.DNZ_CHR_ID DNZ_CHR_ID,
STYT.NAME FEE_OR_SERVICE_NAME,
to_number(CPLB_FEE.OBJECT1_ID1) FEE_SUPPLIER_ID,
pov.vendor_name FEE_SUPPLIER,
pov.segment1 FEE_SUPPLIER_NUMBER,
TO_CHAR(RUL_LAFREQ.OBJECT1_ID1) FEE_OR_SERVICE_FREQUENCY_CODE,
TUOM.NAME FEE_OR_SERVICE_FREQUENCY,
RUL_LAFEXP.RULE_INFORMATION1 FEE_OR_SERV_NUMBER_OF_PERIODS,
RUL_LAFEXP.RULE_INFORMATION2 FEE_OR_SERV_AMOUNT_PER_PERIOD
FROM
OKC_K_ITEMS CIM_FEE,
OKL_STRM_TYPE_TL STYT,
OKC_K_PARTY_ROLES_B CPLB_FEE,
PO_VENDORS POV,
OKC_RULE_GROUPS_B RGP_LAFEXP,
OKC_RULES_B RUL_LAFREQ,
OKC_RULES_B RUL_LAFEXP,
OKL_TIME_UNITS_V TUOM
WHERE
CIM_FEE.JTOT_OBJECT1_CODE = 'OKL_STRMTYP'
AND STYT.ID = CIM_FEE.OBJECT1_ID1
AND STYT.LANGUAGE = USERENV('LANG')
AND CPLB_FEE.CLE_ID (+) = CIM_FEE.CLE_ID
AND CPLB_FEE.DNZ_CHR_ID (+) = CIM_FEE.DNZ_CHR_ID
AND cplb_fee.rle_code (+) = 'OKL_VENDOR'
AND POV.VENDOR_ID (+) = CPLB_FEE.OBJECT1_ID1
AND RGP_LAFEXP.DNZ_CHR_ID (+) = CIM_FEE.DNZ_CHR_ID
AND RGP_LAFEXP.CLE_ID (+) = CIM_FEE.CLE_ID
AND RGP_LAFEXP.RGD_CODE (+) = 'LAFEXP'
AND RUL_LAFREQ.RGP_ID (+) = RGP_LAFEXP.ID
AND RUL_LAFREQ.RULE_INFORMATION_CATEGORY (+) = 'LAFREQ'
AND RUL_LAFEXP.RGP_ID (+) = RGP_LAFEXP.ID
AND RUL_LAFEXP.RULE_INFORMATION_CATEGORY (+) = 'LAFEXP'
AND TUOM.ID1 (+) = RUL_LAFREQ.OBJECT1_ID1 ) fee_line,
fnd_lookup_values_vl LKP_FEE_TYPE,
-- item line_id=34
(SELECT
cleb_item.cle_id cle_id,
cleb_item.dnz_chr_id dnz_chr_id,
cleb_item.id MODEL_LINE_ID,
cleb_item.price_unit UNIT_COST,
to_number(item.object1_id1) INVENTORY_ITEM_ID,
to_number(item.object1_id2) INVENTORY_ITEM_ORG_ID,
item.number_of_items TOTAL_UNITS,
msi.segment1 INVENTORY_ITEM_NAME,
msi.description INVENTORY_ITEM_DESCRIPTION
FROM okc_k_items item,
okc_k_lines_b cleb_item,
MTL_SYSTEM_ITEMS_B msi
WHERE item.cle_id = cleb_item.id
AND cleb_item.lse_id = 34
AND msi.inventory_item_id = item.object1_id1
AND msi.organization_id = item.object1_id2) item_line,
-- instal line 43, 45 contract active
(SELECT
cle_id,
dnz_chr_id,
INSTALL_SITE_USE_ID,
INSTALL_SITE_NAME,
INSTALL_SITE_ID,
INSTALL_SITE_NUMBER,
SERIAL_NUMBER,
MULTIPLE,
instal_site_row_num
from
(select
cle_inst.cle_id,
cle_inst.dnz_chr_id,
to_number(iti.object_id1_new) INSTALL_SITE_USE_ID,
hps.party_site_id INSTALL_SITE_ID,
hps.party_site_number INSTALL_SITE_NUMBER,
substr(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,
hl.address3, hl.address4,hl.city,hl.county,hl.state,hl.province,
hl.postal_code,null,hl.country,null, null,null,null,null,null,
null,'n','n',80,1,1),1,80) INSTALL_SITE_NAME,
-- bug 7631324
--(case when NVL(cim_ib.NUMBER_OF_ITEMS,0) > 1 THEN lookup.MEANING
-- else csi.serial_number end) SERIAL_NUMBER ,
csi.serial_number SERIAL_NUMBER ,
lookup.MEANING MULTIPLE,
row_number() over ( partition by cle_inst.cle_id
order by iti.object_id1_new nulls last ) instal_site_row_num
from hz_locations hl,
hz_party_sites hps,
okl_txl_itm_insts iti,
csi_item_instances csi,
okc_k_items cim_ib,
okc_k_lines_b cle_ib,
okc_k_lines_b cle_inst,
fnd_lookup_values_vl lookup
where cle_inst.lse_id = 43
and cle_ib.cle_id = cle_inst.id
and cle_ib.dnz_chr_id = cle_inst.dnz_chr_id
AND iti.kle_id = cle_ib.id
and cle_ib.lse_id = 45
and cim_ib.cle_id = cle_ib.id
and cim_ib.dnz_chr_id = cle_ib.dnz_chr_id
and cim_ib.object1_id1 = csi.instance_id
and cim_ib.object1_id2 = '#'
and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
and csi.install_location_id = hps.party_site_id
and csi.install_location_type_code = 'HZ_PARTY_SITES'
and hps.location_id = hl.location_id
and lookup.lookup_type like 'OKL_ECC_VALUE_TYPE'
and lookup.lookup_code like 'MULTIPLE')
where instal_site_row_num = 1
) instal_line_active, -- contract active
-- instal_line 43,45 -- contract not active
(SELECT
cle_id,
dnz_chr_id,
INSTALL_SITE_USE_ID,
INSTALL_SITE_NAME,
INSTALL_SITE_ID,
INSTALL_SITE_NUMBER,
SERIAL_NUMBER,
MULTIPLE,
instal_site_row_num
from
(SELECT
cleb_inst.cle_id cle_id,
cleb_inst.dnz_chr_id dnz_chr_id,
iti.id,
to_number(iti.object_id1_new) INSTALL_SITE_USE_ID,
SUBSTR(arp_addr_label_pkg.format_address(null,hl.address1,hl.address2,hl.address3,
hl.address4,hl.city,hl.county,hl.state,hl.province,hl.postal_code,null,hl.country,
null, null,null,null,null,null,null,'n','n',80,1,1),1,80) INSTALL_SITE_NAME,
hps.party_site_id INSTALL_SITE_ID,
hps.party_site_number INSTALL_SITE_NUMBER,
iti.serial_number SERIAL_NUMBER,
lookup.MEANING MULTIPLE,
row_number() over ( partition by cleb_inst.cle_id
order by iti.object_id1_new nulls last ) instal_site_row_num
FROM okc_k_lines_b cleb_inst,
okc_k_lines_b cleb_ib,
okl_txl_itm_insts iti,
hz_locations hl,
hz_party_sites hps,
hz_party_site_uses hpu,
fnd_lookup_values_vl lookup
WHERE cleb_inst.lse_id = 43
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = 45
AND iti.kle_id = cleb_ib.id
AND hpu.party_site_use_id = iti.object_id1_new
AND hps.party_site_id = hpu.party_site_id
AND hl.location_id = hps.location_id
and lookup.lookup_type like 'OKL_ECC_VALUE_TYPE'
and lookup.lookup_code like 'MULTIPLE')
where instal_site_row_num = 1
) instal_line_not_active, -- contract not active
-- this is for inactive contract instal_line 43,45
( select
NVL(count(cleb_inst.cle_id),0) total,
cleb_inst.cle_id,
cleb_inst.dnz_chr_id
FROM okc_k_lines_b cleb_inst,
okc_k_lines_b cleb_ib,
okl_txl_itm_insts iti
WHERE cleb_inst.lse_id = 43
AND cleb_ib.cle_id = cleb_inst.id
AND cleb_ib.dnz_chr_id = cleb_inst.dnz_chr_id
AND cleb_ib.lse_id = 45
AND iti.kle_id = cleb_ib.id
group by cleb_inst.cle_id, cleb_inst.dnz_chr_id) instal_line_number_of_assets, -- this is for inactive contract
-- service line 48
(SELECT
cleb_svc.id cle_id,
msit.description FEE_OR_SERVICE_NAME,
cleb_svc.start_date FEE_OR_SERVICE_EFFECTIVE_FROM,
cleb_svc.end_date FEE_OR_SERVICE_EFFECTIVE_TO,
kle_svc.amount TOTAL_FEE_OR_SERVICE_AMOUNT,
rul_lafreq.object1_id1 FEE_OR_SERVICE_FREQUENCY_CODE,
tuom.name FEE_OR_SERVICE_FREQUENCY,
rul_lafexp.rule_information1 FEE_OR_SERV_NUMBER_OF_PERIODS,
rul_lafexp.rule_information2 FEE_OR_SERV_AMOUNT_PER_PERIOD
FROM
OKC_K_LINES_B CLEB_SVC,
OKL_K_LINES KLE_SVC,
OKC_K_ITEMS CIM_SVC,
MTL_SYSTEM_ITEMS_TL MSIT,
OKC_RULE_GROUPS_B RGP_LAFEXP,
OKC_RULES_B RUL_LAFREQ,
OKC_RULES_B RUL_LAFEXP,
OKL_TIME_UNITS_V TUOM,
OKC_LINE_STYLES_B LNSTYLE
WHERE
cleb_svc.id = kle_svc.id
AND cim_svc.cle_id = cleb_svc.id
AND cim_svc.dnz_chr_id = cleb_svc.dnz_chr_id
AND cim_svc.jtot_object1_code = 'OKX_SERVICE'
AND msit.inventory_item_id = cim_svc.object1_id1
AND msit.organization_id = cim_svc.object1_id2
AND msit.language = USERENV('LANG')
AND rgp_lafexp.dnz_chr_id (+) = cleb_svc.dnz_chr_id
AND rgp_lafexp.cle_id (+) = cleb_svc.id
AND rgp_lafexp.rgd_code (+) = 'LAFEXP'
AND rul_lafreq.rgp_id (+) = rgp_lafexp.id
AND rul_lafreq.rule_information_category (+) = 'LAFREQ'
AND rul_lafexp.rgp_id (+) = rgp_lafexp.id
AND rul_lafexp.rule_information_category (+) = 'LAFEXP'
AND tuom.id1 (+) = rul_lafreq.object1_id1
AND LNSTYLE.LTY_CODE='SOLD_SERVICE'
-- and cleb_svc.dnz_chr_id = 139068
) service_line, -- lse_id 48
-- passthrough_evergreen lse_id 33,48,52
okl_strm_type_b sty_pth_eveg,
OKL_STRM_TYPE_TL STYT_pth_eveg,
OKL_PARTY_PAYMENT_HDR PPH_pth_eveg,
fnd_lookup_values_vl LKP_PYT_BASIS_pth_eveg,
okl_formulae_v fmla_pth_eveg,
-- passthrough_base 52, 48
okl_strm_type_b sty_PTH_BASE,
OKL_STRM_TYPE_TL STYT_PTH_BASE,
OKL_PARTY_PAYMENT_HDR PPH_PTH_BASE,
fnd_lookup_values_vl LKP_PTH_BASE,
-- usage line 56
(SELECT
CLE.ID CLE_ID,
mtl.inventory_item_id INVENTORY_ITEM_ID,
mtl.segment1 INVENTORY_ITEM_NAME,
mtl_tl.DESCRIPTION INVENTORY_ITEM_DESCRIPTION,
OKX_PRICE.id1 PRICE_LIST_ID,
OKX_PRICE.NAME PRICE_LIST_NAME,
to_number(RUL.RULE_INFORMATION1) MINIMUM_QTY_USAGE,
to_number(RUL.RULE_INFORMATION2) DEFAULT_QTY_USAGE,
to_number(RUL.RULE_INFORMATION7) FIXED_USAGE_QUANTITY,
RUL.RULE_INFORMATION8 USAGE_PERIOD_CODE,
-- RUL.RULE_INFORMATION8 USAGE_PERIOD, --sechawla 8-jan-09 7628760
uom.UNIT_OF_MEASURE USAGE_PERIOD, --sechawla 8-jan-09 7628760
to_number(RUL.RULE_INFORMATION9) USAGE_NO_OF_PERIOD,
RUL.RULE_INFORMATION4 LEVEL_FLAG,
RUL.RULE_INFORMATION5 BASE_READING,
OBJECT3_ID1 BASE_READING_UOM_CODE,
RUL.RULE_INFORMATION3 AMCV_FLAG,
RUL.RULE_INFORMATION6 USAGE_TYPE_CODE,
RUL.DNZ_CHR_ID CHR_ID,
RG.CLE_ID usage_LINE_ID,
mtl.ORGANIZATION_ID INVENTORY_ITEM_ORG_ID,
lookup_read.meaning BASE_READING_UOM,
lookup_usage.meaning USAGE_TYPE
FROM
OKC_RULES_B RUL,
OKC_RULE_GROUPS_B RG,
mtl_system_items_b mtl,
mtl_system_items_tl mtl_tl,
OKX_LIST_HEADERS_V OKX_PRICE,
OKC_K_LINES_B CLE,
fnd_lookup_values_vl lookup_read,
fnd_lookup_values_vl lookup_usage,
OKX_UNITS_OF_MEASURE_V uom --sechawla 8-jan-09 7628760
WHERE
RULE_INFORMATION_CATEGORY = 'LAUSBB'
AND RUL.RGP_ID = RG.ID
AND RG.RGD_CODE = 'LAUSBB'
AND mtl.USAGE_ITEM_FLAG = 'Y'
AND mtl.inventory_item_id = RUL.OBJECT1_ID1
AND to_char(mtl.organization_id) = RUL.OBJECT1_ID2
AND OKX_PRICE.LIST_TYPE_CODE = 'PRL'
AND OKX_PRICE.ID1 = RUL.OBJECT2_ID1
AND OKX_PRICE.ID2 = RUL.OBJECT2_ID2
AND CLE.ID = RG.CLE_ID
AND CLE.DNZ_CHR_ID = RG.DNZ_CHR_ID
AND CLE.LSE_ID = 56
AND CLE.STS_CODE <> 'ABANDONED'
-- and CLE.DNZ_CHR_ID = 73036
and mtl.INVENTORY_ITEM_ID = mtl_tl.INVENTORY_ITEM_ID
and mtl.ORGANIZATION_ID = mtl_tl.ORGANIZATION_ID
and mtl_tl.LANGUAGE = userenv('LANG')
AND lookup_read.lookup_type (+) = 'UNIT'
AND lookup_read.LOOKUP_CODE (+) = OBJECT3_ID1
AND lookup_usage.lookup_type (+) = 'OKS_USAGE_TYPE'
AND lookup_usage.LOOKUP_CODE (+) = RUL.RULE_INFORMATION6
and uom.UOM_CODE(+) = RUL.RULE_INFORMATION8 --sechawla 8-jan-09 7628760
) usage_line,
OKL_LA_SERV_INTGR_UV service_k_line,
OKL_INS_POLICIES_UV insurance
-- corporate book -- contract is active
-- corporate book -- contract not active
-- tax book, contract active
-- tax book, contract not active
-- mg_book -- contract active
-- mg_book -- contract not active
WHERE
chr.id = khr.id
AND chr.SCS_CODE = 'LEASE'
AND opp.object_type LIKE 'CONTRACT_FIN_LINE_EXT-CONTRACT'
AND opp.object_value LIKE chr.contract_number
AND opp.assigned_process like P_ASSIGNED_PROCESS -- assigned_process is unique for each run
AND opp.khr_id = chr.id
AND opp.object_status IN ('ACTIVE','INACTIVE')
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 khr.pdt_id = pdt.id
AND pdt.aes_org_id = chr.authoring_org_id
AND lookup_book_class.lookup_type (+) = 'OKL_BOOK_CLASS'
AND lookup_book_class.lookup_code (+) = khr.DEAL_TYPE
AND acct_sys_opts.org_id = chr.authoring_org_id
AND ledger.ledger_id = acct_sys_opts.SET_OF_BOOKS_ID
AND legal_entity.legal_entity_id = khr.LEGAL_ENTITY_ID
-- customer
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 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_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 indx.ID(+) = krp.INTEREST_INDEX_ID
and krp.khr_id (+) = khr.id
and krp.parameter_type_code (+) = 'ACTUAL'
and krp.EFFECTIVE_TO_DATE (+) is null
AND cle.chr_id = chr.id
-- AND cle.LSE_ID in (33, 52,48, 56, 47)
AND cle.id = kle.id
AND cle_tl.id = cle.id
AND cle_tl.language = USERENV('LANG')
AND lse.id = cle.lse_id
AND lse.id = lse_tl.id
AND lse_tl.language = USERENV('LANG')
AND cle_status_tl.code = cle.sts_code
AND cle_status_tl.language = USERENV('LANG')
AND lookup_res_guarantor.lookup_type (+) = 'OKL_RESIDUAL_CODE'
AND lookup_res_guarantor.lookup_code (+) = kle.RESIDUAL_CODE
AND sales_tax.cle_id (+) = cle.id
AND property_tax.cle_id (+) = cle.id
-- asset_filing_lien
AND rgp_asset_filing_lien.cle_id (+) = cle.id
AND rgp_asset_filing_lien.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
and rgp_asset_filing_lien.rgd_code (+) = 'LAAFLG'
and rl_asset_filing_lien.rgp_id (+) = rgp_asset_filing_lien.id
and rl_asset_filing_lien.RULE_INFORMATION_CATEGORY (+) = 'LAFLLN'
and rl_asset_filing_lien.OBJECT1_ID1 = hz_asset_filing_lien.party_id (+)
AND lookups_asset_filing_lien.lookup_type (+) = 'OKL_FILING_TYPE'
--and lookups_asset_filing_lien.lookup_code (+) = rl_ASSET_FILING_TITLE.RULE_INFORMATION1
and lookups_asset_filing_lien.lookup_code (+) = rl_asset_filing_lien.RULE_INFORMATION1
AND lookups_asset_filing_lien_sts.lookup_type (+) = 'OKL_FILING_STATUS'
and lookups_asset_filing_lien_sts.lookup_code (+) = rl_asset_filing_lien.RULE_INFORMATION4
-- ASSET_FILING_TITLE
AND rgp_ASSET_FILING_TITLE.cle_id (+) = cle.id
AND rgp_ASSET_FILING_TITLE.rgd_code (+) = 'LAAFLG'
and rgp_ASSET_FILING_TITLE.dnz_chr_id (+) = cle.DNZ_CHR_ID
and rl_ASSET_FILING_TITLE.rgp_id (+) = rgp_ASSET_FILING_TITLE.id
and rl_ASSET_FILING_TITLE.RULE_INFORMATION_CATEGORY (+) = 'LAFLTL'
AND lookups_ASSET_FILING_TITLE.lookup_type (+) = 'OKL_FILING_TYPE'
and lookups_ASSET_FILING_TITLE.lookup_code (+) = rl_ASSET_FILING_TITLE.RULE_INFORMATION1
AND hz_1_ASSET_FILING_TITLE.party_id (+) = rl_ASSET_FILING_TITLE.object1_id1
AND hz_2_ASSET_FILING_TITLE.party_id (+) = rl_ASSET_FILING_TITLE.object2_id1
--AND fa_line_loan.CLE_ID (+) = cle.id
--AND fa_line_loan.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
--AND fa_line_non_loan.CLE_ID (+) = cle.id
--AND fa_line_non_loan.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
-- AND bank_info cle.lse_id = 33
AND rgp_bank_info.cle_id (+) = cle.id
AND rgp_bank_info.rgd_code (+) = 'LABILL'
AND rgp_bank_info.id = rul_bank_info.rgp_id (+)
AND rgp_bank_info.dnz_chr_id = rul_bank_info.dnz_chr_id (+)
AND rul_bank_info.rule_information_category (+)= 'LABACC'
AND orma_bank_info.id1 (+) = rul_bank_info.object1_id1
AND supplier.cle_id (+) = cle.id
AND supplier.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND fee_line.CLE_ID (+) = cle.id
AND fee_line.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND LKP_FEE_TYPE.LOOKUP_CODE (+) = nvl(KLE.FEE_TYPE, 'XXX')
AND LKP_FEE_TYPE.LOOKUP_TYPE (+) = 'OKL_FEE_TYPES'
AND item_line.cle_id (+) = cle.id
AND item_line.DNZ_CHR_ID (+) = cle.DNZ_CHR_ID
AND instal_line_active.cle_id(+) = cle.id
AND instal_line_active.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
-- begin only for non active contracts
AND instal_line_not_active.cle_id(+) = cle.id
AND instal_line_not_active.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
AND instal_line_number_of_assets.cle_id(+) = cle.id
AND instal_line_number_of_assets.DNZ_CHR_ID(+) = cle.DNZ_CHR_ID
-- end only for non active contracts
AND service_line.cle_id(+) = cle.id
-- AND passthrough_evergreen cle.lse_id in (33,52,48)
AND PPH_pth_eveg.CLE_ID (+) = cle.id
AND STYT_pth_eveg.ID (+) = PPH_pth_eveg.PASSTHRU_STREAM_TYPE_ID
AND STYT_pth_eveg.LANGUAGE(+) = USERENV('LANG')
and sty_pth_eveg.id (+) = styt_pth_eveg.id
AND fmla_pth_eveg.name (+) = PPH_pth_eveg.PAYOUT_BASIS_FORMULA
AND LKP_PYT_BASIS_pth_eveg.LOOKUP_CODE (+) = PPH_pth_eveg.PAYOUT_BASIS
AND LKP_PYT_BASIS_pth_eveg.LOOKUP_TYPE (+) = 'OKL_PAYOUT_BASIS'
and PPH_pth_eveg.PASSTHRU_TERM (+) = 'EVERGREEN'
-- passthrough_base 48, 52
AND cle.id = PPH_PTH_BASE.CLE_ID (+)
AND STYT_PTH_BASE.ID (+) = PPH_PTH_BASE.PASSTHRU_STREAM_TYPE_ID
AND STYT_PTH_BASE.LANGUAGE (+) = USERENV('LANG')
AND LKP_PTH_BASE.LOOKUP_CODE (+) = PPH_PTH_BASE.PAYOUT_BASIS
AND LKP_PTH_BASE.LOOKUP_TYPE (+) = 'OKL_PAYOUT_BASIS'
and sty_PTH_BASE.id (+) = styt_PTH_BASE.id
and PPH_PTH_BASE.PASSTHRU_TERM (+)= 'BASE'
AND usage_line.cle_id(+) = cle.id
AND service_k_line.cle_id (+) = cle.id
AND insurance.kle_id (+) = cle.id
--AND corp_book.ASSET_NUMBER (+) = cle_TL.NAME
--AND tax_book.cle_id (+) = cle_tl.id
--AND tax_book.ASSET_NUMBER (+) = cle_TL.NAME
--AND mg_book.cle_id (+) = cle_tl.id
--AND mg_book.ASSET_NUMBER (+) = cle_TL.NAME
-- parameters section begin
--don't need the following as these paramater matching has been already
--been done in Master program - OKL_K_LINE_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 cle.sts_code like nvl(P_CONTRACT_LINE_STATUS, cle.sts_code)
AND CHR.CONTRACT_NUMBER like NVL(P_CONTRACT_NUMBER, CHR.CONTRACT_NUMBER)
*/
-- OKL_LINE_STYLES
AND lse.LTY_CODE like NVL(P_CONTRACT_LINE_TYPE, lse.LTY_CODE)
--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)
-- 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)
-- 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(fee_line.FEE_SUPPLIER_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(fee_line.FEE_SUPPLIER_ID,-9999))
OR nvl(supplier.SUPPLIER_VENDOR_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(supplier.SUPPLIER_VENDOR_ID,-9999))
OR nvl(service_k_line.SUPPLIER_ID,-9999) = nvl(P_VENDOR_NUMBER,nvl(service_k_line.SUPPLIER_ID,-9999))
)
-- P_VENDOR_NAME -> value set OKL_VENDORS -> returns PO_VENDORS.VENDOR_ID
AND ( nvl(fee_line.FEE_SUPPLIER_ID,-9999) = nvl(P_VENDOR_NAME,nvl(fee_line.FEE_SUPPLIER_ID,-9999))
OR nvl(supplier.SUPPLIER_VENDOR_ID,-9999) = nvl(P_VENDOR_NAME,nvl(supplier.SUPPLIER_VENDOR_ID,-9999))
OR nvl(service_k_line.SUPPLIER_ID,-9999) = nvl(P_VENDOR_NAME,nvl(service_k_line.SUPPLIER_ID,-9999))
)
-- AND NVL(tax_book.FA_TAX_BOOK_TYPE_CODE, 'XXX') LIKE NVL(P_TAX_BOOK, NVL(tax_book.FA_TAX_BOOK_TYPE_CODE, 'XXX'))
);
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)
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);
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_LINE_FIN_EXTRACT_T
WHERE REQUEST_ID = l_parent_request_id;
END delete_report_data;