1 package body PA_ASSET_UTILS as
2 --$Header: PAXAUTLB.pls 115.1 99/07/16 15:18:41 porting ship $
3
4 --
5 -- FUNCTION
6 -- check_unique_asset_name
7 -- PURPOSE
8 -- This function returns 1 if asset name is not already
9 -- used for assets on this project and returns 0 if name is used.
10 -- If Oracle error occurs, Oracle error number is returned.
11 -- HISTORY
12 -- 28-OCT-95 C. Conlin Created
13 --
14 --
15 function check_unique_asset_name (x_asset_name IN varchar2,
16 x_project_id IN number,
17 x_rowid IN varchar2 ) return number
18 is
19 cursor c1 is
20 select asset_name
21 from pa_project_assets
22 where project_id = x_project_id
23 AND asset_name = x_asset_name
24 AND (x_ROWID IS NULL OR x_ROWID <> pa_project_assets.ROWID);
25
26 c1_rec c1%rowtype;
27
28 begin
29 if (x_asset_name is null ) then
30 return(null);
31 end if;
32
33 open c1;
34 fetch c1 into c1_rec;
35 if c1%notfound then
36 return(1);
37 else
38 return(0);
39 end if;
40 close c1;
41
42 exception
43 when others then
44 return(SQLCODE);
45
46 end check_unique_asset_name;
47
48 --
49 -- FUNCTION
50 -- check_valid_asset_number
51 -- PURPOSE
52 -- This function returns
53 -- 1 if asset number has not been used by PA.
54 -- 0 if asset number is already in use in PA
55 -- (further checking should be done to make
56 -- sure the asset is not in use in FA. See
57 -- the FA_MASS_ADD_VALIDATE package.
58 --
59 -- If Oracle error occurs, Oracle error number is returned.
60 --
61 --
62 -- HISTORY
63 -- 28-OCT-95 C. Conlin Created
64 --
65 --
66
67 function check_valid_asset_number (x_asset_number IN varchar2,
68 x_rowid IN varchar2 ) return number
69 is
70
71 cursor c1 is
72 select asset_number
73 from pa_project_assets
74 where asset_number = x_asset_number
75 AND (x_ROWID IS NULL OR x_ROWID <> pa_project_assets.ROWID);
76
77 c1_rec c1%rowtype;
78
79 begin
80 if (x_asset_number is null ) then
81 return(null);
82 end if;
83
84 open c1;
85 fetch c1 into c1_rec;
86 --asset number used in PA?
87 if c1%notfound then
88 return(1);
89 else
90 return(0);
91 end if;
92 close c1;
93
94 exception
95 when others then
96 return(SQLCODE);
97
98 end check_valid_asset_number;
99
100
101 --
102 -- FUNCTION
103 -- check_asset_references
104 -- PURPOSE
105 -- This function returns 1 if an asset can be deleted, and
106 -- returns 0 if the asset has references which prevent it
107 -- from being deleted.
108 --
109 -- If Oracle error occurs, Oracle error number is returned.
110 -- HISTORY
111 -- 28-OCT-95 C. Conlin Created
112 --
113 --
114
115 function check_asset_references (x_project_asset_id IN number)
116 return number
117 is
118 cursor c1 is
119 select project_asset_id
120 from pa_project_asset_lines
121 where project_asset_id = x_project_asset_id;
122
123 c1_rec c1%rowtype;
124
125 begin
126
127 if (x_project_asset_id is null ) then
128 return(null);
129 end if;
130
131 open c1;
132 fetch c1 into c1_rec;
133 if c1%notfound then
134 return(1);
135 else
136 return(0);
137 end if;
138 close c1;
139
140 exception
141 when others then
142 return(SQLCODE);
143
144 end check_asset_references;
145
146 -- FUNCTION
147 -- check_fa_asset_num
148 -- PURPOSE
149 -- This function returns a 1 if the asset number being
150 -- checked is one of the valid FA asset numbers for the
151 -- given pa_asset_id. This function returns a 0 if the
152 -- asset number is NOT a valid FA asset number for the
153 -- given pa_asset_id.
154 --
155 -- If Oracle error occurs, Oracle error number is returned.
156 -- HISTORY
157 -- 29-JAN-96 C. Conlin Created
158 --
159 --
160 function check_fa_asset_num(pa_asset_id IN NUMBER,
161 check_asset_number IN VARCHAR2) return number is
162
163 cursor c1 (pa_asset_id NUMBER) is
164 select distinct faa.asset_number
165 from fa_additions faa,
166 fa_asset_invoices fai,
167 pa_project_asset_lines ppal
168 where
169 ppal.project_asset_id = pa_asset_id AND
170 ppal.project_asset_line_id = fai.project_asset_line_id AND
171 fai.asset_id = faa.asset_id;
172 c1_rec c1%ROWTYPE;
173
174 BEGIN
175 FOR c1_rec in c1(pa_asset_id) LOOP
176 IF c1_rec.asset_number = check_asset_number THEN
177 RETURN(1);
178 END IF;
179 END LOOP;
180 RETURN(0);
181 EXCEPTION
182 when others then
183 return(SQLCODE);
184 end check_fa_asset_num;
185
186
187 FUNCTION fa_implementation_status RETURN VARCHAR2 is
188 x_status VARCHAR2(1);
189 x_dummy NUMBER;
190 BEGIN
191 SELECT location_flex_structure
192 INTO x_dummy
193 FROM fa_system_controls;
194
195 x_status := 'Y';
196 return(x_status);
197 EXCEPTION
198 WHEN OTHERS THEN
199 x_status := 'N';
200 return(x_status);
201 END fa_implementation_status;
202
203
204
205 end pa_asset_utils;