[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_update_tbl IN OUT NOCOPY update_tbl_type)
IS
l_old_cnr_id NUMBER;
SELECT SUM(lsm.amount)
FROM okl_cnsld_ar_hdrs_b cnr,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_strms_b lsm
WHERE cnr.id = p_cnr_id AND
cnr.id = lln.cnr_id AND
lln.id = lsm.lln_id;
SELECT SUM(lsm.amount)
FROM okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_strms_b lsm
WHERE lln.id = p_lln_id AND
lln.id = lsm.lln_id;
IF l_update_tbl.COUNT > 0 THEN
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.cnr_id '||l_update_tbl(m).cnr_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.cons_inv_number '||l_update_tbl(m).cons_inv_number);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.lln_id '||l_update_tbl(m).lln_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.lsm_id '||l_update_tbl(m).lsm_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.asset_number '||l_update_tbl(m).asset_number);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.invoice_format '||l_update_tbl(m).invoice_format);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.line_type '||l_update_tbl(m).line_type);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.sty_name '||l_update_tbl(m).sty_name);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.contract_number '||l_update_tbl(m).contract_number);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.lsm_amount '||l_update_tbl(m).lsm_amount);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.xsi_id '||l_update_tbl(m).xsi_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.xls_id '||l_update_tbl(m).xls_id);
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
IF l_update_tbl(m).cnr_id <> l_old_cnr_id THEN
l_cnr_amount := NULL;
OPEN cnr_amt_csr ( l_update_tbl(m).cnr_id );
UPDATE okl_cnsld_ar_hdrs_b
SET trx_status_code = 'PROCESSED',
amount = l_cnr_amount,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).cnr_id;
l_old_cnr_id := l_update_tbl(m).cnr_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
IF l_update_tbl(m).lln_id <> l_old_lln_id THEN
l_lln_amount := NULL;
OPEN lln_amt_csr( l_update_tbl(m).lln_id );
UPDATE okl_cnsld_ar_lines_b
SET amount = l_lln_amount,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).lln_id;
l_old_lln_id := l_update_tbl(m).lln_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
UPDATE Okl_Ext_Sell_Invs_b
SET TRX_STATUS_CODE = 'WORKING',
XTRX_INVOICE_PULL_YN = 'Y',
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).xsi_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
UPDATE Okl_Ext_Sell_Invs_b
SET TRX_STATUS_CODE = 'ENTERED',
XTRX_INVOICE_PULL_YN = 'Y',
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).xsi_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
UPDATE Okl_Ext_Sell_Invs_tl
SET XTRX_CONS_INVOICE_NUMBER = l_update_tbl(m).cons_inv_number,
XTRX_FORMAT_TYPE = l_update_tbl(m).invoice_format,
XTRX_PRIVATE_LABEL = l_update_tbl(m).private_label,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).xsi_id;
UPDATE Okl_Xtl_Sell_Invs_b
SET LSM_ID = l_update_tbl(m).LSM_ID,
-- XTRX_CONS_LINE_NUMBER = l_update_tbl(m).line_number,
XTRX_CONS_STREAM_ID = l_update_tbl(m).lsm_id,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).xls_id;
UPDATE Okl_Xtl_Sell_Invs_tl
SET XTRX_CONTRACT = l_update_tbl(m).contract_number,
XTRX_ASSET = l_update_tbl(m).asset_number,
XTRX_STREAM_TYPE = l_update_tbl(m).sty_name,
XTRX_STREAM_GROUP = l_update_tbl(m).line_type,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).xls_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
IF l_update_tbl(m).cnr_id <> l_old_cnr_id THEN
UPDATE okl_cnsld_ar_hdrs_b
SET trx_status_code = 'ERROR',
amount = l_cnr_amount,
last_update_date = sysdate,
last_updated_by = Fnd_Global.USER_ID,
last_update_login = Fnd_Global.LOGIN_ID
WHERE id = l_update_tbl(m).cnr_id;
l_old_cnr_id := l_update_tbl(m).cnr_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
DELETE FROM okl_cnsld_ar_strms_b
WHERE id = l_update_tbl(m).lsm_id;
DELETE FROM okl_cnsld_ar_strms_tl
WHERE id = l_update_tbl(m).lsm_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
DELETE FROM okl_cnsld_ar_lines_b
WHERE id = l_update_tbl(m).lln_id;
DELETE FROM okl_cnsld_ar_lines_tl
WHERE id = l_update_tbl(m).lln_id;
FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
DELETE FROM okl_cnsld_ar_hdrs_b
WHERE id = l_update_tbl(m).cnr_id;
DELETE FROM okl_cnsld_ar_hdrs_tl
WHERE id = l_update_tbl(m).cnr_id;
select RULE_INFORMATION1
from okc_rule_groups_v rgp,
okc_rules_v rul
where rgp.dnz_chr_id = cp_khr_id AND
rgp.chr_id = rgp.dnz_chr_id AND
rgp.id = rul.rgp_id AND
rgp.cle_id IS NULL AND
rgp.rgd_code = 'LABILL' AND
rul.rule_information_category = 'LAINVD';
SELECT id,
priority,
pkg_name,
proc_name
FROM okl_invoice_mssgs_v
WHERE cp_consolidated_inv_date
BETWEEN NVL(START_DATE,cp_consolidated_inv_date) AND
NVL(END_DATE,cp_consolidated_inv_date);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS ');
Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_cnrv_rec
,x_cnrv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_imav_rec
,x_imav_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
SELECT id
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = p_cnr_id AND
khr_id = p_khr_id AND
ilt_id = p_ilt_id AND
sequence_number = p_sequence_number;
SELECT id
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = p_cnr_id AND
khr_id = p_khr_id AND
kle_id = p_kle_id AND
ilt_id = p_ilt_id AND
sequence_number = p_sequence_number;
SELECT id
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = p_cnr_id AND
khr_id = p_khr_id AND
kle_id IS NULL AND
ilt_id = p_ilt_id AND
sequence_number = p_sequence_number;
SELECT id
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = p_cnr_id AND
ilt_id = p_ilt_id AND
sequence_number = p_sequence_number;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES ');
Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES (
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_llnv_rec
,x_llnv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES ');
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS ');
Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS(
p_api_version
,p_init_msg_list
,x_return_status
,x_msg_count
,x_msg_data
,p_lsmv_rec
,x_lsmv_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS ');
p_update_tbl IN OUT NOCOPY update_tbl_type)
IS
l_api_name CONSTANT VARCHAR2(30) := 'process_cons_bill_tbl';
l_update_tbl update_tbl_type;
SELECT chr_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT name
FROM okc_k_lines_v
WHERE id = p_cle_id;
SELECT FA.ID
FROM OKC_K_HEADERS_B CHR,
OKC_K_LINES_B TOP_CLE,
OKC_LINE_STYLES_b TOP_LSE,
OKC_K_LINES_B SUB_CLE,
OKC_LINE_STYLES_b SUB_LSE,
OKC_K_ITEMS CIM,
OKC_K_LINES_V FA,
OKC_LINE_STYLES_B AST_LSE,
OKL_CNSLD_AR_STRMS_B LSM
WHERE
CHR.ID = TOP_CLE.DNZ_CHR_ID AND
TOP_CLE.LSE_ID = TOP_LSE.ID AND
TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
TOP_CLE.ID = SUB_CLE.CLE_ID AND
SUB_CLE.LSE_ID = SUB_LSE.ID AND
SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
SUB_CLE.ID = LSM.KLE_ID AND
LSM.ID = p_lsm_id AND
CIM.CLE_ID = SUB_CLE.ID AND
CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
CIM.OBJECT1_ID1 = FA.ID AND
FA.LSE_ID = AST_LSE.ID AND
AST_LSE.LTY_CODE = 'FREE_FORM1';
SELECT
inf.name inf_name,
inf.contract_level_yn,
ity.id ity_id,
ity.name ity_name,
ity.group_asset_yn,
ity.group_by_contract_yn,
ilt.id ilt_id,
ilt.sequence_number,
ilt.name ilt_name,
sty.name sty_name
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt,
okl_invc_frmt_strms_v frs,
okl_strm_type_v sty
WHERE inf.id = p_format_id
AND ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND frs.ilt_id = ilt.id
AND sty.id = frs.sty_id
AND frs.sty_id = p_stream_id;
SELECT
inf.name inf_name,
inf.contract_level_yn,
ity.id ity_id,
ity.name ity_name,
ity.group_asset_yn,
ity.group_by_contract_yn,
ilt.id ilt_id,
ilt.sequence_number,
ilt.name ilt_name
FROM okl_invoice_formats_v inf,
okl_invoice_types_v ity,
okl_invc_line_types_v ilt
WHERE inf.id = p_format_id
AND ity.inf_id = inf.id
AND ilt.ity_id = ity.id
AND inf.ilt_id = ilt.id;
SELECT SUM(lsm.amount)
FROM okl_cnsld_ar_hdrs_b cnr,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_strms_b lsm
WHERE cnr.id = p_cnr_id AND
cnr.id = lln.cnr_id AND
lln.id = lsm.lln_id;
SELECT SUM(lsm.amount)
FROM okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_strms_b lsm
WHERE lln.id = p_lln_id AND
lln.id = lsm.lln_id;
SELECT name
FROM okl_strm_type_v
WHERE id = p_id;
p_update_tbl);
p_update_tbl := l_update_tbl;
p_update_tbl);
p_update_tbl := l_update_tbl;
l_cnt := p_update_tbl.count;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'DEL Updates (p_saved_bill_rec.l_cons_inv_num)'||p_saved_bill_rec.l_cons_inv_num);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'DEL Updates (l_format_name)'||l_format_name);
p_update_tbl(l_cnt).cnr_id := p_saved_bill_rec.l_cnr_id;
p_update_tbl(l_cnt).cons_inv_number := p_saved_bill_rec.l_cons_inv_num;
p_update_tbl(l_cnt).lln_id := p_saved_bill_rec.l_lln_id;
p_update_tbl(l_cnt).lsm_id := l_lsm_id;
p_update_tbl(l_cnt).asset_number := l_asset_name;
p_update_tbl(l_cnt).invoice_format := l_format_name;
p_update_tbl(l_cnt).line_type := l_cons_line_name;
p_update_tbl(l_cnt).sty_name := l_sty_name;
p_update_tbl(l_cnt).contract_number := p_cons_bill_tbl(k).contract_number;
p_update_tbl(l_cnt).private_label := p_cons_bill_tbl(k).private_label;
p_update_tbl(l_cnt).lsm_amount := p_cons_bill_tbl(k).amount;
p_update_tbl(l_cnt).xsi_id := p_cons_bill_tbl(k).xsi_id;
p_update_tbl(l_cnt).xls_id := p_cons_bill_tbl(k).xls_id;
CURSOR ubb_csr IS SELECT
xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
TRUNC(xsi.TRX_DATE) date_consolidated,
tai.khr_id contract_id, -- get contract Id
xsi.org_id org_id,
tai.clg_id clg_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
tld.sty_id stream_id, -- to get the line seq #
til.line_number ubb_line_number,
xsi.id xsi_id,
xls.id xls_id,
xls.amount ubb_amount,
xls.sel_id sel_id
--vthiruva added for bug#4438971 fix..24-JUN-2005
,xsi.inf_id inf_id
,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_txd_ar_ln_dtls_v tld,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b chr,
okl_parallel_processes pws
WHERE
xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsi.id AND
tld.id = xls.tld_id AND
til.id = tld.TIL_ID_DETAILS AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
-- Contract Specific consolidation
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
-- Contract Specific consolidation
tai.clg_id IS NOT NULL AND
xls.amount > 0 AND
PWS.OBJECT_TYPE = 'CUSTOMER' AND
XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
ORDER BY 1,2,3,4,5,6,7,8;
CURSOR qte_csr IS SELECT
-- Start Bug 4731187 (changed Order by)
tai.qte_id qte_id,
TRUNC(xsi.TRX_DATE) date_consolidated,
xls.amount qte_amount,
xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
tai.khr_id contract_id,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
til.sty_id stream_id,
til.line_number qte_line_number,
til.description description,
xsi.id xsi_id,
xls.id xls_id,
xls.sel_id sel_id
--vthiruva added for bug#4438971 fix..24-JUN-2005
,xsi.inf_id inf_id
,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b chr,
okl_parallel_processes pws
WHERE
xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsi.id AND
til.id = xls.til_id AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
-- Contract Specific consolidation
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
-- Contract Specific consolidation
tai.qte_id IS NOT NULL AND
PWS.OBJECT_TYPE = 'CUSTOMER' AND
XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
ORDER BY 1,2,3,4,5,6,7,8,9;
CURSOR cpy_csr IS SELECT
xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
tai.khr_id contract_id,
TRUNC(xsi.TRX_DATE) date_consolidated,
tai.cpy_id cpy_id,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
til.sty_id stream_id,
til.line_number cpy_line_number,
xsi.id xsi_id,
xls.id xls_id,
xls.amount cpy_amount,
xls.sel_id sel_id
--vthiruva added for bug#4438971 fix..24-JUN-2005
,xsi.inf_id inf_id
,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b chr,
okl_parallel_processes pws
WHERE
xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsi.id AND
til.id = xls.til_id AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
-- Contract Specific consolidation
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
-- Contract Specific consolidation
tai.cpy_id IS NOT NULL AND
xls.amount > 0 AND
PWS.OBJECT_TYPE = 'CUSTOMER' AND
XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
ORDER BY 1,2,3,4,5,6,7,8;
SELECT xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
TRUNC(xsi.trx_date) date_consolidated,
tai.khr_id contract_id,
CHR.contract_number contract_number,
xsi.inf_id inf_id,
'-9958' prev_cons_invoice_num,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
til.sty_id stream_id,
xsi.id xsi_id,
xls.id xls_id,
xls.amount cm2_amount,
xls.sel_id sel_id,
xsi.legal_entity_id legal_entity_id --FOR le uptake project 8 -11 -2006
FROM okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_b xls,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_all_b CHR,
okl_parallel_processes pws
WHERE xsi.trx_status_code = 'SUBMITTED'
AND xls.xsi_id_details = xsi.id
AND til.id = xls.til_id
AND tai.id = til.tai_id
AND tai.khr_id = CHR.id
AND --contract specific consolidation
CHR.contract_number = nvl(p_contract_number, CHR.contract_number)
AND --contract specific consolidation
tai.qte_id IS NULL
AND xls.amount < 0
AND til.til_id_reverses IS NULL
AND pws.object_type = 'CUSTOMER'
AND xsi.customer_id = to_number(pws.object_value)
AND pws.assigned_process = p_assigned_process
UNION
SELECT xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
TRUNC(xsi.trx_date) date_consolidated,
tai.khr_id contract_id,
CHR.contract_number contract_number,
xsi.inf_id inf_id,
xsir.xtrx_cons_invoice_number prev_cons_invoice_num,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
til.sty_id stream_id,
xsi.id xsi_id,
xls.id xls_id,
xls.amount cm2_amount,
xls.sel_id sel_id,
xsi.legal_entity_id legal_entity_id --FOR le uptake project 8 -11 -2006
FROM okl_ext_sell_invs_v xsi,
okl_ext_sell_invs_v xsir,
okl_xtl_sell_invs_b xls,
okl_xtl_sell_invs_b xlsr,
okl_txl_ar_inv_lns_b til,
okl_trx_ar_invoices_b tai,
okc_k_headers_all_b CHR,
okl_parallel_processes pws
WHERE xsi.trx_status_code = 'SUBMITTED'
AND xls.xsi_id_details = xsi.id
AND til.id = xls.til_id
AND tai.id = til.tai_id
AND tai.khr_id = CHR.id
AND --contract specific consolidation
CHR.contract_number = nvl(p_contract_number, CHR.contract_number)
AND --contract specific consolidation
tai.qte_id IS NULL
AND xls.amount <= 0
AND til.til_id_reverses = xlsr.til_id
AND xlsr.xsi_id_details = xsir.id
AND pws.object_type = 'CUSTOMER'
AND xsi.customer_id = to_number(pws.object_value)
AND pws.assigned_process = p_assigned_process
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
SELECT
xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
TRUNC(xsi.TRX_DATE) date_consolidated,
tai.khr_id contract_id, -- get contract Id
chr.contract_number contract_number,
xsi.inf_id inf_id,
'-9958' prev_cons_invoice_num,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
tld.sty_id stream_id, -- to get the line seq #
xsi.id xsi_id,
xls.id xls_id,
xls.amount cm3_amount,
xls.sel_id sel_id
,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_v xsi,
okl_xtl_sell_invs_v xls,
okl_txd_ar_ln_dtls_v tld,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b chr,
okl_parallel_processes pws
WHERE
xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsi.id AND
tld.id = xls.tld_id AND
til.id = tld.TIL_ID_DETAILS AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
-- Contract Specific consolidation
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
-- Contract Specific consolidation
xls.amount < 0 AND
tld.tld_id_reverses IS NULL AND
PWS.OBJECT_TYPE = 'CUSTOMER' AND
XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
UNION
SELECT
xsi.customer_id customer_id,
xsi.currency_code currency,
xsi.customer_address_id bill_to_site,
xsi.receipt_method_id payment_method,
xsi.xtrx_private_label private_label,
TRUNC(xsi.TRX_DATE) date_consolidated,
tai.khr_id contract_id, -- get contract Id
chr.contract_number contract_number,
xsi.inf_id inf_id,
xsir.xtrx_cons_invoice_number prev_cons_invoice_num,
xsi.org_id org_id,
xsi.set_of_books_id set_of_books_id,
til.kle_id kle_id,
tld.sty_id stream_id, -- to get the line seq #
xsi.id xsi_id,
xls.id xls_id,
xls.amount cm3_amount,
xls.sel_id sel_id
,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
FROM
okl_ext_sell_invs_v xsi,
okl_ext_sell_invs_v xsir,
okl_xtl_sell_invs_v xls,
okl_xtl_sell_invs_v xlsr,
okl_txd_ar_ln_dtls_v tld,
okl_txl_ar_inv_lns_v til,
okl_trx_ar_invoices_v tai,
okc_k_headers_b chr,
okl_parallel_processes pws
WHERE
xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
xls.xsi_id_details = xsi.id AND
tld.id = xls.tld_id AND
til.id = tld.TIL_ID_DETAILS AND
tai.id = til.tai_id AND
tai.khr_id = chr.id
AND
-- Contract Specific consolidation
chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
-- Contract Specific consolidation
xls.amount <= 0 AND
tld.tld_id_reverses IS NOT NULL AND
xlsr.tld_id = tld.tld_id_reverses AND
xsir.id = xlsr.xsi_id_details AND
PWS.OBJECT_TYPE = 'CUSTOMER' AND
XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
ORDER BY 1,2,3,4,5,6,7,8,9,10;
l_update_tbl update_tbl_type;
SELECT *
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = p_cnr_id
ORDER BY sequence_number;
SELECT id
FROM okl_invoice_formats_v
WHERE name = p_format_name;
SELECT ASSET_NUMBER
FROM
OKL_CNSLD_AR_STRMS_B LSM
,OKX_ASSET_LINES_V KLE
WHERE LSM.ID = p_lsm_id
AND KLE.PARENT_LINE_ID = LSM.KLE_ID;
SELECT FA.NAME
FROM OKC_K_HEADERS_B CHR,
OKC_K_LINES_B TOP_CLE,
OKC_LINE_STYLES_b TOP_LSE,
OKC_K_LINES_B SUB_CLE,
OKC_LINE_STYLES_b SUB_LSE,
OKC_K_ITEMS CIM,
OKC_K_LINES_V FA,
OKC_LINE_STYLES_B AST_LSE,
OKL_CNSLD_AR_STRMS_B LSM
WHERE
CHR.ID = TOP_CLE.DNZ_CHR_ID AND
TOP_CLE.LSE_ID = TOP_LSE.ID AND
TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
TOP_CLE.ID = SUB_CLE.CLE_ID AND
SUB_CLE.LSE_ID = SUB_LSE.ID AND
SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
SUB_CLE.ID = LSM.KLE_ID AND
LSM.ID = p_lsm_id AND
CIM.CLE_ID = SUB_CLE.ID AND
CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
CIM.OBJECT1_ID1 = FA.ID AND
FA.LSE_ID = AST_LSE.ID AND
AST_LSE.LTY_CODE = 'FREE_FORM1';
TYPE cnr_update_rec_type IS RECORD (
cnr_id NUMBER,
lln_id NUMBER,
lsm_id NUMBER,
xsi_id NUMBER,
xls_id NUMBER,
return_status VARCHAR2(1)
);
TYPE cnr_update_tbl_type IS TABLE OF cnr_update_rec_type
INDEX BY BINARY_INTEGER;
cnr_update_tbl cnr_update_tbl_type;
SELECT contract_number
FROM okc_k_headers_b
WHERE id = p_id;
SELECT name
FROM okl_strm_type_v
WHERE id = p_id;
SELECT khr_id
FROM okl_cnsld_ar_strms_b
WHERE id = p_lsm_id;
SELECT DISTINCT CURRENCY_CODE
FROM okl_cnsld_ar_hdrs_v
WHERE request_id = p_request_id;
SELECT count(*)
FROM okl_cnsld_ar_hdrs_v
WHERE request_id = p_request_id
AND TRX_STATUS_CODE = p_trx_sts
AND CURRENCY_CODE = p_curr_code;
SELECT
DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
FROM dual;
SELECT name
FROM hr_operating_units
WHERE organization_id = mo_global.get_current_org_id();
SELECT SUM(lsm.amount)
FROM okl_cnsld_ar_hdrs_b cnr,
okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_strms_b lsm
WHERE cnr.id = p_cnr_id AND
cnr.id = lln.cnr_id AND
lln.id = lsm.lln_id;
SELECT SUM(lsm.amount)
FROM okl_cnsld_ar_lines_b lln,
okl_cnsld_ar_strms_b lsm
WHERE lln.id = p_lln_id AND
lln.id = lsm.lln_id;
cons_bill_tbl.delete;
l_update_tbl.delete;
cons_bill_tbl.delete;
p_update_tbl => l_update_tbl);
l_update_tbl);
cons_bill_tbl.delete;
l_update_tbl.delete;
cons_bill_tbl.delete;
p_update_tbl => l_update_tbl);
l_update_tbl);
cons_bill_tbl.delete;
l_update_tbl.delete;
cons_bill_tbl.delete;
p_update_tbl => l_update_tbl);
l_update_tbl);
cons_bill_tbl.delete;
l_update_tbl.delete;
cons_bill_tbl.delete;
p_update_tbl => l_update_tbl);
l_update_tbl);
cnr_tab_idx := NVL (cnr_update_tbl.LAST, 0) + 1;
-- Build a PL/SQL table for later Updates
cnr_update_tbl(cnr_tab_idx).cnr_id := l_cnr_id;
cnr_update_tbl(cnr_tab_idx).lln_id := l_lln_id;
cnr_update_tbl(cnr_tab_idx).lsm_id := l_lsm_id;
cnr_update_tbl(cnr_tab_idx).xsi_id := ubb_rec.xsi_id;
cnr_update_tbl(cnr_tab_idx).xls_id := ubb_rec.xls_id;
cnr_update_tbl(cnr_tab_idx).return_status := x_return_status;
-- Increment the PL/SQL table index for updates
cnr_tab_idx := cnr_tab_idx + 1;
cnr_tab_idx := NVL (cnr_update_tbl.LAST, 0) + 1;
-- Build a PL/SQL table for later Updates
cnr_update_tbl(cnr_tab_idx).cnr_id := l_cnr_id;
cnr_update_tbl(cnr_tab_idx).lln_id := l_lln_id;
cnr_update_tbl(cnr_tab_idx).lsm_id := l_lsm_id;
cnr_update_tbl(cnr_tab_idx).xsi_id := qte_rec.xsi_id;
cnr_update_tbl(cnr_tab_idx).xls_id := qte_rec.xls_id;
cnr_update_tbl(cnr_tab_idx).return_status := x_return_status;
-- Increment the PL/SQL table index for updates
cnr_tab_idx := cnr_tab_idx + 1;
cnr_tab_idx := NVL (cnr_update_tbl.LAST, 0) + 1;
-- Build a PL/SQL table for later Updates
cnr_update_tbl(cnr_tab_idx).cnr_id := l_cnr_id;
cnr_update_tbl(cnr_tab_idx).lln_id := l_lln_id;
cnr_update_tbl(cnr_tab_idx).lsm_id := l_lsm_id;
cnr_update_tbl(cnr_tab_idx).xsi_id := cpy_rec.xsi_id;
cnr_update_tbl(cnr_tab_idx).xls_id := cpy_rec.xls_id;
cnr_update_tbl(cnr_tab_idx).return_status := x_return_status;
-- Increment the PL/SQL table index for updates
cnr_tab_idx := cnr_tab_idx + 1;
IF (cnr_update_tbl.COUNT > 0) THEN
cnr_tab_idx := cnr_update_tbl.FIRST;
-- and update the amounts at the line and consolidated bill
-- level
IF l_cnr_id <> cnr_update_tbl(cnr_tab_idx).cnr_id THEN
l_cnr_id := cnr_update_tbl(cnr_tab_idx).cnr_id;
SELECT SUM(amount) INTO l_line_amount
FROM okl_cnsld_ar_strms_v
WHERE lln_id = line_seq.id;
-- Update the consolidated lines with line num
-- and amount
u_llnv_rec.id := line_seq.id;
UPDATE Okl_Cnsld_Ar_Lines_b
SET sequence_number = l_seq_num,
amount = l_line_amount
WHERE id = line_seq.id;
-- Update the amount on the Cons Bill header
l_consbill_amount := 0;
SELECT SUM(amount) INTO l_consbill_amount
FROM okl_cnsld_ar_lines_v
WHERE cnr_id = l_cnr_id;
--Update the consolidated headers table
--Initialize records
u_cnrv_rec := null_cnrv_rec;
UPDATE Okl_Cnsld_Ar_Hdrs_b
SET amount = l_consbill_amount,
trx_status_code = 'PROCESSED'
WHERE id = l_cnr_id;
-- Update the xtrx_fields on XSI
-- Initialize records
l_xsiv_rec := null_xsiv_rec;
IF cnr_update_tbl(cnr_tab_idx).cnr_id IS NOT NULL THEN
l_xsiv_rec.id := cnr_update_tbl(cnr_tab_idx).xsi_id;
SELECT cnr.consolidated_invoice_number,
inf.name
INTO l_xsiv_rec.XTRX_CONS_INVOICE_NUMBER,
l_xsiv_rec.XTRX_FORMAT_TYPE
FROM okl_cnsld_ar_hdrs_v cnr,
okl_invoice_formats_b infb,
okl_invoice_formats_tl inf
WHERE cnr.id = cnr_update_tbl(cnr_tab_idx).cnr_id
AND cnr.inf_id = infb.id(+)
and infb.id = inf.id(+)
and inf.language(+) = userenv('LANG');
UPDATE Okl_Ext_Sell_Invs_b
SET TRX_STATUS_CODE = l_xsiv_rec.TRX_STATUS_CODE,
XTRX_INVOICE_PULL_YN = 'Y'
WHERE id = cnr_update_tbl(cnr_tab_idx).xsi_id;
UPDATE Okl_Ext_Sell_Invs_tl
SET XTRX_CONS_INVOICE_NUMBER = l_xsiv_rec.XTRX_CONS_INVOICE_NUMBER,
XTRX_FORMAT_TYPE = l_xsiv_rec.XTRX_FORMAT_TYPE
WHERE id = cnr_update_tbl(cnr_tab_idx).xsi_id;
-- Update the xtrx_fields on XLS
-- Initialize records
l_xlsv_rec := null_xlsv_rec;
l_xlsv_rec.id := cnr_update_tbl(cnr_tab_idx).xls_id;
SELECT TO_CHAR(lln.sequence_number),
SUBSTR(CONTRACT_NUMBER,1,30),
-- TO_CHAR(lln.kle_id),
-- Added NVL for bug 4528015
NVL(ilt.name, lln.line_type)
INTO l_xlsv_rec.XTRX_CONS_LINE_NUMBER,
l_xlsv_rec.XTRX_CONTRACT,
-- l_xlsv_rec.XTRX_ASSET,
l_xlsv_rec.XTRX_STREAM_GROUP
FROM okl_cnsld_ar_lines_v lln,
okl_invc_line_types_v ilt,
okc_k_headers_b khr
WHERE lln.id = cnr_update_tbl(cnr_tab_idx).lln_id AND
khr.id = lln.khr_id AND
lln.ilt_id = ilt.id(+);
OPEN asset_line_csr(cnr_update_tbl(cnr_tab_idx).lsm_id);
OPEN service_asset_csr(cnr_update_tbl(cnr_tab_idx).lsm_id);
SELECT sty.name
INTO l_xlsv_rec.XTRX_STREAM_TYPE
FROM okl_cnsld_ar_strms_v lsm,
okl_strm_type_v sty
WHERE lsm.id = cnr_update_tbl(cnr_tab_idx).lsm_id AND
sty.id = lsm.sty_id;
-- Update the Contract Number using khr_id in the
-- Streams table. This will override the xtrx_contract
-- set in the block select above.
-- Initialize variables
l_temp_khr_id := NULL;
OPEN get_khr_id(cnr_update_tbl(cnr_tab_idx).lsm_id);
l_xlsv_rec.LSM_ID := cnr_update_tbl(cnr_tab_idx).lsm_id;
l_xlsv_rec.XTRX_CONS_STREAM_ID := cnr_update_tbl(cnr_tab_idx).lsm_id;
-- Update the XLS
-- Start of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.UPDATE_XTL_SELL_INVS
IF(IS_DEBUG_PROCEDURE_ON) THEN
BEGIN
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Xtl_Sell_Invs_Pub.UPDATE_XTL_SELL_INVS ');
UPDATE Okl_Xtl_Sell_Invs_b
SET LSM_ID = l_xlsv_rec.LSM_ID,
XTRX_CONS_LINE_NUMBER = l_xlsv_rec.XTRX_CONS_LINE_NUMBER,
XTRX_CONS_STREAM_ID = l_xlsv_rec.XTRX_CONS_STREAM_ID
WHERE id = l_xlsv_rec.id;
UPDATE Okl_Xtl_Sell_Invs_tl
SET XTRX_CONTRACT = l_xlsv_rec.XTRX_CONTRACT,
XTRX_ASSET = l_xlsv_rec.XTRX_ASSET,
XTRX_STREAM_TYPE = l_xlsv_rec.XTRX_STREAM_TYPE,
XTRX_STREAM_GROUP = l_xlsv_rec.XTRX_STREAM_GROUP
WHERE id = l_xlsv_rec.id;
UPDATE okl_ext_sell_invs_b
SET trx_status_code = 'ERROR'
WHERE id = cnr_update_tbl(cnr_tab_idx).xsi_id;
EXIT WHEN (cnr_tab_idx = cnr_update_tbl.LAST);
cnr_tab_idx := cnr_update_tbl.NEXT(cnr_tab_idx);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process = p_assigned_process;