Introduction:
These queries gives the modules used, data volumes, top products they have sold and other details about the Business.
Queries:
The below are the queries we have used to know the Client business details.
–1) AP Invoices
SELECT
to_char(creation_date, ‘Month-YYYY’) month_year,
round(SUM(a1.invoice_amount), 2) total_inv_amt,
round(AVG(a1.invoice_amount), 2) avg_amt,
COUNT(1) total_inv_cnt
FROM
ap_invoices_all a1
WHERE
creation_date BETWEEN ’01-JAN-22′ AND ’31-DEC-22′
GROUP BY
to_char(creation_date, ‘Month-YYYY’);
–2) AR Invoices
SELECT
month_year,
invoice_currency_code,
round(SUM(trx_amt), 2) total_trx_amt,
round(AVG(trx_amt), 2) avg_amt,
COUNT(1) total_trx_cnt
FROM
(
SELECT
a1.customer_trx_id,
SUM(b1.extended_amount) trx_amt,
a1.invoice_currency_code,
to_char(a1.trx_date, ‘Month-YYYY’) month_year
FROM
ra_customer_trx_all a1,
ra_customer_trx_lines_all b1
WHERE
a1.customer_trx_id = b1.customer_trx_id
GROUP BY
a1.customer_trx_id,
a1.invoice_currency_code,
to_char(a1.trx_date, ‘Month-YYYY’)
)
GROUP BY
month_year,
invoice_currency_code;
–3) PO
SELECT
a1.org_id po_org,
to_char(creation_date, ‘Month-YYYY’),
COUNT(1) po_count
FROM
po_headers_all a1
WHERE
a1.creation_date BETWEEN ’01-JAN-22′ AND ’31-DEC-22′
GROUP BY
to_char(creation_date, ‘Month-YYYY’),
a1.org_id;
–4) Customers & Customers by Geography
SELECT
country,
party_type,
COUNT(1) cust_count
FROM
hz_parties
WHERE
party_type IN (
‘ORGANIZATION’,
‘PERSON’
)
GROUP BY
country,
party_type;
–5) Supplier Sites – Org and Country wise
SELECT
a2.country,
a3.name org_name,
COUNT(a2.vendor_site_id)
FROM
ap_supplier_sites_all a2,
hr_operating_units a3
WHERE
1 = 1
AND a3.organization_id = a2.org_id
GROUP BY
a2.country,
a3.name
ORDER by 1,2,3;
–6) Sales Orders per month
SELECT
a1.org_id po_org,
to_char(creation_date, ‘Month-YYYY’),
COUNT(1) so_count
FROM
oe_order_headers_all a1
WHERE
a1.creation_date BETWEEN ’01-JAN-22′ AND ’31-DEC-22′
GROUP BY
to_char(creation_date, ‘Month-YYYY’),
a1.org_id;
–7) Items
SELECT
a1.organization_id,
(
SELECT
organization_name
FROM
org_organization_definitions
WHERE
organization_id = a1.organization_id
) organization_name,
COUNT(1) item_count
FROM
mtl_system_items_b a1
GROUP BY
organization_id;
–8) OU and ledger details
SELECT
a.business_group_id,
a.organization_id ou_id,
a.name ou_name,
a.short_code ou_short_code,
a.set_of_books_id,
a.default_legal_context_id,
b.name legal_entity_name,
c.name ledger_name
FROM
hr_operating_units a,
xle_entity_profiles b,
gl_ledgers c
WHERE
a.default_legal_context_id = b.legal_entity_id
AND a.set_of_books_id = c.ledger_id;
–9) Top 20 products sold, based on quantity
SELECT
*
FROM
(
SELECT
inventory_item_id,
ordered_item,
SUM(ordered_quantity) quantity_ordered
FROM
oe_order_lines_all
GROUP BY
inventory_item_id,
ordered_item
ORDER BY
SUM(ordered_quantity) DESC
)
WHERE
ROWNUM <= 20;
–10) Discrete jobs created per month
SELECT
TO_CHAR(creation_date,’Month-YYYY’) month_year,
COUNT(1) total_inv_cnt
FROM
wip_discrete_jobs
WHERE
creation_date BETWEEN ’01-JAN-22′ AND ’31-DEC-22′
GROUP BY
TO_CHAR(creation_date,’Month-YYYY’);
–11) Work orders created per month
SELECT
TO_CHAR(creation_date,’Month-YYYY’) month_year,
COUNT(1) total_inv_cnt
FROM
wip_entities
WHERE
creation_date BETWEEN ’01-JAN-22′ AND ’31-DEC-22′
GROUP BY
TO_CHAR(creation_date,’Month-YYYY’);
–12) Discrete jobs completed per month
SELECT
TO_CHAR(date_completed,’Month-YYYY’) month_year,
DECODE(status_type,4,’COMPLETE’,12,’CLOSED’) status,
COUNT(1) total_cnt
FROM
wip_discrete_jobs
WHERE
date_completed BETWEEN ’01-JAN-22′ AND ’31-DEC-22′
AND status_type IN (
4,
12
)
GROUP BY
TO_CHAR(date_completed,’Month-YYYY’),
status_type;
–13) Query for Installed Modules
SELECT
a.application_name,
a.product_code,
DECODE(b.status,’I’,’Installed’,’S’,’Shared’,’NA’) status,
patch_level
FROM
apps.fnd_application_vl a,
apps.fnd_product_installations b
WHERE
a.application_id = b.application_id
AND b.status in (‘I’,’S’)
ORDER BY
product_code ASC;
–14) Vendors and payment details
SELECT
vendor_name,
SUM(amount)
FROM
ap_checks_all
GROUP BY
vendor_name
ORDER BY
SUM(amount) DESC;
