General Ledger - Oracle EBS R12: 2016

Tuesday 27 December 2016

Functions assigned to User and Responsibility PLSQL Query - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/12/function-assigned-to-user-and.html

Functions assigned to User and Responsibility PLSQL Query

select fu.USER_NAME, rr.RESPONSIBILITY_NAME, kk.DESCRIPTION, tt.MENU_NAME
from FND_MENU_ENTRIES_VL kk,
FND_MENUS_VL tt,
FND_RESPONSIBILITY_VL rr

,
FND_USER_RESP_GROUPS_DIRECT ee,
fnd_user fu

where kk.MENU_ID = tt.MENU_ID

and rr.MENU_ID = kk.MENU_ID

and kk.MENU_ID = rr.MENU_ID

and ee.RESPONSIBILITY_ID = rr.RESPONSIBILITY_ID

and fu.USER_ID = ee.user_id

and kk.FUNCTION_ID = 2850

and ee.END_DATE is null

and fu.END_DATE is null

order by fu.USER_NAME

Thursday 22 December 2016

Unable to Select Trading Partner Legal Entities From Trading Partner LOV While Defining Intercompany

http://generalledger-gl.blogspot.com/2016/12/i-had-same-issue-i-was-able-to-use-lov.html

I had the same issue , I was able to use the LOV by entering first character of Legal Entity and LOV worked. Ex: N% and LOV showed the legal entity name for our setup.

Wednesday 26 October 2016

Budget Inquiry PLSQL Query - Oracle EBS R12

http://generalledger-gl.blogspot.com/2017/02/budget-inquiry.html

PLSQL Query to Get Budget Inquiry

select decode(kk.STATUS, 'P', 'Posted', 'U', 'Unposted', kk.STATUS) Status,

fu.USER_NAME User_Created,

fu.FAX Site,

kk.NAME,

kk.JE_SOURCE,

kk.JE_CATEGORY,

kk.PERIOD_NAME,

kk.DEFAULT_EFFECTIVE_DATE,

gc.SEGMENT1 Company,

gc.SEGMENT2 Lcoation,

gc.SEGMENT3 Department,

gc.SEGMENT4 Account,

gc.SEGMENT5 Project,

gc.SEGMENT6 Intercompany,

gc.SEGMENT7 Future1,

gc.SEGMENT8 Future2,

ll.ACCOUNTED_DR Debit,

ll.ACCOUNTED_CR Credit,

ll.DESCRIPTION

from gl_je_headers kk,

gl_je_lines ll,

fnd_user fu,

gl_code_combinations gc

where kk.JE_HEADER_ID = ll.JE_HEADER_ID

and fu.USER_ID = kk.CREATED_BY

and gc.CODE_COMBINATION_ID = ll.CODE_COMBINATION_ID

and kk.ACTUAL_FLAG = 'B'

-- and ll.STATUS = 'P'

-- and ll.STATUS = 'P'

and ll.LEDGER_ID = kk.LEDGER_ID

and kk.LEDGER_ID = 2024

and ll.LEDGER_ID = 2024

Friday 21 October 2016

IntraLocation PLSQL Query (Location Differences in one JV) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/10/intralocation-plsql-query-location.html

IntraLocation PLSQL Query (Location Differences in one JV)

select distinct ff.DOC_SEQUENCE_VALUE,
ff.JE_CATEGORY,
gca.SEGMENT1 Company,
gca.SEGMENT2 Location

,
gca.SEGMENT3 Department,
gca.SEGMENT4 Account,
gca.SEGMENT5 Project,

gca.SEGMENT6 InterCompany,
gca.SEGMENT7 Future1,
gca.SEGMENT8 Furture2,

A.JE_LINE_NUM,
A.ACCOUNTED_DR,
A.ACCOUNTED_CR

from gl_je_lines A,
gl_je_lines B,
gl_code_combinations gca,
gl_code_combinations gcb,
gl_je_headers ff

where A.JE_HEADER_ID = b.JE_HEADER_ID

and A.CODE_COMBINATION_ID = gca.CODE_COMBINATION_ID

and b.CODE_COMBINATION_ID = gcb.CODE_COMBINATION_ID

and ff.JE_HEADER_ID = A.JE_HEADER_ID

and gca.SEGMENT2 != gcb.SEGMENT2

and ff.LEDGER_ID = 2021

and ff.ACTUAL_FLAG = 'A'

and ff.DEFAULT_EFFECTIVE_DATE > '01-JUL-2016'

order by ff.DOC_SEQUENCE_VALUE

#############################################################################

select distinct A.Doc_Sequence_Value,
A.User_Je_Category_Name,
A.DEFAULT_EFFECTIVE_DATE,
A.a_Period,
A.DESCRIPTION,

A.Je_Line_Num,
A.Segment1,
A.Segment2,
A.Segment3,
A.Segment4,
A.Segment5,
A.Segment6,
A.Segment7,
A.Segment8,
a.accounted_dr,
a.accounted_cr

from (select --count(*)

kk.JE_HEADER_ID,
kk.DOC_SEQUENCE_VALUE,
kk.PERIOD_NAME,
jj.JE_LINE_NUM,
kk.LEDGER_ID

,
kk.DEFAULT_EFFECTIVE_DATE,
kk.PERIOD_NAME A_Period,
jj.DESCRIPTION

,
gc.user_je_category_name,
kk.JE_CATEGORY,
jj.ACCOUNTED_DR,
jj.ACCOUNTED_CR,

gcc.SEGMENT1,
gcc.SEGMENT2,
gcc.SEGMENT3,
gcc.SEGMENT4,
gcc.SEGMENT5,
gcc.SEGMENT6,
gcc.SEGMENT7,
gcc.SEGMENT8

from gl_je_headers kk,
gl_je_lines jj,
gl_code_combinations gcc,
gl_je_categories gc

where kk.JE_HEADER_ID = jj.JE_HEADER_ID

and gcc.CODE_COMBINATION_ID = jj.CODE_COMBINATION_ID

and gc.je_category_name = kk.JE_CATEGORY

and jj.LEDGER_ID = kk.LEDGER_ID

and jj.PERIOD_NAME = kk.PERIOD_NAME

and kk.LEDGER_ID = 2021

and jj.STATUS = 'P'

and kk.STATUS = 'P'

and kk.ACTUAL_FLAG = 'A'

and kk.DEFAULT_EFFECTIVE_DATE > '01-JUL-2016'

--and kk.DOC_SEQUENCE_VALUE=16000001

) A,

(select --count(*)

g1.JE_HEADER_ID,
g1.DOC_SEQUENCE_VALUE,
g1.PERIOD_NAME,
j1.JE_LINE_NUM,
g1.ledger_id,

g1.DEFAULT_EFFECTIVE_DATE,
g1.PERIOD_NAME B_Period,
j1.DESCRIPTION

,
y1.user_je_category_name,
g1.JE_CATEGORY,
j1.ACCOUNTED_DR,
j1.ACCOUNTED_CR,

gc1.SEGMENT1,
gc1.SEGMENT2,
gc1.SEGMENT3,
gc1.SEGMENT4,
gc1.SEGMENT5,
gc1.SEGMENT6,
gc1.SEGMENT7,
gc1.SEGMENT8

from gl_je_headers g1,
gl_je_lines j1,
gl_code_combinations gc1,
gl_je_categories y1

where g1.JE_HEADER_ID = j1.JE_HEADER_ID

and gc1.CODE_COMBINATION_ID = j1.CODE_COMBINATION_ID

and y1.je_category_name = g1.JE_CATEGORY

and j1.LEDGER_ID = g1.LEDGER_ID

and j1.PERIOD_NAME = g1.PERIOD_NAME

and j1.LEDGER_ID = 2021

and j1.STATUS = 'P'

and g1.STATUS = 'P'

and g1.ACTUAL_FLAG = 'A'

--and g1.DOC_SEQUENCE_VALUE=16000001

and g1.DEFAULT_EFFECTIVE_DATE > '01-JUL-2016'

) B

where A.Segment2 != b.segment2

and A.JE_HEADER_ID = b.je_header_id

and A.USER_JE_CATEGORY_NAME = b.user_je_category_name

and A.a_Period = B.b_Period

and A.Ledger_Id = b.ledger_id

order by A.Doc_Sequence_Value, a.je_line_num

Wednesday 19 October 2016

PLSQL query to find Open/Close Periods - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/10/query-for-period-closure.html

PLSQL query to find Open/Close Periods

To See Periods Status (Open/Closed & Future)

select NAME, LEDGER_ID, period_name, APPLICATION_NAME, status

from (select ff.NAME NAME,

kk.LEDGER_ID,

kk.PERIOD_NAME,

jj.APPLICATION_NAME,

DECODE(kk.CLOSING_STATUS,

'O',

'Open',

'C',

'Closed',

'F',

'Future',

'N',

'Never') STATUS

from GL_PERIOD_STATUSES_V kk, fnd_application_vl jj, gl_ledgers ff

where 1 = 1

and jj.APPLICATION_ID = kk.APPLICATION_ID

and ff.LEDGER_ID = kk.LEDGER_ID

--and kk.PERIOD_NAME = 'SEP-16'

--and kk.LEDGER_ID=2021

/* and jj.APPLICATION_NAME in ('Payables', 'General Ledger', 'Receivables')*/ -- Open for your Modules

and kk.CLOSING_STATUS != 'N'

--and kk.CLOSING_STATUS = 'O'

--order by kk.PERIOD_NAME

)

union

(SELECT /*(select sob.NAME from gl_sets_of_books sob where sob.SET_OF_BOOKS_ID = fbc.SET_OF_BOOKS_ID)*/

fbc.BOOK_TYPE_CODE NAME,

fbc.set_of_books_id LEDGER_ID,

fdp.period_name,

'Assets' APPLICATION_NAME,

DECODE(fdp.period_close_date, NULL, 'Open', 'Closed') status

FROM fa_book_controls fbc, fa_deprn_periods fdp

--WHERE fbc.set_of_books_id = NVL('&sob',set_of_books_id)

WHERE fbc.book_type_code = fdp.book_type_code

-- and fdp.period_close_date is null

and fbc.DATE_INEFFECTIVE is null

--AND UPPER (fdp.period_name) = UPPER ('&period_name')

--order by fdp.PERIOD_NAME

)

order by period_name

#############################################################################

To See Open Periods Only

1st Query

select NAME, LEDGER_ID, period_name, APPLICATION_NAME, status

from (select ff.NAME NAME,

kk.LEDGER_ID,

kk.PERIOD_NAME,

jj.APPLICATION_NAME,

DECODE(kk.CLOSING_STATUS,

'O',

'Open',

'C',

'Closed',

'F',

'Future',

'N',

'Never') STATUS

from GL_PERIOD_STATUSES_V kk, fnd_application_vl jj, gl_ledgers ff

where 1 = 1

and jj.APPLICATION_ID = kk.APPLICATION_ID

and ff.LEDGER_ID = kk.LEDGER_ID

--and kk.PERIOD_NAME = 'SEP-16'

--and kk.LEDGER_ID=2021

/* and jj.APPLICATION_NAME in ('Payables', 'General Ledger', 'Receivables')*/ -- Open for your Modules

and kk.CLOSING_STATUS != 'N'

and kk.CLOSING_STATUS = 'O'

--order by kk.PERIOD_NAME

)

union

(SELECT /*(select sob.NAME from gl_sets_of_books sob where sob.SET_OF_BOOKS_ID = fbc.SET_OF_BOOKS_ID)*/

fbc.BOOK_TYPE_CODE NAME,

fbc.set_of_books_id LEDGER_ID,

fdp.period_name,

'Assets' APPLICATION_NAME,

DECODE(fdp.period_close_date, NULL, 'Open', 'Closed') status

FROM fa_book_controls fbc, fa_deprn_periods fdp

--WHERE fbc.set_of_books_id = NVL('&sob',set_of_books_id)

WHERE fbc.book_type_code = fdp.book_type_code

and fdp.period_close_date is null

and fbc.DATE_INEFFECTIVE is null

--AND UPPER (fdp.period_name) = UPPER ('&period_name')

--order by fdp.PERIOD_NAME

)

order by period_name

#############################################################################

2nd Query

SELECT DISTINCT (SELECT sob.NAME

FROM gl_sets_of_books sob

WHERE sob.set_of_books_id = a.set_of_books_id) "SOB_Name",

a.period_name "Period_Name"

-- , a.period_num "Period_Num"

,

a.GL_PERIOD "GL_PERIOD"

-- , b.po_status "PO_Status"

,

c.AP_PERIOD "AP_PERIOD",

d.AR_PERIOD "AR_PERIOD",

e.FA_PERIOD "FA_PERIOD"

FROM (SELECT period_name,

period_num,

DECODE(closing_status, 'O', 'Open', null) GL_PERIOD

/*, 'C', 'Closed'

, 'F', 'Future'

, 'N', 'Never'

,*/

,

set_of_books_id

FROM gl_period_statuses

WHERE application_id = 101

-- and closing_status = 'O'

AND UPPER(period_name) =

NVL(UPPER('&period_name'), UPPER(period_name))

AND set_of_books_id = NVL('&sob', set_of_books_id)) a

/* , (SELECT period_name

, DECODE (closing_status

, 'O', 'Open',null) po_status

\* , 'C', 'Closed'

, 'F', 'Future'

, 'N', 'Never'

,*\

, set_of_books_id

FROM gl_period_statuses

WHERE application_id = 201

AND UPPER (period_name) = NVL(UPPER ('&period_name'),UPPER (period_name))

AND set_of_books_id = NVL('&sob',set_of_books_id)

) b*/

,

(SELECT period_name,

DECODE(closing_status, 'O', 'Open', null) AP_PERIOD

/*, 'C', 'Closed'

, 'F', 'Future'

, 'N', 'Never'

,*/

,

set_of_books_id

FROM gl_period_statuses

WHERE application_id = 200

-- and closing_status = 'O'

AND UPPER(period_name) =

NVL(UPPER('&period_name'), UPPER(period_name))

AND set_of_books_id = NVL('&sob', set_of_books_id)) c,

(SELECT period_name,

DECODE(closing_status, 'O', 'Open', null) AR_PERIOD

/*, 'C', 'Closed'

, 'F', 'Future'

, 'N', 'Never'

,*/

,

set_of_books_id

FROM gl_period_statuses

WHERE application_id = 222

-- and closing_status = 'O'

AND UPPER(period_name) =

NVL(UPPER('&period_name'), UPPER(period_name))

AND set_of_books_id = NVL('&sob', set_of_books_id)) d,

(SELECT fdp.period_name,

DECODE(fdp.period_close_date, NULL, 'Open', null) FA_PERIOD,

fbc.set_of_books_id

FROM fa_book_controls fbc, fa_deprn_periods fdp

WHERE fbc.set_of_books_id = NVL('&sob', fbc.set_of_books_id)

AND fbc.book_type_code = fdp.book_type_code

and fbc.DATE_INEFFECTIVE is null

--and DECODE (fdp.period_close_date, NULL, 'Open', 'Closed') = 'Open'

AND UPPER(fdp.period_name) =

NVL(UPPER('&period_name'), UPPER(fdp.period_name))) e

WHERE 1 = 1

-- AND a.period_name = b.period_name(+)

AND a.period_name = c.period_name(+)

AND a.period_name = d.period_name(+)

AND a.period_name = e.period_name(+)

-- AND a.set_of_books_id = b.set_of_books_id(+)

AND a.set_of_books_id = c.set_of_books_id(+)

AND a.set_of_books_id = d.set_of_books_id(+)

AND a.set_of_books_id = e.set_of_books_id(+)

and (GL_PERIOD = 'Open' or AR_PERIOD = 'Open' or AP_PERIOD = 'Open' or

FA_PERIOD = 'Open'

/*or PO_STATUS = 'Open'*/

)

ORDER BY 2;

Thursday 6 October 2016

(GL Accounts) GL_Code_Combinations Segment Wise Values & Description - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/10/glcodecombinations-segment-wise-values.html

(GL Accounts) GL_Code_Combinations Segment Wise Values & Description

CREATE OR REPLACE VIEW GL_COA AS

SELECT

GCC.CODE_COMBINATION_ID,

GCC.SEGMENT1,

FS1.DESCRIPTION DESCRIPTION1,

GCC.SEGMENT2,

FS2.DESCRIPTION DESCRIPTION2,

GCC.SEGMENT3,

FS3.DESCRIPTION DESCRIPTION3,

GCC.SEGMENT4,

FS4.DESCRIPTION DESCRIPTION4,

GCC.SEGMENT5,

FS5.DESCRIPTION DESCRIPTION5,

GCC.SEGMENT6,

FS6.DESCRIPTION DESCRIPTION6,

GCC.SEGMENT7,

FS7.DESCRIPTION DESCRIPTION7,

GCC.SEGMENT8,

FS8.DESCRIPTION DESCRIPTION8

FROM

GL_CODE_COMBINATIONS GCC,

fnd_flex_values_vl FS1,

fnd_flex_values_vl FS2,

fnd_flex_values_vl FS3,

fnd_flex_values_vl FS4,

fnd_flex_values_vl FS5,

fnd_flex_values_vl FS6,

fnd_flex_values_vl FS7,

fnd_flex_values_vl FS8

WHERE ( GCC.CHART_OF_ACCOUNTS_ID=311254 )

AND ( FS8.FLEX_VALUE=GCC.SEGMENT8 )

AND ( FS7.FLEX_VALUE=GCC.SEGMENT7 )

AND ( FS6.FLEX_VALUE=GCC.SEGMENT6 )

AND ( FS5.FLEX_VALUE=GCC.SEGMENT5 )

AND ( FS4.FLEX_VALUE=GCC.SEGMENT4 )

AND ( FS3.FLEX_VALUE=GCC.SEGMENT3 )

AND ( FS2.FLEX_VALUE=GCC.SEGMENT2 )

AND ( FS1.FLEX_VALUE=GCC.SEGMENT1 )

AND ( FS1.FLEX_VALUE_SET_ID = 9214 AND FS1.SUMMARY_FLAG = 'N')

AND ( FS2.FLEX_VALUE_SET_ID = 9215 AND FS2.SUMMARY_FLAG = 'N')

AND ( FS3.FLEX_VALUE_SET_ID = 9216 AND FS3.SUMMARY_FLAG = 'N')

AND ( FS4.FLEX_VALUE_SET_ID = 9217 AND FS4.SUMMARY_FLAG = 'N')

AND ( FS5.FLEX_VALUE_SET_ID = 9218 AND FS5.SUMMARY_FLAG = 'N')

AND ( FS6.FLEX_VALUE_SET_ID = 9219 AND FS6.SUMMARY_FLAG = 'N')

AND ( FS7.FLEX_VALUE_SET_ID = 9220 AND FS7.SUMMARY_FLAG = 'N')

AND ( FS8.FLEX_VALUE_SET_ID = 9221 AND FS8.SUMMARY_FLAG = 'N')

Sunday 2 October 2016

How to Disable Delete Icon from Folder Tools in GL JV - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/10/blog-post.html

How to Disable Delete Icon from Folder Tools in GL JV

Disable Header Icon Delete from Folder Tools

Trigger Event: WHEN-NEW-RECORD-INSTANCE
Target Object: HEADER

Type Property
Object Type: Block
Target Object: HEADER
Property Name: DELETE_ALLOWED
Value: FALSE

Disable Batch Delete Icon from Folder Tools

Trigger Event: WHEN-NEW-RECORD-INSTANCE
Target Object: BATCH

Type Property
Object Type: Block
Target Object: BATCH
Property Name: DELETE_ALLOWED
Value: FALSE

Disable Folder Delete Icon from Folder Tools

Trigger Event: WHEN-NEW-RECORD-INSTANCE
Target Object: FOLDER

Type Property
Object Type: Block
Target Object: FOLDER
Property Name: DELETE_ALLOWED
Value: FALSE

Saturday 1 October 2016

How to see Deleted Document Numbers in GL (GL Doc Seq Audit) - Oracle EBS R12

https://generalledger-gl.blogspot.com/2016/10/how-to-see-deleted-document-numbers-in.htm

How to see Deleted Document Numbers in GL (GL Doc Seq Audit)

select dsa.SET_OF_BOOKS_ID,

aud.creation_date audit_creation_date,

categories.user_je_category_name category,

aud.doc_sequence_value doc_number,

aud.doc_sequence_id doc_sequence_id,

--decode(headers.name,NULL,:DELETED_MSG,:ENTERED_MSG) status, --Commented

decode(headers.name, NULL, 'Deleted', '') status,

fu.USER_NAME,

aud.CREATED_BY Creted_by,

headers.name header_name,

headers.currency_code currency_code,

batches.name batch_name,

lookups.description posting_status,

headers.posted_date posted_date,

headers.running_total_dr debits,

headers.running_total_cr credits,

seq.name sequence_name,

seq.doc_sequence_id seq_sequence_id,

seq.db_sequence_name seq_db_name,

seq.initial_value initial_value,

seq.type type

from gl_lookups lookups,

gl_je_categories categories,

gl_je_batches batches,

gl_je_headers headers,

gl_doc_sequence_audit aud,

fnd_document_sequences seq,

fnd_doc_sequence_assignments dsa,

fnd_user fu

WHERE lookups.lookup_type(+) = 'MJE_BATCH_STATUS'

AND lookups.lookup_code(+) = headers.status

AND categories.je_category_name(+) = headers.je_category

AND batches.je_batch_id(+) = headers.je_batch_id

AND headers.doc_sequence_value(+) = aud.doc_sequence_value

AND headers.doc_sequence_id(+) = aud.doc_sequence_id

and aud.CREATED_BY = fu.USER_ID

and dsa.DOC_SEQUENCE_ID = seq.DOC_SEQUENCE_ID

and dsa.DOC_SEQUENCE_ASSIGNMENT_ID = aud.DOC_SEQUENCE_ASSIGNMENT_ID

-- and headers.DOC_SEQUENCE_ID=dsa.DOC_SEQUENCE_ID

and headers.name is null

--&C_WHERE_SEC --Added for bug12426736 --Commented

--AND ((headers.parent_je_header_id IS NULL) OR (NVL(headers.je_from_sla_flag , 'N')= 'Y'))

/* AND aud.doc_sequence_value between

nvl(:P_SEQUENCE_FROM, aud.doc_sequence_value) and

nvl(:P_SEQUENCE_TO, aud.doc_sequence_value)*/ --Commented

AND aud.doc_sequence_id = seq.doc_sequence_id

AND seq.type IN ('A', 'G')

and dsa.SET_OF_BOOKS_ID = NVL('&Ledger_id', dsa.SET_OF_BOOKS_ID)

ORDER BY seq.name, aud.doc_sequence_value

Wednesday 24 August 2016

CONTROL OR THIRD PARTY ACCOUNTS IN R12 GENERAL LEDGER - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/08/control-or-third-party-accounts-in-r12.html

Control or Third party Accounts in R12 General Ledger

Friday 12 August 2016

GLXJEENT: Enter Journal Error APP-SQLGL-08067 on Fresh Install - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/08/glxjeent-enter-journal-error-app-sqlgl.html

ERROR:

APP-SQLGL-08067: Oracle General Ledger could not retrieve profile option
UNIQUE:SEQ_NUMBERS. Please contact your system administrator.

Missing setup of the profile option 'Sequential Numbering'.

SOLUTION

Set the profile option, 'Sequential Numbering' to 'Not Used'.

If you are not using document sequencing at all right now, you can set it at the site level. Otherwise you can set it at a lower level.

Responsibility = System Administrator
Navigation = Profile/System
Form Name = FNDPOMPV

1. Navigate to the Find System Profile Values form.
2. Check the appropriate levels under Display.
3. Enter 'Sequential Numbering' in the Profile field. Click the [Find] button.
4. Select 'Not Used' from the list of values, for the appropriate level. (If you plan to use Document Sequencing at a later time, you can change the assignment of this profile option then.)
5. Save.

Sunday 31 July 2016

New Fiscal Year Period giving Error on Opening (JUL) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/07/new-fiscal-year-period-giving-error-on.html

New Fiscal Year Period giving Error on Opening (JUL) - Oracle EBS R12

Error:

When Opening Period JUL for the New Fiscal Year the Below Error Occurs:

Invalid Combination (Unit will remain unspecified with Balance sheet accounts)
The following combination is erroneous and does not exist:
05.000.10.010.4110120201.000.00

SHRD0015: fdfkfa-FFSEGSID failed:

Solution:

The Nature of the above Account is Owner's Equity.

This Error is due to Cross Validation Rule. The Third Segment containing Value 10 has Qualifier Enabled of Secondary Tracking. User's have booked Expenses and Revenues on the Third Segment which is Restricted in Cross Validation Rule.

Disable the Cross Validation Rule and Open the Period "OR" Include the Code Combination in Cross Validation Rule

Wednesday 27 July 2016

FSG Rowset Account Ranges Listing - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/07/fsg-rowset-account-ranges-lisitng.html

PLSQL Query to Get FSG Rowset Account Ranges Listing

select bb.sequence,
bb.description,
tt.SIGN,
tt.SEGMENT1_LOW || '-' || SEGMENT2_LOW || '-' || SEGMENT3_LOW || '-' ||
SEGMENT4_LOW || '-' || SEGMENT5_LOW

|| '-' || SEGMENT6_LOW || '-' || SEGMENT7_LOW || '-' || SEGMENT8_LOW || '-' ||
SEGMENT9_LOW,

tt.SEGMENT1_HIGH || '-' || tt.SEGMENT2_HIGH || '-' ||
tt.SEGMENT3_HIGH || '-' || tt.SEGMENT4_HIGH || '-' ||
tt.SEGMENT5_HIGH

|| '-' || tt.SEGMENT6_HIGH || '-' || tt.SEGMENT7_HIGH || '-' ||
tt.SEGMENT8_HIGH || '-' || tt.SEGMENT9_HIGH,

tt.SEGMENT1_TYPE || tt.SEGMENT2_TYPE || tt.SEGMENT3_TYPE ||

tt.SEGMENT4_TYPE || tt.SEGMENT5_TYPE || tt.SEGMENT6_TYPE ||
tt.SEGMENT7_TYPE || tt.SEGMENT8_TYPE Display

from RG_REPORT_AXIS_CONTENTS tt, RG_REPORT_AXES_V bb

where tt.AXIS_SET_ID = bb.axis_set_id

and tt.AXIS_SEQ = bb.sequence

and bb.axis_set_id = 15207

order by bb.sequence asc

Monday 25 July 2016

How to disable Autocopy GL Batch functionality - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/07/how-to-disable-autocopy-gl-batch.html

How to disable Autocopy GL Batch functionality

1) One can use personalizations hide the button (help -> diagnostics -> custom code -> personalize). The button is BATCH.BATCH_AUTOCOPY.

2) Another option you have is to disable the concurrent program GLJRNAUTOC.
System Administrator > Concurrent > Program > Define
Query 'GLJRNAUTOC'.
Uncheck the Enable checkbox and Save.

The button will still be visible on the form, but when pressed an error message is received:
APP-FND-00150: You cannot submit concurrent request for disabled concurrent program GLJRNAUTOC. Contact your system administrator to enable the concurrent program.

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

3) Use the Below Personalization to Hide AutoCopy

Tuesday 12 July 2016

To Check Reversal Periods not assigned to Auto Reversal Category (Query) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/07/to-check-reversal-periods-not-assigned.html

To Check Reversal Periods not assigned to Auto Reversal Category

select * from gl_je_headers yy
where 1=1
and yy.JE_CATEGORY in ('2','35','9','41','47')
and yy.ACCRUAL_REV_PERIOD_NAME is null
and yy.REVERSED_JE_HEADER_ID is null
and (yy.RUNNING_TOTAL_DR <>0 or yy.RUNNING_TOTAL_CR <>0)

Data Transferred from XLA to GL (Query) - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/07/gl-tables-and-xla-tables-match-on.html

PLSQL to Get Data Transferred from XLA to GL

select pp.Acv Account,

NVL(pp.IN_XLA, 0) XLA_AE_LINES_A,

NVL(mm.IN_GL_IMPORT, 0) GL_IMPOERT_REFERENCES_B,

nvl(pp.IN_XLA, 0) - nvl(mm.IN_GL_IMPORT, 0) Difference_AB,

NVL(cc. IN_GL_INTERFACE, 0) GL_INTERFACE_C,

nvl(qq.XLA_TO_GL, 0) XLA_TO_GL_Transferred_D,

nvl(mm.IN_GL_IMPORT - qq.XLA_TO_GL, 0) Difeerence_BD,

NVL(bb.POSTED_IN_GL, 0) POSTED_IN_GL,

NVL(ss.Reversed_in_GL, 0) Reversed_In_GL,

NVL(qq.XLA_TO_GL, 0) - NVL(bb.POSTED_IN_GL, 0) UNPOSTED_IN_GL

-- ww.ala + qq.XLA_TO_GL - ww.ala IN_GL

/*,ww.ala+qq.XLA_TO_GL-ww.ala IN_GL,qq.XLA_TO_GL-ww.ala Difference,bb.fad Reversal, zz.nn Manual_ENT_in_GL , ww.ala+zz.nn Ending_balance

*/

from (select gcc.segment4 Acv,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_XLA

from xla_ae_lines xa, xla_ae_headers xe, gl_code_combinations gcc

-- gl_import_references gi

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

-- and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

--- and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by gcc.segment4) pp,

(select gcc.segment4 Account,

NVL(sum(xa.ACCOUNTED_DR), 0) - NVL(sum(xa.ACCOUNTED_CR), 0) IN_GL_IMPORT

from xla_ae_lines xa,

xla_ae_headers xe,

gl_code_combinations gcc,

gl_import_references gi

where 1 = 1

and xa.AE_HEADER_ID = xe.AE_HEADER_ID

and xa.GL_SL_LINK_ID = gi.GL_SL_LINK_ID

and xa.GL_SL_LINK_TABLE = gi.GL_SL_LINK_TABLE

and gcc.CODE_COMBINATION_ID = xa.CODE_COMBINATION_ID

and xa.APPLICATION_ID = xe.APPLICATION_ID

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

and xa.LEDGER_ID = NVL('&Ledger_id', xa.LEDGER_ID)

and xe.GL_TRANSFER_STATUS_CODE = 'Y'

--and xa.APPLICATION_ID in(260,140,222,200)

group by gcc.segment4) mm,

(SELECT gc.SEGMENT4 Acx,

NVL(sum(tt.ACCOUNTED_DR), 0) - NVL(sum(tt.ACCOUNTED_CR), 0) IN_GL_INTERFACE

FROM gl_interface tt, gl_code_combinations gc

WHERE tt.CODE_COMBINATION_ID = gc.CODE_COMBINATION_ID

and tt.LEDGER_ID = nvl('&Ledger_id', tt.LEDGER_ID)

--and USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices'

AND (USER_JE_SOURCE_NAME, USER_JE_CATEGORY_NAME, LEDGER_ID,

ACTUAL_FLAG, PERIOD_NAME, GL_SL_LINK_ID) NOT IN -- 413

(SELECT

--H.NAME,

--H.DEFAULT_EFFECTIVE_DATE,

--H.DESCRIPTION,

--H.JE_HEADER_ID,

--A.JE_HEADER_ID,

H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

A.GL_SL_LINK_ID

--H.CODE_COMBINATION_ID,

--H.ENTERED_DR,

--H.ENTERED_CR

FROM (SELECT *

FROM gl_interface

WHERE --USER_JE_SOURCE_NAME='Payables' AND USER_JE_CATEGORY_NAME='Purchase Invoices'

LEDGER_ID = NVL('&Ledger_id', LEDGER_ID)

AND ACTUAL_FLAG = 'A' -- 783

) A,

(SELECT H.JE_SOURCE,

H.JE_CATEGORY,

H.LEDGER_ID,

H.ACTUAL_FLAG,

H.PERIOD_NAME,

gll.CODE_COMBINATION_ID,

ael.ENTERED_DR,

ael.ENTERED_CR,

ael.gl_sl_link_id,

H.NAME,

H.DEFAULT_EFFECTIVE_DATE,

H.JE_HEADER_ID,

H.DESCRIPTION

FROM GL_JE_HEADERS H,

GL_JE_LINES gll,

XLA_AE_HEADERS aeh,

XLA_AE_LINES ael,

XLA_EVENTS aea,

GL_IMPORT_REFERENCES gir

WHERE 1 = 1

AND H.JE_HEADER_ID = gll.JE_HEADER_ID

AND ael.gl_sl_link_id = gir.gl_sl_link_id

-- AND gir.gl_sl_link_table in ('APECL', 'XLAJEL','XLAJEL')

-- AND aea.application_id = 200

AND aea.event_id = aeh.event_id

AND aeh.ae_header_id = ael.ae_header_id

AND gll.je_header_id = gir.je_header_id

AND gll.je_line_num = gir.je_line_num

-- AND H.JE_SOURCE='Payables'

-- AND H.JE_CATEGORY='Purchase Invoices'

AND H.LEDGER_ID = NVL('&Ledger_id', H.LEDGER_ID)

AND H.ACTUAL_FLAG = 'A'

--AND H.DEFAULT_EFFECTIVE_DATE='20-JUL-2009'

--AND H.PERIOD_NAME='JUL-09'

--AND H.DESCRIPTION LIKE '%200900160%'

) H

WHERE 1 = 1

AND H.JE_SOURCE = A.USER_JE_SOURCE_NAME

AND H.JE_CATEGORY = A.USER_JE_CATEGORY_NAME

AND H.LEDGER_ID = A.LEDGER_ID

AND H.ACTUAL_FLAG = A.ACTUAL_FLAG

AND H.PERIOD_NAME = A.PERIOD_NAME

--AND H.CODE_COMBINATION_ID=A.CODE_COMBINATION_ID

--AND (H.ENTERED_DR=A.ENTERED_DR or H.ENTERED_CR=A.ENTERED_CR)

AND H.GL_SL_LINK_ID = A.GL_SL_LINK_ID

)

group by gc.SEGMENT4) cc

,

(select gcc.segment4 Acc,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) XLA_TO_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

-- and hdr.status = 'P'

--and ln.status = 'P'

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

group by gcc.segment4) qq,

(select gcc.segment4 Acco,

NVL(sum(ael.ACCOUNTED_DR), 0) - NVL(sum(ael.ACCOUNTED_CR), 0) POSTED_IN_GL

from gl_je_lines ln,

gl_code_combinations gcc,

gl_je_headers hdr,

gl_import_references gir,

xla.xla_transaction_entities ent,

xla_ae_headers aeh,

xla_ae_lines ael,

fnd_user fu,

gl_je_categories glc

where 1 = 1

and hdr.status = 'P'

and ln.status = 'P'

and hdr.actual_flag = 'A'

and ln.je_header_id = hdr.je_header_id

and ln.period_name = hdr.period_name

and ln.code_combination_id = gcc.code_combination_id

and hdr.JE_CATEGORY = glc.je_category_name

and fu.USER_ID = hdr.CREATED_BY

and ln.je_header_id = gir.je_header_id

and ln.je_line_num = gir.je_line_num

and hdr.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = hdr.period_name

and aeh.entity_id = ent.entity_id

and hdr.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and ael.LEDGER_ID = NVL('&Ledger_id', ael.LEDGER_ID)

and ln.LEDGER_ID = NVL('&Ledger_id', ln.LEDGER_ID)

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and

NVL('&To_Account', gcc.SEGMENT4)

group by gcc.segment4) bb,

/*

(select gg.segment4 Aco,

NVL(sum(al.ACCOUNTED_DR)-sum(al.ACCOUNTED_CR), 0) ala

from gl_je_lines al, gl_code_combinations gg, gl_je_headers vv

where al.JE_HEADER_ID = vv.JE_HEADER_ID

and gg.CODE_COMBINATION_ID = al.CODE_COMBINATION_ID

-- and al.STATUS = 'P'

--and vv.STATUS = 'P'

and vv.ACTUAL_FLAG = 'A'

and gg.SEGMENT4 between NVL('&From_Account', gg.SEGMENT4) and

NVL('&To_Account', gg.SEGMENT4)

and vv.LEDGER_ID = al.LEDGER_ID

and vv.LEDGER_ID = '&Ledger_id'

and vv.JE_SOURCE not in

('Manual', 'Spreadsheet', 'Recurring', 'AutoCopy',

'Revaluation', 'Closing Journal')

group by gg.SEGMENT4

) ww,*/

(select ll.segment4 Ack,

NVL(sum(jj.ACCOUNTED_DR), 0) - NVL(sum(jj.ACCOUNTED_CR), 0) Reversed_in_GL

from gl_je_lines jj, gl_code_combinations ll, gl_je_headers nn

where jj.CODE_COMBINATION_ID = ll.CODE_COMBINATION_ID

and jj.JE_HEADER_ID = nn.JE_HEADER_ID

and jj.LEDGER_ID = NVL('&Ledger_id', jj.LEDGER_ID)

and nn.ACTUAL_FLAG = 'A'

--and jj.STATUS='P'and nn.STATUS='P'

and ll.SEGMENT4 between NVL('&From_Account', ll.SEGMENT4) and

NVL('&To_Account', ll.SEGMENT4)

and nn.JE_SOURCE not in

('Manual',
'Spreadsheet',
'Recurring',
'AutoCopy',

'Revaluation',
'Closing Journal')

and nn.REVERSED_JE_HEADER_ID is not null

group by ll.SEGMENT4) ss

where pp.Acv = mm.Account(+)

-- and qq.Acc=pp.Acv

--and ww.Aco=pp.Acv

and mm.Account = qq.Acc(+)

and pp.Acv = cc.Acx(+)

--and mm.Account = ww.Aco

-- and qq.Acc = ww.Aco

and bb.Acco(+) = mm.Account

and mm.Account = ss.Ack(+)

Journal Totals Differ in Debit and Credit even the Journal Lines are Balances - Oracle EBS R12

http://generalledger-gl.blogspot.com/2016/07/journal-totals-are-showing-with-zero.html

Journal Totals Differ in Debit and Credit even the Journal Lines are Balances

Immediate workaround to correct the effected journal totals:

In the Enter Journals form, change the conversion rate (and the currency and conversion type if necessary) of the journal. Commit this change.

Change it back again to the original currency