[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date l_date_tbl;
SELECT /*+ leading(BTN,BTXNL,RALINES,HDR,RAHDR) use_nl(BTXNL,HDR,RAHDR) use_hash(RATYPES) swap_join_inputs(RATYPES) parallel(RALINES) */
btn.id txn_id
,btn.currency_code
,btxnl.id txn_lines_id
,btxnl.bill_instance_number bill_instance_number
,btxnl.btn_id btn_id
,btxnl.bcl_id
,btxnl.bsl_id
,btxnl.split_flag
,hdr.Contract_number
,hdr.Contract_number_modifier
,hdr.last_update_date
,hdr.id hdr_id
,rahdr.trx_number
,ralines.customer_trx_line_id
,ralines.customer_trx_id
,ralines.extended_amount
,ratypes.type
,rahdr.trx_date
From oks_bill_transactions btn
,oks_bill_txn_lines btxnl
,RA_CUSTOMER_TRX_LINES RALINES
,okc_k_headers_all_b hdr
,RA_CUSTOMER_TRX_ALL RAHDR
,RA_CUST_TRX_TYPES_ALL RATYPES
Where btxnl.btn_id = btn.id
And btn.trx_number = '-99'
AND RALINES.line_type ='LINE'
/* Commented by sjanakir for Bug#7190512
And RAHDR.interface_header_attribute1 = hdr.contract_number
And RAHDR.interface_header_attribute2 = NVL(hdr.contract_number_modifier,'-') */
And RALINES.interface_line_attribute1 = hdr.contract_number
And RALINES.interface_line_attribute2 = NVL(hdr.contract_number_modifier,'-')
And RALINES.interface_line_attribute3 = to_char(btxnl.bill_instance_number)
And RAHDR.customer_trx_id = RALINES.customer_trx_id
And RATYPES.cust_trx_type_id = RAHDR.cust_trx_type_id
And RALINES.interface_line_context = 'OKS CONTRACTS'
And ralines.org_id = HDR.org_id
And ralines.org_id = RAHDR.org_id
And ralines.org_id = RATYPES.org_id
ORDER BY btxnl.bill_instance_number ;
Select distinct hdr_id
From oks_ar_fetch_temp;
SELECT id txn_lines_id
,bill_instance_number
,btn_id
,bcl_id
,bsl_id
,trx_amount
FROM oks_bill_txn_lines
WHERE bill_instance_number = p_bill_instance_num;
SELECT nvl(sum(ctl.extended_amount),0 )
FROM RA_CUSTOMER_TRX_LINES_ALL CTL
WHERE CTL.LINK_TO_CUST_TRX_LINE_ID = p_id
AND CTL.line_type = 'TAX';
,l_last_update_date
,l_hdr_id
,l_trx_number
,l_customer_trx_line_id
,l_customer_trx_id
,l_extended_amount
,l_type
,l_trx_date limit 1000;
update oks_bill_transactions
set trx_date = l_trx_date(i)
,trx_number = l_trx_number(i)
,trx_amount = nvl(trx_amount,0) + l_extended_amount(i)
,trx_class = l_type(i)
,last_updated_by = user_id
,last_update_date = sysdate
where id = l_txn_id(i);
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update failed on OKS_BILL_TRANSACTIONS , SQLERRM = '|| SQLERRM);
/*****chk the split flag ,if null just update as usual else if 'P' then
retrieve all records from btl with same bill_instance_number and prorate the tax and inv amt.
*******/
IF l_split_flag(i) IS NULL THEN
/*******Added for P1 bug#4089706. chk previous bill_instance_number if not same then
update trx_line_amt and tax_amt to 0 so that for the price break records
amt can be added.But later on we have to identify these records******/
IF l_txn_lines_id(i) <> nvl(l_previous_btn,0) then
UPDATE oks_bill_txn_lines
SET trx_line_tax_amount = 0
,trx_amount = 0
,trx_line_amount = 0
WHERE id = l_txn_lines_id(i);
before it been added directly from sql in update statment****/
l_tot_tax_amt := 0;
UPDATE oks_bill_txn_lines
SET trx_class = l_type(i)
,trx_number = l_trx_number(i)
,trx_date = l_trx_date(i)
,trx_line_tax_amount = nvl(trx_line_tax_amount,0) + nvl(l_tot_tax_amt,0)
,trx_amount = nvl(trx_amount,0) + l_extended_amount(i)
,trx_line_amount = nvl(trx_line_amount,0) + l_extended_amount(i)
,last_updated_by = user_id
,last_update_date = sysdate
WHERE id = l_txn_lines_id(i);
l_btl_tbl.DELETE;
UPDATE oks_bill_txn_lines
SET trx_class = l_type(i)
,trx_number = l_trx_number(i)
,trx_date = l_trx_date(i)
,trx_amount = l_line_trx_amt
,trx_line_amount = l_line_trx_amt
,trx_line_tax_amount = l_line_tax_amt
,last_updated_by = user_id
,last_update_date = sysdate
WHERE id = l_btl_tbl(l_index).txn_lines_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Update failed on OKS_BILL_TXN_LINES , SQLERRM = '|| SQLERRM);
insert into oks_ar_fetch_temp o
( hdr_id )
values ( l_hdr_id(k));
FND_FILE.PUT_LINE(FND_FILE.LOG,'Insert failed on OKS_AR_FETCH_TEMP , SQLERRM = '|| SQLERRM);
OKC_CVM_PVT.update_contract_version( p_api_version => 1.0,
p_init_msg_list => 'T',
x_return_status => l_ret_stat,
x_msg_count => l_msg_cnt,
x_msg_data => l_msg_data,
p_cvmv_rec => l_cvmv_rec,
x_cvmv_rec => l_cvmv_out_rec);