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;
|