Centres de cost ECOAF

Actualitzacions fetes a la OJDT i JDT1 respecte els camps de centre de cost

Consultar/actualitzar els centres de cost als assentaments de factures i abonaments, entregues i devolucions

--update L set L."ProfitCode"=BP."U_ECO_DIM1",L."OcrCode2"=BP."U_ECO_DIM2",L."OcrCode3"=BP."U_ECO_DIM3"
select C."Number",L."TransId"
from JDT1 L 
join OJDT C on C."TransId"=L."TransId"
join ODLN E on E."DocEntry"=C."CreatedBy"
join OCRD BP on BP."CardCode"=E."CardCode"
where 
(
( L."ProfitCode" is null and L."OcrCode2" is null and L."OcrCode3" is null) 
or (L."ProfitCode"='' and L."OcrCode2"='' and L."OcrCode3"='')
)
and SUBSTR(L."Account",1,1) in ('6','7') 
and C."TransType" in (13,14,15,16)

Actualitzar els assentaments de entrades i sortides de mercaderies

-- Actualitzacions centres de cost ECOALF
-- 3/4/2019 David Marti

--UPDATE A20174_ECOALF_PRODL.JDT1 SET "OcrCode2"='ST_S001' WHERE "OcrCode2"='DP_SOU';
--UPDATE A20174_ECOALF_PRODL.JDT1 SET "OcrCode3"='DP_SOU' WHERE "OcrCode3"='ST_STR';
--UPDATE A20174_ECOALF_PRODL.JDT1 SET "ProfitCode"='ST_STR' WHERE "ProfitCode"='ST001';

-- TK-1 -------------------------------------------------------------------------------------

--SELECT T1."TransId",T1."TransType",T2."Comments",T1."RefDate",T0."Account"
--,T0."ProfitCode",T0."OcrCode2",T0."OcrCode3" 
UPDATE T0 SET T0."ProfitCode"='RE_STO',T0."OcrCode2"='RE_S001',T0."OcrCode3"='DP_SAL'
FROM A20174_ECOALF_PRODL.JDT1 T0
JOIN A20174_ECOALF_PRODL.OJDT T1 on T1."TransId"=T0."TransId"
JOIN A20174_ECOALF_PRODL.OIGN T2 on T2."TransId"=T0."TransId"
WHERE T1."TransType"='59' and T0."Account"='61000000' and T2."Comments" like 'TK-1%';

UPDATE T0 SET T0."ProfitCode"='RE_STO',T0."OcrCode2"='RE_S001',T0."OcrCode3"='DP_SAL'
FROM A20174_ECOALF_PRODL.JDT1 T0
JOIN A20174_ECOALF_PRODL.OJDT T1 on T1."TransId"=T0."TransId"
JOIN A20174_ECOALF_PRODL.OIGE T2 on T2."TransId"=T0."TransId"
WHERE T1."TransType"='60' and T0."Account"='61000000' and T2."Comments" like 'TK-1%';

-- TK-2 -------------------------------------------------------------------------------------

UPDATE T0 SET T0."ProfitCode"='RE_STO',T0."OcrCode2"='RE_S002',T0."OcrCode3"='DP_SAL'
FROM A20174_ECOALF_PRODL.JDT1 T0
JOIN A20174_ECOALF_PRODL.OJDT T1 on T1."TransId"=T0."TransId"
JOIN A20174_ECOALF_PRODL.OIGN T2 on T2."TransId"=T0."TransId"
WHERE T1."TransType"='59' and T0."Account"='61000000' and T2."Comments" like 'TK-2%';

UPDATE T0 SET T0."ProfitCode"='RE_STO',T0."OcrCode2"='RE_S002',T0."OcrCode3"='DP_SAL'
FROM A20174_ECOALF_PRODL.JDT1 T0
JOIN A20174_ECOALF_PRODL.OJDT T1 on T1."TransId"=T0."TransId"
JOIN A20174_ECOALF_PRODL.OIGE T2 on T2."TransId"=T0."TransId"
WHERE T1."TransType"='60' and T0."Account"='61000000' and T2."Comments" like 'TK-2%';

-- TK-3 -------------------------------------------------------------------------------------

UPDATE T0 SET T0."ProfitCode"='RE_STO',T0."OcrCode2"='RE_S003',T0."OcrCode3"='DP_SAL'
FROM A20174_ECOALF_PRODL.JDT1 T0
JOIN A20174_ECOALF_PRODL.OJDT T1 on T1."TransId"=T0."TransId"
JOIN A20174_ECOALF_PRODL.OIGN T2 on T2."TransId"=T0."TransId"
WHERE T1."TransType"='59' and T0."Account"='61000000' and T2."Comments" like 'TK-3%';

UPDATE T0 SET T0."ProfitCode"='RE_STO',T0."OcrCode2"='RE_S003',T0."OcrCode3"='DP_SAL'
FROM A20174_ECOALF_PRODL.JDT1 T0
JOIN A20174_ECOALF_PRODL.OJDT T1 on T1."TransId"=T0."TransId"
JOIN A20174_ECOALF_PRODL.OIGE T2 on T2."TransId"=T0."TransId"
WHERE T1."TransType"='60' and T0."Account"='61000000' and T2."Comments" like 'TK-3%';

update L set L."ProfitCode"=BP."U_ECO_DIM1",L."OcrCode2"=BP."U_ECO_DIM2",L."OcrCode3"=BP."U_ECO_DIM3"
--select C."Number",L."TransId"
from JDT1 L 
join OJDT C on C."TransId"=L."TransId"
join ODLN E on E."DocEntry"=C."CreatedBy"
join OCRD BP on BP."CardCode"=E."CardCode"
where 
(
( L."ProfitCode" is null and L."OcrCode2" is null and L."OcrCode3" is null) 
or (L."ProfitCode"='' and L."OcrCode2"='' and L."OcrCode3"='')
)
and SUBSTR(L."Account",1,1) in ('6','7') 
and C."TransType" in (13,14,15,16)

update L set L."OcrCode"=IC."U_ECO_DIM1",L."OcrCode2"=IC."U_ECO_DIM2",L."OcrCode3"=IC."U_ECO_DIM3"
from RDR1 L
join ORDR C on C."DocEntry"=L."DocEntry"
join OCRD IC on IC."CardCode"=C."CardCode"
where L."OcrCode" IS NULL or L."OcrCode2" IS NULL or L."OcrCode3" IS NULL

update L set L."OcrCode"=IC."U_ECO_DIM1",L."OcrCode2"=IC."U_ECO_DIM2",L."OcrCode3"=IC."U_ECO_DIM3"
from DLN1 L
join ODLN C on C."DocEntry"=L."DocEntry"
join OCRD IC on IC."CardCode"=C."CardCode"
where L."OcrCode" IS NULL or L."OcrCode2" IS NULL or L."OcrCode3" IS NULL

update L set L."OcrCode"=IC."U_ECO_DIM1",L."OcrCode2"=IC."U_ECO_DIM2",L."OcrCode3"=IC."U_ECO_DIM3"
from INV1 L
join OINV C on C."DocEntry"=L."DocEntry"
join OCRD IC on IC."CardCode"=C."CardCode"
where L."OcrCode" IS NULL or L."OcrCode2" IS NULL or L."OcrCode3" IS NULL

Visites: 31