Below query will help to find all PO_Requisitions amounting greater than $100K (USD) and also there functional currency amount.
SELECT prha.requisition_header_id,
prha.segment1,
gdr.conversion_rate,
ROUND (
SUM (
NVL (prda.req_line_quantity, 1)
* NVL (prla.unit_price, 1)
* NVL (
DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
1)),
2)
usd_amount,
gdr.to_currency,
gdr.from_currency,
prla.rate,
ROUND (
SUM (
NVL (prda.req_line_quantity, 1)
* NVL (prla.unit_price, 1)
* NVL (prla.rate, 1)),
2)
line_currency_amount,
prla.currency_code line_currency,
ROUND (
SUM (NVL (prda.req_line_quantity, 1) * NVL (prla.unit_price, 1)),
2)
actual_requisition_amount,
gs.currency_code func_currency,
TRUNC (prha.creation_date) creation_date,
gcc.segment3,
prha.org_id
FROM po_requisition_lines_all prla,
po_req_distributions_all prda,
po_requisition_headers_all prha,
gl_code_combinations_kfv gcc,
gl_sets_of_books gs,
gl_daily_rates gdr
WHERE prha.requisition_header_id = prla.requisition_header_id
AND NVL (prla.cancel_flag, 'N') <> 'Y'
-- AND prha.org_id = 9944
AND prla.requisition_line_id = prda.requisition_line_id
AND prha.authorization_status = 'APPROVED'
AND prda.code_combination_id = gcc.code_combination_id
AND gs.set_of_books_id = prda.set_of_books_id
-- AND gs.currency_code <> prla.currency_code
AND gdr.from_currency = gs.currency_code(+)
AND gdr.to_currency(+) = 'USD'
AND TRUNC (gdr.conversion_date) =
TRUNC (NVL (prla.rate_date, prha.creation_date))
-- AND gcc.segment3 IN ('15510','15610','15620' )
-- AND prha.requisition_header_id = :1
GROUP BY prha.requisition_header_id,
prha.segment1,
prla.rate,
gdr.conversion_rate,
TRUNC (prha.creation_date),
gcc.segment3,
gdr.to_currency,
gdr.from_currency,
gs.currency_code,
prla.currency_code,
prha.org_id
HAVING SUM (
NVL (prda.req_line_quantity, 1)
* NVL (prla.unit_price, 1)
* NVL (DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
1)) > 100000
SELECT prha.requisition_header_id,
prha.segment1,
gdr.conversion_rate,
ROUND (
SUM (
NVL (prda.req_line_quantity, 1)
* NVL (prla.unit_price, 1)
* NVL (
DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
1)),
2)
usd_amount,
gdr.to_currency,
gdr.from_currency,
prla.rate,
ROUND (
SUM (
NVL (prda.req_line_quantity, 1)
* NVL (prla.unit_price, 1)
* NVL (prla.rate, 1)),
2)
line_currency_amount,
prla.currency_code line_currency,
ROUND (
SUM (NVL (prda.req_line_quantity, 1) * NVL (prla.unit_price, 1)),
2)
actual_requisition_amount,
gs.currency_code func_currency,
TRUNC (prha.creation_date) creation_date,
gcc.segment3,
prha.org_id
FROM po_requisition_lines_all prla,
po_req_distributions_all prda,
po_requisition_headers_all prha,
gl_code_combinations_kfv gcc,
gl_sets_of_books gs,
gl_daily_rates gdr
WHERE prha.requisition_header_id = prla.requisition_header_id
AND NVL (prla.cancel_flag, 'N') <> 'Y'
-- AND prha.org_id = 9944
AND prla.requisition_line_id = prda.requisition_line_id
AND prha.authorization_status = 'APPROVED'
AND prda.code_combination_id = gcc.code_combination_id
AND gs.set_of_books_id = prda.set_of_books_id
-- AND gs.currency_code <> prla.currency_code
AND gdr.from_currency = gs.currency_code(+)
AND gdr.to_currency(+) = 'USD'
AND TRUNC (gdr.conversion_date) =
TRUNC (NVL (prla.rate_date, prha.creation_date))
-- AND gcc.segment3 IN ('15510','15610','15620' )
-- AND prha.requisition_header_id = :1
GROUP BY prha.requisition_header_id,
prha.segment1,
prla.rate,
gdr.conversion_rate,
TRUNC (prha.creation_date),
gcc.segment3,
gdr.to_currency,
gdr.from_currency,
gs.currency_code,
prla.currency_code,
prha.org_id
HAVING SUM (
NVL (prda.req_line_quantity, 1)
* NVL (prla.unit_price, 1)
* NVL (DECODE (gdr.from_currency, 'USD', 1, gdr.conversion_rate),
1)) > 100000
No comments:
Post a Comment