Monday, October 19, 2009
Function to return COA Account Combination.
Function to return concatenated account from id.
[code]</pre>
CREATE OR REPLACE FUNCTION APPS.GET_CONCAT_ACC (ACC_ID VARCHAR2)
RETURN VARCHAR2 IS
<p style="padding-left:30px;">AC VARCHAR2(100);</p>
BEGIN
/*** Returns Concatenated Accounts ***/
<p style="padding-left:30px;">SELECT ACCOUNT_COMB
INTO AC
FROM ABH_GL_ACC_CONCAT_SEGMENTS
WHERE CODE_COMBINATION_ID = ACC_ID;
RETURN NVL(AC, 'ERROR');</p>
END;
/
<pre>[/code]
This function is based on the View for Chart of Accounts KFF.
Shameem Bauccha
19 October 2009
View for Chart of Accounts KFF
The script below allows you creates a view for your chart of accounts.
Modify to suit your requirements. Note that the value set name of each segment is passed as parameter.
[code]
CREATE OR REPLACE VIEW ABH_GL_ACC_CONCAT_SEGMENTS
AS
SELECT cc.code_combination_id,
cc.chart_of_accounts_id,
cc.detail_posting_allowed_flag post,
cc.detail_budgeting_allowed_flag budget,
cc.account_type,
cc.show_account_type,
cc.enabled_flag,
cc.summary_flag,
--segment: segments used in COA definition
cc.segment1,
cc.segment2,
cc.segment3,
cc.segment4,
cc.segment5,
cc.segment6,
concat(concat(concat(concat(concat(concat(concat(concat(concat(concat(segment1, '-'), segment2),'-'), segment3), '-'), segment4), '-'), segment5), '-'), segment6) account_comb,
get_flex_vs_desc('ABH Company', cc.segment1, '') Company,
get_flex_vs_desc('ABH Cost Center', cc.segment2, '') "Cost Center",
get_flex_vs_desc('ABH Account', cc.segment3, '') "Account",
get_flex_vs_desc('ABH Sub Account', cc.segment4, cc.segment3) "Sub Account",
get_flex_vs_desc('ABH Location', cc.segment5, '') "Location",
get_flex_vs_desc('ABH Entity-Services', cc.segment6, '') "Entity-Services"
FROM GL_CODE_COMBINATIONS_V cc
[/code]
Refer to document 'Fetching Key Flexfield Value Description' for get_flex_vs_desc.
Shameem Bauccha
19 October 2009
Fetching Flexfield Description
[code]
CREATE OR REPLACE FUNCTION APPS.GET_FLEX_VS_DESC (P_VALUE_SET_NAME VARCHAR2, P_FLEX_VALUE VARCHAR2, P_PARENT_FLEX_VALUE VARCHAR2)
RETURN VARCHAR2 IS
DESCRIP VARCHAR2(50);
BEGIN
IF P_PARENT_FLEX_VALUE IS NULL
THEN
/*** In case the value set is independent ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
--AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
ELSE
/*** If the value set is dependent on another value set ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
END IF;
RETURN NVL(DESCRIP, 'ERROR');
END;
/
[/code]
Shameem Bauccha
19 October 2009
CREATE OR REPLACE FUNCTION APPS.GET_FLEX_VS_DESC (P_VALUE_SET_NAME VARCHAR2, P_FLEX_VALUE VARCHAR2, P_PARENT_FLEX_VALUE VARCHAR2)
RETURN VARCHAR2 IS
DESCRIP VARCHAR2(50);
BEGIN
IF P_PARENT_FLEX_VALUE IS NULL
THEN
/*** In case the value set is independent ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
--AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
ELSE
/*** If the value set is dependent on another value set ***/
SELECT DISTINCT DESCRIPTION
INTO DESCRIP
FROM FND_FLEX_VALUES_VL
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = P_VALUE_SET_NAME
AND FLEX_VALUE = P_FLEX_VALUE
AND PARENT_FLEX_VALUE_LOW = P_PARENT_FLEX_VALUE
);
END IF;
RETURN NVL(DESCRIP, 'ERROR');
END;
/
[/code]
Shameem Bauccha
19 October 2009
Saturday, October 3, 2009
Tax Configuration: Create Tax
Platform: R12
Tax Manager --> Tax Configuration --> Taxes
A tax is defined under a tax regime. You need to define a tax regime before you define your tax.
Refer to document ‘Create Tax Regimes’.
Create Tax
Click on ‘Create’.
Main Information
Configure the Tax.
Select the previously defie tax regime code. For your tax to work, define it for party type ‘Operating Unit Owning Tax Content’ in the Configuration Owner.
You may choose to create the new tax from scratch or based on an existing tax (whereby some settings will be defaulted).
If you have localized taxes based on geography, then the need to define your tax zones before.Configure your tax at the appropriate geography type (country, state, city, region etc…). If you are not using tax zones, set your taxes at country level.
You may decide to configure Exchange rate at this level.
You can also set the Reporting Tax Authority and Collecting Tax Authority.
Controls and Defaults
Check the ‘Allow Entry of Manual Tax Lines’ if you intend to allow user to manually input tax lines on invoices. To be able to check that option, you must however check the ‘Allow Overide and Entry of Inclusive Tax Lines’ option also.
In our scenario we will not allow user to manually create tax lines nor will we allow them to override calculated tax lines.
Tax Account Controls
Tax Exeptions/Exemptions Control
Tax Recovery Controls
If you are defining recoverable taxes, then you need to check the ‘Allow Tax Recovery’ option. Along with rate for tax, you will also need to define tax recovery rate.
In our scenario, taxes are not recoverable. However we have defined them as recoverable and set the recovery rate as 0.
Defaults
Tax Reporting Codes
In some countries, you may have specific tax recording requirements. Configure this section if that is the case.
Tax Manager --> Tax Configuration --> Taxes
A tax is defined under a tax regime. You need to define a tax regime before you define your tax.
Refer to document ‘Create Tax Regimes’.
Create Tax
Click on ‘Create’.
Main Information
Configure the Tax.
Select the previously defie tax regime code. For your tax to work, define it for party type ‘Operating Unit Owning Tax Content’ in the Configuration Owner.
You may choose to create the new tax from scratch or based on an existing tax (whereby some settings will be defaulted).
If you have localized taxes based on geography, then the need to define your tax zones before.Configure your tax at the appropriate geography type (country, state, city, region etc…). If you are not using tax zones, set your taxes at country level.
You may decide to configure Exchange rate at this level.
You can also set the Reporting Tax Authority and Collecting Tax Authority.
Controls and Defaults
Check the ‘Allow Entry of Manual Tax Lines’ if you intend to allow user to manually input tax lines on invoices. To be able to check that option, you must however check the ‘Allow Overide and Entry of Inclusive Tax Lines’ option also.
In our scenario we will not allow user to manually create tax lines nor will we allow them to override calculated tax lines.
Tax Account Controls
Tax Exeptions/Exemptions Control
Tax Recovery Controls
If you are defining recoverable taxes, then you need to check the ‘Allow Tax Recovery’ option. Along with rate for tax, you will also need to define tax recovery rate.
In our scenario, taxes are not recoverable. However we have defined them as recoverable and set the recovery rate as 0.
Defaults
Tax Reporting Codes
In some countries, you may have specific tax recording requirements. Configure this section if that is the case.
Tax Configuration: Complete First Party Legal Entity Party Tax Profile
Platform: R12
Tax Manager --> Parties --> Party Tax Profiles
On the Tax Manager Homepage, click on the ‘Go to Task’ icon.
[caption id="" align="alignnone" width="920" caption="Search First Party Legal Entity - Tax Profile"]
[/caption]
Ensure Party Types is ‘First Party Legal Entity’. Type in the Party Name and click on ‘Go’.
[caption id="" align="alignnone" width="893" caption="Party Tax Profiles"]
[/caption]
Click on ‘Update Tax Profile’.
[caption id="" align="alignnone" width="916" caption="Update Party Tax Profile"]
[/caption]
Configure the four sections as appropriate:
- Main:
[caption id="" align="alignnone" width="905" caption="Party Tax Profile - Main"]
[/caption]
- Classifications
[caption id="" align="alignnone" width="893" caption="Party Tax Profile - Classifications"]
[/caption]
- Tax Reporting Codes
[caption id="" align="alignnone" width="905" caption="Party Tax Profile - Tax Reporting Codes"]
[/caption]
- Configuration Options
[caption id="" align="alignnone" width="885" caption="Party Tax Profile - Configuration Option"]
[/caption]
You can see that the Tax Regime is already associated with the First Party Legal Entity.
Shameem Bauccha
1 July 2009
Tax Manager --> Parties --> Party Tax Profiles
On the Tax Manager Homepage, click on the ‘Go to Task’ icon.
[caption id="" align="alignnone" width="920" caption="Search First Party Legal Entity - Tax Profile"]
[/caption]Ensure Party Types is ‘First Party Legal Entity’. Type in the Party Name and click on ‘Go’.
[caption id="" align="alignnone" width="893" caption="Party Tax Profiles"]
[/caption]Click on ‘Update Tax Profile’.
[caption id="" align="alignnone" width="916" caption="Update Party Tax Profile"]
[/caption]Configure the four sections as appropriate:
- Main:
[caption id="" align="alignnone" width="905" caption="Party Tax Profile - Main"]
[/caption]- Classifications
[caption id="" align="alignnone" width="893" caption="Party Tax Profile - Classifications"]
[/caption]- Tax Reporting Codes
[caption id="" align="alignnone" width="905" caption="Party Tax Profile - Tax Reporting Codes"]
[/caption]- Configuration Options
[caption id="" align="alignnone" width="885" caption="Party Tax Profile - Configuration Option"]
[/caption]You can see that the Tax Regime is already associated with the First Party Legal Entity.
Shameem Bauccha
1 July 2009
Tax Configuration: Create Tax Regimes
Platform: R12
Create Tax Regimes
Tax Manager --> Tax Configuration --> Tax Regimes
[caption id="" align="alignnone" width="853" caption="TaxRegimes"]
[/caption]
Click on ‘Create’.
[caption id="" align="alignnone" width="854" caption="Tax Regime - Main Details"]
[/caption]
Expand the ‘Controls and Defaults’ Region and Configure.
[caption id="" align="alignnone" width="890" caption="Tax Regime Controls and Defaults"]
[/caption]
Note that unless you have set the Legal Authority as Reporting Tax Authority or Collecting Tax Authority while creating the Party Tax Profile for the Legal Authority, the Legal Authority will not be available in the drop down list.
See Document ’E-Business Tax Configuration – Tax Authority Party Tax Profile’
Expand the ‘Compounding Level Controls’ region and Configure.
[caption id="" align="alignnone" width="864" caption="Tax Regime Compounding Level Controls"]
[/caption]
When you are done, click on ‘Continue’.
[caption id="" align="alignnone" width="1024" caption="Create Tax Regime - Configuration Option"]
[/caption]
Add your First Party Legal Entity. Choose your ‘Configuration for Taxes and Rules’. If you are not using Common Configuration, specify your ‘Configuration for Product Exceptions’. Specify an effective date. US localizations require Service Subscriptions. Click on ‘Service Subscriptions’ to add Service Provider.
[caption id="" align="alignnone" width="897" caption="Service Subscriptions"]
[/caption]
When you are done, click on ‘Finish’.
[caption id="" align="alignnone" width="900" caption="Tax Regime Creation Confirmation"]
[/caption]
You will get a confirmation that the tax regime was created successfully.
You can search and see that the Regime has been created.
[caption id="" align="alignnone" width="899" caption="Search Tax Regime"]
[/caption]
Shameem Bauccha
1 July 2009
Create Tax Regimes
Tax Manager --> Tax Configuration --> Tax Regimes
[caption id="" align="alignnone" width="853" caption="TaxRegimes"]
[/caption]Click on ‘Create’.
[caption id="" align="alignnone" width="854" caption="Tax Regime - Main Details"]
[/caption]Expand the ‘Controls and Defaults’ Region and Configure.
[caption id="" align="alignnone" width="890" caption="Tax Regime Controls and Defaults"]
[/caption]Note that unless you have set the Legal Authority as Reporting Tax Authority or Collecting Tax Authority while creating the Party Tax Profile for the Legal Authority, the Legal Authority will not be available in the drop down list.
See Document ’E-Business Tax Configuration – Tax Authority Party Tax Profile’
Expand the ‘Compounding Level Controls’ region and Configure.
[caption id="" align="alignnone" width="864" caption="Tax Regime Compounding Level Controls"]
[/caption]When you are done, click on ‘Continue’.
[caption id="" align="alignnone" width="1024" caption="Create Tax Regime - Configuration Option"]
[/caption]Add your First Party Legal Entity. Choose your ‘Configuration for Taxes and Rules’. If you are not using Common Configuration, specify your ‘Configuration for Product Exceptions’. Specify an effective date. US localizations require Service Subscriptions. Click on ‘Service Subscriptions’ to add Service Provider.
[caption id="" align="alignnone" width="897" caption="Service Subscriptions"]
[/caption]When you are done, click on ‘Finish’.
[caption id="" align="alignnone" width="900" caption="Tax Regime Creation Confirmation"]
[/caption]You will get a confirmation that the tax regime was created successfully.
You can search and see that the Regime has been created.
[caption id="" align="alignnone" width="899" caption="Search Tax Regime"]
[/caption]Shameem Bauccha
1 July 2009
Sunday, August 2, 2009
Query to determine approval path for PO documents
Platform: Oracle R 11.5.10
This query can be used to determine which approval path a Requisition or a Purchase Order has taken:
-- For Requisition
select pos.name
from po_requisition_headers_all rh, wf_item_attribute_values av, per_position_structures pos
where av.item_type = rh.wf_item_type
and av.item_key = rh.wf_item_key
and av.name = 'APPROVAL_PATH_ID'
and to_number(av.NUMBER_VALUE) = pos.position_structure_id
and rh.segment1 = '&1' ;
--and rh.org_id = 172; -- You can use your org_id if necessary
-- For Purchase Order
select pos.name
from po_headers_all poh, wf_item_attribute_values av, per_position_structures pos
where av.item_type = poh.wf_item_type
and av.item_key = poh.wf_item_key
and av.name = 'APPROVAL_PATH_ID'
and to_number(av.NUMBER_VALUE) = pos.position_structure_id
and poh.org_id = 103
and poh.po_header_id = 1324; --You need to retrieve your PO_HEADER_ID
This query can be used to determine which approval path a Requisition or a Purchase Order has taken:
-- For Requisition
select pos.name
from po_requisition_headers_all rh, wf_item_attribute_values av, per_position_structures pos
where av.item_type = rh.wf_item_type
and av.item_key = rh.wf_item_key
and av.name = 'APPROVAL_PATH_ID'
and to_number(av.NUMBER_VALUE) = pos.position_structure_id
and rh.segment1 = '&1' ;
--and rh.org_id = 172; -- You can use your org_id if necessary
-- For Purchase Order
select pos.name
from po_headers_all poh, wf_item_attribute_values av, per_position_structures pos
where av.item_type = poh.wf_item_type
and av.item_key = poh.wf_item_key
and av.name = 'APPROVAL_PATH_ID'
and to_number(av.NUMBER_VALUE) = pos.position_structure_id
and poh.org_id = 103
and poh.po_header_id = 1324; --You need to retrieve your PO_HEADER_ID
Subscribe to:
Posts (Atom)