📄 pkg_zengin_body.sql
字号:
CURSOR DATA_CURSOR(vcKARI_FURIKOMI_NO IN APT_ZENGIN_DATA.FURIKOMI_NO%TYPE)
IS
SELECT
*
FROM
APT_ZENGIN_DATA
WHERE
FURIKOMI_NO = vcKARI_FURIKOMI_NO
FOR UPDATE
NOWAIT
;
CURSOR HED_CURSOR(vcKARI_FURIKOMI_NO IN APT_ZENGIN_HED.FURIKOMI_NO%TYPE)
IS
SELECT
*
FROM
APT_ZENGIN_HED
WHERE
FURIKOMI_NO = vcKARI_FURIKOMI_NO
FOR UPDATE
NOWAIT
;
BEGIN
----慡嬧僨乕僞娭楢僩儔儞偺峏怴
--慡嬧僨乕僞娭楢僩儔儞偺儘僢僋
OPEN KANREN_CURSOR(vcKARI_FURIKOMI_NO);
--慡嬧僨乕僞娭楢僩儔儞偺峏怴
UPDATE
APT_ZENGIN_DATA_KANREN
SET
FURIKOMI_NO = vcFURIKOMI_NO ,
UPD_RECORD = vcUPD_RECORD
WHERE
FURIKOMI_NO = vcKARI_FURIKOMI_NO
;
CLOSE KANREN_CURSOR;
----慡嬧僨乕僞僩儔儞偺峏怴
--慡嬧僨乕僞僩儔儞偺儘僢僋
OPEN DATA_CURSOR(vcKARI_FURIKOMI_NO);
--慡嬧僨乕僞僩儔儞偺峏怴
UPDATE
APT_ZENGIN_DATA
SET
FURIKOMI_NO = vcFURIKOMI_NO ,
--捛壛 2002/09/25 by 嶳嶈(NHT)
SHIHARAI_YMD = TO_DATE(vcFURIKOMI_YMD, 'YYYY/MM/DD'),
--廔椆 2002/09/25 by 嶳嶈(NHT)
UPD_RECORD = vcUPD_RECORD
WHERE
FURIKOMI_NO = vcKARI_FURIKOMI_NO
;
CLOSE DATA_CURSOR;
----慡嬧僿僢僟乕僩儔儞偺峏怴
--慡嬧僿僢僟乕僩儔儞偺儘僢僋
OPEN HED_CURSOR(vcKARI_FURIKOMI_NO);
--慡嬧僿僢僟乕僩儔儞偺峏怴
UPDATE
APT_ZENGIN_HED
SET
FURIKOMI_NO = vcFURIKOMI_NO ,
UPD_RECORD = vcUPD_RECORD
WHERE
FURIKOMI_NO = vcKARI_FURIKOMI_NO
;
CLOSE HED_CURSOR;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--偦偺懠僄儔乕偑敪惗偟偨帪
RETURN SQLCODE;
END FUNC_UPD_FURIKOMI_NO;
--####################################################
--#---------------------------------------------------
--# 慡嬧僿僢僟乕僩儔儞傪峏怴偡傞
--#
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_UPD_ZENGIN_HED
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗怳崬No VARCHAR2(6)
--# 怳崬寧 VARCHAR2(2)
--# 怳崬擔 VARCHAR2(2)
--# 嵟怴峏怴棜楌 VARCHAR2(27)
--#
--# 栠丂傝丂抣丗惓忢廔椆 0
--# 丗堎忢廔椆 SQLCODE
--#
--# 奣丂丂丂梫丗庤悢椏丄怳崬妟丄怳崬擔偑峏怴偝傟偨
--# 慡嬧僨乕僞僩儔儞偺儗僐乕僪傪廤寁偟丄
--# 慡嬧僿僢僟乕僩儔儞傪峏怴偡傞丅
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/08/29
--# 嵟廔廋惓擔丗
--####################################################
FUNCTION FUNC_UPD_ZENGIN_HED(
vcFURIKOMI_NO VARCHAR2,
vcFURIKOMI_MON VARCHAR2,
vcFURIKOMI_DAY VARCHAR2,
vcUPD_RECORD VARCHAR2
)
RETURN NUMBER IS
CURSOR HED_CURSOR(vcFURIKOMI_NO IN APT_ZENGIN_HED.FURIKOMI_NO%TYPE)
IS
SELECT
*
FROM
APT_ZENGIN_HED
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
FOR UPDATE
NOWAIT
;
BEGIN
----慡嬧僿僢僟乕僩儔儞偺峏怴
--慡嬧僿僢僟乕僩儔儞偺儘僢僋
OPEN HED_CURSOR(vcFURIKOMI_NO);
--慡嬧僿僢僟乕僩儔儞偺峏怴
UPDATE
APT_ZENGIN_HED
SET
FURIKOMI_MON = vcFURIKOMI_MON,
FURIKOMI_DAY = vcFURIKOMI_DAY,
UPD_RECORD = vcUPD_RECORD,
GOUKEI_KENSU = ( SELECT
COUNT(*)
FROM
APT_ZENGIN_DATA
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
) ,
GOUKEI_GAKU = ( SELECT
SUM(KINGAKU)
FROM
APT_ZENGIN_DATA
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
)
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
;
CLOSE HED_CURSOR;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--偦偺懠僄儔乕偑敪惗偟偨帪
RETURN SQLCODE;
END FUNC_UPD_ZENGIN_HED;
/*
--####################################################
--#---------------------------------------------------
--# 慡嬧僨乕僞僩儔儞偺怳崬No偺嵟戝抣傪庢摼偡傞
--#
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_GET_MAX_FURIKOMI_NO
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗側偟
--# 栠丂傝丂抣丗怳崬No偺嵟戝抣 VARCHAR2(6)
--# 丗
--# 奣丂丂丂梫丗慡嬧僨乕僞僩儔儞偐傜怳崬No偺嵟戝抣傪庢摼偡傞
--#
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/08/29
--# 嵟廔廋惓擔丗
--####################################################
*/
FUNCTION FUNC_GET_MAX_FURIKOMI_NO
RETURN VARCHAR2 IS
MAX_FURIKOMI_NO APT_ZENGIN_DATA.FURIKOMI_NO%TYPE;
BEGIN
SELECT
MAX(FURIKOMI_NO)
INTO
MAX_FURIKOMI_NO
FROM
APT_ZENGIN_DATA
WHERE
DEL_FLG = '0'
;
RETURN MAX_FURIKOMI_NO;
END FUNC_GET_MAX_FURIKOMI_NO;
--####################################################
--#---------------------------------------------------
--# 慡嬧僨乕僞僩儔儞偍傛傃堷悢偐傜
--# 嬧峴僔僗僥儉巟暐昞儚乕僋僥乕僽儖傪嶌惉偡傞
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_APW_GINKOU_SYSTEM
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗vcFURIKOMI_NO - 怳崬No
--# vcFURIKOMI_YMD - 怳崬擔
--# vcGINKOU_CD - 嬧峴僐乕僪
--# vcGINKOU_BUNRUI_KBN - 嬧峴暘椶
--# vcUPD_RECORD - 峏怴擔
--# 栠丂傝丂抣丗 惓忢廔椆 0
--# 丗 堎忢廔椆 SQLCODE
--#
--# 奣丂丂丂梫丗慡嬧僨乕僞僩儔儞偍傛傃堷悢偐傜
--# 嬧峴僔僗僥儉巟暐昞儚乕僋僥乕僽儖傪嶌惉偡傞
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/09/02
--# 嵟廔廋惓擔丗
--####################################################
FUNCTION FUNC_APW_GINKOU_SYSTEM(
vcFURIKOMI_NO VARCHAR2,
vcFURIKOMI_YMD VARCHAR2,
vcGINKOU_CD VARCHAR2,
vcGINKOU_BUNRUI_KBN VARCHAR2,
vcUPD_RECORD VARCHAR2
)
RETURN NUMBER IS
BEGIN
INSERT INTO APW_GINKOU_SYSTEM
(
SHIHARAI_YMD,
FURIDASHI_GINKOU_ABBREV,
SHIHARAISAKI_CD,
SHIHARAISAKI_ABBREV,
YOKINSHA_NAME,
GINKOU_NO,
GINKOU_NAME,
SITEN_NO,
SITEN_NAME,
YOKIN_SHUMOKU,
KOUZA_NO,
SHIHARAI_GAKU,
TESUURYOU,
FURIKOMI_GAKU,
UPD_RECORD
)
(SELECT
TO_DATE(vcFURIKOMI_YMD, 'YYYY/MM/DD') AS SHIHARAI_YMD,
GINKOU.GINKOU_ABBREV AS FURIDASHI_GINKOU_ABBREV,
ZENGIN.SHIHARAI_CD AS SHIHARAISAKI_CD,
TORI.TORI_ABBREV AS SHIHARAISAKI_ABBREV,
ZENGIN.YOKINSHA_NAME AS YOKINSHA_NAME,
ZENGIN.GINKOU_NO AS GINKOU_NO,
ZENGIN.GINKOU_NAME AS GINKOU_NAME,
ZENGIN.SITEN_NO AS SITEN_NO,
ZENGIN.SITEN_NAME AS SITEN_NAME,
ZENGIN.YOKIN_SHUMOKU AS YOKIN_SHUMOKU,
ZENGIN.KOUZA_NO AS KOUZA_NO,
ZENGIN.SHIHARAI_GAKU AS SHIHARAI_GAKU,
ZENGIN.TESUURYOU AS TESUURYOU,
ZENGIN.KINGAKU AS FURIKOMI_GAKU,
vcUPD_RECORD AS UPD_RECORD
FROM
APT_ZENGIN_DATA ZENGIN,
(SELECT
GINKOU_ABBREV
FROM
APM_GINKOU
WHERE
GINKOU_CD = vcGINKOU_CD
AND GINKOU_BUNRUI_KBN = vcGINKOU_BUNRUI_KBN
AND DEL_FLG = '0'
) GINKOU,
APM_TORI TORI
WHERE
ZENGIN.FURIKOMI_NO = vcFURIKOMI_NO
AND ZENGIN.SHIHARAI_CD = TORI.TORI_CD
);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--偦偺懠僄儔乕偑敪惗偟偨帪
RETURN SQLCODE;
END FUNC_APW_GINKOU_SYSTEM;
--####################################################
--#---------------------------------------------------
--# 慡嬧僿僢僟乕僩儔儞偺懳徾僨乕僞傪巟暐嵪傒
--#乮嶍彍僼儔僌侾乯偵偡傞
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_DEL_ZENGIN_HED
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗vcFURIKOMI_NO - 怳崬No
--# vcUPD_RECORD - 峏怴擔
--# 栠丂傝丂抣丗 惓忢廔椆 0
--# 丗 堎忢廔椆 SQLCODE
--#
--# 奣丂丂丂梫丗慡嬧僿僢僟乕僩儔儞偺懳徾僨乕僞傪
--# 巟暐嵪傒乮嶍彍僼儔僌侾乯偵偡傞
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/09/02
--# 嵟廔廋惓擔丗
--####################################################
FUNCTION FUNC_DEL_ZENGIN_HED(
vcFURIKOMI_NO VARCHAR2,
vcUPD_RECORD VARCHAR2
)
RETURN NUMBER IS
CURSOR HED_CURSOR(vcFURIKOMI_NO IN APT_ZENGIN_HED.FURIKOMI_NO%TYPE)
IS
SELECT
*
FROM
APT_ZENGIN_HED
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
FOR UPDATE
NOWAIT
;
BEGIN
----慡嬧僿僢僟乕僩儔儞偺峏怴
--慡嬧僿僢僟乕僩儔儞偺儘僢僋
OPEN HED_CURSOR(vcFURIKOMI_NO);
--慡嬧僿僢僟乕僩儔儞偺峏怴
UPDATE
APT_ZENGIN_HED
SET
DEL_FLG = '1',
UPD_RECORD = vcUPD_RECORD
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
AND DEL_FLG = '0'
;
CLOSE HED_CURSOR;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--偦偺懠僄儔乕偑敪惗偟偨帪
RETURN SQLCODE;
END FUNC_DEL_ZENGIN_HED;
--####################################################
--#---------------------------------------------------
--# 慡嬧僨乕僞僩儔儞偺懳徾僨乕僞傪巟暐嵪傒
--#乮嶍彍僼儔僌侾乯偵偡傞
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_DEL_ZENGIN_DATA
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗vcFURIKOMI_NO - 怳崬No
--# vcUPD_RECORD - 峏怴擔
--# 栠丂傝丂抣丗 惓忢廔椆 0
--# 丗 堎忢廔椆 SQLCODE
--#
--# 奣丂丂丂梫丗慡嬧僨乕僞僩儔儞偺懳徾僨乕僞傪巟暐嵪傒
--# 乮嶍彍僼儔僌侾乯偵偡傞
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/09/02
--# 嵟廔廋惓擔丗
--####################################################
FUNCTION FUNC_DEL_ZENGIN_DATA(
vcFURIKOMI_NO VARCHAR2,
vcUPD_RECORD VARCHAR2
)
RETURN NUMBER IS
CURSOR DATA_CURSOR(vcFURIKOMI_NO IN APT_ZENGIN_HED.FURIKOMI_NO%TYPE)
IS
SELECT
*
FROM
APT_ZENGIN_DATA
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
FOR UPDATE
NOWAIT
;
BEGIN
----慡嬧僨乕僞僩儔儞偺峏怴
--慡嬧僨乕僞僩儔儞偺儘僢僋
OPEN DATA_CURSOR(vcFURIKOMI_NO);
--慡嬧僨乕僞僩儔儞偺峏怴
UPDATE
APT_ZENGIN_DATA
SET
DEL_FLG = '1',
UPD_RECORD = vcUPD_RECORD
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
AND DEL_FLG = '0'
;
CLOSE DATA_CURSOR;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--偦偺懠僄儔乕偑敪惗偟偨帪
RETURN SQLCODE;
END FUNC_DEL_ZENGIN_DATA;
--####################################################
--#---------------------------------------------------
--# 慡嬧僨乕僞娭楢僩儔儞偺懳徾僨乕僞傪巟暐嵪傒
--#乮嶍彍僼儔僌侾乯偵偡傞
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_DEL_ZENGIN_DATA_KANREN
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗vcFURIKOMI_NO - 怳崬No
--# vcUPD_RECORD - 峏怴擔
--# 栠丂傝丂抣丗 惓忢廔椆 0
--# 丗 堎忢廔椆 SQLCODE
--#
--# 奣丂丂丂梫丗慡嬧僨乕僞娭楢僩儔儞偺懳徾僨乕僞傪
--# 巟暐嵪傒乮嶍彍僼儔僌侾乯偵偡傞
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/09/02
--# 嵟廔廋惓擔丗
--####################################################
FUNCTION FUNC_DEL_ZENGIN_DATA_KANREN(
vcFURIKOMI_NO VARCHAR2,
vcUPD_RECORD VARCHAR2
)
RETURN NUMBER IS
CURSOR DATA_KANREN_CURSOR(vcFURIKOMI_NO IN APT_ZENGIN_DATA_KANREN.FURIKOMI_NO%TYPE)
IS
SELECT
*
FROM
APT_ZENGIN_DATA_KANREN
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
FOR UPDATE
NOWAIT
;
BEGIN
----慡嬧僨乕僞娭楢僩儔儞偺峏怴
--慡嬧僨乕僞娭楢僩儔儞偺儘僢僋
OPEN DATA_KANREN_CURSOR(vcFURIKOMI_NO);
--慡嬧僨乕僞娭楢僩儔儞偺峏怴
UPDATE
APT_ZENGIN_DATA_KANREN
SET
DEL_FLG = '1',
UPD_RECORD = vcUPD_RECORD
WHERE
FURIKOMI_NO = vcFURIKOMI_NO
AND DEL_FLG = '0'
;
CLOSE DATA_KANREN_CURSOR;
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
--偦偺懠僄儔乕偑敪惗偟偨帪
RETURN SQLCODE;
END FUNC_DEL_ZENGIN_DATA_KANREN;
--####################################################
--#---------------------------------------------------
--# 巟暐僩儔儞丄枹暐僩儔儞偺懳徾僨乕僞傪巟暐嵪傒
--#乮嶍彍僼儔僌侾乯偵偡傞
--#---------------------------------------------------
--# 娭丂悢丂柤丗FUNC_DEL_SHIHARAI_MIBARAI
--# 僶乕僕儑儞丗1.0.0
--# 堷丂偒丂悢丗vcFURIKOMI_NO - 怳崬No
--# vcUPD_RECORD - 峏怴擔
--# 栠丂傝丂抣丗 惓忢廔椆 0
--# 丗 堎忢廔椆 SQLCODE
--#
--# 奣丂丂丂梫丗巟暐僩儔儞丄枹暐僩儔儞偺懳徾僨乕僞傪
--# 巟暐嵪傒乮嶍彍僼儔僌侾乯偵偡傞
--#
--# 嶌丂惉丂幰丗Yamazaki(NHT)
--# 嶌丂惉丂擔丗2002/09/02
--# 嵟廔廋惓擔丗
--####################################################
FUNCTION FUNC_DEL_SHIHARAI_MIBARAI(
vcFURIKOMI_NO VARCHAR2,
vcUPD_RECORD VARCHAR2
)
RETURN NUMBER IS
--巟暐僩儔儞儘僢僋梡SELECT ゥ⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -