Hi Oracle Gurus,
Appreciate if anyone can help on this. I want to run this report by time zone.
For example, the report only fetch update made during Guam Timezone. I got an error "Not a valid month" when I changed the format of all Last_update_date to below.
to_char(CAST((FROM_TZ(CAST(FA.LAST_UPDATE_DATE AS TIMESTAMP),'+00:00') AT TIME ZONE 'PACIFIC/GUAM') AS DATE), 'MM/DD/YYYY HH24:MI') LAST_UPDATE_DATE.
I've tried also inserting alter session set nls_date_format = 'DD-MON-YYYY' but didn't work due to error invalid table name
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WITH FA1 AS (
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Tag number' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(TAG_NUMBER) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
TAG_NUMBER NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE TAG_NUMBER IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Serial number' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(SERIAL_NUMBER) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
SERIAL_NUMBER NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE SERIAL_NUMBER IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Model number' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(MODEL_NUMBER) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
MODEL_NUMBER NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE MODEL_NUMBER IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Reference number' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(ATTRIBUTE1) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
ATTRIBUTE1 NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE ATTRIBUTE1 IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Legacy Asset (ID)' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(ATTRIBUTE2) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
ATTRIBUTE2 NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE ATTRIBUTE2 IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Additional Asset Description' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(ATTRIBUTE3) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
ATTRIBUTE3 NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE ATTRIBUTE3 IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Supplier Name from (PROLOGUE)' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(ATTRIBUTE4) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
ATTRIBUTE4 NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE ATTRIBUTE4 IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Comments' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(ATTRIBUTE5) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
ATTRIBUTE5 NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE ATTRIBUTE5 IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'In Use' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(IN_USE_FLAG) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
IN_USE_FLAG NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE IN_USE_FLAG IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Ownership' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(OWNED_LEASED) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
OWNED_LEASED NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE OWNED_LEASED IS NOT NULL) FA
UNION ALL
SELECT DISTINCT
FA.ASSET_NUMBER,
NVL(FA.OLD_VALUE, '-') OLD_VALUE,
NVL(FA.NEW_VALUE,'-') NEW_VALUE,
FA.LAST_UPDATE_DATE,
FA.LAST_UPDATED_BY,
CASE WHEN FA.NEW_VALUE IS NOT NULL THEN 'Bought' END FIELD_NAME
FROM (
SELECT ASSET_NUMBER,
DENSE_RANK() OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE DESC) RN,
LAG(NEW_USED) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) OLD_VALUE,
NEW_USED NEW_VALUE,
LAG(LAST_UPDATE_DATE) OVER(PARTITION BY ASSET_ID ORDER BY LAST_UPDATE_DATE) START_DATE,
LAST_UPDATE_DATE,LAST_UPDATED_BY
FROM FA_ADDITIONS_B_ WHERE OWNED_LEASED IS NOT NULL) FA)
SELECT
FA1.ASSET_NUMBER,
FA1.OLD_VALUE,
FA1.NEW_VALUE,
TO_CHAR(FA1.LAST_UPDATE_DATE, 'MM/DD/YYYY HH24:MI') LAST_UPDATE_DATE,
FA1.LAST_UPDATED_BY,
FA1.FIELD_NAME
FROM FA1
WHERE
FA1.ASSET_NUMBER >= NVL(:FROM_ASSET_NUMBER,ASSET_NUMBER) AND
FA1.ASSET_NUMBER <= NVL(:TO_ASSET_NUMBER,ASSET_NUMBER)
AND FA1.LAST_UPDATE_DATE >= :FROM_TRANSACTION_DATE AND FA1.LAST_UPDATE_DATE <= :TO_TRANSACTION_DATE
AND FA1.NEW_VALUE != ' '
AND FA1.NEW_VALUE || FA1.LAST_UPDATE_DATE != FA1.OLD_VALUE ||FA1.LAST_UPDATE_DATE
--AND FA1.ASSET_NUMBER = '3010'
ORDER BY FA1.ASSET_NUMBER, FA1.LAST_UPDATE_DATE