[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
DECODE(brc.action_type,1,OP_DEL_DAY,3,OP_ADD_DAY,
DECODE(sign(brc.capacity_change),-1,
OP_DEL,OP_ADD)),
dept.organization_id,
NULL,
bdr.department_id,
bdr.resource_id,
decode(brc.action_type,1,
(nvl(sum(decode(bdr.available_24_hours_flag,
1,24,2,
((decode(least(shifts.to_time,shifts.from_time),
shifts.to_time,shifts.to_time + 24*3600,
shifts.to_time) - shifts.from_time)/3600))),0)),
(decode(least(nvl(brc.from_time,0),
nvl(brc.to_time,1)),
nvl(brc.to_time,1),
24 * 3600 + nvl(brc.to_time,1),
nvl(brc.to_time,1)) -
nvl(brc.from_time,0))/3600),
NULL,
decode(brc.action_type,1,bdr.capacity_units,
abs(brc.capacity_change)),
0,
NULL,
decode(brc.action_type,3,cal.prior_date,brc.from_date),
decode(brc.action_type,2,nvl(brc.to_date,g_cutoff_date),
3,cal.prior_date,brc.from_date),
2,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID
FROM bom_resources res,
bom_departments dept,
bom_shift_times shifts,
mtl_parameters mp,
bom_calendar_dates cal,
bom_department_resources bdr,
bom_resource_changes brc
WHERE res.organization_id = dept.organization_id
AND bdr.resource_id = res.resource_id
AND dept.organization_id = g_org_id
AND NVL(bdr.share_from_dept_id,bdr.department_id)
= dept.department_id
AND share_from_dept_id is null
AND brc.shift_num = shifts.shift_num(+)
AND (mp.calendar_code = shifts.calendar_code
OR shifts.calendar_code IS NULL)
AND mp.organization_id = dept.organization_id
AND cal.calendar_date = brc.from_date
AND cal.exception_set_id = mp.calendar_exception_set_id
AND cal.calendar_code = mp.calendar_code
AND bdr.ctp_flag = 1
AND nvl(bdr.resource_group_name,'-1') =
nvl(g_res_group,nvl(bdr.resource_group_name,'-1'))
AND brc.department_id = bdr.department_id
AND brc.resource_id = bdr.resource_id
AND brc.simulation_set = g_simulation_set
AND (brc.from_date >= trunc(sysdate)
OR brc.to_date >= trunc(sysdate))
GROUP BY '-1',dept.organization_id, bdr.department_id, bdr.resource_id,
brc.action_type, brc.to_time, brc.from_time, bdr.capacity_units,
brc.capacity_change, brc.from_date, cal.prior_date, brc.to_date,
brc.shift_num;
SELECT
NUMBER1,
NUMBER2,
NUMBER3,
NUMBER4,
NUMBER5,
nvl(NUMBER6,0),
nvl(NUMBER7,0),
nvl(NUMBER8,0),
0,
2,
DATE1,
DATE2,
2,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
FROM MRP_FORM_QUERY
WHERE query_id = g_query_id
ORDER BY number2, number3, number4, number5, number1;
SELECT
0,
organization_id,
line_id,
department_id,
resource_id,
decode(line_id, null, resource_hours,1),
nvl(max_rate,0),
nvl(resource_units,0),
status,
applied,
resource_start_date,
resource_end_date,
updated,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN
FROM CRP_AVAILABLE_RESOURCES
WHERE (compile_designator=g_compile_designator)
AND (organization_id = g_org_id)
AND ( (line_id = g_line_id)
OR ( (line_id IS NULL) AND (g_line_id IS NULL)))
AND ( (department_id = g_department_id)
OR ( (department_id IS NULL) AND (g_department_id IS NULL)))
AND ( (resource_id = g_resource_id)
OR ( (resource_id IS NULL) AND (g_resource_id IS NULL)))
order by resource_start_date;
update_table;
-- update the change for the previous resource
update_table;
update_table;
-- update the change for the previous resource
update_table;
g_res_tab.delete;
g_tmp_tab.delete;
-- if add non working day, set the updated field as 1
-- so that when re-plan, it will be treated as work day
IF g_change_rec.operation = OP_ADD_DAY THEN
g_tmp_tab(k).updated :=1;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
-- delete work day and add non working day would be caught
-- here only if it falls inside the range and not in a gap,
-- because v_start_record will always = v_end_record in these cases
IF g_change_rec.operation <> OP_DEL_DAY THEN
k:=k+1;
g_tmp_tab(k).updated :=1;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab(k).updated :=1;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
-- need to insert row with date change only first
K:=K+1;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab(k).last_update_date := sysdate;
g_tmp_tab(k).last_updated_by :=
g_change_rec.last_updated_by;
g_tmp_tab.delete;
PROCEDURE update_table IS
m INTEGER;
delete crp_available_resources
where compile_designator = g_compile_designator
and organization_id = g_org_id
AND ( (line_id = g_line_id)
OR ( (line_id IS NULL) AND (g_line_id IS NULL)))
AND ( (department_id = g_department_id)
OR ( (department_id IS NULL) AND (g_department_id IS NULL)))
AND ( (resource_id = g_resource_id)
OR ( (resource_id IS NULL) AND (g_resource_id IS NULL)));
INSERT INTO crp_available_resources
(compile_designator,
organization_id,
line_id,
department_id,
resource_id,
resource_hours,
max_rate,
resource_units,
resource_start_date,
resource_end_date,
status,
applied,
updated,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(g_compile_designator,
g_res_tab(m).organization_id,
g_res_tab(m).line_id,
g_res_tab(m).department_id,
g_res_tab(m).resource_id,
decode( g_res_tab(m).department_id, null, 0,
greatest(0,least(24,g_res_tab(m).resource_hours))),
greatest(0,g_res_tab(m).max_rate),
decode( g_res_tab(m).department_id, null, 1,
greatest(0,round(g_res_tab(m).resource_units,6))),
g_res_tab(m).resource_start_date,
g_res_tab(m).resource_end_date,
g_res_tab(m).status,
g_res_tab(m).applied,
g_res_tab(m).updated,
g_res_tab(m).last_update_date,
g_res_tab(m).last_updated_by,
g_res_tab(m).creation_date,
g_res_tab(m).created_by,
g_res_tab(m).last_update_login);
END update_table;