[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT distinct
organization_id,
resource_id,
department_id
FROM bom_department_resources_v
WHERE organization_id = p_org_id
AND resource_id = p_res_id ;
Procedure Update_Group_Id(
p_temp_group_id NUMBER,
p_main_group_id NUMBER );
select wip_indicators_temp_s.nextval into x_main_group_id
from sys.dual ;
select wip_indicators_temp_s.nextval into x_temp_group_id
from sys.dual ;
Update_Group_Id(
p_temp_group_id => x_temp_group_id,
p_main_group_id => x_main_group_id);
select wip_indicators_temp_s.nextval into x_main_group_id
from sys.dual ;
select wip_indicators_temp_s.nextval into x_temp_group_id
from sys.dual ;
Update_Group_Id(
p_temp_group_id => x_temp_group_id,
p_main_group_id => x_main_group_id);
select wip_indicators_temp_s.nextval into x_main_group_id
from sys.dual ;
select wip_indicators_temp_s.nextval into x_temp_group_id
from sys.dual ;
Update_Group_Id(
p_temp_group_id => x_temp_group_id,
p_main_group_id => x_main_group_id);
select wip_indicators_temp_s.nextval into x_main_group_id
from sys.dual ;
select wip_indicators_temp_s.nextval into x_temp_group_id
from sys.dual ;
Update_Group_Id(
p_temp_group_id => x_temp_group_id,
p_main_group_id => x_main_group_id);
Procedure Update_Group_Id(
p_temp_group_id NUMBER,
p_main_group_id NUMBER) IS
Begin
UPDATE Wip_Indicators_Temp
SET group_id = p_main_group_id
WHERE
group_id = p_temp_group_id ;
End Update_Group_Id ;
* This API inserts the resource transaction data into the
* Wip_Resource_Txn_Interface and uses the Resource_Txn
* API written by bbaby and rbankar
*********************************************************/
PROCEDURE Resource_Txn (
p_DEPARTMENT_ID IN NUMBER,
p_EMPLOYEE_ID IN NUMBER,
p_EMPLOYEE_NUM IN NUMBER,
p_LINE_ID IN NUMBER,
p_OPERATION_SEQ_NUM IN NUMBER,
p_ORGANIZATION_ID IN NUMBER,
p_PRIMARY_QUANTITY IN NUMBER,
p_PROJECT_ID IN NUMBER,
p_REASON_ID IN NUMBER,
p_REFERENCE IN VARCHAR2,
p_RESOURCE_ID IN NUMBER,
p_RESOURCE_SEQ_NUM IN NUMBER,
p_REPETITIVE_SCHEDULE_ID IN NUMBER,
p_SOURCE_CODE IN VARCHAR2,
p_TASK_ID IN NUMBER,
p_TRANSACTION_DATE IN DATE,
p_TRANSACTION_QUANTITY IN NUMBER,
p_WIP_ENTITY_ID IN NUMBER,
p_ACCT_PERIOD_ID IN NUMBER DEFAULT NULL,
p_ACTIVITY_ID IN NUMBER DEFAULT NULL,
p_ACTIVITY_NAME IN VARCHAR2 DEFAULT NULL,
p_ACTUAL_RESOURCE_RATE IN NUMBER DEFAULT NULL,
p_CREATED_BY IN NUMBER DEFAULT NULL,
p_CREATED_BY_NAME IN VARCHAR2 DEFAULT NULL,
p_LAST_UPDATED_BY IN NUMBER,
p_LAST_UPDATED_BY_NAME IN VARCHAR2 DEFAULT NULL,
p_LAST_UPDATE_DATE IN DATE DEFAULT NULL,
p_LAST_UPDATE_LOGIN IN NUMBER,
p_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
p_ATTRIBUTE_CATEGORY IN VARCHAR2 DEFAULT NULL,
p_AUTOCHARGE_TYPE IN NUMBER DEFAULT NULL,
p_BASIS_TYPE IN NUMBER DEFAULT NULL,
p_COMPLETION_TRANSACTION_ID IN NUMBER DEFAULT NULL,
p_CREATION_DATE IN DATE DEFAULT NULL,
p_CURRENCY_ACTUAL_RSC_RATE IN NUMBER DEFAULT NULL,
p_CURRENCY_CODE IN VARCHAR2 DEFAULT NULL,
p_CURRENCY_CONVERSION_DATE IN DATE DEFAULT NULL,
p_CURRENCY_CONVERSION_RATE IN NUMBER DEFAULT NULL,
p_CURRENCY_CONVERSION_TYPE IN VARCHAR2 DEFAULT NULL,
p_DEPARTMENT_CODE IN VARCHAR2 DEFAULT NULL,
p_ENTITY_TYPE IN NUMBER DEFAULT NULL,
p_GROUP_ID IN NUMBER DEFAULT NULL,
p_LINE_CODE IN VARCHAR2 DEFAULT NULL,
p_MOVE_TRANSACTION_ID IN NUMBER DEFAULT NULL,
p_ORGANIZATION_CODE IN VARCHAR2 DEFAULT NULL,
p_PO_HEADER_ID IN NUMBER DEFAULT NULL,
p_PO_LINE_ID IN NUMBER DEFAULT NULL,
p_PRIMARY_ITEM_ID IN NUMBER DEFAULT NULL,
p_PRIMARY_UOM IN VARCHAR2 DEFAULT NULL,
p_PRIMARY_UOM_CLASS IN VARCHAR2 DEFAULT NULL,
p_PROCESS_PHASE IN NUMBER DEFAULT NULL,
p_PROCESS_STATUS IN NUMBER DEFAULT NULL,
p_PROGRAM_APPLICATION_ID IN NUMBER DEFAULT NULL,
p_PROGRAM_ID IN NUMBER DEFAULT NULL,
p_PROGRAM_UPDATE_DATE IN DATE DEFAULT NULL,
p_RCV_TRANSACTION_ID IN NUMBER DEFAULT NULL,
p_REASON_NAME IN VARCHAR2 DEFAULT NULL,
p_RECEIVING_ACCOUNT_ID IN NUMBER DEFAULT NULL,
p_REQUEST_ID IN NUMBER DEFAULT NULL,
p_RESOURCE_CODE IN VARCHAR2 DEFAULT NULL,
p_RESOURCE_TYPE IN NUMBER DEFAULT NULL,
p_SOURCE_LINE_ID IN NUMBER DEFAULT NULL,
p_STANDARD_RATE_FLAG IN NUMBER DEFAULT NULL,
p_TRANSACTION_ID IN NUMBER DEFAULT NULL,
p_TRANSACTION_TYPE IN NUMBER DEFAULT NULL,
p_TRANSACTION_UOM IN VARCHAR2 DEFAULT NULL,
p_USAGE_RATE_OR_AMOUNT IN NUMBER DEFAULT NULL,
p_WIP_ENTITY_NAME IN VARCHAR2 DEFAULT NULL,
p_ret_status OUT NOCOPY VARCHAR2
) is
l_res_txn_rec Wip_Transaction_PUB.Res_rec_Type ;
l_res_txn_rec.last_updated_by := p_last_updated_by ;
l_res_txn_rec.last_updated_by_name := p_last_updated_by_name ;
l_res_txn_rec.last_update_date := p_last_update_date ;
l_res_txn_rec.last_update_login := p_last_update_login ;
l_res_txn_rec.program_update_date := p_program_update_date ;
PROCEDURE Update_Line_Operation (
p_line_operation IN NUMBER,
p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER )
IS
BEGIN
Update Wip_Flow_Schedules
SET current_Line_Operation = p_line_operation
WHERE
Wip_Entity_Id = p_wip_entity_id
AND Organization_Id = p_organization_id ;
END Update_Line_Operation ;
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select
((stop_time - start_time)*maximum_rate)/3600
into
x_available_quantity
from
wip_lines
where
line_id = p_line_id ;
-- Insert the planned quantity and the available quantity for
-- every day based on the WFS table. Note this will make the
-- assumption that the line will be working on non working days
-- also if, a flow schedule is required on a non working day.
-- To correct this hack we actually perform a update at the end
-- of this procedure (that is a hack, you can do a join in this
-- sql statement and actually perform the whole intelligent insert
-- in this statement itself).
insert into wip_indicators_temp(
group_id,
line_id,
transaction_date,
required_quantity,
available_quantity,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
x_group_id,
p_line_id,
trunc(wip_sfcb_utilities.sdate_to_cdate(wfs.scheduled_completion_date)),
sum(wfs.planned_quantity),
x_available_quantity,
WIP_LINE_LOAD,
WIP_LINE_LOAD_PHASE_ONE,
sysdate,
x_userid,
sysdate,
x_userid,
x_appl_id
from
wip_flow_schedules wfs
where
wfs.line_id = p_line_id
and trunc(wip_sfcb_utilities.sdate_to_cdate(wfs.scheduled_completion_date))
between p_date_from and x_date_to
group by trunc(wip_sfcb_utilities.sdate_to_cdate(wfs.scheduled_completion_date)) ;
-- Insert tha line availability for all the days when the line
-- is available, but there is no load on the line. I.e., there
-- are no records in WFS.
-- insert into wip_indicators_temp(
-- group_id,
-- line_id,
-- transaction_date,
-- required_quantity,
-- available_quantity,
-- indicator_type,
-- process_phase,
-- last_update_date,
-- last_updated_by,
-- creation_date,
-- created_by,
-- program_application_id )
--select
-- x_group_id,
-- p_line_id,
-- bcd.calendar_date,
-- null,
-- x_available_quantity,
-- WIP_LINE_LOAD,
-- WIP_LINE_LOAD_PHASE_ONE,
-- sysdate,
-- x_userid,
-- sysdate,
-- x_userid,
-- x_appl_id
--from
-- bom_calendar_dates bcd,
-- mtl_parameters mp,
-- wip_lines wl
--where
-- wl.line_id = p_line_id
--and mp.organization_id = wl.organization_id
--and bcd.calendar_code = mp.calendar_code
--and bcd.exception_set_id = mp.calendar_exception_set_id
--and bcd.calendar_date between p_date_from and p_date_to
--and bcd.seq_num is not null
--and bcd.calendar_date not in
-- ( Select distinct transaction_date
-- from wip_indicators_temp
-- where group_id = x_group_id
-- and indicator_type = WIP_LINE_LOAD
-- and process_phase = WIP_LINE_LOAD_PHASE_ONE
-- ) ;
-- This is a hack and is used to update the availability of
-- the line to be null on the non working days as per
-- the decision by jgu and dsoosai
-- add flm_timezone call to support timezone
SELECT organization_id
INTO x_org_id
FROM wip_lines
WHERE line_id = p_line_id;
UPDATE wip_indicators_temp wit
SET wit.available_quantity = 0
WHERE wit.group_id = x_group_id
and flm_timezone.client_to_calendar(wit.transaction_date) NOT IN (
SELECT bcd.calendar_date
FROM bom_calendar_dates bcd,
mtl_parameters mp,
wip_lines wl
where
wl.line_id = p_line_id
and mp.organization_id = wl.organization_id
and bcd.calendar_code = mp.calendar_code
and bcd.exception_set_id = mp.calendar_exception_set_id
and bcd.calendar_date between
flm_timezone.client_to_calendar(p_date_from) and
flm_timezone.client_to_calendar(p_date_to)
and bcd.seq_num is not null
) ;
5. Update the available hours.
*** to support oracle timez zone
p_from_date, p_to_date should be date only, to represent
a whole day in client time zone
*/
PROCEDURE Populate_Line_Resource_Load (
p_group_id IN NUMBER,
p_organization_id IN NUMBER,
p_date_from IN DATE,
p_date_to IN DATE,
p_line_id IN NUMBER,
p_line_op_id IN NUMBER,
p_userid IN NUMBER,
p_applicationid IN NUMBER,
p_errnum OUT NOCOPY NUMBER,
p_errmesg OUT NOCOPY VARCHAR2) IS
x_date_from DATE;
select wip_indicators_temp_s.nextval into x_group_id
from sys.dual ;
select min(scheduled_start_date), max(scheduled_completion_date)
into x_sim_date_from, x_sim_date_to
from wip_open_flow_schedules_v
where line_id = p_line_id
and standard_operation_id = p_line_op_id
-- for the sake of performance
and
(
( scheduled_start_date >= s_time_from
and scheduled_start_date < s_time_to)
or
( scheduled_completion_date >= s_time_from
and scheduled_completion_date >= s_time_to )
);
SELECT
min(wfs.scheduled_start_date),
max(wfs.scheduled_completion_date)
INTO
x_sim_date_from,
x_sim_date_to
FROM
wip_lines wl,
bom_operation_sequences_v bos,
bom_operational_routings bor,
wip_flow_schedules wfs
WHERE
wfs.scheduled_flag = 1
and bor.organization_id = wfs.organization_id
and bor.assembly_item_id = wfs.primary_item_id
and bor.line_id = wfs.line_id
and bor.cfm_routing_flag = 1
and decode(bor.alternate_routing_designator, null,'@@@@@@@',bor.alternate_routing_designator) =
decode(wfs.alternate_routing_designator, null, '@@@@@@@', wfs.alternate_routing_designator)
and bos.operation_type = 3 /* line operation */
and bos.routing_sequence_id = bor.common_routing_sequence_id
and wl.line_id = wfs.line_id
and wl.organization_id = wfs.organization_id
and wfs.status <> 2
and WIP_SFCB_Utilities.line_op_is_pending (
BOS.operation_seq_num,
BOR.common_routing_sequence_id,
WFS.primary_item_id,
WFS.organization_id,
WFS.alternate_routing_designator,
WFS.current_line_operation
) = 1
and wfs.line_id = p_line_id
and bos.standard_operation_id = p_line_op_id
and
(
( wfs.scheduled_start_date >= s_time_from
and wfs.scheduled_start_date < s_time_to)
or
( wfs.scheduled_completion_date >= s_time_from
and wfs.scheduled_completion_date >= s_time_to )
) ;
-- Insert the required hours for each resource in the line operation
-- This will insert a unique row for each one of shift in each day
-- for which the resource was loaded. The left over resource load
-- will be equally allocated across each of these unique rows.
insert into wip_indicators_temp (
group_id,
wip_entity_id,
organization_id,
resource_id,
resource_code,
department_id,
department_code,
transaction_date,
required_hours,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
x_group_id,
wofsv.wip_entity_id,
wofsv.organization_id,
bors.resource_id,
br.resource_code,
bos.department_id,
bd.department_code,
null,
decode(bors.basis_type,
1,
(NVL( bors.usage_rate_or_amount *
(wofsv.planned_quantity-wofsv.quantity_completed),0
)*
WIP_SFCB_UTILITIES.get_Workday_factor
(trunc(wip_sfcb_utilities.sdate_to_cdate(wofsv.scheduled_start_date)),
trunc(wip_sfcb_utilities.sdate_to_cdate(wofsv.scheduled_completion_date)),
trunc(x_date_from),
trunc(x_date_to),
bors.resource_id,
wofsv.organization_id)),
2,
DECODE(sign(trunc(wip_sfcb_utilities.sdate_to_cdate(wofsv.scheduled_completion_date)) -
x_date_to),
1,
0,
bors.usage_rate_or_amount
)
),
WIP_LINE_RL, -- Indicator Type
WIP_LINE_RL_PHASE_ONE, -- process phase
sysdate,
g_userid,
sysdate,
g_userid,
g_applicationid
from
bom_departments bd,
bom_resources br,
bom_operation_resources bors,
bom_operation_sequences bos2, /* event seqs */
bom_operation_sequences bos, /* line operations */
bom_operational_routings bor,
wip_open_flow_schedules_v wofsv
where
wofsv.organization_id = p_organization_id
and wofsv.line_id = p_line_id
and wofsv.standard_operation_id = p_line_op_id
and ( ( wofsv.scheduled_start_date >= s_time_from
and wofsv.scheduled_start_date < s_time_to)
or
( wofsv.scheduled_completion_date >= s_time_from
and wofsv.scheduled_completion_date < s_time_to)
)
and bor.organization_id = wofsv.organization_id
and bor.assembly_item_id = wofsv.primary_item_id
and bor.line_id = wofsv.line_id
and nvl(bor.alternate_routing_designator,'@@@') =
nvl(wofsv.alternate_routing_designator,'@@@')
and bos.operation_type = 3
and bos.routing_sequence_id = bor.common_routing_sequence_id
and bos.standard_operation_id = p_line_op_id
and bos2.line_op_seq_id = bos.operation_sequence_id
and bors.operation_sequence_id = bos2.operation_sequence_id
and br.resource_id = bors.resource_id
and bd.department_id = bos.department_id ;
-- Summarize the information inserted in the previous statement
-- across the various days, as we do not have show the resource
-- load by day, but we aggregate the information across the days
-- for each resource.
insert into wip_indicators_temp (
group_id,
organization_id,
resource_id,
resource_code,
department_id,
department_code,
transaction_date,
required_hours,
indicator_type,
process_phase,
last_update_date,
last_updated_by,
creation_date,
created_by,
program_application_id )
select
wit.group_id,
wit.organization_id,
wit.resource_id,
wit.resource_code,
wit.department_id,
wit.department_code,
wit.transaction_date,
sum(wit.required_hours),
WIP_LINE_RL, -- Indicator Type
WIP_LINE_RL_PHASE_TWO, -- process phase
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.program_application_id
from
wip_indicators_temp wit
where
wit.group_id = x_group_id
and wit.indicator_type = WIP_LINE_RL
and wit.process_phase = WIP_LINE_RL_PHASE_ONE
group by
wit.group_id,
wit.organization_id,
wit.resource_id,
wit.resource_code,
wit.department_id,
wit.department_code,
wit.transaction_date,
WIP_LINE_RL, -- Indicator Type
WIP_LINE_RL_PHASE_TWO, -- process phase
wit.last_update_date,
wit.last_updated_by,
wit.creation_date,
wit.created_by,
wit.program_application_id ;
-- Delete the non-aggregated resource load information that
-- was inserted with the process phase = 1.
delete from wip_indicators_temp
where group_id = x_group_id
and indicator_type = WIP_LINE_RL
and process_phase = WIP_LINE_RL_PHASE_ONE ;
-- Update the gross availaibility for
-- the various resources in the line operation
-- across the various days that falls in the range that
-- are specified as the parameters.
UPDATE wip_indicators_temp wit
SET wit.available_units = (
select
nvl(sum(((mnra.to_time-mnra.from_time)/3600)*mnra.capacity_units),0)
from
mrp_net_resource_avail mnra
where
mnra.organization_id = wit.organization_id
and mnra.resource_id = wit.resource_id
and mnra.department_id = wit.department_id
and trunc(mnra.shift_date) between x_date_from and x_date_to
and simulation_set is null
)
where wit.group_id = x_group_id
and wit.indicator_type = WIP_LINE_RL
and process_phase = WIP_LINE_RL_PHASE_TWO ;
select
nvl(count(distinct shift_date),0)
into
x_sched_days
from
mrp_net_resource_avail
where resource_id = p_resource_id
and organization_id = p_organization_id
and simulation_set is null
and shift_date between p_sched_start_date and p_date_to ;
select
nvl(count(distinct shift_date),0)
into
x_total_days
from
mrp_net_resource_avail
where resource_id = p_resource_id
and organization_id = p_organization_id
and simulation_set is null
and shift_date between p_sched_start_date and p_sched_completion_date ;
select
nvl(count(distinct shift_date),0)
into
x_sched_days
from
mrp_net_resource_avail
where resource_id = p_resource_id
and organization_id = p_organization_id
and simulation_set is null
and shift_date between p_date_from and p_date_to ;
select
nvl(count(distinct shift_date),0)
into
x_total_days
from
mrp_net_resource_avail
where resource_id = p_resource_id
and organization_id = p_organization_id
and simulation_set is null
and shift_date between p_date_from and p_sched_completion_date ;
select bso.operation_code into opcode
from bom_standard_operations bso, bom_operation_sequences bos
where bso.organization_id = p_org_id
and bso.standard_operation_id = bos.standard_operation_id
and bos.operation_sequence_id = ops_table(i).operation_sequence_id;
select to_date(p_date, fnd_date.output_mask)
into t_date
from dual;
select to_date(p_date, fnd_date.outputdt_mask)
into t_date
from dual;
select to_date(p_to_dt,fnd_date.outputdt_mask) - to_date(p_from_dt,fnd_date.outputdt_mask)
into diff
from dual;
select count(*) into l_num
from wip_discrete_jobs wdj,
oke_k_deliverables_b okd,
(select k_header_id,
oke_k_security_pkg.get_k_access(k_header_id) acc
from oke_k_deliverables_b) okh
where wdj.wip_entity_id = p_jobID
and okd.project_id = wdj.project_id
and nvl(okd.task_id, -1) = nvl(wdj.task_id, -1)
and okh.k_header_id = okd.k_header_id
and okh.acc <> 'NONE';