Charts Of Accounts ( COA ) structure – Segments and Value Set

Charts Of Accounts Structure, query is given below. The query here will fetch you following list of columns:

1. Business Unit Name
2. Ledger Name
3. Charts Of Accounts Name
4. Segment Name
5. Value Set Code
6. Value Set Description

Charts Of Accounts Structure Query

select hou.name BU_NAME,
led.NAME Ledger_Name,
Str.STRUCTURE_CODE CHART_OF_ACCOUNTS_Name,
SegInSt.SEGMENT_CODE COA_SEGMENT_NAME,
vs.value_set_code,
vs.description value_set_desc
from hr_operating_units hou,
xla_gl_ledgers led ,
fnd_kf_structures_b Str,
fnd_kf_str_instances_b StrInSt,
fnd_kf_segment_instances SegInSt,
fnd_vs_value_sets vs
where 1=1
and led.ledger_id = hou.set_of_books_id
AND led.CHART_OF_ACCOUNTS_ID = Str.Structure_id
AND Str.KEY_FLEXFIELD_CODE = ‘GL#’
AND Str.Structure_id = StrInSt.Structure_id
and SegInSt.Structure_inStance_id = StrInSt.Structure_inStance_id and vs.VALUE_SET_ID = SegInSt.VALUE_SET_ID

If you look at the query above, you will see that every Business Unit has something called “Set of Books”. This Set Of Books is nothing but the parent of Charts Of Accounts. Other children of Set of books are Currency & Calendar. Now further, this Chart Of accounts is nothing but segmented structure in which various accounts are stored & used for reporting etc. Generally companies in US use 7 or 8 segment structure but of course, it is not the eternal truth and can change company to company.Enterprise Structure

Enterprise Structure in Oracle Fusion

Brief Details: Every Business Unit has a ledger and ledger has a Chart Of Account. Chart of account is nothing but the structure of the accounts like 7 segment or 8 segment. Each segment has a value set attached to validate the values. Now depending on the Charts Of Accounts Structure of your enterprise, each BU can have same or different Ledger ( set of books). This query will give you details of the Chart of account of each BU.

Comments

Post a Comment

Popular posts from this blog

opaque schema xsd (standard file used in OIC)

 opaque schema xsd (standard file used in OIC) You can use an opaque schema in a stage file action Read File or Write File operation without concern for a schema for the file.   The only condition is that whatever is sent to the opaque element in the opaque schema must be base64-encoded data.  or in other words  it is used to decode base64 files Save the below xml as  opaqueschema.xsd  file and then you can use it in the integration. <?xml version = '1.0' encoding = 'UTF-8'?> <schema targetNamespace="http://xmlns.oracle.com/pcbpel/adapter/opaque/" xmlns="http://www.w3.org/2001/XMLSchema" > <element name="opaqueElement" type="base64Binary" /> </schema>
Read more

SQL,PLSQL interview practice and DSA patterns

 --=============sql=========================== --How to find duplicate records with the number they are duplicated? select job,count(*) from emp group by job; --Write a Query to select to the Nth highest salary from a table? select min(sal) from (select  sal from emp order by sal desc) where rownum < 3;   --Can Dual table be deleted or dropped or altered or inserted? select round(123.45) from dual; --it cant be deleted or dropped or altered or inserted --List the emps in dept 20 whose sal is > the avg sal of deptno 10 emps? select * from emp where deptno = 20  and sal>(select avg(sal)from emp where deptno = 10); --List the empno, ename, sal, dname of all the 'Mgrs' and 'Analyst' working in NEWYORK, --DALLAS with an exp more than 7 years without receiving the Comma Asc order of Loc? select empno,ename,sal,dname from emp,dept where emp.deptno= dept.deptno and  loc in('NEWYORK', 'DALLAS') and job in ('manager','analyst')and trunc(mo...
Read more

QR code Generate From Oracle PLSQL

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...
Read more