View: MSC_UNDO_SUMMARY_V

Product:	MSC - Advanced Supply Chain Planning
Description:	this view shows the summary of all the changes made to the plan output
Implementation/DBA Data:	APPS.MSC_UNDO_SUMMARY_V

View Text

SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, MTP.ORGANIZATION_CODE
, I.ITEM_NAME
, NVL(S.ORDER_NUMBER
, S.TRANSACTION_ID)
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, S.INVENTORY_ITEM_ID
, S.ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_SUPPLIES S
, MSC_TRADING_PARTNERS MTP
, MSC_ITEMS I
, FND_USER US
WHERE S.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID(+) AND S.ORGANIZATION_ID = MTP.SR_TP_ID(+) AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID(+) AND MTP.PARTNER_TYPE (+) = 3 AND U.TRANSACTION_ID = S.TRANSACTION_ID AND U.PLAN_ID = S.PLAN_ID AND U.SR_INSTANCE_ID = S.SR_INSTANCE_ID AND U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED = 1 AND U.PARENT_ID IS NULL UNION ALL SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, MTP.ORGANIZATION_CODE
, I.ITEM_NAME
, NVL(D.ORDER_NUMBER
, D.DEMAND_ID)
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, D.INVENTORY_ITEM_ID
, D.ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_DEMANDS D
, MSC_TRADING_PARTNERS MTP
, MSC_ITEMS I
, FND_USER US
WHERE D.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID(+) AND D.ORGANIZATION_ID = MTP.SR_TP_ID(+) AND D.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID(+) AND MTP.PARTNER_TYPE (+) = 3 AND U.TRANSACTION_ID = D.DEMAND_ID AND U.PLAN_ID = D.PLAN_ID AND U.SR_INSTANCE_ID = D.SR_INSTANCE_ID AND U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED = 2 AND U.PARENT_ID IS NULL UNION ALL SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, I.ITEM_NAME
, MTP.PARTNER_NAME
, MTPS.TP_SITE_CODE
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, S.INVENTORY_ITEM_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, S.SUPPLIER_ID
, S.SUPPLIER_SITE_ID
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_SUPPLIER_CAPACITIES S
, MSC_TRADING_PARTNERS MTP
, MSC_TRADING_PARTNER_SITES MTPS
, MSC_ITEMS I
, FND_USER US
WHERE S.SUPPLIER_ID = MTP.PARTNER_ID(+) AND S.SUPPLIER_SITE_ID = MTPS.PARTNER_SITE_ID(+) AND S.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID(+) AND MTP.PARTNER_TYPE (+) = 1 AND U.TRANSACTION_ID = S.TRANSACTION_ID AND U.PLAN_ID = S.PLAN_ID AND U.SR_INSTANCE_ID = S.SR_INSTANCE_ID AND U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED = 3 AND U.PARENT_ID IS NULL UNION ALL SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, MTP.ORGANIZATION_CODE
, D.DEPARTMENT_CODE||'/'||D.RESOURCE_CODE
, FND_DATE.DATE_TO_DISPLAYDATE(R.SHIFT_DATE)
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, TO_NUMBER(NULL)
, R.ORGANIZATION_ID
, R.DEPARTMENT_ID
, R.RESOURCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_NET_RESOURCE_AVAIL R
, MSC_DEPARTMENT_RESOURCES D
, MSC_TRADING_PARTNERS MTP
, FND_USER US
WHERE R.ORGANIZATION_ID = MTP.SR_TP_ID(+) AND R.SR_INSTANCE_ID = MTP.SR_INSTANCE_ID(+) AND R.PLAN_ID = D.PLAN_ID(+) AND MTP.PARTNER_TYPE (+) = 3 AND R.SR_INSTANCE_ID = D.SR_INSTANCE_ID(+) AND R.ORGANIZATION_ID = D.ORGANIZATION_ID(+) AND R.DEPARTMENT_ID = D.DEPARTMENT_ID(+) AND R.RESOURCE_ID = D.RESOURCE_ID(+) AND U.TRANSACTION_ID = R.TRANSACTION_ID AND U.PLAN_ID = R.PLAN_ID AND U.SR_INSTANCE_ID = R.SR_INSTANCE_ID AND U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED = 4 AND U.PARENT_ID IS NULL UNION ALL SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, P.COMPILE_DESIGNATOR
, NULL
, NULL
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_PLANS P
, FND_USER US
WHERE U.PLAN_ID = P.PLAN_ID AND U.SR_INSTANCE_ID = P.SR_INSTANCE_ID AND U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED = 5 AND U.PARENT_ID IS NULL UNION ALL SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, DECODE(U.ACTION
, 3
, U.BOOKMARK_NAME
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION))
, NULL
, NULL
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, FND_USER US
WHERE U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED IS NULL AND U.PARENT_ID IS NULL UNION ALL SELECT U.UNDO_ID
, U.PLAN_ID
, U.SR_INSTANCE_ID
, U.TABLE_CHANGED
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_TABLE'
, TABLE_CHANGED)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, MSC_GET_NAME.ORG_CODE(MRR.ORGANIZATION_ID
, MRR.SR_INSTANCE_ID)
, MSC_GET_NAME.DEPARTMENT_CODE(DECODE(MRR.RESOURCE_ID
, -1
, 1
, 2)
, MRR.DEPARTMENT_ID
, MRR.ORGANIZATION_ID
, MRR.PLAN_ID
, MRR.SR_INSTANCE_ID)
, MSC_GET_NAME.RESOURCE_CODE(MRR.RESOURCE_ID
, MRR.DEPARTMENT_ID
, MRR.ORGANIZATION_ID
, MRR.PLAN_ID
, MRR.SR_INSTANCE_ID)
, US.USER_NAME
, U.LAST_UPDATE_DATE
, U.LAST_UPDATED_BY
, U.CREATION_DATE
, U.CREATED_BY
, U.LAST_UPDATE_LOGIN
, U.TRANSACTION_ID
, TO_NUMBER(NULL)
, MRR.ORGANIZATION_ID
, MRR.DEPARTMENT_ID
, MRR.RESOURCE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_RESOURCE_REQUIREMENTS MRR
, FND_USER US
WHERE U.TRANSACTION_ID = MRR.TRANSACTION_ID AND U.PLAN_ID = MRR.PLAN_ID AND U.SR_INSTANCE_ID = MRR.SR_INSTANCE_ID AND U.LAST_UPDATED_BY = US.USER_ID AND U.TABLE_CHANGED = 6 AND U.PARENT_ID IS NULL

Columns