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 .