1 package body PA_OBJ_STATUS_LISTS_PKG as
2 /* $Header: PAOBSLTB.pls 120.1 2005/08/19 16:36:53 mwasowic noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
5 X_OBJ_STATUS_LIST_ID in NUMBER,
6 X_OBJECT_TYPE in VARCHAR2,
7 X_OBJECT_ID in NUMBER,
8 X_STATUS_LIST_ID in NUMBER,
9 X_STATUS_TYPE in VARCHAR2,
10 X_CREATION_DATE in DATE,
11 X_CREATED_BY in NUMBER,
12 X_LAST_UPDATE_DATE in DATE,
13 X_LAST_UPDATED_BY in NUMBER,
14 X_LAST_UPDATE_LOGIN in NUMBER
15 ) is
16 cursor C is select ROWID from PA_OBJ_STATUS_LISTS
17 where OBJ_STATUS_LIST_ID = X_OBJ_STATUS_LIST_ID
18 ;
19 begin
20 insert into PA_OBJ_STATUS_LISTS (
21 LAST_UPDATED_BY,
22 LAST_UPDATE_LOGIN,
23 CREATED_BY,
24 LAST_UPDATE_DATE,
25 CREATION_DATE,
26 OBJ_STATUS_LIST_ID,
27 OBJECT_TYPE,
28 OBJECT_ID,
29 STATUS_LIST_ID,
30 STATUS_TYPE
31 ) values (
32 nvl(X_LAST_UPDATED_BY,fnd_global.user_id),
33 nvl(X_LAST_UPDATE_LOGIN,fnd_global.login_id),
34 nvl(X_CREATED_BY,fnd_global.user_id),
35 nvl(X_LAST_UPDATE_DATE,sysdate),
36 nvl(X_CREATION_DATE,sysdate),
37 X_OBJ_STATUS_LIST_ID,
38 X_OBJECT_TYPE,
39 X_OBJECT_ID,
40 X_STATUS_LIST_ID,
41 X_STATUS_TYPE);
42
43 open c;
44 fetch c into X_ROWID;
45 if (c%notfound) then
46 close c;
47 raise no_data_found;
48 end if;
49 close c;
50
51 end INSERT_ROW;
52
53 procedure LOCK_ROW (
54 X_OBJ_STATUS_LIST_ID in NUMBER,
55 X_OBJECT_TYPE in VARCHAR2,
56 X_OBJECT_ID in NUMBER,
57 X_STATUS_LIST_ID in NUMBER,
58 X_STATUS_TYPE in VARCHAR2
59 ) is
60 cursor c1 is select
61 OBJECT_TYPE,
62 OBJECT_ID,
63 STATUS_LIST_ID,
64 STATUS_TYPE,
65 OBJ_STATUS_LIST_ID
66 from PA_OBJ_STATUS_LISTS
67 where OBJ_STATUS_LIST_ID = X_OBJ_STATUS_LIST_ID
68 for update of OBJ_STATUS_LIST_ID nowait;
69 begin
70 for tlinfo in c1 loop
71 if ( ((tlinfo.OBJ_STATUS_LIST_ID = X_OBJ_STATUS_LIST_ID)
72 OR ((tlinfo.OBJ_STATUS_LIST_ID is null) AND (X_OBJ_STATUS_LIST_ID is null)))
73 AND ((tlinfo.OBJECT_TYPE = X_OBJECT_TYPE)
74 OR ((tlinfo.OBJECT_TYPE is null) AND (X_OBJECT_TYPE is null)))
75 AND ((tlinfo.OBJECT_ID = X_OBJECT_ID)
76 OR ((tlinfo.OBJECT_ID is null) AND (X_OBJECT_ID is null)))
77 AND ((tlinfo.STATUS_LIST_ID = X_STATUS_LIST_ID)
78 OR ((tlinfo.STATUS_LIST_ID is null) AND (X_STATUS_LIST_ID is null)))
79 AND ((tlinfo.STATUS_TYPE = X_STATUS_TYPE)
80 OR ((tlinfo.STATUS_TYPE is null) AND (X_STATUS_TYPE is null)))
81 ) then
82 null;
83 else
84 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
85 app_exception.raise_exception;
86 end if;
87 end loop;
88 return;
89 end LOCK_ROW;
90
91 procedure UPDATE_ROW (
92 X_OBJ_STATUS_LIST_ID in NUMBER,
93 X_OBJECT_TYPE in VARCHAR2,
94 X_OBJECT_ID in NUMBER,
95 X_STATUS_LIST_ID in NUMBER,
96 X_STATUS_TYPE in VARCHAR2,
97 X_LAST_UPDATE_DATE in DATE,
98 X_LAST_UPDATED_BY in NUMBER,
99 X_LAST_UPDATE_LOGIN in NUMBER
100 ) is
101 begin
102 update PA_OBJ_STATUS_LISTS set
103 OBJECT_TYPE = X_OBJECT_TYPE,
104 OBJECT_ID = X_OBJECT_ID,
105 STATUS_LIST_ID = X_STATUS_LIST_ID,
106 STATUS_TYPE = X_STATUS_TYPE,
107 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
108 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
109 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
110 where OBJ_STATUS_LIST_ID = X_OBJ_STATUS_LIST_ID;
111
112 if (sql%notfound) then
113 raise no_data_found;
114 end if;
115 end UPDATE_ROW;
116
117 procedure DELETE_ROW (
118 X_OBJ_STATUS_LIST_ID in NUMBER
119 ) is
120 begin
121 delete from PA_OBJ_STATUS_LISTS
122 where OBJ_STATUS_LIST_ID = X_OBJ_STATUS_LIST_ID;
123
124 if (sql%notfound) then
125 raise no_data_found;
126 end if;
127
128 end DELETE_ROW;
129
130 end PA_OBJ_STATUS_LISTS_PKG;
[Home] [Help]