Setup T1 Tables
Previous Topic  Next Topic 

This is a complex form, and even I am not sure how to use it and I wrote it. Sets where each field in the three extract types XVI, IX and X come from and the views used to create them. Use CLIP to copy the current complete view with some dummy dates to the clipboard, you can then paste and execute that view into an SQL tool like WinSQL or SQL profiler to test the resultant data. Make sure you set meaningfull dates


To copy the current settings for the displayed feeder/table to new feeder enter the feeder name next to the copy button and think carefully about pressing "copy", note all three tables must be copied for a new feeder, can be tricky.



Here is the view from the above settings


Note is only sets 100 records for testing (TOP 100) and the dates in the where clause are arbitrary and must be edited!


SELECT TOP 100

        LEFT(COMPANY_TLA,5) AS HPAACD,

        LEFT(dbo.fnEPID(Service_Date,UR,Creating_Feeder,CAMPUS_TLA),20) AS HPRQCD,

        LEFT(Creating_Feeder,5) AS HPAQCD,

        LEFT(Mapped_Dept_Code+Product_code,20) AS HPARCD,

        CONVERT(bigint,CONVERT(varchar,Service_Date,112)) AS HPAID1,

        0 AS HPGUD1,

        LEFT(Doc_Code,10) AS HPO8CD,

        dbo.fnT2Time(Service_Date) AS HPA8TM,

        '' AS HPLKCE,

        '' AS HPLJCE,

        '' AS HPGGCF,

        '' AS HPGHCF,

        '' AS HPGICF,

        0 AS HPE5NR,

        'N' AS HPFSSA,

        SUM(CONVERT(numeric(15,3),Quantity_Num/1)) AS HPDIQT,

        SUM(CONVERT(numeric(15,3),Charge_Money/1)) AS HPQFVL,

        '' AS HPXZTT,

        '' AS HPX0TT,

        0 AS HPGVV1,

        LEFT(Bill_Code,10) AS HPLLCE,

        LEFT(Consultant_Code,10) AS HPLMCE,

        0 AS HPGWV1,

        0 AS HPGXV1,

        0 AS HPGYV1,

        0 AS HPGZV1,

        0 AS HPG0V1,

        0 AS HPG1V1,

        0 AS HPG2V1,

        0 AS HPG3V1,

        0 AS HPG4V1,

        '' AS HPYONO,

        0 AS HPGXBR,

        0 AS HPFOD1,

        0 AS HPFPD1,

        '' AS HPTQCE,

        '' AS HPTRCE,

        '' AS HPS2CE,

        '' AS HPTTCE,

        '' AS HPP3CD,

        '' AS HPAACF,

        '' AS HPABCF,

        '' AS HPACCF,

        '' AS HPADCF,

        '' AS HPAECF,

        '' AS HPTSCE,

        '' AS HPTUCE,

        '' AS HPTVCE,

        '' AS HPTWCE,

        '' AS HPTXCE,

        '' AS HPTYCE,

        '' AS HPTZCE,

        '' AS HPT0CE,

        '' AS HPT1CE,

        '' AS HPT2CE,

        0 AS HPSKBR,

        0 AS HPSLBR,

        'XXXXX' as LASTFIELD


FROM    dbo.tblAuslabData


WHERE (CAMPUS_TLA = 'TSV') AND (Service_Date >= '1 JUL 2007') AND (Service_Date < '1 AUG 2007')  AND (Cancelled_Date IS NULL)


GROUP BY LEFT(COMPANY_TLA,5),LEFT(Creating_feeder,5),LEFT(Mapped_Dept_Code + Product_Code,20),

CONVERT(bigint,CONVERT(varchar,Service_Date,112)),dbo.fnEPID(Service_Date,UR,Creating_Feeder,CAMPUS_TLA),

dbo.fnT2Time(Service_Date),LEFT(Doc_Code,10),LEFT(Bill_Code,10),LEFT(Consultant_Code,10)


ORDER BY HPAACD,HPRQCD,HPAQCD,HPAID1,HPA8TM