• Replies 4 replies
  • Subscribers 218 subscribers
  • Views 116 views
  • Users 0 members are here
Related

GL Summary Report

Darrell Jones
$organization

Hello fellow Tessiturians,

In the GL Summary Report there is a column headed F/Year  (which I take to be Fiscal Year).

Can someone tell me where that reads from please?  Sales on a performance in 2010 are reading F/Year 2009 and I have look everywhere I can think of to identify a Fiscal Year of 2009 associated with the performance in 2010 - and drawn a blank.

Cheers Darrell

Parents
  • $organization

    Hi Darrell,

    Good day.

    GL Summary Report comes from one table only: T_GL_POSTING_HISTORY

    when you post a batch, catch the sql statement from profiler

    part 1:

    SELECT T_TRANSACTION.sequence_no,  
    T_TRANSACTION.transaction_no,  
    T_TRANSACTION.trn_dt,  
    T_TRANSACTION.trn_type,  
    T_TRANSACTION.trn_amt,  
    T_TRANSACTION.fund_no,  
    T_FEE_fee_gl_no = ISNULL(T_FEE.fee_gl_no, ''),  
    T_PMAP_gl_hold_no = ISNULL(T_PMAP.gl_hold_no, ''),  
    T_PMAP_gl_realize_no = ISNULL(T_PMAP.gl_realize_no, ''),  
    T_PMAP_gl_benevolent_no = ISNULL(T_PMAP.gl_benevolent_no, ''),  
    T_FUND_restricted_income_gl_no = ISNULL(T_FUND.restricted_income_gl_no, ''),  
    T_FUND_nr_income_gl_no = ISNULL(T_FUND.nonrestricted_income_gl_no, ''),  
    T_FUND_future_rec_gl_no = ISNULL(T_FUND.future_rec_gl_no, ''),  
    T_FUND_current_rec_gl_no = ISNULL(T_FUND.current_rec_gl_no, ''),  
    T_FUND_written_off_gl_no = ISNULL(T_FUND.written_off_gl_no, ''),  
    T_TRANSACTION.batch_no ,
    ISNULL(T_CAMPAIGN.fyear, 0 ),   
    batch_type_group = ISNULL(TR_BATCH_TYPE.batch_type_group, 0) ,
    T_TRANSACTION.pmap_no,
    T_TRANSACTION.fee_no
    FROM T_TRANSACTION
    LEFT OUTER JOIN T_FEE ON  T_TRANSACTION.fee_no = T_FEE.fee_no
    LEFT OUTER JOIN T_FUND ON T_TRANSACTION.fund_no = T_FUND.fund_no 
    LEFT OUTER JOIN T_PMAP ON T_TRANSACTION.pmap_no = T_PMAP.pmap_no
    JOIN T_CAMPAIGN  ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
    LEFT OUTER JOIN T_BATCH ON T_TRANSACTION.batch_no = T_BATCH.batch_no
    LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
    WHERE  T_TRANSACTION.batch_no = 6963 --my batch_no

    part 2:

    SELECT T_PAYMENT.transaction_no,  
    T_PAYMENT.payment_no,  
    T_PAYMENT.pmt_dt,  
    T_PAYMENT.pmt_amt,  
    T_PAYMENT.pmt_method ,
    asset_gl_no = ISNULL(TR_PAYMENT_METHOD.asset_gl_no, '') ,
    fyear = ISNULL(T_CAMPAIGN.fyear, 0) ,
    batch_type_group = ISNULL(TR_BATCH_TYPE.batch_type_group, 0)
    FROM T_PAYMENT 
    JOIN TR_PAYMENT_METHOD  ON T_PAYMENT.pmt_method = TR_PAYMENT_METHOD.id
    JOIN T_TRANSACTION ON T_PAYMENT.transaction_no = T_TRANSACTION.transaction_no
    and T_PAYMENT.sequence_no = T_TRANSACTION.sequence_no
    JOIN T_CAMPAIGN ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
    LEFT OUTER JOIN T_BATCH ON T_PAYMENT.batch_no = T_BATCH.batch_no
    LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
    WHERE T_PAYMENT.batch_no = 6963    --my batch_no


    So you can see, fyear is ISNULL(T_CAMPAIGN.fyear, 0)

     

    have fun

    Ben

Reply
  • $organization

    Hi Darrell,

    Good day.

    GL Summary Report comes from one table only: T_GL_POSTING_HISTORY

    when you post a batch, catch the sql statement from profiler

    part 1:

    SELECT T_TRANSACTION.sequence_no,  
    T_TRANSACTION.transaction_no,  
    T_TRANSACTION.trn_dt,  
    T_TRANSACTION.trn_type,  
    T_TRANSACTION.trn_amt,  
    T_TRANSACTION.fund_no,  
    T_FEE_fee_gl_no = ISNULL(T_FEE.fee_gl_no, ''),  
    T_PMAP_gl_hold_no = ISNULL(T_PMAP.gl_hold_no, ''),  
    T_PMAP_gl_realize_no = ISNULL(T_PMAP.gl_realize_no, ''),  
    T_PMAP_gl_benevolent_no = ISNULL(T_PMAP.gl_benevolent_no, ''),  
    T_FUND_restricted_income_gl_no = ISNULL(T_FUND.restricted_income_gl_no, ''),  
    T_FUND_nr_income_gl_no = ISNULL(T_FUND.nonrestricted_income_gl_no, ''),  
    T_FUND_future_rec_gl_no = ISNULL(T_FUND.future_rec_gl_no, ''),  
    T_FUND_current_rec_gl_no = ISNULL(T_FUND.current_rec_gl_no, ''),  
    T_FUND_written_off_gl_no = ISNULL(T_FUND.written_off_gl_no, ''),  
    T_TRANSACTION.batch_no ,
    ISNULL(T_CAMPAIGN.fyear, 0 ),   
    batch_type_group = ISNULL(TR_BATCH_TYPE.batch_type_group, 0) ,
    T_TRANSACTION.pmap_no,
    T_TRANSACTION.fee_no
    FROM T_TRANSACTION
    LEFT OUTER JOIN T_FEE ON  T_TRANSACTION.fee_no = T_FEE.fee_no
    LEFT OUTER JOIN T_FUND ON T_TRANSACTION.fund_no = T_FUND.fund_no 
    LEFT OUTER JOIN T_PMAP ON T_TRANSACTION.pmap_no = T_PMAP.pmap_no
    JOIN T_CAMPAIGN  ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
    LEFT OUTER JOIN T_BATCH ON T_TRANSACTION.batch_no = T_BATCH.batch_no
    LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
    WHERE  T_TRANSACTION.batch_no = 6963 --my batch_no

    part 2:

    SELECT T_PAYMENT.transaction_no,  
    T_PAYMENT.payment_no,  
    T_PAYMENT.pmt_dt,  
    T_PAYMENT.pmt_amt,  
    T_PAYMENT.pmt_method ,
    asset_gl_no = ISNULL(TR_PAYMENT_METHOD.asset_gl_no, '') ,
    fyear = ISNULL(T_CAMPAIGN.fyear, 0) ,
    batch_type_group = ISNULL(TR_BATCH_TYPE.batch_type_group, 0)
    FROM T_PAYMENT 
    JOIN TR_PAYMENT_METHOD  ON T_PAYMENT.pmt_method = TR_PAYMENT_METHOD.id
    JOIN T_TRANSACTION ON T_PAYMENT.transaction_no = T_TRANSACTION.transaction_no
    and T_PAYMENT.sequence_no = T_TRANSACTION.sequence_no
    JOIN T_CAMPAIGN ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
    LEFT OUTER JOIN T_BATCH ON T_PAYMENT.batch_no = T_BATCH.batch_no
    LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
    WHERE T_PAYMENT.batch_no = 6963    --my batch_no


    So you can see, fyear is ISNULL(T_CAMPAIGN.fyear, 0)

     

    have fun

    Ben

Children
No Data