CREATE OR REPLACE PACKAGE MEW_AP_BATCH_PAYMENTS_PKG IS
PROCEDURE MAIN(P_BATCH_ID NUMBER
,P_ORG_ID NUMBER
,P_BANK_ACCOUNT_NAME VARCHAR2
,P_CHECK_STOCK_NAME VARCHAR2
,P_CHECK_DATE DATE
,P_CURRENCY_CODE VARCHAR2
,P_INVOICE_NUM VARCHAR2
,P_AMOUNT NUMBER
,P_VENDOR_NUM VARCHAR2
,P_VENDOR_SITE_CODE VARCHAR2
,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE3 DATE DEFAULT NULL
,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL);
---WEBADI API程序错误信息抛出程序
PROCEDURE MEW_PUT_ERROR_P(P_ERROR_MSG IN VARCHAR2);
PROCEDURE SP_AP_ACCOUNTING_11I10(P_CHECK_ID NUMBER);
-- --创建分录过程
PROCEDURE ZJ_11I_BATCH_ACCOUNT(P_ORG_ID IN NUMBER);
--批量付款程序
PROCEDURE BATCH_PAYMENTS_SUB_PRC(RETCODE OUT NUMBER
,ERRBUF OUT VARCHAR2
,P_BATCH_ID NUMBER
,P_PAYMENT_TYPE VARCHAR2
,P_CREATE_JOURNAL_ENTRY_FLAG VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY MEW_AP_BATCH_PAYMENTS_PKG IS
/*===============================================
copyright (c) sie business consulting services
allrights reserved
*================================================
*================================================
* program name:
* MEW_AP_BATCH_PAYMENTS_PKG
* description:
* 发票付款批量导入
* history:
* 1.00 2011-01-06 sie_chenyueyong creation
*===============================================*/
CNT NUMBER := 0;
V_BANK_ACCOUNT_ID NUMBER;
V_CHECK_STOCK_ID NUMBER;
V_CHECK_FORMAT_ID NUMBER;
V_SET_OF_BOOKS_ID NUMBER;
V_PERIOD_NAME VARCHAR2(20);
V_VENDOR_ID NUMBER;
V_VENDOR_SITE_ID NUMBER;
V_CURRENCY_CODE VARCHAR2(20);
V_INVOICE_ID NUMBER;
V_AMOUNT_REMAINING NUMBER;
V_CHECK_ID NUMBER;
V_INVOICE_PAYMENT_ID NUMBER;
V_ACCTS_CODE_COMBINATION_ID NUMBER;
V_DOC_CATEGORY_CODE VARCHAR2(30);
V_DOC_SEQUENCE_ID NUMBER;
V_DB_SEQUENCE_NAME VARCHAR2(30);
V_DOC_SEQUENCE_ASSIGNMENT_ID NUMBER;
V_DB_STR VARCHAR2(2000);
V_DOC_SEQUENCE_VALUE VARCHAR2(30);
V_PAYMENT_METHOD_LOOKUP_CODE VARCHAR2(30);
V_ROWID VARCHAR2(50);
V_CHECK_NUMBER VARCHAR2(30);
V_COUNTRY VARCHAR2(150);
V_CITY VARCHAR2(150);
V_PROVINCE VARCHAR2(150);
V_STATE VARCHAR2(150);
V_COUNTY VARCHAR2(150);
V_ZIP VARCHAR2(150);
V_ADDRESS_LINE1 VARCHAR2(150);
V_ADDRESS_LINE2 VARCHAR2(150);
V_ADDRESS_LINE3 VARCHAR2(150);
V_ADDRESS_LINE4 VARCHAR2(150);
V_BANK_ACCOUNT_NUM VARCHAR2(30);
V_BANK_ACCOUNT_TYPE VARCHAR2(30);
V_VENDOR_NAME VARCHAR2(240);
L_SESSION_ID NUMBER;
V_CHECK_DATE DATE;
V_ORG_ID NUMBER;
V_CURRENT_BATCH_AMOUNT NUMBER;
V_BASE_CURRENCY_CODE VARCHAR2(10);
V_EXCHANGE_RATE NUMBER;
PROCEDURE MAIN(P_BATCH_ID NUMBER
,P_ORG_ID NUMBER
,P_BANK_ACCOUNT_NAME VARCHAR2
,P_CHECK_STOCK_NAME VARCHAR2
,P_CHECK_DATE DATE
,P_CURRENCY_CODE VARCHAR2
,P_INVOICE_NUM VARCHAR2
,P_AMOUNT NUMBER
,P_VENDOR_NUM VARCHAR2
,P_VENDOR_SITE_CODE VARCHAR2
,P_ATTRIBUTE1 VARCHAR2 DEFAULT NULL --汇率类型
,P_ATTRIBUTE2 VARCHAR2 DEFAULT NULL --汇率值
,P_ATTRIBUTE3 DATE DEFAULT NULL --汇率日期
,P_ATTRIBUTE4 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE5 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE6 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE7 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE8 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE9 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE10 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE11 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE12 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE13 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE14 VARCHAR2 DEFAULT NULL
,P_ATTRIBUTE15 VARCHAR2 DEFAULT NULL) IS
V_CANCELLED_DATE DATE;
V_PAYMENT_STATUS_FLAG VARCHAR2(1);
V_WFAPPROVAL_STATUS VARCHAR2(30);
V_HOLD_FLAG VARCHAR2(1);
V_CHECKRUN_ID NUMBER;
V_PURCH_ENCUMBRANCE_FLAG VARCHAR2(1);
CNT1 NUMBER;
BEGIN
/* V_CHECK_DATE := TO_DATE('2010-08-26'
,'YYYY-MM-DD');*/
---初始化环境
/* APPS.FND_GLOBAL.INITIALIZE(SESSION_ID => L_SESSION_ID
,USER_ID => 1027
,RESP_ID => 51385
,RESP_APPL_ID => 200
,SECURITY_GROUP_ID => 0
,SITE_ID => NULL
,LOGIN_ID => 18376457
,CONC_LOGIN_ID => NULL
,PROG_APPL_ID => NULL
,CONC_PROGRAM_ID => NULL
,CONC_REQUEST_ID => NULL
,CONC_PRIORITY_REQUEST => NULL
,FORM_ID => NULL
,FORM_APPL_ID => NULL
,CONC_PROCESS_ID => NULL
,CONC_QUEUE_ID => NULL
,QUEUE_APPL_ID => NULL
,SERVER_ID => NULL);*/
--判断组织ID有效性
IF P_ORG_ID IS NULL
THEN
MEW_PUT_ERROR_P('组织ID不能为空');
ELSE
SELECT COUNT(*)
INTO CNT
FROM HR_OPERATING_UNITS HOU
WHERE HOU.ORGANIZATION_ID = P_ORG_ID;
IF NVL(CNT
,0) = 0
THEN
MEW_PUT_ERROR_P('组织ID不存在');
END IF;
SELECT SET_OF_BOOKS_ID
INTO V_SET_OF_BOOKS_ID
FROM HR_OPERATING_UNITS HOU
WHERE HOU.ORGANIZATION_ID = P_ORG_ID;
SELECT CURRENCY_CODE
INTO V_BASE_CURRENCY_CODE
FROM GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID = V_SET_OF_BOOKS_ID;
END IF;
--判断银行账户有效性
IF P_BANK_ACCOUNT_NAME IS NULL
THEN
MEW_PUT_ERROR_P('银行账户名称不能为空');
ELSE
BEGIN
SELECT ABA.BANK_ACCOUNT_ID
,ABA.BANK_ACCOUNT_NUM
,ABA.BANK_ACCOUNT_TYPE
INTO V_BANK_ACCOUNT_ID
,V_BANK_ACCOUNT_NUM
,V_BANK_ACCOUNT_TYPE
FROM AP_BANK_ACCOUNTS_ALL ABA
WHERE ABA.ORG_ID = P_ORG_ID
AND ABA.BANK_ACCOUNT_NAME = P_BANK_ACCOUNT_NAME
AND ABA.CURRENCY_CODE = P_CURRENCY_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MEW_PUT_ERROR_P('银行账户名称在系统中不存在');
WHEN TOO_MANY_ROWS THEN
MEW_PUT_ERROR_P('获取银行账户ID发生例外' || SUBSTR(SQLCODE
,1
,20));
END;
END IF;
--判断付款单据名称有效性
IF P_CHECK_STOCK_NAME IS NULL
THEN
MEW_PUT_ERROR_P('付款单据名称不能为空');
ELSE
BEGIN
SELECT ACA.CHECK_STOCK_ID
,ACA.CHECK_FORMAT_ID
,ACA.DOC_CATEGORY_CODE
,ACF.PAYMENT_METHOD_LOOKUP_CODE
INTO V_CHECK_STOCK_ID
,V_CHECK_FORMAT_ID
,V_DOC_CATEGORY_CODE
,V_PAYMENT_METHOD_LOOKUP_CODE
FROM AP_CHECK_STOCKS_ALL ACA
,AP_CHECK_FORMATS ACF
WHERE ACF.CHECK_FORMAT_ID = ACA.CHECK_FORMAT_ID
AND NVL(ACA.INACTIVE_DATE
,SYSDATE) >= SYSDATE
AND ACA.NAME = P_CHECK_STOCK_NAME
AND ACA.BANK_ACCOUNT_ID = V_BANK_ACCOUNT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MEW_PUT_ERROR_P('付款单据名称不存在,请检查');
END;
END IF;
IF P_CHECK_DATE IS NULL
THEN
MEW_PUT_ERROR_P('付款日期不能为空');
ELSE
BEGIN
SELECT GPS.PERIOD_NAME
INTO V_PERIOD_NAME
FROM GL_PERIOD_STATUSES GPS
,FND_APPLICATION FA
WHERE GPS.SET_OF_BOOKS_ID = V_SET_OF_BOOKS_ID
AND GPS.APPLICATION_ID = FA.APPLICATION_ID
AND GPS.CLOSING_STATUS = 'O'
AND FA.APPLICATION_SHORT_NAME = 'SQLAP'
AND TRUNC(P_CHECK_DATE) BETWEEN GPS.START_DATE AND GPS.END_DATE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MEW_PUT_ERROR_P('付款日期所在的会计期未打开');
END;
END IF;
IF P_VENDOR_NUM IS NULL
THEN
MEW_PUT_ERROR_P('供应商编号不能为空');
ELSE
BEGIN
SELECT VENDOR_ID
,PV.VENDOR_NAME
INTO V_VENDOR_ID
,V_VENDOR_NAME
FROM PO_VENDORS PV
WHERE PV.SEGMENT1 = P_VENDOR_NUM
AND SYSDATE BETWEEN PV.START_DATE_ACTIVE AND
NVL(PV.END_DATE_ACTIVE
,SYSDATE);
EXCEPTION
WHEN OTHERS THEN
MEW_PUT_ERROR_P('供应商编号不存在');
END;
END IF;
IF P_VENDOR_SITE_CODE IS NULL
THEN
MEW_PUT_ERROR_P('供应商地点不能为空');
ELSE
BEGIN
SELECT VENDOR_SITE_ID
,PVSA.COUNTRY
,PVSA.CITY
,PVSA.PROVINCE
,PVSA.STATE
,PVSA.COUNTY