[Home] [Help]
PACKAGE BODY: APPS.QA_RESULTS_WF_PKG
Source
1 PACKAGE BODY QA_RESULTS_WF_PKG as
2 /* $Header: qanotb.pls 115.2 2003/10/10 19:38:41 ksoh ship $ */
3
4
5
6 PROCEDURE process_updates (
7 itemtype IN VARCHAR2,
8 itemkey IN VARCHAR2,
9 actid IN NUMBER,
10 funcmode IN VARCHAR2,
11 result OUT NOCOPY VARCHAR2) IS
12
13 l_value varchar2(2000);
14 l_occurrence number;
15 l_plan_id number;
16 l_collection_id number;
17 l_PCA_ID number; --plan char action id
18 l_org_id number;
19 l_txnheader_id number;
20 QA_RES_UPDATE_FAILED exception;
21
22 l_char_id number;
23 l_wf_attr VARCHAR2(1000);
24
25 do_update BOOLEAN := FALSE;
26
27 elements qa_validation_api.ElementsArray;
28
29 error_array qa_validation_api.ErrorArray;
30 message_array qa_validation_api.MessageArray;
31 return_status VARCHAR2(1);
32 action_result VARCHAR2(1);
33 msg_count NUMBER;
34 msg_data VARCHAR2(2000);
35
36 cursor c1(c_pca_id NUMBER) IS
37 SELECT qpcao.token_name, qpcao.char_id
38 FROM qa_plan_char_action_outputs qpcao
39 WHERE qpcao.plan_char_action_id = c_pca_id
40 AND qpcao.token_name like 'X_%';
41
42 cursor txn_hdr_cur IS
43 SELECT qr.txn_header_id
44 FROM qa_results qr
45 WHERE qr.plan_id = l_plan_id
46 AND qr.collection_id = l_collection_id
47 AND qr.occurrence = l_occurrence;
48
49 BEGIN
50
51 IF (funcmode = 'RUN') THEN --this needs to be RUN i guess
52
53 /* insert into ilam1 values ('Got here... '||itemtype||'-'||itemkey||'-'||actid||'-'||funcmode);
54 commit; */
55 l_plan_id := to_number(wf_engine.getitemattrtext(
56 itemtype => itemtype,
57 itemkey => itemkey,
58 aname => 'PLAN_ID'));
59
60 l_org_id := to_number(wf_engine.getitemattrtext(
61 itemtype => itemtype,
62 itemkey => itemkey,
63 aname => 'ORG_ID'));
64
65 l_collection_id := wf_engine.getitemattrnumber(
66 itemtype => itemtype,
67 itemkey => itemkey,
68 aname => 'COLLECTION_ID');
69
70 l_occurrence := wf_engine.getitemattrnumber(
71 itemtype => itemtype,
72 itemkey => itemkey,
73 aname => 'OCCURRENCE');
74
75 l_PCA_ID := wf_engine.getitemattrnumber(
76 itemtype => itemtype,
77 itemkey => itemkey,
78 aname => 'PCA_ID');
79 --insert into ilam1 values ('Before Loop'); commit;
80 FOR token_rec IN c1(l_PCA_ID) LOOP
81
82 do_update := TRUE;
83
84 l_char_id := token_rec.char_id;
85 l_wf_attr := token_rec.token_name;
86
87 l_value := wf_engine.getitemattrtext(
88 itemtype => itemtype,
89 itemkey => itemkey,
90 aname => l_wf_attr);
91
92 elements(l_char_id).value := l_value;
93 END LOOP;
94
95 if (do_update) THEN
96 qa_results_pub.update_row(
97 p_api_version => 1.0,
98 p_init_msg_list => fnd_api.g_true,
99 p_commit => fnd_api.g_true,
100 p_plan_id => l_plan_id,
101 p_org_id => l_org_id,
102 p_enabled_flag => 2,
103 p_collection_id => l_collection_id,
104 p_occurrence => l_occurrence,
105 x_row_elements => elements,
106 x_msg_count => msg_count,
107 x_msg_data => msg_data,
108 x_error_array => error_array,
109 x_message_array => message_array,
110 x_return_status => return_status,
111 x_action_result => action_result);
112
113 if (return_status = FND_API.G_RET_STS_ERROR)
114 then
115 FOR i IN error_array.FIRST .. error_array.LAST LOOP
116 qa_skiplot_utility.insert_error_log
117 (p_module_name => 'qa_results_wf_pkg.process_updates',
118 p_error_message => itemtype|| '-' || itemkey
119 || ' *** '
120 || 'char_id: '||error_array(i).element_id
121 || ' Error: ' || error_array(i).error_code);
122 END LOOP;
123 raise QA_RES_UPDATE_FAILED;
124 end if;--endif for x_return_status check
125
126 --insert into ilam1 values ('Update has been called');
127
128 OPEN txn_hdr_cur;
129 FETCH txn_hdr_cur INTO l_txnheader_id;
130 CLOSE txn_hdr_cur;
131
132 QA_PARENT_CHILD_PKG.insert_history_auto_rec(
133 l_plan_id,
134 l_txnheader_id,
135 1, 4);
136 --commit;
137 END IF; -- end if do_update check
138
139 -- if commit is needed for above call
140 -- do an autonomous commit, for now i think commit not needed
141
142 END IF; --end if funcmode = RESPOND
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 wf_core.context('qa_results_wf_pkg', 'process_updates',
147 itemtype, itemkey, to_char(actid), funcmode);
148 raise;
149
150 END process_updates;
151
152 PROCEDURE set_results_url (itemtype IN VARCHAR2,
153 itemkey IN VARCHAR2,
154 actid IN NUMBER,
155 funcmode IN VARCHAR2,
156 result OUT NOCOPY VARCHAR2)
157
158 IS
159 url_string VARCHAR2(1000);
160 l_plan_id VARCHAR2(1000);
161 l_template_plan_id QA_PLANS.TEMPLATE_PLAN_ID%TYPE;
162 l_collection_id NUMBER;
163 l_occurrence NUMBER;
164 extension VARCHAR2(5);
165 search_clause VARCHAR2(1000);
166 search_vars VARCHAR2(1000);
167 HGRID_QUERY_FROM_PCTXT VARCHAR2(250) := 'FROM_PCTXT';
168 PCTXT_VAR_PREFIX VARCHAR2(250) := '__QA_SSQR_PCTXT_';
169 PCTXT_REQUERY_HGRID VARCHAR2(250) := PCTXT_VAR_PREFIX || 'RequeryHgrid';
170 PCTXT_CLEAR_HGRID VARCHAR2(250) := PCTXT_VAR_PREFIX || 'ClearHgrid';
171 PCTXT_HGRID_SEARCH_CLAUSE VARCHAR2(250) := PCTXT_VAR_PREFIX || 'HgridSearchClauseStr';
172 PCTXT_HGRID_SEARCH_VARS VARCHAR2(250) := PCTXT_VAR_PREFIX || 'HgridSearchVarsStr';
173 cursor c(p_plan_id VARCHAR2) is
174 select template_plan_id
175 from qa_plans
176 where plan_id = p_plan_id;
177
178
179 BEGIN
180 l_plan_id := wf_engine.getitemattrtext(
181 itemtype => itemtype,
182 itemkey => itemkey,
183 aname => 'PLAN_ID');
184
185 l_collection_id := wf_engine.getitemattrnumber(
186 itemtype => itemtype,
187 itemkey => itemkey,
188 aname => 'COLLECTION_ID');
189
190 l_occurrence := wf_engine.getitemattrnumber(
191 itemtype => itemtype,
192 itemkey => itemkey,
193 aname => 'OCCURRENCE');
194
195 -- determine if the function name contains NCM_ or CAR_ based on template plan id
196 open c(l_plan_id);
197 fetch c into l_template_plan_id;
198 close c;
199
200 if l_template_plan_id in (18, 35) then
201 extension := 'NCM_';
202 elsif l_template_plan_id = 65 then
203 extension := 'CAR_';
204 else
205 extension := '';
206 end if;
207
208 search_clause := 'plan_id=:1 and occurrence=:2 and collection_id=:3';
209 search_clause := replace(search_clause, ' ', '%20');
210 search_clause := replace(search_clause, '=', '%3D');
211 search_vars := l_plan_id || '@' || l_occurrence || '@' || l_collection_id;
212
213 url_string := 'JSP:/OA_HTML/OA.jsp?OAFunc=QA_SSQR_HGRID_' || extension || 'PAGE' ||
214 '&' || 'OAHP=QA_SSQR_APPLICATION_MENU' ||
215 '&' || 'OASF=QA_SSQR_HGRID_' || extension || 'PAGE' ||
216 '&' || PCTXT_REQUERY_HGRID || '=' || HGRID_QUERY_FROM_PCTXT ||
217 '&' || PCTXT_HGRID_SEARCH_CLAUSE || '=' || search_clause ||
218 '&' || PCTXT_HGRID_SEARCH_VARS || '=' || search_vars ||
219 '&' || 'RootPlanId=' || l_plan_id ||
220 '&' || 'NtfId=-' ||
221 '&' || '#NID-';
222
223 wf_engine.setitemattrtext(
224 itemtype => itemtype,
225 itemkey => itemkey,
226 aname => 'RESULTS_URL',
227 avalue => url_string);
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 wf_core.context('qa_results_wf_pkg', 'set_results_url',
232 itemtype, itemkey, to_char(actid), funcmode);
233 raise;
234
235 END set_results_url;
236
237 END qa_results_wf_pkg;