[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT COUNT(*)
INTO DUMMY
FROM SO_LINES
WHERE HEADER_ID = V_HEADER_ID
AND S29 IN (16, 17);
SELECT NVL(SUM(NVL(MTLSRR.RECEIVED_QUANTITY,0)),0)
INTO RECEIVED_QUANTITY
FROM MTL_SO_RMA_RECEIPTS MTLSRR,
MTL_SO_RMA_INTERFACE MTLSRI
WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
AND MTLSRI.RMA_LINE_ID IN
(SELECT LINE_ID
FROM SO_LINES
WHERE HEADER_ID = V_HEADER_ID
AND S29 = 14);
SELECT COUNT(*)
INTO DUMMY
FROM SO_LINE_DETAILS SLD,
SO_LINES SOL
WHERE SOL.HEADER_ID = V_HEADER_ID
AND SLD.LINE_ID = SOL.LINE_ID
AND SLD.SCHEDULE_STATUS_CODE = 'SUPPLY_RESERVED'
AND SLD.WIP_COMPLETED_QUANTITY < SLD.QUANTITY;
procedure UPDATE_HEADER_INFO(
V_HEADER_ID IN NUMBER
, V_ORDER_CATEGORY IN VARCHAR2
, V_CANCEL_COMMENT IN LONG
, V_CANCEL_CODE IN VARCHAR2
, V_LAST_UPDATED_BY IN NUMBER
, V_LAST_UPDATE_LOGIN IN NUMBER
, V_SOURCE_CODE IN VARCHAR2
, V_PRINT_ERR_MSG IN NUMBER
, V_RESULT OUT NUMBER)
is
x BOOLEAN;
UPDATE SO_HEADERS
SET S1 = DECODE(S1,18,'',S1),
S1_DATE = DECODE(S1,18,'',S1_DATE),
S2 = DECODE(S2,18,'',S2),
S2_DATE = DECODE(S2,18,'',S2_DATE),
S3 = DECODE(S3,18,'',S3),
S3_DATE = DECODE(S3,18,'',S3_DATE),
S4 = 11,
S4_DATE = SYSDATE,
S5 = DECODE(S5,18,'',S5),
S5_DATE = DECODE(S5,18,'',S5_DATE),
S6 = DECODE(S6,18,'',S6),
S6_DATE = DECODE(S6,18,'',S6_DATE),
S7 = DECODE(S7,18,'',S7),
S7_DATE = DECODE(S7,18,'',S7_DATE),
S8 = DECODE(S8,18,'',S8),
S8_DATE = DECODE(S8,18,'',S8_DATE),
S9 = DECODE(S9,18,'',S9),
S9_DATE = DECODE(S9,18,'',S9_DATE),
S10 = DECODE(S10,18,'',S10),
S10_DATE = DECODE(S10,18,'',S10_DATE),
S11 = DECODE(S11,18,'',S11),
S11_DATE = DECODE(S11,18,'',S11_DATE),
S12 = DECODE(S12,18,'',S12),
S12_DATE = DECODE(S12,18,'',S12_DATE),
S13 = DECODE(S13,18,'',S13),
S13_DATE = DECODE(S13,18,'',S13_DATE),
S14 = DECODE(S14,18,'',S14),
S14_DATE = DECODE(S14,18,'',S14_DATE),
S15 = DECODE(S15,18,'',S15),
S15_DATE = DECODE(S15,18,'',S15_DATE),
S16 = DECODE(S16,18,'',S16),
S16_DATE = DECODE(S16,18,'',S16_DATE),
S17 = DECODE(S17,18,'',S17),
S17_DATE = DECODE(S17,18,'',S17_DATE),
S18 = DECODE(S18,18,'',S18),
S18_DATE = DECODE(S18,18,'',S18_DATE),
S19 = DECODE(S19,18,'',S19),
S19_DATE = DECODE(S19,18,'',S19_DATE),
S20 = DECODE(S20,18,'',S20),
S20_DATE = DECODE(S20,18,'',S20_DATE),
S21 = DECODE(S21,18,'',S21),
S21_DATE = DECODE(S21,18,'',S21_DATE),
S22 = DECODE(S22,18,'',S22),
S22_DATE = DECODE(S22,18,'',S22_DATE),
S23 = DECODE(S23,18,'',S23),
S23_DATE = DECODE(S23,18,'',S23_DATE),
S24 = DECODE(S24,18,'',S24),
S24_DATE = DECODE(S24,18,'',S24_DATE),
S25 = DECODE(S25,18,'',S25),
S25_DATE = DECODE(S25,18,'',S25_DATE),
S26 = DECODE(S26,18,'',S26),
S26_DATE = DECODE(S26,18,'',S26_DATE),
S27 = DECODE(S27,18,'',S27),
S27_DATE = DECODE(S27,18,'',S27_DATE),
S28 = DECODE(S28,18,'',S28),
S28_DATE = DECODE(S28,18,'',S28_DATE),
S29 = DECODE(S29,18,'',S29),
S29_DATE = DECODE(S29,18,'',S29_DATE),
S30 = DECODE(S30,18,'',S30),
S30_DATE = DECODE(S30,18,'',S30_DATE)
WHERE HEADER_ID = V_HEADER_ID;
UPDATE SO_LINES
SET LAST_UPDATED_BY = V_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE = SYSDATE,
OPEN_FLAG = NULL,
CANCELLED_QUANTITY = ORDERED_QUANTITY,
S1 = DECODE(S1,18,8,S1),
S1_DATE = DECODE(S1,18,sysdate,S1_DATE),
S2 = DECODE(S2,18,8,S2),
S2_DATE = DECODE(S2,18,sysdate,S2_DATE),
S3 = DECODE(S3,18,8,S3),
S3_DATE = DECODE(S3,18,sysdate,S3_DATE),
S4 = DECODE(S4,18,8,S4),
S4_DATE = DECODE(S4,18,sysdate,S4_DATE),
S5 = DECODE(S5,18,8,S5),
S5_DATE = DECODE(S5,18,sysdate,S5_DATE),
S6 = DECODE(S6,18,8,S6),
S6_DATE = DECODE(S6,18,sysdate,S6_DATE),
S7 = DECODE(S7,18,8,S7),
S7_DATE = DECODE(S7,18,sysdate,S7_DATE),
S8 = DECODE(S8,18,8,S8),
S8_DATE = DECODE(S8,18,sysdate,S8_DATE),
S9 = 11,
S9_DATE = SYSDATE,
S10 = DECODE(S10,18,8,S10),
S10_DATE = DECODE(S10,18,sysdate,S10_DATE),
S11 = DECODE(S11,18,8,S11),
S11_DATE = DECODE(S11,18,sysdate,S11_DATE),
S12 = DECODE(S12,18,8,S12),
S12_DATE = DECODE(S12,18,sysdate,S12_DATE),
S13 = DECODE(S13,18,8,S13),
S13_DATE = DECODE(S13,18,sysdate,S13_DATE),
S14 = DECODE(S14,18,8,S14),
S14_DATE = DECODE(S14,18,sysdate,S14_DATE),
S15 = DECODE(S15,18,8,S15),
S15_DATE = DECODE(S15,18,sysdate,S15_DATE),
S16 = DECODE(S16,18,8,S16),
S16_DATE = DECODE(S16,18,sysdate,S16_DATE),
S17 = DECODE(S17,18,8,S17),
S17_DATE = DECODE(S17,18,sysdate,S17_DATE),
S18 = DECODE(S18,18,8,S18),
S18_DATE = DECODE(S18,18,sysdate,S18_DATE),
S19 = DECODE(S19,18,8,S19),
S19_DATE = DECODE(S19,18,sysdate,S19_DATE),
S20 = DECODE(S20,18,8,S20),
S20_DATE = DECODE(S20,18,sysdate,S20_DATE),
S21 = DECODE(S21,18,8,S21),
S21_DATE = DECODE(S21,18,sysdate,S21_DATE),
S22 = DECODE(S22,18,8,S22),
S22_DATE = DECODE(S22,18,sysdate,S22_DATE),
S23 = DECODE(S23,18,8,S23),
S23_DATE = DECODE(S23,18,sysdate,S23_DATE),
S24 = DECODE(S24,18,8,S24),
S24_DATE = DECODE(S24,18,sysdate,S24_DATE),
S25 = DECODE(S25,18,8,S25),
S25_DATE = DECODE(S25,18,sysdate,S25_DATE),
S26 = DECODE(S26,18,8,S26),
S26_DATE = DECODE(S26,18,sysdate,S26_DATE),
S27 = DECODE(S27,18,8,S27),
S27_DATE = DECODE(S27,18,sysdate,S27_DATE),
S28 = DECODE(S28,18,8,S28),
S28_DATE = DECODE(S28,18,sysdate,S28_DATE),
S29 = DECODE(S29,18,8,S29),
S29_DATE = DECODE(S29,18,sysdate,S29_DATE),
S30 = DECODE(S30,18,8,S30),
S30_DATE = DECODE(S30,18,sysdate,S30_DATE)
WHERE HEADER_ID = V_HEADER_ID;
SELECT LINE_ID
FROM SO_LINES
WHERE HEADER_ID = V_HEADER_ID
AND S29 IN (14,16,17);
UPDATE MTL_SO_RMA_INTERFACE MSRI
SET MSRI.QUANTITY = 0,
MSRI.CLOSED_FLAG = 'Y'
WHERE MSRI.RMA_LINE_ID = rma1rec.LINE_ID
AND MSRI.SOURCE_CODE = V_SOURCE_CODE
AND MSRI.IN_USE_FLAG IS NULL;
INSERT INTO SO_ORDER_CANCELLATIONS
(HEADER_ID, CANCEL_CODE,
CANCELLED_BY, CANCEL_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN, CREATION_DATE,
CREATED_BY, CANCEL_COMMENT )
VALUES(V_HEADER_ID, V_CANCEL_CODE,
v_current_user, SYSDATE,
V_LAST_UPDATED_BY, SYSDATE,
V_LAST_UPDATE_LOGIN, SYSDATE,
v_current_user, V_CANCEL_COMMENT );
DELETE FROM SO_LINE_DETAILS
WHERE LINE_ID IN (
SELECT LINE_ID
FROM SO_LINES
WHERE HEADER_ID = V_HEADER_ID)
AND RELEASED_FLAG = 'N';
DELETE FROM SO_LINE_SERVICE_DETAILS
WHERE LINE_ID IN (
SELECT LINE_ID
FROM SO_LINES
WHERE HEADER_ID = V_HEADER_ID);
END UPDATE_HEADER_INFO;
SELECT COUNT(*)
INTO DUMMY
FROM CS_CP_SERVICES SER,
CS_CUSTOMER_PRODUCTS CPS
WHERE SER.CUSTOMER_PRODUCT_ID = CPS.CUSTOMER_PRODUCT_ID
AND CPS.ORIGINAL_ORDER_LINE_ID IN
(SELECT LINE_ID FROM SO_LINES
WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
AND NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE ||'%'
AND V_SUBTREE_EXISTS = 1
UNION
SELECT LINE_ID
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID)
AND SYSDATE BETWEEN SER.START_DATE_ACTIVE AND SER.END_DATE_ACTIVE;
SELECT COUNT(*)
INTO DUMMY2
FROM CS_CP_SERVICES SER,
CS_CP_SERVICE_TRANSACTIONS TRX
WHERE TRX.SERVICE_ORDER_LINE_ID = V_LINE_ID
AND TRX.CP_SERVICE_ID = SER.CP_SERVICE_ID
AND SYSDATE BETWEEN SER.START_DATE_ACTIVE AND SER.END_DATE_ACTIVE;
SELECT COUNT(*)
INTO DUMMY3
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID
AND ATO_FLAG = 'Y'
AND ATO_LINE_ID IS NOT NULL
AND ITEM_TYPE_CODE <> 'SERVICE'
AND LINE_TYPE_CODE <> 'RETURN'
AND NVL(S2,18) <> 18;
SELECT COUNT(*)
INTO DUMMY
FROM SO_LINE_DETAILS
WHERE LINE_ID = V_LOOP_LINE_ID
AND CONFIGURATION_ITEM_FLAG = 'Y';
SELECT CEIL(NVL(MAX(SUM(NVL(MTLSRR.RECEIVED_QUANTITY,0)) *
((V_ORDER_QTY - NVL(V_CANCELLED_QTY,0))
/ MAX(MTLSRI.QUANTITY))),0))
INTO RECEIVED_QTY
FROM MTL_SO_RMA_RECEIPTS MTLSRR,
MTL_SO_RMA_INTERFACE MTLSRI
WHERE MTLSRR.RMA_INTERFACE_ID = MTLSRI.RMA_INTERFACE_ID
AND MTLSRI.RMA_LINE_ID = V_LINE_ID
GROUP BY MTLSRI.INVENTORY_ITEM_ID,
MTLSRI.COMPONENT_SEQUENCE_ID;
SELECT ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)
INTO TEMP_QTY
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID;
SELECT (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0))
INTO TEMP_QTY
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID;
SELECT COUNT(*)
INTO DUMMY
FROM SO_LINE_DETAILS
WHERE LINE_ID = V_LOOP_LINE_ID
AND SHIPPABLE_FLAG = 'Y'
AND INCLUDED_ITEM_FLAG = 'N';
select nvl(sum(quantity),0)
into LD_QTY
from so_line_details
where nvl(released_flag,'N') = 'N'
and line_id = V_LOOP_LINE_ID
and included_item_flag = 'N'
and nvl(schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
select nvl(sum(pld.requested_quantity),0)
into PD_QTY
from so_picking_lines pl, so_picking_line_details pld
where pl.picking_line_id = pld.picking_line_id
and pl.picking_header_id = 0
and pl.order_line_id = V_LOOP_LINE_ID
and nvl(pld.released_flag,'N') = 'N'
and pl.included_item_flag = 'N'
and nvl(pld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
select distinct inventory_item_id, component_ratio
from so_line_details
where line_id = V_LOOP_LINE_ID
and included_item_flag = 'Y'
order by inventory_item_id;
select nvl(sum(sld.quantity),0)
into LD_QTY
from so_line_details sld
where nvl(sld.released_flag,'N') = 'N'
and sld.line_id = V_LOOP_LINE_ID
and sld.inventory_item_id = TEMP_ITEM_ID
and nvl(sld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
select nvl(sum(pld.requested_quantity),0)
into PD_QTY
from so_picking_lines pl,
so_picking_line_details pld
where pl.picking_line_id = pld.picking_line_id
and pl.picking_header_id = 0
and pl.order_line_id = V_LOOP_LINE_ID
and pl.inventory_item_id = TEMP_ITEM_ID
and nvl(pld.released_flag,'N') = 'N'
and nvl(pld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
** Following select added to calculate Qty for Non Shippable
** Included Items as above query will give PD_QTY=0 for such
** cases.
*/
select nvl(sum(sld.quantity),0) + PD_QTY
into PD_QTY
from so_line_details sld
where nvl(sld.released_flag,'N') = 'Y'
and nvl(sld.shippable_flag,'N') = 'N'
and sld.line_id = V_LOOP_LINE_ID
and sld.inventory_item_id = TEMP_ITEM_ID
and nvl(sld.schedule_status_code,'NULL') <> 'SUPPLY RESERVED';
SELECT NVL(SUM(QUANTITY),0)
INTO LD_QTY
FROM SO_LINE_DETAILS
WHERE LINE_ID = V_ATO_LOOP_LINE_ID
AND CONFIGURATION_ITEM_FLAG = 'Y'
AND RELEASED_FLAG = 'N'
AND NVL(SCHEDULE_STATUS_CODE,'NULL') <> 'SUPPLY RESERVED';
SELECT NVL(SUM(PLD.REQUESTED_QUANTITY),0)
INTO PD_QTY
FROM SO_PICKING_LINE_DETAILS PLD, SO_PICKING_LINES PL
WHERE PL.ORDER_LINE_ID = V_ATO_LOOP_LINE_ID
AND PL.PICKING_HEADER_ID = 0
AND PL.PICKING_LINE_ID = PLD.PICKING_LINE_ID
AND PLD.RELEASED_FLAG = 'N'
AND NVL(PLD.SCHEDULE_STATUS_CODE,'NULL') <> 'SUPPLY RESERVED';
SELECT (L.ordered_quantity - nvl(L.cancelled_quantity,0)) /
(M.ordered_quantity - nvl(M.cancelled_quantity,0))
INTO TOTAL_UNRELEASED
FROM SO_LINES L, SO_LINES M
WHERE L.LINE_ID = V_LOOP_LINE_ID
AND M.LINE_ID = V_ATO_LOOP_LINE_ID;
procedure UPDATE_LINE_INFO(
V_LINE_ID IN NUMBER
, V_REQUESTED_CANCEL_QTY IN NUMBER
, V_CANCEL_COMMENT IN LONG
, V_CANCEL_CODE IN VARCHAR2
, V_FULL IN NUMBER
, V_OPTION_FLAG IN NUMBER
, V_PARENT_LINE_ID IN NUMBER
, V_LINE_TYPE_CODE IN VARCHAR2
, V_SHIPMENT_SCHEDULE_LINE_ID IN NUMBER
, V_SUBTREE_EXISTS IN NUMBER
, V_COMPONENT_CODE IN VARCHAR2
, V_REAL_PARENT_LINE_ID IN NUMBER
, V_LAST_UPDATED_BY IN NUMBER
, V_LAST_UPDATE_LOGIN IN NUMBER
, V_STATUS IN VARCHAR2
, V_RESULT OUT NUMBER
)
IS
CANCEL_QTY_TMP NUMBER := 0;
UPDATE SO_LINES
SET LAST_UPDATED_BY = DECODE(LAST_UPDATED_BY, 0, 1, 1,
DECODE(V_LAST_UPDATED_BY,1,0,V_LAST_UPDATED_BY),
V_LAST_UPDATED_BY, 0, V_LAST_UPDATED_BY)
WHERE (LINE_ID = V_PARENT_LINE_ID
OR PARENT_LINE_ID = V_PARENT_LINE_ID);
SELECT (ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)
- GREATEST (NVL(SHIPPED_QUANTITY,0),
NVL(INVOICED_QUANTITY,0)))
INTO CANCEL_QTY_TMP
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID;
UPDATE SO_LINES L
SET CANCELLED_QUANTITY =
(SELECT NVL(L.CANCELLED_QUANTITY,0) +
(CANCEL_QTY_TMP *
(L.ORDERED_QUANTITY-NVL(L.CANCELLED_QUANTITY,0))/
(L2.ORDERED_QUANTITY-NVL(L2.CANCELLED_QUANTITY,0)))
FROM SO_LINES L2
WHERE L2.LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID)
WHERE L.LINE_ID IN
(select line_id
from so_lines
where parent_line_id = V_SHIPMENT_SCHEDULE_LINE_ID
and nvl(component_code,'0') like V_COMPONENT_CODE || '%'
and V_SUBTREE_EXISTS = 1
union
select line_id
from so_lines
where line_id = V_SHIPMENT_SCHEDULE_LINE_ID);
UPDATE SO_LINES
SET SOURCE_LINE_ID = NULL
WHERE LINE_ID = V_LINE_ID;
SELECT LINE_ID, HEADER_ID, ORDERED_QUANTITY, CANCELLED_QUANTITY
,SHIPPED_QUANTITY,INVOICED_QUANTITY
FROM SO_LINES
WHERE LINE_ID IN
(select line_id
from so_lines
where parent_line_id = V_REAL_PARENT_LINE_ID
and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
OR COMPONENT_CODE IS NULL)
and V_SUBTREE_EXISTS = 1
union
select line_id
from so_lines
where line_id = V_LINE_ID
or service_parent_line_id = V_LINE_ID);
UPDATE SO_LINES SOL
SET CANCELLED_QUANTITY = ORDERED_QUANTITY
- GREATEST (NVL(SHIPPED_QUANTITY,0),
NVL(INVOICED_QUANTITY,0))
WHERE SOL.LINE_ID = full1rec.LINE_ID;
INSERT INTO SO_ORDER_CANCELLATIONS
(LINE_ID, HEADER_ID, CANCEL_CODE, CANCELLED_BY,
CANCEL_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE,
CANCEL_COMMENT, CANCELLED_QUANTITY, STATUS,
CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN )
VALUES
(full1rec.LINE_ID, full1rec.HEADER_ID, V_CANCEL_CODE,
v_current_user,
SYSDATE, V_LAST_UPDATED_BY, SYSDATE, V_CANCEL_COMMENT,
(full1rec.ORDERED_QUANTITY -
NVL(full1rec.CANCELLED_QUANTITY,0) - GREATEST
(NVL(full1rec.SHIPPED_QUANTITY,0),
NVL(full1rec.INVOICED_QUANTITY,0))), V_STATUS,
SYSDATE,
v_current_user, V_LAST_UPDATE_LOGIN);
UPDATE SO_LINES L
SET CANCELLED_QUANTITY =
(SELECT NVL(L.CANCELLED_QUANTITY,0) +
(V_REQUESTED_CANCEL_QTY *
(L.ORDERED_QUANTITY-NVL(L.CANCELLED_QUANTITY,0))/
(L2.ORDERED_QUANTITY-NVL(L2.CANCELLED_QUANTITY,0)))
FROM SO_LINES L2
WHERE L2.LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID)
WHERE L.LINE_ID IN
(select line_id
from so_lines
where parent_line_id = V_SHIPMENT_SCHEDULE_LINE_ID
and nvl(component_code,'0') like V_COMPONENT_CODE || '%'
and V_SUBTREE_EXISTS = 1
union
select line_id
from so_lines
where line_id = V_SHIPMENT_SCHEDULE_LINE_ID);
UPDATE SO_LINES
SET SOURCE_LINE_ID = NULL
WHERE LINE_ID = V_LINE_ID;
SELECT ORDERED_QUANTITY, CANCELLED_QUANTITY
INTO L_CURRENT_ORDERED_QTY, L_CURRENT_CANCELLED_QTY
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID;
SELECT LINE_ID, HEADER_ID, ORDERED_QUANTITY
,CANCELLED_QUANTITY
FROM SO_LINES
WHERE LINE_ID IN
(SELECT LINE_ID
FROM SO_LINES
WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
AND (NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE || '%'
OR COMPONENT_CODE IS NULL)
AND V_SUBTREE_EXISTS = 1
UNION
SELECT LINE_ID
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID
OR SERVICE_PARENT_LINE_ID = V_LINE_ID);
UPDATE SO_LINES L
SET CANCELLED_QUANTITY =
NVL(not_full_rec.CANCELLED_QUANTITY,0)+(V_REQUESTED_CANCEL_QTY *
(not_full_rec.ORDERED_QUANTITY - NVL(not_full_rec.CANCELLED_QUANTITY,0))/
(L_CURRENT_ORDERED_QTY - NVL(L_CURRENT_CANCELLED_QTY,0)))
WHERE L.LINE_ID = not_full_rec.LINE_ID;
INSERT INTO SO_ORDER_CANCELLATIONS
( LINE_ID, HEADER_ID,
CANCEL_CODE, CANCELLED_BY,
CANCEL_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, CANCEL_COMMENT,
CANCELLED_QUANTITY, STATUS,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN )
SELECT L.LINE_ID, L.HEADER_ID,
V_CANCEL_CODE, v_current_user,
SYSDATE, V_LAST_UPDATED_BY,
SYSDATE, V_CANCEL_COMMENT,
NVL(L.CANCELLED_QUANTITY,0) - NVL(SUM (SOC.CANCELLED_QUANTITY),0),
V_STATUS, SYSDATE, v_current_user,
V_LAST_UPDATE_LOGIN
FROM SO_LINES L, SO_ORDER_CANCELLATIONS SOC
WHERE L.LINE_ID IN
(SELECT LINE_ID
FROM SO_LINES
WHERE PARENT_LINE_ID = V_REAL_PARENT_LINE_ID
AND (NVL(COMPONENT_CODE,'0') LIKE V_COMPONENT_CODE || '%'
OR COMPONENT_CODE IS NULL)
AND V_SUBTREE_EXISTS = 1
UNION
SELECT LINE_ID
FROM SO_LINES
WHERE LINE_ID = V_LINE_ID
OR SERVICE_PARENT_LINE_ID = V_LINE_ID)
AND L.LINE_ID = SOC.LINE_ID(+)
HAVING NVL(L.CANCELLED_QUANTITY,0) <> NVL(SUM (SOC.CANCELLED_QUANTITY),0)
GROUP BY L.LINE_ID, L.HEADER_ID, L.CANCELLED_QUANTITY;
** Update quantity_to_invoice for non-shippable items on line Cancellation.
*/
UPDATE SO_LINES
SET QUANTITY_TO_INVOICE =
DECODE(NVL(QUANTITY_TO_INVOICE,0),
0, QUANTITY_TO_INVOICE,
DECODE(NVL(SHIPPED_QUANTITY,0),
0,(ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0)),
SHIPPED_QUANTITY))
WHERE (LINE_ID = V_LINE_ID
OR PARENT_LINE_ID = V_LINE_ID)
AND (S4 + 0 IN (5, 7, 22, 8)
OR S29 + 0 IN (14, 16, 8))
AND EXISTS
(SELECT 'NON SHIPPABLE ITEM'
FROM MTL_SYSTEM_ITEMS MSI
WHERE MSI.ORGANIZATION_ID = l_org_id
AND MSI.INVENTORY_ITEM_ID = SO_LINES.INVENTORY_ITEM_ID
AND MSI.SHIPPABLE_ITEM_FLAG = 'N');
UPDATE SO_LINES SET OPEN_FLAG =
DECODE(nvl(ORDERED_QUANTITY,0) - NVL(CANCELLED_QUANTITY,0)
,0,'',OPEN_FLAG)
WHERE LINE_ID IN
(select line_id
from so_lines
where parent_line_id = V_REAL_PARENT_LINE_ID
and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
or component_code is null)
and V_SUBTREE_EXISTS = 1
union
select line_id
from so_lines
where line_id = V_LINE_ID
or service_parent_line_id = V_LINE_ID);
UPDATE SO_LINES
SET LAST_UPDATED_BY = V_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE = SYSDATE,
S1 = DECODE(OPEN_FLAG,'',DECODE(S1,18,8,S1),S1),
S1_DATE = DECODE(OPEN_FLAG,'',DECODE(S1,18,sysdate,S1_DATE),S1_DATE),
S2 = DECODE(OPEN_FLAG,'',DECODE(S2,18,8,S2),S2),
S2_DATE = DECODE(OPEN_FLAG,'',DECODE(S2,18,sysdate,S2_DATE),S2_DATE),
S3 = DECODE(OPEN_FLAG,'',DECODE(S3,18,8,S3),S3),
S3_DATE = DECODE(OPEN_FLAG,'',DECODE(S3,18,sysdate,S3_DATE),S3_DATE),
S4 = DECODE(OPEN_FLAG,'',DECODE(S4,18,8,S4),S4),
S4_DATE = DECODE(OPEN_FLAG,'',DECODE(S4,18,sysdate,S4_DATE),S4_DATE),
S5 = DECODE(OPEN_FLAG,'',DECODE(S5,18,8,S5),S5),
S5_DATE = DECODE(OPEN_FLAG,'',DECODE(S5,18,sysdate,S5_DATE),S5_DATE),
S6 = DECODE(OPEN_FLAG,'',DECODE(S6,18,8,S6),S6),
S6_DATE = DECODE(OPEN_FLAG,'',DECODE(S6,18,sysdate,S6_DATE),S6_DATE),
S7 = DECODE(OPEN_FLAG,'',DECODE(S7,18,8,S7),S7),
S7_DATE = DECODE(OPEN_FLAG,'',DECODE(S7,18,sysdate,S7_DATE),S7_DATE),
S8 = DECODE(OPEN_FLAG,'',DECODE(S8,18,8,S8),S8),
S8_DATE = DECODE(OPEN_FLAG,'',DECODE(S8,18,sysdate,S8_DATE),S8_DATE),
S9 = DECODE(OPEN_FLAG,'',11,5),
S9_DATE = sysdate,
S10 = DECODE(OPEN_FLAG,'',DECODE(S10,18,8,S10),S10),
S10_DATE= DECODE(OPEN_FLAG,'',DECODE(S10,18,sysdate,S10_DATE),S10_DATE),
S11 = DECODE(OPEN_FLAG,'',DECODE(S11,18,8,S11),S11),
S11_DATE= DECODE(OPEN_FLAG,'',DECODE(S11,18,sysdate,S11_DATE),S11_DATE),
S12 = DECODE(OPEN_FLAG,'',DECODE(S12,18,8,S12),S12),
S12_DATE= DECODE(OPEN_FLAG,'',DECODE(S12,18,sysdate,S12_DATE),S12_DATE),
S13 = DECODE(OPEN_FLAG,'',DECODE(S13,18,8,S13),S13),
S13_DATE= DECODE(OPEN_FLAG,'',DECODE(S13,18,sysdate,S13_DATE),S13_DATE),
S14 = DECODE(OPEN_FLAG,'',DECODE(S14,18,8,S14),S14),
S14_DATE= DECODE(OPEN_FLAG,'',DECODE(S14,18,sysdate,S14_DATE),S14_DATE),
S15 = DECODE(OPEN_FLAG,'',DECODE(S15,18,8,S15),S15),
S15_DATE= DECODE(OPEN_FLAG,'',DECODE(S15,18,sysdate,S15_DATE),S15_DATE),
S16 = DECODE(OPEN_FLAG,'',DECODE(S16,18,8,S16),S16),
S16_DATE= DECODE(OPEN_FLAG,'',DECODE(S16,18,sysdate,S16_DATE),S16_DATE),
S17 = DECODE(OPEN_FLAG,'',DECODE(S17,18,8,S17),S17),
S17_DATE= DECODE(OPEN_FLAG,'',DECODE(S17,18,sysdate,S17_DATE),S17_DATE),
S18 = DECODE(OPEN_FLAG,'',DECODE(S18,18,8,S18),S18),
S18_DATE= DECODE(OPEN_FLAG,'',DECODE(S18,18,sysdate,S18_DATE),S18_DATE),
S19 = DECODE(OPEN_FLAG,'',DECODE(S19,18,8,S19),S19),
S19_DATE= DECODE(OPEN_FLAG,'',DECODE(S19,18,sysdate,S19_DATE),S19_DATE),
S20 = DECODE(OPEN_FLAG,'',DECODE(S20,18,8,S20),S20),
S20_DATE= DECODE(OPEN_FLAG,'',DECODE(S20,18,sysdate,S20_DATE),S20_DATE),
S21 = DECODE(OPEN_FLAG,'',DECODE(S21,18,8,S21),S21),
S21_DATE= DECODE(OPEN_FLAG,'',DECODE(S21,18,sysdate,S21_DATE),S21_DATE),
S22 = DECODE(OPEN_FLAG,'',DECODE(S22,18,8,S22),S22),
S22_DATE= DECODE(OPEN_FLAG,'',DECODE(S22,18,sysdate,S22_DATE),S22_DATE),
S23 = DECODE(OPEN_FLAG,'',DECODE(S23,18,8,S23),S23),
S23_DATE= DECODE(OPEN_FLAG,'',DECODE(S23,18,sysdate,S23_DATE),S23_DATE),
S24 = DECODE(OPEN_FLAG,'',DECODE(S24,18,8,S24),S24),
S24_DATE= DECODE(OPEN_FLAG,'',DECODE(S24,18,sysdate,S24_DATE),S24_DATE),
S25 = DECODE(OPEN_FLAG,'',DECODE(S25,18,8,S25),S25),
S25_DATE= DECODE(OPEN_FLAG,'',DECODE(S25,18,sysdate,S25_DATE),S25_DATE),
S26 = DECODE(OPEN_FLAG,'',DECODE(S26,18,8,S26),S26),
S26_DATE= DECODE(OPEN_FLAG,'',DECODE(S26,18,sysdate,S26_DATE),S26_DATE),
S27 = DECODE(OPEN_FLAG,'',DECODE(S27,18,8,S27),S27),
S27_DATE= DECODE(OPEN_FLAG,'',DECODE(S27,18,sysdate,S27_DATE),S27_DATE),
S28 = DECODE(OPEN_FLAG,'',DECODE(S28,18,8,S28),S28),
S28_DATE= DECODE(OPEN_FLAG,'',DECODE(S28,18,sysdate,S28_DATE),S28_DATE),
S29 = DECODE(OPEN_FLAG,'',DECODE(S29,18,8,S29),S29),
S29_DATE= DECODE(OPEN_FLAG,'',DECODE(S29,18,sysdate,S29_DATE),S29_DATE),
S30 = DECODE(OPEN_FLAG,'',DECODE(S30,18,8,S30),S30),
S30_DATE= DECODE(OPEN_FLAG,'',DECODE(S30,18,sysdate,S30_DATE),S30_DATE)
WHERE LINE_ID IN
(select line_id from so_lines
where parent_line_id = V_REAL_PARENT_LINE_ID
and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
or component_code is null)
and V_SUBTREE_EXISTS = 1
union
select line_id
from so_lines
where line_id = V_LINE_ID
or service_parent_line_id = V_LINE_ID);
UPDATE SO_LINES
SET S3 = DECODE(S3,18,8,S3)
WHERE LINE_ID IN
(select line_id from so_lines
where parent_line_id = V_REAL_PARENT_LINE_ID
and (nvl(component_code,'0') like V_COMPONENT_CODE || '%'
or component_code is null)
and V_SUBTREE_EXISTS = 1
union
select line_id
from so_lines
where line_id = V_LINE_ID
or service_parent_line_id = V_LINE_ID)
AND NOT EXISTS
(SELECT 'BACKORDERED PICKING LINES'
FROM SO_PICKING_LINES
WHERE ORDER_LINE_ID = SO_LINES.LINE_ID
AND PICKING_HEADER_ID = 0
AND ORIGINAL_REQUESTED_QUANTITY > NVL(CANCELLED_QUANTITY,0));
END UPDATE_LINE_INFO;
procedure UPDATE_MODEL_INFO(
V_LINE_ID IN NUMBER
, V_REQUESTED_CANCEL_QTY IN NUMBER
, V_CANCEL_COMMENT IN LONG
, V_CANCEL_CODE IN VARCHAR2
, V_STATUS IN VARCHAR2
, V_LAST_UPDATED_BY IN NUMBER
, V_LAST_UPDATE_LOGIN IN NUMBER
, V_HEADER_ID IN NUMBER
, V_FULL IN NUMBER
, V_RESULT OUT NUMBER
)
IS
DUMMY NUMBER;
SELECT COUNT(*)
INTO DUMMY
FROM SO_PICKING_LINES L,
SO_PICKING_HEADERS H
WHERE L.ORDER_LINE_ID = V_LINE_ID
AND L.PICKING_HEADER_ID = H.PICKING_HEADER_ID
AND H.STATUS_CODE IN ('OPEN' ,'PENDING', 'IN PROGRESS');
UPDATE SO_LINES SET
CANCELLED_QUANTITY =
V_REQUESTED_CANCEL_QTY + NVL(CANCELLED_QUANTITY,0)
WHERE LINE_ID = V_LINE_ID;
UPDATE SO_LINES SET
OPEN_FLAG =
DECODE ((nvl(ORDERED_QUANTITY,0) - NVL(CANCELLED_QUANTITY,0)),
0, '', OPEN_FLAG)
WHERE LINE_ID = V_LINE_ID;
UPDATE SO_LINES SET
LAST_UPDATED_BY = V_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = V_LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE = SYSDATE,
S1 = DECODE(OPEN_FLAG,'',DECODE(S1,18,8,S1),S1),
S2 = DECODE(OPEN_FLAG,'',DECODE(S2,18,8,S2),S2),
S3 = DECODE(OPEN_FLAG,'',DECODE(S3,18,8,S3),S3),
S4 = DECODE(OPEN_FLAG,'',DECODE(S4,18,8,S4),S4),
S5 = DECODE(OPEN_FLAG,'',DECODE(S5,18,8,S5),S5),
S6 = DECODE(OPEN_FLAG,'',DECODE(S6,18,8,S6),S6),
S7 = DECODE(OPEN_FLAG,'',DECODE(S7,18,8,S7),S7),
S8 = DECODE(OPEN_FLAG,'',DECODE(S8,18,8,S8),S8),
S9 = DECODE(OPEN_FLAG,'',11,5),
S10 = DECODE(OPEN_FLAG,'',DECODE(S10,18,8,S10),S10),
S11 = DECODE(OPEN_FLAG,'',DECODE(S11,18,8,S11),S11),
S12 = DECODE(OPEN_FLAG,'',DECODE(S12,18,8,S12),S12),
S13 = DECODE(OPEN_FLAG,'',DECODE(S13,18,8,S13),S13),
S14 = DECODE(OPEN_FLAG,'',DECODE(S14,18,8,S14),S14),
S15 = DECODE(OPEN_FLAG,'',DECODE(S15,18,8,S15),S15),
S16 = DECODE(OPEN_FLAG,'',DECODE(S16,18,8,S16),S16),
S17 = DECODE(OPEN_FLAG,'',DECODE(S17,18,8,S17),S17),
S18 = DECODE(OPEN_FLAG,'',DECODE(S18,18,8,S18),S18),
S19 = DECODE(OPEN_FLAG,'',DECODE(S19,18,8,S19),S19),
S20 = DECODE(OPEN_FLAG,'',DECODE(S20,18,8,S20),S20),
S21 = DECODE(OPEN_FLAG,'',DECODE(S21,18,8,S21),S21),
S22 = DECODE(OPEN_FLAG,'',DECODE(S22,18,8,S22),S22),
S23 = DECODE(OPEN_FLAG,'',DECODE(S23,18,8,S23),S23),
S24 = DECODE(OPEN_FLAG,'',DECODE(S24,18,8,S24),S24),
S25 = DECODE(OPEN_FLAG,'',DECODE(S25,18,8,S25),S25),
S26 = DECODE(OPEN_FLAG,'',DECODE(S26,18,8,S26),S26),
S27 = DECODE(OPEN_FLAG,'',DECODE(S27,18,8,S27),S27),
S28 = DECODE(OPEN_FLAG,'',DECODE(S28,18,8,S28),S28),
S29 = DECODE(OPEN_FLAG,'',DECODE(S29,18,8,S29),S29),
S30 = DECODE(OPEN_FLAG,'',DECODE(S30,18,8,S30),S30)
WHERE LINE_ID = V_LINE_ID;
SELECT COUNT(*)
INTO DUMMY
FROM SO_PICKING_LINES
WHERE PICKING_HEADER_ID = 0
AND ORIGINAL_REQUESTED_QUANTITY > NVL(CANCELLED_QUANTITY,0)
AND ORDER_LINE_ID = V_LINE_ID;
UPDATE SO_LINES
SET S3 = DECODE(S3,18,8,S3)
WHERE LINE_ID = V_LINE_ID;
UPDATE SO_LINES
SET S1_DATE = DECODE(OPEN_FLAG,'',
DECODE(S1,8,SYSDATE,S1_DATE),S1_DATE),
S2_DATE = DECODE(OPEN_FLAG,'',
DECODE(S2,8,SYSDATE,S2_DATE),S2_DATE),
S3_DATE = DECODE(S3,8,SYSDATE,S3_DATE),
S4_DATE = DECODE(OPEN_FLAG,'',
DECODE(S4,8,SYSDATE,S4_DATE),S4_DATE),
S5_DATE = DECODE(OPEN_FLAG,'',
DECODE(S5,8,SYSDATE,S5_DATE),S5_DATE),
S6_DATE = DECODE(OPEN_FLAG,'',
DECODE(S6,8,SYSDATE,S6_DATE),S6_DATE),
S7_DATE = DECODE(OPEN_FLAG,'',
DECODE(S7,8,SYSDATE,S7_DATE),S7_DATE),
S8_DATE = DECODE(OPEN_FLAG,'',
DECODE(S8,8,SYSDATE,S8_DATE),S8_DATE),
S9_DATE = SYSDATE,
S10_DATE=DECODE(OPEN_FLAG,'',
DECODE(S10,8,SYSDATE,S10_DATE),S10_DATE),
S11_DATE=DECODE(OPEN_FLAG,'',
DECODE(S11,8,SYSDATE,S11_DATE),S11_DATE),
S12_DATE=DECODE(OPEN_FLAG,'',
DECODE(S12,8,SYSDATE,S12_DATE),S12_DATE),
S13_DATE=DECODE(OPEN_FLAG,'',
DECODE(S13,8,SYSDATE,S13_DATE),S13_DATE),
S14_DATE=DECODE(OPEN_FLAG,'',
DECODE(S14,8,SYSDATE,S14_DATE),S14_DATE),
S15_DATE=DECODE(OPEN_FLAG,'',
DECODE(S15,8,SYSDATE,S15_DATE),S15_DATE),
S16_DATE=DECODE(OPEN_FLAG,'',
DECODE(S16,8,SYSDATE,S16_DATE),S16_DATE),
S17_DATE=DECODE(OPEN_FLAG,'',
DECODE(S17,8,SYSDATE,S17_DATE),S17_DATE),
S18_DATE=DECODE(OPEN_FLAG,'',
DECODE(S18,8,SYSDATE,S18_DATE),S18_DATE),
S19_DATE=DECODE(OPEN_FLAG,'',
DECODE(S19,8,SYSDATE,S19_DATE),S19_DATE),
S20_DATE=DECODE(OPEN_FLAG,'',
DECODE(S20,8,SYSDATE,S20_DATE),S20_DATE),
S21_DATE=DECODE(OPEN_FLAG,'',
DECODE(S21,8,SYSDATE,S21_DATE),S21_DATE),
S22_DATE=DECODE(OPEN_FLAG,'',
DECODE(S22,8,SYSDATE,S22_DATE),S22_DATE),
S23_DATE=DECODE(OPEN_FLAG,'',
DECODE(S23,8,SYSDATE,S23_DATE),S23_DATE),
S24_DATE=DECODE(OPEN_FLAG,'',
DECODE(S24,8,SYSDATE,S24_DATE),S24_DATE),
S25_DATE=DECODE(OPEN_FLAG,'',
DECODE(S25,8,SYSDATE,S25_DATE),S25_DATE),
S26_DATE=DECODE(OPEN_FLAG,'',
DECODE(S26,8,SYSDATE,S26_DATE),S26_DATE),
S27_DATE=DECODE(OPEN_FLAG,'',
DECODE(S27,8,SYSDATE,S27_DATE),S27_DATE),
S28_DATE=DECODE(OPEN_FLAG,'',
DECODE(S28,8,SYSDATE,S28_DATE),S28_DATE),
S29_DATE=DECODE(OPEN_FLAG,'',
DECODE(S29,8,SYSDATE,S29_DATE),S29_DATE),
S30_DATE=DECODE(OPEN_FLAG,'' ,
DECODE(S30,8,SYSDATE,S30_DATE),S30_DATE)
WHERE LINE_ID = V_LINE_ID;
INSERT INTO SO_ORDER_CANCELLATIONS
( LINE_ID, HEADER_ID,
CANCEL_CODE, CANCELLED_BY,
CANCEL_DATE, LAST_UPDATED_BY,
LAST_UPDATE_DATE, CANCEL_COMMENT,
CANCELLED_QUANTITY, STATUS,
CREATION_DATE, CREATED_BY,
LAST_UPDATE_LOGIN )
VALUES( V_LINE_ID, V_HEADER_ID,
V_CANCEL_CODE, v_current_user,
SYSDATE, V_LAST_UPDATED_BY,
SYSDATE, V_CANCEL_COMMENT,
V_REQUESTED_CANCEL_QTY,
V_STATUS, SYSDATE, v_current_user,
V_LAST_UPDATE_LOGIN );
END UPDATE_MODEL_INFO;
, V_LAST_UPDATED_BY IN NUMBER
, V_RESULT OUT NUMBER
)
IS
X_BOM_MESSAGE VARCHAR2(500) := '';
, USER_ID => V_LAST_UPDATED_BY
, ERR_MSG => X_BOM_MESSAGE
, ERROR_CODE => X_BOM_RESULT);
, V_LAST_UPDATED_BY IN NUMBER
, V_RESULT OUT NUMBER
)
IS
DUMMY NUMBER;
SELECT COUNT(*)
INTO DUMMY
FROM SO_LINES M
WHERE M.LINE_ID = V_LINK_TO_LINE_ID
AND MOD((V_ORDER_QTY
- NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY),
(M.ORDERED_QUANTITY - nvl(M.CANCELLED_QUANTITY,0))) = 0;
V_LAST_UPDATED_BY,
LOAD_BOM_RESULT);
SELECT COUNT(*)
INTO DUMMY2
FROM BOM_EXPLOSIONS
WHERE TOP_BILL_SEQUENCE_ID = V_TOP_BILL_SEQUENCE_ID
AND EXPLOSION_TYPE = 'OPTIONAL'
AND PLAN_LEVEL >= 0
AND EFFECTIVITY_DATE <= TO_DATE(
NVL(substr(V_CREATION_DATE_TIME, 1, 16),
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
'YYYY/MM/DD HH24:MI')
AND DISABLE_DATE > TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
'YYYY/MM/DD HH24:MI')
AND COMPONENT_SEQUENCE_ID = V_PARENT_COMPONENT_SEQUENCE_ID
AND OPTIONAL = 2
AND (V_ORDER_QTY - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY) = 0
AND NOT EXISTS
(SELECT 'X'
FROM SO_LINES
WHERE LINK_TO_LINE_ID = V_LINK_TO_LINE_ID
AND LINE_ID <> V_LINE_ID
AND ORDERED_QUANTITY - NVL(CANCELLED_QUANTITY,0) > 0);
SELECT COUNT(*)
INTO DUMMY3
FROM BOM_EXPLOSIONS
WHERE TOP_BILL_SEQUENCE_ID = V_TOP_BILL_SEQUENCE_ID
AND EXPLOSION_TYPE = 'OPTIONAL'
AND PLAN_LEVEL >= 0
AND EFFECTIVITY_DATE <= TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
'YYYY/MM/DD HH24:MI')
AND DISABLE_DATE > TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
'YYYY/MM/DD HH24:MI')
AND COMPONENT_SEQUENCE_ID = V_COMPONENT_SEQUENCE_ID
AND OPTIONAL = 2
AND (V_ORDER_QTY - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY) = 0;
SELECT COUNT(*)
INTO DUMMY4
FROM BOM_EXPLOSIONS
WHERE TOP_BILL_SEQUENCE_ID = V_TOP_BILL_SEQUENCE_ID
AND EXPLOSION_TYPE = 'OPTIONAL'
AND PLAN_LEVEL >= 0
AND EFFECTIVITY_DATE <= TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
'YYYY/MM/DD HH24:MI')
AND DISABLE_DATE > TO_DATE(NVL(substr(V_CREATION_DATE_TIME, 1, 16),
TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI')),
'YYYY/MM/DD HH24:MI')
AND COMPONENT_SEQUENCE_ID = V_COMPONENT_SEQUENCE_ID
AND NVL(LOW_QUANTITY,0) <=
(SELECT (V_ORDER_QTY - NVL(V_CANCELLED_QTY,0) - V_REQUESTED_CANCEL_QTY) /
(P.ORDERED_QUANTITY - NVL(P.CANCELLED_QUANTITY,0))
FROM SO_LINES P
WHERE P.LINE_ID = V_LINK_TO_LINE_ID);