[Home] [Help]
PACKAGE BODY: APPS.PSP_EFFORT_MESSAGES_PKG
Source
1 package body PSP_EFFORT_MESSAGES_PKG as
2 /* $Header: PSPERMEB.pls 115.4 2002/11/18 12:34:34 lveerubh ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_MESSAGE_ID in NUMBER,
6 X_EFFORT_REPORT_MESSAGE_TYPE in VARCHAR2,
7 X_TEXT in VARCHAR2,
8 X_MODE in VARCHAR2 default 'R'
9 ) is
10 cursor C is select ROWID from PSP_EFFORT_MESSAGES
11 where MESSAGE_ID = X_MESSAGE_ID;
12 X_LAST_UPDATE_DATE DATE;
13 X_LAST_UPDATED_BY NUMBER;
14 X_LAST_UPDATE_LOGIN NUMBER;
15 begin
16 X_LAST_UPDATE_DATE := SYSDATE;
17 if(X_MODE = 'I') then
18 X_LAST_UPDATED_BY := 1;
19 X_LAST_UPDATE_LOGIN := 0;
20 elsif (X_MODE = 'R') then
21 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
22 if X_LAST_UPDATED_BY is NULL then
23 X_LAST_UPDATED_BY := -1;
24 end if;
25 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
26 if X_LAST_UPDATE_LOGIN is NULL then
27 X_LAST_UPDATE_LOGIN := -1;
28 end if;
29 else
30 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
31 app_exception.raise_exception;
32 end if;
33 insert into PSP_EFFORT_MESSAGES (
34 MESSAGE_ID,
35 EFFORT_REPORT_MESSAGE_TYPE,
36 TEXT,
37 CREATION_DATE,
38 CREATED_BY,
39 LAST_UPDATE_DATE,
40 LAST_UPDATED_BY,
41 LAST_UPDATE_LOGIN
42 ) values (
43 X_MESSAGE_ID,
44 X_EFFORT_REPORT_MESSAGE_TYPE,
45 X_TEXT,
46 X_LAST_UPDATE_DATE,
47 X_LAST_UPDATED_BY,
48 X_LAST_UPDATE_DATE,
49 X_LAST_UPDATED_BY,
50 X_LAST_UPDATE_LOGIN
51 );
52
53 open c;
54 fetch c into X_ROWID;
55 if (c%notfound) then
56 close c;
57 raise no_data_found;
58 end if;
59 close c;
60
61 end INSERT_ROW;
62
63 procedure LOCK_ROW (
64 X_MESSAGE_ID in NUMBER,
65 X_EFFORT_REPORT_MESSAGE_TYPE in VARCHAR2,
66 X_TEXT in VARCHAR2
67 ) is
68 cursor c1 is select
69 EFFORT_REPORT_MESSAGE_TYPE,
70 TEXT
71 from PSP_EFFORT_MESSAGES
72 where MESSAGE_ID = X_MESSAGE_ID
73 for update of MESSAGE_ID nowait;
74 tlinfo c1%rowtype;
75
76 begin
77 open c1;
78 fetch c1 into tlinfo;
79 if (c1%notfound) then
80 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
81 app_exception.raise_exception;
82 close c1;
83 return;
84 end if;
85 close c1;
86
87 if ( (tlinfo.EFFORT_REPORT_MESSAGE_TYPE = X_EFFORT_REPORT_MESSAGE_TYPE)
88 AND (tlinfo.TEXT = X_TEXT)
89 ) then
90 null;
91 else
92 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
93 app_exception.raise_exception;
94 end if;
95 return;
96 end LOCK_ROW;
97
98 procedure UPDATE_ROW (
99 X_MESSAGE_ID in NUMBER,
100 X_EFFORT_REPORT_MESSAGE_TYPE in VARCHAR2,
101 X_TEXT in VARCHAR2,
102 X_MODE in VARCHAR2 default 'R'
103 ) is
104 X_LAST_UPDATE_DATE DATE;
105 X_LAST_UPDATED_BY NUMBER;
106 X_LAST_UPDATE_LOGIN NUMBER;
107 begin
108 X_LAST_UPDATE_DATE := SYSDATE;
109 if(X_MODE = 'I') then
110 X_LAST_UPDATED_BY := 1;
111 X_LAST_UPDATE_LOGIN := 0;
112 elsif (X_MODE = 'R') then
113 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
114 if X_LAST_UPDATED_BY is NULL then
115 X_LAST_UPDATED_BY := -1;
116 end if;
117 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
118 if X_LAST_UPDATE_LOGIN is NULL then
119 X_LAST_UPDATE_LOGIN := -1;
120 end if;
121 else
122 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
123 app_exception.raise_exception;
124 end if;
125 update PSP_EFFORT_MESSAGES set
126 EFFORT_REPORT_MESSAGE_TYPE = X_EFFORT_REPORT_MESSAGE_TYPE,
127 TEXT = X_TEXT,
128 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
129 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
130 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
131 where MESSAGE_ID = X_MESSAGE_ID
132 ;
133 if (sql%notfound) then
134 raise no_data_found;
135 end if;
136 end UPDATE_ROW;
137
138 procedure ADD_ROW (
139 X_ROWID in out NOCOPY VARCHAR2,
140 X_MESSAGE_ID in NUMBER,
141 X_EFFORT_REPORT_MESSAGE_TYPE in VARCHAR2,
142 X_TEXT in VARCHAR2,
143 X_MODE in VARCHAR2 default 'R'
144 ) is
145 cursor c1 is select rowid from PSP_EFFORT_MESSAGES
146 where MESSAGE_ID = X_MESSAGE_ID
147 ;
148 dummy c1%rowtype;
149 begin
150 open c1;
151 fetch c1 into dummy;
152 if (c1%notfound) then
153 close c1;
154 INSERT_ROW (
155 X_ROWID,
156 X_MESSAGE_ID,
157 X_EFFORT_REPORT_MESSAGE_TYPE,
158 X_TEXT,
159 X_MODE);
160 return;
161 end if;
162 close c1;
163 UPDATE_ROW (
164 X_MESSAGE_ID,
165 X_EFFORT_REPORT_MESSAGE_TYPE,
166 X_TEXT,
167 X_MODE);
168 end ADD_ROW;
169
170 procedure DELETE_ROW (
171 X_MESSAGE_ID in NUMBER
172 ) is
173 begin
174 delete from PSP_EFFORT_MESSAGES
175 where MESSAGE_ID = X_MESSAGE_ID;
176 ---if (sql%notfound) then
177 ---raise no_data_found;
178 ---end if;
179 end DELETE_ROW;
180
181 end PSP_EFFORT_MESSAGES_PKG;