Find out the invoices in Accounts Payable which aren’t fully paid out for a business unit.

SQL Sample Statement Find out the invoices in Accounts Payable which aren’t fully paid out for a business unit. The SQL code sample is a query for retrieving invoice data from Oracle Cloud for a specific business unit where the invoice amount is greater than the amount paid. This is a common scenario for accounts […]

Determine purchase orders which are still open for a business unit.

SQL Sample Statement Determine purchase orders which are still open for a business unit. SELECT fbu.bu_name procurement_business_unit, poh.segment1 po_number, trunc(poh.creation_date) po_creation_date, pol.line_num line_number, pol.item_description item_description, pol.list_price line_list_price, pv.segment1 supplier_number, pvs.vendor_site_code supplier_site_code FROM po_headers_all poh INNER JOIN po_lines_all pol ON poh.po_header_id = pol.po_header_id AND coalesce(poh.document_status, ‘OPEN’) IN ( ‘OPEN’ ) INNER JOIN poz_suppliers pv ON poh.vendor_id […]

Retrieve employee headcount as of today.

SQL Sample Statement Retrieve employee headcount as of today. SELECT COUNT(1) FROM per_person_secured_list_v ppf, per_assignment_secured_list_v paam, per_periods_of_service pps, per_person_type_usages_f pptu WHERE 1 = 1 AND ppf.person_id = paam.person_id AND paam.period_of_service_id = pps.period_of_service_id AND paam.person_id = pptu.person_id AND paam.assignment_status_type = ‘ACTIVE’ AND paam.assignment_type = ‘E’ AND paam.effective_latest_change = ‘Y’ AND pps.primary_flag = ‘Y’ AND pptu.system_person_type IN […]