PACKAGE BODY: APPS.GMA_LOCK_RECORD

Source

1 PACKAGE BODY GMA_LOCK_RECORD AS
2 /* $Header: GMALOCKB.pls 115.2 2003/05/07 14:11:18 appldev ship $ */
3   FUNCTION lock_record(V_Table_name VARCHAR2,V_Column_name VARCHAR2,V_Column_val NUMBER,V_Last_update_date DATE) RETURN NUMBER IS
4 
5     X_Col_val varchar2(32);
6     X_Cur_lock_hdr integer;
7     X_Get_who_hdr integer;
8     X_Rows_processed integer;
9     X_Last_update_date DATE;
10     X_Select_statement varchar2(200);
11 
12   BEGIN
13     X_Select_statement := 'SELECT '||V_Column_name||' FROM '||V_Table_name || ' WHERE '||V_Column_name
14                                    || ' = :V_Column_val '||' FOR UPDATE OF '|| V_Column_name ||' NOWAIT ';
15 
16 
17     IF dbms_sql.is_open(X_Get_who_hdr) THEN
18       dbms_sql.close_cursor(X_Get_who_hdr);
19     END IF;
20 
21     BEGIN
22       IF dbms_sql.is_open(X_Cur_lock_hdr) THEN
23         dbms_sql.close_cursor(X_Cur_lock_hdr);
24       END IF;
25 
26       X_Cur_lock_hdr:=dbms_sql.open_cursor;
27       dbms_sql.parse(X_Cur_lock_hdr,X_Select_statement,0);
28       dbms_sql.define_column(X_Cur_lock_hdr, 1, V_Column_name,32);
29 
30       --  Changing literals to bind variables as per coding standard.      |
31       --  See detials in bug 2941580                                       |
32       dbms_sql.bind_variable(X_Cur_lock_hdr, 'V_Column_val',to_char(V_Column_val));
33 
34       X_Rows_processed:=dbms_sql.execute(X_Cur_lock_hdr);
35       IF dbms_sql.fetch_rows(X_Cur_lock_hdr) > 0 THEN
36         Null;
37       ELSE
38         IF dbms_sql.is_open(X_Cur_lock_hdr) THEN
39           dbms_sql.close_cursor(X_Cur_lock_hdr);
40         END IF;
41         ROLLBACK;
42         RETURN(-1);
43       END IF;
44       dbms_sql.close_cursor(X_Cur_lock_hdr);
45 
46       X_Select_statement:='SELECT Last_update_date FROM '||V_Table_name||' WHERE '||V_Column_name || '=:V_Column_val';
47       X_Get_who_hdr:=dbms_sql.open_cursor;
48       dbms_sql.parse(X_Get_who_hdr,X_Select_statement,0);
49       dbms_sql.define_column(X_Get_who_hdr, 1, V_Last_update_date);
50 
51       --  Changing literals to bind variables as per coding standard.      |
52       --  See detials in bug 2941580                                       |
53       dbms_sql.bind_variable(X_Get_who_hdr,'V_Column_val',to_char(V_Column_val));
54 
55       X_Rows_processed:=dbms_sql.execute(X_Get_who_hdr);
56       IF dbms_sql.fetch_rows(X_Get_who_hdr) > 0 THEN
57         dbms_sql.column_value(X_Get_who_hdr,1,X_Last_update_date);
58       END IF;
59       IF dbms_sql.is_open(X_Get_who_hdr) THEN
60         dbms_sql.close_cursor(X_Get_who_hdr);
61       END IF;
62       IF X_Last_update_date <> V_Last_update_date THEN
63         ROLLBACK;
64         RETURN(-2);
65       END IF;
66       RETURN (1);
67     EXCEPTION
68       WHEN app_exceptions.record_lock_exception THEN
69         IF dbms_sql.is_open(X_Get_who_hdr) THEN
70           dbms_sql.close_cursor(X_Get_who_hdr);
71         END IF;
72         RETURN(0);
73     END;
74   END Lock_record;
75 END GMA_LOCK_RECORD;