How to get Invoice amount field of (CustInvoicetrans.Invoice Amount) , delivered from (CustPackingSlipTrans.Qty)

Hi Experts ,

I want get the value of Invoice amount (CustinvoiceTrans.Invoice amount ) in the excel sheet .

this sales order invoice created from sales quotation .

I want know the what is the relation between custquotationjour and CustinvoiceTrans or Custquotationtrans and CustinvoiceTrans?

Paths for :

DISPATCH QTY :Packing slip Journal >> lines >>delivered(sum ) (CustPackingSlipTrans).Qty)

"DISPATCH VALUE: Invoice Journal >> amount field line level (CustInvoicetrans.Invoice Amount)

"BALANCE AGAINST PPAP QTY" : so lines >> Updatelines >>delivery Remainder field .

I am unble find the relation of this tables . please suggest how will get the this values in the Excel sheet .

I attached image of My Query also

private void run()
{
CommaIo io;
Dialog dialog;
DialogField dlgFileName;
Filename csvfileName;
FileIOPermission permission;
int recordCount = 0;
int i=0;
String20 quotaionJourId;
int strLength;
AccountNum accountNum;
SalesTaker pesron;
int j;
utcDateTime Crddate;

SysOperationProgress exportprogress = new SysOperationProgress();

#File
#AviFiles

try
{
exportprogress.setCaption(“Exporting quotation lines to CSV file…”);
exportprogress.setAnimation(#AviTransfer);

// Header Column

conHeader = [“CUSTOMER NAME”,“PART NO”,“PART DETAILS”,“PRICE”,“QUOTATION NO”,“CARD ISSUED DATE”,“CRD DATE” ," crd date", “PPAP QTY”,“TOTAL PPAP VALUE”,“DISPATCH QTY”,“DISPATCH VALUE”,“BALANCE AGAINST PPAP QTY”,“DISPATCH QTY per DATE”,“Remarks”];
dialog = new Dialog(“Exporting quotation lines”);
dialog.filenameLookupFilter([“Excel Files”,"*.csv"]);
dlgFileName = dialog.addField(extendedTypeStr(FilePath),“File Path”);

queryRun = new QueryRun(queryStr(PC_PPAPCard));

if(dialog.run())
{

csvfileName = strFmt(@"%1%2%3_%4.csv",dlgFileName.value(), “PPAP Details”, date2str(today(),321,2,0,2,0,4),strRem(time2Str(timeNow(), TimeSeparator::Colon, TimeFormat::Hour24), “:”));
if(csvfileName)
{
permission = new FileIOPermission(csvfileName,#io_write);
permission.assert();

io = new CommaIo(csvfileName,#io_write);

io.inFieldDelimiter(’,’);

if(!io || io.status() != IO_Status::Ok)
{
throw error(“Error in opening file!”);
}

io.writeExp(conHeader);

if(queryRun.prompt())
{
while( queryRun.next())
{

custQuotationJour = queryRun.get(tableNum(CustQuotationJour));
custQuotationTrans = queryRun.get(tableNum(CustQuotationTrans));
inventtable = queryRun.get(tableNum(InventTable));
salesQuotationLine = queryRun.get(tableNum(SalesQuotationLine));
salesQuotationTable = queryRun.get(tableNum(salesQuotationTable));

custTable = CustTable::find(custQuotationJour.InvoiceAccount);

inventTable = InventTable::find(custQuotationTrans.ItemId);

select ExternalItemID from custVendExternalItem
where custVendExternalItem.CustVendRelation == custQuotationJour.OrderAccount
&& custVendExternalItem.ModuleType == 4
&& custVendExternalItem.ItemId == inventTable.ItemId;

select InvoiceAmount from CustInvoiceJour
where CustInvoiceJour.SalesId == CustQuotationJour.SalesId;

if (custQuotationJour.PC_QuotationStage == PC_ItemStage::PPAP)
{

conQuotationData = [ custQuotationJour.OrderAccount,// 1 customer Name
custVendExternalItem.ExternalItemId,// 2 part no
inventTable.itemName(),// 3 part name
custQuotationTrans.SalesPrice,// 4 price
custQuotationTrans.quotationDocNum(),//5 quotation number
custQuotationJour.QuotationDate,// 6 card issue date
salesQuotationTable.createdDateTime,//7 CRD DATE
custQuotationTrans.Qty,// 8 PPAP Qty
CustQuotationTrans.lineAmountInclTax(), // 9 Total PPAP Value
CustInvoiceJour.InvoiceAmount,// 11 dispatch value
salesquotationline.PC_SpecialRemarks// 14 remarks

];

io.writeExp(conQuotationData);

}

}

CodeAccessPermission::revertAssert();
info(strFmt(“Report generated and saved. Path: %1”, csvfileName));
}
else
{
info(“Invalid file name specified!”);
}
}
}
}
catch(Exception::Error)
{
throw error(“Erorr in exporting journal lines.”);
}

}

I want to print above Red highlighted in the excel

i am unable to find the relation the of this 3 field of tables .

pastedimage1519591966276v1.png

You can use InventTrans to get the related custPackingSlipTrans.
InventTrans has invoice id and invoice date (dateFinancial on InventTrans), the same record will have the packing slip id and packing slip date (datePhysical on InventTrans).