Monday, August 17, 2009

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;

 

2 comments:

  1. Hi Bipin,

    i am trying to call a 3rd party Web service from Peoplesoft by consuming a web service, i have an error message

    Integration Gateway - HttpTargetConnector:ExternalApplicationException. Http status code HttpStatusCode returned : 500. (158,10623) PY_IC_DD_DTA_VW.BANK_CD.FieldChange PCPC:3115 Statement:35

    HttpTargetConnector:ExternalApplicationException. External System responded with an Error status. For Http Status Code explanation please check Http protocol Specifications.


    When i try that from Peoplecode. How ever the same xml works well when i use it via the IB service operation test utility, it gets a response back from the vendor.
    Please let me know if you have any ideas about resolving this issue.

    thanks
    ash

    ReplyDelete
  2. Very useful to know Meta SQL could be used in the where clause ... I was handcuffed by not being able to use an ALIAS .... this was very appreciated. Peace .....

    ReplyDelete