/*
/*=================================
受注集計処理
=================================*/
CREATE or REPLACE PROCEDURE JHUPD
(
ERR IN OUT NUMBER
,P区分 IN VARCHAR2 -- 1:作成,2:変更,3:集計,4:SZ03,SZ04集計
,P組織 IN VARCHAR2 -- 入力組織
,P受注番号 IN NUMBER
,P行 IN NUMBER -- 登録行
,P数量1 IN NUMBER
,P数量2 IN NUMBER
)
AS
EXIT_FLG NUMBER;
ERR_FLG NUMBER;
V数量 NUMBER;
V数量2 NUMBER;
V入出荷数量2 NUMBER;
V完了 VARCHAR2(1);
V在庫組織 VARCHAR2(4);
V大分類 VARCHAR2(2);
V商品ｺｰﾄﾞ VARCHAR2(20);
V場所区分 VARCHAR2(1);
V場所ｺｰﾄﾞ VARCHAR2(8);
V単品ｺｰﾄﾞ VARCHAR2(15);
V認識 VARCHAR2(1);
CURSOR cur_TR01 is
select 識別A from TR01
where "識別A" = '1' AND "識別C" = ' ' AND "入力組織" = P組織 AND "受発注番号" = P受注番号;
BEGIN
/*=================================
初期処理
=================================*/
DBMS_OUTPUT.PUT_LINE('JHUPD START');
SAVEPOINT UPD_START;
ERR_FLG := ERR;
ERR_FLG := 0;
EXIT_FLG := 0;
BEGIN
select 在庫組織 into V在庫組織 from NA10 where 組織 = P組織;
EXCEPTION
WHEN OTHERS THEN
V在庫組織 := P組織;
END;
BEGIN
select
大分類,
商品ｺｰﾄﾞ,
場所区分,
場所ｺｰﾄﾞ,
単品ｺｰﾄﾞ,
入力数量2,
入出荷数量2
into
V大分類,
V商品ｺｰﾄﾞ,
V場所区分,
V場所ｺｰﾄﾞ,
V単品ｺｰﾄﾞ,
V数量2,
V入出荷数量2
from TR07
WHERE
"識別A" = '7' AND
"識別C" = ' ' AND
"入力組織" = P組織 AND
"伝票番号" = P受注番号 AND
"登録行" = P行;
EXCEPTION
WHEN OTHERS THEN
EXIT_FLG := 1;
ERR := 9;
END;
IF P区分 = '5' THEN
V数量2 := V数量2 * -1;
END IF;
/*=================================
主処理
=================================*/
/* 作成 */
ERR_FLG := 1;
IF P区分 = '1' and EXIT_FLG = 0 THEN
UPDATE TR07 SET
"入出荷数量1" = 0
,"入出荷数量2" = 0
,"残高数量1" = P数量1
,"残高数量2" = P数量2
WHERE "識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
IF SQL%NOTFOUND THEN
ERR := ERR_FLG;
ELSE
LOOP
UPDATE SZ03 SET
"現在現物数量2" = "現在現物数量2" - P数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ;
IF SQL%NOTFOUND THEN
INSERT INTO SZ03 VALUES
(V在庫組織,V大分類,V商品ｺｰﾄﾞ
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0); /* 142 */
ELSE
EXIT;
END IF;
END LOOP;
LOOP
UPDATE SZ04 SET
"現在現物数量2" = "現在現物数量2" - P数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ AND "場所区分" = NVL(V場所区分,' ') AND "場所ｺｰﾄﾞ" = NVL(V場所ｺｰﾄﾞ,' ') AND "単品ｺｰﾄﾞ" = NVL(V単品ｺｰﾄﾞ,' ');
IF SQL%NOTFOUND THEN
INSERT INTO SZ04 VALUES
(V在庫組織
,NVL(V場所区分,' ')
,NVL(V場所ｺｰﾄﾞ,' ')
,V大分類
,V商品ｺｰﾄﾞ
,NVL(V単品ｺｰﾄﾞ,' ')
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,sysdate,NULL,sysdate,0); /* 83 */
ELSE
EXIT;
END IF;
END LOOP;
END IF;
END IF;
/* 変更 */
ERR_FLG := 2;
IF P区分 = '2' and EXIT_FLG = 0 THEN
UPDATE TR07 SET
"残高数量1" = 入力数量1 - "入出荷数量1"
,"残高数量2" = 入力数量2 - "入出荷数量2"
WHERE "識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
IF SQL%NOTFOUND THEN
ERR := ERR_FLG;
ELSE
LOOP
UPDATE SZ03 SET
"現在現物数量2" = "現在現物数量2" - P数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ;
IF SQL%NOTFOUND THEN
INSERT INTO SZ03 VALUES
(V在庫組織,V大分類,V商品ｺｰﾄﾞ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL
,NULL,NULL,sysdate,NULL,sysdate,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0); /* 142 */
ELSE
EXIT;
END IF;
END LOOP;
LOOP
UPDATE SZ04 SET
"現在現物数量2" = "現在現物数量2" - P数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ AND "場所区分" = NVL(V場所区分,' ') AND "場所ｺｰﾄﾞ" = NVL(V場所ｺｰﾄﾞ,' ') AND "単品ｺｰﾄﾞ" = NVL(V単品ｺｰﾄﾞ,' ');
IF SQL%NOTFOUND THEN
INSERT INTO SZ04 VALUES
(V在庫組織,NVL(V場所区分,' '),NVL(V場所ｺｰﾄﾞ,' '),V大分類,V商品ｺｰﾄﾞ
,NVL(V単品ｺｰﾄﾞ,' ')
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,sysdate,NULL,sysdate,0); /* 83 */
ELSE
EXIT;
END IF;
END LOOP;
END IF;
END IF;
/* 集計 */
ERR_FLG := 3;
IF P区分 = '3' and EXIT_FLG = 0 THEN
UPDATE TR07 SET
"入出荷数量1" = "入出荷数量1" + P数量1
,"入出荷数量2" = "入出荷数量2" + P数量2
,"残高数量1" = "残高数量1" - P数量1
,"残高数量2" = "残高数量2" - P数量2
WHERE "識別A" = '7' AND "識別C" = ' ' AND "入力組織" = P組織 AND "伝票番号" = P受注番号 AND "登録行" = P行;
IF SQL%NOTFOUND THEN
ERR := ERR_FLG;
ELSE
SELECT NVL("残高数量2",0),完了 INTO V数量,V完了 FROM TR07
WHERE
"識別A" = '7'
AND "識別C" = ' '
AND "入力組織" = P組織
AND "伝票番号" = P受注番号
AND "登録行" = P行;
IF V完了 is NULL THEN
IF V数量 <= 0 THEN
UPDATE TR07 SET "完了" = '1'
WHERE
"識別A" = '7'
AND "識別C" = ' '
AND "入力組織" = P組織
AND "伝票番号" = P受注番号
AND "登録行" = P行;
END IF;
ELSE
IF V数量 > 0 THEN
UPDATE TR07 SET "完了" = NULL
WHERE
"識別A" = '7'
AND "識別C" = ' '
AND "入力組織" = P組織
AND "伝票番号" = P受注番号
AND "登録行" = P行;
END IF;
END IF;
END IF;
END IF;
ERR_FLG := 4;
IF ( P区分 = '4' or P区分 = '5' ) and EXIT_FLG = 0 THEN
LOOP
UPDATE SZ03 SET
"現在現物数量2" = "現在現物数量2" + V数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ;
IF SQL%NOTFOUND THEN
INSERT INTO SZ03 VALUES
(V在庫組織,V大分類,V商品ｺｰﾄﾞ
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0); /* 142 */
ELSE
EXIT;
END IF;
END LOOP;
LOOP
UPDATE SZ04 SET
"現在現物数量2" = "現在現物数量2" + V数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE
"組織" = V在庫組織
AND "大分類" = V大分類
AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ
AND "場所区分" = NVL(V場所区分,' ')
AND "場所ｺｰﾄﾞ" = NVL(V場所ｺｰﾄﾞ,' ')
AND "単品ｺｰﾄﾞ" = NVL(V単品ｺｰﾄﾞ,' ');
IF SQL%NOTFOUND THEN
INSERT INTO SZ04 VALUES
(V在庫組織,NVL(V場所区分,' '),NVL(V場所ｺｰﾄﾞ,' '),V大分類,V商品ｺｰﾄﾞ
,NVL(V単品ｺｰﾄﾞ,' ')
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,sysdate,NULL,sysdate,0); /* 83 */
ELSE
EXIT;
END IF;
END LOOP;
END IF;
ERR_FLG := 6;
IF ( P区分 = '6' ) and EXIT_FLG = 0 THEN
OPEN cur_TR01;
FETCH cur_TR01 INTO
V認識;
if cur_TR01%FOUND then
V認識 := 2;
else
V認識 := 1;
end if;
CLOSE cur_TR01;
IF V認識 = '1' THEN
LOOP
UPDATE SZ03 SET
"現在現物数量2" = "現在現物数量2" + V数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE "組織" = V在庫組織 AND "大分類" = V大分類 AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ;
IF SQL%NOTFOUND THEN
INSERT INTO SZ03 VALUES
(V在庫組織,V大分類,V商品ｺｰﾄﾞ
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,sysdate,NULL,sysdate,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0); /* 142 */
ELSE
EXIT;
END IF;
END LOOP;
LOOP
UPDATE SZ04 SET
"現在現物数量2" = "現在現物数量2" + V数量2,
"更新日" = sysdate,
"在庫更新日" = sysdate
WHERE
"組織" = V在庫組織
AND "大分類" = V大分類
AND "商品ｺｰﾄﾞ" = V商品ｺｰﾄﾞ
AND "場所区分" = NVL(V場所区分,' ')
AND "場所ｺｰﾄﾞ" = NVL(V場所ｺｰﾄﾞ,' ')
AND "単品ｺｰﾄﾞ" = NVL(V単品ｺｰﾄﾞ,' ');
IF SQL%NOTFOUND THEN
INSERT INTO SZ04 VALUES
(V在庫組織,NVL(V場所区分,' '),NVL(V場所ｺｰﾄﾞ,' ')
,V大分類,V商品ｺｰﾄﾞ,NVL(V単品ｺｰﾄﾞ,' ')
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
,0,0,0,0,0,0,0,0
,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL
,sysdate,NULL,sysdate,0); /* 83 */
ELSE
EXIT;
END IF;
END LOOP;
END IF;
END IF;
/*=================================
終了処理
=================================*/
DBMS_OUTPUT.PUT_LINE('JHUPD NORMAL END');
EXCEPTION
WHEN OTHERS THEN
-- ERR_FLG := 1;
ERR := ERR_FLG;
DBMS_OUTPUT.PUT_LINE('ERROR AT JHUPD');
ROLLBACK TO UPD_START;
END JHUPD;
/