1 PACKAGE BODY qa_location AS
2 /* $Header: qlthrb.plb 120.0 2005/05/24 18:25:10 appldev noship $ */
3
4 PROCEDURE qa_predel_validation (p_location_id in number) IS
5 --
6 -- final is the eventual dynamic SQL. I am being extremely
7 -- conservative here to make use of the array variation of
8 -- dbms_sql.parse procedure. This becomes needed if the no. of
9 -- collection plans that contain Location is more than 1,600
10 -- or so.
11 --
12 final dbms_sql.varchar2s;
13 l_most_common varchar2(30);
14 loc_code hr_locations_all.location_code%TYPE; -- Incoming Location Code
15 c integer; -- Cursor
16 n integer; -- No. of rows returned
17 l_status integer;
18
19 --
20 -- Completely modified to take advantage of the new
21 -- qa_char_indexes_pkg. Part of Bug 3930666.
22 -- bso Tue Apr 5 17:50:48 PDT 2005
23 --
24
25 BEGIN
26
27 hr_utility.set_location('QA_LOCATION.QA_PREDEL_VALIDATION', 1);
28
29 --
30 -- Bug 3930666. Construct the decode statement
31 -- using the efficient qa_char_indexes_pkg.
32 --
33 l_status := qa_char_indexes_pkg.construct_decode_function(
34 qa_ss_const.LOCATION, 'QR.', l_most_common, final);
35
36 IF l_status = qa_char_indexes_pkg.ERR_ELEMENT_NOT_IN_USE THEN
37 -- Great! Element not in use, simply return.
38 RETURN;
39
40 ELSIF l_status < 0 THEN
41 -- Extremely unusual
42 hr_utility.set_message(250, 'QA_LOC_RESULTS');
43 hr_utility.raise_error;
44
45 ELSE
46 -- Start the search process.
47 --
48 -- Find the location code for the given location_id
49 --
50 select location_code into loc_code
51 from hr_locations_all
52 where location_id = p_location_id;
53
54 --
55 -- Bug 3930666. Use bind variable to prevent error
56 -- when single-quote appears in loc_code.
57 --
58 -- Coding is greatly simplified using
59 -- qa_char_indexes_pkg which returns a DECODE
60 -- function such as DECODE(qr.plan_id, 111, qr.CHARACTER1,
61 -- 112, qr.CHARACTER2, qr.CHARACTER4) or a simple
62 -- qr.CHARACTER1 if all reside in the same column.
63 -- All we need to do is to prefix this function with
64 -- a SELECT from qa_results to check if the input
65 -- loc_code (:2 below) is present.
66 --
67 -- bso Tue Apr 5 17:52:17 PDT 2005
68 --
69
70 final(0) := 'SELECT 1 FROM qa_plan_chars qpc, qa_results qr
71 WHERE qpc.char_id = :1 AND qpc.plan_id = qr.plan_id AND :2 = ';
72
73 c := dbms_sql.open_cursor;
74 dbms_sql.parse(c, final, 0, final.last, false, dbms_sql.native);
75 dbms_sql.bind_variable(c, ':1', qa_ss_const.LOCATION);
76 dbms_sql.bind_variable(c, ':2', loc_code);
77 n := dbms_sql.execute_and_fetch(c);
78 dbms_sql.close_cursor(c);
79
80 --
81 -- n = 1 means yes it is being used, so we
82 -- veto the deletion by raising an exception.
83 --
84 if n = 1 then
85 hr_utility.set_message(250, 'QA_LOC_RESULTS');
86 hr_utility.raise_error;
87 end if;
88 END IF;
89
90 END qa_predel_validation;
91
92 END qa_location;
[Home] [Help]