[Home] [Help]
5097: -- Start of comments
5098: -- API name : reconfigure_del_trips
5099: -- Type : Public
5100: -- Pre-reqs : None.
5101: -- Function : The Bill of Lading(From WSH_DOCUMENT_INSTANCES for entity name
5102: -- and entity id) will trigger the reconfiguration of the
5103: -- deliveries and associated trips.
5104: -- For delivery details which will be marked as 'C'-SHIPPED,
5105: -- we need to ensure that lines having the same Bill of Lading
6157: WDG.delivery_leg_id,
6158: wdi.sequence_number bol
6159: from
6160: wsh_delivery_legs wdg,
6161: wsh_document_instances wdi
6162: where
6163: wdg.delivery_leg_id = wdi.entity_id and
6164: wdi.entity_name = 'WSH_DELIVERY_LEGS' AND
6165: wdg.delivery_id = p_del_id AND
6208: END IF;
6209: --
6210:
6211:
6212: -- If there is a row present in the table wsh_document_instances for this
6213: -- delivery leg id, then the corresponding row is updated with the new BOL,
6214: -- if the BOLs are different i.e if the BOL passed as an input to this API
6215: -- is different from the BOL present in the table.
6216: -- If there is no row in the table wsh_document_instances for this
6212: -- If there is a row present in the table wsh_document_instances for this
6213: -- delivery leg id, then the corresponding row is updated with the new BOL,
6214: -- if the BOLs are different i.e if the BOL passed as an input to this API
6215: -- is different from the BOL present in the table.
6216: -- If there is no row in the table wsh_document_instances for this
6217: -- delivery leg id, then the API WSH_ASN_RECEIPT_PVT.CREATE_UPDATE_INBOUND_DOCUMENT
6218: -- is called to create a new row.
6219:
6220: IF c_BOL%FOUND THEN
6218: -- is called to create a new row.
6219:
6220: IF c_BOL%FOUND THEN
6221: IF ((p_bol IS NOT NULL) AND (nvl(p_bol,'$$$$') <> nvl(l_bol,'$$$$'))) THEN
6222: UPDATE wsh_document_instances
6223: SET sequence_number = p_bol,
6224: last_update_date = SYSDATE,
6225: last_updated_by = FND_GLOBAL.USER_ID,
6226: last_update_login = FND_GLOBAL.LOGIN_ID
7530: OPEN c_get_last_leg(l_new_del_id); --code changed to use the newly created Delivery ID
7531: FETCH c_get_last_leg
7532: INTO l_final_del_leg_id;
7533:
7534: -- Create a new record in the table wsh_document_instances and update the record
7535: -- with the BOL number curr_bol for the current Delivery.
7536: IF (c_get_last_leg%FOUND) AND (curr_bol IS NOT NULL) THEN
7537:
7538: IF l_debug_on THEN
7564: l_stored_del_id := l_new_del_id;
7565:
7566:
7567: -- The following Else part -> Set the BOL for the last leg of the current Delivery if a matching record
7568: -- is already existing in the table wsh_document_instances.
7569: ELSE -- corresponding to IF l_trigger = 1 THEN
7570: --} {
7571:
7572: OPEN c_get_last_leg(curr_del);
7614: pack_ids(pack_ids.count + 1) := p_local_dd_rec(l_loop_index).del_detail_id;
7615:
7616:
7617: -- Update the Packing Slip number for the current delivery after checking
7618: -- for the existence of a matching record in wsh_document_instances.
7619: -- If there is no matching record insert a new record using the
7620: -- API WSH_ASN_RECEIPT_PVT.CREATE_UPDATE_INBOUND_DOCUMENT.
7621: IF ((l_psno_flag = 0) AND (l_psno IS NOT NULL) ) THEN
7622: UPDATE wsh_document_instances
7618: -- for the existence of a matching record in wsh_document_instances.
7619: -- If there is no matching record insert a new record using the
7620: -- API WSH_ASN_RECEIPT_PVT.CREATE_UPDATE_INBOUND_DOCUMENT.
7621: IF ((l_psno_flag = 0) AND (l_psno IS NOT NULL) ) THEN
7622: UPDATE wsh_document_instances
7623: SET
7624: sequence_number = l_psno,
7625: last_update_date = SYSDATE,
7626: last_updated_by = FND_GLOBAL.USER_ID,
7802: Cursor c_bol_psno_exists(l_ent_id NUMBER,l_ent_name VARCHAR2,l_doc_type VARCHAR2) is
7803: select
7804: '1'
7805: from
7806: wsh_document_instances wdi
7807: where
7808: wdi.entity_id = l_ent_id AND
7809: wdi.entity_name = l_ent_name AND
7810: wdi.document_type= l_doc_type;
7924: WSH_DEBUG_SV.log(l_module_name,'l_temp',l_temp);
7925: END IF;
7926:
7927:
7928: --True if record already exists in wsh_document_instances for this delivery leg id
7929: --.So just do an update for the current document number.
7930: IF c_bol_psno_exists%FOUND THEN
7931:
7932:
7929: --.So just do an update for the current document number.
7930: IF c_bol_psno_exists%FOUND THEN
7931:
7932:
7933: UPDATE wsh_document_instances
7934: SET sequence_number = p_document_number,
7935: last_update_date = SYSDATE,
7936: last_updated_by = FND_GLOBAL.USER_ID,
7937: last_update_login = FND_GLOBAL.LOGIN_ID
7938: WHERE entity_name = p_entity_name
7939: AND entity_id = l_entity_id
7940: AND document_type= l_document_type;
7941:
7942: --means no matching records in wsh_document_instances
7943: --so insert a new record
7944: ELSIF c_bol_psno_exists%NOTFOUND THEN
7945:
7946: --{ Bug 3761178
7958: --}
7959:
7960: l_appl_num := 665; --Bug# 3789154
7961:
7962: INSERT INTO wsh_document_instances
7963: ( document_instance_id
7964: , document_type
7965: , sequence_number
7966: , status
7994: , attribute14
7995: , attribute15
7996: )
7997: VALUES
7998: ( wsh_document_instances_s.nextval
7999: , l_document_type
8000: , p_document_number
8001: , l_status
8002: , null