oracle 计划外折旧,计划外折旧(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;