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

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

Scripts - Flexfield Values

Key Flexfields are stored in the following tables:

1. FND_FLEX_VALUE_SETS


2. FND_FLEX_VALUES

SELECT *
FROM FND_FLEX_VALUES
WHERE FLEX_VALUE_SET_ID =
(
SELECT FLEX_VALUE_SET_ID
FROM FND_FLEX_VALUE_SETS
WHERE FLEX_VALUE_SET_NAME = :VALUE_SET_NAME
)
AND ENABLED_FLAG = 'Y' -- For Active Values
AND SUMMARY_FLAG = 'N' -- For Child Values

Pass the Value Set Name as Parameter

Technical - Territory / Jurisdiction Tables

Below is the list of tables concerned when you create territories, jurisdictions, legal entities, establishments and registrations:

1. XLE_JURISDICTIONS_VL


2. XLE_REGISTRATIONS


3. XLE_ENTITY_PROFILES


4. XLE_ETB_PROFILES


5. XLE_FIRSTPARTY_INFORMATION_V


6. XLE_REGISTRATIONS

I had an issue with creation on Legal Entities in R12.0.4. When I created the legal entity, it created the legal entity twice and created 2 registrations as well. I was unable to disable one of them as any change to one would reflect on the other.

As a consequence when I was attaching the Legal Entity in my accounting setup, I had to attach both legal entities. This was not acceptable however.

When I checked, even the jurisdiction was created twice. Going through the above tables and clearing the inappropiate lines resolved the issue.

Note however that this is not a recommended solution.

Shameem Bauccha

18 June 2009