VIEW: APPS.JTF_FM_CONTENT_HISTORY_V

Source

View Text - Preformatted

SELECT LAST_UPDATE_DATE, BATCH_NUMBER, PARTY_ID, QUANTITY, DOCUMENT_TYPE, LAST_UPDATED_BY, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, MES_DOC_ID, CONTENT_NUMBER, CONTENT_TYPE, CONTENT_NAME, DESTINATION_ADDR, SUBMIT_DT_TM, OUTPUT_ID, USER_NOTES, OUTCOME_CODE, OUTCOME_DESC, HIST_REQ_ID, F_DELETEDFLAG, OBJECT_VERSION_NUMBER, PARTY_NAME, to_number(CONTENT_NUMBER || HIST_REQ_ID || to_char(SUBMIT_DT_TM,'YYYYMMDDHH24MISS') || BATCH_NUMBER) PK_ID from jtf_fm_content_history UNION ALL SELECT pro.LAST_UPDATE_DATE LAST_UPDATE_DATE, pro.JOB BATCH_NUMBER, pro.PARTY_ID PARTY_ID, con.QUANTITY QUANTITY, con.DOCUMENT_TYPE DOCUMNET_TYPE , pro.LAST_UPDATED_BY LAST_UPDATED_BY, pro.CREATION_DATE CREATION_DATE, pro.CREATED_BY CREATED_BY, pro.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, con.CONTENT_ID MES_DOC_ID, con.CONTENT_NUMBER CONTENT_NUMBER, con.CONTENT_TYPE CONTENT_TYPE, con.CONTENT_NAME CONTENT_NAME, DECODE (his.MEDIA_TYPE,'EMAIL',pro.EMAIL_ADDRESS,'FAX',pro.FAX_ADDRESS,'PRINTER',pro.PRINT_ADDRESS,'UNKNOWN') DESTINATION_ADDR, his.SUBMIT_DT_TM SUBMIT_DT_TM, 0 OUTPUT_ID, con.USER_NOTES USER_NOTES, pro.OUTCOME_CODE OUTCOME_CODE, NULL OUTCOME_DESC, pro.REQUEST_ID HIST_REQ_ID, NULL F_DELETEDFLAG , 0 OBJECT_VERSION_NUMBER, pro.PARTY_NAME PARTY_NAME, 0 PK_ID from jtf_fm_processed pro, jtf_fm_request_contents con, jtf_Fm_Request_history his where pro.REQUEST_ID =his.HIST_REQ_ID AND his.HIST_REQ_ID=con.REQUEST_ID

View Text - HTML Formatted

SELECT LAST_UPDATE_DATE
, BATCH_NUMBER
, PARTY_ID
, QUANTITY
, DOCUMENT_TYPE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, MES_DOC_ID
, CONTENT_NUMBER
, CONTENT_TYPE
, CONTENT_NAME
, DESTINATION_ADDR
, SUBMIT_DT_TM
, OUTPUT_ID
, USER_NOTES
, OUTCOME_CODE
, OUTCOME_DESC
, HIST_REQ_ID
, F_DELETEDFLAG
, OBJECT_VERSION_NUMBER
, PARTY_NAME
, TO_NUMBER(CONTENT_NUMBER || HIST_REQ_ID || TO_CHAR(SUBMIT_DT_TM
, 'YYYYMMDDHH24MISS') || BATCH_NUMBER) PK_ID
FROM JTF_FM_CONTENT_HISTORY UNION ALL SELECT PRO.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PRO.JOB BATCH_NUMBER
, PRO.PARTY_ID PARTY_ID
, CON.QUANTITY QUANTITY
, CON.DOCUMENT_TYPE DOCUMNET_TYPE
, PRO.LAST_UPDATED_BY LAST_UPDATED_BY
, PRO.CREATION_DATE CREATION_DATE
, PRO.CREATED_BY CREATED_BY
, PRO.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, CON.CONTENT_ID MES_DOC_ID
, CON.CONTENT_NUMBER CONTENT_NUMBER
, CON.CONTENT_TYPE CONTENT_TYPE
, CON.CONTENT_NAME CONTENT_NAME
, DECODE (HIS.MEDIA_TYPE
, 'EMAIL'
, PRO.EMAIL_ADDRESS
, 'FAX'
, PRO.FAX_ADDRESS
, 'PRINTER'
, PRO.PRINT_ADDRESS
, 'UNKNOWN') DESTINATION_ADDR
, HIS.SUBMIT_DT_TM SUBMIT_DT_TM
, 0 OUTPUT_ID
, CON.USER_NOTES USER_NOTES
, PRO.OUTCOME_CODE OUTCOME_CODE
, NULL OUTCOME_DESC
, PRO.REQUEST_ID HIST_REQ_ID
, NULL F_DELETEDFLAG
, 0 OBJECT_VERSION_NUMBER
, PRO.PARTY_NAME PARTY_NAME
, 0 PK_ID
FROM JTF_FM_PROCESSED PRO
, JTF_FM_REQUEST_CONTENTS CON
, JTF_FM_REQUEST_HISTORY HIS
WHERE PRO.REQUEST_ID =HIS.HIST_REQ_ID
AND HIS.HIST_REQ_ID=CON.REQUEST_ID