1 PACKAGE BODY PJM_PROJTASK_DELETION AS
2 /* $Header: PJMPTDLB.pls 120.1 2005/07/01 16:39:15 jxtang noship $ */
3
4 -- Function name : Checkuse_ProjTask
5 -- Pre-reqs : None.
6 -- Function : Checks if project/task references are currently used
7 -- in manufacturing applications.
8 -- This function should be performed prior to project/task
9 -- deletion from Oracle Projects
10 -- Parameters :
11 -- IN : p_project_id IN NUMBER Optional
12 -- : p_task_id IN NUMBER Optional
13 -- RETURNS :
14 -- Returns -1 if both input Project/Task arguments are null.
15 -- Returns 1 if input Project/Task argument is still referred
16 -- in MFG applications.
17 -- Returns 2 if input Project/Task argument is still referred
18 -- in PJM Task Assignment
19 --
20 -- This function does not check detailed status
21 -- such as closed sales order line, or canceled PO
22 -- line/shipments, etc. Therefore those project/
23 -- task references will prevent deletion in Oracle
24 -- projects, so users should manually
25 -- purge those references in mfg apps in order to
26 -- delete it successfully in Oracle Projects.
27 --
28 -- Returns 0 if input Project/Task argument is not referred.
29 --
30 FUNCTION CheckUse_ProjectTask (p_project_id IN NUMBER,
31 p_task_id IN NUMBER)
32
33 RETURN NUMBER
34 IS
35
36 -- This function can be returns 1 if project id and task id is in use.
37 -- How to indicate at least one of the parameters has to be specified?
38
39 pjm_active NUMBER :=0;
40 l_project_id NUMBER :=0;
41 l_dummy NUMBER :=0;
42 l_not_found BOOLEAN;
43
44 CURSOR c1 IS
45 SELECT 1
46 FROM dual
47 WHERE
48 EXISTS (
49 SELECT 1
50 FROM pjm_project_parameters ppp
51 WHERE ppp.project_id = l_project_id
52 --
53 -- Bug 917915: should only return value if p_task_id is
54 -- null
55 AND p_task_id is null
56 );
57 CURSOR c2 IS
58 SELECT 1
59 FROM dual
60 WHERE
61 EXISTS (
62 SELECT 1
63 FROM oe_order_lines_all ool
64 WHERE ool.project_id = l_project_id
65 AND ool.task_id = NVL(p_task_id, ool.task_id)
66 );
67 CURSOR c3 IS
68 SELECT 1
69 FROM dual
70 WHERE
71 EXISTS (
72 SELECT 1
73 FROM wip_discrete_jobs job
74 WHERE job.project_id = l_project_id
75 AND job.task_id = NVL(p_task_id, job.task_id)
76 );
77 CURSOR c4 IS
78 SELECT 1
79 FROM dual
80 WHERE
81 EXISTS (
82 SELECT 1
83 FROM mtl_item_locations loc
84 WHERE loc.project_id = l_project_id
85 AND loc.task_id = NVL(p_task_id, loc.task_id)
86 );
87 CURSOR c5 IS
88 SELECT 1
89 FROM dual
90 WHERE
91 EXISTS (
92 SELECT 1
93 FROM mtl_material_transactions mmt
94 WHERE (mmt.source_project_id = l_project_id
95 AND mmt.source_task_Id = NVL(p_task_id, mmt.source_task_id)
96 ) );
97 CURSOR c6 IS
98 SELECT 1
99 FROM dual
100 WHERE
101 EXISTS (
102 SELECT 1
103 FROM mrp_forecast_dates frct
104 WHERE frct.project_id = l_project_id
105 AND frct.task_id = NVL(p_task_id, frct.task_id)
106 );
107 CURSOR c7 IS
108 SELECT 1
109 FROM dual
110 WHERE
111 EXISTS (
112 SELECT 1
113 FROM mrp_schedule_dates schd
114 WHERE schd.project_id = l_project_id
115 AND schd.task_id = NVL(p_task_id, schd.task_id)
116 );
117 CURSOR c8 IS
118 SELECT 1
119 FROM dual
120 WHERE
121 EXISTS (
122 SELECT 1
123 FROM po_distributions_all dist
124 WHERE dist.project_id = l_project_id
125 AND dist.task_id = NVL(p_task_id, dist.task_id)
126 );
127 CURSOR c9 IS
128 SELECT 1
129 FROM dual
130 WHERE
131 EXISTS (
132 SELECT 1
133 FROM po_req_distributions_all rdist
134 WHERE rdist.project_id = l_project_id
135 AND rdist.task_id = NVL(p_task_id, rdist.task_id)
136 );
137
138 CURSOR c10 IS
139 SELECT 1
140 FROM dual
141 WHERE
142 EXISTS (
143 SELECT 1
144 FROM wip_transactions wip
145 WHERE wip.project_id = l_project_id
146 AND wip.task_id = NVL(p_task_id, wip.task_id)
147 );
148
149 CURSOR c11 IS
150 SELECT 1
151 FROM dual
152 WHERE
153 EXISTS (
154 SELECT 1
155 FROM mtl_material_transactions mmt
156 WHERE (mmt.project_id = l_project_id
157 AND mmt.task_id = NVL(p_task_id, mmt.task_id)
158 ));
159
160 CURSOR c12 IS
161 SELECT 1
162 FROM dual
163 WHERE
164 EXISTS (
165 SELECT 1
166 FROM mtl_material_transactions mmt
167 WHERE (mmt.project_id = l_project_id
168 AND mmt.to_task_id = NVL(p_task_id, mmt.to_task_id)
169 ));
170
171 BEGIN
172
173 SELECT decode(count(1),0,0,1)
174 INTO pjm_active
175 FROM pjm_org_parameters
176 WHERE project_reference_enabled = 'Y';
177
178 IF ( pjm_active = 0 ) THEN
179 RETURN(0);
180 END IF;
181
182 IF (p_task_id IS NOT NULL) OR (p_project_id IS NOT NULL) THEN
183 IF p_project_id IS NULL THEN
184 SELECT tsk.project_id
185 INTO l_project_id
186 FROM pa_tasks tsk
187 WHERE task_id = p_task_id;
188 ELSE
189 l_project_id := p_project_id;
190 END IF;
191
192 OPEN c1;
193 FETCH c1 INTO l_dummy;
194 l_not_found := c1%NOTFOUND;
195 CLOSE c1;
196
197 IF l_not_found THEN
198 OPEN c2;
199 FETCH c2 INTO l_dummy;
200 l_not_found := c2%NOTFOUND;
201 CLOSE c2;
202 END IF;
203
204 IF l_not_found THEN
205 OPEN c3;
206 FETCH c3 INTO l_dummy;
207 l_not_found := c3%NOTFOUND;
208 CLOSE c3;
209 END IF;
210
211 IF l_not_found THEN
212 OPEN c4;
213 FETCH c4 INTO l_dummy;
214 l_not_found := c4%NOTFOUND;
215 CLOSE c4;
216 END IF;
217
218 IF l_not_found THEN
219 OPEN c5;
220 FETCH c5 INTO l_dummy;
221 l_not_found := c5%NOTFOUND;
222 CLOSE c5;
223 END IF;
224
225 IF l_not_found THEN
226 OPEN c6;
227 FETCH c6 INTO l_dummy;
228 l_not_found := c6%NOTFOUND;
229 CLOSE c6;
230 END IF;
231
232
233
234 IF l_not_found THEN
235 OPEN c7;
236 FETCH c7 INTO l_dummy;
237 l_not_found := c7%NOTFOUND;
238 CLOSE c7;
239 END IF;
240
241 IF l_not_found THEN
242 OPEN c8;
243 FETCH c8 INTO l_dummy;
244 l_not_found := c8%NOTFOUND;
245 CLOSE c8;
246 END IF;
247
248 IF l_not_found THEN
249 OPEN c9;
250 FETCH c9 INTO l_dummy;
251 l_not_found := c9%NOTFOUND;
252 CLOSE c9;
253 END IF;
254
255 IF l_not_found THEN
256 OPEN c10;
257 FETCH c10 INTO l_dummy;
258 l_not_found := c10%NOTFOUND;
259 CLOSE c10;
260 END IF;
261
262 IF l_not_found THEN
263 OPEN c11;
264 FETCH c11 INTO l_dummy;
265 l_not_found := c11%NOTFOUND;
266 CLOSE c11;
267 END IF;
268
269 IF l_not_found THEN
270 OPEN c12;
271 FETCH c12 INTO l_dummy;
272 l_not_found := c12%NOTFOUND;
273 CLOSE c12;
274 END IF;
275
276 -- Bug 3600806, add pjm table reference
277
278 IF l_not_found THEN
279 SELECT 2
280 INTO l_dummy
281 FROM dual
282 WHERE EXISTS (
283 SELECT 1
284 FROM pjm_default_tasks pjm
285 WHERE pjm.project_id = l_project_id
286 AND pjm.task_id = NVL(p_task_id, pjm.task_id)
287 );
288 END IF;
289 ELSE
290 l_dummy := -1;
291 END IF;
292
293 return(l_dummy);
294
295 EXCEPTION
296 WHEN NO_DATA_FOUND THEN
297 RETURN(0);
298 WHEN others THEN
299 RAISE;
300
301 END CheckUse_ProjectTask;
302
303 -- Function name : Checkuse_ProjOrg
304 -- Pre-reqs : None.
305 -- Function : Checks if project references are currently present
306 -- in the given organization
307 -- Parameters :
308 -- IN : p_project_id IN NUMBER Required
309 -- : p_org_id IN NUMBER Required
310 -- RETURNS :
311 -- Returns -1 if either argument is null.
312 -- Returns 1 if input Project argument is still referred
313 -- in input Org
314 --
315 -- This function does not check detailed status
316 -- such as closed sales order line, or canceled PO
317 -- line/shipments, etc.
318 --
319 -- Returns 0 if input Project argument is not referred in
320 -- the given org.
321 --
322 FUNCTION CheckUse_ProjOrg (p_project_id IN NUMBER,
323 p_org_id IN NUMBER)
324 RETURN NUMBER IS
325
326 retcode NUMBER :=0;
327
328 BEGIN
329
330 IF (p_org_id IS NOT NULL) AND (p_project_id IS NOT NULL) THEN
331
332 SELECT 1
333 INTO retcode
334 FROM dual
335 WHERE
336 EXISTS (
337 SELECT 1
338 FROM oe_order_lines_all ool
339 WHERE ool.project_id = p_project_id
340 AND ool.ship_from_org_id = p_org_id
341 ) OR
342 EXISTS (
343 SELECT 1
344 FROM wip_discrete_jobs job
345 WHERE job.project_id = p_project_id
346 AND job.organization_id = p_org_id
347 ) OR
348 EXISTS (
349 SELECT 1
350 FROM mtl_item_locations loc
351 WHERE loc.project_id = p_project_id
352 AND loc.organization_id = p_org_id
353 ) OR
354 EXISTS (
355 SELECT 1
356 FROM mtl_material_transactions mmt
357 WHERE (mmt.project_id = p_project_id
358 AND mmt.organization_id = p_org_id
359 )
360 OR (mmt.to_project_id = p_project_id
361 AND mmt.transfer_organization_id = p_org_id
362 )
363 OR (mmt.source_project_id = p_project_id
364 AND mmt.organization_id = p_org_id
365 )
366 ) OR
367 EXISTS (
368 SELECT 1
369 FROM mrp_forecast_dates frct
370 WHERE frct.project_id = p_project_id
371 AND frct.organization_id = p_org_id
372 ) OR
373 EXISTS (
374 SELECT 1
375 FROM mrp_schedule_dates schd
376 WHERE schd.project_id = p_project_id
377 AND schd.organization_id = p_org_id
378 ) OR
379 EXISTS (
380 SELECT 1
381 FROM po_distributions_all dist
382 WHERE dist.project_id = p_project_id
383 AND dist.destination_organization_id = p_org_id
384 ) OR
385 EXISTS (
386 SELECT 1
387 FROM po_req_distributions_all rdist
388 , po_requisition_lines_all rline
389 WHERE rdist.project_id = p_project_id
390 AND rline.requisition_line_id = rdist.requisition_line_id
391 AND rline.destination_organization_id = p_org_id
392 );
393 ELSE
394 retcode := -1;
395 END IF;
396
397 return(retcode);
398
399 EXCEPTION
400 WHEN NO_DATA_FOUND THEN
401 RETURN(0);
402 WHEN others THEN
403 RAISE;
404
405 END CheckUse_ProjOrg;
406
407 END PJM_PROJTASK_DELETION;
[Home] [Help]