List of Vendor Sites with no invoices, no PO's

Select sites.vendor_site_id,
vend.segment1 Vendor#,
vendor_name,
sites.vendor_site_code,
vend.end_date_active VEND_INACTIVE,
sites.inactive_date SITE_INACTIVE
from po_vendor_sites_all sites,
po_vendors vend,
ap_invoices_all inv,
po_headers_all PO
where vend.vendor_id = sites.vendor_id
and sites.vendor_site_id = inv.vendor_site_id(+)
and inv.vendor_site_id is null
and sites.vendor_site_id = po.vendor_site_id(+)
and po.vendor_site_id is null
and sites.inactive_date < sysdate
order by vendor_name, vendor_site_code;

Read More

Query : To find PO corrections

execute fnd_client_info.set_org_context('ORG_ID');

SELECT pol.po_header_id,pol.po_line_id, rct.po_line_location_id Line_location_id
, sum (nvl(rct1.source_doc_quantity,0) ) Qty_corrected
from rcv_transactions rct
, rcv_transactions rct1
, po_lines pol
, po_line_locations pll
where rct.transaction_type in ( 'RECEIVE' ,'MATCH')
and rct.po_line_location_id = pll.line_location_id
and rct1.transaction_type = 'CORRECT'
and rct1.parent_transaction_id = rct.transaction_id
and rct1.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') NOT IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id
union all
SELECT pol.po_header_id,pol.po_line_id,rct.po_line_location_id Line_location_id
, sum (nvl(rct1.amount,0) ) Qty_corrected
from rcv_transactions rct
, rcv_transactions rct1
, po_lines pol
, po_line_locations pll
where rct.transaction_type in ( 'RECEIVE' ,'MATCH')
and rct.po_line_location_id = pll.line_location_id
and rct1.transaction_type = 'CORRECT'
and rct1.parent_transaction_id = rct.transaction_id
and rct1.po_line_location_id = pll.line_location_id
and rct.po_line_id = pol.po_line_id
and nvl(pol.order_type_lookup_code,'QUANTITY') IN ('RATE','FIXED PRICE')
group by pol.po_header_id,pol.po_line_id,rct.po_line_location_id

Read More