SELECT *
FROM ( SELECT SUM (unit_price * quantity * gdr.CONVERSION_RATE) req_cost,
b.requisition_header_id,
a.segment1,
a.org_id, b.DESTINATION_TYPE_CODE, gdr.conversion_type
FROM po_requisition_headers_all a,
po_requisition_lines_all b,
org_organization_definitions ood,
gl_sets_of_books gsob,
gl_daily_rates gdr
WHERE a.requisition_header_id = b.requisition_header_id
AND a.org_id = ood.organization_id
AND ood.set_of_books_id = gsob.set_of_books_id
AND gsob.currency_code = gdr.from_currency
-- AND gdr.conversion_type = 'Corporate'
AND gdr.to_currency = 'USD'
AND gdr.conversion_date = TRUNC (SYSDATE)
AND NVL (b.cancel_flag, 'N') = 'N'
AND a.approved_date > SYSDATE - 365
GROUP BY b.requisition_header_id, a.segment1, a.org_id, b.DESTINATION_TYPE_CODE, gdr.conversion_type)
WHERE req_cost > 100000
No comments:
Post a Comment