[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
X_Program_Update_Date DATE
) IS
X_progress VARCHAR2(4) := '000';
SELECT *
FROM RCV_SHIPMENT_LINES
WHERE rowid = X_Rowid
FOR UPDATE of Shipment_Line_Id NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
PROCEDURE Update_Line_s(
X_Shipment_Line_Id NUMBER,
X_item_revision VARCHAR2,
X_stock_locator_id NUMBER,
X_packing_slip VARCHAR2,
X_comments VARCHAR2,
X_routing_header_id NUMBER,
X_Reason_id NUMBER
) IS
X_progress VARCHAR2(4) := '000';
update rcv_shipment_lines
set item_revision = x_item_revision,
locator_id = x_stock_locator_id,
packing_slip = x_packing_slip,
comments = x_comments,
routing_header_id = x_routing_header_id,
reason_id = x_reason_id
where shipment_line_id = x_shipment_line_id;
po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_Line_s', x_progress, sqlcode);
END Update_Line_s;
PROCEDURE update_wc_line(
p_shipment_line_id IN NUMBER,
p_requested_amount IN NUMBER DEFAULT NULL,
p_material_stored_amount IN NUMBER DEFAULT NULL,
p_amount_shipped IN NUMBER DEFAULT NULL,
p_quantity_shipped IN NUMBER DEFAULT NULL
) IS
X_progress VARCHAR2(4) := '000';
select wf_item_key
into l_itemkey
from rcv_shipment_headers
where shipment_header_id =( select shipment_header_id
from rcv_shipment_lines
where shipment_line_id= p_shipment_line_id);
l_api_name := l_itemkey || ' update_wc_line';
'Enter update_wc_line');
'Before call to update_quantity_amount');
update_quantity_amount(p_shipment_line_id,p_quantity_shipped,p_amount_shipped);
'After call to update_quantity_amount');
update rcv_shipment_lines
set requested_amount = p_requested_amount,
material_stored_amount = p_material_stored_amount,
amount_shipped = p_amount_shipped,
quantity_shipped = p_quantity_shipped,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where shipment_line_id = p_shipment_line_id;
'Leave update_wc_line');
po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_wc_line', x_progress, sqlcode);
END Update_wc_line;
PROCEDURE update_quantity_amount(
p_Shipment_Line_Id IN NUMBER,
p_quantity_shipped IN NUMBER,
p_amount_shipped IN NUMBER
) IS
X_progress VARCHAR2(4) := '000';
SELECT QUANTITY,
UNIT_OF_MEASURE,
nvl(ITEM_ID, -1),
FROM_ORGANIZATION_ID,
TO_ORGANIZATION_ID,
TO_CHAR(RECEIPT_DATE,'DDMMYYYY'),
ROWID
FROM
MTL_SUPPLY
WHERE CHANGE_FLAG = 'Y';
select wf_item_key
into l_itemkey
from rcv_shipment_headers
where shipment_header_id =( select shipment_header_id
from rcv_shipment_lines
where shipment_line_id= p_shipment_line_id);
l_api_name := l_itemkey || ' update_quantity_amount';
'Enter update_quantity_amount');
* to update po_line_locations.
*/
select nvl(quantity_shipped,0),
nvl(amount_shipped,0),
po_line_location_id
into l_orig_qty_shipped,
l_orig_amt_shipped,
l_line_location_id
from rcv_shipment_lines
where shipment_line_id = p_shipment_line_id;
/* Update po_line_location.quantity_shipped */
l_qty_shipped := p_quantity_shipped - l_orig_qty_shipped;
UPDATE po_line_locations_all poll
set poll.quantity_shipped = nvl(poll.quantity_shipped,0) +
l_qty_shipped,
poll.amount_shipped = nvl(poll.amount_shipped,0) +
l_amt_shipped,
poll.last_update_date = sysdate,
poll.last_updated_by = fnd_global.user_id,
poll.last_update_login = fnd_global.login_id
where poll.line_location_id = l_line_location_id;
'After update to poll ' );
/* To update PO supply we need to know the distribution id */
IF (g_asn_debug = 'Y') THEN
debug_log(FND_LOG.LEVEL_STATEMENT,l_api_name,
'p_shipment_line_id '||p_shipment_line_id );
select po_distribution_id
into l_distribution_id
from mtl_supply
where supply_type_code = 'SHIPMENT'
and po_line_location_id = l_line_location_id
and shipment_line_id = p_shipment_line_id;
/* Update the shipment qty with the new quantity. */
update mtl_supply
set quantity = quantity + l_qty_shipped,
change_flag = 'Y'
where supply_type_code = 'SHIPMENT'
and po_line_location_id = l_line_location_id
and shipment_line_id = p_shipment_line_id;
'After update to shipment supply' );
/* Update PO supply in mtl_supply */
update mtl_supply
set quantity = quantity + l_qty_shipped,
change_flag = 'Y'
where supply_type_code = 'PO'
and po_distribution_id = l_distribution_id;
'After update to PO supply' );
'Before Delete' );
delete from mtl_supply
where rowid = l_supply_rowid;
'After Delete' );
SELECT muom.unit_of_measure, NULL
INTO l_primary_uom,
l_lead_time
FROM mtl_units_of_measure muom,
mtl_units_of_measure tuom
WHERE tuom.unit_of_measure = l_supply_uom
AND tuom.uom_class = muom.uom_class
AND muom.base_uom_flag = 'Y';
SELECT PRIMARY_UNIT_OF_MEASURE,
POSTPROCESSING_LEAD_TIME
INTO l_primary_uom,
l_lead_time
FROM MTL_SYSTEM_ITEMS
WHERE INVENTORY_ITEM_ID = l_supply_item_id
AND ORGANIZATION_ID = l_supply_to_org;
'Before update to mtl_supply.quantity' );
UPDATE MTL_SUPPLY
SET TO_ORG_PRIMARY_QUANTITY = l_primary_qty,
TO_ORG_PRIMARY_UOM = l_primary_uom,
CHANGE_FLAG = NULL,
CHANGE_TYPE = null,
EXPECTED_DELIVERY_DATE =
decode(l_supply_item_id,
-1, TO_DATE(NULL),
TO_DATE(l_supply_receipt_date,'DDMMYYYY')
+ nvl(l_lead_time, 0 ))
WHERE ROWID = l_supply_rowid;
'After update to mtl_supply.quantity' );
'Leave update_quantity_amount' );
po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.Update_quantity_amount', x_progress, sqlcode);
END Update_quantity_amount;
PROCEDURE delete_line_s(
p_Shipment_Line_Id IN NUMBER) IS
X_progress VARCHAR2(4) := '000';
select wf_item_key
into l_itemkey
from rcv_shipment_headers
where shipment_header_id =( select shipment_header_id
from rcv_shipment_lines
where shipment_line_id= p_shipment_line_id);
l_api_name := l_itemkey || ' delete_line_s';
'Enter delete_line_s');
update_quantity_amount(p_shipment_line_id,
0,--quantity_shipped
0); --amount_shipped
'Before Delete '||p_shipment_line_id);
delete from rcv_shipment_lines
where shipment_line_id= p_shipment_line_id;
po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.delete_line_s', x_progress, sqlcode);
END delete_line_s;
PROCEDURE update_approval_status(p_level IN VARCHAR2,
p_approval_status IN VARCHAR2,
p_comments IN VARCHAR2,
p_document_id IN NUMBER) IS
x_progress varchar2(4) := '000';
select wf_item_key
into l_itemkey
from rcv_shipment_headers
where shipment_header_id = p_document_id;
l_api_name := l_itemkey || ' update_approval_status';
'Enter HEADER update_approval_status');
update rcv_shipment_headers
set comments = p_comments,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where shipment_header_id = p_document_id;
select wf_item_key
into l_itemkey
from rcv_shipment_headers
where shipment_header_id =( select shipment_header_id
from rcv_shipment_lines
where shipment_line_id= p_document_id);
l_api_name := l_itemkey || ' update_approval_status';
'Enter LINE update_approval_status');
update rcv_shipment_lines
set approval_status = p_approval_status,
comments = p_comments,
last_update_date = sysdate,
last_updated_by = fnd_global.user_id,
last_update_login = fnd_global.login_id
where shipment_line_id = p_document_id;
'Leave update_approval_status');
po_message_s.sql_error('RCV_SHIPMENT_LINES_PKG.update_approval_status', x_progress, sqlcode);
END update_approval_status;