R12 OPEN PO DISTRIBUTION

SELECT
ph.segment1 po_num,
hou.name Operating_unit,
pl.line_num,
pda.distribution_num,
--pla.quantity quantity_ordered,
pda.quantity_ordered,
pla.quantity_shipped quantity_shipped,
pla.quantity_cancelled quantity_cancelled,
pla.quantity_billed quantity_billed,
--pla.quantity
--pda.expenditure_item_date,
--pda.expenditure_type,
--ppa.name PROJECT_NAME,
--ppa.project_type,
ph.currency_code,
cast(pla.quantity_received as decimal (15,6)) quantity_received,
--pla.quantity_received,
--pl.quantity Line_quantity,
--pl.unit_price*pl.quantity Amt,
(select CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.code_combination_id ) charge_accounts ,
(select CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where pda.dest_charge_account_id =pda.accrual_account_id ) dest_charge_accounts ,
trunc(ph.creation_date),
povs.PAY_GROUP_LOOKUP_CODE,
povs.PAYMENT_METHOD_LOOKUP_CODE,
vendor_name,
vendor_site_code,
--povs.attribute1,
pla.days_early_receipt_allowed,
        pla.days_late_receipt_allowed,
        pla.receipt_days_exception_code,
        pla.last_accept_date,
        pla.qty_rcv_tolerance,
        pla.qty_rcv_exception_code ,
        --pla.receiving_routing_id,
        pla.enforce_ship_to_location_code,
        rrh.routing_name,
        pda.destination_type_code,
        --pda.deliver_to_person_id,
        --papf.full_name,
        (papf.last_name||','||papf.first_name) requester,
        --pda.deliver_to,
        hl1.location_code deliver_to,
        pda.destination_subinventory,
        --pda.quantity_ordered,
        pda.req_header_reference_num,
        pda.req_line_reference_num,
        pda.rate_date,
        (select CONCATENATED_SEGMENTS from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id =pda.accrual_account_id ) accrual_accounts ,
        (select concatenated_segments from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id=pda.budget_account_id) po_budget_account,
        (select concatenated_segments from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id= pda.variance_account_id) po_variance_account,
        (select concatenated_segments from apps.gl_code_combinations_kfv gcc where gcc.code_combination_id= pda.dest_variance_account_id) po_dest_variance_account,
        ppa.segment1 project_number,
ppa.name "Project_name",
pt.task_number ,
pt.task_name,
pda.award_id,--gms_awards_all to get award_number
pda.expenditure_type,
(select name from apps.hr_all_organization_units where organization_id=pda.expenditure_organization_id) EXPENDITURE_ORGANIZATION,
pda.expenditure_item_date

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,
hr_locations hl,
hr_locations hl1,
apps.po_vendors pov,
apps.po_vendor_sites_all povs,
apps.rcv_routing_headers rrh,
apps.per_all_people_f papf
where 1=1 --ph.segment1=111120004
and ph.closed_code='OPEN'
and TRIM(ph.closed_code) not in( 'Finally Closed','Closed')
--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 ph.AUTHORIZATION_STATUS !='Rejected'
--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 pla.receiving_routing_id=rrh.routing_header_id
and papf.person_id=pda.deliver_to_person_id
and pda.deliver_to_location_id=hl1.location_id
order by ph.segment1