[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT picking_line_detail_id, requested_quantity, shipped_quantity,
serial_number
FROM so_picking_line_details
WHERE delivery_id = X_Delivery_Id
AND transaction_temp_id IS NOT NULL;
SELECT msi.serial_number_control_code
FROM mtl_system_items msi,
so_picking_lines_all pl,
so_picking_line_details pld
WHERE pl.inventory_item_id = msi.inventory_item_id
AND pl.warehouse_id = msi.organization_id
AND msi.serial_number_control_code in (2,5,6)
AND pl.picking_line_id = pld.picking_line_id
AND pld.picking_line_detail_id = X_Pk_Line_Detail_Id;
Delete_From_Msnt(X_Delivery_Id);
PROCEDURE Update_Details( X_Trx_Src_Line_Id IN NUMBER,
X_Requested_Qty IN NUMBER,
X_Shipped_Qty IN NUMBER,
X_Serial IN VARCHAR2) IS
BEGIN
UPDATE SO_PICKING_LINE_DETAILS PLD
SET
(LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUESTED_QUANTITY,
SHIPPED_QUANTITY,
SERIAL_NUMBER ) =
(SELECT
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
X_Requested_Qty,
X_Shipped_Qty,
X_Serial
FROM DUAL)
WHERE PICKING_LINE_DETAIL_ID = X_Trx_Src_Line_Id;
FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Update_Details');
FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Update_Details');
END Update_Details;
FUNCTION Insert_Splitted_Details( X_Parent_Detail_Id IN NUMBER,
X_Req_Qty IN NUMBER,
X_detail_type_code IN VARCHAR2)
RETURN NUMBER IS
X_new_detail_id NUMBER;
SELECT SO_PICKING_LINE_DETAILS_S.NEXTVAL
INTO X_new_detail_id
FROM DUAL;
Insert_Details( x_new_detail_id,
X_Parent_Detail_Id,
NULL,
X_Req_Qty,
NULL,
'SPLIT',
X_detail_type_code);
END Insert_Splitted_Details;
PROCEDURE Insert_Details( X_New_Detail_Id IN NUMBER,
X_Parent_Detail_Id IN NUMBER,
X_Trx_Qty IN NUMBER,
X_Req_Qty IN NUMBER,
X_Serial IN VARCHAR2,
X_Mode IN VARCHAR2,
X_detail_type_code IN VARCHAR2 DEFAULT 'NA')
IS
X_Created_Detail_Id NUMBER;
SELECT SO_PICKING_LINE_DETAILS_S.NEXTVAL
INTO X_Created_Detail_Id
FROM DUAL;
INSERT INTO SO_PICKING_LINE_DETAILS (
PICKING_LINE_DETAIL_ID
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,CREATED_BY
,CREATION_DATE
,LAST_UPDATE_LOGIN
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PICKING_LINE_ID
,WAREHOUSE_ID
,REQUESTED_QUANTITY
,SHIPPED_QUANTITY
,SERIAL_NUMBER
,LOT_NUMBER
,CUSTOMER_REQUESTED_LOT_FLAG
,REVISION
,SUBINVENTORY
,INVENTORY_LOCATION_ID
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,SEGMENT8
,SEGMENT9
,SEGMENT10
,SEGMENT11
,SEGMENT12
,SEGMENT13
,SEGMENT14
,SEGMENT15
,SEGMENT16
,SEGMENT17
,SEGMENT18
,SEGMENT19
,SEGMENT20
,INVENTORY_LOCATION_SEGMENTS
,DETAIL_TYPE_CODE
,CONTEXT
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RELEASED_FLAG
,SCHEDULE_DATE
,SCHEDULE_LEVEL
,SCHEDULE_STATUS_CODE
,DEMAND_ID
,AUTOSCHEDULED_FLAG
,DELIVERY
,WIP_RESERVED_QUANTITY
,WIP_COMPLETED_QUANTITY
,SUPPLY_SOURCE_TYPE
,SUPPLY_SOURCE_HEADER_ID
,UPDATE_FLAG
,DEMAND_CLASS_CODE
,RESERVABLE_FLAG
,TRANSACTABLE_FLAG
,LATEST_ACCEPTABLE_DATE
,DPW_ASSIGNED_FLAG
,DELIVERY_ID
,DEPARTURE_ID
,LOAD_SEQ_NUMBER
,MASTER_CONTAINER_ITEM_ID
,DETAIL_CONTAINER_ITEM_ID
,TRANSACTION_TEMP_ID
,PICK_SLIP_NUMBER
,CONTAINER_ID
,MVT_STAT_STATUS)
SELECT
Nvl(X_New_Detail_id, X_Created_Detail_id)
,SYSDATE
,FND_GLOBAL.USER_ID
,FND_GLOBAL.USER_ID
,SYSDATE
,FND_GLOBAL.LOGIN_ID
,PROGRAM_APPLICATION_ID
,PROGRAM_ID
,PROGRAM_UPDATE_DATE
,REQUEST_ID
,PICKING_LINE_ID
,WAREHOUSE_ID
,X_Req_Qty
,X_Trx_Qty
,X_Serial
,LOT_NUMBER
,CUSTOMER_REQUESTED_LOT_FLAG
,REVISION
,SUBINVENTORY
,INVENTORY_LOCATION_ID
,SEGMENT1
,SEGMENT2
,SEGMENT3
,SEGMENT4
,SEGMENT5
,SEGMENT6
,SEGMENT7
,SEGMENT8
,SEGMENT9
,SEGMENT10
,SEGMENT11
,SEGMENT12
,SEGMENT13
,SEGMENT14
,SEGMENT15
,SEGMENT16
,SEGMENT17
,SEGMENT18
,SEGMENT19
,SEGMENT20
,INVENTORY_LOCATION_SEGMENTS
,Decode( X_detail_type_code, 'NA', DETAIL_TYPE_CODE,
X_detail_type_code)
,CONTEXT
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,RELEASED_FLAG
,SCHEDULE_DATE
,SCHEDULE_LEVEL
,SCHEDULE_STATUS_CODE
,DEMAND_ID
,AUTOSCHEDULED_FLAG
,DELIVERY
,WIP_RESERVED_QUANTITY
,WIP_COMPLETED_QUANTITY
,SUPPLY_SOURCE_TYPE
,SUPPLY_SOURCE_HEADER_ID
,UPDATE_FLAG
,DEMAND_CLASS_CODE
,RESERVABLE_FLAG
,TRANSACTABLE_FLAG
,LATEST_ACCEPTABLE_DATE
,DPW_ASSIGNED_FLAG
,DELIVERY_ID
,DEPARTURE_ID
,LOAD_SEQ_NUMBER
,MASTER_CONTAINER_ITEM_ID
,DETAIL_CONTAINER_ITEM_ID
,decode(X_Mode,'REMAINDER', NULL,TRANSACTION_TEMP_ID)
,PICK_SLIP_NUMBER
,decode(X_Mode,'REMAINDER', NULL,CONTAINER_ID)
,MVT_STAT_STATUS
FROM SO_PICKING_LINE_DETAILS WHERE
PICKING_LINE_DETAIL_ID = X_Parent_Detail_Id ;
FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Insert_Details');
FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Insert_Details');
Insert_Details( NULL,
X_Picking_Line_Detail_Id,
0,
X_New_Requested,
NULL ,
'REMAINDER') ;
UPDATE so_picking_line_details pld
SET pld.requested_quantity = pld.shipped_quantity
WHERE pld.picking_line_detail_id = X_Picking_Line_Detail_Id;
SELECT msnt.transaction_temp_id,
pld.picking_line_detail_id,
msnt.vendor_serial_number,
msnt.fm_serial_number,
msnt.to_serial_number,
msnt.serial_prefix
FROM mtl_serial_numbers_temp msnt,
so_picking_line_details pld
WHERE pld.picking_line_detail_id = pk_line_detail_id
AND pld.transaction_temp_id = msnt.transaction_temp_id(+)
ORDER BY msnt.transaction_temp_id;
X_can_update NUMBER;
X_can_update := 1;
-- Update first picking line detail
X_loop_end := to_number(X_serial_qty);
IF (X_can_update = 1) THEN
Update_Details(X_trx_source_line_id,
1,
1,
X_Serial_Current);
X_can_update := 0;
-- Insert the rest of the line details
FOR I IN 1..X_loop_end LOOP
-- Determine next serial number
X_Serial_Current := Next_Serial(X_Serial_Prefix,
X_Serial_Numeric_Len,
X_Serial_Numeric);
Insert_Details(NULL,
X_trx_source_line_id,
1,
1,
X_Serial_Current,
'NEW');
select s_prefix || lpad('000000000000000000000000000000',
s_num_length - length(to_char(s_num_current + 1)))
|| to_char(s_num_current + 1)
into X_new_serial
from dual;
PROCEDURE Delete_From_Msnt(X_Delivery_Id NUMBER ) IS
BEGIN
DELETE FROM mtl_serial_numbers_temp
WHERE transaction_temp_id in
(SELECT transaction_temp_id
FROM so_picking_line_details
WHERE delivery_id = X_Delivery_Id );
FND_MESSAGE.Set_Token('PACKAGE','WSH_SC_PLD_PKG.Delete_From_Msnt');