[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT sum(cnt) Number_of_invoices
,sum(invoice_amount) Value
,currency_code
FROM
(SELECT
COUNT(*) cnt,
SUM (NVL(xls.amount,0)) invoice_amount,
xsi.currency_code
FROM
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld,
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_k_headers_full_v khr
WHERE tai.id = til.tai_id
AND til.id = tld.til_id_details
AND xsi.id = xls.xsi_id_details
AND xls.tld_id = tld.id
AND tai.trx_status_code <> 'ERROR'
AND tai.khr_id = khr.id
AND khr.contract_number = NVL(p_in_contract_number, khr.contract_number)
GROUP BY xsi.currency_code)
GROUP BY currency_code;
SELECT sum(cnt) Number_of_invoices
,sum(invoice_amount) Value
,currency_code
FROM
(SELECT
COUNT(*) cnt,
SUM (NVL(xls.amount,0)) invoice_amount,
xsi.currency_code
FROM okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_k_headers_full_v khr
WHERE tai.id = til.tai_id
AND xsi.id = xls.xsi_id_details
AND xls.til_id = til.id
AND xsi.currency_code = p_currency_code
AND tai.trx_status_code <> 'ERROR'
AND tai.khr_id = khr.id
AND khr.contract_number = NVL(p_in_contract_number, khr.contract_number)
GROUP BY xsi.currency_code
)
GROUP BY currency_code;
SELECT
count(*) cnt,
SUM(NVL(line.EXTENDED_AMOUNT,0)) Invoice_Amount,
hdr.invoice_currency_code
FROM ra_customer_trx_all hdr,
ra_customer_trx_lines_all line,
ra_batch_sources_all batch,
RA_CUST_TRX_TYPES_ALL trx_type
WHERE line.customer_trx_id = hdr.customer_trx_id
AND hdr.batch_source_id = batch.batch_source_id
AND batch.name = 'OKL_CONTRACTS'
AND line.line_type = 'LINE'
AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
AND hdr.invoice_currency_code = p_currency_code
AND hdr.interface_header_attribute6
= NVL(p_in_contract_number,hdr.interface_header_attribute6)
GROUP BY
hdr.invoice_currency_code
ORDER BY 3;
SELECT count(*) cnt,
SUM(AMOUNT) Invoice_amt,
hdr.currency_code
FROM ra_interface_lines_all hdr,
RA_CUST_TRX_TYPES_ALL trx_type
WHERE hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
AND hdr.currency_code = p_currency_code
AND hdr.batch_source_name = 'OKL_CONTRACTS'
AND hdr.interface_line_attribute6
= NVL(p_in_contract_number,hdr.interface_line_attribute6)
GROUP BY
hdr.currency_code
ORDER BY 3;
SELECT *
FROM OKL_K_HEADERS_FULL_V
WHERE contract_number = NVL( p_contract_number, contract_number );
SELECT CURRENCY_CODE
,XTRX_CONTRACT
--,TAI
--,TRX_STATUS_CODE
,sum(cnt) Number_of_invoices
,sum(amt) Value
FROM
(SELECT
COUNT(*) cnt,
SUM (NVL(xls.amount,0)) amt,
xsi.currency_code,
xls.xtrx_contract,
tai.trx_status_code tai,
xsi.trx_status_code
FROM
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld,
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls
WHERE tai.id = til.tai_id
AND til.id = tld.til_id_details
AND xsi.id = xls.xsi_id_details
AND xls.tld_id = tld.id
AND tai.trx_status_code <> 'ERROR'
AND xls.xtrx_contract = NVL ( p_in_contract_number, xls.xtrx_contract )
AND xsi.trx_date >= NVL( p_in_from_bill_date, xsi.trx_date )
AND xsi.trx_date <= NVL( p_in_to_bill_date, xsi.trx_date )
GROUP BY
xsi.currency_code,
xls.xtrx_contract,
xsi.trx_status_code,
tai.trx_status_code)
GROUP BY currency_code,XTRX_CONTRACT;
SELECT CURRENCY_CODE
,XTRX_CONTRACT
--,TAI
--,TRX_STATUS_CODE
,sum(cnt) Number_of_invoices
,sum(amt) Value
FROM
( SELECT
COUNT(*) cnt,
SUM (NVL(xls.amount,0)) amt,
xsi.currency_code,
xls.xtrx_contract,
tai.trx_status_code tai,
xsi.trx_status_code
FROM okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til
WHERE tai.id = til.tai_id
AND xsi.id = xls.xsi_id_details
AND xsi.currency_code = p_currency
AND xls.til_id = til.id
AND tai.trx_status_code <> 'ERROR'
AND xls.xtrx_contract = NVL ( p_in_contract_number, xls.xtrx_contract )
AND xsi.trx_date >= NVL( p_in_from_bill_date, xsi.trx_date )
AND xsi.trx_date <= NVL( p_in_to_bill_date, xsi.trx_date )
GROUP BY
xsi.currency_code,
xls.xtrx_contract,
xsi.trx_status_code,
tai.trx_status_code)
GROUP BY currency_code,XTRX_CONTRACT;
SELECT
count(*) cnt,
SUM(NVL(line.EXTENDED_AMOUNT,0)) Invoice_Amount,
hdr.invoice_currency_code,
hdr.INTERFACE_HEADER_ATTRIBUTE6 Contract_Number--,
-- decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo') Invoice_Type
FROM ra_customer_trx_all hdr,
ra_customer_trx_lines_all line,
ra_batch_sources_all batch,
RA_CUST_TRX_TYPES_ALL trx_type
WHERE line.customer_trx_id = hdr.customer_trx_id
AND hdr.batch_source_id = batch.batch_source_id
AND batch.name = 'OKL_CONTRACTS'
AND line.line_type = 'LINE'
AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
AND hdr.INTERFACE_HEADER_ATTRIBUTE6 =
NVL(rtrim(ltrim( p_in_contract_number)),hdr.INTERFACE_HEADER_ATTRIBUTE6)
AND hdr.trx_date >= NVL( p_in_from_bill_date , hdr.trx_date )
AND hdr.trx_date <= NVL( p_in_to_bill_date , hdr.trx_date)
AND hdr.invoice_currency_code = p_currency
GROUP BY
hdr.invoice_currency_code,
hdr.INTERFACE_HEADER_ATTRIBUTE6--,
--decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo')
ORDER BY 3;
SELECT count(*) cnt,
SUM(AMOUNT) Invoice_amt,
hdr.currency_code,
interface_line_attribute6--,
--decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo') Invoice_Type
FROM ra_interface_lines_all hdr,
RA_CUST_TRX_TYPES_ALL trx_type
WHERE hdr.batch_source_name = 'OKL_CONTRACTS'
AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
AND hdr.INTERFACE_LINE_ATTRIBUTE6 =
NVL(rtrim(ltrim( p_in_contract_number)),hdr.INTERFACE_LINE_ATTRIBUTE6)
AND hdr.trx_date >= NVL( p_in_from_bill_date , hdr.trx_date )
AND hdr.trx_date <= NVL( p_in_to_bill_date , hdr.trx_date )
AND hdr.currency_code = p_currency
GROUP BY
hdr.currency_code,
hdr.interface_line_attribute6--,
--decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo')
ORDER BY 3;
SELECT precision
FROM fnd_currencies_vl
WHERE CURRENCY_CODE = p_curr_code;
SELECT AMOUNT Invoice_amt,
hdr.currency_code,
interface_line_attribute6 contract_number,
INTERFACE_LINE_ATTRIBUTE7 asset,
INTERFACE_LINE_ATTRIBUTE9 stream_type,
decode(trx_type.TYPE,'INV','Invoice','CM','Credit Memo') Invoice_Type,
trx_date due_date,
err.MESSAGE_TEXT remarks
FROM ra_interface_lines_all hdr,
RA_CUST_TRX_TYPES_ALL trx_type,
ra_interface_errors_all err
WHERE hdr.batch_source_name = 'OKL_CONTRACTS'
AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
AND hdr.INTERFACE_LINE_ATTRIBUTE6 =
NVL(rtrim(ltrim( p_in_contract_number)),hdr.INTERFACE_LINE_ATTRIBUTE6)
AND hdr.trx_date >= NVL( p_in_from_bill_date , hdr.trx_date )
AND hdr.trx_date <= NVL( p_in_to_bill_date , hdr.trx_date )
AND hdr.interface_line_id (+) = err.interface_line_id
ORDER BY 3,4,5,6;
SELECT NAME
FROM hr_operating_units
WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; --MOAC- Concurrent request