[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 distinct KHR.ID
FROM OKC_K_HEADERS_B KHR,
OKL_STREAMS STM,
OKL_STRM_TYPE_B STY,
OKL_STRM_ELEMENTS SEL
WHERE KHR.contract_number = NVL(p_contract_number, contract_number)
AND KHR.STS_CODE = 'BOOKED'
AND KHR.ID = STM.KHR_ID
AND STM.sty_id = STY.id
AND STY.STREAM_TYPE_PURPOSE = 'REBOOK_BILLING_ADJUSTMENT'
AND STM.active_yn = 'Y'
AND sel.stm_id = stm.id
AND SEL.DATE_BILLED IS NULL;
/* SELECT id */
/* FROM okc_k_headers_b */
/* WHERE contract_number = NVL(p_contract_number, contract_number)
AND */
/* sts_code = 'BOOKED' AND */
/* id in ( */
/* SELECT rebook.id */
/* FROM okc_k_headers_b orig, */
/* okc_k_headers_b rebook */
/* WHERE orig.contract_number = NVL(p_contract_number,
orig.contract_number) and */
/* orig.authoring_org_id =
NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
/* rebook.orig_system_id1 = orig.id and */
/* rebook.orig_system_source_code = 'OKL_REBOOK' */
/* UNION */
/* SELECT mass_rebook.id */
/* FROM okc_k_headers_b mass_rebook, */
/* okl_rbk_selected_contract rbk */
/* WHERE mass_rebook.contract_number = NVL(p_contract_number,
mass_rebook.contract_number) and */
/* mass_rebook.authoring_org_id =
NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
/* rbk.khr_id = mass_rebook.id) */
/* order by 1; */
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 stream_name,
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_b 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.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT'
-- AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code = 'BOOKED'
AND khr.id = p_khr_id
-- AND khr.contract_number =
-- NVL (NULL, khr.contract_number)
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
-- Bug 3816891 adjust terminated asset lines
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
ORDER BY 1, 2, 3;
SELECT
tai.date_entered date_billed,
khr.id khr_id,
TRUNC (tai.date_invoiced) bill_date,
tld.kle_id kle_id,
tld.sel_id sel_id,
tld.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.code comments,
sty.taxable_default_yn taxable_default_yn,
OKL_BILLING_UTIL_PVT.INV_LN_AMT_ORIG_WOTAX
(tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount,
khr.sts_code sts_code,
tld.id tld_id,
OKL_BILLING_UTIL_PVT.INV_LN_AMT_REMAINING_W_INCTAX
(tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount_due_remaining,
PS.trx_number trx_number,
PS.class,
PS.TERMS_SEQUENCE_NUMBER
FROM
okl_strm_type_b sty,
okc_k_headers_b khr,
okl_k_headers khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
okl_txd_ar_ln_dtls_v tld,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okl_bpd_tld_ar_lines_v tldv,
AR_PAYMENT_SCHEDULES_ALL PS
WHERE sty.billable_yn = 'Y'
AND tld.sty_id = sty.id
AND khr.id = p_khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND tld.kle_id = p_kle_id
AND tld.kle_id = kle.id
AND khl.id = khr.id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND tai.trx_status_code = 'PROCESSED'
AND tai.id = til.tai_id
AND til.id = tld.til_id_details
AND tldv.tld_id = tld.id
AND tldv.khr_id = khr.id
AND tldv.customer_trx_id = ps.customer_trx_id
AND tldv.customer_trx_id IS NOT NULL
AND PS.TERMS_SEQUENCE_NUMBER = 1
AND PS.amount_due_remaining > 0
ORDER BY 1, 2, 3;
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; */
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = err_tbl(i).tai_id;
UPDATE okl_strm_elements
SET date_billed = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID --Bug: 14742784 changes start here
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID -- Bug: 14742784 changes end here
WHERE id = adj_streams_rec.sel_id;
okl_credit_memo_pub.insert_request(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_tld_id => unpaid_invs_rec.tld_id,
p_credit_amount => (-1*l_credit_amount),
p_credit_sty_id => NULL,
p_credit_desc => 'Rebook Adjustment Credit Memo',
p_credit_date => l_credit_date,
p_try_id => NULL,
p_transaction_source=>'REBOOK', -- bug 6328168
x_tai_id => lx_tai_id,
x_taiv_rec => r_taiv_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = err_tbl(i).tai_id;
UPDATE okl_strm_elements
SET date_billed = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID -- BUG: 14742784 changes start here
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID --BUG:14742784 changes end here
WHERE id = adj_streams_rec.sel_id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = err_tbl(i).tai_id;
UPDATE okl_strm_elements
SET date_billed = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID -- BUG: 14742784 changes start here
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14742784 changes end here
WHERE id = adj_streams_rec.sel_id;
SELECT distinct KHR.ID
FROM OKC_K_HEADERS_B KHR,
OKL_STREAMS STM,
OKL_STRM_TYPE_B STY,
OKL_STRM_ELEMENTS SEL
WHERE KHR.contract_number = NVL(p_contract_number, contract_number)
AND KHR.STS_CODE = 'BOOKED'
AND KHR.ID = STM.KHR_ID
AND STM.sty_id = STY.id
AND STY.STREAM_TYPE_PURPOSE = 'REBOOK_BILLING_ADJUSTMENT'
AND STM.active_yn = 'Y'
AND sel.stm_id = stm.id
AND SEL.DATE_BILLED IS NULL;
/* SELECT id */
/* FROM okc_k_headers_b */
/* WHERE contract_number = NVL(p_contract_number, contract_number)
AND */
/* sts_code = 'BOOKED' AND */
/* id in ( */
/* SELECT rebook.id */
/* FROM okc_k_headers_b orig, */
/* okc_k_headers_b rebook */
/* WHERE orig.contract_number = NVL(p_contract_number,
orig.contract_number) and */
/* orig.authoring_org_id =
NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
/* rebook.orig_system_id1 = orig.id and */
/* rebook.orig_system_source_code = 'OKL_REBOOK' */
/* UNION */
/* SELECT mass_rebook.id */
/* FROM okc_k_headers_b mass_rebook, */
/* okl_rbk_selected_contract rbk */
/* WHERE mass_rebook.contract_number = NVL(p_contract_number,
mass_rebook.contract_number) and */
/* mass_rebook.authoring_org_id =
NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
/* rbk.khr_id = mass_rebook.id) */
/* order by 1; */
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 stream_name,
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_b 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.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT'
-- AND sty.billable_yn = 'Y'
AND khr.id = stm.khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code = 'BOOKED'
AND khr.id = p_khr_id
-- AND khr.contract_number =
-- NVL (NULL, khr.contract_number)
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')
ORDER BY 1, 2, 3;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
UPDATE okl_strm_elements
SET date_billed = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID -- BUG:14742784 changes start here
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID -- BUG:14742784 changes end here
WHERE id = adj_streams_rec.sel_id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
UPDATE okl_strm_elements
SET date_billed = SYSDATE
,last_updated_by = FND_GLOBAL.USER_ID -- BUG:14742784 changes start here
,last_update_date = sysdate
,last_update_login = FND_GLOBAL.LOGIN_ID --BUG:14742784 changes end here
WHERE id = adj_streams_rec.sel_id;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
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 = 'SUBMITTED' 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 = 'ERROR' AND
a.request_id = p_req_id ;
SELECT
tai.date_entered date_billed,
khr.id khr_id,
TRUNC (tai.date_invoiced) bill_date,
tld.kle_id kle_id,
tld.sel_id sel_id,
tld.sty_id sty_id,
khr.contract_number contract_number,
khr.currency_code currency_code,
khr.authoring_org_id authoring_org_id,
sty.code comments,
sty.taxable_default_yn taxable_default_yn,
OKL_BILLING_UTIL_PVT.INV_LN_AMT_ORIG_WOTAX
(tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount,
khr.sts_code sts_code,
tld.id tld_id,
OKL_BILLING_UTIL_PVT.INV_LN_AMT_REMAINING_W_INCTAX
(tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount_due_remaining,
PS.trx_number trx_number,
PS.class,
PS.TERMS_SEQUENCE_NUMBER
FROM
okl_strm_type_b sty,
okc_k_headers_b khr,
okl_k_headers khl,
okc_k_lines_b kle,
okc_statuses_b khs,
okc_statuses_b kls,
okl_txd_ar_ln_dtls_v tld,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okl_bpd_tld_ar_lines_v tldv,
AR_PAYMENT_SCHEDULES_ALL PS
WHERE sty.billable_yn = 'Y'
AND tld.sty_id = p_sty_id
AND tld.sty_id = sty.id
AND khr.id = p_khr_id
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
AND tld.kle_id = p_kle_id
AND tld.kle_id = kle.id
AND khl.id = khr.id
AND khl.deal_type IS NOT NULL
AND khs.code = khr.sts_code
AND khs.ste_code = 'ACTIVE'
AND kls.code (+) = kle.sts_code
AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
AND tai.trx_status_code = 'PROCESSED'
AND tai.id = til.tai_id
AND til.id = tld.til_id_details
AND tldv.tld_id = tld.id
AND tldv.khr_id = khr.id
AND tldv.customer_trx_id = ps.customer_trx_id
AND tldv.customer_trx_id IS NOT NULL
AND PS.TERMS_SEQUENCE_NUMBER = 1
AND PS.amount_due_remaining > 0
ORDER BY 1, 2, 3;
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; */
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = err_tbl(j).tai_id;
okl_credit_memo_pub.insert_request(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_tld_id => unpaid_invs_rec.tld_id,
p_credit_amount => (-1*l_credit_amount),
p_credit_sty_id => NULL,
p_credit_desc => 'Rebook Adjustment Credit Memo',
p_credit_date => l_credit_date,
p_try_id => NULL,
p_transaction_source=>'REBOOK', -- Bug 6328168
x_tai_id => lx_tai_id,
x_taiv_rec => r_taiv_rec,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = err_tbl(j).tai_id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = err_tbl(j).tai_id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;
UPDATE okl_trx_ar_invoices_b
SET trx_status_code = 'ERROR'
WHERE id = lx_taiv_rec.id;