[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM( (NVL( ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 )) *
NVL(SELLING_PRICE, 0 ))
, 0)
INTO order_line_total
FROM SO_LINES
WHERE SHIPMENT_SCHEDULE_LINE_ID = ORDER_LINE_ID;
SELECT NVL(SUM((NVL( ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 )) *
NVL(SELLING_PRICE, 0 ))
, 0)
INTO order_line_total
FROM SO_LINES
WHERE (ROWID = ORDER_ROWID
OR PARENT_LINE_ID = ORDER_LINE_ID
OR SERVICE_PARENT_LINE_ID = ORDER_LINE_ID );
SELECT NVL(SUM((NVL( ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 )) *
NVL(SELLING_PRICE, 0 ))
, 0)
INTO order_line_total
FROM SO_LINES
WHERE (ROWID = ORDER_ROWID
OR SERVICE_PARENT_LINE_ID = ORDER_LINE_ID );
SELECT NVL( SUM( DECODE( SERVICE_PARENT_LINE_ID, P_LINES_LINE_ID,
(NVL( ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 ) ) *
NVL( SELLING_PRICE, 0 )
, 0 ) ), 0 )
INTO Service_Total
FROM SO_LINES
WHERE (ROWID = P_ROWID
OR PARENT_LINE_ID = P_LINE_ID
OR SERVICE_PARENT_LINE_ID = P_LINE_ID );
SELECT NVL( SUM( DECODE( SERVICE_PARENT_LINE_ID, P_LINES_LINE_ID,
(NVL( ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 ) ) *
NVL( SELLING_PRICE, 0 )
, 0 ) ), 0 )
INTO Service_Total
FROM SO_LINES
WHERE (ROWID = P_ROWID
OR SERVICE_PARENT_LINE_ID = P_LINE_ID );
select meaning
into schedule_status
from so_lookups
where lookup_code = schedule_status_code
and lookup_type = 'SCHEDULE STATUS';
SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
'RESERVED', SLD.QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE(
SLD.SCHEDULE_STATUS_CODE,
'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
0, NULL,
'DEMANDED' ),
'SUPPLY RESERVED' ),
'RESERVED' )
INTO schedule_status_code
FROM SO_LINE_DETAILS SLD
WHERE SLD.LINE_ID = ORDER_LINE_ID;
select meaning
into schedule_status
from so_lookups
where lookup_code = schedule_status_code
and lookup_type = 'SCHEDULE STATUS';
SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
'RESERVED', SLD.QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE(
SLD.SCHEDULE_STATUS_CODE,
'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
0, NULL,
'DEMANDED' ),
'SUPPLY RESERVED' ),
'RESERVED' )
INTO schedule_status_code
FROM SO_LINE_DETAILS SLD
WHERE SLD.LINE_ID = ORDER_LINE_ID;
SELECT DECODE( COUNT(*), 0, 'N', 'Y' )
INTO ATO_Ind
FROM SO_LINES
WHERE PARENT_LINE_ID = P_Line_Id
AND SERVICE_PARENT_LINE_ID IS NULL
AND ATO_FLAG = 'Y'
AND ROWNUM = 1;
SELECT DECODE( COUNT(*), 0, 'N', 'Y' )
INTO ATO_Ind
FROM SO_LINES
WHERE PARENT_LINE_ID = P_Line_Id
AND SERVICE_PARENT_LINE_ID IS NULL
AND ATO_FLAG = 'Y'
AND ROWNUM = 1;
SELECT sum(nvl(quantity, 0)), configuration_item_flag
FROM so_line_details
WHERE line_id = X_Line_Id
AND nvl(included_item_flag, 'N') = 'N'
AND nvl(released_flag, 'Y') = 'Y'
GROUP BY configuration_item_flag;
SELECT SUM(NVL(REQUESTED_QUANTITY,0))
INTO rel_qty
FROM SO_PICKING_LINE_DETAILS
WHERE PICKING_LINE_ID = P_LINE_ID
AND NVL(RELEASED_FLAG,'N')='Y';
select sum( decode( SCHEDULE_STATUS_CODE,
'RESERVED', QUANTITY,
0))
into RESERVED_QTY
from SO_LINE_DETAILS
where line_id = ORDER_LINE_ID
and NVL(INCLUDED_ITEM_FLAG, 'N')='N';
select sum(nvl(det.quantity, 0))
into reserved_qty
from so_lines l,
so_line_details det
where
l.line_id = p_line_id
and det.line_id = l.line_id
and det.included_item_flag = 'Y'
and det.schedule_status_code = 'RESERVED'
-- and det.component_code = l.component_code ||
-- ltrim(p_component_code, '0123456789');
select sum(nvl(det.quantity, 0))
into released_qty
from so_lines l,
so_line_details det
where
l.line_id = p_line_id
and det.line_id = l.line_id
and det.released_flag = 'Y'
and det.included_item_flag = 'Y'
-- and det.component_code = l.component_code ||
-- ltrim(p_component_code, '0123456789');
SELECT DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
'RESERVED', SLD.QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE( SLD.SCHEDULE_STATUS_CODE,
'SUPPLY RESERVED', SLD.QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE(
SLD.SCHEDULE_STATUS_CODE,
'DEMANDED', SLD.QUANTITY, 0 ) ),0 ),
0, NULL,
'DEMANDED' ),
'SUPPLY RESERVED' ),
'RESERVED' )
INTO schedule_status_code
FROM
SO_LINES SL,
SO_LINE_DETAILS SLD
WHERE
SL.line_id = P_Line_Id
and SLD.Line_id = SL.Line_Id
and SLD.Included_Item_Flag = 'Y'
--and SLD.Component_Code = SL.Component_Code ||
-- ltrim(P_Component_Code, '0123456789');
select 'Y'
into Supply_Reserved_Details
from so_line_details
where
line_id = P_Line_Id
and schedule_status_code = 'SUPPLY RESERVED'
and rownum = 1;
SELECT DECODE( NVL(SUM(DECODE( HOLD_RELEASE_ID, NULL, 1, 0)),0),
0,'N', 'Y')
INTO HOLD_FLAG
FROM SO_ORDER_HOLDS_ALL
WHERE LINE_ID = ORDER_LINE_ID
OR (LINE_ID IS NULL AND HEADER_ID = ORDER_HEADER_ID);
SELECT LINE_NUMBER
INTO SHIP_NUMBER
FROM SO_LINES
WHERE LINE_ID = ORDER_PARENT_LINE_ID;
SELECT LINE_NUMBER
INTO LINES_NUMBER
FROM SO_LINES
WHERE LINE_ID = ORDER_PARENT_LINE_ID;
SELECT LINE_NUMBER
INTO LINES_NUMBER
FROM SO_LINES
WHERE LINE_ID = ORDER_SHIP_SCHEDULE_LINE_ID;
SELECT CONCATENATED_SEGMENTS
INTO ITEM_NAME
FROM MTL_SYSTEM_ITEMS_KFV
WHERE INVENTORY_ITEM_ID = ITEM_ID
AND ORGANIZATION_ID = ORG_ID;
SELECT NAME
INTO ORDER_TYPE
FROM SO_ORDER_TYPES
WHERE ORDER_TYPE_ID = ID;
SELECT NVL(SUM( (NVL( l.ordered_quantity, 0) -
NVL( l.cancelled_quantity, 0)) *
NVL( l.selling_price, 0)), 0) ORDER_TOTAL
FROM so_lines l
WHERE l.header_id = X_Header_Id
AND l.line_type_code IN ('REGULAR', 'DETAIL','RETURN');
SELECT NVL(SUM( (NVL( ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 )) *
NVL(SELLING_PRICE, 0 ))
, 0)
INTO line_total
FROM SO_LINES
WHERE SHIPMENT_SCHEDULE_LINE_ID = P_Line_Id;
SELECT NVL(SUM((NVL(ORDERED_QUANTITY, 0 ) -
NVL( CANCELLED_QUANTITY, 0 )) *
NVL(SELLING_PRICE, 0 )),0)
INTO L_Configuration_Total
FROM SO_LINES
WHERE (LINE_ID = Config_Parent_Line_Id
OR PARENT_LINE_ID = Config_Parent_Line_Id
OR SERVICE_PARENT_LINE_ID =
Config_Parent_Line_Id );
select to_char(ord.order_number)
into rtn_value
from so_headers ord
where ord.header_id = decode(rtrim(p_original_system_reference,
'0123456789'),
null,to_number(p_original_system_reference),
null);
select typ.name
into rtn_value
from so_headers ord,
so_order_types typ
where typ.order_type_id = ord.order_type_id
and ord.header_id = decode(rtrim(p_original_system_reference,
'0123456789'),
null,to_number(p_original_system_reference),
null);
select Ordered_Quantity,
nvl(cancelled_quantity, 0),
link_to_line_id,
item_type_code,
Ato_Flag,
S29,
Shipped_quantity
into L_Qty_Ordered,
l_cancel_qty,
l_link_to_line_id,
l_item_type,
L_Ato_Flag,
L_S29,
L_Shipped_quantity
from so_lines
where line_id = p_line_id;
select floor(
nvl( min( mtlint.received_quantity *
(lin.Ordered_Quantity - nvl(lin.cancelled_quantity,0)) /
mtlint.interfaced_quantity),0))
into l_qty_received
from so_rma_mtl_int_v mtlint,
so_lines lin
where mtlint.rma_line_id = p_line_id
and lin.line_id = p_line_id
group by lin.line_id,
lin.ordered_quantity,
lin.cancelled_quantity;
SELECT count (line_detail_id)
INTO l_included_item_count
FROM so_line_details
WHERE line_id = l_link_to_line_id
AND included_item_flag = 'Y';
SELECT floor(nvl(min(MTLINT.Received_Quantity),0))
INTO l_qty_received
FROM SO_RMA_MTL_INT_DETAIL_V MTLINT
WHERE MTLINT.Detail_line_id = l_link_to_line_id
AND MTLINT.RMA_LINE_ID = P_Line_Id;
SELECT sum(nvl(MTLSRR.received_quantity,0))
INTO l_qty_received
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 = p_line_id;
SELECT sum(nvl(MTLSRR.received_quantity,0))
INTO l_qty_received
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 = p_line_id;
select min(rma_interface_id)
into l_interface_id
from mtl_so_rma_interface
where rma_line_id = p_line_id;
select Ordered_Quantity,
nvl(cancelled_quantity, 0),
link_to_line_id,
item_type_code,
Ato_Flag,
S29,
Shipped_quantity
into L_Qty_Ordered,
l_cancel_qty,
l_link_to_line_id,
l_item_type,
L_Ato_Flag,
L_S29,
L_Shipped_quantity
from so_lines
where line_id = p_line_id;
select floor(
nvl( min( mtlint.accepted_quantity *
(lin.Ordered_Quantity - nvl(lin.cancelled_quantity,0)) /
mtlint.interfaced_quantity),0))
into l_qty_received
from so_rma_mtl_int_v mtlint,
so_lines lin
where mtlint.rma_line_id = p_line_id
and lin.line_id = p_line_id
group by lin.line_id,
lin.ordered_quantity,
lin.cancelled_quantity;
SELECT count (line_detail_id)
INTO l_included_item_count
FROM so_line_details
WHERE line_id = l_link_to_line_id
AND included_item_flag = 'Y';
SELECT floor(nvl(min(MTLINT.Accepted_Quantity),0))
INTO l_qty_received
FROM SO_RMA_MTL_INT_DETAIL_V MTLINT
WHERE MTLINT.Detail_line_id = l_link_to_line_id
AND MTLINT.RMA_LINE_ID = P_Line_Id;
SELECT sum(nvl(MTLSRR.accepted_quantity,0))
INTO l_qty_received
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 = p_line_id;
SELECT sum(nvl(MTLSRR.accepted_quantity,0))
INTO l_qty_received
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 = p_line_id;
select min(rma_interface_id)
into l_interface_id
from mtl_so_rma_interface
where rma_line_id = p_line_id;
SELECT MAX( MTLSRR.RECEIPT_DATE )
INTO L_Received_Date
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 = p_line_id
AND MTLSRR.RECEIVED_QUANTITY > 0;
SELECT MAX( MTLSRR.RECEIPT_DATE )
INTO L_Accepted_Date
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 = p_line_id
AND MTLSRR.ACCEPTED_QUANTITY > 0;
SELECT MEANING
INTO L_Flag_Display
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = 'TAX_CONTROL_FLAG'
AND LOOKUP_CODE = L_Flag;
SELECT MEANING
INTO L_REASON_DISPLAY
FROM AR_LOOKUPS
WHERE LOOKUP_CODE = L_REASON
AND LOOKUP_TYPE = 'TAX_REASON';
SELECT NVL( SUM( PERCENT ), 0 )
INTO L_TOTAL
FROM SO_PRICE_ADJUSTMENTS
WHERE HEADER_ID = P_HEADER_ID
AND (LINE_ID IS NULL
OR LINE_ID = P_LINE_ID);
SELECT tax.tax_exempt_number,
tax.tax_exempt_reason_meaning
FROM tax_exemptions_qp_v tax
WHERE tax.ship_to_site_use_id = X_Ship_To_Site_Use_Id
AND tax.bill_to_customer_id = X_Invoice_To_customer_id
AND trunc(NVL(X_Date_Ordered, SYSDATE))
between trunc(tax.start_date) and
trunc(NVL(tax.end_date, NVL(X_Date_Ordered, SYSDATE)))
AND tax.status_code = 'PRIMARY';
SELECT 'Y'
INTO l_flag
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM so_line_details
WHERE line_id = X_line_id
AND configuration_item_flag = 'Y');
SELECT SUM(NVL(quantity,0))
INTO l_qty
FROM so_line_Details
WHERE line_id = X_line_id
AND NVL(included_item_flag, 'N') = 'N'
AND NVL(released_flag, 'Y') = 'Y'
AND NVL(configuration_item_flag, 'N') =
OE_QUERY.line_config_item_exists(X_line_id);
SELECT expiration_date
INTO l_expiration_date
FROM mtl_lot_numbers
WHERE inventory_item_id = X_inventory_item_id
AND organization_id = X_organization_id
AND lot_number = X_lot_number;
SELECT NVL( SUM( NVL(requested_quantity,0)), 0)
INTO l_num
FROM so_picking_line_details
WHERE schedule_status_code = 'RESERVED'
AND picking_line_id = X_picking_line_id;
SELECT Nvl(SUM(pld.requested_quantity),0)
INTO l_backordered_quantity
FROM
so_picking_lines pl,
so_picking_line_details pld
WHERE
Nvl(pld.released_flag, 'Y') = 'N'
AND pld.picking_line_id = pl.picking_line_id
AND pl.picking_header_id = 0
AND Nvl(pl.included_item_flag, 'N') = 'N'
AND pl.order_line_id = x_line_id;
select inventory_item_id
into item_id
from so_picking_lines
where picking_line_id = X_picking_line_id;
Select NVL(Min(Group_Available_Date), Min(Request_ATP_Date))
Into L_ATP_Date
From MTL_Demand_Interface
Where Session_Id = P_Session_Id
And Demand_Source_Line = P_Line_Id
And Inventory_Item_Id = P_Inventory_Item_Id
And N_Column4 is Null
Having Count(Distinct Group_Available_Date) = 1
Or (Count(Distinct Group_Available_Date) = 0
And Count(Distinct Request_ATP_Date) = 1);
Select Min(Request_Date_ATP_Quantity)
Into L_Available_Qty
From MTL_Demand_Interface
Where Session_Id = P_Session_Id
And Demand_Source_Line = P_Line_Id
And Inventory_Item_Id = P_Inventory_Item_Id
And Line_Item_Quantity > 0
And N_Column4 is Null
Having Count(Demand_Source_Delivery) = 1;
Select Min(RowIdtoChar(RowId))
Into L_Demand_Interface_RowId
From MTL_Demand_Interface
Where Session_Id = P_Session_Id
And Demand_Source_Line = P_Line_Id
And N_Column4 is Null
And Line_Item_Quantity <> 0
And Action_Code = 100
Having Count(Distinct ATP_Group_Id) = 1;
Select NVL(Min(Group_Available_Date), Min(Request_ATP_Date))
Into L_ATP_Date
From MTL_Demand_Interface
Where Session_Id = P_Session_Id
And Demand_Source_Delivery = P_Delivery
And N_Column4 is Null
Having Count(Distinct Group_Available_Date) = 1
Or (Count(Distinct Group_Available_Date) = 0
And Count(Distinct Request_ATP_Date) = 1);
Select Min(Request_Date_ATP_Quantity)
Into L_Available_Qty
From MTL_Demand_Interface
Where Session_Id = P_Session_Id
And Demand_Source_Delivery = P_Delivery
And Line_Item_Quantity > 0
And N_Column4 is Null
Having Count(Demand_Source_Delivery) = 1;
Select Min(RowIdtoChar(RowId))
Into L_Demand_Interface_RowId
From MTL_Demand_Interface
Where Session_Id = P_Session_Id
And Demand_Source_Line = P_Delivery
And N_Column4 is Null
And Line_Item_Quantity <> 0
And Action_Code = 100
Having Count(Distinct ATP_Group_Id) = 1;
SELECT DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
'RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
'SUPPLY RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE(
SPLD.SCHEDULE_STATUS_CODE,
'DEMANDED', SPLD.REQUESTED_QUANTITY, 0 ) ),0 ),
0, NULL,
'DEMANDED' ),
'SUPPLY RESERVED' ),
'RESERVED' )
INTO schedule_status_code
FROM SO_PICKING_LINE_DETAILS SPLD
WHERE SPLD.PICKING_LINE_ID = P_LINE_ID;
select meaning
into schedule_status
from so_lookups
where lookup_code = schedule_status_code
and lookup_type = 'SCHEDULE STATUS';
SELECT DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
'RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE( SPLD.SCHEDULE_STATUS_CODE,
'SUPPLY RESERVED', SPLD.REQUESTED_QUANTITY, 0 ) ), 0),
0,
DECODE( NVL( SUM( DECODE(
SPLD.SCHEDULE_STATUS_CODE,
'DEMANDED', SPLD.REQUESTED_QUANTITY, 0 ) ),0 ),
0, NULL,
'DEMANDED' ),
'SUPPLY RESERVED' ),
'RESERVED' )
INTO schedule_status_code
FROM SO_PICKING_LINE_DETAILS SPLD
WHERE SPLD.PICKING_LINE_ID = P_LINE_ID;
SELECT org_id
INTO org_id
from so_headers
where rownum = 1;
Select name
into org_name
from hr_operating_units
where organization_id = org_id
and rownum = 1;
select name from so_results
where result_id = p_id;
SELECT SHIPMENT_SCHEDULE_LINE_ID,
PARENT_LINE_ID,
LINE_NUMBER
INTO V_SHIPMENT_SCHEDULE_LINE_ID,
V_PARENT_LINE_ID,
V_LINE_NUMBER
FROM SO_LINES
WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
SELECT NVL(SUM(AMOUNT_DUE_REMAINING),0)
INTO v_balance
FROM AR_PAYMENT_SCHEDULES
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
SELECT NVL(SUM(EXTENDED_AMOUNT),0)
INTO v_invoice
FROM RA_CUSTOMER_TRX_LINES
WHERE CUSTOMER_TRX_ID = P_CUSTOMER_TRX_ID;
SELECT SHIPMENT_SCHEDULE_LINE_ID,
PARENT_LINE_ID,
LINE_NUMBER
INTO V_SHIPMENT_SCHEDULE_LINE_ID,
V_PARENT_LINE_ID,
V_LINE_NUMBER
FROM SO_LINES
WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
SELECT LINE_NUMBER
INTO BASE_LINE_NUMBER
FROM SO_LINES
WHERE LINE_ID = V_PARENT_LINE_ID;
SELECT LINE_NUMBER
INTO BASE_LINE_NUMBER
FROM SO_LINES
WHERE LINE_ID = V_SHIPMENT_SCHEDULE_LINE_ID;
SELECT SHIPMENT_SCHEDULE_LINE_ID,
PARENT_LINE_ID,
LINE_NUMBER
INTO V_SHIPMENT_SCHEDULE_LINE_ID,
V_PARENT_LINE_ID,
V_LINE_NUMBER
FROM SO_LINES
WHERE LINE_ID = P_SERVICE_PARENT_LINE_ID;
SELECT LINE_NUMBER
INTO SCHEDULE_LINE_NUMBER
FROM SO_LINES
WHERE LINE_ID = V_PARENT_LINE_ID;