The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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;
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 ;
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;
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;
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;
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;
msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
var_calendar_code, var_exception_set_id);
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;
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 ;
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;
msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
var_calendar_code, var_exception_set_id);
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;
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;
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;
msc_calendar.select_calendar_defaults(arg_org_id,arg_instance_id,
var_calendar_code, var_exception_set_id);
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 ;
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 ;
msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
var_calendar_code, var_exception_set_id);
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 ;
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 ;
msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
var_calendar_code, var_exception_set_id);
msc_calendar.select_calendar_defaults(arg_org_id, arg_instance_id,
var_calendar_code, var_exception_set_id);
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 ;
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;
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;
raise_application_error(-200000, 'Cannot select calendar defaults');
END select_calendar_defaults;
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;
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;
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;
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 ;
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);
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) ;
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);
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);
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;
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);
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);
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) ;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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;
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);
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;
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);
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;
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;
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;
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;
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;