PO Requisitions and Project Details

select hou.name ope_unit
,prh.segment1 req_number
,pol.currency_code
,pol.quantity
,pol.quantity_received
,pol.quantity*pol.unit_price amount
,ppa.segment1 project
,pat.task_number
,pod.expenditure_type
,(select name from apps.hr_all_organization_units where organization_id=pod.expenditure_organization_id) EXPENDITURE_ORGANIZATION
,(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pod.code_combination_id ) charge_accounts
,(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pod.accrual_account_id ) accrual_accounts
,trunc(prh.creation_date) requ_cre_date
,povs.PAY_GROUP_LOOKUP_CODE
,povs.PAYMENT_METHOD_LOOKUP_CODE
,pov.vendor_name
,povs.vendor_site_code
,polt.line_type
,mc.segment1'.'mc.segment2
from
po_requisition_headers_all prh,
po_requisition_lines_all pol,
po_vendors pov,
po_vendor_sites_all povs,
po_line_types polt,
po_req_distributions_all pod,
mtl_categories mc,
pa_projects_all ppa,
pa_tasks pat,
hr_all_organization_units hou
where 1=1--prh.segment1='800034786'
and prh.requisition_header_id = pol.requisition_header_id
and pol.vendor_id = pov.vendor_id
and pol.vendor_site_id = povs.vendor_site_id
and pol.requisition_line_id = pod.requisition_line_id
and pol.line_type_id = polt.line_type_id
and pol.category_id = mc.category_id
and pod.project_id = ppa.project_id
and pod.task_id = pat.task_id
and prh.org_id = hou.organization_id
--and trunc(prh.creation_date) >= to_date('22-SEP-2009','DD-MON-YYYY')
order by 1,2

Purchase Orders with Project Details

SELECT
hou.name Operating_unit,
ph.segment1 po_num,
ph.currency_code,
pla.quantity quantity_ordered,
pla.quantity_received,
pl.line_num,
pl.quantity Line_quantity,
pl.unit_price*pl.quantity Amt,
ppa.segment1 project_number,
pt.task_number ,
pda.expenditure_type,
--api.invoice_num,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select gcc.CONCATENATED_SEGMENTS from gl_code_combinations_kfv gcc
where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts ,
trunc(ph.creation_date),
povs.PAY_GROUP_LOOKUP_CODE,
povs.PAYMENT_METHOD_LOOKUP_CODE,
vendor_name,
vendor_site_code,
povs.attribute1
from apps.po_headers_all ph,
apps.po_lines_all pl,
apps.po_line_locations_all pla,
apps.hr_operating_units hou,
apps.po_line_types plt,
apps.pa_projects_all ppa,
apps.po_distributions_all pda,
apps.pa_tasks pt,
apps.gl_code_combinations_kfv gcc,
apps.hr_locations hl,
apps.po_vendors pov,
apps.po_vendor_sites_all povs
--ap_invoices_all api
where 1=1
--and ph.segment1='TU672620'
and ph.po_header_id=pl.po_header_id
and pl.po_line_id=pla.po_line_id
and pl.line_type_id=plt.line_type_id
and ph.po_header_id=pda.po_header_id
and pda.line_location_id=pla.line_location_id
and pda.project_id=ppa.project_id
and pda.task_id=pt.task_id(+)
--and (pda.project_id is null or pda.task_id is null)
and gcc.code_combination_id =pda.code_combination_id
and ph.AUTHORIZATION_STATUS ='APPROVED'
--AND trunc(ph.creation_date) >= to_date(:p_date) -- MM/DD/YYYY
--and ph.APPROVED_FLAG ='Y'
and hl.location_id=ph.ship_to_location_id
and hou.organization_id=pda.org_id
and ph.vendor_id = pov.vendor_id
and ph.vendor_site_id = povs.vendor_site_id
--and api.po_header_id=ph.po_header_id
--and api.project_id=ppa.project_id
--and api.task_id=pt.task_id