APPS.MSC_CALENDAR SQL Statements

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

Line: 42

        SELECT  min(calendar_date), max(calendar_date), min(seq_num),
                    max(seq_num)
        INTO    min_date, max_date, min_seq_num, max_seq_num
        FROM    msc_calendar_dates
        WHERE   calendar_code = arg_calendar_code
        AND     seq_num is not null
        AND     exception_set_id = arg_exception_set_id
	AND     sr_instance_id = arg_instance_id;

Line: 51

        SELECT  min(period_start_date), max(period_start_date)
        INTO    min_period_date, max_period_date
        FROM    msc_period_start_dates
        WHERE   calendar_code = arg_calendar_code
        AND     exception_set_id = arg_exception_set_id
	AND     sr_instance_id = arg_instance_id;

Line: 58

        SELECT  min(week_start_date), max(week_start_date), min(seq_num),
                max(seq_num)
        INTO    min_week_date, max_week_date, min_week_seq_num,
                max_week_seq_num
        FROM    msc_cal_week_start_dates
        WHERE   calendar_code = arg_calendar_code
        AND     exception_set_id = arg_exception_set_id
	AND     sr_instance_id = arg_instance_id	;

Line: 97

      SELECT  period_start_date
      FROM    msc_period_start_dates cal
      WHERE   cal.exception_set_id = var_exception_set_id
        AND   cal.calendar_code = var_calendar_code
        AND   cal.period_start_date > TRUNC(arg_date)
	AND   cal.sr_instance_id = arg_instance_id
      ORDER BY period_start_date;

Line: 119

      SELECT  period_start_date
      FROM    msc_period_start_dates cal
      WHERE   cal.exception_set_id = var_exception_set_id
        AND   cal.calendar_code = var_calendar_code
        AND   cal.period_start_date < TRUNC(arg_date)
	AND   cal.sr_instance_id = arg_instance_id
      ORDER BY period_start_date DESC;

Line: 163

        SELECT calendar_date
        INTO   var_return_date
        FROM   msc_calendar_dates  cal
        WHERE  cal.exception_set_id = var_exception_set_id
          AND  cal.calendar_code = var_calendar_code
          AND  cal.seq_num = arg_seq_num + l_arg_offset
          --AND  cal.seq_num = arg_seq_num + arg_offset
	  AND  cal.sr_instance_id = arg_instance_id;

Line: 205

        SELECT week_start_date
        INTO   var_return_date
        FROM   msc_cal_week_start_dates  cal
        WHERE  cal.exception_set_id = var_exception_set_id
          AND  cal.calendar_code = var_calendar_code
          AND  cal.seq_num = arg_seq_num + l_arg_offset
	  AND  cal.sr_instance_id = arg_instance_id;

Line: 241

   msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
            var_calendar_code, var_exception_set_id);

Line: 253

            SELECT  cal.next_date
            INTO    var_return_date
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id;

Line: 268

            SELECT  MIN(cal.week_start_date)
            INTO    var_return_date
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.week_start_date >= TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id ;

Line: 285

            SELECT  MIN(cal.period_start_date)
            INTO    var_return_date
            FROM    msc_period_start_dates  cal
             WHERE  cal.exception_set_id = var_exception_set_id
               AND  cal.calendar_code = var_calendar_code
               AND  cal.period_start_date >= TRUNC(arg_date)
	       AND   cal.sr_instance_id = arg_instance_id;

Line: 311

    msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
            var_calendar_code, var_exception_set_id);

Line: 321

            SELECT  cal.prior_date
            INTO    var_return_date
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id;

Line: 335

            SELECT  MAX(cal.week_start_date)
            INTO    var_return_date
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.week_start_date <= TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id;

Line: 349

            SELECT  MAX(cal.period_start_date)
            INTO    var_return_date
            FROM    msc_period_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.period_start_date <= TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id;

Line: 371

    msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
            var_calendar_code, var_exception_set_id);

Line: 380

            SELECT  cal.next_seq_num
            INTO    var_return_number
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id ;

Line: 394

            SELECT  MIN(cal.seq_num)
            INTO    var_return_number
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.week_start_date >= TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id ;

Line: 417

    msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
            var_calendar_code, var_exception_set_id);

Line: 426

            SELECT  cal.prior_seq_num
            INTO    var_return_number
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id ;

Line: 440

            SELECT  MAX(cal.seq_num)
            INTO    var_return_number
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = var_calendar_code
              AND   cal.week_start_date <= TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id ;

Line: 475

    msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
            var_calendar_code, var_exception_set_id);

Line: 529

    msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
            var_calendar_code, var_exception_set_id);

Line: 545

      SELECT count(period_start_date)
      INTO var_return_number
      FROM msc_period_start_dates cal
      WHERE cal.exception_set_id = var_exception_set_id
      AND   cal.calendar_code = var_calendar_code
      AND   cal.period_start_date between var_prev_work_day
        and var_prev_work_day2
      AND   cal.period_start_date <> var_prev_work_day2
      AND   cal.sr_instance_id = arg_instance_id ;

Line: 564

PROCEDURE select_calendar_defaults(
				   arg_org_id IN NUMBER,
				   arg_instance_id IN NUMBER,
				   arg_calendar_code OUT NOCOPY VARCHAR2,
				   arg_exception_set_id OUT NOCOPY NUMBER) IS
l_org_id    number;

Line: 584

   SELECT
     calendar_code,
     calendar_exception_set_id
   INTO     arg_calendar_code,
            arg_exception_set_id
   FROM     msc_trading_partners
   WHERE    sr_tp_id = l_org_id
   AND		partner_type = 3
   AND      sr_instance_id = arg_instance_id;

Line: 595

        raise_application_error(-200000, 'Cannot select calendar defaults');

Line: 599

END select_calendar_defaults;

Line: 625

            SELECT  cal.prior_date
            INTO    var_return_date
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = arg_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id;

Line: 657

        SELECT  min(calendar_date), max(calendar_date), min(seq_num),
                    max(seq_num)
        INTO    min_date, max_date, min_seq_num, max_seq_num
        FROM    msc_calendar_dates
        WHERE   calendar_code = arg_calendar_code
        AND     seq_num is not null
        AND     exception_set_id = arg_exception_set_id;

Line: 665

        SELECT  min(period_start_date), max(period_start_date)
        INTO    min_period_date, max_period_date
        FROM    msc_period_start_dates
        WHERE   calendar_code = arg_calendar_code
        AND     exception_set_id = arg_exception_set_id;

Line: 671

        SELECT  min(week_start_date), max(week_start_date), min(seq_num),
                max(seq_num)
        INTO    min_week_date, max_week_date, min_week_seq_num,
                max_week_seq_num
        FROM    msc_cal_week_start_dates
        WHERE   calendar_code = arg_calendar_code
        AND     exception_set_id = arg_exception_set_id	;

Line: 727

            SELECT  cal.next_date
            INTO    var_return_date
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date);

Line: 741

            SELECT  MIN(cal.week_start_date)
            INTO    var_return_date
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.week_start_date >= TRUNC(arg_date) ;

Line: 755

            SELECT  MIN(cal.period_start_date)
            INTO    var_return_date
            FROM    msc_period_start_dates  cal
             WHERE  cal.exception_set_id = var_exception_set_id
               AND  cal.calendar_code = arg_calendar_code
               AND  cal.period_start_date >= TRUNC(arg_date);

Line: 795

            SELECT  cal.prior_date
            INTO    var_return_date
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date);

Line: 808

            SELECT  MAX(cal.week_start_date)
            INTO    var_return_date
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.week_start_date <= TRUNC(arg_date)
	      AND   cal.sr_instance_id = arg_instance_id;

Line: 822

            SELECT  MAX(cal.period_start_date)
            INTO    var_return_date
            FROM    msc_period_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.period_start_date <= TRUNC(arg_date);

Line: 858

            SELECT  cal.prior_seq_num
            INTO    var_return_number
            FROM    msc_calendar_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.calendar_date = TRUNC(arg_date);

Line: 872

            SELECT  MAX(cal.seq_num)
            INTO    var_return_number
            FROM    msc_cal_week_start_dates  cal
            WHERE   cal.exception_set_id = var_exception_set_id
              AND   cal.calendar_code = arg_calendar_code
              AND   cal.week_start_date <= TRUNC(arg_date) ;

Line: 905

	SELECT COUNT(*)
	INTO  	var_return_number
	FROM	msc_calendar_dates
	WHERE   sr_instance_id = arg_instance_id
	AND	calendar_code = arg_calendar_code
	AND	exception_set_id = -1
	AND 	seq_num is not null
	AND 	calendar_date between arg_date1 and arg_date2;

Line: 1015

                SELECT  calendar_code, association_type
                INTO    l_calendar_code, p_association_type
                FROM    (SELECT calendar_code, association_type
                        FROM    MSC_CALENDAR_ASSIGNMENTS
                        WHERE   SR_INSTANCE_ID  = p_instance_id
                        AND     CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
                        AND     PARTNER_TYPE    = p_partner_type
                        AND     PARTNER_ID      = p_partner_id
                        AND     NVL(PARTNER_SITE_ID, l_partner_site_id) = l_partner_site_id
                        AND     NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
                        ORDER BY ASSOCIATION_LEVEL)
                WHERE   ROWNUM = 1;

Line: 1044

                SELECT  calendar_code, association_type
                INTO    l_calendar_code, p_association_type
                FROM    (SELECT calendar_code, association_type
                        FROM    MSC_CALENDAR_ASSIGNMENTS
                        WHERE   SR_INSTANCE_ID          = p_instance_id
                        AND     CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
                        AND     ORGANIZATION_ID         = p_organization_id
                        AND     NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
                        ORDER BY ASSOCIATION_LEVEL)
                WHERE        ROWNUM = 1;

Line: 1074

                SELECT  calendar_code, association_type
                INTO    l_calendar_code, p_association_type
                FROM    MSC_CALENDAR_ASSIGNMENTS
                WHERE   SR_INSTANCE_ID          = p_instance_id
                AND     PARTNER_TYPE            = p_partner_type
                AND     SHIP_METHOD_CODE        = l_ship_method_code;

Line: 1091

                SELECT  NVL(delivery_calendar_code,MSC_CALENDAR.FOC), association_type
                INTO    l_calendar_code, p_association_type
                FROM    (SELECT delivery_calendar_code, decode(supplier_site_id,
                                                                      null, C_ITEM_VENDOR,
                                                                            C_ITEM_VENDOR_SITE) association_type
                        FROM    MSC_ITEM_SUPPLIERS
                        WHERE   SR_INSTANCE_ID          = p_instance_id
                        AND     PLAN_ID                 = p_plan_id
                        AND     INVENTORY_ITEM_ID       = p_inventory_item_id
                        AND     SUPPLIER_ID             = p_partner_id
                        AND     NVL(SUPPLIER_SITE_ID, l_partner_site_id) = l_partner_site_id
                        ORDER BY decode(supplier_site_id, null, C_ITEM_VENDOR, C_ITEM_VENDOR_SITE) desc
                        )
                WHERE        ROWNUM = 1;

Line: 1109

                SELECT  calendar_code, C_ORGANIZATION
                INTO    l_calendar_code, p_association_type
                FROM    MSC_TRADING_PARTNERS
                WHERE   SR_INSTANCE_ID  = p_instance_id
                AND     PARTNER_TYPE    = 3
                AND     SR_TP_ID        = p_organization_id;

Line: 1131

                        SELECT  calendar_code, C_ORGANIZATION
                        INTO    l_calendar_code, p_association_type
                        FROM    MSC_TRADING_PARTNERS
                        WHERE   SR_INSTANCE_ID  = p_instance_id
                        AND     PARTNER_TYPE    = 3
                        AND     SR_TP_ID        = p_organization_id;

Line: 1205

                SELECT  calendar_code, association_type
                INTO    l_calendar_code, p_association_type
                FROM    (SELECT calendar_code, association_type
                        FROM    MSC_CALENDAR_ASSIGNMENTS
                        WHERE   SR_INSTANCE_ID  = p_instance_id
                        AND     CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
                        AND     PARTNER_TYPE    = p_partner_type
                        AND     PARTNER_ID      = p_partner_id
                        AND     NVL(PARTNER_SITE_ID, l_partner_site_id) = l_partner_site_id
                        AND     NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
                        ORDER BY ASSOCIATION_LEVEL)
                WHERE   ROWNUM = 1;

Line: 1230

                SELECT  calendar_code, association_type
                INTO    l_calendar_code, p_association_type
                FROM    (SELECT calendar_code, association_type
                        FROM    MSC_CALENDAR_ASSIGNMENTS
                        WHERE   SR_INSTANCE_ID          = p_instance_id
                        AND     CALENDAR_TYPE in (l_calendar_type, 'CARRIER')
                        AND     ORGANIZATION_ID         = p_organization_id
                        AND     NVL(SHIP_METHOD_CODE, l_ship_method_code) = l_ship_method_code
                        ORDER BY ASSOCIATION_LEVEL)
                WHERE        ROWNUM = 1;

Line: 1254

                SELECT  calendar_code, association_type
                INTO    l_calendar_code, p_association_type
                FROM    MSC_CALENDAR_ASSIGNMENTS
                WHERE   SR_INSTANCE_ID          = p_instance_id
                AND     PARTNER_TYPE            = p_partner_type
                AND     SHIP_METHOD_CODE        = l_ship_method_code;

Line: 1267

                SELECT  NVL(delivery_calendar_code,MSC_CALENDAR.FOC), association_type
                INTO    l_calendar_code, p_association_type
                FROM    (SELECT delivery_calendar_code, decode(supplier_site_id,
                                                                      null, C_ITEM_VENDOR,
                                                                            C_ITEM_VENDOR_SITE) association_type
                        FROM    MSC_ITEM_SUPPLIERS
                        WHERE   SR_INSTANCE_ID          = p_instance_id
                        AND     PLAN_ID                 = p_plan_id
                        AND     INVENTORY_ITEM_ID       = p_inventory_item_id
                        AND     SUPPLIER_ID             = p_partner_id
                        AND     NVL(SUPPLIER_SITE_ID, l_partner_site_id) = l_partner_site_id
                        ORDER BY decode(supplier_site_id, null, C_ITEM_VENDOR, C_ITEM_VENDOR_SITE) desc
                        )
                WHERE        ROWNUM = 1;

Line: 1284

                SELECT  calendar_code, C_ORGANIZATION
                INTO    l_calendar_code, p_association_type
                FROM    MSC_TRADING_PARTNERS
                WHERE   SR_INSTANCE_ID  = p_instance_id
                AND     PARTNER_TYPE    = 3
                AND     SR_TP_ID        = p_organization_id;

Line: 1302

                        SELECT  calendar_code, C_ORGANIZATION
                        INTO    l_calendar_code, p_association_type
                        FROM    MSC_TRADING_PARTNERS
                        WHERE   SR_INSTANCE_ID  = p_instance_id
                        AND     PARTNER_TYPE    = 3
                        AND     SR_TP_ID        = p_organization_id;

Line: 1345

        SELECT  NEXT_DATE
        INTO    l_next_work_day
        FROM    MSC_CALENDAR_DATES
        WHERE   SR_INSTANCE_ID      = p_instance_id
        AND     CALENDAR_CODE       = p_calendar_code
        AND     EXCEPTION_SET_ID    = -1
        AND     CALENDAR_DATE       = TRUNC(p_calendar_date);

Line: 1357

                    SELECT  FIRST_WORKING_DATE, LAST_WORKING_DATE
                    INTO    l_first_work_day, l_last_work_day
                    FROM    MSC_CALENDARS
                    WHERE   SR_INSTANCE_ID	= p_instance_id
                    AND     CALENDAR_CODE	= p_calendar_code;

Line: 1403

        SELECT  PRIOR_DATE
        INTO    l_prev_work_day
        FROM    MSC_CALENDAR_DATES
        WHERE   SR_INSTANCE_ID		= p_instance_id
        AND     CALENDAR_CODE		= p_calendar_code
        AND     EXCEPTION_SET_ID	= -1
        AND     CALENDAR_DATE		= TRUNC(p_calendar_date);

Line: 1415

                    SELECT  FIRST_WORKING_DATE, LAST_WORKING_DATE
                    INTO    l_first_work_day, l_last_work_day
                    FROM    MSC_CALENDARS
                    WHERE   SR_INSTANCE_ID	= p_instance_id
                    AND     CALENDAR_CODE	= p_calendar_code;

Line: 1493

                SELECT  cal2.calendar_date
                INTO    l_offsetted_day
                FROM    MSC_CALENDAR_DATES cal1, MSC_CALENDAR_DATES cal2
                WHERE   cal1.sr_instance_id	= p_instance_id
                AND     cal1.calendar_code	= p_calendar_code
                AND     cal1.exception_set_id	= -1
                AND     cal1.calendar_date	= TRUNC(l_input_date)		--6625744
                AND     cal2.sr_instance_id	= cal1.sr_instance_id
                AND     cal2.calendar_code	= cal1.calendar_code
                AND     cal2.exception_set_id	= cal1.exception_set_id
                AND     cal2.seq_num		= cal1.prior_seq_num + l_days_offset;

Line: 1511

                            SELECT	FIRST_WORKING_DATE, LAST_WORKING_DATE
                            INTO	l_first_work_day, l_last_work_day
                            FROM	MSC_CALENDARS
                            WHERE	SR_INSTANCE_ID	= p_instance_id
                            AND	CALENDAR_CODE	= p_calendar_code;

Line: 1545

                SELECT  cal2.calendar_date
                INTO    l_offsetted_day
                FROM    MSC_CALENDAR_DATES cal1, MSC_CALENDAR_DATES cal2
                WHERE   cal1.sr_instance_id	= p_instance_id
                AND     cal1.calendar_code	= p_calendar_code
                AND     cal1.exception_set_id	= -1
                AND     cal1.calendar_date	= TRUNC(l_input_date)		--6625744
                AND     cal2.sr_instance_id	= cal1.sr_instance_id
                AND     cal2.calendar_code	= cal1.calendar_code
                AND     cal2.exception_set_id	= cal1.exception_set_id
                AND     cal2.seq_num		= cal1.next_seq_num + l_days_offset;

Line: 1563

                            SELECT  FIRST_WORKING_DATE, LAST_WORKING_DATE
                            INTO    l_first_work_day, l_last_work_day
                            FROM    MSC_CALENDARS
                            WHERE   SR_INSTANCE_ID	= p_instance_id
                            AND     CALENDAR_CODE	= p_calendar_code;