AP AR GL FA INV事务处理报表

 

CREATE OR REPLACE PACKAGE MEW_BATCH_IMPINVOICES_PKG IS

  PROCEDURE MAIN(P_BATCH_ID                  NUMBER,
                 P_ORG_ID                    NUMBER,
                 P_INVOICE_TYPE              VARCHAR2,
                 P_VENDOR_NUMBER             VARCHAR2,
                 P_VENDOR_SITE_CODE          VARCHAR2,
                 P_INVOICE_DATE              DATE,
                 P_INVOICE_NUMBER            VARCHAR2,
                 P_INVOICE_CURRENCY_CODE     VARCHAR2,
                 P_INVOICE_AMOUNT            VARCHAR2,
                 P_GL_DATE                   DATE,
                 P_EXCHANGE_RATE_TYPE        VARCHAR2 DEFAULT NULL,
                 P_EXCHANGE_RATE_DATE        DATE DEFAULT NULL,
                 P_EXCHANGE_RATE             NUMBER DEFAULT NULL,
                 P_INVOICE_DESCRIPTION       VARCHAR2 DEFAULT NULL,
                 P_TERMS                     VARCHAR2,
                 P_TERMS_DATE                DATE,
                 P_LINE_TYPE                 VARCHAR2,
                 P_DEBIT_ACCOUNT_AMOUNT      NUMBER,
                 P_DISTRIBUTION_ACCOUNT_CODE VARCHAR2,
                 P_LINE_DESCRIPTION          VARCHAR2 DEFAULT NULL);

END;
/
CREATE OR REPLACE PACKAGE BODY MEW_BATCH_IMPINVOICES_PKG IS
  /*===============================================
    copyright (c) sie business consulting services
                allrights reserved
  *================================================
  *================================================
  *   program name:
  *                MEW_BATCH_IMPINVOICES_PKG
  *   description:
  *                发票批量导入
  *   history:
  *   1.00   2011-01-06   sie_chenyueyong  creation
  *===============================================*/
  PROCEDURE MAIN(P_BATCH_ID                  NUMBER,
                 P_ORG_ID                    NUMBER,
                 P_INVOICE_TYPE              VARCHAR2,
                 P_VENDOR_NUMBER             VARCHAR2,
                 P_VENDOR_SITE_CODE          VARCHAR2,
                 P_INVOICE_DATE              DATE,
                 P_INVOICE_NUMBER            VARCHAR2,
                 P_INVOICE_CURRENCY_CODE     VARCHAR2,
                 P_INVOICE_AMOUNT            VARCHAR2,
                 P_GL_DATE                   DATE,
                 P_EXCHANGE_RATE_TYPE        VARCHAR2 DEFAULT NULL,
                 P_EXCHANGE_RATE_DATE        DATE DEFAULT NULL,
                 P_EXCHANGE_RATE             NUMBER DEFAULT NULL,
                 P_INVOICE_DESCRIPTION       VARCHAR2 DEFAULT NULL,
                 P_TERMS                     VARCHAR2,
                 P_TERMS_DATE                DATE,
                 P_LINE_TYPE                 VARCHAR2,
                 P_DEBIT_ACCOUNT_AMOUNT      NUMBER,
                 P_DISTRIBUTION_ACCOUNT_CODE VARCHAR2,
                 P_LINE_DESCRIPTION          VARCHAR2 DEFAULT NULL) IS
    V_ORG_ID                   NUMBER;
    V_INVOICE_TYPE             VARCHAR2(50);
    V_VENDOR_ID                NUMBER;
    V_EXCHANGE_RATE            NUMBER;
    V_TERM_ID                  NUMBER;
    V_LINE_TYPE                VARCHAR2(50);
    V_VENDOR_SITE_ID           NUMBER;
    V_CODE_COMBINATION_ID      NUMBER;
    V_AP_INVOICES_INTERFACE_ID NUMBER;
    V_PERIOD_NAME              VARCHAR2(20);
    V_SET_OF_BOOKS_ID          NUMBER;
  BEGIN
 
    BEGIN
      SELECT ORGANIZATION_ID
        INTO V_ORG_ID
        FROM HR_OPERATING_UNITS HOU
       WHERE HOU.ORGANIZATION_ID = P_ORG_ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('ORG ID不存在');
    END;
 
    SELECT HOU.SET_OF_BOOKS_ID
      INTO V_SET_OF_BOOKS_ID
      FROM HR_OPERATING_UNITS HOU
     WHERE HOU.ORGANIZATION_ID = V_ORG_ID;
 
    /*    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_INVOICE_DATE) BETWEEN GPS.START_DATE AND GPS.END_DATE;
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          APP_EXCEPTION.RAISE_EXCEPTION('AP会计期未打开' || P_INVOICE_DATE);
      END;
    */
    BEGIN
      SELECT ALT.LOOKUP_CODE
     
        INTO V_INVOICE_TYPE
     
        FROM AP_LC_INVOICE_TYPES_V ALT
       WHERE ALT.LOOKUP_CODE = UPPER(P_INVOICE_TYPE);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('INVOICE TYPE不存在');
    END;
 
    BEGIN
      SELECT VENDOR_ID
        INTO V_VENDOR_ID
        FROM PO_VENDORS PV
       WHERE PV.SEGMENT1 = P_VENDOR_NUMBER;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('VENDOR NUMBER不存在');
    END;
 
    BEGIN
      SELECT VENDOR_SITE_ID
        INTO V_VENDOR_SITE_ID
        FROM PO_VENDOR_SITES_ALL PVA
       WHERE PVA.VENDOR_SITE_CODE = P_VENDOR_SITE_CODE
         AND PVA.ORG_ID = V_ORG_ID
         AND PVA.VENDOR_ID = V_VENDOR_ID;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('VENDOR SITE CODE不存在');
    END;
 
    IF P_INVOICE_CURRENCY_CODE <> 'CNY' THEN
      IF P_EXCHANGE_RATE_DATE IS NULL THEN
        APP_EXCEPTION.RAISE_EXCEPTION('EXCHANGE RATE DATE不能为空');
      ELSE
        IF P_EXCHANGE_RATE_TYPE IS NULL THEN
          APP_EXCEPTION.RAISE_EXCEPTION('EXCHANGE RATE TYPE 不能为空');
        ELSE
          IF P_EXCHANGE_RATE_TYPE = 'User' THEN
            IF P_EXCHANGE_RATE IS NULL THEN
              APP_EXCEPTION.RAISE_EXCEPTION('EXCHANGE RATE不能为空');
            END IF;
            V_EXCHANGE_RATE := P_EXCHANGE_RATE;
          ELSE
            V_EXCHANGE_RATE := NULL;
            /*      BEGIN
              SELECT GDV.SHOW_CONVERSION_RATE
                INTO V_EXCHANGE_RATE
                FROM GL_DAILY_RATES_V GDV
               WHERE GDV.FROM_CURRENCY = P_INVOICE_CURRENCY_CODE
                 AND GDV.TO_CURRENCY = 'CNY'
                 AND GDV.CONVERSION_DATE = TRUNC(P_EXCHANGE_RATE_DATE)
                 AND GDV.USER_CONVERSION_TYPE = P_EXCHANGE_RATE_TYPE;
            EXCEPTION
              WHEN NO_DATA_FOUND THEN
                APP_EXCEPTION.RAISE_EXCEPTION('汇率类型或汇率不存在');
            END;*/
          END IF;
        END IF;
      END IF;
    END IF;
 
    BEGIN
      SELECT TERM_ID
        INTO V_TERM_ID
        FROM AP_TERMS_BAT_PAY_TERMS_V ATT
       WHERE ATT.TERMS = P_TERMS;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('TERMS不存在');
    END;
 
    BEGIN
      SELECT LOOKUP_CODE
        INTO V_LINE_TYPE
        FROM AP_LOOKUP_CODES ALC
       WHERE LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE'
         AND LOOKUP_CODE NOT IN ('PREPAY', 'IPI', 'ICMS')
         AND ALC.LOOKUP_CODE = UPPER(P_LINE_TYPE);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('LINE TYPE不存在');
     
    END;
 
    BEGIN
      SELECT CODE_COMBINATION_ID
        INTO V_CODE_COMBINATION_ID
        FROM GL_CODE_COMBINATIONS_KFV GC
       WHERE GC.CONCATENATED_SEGMENTS = P_DISTRIBUTION_ACCOUNT_CODE;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        APP_EXCEPTION.RAISE_EXCEPTION('ACCOUNT CODE不存在');
    END;
 
    BEGIN
      --同一供应商发票编号不能重复（主键为org_id/invoice_number/vendor_id）
      SELECT AII.INVOICE_ID
        INTO V_AP_INVOICES_INTERFACE_ID
        FROM AP_INVOICES_INTERFACE AII
       WHERE AII.GROUP_ID = P_BATCH_ID
         AND AII.INVOICE_NUM = P_INVOICE_NUMBER
         AND AII.VENDOR_ID = V_VENDOR_ID
         and aii.status is null
      --AND AII.VENDOR_SITE_ID = V_VENDOR_SITE_ID
      --AND aii.invoice_type_lookup_code = V_INVOICE_TYPE
      ;
   
      INSERT INTO AP_INVOICE_LINES_INTERFACE
        (INVOICE_ID
         -- ,INVOICE_LINE_ID
         -- ,LINE_NUMBER
        ,
         LINE_TYPE_LOOKUP_CODE,
         AMOUNT,
         ACCOUNTING_DATE,
         DESCRIPTION,
         DIST_CODE_COMBINATION_ID,
         ORG_ID,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         CREATED_BY,
         CREATION_DATE)
      VALUES
        (V_AP_INVOICES_INTERFACE_ID
         --  ,NULL
         -- ,V_LINE_NUMBER
        ,
         V_LINE_TYPE,
         P_DEBIT_ACCOUNT_AMOUNT,
         P_INVOICE_DATE,
         P_LINE_DESCRIPTION,
         V_CODE_COMBINATION_ID,
         P_ORG_ID,
         FND_GLOBAL.USER_ID,
         SYSDATE,
         FND_GLOBAL.USER_ID,
         SYSDATE);
   
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        ---Get invoice_id
        SELECT AP_INVOICES_INTERFACE_S.NEXTVAL
          INTO V_AP_INVOICES_INTERFACE_ID
          FROM SYS.DUAL;
     
        INSERT INTO AP_INVOICES_INTERFACE
          (INVOICE_ID,
           INVOICE_NUM,
           INVOICE_TYPE_LOOKUP_CODE,
           INVOICE_DATE,
           VENDOR_ID,
           VENDOR_SITE_ID,
           INVOICE_AMOUNT,
           INVOICE_CURRENCY_CODE,
           EXCHANGE_RATE,
           EXCHANGE_RATE_TYPE,
           EXCHANGE_DATE,
           TERMS_ID,
           TERMS_DATE,
           DESCRIPTION,
           PAYMENT_METHOD_LOOKUP_CODE,
           GL_DATE,
           ORG_ID,
           LAST_UPDATE_DATE,
           LAST_UPDATED_BY,
           CREATION_DATE,
           CREATED_BY,
           GROUP_ID,
           SOURCE)
        VALUES
          (V_AP_INVOICES_INTERFACE_ID,
           P_INVOICE_NUMBER --发票编号
          ,
           V_INVOICE_TYPE --发票类型
          ,
           P_INVOICE_DATE --发票日期
          ,
           V_VENDOR_ID --供应商ID
          ,
           V_VENDOR_SITE_ID --供应商地点ID
          ,
           P_INVOICE_AMOUNT --发票金额
          ,
           P_INVOICE_CURRENCY_CODE --发票币别
          ,
           V_EXCHANGE_RATE --发票汇率
          ,
           P_EXCHANGE_RATE_TYPE --汇率类型
          ,
           P_EXCHANGE_RATE_DATE --汇率日期
          ,
           V_TERM_ID --付款条件
          ,
           P_TERMS_DATE --付款条件日期
          ,
           P_INVOICE_DESCRIPTION --备注      
          ,
           'CHECK' --付款方法      
          ,
           P_GL_DATE --gl date
          ,
           P_ORG_ID,
           SYSDATE,
           FND_GLOBAL.USER_ID,
           SYSDATE,
           FND_GLOBAL.USER_ID,
           TO_CHAR(P_BATCH_ID),
           'MEW_PACKAGE_IMPORT');
     
        INSERT INTO AP_INVOICE_LINES_INTERFACE
          (INVOICE_ID
           -- ,INVOICE_LINE_ID
           -- ,LINE_NUMBER
          ,
           LINE_TYPE_LOOKUP_CODE,
           AMOUNT,
           ACCOUNTING_DATE,
           DESCRIPTION,
           DIST_CODE_COMBINATION_ID,
           ORG_ID,
           LAST_UPDATED_BY,
           LAST_UPDATE_DATE,
           CREATED_BY,
           CREATION_DATE)
        VALUES
          (V_AP_INVOICES_INTERFACE_ID
           --  ,NULL
           -- ,V_LINE_NUMBER
          ,
           V_LINE_TYPE,
           P_DEBIT_ACCOUNT_AMOUNT,
           P_GL_DATE,
           P_LINE_DESCRIPTION,
           V_CODE_COMBINATION_ID,
           P_ORG_ID,
           FND_GLOBAL.USER_ID,
           SYSDATE,
           FND_GLOBAL.USER_ID,
           SYSDATE);
    END;
 
  END;

END;
/