[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update_po_shipment;
PROCEDURE update_po_shipment IS
CURSOR CR_SHIPMENTS IS
Select pll.secondary_quantity_shipped,
pll.unit_meas_lookup_code,
pl.item_id,
pll.secondary_unit_of_measure,
pll.po_header_id,
pll.po_line_id,
pll.line_location_id,
pll.quantity_shipped,
pll.ship_to_organization_id, /* Added for 10243209 */
pll.quantity_cancelled
from po_line_locations_all pll,
po_lines_all pl,
mtl_parameters mp
where pll.secondary_unit_of_measure is not null
AND pll.po_header_id = pll.po_header_id
AND pll.po_line_id = pl.po_line_id
AND pll.ship_to_organization_id = mp.organization_id
AND pll.shipment_type in ('STANDARD', 'PLANNED', 'BLANKET')
AND (
(nvl(pll.secondary_quantity_shipped,0) = 0 and nvl(pll.quantity_shipped,0) <> 0)
OR
(nvl(pll.secondary_quantity_cancelled,0) = 0 and nvl(pll.quantity_cancelled,0) <> 0)
)
AND mp.process_enabled_flag = 'Y'
FOR UPDATE OF secondary_quantity_shipped;
select organization_id
from ic_whse_mst
where mtl_organization_id = p_org_id
and subinventory_ind_flag = 'Y'
and nvl(migrated_ind, 0) = 1 ;
UPDATE po_line_locations_all
SET secondary_quantity_shipped = nvl(l_secondary_quantity_shipped,secondary_quantity_shipped),
secondary_quantity_cancelled = nvl(l_secondary_quantity_cancelled,secondary_quantity_cancelled),
ship_to_organization_id = nvl(l_migrated_org_id, ship_to_organization_id) /* 10243209 */
WHERE CURRENT OF cr_shipments;
insert into gml_po_mig_errors
(migration_type,po_header_id,po_line_id,line_location_id,
transaction_id, shipment_header_id,shipment_line_id,
column_name,table_name,error_message,
creation_date,last_update_date)
values ('CONVERGENCE',cr_rec.po_header_id,cr_rec.po_line_id,cr_rec.line_location_id,
NULL, NULL, NULL,
'SECONDARY_QUANTITY_SHIPPED','PO_LINE_LOCATIONS_ALL',
'ERROR DERIVING SECONDARY QUANTITY SHIPPED FROM QUANTITY SHIPPED',sysdate,sysdate);
END update_po_shipment;
SELECT *
FROM gr_property_values_tl
WHERE language = userenv('LANG') and
property_id = 'UNCLSS';
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM po_hazard_classes_tl
WHERE hazard_class = v_hazard_class);
SELECT PO_HAZARD_CLASSES_S.nextval
FROM sys.dual;
SELECT *
FROM gr_property_values_tl
WHERE language in (SELECT language_code
FROM fnd_languages
WHERE language_code <> userenv('LANG')
AND installed_flag in ('I','B'))
AND value = v_hazard_class;
/* Select hazard classes that have not yet been migrated */
OPEN c_get_hazard_classes;
PO_HAZARD_CLASSES_PKG.insert_row (
X_ROWID => l_rowid,
X_HAZARD_CLASS_ID => l_seq,
X_HAZARD_CLASS => l_hazard_class_rec.value,
X_DESCRIPTION => l_hazard_class_rec.meaning,
X_INACTIVE_DATE => NULL,
X_CREATION_DATE => l_hazard_class_rec.CREATION_DATE,
X_CREATED_BY => l_hazard_class_rec.CREATED_BY,
X_LAST_UPDATE_DATE => l_hazard_class_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_hazard_class_rec.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => l_hazard_class_rec.LAST_UPDATE_LOGIN,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_REQUEST_ID => NULL );
/* Update the descriptions with the values from Regulatory */
UPDATE po_hazard_classes_tl
SET description = l_translated_rec.meaning,
source_lang = l_translated_rec.source_lang,
creation_date = l_translated_rec.creation_date,
created_by = l_translated_rec.created_by,
last_update_date = l_translated_rec.last_update_date,
last_updated_by = l_translated_rec.last_updated_by,
last_update_login = l_translated_rec.last_update_login
WHERE language = l_translated_rec.language
AND hazard_class_id = l_seq;
END LOOP; /* Number or records selected */
SELECT DISTINCT(number_value)
FROM gr_item_properties
WHERE migration_ind IS NULL and
property_id = 'UNNUMB' and
label_code = '14001';
SELECT *
FROM gr_item_properties
WHERE migration_ind IS NULL and
number_value = l_un_number and
property_id = 'UNNUMB' and
label_code = '14001'
ORDER BY creation_date;
SELECT 1
FROM sys.dual
WHERE EXISTS (SELECT 1
FROM po_un_numbers_tl
WHERE un_number = 'UN'||v_un_number);
SELECT PO_UN_NUMBERS_S.nextval
FROM sys.dual;
/* Select un numbers that have not yet been migrated */
OPEN c_get_un_numbers;
PO_UN_NUMBERS_PKG.insert_row (
X_ROWID => l_rowid,
X_UN_NUMBER_ID => l_seq,
X_UN_NUMBER => 'UN'||TO_CHAR(l_un_number_rec.number_value),
X_DESCRIPTION => 'UN'||TO_CHAR(l_un_number_rec.number_value),
X_HAZARD_CLASS_ID => l_hazard_class_id,
X_INACTIVE_DATE => NULL,
X_CREATION_DATE => l_un_number_rec.CREATION_DATE,
X_CREATED_BY => l_un_number_rec.CREATED_BY,
X_LAST_UPDATE_DATE => l_un_number_rec.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => l_un_number_rec.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => l_un_number_rec.LAST_UPDATE_LOGIN,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL,
X_REQUEST_ID => NULL );
UPDATE gr_item_properties
SET migration_ind = l_mig_status
WHERE number_value = l_un_number_rec.number_value and
property_id = 'UNNUMB' and
label_code = '14001';
END LOOP; /* Number or records selected */