An Update Query - Integration with Oracle E-Business Suite

An update query I wrote during the creation of a report. I want to share that query with all the readers of this blog, may be it will help you in creating reports.


create table #InvoiceData (Invoice_ID varchar(15),Customer_ID varchar(15),Invoice_Date datetime ,Due_Date datetime,Invoice_Amount float,Invoice varchar(10))
insert into #InvoiceData (Invoice_ID,Customer_ID,Invoice_Date,Due_Date,Invoice_Amount,Invoice)
SELECT DISTINCT Invoice_ID,Customer_ID,Invoice_Date,Due_Date,Invoice_Amount,Invoice
FROM OPENQUERY(OracleEBiz2,'select * from customer_invoice')
create table #temp (invoice_id varchar(25) ,amountpaid float ,amountrec float,aging int,invoicestatus varchar(25),invoicedate datetime, duedate datetime,customerid varchar(25))
insert into #temp(invoice_id)
select distinct invoice_id
from #InvoiceData
update #temp
set amountpaid = temp2.IA --(Select SUM(Inovice_Amount) from @InvoiceData WHERE Invoice='INVOICE' AND Invoice_ID = @temp.invoiceID)
from
(
Select invoice_id,ISNULL(sum(Invoice_Amount),0) IA,invoice
from #InvoiceData
where invoice='INVOICE'
group by invoice_id,invoice
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id

update #temp
set amountrec = temp2.IA --(Select SUM(Inovice_Amount) from @InvoiceData WHERE Invoice='INVOICE' AND Invoice_ID = @temp.invoiceID)
from
(
Select invoice_id,ISNULL(sum(Invoice_Amount),0.0) IA,invoice
from #InvoiceData
where invoice='RECEIPT'
group by invoice_id,invoice
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id

update #temp
set invoicedate = Invoice_Date,
duedate = Due_Date,
customerid = Customer_ID
from #temp,#InvoiceData
where #temp.invoice_id = #InvoiceData.invoice_id
/*update #temp
set
from #temp,#InvoiceData
where #temp.invoice_id = #InvoiceData.invoice_id
*/
update #temp
set aging = temp2.aging
from
(
select invoice_id,(case when (#temp.amountpaid = #temp.amountrec) then 0 else datediff(day,#temp.duedate,getdate()) end) aging
from #temp
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id
update #temp
set invoicestatus = temp2.[status]
from
(
select invoice_id,(case when (#temp.aging = 0) then 'Paid' else 'Pending' end) [status]
from #temp
)temp2
inner join #temp on #temp.invoice_id = temp2.invoice_id


select t.invoice_id,t.amountpaid,t.amountrec,t.aging,t.invoicestatus,convert(varchar(25),t.invoicedate,3)invoicedate ,convert(varchar(25),t.duedate,3) duedate, ab.name from #temp t
inner join accountbase ab on t.customerid COLLATE Latin1_General_CI_AS = ab.accountnumber
where aging <> 0
order by ab.name,invoice_id