[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_release_asset_flag VARCHAR2(1); -- only updated at load_input_record()
,DELETED_YN OKL_K_HEADERS_FULL_V.DELETED_YN%TYPE
,CUST_PO_NUMBER_REQ_YN OKL_K_HEADERS_FULL_V.CUST_PO_NUMBER_REQ_YN%TYPE
,PRE_PAY_REQ_YN OKL_K_HEADERS_FULL_V.PRE_PAY_REQ_YN%TYPE
,CUST_PO_NUMBER OKL_K_HEADERS_FULL_V.CUST_PO_NUMBER%TYPE
,SHORT_DESCRIPTION OKL_K_HEADERS_FULL_V.SHORT_DESCRIPTION%TYPE
,COMMENTS OKL_K_HEADERS_FULL_V.COMMENTS%TYPE
,DESCRIPTION OKL_K_HEADERS_FULL_V.DESCRIPTION%TYPE
,DPAS_RATING OKL_K_HEADERS_FULL_V.DPAS_RATING%TYPE
,COGNOMEN OKL_K_HEADERS_FULL_V.COGNOMEN%TYPE
,TEMPLATE_YN OKL_K_HEADERS_FULL_V.TEMPLATE_YN%TYPE
,tEMPLATE_USED OKL_K_HEADERS_FULL_V.TEMPLATE_USED%TYPE
,DATE_APPROVED OKL_K_HEADERS_FULL_V.DATE_APPROVED%TYPE
,DATETIME_CANCELLED OKL_K_HEADERS_FULL_V.DATETIME_CANCELLED%TYPE
,AUTO_RENEW_DAYS OKL_K_HEADERS_FULL_V.AUTO_RENEW_DAYS%TYPE
,DATE_ISSUED OKL_K_HEADERS_FULL_V.DATE_ISSUED%TYPE
,DATETIME_RESPONDED OKL_K_HEADERS_FULL_V.DATETIME_RESPONDED%TYPE
,NON_RESPONSE_REASON OKL_K_HEADERS_FULL_V.NON_RESPONSE_REASON%TYPE
,NON_RESPONSE_EXPLAIN OKL_K_HEADERS_FULL_V.NON_RESPONSE_EXPLAIN%TYPE
,RFP_TYPE OKL_K_HEADERS_FULL_V.RFP_TYPE%TYPE
,CHR_TYPE OKL_K_HEADERS_FULL_V.CHR_TYPE%TYPE
,KEEP_ON_MAIL_LIST OKL_K_HEADERS_FULL_V.KEEP_ON_MAIL_LIST%TYPE
,SET_ASIDE_REASON OKL_K_HEADERS_FULL_V.SET_ASIDE_REASON%TYPE
,SET_ASIDE_PERCENT OKL_K_HEADERS_FULL_V.SET_ASIDE_PERCENT%TYPE
,RESPONSE_COPIES_REQ OKL_K_HEADERS_FULL_V.RESPONSE_COPIES_REQ%TYPE
,DATE_CLOSE_PROJECTED OKL_K_HEADERS_FULL_V.DATE_CLOSE_PROJECTED%TYPE
,DATETIME_PROPOSED OKL_K_HEADERS_FULL_V.DATETIME_PROPOSED%TYPE
,DATE_SIGNED OKL_K_HEADERS_FULL_V.DATE_SIGNED%TYPE
,DATE_TERMINATED OKL_K_HEADERS_FULL_V.DATE_TERMINATED%TYPE
,DATE_RENEWED OKL_K_HEADERS_FULL_V.DATE_RENEWED%TYPE
,TRN_CODE OKL_K_HEADERS_FULL_V.TRN_CODE%TYPE
,START_DATE OKL_K_HEADERS_FULL_V.START_DATE%TYPE
,END_DATE OKL_K_HEADERS_FULL_V.END_DATE%TYPE
,AUTHORING_ORG_ID OKL_K_HEADERS_FULL_V.AUTHORING_ORG_ID%TYPE
,BUY_OR_SELL OKL_K_HEADERS_FULL_V.BUY_OR_SELL%TYPE
,ISSUE_OR_RECEIVE OKL_K_HEADERS_FULL_V.ISSUE_OR_RECEIVE%TYPE
,ESTIMATED_AMOUNT OKL_K_HEADERS_FULL_V.ESTIMATED_AMOUNT%TYPE
,CHR_ID_RENEWED_TO OKL_K_HEADERS_FULL_V.CHR_ID_RENEWED_TO%TYPE
,ESTIMATED_AMOUNT_RENEWED OKL_K_HEADERS_FULL_V.ESTIMATED_AMOUNT_RENEWED%TYPE
,CURRENCY_CODE_RENEWED OKL_K_HEADERS_FULL_V.CURRENCY_CODE_RENEWED%TYPE
,USER_ACCESS_LEVEL OKL_K_HEADERS_FULL_V.USER_ACCESS_LEVEL%TYPE
,UPG_ORIG_SYSTEM_REF OKL_K_HEADERS_FULL_V.UPG_ORIG_SYSTEM_REF%TYPE
,UPG_ORIG_SYSTEM_REF_ID OKL_K_HEADERS_FULL_V.UPG_ORIG_SYSTEM_REF_ID%TYPE
,APPLICATION_ID OKL_K_HEADERS_FULL_V.APPLICATION_ID%TYPE
,RESOLVED_UNTIL OKL_K_HEADERS_FULL_V.RESOLVED_UNTIL%TYPE
,ATTRIBUTE_CATEGORY OKL_K_HEADERS_FULL_V.ATTRIBUTE_CATEGORY%TYPE
,ATTRIBUTE1 OKL_K_HEADERS_FULL_V.ATTRIBUTE1%TYPE
,ATTRIBUTE2 OKL_K_HEADERS_FULL_V.ATTRIBUTE2%TYPE
,ATTRIBUTE3 OKL_K_HEADERS_FULL_V.ATTRIBUTE3%TYPE
,ATTRIBUTE4 OKL_K_HEADERS_FULL_V.ATTRIBUTE4%TYPE
,ATTRIBUTE5 OKL_K_HEADERS_FULL_V.ATTRIBUTE5%TYPE
,ATTRIBUTE6 OKL_K_HEADERS_FULL_V.ATTRIBUTE6%TYPE
,ATTRIBUTE7 OKL_K_HEADERS_FULL_V.ATTRIBUTE7%TYPE
,ATTRIBUTE8 OKL_K_HEADERS_FULL_V.ATTRIBUTE8%TYPE
,ATTRIBUTE9 OKL_K_HEADERS_FULL_V.ATTRIBUTE9%TYPE
,ATTRIBUTE10 OKL_K_HEADERS_FULL_V.ATTRIBUTE10%TYPE
,ATTRIBUTE11 OKL_K_HEADERS_FULL_V.ATTRIBUTE11%TYPE
,ATTRIBUTE12 OKL_K_HEADERS_FULL_V.ATTRIBUTE12%TYPE
,ATTRIBUTE13 OKL_K_HEADERS_FULL_V.ATTRIBUTE13%TYPE
,ATTRIBUTE14 OKL_K_HEADERS_FULL_V.ATTRIBUTE14%TYPE
,ATTRIBUTE15 OKL_K_HEADERS_FULL_V.ATTRIBUTE15%TYPE
,CREATED_BY OKL_K_HEADERS_FULL_V.CREATED_BY%TYPE
,CREATION_DATE OKL_K_HEADERS_FULL_V.CREATION_DATE%TYPE
,LAST_UPDATED_BY OKL_K_HEADERS_FULL_V.LAST_UPDATED_BY%TYPE
,LAST_UPDATE_DATE OKL_K_HEADERS_FULL_V.LAST_UPDATE_DATE%TYPE
,LAST_UPDATE_LOGIN OKL_K_HEADERS_FULL_V.LAST_UPDATE_LOGIN%TYPE
,ORIG_SYSTEM_SOURCE_CODE OKL_K_HEADERS_FULL_V.ORIG_SYSTEM_SOURCE_CODE%TYPE
,ORIG_SYSTEM_ID1 OKL_K_HEADERS_FULL_V.ORIG_SYSTEM_ID1%TYPE
,ORIG_SYSTEM_REFERENCE1 OKL_K_HEADERS_FULL_V.ORIG_SYSTEM_REFERENCE1%TYPE
,KHR_ROW_ID OKL_K_HEADERS_FULL_V.KHR_ROW_ID%TYPE
,KHR_OBJECT_VERSION_NUMBER OKL_K_HEADERS_FULL_V.KHR_OBJECT_VERSION_NUMBER%TYPE
,ISG_ID OKL_K_HEADERS_FULL_V.ISG_ID%TYPE
,KHR_ID OKL_K_HEADERS_FULL_V.KHR_ID%TYPE
,PDT_ID OKL_K_HEADERS_FULL_V.PDT_ID%TYPE
,AMD_CODE OKL_K_HEADERS_FULL_V.AMD_CODE%TYPE
,DATE_FIRST_ACTIVITY OKL_K_HEADERS_FULL_V.DATE_FIRST_ACTIVITY%TYPE
,GENERATE_ACCRUAL_YN OKL_K_HEADERS_FULL_V.GENERATE_ACCRUAL_YN%TYPE
,GENERATE_ACCRUAL_OVERRIDE_YN OKL_K_HEADERS_FULL_V.GENERATE_ACCRUAL_OVERRIDE_YN%TYPE
,DATE_REFINANCED OKL_K_HEADERS_FULL_V.DATE_REFINANCED%TYPE
,CREDIT_ACT_YN OKL_K_HEADERS_FULL_V.CREDIT_ACT_YN%TYPE
,TERM_DURATION OKL_K_HEADERS_FULL_V.TERM_DURATION%TYPE
,CONVERTED_ACCOUNT_YN OKL_K_HEADERS_FULL_V.CONVERTED_ACCOUNT_YN%TYPE
,DATE_CONVERSION_EFFECTIVE OKL_K_HEADERS_FULL_V.DATE_CONVERSION_EFFECTIVE%TYPE
,SYNDICATABLE_YN OKL_K_HEADERS_FULL_V.SYNDICATABLE_YN%TYPE
,SALESTYPE_YN OKL_K_HEADERS_FULL_V.SALESTYPE_YN%TYPE
,DATE_DEAL_TRANSFERRED OKL_K_HEADERS_FULL_V.DATE_DEAL_TRANSFERRED%TYPE
,DATETIME_PROPOSAL_EFFECTIVE OKL_K_HEADERS_FULL_V.DATETIME_PROPOSAL_EFFECTIVE%TYPE
,DATETIME_PROPOSAL_INEFFECTIVE OKL_K_HEADERS_FULL_V.DATETIME_PROPOSAL_INEFFECTIVE%TYPE
,DATE_PROPOSAL_ACCEPTED OKL_K_HEADERS_FULL_V.DATE_PROPOSAL_ACCEPTED%TYPE
,KHR_ATTRIBUTE_CATEGORY OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE_CATEGORY%TYPE
,KHR_ATTRIBUTE1 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE1%TYPE
,KHR_ATTRIBUTE2 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE2%TYPE
,KHR_ATTRIBUTE3 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE3%TYPE
,KHR_ATTRIBUTE4 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE4%TYPE
,KHR_ATTRIBUTE5 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE5%TYPE
,KHR_ATTRIBUTE6 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE6%TYPE
,KHR_ATTRIBUTE7 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE7%TYPE
,KHR_ATTRIBUTE8 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE8%TYPE
,KHR_ATTRIBUTE9 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE9%TYPE
,KHR_ATTRIBUTE10 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE10%TYPE
,KHR_ATTRIBUTE11 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE11%TYPE
,KHR_ATTRIBUTE12 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE12%TYPE
,KHR_ATTRIBUTE13 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE13%TYPE
,KHR_ATTRIBUTE14 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE14%TYPE
,KHR_ATTRIBUTE15 OKL_K_HEADERS_FULL_V.KHR_ATTRIBUTE15%TYPE
,KHR_CREATED_BY OKL_K_HEADERS_FULL_V.KHR_CREATED_BY%TYPE
,KHR_CREATON_DATE OKL_K_HEADERS_FULL_V.KHR_CREATON_DATE%TYPE
,KHR_LAST_UPDATED_BY OKL_K_HEADERS_FULL_V.KHR_LAST_UPDATED_BY%TYPE
,KHR_LAST_UPDATE_DATE OKL_K_HEADERS_FULL_V.KHR_LAST_UPDATE_DATE%TYPE
,KHR_LAST_UPDATE_LOGIN OKL_K_HEADERS_FULL_V.KHR_LAST_UPDATE_LOGIN%TYPE
,PRE_TAX_YIELD OKL_K_HEADERS_FULL_V.PRE_TAX_YIELD%TYPE
,AFTER_TAX_YIELD OKL_K_HEADERS_FULL_V.AFTER_TAX_YIELD%TYPE
,IMPLICIT_INTEREST_RATE OKL_K_HEADERS_FULL_V.IMPLICIT_INTEREST_RATE%TYPE
,IMPLICIT_NON_IDC_INTEREST_RATE OKL_K_HEADERS_FULL_V.IMPLICIT_NON_IDC_INTEREST_RATE%TYPE
,TARGET_PRE_TAX_YIELD OKL_K_HEADERS_FULL_V.TARGET_PRE_TAX_YIELD%TYPE
,TARGET_AFTER_TAX_YIELD OKL_K_HEADERS_FULL_V.TARGET_AFTER_TAX_YIELD%TYPE
,TARGET_IMPLICIT_INTEREST_RATE OKL_K_HEADERS_FULL_V.TARGET_IMPLICIT_INTEREST_RATE%TYPE
,TARGET_IMPLICIT_NONIDC_INTRATE OKL_K_HEADERS_FULL_V.TARGET_IMPLICIT_NONIDC_INTRATE%TYPE
,DATE_LAST_INTERIM_INTEREST_CAL OKL_K_HEADERS_FULL_V.DATE_LAST_INTERIM_INTEREST_CAL%TYPE
,DEAL_TYPE OKL_K_HEADERS_FULL_V.DEAL_TYPE%TYPE
,PRE_TAX_IRR OKL_K_HEADERS_FULL_V.PRE_TAX_IRR%TYPE
,AFTER_TAX_IRR OKL_K_HEADERS_FULL_V.AFTER_TAX_IRR%TYPE
,EXPECTED_DELIVERY_DATE OKL_K_HEADERS_FULL_V.EXPECTED_DELIVERY_DATE%TYPE
,ACCEPTED_DATE OKL_K_HEADERS_FULL_V.ACCEPTED_DATE%TYPE
,PREFUNDING_ELIGIBLE_YN OKL_K_HEADERS_FULL_V.PREFUNDING_ELIGIBLE_YN%TYPE
,REVOLVING_CREDIT_YN OKL_K_HEADERS_FULL_V.REVOLVING_CREDIT_YN%TYPE
,CURRENCY_CONVERSION_TYPE OKL_K_HEADERS_FULL_V.CURRENCY_CONVERSION_TYPE%TYPE
,CURRENCY_CONVERSION_RATE OKL_K_HEADERS_FULL_V.CURRENCY_CONVERSION_RATE%TYPE
,CURRENCY_CONVERSION_DATE OKL_K_HEADERS_FULL_V.CURRENCY_CONVERSION_DATE%TYPE
,ASSIGNABLE_YN OKL_K_HEADERS_FULL_V.ASSIGNABLE_YN%TYPE
,CUST_ACCT_ID OKL_K_HEADERS_FULL_V.CUST_ACCT_ID%TYPE
,BILL_TO_SITE_USE_ID OKL_K_HEADERS_FULL_V.BILL_TO_SITE_USE_ID%TYPE);
,DELETE_BEFORE_IMPORT_YN OKL_HEADER_INTERFACE.DELETE_BEFORE_IMPORT_YN%TYPE /* akp_delete */
,ST_UPDATE_LINES_FROM_CONTRACT OKL_HEADER_INTERFACE.ST_UPDATE_LINES_FROM_CONTRACT%TYPE
,ST_INTEREST_DISCLOSED OKL_HEADER_INTERFACE.ST_INTEREST_DISCLOSED%TYPE
,ST_TRANSFER_OF_TITLE OKL_HEADER_INTERFACE.ST_TRANSFER_OF_TITLE%TYPE
,ST_SALE_AND_LEASE_BACK OKL_HEADER_INTERFACE.ST_SALE_AND_LEASE_BACK%TYPE
,ST_PURCHASE_OF_LEASE OKL_HEADER_INTERFACE.ST_PURCHASE_OF_LEASE%TYPE
,ST_EQUIPMENT_USAGE OKL_HEADER_INTERFACE.ST_EQUIPMENT_USAGE%TYPE
,ST_EQUIPMENT_AGE OKL_HEADER_INTERFACE.ST_EQUIPMENT_AGE%TYPE
,ST_ASSET_UPFRONT_TAX OKL_HEADER_INTERFACE.ST_ASSET_UPFRONT_TAX%TYPE
,ST_BILL_STREAM_TYPE_CODE OKL_HEADER_INTERFACE.ST_BILL_STREAM_TYPE_CODE%TYPE
,ST_BILL_STREAM_PURPOSE_CODE OKL_HEADER_INTERFACE.ST_BILL_STREAM_PURPOSE_CODE%TYPE
,ST_FIN_STREAM_TYPE_CODE OKL_HEADER_INTERFACE.ST_FIN_STREAM_TYPE_CODE%TYPE
,ST_FIN_STREAM_PURPOSE_CODE OKL_HEADER_INTERFACE.ST_FIN_STREAM_PURPOSE_CODE%TYPE
,ST_CAP_STREAM_TYPE_CODE OKL_HEADER_INTERFACE.ST_CAP_STREAM_TYPE_CODE%TYPE
,ST_CAP_STREAM_PURPOSE_CODE OKL_HEADER_INTERFACE.ST_CAP_STREAM_PURPOSE_CODE%TYPE
-- AKP: Vartiable Rate
,INT_RATE_EFFECTIVE_FROM_DATE OKL_HEADER_INTERFACE.INT_RATE_EFFECTIVE_FROM_DATE%TYPE
,INT_RATE_EFFECTIVE_TO_DATE OKL_HEADER_INTERFACE.INT_RATE_EFFECTIVE_TO_DATE%TYPE
,INT_RATE_PRINC_BASIS_CODE OKL_HEADER_INTERFACE.INT_RATE_PRINC_BASIS_CODE%TYPE
,INT_RATE_BASIS_CODE OKL_HEADER_INTERFACE.INT_RATE_BASIS_CODE%TYPE
,INT_RATE_DELAY_CODE OKL_HEADER_INTERFACE.INT_RATE_DELAY_CODE%TYPE
,INT_RATE_DELAY_FREQ OKL_HEADER_INTERFACE.INT_RATE_DELAY_FREQ%TYPE
,INT_RATE_CMPND_FREQ_CODE OKL_HEADER_INTERFACE.INT_RATE_CMPND_FREQ_CODE%TYPE
,INT_RATE_CATCHUP_BASIS_CODE OKL_HEADER_INTERFACE.INT_RATE_CATCHUP_BASIS_CODE%TYPE
,INT_RATE_CATCHUP_START_DATE OKL_HEADER_INTERFACE.INT_RATE_CATCHUP_START_DATE%TYPE
,INT_RATE_CAT_STLMNT_CODE OKL_HEADER_INTERFACE.INT_RATE_CAT_STLMNT_CODE%TYPE
,INT_RATE_CHANGE_START_DATE OKL_HEADER_INTERFACE.INT_RATE_CHANGE_START_DATE%TYPE
,INT_RATE_CHANGE_FREQ_CODE OKL_HEADER_INTERFACE.INT_RATE_CHANGE_FREQ_CODE%TYPE
,INT_RATE_CHANGE_VALUE OKL_HEADER_INTERFACE.INT_RATE_CHANGE_VALUE%TYPE
,INT_RATE_CON_OPTION_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_OPTION_CODE%TYPE
,INT_RATE_NEXT_CON_DATE OKL_HEADER_INTERFACE.INT_RATE_NEXT_CON_DATE%TYPE
--,INT_RATE_EFFECTIVE_FROM_DATE_CON OKL_HEADER_INTERFACE.INT_RATE_EFFECTIVE_FROM_DATE_CON%TYPE
--,INT_RATE_EFFECTIVE_TO_DATE_CON OKL_HEADER_INTERFACE.INT_RATE_EFFECTIVE_TO_DATE_CON%TYPE
,INT_RATE_CON_INDEX_NAME OKL_HEADER_INTERFACE.INT_RATE_CON_INDEX_NAME%TYPE
,INT_RATE_CON_BASE_RATE OKL_HEADER_INTERFACE.INT_RATE_CON_BASE_RATE%TYPE
,INT_RATE_CON_START_DATE OKL_HEADER_INTERFACE.INT_RATE_CON_START_DATE%TYPE
,INT_RATE_CON_ADDER_RATE OKL_HEADER_INTERFACE.INT_RATE_CON_ADDER_RATE%TYPE
,INT_RATE_CON_MAXIMUM_RATE OKL_HEADER_INTERFACE.INT_RATE_CON_MAXIMUM_RATE%TYPE
,INT_RATE_CON_MINIMUM_RATE OKL_HEADER_INTERFACE.INT_RATE_CON_MINIMUM_RATE%TYPE
,INT_RATE_CON_PRC_BAS_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_PRC_BAS_CODE%TYPE
,INT_RATE_CON_DAY_MTH_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_DAY_MTH_CODE%TYPE
,INT_RATE_CON_DAY_YER_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_DAY_YER_CODE%TYPE
,INT_RATE_CON_INT_BAS_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_INT_BAS_CODE%TYPE
,INT_RATE_CON_RATE_DELAY_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_RATE_DELAY_CODE%TYPE
,INT_RATE_CON_RATE_DELAY_FREQ OKL_HEADER_INTERFACE.INT_RATE_CON_RATE_DELAY_FREQ%TYPE
,INT_RATE_CON_CMPND_FREQ_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_CMPND_FREQ_CODE%TYPE
,INT_RATE_CON_FORMULA_NAME OKL_HEADER_INTERFACE.INT_RATE_CON_FORMULA_NAME%TYPE
,INT_RATE_CON_CAT_BAS_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_CAT_BAS_CODE%TYPE
,INT_RATE_CON_CAT_START_DATE OKL_HEADER_INTERFACE.INT_RATE_CON_CAT_START_DATE%TYPE
,INT_RATE_CON_CAT_STLMNT_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_CAT_STLMNT_CODE%TYPE
,INT_RATE_CON_CHG_START_DATE OKL_HEADER_INTERFACE.INT_RATE_CON_CHG_START_DATE%TYPE
,INT_RATE_CON_CHG_FREQ_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_CHG_FREQ_CODE%TYPE
,INT_RATE_CON_CHG_VALUE OKL_HEADER_INTERFACE.INT_RATE_CON_CHG_VALUE%TYPE
,INT_RATE_CAT_FREQ_CODE OKL_HEADER_INTERFACE.INT_RATE_CAT_FREQ_CODE%TYPE
,INT_RATE_CON_CAT_FREQ_CODE OKL_HEADER_INTERFACE.INT_RATE_CON_CAT_FREQ_CODE%TYPE
-- Bug 4655611
,RVI_STREAM_CODE OKL_HEADER_INTERFACE.RVI_STREAM_CODE%TYPE
,RVI_STREAM_PURPOSE_CODE OKL_HEADER_INTERFACE.RVI_STREAM_PURPOSE_CODE%TYPE
-- Bug 4655611
-- Contract Additional field, Bug 4558486
,ATTRIBUTE_CATEGORY OKL_HEADER_INTERFACE.ATTRIBUTE_CATEGORY%TYPE
,ATTRIBUTE1 OKL_HEADER_INTERFACE.ATTRIBUTE1%TYPE
,ATTRIBUTE2 OKL_HEADER_INTERFACE.ATTRIBUTE2%TYPE
,ATTRIBUTE3 OKL_HEADER_INTERFACE.ATTRIBUTE3%TYPE
,ATTRIBUTE4 OKL_HEADER_INTERFACE.ATTRIBUTE4%TYPE
,ATTRIBUTE5 OKL_HEADER_INTERFACE.ATTRIBUTE5%TYPE
,ATTRIBUTE6 OKL_HEADER_INTERFACE.ATTRIBUTE6%TYPE
,ATTRIBUTE7 OKL_HEADER_INTERFACE.ATTRIBUTE7%TYPE
,ATTRIBUTE8 OKL_HEADER_INTERFACE.ATTRIBUTE8%TYPE
,ATTRIBUTE9 OKL_HEADER_INTERFACE.ATTRIBUTE9%TYPE
,ATTRIBUTE10 OKL_HEADER_INTERFACE.ATTRIBUTE10%TYPE
,ATTRIBUTE11 OKL_HEADER_INTERFACE.ATTRIBUTE11%TYPE
,ATTRIBUTE12 OKL_HEADER_INTERFACE.ATTRIBUTE12%TYPE
,ATTRIBUTE13 OKL_HEADER_INTERFACE.ATTRIBUTE13%TYPE
,ATTRIBUTE14 OKL_HEADER_INTERFACE.ATTRIBUTE14%TYPE
,ATTRIBUTE15 OKL_HEADER_INTERFACE.ATTRIBUTE15%TYPE
-- Contract Additional field, Bug 4558486
-- $1 Buyout
,EOT_AUTO_PROC_PURCHASE_OPT OKL_HEADER_INTERFACE.EOT_AUTO_PROC_PURCHASE_OPT%TYPE
-- $1 Buyout
,PTH_EVG_PAYOUT_BASIS OKL_HEADER_INTERFACE.PTH_EVG_PAYOUT_BASIS%TYPE
,PTH_PAYOUT_BASIS_FORMULA OKL_HEADER_INTERFACE.PTH_PAYOUT_BASIS_FORMULA%TYPE
,PTH_EVG_STREAM_CODE OKL_HEADER_INTERFACE.PTH_EVG_STREAM_CODE%TYPE
,PTH_EVG_STREAM_PURPOSE_CODE OKL_HEADER_INTERFACE.PTH_EVG_STREAM_PURPOSE_CODE%TYPE
,SEND_BILL_SALE OKL_HEADER_INTERFACE.SEND_BILL_SALE%TYPE
,MANUAL_QUOTES_ONLY OKL_HEADER_INTERFACE.MANUAL_QUOTES_ONLY%TYPE
,FIRST_TERMINATION_DATE OKL_HEADER_INTERFACE.FIRST_TERMINATION_DATE%TYPE
,QUOTE_RCPT_ADDL_RCPT_ROLE OKL_HEADER_INTERFACE.QUOTE_RCPT_ADDL_RCPT_ROLE%TYPE
,QUOTE_RCPT_ALLOC_PTG OKL_HEADER_INTERFACE.QUOTE_RCPT_ALLOC_PTG%TYPE
,QUOTE_APPRVR_ADV_NOTICE_ROLE OKL_HEADER_INTERFACE.QUOTE_APPRVR_ADV_NOTICE_ROLE%TYPE
,QUOTE_APPROVER_DELAY_DAYS OKL_HEADER_INTERFACE.QUOTE_APPROVER_DELAY_DAYS%TYPE
,ERT_SERV_MANT_FRML OKL_HEADER_INTERFACE.ERT_SERV_MANT_FRML%TYPE
,EOT_SERV_MANT_FRML OKL_HEADER_INTERFACE.EOT_SERV_MANT_FRML%TYPE
--Added by dpsingh for LE Uptake
,LEGAL_ENTITY_ID NUMBER := Okl_Api.G_MISS_NUM
-- Added by Punith for R12 ebtax
,ST_TAX_SCHEDULE_APPLIES_FLAG OKL_HEADER_INTERFACE.ST_TAX_SCHEDULE_APPLIES_FLAG%TYPE
);
SELECT bill_to_site_use_id,
cust_acct_id
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT TO_CHAR(p_from_date ,fnd_date.canonical_mask)
INTO l_canonical_date
FROM dual;
PROCEDURE Update_Interface_Status (p_contract_number IN okl_header_interface.contract_number_old%TYPE,
p_new_contract_number IN okl_header_interface.contract_number%TYPE,
p_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2 ) IS
x_proc_name VARCHAR2(35) := 'UPDATE_INTERFACE_STATUS';
update_failed EXCEPTION;
UPDATE okl_header_interface
SET status = p_status,
contract_number = p_new_contract_number,
scheduled_worker_id = decode(p_status, 'ERROR', null, scheduled_worker_id),
date_created_in_okl = decode(p_status,'INTERFACED',SYSDATE,
'FINAL', SYSDATE, NULL) -- update date in case of Successful loading
WHERE contract_number_old = p_contract_number;
RAISE update_failed;
WHEN update_failed THEN
okl_api.set_message(
G_APP_NAME,
G_UNEXPECTED_ERROR,
'OKL_SQLCODE',
SQLCODE,
'OKL_SQLERRM',
SQLERRM || ': '||G_PKG_NAME||'.'||x_proc_name
);
END Update_Interface_Status;
SELECT
ID
,OBJECT_VERSION_NUMBER
,SFWT_FLAG
,CHR_ID_RESPONSE
,CHR_ID_AWARD
,CHR_ID_RENEWED
,INV_ORGANIZATION_ID
,STS_CODE
,QCL_ID
,SCS_CODE
,CONTRACT_NUMBER
,CURRENCY_CODE
,CONTRACT_NUMBER_MODIFIER
,ARCHIVED_YN
,DELETED_YN
,CUST_PO_NUMBER_REQ_YN
,PRE_PAY_REQ_YN
,CUST_PO_NUMBER
,SHORT_DESCRIPTION
,COMMENTS
,DESCRIPTION
,DPAS_RATING
,COGNOMEN
,TEMPLATE_YN
,TEMPLATE_USED
,DATE_APPROVED
,DATETIME_CANCELLED
,AUTO_RENEW_DAYS
,DATE_ISSUED
,DATETIME_RESPONDED
,NON_RESPONSE_REASON
,NON_RESPONSE_EXPLAIN
,RFP_TYPE
,CHR_TYPE
,KEEP_ON_MAIL_LIST
,SET_ASIDE_REASON
,SET_ASIDE_PERCENT
,RESPONSE_COPIES_REQ
,DATE_CLOSE_PROJECTED
,DATETIME_PROPOSED
,DATE_SIGNED
,DATE_TERMINATED
,DATE_RENEWED
,TRN_CODE
,START_DATE
,END_DATE
,AUTHORING_ORG_ID
,BUY_OR_SELL
,ISSUE_OR_RECEIVE
,ESTIMATED_AMOUNT
,CHR_ID_RENEWED_TO
,ESTIMATED_AMOUNT_RENEWED
,CURRENCY_CODE_RENEWED
,USER_ACCESS_LEVEL
,UPG_ORIG_SYSTEM_REF
,UPG_ORIG_SYSTEM_REF_ID
,APPLICATION_ID
,RESOLVED_UNTIL
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,ORIG_SYSTEM_SOURCE_CODE
,ORIG_SYSTEM_ID1
,ORIG_SYSTEM_REFERENCE1
,KHR_ROW_ID
,KHR_OBJECT_VERSION_NUMBER
,ISG_ID
,KHR_ID
,PDT_ID
,AMD_CODE
,DATE_FIRST_ACTIVITY
,GENERATE_ACCRUAL_YN
,GENERATE_ACCRUAL_OVERRIDE_YN
,DATE_REFINANCED
,CREDIT_ACT_YN
,TERM_DURATION
,CONVERTED_ACCOUNT_YN
,DATE_CONVERSION_EFFECTIVE
,SYNDICATABLE_YN
,SALESTYPE_YN
,DATE_DEAL_TRANSFERRED
,DATETIME_PROPOSAL_EFFECTIVE
,DATETIME_PROPOSAL_INEFFECTIVE
,DATE_PROPOSAL_ACCEPTED
,KHR_ATTRIBUTE_CATEGORY
,KHR_ATTRIBUTE1
,KHR_ATTRIBUTE2
,KHR_ATTRIBUTE3
,KHR_ATTRIBUTE4
,KHR_ATTRIBUTE5
,KHR_ATTRIBUTE6
,KHR_ATTRIBUTE7
,KHR_ATTRIBUTE8
,KHR_ATTRIBUTE9
,KHR_ATTRIBUTE10
,KHR_ATTRIBUTE11
,KHR_ATTRIBUTE12
,KHR_ATTRIBUTE13
,KHR_ATTRIBUTE14
,KHR_ATTRIBUTE15
,KHR_CREATED_BY
,KHR_CREATON_DATE
,KHR_LAST_UPDATED_BY
,KHR_LAST_UPDATE_DATE
,KHR_LAST_UPDATE_LOGIN
,PRE_TAX_YIELD
,AFTER_TAX_YIELD
,IMPLICIT_INTEREST_RATE
,IMPLICIT_NON_IDC_INTEREST_RATE
,TARGET_PRE_TAX_YIELD
,TARGET_AFTER_TAX_YIELD
,TARGET_IMPLICIT_INTEREST_RATE
,TARGET_IMPLICIT_NONIDC_INTRATE
,DATE_LAST_INTERIM_INTEREST_CAL
,DEAL_TYPE
,PRE_TAX_IRR
,AFTER_TAX_IRR
,EXPECTED_DELIVERY_DATE
,ACCEPTED_DATE
,PREFUNDING_ELIGIBLE_YN
,REVOLVING_CREDIT_YN
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,ASSIGNABLE_YN
,CUST_ACCT_ID
,BILL_TO_SITE_USE_ID
FROM okl_k_headers_full_v
WHERE contract_number = p_template
AND template_yn = 'Y';
SELECT
rule.ID,
rule.OBJECT_VERSION_NUMBER,
rule.SFWT_FLAG,
rule.OBJECT1_ID1,
rule.OBJECT2_ID1,
rule.OBJECT3_ID1,
rule.OBJECT1_ID2,
rule.OBJECT2_ID2,
rule.OBJECT3_ID2,
rule.JTOT_OBJECT1_CODE,
rule.JTOT_OBJECT2_CODE,
rule.JTOT_OBJECT3_CODE,
rule.DNZ_CHR_ID,
rule.RGP_ID,
rule.PRIORITY,
rule.STD_TEMPLATE_YN,
rule.COMMENTS,
rule.WARN_YN,
rule.ATTRIBUTE_CATEGORY,
rule.ATTRIBUTE1,
rule.ATTRIBUTE2,
rule.ATTRIBUTE3,
rule.ATTRIBUTE4,
rule.ATTRIBUTE5,
rule.ATTRIBUTE6,
rule.ATTRIBUTE7,
rule.ATTRIBUTE8,
rule.ATTRIBUTE9,
rule.ATTRIBUTE10,
rule.ATTRIBUTE11,
rule.ATTRIBUTE12,
rule.ATTRIBUTE13,
rule.ATTRIBUTE14,
rule.ATTRIBUTE15,
rule.CREATED_BY,
rule.CREATION_DATE,
rule.LAST_UPDATED_BY,
rule.LAST_UPDATE_DATE,
rule.LAST_UPDATE_LOGIN,
--rule.TEXT,
rule.RULE_INFORMATION_CATEGORY,
rule.RULE_INFORMATION1,
rule.RULE_INFORMATION2,
rule.RULE_INFORMATION3,
rule.RULE_INFORMATION4,
rule.RULE_INFORMATION5,
rule.RULE_INFORMATION6,
rule.RULE_INFORMATION7,
rule.RULE_INFORMATION8,
rule.RULE_INFORMATION9,
rule.RULE_INFORMATION10,
rule.RULE_INFORMATION11,
rule.RULE_INFORMATION12,
rule.RULE_INFORMATION13,
rule.RULE_INFORMATION14,
rule.RULE_INFORMATION15,
rule.TEMPLATE_YN,
rule.ans_set_jtot_object_code,
rule.ans_set_jtot_object_id1,
rule.ans_set_jtot_object_id2,
rule.DISPLAY_SEQUENCE
FROM okc_rule_groups_v rg,
okc_rules_v rule
WHERE rg.id = rule.rgp_id
AND rg.dnz_chr_id = p_chr_id
AND ( ( p_cle_id IS NULL
AND
rg.cle_id IS NULL
)
OR
rg.cle_id = p_cle_id
)
AND rg.rgd_code = p_rgd_code
AND rule.rule_information_category = p_rule_code;
x_rulv_tbl(i).last_updated_by := rulv_rec.last_updated_by;
x_rulv_tbl(i).last_update_date := rulv_rec.last_update_date;
x_rulv_tbl(i).last_update_login := rulv_rec.last_update_login;
SELECT ID1, NAME
FROM okl_strmtyp_source_v
WHERE status = 'A'
AND name = p_strm_code
AND stream_type_purpose = p_strm_purpose;
SELECT currency_code
FROM FND_CURRENCIES
WHERE currency_code = p_curr_code
AND NVL(end_date_active,SYSDATE) >= SYSDATE;
SELECT asset_id
FROM OKL_LA_RELEASE_ASSETS_UV
WHERE asset_number = p_asset_number
AND organization_id = p_org_id;
SELECT 'Y'
FROM OKL_LINES_INTERFACE OLI
WHERE oli.contract_number_old = p_contract_number
AND EXISTS ( SELECT fa.asset_id
FROM FA_ADDITIONS_B FA,
OKL_ASSET_RETURNS_B OAR,
OKC_K_ITEMS CIM_FA,
OKC_K_LINES_B CLEB_FA,
OKC_K_LINES_B CLEB_TL,
OKC_K_HEADERS_B CHRB
WHERE cleb_tl.chr_id = chrb.id
AND chrb.inv_organization_id = p_org_id
AND cleb_tl.lse_id = G_FIN_LINE_LTY_ID
AND oar.kle_id = cleb_tl.id
AND oar.ars_code = 'RE_LEASE'
AND cleb_fa.cle_id = cleb_tl.id
AND cleb_fa.lse_id = G_FA_LINE_LTY_ID
AND cleb_fa.id = cim_fa.cle_id
AND cleb_fa.dnz_chr_id =cim_fa.dnz_chr_id
AND cim_fa.object1_id1 = fa.asset_id
AND cim_fa.object1_id2 ='#'
AND fa.asset_number = oli.asset_number
AND oli.asset_number = p_asset_number -- Bug #15992711: Add extra condition to fetch the record pertaining to asset number
AND not exists (SELECT '1'
FROM okc_k_headers_b chrb_2,
okc_statuses_b chr_sts,
okc_k_lines_b cleb_2,
okc_statuses_b cle_sts,
okc_k_items cim_2
WHERE chrb_2.id = cleb_2.dnz_chr_id
AND chrb_2.scs_code in ('LEASE','LOAN')
AND chrb_2.sts_code = chr_sts.code
AND chr_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND cleb_2.id = cim_2.cle_id
AND cleb_2.dnz_chr_id = cim_2.dnz_chr_id
AND cleb_2.sts_code = cle_sts.code
AND cle_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND cim_2.object1_id1 = to_char(fa.asset_id)
AND cim_2.object1_id2 = '#' and cim_2.jtot_object1_code = 'OKX_ASSET'
)
) ;
SELECT 'Y'
FROM OKL_LINES_INTERFACE OLI
WHERE oli.contract_number_old = p_contract_number
AND NOT EXISTS (SELECT fa.asset_id
FROM FA_ADDITIONS_B FA,
OKL_ASSET_RETURNS_B OAR,
OKC_K_ITEMS CIM_FA,
OKC_K_LINES_B CLEB_FA,
OKC_K_LINES_B CLEB_TL,
OKC_K_HEADERS_B CHRB
WHERE cleb_tl.chr_id = chrb.id
AND chrb.inv_organization_id = p_org_id
AND cleb_tl.lse_id = G_FIN_LINE_LTY_ID
AND oar.kle_id = cleb_tl.id
AND oar.ars_code = 'RE_LEASE'
AND cleb_fa.cle_id = cleb_tl.id
AND cleb_fa.lse_id = G_FA_LINE_LTY_ID
AND cleb_fa.id = cim_fa.cle_id
AND cleb_fa.dnz_chr_id =cim_fa.dnz_chr_id
AND cim_fa.object1_id1 = fa.asset_id
AND cim_fa.object1_id2 ='#'
AND fa.asset_number = oli.asset_number
) ;
SELECT 'Y'
FROM OKL_LINES_INTERFACE OLI
WHERE oli.contract_number_old = p_contract_number
AND oli.line_type = 'ASSET'
AND oli.dt_effective_balance_legacy IS NOT NULL
AND EXISTS ( SELECT 'Y'
FROM OKL_LINES_INTERFACE OLI1
WHERE oli1.contract_number_old = p_contract_number
AND oli1.line_type = 'ASSET'
AND (oli1.dt_effective_balance_legacy IS NULL OR
oli1.dt_effective_balance_legacy <> oli.dt_effective_balance_legacy)
) ;
SELECT service_vendor_id,
service_vendor_name
FROM okl_party_roles_interface
WHERE contract_number_old = p_contract_number
AND line_number = p_line_number;
SELECT 'X'
FROM FND_LOOKUPS
WHERE lookup_code = p_lookup_code
AND lookup_type = p_lookup_type;
SELECT id1
FROM okx_parties_v
WHERE NVL(TRUNC(end_date_active), TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND ((id1 = p_customer_id
AND p_customer_id IS NOT NULL)
OR
(party_number = p_customer_number
AND
p_customer_number IS NOT NULL));
SELECT contract_number
FROM okc_k_headers_v
WHERE contract_number = p_contract_number;
SELECT id1,
uom_code
FROM okx_tuom_v
WHERE uom_class = 'OKL-TIME'
AND base_uom_flag = 'N'
AND name = p_payment_freq_code;
SELECT id1,
id1
FROM okl_time_units_v
WHERE name = p_payment_freq_code;
SELECT 'X'
FROM okl_lines_interface
WHERE contract_number_old = p_contract_number
AND line_number = p_line_number;
SELECT 'Y'
FROM okx_units_of_measure_v okx,
okc_time_code_units_v okc
WHERE okx.uom_code = okc.uom_code
AND okc.active_flag = 'Y'
AND okc.tce_code NOT IN ('HOUR','MINUTE','SECOND')
AND okx.uom_code = p_freq_code;
SELECT *
FROM okl_header_interface
WHERE (
batch_number = p_batch_number
OR
contract_number_old = p_contract_number
OR
(
customer_number = p_customer_number
AND
start_date between p_start_date_from AND p_start_date_to
)
)
AND nvl(scheduled_worker_id,'NONE') = g_instance_number
AND status in ('NEW','ERROR'); --Process only NEW and previuosly FAILED records
SELECT *
FROM okl_lines_interface
WHERE contract_number_old = p_contract_number_old
ORDER BY line_number;
SELECT *
FROM okl_party_roles_interface
WHERE contract_number_old = p_contract_number;
SELECT *
FROM okl_terms_interface
WHERE contract_number_old = p_contract_number;
SELECT *
FROM okl_payments_interface
WHERE contract_number_old = p_contract_number;
Update_Interface_Status(p_contract_number => okl_header_rec.contract_number_old,
p_new_contract_number => okl_header_rec.contract_number,
p_status => 'ELIGIBLE',
x_return_status => x_return_status
);
Update_Interface_Status(p_contract_number => okl_header_rec.contract_number_old,
p_new_contract_number => okl_header_rec.contract_number,
p_status => 'ERROR',
x_return_status => x_return_status
);
SELECT a.id,
b.revenue_recognition_method,
b.interest_calculation_basis,
b.deal_type
FROM okl_products_v a,
okl_product_parameters_v b
WHERE ((a.name = p_product_name
AND
p_product_name IS NOT NULL
)
OR
(a.id = p_product_id
AND
p_product_id IS NOT NULL
)
)
AND TRUNC(SYSDATE) BETWEEN NVL(a.from_date,TRUNC(SYSDATE)) AND NVL(a.to_date,TRUNC(SYSDATE))
AND A.id = B.id;
SELECT 'Y'
FROM okl_products prod,
okl_prod_qlty_val_uv pqv
WHERE ((prod.name = p_product_name
AND
p_product_name IS NOT NULL
)
OR
(prod.id = p_product_id
AND
p_product_id IS NOT NULL
)
)
AND TRUNC(SYSDATE) BETWEEN NVL(prod.from_date,TRUNC(SYSDATE)) AND NVL(prod.to_date,TRUNC(SYSDATE))
AND prod.id = pqv.pdt_id
AND prod.reporting_pdt_id IS NULL
AND pqv.quality_name IN ('LEASE','INVESTOR')
AND pqv.quality_val = 'LOAN';
SELECT id
FROM okc_k_headers_all_b
WHERE ((contract_number = p_agreement_number
AND
p_agreement_number IS NOT NULL
)
OR
(id = p_agreement_id
AND
p_agreement_id IS NOT NULL
)
)
AND scs_code = p_scs_code
AND sts_code = 'ACTIVE'
AND authoring_org_id = p_auth_org_id;
SELECT id
FROM okc_k_headers_all_b
WHERE ((contract_number = p_agreement_number
AND
p_agreement_number IS NOT NULL
)
OR
(id = p_agreement_id
AND
p_agreement_id IS NOT NULL
)
)
AND authoring_org_id = p_org_id
AND scs_code = p_scs_code
AND sts_code <> 'TERMINATED';
SELECT id1
FROM okx_vendors_v
WHERE name = p_vendor_name;
SELECT id1
FROM okx_parties_v
WHERE ((party_number = p_party_number
AND
p_party_number IS NOT NULL)
OR
(id1 = p_party_id
AND
p_party_id IS NOT NULL
)
);
SELECT prt.PARTY_ID,
prt.PARTY_NAME,
prt.PARTY_NUMBER,
hps.PARTY_SITE_ID,
hps.PARTY_SITE_NAME,
hps.PARTY_SITE_NUMBER
FROM
HZ_PARTIES PRT,
HZ_PARTY_SITES HPS
WHERE
prt.PARTY_ID = hps.PARTY_ID
--start modified by abhaxen for 6689015 on 24-Jan-2008
-- Added new filters to remove full table scans
AND ( prt.CATEGORY_CODE = p_category_code AND p_category_code IS NOT NULL )
AND ( ( prt.PARTY_ID = p_party_id AND p_party_id IS NOT NULL )
OR ( prt.PARTY_NAME = p_party_name AND p_party_name IS NOT NULL)
OR ( prt.PARTY_NUMBER = p_party_number AND p_party_number IS NOT NULL ))
AND ( ( hps.PARTY_SITE_ID = p_party_site_id AND p_party_site_id IS NOT NULL )
OR( hps.PARTY_SITE_NUMBER = p_party_site_number AND p_party_site_number IS NOT NULL )
OR( hps.PARTY_SITE_NAME = p_party_site_name AND p_party_site_name IS NOT NULL ))
;
SELECT id1
FROM okx_parties_v
WHERE ((party_number = p_customer_number
AND
p_customer_number IS NOT NULL
)
OR
(id1 = p_customer_id
AND
p_customer_id IS NOT NULL
)
);
SELECT site_use.id1
FROM okx_cust_site_uses_v site_use,
hz_cust_acct_sites_all site
WHERE site.cust_acct_site_id = site_use.cust_acct_site_id
AND ((site_use.name = p_bill_to_address
AND
p_bill_to_address IS NOT NULL)
OR
(site_use.id1 = p_bill_to_address_id
AND
p_bill_to_address_id IS NOT NULL
)
)
AND site_use.org_id = p_org_id
AND site.org_id = p_org_id
AND site_use.site_use_code = 'BILL_TO'
AND site_use.cust_account_id = p_customer_account_id
AND site_use.b_status = 'A'
AND site.status = 'A';
SELECT id1
FROM okx_rcpt_method_accounts_v
WHERE (
(bank_account_num = p_bank_acc_number)
OR
(id1 = p_bank_acc_id)
)
AND customer_id = p_customer_acc_id
-- udhenuko Bug#5925603 : Start
--AND customer_site_use_id =p_cust_site_id
AND ((customer_site_use_id = p_cust_site_id)
or ( customer_site_use_id IS NULL) )
--Bug#5925603: End
-- rbanerje Bug#5925603(Release bug 7355967) : Start
-- AND org_id = p_org_id
-- rbanerje Bug#5925603(Release bug 7355967) : End
AND TRUNC(SYSDATE) between NVL(START_DATE_ACTIVE, TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE))
-- udhenuko Bug#5925603 : added order clause so that one used at site level will take precedence
ORDER BY CUSTOMER_SITE_USE_ID;
SELECT id
FROM okl_indices
WHERE name = p_index_name;
SELECT id1
FROM okx_payables_terms_v
WHERE (name = p_payment_term
AND
p_payment_term IS NOT NULL)
OR (id1 = p_payment_term_id
AND
p_payment_term_id IS NOT NULL)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active),TRUNC(SYSDATE))
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
SELECT id1
FROM okx_vendor_sites_v
WHERE (name = p_name
AND
p_name IS NOT NULL)
OR
(id1 = p_id
AND
p_id IS NOT NULL)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE))
AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
SELECT id,
name
FROM okl_invoice_formats_v
WHERE ((name = p_invoice_format_code
AND
p_invoice_format_code IS NOT NULL)
OR
(id = p_invoice_format_id
AND
p_invoice_format_id IS NOT NULL
)
)
AND TRUNC(SYSDATE) between NVL(START_DATE, TRUNC(SYSDATE)) AND NVL(END_DATE,TRUNC(SYSDATE));
SELECT id1
FROM okx_receipt_methods_v
WHERE ((name = p_payment_method
AND
p_payment_method IS NOT NULL)
OR
(id1 = p_payment_method_id
AND
p_payment_method_id IS NOT NULL))
AND customer_id = p_customer_acc_id
AND (site_use_id = p_bill_to_address_id
OR
site_use_id IS NULL)
AND TRUNC(SYSDATE) between NVL(START_DATE_ACTIVE, TRUNC(SYSDATE)) AND NVL(END_DATE_ACTIVE,TRUNC(SYSDATE));
SELECT id1
FROM okx_customer_accounts_v
WHERE ((description = p_cust_acc_number
AND
p_cust_acc_number IS NOT NULL)
OR
(id1 = p_customer_account_id
AND
p_customer_account_id IS NOT NULL
)
)
AND party_id = p_cust_id
AND status = 'A';
SELECT id1
FROM okx_customer_accounts_v
WHERE ((description = p_cust_acc_number
AND
p_cust_acc_number IS NOT NULL)
OR
(id1 = p_customer_account_id
AND
p_customer_account_id IS NOT NULL
)
)
AND status = 'A';
SELECT id1
FROM okx_tuom_v
WHERE uom_code = p_base_uom_code;
SELECT id1
FROM okx_list_headers_v
WHERE list_type_code = 'PRL'
AND ((name = p_price_list_name
AND
p_price_list_name IS NOT NULL
)
OR
(id1 = p_price_list_id
AND
p_price_list_id IS NOT NULL
)
)
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE) AND NVL(end_date_active, SYSDATE);
SELECT id1,
id2
FROM okx_system_items_v
WHERE usage_item_flag = 'Y'
AND organization_id = p_org_id
AND ((name = p_item_name
AND
p_item_name IS NOT NULL
)
OR
(id1 = p_item_id
AND
p_item_id IS NOT NULL
)
);
PROCEDURE create_update_asset_rules(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_chr_id IN okc_k_headers_v.id%TYPE,
p_contract_number IN okl_header_interface.contract_number_old%TYPE,
p_cle_id IN okl_k_lines_v.id%TYPE,
p_line_number IN okc_k_lines_v.line_number%TYPE,
p_line_type IN okl_lines_interface.line_type%TYPE,
p_inv_org_id IN okl_lines_interface.inventory_organization_id%TYPE,
p_sales_tax_exempt_flag IN okl_lines_interface.sales_tax_exempt_flag%TYPE,
p_sales_tax_exempt_cert_number IN okl_lines_interface.sales_tax_exempt_cert_number%TYPE,
p_sales_tax_override_flag IN okl_lines_interface.sales_tax_override_flag%TYPE,
p_sales_tax_override_rate IN okl_lines_interface.sales_tax_override_rate%TYPE,
p_st_upd_lines_from_contract IN okl_lines_interface.st_update_lines_from_contract%TYPE,
p_st_transfer_of_title IN okl_lines_interface.st_transfer_of_title%TYPE,
p_st_sale_and_lease_back IN okl_lines_interface.st_sale_and_lease_back%TYPE,
p_st_purchase_of_lease IN okl_lines_interface.st_purchase_of_lease%TYPE,
p_st_equipment_usage IN okl_lines_interface.st_equipment_usage%TYPE,
p_st_equipment_age IN okl_lines_interface.st_equipment_age%TYPE,
p_st_asset_upfront_tax IN okl_lines_interface.st_asset_upfront_tax%TYPE,
p_vat_tax_exempt IN okl_lines_interface.vat_tax_exempt%TYPE,
p_vat_tax_exempt_cert_number IN okl_lines_interface.vat_tax_exempt_cert_number%TYPE,
p_vat_tax_override_flag IN okl_lines_interface.vat_tax_override_flag%TYPE,
p_vat_tax_override_rate IN okl_lines_interface.vat_tax_override_rate%TYPE,
p_prop_tax_applicable IN okl_lines_interface.prop_tax_applicable%TYPE,
p_prop_tax_lease_rep IN okl_lines_interface.prop_tax_lease_rep%TYPE,
p_french_tax_code IN okl_lines_interface.french_tax_code%TYPE,
p_french_tax_name IN okl_lines_interface.french_tax_name%TYPE,
p_prop_tax_bill_method IN okl_lines_interface.prop_tax_bill_method%TYPE,
p_prop_tax_as_contract_yn IN okl_lines_interface.prop_tax_as_contract_yn%TYPE ) IS
line_rule_failed EXCEPTION;
l_proc_name VARCHAR2(35) := 'CREATE_UPDATE_ASSET_RULES';
SELECT code
FROM okl_french_tax_v
WHERE ( code = p_tax_code
AND
p_tax_code IS NOT NULL)
OR ( name = p_tax_name
AND
p_tax_name IS NOT NULL)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date), TRUNC(SYSDATE))
AND NVL(TRUNC(end_date), TRUNC(SYSDATE));
SELECT rgp.id
FROM okc_rule_groups_v rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LAASTX'
AND rgp.rgp_type = 'KRG';
SELECT rule.id
FROM okc_rules_v rule
WHERE rule.dnz_chr_id = p_chr_id
AND rule.rgp_id = p_rgp_id
AND rule.rule_information_category = 'LAASTX';
SELECT rule.id
FROM okc_rules_v rule
WHERE rule.dnz_chr_id = p_chr_id
AND rule.rgp_id = p_rgp_id
AND rule.rule_information_category = 'LAPRTX';
OKL_RULE_PUB.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => l_rulv_rec,
x_rulv_rec => x_rulv_rec
);
OKL_RULE_PUB.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => lp_rulv_rec,
x_rulv_rec => xp_rulv_rec
);
END create_update_asset_rules;
p_st_upd_lines_from_contract IN okl_lines_interface.st_update_lines_from_contract%TYPE,
p_st_transfer_of_title IN okl_lines_interface.st_transfer_of_title%TYPE,
p_st_sale_and_lease_back IN okl_lines_interface.st_sale_and_lease_back%TYPE,
p_st_purchase_of_lease IN okl_lines_interface.st_purchase_of_lease%TYPE,
p_st_equipment_usage IN okl_lines_interface.st_equipment_usage%TYPE,
p_st_equipment_age IN okl_lines_interface.st_equipment_age%TYPE,
p_st_asset_upfront_tax IN okl_lines_interface.st_asset_upfront_tax%TYPE,
p_vat_tax_exempt IN okl_lines_interface.vat_tax_exempt%TYPE,
p_vat_tax_exempt_cert_number IN okl_lines_interface.vat_tax_exempt_cert_number%TYPE,
p_vat_tax_override_flag IN okl_lines_interface.vat_tax_override_flag%TYPE,
p_vat_tax_override_rate IN okl_lines_interface.vat_tax_override_rate%TYPE,
p_lien_type IN okl_lines_interface.lien_type%TYPE,
p_lien_filing_number IN okl_lines_interface.lien_filing_number%TYPE,
p_lien_filing_date IN okl_lines_interface.lien_filing_date%TYPE,
p_lien_filing_status IN okl_lines_interface.lien_filing_status%TYPE,
p_lienholder_id IN okl_lines_interface.lienholder_id%TYPE,
p_lienholder_number IN okl_lines_interface.lienholder_number%TYPE,
p_filing_jurisdiction IN okl_lines_interface.filing_jurisdiction%TYPE,
p_filing_sub_jurisdiction IN okl_lines_interface.filing_sub_jurisdiction%TYPE,
p_lien_expiration_date IN okl_lines_interface.lien_expiration_date%TYPE,
p_lien_continuation_number IN okl_lines_interface.lien_continuation_number%TYPE,
p_lien_continuation_date IN okl_lines_interface.lien_continuation_date%TYPE,
p_title_type IN okl_lines_interface.title_type%TYPE,
p_title_issuer_number IN okl_lines_interface.title_issuer_number%TYPE,
p_title_issuer_id IN okl_lines_interface.title_issuer_id%TYPE,
p_title_date IN okl_lines_interface.title_date%TYPE,
p_title_number IN okl_lines_interface.title_number%TYPE,
p_registration_number IN okl_lines_interface.registration_number%TYPE,
p_asset_registration_loc_name IN okl_lines_interface.asset_registration_loc_name%TYPE,
p_title_custodian_name IN okl_lines_interface.title_custodian_name%TYPE,
p_payee_site_name IN okl_lines_interface.payee_site_name%TYPE,
p_passthrough_percent IN okl_lines_interface.passthrough_percent%TYPE,
p_passthrough_basis_code IN okl_lines_interface.passthrough_basis_code%TYPE,
p_fee_code IN okl_lines_interface.fee_code%TYPE,
p_stream_purpose IN okl_lines_interface.stream_purpose_code%TYPE,
p_usage_item_name IN okl_lines_interface.usage_item_name%TYPE,
p_usage_item_id IN okl_lines_interface.usage_item_id%TYPE,
p_price_list_name IN okl_lines_interface.price_list_name%TYPE,
p_price_list_id IN okl_lines_interface.price_list_id%TYPE,
p_minimum_qty_usage IN okl_lines_interface.minimum_qty_usage%TYPE,
p_default_qty_usage IN okl_lines_interface.default_qty_usage%TYPE,
p_amcv_flag IN okl_lines_interface.amcv_flag%TYPE,
p_level_flag IN okl_lines_interface.level_flag%TYPE,
p_base_reading IN okl_lines_interface.base_reading%TYPE,
p_base_reading_uom_code IN okl_lines_interface.base_reading_uom_code%TYPE,
p_usage_type_code IN okl_lines_interface.usage_type_code%TYPE,
p_fixed_usage_quantity IN okl_lines_interface.fixed_usage_quantity%TYPE,
p_usage_period IN okl_lines_interface.usage_period%TYPE,
p_usage_no_of_period IN okl_lines_interface.usage_no_of_period%TYPE,
p_fee_no_of_period IN okl_lines_interface.fee_exp_no_of_periods%TYPE,
p_fee_frequency IN okl_lines_interface.fee_exp_frequency%TYPE,
p_fee_amount_per_period IN okl_lines_interface.fee_exp_amount_per_period%TYPE,
p_prop_tax_applicable IN okl_lines_interface.prop_tax_applicable%TYPE,
p_prop_tax_lease_rep IN okl_lines_interface.prop_tax_lease_rep%TYPE,
p_french_tax_code IN okl_lines_interface.french_tax_code%TYPE,
p_french_tax_name IN okl_lines_interface.french_tax_name%TYPE,
-- Added by rravikir (Fix for Bug 3947959)
p_prop_tax_bill_method IN okl_lines_interface.prop_tax_bill_method%TYPE,
p_prop_tax_as_contract_yn IN okl_lines_interface.prop_tax_as_contract_yn%TYPE
-- End
) IS
line_rule_failed EXCEPTION;
SELECT id1
FROM okl_time_units_v
WHERE name = p_frequency
AND status = 'A'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
SELECT code
FROM okl_french_tax_v
WHERE ( code = p_tax_code
AND
p_tax_code IS NOT NULL)
OR ( name = p_tax_name
AND
p_tax_name IS NOT NULL)
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date), TRUNC(SYSDATE))
AND NVL(TRUNC(end_date), TRUNC(SYSDATE));
SELECT 'Y'
FROM fnd_lookups
WHERE lookup_type = 'OKS_USAGE_TYPE'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND
NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
SELECT enable_asset_default
FROM okl_property_tax_setups
WHERE org_id = (SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_chr_id);
SELECT rgp.id
FROM okc_rule_groups_v rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LAASTX'
AND rgp.rgp_type = 'KRG';
SELECT rule.id
FROM okc_rules_v rule
WHERE rule.dnz_chr_id = p_chr_id
AND rule.rgp_id = p_rgp_id
AND rule.rule_information_category = 'LAASTX';
create_update_asset_rules(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_chr_id => p_chr_id,
p_cle_id => p_cle_id,
p_contract_number => p_contract_number,
p_line_number => p_line_number,
p_line_type => p_line_type,
p_inv_org_id => p_inv_org_id,
p_sales_tax_exempt_flag => p_sales_tax_exempt_flag,
p_sales_tax_exempt_cert_number => p_sales_tax_exempt_cert_number,
p_sales_tax_override_flag => p_sales_tax_override_flag,
p_sales_tax_override_rate => p_sales_tax_override_rate,
p_st_upd_lines_from_contract => p_st_upd_lines_from_contract,
p_st_transfer_of_title => p_st_transfer_of_title,
p_st_sale_and_lease_back => p_st_sale_and_lease_back,
p_st_purchase_of_lease => p_st_purchase_of_lease,
p_st_equipment_usage => p_st_equipment_usage,
p_st_equipment_age => p_st_equipment_age,
p_st_asset_upfront_tax => p_st_asset_upfront_tax,
p_vat_tax_exempt => p_vat_tax_exempt,
p_vat_tax_exempt_cert_number => p_vat_tax_exempt_cert_number,
p_vat_tax_override_flag => p_vat_tax_override_flag,
p_vat_tax_override_rate => p_vat_tax_override_rate,
p_prop_tax_applicable => p_prop_tax_applicable,
p_prop_tax_lease_rep => p_prop_tax_lease_rep,
p_french_tax_code => p_french_tax_code,
p_french_tax_name => p_french_tax_name,
p_prop_tax_bill_method => p_prop_tax_bill_method,
p_prop_tax_as_contract_yn => p_prop_tax_as_contract_yn);
OKL_RULE_PUB.update_rule(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_rulv_rec => l_rulv_rec,
x_rulv_rec => x_rulv_rec
);
SELECT id1
FROM okx_parties_v
WHERE ((name = p_name
AND
p_name IS NOT NULL)
OR
(id1 = p_id
AND
p_id IS NOT NULL
)
);
SELECT id
FROM okl_late_policies_v
WHERE name = p_policy_code
AND org_id = p_org_id
AND ( late_policy_type_code = 'LCT'
OR
late_policy_type_code = p_policy_type );
SELECT id1
FROM okl_bpd_active_csh_rls_v
WHERE name = p_rule_name
AND org_id = p_org_id
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(START_DATE),TRUNC(SYSDATE)) AND
NVL(TRUNC(END_DATE),TRUNC(SYSDATE));
select id
INTO l_formula_id
FROM OKL_FORMULAE_B
WHERE NAME = p_name;
SELECT object1_id1
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND chr_id = dnz_chr_id
AND jtot_object1_code = 'OKX_PARTY'
AND rle_code = 'LESSEE';
SELECT contract_number, start_date
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT 'Y'
FROM okl_stream_types_uv STL
WHERE STL.id = p_sty_id
AND STL.start_date <= p_contract_start_date
AND (STL.end_date >= p_contract_start_date OR STL.end_date IS NULL);
SELECT tax_schedule_yn
FROM okl_system_params;
l_st_upd_lines_from_contract := p_header_rec.st_update_lines_from_contract;
SELECT id1
FROM okx_salesreps_v
WHERE (( id1 = p_salesrep_id
AND
p_salesrep_id IS NOT NULL
)
OR
( name = p_salesrep_name
AND
p_salesrep_name IS NOT NULL
)
)
AND org_id = p_org_id;
SELECT party_site_id
FROM okx_party_site_uses_v
WHERE ((party_site_number = p_site_number
AND
p_site_number IS NOT NULL)
OR
(id1 = p_site_id
AND
p_site_id IS NOT NULL
)
)
AND site_use_type = 'SHIP_TO'
AND party_id = p_customer_id
AND status = 'A';
SELECT site_use.id1
FROM okx_cust_site_uses_v site_use,
hz_cust_acct_sites_all site
WHERE site.cust_acct_site_id = site_use.cust_acct_site_id
AND site_use.party_site_id = p_party_site_id
AND site_use.org_id = p_org_id
AND site.org_id = p_org_id
AND site_use.site_use_code = 'SHIP_TO'
AND site_use.cust_account_id = p_cust_acct_id
AND site_use.b_status = 'A'
AND site.status = 'A';
SELECT id1
FROM okx_party_sites_v
WHERE ( party_site_number = p_site_number
AND
p_site_number IS NOT NULL)
OR ( id1 = p_site_id
AND
p_site_id IS NOT NULL);
SELECT rrd.id
FROM okc_rg_role_defs rrd,
okc_subclass_rg_defs srd,
okc_subclass_roles srl
WHERE rrd.srd_id = srd.id
AND rrd.sre_id = srl.id
AND srd.scs_code = 'LEASE'
AND srd.rgd_code = p_rgd_code --'LAGRDT'
AND srl.scs_code = 'LEASE'
AND srl.rle_code = p_rle_code; --'GUARANTOR'
SELECT id1
FROM okx_parties_v
WHERE ((party_number = p_private_label_number
AND
p_private_label_number IS NOT NULL)
OR
(id1 = p_private_label_id
AND
p_private_label_id IS NOT NULL
)
);
OKL_INS_POLICIES_PUB.INSERT_INS_POLICIES(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_ipyv_rec,
x_ipyv_rec => x_ipyv_rec
);
SELECT pay_group_lookup_code
FROM po_vendor_sites_all
WHERE vendor_site_id = p_vendor_paysite_id
AND org_id = p_auth_org_id;
SELECT id1
FROM okx_parties_v
WHERE ((party_number = p_customer_number
AND
p_customer_number IS NOT NULL
)
OR
(id1 = p_customer_id
AND
p_customer_id IS NOT NULL
)
);
SELECT 'Y'
FROM FND_LOOKUPS
WHERE lookup_type = 'OKL_PAYMENT_BASIS'
AND nvl(enabled_flag,'N') = 'Y'
AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
AND lookup_code = p_payment_basis;
SELECT 'Y'
FROM FND_LOOKUPS
WHERE lookup_type = 'OKL_DISBURSE_BASIS'
AND nvl(enabled_flag,'N') = 'Y'
AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
AND lookup_code = p_disbursement_basis;
SELECT 'Y'
FROM FND_LOOKUPS
WHERE lookup_type = 'OKL_PROC_FEE_BASIS'
AND nvl(enabled_flag,'N') = 'Y'
AND sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate)
AND lookup_code = p_processing_fee_basis;
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec
);
debug_message('------>prescribed_asset_flag updated: '||x_klev_rec.prescribed_asset_yn);
okl_contract_pub.update_contract_line(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec
);
debug_message('------>Bill to updated: '||x_clev_rec.bill_to_site_use_id);
SELECT rgp.id
FROM okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.rgp_type = 'KRG';
SELECT organization_id
FROM hr_all_organization_units
WHERE organization_id = p_inventory_org_id
AND SYSDATE BETWEEN NVL(date_from, SYSDATE) AND NVL(date_to, SYSDATE);
SELECT organization_id
FROM hr_all_organization_units
WHERE ((name = p_inventory_org_name
AND
p_inventory_org_name IS NOT NULL)
OR
(organization_id = p_inventory_org_id
AND
p_inventory_org_id IS NOT NULL
)
)
AND SYSDATE BETWEEN NVL(date_from, SYSDATE) AND NVL(date_to, SYSDATE);
SELECT inventory_item_id
FROM mtl_system_items
WHERE ((segment1 = p_inventory_item_name
AND
p_inventory_item_name IS NOT NULL)
OR
(inventory_item_id = p_inventory_item_id
AND
p_inventory_item_id IS NOT NULL
)
)
AND organization_id = p_inventory_org_id
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
SELECT id1
FROM okx_system_items_v
WHERE ((name = p_inventory_item_name
AND
p_inventory_item_name IS NOT NULL)
OR
(id1 = p_inventory_item_id
AND
p_inventory_item_id IS NOT NULL
)
)
AND service_item_flag = 'Y'
AND vendor_warranty_flag = 'N'
AND organization_id = p_inventory_org_id
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
SELECT id1,
id2
FROM okx_assets_v
WHERE asset_number = p_asset_number
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
SELECT code_combination_id
FROM fa_asset_keywords
WHERE (segment1 = p_segment1
OR
p_segment1 IS NULL)
AND (segment2 = p_segment2
OR
p_segment2 IS NULL)
AND (segment2 = p_segment2
OR
p_segment2 IS NULL)
AND (segment3 = p_segment3
OR
p_segment3 IS NULL)
AND (segment4 = p_segment4
OR
p_segment4 IS NULL)
AND (segment5 = p_segment5
OR
p_segment5 IS NULL)
AND (segment6 = p_segment6
OR
p_segment6 IS NULL)
AND (segment7 = p_segment7
OR
p_segment7 IS NULL)
AND (segment8 = p_segment8
OR
p_segment8 IS NULL)
AND (segment9 = p_segment9
OR
p_segment9 IS NULL)
AND (segment10 = p_segment10
OR
p_segment10 IS NULL)
AND enabled_flag <> 'N'
AND TRUNC(SYSDATE) BETWEEN NVL(TRUNC(start_date_active), TRUNC(SYSDATE)) AND NVL(TRUNC(end_date_active), TRUNC(SYSDATE));
SELECT category_id
FROM FA_CATEGORIES_B
WHERE (segment1 = NVL(p_segment1,'NULL') OR segment1 IS NULL)
AND (segment2 = NVL(p_segment2,'NULL') OR segment2 is NULL)
AND (segment3 = NVL(p_segment3,'NULL') OR segment3 IS NULL)
AND (segment4 = NVL(p_segment4,'NULL') OR segment4 IS NULL)
AND (segment5 = NVL(p_segment5,'NULL') OR segment5 IS NULL)
AND (segment6 = NVL(p_segment6,'NULL') OR segment6 IS NULL)
AND (segment7 = NVL(p_segment7,'NULL') OR segment7 IS NULL)
AND enabled_flag <> 'N'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
SELECT location_id
FROM okx_ast_locs_v
WHERE (segment1 = NVL(P_segment1,'NULL') OR segment1 IS NULL)
AND (segment2 = NVL(p_segment2,'NULL') OR segment2 is NULL)
AND (segment3 = NVL(p_segment3,'NULL') OR segment3 IS NULL)
AND (segment4 = NVL(p_segment4,'NULL') OR segment4 IS NULL)
AND (segment5 = NVL(p_segment5,'NULL') OR segment5 IS NULL)
AND (segment6 = NVL(p_segment6,'NULL') OR segment6 IS NULL)
AND (segment7 = NVL(p_segment7,'NULL') OR segment7 IS NULL)
AND enabled_flag <> 'N'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE);
SELECT id1
FROM okx_party_site_uses_v
WHERE ((party_site_number = p_install_site_name
AND
p_install_site_number IS NOT NULL)
OR
(id1 = p_install_site_id
AND
p_install_site_id IS NOT NULL
)
)
AND site_use_type = 'INSTALL_AT'
AND party_id = g_customer_id
AND status = 'A';
SELECT id
FROM OKC_LINE_STYLES_B ols,
okc_subclass_top_line ostl
WHERE LTY_CODE = p_lty_code
AND ostl.scs_code = 'LEASE'
AND ols.id = ostl.lse_id;
SELECT ols2.id
FROM okc_line_styles_v ols1,
okc_line_styles_v ols2,
okc_subclass_top_line ostl
WHERE ostl.scs_code = 'LEASE'
AND ostl.lse_id = ols1.id
AND ols1.lty_code = p_top_line_lty_code
AND ols2.lty_code = p_lty_code
AND ols2.lse_parent_id = ostl.lse_id;
select 1
from fnd_lookups lok
where lok.lookup_type = 'OKL_PROPERTY_CLASS_OPTIONS'
and lok.lookup_code = p_prc_code;
SELECT id
FROM okc_k_lines_v
WHERE name = p_asset_number
AND chr_id = p_chr_id
AND lse_id = (
SELECT id
FROM okc_line_styles_v
WHERE lty_code = 'FREE_FORM1' -- to determine TOP line
);
SELECT 'Y'
FROM okc_k_items_v item,
okl_k_lines_full_v model,
okc_line_styles_v style,
cs_counters cc,
cs_ctr_associations assoc
WHERE model.lse_id = style.id
AND model.id = item.cle_id
AND model.cle_id = p_asset_line_id
AND style.lty_code = 'ITEM'
AND cc.counter_group_id = assoc.counter_group_id
AND cc.usage_item_id = p_usage_item_id
AND assoc.source_object_id = item.object1_id1;
SELECT id
FROM okl_trx_quotes_b
WHERE quote_number = p_qte_number;
SELECT contract_number, start_date
FROM okl_k_headers_full_v
WHERE id = p_contract_id;
SELECT 'Y'
FROM okl_stream_types_uv STL
WHERE STL.id = p_sty_id
AND STL.start_date <= p_contract_start_date
AND (STL.end_date >= p_contract_start_date OR STL.end_date IS NULL);
SELECT fees_vendor_name,
fees_vendor_id
FROM okl_party_roles_interface
WHERE contract_number_old = p_contract_number
AND line_number = p_line_number;
SELECT nvl(capital_amount , 0)
from okl_k_lines
where id = p_line_id;
SELECT name
FROM okx_system_items_v
WHERE id1 = p_inv_item_id
AND id2 = p_inv_org_id;
null; -- Update asset x_asset_line_id with p_link_asset_amount
select nvl(capital_amount , 0)
into l_capital_amount
from okl_k_lines
where id = x_asset_line_id;
okl_contract_pub.update_contract_line
(p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => x_clev_rec,
x_klev_rec => x_klev_rec);
SELECT id
FROM okc_qa_check_lists_v
WHERE name = p_qcl_name;
SELECT id1
FROM okx_parties_v
WHERE ((party_number = p_customer_number
AND
p_customer_number IS NOT NULL
)
OR
(id1 = p_customer_id
AND
p_customer_id IS NOT NULL
)
);
SELECT start_date
FROM okc_k_headers_v
WHERE id = p_chr_id;
SELECT object1_id1
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND chr_id = dnz_chr_id
AND jtot_object1_code = 'OKX_PARTY'
AND rle_code = 'LESSEE' ;
SELECT cust_acct_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT 'Y'
FROM okc_k_headers_v
WHERE id = p_chr_id
AND end_date >= p_main_start_date;
SELECT contract_number,
orig_system_reference1
FROM okc_k_headers_v
WHERE id = p_chr_id
AND orig_system_source_code = 'OKL_IMPORT';
SELECT import_request_stage
FROM okl_header_interface
WHERE contract_number = p_contract_number
AND status = 'INTERFACED';
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_khr_id => p_chr_id ,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_SUBMIT_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_COMPLETE);
Update_Interface_Status(
p_contract_number => l_orig_contract,
p_new_contract_number => l_contract_number,
p_status => 'FINAL',
x_return_status => x_return_status
);
SELECT id
FROM okl_txl_assets_v
WHERE kle_id = p_id;
SELECT id1
FROM okx_parties_v
WHERE ((party_number = p_customer_number
AND
p_customer_number IS NOT NULL
)
OR
(id1 = p_customer_id
AND
p_customer_id IS NOT NULL
)
);
x_chrv_rec.deleted_yn := 'N';
SELECT line_type,
asset_number
FROM okl_lines_interface
WHERE contract_number_old = p_cont_number
AND line_number = p_line;
SELECT vendor_id
FROM po_vendors
WHERE ( (vendor_id = p_vendor_id
AND
p_vendor_id IS NOT NULL
)
OR (
vendor_name = p_vendor_name
AND
p_vendor_name IS NOT NULL
)
);
select vendor_site_id
from po_vendor_sites_all
where ( (vendor_site_id = p_site_id
AND
p_site_id IS NOT NULL)
OR
(vendor_site_code = p_site_code
AND
p_site_code IS NOT NULL)
)
and org_id = p_auth_org_id
and vendor_id = p_vendor_id;
SELECT id
FROM okl_txd_assets_b
WHERE tal_id = p_tal_id
AND tax_book = p_tax_book;
okl_txd_assets_pub.update_txd_asset_def(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_adpv_rec => l_adpv_rec,
x_adpv_rec => x_adpv_rec
);
debug_message('------>TXD Line Updated');
SELECT *
FROM okl_lines_interface
WHERE line_type = 'ASSET_ADDON'
AND contract_number_old = p_contract_number
AND asset_number = p_asset_number;
SELECT *
FROM okl_party_roles_interface
WHERE contract_number_old = p_contract_number_old
AND ( (line_number = p_line_number
AND
p_line_number IS NOT NULL
)
OR
(line_number IS NULL
AND
p_line_number IS NULL
)
);
l_clev_tbl.delete;
l_klev_tbl.delete;
l_cimv_tbl.delete;
SELECT *
FROM okl_lines_interface
WHERE line_type = 'ASSET_SUBSIDY'
AND contract_number_old = p_contract_number
AND asset_number = p_asset_number;
SELECT vendor_id
FROM po_vendors
WHERE vendor_name = p_name;
SELECT id
FROM okl_subsidies_b
WHERE name = p_name
AND org_id = p_org;
SELECT vendor_site_id
FROM po_vendor_sites_all
WHERE vendor_site_code = p_code
AND vendor_id = p_ven_id
AND org_id = p_org;
SELECT id1
FROM okx_payables_terms_v
WHERE NAME = p_name
AND status = 'A';
PROCEDURE check_and_delete(
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_contract_number IN varchar2,
p_delete_before_import_yn IN varchar2 -- akp_delete
) IS
l_proc_name VARCHAR2(35) := 'CHECK_AND_DELETE';
select id
from okc_k_headers_b
where contract_number = p_contract_no;
select id
from okl_ins_policies_b where khr_id=p_khr_id;
if (upper(nvl(p_delete_before_import_yn, 'N')) <> 'Y') then
return;
debug_message('Contract ' || p_contract_number || ' picked up for delete...');
debug_message('Contract ' || p_contract_number || ' not found in OLM for delete...');
okl_contract_pub.delete_contract(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_contract_id => l_contract_id );
debug_message('Contract ' || p_contract_number || ' deleted from OLM...');
debug_message('Error during delete contract ' || p_contract_number );
OKL_INS_POLICIES_PUB.delete_ins_policies(p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_ipyv_rec => l_ipyv_rec);
debug_message('Contract ' || p_contract_number || ' deleted from OLM after delete_ins_policies...');
debug_message('Error during OKL_INS_POLICIES_PUB.delete_ins_policies ' || p_contract_number );
END check_and_delete;
x_update_status VARCHAR2(1);
SELECT rgp.id rgp_id,
slh.id slh_id
FROM okc_rule_groups_b rgp,
okc_rules_b slh,
okl_strm_type_b sty
WHERE rgp.chr_id = p_chr_id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.rgd_code = 'LALEVL'
AND slh.rgp_id = rgp.id
AND slh.dnz_chr_id = rgp.dnz_chr_id
AND slh.rule_information_category = 'LASLH'
AND sty.id = slh.object1_id1
AND slh.jtot_object1_code = 'OKL_STRMTYP'
AND sty.stream_type_purpose = 'VARIABLE_INTEREST_SCHEDULE';
select 1 from
xle_le_ou_ledger_v
where OPERATING_UNIT_ID=p_org_id
and LEGAL_ENTITY_ID=p_legal_entity; -- cusrsor to get LE for an OU
SELECT BATCH_NUMBER
,TEMPLATE_NUMBER
,STATUS
,CREATED_BY
,DATE_CREATED
,DATE_CREATED_IN_OKL
,APPLICATION_CODE
,CONTRACT_CATEGORY
,CONTRACT_NUMBER_OLD
,CONTRACT_SOURCE
,CONTRACT_NUMBER
,CUSTOMER_NUMBER
,CUSTOMER_ID
,CUSTOMER_ACCOUNT_NUMBER
,CUSTOMER_ACCOUNT_ID
,START_DATE
,IMPORT_REQUEST_STAGE
,CURRENCY_CODE
,CUSTOMER_PO_NUMBER
,DATE_SIGNED
,DATE_APPROVED
,ACCEPTANCE_METHOD_CODE
,CONSUMER_CREDIT_ACT_DEAL_FLAG
,TERM
,CONVERTED_ACCOUNT
,ACCEPTED_DATE
,DEAL_TYPE_CODE
,EXPECTED_DELIVERY_DATE
,CONTRACT_DESCRIPTION
,SALESPERSON_NAME
,SALESPERSON_ID
,PRIVATE_LABEL_NUMBER
,PRIVATE_LABEL_ID
,PRIVATE_LABEL_URL
,PROGRAM_AGREEMENT_NUMBER
,PROGRAM_AGREEMENT_ID
,PROGRAM_VENDOR_NAME
,PROGRAM_VENDOR_ID
,MASTER_LEASE_AGREEMENT_NUMBER
,MASTER_LEASE_AGREEMENT_ID
,PRODUCT_NAME
,PRODUCT_ID
,CALCULATE_RESIDUAL_INSURANCE
,REBOOK_LIMIT_DATE
,IMPORT_REQUEST_ID
,END_OF_TERM_OPTION
,END_OF_TERM_AMOUNT
,MID_TERM_OPTION
,MID_TERM_AMOUNT
,SECURITY_DEPOSIT_HOLD_FLAG
,SECURITY_DEPOSIT_NET_FLAG
,SECURITY_DEPOSIT_DATE
,RENEWAL_NOTICE_DAYS
,RENEWAL_OPTION
,RENEWAL_AMOUNT
,LATE_INTEREST_HELD_UNTIL_DATE
,LATE_INTEREST_EXEMPT_FLAG
,LATE_INTEREST_PRODUCT_CODE
,LATE_CHARGE_HELD_UNTIL_DATE
,LATE_CHARGE_PRODUCT_CODE
,LATE_CHARGE_EXEMPT_FLAG
,VARIABLE_RATE
,CONVERT_TYPE
,CONVERSION_METHOD
,DATE_OF_CONVERSION
,CONVERT_BY_DATE
,VARIABLE_METHOD
,INDEX_NAME
,BASE_RATE
,ADDER
,MINIMUM_RATE
,MAXIMUM_RATE
,TOLERANCE
,ADJUSTMENT_FREQUENCY
,DAYS_IN_YEAR
,DAYS_IN_MONTH
,INTEREST_METHOD
,INTEREST_START_DATE
,METHOD_OF_CALCULATION
,FORMULA_NAME
,CAPITALIZE_FLAG
,NON_NOTIFICATION_FLAG
,TAX_WITHHOLDING_FLAG
,TAX_CALC_FORMULA_NAME
,TAX_OWNER
,FACTORING_DATE
,FACTORING_PERCENTAGE
,FACTORING_DISCOUNT_RATE
,EVERGREEN_ELIGIBLE_FLAG
,BILL_TO_ADDRESS_ID
,BILL_TO_ADDRESS
,BANK_ACCOUNT_ID
,BANK_ACCOUNT_NUMBER
,REASON_FOR_INVOICE_REVIEW
,INVOICE_REVIEW_UNTIL_DATE
,INVOICE_FORMAT_ID
,INVOICE_FORMAT_CODE
,PRINT_LEAD_DAYS --hariven bug 5359935
,REVIEW_INVOICE_FLAG
,PAYMENT_METHOD_ID
,PAYMENT_METHOD
,AUTHORING_ORG_ID
,INVENTORY_ORGANIZATION_ID
,POLICY_NUMBER
,POLICY_EFFECTIVE_FROM
,POLICY_EFFECTIVE_TO
,COVERED_AMOUNT
,DEDUCTIBLE_AMOUNT
,ENDORSEMENT
,NAME_OF_INSURED
,LESSOR_INSURED_FLAG
,LESSOR_PAYEE_FLAG
,INSURANCE_COMPANY_NAME
,INSURANCE_COMPANY_ID
,INSURANCE_COMPANY_SITE_NAME
,INSURANCE_COMPANY_SITE_ID
,AGENT_NAME
,AGENT_ID
,AGENT_ADDRESS_SITE_NAME
,AGENT_ADDRESS_SITE_ID
,PROOF_DUE_DATE
,PROOF_PROVIDED_DATE
,PREFUNDING_ELIGIBLE_FLAG
,PRIVATE_ACTIVITY_BOND_FLAG
,RVI_AUTO_CALCULATE_FLAG
,RVI_GUARANTEED_AMOUNT
,RVI_PRESENT_GUARANTEED_AMOUNT
,RVI_PREMIUM_AMOUNT
,RVI_RATE
,FLOOR_PRICE_FORMULA
,REMKT_SALE_PRICE_FORMULA
,REPURCHASE_QUOTE_OPTION
,REPURCHASE_QUOTE_FORMULA
,SALE_PRICE_OPTION
,SALE_PRICE_AMOUNT
,SALE_PRICE_FORMULA
,SALE_PRICE_PRORATE
,DISCOUNT_RATE_OPTION
,DISCOUNT_RATE_AMOUNT
,DISCOUNT_RATE_FORMULA
,DISCOUNT_RATE_PRORATE
,QUOTE_FEE_OPTION
,QUOTE_FEE_AMOUNT
,QUOTE_FEE_FORMULA
,QUOTE_FEE_PRORATE
,ERT_PURCHASE_OPT
,ERT_PURCHASE_OPT_TYPE
,ERT_PURCHASE_OPT_AMOUNT
,ERT_PURCHASE_OPT_FORMULA
,ERT_PURCHASE_OPT_PRORATE
,ERT_PURCHASE_OPT_MAX_OPT
,ERT_PURCHASE_OPT_MAX_AMT
,ERT_PURCHASE_OPT_MAX_FORMULA
,ERT_PURCHASE_OPT_MIN_OPT
,ERT_PURCHASE_OPT_MIN_AMT
,ERT_PURCHASE_OPT_MIN_FORMULA
,EOT_PURCHASE_OPT
,EOT_PURCHASE_OPT_TYPE
,EOT_PURCHASE_OPT_AMOUNT
,EOT_PURCHASE_OPT_FORMULA
,EOT_PURCHASE_OPT_PRORATE
,EOT_PURCHASE_OPT_MAX_OPT
,EOT_PURCHASE_OPT_MAX_AMT
,EOT_PURCHASE_OPT_MAX_FORMULA
,EOT_PURCHASE_OPT_MIN_OPT
,EOT_PURCHASE_OPT_MIN_AMT
,EOT_PURCHASE_OPT_MIN_FORMULA
,TQP_ERL_TERMINATION_ALWD_FLAG
,TQP_PRT_TERMINATION_ALWD_FLAG
,QUOTE_EFFECTIVE_DAYS
,QUOTE_EFF_MAX_DAYS
,EOT_TOLERANCE_DAYS
,PRT_TERMINATION_APPROVAL_REQ
,PRT_TERMINATION_ALLOWED_FLAG
,GAIN_LOSS_APPROVAL_REQ
,GAIN_LOSS_NET_QUOTE_OPT
,GAIN_LOSS_NET_QUOTE_AMT
,GAIN_LOSS_NET_QUOTE_FORMULA
,GAIN_LOSS_TOLERANCE_ALLOWED
,QUOTE_RCPT_CONTRACT_ROLE
,QUOTE_APPROVER_CONTRACT_ROLE
,QUOTE_CRTSY_COPY_CONTRACT_ROLE
,ERT_QUOTE_CALC_TERM_OPTION
,ERT_QUOTE_CALC_TERM_FORMULA
,ERT_CONTRACT_OBLIG_OPT
,ERT_CONTRACT_OBLIG_AMOUNT
,ERT_CONTRACT_OBLIG_FORMULA
,ERT_CONTRACT_OBLIG_PRORATE
,ERT_DISC_RATE_OPT
,ERT_DISC_RATE_AMOUNT
,ERT_DISC_RATE_FORMULA
,ERT_DISC_RATE_PRORATE
,ERT_QUOTE_FEE_OPT
,ERT_QUOTE_FEE_AMOUNT
,ERT_QUOTE_FEE_FORMULA
,ERT_QUOTE_FEE_PRORATE
,ERT_RETURN_FEE_OPTION
,ERT_RETURN_FEE_AMOUNT
,ERT_RETURN_FEE_FORMULA
,ERT_RETURN_FEE_PRORATE
,ERT_ROLL_INCTV_OPTION
,ERT_ROLL_INCTV_AMOUNT
,ERT_ROLL_INCTV_FORMULA
,ERT_ROLL_INCTV_PRORATE
,ERT_SECU_DEP_DISPO_OPTION
,ERT_SECU_DEP_DISPO_AMOUNT
,ERT_SECU_DEP_DISPO_FORMULA
,ERT_SECU_DEP_DISPO_PRORATE
,ERT_TERM_PENALTY_OPTION
,ERT_TERM_PENALTY_AMOUNT
,ERT_TERM_PENALTY_FORMULA
,ERT_TERM_PENALTY_PRORATE
,ERT_TERM_PENALTY_CAP_OPTION
,ERT_TERM_PENALTY_CAP_AMOUNT
,ERT_TERM_PENALTY_CAP_FORMULA
,ERT_EST_PROP_TAX_OPTION
,ERT_EST_PROP_TAX_AMOUNT
,ERT_EST_PROP_TAX_FORMULA
,ERT_EST_PROP_TAX_PRORATE
,ERT_CONTR_FEES_OPTION
,ERT_OUTSTND_BAL_OPTION
,ERT_SERV_MANT_OPTION
,EOT_QUOTE_CALC_TERM_OPTION
,EOT_QUOTE_CALC_TERM_FORMULA
,EOT_CONTRACT_OBLIG_OPT
,EOT_CONTRACT_OBLIG_AMOUNT
,EOT_CONTRACT_OBLIG_FORMULA
,EOT_CONTRACT_OBLIG_PRORATE
,EOT_DISC_RATE_OPT
,EOT_DISC_RATE_AMOUNT
,EOT_DISC_RATE_FORMULA
,EOT_DISC_RATE_PRORATE
,EOT_QUOTE_FEE_OPT
,EOT_QUOTE_FEE_AMOUNT
,EOT_QUOTE_FEE_FORMULA
,EOT_QUOTE_FEE_PRORATE
,EOT_RETURN_FEE_OPTION
,EOT_RETURN_FEE_AMOUNT
,EOT_RETURN_FEE_FORMULA
,EOT_RETURN_FEE_PRORATE
,EOT_ROLL_INCTV_OPTION
,EOT_ROLL_INCTV_AMOUNT
,EOT_ROLL_INCTV_FORMULA
,EOT_ROLL_INCTV_PRORATE
,EOT_SECU_DEP_DISPO_OPTION
,EOT_SECU_DEP_DISPO_AMOUNT
,EOT_SECU_DEP_DISPO_FORMULA
,EOT_SECU_DEP_DISPO_PRORATE
,EOT_TERM_PENALTY_OPTION
,EOT_TERM_PENALTY_AMOUNT
,EOT_TERM_PENALTY_FORMULA
,EOT_TERM_PENALTY_PRORATE
,EOT_TERM_PENALTY_CAP_OPTION
,EOT_TERM_PENALTY_CAP_AMOUNT
,EOT_TERM_PENALTY_CAP_FORMULA
,EOT_EST_PROP_TAX_OPTION
,EOT_EST_PROP_TAX_AMOUNT
,EOT_EST_PROP_TAX_FORMULA
,EOT_EST_PROP_TAX_PRORATE
,EOT_CONTR_FEES_OPTION
,EOT_OUTSTND_BAL_OPTION
,EOT_SERV_MANT_OPTION
,EVERGREEN_PASSTHRU_FEES
,EVERGREEN_PASSTHRU_PERCENT
,LIEN_TYPE
,FILING_NUMBER
,FILING_DATE
,FILING_STATUS
,LIEN_HOLDER_NAME
,LIEN_HOLDER_ID
,JURISDICTION
,SUB_JURISDICTION
,LIEN_EXPIRATION_DATE
,LIEN_CONTINUATION_NUMBER
,LIEN_CONTINUATION_DATE
,TITLE_TYPE
,TITLE_ISSUER_NAME
,TITLE_ISSUER_ID
,TITLE_DATE
,TITLE_NUMBER
,REGISTRATION_NUMBER
,LOCATION
,TITLE_CUSTODIAN_NAME
,TITLE_CUSTODIAN_ID
,PAYEE_SITE
,REGISTRATION_LOCATION
,REG_EXPIRATION_DATE
,CREDIT_LINE_ID
,CREDIT_LINE_NUMBER
,PRTFL_APPROVAL_REQ
,PRTFL_ASSGN_GROUP
,PRTFL_BUDGET_AMT_OPT
,PRTFL_BUDGET_FIXED_AMT
,PRTFL_BUDGET_AMT_FORMULA
,PRTFL_DAYS_FROM_CON_EXPR
,PRTFL_STRATEGY
,PROP_TAX_APPLICABLE
,PROP_TAX_LEASE_REP
,MEX_TAX_SUB_BASIC_WTHLD
,MEX_TAX_CALC_FORMULA
,AUS_TAX_STAMP_DUTY
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,ASSIGNABLE_YN
,LESSEE_VENDOR_ID
,LESSEE_VENDOR_NAME
,LESSEE_VENDOR_SITE_ID
,LESSEE_VENDOR_SITE_CODE
,INSURANCE_COMPANY_NUMBER
,INSURANCE_COMPANY_SITE_NUMBER
,AGENT_NUMBER
,AGENT_ADDRESS_SITE_NUMBER
,CASH_APPL_RULE_NAME
,PROP_TAX_BILL_METHOD
,GAIN_LOSS_TOLRNC_ALLWD_AMT
,GAIN_LOSS_TOLRNC_ALLWD_FRML
,GAIN_LOSS_TOLRNC_BASIS
,GAIN_LOSS_APPRV_PROC_FRML
,ERT_ABSORBED_FEE_FRML
,ERT_EXPENSE_FEE_FRML
,ERT_FINANCED_FEE_FRML
,ERT_GENERAL_FEE_FRML
,ERT_INCOME_FEE_FRML
,ERT_MISC_FEE_FRML
,ERT_PASSTHRU_FEE_FRML
,ERT_ROLLOVER_FEE_FRML
,EOT_ABSORBED_FEE_FRML
,EOT_EXPENSE_FEE_FRML
,EOT_FINANCED_FEE_FRML
,EOT_GENERAL_FEE_FRML
,EOT_INCOME_FEE_FRML
,EOT_MISC_FEE_FRML
,EOT_PASSTHRU_FEE_FRML
,EOT_ROLLOVER_FEE_FRML
,DELETE_BEFORE_IMPORT_YN
,ST_UPDATE_LINES_FROM_CONTRACT
,ST_INTEREST_DISCLOSED
,ST_TRANSFER_OF_TITLE
,ST_SALE_AND_LEASE_BACK
,ST_PURCHASE_OF_LEASE
,ST_EQUIPMENT_USAGE
,ST_EQUIPMENT_AGE
,ST_ASSET_UPFRONT_TAX
,ST_BILL_STREAM_TYPE_CODE
,ST_BILL_STREAM_PURPOSE_CODE
,ST_FIN_STREAM_TYPE_CODE
,ST_FIN_STREAM_PURPOSE_CODE
,ST_CAP_STREAM_TYPE_CODE
,ST_CAP_STREAM_PURPOSE_CODE
,ST_TAX_SCHEDULE_APPLIES_FLAG
FROM okl_header_interface
WHERE (
batch_number = p_batch_number
OR
contract_number_old = p_contract_number
OR
(
customer_number = p_customer_number
AND
start_date between p_start_date_from AND p_start_date_to
)
)
AND nvl(scheduled_worker_id,'NONE') = g_instance_number
AND status = 'INTERFACED'
AND import_request_stage <> 'NEW' ; --Process INTERFACED contract upto import_request_stage
SELECT
BATCH_NUMBER
,TEMPLATE_NUMBER
,STATUS
,CREATED_BY
,DATE_CREATED
,DATE_CREATED_IN_OKL
,APPLICATION_CODE
,CONTRACT_CATEGORY
,CONTRACT_NUMBER_OLD
,CONTRACT_SOURCE
,CONTRACT_NUMBER
,CUSTOMER_NUMBER
,CUSTOMER_ID
,CUSTOMER_ACCOUNT_NUMBER
,CUSTOMER_ACCOUNT_ID
,START_DATE
,IMPORT_REQUEST_STAGE
,CURRENCY_CODE
,CUSTOMER_PO_NUMBER
,DATE_SIGNED
,DATE_APPROVED
,ACCEPTANCE_METHOD_CODE
,CONSUMER_CREDIT_ACT_DEAL_FLAG
,TERM
,CONVERTED_ACCOUNT
,ACCEPTED_DATE
,DEAL_TYPE_CODE
,EXPECTED_DELIVERY_DATE
,CONTRACT_DESCRIPTION
,SALESPERSON_NAME
,SALESPERSON_ID
,PRIVATE_LABEL_NUMBER
,PRIVATE_LABEL_ID
,PRIVATE_LABEL_URL
,PROGRAM_AGREEMENT_NUMBER
,PROGRAM_AGREEMENT_ID
,PROGRAM_VENDOR_NAME
,PROGRAM_VENDOR_ID
,MASTER_LEASE_AGREEMENT_NUMBER
,MASTER_LEASE_AGREEMENT_ID
,PRODUCT_NAME
,PRODUCT_ID
,CALCULATE_RESIDUAL_INSURANCE
,REBOOK_LIMIT_DATE
,IMPORT_REQUEST_ID
,END_OF_TERM_OPTION
,END_OF_TERM_AMOUNT
,MID_TERM_OPTION
,MID_TERM_AMOUNT
,SECURITY_DEPOSIT_HOLD_FLAG
,SECURITY_DEPOSIT_NET_FLAG
,SECURITY_DEPOSIT_DATE
,RENEWAL_NOTICE_DAYS
,RENEWAL_OPTION
,RENEWAL_AMOUNT
,LATE_INTEREST_HELD_UNTIL_DATE
,LATE_INTEREST_EXEMPT_FLAG
,LATE_INTEREST_PRODUCT_CODE
,LATE_CHARGE_HELD_UNTIL_DATE
,LATE_CHARGE_PRODUCT_CODE
,LATE_CHARGE_EXEMPT_FLAG
,VARIABLE_RATE
,CONVERT_TYPE
,CONVERSION_METHOD
,DATE_OF_CONVERSION
,CONVERT_BY_DATE
,VARIABLE_METHOD
,INDEX_NAME
,BASE_RATE
,ADDER
,MINIMUM_RATE
,MAXIMUM_RATE
,TOLERANCE
,ADJUSTMENT_FREQUENCY
,DAYS_IN_YEAR
,DAYS_IN_MONTH
,INTEREST_METHOD
,INTEREST_START_DATE
,METHOD_OF_CALCULATION
,FORMULA_NAME
,CAPITALIZE_FLAG
,NON_NOTIFICATION_FLAG
,TAX_WITHHOLDING_FLAG
,TAX_CALC_FORMULA_NAME
,TAX_OWNER
,FACTORING_DATE
,FACTORING_PERCENTAGE
,FACTORING_DISCOUNT_RATE
,EVERGREEN_ELIGIBLE_FLAG
,BILL_TO_ADDRESS_ID
,BILL_TO_ADDRESS
,BANK_ACCOUNT_ID
,BANK_ACCOUNT_NUMBER
,REASON_FOR_INVOICE_REVIEW
,INVOICE_REVIEW_UNTIL_DATE
,INVOICE_FORMAT_ID
,INVOICE_FORMAT_CODE
,PRINT_LEAD_DAYS --hariven bug 5359935
,REVIEW_INVOICE_FLAG
,PAYMENT_METHOD_ID
,PAYMENT_METHOD
,AUTHORING_ORG_ID
,INVENTORY_ORGANIZATION_ID
,POLICY_NUMBER
,POLICY_EFFECTIVE_FROM
,POLICY_EFFECTIVE_TO
,COVERED_AMOUNT
,DEDUCTIBLE_AMOUNT
,ENDORSEMENT
,NAME_OF_INSURED
,LESSOR_INSURED_FLAG
,LESSOR_PAYEE_FLAG
,INSURANCE_COMPANY_NAME
,INSURANCE_COMPANY_ID
,INSURANCE_COMPANY_SITE_NAME
,INSURANCE_COMPANY_SITE_ID
,AGENT_NAME
,AGENT_ID
,AGENT_ADDRESS_SITE_NAME
,AGENT_ADDRESS_SITE_ID
,PROOF_DUE_DATE
,PROOF_PROVIDED_DATE
,PREFUNDING_ELIGIBLE_FLAG
,PRIVATE_ACTIVITY_BOND_FLAG
,RVI_AUTO_CALCULATE_FLAG
,RVI_GUARANTEED_AMOUNT
,RVI_PRESENT_GUARANTEED_AMOUNT
,RVI_PREMIUM_AMOUNT
,RVI_RATE
,FLOOR_PRICE_FORMULA
,REMKT_SALE_PRICE_FORMULA
,REPURCHASE_QUOTE_OPTION
,REPURCHASE_QUOTE_FORMULA
,SALE_PRICE_OPTION
,SALE_PRICE_AMOUNT
,SALE_PRICE_FORMULA
,SALE_PRICE_PRORATE
,DISCOUNT_RATE_OPTION
,DISCOUNT_RATE_AMOUNT
,DISCOUNT_RATE_FORMULA
,DISCOUNT_RATE_PRORATE
,QUOTE_FEE_OPTION
,QUOTE_FEE_AMOUNT
,QUOTE_FEE_FORMULA
,QUOTE_FEE_PRORATE
,ERT_PURCHASE_OPT
,ERT_PURCHASE_OPT_TYPE
,ERT_PURCHASE_OPT_AMOUNT
,ERT_PURCHASE_OPT_FORMULA
,ERT_PURCHASE_OPT_PRORATE
,ERT_PURCHASE_OPT_MAX_OPT
,ERT_PURCHASE_OPT_MAX_AMT
,ERT_PURCHASE_OPT_MAX_FORMULA
,ERT_PURCHASE_OPT_MIN_OPT
,ERT_PURCHASE_OPT_MIN_AMT
,ERT_PURCHASE_OPT_MIN_FORMULA
,EOT_PURCHASE_OPT
,EOT_PURCHASE_OPT_TYPE
,EOT_PURCHASE_OPT_AMOUNT
,EOT_PURCHASE_OPT_FORMULA
,EOT_PURCHASE_OPT_PRORATE
,EOT_PURCHASE_OPT_MAX_OPT
,EOT_PURCHASE_OPT_MAX_AMT
,EOT_PURCHASE_OPT_MAX_FORMULA
,EOT_PURCHASE_OPT_MIN_OPT
,EOT_PURCHASE_OPT_MIN_AMT
,EOT_PURCHASE_OPT_MIN_FORMULA
,TQP_ERL_TERMINATION_ALWD_FLAG
,TQP_PRT_TERMINATION_ALWD_FLAG
,QUOTE_EFFECTIVE_DAYS
,QUOTE_EFF_MAX_DAYS
,EOT_TOLERANCE_DAYS
,PRT_TERMINATION_APPROVAL_REQ
,PRT_TERMINATION_ALLOWED_FLAG
,GAIN_LOSS_APPROVAL_REQ
,GAIN_LOSS_NET_QUOTE_OPT
,GAIN_LOSS_NET_QUOTE_AMT
,GAIN_LOSS_NET_QUOTE_FORMULA
,GAIN_LOSS_TOLERANCE_ALLOWED
,QUOTE_RCPT_CONTRACT_ROLE
,QUOTE_APPROVER_CONTRACT_ROLE
,QUOTE_CRTSY_COPY_CONTRACT_ROLE
,ERT_QUOTE_CALC_TERM_OPTION
,ERT_QUOTE_CALC_TERM_FORMULA
,ERT_CONTRACT_OBLIG_OPT
,ERT_CONTRACT_OBLIG_AMOUNT
,ERT_CONTRACT_OBLIG_FORMULA
,ERT_CONTRACT_OBLIG_PRORATE
,ERT_DISC_RATE_OPT
,ERT_DISC_RATE_AMOUNT
,ERT_DISC_RATE_FORMULA
,ERT_DISC_RATE_PRORATE
,ERT_QUOTE_FEE_OPT
,ERT_QUOTE_FEE_AMOUNT
,ERT_QUOTE_FEE_FORMULA
,ERT_QUOTE_FEE_PRORATE
,ERT_RETURN_FEE_OPTION
,ERT_RETURN_FEE_AMOUNT
,ERT_RETURN_FEE_FORMULA
,ERT_RETURN_FEE_PRORATE
,ERT_ROLL_INCTV_OPTION
,ERT_ROLL_INCTV_AMOUNT
,ERT_ROLL_INCTV_FORMULA
,ERT_ROLL_INCTV_PRORATE
,ERT_SECU_DEP_DISPO_OPTION
,ERT_SECU_DEP_DISPO_AMOUNT
,ERT_SECU_DEP_DISPO_FORMULA
,ERT_SECU_DEP_DISPO_PRORATE
,ERT_TERM_PENALTY_OPTION
,ERT_TERM_PENALTY_AMOUNT
,ERT_TERM_PENALTY_FORMULA
,ERT_TERM_PENALTY_PRORATE
,ERT_TERM_PENALTY_CAP_OPTION
,ERT_TERM_PENALTY_CAP_AMOUNT
,ERT_TERM_PENALTY_CAP_FORMULA
,ERT_EST_PROP_TAX_OPTION
,ERT_EST_PROP_TAX_AMOUNT
,ERT_EST_PROP_TAX_FORMULA
,ERT_EST_PROP_TAX_PRORATE
,ERT_CONTR_FEES_OPTION
,ERT_OUTSTND_BAL_OPTION
,ERT_SERV_MANT_OPTION
,EOT_QUOTE_CALC_TERM_OPTION
,EOT_QUOTE_CALC_TERM_FORMULA
,EOT_CONTRACT_OBLIG_OPT
,EOT_CONTRACT_OBLIG_AMOUNT
,EOT_CONTRACT_OBLIG_FORMULA
,EOT_CONTRACT_OBLIG_PRORATE
,EOT_DISC_RATE_OPT
,EOT_DISC_RATE_AMOUNT
,EOT_DISC_RATE_FORMULA
,EOT_DISC_RATE_PRORATE
,EOT_QUOTE_FEE_OPT
,EOT_QUOTE_FEE_AMOUNT
,EOT_QUOTE_FEE_FORMULA
,EOT_QUOTE_FEE_PRORATE
,EOT_RETURN_FEE_OPTION
,EOT_RETURN_FEE_AMOUNT
,EOT_RETURN_FEE_FORMULA
,EOT_RETURN_FEE_PRORATE
,EOT_ROLL_INCTV_OPTION
,EOT_ROLL_INCTV_AMOUNT
,EOT_ROLL_INCTV_FORMULA
,EOT_ROLL_INCTV_PRORATE
,EOT_SECU_DEP_DISPO_OPTION
,EOT_SECU_DEP_DISPO_AMOUNT
,EOT_SECU_DEP_DISPO_FORMULA
,EOT_SECU_DEP_DISPO_PRORATE
,EOT_TERM_PENALTY_OPTION
,EOT_TERM_PENALTY_AMOUNT
,EOT_TERM_PENALTY_FORMULA
,EOT_TERM_PENALTY_PRORATE
,EOT_TERM_PENALTY_CAP_OPTION
,EOT_TERM_PENALTY_CAP_AMOUNT
,EOT_TERM_PENALTY_CAP_FORMULA
,EOT_EST_PROP_TAX_OPTION
,EOT_EST_PROP_TAX_AMOUNT
,EOT_EST_PROP_TAX_FORMULA
,EOT_EST_PROP_TAX_PRORATE
,EOT_CONTR_FEES_OPTION
,EOT_OUTSTND_BAL_OPTION
,EOT_SERV_MANT_OPTION
,EVERGREEN_PASSTHRU_FEES
,EVERGREEN_PASSTHRU_PERCENT
,LIEN_TYPE
,FILING_NUMBER
,FILING_DATE
,FILING_STATUS
,LIEN_HOLDER_NAME
,LIEN_HOLDER_ID
,JURISDICTION
,SUB_JURISDICTION
,LIEN_EXPIRATION_DATE
,LIEN_CONTINUATION_NUMBER
,LIEN_CONTINUATION_DATE
,TITLE_TYPE
,TITLE_ISSUER_NAME
,TITLE_ISSUER_ID
,TITLE_DATE
,TITLE_NUMBER
,REGISTRATION_NUMBER
,LOCATION
,TITLE_CUSTODIAN_NAME
,TITLE_CUSTODIAN_ID
,PAYEE_SITE
,REGISTRATION_LOCATION
,REG_EXPIRATION_DATE
,CREDIT_LINE_ID
,CREDIT_LINE_NUMBER
,PRTFL_APPROVAL_REQ
,PRTFL_ASSGN_GROUP
,PRTFL_BUDGET_AMT_OPT
,PRTFL_BUDGET_FIXED_AMT
,PRTFL_BUDGET_AMT_FORMULA
,PRTFL_DAYS_FROM_CON_EXPR
,PRTFL_STRATEGY
,PROP_TAX_APPLICABLE
,PROP_TAX_LEASE_REP
,MEX_TAX_SUB_BASIC_WTHLD
,MEX_TAX_CALC_FORMULA
,AUS_TAX_STAMP_DUTY
,CURRENCY_CONVERSION_TYPE
,CURRENCY_CONVERSION_RATE
,CURRENCY_CONVERSION_DATE
,ASSIGNABLE_YN
,LESSEE_VENDOR_ID
,LESSEE_VENDOR_NAME
,LESSEE_VENDOR_SITE_ID
,LESSEE_VENDOR_SITE_CODE
,INSURANCE_COMPANY_NUMBER
,INSURANCE_COMPANY_SITE_NUMBER
,AGENT_NUMBER
,AGENT_ADDRESS_SITE_NUMBER
,CASH_APPL_RULE_NAME
,PROP_TAX_BILL_METHOD
,GAIN_LOSS_TOLRNC_ALLWD_AMT
,GAIN_LOSS_TOLRNC_ALLWD_FRML
,GAIN_LOSS_TOLRNC_BASIS
,GAIN_LOSS_APPRV_PROC_FRML
,ERT_ABSORBED_FEE_FRML
,ERT_EXPENSE_FEE_FRML
,ERT_FINANCED_FEE_FRML
,ERT_GENERAL_FEE_FRML
,ERT_INCOME_FEE_FRML
,ERT_MISC_FEE_FRML
,ERT_PASSTHRU_FEE_FRML
,ERT_ROLLOVER_FEE_FRML
,EOT_ABSORBED_FEE_FRML
,EOT_EXPENSE_FEE_FRML
,EOT_FINANCED_FEE_FRML
,EOT_GENERAL_FEE_FRML
,EOT_INCOME_FEE_FRML
,EOT_MISC_FEE_FRML
,EOT_PASSTHRU_FEE_FRML
,EOT_ROLLOVER_FEE_FRML
,DELETE_BEFORE_IMPORT_YN -- akp_delete
,ST_UPDATE_LINES_FROM_CONTRACT
,ST_INTEREST_DISCLOSED
,ST_TRANSFER_OF_TITLE
,ST_SALE_AND_LEASE_BACK
,ST_PURCHASE_OF_LEASE
,ST_EQUIPMENT_USAGE
,ST_EQUIPMENT_AGE
,ST_ASSET_UPFRONT_TAX
,ST_BILL_STREAM_TYPE_CODE
,ST_BILL_STREAM_PURPOSE_CODE
,ST_FIN_STREAM_TYPE_CODE
,ST_FIN_STREAM_PURPOSE_CODE
,ST_CAP_STREAM_TYPE_CODE
,ST_CAP_STREAM_PURPOSE_CODE
,INT_RATE_EFFECTIVE_FROM_DATE
,INT_RATE_EFFECTIVE_TO_DATE
,INT_RATE_PRINC_BASIS_CODE
,INT_RATE_BASIS_CODE
,INT_RATE_DELAY_CODE
,INT_RATE_DELAY_FREQ
,INT_RATE_CMPND_FREQ_CODE
,INT_RATE_CATCHUP_BASIS_CODE
,INT_RATE_CATCHUP_START_DATE
,INT_RATE_CAT_STLMNT_CODE
,INT_RATE_CHANGE_START_DATE
,INT_RATE_CHANGE_FREQ_CODE
,INT_RATE_CHANGE_VALUE
,INT_RATE_CON_OPTION_CODE
,INT_RATE_NEXT_CON_DATE
,INT_RATE_CON_INDEX_NAME
,INT_RATE_CON_BASE_RATE
,INT_RATE_CON_START_DATE
,INT_RATE_CON_ADDER_RATE
,INT_RATE_CON_MAXIMUM_RATE
,INT_RATE_CON_MINIMUM_RATE
,INT_RATE_CON_PRC_BAS_CODE
,INT_RATE_CON_DAY_MTH_CODE
,INT_RATE_CON_DAY_YER_CODE
,INT_RATE_CON_INT_BAS_CODE
,INT_RATE_CON_RATE_DELAY_CODE
,INT_RATE_CON_RATE_DELAY_FREQ
,INT_RATE_CON_CMPND_FREQ_CODE
,INT_RATE_CON_FORMULA_NAME
,INT_RATE_CON_CAT_BAS_CODE
,INT_RATE_CON_CAT_START_DATE
,INT_RATE_CON_CAT_STLMNT_CODE
,INT_RATE_CON_CHG_START_DATE
,INT_RATE_CON_CHG_FREQ_CODE
,INT_RATE_CON_CHG_VALUE
,INT_RATE_CAT_FREQ_CODE
,INT_RATE_CON_CAT_FREQ_CODE
,RVI_STREAM_CODE
,RVI_STREAM_PURPOSE_CODE
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,EOT_AUTO_PROC_PURCHASE_OPT
,PTH_EVG_PAYOUT_BASIS
,PTH_PAYOUT_BASIS_FORMULA
,PTH_EVG_STREAM_CODE
,PTH_EVG_STREAM_PURPOSE_CODE
,SEND_BILL_SALE
,MANUAL_QUOTES_ONLY
,FIRST_TERMINATION_DATE
,QUOTE_RCPT_ADDL_RCPT_ROLE
,QUOTE_RCPT_ALLOC_PTG
,QUOTE_APPRVR_ADV_NOTICE_ROLE
,QUOTE_APPROVER_DELAY_DAYS
,ERT_SERV_MANT_FRML
,EOT_SERV_MANT_FRML
--Added by dpsingh for LE Uptake
,LEGAL_ENTITY_ID
,ST_TAX_SCHEDULE_APPLIES_FLAG
FROM okl_header_interface
WHERE (
batch_number = p_batch_number
OR
contract_number_old = p_contract_number
OR
(
customer_number = p_customer_number
AND
start_date between p_start_date_from AND p_start_date_to
)
)
AND nvl(scheduled_worker_id, 'NONE') = g_instance_number
AND status in ('NEW', 'ERROR') -- Bug 4350579
AND application_code = 'OKL' -- Bug 4350579
AND contract_category = 'LEASE'; -- Bug 4350579
SELECT *
FROM okl_lines_interface
WHERE contract_number_old = p_contract_number_old
AND serial_number IS NULL -- indicates TOP line
AND line_type <> 'ASSET_SUBSIDY' -- this line_type is being processed in process_subsidy()
AND line_type <> 'ASSET_ADDON' -- this line_type is being processed in process_addon()
ORDER BY
line_type,
line_number;
SELECT serial_number
FROM okl_lines_interface
WHERE contract_number_old = p_contract_number_old
AND serial_number IS NOT NULL
AND line_number = p_line_number;
SELECT *
FROM okl_party_roles_interface
WHERE contract_number_old = p_contract_number_old
AND ( (line_number = p_line_number
AND
p_line_number IS NOT NULL
)
OR
(line_number IS NULL
AND
p_line_number IS NULL
)
);
SELECT *
FROM okl_terms_interface
WHERE contract_number_old = p_contract_number_old
AND ( line_number = p_line_number
AND
p_line_number IS NOT NULL
);
SELECT *
FROM okl_payments_interface
WHERE contract_number_old = p_contract_number_old
AND payment_type_code <> 'RENT' -- Bug 5155206
AND ( (line_number = p_line_number
AND
p_line_number IS NOT NULL
)
OR
(line_number IS NULL
AND
p_line_number IS NULL
)
)
ORDER BY
contract_number_old,
line_number,
--asset_number,
payment_type_code,
payment_schedule_number,
start_date;
SELECT object1_id1
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND chr_id = dnz_chr_id
AND jtot_object1_code = 'OKX_PARTY'
AND rle_code = 'LESSEE';
SELECT sales.object1_id1
FROM okc_contacts_v sales,
okc_k_party_roles_v party
WHERE sales.cpl_id = party.id
AND party.rle_code = 'LESSOR'
AND party.chr_id = p_chr_id
AND party.dnz_chr_id = p_chr_id -- Bug# 3862650
AND sales.role = 'Salesperson';
SELECT head.id
FROM okc_k_headers_v head,
okc_governances gov
WHERE head.id = gov.chr_id_referred
AND head.scs_code = p_scs_code --'MASTER_LEASE'
AND gov.dnz_chr_id = p_chr_id;
SELECT id
FROM okl_txl_assets_b
WHERE dnz_khr_id = p_khr_id
AND kle_id = p_kle_id;
SELECT 'Y' loan_revolving
FROM okl_k_headers
WHERE deal_type = 'LOAN-REVOLVING'
AND id = p_chr_id;
SELECT asset_id
FROM FA_ADDITIONS_B
WHERE asset_number = p_asset_number;
SELECT *
FROM okl_payments_interface
WHERE contract_number_old = p_contract_number_old
AND payment_type_code = 'RENT'
AND ( (line_number = p_line_number
AND
p_line_number IS NOT NULL
)
OR
(line_number IS NULL
AND
p_line_number IS NULL
)
)
ORDER BY
contract_number_old,
line_number,
--asset_number,
payment_schedule_number,
start_date;
debug_message('Calling check_and_delete ' || to_char(sysdate,'HH24:MI:SS'));
check_and_delete(
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_contract_number => okl_header_rec.contract_number,
p_delete_before_import_yn => okl_header_rec.delete_before_import_yn -- akp_delete
);
debug_message('check_and_delete done ' || to_char(sysdate,'HH24:MI:SS'));
okl_txd_assets_pub.update_txd_asset_def(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_adpv_rec => l_adpv_rec,
x_adpv_rec => x_adpv_rec
);
debug_message('------>TXD Line Updated');
p_itiv_ib_tbl.DELETE; -- initialize table, Bug 4067179
OKL_LA_ASSET_PVT.update_contract_line(
p_api_version =>1.0
,p_init_msg_list =>OKL_API.G_FALSE
,x_return_status =>x_return_status
,x_msg_count =>x_msg_count
,x_msg_data =>x_msg_data
,p_id =>x_clev_fin_rec.id
,p_date_delivery_expected => NULL
,p_date_funding_expected => p_klev_fin_rec.date_funding_expected
,p_org_id =>NULL
,p_organization_id =>NULL
);
okl_txl_assets_pub.update_txl_asset_def(
p_api_version => 1.0,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tlpv_rec => l_talv_rec,
x_tlpv_rec => x_talv_rec
);
debug_message('Depreciation Cost update: '||x_return_status);
p_st_upd_lines_from_contract => okl_line_rec.st_update_lines_from_contract,
p_st_transfer_of_title => okl_line_rec.st_transfer_of_title,
p_st_sale_and_lease_back => okl_line_rec.st_sale_and_lease_back,
p_st_purchase_of_lease => okl_line_rec.st_purchase_of_lease,
p_st_equipment_usage => okl_line_rec.st_equipment_usage,
p_st_equipment_age => okl_line_rec.st_equipment_age,
p_st_asset_upfront_tax => okl_line_rec.st_asset_upfront_tax,
p_vat_tax_exempt => okl_line_rec.vat_tax_exempt,
p_vat_tax_exempt_cert_number => okl_line_rec.vat_tax_exempt_cert_number,
p_vat_tax_override_flag => okl_line_rec.vat_tax_override_flag,
p_vat_tax_override_rate => okl_line_rec.vat_tax_override_rate,
p_lien_type => okl_line_rec.lien_type,
p_lien_filing_number => okl_line_rec.lien_filing_number,
p_lien_filing_date => okl_line_rec.lien_filing_date,
p_lien_filing_status => okl_line_rec.lien_filing_status,
p_lienholder_id => okl_line_rec.lienholder_id,
p_lienholder_number => okl_line_rec.lienholder_number,
p_filing_jurisdiction => okl_line_rec.filing_jurisdiction,
p_filing_sub_jurisdiction => okl_line_rec.filing_sub_jurisdiction,
p_lien_expiration_date => okl_line_rec.lien_expiration_date,
p_lien_continuation_number => okl_line_rec.lien_continuation_number,
p_lien_continuation_date => okl_line_rec.lien_continuation_date,
p_title_type => okl_line_rec.title_type,
p_title_issuer_number => okl_line_rec.title_issuer_number,
p_title_issuer_id => okl_line_rec.title_issuer_id,
p_title_date => okl_line_rec.title_date,
p_title_number => okl_line_rec.title_number,
p_registration_number => okl_line_rec.registration_number,
p_asset_registration_loc_name => okl_line_rec.asset_registration_loc_name,
p_title_custodian_name => okl_line_rec.title_custodian_name,
p_payee_site_name => okl_line_rec.payee_site_name,
p_passthrough_percent => okl_line_rec.passthrough_percent,
p_passthrough_basis_code => okl_line_rec.passthrough_basis_code,
p_fee_code => okl_line_rec.fee_code,
p_stream_purpose => okl_line_rec.stream_purpose_code,
p_usage_item_name => okl_line_rec.usage_item_name,
p_usage_item_id => okl_line_rec.usage_item_id,
p_price_list_name => okl_line_rec.price_list_name,
p_price_list_id => okl_line_rec.price_list_id,
p_minimum_qty_usage => okl_line_rec.minimum_qty_usage,
p_default_qty_usage => okl_line_rec.default_qty_usage,
p_amcv_flag => okl_line_rec.amcv_flag,
p_level_flag => okl_line_rec.level_flag,
p_base_reading => okl_line_rec.base_reading,
p_base_reading_uom_code => okl_line_rec.base_reading_uom_code,
p_usage_type_code => okl_line_rec.usage_type_code,
p_fixed_usage_quantity => okl_line_rec.fixed_usage_quantity,
p_usage_period => okl_line_rec.usage_period,
p_usage_no_of_period => okl_line_rec.usage_no_of_period,
p_fee_no_of_period => okl_line_rec.fee_exp_no_of_periods,
p_fee_frequency => okl_line_rec.fee_exp_frequency,
p_fee_amount_per_period => okl_line_rec.fee_exp_amount_per_period,
p_prop_tax_applicable => okl_line_rec.prop_tax_applicable,
p_prop_tax_lease_rep => okl_line_rec.prop_tax_lease_rep,
p_french_tax_code => okl_line_rec.french_tax_code,
p_french_tax_name => okl_line_rec.french_tax_name,
-- Added by rravikir (Fix for Bug 3947959)
p_prop_tax_bill_method => okl_line_rec.prop_tax_bill_method,
p_prop_tax_as_contract_yn => okl_line_rec.prop_tax_as_contract_yn
-- End
);
p_update_type => 'IMPORT'
);
update_interface_status(
p_contract_number => okl_header_rec.contract_number_old,
p_new_contract_number => x_chrv_rec.contract_number,
p_status => 'INTERFACED',
x_return_status => x_update_status
);
update_interface_status(
p_contract_number => okl_header_rec.contract_number_old,
p_new_contract_number => okl_header_rec.contract_number,
p_status => 'ERROR',
x_return_status => x_update_status
);
select b.contract_number_old, count(*)
from okl_lines_interface b
where b.contract_number_old in (
select a.contract_number_old
from okl_header_interface a
where
(
a.batch_number = p_batch_number
OR
a.contract_number_old = p_contract_number
OR
(
a.customer_number = p_customer_number
AND
a.start_date between p_start_date_from AND p_start_date_to
)
)
AND a.status in ('NEW','ERROR')
AND a.contract_number_old = b.contract_number_old
)
group by b.contract_number_old
order by 2;
select a.contract_number_old
from okl_header_interface a
where
(
a.batch_number = p_batch_number
OR
a.contract_number_old = p_contract_number
OR
(
a.customer_number = p_customer_number
AND
a.start_date between p_start_date_from AND p_start_date_to
)
)
AND a.status in ('NEW','ERROR');
l_status_tab.delete;
update okl_header_interface a
set a.scheduled_worker_id = '1'
where
(
a.batch_number = p_batch_number
OR
a.contract_number_old = p_contract_number
OR
(
a.customer_number = p_customer_number
AND
a.start_date between p_start_date_from AND p_start_date_to
)
)
AND a.status in ('NEW','ERROR');
batch_num_tab.delete;
req_id.delete;
update okl_header_interface
set scheduled_worker_id = to_char(i)
where batch_number = l_batch_number;
select count(*) into l_header_count
from okl_header_interface b
where (
b.batch_number = p_batch_number
OR
b.contract_number_old = p_contract_number
OR
(
b.customer_number = p_customer_number
AND
b.start_date between p_start_date_from AND p_start_date_to
)
)
AND b.status in ('NEW','ERROR');
select count(*) into l_line_count
from okl_lines_interface a
where a.contract_number_old in (
select b.contract_number_old
from okl_header_interface b
where (
b.batch_number = p_batch_number
OR
b.contract_number_old = p_contract_number
OR
(
b.customer_number = p_customer_number
AND
b.start_date between p_start_date_from AND p_start_date_to
)
)
AND b.status in ('NEW','ERROR')
AND b.contract_number_old = a.contract_number_old
);
update okl_header_interface
set scheduled_worker_id = l_status_tab(i)
where contract_number_old = l_contract_num_tab(i);
write_to_log('After bulk update...');
l_contract_tab.delete;
l_contract_tab_count.delete;
l_contract_num_tab.delete;
l_count_tab.delete;
l_contract_num_tab2.delete;
l_count_tab2.delete;
update okl_header_interface set scheduled_worker_id=l_status_tab(j)
where contract_number_old = l_contract_tab(j);
l_contract_tab.delete;
l_contract_tab_count.delete;
l_status_tab.delete;
update okl_header_interface set scheduled_worker_id=l_status_tab(j)
where contract_number_old = l_contract_tab(j);
l_contract_tab.delete;
l_contract_tab_count.delete;
l_status_tab.delete;
update okl_header_interface set scheduled_worker_id=l_status_tab(j)
where contract_number_old = l_contract_tab(j);
l_contract_tab.delete;
l_contract_tab_count.delete;
l_status_tab.delete;
update okl_header_interface set scheduled_worker_id=l_status_tab(j)
where contract_number_old = l_contract_tab(j);
l_contract_tab.delete;
l_contract_tab_count.delete;
l_status_tab.delete;