[Home] [Help]
PACKAGE BODY: APPS.CSM_MULTI_MOBQRY_PKG
Source
1 package body CSM_MULTI_MOBQRY_PKG as
2 /*$Header: csm5fmqb.pls 120.0.12020000.2 2013/04/09 06:22:37 saradhak noship $*/
3
4 -- DESCRIPTION
5 -- This package is used by CSM HTML5 MFS Application for functional MQs
6 --
7 -- HISTORY
8 -- 22-feb-2012 saradhak Created
9 --
10 g_pass_error VARCHAR2(500):='Invalid password passed. Please verify password using Sync.';
11 g_nl_chr VARCHAR2(10) :=fnd_global.local_chr(10);
12
13 CURSOR c_get_hr_loc_id(b_user_id NUMBER,b_ps_id NUMBER) IS
14 SELECT max(hrl.location_id)
15 FROM po_location_associations_all hrl, HZ_CUST_ACCT_SITES_ALL cas
16 WHERE hrl.org_id=cas.org_id and hrl.address_id=cas.cust_acct_site_id
17 and cas.party_site_id=b_ps_id
18 and (exists (select 1 from CSM_HZ_CUST_ACCT_SITES_ALL_ACC casa where cas.cust_acct_site_id=casa.cust_acct_site_id and casa.user_id=b_user_id)
19 or exists(select 1 from csm_po_loc_ass_all_acc poa where poa.location_id=hrl.location_id and poa.user_id=b_user_id ));
20
21 TYPE l_char_type IS TABLE OF VARCHAR2(1000) INDEX BY BINARY_INTEGER;
22 TYPE l_num_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
23
24
25 FUNCTION to_xml_tag_name(p_input IN VARCHAR2) RETURN VARCHAR2
26 IS
27 l_x varchar2(1000);
28 l_inp varchar2(2000):=replace(p_input,' ','_');
29 l_c varchar2(1):='';
30 BEGIN
31 select trim(translate(l_inp,'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789_',' ')) into l_x from dual;
32 l_x:=replace(l_x,' ','');
33 l_c:=substr(l_x,-1);
34 loop
35 exit when (l_c is null OR l_c='');
36 l_x:=replace(l_x,l_c,'');
37 l_inp:=replace (l_inp,l_c,'_');
38 l_c:=substr(l_x,-1);
39 end loop;
40 RETURN l_inp;
41 exception
42 when others then
43 RETURN l_inp;
44 END to_xml_tag_name;
45
46 FUNCTION translateString(p_input IN VARCHAR2) RETURN VARCHAR2
47 IS
48 BEGIN
49 return replace(replace(replace(p_input,'\','\\'),'"','\"'), g_nl_chr, '\n');
50 END translateString;
51
52 --light version of CSM_HTML5_PKG.parse_XML
53 PROCEDURE PARSE_XML(l_xml_clob_payload IN CLOB,
54 X_COL_NAME_LIST OUT NOCOPY CSM_VARCHAR_LIST,
55 X_COL_VALUE_LIST OUT NOCOPY CSM_VARCHAR_LIST,
56 p_fix_esc_chars IN BOOLEAN:=false)
57
58 AS
59 l_xml_doc xmldom.DOMDocument;
60 l_xml_parser xmlparser.Parser;
61 l_xml_node_list xmldom.DOMNodeList;
62 l_xml_node xmldom.DOMNode;
63 l_xml_node_len NUMBER;
64 len2 number;
65 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
66 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
67 BEGIN
68
69 l_xml_parser := xmlparser.newParser;
70 xmlparser.parseClob(l_xml_parser, l_xml_clob_payload);
71 L_XML_DOC := XMLPARSER.GETDOCUMENT(L_XML_PARSER);
72 xmlparser.freeParser(l_xml_parser);
73 l_xml_node_list := xmldom.getElementsByTagName(l_xml_doc, '*');
74 L_XML_NODE_LEN := XMLDOM.GETLENGTH(L_XML_NODE_LIST);
75 L_COL_NAME_LIST.EXTEND(L_XML_NODE_LEN);
76 l_COL_VALUE_LIST.EXTEND(l_xml_node_len);
77 -- loop through elements
78 FOR I IN 2..L_XML_NODE_LEN-1 LOOP
79 l_xml_node := xmldom.item(l_xml_node_list, i);
80
81 l_COL_NAME_LIST(i-1) := xmldom.getNodeName(l_xml_node);
82 L_XML_NODE := XMLDOM.GETFIRSTCHILD(L_XML_NODE);
83 If xmldom.getNodeType(l_xml_node) = xmldom.TEXT_NODE THEN
84 l_COL_VALUE_LIST(i-1) := xmldom.getNodeValue(l_xml_node);
85 IF p_fix_esc_chars THEN
86 l_COL_VALUE_LIST(i-1):=replace(replace(replace(l_COL_VALUE_LIST(i-1),'%amp;',fnd_global.local_chr(38)),'%lt;','<'),'%gt;','>');
87 END IF;
88 ELSE
89 l_COL_VALUE_LIST(i-1) := NULL;
90 END IF;
91 END LOOP;
92 x_COL_NAME_LIST := l_COL_NAME_LIST;
93 X_COL_VALUE_LIST := L_COL_VALUE_LIST;
94 END PARSE_XML;
95
96 function get_ff_where(p_set_id in NUMBER ) return varchar2
97 is
98 l_where FND_FLEX_VALIDATION_TABLES.ADDITIONAL_WHERE_CLAUSE%type;
99 begin
100 select ADDITIONAL_WHERE_CLAUSE into l_where
101 from FND_FLEX_VALIDATION_TABLES
102 where FLEX_VALUE_SET_ID=p_set_id;
103 return l_where;
104 end get_ff_where;
105
106
107 --query 26
108 procedure q_get_address_DFF(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_addr_style IN VARCHAR2, p_result OUT nocopy CLOB,
109 x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
110 IS
111
112 CURSOR c_style IS
113 SELECT APPLICATION_COLUMN_NAME tag, DECODE(REQUIRED_FLAG,'Y','*','')||FORM_LEFT_PROMPT text,
114 b.FLEX_VALUE_SET_ID ffvs,b.validation_type
115 FROM FND_DESCR_FLEX_COL_USAGE_VL a,FND_FLEX_VALUE_SETS b
116 WHERE DESCRIPTIVE_FLEX_CONTEXT_CODE = p_addr_style
117 AND APPLICATION_ID = 222 AND DESCRIPTIVE_FLEXFIELD_NAME = 'Remit Address HZ'
118 AND DISPLAY_FLAG = 'Y' AND ENABLED_FLAG='Y'
119 AND a.FLEX_VALUE_SET_ID=b.FLEX_VALUE_SET_ID(+)
120 ORDER BY COLUMN_SEQ_NUM;
121
122 type curtype is REF Cursor;
123 c_cur curtype;
124
125 l_result CLOB:='';
126 l_user_id NUMBER; l_resp_id NUMBER;l_app_id NUMBER;
127 l_hr_loc_id NUMBER;
128 l_language VARCHAR2(1000);
129 l_ffvs_id NUMBER;
130 l_ff_sql VARCHAR2(4000);
131 l_ff_where VARCHAR2(4000);
132 l_do_loop BOOLEAN:=false;
133 l_code VARCHAR2(1000);l_meaning VARCHAR2(4000);
134 BEGIN
135 CSM_UTIL_PKG.LOG('Entering q_get_address_DFF for country style:'||p_addr_style, 'CSM_MULTI_MOBQRY_PKG.q_get_address_DFF', FND_LOG.LEVEL_PROCEDURE);
136
137 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
138 x_error_message := g_pass_error;
139 p_result:='E:'||x_error_message;
140 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_get_address_DFF', FND_LOG.LEVEL_PROCEDURE);
141 RETURN;
142 END IF;
143
144 select user_id,responsibility_id,app_id
145 into l_user_id ,l_resp_id ,l_app_id
146 from asg_user where user_name=p_user_name;
147
148 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE='''||fnd_profile.value_specific('ICX_LANGUAGE',l_user_id ,l_resp_id ,l_app_id)||'''';
149
150 l_result:=l_result||'<ROW>';
151 FOR rec IN c_style
152 LOOP
153 l_result:=l_result||'<'||rec.tag||'><PROMPT>'||rec.text||'</PROMPT>';
154 IF rec.ffvs IS NOT NULL THEN
155 l_do_loop:=false;
156 IF rec.VALIDATION_TYPE = 'I' THEN
157 OPEN c_cur FOR 'select flex_value as code,flex_value_meaning as meaning from FND_FLEX_VALUES_VL WHERE FLEX_VALUE_SET_ID = '||rec.ffvs||' and enabled_flag=''Y''';
158 l_do_loop:=true;
159 ELSIF rec.VALIDATION_TYPE = 'F' THEN
160
161 select 'SELECT '||VALUE_COLUMN_NAME ||' as code,'||NVL(MEANING_COLUMN_NAME,VALUE_COLUMN_NAME)||' as meaning FROM '||APPLICATION_TABLE_NAME,ADDITIONAL_WHERE_CLAUSE
162 INTO l_ff_sql,l_ff_where from FND_FLEX_VALIDATION_TABLES WHERE FLEX_VALUE_SET_ID=rec.ffvs;
163
164 OPEN c_cur FOR l_ff_sql||' '||replace(l_ff_where,fnd_global.local_chr(10),' ');
165 l_do_loop:=true;
166 END IF;
167
168 IF l_do_loop THEN
169 l_result:=l_result||'<SET>';
170 LOOP
171 FETCH c_cur INTO l_code,l_meaning;
172 EXIT WHEN c_cur%NOTFOUND;
173 l_result:=l_result||'<VAL><CODE>'||l_code||'</CODE><MEAN>'||l_meaning||'</MEAN></VAL>';
174 END LOOP;
175 CLOSE c_cur;
176 l_result:=l_result||'</SET>';
177 END IF;
178 END IF;
179 l_result:=l_result||'</'||rec.tag||'>';
180 END LOOP;
181 l_result:=l_result||'</ROW>';
182
183 CSM_UTIL_PKG.LOG('Leaving q_get_address_DFF ', 'CSM_MULTI_MOBQRY_PKG.q_get_address_DFF', FND_LOG.LEVEL_PROCEDURE);
184 x_return_status := FND_API.G_RET_STS_SUCCESS;
185 p_result:='S:<RESULT>'||l_result||'</RESULT>';
186 EXCEPTION
187 WHEN OTHERS THEN
188 x_return_status := FND_API.G_RET_STS_SUCCESS;
189 x_error_message := 'Exception occurred in q_get_address_DFF: ' || substr(sqlerrm,0,1000);
190 p_result:='E:'||translateString(x_error_message);
191 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_get_address_DFF', FND_LOG.LEVEL_EXCEPTION);
192 END q_get_address_DFF;
193
194 --query 25
195 procedure q_ship_methods(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_from_org_id IN NUMBER,p_to_hzloc_id IN NUMBER,
196 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
197 IS
198
199 CURSOR c_ship_methods(b_to_location_id number)
200 IS
201 select shipping_method, shipping_method_meaning, shipping_cost,
202 organization_id, from_location_id, to_location_id,
203 destination_type, to_char(arrival_date,'DD-MON-RRRR HH24:MI:SS') arrival_date,
204 lead_time, lead_time_uom,
205 intransit_time, distance, distance_uom
206 from csp_shipping_details_v
207 where organization_id = p_from_org_id
208 and to_location_id = b_to_location_id
209 AND location_source = 'HR'
210 order by shipping_cost,arrival_date;
211
212 l_result CLOB:='';
213 l_user_id NUMBER; l_resp_id NUMBER;l_app_id NUMBER;
214 l_hr_loc_id NUMBER;
215 l_language VARCHAR2(1000);
216 BEGIN
217 CSM_UTIL_PKG.LOG('Entering q_ship_methods: frm_org:'||p_from_org_id||' to_hzloc:'||p_to_hzloc_id, 'CSM_MULTI_MOBQRY_PKG.q_ship_methods', FND_LOG.LEVEL_PROCEDURE);
218
219 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
220 x_error_message := g_pass_error;
221 p_result:='E:'||x_error_message;
222 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_ship_methods', FND_LOG.LEVEL_PROCEDURE);
223 RETURN;
224 END IF;
225
226 select user_id,responsibility_id,app_id
227 into l_user_id ,l_resp_id ,l_app_id
228 from asg_user where user_name=p_user_name;
229
230 SELECT max(hrl.location_id) INTO l_hr_loc_id
231 FROM po_location_associations_all hrl, HZ_CUST_ACCT_SITES_ALL cas
232 WHERE hrl.org_id=cas.org_id and hrl.address_id=cas.cust_acct_site_id
233 and cas.party_site_id=p_to_hzloc_id
234 and (exists (select 1 from CSM_HZ_CUST_ACCT_SITES_ALL_ACC casa where cas.cust_acct_site_id=casa.cust_acct_site_id and casa.user_id=l_user_id)
235 or exists(select 1 from csm_po_loc_ass_all_acc poa where poa.location_id=hrl.location_id and poa.user_id=l_user_id ));
236
237 CSM_UTIL_PKG.LOG('HR Location_id:'||l_hr_loc_id , 'CSM_MULTI_MOBQRY_PKG.q_ship_methods', FND_LOG.LEVEL_PROCEDURE);
238
239 IF l_hr_loc_id IS NULL THEN
240 x_return_status := FND_API.G_RET_STS_SUCCESS;
241 p_result:='E:Passed TO Location is invalid.';
242 return;
243 END IF;
244
245 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE='''||fnd_profile.value_specific('ICX_LANGUAGE',l_user_id ,l_resp_id ,l_app_id)||'''';
246
247 FOR rec IN c_ship_methods(l_hr_loc_id)
248 LOOP
249 l_result:=l_result||'<ROW>';
250 l_result:=l_result||'<SHP_MTD>'||rec.shipping_method||'</SHP_MTD>'
251 ||'<SHP_MTD_TL>'||rec.shipping_method_meaning||'</SHP_MTD_TL>'
252 ||'<SHP_COST>'||rec.shipping_cost||'</SHP_COST>'
253 ||'<F_ORG_ID>'||rec.organization_id||'</F_ORG_ID>'
254 ||'<DEST_TYPE>'||rec.destination_type||'</DEST_TYPE>'
255 ||'<ARR_DATE>'||rec.arrival_date||'</ARR_DATE>'
256 ||'<LEAD_T>'||rec.lead_time||'</LEAD_T>'
257 ||'<LEAD_T_UOM>'||rec.lead_time_uom||'</LEAD_T_UOM>'
258 ||'<INTRANS_T>'||rec.intransit_time||'</INTRANS_T>'
259 ||'<DIST>'||rec.distance||'</DIST>'
260 ||'<DIST_UOM>'||rec.distance_uom||'</DIST_UOM>';
261 l_result:=l_result||'</ROW>';
262 END LOOP;
263
264 CSM_UTIL_PKG.LOG('Leaving q_ship_methods ', 'CSM_MULTI_MOBQRY_PKG.q_ship_methods', FND_LOG.LEVEL_PROCEDURE);
265 x_return_status := FND_API.G_RET_STS_SUCCESS;
266 p_result:='S:<RESULT>'||l_result||'</RESULT>';
267 EXCEPTION
268 WHEN OTHERS THEN
269 x_return_status := FND_API.G_RET_STS_SUCCESS;
270 x_error_message := 'Exception occurred in q_ship_methods: ' || substr(sqlerrm,0,1000);
271 p_result:='E:'||translateString(x_error_message);
272 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_ship_methods', FND_LOG.LEVEL_EXCEPTION);
273 END q_ship_methods;
274
275 --query 23
276 procedure q_receivable_parts(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_task_id IN NUMBER,
277 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
278 IS
279 CURSOR c_parts IS
280 select distinct fl.meaning source_type,
281 hdr.creation_date,
282 hdr.document_number,
283 hdr.status,
284 hdr.source,
285 task.task_number,
286 hdr.destination,
287 line.item_number,
288 line.item_description,
289 UNIT_OF_MEASURE_TL uom,
290 line.serial_number,
291 line.order_quantity,
292 line.ship_quantity,
293 line.req_line_detail_id,
294 line.shipment_number
295 from csp_receiving_headers_v hdr,
296 csp_receiving_lines_v line,
297 jtf_tasks_b task,
298 MTL_UNITS_OF_MEASURE_TL mt,
299 fnd_lookup_values fl,
300 asg_user au
301 where line.header_id = hdr.header_id
302 and hdr.task_number=task.task_number
303 and au.user_name=p_user_name
304 and hdr.resource_id=au.resource_id
305 and task.task_id=p_task_id
306 and mt.language=au.language
307 and line.uom_code=mt.uom_code
308 and fl.lookup_type = 'CSP_REQ_SOURCE_TYPE'
309 and fl.lookup_code = line.source_type and fl.language=au.language
310 and (line.source_type ='RES' OR line.shipment_number IS NOT NULL)
311 order by hdr.creation_date desc;
312
313 l_result CLOB:='';
314 l_tag VARCHAR2(1000);
315 BEGIN
316 CSM_UTIL_PKG.LOG('Entering q_receivable_parts for '||p_task_id, 'CSM_MULTI_MOBQRY_PKG.q_receivable_parts', FND_LOG.LEVEL_PROCEDURE);
317
318 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
319 x_error_message := g_pass_error;
320 p_result:='E:'||x_error_message;
321 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receivable_parts', FND_LOG.LEVEL_PROCEDURE);
322 RETURN;
323 END IF;
324
325 FOR rec IN c_parts
326 LOOP
327 l_tag:=to_xml_tag_name('RCV_'||rec.req_line_detail_id||'_'||rec.serial_number);
328 l_result:=l_result||'<'||l_tag||'>';
329 l_result:=l_result||'<ITEM_NUM>'||rec.item_number||'</ITEM_NUM>'
330 ||'<ITEM_DSC>'||rec.item_description||'</ITEM_DSC>'
331 ||'<ITEM_UOM>'||rec.uom||'</ITEM_UOM>'
332 ||'<ORD_NUM>'||rec.document_number||'</ORD_NUM>'
333 ||'<ORD_TYPE>'||rec.source_type||'</ORD_TYPE>'
334 ||'<ORD_QTY>'||rec.order_quantity||'</ORD_QTY>'
335 ||'<ORD_SRC>'||rec.source||'</ORD_SRC>'
336 ||'<ORD_DEST>'||rec.destination||'</ORD_DEST>'
337 ||'<SHP_QTY>'||rec.ship_quantity||'</SHP_QTY>'
338 ||'<SHP_NUM>'||rec.shipment_number||'</SHP_NUM>'
339 ||'<SRL_NUM>'||rec.serial_number||'</SRL_NUM>'
340 ||'<RLD_ID>'||rec.req_line_detail_id||'</RLD_ID>';
341 l_result:=l_result||'</'||l_tag||'>';
342 END LOOP;
343
344 CSM_UTIL_PKG.LOG('Leaving q_receivable_parts ', 'CSM_MULTI_MOBQRY_PKG.q_receivable_parts', FND_LOG.LEVEL_PROCEDURE);
345 x_return_status := FND_API.G_RET_STS_SUCCESS;
346 p_result:='S:<RESULT>'||l_result||'</RESULT>';
347 EXCEPTION
348 WHEN OTHERS THEN
349 x_return_status := FND_API.G_RET_STS_SUCCESS;
350 x_error_message := 'Exception occurred in q_receivable_parts: ' || substr(sqlerrm,0,1000);
351 p_result:='E:'||translateString(x_error_message);
352 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receivable_parts', FND_LOG.LEVEL_EXCEPTION);
353 END q_receivable_parts;
354
355 --query --with correct serial number --not USED
356 procedure q_receive_parts2(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_rql_dtl_id IN VARCHAR2,p_qty IN VARCHAR2,p_serials IN VARCHAR2,p_close_short IN VARCHAR2,
357 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
358 IS
359
360 /* --csp views takes more than 2 mins to fetch,
361 CURSOR c_part_line ( p_rld_id IN NUMBER,p_srl IN VARCHAR2) is
362 select hdr.source_type,
363 hdr.source_organization_id ,
364 hdr.source_subinventory,
365 hdr.destination_organization_id ,
366 hdr.destination_subinventory,
367 hdr.header_id,
368 line.line_id,
369 line.inventory_item_id,
370 line.revision,
371 line.uom_code,
372 line.serial_number,
373 line.order_quantity,
374 line.req_line_detail_id,
375 line.shipment_number,
376 line.shipment_line_id
377 from csp_receiving_headers_v hdr,
378 csp_receiving_lines_v line
379 where line.req_line_detail_id = p_rld_id
380 and line.header_id = hdr.header_id
381 and nvl(line.serial_number,'-1') =nvl(p_srl,'-1') ;
382 */
383
384 CURSOR c_part_line ( b_rld_id IN NUMBER,b_srl IN VARCHAR2)
385 is
386 select mr.reservation_id document_number,
387 rld.source_type,
388 mr.organization_id source_organization_id,
389 mr.subinventory_code source_subinventory,
390 rh.destination_organization_id ,
391 rh.destination_subinventory,
392 mr.reservation_id header_id,
393 mr.reservation_id line_id,
394 mr.inventory_item_id,
395 mr.revision,
396 mr.reservation_uom_code uom_code,
397 mr.serial_number,
398 mr.reservation_quantity order_quantity,
399 decode(mr.serial_number, NULL, mr.reservation_quantity, 1) as ship_quantity,
400 rld.req_line_detail_id,
401 NULL as shipment_number,
402 NULL as shipment_line_id
403 from csp_req_line_details rld,
404 csp_requirement_lines rl,
405 csp_requirement_headers rh,
406 mtl_reservations mr
407 where rld.source_type='RES'
408 and rld.requirement_line_id=rl.requirement_line_id
409 and rl.requirement_header_id=rh.requirement_header_id
410 and rld.source_id=mr.reservation_id
411 and (rh.destination_organization_id<>mr.organization_id OR NVL(mr.subinventory_code,'-999') <> NVL(rh.destination_subinventory,'-999'))
412 and req_line_detail_id=b_rld_id
413 and nvl(mr.serial_number,'-1') =nvl(b_srl,'-1')
414 union all
415 select oeh.order_number,
416 rld.source_type,
417 oel.ship_from_org_id source_organization_id,
418 oel.subinventory source_subinventory,
419 rh.destination_organization_id ,
420 rh.destination_subinventory,
421 oel.header_id,
422 oel.line_id,
423 oel.inventory_item_id,
424 NULL as REVISION,
425 oel.shipping_quantity_uom uom_code,
426 rss.serial_num serial_number,
427 oel.ordered_quantity order_quantity,
428 decode(rss.serial_num, NULL,oel.shipped_quantity,1) ship_quantity,
429 rld.req_line_detail_id,
430 rch.shipment_num as shipment_number,
431 rcl.shipment_line_id
432 from csp_req_line_details rld,
433 csp_requirement_lines rl,
434 csp_requirement_headers rh ,
435 oe_order_lines_all oel,
436 oe_order_headers_all oeh,
437 rcv_shipment_lines rcl,
438 rcv_shipment_headers rch ,
439 rcv_serials_supply rss
440 where rld.source_type='IO'
441 and rld.source_id=oel.line_id
442 and oel.header_id=oeh.header_id
443 and rld.requirement_line_id=rl.requirement_line_id
444 and rl.requirement_header_id=rh.requirement_header_id
445 and rcl.source_document_code='REQ'
446 AND rcl.requisition_line_id=oel.source_document_line_id
447 and rcl.shipment_header_id=rch.shipment_header_id
448 and rcl.quantity_received< rcl.quantity_shipped
449 and rcl.shipment_line_id = rss.shipment_line_id (+)
450 and req_line_detail_id=b_rld_id
451 and nvl(rss.serial_num,'-1') =nvl(b_srl,'-1') ;
452
453
454 l_msg_count NUMBER;
455 l_msg_data VARCHAR2(4000);
456 l_str1 varchar2(4000):=p_rql_dtl_id;
457 l_str2 varchar2(4000):=p_qty;
458 l_str3 varchar2(4000):=p_serials;
459 l_str4 varchar2(4000):=p_close_short;
460
461 l_rld_tab l_num_type;
462 l_qty_tab l_num_type;
463 l_srl_tab l_char_type;
464 l_cs_tab l_char_type;
465
466
467 l1 number; l2 number; l3 number; l4 number;
468 cnt number :=1;
469 iCnt number:=0;
470 l_dummy boolean :=false;
471
472 l_trans_items_rec_type CSP_TRANSACTIONS_PUB.Trans_Items_Rec_Type;
473 l_null_trans_rec CSP_TRANSACTIONS_PUB.Trans_Items_Rec_Type;
474 r_part_line c_part_line%rowtype;
475
476 l_result CLOB :='';
477 l_tag VARCHAR2(1000);
478 l_user_id NUMBER;
479 l_resp_id NUMBER;
480 l_app_id NUMBER;
481 begin
482 CSM_UTIL_PKG.LOG('Entering q_receive_parts for '||p_rql_dtl_id||fnd_global.local_chr(10)
483 ||p_qty||fnd_global.local_chr(10)||p_serials||fnd_global.local_chr(10)||p_close_short, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
484
485 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
486 x_error_message := g_pass_error;
487 p_result:='E:'||x_error_message;
488 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
489 RETURN;
490 END IF;
491
492 if instr(l_str1,':,:')>0 then
493 loop
494 exit when instr(l_str1,':,:')=0 ;
495 l1:= instr(l_str1,':,:');
496 l2:= instr(l_str2,':,:');
497 l3:= instr(l_str3,':,:');
498 l4:= instr(l_str4,':,:');
499 l_rld_tab(cnt):=to_number(substr(l_str1,1,l1-1));
500 l_qty_tab(cnt):=to_number(substr(l_str2,1,l2-1));
501 l_srl_tab(cnt):=substr(l_str3,1,l3-1);
502 l_cs_tab(cnt):=upper(substr(l_str4,1,l4-1));
503 cnt:=cnt+1;
504 l_str1:=substr(l_str1,l1+3);
505 l_str2:=substr(l_str2,l2+3);
506 l_str3:=substr(l_str3,l3+3);
507 l_str4:=substr(l_str4,l4+3);
508 end loop;
509 end if;
510 l_rld_tab(cnt):=to_number(l_str1);
511 l_qty_tab(cnt):=to_number(l_str2);
512 l_srl_tab(cnt):=l_str3;
513 l_cs_tab(cnt):=upper(l_str4);
514
515
516 SELECT user_id,responsibility_id, app_id
517 INTO l_user_id,l_resp_id,l_app_id
518 FROM asg_user WHERE user_name=p_user_name;
519
520 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
521
522 FOR I IN 1..l_rld_tab.COUNT
523 LOOP
524
525 l_tag:=to_xml_tag_name('RCV_'||l_rld_tab(I)||'_'||l_srl_tab(I));
526 l_result:=l_result||'<'||l_tag||'>';
527
528 CSM_UTIL_PKG.LOG('Inside Loop for '||l_rld_tab(I)||' '||fnd_global.local_chr(10)
529 ||l_qty_tab(I)||' '||fnd_global.local_chr(10)||l_srl_tab(I)||' '||fnd_global.local_chr(10)||' '||l_cs_tab(I), 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
530
531
532 open c_part_line(l_rld_tab(I),l_srl_tab(I));
533 fetch c_part_line into r_part_line;
534
535 IF c_part_line%FOUND THEN
536
537 l_trans_items_rec_type:=l_null_trans_rec;
538
539 l_trans_items_rec_type.INVENTORY_ITEM_ID := r_part_line.INVENTORY_ITEM_ID;
540 l_trans_items_rec_type.REVISION := r_part_line.REVISION;
541 l_trans_items_rec_type.QUANTITY := l_qty_tab(I);
542 l_trans_items_rec_type.UOM_CODE := r_part_line.uom_code;
543 l_trans_items_rec_type.SERIAL_NUMBER := r_part_line.serial_number;
544 l_trans_items_rec_type.FRM_ORGANIZATION_ID := r_part_line.source_organization_id;
545 l_trans_items_rec_type.FRM_SUBINVENTORY_CODE := r_part_line.source_subinventory;
546 l_trans_items_rec_type.TO_ORGANIZATION_ID := r_part_line.destination_organization_id;
547 l_trans_items_rec_type.TO_SUBINVENTORY_CODE := r_part_line.destination_subinventory;
548 l_trans_items_rec_type.SHIPMENT_NUMBER := r_part_line.shipment_number;
549 l_trans_items_rec_type.SHIPMENT_LINE_ID := r_part_line.shipment_line_id;
550
551 CSM_UTIL_PKG.LOG
552 ( 'Calling API CSP_TRANSACTIONS_PUB.receive_requirement_trans for '||r_part_line.source_type||':'||r_part_line.line_id||':'||r_part_line.serial_number ,
553 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_STATEMENT );
554
555 CSP_TRANSACTIONS_PUB.receive_requirement_trans (
556 p_trans_header_id => r_part_line.header_id,
557 p_trans_line_id => r_part_line.line_id,
558 p_trans_record => l_trans_items_rec_type,
559 p_trans_type => r_part_line.source_type,
560 p_req_line_detail_id => r_part_line.req_line_detail_id,
561 p_close_short => l_cs_tab(I),
562 x_return_status => x_return_status,
563 x_msg_count => l_msg_count,
564 x_msg_data => l_msg_data
565 );
566
567 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
568 l_msg_data := replace(fnd_msg_pub.get(l_msg_count,'F'),fnd_global.local_chr(10), ' ');
569 x_error_message := 'Error in CSP_TRANSACTIONS_PUB.receive_requirement_trans' || ' for PK : ' || r_part_line.line_id || ' Details:' || l_msg_data ;
570 CSM_UTIL_PKG.LOG( x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_ERROR);
571 l_result:=l_result||'<ERROR>'||translateString(l_msg_data)||'</ERROR>'||'</'||l_tag||'>';
572 ELSE
573 CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND(r_part_line.destination_organization_id,r_part_line.destination_subinventory
574 ,r_part_line.INVENTORY_ITEM_ID,l_user_id);
575 IF(r_part_line.serial_number IS NOT NULL) THEN
576 CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM(r_part_line.destination_organization_id,r_part_line.destination_subinventory
577 ,r_part_line.INVENTORY_ITEM_ID,r_part_line.serial_number,l_user_id);
578 END IF;
579 IF r_part_line.source_type='IO' THEN
580 FOR rec IN (SELECT USER_ID FROM CSP_REQ_LINE_DETAILS rld ,CSM_REQ_LINE_DETAILS_ACC acc
581 WHERE rld.SOURCE_TYPE='IO' AND rld.SOURCE_ID=r_part_line.line_id AND acc.REQ_LINE_DETAIL_ID=rld.REQ_LINE_DETAIL_ID)
582 LOOP --to update received qty
583 l_dummy := asg_download.mark_dirty(
584 p_pub_item => 'CSM_ORDER_LINES'
585 , p_accessid => r_part_line.line_id
586 , p_userid => rec.user_id
587 , p_dml => 'U'
588 , p_timestamp => sysdate
589 );
590 END LOOP;
591 END IF;
592 l_result:=l_result||'<SUCCESS/></'||l_tag||'>';
593 END IF;
594 ELSE
595 l_result:=l_result||'<ERROR>No Line Found for receive</ERROR></'||l_tag||'>';
596 END IF;
597 close c_part_line;
598 END LOOP;
599
600 CSM_UTIL_PKG.LOG('Leaving q_receive_parts ', 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
601 x_return_status := FND_API.G_RET_STS_SUCCESS;
602 p_result:='S:<RESULT>'||l_result||'</RESULT>';
603 EXCEPTION
604 WHEN FND_API.G_EXC_ERROR THEN
605 ROLLBACK;
606 x_return_status := FND_API.G_RET_STS_SUCCESS;
607 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts',FND_LOG.LEVEL_PROCEDURE);
608 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
609 p_result:='E:'||translateString(x_error_message);
610 WHEN OTHERS THEN
611 ROLLBACK;
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613 x_error_message := 'Exception occurred in q_receive_parts: ' || substr(sqlerrm,0,1000);
614 p_result:='E:'||translateString(x_error_message);
615 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_EXCEPTION);
616 end q_receive_parts2;
617
618 --query 24 --if no serial number, receive random --USED
619 procedure q_receive_parts(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_rql_dtl_id IN VARCHAR2,p_qty IN VARCHAR2,p_serials IN VARCHAR2,p_close_short IN VARCHAR2,
620 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
621 IS
622
623 /* --csp views takes more than 2 mins to fetch,
624 CURSOR c_part_line ( b_rld_id IN NUMBER,b_qty IN VARCHAR2) is
625 select hdr.document_number,
626 hdr.source_type,
627 hdr.source_organization_id ,
628 hdr.source_subinventory,
629 hdr.destination_organization_id ,
630 hdr.destination_subinventory,
631 hdr.header_id,
632 line.line_id,
633 line.inventory_item_id,
634 line.revision,
635 line.uom_code,
636 line.serial_number,
637 line.order_quantity,
638 line.ship_quantity,
639 line.req_line_detail_id,
640 line.shipment_number,
641 line.shipment_line_id
642 from csp_receiving_headers_v hdr,
643 csp_receiving_lines_v line
644 where line.req_line_detail_id = b_rld_id
645 and line.header_id = hdr.header_id
646 and rownum <b_qty+1;
647 */
648 CURSOR c_part_line ( b_rld_id IN NUMBER,b_qty IN VARCHAR2)
649 is
650 select mr.reservation_id document_number,
651 rld.source_type,
652 mr.organization_id source_organization_id,
653 mr.subinventory_code source_subinventory,
654 rh.destination_organization_id ,
655 rh.destination_subinventory,
656 mr.reservation_id header_id,
657 mr.reservation_id line_id,
658 mr.inventory_item_id,
659 mr.revision,
660 mr.reservation_uom_code uom_code,
661 mr.serial_number,
662 mr.reservation_quantity order_quantity,
663 decode(mr.serial_number, NULL, mr.reservation_quantity, 1) as ship_quantity,
664 rld.req_line_detail_id,
665 NULL as shipment_number,
666 NULL as shipment_line_id
667 from csp_req_line_details rld,
668 csp_requirement_lines rl,
669 csp_requirement_headers rh,
670 mtl_reservations mr
671 where rld.source_type='RES'
672 and rld.requirement_line_id=rl.requirement_line_id
673 and rl.requirement_header_id=rh.requirement_header_id
674 and rld.source_id=mr.reservation_id
675 and (rh.destination_organization_id<>mr.organization_id OR NVL(mr.subinventory_code,'-999') <> NVL(rh.destination_subinventory,'-999'))
676 and req_line_detail_id=b_rld_id
677 and rownum < b_qty+1
678 union all
679 select oeh.order_number,
680 rld.source_type,
681 oel.ship_from_org_id source_organization_id,
682 oel.subinventory source_subinventory,
683 rh.destination_organization_id ,
684 rh.destination_subinventory,
685 oel.header_id,
686 oel.line_id,
687 oel.inventory_item_id,
688 NULL as REVISION,
689 oel.shipping_quantity_uom uom_code,
690 rss.serial_num serial_number,
691 oel.ordered_quantity order_quantity,
692 decode(rss.serial_num, NULL,oel.shipped_quantity,1) ship_quantity,
693 rld.req_line_detail_id,
694 rch.shipment_num as shipment_number,
695 rcl.shipment_line_id
696 from csp_req_line_details rld,
697 csp_requirement_lines rl,
698 csp_requirement_headers rh ,
699 oe_order_lines_all oel,
700 oe_order_headers_all oeh,
701 rcv_shipment_lines rcl,
702 rcv_shipment_headers rch ,
703 rcv_serials_supply rss
704 where rld.source_type='IO'
705 and rld.source_id=oel.line_id
706 and oel.header_id=oeh.header_id
707 and rld.requirement_line_id=rl.requirement_line_id
708 and rl.requirement_header_id=rh.requirement_header_id
709 and rcl.source_document_code='REQ'
710 AND rcl.requisition_line_id=oel.source_document_line_id
711 and rcl.shipment_header_id=rch.shipment_header_id
712 and rcl.quantity_received< rcl.quantity_shipped
713 and rcl.shipment_line_id = rss.shipment_line_id (+)
714 and req_line_detail_id=b_rld_id
715 and rownum < b_qty+1;
716
717
718
719 l_msg_count NUMBER;
720 l_msg_data VARCHAR2(4000);
721 l_str1 varchar2(4000):=p_rql_dtl_id;
722 l_str2 varchar2(4000):=p_qty;
723 l_str3 varchar2(4000):=p_serials;
724 l_str4 varchar2(4000):=p_close_short;
725
726 l_rld_tab l_num_type;
727 l_qty_tab l_num_type;
728 l_srl_tab l_char_type;
729 l_cs_tab l_char_type;
730
731 l1 number; l2 number; l3 number; l4 number;
732 cnt number :=1;
733 iCnt number:=0;
734 l_found boolean:=false;
735 l_dummy boolean:=false;
736
737 l_trans_items_rec_type CSP_TRANSACTIONS_PUB.Trans_Items_Rec_Type;
738 l_null_trans_rec CSP_TRANSACTIONS_PUB.Trans_Items_Rec_Type;
739 r_part_line c_part_line%rowtype;
740
741 l_result CLOB :='';
742 l_tag VARCHAR2(1000);
743 l_user_id NUMBER;
744 l_resp_id NUMBER;
745 l_app_id NUMBER;
746 begin
747 CSM_UTIL_PKG.LOG('Entering q_receive_parts for '||p_rql_dtl_id||fnd_global.local_chr(10)
748 ||p_qty||fnd_global.local_chr(10)||p_serials||fnd_global.local_chr(10)||p_close_short, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
749
750 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
751 x_error_message := g_pass_error;
752 p_result:='E:'||x_error_message;
753 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
754 RETURN;
755 END IF;
756
757 if instr(l_str1,':,:')>0 then
758 loop
759 exit when instr(l_str1,':,:')=0 ;
760 l1:= instr(l_str1,':,:');
761 l2:= instr(l_str2,':,:');
762 l3:= instr(l_str3,':,:');
763 l4:= instr(l_str4,':,:');
764 l_rld_tab(cnt):=to_number(substr(l_str1,1,l1-1));
765 l_qty_tab(cnt):=to_number(substr(l_str2,1,l2-1));
766 l_srl_tab(cnt):=substr(l_str3,1,l3-1);
767 l_cs_tab(cnt):=upper(substr(l_str4,1,l4-1));
768 cnt:=cnt+1;
769 l_str1:=substr(l_str1,l1+3);
770 l_str2:=substr(l_str2,l2+3);
771 l_str3:=substr(l_str3,l3+3);
772 l_str4:=substr(l_str4,l4+3);
773 end loop;
774 end if;
775 l_rld_tab(cnt):=to_number(l_str1);
776 l_qty_tab(cnt):=to_number(l_str2);
777 l_srl_tab(cnt):=l_str3;
778 l_cs_tab(cnt):=upper(l_str4);
779
780
781 SELECT user_id,responsibility_id, app_id
782 INTO l_user_id,l_resp_id,l_app_id
783 FROM asg_user WHERE user_name=p_user_name;
784
785 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
786
787 FOR I IN 1..l_rld_tab.COUNT
788 LOOP
789
790 l_tag:=to_xml_tag_name('RCV_'||l_rld_tab(I));
791 l_result:=l_result||'<'||l_tag||'>';
792
793 CSM_UTIL_PKG.LOG('Inside Loop for '||l_rld_tab(I)||' '||fnd_global.local_chr(10)
794 ||l_qty_tab(I)||' '||fnd_global.local_chr(10)||l_srl_tab(I)||' '||fnd_global.local_chr(10)||' '||l_cs_tab(I), 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
795
796
797 l_dummy:=false;
798 l_found:=false;
799 FOR r_part_line IN c_part_line(l_rld_tab(I),l_qty_tab(I))
800 LOOP
801 l_found:=true;
802 l_trans_items_rec_type:=l_null_trans_rec;
803
804 l_trans_items_rec_type.INVENTORY_ITEM_ID := r_part_line.INVENTORY_ITEM_ID;
805 l_trans_items_rec_type.REVISION := r_part_line.REVISION;
806 l_trans_items_rec_type.SERIAL_NUMBER := r_part_line.serial_number;
807 IF r_part_line.serial_number IS NULL THEN
808 l_trans_items_rec_type.QUANTITY := l_qty_tab(I);
809 ELSE
810 l_trans_items_rec_type.QUANTITY := 1;
811 END IF;
812 l_trans_items_rec_type.UOM_CODE := r_part_line.uom_code;
813 l_trans_items_rec_type.FRM_ORGANIZATION_ID := r_part_line.source_organization_id;
814 l_trans_items_rec_type.FRM_SUBINVENTORY_CODE := r_part_line.source_subinventory;
815 l_trans_items_rec_type.TO_ORGANIZATION_ID := r_part_line.destination_organization_id;
816 l_trans_items_rec_type.TO_SUBINVENTORY_CODE := r_part_line.destination_subinventory;
817 l_trans_items_rec_type.SHIPMENT_NUMBER := r_part_line.shipment_number;
818 l_trans_items_rec_type.SHIPMENT_LINE_ID := r_part_line.shipment_line_id;
819
820 CSM_UTIL_PKG.LOG
821 ( 'Calling API CSP_TRANSACTIONS_PUB.receive_requirement_trans for '||r_part_line.source_type||':'||r_part_line.line_id||':'||r_part_line.serial_number ,
822 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_STATEMENT );
823
824 CSP_TRANSACTIONS_PUB.receive_requirement_trans (
825 p_trans_header_id => r_part_line.header_id,
826 p_trans_line_id => r_part_line.line_id,
827 p_trans_record => l_trans_items_rec_type,
828 p_trans_type => r_part_line.source_type,
829 p_req_line_detail_id => r_part_line.req_line_detail_id,
830 p_close_short => l_cs_tab(I),
831 x_return_status => x_return_status,
832 x_msg_count => l_msg_count,
833 x_msg_data => l_msg_data
834 );
835
836 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
837 l_msg_data := replace(fnd_msg_pub.get(l_msg_count,'F'),fnd_global.local_chr(10), ' ');
838 x_error_message := 'Error in CSP_TRANSACTIONS_PUB.receive_requirement_trans' || ' for PK : ' || r_part_line.line_id || ' Details:' || l_msg_data ;
839 CSM_UTIL_PKG.LOG( x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_ERROR);
840 IF(r_part_line.serial_number IS NOT NULL) THEN
841 l_result:=l_result||'<ERROR><SERIAL>'||r_part_line.serial_number||'</SERIAL><MSG>'||translateString(l_msg_data)||'</MSG></ERROR>';
842 ELSE
843 l_result:=l_result||'<ERROR><MSG>'||translateString(l_msg_data)||'</MSG></ERROR>';
844 END IF;
845 ELSE
846 CSM_SYSTEM_ITEM_EVENT_PKG.RECEIVED_MTL_ONHAND(r_part_line.destination_organization_id,r_part_line.destination_subinventory
847 ,r_part_line.INVENTORY_ITEM_ID,l_user_id);
848 IF(r_part_line.serial_number IS NOT NULL) THEN
849 CSM_SERIAL_NUMBERS_EVENT_PKG.RECEIVED_MTL_SERIAL_NUM(r_part_line.destination_organization_id,r_part_line.destination_subinventory
850 ,r_part_line.INVENTORY_ITEM_ID,r_part_line.serial_number,l_user_id);
851 END IF;
852 IF r_part_line.source_type='IO' AND NOT l_dummy THEN
853 FOR rec IN (SELECT USER_ID FROM CSP_REQ_LINE_DETAILS rld ,CSM_REQ_LINE_DETAILS_ACC acc
854 WHERE rld.SOURCE_TYPE='IO' AND rld.SOURCE_ID=r_part_line.line_id AND acc.REQ_LINE_DETAIL_ID=rld.REQ_LINE_DETAIL_ID)
855 LOOP --to update received qty
856 l_dummy := asg_download.mark_dirty(
857 p_pub_item => 'CSM_ORDER_LINES'
858 , p_accessid => r_part_line.line_id
859 , p_userid => rec.user_id
860 , p_dml => 'U'
861 , p_timestamp => sysdate
862 );
863 END LOOP;
864 END IF;
865
866 IF(r_part_line.serial_number IS NOT NULL) THEN
867 l_result:=l_result||'<SUCCESS><ORD_NUM>'||r_part_line.document_number||'</ORD_NUM><ORD_TYPE>'||r_part_line.source_type
868 ||'</ORD_TYPE><SHP_QTY>'||r_part_line.ship_quantity||'</SHP_QTY><QTY>1</QTY><SERIAL>'||r_part_line.serial_number||'</SERIAL></SUCCESS>';
869 ELSE
870 l_result:=l_result||'<SUCCESS><ORD_NUM>'||r_part_line.document_number||'</ORD_NUM><ORD_TYPE>'||r_part_line.source_type
871 ||'</ORD_TYPE><SHP_QTY>'||r_part_line.ship_quantity||'</SHP_QTY><QTY>'||l_qty_tab(I)||'</QTY></SUCCESS>';
872 END IF;
873 END IF;
874 END LOOP;
875 IF NOT l_found THEN
876 l_result:=l_result||'<ERROR><MSG>No Line Found for receive</MSG></ERROR>';
877 END IF;
878 l_result:=l_result||'</'||l_tag||'>';
879 END LOOP;
880
881 CSM_UTIL_PKG.LOG('Leaving q_receive_parts ', 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_PROCEDURE);
882 x_return_status := FND_API.G_RET_STS_SUCCESS;
883 p_result:='S:<RESULT>'||l_result||'</RESULT>';
884 EXCEPTION
885 WHEN FND_API.G_EXC_ERROR THEN
886 ROLLBACK;
887 x_return_status := FND_API.G_RET_STS_SUCCESS;
888 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts',FND_LOG.LEVEL_PROCEDURE);
889 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
890 p_result:='E:'||translateString(x_error_message);
891 WHEN OTHERS THEN
892 ROLLBACK;
893 x_return_status := FND_API.G_RET_STS_SUCCESS;
894 x_error_message := 'Exception occurred in q_receive_parts: ' || substr(sqlerrm,0,1000);
895 p_result:='E:'||translateString(x_error_message);
896 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_receive_parts', FND_LOG.LEVEL_EXCEPTION);
897 end q_receive_parts;
898
899 procedure q_upd_rqmt_ship_to( p_user_name IN VARCHAR2,p_password IN VARCHAR2,P_REQ_HDR_ID IN VARCHAR2,P_ADDRESS_TYPE IN VARCHAR2,
900 P_SHIP_LOC_SRC IN VARCHAR2,P_SHIP_LOC_ID IN VARCHAR2,p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
901 IS
902 l_rqmt_header_Rec csp_requirement_headers_pvt.requirement_header_rec_type;
903 l_header_rec csp_parts_requirement.Header_rec_type;
904 l_msg_count NUMBER;
905 l_msg_data VARCHAR2(2000);
906 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
907 l_party_site_id NUMBER;
908 l_customer_id NUMBER;
909 l_cust_account_id NUMBER;
910 l_api_version_number CONSTANT NUMBER := 1.0;
911 l_task_id NUMBER;
912
913 BEGIN
914 x_return_status := FND_API.G_RET_STS_SUCCESS;
915
916 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
917 x_error_message := g_pass_error;
918 p_result:='E:'||x_error_message;
919 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_rqmt_ship_to', FND_LOG.LEVEL_PROCEDURE);
920 RETURN;
921 END IF;
922 x_error_message:='Success';
923
924 l_header_rec.requirement_header_id:=P_REQ_HDR_ID;
925 l_header_rec.ADDRESS_TYPE := NVL(P_ADDRESS_TYPE,'R'); --C or R or T
926
927 SELECT TASK_ID INTO l_task_id
928 FROM JTF_TASK_ASSIGNMENTS
929 WHERE TASK_ASSIGNMENT_ID=(SELECT TASK_ASSIGNMENT_ID FROM CSP_REQUIREMENT_HEADERS WHERE REQUIREMENT_HEADER_ID=P_REQ_HDR_ID);
930
931 IF NVL(P_SHIP_LOC_SRC,'HR')='HZ' THEN
932 l_header_rec.SHIP_TO_LOCATION_ID:=get_hr_location(p_user_name,l_task_id,P_SHIP_LOC_ID); --party site
933 ELSE
934 l_header_rec.SHIP_TO_LOCATION_ID:=P_SHIP_LOC_ID;
935 END IF;
936
937 l_rqmt_header_Rec.requirement_header_id := l_header_rec.requirement_header_id;
938 l_rqmt_header_Rec.address_type := l_header_Rec.address_type;
939 l_rqmt_header_Rec.last_update_date := sysdate;
940 l_rqmt_header_Rec.ship_to_location_id := nvl(l_header_rec.ship_to_location_id, FND_API.G_MISS_NUM);
941 l_rqmt_header_Rec.last_updated_by := asg_base.get_user_id(p_user_name);
942 l_rqmt_header_Rec.last_update_login := nvl(fnd_global.login_id, -1);
943
944 --l_rqmt_header_Rec.task_id := nvl(l_header_rec.task_id, FND_API.G_MISS_NUM);
945 --l_rqmt_header_Rec.need_by_date:= nvl(l_header_rec.need_by_date, FND_API.G_MISS_DATE);
946
947
948
949 CSP_REQUIREMENT_HEADERS_PVT.Update_requirement_headers(
950 P_Api_Version_Number => l_api_Version_number,P_Init_Msg_List => FND_API.G_TRUE,P_Commit => FND_API.G_FALSE,
951 p_validation_level => null,
952 P_REQUIREMENT_HEADER_Rec => l_rqmt_header_rec,
953 X_Return_Status => l_Return_status,
954 X_Msg_Count => l_msg_count,
955 X_Msg_Data => l_msg_data
956 );
957
958 IF l_return_status IS NULL THEN
959 p_result:='E:No message returned by CSP api.';
960 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_rqmt_ship_to', FND_LOG.LEVEL_PROCEDURE);
961 RETURN;
962 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
963 RAISE FND_API.G_EXC_ERROR;
964 END IF;
965
966 p_result:='S:'||to_char(l_Return_status);
967 CSM_UTIL_PKG.LOG('Leaving after successful execution', 'CSM_MULTI_MOBQRY_PKG.q_upd_rqmt_ship_to',FND_LOG.LEVEL_PROCEDURE);
968 EXCEPTION
969 WHEN FND_API.G_EXC_ERROR THEN
970 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_upd_rqmt_ship_to',FND_LOG.LEVEL_PROCEDURE);
971 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
972 p_result:='E:'||translateString(x_error_message);
973 WHEN OTHERS THEN
974 x_error_message := 'Exception occurred in q_upd_rqmt_ship_to: ' || substr(sqlerrm,1,3900);
975 p_result:='E:'||translateString(x_error_message);
976 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_rqmt_ship_to', FND_LOG.LEVEL_PROCEDURE);
977 END q_upd_rqmt_ship_to;
978
979 --query 21
980 procedure q_upd_oe_shipTo(p_user_name IN VARCHAR2,p_password IN VARCHAR2,P_REQ_HDR_ID IN VARCHAR2,P_ADDRESS_TYPE IN VARCHAR2,
981 P_SHIP_LOC_SRC IN VARCHAR2,P_SHIP_LOC_ID IN VARCHAR2, p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
982 IS
983 l_return_status VARCHAR2(10);
984 l_msg_count NUMBER;
985 l_msg_data VARCHAR2(4000);
986 l_user_id NUMBER;
987 l_resp_id NUMBER;
988 l_app_id NUMBER;
989 l_org_id NUMBER;
990 l_task_id NUMBER;
991 l_err boolean:=false;
992 l_hr_loc_id NUMBER;
993 begin
994 CSM_UTIL_PKG.LOG('Entering q_upd_oe_shipTo for '||P_REQ_HDR_ID||':'||P_ADDRESS_TYPE||':'||P_SHIP_LOC_ID, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo', FND_LOG.LEVEL_PROCEDURE);
995 x_return_status := FND_API.G_RET_STS_SUCCESS;
996
997 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
998 x_error_message := g_pass_error;
999 l_err:=true;
1000 ELSIF P_SHIP_LOC_ID IS NULL THEN
1001 x_error_message := 'Location specified cannot be found in server.';
1002 l_err:=true;
1003 END IF;
1004
1005 SELECT TASK_ID INTO l_task_id
1006 FROM JTF_TASK_ASSIGNMENTS
1007 WHERE TASK_ASSIGNMENT_ID=(SELECT TASK_ASSIGNMENT_ID FROM CSP_REQUIREMENT_HEADERS WHERE REQUIREMENT_HEADER_ID=P_REQ_HDR_ID);
1008
1009 IF NVL(P_SHIP_LOC_SRC,'HR')='HZ' THEN
1010 l_hr_loc_id:=get_hr_location(p_user_name,l_task_id,P_SHIP_LOC_ID); --party site
1011 ELSE
1012 l_hr_loc_id:=P_SHIP_LOC_ID;
1013 END IF;
1014
1015 IF l_err THEN
1016 p_result:='E:'||x_error_message;
1017 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo', FND_LOG.LEVEL_PROCEDURE);
1018 RETURN;
1019 END IF;
1020
1021
1022 SELECT user_id,responsibility_id, app_id ,org_id
1023 INTO l_user_id,l_resp_id,l_app_id,l_org_id
1024 FROM asg_user WHERE user_name=p_user_name;
1025
1026 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
1027 MO_GLOBAL.INIT ('CSM');
1028
1029 CSP_PARTS_ORDER.upd_oe_ship_to_add (
1030 p_req_header_id =>P_REQ_HDR_ID
1031 ,p_new_hr_loc_id => l_hr_loc_id
1032 ,p_new_add_type => NVL(P_ADDRESS_TYPE,'R')
1033 ,p_update_req_header =>'Y'
1034 ,x_return_status =>l_return_status
1035 ,x_msg_count =>l_msg_count
1036 ,x_msg_data =>l_msg_data );
1037
1038
1039 IF l_return_status IS NULL THEN
1040 p_result:='E:No message returned by CSP api.';
1041 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo', FND_LOG.LEVEL_PROCEDURE);
1042 RETURN;
1043 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1044 RAISE FND_API.G_EXC_ERROR;
1045 END IF;
1046
1047 CSM_UTIL_PKG.LOG('Leaving q_upd_oe_shipTo ', 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo', FND_LOG.LEVEL_PROCEDURE);
1048 p_result:='S:'||to_char(l_return_status);
1049 EXCEPTION
1050 WHEN FND_API.G_EXC_ERROR THEN
1051 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo',FND_LOG.LEVEL_PROCEDURE);
1052 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
1053 p_result:='E:'||translateString(x_error_message);
1054 WHEN OTHERS THEN
1055 x_error_message := 'Exception occurred in q_upd_oe_shipTo: ' || substr(sqlerrm,0,1000);
1056 p_result:='E:'||translateString(x_error_message);
1057 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo', FND_LOG.LEVEL_EXCEPTION);
1058 end q_upd_oe_shipTo;
1059
1060 --query 22
1061 procedure q_upd_oe_line_shipMethod(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_ord_line_id IN NUMBER,p_ship_mtd IN VARCHAR2,
1062 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
1063 IS
1064 l_return_status VARCHAR2(10);
1065 l_msg_count NUMBER;
1066 l_msg_data VARCHAR2(4000);
1067 l_user_id NUMBER;
1068 l_resp_id NUMBER;
1069 l_app_id NUMBER;
1070 l_org_id NUMBER;
1071 begin
1072 CSM_UTIL_PKG.LOG('Entering q_upd_oe_line_shipMethod for '||p_ord_line_id||':'||p_ship_mtd, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_line_shipMethod', FND_LOG.LEVEL_PROCEDURE);
1073
1074 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
1075 x_error_message := g_pass_error;
1076 p_result:='E:'||x_error_message;
1077 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_shipTo', FND_LOG.LEVEL_PROCEDURE);
1078 RETURN;
1079 END IF;
1080
1081
1082 SELECT user_id,responsibility_id, app_id ,org_id
1083 INTO l_user_id,l_resp_id,l_app_id,l_org_id
1084 FROM asg_user WHERE user_name=p_user_name;
1085
1086 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
1087 MO_GLOBAL.INIT ('CSM');
1088
1089 CSP_PARTS_ORDER.upd_oe_line_ship_method (
1090 p_oe_line_id=>p_ord_line_id
1091 ,p_ship_method =>p_ship_mtd
1092 ,x_return_status =>l_return_status
1093 ,x_msg_count =>l_msg_count
1094 ,x_msg_data =>l_msg_data);
1095
1096
1097 IF l_return_status IS NULL THEN
1098 p_result:='E:No message returned by CSP api.';
1099 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_line_shipMethod', FND_LOG.LEVEL_PROCEDURE);
1100 RETURN;
1101 ELSIF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1102 RAISE FND_API.G_EXC_ERROR;
1103 END IF;
1104
1105 CSM_UTIL_PKG.LOG('Leaving q_upd_oe_line_shipMethod ', 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_line_shipMethod', FND_LOG.LEVEL_PROCEDURE);
1106 p_result:='S:'||to_char(l_return_status);
1107 EXCEPTION
1108 WHEN FND_API.G_EXC_ERROR THEN
1109 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_line_shipMethod',FND_LOG.LEVEL_PROCEDURE);
1110 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
1111 p_result:='E:'||translateString(x_error_message);
1112 WHEN OTHERS THEN
1113 x_error_message := 'Exception occurred in q_upd_oe_line_shipMethod: ' || substr(sqlerrm,0,1000);
1114 p_result:='E:'||translateString(x_error_message);
1115 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_oe_line_shipMethod', FND_LOG.LEVEL_EXCEPTION);
1116 end q_upd_oe_line_shipMethod;
1117
1118
1119 FUNCTION get_hr_location(p_user_name in varchar2,p_task_id IN NUMBER,p_party_site_id NUMBER) RETURN NUMBER
1120 IS
1121 l_ret VARCHAR2(10);
1122 l_msg_count NUMBER;
1123 l_msg_data VARCHAR2(4000);
1124 l_hz_loc_id NUMBER;
1125 x_loc_id NUMBER:=NULL;
1126 l_user_id NUMBER;
1127 l_resp_id NUMBER;
1128 l_app_id NUMBER;
1129 l_org_id NUMBER;
1130 l_party_id NUMBER;
1131 l_account_id NUMBER;
1132 l_cas_id NUMBER;
1133
1134 begin
1135 SELECT user_id,responsibility_id, app_id
1136 INTO l_user_id,l_resp_id,l_app_id
1137 FROM asg_user WHERE user_name=p_user_name;
1138
1139 OPEN c_get_hr_loc_id(l_user_id,p_party_site_id);
1140 FETCH c_get_hr_loc_id INTO x_loc_id;
1141 CLOSE c_get_hr_loc_id;
1142
1143 IF x_loc_id IS NOT NULL THEN
1144 RETURN x_loc_id;
1145 END IF;
1146
1147 CSM_UTIL_PKG.LOG('Creating new loc as No HR Location found for party site id:'||p_party_site_id, 'CSM_MULTI_MOBQRY_PKG.get_hr_location',FND_LOG.LEVEL_PROCEDURE);
1148
1149 SELECT a.cust_account_id ,b.party_id,b.location_id,a.org_id
1150 INTO l_account_id,l_party_id,x_loc_id,l_org_id
1151 FROM hz_cust_acct_sites_all a, hz_party_sites b,jtf_tasks_b t,cs_incidents_all_b i
1152 WHERE b.PARTY_SITE_ID=a.PARTY_SITE_ID
1153 AND t.task_id=p_task_id
1154 and t.sourcE_object_type_code='SR'
1155 and i.customer_id=b.party_id
1156 and t.sourcE_object_id=i.incident_id
1157 AND a.ORG_ID=i.org_id
1158 AND b.PARTY_SITE_ID=p_party_site_id;
1159
1160 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
1161 --mo_global.set_policy_context('S',l_org_id); also done by CSP api ..so leave it to them
1162 MO_GLOBAL.INIT ('CSM');
1163
1164 l_hz_loc_id:=x_loc_id;
1165
1166 CSM_UTIL_PKG.LOG('Before call to CSP: hz_location_id:'||l_hz_loc_id, 'CSM_MULTI_MOBQRY_PKG.get_hr_location',FND_LOG.LEVEL_PROCEDURE);
1167
1168 csp_ship_to_address_pvt.cust_inv_loc_link
1169 ( p_api_version => 1.0
1170 ,p_Init_Msg_List => FND_API.G_FALSE
1171 ,p_commit => FND_API.G_FALSE
1172 ,px_location_id => x_loc_id
1173 ,p_party_site_id => p_party_site_id
1174 ,p_cust_account_id => l_account_id
1175 ,p_customer_id => l_party_id
1176 ,p_org_id => l_org_id
1177 ,p_attribute_category => null,p_attribute1 => null
1178 ,p_attribute2 => null,p_attribute3 => null
1179 ,p_attribute4 => null ,p_attribute5 => null
1180 ,p_attribute6 => null,p_attribute7 => null
1181 ,p_attribute8 => null,p_attribute9 => null
1182 ,p_attribute10 => null,p_attribute11 => null
1183 ,p_attribute12 => null,p_attribute13 => null
1184 ,p_attribute14 => null,p_attribute15 => null
1185 ,p_attribute16 => null,p_attribute17 => null
1186 ,p_attribute18 => null,p_attribute19 => null
1187 ,p_attribute20 => null,x_return_status => l_ret ,x_msg_count => l_msg_count ,x_msg_data => l_msg_data);
1188
1189
1190 CSM_UTIL_PKG.LOG('After call to CSP: x_loc_id:-'||x_loc_id ||' -:-ret status:'||l_ret, 'CSM_MULTI_MOBQRY_PKG.get_hr_location',FND_LOG.LEVEL_PROCEDURE);
1191
1192 if ((l_ret IS NULL AND x_loc_id = l_hz_loc_id) OR (l_ret <> FND_API.G_RET_STS_SUCCESS)) THEN
1193 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||fnd_msg_pub.get(l_msg_count,'F'), 'CSM_MULTI_MOBQRY_PKG.get_hr_location',FND_LOG.LEVEL_PROCEDURE);
1194 RAISE FND_API.G_EXC_ERROR;
1195 END IF;
1196
1197 RETURN x_loc_id;
1198 end get_hr_location;
1199
1200 procedure create_ship_to_address(p_user_name IN VARCHAR2,l_ta_id IN NUMBER
1201 ,l_adrs_line_1 IN VARCHAR2 ,l_adrs_line_2 IN VARCHAR2,l_adrs_line_3 IN VARCHAR2,l_adrs_line_4 IN VARCHAR2
1202 ,l_city IN VARCHAR2,l_county IN VARCHAR2, l_state IN VARCHAR2,l_province IN VARCHAR2,l_phonetic IN VARCHAR2
1203 ,l_country IN VARCHAR2,l_pincode IN VARCHAR2
1204 ,x_loc_id IN OUT NUMBER,x_cust_id OUT NOCOPY NUMBER, x_ovn IN OUT NUMBER
1205 ,l_ret OUT NOCOPY VARCHAR2 ,l_msg_cnt OUT NOCOPY NUMBER ,l_msg_data OUT NOCOPY VARCHAR2)
1206 Is
1207
1208 l_rs_type VARCHAR2(100):='RS_EMPLOYEE';
1209 l_rs_id NUMBER;
1210 l_user_id NUMBER;
1211 l_resp_id NUMBER;
1212 l_app_id NUMBER;
1213 l_org_id NUMBER;
1214
1215 l_style VARCHAR2(200):='GENERIC';
1216
1217 BEGIN
1218
1219 SELECT user_id,responsibility_id, app_id ,org_id,resource_id
1220 INTO l_user_id,l_resp_id,l_app_id,l_org_id,l_rs_id
1221 FROM asg_user WHERE user_name=p_user_name;
1222
1223 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
1224 --mo_global.set_policy_context('S',l_org_id); also done by CSP api ..so leave it to them
1225 MO_GLOBAL.INIT ('CSM');
1226
1227 csp_ship_to_address_pvt.ship_to_address_handler(
1228 P_TASK_ASSIGNMENT_ID => l_ta_id
1229 ,P_RESOURCE_TYPE => l_rs_type ,P_RESOURCE_ID => l_rs_id
1230 ,P_CUSTOMER_ID => x_cust_id ,P_LOCATION_ID => x_loc_id
1231 ,P_STYLE => l_style
1232 ,P_ADDRESS_LINE_1 => l_adrs_line_1 ,P_ADDRESS_LINE_2 => l_adrs_line_2
1233 ,P_ADDRESS_LINE_3 => l_adrs_line_3
1234 --,P_ADDRESS_LINE_4 => l_adrs_line_4
1235 ,P_COUNTRY => l_country
1236 ,P_POSTAL_CODE => l_pincode
1237 ,P_REGION_1 => l_county
1238 ,P_REGION_2 => l_state
1239 ,P_REGION_3 => null
1240 ,P_TOWN_OR_CITY => l_city
1241 ,P_TELEPHONE_NUMBER_1 => null
1242 ,P_TELEPHONE_NUMBER_2 => null
1243 ,P_TELEPHONE_NUMBER_3 => null
1244 ,P_TAX_NAME => null
1245 ,P_LOC_INFORMATION13 => null,P_LOC_INFORMATION14=> null,P_LOC_INFORMATION15=> null
1246 ,P_LOC_INFORMATION16 => null,P_LOC_INFORMATION17=> null ,P_LOC_INFORMATION18=> null
1247 ,P_LOC_INFORMATION19 => null,P_LOC_INFORMATION20=> null,P_TIMEZONE=> null
1248 ,P_PRIMARY_FLAG => 'N'
1249 ,P_STATUS => null
1250 ,P_OBJECT_VERSION_NUMBER => x_ovn
1251 ,p_API_VERSION_NUMBER => 1.0
1252 ,P_INIT_MSG_LIST => 'T'
1253 ,P_COMMIT => 'F'
1254 ,P_ATTRIBUTE_CATEGORY=> null,P_ATTRIBUTE1=> null ,P_ATTRIBUTE2=> null ,P_ATTRIBUTE3=> null
1255 ,P_ATTRIBUTE4=> null ,P_ATTRIBUTE5=> null,P_ATTRIBUTE6=> null ,P_ATTRIBUTE7=> null
1256 ,P_ATTRIBUTE8=> null ,P_ATTRIBUTE9=> null ,P_ATTRIBUTE10=> null ,P_ATTRIBUTE11=> null
1257 ,P_ATTRIBUTE12=> null ,P_ATTRIBUTE13=> null ,P_ATTRIBUTE14=> null ,P_ATTRIBUTE15=> null
1258 ,P_ATTRIBUTE16=> null ,P_ATTRIBUTE17=> null ,P_ATTRIBUTE18=> null ,P_ATTRIBUTE19=> null ,P_ATTRIBUTE20=> null
1259 ,P_PROVINCE => l_province
1260 ,P_ADDRESS_LINES_PHONETIC => l_phonetic
1261 ,X_RETURN_STATUS => l_ret
1262 ,X_MSG_COUNT => l_msg_cnt
1263 ,X_MSG_DATA => l_msg_data );
1264
1265 END create_ship_to_address;
1266
1267 procedure q_create_shipTo(p_user_name IN VARCHAR2,p_password IN VARCHAR2,p_ta_id IN NUMBER,p_address IN VARCHAR2,
1268 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
1269 is
1270
1271 l_adrs_line_1 varchar2(240);
1272 l_adrs_line_2 varchar2(240);
1273 l_adrs_line_3 varchar2(240);
1274 l_adrs_line_4 varchar2(240);
1275 l_country varchar2(60);
1276 l_pincode varchar2(30);
1277 l_city varchar2(30);
1278 l_county varchar2(120);
1279 l_state varchar2(120);
1280 l_province varchar2(120);
1281 l_phonetic VARCHAR2(500);
1282
1283 --out vars
1284 x_ovn NUMBER;
1285 x_loc_id NUMBER;
1286 x_cust_id NUMBER;
1287 l_return_status VARCHAR2(10);
1288 l_msg_count NUMBER;
1289 l_msg_data VARCHAR2(4000);
1290
1291 l_hz_loc_id NUMBER;
1292 l_hz_psite_id NUMBER;
1293 l_hr_loc_id NUMBER;
1294 l_cust_acct_site_id NUMBER;
1295 l_org_id NUMBER;
1296
1297 xmlD CLOB:=empty_clob();
1298 l_COL_NAME_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1299 L_COL_VALUE_LIST CSM_VARCHAR_LIST := CSM_VARCHAR_LIST();
1300
1301 begin
1302
1303 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
1304 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1305 x_error_message := g_pass_error;
1306 p_result:='E:'||x_error_message;
1307 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_create_shipTo', FND_LOG.LEVEL_PROCEDURE);
1308 RETURN;
1309 END IF;
1310
1311 x_return_status := FND_API.G_RET_STS_SUCCESS;
1312 x_error_message:='Success';
1313
1314 CSM_UTIL_PKG.LOG('Entering q_create_shipTo with xml '||p_address, 'CSM_MULTI_MOBQRY_PKG.q_create_shipTo', FND_LOG.LEVEL_PROCEDURE);
1315
1316 xmlD:='<?xml version="1.0"?><ROWSET><ROW>'||p_address||'</ROW></ROWSET>';
1317
1318 PARSE_XML(xmlD,L_COL_NAME_LIST,L_COL_VALUE_LIST,true);
1319 FOR I IN 1..L_COL_NAME_LIST.COUNT
1320 LOOP
1321 IF L_COL_NAME_LIST(I)='ADDRESS1' THEN
1322 l_adrs_line_1:=L_COL_VALUE_LIST(I);
1323 ELSIF L_COL_NAME_LIST(I)='ADDRESS2' THEN
1324 l_adrs_line_2:=L_COL_VALUE_LIST(I);
1325 ELSIF L_COL_NAME_LIST(I)='ADDRESS3' THEN
1326 l_adrs_line_3:=L_COL_VALUE_LIST(I);
1327 ELSIF L_COL_NAME_LIST(I)='ADDRESS4' THEN
1328 l_adrs_line_4:=L_COL_VALUE_LIST(I);
1329 ELSIF L_COL_NAME_LIST(I)='CITY' THEN
1330 l_city:=L_COL_VALUE_LIST(I);
1331 ELSIF L_COL_NAME_LIST(I)='COUNTY' THEN
1332 l_county:=L_COL_VALUE_LIST(I);
1333 ELSIF L_COL_NAME_LIST(I)='STATE' THEN
1334 l_state:=L_COL_VALUE_LIST(I);
1335 ELSIF L_COL_NAME_LIST(I)='PROVINCE' THEN
1336 l_province:=L_COL_VALUE_LIST(I);
1337 ELSIF L_COL_NAME_LIST(I)='ADDRESS_LINES_PHONETIC' THEN
1338 l_phonetic:=L_COL_VALUE_LIST(I);
1339 ELSIF L_COL_NAME_LIST(I)='COUNTRY' THEN
1340 l_country:=L_COL_VALUE_LIST(I);
1341 ELSIF L_COL_NAME_LIST(I)='POSTAL_CODE' THEN
1342 l_pincode:=L_COL_VALUE_LIST(I);
1343 END IF;
1344 END LOOP;
1345
1346 CSM_UTIL_PKG.LOG(l_adrs_line_1||','||l_adrs_line_2||','||l_adrs_line_3||','||l_adrs_line_4||','||l_city||','||l_county||','
1347 ||l_state||','||l_province||','||l_phonetic ||','||l_country||','||l_pincode ,'CSM_MULTI_MOBQRY_PKG.q_create_shipTo', FND_LOG.LEVEL_PROCEDURE);
1348
1349 create_ship_to_address(p_user_name,p_ta_id,l_adrs_line_1 ,l_adrs_line_2 ,l_adrs_line_3,l_adrs_line_4 ,l_city ,l_county
1350 ,l_state,l_province,l_phonetic,l_country,l_pincode ,x_loc_id,x_cust_id, x_ovn,l_return_status ,l_msg_count ,l_msg_data );
1351
1352 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1353 RAISE FND_API.G_EXC_ERROR;
1354 END IF;
1355
1356
1357 l_hr_loc_id:=x_loc_id;
1358
1359 select b.party_site_id,c.location_id ,a.address_id,a.org_id
1360 into l_hz_psite_id,l_hz_loc_id,l_cust_acct_site_id,l_org_id
1361 from po_location_associations_all a, HZ_CUST_ACCT_SITES_ALL b ,hz_party_sites c
1362 where a.location_id=x_loc_id
1363 and b.cust_account_id=x_cust_id
1364 and a.address_id=b.cust_acct_site_id
1365 and b.party_site_id=c.party_site_id;
1366
1367 --PO TABLE column meaning
1368 --CUSTOMER_ID = x_cust_id (cust_account_id)
1369 --ADDRESS_ID =l_cust_acct_site_id (cust_acct_site_id)
1370 --LOCATION_ID = l_hr_loc_id
1371
1372 CSM_UTIL_PKG.LOG('Leaving q_create_shipTo ', 'CSM_MULTI_MOBQRY_PKG.q_create_shipTo', FND_LOG.LEVEL_PROCEDURE);
1373 p_result:='S:'||to_char(l_hz_psite_id)||':'||to_char(l_hz_loc_id)||':'||to_char(l_cust_acct_site_id)||':'||to_char(l_hr_loc_id)
1374 ||':'||to_char(l_org_id)||':'||to_char(x_cust_id);
1375 EXCEPTION
1376 WHEN FND_API.G_EXC_ERROR THEN
1377 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_create_shipTo',FND_LOG.LEVEL_PROCEDURE);
1378 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
1379 p_result:='E:'||translateString(x_error_message);
1380 WHEN OTHERS THEN
1381 x_error_message := 'Exception occurred in q_create_shipTo: ' || substr(sqlerrm,0,1000);
1382 p_result:='E:'||translateString(x_error_message);
1383 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_create_shipTo', FND_LOG.LEVEL_EXCEPTION);
1384 end q_create_shipTo;
1385
1386 procedure q_available_parts( p_user_name IN VARCHAR2,p_password IN VARCHAR2,P_ITEM_NAME IN VARCHAR2,P_QTY IN VARCHAR2,P_WH_TYPE IN VARCHAR2,
1387 P_NEED_BY IN VARCHAR2,P_SUBSTITUTE IN VARCHAR2,P_DIST IN NUMBER,
1388 P_INV_MINE IN VARCHAR2,P_INV_TECH IN VARCHAR2,P_INC_CLOSED IN VARCHAR2,P_QTY_TYPE IN VARCHAR2, P_LOC_SRC IN VARCHAR2, P_LOC_ID IN NUMBER,
1389 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
1390 Is
1391 PRAGMA AUTONOMOUS_TRANSACTION;
1392
1393 CURSOR c_get_item(p_item VARCHAR2, p_org_id NUMBER)
1394 IS
1395 SELECT INVENTORY_ITEM_ID FROM MTL_SYSTEM_ITEMS_B
1396 WHERE UPPER(SEGMENT1) = UPPER(p_item)
1397 AND ORGANIZATION_ID = p_ORG_ID;
1398
1399
1400 /*CURSOR C_GET_ORG(p_ORG_ID NUMBER) IS
1401 SELECT p.master_organization_id,tl.NAME
1402 FROM HR_ALL_ORGANIZATION_UNITS_TL tl,mtl_parameters p
1403 WHERE LANGUAGE = asg_base.get_language(p_user_name)
1404 AND tl.ORGANIZATION_ID = p.master_organization_id
1405 AND p.ORGANIZATION_ID =p_org_id;*/
1406
1407 CURSOR C_GET_ORG(p_ORG_ID NUMBER) IS
1408 SELECT tl.organization_id,tl.NAME
1409 FROM HR_ALL_ORGANIZATION_UNITS_TL tl
1410 WHERE LANGUAGE = asg_base.get_language(p_user_name)
1411 AND tl.ORGANIZATION_ID = p_org_id;
1412
1413
1414 CURSOR C_GET_ORG_CODE(p_ORG_ID NUMBER) IS
1415 SELECT organization_code from mtl_parameters
1416 WHERE ORGANIZATION_ID =p_org_id;
1417
1418 CURSOR c_get_ohq(b_req_item_id NUMBER) IS
1419 SELECT acc.organization_id, acc.subinventory_code,acc.REQUIRED_ITEM_ID,acc.REQUIRED_ITEM_REV,acc.REQUIRED_QUANTITY,
1420 acc.supplied_item_id,acc.supplied_item_rev,acc.supplied_quantity,acc.SUPPLIED_ITEM_TYPE,acc.source_type_code,
1421 acc.SHIPPING_DATE ,acc.SHIPPING_METHOD,acc.SHIPPING_COST,acc.ARRIVAL_DATE,acc.DISTANCE,acc.OPEN_OR_CLOSED,
1422 msik.concatenated_segments as item_name, msik.serial_number_control_code as serial_number_control_code,
1423 tl.description as description, msik.primary_uom_code as uom_code,msik.comms_nl_trackable_flag as comms_nl_trackable_flag,
1424 msik.material_billable_flag, msik.RESTRICT_LOCATORS_CODE, msik.LOCATION_CONTROL_CODE, msik.restrict_subinventories_code, msik.RECOVERED_PART_DISP_CODE
1425 ,flv.meaning as ship_method_meaning
1426 FROM csp_available_parts_temp acc, mtl_system_items_kfv msik, mtl_system_items_tl tl ,fnd_lookup_values flv
1427 WHERE msik.inventory_item_id = acc.supplied_item_id AND msik.organization_id = acc.organization_id
1428 AND acc.required_item_id=b_req_item_id
1429 AND msik.mtl_transactions_enabled_flag = 'Y' AND tl.inventory_item_id = msik.inventory_item_id
1430 AND tl.organization_id = msik.organization_id AND tl.LANGUAGE = asg_base.get_language(p_user_name)
1431 AND flv.lookup_type(+) = 'SHIP_METHOD' AND flv.lookup_code(+) = acc.SHIPPING_METHOD AND flv.language(+)=asg_base.get_language(p_user_name)
1432 ORDER BY acc.SHIPPING_COST,acc.organization_id,nvl(acc.distance,0);
1433
1434 l_str1 varchar2(4000):=P_ITEM_NAME;
1435 l_str2 varchar2(4000):=P_QTY;
1436 l_item_tab l_char_type;
1437 l_item_name_tab l_char_type;
1438
1439 l_qty_tab l_num_type;
1440 l1 number;
1441 l2 number; cnt number :=1;
1442 iCnt number:=0;
1443
1444
1445 l_msg_count NUMBER;
1446 l_msg_data VARCHAR2(4000);
1447
1448 L_XML_RESULT CLOB:=empty_clob();
1449 L_FAILED_RESULT CLOB:=empty_clob();
1450 L_PROFILE_VALUE NUMBER;
1451 L_GIVEN_ITEM_ID NUMBER;
1452 L_ORG_NAME VARCHAR2(240);
1453 L_ORG_ID NUMBER;
1454 L_RETURN_STATUS VARCHAR2(100);
1455 l_required_parts_tbl csp_part_search_pvt.required_parts_tbl;
1456 l_search_params_rec csp_part_search_pvt.search_params_rec;
1457 l_count NUMBER;
1458 l_resource_id NUMBER;
1459 l_err BOOLEAN :=false;
1460 l_xml_tname VARCHAR2(1000);
1461
1462 BEGIN
1463
1464 CSM_UTIL_PKG.LOG('Entering q_available_parts for Item : ' ||P_ITEM_NAME, 'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_PROCEDURE);
1465
1466 l_profile_value := TO_NUMBER(fnd_profile.value_specific('CS_INV_VALIDATION_ORG',asg_base.get_user_id(p_user_name),NULL,NULL));
1467
1468 x_return_status := FND_API.G_RET_STS_SUCCESS;
1469
1470 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
1471 x_error_message := g_pass_error;
1472 l_err:=true;
1473 ELSIF l_profile_value IS NULL THEN
1474 x_error_message := 'Please set Service: Inventory Validation Profile for this user.';
1475 l_err:=true;
1476 ELSIF P_LOC_ID IS NULL THEN
1477 x_error_message := 'Location specified cannot be found in server.';
1478 l_err:=true;
1479 END IF;
1480
1481 IF l_err THEN
1482 p_result:='E:'||x_error_message;
1483 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_PROCEDURE);
1484 RETURN;
1485 END IF;
1486
1487 x_error_message:='Success';
1488
1489
1490
1491 OPEN C_GET_ORG(L_PROFILE_VALUE);
1492 FETCH C_GET_ORG INTO L_ORG_ID,L_ORG_NAME;
1493 CLOSE C_GET_ORG;
1494
1495 if instr(l_str1,':,:')>0 then
1496 loop
1497 exit when instr(l_str1,':,:')=0 ;
1498 l1:= instr(l_str1,':,:');
1499 l2:= instr(l_str2,':,:');
1500 l_item_tab(cnt):=upper(substr(l_str1,1,l1-1));
1501 l_qty_tab(cnt):=to_number(substr(l_str2,1,l2-1));
1502 cnt:=cnt+1;
1503 l_str1:=substr(l_str1,l1+3);
1504 l_str2:=substr(l_str2,l2+3);
1505 end loop;
1506 end if;
1507 l_item_tab(cnt):=upper(l_str1);
1508 l_qty_tab(cnt):=to_number(l_str2);
1509
1510
1511
1512 /***************COMMON SEARCH PARAMS - Start*/
1513
1514 l_search_params_rec.search_method := NULL;
1515
1516 IF P_INV_MINE IS NOT NULL THEN
1517 l_search_params_rec.my_inventory := TRUE;
1518 ELSE
1519 l_search_params_rec.my_inventory := FALSE;
1520 END IF;
1521
1522 IF P_INV_TECH IS NOT NULL THEN
1523 l_search_params_rec.technicians := TRUE;
1524 ELSE
1525 l_search_params_rec.technicians := FALSE;
1526 END IF;
1527
1528 IF P_WH_TYPE IS NOT NULL THEN
1529 IF SUBSTR(P_WH_TYPE,1,1)='Y' THEN
1530 l_search_params_rec.manned_warehouses := TRUE;
1531 ELSE
1532 l_search_params_rec.manned_warehouses := FALSE;
1533 END IF;
1534
1535 IF SUBSTR(P_WH_TYPE,2,1)='Y' THEN
1536 l_search_params_rec.unmanned_warehouses := TRUE;
1537 ELSE
1538 l_search_params_rec.unmanned_warehouses := FALSE;
1539 END IF;
1540
1541 ELSE
1542 l_search_params_rec.manned_warehouses := FALSE;
1543 l_search_params_rec.unmanned_warehouses := FALSE;
1544 END IF;
1545
1546 IF P_NEED_BY IS NOT NULL THEN
1547 l_search_params_rec.need_by_date := TO_DATE(P_NEED_BY,'RRRR-MM-DD HH24:MI:SS');
1548 ELSE
1549 l_search_params_rec.need_by_date := NULL;
1550 END IF;
1551
1552 IF P_SUBSTITUTE IS NOT NULL AND P_SUBSTITUTE='N' THEN
1553 l_search_params_rec.include_alternates := FALSE;
1554 ELSE
1555 l_search_params_rec.include_alternates := TRUE;
1556 END IF;
1557
1558 IF P_QTY_TYPE IS NOT NULL THEN
1559 l_search_params_rec.quantity_type := P_QTY_TYPE;
1560 ELSE
1561 l_search_params_rec.quantity_type := 'AVAILABLE';
1562 END IF;
1563
1564 IF P_DIST > 0 THEN
1565 l_search_params_rec.distance := P_DIST;
1566 L_SEARCH_PARAMS_REC.DISTANCE_UOM := 'MILE';
1567 ELSE
1568 l_search_params_rec.distance := NULL;
1569 L_SEARCH_PARAMS_REC.DISTANCE_UOM := null;
1570 END IF;
1571
1572
1573 IF P_INC_CLOSED IS NOT NULL THEN
1574 l_search_params_rec.include_closed := TRUE;
1575 ELSE
1576 l_search_params_rec.include_closed := FALSE;
1577 END IF;
1578
1579 l_search_params_rec.resource_type := 'RS_EMPLOYEE';
1580 l_search_params_rec.resource_id := asg_base.get_resource_id(p_user_name);
1581 l_search_params_rec.source_organization_id := NULL; --L_PROFILE_VALUE
1582 l_search_params_rec.source_subinventory := NULL;
1583 l_search_params_rec.current_location := NULL;
1584
1585 IF NVL(P_LOC_SRC,'HR')='HZ' THEN
1586 l_search_params_rec.to_hz_location_id := P_LOC_ID;
1587 l_search_params_rec.to_location_id := null;
1588 ELSE
1589 l_search_params_rec.to_hz_location_id := null;
1590 l_search_params_rec.to_location_id := P_LOC_ID;
1591 END IF;
1592
1593
1594 CSM_UTIL_PKG.LOG('***************Search Params***************'
1595 ||g_nl_chr ||'Search My inv: '||CSM_UTIL_PKG.bool2yn(l_search_params_rec.my_inventory)
1596 ||g_nl_chr ||'Search Tech inv: '||CSM_UTIL_PKG.bool2yn(l_search_params_rec.technicians)
1597 ||g_nl_chr ||'Search Manned WH: '||CSM_UTIL_PKG.bool2yn(l_search_params_rec.manned_warehouses)
1598 ||g_nl_chr ||'Search Unmanned WH: '||CSM_UTIL_PKG.bool2yn(l_search_params_rec.unmanned_warehouses)
1599 ||g_nl_chr ||'Search Need by: '||l_search_params_rec.need_by_date
1600 ||g_nl_chr ||'Search Incl Substitutes: '||CSM_UTIL_PKG.bool2yn(l_search_params_rec.include_alternates)
1601 ||g_nl_chr ||'Search Qty type: '||l_search_params_rec.quantity_type
1602 ||g_nl_chr ||'Search Distance: '||l_search_params_rec.distance
1603 ||g_nl_chr ||'Search loc Src: '||NVL(P_LOC_SRC,'HR')
1604 ||g_nl_chr ||'Search location: '||nvl(l_search_params_rec.to_location_id,l_search_params_rec.to_hz_location_id)
1605 ||g_nl_chr ||'Search Incl Closed: '||CSM_UTIL_PKG.bool2yn(l_search_params_rec.include_closed)
1606 , 'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_PROCEDURE);
1607
1608 /***************SEARCH PARAMS - End*/
1609
1610
1611
1612 L_FAILED_RESULT:='';
1613
1614 FOR I in 1..cnt
1615 LOOP
1616 CSM_UTIL_PKG.LOG('Checking Item : ' ||l_item_tab(I)||' with qty:'||l_qty_tab(I), 'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_PROCEDURE);
1617 L_GIVEN_ITEM_ID:=NULL;
1618 OPEN C_GET_ITEM(l_item_tab(I), L_ORG_ID); --Get whether given item is valid or not
1619 FETCH C_GET_ITEM INTO L_GIVEN_ITEM_ID;
1620 CLOSE C_GET_ITEM;
1621
1622 IF L_GIVEN_ITEM_ID IS NULL THEN
1623 x_error_message := 'The required Item:' ||l_item_tab(I) || ' is not present in the Organization:' || L_ORG_NAME;
1624 l_xml_tname:=to_xml_tag_name(l_item_tab(I));
1625 L_FAILED_RESULT:=L_FAILED_RESULT||'<I_'||l_xml_tname||'><FAILURE>'||x_error_message||'</FAILURE></I_'||l_xml_tname||'>'; --only number in tags causes issue in client
1626 CSM_UTIL_PKG.LOG( x_error_message,'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_ERROR);
1627 ELSE
1628 iCnt:=iCnt+1;
1629 l_item_name_tab(iCnt):=l_item_tab(I);
1630
1631 --Set the item parameters
1632 l_required_parts_tbl(iCnt).inventory_item_id := L_GIVEN_ITEM_ID;
1633 l_required_parts_tbl(iCnt).revision := NULL;
1634
1635 IF l_qty_tab(I) IS NOT NULL AND l_qty_tab(I)>0 THEN
1636 l_required_parts_tbl(iCnt).quantity := l_qty_tab(I);
1637 ELSE
1638 l_required_parts_tbl(iCnt).quantity := NULL;
1639 END IF;
1640
1641 END IF; --item found ?
1642 END LOOP;
1643
1644 L_XML_RESULT := 'S:<RESULT>';
1645
1646 FOR grping IN 1..2
1647 LOOP
1648
1649 IF grping=1 THEN
1650 L_XML_RESULT:=L_XML_RESULT||'<ORD_SHIP_SET>'||L_FAILED_RESULT;
1651 l_search_params_rec.ship_set := TRUE;
1652 ELSE
1653 l_search_params_rec.ship_set := NULL;
1654 L_XML_RESULT:=L_XML_RESULT||'<UNORDERED>'||L_FAILED_RESULT;
1655 END IF;
1656
1657 IF iCnt > 0 THEN
1658
1659 csp_part_search_pvt.search(p_required_parts => l_required_parts_tbl,
1660 p_search_params => l_search_params_rec,
1661 x_return_status => L_RETURN_STATUS,
1662 x_msg_data => l_msg_data,
1663 x_msg_count => l_msg_count);
1664
1665 IF L_RETURN_STATUS<> FND_API.G_RET_STS_SUCCESS THEN
1666 x_error_message := 'Part Search Api Failed with error: ' || fnd_msg_pub.get(l_msg_count,'F');
1667 p_result:='E:'||translateString(x_error_message);
1668 CSM_UTIL_PKG.LOG( x_error_message,'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_ERROR);
1669 RETURN; --this is meant for error callback
1670 ELSE
1671 FOR I IN 1..l_required_parts_tbl.COUNT
1672 LOOP
1673 l_xml_tname:=to_xml_tag_name(l_item_tab(I));
1674 L_XML_RESULT :=L_XML_RESULT||'<I_'||l_xml_tname||'><SUCCESS>';
1675
1676 FOR r_get_ohq IN c_get_ohq(l_required_parts_tbl(I).inventory_item_id)
1677 LOOP
1678
1679 --get org name for display
1680 OPEN C_GET_ORG_CODE(r_get_ohq.ORGANIZATION_ID);
1681 FETCH C_GET_ORG_CODE INTO L_ORG_NAME;
1682 CLOSE C_GET_ORG_CODE;
1683
1684 L_XML_RESULT := L_XML_RESULT || '<ROW>'
1685 || '<ORG_ID>'||to_char(r_get_ohq.organization_id)||'</ORG_ID>'
1686 || '<ORG_CODE>'||to_char(L_ORG_NAME)||'</ORG_CODE>'
1687 || '<SUB_INV>'||to_char(r_get_ohq.subinventory_code)||'</SUB_INV>'
1688 || '<SP_ID>'||to_char(r_get_ohq.supplied_item_id)||'</SP_ID>'
1689 || '<SP_R>'||to_char(r_get_ohq.supplied_item_rev)||'</SP_R>'
1690 || '<SP_Q>'||to_char(r_get_ohq.supplied_quantity)||'</SP_Q>'
1691 || '<SP_T>'||to_char(r_get_ohq.SUPPLIED_ITEM_TYPE)||'</SP_T>'
1692 || '<SP_N>'||to_char(r_get_ohq.item_name)||'</SP_N>'
1693 || '<SP_DS>'||to_char(r_get_ohq.description)||'</SP_DS>'
1694 || '<SP_UOM>'||to_char(r_get_ohq.uom_code)||'</SP_UOM>'
1695 || '<SP_TRK>'||to_char(r_get_ohq.comms_nl_trackable_flag)||'</SP_TRK>'
1696 || '<SP_BILL>'||to_char(r_get_ohq.material_billable_flag)||'</SP_BILL>'
1697 || '<SP_RLC>'||to_char(r_get_ohq.RESTRICT_LOCATORS_CODE)||'</SP_RLC>'
1698 || '<SP_LCC>'||to_char(r_get_ohq.LOCATION_CONTROL_CODE)||'</SP_LCC>'
1699 || '<SP_RSC>'||to_char(r_get_ohq.restrict_subinventories_code)||'</SP_RSC>'
1700 || '<SP_RPD>'||to_char(r_get_ohq.RECOVERED_PART_DISP_CODE)||'</SP_RPD>'
1701 || '<SRC_T>'||to_char(r_get_ohq.source_type_code)||'</SRC_T>'
1702 || '<SHP_D>'||to_char(r_get_ohq.SHIPPING_DATE)||'</SHP_D>'
1703 || '<SHP_MCODE>'||to_char(r_get_ohq.SHIPPING_METHOD)||'</SHP_MCODE>'
1704 || '<SHP_MEAN>'||to_char(r_get_ohq.ship_method_meaning)||'</SHP_MEAN>'
1705 || '<SHP_C>'||to_char(r_get_ohq.SHIPPING_COST)||'</SHP_C>'
1706 || '<ARR_D>'||to_char(r_get_ohq.ARRIVAL_DATE)||'</ARR_D>'
1707 || '<DIST>'||to_char(r_get_ohq.DISTANCE)||'</DIST>'
1708 || '<DIST_U>'||to_char(fnd_profile.value('CSFW_DEFAULT_DISTANCE_UNIT'))||'</DIST_U>'
1709 || '<O_OR_C>'||to_char(r_get_ohq.OPEN_OR_CLOSED)||'</O_OR_C>'
1710 || '<SRL_C>'||to_char(r_get_ohq.serial_number_control_code)||'</SRL_C>'
1711 || ' </ROW> ';
1712
1713 END LOOP;
1714
1715 l_xml_result := l_xml_result || '</SUCCESS></I_'||l_xml_tname||'>';
1716 END LOOP;
1717 END IF; --api failed ?
1718 END IF; --if there is item to be processed by CSP
1719
1720 IF grping=1 THEN
1721 L_XML_RESULT:=L_XML_RESULT||'</ORD_SHIP_SET>';
1722 ELSE
1723 L_XML_RESULT:=L_XML_RESULT||'</UNORDERED>';
1724 END IF;
1725 COMMIT;
1726 END LOOP; --grping loop
1727
1728 p_result := l_xml_result|| '</RESULT>';
1729
1730 CSM_UTIL_PKG.LOG('Leaving q_available_parts for Item Name : ' || P_ITEM_NAME, 'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_PROCEDURE);
1731 COMMIT;
1732 EXCEPTION
1733 WHEN OTHERS THEN
1734 x_error_message := 'Exception occurred in q_available_parts: ' || substr(sqlerrm,0,1000);
1735 p_result:='E:'||translateString(x_error_message);
1736 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_available_parts', FND_LOG.LEVEL_EXCEPTION);
1737 COMMIT;
1738 END q_available_parts;
1739
1740
1741 --Query 20
1742 procedure q_return_item( p_user_name IN VARCHAR2,p_password IN VARCHAR2,P_ITEM_NAME IN VARCHAR2,P_ORG_ID IN NUMBER,
1743 p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
1744 Is
1745
1746 L_XML_RESULT CLOB:=empty_clob();
1747 l_count NUMBER:=0;
1748 l_item_name VARCHAR2(500);
1749 l_xml_tname VARCHAR2(1000);
1750
1751 BEGIN
1752
1753 l_item_name := replace(P_ITEM_NAME,':,:','%');
1754
1755 CSM_UTIL_PKG.LOG('Entering q_return_item for Item: ' ||P_ITEM_NAME||' and after decode:'||l_item_name , 'CSM_MULTI_MOBQRY_PKG.q_return_item', FND_LOG.LEVEL_PROCEDURE);
1756
1757 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
1758 x_return_status := fnd_api.G_RET_STS_SUCCESS;
1759 x_error_message := g_pass_error;
1760 p_result:='E:'||x_error_message;
1761 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_return_item', FND_LOG.LEVEL_PROCEDURE);
1762 RETURN;
1763 END IF;
1764
1765 x_return_status := FND_API.G_RET_STS_SUCCESS;
1766 x_error_message:='Success';
1767
1768 L_XML_RESULT := 'S:<RESULT><SUCCESS>';
1769 l_count:=0;
1770 FOR rec IN (SELECT msi.inventory_item_id , msi.organization_id , msi.segment1 AS inventory_name, msi_tl.description , msi.enabled_flag , msi.start_date_active ,
1771 msi.end_date_active , msi.primary_uom_code , msi.service_item_flag , msi.serviceable_product_flag , msi.material_billable_flag , msi.returnable_flag ,
1772 msi.mtl_transactions_enabled_flag , msi.serv_req_enabled_code, msi.serial_number_control_code , msi.lot_control_code , msi.inventory_asset_flag ,
1773 msi.purchasing_enabled_flag , msi.internal_order_enabled_flag , msi.internal_order_flag , msi.purchasing_item_flag , msi.restrict_subinventories_code,
1774 msi.comms_nl_trackable_flag , msi.stock_enabled_flag, msi.attribute1 , msi.attribute2 , msi.attribute3 , msi.attribute4 , msi.attribute5 , msi.attribute6 ,
1775 msi.attribute7 , msi.attribute8 , msi.attribute9 , msi.attribute10 , msi.attribute11 , msi.attribute12, msi.attribute13 , msi.attribute14 , msi.attribute15 ,
1776 msi.attribute_category, msi.contract_item_type_code, msi.recovered_part_disp_code, msi.revision_qty_control_code, msi.RESTRICT_LOCATORS_CODE,
1777 msi.LOCATION_CONTROL_CODE, msi.LIST_PRICE_PER_UNIT, UPPER(msi.segment1) AS inventory_name_upper
1778 FROM mtl_system_items_b msi, mtl_system_items_tl msi_tl
1779 WHERE msi_tl.inventory_item_id = msi.inventory_item_id AND msi_tl.organization_id = msi.organization_id AND msi_tl.language = asg_base.get_language(p_user_name)
1780 AND msi.segment1 like l_item_name AND msi.organization_id=P_ORG_ID and rownum<=11 order by msi.segment1)
1781 --at client limiting to 10 items found , showing 11 for them to know that more exists here
1782 LOOP
1783 IF l_count=0 THEN
1784 L_XML_RESULT:= L_XML_RESULT||'<COLUMNS><C1>inventory_item_id</C1><C2>organization_id</C2><C3>inventory_name</C3>';
1785 L_XML_RESULT:= L_XML_RESULT||'<C4>description</C4><C5>enabled_flag</C5><C6>start_date_active</C6>';
1786 L_XML_RESULT:= L_XML_RESULT||'<C7>primary_uom_code</C7><C8>service_item_flag</C8><C9>serviceable_product_flag</C9>';
1787 L_XML_RESULT:= L_XML_RESULT||'<C10>material_billable_flag</C10><C11>returnable_flag</C11><C12>mtl_transactions_enabled_flag</C12>';
1788 L_XML_RESULT:= L_XML_RESULT||'<C13>serv_req_enabled_code</C13><C14>serial_number_control_code</C14><C15>lot_control_code</C15>';
1789 L_XML_RESULT:= L_XML_RESULT||'<C16>inventory_asset_flag</C16><C17>purchasing_enabled_flag</C17><C18>internal_order_enabled_flag</C18>';
1790 L_XML_RESULT:= L_XML_RESULT||'<C19>internal_order_flag</C19><C20>purchasing_item_flag</C20><C21>restrict_subinventories_code</C21>';
1791 L_XML_RESULT:= L_XML_RESULT||'<C22>comms_nl_trackable_flag</C22><C23>stock_enabled_flag</C23><C24>contract_item_type_code</C24>';
1792 L_XML_RESULT:= L_XML_RESULT||'<C25>recovered_part_disp_code</C25><C26>revision_qty_control_code</C26><C27>RESTRICT_LOCATORS_CODE</C27>';
1793 L_XML_RESULT:= L_XML_RESULT||'<C28>LOCATION_CONTROL_CODE</C28><C29>LIST_PRICE_PER_UNIT</C29><C30>inventory_name_upper</C30></COLUMNS><ROWSET>';
1794 l_count:=1;
1795 END IF;
1796
1797 l_xml_tname:=to_xml_tag_name(rec.inventory_name);
1798 L_XML_RESULT:= L_XML_RESULT||'<I_'||l_xml_tname||'>';
1799
1800 /*START FILL COLUMNS */
1801 L_XML_RESULT:= L_XML_RESULT||'<C1>'||to_char(rec.inventory_item_id)||'</C1>';
1802 L_XML_RESULT:= L_XML_RESULT||'<C2>'||to_char(rec.organization_id)||'</C2>';
1803 L_XML_RESULT:= L_XML_RESULT||'<C3>'||to_char(rec.inventory_name)||'</C3>';
1804 L_XML_RESULT:= L_XML_RESULT||'<C4>'||to_char(rec.description)||'</C4>';
1805 L_XML_RESULT:= L_XML_RESULT||'<C5>'||to_char(rec.enabled_flag)||'</C5>';
1806 L_XML_RESULT:= L_XML_RESULT||'<C6>'||to_char(rec.start_date_active)||'</C6>';
1807 L_XML_RESULT:= L_XML_RESULT||'<C7>'||to_char(rec.primary_uom_code)||'</C7>';
1808 L_XML_RESULT:= L_XML_RESULT||'<C8>'||to_char(rec.service_item_flag)||'</C8>';
1809 L_XML_RESULT:= L_XML_RESULT||'<C9>'||to_char(rec.serviceable_product_flag)||'</C9>';
1810 L_XML_RESULT:= L_XML_RESULT||'<C10>'||to_char(rec.material_billable_flag)||'</C10>';
1811 L_XML_RESULT:= L_XML_RESULT||'<C11>'||to_char(rec.returnable_flag)||'</C11>';
1812 L_XML_RESULT:= L_XML_RESULT||'<C12>'||to_char(rec.mtl_transactions_enabled_flag)||'</C12>';
1813 L_XML_RESULT:= L_XML_RESULT||'<C13>'||to_char(rec.serv_req_enabled_code)||'</C13>';
1814 L_XML_RESULT:= L_XML_RESULT||'<C14>'||to_char(rec.serial_number_control_code)||'</C14>';
1815 L_XML_RESULT:= L_XML_RESULT||'<C15>'||to_char(rec.lot_control_code)||'</C15>';
1816 L_XML_RESULT:= L_XML_RESULT||'<C16>'||to_char(rec.inventory_asset_flag)||'</C16>';
1817 L_XML_RESULT:= L_XML_RESULT||'<C17>'||to_char(rec.purchasing_enabled_flag)||'</C17>';
1818 L_XML_RESULT:= L_XML_RESULT||'<C18>'||to_char(rec.internal_order_enabled_flag)||'</C18>';
1819 L_XML_RESULT:= L_XML_RESULT||'<C19>'||to_char(rec.internal_order_flag)||'</C19>';
1820 L_XML_RESULT:= L_XML_RESULT||'<C20>'||to_char(rec.purchasing_item_flag)||'</C20>';
1821 L_XML_RESULT:= L_XML_RESULT||'<C21>'||to_char(rec.restrict_subinventories_code)||'</C21>';
1822 L_XML_RESULT:= L_XML_RESULT||'<C22>'||to_char(rec.comms_nl_trackable_flag)||'</C22>';
1823 L_XML_RESULT:= L_XML_RESULT||'<C23>'||to_char(rec.stock_enabled_flag)||'</C23>';
1824 L_XML_RESULT:= L_XML_RESULT||'<C24>'||to_char(rec.contract_item_type_code)||'</C24>';
1825 L_XML_RESULT:= L_XML_RESULT||'<C25>'||to_char(rec.recovered_part_disp_code)||'</C25>';
1826 L_XML_RESULT:= L_XML_RESULT||'<C26>'||to_char(rec.revision_qty_control_code)||'</C26>';
1827 L_XML_RESULT:= L_XML_RESULT||'<C27>'||to_char(rec.RESTRICT_LOCATORS_CODE)||'</C27>';
1828 L_XML_RESULT:= L_XML_RESULT||'<C28>'||to_char(rec.LOCATION_CONTROL_CODE)||'</C28>';
1829 L_XML_RESULT:= L_XML_RESULT||'<C29>'||to_char(rec.LIST_PRICE_PER_UNIT)||'</C29>';
1830 L_XML_RESULT:= L_XML_RESULT||'<C30>'||to_char(rec.inventory_name_upper)||'</30>';
1831
1832 --skipping attribute columns for now
1833
1834 /*END FILL COLUMNS */
1835
1836 L_XML_RESULT:= L_XML_RESULT||'</I_'||l_xml_tname||'>';
1837 END LOOP;
1838
1839 IF l_count=0 THEN
1840 p_result := 'S:<RESULT><FAILURE>Sorry, No matching items found for "'||l_item_name||'".</FAILURE></RESULT>';
1841 ELSE
1842 p_result := L_XML_RESULT||'</ROWSET></SUCCESS></RESULT>';
1843 END IF;
1844
1845 CSM_UTIL_PKG.LOG('Leaving q_return_item for Item Name : ' || l_item_name, 'CSM_MULTI_MOBQRY_PKG.q_return_item', FND_LOG.LEVEL_PROCEDURE);
1846 EXCEPTION
1847 WHEN OTHERS THEN
1848 x_error_message := 'Exception occurred in q_return_item: ' || substr(sqlerrm,0,1000);
1849 p_result:='E:'||translateString(x_error_message);
1850 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_return_item', FND_LOG.LEVEL_EXCEPTION);
1851 END q_return_item;
1852
1853 --Query 18
1854 procedure q_upd_task_status( p_user_name IN VARCHAR2,p_password IN VARCHAR2,P_TA_ID IN VARCHAR2, P_TA_ST_ID IN VARCHAR2
1855 ,p_result OUT nocopy CLOB,x_return_status OUT nocopy VARCHAR2,x_error_message OUT nocopy VARCHAR2)
1856 IS
1857 l_task_ovn NUMBER; l_ta_ovn NUMBER;
1858 l_tsk_st_id NUMBER;
1859 l_msg_count NUMBER;
1860 l_msg_data VARCHAR2(2000);
1861 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1862 l_ta_id NUMBER:=to_number(P_TA_ID);
1863 l_ta_audit_id NUMBER;
1864
1865 CURSOR c_get_ovn IS
1866 SELECT object_version_number
1867 FROM JTF_TASK_ASSIGNMENTS
1868 WHERE TASK_ASSIGNMENT_ID=to_number(P_TA_ID);
1869
1870 CURSOR c_task_status IS
1871 SELECT name FROM JTF_TASK_STATUSES_TL WHERE TASK_STATUS_ID=to_number(P_TA_ST_ID)
1872 AND LANGUAGE=asg_base.get_language(p_user_name);
1873
1874 l_user_id NUMBER;
1875 l_resp_id NUMBER;
1876 l_app_id NUMBER;
1877
1878 l_rejected_flag VARCHAR2(1);
1879 l_on_hold_flag VARCHAR2(1);
1880 l_cancelled_flag VARCHAR2(1);
1881 l_closed_flag VARCHAR2(1);
1882 l_completed_flag VARCHAR2(1);
1883 l_debrief_header_id NUMBER;
1884
1885 l_old_taa jtf_task_assignments%rowtype;
1886 l_new_taa jtf_task_assignments%rowtype;
1887
1888 CURSOR c_audit
1889 IS select * from jtf_task_assignments where task_assignment_id=to_number(p_ta_id);
1890
1891 CURSOR c_chk_task_status ( p_task_assignment_id NUMBER ) IS
1892 SELECT dh.debrief_header_id, tst.rejected_flag,
1893 tst.on_hold_flag, tst.cancelled_flag,
1894 tst.closed_flag, tst.completed_flag
1895 FROM jtf_task_assignments tas, csf_debrief_headers dh,
1896 jtf_task_statuses_b tst
1897 WHERE tas.task_assignment_id = p_task_assignment_id
1898 AND tas.task_assignment_id = dh.task_assignment_id
1899 AND tas.assignment_status_id = tst.task_status_id;
1900
1901 BEGIN
1902 x_return_status := FND_API.G_RET_STS_SUCCESS;
1903 x_error_message:='Success';
1904
1905 IF NOT CSM_HTML5_PKG.authenticate(p_user_name,p_password) THEN
1906 x_error_message := g_pass_error;
1907 p_result:='E:'||x_error_message;
1908 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_task_status', FND_LOG.LEVEL_PROCEDURE);
1909 RETURN;
1910 END IF;
1911
1912 OPEN c_get_ovn;
1913 FETCH c_get_ovn INTO l_ta_ovn;
1914 CLOSE c_get_ovn;
1915
1916 SELECT user_id,responsibility_id, app_id
1917 INTO l_user_id,l_resp_id,l_app_id
1918 FROM asg_user
1919 WHERE user_name=p_user_name ;
1920
1921 OPEN c_audit;
1922 FETCH c_audit INTO l_old_taa;
1923 CLOSE c_audit;
1924
1925 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id);
1926
1927 csf_task_assignments_pub.update_assignment_status
1928 ( p_api_version => 1.0
1929 , p_init_msg_list => FND_API.G_TRUE
1930 , p_commit => FND_API.G_TRUE
1931 -- Bug 101406041 , p_validation_level => FND_API.G_VALID_LEVEL_NONE
1932 -- Validate task status transitions.
1933 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1934 , x_return_status => l_return_status
1935 , x_msg_count => l_msg_count
1936 , x_msg_data => l_msg_data
1937 , p_task_assignment_id => l_ta_id
1938 , p_assignment_status_id => to_number(P_TA_ST_ID)
1939 , p_object_version_number => l_ta_ovn
1940 , p_update_task => 'T'
1941 , x_task_object_version_number => l_task_ovn
1942 , x_task_status_id => l_tsk_st_id
1943 );
1944
1945 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1946 RAISE FND_API.G_EXC_ERROR;
1947 END IF;
1948
1949 /****************** POST DEBRIEF **********************/
1950
1951 -- Bug # 14262923 For a given debrief header check the task Assignment status.
1952 -- If it is one of the following -
1953 -- rejected, on_hold, cancelled, closed or completed then call the api
1954 -- csf_debrief_update_pkg.form_Call for processing charges
1955
1956 OPEN c_chk_task_status ( l_ta_id );
1957 FETCH c_chk_task_status INTO l_debrief_header_id, l_rejected_flag, l_on_hold_flag,
1958 l_cancelled_flag, l_closed_flag, l_completed_flag;
1959
1960 CSM_UTIL_PKG.LOG('l_debrief_header_id:' || l_debrief_header_id ||
1961 'l_rejected_flag: ' || l_rejected_flag || ' l_on_hold_flag: ' || l_on_hold_flag ||
1962 'l_cancelled_flag : ' || l_cancelled_flag || ' l_closed_flag: ' || l_closed_flag ||
1963 ' l_completed_flag: ' || l_completed_flag
1964 ,'CSM_MULTI_MOBQRY_PKG.q_upd_task_status' , FND_LOG.LEVEL_PROCEDURE);
1965
1966 IF c_chk_task_status%FOUND THEN
1967
1968 IF ( (l_rejected_flag='Y') OR (l_on_hold_flag='Y') OR (l_cancelled_flag='Y')
1969 OR (l_closed_flag='Y') OR (l_completed_flag='Y') ) THEN
1970
1971 fnd_global.apps_initialize(l_user_id, l_resp_id, l_app_id); --Bug 16192129
1972
1973 CSM_UTIL_PKG.LOG( 'Call debrief post program for the task assignment id: ' || l_ta_id, 'CSM_MULTI_MOBQRY_PKG.q_upd_task_status', FND_LOG.LEVEL_PROCEDURE);
1974
1975 csf_debrief_update_pkg.form_Call (1.0, l_debrief_header_id );
1976
1977 END IF;
1978 END IF;
1979
1980 CLOSE c_chk_task_status;
1981
1982
1983 p_result:='S:'||to_char(l_tsk_st_id);
1984 CSM_UTIL_PKG.LOG('Leaving after successful execution', 'CSM_MULTI_MOBQRY_PKG.q_upd_task_status',FND_LOG.LEVEL_PROCEDURE);
1985 EXCEPTION
1986 WHEN FND_API.G_EXC_ERROR THEN
1987 CSM_UTIL_PKG.LOG('Exception Occurred: MSG COUNT:'||l_msg_count||' <->exception msg:'||l_msg_data, 'CSM_MULTI_MOBQRY_PKG.q_upd_task_status',FND_LOG.LEVEL_PROCEDURE);
1988 x_error_message:=fnd_msg_pub.get(l_msg_count,'F');
1989 p_result:='E:'||translateString(x_error_message);
1990 WHEN OTHERS THEN
1991 x_error_message := 'Exception occurred in q_upd_task_status: ' || substr(sqlerrm,1,3900);
1992 p_result:='E:'||translateString(x_error_message);
1993 CSM_UTIL_PKG.LOG(x_error_message, 'CSM_MULTI_MOBQRY_PKG.q_upd_task_status', FND_LOG.LEVEL_PROCEDURE);
1994 END q_upd_task_status;
1995
1996 end CSM_MULTI_MOBQRY_PKG;