请输入
菜单

已开票、已收金额反写合同订单的demo

下载

一、应用场景

1.1、销售发票的关联流程:销售订单关联出库单,出库单再关联销售发票时,将开票总额反写到订单上

1.2、收款单的关联流程:销售订单关联出库单,出库单关联销售发票,销售发票关联收款计划,收款计划再关联收款单时,将已收总额反写到订单上

二、demo脚本

DROP PROCEDURE IF EXISTS `yingdian_reset_orderinfo`;
delimiter ||
CREATE  PROCEDURE `yingdian_reset_orderinfo`(
)
BEGIN

    SET @disable_triggers=1;
    UPDATE ld_salesorder a
    LEFT JOIN ld_salesordercf b ON b.salesorderid=a.salesorderid
    LEFT JOIN (
        SELECT
            b.id,
            SUM(c.tax_amount) AS amount_fp
        FROM ld_salesorderdetail b
        LEFT JOIN (
            SELECT
                b.parent_lineitem_id,
                SUM(c.tax_amount) AS tax_amount
            FROM ld_outboundorder a
            LEFT JOIN ld_outboundorderdetail b ON b.id=a.out_id
            LEFT JOIN (
                SELECT
                    SUM(b.tax_amount) AS tax_amount,
                    b.parent_lineitem_id
                FROM ld_invoice a
                LEFT JOIN ld_invoicedetail b ON b.id=a.invoiceid
                WHERE a.deleted=0
                    AND a.approvestatus<>'已拒绝'
                    AND b.parent_lineitem_id>0
                GROUP BY b.parent_lineitem_id
            ) c ON c.parent_lineitem_id=b.lineitem_id
            WHERE a.deleted=0
                AND a.approvestatus<>'已拒绝'
                AND b.parent_lineitem_id>0
            GROUP BY b.parent_lineitem_id
        ) c ON c.parent_lineitem_id=b.lineitem_id
        GROUP BY b.id
    ) c ON c.id=a.salesorderid
    SET 
        b.cf_8388=c.amount_fp,
        b.cf_8389=a.total-IFNULL(c.amount_fp,0)
    WHERE IFNULL(b.cf_8388,0)<>IFNULL(c.amount_fp,0)
        OR IFNULL(b.cf_8389,0)<>a.total-IFNULL(c.amount_fp,0);

    UPDATE  ld_salesorder a
    LEFT JOIN ld_salesordercf b ON b.salesorderid=a.salesorderid
    LEFT JOIN (
        SELECT
            b.id,
            SUM(c.tax_amount) AS amount_sk
        FROM ld_salesorderdetail b
        LEFT JOIN (
            SELECT
                SUM(c.tax_amount) AS tax_amount,
                b.parent_lineitem_id
            FROM ld_outboundorder a
            LEFT JOIN ld_outboundorderdetail b ON b.id=a.out_id
            LEFT JOIN (
                SELECT
                    SUM(c.tax_amount) AS tax_amount,
                    b.parent_lineitem_id
                FROM ld_invoice a
                LEFT JOIN ld_invoicedetail b ON b.id=a.invoiceid
                LEFT JOIN (
                    SELECT
                        SUM(c.tax_amount) AS tax_amount,
                        b.parent_lineitem_id
                    FROM ld_customform16 a
                    LEFT JOIN ld_customform16detail b ON  b.id=a.customform16id
                    LEFT JOIN (
                        SELECT
                            SUM(b.tax_amount) AS tax_amount,
                            b.parent_lineitem_id
                        FROM ld_cashbill a
                        LEFT JOIN ld_cashbill2paymentplan b ON b.CashBillID=a.cashbillid
                        WHERE a.deleted=0
                            AND a.approvestatus<>'已拒绝'
                            AND b.parent_lineitem_id>0
                            AND b.entitytype='CustomForm16'
                        GROUP BY b.parent_lineitem_id
                    ) c ON c.parent_lineitem_id=b.lineitem_id
                    WHERE a.deleted=0
                        AND a.approvestatus<>'已拒绝'
                        AND b.parent_lineitem_id>0
                    GROUP BY b.parent_lineitem_id
                ) c ON c.parent_lineitem_id=b.lineitem_id
                WHERE a.deleted=0
                    AND a.approvestatus<>'已拒绝'
                    AND b.parent_lineitem_id>0
                GROUP BY b.parent_lineitem_id
            ) c ON c.parent_lineitem_id=b.lineitem_id
            WHERE a.deleted=0
                AND a.approvestatus<>'已拒绝'
                AND b.parent_lineitem_id>0
            GROUP BY b.parent_lineitem_id
        ) c ON c.parent_lineitem_id=b.lineitem_id
        GROUP BY b.id
    ) c ON c.id=a.salesorderid
    SET 
        b.cf_8391=IFNULL(c.amount_sk,0)+IFNULL(cf_8396,0),
        b.cf_8390=IFNULL(a.total,0)-IFNULL(c.amount_sk,0)-IFNULL(cf_8396,0)
    WHERE IFNULL(b.cf_8391,0)<>IFNULL(c.amount_sk,0)+IFNULL(cf_8396,0)
        OR IFNULL(b.cf_8390,0)<>IFNULL(a.total,0)-IFNULL(c.amount_sk,0)-IFNULL(cf_8396,0);
    SET @disable_triggers=NULL;
end ||
delimiter;

CALL yingdian_reset_orderinfo();


DROP EVENT IF EXISTS `yingdian_reset_orderinfo_event`;
delimiter ||
CREATE  EVENT `yingdian_reset_orderinfo_event` ON SCHEDULE EVERY 30 MINUTE STARTS NOW() ON COMPLETION NOT PRESERVE ENABLE DO 
CALL yingdian_reset_orderinfo();
||
delimiter;

20250114.特殊已开票和收款反写开发.sql

上一个
如何实现数字转英文单词功能?
下一个
单据分录根据录入的产品、数量等字段自动带入其他字段
最近修改: 2025-02-05