Relation between SalesLine and GeneralJournalAccountEntry

Hello ,

For my business i have two case :

Case 1

I have created two subQuery and two tmp table

  • LedgerQuery that i get GeneralJournalAccountEntry.LEDGERDIMENSION => i insert list of field in LedgerQueryTableTmp
  • vendorTransQuery that i get AccountingDistribution.LEDGERDIMENSION .I insert list of field in vendorTransQueryTableTmp

For match two tables and insert the result in the finale table , i use ledgerDimension and voucher basedon this relation in PurchLine Table : datasource.addLink(fieldNum(AccountingDistribution, SOURCEDOCUMENTLINE), fieldNum(PurchLine,SOURCEDOCUMENTLINE));

So ,the fisrt case is ok.

Case 2

But , here i want to do something like case 1,Is there the some relation for SalesLine (between salesLine ,AccountingDistribution!)?

I have another query CustTransQuery and i want match it with the first query LedgerQuery

I have already GeneralJournalAccountEntry.LEDGERDIMENSION in LedgerQuery now i want get ledgerDimension in a table like AccountingDistribution.I don’t want use SalesLine.LedgerDimension because isn’t the same value.

I thank i’m looking for the relation between SalesLine and GeneralJournalAccountEntry. Now i have only the voucher but i need more that’s why i want ledgerDimension also

What creates entries in accounting are postings, such us when you post a packing slip or an invoice. That’s what you should focus on, in my opinion.

I need this information for a packing slip and an invoice.
This job works for the Case 1
static void SAUInvoicedPO(Args _args)
{
Query query;
QueryRun qRun;
QueryBuildDatasource datasource;

VendInvoiceTrans vendInvoiceTrans;
VendInvoiceJour vendInvoiceJour;
PurchLine purchLine;
AccountingDistribution accountDistribution;
PurchTable purchTable;

//PurchTable
query = new Query();
datasource = query.addDataSource(tableNum(PurchTable));

//VendInvoiceJour
datasource = datasource.addDataSource(tableNum(VendInvoiceJour));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);
datasource.addRange(fieldnum(VendInvoiceJour,PurchId)).value("=order0001");
datasource.addRange(fieldnum(VendInvoiceJour,InvoiceDate)).value(queryRange(19\4\2016,20\04\2016));

//VendInvoiceTrans
datasource = datasource.addDataSource(tableNum(VendInvoiceTrans));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);

//PurchLine
datasource = datasource.addDataSource(tableNum(PurchLine));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(false);
datasource.addLink(fieldNum(vendInvoiceTrans,PurchId), fieldNum(purchLine, PurchId));
datasource.addLink(fieldNum(vendInvoiceTrans, PurchaseLineLineNumber), fieldNum(purchLine,LineNumber));

//ACCOUNTINGDISTRIBUTION
datasource = datasource.addDataSource(tableNum(AccountingDistribution));
datasource.joinMode(JoinMode::InnerJoin);
datasource.addLink(fieldNum(accountingDistribution, SOURCEDOCUMENTLINE), fieldNum(purchLine,SOURCEDOCUMENTLINE));

qRun = new QueryRun(query);
while(qRun.next())
{
vendInvoiceTrans = qRun.get(tablenum(VendInvoiceTrans));
vendInvoiceJour = qRun.get(tablenum(VendInvoiceJour));
purchLine = qRun.get(tablenum(PurchLine));
accountDistribution = qRun.get(tablenum(AccountingDistribution));
purchTable = qRun.get(tablenum(PurchTable));

info(strfmt(’%1 ,%2 , %3, %4 %5 , %6, %7 %8 %9 %10 %11’,vendInvoiceJour.LedgerVoucher,
vendInvoiceTrans.PurchID,
vendInvoiceJour.InvoiceId,
vendInvoiceTrans.ItemId,
vendInvoiceTrans.Qty,
vendInvoiceTrans.PurchUnit
,PurchLine.REMAINPURCHFINANCIAL,
PurchLine.REMAINPURCHPHYSICAL,
purchLine.RemainPurchFinancial
,purchTable.PurchName,
purchLine.PurchStatus
));

}
}
So i need do the some for Sales ORDER ,i tried this for the CASE 2 :
static void getCustInvoicedTrans(Args _args)
{
Query query;
QueryRun qRun;
QueryBuildDatasource datasource;

CustInvoiceTrans custInvoiceTrans;
CustInvoiceJour custInvoiceJour;
SalesLine salesLine;
AccountingDistribution accountDistribution;
SalesTable salesTable;
MainAccount mAccount;
DimensionAttributeValueCombination dimAttrCombination;

//SalesTable
query = new Query();
datasource = query.addDataSource(tableNum(SalesTable));

//CustInvoiceJour
datasource = datasource.addDataSource(tableNum(CustInvoiceJour));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);
datasource.addRange(fieldnum(custInvoiceJour,SalesId)).value("=FRA1-000134");//FRA1-000134
//datasource.addRange(fieldnum(GENERALJOURNALENTRY,DOCUMENTDATE)).value(queryRange(4\5\2016,4\5\2016));

//custInvoiceTrans
datasource = datasource.addDataSource(tableNum(CustInvoiceTrans));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);

//SalesLine
datasource = datasource.addDataSource(tableNum(SalesLine));
datasource.joinMode(JoinMode::InnerJoin);
datasource.addLink(fieldNum(custInvoiceTrans,SalesId), fieldNum(SalesLine, SalesId));
//datasource.addLink(fieldNum(custInvoiceTrans, LineNum), fieldNum(SalesLine,LineNum));
datasource.addRange(fieldnum(SalesLine,SalesStatus)).value(enum2str(SalesStatus::Invoiced));
datasource.addRange(fieldnum(SalesLine,SalesStatus)).value(enum2str(SalesStatus::Delivered));

//DimensionAttributeValueCombination
datasource = datasource.addDataSource(tableNum(DimensionAttributeValueCombination));
datasource.joinMode(JoinMode::InnerJoin);
datasource.relations(true);

so where i can get a ledgerDimension that have the some value of generaljournalaccountEntry

I am giving you a query based on a scenario.

When you open the voucher form from Customer invoice journal, following is the query which is generated

SELECT FIRSTFAST * FROM GeneralJournalEntry(GeneralJournalEntry) ORDER BY GeneralJournalEntry.AccountingDate ASC, GeneralJournalEntry.JournalNumber ASC, GeneralJournalEntry.SubledgerVoucher ASC JOIN * FROM GeneralJournalAccountEntry(GeneralJournalAccountEntry) ON GeneralJournalEntry.RecId = GeneralJournalAccountEntry.GeneralJournalEntry JOIN * FROM DimensionAttributeValueCombination(DimensionAttributeValueCombination) ON GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeValueCombination.RecId JOIN * FROM MainAccount(MainAccount) ON DimensionAttributeValueCombination.MainAccount = MainAccount.RecId OUTER JOIN * FROM FiscalCalendarPeriod(FiscalCalendarPeriod) ON GeneralJournalEntry.FiscalCalendarPeriod = FiscalCalendarPeriod.RecId EXISTS JOIN * FROM SubledgerVoucherGeneralJournalEntry(SubledgerVoucherGeneralJournalEntry_1) WHERE GeneralJournalEntry.RecId = SubledgerVoucherGeneralJournalEntry.GeneralJournalEntry AND CustInvoiceJour.LedgerVoucher=SubledgerVoucherGeneralJournalEntry.Voucher AND CustInvoiceJour.InvoiceDate=SubledgerVoucherGeneralJournalEntry.AccountingDate

You can check with the tables linked in order to get the values from DimensionAttributeValueCombination table.

Similarly if you want more details on the tables which can be used, place a breakpoint on init method of LedgerTransVoucher form and just check how the queries are made.