接口表开发AR

来源：互联网发布：oracle数据库巡检模板编辑：程序博客网时间：2024/05/29 09:01

/* Formatted on 2014-9-25 17:51:37 (QP5 v5.115.810.9015) */
DECLARE
   V_LINES_REC    RA_INTERFACE_LINES_ALL%ROWTYPE;
   V_SALESCREDITS_REC RA_INTERFACE_SALESCREDITS_ALL%ROWTYPE;
   V_INTERFACE_LINE_ID NUMBER;
   V_PRIMARY_SALESREP_ID NUMBER;
   V_SET_OF_BOOKS_ID NUMBER;
   V_CURRENCY_CODE VARCHAR2(30);
   V_SOB_CURRENCY_CODE VARCHAR2(30);
   V_PAYMENT_TERM_ID NUMBER := NULL;
   V_CUST_TRX_TYPE_ID NUMBER;
   V_INVOICE_TO_ORG_ID NUMBER;
   V_SHIP_TO_ORG_ID NUMBER;
   -----------------------------
   V_BILL_CUSTOMER_ID NUMBER;
   V_BILL_ADDRESS_ID NUMBER;
   V_SHIP_CUSTOMER_ID NUMBER;
   V_SHIP_ADDRESS_ID NUMBER;
   V_TRANSACTION_TYPE_ID NUMBER;                                    --订单类型
   V_DELIVERY_NAME VARCHAR2(100) := '644241';
   P_ORG_ID NUMBER :=188 ;
   P_CUSTOMER_ID NUMBER := 1618336;
   P_SHIP_HEADER_ID NUMBER :=41220 ;
   P_SEND_NUM VARCHAR2(200):='FSQ11110010' ; --客制化的发货单
   P_ORDER_HEADER_ID NUMBER:= 615534;
   P_ORDER_HEADER_NUMBER VARCHAR2(240):='931211110003' ;
   P_DELIVERY_ID NUMBER := 644241 ;
   P_ACTUAL_DEPARTURE_DATE DATE := SYSDATE ;
   P_AMOUNT NUMBER := 2000;
   P_TYPE NUMBER :=1 ;--发票类型 1 代表运保费，2 代表其它应收 --交货
   L_PROCESS_PHASE NUMBER ;
      ---全局参数
   V_USER_ID NUMBER := FND_GLOBAL.USER_ID;
   --User ID, Sysadmin here
   V_LOGIN_ID NUMBER := FND_GLOBAL.LOGIN_ID;
   x_retcode NUMBER ;
   x_errbuf VARCHAR2(2000);
BEGIN

     mo_global.set_policy_context('S',P_ORG_ID);
     V_primary_salesrep_id:=-3;
          --查看交货名称
      BEGIN
         SELECT NAME
         INTO V_DELIVERY_NAME
         FROM WSH_NEW_DELIVERIES
         WHERE DELIVERY_ID=P_DELIVERY_ID;
      EXCEPTION
         WHEN OTHERS THEN
            x_retcode:=2;
            x_errbuf:='查找交货名称失败';
            DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
            RETURN;
      END;

      --查找帐本ID
      BEGIN
         SELECT SET_OF_BOOKS_ID
         INTO V_SET_OF_BOOKS_ID
         FROM hr_operating_units HOU
         WHERE HOU.ORGANIZATION_ID=P_ORG_ID;
      EXCEPTION
         WHEN OTHERS THEN
            x_retcode:=2;
            x_errbuf:='查找帐本ID时失败';
            DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
            RETURN;
      END;

      L_PROCESS_PHASE:=1;

      --查找本位币
      BEGIN
         SELECT CURRENCY_CODE
         INTO V_SOB_CURRENCY_CODE
         FROM GL_LEDGERS
         WHERE LEDGER_ID=V_SET_OF_BOOKS_ID;
      EXCEPTION
         WHEN OTHERS THEN
            x_retcode:=2;
            x_errbuf:='查找本位币时失败';
             DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
            RETURN;
      END;

      L_PROCESS_PHASE:=2;

      --查找订单币种
      BEGIN
         SELECT TRANSACTIONAL_CURR_CODE,PAYMENT_TERM_ID,INVOICE_TO_ORG_ID,SHIP_TO_ORG_ID,ORDER_TYPE_ID
         INTO V_CURRENCY_CODE,V_PAYMENT_TERM_ID,V_INVOICE_TO_ORG_ID,V_SHIP_TO_ORG_ID,V_TRANSACTION_TYPE_ID
         FROM OE_ORDER_HEADERS_ALL
         WHERE HEADER_ID=P_ORDER_HEADER_ID;
      EXCEPTION
         WHEN OTHERS THEN
            x_retcode:=2;
            x_errbuf:='查找订单时失败';
             DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
            RETURN;
      END;

      L_PROCESS_PHASE:=3;

      IF V_INVOICE_TO_ORG_ID IS NULL THEN
         x_retcode:=2;
         x_errbuf:='查找收单地点为空';
          DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
         RETURN;
      END IF;

      IF V_SHIP_TO_ORG_ID IS NULL THEN
         x_retcode:=2;
         x_errbuf:='查找收货地点为空';
          DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
         RETURN;
      END IF;

      L_PROCESS_PHASE:=4;

      --查找交易类型
      IF P_TYPE=1 THEN
         --运保费的，事务处理类型见配置
         L_PROCESS_PHASE:=4.1;

         BEGIN
            SELECT CUST_TRX_TYPE_ID
            INTO V_CUST_TRX_TYPE_ID
            FROM RA_CUST_TRX_TYPES
            WHERE NAME= ----更改快速编码，运费发票的事务处理类型的抓取逻辑：OU_ID&交易类型 by sam.t 2012.03.31
               (SELECT XYG_PUB_COMMON_PKG.GET_CHAR_POSITION_CONTENT(MEANING,'&',2)--MEANING
               FROM FND_LOOKUP_VALUES_VL
               WHERE LOOKUP_TYPE='XYG_FREIGHT_TRANSACTION_TYPE'
               AND   LOOKUP_CODE=P_ORG_ID
               AND   ENABLED_FLAG='Y'
               AND   SYSDATE BETWEEN START_DATE_ACTIVE AND NVL(END_DATE_ACTIVE+0.99999,SYSDATE+1)
               AND   ROWNUM=1)
            AND ORG_ID=P_ORG_ID;
         EXCEPTION
            WHEN OTHERS THEN
               x_retcode:=2;
               x_errbuf:='查找交易类型时失败';
                DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
               RETURN;
         END;
      ELSIF P_TYPE=2 THEN
         --其它应收的，事务处理类型同原票的事务类型
         L_PROCESS_PHASE:=4.2;

         BEGIN
            SELECT CUST_TRX_TYPE_ID
            INTO V_CUST_TRX_TYPE_ID
            FROM OE_TRANSACTION_TYPES_ALL
            WHERE TRANSACTION_TYPE_ID=V_TRANSACTION_TYPE_ID;
         EXCEPTION
            WHEN OTHERS THEN
               x_retcode:=2;
               x_errbuf:='查找交易类型时失败';
                DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
               RETURN;
         END;
      END IF;

      L_PROCESS_PHASE:=4.3;

      IF V_CUST_TRX_TYPE_ID IS NULL THEN
         x_retcode:=2;
         x_errbuf:='查找交易类型为空';
          DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
         RETURN;
      END IF;

      L_PROCESS_PHASE:=5;

      --查找BILL_ADDRESS_ID
      BEGIN
         SELECT SITE.CUST_ACCOUNT_ID
               ,SITE.CUST_ACCT_SITE_ID
         INTO   V_BILL_CUSTOMER_ID
               ,V_BILL_ADDRESS_ID
         FROM   HZ_CUST_SITE_USES_ALL USES
               ,HZ_CUST_ACCT_SITES_ALL SITE
         WHERE USES.SITE_USE_ID=V_INVOICE_TO_ORG_ID
         AND   SITE.CUST_ACCT_SITE_ID=USES.CUST_ACCT_SITE_ID;
      EXCEPTION
         WHEN OTHERS THEN
            x_retcode:=2;
            x_errbuf:='查找收单地点失败';
             DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
            RETURN;
      END;

      L_PROCESS_PHASE:=6;

      IF V_BILL_CUSTOMER_ID IS NULL OR V_BILL_ADDRESS_ID IS NULL THEN
         x_retcode:=2;
         x_errbuf:='查找收单方失败';
          DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
         RETURN;
      END IF;

      L_PROCESS_PHASE:=7;

      --查找SHIP_TO_ORG_ID
      BEGIN
         SELECT SITE.CUST_ACCOUNT_ID
               ,SITE.CUST_ACCT_SITE_ID
         INTO   V_SHIP_CUSTOMER_ID
               ,V_SHIP_ADDRESS_ID
         FROM   HZ_CUST_SITE_USES_ALL USES
               ,HZ_CUST_ACCT_SITES_ALL SITE
         WHERE USES.SITE_USE_ID=V_SHIP_TO_ORG_ID
         AND   SITE.CUST_ACCT_SITE_ID=USES.CUST_ACCT_SITE_ID;
      EXCEPTION
         WHEN OTHERS THEN
            x_retcode:=2;
            x_errbuf:='查找收货地点失败';
             DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
            RETURN;
      END;

      IF V_SHIP_CUSTOMER_ID IS NULL OR V_SHIP_ADDRESS_ID IS NULL THEN
         x_retcode:=2;
         x_errbuf:='查找收货方失败';
          DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
         RETURN;
      END IF;
      L_PROCESS_PHASE:=8;

   SELECT RA_CUSTOMER_TRX_LINES_S.NEXTVAL INTO V_INTERFACE_LINE_ID FROM DUAL;

   V_LINES_REC.INTERFACE_LINE_ID := V_INTERFACE_LINE_ID;
   V_LINES_REC.ORG_ID := P_ORG_ID;
   V_LINES_REC.INTERFACE_LINE_CONTEXT := 'SPLIT_FREIGHT';
   V_LINES_REC.INTERFACE_LINE_ATTRIBUTE1 := P_ORDER_HEADER_NUMBER;
   V_LINES_REC.INTERFACE_LINE_ATTRIBUTE2 := P_SHIP_HEADER_ID;
   V_LINES_REC.INTERFACE_LINE_ATTRIBUTE3 := V_DELIVERY_NAME;
   V_LINES_REC.INTERFACE_LINE_ATTRIBUTE4 := P_SEND_NUM;
   V_LINES_REC.INTERFACE_LINE_ATTRIBUTE5 := P_TYPE;
   V_LINES_REC.BATCH_SOURCE_NAME := 'OM导入发票1';
   V_LINES_REC.SET_OF_BOOKS_ID := V_SET_OF_BOOKS_ID;
   V_LINES_REC.LINE_TYPE := 'LINE';

   SELECT DECODE(P_TYPE, 1, '运保费', '其它应收')
     INTO V_LINES_REC.DESCRIPTION
     FROM DUAL;

   V_LINES_REC.CURRENCY_CODE := V_CURRENCY_CODE;
   V_LINES_REC.AMOUNT := P_AMOUNT;
   V_LINES_REC.CUST_TRX_TYPE_ID := V_CUST_TRX_TYPE_ID;
   V_LINES_REC.TERM_ID := V_PAYMENT_TERM_ID;
   V_LINES_REC.ORIG_SYSTEM_BILL_CUSTOMER_ID := V_BILL_CUSTOMER_ID;
   V_LINES_REC.ORIG_SYSTEM_BILL_ADDRESS_ID := V_BILL_ADDRESS_ID;
   V_LINES_REC.ORIG_SYSTEM_SHIP_CUSTOMER_ID := V_SHIP_CUSTOMER_ID;
   V_LINES_REC.ORIG_SYSTEM_SHIP_ADDRESS_ID := V_SHIP_ADDRESS_ID;
   V_LINES_REC.ORIG_SYSTEM_SOLD_CUSTOMER_ID := P_CUSTOMER_ID;

   IF V_CURRENCY_CODE = V_SOB_CURRENCY_CODE THEN
      V_LINES_REC.CONVERSION_TYPE := 'User';
      V_LINES_REC.CONVERSION_DATE := NULL;
      V_LINES_REC.CONVERSION_RATE := 1;
   ELSE
      V_LINES_REC.CONVERSION_TYPE := 'Corporate';
      V_LINES_REC.CONVERSION_DATE := NULL;
      V_LINES_REC.CONVERSION_RATE := NULL;
   END IF;
   --5个 who
   V_LINES_REC.CREATED_BY := V_USER_ID;
   V_LINES_REC.CREATION_DATE :=SYSDATE ;
   V_LINES_REC.LAST_UPDATED_BY:= V_USER_ID ;
   V_LINES_REC.LAST_UPDATE_DATE:= SYSDATE;
   V_LINES_REC.LAST_UPDATE_LOGIN := V_LOGIN_ID ;

   V_LINES_REC.PRIMARY_SALESREP_ID := V_PRIMARY_SALESREP_ID;
   --V_LINES_REC.TAX_CODE                     := TRIM(P_VAT_CODE);
   V_LINES_REC.TRX_DATE := P_ACTUAL_DEPARTURE_DATE;
   V_LINES_REC.GL_DATE := P_ACTUAL_DEPARTURE_DATE;
   V_LINES_REC.SALES_ORDER := P_ORDER_HEADER_NUMBER || '/' || P_SEND_NUM;
   V_LINES_REC.TRANSLATED_DESCRIPTION := P_SEND_NUM;
   V_SALESCREDITS_REC.INTERFACE_LINE_ID := V_INTERFACE_LINE_ID;
   V_SALESCREDITS_REC.ORG_ID := P_ORG_ID;
   V_SALESCREDITS_REC.INTERFACE_LINE_CONTEXT := 'SPLIT_FREIGHT';
   V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE1 := P_ORDER_HEADER_NUMBER;
   V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE2 := P_SHIP_HEADER_ID;
   V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE3 := V_DELIVERY_NAME;
   V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE4 := P_SEND_NUM;
   V_SALESCREDITS_REC.INTERFACE_LINE_ATTRIBUTE5 := P_TYPE;
   V_SALESCREDITS_REC.SALES_CREDIT_TYPE_ID := 1;
   V_SALESCREDITS_REC.SALESREP_ID := V_PRIMARY_SALESREP_ID;
   V_SALESCREDITS_REC.SALES_CREDIT_PERCENT_SPLIT := 100;

    --5个 who
   V_SALESCREDITS_REC.CREATED_BY := V_USER_ID;
   V_SALESCREDITS_REC.CREATION_DATE :=SYSDATE ;
   V_SALESCREDITS_REC.LAST_UPDATED_BY:= V_USER_ID ;
   V_SALESCREDITS_REC.LAST_UPDATE_DATE:= SYSDATE;
   V_SALESCREDITS_REC.LAST_UPDATE_LOGIN := V_LOGIN_ID ;
   L_PROCESS_PHASE:=9;

INSERT INTO RA_INTERFACE_LINES_ALL
VALUES V_LINES_REC;

   INSERT INTO RA_INTERFACE_SALESCREDITS_ALL
       VALUES V_SALESCREDITS_REC;
       L_PROCESS_PHASE:=10;
   DBMS_OUTPUT.PUT_LINE('x_retcode'||x_retcode||'*'||'x_errbuf'||x_errbuf||'程序进度'||L_PROCESS_PHASE);
COMMIT;
END;

/*
在查找类型的时候，有定义了一个快速编码 XYG_FREIGHT_TRANSACTION_TYPE
里面用OU_ID&交易类型进行维护数据

0 0