[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';
SELECT 'Check for records in RSL'
INTO x_temp
FROM rcv_shipment_lines
where shipment_line_id = x_shipment_line_id;
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');
SELECT 'Check for records in RSL'
INTO x_temp
FROM rcv_shipment_lines
where shipment_line_id = p_shipment_line_id;
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 quantity,
po_distribution_id
FROM MTL_SUPPLY
WHERE supply_type_code = 'SHIPMENT'
AND po_line_location_id = p_po_line_location_id
AND shipment_line_id = p_shipment_line_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_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 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 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' );
/* Update last distribution in case of over receipt across the pay item */
select Sum(quantity), Max(po_distribution_id)
into l_sum_qty, 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 mtl_supply
set quantity = quantity + (p_quantity_shipped - l_sum_qty),
change_flag = 'Y'
where supply_type_code = 'SHIPMENT'
and po_distribution_id = l_distribution_id
and shipment_line_id = p_shipment_line_id;
/* If quantity on Work Confirmation is greater than the distribution quantity, do not update
mtl_supply for distribution */
IF ( l_remaining_qty > 0) THEN
l_wc_qty := l_remaining_qty;
/* If quantity on Work Confirmation is less than the distribution quantity, delete 'SHIPMENT' supply
for the distribution and re-create 'PO' supply. Re-create PO supply for all subsequent distributions */
UPDATE mtl_supply
SET quantity = (quantity + l_remaining_qty),
change_flag = 'Y'
where supply_type_code = 'SHIPMENT'
and po_distribution_id = l_distribution_id;
insert into mtl_supply
(supply_type_code,
supply_source_id,
last_updated_by,
last_update_date,
last_update_login,
created_by,
creation_date,
po_header_id,
po_line_id,
po_line_location_id,
po_distribution_id,
po_release_id,
item_id,
item_revision,
quantity,
unit_of_measure,
receipt_date,
need_by_date,
destination_type_code,
location_id,
to_organization_id,
to_subinventory,
change_flag)
select 'PO',
pd.po_distribution_id,
pd.last_updated_by,
pd.last_update_date,
pd.last_update_login,
pd.created_by,
pd.creation_date,
pd.po_header_id,
pd.po_line_id,
pd.line_location_id,
pd.po_distribution_id,
pd.po_release_id,
pl.item_id,
pl.item_revision,
-(l_remaining_qty),
pl.unit_meas_lookup_code,
nvl(pll.promised_date,pll.need_by_date),
nvl(pll.promised_date,pll.need_by_date),
pd.destination_type_code,
pd.deliver_to_location_id,
pd.destination_organization_id,
pd.destination_subinventory,
'Y'
from po_distributions_all pd,
po_line_locations_all pll,
po_lines_all pl
where pd.po_distribution_id = dist_info.po_distribution_id
and pll.line_location_id = pd.line_location_id
and pl.po_line_id = pd.po_line_id
and nvl(pll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
and nvl(pll.closed_code, 'OPEN') <> 'CLOSED'
and nvl(pll.closed_code, 'OPEN') <> 'CLOSED FOR RECEIVING'
and nvl(pll.cancel_flag, 'N') = 'N'
and nvl(pll.approved_flag, 'Y') = 'Y'
and pll.quantity is not NULL
and not exists
(select 'Supply Exists'
from mtl_supply ms1
where ms1.supply_type_code = 'PO'
and ms1.supply_source_id = pd.po_distribution_id);
'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;
SELECT Nvl(quantity,0),
Nvl(amount,0),
Nvl(requested_amount,0),
Nvl(material_stored_amount,0)
INTO l_trx_quantity,
l_trx_amount,
l_requested_amount,
l_material_stored_amount
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_interface_transaction_id
AND shipment_line_id = p_shipment_line_id;
update rcv_shipment_lines
set quantity_shipped = quantity_shipped + l_trx_quantity,
amount_shipped = amount_shipped + l_trx_amount,
requested_amount = requested_amount + l_requested_amount,
material_stored_amount = material_stored_amount + l_material_stored_amount
WHERE shipment_line_id = p_shipment_line_id;
SELECT transaction_id,Nvl(quantity,0)
FROM rcv_transactions
WHERE shipment_header_id = p_shipment_header_id
AND po_line_location_id = p_po_line_location_id
AND transaction_type = p_parent_transaction_type;
SELECT transaction_id,Nvl(amount,0)
FROM rcv_transactions
WHERE shipment_header_id = p_shipment_header_id
AND po_line_location_id = p_po_line_location_id
AND transaction_type = p_parent_transaction_type;