VAR_DATA_xxxx Tables


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:
  1. Add the Field(s) onto the VAR_DATA_xxxx Record.
  2. 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.
  3. 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;

0 comments: