[Date Prev][Date Next][Thread Prev][Thread Next]
[Author Index]
[Date Index]
[Thread Index]
[SQR-USERS Info]
[SQRUG Home Page]
RE: [sqr-users] File Help
Sorry, Melissa, I didn't notice the JR on PS_JOB_JR. But you can avoid
POS2, the second copy of PS_POSITION_DATA. Instead of writing:
AND POS2.POSITION_NBR = JOB.REPORTS_TO
AND JOB2.POSITION_NBR = POS2.POSITION_NBR
you could write:
AND JOB2.POSITION_NBR = JOB.REPORTS_TO
As for your question about SETID, this is a Peoplesoft feature that allows
you to easily make different sets of rows applicable to different employees.
It's used in PS_DEPT_TBL, PS_JOBCODE_TBL, PS_LOCATION_TBL, and over 400
other prompt tables. Suppose you have a large organization with different
divisions. All the departments in one division might have one SETID value,
all the departments in another division have a different SETID value. Then,
once you've entered the SETID_DEPT on the Job Data page, the search for
eligible departments would be limited to those with that SETID.
-----Original Message-----
From: m rapson [mailto:mrapson@hotmail.com]
Sent: Thursday, July 31, 2003 9:50 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] File Help
Job Jr is needed to grab a field to tell whether a user works from home
Job is used a second time so I can join it back to position to get the
Reports To Position number
position is used twice to join the position number back to an employee and
the supervisor(JOB1 and Job2)
That is the reason for duplicating the tables. Effective dating I didn't
even think about. We have only been working with PS for 5 months to get HR
up and running(HR info and Payroll) while converting over legacy systems.
I will update with eft dting and look at the rest.
Thanks for your help!
Melissa
----Original Message Follows----
From: "Alexander, Steve" <Steve.Alexander@ci.sj.ca.us>
Reply-To: sqr-users@sqrug.org
To: "'sqr-users@sqrug.org'" <sqr-users@sqrug.org>
Subject: RE: [sqr-users] File Help
Date: Thu, 31 Jul 2003 09:34:27 -0700
Also, you have three copies of PS_JOB, two copies of PS_PERSONAL_DATA, and
two copies of PS_NAME. Why are you doing that? Even if you don't use
fields from the redundant copies, they can cause you to multiply your
records if, for instance, there are more than one employee with the same
POSITION_NBR.
You should probably get FULL_PART_TIME and REG_TEMP from PS_JOB, not
PS_POSITION_DATA. The values in PS_POSITION_DATA are the defaults for that
position. The values in PS_JOB are either the same, or may override those
defaults. You could save a lot of work, time, complication, and perhaps row
duplication by removing PS_POSITION_DATA from the query.
-----Original Message-----
From: Knapp, Richard [mailto:KnappR@umsystem.edu]
Sent: Thursday, July 31, 2003 9:10 AM
To: sqr-users@sqrug.org
Subject: RE: [sqr-users] File Help
I don't see anything about effective dating. That could bump up your record
count.
Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
-----Original Message-----
From: m rapson [mailto:mrapson@hotmail.com]
Sent: Thursday, July 31, 2003 10:51 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] File Help
Hello All! This is the first SQR I have written. I am outputting to a file
employee information. The sqr has 2 procedures, one pulls information about
active employees and the second pulls information about terminated
employees. The file should only contain around 1200 records but is
returning about 40000 or so. Not really sure where to look so I thought I
would post this and see if someone can help.
Much appreciated
Mel
#include 'setenv.sqc' !Set environment
#Include 'setup07.sqc' !Printer and page-size initialization
begin-REPORT
do Init-DateTime
do Get-Current-DateTime
do Stdapi-Init
display 'Building Interface File for Employee Website'
do Open-file
do MAIN
do close
do Commit-Transaction
display 'Finished Building Interface File for Employee Website'
end-REPORT
!************************************************************************
begin-Procedure Open-file
LET $OUTFILE = 'c:\ep_empwebsite.csv'
open $OUTFILE as 1 for-writing record = 200
let $csv = ','
end-procedure
!************************************************************************
begin-procedure close
close 1
end-procedure
!************************************************************************
begin-PROCEDURE MAIN
begin-SELECT distinct
EMP.EMPLID
JOB.EMPL_STATUS
NAM.LAST_NAME
NAM.FIRST_NAME
NAM.PREF_FIRST_NAME
JOB.DEPTID
DEPT.DESCR
JOBJR.SUPV_LVL_ID
JOB.GL_PAY_TYPE
EMP.BUSINESS_TITLE
LOC.ADDRESS1
LOC.ADDRESS2
LOC.CITY
LOC.STATE
LOC.POSTAL
LOC.COUNTRY
PHN.PHONE
POS.REG_TEMP
POS.FULL_PART_TIME
NAM2.NAME
LET $GL_PAY_TYPE = SUBSTR(&JOB.GL_PAY_TYPE, 0, 1)
IF &POS.REG_TEMP = 'T'
let $EMPL_STATUS = 'Temp'
ELSE
IF &POS.FULL_PART_TIME = 'F'
let $EMPL_STATUS = 'FT'
ELSE
let $EMPL_STATUS = 'PT'
END-IF
END-IF
IF &JOB.EMPL_STATUS = 'T'
let $TERM_FLAG = 'yes'
ELSE
let $TERM_FLAG = ' '
END-IF
LET $EMPLID = &EMP.EMPLID
LET $LASTNAME = &NAM.LAST_NAME
LET $FIRSTNAME = &NAM.FIRST_NAME
LET $PREFNAME = &NAM.PREF_FIRST_NAME
LET $DEPTID = substr(&JOB.DEPTID, 2, 3)
LET $WORKAT = &JOBJR.SUPV_LVL_ID
LET $DIVISION = substr(&JOB.GL_PAY_TYPE, 1, 1)
EVALUATE $DIVISION
WHEN = '1'
LET $DIVISIONDESCR = 'Local Sales'
WHEN = '2'
LET $DIVISIONDESCR = 'Sales Administration
Support'
WHEN = '3'
LET $DIVISIONDESCR = 'Duncan'
WHEN = '4'
LET $DIVISIONDESCR = 'Colorado Springs'
WHEN = '5'
LET $DIVISIONDESCR = 'Partnership Marketing'
WHEN = '6'
LET $DIVISIONDESCR = 'Headquarters'
WHEN = '7'
LET $DIVISIONDESCR = 'Sally Foster'
WHEN-OTHER
BREAK
END-EVALUATE
LET $TITLE = &EMP.BUSINESS_TITLE
LET $ADDRESS1 = &LOC.ADDRESS1
LET $ADDRESS2 =&LOC.ADDRESS2
LET $CITY = &LOC.CITY
LET $STATE = &LOC.STATE
LET $ZIPCODE = &LOC.POSTAL
LET $COUNTRY = &LOC.COUNTRY
LET $PHONE = &PHN.PHONE
LET $REGTEMP = &POS.REG_TEMP
LET $FULLPART = &POS.FULL_PART_TIME
LET $SUPERVISOR = &NAM2.NAME
DO WRITE-FILE
DO TERMINATE
FROM SYSADM.PS_LOCATION_TBL LOC,
SYSADM.PS_DEPT_TBL DEPT,
SYSADM.PS_PERSONAL_PHONE PHN,
SYSADM.PS_POSITION_DATA POS2,
SYSADM.PS_POSITION_DATA POS,
SYSADM.PS_NAMES NAM2,
SYSADM.PS_NAMES NAM,
SYSADM.PS_JOB JOB2,
SYSADM.PS_JOB_JR JOBJR,
SYSADM.PS_JOB JOB,
SYSADM.PS_EMPLOYMENT EMP
WHERE EMP.EMPLID = JOB.EMPLID
AND JOB.EMPL_STATUS in ('A', 'L', 'T', 'S')
AND JOBJR.EMPLID = JOB.EMPLID
AND POS.POSITION_NBR = JOB.POSITION_NBR
AND POS2.POSITION_NBR = JOB.REPORTS_TO
AND JOB2.POSITION_NBR = POS2.POSITION_NBR
AND NAM.EMPLID = EMP.EMPLID
AND NAM.NAME_TYPE = 'PRI'
AND NAM2.Emplid = JOB2.EMPLID
AND NAM2.NAME_TYPE = 'PRI'
AND PHN.EMPLID(+) = EMP.EMPLID
AND PHN.PHONE_TYPE(+) = 'BUSN'
AND LOC.LOCATION(+) = JOB.LOCATION
AND DEPT.DEPTID = JOB.DEPTID
end-SELECT
end-PROCEDURE
! ****** This will pull terminated employees
begin-PROCEDURE TERMINATE
begin-SELECT distinct
TEMP.EMPLID
TJOB.EMPL_STATUS
TNAM.LAST_NAME
TNAM.FIRST_NAME
TNAM.PREF_FIRST_NAME
TJOB.DEPTID
TDEPT.DESCR
TJOBJR.SUPV_LVL_ID
TJOB.GL_PAY_TYPE
TEMP.BUSINESS_TITLE
TLOC.ADDRESS1
TLOC.ADDRESS2
TLOC.CITY
TLOC.STATE
TLOC.POSTAL
TLOC.COUNTRY
TPHN.PHONE
TPOS.REG_TEMP
TPOS.FULL_PART_TIME
TNAM2.NAME
LET $GL_PAY_TYPE = SUBSTR(&TJOB.GL_PAY_TYPE, 0, 1)
LET $EMPL_STATUS = ''
IF &TJOB.EMPL_STATUS = 'T'
let $TERM_FLAG = 'yes'
ELSE
let $TERM_FLAG = ' '
END-IF
LET $EMPLID = &TEMP.EMPLID
LET $LASTNAME = &TNAM.LAST_NAME
LET $FIRSTNAME = &TNAM.FIRST_NAME
LET $PREFNAME = &TNAM.PREF_FIRST_NAME
LET $DEPTID = substr(&TJOB.DEPTID, 2, 3)
LET $WORKAT = &TJOBJR.SUPV_LVL_ID
LET $DIVISION = substr(&TJOB.GL_PAY_TYPE, 1, 1)
EVALUATE $DIVISION
WHEN = '1'
LET $DIVISIONDESCR = 'Local Sales'
WHEN = '2'
LET $DIVISIONDESCR = 'Sales Administration Support'
WHEN = '3'
LET $DIVISIONDESCR = 'Duncan'
WHEN = '4'
LET $DIVISIONDESCR = 'Colorado Springs'
WHEN = '5'
LET $DIVISIONDESCR = 'Partnership Marketing'
WHEN = '6'
LET $DIVISIONDESCR = 'Headquarters'
WHEN = '7'
LET $DIVISIONDESCR = 'Sally Foster'
WHEN-OTHER
BREAK
END-EVALUATE
LET $TITLE = &TEMP.BUSINESS_TITLE
LET $ADDRESS1 = &TLOC.ADDRESS1
LET $ADDRESS2 =&TLOC.ADDRESS2
LET $CITY = &TLOC.CITY
LET $STATE = &TLOC.STATE
LET $ZIPCODE = &TLOC.POSTAL
LET $COUNTRY = &TLOC.COUNTRY
LET $PHONE = &TPHN.PHONE
LET $REGTEMP = &TPOS.REG_TEMP
LET $FULLPART = &TPOS.FULL_PART_TIME
LET $SUPERVISOR = &TNAM2.NAME
DO WRITE-FILE
FROM SYSADM.PS_LOCATION_TBL TLOC,
SYSADM.PS_DEPT_TBL TDEPT,
SYSADM.PS_PERSONAL_PHONE TPHN,
SYSADM.PS_POSITION_DATA TPOS2,
SYSADM.PS_POSITION_DATA TPOS,
SYSADM.PS_NAMES TNAM2,
SYSADM.PS_NAMES TNAM,
SYSADM.PS_JOB TJOB2,
SYSADM.PS_JOB_JR TJOBJR,
SYSADM.PS_JOB TJOB,
SYSADM.PS_EMPLOYMENT TEMP
WHERE TEMP.EMPLID = TJOB.EMPLID
AND TJOB.EMPL_STATUS in ('T', 'S')
AND TNAM.EMPLID = TEMP.EMPLID
AND TNAM.NAME_TYPE = 'PRI'
AND TJOBJR.EMPLID(+) = TJOB.EMPLID
AND TPOS.POSITION_NBR(+) = TJOB.POSITION_NBR
AND TPOS2.POSITION_NBR(+) = TJOB.REPORTS_TO
AND TJOB2.POSITION_NBR(+) = TPOS2.POSITION_NBR
AND TNAM2.Emplid(+) = TJOB2.EMPLID
AND TNAM2.NAME_TYPE(+) = 'PRI'
AND TPHN.EMPLID(+) = TEMP.EMPLID
AND TPHN.PHONE_TYPE(+) = 'BUSN'
AND TLOC.LOCATION(+) = TJOB.LOCATION
AND TDEPT.DEPTID(+) = TJOB.DEPTID
end-SELECT
end-PROCEDURE
!**********************************************************************
begin-Procedure Write-File
write 1 from
$TERM_FLAG
$csv
$EMPLID
$csv
$LASTNAME
$csv
$FIRSTNAME
$csv
$PREFNAME
$csv
$EMPL_STATUS
$csv
$DEPTID
$csv
$WORKAT
$csv
$GL_PAY_TYPE
$csv
$DIVISIONDESCR
$csv
$TITLE
$csv
$ADDRESS1
$csv
$ADDRESS2
$csv
$CITY
$csv
$STATE
$csv
$ZIPCODE
$csv
$COUNTRY
$csv
$PHONE
$csv
$SUPERVISOR
end-PROCEDURE
!************************************************************************
#include 'askaod.sqc' !Ask As Of Date input
#Include 'datetime.sqc' !Routine for Date and Time formatting
#Include 'curdttim.sqc' !Get-Current-DateTime procedure
#Include 'ldorgsb.sqc' !Load Orgcode Symbols procedure
#Include 'getorgsb.sqc' !Get Orgcode Symbol procedure
#Include 'tranctrl.sqc' !Common Transaction Handling
#Include 'sqrtrans.sqc' !sql error procedure
#include 'prcsapi.sqc'
#include 'prcsdef.sqc'
#include 'stdapi.sqc' !Routine to Update Run Status
_________________________________________________________________
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*.
http://join.msn.com/?page=features/virus
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users
_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users