Get payment detail in Report for sales invoice

brijeshvaidya

Hello friends,
i want to get the information regarding the payment made by the customer against the sales invoice.

i want to customize the sales invoice report with these field added to the report.

i tried this things:

create the cash receipt journal,
then post it and check the customer ledger entries.

but how to get the y amount of payment made against the sales invoice no. x with z date?

what are the table and column name those are relate to the sales invoice header table,
so that i can relate it and get the payment amount and the payment date in the customized sales invoice report.

colingbradley

The Detailed Customer Ledger Entry (Table 379) will have the payment detail record but you could use the Customer Ledger Entry, link by Document No. > No. and use the Flowfield for the amount and remaining amount.
All the values to do with payments in the Customer Ledger Entry table are flowfields.
So you must use the CALCFIELDS() and insert the fields you need.
You can lookup multiple payments from Table 379 the Detailed entries and add that to the report so you will be able to print the invoice with each payment.

If the payment is a prepayment than you have a problem as there is no direct link to the posted sales invoice.
You would have to have a new field in the Sales Header and Sales Invoice Header such as "Pre-payment No." but that would only allow one prepayment.

If you have many, you would need to create a table to hold the prepayments and use the original order number for example as the link.

Cheers,
Colin

brijeshvaidya

Thank you very much... colingbradley
it is solved.....


i took the payment detail from the detailed customer ledger entry table
and the remaining amount from the customer ledger entry table...

here is the code:


DCLE - OnPreDataItem()
DCLE.SETCURRENTKEY(DCLE."Entry No.");
DCLE.SETRANGE(DCLE."Cust. Ledger Entry No.","Cust. Ledger Entry2"."Entry No.");
DCLE.SETFILTER(DCLE."Entry Type",FORMAT(DCLE."Entry Type"::Application));
IF DCLE.FIND('-') THEN BEGIN
REPEAT
MESSAGE(FORMAT(DCLE."Credit Amount"));
UNTIL DCLE.NEXT = 0;
END;

and


for customer ledger entry table...



Cust. Ledger Entry2 - OnPreDataItem()
"Cust. Ledger Entry2".SETCURRENTKEY("Cust. Ledger Entry2"."Entry No.");
"Cust. Ledger Entry2".SETRANGE("Cust. Ledger Entry2"."Customer No.","Sales Invoice Header"."Bill-to Customer No.");
"Cust. Ledger Entry2".SETRANGE("Cust. Ledger Entry2"."Document No.","Sales Invoice Header"."No.");
IF "Cust. Ledger Entry2".FIND('-') THEN BEGIN
REPEAT
"Cust. Ledger Entry2".CALCFIELDS("Cust. Ledger Entry2"."Remaining Amount");
UNTIL "Cust. Ledger Entry2".NEXT = 0;
END;

and show in the section of the dcle and cle tables...