1.Query to find requisition total amount:
SELECT nvl(SUM(decode(order_type_lookup_code,
'RATE', amount,
'FIXED PRICE', amount,
quantity * unit_price)), 0) req_amount,
po_ame_setup_pvt.get_function_currency(max(requisition_header_id)) req_func_currency,
po_ame_setup_pvt.get_rate_type(max(requisition_header_id)) rate_type
from po_requisition_lines_all
where requisition_header_id = po_ame_setup_pvt.get_new_req_header_id(3915049)
and nvl(cancel_flag, 'N') = 'N'
and nvl(modified_by_agent_flag, 'N') = 'N';
select approved_date from po_requisition_headers_all where requisition_header_id=3915049;
2.Latest Change Request/Change Order details:
Change Order gets created for a Requisition when the req lines created onto Purchase order and then a change need in Requisition.
SELECT MAX (change_request_group_id) change_request_group_id, MAX(last_update_date) last_update_date
FROM po_change_requests
WHERE document_header_id = p_transaction_id
AND document_type = 'REQ';
PO_REQUISITION_HEADERS_ALL.CHANGE_PENDING_FLAG => Indicates whether requisition is in Change Order process or not
3. Requisition amount from a change request
SELECT NVL (
SUM (
DECODE (
pcr3.action_type,
'CANCELLATION', 0,
DECODE (
prl.order_type_lookup_code,
'RATE', NVL (pcr4.new_amount, prl.amount),
'FIXED PRICE', NVL (pcr4.new_amount, prl.amount),
NVL (pcr1.new_price, prl.unit_price)
* NVL (pcr2.new_quantity, prl.quantity)))),
0)
FROM po_requisition_lines_all prl,
po_change_requests pcr1,
po_change_requests pcr2,
po_change_requests pcr3,
po_change_requests pcr4
WHERE prl.requisition_line_id = pcr1.document_line_id(+)
AND pcr1.change_request_group_id(+) =
l_change_request_group_id
AND pcr1.request_level(+) = 'LINE'
AND pcr1.change_active_flag(+) = 'Y'
AND pcr1.new_price(+) IS NOT NULL
AND prl.requisition_line_id = pcr2.document_line_id(+)
AND pcr2.change_request_group_id(+) =
l_change_request_group_id
AND pcr2.request_level(+) = 'LINE'
AND pcr2.action_type(+) = 'DERIVED'
AND pcr2.new_quantity(+) IS NOT NULL
AND prl.requisition_line_id = pcr4.document_line_id(+)
AND pcr4.change_request_group_id(+) =
l_change_request_group_id
AND pcr4.request_level(+) = 'LINE'
AND pcr4.action_type(+) = 'DERIVED'
AND pcr4.new_amount(+) IS NOT NULL
AND prl.requisition_line_id = pcr3.document_line_id(+)
AND pcr3.change_request_group_id(+) =
l_change_request_group_id
AND pcr3.request_level(+) = 'LINE'
AND pcr3.action_type(+) = 'CANCELLATION'
AND prl.requisition_header_id = p_transaction_id
AND NVL (prl.modified_by_agent_flag, 'N') = 'N'
AND NVL (prl.cancel_flag, 'N') = 'N';
4. Last action on PO Requisition:
SELECT TRUNC (MAX (NVL (action_date, SYSDATE)))
FROM PO_ACTION_HISTORY
WHERE 1 = 1
AND object_type_code = 'REQUISITION'
AND object_id = p_transaction_id
AND action_code = 'SUBMIT';
5. Project Managers for Requisition:
SELECT ppp.person_id
FROM po_requisition_lines_all pla,
po_req_distributions_all pda,
pa_project_players ppp
WHERE 1 = 1
AND requisition_header_id = p_transaction_id
AND pda.requisition_line_id = pla.requisition_line_id
AND pda.project_id = ppp.project_id
AND ppp.person_id IS NOT NULL
AND TRUNC (SYSDATE) BETWEEN TRUNC (
NVL (ppp.start_date_active,
SYSDATE))
AND TRUNC (
NVL (ppp.end_date_active,
SYSDATE + 1))
AND ppp.project_role_type = 'PROJECT MANAGER'
GROUP BY ppp.person_id;
6. Task managers for a requisition:
SELECT pt.task_manager_person_id person_id
FROM po_requisition_lines_all pla,
po_req_distributions_all pda,
pa_tasks pt
WHERE 1 = 1
AND requisition_header_id = p_transaction_id
AND pda.requisition_line_id = pla.requisition_line_id
AND pda.task_id = pt.task_id
AND pt.task_manager_person_id IS NOT NULL
AND TRUNC (SYSDATE) BETWEEN TRUNC (pt.start_date)
AND TRUNC (
NVL (pt.completion_date,
SYSDATE + 1));
7. Requisition Preparer and requester details:
SELECT DISTINCT h.org_id,
h.approved_date,
h.preparer_id,
l.to_person_id requester_id
FROM po_requisition_headers_all h, po_requisition_lines_all l
WHERE h.requisition_header_id = p_transaction_id
AND h.requisition_header_id = l.requisition_header_id;
