やたらと長いですが、重要な部分は二つです。 1) SAVEPOINT と ROLLBACK の関係 2) UPDATE で 対象データが無い場合に IF SQL%NOTFOUND THEN で、INSERT を実行
001.
/*
002.
/*=================================
003.
受注集計処理
004.
=================================*/
005.
CREATE
or
REPLACE
PROCEDURE
JHUPD
006.
(
007.
ERR
IN
OUT
NUMBER
008.
,P区分
IN
VARCHAR2
-- 1:作成,2:変更,3:集計,4:SZ03,SZ04集計
009.
,P組織
IN
VARCHAR2
-- 入力組織
010.
,P受注番号
IN
NUMBER
011.
,P行
IN
NUMBER
-- 登録行
012.
,P数量1
IN
NUMBER
013.
,P数量2
IN
NUMBER
014.
)
015.
AS
016.
017.
EXIT_FLG NUMBER;
018.
ERR_FLG NUMBER;
019.
V数量 NUMBER;
020.
V数量2 NUMBER;
021.
V入出荷数量2 NUMBER;
022.
V完了
VARCHAR2
(1);
023.
V在庫組織
VARCHAR2
(4);
024.
025.
V大分類
VARCHAR2
(2);
026.
V商品コード
VARCHAR2
(20);
027.
V場所区分
VARCHAR2
(1);
028.
V場所コード
VARCHAR2
(8);
029.
V単品コード
VARCHAR2
(15);
030.
V認識
VARCHAR2
(1);
031.
032.
CURSOR
cur_TR01
is
033.
select
識別A
from
TR01
034.
where
"識別A"
=
'1'
AND
"識別C"
=
' '
AND
"入力組織"
= P組織
AND
"受発注番号"
= P受注番号;
035.
BEGIN
036.
/*=================================
037.
初期処理
038.
=================================*/
039.
DBMS_OUTPUT
.PUT_LINE(
'JHUPD START'
);
040.
SAVEPOINT UPD_START;
041.
ERR_FLG := ERR;
042.
ERR_FLG := 0;
043.
EXIT_FLG := 0;
044.
045.
BEGIN
046.
select
在庫組織
into
V在庫組織
from
NA10
where
組織 = P組織;
047.
EXCEPTION
048.
WHEN
OTHERS
THEN
049.
V在庫組織 := P組織;
050.
END
;
051.
BEGIN
052.
select
053.
大分類,
054.
商品コード,
055.
場所区分,
056.
場所コード,
057.
単品コード,
058.
入力数量2,
059.
入出荷数量2
060.
into
061.
V大分類,
062.
V商品コード,
063.
V場所区分,
064.
V場所コード,
065.
V単品コード,
066.
V数量2,
067.
V入出荷数量2
068.
from
TR07
069.
WHERE
070.
"識別A"
=
'7'
AND
071.
"識別C"
=
' '
AND
072.
"入力組織"
= P組織
AND
073.
"伝票番号"
= P受注番号
AND
074.
"登録行"
= P行;
075.
EXCEPTION
076.
WHEN
OTHERS
THEN
077.
EXIT_FLG := 1;
078.
ERR := 9;
079.
END
;
080.
081.
IF
P区分 =
'5'
THEN
082.
V数量2 := V数量2 * -1;
083.
END
IF
;
084.
085.
086.
087.
088.
/*=================================
089.
主処理
090.
=================================*/
091.
/* 作成 */
092.
ERR_FLG := 1;
093.
IF
P区分 =
'1'
and
EXIT_FLG = 0
THEN
094.
UPDATE
TR07
SET
095.
"入出荷数量1"
= 0
096.
,
"入出荷数量2"
= 0
097.
,
"残高数量1"
= P数量1
098.
,
"残高数量2"
= P数量2
099.
WHERE
"識別A"
=
'7'
AND
"識別C"
=
' '
AND
"入力組織"
= P組織
AND
"伝票番号"
= P受注番号
AND
"登録行"
= P行;
100.
IF
SQL%
NOTFOUND
THEN
101.
ERR := ERR_FLG;
102.
ELSE
103.
LOOP
104.
UPDATE
SZ03
SET
105.
"現在現物数量2"
=
"現在現物数量2"
- P数量2,
106.
"更新日"
= sysdate,
107.
"在庫更新日"
= sysdate
108.
WHERE
"組織"
= V在庫組織
AND
"大分類"
= V大分類
AND
"商品コード"
= V商品コード;
109.
IF
SQL%
NOTFOUND
THEN
110.
INSERT
INTO
SZ03
VALUES
111.
(V在庫組織,V大分類,V商品コード
112.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
113.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
114.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
115.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
116.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
117.
,0,0,0,0,0,0,0,0,0
118.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,sysdate,
NULL
,sysdate,0,0
119.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
120.
,0,0,0);
/* 142 */
121.
ELSE
122.
EXIT
;
123.
END
IF
;
124.
END
LOOP
;
125.
LOOP
126.
UPDATE
SZ04
SET
127.
"現在現物数量2"
=
"現在現物数量2"
- P数量2,
128.
"更新日"
= sysdate,
129.
"在庫更新日"
= sysdate
130.
WHERE
"組織"
= V在庫組織
AND
"大分類"
= V大分類
AND
"商品コード"
= V商品コード
AND
"場所区分"
= NVL(V場所区分,
' '
)
AND
"場所コード"
= NVL(V場所コード,
' '
)
AND
"単品コード"
= NVL(V単品コード,
' '
);
131.
IF
SQL%
NOTFOUND
THEN
132.
INSERT
INTO
SZ04
VALUES
133.
(V在庫組織
134.
,NVL(V場所区分,
' '
)
135.
,NVL(V場所コード,
' '
)
136.
,V大分類
137.
,V商品コード
138.
,NVL(V単品コード,
' '
)
139.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
140.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
141.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
142.
,0,0,0,0,0,0,0,0
143.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
144.
,sysdate,
NULL
,sysdate,0);
/* 83 */
145.
ELSE
146.
EXIT
;
147.
END
IF
;
148.
END
LOOP
;
149.
END
IF
;
150.
151.
END
IF
;
152.
153.
/* 変更 */
154.
ERR_FLG := 2;
155.
IF
P区分 =
'2'
and
EXIT_FLG = 0
THEN
156.
UPDATE
TR07
SET
157.
"残高数量1"
= 入力数量1 -
"入出荷数量1"
158.
,
"残高数量2"
= 入力数量2 -
"入出荷数量2"
159.
WHERE
"識別A"
=
'7'
AND
"識別C"
=
' '
AND
"入力組織"
= P組織
AND
"伝票番号"
= P受注番号
AND
"登録行"
= P行;
160.
IF
SQL%
NOTFOUND
THEN
161.
ERR := ERR_FLG;
162.
ELSE
163.
LOOP
164.
UPDATE
SZ03
SET
165.
"現在現物数量2"
=
"現在現物数量2"
- P数量2,
166.
"更新日"
= sysdate,
167.
"在庫更新日"
= sysdate
168.
WHERE
"組織"
= V在庫組織
AND
"大分類"
= V大分類
AND
"商品コード"
= V商品コード;
169.
IF
SQL%
NOTFOUND
THEN
170.
INSERT
INTO
SZ03
VALUES
171.
(V在庫組織,V大分類,V商品コード,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
172.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
173.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
174.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
175.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
176.
,0,0,0,0,0,0,0,0,0,
NULL
,
NULL
,
NULL
,
NULL
177.
,
NULL
,
NULL
,sysdate,
NULL
,sysdate,0,0
178.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
179.
,0,0,0);
/* 142 */
180.
ELSE
181.
EXIT
;
182.
END
IF
;
183.
END
LOOP
;
184.
LOOP
185.
UPDATE
SZ04
SET
186.
"現在現物数量2"
=
"現在現物数量2"
- P数量2,
187.
"更新日"
= sysdate,
188.
"在庫更新日"
= sysdate
189.
WHERE
"組織"
= V在庫組織
AND
"大分類"
= V大分類
AND
"商品コード"
= V商品コード
AND
"場所区分"
= NVL(V場所区分,
' '
)
AND
"場所コード"
= NVL(V場所コード,
' '
)
AND
"単品コード"
= NVL(V単品コード,
' '
);
190.
IF
SQL%
NOTFOUND
THEN
191.
INSERT
INTO
SZ04
VALUES
192.
(V在庫組織,NVL(V場所区分,
' '
),NVL(V場所コード,
' '
),V大分類,V商品コード
193.
,NVL(V単品コード,
' '
)
194.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
195.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
196.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
197.
,0,0,0,0,0,0,0,0
198.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
199.
,sysdate,
NULL
,sysdate,0);
/* 83 */
200.
ELSE
201.
EXIT
;
202.
END
IF
;
203.
END
LOOP
;
204.
END
IF
;
205.
END
IF
;
206.
207.
/* 集計 */
208.
ERR_FLG := 3;
209.
IF
P区分 =
'3'
and
EXIT_FLG = 0
THEN
210.
UPDATE
TR07
SET
211.
"入出荷数量1"
=
"入出荷数量1"
+ P数量1
212.
,
"入出荷数量2"
=
"入出荷数量2"
+ P数量2
213.
,
"残高数量1"
=
"残高数量1"
- P数量1
214.
,
"残高数量2"
=
"残高数量2"
- P数量2
215.
WHERE
"識別A"
=
'7'
AND
"識別C"
=
' '
AND
"入力組織"
= P組織
AND
"伝票番号"
= P受注番号
AND
"登録行"
= P行;
216.
IF
SQL%
NOTFOUND
THEN
217.
ERR := ERR_FLG;
218.
ELSE
219.
SELECT
NVL(
"残高数量2"
,0),完了
INTO
V数量,V完了
FROM
TR07
220.
WHERE
221.
"識別A"
=
'7'
222.
AND
"識別C"
=
' '
223.
AND
"入力組織"
= P組織
224.
AND
"伝票番号"
= P受注番号
225.
AND
"登録行"
= P行;
226.
IF
V完了
is
NULL
THEN
227.
IF
V数量 <= 0
THEN
228.
UPDATE
TR07
SET
"完了"
=
'1'
229.
WHERE
230.
"識別A"
=
'7'
231.
AND
"識別C"
=
' '
232.
AND
"入力組織"
= P組織
233.
AND
"伝票番号"
= P受注番号
234.
AND
"登録行"
= P行;
235.
END
IF
;
236.
ELSE
237.
IF
V数量 > 0
THEN
238.
UPDATE
TR07
SET
"完了"
=
NULL
239.
WHERE
240.
"識別A"
=
'7'
241.
AND
"識別C"
=
' '
242.
AND
"入力組織"
= P組織
243.
AND
"伝票番号"
= P受注番号
244.
AND
"登録行"
= P行;
245.
END
IF
;
246.
END
IF
;
247.
END
IF
;
248.
END
IF
;
249.
250.
ERR_FLG := 4;
251.
IF
( P区分 =
'4'
or
P区分 =
'5'
)
and
EXIT_FLG = 0
THEN
252.
LOOP
253.
UPDATE
SZ03
SET
254.
"現在現物数量2"
=
"現在現物数量2"
+ V数量2,
255.
"更新日"
= sysdate,
256.
"在庫更新日"
= sysdate
257.
WHERE
"組織"
= V在庫組織
AND
"大分類"
= V大分類
AND
"商品コード"
= V商品コード;
258.
IF
SQL%
NOTFOUND
THEN
259.
INSERT
INTO
SZ03
VALUES
260.
(V在庫組織,V大分類,V商品コード
261.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
262.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
263.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
264.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
265.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
266.
,0,0,0,0,0,0,0,0,0
267.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,sysdate,
NULL
,sysdate,0,0
268.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
269.
,0,0,0);
/* 142 */
270.
ELSE
271.
EXIT
;
272.
END
IF
;
273.
END
LOOP
;
274.
LOOP
275.
UPDATE
SZ04
SET
276.
"現在現物数量2"
=
"現在現物数量2"
+ V数量2,
277.
"更新日"
= sysdate,
278.
"在庫更新日"
= sysdate
279.
WHERE
280.
"組織"
= V在庫組織
281.
AND
"大分類"
= V大分類
282.
AND
"商品コード"
= V商品コード
283.
AND
"場所区分"
= NVL(V場所区分,
' '
)
284.
AND
"場所コード"
= NVL(V場所コード,
' '
)
285.
AND
"単品コード"
= NVL(V単品コード,
' '
);
286.
IF
SQL%
NOTFOUND
THEN
287.
INSERT
INTO
SZ04
VALUES
288.
(V在庫組織,NVL(V場所区分,
' '
),NVL(V場所コード,
' '
),V大分類,V商品コード
289.
,NVL(V単品コード,
' '
)
290.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
291.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
292.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
293.
,0,0,0,0,0,0,0,0
294.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
295.
,sysdate,
NULL
,sysdate,0);
/* 83 */
296.
ELSE
297.
EXIT
;
298.
END
IF
;
299.
END
LOOP
;
300.
END
IF
;
301.
302.
ERR_FLG := 6;
303.
IF
( P区分 =
'6'
)
and
EXIT_FLG = 0
THEN
304.
OPEN
cur_TR01;
305.
306.
FETCH
cur_TR01
INTO
307.
V認識;
308.
if
cur_TR01%
FOUND
then
309.
V認識 := 2;
310.
else
311.
V認識 := 1;
312.
end
if
;
313.
CLOSE
cur_TR01;
314.
IF
V認識 =
'1'
THEN
315.
316.
LOOP
317.
318.
319.
UPDATE
SZ03
SET
320.
"現在現物数量2"
=
"現在現物数量2"
+ V数量2,
321.
"更新日"
= sysdate,
322.
"在庫更新日"
= sysdate
323.
WHERE
"組織"
= V在庫組織
AND
"大分類"
= V大分類
AND
"商品コード"
= V商品コード;
324.
IF
SQL%
NOTFOUND
THEN
325.
INSERT
INTO
SZ03
VALUES
326.
(V在庫組織,V大分類,V商品コード
327.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
328.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
329.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
330.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
331.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
332.
,0,0,0,0,0,0,0,0,0
333.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,sysdate,
NULL
,sysdate,0,0
334.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
335.
,0,0,0);
/* 142 */
336.
ELSE
337.
EXIT
;
338.
END
IF
;
339.
END
LOOP
;
340.
LOOP
341.
UPDATE
SZ04
SET
342.
"現在現物数量2"
=
"現在現物数量2"
+ V数量2,
343.
"更新日"
= sysdate,
344.
"在庫更新日"
= sysdate
345.
WHERE
346.
"組織"
= V在庫組織
347.
AND
"大分類"
= V大分類
348.
AND
"商品コード"
= V商品コード
349.
AND
"場所区分"
= NVL(V場所区分,
' '
)
350.
AND
"場所コード"
= NVL(V場所コード,
' '
)
351.
AND
"単品コード"
= NVL(V単品コード,
' '
);
352.
IF
SQL%
NOTFOUND
THEN
353.
INSERT
INTO
SZ04
VALUES
354.
(V在庫組織,NVL(V場所区分,
' '
),NVL(V場所コード,
' '
)
355.
,V大分類,V商品コード,NVL(V単品コード,
' '
)
356.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0
357.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
358.
,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0
359.
,0,0,0,0,0,0,0,0
360.
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
,
NULL
361.
,sysdate,
NULL
,sysdate,0);
/* 83 */
362.
ELSE
363.
EXIT
;
364.
END
IF
;
365.
366.
END
LOOP
;
367.
368.
END
IF
;
369.
END
IF
;
370.
/*=================================
371.
終了処理
372.
=================================*/
373.
DBMS_OUTPUT
.PUT_LINE(
'JHUPD NORMAL END'
);
374.
375.
EXCEPTION
376.
WHEN
OTHERS
THEN
377.
-- ERR_FLG := 1;
378.
ERR := ERR_FLG;
379.
DBMS_OUTPUT
.PUT_LINE(
'ERROR AT JHUPD'
);
380.
ROLLBACK
TO
UPD_START;
381.
END
JHUPD;
382.
/