[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
AR_INVOICE_ID Ar_Invoice_Id,
Invoice_Number Invoice_number,
invoice_currency_code ,
INVOICE_LINE_ID invoice_line_id,
Line_Identifier Line_Number,
amount_due_remaining amount_due_remaining,
line_identifier Line_Identifier,
sty_id,
CONTRACT_NUMBER
From OKL_RCPT_ARINV_BALANCES_UV
Where Invoice_Number = cp_arinv_num
And Org_id = cp_org_id
--asawanka changed for bug #5391874
AND customer_account_number = nvl(p_customer_num,customer_account_number)
AND status = 'OP';
SELECT A.Id contract_id , A.start_date Start_Date
From Okc_k_headers_all_b A, OKL_RCPT_ARINV_BALANCES_UV B
Where B.Invoice_Number = cp_arinv_num
and a.contract_number = b.contract_number
And b.org_id= cp_org_id;
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
,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 lpt.khr_id contract_id, lpt.CONTRACT_NUMBER contract_number
FROM OKL_RCPT_ARINV_BALANCES_UV lpt
WHERE lpt.INVOICE_NUMBER = l_inv_ref
AND lpt.amount_due_remaining > 0
And lpt.org_id= l_org_id
ORDER BY lpt.start_date;
SELECT TRX_NUMBER, invoice_currency_code
FROM RA_CUSTOMER_TRX_ALL
WHERE TRX_NUMBER= l_inv_ref
AND org_id = cp_org_id;
open_inv_tbl.delete;
x_appl_tbl.delete;
x_appl_tbl.delete;
x_appl_tbl.DELETE;
SELECT AR_INVOICE_ID,
AR_INVOICE_NUMBER,
INVOICE_LINE_ID,
LINE_NUMBER,
AMOUNT_DUE_REMAINING,
STY_ID
FROM OKL_RCPT_CUST_CONT_BALANCES_UV
WHERE KHR_ID = cp_contract_id
AND ORG_ID = cp_org_id
AND STATUS = 'OP';
SELECT AR_INVOICE_ID,
AR_INVOICE_NUMBER,
INVOICE_LINE_ID,
LINE_NUMBER,
AMOUNT_DUE_REMAINING,
STY_ID
FROM OKL_RCPT_CUST_CONT_BALANCES_UV
WHERE KHR_ID = cp_contract_id
--asawanka changed for bug #5391874
AND CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
AND ORG_ID = cp_org_id
AND STATUS = 'OP';
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
,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_ALL
WHERE CAU_ID = cp_cau_id
AND START_DATE <= trunc(SYSDATE)
AND (END_DATE >= trunc(SYSDATE) OR END_DATE IS NULL);
SELECT ID CONTRACT_ID, currency_code
FROM OKC_K_HEADERS_ALL_B
WHERE contract_number = cp_contract_num;
x_appl_tbl.delete;
x_appl_tbl.DELETE;
x_appl_tbl.delete;
x_appl_tbl.DELETE;
SELECT AR_INVOICE_ID,
AR_INVOICE_NUMBER,
INVOICE_LINE_ID,
LINE_NUMBER,
AMOUNT_DUE_REMAINING,
INVOICE_DUE_DATE
FROM OKL_RCPT_CUST_CONT_BALANCES_UV
WHERE CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
AND ORG_ID = cp_org_id
AND STATUS = 'OP'
AND CURRENCY_CODE = cp_curr_code
-- AND AMOUNT_DUE_REMAINING > 0
ORDER BY INVOICE_DUE_DATE;
SELECT AR_INVOICE_ID,
AR_INVOICE_NUMBER,
INVOICE_LINE_ID,
LINE_NUMBER,
AMOUNT_DUE_REMAINING,
INVOICE_DUE_DATE
FROM OKL_RCPT_CUST_CONT_BALANCES_UV
WHERE CUSTOMER_ACCOUNT_NUMBER = cp_customer_num
AND ORG_ID = cp_org_id
AND STATUS = 'OP'
-- AND AMOUNT_DUE_REMAINING > 0
AND CURRENCY_CODE = cp_curr_code
ORDER BY INVOICE_DUE_DATE DESC;
x_appl_tbl.DELETE;
SELECT ID
,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_ALL
WHERE default_rule = 'YES'
AND TRUNC(end_date) IS NULL
AND org_id = p_org_id;
has been set to -1. We will increment counter everytime will update amount to -1
. If value of counter is equal to count of l_rcpt_tbl then will terminate the loop
. It makes a check in the loop whether amount is -1 if it is -1 then leave it
otherwise if it is accessing any record from top of l_rcpt_tbl for the first time
then assign create invoice header record and create 1st invoice line for this header.
Also set amount to -1 and increment counter.
. For the subsequent records in l_rcpt_tbl, if same invoice header is found then
create another line record in invoice lines table. Also set amount to -1 and
increment counter.
. If the index of l_rcpt_tbl is the last then associate invoice lines table to
invoice header record. Set complete_cycle to TRUE. Re-initialize i to 1,
which will point to first record of l_rcpt_tbl. Re-initialize line_counter to 1.
Increment hdr_counter.
*/
IF (G_DEBUG_ENABLED = 'Y') THEN
G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
l_inv_lns_tbl.delete;
l_inv_lns_tbl.delete;
SELECT lpt.stream_type_id
,lpt.amount_due_remaining invoice_due_remaining
,lpt.amount_due_original invoice_due_original
,lpt.currency_code invoice_currency_code
,lpt.AR_INVOICE_NUMBER
,lpt.AR_INVOICE_ID
,lpt.stream_element_id
,lpt.ar_invoice_line_id
,lpt.ar_invoice_line_number
,lpt.trx_date invoice_date
,lpt.contract_number
FROM okl_rcpt_consinv_balances_uv lpt
WHERE lpt.consolidated_invoice_id = cp_cons_bill_id
--asawanka changed for bug #5391874
AND lpt.customer_account_number = nvl(cp_customer_num,lpt.customer_account_number)
AND lpt.org_id=cp_org_id
AND lpt.status='OP';
SELECT DISTINCT(lpt.contract_number)
,lpt.invoice_date Start_date, lpt.contract_id,lpt.currency_code
FROM okl_rcpt_consinv_balances_uv lpt
WHERE lpt.consolidated_invoice_id = cp_cons_bill_id --Always passing cp_cons_bill_id as not null so no need to have nvl
AND lpt.customer_account_number = NVL (cp_customer_num, lpt.customer_account_number);
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
,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 lpt.contract_id, lpt.contract_number
FROM okl_rcpt_consinv_balances_uv lpt, okc_k_headers_all_b khr
WHERE lpt.consolidated_invoice_id = cp_cons_bill_id
AND lpt.status = 'OP'
AND lpt.amount_due_remaining > 0
AND khr.id = lpt.contract_id
ORDER BY khr.start_date;
select ID
from OKL_CNSLD_AR_HDRS_B
where consolidated_invoice_number=cl_cons_inv;
x_appl_tbl.DELETE;