select po.segment1 po_num,
po.creation_date po_date,
po.approved_date,
decode (po.segment1,
null, '',
nvl (po.authorization_status, 'INCOMPLETE'))
po_status,
poven.vendor_name,
pol.line_num po_line_num,
xxmb_get_item_code (pol.item_id, poloc.ship_to_organization_id)
po_productid,
pol.item_description po_item_description,
podis.quantity_ordered po_quantity_ordered,
pol.unit_price po_unit_price,
(pol.unit_price * podis.quantity_ordered) po_amt,
po.currency_code po_currency_code,
podis.rate po_rate,
decode (po.currency_code,
'USD', pol.unit_price * podis.quantity_ordered,
pol.unit_price * podis.rate * podis.quantity_ordered)
po_amt_usd,
poloc.quantity_received qty_rec,
poloc.quantity_billed qty_billed,
poloc.need_by_date,
poloc.promised_date,
(select max (transaction_date)
from rcv_transactions
where transaction_type = 'RECEIVE'
and po_distribution_id = podis.po_distribution_id)
receiving_date,
(poloc.promised_date
- (select max (transaction_date)
from rcv_transactions
where transaction_type = 'RECEIVE'
and po_distribution_id = podis.po_distribution_id))
promised_to_rec_days,
(select max (transaction_date)
from rcv_transactions
where transaction_type = 'DELIVER'
and po_distribution_id = podis.po_distribution_id)
deliver_date,
buyer.full_name buyer_name,
(select full_name
from per_all_people_f rec_per
where 1 = 1
and rec_per.person_id =
(select employee_id
from rcv_transactions
where transaction_type = 'RECEIVE'
and po_distribution_id =
podis.po_distribution_id
and rownum = 1)
and sysdate between rec_per.effective_start_date
and rec_per.effective_end_date
and rownum = 1)
receiver_name,
pol.closed_code po_closed_status,
req_details.requisition_num,
decode (
gcc.segment4,
null,
'',
substr (
apps.gl_flexfields_pkg.get_description_sql (
gcc.chart_of_accounts_id,
4,
gcc.segment4
),
1,
40
)
)
segment4_desc,
decode (
gcc.segment5,
null,
'',
substr (
apps.gl_flexfields_pkg.get_description_sql (
gcc.chart_of_accounts_id,
5,
gcc.segment5
),
1,
40
)
)
segment5_desc,
req_details.requestor
from po_headers_all po,
po_vendors poven,
po_lines_all pol,
po_line_locations_all poloc,
po_distributions_all podis,
gl_code_combinations gcc,
(select *
from per_all_people_f
where sysdate between effective_start_date
and effective_end_date) buyer,
(select porh.segment1 requisition_num,
requestor.full_name requestor,
pord.distribution_id
from po_requisition_headers_all porh,
po_requisition_lines_all porl,
po_req_distributions_all pord,
(select *
from per_all_people_f
where sysdate between effective_start_date
and effective_end_date) requestor
where porh.requisition_header_id = porl.requisition_header_id
and porl.requisition_line_id = pord.requisition_line_id
and requestor.person_id(+) = to_person_id) req_details
where po.po_header_id = pol.po_header_id
and po.po_header_id = poloc.po_header_id
and poven.vendor_id = po.vendor_id
and po.po_header_id = podis.po_header_id
and pol.po_line_id = poloc.po_line_id
and pol.po_line_id = podis.po_line_id
and buyer.person_id = po.agent_id
and gcc.code_combination_id = podis.code_combination_id
and req_details.distribution_id(+) = podis.req_distribution_id
