[Home] [Help]
PACKAGE BODY: APPS.RCV_OPSM_DLV_BE_PKG
Source
1 PACKAGE body RCV_OPSM_DLV_BE_PKG AS
2 /* $Header: RCVOPSMDLVB.pls 120.2 2010/09/27 20:38:34 skaradib noship $ */
3 ------------------------------------------------------------------------------------------------
4 --
5 -- Procedure: RCV_OPSM_DLV_EVENT
6 -- Parameters: p_shipmentheaderid - SHIPMENT_HEADER_ID
7 -- p_groupid - GROUP_ID
8 -- p_out_rmaheader_info - out variable for RCV_OPSM_DLVHDR_TBL type
9 -- p_out_rmaline_info - out variable for RCV_OPSM_DLVLINE_TBL type
10 -- p_out_rmalot_info - out variable for RCV_OPSM_DLVLOT_TBL type
11 -- p_out_rmaserial_info - out variable for RCV_OPSM_DLVSRL_TBL type
12 -- x_return_status - return status of the API
13 -- x_msg_data - return Error Message of the API
14 --
15 --
16 -- Description: This Procedure fetches all the OPSM Integrated items for the input parameter
17 -- shipment header id and Group id and load the details to the respective
18 -- table type objects and sends them as out variable.
19 --
20 -- Author: susubram
21 ------------------------------------------------------------------------------------------------
22
23 PROCEDURE RCV_OPSM_DLV_EVENT(p_shipmentheaderid in number,
24 p_groupid in number,
25 p_out_rmaheader_info out nocopy RCV_OPSM_DLVHDR_TBL,
26 p_out_rmaline_info out nocopy RCV_OPSM_DLVLINE_TBL,
27 p_out_rmalot_info out nocopy RCV_OPSM_DLVLOT_TBL,
28 p_out_rmaserial_info out nocopy RCV_OPSM_DLVSRL_TBL,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_data OUT NOCOPY VARCHAR2)
31 IS
32 l_headerinfo RCV_OPSM_DLVHDR_REC;
33 l_headerinfo_tab RCV_OPSM_DLVHDR_TBL;
34 l_lineinfo RCV_OPSM_DLVLINE_REC;
35 l_lineinfo_tab RCV_OPSM_DLVLINE_TBL;
36 l_lotinfo RCV_OPSM_DLVLOT_REC;
37 l_lotinfo_tab RCV_OPSM_DLVLOT_TBL;
38 l_serialinfo RCV_OPSM_DLVSRL_REC;
39 l_serialinfo_tab RCV_OPSM_DLVSRL_TBL;
40 i number:=1;
41 j number:=1;
42 k number:=1;
43 l number:=1;
44 m number:=1;
45
46 l_module_name CONSTANT VARCHAR2(1000):= 'rcv.plsql.'||G_PKG_NAME||'.'||'RCV_OPSM_DLV_EVENT';
47 l_msg_data VARCHAR2(2000):=NULL;
48 g_asn_debug VARCHAR2(1):= NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
49
50 TYPE delivery_count IS TABLE OF NUMBER INDEX BY VARCHAR2(255);
51 del_count delivery_count;
52
53
54 CURSOR cur_lines_info IS
55 SELECT ROLV.SHIPMENT_HEADER_ID,
56 ROLV.SHIPMENT_LINE_ID,
57 ROLV.ITEM_ID,
58 ROLV.ITEM,
59 ROLV.ORGANIZATION_CODE,
60 ROLV.ITEM_DESCRIPTION,
61 ROLV.OPSM_INTEGRATED_FLAG,
62 ROLV.SERIAL_TYPE,
63 ROLV.QUANTITY_SHIPPED,
64 ROLV.QUANTITY_RECEIVED,
65 ROLV.UNIT_OF_MEASURE,
66 ROLV.SHIPMENT_LINE_STATUS_CODE,
67 ROLV.BAR_CODE_LABEL,
68 ROLV.QC_GRADE,
69 ROLV.ASN_LINE_FLAG,
70 ROLV.DESTINATION_TYPE_CODE,
71 ROLV.LOT_CONTROL_CODE,
72 ROLV.SERIAL_NUMBER_CONTROL_CODE,
73 ROLV.OE_ORDER_HEADER_ID,
74 ROLV.ORDER_NUMBER,
75 ROLV.ORDER_TYPE,
76 ROLV.LINE_NUMBER,
77 ROLV.PARENT_TRANSACTION_ID,
78 ROLV.TRANSACTION_ID,
79 ROLV.TRANSACTION_TYPE,
80 ROLV.TRANSACTION_DATE,
81 ROLV.TRANSACTION_QTY,
82 ROLV.TRANSACTION_UOM,
83 ROLV.PRIMARY_QUANTITY,
84 ROLV.PRIMARY_UOM_CODE,
85 ROLV.SECONDARY_QUANTITY,
86 ROLV.SECONDARY_UOM_CODE,
87 ROLV.SOURCE_DOCUMENT_CODE,
88 ROLV.TRANS_DESTINATION_TYPE_CODE,
89 ROLV.SOLDBYORGID,
90 ROLV.SOLDBYCONTACTID,
91 ROLV.SHIPFROMORGID,
92 ROLV.SHIPFROMCONTACTID,
93 ROLV.BILLEDBYORGID,
94 ROLV.BILLEDBYCONTACTID,
95 ROLV.GROUP_ID
96 FROM RCV_OPSM_LINE_V ROLV,
97 MTL_CROSS_REFERENCES_VL MCRV,
98 MTL_PARAMETERS MP
99 WHERE ROLV.SHIPMENT_HEADER_ID =p_shipmentheaderid
100 AND ROLV.GROUP_ID =p_groupid
101 AND ROLV.SOURCE_DOCUMENT_CODE ='RMA RECEIPT'
102 AND ROLV.LOT_CONTROL_CODE = 2
103 AND MP.OPSM_ENABLED_FLAG = 'Y'
104 AND MP.ORGANIZATION_ID = ROLV.ORGANIZATION_ID
105 AND MP.MASTER_ORGANIZATION_ID = MCRV.ORGANIZATION_ID
106 AND ROLV.ITEM_ID = MCRV.INVENTORY_ITEM_ID
107 AND MCRV.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
108 AND MCRV.CROSS_REFERENCE ='YES'
109 AND NOT EXISTS ( SELECT 'Y' FROM mtl_cross_references_vl mcr
110 WHERE mcr.inventory_item_id = ROLV.ITEM_ID
111 AND mcr.organization_id = ROLV.ORGANIZATION_ID
112 AND mcr.cross_reference_type ='OPSM INTEGRATED'
113 AND upper(mcr.cross_reference)= 'NO');
114
115
116 cursor cur_headers_info(p_shipmentheaderid in number,p_groupid in number,p_soldbyorgid in number,p_soldbycontactid in number,p_shipfromcontactid in number,p_billedbyorgid in number,p_billedbycontactid in number) IS
117 SELECT distinct ROLV.SHIPMENT_HEADER_ID,
118 ROMAV.ORGANIZATION_ID,
119 MP.ORGANIZATION_CODE,
120 ROMAV.RECEIPT_NUM,
121 ROCAV.CHANGEOFOWNERSHIP,
122 ROMAV.BILL_OF_LADING,
123 ROMAV.CREATED_BY,
124 ROMAV.CREATION_DATE,
125 ROMAV.LAST_UPDATED_BY,
126 ROMAV.LAST_UPDATE_DATE,
127 ROCAV.SOLDBYORGID,
128 ROCAV.SOLDBYCONTACTID,
129 ROCAV.SOLDBYORGCODE,
130 ROCAV.SOLDBYORGNAME,
131 ROCAV.SOLDBYLINE1 ,
132 ROCAV.SOLDBYLINE2 ,
133 ROCAV.SOLDBYLINE3 ,
134 ROCAV.SOLDBYLINE4 ,
135 ROCAV.SOLDBYCITY ,
136 ROCAV.SOLDBYSTATE ,
137 ROCAV.SOLDBYCOUNTRY ,
138 ROCAV.SOLDBYPOSTALCODE ,
139 ROCAV.SOLDBYCONTACTNAME ,
140 ROCAV.SOLDBYPHONECOUNTRYCODE ,
141 ROCAV.SOLDBYPHONEAREACODE ,
142 ROCAV.SOLDBYPHONENUMBER ,
143 ROCAV.SOLDBYPHONEEXTENSION ,
144 ROCAV.SOLDBYCONTACTEMAIL ,
145 ROCAV.BILLEDBYORGID ,
146 ROCAV.BILLEDBYCONTACTID ,
147 ROCAV.BILLEDBYORGCODE ,
148 ROCAV.BILLEDBYORGNAME ,
149 ROCAV.BILLEDBYLINE1 ,
150 ROCAV.BILLEDBYLINE2 ,
151 ROCAV.BILLEDBYLINE3 ,
152 ROCAV.BILLEDBYLINE4 ,
153 ROCAV.BILLEDBYCITY ,
154 ROCAV.BILLEDBYSTATE ,
155 ROCAV.BILLEDBYCOUNTRY ,
156 ROCAV.BILLEDBYPOSTALCODE ,
157 ROCAV.BILLEDBYCONTACTNAME ,
158 ROCAV.BILLEDBYPHONECOUNTRYCODE ,
159 ROCAV.BILLEDBYPHONEAREACODE ,
160 ROCAV.BILLEDBYPHONENUMBER ,
161 ROCAV.BILLEDBYPHONEEXTENSION ,
162 ROCAV.BILLEDBYCONTACTEMAIL ,
163 ROCAV.SHIPFROMORGID ,
164 ROCAV.SHIPFROMCONTACTID ,
165 ROCAV.SHIPFROMORGCODE ,
166 ROCAV.SHIPFROMORGNAME ,
167 ROCAV.SHIPFROMLINE1 ,
168 ROCAV.SHIPFROMLINE2 ,
169 ROCAV.SHIPFROMLINE3 ,
170 ROCAV.SHIPFROMLINE4 ,
171 ROCAV.SHIPFROMCITY ,
172 ROCAV.SHIPFROMSTATE ,
173 ROCAV.SHIPFROMCOUNTRY ,
174 ROCAV.SHIPFROMPOSTALCODE ,
175 ROCAV.SHIPFROMCONTACTNAME ,
176 ROCAV.SHIPFROMPHONECOUNTRYCODE ,
177 ROCAV.SHIPFROMPHONEAREACODE ,
178 ROCAV.SHIPFROMPHONENUMBER ,
179 ROCAV.SHIPFROMPHONEEXTENSION ,
180 ROCAV.SHIPFROMCONTACTEMAIL ,
181 ROMAV.SHIPTOORGID ,
182 ROMAV.SHIPTOORGCODE ,
183 ROMAV.SHIPTOORGNAME ,
184 ROMAV.SHIPTOLINE1 ,
185 ROMAV.SHIPTOLINE2 ,
186 ROMAV.SHIPTOLINE3 ,
187 ROMAV.SHIPTOCITY ,
188 ROMAV.SHIPTOSTATE ,
189 ROMAV.SHIPTOCOUNTRY ,
190 ROMAV.SHIPTOPOSTALCODE ,
191 ROMAV.SHIPTOCONTACTNAME ,
192 ROMAV.SHIPTOCONTACTPHONE ,
193 ROMAV.SHIPTOCONTACTEMAIL ,
194 ROMAV.SOLDTOORGID ,
195 ROMAV.SOLDTOORGCODE ,
196 ROMAV.SOLDTOORGNAME ,
197 ROMAV.SOLDTOLINE1 ,
198 ROMAV.SOLDTOLINE2 ,
199 ROMAV.SOLDTOLINE3 ,
200 ROMAV.SOLDTOCITY ,
201 ROMAV.SOLDTOSTATE ,
202 ROMAV.SOLDTOCOUNTRY ,
203 ROMAV.SOLDTOPOSTALCODE ,
204 ROMAV.SOLDTOCONTACTNAME ,
205 ROMAV.SOLDTOCONTACTPHONE ,
206 ROMAV.SOLDTOCONTACTEMAIL
207 FROM RCV_OPSM_LINE_V ROLV,
208 RCV_OPSM_MANUFACT_ADDRESS_V ROMAV,
209 RCV_OPSM_CUSTOMERADDRESS_V ROCAV,
210 MTL_PARAMETERS MP
211 WHERE ROLV.SHIPMENT_HEADER_ID=p_shipmentheaderid
212 AND ROLV.GROUP_ID=p_groupid
213 AND ROLV.SHIPMENT_HEADER_ID=ROMAV.SHIPMENT_HEADER_ID
214 AND ROLV.SHIPMENT_LINE_ID=ROMAV.SHIPMENT_LINE_ID
215 AND ROMAV.SHIPMENT_HEADER_ID=ROCAV.SHIPMENT_HEADER_ID
216 AND ROMAV.SHIPMENT_LINE_ID=ROCAV.SHIPMENT_LINE_ID
217 AND ROMAV.SHIP_TO_ORG_ID=MP.ORGANIZATION_ID
218 AND NVL(ROLV.SOLDBYORGID,-999)=NVL(p_soldbyorgid,-999)
219 AND NVL(ROLV.SOLDBYCONTACTID,-999)=NVL(p_soldbycontactid,-999)
220 AND NVL(ROLV.SHIPFROMCONTACTID,-999)=NVL(p_shipfromcontactid,-999)
221 AND NVL(ROLV.BILLEDBYORGID,-999)=NVL(p_billedbyorgid,-999)
222 AND NVL(ROLV.BILLEDBYCONTACTID,-999)=NVL(p_billedbycontactid,-999);
223
224
225 CURSOR cur_lots_info(p_header_id in number,p_line_id in NUMBER) IS
226 SELECT DISTINCT ROLSV.SHIPMENT_LINE_ID,
227 ROLSV.SHIPMENT_HEADER_ID,
228 ROLSV.TRANSACTION_ID,
229 ROLSV.PARENT_LOT_NUMBER,
230 ROLSV.LOT_NUM,
231 ROLSV.ORGANIZATION_CODE,
232 ROLSV.LOTQTY,
233 ROLSV.LOTQTYUOM,
234 ROLSV.PRIMARY_QUANTITY,
235 ROLSV.PRIMARY_UOM_CODE,
236 ROLSV.SECONDARY_QUANTITY,
237 ROLSV.SECONDARY_UOM_CODE,
238 ROLSV.QC_GRADE,
239 ROLSV.REASON_CODE,
240 ROLSV.ORIGINATION_DATE,
241 ROLSV.BEST_BY_DATE,
242 ROLSV.RETEST_DATE,
243 ROLSV.EXPIRATION_DATE
244 FROM RCV_OPSM_LOT_SERIAL_V ROLSV
245 WHERE ROLSV.SHIPMENT_HEADER_ID=p_header_id
246 AND ROLSV.SHIPMENT_LINE_ID=p_line_id;
247
248 CURSOR cur_serial_info(p_shipment_line_id in number,p_transaction_id in NUMBER,p_lotnum in varchar2) IS
249 SELECT ROLSV.SHIPMENT_LINE_ID,
250 ROLSV.TRANSACTION_ID,
251 ROLSV.LOT_NUM,
252 ROLSV.SERIAL_NUM
253 FROM RCV_OPSM_LOT_SERIAL_V ROLSV
254 WHERE ROLSV.SHIPMENT_LINE_ID=p_shipment_line_id
255 AND ROLSV.TRANSACTION_ID= p_transaction_id
256 AND NVL(ROLSV.LOT_NUM,-999)=NVL(p_lotnum,-999);
257
258
259 BEGIN
260
261 IF g_asn_debug is null THEN
262 g_asn_debug:='Y';
263 END IF;
264
265 x_return_status := 'U';
266
267 FOR v_cur_lines_info IN cur_lines_info
268 LOOP
269
270
271 IF NOT del_count.EXISTS(TO_CHAR(v_cur_lines_info.SHIPFROMCONTACTID)
272 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYORGID)
273 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYCONTACTID)
274 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYORGID)
275 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYCONTACTID)) THEN
276
277 del_count(TO_CHAR(v_cur_lines_info.SHIPFROMCONTACTID)
278 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYORGID)
279 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYCONTACTID)
280 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYORGID)
281 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYCONTACTID)) := 1;
282
283
284
285
286 END IF;
287 END LOOP;
288
289
290 FOR v_cur_lines_info IN cur_lines_info
291 LOOP
292
293
294
295 l_lineinfo:=RCV_OPSM_DLVLINE_REC( v_cur_lines_info.SHIPMENT_HEADER_ID,
296 v_cur_lines_info.SHIPMENT_LINE_ID,
297 v_cur_lines_info.ITEM_ID,
298 v_cur_lines_info.ITEM,
299 v_cur_lines_info.ORGANIZATION_CODE,
300 v_cur_lines_info.ITEM_DESCRIPTION,
301 v_cur_lines_info.OPSM_INTEGRATED_FLAG,
302 v_cur_lines_info.SERIAL_TYPE,
303 v_cur_lines_info.QUANTITY_SHIPPED,
304 v_cur_lines_info.QUANTITY_RECEIVED,
305 v_cur_lines_info.UNIT_OF_MEASURE,
306 v_cur_lines_info.SHIPMENT_LINE_STATUS_CODE,
307 v_cur_lines_info.BAR_CODE_LABEL,
308 v_cur_lines_info.QC_GRADE,
309 v_cur_lines_info.ASN_LINE_FLAG,
310 v_cur_lines_info.DESTINATION_TYPE_CODE,
311 v_cur_lines_info.LOT_CONTROL_CODE,
312 v_cur_lines_info.SERIAL_NUMBER_CONTROL_CODE,
313 v_cur_lines_info.OE_ORDER_HEADER_ID,
314 v_cur_lines_info.ORDER_NUMBER,
315 v_cur_lines_info.ORDER_TYPE,
316 v_cur_lines_info.LINE_NUMBER,
317 v_cur_lines_info.SOLDBYORGID,
318 v_cur_lines_info.SOLDBYCONTACTID,
319 v_cur_lines_info.BILLEDBYORGID,
320 v_cur_lines_info.BILLEDBYCONTACTID,
321 v_cur_lines_info.SHIPFROMORGID,
322 v_cur_lines_info.SHIPFROMCONTACTID,
323 v_cur_lines_info.PARENT_TRANSACTION_ID,
324 v_cur_lines_info.TRANSACTION_ID,
325 v_cur_lines_info.TRANSACTION_TYPE,
326 v_cur_lines_info.TRANSACTION_DATE,
327 v_cur_lines_info.TRANSACTION_QTY,
328 v_cur_lines_info.TRANSACTION_UOM,
329 v_cur_lines_info.PRIMARY_QUANTITY,
330 v_cur_lines_info.PRIMARY_UOM_CODE,
331 v_cur_lines_info.SECONDARY_QUANTITY,
332 v_cur_lines_info.SECONDARY_UOM_CODE,
333 v_cur_lines_info.SOURCE_DOCUMENT_CODE,
334 v_cur_lines_info.TRANS_DESTINATION_TYPE_CODE);
335
336 IF j=1 THEN
337 l_lineinfo_tab :=RCV_OPSM_DLVLINE_TBL(l_lineinfo);
338 END IF;
339
340 IF j>1 THEN
341 l_lineinfo_tab.EXTEND;
342 l_lineinfo_tab(j):=l_lineinfo;
343
344 END IF;
345 j:=j+1;
346
347
348 FOR v_cur_lots_info IN cur_lots_info(v_cur_lines_info.SHIPMENT_HEADER_ID,v_cur_lines_info.SHIPMENT_LINE_ID)
349 LOOP
350
351
352 l_lotinfo:=RCV_OPSM_DLVLOT_REC(v_cur_lots_info.SHIPMENT_HEADER_ID,
353 v_cur_lots_info.SHIPMENT_LINE_ID,
354 v_cur_lots_info.TRANSACTION_ID,
355 v_cur_lots_info.PARENT_LOT_NUMBER,
356 v_cur_lots_info.LOT_NUM,
357 v_cur_lots_info.ORGANIZATION_CODE,
358 v_cur_lots_info.LOTQTY,
359 v_cur_lots_info.LOTQTYUOM,
360 v_cur_lots_info.PRIMARY_QUANTITY,
361 v_cur_lots_info.PRIMARY_UOM_CODE,
362 v_cur_lots_info.SECONDARY_QUANTITY,
363 v_cur_lots_info.SECONDARY_UOM_CODE,
364 v_cur_lots_info.QC_GRADE,
365 v_cur_lots_info.REASON_CODE,
366 v_cur_lots_info.ORIGINATION_DATE,
367 v_cur_lots_info.BEST_BY_DATE,
368 v_cur_lots_info.EXPIRATION_DATE,
369 v_cur_lots_info.RETEST_DATE);
370
371 IF k=1 THEN
372 l_lotinfo_tab :=RCV_OPSM_DLVLOT_TBL(l_lotinfo);
373 END IF;
374 IF k>1 THEN
375 l_lotinfo_tab.EXTEND;
376 l_lotinfo_tab(k):=l_lotinfo;
377
378 END IF;
379 k:=k+1;
380
381 FOR v_cur_serial_info IN cur_serial_info(v_cur_lots_info.SHIPMENT_LINE_ID,v_cur_lots_info.TRANSACTION_ID,v_cur_lots_info.LOT_NUM)
382 LOOP
383
384
385
386 l_serialinfo:=RCV_OPSM_DLVSRL_REC(v_cur_serial_info.SHIPMENT_LINE_ID,
387 v_cur_serial_info.TRANSACTION_ID,
388 v_cur_serial_info.LOT_NUM,
389 v_cur_serial_info.SERIAL_NUM);
390
391 IF l=1 THEN
392 l_serialinfo_tab :=RCV_OPSM_DLVSRL_TBL(l_serialinfo);
393 END IF;
394 IF l>1 THEN
395 l_serialinfo_tab.EXTEND;
396 l_serialinfo_tab(l):=l_serialinfo;
397
398 END IF;
399 l:=l+1;
400 END LOOP; --serial
401
402 END LOOP;--lot
403
404
405 IF del_count.exists(TO_CHAR(v_cur_lines_info.SHIPFROMCONTACTID)
406 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYORGID)
407 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYCONTACTID)
408 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYORGID)
409 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYCONTACTID)) THEN
410
411
412
413 del_count.DELETE(TO_CHAR(v_cur_lines_info.SHIPFROMCONTACTID)
414 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYORGID)
415 ||'.'||TO_CHAR(v_cur_lines_info.BILLEDBYCONTACTID)
416 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYORGID)
417 ||'.'||TO_CHAR(v_cur_lines_info.SOLDBYCONTACTID));
418
419
420
421 FOR v_cur_headers_info IN cur_headers_info(v_cur_lines_info.SHIPMENT_HEADER_ID,v_cur_lines_info.GROUP_ID,v_cur_lines_info.SOLDBYORGID,v_cur_lines_info.SOLDBYCONTACTID,v_cur_lines_info.SHIPFROMCONTACTID,v_cur_lines_info.BILLEDBYORGID,
422 v_cur_lines_info.BILLEDBYCONTACTID)
423 LOOP
424
425
426
427
428 l_headerinfo:=RCV_OPSM_DLVHDR_REC(v_cur_headers_info.SHIPMENT_HEADER_ID,
429 v_cur_headers_info.ORGANIZATION_ID,
430 v_cur_headers_info.ORGANIZATION_CODE,
431 v_cur_headers_info.RECEIPT_NUM,
432 v_cur_headers_info.CHANGEOFOWNERSHIP,
433 v_cur_headers_info.BILL_OF_LADING ,
434 v_cur_headers_info.CREATED_BY ,
435 v_cur_headers_info.CREATION_DATE ,
436 v_cur_headers_info.LAST_UPDATED_BY ,
437 v_cur_headers_info.LAST_UPDATE_DATE ,
438 v_cur_headers_info.SOLDBYORGID ,
439 v_cur_headers_info.SOLDBYCONTACTID ,
440 v_cur_headers_info.SOLDBYORGCODE ,
441 v_cur_headers_info.SOLDBYORGNAME ,
442 v_cur_headers_info.SOLDBYLINE1 ,
443 v_cur_headers_info.SOLDBYLINE2 ,
444 v_cur_headers_info.SOLDBYLINE3 ,
445 v_cur_headers_info.SOLDBYLINE4 ,
446 v_cur_headers_info.SOLDBYCITY ,
447 v_cur_headers_info.SOLDBYSTATE ,
448 v_cur_headers_info.SOLDBYCOUNTRY ,
449 v_cur_headers_info.SOLDBYPOSTALCODE ,
450 v_cur_headers_info.SOLDBYCONTACTNAME ,
451 v_cur_headers_info.SOLDBYPHONECOUNTRYCODE ,
452 v_cur_headers_info.SOLDBYPHONEAREACODE ,
453 v_cur_headers_info.SOLDBYPHONENUMBER ,
454 v_cur_headers_info.SOLDBYPHONEEXTENSION ,
455 v_cur_headers_info.SOLDBYCONTACTEMAIL ,
456 v_cur_headers_info.BILLEDBYORGID ,
457 v_cur_headers_info.BILLEDBYCONTACTID ,
458 v_cur_headers_info.BILLEDBYORGCODE ,
459 v_cur_headers_info.BILLEDBYORGNAME ,
460 v_cur_headers_info.BILLEDBYLINE1 ,
461 v_cur_headers_info.BILLEDBYLINE2 ,
462 v_cur_headers_info.BILLEDBYLINE3 ,
463 v_cur_headers_info.BILLEDBYLINE4 ,
464 v_cur_headers_info.BILLEDBYCITY ,
465 v_cur_headers_info.BILLEDBYSTATE ,
466 v_cur_headers_info.BILLEDBYCOUNTRY ,
467 v_cur_headers_info.BILLEDBYPOSTALCODE ,
468 v_cur_headers_info.BILLEDBYCONTACTNAME ,
469 v_cur_headers_info.BILLEDBYPHONECOUNTRYCODE ,
470 v_cur_headers_info.BILLEDBYPHONEAREACODE ,
471 v_cur_headers_info.BILLEDBYPHONENUMBER ,
472 v_cur_headers_info.BILLEDBYPHONEEXTENSION ,
473 v_cur_headers_info.BILLEDBYCONTACTEMAIL ,
474 v_cur_headers_info.SHIPFROMORGID ,
475 v_cur_headers_info.SHIPFROMCONTACTID ,
476 v_cur_headers_info.SHIPFROMORGCODE ,
477 v_cur_headers_info.SHIPFROMORGNAME ,
478 v_cur_headers_info.SHIPFROMLINE1 ,
479 v_cur_headers_info.SHIPFROMLINE2 ,
480 v_cur_headers_info.SHIPFROMLINE3 ,
481 v_cur_headers_info.SHIPFROMLINE4 ,
482 v_cur_headers_info.SHIPFROMCITY ,
483 v_cur_headers_info.SHIPFROMSTATE ,
484 v_cur_headers_info.SHIPFROMCOUNTRY ,
485 v_cur_headers_info.SHIPFROMPOSTALCODE ,
486 v_cur_headers_info.SHIPFROMCONTACTNAME ,
487 v_cur_headers_info.SHIPFROMPHONECOUNTRYCODE ,
488 v_cur_headers_info.SHIPFROMPHONEAREACODE ,
489 v_cur_headers_info.SHIPFROMPHONENUMBER ,
490 v_cur_headers_info.SHIPFROMPHONEEXTENSION ,
491 v_cur_headers_info.SHIPFROMCONTACTEMAIL ,
492 v_cur_headers_info.SHIPTOORGID ,
493 v_cur_headers_info.SHIPTOORGCODE ,
494 v_cur_headers_info.SHIPTOORGNAME ,
495 v_cur_headers_info.SHIPTOLINE1 ,
496 v_cur_headers_info.SHIPTOLINE2 ,
497 v_cur_headers_info.SHIPTOLINE3 ,
498 v_cur_headers_info.SHIPTOCITY ,
499 v_cur_headers_info.SHIPTOSTATE ,
500 v_cur_headers_info.SHIPTOCOUNTRY ,
501 v_cur_headers_info.SHIPTOPOSTALCODE ,
502 v_cur_headers_info.SHIPTOCONTACTNAME ,
503 v_cur_headers_info.SHIPTOCONTACTPHONE ,
504 v_cur_headers_info.SHIPTOCONTACTEMAIL ,
505 v_cur_headers_info.SOLDTOORGID ,
506 v_cur_headers_info.SOLDTOORGCODE ,
507 v_cur_headers_info.SOLDTOORGNAME ,
508 v_cur_headers_info.SOLDTOLINE1 ,
509 v_cur_headers_info.SOLDTOLINE2 ,
510 v_cur_headers_info.SOLDTOLINE3 ,
511 v_cur_headers_info.SOLDTOCITY ,
512 v_cur_headers_info.SOLDTOSTATE ,
513 v_cur_headers_info.SOLDTOCOUNTRY ,
514 v_cur_headers_info.SOLDTOPOSTALCODE ,
515 v_cur_headers_info.SOLDTOCONTACTNAME ,
516 v_cur_headers_info.SOLDTOCONTACTPHONE ,
517 v_cur_headers_info.SOLDTOCONTACTEMAIL);
518
519
520 IF i=1 THEN
521 l_headerinfo_tab := RCV_OPSM_DLVHDR_TBL(l_headerinfo);
522 END IF;
523 IF i>1 THEN
524 l_headerinfo_tab.EXTEND;
525 l_headerinfo_tab(i):=l_headerinfo;
526 END IF;
527 i:=i+1;
528
529
530 END loop; --HEADER CURSOR
531 END IF;
532
533 END LOOP; --LINE CURSOR
534
535
536
537
538 x_return_status := rcv_error_pkg.G_RET_STS_SUCCESS;
539 l_msg_data:='Data Fetched Into Table Type';
540 x_msg_data:=l_msg_data;
541
542 p_out_rmaheader_info :=l_headerinfo_tab;
543 p_out_rmaline_info :=l_lineinfo_tab;
544 p_out_rmalot_info :=l_lotinfo_tab;
545 p_out_rmaserial_info :=l_serialinfo_tab;
546
547
548 EXCEPTION
549 WHEN NO_DATA_FOUND THEN
550 asn_debug.put_line(l_module_name||'No Data Found.Oracle error message is '|| substr(SQLERRM,1,200),fnd_log.level_error);
551 x_return_status :=rcv_error_pkg.g_ret_sts_error;
552 l_msg_data:='NO DATA FOUND -ERROR';
553 x_msg_data:=l_msg_data;
554
555 WHEN OTHERS THEN
556 IF g_asn_debug <>'Y' THEN
557 asn_debug.put_line(l_module_name||'Unexpected error has occured. Oracle error message is '||substr(SQLERRM,1,200)||fnd_log.level_error);
558 END IF;
559 x_return_status := rcv_error_pkg.G_RET_STS_UNEXP_ERROR;
560 l_msg_data := SQLERRM ||' at RCV_OPSM_DLV_BE_PKG.RCV_OPSM_DLV_EVENT';
561 x_msg_data := l_msg_data;
562
563 END RCV_OPSM_DLV_EVENT;
564
565 ---------------------------------------------------------------------------------------
566 -- Procedure: RCV_OPSM_CHECK
567 -- Parameters: p_shipment_header_id - SHIPMENT HEADER ID
568 -- p_groupid -GROUP ID
569 -- p_opsmexist- It returns 'Y' or 'N'
570 -- x_return_status-return status of the API
571 -- x_msg_data -return Error Message of the API
572 --
573 -- Description: This Function Returns 'Y' or 'N'after checking
574 -- the condition that the Shipment Header id has at least one OPSM
575 -- enabled item or not.
576 ---------------------------------------------------------------------------------------
577
578 PROCEDURE RCV_OPSM_CHECK(p_shipmentheaderid IN NUMBER,
579 p_groupid IN NUMBER,
580 p_opsmexist OUT NOCOPY VARCHAR2,
581 x_return_status OUT NOCOPY VARCHAR2,
582 x_msg_data OUT NOCOPY VARCHAR2)
583 IS
584
585 l_count NUMBER:=0;
586 l_count1 NUMBER:=0;
587 l_module_name CONSTANT VARCHAR2(100):= 'rcv.plsql.' ||G_PKG_NAME || '.' || 'RCV_OPSM_CHECK';
588 l_msg_data VARCHAR2(2000):=NULL;
589 g_asn_debug VARCHAR2(1):= NVL(fnd_profile.VALUE('RCV_DEBUG_MODE'), 'N');
590
591 CURSOR c_opsm_items
592 IS
593 SELECT ROLV.item_id,ROLV.organization_id
594 FROM RCV_OPSM_LINE_V ROLV
595 WHERE rolv.shipment_header_id=p_shipmentheaderid
596 AND rolv.GROUP_ID=p_groupid;
597
598 BEGIN
599 IF g_asn_debug is null THEN
600 g_asn_debug:='Y';
601 END IF;
602
603 x_return_status := 'U';
604
605
606 FOR v_opsm_items IN c_opsm_items LOOP
607 --To check whether Item is OPSM Integrated or not
608 BEGIN
609 SELECT 1
610 INTO l_count
611 FROM MTL_PARAMETERS MP,
612 MTL_CROSS_REFERENCES_VL MCRV,
613 MTL_SYSTEM_ITEMS_B MSIB
614 WHERE MSIB.ORGANIZATION_ID = v_opsm_items.ORGANIZATION_ID
615 AND MSIB.INVENTORY_ITEM_ID = v_opsm_items.ITEM_ID
616 AND MSIB.ORGANIZATION_ID = MP.ORGANIZATION_ID
617 AND MP.OPSM_ENABLED_FLAG = 'Y'
618 AND MSIB.LOT_CONTROL_CODE = 2 --(hard coded 2 refers lot control)
619 AND MP.MASTER_ORGANIZATION_ID =MCRV.ORGANIZATION_ID
620 AND MCRV.INVENTORY_ITEM_ID = MSIB.INVENTORY_ITEM_ID
621 AND MCRV.CROSS_REFERENCE_TYPE = 'OPSM INTEGRATED'
622 AND MCRV.CROSS_REFERENCE = 'YES'
623 AND NOT EXISTS ( SELECT 'Y' FROM mtl_cross_references_vl mcr
624 WHERE mcr.inventory_item_id = v_opsm_items.ITEM_ID
625 AND mcr.organization_id = v_opsm_items.ORGANIZATION_ID
626 AND mcr.cross_reference_type ='OPSM INTEGRATED'
627 AND upper(mcr.cross_reference)= 'NO');
628
629
630
631 l_count1:=l_count1+l_count;
632
633 IF g_asn_debug='Y' THEN
634 asn_debug.put_line(l_module_name||'OPSM INTEGRATED SINGLE'||l_count);
635 asn_debug.put_line(l_module_name||'OPSM INTEGRATED COUNT'||l_count1);
636 END IF;
637
638 IF l_count1 > 0 THEN
639 EXIT;
640 END IF;
641
642
643
644 EXCEPTION
645 WHEN NO_DATA_FOUND THEN
646 l_count:=0;
647 p_opsmexist:='N';
648 x_return_status:=rcv_error_pkg.g_ret_sts_error;
649 l_msg_data:='NO DATA FOUND';
650 x_msg_data:=l_msg_data;
651 WHEN OTHERS THEN
652 l_count:=0;
653 p_opsmexist:='N';
654 x_return_status :=rcv_error_pkg.g_ret_sts_unexp_error;
655 l_msg_data:='UNEXPECTED ERROR';
656 x_msg_data:=l_msg_data;
657 END;
658
659 END LOOP;
660
661 IF l_count1 > 0 THEN
662 p_opsmexist:='Y';
663 IF g_asn_debug='Y' THEN
664 asn_debug.put_line(l_module_name||'OPSM INTEGRATED EXIST'||p_opsmexist);
665 l_msg_data:='OPSM ITEM EXIST';
666 x_msg_data:=l_msg_data;
667 END IF;
668
669 ELSE
670 p_opsmexist:='N';
671 IF g_asn_debug='Y' THEN
672 asn_debug.put_line(l_module_name||'OPSM INTEGRATED NOT EXIST'||p_opsmexist);
673 l_msg_data:='OPSM ITEM NOT EXIST';
674 x_msg_data:=l_msg_data;
675
676
677 END IF;
678 END IF;
679
680
681 x_return_status := rcv_error_pkg.G_RET_STS_SUCCESS;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 l_count:=0;
686 p_opsmexist:='N';
687
688 IF g_asn_debug<>'Y' THEN
689 asn_debug.put_line(l_module_name||'OPSM INTEGRATED EXIST'||p_opsmexist);
690 asn_debug.put_line(l_module_name||'Unexpected error has occured. Oracle error message is '||substr(SQLERRM,1,200)||fnd_log.level_error);
691 END IF;
692
693 x_return_status := rcv_error_pkg.G_RET_STS_UNEXP_ERROR;
694
695 l_msg_data := SQLERRM ||' at RCV_OPSM_DLV_BE_PKG.RCV_OPSM_CHECK';
696 x_msg_data := l_msg_data;
697 END RCV_OPSM_CHECK;
698 END RCV_OPSM_DLV_BE_PKG;