DMS - Data Mover Scripts

DMS to Export PeopleSoft Project Message Catalog Entries
-
Export PSMSGCATDEFN Where MESSAGE_SET_NBR=14950 And MESSAGE_NBR IN (55, 56, 57, 58, 59, 60, 61, 64, 65, 66, 67);
Export PSMSGCATDEFN Where MESSAGE_SET_NBR=14951 And MESSAGE_NBR IN 138, 139, 27, 28, 29, 30, 63, 64);

We moved to following DMS to export all the message catalog entries inserted in the project definition. And it doesn’t have to change every time you create and insert new entries in the project definition.

Similar DMS is used to pick the master project catalog entries from bundle project, to which all the teams in campus solutions merge their project to.

EXPORT PSMSGCATDEFN
WHERE EXISTS
(
SELECT 'X' FROM PSPROJECTITEM B
WHERE B.PROJECTNAME = '<< insert your project name >>'
AND OBJECTTYPE = 25
AND OBJECTVALUE1 = MESSAGE_SET_NBR
AND OBJECTVALUE2 = MESSAGE_NBR
);

-
-
DMS to remove past and future dated rows from a effective dated table
-

Following gave SQL Validator issues stating that table name alias can NOT be used in a delete / update SQL in a DMS script.
Following is the DMS scripts which leads to SQL Validator issue
-- Removing past and future dated rows from a effective dated table

DELETE FROM PS_TABLE_NAME TABLE_ALIAS1
WHERE EXISTS(
SELECT 'X' FROM PS_TABLE_NAME TABLE_ALIAS2
WHERE TABLE_ALIAS1 .FIELD1 = TABLE_ALIAS2.FIELD1
AND TABLE_ALIAS1 .FIELD2 = TABLE_ALIAS2.FIELD2
AND %EffdtCheck(TABLE_NAME, TABLE_ALIAS2, %CurrentDateTimeIn)
AND TABLE_ALIAS1.EFFDT <> TABLE_ALIAS2.EFFDT
);

And I used the following to resolve that issue, the crux is to hold the required data in a .dat file, delete the complete table and re-populate the table with the data in the .dat file. So both export and import happening in the same DMS import script we submit to bundle.

-- Removing past and future dated rows from a effective dated table
SET OUTPUT updXXXXXX_XX.dat;
EXPORT TABLE_NAME WHERE %EffdtCheck(TABLE_NAME, PS_TABLE_NAME, %CurrentDateIn);
DELETE FROM PS_SAE_CRSCTLG_EXT;
SET INPUT updXXXXXX_XX.dat;
IMPORT TABLE_NAME;