WITH supplier_address_custom AS (
SELECT /*+ materialize */
*
FROM
(
SELECT
NULL old_value,
hl_.flag1 new_value,
hps.location_id,
hl_.field_name field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 7, 1) audit_bit_map,
audit_change_bit_map_,
site_use_type,
(
CASE
WHEN site_use_type = 'PURCHASING'
THEN 'Ordering'
WHEN site_use_type = 'PAY'
THEN 'Remit to'
ELSE NULL
END
) field_name,
(
CASE
WHEN status = 'A'
THEN 'Y'
ELSE 'N'
END
) flag1,
audit_action_type_,
last_updated_by,
party_site_id,
NULL row_num,
last_update_date
FROM
hz_party_site_uses_ hl_
WHERE
1 = 1
AND site_use_type IN ( 'PURCHASING', 'PAY' )
ORDER BY
last_update_date DESC
) hl_,
hz_party_sites hps
WHERE
1 = 1
AND hps.party_site_id = hl_.party_site_id
AND trunc(hl_.last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(hl_.last_update_date)) AND nvl(:p_last_update_to,
trunc(hl_.last_update_date))
UNION ALL
SELECT
(
SELECT
address1
FROM
(
SELECT
location_id,
address1,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_locations_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
hl_.address1 new_value,
hl_.location_id,
'Address Line 1' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 8, 1) audit_bit_map,
audit_change_bit_map_,
address1,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
hl_.address1 new_value,
hl_.location_id,
'Address Line 1' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 8, 1) audit_bit_map,
audit_change_bit_map_,
address1,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
AND address1 IS NOT NULL
UNION ALL
SELECT
(
SELECT
address2
FROM
(
SELECT
location_id,
address2,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_locations_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
hl_.address2 new_value,
hl_.location_id,
'Address Line 2' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 9, 1) audit_bit_map,
audit_change_bit_map_,
address2,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
hl_.address2 new_value,
hl_.location_id,
'Address Line 2' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 9, 1) audit_bit_map,
audit_change_bit_map_,
address2,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
AND address2 IS NOT NULL
UNION ALL
SELECT
(
SELECT
city
FROM
(
SELECT
location_id,
city,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_locations_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
hl_.city new_value,
hl_.location_id,
'City' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 12, 1) audit_bit_map,
audit_change_bit_map_,
city,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
hl_.city new_value,
hl_.location_id,
'City' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 12, 1) audit_bit_map,
audit_change_bit_map_,
city,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
AND city IS NOT NULL
UNION ALL
SELECT
(
SELECT
postal_code
FROM
(
SELECT
location_id,
postal_code,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_locations_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
hl_.postal_code new_value,
hl_.location_id,
'Postal Code' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 13, 1) audit_bit_map,
audit_change_bit_map_,
postal_code,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
hl_.postal_code new_value,
hl_.location_id,
'Postal Code' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 13, 1) audit_bit_map,
audit_change_bit_map_,
postal_code,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND postal_code IS NOT NULL
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
state
FROM
(
SELECT
location_id,
state,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_locations_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
hl_.state new_value,
hl_.location_id,
'State' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 14, 1) audit_bit_map,
audit_change_bit_map_,
state,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
hl_.state new_value,
hl_.location_id,
'State' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 14, 1) audit_bit_map,
audit_change_bit_map_,
state,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND state IS NOT NULL
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
to_char(end_date_active, 'DD/MM/YYYY')
FROM
(
SELECT
location_id,
end_date_active,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_party_sites_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
to_char(hl_.end_date_active, 'DD/MM/YYYY') new_value,
hl_.location_id,
'Inactive date' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 9, 1) audit_bit_map,
audit_change_bit_map_,
end_date_active,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_party_sites_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
(
CASE
WHEN to_char(hl_.end_date_active, 'DD/MM/YYYY') = '31/12/4712'
THEN NULL
ELSE to_char(hl_.end_date_active, 'DD/MM/YYYY')
END
) new_value,
hl_.location_id,
'Inactive date' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 8, 1) audit_bit_map,
audit_change_bit_map_,
end_date_active,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_party_sites_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND (
CASE
WHEN to_char(hl_.end_date_active, 'DD/MM/YYYY') = '31/12/4712'
THEN NULL
END
) IS NOT NULL
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
province
FROM
(
SELECT
location_id,
province,
last_update_date,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) AS row_number1
FROM
hz_locations_
)
WHERE
row_number1 = row_num - 1
AND location_id = hl_.location_id
) old_value,
hl_.province new_value,
hl_.location_id,
'Province' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 15, 1) audit_bit_map,
audit_change_bit_map_,
province,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
audit_bit_map = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
hl_.province new_value,
hl_.location_id,
'Province' field_name,
hl_.last_update_date,
hl_.audit_action_type_,
hl_.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 15, 1) audit_bit_map,
audit_change_bit_map_,
province,
audit_action_type_,
last_updated_by,
location_id,
ROW_NUMBER()
OVER(PARTITION BY location_id
ORDER BY
location_id, last_update_date
) row_num,
last_update_date
FROM
hz_locations_ hl_
WHERE
1 = 1
ORDER BY
last_update_date DESC
) hl_
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND province IS NOT NULL
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
ORDER BY
last_update_date DESC
)
), poz_suppliers_pii_custom AS (
SELECT /*+ materialize */
*
FROM
(
SELECT
(
SELECT
income_tax_id
FROM
(
SELECT
vendor_id,
last_update_date,
income_tax_id,
ROW_NUMBER()
OVER(PARTITION BY vendor_id
ORDER BY
vendor_id, last_update_date
) AS row_number1
FROM
poz_suppliers_pii_
)
WHERE
row_number1 = row_num - 1
AND vendor_id = ps.vendor_id
) old_value,
ps.income_tax_id new_value,
ps.vendor_id,
'Tax ID' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 1, 2) audit_change_bit_map_,
vendor_id,
income_tax_id,
audit_action_type_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_id
ORDER BY
vendor_id, last_update_date
) row_num,
last_update_date
FROM
poz_suppliers_pii_ ps_
ORDER BY
ps_.last_update_date DESC
) ps
WHERE
audit_change_bit_map_ = 01
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
income_tax_id
FROM
(
SELECT
vendor_id,
last_update_date,
income_tax_id,
ROW_NUMBER()
OVER(PARTITION BY vendor_id
ORDER BY
vendor_id, last_update_date
) AS row_number1
FROM
poz_suppliers_pii_
)
WHERE
row_number1 = row_num - 1
AND vendor_id = ps.vendor_id
) old_value,
ps.income_tax_id new_value,
ps.vendor_id,
'Tax ID' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 1, 2) audit_change_bit_map_,
vendor_id,
income_tax_id,
audit_action_type_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_id
ORDER BY
vendor_id, last_update_date
) row_num,
last_update_date
FROM
poz_suppliers_pii_ ps_
ORDER BY
ps_.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND income_tax_id IS NOT NULL
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
)
), audit_bank_account AS (
SELECT /*+ materialize */
*
FROM
(
SELECT
(
SELECT
bank_account_num
FROM
(
SELECT
ext_bank_account_id,
last_update_date,
bank_account_num,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) AS row_number1
FROM
iby_ext_bank_accounts_
)
WHERE
row_number1 = row_num - 1
AND ext_bank_account_id = ps.ext_bank_account_id
) old_value,
ps.bank_account_num new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
ext_bank_account_id,
row_num,
'Bank Account Number' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
psv.vendor_id,
supplier_site_id vendor_site_id,
hps.vendor_site_code,
ieba.ext_bank_account_id,
ieba.bank_account_num,
ieba.audit_action_type_,
row_num,
ieba.last_update_date,
ieba.audit_change_bit_map_,
ieba.last_updated_by
FROM
poz_suppliers psv,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
(
SELECT
ext_bank_account_id,
last_update_date,
bank_account_num,
audit_action_type_,
audit_change_bit_map_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) row_num
FROM
iby_ext_bank_accounts_
) ieba,
poz_supplier_sites_all_m hps
WHERE
1 = 1
AND psv.vendor_id = hps.vendor_id
AND hps.vendor_site_id = iepa.supplier_site_id
AND hps.party_site_id = iepa.party_site_id
AND iepa.payee_party_id = psv.party_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ieba.ext_bank_account_id = ipiua.instrument_id
ORDER BY
ieba.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
ps.bank_account_num new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
ext_bank_account_id,
row_num,
'Bank Account Number' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
psv.vendor_id,
supplier_site_id vendor_site_id,
hps.vendor_site_code,
ieba.ext_bank_account_id,
ieba.bank_account_num,
ieba.audit_action_type_,
row_num,
ieba.last_update_date,
ieba.audit_change_bit_map_,
ieba.last_updated_by
FROM
poz_suppliers psv,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
(
SELECT
ext_bank_account_id,
last_update_date,
bank_account_num,
audit_action_type_,
audit_change_bit_map_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) row_num
FROM
iby_ext_bank_accounts_
WHERE
bank_account_num IS NOT NULL
) ieba,
poz_supplier_sites_all_m hps
WHERE
1 = 1
AND psv.vendor_id = hps.vendor_id
AND hps.vendor_site_id = iepa.supplier_site_id
AND hps.party_site_id = iepa.party_site_id
AND iepa.payee_party_id = psv.party_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ieba.ext_bank_account_id = ipiua.instrument_id
ORDER BY
ieba.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
party_name
FROM
(
SELECT
ext_bank_account_id,
last_update_date,
branch_id,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) AS row_number1
FROM
iby_ext_bank_accounts_
),
hz_parties hp1
WHERE
row_number1 = row_num - 1
AND hp1.party_id = branch_id
AND ext_bank_account_id = ps.ext_bank_account_id
) old_value,
ps.party_name new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
ext_bank_account_id,
row_num,
'Routing Number' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
psv.vendor_id,
supplier_site_id vendor_site_id,
hps.vendor_site_code,
ieba.ext_bank_account_id,
hp.party_name,
ieba.audit_action_type_,
row_num,
ieba.last_update_date,
ieba.audit_change_bit_map_,
ieba.last_updated_by
FROM
poz_suppliers psv,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
hz_parties hp,
(
SELECT
ext_bank_account_id,
last_update_date,
branch_id,
audit_action_type_,
audit_change_bit_map_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) row_num
FROM
iby_ext_bank_accounts_
) ieba,
poz_supplier_sites_all_m hps
WHERE
1 = 1
AND psv.vendor_id = hps.vendor_id
AND hps.vendor_site_id = iepa.supplier_site_id
AND hps.party_site_id = iepa.party_site_id
AND iepa.payee_party_id = psv.party_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ieba.ext_bank_account_id = ipiua.instrument_id
AND hp.party_id = ieba.branch_id
ORDER BY
ieba.last_update_date DESC
) ps
WHERE
1 = 1
--AND AUDIT_CHANGE_BIT_MAP_ = '0'
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
ps.party_name new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
ext_bank_account_id,
row_num,
'Routing Number' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
psv.vendor_id,
supplier_site_id vendor_site_id,
hps.vendor_site_code,
ieba.ext_bank_account_id,
hp.party_name,
ieba.audit_action_type_,
row_num,
ieba.last_update_date,
ieba.audit_change_bit_map_,
ieba.last_updated_by
FROM
poz_suppliers psv,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
hz_parties hp,
(
SELECT
ext_bank_account_id,
last_update_date,
branch_id,
audit_action_type_,
audit_change_bit_map_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) row_num
FROM
iby_ext_bank_accounts_
WHERE
branch_id IS NOT NULL
) ieba,
poz_supplier_sites_all_m hps
WHERE
1 = 1
AND psv.vendor_id = hps.vendor_id
AND hps.vendor_site_id = iepa.supplier_site_id
AND hps.party_site_id = iepa.party_site_id
AND iepa.payee_party_id = psv.party_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ieba.ext_bank_account_id = ipiua.instrument_id
AND hp.party_id = ieba.branch_id
ORDER BY
ieba.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
to_char(end_date, 'DD/MM/YYYY') end_date
FROM
(
SELECT
test1.*,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) AS row_number1
FROM
(
SELECT
ext_bank_account_id,
last_update_date,
end_date
FROM
iby_ext_bank_accounts_
UNION ALL
SELECT
instrument_id ext_bank_account_id,
last_update_date,
end_date
FROM
iby_pmt_instr_uses_all_
) test1
)
WHERE
row_number1 = row_num - 1
AND ext_bank_account_id = ps.ext_bank_account_id
) old_value,
to_char(ps.end_date, 'DD/MM/YYYY') new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
ext_bank_account_id,
row_num,
'Assignment Inactive On' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
psv.vendor_id,
supplier_site_id vendor_site_id,
hps.vendor_site_code,
ieba_.ext_bank_account_id,
ieba_.end_date,
ieba_.audit_action_type_,
row_num,
ieba_.last_update_date,
ieba_.audit_change_bit_map_,
ieba_.last_updated_by
FROM
poz_suppliers psv,
iby_external_payees_all iepa,
(
SELECT
test.*,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) row_num
FROM
(
SELECT
ext_bank_account_id,
last_update_date,
end_date,
NULL ext_pmt_party_id,
audit_action_type_,
audit_change_bit_map_,
last_updated_by
FROM
iby_ext_bank_accounts_
WHERE
audit_action_type_ = 'INSERT'
UNION ALL
SELECT
instrument_id ext_bank_account_id,
last_update_date,
end_date,
ext_pmt_party_id,
audit_action_type_,
audit_change_bit_map_,
last_updated_by
FROM
iby_pmt_instr_uses_all_
WHERE
audit_change_bit_map_ = '0000000000001'
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to
,
trunc(last_update_date
))
) test
) ieba_,
poz_supplier_sites_all_m hps
WHERE
1 = 1
AND psv.vendor_id = hps.vendor_id
AND hps.vendor_site_id = iepa.supplier_site_id
AND hps.party_site_id = iepa.party_site_id
AND iepa.payee_party_id = psv.party_id
AND ieba_.ext_pmt_party_id = iepa.ext_payee_id
ORDER BY
ieba_.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
(
CASE
WHEN to_char(ps.end_date, 'DD/MM/YYYY') = '31/12/4712'
THEN NULL
ELSE to_char(ps.end_date, 'DD/MM/YYYY')
END
) new_value
,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
ext_bank_account_id,
row_num,
'Assignment Inactive On' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
psv.vendor_id,
supplier_site_id vendor_site_id,
hps.vendor_site_code,
ieba.ext_bank_account_id,
ieba.end_date,
ieba.audit_action_type_,
row_num,
ieba.last_update_date,
ieba.audit_change_bit_map_,
ieba.last_updated_by
FROM
poz_suppliers psv,
iby_external_payees_all iepa,
iby_pmt_instr_uses_all ipiua,
(
SELECT
ext_bank_account_id,
last_update_date,
end_date,
audit_action_type_,
audit_change_bit_map_,
last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY ext_bank_account_id
ORDER BY
ext_bank_account_id, last_update_date
) row_num
FROM
iby_ext_bank_accounts_
WHERE
end_date IS NOT NULL
) ieba,
poz_supplier_sites_all_m hps
WHERE
1 = 1
AND psv.vendor_id = hps.vendor_id
AND hps.vendor_site_id = iepa.supplier_site_id
AND hps.party_site_id = iepa.party_site_id
AND iepa.payee_party_id = psv.party_id
AND ipiua.ext_pmt_party_id = iepa.ext_payee_id
AND ieba.ext_bank_account_id = ipiua.instrument_id
ORDER BY
ieba.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND (
CASE
WHEN to_char(ps.end_date, 'DD/MM/YYYY') = '31/12/4712'
THEN NULL
END
) IS NOT NULL
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
)
),
poz_suppliers_sites_custom AS (
SELECT /*+ materialize */
*
FROM
(
SELECT
(
SELECT
pay_site_flag
FROM
(
SELECT
vendor_site_id,
last_update_date,
pay_site_flag,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, last_update_date
) AS row_number1
FROM
poz_supplier_sites_all_m_
)
WHERE
row_number1 = row_num - 1
AND vendor_site_id = ps.vendor_site_id
) old_value,
ps.pay_site_flag new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Pay' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 8, 1) audit_change_bit_map_,
vendor_id,
vendor_site_id,
vendor_site_code,
pay_site_flag,
audit_action_type_,
pssam_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, last_update_date
) row_num,
last_update_date
FROM
poz_supplier_sites_all_m_ pssam_
ORDER BY
pssam_.last_update_date DESC
) ps
WHERE
audit_change_bit_map_ = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
ps.pay_site_flag new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Pay' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 1, 2) audit_change_bit_map_,
vendor_id,
vendor_site_id,
vendor_site_code,
pay_site_flag,
audit_action_type_,
pssam_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, last_update_date
) row_num,
last_update_date
FROM
poz_supplier_sites_all_m_ pssam_
WHERE
pay_site_flag IS NOT NULL
ORDER BY
pssam_.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
primary_pay_site_flag
FROM
(
SELECT
vendor_site_id,
last_update_date,
primary_pay_site_flag,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, last_update_date
) AS row_number1
FROM
poz_supplier_sites_all_m_
)
WHERE
row_number1 = row_num - 1
AND vendor_site_id = ps.vendor_site_id
) old_value,
ps.primary_pay_site_flag new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Primary pay' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 17, 1) audit_change_bit_map_,
vendor_id,
vendor_site_id,
vendor_site_code,
primary_pay_site_flag,
audit_action_type_,
pssam_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, last_update_date
) row_num,
last_update_date
FROM
poz_supplier_sites_all_m_ pssam_
ORDER BY
pssam_.last_update_date DESC
) ps
WHERE
audit_change_bit_map_ = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
ps.primary_pay_site_flag new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Primary pay' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 1, 2) audit_change_bit_map_,
vendor_id,
vendor_site_id,
vendor_site_code,
primary_pay_site_flag,
audit_action_type_,
pssam_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, last_update_date
) row_num,
last_update_date
FROM
poz_supplier_sites_all_m_ pssam_
WHERE
primary_pay_site_flag IS NOT NULL
ORDER BY
pssam_.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
(
SELECT
name
FROM
(
SELECT
vendor_site_id,
pssam1.last_update_date,
apt1.name,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, pssam1.last_update_date
) AS row_number1
FROM
poz_supplier_sites_all_m_ pssam1,
ap_terms_tl apt1
WHERE
terms_id = apt1.term_id
AND apt1.language = userenv('LANG')
)
WHERE
row_number1 = row_num - 1
AND vendor_site_id = ps.vendor_site_id
) old_value,
ps.name new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Payment Terms' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 11, 1) audit_change_bit_map_,
vendor_id,
vendor_site_id,
vendor_site_code,
apt.name,
audit_action_type_,
pssam_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, pssam_.last_update_date
) row_num,
pssam_.last_update_date
FROM
poz_supplier_sites_all_m_ pssam_,
ap_terms_tl apt
WHERE
pssam_.terms_id = apt.term_id
AND apt.language = userenv('LANG')
ORDER BY
pssam_.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_change_bit_map_ = 1
AND audit_action_type_ = 'UPDATE'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
ps.name new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Payment Terms' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 10, 1) audit_change_bit_map_,
vendor_id,
vendor_site_id,
vendor_site_code,
apt.name,
audit_action_type_,
pssam_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY vendor_site_id
ORDER BY
vendor_site_id, pssam_.last_update_date
) row_num,
pssam_.last_update_date
FROM
poz_supplier_sites_all_m_ pssam_,
ap_terms_tl apt
WHERE
pssam_.terms_id = apt.term_id
AND apt.language = userenv('LANG')
ORDER BY
pssam_.last_update_date DESC
) ps
WHERE
1 = 1
AND audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
UNION ALL
SELECT
NULL old_value,
ps.payment_method_code new_value,
ps.vendor_id,
ps.vendor_site_id,
ps.vendor_site_code,
'Payment Method' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT
substr(audit_change_bit_map_, 1, 2) audit_change_bit_map_,
vendor_id,
vendor_site_id,
pssam.vendor_site_code,
ext_party_pmt_mthd_id,
ieppm_.payment_method_code,
ieppm_.audit_action_type_,
ieppm_.last_updated_by,
ROW_NUMBER()
OVER(PARTITION BY ext_party_pmt_mthd_id
ORDER BY
ext_party_pmt_mthd_id, ieppm_.last_update_date
) row_num,
ieppm_.last_update_date
FROM
poz_supplier_sites_all_m pssam,
iby_ext_party_pmt_mthds_ ieppm_,
iby_external_payees_all ibepa
WHERE
nvl(pssam.inactive_date, sysdate) <= sysdate
AND pssam.vendor_site_id = ibepa.supplier_site_id
AND nvl(ibepa.inactive_date, sysdate) <= sysdate
AND ibepa.ext_payee_id = ieppm_.ext_pmt_party_id
AND payment_method_code IS NOT NULL
ORDER BY
pssam.last_update_date DESC
) ps
WHERE
audit_action_type_ = 'INSERT'
AND trunc(last_update_date) BETWEEN nvl(:p_last_update_from,
trunc(last_update_date)) AND nvl(:p_last_update_to,
trunc(last_update_date))
)
),
poz_sup_site_contacts_custom AS (
SELECT /*+ materialize */ * FROM
(
SELECT (SELECT first_name
FROM ( SELECT party_site_id,
last_update_date,
attribute1 first_name,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) AS row_number1
FROM hz_party_sites_
)
WHERE row_number1 = row_num - 1
AND party_site_id = ps.party_site_id
) old_value,
ps.first_name new_value,
ps.party_site_id,
'First Name' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
( SELECT substr(audit_change_bit_map_, 10, 1) audit_change,
party_site_id,
attribute1 first_name,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) row_num,
last_update_date
FROM hz_party_sites_ ps_
ORDER BY ps_.last_update_date DESC
) ps
WHERE audit_change = '1'
AND audit_action_type_ = 'UPDATE'
AND TRUNC(ps.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(ps.last_update_date)) AND NVL(:p_last_update_to, TRUNC(ps.last_update_date))
UNION ALL
SELECT (SELECT last_name
FROM ( SELECT party_site_id,
last_update_date,
attribute2 last_name,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) AS row_number1
FROM hz_party_sites_
)
WHERE row_number1 = row_num - 1
AND party_site_id = ps.party_site_id
) old_value,
ps.last_name new_value,
ps.party_site_id,
'Last Name' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
( SELECT substr(audit_change_bit_map_, 11, 1) audit_change,
party_site_id,
attribute2 last_name,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) row_num,
last_update_date
FROM hz_party_sites_ ps_
ORDER BY ps_.last_update_date DESC
) ps
WHERE audit_change = '1'
AND audit_action_type_ = 'UPDATE'
AND TRUNC(ps.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(ps.last_update_date)) AND NVL(:p_last_update_to, TRUNC(ps.last_update_date))
UNION ALL
SELECT (SELECT supplier_email
FROM ( SELECT party_site_id,
last_update_date,
attribute3 supplier_email,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) AS row_number1
FROM hz_party_sites_
)
WHERE row_number1 = row_num - 1
AND party_site_id = ps.party_site_id
) old_value,
ps.supplier_email new_value,
ps.party_site_id,
'Primary Contact Email' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
( SELECT substr(audit_change_bit_map_, 12, 1) audit_change,
party_site_id,
attribute3 supplier_email,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) row_num,
last_update_date
FROM hz_party_sites_ ps_
ORDER BY ps_.last_update_date DESC
) ps
WHERE audit_change = '1'
AND audit_action_type_ = 'UPDATE'
AND TRUNC(ps.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(ps.last_update_date)) AND NVL(:p_last_update_to, TRUNC(ps.last_update_date))
UNION ALL
SELECT (SELECT phone_num
FROM ( SELECT owner_table_id,
contact_point_id,
last_update_date,
'+'||phone_country_code||' '||phone_area_code||' '||phone_number||' '||phone_extension phone_num,
ROW_NUMBER() OVER(PARTITION BY contact_point_id ORDER BY contact_point_id, last_update_date ) AS row_number1
FROM hz_contact_points_
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type = 'PHONE'
AND contact_point_id = cp.contact_point_id
)
WHERE row_number1 = row_num - 1
AND owner_table_id = cp.owner_table_id
) old_value,
cp.phone_num new_value,
cp.owner_table_id party_site_id,
'Phone Number' field_name,
cp.last_update_date,
cp.audit_action_type_,
cp.last_updated_by
FROM
( SELECT substr(audit_change_bit_map_, 13, 4) audit_change,
owner_table_id,
contact_point_id,
'+'||phone_country_code||' '||phone_area_code||' '||phone_number||' '||phone_extension phone_num,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY contact_point_id ORDER BY contact_point_id, last_update_date ) row_num,
last_update_date
FROM hz_contact_points_ cp_
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type = 'PHONE'
ORDER BY last_update_date DESC
) cp,
hz_party_sites hps
WHERE audit_change > '0000'
AND audit_action_type_ = 'UPDATE'
AND cp.owner_table_id = hps.party_site_id
AND TRUNC(cp.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(cp.last_update_date)) AND NVL(:p_last_update_to, TRUNC(cp.last_update_date))
UNION ALL
SELECT (SELECT email_address
FROM ( SELECT owner_table_id,
contact_point_id,
last_update_date,
email_address,
ROW_NUMBER() OVER(PARTITION BY contact_point_id ORDER BY contact_point_id, last_update_date ) AS row_number1
FROM hz_contact_points_
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type = 'EMAIL'
AND contact_point_id = cp.contact_point_id
)
WHERE row_number1 = row_num - 1
AND owner_table_id = cp.owner_table_id
) old_value,
cp.email_address new_value,
cp.owner_table_id party_site_id,
'PO Email' field_name,
cp.last_update_date,
cp.audit_action_type_,
cp.last_updated_by
FROM
( SELECT substr(audit_change_bit_map_, 13, 1) audit_change,
owner_table_id,
contact_point_id,
email_address,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY contact_point_id ORDER BY contact_point_id, last_update_date ) row_num,
last_update_date
FROM hz_contact_points_ cp_
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type = 'EMAIL'
ORDER BY last_update_date DESC
) cp,
hz_party_sites hps
WHERE audit_change = '1'
AND audit_action_type_ = 'UPDATE'
AND cp.owner_table_id = hps.party_site_id
AND TRUNC(cp.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(cp.last_update_date)) AND NVL(:p_last_update_to, TRUNC(cp.last_update_date))
UNION ALL
SELECT NULL old_value,
ps.first_name new_value,
ps.party_site_id,
'First Name' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT substr(audit_change_bit_map_, 10, 1) audit_change,
party_site_id,
attribute1 first_name,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) row_num,
last_update_date
FROM hz_party_sites_ ps_
ORDER BY ps_.last_update_date DESC
) ps
WHERE ps.first_name IS NOT NULL
AND ps.audit_action_type_ = 'INSERT'
AND TRUNC(ps.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(ps.last_update_date)) AND NVL(:p_last_update_to, TRUNC(ps.last_update_date))
UNION ALL
SELECT NULL old_value,
ps.last_name new_value,
ps.party_site_id,
'Last Name' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT substr(audit_change_bit_map_, 11, 1) audit_change,
party_site_id,
attribute2 last_name,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) row_num,
last_update_date
FROM hz_party_sites_ ps_
ORDER BY ps_.last_update_date DESC
) ps
WHERE ps.last_name IS NOT NULL
AND ps.audit_action_type_ = 'INSERT'
AND TRUNC(ps.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(ps.last_update_date)) AND NVL(:p_last_update_to, TRUNC(ps.last_update_date))
UNION ALL
SELECT NULL old_value,
ps.supplier_email new_value,
ps.party_site_id,
'Primary Contact Email' field_name,
ps.last_update_date,
ps.audit_action_type_,
ps.last_updated_by
FROM
(
SELECT substr(audit_change_bit_map_, 12, 1) audit_change,
party_site_id,
attribute3 supplier_email,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY party_site_id ORDER BY party_site_id, last_update_date ) row_num,
last_update_date
FROM hz_party_sites_ ps_
ORDER BY ps_.last_update_date DESC
) ps
WHERE ps.supplier_email IS NOT NULL
AND ps.audit_action_type_ = 'INSERT'
AND TRUNC(ps.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(ps.last_update_date)) AND NVL(:p_last_update_to, TRUNC(ps.last_update_date))
UNION ALL
SELECT NULL old_value,
'+'||cp.phone_country_code||' '||cp.phone_area_code||' '||cp.phone_number||' '||cp.phone_extension new_value,
cp.owner_table_id party_site_id,
'Phone Number' field_name,
cp.last_update_date,
cp.audit_action_type_,
cp.last_updated_by
FROM
(
SELECT substr(audit_change_bit_map_, 13, 4) audit_change,
owner_table_id,
contact_point_id,
phone_country_code,
phone_area_code,
phone_number,
phone_extension,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY contact_point_id ORDER BY contact_point_id, last_update_date ) row_num,
last_update_date
FROM hz_contact_points_ cp_
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type = 'PHONE'
ORDER BY last_update_date DESC
) cp,
hz_party_sites hps
WHERE (cp.phone_country_code IS NOT NULL OR
cp.phone_area_code IS NOT NULL OR
cp.phone_number IS NOT NULL OR
cp.phone_extension IS NOT NULL
)
AND cp.audit_action_type_ = 'INSERT'
AND cp.owner_table_id = hps.party_site_id
AND TRUNC(cp.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(cp.last_update_date)) AND NVL(:p_last_update_to, TRUNC(cp.last_update_date))
UNION ALL
SELECT NULL old_value,
cp.email_address new_value,
cp.owner_table_id party_site_id,
'PO Email' field_name,
cp.last_update_date,
cp.audit_action_type_,
cp.last_updated_by
FROM
(
SELECT substr(audit_change_bit_map_, 13, 1) audit_change,
owner_table_id,
contact_point_id,
email_address,
audit_action_type_,
last_updated_by,
ROW_NUMBER() OVER(PARTITION BY contact_point_id ORDER BY contact_point_id, last_update_date ) row_num,
last_update_date
FROM hz_contact_points_ cp_
WHERE owner_table_name = 'HZ_PARTY_SITES'
AND contact_point_type = 'EMAIL'
ORDER BY last_update_date DESC
) cp,
hz_party_sites hps
WHERE cp.email_address IS NOT NULL
AND cp.audit_action_type_ = 'INSERT'
AND cp.owner_table_id = hps.party_site_id
AND TRUNC(cp.last_update_date) BETWEEN NVL(:p_last_update_from, TRUNC(cp.last_update_date)) AND NVL(:p_last_update_to, TRUNC(cp.last_update_date))
)
) -- Added as per EAP-2494
SELECT
psv.vendor_name,
psv.vendor_id,
psv.segment1,
old_value,
new_value,
pssc.last_update_date,
pssc.audit_action_type_,
pssc.field_name,
pssc.last_updated_by,
psav.party_site_name entity
FROM
poz_suppliers_v psv,
supplier_address_custom pssc,
hz_party_sites psav
WHERE
1 = 1
AND psv.party_id = psav.party_id
AND psav.location_id = pssc.location_id
AND psv.segment1 IN nvl((:p_supplier_number), psv.segment1)
AND trunc(pssc.last_update_date) BETWEEN nvl(:p_last_update_from, trunc(pssc.last_update_date)) AND nvl(:p_last_update_to, trunc(pssc.last_update_date))
UNION ALL
SELECT
psv.vendor_name,
psv.vendor_id,
psv.segment1,
old_value,
new_value,
pssc.last_update_date,
pssc.audit_action_type_,
pssc.field_name,
pssc.last_updated_by,
pssc.vendor_site_code entity
FROM
poz_suppliers_v psv,
audit_bank_account pssc
WHERE
1 = 1
AND psv.vendor_id = pssc.vendor_id
AND psv.segment1 IN nvl((:p_supplier_number), psv.segment1)
AND trunc(pssc.last_update_date) BETWEEN nvl(:p_last_update_from, trunc(pssc.last_update_date)) AND nvl(:p_last_update_to, trunc(pssc.last_update_date))
UNION ALL
SELECT
psv.vendor_name,
psv.vendor_id,
psv.segment1,
old_value,
new_value,
pssc.last_update_date,
pssc.audit_action_type_,
pssc.field_name,
pssc.last_updated_by,
pssc.vendor_site_code entity
FROM
poz_suppliers_v psv,
poz_suppliers_sites_custom pssc
WHERE
1 = 1
AND psv.vendor_id = pssc.vendor_id
AND psv.segment1 IN nvl((:p_supplier_number), psv.segment1)
AND trunc(pssc.last_update_date) BETWEEN nvl(:p_last_update_from, trunc(pssc.last_update_date)) AND nvl(:p_last_update_to, trunc(pssc.last_update_date))
UNION ALL
SELECT
psv.vendor_name vendor_name,
psv.vendor_id,
psv.segment1,
old_value,
new_value,
pssc.last_update_date,
pssc.audit_action_type_,
pssc.field_name,
pssc.last_updated_by,
psv.vendor_name entity
FROM
poz_suppliers_v psv,
poz_suppliers_pii_custom pssc
WHERE
1 = 1
AND psv.vendor_id = pssc.vendor_id
AND psv.segment1 IN nvl((:p_supplier_number), psv.segment1)
AND trunc(pssc.last_update_date) BETWEEN nvl(:p_last_update_from, trunc(pssc.last_update_date)) AND nvl(:p_last_update_to, trunc(pssc.last_update_date))
UNION ALL
SELECT
psv.vendor_name,
psv.vendor_id,
psv.segment1,
old_value,
new_value,
pssc.last_update_date,
pssc.audit_action_type_,
pssc.field_name,
pssc.last_updated_by,
psav.party_site_name entity
FROM
poz_suppliers_v psv,
poz_sup_site_contacts_custom pssc,
hz_party_sites psav
WHERE
1 = 1
AND psv.party_id = psav.party_id
AND psav.party_site_id = pssc.party_site_id
AND psv.segment1 IN nvl((:p_supplier_number), psv.segment1)
AND trunc(pssc.last_update_date) BETWEEN nvl(:p_last_update_from, trunc(pssc.last_update_date)) AND nvl(:p_last_update_to, trunc(pssc.last_update_date))
ORDER BY
last_update_date DESC,
segment1 ASC
