General Ledger - Oracle EBS R12: October 2016

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