加载中…
正文 字体大小:

固定资产导入API

(2015-12-08 16:51:36)
标签:

365

分类: API
CREATE TABLE xx_asset_imp
(
asset_num  varchar2(15),asset_key_ccid number,
TAG_NUMBER varchar2(15),
DESCRIPTION varchar2(80),
ASSET_CATEGORY_ID number(15), 
asset_category varchar(255),
BOOK_TYPE_CODE  varchar2(15),
DATE_PLACED_IN_SERVICE date,
FIXED_ASSETS_COST number,
FIXED_ASSETS_UNITS number,
expense_account  varchar(255),
EXPENSE_CODE_COMBINATION_ID number,
LOCATION_ID number,
REVIEWER_COMMENTS  varchar2(60),
ASSET_TYPE   varchar(255),
TRANSACTION_DATE date,

deprn_method_code  varchar2(15),
life_in_months  number,
ytd_deprn number,  --本年累计折旧
deprn_reserve number,
bonus_ytd_deprn number,
bonus_deprn_reserve number

);


create or replace procedure auto_import_info_p_2 (errbuf out varchar2, retcode out number) is
  l_trans_rec FA_API_TYPES.trans_rec_type;
  l_dist_trans_rec FA_API_TYPES.trans_rec_type;
  l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
  l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
  l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
  l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
  l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
  l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
  l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
  l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
  l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
  l_inv_tbl FA_API_TYPES.inv_tbl_type;
  l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;

  l_return_status VARCHAR2(1);
  l_mesg_count number := 0;
  l_mesg_len number;
  l_mesg varchar2(4000);

      CURSOR C_ASSET_CUR IS
         SELECT * FROM xx_asset_imp ;

  begin
    

    fnd_profile.put('PRINT_DEBUG', 'Y'); --用于指定相关的配置信息
    dbms_output.enable(1000000);
    fa_srvr_msg.init_server_message; --初始化错误信息堆栈。
    fa_debug_pkg.initialize; --初始化调试信息堆栈
    
  for c_asset_cur_ref in C_ASSET_CUR loop 
             
              -- desc info
          l_asset_desc_rec.description :=c_asset_cur_ref.DESCRIPTION;
          l_asset_desc_rec.asset_key_ccid := 1001;

          -- cat info *** NEED TO CHANGE BASED ON CATEGORY SETUP FOR YOUR BOOK ***
          l_asset_cat_rec.category_id := 2001;

          --type info
          l_asset_type_rec.asset_type := 'CAPITALIZED';

          -- fin info
          l_asset_fin_rec.cost := c_asset_cur_ref.FIXED_ASSETS_COST ;
          l_asset_fin_rec.date_placed_in_service := c_asset_cur_ref.DATE_PLACED_IN_SERVICE ;
          l_asset_fin_rec.depreciate_flag := 'YES';
          l_asset_fin_rec.deprn_method_code := 'STL';
          l_asset_fin_rec.life_in_months := 48;

          -- deprn info
          l_asset_deprn_rec.ytd_deprn := 500;
          l_asset_deprn_rec.deprn_reserve := 500;
          l_asset_deprn_rec.bonus_ytd_deprn := 0;
          l_asset_deprn_rec.bonus_deprn_reserve := 0;


          -- book / trans info
          l_asset_hdr_rec.book_type_code :=c_asset_cur_ref.BOOK_TYPE_CODE;
          l_trans_rec.transaction_date_entered := l_asset_fin_rec.date_placed_in_service ;
          l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;

          -- distribution info

          l_asset_dist_rec.units_assigned :=  c_asset_cur_ref.FIXED_ASSETS_UNITS ;
          l_asset_dist_rec.expense_ccid := 145609;
          l_asset_dist_rec.location_ccid :=1002;
          l_asset_dist_rec.assigned_to := null;
          l_asset_dist_rec.transaction_units :=  l_asset_dist_rec.units_assigned ;
          l_asset_dist_tbl(1) := l_asset_dist_rec;



           
             
               

            
  
  



    -- call the api
    fa_addition_pub.do_addition(p_api_version          => 1.0, --版本                         --内部使用参数,版本信息
                                p_init_msg_list        => fnd_api.g_false, --不初始化消息                 --确定是否应该消息栈初始化和清除。
                                p_commit               => fnd_api.g_false, --是否自动提交处理过的资产
                                p_validation_level     => fnd_api.g_valid_level_full, --检查API是否应该对资产进行验证
                                x_return_status        => l_return_status, --确定是否API的成功完成。
                                x_msg_count            => l_mesg_count, --邮件数堆栈的消息。
                                x_msg_data             => l_mesg, --消息堆栈
                                p_calling_fn           => null, --调用API函数
                                px_trans_rec           => l_trans_rec, --描述的交易发生。
                                px_dist_trans_rec      => l_dist_trans_rec, --描述了交易分布?
                                px_asset_hdr_rec       => l_asset_hdr_rec, --添加资产的唯一标识符。
                                px_asset_desc_rec      => l_asset_desc_rec, --资产说明
                                px_asset_type_rec      => l_asset_type_rec, --资产类型
                                px_asset_cat_rec       => l_asset_cat_rec, --资产分类信息
                                px_asset_hierarchy_rec => l_asset_hierarchy_rec, --资产层次的信息Hierarchy information of the asset
                                px_asset_fin_rec       => l_asset_fin_rec, --资产财务信息
                                px_asset_deprn_rec     => l_asset_deprn_rec, --资产折旧信息
                                px_asset_dist_tbl      => l_asset_dist_tbl, --资产分布信息
                                px_inv_tbl             => l_inv_tbl, --资产发票
                                px_inv_rate_tbl        => l_inv_rate_tbl --对利率表为资产为医学研究委员会的发票
                                );
    apps.fnd_file.put_line(apps.fnd_file.log,
                           'l_return_status : ' || l_return_status);
    apps.fnd_file.put_line(apps.fnd_file.log,
                           'fnd_api.g_ret_sts_success : ' ||
                           fnd_api.g_ret_sts_success); ---G_RET_STS_SUCCESS   CONSTANT    VARCHAR2(1) :=  'S';
    if (l_return_status <> fnd_api.g_ret_sts_success) then
      apps.fnd_file.put_line(apps.fnd_file.log, 'FAILED');
      apps.fnd_file.put_line(apps.fnd_file.log, 'error: ' || sqlerrm);
      l_mesg_count := fnd_msg_pub.count_msg;

      if l_mesg_count > 0 then

        l_mesg := chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_first,
                                                    fnd_api.g_false),
                                    1,
                                    512);
        for i in 1 .. 2
        loop
          l_mesg := l_mesg || chr(10) || substr(fnd_msg_pub.get(fnd_msg_pub.g_next,
                                                                fnd_api.g_false),
                                                1,
                                                512);

        end loop;

        fnd_msg_pub.delete_msg();

        l_mesg_len := length(l_mesg);
        for i in 1 .. ceil(l_mesg_len / 255)
        loop
          apps.fnd_file.put_line(apps.fnd_file.log,
                                 substr(l_mesg, ((i * 255) - 254), 255));
        end loop;
      end if;

    else
      apps.fnd_file.put_line(apps.fnd_file.log, 'SUCCESS');
      apps.fnd_file.put_line(apps.fnd_file.log,
                             'THID: ' ||
                             to_char(l_trans_rec.transaction_header_id));
      apps.fnd_file.put_line(apps.fnd_file.log,
                             'ASSET_ID: ' ||
                             to_char(l_asset_hdr_rec.asset_id));
      apps.fnd_file.put_line(apps.fnd_file.log,
                             'ASSET_NUMBER: ' ||
                             l_asset_desc_rec.asset_number);

    end if;
 end loop; 
  end auto_import_info_p_2 ;

在测试的过程中因为改了一个日期,导致date in service 与transaction date 不一致,导致程序在运行的时候出现如下错误:
l_return_status : E
fnd_api.g_ret_sts_success : S
FAILED
error: ORA-0000: normal, successful completion

Error checking transaction dates for asset_id 3985.  
找到错误原因,调试后,目前基本上导入成功。明天还需要进一步优化脚本。

代码菜鸟,慢慢练习吧。
参考文档:Oracle Assets Additions API Documentation Supplement (Doc ID 232070.1)

MPORTANT
This document is relevant for 11i. There are minor changes in the code in R12. For R12 documentation and examples, please reference the R12 User Guide.

Thank you for using My Oracle Support!

Oracle Assets Additions API


You can use the Additions API to add assets directly via PL/SQL rather than through the normal Oracle Assets interfaces. The Additions API uses the FA_ADDITION_PUB.DO_ADDITION procedure. You can use this API if you have a custom interface that makes it difficult to use with the existing asset additions interfaces in Oracle Assets.

Oracle Assets also allows you to add assets using any of the following methods:

  • QuickAdditions: You use the QuickAdditions process to quickly enter ordinary assets when you must enter them manually. You can enter minimal information in the QuickAdditions window, and the remaining asset information defaults from the asset category, book, and the date placed in service.
  • Detail Additions: You use the Detail Additions process to manually add complex assets which the QuickAdditions process does not handle, for example, assets that have a salvage value or assets with more than one assignments.
  • Mass Additions: You use the Mass Additions process to add assets automatically from an external source. Create assets from one or more invoice distribution lines in Oracle Payables, CIP asset lines in Oracle Projects, asset information from another assets system, or information from any other feeder system using the interface.

CIP Assets

If you have checked the Allow CIP Assets check box on the Book Controls window of a tax book, when you add CIP assets using the Additions API, the API automatically adds those CIP assets to that tax book at the same time that they are added to the corporate book.

 

MRC

If you have set up Multiple Reporting Currencies (MRC), when you add assets using the Additions API, the API automatically copies the assets to the reporting books.

 

Passing Null Values

You must pass special values in order to null the values of BONUS_RULE, CEILING_NAME, or GROUP_ASSET_ID. If you pass a NULL value directly for these values, the API will automatically default to the value setup for the category. The table below shows the value that must be passed for the field value to be NULL.

 

Field Value to Pass for NULL Field
BONUS_RULE FND_API.G_MISS_CHAR
CEILING_NAME FND_API.G_MISS_CHAR
GROUP_ASSET_ID FND_API.G_MISS_NUM

See Also

Additions API Description

Sample Script: Using the Additions API via Invoices

Sample Script: Using the Additions API with No Invoices

 

Additions API Description

The Additions API procedure is called: FA_ADDITION_PUB.DO_ADDITION ().

The following table provides the arguments, types, value, and descriptions of the elements of the FA_ADDITION_PUB.DO_ADDITION procedure.

Each argument has a prefix of P, X, or PX. These prefixes mean the following:

 

  • P - indicates an In argument
  • X - indicates an Out argument
  • PX - indicates an argument that is both In and Out
Argument Type Value Description
P_API_VERSION NUMBER Internal use only Version of the API in use.
P_INIT_MSG_LIST VARCHAR2(1) FND_API.G_TRUE - Initialize the message stack
FND_API.G_FALSE - Do not initialize the message stack (Default)
Determines whether the messages stack should be initialized and cleared.
P_COMMIT VARCHAR2(1) FND_API.G_TRUE - Commit automatically
FND_API.G_FALSE - Do not commit automatically (Default)
Flag on whether to commit asset after processed.
P_VALIDATION_LEVEL NUMBER FND_API.G_VALID_ LEVEL_NONE - Lowest level of validation possible for a transaction
FND_API.G_VALID_ LEVEL_FULL - Highest level of validation possible for a transaction (Default)
Check on whether the API should do the validation for the asset.
X_RETURN_STATUS VARCHAR2(1) FND_API.G_RET_STS_ SUCCESS - Addition was a success
FND_API.G_RET_STS_ ERROR - Addition failed FND_API.G_RET_STS_ UNEXP_ERROR - Unexpected error
Determines whether or not the API completed successfully.
X_MSG_COUNT NUMBER   Number of messages on the message stack.
X_MSG_DATA VARCHAR2(1024)   Message stack.
P_CALLING_FN VARCHAR2(30)   Function calling the API
PX_TRANS_REC FA_API_TYPES. TRANS_REC_TYPE   Describes the transaction taking place.
PX_DIST_TRANS_REC FA_API_TYPES. DIST_TRANS_REC_ TYPE   Describes the distribution of the transaction.
PX_ASSET_HDR_REC FA_API_TYPES. ASSET_HDR_REC_ TYPE   Unique identifiers of the asset being added.
PX_ASSET_DESC_REC FA_API_TYPES. ASSET_DESC_REC_ TYPE   Description of the asset.
PX_ASSET_TYPE_REC FA_API_TYPES. ASSET_TYPE_REC_ TYPE   Asset type of the asset.
PX_ASSET_CAT_REC FA_API_TYPES. ASSET_CAT_REC_ TYPE   Category information of the asset.
PX_ASSET_HIERARCHY_REC FA_API_TYPES. ASSET_HIERARCHY_REC_TYPE   Hierarchy information of the asset.
PX_ASSET_FIN_REC FA_API_TYPES. ASSET_FIN_REC_ TYPE   Financial information of the asset.
PX_ASSET_DEPRN_REC FA_API_TYPES. ASSET_DEPRN_ REC_TYPE   Depreciation information of the asset.
PX_ASSET_DIST_TBL FA_API_TYPES. ASSET_DIST_TBL_ TYPE   Distribution information of the asset.
PX_INV_TBL FA_API_TYPES. INV_TBL_TYPE   Invoices for the asset.
PX_INV_RATE_TBL FA_API_TYPES. INV_RATE_TBL_ TYPE   Table of rates for the invoices for the asset for MRC.
Table 1 - 1. (Page 2 of 2)

 

TRANS_REC_TYPE Transaction Structure

The TRANS_REC_TYPE transaction structure contains information about the transaction, such as the transaction header ID and the transaction type code. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
TRANSACTION_HEADER_ ID Internal use only NUMBER(15) Optional OUT parameter
TRANSACTION_DATE_ ENTERED Optional DATE Must be the same as the date placed in service, which it defaults to.
TRANSACTION_NAME Optional VARCHAR2(20) Description of the transaction.
MASS_REFERENCE_ID Optional NUMBER(15) Identifies concurrent request that invoked the mass transaction.
TRANSACTION_SUBTYPE Optional  
VARCHAR2(9)
AMORTIZED - Amortize NBV addition
NULL - Regular addition
AMORTIZATION_START_ DATE Optional DATE Amortization start date
CALLING_INTERFACE Optional VARCHAR2(30) Defaults to CUSTOM
DESC_FLEX Optional DESC_FLEX_ REC Descriptive flexfield segment
WHO_INFO Required STANDARD_ WHO_REC Standard Who columns
 

ASSET_HDR_REC_TYPE Asset Structure

The ASSET_HDR_REC_TYPE asset structure contains unique identification information for a given asset, such as the asset ID and book type code. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
ASSET_ID Internal use only for addition to Corporate book. Required for addition to tax book for asset already existing in Corporate book. For a tax addition, only the TRANS_REC_TYPE and the ASSET_HDR_REC_TYPE attributes are needed. The values in the other structures such as ASSET_DESC_REC_TYPE, ASSET_FIN_REC_TYPE, ASSET_TYPE_REC_TYPE, ASSET_CAT_REC_TYPE, etc, will default form the values of the Corporate book, NUMBER(15) Optional OUT parameter
BOOK_TYPE_CODE Required VARCHAR2(15) Book name
 

ASSET_DESC_REC_TYPE Asset Structure

The ASSET_DESC_REC_TYPE asset structure contains descriptive information about the asset, such as the tag number, serial number, and manufacturer name. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
ASSET_NUMBER Optional VARCHAR2(15) Defaults to asset_id. Optional OUT parameter.
DESCRIPTION Required VARCHAR2(80) Description of the asset.
TAG_NUMBER Optional VARCHAR2(15) Tag number of the asset.
SERIAL_NUMBER Optional VARCHAR2(35) Serial number of the asset.
ASSET_KEY_CCID Optional NUMBER(15) Identifies an asset key flexfield combination for the asset.
PARENT_ASSET_ID Optional NUMBER(15) Identifies a parent asset for subcomponents.
STATUS Optional VARCHAR2(150) Describes the status of the asset. (CRL Assets only)
MANUFACTURER_NAME Optional VARCHAR2(30) Name of the manufacturer.
MODEL_NUMBER Optional VARCHAR2(40) Model number of the asset.
WARRANTY_ID Optional NUMBER(15) Warranty identification number.
LEASE_ID Optional NUMBER(15) Lease identification number.
IN_USE_FLAG Optional VARCHAR2(3) Indicates whether the asset is in use. YES - In use (default)
NO - Not in use
INVENTORIAL Optional VARCHAR2(3) YES - Include in physical inventory
NO - Do not include in physical inventory Defaults to value in category
PROPERTY_TYPE_CODE Optional VARCHAR2(10) Indicates the property type. Defaults to value in category
PROPERTY_1245_1250_ CODE Optional VARCHAR2(4) 1245 - Personal
1250 - Real Defaults to value in category
OWNED_LEASED Optional VARCHAR2(15) OWNED - Owned (Default)
LEASED - Leased Defaults to value in category
NEW_USED Optional VARCHAR2(4) NEW - New (Default)
USED - Used
LEASE_DESC_FLEX Optional DESC_FLEX_ REC_TYPE Lease descriptive flexfield segments.
GLOBAL_DESC_FLEX Optional DESC_FLEX_ REC_TYPE Global descriptive flexfield segments.
Table 1 - 2. (Page 2 of 2)

ASSET_TYPE_REC_TYPE Asset Structure

The ASSET_TYPE_REC_TYPE asset structure contains information about the asset type: CIP, capitalized, or expensed. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
ASSET_TYPE Required VARCHAR2(15) CAPITALIZED - Capitalized asset (default)
CIP - CIP asset
EXPENSED - Expensed asset
 

ASSET_CAT_REC_TYPE Asset Structure

The ASSET_CAT_REC_TYPE asset structure contains information about the asset category, such as the category ID and the category descriptive flexfield. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
CATEGORY_ID Required NUMBER(15) Identifies whether the asset is CIP, Capitalized, or Expensed.
CATEGORY_DESC_FLEX Optional DESC_FLEX_ REC_TYPE Category descriptive flexfield segments.
Table 1 - 3. (Page 1 of 1)

ASSET_HIERARCHY_REC_TYPE Asset Structure

The ASSET_HIERARCHY_REC_TYPE asset structure contains information about whether the asset belongs to an asset hierarchy. This asset structure applies only to CRL Assets. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
PARENT_HIERARCHY_ID Optional NUMBER(15) Identifies the hierarchy to which the asset belongs. (CRL assets)
Table 1 - 4. (Page 1 of 1)

ASSET_FIN_REC_TYPE Asset Structure

The ASSET_FIN_REC_TYPE asset structure contains financial information for a given asset. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
DATE_PLACED_IN_ SERVICE Required (if no invoices were populated) DATE Date the asset was placed in service.
DEPRN_METHOD_CODE Optional VARCHAR2(12) The name of the depreciation menthod. Defaults to value in category
LIFE_IN_MONTHS Optional NUMBER(4) Life of the asset in total months. Defaults to value in category
COST Required (if no invoices were populated) NUMBER Current cost of the asset.
ORIGINAL_COST Optional NUMBER Original cost of the asset. Defaults to cost.
SALVAGE_VALUE Optional NUMBER Asset salvage value. Defaults to value in category
PRORATE_CONVENTION_ CODE Optional VARCHAR2(10) Depreciation prorate convention. Defaults to value in category
DEPRECIATE_FLAG Required  
VARCHAR2(3)
Indicates whether the asset is depreciating. YES - Asset is depreciating
NO - Asset is not depreciating
ITC_AMOUNT_ID Optional NUMBER(15) Identifies the ITC rate.
BASIC_RATE Optional NUMBER Defaults to value in category
ADJUSTED_RATE Optional NUMBER Defaults to value in category
BONUS_RULE Optional NUMBER Bonus rule for an asset. Defaults to value in category. If a defualt value is defined, and you want the value to be NULL, you must pass FND_API.G_MISS_CHAR to the Addition API.
CEILING_NAME Optional VARCHAR2(30) Identifies a deprecation ceiling to be used in calculating deprecation. Defaults to value in category. If a defualt value is defined, and you want the value to be NULL, you must pass FND_API.G_MISS_CHAR to the Addition API.
PRODUCTION_CAPACITY Optional NUMBER Capacity of a units of production asset. Defaults to value in category
UNIT_OF_MEASURE Optional VARCHAR2(25) Unit of measure of a units of production asset. Defaults to value in category.
REVAL_CEILING Optional NUMBER Upper limit for revaluing asset cost.
UNREVALUED_COST Optional NUMBER Cost without regard to any revaluation.
SHORT_FISCAL_YEAR_ FLAG Optional  
VARCHAR2(3)
YES - Asset is in a short fiscal year
NO - Asset is not in a short fiscal year
CONVERSION_DATE Optional DATE Date short tax year asset added to the acquiring company.
ORIG_DEPRN_START_DATE Optional DATE Date short tax year asset begin depreciating in the acquired company's books.
GROUP_ASSET_ID Optional NUMBER(15) Group asset identification number. (CRL Asset). If a defualt value is defined, and you want the value to be NULL, you must pass FND_API.G_MISS_NUM to the Addition API.
GLOBAL_ATTRIBUTE1-20 Optional VARCHAR2(150) Reserved for country specific functionality.
GLOBAL_ATTRIBUTE_ CATEGORY Optional VARCHAR2(30) Reserved for country specific functionality.
Table 1 - 5. (Page 2 of 2)

ASSET_DEPRN_REC_TYPE Asset Structure

The ASSET_DEPRN_REC_TYPE asset structure contains depreciation information for a given asset. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
YTD_DEPRN Optional NUMBER Year-to-date depreciation expense.
DEPRN_RESERVE Optional NUMBER Total depreciation taken since the beginning of the asset's life.
REVAL_DEPRN_RESERVE Optional NUMBER For a period in which this asset was revalued: The value is the change in net book value due to revaluation of asset cost, and sometimes also the revaluation of depreciation reserve. For all other periods: The value is the revaluation reserve amount after depreciation is run.
Table 1 - 6. (Page 1 of 1)

ASSET_DIST_REC_TYPE Asset Structure

The ASSET_DIST_REC_TYPE asset structure contains information that represents a single source or destination distribution line. All the ASSET_DIST_REC_TYPE records comprising a single transfer transaction are contained in the ASSET_DIST_TBL_TYPE table. At least one source line and at least one destination line are required for each transfer transaction. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
UNITS_ASSIGNED Required NUMBER Number of units assigned to the distribution.
ASSIGNED_TO Optional NUMBER(15) Employee identification number.
EXPENSE_CCID Required NUMBER(15) Depreciation expense account identification number.
LOCATION_CCID Required NUMBER(15) Location flexfield identification number.
Table 1 - 7. (Page 2 of 2)

INV_REC_TYPE Invoice Structure

The INV_REC_TYPE invoice structure contains invoice information for a single invoice associated with an asset. The INV_TBL_TYPE is a table of INV_REC_TYPE records. These records contain all invoices that have been applied to the asset. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
FIXED_ASSETS_COST Required (only when the record is populated) NUMBER Cost of asset in Oracle Assets.
PO_VENDOR_ID Optional NUMBER(15) Supplier identification number.
PO_NUMBER Optional VARCHAR2(20) Purchase order number.
INVOICE_NUMBER Optional VARCHAR2(50) Invoice number.
PAYABLES_BATCH_NAME Optional VARCHAR2(50) Name of the payables batch that contained this invoice.
PAYABLES_CODE_ COMBINATION_ID Optional NUMBER(15) Clearing account number to which this line was posted in accounts payable.
FEEDER_SYSTEM_NAME Optional VARCHAR2(40) Name of the feeder system that created the FA_MASS_ADDITIONS row.
CREATE_BATCH_DATE Optional DATE Date the mass additions batch was created for this row.
CREATE_BATCH_ID Optional NUMBER(15) Mass additions create request identification number for this row.
INVOICE_DATE Optional DATE Invoice date from accounts payable.
PAYABLES_COST Optional NUMBER Invoice cost from accounts payable.
POST_BATCH_ID Optional NUMBER(15) Mass Additions Post request identification number for this row.
INVOICE_ID Optional NUMBER(15) Invoice identification number.
AP_DISTRIBUTION_LINE_ NUMBER Optional NUMBER(15) Distribution line identification number.
PAYABLES_UNITS Optional NUMBER Units from AP_INVOICE_ DISTRIBUTIONS row.
DESCRIPTION Optional VARCHAR2(80) Invoice line description.
DELETED_FLAG Optional VARCHAR2(3) Indicates whether this row has been deleted in the CIP Adjustments form. Defaults to NO, which it should be for all addition transactions.
PROJECT_ASSET_LINE_ID Optional NUMBER(15) Identifier of the summarized asst cost line transferred from Oracle Projects to create this line.
PROJECT_ID Optional NUMBER(15) Identifier of the project from which the costs are collected, summarized, and transferred from Oracle Projects.
TASK_ID Optional NUMBER(15) Identifier of the task from which costs are collected, summarized, and transferred from Oracle Projects. This column is populated only if the costs were summarized by task.
ATTRIBUTE1 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE2 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE3 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE4 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE5 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE6 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE7 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE8 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE9 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE10 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE11 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE12 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE13 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE14 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE15 Optional VARCHAR2(150) Descriptive flexfield segment.
ATTRIBUTE_CATEGORY_ CODE Optional VARCHAR2(30) Descriptive flexfield structure defining column.
INV_INDICATOR Required (only when this record is populated and using MRC and INV_RATE_REC) NUMBER Join to INV_RATE_REC. YTD_DEPRN NUMBER Year-to-date deprecation expense.
YTD_DEPRN Optional NUMBER Year-to-date deprecation expense.
DEPRN_RESERVE Optional NUMBER Total deprecation taken since beginning of asset life.
BONUS_YTD_DEPRN Optional NUMBER Bonus year-to-date depreciation expense.
BONUS_DEPRN_RESERVE Optional NUMBER Total bonus depreciation taken since beginning of asset life.
REVAL_AMORTIZATION_ BASIS Optional NUMBER Revaluation reserve used in calculating amortization of revaluation reserve. Value is updated only when the asset is revalued or has an amortized adjustment.
REVAL_YTD_DEPRN Optional NUMBER Year-to-date deprecation expense due to revaluation.
REVAL_DEPRN_RESERVE Optional NUMBER For a period in which this asset was revalued: Change in net book value due to revaluation of asset cost and sometimes also revaluation of depreciation reserve. For all other periods: Revaluation reserve amount after depreciation run.
Table 1 - 8. (Page 4 of 4)

INV_RATE_REC_TYPE Invoice Structure

The INV_RATE_REC_TYPE invoice structure contains rate information for an invoice. Each INV_RATE_REC_TYPE record contains one conversion rate. The INV_RATE_TBL_TYPE is a table of INV_RATE_REC_TYPE records. This table is used by MRC reporting books to determine the conversion rates for an asset. The following table shows type and value information for each argument.

 

Argument Required / Optional Type Value
INV_INDICATOR Optional - When using MRC, this value and record should generally be populated. If it is not populated, the daily rate is derived based on the transaction date. NUMBER Join to INV_REC.
SET_OF_BOOKS_ID Required - Required when using MRC, and only needed when the record is populated. NUMBER(15) Set of books identification number for GL set of books.
EXCHANGE_RATE Required - Required when using MRC, and only needed when the record is populated. NUMBER Exchange rate for the currency.
Table 1 - 9. (Page 1 of 1)

Sample Script: Using the Additions API via Invoices

The following sample script shows how you can use the Additions API via invoices:


set serveroutput on

declare

l_trans_rec FA_API_TYPES.trans_rec_type;
l_dist_trans_rec FA_API_TYPES.trans_rec_type;
l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
l_inv_tbl FA_API_TYPES.inv_tbl_type;
l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;
l_inv_rec FA_API_TYPES.inv_rec_type;
l_return_status VARCHAR2(1);
l_mesg_count number := 0;
l_mesg_len number;
l_mesg varchar2(4000);

begin

fnd_profile.put('PRINT_DEBUG', 'Y');
dbms_output.enable(10000000);

FA_SRVR_MSG.Init_Server_Message;
FA_DEBUG_PKG.Initialize;

-- desc info
l_asset_desc_rec.description := 'Dell computer';
l_asset_desc_rec.asset_key_ccid := 2;

-- cat info *** NEED TO CHANGE BASED ON CATEGORY SETUP FOR YOUR BOOK ***
l_asset_cat_rec.category_id := 21;

--type info
l_asset_type_rec.asset_type := 'CAPITALIZED';

-- invoice info
l_inv_rec.fixed_assets_cost := 2500;
l_inv_rec.deleted_flag := 'NO';
l_inv_rec.description := l_asset_desc_rec.description;
l_inv_rec.unrevalued_cost := 5555;
l_inv_rec.create_batch_id := 1000;
l_inv_rec.payables_code_combination_id := 13528;
l_inv_rec.feeder_system_name := 'ACK';
l_inv_rec.payables_cost := 5555;
l_inv_rec.payables_units := 1;
l_inv_rec.po_vendor_id := 1;
l_inv_rec.inv_indicator := 1;

l_inv_tbl (1) := l_inv_rec;

-- rate info
l_inv_rate_tbl(1).inv_indicator := 1;
-- *** NEED TO CHANGE BASED ON SOB_ID OF REPORTING BOOKS ***
l_inv_rate_tbl(1).set_of_books_id := 658; -- first reporting book
l_inv_rate_tbl(1).exchange_rate := .532;

l_inv_rate_tbl(2).inv_indicator := 1;
l_inv_rate_tbl(2).set_of_books_id := 659; -- second reporting book
l_inv_rate_tbl(2).exchange_rate := .233;

-- fin info
l_asset_fin_rec.date_placed_in_service := '&DPIS';
l_asset_fin_rec.depreciate_flag := 'YES';

-- deprn info
l_asset_deprn_rec.ytd_deprn := 0;
l_asset_deprn_rec.deprn_reserve := 0;
l_asset_deprn_rec.bonus_ytd_deprn := 0;
l_asset_deprn_rec.bonus_deprn_reserve := 0;

-- book / trans info
l_asset_hdr_rec.book_type_code := '&book';
l_trans_rec.transaction_date_entered := l_asset_fin_rec.date_placed_in_service;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;


-- l_asset_dist_tbl := fa_API_TYPES.asset_dist_tbl_type (null);

l_asset_dist_rec.units_assigned := 1;
l_asset_dist_rec.expense_ccid := 12975;
l_asset_dist_rec.location_ccid := 2;
l_asset_dist_rec.assigned_to := null;
l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
l_asset_dist_tbl(1) := l_asset_dist_rec;


--l_asset_desc_rec.asset_number :=
--l_asset_desc_rec.property_type_code :=
--l_asset_desc_rec.property_1245_1250_code :=
--l_asset_desc_rec.in_use_flag :=
--l_asset_desc_rec.owned_leased :=
--l_asset_desc_rec.new_used :=

--l_asset_desc_rec.inventorial :=
--l_asset_desc_rec.manufacturer_name :=
--l_asset_desc_rec.serial_number :=
--l_asset_desc_rec.model_number :=
--l_asset_desc_rec.tag_number :=
--l_asset_desc_rec.parent_asset_id :=
--l_asset_desc_rec.warranty_id :=
--l_asset_desc_rec.lease_id :=

-- call the api
fa_addition_pub.do_addition (p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => null,
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
--l_asset_desc_rec.inventorial :=
--l_asset_desc_rec.manufacturer_name :=
--l_asset_desc_rec.serial_number :=
--l_asset_desc_rec.model_number :=
--l_asset_desc_rec.tag_number :=
--l_asset_desc_rec.parent_asset_id :=
--l_asset_desc_rec.warranty_id :=
--l_asset_desc_rec.lease_id :=

-- call the api fa_addition_pub.do_addition
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => null,
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec => l_asset_desc_rec,
px_asset_type_rec => l_asset_type_rec,
px_asset_cat_rec => l_asset_cat_rec,
px_asset_hierarchy_rec => l_asset_hierarchy_rec,
px_asset_fin_rec => l_asset_fin_rec,
px_asset_deprn_rec => l_asset_deprn_rec,
px_asset_dist_tbl => l_asset_dist_tbl,
px_inv_tbl => l_inv_tbl,
px_inv_rate_tbl => l_inv_rate_tbl
);

dbms_output.put_line(l_return_status);

if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then

dbms_output.put_line('FAILED');
-- dbms_output.put_line(to_char(sqlerr));
dbms_output.put_line(sqlerrm);
l_mesg_count := fnd_msg_pub.count_msg;

if l_mesg_count > 0 then

l_mesg := chr(10) || substr(fnd_msg_pub.get
(fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
1, 512);

for i in 1..2 loop -- (l_mesg_count - 1) loop
l_mesg := l_mesg || chr(10) ||
substr(fnd_msg_pub.get
(fnd_msg_pub.G_NEXT,
fnd_api.G_FALSE), 1, 512);
end loop;

fnd_msg_pub.delete_msg();

l_mesg_len := length(l_mesg);
for i in 1..ceil(l_mesg_len/255) loop
dbms_output.put_line(substr(l_mesg, ((i*255)-254), 255));
end loop;
end if;

else

dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);

end if;

end;
/

 

Sample Script: Using the Additions API with No Invoices

The following sample script shows how you can use the Additions API to test a manual change, when no invoice information is used:

set serveroutput on

declare

l_trans_rec FA_API_TYPES.trans_rec_type;
l_dist_trans_rec FA_API_TYPES.trans_rec_type;
l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
l_inv_tbl FA_API_TYPES.inv_tbl_type;
l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;

l_return_status VARCHAR2(1);
l_mesg_count number := 0;
l_mesg_len number;
l_mesg varchar2(4000);

begin

fnd_profile.put('PRINT_DEBUG', 'Y');
dbms_output.enable(1000000);

FA_SRVR_MSG.Init_Server_Message;
FA_DEBUG_PKG.Initialize;

-- desc info
l_asset_desc_rec.description := 'Dell computer';
l_asset_desc_rec.asset_key_ccid := 2;

-- cat info *** NEED TO CHANGE BASED ON CATEGORY SETUP FOR YOUR BOOK ***
l_asset_cat_rec.category_id := 21;

--type info
l_asset_type_rec.asset_type := 'CAPITALIZED';

-- fin info
l_asset_fin_rec.cost := 50000;
l_asset_fin_rec.date_placed_in_service := '&DPIS';
l_asset_fin_rec.depreciate_flag := 'YES';
l_asset_fin_rec.deprn_method_code := 'HS-FORM NBV';
l_asset_fin_rec.life_in_months := 72;

-- deprn info
l_asset_deprn_rec.ytd_deprn := 500;
l_asset_deprn_rec.deprn_reserve := 500;
l_asset_deprn_rec.bonus_ytd_deprn := 0;
l_asset_deprn_rec.bonus_deprn_reserve := 0;


-- book / trans info
l_asset_hdr_rec.book_type_code := '&book';
l_trans_rec.transaction_date_entered := l_asset_fin_rec.date_placed_in_service;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;

-- distribution info

l_asset_dist_rec.units_assigned := 1;
l_asset_dist_rec.expense_ccid := 12975;
l_asset_dist_rec.location_ccid := 2;
l_asset_dist_rec.assigned_to := null;
l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
l_asset_dist_tbl(1) := l_asset_dist_rec;


--l_asset_desc_rec.asset_number :=
--l_asset_desc_rec.property_type_code := 'REAL';
--l_asset_desc_rec.property_1245_1250_code := '1245';
--l_asset_desc_rec.in_use_flag := 'YES';
--l_asset_desc_rec.owned_leased := 'OWNED';
--l_asset_desc_rec.new_used := 'NEW';
--l_asset_desc_rec.inventorial := 'YES';
--l_asset_desc_rec.manufacturer_name :=
--l_asset_desc_rec.serial_number :=
--l_asset_desc_rec.model_number :=
--l_asset_desc_rec.tag_number :=
--l_asset_desc_rec.parent_asset_id :=
--l_asset_desc_rec.warranty_id :=
--l_asset_desc_rec.lease_id :=

-- For tax addition, will need existing asset_id
--l_asset_hdr_rec.asset_id :=

--l_asset_fin_rec.salvage_value :=
--l_asset_fin_rec.unrevalued_cost :=
--l_asset_fin_rec.short_fiscal_year_flag :=
--l_asset_fin_rec.conversion_date :=
--l_asset_fin_rec.orig_deprn_start_date :=
--l_asset_fin_rec.unit_of_measure :=

--l_asset_deprn_rec.reval_deprn_reserve :=
--l_asset_deprn_rec.reval_amortization_basis :=

-- Accept amort start date for amortize NBV additions
-- l_trans_rec.amortization_start_date :=
-- to_date('&amort_start_date', 'DD-MON-YYYY');

-- call the api
fa_addition_pub.do_addition
(p_api_version => 1.0,
p_init_msg_list => FND_API.G_FALSE,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => l_return_status,
x_msg_count => l_mesg_count,
x_msg_data => l_mesg,
p_calling_fn => null,
px_trans_rec => l_trans_rec,
px_dist_trans_rec => l_dist_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec => l_asset_desc_rec,
px_asset_type_rec => l_asset_type_rec,
px_asset_cat_rec => l_asset_cat_rec,
px_asset_hierarchy_rec => l_asset_hierarchy_rec,
px_asset_fin_rec => l_asset_fin_rec,
px_asset_deprn_rec => l_asset_deprn_rec,
px_asset_dist_tbl => l_asset_dist_tbl,
px_inv_tbl => l_inv_tbl,
px_inv_rate_tbl => l_inv_rate_tbl
);

dbms_output.put_line(l_return_status);

if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then 
dbms_output.put_line('FAILED');
-- dbms_output.put_line(to_char(sqlerr));
dbms_output.put_line(sqlerrm);
l_mesg_count := fnd_msg_pub.count_msg;

if l_mesg_count > 0 then

l_mesg := chr(10) || substr(fnd_msg_pub.get
(fnd_msg_pub.G_FIRST, fnd_api.G_FALSE),
1, 512);
for i in 1..2 loop -- (l_mesg_count - 1) loop
l_mesg := l_mesg || chr(10) ||
substr(fnd_msg_pub.get
(fnd_msg_pub.G_NEXT,
fnd_api.G_FALSE), 1, 512);

end loop;

fnd_msg_pub.delete_msg();

l_mesg_len := length(l_mesg);
for i in 1..ceil(l_mesg_len/255) loop
dbms_output.put_line(substr(l_mesg, ((i*255)-254), 255));
end loop;
end if;

else

dbms_output.put_line('SUCCESS');
dbms_output.put_line('THID' || to_char(l_trans_rec.transaction_header_id));
dbms_output.put_line('ASSET_ID' || to_char(l_asset_hdr_rec.asset_id));
dbms_output.put_line('ASSET_NUMBER' || l_asset_desc_rec.asset_number);
end if;

end;

/



0

阅读 评论 收藏 转载 喜欢 打印举报
  • 评论加载中,请稍候...
发评论

    发评论

    以上网友发言只代表其个人观点,不代表新浪网的观点或立场。

      

    新浪BLOG意见反馈留言板 电话:4006900000 提示音后按1键(按当地市话标准计费) 欢迎批评指正

    新浪简介 | About Sina | 广告服务 | 联系我们 | 招聘信息 | 网站律师 | SINA English | 会员注册 | 产品答疑

    新浪公司 版权所有