[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_errors(p_rti_id IN NUMBER,
p_group_id IN NUMBER,
p_header_interface_id IN NUMBER,
p_column_name IN VARCHAR2,
p_table_name IN VARCHAR2,
p_mesg_owner IN VARCHAR2,
p_Error_Message IN VARCHAR2,
p_Error_Message_name IN VARCHAR2,
p_TokenName1 IN VARCHAR2 DEFAULT NULL,
p_TokenValue1 IN VARCHAR2 DEFAULT NULL,
p_TokenName2 IN VARCHAR2 DEFAULT NULL,
p_TokenValue2 IN VARCHAR2 DEFAULT NULL,
p_TokenName3 IN VARCHAR2 DEFAULT NULL,
p_TokenValue3 IN VARCHAR2 DEFAULT NULL,
p_TokenName4 IN VARCHAR2 DEFAULT NULL,
p_TokenValue4 IN VARCHAR2 DEFAULT NULL
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT group_id,header_interface_id
INTO l_group_id,l_header_interface_id
FROM rcv_transactions_interface
WHERE interface_transaction_id = p_rti_id ;
INSERT INTO po_interface_errors(Interface_Type,
Interface_Transaction_Id,
column_name,
table_name,
error_message,
Error_Message_name,
processing_date,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_by,
Last_Update_Login,
Interface_Header_ID,
Interface_Line_Id,
Interface_Distribution_Id,
Request_Id,
Program_Application_id,
Program_Id,
Program_Update_date,
BATCH_ID)
VALUES
(l_interface_type,
po_interface_errors_s.NEXTVAL,
p_column_name,
p_table_name,
l_error_message,
p_Error_Message_name,
SYSDATE,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
NVL(p_header_interface_id,l_header_interface_id),
p_rti_id,
null,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
SYSDATE,
nvl(p_group_id,l_group_id));
asn_debug.put_line('Unhandled exception in gml_opm_roi_grp.insert_errors=>'||substr(sqlerrm,1,200));
END insert_errors;
Select sum(loct_onhand)
From ic_loct_inv ilv,ic_loct_mst ilm,ic_whse_mst iwm
Where ilv.item_id = p_opm_item_id
and ilv.whse_code = iwm.whse_code
and ilv.location = ilm.location
and iwm.mtl_organization_id = p_organization_id
and ilm.inventory_location_id = p_locator_id
and ilv.lot_id = nvl(p_lot_id,ilv.lot_id)
having sum(loct_onhand) > 0;
Select sum(loct_onhand)
From ic_loct_inv ilv,ic_whse_mst iwm
Where ilv.item_id = p_opm_item_id
and ilv.whse_code = iwm.whse_code
and ilv.location = l_default_location
and iwm.mtl_organization_id = p_organization_id
and ilv.lot_id = nvl(p_lot_id,ilv.lot_id)
having sum(loct_onhand)> 0;
Select noninv_ind
From ic_item_mst
Where item_id = p_opm_item_id;
insert_errors( p_rti_id => p_rti_id,
p_group_id => NULL,
p_header_interface_id => NULL,
p_column_name => 'PRIMARY_QUANTITY',
p_table_name => l_table_name,
p_error_message => 'UNHANDLED EXCEPTION IN GML_OPM_ROI_GRP.VALIDATE_QUANTITY_ONHAND :' || l_progress||'-' ||
substr(sqlerrm,1,1000),
p_mesg_owner => NULL,
p_Error_Message_name => NULL);
select item_um2,item_id into l_item_um2,l_opm_item_id from ic_item_mst
where item_no = x_opm_record.item_num ;
select iim.item_no , iim.item_um2 , iim.item_id into x_opm_record.item_num,l_item_um2 , l_opm_item_id
from mtl_system_items_b msi,ic_item_mst iim
where msi.inventory_item_id = x_opm_record.item_id
and msi.organization_id = x_opm_record.to_organization_id
and msi.segment1 = iim.item_no ;
insert_errors(p_rti_id => x_opm_record.rti_id,
p_group_id => x_opm_record.group_id,
p_header_interface_id => x_opm_record.header_interface_id,
p_column_name => 'ITEM_ID',
p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
p_error_message => NULL,
p_mesg_owner => 'GML',
p_Error_Message_name => 'GML_OPM_ITEM_NOT_EXIST' );
insert_errors(p_rti_id => x_opm_record.rti_id,
p_group_id => x_opm_record.group_id,
p_header_interface_id => x_opm_record.header_interface_id,
p_column_name => 'PRIMARY_QUANTITY',
p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
p_error_message => FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),
p_mesg_owner => NULL,
p_Error_Message_name => NULL );
SELECT muom.unit_of_measure INTO x_opm_record.secondary_unit_of_measure
FROM mtl_units_of_measure muom
WHERE muom.uom_code = x_opm_record.secondary_uom_code ;
insert_errors(p_rti_id => x_opm_record.rti_id,
p_group_id => x_opm_record.group_id,
p_header_interface_id => x_opm_record.header_interface_id,
p_column_name => 'SECONDARY_QUANTITY',
p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
p_error_message => l_msg_data,
p_mesg_owner => NULL,
p_Error_Message_name => NULL );
SELECT muom.uom_code INTO x_opm_record.secondary_uom_code
FROM mtl_units_of_measure muom
WHERE muom.unit_of_measure = x_opm_record.secondary_unit_of_measure ;
insert_errors( p_rti_id => x_opm_record.rti_id,
p_group_id => x_opm_record.group_id,
p_header_interface_id => x_opm_record.header_interface_id,
p_column_name => 'OPM_COLUMNS',
p_table_name => 'RCV_TRANSACTIONS_INTERFACE',
p_error_message => 'UNHANDLED EXCEPTION IN VALIDATE_OPM_PARAMETERS :' || l_progress||'-' ||
substr(sqlerrm,1,1000),
p_mesg_owner => NULL,
p_Error_Message_name => NULL);
SELECT lot_id,inactive_ind,delete_mark
FROM ic_lots_mst
WHERE lot_no = p_lot_attribute_rec.lot_no
AND item_id = p_lot_attribute_rec.opm_item_id ;
SELECT lot_id,inactive_ind,delete_mark
FROM ic_lots_mst
WHERE lot_no = p_lot_attribute_rec.lot_no
AND item_id = p_lot_attribute_rec.opm_item_id
AND sublot_no = p_lot_attribute_rec.sublot_no ;
l_delete_mark BINARY_INTEGER;
SELECT sublot_ctl,shelf_life
INTO l_sublot_ctl,l_shelf_life
FROM ic_item_mst_b
WHERE item_id = p_lot_attribute_rec.opm_item_id ;
select 'x' into l_temp
from sy_reas_cds
where reason_code = p_lot_attribute_rec.reason_code
and delete_mark = 0 ;
FETCH Cur_lot_ctrl_lot INTO p_lot_attribute_rec.lot_id,l_inactive_ind,l_delete_mark;
FETCH Cur_sublot_ctrl_lot INTO p_lot_attribute_rec.lot_id,l_inactive_ind,l_delete_mark;
ELSIF l_delete_mark = 1 THEN
FND_MESSAGE.SET_NAME('GML', 'PO_LOT_DELETED');
SELECT shelf_life_code INTO l_shelf_life_code
FROM mtl_system_items_b
WHERE inventory_item_id = p_inventory_item_id
AND organization_id = p_organization_id ;
SELECT p_trans_date + l_shelf_life into l_expire_date FROM DUAL ;
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => 'EXPIRATION_DATE',
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => null,
p_mesg_owner => 'GML',
p_Error_Message_name => 'GML_OVERRIDE_EXP_DATE',
p_TokenName1 => 'LOT_NO',
p_Tokenvalue1 => p_lot_attribute_rec.lot_no,
p_TokenName2 => 'SUBLOT_NO',
p_Tokenvalue2 => p_lot_attribute_rec.sublot_no);
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => 'EXPIRATION_DATE',
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => null,
p_mesg_owner => 'GML',
p_Error_Message_name => 'GML_OVERRIDE_EXP_DATE',
p_TokenName1 => 'LOT_NO',
p_Tokenvalue1 => p_lot_attribute_rec.lot_no,
p_TokenName2 => 'SUBLOT_NO',
p_Tokenvalue2 => p_lot_attribute_rec.sublot_no);
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => null,
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => 'UNHANDLED EXCEPTION IN VALIDATE_LOT_ATTRIBUTES :' || l_progress||'-' ||
substr(sqlerrm,1,1000),
p_mesg_owner => NULL,
p_Error_Message_name => NULL);
select reason_code
From sy_reas_cds
Where delete_mark = 0
and (l_reason_code_security = 'Y')
AND (reason_code in (select reason_code from gma_reason_code_security
where (doc_type = 'PORC' or doc_type IS NULL) and
(responsibility_id = FND_GLOBAL.RESP_id or responsibility_id IS NULL)))
Union all
Select reason_code
From sy_reas_cds
Where delete_mark = 0;
SELECT whse_code INTO l_whse_code
FROM IC_WHSE_MST
WHERE mtl_organization_id = p_organization_id;
SELECT lot_status,status_ctl
INTO l_default_lot_status,l_item_status_ctl
FROM IC_ITEM_MST
WHERE item_id = p_lot_attribute_rec.opm_item_id;
SELECT location INTO l_location
FROM IC_LOCT_MST
WHERE inventory_location_id = p_locator_id;
SELECT lot_status, loct_onhand
INTO l_inv_lot_status, l_inv_loct_onhand
FROM ic_loct_inv ilv
WHERE ilv.item_id = p_lot_attribute_rec.opm_item_id
AND ilv.lot_id = p_lot_attribute_rec.lot_id
AND ilv.whse_code = l_whse_code
AND ilv.location = l_location;
SELECT s.co_code,w.orgn_code INTO l_trans_rec.co_code,l_trans_rec.orgn_code
FROM IC_WHSE_MST W,SY_ORGN_MST S
WHERE w.whse_code = l_whse_code
and w.orgn_code = s.orgn_code;
Update IC_LOCT_INV
Set lot_status = l_default_lot_status
Where item_id = p_lot_attribute_rec.opm_item_id
And whse_code = l_whse_code
And location = l_location
And lot_id = p_lot_attribute_rec.lot_id;
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => null,
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => 'UNHANDLED EXCEPTION IN CHECK_LOT_STATUS :' || l_progress||'-' ||
substr(sqlerrm,1,1000),
p_mesg_owner => NULL,
p_Error_Message_name => NULL);
Select pvm.vendor_no INTO l_new_lot_rec.shipvendor_no
From po_Vend_mst pvm,ic_whse_mst iwm,sy_orgn_mst som
Where pvm.OF_VENDOR_ID = p_vendor_id
and pvm.OF_VENDOR_SITE_ID = p_vendor_site_id
and iwm.mtl_organization_id = p_organization_id
and iwm.orgn_code = som.orgn_code
and som.co_code = pvm.co_code;
SELECT um_type , um_code INTO l_from_um_type , l_from_um_code
FROM SY_UOMS_MST
WHERE unit_of_measure = p_from_unit_of_measure ;
SELECT um_type , um_code INTO l_to_um_type , l_to_um_code
FROM SY_UOMS_MST
WHERE unit_of_measure = p_to_unit_of_measure ;
SELECT dualum_ind INTO l_dualum_ind
FROM IC_ITEM_MST_B
WHERE item_no = l_new_lot_rec.item_no ;
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => null,
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => 'UNHANDLED EXCEPTION IN CREATE_NEW_LOT :' || l_progress||'-' ||
substr(sqlerrm,1,1000),
p_mesg_owner => NULL,
p_Error_Message_name => NULL);
Select 1
From oe_lot_serial_numbers
Where (line_id = p_oe_line_id
or line_set_id =
(select line_set_id
from oe_order_lines_all
where line_id = p_oe_line_id
and header_id = p_oe_header_id)
);
Select o.quantity
from oe_lot_serial_numbers o
Where (o.line_id = p_oe_order_line_id
OR
o.line_set_id = x_line_set_id )
AND o.lot_number = p_lot_no
AND o.sublot_number = p_sublot_no ;
SELECT line_set_id INTO x_line_set_id
FROM oe_order_lines_all
WHERE header_id = p_oe_order_header_id
AND line_id = p_oe_order_line_id ;
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => 'LOT_NUMBER',
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => null,
p_mesg_owner => 'GML',
p_Error_Message_name => 'GML_DIFF_RMA_LOT'
);
insert_errors( p_rti_id => p_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => 'TRANSACTION_QUANTITY',
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => null,
p_mesg_owner => 'GML',
p_Error_Message_name => 'GML_DIFF_RMA_QTY',
p_TokenName1 => 'S1',
p_TokenValue1 => x_allowed_quantity
);
l_update_lot_qty BOOLEAN := FALSE;
SELECT sum(itp.trans_qty)
FROM rcv_transactions rt , ic_tran_pnd itp
WHERE
rt.shipment_header_id = l_shipment_header_id
AND rt.shipment_line_id = l_shipment_line_id
AND itp.doc_id = rt.shipment_header_id
AND itp.line_id = rt.transaction_id
AND itp.doc_type = 'PORC'
AND itp.lot_id = p_lot_id ;
Select sum(itp.trans_qty),
itp.doc_id,
itp.line_id
from rcv_transactions rcv,
gml_recv_trans_map grm,
ic_tran_pnd itp
where rcv.transaction_id = l_parent_transaction_id
and rcv.comments = 'OPM RECEIPT'
and rcv.interface_transaction_id = grm.interface_transaction_id
and itp.doc_type = 'RECV'
and grm.recv_id = itp.doc_id
and grm.line_id = itp.line_id
and itp.lot_id = p_lot_id
group by itp.doc_id,itp.line_id
having sum(itp.trans_qty) > 0 ;
Select sum(itp.trans_qty) qty
from ic_tran_pnd itp,
po_Rtrn_dtl d,
po_rtrn_hdr h
where h.return_id = d.return_id
and d.recv_id = l_recv_id
and d.recvline_id = l_recvline_id
and h.delete_mark <> -1
and d.return_id = itp.doc_id
and d.line_id = itp.line_id
and itp.doc_type = 'RTRN'
and itp.lot_id = p_lot_id
and itp.delete_mark <> 1;
SELECT product_transaction_id,
Ltrim(Rtrim(lot_number)) ,
Ltrim(Rtrim(sublot_num)) ,
lot_expiration_date,
transaction_quantity,
primary_quantity,
secondary_quantity,
reason_code,
substr(ltrim(rtrim(supplier_lot_number)),1,32),
substr(description,1,40)
INTO l_rti_id ,
l_lot_no ,
l_sublot_no ,
l_lot_expiration_date ,
l_lot_quantity ,
l_lot_primary_quantity ,
l_lot_secondary_quantity ,
l_reason_code ,
l_vendor_lot_no_on_lot ,
l_lot_desc
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE rowid = p_mtlt_rowid ;
SELECT TRANSACTION_TYPE
, SOURCE_DOCUMENT_CODE
, item_id
, TO_ORGANIZATION_ID
, locator_id
, from_locator_id
, unit_of_measure
, secondary_unit_of_measure
, transaction_date
, vendor_lot_num
, vendor_id
, vendor_site_id
, parent_transaction_id
, shipment_header_id
, shipment_line_id
, Nvl(primary_quantity, quantity)
, validation_flag
, oe_order_header_id
, oe_order_line_id
, quantity
, secondary_quantity
INTO L_TRANSACTION_TYPE
, L_SOURCE_DOCUMENT_CODE
, l_item_id
, l_org_id
, l_to_locator_id
, l_from_locator_id
, l_unit_of_measure
, l_secondary_unit_of_measure
, l_transaction_date
, l_vendor_lot_no_on_line
, l_vendor_id
, l_vendor_site_id
, l_parent_transaction_id
, l_shipment_header_id
, l_shipment_line_id
, l_rti_primary_qty
, l_validation_flag
, l_oe_order_header_id
, l_oe_order_line_id
, l_rti_quantity
, l_rti_secondary_quantity
FROM RCV_TRANSACTIONS_INTERFACE
WHERE INTERFACE_TRANSACTION_ID = l_rti_id;
SELECT count(1) INTO l_same_lot_count
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE
PRODUCT_CODE = 'RCV'
AND PRODUCT_TRANSACTION_ID = l_rti_id
AND Ltrim(Rtrim(lot_number)) = l_lot_no
AND ((sublot_num IS NULL AND l_sublot_no IS NULL) OR (Ltrim(Rtrim(sublot_num)) = l_sublot_no )) ;
SELECT count(1) INTO l_total_no_of_lots
FROM MTL_TRANSACTION_LOTS_TEMP
WHERE
PRODUCT_CODE = 'RCV'
AND PRODUCT_TRANSACTION_ID = l_rti_id ;
UPDATE mtl_transaction_lots_temp
SET secondary_quantity = l_lot_secondary_quantity
WHERE rowid = p_mtlt_rowid ;
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
SELECT shipment_header_id, shipment_line_id
INTO l_shipment_header_id , l_shipment_line_id
FROM rcv_transactions
WHERE transaction_id = l_parent_transaction_id ;
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
select rcv.comments
into l_comment
from rcv_transactions rcv
where rcv.transaction_id = l_parent_transaction_id;
SELECT 'X' INTO l_temp
FROM ic_tran_pnd itp
WHERE
itp.doc_id = l_shipment_header_id
and itp.line_id = l_parent_transaction_id
and itp.doc_type = 'PORC'
and itp.lot_id = p_lot_id ;
SELECT 'X' INTO l_temp
FROM rcv_transactions rcv,gml_recv_trans_map grm,ic_tran_pnd itp
WHERE rcv.transaction_id = l_parent_transaction_id
and rcv.interface_transaction_id = grm.interface_transaction_id
and grm.recv_id = itp.doc_id
and itp.doc_type = 'RECV'
and grm.line_id = itp.line_id
and itp.lot_id = p_lot_id ;
insert_errors(p_rti_id => l_rti_id,
p_group_id => NULL,
p_header_interface_id => NULL,
p_column_name => 'TRANSACTION_QUANTITY',
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),
p_mesg_owner => NULL,
p_error_message_name => NULL );
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
select rcv.comments
into l_comment
from rcv_transactions rcv
where rcv.transaction_id = l_parent_transaction_id;
SELECT 'X' INTO l_temp
FROM ic_tran_pnd itp
WHERE
itp.doc_id = l_shipment_header_id
and itp.line_id = l_parent_transaction_id
and itp.doc_type = 'PORC'
and itp.lot_id = p_lot_id ;
SELECT 'X' INTO l_temp
FROM rcv_transactions rcv,gml_recv_trans_map grm,ic_tran_pnd itp
WHERE rcv.transaction_id = l_parent_transaction_id
and rcv.interface_transaction_id = grm.interface_transaction_id
and grm.recv_id = itp.doc_id
and itp.doc_type = 'RECV'
and grm.line_id = itp.line_id
and itp.lot_id = p_lot_id ;
l_update_lot_qty := TRUE ;
IF l_update_lot_qty THEN
update mtl_transaction_lots_temp
set secondary_quantity = l_lot_secondary_quantity
where rowid = p_mtlt_rowid ;
insert_errors(p_rti_id => l_rti_id,
p_group_id => NULL,
p_header_interface_id => NULL,
p_column_name => NULL,
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => FND_MSG_PUB.GET(p_msg_index => FND_MSG_PUB.G_LAST,p_encoded => 'F'),
p_mesg_owner => NULL,
p_Error_Message_name => NULL );
insert_errors( p_rti_id => l_rti_id,
p_group_id => null,
p_header_interface_id => null,
p_column_name => null,
p_table_name => 'MTL_TRANSACTION_LOTS_INTERFACE',
p_error_message => 'UNHANDLED EXCEPTION IN VALIDATE_OPM_LOT :' || l_progress||'-' ||
substr(sqlerrm,1,1000),
p_mesg_owner => NULL,
p_Error_Message_name => NULL);