Use-Case: This SQL query is used to fetch important details about blanket purchase order (PO) approvals. It pulls information like approval actions, dates, revision numbers, PO amounts, and checks if a second approval was needed.
Introduction: This SQL query helps track the approval history of blanket purchase orders (POs). It shows important details like PO numbers, approval dates, revision numbers, and amounts. It also checks if a second approval was needed, making it easier to understand the approval process and key actions.
The SQL query serves the following purposes:
- Retrieves key details for blanket POs, such as the PO number, approval dates, and who performed the action.
- Includes financial information like PO limits, total amounts, and currency codes.
- Calculates the total amount in CAD for easier reference.
- Flags whether a second approval (second sign-off) was required.
SQL Query:
SELECT DISTINCT
pah.object_id,
pha.segment1 AS po_number,
pah.action_code,
pah.action_date,
papf.full_name AS performed_by,
pah.note,
pha.currency_code,
pha.rate,
pha.amount_limit,
pha.blanket_total_amount,
( pha.rate * pha.blanket_total_amount ) AS blanket_total_amount_cad,
abc.second_sign,
pah.object_revision_num AS revision_number
FROM
po_action_history pah,
per_all_people_f papf,
po_headers_all pha,
(
SELECT
object_id,
action_code,
object_revision_num,
CASE
WHEN COUNT(action_code) <= 1 THEN ‘N’
ELSE ‘Y’
END
AS second_sign
FROM
po_action_history
WHERE
1 = 1
AND object_sub_type_code = ‘BLANKET’
AND action_code = ‘FORWARD’
GROUP BY
object_id,
action_code,
object_revision_num
HAVING
COUNT(action_code) > 0
) abc
WHERE
pah.action_code = ‘APPROVE’
AND pah.employee_id = papf.person_id
AND pah.object_id = pha.po_header_id
AND pah.object_id = abc.object_id
AND pah.object_revision_num = abc.object_revision_num
AND pah.object_sub_type_code = ‘BLANKET’
AND pha.segment1 = ‘Enter the PO Number ‘ — PO Number for which we need to get the details
ORDER BY
pha.segment1 ASC
Tables Used
- po_action_history (for action details like approval or forwarding).
- po_headers_all (for PO amounts, currency, and limits).
- per_all_people_f (for employee information).
Filters: The query focuses on:
- Blanket POs (object_sub_type_code = ‘BLANKET’).
- Approved actions (Action_Code = ‘APPROVE’).
Sorting Results
The output is ordered by PO number and revision number for better readability.
Calculations
It calculates the blanket total amount in CAD by multiplying the rate with the total amount (pha.rate * pha.blanket_total_amount).
Summary:
This query helps you track blanket POs by showing who approved them, when they were approved, and how much they’re worth. It also checks if a second approval was needed, making it easy to see the full approval process and all the important details.
