eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.EAM_MAT_REQ_VALIDATE_PVT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 39


    *             transaction type is Update and the record
    *             does not exist then the return status would be an
    *             error as well. Mesg_Token_Table will carry the
    *             error messsage and the tokens associated with the
    *             message.
    *********************************************************************/

     PROCEDURE Check_Existence
     ( p_eam_mat_req_rec        IN  EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
     , x_old_eam_mat_req_rec    OUT NOCOPY EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
     , x_Mesg_Token_Tbl	        OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
     , x_return_status	        OUT NOCOPY VARCHAR2
        )
     IS
            l_token_tbl         EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;

Line: 91


             (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
        THEN
            l_token_tbl(1).token_name  := 'INVENTORY_ITEM_ID';

Line: 122


                   x_old_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;

Line: 178


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

         begin
           select 1
             into g_dummy
             from wip_operations wo
             where wo.organization_id = p_eam_mat_req_rec.organization_id
             and wo.wip_entity_id = p_eam_mat_req_rec.wip_entity_id
             and wo.operation_seq_num = p_eam_mat_req_rec.operation_seq_num;

Line: 233


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

	-- Fix for bug# 3602041 - Removed check for bom_enabled flag in the where clause of the query
           select 1
             into g_dummy
             from mtl_system_items
            where inventory_item_id = p_eam_mat_req_rec.inventory_item_id
              and organization_id = p_eam_mat_req_rec.organization_id
			  and ( bom_item_type  = 4
              and ( eam_item_type  IS NULL or eam_item_type  = 3 or eam_item_type = 1) );

Line: 249


          select stock_enabled_flag into l_stockable_flag
            from mtl_system_items where
            inventory_item_id =  p_eam_mat_req_rec.inventory_item_id
            and organization_id = p_eam_mat_req_rec.organization_id;

Line: 254


            select count(*) into l_count from mtl_system_items
              where inventory_item_id =  p_eam_mat_req_rec.inventory_item_id
              and organization_id = p_eam_mat_req_rec.organization_id
              and purchasing_enabled_flag = 'Y'
              and purchasing_item_flag = 'Y';

Line: 314


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

         if p_eam_mat_req_rec.quantity_per_assembly < 0 then
           raise fnd_api.g_exc_unexpected_error;

Line: 351


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

        if p_eam_mat_req_rec.supply_subinventory is not null then
         select 1 into g_dummy
           from mtl_secondary_inventories msinv
           where nvl(msinv.disable_date, sysdate+2) > sysdate
           and msinv.organization_id = p_eam_mat_req_rec.organization_id
           and msinv.secondary_inventory_name = p_eam_mat_req_rec.supply_subinventory;

Line: 393


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

         if p_eam_mat_req_rec.supply_locator_id is not null then
          select 1 into g_dummy
            from mtl_item_locations_kfv
            where (disable_date > sysdate or disable_date is null)
            and organization_id = p_eam_mat_req_rec.organization_id
            and subinventory_code = p_eam_mat_req_rec.supply_subinventory
            and inventory_location_id = p_eam_mat_req_rec.supply_locator_id;

Line: 437


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

         if p_eam_mat_req_rec.department_id is null then

           if p_eam_mat_req_rec.operation_seq_num <> 1 then
             raise fnd_api.g_exc_error;

Line: 447


           select 1 into g_dummy
             from wip_operations where
             wip_entity_id = p_eam_mat_req_rec.wip_entity_id and
             organization_id = p_eam_mat_req_rec.organization_id and
             operation_seq_num = p_eam_mat_req_rec.operation_seq_num and
             department_id = p_eam_mat_req_rec.department_id;

Line: 454


           select 1 into g_dummy
             from bom_departments where
             department_id = p_eam_mat_req_rec.department_id
             and organization_id = p_eam_mat_req_rec.organization_id;

Line: 471


	SELECT bd.department_code into l_token_tbl(1).token_value
            FROM  bom_departments bd
            WHERE          bd.DEPARTMENT_ID = p_eam_mat_req_rec.department_id
            AND     bd.organization_id   = p_eam_mat_req_rec.organization_id;

Line: 499


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then


           if(p_eam_mat_req_rec.wip_supply_type is not null and p_eam_mat_req_rec.wip_supply_type not in (wip_constants.push, wip_constants.bulk, wip_constants.based_on_bom)) then
             --not a valid supply type

             raise fnd_api.g_exc_unexpected_error;

Line: 540


       if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then

         if p_eam_mat_req_rec.mrp_net_flag not in (wip_constants.yes, wip_constants.no) then
             raise fnd_api.g_exc_unexpected_error;

Line: 571


    SELECT stock_enabled_flag,primary_uom_code
    INTO l_stockable_flag,l_uom
    FROM MTL_SYSTEM_ITEMS_KFV
    WHERE inventory_item_id = p_eam_mat_req_rec.inventory_item_id
    AND organization_id = p_eam_mat_req_rec.organization_id;

Line: 577


  IF(p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_DELETE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) THEN
         l_allocated := EAM_MATERIAL_ALLOCQTY_PKG.allocated_quantity(
	                               p_eam_mat_req_rec.wip_entity_id,
				       p_eam_mat_req_rec.operation_seq_num,
       	                               p_eam_mat_req_rec.organization_id,
	                               p_eam_mat_req_rec.inventory_item_id);

Line: 595


       if (p_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then

           select count(*)
             into l_count_mmtt
             from mtl_material_transactions_temp
            where transaction_source_id = p_eam_mat_req_rec.wip_entity_id
              and organization_id       = p_eam_mat_req_rec.organization_id
              and operation_seq_num     = p_eam_mat_req_rec.operation_seq_num
              and inventory_item_id     = p_eam_mat_req_rec.inventory_item_id;

Line: 609


           select count(*)
             into l_count_mmt
             from mtl_material_transactions
            where transaction_source_id = p_eam_mat_req_rec.wip_entity_id
              and organization_id       = p_eam_mat_req_rec.organization_id
              and operation_seq_num     = p_eam_mat_req_rec.operation_seq_num
              and inventory_item_id     = p_eam_mat_req_rec.inventory_item_id;

Line: 621


           select quantity_issued
             into l_issued_qty
             from wip_requirement_operations
            where wip_entity_id     = p_eam_mat_req_rec.wip_entity_id
              and organization_id   = p_eam_mat_req_rec.organization_id
              and operation_seq_num = p_eam_mat_req_rec.operation_seq_num
              and inventory_item_id = p_eam_mat_req_rec.inventory_item_id;

Line: 645


           (  p_message_name  => 'EAM_MR_DELETE_INVALID'
            , p_token_tbl     => l_token_tbl
            , p_mesg_token_tbl     => l_mesg_token_tbl
            , x_mesg_token_tbl     => l_out_mesg_token_tbl
           );

Line: 662


    if (p_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
        and p_eam_mat_req_rec.required_quantity < p_old_eam_mat_req_rec.required_quantity) then
       begin

	select material_issue_by_mo
	into l_material_issue_by_mo
	from wip_discrete_jobs
	where organization_id=p_eam_mat_req_rec.organization_id
	and wip_entity_id=p_eam_mat_req_rec.wip_entity_id;

Line: 701


IF(p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_DELETE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) THEN
--Bug#3691325 If Po Quantity or Req Quantity is greater than zero,we cannot delete the direct item
--Bug#4862404 (appsperf) - Brought the eam_work_order_direct_items_v view query inline and removed all
--                         unnecessary columns/tables.
SELECT greatest(nvl(ewodi.po_quantity_ordered,0), nvl(ewodi.rql_quantity_ordered,0))
     INTO l_ordered_quantity
from
(
SELECT
wro.wip_entity_id,
wro.operation_seq_num,
wro.organization_id,
wro.inventory_item_id as item_id,
wro.quantity as rql_quantity_ordered,
sum(pd.quantity_ordered) as po_quantity_ordered
FROM ( SELECT wro.wip_entity_id, wro.operation_seq_num, wro.organization_id, wro.inventory_item_id,
sum(rql.quantity) quantity
FROM (
 SELECT
wro.wip_entity_id, wro.operation_seq_num, wro.organization_id, wro.inventory_item_id
FROM wip_requirement_operations wro, mtl_system_items_kfv msi
WHERE msi.inventory_item_id = wro.inventory_item_id
AND msi.organization_id = wro.organization_id
AND nvl(msi.stock_enabled_flag, 'N') = 'N'
 )
wro,
po_requisition_lines_all rql,
po_requisition_headers_all rqh
WHERE
wro.wip_entity_id = rql.wip_entity_id (+)
AND wro.organization_id = rql.destination_organization_id (+)
AND wro.operation_seq_num = rql.wip_operation_seq_num (+)
AND rql.requisition_header_id = rqh.requisition_header_id(+)
AND upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
AND rql.wip_resource_seq_num is null AND wro.inventory_item_id = rql.item_id (+)
GROUP BY
wro.wip_entity_id, wro.operation_seq_num, wro.organization_id,
wro.inventory_item_id)
wro,
( SELECT pd1.wip_entity_id,
         pd1.wip_operation_seq_num,
         pd1.destination_organization_id,
         pd1.wip_resource_seq_num,
         pd1.quantity_ordered,
         pol.item_id,
         pol.cancel_flag
FROM po_lines_all pol, po_distributions_all pd1
WHERE pol.po_line_id = pd1.po_line_id ) pd
WHERE wro.wip_entity_id = pd.wip_entity_id(+)
AND wro.organization_id = pd.destination_organization_id(+)
AND wro.operation_seq_num = pd.wip_operation_seq_num(+)
AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y'
AND pd.wip_resource_seq_num is null
AND wro.inventory_item_id = pd.item_id (+)
GROUP BY
wro.wip_entity_id, wro.operation_seq_num, wro.organization_id,
wro.inventory_item_id,
 wro.quantity
) ewodi
     WHERE ewodi.wip_entity_id= p_eam_mat_req_rec.wip_entity_id
     AND ewodi.operation_seq_num=p_eam_mat_req_rec.operation_seq_num
     AND ewodi.organization_id=p_eam_mat_req_rec.organization_id
     AND ewodi.item_id=p_eam_mat_req_rec.inventory_item_id;

Line: 767


     IF(p_eam_mat_req_rec.transaction_type =EAM_PROCESS_WO_PVT.G_OPR_DELETE) AND
        (l_ordered_quantity > 0) THEN
	   l_token_tbl(1).token_name  := 'WIP_ENTITY_ID';

Line: 774


           (  p_message_name  => 'EAM_DI_DELETE_INVALID'
            , p_token_tbl     => l_token_tbl
            , p_mesg_token_tbl     => l_mesg_token_tbl
            , x_mesg_token_tbl     => l_out_mesg_token_tbl
           );

Line: 780


  IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Cannot delete non-stockable inventory item . . . '); END IF;