[Home] [Help]
19: -- on top of transfer_serial_numbers_pvt
20:
21: --========================================================================
22: -- PROCEDURE : transfer_serial_numbers_pvt
23: -- Transfers Serial Numbers from mtl_serial_numbers_temp to
24: -- wsh_serial_numbers and this will be used to display the
25: -- Serial Numbers shipped from the Shipping Transactions Form
26: --
27: -- PARAMETERS: p_transfer_param Input Parameter for this API
33: -- value 'INV' is passed.
34: -- x_return_status return status of the API.
35:
36: -- COMMENT : This procedure is used to transfer the serial number information
37: -- from mtl_serial_numbers_temp to the wsh_serial_numbers table.
38: -- It then deletes these entries from mtl_serial_numbers_temp.
39: -- IF this procedure is called during the INV interface then only
40: -- the lines that have been interfaced to OM are processed, else
41: -- the non-oe lines are processed as well.
34: -- x_return_status return status of the API.
35:
36: -- COMMENT : This procedure is used to transfer the serial number information
37: -- from mtl_serial_numbers_temp to the wsh_serial_numbers table.
38: -- It then deletes these entries from mtl_serial_numbers_temp.
39: -- IF this procedure is called during the INV interface then only
40: -- the lines that have been interfaced to OM are processed, else
41: -- the non-oe lines are processed as well.
42: --========================================================================
72: wsh_delivery_assignments_v da,
73: wsh_delivery_legs dg,
74: wsh_new_deliveries dl,
75: wsh_trip_stops st,
76: mtl_serial_numbers_temp msnt
77: where st.batch_id = p_batch_id
78: and st.stop_location_id = dl.initial_pickup_location_id
79: and dg.delivery_id = dl.delivery_id
80: and da.delivery_id = dl.delivery_id
112: wsh_delivery_assignments_v da,
113: wsh_delivery_legs dg,
114: wsh_new_deliveries dl,
115: wsh_trip_stops st,
116: mtl_serial_numbers_temp msnt
117: where st.batch_id = p_batch_id
118: and st.stop_location_id = dl.initial_pickup_location_id
119: and dg.delivery_id = dl.delivery_id
120: and da.delivery_id = dl.delivery_id
421: -- Bug 6625172: Removing Delete of msnt for each transaction_temp_id from here
422: -- and updating wsh_delivery_details only when p_transfer_param is 'WDD'
423: IF p_transfer_param = 'WDD' THEN
424: /* FORALL i IN 1 .. cur_fetch
425: DELETE FROM mtl_serial_numbers_temp
426: WHERE transaction_temp_id = l_transaction_temp_id(i)
427: AND fm_serial_number = l_fm_serial_number(i);
428:
429: del_rows := del_rows + sql%rowcount;
445: CLOSE dd_msnt_rec_om;
446: -- bug 6625172: Deleting the msnt records for all the selected transaction_temp_id's for OM records
447: IF (ins_rows > 0) THEN
448: --{
449: DELETE mtl_serial_numbers_temp
450: WHERE transaction_temp_id IN
451: ( SELECT DISTINCT dd.transaction_temp_id
452: from wsh_delivery_details dd,
453: wsh_delivery_assignments da,
453: wsh_delivery_assignments da,
454: wsh_delivery_legs dg,
455: wsh_new_deliveries dl,
456: wsh_trip_stops st,
457: mtl_serial_numbers_temp msnt
458: where st.batch_id = p_batch_id
459: and st.stop_location_id = dl.initial_pickup_location_id
460: and dg.delivery_id = dl.delivery_id
461: and da.delivery_id = dl.delivery_id
478: CLOSE dd_msnt_rec;
479: -- bug 6625172: Deleting the msnt records for all the selected transaction_temp_id's for non-OM records
480: IF (ins_rows > 0) THEN
481: --{
482: DELETE mtl_serial_numbers_temp
483: WHERE transaction_temp_id IN
484: ( SELECT DISTINCT dd.transaction_temp_id
485: from wsh_delivery_details dd,
486: wsh_delivery_assignments da,
486: wsh_delivery_assignments da,
487: wsh_delivery_legs dg,
488: wsh_new_deliveries dl,
489: wsh_trip_stops st,
490: mtl_serial_numbers_temp msnt
491: where st.batch_id = p_batch_id
492: and st.stop_location_id = dl.initial_pickup_location_id
493: and dg.delivery_id = dl.delivery_id
494: and da.delivery_id = dl.delivery_id
507: END IF;
508:
509: IF l_debug_on THEN
510: WSH_DEBUG_SV.log(l_module_name,'Numbers of rows inserted into wsh_serial_numbers: '|| to_char(ins_rows) ||
511: ', Number of rows deleted from mtl_serial_numbers_temp: '|| to_char(del_rows) ||
512: ', Number of rows updated in wsh_delivery_details: '|| to_char(upd_rows) );
513: WSH_DEBUG_SV.pop(l_module_name);
514: END IF;
515:
550: -- value 'INV' is passed.
551: -- x_return_status return status of the API.
552: --
553: -- COMMENT : This procedure is used to transfer the serial number information
554: -- from mtl_serial_numbers_temp to the wsh_serial_numbers table.
555: -- It then deletes these entries from mtl_serial_numbers_temp.
556: -- IF this procedure is called during the INV interface then only
557: -- the lines that have been interfaced to OM are processed, else
558: -- the non-oe lines are processed as well.
551: -- x_return_status return status of the API.
552: --
553: -- COMMENT : This procedure is used to transfer the serial number information
554: -- from mtl_serial_numbers_temp to the wsh_serial_numbers table.
555: -- It then deletes these entries from mtl_serial_numbers_temp.
556: -- IF this procedure is called during the INV interface then only
557: -- the lines that have been interfaced to OM are processed, else
558: -- the non-oe lines are processed as well.
559: --========================================================================
4119: --
4120: return;
4121: END IF;
4122:
4123: -- bug 2787888 : Added call to transfer serial records from mtl_serial_numbers_temp to wsh_serial_numbers
4124: IF l_debug_on THEN
4125: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling TRANSFER_SERIAL_NUMBERS FOR BATCH ' || TO_CHAR ( P_batch_id ) );
4126: END IF;
4127:
5292: ELSE --}{
5293: l_completion_status := 'INTERFACED' ;
5294: END if; --}
5295: --HVOP heali
5296: -- Bug 2657652 : Added call to transfer serial records from mtl_serial_numbers_temp to wsh_serial_numbers
5297: IF l_debug_on THEN
5298: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling TRANSFER_SERIAL_NUMBERS FOR ' || TO_CHAR ( p_batch_id ) );
5299: END IF;
5300: