select
x.*
from
(
select
hou.name operating_unit,
mp.organization_code,
pha.segment1 po_number,
pra.release_num,
pla.line_num,
aps.vendor_name supplier,
assa.vendor_site_code supplier_site,
msiv.concatenated_segments item,
nvl(msiv.description,pla.item_description) item_description,
xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) item_type,
pla.unit_meas_lookup_code uom,
pha.currency_code currency,
pla.unit_price,
(select sum(plla.quantity-nvl(plla.quantity_cancelled,0)) from po_line_locations_all plla where plla.po_line_id=pla.po_line_id and nvl(plla.po_release_id,-99)=nvl(pra.po_release_id,-99) and plla.ship_to_organization_id=mp.organization_id) quantity_requested,
max(case when rt.transaction_type='RECEIVE' then rt.transaction_date end) last_receipt_date,
max(case when rt.transaction_type='RECEIVE' then rsh.receipt_num end) keep (dense_rank last order by case when rt.transaction_type='RECEIVE' then rt.transaction_date end nulls first) last_receipt_number,
sum(case when rt.transaction_type='RECEIVE' then rt.source_doc_quantity end) quantity_received,
sum(case when rt.transaction_type='CORRECT' and rt_par.transaction_type='RECEIVE' then rt.source_doc_quantity end) quantity_corrected_receipt,
sum(case when rt.transaction_type='DELIVER' then rt.source_doc_quantity end) quantity_delivered,
sum(case when rt.transaction_type='CORRECT' and rt_par.transaction_type='DELIVER' then rt.source_doc_quantity end) quantity_corrected_delivery,
sum(case when rt.transaction_type='RECEIVE' or rt.transaction_type='CORRECT' and rt_par.transaction_type='RECEIVE' then rt.source_doc_quantity end) total_quantity_received,
sum(case when rt.transaction_type='DELIVER' or rt.transaction_type='CORRECT' and rt_par.transaction_type='DELIVER' then rt.source_doc_quantity end) total_quantity_delivered,
round(sum(case when rt.transaction_type='RECEIVE' or rt.transaction_type='CORRECT' and rt_par.transaction_type='RECEIVE' then rt.source_doc_quantity*rt.po_unit_price end),2) amount_received,
round(sum(case when rt.transaction_type='DELIVER' or rt.transaction_type='CORRECT' and rt_par.transaction_type='DELIVER' then rt.source_doc_quantity*rt.po_unit_price end),2) amount_delivered,
nvl(sum(case
when rt.transaction_type='RECEIVE' or rt.transaction_type='CORRECT' and rt_par.transaction_type='RECEIVE' then rt.source_doc_quantity
when rt.transaction_type='DELIVER' or rt.transaction_type='CORRECT' and rt_par.transaction_type='DELIVER' then -rt.source_doc_quantity
end),0) quantity_variance,
nvl(round(sum(case
when rt.transaction_type='RECEIVE' or rt.transaction_type='CORRECT' and rt_par.transaction_type='RECEIVE' then rt.source_doc_quantity*rt.po_unit_price
when rt.transaction_type='DELIVER' or rt.transaction_type='CORRECT' and rt_par.transaction_type='DELIVER' then -rt.source_doc_quantity*rt.po_unit_price
end),2),0) cost_variance,
sum(case when rt.transaction_type='RETURN TO VENDOR' or rt.transaction_type='CORRECT' and rt_par.transaction_type='RETURN TO VENDOR' then rt.source_doc_quantity end) quantity_returned_to_vendor,
sum(case when rt.transaction_type='RETURN TO RECEIVING' or rt.transaction_type='CORRECT' and rt_par.transaction_type='RETURN TO RECEIVING' then rt.source_doc_quantity end) quantity_returned_to_receiving
from
rcv_transactions rt,
rcv_transactions rt_par,
rcv_shipment_headers rsh,
po_headers_all pha,
po_releases_all pra,
po_lines_all pla,
ap_suppliers aps,
ap_supplier_sites_all assa,
mtl_system_items_vl msiv,
mtl_parameters mp,
hr_operating_units hou
where
1=1 and
rt.transaction_type in ('RECEIVE','DELIVER','CORRECT','RETURN TO VENDOR','RETURN TO RECEIVING') and
rt.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and
rt.parent_transaction_id=rt_par.transaction_id(+) and
(rt.transaction_type<>'CORRECT' or rt_par.transaction_type in ('RECEIVE','DELIVER','RETURN TO VENDOR','RETURN TO RECEIVING')) and
rt.shipment_header_id=rsh.shipment_header_id and
rt.po_header_id=pha.po_header_id and
rt.po_release_id=pra.po_release_id(+) and
rt.po_line_id=pla.po_line_id and
pha.vendor_id=aps.vendor_id and
pha.vendor_site_id=assa.vendor_site_id(+) and
pla.item_id=msiv.inventory_item_id(+) and
rt.organization_id=msiv.organization_id(+) and
rt.organization_id=mp.organization_id and
pha.org_id=hou.organization_id and
pha.org_id in (select mgoat.organization_id from mo_glob_org_access_tmp mgoat union select fnd_global.org_id from dual where fnd_release.major_version=11)
group by
hou.name,
mp.organization_code,
pha.segment1,
pra.release_num,
pla.line_num,
aps.vendor_name,
assa.vendor_site_code,
msiv.concatenated_segments,
nvl(msiv.description,pla.item_description),
msiv.item_type,
pla.unit_meas_lookup_code,
pha.currency_code,
pla.unit_price,
pla.po_line_id,
pra.po_release_id,
mp.organization_id
) x
where
2=2
order by
x.operating_unit,
x.organization_code,
x.po_number,
x.release_num,
x.line_num |
