Home » Applications » Oracle Fusion Apps & E-Business Suite » PA and GL LINK (10.5.10)
PA and GL LINK [message #324393] Mon, 02 June 2008 05:09 Go to next message
lakshminarayana.j
Messages: 9
Registered: May 2008
Location: Pune
Junior Member
Hi ,

I am struggling to find the link between the General Ledger and Oracle Projects (PA) .

In my report following are the columns.

Proj #, Task #, Customer, GL Acct, Dist Amt, Description, Invoice #, GL Period

I have one table pa_cost_distribution_lines_all in that "code_combination_id" column contain null values in our business aspect.

Suggest me.

Lakshmi Narayna JV
Re: PA and GL LINK [message #325709 is a reply to message #324393] Sat, 07 June 2008 17:32 Go to previous messageGo to next message
djp1976
Messages: 15
Registered: September 2007
Junior Member
This should not be to hard as I just completed a script for linking the GL table to Projects.

In the PA_COST_DISTRIBUTIONS_LINES table you need to take the CR_CODE_COMBINATION_ID or DR_CODE_COMBINATION_ID and link it to the CODE_COMBINATION_ID in the GL_CODE_COMBINATIONS table. they need to be linked independently as on is for debit and the other for credit. It worked for me. Good luck.
Re: PA and GL LINK [message #325795 is a reply to message #325709] Sun, 08 June 2008 23:00 Go to previous messageGo to next message
lakshminarayana.j
Messages: 9
Registered: May 2008
Location: Pune
Junior Member
I will Try it.

Thanks
Re: PA and GL LINK [message #326314 is a reply to message #325709] Wed, 11 June 2008 01:24 Go to previous messageGo to next message
lakshminarayana.j
Messages: 9
Registered: May 2008
Location: Pune
Junior Member
Hi All,

Could you please Send me a sample query between gl and PA with the above table(PA_COST_DISTRIBUTION_LINES).

Thanks
Lakshmi Narayana
Re: PA and GL LINK [message #329181 is a reply to message #324393] Tue, 24 June 2008 07:49 Go to previous messageGo to next message
Rajkumar_mj
Messages: 18
Registered: July 2006
Location: Chenna
Junior Member
SELECT
hr2.name Person_org
,hr3.name Prj_org
--,paei.Expenditure_item_id
,p.segment1 Prj_no
,pt.Task_number
,pt.task_name
,hr.full_name
, paei.EXPENDITURE_TYPE
, pacdl.TRANSFER_STATUS_CODE trans_status
, to_Char(paei.EXPENDITURE_ITEM_DATE , 'Mon-YYYY')
, to_char(pacdl.GL_DATE,'Mon-YYYY') GL_period
, sum(pacdl.quantity ) Hrs
,pacdl.denom_currency_code Transaction_curr
,sum(pacdl.denom_burdened_cost) Transaction_amt
,pacdl.ACCT_CURRENCY_CODE Functional_curr
,sum(pacdl.ACCT_BURDENED_COST) Functional_amt
, gldr.SEGMENT1 DR_Entity
, gldr.SEGMENT7 DR_Region
, gldr.SEGMENT2 DR_CORP_AC
, gldr.SEGMENT3 DR_CC
, gldr.SEGMENT4 DR_PRJ
, gldr.SEGMENT6 DR_Interco
, gldr.SEGMENT8 DR_Type
, gldr.SEGMENT9 DR_Prd
, gldr.SEGMENT5 DR_Orgin
, gldr.SEGMENT10 DR_Res
, glcr.SEGMENT1 CR_Entity
, glcr.SEGMENT7 CR_Region
, glcr.SEGMENT2 CR_CORP_AC
, glcr.SEGMENT3 CR_CC
, glcr.SEGMENT4 CR_PRJ
, glcr.SEGMENT6 CR_Interco
, glcr.SEGMENT8 CR_Type
, glcr.SEGMENT9 CR_Prd
, glcr.SEGMENT5 CR_Orgin
, glcr.SEGMENT10 CR_Res

FROM
PA.PA_EXPENDITURE_ITEMS_ALL paei
,PA.PA_EXPENDITURES_ALL pae
, PA.PA_COST_DISTRIBUTION_LINES_ALL pacdl
,GL.GL_CODE_COMBINATIONS gldr
,GL.GL_CODE_COMBINATIONS glcr
,pa_projects_All p
,per_people_f hr
,HR_ALL_ORGANIZATION_UNITS hr2
,HR_ALL_ORGANIZATION_UNITS hr3
,pa_tasks pt
where
paei.EXPENDITURE_ID = pae.EXPENDITURE_ID
and p.project_id = paei.project_id
and pae.incurred_by_person_id = hr.Person_id
and paei.EXPENDITURE_ITEM_ID = pacdl.EXPENDITURE_ITEM_ID
and pacdl.DR_CODE_COMBINATION_ID = gldr.CODE_COMBINATION_ID
and pacdl.CR_CODE_COMBINATION_ID = glcr.CODE_COMBINATION_ID
and paei.ORG_ID = hr2.organization_id
and p.ORG_ID = hr3.organization_id
and pt.task_id = paei.task_id
and paei.SYSTEM_LINKAGE_FUNCTION = 'ST'
and pacdl.TRANSFER_STATUS_CODE = 'A'
and trunc(pacdl.GL_DATE) > to_date('31-DEC-2007', 'DD-MON-YYYY')
and trunc(pacdl.GL_DATE) < to_date('26-MAY-2008', 'DD-MON-YYYY')
and SYSDATE BETWEEN hr.effective_start_date AND hr.effective_end_date
group by

hr2.name
,hr3.name
,p.segment1
,pt.Task_number
,pt.task_name
,hr.full_name
, paei.EXPENDITURE_TYPE
, pacdl.TRANSFER_STATUS_CODE
, to_Char(paei.EXPENDITURE_ITEM_DATE , 'Mon-YYYY')
, to_char(pacdl.GL_DATE,'Mon-YYYY')
,pacdl.denom_currency_code
,pacdl.ACCT_CURRENCY_CODE
, gldr.SEGMENT1
, gldr.SEGMENT7
, gldr.SEGMENT2
, gldr.SEGMENT3
, gldr.SEGMENT4
, gldr.SEGMENT6
, gldr.SEGMENT8
, gldr.SEGMENT9
, gldr.SEGMENT5
, gldr.SEGMENT10
, glcr.SEGMENT1
, glcr.SEGMENT7
, glcr.SEGMENT2
, glcr.SEGMENT3
, glcr.SEGMENT4
, glcr.SEGMENT6
, glcr.SEGMENT8
, glcr.SEGMENT9
, glcr.SEGMENT5
, glcr.SEGMENT10
Re: PA and GL LINK [message #329796 is a reply to message #329181] Thu, 26 June 2008 10:08 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Minor detail:
AND    SYSDATE BETWEEN hr.effective_start_date AND hr.effective_end_date 

This should be:
AND    paei.expenditure_item_date BETWEEN hr.effective_start_date AND
       hr.effective_end_date


(now you're checking what the current data for this person is, while this should be the data for this person as it was at the time the expenditure was spent).
Previous Topic: Cut the word nicely to the next line?
Next Topic: Please give me query to find out the Elements which are taged to one employee.
Goto Forum:
  


Current Time: Fri Dec 09 11:58:07 CST 2016

Total time taken to generate the page: 0.12018 seconds