APPS.MSC_ATP_EXCEPTIONS SQL Statements

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

Line: 21

    SELECT DECODE(plans.plan_type, 4, 2,
             DECODE(daily_material_constraints, 1, 1,
               DECODE(daily_resource_constraints, 1, 1,
                 DECODE(weekly_material_constraints, 1, 1,
                   DECODE(weekly_resource_constraints, 1, 1,
                     DECODE(period_material_constraints, 1, 1,
                       DECODE(period_resource_constraints, 1, 1, 2)
                           )
                         )
                       )
                     )
                   )
                 ),
           DECODE(NVL(fnd_profile.value('MSO_BATCHABLE_FLAG'),'N'), 'Y', DECODE(plans.plan_type, 4, 0,2,0,
             DECODE(daily_material_constraints, 1, 1,
               DECODE(daily_resource_constraints, 1, 1,
                 DECODE(weekly_material_constraints, 1, 1,
                   DECODE(weekly_resource_constraints, 1, 1,
                     DECODE(period_material_constraints, 1, 1,
                       DECODE(period_resource_constraints, 1, 1, 2)
                           )
                         )
                       )
                     )
                   )
                 ), 0)

    INTO   l_optimized_plan,l_constrain_plan
    FROM   msc_designators desig,
           msc_plans plans
    WHERE  plans.plan_id = p_plan_id
    AND    desig.designator = plans.compile_designator
    AND    desig.sr_instance_id = plans.sr_instance_id
    AND    desig.organization_id = plans.organization_id;

Line: 76

l_insert_item_exception         NUMBER := 0;

Line: 136

        insert into msc_exception_details (
            exception_detail_id,
            exception_type,
            quantity,
            date1,-- Demand Satisfaction Date
            number1,  -- Demand ID
            number3,  -- Demand date quantity
            plan_id,
            organization_id,
            inventory_item_id,
            resource_id,
            department_id,
            sr_instance_id,
            last_update_date,
            last_updated_by,
            creation_date,
            created_by,
            number4  -- Origination
        )
        values (
            msc_exception_details_s.nextval,
            p_exception_rec.exception_type,
            p_exception_rec.quantity,
            p_exception_rec.demand_satisfy_date, -- This is a misnomer. This actually contains demand date.
            p_exception_rec.demand_id,
            p_exception_rec.quantity_satisfied,
            p_exception_rec.plan_id,
            p_exception_rec.organization_id,
            p_exception_rec.inventory_item_id,
            -1,
            -1,
            p_exception_rec.sr_instance_id,
            sysdate,
            FND_GLOBAL.USER_ID,
            sysdate,
            FND_GLOBAL.USER_ID,
            MSC_ATP_EXCEPTIONS.G_ATP_EXCEPTION_ORIGIN_CODE
        );

Line: 186

    msc_sch_wb.atp_debug ('AEX: Inserted details record. Now updating item record');

Line: 191

         select 1
           into l_tmp_var
           from msc_item_exceptions
          where plan_id = p_exception_rec.plan_id
            and organization_id = p_exception_rec.organization_id
            and sr_instance_id = p_exception_rec.sr_instance_id
            and inventory_item_id = p_exception_rec.inventory_item_id
            and exception_type = p_exception_rec.exception_type;

Line: 205

            l_insert_item_exception := 1;

Line: 210

            l_insert_item_exception := 0;

Line: 221

    if (l_insert_item_exception = 1 ) THEN
        BEGIN
            -- insert into msc_item_exceptions
            insert into msc_item_exceptions (
                plan_id,
                organization_id,
                sr_instance_id,
                inventory_item_id,
                exception_type,
                exception_group,
                -- exception_count, bug 2795053-reopen (ssurendr): Count removed
                last_update_date,
                last_updated_by,
                creation_date,
                created_by
            )
            values (
                p_exception_rec.plan_id,
                p_exception_rec.organization_id,
                p_exception_rec.sr_instance_id,
                p_exception_rec.inventory_item_id,
                p_exception_rec.exception_type,
                p_exception_rec.exception_group,
                -- 1, bug 2795053-reopen (ssurendr): Count removed.
                sysdate,
                FND_GLOBAL.USER_ID,
                sysdate,
                FND_GLOBAL.USER_ID
            );

Line: 251

            msc_sch_wb.atp_debug ('AEX: Inserted item exception record');

Line: 256

                msc_sch_wb.atp_debug('Add_Exception: Error while inserting new exception type');