PPAV.

NAME
PPAV.SEGMENT1
PTV.TASK_NAME
PTV.TASK_NUMBER
HAOU.NAME
PTSV.TRANSACTION_SOURCE
PTDV.DOCUMENT_NAME
PTDEV.DOC_ENTRY_NAME
PETV.EXPENDITURE_TYPE_NAME
PEIA.EXPENDITURE_ITEM_ID
PEIA.EXPENDITURE_ITEM_DATE
FABUV.BU_NAME
HAOU.NAME
PPNF.FIRST_NAME
PAPF.PERSON_NUMBER
PEIA.QUANTITY
PEIA.UNIT_OF_MEASURE
PEIA.ACCT_RAW_COST
PEIA.ACCT_BURDENED_COST
PWTV.NAME
PWTV.BILLABLE_CAPITALIZABLE_FLAG
PTV.BILLABLE_FLAG
PTV.CAPITALIZABLE_FLAG
PEGA.EXPENDITURE_GROUP

PJF_PROJECTS_ALL_VL PPAV
PJF_TASKS_V PTV
hr_all_organization_units HAOU
PJF_TXN_SOURCES_VL PTSV
PJF_TXN_DOCUMENT_VL PTDV
PJF_TXN_DOC_ENTRY_VL PTDEV
PJF_EXP_TYPES_VL PETV
PJC_EXP_ITEMS_ALL PEIA
FUN_ALL_BUSINESS_UNITS_V FABUV
PER_PERSON_NAMES_F PPNF
PER_ALL_PEOPLE_F PAPF
PJF_WORK_TYPES_VL PWTV
PJC_EXP_GROUPS_ALL PEGA
============
SELECT DISTINCT
glm.Ledger_name
,glm.legal_entity
,glm.ou_name
,glm.period_name
,glm.source
,glm.je_cagegory
,glm.ACTUAL_FLAG
,glm.Company
,glm.account
,glm.Line_of_Business
,glm.Cost_Centre
,glm.Location
,glm.Facility
,glm.Intercompany
,glm.Future1
,glm.Future2
,glm.accounted_cr
,glm.accounted_dr
,glm.STAT_Amount
,glm.creation_date
,glm.Reference21
,glm.Reference22
,glm.Reference23
,glm.segment30
,glm.currency_code
,glm.Reference24
,glm.Reference25
,glm.Reference26
,glm.Reference27
,glm.Reference28
,glm.Reference29
,glm.Reference30
,glm.posted_date
,glm.Batch_Name
,ppa.project_id
,ppa.name Project_name
,ppa.segment1 Project_Number
,pt.task_name
,pt.task_number
,pts.transaction_source
,ptd.document_name
,ptde.doc_entry_name
,pet.expenditure_type_name
,pei.expenditure_item_id Expenditure_item_Number
,pei.expenditure_item_date
,fnbu.bu_name
,person.employee_name
,person.employee_number
,pei.quantity
,pei.unit_of_measure
,pei.acct_raw_cost raw_cost
,pei.acct_burdened_cost burdened_cost
,pwtt.name Work_Type
,pwtb.billable_capitalizable_flag billable
,pt.capitalizable_flag
,pei.user_batch_name Expenditure_Batch_Name
,ppev.Project_Unit_Name Project_Unit
,pih.invoice_status_code invoice_status
,pei.bill_hold_flag Hold_Invoice
,pei.Invoiced_Percentage
,pei.revenue_recognized_flag Revenue_Status
,pei.revenue_hold_flag Hold_Revenue
,ps.vendor_name Supplier_Name
,ps.segment1 Supplier_Number
,pppd.po_number Purchase_Order
,pppd.po_line_id Purchase_Order_Line
,pect.expenditure_category_name Expenditure_Category
,petb.revenue_category_code
,pei.RAW_COST_RATE
,pei.BURDEN_COST_RATE
,pih.invoice_num Supplier_Invoice_Number
,pcomm.expenditure_comment exp_Comment
,non_labor.nlr_name Nonlabor_Resource
,non_labor.non_res_org Nonlabor_Resource_Organization
,pj.name High_Class_Job
,pjrv.rate
,TO_CHAR(pei.ACCT_RATE_DATE,'DD-MON-YYYY') rate_date
,pldf.location_name
,pman.DISPLAY_NAME PManager
,paf.assignment_number
FROM pjf_projects_all_vl ppa
,pjf_tasks_v pt
,pjc_exp_items_all pei
,pjf_txn_sources_vl pts
,pjf_txn_document_tl ptd
,pjf_txn_doc_entry_vl ptde
,pjf_exp_types_vl pet
,fun_names_business_units_v fnbu
--,per_all_people_f pap
--,per_person_names_f ppn
,(SELECT pap.person_id
,ppn.full_name employee_name
,pap.person_number employee_number
FROM per_all_people_f pap
,per_person_names_f ppn
WHERE pap.person_id = ppn.person_id
AND ppn.name_type ='GLOBAL') person
,pjf_work_types_tl pwtt
,pjf_work_types_b pwtb
,pjc_projects_expend_v ppev
,pjb_bill_trxs pbt
,pjb_inv_line_dists pild
,pjb_invoice_lines pil
,pjb_invoice_headers pih
,PJC_PRJ_PO_DIST_V pppd
,poz_suppliers_v ps
,pjf_exp_types_b petb
,pjf_exp_categories_tl pect
,pjc_exp_comments pcomm
,(SELECT pnlrb.non_labor_resource_id
,pnlrb.expenditure_type_id
,pnlrt.description
,pnlrt.nlr_name
,PJFORG.ORG_ID
,HRORG.ORGANIZATION_ID
,HRORG.NAME non_res_org
,HRORG.EFFECTIVE_START_DATE
,HRORG.EFFECTIVE_END_DATE
,CASE WHEN (PJFORG.INACTIVE_DATE IS NULL) THEN 'Y'
WHEN (PJFORG.INACTIVE_DATE IS NOT NULL) THEN 'N'
END ACTIVE_FLAG
FROM HR_ORGANIZATION_V HRORG
,PJF_ALL_ORGANIZATIONS PJFORG
,PJF_NON_LABOR_RES_ORGS pnlro
,pjf_non_labor_res_b pnlrb
,pjf_non_labor_res_tl pnlrt
WHERE pnlrb.equipment_resource_flag='Y'
AND pnlrb.non_labor_resource_id = pnlrt.non_labor_resource_id
AND pnlrt.non_labor_resource_id = pnlro.non_labor_resource_id
AND pnlro.organization_id = pjforg.organization_id
AND PJFORG.ORGANIZATION_ID = HRORG.ORGANIZATION_ID
AND PJFORG.PA_ORG_USE_TYPE = 'EXPENDITURES'
AND HRORG.CLASSIFICATION_CODE = 'PA_EXPENDITURE_ORG'
) non_labor
,per_jobs pj
,PJF_JOB_RATES_V pjrv
,per_assignments_f paf
,PER_LOCATION_DETAILS_F_VL pldf
,PJT_PRIMARYPROJMANAGER_V pman
--,pjb_rev_distributions prd
,xla_distribution_links xdl
,xla_ae_headers xah
,xla_ae_lines xal
--,xla_events xe
,gl_import_references gir
,(SELECT gl.name Ledger_name
,xep.name legal_entity
,hou.name ou_name
,gjh.period_name
,gjh.je_source source
,gjh.je_category je_cagegory
,gjh.ACTUAL_FLAG
,gcc.SEGMENT1 Company
,gcc.SEGMENT2 Account
,gcc.SEGMENT3 Line_of_Business
,gcc.SEGMENT4 Cost_Centre
,gcc.SEGMENT5 Location
,gcc.SEGMENT6 Facility
,gcc.SEGMENT7 Intercompany
,gcc.SEGMENT8 Future1
,gcc.SEGMENT9 Future2
,CASE WHEN gjl.currency_code != 'STAT' THEN gjl.accounted_cr END
accounted_cr
,CASE WHEN gjl.currency_code != 'STAT' THEN gjl.accounted_dr END
accounted_dr
,CASE WHEN gjl.currency_code = 'STAT' THEN
NVL(gjl.accounted_cr,gjl.accounted_dr) END STAT_Amount
,TO_CHAR(gjl.creation_date,'DD/MON/YYYY') creation_date
,gjl.REFERENCE_1 Reference21
,gjl.REFERENCE_2 Reference22
,gjl.REFERENCE_3 Reference23
,'00' segment30
,gjl.currency_code
,NULL Reference24
,NULL Reference25
,NULL Reference26
,NULL Reference27
,NULL Reference28
,NULL Reference29
,NULL Reference30
,TO_CHAR(gjh.posted_date, 'DD/MON/YYYY') posted_date
,gjb.name Batch_Name
,gl.ledger_id
,gjb.je_batch_id
,gjb.default_period_name
,gjh.je_header_id
,gjl.je_line_num
FROM gl_ledgers gl
,hr_operating_units hou
,xle_entity_profiles xep
,gl_je_headers gjh
,gl_code_combinations gcc
,gl_je_lines gjl
,gl_je_batches gjb
WHERE gl.ledger_id = hou.set_of_books_id
AND hou.default_legal_context_id = xep.legal_entity_id
AND xep.transacting_entity_flag = 'Y'
AND gl.ledger_id = gjh.ledger_id
AND gl.chart_of_accounts_id = gcc.chart_of_accounts_id
AND gl.ledger_id = gjl.ledger_id
AND gjh.je_header_id = gjl.je_header_id
AND gjh.period_name = gjl.period_name
AND gcc.code_combination_id = gjl.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.period_name = gjb.default_period_name) glm
-- WHere clause
WHERE ppa.project_id = pt.project_id
AND ppa.project_id = pei.project_id
AND pei.task_id =pt.task_id
AND pei.transaction_source_id =pts.transaction_source_id
AND pei.document_id = ptd.document_id
AND pei.document_id = ptde.document_id(+)
AND pei.document_id = ptde.document_id(+)
AND pei.expenditure_type_id = pet.expenditure_type_id(+)
AND ppa.org_id = fnbu.bu_id
--AND pei.incurred_by_person_id=ppn.person_id
--AND ppn.name_type ='GLOBAL'
AND pei.incurred_by_person_id = person.person_id(+)
--AND ppa.work_type_id = pwtt.work_type_id(+)
AND pei.work_type_id = pwtt.work_type_id(+)
AND pei.work_type_id = pwtb.work_type_id(+)
AND pei.expenditure_item_id =pbt.transaction_id
AND ppa.project_id =ppev.project_id (+)
--AND pbt.org_id = fnbu.bu_id(+)
AND pbt.bill_trx_id = pild.bill_trx_id(+)
AND pil.invoice_line_id = pild.invoice_line_id(+)
AND pih.invoice_id = pil.invoice_id(+)
AND ppa.project_id = pppd.project_id
AND pt.task_id = pppd.task_id
AND pei.vendor_id = ps.vendor_id(+)
AND pei.expenditure_type_id=petb.expenditure_type_id(+)
AND petb.expenditure_category_id = pect.expenditure_category_id(+)
AND pei.EXPENDITURE_ITEM_ID = pcomm.EXPENDITURE_ITEM_ID(+)
AND petb.expenditure_type_id= non_labor.expenditure_type_id(+)
AND pei.person_job_id =pj.job_id
AND pj.job_id = pjrv.job_id
AND pj.job_id = paf.job_id
AND paf.location_id = pldf.location_id(+)
AND ppa.project_id = pman.Project_Id(+)
AND pei.expenditure_item_id = xdl.source_distribution_id_num_1
AND xdl.ae_header_id = xah.ae_header_id
AND xah.ae_header_id = xal.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
--AND xal.event_id = xe.event_id
AND xal.gl_sl_link_id = gir.gl_sl_link_id(+)
--AND xal.gl_sl_link_table = gir.gl_sl_link_table
AND gir.je_header_id = glm.je_header_id(+)
AND gir.je_line_num = glm.je_line_num(+)
AND gir.je_batch_id = glm.je_batch_id(+)
--AND ppa.project_id=300000013987228

SELECT pppd.*
FROM pjf_projects_all_vl ppa
,PJC_PRJ_PO_DIST_V pppd
,pjf_tasks_v pt
WHERE ppa.project_id = pt.project_id
AND ppa.project_id = pppd.project_id
AND pppd.task_id = pt.task_id
AND ppa.project_id = 300000011242344

select ppm.project_id
from PRJ_PROJECT_MANAGER_V ppm
,pjf_projects_all_vl ppa
WHERE ppa.project_id = ppm.project_id

SELECT peia.* ,prd.linked_project_id FROM pjc_exp_items_all peia

SELECT pei.*
FROM pjf_projects_all_vl ppa
,pjf_tasks_v pt
,pjc_exp_items_all pei
WHERE ppa.project_id = pt.project_id
AND ppa.project_id = pei.project_id
--AND ppa.work_type_id = pei.work_type_id
AND pt.task_id = pei.task_id
PJB_XLA_EXP_REF_V