[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NAME,
BACKORDERS_ONLY_FLAG,
NVL(RELEASE_SEQ_RULE_ID, -1),
NVL(PICK_SLIP_RULE_ID, -1),
NVL(PARTIAL_ALLOWED_FLAG, 'N'),
NVL(INCLUDE_PLANNED_LINES, 'N'),
NVL(CUSTOMER_ID, 0),
NVL(DATE_REQUESTED_FROM, NULL),
NVL(DATE_REQUESTED_TO, NULL),
NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
NVL(HEADER_ID, 0),
NVL(INVENTORY_ITEM_ID, 0),
NVL(DEPARTURE_ID, 0),
NVL(DELIVERY_ID, 0),
NVL(ORDER_TYPE_ID, 0),
NVL(SCHEDULED_SHIPMENT_DATE_FROM, NULL),
NVL(SCHEDULED_SHIPMENT_DATE_TO, NULL),
NVL(SHIPMENT_PRIORITY_CODE, ''),
NVL(SHIP_METHOD_CODE, ''),
NVL(SHIP_SET_NUMBER, 0),
NVL(SITE_USE_ID, 0),
NVL(SUBINVENTORY, ''),
NVL(WAREHOUSE_ID, -1),
NVL(ORG_ID, -3114),
NVL(AUTOCREATE_DELIVERY_FLAG,'N'),
NVL(ORDER_LINE_ID, 0),
TO_NUMBER(NVL(PRINT_FLAG, '-1'))
FROM SO_PICKING_BATCHES_ALL
WHERE BATCH_ID = x_batch_id
FOR UPDATE OF BATCH_ID NOWAIT;
SELECT NAME,
NVL(ORDER_ID_PRIORITY, -1),
DECODE(ORDER_ID_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(INVOICE_VALUE_PRIORITY, -1),
DECODE(INVOICE_VALUE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SCHEDULE_DATE_PRIORITY, -1),
DECODE(SCHEDULE_DATE_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(SHIPMENT_PRI_PRIORITY, -1),
DECODE(SHIPMENT_PRI_SORT, 'A', 'ASC', 'D', 'DESC', ''),
NVL(DEPARTURE_PRIORITY, -1),
DECODE(DEPARTURE_SORT, 'A', 'ASC', 'D', 'DESC', '')
FROM WSH_RELEASE_SEQ_RULES
WHERE RELEASE_SEQ_RULE_ID = x_rsr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
SELECT NVL(ORDER_NUMBER_FLAG,'N')
FROM WSH_PICK_SLIP_RULES
WHERE PICK_SLIP_RULE_ID = x_psr_id
AND SYSDATE BETWEEN TRUNC(NVL(START_DATE_ACTIVE, SYSDATE)) AND
NVL(END_DATE_ACTIVE, TRUNC(SYSDATE)+1);
SELECT NVL(PRINT_PICK_SLIP_MODE, 'E')
FROM WSH_PARAMETERS
WHERE ORGANIZATION_ID = x_org_id;
SELECT HEADER_ID, NVL(PARENT_LINE_ID, -1)
FROM SO_LINES_ALL
WHERE LINE_ID = x_line_id;
-- of building the select statement
invoice_value_flag := 'Y';
-- Update picking batch setting request id and other who parameters
-- Use the parameters passed to the Init Function, instead of using the
-- column names in the = conditions of the SQL. That is use
-- p_user_id , p_program_id , p_request_id , p_login_id and p_batch_id and p_application_id
UPDATE SO_PICKING_BATCHES_ALL
SET REQUEST_ID = p_request_id,
PROGRAM_APPLICATION_ID = p_application_id,
PROGRAM_ID = p_program_id,
PROGRAM_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = p_user_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = p_login_id
WHERE BATCH_ID = p_batch_id
AND (REQUEST_ID IS NULL OR REQUEST_ID = p_request_id);
WSH_UTIL.Write_Log('Could not lock Batch ID ' || to_char(p_batch_id) || ' for update.');
SELECT COUNT(*)
FROM SO_REPORT_SETS
WHERE REPORT_SET_ID = x_doc_set_id
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE+1);
SELECT COUNT(*)
FROM SO_PICKING_RULES
WHERE PICKING_RULE = x_rule_name
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE)
AND NVL(END_DATE_ACTIVE, SYSDATE+1);
SELECT par.pick_slip_rule_id
FROM WSH_PARAMETERS par,
SO_PICKING_RULES rules
WHERE rules.picking_rule = x_rule_name
AND rules.warehouse_id = par.organization_id;
SELECT par.release_seq_rule_id
FROM WSH_PARAMETERS par,
SO_PICKING_RULES rules
WHERE rules.picking_rule = x_rule_name
AND rules.warehouse_id = par.organization_id;
SELECT SO_PICKING_BATCHES_S.NEXTVAL
INTO x_batch_id
FROM DUAL;
SELECT COUNT(*)
INTO count_temp
FROM SO_PICKING_BATCHES_ALL
WHERE NAME = x_batch_name;
INSERT INTO SO_PICKING_BATCHES_ALL
(BATCH_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
NAME,
PRINT_FLAG,
BACKORDERS_ONLY_FLAG,
EXISTING_RSVS_ONLY_FLAG,
SHIPMENT_PRIORITY_CODE,
HEADER_ID,
ORDER_TYPE_ID,
WAREHOUSE_ID,
CUSTOMER_ID,
SITE_USE_ID,
SHIP_METHOD_CODE,
SUBINVENTORY,
SHIP_SET_NUMBER,
INVENTORY_ITEM_ID,
DATE_REQUESTED_FROM,
DATE_REQUESTED_TO,
SCHEDULED_SHIPMENT_DATE_FROM,
SCHEDULED_SHIPMENT_DATE_TO,
PICK_SLIP_RULE_ID,
RELEASE_SEQ_RULE_ID,
PARTIAL_ALLOWED_FLAG,
INCLUDE_PLANNED_LINES,
AUTOCREATE_DELIVERY_FLAG,
ORG_ID)
SELECT x_batch_id,
SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_login_id,
x_batch_name,
decode(p_doc_set, '-1', NULL, p_doc_set),
BACKORDERS_ONLY_FLAG,
NVL(EXISTING_RSVS_ONLY_FLAG, 'N'),
SHIPMENT_PRIORITY_CODE,
HEADER_ID,
ORDER_TYPE_ID,
WAREHOUSE_ID,
CUSTOMER_ID,
SITE_USE_ID,
SHIP_METHOD_CODE,
SUBINVENTORY,
SHIP_SET_NUMBER,
INVENTORY_ITEM_ID,
DECODE(DATE_REQUESTED_FROM, TO_DATE (1,'J'), TRUNC(SYSDATE),
DATE_REQUESTED_FROM),
DECODE(DATE_REQUESTED_TO, TO_DATE (1,'J'), TRUNC(SYSDATE),
DATE_REQUESTED_TO),
DECODE(SCHEDULED_SHIPMENT_DATE_FROM,TO_DATE (1,'J'), TRUNC(SYSDATE),
SCHEDULED_SHIPMENT_DATE_FROM),
DECODE(SCHEDULED_SHIPMENT_DATE_TO,TO_DATE (1,'J'), TRUNC(SYSDATE),
SCHEDULED_SHIPMENT_DATE_TO),
NVL(PICK_SLIP_RULE_ID, default_psr),
NVL(RELEASE_SEQ_RULE_SET_ID, default_rsr),
PARTIAL_ALLOWED_FLAG,
INCLUDE_PLANNED_LINES_FLAG,
AUTOCREATE_DELIVERY_FLAG,
operating_org -- Insert Operating Org
FROM SO_PICKING_RULES
WHERE PICKING_RULE = p_rule_name;
WSH_UTIL.Write_Log('Inserted batch name ' || x_batch_name
|| ' with batch_id ' ||
to_char(p_new_batch_id));
Process_Buffer('u', ' SELECT ');
Process_Buffer('u', ' (SELECT * FROM SO_LINES_ALL SL');
Process_Buffer('b', ' SELECT ');
Process_Buffer('b', ' (SELECT * FROM SO_LINES_ALL SL');
Process_Buffer('s', ' SELECT ');
Process_Buffer('s', ' (SELECT L1.LINE_ID FROM SO_LINES_ALL L1 ');
Process_Buffer('s', ' SELECT L2.LINE_ID FROM SO_LINES_ALL L2 ');
-- This statement selects service and non-shippable standard lines
--
non_ship_SQL := '';
Process_Buffer('n', ' SELECT ');
Process_Buffer('n', ' ( SELECT XX.LINE_ID FROM ');
Process_Buffer('n', ' SELECT ''shippable component for a model'' ');
Process_Buffer('n', ' SELECT ''shippable component for model components'' ');
-- select non-ship lines from orders in departure
Process_Buffer('n', ' AND EXISTS (SELECT LD1.LINE_DETAIL_ID ');
-- select non-ship lines from orders in delivery
Process_Buffer('n', ' AND EXISTS (SELECT LD1.LINE_DETAIL_ID ');
Process_Buffer('sreq',' UPDATE SO_LINES_ALL L ');
Process_Buffer('sreq',' (SELECT L2.LINE_ID ');
Process_Buffer('sreq',' SELECT L3.LINE_ID ');
Process_Buffer('sreq', ' AND EXISTS (SELECT ''a line detail'' ');