Reporting the payments received against shipment’s C&F value.

In this example our client was facing an  issue where shipments are made  and C&F value amount is taken as Accounts Receivable, payments are received in 2 to 3  installments.  Management wants to know how much is outstanding against each shipment C&F Invoice.

In database invoice is generated on C&F basis where comprehensive information is recorded like invoice no,  Bill of Lading No, C&F Amount, Shipment NO and so on.

When Payments are received it is recorded exportpayments table along with shipment NO.

In order to create a report to what is outstanding against respective invoice we created a SQL Query  using select into Transact-SQL  as follows;

select sn1, sum(amt) as cfvl

 into #sd4

from dbo.exportpayments

group by sn1

Select Sn, CFvlu,sn1, cfvl, Cfvlu-cfvl as balance from

 dbo.invocesexpressions inner join #sd4 on

dbo.invocesexpressions.sn = #sd4.sn1

drop table #sd4

In the above example we selected Shipment NO SN1 and sum amount Sum(amt) as cfvlu

Into temp table SD4 and then we selected Shipment no SN, CFVLU from invoices table and CFVL from Temp table #SD4 and join Invoices table and #sd4 based on Shipment NO

At the end we dropped thetemp table  #SD4.

This example utilized the temp table for generating outstanding payments report.

Shipmnt              CFVLU          Shpmnt  Amnt RCD       Blnc