[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
sqltxt VARCHAR2(9999); -- SQL select statement
JTF_DIAGNOSTIC_COREAPI.insert_style_sheet;
sqltxt := ' SELECT mp.organization_code' ||
' || '' (''' ||
' ||x.organization_id' ||
' ||'')'' "Organization|Code (Id)" ,' ||
' mif.item_number' ||
' || '' (''' ||
' ||x.inventory_item_id' ||
' ||'')'' "Item (Id)" ,' ||
' x.subinventory_code "Subinv" ,' ||
' x.locator_id "Loc id" ,' ||
' x.lot_number "Lot Number" ,' ||
' mtsv.status_code' ||
' ||'' (''' ||
' ||x.status_id' ||
' ||'')'' "StatusCode (Id)"' ||
' FROM' ||
' ( SELECT organization_id ,' ||
' inventory_item_id,' ||
' subinventory_code,' ||
' locator_id ,' ||
' lot_number ,' ||
' status_id ,' ||
' COUNT(*) C1' ||
' FROM mtl_onhand_quantities_detail moqd1' ||
' WHERE lpn_id is null ' ||
' GROUP BY organization_id ,' ||
' inventory_item_id ,' ||
' subinventory_code ,' ||
' locator_id ,' ||
' lot_number ,' ||
' status_id' ||
' ) X ,' ||
' (SELECT organization_id ,' ||
' inventory_item_id,' ||
' subinventory_code,' ||
' locator_id ,' ||
' lot_number ,' ||
' COUNT(*) C2' ||
' FROM mtl_onhand_quantities_detail moqd2' ||
' WHERE lpn_id is null ' ||
' GROUP BY organization_id ,' ||
' inventory_item_id ,' ||
' subinventory_code ,' ||
' locator_id ,' ||
' lot_number' ||
' ) Y ,' ||
' mtl_parameters mp ,' ||
' mtl_item_flexfields mif ,' ||
' mtl_material_statuses_vl mtsv' ||
' WHERE mp.organization_id = x.organization_id' ||
' AND x.organization_id = mif.organization_id' ||
' AND x.inventory_item_id = mif.inventory_item_id' ||
' AND x.organization_id = y.organization_id' ||
' AND x.inventory_item_id = y.inventory_item_id' ||
' AND x.subinventory_code = y.subinventory_code' ||
' AND NVL(x.locator_id,-9999) = NVL(y.locator_id,-9999)' ||
' AND NVL(x.lot_number,''@@@@'') = NVL(y.lot_number,''@@@@'')' ||
' AND x.C1 <> y.C2' ||
' AND mtsv.status_id = x.status_id' ||
' AND mp.default_status_id IS NOT NULL' ||
' AND mif.serial_number_control_code IN (1,6) ';