PACKAGE BODY: APPS.SHP_GEN_UNIQUE_PKG

Source

1 PACKAGE BODY SHP_GEN_UNIQUE_PKG as
2 /* $Header: SHPFXUQB.pls 115.0 99/07/16 08:17:25 porting ship $ */
3 
4   --
5   -- PUBLIC FUNCTIONS
6   --
7 
8   --
9   -- Name
10   --   Gen_Check_Unique
11   -- Purpose
12   --   Checks for duplicates in database
13   -- Arguments
14   --   query_text               query to execute to test for uniqueness
15   --   prod_name		product name to send message for
16   --   msg_name			message to print if duplicate found
17   --
18   -- Notes
19   --   uses DBMS_SQL package to create and execute cursor for given query
20 
21   PROCEDURE Gen_Check_Unique(query_text VARCHAR2,
22 			 prod_name VARCHAR2,
23 			 msg_name VARCHAR2) IS
24 	rec_cursor INTEGER;
25 	any_found INTEGER;
26   BEGIN
27     rec_cursor := dbms_sql.open_cursor;
28     dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
29     any_found := dbms_sql.execute_and_fetch(rec_cursor);
30     IF (any_found > 0) THEN
31       FND_MESSAGE.SET_NAME(prod_name,msg_name);
32       APP_EXCEPTION.RAISE_EXCEPTION;
33     END IF;
34   END;
35 
36   PROCEDURE Get_Active_Date(query_text 		IN	VARCHAR2,
37    			    date_fetched 	OUT	DATE) IS
38     rec_cursor 			INTEGER;
39     row_processed 		INTEGER;
40     error_out			EXCEPTION;
41     date_in_table		DATE;
42   BEGIN
43     rec_cursor := dbms_sql.open_cursor;
44     dbms_sql.parse(rec_cursor,query_text,dbms_sql.v7);
45     dbms_sql.define_column(rec_cursor, 1, date_in_table);
46     row_processed := dbms_sql.execute(rec_cursor);
47 
48     IF ( dbms_sql.fetch_rows(rec_cursor) > 0) THEN
49       dbms_sql.column_value( rec_cursor, 1, date_in_table);
50     ELSE
51       RAISE error_out;
52     END IF;
53 
54     dbms_sql.close_cursor(rec_cursor);
55 
56     date_fetched := date_in_table;
57 
58   EXCEPTION
59     WHEN OTHERS THEN
60         dbms_sql.close_cursor(rec_cursor);
61         FND_MESSAGE.Set_Name('OE','OE_QUERY_ERROR');
62         FND_MESSAGE.Set_Token('PACKAGE','SHP_GEN_UNIQUE_PKG');
63         FND_MESSAGE.Set_Token('ORA_ERROR',to_char(sqlcode));
64         FND_MESSAGE.Set_Token('ORA_TEXT',query_text);
65         APP_EXCEPTION.Raise_Exception;
66   END Get_Active_Date;
67 
68 
69 END SHP_GEN_UNIQUE_PKG;