[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE get_totals (p_select IN VARCHAR2,
p_from IN VARCHAR2,
p_where IN VARCHAR2,
x_inv_total OUT NOCOPY NUMBER,
x_rec_total OUT NOCOPY NUMBER,
x_due_total OUT NOCOPY NUMBER,
x_credit_total OUT NOCOPY NUMBER,
x_adjust_total OUT NOCOPY NUMBER,
x_row_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_sql VARCHAR2(1000);
l_sql := ' SELECT '||p_select||' FROM '||p_from||' WHERE '||p_where;
l_sql := ' SELECT '||p_select||' FROM '||p_from;
SELECT rul.rule_information1 svf_applicability
FROM okc_rules_b rul
WHERE rul.dnz_chr_id = p_khr_id
AND rul.rule_information_category = p_svf_code;
SELECT svf.id svf_id,
fnd.meaning svf_name,
svf.amount svf_amount,
fnd.description svf_desc
FROM fnd_lookups fnd,
okl_service_fees_b svf
WHERE svf.srv_code = p_svf_code
AND NVL(svf.organization_id, -99) = NVL(mo_global.get_current_org_id(), -99)
AND svf.srv_code = fnd.lookup_code
AND lookup_type = 'OKL_SERVICE_FEES';
select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
,TIL.Amount
from OKL_TRX_AR_INVOICES_B TAI
,OKL_TXL_AR_INV_LNS_B TIL
,OKL_TXD_AR_LN_DTLS_B TLD2
,OKL_TXD_AR_LN_DTLS_B TLD
,OKL_STRM_TYPE_TL STYT
,OKL_XTL_SELL_INVS_V XLS
,OKC_K_HEADERS_V CHR
,OKL_CNSLD_AR_STRMS_B LSM
,OKL_CNSLD_AR_LINES_B LLN
,AR_PAYMENT_SCHEDULES_ALL APS
,OKL_CNSLD_AR_HDRS_B CNR
where TAI.ID = p_tai_id
AND CHR.ID = p_khr_id
AND TIL.TAI_ID =TAI.ID
AND TIL.ID = TLD2.TIL_ID_DETAILS
AND TLD2.TLD_ID_REVERSES =TLD.ID
AND TLD.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND XLS.TLD_ID = TLD.ID
AND XLS.LSM_ID = LSM.ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
UNION
select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
,TIL.Amount
from OKL_TRX_AR_INVOICES_B TAI
,OKL_TXL_AR_INV_LNS_B TIL2
,OKL_TXL_AR_INV_LNS_B TIL
,OKL_STRM_TYPE_TL STYT
,OKL_XTL_SELL_INVS_V XLS
,OKC_K_HEADERS_V CHR
,OKL_CNSLD_AR_STRMS_B LSM
,OKL_CNSLD_AR_LINES_B LLN
,AR_PAYMENT_SCHEDULES_ALL APS
,OKL_CNSLD_AR_HDRS_B CNR
where TAI.ID = p_tai_id
AND CHR.ID = p_khr_id
AND TIL.TAI_ID =TAI.ID
AND TIL2.TIL_ID_REVERSES = TIL.ID
AND TIL.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND XLS.TIL_ID = TIL.ID
AND XLS.LSM_ID = LSM.ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
UNION --Added following union for new invoices --dkagrawa
SELECT DISTINCT STYT.NAME
,RACTRX.TRX_NUMBER CONSOLIDATED_INVOICE_NUMBER
,APS.TRX_DATE
,RACTRL.AMOUNT_DUE_ORIGINAL
,OKL_BILLING_UTIL_PVT.INVOICE_LINE_AMOUNT_APPLIED(RACTRX.customer_trx_id, RACTRL.customer_trx_line_id) AMOUNT_APPLIED
,RACTRL.AMOUNT_DUE_REMAINING
,TIL.Amount
from OKL_TRX_AR_INVOICES_B TAI
,OKL_TXL_AR_INV_LNS_B TIL
,OKL_TXD_AR_LN_DTLS_B TLD2
,OKL_TXD_AR_LN_DTLS_B TLD
,OKL_STRM_TYPE_TL STYT
,OKC_K_HEADERS_V CHR
,AR_PAYMENT_SCHEDULES_ALL APS
,RA_CUSTOMER_TRX_ALL RACTRX
,RA_CUSTOMER_TRX_LINES_ALL RACTRL
where TAI.ID = p_tai_id
AND CHR.ID = p_khr_id
AND TIL.TAI_ID =TAI.ID
AND TIL.ID = TLD2.TIL_ID_DETAILS
AND TLD2.TLD_ID_REVERSES =TLD.ID
AND TLD.khr_id = CHR.ID
AND TLD.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND TLD.ID = RACTRL.INTERFACE_LINE_ATTRIBUTE14
AND APS.CUSTOMER_TRX_ID = RACTRL.CUSTOMER_TRX_ID
AND RACTRL.CUSTOMER_TRX_ID = RACTRX.CUSTOMER_TRX_ID
AND RACTRL.INTERFACE_LINE_ATTRIBUTE1 IS NULL;
/*select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
,TIL.Amount
from OKL_TRX_AR_INVOICES_B TAI
,OKL_TXL_AR_INV_LNS_B TIL
,OKL_TXD_AR_LN_DTLS_B TLD
,OKL_STRM_TYPE_TL STYT
,OKL_XTL_SELL_INVS_V XLS
,OKC_K_HEADERS_V CHR
,OKL_CNSLD_AR_STRMS_B LSM
,OKL_CNSLD_AR_LINES_B LLN
,AR_PAYMENT_SCHEDULES_ALL APS
,OKL_CNSLD_AR_HDRS_B CNR
,OKC_K_LINES_B CLE
,OKC_LINE_STYLES_B LSE
,OKC_K_ITEMS CIM
,FA_ADDITIONS_B FAA
where TAI.ID = p_lsm_id
AND CHR.ID = p_khr_id
AND TIL.TAI_ID =TAI.ID
AND TIL.TIL_ID_REVERSES = TLD.TIL_ID_DETAILS
AND TLD.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND XLS.TLD_ID = TLD.ID
AND XLS.XTRX_CONTRACT = CHR.CONTRACT_NUMBER
AND XLS.LSM_ID = LSM.ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.KHR_ID = CHR.ID
AND LSM.KLE_ID = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CLE.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID;
select STYT.NAME,CNR.CONSOLIDATED_INVOICE_NUMBER
,APS.TRX_DATE,APS.AMOUNT_DUE_ORIGINAL,
APS.AMOUNT_APPLIED,APS.AMOUNT_DUE_REMAINING
,TIL.Amount
from OKL_TRX_AR_INVOICES_B TAI
,OKL_TXL_AR_INV_LNS_B TIL
,OKL_TXD_AR_LN_DTLS_B TLD
,OKL_STRM_TYPE_TL STYT
,OKL_XTL_SELL_INVS_V XLS
,OKC_K_HEADERS_V CHR
,OKL_CNSLD_AR_STRMS_B LSM
,OKL_CNSLD_AR_LINES_B LLN
,AR_PAYMENT_SCHEDULES_ALL APS
,OKL_CNSLD_AR_HDRS_B CNR
,OKC_K_LINES_B CLE
,OKC_LINE_STYLES_B LSE
,OKC_K_ITEMS CIM
,FA_ADDITIONS_B FAA
where TAI.ID = p_lsm_id
AND CHR.ID = p_khr_id
AND TIL.TAI_ID =TAI.ID
AND TIL.TIL_ID_REVERSES = TLD.TIL_ID_DETAILS
AND TLD.STY_ID = STYT.ID
AND STYT.LANGUAGE = USERENV('LANG')
AND XLS.TIL_ID = TIL.ID
AND XLS.XTRX_CONTRACT = CHR.CONTRACT_NUMBER
AND XLS.LSM_ID = LSM.ID
AND LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND LSM.LLN_ID = LLN.ID
AND LLN.CNR_ID = CNR.ID
AND LSM.KHR_ID = CHR.ID
AND LSM.KLE_ID = CLE.CLE_ID
AND CLE.LSE_ID = LSE.ID
AND LSE.LTY_CODE = 'FIXED_ASSET'
AND CLE.ID = CIM.CLE_ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.OBJECT1_ID1 = FAA.ASSET_ID;*/
CURSOR c_ptm IS SELECT 1
FROM okl_process_tmplts_b
WHERE NVL(org_id, -99) = NVL(mo_global.get_current_org_id(), -99)
AND ptm_code = p_ptm_code
AND start_date <= TRUNC(SYSDATE)
AND NVL(end_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
CURSOR c_email IS SELECT email_address
FROM hz_parties hzp, okc_k_party_roles_b cpl
WHERE cpl.dnz_chr_id = p_khr_id
AND cpl.jtot_object1_code = 'OKX_PARTY'
AND cpl.rle_code = 'PRIVATE_LABEL'
AND cpl.object1_id1 = hzp.party_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = p_try_name
AND language = 'US';
SELECT sty.id
FROM okl_strm_type_tl styt, okl_strm_type_b sty
WHERE styt.name = p_sty_name
AND styt.language = 'US'
AND sty.id = styt.id
AND sty.start_date <= TRUNC(SYSDATE)
AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);*/
SELECT id
FROM okl_service_fees_b
WHERE srv_code = p_svf_code
AND start_date <= TRUNC(SYSDATE)
AND NVL(end_date, SYSDATE) >= TRUNC(SYSDATE);
SELECT pdt_id
FROM okl_k_headers
WHERE id = p_khr_id;
SELECT scs_code
FROM okc_k_headers_b
WHERE scs_code = 'SYNDICATION'
AND id = p_khr_id;
SELECT 1
FROM okc_rules_b
WHERE dnz_chr_id = p_khr_id
AND rule_information_category = 'LAFCTG';
okl_trx_ar_invoices_pub.insert_trx_ar_invoices(p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_taiv_rec => i_taiv_rec,
x_taiv_rec => r_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tilv_rec => i_tilv_rec,
x_tilv_rec => r_tilv_rec);