57: x_mini_site_section_item_id OUT NUMBER
58: )
59: IS
60:
61: CURSOR c IS SELECT rowid FROM jtf_dsp_msite_sct_items
62: WHERE mini_site_section_item_id = x_mini_site_section_item_id;
63: CURSOR c2 IS SELECT jtf_dsp_msite_sct_items_s1.nextval FROM dual;
64:
65: BEGIN
59: IS
60:
61: CURSOR c IS SELECT rowid FROM jtf_dsp_msite_sct_items
62: WHERE mini_site_section_item_id = x_mini_site_section_item_id;
63: CURSOR c2 IS SELECT jtf_dsp_msite_sct_items_s1.nextval FROM dual;
64:
65: BEGIN
66:
67: -- Primary key validation check
74: CLOSE c2;
75: END IF;
76:
77: -- insert base
78: INSERT INTO jtf_dsp_msite_sct_items
79: (
80: mini_site_section_item_id,
81: object_version_number,
82: mini_site_id,
137: IS
138: BEGIN
139:
140: -- update base
141: UPDATE jtf_dsp_msite_sct_items SET
142: object_version_number = object_version_number + 1,
143: start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
144: start_date_active, p_start_date_active),
145: end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
175: )
176: IS
177: BEGIN
178:
179: DELETE FROM jtf_dsp_msite_sct_items
180: WHERE mini_site_section_item_id = p_mini_site_section_item_id;
181:
182: IF (sql%NOTFOUND) THEN
183: RAISE NO_DATA_FOUND;
235: -- Check duplicate entry
236: BEGIN
237:
238: SELECT mini_site_section_item_id INTO l_tmp_msite_sct_item_id
239: FROM jtf_dsp_msite_sct_items
240: WHERE mini_site_id = p_mini_site_id
241: AND section_item_id = p_section_item_id;
242:
243: EXCEPTION
759: -- is specified, then query for mini_site_section_item_id
760: BEGIN
761:
762: SELECT mini_site_section_item_id INTO l_msite_sct_item_id
763: FROM jtf_dsp_msite_sct_items
764: WHERE mini_site_id = p_mini_site_id
765: AND section_item_id = p_section_item_id;
766:
767: EXCEPTION
941: -- query for mini_site_section_item_id
942: BEGIN
943:
944: SELECT mini_site_section_item_id INTO l_msite_sct_item_id
945: FROM jtf_dsp_msite_sct_items
946: WHERE mini_site_id = p_mini_site_id
947: AND section_item_id = p_section_item_id;
948:
949: EXCEPTION