Archive for the ‘GL’ Category

11.0.3. Extract GL Budgets

Wednesday, December 7th, 2016

SQL Statement to extract GL Budget STAT amounts.

 

select /*+ index(gcc code_combination_id) */
bal.period_num,
bal.period_net_dr,
bal.period_net_cr,
gcc.segment1,
gcc.segment2,
gcc.segment3,
gcc.segment4,
gcc.segment5,
gcc.segment6,
gcc.segment7
from apps.gl_balances bal
, apps.gl_sets_of_books bsk
, apps.gl_code_combinations gcc
, apps.gl_periods pd
where bal.period_year = 2016
and bal.period_num = 1
and bal.actual_flag = ‘B’
and ‘STAT’ =  bal.currency_code
and bsk.set_of_books_id = bal.set_of_books_id
and pd.period_set_name = bsk.period_set_name
and pd.period_name = bal.period_name
and gcc.code_combination_id = bal.code_combination_id
and gcc.summary_flag = ‘N’

Implementation Question: Should I turn on Dynamic combination creation?

Wednesday, June 1st, 2016

Oracle recommend turning on the setting for dynamic combinations creation (cloud) or dynamic inserts (11i/R12) at least until all setup related activities are finalised. Turning off dynamic creation / inserts limits the code combinations allows and can hinder implementations.

Oracle EBS SQL: OTL

Friday, December 20th, 2013

The following SQL statement can be used for debugging the Oracle Time and Labor (OTL) and Projects (PA) interface.
This was created during a recent R12 upgrade where attributes where somehow resulting in a quantity in projects multiplied by the project ID.

SELECT   hts.timecard_id, hts.resource_id, hts.start_time,
hts.stop_time, hts.submission_date,
htb1.start_time each_day, hta.attribute1 project_id,
hta.attribute2 task_id, htb2.measure, pt.task_name, 
hts.approval_item_key, hts.transferred_to,
htb.unit_of_measure
,ppa.segment1, pt.task_number
/**                Funny Attributes
  These should be cleared during the upgrade Process. They are still populated post upgrade. 
**/                
,hta.attribute25
,hta.attribute26
,hta.attribute27
,hta.attribute29
FROM hxc_time_building_blocks htb,
hxc_time_building_blocks htb1,
hxc_time_building_blocks htb2,
hxc_time_attribute_usages htau,
hxc_time_attributes hta,
pa_projects_all ppa,
hxc_timecard_summary hts,
pa_tasks pt
WHERE 1=1
AND htb1.parent_building_block_id = htb.time_building_block_id
AND htb1.parent_building_block_ovn = htb.object_version_number
AND htb2.parent_building_block_id = htb1.time_building_block_id
AND htb2.parent_building_block_ovn = htb1.object_version_number              
AND htb.SCOPE = 'TIMECARD'
AND htb1.SCOPE = 'DAY'
AND htb2.SCOPE = 'DETAIL'
AND htau.time_building_block_id = htb2.time_building_block_id
AND htau.time_building_block_ovn = htb2.object_version_number
AND htau.time_attribute_id = hta.time_attribute_id
AND hts.start_time = htb.start_time
AND hts.resource_id = htb.resource_id 
AND hta.attribute_category = 'PROJECTS'
AND hta.attribute2 = pt.task_id
AND hta.attribute1 = pt.project_id
AND ppa.project_id = hta.attribute1              
AND hts.timecard_id = :p_timecard_id              
and ppa.segment1 like 'YOUR_PROJECT_NUMBER'
--AND hts.approval_status <> 'APPROVED'
--AND trunc(hts.submission_date) = trunc(sysdate-360)
--AND hts.transferred_to is null        
--and ppa.project_id <> hta.attribute25

Oracle EBS SQL: GL Headers and Lines

Thursday, December 19th, 2013

The following SQL Statement is useful to find information about your GL Journals:

Note, this SQL statement is designed to link attributes 1 and 2 on the GL Journal lines to Projects, as such should be removed for any use elsewhere.

SELECT gjh.je_header_id jnl_hdr_id ,
gjct.user_je_category_name category ,
gjst.user_je_source_name source ,
gjb.status post_status ,
gjb.name batch_name ,
gjh.period_name ,
gjh.name journal_name ,
gjh.external_reference REF ,
gjh.date_created ,
gjh.description ,
gjh.running_total_dr ttl ,
gjh.creation_date ,
fu.description created_by ,
gjl.je_line_num ,
gjl.description line_descr ,
gjl.reference_1 line_ref ,
gjl.reference_5 ap_inv_num ,
gjl.reference_6 line_src ,
gjl.reference_10 line_type ,
GCC.SEGMENT1
|| '-'
|| gcc.segment2
|| '-'
|| gcc.segment3
|| '-'
|| GCC.SEGMENT4
|| '-'
|| gcc.segment5 cgh_acct ,
GJL.ACCOUNTED_DR DR ,
GJL.ACCOUNTED_CR CR ,
GJL.ATTRIBUTE1 PROJECT_ID ,
GJL.ATTRIBUTE2 TASK_ID ,
PPA.SEGMENT1 PROJECTNUMBER,
PT.TASK_NUMBER TASKNUMBER
FROM GL.GL_JE_HEADERS GJH ,
GL.GL_JE_BATCHES GJB ,
GL.GL_JE_LINES GJL ,
GL.GL_JE_SOURCES_TL GJST ,
GL.GL_JE_CATEGORIES_TL GJCT ,
GL.GL_CODE_COMBINATIONS GCC ,
APPLSYS.FND_USER FU ,
PA.PA_PROJECTS_ALL PPA ,
PA.PA_TASKS pt
WHERE GJH.JE_BATCH_ID                                            = GJB.JE_BATCH_ID(+)
AND gjh.je_header_id                                             = gjl.je_header_id
AND GJH.JE_SOURCE                                                = GJST.JE_SOURCE_NAME
AND GJH.JE_CATEGORY                                              = GJCT.JE_CATEGORY_NAME
AND GJH.CREATED_BY                                               = FU.USER_ID
AND GJL.CODE_COMBINATION_ID                                      = GCC.CODE_COMBINATION_ID
-- These allow for duff information from other sources to be excluded.
AND DECODE(GJL.CONTEXT,'Capital Contribution',GJL.ATTRIBUTE1,-1) = PPA.PROJECT_ID(+)
AND DECODE(GJL.CONTEXT,'Capital Contribution',GJL.ATTRIBUTE2,-1) = PT.TASK_ID (+)
AND GJH.JE_CATEGORY                                              = 'Capital Contribution'
ORDER BY GJH.CREATION_DATE DESC ,
GJB.NAME ,
GJH.NAME ,
GJL.JE_LINE_NUM;

* All SQL statements are provided without warranty or support. Use at your own risk!