Good day
As per below attached 2 images, I am trying perform CDC on one driving table "STG_AL_INV_TRANS" which has one join and multiple lookups (dimensions) to load a fact table.
I have started the simple journalizing on this table and ticked "Journalizing Data Only". I am using "IKM Incremental Update" as the intergration Module with FlOW_CONTROL = False
and ANALYZE_TARGET = True.
When I run the mapping I get below error. Due to the joins and multiple lookups , code generated by ODI is quiet long and complex for me to debug.( Find at the end)
(Mapping a source directly to a target with the above mentioned setting works fine)
How can I resolve this error?
ODI-1228: Task Insert flow into I$ table-IKM Oracle Incremental Update- fails on the target connection ODS_APP.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "JRN_FLAG": invalid identifier
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:466)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:407)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1113)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:546)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:603)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:218)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:31)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:1000)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1316)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:2168)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:2100)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:363)
at oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:141)
at oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28)
at oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
at oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:189)
at oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:111)
at oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:864)
at oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2024)
at oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:562)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
at oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
at oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
at oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
at java.lang.Thread.run(Thread.java:745)
* DETECTION_STRATEGY = NOT_EXISTS */
insert into APP.I$_ODS_F_WMS_ALSER_STK_MOVE
(
ORG_UNIT,
COMPANY_ID,
TRANS_ID,
MOVE_ID,
TRANS_DATE_KEY,
TRANS_TIME_KEY,
PRODUCT_KEY,
WHSE_LOC_KEY,
WHSE_AREA_KEY,
TRANSTYPE_KEY,
USERS_KEY,
BATCH_KEY,
COMMENTS,
SHIFT_NO,
AREA_TYPE,
BATCH_DATE,
BATCH_NUMBER,
PRODUCT_INSTANCE_ID,
PUOM,
SUOM,
REF_DOC_ID,
COMPLETED_IND,
REASON_CODE,
PQTY,
SQTY,
PRODUCTION_PQTY,
PRODUCTION_SQTY,
PRODUCTION3RDPARTY_PQTY,
PRODUCTION3RDPARTY_SQTY,
SALES_PQTY,
SALES_SQTY,
SALES_RETURN_PQTY,
SALES_RETURN_SQTY,
RECLASS_PQTY,
RECLASS_SQTY,
REWORK_PQTY,
REWORK_SQTY,
TRANSFER_IN_PQTY,
TRANSFER_IN_SQTY,
TRANSFER_OUT_PQTY,
TRANSFER_OUT_SQTY,
STOCK_ADJUSTMENTS_PQTY,
STOCK_ADJUSTMENTS_SQTY,
STOCK_COUNT_PQTY,
STOCK_COUNT_SQTY,
STOCK_ON_HAND_PQTY,
STOCK_ON_HAND_SQTY,
HOSPITAL_STOCK_PQTY,
HOSPITAL_STOCK_SQTY,
INTERNAL_MOV_PQTY,
INTERNAL_MOV_SQTY,
STK_LESS_HOSP_PQTY,
STK_LESS_HOSP_SQTY,
CREATED_BY,
CREATED_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_DATE,
IND_UPDATE
)
select
ORG_UNIT,
COMPANY_ID,
TRANS_ID,
MOVE_ID,
TRANS_DATE_KEY,
TRANS_TIME_KEY,
PRODUCT_KEY,
WHSE_LOC_KEY,
WHSE_AREA_KEY,
TRANSTYPE_KEY,
USERS_KEY,
BATCH_KEY,
COMMENTS,
SHIFT_NO,
AREA_TYPE,
BATCH_DATE,
BATCH_NUMBER,
PRODUCT_INSTANCE_ID,
PUOM,
SUOM,
REF_DOC_ID,
COMPLETED_IND,
REASON_CODE,
PQTY,
SQTY,
PRODUCTION_PQTY,
PRODUCTION_SQTY,
PRODUCTION3RDPARTY_PQTY,
PRODUCTION3RDPARTY_SQTY,
SALES_PQTY,
SALES_SQTY,
SALES_RETURN_PQTY,
SALES_RETURN_SQTY,
RECLASS_PQTY,
RECLASS_SQTY,
REWORK_PQTY,
REWORK_SQTY,
TRANSFER_IN_PQTY,
TRANSFER_IN_SQTY,
TRANSFER_OUT_PQTY,
TRANSFER_OUT_SQTY,
STOCK_ADJUSTMENTS_PQTY,
STOCK_ADJUSTMENTS_SQTY,
STOCK_COUNT_PQTY,
STOCK_COUNT_SQTY,
STOCK_ON_HAND_PQTY,
STOCK_ON_HAND_SQTY,
HOSPITAL_STOCK_PQTY,
HOSPITAL_STOCK_SQTY,
INTERNAL_MOV_PQTY,
INTERNAL_MOV_SQTY,
STK_LESS_HOSP_PQTY,
STK_LESS_HOSP_SQTY,
CREATED_BY,
CREATED_DATE,
LAST_UPDATED_BY,
LAST_UPDATED_DATE,
IND_UPDATE
from (
select
ORG_UNIT.ORG_UNIT ORG_UNIT,
INLINE_VIEW_5.COMPANY_ID_1 COMPANY_ID,
INLINE_VIEW_5.TRANS_ID TRANS_ID,
INLINE_VIEW_5.MOVE_ID MOVE_ID,
to_number(to_char(INLINE_VIEW_5.TRANS_DATE,'YYYYMMDD')) TRANS_DATE_KEY,
to_number(to_char(INLINE_VIEW_5.TRANS_DATE , 'HH24MISS')) TRANS_TIME_KEY,
INLINE_VIEW_5.PRODUCT_KEY PRODUCT_KEY,
INLINE_VIEW_5.WHSE_LOC_KEY WHSE_LOC_KEY,
INLINE_VIEW_5.WHSE_AREA_KEY WHSE_AREA_KEY,
INLINE_VIEW_5.TRANSTYPE_KEY TRANSTYPE_KEY,
INLINE_VIEW_5.USERS_KEY USERS_KEY,
INLINE_VIEW_5.BATCH_KEY BATCH_KEY,
INLINE_VIEW_5.COMMENTS COMMENTS,
INLINE_VIEW_5.SHIFT_NO SHIFT_NO,
INLINE_VIEW_5.AREA_TYPE AREA_TYPE,
INLINE_VIEW_5.BATCH_DATE BATCH_DATE,
INLINE_VIEW_5.BATCH_NUMBER BATCH_NUMBER,
INLINE_VIEW_5.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID,
INLINE_VIEW_5.PUOM PUOM,
INLINE_VIEW_5.SUOM SUOM,
INLINE_VIEW_5.REF_DOC_ID REF_DOC_ID,
INLINE_VIEW_5.COMPLETED_IND COMPLETED_IND,
INLINE_VIEW_5.REASON_CODE REASON_CODE,
INLINE_VIEW_5.PQTY PQTY,
INLINE_VIEW_5.SQTY SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Production'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END PRODUCTION_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Production'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END PRODUCTION_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Production 3rd Party'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END PRODUCTION3RDPARTY_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Production 3rd Party'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END PRODUCTION3RDPARTY_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Sales'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END SALES_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Sales'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END SALES_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Sales Returns'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END SALES_RETURN_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Sales Returns'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END SALES_RETURN_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Reclass'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END RECLASS_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Reclass'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END RECLASS_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Rework'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END REWORK_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Rework'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END REWORK_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Transfer In'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END TRANSFER_IN_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Transfer In'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END TRANSFER_IN_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Transfer Out'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END TRANSFER_OUT_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Transfer Out'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END TRANSFER_OUT_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Stock Adjustments'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END STOCK_ADJUSTMENTS_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Stock Adjustments'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END STOCK_ADJUSTMENTS_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Stock Count Adjustments'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END STOCK_COUNT_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Stock Count Adjustments'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END STOCK_COUNT_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP <> 'Exclude From SOH'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END STOCK_ON_HAND_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP <> 'Exclude From SOH'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END STOCK_ON_HAND_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP <> 'Exclude From SOH'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION = '6/H1/H1/H1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END HOSPITAL_STOCK_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP <> 'Exclude From SOH'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION = '6/H1/H1/H1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END HOSPITAL_STOCK_SQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Internal Movements'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END INTERNAL_MOV_PQTY,
CASE WHEN INLINE_VIEW_5.CONS_REP_MAP = 'Internal Movements'
AND INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1' THEN INLINE\_VIEW\_5.SQTY
ELSE 0 END INTERNAL_MOV_SQTY,
CASE
WHEN
INLINE\_VIEW\_5.CONS\_REP\_MAP \<> 'Exclude From SOH'
AND
INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION \<> 'K1/K1/K1/K1'
THEN INLINE\_VIEW\_5.PQTY - (
CASE WHEN INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION = '6/H1/H1/H1'
THEN INLINE\_VIEW\_5.PQTY
ELSE 0 END)
ELSE 0 END STK_LESS_HOSP_PQTY,
CASE
WHEN INLINE_VIEW_5.CONS_REP_MAP <> 'Exclude From SOH'
AND INLINE_VIEW_5.WHSE_STRG_LOCATION <> 'K1/K1/K1/K1'
THEN INLINE\_VIEW\_5.SQTY -
(CASE WHEN INLINE\_VIEW\_5.WHSE\_STRG\_LOCATION = '6/H1/H1/H1'
THEN INLINE\_VIEW\_5.SQTY ELSE 0 END)
ELSE 0 END STK_LESS_HOSP_SQTY,
INLINE_VIEW_5.CREATED_BY_1 CREATED_BY,
INLINE_VIEW_5.CREATED_DATE CREATED_DATE,
INLINE_VIEW_5.LAST_UPDATED_BY LAST_UPDATED_BY,
INLINE_VIEW_5.LAST_UPDATED_DATE LAST_UPDATED_DATE,
JRN_FLAG IND_UPDATE
from (
SELECT
INLINE_VIEW_4.CREATED_DATE CREATED_DATE ,
INLINE_VIEW_4.LAST_UPDATED_BY LAST_UPDATED_BY ,
INLINE_VIEW_4.CONS_REP_MAP CONS_REP_MAP ,
INLINE_VIEW_4.COMPANY_ID COMPANY_ID ,
INLINE_VIEW_4.TRANS_DATE TRANS_DATE ,
INLINE_VIEW_4.TRANS_ID TRANS_ID ,
INLINE_VIEW_4.TRANS_TYPE TRANS_TYPE ,
INLINE_VIEW_4.TRANS_SUBTYPE TRANS_SUBTYPE ,
INLINE_VIEW_4.PRODUCT_CODE PRODUCT_CODE ,
INLINE_VIEW_4.WHSE_CODE WHSE_CODE ,
INLINE_VIEW_4.WHSE_STRG_LOCATION WHSE_STRG_LOCATION ,
INLINE_VIEW_4.MOVE_ID MOVE_ID ,
INLINE_VIEW_4.PQTY PQTY ,
INLINE_VIEW_4.SQTY SQTY ,
INLINE_VIEW_4.BATCH_NUMBER BATCH_NUMBER ,
INLINE_VIEW_4.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID ,
INLINE_VIEW_4.PUOM PUOM ,
INLINE_VIEW_4.REF_DOC_ID REF_DOC_ID ,
INLINE_VIEW_4.COMPLETED_IND COMPLETED_IND ,
INLINE_VIEW_4.REASON_CODE REASON_CODE ,
INLINE_VIEW_4.CREATED_BY CREATED_BY ,
INLINE_VIEW_4.COMMENTS COMMENTS ,
INLINE_VIEW_4.SHIFT_NO SHIFT_NO ,
INLINE_VIEW_4.AREA_TYPE AREA_TYPE ,
INLINE_VIEW_4.LAST_UPDATED_DATE LAST_UPDATED_DATE ,
INLINE_VIEW_4.WHSE_LOC_KEY WHSE_LOC_KEY ,
INLINE_VIEW_4.WHSE_CODE_1 WHSE_CODE_1 ,
INLINE_VIEW_4.WHSE_STRG_LOCATION_1 WHSE_STRG_LOCATION_1 ,
INLINE_VIEW_4.AREA AREA ,
INLINE_VIEW_4.WHSE_CODE_2 WHSE_CODE_2 ,
INLINE_VIEW_4.AREA_1 AREA_1 ,
INLINE_VIEW_4.WHSE_AREA_KEY WHSE_AREA_KEY ,
INLINE_VIEW_4.PRODUCT_KEY PRODUCT_KEY ,
INLINE_VIEW_4.PRODUCT_CODE_1 PRODUCT_CODE_1 ,
INLINE_VIEW_4.SUOM SUOM ,
INLINE_VIEW_4.BATCH_KEY BATCH_KEY ,
INLINE_VIEW_4.BATCH_NUMBER_1 BATCH_NUMBER_1 ,
INLINE_VIEW_4.BATCH_DATE BATCH_DATE ,
INLINE_VIEW_4.TRANSTYPE_KEY TRANSTYPE_KEY ,
INLINE_VIEW_4.TRAN_TYPE TRAN_TYPE ,
INLINE_VIEW_4.TRAN_SUBTYPE TRAN_SUBTYPE ,
INLINE_VIEW_4.CREATED_BY_1 CREATED_BY_1 ,
ODS_D_WMS_ALSER_USERS.USERS_KEY USERS_KEY ,
ODS_D_WMS_ALSER_USERS.COMPANY_ID COMPANY_ID_1 ,
ODS_D_WMS_ALSER_USERS.USER_LOGON USER_LOGON
FROM
(
SELECT
INLINE_VIEW_3.CREATED_DATE CREATED_DATE ,
INLINE_VIEW_3.LAST_UPDATED_BY LAST_UPDATED_BY ,
INLINE_VIEW_3.CONS_REP_MAP CONS_REP_MAP ,
INLINE_VIEW_3.COMPANY_ID COMPANY_ID ,
INLINE_VIEW_3.TRANS_DATE TRANS_DATE ,
INLINE_VIEW_3.TRANS_ID TRANS_ID ,
INLINE_VIEW_3.TRANS_TYPE TRANS_TYPE ,
INLINE_VIEW_3.TRANS_SUBTYPE TRANS_SUBTYPE ,
INLINE_VIEW_3.PRODUCT_CODE PRODUCT_CODE ,
INLINE_VIEW_3.WHSE_CODE WHSE_CODE ,
INLINE_VIEW_3.WHSE_STRG_LOCATION WHSE_STRG_LOCATION ,
INLINE_VIEW_3.MOVE_ID MOVE_ID ,
INLINE_VIEW_3.PQTY PQTY ,
INLINE_VIEW_3.SQTY SQTY ,
INLINE_VIEW_3.BATCH_NUMBER BATCH_NUMBER ,
INLINE_VIEW_3.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID ,
INLINE_VIEW_3.PUOM PUOM ,
INLINE_VIEW_3.REF_DOC_ID REF_DOC_ID ,
INLINE_VIEW_3.COMPLETED_IND COMPLETED_IND ,
INLINE_VIEW_3.REASON_CODE REASON_CODE ,
INLINE_VIEW_3.CREATED_BY CREATED_BY ,
INLINE_VIEW_3.COMMENTS COMMENTS ,
INLINE_VIEW_3.SHIFT_NO SHIFT_NO ,
INLINE_VIEW_3.AREA_TYPE AREA_TYPE ,
INLINE_VIEW_3.LAST_UPDATED_DATE LAST_UPDATED_DATE ,
INLINE_VIEW_3.WHSE_LOC_KEY WHSE_LOC_KEY ,
INLINE_VIEW_3.WHSE_CODE_1 WHSE_CODE_1 ,
INLINE_VIEW_3.WHSE_STRG_LOCATION_1 WHSE_STRG_LOCATION_1 ,
INLINE_VIEW_3.AREA AREA ,
INLINE_VIEW_3.WHSE_CODE_2 WHSE_CODE_2 ,
INLINE_VIEW_3.AREA_1 AREA_1 ,
INLINE_VIEW_3.WHSE_AREA_KEY WHSE_AREA_KEY ,
INLINE_VIEW_3.PRODUCT_KEY PRODUCT_KEY ,
INLINE_VIEW_3.PRODUCT_CODE_1 PRODUCT_CODE_1 ,
INLINE_VIEW_3.SUOM SUOM ,
INLINE_VIEW_3.BATCH_KEY BATCH_KEY ,
INLINE_VIEW_3.BATCH_NUMBER_1 BATCH_NUMBER_1 ,
INLINE_VIEW_3.BATCH_DATE BATCH_DATE ,
ODS_D_WMS_ALSER_TRANSTYPES.TRANSTYPE_KEY TRANSTYPE_KEY ,
ODS_D_WMS_ALSER_TRANSTYPES.TRAN_TYPE TRAN_TYPE ,
ODS_D_WMS_ALSER_TRANSTYPES.TRAN_SUBTYPE TRAN_SUBTYPE ,
ODS_D_WMS_ALSER_TRANSTYPES.CREATED_BY CREATED_BY_1
FROM
(
SELECT
INLINE_VIEW_2.CREATED_DATE CREATED_DATE ,
INLINE_VIEW_2.LAST_UPDATED_BY LAST_UPDATED_BY ,
INLINE_VIEW_2.CONS_REP_MAP CONS_REP_MAP ,
INLINE_VIEW_2.COMPANY_ID COMPANY_ID ,
INLINE_VIEW_2.TRANS_DATE TRANS_DATE ,
INLINE_VIEW_2.TRANS_ID TRANS_ID ,
INLINE_VIEW_2.TRANS_TYPE TRANS_TYPE ,
INLINE_VIEW_2.TRANS_SUBTYPE TRANS_SUBTYPE ,
INLINE_VIEW_2.PRODUCT_CODE PRODUCT_CODE ,
INLINE_VIEW_2.WHSE_CODE WHSE_CODE ,
INLINE_VIEW_2.WHSE_STRG_LOCATION WHSE_STRG_LOCATION ,
INLINE_VIEW_2.MOVE_ID MOVE_ID ,
INLINE_VIEW_2.PQTY PQTY ,
INLINE_VIEW_2.SQTY SQTY ,
INLINE_VIEW_2.BATCH_NUMBER BATCH_NUMBER ,
INLINE_VIEW_2.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID ,
INLINE_VIEW_2.PUOM PUOM ,
INLINE_VIEW_2.REF_DOC_ID REF_DOC_ID ,
INLINE_VIEW_2.COMPLETED_IND COMPLETED_IND ,
INLINE_VIEW_2.REASON_CODE REASON_CODE ,
INLINE_VIEW_2.CREATED_BY CREATED_BY ,
INLINE_VIEW_2.COMMENTS COMMENTS ,
INLINE_VIEW_2.SHIFT_NO SHIFT_NO ,
INLINE_VIEW_2.AREA_TYPE AREA_TYPE ,
INLINE_VIEW_2.LAST_UPDATED_DATE LAST_UPDATED_DATE ,
INLINE_VIEW_2.WHSE_LOC_KEY WHSE_LOC_KEY ,
INLINE_VIEW_2.WHSE_CODE_1 WHSE_CODE_1 ,
INLINE_VIEW_2.WHSE_STRG_LOCATION_1 WHSE_STRG_LOCATION_1 ,
INLINE_VIEW_2.AREA AREA ,
INLINE_VIEW_2.WHSE_CODE_2 WHSE_CODE_2 ,
INLINE_VIEW_2.AREA_1 AREA_1 ,
INLINE_VIEW_2.WHSE_AREA_KEY WHSE_AREA_KEY ,
INLINE_VIEW_2.PRODUCT_KEY PRODUCT_KEY ,
INLINE_VIEW_2.PRODUCT_CODE_1 PRODUCT_CODE_1 ,
INLINE_VIEW_2.SUOM SUOM ,
ODS_D_WMS_ALSER_BATCHES.BATCH_KEY BATCH_KEY ,
ODS_D_WMS_ALSER_BATCHES.BATCH_NUMBER BATCH_NUMBER_1 ,
ODS_D_WMS_ALSER_BATCHES.BATCH_DATE BATCH_DATE
FROM
(
SELECT
INLINE_VIEW_1.CREATED_DATE CREATED_DATE ,
INLINE_VIEW_1.LAST_UPDATED_BY LAST_UPDATED_BY ,
INLINE_VIEW_1.CONS_REP_MAP CONS_REP_MAP ,
INLINE_VIEW_1.COMPANY_ID COMPANY_ID ,
INLINE_VIEW_1.TRANS_DATE TRANS_DATE ,
INLINE_VIEW_1.TRANS_ID TRANS_ID ,
INLINE_VIEW_1.TRANS_TYPE TRANS_TYPE ,
INLINE_VIEW_1.TRANS_SUBTYPE TRANS_SUBTYPE ,
INLINE_VIEW_1.PRODUCT_CODE PRODUCT_CODE ,
INLINE_VIEW_1.WHSE_CODE WHSE_CODE ,
INLINE_VIEW_1.WHSE_STRG_LOCATION WHSE_STRG_LOCATION ,
INLINE_VIEW_1.MOVE_ID MOVE_ID ,
INLINE_VIEW_1.PQTY PQTY ,
INLINE_VIEW_1.SQTY SQTY ,
INLINE_VIEW_1.BATCH_NUMBER BATCH_NUMBER ,
INLINE_VIEW_1.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID ,
INLINE_VIEW_1.PUOM PUOM ,
INLINE_VIEW_1.REF_DOC_ID REF_DOC_ID ,
INLINE_VIEW_1.COMPLETED_IND COMPLETED_IND ,
INLINE_VIEW_1.REASON_CODE REASON_CODE ,
INLINE_VIEW_1.CREATED_BY CREATED_BY ,
INLINE_VIEW_1.COMMENTS COMMENTS ,
INLINE_VIEW_1.SHIFT_NO SHIFT_NO ,
INLINE_VIEW_1.AREA_TYPE AREA_TYPE ,
INLINE_VIEW_1.LAST_UPDATED_DATE LAST_UPDATED_DATE ,
INLINE_VIEW_1.WHSE_LOC_KEY WHSE_LOC_KEY ,
INLINE_VIEW_1.WHSE_CODE_1 WHSE_CODE_1 ,
INLINE_VIEW_1.WHSE_STRG_LOCATION_1 WHSE_STRG_LOCATION_1 ,
INLINE_VIEW_1.AREA AREA ,
INLINE_VIEW_1.WHSE_CODE_2 WHSE_CODE_2 ,
INLINE_VIEW_1.AREA_1 AREA_1 ,
INLINE_VIEW_1.WHSE_AREA_KEY WHSE_AREA_KEY ,
NVL(ODS_D_WMS_ALSER_PROD.PRODUCT_KEY,0) PRODUCT_KEY ,
ODS_D_WMS_ALSER_PROD.PRODUCT_CODE PRODUCT_CODE_1 ,
ODS_D_WMS_ALSER_PROD.SUOM SUOM
FROM
(
SELECT
INLINE_VIEW.CREATED_DATE CREATED_DATE ,
INLINE_VIEW.LAST_UPDATED_BY LAST_UPDATED_BY ,
INLINE_VIEW.CONS_REP_MAP CONS_REP_MAP ,
INLINE_VIEW.COMPANY_ID COMPANY_ID ,
INLINE_VIEW.TRANS_DATE TRANS_DATE ,
INLINE_VIEW.TRANS_ID TRANS_ID ,
INLINE_VIEW.TRANS_TYPE TRANS_TYPE ,
INLINE_VIEW.TRANS_SUBTYPE TRANS_SUBTYPE ,
INLINE_VIEW.PRODUCT_CODE PRODUCT_CODE ,
INLINE_VIEW.WHSE_CODE WHSE_CODE ,
INLINE_VIEW.WHSE_STRG_LOCATION WHSE_STRG_LOCATION ,
INLINE_VIEW.MOVE_ID MOVE_ID ,
INLINE_VIEW.PQTY PQTY ,
INLINE_VIEW.SQTY SQTY ,
INLINE_VIEW.BATCH_NUMBER BATCH_NUMBER ,
INLINE_VIEW.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID ,
INLINE_VIEW.PUOM PUOM ,
INLINE_VIEW.REF_DOC_ID REF_DOC_ID ,
INLINE_VIEW.COMPLETED_IND COMPLETED_IND ,
INLINE_VIEW.REASON_CODE REASON_CODE ,
INLINE_VIEW.CREATED_BY CREATED_BY ,
INLINE_VIEW.COMMENTS COMMENTS ,
INLINE_VIEW.SHIFT_NO SHIFT_NO ,
INLINE_VIEW.AREA_TYPE AREA_TYPE ,
INLINE_VIEW.LAST_UPDATED_DATE LAST_UPDATED_DATE ,
INLINE_VIEW.WHSE_LOC_KEY WHSE_LOC_KEY ,
INLINE_VIEW.WHSE_CODE_1 WHSE_CODE_1 ,
INLINE_VIEW.WHSE_STRG_LOCATION_1 WHSE_STRG_LOCATION_1 ,
INLINE_VIEW.AREA AREA ,
ODS_D_WMS_ALSER_WHSE_AREA.WHSE_CODE WHSE_CODE_2 ,
ODS_D_WMS_ALSER_WHSE_AREA.AREA AREA_1 ,
NVL(ODS_D_WMS_ALSER_WHSE_AREA.WHSE_AREA_KEY,0) WHSE_AREA_KEY
FROM
(
SELECT
JOIN_A.CREATED_DATE CREATED_DATE ,
JOIN_A.LAST_UPDATED_BY LAST_UPDATED_BY ,
JOIN_A.CONS_REP_MAP CONS_REP_MAP ,
JOIN_A.COMPANY_ID COMPANY_ID ,
JOIN_A.TRANS_DATE TRANS_DATE ,
JOIN_A.TRANS_ID TRANS_ID ,
JOIN_A.TRANS_TYPE TRANS_TYPE ,
JOIN_A.TRANS_SUBTYPE TRANS_SUBTYPE ,
JOIN_A.PRODUCT_CODE PRODUCT_CODE ,
JOIN_A.WHSE_CODE WHSE_CODE ,
JOIN_A.WHSE_STRG_LOCATION WHSE_STRG_LOCATION ,
JOIN_A.MOVE_ID MOVE_ID ,
JOIN_A.PQTY PQTY ,
JOIN_A.SQTY SQTY ,
JOIN_A.BATCH_NUMBER BATCH_NUMBER ,
JOIN_A.PRODUCT_INSTANCE_ID PRODUCT_INSTANCE_ID ,
JOIN_A.PUOM PUOM ,
JOIN_A.REF_DOC_ID REF_DOC_ID ,
JOIN_A.COMPLETED_IND COMPLETED_IND ,
JOIN_A.REASON_CODE REASON_CODE ,
JOIN_A.CREATED_BY CREATED_BY ,
JOIN_A.COMMENTS COMMENTS ,
JOIN_A.SHIFT_NO SHIFT_NO ,
JOIN_A.AREA_TYPE AREA_TYPE ,
JOIN_A.LAST_UPDATED_DATE LAST_UPDATED_DATE ,
NVL(ODS_D_WMS_ALSER_WHSE.WHSE_LOC_KEY,0) WHSE_LOC_KEY ,
ODS_D_WMS_ALSER_WHSE.WHSE_CODE WHSE_CODE_1 ,
ODS_D_WMS_ALSER_WHSE.WHSE_STRG_LOCATION WHSE_STRG_LOCATION_1 ,
ODS_D_WMS_ALSER_WHSE.AREA AREA
FROM
APP.C$_0JOIN JOIN_A , APP.ODS_D_WMS_ALSER_WHSE ODS_D_WMS_ALSER_WHSE
WHERE
(JOIN_A.WHSE_CODE = ODS_D_WMS_ALSER_WHSE.WHSE_CODE
and
JOIN_A.WHSE_STRG_LOCATION = ODS_D_WMS_ALSER_WHSE.WHSE_STRG_LOCATION
)
) INLINE_VIEW , APP.ODS_D_WMS_ALSER_WHSE_AREA ODS_D_WMS_ALSER_WHSE_AREA
WHERE
(INLINE_VIEW.WHSE_CODE_1 = ODS_D_WMS_ALSER_WHSE_AREA.WHSE_CODE
and
INLINE_VIEW.AREA = ODS_D_WMS_ALSER_WHSE_AREA.AREA
)
) INLINE_VIEW_1 , APP.ODS_D_WMS_ALSER_PROD ODS_D_WMS_ALSER_PROD
WHERE
(INLINE_VIEW_1.PRODUCT_CODE = ODS_D_WMS_ALSER_PROD.PRODUCT_CODE
)
) INLINE_VIEW_2 , APP.ODS_D_WMS_ALSER_BATCHES ODS_D_WMS_ALSER_BATCHES
WHERE
(INLINE_VIEW_2.BATCH_NUMBER = ODS_D_WMS_ALSER_BATCHES.BATCH_NUMBER
)
) INLINE_VIEW_3 , APP.ODS_D_WMS_ALSER_TRANSTYPES ODS_D_WMS_ALSER_TRANSTYPES
WHERE
(INLINE_VIEW_3.TRANS_TYPE = ODS_D_WMS_ALSER_TRANSTYPES.TRAN_TYPE
and
INLINE_VIEW_3.TRANS_SUBTYPE = ODS_D_WMS_ALSER_TRANSTYPES.TRAN_SUBTYPE
)
) INLINE_VIEW_4 , APP.ODS_D_WMS_ALSER_USERS ODS_D_WMS_ALSER_USERS
WHERE
(INLINE_VIEW_4.CREATED_BY = ODS_D_WMS_ALSER_USERS.USER_LOGON
)
) INLINE_VIEW_5 , APP.ORG_UNIT ORG_UNIT
where (1=1)
and (INLINE_VIEW_5.COMPANY_ID = ORG_UNIT.COMPANY_ID (+))
) S
where NOT EXISTS
( select 1 from APP.ODS_F_WMS_ALSER_STK_MOVE T
where T.TRANS_ID = S.TRANS_ID
and ((T.ORG_UNIT = S.ORG_UNIT) or (T.ORG_UNIT IS NULL and S.ORG_UNIT IS NULL)) and
((T.COMPANY_ID = S.COMPANY_ID) or (T.COMPANY_ID IS NULL and S.COMPANY_ID IS NULL)) and
((T.MOVE_ID = S.MOVE_ID) or (T.MOVE_ID IS NULL and S.MOVE_ID IS NULL)) and
((T.TRANS_DATE_KEY = S.TRANS_DATE_KEY) or (T.TRANS_DATE_KEY IS NULL and S.TRANS_DATE_KEY IS NULL)) and
((T.TRANS_TIME_KEY = S.TRANS_TIME_KEY) or (T.TRANS_TIME_KEY IS NULL and S.TRANS_TIME_KEY IS NULL)) and
((T.PRODUCT_KEY = S.PRODUCT_KEY) or (T.PRODUCT_KEY IS NULL and S.PRODUCT_KEY IS NULL)) and
((T.WHSE_LOC_KEY = S.WHSE_LOC_KEY) or (T.WHSE_LOC_KEY IS NULL and S.WHSE_LOC_KEY IS NULL)) and
((T.WHSE_AREA_KEY = S.WHSE_AREA_KEY) or (T.WHSE_AREA_KEY IS NULL and S.WHSE_AREA_KEY IS NULL)) and
((T.TRANSTYPE_KEY = S.TRANSTYPE_KEY) or (T.TRANSTYPE_KEY IS NULL and S.TRANSTYPE_KEY IS NULL)) and
((T.USERS_KEY = S.USERS_KEY) or (T.USERS_KEY IS NULL and S.USERS_KEY IS NULL)) and
((T.BATCH_KEY = S.BATCH_KEY) or (T.BATCH_KEY IS NULL and S.BATCH_KEY IS NULL)) and
((T.COMMENTS = S.COMMENTS) or (T.COMMENTS IS NULL and S.COMMENTS IS NULL)) and
((T.SHIFT_NO = S.SHIFT_NO) or (T.SHIFT_NO IS NULL and S.SHIFT_NO IS NULL)) and
((T.AREA_TYPE = S.AREA_TYPE) or (T.AREA_TYPE IS NULL and S.AREA_TYPE IS NULL)) and
((T.BATCH_DATE = S.BATCH_DATE) or (T.BATCH_DATE IS NULL and S.BATCH_DATE IS NULL)) and
((T.BATCH_NUMBER = S.BATCH_NUMBER) or (T.BATCH_NUMBER IS NULL and S.BATCH_NUMBER IS NULL)) and
((T.PRODUCT_INSTANCE_ID = S.PRODUCT_INSTANCE_ID) or (T.PRODUCT_INSTANCE_ID IS NULL and S.PRODUCT_INSTANCE_ID IS NULL)) and
((T.PUOM = S.PUOM) or (T.PUOM IS NULL and S.PUOM IS NULL)) and
((T.SUOM = S.SUOM) or (T.SUOM IS NULL and S.SUOM IS NULL)) and
((T.REF_DOC_ID = S.REF_DOC_ID) or (T.REF_DOC_ID IS NULL and S.REF_DOC_ID IS NULL)) and
((T.COMPLETED_IND = S.COMPLETED_IND) or (T.COMPLETED_IND IS NULL and S.COMPLETED_IND IS NULL)) and
((T.REASON_CODE = S.REASON_CODE) or (T.REASON_CODE IS NULL and S.REASON_CODE IS NULL)) and
((T.PQTY = S.PQTY) or (T.PQTY IS NULL and S.PQTY IS NULL)) and
((T.SQTY = S.SQTY) or (T.SQTY IS NULL and S.SQTY IS NULL)) and
((T.PRODUCTION_PQTY = S.PRODUCTION_PQTY) or (T.PRODUCTION_PQTY IS NULL and S.PRODUCTION_PQTY IS NULL)) and
((T.PRODUCTION_SQTY = S.PRODUCTION_SQTY) or (T.PRODUCTION_SQTY IS NULL and S.PRODUCTION_SQTY IS NULL)) and
((T.PRODUCTION3RDPARTY_PQTY = S.PRODUCTION3RDPARTY_PQTY) or (T.PRODUCTION3RDPARTY_PQTY IS NULL and S.PRODUCTION3RDPARTY_PQTY IS NULL)) and
((T.PRODUCTION3RDPARTY_SQTY = S.PRODUCTION3RDPARTY_SQTY) or (T.PRODUCTION3RDPARTY_SQTY IS NULL and S.PRODUCTION3RDPARTY_SQTY IS NULL)) and
((T.SALES_PQTY = S.SALES_PQTY) or (T.SALES_PQTY IS NULL and S.SALES_PQTY IS NULL)) and
((T.SALES_SQTY = S.SALES_SQTY) or (T.SALES_SQTY IS NULL and S.SALES_SQTY IS NULL)) and
((T.SALES_RETURN_PQTY = S.SALES_RETURN_PQTY) or (T.SALES_RETURN_PQTY IS NULL and S.SALES_RETURN_PQTY IS NULL)) and
((T.SALES_RETURN_SQTY = S.SALES_RETURN_SQTY) or (T.SALES_RETURN_SQTY IS NULL and S.SALES_RETURN_SQTY IS NULL)) and
((T.RECLASS_PQTY = S.RECLASS_PQTY) or (T.RECLASS_PQTY IS NULL and S.RECLASS_PQTY IS NULL)) and
((T.RECLASS_SQTY = S.RECLASS_SQTY) or (T.RECLASS_SQTY IS NULL and S.RECLASS_SQTY IS NULL)) and
((T.REWORK_PQTY = S.REWORK_PQTY) or (T.REWORK_PQTY IS NULL and S.REWORK_PQTY IS NULL)) and
((T.REWORK_SQTY = S.REWORK_SQTY) or (T.REWORK_SQTY IS NULL and S.REWORK_SQTY IS NULL)) and
((T.TRANSFER_IN_PQTY = S.TRANSFER_IN_PQTY) or (T.TRANSFER_IN_PQTY IS NULL and S.TRANSFER_IN_PQTY IS NULL)) and
((T.TRANSFER_IN_SQTY = S.TRANSFER_IN_SQTY) or (T.TRANSFER_IN_SQTY IS NULL and S.TRANSFER_IN_SQTY IS NULL)) and
((T.TRANSFER_OUT_PQTY = S.TRANSFER_OUT_PQTY) or (T.TRANSFER_OUT_PQTY IS NULL and S.TRANSFER_OUT_PQTY IS NULL)) and
((T.TRANSFER_OUT_SQTY = S.TRANSFER_OUT_SQTY) or (T.TRANSFER_OUT_SQTY IS NULL and S.TRANSFER_OUT_SQTY IS NULL)) and
((T.STOCK_ADJUSTMENTS_PQTY = S.STOCK_ADJUSTMENTS_PQTY) or (T.STOCK_ADJUSTMENTS_PQTY IS NULL and S.STOCK_ADJUSTMENTS_PQTY IS NULL)) and
((T.STOCK_ADJUSTMENTS_SQTY = S.STOCK_ADJUSTMENTS_SQTY) or (T.STOCK_ADJUSTMENTS_SQTY IS NULL and S.STOCK_ADJUSTMENTS_SQTY IS NULL)) and
((T.STOCK_COUNT_PQTY = S.STOCK_COUNT_PQTY) or (T.STOCK_COUNT_PQTY IS NULL and S.STOCK_COUNT_PQTY IS NULL)) and
((T.STOCK_COUNT_SQTY = S.STOCK_COUNT_SQTY) or (T.STOCK_COUNT_SQTY IS NULL and S.STOCK_COUNT_SQTY IS NULL)) and
((T.STOCK_ON_HAND_PQTY = S.STOCK_ON_HAND_PQTY) or (T.STOCK_ON_HAND_PQTY IS NULL and S.STOCK_ON_HAND_PQTY IS NULL)) and
((T.STOCK_ON_HAND_SQTY = S.STOCK_ON_HAND_SQTY) or (T.STOCK_ON_HAND_SQTY IS NULL and S.STOCK_ON_HAND_SQTY IS NULL)) and
((T.HOSPITAL_STOCK_PQTY = S.HOSPITAL_STOCK_PQTY) or (T.HOSPITAL_STOCK_PQTY IS NULL and S.HOSPITAL_STOCK_PQTY IS NULL)) and
((T.HOSPITAL_STOCK_SQTY = S.HOSPITAL_STOCK_SQTY) or (T.HOSPITAL_STOCK_SQTY IS NULL and S.HOSPITAL_STOCK_SQTY IS NULL)) and
((T.INTERNAL_MOV_PQTY = S.INTERNAL_MOV_PQTY) or (T.INTERNAL_MOV_PQTY IS NULL and S.INTERNAL_MOV_PQTY IS NULL)) and
((T.INTERNAL_MOV_SQTY = S.INTERNAL_MOV_SQTY) or (T.INTERNAL_MOV_SQTY IS NULL and S.INTERNAL_MOV_SQTY IS NULL)) and
((T.STK_LESS_HOSP_PQTY = S.STK_LESS_HOSP_PQTY) or (T.STK_LESS_HOSP_PQTY IS NULL and S.STK_LESS_HOSP_PQTY IS NULL)) and
((T.STK_LESS_HOSP_SQTY = S.STK_LESS_HOSP_SQTY) or (T.STK_LESS_HOSP_SQTY IS NULL and S.STK_LESS_HOSP_SQTY IS NULL)) and
((T.CREATED_BY = S.CREATED_BY) or (T.CREATED_BY IS NULL and S.CREATED_BY IS NULL)) and
((T.CREATED_DATE = S.CREATED_DATE) or (T.CREATED_DATE IS NULL and S.CREATED_DATE IS NULL)) and
((T.LAST_UPDATED_BY = S.LAST_UPDATED_BY) or (T.LAST_UPDATED_BY IS NULL and S.LAST_UPDATED_BY IS NULL)) and
((T.LAST_UPDATED_DATE = S.LAST_UPDATED_DATE) or (T.LAST_UPDATED_DATE IS NULL and S.LAST_UPDATED_DATE IS NULL))
)