[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Call process order with the shipped quantity update.
----------------------------------------------------------------------- */
FUNCTION DropShipReceive( p_rcv_transaction_id IN NUMBER,
p_application_short_name IN VARCHAR2,
p_mode IN NUMBER DEFAULT 0)
RETURN BOOLEAN
IS
l_line_id NUMBER;
SELECT OL.LINE_ID,
RT.TRANSACTION_DATE,
OL.ORG_ID, -- bug 4402566
OD.LINE_LOCATION_ID, -- bug 4402566
RT.PO_HEADER_ID, -- bug 4402566
nvl(OL.SHIPPED_QUANTITY,0) shp_qty
FROM OE_ORDER_LINES_ALL OL,
OE_DROP_SHIP_SOURCES OD,
RCV_TRANSACTIONS RT
WHERE OL.LINE_ID = OD.LINE_ID
AND OL.SOURCE_TYPE_CODE = 'EXTERNAL'
AND OD.PO_HEADER_ID = RT.PO_HEADER_ID
AND OD.PO_LINE_ID = RT.PO_LINE_ID
AND OD.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
AND RT.TRANSACTION_ID = l_transaction_id
ORDER BY 1 desc;
SELECT org_id
INTO l_so_ou_id
FROM oe_order_lines_all
WHERE line_id = l_line_id;
SELECT pol.org_id
INTO l_po_ou_id
FROM po_lines_all pol,
oe_drop_ship_sources ds
WHERE ds.line_id = l_line_id
AND ds.po_header_id = pol.po_header_id
AND ds.po_line_id = pol.po_line_id;
SELECT poh.org_id
INTO l_po_ou_id
FROM po_headers_all poh
WHERE poh.po_header_id = l_po_header_id;
SELECT rt.quantity,
rt.unit_of_measure,
rt.uom_code,
rt.secondary_quantity, -- OPM
rt.secondary_unit_of_measure, -- OPM
rt.organization_id,
rt.subinventory,
NVL(msinv.reservable_type, 2),
rt.locator_id,
rs.item_id,
decode(mi.revision_qty_control_code,2,rs.item_revision,NULL),
mi.mtl_transactions_enabled_flag,
mi.serial_number_control_code,
mi.auto_serial_alpha_prefix,
rt.transfer_lpn_id --bug 3544019
INTO l_rcv_quantity,
l_unit_descr,
l_rcv_uom,
l_rcv_quantity2, -- OPM
l_unit2, -- OPM
l_organization_id,
l_subinventory,
l_sub_reservable,
l_locator_id,
l_item_id,
l_revision,
l_transactable,
l_sn_control_code,
l_as_alpha_prefix,
l_transfer_lpn_id -- bug 3544019
FROM mtl_system_items mi,
mtl_secondary_inventories msinv,
rcv_shipment_lines rs,
rcv_transactions rt
WHERE rt.transaction_id = l_transaction_id
AND rs.shipment_line_id = rt.shipment_line_id
AND mi.organization_id = rt.organization_id
AND mi.inventory_item_id = rs.item_id
AND msinv.organization_id(+) = rt.organization_id
AND msinv.secondary_inventory_name(+) = rt.subinventory;
SELECT rl.lot_num
,NVL( rl.quantity, -1)
,rl.secondary_quantity -- OPM
-- ,rl.sublot_num -- OPM
,rt.transaction_id
FROM rcv_lot_transactions rl,
rcv_transactions rt
WHERE rt.transaction_id = l_transaction_id
AND rl.transaction_id (+) = rt.transaction_id;
** Will use this sequence for inserting all records in
** MTL_INTERFACE tables during decrement inventory
*/
SELECT mtl_material_transactions_s.nextval
INTO l_lot_set_id
FROM dual;
SELECT NVL(reservable_type,2)
INTO l_item_reservable
FROM mtl_system_items
WHERE inventory_item_id = l_line_rec.inventory_item_id
AND organization_id = l_line_rec.ship_from_org_id;
Description : This procedure inserts records in the MTL_TRANSACTION...
tables. The inserted records will be later processed by
mtl_online_transaction_pub.process_online API (called
from the main dropshipreceive function, to decrement
inventory in the system.
This API is called in a loop from the DropShipReceive
function. The receiving transactions are stored in
RCV_TRANSACTIONS table and RCV_LOT_TRANSACTIONS (if there
are lot level transactions). This API will be called only
once (for the record in RCV_TRANSACTIONS) if not lots are
associated. If there are multiple lots in the transaction,
it will be called for every lot.
This API will insert records into
MTL_TRANSACTION_LOTS_INTERFACE: For every receipt in lot
(lot controlled items).
MTL_SERIAL_NUMBERS_INTERFACE : For every serial number
receipt (serial controlled
items.)
MTL_TRANSACTIONS_INTERFACE : For every transaction
(corresponds to qty recd
in RCV_TRANSACTIONS)
IMPORTANT: ALL THE RECORDS INSERTED IN THE ABOVE TABLE FOR A
PARTICULAR TRANSACTION HAVE THE SAME TRANSACTION_INTERFACE_ID.
We pass the p_transaction_header_id to this API which is
value we got from mtl_material_transactions_s sequence.
This value is assigned to the TRANSACTION_INTERFACE_ID.
----------------------------------------------------------------------- */
Procedure Decrement_Inventory(
p_detail_id IN NUMBER,
p_line_rec IN OE_ORDER_PUB.line_rec_type,
p_transaction_id IN NUMBER,
p_transaction_detail_qty IN NUMBER,
p_trans_qty2 IN NUMBER, -- INVCONV
p_inventory_item_id IN NUMBER,
p_delivery IN NUMBER,
p_lot_number IN VARCHAR2,
p_revision IN VARCHAR2,
p_secondary_inventory IN VARCHAR2,
p_locator_id IN NUMBER,
p_warehouse_id IN NUMBER,
p_chart_of_accts IN NUMBER,
p_trx_uom IN VARCHAR2,
p_sn_control_code IN NUMBER,
p_as_alpha_prefix IN VARCHAR2,
p_transaction_header_id IN NUMBER,
p_transfer_lpn_id IN NUMBER, -- 3544019
x_return_status OUT NOCOPY VARCHAR2)
IS
l_source_line_id NUMBER;
/* SELECT oe_transactions_iface_s.nextval
INTO l_source_line_id
FROM dual; */
oe_debug_pub.add( 'Selecting unique id for this transaction' , 1 ) ;
/* Insert lot transaction interface table */
IF l_debug_level > 0 THEN
oe_debug_pub.add( 'Inserting lots' ) ;
INSERT INTO MTL_TRANSACTION_LOTS_INTERFACE
(
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSACTION_INTERFACE_ID,
LOT_NUMBER,
TRANSACTION_QUANTITY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
SERIAL_TRANSACTION_TEMP_ID,
ERROR_CODE,
PROCESS_FLAG )
VALUES
(
profile_values.oe_source_code,
l_source_line_id,
l_lot_set_id,
p_lot_number,
p_transaction_detail_qty,
sysdate,
profile_values.user_id,
sysdate,
profile_values.user_id,
null,
null,
'Y');
END IF; /* end insert lot */
Inserting into serial number interface
1. If it is a predefined serial number controlled item
i.e. serial number control code = 2
then when doing an issue transaction ,get the
serial numbers from prior receipt transactions for the
same item,warehouse,locator combination.
2. If it is a serial controlled item with dynamic generation
at receipt time i.e. serial number control code = 5
logic is same as above when doing an issue transaction.
3. If it is a serial controlled item with dynamic generation
at issue time generate the serial numbers with appropriate prefix .
There is 1 serial number per quantity.
*/
IF (p_sn_control_code = 2 OR
p_sn_control_code = 5 OR
p_sn_control_code = 6)
THEN
SELECT mtl_material_transactions_s.nextval
INTO l_serial_set_id
FROM dual;
SELECT rtrim(ltrim(msn.serial_number))
FROM mtl_serial_numbers msn
WHERE msn.inventory_item_id = p_inventory_item_id
AND msn.current_organization_id = p_warehouse_id
AND nvl(msn.current_subinventory_code,' ') = p_secondary_inventory
AND nvl(msn.current_locator_id,0) = nvl(p_locator_id,0)
AND msn.current_status=3
AND msn.group_mark_id is NULL
AND exists (select 1 from mtl_material_transactions mmt, rcv_transactions rt
where mmt.transaction_id = msn.last_transaction_id
and mmt.transaction_source_type_id = 1
and rt.transaction_id = p_transaction_id
and rt.transaction_id = mmt.rcv_transaction_id
and msn.last_txn_source_id = rt.po_header_id);
oe_debug_pub.add( 'Inserting serial numbers after serial numbers fix' , 1 ) ;
SELECT primary_uom_code
INTO l_primary_uom
FROM mtl_system_items
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = p_line_rec.ship_from_org_id;
SELECT to_char(oe_mtl_sn_interface_s.nextval)
INTO v_serial_number_temp
FROM dual ;
oe_debug_pub.add( 'Inserting into mtl_serial_numbers_interface table ' , 1 ) ;
insert into mtl_serial_numbers_interface
(transaction_interface_id,
source_code,
source_line_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
vendor_serial_number,
vendor_lot_number,
fm_serial_number,
to_serial_number,
error_code,
process_flag
)
values
(
l_serial_set_id, /*transaction_interface_id */
profile_values.oe_source_code, /*source_code */
l_source_line_id, /*source_line_id */
sysdate, /*last_update_date*/
profile_values.user_id, /*last_updated_by */
sysdate , /*creation_date */
profile_values.user_id, /*created by */
profile_values.login_id , /*last_update_login */
profile_values.request_id, /*request_id */
profile_values.application_id, /*program_application_id */
profile_values.program_id, /*program_id */
sysdate, /*program_update_date */
null, /* vendor_serial_number */
null, /* vendor_lot_number */
v_serial_number, /* fm_serial_number */
null, /*to_serial_number */
null, /* error_code */
null /* process_flag */
) ;
oe_debug_pub.add( 'Failed inserting into mtl_serial_numbers_interface' , 1 ) ;
/* Update the column group_mark_id with line_id, so that
this serial number will not be used by another transaction
and thus avoiding duplicates */
IF ((p_sn_control_code = 2) OR
(p_sn_control_code = 5))
THEN
BEGIN
UPDATE mtl_serial_numbers
SET GROUP_MARK_ID = p_line_rec.line_id
where inventory_item_id = p_inventory_item_id
and current_organization_id = p_warehouse_id
and nvl(current_locator_id,0) = nvl(p_locator_id,0)
and serial_number = v_serial_number;
oe_debug_pub.add( 'Serial number updated is :' || v_serial_number , 1 ) ;
UPDATE MTL_TRANSACTION_LOTS_INTERFACE
SET SERIAL_TRANSACTION_TEMP_ID = l_serial_set_id
WHERE TRANSACTION_INTERFACE_ID = l_lot_set_id;
SELECT RT.TRANSACTION_DATE
INTO l_transaction_date
FROM RCV_TRANSACTIONS RT
WHERE RT.TRANSACTION_ID = p_transaction_id;
SELECT h.order_number,ot.name
INTO l_order_number,l_order_type_name
FROM oe_order_headers_all h, oe_order_types_v ot
WHERE h.header_id = p_line_rec.header_id AND
ot.order_type_id = h.order_type_id;
oe_debug_pub.add('Inserting header record' ) ;
oe_debug_pub.add('Last_update_date : ' || sysdate , 1 ) ;
oe_debug_pub.add('Last_updated_by : ' || fnd_global.user_id , 1 ) ;
oe_debug_pub.add('Before inserting records into mtl interface table..' , 1 ) ;
SELECT segment1,
segment2,
segment3,
segment4,
segment5,
segment6,
segment7,
segment8,
segment9,
segment10,
segment11,
segment12,
segment13,
segment14,
segment15,
segment16,
segment17,
segment18,
segment19,
segment20,
segment21,
segment22,
segment23,
segment24,
segment25,
segment26,
segment27,
segment28,
segment29,
segment30
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = l_trans_acc;
INSERT INTO MTL_TRANSACTIONS_INTERFACE
(
SOURCE_CODE,
SOURCE_LINE_ID,
SOURCE_HEADER_ID,
PROCESS_FLAG,
TRANSACTION_MODE,
LOCK_FLAG,
TRANSACTION_HEADER_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
TRANSACTION_QUANTITY,
SECONDARY_TRANSACTION_QUANTITY, -- INVCONV
TRANSACTION_DATE,
ORGANIZATION_ID,
ACCT_PERIOD_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
TRANSACTION_SOURCE_ID,
-- DSP_SEGMENT1, bug 4456817
-- DSP_SEGMENT2,
-- DSP_SEGMENT3,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_TYPE_ID,
DISTRIBUTION_ACCOUNT_ID,
DST_SEGMENT1,
DST_SEGMENT2,
DST_SEGMENT3,
DST_SEGMENT4,
DST_SEGMENT5,
DST_SEGMENT6,
DST_SEGMENT7,
DST_SEGMENT8,
DST_SEGMENT9,
DST_SEGMENT10,
DST_SEGMENT11,
DST_SEGMENT12,
DST_SEGMENT13,
DST_SEGMENT14,
DST_SEGMENT15,
DST_SEGMENT16,
DST_SEGMENT17,
DST_SEGMENT18,
DST_SEGMENT19,
DST_SEGMENT20,
DST_SEGMENT21,
DST_SEGMENT22,
DST_SEGMENT23,
DST_SEGMENT24,
DST_SEGMENT25,
DST_SEGMENT26,
DST_SEGMENT27,
DST_SEGMENT28,
DST_SEGMENT29,
DST_SEGMENT30,
TRANSACTION_REFERENCE,
TRX_SOURCE_LINE_ID,
TRX_SOURCE_DELIVERY_ID,
REVISION,
LOCATOR_ID,
LOC_SEGMENT1,
LOC_SEGMENT2,
LOC_SEGMENT3,
LOC_SEGMENT4,
REQUIRED_FLAG,
PICKING_LINE_ID,
TRANSFER_SUBINVENTORY,
TRANSFER_ORGANIZATION,
SHIP_TO_LOCATION_ID,
REQUISITION_LINE_ID,
TRANSACTION_UOM,
TRANSACTION_INTERFACE_ID,
DEMAND_ID,
SHIPMENT_NUMBER,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
ENCUMBRANCE_ACCOUNT,
ENCUMBRANCE_AMOUNT,
PROJECT_ID,
TASK_ID,
CONTENT_LPN_ID) -- 3544019
VALUES (
profile_values.oe_source_code,
l_source_line_id,
l_transaction_reference,
1, /* PROCESS_FLAG */
3, /* TRANSACTION_MODE */
2, /* LOCK_FLAG */
l_transaction_header_id,
p_inventory_item_id,
p_secondary_inventory,
(-1 * p_transaction_detail_qty),
(-1 * p_trans_qty2), -- INVCONV
l_transaction_date,
p_warehouse_id,
null,
sysdate,
profile_values.user_id,
sysdate,
profile_values.user_id,
l_transaction_source_id,
-- l_order_number, bug 4456817
-- l_order_type_name,
-- profile_values.oe_source_code,
l_trx_source_type_id,
l_trx_action_id,
l_trx_type_code,
l_trans_acc,
l_segment1,
l_segment2,
l_segment3,
l_segment4,
l_segment5,
l_segment6,
l_segment7,
l_segment8,
l_segment9,
l_segment10,
l_segment11,
l_segment12,
l_segment13,
l_segment14,
l_segment15,
l_segment16,
l_segment17,
l_segment18,
l_segment19,
l_segment20,
l_segment21,
l_segment22,
l_segment23,
l_segment24,
l_segment25,
l_segment26,
l_segment27,
l_segment28,
l_segment29,
l_segment30,
l_transaction_reference,
l_line_id,
decode(l_delivery,-1,null,l_delivery),
p_revision,
decode(p_locator_id,-1,null,p_locator_id),
null,
null,
null,
null,
null,
l_shipment_line_id,
decode(l_dest_subinv,' ',null,l_dest_subinv),
decode(l_to_org_id,-1,null,l_to_org_id),
decode(l_location_id,0,null,l_location_id),
decode(l_req_line_id,0,null,l_req_line_id),
p_trx_uom,
l_transaction_interface_id,
null,
null,
null,
null,
null,
null,
decode(l_budget_acct_id,-1, null, l_budget_acct_id),
decode(l_budget_acct_id,-1, null,
l_unit_price * p_transaction_detail_qty),
decode(l_project_id, 0, null, l_project_id),
decode(l_task_id, 0, null, l_task_id),
p_transfer_lpn_id); -- 3544019
oe_debug_pub.add( 'Successfully inserted records in mtl interface table ' , 5 ) ;
oe_debug_pub.add( 'Failed while inserting records in mtl_transactions_interface '||sqlerrm , 1 ) ;
SELECT chart_of_accounts_id
INTO l_chart_of_accs
FROM org_organization_definitions
WHERE org_organization_definitions.organization_id = p_ship_from_org_id; */
SELECT chart_of_accounts_id INTO l_chart_of_accs
FROM gl_sets_of_books gsob, hr_organization_information hoi
WHERE gsob.set_of_books_id = hoi.org_information1
AND upper(hoi.org_information_context) = 'ACCOUNTING INFORMATION'
AND hoi.organization_id = p_ship_from_org_id;
SELECT order_type_id
INTO l_order_type_id
FROM oe_order_headers
WHERE header_id = p_header_id;
SELECT NAME
INTO l_order_type_name
FROM OE_TRANSACTION_TYPES_TL
WHERE TRANSACTION_TYPE_ID = l_order_type_id
AND language = (select language_code
from fnd_languages
where installed_flag = 'B');
SELECT S.SALES_ORDER_ID
INTO l_sales_order_id
FROM MTL_SALES_ORDERS S,
OE_ORDER_HEADERS H
WHERE S.SEGMENT1 = TO_CHAR(H.ORDER_NUMBER)
AND S.SEGMENT2 = l_order_type_name
AND S.SEGMENT3 = l_source_code
AND H.HEADER_ID = p_header_id;
PROCEDURE : Insert_OE_Drop_Ship_Source
DESCRIPTION:
-----------------------------------------------------------------*/
PROCEDURE Insert_OE_Drop_Ship_Source (P_Old_Line_ID IN NUMBER, P_New_Line_ID IN NUMBER) IS
l_Header_ID Number;
SELECT Header_id,
Org_id,
Destination_Organization_ID,
Requisition_Header_ID,
Requisition_Line_ID,
PO_Header_ID,
PO_Line_ID,
Line_Location_ID,
PO_Release_ID
FROM OE_DROP_SHIP_SOURCES
WHERE line_id = l_new_line_id
AND line_location_id = G_LINE_LOCATION_ID; -- bug 4402566
Insert Into OE_Drop_Ship_Sources
(
drop_ship_source_id,
header_id,
line_id,
org_id,
destination_organization_id,
requisition_header_id,
requisition_line_id,
po_header_id,
po_line_id,
line_location_id,
po_release_id,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By
)
Values
(
oe_drop_ship_source_s.nextval,
l_header_id,
l_old_line_id,
l_org_id,
l_destination_organization_id,
l_requisition_header_id,
l_requisition_line_id,
l_po_header_id,
l_po_line_id,
l_line_location_id,
l_po_release_id,
trunc(Sysdate),
nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1),
trunc(Sysdate),
nvl(To_Number(FND_PROFILE.VALUE('USER_ID')),-1)
);
End Insert_OE_Drop_Ship_Source;
the old transactions would be deleted and new ones would be
inserted.Default lot is always checked. A default
transaction would be created if the item is not lot/location
ctl'ed and no transactions exist.
-----------------------------------------------------------------------*/
/*Procedure Decrement_Inventory_for_OPM(
p_detail_id IN NUMBER,
p_line_rec IN OE_ORDER_PUB.line_rec_type,
p_transaction_id IN NUMBER,
p_trans_qty IN NUMBER,
p_trans_qty2 IN NUMBER,
p_inventory_item_id IN NUMBER,
p_delivery IN NUMBER,
p_lot_number IN VARCHAR2,
p_sublot_no IN VARCHAR2,
p_revision IN VARCHAR2,
p_locator_id IN NUMBER,
p_warehouse_id IN NUMBER,
p_chart_of_accts IN NUMBER,
p_trx_uom IN VARCHAR2,
p_sn_control_code IN NUMBER,
p_as_alpha_prefix IN VARCHAR2,
p_transaction_header_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2)
IS
l_source_line_id NUMBER;
Select distinct segment1
From mtl_system_items
Where inventory_item_id = p_inventory_item_id
and organization_id = p_warehouse_id;
Select item_id, item_um
From ic_item_mst
Where item_no = l_opm_item_no;
Select lot_id
From ic_lots_mst
Where lot_no = p_lot_number
And item_id = l_opm_item_id
And lot_id <> 0
And sublot_no = p_sublot_no;
Select lot_id
From ic_lots_mst
Where lot_no = p_lot_number
And item_id = l_opm_item_id
And lot_id <> 0
And sublot_no is null;
Select location
From ic_loct_mst
Where inventory_location_id = p_locator_id;
Select whse_code
From ic_whse_mst
Where mtl_organization_id = p_warehouse_id;
Select count(*)
From ic_tran_pnd
Where item_id = l_opm_item_id
And lot_id = l_opm_lot_id
And line_id = p_line_rec.line_id
And doc_type = 'OMSO'
And delete_mark = 0
And completed_ind = 0;
Select count(*)
From ic_tran_pnd
Where item_id = l_opm_item_id
And lot_id = 0
And line_id = p_line_rec.line_id
And doc_type = 'OMSO'
And delete_mark = 0
And completed_ind = 0;
l_update_tolerance_flag varchar2(1);
x_update_tolerance_flag => l_update_tolerance_flag,
x_ship_tolerance => l_new_tolerance_below,
x_ship_beyond_tolerance => l_ship_beyond_flag,
x_shipped_within_tolerance => l_fulfilled_flag,
x_config_broken => l_proportion_broken_flag,
x_return_status => l_cal_tolr_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
UPDATE oe_order_lines_all
SET shipped_quantity = NULL
,actual_shipment_date = NULL
,lock_control = lock_control + 1
WHERE top_model_line_id = p_line_rec.top_model_line_id
AND shippable_flag = 'N'
AND shipped_quantity is not NULL;
oe_debug_pub.add('Updated non-shippable lines of TSO ...',5);
SELECT max(line_id)
INTO l_new_line_id
FROM oe_order_lines_all
WHERE header_id = p_line_rec.header_id --Bug2489150
AND split_from_line_id = p_line_rec.line_id
AND split_by = 'SYSTEM'; -- Bug-2437391
update oe_drop_ship_sources
set line_id = l_new_line_id
where line_id = p_line_rec.line_id;
insert_oe_drop_ship_source(p_line_rec.line_id, l_new_line_id);
oe_debug_pub.add( 'After inserting drop ship source record ' , 5 ) ;
SELECT requisition_header_id, po_header_id, po_release_id
INTO l_req_header_id,l_po_header_id, l_po_release_id --bug 5328526
FROM oe_drop_ship_sources
WHERE line_id = p_line_id
AND header_id = p_header_id;
SELECT po_header_id, po_release_id
INTO l_po_header_id, l_po_release_id --bug 5328526
FROM oe_drop_ship_sources ds,oe_order_lines l
WHERE ds.header_id = l_header_id
AND l.item_type_code = 'CONFIG'
AND l.line_id = ds.line_id
AND l.ato_line_id = l_ato_line_id;
SELECT po_header_id, po_release_id
INTO l_po_header_id, l_po_release_id --bug 5328526
FROM oe_drop_ship_sources
WHERE line_id = l_line_id
AND header_id = l_header_id;
SELECT drop_ship_flag
INTO l_drop_ship_flag
FROM oe_drop_ship_sources l,po_requisition_lines_all rl
WHERE l.line_id = l_line_id
AND l.header_id = l_header_id
AND l.requisition_line_id = rl.requisition_line_id;