updatefifo.sql - main - ivan/ModMas

updatefifo.sql
100 строк3 KB
i.krivokon
Initial commit
13 мар 2026, 13:49
13 мар 2026, 13:4900d9fb6
use UserModules
-- ----    Clear all
UPdate [user].[GERP_MB_51_FOR_GTD]
set gtd = NULL, REFERENCE_GTD = NULL, REMARK = NULL
;
delete from [user].[GERP_MB_51_FOR_GTD] where mvt='C55'
;

-- Update GTD INFO by GERP GTD mapping from header text
UPdate [user].[GERP_MB_51_FOR_GTD]
set gtd=(
case CHARINDEX('|',document_header_text)
when 0 then document_header_text 
else substring(document_header_text, 0, CHARINDEX('|',document_header_text)) end )
where gtd is null
 AND (case CHARINDEX('|',document_header_text)
when 0 then len(document_header_text)
else len(substring(document_header_text, 0, CHARINDEX('|',document_header_text))) end ) = 23
and (mvt='101' or mvt = '000' or mvt='122')
;

UPdate [user].[GERP_MB_51_FOR_GTD]
set gtd='A01_NON_COMMERCE_GR'
where gtd is null and MVT='A01'
;

UPdate [user].[GERP_MB_51_FOR_GTD]
set gtd='A87_DISASSEMBLE'
where gtd is null and MVT='A87'
;

-- update GTD from Orchestra mapping Invoice vs GTD
UPdate [user].[GERP_MB_51_FOR_GTD]
set gtd=(select distinct gtd_no from [user].GTD_VS_INVOICE_No_ORCHESTRA
where invoice = REFERENCE)
where 
gtd is null and REFERENCE <> ''
;
--  Update not available GTD
UPdate [user].[GERP_MB_51_FOR_GTD]
set gtd='NA' where gtd is null and mvt='101'
;
--    Setting reference gtd for documents with gtd defined
UPdate [user].[GERP_MB_51_FOR_GTD]
set REFERENCE_GTD = gtd
;

with source as (
select MODEL_CODE AS MODEL_NUMBER,
 max(PLAN_YMD) as work_Date, 
 max(PLAN_QTY) as plan_qty, 
 po_no as po_number
from GMESData.DBO.TBM_PM_PO_PLAN
group by model_code, po_no
)

MERGE UserMOdules.[user].[GERP_MB_51_FOR_GTD] target
using -- GMESData.DBO.TB_VWP_QMS_MDL_PLAN 
source
on '0'+target.production_order collate Cyrillic_General_CI_AI 
= source.po_number collate Cyrillic_General_CI_AI
when matched  then 
update set 
target.FG_model = source.MODEL_NUMBER,
target.PRODUCTION_ORDER_DATE = source.WORK_DATE,
target.PRODUCTION_ORDER_QUANTITY = source.PLAN_QTY
;

---  Update start & end quantity by item
WITH mvt as (
select 
id as i,
material as m,
PLNT as p,
sloc as s, 
cast(QUANTITY as integer) as q
from UserModules.[user].GERP_MB_51_FOR_GTD
) 

update UserModules.[user].GERP_MB_51_FOR_GTD
set BEFORE_STOCK = 
(select sum(q) from mvt where i < id and m=material and p=plnt and s=sloc) ,
AFTER_STOCK = 
(select sum(q) from mvt where i <= id and m=material and p=plnt and s=sloc) 

-- ----  Update amount to correct format
update  UserModules.[user].GERP_MB_51_FOR_GTD
set AMOUNT_IN_LC = replace(REPLACE(AMOUNT_IN_LC, ',','.'),' ','')
;
update  UserModules.[user].GERP_MB_51_FOR_GTD
set AMOUNT_IN_LC = ('-' + substring(amount_in_lc ,0, len(amount_in_lc )))
where 
substring(amount_in_lc , len(amount_in_lc ),1) = '-'
;
-- ---   Clear printed log
update 
[user].[GERP_MB_51_FOR_GTD]
set PRINTED = '',
PRINTED_GI = '',
PRINTED_FG = ''