[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_select_jobs_by_status IN NUMBER, -- 1=Yes, 2=No, Default=1
p_rel_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
p_unrel_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
p_onhold_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
p_complete_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
p_closed_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
p_cancelled_jobs IN NUMBER, -- 1=Yes, 2=No, Default=2
-- Added to fix bug #3483253 --
p_org_id IN NUMBER,
p_rout_rev_basis IN NUMBER, /* 1= Job revision Date,2=New revision Date-All jobs,3=New revision Date-Job Revision date less than new date ; */
l_select_jobs_by_status NUMBER;
SELECT wdj.wip_entity_id,
we.wip_entity_name,
wdj.organization_id,
wdj.primary_item_id,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.routing_reference_id) routing_item_id, -- Fix for bug #3347947
wdj.alternate_routing_designator alt_rtg_desig,-- Fix for bug #3347947
wdj.common_routing_sequence_id,
--nvl(wdj.routing_revision_date, sysdate) routing_revision_date,
decode(l_rout_rev_basis,2,l_new_rev_date_rou,nvl(wdj.routing_revision_date, sysdate)) routing_revision_date,
decode(wdj.job_type, 1, wdj.primary_item_id, wdj.bom_reference_id) bill_item_id,-- Fix for bug #3347947
wdj.alternate_bom_designator alt_bom_desig,
WSMPUTIL.GET_JOB_BOM_SEQ_ID(wdj.wip_entity_id) bill_sequence_id,-- Fix for bug #3286849
wdj.common_bom_sequence_id,
-- wdj.bom_revision_date, --commented for 12.1 refresh bom/routing revision date project
decode(l_bom_rev_basis,2,l_new_rev_date_bom,wdj.bom_revision_date) bom_revision_date,
wdj.wip_supply_type,
wdj.status_type
FROM wip_discrete_jobs wdj,
wip_entities we
WHERE we.organization_id = p_org_id
AND we.organization_id = wdj.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.entity_type in (5, 8)
AND we.wip_entity_name between
nvl(l_from_job_name, we.wip_entity_name)
and nvl(l_to_job_name, we.wip_entity_name)
AND wdj.job_type = nvl(l_job_type, wdj.job_type)
AND wdj.primary_item_id = nvl(l_job_assembly_id, wdj.primary_item_id)
AND nvl(wdj.common_bom_sequence_id, -1) =
nvl(l_cmn_bill_seq_id, nvl(wdj.common_bom_sequence_id, -1))
AND wdj.common_routing_sequence_id =
nvl(l_cmn_rtg_seq_id, wdj.common_routing_sequence_id)
-- Start : Fix for bug #3483253 --
-- Changed following condition to allow upgrading all statuses --
AND ( wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_unrel_jobs ,1, 1, 0), wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_rel_jobs ,1, 3, 0), wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_complete_jobs ,1, 4, 0), wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_onhold_jobs ,1, 6, 0), wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_closed_jobs ,1,12, 0), wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,1, decode(l_cancelled_jobs,1, 7, 0), wdj.status_type)
);
fnd_file.put_line(fnd_file.log, ', select_jobs_by_status ='||p_select_jobs_by_status);
AND ((p_select_jobs_by_status = 1) OR (p_select_jobs_by_status IS NULL))-- Yes
AND ((p_rel_jobs = 2) OR (p_rel_jobs IS NULL)) -- No
AND ((p_unrel_jobs = 2) OR (p_unrel_jobs IS NULL)) -- No
AND ((p_onhold_jobs = 2) OR (p_onhold_jobs IS NULL)) -- No
AND ((p_complete_jobs = 2) OR (p_complete_jobs IS NULL)) -- No
AND ((p_closed_jobs = 2) OR (p_closed_jobs IS NULL)) -- No
AND ((p_cancelled_jobs = 2) OR (p_cancelled_jobs IS NULL)) -- No
-- Added to fix bug #3483253 --
THEN
--"Based on the concurrent request parameters, Job Copies for no jobs were refreshed. "
fnd_message.set_name('WSM', 'WSM_NO_JOBS_TO_REFR');
l_select_jobs_by_status := nvl(p_select_jobs_by_status, 1);
l_select_jobs_by_status := 1;
SELECT common_bill_sequence_id
INTO l_cmn_bill_seq_id
FROM bom_bill_of_materials
WHERE organization_id = p_org_id
AND assembly_item_id = l_bill_item_id
AND nvl(alternate_bom_designator, '-1') =
nvl(l_alt_bom_designator, '-1');
SELECT common_routing_sequence_id
INTO l_cmn_rtg_seq_id
FROM bom_operational_routings
WHERE organization_id = p_org_id
AND assembly_item_id = l_rtg_item_id
AND nvl(alternate_routing_designator, '-1') =
nvl(l_alt_rtg_designator, '-1');
IF (l_select_jobs_by_status = 2) THEN
-- =No, this implies select all statuses
l_rel_jobs := 1;
SELECT count(*)
INTO l_count
FROM wip_discrete_jobs wdj,
wip_entities we
WHERE we.organization_id = p_org_id
AND we.organization_id = wdj.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
AND we.entity_type in (5, 8)
AND we.wip_entity_name between
nvl(l_from_job_name, we.wip_entity_name)
and nvl(l_to_job_name, we.wip_entity_name)
AND wdj.job_type = nvl(l_job_type, wdj.job_type)
AND wdj.primary_item_id = nvl(l_job_assembly_id, wdj.primary_item_id)
AND nvl(wdj.common_bom_sequence_id, -1) =
nvl(l_cmn_bill_seq_id, nvl(wdj.common_bom_sequence_id, -1))
AND wdj.common_routing_sequence_id =
nvl(l_cmn_rtg_seq_id, wdj.common_routing_sequence_id)
AND (wdj.status_type = decode (l_select_jobs_by_status,
1, decode(l_unrel_jobs, 1, 1, 0),
wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,
1, decode(l_rel_jobs, 1, 3, 0),
wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,
1, decode(l_complete_jobs, 1, 4, 0),
wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,
1, decode(l_onhold_jobs, 1, 6, 0),
wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,
1, decode(l_closed_jobs, 1, 12, 0),
wdj.status_type)
OR wdj.status_type = decode (l_select_jobs_by_status,
1, decode(l_cancelled_jobs, 1, 7, 0),
wdj.status_type)
);
routing if user selects to apply New Revision Date to only jobs with revision date less than New Revision date */
IF (l_rout_rev_basis = 3 AND cur_refresh_jobs.routing_revision_date <= l_new_rev_date_rou ) THEN
cur_refresh_jobs.routing_revision_date := l_new_rev_date_rou;
SELECT OAP.acct_period_id
INTO l_acct_period_id
FROM ORG_ACCT_PERIODS OAP,
WIP_DISCRETE_JOBS WDJ
WHERE WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
AND WDJ.ORGANIZATION_ID = cur_refresh_jobs.organization_id
AND OAP.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
AND INV_LE_TIMEZONE_PUB.GET_LE_DAY_FOR_INV_ORG (WDJ.DATE_CLOSED,
wdj.organization_id)
BETWEEN OAP.PERIOD_START_DATE AND OAP.SCHEDULE_CLOSE_DATE
AND OAP.OPEN_FLAG = 'Y';
select 1 bulk collect into l_num_tbl
from WIP_REQUIREMENT_OPERATIONS WRO,
WIP_OPERATION_RESOURCE_USAGE WORU,
WIP_SUB_OPERATION_RESOURCES WSOR,
WIP_OPERATION_RESOURCES WOR,
WIP_OPERATIONS WO,
WIP_DISCRETE_JOBS WDJ,
WSM_COPY_OPERATIONS WCO,
WSM_COPY_OP_NETWORKS WCON,
WSM_COPY_OP_RESOURCES WCOR,
WSM_COPY_OP_RESOURCE_USAGE WCORU,
WSM_COPY_REQUIREMENT_OPS WCRO,
WSM_LOT_BASED_JOBS WLBJ,
WIP_OPERATION_YIELDS WOY
where WO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WOR.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID
and WOR.OPERATION_SEQ_NUM (+) = WO.OPERATION_SEQ_NUM
and WSOR.WIP_ENTITY_ID (+) = WO.WIP_ENTITY_ID
and WSOR.OPERATION_SEQ_NUM (+) = WO.OPERATION_SEQ_NUM
and WORU.WIP_ENTITY_ID (+) = WOR.WIP_ENTITY_ID
and WORU.OPERATION_SEQ_NUM (+) = WOR.OPERATION_SEQ_NUM
and WORU.RESOURCE_SEQ_NUM (+) = WOR.RESOURCE_SEQ_NUM
and WRO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WCO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WCON.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WCOR.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WCORU.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WCRO.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WLBJ.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WOY.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
for update NOWAIT;
/* select 1 bulk collect into l_num_tbl
from WIP_OPERATION_RESOURCES WOR,
WIP_OPERATIONS WO,
WIP_DISCRETE_JOBS WDJ,
WSM_COPY_OPERATIONS WCO,
WSM_COPY_OP_RESOURCES WCOR
where WO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
and WO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
and WOR.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WOR.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
and WCO.WIP_ENTITY_ID = WDJ.WIP_ENTITY_ID
and WCO.ORGANIZATION_ID = WDJ.ORGANIZATION_ID
and WCOR.WIP_ENTITY_ID (+) = WDJ.WIP_ENTITY_ID
and WCOR.ORGANIZATION_ID (+) = WDJ.ORGANIZATION_ID
and WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
and WDJ.ORGANIZATION_ID = cur_refresh_jobs.organization_id
for update NOWAIT;
select 1 bulk collect into l_num_tbl
from WIP_DISCRETE_JOBS WDJ
where WDJ.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
and WDJ.ORGANIZATION_ID = cur_refresh_jobs.organization_id
for update NOWAIT;
l_num_tbl.delete;
select 1 bulk collect into l_num_tbl
from WIP_OPERATIONS WO
where WO.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
and WO.ORGANIZATION_ID = cur_refresh_jobs.organization_id
for update NOWAIT;
l_num_tbl.delete;
select 1 bulk collect into l_num_tbl
from WIP_OPERATION_RESOURCES WOR
where WOR.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
and WOR.ORGANIZATION_ID = cur_refresh_jobs.organization_id
for update NOWAIT;
l_num_tbl.delete;
select 1 bulk collect into l_num_tbl
from WIP_REQUIREMENT_OPERATIONS WRO
where WRO.WIP_ENTITY_ID = cur_refresh_jobs.wip_entity_id
and WRO.ORGANIZATION_ID = cur_refresh_jobs.organization_id
for update NOWAIT;
l_num_tbl.delete;
p_last_update_date => sysdate,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_creation_date => sysdate,
p_created_by => fnd_global.user_id,
p_request_id => fnd_global.conc_request_id,
p_program_app_id => fnd_global.prog_appl_id,
p_program_id => fnd_global.conc_program_id,
p_program_update_date => sysdate,
p_inf_sch_flag => 'Y',
p_inf_sch_mode => NULL,
p_inf_sch_date => NULL
);
update wsm_lot_based_jobs wsm
set wsm.current_rtg_op_seq_num = null
where wsm.wip_entity_id = cur_refresh_jobs.wip_entity_id
and not exists (select 1 from wsm_copy_operations wco
where wco.operation_seq_num = nvl(wsm.current_job_op_seq_num,-1)
and wco.wip_entity_id = cur_refresh_jobs.wip_entity_id);
routing in WDJ if user selects new revision date */
IF (l_rout_rev_basis IN (2,3)) OR (l_bom_rev_basis IN (2,3)) THEN
IF (l_rout_rev_basis IN (2,3)) THEN
BEGIN
wip_revisions.routing_revision( p_organization_id => cur_refresh_jobs.organization_id,
p_item_id => cur_refresh_jobs.routing_item_id,
p_revision => l_rtg_revision,
p_revision_date => cur_refresh_jobs.routing_revision_date,
p_start_date => cur_refresh_jobs.routing_revision_date
);
UPDATE WIP_DISCRETE_JOBS
SET routing_revision_date = cur_refresh_jobs.routing_revision_date,
routing_revision = nvl(l_rtg_revision,routing_revision),
bom_revision_date = cur_refresh_jobs.bom_revision_date,
bom_revision = nvl(l_bom_revision,bom_revision)
where wip_entity_id = cur_refresh_jobs.wip_entity_id;
SELECT 1
INTO l_temp
FROM WSM_LOT_BASED_JOBS
WHERE wip_entity_id = cur_refresh_jobs.wip_entity_id;
INSERT into WSM_LOT_BASED_JOBS
(WIP_ENTITY_ID,
ORGANIZATION_ID,
ON_REC_PATH,
INTERNAL_COPY_TYPE,
COPY_PARENT_WIP_ENTITY_ID,
INFINITE_SCHEDULE,
ROUTING_REFRESH_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(cur_refresh_jobs.wip_entity_id,
cur_refresh_jobs.organization_id,
'N', -- ON_REC_PATH
3, -- INTERNAL_COPY_TYPE : -- Copies not existing due to Upgrade
-- and incorrect due to Refresh
NULL, -- COPY_PARENT_WIP_ENTITY_ID
NULL, -- INFINITE_SCHEDULE
SYSDATE, -- ROUTING_REFRESH_DATE
sysdate,
fnd_global.user_id,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
fnd_global.conc_request_id,
fnd_global.prog_appl_id,
fnd_global.conc_program_id,
sysdate
);