Difference between CHAR and VARCHAR2
The difference between CHAR and VARCHAR2 is CHAR(n) will always be n bytes long, it wilbe blank padded upon insert.But VARCHAR2(N) will be 1 to n bytes long but will not be blank padded.
Using a CHAR on a varying field would be a pain due to the search semantics on CHAR.
NO COPY Hint Usage
Prior to Oracle8i release, the IN/OUT parameters in PL/SQL routines were passed using copy-in and copy-out semantics.
Oracle will copy the value being passed to parameters in seperate buffer cache and on completion of the routine it copies back to the variables in the calling program resulting in multiple buffers being opened in memory and the overhead of copying data back and forth.
The IN parameter is passed by reference i.e a pointer to the actual parameter is passed to the corresponding formal parameter and both of them point to the same location and there is no extra memory buffers and also no copy of values back and forth.
From Oracle 8i onwards the NOCOPY parameter HINT had been introduced for OUT and INOUT parameters by which Oracle makes the call by reference approach for both OUT and INOUT parameters.
NOCOPY is the ideal hint for OUT and INOUT parameters when the original value is not to be preserved.
Drawbacks:
-----------
NOCOPY is an HINT and Oracle does not guarantee passing a parameter as reference in the following situations:
1. When the call is a remote procedure call.
2. When the actual parameter being passed is an expression.
3. When there is an implicit conversion involved.
DB Link to a Remote Database
Whenever we have a need to access tables,views etc from a remote database in a Local database we can create a DB link and access the tables and views.
Syntax:
CREATE DATABASE LINK
CONNECT TO "DBId"
IDENTIFIED BY "Pwd"
USING 'Sch_ma_name';
We will be able to INSERT/UPDATE/DELETE data of the remote database from the local database.
And the rest of the operations depend on the avilability of the access to the current user.
We can create PUBLIC/SHARED DB Links.
Autonomous Transactions
In PL/SQL transaction processing takes place serially i.e a new transaction can begin only when an earlier transaction ends, in which case the user issues an explicit COMMIT/ROLLBACK.
But often in our applications we may require to commit or rollback changes without effecting the main transaction in the session.
From PL/SQL 8.1 onwards it is possible to achieve the goal of having to rollback some changes in the main transaction while committing the log entry( the transaction history) by issuing the PL/SQL compiler directive AUTONOMOUS_TRANSACTION.
So inorder to use Autonomous transactions we simple need to use the following directive in the delaration section of the procedure:PRAGMA AUTONOMOUS_TRANSACTION
Create or Replace procedure kiran_p(text VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert into kiran(text) values(text);
COMMIT;
END;
The block in this example becomes an independent transaction and the COMMIT issued in this block will not affect the SQL statements in the main transaction.
Rules of Autonomous transactions:
----------------------------------
1. Nested blocks cannot be declared as autonomous transactions.
2. Autonomous transaction must have COMMIT or ROLLBACK before it completes its
execution, otherwise it returns the following error:
ORA-06519: active autonomous transaction detected and rolled back.
3. As the Autonomous transaction is an independent transaction you cannot Rollback to the
savepoint defined in the main transaction.
4. Again calling one autonomous transaction from the main transaction in your session implies
the session is concurrently running two sessions and so on.And the number of those sessions
possible were determined by the init.ora parameter LIST.
5. You cannot declare package as an autonomous transaction to make all procedures/functions
in the package as autonomous transactions.
6. If an autonomous transaction tries to access the resource held by the main transaction that is
suspended it will result in a deadlock.
Print This Post
Friday 17 April 2009
Oracle Cost Management 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.
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,
Account Generation 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,
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,
- Account Generation Extension
- 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.
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:
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
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
Oracle EBS AOL Faq
Date Parameter in a PL/SQL Concurrent program
suppose we want to pass the date parameter in a PL/SQL concurrent program, declaring a date parameter in a PL/SQL code mapping to the concurrent program parameter errors the program, and we should define the character parameter in PL/SQL and convert the same to date as per the usage. but we have a problem of using the date format to convert the character to date. so to avoid these date format errors oracle provides an API, fnd_date.canonical_to_date.
suppose we have a character variable holding date to be p_start_date,
fnd_date.canonical_to_date(p_start_date) returns date format value.
Show Requests Window after Each Request Submission
To show a small request window whether user would like to submit another request to retain Request submission screen, set the following profile option:
Concurrent: Show Requests Summary After Each Request Submission to 'No'.
The default value for this profile option is 'Yes', which shows the Request Summary screen each time you submit the request.
If 'No' is chosen for this profile option then a decision window is opened asking if you wish to submit another request.
Please check the link for additional Profile option settings
Disabling Concurrent program output:
Sometimes we may be required to disable the output of the concurrent program, considering to disable the output of a concurrent program we need to update the output file, output node location to NULL in the table FND_CONCURRENT_REQUESTS as below,
UPDATE FND_CONCURRENT_REQUESTS
SET outfile_name = null
,outfile_node_name = null
WHERE request_id = :CONC_REQUEST_ID;
How to Make a responsibility as Read Only for a User:
1. Open the CUSTOM.pll in the Form Builder
2. Add the following sample code in the Procedure event for the event 'WHEN-NEW-FORM-INSTANCE'.
3. Compile the CUSTOM.pll and plce under the directory $AU_TOP/resource
The Following sample code makes a responsibility Read-Only for a user,
BEGIN
IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN
BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);
WHILE (blockname is not null) LOOP
IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
END LOOP;
END query_only_mode;
END;
Please check metalink note 363298.1 for any additional info
Trace File Location in Oracle Server:
The trace files are usually located in the udump directory for the Oracle Server.
The exact path can be found from the V$Parameter table which is usually the initial setup procedure for Oracle server:
SELECT name,value from v$parameter WHERE name like 'user_dump_dest';
Oracle Auditing:
Oracle Applications Audit Trails maintains full history of changes made at a table level and column level.
Audit Trails are enabled by a shadow table (Table name appended with _A) of the audited table and triggers on the audited columns.
Concurrent program ‘Run Audit Trail’ is used to create the shadow table and triggers.
Auditing database row changes is a very performance intensive and can cause significant database performance problems. Careful planning and reviews should be done with DBA before enabling auditing. Only a minimal amount of auditing should be done and only to Non-transactional data. Auditing on transactional data could cause significant performance degradation of the entire application, also table with more than a few changes a hour in customer business usage should not be considered for auditing.
The Following setup should be done before proceeding with the Audit:
1. Set the profile option Audit Trail: Activate to ‘Yes’.
2. Select the Audit installations
System Administrator -> Security -> Audi Trail -> Install
All the Schemas for which Auditing to be enabled must be checked.
3. Create a new Audit Group which contains the tables to be audited secured under particular application.
Define Table Columns to be audited,
System Administrator -> Security -> Audi Trail -> Tables
And the following columns should not be included as the user information is automatically added,
Creation Date, Created By, Last update Login, Last Update Date, Last Updated By
4. Run Audit Trail Update Program
This activates the auditing, and
· Creates a shadow table with the same name as original table but appended by ‘_A’.
· Creates trigger for each audited column.
· Creates 2 views for each audited column appended with ‘_AC#’ AND ‘_AV#’ where # is a sequential number.
The AuditTrail table data can only be accessed by the underlying shadow tables via SQL only, there are no Standard Oracle Application reports to access Audit Trail Data.
The AuditTrail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging,
the Procedure
·System Administrator -> Security -> Audi Trail -> Groups
· Select the 'Security Audit' group and set the group state to 'Disable – Purge Table'
· Run the ‘Audit Trail Update Tables’ Report
· Purge the unwanted data from the shadow tables, be careful that the Reports developed on the historic data could be impacted with this process.
· System Administrator -> Security -> Audi Trail -> Groups
· Select the “Security Audit” group and set the group state to “Enable” Run the ‘Audit Trail Update Tables’ Report
FNDLOAD Commands
Most of the times Oracle Application Custom Developers creates custom menu and a custom responsibility in one of the development instance. The following commands are useful in migrating these to other environments using FNDLOAD/FNDSLOAD.
Please find the commands to download the data using FNDLOAD utility
Printer Styles:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME="printer style name"
Lookups:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="prod"
LOOKUP_TYPE="lookup name"
Descriptive Flexfield with all of specific Contexts:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=’COL_ALL:REF_ALL:CTX_ONE:SEG_ALL’ APPLICATION_SHORT_NAME="prod" DESCRIPTIVE_FLEXFIELD_NAME="desc flex name" P_CONTEXT_CODE="context name"
Key Flexfield Structures:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=’COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL’ APPLICATION_SHORT_NAME="prod" ID_FLEX_CODE="key flex code" P_STRUCTURE_CODE="structure name"
Concurrent Programs:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME="prod" CONCURRENT_PROGRAM_NAME="concurrent name"
Value Sets:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME="value set name"
Value Sets with values:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VSET_VALUE FLEX_VALUE ="flexfield segment value"
Profile Options:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME="profile option" APPLICATION_SHORT_NAME="prod"
Request Groups:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME="request group" APPLICATION_SHORT_NAME="prod"
Request Sets:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Request Set Stage Links:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET_LINKS
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Responsibilities:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY="responsibility"
Menus:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME="menu_name"
Messages:
FNDLOAD apps/apps@ 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct file_name.ldt FND_NEW_MESSAGES MESSAGE_NAME="message_name" APPLICATION_SHORT_NAME=”prod”
XDO Definition:
FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=”prod” DATA_SOURCE_CODE=”Data Def Name”
Functions:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=”Function Name”
Templates from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbptmpl.lct file_name.ldt AR_BPA_TEMPLATES
Items from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbpitem.lct file_name.ldt AR_BPA_ITEM
Data Sources from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbpds.lct file_name.ldt AR_BPA_DATA_SOURCES
Oracle Alerts:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME=”prod”
Form Personalization:
FND_TOP/bin/FNDLOAD/ 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct FND_FORM_CUSTOM_RULES form_name=
suppose we want to pass the date parameter in a PL/SQL concurrent program, declaring a date parameter in a PL/SQL code mapping to the concurrent program parameter errors the program, and we should define the character parameter in PL/SQL and convert the same to date as per the usage. but we have a problem of using the date format to convert the character to date. so to avoid these date format errors oracle provides an API, fnd_date.canonical_to_date.
suppose we have a character variable holding date to be p_start_date,
fnd_date.canonical_to_date(p_start_date) returns date format value.
Show Requests Window after Each Request Submission
To show a small request window whether user would like to submit another request to retain Request submission screen, set the following profile option:
Concurrent: Show Requests Summary After Each Request Submission to 'No'.
The default value for this profile option is 'Yes', which shows the Request Summary screen each time you submit the request.
If 'No' is chosen for this profile option then a decision window is opened asking if you wish to submit another request.
Please check the link for additional Profile option settings
Disabling Concurrent program output:
Sometimes we may be required to disable the output of the concurrent program, considering to disable the output of a concurrent program we need to update the output file, output node location to NULL in the table FND_CONCURRENT_REQUESTS as below,
UPDATE FND_CONCURRENT_REQUESTS
SET outfile_name = null
,outfile_node_name = null
WHERE request_id = :CONC_REQUEST_ID;
How to Make a responsibility as Read Only for a User:
1. Open the CUSTOM.pll in the Form Builder
2. Add the following sample code in the Procedure event for the event 'WHEN-NEW-FORM-INSTANCE'.
3. Compile the CUSTOM.pll and plce under the directory $AU_TOP/resource
The Following sample code makes a responsibility Read-Only for a user,
BEGIN
IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='
BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);
WHILE (blockname is not null) LOOP
IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN
SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);
END IF;
blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);
END LOOP;
END query_only_mode;
END;
Please check metalink note 363298.1 for any additional info
Trace File Location in Oracle Server:
The trace files are usually located in the udump directory for the Oracle Server.
The exact path can be found from the V$Parameter table which is usually the initial setup procedure for Oracle server:
SELECT name,value from v$parameter WHERE name like 'user_dump_dest';
Oracle Auditing:
Oracle Applications Audit Trails maintains full history of changes made at a table level and column level.
Audit Trails are enabled by a shadow table (Table name appended with _A) of the audited table and triggers on the audited columns.
Concurrent program ‘Run Audit Trail’ is used to create the shadow table and triggers.
Auditing database row changes is a very performance intensive and can cause significant database performance problems. Careful planning and reviews should be done with DBA before enabling auditing. Only a minimal amount of auditing should be done and only to Non-transactional data. Auditing on transactional data could cause significant performance degradation of the entire application, also table with more than a few changes a hour in customer business usage should not be considered for auditing.
The Following setup should be done before proceeding with the Audit:
1. Set the profile option Audit Trail: Activate to ‘Yes’.
2. Select the Audit installations
System Administrator -> Security -> Audi Trail -> Install
All the Schemas for which Auditing to be enabled must be checked.
3. Create a new Audit Group which contains the tables to be audited secured under particular application.
Define Table Columns to be audited,
System Administrator -> Security -> Audi Trail -> Tables
And the following columns should not be included as the user information is automatically added,
Creation Date, Created By, Last update Login, Last Update Date, Last Updated By
4. Run Audit Trail Update Program
This activates the auditing, and
· Creates a shadow table with the same name as original table but appended by ‘_A’.
· Creates trigger for each audited column.
· Creates 2 views for each audited column appended with ‘_AC#’ AND ‘_AV#’ where # is a sequential number.
The AuditTrail table data can only be accessed by the underlying shadow tables via SQL only, there are no Standard Oracle Application reports to access Audit Trail Data.
The AuditTrail information should be purged on a periodic basis. There is no standard purge program and the AuditTrail must be manually disabled to permit purging,
the Procedure
·System Administrator -> Security -> Audi Trail -> Groups
· Select the 'Security Audit' group and set the group state to 'Disable – Purge Table'
· Run the ‘Audit Trail Update Tables’ Report
· Purge the unwanted data from the shadow tables, be careful that the Reports developed on the historic data could be impacted with this process.
· System Administrator -> Security -> Audi Trail -> Groups
· Select the “Security Audit” group and set the group state to “Enable” Run the ‘Audit Trail Update Tables’ Report
FNDLOAD Commands
Most of the times Oracle Application Custom Developers creates custom menu and a custom responsibility in one of the development instance. The following commands are useful in migrating these to other environments using FNDLOAD/FNDSLOAD.
Please find the commands to download the data using FNDLOAD utility
Printer Styles:
FNDLOAD apps/apps@
Lookups:
FNDLOAD apps/apps@
LOOKUP_TYPE="lookup name"
Descriptive Flexfield with all of specific Contexts:
FNDLOAD apps/apps@
Key Flexfield Structures:
FNDLOAD apps/apps@
Concurrent Programs:
FNDLOAD apps/apps@
Value Sets:
FNDLOAD apps/apps@
Value Sets with values:
FNDLOAD apps/apps@
Profile Options:
FNDLOAD apps/apps@
Request Groups:
FNDLOAD apps/apps@
Request Sets:
FNDLOAD apps/apps@
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Request Set Stage Links:
FNDLOAD apps/apps@
APPLICATION_SHORT_NAME="prod" REQUEST_SET_NAME="request set"
Responsibilities:
FNDLOAD apps/apps@
Menus:
FNDLOAD apps/apps@
Messages:
FNDLOAD apps/apps@
XDO Definition:
FNDLOAD apps/apps 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct file_name.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=”prod” DATA_SOURCE_CODE=”Data Def Name”
Functions:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=”Function Name”
Templates from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbptmpl.lct file_name.ldt AR_BPA_TEMPLATES
Items from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbpitem.lct file_name.ldt AR_BPA_ITEM
Data Sources from BPA:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$AR_TOP/patch/115/import/arbpds.lct file_name.ldt AR_BPA_DATA_SOURCES
Oracle Alerts:
FND_TOP/bin/FNDLOAD apps/apps 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct file_name.ldt ALR_ALERTS APPLICATION_SHORT_NAME=”prod”
Form Personalization:
FND_TOP/bin/FNDLOAD
Oracle Workflow FAQ
Why Workflow why not plain PL/SQL
Some of the intriguing facts of why workflow is used rather than plain pl/sql is due to following reasons,
Workflow Upload Errors
Sometimes uploading/saving a workflow to the database may cause constraint errors as below,
- 1602: Could not save.
- 1400: Could not save to database. MODE=UPGRADE EFFDATE=
- 1401: Could not upload ACTIVITY entity 'POAPPRV/POAPPRV_TOP'.
- 210: Oracle Error: ORA-00001: unique constraint (APPLSYS.WF_PROCESS_ACTIVITIES_U2) violated
ORA-01403: no data found
ORA-06512: at "APPS.WF_LOAD", line 1988
ORA-06512: at line 1
. SQL text: BEGIN WF_LOAD.UPLOAD_PROCESS_ACTIVITY(:process_item_type, :process_name, :process_version, :activity_item_type, :activity_name, :instance_id, :instance_label, :protect, :custom, :start_end, :default_result, :icon_geometry, :perform_role, :perform_role_type, :user_comment, :level_error); END;
So to avoid these kind of errors we need to remove the workflow from the database and then re-load the workflow.
Offending rows are found byWorkflow should be removed from the database by the script provided by oracle at $FND_TOP/sql/wfrmitt.sql
This takes ItemType as the parameter.
Now upload the workflow from the command line with the following command,
WFLOAD apps/apps@ XXXX.wft
UPLOAD - honors both protection and customization level of the workflow
UPGRADE - honors only protection level of data
FORCE - FORCE upload regardless of protection or customization levels.
Hiding 'Request Information' button in an Email Notification
Sometimes you may need to hide the Request Information button for an Email-notification according to client business requirements. For this oracle had provided a special message attribute with the internal name #HIDE_MOREINFO to hide the Request Information button in the notification details page.
Usually when users view a notification that require a response from their worklist page, the response region in the notification details page includes the Request Information button by default. If you want to prevent users from requesting more information about a notification, you can add #HIDE_MOREINFO attribute to control whether the Request Information button is displayed or hidden.
The #HIDE_MOREINFO attribute must be either of type text or Lookup added to the associated message. To hide Request Information button, set the value of this attribute to Y as below. To display the Request Information button, set the value to N.
Print This Post
Some of the intriguing facts of why workflow is used rather than plain pl/sql is due to following reasons,
- Simplicity of the solution
- Easier to develop separate small activities(procedure) instead of single big one.
- Easy for future re-usability by just changing the Workflow activities
- Easier to change the order of processing by moving the activities
- Saving time by just moving the activities
- and most interestingly, Visible solution to provide an overview of business process.
Workflow Upload Errors
Sometimes uploading/saving a workflow to the database may cause constraint errors as below,
- 1602: Could not save.
- 1400: Could not save to database. MODE=UPGRADE EFFDATE=
- 1401: Could not upload ACTIVITY entity 'POAPPRV/POAPPRV_TOP'.
- 210: Oracle Error: ORA-00001: unique constraint (APPLSYS.WF_PROCESS_ACTIVITIES_U2) violated
ORA-01403: no data found
ORA-06512: at "APPS.WF_LOAD", line 1988
ORA-06512: at line 1
. SQL text: BEGIN WF_LOAD.UPLOAD_PROCESS_ACTIVITY(:process_item_type, :process_name, :process_version, :activity_item_type, :activity_name, :instance_id, :instance_label, :protect, :custom, :start_end, :default_result, :icon_geometry, :perform_role, :perform_role_type, :user_comment, :level_error); END;
So to avoid these kind of errors we need to remove the workflow from the database and then re-load the workflow.
Offending rows are found by
This takes ItemType as the parameter.
Now upload the workflow from the command line with the following command,
WFLOAD apps/apps@
UPLOAD - honors both protection and customization level of the workflow
UPGRADE - honors only protection level of data
FORCE - FORCE upload regardless of protection or customization levels.
Hiding 'Request Information' button in an Email Notification
Sometimes you may need to hide the Request Information button for an Email-notification according to client business requirements. For this oracle had provided a special message attribute with the internal name #HIDE_MOREINFO to hide the Request Information button in the notification details page.
Usually when users view a notification that require a response from their worklist page, the response region in the notification details page includes the Request Information button by default. If you want to prevent users from requesting more information about a notification, you can add #HIDE_MOREINFO attribute to control whether the Request Information button is displayed or hidden.
The #HIDE_MOREINFO attribute must be either of type text or Lookup added to the associated message. To hide Request Information button, set the value of this attribute to Y as below. To display the Request Information button, set the value to N.
Print This Post
Oracle Project Contracts
- Why and what is Project Contracts?
- Contract Attributes
- Contract Creation
- Contract Funding, Fund Allocations and Agreements
- Contract Billing
- Transfer Contract Events to AR
- Shipping Contracts to OM
- OKC and OKE Tables
- Contracts Migration API
- Contracts Access By Role
- Contracts Change Management
Why and What is Project Contracts ?
Oracle Project Contracts support complex project contract management needs of project driven organizations including commercial and government contractors, agencies and subcontractors characterized by,
Contract Attributes:
Contract Creation
Contract can be created either manually or can be copied from the existing contract, where we copy all the attributes, options of the corresponding contract as shown in the initial form below,
Contracts Super user -> Contract organizer -> New Document
And on Clicking the Next button, please enter the corresponding contract attributes and click Finish button,
Contracts similar to most of the data model objects have a header and a line storing the corresponding items.
This creates a contract header as shown below,
You need to enter the fields’ come-into Force date, Owning Organization, Project and the rest all mandatory details.
For the contract to provide Billing, In the Financial list of the Contract header Check the Definitized or Bill Without Definitized Check boxes.
And enter the contract line by clicking on the Contract Line Tab and clicking o the Add (+) button in the menu.
Also make sure that the Billable Check box is checked for creating billing events and Shippable, which is used for shipping the items to OM.
Contract Header Tables,
OKE_K_HEADERS : This stores data related to only Oracle Project Contracts
OKC_K_HEADERS_B: This is the Contract Core base table upon which OKE_K_HEADERS is built and both have one to one relationship.
Contract Lines Tables,
OKE_K_LINES : This stores Line data related to only Oracle Project Contracts
OKC_K_LINES_B : This is the Contract Core base table upon which OKE_K_LINES is built and both have one to one relationship.
And here after creating the header and line with/without items, Change to the ‘Administration’ tab where we should make our self as the Contract Administrator to own the rights of the contract.
For this, we need to be,
Approve and Sign the document through the Contract Approval Workflow process in the Workflow Administrator Web Applications responsibility.
Once the contract Approval Workflow is approved, the contract status is modified from ‘Entered’ to ‘Active’, and the contract becomes ‘Active’ as shown below,
And now, on clicking the ‘Go To’ button as we have associated our self a ‘Contract Administrator’ privileges, we would be able to see the list of associated functions for the contract as shown below,
And if we select ‘Authoring Workbench’, it displays the contract workbench as below,
Now, enter the details of funding the contract from the parties specified in previous section.
The customers who have been assigned the Fund by authority can only fund the contract.
Once the funding had been created for the contract, we need to create allocation to the respective contract Header/Line and the project associated,
Click the Allocations Button, and enter the contract header. Line and amount to allocate
Managing Contract Billing
For creating the Billing, we need to have the deliverables.
For creating the deliverables, we can ‘Default the deliverables’ or create manual deliverable. Lets default the deliverable as
Action --> Default Deliverable
Once the deliverables are defaulted, you can proceed to deliverable tracking system, open the deliverables and also check that the Billable, and Ready to Bill columns are checked for the billing to be created for the associated project.
Now, we could see the event being transferred to Projects(PA), and we shall see the same in PA,
Project Billing Super User --> Billing à Events --> Project --> Find Project 100000489
- Ever changing contract specifications
- Procure-contract components and services
- Managing Contract Billing
- Contract Funding in Multiple Currencies
- Compliance of government regulations
- Deliverable tracking integrated with ERP functions like procurement, planning and production
- Flowdown of contract information to subcontractors and prioritization of contract deliverable.
- Audit trial for all contracts
Contract Attributes:
- Type: This indicates whether the contract is an Award, Bid, Proposal, subcontract etc.,
- Intent: This indicates the main objective of the contract is either to Buy or Sell. Buy is used for Subcontracted deliverable or as a customer Buy document. Sell is used for all outbound deliverable.
- Number: Number of the Contract, enabled only if document type numbering is set to ‘Manual’.
- Currency: Currency in which the contract amounts and prices are defined.
- Start Date: Start Date of the Contract
- Item Master Org: The manufacturing organization where you have defined inventory items; it is a manufacturing entity.
Contract Creation
Contract can be created either manually or can be copied from the existing contract, where we copy all the attributes, options of the corresponding contract as shown in the initial form below,
Contracts Super user -> Contract organizer -> New Document
And on Clicking the Next button, please enter the corresponding contract attributes and click Finish button,
Contracts similar to most of the data model objects have a header and a line storing the corresponding items.
This creates a contract header as shown below,
You need to enter the fields’ come-into Force date, Owning Organization, Project and the rest all mandatory details.
For the contract to provide Billing, In the Financial list of the Contract header Check the Definitized or Bill Without Definitized Check boxes.
And enter the contract line by clicking on the Contract Line Tab and clicking o the Add (+) button in the menu.
Also make sure that the Billable Check box is checked for creating billing events and Shippable, which is used for shipping the items to OM.
Contract Header Tables,
OKE_K_HEADERS : This stores data related to only Oracle Project Contracts
OKC_K_HEADERS_B: This is the Contract Core base table upon which OKE_K_HEADERS is built and both have one to one relationship.
Contract Lines Tables,
OKE_K_LINES : This stores Line data related to only Oracle Project Contracts
OKC_K_LINES_B : This is the Contract Core base table upon which OKE_K_LINES is built and both have one to one relationship.
And here after creating the header and line with/without items, Change to the ‘Administration’ tab where we should make our self as the Contract Administrator to own the rights of the contract.
For this, we need to be,
- Defined as an employee and assigned a Job in which Business unit/Organization is the contract being defined.
- And the Employee name is registered as the contact person to the Oracle Applications User.
- Assign yourself as a contract Administrator as shown below and also associate the contract Approval workflow and Start it to make the Contract Active.
Approve and Sign the document through the Contract Approval Workflow process in the Workflow Administrator Web Applications responsibility.
Once the contract Approval Workflow is approved, the contract status is modified from ‘Entered’ to ‘Active’, and the contract becomes ‘Active’ as shown below,
And now, on clicking the ‘Go To’ button as we have associated our self a ‘Contract Administrator’ privileges, we would be able to see the list of associated functions for the contract as shown below,
And if we select ‘Authoring Workbench’, it displays the contract workbench as below,
Contract Funding, Fund Allocations and Agreements
Funding: Funding for the Contract is done in Oracle Project Contracts, and funding for the Project is done in Oracle Projects
For the Funding to be done at the contract level, there should be a funding source determined by ‘Fund By’ in the ‘Parties and Contacts’ Tab of Contract Authoring Workbench as below,
And now we shall navigate to Funding
Contract Organizer (00001) --> Got To --> Funding Workbench
Funding: Funding for the Contract is done in Oracle Project Contracts, and funding for the Project is done in Oracle Projects
For the Funding to be done at the contract level, there should be a funding source determined by ‘Fund By’ in the ‘Parties and Contacts’ Tab of Contract Authoring Workbench as below,
And now we shall navigate to Funding
Contract Organizer (00001) --> Got To --> Funding Workbench
And proceed by clicking 'Next',
Now, enter the details of funding the contract from the parties specified in previous section.
The customers who have been assigned the Fund by authority can only fund the contract.
Once the funding had been created for the contract, we need to create allocation to the respective contract Header/Line and the project associated,
Click the Allocations Button, and enter the contract header. Line and amount to allocate
Now, once entered Create agreement by clicking the Create/Update Agreement button, this created entries in PA.
Here we have created funding and transferred the same to PA as agreement.
Here we have created funding and transferred the same to PA as agreement.
For creating the Billing, we need to have the deliverables.
For creating the deliverables, we can ‘Default the deliverables’ or create manual deliverable. Lets default the deliverable as
Action --> Default Deliverable
Once the deliverables are defaulted, you can proceed to deliverable tracking system, open the deliverables and also check that the Billable, and Ready to Bill columns are checked for the billing to be created for the associated project.
And now, we shall initiate billing for the events to the projects,
Go To --> Deliverable Based Billing --> Find --> View Events --> New
Enter the Event Type, Billing Task and initiate the Billing,
Go To --> Deliverable Based Billing --> Find --> View Events --> New
Enter the Event Type, Billing Task and initiate the Billing,
Project Billing Super User --> Billing à Events --> Project --> Find Project 100000489
Open the event,
Transfer Contract entries to AR Invoice
Oracle project contract entries can be transferred to AR by running the following programs in the specified order and with the following mandatory steps,
Mandatory steps for creating the Sales order Invoices in AR from PA events,
Base-lining the project funding:
Project Superuser -> Billing -> Funding Enquiry -> Click on Baseline Funding
Baselining the Funding automatically enters the Revenue cost Budget.
The Approved cost budget should be entered as below,
Project Superuser -> Budgets
select Approved Cost Budget
Move to Details, enter the amount for the cost budget, save and close. Submit
Now Baseline the budget amount.
The currency the invoice is created depending on the profile option 'Invoice by Bill Transaction Currency' setup at the project level.
OKC and OKE Tables
Contracts Migration API
Contract migration does not happen as the same traditional way of copying data from legacy systems to Interface tables and from interface tables to base tables because oracle does not provide any interface tables for Contracts, but of course provided some other methodology of importing data through the API.
Contracts constitutes the following data,
Transfer Contract entries to AR Invoice
Oracle project contract entries can be transferred to AR by running the following programs in the specified order and with the following mandatory steps,
Mandatory steps for creating the Sales order Invoices in AR from PA events,
- The Project Funding should be base-lined.
- Approved cost Budget and Approved Revenue Budget should be entered for the project.
Base-lining the project funding:
Project Superuser -> Billing -> Funding Enquiry -> Click on Baseline Funding
Baselining the Funding automatically enters the Revenue cost Budget.
The Approved cost budget should be entered as below,
Project Superuser -> Budgets
select Approved Cost Budget
Move to Details, enter the amount for the cost budget, save and close. Submit
Now Baseline the budget amount.
After performing the above setups, run the following programs for the project
PRC: Generate draft invoice for a single project
The output shows the details of the draft invoice.
Now, query the invoice and approve as below,
Project Status Enquiry -> Project Status Enquiry
Open the Invoices, Approve and then Release
Now, Run program PRC: Interface Invoices to receivables
The output shows the details of the invoice,
The interfaced invoice lines had to be imported to AR, and this is done by program,
Autoinvoice import program as below
And the AR invoice is created as below,
PRC: Generate draft invoice for a single project
The output shows the details of the draft invoice.
Now, query the invoice and approve as below,
Project Status Enquiry -> Project Status Enquiry
Open the Invoices, Approve and then Release
Now, Run program PRC: Interface Invoices to receivables
The output shows the details of the invoice,
The interfaced invoice lines had to be imported to AR, and this is done by program,
Autoinvoice import program as below
And the AR invoice is created as below,
The currency the invoice is created depending on the profile option 'Invoice by Bill Transaction Currency' setup at the project level.
OKC and OKE Tables
Contracts Migration API
Contract migration does not happen as the same traditional way of copying data from legacy systems to Interface tables and from interface tables to base tables because oracle does not provide any interface tables for Contracts, but of course provided some other methodology of importing data through the API.
Contracts constitutes the following data,
- Contracts headers
- Contract Party
- Contract Contact
- Contract Terms
- Contracts Lines
- Contract Deliverables
- Contract Billing events
- Contract Funding and Fund Allocations
Contracts Access By Role
usually Contracts are not accessed by any other user unless he owns the contract administrator role assigned in the Authoring workbench.
The Contract access can be provided to any user by the contract administrator in the Contracts access screen at:
OKE Contracts --> Security --> Contract Accesses
And the access can be provided of these following roles,
--> Contract Administrator
--> Program Manager
--> Project Accountant
--> Project Controller
Print This Post
Subscribe to:
Posts (Atom)