[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PHC.HAZARD_CLASS
FROM PO_HAZARD_CLASSES PHC, MTL_SYSTEM_ITEMS_VL MSI
WHERE MSI.INVENTORY_ITEM_ID= P_INVENTORY_ITEM_ID
and MSI.ORGANIZATION_ID = P_ORGANIZATION_ID
and MSI.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
and SYSDATE < (NVL(PHC.INACTIVE_DATE,sysdate));
SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE
FROM hr_all_organization_units hou,
mtl_parameters mp
WHERE hou.organization_id = Plant
AND NVL(hou.date_to, SYSDATE+1) >= SYSDATE
and mp.organization_id = hou.organization_id;
l_argument_string := 'select XMLELEMENT("DispenseDispatch", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''''||:x ||'' ''),
XMLTYPE('''' || :y||'' ''),
XMLTYPE('''' || :z||'' ''),
XMLTYPE('''' ||:xx||'' ''),
XMLTYPE(''''|| :yy ||'' '')
)),
XMLAGG(XMLELEMENT("DispatchDetails",
XMLFOREST(:orgName as OrganizationName,
RES.subinventory_code as Location ,
RES.INVENTORY_ITEM_ID as ITEM_ID,
MSI.CONCATENATED_SEGMENTS as ITEM ,
MSI.description as Description ,
flYesNo.meaning AS HAZARDOUS_MATERIAL_FLAG,
decode(nvl(MSI.HAZARD_CLASS_ID,-1),-1,null,
GMO_DISP_CON_PKG.GET_HAZARD_CLASS_NAME (GMDL.ORGANIZATION_ID,
GMDL.INVENTORY_ITEM_ID)) AS HAZARD_CLASS,
RES.reservation_id as RESERVATION_ID ,
RES.LOT_NUMBER as LOT,
GBH.BATCH_NO as BatchNo,
GBSI.OPERATION as Operation,
GMO_DISPENSE_PVT.GET_PENDING_DISPENSE_QTY(RES.reservation_id,RES.inventory_item_id,
RES.ORGANIZATION_ID,conf.recipe_id,GMDL.MATERIAL_DETAIL_ID,res.primary_uom_code,
res.primary_reservation_quantity,GMDL.plan_qty,GMDL.dtl_um,
RES.LOT_NUMBER) REQUIREDQUANTITY,
conf.DISPENSE_UOM UOM ,
GBSI.BATCHSTEP_NO as BatchStepNo,
RES.primary_uom_code as PRIMARY_UOM ,
FND_DATE.DATE_TO_DISPLAYDT(GMDL.MATERIAL_REQUIREMENT_DATE, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) as REQUIREMENT_DATE)
) order by GMDL.MATERIAL_REQUIREMENT_DATE desc
)
)
FROM
GME_BATCH_HEADER GBH,
GME_MATERIAL_DETAILS GMDL,
MTL_RESERVATIONS RES,
MTL_SYSTEM_ITEMS_VL MSI,
gmo_dispense_config conf,
gmo_dispense_config_inst conf_inst,
FND_LOOKUPS flYesNo,
(SELECT GBSI.MATERIAL_DETAIL_ID, GBSI.BATCHSTEP_ID,
GBS.BATCHSTEP_NO , GMO.OPRN_NO OPERATION
FROM
GME_BATCH_STEPS GBS,
GME_BATCH_STEP_ITEMS GBSI,
GMD_OPERATIONS GMO
WHERE GBS.BATCHSTEP_ID = GBSI.BATCHSTEP_ID
AND GBS.OPRN_ID = GMO.OPRN_ID) GBSI
WHERE RES.DEMAND_SOURCE_TYPE_ID=5
AND RES.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND RES.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND GBH.BATCH_ID = GMDL.BATCH_ID
AND GBH.BATCH_STATUS in (1,2)
AND RES.DEMAND_SOURCE_HEADER_ID = GBH.BATCH_ID
AND RES.DEMAND_SOURCE_LINE_ID = GMDL.MATERIAL_DETAIL_ID
AND conf_inst.ENTITY_KEY = GMDL.MATERIAL_DETAIL_ID
AND conf_inst.ENTITY_NAME = ''MATERIAL_DETAILS_ID''
AND conf_inst.DISPENSE_CONFIG_ID = conf.CONFIG_ID
AND GMDL.MATERIAL_DETAIL_ID = GBSI.MATERIAL_DETAIL_ID(+)
and flYesNo.LOOKUP_CODE = nvl(MSI.HAZARDOUS_MATERIAL_FLAG,''N'')
and flYesNo.LOOKUP_TYPE = ''GMO_YES_NO''
and gmdl.line_type = -1
and gme_api_grp.IS_RESERVATION_FULLY_SPECIFIED(res.reservation_id) = 1
and gmo_dispense_pvt.is_dispense_required(res.reservation_id, msi.inventory_item_id,
gbh.organization_id, null, GMDL.MATERIAL_DETAIL_ID, res.primary_reservation_quantity,
res.primary_uom_code,gmdl.plan_qty,gmdl.dtl_um, res.lot_number ) = ''T''
AND GBH.organization_id =:a ';
SELECT hou.name ORGANIZATION_NAME , mp.organization_code ORGANIZATION_CODE
FROM hr_all_organization_units hou,
mtl_parameters mp
WHERE hou.organization_id = Plant
AND NVL(hou.date_to, SYSDATE+1) >= SYSDATE
and mp.organization_id = hou.organization_id;
l_argument_string := 'select XMLELEMENT("DispenseHistory", XMLCONCAT(XMLSEQUENCETYPE(XMLTYPE(''''||:x||'' ''),
XMLTYPE(''''||:y||'' ''),
XMLTYPE(''''||:z||'' ''),
XMLTYPE(''''||:xx||'' ''),
XMLTYPE(''''||:yy||'' ''),
XMLTYPE(''''||:zz||'' '')
)),
XMLAGG(XMLELEMENT("DispenseHistoryDetails",
XMLFOREST(:orgName as OrganizationName ,
GMDL.subinventory_code as Location ,
GMDL.inventory_item_id as Item_id ,
MSI.CONCATENATED_SEGMENTS as Item ,
MSI.description as Description ,
GMDL.dispense_number DISPENSE_NO ,
GMDL.lot_number as Lot,
gbh.batch_no as BatchNo,
gbsi.batchstep_no as BatchStepNo ,
GMD.LINE_NO as LINE_NO ,
gmo_dispense_pvt.get_net_disp_dispensed_qty(GMDL.dispense_id) as NetDispensedQuantity ,
FND_DATE.DATE_TO_DISPLAYDT(GMDL.dispensed_date, FND_TIMEZONES.GET_SERVER_TIMEZONE_CODE) as DispensedDate ,
GMDL.required_qty as RequiredQuantity ,
lkup.meaning as DispensingMode,
GMDL.dispense_uom DispensedUOM ,
GMDL.erecord_id ErecordID,
gbsi.oprn_no as Operation,
(select nvl(sum(nvl(undispensed_qty,0)),0) from gmo_material_undispenses where dispense_id = GMDL.dispense_id and GMDL.material_status <> ''REVDISPONLY'') as reverse_dispensed_qty ,
(select nvl(sum(material_loss),0) from gmo_material_undispenses where dispense_id = GMDL.dispense_id and GMDL.material_status <> ''REVDISPONLY'') as Material_loss,
GMO_UTILITIES.GET_USER_DISPLAY_NAME(GMDL.created_by) Operator)
) order by GMDL.dispensed_date desc
)
)
from
GMO_MATERIAL_DISPENSES GMDL,
GME_BATCH_HEADER gbh ,
MTL_SYSTEM_ITEMS_VL MSI,
Gme_Material_details GMD,
fnd_lookups lkup,
(SELECT GBS.BATCHSTEP_ID,
GBS.BATCHSTEP_NO ,
GMDOP.OPRN_NO ,
GBSI.MATERIAL_DETAIL_ID
FROM
GME_BATCH_STEPS GBS,
GME_BATCH_STEP_ITEMS GBSI,
GMD_OPERATIONS GMDOP
WHERE GBS.BATCHSTEP_ID = GBSI.BATCHSTEP_ID
AND GBS.OPRN_ID = GMDOP.OPRN_ID) GBSI
where gbh.batch_id = GMDL.batch_id
and gbh.organization_id = GMDL.organization_id
and GMDL.batch_step_id = GBSI.BATCHSTEP_ID(+)
and GMDL.material_detail_id = gbsi.material_detail_id (+)
and MSI.inventory_item_id = GMDL.inventory_item_id
and MSI.organization_id = GMDL.organization_id
and gmd.MATERIAL_DETAIL_ID = GMDL.MATERIAL_DETAIL_ID
and lkup.lookup_type = ''GMO_DISPENSE_MODE''
and lkup.lookup_code = GMDL.dispensing_mode
AND gbh.organization_id =:a';