Purchase Order Lines Received

Please assist, I am trying create a report indicating Purchase Order Lines received in a specific period however I need to exclude non stock items and PO's for utilities. I only want to look at Stock Items.

Thus the GLAccountsFull must be linked, I have tried every which way but with no luck.

I want to include or exclude certain GL Accounts in the parameters.

Parents
  • 0

    Dear Craig,

    Getting the following error, I am using Sage Evolution

    Description : Invalid sql query near SE WHEN "IM_PeriodPostingHistory"."FiscalCalYear" = CAST( AS CHAR) THEN (IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost) ELSE 0 END

    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11' OR IM_PeriodPostingHistory.FiscalCalPeriod = '12') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

    FROM IM_PeriodPostingHistory INNER JOIN CI_Item ON IM_PeriodPostingHistory.ItemCode = CI_Item.ItemCode

    WHERE
    (IM_PeriodPostingHistory.FiscalCalYear = '2018'
    )

    Number : -2146233088
    Source :
    Driver Error : Unknown
    SQL State : Unknown

Reply
  • 0

    Dear Craig,

    Getting the following error, I am using Sage Evolution

    Description : Invalid sql query near SE WHEN "IM_PeriodPostingHistory"."FiscalCalYear" = CAST( AS CHAR) THEN (IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost) ELSE 0 END

    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END


    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

    ,
    CASE WHEN IM_PeriodPostingHistory.FiscalCalYear = CAST( AS CHAR) AND (IM_PeriodPostingHistory.FiscalCalPeriod = '01' OR IM_PeriodPostingHistory.FiscalCalPeriod = '02' OR IM_PeriodPostingHistory.FiscalCalPeriod = '03' OR IM_PeriodPostingHistory.FiscalCalPeriod = '04' OR IM_PeriodPostingHistory.FiscalCalPeriod = '05' OR IM_PeriodPostingHistory.FiscalCalPeriod = '06' OR IM_PeriodPostingHistory.FiscalCalPeriod = '07' OR IM_PeriodPostingHistory.FiscalCalPeriod = '08' OR IM_PeriodPostingHistory.FiscalCalPeriod = '09' OR IM_PeriodPostingHistory.FiscalCalPeriod = '10' OR IM_PeriodPostingHistory.FiscalCalPeriod = '11' OR IM_PeriodPostingHistory.FiscalCalPeriod = '12') THEN IM_PeriodPostingHistory.BeginningBalExtendedStdCost + IM_PeriodPostingHistory.PeriodChangeExtendedStdCost ELSE 0 END

    FROM IM_PeriodPostingHistory INNER JOIN CI_Item ON IM_PeriodPostingHistory.ItemCode = CI_Item.ItemCode

    WHERE
    (IM_PeriodPostingHistory.FiscalCalYear = '2018'
    )

    Number : -2146233088
    Source :
    Driver Error : Unknown
    SQL State : Unknown

Children
No Data