APPS.MSC_ATP_FUNC SQL Statements

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

Line: 32

    SELECT atp_flag
    INTO   l_atp_flag
    FROM   msc_system_items
    WHERE  sr_inventory_item_id = p_inventory_item_id
    AND    organization_id = p_organization_id
    AND    plan_id = p_plan_id
    AND    sr_instance_id = p_instance_id;

Line: 41

    SELECT i.atp_flag, i.bom_item_type, b.atp_check
    INTO   l_atp_flag, l_bom_item_type, l_atp_check
    FROM   msc_system_items i, msc_bom_temp b
    WHERE  i.sr_inventory_item_id = p_inventory_item_id
    AND    i.organization_id = p_organization_id
    AND    i.plan_id = p_plan_id
    AND    i.sr_instance_id = p_instance_id
    AND    b.component_item_id (+) = i.sr_inventory_item_id
    AND    b.component_identifier (+) = MSC_ATP_PVT.G_COMP_LINE_ID
    AND    b.session_id (+) = MSC_ATP_PVT.G_SESSION_ID;

Line: 103

    SELECT atp_components_flag , bom_item_type,
           pick_components_flag, replenish_to_order_flag
    INTO   l_atp_comp_flag, l_bom_item_type,
           l_pick_comp_flag, l_replenish_flag
    FROM   msc_system_items
    WHERE  sr_inventory_item_id = p_inventory_item_id
    AND    organization_id = p_organization_id
    AND    plan_id = -1
    AND    sr_instance_id = p_instance_id;

Line: 135

        SELECT   count(assembly_identifier)
        INTO     l_cto_bom
        FROM     msc_bom_temp mbt
        WHERE    mbt.session_id = MSC_ATP_PVT.G_SESSION_ID
        AND      mbt.assembly_identifier = MSC_ATP_PVT.G_COMP_LINE_ID
        AND      mbt.assembly_item_id = p_inventory_item_id;

Line: 173

          SELECT atp_check
          INTO   l_atp_check
          FROM   msc_bom_temp
          WHERE  component_item_id = p_inventory_item_id
          AND    component_identifier = MSC_ATP_PVT.G_COMP_LINE_ID
          AND    session_id = MSC_ATP_PVT.G_SESSION_ID;

Line: 221

        SELECT sr_tp_site_id
        INTO   l_location_id
        FROM   msc_trading_partner_sites
        WHERE  sr_tp_id = p_organization_id
        AND    sr_instance_id = p_instance_id
        AND    partner_type = 3;

Line: 233

        SELECT loc.location_id
        INTO   l_location_id
        FROM   msc_tp_site_id_lid tpsid,
               msc_location_associations loc
        -- Modified for Sony Bug 2793404
        -- Remove customer_id filter and corresponding join to msc_tp_id Bug 2816887
	WHERE  tpsid.sr_tp_site_id = p_customer_site_id
        AND    tpsid.sr_instance_id = p_instance_id
        AND    tpsid.partner_type = 2
        AND    loc.partner_site_id = tpsid.tp_site_id
        AND    loc.sr_instance_id = tpsid.sr_instance_id
        AND    loc.organization_id is NULL;

Line: 252

        SELECT l.location_id
        INTO   l_location_id
        FROM   msc_location_associations l
        WHERE  l.sr_instance_id = p_instance_id
        AND    l.partner_id = p_supplier_id
        AND    l.partner_site_id = p_supplier_site_id;

Line: 282

       msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting infinite_time_fence_date, MSC_ATP_PVT.G_INV_CTP = 4');

Line: 285

    SELECT curr_cutoff_date
    INTO   l_infinite_time_fence_date
    FROM   msc_plans
    WHERE  plan_id = p_plan_id;

Line: 295

       msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting infinite_time_fence_date, MSC_ATP_PVT.G_INV_CTP = 5');

Line: 306

       msc_sch_wb.atp_debug('get_infinite_time_fence_date: ' || 'selecting item type for PDS');

Line: 308

    SELECT i.bom_item_type
    INTO   l_item_type
    FROM   msc_system_items i
    WHERE  i.plan_id = p_plan_id
    AND    i.sr_instance_id = p_instance_id
    AND    i.organization_id = p_organization_id
    AND    i.sr_inventory_item_id = p_inventory_item_id;

Line: 332

    SELECT c2.calendar_date
    INTO   l_infinite_time_fence_date
    FROM   msc_calendar_dates c2,
           msc_calendar_dates c1,
           msc_atp_rules r,
           msc_trading_partners tp,
           msc_system_items i
    WHERE  i.sr_inventory_item_id = p_inventory_item_id
    AND    i.organization_id = p_organization_id
    --AND    i.plan_id = p_plan_id
    AND    i.plan_id = -1   -- for 1478110
    AND    i.sr_instance_id = p_instance_id
    AND    tp.sr_tp_id = i.organization_id
    AND    tp.sr_instance_id = i.sr_instance_id
    AND    tp.partner_type = 3
    AND    r.sr_instance_id = tp.sr_instance_id
    AND    r.rule_id = NVL(i.atp_rule_id, NVL(tp.default_atp_rule_id,0))
    AND    c1.sr_instance_id = r.sr_instance_id
    AND    c1.calendar_date = TRUNC(sysdate)
    AND    c1.calendar_code = tp.calendar_code
    AND    c1.exception_set_id = -1
    AND    c2.sr_instance_id = c1.sr_instance_id

    -- Bug 2877340, 2746213
    -- Add Infinite Supply Time Fence PAD
    --bug3609031 adding ceil
    AND    c2.seq_num = c1.next_seq_num +
                  DECODE(r.infinite_supply_fence_code,
                  1, ceil(i.cumulative_total_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
                  2, ceil(i.cum_manufacturing_lead_time) + MSC_ATP_PVT.G_INF_SUP_TF_PAD,
                  3, DECODE(NVL(ceil(i.preprocessing_lead_time),-1)+
                            NVL(ceil(i.full_lead_time),-1)+
                            NVL(ceil(i.postprocessing_lead_time),-1),-3,
                            NULL,              -- All are NULL so return NULL.
                            NVL(ceil(i.preprocessing_lead_time),0)+   -- Otherwise
                            NVL(ceil(i.full_lead_time),0) +           -- evaluate to
                            NVL(ceil(i.postprocessing_lead_time),0) -- NON NULL
                            + MSC_ATP_PVT.G_INF_SUP_TF_PAD),
                                               -- Bugs 1986353, 2004479.
                  4, r.infinite_supply_time_fence)
    -- End Bug 2877340, 2746213
    AND    c2.calendar_code = c1.calendar_code
    AND    c2.exception_set_id = -1;

Line: 398

         SELECT trunc(curr_cutoff_date)
         INTO   l_infinite_time_fence_date
         FROM   msc_plans
         WHERE  plan_id = p_plan_id;

Line: 420

    SELECT organization_code
    INTO   l_org_code
    FROM   msc_trading_partners
    WHERE  sr_tp_id = p_organization_id
    AND    sr_instance_id = p_instance_id
    AND    partner_type = 3;

Line: 442

    SELECT substr(ITEM_NAME, 1, 40)
    INTO   l_inv_item_name
    FROM   msc_system_items
    WHERE  organization_id = p_organization_id
    AND    sr_inventory_item_id = p_inventory_item_id
    AND    plan_id = -1
    AND    sr_instance_id = p_instance_id;

Line: 470

    SELECT inventory_item_id
    INTO   l_inv_item_id
    FROM   msc_system_items
    WHERE  organization_id = p_organization_id
    AND    sr_inventory_item_id = p_inventory_item_id
    AND    plan_id = -1
    AND    sr_instance_id = p_instance_id;

Line: 493

          SELECT inventory_item_id, inventory_item_id
          bulk   collect into
                 l_sr_inv_item_id, l_inv_item_ids
          FROM   msc_system_items
          WHERE  organization_id = p_organization_id
          AND    sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
          AND    plan_id = -1
          AND    sr_instance_id = p_instance_id;

Line: 504

          select sr_inventory_item_id, inventory_item_id
          bulk   collect into
                 l_sr_inv_item_id, l_inv_item_ids
          from   msc_item_id_lid
          where  sr_inventory_item_id in (p_inventory_item_id, p_match_item_id)
          AND    sr_instance_id = p_instance_id;

Line: 540

          SELECT inventory_item_id
          INTO   l_inv_item_id
          FROM   msc_system_items
          WHERE  organization_id = p_organization_id
          AND    sr_inventory_item_id = p_inventory_item_id
          AND    plan_id = -1
          AND    sr_instance_id = p_instance_id;

Line: 550

          SELECT inventory_item_id
          INTO   l_inv_item_id
          FROM   msc_item_id_lid
          WHERE  sr_inventory_item_id = p_inventory_item_id
          AND    sr_instance_id = p_instance_id;

Line: 572

    Select count(*) into l_count from msc_item_id_lid;

Line: 599

        SELECT partner_name
        INTO   l_supplier_name
        FROM   msc_trading_partners s
        WHERE  s.partner_id = p_supplier_id;

Line: 619

    SELECT TP_SITE_CODE
    INTO   l_supplier_site_name
    FROM   msc_trading_partner_sites
    WHERE  PARTNER_SITE_ID = p_supplier_site_id;

Line: 653

  SELECT MEANING
  into l_sd_source_name
  FROM MFG_LOOKUPS
  WHERE LOOKUP_TYPE = DECODE(p_sd_type, 2, 'MRP_ORDER_TYPE',
                             DECODE(p_sd_source_type,
                                    1, 'MRP_PLANNED_ORDER_DEMAND',
                                    3, 'MRP_PLANNED_ORDER_DEMAND',
                                   25, 'MRP_PLANNED_ORDER_DEMAND',
                                    'MRP_DEMAND_ORIGINATION'))
  AND LOOKUP_CODE = p_sd_source_type   ;

Line: 679

    SELECT  cal.prior_date
    INTO    l_date
    FROM    msc_calendar_dates  cal,
            msc_trading_partners tp
    WHERE   cal.exception_set_id = tp.calendar_exception_set_id
    AND     cal.calendar_code = tp.calendar_code
    AND     cal.calendar_date = TRUNC(p_date)
    AND     cal.sr_instance_id = tp.sr_instance_id
    AND     tp.sr_instance_id = p_instance_id
    AND     tp.partner_type = 3
    AND     tp.sr_tp_id = p_organization_id;

Line: 706

        SELECT  '1'
        INTO    l_valid
        FROM    MSC_DESIGNATORS
        WHERE   INVENTORY_ATP_FLAG = 1
        AND     DESIGNATOR_TYPE = 2
        AND     DESIGNATOR_ID = p_desig_id;

Line: 730

        SELECT  designator
        INTO    l_designator
        FROM    MSC_DESIGNATORS
        WHERE   DESIGNATOR_ID = p_desig_id;

Line: 751

        SELECT  demand_class
        INTO    l_demand_class
        FROM    MSC_DESIGNATORS
        WHERE   DESIGNATOR_ID = p_desig_id;

Line: 772

    SELECT C.NEXT_SEQ_NUM
    INTO   l_seq_num
    FROM   MSC_CALENDAR_DATES C,
           MSC_TRADING_PARTNERS TP
    WHERE  TP.SR_TP_ID = p_organization_id
    AND    TP.SR_INSTANCE_ID = p_instance_id
    AND    TP.PARTNER_TYPE = 3
    AND    C.CALENDAR_CODE = TP.CALENDAR_CODE
    AND    C.EXCEPTION_SET_ID = TP.CALENDAR_EXCEPTION_SET_ID
    AND    C.SR_INSTANCE_ID = TP.SR_INSTANCE_ID
    AND    C.CALENDAR_DATE = TRUNC(p_date);

Line: 806

SELECT tolerance_percentage
INTO   v_tolerance_percent
FROM   (SELECT  tolerance_percentage
	FROM   msc_supplier_flex_fences
	WHERE  fence_days <= p_seq_num_difference
	AND    sr_instance_id = p_instance_id
	AND    plan_id = p_plan_id
	AND    organization_id = p_organization_id
	AND    inventory_item_id = p_inventory_item_id
	AND    supplier_id = p_supplier_id
	AND    NVL(supplier_site_id, -1) = NVL(p_supplier_site_id, -1)
	ORDER BY fence_days desc
	)
WHERE  ROWNUM = 1;

Line: 839

        SELECT  order_number
        INTO    l_order_number
        FROM    MSC_SUPPLIES
        WHERE   plan_id = p_plan_id
        AND     transaction_id = p_supply_id;

Line: 864

        SELECT  order_type
        INTO    l_order_type
        FROM    MSC_SUPPLIES
        WHERE   plan_id = p_plan_id
        AND     transaction_id = p_supply_id;

Line: 894

SELECT  intransit_time,
	((10 * (10 - mrt.region_type)) + DECODE(mrt.zone_flag, 'Y', 1, 0)) region_level
FROM    msc_interorg_ship_methods mism,
	msc_regions_temp mrt
WHERE   mism.plan_id = -1
AND     mism.from_location_id = p_from_location_id
AND     mism.sr_instance_id = p_from_instance_id
AND     mism.sr_instance_id2 = p_to_instance_id
AND     mism.ship_method = p_ship_method
AND     mism.to_region_id = mrt.region_id
AND     mrt.session_id = p_session_id
AND     mrt.partner_site_id = p_partner_site_id
ORDER BY 2;

Line: 910

         SELECT  intransit_time
         INTO    l_intransit_time
         FROM    msc_interorg_ship_methods
         WHERE   plan_id = -1
         AND     from_location_id = p_from_location_id
         AND     sr_instance_id = p_from_instance_id
         AND     to_location_id = p_to_location_id
         AND     sr_instance_id2 = p_to_instance_id
         AND     ship_method = p_ship_method
         AND     to_region_id is null
         AND     rownum = 1;

Line: 923

	     -- savirine added the following select statement on Sep 24, 2001
	     OPEN c_lead_time;

Line: 1002

        msc_sch_wb.atp_debug('Selecting Process Sequence ID');

Line: 1003

        Select process_sequence_id
        into l_process_seq_id
        from msc_process_effectivity prc
        where prc.plan_id = p_plan_id
        and   prc.item_id = p_item_id
        and   prc.organization_id = p_organization_id
        and   prc.sr_instance_id = p_sr_instance_id
        and   trunc(prc.effectivity_date) <= trunc(p_new_schedule_date)
        and   trunc(nvl(prc.disable_date,p_new_schedule_date))
               >= trunc(p_new_schedule_date)
        and   prc.preference = 1;