[Home] [Help]
33: -- TABLE HANDLERS
34: -- 1. insert_jtf_perz_query
35: -- 2. insert_jtf_perz_query_order_by
36: -- 3. insert_jtf_perz_query_param
37: -- 4. insert_jtf_perz_query_raw_sql
38: -- 5. update_jtf_perz_query
39:
40: -- *****************************************************************************
41: -- *****************************************************************************
186: -- *****************************************************************************
187:
188: -- insert row into query order by
189:
190: PROCEDURE insert_jtf_perz_query_raw_sql(
191: x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
192: X_Query_Raw_Sql_ID IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
193: x_Query_ID NUMBER,
194: x_Select_String VARCHAR2,
197: X_Order_by_String VARCHAR2,
198: X_Group_by_String VARCHAR2,
199: X_Having_String VARCHAR2
200: ) IS
201: CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_RAW_SQL
202: WHERE Query_Raw_Sql_ID = X_Query_Raw_Sql_ID;
203: CURSOR C2 IS SELECT JTF_PERZ_QUERY_RAW_SQL_s.NEXTVAL FROM sys.dual;
204:
205: BEGIN
199: X_Having_String VARCHAR2
200: ) IS
201: CURSOR C IS SELECT ROWID FROM JTF_PERZ_QUERY_RAW_SQL
202: WHERE Query_Raw_Sql_ID = X_Query_Raw_Sql_ID;
203: CURSOR C2 IS SELECT JTF_PERZ_QUERY_RAW_SQL_s.NEXTVAL FROM sys.dual;
204:
205: BEGIN
206: IF ((X_Query_Raw_Sql_ID IS NULL) OR
207: (X_Query_Raw_Sql_ID = Fnd_Api.G_MISS_NUM ))THEN
208: OPEN C2;
209: FETCH C2 INTO X_Query_Raw_Sql_ID;
210: CLOSE C2;
211: END IF;
212: INSERT INTO JTF_PERZ_QUERY_RAW_SQL(
213: QUERY_RAW_SQL_ID,
214: QUERY_ID,
215: SELECT_STRING,
216: FROM_STRING,
238: IF (C%NOTFOUND) THEN
239: CLOSE C;
240: RAISE NO_DATA_FOUND;
241: END IF;
242: END insert_jtf_perz_query_raw_sql;
243: -- *****************************************************************************
244:
245: PROCEDURE insert_jtf_perz_query_param(
246: x_Rowid OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
577:
578: l_rowid := NULL;
579: l_Query_Raw_Sql_ID := p_query_raw_sql_rec.Query_Raw_Sql_ID;
580:
581: insert_jtf_perz_query_raw_sql(
582: l_rowid,
583: l_Query_Raw_Sql_ID,
584: l_query_id,
585: p_query_raw_sql_rec.Select_String,
755: -- 3. Delete all order by table entries for this query_id
756: DELETE FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_query_id;
757:
758: -- 4. Delete all raw sql table entries for this query_id
759: DELETE FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;
760:
761: -- 5. Update query header information
762: -- **** UPDATE CALL HERE
763: update_jtf_perz_query(
807:
808: IF (p_query_raw_sql_rec.Select_String IS NOT NULL) THEN
809: l_rowid := NULL;
810: l_Query_Raw_Sql_ID := p_query_raw_sql_rec.Query_Raw_Sql_ID;
811: insert_jtf_perz_query_raw_sql(
812: l_rowid,
813: l_Query_Raw_Sql_ID,
814: l_query_id,
815: p_query_raw_sql_rec.Select_String,
931: -- 3. Delete all order by table entries for this query_id
932: DELETE FROM JTF_PERZ_QUERY_ORDER_BY WHERE QUERY_ID = l_query_id;
933:
934: -- 4. Delete all raw sql table entries for this query_id
935: DELETE FROM JTF_PERZ_QUERY_RAW_SQL WHERE QUERY_ID = l_query_id;
936:
937: -- 5. Delete query header table entries for this query_id
938: DELETE FROM JTF_PERZ_QUERY WHERE QUERY_ID = l_query_id;
939:
1515: x_query_raw_sql_rec.WHERE_STRING,
1516: x_query_raw_sql_rec.ORDER_BY_STRING,
1517: x_query_raw_sql_rec.GROUP_BY_STRING,
1518: x_query_raw_sql_rec.HAVING_STRING
1519: FROM JTF_PERZ_QUERY_RAW_SQL
1520: WHERE QUERY_ID = p_query_id;
1521:
1522: EXCEPTION
1523: WHEN NO_DATA_FOUND THEN