APPS.INV_SHORTCHECKEXEC_PVT SQL Statements

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

Line: 62

	SELECT sum(quantity_open) short_quantity,
	       inventory_item_id
	FROM mtl_short_chk_temp
	WHERE organization_id = p_organization_id
        AND seq_num = p_seq_num     -- Bug 5081665: filter on seq_num
	group by inventory_item_id;

Line: 84

	SELECT short_statement
	FROM   mtl_short_chk_statements
	WHERE  organization_id = p_organization_id
	AND    detail_sum_flag = p_sum_detail_flag;

Line: 115

		SELECT mtl_short_chk_temp_s.NEXTVAL
		INTO   x_seq_num
		FROM   dual;

Line: 186

     	SELECT NVL(SUM(transaction_quantity),0)
     	INTO   L_ATT_qty
     	FROM   mtl_att_qty_v
     	WHERE  organization_id 		= p_organization_id
     	AND    inventory_item_id	= p_inventory_item_id;

Line: 241

	 select count(*) into L_count from mtl_short_chk_temp;

Line: 253

                     delete from mtl_short_chk_temp
                     where inventory_item_id = L_Quantity_rec.inventory_item_id
                     and seq_num = x_seq_num  -- Bug 5081665: filter on seq_num
                     and organization_id = p_organization_id;

Line: 299

		SELECT FND_API.G_TRUE
	   	INTO   x_check_result
	   	FROM   mtl_short_chk_temp
	   	WHERE  seq_num = x_seq_num
		AND    rownum < 2;

Line: 420

     	SELECT DECODE(p_sum_detail_flag,1,shortage_msg_background_flag,
				     	2,shortage_msg_online_flag)
     	FROM   mtl_transaction_types
     	WHERE  transaction_type_id = p_transaction_type_id;

Line: 427

	SELECT DECODE(check_shortages_flag,'Y',1,'N',2,NULL)
	FROM   mtl_system_items
	WHERE  inventory_item_id = p_inventory_item_id
	AND    organization_id   = p_organization_id;

Line: 560

	SELECT concatenated_segments
	FROM   mtl_system_items_kfv
	WHERE  inventory_item_id = p_inventory_item_id
	AND    organization_id	 = p_organization_id;

Line: 566

	SELECT organization_code
	FROM   mtl_parameters
	WHERE  organization_id = p_organization_id;

Line: 571

	SELECT DECODE(check_wip_flag,1,wip_notif_comp_planner_flag,2)
		wip_notif_comp_planner_flag,
	       DECODE(check_wip_flag,1,wip_notif_ass_planner_flag,2)
		wip_notif_ass_planner_flag,
	       DECODE(check_wip_flag,1,wip_notif_comp_buyer_flag,2)
		wip_notif_comp_buyer_flag,
	       DECODE(check_wip_flag,1,wip_notif_job_creator_flag,2)
		wip_notif_job_creator_flag,
	       DECODE(check_oe_flag,1,oe_notif_item_planner_flag,2)
		oe_notif_item_planner_flag,
	       DECODE(check_oe_flag,1,oe_notif_so_creator_flag,2)
		oe_notif_so_creator_flag
	FROM   mtl_short_chk_param
	WHERE  organization_id = p_organization_id;

Line: 590

	SELECT DISTINCT FU.user_name	user_name
	FROM   mtl_planners MP,
	       mtl_system_items MSI,
	       fnd_user FU,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.wip_notif_comp_planner_flag = 1
	AND    MSCT.object_type 	IN (1,2)
	AND    MSCT.seq_num     	= p_seq_num
        AND    MSCT.inventory_item_id 	= MSI.inventory_item_id
	AND    MSCT.organization_id	= MSI.organization_id
	AND    MSI.planner_code		= MP.planner_code
	AND    MSI.organization_id	= MP.organization_id
	AND    FU.employee_id		= MP.employee_id
	-- WIP assembly planner (discrete jobs)
	UNION
	SELECT DISTINCT FU.user_name
	FROM   mtl_planners MP,
	       mtl_system_items MSI,
	       wip_entities WE,
	       fnd_user FU,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.wip_notif_ass_planner_flag = 1
	AND    MSCT.seq_num     	= p_seq_num
	AND    MSCT.object_type 	= 1
	AND    MSCT.object_id		= WE.wip_entity_id
	AND    WE.primary_item_id   	= MSI.inventory_item_id
	AND    MSCT.organization_id	= MSI.organization_id
	AND    MSI.planner_code		= MP.planner_code
	AND    MSI.organization_id	= MP.organization_id
	AND    FU.employee_id		= MP.employee_id
	-- WIP assembly planner (repetitive schedules)
	UNION
	SELECT DISTINCT FU.user_name
	FROM   mtl_planners MP,
	       mtl_system_items MSI,
	       wip_repetitive_schedules WRS,
	       wip_repetitive_items WRI,
	       fnd_user FU,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.wip_notif_ass_planner_flag = 1
	AND    MSCT.seq_num     	= p_seq_num
	AND    MSCT.object_type 	= 2
	AND    MSCT.object_id		= WRS.repetitive_schedule_id
	AND    WRI.wip_entity_id        = WRS.wip_entity_id
        AND    WRI.line_id              = WRS.line_id
        AND    WRI.organization_id      = MSCT.organization_id
	AND    WRI.primary_item_id   	= MSI.inventory_item_id
	AND    MSCT.organization_id	= MSI.organization_id
	AND    MSI.planner_code		= MP.planner_code
	AND    MSI.organization_id	= MP.organization_id
	AND    FU.employee_id		= MP.employee_id
	-- WIP component buyer
	UNION
	SELECT DISTINCT FU.user_name
	FROM   mtl_system_items MSI,
	       fnd_user FU,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.wip_notif_comp_buyer_flag = 1
	AND    MSCT.seq_num     	= p_seq_num
	AND    MSCT.object_type 	IN (1,2)
        AND    MSCT.inventory_item_id 	= MSI.inventory_item_id
	AND    MSCT.organization_id	= MSI.organization_id
	AND    FU.employee_id		= MSI.buyer_id
	-- WIP discrete job creator
	UNION
	SELECT DISTINCT FU.user_name
	FROM   fnd_user FU,
	       wip_discrete_jobs WDJ,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.wip_notif_job_creator_flag = 1
	AND    MSCT.seq_num		= p_seq_num
	AND    MSCT.object_type 	= 1
	AND    MSCT.object_id   	= WDJ.wip_entity_id
	AND    MSCT.organization_id     = WDJ.organization_id
	AND    WDJ.created_by		= FU.user_id
	-- WIP repetitive schedule creator
	UNION
        SELECT DISTINCT FU.user_name
        FROM   fnd_user FU,
	       wip_repetitive_schedules WRS,
               mtl_short_chk_temp MSCT
        WHERE  L_ShortParam_rec.wip_notif_job_creator_flag = 1
        AND    MSCT.seq_num     	= p_seq_num
        AND    MSCT.object_type 	= 2
        AND    MSCT.object_id   	= WRS.repetitive_schedule_id
	AND    MSCT.organization_id	= WRS.organization_id
	AND    WRS.created_by   	= FU.user_id
	-- OE item planner
 	UNION
	SELECT DISTINCT FU.user_name	user_name
	FROM   mtl_planners MP,
	       mtl_system_items MSI,
	       fnd_user FU,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.oe_notif_item_planner_flag = 1
	AND    MSCT.object_type = 4
	AND    MSCT.seq_num     	= p_seq_num
        AND    MSCT.inventory_item_id 	= MSI.inventory_item_id
	AND    MSCT.organization_id	= MSI.organization_id
	AND    MSI.planner_code		= MP.planner_code
	AND    MSI.organization_id	= MP.organization_id
	AND    FU.employee_id		= MP.employee_id
	-- OE sales order creator
	UNION
	SELECT DISTINCT FU.user_name
	FROM   fnd_user FU,
	       oe_order_headers SH,
	       mtl_short_chk_temp MSCT
	WHERE  L_ShortParam_rec.oe_notif_so_creator_flag = 1
	AND    MSCT.seq_num     	= p_seq_num
        AND    MSCT.object_type 	= 4
	AND    MSCT.object_id		= SH.header_id
	AND    SH.created_by		= FU.user_id;

Line: 717

        SELECT hoi.org_information3 into l_ou_org_id
	FROM hr_organization_information hoi
        WHERE hoi.org_information_context ='Accounting Information'
        AND hoi.organization_id = p_organization_id ;

Line: 936

     DELETE FROM mtl_short_chk_temp
     WHERE  seq_num = p_seq_num;

Line: 997

      SELECT concatenated_segments
      INTO L_item_conc_segments
      FROM mtl_system_items_kfv
      WHERE organization_id = p_organization_id
      AND inventory_item_id = p_inventory_item_id;

Line: 1032

     SELECT ( NVL(bcd2.seq_num, (bcd2.next_seq_num - 1))
              - NVL(bcd1.seq_num, bcd1.next_seq_num)
              + 1
            )
       INTO l_num_days
       FROM mtl_parameters  mp
          , bom_calendar_dates  bcd1
          , bom_calendar_dates  bcd2
      WHERE mp.organization_id = p_organization_id
        AND bcd1.calendar_code = mp.calendar_code
        AND bcd1.exception_set_id = mp.calendar_exception_set_id
        AND bcd1.calendar_date = TRUNC(p_first_unit_start_date)
        AND bcd2.calendar_code = mp.calendar_code
        AND bcd2.exception_set_id = mp.calendar_exception_set_id
        AND bcd2.calendar_date = TRUNC(sysdate);

Line: 1068

  SELECT LEAST( ( wro.required_quantity *
                  DECODE( NVL(wp.include_component_yield,1)
                        , 2, NVL(wro.component_yield_factor,1)
                        , 1
                        )
                  - p_quantity_issued
                  - NVL(l_qty_allocated,0)
                  - NVL( wo.CUMULATIVE_SCRAP_QUANTITY * wro.QUANTITY_PER_ASSEMBLY
                         / DECODE( NVL(wp.include_component_yield,1)
                                 , 2, 1
                                 , NVL(wro.component_yield_factor,1)
                                 )
                       , 0
                       )
                )
              , ( wrs.daily_production_rate * wro.quantity_per_assembly * l_num_days)
                + NVL(wro.quantity_backordered, 0)
              )
    INTO l_open_qty
    FROM wip_parameters              wp
       , wip_requirement_operations  wro
       , wip_operations              wo
       , wip_repetitive_schedules    wrs
   WHERE wp.organization_id         = p_organization_id
     AND wro.organization_id        = wp.organization_id
     AND wro.wip_entity_id          = p_wip_entity_id
     AND wro.repetitive_schedule_id = p_repetitive_schedule_id
     AND wro.operation_seq_num      = p_operation_seq_num
     AND wro.inventory_item_id      = p_inventory_item_id
     AND wro.required_quantity      > (wro.quantity_issued + NVL(l_qty_allocated,0))
     AND wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
     AND wro.operation_seq_num      = wo.operation_seq_num (+)
     AND wrs.organization_id        = wro.organization_id
     AND wrs.wip_entity_id          = wro.wip_entity_id
     AND wrs.repetitive_schedule_id = wro.repetitive_schedule_id;