[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(amount_applied)
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE status = 'UNAPP'
AND display = 'Y'
AND cash_receipt_id = cp_csh_rcpt_id;
SELECT customer_trx_id,
customer_trx_line_id,
(line_Applied + tax_applied) amount_applied
FROM okl_Receipt_Applications_uv
WHERE cash_receipt_id = cp_csh_rcpt_id;
SELECT sum(nvl(amount_applied,0))
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE status = 'UNAPP'
AND cash_receipt_id = cp_csh_rcpt_id;
SELECT sum(nvl(amount_applied,0))
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE status = 'ACC'
AND cash_receipt_id = cp_csh_rcpt_id;
select ca.account_number
from HZ_CUST_ACCOUNTS ca
where ca.cust_account_id = cp_account_id;
SELECT DISTINCT(a.cash_receipt_id) icr_id,
a.amount,
a.date_effective,
a.check_number,
a.currency_code,
-- arcash.exchange_rate_date,
-- arcash.exchange_rate_type,
-- arcash.exchange_rate ,
a.id,
arcash.receipt_Date
FROM OKL_TRX_CSH_RECEIPT_V a,
OKL_TXL_RCPT_APPS_V b,
ar_cash_receipts_All arcash
WHERE a.id = b.rct_id_details
AND a.FULLY_APPLIED_FLAG = 'N'
AND a.EXPIRED_FLAG = 'N'
AND a.receipt_type = 'ADV'
AND b.khr_id = cp_cont_id
AND a.cash_receipt_id = arcash.cash_receipt_id
AND a.currency_code = decode(p_cross_currency_allowed,'N', cp_currency_code ,a.currency_code)
ORDER BY receipt_date;
SELECT (unapplied_amount + onaccount_amount) amount_available
FROM okl_receipt_Details_uv
WHERE cash_Receipt_id = cp_csh_rcpt_id;
SELECT lpt.sty_id
,lpt.amount_due_remaining
,lpt.currency_code
,lpt.ar_invoice_number
,lpt.trx_date
,lpt.customer_acct_id
,lpt.khr_id
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.contract_number = cp_contract_num
AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND lpt.currency_code = decode(p_cross_currency_allowed,'N', NVL(cp_currency_code, lpt.currency_code),lpt.currency_code);
OKL_RCT_PVT.update_row( p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_rctv_rec => l_rctv_rec
,x_rctv_rec => x_rctv_rec);
select ca.account_number
from HZ_CUST_ACCOUNTS ca
where ca.cust_account_id = cp_account_id;
SELECT lpt.sty_id
,lpt.amount_due_remaining
,lpt.currency_code
,lpt.AR_INVOICE_NUMBER
,lpt.trx_date
,lpt.ar_invoice_id
,lpt.invoice_line_id
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.contract_number = cp_contract_num
AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND lpt.currency_code = decode(p_cross_currency_allowed,'N',cp_currency_code,lpt.currency_code); --dkagrawa added decode for cross currency support
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 ca.account_number
from HZ_CUST_ACCOUNTS ca
where ca.cust_account_id = cp_account_id;
SELECT lpt.sty_id
,lpt.amount_due_remaining
,lpt.currency_code
,lpt.ar_invoice_number
,lpt.trx_date
,lpt.ar_invoice_id
,lpt.invoice_line_id
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.contract_number = cp_contract_num
AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
AND lpt.sty_id = NVL (cp_stream_type_id, lpt.sty_id)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND lpt.currency_code = decode(p_cross_currency_allowed,'N',cp_currency_code,lpt.currency_code); --dkagrawa added decode for cross currency support
SELECT hca.account_number
FROM hz_cust_accounts_all hca,
ar_cash_receipts_all arcash
WHERE hca.cust_account_id = arcash.pay_from_customer
AND arcash.cash_receipt_id = cp_cash_receipt_id;
SELECT b.sty_id, b.amount
FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b
WHERE a.id = b.rct_id_details
AND a.receipt_type = 'ADV'
AND a.EXPIRED_FLAG = 'N'
AND a.FULLY_APPLIED_FLAG = 'N'
AND a.cash_receipt_id = cp_icr_id
AND b.khr_id = cp_cont_id
AND b.sty_id IS NOT NULL;
SELECT sty_id
FROM OKL_STRM_TYP_ALLOCS
WHERE stream_allc_type = cp_str_all_type
AND cat_id = cp_cat_id
ORDER BY sequence_number;
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;
SELECT org_id
FROM okc_k_headers_All_b
WHERE id = cp_khr_id;
SELECT sum(nvl(amount_applied,0))
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE status = 'ACC'
AND cash_receipt_id = cp_csh_rcpt_id;
SELECT nvl(expired_flag,'N') INTO l_expired_flag
FROM OKL_TRX_CSH_RECEIPT_V
WHERE cash_receipt_id = cp_rct_id;
SELECT DISTINCT(lpt.contract_number),
lpt.khr_id,
lpt.start_date,
lpt.currency_code,
lpt.customer_account_number
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.customer_account_number = nvl(cp_customer_num,lpt.customer_account_number)
AND lpt.contract_number = NVL(cp_contract_num,lpt.contract_number)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND lpt.org_id = mo_global.get_current_org_id
ORDER BY lpt.start_date;
SELECT b.amount,
b.receipt_number,
b.currency_code,
b.receipt_date,
a.id
FROM OKL_TRX_CSH_RECEIPT_V a,
ar_Cash_receipts_all b
WHERE a.fully_applied_flag = 'N'
AND a.expired_flag = 'N'
AND a.receipt_type = 'ADV'
AND a.cash_receipt_id = b.cash_receipt_id
AND a.cash_receipt_id = cp_icr_id;
SELECT DISTINCT(c.cash_receipt_id) icr_id,
a.id RCT_ID,
c.receipt_date,
b.khr_id
FROM OKL_TRX_CSH_RECEIPT_V a,
OKL_TXL_RCPT_APPS_V b,
AR_CASH_RECEIPTS_ALL c
WHERE a.id = b.rct_id_details
AND a.cash_receipt_id = c.cash_receipt_id
AND a.FULLY_APPLIED_FLAG = 'N'
AND a.receipt_type = 'ADV'
AND a.expired_flag = 'N'
AND b.khr_id IS NOT NULL
ORDER BY c.receipt_date;
SELECT b.amount,
b.receipt_number,
b.currency_code,
a.ile_id,
a.id,
b.cash_receipt_id icr_id,
b.pay_from_customer customer_number
FROM OKL_TRX_CSH_RECEIPT_V a,
AR_CASH_RECEIPTS_ALL b,
hz_cust_accounts_all hca
WHERE a.cash_receipt_id = b.cash_receipt_id
AND hca.account_number = cp_customer_num
AND b.pay_from_customer = hca.cust_account_id
AND a.fully_applied_flag = 'N';
SELECT cust_account_id
FROM hz_cust_accounts
WHERE account_number = cp_customer_num;
SELECT cash_receipt_id,
currency_code,
amount,
receipt_date,
receipt_number,
decode(currency_code,cp_receipt_currency,1,2) order_by_ccy
FROM AR_CASH_RECEIPTS
WHERE pay_from_customer = cp_customer_id
AND currency_code = decode(p_cross_currency_allowed,'N',cp_receipt_currency,currency_code) --dkagrawa added decode for cross currency support
AND receipt_date >= cp_receipt_date_from
AND receipt_date <= cp_receipt_date_to
ORDER BY order_by_ccy ASC;
SELECT (unapplied_amount + onaccount_amount) amount_available
FROM okl_receipt_Details_uv
WHERE cash_receipt_id = cp_csh_rcpt_id;
SELECT lpt.sty_id
,lpt.amount_due_remaining
,lpt.currency_code
,lpt.ar_invoice_number
,lpt.trx_date
,lpt.customer_account_number
,lpt.customer_acct_id
,lpt.khr_id
,lpt.ar_invoice_id
,lpt.invoice_line_id
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.contract_number = cp_contract_num
AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number)
AND lpt.sty_id = NVL (cp_stream_type_id, lpt.sty_id)
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND lpt.currency_code = decode(p_cross_currency_allowed,'N',cp_currency_code,lpt.currency_code); --dkagrawa added decode for cross currency support
SELECT a.cash_receipt_id icr_id
FROM AR_CASH_RECEIPTS a,
hz_cust_Accounts_all hca
WHERE a.receipt_number = l_receipt_num
AND a.pay_from_customer = hca.cust_account_id
AND hca.account_number = cp_customer_num;
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 NUM_DAYS_HOLD_ADV_PAY
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 NUM_DAYS_HOLD_ADV_PAY
FROM OKL_CASH_ALLCTN_RLS
WHERE default_rule = 'YES'
AND TRUNC(end_date) IS NULL;
SELECT account_number
from hz_cust_accounts
where cust_Account_id = cp_acct_id;
OKL_RCT_PVT.update_row( p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_rctv_rec => l_rctv_rec
,x_rctv_rec => x_rctv_rec);
SELECT DISTINCT(a.cash_receipt_id) icr_id,
a.id RCT_ID,
c.receipt_date,
b.khr_id
FROM OKL_TRX_CSH_RECEIPT_V a, OKL_TXL_RCPT_APPS_V b, AR_CASH_RECEIPTS_ALL c
WHERE a.id = b.rct_id_details
AND a.cash_receipt_id = c.cash_receipt_id
AND a.FULLY_APPLIED_FLAG = 'N'
AND a.receipt_type = 'ADV'
AND a.expired_flag = 'N'
AND b.khr_id IS NOT NULL
ORDER BY c.receipt_date;
SELECT (unapplied_amount + onaccount_amount) amount_available
FROM okl_receipt_Details_uv
WHERE cash_receipt_id = cp_csh_rcpt_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 NUM_DAYS_HOLD_ADV_PAY
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 DISTINCT(lpt.contract_number),
lpt.khr_id,
lpt.start_date
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.customer_account_number = cp_customer_num
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND lpt.currency_code = NVL (cp_receipt_currency,lpt.currency_code)
AND lpt.org_id = mo_global.get_current_org_id
ORDER BY lpt.start_date;
SELECT lpt.customer_account_number
FROM okl_rcpt_cust_cont_balances_uv lpt
WHERE lpt.contract_number = cp_contract_num
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND rownum < 2;
SELECT NUM_DAYS_HOLD_ADV_PAY
FROM OKL_CASH_ALLCTN_RLS
WHERE default_rule = 'YES'
AND TRUNC(end_date) IS NULL;
SELECT account_number
from hz_cust_accounts
where cust_Account_id = cp_acct_id;
OKL_RCT_PVT.update_row( p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_rctv_rec => l_rctv_rec
,x_rctv_rec => x_rctv_rec);
SELECT count(id) INTO l_rct_id
FROM okl_trx_csh_rcpt_all_b
WHERE receipt_type= 'ADV';
log_file('after update adv count = '|| l_rct_id);
SELECT count(id) INTO l_rct_id
FROM okl_trx_csh_rcpt_all_b
WHERE receipt_type= 'ADV';
SELECT DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
CURSOR c_get_gl_date(cp_date IN DATE) IS SELECT * from (
SELECT trunc(cp_date) gl_date, 1 Counter
FROM gl_period_statuses
WHERE application_id = 222
-- BEGIN abindal bug 4356410 --
AND closing_status IN ('F','O')
-- END abindal bug 4356410 --
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND trunc(cp_date) between start_date and end_date
AND adjustment_period_flag = 'N'
UNION
SELECT MAX(end_date) gl_date, 2 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND end_date <= trunc(cp_date)
AND adjustment_period_flag = 'N'
UNION
SELECT MIN(start_date) gl_date, 3 Counter
FROM gl_period_statuses
WHERE application_id = 222
AND ledger_id = okl_accounting_util.get_set_of_books_id
AND closing_status IN ('F','O')
AND start_date >= trunc(cp_date)
AND adjustment_period_flag = 'N'
)
where gl_date is not null
order by counter;
SELECT onaccount_amount amount_available
FROM okl_receipt_Details_uv
WHERE cash_receipt_id = cp_csh_rcpt_id;
SELECT amount_due_remaining
FROM AR_PAYMENT_SCHEDULES_ALL
WHERE customer_trx_id = cp_receivables_invoice_id;
SELECT amount_applied, receivable_application_id
FROM AR_RECEIVABLE_APPLICATIONS_ALL
WHERE applied_customer_trx_id = cp_customer_trx_id
AND cash_receipt_id = cp_cash_receipt_id
AND status = 'APP'
ORDER BY creation_date desc;
SELECT trx_date,org_id
FROM RA_CUSTOMER_TRX_ALL
WHERE customer_trx_id = cp_inv_id;
SELECT customer_trx_line_id invoice_line_id, amount_due_remaining
FROM ra_customer_trx_lines_All
WHERE customer_trx_id = cp_inv_id
AND line_type ='LINE'
AND nvl(amount_due_remaining,0) > 0;
l_ar_llca_tbl.DELETE;