This is a technical document assuming the Functional setup for PLA (Product Line Accounting) including Item Categories, Accounting classes, cost groups had been setup.
Extension hooks are meant to provide a flexibility to customize certain cost Processing areas to meet unique requirements, if different from the base Functionality provided.
Different set of accounting entries (debit - credit records) than those derived by the system can be created. In this case, cost processor doesn't create any distribution on its own.
There are 2 ways of implementing the hook,
1. Account Generation Extension
2. Account Entry Extension
Account Generation Extension:
Account Generation extension allows providing alternate accounting entries.
Cost Management calls the Account Generation extension each time standard or average costing material or standard cost update transactions are performed.
Cost Management provides a template package and functions as a basis for account generation type extensions. The name of the template package is CSTPSCHK.
These accounts are picked and used by the cost processor when the cost processor performs accounting distributions. You can use this extension in a standard or average costing organization. You should not use this client extension if you are using the Accounting Entry Extension.
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.
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.
As per the business scenario for the Purchase Order receipt, we have generated different accounts based on the Accounting Type:
If the Accounting Type is ‘Inv valuation’ we have generated the Material Account from the Account Categories form of Category type ‘BUY’,
If the Accounting Type is ‘Receiving Inspection’ we have generated the ‘Receiving Inventory Account’ from the Receiving Form,
And if the Accounting Type is 'Purchase price variance' or 'rate variance', we have generated the 'Purchase price variance' from the Cost group Form as below,
When the Account Generation extension is implemented using PL/SQL then the call to the workflow is to be removed.
The customized Package CSTPSCHK is part of files CSTSCHKS.pls/CSTSCHKB.pls and herein placed the Package files:
----------------------- CSTSCHKB.pls -------------------------------------
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
CREATE OR REPLACE PACKAGE BODY CSTPSCHK AS
/* $Header: CSTSCHKB.pls 115.11.115100.2 2004/12/13 21:27:54 nnayak ship $ */
-- FUNCTION
-- std_cost_dist_hook Cover routine to allow users to customize.
-- They will be able to circumvent the
-- standard cost distribution process. This is
-- called by inltcp.ppc.
--
--
-- RETURN VALUES
-- integer 1 Hook has been used.
-- 0 Continue cost distribution for this transaction -- as usual.
--
function std_cost_dist_hook(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_USER_ID IN NUMBER,
I_LOGIN_ID IN NUMBER,
I_REQ_ID IN NUMBER,
I_PRG_APPL_ID IN NUMBER,
I_PRG_ID IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
)
return integer IS
BEGIN
o_err_code := '';
o_err_num := 0;
o_err_msg := '';
return 0;
EXCEPTION
when others then
o_err_num := SQLCODE;
o_err_msg := 'CSTPSCHK.STD_COST_DIST_HOOK:' || substrb(SQLERRM,1,150);
return 0;
END std_cost_dist_hook;
-- FUNCTION
-- std_cost_update_hook Cover routine to allow users to customize.
-- They will be able to circumvent the
-- standard cost update process. This is
-- called by cmlicu.ppc.
--
--
-- RETURN VALUES
-- integer 1 Hook has been used.
-- 0 Continue cost distribution for this transaction
-- as ususal.
--
FUNCTION std_cost_update_hook(
i_org_id IN NUMBER,
i_cost_update_id IN NUMBER,
i_user_id IN NUMBER,
i_login_id IN NUMBER,
i_req_id IN NUMBER,
i_prg_appl_id IN NUMBER,
i_prg_id IN NUMBER,
o_err_num OUT NOCOPY NUMBER,
o_err_code OUT NOCOPY VARCHAR2,
o_err_msg OUT NOCOPY VARCHAR2
)
RETURN integer IS
BEGIN
o_err_code := '';
o_err_num := 0;
o_err_msg := '';
return 0;
EXCEPTION
when others then
o_err_num := SQLCODE;
o_err_msg := 'CSTPSCHK.STD_COST_UPDATE_HOOK:' || substrb(SQLERRM,1,150);
return 0;
END std_cost_update_hook;
-- FUNCTION
-- std_get_account_id Cover routine to allow users the flexbility
-- in determining the account they want to
-- post the inventory transaction to.
--
--
-- RETURN VALUES
-- integer >0 User selected account number.
-- -1 Use the default account for distribution.
--
function std_get_account_id(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_DEBIT_CREDIT IN NUMBER,
I_ACCT_LINE_TYPE IN NUMBER,
I_COST_ELEMENT_ID IN NUMBER,
I_RESOURCE_ID IN NUMBER,
I_SUBINV IN VARCHAR2,
I_CG_ID IN NUMBER,
I_EXP IN NUMBER,
I_SND_RCV_ORG IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
)
return integer IS
l_account_num number := -1;
l_txn_type_id number;
l_txn_act_id number;
l_txn_src_type_id number;
l_item_id number;
wf_err_num number := 0;
wf_err_code varchar2(500) ;
wf_err_msg varchar2(500) ;
ln_category_id mtl_categories_b.category_id%TYPE;
lc_material_account MTL_CATEGORY_ACCOUNTS_V.material_account%TYPE;
ln_receiving_account_id rcv_receiving_parameters_v.receiving_account_id%TYPE;
ln_purchase_price_var_account cst_cost_group_accounts.purchase_price_var_account%TYPE;
BEGIN
wf_err_code := '';
wf_err_msg := '';
o_err_num := 0;
o_err_code := '';
o_err_msg := '';
SELECT transaction_type_id,
transaction_action_id,
transaction_source_type_id,
inventory_item_id
INTO l_txn_type_id,
l_txn_act_id,
l_txn_src_type_id,
l_item_id
FROM MTL_MATERIAL_TRANSACTIONS
WHERE transaction_id = I_TXN_ID;
l_account_num := CSTPSCWF.START_STD_WF(i_txn_id, l_txn_type_id,l_txn_act_id,
l_txn_src_type_id, i_org_id,
l_item_id,
i_cost_element_id,1,
i_subinv,i_cg_id,i_resource_id,
wf_err_num, wf_err_code, wf_err_msg);
o_err_num := NVL(wf_err_num, 0);
o_err_code := NVL(wf_err_code, 'No Error in CSTPSWF.START_STD_WF');
o_err_msg := NVL(wf_err_msg, 'No Error in CSTPSWF.START_STD_WF');
-- if -1 then use default account, else use this account for distribution
return l_account_num;
EXCEPTION
when others then
o_err_num := -1;
o_err_code := to_char(SQLCODE);
o_err_msg := 'Error in CSTPSCHK.STD_GET_ACCOUNT_ID:' || substrb(SQLERRM,1,150);
return 0;
END std_get_account_id;
-- FUNCTION
-- std_get_update_acct_id Cover routine to allow users the flexbility
-- in determining the account they want to
-- post the Update transaction to.
--
--
-- RETURN VALUES
-- integer >0 User selected account number
-- -1 Use the default account for distribution.
--
function std_get_update_acct_id(
I_ORG_ID IN NUMBER,
I_TXN_ID IN NUMBER,
I_TXN_TYPE_ID IN NUMBER,
I_TXN_ACT_ID IN NUMBER,
I_TXN_SRC_TYPE_ID IN NUMBER,
I_ITEM_ID IN NUMBER,
I_UPDATE_ID IN NUMBER,
I_DEBIT_CREDIT IN NUMBER,
I_ACCT_LINE_TYPE IN NUMBER,
I_COST_ELEMENT_ID IN NUMBER,
I_RESOURCE_ID IN NUMBER,
I_SUBINV IN VARCHAR2,
I_CG_ID IN NUMBER,
I_EXP IN NUMBER,
I_SND_RCV_ORG IN NUMBER,
O_Err_Num OUT NOCOPY NUMBER,
O_Err_Code OUT NOCOPY VARCHAR2,
O_Err_Msg OUT NOCOPY VARCHAR2
)
return integer IS
l_account_num number := -1;
wf_err_num number := 0;
wf_err_code varchar2(500) ;
wf_err_msg varchar2(500) ;
ln_category_id mtl_categories_b.category_id%TYPE;
lc_material_account MTL_CATEGORY_ACCOUNTS_V.material_account%TYPE;
ln_receiving_account_id rcv_receiving_parameters_v.receiving_account_id%TYPE;
ln_purchase_price_var_account cst_cost_group_accounts.purchase_price_var_account%TYPE;
BEGIN
o_err_num := 0;
o_err_code := '';
o_err_msg := '';
wf_err_code := '';
wf_err_msg := '';
/* Workflow call is commented */
/*
l_account_num := CSTPSCWF.START_STD_WF(i_txn_id,
i_txn_type_id,
i_txn_act_id,
i_txn_src_type_id,
i_org_id,
i_item_id,
i_cost_element_id,
1,
i_subinv,
i_cg_id,
i_resource_id,
wf_err_num,
wf_err_code,
wf_err_msg);
o_err_num := NVL(wf_err_num, 0);
o_err_code := NVL(wf_err_code, 'No Error in CSTPSWF.START_STD_WF');
o_err_msg := NVL(wf_err_msg, 'No Error in CSTPSWF.START_STD_WF');
*/
IF I_ACCT_LINE_TYPE = 1 THEN
BEGIN
SELECT category_id
INTO ln_category_id
FROM mtl_categories_b
WHERE structure_id = 1
AND segment1 = 'BUY';
SELECT mcav.material_account
INTO lc_material_account
FROM MTL_CATEGORY_ACCOUNTS_V mcav
,mtl_parameters mpm
WHERE mpm.organization_id = mcav.organization_id
AND mpm.organization_id = I_ORG_ID
AND mcav.category_id = ln_category_id;
l_account_num := lc_material_account;
fnd_file.put_line(fnd_file.log, 'Material Account for Inv:'||lc_material_account);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_account_num := NULL;
END;
END IF;
IF I_ACCT_LINE_TYPE = 5 THEN
BEGIN
SELECT receiving_account_id
INTO ln_receiving_account_id
FROM rcv_receiving_parameters_v rrpv
,mtl_parameters mpm
WHERE mpm.organization_id = rrpv.organization_id
AND mpm.organization_id = I_ORG_ID;
l_account_num := ln_receiving_account_id;
fnd_file.put_line(fnd_file.log, 'Receiving Account for REC:'||ln_receiving_account_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_account_num := NULL;
END;
END IF;
IF I_ACCT_LINE_TYPE = 6 THEN
BEGIN
SELECT purchase_price_var_account
INTO ln_purchase_price_var_account
FROM CST_COST_GROUP_ACCOUNTS
WHERE cost_group_id = 31843;
l_account_num := ln_purchase_price_var_account;
fnd_file.put_line(fnd_file.log, 'Payab Account for REC:'||ln_purchase_price_var_account);
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_account_num := NULL;
END;
END IF;
return l_account_num;
EXCEPTION
when others then
o_err_num := SQLCODE;
o_err_msg := 'CSTPSCHK.STD_GET_UPDATE_ACCT_ID:' || substrb(SQLERRM,1,150);
return -1;
END std_get_update_acct_id;
-- FUNCTION
-- std_get_update_scrap_acct_id Routine to allow users to select the account
-- to be used for posting scrap adjustments in the
-- std cost update process for standard lot based jobs
--
-- INPUT PARAMETERS
-- I_ORG_ID
-- I_UPDATE_ID
-- I_WIP_ENTITY_ID wip_entity_id of the work order
-- I_DEPT_ID department_id of the department that runs the operation
-- I_OPERATION_SEQ_NUM operation sequence number of the operation
--
-- RETURN VALUES
-- integer -1 Use the department scrap account
-- else use the value returned by this function
--
-- NOTE THE USE OF RESTRICT_REFERERENCES PRAGMA in the function declaration in the pkg spec.
-- This pragma is needed because this function is being called directly in a SQL statement.
-- Hence make sure you do not use any DML statements in this function and in any other
-- procedure or function called by this function
-- Error messages will not be printed in the standard cost update concurrent log file
-- since out variables are not permitted in this function. So make sure you return valid
-- account numbers when you use this function.
function std_get_update_scrap_acct_id(
I_ORG_ID IN NUMBER,
I_UPDATE_ID IN NUMBER,
I_WIP_ENTITY_ID IN NUMBER,
I_DEPT_ID IN NUMBER,
I_OPERATION_SEQ_NUM IN NUMBER
)
return integer IS
l_err_num NUMBER := 0;
l_err_msg VARCHAR2(240);
l_est_scrap_acct_flag NUMBER := 0;
l_cost_adj_acct NUMBER := 0;
BEGIN
l_err_msg := '';
/* Bug #3447776. Check to see if the organization is ESA disabled or if the job is
non-standard. If so, return the WIP standard cost adjustment account. */
l_est_scrap_acct_flag := WSMPUTIL.WSM_ESA_ENABLED(i_wip_entity_id, l_err_num, l_err_msg);
IF l_est_scrap_acct_flag = 1 THEN
return -1;
ELSE
SELECT WDJ.std_cost_adjustment_account
INTO l_cost_adj_acct
FROM wip_discrete_jobs WDJ
WHERE WDJ.wip_entity_id = I_WIP_ENTITY_ID
AND WDJ.organization_id = I_ORG_ID;
return l_cost_adj_acct;
END IF;
EXCEPTION
when others then
return -1;
END std_get_update_scrap_acct_id;
-- FUNCTION
-- std_get_est_scrap_rev_acct_id Routine to allow users to select the account
-- to be used for posting estimated scrap reversal in the
-- Operation Yield Processor for scrap transactions.
--
-- INPUT PARAMETERS
-- I_ORG_ID
-- I_WIP_ENTITY_ID wip_entity_id of the work order
-- I_OPERATION_SEQ_NUM operation sequence number of the operation
--
-- RETURN VALUES
-- integer -1 Use the department scrap account
-- else use the value returned by this function
--
function std_get_est_scrap_rev_acct_id(
I_ORG_ID IN NUMBER,
I_WIP_ENTITY_ID IN NUMBER,
I_OPERATION_SEQ_NUM IN NUMBER
)
return integer IS
BEGIN
return -1;
EXCEPTION
when others then
return -1;
END std_get_est_scrap_rev_acct_id;
END CSTPSCHK;
/
COMMIT;
EXIT;
----------------------- CSTSCHKB.pls -------------------------------------
Implementing Client Extension through Workflow:
The Account Generation Extension workflow is called from the Account Generator Client Extension.
The workflow is designed to provide a graphical user interface to the Client Extension, as well as additional features such as drag–and–drop functionality. The workflow, like the Client Extension, allows you to specify the accounts for distribution based on the type of transaction or to define your own business functions to generate the desired accounts.
You may choose to continue using the Account Generator Client Extension without using the workflow, by turning off the call to the workflow within the client extension.
If you choose to use the workflow, you modify the workflow rather than the Client Extension.
Account Generation Extension Workflow has 19 processes, one for each accounting line type.
The code provided by ‘ORACLE’ for the Account Generation Workflow is available in Package ‘CSTPSCWF’.
A call to the workflow is done by ‘CSTPSCWF.START_STD_WF’ in Package ‘CSTPSCHK’.
And 19 processes for generating different types of accounts are as below,
STDALT1-19 from Account to WIP Variance Processes as example provided above.
But ORACLE only provides code for generating accounts of the following Accounting Line Types:
Product line Material Account (CSTPSCWF.get_std_mtl_pla).
Product line Material Overhead Account (CSTPSCWF.get_std_mo_pla)
Product line Resource Account (CSTPSCWF.get_std_res_pla)
Product line Outside Processing Account (CSTPSCWF.get_std_osp_pla)
Product line Overhead Account (CSTPSCWF.get_std_ovh_pla)
And the above accounts are provided only for the Sub-Inventory and Item Setup. But if we want to implement the Cost Group/Project Level setup then we need to write all the code from the Start, no code provided by ‘ORACLE’ will be useful.
All the processes of the workflow are being initiated depending on the Accounting Line Types ranging from 1-19, and all use the same procedure call ‘CSTPSCWF.get_def_acc’.
So all the code has to be written in the proc CSTPSCWF.get_def_acc or modify the workflow accordingly to use the procedures mentioned above.
Print This Post
Posts
No comments:
Post a Comment