PCM - Correcct Linkage between Esponsor Created Records and corresponding PCM

Problem Statement

The PCM / esponsor DB is create is such a way that for a PCM record created from esponsor, it will appear as 2 rows in the DB:

• one row shows the esponsor data with status "Merged" or "Auto Created" without the PCM record number and other PCM related field
• one row shows the PCM data with the corresponding Esponsor numbers but not the details of the other esponsor fields

Aim is to harmonize report back on the esponsr row, the reference of the PCM id that got created. Please see file enclosed:

• green PCM IDs are the ones to be reported against the esponsor row to help create the linkage (GSC Type NPL)
• orange PCM IDs are the one to be reported against the esponsor row to help create the linkage (GSC type MC, OTHER, ST....)

Additionally, we have noticed some esponsor records being duplicated in the db due to below causes:

• different time stamps for the same date
• same product in multiple line to account for multiple years & volumes

Acceptance Criteria

• Can we populate back the PCM ID numbers at the esponsor row level  for the status Merged and Auto Created ?
• Can we suppress the duplicated esponsor line by removing time stamp and aggregate years and volumes (only applicable to Source Request)