[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE INSERT_ROW(P_ROWID IN OUT NOCOPY VARCHAR2,
P_RATE_ID IN OUT NOCOPY NUMBER,
P_BUSINESS_GROUP_ID NUMBER,
P_PARENT_SPINE_ID NUMBER,
P_NAME VARCHAR2,
P_RATE_TYPE VARCHAR2,
P_RATE_UOM VARCHAR2,
P_COMMENTS VARCHAR2,
P_REQUEST_ID NUMBER,
P_PROGRAM_APPLICATION_ID NUMBER,
P_PROGRAM_ID NUMBER,
P_PROGRAM_UPDATE_DATE DATE,
P_ATTRIBUTE_CATEGORY VARCHAR2,
P_ATTRIBUTE1 VARCHAR2,
P_ATTRIBUTE2 VARCHAR2,
P_ATTRIBUTE3 VARCHAR2,
P_ATTRIBUTE4 VARCHAR2,
P_ATTRIBUTE5 VARCHAR2,
P_ATTRIBUTE6 VARCHAR2,
P_ATTRIBUTE7 VARCHAR2,
P_ATTRIBUTE8 VARCHAR2,
P_ATTRIBUTE9 VARCHAR2,
P_ATTRIBUTE10 VARCHAR2,
P_ATTRIBUTE11 VARCHAR2,
P_ATTRIBUTE12 VARCHAR2,
P_ATTRIBUTE13 VARCHAR2,
P_ATTRIBUTE14 VARCHAR2,
P_ATTRIBUTE15 VARCHAR2,
P_ATTRIBUTE16 VARCHAR2,
P_ATTRIBUTE17 VARCHAR2,
P_ATTRIBUTE18 VARCHAR2,
P_ATTRIBUTE19 VARCHAR2,
P_ATTRIBUTE20 VARCHAR2,
P_RATE_BASIS VARCHAR2) IS
CURSOR c1 IS
SELECT PAY_RATES_S.NEXTVAL
FROM SYS.DUAL;
SELECT rowid
FROM PAY_RATES
WHERE RATE_ID = P_RATE_ID;
INSERT INTO PAY_RATES (RATE_ID, BUSINESS_GROUP_ID, PARENT_SPINE_ID,
NAME, RATE_TYPE, RATE_UOM, COMMENTS, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID,
PROGRAM_UPDATE_DATE, ATTRIBUTE_CATEGORY,
ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4,
ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16,
ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20,
RATE_BASIS)
VALUES (P_RATE_ID, P_BUSINESS_GROUP_ID, P_PARENT_SPINE_ID,
P_NAME, P_RATE_TYPE, P_RATE_UOM, P_COMMENTS, P_REQUEST_ID,
P_PROGRAM_APPLICATION_ID, P_PROGRAM_ID,
P_PROGRAM_UPDATE_DATE, P_ATTRIBUTE_CATEGORY,
P_ATTRIBUTE1, P_ATTRIBUTE2, P_ATTRIBUTE3, P_ATTRIBUTE4,
P_ATTRIBUTE5, P_ATTRIBUTE6, P_ATTRIBUTE7, P_ATTRIBUTE8,
P_ATTRIBUTE9, P_ATTRIBUTE10, P_ATTRIBUTE11, P_ATTRIBUTE12,
P_ATTRIBUTE13, P_ATTRIBUTE14, P_ATTRIBUTE15, P_ATTRIBUTE16,
P_ATTRIBUTE17, P_ATTRIBUTE18, P_ATTRIBUTE19, P_ATTRIBUTE20,
P_RATE_BASIS);
/* calling database package to insert database item */
hrdyndbi.create_grade_spine_dict(P_RATE_ID);
END INSERT_ROW;
PROCEDURE UPDATE_ROW(P_ROWID VARCHAR2,
P_RATE_ID NUMBER,
P_BUSINESS_GROUP_ID NUMBER,
P_PARENT_SPINE_ID NUMBER,
P_NAME VARCHAR2,
P_RATE_TYPE VARCHAR2,
P_RATE_UOM VARCHAR2,
P_COMMENTS VARCHAR2,
P_REQUEST_ID NUMBER,
P_PROGRAM_APPLICATION_ID NUMBER,
P_PROGRAM_ID NUMBER,
P_PROGRAM_UPDATE_DATE DATE,
P_ATTRIBUTE_CATEGORY VARCHAR2,
P_ATTRIBUTE1 VARCHAR2,
P_ATTRIBUTE2 VARCHAR2,
P_ATTRIBUTE3 VARCHAR2,
P_ATTRIBUTE4 VARCHAR2,
P_ATTRIBUTE5 VARCHAR2,
P_ATTRIBUTE6 VARCHAR2,
P_ATTRIBUTE7 VARCHAR2,
P_ATTRIBUTE8 VARCHAR2,
P_ATTRIBUTE9 VARCHAR2,
P_ATTRIBUTE10 VARCHAR2,
P_ATTRIBUTE11 VARCHAR2,
P_ATTRIBUTE12 VARCHAR2,
P_ATTRIBUTE13 VARCHAR2,
P_ATTRIBUTE14 VARCHAR2,
P_ATTRIBUTE15 VARCHAR2,
P_ATTRIBUTE16 VARCHAR2,
P_ATTRIBUTE17 VARCHAR2,
P_ATTRIBUTE18 VARCHAR2,
P_ATTRIBUTE19 VARCHAR2,
P_ATTRIBUTE20 VARCHAR2,
P_RATE_BASIS VARCHAR2) IS
BEGIN
UPDATE PAY_RATES
SET RATE_ID = P_RATE_ID ,
BUSINESS_GROUP_ID = P_BUSINESS_GROUP_ID ,
PARENT_SPINE_ID = P_PARENT_SPINE_ID ,
NAME = P_NAME ,
RATE_TYPE = P_RATE_TYPE ,
RATE_UOM = P_RATE_UOM ,
COMMENTS = P_COMMENTS ,
REQUEST_ID = P_REQUEST_ID ,
PROGRAM_APPLICATION_ID = P_PROGRAM_APPLICATION_ID ,
PROGRAM_ID = P_PROGRAM_ID ,
PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE ,
ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY ,
ATTRIBUTE1 = P_ATTRIBUTE1 ,
ATTRIBUTE2 = P_ATTRIBUTE2 ,
ATTRIBUTE3 = P_ATTRIBUTE3 ,
ATTRIBUTE4 = P_ATTRIBUTE4 ,
ATTRIBUTE5 = P_ATTRIBUTE5 ,
ATTRIBUTE6 = P_ATTRIBUTE6 ,
ATTRIBUTE7 = P_ATTRIBUTE7 ,
ATTRIBUTE8 = P_ATTRIBUTE8 ,
ATTRIBUTE9 = P_ATTRIBUTE9 ,
ATTRIBUTE10 = P_ATTRIBUTE10 ,
ATTRIBUTE11 = P_ATTRIBUTE11 ,
ATTRIBUTE12 = P_ATTRIBUTE12 ,
ATTRIBUTE13 = P_ATTRIBUTE13 ,
ATTRIBUTE14 = P_ATTRIBUTE14 ,
ATTRIBUTE15 = P_ATTRIBUTE15 ,
ATTRIBUTE16 = P_ATTRIBUTE16 ,
ATTRIBUTE17 = P_ATTRIBUTE17 ,
ATTRIBUTE18 = P_ATTRIBUTE18 ,
ATTRIBUTE19 = P_ATTRIBUTE19 ,
ATTRIBUTE20 = P_ATTRIBUTE20 ,
RATE_BASIS = P_RATE_BASIS
WHERE ROWID = P_ROWID;
/* calling database package to first delete the existing database item
and then call the database package to insert the updated database item */
hrdyndbi.delete_grade_spine_dict(P_RATE_ID);
END UPDATE_ROW;
PROCEDURE DELETE_ROW(P_ROWID VARCHAR2,P_RATE_ID NUMBER,P_CHILD VARCHAR2) IS
BEGIN
IF P_CHILD = 'Y'
THEN
/* delete any children if they exist */
DELETE FROM PAY_GRADE_RULES_F
WHERE RATE_ID = P_RATE_ID;
/* now delete the master */
DELETE FROM PAY_RATES WHERE PAY_RATES.ROWID = P_ROWID;
/* calling database package to delete database item */
hrdyndbi.delete_grade_spine_dict(P_RATE_ID);
END DELETE_ROW;
P_PROGRAM_UPDATE_DATE DATE,
P_ATTRIBUTE_CATEGORY VARCHAR2,
P_ATTRIBUTE1 VARCHAR2,
P_ATTRIBUTE2 VARCHAR2,
P_ATTRIBUTE3 VARCHAR2,
P_ATTRIBUTE4 VARCHAR2,
P_ATTRIBUTE5 VARCHAR2,
P_ATTRIBUTE6 VARCHAR2,
P_ATTRIBUTE7 VARCHAR2,
P_ATTRIBUTE8 VARCHAR2,
P_ATTRIBUTE9 VARCHAR2,
P_ATTRIBUTE10 VARCHAR2,
P_ATTRIBUTE11 VARCHAR2,
P_ATTRIBUTE12 VARCHAR2,
P_ATTRIBUTE13 VARCHAR2,
P_ATTRIBUTE14 VARCHAR2,
P_ATTRIBUTE15 VARCHAR2,
P_ATTRIBUTE16 VARCHAR2,
P_ATTRIBUTE17 VARCHAR2,
P_ATTRIBUTE18 VARCHAR2,
P_ATTRIBUTE19 VARCHAR2,
P_ATTRIBUTE20 VARCHAR2,
P_CHILD_EXIST VARCHAR2,
P_MODE VARCHAR2,
P_RATE_BASIS VARCHAR2) IS
CURSOR C IS SELECT * FROM PAY_RATES WHERE ROWID = P_ROWID
FOR UPDATE OF RATE_ID NOWAIT;
CURSOR C2 IS SELECT * FROM PAY_GRADE_RULES_F WHERE RATE_ID = P_RATE_ID
FOR UPDATE OF GRADE_RULE_ID NOWAIT;
AND((RECINFO.PROGRAM_UPDATE_DATE = P_PROGRAM_UPDATE_DATE)
OR(RECINFO.PROGRAM_UPDATE_DATE IS NULL AND P_PROGRAM_UPDATE_DATE IS NULL))
AND((RECINFO.ATTRIBUTE_CATEGORY = P_ATTRIBUTE_CATEGORY)
OR(RECINFO.ATTRIBUTE_CATEGORY IS NULL AND P_ATTRIBUTE_CATEGORY IS NULL))
AND((RECINFO.ATTRIBUTE1 = P_ATTRIBUTE1)
OR(RECINFO.ATTRIBUTE1 IS NULL AND P_ATTRIBUTE1 IS NULL))
AND((RECINFO.ATTRIBUTE2 = P_ATTRIBUTE2)
OR(RECINFO.ATTRIBUTE2 IS NULL AND P_ATTRIBUTE2 IS NULL))
AND((RECINFO.ATTRIBUTE3 = P_ATTRIBUTE3)
OR(RECINFO.ATTRIBUTE3 IS NULL AND P_ATTRIBUTE3 IS NULL))
AND((RECINFO.ATTRIBUTE4 = P_ATTRIBUTE4)
OR(RECINFO.ATTRIBUTE4 IS NULL AND P_ATTRIBUTE4 IS NULL))
AND((RECINFO.ATTRIBUTE5 = P_ATTRIBUTE5)
OR(RECINFO.ATTRIBUTE5 IS NULL AND P_ATTRIBUTE5 IS NULL))
AND((RECINFO.ATTRIBUTE6 = P_ATTRIBUTE6)
OR(RECINFO.ATTRIBUTE6 IS NULL AND P_ATTRIBUTE6 IS NULL))
AND((RECINFO.ATTRIBUTE7 = P_ATTRIBUTE7)
OR(RECINFO.ATTRIBUTE7 IS NULL AND P_ATTRIBUTE7 IS NULL))
AND((RECINFO.ATTRIBUTE8 = P_ATTRIBUTE8)
OR(RECINFO.ATTRIBUTE8 IS NULL AND P_ATTRIBUTE8 IS NULL))
AND((RECINFO.ATTRIBUTE9 = P_ATTRIBUTE9)
OR(RECINFO.ATTRIBUTE9 IS NULL AND P_ATTRIBUTE9 IS NULL))
AND((RECINFO.ATTRIBUTE10 = P_ATTRIBUTE10)
OR(RECINFO.ATTRIBUTE10 IS NULL AND P_ATTRIBUTE10 IS NULL))
AND((RECINFO.ATTRIBUTE11 = P_ATTRIBUTE11)
OR(RECINFO.ATTRIBUTE11 IS NULL AND P_ATTRIBUTE11 IS NULL))
AND((RECINFO.ATTRIBUTE12 = P_ATTRIBUTE13)
OR(RECINFO.ATTRIBUTE12 IS NULL AND P_ATTRIBUTE12 IS NULL))
AND((RECINFO.ATTRIBUTE13 = P_ATTRIBUTE13)
OR(RECINFO.ATTRIBUTE13 IS NULL AND P_ATTRIBUTE13 IS NULL))
AND((RECINFO.ATTRIBUTE14 = P_ATTRIBUTE14)
OR(RECINFO.ATTRIBUTE14 IS NULL AND P_ATTRIBUTE14 IS NULL))
AND((RECINFO.ATTRIBUTE15 = P_ATTRIBUTE15)
OR(RECINFO.ATTRIBUTE15 IS NULL AND P_ATTRIBUTE15 IS NULL))
AND((RECINFO.ATTRIBUTE16 = P_ATTRIBUTE16)
OR(RECINFO.ATTRIBUTE16 IS NULL AND P_ATTRIBUTE16 IS NULL))
AND((RECINFO.ATTRIBUTE17 = P_ATTRIBUTE17)
OR(RECINFO.ATTRIBUTE17 IS NULL AND P_ATTRIBUTE17 IS NULL))
AND((RECINFO.ATTRIBUTE18 = P_ATTRIBUTE18)
OR(RECINFO.ATTRIBUTE18 IS NULL AND P_ATTRIBUTE18 IS NULL))
AND((RECINFO.ATTRIBUTE19 = P_ATTRIBUTE19)
OR(RECINFO.ATTRIBUTE19 IS NULL AND P_ATTRIBUTE19 IS NULL))
AND((RECINFO.ATTRIBUTE20 = P_ATTRIBUTE20)
OR(RECINFO.ATTRIBUTE20 IS NULL AND P_ATTRIBUTE20 IS NULL))
AND((RECINFO.RATE_BASIS = P_RATE_BASIS)
OR(RECINFO.RATE_BASIS IS NULL AND P_RATE_BASIS IS NULL)))
THEN
RETURN;