VIEW: APPS.MSC_UNDO_SUMMARY_V

Source

View Text - Preformatted

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 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(msp.from_organization_id,msp.sr_instance_id), msc_get_name.org_code(msp.to_organization_id,msp.to_sr_instance_id), to_char(msp.shipment_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), msp.from_organization_id, to_number(null), to_number(null), to_number(NULL), to_number(NULL), u.parent_id FROM msc_undo_summary u, msc_shipments msp, fnd_user us WHERE u.transaction_id = msp.shipment_id AND u.plan_id = msp.plan_id AND u.sr_instance_id = msp.sr_instance_id AND u.last_updated_by = us.user_id AND u.table_changed = 7 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',4), u.action, msc_get_name.lookup_meaning('MSC_UNDO_ACTION',action), mtp.organization_code, d.department_code||'/'||d.resource_code||'/'|| msc_get_name.item_name(mdri.equipment_item_id, null, null, null) || decode(mdri.serial_number, null, '', ':'||mdri.serial_number), 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_res_inst_avail r, msc_dept_res_instances mdri, 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 = mdri.plan_id(+) AND mtp.partner_type (+) = 3 AND r.sr_instance_id = mdri.sr_instance_id(+) AND r.organization_id = mdri.organization_id(+) AND r.department_id = mdri.department_id(+) AND r.resource_id = mdri.resource_id(+) AND r.res_instance_id = mdri.res_instance_id AND r.serial_number = mdri.serial_number AND u.transaction_id = r.inst_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 =8 AND u.parent_id IS NULL AND mdri.resource_id = d.resource_id AND mdri.department_id = d.department_id AND mdri.sr_instance_id = d.sr_instance_id AND mdri.plan_id = d.plan_id AND mdri.organization_id = d.organization_id

View Text - HTML Formatted

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 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(MSP.FROM_ORGANIZATION_ID
, MSP.SR_INSTANCE_ID)
, MSC_GET_NAME.ORG_CODE(MSP.TO_ORGANIZATION_ID
, MSP.TO_SR_INSTANCE_ID)
, TO_CHAR(MSP.SHIPMENT_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)
, MSP.FROM_ORGANIZATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, U.PARENT_ID
FROM MSC_UNDO_SUMMARY U
, MSC_SHIPMENTS MSP
, FND_USER US
WHERE U.TRANSACTION_ID = MSP.SHIPMENT_ID
AND U.PLAN_ID = MSP.PLAN_ID
AND U.SR_INSTANCE_ID = MSP.SR_INSTANCE_ID
AND U.LAST_UPDATED_BY = US.USER_ID
AND U.TABLE_CHANGED = 7
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'
, 4)
, U.ACTION
, MSC_GET_NAME.LOOKUP_MEANING('MSC_UNDO_ACTION'
, ACTION)
, MTP.ORGANIZATION_CODE
, D.DEPARTMENT_CODE||'/'||D.RESOURCE_CODE||'/'|| MSC_GET_NAME.ITEM_NAME(MDRI.EQUIPMENT_ITEM_ID
, NULL
, NULL
, NULL) || DECODE(MDRI.SERIAL_NUMBER
, NULL
, ''
, ':'||MDRI.SERIAL_NUMBER)
, 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_RES_INST_AVAIL R
, MSC_DEPT_RES_INSTANCES MDRI
, 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 = MDRI.PLAN_ID(+)
AND MTP.PARTNER_TYPE (+) = 3
AND R.SR_INSTANCE_ID = MDRI.SR_INSTANCE_ID(+)
AND R.ORGANIZATION_ID = MDRI.ORGANIZATION_ID(+)
AND R.DEPARTMENT_ID = MDRI.DEPARTMENT_ID(+)
AND R.RESOURCE_ID = MDRI.RESOURCE_ID(+)
AND R.RES_INSTANCE_ID = MDRI.RES_INSTANCE_ID
AND R.SERIAL_NUMBER = MDRI.SERIAL_NUMBER
AND U.TRANSACTION_ID = R.INST_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 =8
AND U.PARENT_ID IS NULL
AND MDRI.RESOURCE_ID = D.RESOURCE_ID
AND MDRI.DEPARTMENT_ID = D.DEPARTMENT_ID
AND MDRI.SR_INSTANCE_ID = D.SR_INSTANCE_ID
AND MDRI.PLAN_ID = D.PLAN_ID
AND MDRI.ORGANIZATION_ID = D.ORGANIZATION_ID