【合同项目】合同项目运维记录

合同项目运维记录

修改数据

修改合同物料行

scms_other_inventory
scms_material_process

查询合同变更条数

履约状态就是合同状态，TO_BE_SIGN、SIGN、IN_PROGRESS，这些状态可以正常履约
scms_contract.state
scms_contract_change记录了变更信息，可以通过scms_contract_change.contract_code字段关联原合同找到变更条数

合同搜不到

JT-WTXHT-2020-09-000109-1、JT-WTXHT-2020-09-000167-1
帆哥，麻烦看下这两个合同为什么找不到，物流的
原因是初始化的合同，经办部门没调整

结构化编辑选不到合同

经办部门要调整下，不在现在组织机构里面，系统校验的是org_code，改的话一般把org_name一起改了

SQL示例

查询物流以及广州合同

SELECT
	sc.`code` AS "合同编号",
	sc.`name` AS "合同名称",
	sc.type_name AS "合同类型",
	sc.org_name AS "部门",
	sc.party_ai_name AS "甲方",
	e_ai.unified_social_code AS "采购人编码",
	sc.party_bi_name AS "乙方",
	e_bi.unified_social_code AS "合同卖方代码",
	sc.tax_amount AS "合同总金额",
-- 	sc.source AS "合同来源",
	lov.meaning AS "合同来源",
	sc.source_code AS "来源编码",
	sc.project_name AS "项目名称",
	scc.wf_approval_time AS "合同变更日期",
	scc.wf_approval_status AS "合同变更状态",
--     smp.material_name AS "物料名称",
--     smp.specification AS "规格型号",
--     smp.unit AS "单位",
--     smp.manufacturer AS "生产厂家",
-- 		smp.material_tax_price AS "含税单价",
--     smp.tax_rate AS "税率",
--     spi.weight_type_name AS "计重方式",
-- 	spi.tax_name AS "税率名称",
-- 	spi.tax_amount AS "含税总价",
-- t2.dynamic_form_value,
    JSON_EXTRACT(t2.dynamic_form_value, '$.settlementType.meaning') AS "结算方式",
    JSON_EXTRACT(t2.dynamic_form_value, '$.consumptionType.name') AS "支付方式",
CASE
		
		WHEN sc.state = 'IN_PROGRESS' THEN
		'执行中' 
		WHEN sc.state = 'SIGN' THEN
		'签署中' ELSE sc.state -- 如果有其他状态，保持原值
		
	END AS "合同状态" 
FROM
	dwd_mana_scms_contract sc --     LEFT JOIN scms_material_process smp ON sc.`code` = smp.contract_code
-- 	LEFT JOIN dwd_mana_scms_purchase_inventory spi ON sc.id = spi.contract_id
	LEFT JOIN dwd_mana_hpfm_tenant hc ON hc.tenant_num = sc.party_bi_code -- 	LEFT OUTER JOIN dwd_sale_crm_customer_rel t4 ON hc.tenant_num = t4.customer_code
	LEFT JOIN dwd_mana_scms_contract_change scc ON sc.`code` = scc.contract_code
	LEFT JOIN dwd_mana_oss_enterprise_basic e_ai ON sc.party_ai_code = e_ai.enterprise_num
	LEFT JOIN dwd_mana_oss_enterprise_basic e_bi ON sc.party_bi_code = e_bi.enterprise_num 
	LEFT JOIN (
        SELECT 
            sfv.business_id,
            sfv.last_update_time,
            sfv.dynamic_form_value,
            ROW_NUMBER() OVER (PARTITION BY sfv.business_id ORDER BY sfv.last_update_time DESC) as rn
        FROM dwd_mana_scms_form_value sfv
    ) AS t2 ON sc.id = t2.business_id AND t2.rn = 1
	LEFT JOIN dwd_hpfm_lov_value lov ON sc.source = lov.`value`
WHERE
	(
		( sc.party_ai_name = "云南建投物流有限公司" AND sc.org_name IN ( "工程物资事业部" ) ) 
	OR ( sc.party_ai_name = "广州营家现代产业发展有限公司" AND sc.org_id IN ( 49497, 62019, 73857, 73859, 73860, 73861, 73864 ) )
) 
-- 	AND t4.enable_flag = 1 
-- 	AND t4.del_flag = 0 
-- 	AND t4.internal_external_flag = 1
	
	AND sc.state IN ( 'SIGN', 'IN_PROGRESS' ) 
	AND sc.creation_date >= '2025-07-01' 
	AND sc.creation_date < '2025-12-31' -- GROUP BY smp.material_code, sc.code, smp.manufacturer
	
	AND sc.type_name LIKE "%采购%" 
	-- 	AND ( sc.source_code LIKE '%RFX%' OR sc.source_code LIKE '%BID%' )
	-- 	and sc.party_bi_name = '昆明中畅实业有限公司'
	AND sc.`name` NOT LIKE '%废旧%'
  AND sc.`name` NOT LIKE '%废钢%'
	AND sc.source != "SUPPLEMENT_AGREEMENT_DIRECTLY"
GROUP BY
	sc.CODE,
	sc.`name`,
	sc.type_name,
	sc.org_name,
	sc.party_ai_name,
	sc.party_bi_name,
	sc.tax_amount,
	sc.source_code,
	sc.project_name,
	scc.wf_approval_time,
	scc.wf_approval_status,-- 	spi.tax_name,
-- 	spi.tax_amount,
	sc.state,
	sc.creation_date,
	e_ai.unified_social_code,
	t2.dynamic_form_value,
	e_bi.unified_social_code,
	lov.meaning
ORDER BY
	sc.NAME ASC,
	sc.creation_date DESC;

根据时间查询头行

SELECT
    sc.`name` AS "合同名称",
    sc.`code` AS "合同编号",
    sc.type_name AS "合同类型",
    sc.org_name AS "部门",
    sc.party_ai_name AS "甲方",
    sc.party_bi_name AS "乙方",
    sc.tax_amount AS "合同总金额",
    sc.project_name AS "项目名称",
    smp.material_name AS "物料名称",
    smp.specification AS "规格型号",
    smp.unit AS "单位",
    smp.manufacturer AS "生产厂家",
    spi.weight_type_name AS "计重方式",
    smp.material_tax_price AS "含税单价",
    smp.tax_rate AS "税率",
    spi.tax_name AS "税率名称",
    spi.tax_amount AS "含税总价",
    t2.last_update_date,
    t2.dynamic_form_value,
    JSON_EXTRACT(t2.dynamic_form_value, '$.settlementType.meaning') AS "结算方式",
    JSON_EXTRACT(t2.dynamic_form_value, '$.consumptionType.name') AS "支付方式",
    sc.state 
FROM
    scms_contract sc
    LEFT JOIN scms_material_process smp ON sc.`code` = smp.contract_code
    LEFT JOIN scms_purchase_inventory spi ON sc.id = spi.contract_id
    LEFT JOIN (
        SELECT 
            sfv.business_id,
            sfv.last_update_date,
            sfv.dynamic_form_value,
            ROW_NUMBER() OVER (PARTITION BY sfv.business_id ORDER BY sfv.last_update_date DESC) as rn
        FROM scms_form_value sfv
    ) AS t2 ON sc.id = t2.business_id AND t2.rn = 1
WHERE
    sc.company_name = "云南建投物流有限公司" 
    AND sc.state IN ('SIGN', 'TO_BE_SIGN', 'IN_PROGRESS') 
    AND sc.org_name = "工程物资事业部"
    -- 查询2024年1月的数据
    AND sc.creation_date >= '2023-01-01'
    AND sc.creation_date < '2023-03-01'
GROUP BY smp.material_code, sc.code, smp.manufacturer
ORDER BY sc.NAME ASC, sc.creation_date DESC;