#Debit credit table full

Sort excel by CustID then trnNo then rowID (It will need more work though as only one customer has paid but this might help you) RecdOn = replacing with trnDate every time when knock off take placeįrom this intermediateResultTable FinalResult Table will derived RecdAmt = sum of all knock off amount against a particular Debit entry No Credit entry found of custID : a2 thats why RecdAmt is 0 and RecdOn if null Remaining 10000 of BNK will be knock off and RecdOn is Remaining 10000 of BNK-3 will be knock off in next invoiceĪ. IIIrd BNK-3 will knock off against INV-1 with partial 5000 only and RecdOn is trnDate=

#Debit credit table full

IInd BNK-2 will knock off against INV-1 with full 15000 and RecdOn is trnDate=Ĭ. Ist BNK-1 will knock off against INV-1 with full 50000 and RecdOn is trnDate= ī.

Resident intTab group by iCustId, iInvNo, iInvDate, iAmount Įach line in result table is derived from trnTable where trnType=Dr, means each Debit entries of each customer.Ģ.RecdAmt : Shows Amount knock off / received against a particular invoice on FIFO basisĪ. If vDrRow > vDrRecords then vCounter=2 endif //- Checking Infinity loop row counter If vBalAmt=vCrRecords then //- Checking Credit trn.

ICustId, iInvNo, iInvDate, iAmount, iCrAmt, iCrDate IntTab: //- Inserting records in to intermedieate result table VCrAmt=if(vBalAmt>0,vCrAmt,(vCrAmt+vBalAmt)) If vCrFlg=0 then //- Protecting from every time read from credit trn. Load trnDate as docDate, trnAmount as crAmt Resident trnTab where trnType='Cr' and custId = '$(vDrCustId)' order by custId,trnDate If NoOfRows('crTab')>0 then DROP Table crTab endif If vCrCustIdvDrCustId then //- Debit custId changed then drop credit trn table and load credit trn records in credit trn table If vCrCustIdvDrCustId then vCrCustId='' endif //- seting custId VDrCustId=peek('drCustId',vDrRow,'drTab') If vDrFlg=0 then //- Protecting from every time read from debit trn. Let vCounter=1 let vDrFlg=0 let vDrRow=0 let vCrFlg=0 let vCrCustId='' vCrRow=0 let vDrRecords=NoOfRows('drTab')-1 Note : With in infinity loop flow is controled by if then else blocks.įor me it not possible to wright each and every steps of logic here any one has to understand it. if yes then repeate steps from step no 3 but for next row After each insert, check whether debit entry fully knock off or not then again if not then then repeate steps 5,6,7Ĩ. Knock off Debit previously read debit record with credit record and insert each knocked off record in Intermediate result tableħ. Read First entry from Credit records tableĦ. Extrect all Credit records in a separate table say crTab for just previously read custId and set vCrFlg=0ĥ.

Table and set vDrFlg variable value to 1Ĥ. Start a infinity loop and With in this loopģ. Declare some variabale / flage and set there values to 0Ģ. Invoice Details in a separate Table say drTabĢ. Logic Of Data Transformation : This solution is based on structural programming gimmicksġ. Resident trnTab where trnType='Dr' order by custId,trnDate I will try to suggestion on this from Mr. I could not found other better way may others have.īut I am not satisfied with this salutation because it will reduce the performance due to havey use of if then else block. Solution to your query is based on structural programming approach.