[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT wdd.delivery_detail_id
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE wdd.source_header_id = p_header_id
AND wdd.source_line_id = p_line_id
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id IS NULL;
SELECT wdd.delivery_detail_id,
wdd.released_status,
wdd.organization_id,
wdd.original_subinventory,
wda.delivery_id,
wnd.name delivery_name,
wnd.ship_method_code
FROM wsh_delivery_details wdd,
wsh_delivery_assignments wda,
wsh_new_deliveries wnd
WHERE wdd.source_header_id = p_header_id
AND wdd.source_line_id = p_line_id
AND wdd.released_status IN ('B', 'R', 'Y')
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wda.delivery_id = wnd.delivery_id;
SELECT mtrh.header_id,
mtrh.move_order_type,
mtrl.line_id
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
wsh_delivery_details wdd
WHERE mtrh.header_id = mtrl.header_id
AND mtrl.line_id = wdd.move_order_line_id
AND wdd.delivery_detail_id = p_delivery_detail_id;
UPDATE oe_order_lines_all
SET ship_tolerance_above = ROUND(l_ship_tolerance_above)
WHERE header_id = p_header_id
AND line_id=p_line_id;
UPDATE wsh_delivery_details wdd1
SET wdd1.ship_tolerance_above = ROUND(l_ship_tolerance_above)
WHERE wdd1.delivery_detail_id IN
(SELECT delivery_detail_id
FROM wsh_delivery_details wdd2,
oe_order_lines_all oel
WHERE wdd2.source_header_id = oel.header_id
AND wdd2.source_line_id = oel.line_id
AND oel.source_document_id = p_inter_order_id
AND oel.source_document_line_id = p_inter_line_id
AND oel.header_id = p_header_id
AND oel.line_id = p_line_id
);
l_trolin_tbl(1).operation := INV_GLOBALS.G_OPR_UPDATE;
l_trolin_tbl.DELETE;
SELECT request_number,
move_order_type
FROM mtl_txn_request_headers
WHERE header_id = p_header_id;
SELECT line_status
FROM mtl_txn_request_lines
WHERE line_id = p_line_id;
SELECT line_id
FROM mtl_txn_request_lines
WHERE header_id = p_header_id
AND line_status not in (5, 6);
UPDATE mtl_txn_request_lines
SET quantity_detailed = NULL
WHERE line_id = p_intra_line_id;
p_transaction_mode => 1, -- INSERT
p_move_order_type => l_move_order_type,
p_serial_flag => NULL,
p_plan_tasks => FALSE,
p_auto_pick_confirm => FALSE,
p_commit => FALSE
);
UPDATE mtl_txn_request_lines
SET quantity_detailed = quantity_delivered
WHERE line_id = p_intra_line_id;
* This procedure validate the input PARAMETERS and select the necessary info
* required for further validations and processing.
*
* PARAMETERS
* ==========
* NAME TYPE DESCRIPTION
* ----------------- -------- ---------------------------------------------
* p_kanban_card_id IN This parameter passes the Kanban Card Id.
* p_transfer_qty IN This parameter passes the actual transfer quantity.
* x_kanban_type OUT Returns the KanBan Type (Inter/Intra)
* x_inter_order_num OUT Returns the Kanban Internal Sales Order Number
* x_inter_order_id OUT Returns the Kanban Internal Sales Order Id
* x_inter_line_id OUT Returns the Kanban Internal Sales Order Line Id
* x_intra_order_num OUT Returns the Kanban move order number
* x_intra_order_id OUT Returns the kanban mover order id
* x_intra_line_id OUT Returns the Kanban Move order line id
* x_header_id OUT Returns the header_id from OE_ORDER_LINES_ALL
* x_line_id OUT Returns the line_id from OE_ORDER_LINES_ALL
* x_kanban_size OUT Returns the kanban size
* x_retcode OUT Returns the error code.
* Values are: S - Normal Completion
* W - Warning
* E - Error
* x_errmsg OUT Returns the error message if any.
*
*PREREQUISITES
* None
*
* CALLED BY
* complete_process
*
**********************************************************************************************/
PROCEDURE validate_data
( p_kanban_card_id IN NUMBER,
p_transfer_qty IN NUMBER,
x_organization_id OUT NOCOPY NUMBER,
x_source_type OUT NOCOPY NUMBER,
x_kanban_size OUT NOCOPY NUMBER,
x_inter_order_num OUT NOCOPY VARCHAR2,
x_inter_order_id OUT NOCOPY NUMBER,
x_inter_line_id OUT NOCOPY NUMBER,
x_intra_order_num OUT NOCOPY VARCHAR2,
x_intra_order_id OUT NOCOPY NUMBER,
x_intra_line_id OUT NOCOPY NUMBER,
x_header_id OUT NOCOPY NUMBER,
x_line_id OUT NOCOPY NUMBER,
x_retcode OUT NOCOPY VARCHAR2,
x_errmsg OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'validate_data';
SELECT SUM(moq.transaction_quantity) transaction_quantity
FROM mtl_onhand_quantities moq,
mtl_kanban_cards mkc
WHERE mkc.inventory_item_id = moq.inventory_item_id
AND mkc.SOURCE_organization_id = moq.organization_id
AND mkc.source_subinventory = moq.subinventory_code
AND NVL(mkc.SOURCE_LOCATOR_ID, 1) = NVL(moq.locator_id, 1)
AND mkc.kanban_card_id = p_kanban_card_id;
SELECT oeh.order_number,
mkca.document_header_id inter_header_id,
mkca.document_detail_id inter_line_id,
wdd.ship_tolerance_above,
oel.flow_status_code flow_status_code,
oel.line_id line_id,
oel.header_id header_id
FROM oe_order_headers_all oeh,
oe_order_lines_all oel ,
wsh_delivery_details wdd,
mtl_kanban_cards mkc,
mtl_kanban_card_activity mkca
WHERE oeh.source_document_id = mkca.document_header_id
AND oeh.header_id = oel.header_id
AND oel.source_document_line_id = mkca.document_detail_id
AND wdd.source_line_id = oel.line_id
AND wdd.source_header_id = oeh.header_id
AND wdd.released_status IN ('B', 'R', 'Y')
AND oeh.flow_status_code = 'BOOKED'
AND oel.flow_status_code = 'AWAITING_SHIPPING'
AND mkca.kanban_card_id = mkc.kanban_card_id
AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
AND mkc.kanban_card_id = p_kanban_card_id;
SELECT mtrh.request_number,
mtrh.header_id,
mtrl.line_id
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_kanban_cards mkc,
mtl_kanban_card_activity mkca
WHERE mtrh.header_id = mkca.document_header_id
AND mtrl.line_id = mkca.document_detail_id
AND mtrh.header_status = mtrl.line_status
AND mtrh.header_status = 7 -- Pre Approved
AND mkca.kanban_card_id = mkc.kanban_card_id
AND mkca.replenishment_cycle_id = mkc.current_replnsh_cycle_id
AND mkc.kanban_card_id = p_kanban_card_id;
SELECT mkc.kanban_card_id,
mkc.card_status,
mkc.source_type,
mkc.kanban_size,
mkc.inventory_item_id,
mkc.organization_id,
mkc.subinventory_name,
mkc.locator_id,
mkc.source_organization_id,
mkc.source_subinventory,
mkc.source_locator_id
FROM mtl_kanban_cards mkc
WHERE mkc.kanban_card_id = p_kanban_card_id;
IS SELECT msi.lot_control_code,
msi.serial_number_control_code
FROM mtl_system_items msi
WHERE msi.inventory_item_id = p_item_id
AND msi.organization_id = p_organization_id;