Summarizing from temp table to Sales Invoice

satyajit

Hi,

We have created a temporary table in which we have a set of data. This data is pulled from another software. We need to sumamrize the transactions and create a sales invoice from the same. How do we do it? Please find below sample data.

Transaction ID Date Customer ID Type Quantity Category
1 14/07/2010 801 Invoice 2 A
2 14/07/2010 801 Invoice 4 B
3 14/07/2010 801 Invoice 7 A
4 14/07/2010 802 Invoice 5 B
5 14/07/2010 802 Invoice 6 B
6 14/07/2010 802 Invoice 1 A


So the first Invoice will be with Customer 801 and the Sales Line would be 2 lines with G/L Account----
1 for Category A (which is directly the mapped to dimension and which in turn is mapped to GL Account) with quantity total as 9 and 2nd line as Category B with quantity total as 4.

2nd Invoice would be for Customer 802 with 1 sales line as Category A with quantity total as 1 and 2nd line as Category B with quantity total as 11.

Please help how the code needs to be written for the same.

upasanisandip

Hi satyajit,
You can sort on invoice no. first
801
802
then pass the filter on category A you will get 2 records for 801.
in a temporary variable you can take the total & insert into the table.
for e.g.
temptable.setcurrentkey(invoiceno);
repeat
temptable2.reset;
temptable2.setfilter(temptable.invoiecno,temptable1.invoiceno);
temptable2.setfilter(temptable.category='A');
if temptable2.find('-') then
repeat
// take the totals in variables.
until temptable2.next=0;

temptable2.reset;
temptable2.setfilter(temptable.invoiecno,temptable1.invoiceno);
temptable2.setfilter(temptable.category='B');
if temptable2.find('-') then
repeat
// take the totals in variables.
until temptable2.next=0;

until temptable1.next=0;
You will get the total @ the end for category A & for 1 invoice no. 801. In next loop you will get the total for B for same invoice no.
Hope this help you.