In version 7 variable data specific to each ADMIN_FUNCTION for a Communication was stored on the COMMUNICATION Table. This has changed in v9, with a specific Table for each Admin Function.
For example: Records VAR_DATA_ADMA stores variable data for Admin function ADMA, VAR_DATA_SPRG for SPRG and so on.
PS_COMMUNICATION joins to PS_VAR_DATA_xxxx on COMMON_ID and VAR_DATA_SEQ.
IMPORTANT: This is completely different to a normal child table in PeopleSoft (and one has to question wtf they did it for). VAR_DATA_xxxx is not a child table of COMMUNICATION, in the typical PeopleSoft fashion. COMMUNICATION.VAR_DATA_SEQ is a Foreign Key, as in a standard, relational database. The reasoning for this is unknown to me as it doesn't match the other 30,000 Tables.
There is PeopleCode on COMMUNICATION.VAR_DATA_SEQ that handles synchronising its value between the two Tables. It might pay to spend 5 minutes understanding it.
______________________
Common scenario: Business was using a variable data field in v7 that is not on the associated VAR_DATA_xxxx Record in v9. What to do in v9?
Solution:
- Add the Field(s) onto the VAR_DATA_xxxx Record.
- Each Admin Function has an associated Variable Data page called COMM_VAR_xxxx, where xxxx is the Admin Function code. Add the fields to this page from Record.CM_DERIVED.
- Use the following script to populate the v9 tables:
-- Update configuration table.
UPDATE PS_ADM_FUNCTN_TBL
SET
ACAD_CAREER_AF = 'Y', STDNT_CAR_NBR_AF = 'Y', AID_YEAR_AF = 'Y', CAMPUS_EVNT_NBR_AF = 'Y', EVENT_MTG_NBR_AF = 'Y'
, ADMIN_FCN_PEOPLE = 'N', SSR_RS_CANDNBR_AF = 'N'
WHERE ADMIN_FUNCTION = 'THES';
COMMIT;
-- Move data from v7 PS_COMMUNICATION into v9 VAR_DATA_xxxx
DELETE FROM PS_VAR_DATA_THES;
INSERT INTO PS_VAR_DATA_THES (
COMMON_ID
, VAR_DATA_SEQ
, SSR_RS_CANDIT_NBR
, SSR_RS_SUBMSSN_NBR
, ACAD_CAREER -- < New field
, STDNT_CAR_NBR -- < New field
, AID_YEAR -- < New field
, CAMPUS_EVENT_NBR -- < New field
, EVENT_MTG_NBR -- < New field
)
SELECT
EMPLID
, (ROW_NUMBER() OVER(PARTITION BY EMPLID ORDER BY EMPLID)) - 1 AS VAR_DATA_SEQ
, 0
, 0
, ACAD_CAREER
, STDNT_CAR_NBR
, AID_YEAR
, CAMPUS_EVENT_NBR
, EVENT_MTG_NBR
FROM
(SELECT DISTINCT
EMPLID
, ACAD_CAREER
, STDNT_CAR_NBR
, AID_YEAR
, CAMPUS_EVENT_NBR
, EVENT_MTG_NBR
FROM PS_COMMUNICATION@NSCONV.UNSW.EDU.AU
WHERE ADMIN_FUNCTION = 'THES')
;
-- Update ps_communication to point to correct var_data/var_data_seq
-- This updates ps_communication by first finding the matching row in v7, and then finding the converted var data in v9.
-- It points to the new v9 data by updating ps_communication.var_data_seq.
update ps_communication x
set x.var_data_seq =
(select var_Data_Seq
from
ps_communication@nsconv.unsw.edu.au a
, ps_var_data_thes b
where a.emplid = x.common_id
and a.comm_dttm = x.comm_dttm
and a.institution = x.institution
and a.admin_function = x.admin_function
and a.comm_category = x.comm_category
and b.common_id = a.emplid
and b.acad_career = a.acad_career
and b.stdnt_car_nbr = a.stdnt_car_nbr
and b.aid_year = a.aid_year
and b.campus_event_nbr = a.campus_event_nbr
and b.event_mtg_nbr = a.event_mtg_nbr
)
where x.admin_function = 'THES'
;
commit;
Posts
