0
Hello, I would like a help to always use the value of a table. I have a table "COSTS" and every month it suffers price change of the products, but are not all that has update
My question is that whenever I need to use the COST table with the PRODUCT table, as I always call the last updated date of the product. I will show the code I created, but I can not show the value of the last cost table.
I thank you already this is a short excerpt of the study more meets the basics I need to explain and understand.
CTE.DTCONTAGEM
,CTE.CODEMP
,PRO.NCM
,PRO.DESCRPROD
,CTE.CODPROD
,PRO.AD_PARTNUMBER
,CTE.CODVOL
,CTE.QTDEST
---na tabela de custo tem esse campo CUSSEMICM que preciso que seja sempre a ultima data inserida
,CUS.CUSSEMICM
-- esse código era só um teste porém traz qualquer valor do custo e não o ultimo
,(SELECT nvl(MAX(FCUS.CUSSEMICM), 0)
FROM TGFCUS FCUS
WHERE FCUS.CODPROD = CTE.CODPROD
AND FCUS.DTATUAL < trunc (SYSDATE) -1) AS CUSTO
,CUS.CUSSEMICM
,CTE.QTDEST * CUS.CUSSEMICM AS CUS_SEM_ICMS -- esse é o calculo que precisa fazer mais o custo tem que ser o certo
,CUS.CUSMEDCALC
,CTE.CODLOCAL
,CTE.CODPARC
,CTE.TIPO
FROM
VGFCTE CTE
LEFT JOIN TGFCUS CUS ON (CUS.CODPROD = CTE.CODPROD AND CUS.CODEMP = CTE.CODEMP AND CTE.CODLOCAL = CUS.CODLOCAL)
LEFT JOIN TGFPRO PRO ON (CTE.CODPROD = PRO.CODPROD )
WHERE CTE.TIPO ='P' AND (PRO.CODGRUPOPROD >= 30101000 AND PRO.CODGRUPOPROD <= 31601000)
AND CTE.CODLOCAL IN (10000000,20000000)
AND CTE.QTDEST > 0