General Ledger report

Question

I am Working on SSRS custom report that show general ledger and and need to show in the report all transaction for account and sub-Account and the vendor or customer, for example if the transaction come from Purchase Order then I need to show the vendor, and if come from Sales Order then I need to Show the customer.

I Know the relation between Purchase Order and GeneralGournalAccountEntry but I need the relation between GeneralGournalAccountEntry to show the custoomer.

Answer 1

To know if it comes from Sales Order check field JournalCategory from GeneralJournalEntry table with the value Sales.

To know customer I Leave this code:

static void stackoverflow(Args _args)
{
    GeneralJournalAccountEntry GeneralJournalAccountEntry;
    GeneralJournalEntry        GeneralJournalEntry;
    CustInvoiceJour            CustInvoiceJour;
    CustTable                  CustTable;

    ;

    select * from GeneralJournalAccountEntry where GeneralJournalAccountEntry.RecId == 88888888 //Your GeneralJournalAccountEntry recId
        join GeneralJournalEntry where GeneralJournalEntry.RecId           == GeneralJournalAccountEntry.GeneralJournalEntry &&
                                       GeneralJournalEntry.JournalCategory == LedgerTransType::Sales //Sales Order                                            
        join CustInvoiceJour     where CustInvoiceJour.LedgerVoucher       == GeneralJournalEntry.SubledgerVoucher
        join CustTable           where CustTable.AccountNum                == CustInvoiceJour.InvoiceAccount;

    info(CustTable.AccountNum);
}