Maximo List Archive

From: cbrandon63 (2013-05-14 18:25)

I have a requirement to create a report that reads in a flat file generated from our accounting system then uses the values from that file to audit invoices in Maximo.
I have created a SQL stored procedure to read and parse the flat file.
USE [maximo7]
GO
/****** Object: StoredProcedure [dbo].[sp_apdtl] Script Date: 05/14/2013 11:43:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_apdtl]
-- Add the parameters for the stored procedure here
-- <@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>,
-- <@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
--drop table APDTL_xl
--drop table txt_in
create table txt_in (txtvar varchar(89))
create table APDTL_xl(xl_blank1 varchar(1),
xl_vendor varchar(6),
xl_vndname varchar(30),
xl_checkno varchar(10),
xl_invoice varchar(15),
xl_invdate date,
xl_terms varchar(2),
xl_type varchar(2),
xl_amount decimal(15,2))
BULK
INSERT txt_in
FROM 'c:\applications\apmxdump.txt'
WITH
(
--FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
insert into APDTL_xl(xl_blank1
, xl_vendor
, xl_vndname
, xl_checkno
, xl_invoice
, xl_invdate
, xl_terms
, xl_type
, xl_amount)
SELECT substring(txtvar,1,1),
substring(txtvar,2,6),
substring(txtvar,8,30),
substring(txtvar,38,10),
substring(txtvar,48,15),
cast(substring(txtvar,63,8)as date),
case substring(txtvar,71,2)
when 'VO' then '30' else substring(txtvar,71,2) end,
case substring(txtvar,71,2)
when 'VO' then substring(txtvar,71,2) else substring(txtvar,73,2) end,
case substring(txtvar,71,2)
when 'VO' then cast(cast(substring(txtvar,73,15) as money) as decimal) else cast(cast(substring(txtvar,75,15) as money) as decimal) end
-- substring(txtvar,71,2),
-- substring(txtvar,73,2),
-- cast(cast(substring(txtvar,75,15) as money) as decimal)
FROM txt_in
--select * from txt_in
declare apdtl CURSOR FOR
select * from apdtl_xl;
OPEN apdtl;
return;
drop table APDTL_xl
drop table txt_in
END
If I execute the stored procedure from SQL Server Management Studio I get the following results set:
AAO324 AAOHN 101359 NOV '09|||||||| 2009-11-20 SC VO 185.00
ADS263 ALCO DISCOUNT STORE 101360 271-01661|||||| 2009-11-11 30 VO 29.00
AEI232 DHL 101361 183912||||||||| 2009-11-12 02 VO 68.00
However, I'm not sure how to get/use the result set in a BIRT report for Maximo.
I have created a data source:
open (I tried the select statement first)
APDTL_xl = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
APDTL_xl.open();
var sqlText = new String();
sqlText = "call maximo7.dbo.sp_apdtl()"
;
APDTL_xl.setQuery(sqlText);
//sqlText = " select sp_apdtl from dual"
fetch:
if (!APDTL_xl.fetch())
return (false);
row["xl_blank1"] = APDTL_xl.getString("xl_blank1");
row["xl_vendor"] = APDTL_xl.getString("xl_vendor");
row["xl_vndname"] = APDTL_xl.getString("xl_vndname");
row["xl_checkno"] = APDTL_xl.getString("xl_checkno");
row["xl_invoice"] = APDTL_xl.getString("xl_invoice");
row["xl_invdate"] = APDTL_xl.getTimestamp("xl_invdate");
row["xl_terms"] = APDTL_xl.getString("xl_terms");
row["xl_type"] = APDTL_xl.getString("xl_type");
row["xl_amount"] = APDTL_xl.getDouble("xl_amount");
return (true);
I get no errors, but I also get no results when I try to run report with these fields as output in BIRT report. (btw .. the location of the input file is the C: drive on the server. Is it actually finding the file?)
I have become very frustrated with this. What am I missing, please?