Export of Posting Information

Teresa,

Give this a try in your test environment – it should do what you want it to do:

--sum records by post_no,gl account and type

insert into lt_gl_dataport

SELECT post_dt =  (substring(convert(char(8),post_dt,112),5,2) +

          substring(convert(char(8),post_dt,112),7,2) +

          substring(convert(char(8),post_dt,112),1,4) ),

    post_no,

    gl_act_no ,

    description = convert(char(5),post_no) + ' ' + gl_act_desc,

--    amt = CASE when type = 'D' then sum(amt) when type = 'C' then sum(amt) * -1 end,

      amt = SUM(case when type = 'D' then amt when type = 'C' then (amt * -1) end),

    budget_code = ' ',

    curr_code = ' '

FROM    #t_gl_dataport

--group by post_dt,post_no, gl_act_no, type,convert(char(8),post_dt,112), gl_act_desc

group by post_dt,post_no, gl_act_no, convert(char(8),post_dt,112), gl_act_desc

--order by post_dt,post_no, gl_act_no, type

order by post_dt,post_no, gl_act_no

-steve carlock

Information Technology Manager

Santa Barbara Center for the Performing Arts

1330 State Street, Suite 101

Santa Barbara, CA 93101

(805) 899-3000 x 111 (phone)

(805) 899-3081 (fax)

We have just transitioned to a new finance system and I'm working on formatting an export file to be automatically imported into the finance system nightly.  I have everything formatted and exporting with no issues except for one thing....my finance department needs the data to be a net amount per GL number not a sum of the debits and a sum of the credits as my procedure is currently doing.  I basically have up to two lines per GL number in the export and need to get it to only 1 line per GL that is the NET amount.  I'm having issues figuring out how to code this since I'm not really a DBA.

Can anyone point me in the direction of now to get the debits and credits calculated to give me the NET.  I've attached the "meat" of my code for reference.  Thanks in advance for the help.

Teresa

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LCP_DATAPORT_GL_TKT]    Script Date: 08/30/2011 09:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER                PROCEDURE [dbo].[LCP_DATAPORT_GL_TKT]
--(@post_dt_start datetime,
-- @post_dt_end datetime
--)
 
AS

Set NoCount On

/*****************************************************************************
one time only to keep track of which post no's have been transferred to Navision

--truncate table lt_gl_dataport_hist
create table lt_gl_dataport_hist
     (post_no int,
      create_dt datetime,
      created_by varchar(8) ,
    constraint gl_navision_pk unique  (post_no )   )
******************************************************************************/

create table #t_gl_dataport
    (gl_act_no    char(30),
    gl_act_desc    varchar(30),
    post_no        int,
    post_dt        datetime,
    type        char(1),
    amt        money,
    )
truncate table lt_gl_dataport   

--select records based on start and end date parameters and verify that post
--does not exist in lt_gl_dataport_hist to be sure it has not previously
--been transferred to Navision

declare @post_dt_start datetime
declare @post_dt_end datetime

set @post_dt_start = dateadd(day, datediff(day, 0, getdate()),-10)
set @post_dt_end = dateadd(day, datediff(day, 0, getdate()),0) 
 
insert into #t_gl_dataport
SELECT  a.gl_act_no ,         
    gl_description = ISNULL(b.gl_description, '***UNKNOWN***'),
    a.post_no,
    a.post_dt,
    a.type ,          
    sum(a.post_amt)
FROM    T_GL_POSTING_HISTORY a (nolock)
    LEFT OUTER JOIN T_GL_ACCOUNT b  (nolock) ON a.gl_act_no  =  b.gl_account_no 
WHERE     a.post_dt between @post_dt_start and @post_dt_end
and not exists(select post_no from lt_gl_dataport_hist c
    where a.post_no = c.post_no)
and a.batch_type_group = 9
group by a.gl_act_no, b.gl_description,a.post_no, a.post_dt, a.type
order by a.type, a.gl_act_no, a.post_no
 
 
--sum records by post_no,gl account and type
insert into lt_gl_dataport
SELECT post_dt =  (substring(convert(char(8),post_dt,112),5,2) +
          substring(convert(char(8),post_dt,112),7,2) +
          substring(convert(char(8),post_dt,112),1,4) ),
    post_no,
    gl_act_no ,
    description = convert(char(5),post_no) + ' ' + gl_act_desc,
    amt = CASE when type = 'D' then sum(amt) when type = 'C' then sum(amt) * -1 end,
    budget_code = ' ',
    curr_code = ' '
FROM    #t_gl_dataport
group by post_dt,post_no, gl_act_no, type,convert(char(8),post_dt,112), gl_act_desc
order by post_dt,post_no, gl_act_no, type

--update table of post no's dataported to Navision to avoid duplication
insert into lt_gl_dataport_hist
(post_no,create_dt,created_by)
select distinct post_no,
    getdate(),
    user_name()
from lt_gl_dataport

--select data for report
SELECT post_dt =   substring(post_dt,1,2)  + '/' +
          substring (post_dt,3,2 ) + '/' +
          substring (post_dt,5,4),
    post_no,
    gl_act_no,
    ' ', --see comments above
    substring(description,6,44) ,
    amt  ,
    budget_code = ' ',
    curr_code = ' '
  from lt_gl_dataport

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Teresa,

Give this a try in your test environment – it should do what you want it to do:

--sum records by post_no,gl account and type

insert into lt_gl_dataport

SELECT post_dt =  (substring(convert(char(8),post_dt,112),5,2) +

          substring(convert(char(8),post_dt,112),7,2) +

          substring(convert(char(8),post_dt,112),1,4) ),

    post_no,

    gl_act_no ,

    description = convert(char(5),post_no) + ' ' + gl_act_desc,

--    amt = CASE when type = 'D' then sum(amt) when type = 'C' then sum(amt) * -1 end,

      amt = SUM(case when type = 'D' then amt when type = 'C' then (amt * -1) end),

    budget_code = ' ',

    curr_code = ' '

FROM    #t_gl_dataport

--group by post_dt,post_no, gl_act_no, type,convert(char(8),post_dt,112), gl_act_desc

group by post_dt,post_no, gl_act_no, convert(char(8),post_dt,112), gl_act_desc

--order by post_dt,post_no, gl_act_no, type

order by post_dt,post_no, gl_act_no

-steve carlock

Information Technology Manager

Santa Barbara Center for the Performing Arts

1330 State Street, Suite 101

Santa Barbara, CA 93101

(805) 899-3000 x 111 (phone)

(805) 899-3081 (fax)

We have just transitioned to a new finance system and I'm working on formatting an export file to be automatically imported into the finance system nightly.  I have everything formatted and exporting with no issues except for one thing....my finance department needs the data to be a net amount per GL number not a sum of the debits and a sum of the credits as my procedure is currently doing.  I basically have up to two lines per GL number in the export and need to get it to only 1 line per GL that is the NET amount.  I'm having issues figuring out how to code this since I'm not really a DBA.

Can anyone point me in the direction of now to get the debits and credits calculated to give me the NET.  I've attached the "meat" of my code for reference.  Thanks in advance for the help.

Teresa

USE [impresario]
GO
/****** Object:  StoredProcedure [dbo].[LCP_DATAPORT_GL_TKT]    Script Date: 08/30/2011 09:10:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER                PROCEDURE [dbo].[LCP_DATAPORT_GL_TKT]
--(@post_dt_start datetime,
-- @post_dt_end datetime
--)
 
AS

Set NoCount On

/*****************************************************************************
one time only to keep track of which post no's have been transferred to Navision

--truncate table lt_gl_dataport_hist
create table lt_gl_dataport_hist
     (post_no int,
      create_dt datetime,
      created_by varchar(8) ,
    constraint gl_navision_pk unique  (post_no )   )
******************************************************************************/

create table #t_gl_dataport
    (gl_act_no    char(30),
    gl_act_desc    varchar(30),
    post_no        int,
    post_dt        datetime,
    type        char(1),
    amt        money,
    )
truncate table lt_gl_dataport   

--select records based on start and end date parameters and verify that post
--does not exist in lt_gl_dataport_hist to be sure it has not previously
--been transferred to Navision

declare @post_dt_start datetime
declare @post_dt_end datetime

set @post_dt_start = dateadd(day, datediff(day, 0, getdate()),-10)
set @post_dt_end = dateadd(day, datediff(day, 0, getdate()),0) 
 
insert into #t_gl_dataport
SELECT  a.gl_act_no ,         
    gl_description = ISNULL(b.gl_description, '***UNKNOWN***'),
    a.post_no,
    a.post_dt,
    a.type ,          
    sum(a.post_amt)
FROM    T_GL_POSTING_HISTORY a (nolock)
    LEFT OUTER JOIN T_GL_ACCOUNT b  (nolock) ON a.gl_act_no  =  b.gl_account_no 
WHERE     a.post_dt between @post_dt_start and @post_dt_end
and not exists(select post_no from lt_gl_dataport_hist c
    where a.post_no = c.post_no)
and a.batch_type_group = 9
group by a.gl_act_no, b.gl_description,a.post_no, a.post_dt, a.type
order by a.type, a.gl_act_no, a.post_no
 
 
--sum records by post_no,gl account and type
insert into lt_gl_dataport
SELECT post_dt =  (substring(convert(char(8),post_dt,112),5,2) +
          substring(convert(char(8),post_dt,112),7,2) +
          substring(convert(char(8),post_dt,112),1,4) ),
    post_no,
    gl_act_no ,
    description = convert(char(5),post_no) + ' ' + gl_act_desc,
    amt = CASE when type = 'D' then sum(amt) when type = 'C' then sum(amt) * -1 end,
    budget_code = ' ',
    curr_code = ' '
FROM    #t_gl_dataport
group by post_dt,post_no, gl_act_no, type,convert(char(8),post_dt,112), gl_act_desc
order by post_dt,post_no, gl_act_no, type

--update table of post no's dataported to Navision to avoid duplication
insert into lt_gl_dataport_hist
(post_no,create_dt,created_by)
select distinct post_no,
    getdate(),
    user_name()
from lt_gl_dataport

--select data for report
SELECT post_dt =   substring(post_dt,1,2)  + '/' +
          substring (post_dt,3,2 ) + '/' +
          substring (post_dt,5,4),
    post_no,
    gl_act_no,
    ' ', --see comments above
    substring(description,6,44) ,
    amt  ,
    budget_code = ' ',
    curr_code = ' '
  from lt_gl_dataport

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!