[Home] [Help]
PACKAGE BODY: APPS.PA_OBJ_STATUS_CHANGES_PKG
Source
1 package body PA_OBJ_STATUS_CHANGES_PKG as
2 /* $Header: PAOBSCTB.pls 120.1 2005/08/19 16:36:46 mwasowic noship $ */
3
4 procedure INSERT_ROW (
5 X_ROWID in out NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
6 X_OBJ_STATUS_CHANGE_ID in NUMBER,
7 X_OBJECT_TYPE in VARCHAR2,
8 X_OBJECT_ID in NUMBER,
9 X_STATUS_TYPE in VARCHAR2,
10 X_NEW_PROJECT_STATUS_CODE in VARCHAR2,
11 X_NEW_PROJECT_SYSTEM_STATUS_CO in VARCHAR2,
12 X_OLD_PROJECT_STATUS_CODE in VARCHAR2,
13 X_OLD_PROJECT_SYSTEM_STATUS_CO in VARCHAR2,
14 X_CHANGE_COMMENT in VARCHAR2,
15 X_CREATION_DATE in DATE,
16 X_CREATED_BY in NUMBER,
17 X_LAST_UPDATE_DATE in DATE,
18 X_LAST_UPDATED_BY in NUMBER,
19 X_LAST_UPDATE_LOGIN in NUMBER
20 ) is
21 cursor C is select ROWID from PA_OBJ_STATUS_CHANGES
22 where OBJ_STATUS_CHANGE_ID = X_OBJ_STATUS_CHANGE_ID ;
23 begin
24 insert into PA_OBJ_STATUS_CHANGES (
25 OBJ_STATUS_CHANGE_ID,
26 OBJECT_TYPE,
27 OBJECT_ID,
28 STATUS_TYPE,
29 NEW_PROJECT_STATUS_CODE,
30 NEW_PROJECT_SYSTEM_STATUS_CODE,
31 OLD_PROJECT_STATUS_CODE,
32 OLD_PROJECT_SYSTEM_STATUS_CODE,
33 CREATION_DATE,
34 CREATED_BY,
35 LAST_UPDATE_DATE,
36 LAST_UPDATED_BY,
37 LAST_UPDATE_LOGIN,
38 CHANGE_COMMENT
39 ) values (
40 X_OBJ_STATUS_CHANGE_ID,
41 X_OBJECT_TYPE,
42 X_OBJECT_ID,
43 X_STATUS_TYPE,
44 X_NEW_PROJECT_STATUS_CODE,
45 X_NEW_PROJECT_SYSTEM_STATUS_CO,
46 X_OLD_PROJECT_STATUS_CODE,
47 X_OLD_PROJECT_SYSTEM_STATUS_CO,
48 X_CREATION_DATE,
49 X_CREATED_BY,
50 X_LAST_UPDATE_DATE,
51 X_LAST_UPDATED_BY,
52 X_LAST_UPDATE_LOGIN,
53 X_CHANGE_COMMENT);
54
55 open c;
56 fetch c into X_ROWID;
57 if (c%notfound) then
58 close c;
59 raise no_data_found;
60 end if;
61 close c;
62
63 end INSERT_ROW;
64
65 procedure LOCK_ROW (
66 X_OBJ_STATUS_CHANGE_ID in NUMBER,
67 X_OBJECT_TYPE in VARCHAR2,
68 X_OBJECT_ID in NUMBER,
69 X_STATUS_TYPE in VARCHAR2,
70 X_NEW_PROJECT_STATUS_CODE in VARCHAR2,
71 X_NEW_PROJECT_SYSTEM_STATUS_CO in VARCHAR2,
72 X_OLD_PROJECT_STATUS_CODE in VARCHAR2,
73 X_OLD_PROJECT_SYSTEM_STATUS_CO in VARCHAR2,
74 X_CHANGE_COMMENT in VARCHAR2
75 ) is
76 cursor c1 is select
77 OBJECT_TYPE,
78 OBJECT_ID,
79 STATUS_TYPE,
80 NEW_PROJECT_STATUS_CODE,
81 NEW_PROJECT_SYSTEM_STATUS_CODE,
82 OLD_PROJECT_STATUS_CODE,
83 OLD_PROJECT_SYSTEM_STATUS_CODE,
84 CHANGE_COMMENT,
85 OBJ_STATUS_CHANGE_ID
86 from PA_OBJ_STATUS_CHANGES
87 where OBJ_STATUS_CHANGE_ID = X_OBJ_STATUS_CHANGE_ID
88 for update of OBJ_STATUS_CHANGE_ID nowait;
89 begin
90 for tlinfo in c1 loop
91 if ( (tlinfo.OBJ_STATUS_CHANGE_ID = X_OBJ_STATUS_CHANGE_ID)
92 AND (tlinfo.OBJECT_TYPE = X_OBJECT_TYPE)
93 AND (tlinfo.OBJECT_ID = X_OBJECT_ID)
94 AND (tlinfo.STATUS_TYPE = X_STATUS_TYPE)
95 AND (tlinfo.NEW_PROJECT_STATUS_CODE = X_NEW_PROJECT_STATUS_CODE)
96 AND (tlinfo.NEW_PROJECT_SYSTEM_STATUS_CODE = X_NEW_PROJECT_SYSTEM_STATUS_CO)
97 AND (tlinfo.OLD_PROJECT_STATUS_CODE = X_OLD_PROJECT_STATUS_CODE)
98 AND (tlinfo.OLD_PROJECT_SYSTEM_STATUS_CODE = X_OLD_PROJECT_SYSTEM_STATUS_CO)
99 AND ((tlinfo.CHANGE_COMMENT = X_CHANGE_COMMENT)
100 OR ((tlinfo.CHANGE_COMMENT is null) AND (X_CHANGE_COMMENT is null)))
101 ) then
102 null;
103 else
104 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
105 app_exception.raise_exception;
106 end if;
107 end loop;
108 return;
109 end LOCK_ROW;
110
111 procedure UPDATE_ROW (
112 X_OBJ_STATUS_CHANGE_ID in NUMBER,
113 X_OBJECT_TYPE in VARCHAR2,
114 X_OBJECT_ID in NUMBER,
115 X_STATUS_TYPE in VARCHAR2,
116 X_NEW_PROJECT_STATUS_CODE in VARCHAR2,
117 X_NEW_PROJECT_SYSTEM_STATUS_CO in VARCHAR2,
118 X_OLD_PROJECT_STATUS_CODE in VARCHAR2,
119 X_OLD_PROJECT_SYSTEM_STATUS_CO in VARCHAR2,
120 X_CHANGE_COMMENT in VARCHAR2,
121 X_LAST_UPDATE_DATE in DATE,
122 X_LAST_UPDATED_BY in NUMBER,
123 X_LAST_UPDATE_LOGIN in NUMBER
124 ) is
125 begin
126 update PA_OBJ_STATUS_CHANGES set
127 OBJECT_TYPE = X_OBJECT_TYPE,
128 OBJECT_ID = X_OBJECT_ID,
129 STATUS_TYPE = X_STATUS_TYPE,
130 NEW_PROJECT_STATUS_CODE = X_NEW_PROJECT_STATUS_CODE,
131 NEW_PROJECT_SYSTEM_STATUS_CODE = X_NEW_PROJECT_SYSTEM_STATUS_CO,
132 OLD_PROJECT_STATUS_CODE = X_OLD_PROJECT_STATUS_CODE,
133 OLD_PROJECT_SYSTEM_STATUS_CODE = X_OLD_PROJECT_SYSTEM_STATUS_CO,
134 CHANGE_COMMENT = X_CHANGE_COMMENT,
135 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
136 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
137 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
138 where OBJ_STATUS_CHANGE_ID = X_OBJ_STATUS_CHANGE_ID;
139
140 if (sql%notfound) then
141 raise no_data_found;
142 end if;
143 end UPDATE_ROW;
144
145 procedure DELETE_ROW (
146 X_OBJ_STATUS_CHANGE_ID in NUMBER
147 ) is
148 begin
149 delete from PA_OBJ_STATUS_CHANGES
150 where OBJ_STATUS_CHANGE_ID = X_OBJ_STATUS_CHANGE_ID;
151
152 if (sql%notfound) then
153 raise no_data_found;
154 end if;
155
156 end DELETE_ROW;
157
158 end PA_OBJ_STATUS_CHANGES_PKG;