日记账导入API

PROCEDURE IMPORT_GL_INTERFACE IS
CURSOR CUR_GL_TEMP IS
SELECT GL.REFERENCE1, --批名
GL.REFERENCE2, --批说明
GL.GL_NAME, --日记账
GL.DESCRIPTION, --日记账说明
GL.SET_BOOK_NAME, --分类账套
GL.PERIOD_NAME, --期间
GL.DATE_CREATED, --创建时间
GL.CURRENCY_CODE, --币别
TRIM(GL.CODE_COMBINATIONS) CODE_COMBINATIONS, --账户
GL.ACCOUNTED_DR, --借项
GL.ACCOUNTED_CR, --贷项
GL.LINE_DESCRIPTION, --行说明
GL.CE_FLOW --现金流
FROM GL.GL_INTERFACE_TEMP GL;
E_CHECK_HEADER_FAILURE EXCEPTION;
V_LEDGER_COUNT NUMBER := 0;
V_SET_BOOK_NAME VARCHAR2(100);
V_ERR_MESSAGE VARCHAR2(100);
L_SET_OF_BOOKS_ID NUMBER := 0;
L_IFACE_REC GL_INTERFACE%ROWTYPE;
V_PERIOD_COUNT NUMBER := 0;
V_PERIOD_NAME VARCHAR2(100);
L_COUNT NUMBER := 0;
L_REQUEST_ID NUMBER;
--L_SET_OF_BOOKS_ID NUMBER := 2052;
L_USER_JE_SOURCE_NAME GL_JE_SOURCES_TL.USER_JE_SOURCE_NAME%TYPE := 'Manual';
L_USER_JE_CATEGORY_NAME GL_JE_CATEGORIES_TL.USER_JE_CATEGORY_NAME%TYPE := '记账凭证';
L_JE_SOURCE_NAME GL_JE_SOURCES_TL.JE_SOURCE_NAME%TYPE;
L_INTERFACE_RUN_ID NUMBER;
L_RESP_ID NUMBER;
L_RESP_APPL_ID NUMBER;
V_SUM_DR NUMBER;
V_SUM_CR NUMBER;
V_GROUP_ID NUMBER;
L_CURRENCY_COUNT NUMBER;
L_CURRENCY_CODE VARCHAR2(100);
V_REFERENCE1_COUNT NUMBER;
V_REFERENCE1 VARCHAR2(100);
V_REFERENCE2 VARCHAR2(100);
V_NAME_COUNT NUMBER;
L_DATE_COUNT NUMBER;
L_DATE_CREATED DATE;
L_DESCRIPTION VARCHAR2(100); --公司
L_FLEX_COUNT NUMBER;
BEGIN
DELETE FROM CUX_IMPORT_DATA_ERROR WHERE COLUMN_VALUES = '日记账导入';
COMMIT;
BEGIN
--验证批
BEGIN
--验证是否只有一个批
SELECT COUNT(DISTINCT REFERENCE1)
INTO V_REFERENCE1_COUNT
FROM GL.GL_INTERFACE_TEMP CWI;
IF V_REFERENCE1_COUNT <> 1 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入数据只能是一个批,不能为多个批-';
RAISE E_CHECK_HEADER_FAILURE;
ELSE
--获取批
SELECT TRIM(REFERENCE1), TRIM(REFERENCE2)
INTO V_REFERENCE1, V_REFERENCE2
FROM GL.GL_INTERFACE_TEMP CWI
WHERE ROWNUM = 1;
END IF;
END;
BEGIN
--整体验证
--验证是否只有一个账套
SELECT COUNT(DISTINCT SET_BOOK_NAME)
INTO V_LEDGER_COUNT
FROM GL.GL_INTERFACE_TEMP CWI;
IF V_LEDGER_COUNT <> 1 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入数据只能是一个账套,不能为多个账套-';
RAISE E_CHECK_HEADER_FAILURE;
ELSE
--获取账套
SELECT SET_BOOK_NAME
INTO V_SET_BOOK_NAME
FROM GL.GL_INTERFACE_TEMP CWI
WHERE ROWNUM = 1;
END IF;
END;
--取账套ID
BEGIN
SELECT LEDGER_ID, DESCRIPTION
INTO L_SET_OF_BOOKS_ID, L_DESCRIPTION
FROM GL_LEDGERS
WHERE NAME = V_SET_BOOK_NAME;
EXCEPTION
WHEN OTHERS THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-不存在这个账套-' || V_SET_BOOK_NAME;
RAISE E_CHECK_HEADER_FAILURE;
END;
BEGIN
--整体验证
--验证是否只有一个账套
SELECT COUNT(DISTINCT PERIOD_NAME)
INTO V_PERIOD_COUNT
FROM GL.GL_INTERFACE_TEMP CWI;
IF V_PERIOD_COUNT <> 1 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入数据只能是一个期间,不能为多个期间-';
RAISE E_CHECK_HEADER_FAILURE;
ELSE
--获取期间
SELECT PERIOD_NAME
INTO V_PERIOD_NAME
FROM GL.GL_INTERFACE_TEMP CWI
WHERE ROWNUM = 1;
END IF;
END;
--验证币别
BEGIN
--整体验证
--验证是否只有一个币别
SELECT COUNT(DISTINCT CURRENCY_CODE)
INTO L_CURRENCY_COUNT
FROM GL.GL_INTERFACE_TEMP CWI;
IF L_CURRENCY_COUNT <> 1 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入数据只能是一个币别,不能为多个币别-';
RAISE E_CHECK_HEADER_FAILURE;
ELSE
--获取币别
SELECT CURRENCY_CODE
INTO L_CURRENCY_CODE
FROM GL.GL_INTERFACE_TEMP CWI
WHERE ROWNUM = 1;
--是否有效
SELECT COUNT(*)
INTO L_CURRENCY_COUNT
FROM GL_CURRENCIES GC
WHERE ENABLED_FLAG = 'Y'
AND GC.CURRENCY_CODE = L_CURRENCY_CODE;
IF L_CURRENCY_COUNT = 0 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入数据币别是无效的-';
RAISE E_CHECK_HEADER_FAILURE;
END IF;
END IF;
END;
--判断GL期间是否打开
BEGIN
SELECT COUNT(*)
INTO L_COUNT
FROM GL_PERIOD_STATUSES
WHERE SET_OF_BOOKS_ID = L_SET_OF_BOOKS_ID
AND CLOSING_STATUS = 'O' --打开状态
AND PERIOD_NAME = V_PERIOD_NAME; --期间
IF L_COUNT = 0 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-期间-' || V_PERIOD_NAME ||
'不是打开状态';
RAISE E_CHECK_HEADER_FAILURE;
ELSE
L_IFACE_REC.PERIOD_NAME := V_PERIOD_NAME;
END IF;
END;
--日期
BEGIN
--整体验证
--验证是否只有一个币别
SELECT COUNT(DISTINCT DATE_CREATED)
INTO L_DATE_COUNT
FROM GL.GL_INTERFACE_TEMP CWI;
IF L_CURRENCY_COUNT <> 1 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入数据只能是一个有效日期,不能为多个有效日期-';
RAISE E_CHECK_HEADER_FAILURE;
ELSE
--获取有效日期
SELECT DATE_CREATED
INTO L_DATE_CREATED
FROM GL.GL_INTERFACE_TEMP CWI
WHERE ROWNUM = 1;
SELECT COUNT(*)
INTO L_COUNT
FROM GL_PERIOD_STATUSES T
WHERE SET_OF_BOOKS_ID = L_SET_OF_BOOKS_ID
AND CLOSING_STATUS = 'O' --打开状态
AND PERIOD_NAME = V_PERIOD_NAME
AND T.START_DATE <= L_DATE_CREATED
AND T.END_DATE >= L_DATE_CREATED; --期间
IF L_COUNT = 0 THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-导入有效日期和期间不在同个范围内-';
RAISE E_CHECK_HEADER_FAILURE;
END IF;
END IF;
END;
--借项是否等于贷项
BEGIN
--整体验证
SELECT SUM(round(NVL(CWI.ACCOUNTED_DR, 0),2)), SUM(round(NVL(CWI.ACCOUNTED_CR, 0),2))
INTO V_SUM_DR, V_SUM_CR
FROM GL.GL_INTERFACE_TEMP CWI;
IF V_SUM_DR <> V_SUM_CR THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-借项不等于贷项-';
RAISE E_CHECK_HEADER_FAILURE;
END IF;
END;
FND_GLOBAL.SET_NLS_CONTEXT('AMERICAN');
SELECT FAP.APPLICATION_ID
INTO L_RESP_APPL_ID
FROM FND_APPLICATION FAP
WHERE FAP.APPLICATION_SHORT_NAME = 'SQLGL';
-- DBMS_OUTPUT.PUT_LINE('l_resp_appl_id:=' || L_RESP_APPL_ID);
--这部分是为了确定模拟登陆中职责ID
SELECT FR.RESPONSIBILITY_ID
INTO L_RESP_ID
FROM FND_RESPONSIBILITY_TL FR
WHERE FR.RESPONSIBILITY_NAME = '40ZZDQ_GL_SUPERUSER' --这个是我总账职责的名字
AND FR.APPLICATION_ID = L_RESP_APPL_ID
AND FR.LANGUAGE = 'ZHS';
-- DBMS_OUTPUT.PUT_LINE('l_resp_id:=' || L_RESP_ID);
FND_GLOBAL.APPS_INITIALIZE(USER_ID => 4467, --FND_GLOBAL.USER_ID,
RESP_ID => L_RESP_ID,
RESP_APPL_ID => L_RESP_APPL_ID);
--日记账
--头说明
BEGIN
SELECT DESCRIPTION
INTO L_IFACE_REC.REFERENCE5
FROM GL.GL_INTERFACE_TEMP CWI
WHERE ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
L_IFACE_REC.REFERENCE5 := NULL;
END;
SELECT GL_INTERFACE_CONTROL_S.NEXTVAL INTO V_GROUP_ID FROM DUAL;
L_IFACE_REC.GROUP_ID := V_GROUP_ID;
L_IFACE_REC.STATUS := 'NEW';
L_IFACE_REC.SET_OF_BOOKS_ID := L_SET_OF_BOOKS_ID;
/*L_IFACE_REC.ACCOUNTING_DATE := SYSDATE;
L_IFACE_REC.DATE_CREATED := SYSDATE;*/
L_IFACE_REC.CREATED_BY := FND_GLOBAL.USER_ID; --FND_GLOBAL.USER_ID;
L_IFACE_REC.ACTUAL_FLAG := 'A';
L_IFACE_REC.USER_JE_SOURCE_NAME := L_USER_JE_SOURCE_NAME;
L_IFACE_REC.USER_JE_CATEGORY_NAME := L_USER_JE_CATEGORY_NAME;
L_IFACE_REC.REFERENCE1 := V_REFERENCE1;
L_IFACE_REC.REFERENCE2 := V_REFERENCE2;
-- L_IFACE_REC.REFERENCE4 :='日记账的名字';
-- L_IFACE_REC.REFERENCE5 := '头说明';
-- L_IFACE_REC.REFERENCE10 := '行说明';
L_IFACE_REC.CURRENCY_CODE := L_CURRENCY_CODE; -- 'CNY';
-- L_IFACE_REC.CURRENCY_CODE := 'CNY';
-- L_IFACE_REC.PERIOD_NAME := 'SEP-20';
FOR REC_GL_TEMP IN CUR_GL_TEMP LOOP
--日记账
IF REC_GL_TEMP.GL_NAME IS NOT NULL THEN
L_IFACE_REC.REFERENCE4 := REC_GL_TEMP.GL_NAME;
ELSE
V_ERR_MESSAGE := V_ERR_MESSAGE || '-日记账不能为空-';
RAISE E_CHECK_HEADER_FAILURE;
END IF;

BEGIN
SELECT COUNT(1)
INTO L_FLEX_COUNT
FROM FND_FLEX_VALUES_VL
WHERE DESCRIPTION = L_DESCRIPTION
AND FLEX_VALUE = SUBSTR(REC_GL_TEMP.CODE_COMBINATIONS, 1, 2);
END;
IF L_FLEX_COUNT=0 then
V_ERR_MESSAGE := V_ERR_MESSAGE ||L_DESCRIPTION|| '不存在这公司以' ||
SUBSTR(REC_GL_TEMP.CODE_COMBINATIONS, 1, 2)||'开头';
RAISE E_CHECK_HEADER_FAILURE;
END IF ;
BEGIN
SELECT CODE_COMBINATION_ID
INTO L_IFACE_REC.CODE_COMBINATION_ID
FROM GL_CODE_COMBINATIONS
WHERE SEGMENT1 || '-' || SEGMENT2 || '-' || SEGMENT3 || '-' ||
SEGMENT4 || '-' || SEGMENT5 || '-' || SEGMENT6 || '-' ||
SEGMENT7 = TRIM(REC_GL_TEMP.CODE_COMBINATIONS);
EXCEPTION
WHEN OTHERS THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-不存在这账户-' ||
REC_GL_TEMP.CODE_COMBINATIONS;
RAISE E_CHECK_HEADER_FAILURE;
END;
IF REC_GL_TEMP.DATE_CREATED IS NULL THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-日期不能为空-' ||
REC_GL_TEMP.DATE_CREATED;
RAISE E_CHECK_HEADER_FAILURE;
ELSE
L_IFACE_REC.ACCOUNTING_DATE := REC_GL_TEMP.DATE_CREATED;
L_IFACE_REC.DATE_CREATED := SYSDATE;
END IF;
IF NVL(REC_GL_TEMP.ACCOUNTED_DR, 0) <> 0 AND
NVL(REC_GL_TEMP.ACCOUNTED_CR, 0) = 0 THEN
--借方
L_IFACE_REC.ENTERED_DR := round(nvl(REC_GL_TEMP.ACCOUNTED_DR,0),2);
L_IFACE_REC.ACCOUNTED_DR := round(nvl(REC_GL_TEMP.ACCOUNTED_DR,0),2);
L_IFACE_REC.ENTERED_CR := NULL;
L_IFACE_REC.ACCOUNTED_CR := NULL;

L_IFACE_REC.DATE_CREATED := TO_DATE(REC_GL_TEMP.DATE_CREATED);
L_IFACE_REC.REFERENCE10 := REC_GL_TEMP.LINE_DESCRIPTION; --行说明
ELSIF NVL(REC_GL_TEMP.ACCOUNTED_DR, 0) = 0 AND
NVL(REC_GL_TEMP.ACCOUNTED_CR, 0) <> 0 THEN
--贷方
L_IFACE_REC.ENTERED_DR := NULL;
L_IFACE_REC.ACCOUNTED_DR := NULL;
L_IFACE_REC.ENTERED_CR := round(nvl(REC_GL_TEMP.ACCOUNTED_CR,0),2);
L_IFACE_REC.ACCOUNTED_CR := round(nvl(REC_GL_TEMP.ACCOUNTED_CR,0),2);
L_IFACE_REC.DATE_CREATED := TO_DATE(REC_GL_TEMP.DATE_CREATED);
L_IFACE_REC.REFERENCE10 := REC_GL_TEMP.LINE_DESCRIPTION; --行说明
END IF;
--获取现金流
IF REC_GL_TEMP.CE_FLOW IS NULL THEN
IF MEG_CUX_ODD.GET_GL_MEG_ACCOUNT_ATTR1(L_IFACE_REC.CODE_COMBINATION_ID) = 'Y' THEN
V_ERR_MESSAGE := V_ERR_MESSAGE || '-银行账户的现金流必输-' ||
REC_GL_TEMP.CE_FLOW;
RAISE E_CHECK_HEADER_FAILURE;
ELSE
L_IFACE_REC.ATTRIBUTE5 := NULL;
END IF;
ELSE
BEGIN
SELECT FFV.FLEX_VALUE_MEANING
INTO L_IFACE_REC.ATTRIBUTE5
FROM FND_FLEX_VALUES_VL FFV
WHERE (FLEX_VALUE_SET_ID = 1013011)
AND FFV.DESCRIPTION = REC_GL_TEMP.CE_FLOW;
EXCEPTION
WHEN OTHERS THEN
L_IFACE_REC.ATTRIBUTE5 := NULL;
END;
END IF;
INSERT INTO GL_INTERFACE VALUES L_IFACE_REC;
END LOOP;
SELECT MAX(JES.JE_SOURCE_NAME)
INTO L_JE_SOURCE_NAME
FROM GL_JE_SOURCES_VL JES
WHERE JES.USER_JE_SOURCE_NAME = 'Manual'; -- L_USER_JE_SOURCE_NAME;
SELECT GL_INTERFACE_CONTROL_S.NEXTVAL
INTO L_INTERFACE_RUN_ID
FROM DUAL;
GL_JOURNAL_IMPORT_PKG.POPULATE_INTERFACE_CONTROL(USER_JE_SOURCE_NAME => 'Manual',
GROUP_ID => V_GROUP_ID, --V_GROUP_ID,
SET_OF_BOOKS_ID => L_SET_OF_BOOKS_ID,
INTERFACE_RUN_ID => L_INTERFACE_RUN_ID);
INSERT INTO GL_INTERFACE_CONTROL
(JE_SOURCE_NAME,
GROUP_ID,
INTERFACE_RUN_ID,
SET_OF_BOOKS_ID,
STATUS)
VALUES
(L_JE_SOURCE_NAME,
L_IFACE_REC.GROUP_ID,
L_INTERFACE_RUN_ID,
L_SET_OF_BOOKS_ID,
'S');
COMMIT;
L_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST('SQLGL',
'GLLEZL',
'',
TO_CHAR(SYSDATE,
'YYYY/MM/DD HH24:MI:SS'),
FALSE,
L_INTERFACE_RUN_ID,
L_SET_OF_BOOKS_ID,
'N',
NULL,
NULL,
'N',
'O',
CHR(0));

COMMIT;
-- DBMS_OUTPUT.PUT_LINE('Request ID ' || L_REQUEST_ID);
EXCEPTION
WHEN E_CHECK_HEADER_FAILURE THEN
INSERT INTO CUX_IMPORT_DATA_ERROR
(ID, ERR_MESSAGE, COLUMN_VALUES)
VALUES
(1, V_ERR_MESSAGE, '日记账导入');
END;
COMMIT;
END IMPORT_GL_INTERFACE;