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
,PVSA.ZIP
,PVSA.ADDRESS_LINE1
,PVSA.ADDRESS_LINE2
,PVSA.ADDRESS_LINE3
,PVSA.ADDRESS_LINE4
INTO V_VENDOR_SITE_ID
,V_COUNTRY
,V_CITY
,V_PROVINCE
,V_STATE
,V_COUNTY
,V_ZIP
,V_ADDRESS_LINE1
,V_ADDRESS_LINE2
,V_ADDRESS_LINE3
,V_ADDRESS_LINE4
FROM PO_VENDOR_SITES_ALL PVSA
WHERE PVSA.VENDOR_ID = V_VENDOR_ID
AND PVSA.VENDOR_SITE_CODE = P_VENDOR_SITE_CODE
AND PVSA.ORG_ID = P_ORG_ID;
EXCEPTION
WHEN OTHERS THEN
MEW_PUT_ERROR_P('供应商地点不存在');
END;
END IF;
IF P_CURRENCY_CODE IS NULL
THEN
MEW_PUT_ERROR_P('付款币别不能为空');
END IF;
IF P_INVOICE_NUM IS NULL
THEN
MEW_PUT_ERROR_P('发票编号不能为空');
ELSE
BEGIN
SELECT AIA.INVOICE_ID
,AIA.INVOICE_CURRENCY_CODE
,AIA.ACCTS_PAY_CODE_COMBINATION_ID
,AIA.CANCELLED_DATE
,AIA.PAYMENT_STATUS_FLAG
,AIA.WFAPPROVAL_STATUS
INTO V_INVOICE_ID
,V_CURRENCY_CODE
,V_ACCTS_CODE_COMBINATION_ID
,V_CANCELLED_DATE
,V_PAYMENT_STATUS_FLAG
,V_WFAPPROVAL_STATUS
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_NUM = P_INVOICE_NUM
AND AIA.VENDOR_ID = V_VENDOR_ID
AND AIA.VENDOR_SITE_ID = V_VENDOR_SITE_ID
AND AIA.INVOICE_CURRENCY_CODE = P_CURRENCY_CODE;
IF V_CANCELLED_DATE IS NOT NULL
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票已取消，不能执行付款');
END IF;
IF V_PAYMENT_STATUS_FLAG NOT IN ('P'
,'N')
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票付款状态为' || V_PAYMENT_STATUS_FLAG ||
'不能执行付款');
END IF;
IF V_WFAPPROVAL_STATUS NOT IN
('WFAPPROVED'
,'NOT REQUIRED'
,'MANUALLY APPROVED')
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票未审批，不能执行付款');
END IF;
SELECT APA.HOLD_FLAG
,APA.CHECKRUN_ID
INTO V_HOLD_FLAG
,V_CHECKRUN_ID
FROM AP_PAYMENT_SCHEDULES_ALL APA
WHERE APA.INVOICE_ID = V_INVOICE_ID
AND ROWNUM = 1; -- add by zhuhailong
IF NVL(V_HOLD_FLAG
,'N') <> 'N'
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票被HOLD,不能执行付款');
END IF;
IF V_CHECKRUN_ID IS NOT NULL
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票已执行付款');
END IF;
SELECT COUNT(*)
INTO CNT
FROM AP_HOLDS AH
WHERE AH.INVOICE_ID = V_INVOICE_ID
AND AH.RELEASE_LOOKUP_CODE IS NULL;
IF NVL(CNT
,0) > 0
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票被HOLD,不能执行付款');
END IF;
SELECT COUNT(*)
INTO CNT
FROM AP_INVOICE_DISTRIBUTIONS D
WHERE D.INVOICE_ID = V_INVOICE_ID;
IF NVL(CNT
,0) = 0
THEN
APP_EXCEPTION.RAISE_EXCEPTION('发票没有分配行，不能执行付款');
END IF;
SELECT FSP.PURCH_ENCUMBRANCE_FLAG
INTO V_PURCH_ENCUMBRANCE_FLAG
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE FSP.ORG_ID = P_ORG_ID;
SELECT COUNT(*)
INTO CNT
FROM AP_INVOICE_DISTRIBUTIONS D2
WHERE D2.INVOICE_ID = V_INVOICE_ID
AND NVL(D2.MATCH_STATUS_FLAG
,'N') IN ('N'
,'S');
/* IF NVL(CNT
,0) = 0 AND
V_PURCH_ENCUMBRANCE_FLAG = 'N'
THEN
NULL;
ELSE
APP_EXCEPTION.RAISE_EXCEPTION('发票不符合付款条件');
END IF;*/
SELECT COUNT(*)
INTO CNT1
FROM AP_INVOICE_DISTRIBUTIONS D3
WHERE D3.INVOICE_ID = V_INVOICE_ID
AND NVL(D3.MATCH_STATUS_FLAG
,'N') <> 'A';
IF (NVL(CNT1
,0) = 0 AND V_PURCH_ENCUMBRANCE_FLAG = 'Y') OR
(NVL(CNT
,0) = 0 AND V_PURCH_ENCUMBRANCE_FLAG = 'N')
THEN
NULL;
ELSE
APP_EXCEPTION.RAISE_EXCEPTION('发票不符合付款条件');
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- MEW_PUT_ERROR_P('发票编号(发票的币别/供应商编号/供应商地点等于当前币别/供应商编号/供应商地点)不存在');
MEW_PUT_ERROR_P('发票编号不存在');
--APP_EXCEPTION.RAISE_EXCEPTION(SQLERRM);
END;
END IF;
IF P_CURRENCY_CODE <> V_CURRENCY_CODE
THEN
MEW_PUT_ERROR_P('付款币别与发票币别不一致');
END IF;
IF NVL(P_AMOUNT
,0) = 0
THEN
MEW_PUT_ERROR_P('付款金额不能为空或等于零');
ELSE
SELECT AIA.INVOICE_AMOUNT - NVL(AIA.AMOUNT_PAID
,0)
INTO V_AMOUNT_REMAINING
FROM AP_INVOICES_ALL AIA
WHERE AIA.INVOICE_ID = V_INVOICE_ID;
SELECT NVL(SUM(MAIP.AMOUNT)
,0)
INTO V_CURRENT_BATCH_AMOUNT
FROM MEW_AP_INVOICE_PAYMENTS MAIP
WHERE MAIP.INVOICE_ID = V_INVOICE_ID;
IF P_AMOUNT > V_AMOUNT_REMAINING - V_CURRENT_BATCH_AMOUNT
THEN
MEW_PUT_ERROR_P('付款金额大于发票余额');
END IF;
END IF;
IF P_CURRENCY_CODE <> V_BASE_CURRENCY_CODE
THEN
IF P_ATTRIBUTE1 IS NULL
THEN
MEW_PUT_ERROR_P('当前币别是外币，汇率类型不能为空');
ELSE
IF P_ATTRIBUTE3 IS NULL --汇率日期
THEN
APP_EXCEPTION.RAISE_EXCEPTION('汇率日期不能为空！');
ELSE
IF P_ATTRIBUTE1 = 'User' --用户汇率为USER
THEN
IF P_ATTRIBUTE2 IS NULL
THEN
APP_EXCEPTION.RAISE_EXCEPTION('汇率不能为空');
ELSE
V_EXCHANGE_RATE := P_ATTRIBUTE2;
END IF;
ELSE
BEGIN
SELECT GDV.SHOW_CONVERSION_RATE
INTO V_EXCHANGE_RATE
FROM GL_DAILY_RATES_V GDV
WHERE GDV.FROM_CURRENCY = P_CURRENCY_CODE
AND GDV.TO_CURRENCY = V_BASE_CURRENCY_CODE
AND GDV.CONVERSION_DATE = TRUNC(P_ATTRIBUTE3)
AND GDV.USER_CONVERSION_TYPE = P_ATTRIBUTE1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
MEW_PUT_ERROR_P('汇率类型不存在或汇率日期不存在当前汇率类型的汇率');
END;
END IF;
END IF;
END IF;
END IF;
INSERT INTO MEW_AP_INVOICE_PAYMENTS
SELECT P_BATCH_ID
,P_BANK_ACCOUNT_NAME
,P_CHECK_STOCK_NAME
,P_CHECK_DATE
,P_CURRENCY_CODE
,P_INVOICE_NUM
,P_AMOUNT
,P_VENDOR_NUM
,P_VENDOR_SITE_CODE
,P_ATTRIBUTE1 --汇率类型
,V_EXCHANGE_RATE --汇率
,P_ATTRIBUTE3 --汇率日期
,P_ATTRIBUTE4
,P_ATTRIBUTE5
,P_ATTRIBUTE6
,P_ATTRIBUTE7
,P_ATTRIBUTE8
,P_ATTRIBUTE9
,P_ATTRIBUTE10
,P_ATTRIBUTE11
,P_ATTRIBUTE12
,P_ATTRIBUTE13
,P_ATTRIBUTE14
,P_ATTRIBUTE15
,SYSDATE
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.USER_ID
,NULL
,P_ORG_ID
,V_SET_OF_BOOKS_ID
,V_BANK_ACCOUNT_ID
,V_BANK_ACCOUNT_NUM
,V_BANK_ACCOUNT_TYPE
,V_CHECK_STOCK_ID
,V_CHECK_FORMAT_ID
,V_DOC_CATEGORY_CODE
,V_PAYMENT_METHOD_LOOKUP_CODE
,V_PERIOD_NAME
,V_VENDOR_ID
,V_VENDOR_NAME
,V_VENDOR_SITE_ID
,V_COUNTRY
,V_CITY
,V_PROVINCE
,V_STATE
,V_COUNTY
,V_ZIP
,V_ADDRESS_LINE1
,V_ADDRESS_LINE2
,V_ADDRESS_LINE3
,V_ADDRESS_LINE4
,V_INVOICE_ID
FROM DUAL;
END;
PROCEDURE SP_AP_ACCOUNTING_11I10(P_CHECK_ID NUMBER) IS
V_ACCOUNT_EVENT_ID NUMBER(15);
BEGIN
SELECT AP.AP_ACCOUNTING_EVENTS_S.NEXTVAL
INTO V_ACCOUNT_EVENT_ID
FROM DUAL;
UPDATE AP.AP_INVOICE_PAYMENTS_ALL AIP
SET AIP.ACCOUNTING_EVENT_ID = V_ACCOUNT_EVENT_ID
,AIP.ACCRUAL_POSTED_FLAG = 'N'
,AIP.CASH_POSTED_FLAG = 'N'
,AIP.POSTED_FLAG = 'N'
WHERE AIP.CHECK_ID = P_CHECK_ID;
INSERT INTO AP.AP_ACCOUNTING_EVENTS_ALL
(ACCOUNTING_EVENT_ID
,EVENT_TYPE_CODE
,ACCOUNTING_DATE
,EVENT_NUMBER
,EVENT_STATUS_CODE
,SOURCE_TABLE
,SOURCE_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,ORG_ID)
SELECT V_ACCOUNT_EVENT_ID
,'PAYMENT'
,AC.CHECK_DATE
,1
,'CREATED'
,'AP_CHECKS'
,AC.CHECK_ID
,SYSDATE
,-99
,SYSDATE
,-99
,-1
,AC.ORG_ID
FROM AP.AP_CHECKS_ALL AC
WHERE AC.CHECK_ID = P_CHECK_ID;
END SP_AP_ACCOUNTING_11I10;
---WEBADI API程序错误信息抛出程序
PROCEDURE MEW_PUT_ERROR_P(P_ERROR_MSG IN VARCHAR2) IS
BEGIN
FND_MESSAGE.SET_NAME(APPLICATION => 'PER'
,NAME => 'MEW_WEB_ADI_MSG');
FND_MESSAGE.SET_TOKEN(TOKEN => 'ERROR_MSG'
,VALUE => P_ERROR_MSG);
FND_MESSAGE.RAISE_ERROR;
END MEW_PUT_ERROR_P;
PROCEDURE ZJ_11I_BATCH_ACCOUNT(P_ORG_ID IN NUMBER) IS
V_REQUEST_ID NUMBER;
ERRBUF VARCHAR2(1000);
BEGIN
/* BEGIN
V_REQUEST_ID := NULL;
--调用标准提交请求过程，提交创建会计分录的请求
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'SQLAP'
,PROGRAM => 'APACCENG'
,DESCRIPTION => '创建发票会计分录'
,START_TIME => TO_CHAR(SYSDATE
,'dd-mon-yy hh:mi:ss')
,SUB_REQUEST => FALSE
,ARGUMENT1 => NULL
, --Start Date
ARGUMENT2 => NULL
, --End Date
ARGUMENT3 => 'Invoices'
, --Journal Category
ARGUMENT4 => 'N'
, --Submit Transfer to GL
ARGUMENT5 => 'N'
, --Submit Journal Import
ARGUMENT6 => 'Y'
, --Validate Accounts
ARGUMENT7 => 'N'
, --Summarize Report
ARGUMENT8 => TO_CHAR(50)
, --Commit Cycle
ARGUMENT9 => TO_CHAR(P_ORG_ID)
, --org_id
ARGUMENT10 => 'N'
, --Debug
ARGUMENT11 => NULL
, --Invoice Batch ID
ARGUMENT12 => NULL
, --Payment Batch Name
ARGUMENT13 => 'N' --Trace Option
,ARGUMENT14 => 'Y' --Run Accounting Report
);
IF NVL(V_REQUEST_ID
,0) > 0
THEN
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'创建发票会计分录成功！');
END IF;
EXCEPTION
WHEN OTHERS THEN
ERRBUF := '创建发票会计分录失败！';
FND_FILE.PUT_LINE(FND_FILE.LOG
,ERRBUF);
RETURN;
END;*/
BEGIN
V_REQUEST_ID := NULL;
V_REQUEST_ID := FND_REQUEST.SUBMIT_REQUEST(APPLICATION => 'SQLAP'
,PROGRAM => 'APACCENG'
,DESCRIPTION => '创建付款会计分录'
,START_TIME => TO_CHAR(SYSDATE
,'dd-mon-yy hh:mi:ss')
,SUB_REQUEST => FALSE
,ARGUMENT1 => NULL
, --Start Date
ARGUMENT2 => NULL
, --End Date
ARGUMENT3 => 'Payments'
, --Journal Category
ARGUMENT4 => 'N'
, --Submit Transfer to GL
ARGUMENT5 => 'N'
, --Submit Journal Import
ARGUMENT6 => 'Y'
, --Validate Accounts
ARGUMENT7 => 'N'
, --Summarize Report
ARGUMENT8 => TO_CHAR(50)
, --Commit Cycle
ARGUMENT9 => TO_CHAR(P_ORG_ID)
, --org_id
ARGUMENT10 => 'N'
, --Debug
ARGUMENT11 => NULL
, --Invoice Batch ID
ARGUMENT12 => NULL
, --Payment Batch Name
ARGUMENT13 => 'N' --Trace Option
,ARGUMENT14 => 'Y' --Run Accounting Report
);
IF NVL(V_REQUEST_ID
,0) > 0
THEN
COMMIT;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'创建付款会计分录成功！');
END IF;
EXCEPTION
WHEN OTHERS THEN
ERRBUF := '创建付款会计分录失败！';
FND_FILE.PUT_LINE(FND_FILE.LOG
,ERRBUF);
RETURN;
END;
END ZJ_11I_BATCH_ACCOUNT;
--批量付款程序
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) IS
--是否创建日记账分录
CURSOR C1 IS
SELECT DISTINCT MAIP.ORG_ID
,MAIP.SET_OF_BOOKS_ID
,MAIP.VENDOR_ID
,MAIP.VENDOR_NUM
,MAIP.VENDOR_SITE_ID
,MAIP.VENDOR_SITE_CODE
,MAIP.ADDRESS_LINE1
,MAIP.ADDRESS_LINE2
,MAIP.ADDRESS_LINE3
,MAIP.ADDRESS_LINE4
,MAIP.COUNTRY
,MAIP.STATE
,MAIP.ZIP
,MAIP.COUNTY
,MAIP.CITY
,MAIP.PROVINCE
,MAIP.VENDOR_NAME
,MAIP.BANK_ACCOUNT_ID
,MAIP.BANK_ACCOUNT_TYPE
,MAIP.BANK_ACCOUNT_NAME
,MAIP.BANK_ACCOUNT_NUM
,MAIP.CHECK_STOCK_NAME
,MAIP.CHECK_STOCK_ID
,MAIP.CHECK_FORMAT_ID
,MAIP.DOC_CATEGORY_CODE
,MAIP.PAYMENT_METHOD_LOOKUP_CODE
,MAIP.CURRENCY_CODE
,MAIP.ATTRIBUTE1 --汇率类型
,MAIP.ATTRIBUTE2 --汇率
,TRUNC(MAIP.ATTRIBUTE3) EXCHANGE_RATE_DATE --汇率日期
,TRUNC(MAIP.CHECK_DATE) CHECK_DATE
,SUM(MAIP.AMOUNT) AMOUNT
FROM MEW_AP_INVOICE_PAYMENTS MAIP
WHERE MAIP.BATCH_ID = P_BATCH_ID
GROUP BY MAIP.ORG_ID
,MAIP.SET_OF_BOOKS_ID
,MAIP.VENDOR_ID
,MAIP.VENDOR_NUM
,MAIP.VENDOR_SITE_ID
,MAIP.VENDOR_SITE_CODE
,MAIP.ADDRESS_LINE1
,MAIP.ADDRESS_LINE2
,MAIP.ADDRESS_LINE3
,MAIP.ADDRESS_LINE4
,MAIP.COUNTRY
,MAIP.STATE
,MAIP.ZIP
,MAIP.COUNTY
,MAIP.CITY
,MAIP.PROVINCE
,MAIP.VENDOR_NAME
,MAIP.BANK_ACCOUNT_ID
,MAIP.BANK_ACCOUNT_TYPE
,MAIP.BANK_ACCOUNT_NAME
,MAIP.BANK_ACCOUNT_NUM
,MAIP.CHECK_STOCK_NAME
,MAIP.CHECK_STOCK_ID
,MAIP.CHECK_FORMAT_ID
,MAIP.DOC_CATEGORY_CODE
,MAIP.PAYMENT_METHOD_LOOKUP_CODE
,MAIP.CURRENCY_CODE
,MAIP.ATTRIBUTE1 --汇率类型
,MAIP.ATTRIBUTE2 --汇率
,TRUNC(MAIP.ATTRIBUTE3) --汇率日期
,TRUNC(MAIP.CHECK_DATE)
ORDER BY TRUNC(MAIP.CHECK_DATE);
CNT NUMBER := 0;
V_CONCURRENCY_STATUS BOOLEAN;
V_EXCHANGE_RATE_TYPE VARCHAR2(50);
V_SUM_INVOICE_AMOUNT NUMBER;
V_SUB_INVOICE_AMOUNT NUMBER;
BEGIN
SELECT COUNT(*)
INTO CNT
FROM MEW_AP_INVOICE_PAYMENTS MAIP
WHERE MAIP.BATCH_ID = P_BATCH_ID;
IF NVL(CNT
,0) = 0
THEN
V_CONCURRENCY_STATUS := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING'
,'当前批次没有数据需要处理');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'当前批次没有数据需要处理');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'');
ELSE
CNT := 0;
--输出付款记录总数
FND_FILE.PUT_LINE(FND_FILE.LOG
,'本次生成的付款单号明细如下：');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'付款单号' || ' ' || '付款金额');
IF P_PAYMENT_TYPE = 'S'
THEN
FOR R1 IN C1
LOOP
V_SUM_INVOICE_AMOUNT := ROUND(R1.AMOUNT * NVL(R1.ATTRIBUTE2
,1)
,2);
CNT := CNT + 1;
--8 获取付款单ID
SELECT AP_CHECKS_S.NEXTVAL INTO V_CHECK_ID FROM DUAL;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'cnt1:'||cnt);
IF R1.ATTRIBUTE1 IS NOT NULL
THEN
SELECT DCT.CONVERSION_TYPE
INTO V_EXCHANGE_RATE_TYPE
FROM GL_DAILY_CONVERSION_TYPES DCT
WHERE DCT.CONVERSION_TYPE != 'Period Average (Upgrade)'
AND DCT.CONVERSION_TYPE != 'EMU FIXED'
AND DCT.USER_CONVERSION_TYPE = R1.ATTRIBUTE1;
END IF;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'cnt:'||cnt);
FOR R2 IN (SELECT *
FROM MEW_AP_INVOICE_PAYMENTS MAIP
WHERE MAIP.ORG_ID = R1.ORG_ID
AND MAIP.VENDOR_NUM = R1.VENDOR_NUM
AND MAIP.VENDOR_SITE_CODE = R1.VENDOR_SITE_CODE
AND MAIP.CHECK_DATE = R1.CHECK_DATE
AND MAIP.BANK_ACCOUNT_NAME = R1.BANK_ACCOUNT_NAME
AND MAIP.CHECK_STOCK_NAME = R1.CHECK_STOCK_NAME
AND MAIP.CURRENCY_CODE = R1.CURRENCY_CODE
AND (MAIP.ATTRIBUTE1 = R1.ATTRIBUTE1 OR
R1.ATTRIBUTE1 IS NULL)
AND (MAIP.ATTRIBUTE2 = R1.ATTRIBUTE2 OR
R1.ATTRIBUTE2 IS NULL)
AND (TRUNC(MAIP.ATTRIBUTE3) = R1.EXCHANGE_RATE_DATE OR
R1.EXCHANGE_RATE_DATE IS NULL)
ORDER BY MAIP.CHECK_DATE)
LOOP
V_SUB_INVOICE_AMOUNT := ROUND(R2.AMOUNT * NVL(R2.ATTRIBUTE2
,1)
,2);
--9 获取付款ID
SELECT AP_INVOICE_PAYMENTS_S.NEXTVAL
INTO V_INVOICE_PAYMENT_ID
FROM DUAL;
--11 写付款信息至发票付款表
INSERT INTO AP_INVOICE_PAYMENTS_ALL
(ACCOUNTING_DATE
,ACCRUAL_POSTED_FLAG
,AMOUNT
,CASH_POSTED_FLAG
,CHECK_ID
,INVOICE_ID
,INVOICE_PAYMENT_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,PAYMENT_NUM
,PERIOD_NAME
,POSTED_FLAG
,SET_OF_BOOKS_ID
-- ,ACCTS_PAY_CODE_COMBINATION_ID
,CREATED_BY
,CREATION_DATE
,EXCHANGE_DATE --汇率日期
,EXCHANGE_RATE --汇率
,EXCHANGE_RATE_TYPE --汇率类型
,GAIN_CODE_COMBINATION_ID
,LOSS_CODE_COMBINATION_ID
,INVOICE_BASE_AMOUNT
,PAYMENT_BASE_AMOUNT
,FUTURE_PAY_CODE_COMBINATION_ID
,REVERSAL_FLAG)
VALUES
(R2.CHECK_DATE
,'Y'
,R2.AMOUNT
,'Y'
,V_CHECK_ID
,R2.INVOICE_ID
,V_INVOICE_PAYMENT_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,1
,R2.PERIOD_NAME
,'Y'
,R2.SET_OF_BOOKS_ID
-- ,V_ACCTS_CODE_COMBINATION_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,NVL(SYSDATE
,R2.ATTRIBUTE3) --汇率日期
,R2.ATTRIBUTE2 --汇率
,V_EXCHANGE_RATE_TYPE --汇率类型
,NULL
,NULL
,V_SUB_INVOICE_AMOUNT
,V_SUB_INVOICE_AMOUNT
,NULL
,'N');
--12 更新发票数据状态
UPDATE AP_PAYMENT_SCHEDULES_ALL
SET AMOUNT_REMAINING = AMOUNT_REMAINING - R2.AMOUNT
,DISCOUNT_AMOUNT_REMAINING = 0
,PAYMENT_STATUS_FLAG = DECODE(AMOUNT_REMAINING -
R2.AMOUNT
,0
,'Y'
,AMOUNT_REMAINING
,PAYMENT_STATUS_FLAG
,'P')
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
,CHECKRUN_ID = NULL
WHERE INVOICE_ID = R2.INVOICE_ID
AND PAYMENT_NUM = 1;
--13 更新发票表已支付金额
UPDATE AP_INVOICES_ALL
SET AMOUNT_PAID = NVL(AMOUNT_PAID
,0) + R2.AMOUNT
,DISCOUNT_AMOUNT_TAKEN = NVL(DISCOUNT_AMOUNT_TAKEN
,0)
,PAYMENT_STATUS_FLAG = AP_INVOICES_UTILITY_PKG.GET_PAYMENT_STATUS(R2.INVOICE_ID)
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE INVOICE_ID = R2.INVOICE_ID;
END LOOP;
--15 获取凭证编号
SELECT FDS.DOC_SEQUENCE_ID
,FDS.DB_SEQUENCE_NAME
,FDSA.DOC_SEQUENCE_ASSIGNMENT_ID
INTO V_DOC_SEQUENCE_ID
,V_DB_SEQUENCE_NAME
,V_DOC_SEQUENCE_ASSIGNMENT_ID
FROM FND_DOC_SEQUENCE_ASSIGNMENTS FDSA
,FND_DOCUMENT_SEQUENCES FDS
WHERE FDSA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID
AND FDSA.APPLICATION_ID = 200
AND FDSA.SET_OF_BOOKS_ID = R1.SET_OF_BOOKS_ID
AND FDS.TYPE = 'A'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(FDSA.START_DATE
,SYSDATE)) AND
TRUNC(NVL(FDSA.END_DATE
,SYSDATE))
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(FDS.START_DATE
,SYSDATE)) AND
TRUNC(NVL(FDS.END_DATE
,SYSDATE))
AND FDSA.CATEGORY_CODE = R1.DOC_CATEGORY_CODE
AND FDSA.METHOD_CODE = 'M';
--6 获取并更新付款单号
SELECT LAST_DOCUMENT_NUM + 1
INTO V_CHECK_NUMBER
FROM AP_CHECK_STOCKS_ALL ACS
WHERE ACS.CHECK_STOCK_ID = R1.CHECK_STOCK_ID
AND NVL(ACS.INACTIVE_DATE
,SYSDATE) >= SYSDATE;
--输出付款单号
FND_FILE.PUT_LINE(FND_FILE.LOG
,V_CHECK_NUMBER || ' ' || R1.AMOUNT);
UPDATE AP_CHECK_STOCKS_ALL
SET LAST_DOCUMENT_NUM = V_CHECK_NUMBER
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE CHECK_STOCK_ID = R1.CHECK_STOCK_ID;
V_DB_STR := 'select ' || V_DB_SEQUENCE_NAME ||
'.nextval from dual';
-- SELECT FND_DOC_SEQ_411_S.NEXTVAL INTO V_DOC_SEQUENCE_ID FROM DUAL;
EXECUTE IMMEDIATE V_DB_STR
INTO V_DOC_SEQUENCE_VALUE;
INSERT INTO AP_DOC_SEQUENCE_AUDIT
(DOC_SEQUENCE_ID
,DOC_SEQUENCE_ASSIGNMENT_ID
,DOC_SEQUENCE_VALUE
,CREATION_DATE
,CREATED_BY)
VALUES
(V_DOC_SEQUENCE_ID
,V_DOC_SEQUENCE_ASSIGNMENT_ID
,V_DOC_SEQUENCE_VALUE
,SYSDATE
,FND_GLOBAL.USER_ID);
-- COMMIT;
--16 生成付款单记录
AP_CHECKS_PKG.INSERT_ROW(X_ROWID => V_ROWID
,X_AMOUNT => R1.AMOUNT
,X_BANK_ACCOUNT_ID => R1.BANK_ACCOUNT_ID
,X_BANK_ACCOUNT_NAME => R1.BANK_ACCOUNT_NAME
,X_CHECK_DATE => R1.CHECK_DATE
,X_CHECK_ID => V_CHECK_ID
,X_CHECK_NUMBER => V_CHECK_NUMBER
,X_CURRENCY_CODE => R1.CURRENCY_CODE
,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,X_LAST_UPDATE_DATE => SYSDATE
,X_PAYMENT_METHOD_LOOKUP_CODE => R1.PAYMENT_METHOD_LOOKUP_CODE
,X_PAYMENT_TYPE_FLAG => 'M'
,X_ADDRESS_LINE1 => R1.ADDRESS_LINE1
,X_ADDRESS_LINE2 => R1.ADDRESS_LINE2
,X_ADDRESS_LINE3 => R1.ADDRESS_LINE3
,X_CHECKRUN_NAME => NULL
,X_CHECK_FORMAT_ID => R1.CHECK_FORMAT_ID
,X_CHECK_STOCK_ID => R1.CHECK_STOCK_ID
,X_CITY => R1.CITY
,X_COUNTRY => R1.COUNTRY
,X_CREATED_BY => FND_GLOBAL.USER_ID
,X_CREATION_DATE => SYSDATE
,X_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,X_STATUS_LOOKUP_CODE => 'NEGOTIABLE'
,X_VENDOR_NAME => R1.VENDOR_NAME
,X_VENDOR_SITE_CODE => R1.VENDOR_SITE_CODE
,X_EXTERNAL_BANK_ACCOUNT_ID => NULL
,X_ZIP => NULL
,X_BANK_ACCOUNT_NUM => R1.BANK_ACCOUNT_NUM
,X_BANK_ACCOUNT_TYPE => R1.BANK_ACCOUNT_TYPE
,X_BANK_NUM => NULL
,X_CHECK_VOUCHER_NUM => NULL
,X_CLEARED_AMOUNT => NULL
,X_CLEARED_DATE => NULL
,X_DOC_CATEGORY_CODE => R1.DOC_CATEGORY_CODE
,X_DOC_SEQUENCE_ID => V_DOC_SEQUENCE_ID
,X_DOC_SEQUENCE_VALUE => V_DOC_SEQUENCE_VALUE
,X_PROVINCE => R1.PROVINCE
,X_RELEASED_DATE => NULL
,X_RELEASED_BY => NULL
,X_STATE => R1.STATE
,X_ATTRIBUTE15 => NULL
,X_FUTURE_PAY_DUE_DATE => NULL
,X_ADDRESS_LINE4 => R1.ADDRESS_LINE4
,X_COUNTY => R1.COUNTY
,X_ADDRESS_STYLE => NULL
,X_ORG_ID => R1.ORG_ID
,X_VENDOR_ID => R1.VENDOR_ID
,X_VENDOR_SITE_ID => R1.VENDOR_SITE_ID
,X_EXCHANGE_RATE => R1.ATTRIBUTE2
,X_EXCHANGE_DATE => NVL(R1.EXCHANGE_RATE_DATE
,SYSDATE)
,X_EXCHANGE_RATE_TYPE => V_EXCHANGE_RATE_TYPE
,X_BASE_AMOUNT => V_SUM_INVOICE_AMOUNT
,X_CALLING_SEQUENCE => 'APXPAWKB'
,X_DESCRIPTION => NULL);
-- COMMIT;
--17 创建付款会计分录
SP_AP_ACCOUNTING_11I10(V_CHECK_ID);
END LOOP;
ELSE
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'cnt:'||cnt);
FOR R2 IN (SELECT *
FROM MEW_AP_INVOICE_PAYMENTS MAIP
WHERE MAIP.BATCH_ID = P_BATCH_ID
ORDER BY MAIP.CHECK_DATE)
LOOP
CNT := CNT + 1;
--8 获取付款单ID
SELECT AP_CHECKS_S.NEXTVAL INTO V_CHECK_ID FROM DUAL;
-- FND_FILE.PUT_LINE(FND_FILE.LOG,'cnt1:'||cnt);
IF R2.ATTRIBUTE1 IS NOT NULL
THEN
SELECT DCT.CONVERSION_TYPE
INTO V_EXCHANGE_RATE_TYPE
FROM GL_DAILY_CONVERSION_TYPES DCT
WHERE DCT.CONVERSION_TYPE != 'Period Average (Upgrade)'
AND DCT.CONVERSION_TYPE != 'EMU FIXED'
AND DCT.USER_CONVERSION_TYPE = R2.ATTRIBUTE1;
END IF;
V_SUB_INVOICE_AMOUNT := R2.AMOUNT * NVL(R2.ATTRIBUTE2
,1);
--9 获取付款ID
SELECT AP_INVOICE_PAYMENTS_S.NEXTVAL
INTO V_INVOICE_PAYMENT_ID
FROM DUAL;
--11 写付款信息至发票付款表
INSERT INTO AP_INVOICE_PAYMENTS_ALL
(ACCOUNTING_DATE
,ACCRUAL_POSTED_FLAG
,AMOUNT
,CASH_POSTED_FLAG
,CHECK_ID
,INVOICE_ID
,INVOICE_PAYMENT_ID
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,PAYMENT_NUM
,PERIOD_NAME
,POSTED_FLAG
,SET_OF_BOOKS_ID
-- ,ACCTS_PAY_CODE_COMBINATION_ID
,CREATED_BY
,CREATION_DATE
,EXCHANGE_DATE --汇率日期
,EXCHANGE_RATE --汇率
,EXCHANGE_RATE_TYPE --汇率类型
,GAIN_CODE_COMBINATION_ID
,LOSS_CODE_COMBINATION_ID
,INVOICE_BASE_AMOUNT
,PAYMENT_BASE_AMOUNT
,FUTURE_PAY_CODE_COMBINATION_ID
,REVERSAL_FLAG)
VALUES
(R2.CHECK_DATE
,'Y'
,R2.AMOUNT
,'Y'
,V_CHECK_ID
,R2.INVOICE_ID
,V_INVOICE_PAYMENT_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,1
,R2.PERIOD_NAME
,'Y'
,R2.SET_OF_BOOKS_ID
-- ,V_ACCTS_CODE_COMBINATION_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,NVL(SYSDATE
,R2.ATTRIBUTE3) --汇率日期
,R2.ATTRIBUTE2 --汇率
,V_EXCHANGE_RATE_TYPE --汇率类型
,NULL
,NULL
,V_SUB_INVOICE_AMOUNT
,V_SUB_INVOICE_AMOUNT
,NULL
,'N');
--12 更新发票数据状态
UPDATE AP_PAYMENT_SCHEDULES_ALL
SET AMOUNT_REMAINING = AMOUNT_REMAINING - R2.AMOUNT
,DISCOUNT_AMOUNT_REMAINING = 0
,PAYMENT_STATUS_FLAG = DECODE(AMOUNT_REMAINING -
R2.AMOUNT
,0
,'Y'
,AMOUNT_REMAINING
,PAYMENT_STATUS_FLAG
,'P')
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
,CHECKRUN_ID = NULL
WHERE INVOICE_ID = R2.INVOICE_ID
AND PAYMENT_NUM = 1;
--13 更新发票表已支付金额
UPDATE AP_INVOICES_ALL
SET AMOUNT_PAID = NVL(AMOUNT_PAID
,0) + R2.AMOUNT
,DISCOUNT_AMOUNT_TAKEN = NVL(DISCOUNT_AMOUNT_TAKEN
,0)
,PAYMENT_STATUS_FLAG = AP_INVOICES_UTILITY_PKG.GET_PAYMENT_STATUS(R2.INVOICE_ID)
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE INVOICE_ID = R2.INVOICE_ID;
--15 获取凭证编号
SELECT FDS.DOC_SEQUENCE_ID
,FDS.DB_SEQUENCE_NAME
,FDSA.DOC_SEQUENCE_ASSIGNMENT_ID
INTO V_DOC_SEQUENCE_ID
,V_DB_SEQUENCE_NAME
,V_DOC_SEQUENCE_ASSIGNMENT_ID
FROM FND_DOC_SEQUENCE_ASSIGNMENTS FDSA
,FND_DOCUMENT_SEQUENCES FDS
WHERE FDSA.DOC_SEQUENCE_ID = FDS.DOC_SEQUENCE_ID
AND FDSA.APPLICATION_ID = 200
AND FDSA.SET_OF_BOOKS_ID = R2.SET_OF_BOOKS_ID
AND FDS.TYPE = 'A'
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(FDSA.START_DATE
,SYSDATE)) AND
TRUNC(NVL(FDSA.END_DATE
,SYSDATE))
AND TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(FDS.START_DATE
,SYSDATE)) AND
TRUNC(NVL(FDS.END_DATE
,SYSDATE))
AND FDSA.CATEGORY_CODE = R2.DOC_CATEGORY_CODE
AND FDSA.METHOD_CODE = 'M';
--6 获取并更新付款单号
SELECT LAST_DOCUMENT_NUM + 1
INTO V_CHECK_NUMBER
FROM AP_CHECK_STOCKS_ALL ACS
WHERE ACS.CHECK_STOCK_ID = R2.CHECK_STOCK_ID
AND NVL(ACS.INACTIVE_DATE
,SYSDATE) >= SYSDATE;
--输出付款单号
FND_FILE.PUT_LINE(FND_FILE.LOG
,V_CHECK_NUMBER || ' ');
UPDATE AP_CHECK_STOCKS_ALL
SET LAST_DOCUMENT_NUM = V_CHECK_NUMBER
,LAST_UPDATE_DATE = SYSDATE
,LAST_UPDATED_BY = FND_GLOBAL.USER_ID
WHERE CHECK_STOCK_ID = R2.CHECK_STOCK_ID;
V_DB_STR := 'select ' || V_DB_SEQUENCE_NAME ||
'.nextval from dual';
-- SELECT FND_DOC_SEQ_411_S.NEXTVAL INTO V_DOC_SEQUENCE_ID FROM DUAL;
EXECUTE IMMEDIATE V_DB_STR
INTO V_DOC_SEQUENCE_VALUE;
INSERT INTO AP_DOC_SEQUENCE_AUDIT
(DOC_SEQUENCE_ID
,DOC_SEQUENCE_ASSIGNMENT_ID
,DOC_SEQUENCE_VALUE
,CREATION_DATE
,CREATED_BY)
VALUES
(V_DOC_SEQUENCE_ID
,V_DOC_SEQUENCE_ASSIGNMENT_ID
,V_DOC_SEQUENCE_VALUE
,SYSDATE
,FND_GLOBAL.USER_ID);
-- COMMIT;
--16 生成付款单记录
AP_CHECKS_PKG.INSERT_ROW(X_ROWID => V_ROWID
,X_AMOUNT => R2.AMOUNT
,X_BANK_ACCOUNT_ID => R2.BANK_ACCOUNT_ID
,X_BANK_ACCOUNT_NAME => R2.BANK_ACCOUNT_NAME
,X_CHECK_DATE => R2.CHECK_DATE
,X_CHECK_ID => V_CHECK_ID
,X_CHECK_NUMBER => V_CHECK_NUMBER
,X_CURRENCY_CODE => R2.CURRENCY_CODE
,X_LAST_UPDATED_BY => FND_GLOBAL.USER_ID
,X_LAST_UPDATE_DATE => SYSDATE
,X_PAYMENT_METHOD_LOOKUP_CODE => R2.PAYMENT_METHOD_LOOKUP_CODE
,X_PAYMENT_TYPE_FLAG => 'M'
,X_ADDRESS_LINE1 => R2.ADDRESS_LINE1
,X_ADDRESS_LINE2 => R2.ADDRESS_LINE2
,X_ADDRESS_LINE3 => R2.ADDRESS_LINE3
,X_CHECKRUN_NAME => NULL
,X_CHECK_FORMAT_ID => R2.CHECK_FORMAT_ID
,X_CHECK_STOCK_ID => R2.CHECK_STOCK_ID
,X_CITY => R2.CITY
,X_COUNTRY => R2.COUNTRY
,X_CREATED_BY => FND_GLOBAL.USER_ID
,X_CREATION_DATE => SYSDATE
,X_LAST_UPDATE_LOGIN => FND_GLOBAL.USER_ID
,X_STATUS_LOOKUP_CODE => 'NEGOTIABLE'
,X_VENDOR_NAME => R2.VENDOR_NAME
,X_VENDOR_SITE_CODE => R2.VENDOR_SITE_CODE
,X_EXTERNAL_BANK_ACCOUNT_ID => NULL
,X_ZIP => NULL
,X_BANK_ACCOUNT_NUM => R2.BANK_ACCOUNT_NUM
,X_BANK_ACCOUNT_TYPE => R2.BANK_ACCOUNT_TYPE
,X_BANK_NUM => NULL
,X_CHECK_VOUCHER_NUM => NULL
,X_CLEARED_AMOUNT => NULL
,X_CLEARED_DATE => NULL
,X_DOC_CATEGORY_CODE => R2.DOC_CATEGORY_CODE
,X_DOC_SEQUENCE_ID => V_DOC_SEQUENCE_ID
,X_DOC_SEQUENCE_VALUE => V_DOC_SEQUENCE_VALUE
,X_PROVINCE => R2.PROVINCE
,X_RELEASED_DATE => NULL
,X_RELEASED_BY => NULL
,X_STATE => R2.STATE
,X_ATTRIBUTE15 => NULL
,X_FUTURE_PAY_DUE_DATE => NULL
,X_ADDRESS_LINE4 => R2.ADDRESS_LINE4
,X_COUNTY => R2.COUNTY
,X_ADDRESS_STYLE => NULL
,X_ORG_ID => R2.ORG_ID
,X_VENDOR_ID => R2.VENDOR_ID
,X_VENDOR_SITE_ID => R2.VENDOR_SITE_ID
,X_EXCHANGE_RATE => R2.ATTRIBUTE2
,X_EXCHANGE_DATE => NVL(R2.ATTRIBUTE3
,SYSDATE)
,X_EXCHANGE_RATE_TYPE => V_EXCHANGE_RATE_TYPE
,X_BASE_AMOUNT => V_SUM_INVOICE_AMOUNT
,X_CALLING_SEQUENCE => 'APXPAWKB'
,X_DESCRIPTION => NULL);
-- COMMIT;
--17 创建付款会计分录
SP_AP_ACCOUNTING_11I10(V_CHECK_ID);
END LOOP;
END IF;
BEGIN
SELECT DISTINCT ORG_ID
INTO V_ORG_ID
FROM MEW_AP_INVOICE_PAYMENTS T
WHERE T.BATCH_ID = P_BATCH_ID;
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG
,'本批次数据组织ID不唯一');
APP_EXCEPTION.RAISE_EXCEPTION;
END;
FND_FILE.PUT_LINE(FND_FILE.LOG
,'');
--输出付款记录总数
FND_FILE.PUT_LINE(FND_FILE.LOG
,'本次共生成：' || CNT || ' 笔付款.');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'');
FND_FILE.PUT_LINE(FND_FILE.LOG
,'');
IF P_CREATE_JOURNAL_ENTRY_FLAG = 'Y'
THEN
ZJ_11I_BATCH_ACCOUNT(V_ORG_ID);
END IF;
DELETE FROM MEW_AP_INVOICE_PAYMENTS MAIP
WHERE MAIP.BATCH_ID = P_BATCH_ID;
END IF;
END;
END;
/
扫一扫
922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
