eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.PSP_EFFORT_REPORT_DETAILS_PKG

Source


1 package body PSP_EFFORT_REPORT_DETAILS_PKG as
2  /* $Header: PSPERDEB.pls 115.7 2002/11/18 12:28:54 lveerubh ship $ */
3 procedure INSERT_ROW (
4   X_ROWID in out NOCOPY VARCHAR2,
5   X_EFFORT_REPORT_ID in NUMBER,
6   X_VERSION_NUM in NUMBER,
7   X_EFFORT_REPORT_LINE_NUM in NUMBER,
8   X_ASSIGNMENT_ID in NUMBER,
9   X_ELEMENT_TYPE_ID in NUMBER,
10   X_GL_CODE_COMBINATION_ID in NUMBER,
11   X_PROJECT_ID in NUMBER,
12   X_EXPENDITURE_ORGANIZATION_ID in NUMBER,
13   X_EXPENDITURE_TYPE in VARCHAR2,
14   X_TASK_ID in NUMBER,
15   X_AWARD_ID in NUMBER,
16   X_TOTAL_AMOUNT in NUMBER,
17   X_MODE in VARCHAR2 default 'R'
18   ) is
19     cursor C is select ROWID from PSP_EFFORT_REPORT_DETAILS
20       where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
21       and VERSION_NUM = X_VERSION_NUM
22       and EFFORT_REPORT_LINE_NUM = X_EFFORT_REPORT_LINE_NUM;
23     X_LAST_UPDATE_DATE DATE;
24     X_LAST_UPDATED_BY NUMBER;
25     X_LAST_UPDATE_LOGIN NUMBER;
26 begin
27   X_LAST_UPDATE_DATE := SYSDATE;
28   if(X_MODE = 'I') then
29     X_LAST_UPDATED_BY := 1;
30     X_LAST_UPDATE_LOGIN := 0;
31   elsif (X_MODE = 'R') then
32     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
33     if X_LAST_UPDATED_BY is NULL then
34       X_LAST_UPDATED_BY := -1;
35     end if;
36     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
37     if X_LAST_UPDATE_LOGIN is NULL then
38       X_LAST_UPDATE_LOGIN := -1;
39     end if;
40   else
41     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
42     app_exception.raise_exception;
43   end if;
44   insert into PSP_EFFORT_REPORT_DETAILS (
45     EFFORT_REPORT_ID,
46     VERSION_NUM,
47     EFFORT_REPORT_LINE_NUM,
48     ASSIGNMENT_ID,
49     ELEMENT_TYPE_ID,
50     GL_CODE_COMBINATION_ID,
51     PROJECT_ID,
52     EXPENDITURE_ORGANIZATION_ID,
53     EXPENDITURE_TYPE,
54     TASK_ID,
55     AWARD_ID,
56     TOTAL_AMOUNT,
57     CREATION_DATE,
58     CREATED_BY,
59     LAST_UPDATE_DATE,
60     LAST_UPDATED_BY,
61     LAST_UPDATE_LOGIN
62   ) values (
63     X_EFFORT_REPORT_ID,
64     X_VERSION_NUM,
65     X_EFFORT_REPORT_LINE_NUM,
66     X_ASSIGNMENT_ID,
67     X_ELEMENT_TYPE_ID,
68     X_GL_CODE_COMBINATION_ID,
69     X_PROJECT_ID,
70     X_EXPENDITURE_ORGANIZATION_ID,
71     X_EXPENDITURE_TYPE,
72     X_TASK_ID,
73     X_AWARD_ID,
74     X_TOTAL_AMOUNT,
75     X_LAST_UPDATE_DATE,
76     X_LAST_UPDATED_BY,
77     X_LAST_UPDATE_DATE,
78     X_LAST_UPDATED_BY,
79     X_LAST_UPDATE_LOGIN
80   );
81 
82   open c;
83   fetch c into X_ROWID;
84   if (c%notfound) then
85     close c;
86     raise no_data_found;
87   end if;
88   close c;
89 
90 end INSERT_ROW;
91 
92 procedure LOCK_ROW (
93   X_EFFORT_REPORT_ID in NUMBER,
94   X_VERSION_NUM in NUMBER,
95   X_EFFORT_REPORT_LINE_NUM in NUMBER,
96   X_ASSIGNMENT_ID in NUMBER,
97   X_ELEMENT_TYPE_ID in NUMBER,
98   X_GL_CODE_COMBINATION_ID in NUMBER,
99   X_PROJECT_ID in NUMBER,
100   X_EXPENDITURE_ORGANIZATION_ID in NUMBER,
101   X_EXPENDITURE_TYPE in VARCHAR2,
102   X_TASK_ID in NUMBER,
103   X_AWARD_ID in NUMBER,
104   X_TOTAL_AMOUNT in NUMBER
105 ) is
106   cursor c1 is select
107       ASSIGNMENT_ID,
108       ELEMENT_TYPE_ID,
109       GL_CODE_COMBINATION_ID,
110       PROJECT_ID,
111       EXPENDITURE_ORGANIZATION_ID,
112       EXPENDITURE_TYPE,
113       TASK_ID,
114       AWARD_ID,
115       TOTAL_AMOUNT
116     from PSP_EFFORT_REPORT_DETAILS
117     where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
118     and VERSION_NUM = X_VERSION_NUM
119     and EFFORT_REPORT_LINE_NUM = X_EFFORT_REPORT_LINE_NUM
120     for update of EFFORT_REPORT_ID nowait;
121   tlinfo c1%rowtype;
122 
123 begin
124   open c1;
125   fetch c1 into tlinfo;
126   if (c1%notfound) then
127     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
128     app_exception.raise_exception;
129     close c1;
130     return;
131   end if;
132   close c1;
133 
134   if ( (tlinfo.ASSIGNMENT_ID = X_ASSIGNMENT_ID)
135       AND (tlinfo.ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID)
136       AND ((tlinfo.GL_CODE_COMBINATION_ID = X_GL_CODE_COMBINATION_ID)
137            OR ((tlinfo.GL_CODE_COMBINATION_ID is null)
138                AND (X_GL_CODE_COMBINATION_ID is null)))
139       AND ((tlinfo.PROJECT_ID = X_PROJECT_ID)
140            OR ((tlinfo.PROJECT_ID is null)
141                AND (X_PROJECT_ID is null)))
142       AND ((tlinfo.EXPENDITURE_ORGANIZATION_ID = X_EXPENDITURE_ORGANIZATION_ID)
143            OR ((tlinfo.EXPENDITURE_ORGANIZATION_ID is null)
144                AND (X_EXPENDITURE_ORGANIZATION_ID is null)))
145       AND ((tlinfo.EXPENDITURE_TYPE = X_EXPENDITURE_TYPE)
146            OR ((tlinfo.EXPENDITURE_TYPE is null)
147                AND (X_EXPENDITURE_TYPE is null)))
148       AND ((tlinfo.TASK_ID = X_TASK_ID)
149            OR ((tlinfo.TASK_ID is null)
150                AND (X_TASK_ID is null)))
151       AND ((tlinfo.AWARD_ID = X_AWARD_ID)
152            OR ((tlinfo.AWARD_ID is null)
153                AND (X_AWARD_ID is null)))
154       AND (tlinfo.TOTAL_AMOUNT = X_TOTAL_AMOUNT)
155   ) then
156     null;
157   else
158     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
159     app_exception.raise_exception;
160   end if;
161   return;
162 end LOCK_ROW;
163 
164 procedure UPDATE_ROW (
165   X_EFFORT_REPORT_ID in NUMBER,
166   X_VERSION_NUM in NUMBER,
167   X_EFFORT_REPORT_LINE_NUM in NUMBER,
168   X_ASSIGNMENT_ID in NUMBER,
169   X_ELEMENT_TYPE_ID in NUMBER,
170   X_GL_CODE_COMBINATION_ID in NUMBER,
171   X_PROJECT_ID in NUMBER,
172   X_EXPENDITURE_ORGANIZATION_ID in NUMBER,
173   X_EXPENDITURE_TYPE in VARCHAR2,
174   X_TASK_ID in NUMBER,
175   X_AWARD_ID in NUMBER,
176   X_TOTAL_AMOUNT in NUMBER,
177   X_MODE in VARCHAR2 default 'R'
178   ) is
179     X_LAST_UPDATE_DATE DATE;
180     X_LAST_UPDATED_BY NUMBER;
181     X_LAST_UPDATE_LOGIN NUMBER;
182 begin
183   X_LAST_UPDATE_DATE := SYSDATE;
184   if(X_MODE = 'I') then
185     X_LAST_UPDATED_BY := 1;
186     X_LAST_UPDATE_LOGIN := 0;
187   elsif (X_MODE = 'R') then
188     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
189     if X_LAST_UPDATED_BY is NULL then
190       X_LAST_UPDATED_BY := -1;
191     end if;
192     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
193     if X_LAST_UPDATE_LOGIN is NULL then
194       X_LAST_UPDATE_LOGIN := -1;
195     end if;
196   else
197     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
198     app_exception.raise_exception;
199   end if;
200   update PSP_EFFORT_REPORT_DETAILS set
201     ASSIGNMENT_ID = X_ASSIGNMENT_ID,
202     ELEMENT_TYPE_ID = X_ELEMENT_TYPE_ID,
203     GL_CODE_COMBINATION_ID = X_GL_CODE_COMBINATION_ID,
204     PROJECT_ID = X_PROJECT_ID,
205     EXPENDITURE_ORGANIZATION_ID = X_EXPENDITURE_ORGANIZATION_ID,
206     EXPENDITURE_TYPE = X_EXPENDITURE_TYPE,
207     TASK_ID = X_TASK_ID,
208     AWARD_ID = X_AWARD_ID,
209     TOTAL_AMOUNT = X_TOTAL_AMOUNT,
210     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
211     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
212     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
213   where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
214   and VERSION_NUM = X_VERSION_NUM
215   and EFFORT_REPORT_LINE_NUM = X_EFFORT_REPORT_LINE_NUM
216   ;
217   ---if (sql%notfound) then
218     ---raise no_data_found;
219   ---end if;
220 end UPDATE_ROW;
221 
222 procedure ADD_ROW (
223   X_ROWID in out NOCOPY VARCHAR2,
224   X_EFFORT_REPORT_ID in NUMBER,
225   X_VERSION_NUM in NUMBER,
226   X_EFFORT_REPORT_LINE_NUM in NUMBER,
227   X_ASSIGNMENT_ID in NUMBER,
228   X_ELEMENT_TYPE_ID in NUMBER,
229   X_GL_CODE_COMBINATION_ID in NUMBER,
230   X_PROJECT_ID in NUMBER,
231   X_EXPENDITURE_ORGANIZATION_ID in NUMBER,
232   X_EXPENDITURE_TYPE in VARCHAR2,
233   X_TASK_ID in NUMBER,
234   X_AWARD_ID in NUMBER,
235   X_TOTAL_AMOUNT in NUMBER,
236   X_MODE in VARCHAR2 default 'R'
237   ) is
238   cursor c1 is select rowid from PSP_EFFORT_REPORT_DETAILS
239      where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
240      and VERSION_NUM = X_VERSION_NUM
241      and EFFORT_REPORT_LINE_NUM = X_EFFORT_REPORT_LINE_NUM
242   ;
243   dummy c1%rowtype;
244 begin
245   open c1;
246   fetch c1 into dummy;
247   if (c1%notfound) then
248     close c1;
249     INSERT_ROW (
250      X_ROWID,
251      X_EFFORT_REPORT_ID,
252      X_VERSION_NUM,
253      X_EFFORT_REPORT_LINE_NUM,
254      X_ASSIGNMENT_ID,
255      X_ELEMENT_TYPE_ID,
256      X_GL_CODE_COMBINATION_ID,
257      X_PROJECT_ID,
258      X_EXPENDITURE_ORGANIZATION_ID,
259      X_EXPENDITURE_TYPE,
260      X_TASK_ID,
261      X_AWARD_ID,
262      X_TOTAL_AMOUNT,
263      X_MODE);
264     return;
265   end if;
266   close c1;
267   UPDATE_ROW (
268    X_EFFORT_REPORT_ID,
269    X_VERSION_NUM,
270    X_EFFORT_REPORT_LINE_NUM,
271    X_ASSIGNMENT_ID,
272    X_ELEMENT_TYPE_ID,
273    X_GL_CODE_COMBINATION_ID,
274    X_PROJECT_ID,
275    X_EXPENDITURE_ORGANIZATION_ID,
276    X_EXPENDITURE_TYPE,
277    X_TASK_ID,
278    X_AWARD_ID,
279    X_TOTAL_AMOUNT,
280    X_MODE);
281 end ADD_ROW;
282 
283 procedure DELETE_ROW (
284   X_EFFORT_REPORT_ID in NUMBER,
285   X_VERSION_NUM in NUMBER,
286   X_EFFORT_REPORT_LINE_NUM in NUMBER
287 ) is
288 begin
289   delete from PSP_EFFORT_REPORT_DETAILS
290   where EFFORT_REPORT_ID = X_EFFORT_REPORT_ID
291   and VERSION_NUM = X_VERSION_NUM
292   and EFFORT_REPORT_LINE_NUM = X_EFFORT_REPORT_LINE_NUM;
293   ---if (sql%notfound) then
294     ---raise no_data_found;
295   ---end if;
296 end DELETE_ROW;
297 
298 end PSP_EFFORT_REPORT_DETAILS_PKG;