|
[EDI-L Mailing List Archive Home]
[Message List]
[Reply To This Message]
Re: GenTran NT 4.0 Reporting
JJ,
Here try this, it works on all windows version of Gentran and can be
modified slightly to work with Director. It creates a stored
procedure in MSSQL. The first time you run this the output will be
huge because the report is not date specific, it only reports on
transaction since the last time the report was run. From there you
can call it using osql.exe and pipe the output for a text file or you
can schedule a process in SQL to do the following:
Stephen Case
503-572-6321
EDI Associates, Inc.
http://www.ediassociates.com
EXEC xp_sendmail @recipients =
@query = 'EXEC usp_InboundFADailyReports',
@subject = 'SQL Gentran FA Received Report',
@message = 'SQL Gentran FA Received Report',
@attach_results = 'TRUE', @width = 250
-- Load this Stored Procedure into the Gentran Database
CREATE PROCEDURE usp_InboundFADailyReports AS
-- TimeCreated is GMT time. Must offset this based on timezone.
-- Offset must be in seconds, not hours since the dateadd functions
-- is being invoked in terms of seconds.
-- If it is DayLightSavings time and the computer is set to auto
-- adjust for DayLightSavings time then 1 hour will have to be
-- subtracted from the calculated offset
/**********************DECLARATIONS*************************/
declare @GMTOffsetInSeconds integer
declare @GMTOffsetInHours integer
declare @Doc char(3)
/*******************INITIALIZATIONS*************************/
/* Standard Time offset = -5 */
/* Daylight Savings Time offset = -4 */
select @GMTOffsetInHours = -4
set nocount ON
-- FORMULA:
-- OffsetInSeconds = OffsetInHours * SecondsPerHour
select @GMTOffsetInSeconds = 3600 * @GMTOffsetInHours
Print 'EDI INBOUND FUNCTIONAL ACKNOWLEDGEMENTS Activity Report'
Select GETDATE()
Print ''
Select Count(PartnerKEY) as 'Daily Transaction Count' from Document_tb
where AppField6 !> ' '
and TransactionSetID = '997'
and Direction = '0'
Print ''
set nocount OFF
Select PartnerKEY,
substring(TransactionSetID,1,3) as Doc,
dateadd(second,(TimeCreated + @GMTOffsetInSeconds),'1/1/1970')
as TimeCreated,
substring(DocumentName,1,20) as Document,
substring(ReferenceData,1,10) as Reference,
Status =
CASE ComplianceStatus
WHEN 0 THEN 'Incomplete'
WHEN 1 THEN 'Not Compliant'
WHEN 2 THEN 'OK'
WHEN 3 THEN 'DocQueued'
WHEN 4 THEN 'Sent'
WHEN 5 THEN 'NetReceived'
WHEN 6 THEN 'NetDelivered'
WHEN 7 THEN 'FAReceived'
WHEN 8 THEN 'Waiting'
WHEN 9 THEN 'OverDue'
WHEN 10 THEN 'NetWarning'
WHEN 11 THEN 'NetError'
WHEN 12 THEN 'FAwErrors'
WHEN 13 THEN 'FAPartial'
WHEN 14 THEN 'FAReject'
WHEN 15 THEN 'NetPickedUp'
WHEN 16 THEN 'Duplicate'
WHEN 17 THEN 'ReadyToSend'
WHEN 18 THEN 'SendFailed'
ELSE 'UNKNOWN'
END
from Document_tb
Where AppField6 !> ' '
and TransactionSetID = '997'
and Direction = '0'
order by PartnerKEY,ReferenceData;
set nocount ON
update Document_tb
set Document_tb.AppField6 = GETDATE()
where AppField6 !> ' '
and TransactionSetID = '997'
and Direction = '0'
|
 |
Subscribe in XML format
| RSS 2.0 |
|
| Atom 0.3 |
|
|
Cart
