Code Combinations CCID

GL : Code Combinations CCIDs:-
------------------------------------

The GL Code Combinations extract can be select by chart of accounts, specific segment values or specific code combination attributes.

This can be used for chart of accounts maintenance and review

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

SELECT FST.ID_FLEX_STRUCTURE_NAME, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
, GCC.CODE_COMBINATION_ID
, GCC.LAST_UPDATE_DATE
, GCC.JGZZ_RECON_FLAG
, GCC.START_DATE_ACTIVE
, GCC.END_DATE_ACTIVE
, GCC.DETAIL_POSTING_ALLOWED_FLAG
, GCC.ENABLED_FLAG
, GCC.SUMMARY_FLAG
, GCC.START_DATE_ACTIVE
FROM GL_CODE_COMBINATIONS GCC
, FND_ID_FLEX_STRUCTURES_VL FST
WHERE FST.ID_FLEX_NUM = GCC.CHART_OF_ACCOUNTS_ID
AND FST.APPLICATION_ID = 101
AND FST.ID_FLEX_CODE = 'GL#'
--AND GCC.SEGMENT1 IN ('25','26','30')
--AND SUBSTR(FST.ID_FLEX_STRUCTURE_NAME,1,2) IN ('HB')
ORDER BY 1,2,3

GL Modules

GL Module
-------------

Journal Balances

Autopost Definitions

Balances and Movements

Code Combinations CCID

CVR Cross Validation Rule Overview

CVR Cross Validation Rule Detail Listing

Flexfield Security Rule Assignments

Flexfield Security Rule Definitions

FSG Report and Components Overview

Interface Summary

Interface Details

Journal Header Summary

Journal Line Based Trial Balance Report

Journal Lines With AP Source Reference Fields

Mass Allocation Formula review script

Period Status script

Segment Value Listing

Set of Books Configuration Overview

Summary Account Template definition review script

Operating Unit and Legal Entity Configuration

Chart of Account Segment Hierarchy Ranges

Chart of Accounts Structure Overview

Ready Scripts

This section would have all the ready scripts available.

General/AOL Module scripts

GL Module

PO Module

AP Module

INV Module

OM Module

General/AOL Module scripts

General/AOL Module scripts
----------------------------------

To know the Application version before upgrade and current version

Get Report With Parameters list

Know who all users are using the E-Business suite

Get the user’s status in the ICX_SESSIONS table

Get detail of Locks with Object Locked

Audit the changes in Profile Options in given number of days

To know the columns of given table and status of the table or object given

Find out the Patch Level

How to Select the Nth highest / lowest value from a table

udump path

Compile all the objects in given schema

Concurrent program attached to which responsibility

Invoker or Definer of package

Check the version of the file

Display the Database Version and Server Operating System Name and version

Registering Executable, Concurrent Program etc from backend

Operating Unit name based on the Operating Unit ID

Concurrent program name based on the request ID

Flexfields avilable for your Oracle Application Version

Submit the Concurrent Program from Backend

Register the table in Application from backend

Write message in Log or Out file

Permission names for specific User

Query to get the Number of Seconds or Minutes or Hours between 2 given days

Initialize Apps

Credit Card Number (of different format) from specific column

Know Instance Name

Know license applications

Balances and Movements

GL : Balances and Movements:-
------------------------------------

The following query will give a Trial balance with opening, movement and closing balances for upto ten segments in the chart of accounts by currency.

This can be used to as a quick method of running a trial balance for data extract in the desired format.

For example to use to extract to a third party reporting system such as Hyperion

It is recommended that this script is run for a single period and book first to gauge performance in your environment.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

SELECT SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.CODE_COMBINATION_ID
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10 "DISTRIBUTION"
,SUM( NVL(GB.BEGIN_BALANCE_DR,0) - NVL(GB.BEGIN_BALANCE_CR,0))"OPEN BAL"
,NVL(GB.PERIOD_NET_DR,0) "DEBIT"
,NVL(GB.PERIOD_NET_CR,0) "CREDIT"
,SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))"NET MOVEMENT"
,SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0))"CLOSE BAL"
, GB.CURRENCY_CODE
, GB.TRANSLATED_FLAG
, GB.TEMPLATE_ID
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GB.CURRENCY_CODE = SOB.CURRENCY_CODE
AND GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
AND GB.PERIOD_NAME = 'APR-04'
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HB')
GROUP BY SOB.NAME
, GB.ACTUAL_FLAG
, GB.PERIOD_NAME
, GCC.CODE_COMBINATION_ID
, GCC.SEGMENT1||'-'||GCC.SEGMENT2||'-'||GCC.SEGMENT3||'-'||GCC.SEGMENT4||'-'||GCC.SEGMENT5||'-'||GCC.SEGMENT6
||'-'||GCC.SEGMENT7||'-'||GCC.SEGMENT8||'-'||GCC.SEGMENT9||'-'||GCC.SEGMENT10
, NVL(GB.PERIOD_NET_DR,0)
, NVL(GB.PERIOD_NET_CR,0)
, GB.CURRENCY_CODE
, GB.TRANSLATED_FLAG
, GB.TEMPLATE_ID
HAVING SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) <> 0

Autopost Definitions

GL AUTOPOST DEFINITIONS

-----------------------------------

The Following query will list the autopost definitions by book show the journal source and category.

I have tested in 11.5.10.2 Instance. This will work in all the 11i Instances.

I have commented the SOB condition. Check your Instances SOB short-name and provide accordingly if you have multiple SOB in your business Structure.

SELECT SOB.NAME "BOOK"
, APS.AUTOPOST_SET_NAME "SET NAME"
, APS.DESCRIPTION
, APS.ENABLED_FLAG
, APS.SUBMIT_ALL_PRIORITIES_FLAG "SUBMIT ALL?"
, APO.ACTUAL_FLAG "ACTUAL FLAG"
, APO.PERIOD_NAME "PERIOD"
, APO.JE_SOURCE_NAME "SOURCE"
, APO.USER_JE_CATEGORY_NAME "CATEGORY"
FROM GL_AUTOMATIC_POSTING_SETS_V APS, GL_AUTOMATIC_POSTING_OPTIONS_V APO, GL_SETS_OF_BOOKS SOB
WHERE APO.AUTOPOST_SET_ID = APS.AUTOPOST_SET_ID
AND APS.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
--AND SUBSTR(SOB.NAME,1,2) IN ('HB')
ORDER BY 1

ADI Journal Balances

GL : ADI Journal Balances:-
-------------------------------

The following query gives a Trial balance in ADI format for upto 10 segments in the chart of accounts with Debit and Credit Balance.

This can be used to extract GL balances data from one environment in and ADI Journal format to load into another environment.

It is recommended that this script is run for a single period and book first to gauge performance in your environment.

I have tested this script in Vision 11.5.10.2 (This will work in all the 11i Instances).

In the following select statement, I have commented PERIOD and SOB condition. Check in your Instances and run according to your requirment.

SELECT SOB.NAME
,    GB.PERIOD_NAME
,    GCC.SEGMENT1
,    GCC.SEGMENT2
,    GCC.SEGMENT3
,    GCC.SEGMENT4
,    GCC.SEGMENT5
,    GCC.SEGMENT6
,    GCC.SEGMENT7
,    GCC.SEGMENT8
,    GCC.SEGMENT9
,    GCC.SEGMENT10 
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) >= 0 
       THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)))  
    ELSE 0 END ) "DEBIT" 
,(CASE WHEN SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <= 0 
       THEN (SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0))*-1)  
    ELSE 0 END )  "CREDIT"
FROM GL_BALANCES GB
, GL_CODE_COMBINATIONS GCC, GL_SETS_OF_BOOKS SOB
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND  GB.ACTUAL_FLAG = 'A'
--AND  GB.PERIOD_NAME = 'JAN-08'
AND    GB.CURRENCY_CODE = SOB.CURRENCY_CODE
--AND SUBSTR(SOB.SHORT_NAME,1,2) IN ('HB')
AND  GB.TEMPLATE_ID IS NULL
AND GB.SET_OF_BOOKS_ID = SOB.SET_OF_BOOKS_ID
GROUP BY  SOB.NAME
,    GB.ACTUAL_FLAG
,    GB.PERIOD_NAME
,    GCC.SEGMENT1
,    GCC.SEGMENT2
,    GCC.SEGMENT3
,    GCC.SEGMENT4
,    GCC.SEGMENT5
,    GCC.SEGMENT6
,    GCC.SEGMENT7
,    GCC.SEGMENT8
,    GCC.SEGMENT9
,    GCC.SEGMENT10 
,    NVL(GB.PERIOD_NET_DR,0)
,    NVL(GB.PERIOD_NET_CR,0)
HAVING SUM( NVL(GB.PERIOD_NET_DR,0) - NVL(GB.PERIOD_NET_CR,0)) <> 0
ORDER BY 1,2,3,4,5,6,7,8,9