Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- BEGIN
- CREATE TABLE in_log_product_balance_stock_20180221 AS SELECT * FROM in_log_product_balance_stock
- SELECT COUNT(1) FROM in_log_product_balance_stock
- SELECT COUNT(1) FROM in_log_product_balance_stock_20180221
- CREATE TABLE in_log_product_consignment_balance_stock_20180221 AS SELECT * FROM in_log_product_consignment_balance_stock
- SELECT COUNT(1) FROM in_log_product_consignment_balance_stock
- SELECT COUNT(1) FROM in_log_product_consignment_balance_stock_20180221
- SELECT
- * FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2016%' AND NOT EXISTS (
- SELECT 1 FROM pu_receive_goods B
- INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
- WHERE B.doc_no = A.doc_no
- AND B.doc_date = A.doc_date
- AND B.doc_type_id = A.doc_type_id
- AND B.receive_goods_id =A.ref_id
- AND C.product_id = A.product_id
- AND C.qty_rcv_int = A.qty
- )
- SELECT * FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2017%' AND NOT EXISTS (
- SELECT 1 FROM pu_receive_goods B
- INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
- WHERE B.doc_no = A.doc_no
- AND B.doc_date = A.doc_date
- AND B.doc_type_id = A.doc_type_id
- AND B.receive_goods_id =A.ref_id
- AND C.product_id = A.product_id
- AND C.qty_rcv_int = A.qty
- )
- BEGIN
- --DELETE FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2016%' AND NOT EXISTS (
- SELECT 1 FROM pu_receive_goods B
- INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
- WHERE B.doc_no = A.doc_no
- AND B.doc_date = A.doc_date
- AND B.doc_type_id = A.doc_type_id
- AND B.receive_goods_id =A.ref_id
- AND C.product_id = A.product_id
- AND C.qty_rcv_int = A.qty
- )
- WITH tt_not_found AS (
- SELECT * FROM in_log_product_balance_stock_20180221 A
- WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2016%'
- AND NOT EXISTS (
- SELECT 1 FROM pu_receive_goods B
- INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
- WHERE B.doc_no = A.doc_no
- AND B.doc_date = A.doc_date
- AND B.doc_type_id = A.doc_type_id
- AND B.receive_goods_id =A.ref_id
- AND C.product_id = A.product_id
- AND C.qty_rcv_int = A.qty
- )
- )
- INSERT INTO in_log_product_balance_stock(
- tenant_id, ou_id, doc_type_id,
- ref_id, doc_no, doc_date, partner_id, product_id, warehouse_id,
- product_balance_id, product_status, base_uom_id, qty, version,
- create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id,
- A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
- D.product_balance_id, 'GOOD', B.base_uom_id, SUM(B.qty_rcv_int), 0,
- '20180131132801', -1, '20180131132801', -1
- FROM pu_receive_goods A
- INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
- INNER JOIN in_product_balance D ON
- A.tenant_id = D.tenant_id AND
- B.product_id = D.product_id
- AND EXISTS(
- SELECT 1
- FROM tt_not_found Z
- WHERE A.receive_goods_id = Z.ref_id
- AND A.doc_type_id = Z.doc_type_id
- AND A.doc_no = Z.doc_no
- AND A.doc_date = Z.doc_date
- )
- AND NOT EXISTS(
- SELECT 1
- FROM in_log_product_balance_stock Y
- WHERE A.tenant_id = Y.tenant_id
- AND A.ou_id = Y.ou_id
- AND A.doc_type_id = Y.doc_type_id
- AND A.receive_goods_id = Y.ref_id
- AND A.doc_no = Y.doc_no
- AND A.doc_date = Y.doc_date
- AND A.partner_id = Y.partner_id
- AND B.product_id = Y.product_id
- AND A.warehouse_id = Y.warehouse_id
- AND D.product_balance_id = Y.product_balance_id
- AND B.base_uom_id = Y.base_uom_id
- AND Y.product_status = 'GOOD'
- )
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id,
- A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
- D.product_balance_id, B.base_uom_id
- --DELETE FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2017%' AND NOT EXISTS (
- SELECT 1 FROM pu_receive_goods B
- INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
- WHERE B.doc_no = A.doc_no
- AND B.doc_date = A.doc_date
- AND B.doc_type_id = A.doc_type_id
- AND B.receive_goods_id =A.ref_id
- AND C.product_id = A.product_id
- AND C.qty_rcv_int = A.qty
- )
- WITH tt_not_found AS (
- SELECT * FROM in_log_product_balance_stock_20180221 A
- WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2017%'
- AND NOT EXISTS (
- SELECT 1 FROM pu_receive_goods B
- INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
- WHERE B.doc_no = A.doc_no
- AND B.doc_date = A.doc_date
- AND B.doc_type_id = A.doc_type_id
- AND B.receive_goods_id =A.ref_id
- AND C.product_id = A.product_id
- AND C.qty_rcv_int = A.qty
- )
- )
- INSERT INTO in_log_product_balance_stock(
- tenant_id, ou_id, doc_type_id,
- ref_id, doc_no, doc_date, partner_id, product_id, warehouse_id,
- product_balance_id, product_status, base_uom_id, qty, version,
- create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id,
- A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
- D.product_balance_id, 'GOOD', B.base_uom_id, SUM(B.qty_rcv_int), 0,
- '20180131132801', -1, '20180131132801', -1
- FROM pu_receive_goods A
- INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
- INNER JOIN in_product_balance D ON
- A.tenant_id = D.tenant_id AND
- B.product_id = D.product_id
- AND EXISTS(
- SELECT 1
- FROM tt_not_found Z
- WHERE A.receive_goods_id = Z.ref_id
- AND A.doc_type_id = Z.doc_type_id
- AND A.doc_no = Z.doc_no
- AND A.doc_date = Z.doc_date
- )
- AND NOT EXISTS(
- SELECT 1
- FROM in_log_product_balance_stock Y
- WHERE A.tenant_id = Y.tenant_id
- AND A.ou_id = Y.ou_id
- AND A.doc_type_id = Y.doc_type_id
- AND A.receive_goods_id = Y.ref_id
- AND A.doc_no = Y.doc_no
- AND A.doc_date = Y.doc_date
- AND A.partner_id = Y.partner_id
- AND B.product_id = Y.product_id
- AND A.warehouse_id = Y.warehouse_id
- AND D.product_balance_id = Y.product_balance_id
- AND B.base_uom_id = Y.base_uom_id
- AND Y.product_status = 'GOOD'
- )
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id,
- A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
- D.product_balance_id, B.base_uom_id
- ROLLBACK
- BEGIN
- UPDATE in_log_product_consignment_balance_stock SET doc_date = '20171103' WHERE ref_id = 4485556 AND doc_type_id = 311 AND doc_no = 'DO/000170/KPS/201709' AND ou_id = 10 AND tenant_id = 10 AND doc_date = '20170929'
- UPDATE in_log_product_consignment_balance_stock SET doc_date = '20171103' WHERE ref_id = 4485699 AND doc_type_id = 311 AND doc_no = 'DO/000001/KPS/201710' AND ou_id = 10 AND tenant_id = 10 AND doc_date = '20171002'
- ROLLBACK
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement