安徽直连缴款程序包
包头
create or replace package TEDI_AH_TAX_PAYMENT_PKG is
-- Author : CHENGM56
-- Created : 2022/9/21 15:08:06
-- Purpose : 安徽直连缴款程序包
/*===============================================================
* Program Name: TCM_TP_PKG.MAIN
* Author : CHENGM56
* Date : 2022-09-21
* Purpose : 税企直连-缴款模块数据集成入口
* Parameters :
* In P_INTERFACE_ID VARCHAR2 接口实例表id
* Out p_result_code NUMBER 返回码：0-成功 1-失败
* p_result_msg VARCHAR2 返回错误信息
*
* Update History
* Version Date Name Description
* -------- ---------- --------------------------- --------------------
* V1.0 2022-09-21 CHENGM56 Creation
* ===============================================================*/
PROCEDURE PRO_PAYMENT_REC_STATUS(P_REQUEST_ID IN VARCHAR2 /* 请求ID */,
P_ZSUUID IN VARCHAR2 /*征收uuid*/,
P_TAX_AMOUNT IN NUMBER /*金额*/,
P_RESULT_CODE OUT NUMBER /* 返回码：0-成功 1-失败 */,
P_RESULT_MSG OUT VARCHAR2 /* 返回错误信息 */);
end TEDI_AH_TAX_PAYMENT_PKG;
包体
create or replace package body TEDI_AH_TAX_PAYMENT_PKG is
-- Author : CHENGM56
-- Created : 2022/9/21 15:08:06
-- Purpose : 安徽直连缴款程序包
/*===============================================================
* Program Name: TCM_TP_PKG.MAIN
* Author : CHENGM56
* Date : 2022-09-21
* Purpose : 税企直连-缴款
* Parameters :
* In P_INTERFACE_ID VARCHAR2 接口实例表id
* Out p_result_code NUMBER 返回码：0-成功 1-失败
* p_result_msg VARCHAR2 返回错误信息
*
* Update History
* Version Date Name Description
* -------- ---------- --------------------------- --------------------
* V1.0 2022-09-21 CHENGM56 Creation
* ===============================================================*/
PROCEDURE PRO_PAYMENT_REC_STATUS(P_REQUEST_ID IN VARCHAR2 /* 请求ID */,
P_ZSUUID IN VARCHAR2 /*征收uuid*/,
P_TAX_AMOUNT IN NUMBER /*金额*/,
P_RESULT_CODE OUT NUMBER /* 返回码：0-成功 1-失败 */,
P_RESULT_MSG OUT VARCHAR2 /* 返回错误信息 */) AS
P_PROC_NAME VARCHAR2(128) := 'TEDI_AH_TAX_PAYMENT_PKG.PRO_PAYMENT_REC_STATUS'; --获取此过程名，记录日志用
L_EFFECT_ROW_NUM NUMBER ; -- 记录操作的行数
TIS_MSG VARCHAR2(300); -- 记录说明
CNT NUMBER;
V_TAXPAYER_NO VARCHAR2(20);
V_TAXPAYER_NAME VARCHAR2(200);
V_CREATED_BY VARCHAR2(100);
V_LAST_UPDATED_BY VARCHAR2(100);
BEGIN
P_RESULT_CODE := 0; /* 返回码：0-成功 1-失败 */
P_RESULT_MSG := '';
/*程序开始*/
PKG_TAX.PROC_LOG_DEBUG(P_REQUEST_ID,
'1.程序' ||P_PROC_NAME||'开始',
P_RESULT_CODE,
P_RESULT_MSG,
P_PROC_NAME);
-- 获取参数
SELECT S.TAXPAYER_NO,
S.TAXPAYER_NAME,
S.CREATED_BY,
S.LAST_UPDATED_BY
INTO V_TAXPAYER_NO,
V_TAXPAYER_NAME,
V_CREATED_BY,
V_LAST_UPDATED_BY
FROM TBM_OUT_PAY_INFO S -- 纳税人未清缴信息业务表
WHERE S.ZSUUID = P_ZSUUID
AND S.REQUEST_ID = P_REQUEST_ID
;
-- 先判断是否存在
SELECT COUNT(*)
INTO CNT
FROM TBM_PAYMENT_REC_QUERY T -- 缴款记录查询表
WHERE T.ZSUUID = P_ZSUUID
AND T.REQUEST_ID = P_REQUEST_ID
;
IF CNT = 0 THEN
-- 将数据插入 缴款记录查询表
INSERT INTO TBM_PAYMENT_REC_QUERY(ID,
REQUEST_ID,
TAXPAYER_NO,
TAXPAYER_NAME,
YZPZXH,
YZPZMXXH,
YZPZZLDM,
YJSE,
ZSXMDM,
ZSPMDM,
ZSPMMC,
SKSSQQ,
SKSSQZ,
YBTSE,
NSSBRQ,
SBFSDM,
JKQX,
ZNJLREXT,
ZNORJFKORLXEXT,
ZSUUID,
SKSXDM,
SKZLDM,
YZFSRQ,
SKSSSWJGDM,
CZLXDM,
PAYMENT_TIME,
PAYMENT_STATUS_CODE,
PAYMENT_STATUS_DESC,
OPRA_DESC,
DEBIT_RETURN_CODE,
PAGE_PRP_INFO_DESC,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE)
SELECT TBM_PAYMENT_REC_QUERY_S.NEXTVAL,
P_REQUEST_ID,
V_TAXPAYER_NO,
V_TAXPAYER_NAME,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
V_CREATED_BY,
SYSDATE,
V_LAST_UPDATED_BY,
SYSDATE
FROM TBM_OUT_PAY_INFO A
WHERE A.ZSUUID = P_ZSUUID
AND A.REQUEST_ID = P_REQUEST_ID
;
END IF;
--程序结束
PKG_TAX.PROC_LOG_DEBUG(P_REQUEST_ID,
'2.程序' || P_PROC_NAME || '结束',
P_RESULT_CODE,
P_RESULT_MSG,
P_PROC_NAME);
EXCEPTION
WHEN PKG_TAX.MIDEA_LOGGED_EXCEPTION THEN
/*释放资源,如游标,临时表;回滚事务*/
P_RESULT_CODE := 1; /*传递给ETL_TOOL非0错误代码*/
P_RESULT_MSG := '调用子过程发生异常，请参考详细的步骤日志';
RETURN;
WHEN OTHERS THEN
/*释放资源,如游标,临时表;回滚事务*/
P_RESULT_CODE := 1; /*传递给ETL_TOOL非0错误代码*/
P_RESULT_MSG := '[Error Code:' ||
TO_CHAR(SQLCODE) ||
'].SQLERRM:' || SQLERRM;
END PRO_PAYMENT_REC_STATUS;
end TEDI_AH_TAX_PAYMENT_PKG;
select distinct regexp_substr('123,456,789,123',
'[^,]+',
1,
level,
'i') as cnt
from dual
connect by level <= regexp_count('123,456,789,123'/*p_exclude_category_code*/,',') + 1
