ItemAccountGP.sql

select
    max(t.ITMCLSCD) ITMCLSCD,
    max(case when ac.ACTINDX = t.IVIVINDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVIVINDX,
    max(case when ac.ACTINDX = t.IVIVOFIX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVIVOFIX,
    max(case when ac.ACTINDX = t.IVCOGSIX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVCOGSIX,
    max(case when ac.ACTINDX = t.IVSLSIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVSLSIDX,
    max(case when ac.ACTINDX = t.IVSLDSIX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVSLDSIX,
    max(case when ac.ACTINDX = t.IVSLRNIX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVSLRNIX,
    max(case when ac.ACTINDX = t.IVINUSIX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVINUSIX,
    max(case when ac.ACTINDX = t.IVINSVIX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVINSVIX,
    max(case when ac.ACTINDX = t.IVDMGIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVDMGIDX,
    max(case when ac.ACTINDX = t.IVVARIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVVARIDX,
    max(case when ac.ACTINDX = t.DPSHPIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) DPSHPIDX,
    max(case when ac.ACTINDX = t.PURPVIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) PURPVIDX,
    max(case when ac.ACTINDX = t.UPPVIDX  then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) UPPVIDX, 
    max(case when ac.ACTINDX = t.IVRETIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) IVRETIDX,
    max(case when ac.ACTINDX = t.ASMVRIDX then rtrim(ltrim(ac.ACTNUMBR_1)) + '-' + rtrim(ltrim(ac.ACTNUMBR_2)) + '-' + rtrim(ltrim(ac.ACTNUMBR_3)) else '' end) ASMVRIDX
from 
    IV40400 t, 
    GL00100 ac 
where 
    t.ITMCLSCD = 'class_you_are_lookin_by' and 
    ac.ACTINDX IN ( 
        t.IVIVINDX, 
        t.IVIVOFIX, 
        t.IVCOGSIX, 
        t.IVSLSIDX, 
        t.IVSLDSIX, 
        t.IVSLRNIX, 
        t.IVINUSIX, 
        t.IVINSVIX, 
        t.IVDMGIDX, 
        t.IVVARIDX, 
        t.DPSHPIDX, 
        t.PURPVIDX, 
        t.UPPVIDX, 
        t.IVRETIDX, 
        t.ASMVRIDX )