AP发票批量付款程序（WEB ADI）

 

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