Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_identify_deposits_in_a_make_deposit_batch [2019/01/27 11:29] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== Explanation of SQL =====
 +
 +
 +
 +Given the JournalID of a make deposit entry, this query shows all of the payments that went into that Make Deposit. ​ It also includes the make deposit entry itself (this is normally the top entry).
 +
 +
 +
 +===== Risk of Data Corruption if Run Improperly =====
 +
 +**None**. This is a selection query and no data is modified in the running of it.
 +
 +
 +
 +===== SQL =====
 +
 +
 +
 +<code sql>
 +-- Identify the transactions for a make-deposit sequence
 +declare @DepositJournalID int;
 +set @DepositJournalID = 102794;
 +select ID, EntryDateTime,​ Amount,  ​
 + (Select AccountName from GLAccount where ID = GLAccountID) as AccountName,​
 + (select OrderNumber from TransHeader where ID = TransactionID) as OrderNumber,​
 +        (select AccountName from PaymentAccount where ID = Classification) as PaymentType,​
 + IsModified,​ Reconciled, ​
 + GLAccountID,​ Classification, ​
 +     GroupID, TransactionID,​ JournalID, ReconciliationID,​ OffBalanceSheet,​ DepositJournalID,​
 +        (select Description from Journal where ID = JournalID) as Description,​
 +        (select Notes from Journal where ID = JournalID) as Notes,
 + ModifiedDate
 +from Ledger
 +where (JournalID = @DepositJournalID) ​
 +      or (DepositJournalID = @DepositJournalID and GLClassificationType = 1007 )
 +order by
 +id desc
 +</​code>​
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 9/2011
 +  * Version : Control 4.4+
 +
 +
 +