SELECT Qry_rslt.”Supplier Name”
,Qry_rslt.”Supplier Number”
,Qry_rslt.”Supplier Site Name”
,Qry_rslt.”Supplier Status”
,Qry_rslt.”Inactive Date”
,Qry_rslt.”Procurement Business Unit”
,Qry_rslt.”Currency”
,Qry_rslt.”Total Amount”
,Qry_rslt.”No Data Found”
,Qry_rslt.key
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN DECODE(:p_bu_name,NULL,’ALL’,list_bu,list_bu)
ELSE NULL
ENDAS list_bu
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN :P_SUPPLIER_NUM_FROM
ELSE NULL
ENDAS V_SUPPLIER_NUM_FROM
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN :P_SUPPLIER_NUM_TO
ELSE NULL
ENDAS V_SUPPLIER_NUM_TO
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN :P_SUPPLIER_NAME_FROM
ELSE NULL
ENDAS V_SUPPLIER_NAME_FROM
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN :P_SUPPLIER_NAME_TO
ELSE NULL
ENDAS V_SUPPLIER_NAME_TO
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN TO_CHAR(:P_FROM_DATE, ‘MON/DD/YYYY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)
ELSE NULL
ENDAS V_FROM_DATE
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN TO_CHAR(:P_TO_DATE, ‘MON/DD/YYYY’, ‘NLS_DATE_LANGUAGE=ENGLISH’)
ELSE NULL
ENDAS V_TO_DATE
,CASE
WHEN ROW_NUMBER() OVER (PARTITION BY Qry_rslt.key ORDER BY Qry_rslt.key) = 1
THEN TO_CHAR(SYSDATE,’DD-Mon-YYYY HH24:MI:SS’,’NLS_DATE_LANGUAGE=ENGLISH’)
ELSE NULL
ENDAS EXECUTION_DATE,
V_BU_NAME
FROM (SELECT
poz_sv.vendor_name AS “Supplier Name”,
poz_sv.segment1 AS “Supplier Number”,
poz_ssv.vendor_site_code AS “Supplier Site Name”,
DECODE(poz_sv.enabled_flag,’Y’,’Active’,’N’,’Inactive’) AS “Supplier Status”,
TO_CHAR(poz_sv.end_date_active,’DD-Mon-YYYY’,’NLS_DATE_LANGUAGE=ENGLISH’) AS “Inactive Date”,
fnbuv.bu_name AS “Procurement Business Unit”,
aia.invoice_currency_code AS “Currency”,
nvl(SUM(aia.invoice_amount),0) AS “Total Amount”,
NULL AS “No Data Found”,
‘ALL’ AS key,
(
SELECT
LISTAGG(bu_name,’, ‘) WITHIN GROUP(
ORDER BY
bu_name
)
FROM
fun_names_business_units_v fnbuv1
WHERE
1 = 1
AND (
fnbuv1.bu_name IN (
:p_bu_name
)
OR ( (
SELECT
LISTAGG(bu_name,’, ‘) WITHIN GROUP(
ORDER BY
bu_name
) csv
FROM
fun_names_business_units_v
WHERE 1=1
and bu_name IN (
:p_bu_name
)
) IS NULL )
)
) list_bu,
:P_BU_NAME V_BU_NAME
FROM
poz_suppliers_v poz_sv,
poz_supplier_sites_v poz_ssv,
ap_invoices_all aia,
zx_party_tax_profile zptp,
fun_names_business_units_v fnbuv
WHERE
1 = 1
AND poz_sv.vendor_id = poz_ssv.vendor_id
AND poz_sv.vendor_id = aia.vendor_id (+)
AND poz_sv.party_id = zptp.party_id
AND zptp.rep_registration_number IS NULL
AND poz_ssv.prc_bu_id = fnbuv.bu_id
AND aia.org_id(+)=fnbuv.bu_id
— Filter by Business Unit parameter (multi-select support)
AND (
fnbuv.bu_name IN (
:p_bu_name
)
OR ( (
SELECT
LISTAGG(bu_name,’, ‘) WITHIN GROUP(
ORDER BY
bu_name
) csv
FROM
fun_names_business_units_v
WHERE
bu_name IN (
:p_bu_name
)
) IS NULL )
)
— Supplier Number range
AND (
:p_supplier_num_from IS NULL
OR poz_sv.segment1 >=:p_supplier_num_from
)
AND (
:p_supplier_num_to IS NULL
OR poz_sv.segment1 <=:p_supplier_num_to
)
— Supplier Name range
AND (
:p_supplier_name_from IS NULL
OR upper(poz_sv.vendor_name) >= upper(:p_supplier_name_from)
)
AND (
:p_supplier_name_to IS NULL
OR upper(poz_sv.vendor_name) <= upper(:p_supplier_name_to)
)
— Invoice Date range
AND (
:p_from_date IS NULL
OR poz_sv.creation_date >=:p_from_date
)
AND (
:p_to_date IS NULL
OR poz_sv.creation_date <=:p_to_date
)
GROUP BY
poz_sv.vendor_name,
poz_sv.segment1,
poz_ssv.vendor_site_code,
poz_sv.enabled_flag,
poz_sv.end_date_active,
fnbuv.bu_name,
aia.invoice_currency_code
UNION ALL
— Show “No Data Found” if above query returns no rows
SELECT
NULL AS “Supplier Name”,
NULL AS “Supplier Number”,
NULL AS “Supplier Site Name”,
NULL AS “Supplier Status”,
NULL AS “Inactive Date”,
NULL AS “Procurement Business Unit”,
NULL AS “Currency”,
NULL AS “Total Amount”,
‘No Data Found’ AS “No Data Found”,
‘ALL’ AS key,
(
SELECT
LISTAGG(bu_name,’, ‘) WITHIN GROUP(
ORDER BY
bu_name
)
FROM
fun_names_business_units_v fnbuv1
WHERE
1 = 1
AND (
fnbuv1.bu_name IN (
:p_bu_name
)
OR ( (
SELECT
LISTAGG(bu_name,’, ‘) WITHIN GROUP(
ORDER BY
bu_name
) csv
FROM
fun_names_business_units_v
WHERE
bu_name IN (
:p_bu_name
)
) IS NULL )
)
) list_bu,
:P_BU_NAME V_BU_NAME
FROM
dual
WHERE
NOT EXISTS (
SELECT
1
FROM
poz_suppliers_v poz_sv,
poz_supplier_sites_v poz_ssv,
ap_invoices_all aia,
zx_party_tax_profile zptp,
fun_names_business_units_v fnbuv
WHERE
1 = 1
AND poz_sv.vendor_id = poz_ssv.vendor_id
AND poz_sv.vendor_id = aia.vendor_id (+)
AND poz_sv.party_id = zptp.party_id
AND zptp.rep_registration_number IS NULL
AND poz_ssv.prc_bu_id = fnbuv.bu_id
AND aia.org_id(+)=fnbuv.bu_id
AND (
fnbuv.bu_name IN (
:p_bu_name
)
OR ( (
SELECT
LISTAGG(bu_name,’, ‘) WITHIN GROUP(
ORDER BY
bu_name
) csv
FROM
fun_names_business_units_v
WHERE
bu_name IN (
:p_bu_name
)
) IS NULL )
)
AND (
:p_supplier_num_from IS NULL
OR poz_sv.segment1 >=:p_supplier_num_from
)
AND (
:p_supplier_num_to IS NULL
OR poz_sv.segment1 <=:p_supplier_num_to
)
AND (
:p_supplier_name_from IS NULL
OR upper(poz_sv.vendor_name) >= upper(:p_supplier_name_from)
)
AND (
:p_supplier_name_to IS NULL
OR upper(poz_sv.vendor_name) <= upper(:p_supplier_name_to)
)
AND (
:p_from_date IS NULL
OR poz_sv.creation_date >=:p_from_date
)
AND (
:p_to_date IS NULL
OR poz_sv.creation_date <=:p_to_date
)
)) Qry_rslt
Recent Posts