Oracle Cost Management Client Extensions
Client Extensions
Oracle Cost Management provides flexible cost processing. However, many companies have business requirements that are unique to their company or country. To address these unique requirements, Cost Management provides subprograms which enable you to extend the functionality of the product to implement and automate company-specific business rules. In other words, subprograms provide extensibility by letting you tailor the PL/SQL language to suit your needs. For this reason these subprograms are commonly known as client extensions.
Note: Extensions are applicable to all organizations unless the client extension is designed to restrict its use.
The following tables list the Cost Management client extensions and their predefined template function files. The template function files are stored in the Cost Management patch/115/sq directory.
Note: Accounting Entry Extensions are obsolete for Release 12. If you have implemented Accounting Entry Extensions and are upgrading to Release 12, then you must implement the logic in those extensions by customizing the base Costing SLA rules.
| Standard Cost Client Extensions | Package Specification File | Package Body File |
|---|---|---|
| Account Generation Extension | CSTSCHKS.pls | CSTSCHKB.pls |
In Average/FIFO/LIFO costing organizations, you can implement Transaction Cost, Account Generation, and Cost Processing Cutoff Date client extensions. In standard costing, you can only implement the Account Generation extension.
To define company specific rules using client extensions, design and write these rules using PL/SQL functions; these functions are called during specific points in the normal processing flow of Cost Management.
These functions that you write are extensions rather than customizations because they are supported features within the product and are easily upgradable between releases of the product. Customizations are changes made to the base product and are not supported nor easily upgraded.
Important: You are responsible for the support and upgrade of the logic within the functions that you write, which may be impacted by changes between releases of Oracle E-Business Suite products.
This essay provides a general overview of client extensions and their implementation as well as specific information about the client extensions available in Cost Management.
Related Topics
Implementing Client Extensions
Determining Your Business Needs
Writing PL/SQL Procedures/Functions
Types of Client Extensions
You can implement the following extensions to help you address your company’s business requirements.
Transaction Cost Extension
You can use this extension to reset the elemental costs of transactions in Average/FIFO/LIFO costing organizations.
Account Generation Extension
You can use this extension to provide alternative accounts per your requirements for account distributions. These accounts are picked and used by the cost processor when the cost processor performs accounting distributions. You can use this extension in all costing organizations.
Cost Processing Cutoff Date Extension
You can use this extension to process only transactions up through a user-specified date. You can only use this extension in Average/FIFO/LIFO costing organizations.
Related Topics
Costs Processing Cutoff Date Extension
Implementing Client Extensions
The implementation of client extensions primarily consists of the following three steps:
-
Determining your business requirements
-
Designing client extension logic
-
Modifying appropriate template function files using PL/SQL
Each of these steps requires a specific expertise. The analysis and design portions require an implementation team member who is functionally knowledgeable about the company specific business rules, the implementation of Cost Management for your company, and the conceptual flow of the client extensions. The PL/SQL coding portion requires an implementation team member who is technically knowledgeable with PL/SQL and the Cost Management data structures.
Determining Your Business Requirements
The first step of implementing client extensions is to determine if you need to use the client extensions. You can do this by following these steps, which are part of the process of any Cost Management implementation:
-
Clearly define and document your company specific business requirements and rules.
-
Determine if these business rules are handled by the normal functionality of Cost Management.
-
For those business rules not handled by the normal functionality, review the client extensions and determine if a client extension can help address the specific business rules, based on your documented business requirements.
Designing Client Extensions
Designing your client extension is the most significant part of implementing client extensions. This design cycle involves the following aspects:
-
Understand the appropriate client extension, including its intended purpose, its processing flow, the predefined place that it is called, and its input values.
-
Define and document the requirements and logic of your business rules under all possible conditions. This logic includes the required inputs, the calculations performed, and the corresponding outputs.
-
Determine the data elements required to drive your rules and how you will select or derive each of the required elements. Define additional implementation data and document additional business functions of using the system based on the requirements of your business rules.
Determining Data Elements
Predefined Parameters
Cost Management provides predefined parameters for each client extension. The program which calls and executes the client extension passes in values for the predefined parameters, which define the context of what transaction is being processed.
Derived Information
You can derive additional information from the predefined parameters. For example, a client extension may have a predefined parameter of TRANSACTION_ID, which is the identifier of the transaction. Your business rule needs transaction type, so you derive the transaction type from the TRANSACTION_ID.
Example of Designing a Client Extension
Let's use the earlier extension example to illustrate these design steps.
-
After studying client extensions, you have decided to use the account generation extension to implement the following policy:
You should only use product line accounts for subinventory transfers
-
You define the logic for the account generation extension as:
IF transaction is (subinventory_transfer) THEN Find out Product Line Account for this item, organization, and subinventory RETURN Accounts
-
Determine what input data and output data is required. Per the above example, the data required and its source is listed in the following table:
| Input | Type of Parameter |
|---|---|
| Transaction identifier | Predefined |
| Organization identifier | Predefined |
| Inventory item identifier | Derived |
| Subinventory Code | Predefined |
| Transaction Action | Derived |
The SQL script for capturing the derived data listed in the above table is as follows:
SELECT Transaction_action, inventory_item
identifier
FROM mtl_material_transactions
WHERE transaction identifier = current transaction
The following outputs must be derived from these inputs:
SELECT product line accounts FROM mtl_category_accounts WHERE inventory_item_id = current item identifier AND organization_id = current organization identifier AND subinventory_code = subinventory code
Writing PL/SQL Functions/Procedures
To help you to write PL/SQL functions for client extensions, we first provide you with a brief technical background of PL/SQL functions. Then, we provide you with information on how to use predefined functions and parameters in writing your own functions. We recommend that you read the PL/SQL User’s Guide and Reference to learn more about PL/SQL.
Packages
Packages are database objects that group logically related PL/SQL types, objects, and subprograms. Packages usually consist of two files: a package specification file and a package body file. The specification file is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use in the package. It contains the name of the package and functions function declarations. The package body defines cursors and subprograms, contains the actual PL/SQL code for the functions, and so implements the specification.
Functions
Functions are subprograms within a package that are invoked by the application, perform a specific action, and compute a value. Functions define what parameters will be passed in as context for the program, how the inputs are processed, and what output is returned. A function consists of the following elements:
-
Inputs - Each function has predefined input parameters, which must be passed in the predefined order. The parameters identify the transaction being processed and the context in which the program is called. You can derive additional inputs from any Oracle table based on the pre-defined input parameters.
-
Logic - The function uses the inputs and performs any logical processing and calculations. The program can be a simple program, such that it returns a fixed number, or it can be a complex algorithm that performs a number of functions.
-
Outputs - Each function returns whatever value you de-fine it to return. For example, your function for account generation extensions may return a null value if the transaction passes all val-idation rules; or an error message if validation fails.
Syntax for Functions
A function consists of two parts: the specification and the body. The function specification begins with the keyword FUNCTION and ends with the function name or a parameter list. The function body begins with the keyword IS and ends with the keyword END followed by an optional function name. The function body has three parts: a declarative part, an executable part, and an optional error handling part. You write functions using the following syntax:
FUNCTION name [ (parameter [, parameter,...]) ] RETURN
DATATYPE IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
The parameter syntax above expands to the following syntax:
parameter_name [IN | OUT | IN OUT] datatype [{:= | DEFAULT}
expr]
For more information, refer to the PL/SQL User’s Guide and Reference Manual.
Using Template Functions
Cost Management provides you with template functions for each client extension that you can use to write your own functions. Each function contains predefined parameters that are passed into the function by the program that calls the function; you cannot change these predefined input parameters. The Client Extensions Table: page C – 2 lists each client extension and its predefined template function filenames. The template function files are stored in the Cost Management admin/sql directory.
-
Suggestion: Review the appropriate files before you design and implement a client extension. They provide a lot of useful information, including the predefined input parameter list and example case studies.
-
Suggestion: You should make a copy of these template files in a directory used by your company to store code that you have written. You should make changes to these copies of the files instead of writing directly into these template files. These template files will be replaced when the software is upgraded between releases. Use your modified files to reinstall your functions after an upgrade to a new release of Cost Management.
Writing Logic in Your PL/SQL Functions
You write the logic in the PL/SQL functions based on the functional specifications created during the design process. Before you begin to write the client extension PL/SQL functions, you should have a clear understanding of the client extension functions; including the inputs and outputs, the error handling of the extension, along with any example functions provided for each extension. Read the appropriate client extension essays and template functions to obtain detailed information about the client extensions.
As you determine how to best write the client extension, you should consider these issues:
-
Can I derive every derived input parameter based on the data structures known?
-
What outputs should the client extension return?
-
How does the client extension handle exceptions?
-
Are there functions which I can write which are reusable across similar client extensions?
-
How I can write logical, well commented code that is easy to maintain and debug?
-
How do I test and debug this client extension?
-
Are there any performance considerations in the client extension? If so, what are they and how do I address them?
Important: You must not commit data within your PL/SQL function. Cost Management processes that call your functions handle the commit logic.
Storing Your Functions
After you write your functions and ensure that the specification file correctly includes any functions that you have defined, you need to compile and store the functions in the database in the Applications Oracle username. You must install the package specification before the package body.
The syntax for compiling and storing PL/SQL functions is included in the template function files. Assuming you have written your functions using copies of these template function files, you can use these steps to compile and store your functions:
Change to the directory in which your files are stored (use the command that is appropriate to your operating system).
$ sqlplus <apps username> / <apps password> SQL> @<spec_filename>.pls SQL> @<body_filename>.pls
For example, use the following commands to install your account generation extension (assuming your Oracle E-Business Suite Oracle username/password is apps/apps):
$ sqlplus apps/apps SQL> @CSTPSCHK.pls apps apps @ CSTSCHKS.pls SQL> @CSTPSCHK.pls apps apps @ CSTSCHKB.pls
If you encounter compilation errors in trying to create your packages and its functions, then you must debug the errors, correct your package definitions, and try to create your packages again. You must successfully compile and store your package and its functions in the database before you can use the client extensions in Cost Management.
Testing Your Functions
After you have created your client extension functions, you must test your client extension definitions within the processing flow of Cost Management to verify the results are as you expect.
Transaction Cost Extension
The Transaction Cost Extension allows you to reset the transaction costs by cost element and by level in an Average/FIFO/LIFO costing organization.
Processing
Cost Management calls the transaction cost extensions for most transactions at the time of processing. The three exceptions are Average Cost Update transactions, in an average costing organization, a Common Issue to Project Work in Process transaction, in a project manufacturing costing organization, and Interorganization Transfer transactions.
You should ensure that the cost element by level costs is in MTL_CST_ACTUAL_COST_DETAILS according to your requirements.
Return Values of Function
When this extension is called, the system determines whether it has been implemented and returns a value accordingly. The return values are as follows:
-
1 - The extension has been used and costs have been reset by cost element and by level.
-
0 - The extension has not been used.
Related Topics
Writing Transaction Cost Extensions
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
Writing Transaction Cost Extensions
Cost Management provides a template package and function that you use as the basis of your transaction cost extension functions. The name of the package is CSTPACHK.
Print out and review the following files before you begin writing transaction costing extensions. The files are located in the Cost Management plsql/115/sql directory.
-
CSTACHKS.pls - Transaction Cost Extension Package Specifica-tion Template. If you create functions within the package, outside the predefined function, you must also modify this package.
-
CSTACHKB.pls - Transaction Cost Extension Package Body Template. This file contains the function that you modify to implement transaction cost ex-tensions. You can define as many functions as you like within this package or within the pre-defined function.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Storing Your functions.
Package Function
actual_cost_hook
The following table lists the parameters for Transaction Cost type extensions.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the cur-rent transaction is done in |
| i_txn_id | IN | NUMBER | Transaction identifier |
| i_layer_id | IN | NUMBER | The costing layer this transac-tion item corresponds to. Us-ing the layer_id, one can ob-tain the current average cost stored in cst_quantity_layers |
| i_cost_type | IN | NUMBER | The valuation cost type the cost processor is running for. |
| i_user_id | IN | NUMBER | User identifier |
| i_login_id | IN | NUMBER | Login identifier |
| i_req_id | IN | NUMBER | Request ID for program |
| i_prg_appl_id | IN | NUMBER | Application ID of program |
| i_prg_id | IN | NUMBER | Program identifier |
| l_err_num | OUT | NUMBER | Error number |
| l_err_code | OUT | NUMBER | Error code |
| l_err_msg | OUT | VARCHAR2 | Error message |
Error Handling
l_err_num
This parameter indicates the processing status of your extension as follows:
1_err_num = 0 - The extension executed successfully.
1_err_num <> 0 - An error occurred and the extension did not process successfully.
l_err_code and l_err_msg
Values for error_code, l_err_code, and error_explanation, l_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Related Topics
Oracle Bills of Material Technical Reference Manual, Release 12
Account Generation Extension
The Account Generation Extension allows you to provide alternative accounts. The Account Generation Extension has two template functions: one for material and scrap transactions and another for standard cost update transactions.
Note: Although the resulting accounts are provided as default accounts if these extensions are used, it is not recommended to write new extensions. You should use Subledger Accounting (SLA) to derive custom accounts. See: Oracle Subledger Accounting Implementation Guide.
Processing
Cost Management calls the Account Generation extension each time standard or average costing material or standard cost update transactions are performed.
Return Values of Function
When this extension is called the system determines whether it has been implemented and returns a value accordingly. The return values are as follows:
-
–1 - The extension was not used and the normal default accounts should be used.
-
>0 - The extension was used and the user specified accounts should be used.
Related Topics
Writing Account Generation Extensions for Standard Costing
Writing Account Generation Extensions for Average Costing
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference, Release 12
Writing Account Generation Extensions for Standard Costing
Cost Management provides a template package and functions as a basis for account generation type extensions. The name of the template package is CSTPSCHK.
Print the following files before you begin writing Account Generation type extensions. The files are located in the Cost Management plsql/115/sql directory.
-
CSTSCHKS.pls - Account Generation Type Extension Package Specification Template. If you create functions outside the predefined function within the CSTPSCHK package, you must also modify this file to include those new functions.
-
CSTSCHKB.pls - Account Generation Type Extension Package Body Template. This file contains the function that you can modify to implement the Ac-count Generation type extension.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function for Material and Scrap Transactions
You can use this function for either material or scrap transactions.
std_get_account_id
The following table lists the parameters for Account Generation type extensions that provide alternate material transaction accounts in standard costing organizations.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the actual cost worker is running in |
| i_txn_id | IN | NUMBER | Transaction identifier |
| i_debit_credit | IN | NUMBER | Transaction type; debit or credit |
| i_acct_line_type | IN | NUMBER | Accounting line type |
| i_cost_element_id | IN | NUMBER | Cost element identifier |
| i_resource_id | IN | NUMBER | Resource identifier |
| i_subinv | IN | VARCHAR2 | Subinventory involved |
| i_exp | IN | NUMBER | Expense or asset account indicator |
| i_snd_rcv_org | IN | NUMBER | Sending or receiving organization indicator |
| i_txn_type_id | IN | NUMBER | Transaction type identifier |
| i_txn_act_id | IN | NUMBER | Transaction action identifier |
| i_txn_src_type_id | IN | NUMBER | Transaction source type identifier |
| i_item_id | IN | NUMBER | Item identifier |
| i_cg_id | IN | NUMBER | Cost group identifier |
| o_err_code | OUT | VARCHAR2 | Output error code. Can be any SQL code. |
| o_err_num | OUT | NUMBER | Output error number |
| o_err_msg | OUT | VARCHAR2 | Error message |
Additional Information About Parameters
Debit/Credit
The valid values of i_debit_credit are as follows and indicate whether the traction is a debit or a credit:
-
i_debit_credit = 1 Indicates a debit.
-
i_debit_credit = –1 Indicates a credit.
Expense Account Indicator
The valid values of i_exp are:
-
i_exp = 0 - Expense item transaction.
-
i_exp = 1 - Asset item transaction.
Sending/Receiving Organization
The valid values of i_snd_rcvg_org are:
-
i_snd_rcvg_org = 1 - The organization (i_org_id) is a sending orga-nization for inter–organization transactions.
-
i_snd_rcvg_org = 2 - The organization (i_org_id) is a receiving or-ganization for inter–organization transactions.
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Package Function for Standard Cost Update Transactions
std_get_update_acct_id
The following table lists the parameters for Account Generation type extensions that provide alternate cost update accounts in standard costing organizations.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the cost distributor is running in |
| i_update_id | IN | NUMBER | Cost update session identifier |
| i_debit_credit | IN | NUMBER | Type of transaction, debit or credit |
| i_cost_element_id | IN | NUMBER | Cost element identifier |
| i_acct_line_type | IN | NUMBER | The accounting line type |
| i_resource_id | IN | NUMBER | Resource identifier |
| i_subinv | IN | VARCHAR2 | Subinventory identifier |
| i_exp | IN | NUMBER | Expense or asset account indicator |
| i_snd_rcv_org | IN | NUMBER | Sending or receiving organization indicator |
| o_err_code | OUT | VARCHAR2 | Output error code. Can be any SQL code. |
| o_err_num | OUT | NUMBER | Output error number |
| o_err_msg | OUT | VARCHAR2 | Error message |
Additional Information About Parameters
Debit/Credit
The valid values of i_debit_credit are as follows and indicate whether the traction is a debit or a credit:
-
i_debit_credit = 1 - Indicates a debit.
-
i_debit_credit = –1 - Indicates a credit.
Expense Account Identifier
The valid values of i_exp are:
-
i_exp = 0 - The transaction is an asset transaction.
-
i_exp <> 0 - The transaction is an expense transaction.
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Package Function
std_get_update_scrap_acct_id
This routine lets you select the account to be used for posting scrap adjustments in the standard cost update process for standard lot based jobs.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the cost distributor is running in |
| i_cost_update_id | IN | NUMBER | Cost update session identifier |
| I_WIP_ENTITY_ID | IN | NUMBER | wip_entity_id of the work order |
| I_DEPT_ID | IN | NUMBER | department_id of the department that runs the operation |
| I_OPERATION_SEQ_NUM | IN | NUMBER | operation sequence number of the operation |
Return Values of Function
When this extension is called, the application determines if it has been implemented and returns a value accordingly. The return values are:
-
-1 The extension was not used. Hence use the normal department scrap account
-
<>-1 use this return value as the scrap adjustment account
Package Function
std_get_est_scrap_rev_acct_id
This routine lets you select the account to be used for posting estimated scrap reversal in the operation yield processor for scrap transactions.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the cost distributor is running in |
| I_WIP_ENTITY_ID | IN | NUMBER | wip_entity_id of the work order |
| I_OPERATION_SEQ_NUM | IN | NUMBER | operation sequence number of the operation |
Return Values of Function
When this extension is called, the application determines if it has been implemented and returns a value accordingly. The return values are:
-
-1 The extension was not used. The default scrap account of current operation will be used.
-
<>-1 use this return value to be charged for scrap reversal transaction
Related Topics
Writing Account Generation Extensions for Average Costing
Oracle Bills of Material Technical Reference Manual, Release 12
Writing Account Generation Extensions for Average/FIFO/LIFO Costing
Cost Management provides a template package and function for account generation type extensions. The name of the template package is CSTPACHK.
Print the following files before you begin writing Accounting Generation type extensions. The files are located in the Cost Management plsql/115/sql directory.
-
CSTACHKS.pls - Accounting Generation Type Extension Pack-age Specification Template. If you create func-tions outside the predefined function within the CSTPACHK package, you must also modify this file to include those new func-tions.
-
CSTACHKB.pls - Accounting Generation Type Extension Pack-age Body Template. This file contains the function that you can modify to implement the Account Generation type extension.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function for Material Transactions
get_account_id
The following table lists the parameters for Account Generation type extensions that provide alternate material transaction accounts in Average/FIFO/LIFO costing organizations.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the actual cost worker is running in |
| i_txn__id | IN | NUMBER | Transaction identifier |
| i_debit_credit | IN | NUMBER | Transaction type; debit or credit |
| i_acct_line_type | IN | NUMBER | Accounting line type |
| i_cost_element_id | IN | NUMBER | Cost element Identifier |
| i_resource_id | IN | NUMBER | Resource identifier |
| i_subinv | IN | VARCHAR2 | Subinventory involved (optional) |
| i_exp | IN | NUMBER | Expense or asset account identifier |
| i_snd_rcv_org | IN | NUMBER | Sending or receiving organization identifier |
| i_txn_type_id | IN | NUMBER | Transaction type identifier |
| i_txn_type_id | IN | NUMBER | Transaction action identifier |
| i_txn_src_type_id | IN | NUMBER | Transaction source type identifier |
| i_item_id | IN | NUMBER | Item identifier |
| i_cg_id | IN | NUMBER | Cost group identifier |
| o_err_code | OUT | VARCHAR2 | Output error code. Can be any SQL code. |
| o_err_nun | OUT | NUMBER | Output error number |
| o_err_msg | OUT | VARCHAR2 | Error message |
Additional Information About Parameters
Debit/Credit
The valid values of i_debit_credit are as follows and indicate whether the transaction is a debit or a credit:
-
i_debit_credit = 1 - Indicates a debit.
-
i_debit_credit = –1 - Indicates a credit.
Expense Account Identifier
The valid values of i_exp are:
-
i_exp = 0 - The transaction is an asset transaction.
-
i_exp <> 0 - The transaction is an expense transaction.
Sending/Receiving Organization
The valid values of i_snd_rcvg_org are:
-
i_snd_rcvg_org = 1 - The organization (i_org_id) is a sending orga-nization.
-
i_snd_rcvg_org = 2 - The organization (i_org_id) is a receiving or-ganization.
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Related Topics
Writing Account Generation Extensions for Standard Costing
Oracle Bills of Material Technical Reference Manual, Release 12
Cost Processing Cutoff Date Extension
The Cost Processing Cutoff Date Extension allows you to cost (process) transactions up through a user–specified date.
Processing
Cost Management calls the cost processing cutoff date extension as each transaction is processed.
Return Values of Function
-
Date (DD–MON–YYYY) - The extension was invoked and costs were processed up through the date returned.
-
SYSDATE + 1 - The extension was not used.
For example, if the return value is 01–DEC–1998, the system processes all costs up to 11:59:59 pm on 30–NOV–1998.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual
Writing Cost Processing Cutoff Date Extensions
Cost Management provides a template package and function that you use as the basis of your cost processing cutoff date extension functions. The name of the template package is CSTPACHK.
Print the following files before you begin writing Cost Processing Cutoff Date type extensions. The files are located in the Cost Management plsql/115/sql directory.
-
CSTACHKS.pls - Cost Processing Cutoff Date Type Extension Package Specification Template. If you create functions outside the predefined function within the CSTPSCHK package, you must also modify this file to include those new functions.
-
CSTACHKB.pls - Cost Processing Cutoff Date Extension Package Body Template. This file contains the function that you can modify to implement the Cost Processing Cutoff Date type extension.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function for Cutoff Date
get_date
You can use this function for either material or scrap transactions. The following table lists the parameters for the Cost Processing Cutoff Date extension.
Parameters
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the current transaction is done in |
| o_error_message | OUT | VARCHAR2 | Error message |
Related Topics
Oracle Bills of Materials and Oracle Configurator Technical Reference Manual, Release 12
Project Cost Collector Transaction Client Extension
The Project Cost Collector Transaction client extension provides you the flexibility to control the logic of inserting records into the Projects interface table (PA_TRANSACTIONS_INTERFACE_ALL) for each inventory transaction.
An example of this flexibility includes the ability to write individual records by serial number.
Processing
The extension is invoked only for transactions that are eligible to be transferred to Oracle Projects. For a description of transactions that are eligible for transfer to Oracle Projects, see: Project Manufacturing Costing Transactions. If activated, the Cost Collector does not insert a record in the Projects interface table, but relies on the customized code to do so.
Return Values of Function
When this extension is called the system determines whether it has been implemented and returns a value accordingly. The return values are as follows:
Returns a non–zero value in variable o_hook_used if being used.
Writing Project Cost Collector Transaction Client Extension
Cost Management provides a template package and function that you use as the basis of your project cost collector transaction extension functions. The name of the template package is CST_COST_COLLECTOR_HOOK.
Print the following files before you begin writing Cost Collector Transaction Client Extensions. The files are located in the Cost Management plsql/115/sql directory.
-
CSTCCHKS.pls - Project Cost Collector Transaction Extension Package Specification Template.
-
CSTCCHKB.pls - Project Cost Collector Transaction Extension Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
pm_invtxn_hook
The following table lists the parameters for Project Cost Collector Transaction Extensions.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| p_transaction_id | IN | NUMBER | Transaction identifier |
| p_organization_id | IN | NUMBER | Organization identifier |
| p_transaction_action _id | IN | NUMBER | Transaction action identifier |
| p_transaction_source _type_id | IN | NUMBER | Transaction source type identifier |
| p_type_class | IN | NUMBER | Type class identifier (=1 if proj miscellaneous transactions) |
| p_project_id | IN | NUMBER | Project identifier |
| p_task_id | IN | NUMBER | Task identifier |
| p_transaction_date | IN | DATE | Transaction date |
| p_primary_quantity | IN | NUMBER | Transaction quantity |
| p_cost_group_id | IN | NUMBER | Cost group identifier |
| p_transfer_cost_group_id | IN | NUMBER | Transfer cost group |
| p_inventory_item_id | IN | NUMBER | Item |
| p_transaction_source _id | IN | NUMBER | Transaction source |
| p_to_project_id | IN | NUMBER | Destination project ID |
| p_to_task_id | IN | NUMBER | Destination task ID |
| p_source_project_id | IN | NUMBER | Source project ID |
| p_source_task_id | IN | NUMBER | Source task ID |
| p_transfer_transaction_id | IN | NUMBER | Transfer transaction identifier |
| p_primary_cost _method | IN | NUMBER | Organization's cost method |
| p_acct_period_id | IN | NUMBER | Inventory accounting period |
| p_exp_org_id | IN | NUMBER | Expenditure organization |
| p_distribution _account_id | IN | NUMBER | Distribution account |
| p_proj_job_ind | IN | NUMBER | Flag to indicate a project job |
| p_first_matl_se_exp _type | IN | VARCHAR2 | First material subelement expenditure type |
| p_inv_txn_source _literal | IN | VARCHAR2 | Inventory transaction source literal |
| p_cap_txn_source _literal | IN | VARCHAR2 | Capital transaction source literal |
| p_inv_syslink_literal | IN | VARCHAR2 | Inventory system linkage literal |
| p_bur_syslink_literal | IN | VARCHAR2 | Burden system linkage literal |
| p_wip_syslink_literal | IN | VARCHAR2 | Work in process system linkage literal |
| p_user_def_exp_type | IN | NUMBER | Flag to indicate user defined expenditure type profile option |
| p_transfer _organization_id | IN | NUMBER | Transfer organization |
| p_flow_schedule | IN | NUMBER | Flag to indicate a flow schedule |
| p_si_asset_yes_no | IN | NUMBER | Flag to indicate an asset subinventory |
| p_transfer_si_asset _yes_no | IN | NUMBER | Flag to indicate an asset transfer subinventory |
| p_exp_type | IN | NUMBER | Expenditure type |
| p_dr_code _combination_id | IN | NUMBER | Debit account |
| p_cr_code _combination_id | IN | NUMBER | Credit account |
| p_raw_cost | IN | NUMBER | Raw cost |
| p_burden_cost | IN | NUMBER | Burden cost |
| o_hook_used | OUT | NUMBER | Flag to indicate if this extension is being used |
| o_err_ num | OUT | VARCHAR2 | Error number |
| o_err_code | OUT | VARCHAR2 | Error code |
| o_err_msg | OUT | VARCHAR2 | Error message |
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Related Topics
Writing Transaction Cost Extensions
PL/SQL User's Guide and Reference
Project Cost Collector Accounting Extension
This extension gives the users the flexibility to pass accounts to the project interface. The extension may be used for passing custom capitalization accounts.
Processing
The extension is invoked only for transactions that are eligible to be transferred to Oracle Projects. For a description of transactions that are eligible for transfer to Oracle Projects, see: Project Manufacturing Costing Transactions.
Return Values of Function
When this extension is called the system determines whether it has been implemented and returns a value accordingly. The return values are as follows:
Returns True if being used.
Writing Project Cost Collector Accounting Extension
Cost Management provides a template package and function that you use as the basis of your project cost collector accounting extension functions. The name of the template package is CST_PRJMFG_ACCT_HOOK.
Print the following files before you begin writing Cost Collector Accounting Extensions. The files are located in the Cost Management plsql/115/sql directory.
-
CSTPMHKS.pls - Project Cost Collector Accounting Extension Package Specification Template.
-
CSTPMHKB.pls - Project Cost Collector Accounting Extension Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
pm_use_hook_acct
The following table lists the parameters for Project Collector Accounting Extensions.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| p_transaction_id | IN | NUMBER | Transaction identifier |
| p_transaction_action _id | IN | NUMBER | Transaction action identifier |
| p_transaction_source _type_id | IN | NUMBER | Transaction source type identifier |
| p_organization_id | IN | NUMBER | Organization identifier |
| p_inventory_item_id | IN | NUMBER | Item identifier |
| p_cost_element_id | IN | NUMBER | Cost element |
| p_resource_id | IN | NUMBER | Subelement identifier |
| p_primary_quantity | IN | NUMBER | Transaction quantity expressed in primary unit of measure |
| p_transfer _organization_id | IN | NUMBER | Transfer organization identifier |
| p_fob_point | IN | NUMBER | FOB point identifier |
| p_wip_entity_id | IN | NUMBER | WIP entity identifier |
| p_basis_resource_id | IN | NUMBER | Basis subelemnt for resource based overheads |
| o_dr_code _combination_id | OUT | NUMBER | Debit account |
| o_cr_code _combination_id | OUT | NUMBER | Credit account |
Related Topics
Writing Transaction Cost Extensions
PL/SQL User's Guide and Reference
Periodic Average Costing Account Generation Extension
The Periodic Average Costing Account Generation Extension lets you provide alternative accounts.
Processing
Cost Management calls the Account Generation extension each time a transaction is processed by the Periodic Distributions Processor.
Return Values of Function
When this extension is called, the application determines if it has been implemented, and returns a value accordingly. The return values are as follows:
-
–1 The extension was not used and the normal default accounts should be used.
-
>0 The extension was used and the user specified accounts should be used. The return value should be a valid code_combination_id from the GL_CODE_COMBINATIONS table for the ledger associated with the cost group.
Writing Account Generation Extensions for Periodic Average Costing
Cost Management provides a template package and function for account generation extension for Periodic Average Costing. The name of the template package is CSTPAPHK.
Print the following files before you begin writing Periodic Average Costing Account extensions. The files are located in the Cost Management patch/115/sql directory.
-
CSTAPHKS.pls Account Generation Extension Package Specification Template. If you create functions outside the predefined function within the CSTPACHK package, then you must also modify this file to include those new functions.
-
CSTAPHKB.pls Account Generation Extension Package Body Template. This file contains the function get_account_id that you can modify to implement the Account Generation extension.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function for Periodic Average Costing Account Generation client extension get_account_id
The following table lists the parameters for Account Generation extension that provide alternate accounts in Periodic Average Costing.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| I_TXN_ID | IN | NUMBER | Transaction Identifier |
| I_LEGAL_ENTITY | IN | NUMBER | Legal Entity Identifier |
| I_COST_TYPE_ID | IN | NUMBER | Cost Type being processed |
| I_COST_GROUP_ID | IN | NUMBER | Cost Group being processed |
| I_DR_FLAG | IN | BOOLEAN | Flag indicating if this is the debit line |
| I_ACCT_LINE_TYPE | IN | NUMBER | Accounting Line Type |
| I_COST_ELEMENT_ID | IN | NUMBER | Cost Element Identifier |
| I_RESOURCE_ID | IN | NUMBER | Resource Identifier |
| I_SUBINV | IN | VARCHAR2 | Subinventory on the transaction |
| I_EXP | IN | BOOLEAN | Expense or Asset identifier |
| O_Err_Num | OUT | NUMBER | Output Error Number |
| O_Err_Code | OUT | VARCHAR2 | Output Error Code – can be any SQL error code |
| O_Err_Msg | OUT | VARCHAR2 | Output Error Message |
Additional Information About Parameters
Debit/Credit
The valid values of i_dr_flag are as follows and indicate whether the accounting entry is a debit or a credit:
-
i_dr_flag = TRUE Indicates a debit
-
i_dr_flag = FALSE Indicates a credit
(TRUE and FALSE are pre-defined PL/SQL constants that can be assigned to BOOLEAN variables).
Expense Account Identifier
The valid values of i_exp are:
-
i_exp = TRUE The transaction is an expense distribution.
-
i_exp = FALSE The transaction is an asset distribution.
(TRUE and FALSE are pre-defined PL/SQL constants that can be assigned to BOOLEAN variables).
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 The extension executed successfully.
-
o_err_num <> 0 An error condition has occurred and the extension did not process successfully.
o_err_code and o_err_msg
Values for o_err_code, and o_err_msg can be viewed in the Periodic Distributions Processor Log file.
Related Topics
Writing Transaction Cost Extensions
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
Periodic Average Costing Landed Cost Extension
The Periodic Average Costing Landed Cost Extension is used by Brazil customers for localization purposes.
You must run the custom landed cost calculation API through CST_LandedCostHook_PUB.landed_cost_hook.
This lets you bypass the acquisition cost processing from Periodic Acquisition Cost Processor and Periodic Acquisition Cost Adjustment Processor.
Important: You can only use this extension as country localization by checking the country code. You should not use this extension customization purposes.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| I_PERIOD | IN | NUMBER | Period Indentifier |
| I_START_DATE | IN | NUMBER | Start Date |
| I_END_DATE | IN | NUMBER | End Date |
| I_COST_TYPE_ID | IN | NUMBER | Cost Type being processed |
| I_COST_GROUP_ID | IN | NUMBER | Cost Group being processed |
| I_USER_ID | IN | NUMBER | User Identifier |
| I_LOGIN_ID | IN | NUMBER | User Login Identifier |
| I_REQ_ID | IN | NUMBER | |
| I_PROG_ID | IN | NUMBER | |
| I_PROG_APPL_ID | IN | ||
| l_ERR_NUM | OUT | NUMBER | Output Error Number |
| I_ERR_CODE | OUT | VARCHAR2 | Output Error Code – can be any SQL error code |
| l_ERR_MSG | OUT | VARCHAR2 | Output Error Message |
Period Close Check for Shipping Transactions
The Period Close Check for Shipping Transactions client extension (CST_PeriodCloseOption_PUB.Get_ShippingTxnHook_Value) checks for pending transactions that prevent the period close program from processing. You can use this client extension to specify whether Shipping Transactions should be Resolution Required or Resolution Recommended. If you modify the code in the client extension procedure to return a value of 1 instead of 0 (default), then resolution of shipping transactions becomes optional. By default, the resolution of shipping transactions is required.
After a sales order is Ship Confirmed in the Oracle Manufacturing Shipping application, the inventory quantities must be depleted for the items in the sales order, and this is accomplished by running the Inventory Interface concurrent program. This concurrent program inserts rows into the Mtl_transaction_interface table. You can run the deferred transactions at a later date.
The Period Close process checks for unprocessed shipping transactions. You can view unprocessed transactions from the Inventory Accounting Periods window by selecting an Open accounting period, and choosing the Pending option to open the Pending Transactions window.
The Inventory Period Close validation also checks for pending landed cost adjustment records in the CST_LC_ADJ_INTERFACE table when LCM is enabled for a given inventory organization.
The Pending Transactions window displays the Resolution settings for shipping transactions.
You can also set up system alerts and view notifications in the Oracle Applications Manager (OAM) console. Set up system alerts by selecting System Alert Setup in the System Alerts- Notification window.
See: System Alerts ,
For details on setting up System Alerts, see the Oracle E-Business Suite System Administrator's Guide.
Get Absorption Account Extension
The Get Absorption Account (CSTPACHK.get_absorption_account_id) client extension lets you specify alternative resource absorption account based on resource instance and charge department for eAM jobs. This client extension is valid for all costing organizations. The value returned by the function is the absorption account which is used to create the resource absorption accounting for the resource or outside processing transactions in eAM jobs.
Processing
Cost Management calls the Get Absorption Account extension each time a resource or outside processing transaction is processed.
Return Values of Function
When this extension is called, the application determines if it has been implemented and returns a value accordingly. The return values are:
-
-1 The extension was not used and the normal default accounts should be used.
-
>0 The extension was used and the user specified accounts should be used. The return value should be a valid code_combination_id from the GL_CODE_COMBINATIONS table for the ledger associated with the organization.
Writing Get Absorption Account Extension
Cost Management provides a template package and function that you use as the basis of your get absorption account id extension function. The name of the template package is CSTPACHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTACHKS.pls – Get Absorption Account Extension Package Specification Template. If you create functions outside the predefined function within the CSTPACHK package, you must also modify this file to include those new functions.
-
CSTACHKB.pls - Get Absorption Account Extension Package Body Template. This file contains the function that you can modify to implement the Get Absorption Account extension.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function for Resource and Outside Processing Transactions
Get_absorption_account_id
You can use this function for either resource or outside processing transactions. The following table lists the parameters for the get absorption account id extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| I_ORG_ID | IN | NUMBER | Organization identifier |
| I_TXN_ID | IN | NUMBER | Transaction identifier |
| I_CHARGE_DEPT_ID | IN | NUMBER | Department to be charged identifier |
| I_RES_INSTANCE_ID | IN | NUMBER | Resource Instance Identifier |
Validate Job Re-estimation Extension
The Validate Job Re-estimation (CSTPACHK.validate_job_est_status_hook) client extension lets you override default validation for allow or disallow cost re-estimation for eAM jobs. You can modify the function validate_job_est_status_hook to implement your own logic for determining whether to allow or disallow cost re-estimation.
Processing
The Work Order Value summary form calls this function to determine if the re-estimation flag can be updated or not.
Return Values of Function
When this extension is called, the application determines if it has been implemented and returns a value accordingly. The return values are:
-
0 - hook is not used
-
1 - hook is used
Writing Validate Job Re-estimation Extension
Cost Management provides a template package and function that you use as the basis of your get absorption account id extension function. The name of the template package is CSTPACHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTACHKS.pls – Validate Job Re-estimation Extension Package Specification Template. If you create functions outside the predefined function within the CSTPACHK package, you must also modify this file to include those new functions.
-
CSTACHKB.pls - Validate Job Re-estimation Extension Package Body Template. This file contains the function that you can modify to implement the Validate Job Re-estimation extension.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
validate_job_est_status_hook
The following table lists the parameters for Validate Job Re-estimation Extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_wip_entity_id | IN | NUMBER | Work Order identifier |
| i_job_status | IN | NUMBER | Work Order status identifier |
| i_urr_est_status | IN | NUMBER | Current Estimation status of the Work Order |
| o_validate_flag | OUT | NUMBER | Organization identifier |
| O_Err_Num | OUT | NUMBER | Output Error Number |
| O_Err_Code | OUT | VARCHAR2 | Output Error Code – can be any SQL error code |
| O_Err_Msg | OUT | VARCHAR2 | Output Error Message |
VALUES for o_validate_flag
-
0 - Re-estimation flag is not updateable in Work Order Value summary form.
-
1 - Re-estimation flag is updateable Work Order Value summary form.
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
FIFO/LIFO Layer Consumption Extension
Three client extensions are provided to let you modify the behavior of layer consumption logic in FIFO/LIFO. The name of the package is CSTPACHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTACHKS.pls – FIFO/LIFO Layer Consumption Extension Package Specification Template.
-
CSTACHKB.pls – FIFO/LIFO Layer Consumption Extension Package Body Template.
Consumption Logic
For issues from asset sub inventories, the layers will be consumed in the following order:
-
quantity in the layer specified by the layer_hook .
-
Drive the layer specified by the layer_hook negative only if there are no other positive layers.
-
Positive quantity from the layers specified by the layers_hook in the order that they are specified.
-
For return to receiving, corrections and assembly returns:
-
Positive quantity from the layers that was created for the deliveries for the same PO or completions from the same job in FIFO/LIFO manner.
-
Drive the earliest / latest layer that was created for the deliveries for the same PO or completions from the same job negative only if there are no other positive layers.
-
Package Function for Single Layer Consumption
This client extension lets you define a layer from which a transaction should consume. Cost Management calls this single layer consumption extension for each transaction at the time of costing for FIFO/LIFO transactions.
layer_hook
The following table lists the parameters for Single Layer Consumption Extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_org_id | IN | NUMBER | The organization that the layer cost worker is running in |
| i_txn_id | IN | NUMBER | Transaction identifier |
| i_layer_id | IN | NUMBER | Layer Id of the transaction to be consumed |
| i_cost_method | IN | NUMBER | Costing method used for processing the transaction |
| i_user_id | IN | NUMBER | User identifier |
| i_login_id | IN | NUMBER | Login identifier |
| i_req_id | IN | VARCHAR2 | Request ID for program |
| i_prg_appl_id | IN | NUMBER | Application ID of program |
| i_prg_id | IN | NUMBER | Program identifier |
| o_err_code | OUT | VARCHAR2 | Output error code. Can be any SQL code. |
| o_err_num | OUT | NUMBER | Output error number |
| o_err_msg | OUT | VARCHAR2 | Error message |
Return Values of Function
-
>0 The extension has been used, return value is the inventory layer id from CST_INV_LAYERS.
-
0 The extension has not been used.
Additional Information about Parameters
Transaction Information
-
i_org_id: The organization id corresponding to the transaction in MTL_MATERIAL_TRANSACTIONS.
-
i_txn_id: This transaction id should correspond to the id of the material transaction in MTL_MATERIAL_TRANSACTIONS table that requires the layer consumption.
-
i_layer_id: This is the input layer id from CST_QUANTITY_LAYERS for a combination of organization, item and cost group.
Costing Method
The valid values for i_cost_method are:
-
i_cost_method = 5 – FIFO
-
i_cost_method = 6 – LIFO
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Procedure for Multilayer Consumption
This client extension lets you define multiple layers from which a transaction should consume from. Cost Management calls this multilayer consumption extension for each transaction at the time of costing for FIFO/LIFO transactions.
layers_hook
The following table lists the parameters for the multiple layer consumption:
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_txn_id | IN | NUMBER | Transaction identifier |
| i_required_qty | IN | NUMBER | Quantity that transaction needs to consume |
| i_cost_method | IN | NUMBER | Costing method used for processing the transaction |
| o_custom_layers | IN OUT | INV_LAYER_TBL | PL/SQL table that contains the layers and the quantities that should be consumed for this transaction |
| o_err_code | OUT | VARCHAR2 | Output error code. Can be any SQL code. |
| o_err_num | OUT | NUMBER | Output error number |
| o_err_msg | OUT | VARCHAR2 | Error message |
Additional Information about Parameters
Transaction Information
-
i_txn_id: The transaction id of the material transaction in MTL_MATERIAL_TRANSACTIONS.
-
i_required_qty: The quantity in primary Unit of Measure that this transaction should consume.
List of inventory layers
o_custom_layers: This is a PL/SQL table that contains records of type inv_layer_rec. The records have two columns, one contains the layer id that should be matched with the inv_layer_id in CST_INV_LAYERS table and the other one contains the layer quantity that should be consumed for this layer.
Costing Method
The valid values for i_cost_method are:
-
i_cost_method = 5 – FIFO
-
i_cost_method = 6 – LIFO
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Function for Layers Merge
This client extension lets you specify if a layer merge should be attempted for the desired transactions. It will attempt to combine the quantity from the specified receipt transaction with an existing inventory layer. Cost Management calls this client extension every time a layer is going to be created to check if this new layer should be merged with an existing layer. By default, the program attempts to merge layers.
LayerMerge_hook
The following table lists the parameters for the Layer Merge consumption:
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_txn_id | IN | NUMBER | Transaction identifier |
| o_err_code | OUT | VARCHAR2 | Output error code. Can be any SQL code. |
| o_err_num | OUT | NUMBER | Output error number |
| o_err_msg | OUT | VARCHAR2 | Error message |
Return Values of Function:
-
1 - When the system has attempted to combine the quantity from the specified receipt transaction with an existing inventory layer.
-
0 - When a new inventory layer has been created for the specified receipt transaction.
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
-
o_err_num = 0 - The extension executed successfully.
-
o_err_num <> 0 - An error condition has occurred and the ex-tension did not process successfully.
o_err_code and o_err_msg
Values for error_code, o_err_code, and error_explanation, o_err_msg, are stored in the MTL_MATERIAL_TRANSACTIONS table.
Disable Accrual Accounting Extension
The Disable Accrual Accounting Extension lets you disable accrual accounting entries for all PO receiving transactions. When disabled, accrual accounting distributions are not created for all PO receiving transactions.
Processing
Cost Management calls the Disable Accrual Accounting Extension through the receiving interface transaction manager.
Return Values of Function
When this extension is called, the application determines whether it has been implemented and returns a value accordingly. The return values are:
-
1 - disable receipt accrual accounting
-
0 - do receipt accrual accounting
-
-999 - error occurred
Writing Disable Accrual Accounting Extension
Cost Management provides a package and function that you use to disable accrual accounting extension function. The name of the package is CSTRVHKS. The files are located in the Cost Management plsql/115/sql directory.
-
CSTRVHKS.pls – Disable Accrual Accounting Extension Package Specification Template.
-
CSTRVHKB.pls – Disable Accrual Accounting Extension Package Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
disable_accrual
The following table lists the parameters for disable accrual accounting extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| err_num | OUT | NUMBER | Error number |
| err_code | OUT | NUMBER | Error code |
| err_msg | OUT | VARCHAR2 | Error message |
Error Handling
err_num
This parameter indicates the processing status of your extension as follows:
err_num <> 0 - An error occurred and the extension did not process successfully.
err_code and err_msg
If err_num <> 0, values for err_code is sqlcode and err_msg is the error message from sqlerrm.
PAC Actual Cost Extension
The PAC Actual Cost Extension allows you to reset the actual cost for cost owned transactions of asset items in periodic average costing.
Processing
Cost Management calls the actual cost extension for the transactions satisfying following criteria:
-
Asset Item
-
Cost Owned transactions
-
No WIP related transactions
PAC Actual cost extension is invoked in the Periodic Average Cost Processor while computing the periodic weighted average cost. Ensure that the cost element by level costs are in MTL_PAC_ACTUAL_COST_DETAILS according to your requirements.
Return Values of Function
When this extension is called, the system determines whether it has been implemented and returns a value accordingly. The return values are as follows:
-
1 - The extension has been used and actual costs have been reset for this transaction id.
-
-1 - The extension has not been used. Continue cost processing for this transaction as usual.
Writing PAC Actual Cost Extension
Cost Management provides a template package and function that you use as the basis of your PAC actual cost extension. The name of the package is CSTPPCHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTPCHKS.pls - PAC Actual Cost Extension Package Specification Template.
-
CSTPCHKB.pls - PAC Actual Cost PAC Extension Package Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
actual_cost_hook
The following table lists the parameters for Actual Cost PAC extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_pac_period_id | IN | NUMBER | PAC period id |
| i_cost_group_id | IN | NUMBER | PAC Cost Group id |
| i_cost_type_id | IN | NUMBER | PAC Cost Type id |
| i_cost_method | IN | NUMBER | 3 - used for Periodic Average Costing, 4 - used for Incremental LIFO |
| i_txn_id | IN | NUMBER | Material Transaction id |
| i_cost_layer_id | IN | NUMBER | PAC cost layer id |
| i_qty_layer_id | IN | NUMBER | PAC quantity layer id |
| i_pac_rates_id | IN | NUMBER | PAC Rates identifier |
| i_user_id | IN | NUMBER | User identifier |
| i_login_id | IN | NUMBER | Login identifier |
| i_req_id | IN | NUMBER | Request ID for program |
| i_prg_appl_id | IN | NUMBER | Application ID of program |
| i_prg_id | IN | NUMBER | Program identifier |
| o_err_num | OUT | NUMBER | Error number |
| o_err_code | OUT | NUMBER | Error code |
| o_err_msg | OUT | VARCHAR2 | Error message |
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
o_err_num = 0 - The extension executed successfully.
o_err_num <> 0 - An error occurred and the extension did not process successfully.
o_err_code and o_err_msg
Values for o_err_code, and o_err_msg are retrieved from SQLCODE and SQLERRM when error occurs.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
PAC Beginning Balance Extension
PAC Beginning Balance Extension lets you initialize the beginning costs and beginning quantities in periodic average costing.
Processing
Cost Management calls the PAC beginning balance extension in Periodic Average Cost Processor, while setting up the beginning balance for the current PAC period. Ensure that the cost element by level costs are in CST_PAC_ITEM_COST_DETAILS, PAC item costs are in CST_PAC_ITEM_COSTS, and PAC layer quantity information is in CST_PAC_QUANTITY_LAYERS. For WIP completion items, operation requirements, applied value, applied quantity, relieved value, relieved quantity information to be populated in CST_PAC_REQ_OPER_COST_DETAILS.
Return Values of Function
When this extension is called, the system determines whether it has been implemented and returns a value accordingly. The return values are as follows:
-
1 - The extension has been used and beginning balance is set for the current PAC period, cost group, cost type.
-
-1 - The extension has not been used. Copy beginning balance from previous PAC period.
Writing PAC Beginning Balance Extension
Cost Management provides a template package and function that you use as the basis to set the beginning balance for current PAC period, cost group and cost type. The name of the package is CSTPPCHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTPCHKS.pls - Beginning Balance PAC Extension Package Specification Template.
-
CSTPCHKB.pls - Beginning Balance PAC Extension Package Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
beginning_balance_hook
The following table lists the parameters for beginning balance PAC extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_pac_period_id | IN | NUMBER | Current PAC period id |
| i_prior_pac_period_id | IN | NUMBER | Previous PAC period id |
| i_legal_entity | IN | NUMBER | PAC Legal Entity |
| i_cost_type_id | IN | NUMBER | PAC Cost Type id |
| i_cost_group_id | IN | NUMBER | PAC Cost Group id |
| i_cost_method | IN | NUMBER | 3 - Periodic Average Costing. Cannot be used for other costing methods. |
| i_user_id | IN | NUMBER | User identifier |
| i_login_id | IN | NUMBER | Login identifier |
| i_req_id | IN | NUMBER | Request ID for program |
| i_prg_appl_id | IN | NUMBER | Application ID of program |
| i_prg_id | IN | NUMBER | Program identifier |
| o_err_num | OUT | NUMBER | Error number |
| o_err_code | OUT | NUMBER | Error code |
| o_err_msg | OUT | VARCHAR2 | Error message |
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
o_err_num = 0 - The extension executed successfully.
o_err_num <> 0 - An error occurred and the extension did not process successfully.
o_err_code and o_err_msg
Values for o_err_code, and o_err_msg are retrieved from SQLCODE and SQLERRM when error occurs.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
PAC Acquisition Cost Extension
The PAC Acquisition Cost Extension lets you circumvent the standard periodic acquisition cost process.
Processing
Cost Management calls the acquisition cost extension from Periodic Acquisition Cost Processor and Periodic Acquisition Cost Adjustment Processor. Ensure that acquisition costs are available in the following tables:
CST_RCV_ACQ_COSTS and CST_RCV_ACQ_COST_DETAILS for each PO receipt belong to current PAC period. They are processed through Periodic Acquisition Cost Processor.
CST_RCV_ACQ_COSTS_ADJ and CST_RCV_ACQ_COST_DETAILS_ADJ for each parent PO receipt of the prior PAC periods where matched AP invoices belongs to current PAC period. They are processed through Periodic Acquisition Cost Adjustment Processor.
Return Values of Function
When this extension is called, the application determines whether it has been implemented and returns a value accordingly. The return values are:
-
1 - The extension has been used to circumvent the standard periodic acquisition cost process.
-
0 - The extension has not been used. Continue to use periodic acquisition cost processes.
Writing PAC Acquisition Cost Extension
Cost Management provides a template package and function that you use as the basis of your Acquisition cost extension functions. The name of the package is CSTPPAHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTPAHKS.pls - PAC Acquisition Cost Extension Package Specification Template.
-
CSTPAHKB.pls - PAC Acquisition Cost Extension Package Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Function
acq_cost_hook
The following table lists the parameters for PAC Acquisition Cost extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_period_id | IN | NUMBER | PAC period id |
| i_start_date | IN | DATE | PAC period start date |
| i_end_date | IN | DATE | PAC period end date |
| i_cost_type_id | IN | NUMBER | PAC Cost Type id |
| i_cost_group_id | IN | NUMBER | PAC Cost Group id |
| i_user_id | IN | NUMBER | User identifier |
| i_login_id | IN | NUMBER | Login identifier |
| i_req_id | IN | NUMBER | Request ID for program |
| i_prg_id | IN | NUMBER | Program identifier |
| i_prg_appl_id | IN | NUMBER | Application ID of program |
| o_err_num | OUT NOCOPY | NUMBER | Error number |
| o_err_code | OUT NOCOPY | NUMBER | Error code |
| o_err_msg | OUT NOCOPY | VARCHAR2 | Error message |
Error Handling
o_err_num
This parameter indicates the processing status of your extension as follows:
o_err_num = 0 - The extension executed successfully.
o_err_num <> 0 - An error occurred and the extension did not process successfully.
o_err_code and o_err_msg
Values for o_err_code, and o_err_msg are retrieved from SQLCODE and SQLERRM when error occurs.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
PAC Acquisition Receipt Cost Extension
The PAC Acquisition Receipt Cost Extension lets you reset the acquisition costs of receiving transactions in Periodic Average Costing.
Processing
Cost Management calls the acquisition receipt cost extension in Periodic Average Cost Processor. Acquisition Receipt cost extension is used to set the acquisition costs according to user requirements. Acquisition Receipt cost extension is invoked only when there is no data found in CST_RCV_ACQ_COSTS.
Output of Procedure
When this extension is called, the application determines whether it has been implemented and outputs the values accordingly.
O_HOOK_COST
-
0 - acquisition cost is 0 for the transaction. The extension might not have been used.
-
<>0 - acquisition cost has a non-zero value for the transaction.
Writing PAC Acquisition Receipt Cost Extension
Cost Management provides a template package and procedure that you use as the basis of your Acquisition Receipt cost extension procedure. The name of the package is CSTPPAHK. The files are located in the Cost Management plsql/115/sql directory.
-
CSTPAHKS.pls - PAC Acquisition Receipt Cost Extension Package Specification Template.
-
CSTPAHKB.pls - PAC Acquisition Receipt Cost Extension Package Body Template.
Tip: After you write the function, do not forget to compile it and store it in the database. See: Writing PL/SQL Functions/Procedures.
Package Procedure
acq_receipt_cost_hook
The following table lists the parameters for PAC Acquisition Receipt Cost extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| i_cost_type_id | IN | NUMBER | PAC Cost Type id |
| i_cost_group_id | IN | NUMBER | PAC Cost Group id |
| i_par_txn | IN | NUMBER | Transaction id of parent receive or match receiving transactions |
| o_hook_cost | OUT NOCOPY | NUMBER | Hook Acquisition Cost for the receiving transaction |
| o_error_num | OUT NOCOPY | NUMBER | Error number |
| o_error_msg | OUT NOCOPY | VARCHAR2 | Error message |
Error Handling
o_error_num
This parameter indicates the processing status of your extension as follows:
o_error_num = -1 - The extension executed successfully.
o_error_num <> -1 - An error occurred and the extension did not process successfully.
o_error_msg
Value for o_error_msg is retrieved from SQLCODE and SQLERRM when error occurs.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
Supply Chain Cost Rollup Buy Cost Extension
The Supply Chain Cost Rollup Buy Cost Extension lets you override the buy cost for a buy/purchase item defined against the buy cost type parameter.
Processing
Cost Management calls the Supply Chain Cost Rollup Buy Cost Extension for each item and organization at the time of populating buy costs against the assignment set for the Vendor source type. If this client extension is used, then the returned value is taken as the buy cost of the item, or the cost from the item costs table defined against the buy cost type is used.
Return Values of Function
When this extension is called, the application determines whether it has been implemented and returns a value accordingly. The return values are:
-
-1 - The extension has not been used and processing would be based on the standard definitions.
-
<> -1 - The extension has been used and the returned value is taken as the buy cost for the item.
Writing Supply Chain Cost Rollup Buy Cost Extension
Cost Management provides a template package and function that you use as the basis of your supply chain cost extension functions. The name of the package is CSTPSCHO. The files are located in the Cost Management patch/115/sql directory.
-
CSTSCHOS.pls - Supply Chain Cost Rollup Extension Package Specification Template.
-
CSTSCHOB.pls - Supply Chain Cost Rollup Extension Package Body Template.
Package Function
Get_Buy_Cost_Hook
The following table lists the parameters for Get Buy Cost Hook extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| p_rollup_id | IN | NUMBER | Rollup Identifier sequence |
| p_assignment_set_id | IN | NUMBER | Assignment set value used |
| p_item_id | IN | NUMBER | Inventory Item Identifier |
| p_organization_id | IN | NUMBER | Organization Identifier |
| p_vendor_id | IN | NUMBER | Vendor Identifier |
| p_site_id | IN | NUMBER | Vendor Site Identifier |
| p_ship_method | IN | NUMBER | Vendor Ship Method |
| x_err_code | OUT | NUMBER | Error Code |
| x_err_buf | OUT | VARCHAR2 | Error Message |
Error Handling
x_err_code
This parameter indicates whether the Hook has been customized.
x_err_code = 0 - The extension executed successfully.
x_err_code <> 0 - An error occurred and the extension did not process successfully.
x_err_buf
x_err_buf returns the error description.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
Supply Chain Cost Rollup Markup Cost Extension
The Supply Chain Cost Rollup Markup Cost Extension lets you override the Interorg transfer charge based on the item, source organization, destination organization and markup code.
Processing
Cost Management calls the Supply Chain Cost Rollup Markup Cost Extension for each item and organization at the time of populating Interorg transfer charge against the Transfer source type. This is charged as material overhead cost from source organization. If this client extension is used, then the value assigned to markup indicates the amount or percentage and the markup code indicates whether the markup amount is in terms of amount or percentage.
Return Values of Function
When this extension is called, the system determines whether it has been implemented and returns a value accordingly. The return values are:
X_Markup_Code
-
-1 - The extension has not been used and processing would be based on the standard definitions.
-
2 - The extension has been used and the returned value indicates the markup value is in terms of amount.
-
3 - The extension has been used and the returned value indicates the markup value is in terms of percentage.
X_Markup
Returned value indicates the markup cost.
Writing Supply Chain Cost Rollup Markup Cost Extension
Cost Management provides a template package and function that you use as the basis of your supply chain cost extension functions. The name of the package is CSTPSCHO. The files are located in the Cost Management patch/115/sql directory.
-
CSTSCHOS.pls - Supply Chain Cost Rollup Extension Package Specification Template.
-
CSTSCHOB.pls - Supply Chain Cost Rollup Extension Package Body Template.
Package Function
Get_Markup_Hook
The following table lists the parameters for Get Markup Hook extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| L_rollup_id | IN | NUMBER | Rollup Identifier sequence |
| L_item_id | IN | NUMBER | Inventory Item Identifier |
| l_org_id | IN | NUMBER | Organization Identifier |
| l_src_org_id | IN | NUMBER | Source Organization Identifier |
| l_dest_cost_type_id | IN | NUMBER | Destination Cost Type Identifier |
| l_buy_cost_type_id | IN | NUMBER | Buy Cost Type Identifier |
| l_markup | OUT | NUMBER | Markup value in Amount or Percent |
| l_markup_code | OUT | NUMBER | Indicate whether the markup value is amount or percentage. 2 - Indicates value in amount. 3 - Indicates value in percentage. Other values are ignored. |
| x_err_code | OUT | NUMBER | Error Code |
| x_err_buf | OUT | VARCHAR2 | Error Message |
Error Handling
x_err_code
This parameter indicates whether the Hook has been customized.
x_err_code = 0 - The extension executed successfully.
x_err_code <> 0 - An error occurred and the extension did not process successfully.
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12
Supply Chain Cost Rollup Shipping Cost Extension
The Supply Chain Cost Rollup Shipping Cost Extension lets you override the shipping cost based on the item, source organization, destination organization, and shipping method.
Processing
Cost Management calls the Supply Chain Cost Rollup Shipping Cost Extension for each item and organization at the time of populating shipping costs for Transfer source type. This is charged as material overhead from source organization. If this client extension is used, then the value assigned to ship charge indicates the value and the ship_charge_code indicates whether the ship charge value is in terms of amount or percentage.
Return Values of Function
When this extension is called, the application determines whether it has been implemented and returns a value accordingly. The return values are:
X_Ship_Charge_Code
-
-1 - The extension has not been used and processing would be based on the standard definitions.
-
2 - The extension has been used and the returned value indicates the ship charge value is in terms of amount.
-
3 - The extension has been used and the returned value indicates the ship charge value is in terms of percentage.
X_Ship_Charge
Returned value indicates the ship charge cost.
Writing Supply Chain Cost Rollup Shipping Cost Extensions
Cost Management provides a template package and function that you use as the basis of your supply chain cost extension functions. The name of the package is CSTPSCHO. The files are located in the Cost Management patch/115/sql directory.
-
CSTSCHOS.pls - Supply Chain Cost Rollup Extension Package Specification Template.
-
CSTSCHOB.pls - Supply Chain Cost Rollup Extension Package Body Template.
Package Function
Get_Shipping_Hook
The following table lists the parameters for Get Shipping Hook extension.
| Parameter | Usage | Type | Description |
|---|---|---|---|
| L_rollup_id | IN | NUMBER | Rollup Identifier sequence |
| L_item_id | IN | NUMBER | Inventory Item Identifier |
| l_org_id | IN | NUMBER | Organization Identifier |
| l_src_org_id | IN | NUMBER | Source Organization Identifier |
| l_dest_cost_type_id | IN | NUMBER | Destination Cost Type Identifier |
| l_buy_cost_type_id | IN | NUMBER | Buy Cost Type Identifier |
| L_ship_method | IN | NUMBER | Shipping Method |
| x_ship_charge | OUT | NUMBER | Ship Charge value in Amount or Percent |
| x_ship_charge_code | OUT | NUMBER | Indicates whether x_ship_charge value is in amount or percentage. 2 Indicates value in amount. 3 Indicates value in percentage Other values are ignored. |
| x_err_code | OUT | NUMBER | Error Code |
| x_err_buf | OUT | VARCHAR2 | Error Message |
Error Handling
x_err_code
This parameter indicates whether the Hook has been customized.
x_err_code = 0 - The extension executed successfully.
x_err_code <> 0 - An error occurred and the extension did not process successfully.
x_err_buf
x_err_buf returns the error description
Related Topics
PL/SQL User's Guide and Reference
Oracle Bills of Material Technical Reference Manual, Release 12