I want to add a field from the APBOL table to the Detailed AP Aged Payables S300 SQL (C91-0-2) container.

I cannot add the field using the Add Expressions as the table is not listed.

 But it is being selected in the SQL.  I just don’t know how/what the correct syntax is to add the field – IDACCTSET.   SQL query is below.  Any suggestions?

(((((([APVEN]

LEFT JOIN (SELECT *

     FROM

(SELECT [APOBL].[IDVEND] as VENDNO

      ,[APOBL].[IDINVC] as DOCNO

      ,[APOBL].[TXTTRXTYPE] as DOCTYPE

      --,[APOBL].[IDACCTSET] as ACCT

      ,'' AS APPLYTYPE

      ,[APOBL].[IDRMIT] AS REMIT

      ,CAST(CAST(ISNULL([APOBL].[DATEINVC],19000101) AS varchar) AS date) as DATEDOC

      ,CAST(CAST(ISNULL([APOBL].[DATEBUS],19000101) AS varchar) AS date) as DATEBUS

      ,CAST(CAST(ISNULL([APOBL].[DATEINVCDU],19000101) AS varchar) AS date) as DATEDUE

      ,CAST(CAST(ISNULL([APOBL].[DATEINVC],19000101) AS varchar) AS date) as MATCHDATE

      ,CAST(CAST(ISNULL([APOBL].[DATEINVCDU],19000101) AS varchar) AS date) as MATCHDUE

      ,CAST(CAST(CASE WHEN [APOBL].[DATELSTACT]=0 THEN 19000101 ELSE [APOBL].[DATELSTACT] END AS varchar) AS date) as LSTACTIVE

    

      ,'' AS APPLIED

      ,[APOBL].[AMTINVCHC] as AMTHC

      ,[APOBL].[AMTINVCTC] as AMTTC

      ,[APOBL].[IDVENDGRP] as GRP

      ,[APOBL].[CODECURN] as CURR

      ,[APOBL].[FISCYR] as FISCYR

      ,[APOBL].[FISCPER] as FISCPER

      ,[APOBL].[AUDTORG] as ORG

      --,[APOBL].[IDACCTSET] as ACCT

      ,[APOBL].[CNTTOTPAYM] AS NOOFSCH

      ,1 AS PAYNO

      ,[APOBL].[SWNONRCVBL] AS MISCFLAG

 

     

FROM [APOBL]

WHERE  CAST(CAST([APOBL].[DATEINVC] AS VARCHAR) AS DATE) < = @CUTOFFDATE@

 

UNION ALL

 

SELECT

       [APOBP].[IDVEND] as VENDNO

      ,[APOBP].[IDINVC] as DOCNO

      ,MATCH.[DOCTYP] as DOCTYPE

      ,[APOBP].[TRANSTYPE] AS APPLYTYPE

      ,[APOBP].[IDRMIT] AS REMIT

      ,CAST(CAST((CASE WHEN [APOBP].[DATERMIT] =0 THEN 19000101 ELSE [APOBP].[DATERMIT] END) AS varchar) AS date) as DATEDOC

      ,CAST(CAST(ISNULL([APOBP].[DATEBUS],19000101) AS varchar) AS date) as DATEBUS

      ,CAST(CAST(19000101 AS varchar) AS date) as DATEDUE

      ,CAST(CAST(ISNULL(MATCH.[DATEINVC],19000101)AS VARCHAR) AS DATE) AS MATCHDATE

      ,CAST(CAST(ISNULL(MATCH.[DATEDUE],19000101)AS VARCHAR) AS DATE) AS MATCHDUE

      ,CAST(CAST((CASE WHEN [APOBP].[DATERMIT] =0 THEN 19000101 ELSE [APOBP].[DATERMIT] END) AS varchar) AS date) as LSTACTIVE

      ,[APOBP].[IDMEMOXREF] as APPLIED

      ,[APOBP].[AMTPAYMHC] as AMTHC

      ,[APOBP].[AMTPAYMTC] as AMTTC

      ,'' as GRP

      ,[APOBP].[CODECURN] as CURR

      ,[APOBP].[FISCYR] as FISCYR

      ,[APOBP].[FISCPER] as FISCPER

      ,[APOBP].[AUDTORG] as ORG

      ,1 AS NOOFSCH

      ,[APOBP].[CNTPAYMNBR] AS PAYNO

      ,0 AS MISCFLAG

 

        FROM [APOBP]

        LEFT JOIN (SELECT DISTINCT

                     IDVEND

                    ,IDINVC

                    ,[APOBL].[TXTTRXTYPE] AS DOCTYP

                    ,DATEINVC

                    ,DATEINVCDU AS DATEDUE

                   FROM [APOBL]

                   WHERE  CAST(CAST([APOBL].[DATEINVC] AS VARCHAR) AS DATETIME) < = @CUTOFFDATE@

                 ) AS MATCH

  ON [APOBP].[IDVEND] = MATCH.[IDVEND] AND [APOBP].[IDINVC]= MATCH.[IDINVC]

 

 WHERE  CAST(CAST(ISNULL([APOBP].[DATEBUS],19000101) AS varchar) AS date) < = @CUTOFFDATE@

            

          

    ) AS RECS

        LEFT JOIN (SELECT

                      RECTOTAL.[IDVEND]

                     ,RECTOTAL.[IDINVC]

                     ,SUM(RECTOTAL.[AMTHC]) AS BALHC

                     ,SUM(RECTOTAL.[AMTTC]) AS BALTC

             FROM(

                   SELECT

                        [IDVEND]

                       ,[IDINVC]

                       ,SUM([APL].[AMTINVCHC]) as AMTHC

                       ,SUM([APL].[AMTINVCTC]) AS AMTTC

                     FROM  [APOBL] APL

                     WHERE  CAST(CAST([APL].[DATEINVC] AS VARCHAR) AS DATE) < = @CUTOFFDATE@

                     GROUP BY

                        [IDVEND]

                       ,[IDINVC]

                    

                     UNION ALL

                    

                     SELECT

                        [IDVEND]

                       ,[IDINVC]

                       ,SUM([APY].[AMTPAYMHC]) as AMTHC

                       ,SUM([APY].[AMTPAYMTC]) AS AMTTC

                     FROM  [APOBP] APY

                     WHERE  CAST(CAST(ISNULL([APY].[DATEBUS],19000101) AS varchar) AS date) < = @CUTOFFDATE@

                     GROUP BY

                        [IDVEND]

                       ,[IDINVC]

                      

                  ) RECTOTAL

                    GROUP BY IDVEND,IDINVC

               ) RECTOTALS

                  

    ON RECS.[VENDNO] = RECTOTALS.[IDVEND] AND RECS.[DOCNO] = RECTOTALS.[IDINVC]

   LEFT JOIN (SELECT [APOBS].[IDVEND] AS SCHVEND

                     ,[APOBS].[IDINVC] AS SCHDOCNO

                     ,[APOBS].[DATEDUE] AS SCHDUE

                     ,[APOBS].[CNTPAYM]

                     ,[APOBS].[AMTDUEHC] AS SCHAMTHC

                     ,[APOBS].[AMTDUETC] AS SCHAMTTC

                     ,[APOBS].[AMTPYMRMHC] AS SCHRMHC

                     ,[APOBS].[AMTPYMRMTC] AS SCHRMTC

               FROM [APOBS]

               )PAYSCH

      ON RECS.[VENDNO] = [PAYSCH].[SCHVEND] AND RECS.[DOCNO] = [PAYSCH].[SCHDOCNO]

          AND (RECS.[NOOFSCH]>1 OR RECS.[PAYNO] =[PAYSCH].[CNTPAYM])

 

  ) AS RECORDS

  ON [APVEN].[VENDORID] = RECORDS.[VENDNO])

LEFT JOIN [APVGR]

  ON [APVEN].[IDGRP] = [APVGR].[GROUPID])

LEFT JOIN [APRAS]

  ON [APVEN].[IDACCTSET] = [APRAS].[ACCTSET])

LEFT JOIN [APRTA]

  ON [APVEN].[TERMSCODE] = [APRTA].[TERMSCODE])

INNER JOIN [APP04]

  ON [APP04].[RECID04] = [APP04].[RECID04])

LEFT JOIN CSCOM

  ON RECORDS.[ORG] = [CSCOM].[AUDTORG])

 

WHERE 1=1

AND ((CASE WHEN '@CNCYTYPE@' = 'Functional' THEN ISNULL(RECORDS.[BALHC],0) ELSE ISNULL(RECORDS.[BALTC],0) END

) <> 0

AND (CASE WHEN CAST(CAST(RECORDS.[MATCHDATE] AS varchar) AS DATE)<= @CUTOFFDATE@

     THEN (CASE WHEN '@CNCYTYPE@'= 'Functional'

                THEN ISNULL(RECORDS.[AMTHC],0) 

                ELSE ISNULL(RECORDS.[AMTTC],0)

           END)

     ELSE 0

END) <>0)

AND (((CASE WHEN '@REPORTTYPE@' = 'Age as of Document Date'

          THEN 1

          ELSE ISNULL(RECORDS.[CNTPAYM],1)

           END) = ISNULL(RECORDS.[CNTPAYM],1) OR RECORDS.[PAYNO] = RECORDS.[CNTPAYM]))

Views: 53

Reply to This

Replies to This Discussion

The Select statement in the query limits the fields that can be added as expressions. I expect that if you add the field you want to the Select statement, you will then be able to add an expression using that field to your container. 

Hope this helps. 

Thanks - it does help somewhat.  But where do I add the field in the Select statement.  I tried a couple of different things but get a syntax error??

Thanks again

You'd have to play around with it. It is a very complex Select statement with multiple levels. Also, if/when you do get the field added to it, I can't say what that would do to the results of the query (you might break something in adding that field). Perhaps creating another container with your field, and then using a Union report to combine the Aged Payables to the new field? Just a suggestion and I'm not sure how much work that would be to make it work properly (I don't know what you want to do with the report). 

J. 

Thanks Jim,

I only need to add the one field so I can add it to the final report for pivot purposes.

I will try your suggestion of a union report.

Thanks again

do you just want to add a expressions from this container (AP Aged Payables)?

if so, just add a SQL Join with [APBOL].[Field] in Expression Source is fine.

Thanks Marius,

Can you give me a bit more detail?

Howard

If anyone can provide an answer or the updated SQL, we are happy to pay for your time and expertise.

RSS

The Sage Intelligence Blog

Like Sage Intelligence?

Follow @SageSupport

© 2019   Created by Sage Alchemex.   Powered by

Badges  |  Report an Issue  |  Terms of Service