[Home] [Help]
PACKAGE BODY: APPS.QA_MQA_MWA_API
Source
1 PACKAGE BODY qa_mqa_mwa_api AS
2 /* $Header: qamwab.pls 120.3 2007/11/14 10:07:43 bhsankar ship $ */
3
4 /*
8 32: Customer
5 Context elements for QA WMS Quality Check
6 10: Content Item
7 11: Item Category
9 146: Ship To Location
10 173: Container Item
11 -- 150: License Plate Number
12 */
13 CtxCharIds CtxElemCharIdTab := CtxElemCharIdTab(10, 11, 32, 146, 173);
14
15 --
16 -- Return 1 if the application p_short_name is installed.
17 -- Wrapper to fnd_installation.get_app_info (which, having
18 -- a Boolean return value, is not compatible with current
19 -- JDBC versions.
20 --
21 FUNCTION app_installed(p_short_name IN VARCHAR2) RETURN NUMBER IS
22 l_status varchar2(1);
23 l_industry varchar2(10);
24 l_schema varchar2(30);
25 dummy boolean;
26 BEGIN
27 dummy := fnd_installation.get_app_info(p_short_name, l_status,
28 l_industry, l_schema);
29
30 --
31 -- l_status will be 'I' if installed or 'N' if not.
32 -- anagarwa: I added another status, 'S' because it is possible
33 -- to have this status for some users. If not taken care of,
34 -- this can cause the disappearnce of Quality button in WIP move.
35 -- This was verified by '[email protected]'
36 -- For more info see bug #1716380
37 --
38 IF l_status = 'I'OR l_status = 'S' THEN
39 RETURN 1;
40 END IF;
41
42 RETURN 0;
43 END app_installed;
44
45
46 PROCEDURE transaction_completed(collection_id IN NUMBER,
47 commit_flag IN VARCHAR2 DEFAULT 'Y') IS
48
49 l_commit VARCHAR2(1);
50 l_status VARCHAR2(1);
51 l_msg_count NUMBER;
52 l_msg_data VARCHAR2(2000);
53 BEGIN
54 IF commit_flag = 'Y' THEN
55 l_commit := fnd_api.g_true;
56 ELSE
57 l_commit := fnd_api.g_false;
58 END IF;
59 qa_result_grp.enable(
60 p_api_version => 1.0,
61 p_collection_id => collection_id,
62 p_commit => l_commit,
63 p_return_status => l_status,
64 p_msg_count => l_msg_count,
65 p_msg_data => l_msg_data);
66 END transaction_completed;
67
68
69 PROCEDURE explode_wms_lpn(
70 p_lpn_id IN NUMBER,
71 p_org_id IN NUMBER,
72 x_content_table OUT NOCOPY WMS_CONTAINER_PUB.WMS_CONTAINER_TBL_TYPE,
73 x_elements OUT NOCOPY qa_txn_grp.ElementsArray,
74 x_element_ids OUT NOCOPY CtxElemIdTab) IS
75
76 msg_count NUMBER;
77 msg_data VARCHAR2(2000);
78 return_status VARCHAR2(500);
79
80 -- Bug 6622697
81 -- Variables to collect cursor records.
82 -- bhsankar Tue Nov 13 22:08:33 PST 2007
83 --
84 l_ctxid number;
85 l_ctxvalue varchar2(2000);
86
87 cursor ContainerItem(lpnID NUMBER, orgID NUMBER) is
88 select wlpn.inventory_item_id, msik.concatenated_segments
89 from wms_license_plate_numbers wlpn, mtl_system_items_kfv msik
90 where wlpn.lpn_id = lpnID and
91 msik.inventory_item_id = wlpn.inventory_item_id and
92 msik.organization_id = orgID;
93
94 cursor Customer(lpnID NUMBER) is
95 select wdd.customer_id, rc.customer_name
96 from wsh_delivery_details wdd, qa_customers_lov_v rc
97 where wdd.lpn_id = lpnID and
98 wdd.customer_id = rc.customer_id;
99
100 cursor ShipToLocation(lpnID NUMBER) is
101 select wdd.ship_to_location_id, tl.description
102 from wsh_delivery_details wdd, hr_locations_all_tl tl
103 where wdd.lpn_id = lpnID and
104 wdd.ship_to_location_id = tl.location_id and
105 tl.language = userenv('LANG')
106 union
107 select wdd.ship_to_location_id,
108 substr(hz.address1,1,50) description
109 from wsh_delivery_details wdd, hz_locations hz
110 where wdd.lpn_id = lpnID and
111 wdd.ship_to_location_id = hz.location_id;
112
113 BEGIN
114
115 WMS_CONTAINER_PUB.Explode_LPN(
116 p_api_version => 1.0,
117 p_init_msg_list => fnd_api.g_false,
118 p_commit => fnd_api.g_false,
119 x_return_status => return_status,
120 x_msg_count => msg_count,
121 x_msg_data => msg_data,
122 p_lpn_id => p_lpn_id,
123 p_explosion_level => 0,
124 x_content_tbl => x_content_table
125 );
126
127 open ContainerItem(p_lpn_id, p_org_id);
128 -- bug 6622697
129 -- If there are no records then the array element
130 -- would not at all exist raising a "No data found"
131 -- exception whenever it is accessed, hence
132 -- fetching into the variables rather than
133 -- directly into the array elements.
134 -- bhsankar Tue Nov 13 22:08:33 PST 2007
135 fetch ContainerItem into l_ctxid, l_ctxvalue;
136 x_element_ids(CtxCharIds(5)).ID := l_ctxid;
137 x_elements(CtxCharIds(5)).value := l_ctxvalue;
138 close ContainerItem;
139
140 open Customer(p_lpn_id);
141 -- bug 6622697
142 -- If there are no records then the array element
143 -- would not at all exist raising a "No data found"
144 -- exception whenever it is accessed, hence
145 -- fetching into the variables rather than
146 -- directly into the array elements.
147 -- bhsankar Tue Nov 13 22:08:33 PST 2007
148 l_ctxid := null;
149 l_ctxvalue := null;
150
151 fetch Customer into l_ctxid, l_ctxvalue;
152 x_element_ids(CtxCharIds(3)).ID := l_ctxid;
153 x_elements(CtxCharIds(3)).value := l_ctxvalue;
157 -- bug 6622697
154 close Customer;
155
156 open ShipToLocation(p_lpn_id);
158 -- If there are no records then the array element
159 -- would not at all exist raising a "No data found"
160 -- exception whenever it is accessed, hence
161 -- fetching into the variables rather than
162 -- directly into the array elements.
163 -- bhsankar Tue Nov 13 22:08:33 PST 2007
164 l_ctxid := null;
165 l_ctxvalue := null;
166
167 fetch ShipToLocation into l_ctxid, l_ctxvalue;
168 x_element_ids(CtxCharIds(4)).ID := l_ctxid;
169 x_elements(CtxCharIds(4)).value := l_ctxvalue;
170 close ShipToLocation;
171
172 END explode_wms_lpn;
173
174
175 PROCEDURE evaluate_triggers(
176 p_lpn_id IN NUMBER,
177 p_txn_number IN NUMBER,
178 p_org_id IN NUMBER,
179 x_plan_contexts_str OUT NOCOPY VARCHAR2,
180 x_plan_ctxs_ids_str OUT NOCOPY VARCHAR2,
181 x_plan_txn_ids_str OUT NOCOPY VARCHAR2) IS
182
183 x_content_table WMS_CONTAINER_PUB.WMS_CONTAINER_TBL_TYPE;
184
185 counter1 NUMBER := 0;
186 counter2 NUMBER := 0;
187 NoAvailablePlans NUMBER := 0;
188 NoContentItems NUMBER := 0;
189
190 AvailablePlanTxns PlanTxnTab;
191
192 ItemCatVal VARCHAR2(122) := null;
193 ItemCatID NUMBER := null;
194 Item VARCHAR2(40);
195 ApplicablePlanCtx VARCHAR2(1000);
196 ApplicablePlanCtxId VARCHAR2(1000);
197
198 elements qa_txn_grp.ElementsArray;
199 element_ids CtxElemIdTab;
200
201 CURSOR AvailablePlans(txn_number NUMBER, orgID NUMBER) IS
202 SELECT qpt.plan_transaction_id, qp.plan_id
203 FROM qa_plan_transactions qpt, qa_plans qp
204 WHERE qpt.transaction_number = txn_number
205 AND qpt.plan_id = qp.plan_id
206 AND qp.organization_id = orgID
207 AND qpt.enabled_flag = 1;
208
209 BEGIN
210 x_plan_contexts_str := null;
211 x_plan_ctxs_ids_str := null;
212 x_plan_txn_ids_str := null;
213
214 For i in AvailablePlans(p_txn_number, p_org_id) Loop
215 counter1 := counter1 + 1;
216 AvailablePlanTxns(counter1).PlanTxnID :=
217 i.plan_transaction_id;
218 AvailablePlanTxns(counter1).PlanID :=
219 i.plan_id;
220 End Loop;
221
222 NoAvailablePlans := counter1;
223
224 explode_wms_lpn(
225 p_lpn_id,
226 p_org_id,
227 x_content_table,
228 elements,
229 element_ids);
230
231 NoContentItems := x_content_table.COUNT;
232
233 For counter1 in 1..NoContentItems Loop
234 if (x_content_table(counter1).content_type = 1) and
235 (x_content_table(counter1).content_item_id is not null) then
236 Item := qa_flex_util.item(
237 x_org_id => p_org_id,
238 x_item_id => x_content_table(counter1).content_item_id);
239 elements(CtxCharIds(1)).value := Item; -- content_item
240 element_ids(CtxCharIds(1)).ID := x_content_table(counter1).content_item_id;
241
242 QA_FLEX_UTIL.get_item_category_val(
243 p_org_id => p_org_id,
244 p_item_val => null,
245 p_item_id => x_content_table(counter1).content_item_id,
246 x_category_val => ItemCatVal,
247 x_category_id => ItemCatID);
248 elements(CtxCharIds(2)).value := ItemCatVal; -- item_category
249 element_ids(CtxCharIds(2)).ID := ItemCatID;
250
251 For counter2 in 1..NoAvailablePlans Loop
252 IF triggers_matched(AvailablePlanTxns(counter2).PlanTxnID,
253 elements) = 'T' THEN
254 /*Using 0 for plan_id */
255 ApplicablePlanCtx := '0=' ||
256 AvailablePlanTxns(counter2).PlanID ||
257 '@' || CtxCharIds(1) || '=' ||
258 elements(CtxCharIds(1)).value ||
259 '@' || CtxCharIds(2) || '=' ||
260 elements(CtxCharIds(2)).value ||
261 '@' || CtxCharIds(3) || '=' ||
262 elements(CtxCharIds(3)).value ||
263 '@' || CtxCharIds(4) || '=' ||
264 elements(CtxCharIds(4)).value ||
265 '@' || CtxCharIds(5) || '=' ||
266 elements(CtxCharIds(5)).value;
267
268 ApplicablePlanCtxId := '0=' ||
269 AvailablePlanTxns(counter2).PlanID ||
270 '@' || CtxCharIds(1) || '=' ||
271 element_ids(CtxCharIds(1)).ID ||
272 '@' || CtxCharIds(2) || '=' ||
273 element_ids(CtxCharIds(2)).ID ||
274 '@' || CtxCharIds(3) || '=' ||
275 element_ids(CtxCharIds(3)).ID ||
276 '@' || CtxCharIds(4) || '=' ||
277 element_ids(CtxCharIds(4)).ID ||
278 '@' || CtxCharIds(5) || '=' ||
279 element_ids(CtxCharIds(5)).ID;
280
281 If x_plan_txn_ids_str is not null then
282 x_plan_txn_ids_str :=
283 x_plan_txn_ids_str || ',' ||
284 AvailablePlanTxns(counter2).PlanTxnID;
285 x_plan_contexts_str :=
286 x_plan_contexts_str || '%' || ApplicablePlanCtx;
287 x_plan_ctxs_ids_str :=
288 x_plan_ctxs_ids_str || '%' || ApplicablePlanCtxId;
289 else
290 x_plan_txn_ids_str := AvailablePlanTxns(counter2).PlanTxnID;
291 x_plan_contexts_str := ApplicablePlanCtx;
292 x_plan_ctxs_ids_str := ApplicablePlanCtxId;
293 end if;
294 END IF;
295 End Loop;
296 End If;
297 End Loop;
298
299 END evaluate_triggers;
300
301
302 FUNCTION triggers_matched(p_plan_txn_id IN NUMBER, elements IN qa_txn_grp.ElementsArray)
303 RETURN VARCHAR2 IS
304
305 BEGIN
306
307 FOR plan_record in (
308 SELECT qpct.operator,
309 qpct.Low_Value,
310 qpct.High_Value ,
314 qa_chars qc
311 qc.datatype,
312 qc.char_id
313 FROM qa_plan_collection_triggers qpct,
315 WHERE qpct.Collection_Trigger_ID = qc.char_id and
316 qpct.plan_transaction_id = p_plan_txn_id) LOOP
317
318 IF NOT elements.EXISTS(plan_record.char_id) THEN
319 RETURN 'F';
320 END IF;
321
322 IF NOT qltcompb.compare(
323 elements(plan_record.char_id).value,
324 plan_record.operator,
325 plan_record.Low_Value,
326 plan_record.High_Value,
327 plan_record.datatype) THEN
328 RETURN 'F';
329 END IF;
330
331 END LOOP;
332
333 RETURN 'T';
334 END triggers_matched;
335
336 -- Bug 4519558. Oa Fwk Integration Project. UT bug fix.
337 -- Return fnd_api.g_true if p_txn is a mobile txn
338 -- else return fnd_api.g_false.
339 -- Mobile transaction number will come in range [1001,1999]
340 -- srhariha. Mon Aug 22 02:50:35 PDT 2005.
341
342
343 FUNCTION is_mobile_txn(p_txn IN NUMBER) RETURN VARCHAR2 IS
344
345 BEGIN
346
347 IF (p_txn between 1001 and 1999) THEN
348 return fnd_api.g_true;
349 END IF;
350
351 return fnd_api.g_false;
352
353 END is_mobile_txn;
354
355 END qa_mqa_mwa_api;