1 PACKAGE qa_ss_results AUTHID CURRENT_USER AS
2 /* $Header: qltssreb.pls 120.11 2011/04/11 18:20:51 ntungare ship $ */
3
4 --
5 -- Post a result to the database. This is a wrapper to the QA API
6 -- qa_results_api.insert_row. Do not perform commit. Most of the
7 -- parameters are self explanatory.
8 --
9 -- x_result is a flattened @-separated list of <char_id>=<value>.
10 -- x_result1 is an @-sepated list of <char_id>=<id> used for
11 -- hardcoded referenced elements that have known IDs.
12 -- x_result2 is not used but can be reserved for
13 -- expansion if one VARCHAR2 is not enough.
14 -- x_enabled maps to the status flag in qa_results
15 -- (i.e., null or 2 for enabled, 1 for disabled).
16 -- x_committed is whether to commit the row afterwards,
17 -- 1 = yes, others = no.
18 -- x_messages is an @-separated string of error messages if there
19 -- is any error.
20 --
21 -- Return 0 if OK
22 -- Return -1 if error.
23 --
24 FUNCTION nontxn_post_result(
25 x_occurrence OUT NOCOPY NUMBER,
26 x_org_id IN NUMBER,
27 x_plan_id IN NUMBER,
28 x_spec_id IN NUMBER,
29 x_collection_id IN NUMBER,
30 x_result IN VARCHAR2,
31 x_result1 IN VARCHAR2,
32 x_result2 IN VARCHAR2, -- not used yet, for future expansion
33 x_enabled IN INTEGER,
34 x_committed IN INTEGER,
35 x_messages OUT NOCOPY VARCHAR2)
36 RETURN INTEGER;
37
38 --
39 -- The overloaded method is used for transaction only
40 --
41 FUNCTION post_result(
42 x_occurrence IN OUT NOCOPY NUMBER,
43 x_org_id IN NUMBER,
44 x_plan_id IN NUMBER,
45 x_spec_id IN NUMBER,
46 x_collection_id IN NUMBER,
47 x_result IN VARCHAR2,
48 x_result1 IN VARCHAR2,
49 x_result2 IN VARCHAR2, -- not used yet, for future expansion
50 x_enabled IN INTEGER,
51 x_committed IN INTEGER,
52 x_transaction_number IN NUMBER,
53 x_messages OUT NOCOPY VARCHAR2)
54 RETURN INTEGER;
55
56
57 --
58 -- The overloaded method is used for ssqr.
59 -- In addition to the code in post_result,
60 -- we call insert history and automatic records.
61 --
62 -- 12.1 QWB Usability Improvements
63 -- Added 2 new paramters to pass the aggregated values for
64 -- the parent plan collection elements to the JAVA layer.
65 --
66 --
67 -- bug 11896067
68 -- Added a new parameter, p_background_flag
69 --
70 FUNCTION ssqr_post_result(
71 x_occurrence IN OUT NOCOPY NUMBER,
72 x_org_id IN NUMBER,
73 x_plan_id IN NUMBER,
74 x_spec_id IN NUMBER,
75 x_collection_id IN NUMBER,
76 x_txn_header_id IN NUMBER,
77 x_par_plan_id IN NUMBER,
78 x_par_col_id IN NUMBER,
79 x_par_occ IN NUMBER,
80 x_result IN VARCHAR2,
81 x_result1 IN VARCHAR2,
82 x_result2 IN VARCHAR2, -- not used yet, for future expansion
83 x_enabled IN INTEGER,
84 x_committed IN INTEGER,
85 x_transaction_number IN NUMBER,
86 x_messages OUT NOCOPY VARCHAR2,
87 x_agg_elements OUT NOCOPY VARCHAR2,
88 x_agg_val OUT NOCOPY VARCHAR2,
89 p_last_update_date IN DATE DEFAULT SYSDATE,
90 p_background_flag IN NUMBER DEFAULT 0)
91 RETURN INTEGER;
92
93 --
94 -- update_result
95 --
96 FUNCTION update_result(
97 x_occurrence IN NUMBER,
98 x_org_id IN NUMBER,
99 x_plan_id IN NUMBER,
100 x_spec_id IN NUMBER,
101 x_collection_id IN NUMBER,
102 x_result IN VARCHAR2,
103 x_result1 IN VARCHAR2,
104 x_result2 IN VARCHAR2, -- not used yet, for future expansion
105 x_enabled IN INTEGER,
106 x_committed IN INTEGER,
107 x_transaction_number IN NUMBER,
108 x_messages OUT NOCOPY VARCHAR2)
109 RETURN INTEGER;
110
111
112 --
113 -- Delete a result. Do not perform commit.
114 --
115 PROCEDURE delete_result(
116 x_plan_id IN NUMBER,
117 x_collection_id IN NUMBER,
118 x_occurrence IN NUMBER);
119
120 --
121 -- Batch delete a set of results (supply occurrences in
122 -- comma-separated list.) Do not perform commit.
123 --
124 PROCEDURE delete_results(
125 x_plan_id IN NUMBER,
126 x_collection_id IN NUMBER,
127 x_occurrences IN VARCHAR2);
128
129 --
130 -- Perform database commit. Do not use in transaction integration,
131 -- otherwise we will be committing the parent's data without their
132 -- knowing! Actions will be fired in the background.
133 --
134 PROCEDURE commit_results;
135
136 PROCEDURE wrapper_fire_action (
137 q_collection_id IN NUMBER,
138 q_return_status OUT NOCOPY VARCHAR2,
139 q_msg_count OUT NOCOPY NUMBER,
140 q_msg_data OUT NOCOPY VARCHAR2);
141
142
143 PROCEDURE GET_COLLECTION_ID (x_collection_id OUT NOCOPY NUMBER);
144
145 --
146 -- Bug 6881303
147 -- added 2 new elements, one a comma separated list of the
148 -- Parent collection elements that would receive aggregated
149 -- values and the other a comma separated list of the
150 -- aggregated values.
151 -- ntungare Fri Mar 21 01:19:03 PDT 2008
152 --
153 FUNCTION ssqr_update_result(
154 x_occurrence IN NUMBER,
155 x_org_id IN NUMBER,
156 x_plan_id IN NUMBER,
157 x_spec_id IN NUMBER,
158 x_collection_id IN NUMBER,
159 x_txn_header_id IN NUMBER,
160 x_par_plan_id IN NUMBER,
161 x_par_col_id IN NUMBER,
162 x_par_occ IN NUMBER,
163 x_result IN VARCHAR2,
164 x_result1 IN VARCHAR2,
165 x_result2 IN VARCHAR2, -- not used yet, for future expansion
166 x_enabled IN INTEGER,
167 x_committed IN INTEGER,
168 x_transaction_number IN NUMBER,
169 x_messages OUT NOCOPY VARCHAR2,
170 x_agg_elements OUT NOCOPY VARCHAR2,
171 x_agg_val OUT NOCOPY VARCHAR2,
172 p_last_update_date IN DATE DEFAULT SYSDATE)
173 RETURN INTEGER;
174
175 --
176 -- bug 5306909
177 -- Added p_last_update_date parameter. This parameter is used to
178 -- check whether the record which the user is trying to update has
179 -- been updated already by some other user.
180 -- ntungare Mon Apr 10 07:00:21 PDT 2006
181 --
182 FUNCTION ssqr_lock_row (
183 p_occurrence IN NUMBER,
184 p_plan_id IN NUMBER,
185 p_last_update_date IN DATE,
186 x_status OUT NOCOPY VARCHAR2)
187 RETURN INTEGER;
188
189 --
190 -- 12.1 QWB Usability Improvements
191 -- Added 2 new parameters x_charid_str and x_id_str
192 -- to return comma separated strings of HC
193 -- elements and the Normalized Id values
194 --
195 FUNCTION ssqr_validate_row (
196 p_occurrence IN OUT NOCOPY NUMBER,
197 p_org_id IN NUMBER,
198 p_plan_id IN NUMBER,
199 p_spec_id IN NUMBER,
200 p_collection_id IN NUMBER,
201 p_result IN VARCHAR2,
202 p_result1 IN VARCHAR2,
203 p_result2 IN VARCHAR2, -- not used yet, for future expansion
204 p_enabled IN INTEGER,
205 p_committed IN INTEGER,
206 p_transaction_number IN NUMBER,
207 p_transaction_id IN NUMBER DEFAULT 0,
208 -- p_who_created_by IN NUMBER := fnd_global.user_id,
209 x_messages OUT NOCOPY VARCHAR2,
210 x_charid_str OUT NOCOPY VARCHAR2,
211 x_id_str out NOCOPY VARCHAR2)
212 RETURN INTEGER ;
213
214
215 TYPE mesg_table IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
216
217
218 -- Bug 4502450 R12. eSig Status support in Multirow UQR
219 -- Functions checks if the current plan row has PENDING eSig
220 -- Status, if not it checks all the parent plans for eSig Status
221 -- if for corresponding parent row has eSig Status as PENDING
222 -- fills the message array and returns -1
223 -- rest is taken care in updateRow() method of QualityResultsEOImpl
224 -- saugupta Wed, 24 Aug 2005 08:51:28 -0700 PDT
225 FUNCTION validate_esig_for_update(
226 p_plan_id IN NUMBER,
227 p_plan_collection_id IN NUMBER,
228 p_plan_occurrence IN NUMBER)
229 RETURN BOOLEAN;
230
231 -- bug 4658275. eSig functionality support in QWB
232 -- this new method checks if user can insert a new
233 -- child row if ERES is enables
234 -- saugupta Tue, 18 Oct 2005 02:55:19 -0700 PDT
235 FUNCTION validate_esig_for_insert(p_plan_id IN NUMBER,
236 p_plan_collection_id IN NUMBER,
237 p_plan_occurrence IN NUMBER)
238 RETURN BOOLEAN;
239
240
241 -- R12 Project MOAC 4637896
242 -- For modularity, exposing this method for use by
243 -- qa_mqa_results
244 --
245 -- Parse out the error messages in the ErrorArray
246 -- returned by the validation API and append them
247 -- into an @-separated string. If an error message
248 -- contains @, it will be doubly encoded.
249 --
250 PROCEDURE get_error_messages(
251 p_errors IN qa_validation_api.ErrorArray,
252 p_plan_id IN NUMBER,
253 x_messages OUT NOCOPY VARCHAR2);
254
255 PROCEDURE post_error_messages (p_errors IN qa_validation_api.ErrorArray,
256 plan_id NUMBER);
257
258 -- R12.1 QWB Usability Improvements project
259 -- Function to perform deletetion of rows
260 --
261 FUNCTION delete_row(p_plan_id IN NUMBER,
262 p_collection_id IN NUMBER,
263 p_occurrence IN NUMBER,
264 p_org_id IN NUMBER,
265 p_txn_header_id IN NUMBER,
266 p_par_plan_id IN NUMBER DEFAULT -1,
267 p_par_col_id IN NUMBER DEFAULT -1,
268 p_par_occ IN NUMBER DEFAULT -1)
269 RETURN VARCHAR2;
270
271 END qa_ss_results;