Wednesday, December 29, 2010

AppEngine:%bind()_Dynamic Where Clause :Single Quote Problem

I've requirement to prepare dynamic where cluses based on setup.

In Setup: PLAN_TYPE values are stored like this as string : '10','11','15'.

This string  needs to inserted into AET ; In SQL Action, passed value using %bind().
eg: In AET
SR_TEST_AET.DESCR = '10','11','15'

SQL Action:
Select * from PS_SR_TEST_REC where plan_type in (%bind(DESC)).

Internally this sql is expanding differently as below : 


Select * from PS_SR_TEST_REC where plan_type in (''''10'',''11'',''15'''') -  due to this i faced lot of problem.

Solution: to insert dynamic clasue:                                                                                                                 

Select * from PS_SR_TEST_REC where plan_type in (%bind(DESC),NOQUOTES);
 it solves my problem & it expands as required

Select * from PS_SR_TEST_REC where plan_type in ( '10','11','15').


^_^

Monday, December 27, 2010

Peopletool 8.51: Peoplesoft Test Framework

with release of  8.51 peoplesoft comes with PS test framework (PTF) with record & playback.

PTF is located under /setup/PsTestFramework/setup.exe.

learn more follow the links:


 
 :-)

Saturday, December 25, 2010

Benefit Administration

Primary Ben Admin Tables:-

•BAS_PARTIC - Stores information about a specific event.
•BAS_PARTIC_PLAN - Stores data about each plan type for an event.
•BAS_PARTIC_OPTN - Stores data about the options available.
•BAS_PARTIC_COST - Stores costs for each option.
•BAS_PARTIC_INVT  - Stores investment options by plan type
•BAS_PARTIC_DPND - Stores dependent information by plan type


To find the recent status of Open Enrollment
----------------------------------
SELECT BAS_PROCESS_STATUS,COUNT(*) FROM PS_BAS_PARTIC WHERE SCHED_ID = ‘XX’ GROUP BY BAS_PROCESS_STATUS

To identify who is eligible to make different plan (healh, saving, etc) for recent event maintainence.

SELECT A.EMPLID FROM PS_BAS_PARTIC A, PS_BAS_PARTIC_PLAN B WHERE A.SCHED_ID = B.SCHED_ID AND A.EMPLID = B.EMPLID AND A.BENEFIT_RCD_NBR = B.BENEFIT_RCD_NBR AND A.EVENT_ID = B.EVENT_ID AND B.PLAN_TYPE = '20‘  /*different plans*/
AND B.ELECT_ALLOWED= 'Y‘ AND A.EVENT_DT > 'XX/XX/XXXX' /*event date*/.


Source : Web Source (OHUG).

Friday, December 24, 2010

Peoplesoft Base Benefit Tables

Peoplesoft Base Benefit Tables:

PS_BAS_PARTIC, PS_BAS_PARTIC_COST, PS_BAS_PARTIC_DPND, PS_BAS_PARTIC_INVT, PS_BAS_PARTIC_OPTN, PS_BAS_PARTIC_PLAN, PS_BENEF_COMMENT, PS_BEN_PROG_PARTIC, PS_DEPENDENT_BENEF, PS_DISABILITY_BEN, PS_FSA_BENEFIT, PS_FSA_PAYMENT, PS_HEALTH_BENEFIT, PS_HEALTH_DEPENDNT, PS_LEAVE_ACCRUAL, PS_LEAVE_PLAN, PS_LIFE_ADD_BEN, PS_LIFE_ADD_BENEFC, PS_PENSION_BENEFC, PS_PENSION_PLAN, PS_RTRMNT_PLAN, PS_SAVINGS_BENEFIC, PS_SAVINGS_INVEST, PS_SAVINGS_PLAN, PS_VACATION_BEN

Peoplesoft NA Payroll Tables

Peoplesoft NA Payroll Table List:

PS_STATE_TAX_DATA, PS_GENL_DEDUCTION, PS_BAL_ADJ_UI_CAN, PS_CAN_CHECK_YTD, PS_CAN_DED_BALANCE, PS_CAN_ERN_BALANCE, PS_CAN_TAX_BALANCE, PS_CAN_TAX_DATA, PS_CHECK_YTD, PS_DEDUCTION_BAL, PS_EARNINGS_BAL, PS_FED_TAX_DATA, PS_TAX_BALANCE, PS_LOCAL_TAX_DATA, PS_BAL_ADJ_ARR, PS_BAL_ADJ_CHK, PS_BAL_ADJ_CN_CHK, PS_BAL_ADJ_CN_DED, PS_BAL_ADJ_CN_ERN, PS_BAL_ADJ_CN_TAX, PS_BAL_ADJ_DED, PS_BAL_ADJ_ERN, PS_BAL_ADJ_GRN, PS_BAL_ADJ_TAX, PS_DED_ARREARS, PS_GARN_RULE, PS_GARN_SCHED, PS_GARN_SPEC, PS_GENL_DED_CD,PS_PAY_CHECK, PS_PAY_DEDUCTION

Peoplesoft HR/HCM Tables

List of Peoplesoft HCM Tables.

PS_EMPLOYEES, PS_JOB,PS_PERSONAL_DATA, PS_EMPLOYEES_LNG, PS_EMPLOYMENT, PS_EMPLOYMENT_LNG, PS_EMPLOYMENT_LNG1, PS_EMPLOYMENT_LNG2, PS_EMPLOYMENT_LNG3, PS_HS_EMPLOYMENT, PS_HS_EMPL_REL_CAN, PS_HS_EMPL_REL_LNG, PS_HS_EMPL_SUB_CAN, PS_HS_EMPL_SUB_LNG, PS_INJ_CLMADDR_CAN, PS_JOB, PS_PERSONL_DTA_LNG, PS_PERS_DATA_AET, PS_PERS_DATA_EFFDT, PS_PERS_DTAEFF_LNG, PS_PERS_NID, PS_JOB_EARNS_DIST, PS_JOB_APPROVALS, PS_BEN_PROG_PARTIC