Deleting GL Business Units

April 17th, 2012 by Tyson No comments »

Although you typically don’t want to do this, let’s say you set up a GLBU in a non-production environment that really shouldn’t be there.  Here is some SQL to get rid of the GLBU.  Other ideas would be to enable BU security and restrict access to the GLBU’s or just manage them through the Open/Closed periods control, but this should get them out of everyone’s prompt list.

My hope is that SOMEDAY Oracle / PeopleSoft will allow for the INACTIVATION of GLBU’s, but that’s been a feature request for what seems like ever.  Don’t hold your breath.

Note: replace US001 with your business unit(s).  Also, this is valid as of PeopleSoft Financials v9.1, Tools 8.52.

DELETE FROM PS_BUS_UNIT_TBL_GL WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BUS_UNIT_TBL_FS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_SETID_TBL WHERE SETID = ‘US001’;
DELETE FROM PS_SET_CNTRL_TBL WHERE SETCNTRLVALUE = ‘US001’;
DELETE FROM PS_SET_CNTRL_GROUP WHERE SETCNTRLVALUE = ‘US001’;
DELETE FROM PS_SET_CNTRL_REC WHERE SETCNTRLVALUE = ‘US001’;
DELETE FROM PS_SET_CNTRL_TREE WHERE SETCNTRLVALUE = ‘US001’;
DELETE FROM PS_BU_JE_ID_CFS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_JE_IC_CFS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_JE_BS_CFS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_JE_ES_CFS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_JE_AS_CFS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BUL_JE_RA_CFS WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BUS_UNIT_IDS_GL WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_ADB_INCR_CONTRL WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_LGRP_ADB_ACT WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_LED_TBL WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_LED_GRP_TBL WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BU_LED_COMB_TBL WHERE BUSINESS_UNIT = ‘US001’;
DELETE FROM PS_BUL_CNTL_BUD WHERE BUSINESS_UNIT = ‘US001’;

Sort using XLATLONGNAME instead of Translate

June 15th, 2010 by Tyson No comments »

I have a question. Hope you can help 🙂

Need help to sort using XLATLONGNAME. Currently we have a search record that returns results in a grid (on the search page) Results are displayed in 4 columns. One of the columns has a translate value. So the sort is based on the translate value. We want it to be by the XLATLONGNAME. Is that possible.

Example (say we have the following)

Translate Value – Long Name
A – Backlog
B – Available
C – Contract
D – Entitled
E – Dispatch

We do NOT want to change the translate value order. But want to use the XLATLONGNAME for the search sort.

Currently when the results are displayed, the sort is by Translate value. Say if the field 2 is the one with the above translate value, then the results show up as

Field 1 :: Field 2
Backlog :: XXXXX
Available:: XXXXX
Contract:: XXXXX
Entitled :: XXXXX
Dispatch:: XXXXX
…this is cause its sorted by Translate value.

Need this to be as follows ..
Field1 :: Field 2
Available:: XXXXX
Backlog :: XXXXX
Contract:: XXXXX
Dispatch:: XXXXX
Entitled :: XXXXX

The above set is what is required – This is by XLATLONGNAME.

Thank you,
Jessica

Jessica,

You’ll likely have to create a new search record. It should be a view of the current search record joined to the XLAT item. You can then select the long name and sort on it that way.

-Tyson

Auditing Journal Post in PS9.0

February 11th, 2010 by Tyson No comments »

In every GL implementation I’ve been involved with, auditors or managers always ask for the ability to audit who posted their own journal entries. Unfortunately PeopleSoft doesn’t provide the means to do this very easily, so I’ve written a couple of queries that you can use to build a report or a view that satisfies this requirement.

The first query is only valid if you’ve disallowed users from On-Demand Posting journals (the “Journal Post” option in the drop-down box on the JE Lines screen). If you disable this process, users have to utilize the batch journal post process. Here is the SQL:

SELECT A.OPRID, B.BUSINESS_UNIT, B.JOURNAL_ID, B.JOURNAL_DATE, B.DESCR 
	FROM PS_JRNL_POST_REQ A, PS_JRNL_HEADER B 
	WHERE A.OPRID = B.OPRID 
		AND B.BUSINESS_UNIT = A.BUSINESS_UNIT;

The second query is valid if you haven’t restricted On-Demand Posting of journal entries. It’s a bit tricky, so stay with me:

SELECT A.BUSINESS_UNIT, A.JOURNAL_ID, A.JOURNAL_DATE, A.DESCR, A.OPRID, 
	A.POSTED_DATE, A.PROCESS_INSTANCE, B.JOBID, B.PROGRAM_NAME, 
	C.MESSAGE_PARM
  FROM PS_JRNL_HEADER A, PS_MESSAGE_LOG B, PS_MESSAGE_LOGPARM C
  WHERE B.PROCESS_INSTANCE = A.PROCESS_INSTANCE
    AND B.PROCESS_INSTANCE = C.PROCESS_INSTANCE
    AND B.MESSAGE_SEQ = C.MESSAGE_SEQ
    AND C.MESSAGE_SEQ = 2
    AND C.PARM_SEQ = 2
    AND A.OPRID = C.MESSAGE_PARM
    AND B.PROGRAM_NAME LIKE 'GLPP%'
  ORDER BY A.BUSINESS_UNIT, A.JOURNAL_ID, A.JOURNAL_DATE;

I’ve heard that PeopleSoft 9.1 provides this ability as delivered, but haven’t seen it yet. I’ll update this post once I verify the feature is available. Happy querying!