計劃外折舊(unplanned deprn)API開發常式

-******************************************************************************
  -- Punction: Get transaction date for API
  --******************************************************************************
  FUNCTION get_date_f(p_book_type_code IN VARCHAR2) RETURN DATE IS
    lv_transaction_date DATE;
  BEGIN
    --get transaction data entered
    SELECT greatest(dp.calendar_period_open_date,
                    least(trunc(SYSDATE), dp.calendar_period_close_date))
      INTO lv_transaction_date
      FROM fa_deprn_periods dp
     WHERE dp.book_type_code = p_book_type_code
       AND dp.period_close_date IS NULL;

    RETURN lv_transaction_date;
  EXCEPTION
    WHEN no_data_found THEN
      RETURN trunc(SYSDATE);
    WHEN too_many_rows THEN
      RETURN trunc(SYSDATE);
  END get_date_f;

  --******************************************************************************
  -- Procedure: exec API
  --******************************************************************************
  PROCEDURE do_unplanned_p(lv_errbuf OUT VARCHAR2, lv_retcode OUT VARCHAR2) IS
    lv_status                VARCHAR2(10);
    lv_msg_count             NUMBER;
    lv_msg_data              VARCHAR2(512);
    lv_transaction_header_id NUMBER(15);

  BEGIN
    FA_TRANS_API_PUB.DO_UNPLANNED(
                                  -- Standard Parameters --
                                  p_api_version      => 1.0,
                                  p_init_msg_list    => 'T',
                                  p_commit           => 'F',
                                  p_validation_level => 100,
                                  x_return_status    => lv_status,
                                  x_msg_count        => lv_msg_count,
                                  x_msg_data         => lv_msg_data,
                                  p_calling_fn       => 'fac_mass_deprn_adjustments_pkg.do_unplanned_p',
                                  -- API Options --
                                  p_debug_flag => 'NO',
                                  -- Out Parameters --
                                  x_transaction_header_id => lv_transaction_header_id,
                                  -- Transaction Info --
                                  p_transaction_date_entered => get_date_f(gv_catchup_amount.book_type_code),
                                  p_transaction_name         => 'ADJUSTMENT',
                                  p_transaction_subtype      => NULL,
                                  p_amortization_start_date  => NULL,
                                  p_calling_interface        => 'FAXASSET',
                                  p_last_update_date         => SYSDATE,
                                  p_last_updated_by          => -1,
                                  p_created_by               => -1,
                                  p_creation_date            => SYSDATE,
                                  p_last_update_login        => -1,
                                  p_attribute1               => NULL,
                                  p_attribute2               => NULL,
                                  p_attribute3               => NULL,
                                  p_attribute4               => NULL,
                                  p_attribute5               => NULL,
                                  p_attribute6               => NULL,
                                  p_attribute7               => NULL,
                                  p_attribute8               => NULL,
                                  p_attribute9               => NULL,
                                  p_attribute10              => NULL,
                                  p_attribute11              => NULL,
                                  p_attribute12              => NULL,
                                  p_attribute13              => NULL,
                                  p_attribute14              => NULL,
                                  p_attribute15              => NULL,
                                  p_attribute_category_code  => NULL,
                                  -- Asset Header Info --
                                  p_asset_id       => gv_catchup_amount.asset_id,
                                  p_book_type_code => gv_catchup_amount.book_type_code,
                                  -- Unplanned Depreciation Info --
                                  p_code_combination_id => gv_catchup_amount.code_combination_id,
                                  p_unplanned_amount    => gv_catchup_amount.catchup_amount,
                                  p_unplanned_type      => 'UNPLAN');

    IF lv_status = 'S' THEN
      --success
      --update record status
      UPDATE fac_mass_deprn_adjustment_load t
         SET t.status = 'Complete'
       WHERE t.request_id = gv_request_id
         AND t.book_type_code = gv_catchup_amount.book_type_code
         AND t.asset_number = gv_catchup_amount.asset_number;

    ELSE
      --failure
      --update record status
      UPDATE fac_mass_deprn_adjustment_load t
         SET t.status = 'Error'
       WHERE t.request_id = gv_request_id
         AND t.book_type_code = gv_catchup_amount.book_type_code
         AND t.asset_number = gv_catchup_amount.asset_number;
        END IF;
    COMMIT;

  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      lv_retcode := SQLCODE;
      lv_errbuf  := SQLERRM;
  END do_unplanned_p;