[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT id
FROM OKL_TRX_TYPES_TL
WHERE name = cp_name
AND LANGUAGE = cp_language;
SELECT ID1
FROM OKX_CUST_TRX_TYPES_V
WHERE name = 'Invoice-OKL' AND
set_of_books_id = p_sob_id AND
org_id = p_org_id;
SELECT rule_information3
FROM okc_rules_b rule,
okc_rule_groups_b rgp
WHERE rgp.id = rule.rgp_id
AND rgp.dnz_chr_id = p_khr_id
AND rgd_code = 'LABILL'
AND rule_information_category = 'LAINVD';
SELECT term.printing_lead_days
FROM okc_k_headers_b khr
,hz_customer_profiles cp
,ra_terms_b term
WHERE khr.id = p_khr_id
AND khr.bill_to_site_use_id = cp.site_use_id
AND cp.standard_terms = term.term_id;
SELECT DECODE(disposition_code, 'NEGOTIATION', 'Y', 'GRANTED', 'Y', NULL, 'Y', 'N') bankruptcy_status
, disposition_code
FROM iex_bankruptcies ban
WHERE EXISTS (SELECT 1 FROM okc_k_party_roles_b rle
WHERE rle.dnz_chr_id = cp_khr_id
AND rle.rle_code = 'LESSEE'
AND TO_NUMBER(rle.object1_id1) = ban.party_id);
SELECT CR_DR_FLAG,
CODE_COMBINATION_ID,
SOURCE_ID,
AMOUNT,
PERCENTAGE,
NVL(COMMENTS,'-99') COMMENTS
FROM OKL_TRNS_ACC_DSTRS
WHERE SOURCE_ID = p_source_id AND
SOURCE_TABLE = p_source_table;
SELECT *
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
SELECT 'X' FROM
OKL_TXD_AR_LN_DTLS_B
WHERE ID = p_tld_id
AND TLD_ID_REVERSES IS NULL;
-- Variables for bulk updates
-------------------------------------
l_tai_id_cnt NUMBER := 0;
l_program_update_date DATE;
lx_last_updated_by okl_k_control.last_updated_by%TYPE := Fnd_Global.USER_ID;
lx_last_update_login okl_k_control.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
INTO
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date
FROM dual;
-- Bulk insert TAI, TIL, TLD, XSI and XLS records
--------------------------------------------------
-- --------------------------------------
-- Transfer Tai records to the Tai table
-- --------------------------------------
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_trx_ar_invoices_b');
INSERT INTO OKL_TRX_AR_INVOICES_B
VALUES tai_tbl(indx);
dist_khr_id_tbl.delete;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_trx_ar_invoices_b');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_trx_ar_invoices_tl');
INSERT INTO OKL_TRX_AR_INVOICES_TL
VALUES taitl_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_trx_ar_invoices_tl');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txl_ar_inv_lns_b');
INSERT INTO OKL_TXL_AR_INV_LNS_B
VALUES til_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txl_ar_inv_lns_b');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txl_ar_inv_lns_tl');
INSERT INTO OKL_TXL_AR_INV_LNS_TL
VALUES tiltl_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txl_ar_inv_lns_tl');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txd_ar_ln_dtls_b');
INSERT INTO OKL_TXD_AR_LN_DTLS_B
VALUES tld_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txd_ar_ln_dtls_b');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_txd_ar_ln_dtls_tl');
INSERT INTO OKL_TXD_AR_LN_DTLS_TL
VALUES tldtl_tbl(indx);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Done Inserting into okl_txd_ar_ln_dtls_tl');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Inserting into okl_ext_sell_invs_b');
l_acc_gen_tbl.DELETE;
l_ctxt_tbl.DELETE;
l_tmpl_identify_tbl.DELETE;
l_dist_info_tbl.DELETE;
-- for bulk updates
-----------------------------------------------------
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'all_rec_tbl.count : ' || all_rec_tbl.COUNT);
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'SUBMITTED' -- 'PROCESSED' -- rmunjulu R12 Fixes changed to submitted IS THIS CORRECT
WHERE id = tai_id_tbl(indx);
UPDATE okl_strm_elements
SET date_billed = SYSDATE
WHERE id = sel_id_tbl(indx);
UPDATE okl_k_control nbd
set EARLIEST_STRM_BILL_DATE = (
SELECT MIN(ste.stream_element_date)
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN', 'INVESTOR')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED', 'ACTIVE') -- bug 6472228 added EXPIRED status
AND khr.id = nbd.khr_id
AND khl.id = stm.khr_id
AND (khl.deal_type IS NOT NULL OR khr.sts_code = 'ACTIVE')
AND khs.code = khr.sts_code
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') ), -- bug 6472228 added EXPIRED status
last_update_date = sysdate,
last_updated_by = lx_last_updated_by,
last_update_login = lx_last_update_login,
request_id = lx_request_id,
program_update_date = sysdate,
program_id = lx_program_id
where nbd.khr_id = dist_khr_id_tbl(indx);
dist_khr_id_tbl.delete;
tai_tbl.DELETE;
til_tbl.DELETE;
tld_tbl.DELETE;
taitl_tbl.DELETE;
tiltl_tbl.DELETE;
tldtl_tbl.DELETE;
tai_id_tbl.DELETE;
sel_id_tbl.DELETE;
all_rec_tbl.DELETE;
SELECT OKL_TXD_AR_LN_DTLS_B_S.NEXTVAL
FROM dual;
SELECT ID1
FROM OKX_CUST_TRX_TYPES_V
WHERE name = 'Invoice-OKL' AND
set_of_books_id = p_sob_id AND
org_id = p_org_id;
SELECT ID1
FROM OKX_CUST_TRX_TYPES_V
WHERE name = 'Credit Memo-OKL' AND
set_of_books_id = p_sob_id AND
org_id = p_org_id;
-- Declare records: i - insert, u - update, r - result
------------------------------------------------------------
-- Transaction headers
i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
SELECT cust_acct_id
FROM okc_k_headers_v
WHERE id = p_khr_id;
SELECT cs.cust_acct_site_id
, cp.standard_terms payment_term_id
FROM okc_k_headers_v khr
, okx_cust_site_uses_v cs
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND khr.bill_to_site_use_id = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+);
SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
FROM okc_k_headers_b khr
, okx_cust_site_uses_v cs
, okc_k_lines_b cle
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle.chr_id IS NOT NULL
AND cle.id = p_kle_id
AND cle.BILL_TO_SITE_USE_ID = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+)
UNION
SELECT cs.cust_acct_site_id, cp.standard_terms payment_term_id
FROM okc_k_headers_b khr
, okc_k_lines_b cle
, okc_k_items item
, okc_k_lines_b linked_asset
, okx_cust_site_uses_v cs
, hz_customer_profiles cp
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle.id = p_kle_id
AND cle.chr_id IS NULL
AND cle.id = item.cle_id
AND item.object1_id1 = linked_asset.id
AND linked_asset.BILL_TO_SITE_USE_ID = cs.id1
AND khr.bill_to_site_use_id = cp.site_use_id(+);
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_khr_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.cle_id = p_kle_id AND
rul.rule_information_category = 'LAPMTH' AND
rgp.dnz_chr_id = p_khr_id
UNION
SELECT rul.object1_id1
FROM okc_k_lines_b cle
, okc_k_items_v item
, okc_k_lines_b linked_asset
, OKC_RULES_B rul
, Okc_rule_groups_B rgp
WHERE cle.dnz_chr_id = p_khr_id AND
cle.id = p_kle_id AND
cle.chr_id IS NULL AND
cle.id = item.cle_id AND
item.object1_id1 = linked_asset.id AND
linked_asset.id = rgp.cle_id AND
linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
rgp.rgd_code = 'LABILL' AND
rul.rgp_id = rgp.id AND
rul.rule_information_category = 'LAPMTH';
SELECT C.RECEIPT_METHOD_ID
FROM RA_CUST_RECEIPT_METHODS C
WHERE C.cust_receipt_method_id = p_cust_rct_mthd;
SELECT C.CREATION_METHOD_CODE
FROM AR_RECEIPT_METHODS M,
AR_RECEIPT_CLASSES C
WHERE M.RECEIPT_CLASS_ID = C.RECEIPT_CLASS_ID AND
M.receipt_method_id = p_rct_method_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.rgd_code = 'LABILL' AND
rgp.dnz_chr_id = rgp.chr_id AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = p_khr_id;
SELECT object1_id1
FROM OKC_RULES_B rul,
Okc_rule_groups_B rgp
WHERE rul.rgp_id = rgp.id AND
rgp.cle_id = p_kle_id AND
rgp.rgd_code = 'LABILL' AND
rul.rule_information_category = 'LABACC' AND
rgp.dnz_chr_id = p_khr_id
UNION
SELECT rul.object1_id1
FROM okc_k_lines_b cle
, okc_k_items_v item
, okc_k_lines_b linked_asset
, OKC_RULES_B rul
, Okc_rule_groups_B rgp
WHERE cle.dnz_chr_id = p_khr_id AND
cle.id = p_kle_id AND
cle.chr_id IS NULL AND
cle.id = item.cle_id AND
item.object1_id1 = linked_asset.id AND
linked_asset.id = rgp.cle_id AND
linked_asset.dnz_chr_id = rgp.dnz_chr_id AND
rgp.rgd_code = 'LABILL' AND
rul.rgp_id = rgp.id AND
rul.rule_information_category = 'LABACC';
SELECT bank_account_id
FROM OKX_RCPT_METHOD_ACCOUNTS_V
WHERE id1 = p_id1;
SELECT currency_code
,currency_conversion_type
,currency_conversion_rate
,currency_conversion_date
FROM okl_k_headers_full_v
WHERE id = cp_khr_id;
SELECT B.term_id
FROM ra_terms_b b, ra_terms_tl t
WHERE t.name = 'IMMEDIATE'
and B.TERM_ID = T.TERM_ID
and T.LANGUAGE = userenv('LANG');
SELECT pdt_id
FROM okl_k_headers_full_v
WHERE id = p_khr_id;
SELECT
C.CODE_COMBINATION_ID,
C.AE_LINE_TYPE,
C.CRD_CODE,
C.ACCOUNT_BUILDER_YN,
C.PERCENTAGE
FROM OKL_AE_TEMPLATES A,
OKL_PRODUCTS_V B,
OKL_AE_TMPT_LNES C
WHERE A.aes_id = b.aes_id AND
A.start_date <= p_date AND
(A.end_date IS NULL OR A.end_date >= p_date) AND
A.memo_yn = 'N' AND
b.id = p_pdt_id AND
a.sty_id = p_sty_id AND
a.try_id = p_try_id AND
C.avl_id = A.id;
SELECT inf.id,
rul.rule_information4 review_invoice_yn
FROM okc_rule_groups_v rgp,
okc_rules_v rul,
okl_invoice_formats_v inf
WHERE rgp.dnz_chr_id = cp_khr_id AND
rgp.chr_id = rgp.dnz_chr_id AND
rgp.id = rul.rgp_id AND
rgp.cle_id IS NULL AND
rgp.rgd_code = 'LABILL' AND
rul.rule_information_category = 'LAINVD' AND
rul.rule_information1 = inf.name;
SELECT *
FROM FND_LANGUAGES
WHERE INSTALLED_FLAG IN ('I', 'B');
l_program_update_date DATE;
SELECT Rule_information1 PRIVATE_LABEL
FROM okc_rule_groups_b A,
okc_rules_b B
WHERE A.DNZ_CHR_ID = CP_KHR_ID
AND A.rgd_code = 'LALABL'
AND A.id = B.rgp_id
AND B.rule_information_category = 'LALOGO';
SELECT nvl(inv_organization_id,-99)
FROM okc_k_headers_b
WHERE id = p_khr_id;
SELECT 1
FROM okc_k_headers_all_b
WHERE id = p_khr_id
AND scs_code = 'INVESTOR';
SELECT cle_inv.cust_acct_id, cs.cust_acct_site_id, hz.party_name
FROM okc_k_headers_b khr
, okx_cust_site_uses_v cs
, okc_k_lines_b cle
, okc_k_lines_b cle_inv
, okc_k_party_roles_b cpl
, okc_k_party_roles_b cpl_inv
, hz_parties hz
WHERE khr.id = p_khr_id
AND cle.dnz_chr_id = khr.id
AND cle_inv.dnz_chr_id = khr.id
AND cpl.rle_code = 'INVESTOR'
AND cpl_inv.rle_code = 'INVESTOR'
and cpl.cle_id = cle.id
AND cpl.object1_id1 = cpl_inv.object1_id1
AND cpl_inv.cle_id = cle_inv.id
AND cle_inv.lse_id = 65
AND cle.chr_id IS NOT NULL
AND cle_inv.chr_id IS NOT NULL
AND cle.id = p_kle_id
AND cle_inv.BILL_TO_SITE_USE_ID = cs.id1
and hz.party_id = cpl_inv.object1_id1;
-- If all records are processd, do bulk insert and update
------------------------------------------------------------
IF p_end_of_records = 'Y' THEN
Fnd_File.PUT_LINE (Fnd_File.LOG, ' Done building TAI, TIL, TLD, XSI and XLS records ...');
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
INTO
l_request_id,
l_program_application_id,
l_program_id,
l_program_update_date
FROM dual;
tai_tbl(l_tai_cnt).LAST_UPDATE_DATE := SYSDATE;
tai_tbl(l_tai_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
tai_tbl(l_tai_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
tai_tbl(l_tai_cnt).program_update_date := l_program_update_date;
taitl_tbl(l_taitl_cnt).LAST_UPDATE_DATE := SYSDATE;
taitl_tbl(l_taitl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
taitl_tbl(l_taitl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
til_tbl(l_til_cnt).LAST_UPDATE_DATE := SYSDATE;
til_tbl(l_til_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
til_tbl(l_til_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
til_tbl(l_til_cnt).program_update_date := l_program_update_date;
tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_DATE := SYSDATE;
tiltl_tbl(l_tiltl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
tld_tbl(l_tld_cnt).LAST_UPDATE_DATE := SYSDATE;
tld_tbl(l_tld_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
tld_tbl(l_tld_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
tld_tbl(l_tld_cnt).program_update_date := l_program_update_date;
tldtl_tbl(l_tldtl_cnt).LAST_UPDATE_DATE := SYSDATE;
tldtl_tbl(l_tldtl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
tldtl_tbl(l_tldtl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
AND khr.contract_number = p_contract_number
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
-- AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED' , 'EXPIRED') -- bug 6472228 added EXPIRED status
AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
AND ( p_source <> 'PRINCIPAL_PAYDOWN' OR (p_source = 'PRINCIPAL_PAYDOWN'
AND sty.stream_type_purpose
in ('UNSCHEDULED_PRINCIPAL_PAYMENT','UNSCHEDULED_LOAN_PAYMENT')
)
)
-- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
UNION
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
okc_k_lines_b cle
WHERE TRUNC(ste.stream_element_date) >= TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <= TRUNC((NVL (p_to_bill_date, SYSDATE) + nvl(get_printing_lead_days(stm.khr_id), 0)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code = 'INVESTOR'
AND khr.sts_code = ( 'ACTIVE') -- bug 6472228 added EXPIRED status
AND khr.contract_number = p_contract_number
AND nvl(p_ia_contract_type,L_IA_TYPE) = L_IA_TYPE
AND cle.dnz_chr_id = khr.id
AND (p_inv_cust_acct_id IS NULL OR (p_inv_cust_acct_id IS NOT NULL AND (cle.cust_acct_id IS NOT NULL and cle.cust_acct_id = p_inv_cust_acct_id)))
AND khl.id = stm.khr_id
AND khs.code = khr.sts_code
AND kle.id = stm.kle_id
AND kls.code = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED' , 'EXPIRED') -- bug 6472228 added EXPIRED status
AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khr.contract_number = p_contract_number
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
, okc_rules_b rul
WHERE rgp.dnz_chr_id = kle.dnz_chr_id
AND rgp.cle_id = kle.id
AND rgp.rgd_code = 'LAASTX'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPRTX'
AND rul.rule_information1 = 'Y'
AND (rul.rule_information3 = 'ACTUAL')
)
AND sty.stream_type_purpose = 'ACTUAL_PROPERTY_TAX'
AND (p_source <> 'PRINCIPAL_PAYDOWN')
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khr.contract_number = p_contract_number
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
, okc_rules_b rul
WHERE rgp.dnz_chr_id = kle.dnz_chr_id
AND rgp.cle_id = kle.id
AND rgp.rgd_code = 'LAASTX'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPRTX'
AND rul.rule_information1 = 'Y'
AND (rul.rule_information3 = 'ESTIMATED' OR rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
)
AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
AND (p_source <> 'PRINCIPAL_PAYDOWN')
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
-- AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
-- AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
AND ( p_source <> 'PRINCIPAL_PAYDOWN' OR (p_source = 'PRINCIPAL_PAYDOWN'
AND sty.stream_type_purpose
= 'UNSCHEDULED_PRINCIPAL_PAYMENT'
)
)
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
UNION
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
okc_k_lines_b cle
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <= TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code = 'INVESTOR'
AND khr.sts_code = 'ACTIVE'
AND nvl(p_ia_contract_type,L_IA_TYPE) = L_IA_TYPE
AND cle.dnz_chr_id = khr.id
AND (p_inv_cust_acct_id IS NULL OR (p_inv_cust_acct_id IS NOT NULL AND (cle.cust_acct_id IS NOT NULL and cle.cust_acct_id = p_inv_cust_acct_id)))
AND khl.id = stm.khr_id
AND khs.code = khr.sts_code
-- AND khs.ste_code = 'ACTIVE'
AND kle.id = stm.kle_id
AND kls.code = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
-- AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
, okc_rules_b rul
WHERE rgp.dnz_chr_id = kle.dnz_chr_id
AND rgp.cle_id = kle.id
AND rgp.rgd_code = 'LAASTX'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPRTX'
AND rul.rule_information1 = 'Y'
AND (rul.rule_information3 = 'ACTUAL')
)
AND sty.stream_type_purpose = 'ACTUAL_PROPERTY_TAX'
AND (p_source <> 'PRINCIPAL_PAYDOWN')
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
-- AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
, okc_rules_b rul
WHERE rgp.dnz_chr_id = kle.dnz_chr_id
AND rgp.cle_id = kle.id
AND rgp.rgd_code = 'LAASTX'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPRTX'
AND rul.rule_information1 = 'Y'
AND (rul.rule_information3 = 'ESTIMATED' OR rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
)
AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
AND (p_source <> 'PRINCIPAL_PAYDOWN')
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
OKL_PARALLEL_PROCESSES pws
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
AND ( p_source <> 'PRINCIPAL_PAYDOWN' OR (p_source = 'PRINCIPAL_PAYDOWN'
AND sty.stream_type_purpose
= 'UNSCHEDULED_PRINCIPAL_PAYMENT'
)
)
AND pws.object_type = 'CONTRACT'
AND pws.object_value = khr.contract_number
AND pws.assigned_process = p_assigned_process
-- modified by zrehman for Bug#6788005 on 04-Feb-2008 start
UNION
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
okc_k_lines_b cle,
OKL_PARALLEL_PROCESSES pws
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code = 'INVESTOR'
AND khr.sts_code = 'ACTIVE' -- bug 6472228 added EXPIRED status
AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND nvl(p_ia_contract_type,L_IA_TYPE) = L_IA_TYPE
AND cle.dnz_chr_id = khr.id
AND (p_inv_cust_acct_id IS NULL OR (p_inv_cust_acct_id IS NOT NULL AND (cle.cust_acct_id IS NOT NULL and cle.cust_acct_id = p_inv_cust_acct_id)))
AND khl.id = stm.khr_id
AND khs.code = khr.sts_code
AND kle.id = stm.kle_id
AND kls.code = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED', 'EXPIRED') -- bug 6472228 added EXPIRED status
AND sty.stream_type_purpose NOT IN ('ACTUAL_PROPERTY_TAX', 'ESTIMATED_PROPERTY_TAX')
AND pws.object_type = 'CONTRACT'
AND pws.object_value = khr.contract_number
AND pws.assigned_process = p_assigned_process
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
OKL_PARALLEL_PROCESSES pws
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
, okc_rules_b rul
WHERE rgp.dnz_chr_id = kle.dnz_chr_id
AND rgp.cle_id = kle.id
AND rgp.rgd_code = 'LAASTX'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPRTX'
AND rul.rule_information1 = 'Y'
AND (rul.rule_information3 = 'ACTUAL')
)
AND sty.stream_type_purpose = 'ACTUAL_PROPERTY_TAX'
AND (p_source <> 'PRINCIPAL_PAYDOWN')
AND pws.object_type = 'CONTRACT'
AND pws.object_value = khr.contract_number
AND pws.assigned_process = p_assigned_process
ORDER BY 1, 2, 3;
SELECT stm.khr_id khr_id,
TRUNC (ste.stream_element_date) bill_date,
stm.kle_id kle_id,
ste.id sel_id,
stm.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.name comments,
sty.taxable_default_yn taxable_default_yn,
ste.amount amount,
khr.sts_code sts_code
FROM OKL_STRM_ELEMENTS ste,
OKL_STREAMS stm,
okl_strm_type_v sty,
okc_k_headers_b khr,
OKL_K_HEADERS khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
OKL_PARALLEL_PROCESSES pws
WHERE TRUNC(ste.stream_element_date) >=
TRUNC(NVL (p_from_bill_date, ste.stream_element_date))
AND TRUNC(ste.stream_element_date) <=
TRUNC((NVL (p_to_bill_date, SYSDATE) + get_printing_lead_days(stm.khr_id)))
AND ste.amount <> 0
AND stm.id = ste.stm_id
AND ste.date_billed IS NULL
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND khr.contract_number = NVL (p_contract_number, khr.contract_number)
AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
AND khr.cust_acct_id = NVL( p_cust_acct_id, khr.cust_acct_id )
AND khl.id = stm.khr_id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kle.id (+) = stm.kle_id
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND EXISTS (SELECT 1 FROM okc_rule_groups_b rgp
, okc_rules_b rul
WHERE rgp.dnz_chr_id = kle.dnz_chr_id
AND rgp.cle_id = kle.id
AND rgp.rgd_code = 'LAASTX'
AND rgp.id = rul.rgp_id
AND rul.rule_information_category = 'LAPRTX'
AND rul.rule_information1 = 'Y'
AND (rul.rule_information3 = 'ESTIMATED' OR rul.rule_information3 = 'ESTIMATED_AND_ACTUAL')
)
AND sty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
AND (p_source <> 'PRINCIPAL_PAYDOWN')
AND pws.object_type = 'CONTRACT'
AND pws.object_value = khr.contract_number
AND pws.assigned_process = p_assigned_process
ORDER BY 1, 2, 3;
SELECT COUNT(*)
FROM okl_trx_ar_invoices_v a,
okl_txl_ar_inv_lns_v b,
okl_txd_ar_ln_dtls_v c
WHERE a.id = b.tai_id AND
b.id = c.til_id_details AND
a.trx_status_code = p_sts AND
a.request_id = p_req_id ;
SELECT COUNT(*)
FROM okl_trx_ar_invoices_v a,
okl_txl_ar_inv_lns_v b,
okl_txd_ar_ln_dtls_v c
WHERE a.id = b.tai_id AND
b.id = c.til_id_details AND
a.trx_status_code = p_sts AND
a.request_id = p_req_id ;
SELECT NAME
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
bill_tbl.DELETE;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
DELETE OKL_PARALLEL_PROCESSES
WHERE 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;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;