[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: --========================================================================
127: wsh_delivery_assignments_v da,
128: wsh_delivery_legs dg,
129: wsh_new_deliveries dl,
130: wsh_trip_stops st,
131: mtl_serial_numbers_temp msnt
132: where st.batch_id = p_batch_id
133: and st.stop_location_id = dl.initial_pickup_location_id
134: and dg.delivery_id = dl.delivery_id
135: and da.delivery_id = dl.delivery_id
222: wsh_delivery_assignments_v da,
223: wsh_delivery_legs dg,
224: wsh_new_deliveries dl,
225: wsh_trip_stops st,
226: mtl_serial_numbers_temp msnt
227: where st.batch_id = p_batch_id
228: and st.stop_location_id = dl.initial_pickup_location_id
229: and dg.delivery_id = dl.delivery_id
230: and da.delivery_id = dl.delivery_id
860: -- Bug 6625172: Removing Delete of msnt for each transaction_temp_id from here
861: -- and updating wsh_delivery_details only when p_transfer_param is 'WDD'
862: IF p_transfer_param = 'WDD' THEN
863: /* FORALL i IN 1 .. cur_fetch
864: DELETE FROM mtl_serial_numbers_temp
865: WHERE transaction_temp_id = l_transaction_temp_id(i)
866: AND fm_serial_number = l_fm_serial_number(i);
867:
868: del_rows := del_rows + sql%rowcount;
888: CLOSE dd_msnt_rec_om;
889: -- bug 6625172: Deleting the msnt records for all the selected transaction_temp_id's for OM records
890: IF (ins_rows > 0) THEN
891: --{
892: DELETE mtl_serial_numbers_temp
893: WHERE transaction_temp_id IN
894: ( SELECT DISTINCT dd.transaction_temp_id
895: from wsh_delivery_details dd,
896: wsh_delivery_assignments da,
896: wsh_delivery_assignments da,
897: wsh_delivery_legs dg,
898: wsh_new_deliveries dl,
899: wsh_trip_stops st,
900: mtl_serial_numbers_temp msnt
901: where st.batch_id = p_batch_id
902: and st.stop_location_id = dl.initial_pickup_location_id
903: and dg.delivery_id = dl.delivery_id
904: and da.delivery_id = dl.delivery_id
921: CLOSE dd_msnt_rec;
922: -- bug 6625172: Deleting the msnt records for all the selected transaction_temp_id's for non-OM records
923: IF (ins_rows > 0) THEN
924: --{
925: DELETE mtl_serial_numbers_temp
926: WHERE transaction_temp_id IN
927: ( SELECT DISTINCT dd.transaction_temp_id
928: from wsh_delivery_details dd,
929: wsh_delivery_assignments da,
929: wsh_delivery_assignments da,
930: wsh_delivery_legs dg,
931: wsh_new_deliveries dl,
932: wsh_trip_stops st,
933: mtl_serial_numbers_temp msnt
934: where st.batch_id = p_batch_id
935: and st.stop_location_id = dl.initial_pickup_location_id
936: and dg.delivery_id = dl.delivery_id
937: and da.delivery_id = dl.delivery_id
950: END IF;
951:
952: IF l_debug_on THEN
953: WSH_DEBUG_SV.log(l_module_name,'Numbers of rows inserted into wsh_serial_numbers: '|| to_char(ins_rows) ||
954: ', Number of rows deleted from mtl_serial_numbers_temp: '|| to_char(del_rows) ||
955: ', Number of rows updated in wsh_delivery_details: '|| to_char(upd_rows) );
956: WSH_DEBUG_SV.pop(l_module_name);
957: END IF;
958:
993: -- value 'INV' is passed.
994: -- x_return_status return status of the API.
995: --
996: -- COMMENT : This procedure is used to transfer the serial number information
997: -- from mtl_serial_numbers_temp to the wsh_serial_numbers table.
998: -- It then deletes these entries from mtl_serial_numbers_temp.
999: -- IF this procedure is called during the INV interface then only
1000: -- the lines that have been interfaced to OM are processed, else
1001: -- the non-oe lines are processed as well.
994: -- x_return_status return status of the API.
995: --
996: -- COMMENT : This procedure is used to transfer the serial number information
997: -- from mtl_serial_numbers_temp to the wsh_serial_numbers table.
998: -- It then deletes these entries from mtl_serial_numbers_temp.
999: -- IF this procedure is called during the INV interface then only
1000: -- the lines that have been interfaced to OM are processed, else
1001: -- the non-oe lines are processed as well.
1002: --========================================================================
5060: --
5061: return;
5062: END IF;
5063:
5064: -- bug 2787888 : Added call to transfer serial records from mtl_serial_numbers_temp to wsh_serial_numbers
5065: IF l_debug_on THEN
5066: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling TRANSFER_SERIAL_NUMBERS FOR BATCH ' || TO_CHAR ( P_batch_id ) );
5067: END IF;
5068:
6817: ELSE --}{
6818: l_completion_status := 'INTERFACED' ;
6819: END if; --}
6820: --HVOP heali
6821: -- Bug 2657652 : Added call to transfer serial records from mtl_serial_numbers_temp to wsh_serial_numbers
6822: IF l_debug_on THEN
6823: WSH_DEBUG_SV.logmsg(l_module_name, 'Calling TRANSFER_SERIAL_NUMBERS FOR ' || TO_CHAR ( p_batch_id ) );
6824: END IF;
6825: