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_DIRECT_ITEMS_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_direct_items_rec        IN  EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
     , x_old_eam_direct_items_rec    OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_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  := 'DIRECT_ITEM_SEQUENCE_ID';

Line: 122


                   x_old_eam_direct_items_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;

Line: 173


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

         if (p_eam_direct_items_rec.operation_seq_num = 1 and
             p_eam_direct_items_rec.department_id is not null) OR
            (p_eam_direct_items_rec.operation_seq_num <> 1 and
             p_eam_direct_items_rec.department_id is null) then
           raise fnd_api.g_exc_error;

Line: 184


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

Line: 189


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

Line: 226


IF(p_eam_direct_items_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 ewodi view query inline and removed all unnecessary tables/columns.

     SELECT greatest(nvl(ewodi.po_quantity_ordered,0), nvl(ewodi.rql_quantity_ordered,0))
     INTO l_ordered_quantity
from
(
SELECT
wed.wip_entity_id AS wip_entity_id,
wed.operation_seq_num,
wed.organization_id,
wed.direct_item_sequence_id,
wed.description as item_description,
wed.quantity as rql_quantity_ordered,
sum(pd.quantity_ordered) as po_quantity_ordered
FROM
( SELECT
wed.wip_entity_id,
wed.operation_seq_num,
wed.organization_id,
wed.direct_item_sequence_id,
wed.description,
/*sum(rql.quantity) #6118897 7509781*/
sum(Decode(upper(NVL(rqh.authorization_status, 'APPROVED')), 'CANCELLED', 0, 'REJECTED', 0, 'SYSTEM_SAVED',0,rql.quantity)) quantity
FROM
wip_eam_direct_items wed, po_requisition_lines_all rql, po_requisition_headers_all rqh
WHERE
wed.wip_entity_id = rql.wip_entity_id (+)
AND wed.organization_id = rql.destination_organization_id (+)
AND wed.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') #6118897 7509781*/
AND rql.item_id is null
AND (wed.direct_item_sequence_id = rql.wip_resource_seq_num OR rql.wip_resource_seq_num is null )
AND wed.description = rql.item_description(+)
GROUP BY wed.wip_entity_id, wed.operation_seq_num, wed.organization_id,
wed.direct_item_sequence_id, wed.description
)
wed,
( SELECT
    pd1.wip_entity_id,
    pd1.wip_operation_seq_num,
    pd1.destination_organization_id,
    pol.item_description,
    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
AND upper(nvl(pol.cancel_flag,'N')) <> 'Y' ) pd    /* #7509781*/
WHERE
wed.wip_entity_id = pd.wip_entity_id(+)
AND wed.organization_id = pd.destination_organization_id (+)
AND wed.operation_seq_num = pd.wip_operation_seq_num(+)
/*AND upper(nvl(pd.cancel_flag,'N')) <> 'Y'   7509781 */
AND pd.item_id is null
AND wed.direct_item_sequence_id = pd.wip_resource_seq_num(+)   /* #7509781 */
AND wed.description = pd.item_description(+)
GROUP BY
wed.wip_entity_id,
wed.operation_seq_num,
wed.organization_id,
wed.direct_item_sequence_id,
wed.description,
wed.quantity
) ewodi
     WHERE ewodi.wip_entity_id= p_eam_direct_items_rec.wip_entity_id
     AND ewodi.operation_seq_num=p_eam_direct_items_rec.operation_seq_num
     AND ewodi.organization_id =p_eam_direct_items_rec.organization_id
     AND ewodi.direct_item_sequence_id=p_eam_direct_items_rec.direct_item_sequence_id;

Line: 308


     IF(p_eam_direct_items_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: 315


           (  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: 321


  IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Cannot delete direct item . . . '); END IF;

Line: 337


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

         if p_eam_direct_items_rec.operation_seq_num = 1 then
	   select 1
	     into g_dummy
	     from dual
	     where  p_eam_direct_items_rec.operation_seq_num = 1
             and not exists (select 1 from wip_operations
             where wip_entity_id = p_eam_direct_items_rec.wip_entity_id
             and organization_id = p_eam_direct_items_rec.organization_id);

Line: 348


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

Line: 387


       if (p_eam_direct_items_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 direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id
              and organization_id = p_eam_direct_items_rec.organization_id
			  and ( bom_item_type  = 4
              and ( eam_item_type  IS NULL or eam_item_type  = 3) );

Line: 431


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

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

Line: 468


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


           if(p_eam_direct_items_rec.wip_supply_type is not null and p_eam_direct_items_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: 509


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

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

Line: 551


       if (p_eam_direct_items_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_direct_items_rec.wip_entity_id
              and organization_id       = p_eam_direct_items_rec.organization_id
              and operation_seq_num     = p_eam_direct_items_rec.operation_seq_num
              and direct_item_sequence_id     = p_eam_direct_items_rec.direct_item_sequence_id;

Line: 565


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

Line: 577


           select quantity_issued
             into l_issued_qty
             from wip_requirement_operations
            where wip_entity_id     = p_eam_direct_items_rec.wip_entity_id
              and organization_id   = p_eam_direct_items_rec.organization_id
              and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
              and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;

Line: 601


           (  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
           );