4 Replies Latest reply: Feb 15, 2013 5:58 AM by user8984055 RSS

    Unable to move data to the base table using interface table

    user8984055
      Hi, i am workin on AR invoice conversion. Although the data is moving to the interface table but its not goin to the base table when i run the autoinvoice master program. PFB my code-



      DECLARE

      gn_trx_type NUMBER:=0;
      gn_term_name NUMBER:=0;
      gn_currency NUMBER:=0;
      gn_trx_source NUMBER:=0;
      gn_uom_code NUMBER:=0;
      gn_tax_code NUMBER:=0;
      gn_opr_unit NUMBER:=0;
      gn_conversion_type NUMBER:=0;
      gn_item NUMBER:=0;
      gn_flag NUMBER:=0;
      gn_org_id NUMBER :=0;
      gv_date number:=0;
      gv_RECORD_status VARCHAR2(240):=null;
      CURSOR g_arinv_cur
      IS
      SELECT * FROM XYL_CONV_ARINV_STG;
      used_exception EXCEPTION;

      ------------------------------------------------TRANSACTION TYPE(line type)------------------------------------------------

      BEGIN
      FOR v_aprec IN g_arinv_cur
      LOOP
      BEGIN
      gn_trx_type:=0;
      gn_term_name:=0;
      gn_currency:=0;
      gn_trx_source:=0;
      gn_uom_code:=0;
      gn_tax_code:=0;
      gn_opr_unit:=0;
      gn_conversion_type:=0;
      gn_item:=0;
      gn_flag:=0;
      gn_org_id :=0;
      gv_RECORD_status:='';
      DBMS_OUTPUT.NEW_LINE();
      DBMS_OUTPUT.PUT_LINE('------------------------------------------------------');
      DBMS_OUTPUT.NEW_LINE();
      DBMS_OUTPUT.PUT_LINE(' NEW RECORD INPUT ');
      DBMS_OUTPUT.NEW_LINE();
      DBMS_OUTPUT.PUT_LINE('------------------------------------------------------');
      DBMS_OUTPUT.NEW_LINE();
      BEGIN
      IF v_aprec.STG_LINE_CONTEXT IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('TRANSACTION TYPE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE STG_LINE_CONTEXT IS NULL;
      RAISE used_exception;
      ELSE
      SELECT count(name)
      INTO gn_trx_type
      FROM RA_CUST_TRX_TYPES_ALL
      WHERE NAME=v_aprec.STG_LINE_CONTEXT;
      IF gn_trx_type > 0 THEN
      DBMS_OUTPUT.PUT_LINE('TRANSACTION TYPE IS VALID');
      /*UPDATE XYL_CONV_ARINV_STG
      SET
      --ERROR_MESSAGE = 'VALID RECORD',
      RECORD_STATUS = 'V'
      WHERE transaction_type=v_aprec.transaction_type;*/
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID TRANSACTION TYPE');
      gv_RECORD_status := 'INVALID TRANSACTION TYPE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE STG_LINE_CONTEXT=v_aprec.STG_LINE_CONTEXT;
      END;

      ------------------------------------------------TRANSACTION SOURCE--------------------------------------------
      BEGIN
      IF v_aprec.BATCH_SOURCE_NAME IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('TRANSACTION SOURCE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE BATCH_SOURCE_NAME IS NULL;
      ELSE
      SELECT count(name)
      INTO gn_trx_source
      FROM RA_BATCH_SOURCES_ALL
      WHERE name=v_aprec.BATCH_SOURCE_NAME;
      IF gn_trx_source >0
      THEN
      DBMS_OUTPUT.PUT_LINE('TRANSACTION SOURCE IS VALID');
      /* UPDATE XYL_CONV_ARINV_STG
      SET
      -- ERROR_MESSAGE = ' VALID RECORD',
      RECORD_STATUS = 'V'
      WHERE transaction_source =v_aprec.transaction_source;*/
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID TRANSACTION SOURCE');
      gv_RECORD_status:= gv_RECORD_status ||'INVALID TRANSACTION SOURCE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE BATCH_SOURCE_NAME =v_aprec.BATCH_SOURCE_NAME;
      END;
      ------------------------------------------------------------CURRENCY VALIDATION--------------------------------
      BEGIN
      IF v_aprec.currency_code IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('CURRENCY CODE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE currency_code IS NULL ;
      ELSE
      SELECT count(currency_code)
      INTO gn_currency
      FROM FND_CURRENCIES
      WHERE currency_code=v_aprec.currency_code;
      IF gn_currency >0
      THEN
      DBMS_OUTPUT.PUT_LINE('CURRENCY CODE IS VALID');
      /*UPDATE XYL_CONV_ARINV_STG
      SET
      -- ERROR_MESSAGE = ' VALID CURRENCY CODE',
      RECORD_STATUS = 'V'
      WHERE currency_code=v_aprec.currency_code;*/
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID CURRENCY CODE');
      gv_RECORD_status:= gv_RECORD_status ||'INVALID CURRENCY CODE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE currency_code=v_aprec.currency_code;
      END;
      -----------------------------------------------CONVERSION TYPE----------------------------------------------------------
      BEGIN
      IF v_aprec.conversion_type IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('CONVERSION TYPE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE conversion_type IS NULL;
      ELSE
      SELECT count(CONVERSION_TYPE)
      INTO gn_conversion_type
      FROM GL_DAILY_CONVERSION_TYPES
      WHERE CONVERSION_TYPE=v_aprec.CONVERSION_TYPE;
      IF gn_conversion_type >0
      THEN
      DBMS_OUTPUT.PUT_LINE('CONVERSION TYPE IS VALID');
      /*UPDATE XYL_CONV_ARINV_STG
      SET
      -- ERROR_MESSAGE = ' VALID CURRENCY CODE',
      RECORD_STATUS = 'V'
      WHERE CONVERSION_TYPE=v_aprec.CONVERSION_TYPE;*/
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID CONVERSION TYPE IS INVALID');
      gv_RECORD_status:= gv_RECORD_status ||'INVALID CONVERSION TYPE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE CONVERSION_TYPE=v_aprec.CONVERSION_TYPE;
      END;



      ------------------------------------------------------PAYMENT TERM----------------------------------------------

      BEGIN
      IF v_aprec.TERM_NAME IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('PAYMENT TERM IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE TERM_NAME IS NULL;
      ELSE
      SELECT count(name)
      INTO gn_term_name
      FROM RA_TERMS
      WHERE name =v_aprec.TERM_NAME;
      IF gn_term_name >0
      THEN
      DBMS_OUTPUT.PUT_LINE('PAYMENT TERM IS VALID');
      /* UPDATE XYL_CONV_ARINV_STG
      SET
      -- ERROR_MESSAGE = ' VALID PAYMENT TERM',
      RECORD_STATUS = 'V'
      WHERE TERM_NAME =v_aprec.TERM_NAME; */
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID PAYMENT TERM');
      gv_RECORD_status:= gv_RECORD_status || 'INVALID PAYMENT TERM';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE TERM_NAME =v_aprec.TERM_NAME;
      END;


      ----------------------------------------------------------INVENTORY ITEM ID-----------------------------------------
      BEGIN
      IF v_aprec.inventory_item_id IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('INVENTORY ITEM ID IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE inventory_item_id IS NULL;
      ELSE
      SELECT count(inventory_item_id)
      INTO gn_item
      FROM MTL_SYSTEM_ITEMS
      WHERE inventory_item_id=v_aprec.inventory_item_id;
      IF gn_item>0
      THEN
      DBMS_OUTPUT.PUT_LINE('INVENTORY ITEM ID IS VALID');
      /*UPDATE XYL_CONV_ARINV_STG
      SET
      -- ERROR_MESSAGE = ' VALID INVENTORY ITEM',
      RECORD_STATUS = 'V'
      WHERE item_number=v_aprec.item_number;*/
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID INVENTORY ITEM ID');
      gv_RECORD_status:= gv_RECORD_status || 'INVALID ITEM NUMBER';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE inventory_item_id = v_aprec.inventory_item_id;
      END;
      ----------------------------------------------UNITS OF MEASURE--------------------------------------------------
      BEGIN
      IF v_aprec.uom_code IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('UOM CODE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE uom_code IS NULL ;
      ELSE
      SELECT count(UOM_CODE)
      INTO gn_uom_code
      FROM MTL_UNITS_OF_MEASURE
      WHERE UOM_CODE=v_aprec.UOM_CODE;
      IF gn_uom_code >0 THEN
      DBMS_OUTPUT.PUT_LINE('UOM IS VALID');
      /*UPDATE XYL_CONV_ARINV_STG
      SET
      --ERROR_MESSAGE = ' VALID UOM CODE',
      RECORD_STATUS = 'V'
      WHERE UOM_CODE=v_aprec.uom_code; */
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID UOM CODE');
      gv_RECORD_status:= gv_RECORD_status || 'INVALID UNITS OF MEASURE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE UOM_CODE=v_aprec.uom_code;
      END;

      --------------------------------------------------AR_VAT_TAX_ALL_B------------------------------------
      BEGIN
      IF v_aprec.tax_code IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('TAX CODE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE tax_code IS NULL;
      ELSE
      SELECT count(tax_code)
      INTO gn_tax_code
      FROM AR_VAT_TAX_ALL_B
      WHERE tax_code=v_aprec.tax_code;
      IF gn_tax_code>0
      THEN
      DBMS_OUTPUT.PUT_LINE('TAX CODE IS VALID');
      /* UPDATE XYL_CONV_ARINV_STG
      SET
      --ERROR_MESSAGE = 'VALID TAX CODE',
      RECORD_STATUS = 'V'
      WHERE tax_code=v_aprec.tax_code;*/
      gn_flag:=gn_flag+1;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID TAX CODE');
      gv_RECORD_status:= gv_RECORD_status || 'INVALID TAX CODE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE tax_code=v_aprec.tax_code;
      END;

      --------------------------------------------------OPERATING UNITS------------------------------------------
      BEGIN
      IF v_aprec.ORG_ID IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('OPERATING UNIT IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE ORG_ID IS NULL;
      ELSE
      SELECT COUNT(name)
      INTO gn_opr_unit
      FROM hr_operating_units
      WHERE ORGANIZATION_ID=v_aprec.ORG_ID;
      IF gn_opr_unit>0
      THEN
      DBMS_OUTPUT.PUT_LINE('OPERATING UNIT IS VALID');
      /*UPDATE XYL_CONV_ARINV_STG
      SET
      -- ERROR_MESSAGE = ' VALID OPERATING UNIT',
      RECORD_STATUS = 'V'
      WHERE operating_unit=v_aprec.operating_unit;*/
      gn_flag:=gn_flag+1;
      DBMS_OUTPUT.PUT_LINE('Flag:'||gn_flag);
      SELECT ORGANIZATION_ID INTO gn_org_id FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID=v_aprec.ORG_ID;
      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('INVALID OPERATING UNITS');
      gv_RECORD_status:= gv_RECORD_status || 'INVALID OPERATING UNIT CODE';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE =gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE ORG_ID=v_aprec.ORG_ID;
      END;
      ---------------------------------------------------------GL DATE VALIDATION-----------------------------------
      BEGIN
      IF v_aprec.GL_DATE IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('INVOICE DATE IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE GL_DATE IS NULL;
      ELSE
      SELECT COUNT (*)
      INTO gv_date
      FROM gl_period_statuses gps

      where 1=1 and gps.set_of_books_id = v_aprec.set_of_books_id
      AND gps.closing_status IN ('O', 'F')
      AND v_aprec.GL_DATE BETWEEN NVL (gps.start_date, v_aprec.GL_DATE ) AND NVL (gps.end_date,
      v_aprec.GL_DATE
      );

      if gv_date>1
      THEN
      DBMS_OUTPUT.PUT_LINE('INVOICE DATE IS VALID');

      gn_flag:=gn_flag+1;
      DBMS_OUTPUT.PUT_LINE('Flag:'||gn_flag);

      ELSE
      RAISE used_exception;
      END IF;
      END IF;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('CLOSED GL DATE');
      gv_RECORD_status:= gv_RECORD_status || 'error';
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE =gv_RECORD_status,
      RECORD_STATUS = 'E'
      WHERE set_of_books_id = v_aprec.set_of_books_id;
      END;

      ----------------------------------------------NOT NULL VALIDATIONS-----------------------------------------
      /* BEGIN
      IF v_aprec.transaction_class IS NULL
      THEN
      DBMS_OUTPUT.PUT_LINE('TRANSACTION CLASS IS NULL');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'TRANSACTION CLASS IS NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE transaction_class IS NULL;
      --RAISE used_exception;
      ELSE IF v_aprec.GL_DATE IS NULL
      THEN
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE GL_DATE IS NULL;
      DBMS_OUTPUT.PUT_LINE('INVOICE DATE IS NULL');
      --RAISE used_exception;
      ELSE IF v_aprec.status IS NULL THEN
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = 'NULL RECORD',
      RECORD_STATUS = 'E'
      WHERE status IS NULL;
      DBMS_OUTPUT.PUT_LINE('STATUS IS NULL');
      --RAISE used_exception;
      END IF;
      END IF;
      END IF;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('NOT NULL EXCEPTION BLOCK:'||SQLERRM);
      END;*/
      IF gn_flag=10
      THEN
      DBMS_OUTPUT.PUT_LINE ('ALL THE FIELDS ARE VALID');
      UPDATE XYL_CONV_ARINV_STG
      SET
      ERROR_MESSAGE = ' ALL THE FIELDS ARE VALID',
      RECORD_STATUS = 'V'
      WHERE LINE_NUMBER=v_aprec.LINE_NUMBER;

      INSERT INTO ra_interface_lines_all
      (INTERFACE_LINE_ID,

      INTERFACE_LINE_CONTEXT,
      INTERFACE_LINE_ATTRIBUTE1,
      BATCH_SOURCE_NAME,
      SET_OF_BOOKS_ID,
      LINE_TYPE,
      DESCRIPTION,
      CURRENCY_CODE,
      AMOUNT,
      CUST_TRX_TYPE_ID,
      TERM_NAME,
      TERM_ID,
      ORIG_SYSTEM_BILL_CUSTOMER_ID,
      ORIG_SYSTEM_BILL_ADDRESS_ID,
      CONVERSION_TYPE,
      CONVERSION_DATE,
      CONVERSION_RATE,
      CUSTOMER_TRX_ID,
      TRX_DATE,
      GL_DATE,
      TRX_NUMBER,
      LINE_NUMBER,
      QUANTITY,
      QUANTITY_ORDERED,
      UNIT_SELLING_PRICE,
      UNIT_STANDARD_PRICE,
      TAX_CODE,
      PRIMARY_SALESREP_ID,
      PURCHASE_ORDER,
      UOM_CODE,
      TAX_EXEMPT_FLAG,
      CREATED_BY,
      CREATION_DATE,
      LAST_UPDATED_BY,
      LAST_UPDATE_DATE,
      LAST_UPDATE_LOGIN,
      ORG_ID



      )
      VALUES
      (
      v_aprec.STG_LINE_ID,

      v_aprec.STG_LINE_CONTEXT,
      v_aprec.STG_LINE_ATTRIBUTE1,
      v_aprec.BATCH_SOURCE_NAME,
      v_aprec.SET_OF_BOOKS_ID,
      v_aprec.LINE_TYPE,
      v_aprec.DESCRIPTION,
      v_aprec.CURRENCY_CODE,
      v_aprec.AMOUNT,
      v_aprec.CUST_TRX_TYPE_ID,
      v_aprec.TERM_NAME,
      v_aprec.TERM_ID,
      v_aprec.ORIG_SYSTEM_BILL_CUSTOMER_ID,
      v_aprec.ORIG_SYSTEM_BILL_ADDRESS_ID,
      v_aprec.CONVERSION_TYPE,
      v_aprec.CONVERSION_DATE,
      v_aprec.CONVERSION_RATE,
      v_aprec.CUSTOMER_TRX_ID,
      v_aprec.TRX_DATE,
      v_aprec.GL_DATE,
      v_aprec.TRX_NUMBER,
      v_aprec.LINE_NUMBER,
      v_aprec.QUANTITY,
      v_aprec.QUANTITY_ORDERED,
      v_aprec.UNIT_SELLING_PRICE,
      v_aprec.UNIT_STANDARD_PRICE,
      v_aprec.TAX_CODE,
      v_aprec.PRIMARY_SALESREP_ID,
      v_aprec.PURCHASE_ORDER,
      v_aprec.UOM_CODE,
      v_aprec.TAX_EXEMPT_FLAG,
      v_aprec.CREATED_BY,
      v_aprec.CREATION_DATE,
      v_aprec.LAST_UPDATED_BY,
      v_aprec.LAST_UPDATE_DATE,
      v_aprec.LAST_UPDATE_LOGIN,
      v_aprec.ORG_ID



      );

      END IF;
      EXCEPTION
      WHEN used_exception THEN
      DBMS_OUTPUT.PUT_LINE ('INVALID RECORD FOUND,LOOP TERMINATED');
      END;
      END LOOP;
      COMMIT;
      END;


      Interface tables used are: RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL.
      i have done validations for Lines only. Is it necessary to do validations for Distributions for Auto Invoice master program to run?

      Any help would be appreciated.


      Thanks
        • 1. Re: Unable to move data to the base table using interface table
          nani.krishna
          what is the error messsage you are getting after you run Auto Invoice master program

          Hope you have defined the Interface line context, batch sources, transaction types, customer bill to id, sites

          make sure the data you populate should be in align with the batch source you define
          for example: Batch source: you can say Acccount code combination ID, whereas in RA_INTERFACE_DISTRIBUTIONS table if you are populating segments then AUto invoice will fail

          Cross check all the setups once and re run Auto Invoice

          If there is too much of data, just run for one order number and then check

          Best Regards
          Krishna
          • 2. Re: Unable to move data to the base table using interface table
            user8984055
            Hii,

            I AM GETTING THE FOLLOWING ERROR MESSAGE WHEN I RAN THE AUTO INVOICE MASTER PROGRAM :










            ---------------------------------------------------------------------------
            Receivables: Version : 12.0.0

            Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

            RAXMTR module: Autoinvoice Master Program
            ---------------------------------------------------------------------------

            Current system time is 08-FEB-2013 12:29:59

            ---------------------------------------------------------------------------

            You have not assigned a value for AR: Use Parallel Hints profile option. The default value will be used.
            raagtr()+ Current system time is 08-FEB-2013 12:29:59
            crb->raaucrpn = RAXTRX
            crb->raaucrcr =
            crb->raaucrnt = 0
            crb->raaucrml = 3
            raagtr()- Current system time is 08-FEB-2013 12:29:59
            raampa()+ Current system time is 08-FEB-2013 12:29:59
            raampa()- Current system time is 08-FEB-2013 12:29:59
            raamcw()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raacrf()+ Current system time is 08-FEB-2013 12:29:59
            raacrf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            raaasf()+ Current system time is 08-FEB-2013 12:29:59
            raaasf()- Current system time is 08-FEB-2013 12:29:59
            WHERE NVL(INTERFACE_STATUS, '~') != 'P'
            AND (REQUEST_ID IS NULL
            )
            AND BATCH_SOURCE_NAME = 'Intercompany'
            AND DECODE(498, -99, ORG_ID, 498) = ORG_ID
            AND CUST_TRX_TYPE_ID = 20

            AND GL_DATE between TO_DATE('03-JAN-99' , 'DD-MON-RR') and TO_DATE('31-JAN-99' , 'DD-MON-RR') AND LINK_TO_LINE_ATTRIBUTE1 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE2 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE3 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE4 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE5 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE6 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE7 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE8 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE9 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE10 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE11 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE12 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE13 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE14 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE15 IS NULL

            len = 816
            raamcw()- Current system time is 08-FEB-2013 12:29:59
            SELECT /*+ parallel(l) */ COUNT(*)
            from ra_interface_lines_all l
            WHERE NVL(INTERFACE_STATUS, '~') != 'P'
            AND (REQUEST_ID IS NULL
            )
            AND BATCH_SOURCE_NAME = 'Intercompany'
            AND DECODE(498, -99, ORG_ID, 498) = ORG_ID
            AND CUST_TRX_TYPE_ID = 20

            AND GL_DATE between TO_DATE('03-JAN-99' , 'DD-MON-RR') and TO_DATE('31-JAN-99' , 'DD-MON-RR') AND LINK_TO_LINE_ATTRIBUTE1 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE2 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE3 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE4 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE5 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE6 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE7 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE8 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE9 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE10 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE11 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE12 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE13 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE14 IS NULL
            AND LINK_TO_LINE_ATTRIBUTE15 IS NULL

            len = 893
            No data found

            Total rows for current operating unit: 0
            Batch source [Intercompany]: 0
            Transaction Type [20]: 0
            GL Date from [1999/01/03 00:00:00] to [1999/01/31 00:00:00]: 0
            ---------------------------------------------------------------------------
            Start of log messages from FND_FILE
            ---------------------------------------------------------------------------
            ---------------------------------------------------------------------------
            End of log messages from FND_FILE
            ---------------------------------------------------------------------------


            ---------------------------------------------------------------------------
            Executing request completion options...

            ------------- 1) PRINT   -------------


            Printing output file.
            Request ID : 6651128      
            Number of copies : 0      
            Printer : noprint

            --------------------------------------


            Finished executing request completion options.

            ---------------------------------------------------------------------------
            Concurrent request completed successfully
            Current system time is 08-FEB-2013 12:30:11

            ---------------------------------------------------------------------------
            • 3. Re: Unable to move data to the base table using interface table
              ThAn
              Hi,

              Total rows for current operating unit: 0

              Are you sure that you are using right ORG_ID? Or by other means:
              are you using right responsibility to import those interface records
              (the responsibility which uses the same ORG_ID as the one used
              in interface records)?

              Regards
              • 4. Re: Unable to move data to the base table using interface table
                user8984055
                Hi,

                Now i am getting the following error:

                Errors: 1) All enabled segments of Transaction Flexfield must have a value (.)

                I know the enabled attributes for interface_line_attribute1=order_id and interface_line_attribute=legacy_line_id . Is the order id unique ???and the transaction source i am using is LEGACY.

                Please help.



                Error file-:





                Vision Italy EUR AutoInvoice Execution Report Date: 14-FEB-2013 07:56
                Page: 1 of 3
                Request Id: 5967638

                Batch Source: LEGACY

                Transaction Flexfield:

                Transaction Type:
                Bill To Customer Number: to
                Bill To Customer Name: to
                GL Date: to
                Ship Date: to
                Transaction Number: to
                Sales Order Number: to
                Transaction Date: to
                Ship To Cust Number: to
                Ship To Cust Name: to

                Interface Lines: Interface Distributions:

                Selected: 3 Selected: 0
                Successfully Processed: 0 Successfully Processed: 0
                Rejected: 3 Rejected: 0

                Interface Salesreps: Interface Contingencies:

                Selected: 0 Selected: 0
                Successfully Processed: 0 Successfully Processed: 0
                Rejected: 0 Rejected: 0



                Transactions Created:


                Grand Totals:

                Number of Number of Number of Number of Number of
                Class Transactions Lines (*) Sales Credits Distributions Contingencies Invoice Amount
                ------------------------------ ------------- ------------- ------------- ------------- ------------- -------------------
                ------------- ------------- ------------- ------------- ------------- -------------------
                0.00


                * Number of Lines includes Tax lines






                Vision Italy EUR AutoInvoice Execution Report Date: 14-FEB-2013 07:56
                Page: 2 of 3
                Request Id: 5967638






                Vision Italy EUR AutoInvoice Execution Report Date: 14-FEB-2013 07:56
                Page: 3 of 3
                Request Id: 5967638


                Summary of Receipts Processed :






                Vision Italy EUR AutoInvoice Validation Report Date: 14-FEB-2013 07:56
                Page: 1 of 3
                Auto Invoice Request Id 5967638
                Batch Source: LEGACY

                Transaction Flexfield: VISION BUILD:

                Type Bill to Customer Name Item Description Currency Amount
                -------------------- ---------------------------------------- ---------------------------------------- ----------- -----------------
                Invoice Computer Service and Rentals TANVI EUR 40,161.00

                Errors: 1) All enabled segments of Transaction Flexfield must have a value (.)
                Type Bill to Customer Name Item Description Currency Amount
                -------------------- ---------------------------------------- ---------------------------------------- ----------- -----------------
                Invoice Computer Service and Rentals TANVI EUR 40,161.00

                Errors: 1) All enabled segments of Transaction Flexfield must have a value (.)
                Type Bill to Customer Name Item Description Currency Amount
                -------------------- ---------------------------------------- ---------------------------------------- ----------- -----------------
                Invoice Computer Service and Rentals TANVI EUR 40,161.00




                +**Errors:   1) All enabled segments of Transaction Flexfield must have a value (.)*



                *+
                Summary of Transactions Rejected:

                Currency Number of Number of Number of
                Code Lines Credit Distributions Invoice Currency Amount
                -------------- -------------- -------------- -------------- ---------------------------
                EUR 3 0 0 120,483.00

                Summary of Transactions with Errors:

                Currency Number of Number of Number of
                Code Lines Credit Distributions Invoice Currency Amount
                -------------- -------------- -------------- -------------- ---------------------------
                EUR 3 0 0 120,483.00






                Vision Italy EUR AutoInvoice Validation Report Date: 14-FEB-2013 07:56
                Page: 2 of 3
                Auto Invoice Request Id 5967638
                Batch Source: LEGACY






                Vision Italy EUR AutoInvoice Exception Report Date: 14-FEB-2013 07:56
                Page: 3 of 3
                Auto Invoice Request Id 5967638
                Batch Source: LEGACY