[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT rj.header_id header_id,
txn.organization_id org_id,
txn.transaction_type_id txn_type_id, /* ST bug fix : 3150692 Added */
rj.class_code class_code, /* ST bug fix 3150692 review comments : Added */
rj.primary_item_id item_id,
rj.completion_subinventory subinv_name,
rj.rowid rj_rowid --SpUA Add
FROM wsm_resulting_jobs_interface rj,
wsm_split_merge_txn_interface txn
WHERE rj.header_id = p_header_id
AND txn.header_id = rj.header_id
AND rj.job_type = WIP_CONSTANTS.STANDARD
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.process_status = WIP_CONSTANTS.RUNNING
ORDER BY TXN.TRANSACTION_DATE;
SELECT rj.class_code class_code,
rj.job_type job_type,
txn.organization_id org_id,
rj.header_id header_id
FROM wsm_resulting_jobs_interface rj,
wsm_split_merge_txn_interface txn
WHERE txn.header_id = rj.header_id
AND rj.header_id = p_header_id -- WLTEnh Add
AND txn.transaction_type_id = WSMPCNST.BONUS
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.process_status = WIP_CONSTANTS.RUNNING
ORDER BY TXN.TRANSACTION_DATE;
SELECT rj.header_id header_id,
txn.organization_id org_id,
rj.primary_item_id item_id,
rj.completion_subinventory subinv_name,
txn.transaction_type_id txn_type_id
FROM wsm_resulting_jobs_interface rj,
wsm_split_merge_txn_interface txn
-- WHERE rj.class_code is null -- Commented to fix bug #3150692--
-- AND rj.header_id = p_header_id -- Commented to fix bug #3150692--
WHERE rj.header_id = p_header_id
AND txn.header_id = rj.header_id
AND rj.job_type = WIP_CONSTANTS.NONSTANDARD
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.process_status = WIP_CONSTANTS.RUNNING
ORDER BY TXN.TRANSACTION_DATE;
SELECT distinct (rj.header_id) header_id, txn.organization_id org_id,
txn.transaction_type_id transaction_type_id,
rj.start_quantity start_quantity,
rj.net_quantity net_quantity,
rj.wip_entity_name,
/* ST : fix bug 3766859 : added job type */
rj.job_type
FROM wsm_resulting_jobs_interface rj,
wsm_split_merge_txn_interface txn
WHERE rj.net_quantity is NULL
AND rj.header_id = p_header_id
AND txn.header_id = rj.header_id
AND txn.transaction_type_id in
(WSMPCNST.SPLIT,
WSMPCNST.MERGE,
WSMPCNST.BONUS,
WSMPCNST.UPDATE_QUANTITY)
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.process_status = WIP_CONSTANTS.RUNNING;
SELECT distinct (rj.header_id) header_id,
txn.organization_id org_id
FROM wsm_resulting_jobs_interface rj,
wsm_split_merge_txn_interface txn
WHERE ( rj.coproducts_supply is NULL OR
rj.coproducts_supply NOT IN (1,2))
AND txn.header_id = rj.header_id
AND rj.header_id = p_header_id
AND txn.transaction_type_id in
(WSMPCNST.SPLIT,
WSMPCNST.MERGE,
WSMPCNST.BONUS)
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.process_status = WIP_CONSTANTS.RUNNING;
select (wdj.net_quantity - wdj.quantity_scrapped)
potential_supply,
wdj.quantity_scrapped, --FP bugfix 3403087
we.wip_entity_name
from wsm_starting_jobs_interface wsji,
wip_discrete_jobs wdj, wip_entities we
WHERE wsji.header_id = hdr_id
AND wsji.wip_entity_id = wdj.wip_entity_id
AND we.wip_entity_id = wsji.wip_entity_id;
UPDATE wsm_resulting_jobs_interface rj
SET class_code = l_class_code
WHERE rj.header_id = c_no_class_code_rj_rec.header_id
-- AND rj.class_code IS NULL /* ST bug fix 3150692 Added check for class_code to prevent overwrite of user entered value for Std Bonus txn*/
/* ST bug fix 3150692 review comments : Commented the class code check */
AND rj.rowid = c_no_class_code_rj_rec.rj_rowid --SpUA Add
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.process_status = WIP_CONSTANTS.RUNNING;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.class_code is null
AND wrji.header_id = c_no_class_code_rj_rec.header_id
/* ST bug fix 3150692 review comments : Start : We check for the txn type outside.. hence Commenting out the below check
AND EXISTS ( SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND (txn.transaction_type_id in
-- Start : Fix for bug #3150692 --
(WSMPCNST.BONUS)))
-- (WSMPCNST.UPDATE_ASSEMBLY,
-- WSMPCNST.BONUS,
-- WSMPCNST.UPDATE_ROUTING)
-- --SpUA begin
-- OR (txn.transaction_type_id in (WSMPCNST.SPLIT) AND
-- wrji.split_has_update_assy = 1)))
-- --SpUA end
-- End : Fix for bug #3150692 --
ST bug fix 3150692 review comments end */
AND wrji.rowid = c_no_class_code_rj_rec.rj_rowid --SpUA add
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.process_status = WIP_CONSTANTS.RUNNING;
select est_scrap_account,
est_scrap_var_account
into p_est_scrap_account,
p_est_scrap_var_account
from wip_accounting_classes
where class_code = c_vldt_class_code_rj_rec.class_code
and organization_id = c_vldt_class_code_rj_rec.org_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = c_vldt_class_code_rj_rec.header_id;
update wsm_resulting_jobs_interface rj
set class_code = (select dis.class_code
from wip_discrete_jobs dis,
wsm_starting_jobs_interface sj
where sj.header_id = rj.header_id
and sj.wip_entity_id = dis.wip_entity_id
and sj.representative_flag = 'Y')
/* where rj.class_code is null -- ST bug fix 3150692 Overwrite the class code in the resulting jobs with the parent jobs's value
and rj.process_status = WIP_CONSTANTS.RUNNING */ /* ST bug fix 3150692 : Commented the where condition */
where rj.process_status = WIP_CONSTANTS.RUNNING
and rj.internal_group_id = WSMPLOAD.G_GROUP_ID
and rj.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and exists (
SELECT null
FROM wsm_split_merge_txn_interface txn2
WHERE txn2.header_id = rj.header_id
AND (txn2.transaction_type_id not in
-- Start : Fix for bug #3150692 --
(WSMPCNST.BONUS)));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.class_code is null
AND wrji.header_id = C_NONSTD_NO_CLASS_CODE_RJ_rec.header_id
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.process_status = WIP_CONSTANTS.RUNNING;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.class_code = (SELECT wdj.class_code
FROM wip_discrete_jobs wdj,
wsm_starting_jobs_interface wsji
WHERE wsji.wip_entity_id = wdj.wip_entity_id
AND wsji.representative_flag = 'Y'
AND wsji.header_id = wrji.header_id)
-- WHERE wrji.class_code is null -- Commented to fix bug #3150692
WHERE wrji.header_id = C_NONSTD_NO_CLASS_CODE_RJ_rec.header_id
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.process_status = WIP_CONSTANTS.RUNNING;
select we.wip_entity_name,
wdj.start_quantity,
wdj.net_quantity,
wdj.quantity_scrapped
into sj_wip_entity_name,
sj_start_quantity,
sj_net_quantity,
sj_scrap_quantity
from wsm_starting_jobs_interface wsji,
wip_discrete_jobs wdj, wip_entities we
WHERE wsji.header_id = c_net_quantity_rec.header_id
AND wsji.wip_entity_id = wdj.wip_entity_id
AND wsji.wip_entity_id = we.wip_entity_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.net_quantity = 0
WHERE wrji.net_quantity is NULL
AND wrji.wip_entity_name = c_net_quantity_rec.wip_entity_name
AND wrji.header_id = c_net_quantity_rec.header_id;
update wsm_resulting_jobs_interface wrji
set wrji.net_quantity = (select wdj.net_quantity
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = we.wip_entity_id
and wdj.organization_id = we.organization_id
and we.wip_entity_name = c_net_quantity_rec.wip_entity_name)
where wrji.net_quantity is null
and wrji.wip_entity_name = c_net_quantity_rec.wip_entity_name
and wrji.header_id = c_net_quantity_rec.header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.net_quantity =
round(c_net_quantity_rec.start_quantity *
(sj_net_quantity-sj_scrap_quantity) *
(1/(sj_start_quantity-sj_scrap_quantity)), 6 )
WHERE wrji.net_quantity is NULL
AND wrji.wip_entity_name = c_net_quantity_rec.wip_entity_name
AND wrji.header_id = c_net_quantity_rec.header_id;
update wsm_resulting_jobs_interface wrji
set wrji.net_quantity = (select wdj.net_quantity
from wip_discrete_jobs wdj,
wip_entities we
where wdj.wip_entity_id = we.wip_entity_id
and wdj.organization_id = we.organization_id
and we.wip_entity_name = c_net_quantity_rec.wip_entity_name)
where wrji.net_quantity is null
and wrji.wip_entity_name = c_net_quantity_rec.wip_entity_name
and wrji.header_id = c_net_quantity_rec.header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.net_quantity = rj_net_quantity
WHERE wrji.net_quantity is NULL
AND wrji.wip_entity_name = c_net_quantity_rec.wip_entity_name
AND wrji.header_id = c_net_quantity_rec.header_id;
IF (c_net_quantity_rec.transaction_type_id = WSMPCNST.UPDATE_QUANTITY) THEN
l_stmt_num := 16;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.net_quantity =
-- Start : Changes for bug#3181486 - Net Planned Qty --
-- (select (wrji.start_quantity-wdj.start_quantity+wdj.net_quantity)
(select (wrji.start_quantity*(wdj.net_quantity-wdj.quantity_scrapped)
*(1/(wdj.start_quantity-wdj.quantity_scrapped))+wdj.quantity_scrapped)
-- End : Changes for bug#3181486 - Net Planned Qty --
from wip_discrete_jobs wdj, wip_entities we
where wdj.wip_entity_id=we.wip_entity_id
and we.wip_entity_name=wrji.wip_entity_name)
WHERE wrji.net_quantity is NULL
AND wrji.header_id = c_net_quantity_rec.header_id;
select net_quantity
into l_stmt_num
from wsm_resulting_jobs_interface wrji
where wrji.wip_entity_name = c_net_quantity_rec.wip_entity_name
and wrji.header_id = c_net_quantity_rec.header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.net_quantity = total_potential_supply
WHERE wrji.net_quantity is NULL
AND wrji.header_id = c_net_quantity_rec.header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.net_quantity = total_potential_supply + l_quantity_scrapped
WHERE wrji.net_quantity is NULL
AND wrji.header_id = c_net_quantity_rec.header_id;
UPDATE wsm_resulting_jobs_interface wrji
/* ST : fix for bug 3766859 : added decode : in case of non-std bonus default to 0 */
SET wrji.net_quantity = decode ( c_net_quantity_rec.job_type, WIP_CONSTANTS.STANDARD,wrji.start_quantity
,0)
WHERE wrji.header_id = c_net_quantity_rec.header_id
AND wrji.net_quantity is NULL ;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.coproducts_supply =
(SELECT decode(coproducts_supply_default, NULL, 2
, coproducts_supply_default)
FROM wsm_parameters
WHERE organization_id = c_coproducts_supply_rec.org_id )
WHERE wrji.header_id = c_coproducts_supply_rec.header_id
AND ( wrji.coproducts_supply is NULL OR
wrji.coproducts_supply NOT IN (1,2));
UPDATE wsm_resulting_jobs_interface rj
SET rj.starting_intraoperation_step =
(select sj.intraoperation_step
from wsm_starting_jobs_interface sj
where sj.header_id = rj.header_id
and sj.representative_flag = 'Y')
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.header_id = p_header_id -- WLTEnh
AND rj.starting_intraoperation_step is null;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.starting_intraoperation_step = WIP_CONSTANTS.QUEUE
WHERE wrji.starting_intraoperation_step IS NULL
AND wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id = p_header_id -- WLTEnh
AND EXISTS(SELECT 1
FROM wsm_split_merge_txn_interface sji
WHERE sji.header_id = wrji.header_id
AND sji.transaction_type_id = WSMPCNST.BONUS
AND sji.process_status = WIP_CONSTANTS.RUNNING);
UPDATE wsm_resulting_jobs_interface rj
SET rj.forward_op_option = 4
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.forward_op_option <> 4
AND rj.header_id = p_header_id -- WLTEnh
AND rj.header_id IN (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
AND process_status = WIP_CONSTANTS.RUNNING
AND (transaction_type_id IN
--SpUA begin
( --WSMPCNST.SPLIT,
WSMPCNST.BONUS)
OR (transaction_type_id IN (WSMPCNST.SPLIT)
AND rj.split_has_update_assy = 0)));
UPDATE wsm_resulting_jobs_interface rj
SET (rj.completion_subinventory,
rj.completion_locator_id) =
(SELECT bor.completion_subinventory,
/* decode(msi.locator_type, 1, NULL, */ /* ST bugfix 3336844 */
bor.completion_locator_id /*ST bug 3336844 ) */ -- BBK- Bug#2795951
-- NULL) -- CZH.BUG2398718
-- This is for other locator types like Dynamic Entry (3) and
-- Item level control (4), which are not supported by OSFM.
FROM bom_operational_routings bor,
mtl_secondary_inventories msi -- CZH.BUG2398718
WHERE bor.common_routing_sequence_id = rj.common_routing_sequence_id
AND bor.organization_id = rj.organization_id
--VJ: Start Changes for NSLBJ--
AND bor.assembly_item_id = decode(rj.job_type,
WIP_CONSTANTS.STANDARD, rj.primary_item_id,
WIP_CONSTANTS.NONSTANDARD, rj.routing_reference_id,
rj.primary_item_id)
AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
)
--VJ: End Changes for NSLBJ--
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.rowid = l_wrji_rowid --SpUA Add
AND rj.completion_subinventory is null
AND rj.common_routing_sequence_id is not null -- CZH.BUG2398718
AND rj.header_id IN (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
and header_id = p_header_id -- WLTEnh
AND process_status = WIP_CONSTANTS.RUNNING
AND (transaction_type_id IN (WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.BONUS,
WSMPCNST.UPDATE_ROUTING)
--SpUA begin
OR (transaction_type_id IN (WSMPCNST.SPLIT)
AND rj.split_has_update_assy = 1)))
--SpUA end
RETURNING rj.completion_subinventory,
rj.completion_locator_id
INTO l_temp_csi,
l_temp_loc_id;
UPDATE wsm_resulting_jobs_interface rj
SET (rj.completion_subinventory,
rj.completion_locator_id) =
(SELECT wdj.completion_subinventory,
/*decode(msi.locator_type, 1, NULL,*/ /* ST bug 3336844 */
wdj.completion_locator_id /* ST bug 3336844 ) */ -- BBK- Bug#2795951
-- NULL) -- CZH.BUG2398718
-- This is for other locator types like Dynamic Entry (3) and
-- Item level control (4), which are not supported by OSFM.
FROM wip_discrete_jobs wdj,
wsm_starting_jobs_interface sj,
mtl_secondary_inventories msi -- CZH.BUG2398718
WHERE wdj.wip_entity_id = sj.wip_entity_id
AND wdj.organization_id = sj.organization_id --VJ: Added for NSLBJ--
AND sj.header_id = rj.header_id --VJ: Added for NSLBJ--
AND sj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND msi.secondary_inventory_name = wdj.completion_subinventory -- CZH.BUG2398718
AND msi.organization_id = wdj.organization_id -- CZH.BUG2398718
)
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.header_id = p_header_id -- WLTEnh
AND rj.completion_subinventory is null
AND rj.header_id IN (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
and header_id = p_header_id -- WLTEnh
AND process_status = WIP_CONSTANTS.RUNNING
--SpUA begin
AND (transaction_type_id IN ( --WSMPCNST.SPLIT,
WSMPCNST.UPDATE_LOT_NAME,
WSMPCNST.UPDATE_QUANTITY)
OR (transaction_type_id IN (WSMPCNST.SPLIT)
AND rj.split_has_update_assy = 0)));
UPDATE wsm_resulting_jobs_interface rj
SET (rj.completion_subinventory,
rj.completion_locator_id) =
(SELECT wdj.completion_subinventory,
/* decode(msi.locator_type, 1, NULL,*/ /* St bug 3336844 */
wdj.completion_locator_id /* ST bug fix 3336844 ) */ -- BBK- Bug#2795951
-- NULL) -- CZH.BUG2398718
-- This is for other locator types like Dynamic Entry (3) and
-- Item level control (4), which are not supported by OSFM.
FROM wip_discrete_jobs wdj,
wsm_starting_jobs_interface sj,
mtl_secondary_inventories msi -- CZH.BUG2398718
WHERE wdj.wip_entity_id = sj.wip_entity_id
AND sj.header_id = p_header_id -- WLTEnh --rj.header_id
AND sj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND sj.representative_flag = 'Y'
AND msi.secondary_inventory_name = wdj.completion_subinventory -- CZH.BUG2398718
AND msi.organization_id = wdj.organization_id -- CZH.BUG2398718
)
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.completion_subinventory is null
AND rj.header_id IN (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
and header_id = p_header_id -- WLTEnh
AND process_status = WIP_CONSTANTS.RUNNING
AND transaction_type_id IN ( WSMPCNST.MERGE ))
RETURNING rj.completion_subinventory, -- WLTEnh
rj.completion_locator_id
INTO l_temp_csi,
l_temp_loc_id;
l_split_has_update_assy NUMBER; --SpUA
SELECT we.wip_entity_id,
we.wip_entity_name,
we.organization_id --VJ: CodeRVW: Added for NSLBJ--
FROM wip_entities we,
wsm_STARTING_JOBS_INTERFACE wsji
WHERE wsji.header_id = l_header_id
AND wsji.wip_entity_name IS NOT NULL
AND we.organization_id = wsji.organization_id
AND we.wip_entity_name = wsji.wip_entity_name;
SELECT rj.scheduled_start_date scheduled_start_date,
rj.scheduled_completion_date scheduled_completion_date,rj.start_quantity,
rj.organization_id org_id,
rj.primary_item_id item_id
FROM wsm_resulting_jobs_interface rj
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.header_id = l_header_id
AND (rj.scheduled_start_date is null
or rj.scheduled_completion_date is null)
AND rownum < 2;
SELECT wrji.primary_item_id item_id,
nvl(wrji.bom_reference_id, -1) bom_ref_id,
wrji.routing_reference_id rtg_ref_id,
wrji.job_type job_type,
wrji.wip_entity_name we_name
/*BA WLTEnh */
, wrji.bom_revision_date
, wrji.bom_revision
, wrji.routing_revision_date
, wrji.routing_revision
/*EA WLTEnh */
FROM wsm_resulting_jobs_interface wrji
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id = l_header_id
AND wrji.wip_entity_name = l_we_name; --VJ: Added condition to fix bug #2315397--
CURSOR REV_CUR(p_header_id NUMBER) IS -- WLTEnh to fix incorrect update
SELECT ROWIDTOCHAR(RJ.ROWID) "X_ROWID", RJ.ORGANIZATION_ID, RJ.HEADER_ID
FROM WSM_RESULTING_JOBS_INTERFACE RJ
WHERE RJ.HEADER_ID = p_header_id
AND RJ.GROUP_ID = WSMPLOAD.G_GROUP_ID
AND RJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
AND (RJ.BOM_REVISION IS NOT NULL OR RJ.BOM_REVISION_DATE IS NOT NULL)
AND RJ.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
SELECT SJ.WIP_ENTITY_ID,
TXN.TRANSACTION_DATE,
TXN.TRANSACTION_ID,
TXN.HEADER_ID
, TXN.ORGANIZATION_ID -- 2804945
FROM WSM_SPLIT_MERGE_TXN_INTERFACE TXN,
WSM_STARTING_JOBS_INTERFACE SJ
WHERE TXN.INTERNAL_GROUP_ID = WSMPLOAD.G_GROUP_ID
AND SJ.HEADER_ID = TXN.HEADER_ID
AND TXN.header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
AND TXN.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
AND SJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
ORDER BY TXN.TRANSACTION_DATE;
SELECT RJ.WIP_ENTITY_NAME,
TXN.TRANSACTION_DATE,
TXN.TRANSACTION_ID,
TXN.HEADER_ID
, TXN.ORGANIZATION_ID -- 2804945
FROM WSM_SPLIT_MERGE_TXN_INTERFACE TXN,
WSM_RESULTING_JOBS_INTERFACE RJ
WHERE TXN.INTERNAL_GROUP_ID = WSMPLOAD.G_GROUP_ID
AND RJ.HEADER_ID = TXN.HEADER_ID
AND TXN.header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
AND TXN.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
AND RJ.PROCESS_STATUS = WIP_CONSTANTS.RUNNING
ORDER BY TXN.TRANSACTION_DATE;
SELECT header_id /* BA WLTEnh */
, transaction_type_id
, transaction_date
, reason_id
, organization_id
, rowid /*EA WLTEnh */
FROM wsm_split_merge_txn_interface
WHERE process_status = WIP_CONSTANTS.RUNNING
AND internal_group_id = WSMPLOAD.G_GROUP_ID
AND header_id = WSMPLOAD.G_HEADER_ID -- XleSplits
ORDER BY TRANSACTION_DATE; --VJ: Added for NSLBJ--
SELECT wsji.header_id wsji_header_id ,
wsji.wip_entity_id ,
nvl(wsji.routing_seq_id, -1) routing_seq_id,
nvl(wsji.representative_flag, 'N') representative_flag,
wsmti.transaction_type_id --VJ: CodeRVW: Added for NSLBJ--
/*BA WLTEnh*/
, wsji.rowid wsji_rowid
, wsji.wip_entity_name
, wsji.organization_id
, wsji.primary_item_id
, wsji.intraoperation_step
, wsji.operation_seq_num
/*EA WLTEnh*/
FROM wsm_starting_jobs_interface wsji,
wsm_split_merge_txn_interface wsmti --VJ: CodeRVW: Added for NSLBJ--
WHERE wsji.process_status = WIP_CONSTANTS.RUNNING
AND wsji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wsmti.internal_group_id = WSMPLOAD.G_GROUP_ID --VJ: CodeRVW: Added for NSLBJ--
AND wsji.header_id = wsmti.header_id --VJ: CodeRVW: Added for NSLBJ--
AND wsmti.header_id = WSMPLOAD.G_HEADER_ID
AND wsmti.process_status = WIP_CONSTANTS.RUNNING;
SELECT wsmti.header_id ,
nvl(common_routing_sequence_id, -1) common_routing_sequence_id,
nvl(alternate_routing_designator, '-1') alternate_routing_designator,
nvl(common_bom_sequence_id, -1) common_bom_sequence_id,
nvl(alternate_bom_designator, '-1') alternate_bom_designator,
wsmti.transaction_type_id ,
nvl(wrji.organization_id , wsmti.organization_id), /*ST bugfix 3336844 add nvl */ --VJ: Changed for NSLBJ--
wrji.wip_entity_name job_name, --bugfix2099827
wrji.routing_revision , -- ADD: CZH.I_OED-1
wrji.routing_revision_date , -- ADD: CZH.I_OED-1
wrji.bom_revision , -- Bug#2662639
wrji.bom_revision_date, -- Bug#2662639
--VJ: CodeRVW: Start Additions for NSLBJ--
wrji.job_type job_type,
wrji.wip_entity_name wip_entity_name,
wrji.primary_item_id,
wrji.routing_reference_id,
wrji.bom_reference_id,
wrji.scheduled_start_date,
wrji.scheduled_completion_date,
wrji.start_quantity,
wrji.net_quantity,
wrji.class_code,
wrji.completion_subinventory,
wrji.completion_locator_id
--VJ: CodeRVW: End Additions for NSLBJ--
, wrji.rowid wrji_rowid
, nvl(wrji.split_has_update_assy, 0) --SpUA add
FROM wsm_resulting_jobs_interface wrji,
wsm_split_merge_txn_interface wsmti
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wsmti.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id=wsmti.header_id
AND wsmti.header_id = WSMPLOAD.G_HEADER_ID
AND wsmti.process_status = WIP_CONSTANTS.RUNNING;
select null into l_dummy
from mfg_lookups mfg
where mfg.lookup_code = l_cur_wsmti_rec.transaction_type_id
and mfg.lookup_type = 'WSM_WIP_LOT_TXN_TYPE';
select null into l_dummy
from wsm_parameters para
where para.organization_id = l_cur_wsmti_rec.organization_id
and exists ( select null
from mtl_parameters mtl
where mtl.organization_id = para.organization_id)
and trunc(nvl(l_cur_wsmti_rec.transaction_date, sysdate)) <
(select nvl(disable_date, sysdate + 1)
from org_organization_definitions org
where org.organization_id = para.organization_id) ;
select null into l_dummy
from mtl_transaction_reasons mtl
where mtl.reason_id = l_cur_wsmti_rec.reason_id
and nvl(mtl.disable_date, sysdate+1) > sysdate;
Select null into l_dummy
From wsm_starting_jobs_interface
where header_id = l_cur_wsmti_rec.header_id;
select null into l_dummy
from wsm_resulting_jobs_interface rj
where rj.header_id = l_cur_wsmti_rec.header_id;
If l_wsmti_error_flag = 1 Then -- Only a Single UPDATE on WSMTI
l_stmt_num := 13;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.rowid = l_cur_wsmti_rec.rowid;
Update wsm_starting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
Where wsji.header_id = l_cur_wsmti_rec.header_id;
Update wsm_resulting_jobs_interface wrji
Set wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
Where wrji.header_id = l_cur_wsmti_rec.header_id;
select null into l_dummy
from wsm_split_merge_txn_interface txn
where txn.header_id = l_cur_wsji_rec.wsji_header_id
and txn.internal_group_id = WSMPLOAD.G_GROUP_ID;
SELECT organization_id into l_cur_wsji_rec.organization_id
FROM wsm_split_merge_txn_interface
WHERE header_id = l_cur_wsji_rec.wsji_header_id
AND internal_group_id = WSMPLOAD.G_GROUP_ID;
select null into l_dummy
from wsm_split_merge_txn_interface txn
where txn.header_id = l_cur_wsji_rec.wsji_header_id
and txn.organization_id = l_cur_wsji_rec.organization_id
and txn.internal_group_id = WSMPLOAD.G_GROUP_ID;
select count(header_id) into l_dummy
From wsm_starting_jobs_interface wsji
Where wsji.header_id = l_cur_wsji_rec.wsji_header_id
and wsji.representative_flag = 'Y'
and exists (select 1
from wsm_split_merge_txn_interface wsmti
where wsmti.header_id = l_cur_wsji_rec.wsji_header_id
and wsmti.transaction_type_id = WSMPCNST.MERGE);
SELECT 'Y' into
l_cur_wsji_rec.representative_flag
FROM wsm_split_merge_txn_interface wsmti
WHERE wsmti.header_id = l_cur_wsji_rec.wsji_header_id
AND wsmti.transaction_type_id IN
(WSMPCNST.SPLIT,
WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.UPDATE_ROUTING,
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME);
select we.wip_entity_name into l_cur_wsji_rec.wip_entity_name
from wip_discrete_jobs dis, wip_entities we
where dis.wip_entity_id = l_cur_wsji_rec.wip_entity_id
and dis.wip_entity_id = we.wip_entity_id
and dis.organization_id = l_cur_wsji_rec.organization_id
and dis.status_type = 3
and dis.job_type in (WIP_CONSTANTS.STANDARD,
WIP_CONSTANTS.NONSTANDARD) --VJ: Added for NSLBJ--
and we.entity_type = 5;
select dis.wip_entity_id into l_cur_wsji_rec.wip_entity_id
from wip_discrete_jobs dis,
wip_entities we
where we.wip_entity_name = l_cur_wsji_rec.wip_entity_name
and we.organization_id = l_cur_wsji_rec.organization_id
and dis.wip_entity_id = we.wip_entity_id
and dis.wip_entity_id = nvl(l_cur_wsji_rec.wip_entity_id, dis.wip_entity_id)
and dis.organization_id = we.organization_id
and dis.status_type = 3
and dis.job_type in (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD)
and we.entity_type = 5;
select null into l_dummy
from wip_discrete_jobs dis
where dis.wip_entity_id = l_cur_wsji_rec.wip_entity_id
and dis.organization_id = l_cur_wsji_rec.organization_id
and dis.status_type = 3;
SELECT min(job_type), max(job_type)
INTO l_min_job_type, l_max_job_type
FROM wsm_starting_jobs_interface wsji,
wip_discrete_jobs wdj,
wip_entities we
WHERE wsji.header_id = l_cur_wsji_rec.wsji_header_id
AND NVL(wsji.wip_entity_name, l_cur_wsji_rec.wip_entity_name) = we.wip_entity_name
AND NVL(wsji.organization_id, l_cur_wsji_rec.organization_id) = we.organization_id
AND we.wip_entity_id = wdj.wip_entity_id;
select null into l_dummy
from wip_operations wo
where wo.organization_id = l_cur_wsji_rec.organization_id
and wo.wip_entity_id = l_cur_wsji_rec.wip_entity_id
and wo.operation_seq_num = l_cur_wsji_rec.operation_seq_num
and decode(wo.quantity_in_queue,0, wo.quantity_waiting_to_move,
wo.quantity_in_queue) > 0
and l_cur_wsji_rec.transaction_type_id <> WSMPCNST.BONUS;
| table: For split, merge, and updates, the intraoperation step |
| can only be Queue or To Move |
+-----------------------------------------------------------------*/
l_stmt_num := 37;
select null into l_dummy
from wsm_split_merge_txn_interface txn
where txn.header_id = l_cur_wsji_rec.wsji_header_id
and txn.transaction_type_id <> WSMPCNST.BONUS
AND l_cur_wsji_rec.intraoperation_step in (WIP_CONSTANTS.QUEUE, WIP_CONSTANTS.TOMOVE);
| of bom_resources. Modified the following update stmt |
+-----------------------------------------------------------------*/
l_stmt_num := 40;
SELECT null into l_dummy
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = l_cur_wsji_rec.wsji_header_id
AND txn.transaction_type_id = WSMPCNST.BONUS;
SELECT count(*)
INTO l_count
FROM wsm_starting_jobs_interface wsji1,
wsm_starting_jobs_interface wsji2
WHERE wsji1.header_id = l_cur_wsji_rec.wsji_header_id
AND wsji1.header_id = wsji2.header_id
AND wsji1.rowid <> wsji2.rowid
AND (l_cur_wsji_rec.wip_entity_name = wsji2.wip_entity_name
OR -- Bug#2679650
l_cur_wsji_rec.wip_entity_id = wsji2.wip_entity_id);
SELECT count(*)
INTO l_count
FROM wsm_starting_jobs_interface wsji
WHERE wsji.header_id = l_cur_wsji_rec.wsji_header_id
AND (l_cur_wsji_rec.wip_entity_name = wsji.wip_entity_name
OR
l_cur_wsji_rec.wip_entity_id = wsji.wip_entity_id);
SELECT wdj.primary_item_id, wdj.common_routing_sequence_id, wdj.job_type
into l_cur_wsji_rec.primary_item_id, l_cur_wsji_rec.routing_seq_id, l_wsji_job_type
FROM wip_discrete_jobs wdj
WHERE wdj.organization_id = l_cur_wsji_rec.organization_id
AND wdj.wip_entity_id = l_cur_wsji_rec.wip_entity_id
AND (
wdj.primary_item_id <> nvl(l_cur_wsji_rec.primary_item_id, -9999)
OR
wdj.common_routing_sequence_id <> nvl(l_cur_wsji_rec.routing_seq_id, -9999)
);
select common_routing_sequence_id
, nvl(alternate_routing_designator, '-1')
, job_type -- Bug#2679705
, routing_reference_id
into l_rtg_seq_id
, l_alt_rtg_desig
, l_wsji_job_type -- Bug# 2679705
, l_wsji_routing_reference_id -- Bug#2679705
from wip_discrete_jobs wdj
where wdj.wip_entity_id=l_cur_wsji_rec.wip_entity_id
and l_cur_wsji_rec.representative_flag='Y' ;
select common_routing_sequence_id
, nvl(alternate_routing_designator, '-1')
, job_type -- Bug#Bug2679705
, routing_reference_id
into l_rtg_seq_id
, l_alt_rtg_desig
, l_wsji_job_type -- Bug# 2679705
, l_wsji_routing_reference_id -- Bug#2679705
from wip_discrete_jobs wdj
where wdj.wip_entity_id=l_cur_wsji_rec.wip_entity_id
and l_cur_wsji_rec.representative_flag='Y' ;
UPDATE wsm_starting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
WHERE wsji.header_id = l_header_id
and NOT EXISTS (select 1
from bom_operational_routings bor
where bor.routing_sequence_id = l_cur_wsji_rec.routing_seq_id
and ((bor.assembly_item_id = l_cur_wsji_rec.primary_item_id
and l_wsji_job_type = WIP_CONSTANTS.STANDARD) -- Bug#2679705
OR
(bor.assembly_item_id = l_wsji_routing_reference_id
and l_wsji_job_type = WIP_CONSTANTS.NONSTANDARD) -- Bug#2679705
)
and bor.organization_id = l_cur_wsji_rec.organization_id
and bor.cfm_routing_flag=3);
SELECT bso.standard_operation_id
INTO l_merge_std_op_id
FROM wsm_starting_jobs_interface wsji,
wip_operations wo,
bom_standard_operations bso
WHERE wsji.header_id = l_cur_wsji_rec.wsji_header_id
--AND NVL(wsji.representative_flag, l_cur_wsji_rec.representative_flag) = 'Y'
AND NVL(wsji.wip_entity_id, l_cur_wsji_rec.wip_entity_id) = wo.wip_entity_id
AND wsji.operation_seq_num = wo.operation_seq_num
AND wo.standard_operation_id = bso.standard_operation_id;
UPDATE wsm_starting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
WHERE wsji.header_id = l_header_id;
WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.UPDATE_ROUTING )) then
l_stmt_num := 53;
Update wsm_starting_jobs_interface wsji
Set wsji.wip_entity_id = l_cur_wsji_rec.wip_entity_id
, wsji.wip_entity_name = l_cur_wsji_rec.wip_entity_name
, wsji.representative_flag = l_cur_wsji_rec.representative_flag
, wsji.primary_item_id = l_cur_wsji_rec.primary_item_id
, wsji.routing_seq_id = l_cur_wsji_rec.routing_seq_id
, wsji.organization_id = l_cur_wsji_rec.organization_id
Where wsji.rowid = l_cur_wsji_rec.wsji_rowid
and l_wsji_error_flag <> 1;
If l_wsji_error_flag = 1 Then -- Only a Single UPDATE on WSMTI
l_stmt_num := 55;
Update wsm_starting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.rowid = l_cur_wsji_rec.wsji_rowid;
Update wsm_resulting_jobs_interface wrji
Set wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
Where wrji.header_id = l_cur_wsji_rec.wsji_header_id;
Update wsm_split_merge_txn_interface wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
Where wsmti.header_id = l_cur_wsji_rec.wsji_header_id;
, l_split_has_update_assy ;
| Validate Split_Has_Update_Assy Flag |
+---------------------------------------------------------*/
l_stmt_num := 60;
/*For Split txns only. If flag is 1, must update assembly.
If flag is 0, assembly must be same. If flag is some other value, error out*/
IF (l_txn_type = WSMPCNST.SPLIT) THEN
l_stmt_num := 61;
UPDATE wsm_resulting_jobs_interface wrji
SET split_has_update_assy = 0
WHERE wrji.header_id = l_header_id
AND wrji.split_has_update_assy IS NULL;
select primary_item_id
into l_sj_item_id
from wsm_starting_jobs_interface wsji
where header_id = l_header_id;
if (l_split_has_update_assy = 0
AND l_primary_item_id <> l_sj_item_id)
OR
(l_split_has_update_assy = 1
AND l_primary_item_id = l_sj_item_id)
OR
(l_split_has_update_assy NOT IN (0, 1))
then
l_stmt_num := 63;
update wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
WHERE wrji.header_id = l_header_id;
FND_MESSAGE.SET_TOKEN('FLD_NAME', 'Split Has Update Assy Flag in Resulting Jobs');
update wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
WHERE wrji.header_id = l_header_id
AND l_txn_type = WSMPCNST.BONUS --VJ: CodeRVW: Added for NSLBJ--
AND wrji.job_type IS NOT NULL
AND wrji.job_type NOT IN (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD); --Fixed bug #2341915
update wsm_resulting_jobs_interface wrji
SET wrji.job_type = WIP_CONSTANTS.STANDARD
WHERE wrji.header_id = l_header_id
AND l_txn_type = WSMPCNST.BONUS
AND wrji.job_type IS NULL;
SELECT min(wdj.job_type), max(wdj.job_type)
INTO l_min_job_type, l_max_job_type
FROM wsm_starting_jobs_interface wsji,
wip_discrete_jobs wdj,
wip_entities we
WHERE wsji.header_id = l_header_id
AND (
(wsji.wip_entity_id = wdj.wip_entity_id
and wsji.wip_entity_id = we.wip_entity_id)
OR
(wsji.wip_entity_name = we.wip_entity_name
AND wsji.organization_id = we.organization_id
AND we.wip_entity_id = wdj.wip_entity_id
and wsji.wip_entity_id is NULL)
);
update wsm_resulting_jobs_interface wrji
SET wrji.job_type = l_min_job_type
WHERE wrji.header_id = l_header_id
and wrji.job_type IS NULL;
update wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR -- WLTEnh
WHERE wrji.header_id = l_header_id
and wrji.job_type IS NOT NULL
AND wrji.job_type <> l_min_job_type;
update wsm_resulting_jobs_interface wrji
SET organization_id = (SELECT organization_id
FROM wsm_split_merge_txn_interface
WHERE header_id = wrji.header_id
AND process_status = WIP_CONSTANTS.RUNNING
AND internal_group_id = WSMPLOAD.G_GROUP_ID)
WHERE wrji.header_id = l_header_id
AND wrji.organization_id IS NULL;
update wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists (select null
from wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.organization_id = wrji.organization_id
and txn.process_status = WIP_CONSTANTS.RUNNING
AND txn.internal_group_id = WSMPLOAD.G_GROUP_ID);
/* ST bug fix 3493984 : Update the resulting lot's start and completion dates */
IF l_txn_type NOT IN (WSMPCNST.BONUS) THEN
BEGIN
select dis.scheduled_start_date,
dis.scheduled_completion_date
into l_scheduled_start_date,
l_scheduled_completion_date
from wip_discrete_jobs dis,
wsm_starting_jobs_interface sj
where sj.header_id = l_header_id
and sj.wip_entity_id = dis.wip_entity_id
and sj.representative_flag = 'Y';
update wsm_resulting_jobs_interface rj
set scheduled_start_date = l_scheduled_start_date,
scheduled_completion_date = l_scheduled_completion_date
where rj.process_status = WIP_CONSTANTS.RUNNING
and rj.internal_group_id = WSMPLOAD.G_GROUP_ID
and rj.header_id = l_header_id;
SELECT wrji.job_type,
wrji.organization_id,
--VJ: Added foll columns to fix bug #2345536--
wrji.net_quantity,
wrji.class_code,
wrji.completion_subinventory,
wrji.completion_locator_id
INTO l_job_type,
l_organization_id,
--VJ: Added foll columns to fix bug #2345536--
l_net_quantity,
l_class_code,
l_completion_subinventory,
l_completion_locator_id
FROM wsm_resulting_jobs_interface wrji
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id = l_header_id
AND wrji.wip_entity_name = l_we_name;
IF (l_txn_type = WSMPCNST.UPDATE_ROUTING) THEN
l_stmt_num := 75;
SELECT wdj.bom_reference_id,
wdj.common_bom_sequence_id,
wdj.alternate_bom_designator,
wdj.bom_revision,
wdj.bom_revision_date,
wdj.routing_revision,
wdj.routing_revision_date
INTO l_wdj_bom_ref_id,
l_wdj_bom_seq_id,
l_wdj_alt_bom_desig,
l_wdj_bom_rev,
l_wdj_bom_rev_dt,
l_wdj_rtg_rev,
l_wdj_rtg_rev_dt
FROM wip_discrete_jobs wdj,
wip_entities we
--VJ: CodeRVW: Start Changes for NSLBJ--
WHERE wdj.wip_entity_id = we.wip_entity_id
AND wdj.organization_id = we.organization_id
AND we.wip_entity_name = l_we_name --wrji.wip_entity_name
AND wdj.organization_id = l_organization_id; --wrji.organization_id
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_err_msg
WHERE header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface
SET bom_reference_id = l_wdj_bom_ref_id,
alternate_bom_designator = l_wdj_alt_bom_desig,
common_bom_sequence_id = l_wdj_bom_seq_id,
bom_revision_date = l_wdj_bom_rev_dt,
bom_revision = l_wdj_bom_rev
WHERE header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_err_msg
WHERE header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_err_msg
WHERE header_id = l_header_id;
END IF; --IF (l_txn_type = WSMPCNST.UPDATE_ROUTING) THEN--
WSMPCNST.UPDATE_LOT_NAME, WSMPCNST.UPDATE_QUANTITY)
OR (l_txn_type IN (WSMPCNST.SPLIT) AND l_split_has_update_assy = 0))
--SpUA end
THEN
-- all bom and routing info should be same as starting (repre) lot
IF (l_txn_type = WSMPCNST.MERGE) THEN
l_stmt_num := 82;
SELECT wdj.bom_reference_id,
wdj.alternate_bom_designator,
wdj.common_bom_sequence_id,
wdj.routing_reference_id,
wdj.alternate_routing_designator,
wdj.common_routing_sequence_id
INTO l_st_bom_ref_id,
l_st_alt_bom_desig,
l_st_bom_seq_id,
l_st_rtg_ref_id,
l_st_alt_rtg_desig,
l_st_rtg_seq_id
FROM wsm_starting_jobs_interface wsji,
wip_discrete_jobs wdj
WHERE wsji.header_id = l_header_id
AND wsji.wip_entity_id = wdj.wip_entity_id
AND wsji.representative_flag='Y';
SELECT wdj.bom_reference_id,
wdj.alternate_bom_designator,
wdj.common_bom_sequence_id,
wdj.routing_reference_id,
wdj.alternate_routing_designator,
wdj.common_routing_sequence_id
INTO l_st_bom_ref_id,
l_st_alt_bom_desig,
l_st_bom_seq_id,
l_st_rtg_ref_id,
l_st_alt_rtg_desig,
l_st_rtg_seq_id
FROM wsm_starting_jobs_interface wsji,
wip_discrete_jobs wdj
WHERE wsji.header_id = l_header_id
AND wsji.wip_entity_id = wdj.wip_entity_id;
UPDATE wsm_resulting_jobs_interface
SET bom_reference_id = l_st_bom_ref_id,
alternate_bom_designator = l_st_alt_bom_desig,
common_bom_sequence_id = l_st_bom_seq_id
WHERE header_id = l_header_id
AND wip_entity_name = l_we_name;
UPDATE wsm_resulting_jobs_interface
SET routing_reference_id = l_st_rtg_ref_id,
alternate_routing_designator = l_st_alt_rtg_desig,
common_routing_sequence_id = l_st_rtg_seq_id
WHERE header_id = l_header_id
AND wip_entity_name = l_we_name;
END IF; --IF (l_txn_type IN (SPLIT, MERGE, UPDATE_LOT_NAME, UPDATE_QUANTITY)) THEN--
IF (l_txn_type IN (WSMPCNST.BONUS, WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING)
--SpUA begin
OR (l_txn_type IN (WSMPCNST.SPLIT) AND l_split_has_update_assy = 1)) THEN
--SpUA end
--VJ: 0313: Changed position for NSLBJ--
-- *** Error Code and Message Guide ***
-- 1: Routing Reference Cannot be Null
-- 2: Invalid Assembly Item Id
-- 3: Invalid Routing Reference Id
-- 4: Invalid Bom Reference Id
-- 5: Invalid Alternate Routing Designator
-- 6: Invalid Alternate Bom Designator
-- 7: Start Date cannot be greater than End Date
-- 8: Both Start and End Dates must be Entered
-- 9: Invalid Start Quantity
-- 10: Invalid Net Quantity
-- 11: Invalid Class Code
-- 12: Invalid Completion Locator Id
-- 13: Invalid Completion Subinventory
-- 14: Invalid Firm Planned Flag
l_stmt_num := 88;
UPDATE wsm_resulting_jobs_interface
SET error_message = p_err_msg
where header_id = l_header_id
and rowid = l_wrji_rowid; --SpUA add
UPDATE wsm_resulting_jobs_interface
SET common_routing_sequence_id = l_nsj_routing_seq_id
WHERE header_id = l_header_id
AND rowid = l_wrji_rowid; --SpUA add
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE header_id = l_header_id
AND rowid = l_wrji_rowid; --SpUA add
UPDATE wsm_resulting_jobs_interface
SET common_bom_sequence_id = l_nsj_bom_seq_id
WHERE header_id = l_header_id
AND rowid = l_wrji_rowid; --SpUA add
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE header_id = l_header_id
AND rowid = l_wrji_rowid; --SpUA add
END IF; --IF (l_txn_type IN (WSMPCNST.BONUS, WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING))
SELECT wrji.job_type,
wrji.organization_id,
wrji.net_quantity,
wrji.class_code,
wrji.completion_subinventory,
wrji.completion_locator_id
INTO l_job_type,
l_organization_id,
l_net_quantity,
l_class_code,
l_completion_subinventory,
l_completion_locator_id
FROM wsm_resulting_jobs_interface wrji
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id = l_header_id
AND wrji.wip_entity_name = l_we_name;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND EXISTS (select 1
FROM wip_entities we,
wsm_starting_jobs_interface sj
WHERE we.wip_entity_name = wrji.wip_entity_name
AND we.wip_entity_id = sj.wip_entity_id
AND wrji.header_id = sj.header_id
AND nvl(sj.representative_flag, 'N') = 'N');
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.SCHEDULED_START_DATE IS NULL
AND wrji.SCHEDULED_COMPLETION_DATE IS NULL;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.scheduled_completion_date = l_sch_completion_date
WHERE wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.scheduled_start_date = l_sch_start_date
WHERE wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
AND wrji.header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.SCHEDULED_START_DATE is not null
AND wrji.SCHEDULED_COMPLETION_DATE is not null
AND wrji.SCHEDULED_START_DATE > wrji.SCHEDULED_COMPLETION_DATE;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists (select null
from wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.process_status = WIP_CONSTANTS.RUNNING);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE header_id = l_header_id
AND wip_entity_name IS NOT NULL
AND length(wip_entity_name) > 30;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and exists (select null
from wip_discrete_jobs_all_v we
where we.wip_entity_name = wrji.wip_entity_name
and we.organization_id = wrji.organization_id
and not exists (select null
from wsm_starting_jobs_interface sj
where sj.header_id = wrji.header_id
and ( sj.wip_entity_id = we.wip_entity_id OR
(sj.wip_entity_name = we.wip_entity_name
and sj.organization_id = we.organization_id)
)
and we.status_type in (3)));
| doing updates. |
+-----------------------------------------------------------------*/
l_stmt_num := 107;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists ((select null
from wsm_starting_jobs_interface sj,
wsm_split_merge_txn_interface txn,
wip_entities we
where txn.header_id = wrji.header_id
and sj.header_id = txn.header_id
and wrji.wip_entity_name = we.wip_entity_name
and we.organization_id = txn.organization_id
and txn.transaction_type_id in
(WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.UPDATE_ROUTING,
WSMPCNST.UPDATE_QUANTITY)
and we.wip_entity_id = sj.wip_entity_id)
union
select null
from wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.transaction_type_id IN
(WSMPCNST.SPLIT,
WSMPCNST.MERGE,
WSMPCNST.BONUS,
WSMPCNST.UPDATE_LOT_NAME));
IF (l_txn_type = (WSMPCNST.UPDATE_LOT_NAME)) THEN
l_stmt_num := 108;
SELECT we.wip_entity_name
INTO l_start_we_name
FROM wip_entities we,
wsm_starting_jobs_interface wsji
WHERE wsji.wip_entity_id = we.wip_entity_id
AND wsji.header_id = l_header_id;
select count(*)
into l_count
from wsm_resulting_jobs_interface
where header_id = l_header_id
and wip_entity_name = l_job_name;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists (select null
from mtl_system_items mtl,
wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.transaction_type_id in
(WSMPCNST.SPLIT,
WSMPCNST.MERGE,
WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.BONUS)
and mtl.organization_id = txn.organization_id
and mtl.inventory_item_id = wrji.primary_item_id
and mtl.build_in_wip_flag = 'Y'
and mtl.pick_components_flag = 'N'
and mtl.eng_item_flag = 'N'
union
select null
from wsm_split_merge_txn_interface txn1
where txn1.header_id = wrji.header_id
and txn1.transaction_type_id in
(WSMPCNST.UPDATE_ROUTING,
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME ));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists (select null
from mtl_system_items mtl,
wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.transaction_type_id in
(WSMPCNST.SPLIT,
WSMPCNST.MERGE,
WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.BONUS)
and mtl.organization_id = txn.organization_id
and mtl.inventory_item_id = wrji.primary_item_id
and mtl.lot_control_code = 2
union
select null
from wsm_split_merge_txn_interface txn1
where txn1.header_id = wrji.header_id
and txn1.transaction_type_id in
(WSMPCNST.UPDATE_ROUTING,
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME ));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists (select null
from mtl_system_items mtl,
wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.transaction_type_id in
(WSMPCNST.SPLIT,
WSMPCNST.MERGE,
WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.BONUS)
and mtl.organization_id = txn.organization_id
and mtl.inventory_item_id = wrji.primary_item_id
and mtl.serial_number_control_code = 1
union
select null
from wsm_split_merge_txn_interface txn1
where txn1.header_id = wrji.header_id
and txn1.transaction_type_id in
(WSMPCNST.UPDATE_ROUTING,
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME ));
| if transaction is update assembly. |
+-----------------------------------------------------------------*/
l_stmt_num := 113;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and exists (select null
from wsm_starting_jobs_interface sj,
wsm_split_merge_txn_interface txn,
wip_discrete_jobs dj
where sj.wip_entity_id = dj.wip_entity_id
and dj.primary_item_id = nvl(wrji.primary_item_id,dj.primary_item_id)
and txn.header_id = wrji.header_id
and sj.header_id = txn.header_id
and txn.transaction_type_id = WSMPCNST.UPDATE_ASSEMBLY);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and not exists (select 1
from gl_code_combinations gcc,
org_organization_definitions ood,
wsm_split_merge_txn_interface txn
where txn.organization_id = ood.organization_id
and txn.header_id = wrji.header_id
and txn.transaction_type_id IN
(WSMPCNST.BONUS,
WSMPCNST.UPDATE_QUANTITY)
and ood.chart_of_accounts_id = gcc.chart_of_accounts_id
and nvl (wrji.bonus_acct_id, -1) = gcc.code_combination_id
and gcc.enabled_flag = 'Y'
and txn.transaction_date between
nvl(gcc.start_date_active, txn.transaction_date)
and nvl(gcc.end_date_active, txn.transaction_date)
union
select 1
from wsm_split_merge_txn_interface txn1
where txn1.transaction_type_id NOT IN
(WSMPCNST.BONUS,
WSMPCNST.UPDATE_QUANTITY)
and txn1.header_id = wrji.header_id);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and ( (wrji.start_quantity < 0 OR nvl(wrji.start_quantity,0) = 0)
or (exists (select null
from wsm_split_merge_txn_interface txn,
wsm_starting_jobs_interface sj,
wip_discrete_jobs dj
where txn.header_id = wrji.header_id
and txn.header_id = sj.header_id
and txn.transaction_type_id = WSMPCNST.UPDATE_QUANTITY
and sj.wip_entity_id = dj.wip_entity_id
and wrji.start_quantity <= (dj.start_quantity -
dj.quantity_scrapped - dj.quantity_completed))));
IF (l_txn_type IN (WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.UPDATE_ROUTING,
WSMPCNST.UPDATE_LOT_NAME)) THEN
l_stmt_num := 117;
SELECT wdj.start_quantity
INTO l_wdj_st_qty
FROM wip_discrete_jobs wdj,
wsm_starting_jobs_interface wsji
WHERE wsji.header_id = l_header_id
AND wsji.wip_entity_id = wdj.wip_entity_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.start_quantity = l_wdj_st_qty
WHERE wrji.header_id = l_header_id
AND wrji.start_quantity <> l_wdj_st_qty;
UPDATE wsm_resulting_jobs_interface wrji
SET routing_reference_id = NULL,
bom_reference_id = NULL
WHERE wrji.header_id = l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and wrji.process_status = WIP_CONSTANTS.RUNNING
and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
and (wrji.routing_reference_id IS NOT NULL
OR wrji.bom_reference_id IS NOT NULL);
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME
)
OR (l_txn_type in (WSMPCNST.SPLIT) and l_split_has_update_assy = 0)) then
--SpUA end
if (l_txn_type = WSMPCNST.MERGE) then
l_stmt_num := 121;
select common_routing_sequence_id, nvl(alternate_routing_designator, '-1')
into l_rtg_seq_id, l_alt_rtg_desig
from wip_discrete_jobs wdj, wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.representative_flag='Y'
and wsji.header_id = l_header_id;
select common_routing_sequence_id, nvl(alternate_routing_designator, '-1')
into l_rtg_seq_id, l_alt_rtg_desig
from wip_discrete_jobs wdj, wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.header_id=l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET common_routing_sequence_id=l_rtg_seq_id
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.header_id=l_header_id
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET alternate_routing_designator=l_alt_rtg_desig
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND wrji.header_id=l_header_id
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET alternate_routing_designator=NULL
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND wrji.header_id=l_header_id
AND wrji.rowid = l_wrji_rowid;
else /* ie. l_txn_type in (WSMPCNST.BONUS, WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING)) */
/*SpUA: Split and Update Assy is now part of this list*/
if ((l_wrji_rtg_seq_id=-1) and
(l_wrji_alt_rtg_desig='-1'))
then
l_stmt_num := 128;
UPDATE wsm_resulting_jobs_interface wrji
SET common_routing_sequence_id=
(select bor.common_routing_sequence_id
from bom_operational_routings bor
where bor.assembly_item_id = decode(wrji.job_type,
WIP_CONSTANTS.STANDARD, wrji.primary_item_id,
wrji.routing_reference_id) -- Fix for bug #2369822
and bor.alternate_routing_designator is null
and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and bor.cfm_routing_flag = 3)
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
RETURNING wrji.common_routing_sequence_id INTO l_wrji_rtg_seq_id; --Added to fix bug #2358370
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND NOT EXISTS (select 1
from bom_operational_routings bor
where bor.routing_sequence_id = wrji.common_routing_sequence_id
and bor.alternate_routing_designator = wrji.alternate_routing_designator
--and bor.assembly_item_id=wrji.primary_item_id --bugfix 2077930
and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and bor.cfm_routing_flag=3);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND NOT EXISTS (select 1
from bom_operational_routings bor
where bor.routing_sequence_id = wrji.common_routing_sequence_id
--and bor.assembly_item_id=wrji.primary_item_id --bugfix 2077930
and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and bor.cfm_routing_flag=3);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.alternate_routing_designator =
(select bor.alternate_routing_designator
from bom_operational_routings bor
where bor.routing_sequence_id = wrji.common_routing_sequence_id
--and bor.assembly_item_id=wrji.primary_item_id --bugfix 2077930
and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and bor.cfm_routing_flag=3)
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND NOT EXISTS (select 1
from bom_operational_routings bor
where bor.alternate_routing_designator = wrji.alternate_routing_designator
and bor.assembly_item_id = wrji.primary_item_id
and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and bor.cfm_routing_flag=3);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.common_routing_sequence_id =
(select bor.common_routing_sequence_id
from bom_operational_routings bor
where bor.alternate_routing_designator = wrji.alternate_routing_designator
and bor.assembly_item_id = wrji.primary_item_id
and bor.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and bor.cfm_routing_flag=3)
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
IF (l_txn_type in (WSMPCNST.UPDATE_ROUTING)) THEN
l_stmt_num := 134;
select nvl(common_routing_sequence_id, -1), nvl(alternate_routing_designator, '-1')
, routing_revision, routing_revision_date -- Added for APS-WLT
into l_rtg_seq_id, l_alt_rtg_desig
, l_wdj_rtg_rev, l_wdj_rtg_rev_dt -- Added for APS-WLT
from wip_discrete_jobs wdj, wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.header_id=l_header_id;
SELECT nvl(common_routing_sequence_id, -1), nvl(alternate_routing_designator, '-1')
INTO l_wrji_rtg_seq_id, l_wrji_alt_rtg_desig
FROM wsm_resulting_jobs_interface
WHERE header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface
SET routing_revision = l_rtg_rev,
routing_revision_date = l_rtg_rev_date
WHERE header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface rj
SET (rj.completion_subinventory,
rj.completion_locator_id) =
(SELECT bor.completion_subinventory,
/*decode(msi.locator_type, 1, NULL,*/ /*ST bug fix 3336844 */
bor.completion_locator_id /*bug 3336844 )*/ -- BBK- Bug#2795951
-- NULL) -- CZH.BUG2398718
-- This is for other locator types like Dynamic Entry (3) and
-- Item level control (4), which are not supported by OSFM.
FROM bom_operational_routings bor,
mtl_secondary_inventories msi -- CZH.BUG2398718
WHERE bor.common_routing_sequence_id = rj.common_routing_sequence_id
AND bor.organization_id = rj.organization_id
AND bor.assembly_item_id = decode(rj.job_type,
WIP_CONSTANTS.STANDARD, rj.primary_item_id,
WIP_CONSTANTS.NONSTANDARD, rj.routing_reference_id,
rj.primary_item_id)
AND msi.secondary_inventory_name = bor.completion_subinventory -- CZH.BUG2398718
AND msi.organization_id = bor.organization_id -- CZH.BUG2398718
)
WHERE rj.process_status = WIP_CONSTANTS.RUNNING
AND rj.internal_group_id = WSMPLOAD.G_GROUP_ID
AND rj.completion_subinventory is null
AND rj.common_routing_sequence_id is not null -- CZH.BUG2398718
AND rj.rowid = l_wrji_rowid --SpUA add
AND rj.header_id IN (SELECT header_id
FROM wsm_split_merge_txn_interface
WHERE internal_group_id = WSMPLOAD.G_GROUP_ID
AND process_status = WIP_CONSTANTS.RUNNING
AND (transaction_type_id IN (WSMPCNST.UPDATE_ASSEMBLY,
WSMPCNST.BONUS,
WSMPCNST.UPDATE_ROUTING)
--SpUA begin
OR (transaction_type_id = WSMPCNST.SPLIT
AND l_split_has_update_assy = 1)));
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME))
OR (l_txn_type = WSMPCNST.SPLIT and l_split_has_update_assy = 0 ) /* ST fix for bug 3327459 */
THEN
l_stmt_num := 138;
select wdj.routing_revision,
wdj.routing_revision_date
into l_rtg_rev,
l_rtg_rev_date
from wip_discrete_jobs wdj,
wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.header_id=l_header_id;
select wdj.routing_revision,
wdj.routing_revision_date
into l_rtg_rev,
l_rtg_rev_date
from wip_discrete_jobs wdj,
wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.representative_flag='Y'
and wsji.header_id=l_header_id;
/* ST bug fix 3493984 Update the resulting job interface */
l_stmt_num := 139.1;
update wsm_resulting_jobs_interface wrji
set routing_revision_date = l_rtg_rev_date,
routing_revision = l_rtg_rev
where wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid;
| Disallow update txn from "ToMove" intraop if that operation is |
| the last operation of the new routing. |
| CZH.I_OED-1, we also check whether routing end is effective. |
+-----------------------------------------------------------------*/
DECLARE
l_op_seq_id NUMBER;
SELECT nvl(common_routing_sequence_id, -1)
INTO l_wrji_rtg_seq_id
FROM wsm_resulting_jobs_interface
WHERE header_id = l_header_id and rowid = l_wrji_rowid; -- WLTEnh.
select wrji.starting_operation_seq_num
into l_wrji_starting_op_seq_num
from wsm_resulting_jobs_interface wrji
where wrji.header_id = l_header_id;
SELECT bos.operation_sequence_id,
bos.operation_seq_num, -- BBK: NSO:WLT
nvl(bos.standard_operation_id, -9999), -- BBK: NSO:WLT
sj.intraoperation_step,
nvl(rj.common_routing_sequence_id,-1) --VJ: Added for NSLBJ--
--Combined with sql at stmt 345--
INTO l_op_seq_id,
l_bos_opseq_num,
l_bos_stdop_id,
l_sj_intraoperation_step, -- BBK:NSO:WLT
l_rj_rout_seq_id --VJ: Added for NSLBJ--
FROM bom_operation_sequences bos,
wsm_resulting_jobs_interface rj,
wsm_starting_jobs_interface sj,
wip_operations wo
WHERE sj.header_id = l_header_id
AND rj.rowid = l_wrji_rowid --SpUA add
AND sj.header_id = rj.header_id
AND sj.operation_seq_num = wo.operation_seq_num
AND sj.wip_entity_id = wo.wip_entity_id
AND rj.common_routing_sequence_id = bos.routing_sequence_id
AND nvl(wo.standard_operation_id, -9999) = nvl(bos.standard_operation_id, -9999) -- BBK:NSOWLT
-- BC: CZH.I_OED-1, should get routing_revision date
--AND bos.effectivity_date <= SYSDATE
--AND nvl(bos.disable_date, SYSDATE+2) > SYSDATE ;
Select operation_code into l_bso_stdop_code
From BOM_STANDARD_OPERATIONS
Where standard_operation_id = l_bos_stdop_id;
SELECT wsji.intraoperation_step
INTO l_st_iop
FROM wsm_starting_jobs_interface wsji,
wsm_split_merge_txn_interface wsmti
WHERE wsji.header_id = l_header_id
AND wsmti.header_id = l_header_id
AND (wsmti.transaction_type_id in
(WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING )
OR (wsmti.transaction_type_id in (WSMPCNST.SPLIT)
AND l_split_has_update_assy = 1));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid
AND wrji.starting_operation_seq_num IS NULL
AND wrji.starting_operation_code IS NULL;
UPDATE wsm_resulting_jobs_interface wrji
SET starting_operation_seq_num = l_bos_opseq_num,
starting_operation_code = l_bso_stdop_code
WHERE wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid --SpUA add
and wrji.process_status = WIP_CONSTANTS.RUNNING
and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
and exists (
select 1
from wsm_starting_jobs_interface sj,
wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.header_id = sj.header_id
and (txn.transaction_type_id in
(WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING )
--SpUA begin
OR (txn.transaction_type_id in (WSMPCNST.SPLIT)
AND l_split_has_update_assy = 1))
--SpUA end
--Start Changes to fix bug #2352369--
--and sj.intraoperation_step = WIP_CONSTANTS.TOMOVE
and (nvl(wrji.starting_std_op_id, l_bos_stdop_id) = l_bos_stdop_id OR
nvl(wrji.starting_operation_code, l_bso_stdop_code) = l_bso_stdop_code)
and wrji.starting_operation_seq_num IS NULL -- ADD: bugfix 2672003, 2682611
and wrji.starting_operation_code IS NULL -- ADD: bugfix 2672003, 2682611
--End Changes to fix bug #2352369--
);
UPDATE wsm_resulting_jobs_interface wrji
SET starting_operation_seq_num = NULL,
starting_operation_code = NULL
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and wrji.process_status = WIP_CONSTANTS.RUNNING
and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID
and exists (
select 1
from wsm_starting_jobs_interface sj,
wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and txn.header_id = sj.header_id
and (txn.transaction_type_id in
(WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING )
--SpUA begin
OR (txn.transaction_type_id in (WSMPCNST.SPLIT)
AND l_split_has_update_assy = 1))
--SpUA end
and sj.intraoperation_step = WIP_CONSTANTS.TOMOVE
);
WSMPCNST.UPDATE_QUANTITY,
WSMPCNST.UPDATE_LOT_NAME)
OR (l_txn_type in (WSMPCNST.SPLIT) and l_split_has_update_assy = 0))
then
--SpUA end
if (l_txn_type = WSMPCNST.MERGE) then
l_stmt_num := 153;
select common_bom_sequence_id, nvl(alternate_bom_designator, '-1')
into l_bom_seq_id, l_alt_bom_desig
from wip_discrete_jobs wdj, wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.representative_flag='Y'
and wsji.header_id=l_header_id;
select common_bom_sequence_id, nvl(alternate_bom_designator, '-1')
into l_bom_seq_id, l_alt_bom_desig
from wip_discrete_jobs wdj, wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.header_id=l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET common_bom_sequence_id=l_bom_seq_id
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.header_id=l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET alternate_bom_designator=l_alt_bom_desig
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.header_id=l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_resulting_jobs_interface wrji
SET alternate_bom_designator=NULL
WHERE wrji.process_status = WIP_CONSTANTS.RUNNING
AND wrji.header_id=l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND wrji.rowid = l_wrji_rowid;
else /* ie. if (l_txn_type in (WSMPCNST.BONUS, WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING)) */
/* SpUA: Split and Update Assy is now part of this list */
l_stmt_num := 160;
UPDATE wsm_resulting_jobs_interface wrji
SET common_bom_sequence_id=
(select bom.common_bill_sequence_id
from bom_bill_of_materials bom,
mtl_system_items msi --bugfix 1828859
where bom.assembly_item_id = wrji.primary_item_id
and bom.alternate_bom_designator is null
and bom.organization_id = l_organization_id --bugfix 1922146: added orgn_id condn
and msi.inventory_item_id = bom.assembly_item_id --bugfix 1828859
and msi.organization_id = bom.organization_id
and ((bom.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bom.assembly_type = 2)))
-- and bom.assembly_type = 1) --endfix
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND NOT EXISTS (select 1
from bom_bill_of_materials bom,
mtl_system_items msi --bugfix 1828859
where bom.bill_sequence_id = wrji.common_bom_sequence_id
and bom.alternate_bom_designator = wrji.alternate_bom_designator
and bom.organization_id = l_organization_id
and msi.inventory_item_id = bom.assembly_item_id --bugfix 1828859
and msi.organization_id = bom.organization_id
and ((bom.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bom.assembly_type = 2)));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
AND NOT EXISTS (select 1
from bom_bill_of_materials bom,
mtl_system_items msi --bugfix 1828859
where bom.bill_sequence_id = wrji.common_bom_sequence_id
--and bom.assembly_item_id=wrji.primary_item_id --bugfix 2077930
and bom.organization_id = l_organization_id -- bugfix 1922146: added orgn_id condn
and msi.inventory_item_id = bom.assembly_item_id --bugfix 1828859
and msi.organization_id = bom.organization_id
and ((bom.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bom.assembly_type = 2)));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.alternate_bom_designator = (select bom.alternate_bom_designator
from bom_bill_of_materials bom,
mtl_system_items msi --bugfix 1828859
where bom.bill_sequence_id = wrji.common_bom_sequence_id
--and bom.assembly_item_id=wrji.primary_item_id --bugfix 2077930
and bom.organization_id = l_organization_id
--bugfix 1922146: added orgn_id condn
and msi.inventory_item_id = bom.assembly_item_id --bugfix 1828859
and msi.organization_id = bom.organization_id
and ((bom.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bom.assembly_type = 2)))
-- and bom.assembly_type=1) --endfix
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid --SpUA add
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and NOT EXISTS (select 1
from bom_bill_of_materials bom,
mtl_system_items msi --bugfix 1828859
where bom.alternate_bom_designator = wrji.alternate_bom_designator
and bom.assembly_item_id = wrji.primary_item_id
and bom.organization_id = l_organization_id -- bugfix 1922146: added orgn_id condn
and msi.inventory_item_id = bom.assembly_item_id --bugfix 1828859
and msi.organization_id = bom.organization_id
and ((bom.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bom.assembly_type = 2)));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.common_bom_sequence_id = (select bom.common_bill_sequence_id
from bom_bill_of_materials bom,
mtl_system_items msi --bugfix 1828859
where bom.alternate_bom_designator = wrji.alternate_bom_designator
and bom.assembly_item_id = wrji.primary_item_id
and bom.organization_id = l_organization_id
-- bugfix 1922146: added orgn_id condn
and msi.inventory_item_id = bom.assembly_item_id --bugfix 1828859
and msi.organization_id = bom.organization_id
and ((bom.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bom.assembly_type = 2)))
-- and bom.assembly_type=1) --endfix
WHERE wrji.header_id = l_header_id
AND wrji.rowid = l_wrji_rowid
AND wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
if (l_txn_type in (WSMPCNST.UPDATE_ROUTING)) THEN
l_stmt_num := 167;
select nvl(common_bom_sequence_id, -1), nvl(alternate_bom_designator, '-1')
into l_bom_seq_id, l_alt_bom_desig
from wip_discrete_jobs wdj, wsm_starting_jobs_interface wsji
where wdj.wip_entity_id=wsji.wip_entity_id
and wsji.header_id=l_header_id;
SELECT nvl(common_bom_sequence_id, -1), nvl(alternate_bom_designator, '-1')
INTO l_wrji_bom_seq_id, l_wrji_alt_bom_desig
FROM wsm_resulting_jobs_interface
WHERE header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET common_bom_sequence_id = l_bom_seq_id,
alternate_bom_designator = l_alt_bom_desig
WHERE wrji.header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and not exists (select null
from bom_routing_alternates_v bor
where bor.assembly_item_id = wrji.primary_item_id
and bor.organization_id = wrji.organization_id
and bor.cfm_routing_flag = 3
and (bor.alternate_routing_designator =
wrji.alternate_routing_designator or
wrji.alternate_routing_designator is NULL));
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and not exists
(select null
from bom_bill_alternates_v bba,
mtl_system_items msi --bugfix 1828859
where bba.assembly_item_id = wrji.primary_item_id
and bba.organization_id = wrji.organization_id
and msi.inventory_item_id = bba.assembly_item_id --bugfix 1828859
and msi.organization_id = bba.organization_id
and ((bba.assembly_type = 1 and msi.eng_item_flag = 'N')
or (bba.assembly_type = 2))
and (bba.alternate_bom_designator =
NVL(wrji.alternate_bom_designator, bba.alternate_bom_designator)) -- or
-- wrji.alternate_bom_designator is NULL)); --- BugFix2627354
select null from dual where l_wrji_alt_bom_desig = '-1'
/*EA2627354*/
);
UPDATE WSM_RESULTING_JOBS_INTERFACE WRJI
SET WRJI.routing_revision = l_routing_rev,
WRJI.routing_revision_date = l_rev_date
WHERE WRJI.header_id = l_header_id
AND WRJI.wip_entity_name = c_bom_rtg_revision_rec.we_name
AND (WRJI.routing_revision IS NULL
OR WRJI.routing_revision_date IS NULL);
UPDATE WSM_RESULTING_JOBS_INTERFACE WRJI
SET WRJI.bom_revision = l_bom_revision,
WRJI.bom_revision_date = l_bom_revision_date
WHERE WRJI.header_id = l_header_id
AND WRJI.wip_entity_name = c_bom_rtg_revision_rec.we_name;
/* ST : 3712972 Update in all the cases.... revision date given precedence....
AND (WRJI.bom_revision IS NULL
OR WRJI.bom_revision_date IS NULL);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND wrji.job_type = WIP_CONSTANTS.STANDARD --VJ: Added for NSLBJ--
and not exists (select null
from mtl_routing_rev_highdate_v rou
where wrji.organization_id = rou.organization_id
--VJ: Start Changes for NSLBJ--
and rou.inventory_item_id = decode(l_job_type,
WIP_CONSTANTS.STANDARD,
wrji.primary_item_id,
WIP_CONSTANTS.NONSTANDARD,
wrji.routing_reference_id)
--VJ: End Changes for NSLBJ--
and rou.process_revision = nvl(wrji.routing_revision,
rou.process_revision)
--BD: CZH: OED-1, DO NOT HAVE TO
--and nvl(wrji.routing_revision_date,sysdate)
-- between rou.effectivity_date and rou.high_date
--ED: CZH: OED-1
);
SELECT REV_REC.HEADER_ID INTO l_temp
FROM mtl_item_rev_highdate_v MIR ,
wsm_resulting_jobs_interface RJ
WHERE MIR.ORGANIZATION_ID = REV_REC.ORGANIZATION_ID
AND RJ.ROWID = chartorowid(REV_REC.X_ROWID)
AND MIR.INVENTORY_ITEM_ID = RJ.PRIMARY_ITEM_ID
AND ( (MIR.REVISION = nvl(RJ.BOM_REVISION, MIR.REVISION))
AND ( nvl(RJ.BOM_REVISION_DATE,MIR.EFFECTIVITY_DATE) BETWEEN
MIR.EFFECTIVITY_DATE AND MIR.HIGH_DATE ) );
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_err_msg
WHERE ROWID = chartorowid(REV_REC.X_ROWID);
| For update routing/assembly, if the starting operation for the |
| resulting lot is |
| . A non-std op or |
| . Not present in the resulting job or |
| . Appears several times in the n/w routing or |
| . Does not appear in the n/w routing, |
| then |
| . Error out the transaction with an appropriate message. |
+------------------------------------------------------------------*/
-- EA: NSO-WLT
-- ***********************************************************************
if (l_txn_type in (WSMPCNST.UPDATE_ASSEMBLY, WSMPCNST.UPDATE_ROUTING)
--SpUA begin
OR (l_txn_type in (WSMPCNST.SPLIT) AND l_split_has_update_assy = 1)) then
--SpUA end
DECLARE
l_com_rtg_seq_id NUMBER;
select
bor.common_routing_sequence_id,
wrji.primary_item_id,
wrji.starting_operation_seq_num,
wrji.starting_operation_code,
wrji.starting_std_op_id
, wrji.organization_id
, wsji.intraoperation_step
into
l_com_rtg_seq_id,
l_pri_it_id,
l_start_op_seq_num,
l_start_op_code,
l_start_std_op_id
, l_organization_id
, l_sj_intraoperation_step
from
bom_operational_routings bor,
wsm_resulting_jobs_interface wrji,
wsm_starting_jobs_interface wsji
where
bor.assembly_item_id = wrji.primary_item_id
and nvl(bor.alternate_routing_designator, '*') = nvl(wrji.alternate_routing_designator, '*')
and wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid --SpUA add
and wsji.header_id = wrji.header_id
and bor.organization_id = wrji.organization_id --VJ: Added for NSLBJ--
--Above condition also a fix for bug #2269765--
and wrji.job_type = WIP_CONSTANTS.STANDARD; --VJ: Added for NSLBJ--
select
wrji.common_routing_sequence_id,
wrji.primary_item_id,
wrji.starting_operation_seq_num,
wrji.starting_operation_code,
wrji.starting_std_op_id
, wrji.organization_id
, wsji.intraoperation_step
into
l_com_rtg_seq_id,
l_pri_it_id,
l_start_op_seq_num,
l_start_op_code,
l_start_std_op_id
, l_organization_id
, l_sj_intraoperation_step
from
wsm_resulting_jobs_interface wrji,
wsm_starting_jobs_interface wsji
where
wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid --SpUA add
and wsji.header_id = wrji.header_id
and wrji.job_type = WIP_CONSTANTS.NONSTANDARD; --VJ: Added for NSLBJ--
select standard_operation_id
into l_start_std_op_id
from bom_standard_operations
where organization_id = l_organization_id -- BBK
and operation_type = 1 -- Standard Operation Type BBK
and line_id is NULL -- Not for a WIP Line BBK
and operation_code = l_start_op_code;
select bos.operation_seq_num
into l_start_op_seq_num
from bom_operation_sequences bos
where bos.routing_sequence_id = l_com_rtg_seq_id
and bos.operation_type = 1
and bos.STANDARD_OPERATION_ID = l_start_std_op_id;
UPDATE wsm_resulting_jobs_interface wrji
SET starting_std_op_id = l_start_std_op_id,
starting_operation_seq_num = l_start_op_seq_num, -- ADD: BUGFIX 2686319
starting_operation_code = l_start_op_code
WHERE wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid --SpUA add
and wrji.process_status = WIP_CONSTANTS.RUNNING
and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_err_msg
WHERE header_id = l_header_id
AND rowid = l_wrji_rowid; --SpUA add
end if; -- if (l_txn_type in (update asly/rtg)
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS)
AND wrji.starting_std_op_id is not null
AND wrji.starting_operation_code is not null
AND wrji.starting_std_op_id <>
( SELECT bso.standard_operation_id
FROM bom_standard_operations bso
WHERE bso.organization_id = wrji.organization_id
AND bso.operation_code = wrji.starting_operation_code);
SELECT count(*)
INTO l_count
FROM wsm_resulting_jobs_interface wrji
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS);
SELECT wrji.starting_std_op_id,
wrji.starting_operation_code,
wrji.starting_operation_seq_num
INTO l_start_std_op_id,
l_start_op_code,
l_start_op_seq_num
FROM wsm_resulting_jobs_interface wrji
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS);
UPDATE wsm_resulting_jobs_interface
SET error_message = p_err_msg
where header_id = l_header_id;
select bos.operation_seq_num,
bos.standard_operation_id
into l_bos_op_seq_num,
l_bos_std_op_id
from bom_operation_sequences bos
where bos.operation_sequence_id = l_start_op_seq_id;
SELECT operation_code
INTO l_bso_op_code
FROM bom_standard_operations
WHERE standard_operation_id = l_bos_std_op_id
AND organization_id = l_organization_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.starting_operation_seq_num = l_bos_op_seq_num,
wrji.starting_operation_code = l_bso_op_code,
wrji.starting_std_op_id = l_bos_std_op_id
WHERE wrji.header_id = l_header_id;
select wrji.common_routing_sequence_id, wrji.organization_id
into l_rl_rtg_seq_id, l_organization_id
from wsm_resulting_jobs_interface wrji
where wrji.header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
WHERE wsji.header_id = l_header_id
and wip_entity_name = l_job_name;
SELECT bos.operation_sequence_id,
bos.standard_operation_id -- 1685779 :added this col as part of select
INTO l_bos_op_seq_id,
l_bos_std_op_id
FROM bom_standard_operations bso,
bom_operation_sequences bos,
wsm_resulting_jobs_interface wrji
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS)
AND bos.routing_sequence_id = wrji.common_routing_sequence_id
AND nvl(bos.standard_operation_id, -9999) = nvl(bso.standard_operation_id, -9999)
AND nvl(bso.operation_code, '*') = nvl(l_start_op_code, '*')
AND bos.operation_seq_num = l_start_op_seq_num --2265237
-- BC: CZH.I_OED-1, check against routing revsion date
-- bugfix 1709639. Check for disabled operation
--AND nvl(bos.disable_date, sysdate+1) > sysdate
--AND bos.effectivity_date <= sysdate;
SELECT bos.operation_seq_num,
bos.operation_sequence_id,
bos.standard_operation_id -- 1685779 :added this col as part of select
INTO l_bos_op_seq_num,
l_bos_op_seq_id,
l_bos_std_op_id
FROM bom_standard_operations bso,
bom_operation_sequences bos,
wsm_resulting_jobs_interface wrji
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS)
AND bos.routing_sequence_id = wrji.common_routing_sequence_id
AND nvl(bos.standard_operation_id, -9999) = nvl(bso.standard_operation_id, -9999)
AND nvl(bso.operation_code, '*') = nvl(l_start_op_code, '*')
-- BC: CZH.I_OED-1, check against routing revsion date
-- bugfix 1709639. Check for disabled operation
--AND nvl(bos.disable_date, sysdate+1) > sysdate
--AND bos.effectivity_date <= sysdate;
UPDATE wsm_resulting_jobs_interface wrji
SET starting_operation_seq_num = l_bos_op_seq_num
WHERE wrji.header_id = l_header_id
and wrji.process_status = WIP_CONSTANTS.RUNNING
and wrji.internal_group_id = WSMPLOAD.G_GROUP_ID;
UPDATE wsm_resulting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
WHERE wsji.header_id = l_header_id
and wip_entity_name = l_job_name;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.starting_operation_seq_num = l_bos_op_seq_num
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS);
SELECT bos.operation_sequence_id,
bos.standard_operation_id
INTO l_bos_op_seq_id,
l_bos_std_op_id
FROM bom_operation_sequences bos,
wsm_resulting_jobs_interface wrji
where wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS)
AND bos.routing_sequence_id = wrji.common_routing_sequence_id
and bos.operation_seq_num = l_start_op_seq_num
-- bugfix 1746686 Validate operation effectivity
-- BC: CZH.I_OED-1, check against routing revsion date
-- bugfix 1709639. Check for disabled operation
--HH24MISS ---AND nvl(bos.disable_date, sysdate+1) > sysdate
--HH24MISS ---AND bos.effectivity_date <= sysdate;
Select operation_code into l_bso_op_code
From BOM_STANDARD_OPERATIONS
Where standard_operation_id = l_bos_std_op_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.starting_operation_code = l_bso_op_code
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.starting_std_op_id = l_bos_std_op_id
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND EXISTS (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS -- BA NSO-WLT
AND wrji.starting_operation_seq_num is null); -- EA NSO-WLT
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
AND exists (SELECT null
FROM wsm_split_merge_txn_interface txn
WHERE txn.header_id = wrji.header_id
AND txn.transaction_type_id = WSMPCNST.BONUS)
AND nvl(wrji.starting_std_op_id, -9999) = nvl(l_bos_std_op_id, -9999)
AND not exists
( select 'Is Primary Path'
from bom_operation_networks bon,
bom_operation_sequences bos
where bon.transition_type = 1 -- Primary
and nvl(bon.disable_date, sysdate+1) > sysdate
--BC: CZH.I_OED-2
--and bon.from_op_seq_id = bos.operation_sequence_id
and WSMPUTIL.replacement_op_seq_id(
bon.from_op_seq_id,
l_rtg_rev_date) = bos.operation_sequence_id
--EC: CZH.I_OED-2
and bos.routing_sequence_id = wrji.common_routing_sequence_id -- BD NSO-WLT
-- BA NSO-WLT
and bos.operation_seq_num = wrji.starting_operation_seq_num
-- BA NSO-WLT
UNION
select 'Is Primary Path'
from bom_operation_networks bon,
bom_operation_sequences bos
--BC: CZH.I_OED-2
--where bon.to_op_seq_id = bos.operation_sequence_id
where WSMPUTIL.replacement_op_seq_id(
bon.to_op_seq_id,
l_rtg_rev_date) = bos.operation_sequence_id
--EC: CZH.I_OED-2
and bos.routing_sequence_id = wrji.common_routing_sequence_id -- BD NSO-WLT
-- BA NSO-WLT
and bos.operation_seq_num = wrji.starting_operation_seq_num
-- BA NSO-WLT
and bon.to_op_seq_id not in ( select bon1.from_op_seq_id
from bom_operation_networks bon1,
bom_operation_sequences bos1
--BC: CZH.I_OED-2
--where bon1.from_op_seq_id = bos1.operation_sequence_id
where WSMPUTIL.replacement_op_seq_id (
bon1.from_op_seq_id,
l_rtg_rev_date) = bos1.operation_sequence_id
--EC: CZH.I_OED-2
and bos1.routing_sequence_id = wrji.common_routing_sequence_id)
);
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and wrji.starting_intraoperation_step is not null
and NOT EXISTS (select null
from wsm_split_merge_txn_interface txn
where txn.header_id = wrji.header_id
and wrji.starting_intraoperation_step =
decode(txn.transaction_type_id,WSMPCNST.BONUS,1,wrji.starting_intraoperation_step)
and wrji.starting_intraoperation_step in
(WIP_CONSTANTS.QUEUE,
WIP_CONSTANTS.TOMOVE));
SELECT transaction_type_id
INTO l_txn_type
FROM wsm_split_merge_txn_interface wsmti
WHERE wsmti.header_id = l_header_id;
IF (l_txn_type = WSMPCNST.UPDATE_ROUTING) THEN
-- need to make sure that completion date
-- has not been changed.
l_stmt_num := 218;
SELECT wdj.scheduled_completion_date
into l_sch_comp_date
from wip_discrete_jobs wdj,
wsm_starting_jobs_interface wsji
WHERE wdj.wip_entity_id = wsji.wip_entity_id
AND wsji.header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET scheduled_completion_date = l_sch_comp_date
WHERE wrji.header_id = l_header_id
AND scheduled_completion_date <> l_sch_comp_date;
select completion_subinventory,
completion_locator_id /*ST bug fix 3336844 nvl(completion_locator_id, -1)*/
into l_comp_sub_inv_r,
l_comp_loc_id_r
from wsm_resulting_jobs_interface
where header_id = l_header_id
and wip_entity_name = l_job_name;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_generic_err_msg
where header_id = l_header_id
and wip_entity_name = l_job_name;
select count(secondary_inventory_name)
into l_count
from mtl_subinventories_val_v
where organization_id = l_organization_id
and secondary_inventory_name = l_comp_sub_inv_r;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_generic_err_msg
where header_id = l_header_id
and wip_entity_name = l_job_name;
SELECT nvl(msub.locator_type, 1) sub_loc_control,
MP.stock_locator_control_code org_loc_control,
MS.restrict_locators_code,
MS.location_control_code item_loc_control
INTO l_sub_loc_control, l_org_loc_control,
l_restrict_locators_code, l_item_loc_control
FROM mtl_system_items MS,
mtl_secondary_inventories MSUB,
mtl_parameters MP
WHERE MP.organization_id = l_organization_id
AND MS.organization_id = l_organization_id
AND MS.inventory_item_id = l_primary_item_id
AND MSUB.secondary_inventory_name = l_comp_sub_inv_r
AND MSUB.organization_id = l_organization_id;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_generic_err_msg
where header_id = l_header_id
and wip_entity_name = l_job_name;
select locator_type
into l_locator_control_type
from mtl_secondary_inventories
where SECONDARY_INVENTORY_NAME = l_comp_sub_inv_r
and organization_id = l_organization_id;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_generic_err_msg
where header_id = l_header_id
and wip_entity_name = l_job_name;
select count(*)
into l_count
from mtl_item_locations_kfv
where INVENTORY_LOCATION_ID = l_comp_loc_id_r
and SUBINVENTORY_CODE = l_comp_sub_inv_r
and organization_id = l_organization_id;
UPDATE wsm_resulting_jobs_interface
SET error_message = l_fnd_generic_err_msg
where header_id = l_header_id
and wip_entity_name = l_job_name;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.completion_locator_id = NULL
where header_id = l_header_id
and wip_entity_name = l_job_name;
IF (l_txn_type in ( WSMPCNST.BONUS, WSMPCNST.UPDATE_ROUTING, WSMPCNST.UPDATE_ASSEMBLY)
--SpUA begin
OR (l_txn_type in (WSMPCNST.SPLIT) AND l_split_has_update_assy = 1)) THEN
--SpUA end
--SpUA bugfix 3161200
--This is not really a SpUA bug!! But fixed anyway. For the valid case where
--of non-std op TM, we need not have a starting op_seq_num. SQL below fails.
--It is not necessary to execute this code at ToMove for APS Profile Option A
declare
l_sj_intraoperation_step number;
select intraoperation_step
into l_sj_intraoperation_step
from wsm_starting_jobs_interface
where header_id = l_header_id;
select wrji.starting_operation_seq_num
into l_osp_start_op_seq_id
from wsm_resulting_jobs_interface wrji
where wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid; --SpUA add
select unique WSMPUTIL.replacement_op_seq_id ( operation_sequence_id,l_rtg_rev_date)
into l_osp_start_op_seq_id
from bom_operation_sequences bos,
wsm_resulting_jobs_interface wrji
where wrji.header_id = l_header_id
and wrji.rowid = l_wrji_rowid --SpUA add
and bos.routing_sequence_id = wrji.common_routing_sequence_id
and bos.operation_seq_num = wrji.starting_operation_seq_num ;
If l_wrji_error_flag = 1 Then -- UPDATE WSMTI
l_stmt_num := 234;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
where wrji.header_id = l_header_id
and wrji.wip_entity_name = NVL(l_job_name, wrji.wip_entity_name);
Update wsm_starting_jobs_interface wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
Where wsji.header_id = l_header_id;
Update wsm_split_merge_txn_interface wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
Where wsmti.header_id = l_header_id;
UPDATE WSM_STARTING_JOBS_INTERFACE wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.header_id = l_header_id
AND wsji.WIP_ENTITY_ID = l_wip_id;
UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
WHERE wrji.header_id = l_header_id;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.header_id = l_header_id;
UPDATE WSM_STARTING_JOBS_INTERFACE wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.header_id = l_header_id
AND wsji.WIP_ENTITY_ID = l_wip_id;
UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
WHERE wrji.header_id = l_header_id;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.header_id = l_header_id;
UPDATE WSM_STARTING_JOBS_INTERFACE wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.header_id = l_header_id
AND wsji.WIP_ENTITY_ID = l_wip_id;
UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
WHERE wrji.header_id = l_header_id;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.header_id = l_header_id;
UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
WHERE wrji.header_id = l_header_id
AND wrji.WIP_ENTITY_NAME = l_job_name;
UPDATE WSM_STARTING_JOBS_INTERFACE wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.header_id = l_header_id;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.header_id = l_header_id;
UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
WHERE wrji.header_id = l_header_id
AND wrji.WIP_ENTITY_NAME = l_job_name;
UPDATE WSM_STARTING_JOBS_INTERFACE wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.header_id = l_header_id;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.header_id = l_header_id;
UPDATE WSM_RESULTING_JOBS_INTERFACE wrji
SET wrji.error_message = l_fnd_generic_err_msg
, wrji.process_status = WIP_CONSTANTS.ERROR
WHERE wrji.header_id = l_header_id
AND wrji.WIP_ENTITY_NAME = l_job_name;
UPDATE WSM_STARTING_JOBS_INTERFACE wsji
SET wsji.error_message = l_fnd_generic_err_msg
, wsji.process_status = WIP_CONSTANTS.ERROR
WHERE wsji.header_id = l_header_id;
UPDATE WSM_SPLIT_MERGE_TXN_INTERFACE wsmti
SET wsmti.error_message = l_fnd_generic_err_msg
, wsmti.process_status = WIP_CONSTANTS.ERROR
WHERE wsmti.header_id = l_header_id;
UPDATE wsm_resulting_jobs_interface wrji
SET wrji.error_message = l_fnd_generic_err_msg
WHERE wrji.header_id = l_header_id
and (wrji.net_quantity < 0 --fixed as a part of bug #2352369
-- and (wrji.net_quantity <= 0
or
wrji.net_quantity > wrji.start_quantity ); --bugfix 2168797: replaced AND with OR
SELECT nvl(wrji.starting_operation_seq_num, -1), wrji.starting_operation_code
INTO l_wrji_starting_op_seq_num, l_wrji_starting_op_code
FROM wsm_resulting_jobs_interface wrji,
wsm_split_merge_txn_interface wsmti
WHERE wrji.header_id = l_header_id
AND wsmti.header_id = wrji.header_id
AND (wsmti.transaction_type_id in (3,5) -- Upd rtg/assy
OR (wsmti.transaction_type_id = 1
AND wrji.split_has_update_assy = 1)) -- SpUA
AND wrji.rowid = l_wrji_rowid;
UPDATE wsm_split_merge_txn_interface
SET error_message = l_fnd_err_msg -- error_message = error_message || l_fnd_err_msg
, process_status = WIP_CONSTANTS.ERROR -- WLTEnh
WHERE header_id IN ( SELECT distinct header_id
FROM wsm_starting_jobs_interface
WHERE error_message IS NOT NULL)
AND process_status = WIP_CONSTANTS.RUNNING;
UPDATE wsm_split_merge_txn_interface
SET error_message = l_fnd_err_msg -- error_message = error_message || l_fnd_err_msg
, process_status = WIP_CONSTANTS.ERROR -- WLTEnh
WHERE header_id IN ( SELECT distinct header_id
FROM wsm_resulting_jobs_interface
WHERE error_message IS NOT NULL)
AND process_status = WIP_CONSTANTS.RUNNING;
select 1 into l_errored_rows
from wsm_split_merge_txn_interface
Where internal_group_id = WSMPLOAD.G_GROUP_ID
and header_id = WSMPLOAD.G_HEADER_ID
and process_status = WIP_CONSTANTS.ERROR;
| Only one starting lot must exist for split and update transactions. |
| Only one resulting lot must exist for bonus, merge, update transactions.|
| At least one starting lot must exist for merge transactions. |
| At least one resulting lot must exist for split transaction. |
+------------------------------------------------------------------------*/
PROCEDURE CHECK_NUMBER_OF_LOTS(p_err_msg IN OUT NOCOPY VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER) IS
BEGIN
null;
| update the values and populate a warning message in the record. |
+-----------------------------------------------------------------*/
--****************************************************************************
----------NOT USED ANY LONGER ---------------------------------------------------
--****************************************************************************
PROCEDURE MATCH_START_RESULT_LOT (p_err_msg IN OUT NOCOPY VARCHAR2,
p_err_num IN OUT NOCOPY NUMBER)IS
BEGIN
NULL;