accasey’s gists

accasey / insert_for_a_record.sql

Created February 28, 2018 15:28

Using a record as input generate the column list needed for the insert statement

	SELECT Y.RECNAME, LTRIM(MAX(SYS_CONNECT_BY_PATH(Y.FIELDNAME ,',')) KEEP (DENSE_RANK LAST ORDER BY Y.CURR_FIELD) ,',') AS FIELDNAMES
	FROM (
	SELECT Z.RECNAME
	,Z.FIELDNAME
	,ROW_NUMBER() OVER (PARTITION BY Z.RECNAME ORDER BY Z.FIELDNUM) AS CURR_FIELD
	,ROW_NUMBER() OVER (PARTITION BY Z.RECNAME ORDER BY Z.FIELDNUM) - 1 AS PREV_FIELD
	FROM PSRECFIELDDB Z
	WHERE 1 = 1
	AND Z.RECNAME='PSAPMSGDOMSTAT') Y
	GROUP BY Y.RECNAME

accasey / refresh_set_ib_domains.sql

Last active February 28, 2018 14:43

After an environment refresh set the IB domain to active and load the dispatchers

	TRUNCATE TABLE PSAPMSGDOMSTAT
	/
	TRUNCATE TABLE PSAPMSGDSPSTAT
	/
	INSERT INTO PSAPMSGDOMSTAT (MACHINENAME,APPSERVER_PATH,DOMAIN_STATUS,IBFAILOVERPRIORITY,IBFAILOVERGROUP,IB_SLAVEMODE,IB_LOADBALANCE,IB_SERVERURL,IB_DOMAIN_POOLING)
	VALUES (UPPER('MYSERVERNAME'),'PATH TO DOMAIN FOLDER', 'A', 0, 0, 0, 1, ' ', 0)
	/
	INSERT INTO PSAPMSGDSPSTAT (DISPATCHERNAME,MACHINENAME,APPSERVER_PATH,DSPSTATUS,STATUSSTRING,IB_SLAVEMODE,DSPRESET,CLEANUP_DTTM,DSPHEALTHDTTM)
	VALUES ('PSPUBDSP_dflt', UPPER('MYSERVERNAME'), 'PATH TO DOMAIN FOLDER', 0, 'ACT', 0, 0, NULL, CAST(SYSTIMESTAMP AS TIMESTAMP))
	/

accasey / update_gp_tables.sql

Created February 16, 2018 14:55

Sanitize global payroll tables

	UPDATE PS_GP_RSLT_ACUM SET CALC_RSLT_VAL = 0, CALC_VAL = 0
	/
	UPDATE PS_GP_RSLT_DELTA SET CALC_DELTA_VAL = 0,UNIT_DELTA_VAL = 0, BASE_DELTA_VAL = 0
	/
	UPDATE PS_GP_RSLT_ERN_DED SET CALC_RSLT_VAL = 0, BASE_RSLT_VAL = 0, RATE_RSLT_VAL = 0, UNIT_RSLT_VAL = 0, RSLT_XFER_VAL = 0
	/
	UPDATE PS_GP_RSLT_PIN SET CALC_RSLT_VAL = 0
	/
	UPDATE PS_GP_RSLT_PI_DATA SET GP_AMT = 0, GP_PCT = 0, GP_BASE = 0, GP_UNIT = 0
	/

accasey / update_comp_rate.sql

Created February 16, 2018 14:51

Update compensation

	UPDATE PS_COMPENSATION SET COMPRATE = 1000.00, CONVERT_COMPRT = 1000, CHANGE_AMT = 100
	/
	UPDATE PS_JOB SET COMPRATE = 1000, CHANGE_AMT = 1, CHANGE_PCT =1 , ANNUAL_RT = 1000, MONTHLY_RT = 100, DAILY_RT = 10, HOURLY_RT = 1
	/

accasey / update_eperf.sql

Created February 16, 2018 14:49

Sanitize ePerformance

	UPDATE PS_EP_APPR_SECTION SET EP_RESULTS='Data has been cleared.'
	/
	UPDATE PS_EP_APPR_ITEM SET EP_RESULTS='Data has been cleared.'
	/
	UPDATE PS_EP_APPR_ITEM SET REVIEW_RATING = 2
	/
	UPDATE PS_EP_APPR_ITEM SET REVIEW_POINTS = 2
	/

accasey / update_vndr_bank_acct.sql

Created February 16, 2018 14:44

Reset vendor bank accounts

	UPDATE PS_VNDR_BANK_ACCT
	SET BANK_ACCT_TYPE = '03'
	,BANK_ACCOUNT_NUM = 'xxxxxxxxxxxxx'
	,DFI_ID_QUAL = 'XX'
	,IBAN_ID = 'XXXXXXXXXXXXXXXXXXXXXX'
	,BENEFICIARY_BANK = 'A BANK'
	,BANK_ID_QUAL = 'xxx'
	,BNK_ID_NBR = 'xxxxxx'
	,DFI_ID_NUM = 'xxxxxxxxxxx'
	,COUNTRY = 'ABC'

accasey / summarize_ledger.sql

Last active February 16, 2018 14:46

Roll up sensitive accounts

Andrew accasey