[API] 匯率轉入

範例 : 適用測試區匯率轉入 

DECLARE

  vFromCurrency   gl_currencies.currency_code%TYPE := 'USD';

  vToCurrency     gl_currencies.currency_code%TYPE := 'TWD';

  vConversionType gl_daily_conversion_types.conversion_type%TYPE := 'XXX';

  vFormDate       gl_daily_rates_interface.from_conversion_date%TYPE := SYSDATE;

  vToDate         gl_daily_rates_interface.to_conversion_date%TYPE := SYSDATE + 30;

  vRate               gl_daily_rates_interface.Conversion_Rate%TYPE;

  vUserConversionType gl_daily_rates_interface.user_conversion_type%TYPE;

  eNotFound EXCEPTION;

  vRequestId NUMBER;

  --取得前一次匯率

  FUNCTION getPreviousRate(pForwardDays IN NUMBER DEFAULT 100) RETURN NUMBER IS

    vExists VARCHAR2(1);

  BEGIN

    FOR i IN (SELECT trunc(SYSDATE - ROWNUM) AS previousDate

              FROM   dual

              CONNECT BY ROWNUM <= pForwardDays)

    LOOP

      vExists := gl_currency_api.rate_exists(x_from_currency => vfromcurrency,

                                             x_to_currency => vtoCurrency,

                                             x_conversion_date => i.previousDate,

                                             x_conversion_type => vConversionType);

      IF vExists = 'Y' THEN

        RETURN gl_currency_api.get_rate(vfromcurrency, vtoCurrency,

                                        i.previousDate, vConversionType);

      END IF;

    END LOOP;

  EXCEPTION

    WHEN OTHERS THEN

      RETURN NULL;

  END getPreviousRate;

BEGIN

  vRate := getPreviousRate; --取得前一次的匯率

  IF vRate IS NULL THEN

    RAISE eNotFound;

  END IF;

  gl_daily_conv_types_pkg.select_columns(x_conversion_type => vConversionType,

                                         x_user_conversion_type => vUserConversionType);

  INSERT INTO gl_daily_rates_interface

    (from_currency,

     to_currency,

     from_conversion_date,

     to_conversion_date,

     user_conversion_type,

     conversion_rate,

     mode_flag)

  VALUES

    (vFromCurrency,

     vToCurrency,

     vFormDate,

     vToDate,

     vUserConversionType,

     vRate,

     'I'); --(d= delete, i = insert, u = update)

  COMMIT;

  --提交轉入請求

  fnd_global.APPS_INITIALIZE(000, 000, 101);

  vRequestId := fnd_request.submit_request(application => 'SQLGL',

                                           program => 'GLDRICCP');

  IF vRequestId <> 0 OR vRequestId IS NOT NULL THEN

    dbms_output.put_line(vRequestId);

    COMMIT;

  END IF;

EXCEPTION

  WHEN eNotFound THEN

    dbms_output.put_line('無法取得前一次匯率資料 !!!');

  WHEN OTHERS THEN

    dbms_output.put_line(dbms_utility.format_error_stack);

END;