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)
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)
) AS MATCH
ON [APOBP].[IDVEND] = MATCH.[IDVEND] AND [APOBP].[IDINVC]= MATCH.[IDINVC]
WHERE CAST(CAST(ISNULL([APOBP].[DATEBUS],19000101) AS varchar) AS date)
) 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)
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)
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)
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]))
