[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ID INTO RULES
FROM OKL_CASH_ALLCTN_RLS_ALL
WHERE DEFAULT_RULE = 'YES'
AND SYSDATE BETWEEN START_DATE AND NVL(END_DATE,SYSDATE)
AND org_id = p_org_id;
SELECT lpt.stream_type_id
,lpt.amount_due_remaining
,lpt.currency_code
,lpt.receivables_invoice_number
,lpt.stream_id
,lpt.trx_date
FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
WHERE lpt.consolidated_invoice_number = cp_cons_bill_num
AND NVL (lpt.contract_number, 999) = NVL (DECODE (cp_contract_num, NULL, lpt.contract_number, cp_contract_num), 999)
AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number)
AND lpt.stream_type_id = NVL (cp_stream_type_id, lpt.stream_type_id)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0;
SELECT lpt.stream_type_id
,lpt.amount_due_remaining
,lpt.currency_code
,lpt.receivables_invoice_number
,lpt.stream_id
,lpt.trx_date
FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
WHERE lpt.consolidated_invoice_number = NVL (cp_cons_bill_num, lpt.consolidated_invoice_number)
AND lpt.contract_number = cp_contract_num
AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number)
AND lpt.stream_type_id = NVL (cp_stream_type_id, lpt.stream_type_id)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0;
SELECT DISTINCT(lpt.contract_number)
,lpt.start_date, lpt.contract_id
FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
WHERE lpt.consolidated_invoice_number = cp_cons_bill_num -- for performance issue #5484903
--NVL (cp_cons_bill_num, lpt.consolidated_invoice_number)
AND lpt.customer_number = NVL (cp_customer_num, lpt.customer_number);
SELECT distinct sta.sty_id, sta.sequence_number
FROM OKL_STRM_TYP_ALLOCS sta, OKL_CNSLD_AR_STRMS_B st
WHERE sta.stream_allc_type = cp_str_all_type
AND sta.cat_id = cp_cat_id
AND st.khr_id = cp_khr_id
AND sta.sty_id = st.sty_id
ORDER BY sta.sequence_number;
SELECT conversion_rate, inverse_conversion_rate
FROM GL_DAILY_RATES_V
WHERE conversion_type = 'Corporate'
AND status_code = 'C'
AND from_currency = cp_from_currency
AND to_currency = cp_to_currency
ORDER BY conversion_date DESC;
select distinct(ST.KHR_ID) CONTRACT_ID ,
CN.CONTRACT_NUMBER CONTRACT_NUMBER ,
CN.START_DATE START_DATE
from
OKC_K_HEADERS_ALL_B CN,
OKL_CNSLD_AR_STRMS_B ST,
OKL_CNSLD_AR_HDRS_B HD,
HZ_CUST_ACCOUNTS CA,
OKL_CNSLD_AR_LINES_B LN,
AR_PAYMENT_SCHEDULES_ALL PS
WHERE PS.CLASS IN ('INV', 'CM')
AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID AND LN.ID = ST.LLN_ID
AND HD.ID = LN.CNR_ID AND CA.CUST_ACCOUNT_ID = HD.IXX_ID
AND CN.ID = ST.KHR_ID
and HD.CONSOLIDATED_INVOICE_NUMBER = cp_cons_bill_num
and ps.status = 'OP'
and ps.amount_due_remaining > 0
ORDER BY CN.start_date; -- fixed for performance bug#5484903 - varangan- 19-9-06
SELECT distinct (lpt.contract_id), lpt.contract_number, lpt.start_date
FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
WHERE lpt.consolidated_invoice_number = cp_cons_bill_num
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0*/
----------
-- get a contract id if not known
CURSOR c_get_contract_id (cp_contract_num IN VARCHAR2) IS
select ST.KHR_ID CONTRACT_ID
from
OKC_K_HEADERS_ALL_B CN,
OKL_CNSLD_AR_STRMS_B ST,
AR_PAYMENT_SCHEDULES_ALL PS
WHERE PS.CLASS IN ('INV', 'CM')
AND ST.RECEIVABLES_INVOICE_ID = PS.CUSTOMER_TRX_ID
AND CN.ID = ST.KHR_ID
and CN.CONTRACT_NUMBER = cp_contract_num
and ps.status = 'OP'
and ps.amount_due_remaining > 0
ORDER BY CN.start_date; -- fixed for performance bug#5484903 - varangan- 19-9-06
SELECT lpt.contract_id
FROM OKL_BPD_LEASING_PAYMENT_TRX_V lpt
WHERE lpt.contract_number = cp_contract_num
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
ORDER BY lpt.start_date; */
SELECT authoring_org_id
FROM OKC_K_HEADERS_B
WHERE contract_number = cp_contract_num;
SELECT '1'
FROM OKL_TRX_CSH_RECEIPT_V
WHERE ile_id = cp_customer_id
AND check_number = cp_check_num
-- AND amount = cp_amount
AND TRUNC(date_effective) = TRUNC(cp_receipt_date);
SELECT a.id, b.id
FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b
WHERE a.id = b.rct_id_details
AND a.ile_id = cp_customer_id
AND a.check_number = cp_check_num
AND a.amount = cp_amount
AND b.cnr_id = NVL(cp_cons_bill_id, NULL);
SELECT a.id, b.id
FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b
WHERE a.id = b.rct_id_details
AND a.ile_id = cp_customer_id
AND a.check_number = cp_check_num
AND a.amount = cp_amount
AND b.khr_id = NVL(cp_contract_id, NULL);
SELECT bank_name, bank_account_num
FROM OKL_BPD_RCPT_MTHDS_UV
WHERE receipt_method_id = cp_irm_id;
SELECT to_number(a.object1_id1)
FROM OKC_RULES_B a, OKC_RULE_GROUPS_B b
WHERE a.rgp_id = b.id
AND b.rgd_code = 'LABILL'
AND a.rule_information_category = 'LAINVD'
AND a.dnz_chr_id = b.chr_id
AND a.dnz_chr_id = cp_khr_id;
SELECT ID
,NAME
,AMOUNT_TOLERANCE_PERCENT
,DAYS_PAST_QUOTE_VALID_TOLERANC
,MONTHS_TO_BILL_AHEAD
,UNDER_PAYMENT_ALLOCATION_CODE
,OVER_PAYMENT_ALLOCATION_CODE
,RECEIPT_MSMTCH_ALLOCATION_CODE
FROM OKL_CASH_ALLCTN_RLS
WHERE CAU_ID = cp_cau_id
AND START_DATE <= trunc(SYSDATE)
AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
SELECT ID
,NAME
,AMOUNT_TOLERANCE_PERCENT
,DAYS_PAST_QUOTE_VALID_TOLERANC
,MONTHS_TO_BILL_AHEAD
,UNDER_PAYMENT_ALLOCATION_CODE
,OVER_PAYMENT_ALLOCATION_CODE
,RECEIPT_MSMTCH_ALLOCATION_CODE
FROM OKL_CASH_ALLCTN_RLS
WHERE default_rule = 'YES'
AND TRUNC(end_date) IS NULL;
CURSOR l_khr_curr_csr(cp_contract_id IN NUMBER) IS SELECT currency_code
FROM okl_k_headers_full_v
WHERE id = cp_contract_id;
CURSOR l_inv_curr_csr(cp_cons_bill_id IN NUMBER) IS SELECT currency_code
FROM okl_cnsld_ar_hdrs_b
WHERE id = cp_cons_bill_id;
SELECT ID INTO l_xcr_id
FROM okl_ext_csh_rcpts_b
WHERE rct_id = l_rct_id;
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCAPB.pls call Okl_Xcr_Pub.update_ext_csh_txns ');
Okl_Xcr_Pub.update_ext_csh_txns( p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,l_xcrv_rec
,x_xcrv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRCAPB.pls call Okl_Xcr_Pub.update_ext_csh_txns ');
SELECT '1'
FROM AR_CASH_RECEIPTS
WHERE PAY_FROM_CUSTOMER = cp_customer_id
AND receipt_number = cp_check_num
AND TRUNC(RECEIPT_DATE) = TRUNC(cp_receipt_date);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRCAPB.pls call Okl_Xcr_Pub.update_ext_csh_txns ');