1 package body qa_ss_om as
2 /* $Header: qltssomb.plb 120.2 2011/03/14 06:31:30 ntungare ship $ */
3
4 function are_om_header_plans_applicable (
5 P_So_Header_Id IN NUMBER DEFAULT NULL
6 )
7 Return VARCHAR2
8 IS
9 plan_applicable VARCHAR2(5) := 'N';
10
11 -- This cursor is to verify if there is data in qr for
12 -- the given so header id
13 CURSOR om_plans_cur IS
14 SELECT 'Y'
15 from QA_RESULTS QR
16 where QR.So_Header_Id = P_So_Header_Id
17 AND Rownum <= 1;
18
19 BEGIN
20 if (p_so_header_id is null) Then
21 return 'N';
22 end if;
23 if (not fnd_function.test('QA_SS_REST_VQR')) then
24 return 'N';
25 end if; -- function security. if test returns false,return N
26
27 open om_plans_cur;
28 fetch om_plans_cur into plan_applicable;
29 close om_plans_cur;
30 Return plan_applicable;
31
32 END are_om_header_plans_applicable;
33 --------------------------------------------------------------------------
34 function are_om_lines_plans_applicable (
35 P_So_Header_Id IN NUMBER DEFAULT NULL,
36 P_Item_Id IN NUMBER DEFAULT NULL
37 )
38 Return VARCHAR2
39 IS
40
41 plan_applicable VARCHAR2(5) := 'N';
42
43 -- This cursor sees if there is row in qr for the
44 -- given so header id and item id combination
45 CURSOR om_plans_cur IS
46 SELECT 'Y'
47 from QA_RESULTS QR
48 where QR.So_Header_Id = P_So_Header_Id
49 AND QR.Item_Id = p_item_id
50 AND Rownum <= 1;
51
52 BEGIN
53 If (p_item_id is Null) OR (p_so_header_id is Null) Then
54 Return 'N';
55 end if;
56 if (not fnd_function.test('QA_SS_REST_VQR')) then
57 return 'N';
58 end if; -- function security. if test returns false,return N
59
60 open om_plans_cur;
61 fetch om_plans_cur into plan_applicable;
62 close om_plans_cur;
63 Return plan_applicable;
64
65 END are_om_lines_plans_applicable;
66 --------------------------------------------------------------------------
67
68 procedure om_header_to_quality (
69 PK1 IN VARCHAR2 DEFAULT NULL, --so header id
70 PK2 IN VARCHAR2 DEFAULT NULL,
71 PK3 IN VARCHAR2 DEFAULT NULL,
72 PK4 IN VARCHAR2 DEFAULT NULL,
73 PK5 IN VARCHAR2 DEFAULT NULL,
74 PK6 IN VARCHAR2 DEFAULT NULL,
75 PK7 IN VARCHAR2 DEFAULT NULL,
76 PK8 IN VARCHAR2 DEFAULT NULL,
77 PK9 IN VARCHAR2 DEFAULT NULL,
78 PK10 IN VARCHAR2 DEFAULT NULL,
79 c_outputs1 OUT NOCOPY VARCHAR2,
80 c_outputs2 OUT NOCOPY VARCHAR2,
81 c_outputs3 OUT NOCOPY VARCHAR2,
82 c_outputs4 OUT NOCOPY VARCHAR2,
83 c_outputs5 OUT NOCOPY VARCHAR2,
84 c_outputs6 OUT NOCOPY VARCHAR2,
85 c_outputs7 OUT NOCOPY VARCHAR2,
86 c_outputs8 OUT NOCOPY VARCHAR2,
87 c_outputs9 OUT NOCOPY VARCHAR2,
88 c_outputs10 OUT NOCOPY VARCHAR2)
89
90 IS
91 plan_tab plan_tab_type;
92 i number := 0;
93
94 -- Fetch plans matching the so header id criteria
95 -- also get the plan name, description and type(meaning)
96 CURSOR om_plans_cur IS
97 SELECT distinct qr.plan_id, qp.name, qp.description, fcl.meaning
98 from QA_RESULTS QR, qa_plans qp, fnd_common_lookups fcl
99 where QR.So_Header_Id = to_number(Pk1)
100 and qr.plan_id = qp.plan_id
101 and qp.plan_type_code = fcl.lookup_code
102 and fcl.lookup_type = 'COLLECTION_PLAN_TYPE'
103 order by qp.name;
104
105 BEGIN
106 if (icx_sec.validatesession) then
107 FOR om_rec IN om_plans_cur LOOP
108 i := i+1;
109 plan_tab(i).plan_id := om_rec.plan_id;
110 plan_tab(i).name := om_rec.name;
111 plan_tab(i).description := om_rec.description;
112 plan_tab(i).meaning := om_rec.meaning;
113 end loop;
114
115 List_OM_Plans(plan_tab, pk1, null);
116 end if; -- end icx validate session
117 EXCEPTION
118 WHEN OTHERS THEN
119 htp.p('Exception in procedure om_headers_to_quality');
120 htp.p(SQLERRM);
121
122 END om_header_to_quality;
123
124 ------------------------------------------------------------------------------------------
125
126 procedure om_lines_to_quality (
127 PK1 IN VARCHAR2 DEFAULT NULL,--so header id
128 PK2 IN VARCHAR2 DEFAULT NULL,--so line id(not used)
129 PK3 IN VARCHAR2 DEFAULT NULL,--item id
130 PK4 IN VARCHAR2 DEFAULT NULL,
131 PK5 IN VARCHAR2 DEFAULT NULL,
132 PK6 IN VARCHAR2 DEFAULT NULL,
133 PK7 IN VARCHAR2 DEFAULT NULL,
134 PK8 IN VARCHAR2 DEFAULT NULL,
135 PK9 IN VARCHAR2 DEFAULT NULL,
136 PK10 IN VARCHAR2 DEFAULT NULL,
137 c_outputs1 OUT NOCOPY VARCHAR2,
138 c_outputs2 OUT NOCOPY VARCHAR2,
139 c_outputs3 OUT NOCOPY VARCHAR2,
140 c_outputs4 OUT NOCOPY VARCHAR2,
141 c_outputs5 OUT NOCOPY VARCHAR2,
142 c_outputs6 OUT NOCOPY VARCHAR2,
143 c_outputs7 OUT NOCOPY VARCHAR2,
144 c_outputs8 OUT NOCOPY VARCHAR2,
145 c_outputs9 OUT NOCOPY VARCHAR2,
146 c_outputs10 OUT NOCOPY VARCHAR2)
147
148 IS
149 plan_tab plan_tab_type;
150 i number := 0;
151
152 -- Fetch plans matching the so header id and itemid criteria
153 -- also get the plan name, description and type(meaning)
154 CURSOR om_plans_cur IS
155 SELECT distinct qr.plan_id, qp.name, qp.description, fcl.meaning
156 from QA_RESULTS QR, qa_plans qp, fnd_common_lookups fcl
157 where QR.So_Header_Id = to_number(Pk1)
158 AND qr.item_id = to_number(pk3)
159 and qr.plan_id = qp.plan_id
160 and qp.plan_type_code = fcl.lookup_code
161 and fcl.lookup_type = 'COLLECTION_PLAN_TYPE'
162 order by qp.name;
163 -- we do not use pk2 which is so_line_id
164 -- becos we base search on so_header and item only
165
166
167 BEGIN
168 if (icx_sec.validatesession) then
169 -- if we get to this level, then item should be non-null
170 -- pk3 is the item id
171 if (pk3 is not null) then
172 FOR om_rec IN om_plans_cur LOOP
173 i := i+1;
174 plan_tab(i).plan_id := om_rec.plan_id;
175 plan_tab(i).name := om_rec.name;
176 plan_tab(i).description := om_rec.description;
177 plan_tab(i).meaning := om_rec.meaning;
178 end loop;
179
180 List_OM_Plans(plan_tab, pk1, pk3);
181 end if; -- end checking non null pk3
182
183 end if; -- end icx validate session
184 EXCEPTION
185 WHEN OTHERS THEN
186 htp.p('Exception in procedure om_lines_to_quality');
187 htp.p(SQLERRM);
188
189 END om_lines_to_quality;
190
191 ------------------------------------------------------------------------------------------
192
193 procedure List_OM_Plans ( plan_tab IN plan_tab_type,
194 P_So_Header_Id IN NUMBER Default Null,
195 P_Item_Id IN NUMBER Default Null)
196 IS
197 l_language_code varchar2(30);
198 no_of_plans NUMBER;
199 pid_i NUMBER;
200 pname varchar2(40);
201 pdesc varchar2(30);
202 ptype varchar2(30);
203 viewurl varchar2(5000);
204 row_color varchar2(10) := 'BLUE';
205
206 om_where_cl varchar2(2000);
207
208 BEGIN
209 if (icx_sec.validatesession) then
210 l_language_code := icx_sec.getid(icx_sec.pv_language_code);
211
212 htp.htmlOpen;
213 htp.p('<BODY bgcolor=#cccccc>');
214 htp.formOpen('');
215 htp.br;
216 htp.tableOpen(cborder=>'BORDER=2', cattributes=>'CELLPADDING=2');
217 htp.tableCaption(fnd_message.get_string('QA','QA_SS_COLL_PLANS'));
218 htp.tableRowOpen (cattributes=>'BGCOLOR="#336699"');
219 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
220 fnd_message.get_string('QA', 'QA_SS_CP_HEADING')
221 || '</font>', calign=>'CENTER');
222 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
223 fnd_message.get_string('QA', 'QA_SS_DESC'), calign=>'CENTER');
224 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
225 'Type'|| '</font>', calign=>'CENTER');
226 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
227 fnd_message.get_string('QA', 'QA_SS_VIEW_BUTTON')|| '</font>', calign=>'CENTER');
228 htp.tableRowClose;
229 htp.p('<TR></TR><TR></TR>');
230
231 -- Construct the where clause here
232 om_where_cl := 'so_header_id='||p_so_header_id;
233
234 -- Bug 5003507. R12 Performance SQL Literals.
235 -- Comment out literal usage in an obsolete code.
236 -- srhariha. Mon Jan 30 04:47:02 PST 2006
237
238 --if (p_item_id is not null) then
239 -- om_where_cl := om_where_cl || ' AND '
240 -- || 'item_id=' || p_item_id;
241 --end if; -- end p_item_id check
242
243 om_where_cl := wfa_html.conv_special_url_chars(om_where_cl);
244
245 -- Loop goes in here
246 no_of_plans := plan_tab.count;
247 For i in 1..no_of_plans
248 Loop
249 pid_i := plan_tab(i).plan_id;
250
251 viewurl := 'qa_ss_core.VQR_Frames?plan_id_i='|| pid_i
252 || '&'
253 || 'ss_where_clause='||om_where_cl;
254
255 pname := substr(plan_tab(i).name,1,30);
256 pdesc := NVL(substr(plan_tab(i).description,1,20), ' ');
257 ptype := NVL(substr(plan_tab(i).meaning,1,20), ' ');
258
259 IF (row_color = 'BLUE') THEN
260 htp.tableRowOpen(cattributes=>'BGCOLOR="#99CCFF"');
261 row_color := 'WHITE';
262 ELSE
263 htp.tableRowOpen(cattributes=>'BGCOLOR="#FFFFFF"');
264 row_color := 'BLUE';
265 END IF; -- end if for row color
266
267 htp.tableData(pname);
268 htp.tableData(pdesc);
269 htp.tableData(ptype);
270
271 htp.tableData(htf.anchor(viewurl, fnd_message.get_string('QA','QA_SS_VIEW_BUTTON'), cattributes=>'TARGET="viewwin"'));
272
273
274 End Loop; -- end of forloop for all rows in list of plans
275 htp.tableClose;
276 htp.formClose;
277 htp.bodyClose;
278 htp.htmlClose;
279
280 end if; -- end icx validate session
281
282 EXCEPTION
283 WHEN OTHERS THEN
284 htp.p('Exception in procedure qa_ss_om.list_om_plans');
285 htp.p(SQLERRM);
286
287 END List_OM_Plans;
288
289
290 function is_om_header_plan_applicable (
291 x_Pid IN NUMBER,
292 x_so_header_id IN VARCHAR2 default null)
293 Return VARCHAR2
294 IS
295 plan_applicable VARCHAR2(5) := 'N';
296 l_mtl_sales_ord_id NUMBER := -99;
297
298 CURSOR om_plans_cur(p_mtl_sales_ord_id IN NUMBER) IS
299 Select 'Y'
300 From QA_RESULTS QR
301 Where QR.So_header_id = p_mtl_sales_ord_id
302 AND QR.plan_id = x_Pid;
303
304 BEGIN
305 if (x_so_header_id is null) then
306 return 'N';
307 end if;
308
309 l_mtl_sales_ord_id :=
310 qa_results_interface_pkg.OEHeader_to_MTLSales
311 ( x_so_header_id );
312
313 open om_plans_cur (l_mtl_sales_ord_id);
314 fetch om_plans_cur into plan_applicable;
315 close om_plans_cur;
316 Return plan_applicable;
317 END;
318
319
320 function is_om_lines_plan_applicable (
321 x_Pid IN NUMBER,
322 x_so_header_id IN VARCHAR2 default null,
323 x_Item_Id in VARCHAR2 default null )
324 Return VARCHAR2
325 IS
326 plan_applicable VARCHAR2(5) := 'N';
327
328 l_mtl_sales_ord_id NUMBER := -99;
329
330 --
331 -- bug 11831190
332 -- USing the sales _order_id instead of header_id
333 --
334 CURSOR om_plans_cur(p_mtl_sales_ord_id IN NUMBER) IS
335 Select 'Y'
336 From QA_RESULTS QR
337 Where QR.so_header_id = p_mtl_sales_ord_id
338 AND QR.Item_Id = x_item_id
339 AND QR.Plan_ID = x_Pid;
340
341 BEGIN
342 If (x_item_id is NULL) or (x_so_header_id is NULL) THEN
343 Return 'N';
344 End If;
345
346 --
347 -- bug 11831190
348 -- Deriving the sales_order_id based on the header_id
349 --
350 l_mtl_sales_ord_id :=
351 qa_results_interface_pkg.OEHeader_to_MTLSales
352 ( x_so_header_id );
353
354 open om_plans_cur(l_mtl_sales_ord_id);
355 fetch om_plans_cur into plan_applicable;
356 close om_plans_cur;
357 Return plan_applicable;
358 END;
359
360
361
362 end qa_ss_om;
363
[Home] [Help]