Hello Corbett, Darkfour, and Stewart,
Thank you for your big response. I am overwhelmed. But there is one thing which I have noticed in the new script given by Stewart is the join condition. Stewart is joining detail and detail detail table. So there will be 5 records created in cursor and I need 5 records to be processed in loop of taxdetail for every 1 record of canteeninvoicedetl. So I think there is a mistake there. In the cursor taxdetail is the detail detail table and canteeninvoicedetl is the detail table so for every canteeninvoicedetl single record there are 5 records of taxdetail.
I will explain in a bit depth here
canteeninvoicedetl table
co collegecd yearcd basic taxcd
1 1 1 5000 78
taxdetail table
co collegecd taxcd accid seqno perc onseq1 onseq2 onseq3 onseq4 onseq5
1 1 78 100 1 12.5 0 null null null null
1 1 78 105 2 4 0 1 null null null
1 1 78 107 3 2 2 null null null null
1 1 78 112 4 1 2 3 null null null
1 1 78 109 5 1.5 1 4 null null null
when the first record in canteeninvoicedetl come then then taxdetail 5 records will be retrieved from taxdetail table ordered by sequence in first sequence (seqno column)
1. the first sequence onseq1 is 0 means on basic
2. the second sequence onseq1 is 0 and onseq2 is 1 means 4 percent on basic+first value i.e 12.5%
3. the third sequence onseq1 is 2 means whatever value is calculated for sequence 2 it calculates 2 percent on that value.
4. the fourth sequence onseq1 is 2 and onseq2 is 3 means whatever values are calculated for sequence 2 and 3 it calculates 1% of that and stores in fourthvalue.
5. the fifth sequence onseq1 is 1 and onseq2 is 4 means whatever values are calculated for sequence 1 and 4 it calculates 1.5% of that and saves in fifthvalue.
This should be in a inside loop for of taxdetail for every record of canteeninvoicedetl.
so first canteeninvoicedetl loop and for every fetch the taxdetail loop.
I hope it is clear now. To my knowledge the stewart program I am not able to compile. Any help would be appreciated as per this logic. I have fitted this logic in oracle and is getting compiled successfully.
Please help.