[Home] [Help]
48: l_other_lookup VARCHAR2(30); -- Holds other lookup if it already exists
49:
50: /* Selects the other lookup column from a row if it exists */
51: CURSOR row_exists_cur IS
52: SELECT other_lookup_code FROM hri_job_category_sets
53: WHERE job_category_set = p_job_cat_set
54: AND (member_lookup_code = p_job_cat_lookup
55: OR member_lookup_code IS NULL and p_job_cat_lookup IS NULL);
56:
70: OPEN row_exists_cur;
71: FETCH row_exists_cur INTO l_other_lookup;
72: IF (row_exists_cur%NOTFOUND OR row_exists_cur%NOTFOUND IS NULL) THEN
73: /* If row does not exist, insert it */
74: INSERT INTO hri_job_category_sets
75: ( job_category_set
76: , member_lookup_code
77: , other_lookup_code )
78: VALUES
80: , p_job_cat_lookup
81: , p_other_lookup );
82: ELSIF (l_other_lookup <> p_other_lookup AND p_other_lookup IS NOT NULL) THEN
83: /* If the other row exists, update it if it is different */
84: UPDATE hri_job_category_sets
85: SET other_lookup_code = p_other_lookup
86: WHERE job_category_set = p_job_cat_set
87: AND member_lookup_code IS NULL;
88: END IF;
100:
101: BEGIN
102:
103: /* Remove row if it exists */
104: DELETE FROM hri_job_category_sets
105: WHERE job_category_set = p_job_cat_set
106: AND (member_lookup_code = p_job_cat_lookup
107: OR other_lookup_code = p_other_lookup);
108: