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  |
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  |
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 #329181 is a reply to message #324393] |
Tue, 24 June 2008 07:49 |
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 |
skooman
Messages: 913
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).
|
|
|
|
Goto Forum:
Current Time: Wed Jul 03 12:50:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
