[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
type t_wlji_last_updt_date is table of wsm_lot_job_interface.last_update_date%type;
type t_wlji_last_updt_by is table of wsm_lot_job_interface.last_updated_by%type;
type t_wlji_last_updt_login is table of wsm_lot_job_interface.last_update_login%type;
type t_wlji_prog_updt_date is table of wsm_lot_job_interface.program_update_date%type;
type t_wlji_last_updt_by_name is table of wsm_lot_job_interface.last_updated_by_name%type;
type t_wsli_last_updated_by is table of wsm_starting_lots_interface.last_updated_by%type;
v_wsli_last_updated_by t_wsli_last_updated_by := t_wsli_last_updated_by();
last_updated_by wsm_starting_lots_interface.last_updated_by%type,
created_by wsm_starting_lots_interface.created_by%type,
primary_uom_code mtl_system_items.primary_uom_code%type,
comp_issue_quantity wsm_starting_lots_interface.component_issue_quantity%type);
select organization_id,
organization_code
from wsm_lot_job_interface
where process_status = p_status
and NVL(transaction_date, creation_date) <= sysdate+1
and NVL(group_id, -99) = NVL(p_group_id, NVL(group_id, -99))
and load_type in (5, 6)
union
select organization_id,
organization_code
from wsm_lot_job_dtl_interface
where process_status = p_status
and parent_header_id IS NULL
and NVL(group_id, -99) = NVL(p_group_id, NVL(group_id, -99))
and transaction_date <= sysdate+1;
select organization_id
into l_org_id
--from ORG_ORGANIZATION_DEFINITIONS
from mtl_parameters
where organization_code = l_org_code;
select 1
into l_temp
from MTL_PARAMETERS MP ,
WSM_PARAMETERS WSM,
HR_ALL_ORGANIZATION_UNITS ORG,
WIP_PARAMETERS WP
where MP.ORGANIZATION_ID = WSM.ORGANIZATION_ID
and ORG.ORGANIZATION_ID = WSM.ORGANIZATION_ID
and WP.ORGANIZATION_ID = WSM.ORGANIZATION_ID
and UPPER(MP.WSM_ENABLED_FLAG)='Y'
and TRUNC(SYSDATE) <= NVL(ORG.DATE_TO, SYSDATE+1)
and WSM.ORGANIZATION_ID = l_org_id;
select from_op_seq_id "from_opseq_id", level
from bom_operation_networks
where transition_type = 1
start with to_op_seq_id = l_bon_seq_id1 and transition_type = 1
connect by to_op_seq_id = prior from_op_seq_id and transition_type = 1
union
select l_bon_seq_id1 "from_opseq_id", -1
from dual
) order by 2 desc;
select wip_entities_s.nextval
into p_wip_entity_id
from dual;
select operation_seq_num
into l_rtg_op_seq_num
from bom_operation_sequences
where operation_sequence_id = l_start_seq_id
and routing_sequence_id = l_common_routing_sequence_id;
select unique(bos.operation_sequence_id)
into l_bon_seq_id1
from bom_operation_sequences bos, bom_operation_networks bon
where bos.operation_seq_num = l_rtg_op_seq_num
and bos.routing_sequence_id = l_common_routing_sequence_id
-- BA: CZH.OED-2, it may have a replacement
and (bos.operation_sequence_id = bon.from_op_seq_id
or bos.operation_sequence_id = bon.to_op_seq_id);
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into l_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = p_org;
insert_procedure(
p_seq_id => l_bon_seq_id2,
p_job_seq_num => l_job_seq_num,
p_common_routing_sequence_id => l_common_routing_sequence_id,
p_supply_type => p_wip_supply_type,
p_wip_entity_id => p_wip_entity_id,
p_organization_id => p_org,
p_quantity => p_start_quantity,
p_job_type => p_job_type,
p_bom_reference_id => p_bom_reference_id,
p_rtg_reference_id => p_routing_reference_id,
p_assembly_item_id => p_item,
p_alt_bom_designator => p_alt_bom_designator,
p_alt_rtg_designator => p_alt_routing_designator,
p_fusd => p_fusd,
--p_lucd => p_lucd, -- bug 3520916
p_lucd => l_lucd, -- bug 3520916
p_rtg_revision_date => p_routing_revision_date,
p_bom_revision_date => p_bom_revision_date,
p_last_updt_date => p_last_updt_date,
p_last_updt_by => p_last_updt_by,
p_creation_date => p_creation_date,
p_created_by => p_created_by,
p_last_updt_login => p_last_updt_login,
p_request_id => p_request_id,
p_program_application_id => p_program_application_id,
p_program_id => p_program_id,
p_prog_updt_date => p_prog_updt_date,
p_error_code => l_error_code,
p_error_msg => l_error_msg);
select operation_seq_num
into abb_op_seq_num
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_sequence_id = wsmputil.replacement_op_seq_id(
l_bon_seq_id1,
p_routing_revision_date);
select max(operation_seq_num)
into max_op_seq_num
from wip_operations
where wip_entity_id = p_wip_entity_id;
UPDATE WIP_OPERATIONS WO
set wo.previous_operation_seq_num =
(select max(operation_seq_num)
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num < wo.operation_seq_num),
wo.next_operation_seq_num =
(select min(operation_seq_num)
from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_seq_num > wo.operation_seq_num),
wo.quantity_in_queue = decode(operation_seq_num, max_op_seq_num,
(decode(p_status_type, 3,
ROUND(p_start_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION), 0)), 0)
where wo.wip_entity_id = p_wip_entity_id;
fnd_file.put_line(fnd_file.log,'update wo('||l_stmt_num||'): '|| SQL%ROWCOUNT);
update WIP_OPERATIONS
--Bug 5207481:Actual quantity changes for bonus:costed_quantity_completed
-- is updated instead of quantity_completed.
set wsm_costed_quantity_completed = p_start_quantity
--Bug 5510126:Scheduled quantity is getting populated in insert_procedure
--for old operations also during move.
,scheduled_quantity = 0
where wip_entity_id = p_wip_entity_id
and operation_seq_num < abb_op_seq_num;
fnd_file.put_line(fnd_file.log,'update quantity_cmplted in wo('||l_stmt_num||'): '|| SQL%ROWCOUNT);
/* select include_component_yield
into l_include_comp_yld
from wip_parameters
where organization_id = p_org;*/
update wip_requirement_operations
--set quantity_issued = required_quantity
set quantity_issued = 0
,required_quantity = 0
where wip_entity_id = p_wip_entity_id
and operation_seq_num < abb_op_seq_num;
update wip_requirement_operations
set quantity_issued = round(quantity_per_assembly * decode(nvl(basis_type,1), 2, 1, p_start_quantity), 6)
where wip_entity_id = p_wip_entity_id
and operation_seq_num < abb_op_seq_num
and wip_supply_type not in (2, 4, 5, 6); -- Fix for bug #2685463
fnd_file.put_line(fnd_file.log,'update quantity_issued in wro('||l_stmt_num||'): '|| SQL%ROWCOUNT);
select wac.material_account,
wac.material_overhead_account,
wac.resource_account,
wac.outside_processing_account,
wac.material_variance_account,
wac.resource_variance_account,
wac.outside_proc_variance_account,
wac.std_cost_adjustment_account,
wac.overhead_account,
wac.overhead_variance_account,
params.po_creation_time,
wac.est_scrap_account,
wac.est_scrap_var_account
into l_material_account,
l_material_overhead_account,
l_resource_account,
l_outside_processing_account,
l_material_variance_account,
l_resource_variance_account,
l_outside_proc_var_acc,
l_std_cost_adjustment_account,
l_overhead_account,
l_overhead_variance_account,
l_po_creation_time,
l_est_scrap_account,
l_est_scrap_var_account
from wip_accounting_classes wac,
wip_parameters params
where wac.class_code(+)= p_class_code
and wac.organization_id(+)= p_org
and params.organization_id = p_org;
INSERT INTO WIP_DISCRETE_JOBS (
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
source_line_id,
source_code,
description,
status_type,
date_released,
primary_item_id,
bom_reference_id,
routing_reference_id,
firm_planned_flag,
job_type,
wip_supply_type,
class_code,
material_account,
material_overhead_account,
resource_account,
outside_processing_account,
material_variance_account,
resource_variance_account,
outside_proc_variance_account,
std_cost_adjustment_account,
overhead_account,
overhead_variance_account,
scheduled_start_date,
scheduled_completion_date,
start_quantity,
quantity_completed,
quantity_scrapped,
net_quantity,
common_bom_sequence_id,
common_routing_sequence_id,
bom_revision,
routing_revision,
bom_revision_date,
routing_revision_date,
lot_number,
alternate_bom_designator,
alternate_routing_designator,
completion_subinventory,
completion_locator_id,
demand_class,
project_id,
task_id,
schedule_group_id,
build_sequence,
line_id,
kanban_card_id,
overcompletion_tolerance_type,
overcompletion_tolerance_value,
end_item_unit_number,
po_creation_time,
priority,
due_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
est_scrap_account,
est_scrap_var_account,
coproducts_supply
)
VALUES
(
p_wip_entity_id,
p_org,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_application_id,
p_program_id,
p_prog_updt_date,
p_source_line_id,
p_source_code,
p_description,
p_status_type,
p_date_released,
-- decode(p_status_type, WIP_CONSTANTS.UNRELEASED, NULL, SYSDATE), --Removed TRUNC for HH24MISS
p_item,
decode(p_job_type, 3,p_bom_reference_id, null),
decode(p_job_type, 3, p_routing_reference_id, null),
p_firm_planned_flag,
decode(p_job_type, 3, WIP_CONSTANTS.NONSTANDARD, WIP_CONSTANTS.STANDARD),
p_wip_supply_type,
p_class_code,
l_material_account,
l_material_overhead_account,
l_resource_account,
l_outside_processing_account,
l_material_variance_account,
l_resource_variance_account,
l_outside_proc_var_acc,
l_std_cost_adjustment_account,
l_overhead_account,
l_overhead_variance_account,
TRUNC(p_fusd,'MI'), --round(p_fusd,'MI'),
TRUNC(p_lucd,'MI'), --round(p_lucd,'MI'),
ROUND(p_start_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
0,
0,
ROUND(p_net_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
p_common_bill_sequence_id,
l_common_routing_sequence_id,
p_bom_revision,
p_routing_revision,
p_bom_revision_date, -- HH24MISS -- Removed rounding to 'MI'
p_routing_revision_date, -- HH24MISS -- Removed rounding to 'MI'
p_lot_number,
p_alt_bom_designator,
p_alt_routing_designator,
p_completion_subinventory,
p_completion_locator_id,
p_demand_class,
p_project_id,
p_task_id,
p_schedule_group_id,
p_build_sequence,
p_line_id,
p_kanban_card_id,
p_overcompl_tol_type,
p_overcompl_tol_value,
p_end_item_unit_number,
l_po_creation_time,
p_priority,
p_due_date,
p_attribute_category,
p_attribute1,
p_attribute2,
p_attribute3,
p_attribute4,
p_attribute5,
p_attribute6,
p_attribute7,
p_attribute8,
p_attribute9,
p_attribute10,
p_attribute11,
p_attribute12,
p_attribute13,
p_attribute14,
p_attribute15,
l_est_scrap_account,
l_est_scrap_var_account,
p_coproducts_supply
);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into wdj');
INSERT INTO WIP_ENTITIES (
wip_entity_id,
organization_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
wip_entity_name,
entity_type,
description,
primary_item_id,
gen_object_id
)
values
(
p_wip_entity_id,
p_org,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_application_id,
p_program_id,
p_prog_updt_date,
p_job_name,
5,
p_description,
p_item,
MTL_GEN_OBJECT_ID_S.nextval
);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into we');
inv_kanban_pvt.update_card_supply_status(
x_return_status => l_returnStatus,
p_kanban_card_id => p_kanban_card_id,
p_supply_status => inv_kanban_pvt.g_supply_status_InProcess,
p_document_type => inv_kanban_pvt.G_Doc_type_lot_job,
p_document_header_id => p_wip_entity_id,
p_Document_detail_Id => null,
p_replenish_quantity => p_start_quantity);
select meaning
into translated_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 5
and upper(enabled_flag) = 'Y';
insert_into_period_balances (
p_wip_entity_id => p_wip_entity_id,
p_organization_id => p_org,
p_class_code => p_class_code,
p_release_date => p_date_released,
p_error_code => l_error_code,
p_err_msg => l_error_msg );
select
wsli.header_id,
wsli.lot_number,
wsli.inventory_item_id,
wsli.organization_id,
wsli.quantity,
wsli.subinventory_code,
wsli.locator_id,
wsli.revision,
wsli.last_updated_by,
wsli.created_by,
msi.primary_uom_code,
wsli.component_issue_quantity
from wsm_starting_lots_interface wsli,
wsm_lot_job_interface wlji,
mtl_system_items msi
where wsli.header_id = wlji.source_line_id
and wlji.group_id = l_group_id
and wlji.process_status = 2 -- WIP_CONSTANTS.running
and wlji.mode_flag = 2
and msi.inventory_item_id = wsli.inventory_item_id
and msi.organization_id = wsli.organization_id;
v_wsli_last_updated_by,
v_wsli_created_by,
v_wsli_primary_uom_code,
v_wsli_comp_issue_qty;
v_wsli(v_wsli_header_id(v_wsli_index)).last_updated_by := v_wsli_last_updated_by(v_wsli_index);
select mmtt.transaction_temp_id,
mmtt.error_code,
mmtt.error_explanation,
wsli.header_id,
wlji.header_id
from mtl_material_transactions_temp mmtt,
wsm_starting_lots_interface wsli,
wsm_lot_job_interface wlji
where mmtt.transaction_header_id = p_header_id
and mmtt.source_line_id = wsli.header_id
and wsli.header_id = wlji.source_line_id ;
update wsm_lot_job_interface wljia
set wljia.process_status = 4,
wljia.error_code = -2,
wljia.error_msg = substr(l_err_explanation,1,240)
where wljia.header_id = l_wlji_header_id ;
PROCEDURE insert_procedure(
p_seq_id IN NUMBER,
p_job_seq_num IN NUMBER,
p_common_routing_sequence_id IN NUMBER, -- routing of the assembly
p_supply_type IN NUMBER,
p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_quantity IN NUMBER,
p_job_type IN NUMBER,
p_bom_reference_id IN NUMBER,
p_rtg_reference_id IN NUMBER,
p_assembly_item_id IN NUMBER,
p_alt_bom_designator IN VARCHAR2,
p_alt_rtg_designator IN VARCHAR2,
p_fusd IN DATE,
p_lucd IN DATE,
p_rtg_revision_date IN DATE,
p_bom_revision_date IN DATE,
p_last_updt_date IN date,
p_last_updt_by IN number,
p_creation_date IN date,
p_created_by IN number,
p_last_updt_login IN number,
p_request_id IN number,
p_program_application_id IN number,
p_program_id IN number,
p_prog_updt_date IN date,
p_error_code OUT NOCOPY NUMBER,
p_error_msg OUT NOCOPY VARCHAR2) IS
l_start_date VARCHAR2(50);
select nvl(OP_SEQ_NUM_INCREMENT, 10)
into l_op_seq_incr
from wsm_parameters
where ORGANIZATION_ID = p_organization_id;
select yield, to_char(operation_yield_enabled), department_id
into l_yield, l_operation_yield_enabled,l_department_id
from bom_operation_sequences
where operation_sequence_id = p_seq_id;
select scrap_account, est_absorption_account
into l_scrap_account, l_est_scrap_abs_account
from bom_departments
where department_id = l_department_id;
INSERT INTO WIP_OPERATIONS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
OPERATION_SEQUENCE_ID,
STANDARD_OPERATION_ID,
DEPARTMENT_ID,
DESCRIPTION,
SCHEDULED_QUANTITY,
QUANTITY_IN_QUEUE,
QUANTITY_RUNNING,
QUANTITY_WAITING_TO_MOVE,
QUANTITY_REJECTED,
QUANTITY_SCRAPPED,
QUANTITY_COMPLETED,
FIRST_UNIT_START_DATE,
FIRST_UNIT_COMPLETION_DATE,
LAST_UNIT_START_DATE,
LAST_UNIT_COMPLETION_DATE,
PREVIOUS_OPERATION_SEQ_NUM,
NEXT_OPERATION_SEQ_NUM,
COUNT_POINT_TYPE,
BACKFLUSH_FLAG,
MINIMUM_TRANSFER_QUANTITY,
DATE_LAST_MOVED,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
OPERATION_YIELD,
OPERATION_YIELD_ENABLED)
SELECT p_wip_entity_id,
p_job_seq_num,
p_organization_id,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_application_id,
p_program_id,
p_prog_updt_date,
SEQ.OPERATION_SEQUENCE_ID,
SEQ.STANDARD_OPERATION_ID,
SEQ.DEPARTMENT_ID,
SEQ.OPERATION_DESCRIPTION,
--Bug 5207481:Actual quantity changes-Scheduled_quantity should be zero.
--Bug 5510126:Scheduled quantity was made as 0 earlier as infinite
--is going to finally update this.But before infinite scheduler is
--called, create_requsitions is being called which uses the value
--in the field scheduled quantity.Hence the previous change is reverted.
ROUND(p_quantity, WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
--0,
0, 0, 0, 0, 0, 0,
TO_DATE(l_start_date, WIP_CONSTANTS.DT_NOSEC_FMT),
TO_DATE(l_completion_date, WIP_CONSTANTS.DT_NOSEC_FMT),
TO_DATE(l_start_date, WIP_CONSTANTS.DT_NOSEC_FMT),
TO_DATE(l_completion_date, WIP_CONSTANTS.DT_NOSEC_FMT),
0,
0,
SEQ.COUNT_POINT_TYPE,
SEQ.BACKFLUSH_FLAG,
NVL(SEQ.MINIMUM_TRANSFER_QUANTITY, 0),
'',
SEQ.ATTRIBUTE_CATEGORY,
SEQ.ATTRIBUTE1,
SEQ.ATTRIBUTE2,
SEQ.ATTRIBUTE3,
SEQ.ATTRIBUTE4,
SEQ.ATTRIBUTE5,
SEQ.ATTRIBUTE6,
SEQ.ATTRIBUTE7,
SEQ.ATTRIBUTE8,
SEQ.ATTRIBUTE9,
SEQ.ATTRIBUTE10,
SEQ.ATTRIBUTE11,
SEQ.ATTRIBUTE12,
SEQ.ATTRIBUTE13,
SEQ.ATTRIBUTE14,
SEQ.ATTRIBUTE15,
l_yield,
l_operation_yield_enabled
FROM BOM_OPERATION_SEQUENCES SEQ
WHERE SEQ.ROUTING_SEQUENCE_ID = p_common_routing_sequence_id
AND NVL(SEQ.OPERATION_TYPE, 1) = 1
--BC: CZH.I_OED-1
/****************
AND TO_DATE(TO_CHAR(SEQ.EFFECTIVITY_DATE, WIP_CONSTANTS.DT_NOSEC_FMT),
WIP_CONSTANTS.DT_NOSEC_FMT) <=
TO_DATE(l_routing_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT)
AND NVL(TO_DATE(TO_CHAR(SEQ.DISABLE_DATE, WIP_CONSTANTS.DT_NOSEC_FMT),
WIP_CONSTANTS.DT_NOSEC_FMT),
TO_DATE(l_routing_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT) + 1) >=
TRUNC(TO_DATE(l_routing_rev_date,WIP_CONSTANTS.DT_NOSEC_FMT))
****************/
/** HH24MISSS - Timestamp FPI changes - DATETIME_FMT **/
-- BC: BUG2754825
--AND TO_DATE(TO_CHAR(SEQ.EFFECTIVITY_DATE, WIP_CONSTANTS.DATETIME_FMT), WIP_CONSTANTS.DATETIME_FMT)
-- <= TO_DATE(l_routing_rev_date, WIP_CONSTANTS.DATETIME_FMT)
--AND NVL(TO_DATE(TO_CHAR(SEQ.DISABLE_DATE, WIP_CONSTANTS.DATETIME_FMT), WIP_CONSTANTS.DATETIME_FMT),
-- TO_DATE(l_routing_rev_date, WIP_CONSTANTS.DATETIME_FMT) + 1)
-- >= TO_DATE(l_routing_rev_date, WIP_CONSTANTS.DATETIME_FMT)
AND l_routing_rev_date BETWEEN SEQ.EFFECTIVITY_DATE AND NVL(SEQ.DISABLE_DATE, l_routing_rev_date+1)
-- EC: BUG2754825
--EC: CZH.I_OED-1
AND OPERATION_SEQUENCE_ID = p_seq_id
AND NOT EXISTS (select 'x' from wip_operations
where wip_entity_id = p_wip_entity_id
and operation_sequence_id = p_seq_id
and operation_seq_num = p_job_seq_num);
select p_job_seq_num
into l_seq_incr
from sys.dual;
X_LAST_UPDATE_LOGIN => to_char(p_last_updt_login),
X_PROGRAM_APPLICATION_ID => to_char(p_program_application_id),
X_PROGRAM_ID => to_char(p_program_id),
X_REQUEST_ID => to_char(p_request_id)) ;
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into wo');
INSERT INTO WIP_OPERATION_YIELDS
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
STATUS,
SCRAP_ACCOUNT,
EST_SCRAP_ABSORB_ACCOUNT)
SELECT p_wip_entity_id,
WO.OPERATION_SEQ_NUM,
p_organization_id,
p_last_updt_date,
p_last_updt_by,
p_creation_date,
p_created_by,
p_last_updt_login,
p_request_id,
p_program_application_id,
p_program_id,
p_prog_updt_date,
NULL,
l_scrap_account,
l_est_scrap_abs_account
FROM WIP_OPERATIONS WO
WHERE WO.WIP_ENTITY_ID = p_wip_entity_id
AND WO.OPERATION_SEQUENCE_ID = p_seq_id
AND WO.OPERATION_SEQ_NUM NOT IN (SELECT WOY.OPERATION_SEQ_NUM
FROM WIP_OPERATION_YIELDS WOY
WHERE WOY.WIP_ENTITY_ID = p_wip_entity_id);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into woy');
INSERT INTO WIP_OPERATION_RESOURCES
(WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_SEQ_NUM,
ORGANIZATION_ID,
LAST_UPDATE_DATE, LAST_UPDATED_BY, CREATION_DATE,
CREATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID,
PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE,
RESOURCE_ID, UOM_CODE,
BASIS_TYPE, USAGE_RATE_OR_AMOUNT, ACTIVITY_ID,
SCHEDULED_FLAG, ASSIGNED_UNITS, AUTOCHARGE_TYPE,
STANDARD_RATE_FLAG, APPLIED_RESOURCE_UNITS, APPLIED_RESOURCE_VALUE,
START_DATE, COMPLETION_DATE,
ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8,
ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14,
ATTRIBUTE15,
SCHEDULE_SEQ_NUM,SUBSTITUTE_GROUP_NUM,PRINCIPLE_FLAG,SETUP_ID,
-- ST : Detailed Scheduling --
maximum_assigned_units,
firm_flag
-- ST : Detailed Scheduling --
)
SELECT OPS.WIP_ENTITY_ID,
OPS.OPERATION_SEQ_NUM,
ORS.RESOURCE_SEQ_NUM,
OPS.ORGANIZATION_ID,
OPS.LAST_UPDATE_DATE, OPS.LAST_UPDATED_BY, OPS.CREATION_DATE,
OPS.CREATED_BY, OPS.LAST_UPDATE_LOGIN, OPS.REQUEST_ID,
OPS.PROGRAM_APPLICATION_ID, OPS.PROGRAM_ID,
OPS.PROGRAM_UPDATE_DATE, ORS.RESOURCE_ID, RSC.UNIT_OF_MEASURE,
ORS.BASIS_TYPE, ORS.USAGE_RATE_OR_AMOUNT, ORS.ACTIVITY_ID,
ORS.SCHEDULE_FLAG, ORS.ASSIGNED_UNITS, ORS.AUTOCHARGE_TYPE,
ORS.STANDARD_RATE_FLAG, 0, 0,
OPS.FIRST_UNIT_START_DATE, OPS.LAST_UNIT_COMPLETION_DATE,
ORS.ATTRIBUTE_CATEGORY, ORS.ATTRIBUTE1, ORS.ATTRIBUTE2,
ORS.ATTRIBUTE3, ORS.ATTRIBUTE4, ORS.ATTRIBUTE5,
ORS.ATTRIBUTE6, ORS.ATTRIBUTE7, ORS.ATTRIBUTE8,
ORS.ATTRIBUTE9, ORS.ATTRIBUTE10, ORS.ATTRIBUTE11,
ORS.ATTRIBUTE12, ORS.ATTRIBUTE13, ORS.ATTRIBUTE14,
ORS.ATTRIBUTE15,
ORS.SCHEDULE_SEQ_NUM,ORS.SUBSTITUTE_GROUP_NUM,ORS.PRINCIPLE_FLAG,ORS.SETUP_ID,
-- ST : Detailed Scheduling --
ORS.ASSIGNED_UNITS,
0
-- ST : Detailed Scheduling --
FROM BOM_RESOURCES RSC,
BOM_OPERATION_RESOURCES ORS,
WIP_OPERATIONS OPS
WHERE OPS.ORGANIZATION_ID = p_organization_id
AND OPS.WIP_ENTITY_ID = p_wip_entity_id
AND OPS.OPERATION_SEQUENCE_ID = ORS.OPERATION_SEQUENCE_ID
AND ORS.RESOURCE_ID = RSC.RESOURCE_ID
AND RSC.ORGANIZATION_ID = OPS.ORGANIZATION_ID
AND ORS.OPERATION_SEQUENCE_ID = p_seq_id
AND OPS.OPERATION_SEQ_NUM NOT IN (select WOR.OPERATION_SEQ_NUM
from Wip_operation_resources WOR
where WOR.wip_entity_id = p_wip_entity_id);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into wor');
SELECT common_routing_sequence_id INTO l_routing_seq_id
FROM BOM_OPERATIONAL_ROUTINGS BOR
WHERE BOR.assembly_item_id= decode(p_job_type,1,p_assembly_item_id,p_rtg_reference_id)
AND nvl(BOR.alternate_routing_designator, '***') = nvl(p_alt_rtg_designator, '***')
and bor.organization_id = p_organization_id;
fnd_file.put_line(fnd_file.log, 'Inserted rows into wro');
p_error_msg := 'WSMPLBJI.insert_procedure('||l_stat_num||'): No Data Found';
p_error_msg := 'WSMPLBJI.insert_procedure('||l_stat_num||'): '||p_error_msg;
p_error_msg := 'WSMPLBJI.insert_procedure('||l_stat_num||')'|| substr(SQLERRM,1,200);
END insert_procedure;
SELECT DISTINCT 'X'
FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
AND DJ.WIP_ENTITY_ID = p_wip_entity_id
AND DJ.ORGANIZATION_ID = p_organization_id
AND (DJ.QUANTITY_COMPLETED <> 0
OR DJ.QUANTITY_SCRAPPED <> 0
OR WPB.TL_RESOURCE_IN <> 0
OR WPB.TL_OVERHEAD_IN <> 0
OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.PL_MATERIAL_IN <> 0
OR WPB.PL_MATERIAL_OVERHEAD_IN <> 0
OR WPB.PL_RESOURCE_IN <> 0
OR WPB.PL_OVERHEAD_IN <> 0
OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.TL_MATERIAL_OUT <> 0
OR WPB.TL_RESOURCE_OUT <> 0
OR WPB.TL_OVERHEAD_OUT <> 0
OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
OR WPB.PL_MATERIAL_OUT <> 0
OR WPB.PL_MATERIAL_OVERHEAD_OUT <> 0
OR WPB.PL_RESOURCE_OUT <> 0
OR WPB.PL_OVERHEAD_OUT <> 0
OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0
OR EXISTS (SELECT 'X'
FROM WIP_REQUIREMENT_OPERATIONS
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id
AND QUANTITY_ISSUED <> 0)
OR EXISTS (SELECT 'X'
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WSM_LOT_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM MTL_MATERIAL_TRANSACTIONS_TEMP
WHERE ORGANIZATION_ID = p_organization_id
AND TRANSACTION_SOURCE_TYPE_ID = 5
AND TRANSACTION_SOURCE_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WIP_MOVE_TRANSACTIONS
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
-- CZH check WLT also
OR EXISTS (SELECT 'X'
FROM WSM_SM_RESULTING_JOBS
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id
AND APPLIED_RESOURCE_UNITS <> 0));
SELECT DISTINCT 'X'
FROM WIP_DISCRETE_JOBS DJ, WIP_PERIOD_BALANCES WPB
WHERE DJ.WIP_ENTITY_ID = WPB.WIP_ENTITY_ID
AND DJ.ORGANIZATION_ID = WPB.ORGANIZATION_ID
AND DJ.WIP_ENTITY_ID = p_wip_entity_id
AND DJ.ORGANIZATION_ID = p_organization_id
AND (DJ.QUANTITY_COMPLETED <> 0
OR DJ.QUANTITY_SCRAPPED <> 0
OR WPB.TL_RESOURCE_IN <> 0
OR WPB.TL_OVERHEAD_IN <> 0
OR WPB.TL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.PL_RESOURCE_IN <> 0
OR WPB.PL_OVERHEAD_IN <> 0
OR WPB.PL_OUTSIDE_PROCESSING_IN <> 0
OR WPB.TL_RESOURCE_OUT <> 0
OR WPB.TL_OVERHEAD_OUT <> 0
OR WPB.TL_OUTSIDE_PROCESSING_OUT <> 0
OR WPB.PL_RESOURCE_OUT <> 0
OR WPB.PL_OVERHEAD_OUT <> 0
OR WPB.PL_OUTSIDE_PROCESSING_OUT <> 0
OR EXISTS (SELECT 'X'
FROM WIP_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WSM_LOT_MOVE_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WIP_COST_TXN_INTERFACE
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WIP_MOVE_TRANSACTIONS
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
-- CZH check WLT also
OR EXISTS (SELECT 'X'
FROM WSM_SM_RESULTING_JOBS
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id)
OR EXISTS (SELECT 'X'
FROM WIP_OPERATION_RESOURCES
WHERE ORGANIZATION_ID = p_organization_id
AND WIP_ENTITY_ID = p_wip_entity_id
AND APPLIED_RESOURCE_UNITS <> 0));
PROCEDURE insert_into_period_balances(
p_wip_entity_id IN NUMBER,
p_organization_id IN NUMBER,
p_class_code IN VARCHAR2,
p_release_date IN DATE,
p_error_code OUT NOCOPY NUMBER,
p_err_msg OUT NOCOPY VARCHAR2 ) IS
x_user_id NUMBER := FND_GLOBAL.USER_ID;
insert into wip_period_balances (
acct_period_id,
wip_entity_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
organization_id,
class_type,
tl_resource_in,
tl_overhead_in,
tl_outside_processing_in,
pl_material_in,
pl_material_overhead_in,
pl_resource_in,
pl_overhead_in,
pl_outside_processing_in,
tl_material_out,
tl_resource_out,
tl_overhead_out,
tl_outside_processing_out,
pl_material_out,
pl_material_overhead_out,
pl_resource_out,
pl_overhead_out,
pl_outside_processing_out,
pl_material_overhead_var,
pl_material_var,
pl_outside_processing_var,
pl_overhead_var,
pl_resource_var,
tl_material_var,
tl_outside_processing_var,
tl_overhead_var,
tl_resource_var,
tl_material_overhead_out,
tl_material_overhead_var)
select oap.acct_period_id,
p_wip_entity_id,
sysdate, x_user_id,
sysdate, x_user_id, x_login_id,
p_organization_id, wc.class_type,
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0, 0, 0, 0, 0
from org_acct_periods oap,
wip_accounting_classes wc
where wc.class_code = p_class_code
and wc.organization_id = p_organization_id
and oap.organization_id = p_organization_id
and oap.schedule_close_date >=
trunc(inv_le_timezone_pub.get_le_day_for_inv_org(
nvl(p_release_date, sysdate),
p_organization_id))
and oap.period_close_date is null
and not exists (
select 'balance record already there'
from wip_period_balances wpb
where wpb.wip_entity_id = p_wip_entity_id
and wpb.acct_period_id = oap.acct_period_id
and wpb.organization_id = oap.organization_id);
fnd_file.put_line(fnd_file.log, 'Inserted '|| l_cnt ||' rows into wip_period_balances');
p_err_msg := 'WSMPLBJI.insert_into_period_balances: '|| substr(SQLERRM,1,200);
end insert_into_period_balances;
select honor_kanban_size
into l_hon_kanban_size
from wsm_parameters
where organization_id = p_org_id;
select header_id
from wsm_lot_job_interface
where process_status = wip_constants.pending
-- and group_id is null
and creation_date <= sysdate+1
and load_type in (5,6)
order by job_name, organization_id; -- CZH: this will group the job together
select count(*)
into total_no_rows
from wsm_lot_job_interface
where process_status = wip_constants.pending
--and group_id is null
and creation_date <= sysdate+1
and load_type in (5,6);
select wsm_lot_job_interface_s.NEXTVAL
into l_group_id
from dual;
update wsm_lot_job_interface wlji
set wlji.group_id = l_group_id,
wlji.process_status = wip_constants.running
where header_id = x_header;
select wsm_lot_job_interface_s.NEXTVAL
into l_group_id
from dual;
select count(*)
into total_no_rows
from wsm_lot_job_interface
where process_status = wip_constants.pending
and group_id = p_group_id
and creation_date <= sysdate+1
and load_type in (5,6);
update wsm_lot_job_interface wlji
set wlji.process_status = wip_constants.running
where group_id = p_group_id;
select
error_code,
error_msg,
last_update_date,
request_id,
program_id,
program_application_id,
last_updated_by,
creation_date,
created_by,
last_update_login,
program_update_date,
last_updated_by_name,
created_by_name,
organization_id,
primary_item_id,
header_id,
process_status,
routing_reference_id,
completion_subinventory,
completion_locator_id,
mode_flag,
group_id,
load_type,
status_type,
last_unit_completion_date,
old_completion_date,
bom_reference_id,
bom_revision_date,
routing_revision_date,
wip_supply_type,
class_code,
lot_number,
job_name,
description,
firm_planned_flag,
alternate_routing_designator,
alternate_bom_designator,
demand_class,
start_quantity,
old_start_quantity,
wip_entity_id,
error,
process_phase,
first_unit_start_date,
first_unit_completion_date,
last_unit_start_date,
scheduling_method,
routing_revision,
bom_revision,
schedule_group_id,
schedule_group_name,
build_sequence,
net_quantity,
allow_explosion,
old_status_type,
interface_id,
coproducts_supply,
source_code,
source_line_id,
process_type,
processing_work_days,
daily_production_rate,
line_id,
lot_control_code,
repetitive_schedule_id,
parent_group_id,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
organization_code,
line_code,
primary_item_segments,
bom_reference_segments,
routing_reference_segments,
completion_locator_segments,
project_id,
project_name,
task_id,
delivery_id,
descriptive_flex_segments,
project_number,
task_number,
project_costed,
end_item_unit_number,
overcompletion_tolerance_type,
overcompletion_tolerance_value,
kanban_card_id,
priority,
due_date,
task_name,
job_type,
date_released --bugfix 2697295
from wsm_lot_job_interface
where group_id = l_group_id
and process_status = 2 -- WIP_CONSTANTS.running;
l_last_update_date DATE := SYSDATE;
update_job_exception EXCEPTION;
select mtl_material_transactions_s.nextval
into txn_header_id
from dual;
select wsm_lot_job_interface_s.NEXTVAL
into batch_group_id
from dual;
update wsm_lot_job_interface
set group_id = batch_group_id
where header_id = v_wlji_header_id(indx);
v_wlji_last_updt_date(v_index) := l_last_update_date;
select 1
into l_no_of_records
from wsm_starting_lots_interface wsli,
wsm_lot_split_merges_interface wlsmi
where wsli.lot_number = v_wlji_job_name(v_index)
and wsli.header_id = wlsmi.header_id
and wlsmi.process_status in (1,2);
select 1
into l_no_of_records
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wlji_item(v_index)
and msi.organization_id = v_wlji_org(v_index)
and msi.lot_control_code = 2;
select 1
into l_no_of_records
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wlji_item(v_index)
and msi.organization_id = v_wlji_org(v_index)
and msi.serial_number_control_code = 1;
select bor.routing_sequence_id,
bor.COMPLETION_SUBINVENTORY,
bor.COMPLETION_LOCATOR_ID
into routing_seq_id,
l_default_subinventory,
l_default_compl_loc_id
--from bom_routing_alternates_v bor
from bom_operational_routings bor,
bom_alternate_designators bad
where ((bor.alternate_routing_designator is null and
bad.alternate_designator_code is null
and bad.organization_id = -1)
or (bor.alternate_routing_designator = bad.alternate_designator_code
and bor.organization_id = bad.organization_id))
and bor.organization_id = v_wlji_org(v_index)
and bor.assembly_item_id = v_wlji_item(v_index)
and NVL(bor.alternate_routing_designator, '&*') = NVL(v_wlji_alt_routing_designator(v_index), '&*')
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
select locator_type
into mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name = v_wlji_completion_subinventory(v_index)
and organization_id = v_wlji_org(v_index);
SELECT bom.common_bill_sequence_id
INTO bom_seq_id
FROM bom_bill_of_materials bom
WHERE bom.alternate_bom_designator is NULL
AND BOM.assembly_item_id = v_wlji_item(v_index)
AND bom.organization_id = v_wlji_org(v_index);
SELECT bom.common_bill_sequence_id
INTO bom_seq_id
FROM bom_bill_of_materials bom,
bom_alternate_designators bad
WHERE ((bom.alternate_bom_designator is null and
bad.alternate_designator_code is null
and bad.organization_id = -1)
OR (bom.alternate_bom_designator = bad.alternate_designator_code
and bom.organization_id = bad.organization_id))
AND bom.alternate_bom_designator = v_wlji_alt_bom_designator(v_index)
AND BOM.assembly_item_id = v_wlji_item(v_index)
AND bom.organization_id = v_wlji_org(v_index);
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 = v_wlji_org(v_index)
AND MS.organization_id = v_wlji_org(v_index)
AND MS.inventory_item_id = v_wlji_item(v_index)
AND MSUB.secondary_inventory_name = v_wlji_completion_subinventory(v_index)
AND MSUB.organization_id = v_wlji_org(v_index);
select 1
into l_no_of_records
from fnd_user
where user_id = v_wlji_last_updt_by(v_index)
and sysdate between start_date and nvl(end_date,sysdate+1);
process_invalid_field( 'Last Updated By',
l_aux_mesg,
l_stmt_num);
select 1
into l_no_of_records
from fnd_user
where user_id = v_wlji_created_by(v_index)
and sysdate between start_date and nvl(end_date,sysdate+1);
select FND_Profile.value('WIP_JOB_PREFIX') || wip_job_number_s.nextval
into v_wlji_job_name(v_index)
from dual ;
select 1 into l_dummy
from wip_entities
where
wip_entity_name = v_wlji_job_name(v_index) and
organization_id = v_wlji_org(v_index);
select wip_entity_id
into v_wlji_wip_entity_id(v_index)
from wip_entities
where
wip_entity_name = v_wlji_job_name(v_index) and
organization_id = v_wlji_org(v_index) ;
select 1 into l_dummy from dual where exists (
select 1
from wip_discrete_jobs
where
wip_entity_id = v_wlji_wip_entity_id(v_index) and
organization_id = v_wlji_org(v_index) and
status_type in (
WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.HOLD,
WIP_CONSTANTS.CANCELLED)
);
select 1 into l_dummy
from wip_discrete_jobs
where
wip_entity_id = v_wlji_wip_entity_id(v_index) and
status_type in (
WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.HOLD,
WIP_CONSTANTS.CANCELLED) ;
select wse.DEFAULT_ACCT_CLASS_CODE
into v_wlji_class_code(v_index)
from wsm_sector_extensions wse, wsm_item_extensions wie
where wie.INVENTORY_ITEM_ID = v_wlji_item(v_index)
and wie.ORGANIZATION_ID = v_wlji_org(v_index)
and wie.SECTOR_EXTENSION_ID = wse.SECTOR_EXTENSION_ID
and wie.ORGANIZATION_ID = wse.ORGANIZATION_ID;
select wse.DEFAULT_ACCT_CLASS_CODE
into v_wlji_class_code(v_index)
from wsm_sector_extensions wse, wsm_subinventory_extensions wve
where wve.SECONDARY_INVENTORY_NAME = v_wlji_completion_subinventory(v_index)
and wve.ORGANIZATION_ID = v_wlji_org(v_index)
and wve.SECTOR_EXTENSION_ID = wse.SECTOR_EXTENSION_ID
and wve.ORGANIZATION_ID = wse.ORGANIZATION_ID;
select SERIAL_NUMBER_CONTROL_CODE,
revision_qty_control_code -- Add bug 2963225
into l_dummy,
l_rev_control_code -- Add bug 2963225
from mtl_system_items_kfv msi
where msi.inventory_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
and msi.organization_id = v_wlji_org(v_index);
xst := WSMPLCVA.v_user.exists(v_wsli(v_wlji_source_line_id(v_index)).last_updated_by);
select 1
into l_no_of_records
from fnd_user
where user_id = v_wsli(v_wlji_source_line_id(v_index)).last_updated_by
and sysdate between start_date and nvl(end_date,sysdate+1);
process_invalid_field( 'Last Updated By in WSM_STARTING_LOTS_INTERFACE',
l_aux_mesg,
l_stmt_num);
WSMPLCVA.v_user(v_wsli(v_wlji_source_line_id(v_index)).last_updated_by)
:= v_wsli(v_wlji_source_line_id(v_index)).last_updated_by;
select 1
into l_no_of_records
from fnd_user
where user_id = v_wsli(v_wlji_source_line_id(v_index)).created_by
and sysdate between start_date and nvl(end_date,sysdate+1);
select 1 into l_dummy
from mtl_transaction_lots_temp
where lot_number = v_wsli(v_wlji_source_line_id(v_index)).lot_number
and rownum = 1;
SELECT 0 into l_dummy
FROM mtl_material_transactions_temp mmtt
WHERE mmtt.organization_id = v_wsli(v_wlji_source_line_id(v_index)).organization_id
and mmtt.inventory_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
and NVL(mmtt.lot_number, '@#$') = v_wsli(v_wlji_source_line_id(v_index)).lot_number
and mmtt.subinventory_code = v_wsli(v_wlji_source_line_id(v_index)).subinventory_code
and NVL(mmtt.locator_id, -9999) = NVL(v_wsli(v_wlji_source_line_id(v_index)).locator_id, -9999)
and mmtt.transaction_type_id = 42 -- Miscellaneous Receipt
and mmtt.transaction_action_id = 27 -- Receipt into stores
and mmtt.transaction_source_type_id = 13 -- Inventory
and v_wsli(v_wlji_source_line_id(v_index)).quantity = ((-1) * mmtt.transaction_quantity)
and mmtt.transaction_date = (
SELECT max(mmtt2.transaction_date)
FROM mtl_material_transactions_temp mmtt2
WHERE mmtt2.organization_id = v_wsli(v_wlji_source_line_id(v_index)).organization_id
and mmtt2.inventory_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
and NVL(mmtt2.lot_number, '@#$') = NVL(v_wsli(v_wlji_source_line_id(v_index)).lot_number, '@#$')
and mmtt2.subinventory_code = v_wsli(v_wlji_source_line_id(v_index)).subinventory_code
and NVL(mmtt2.locator_id, -9999) = NVL(v_wsli(v_wlji_source_line_id(v_index)).locator_id, -9999)
);
SELECT 1
INTO l_dummy
FROM BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS C
WHERE BIC.COMPONENT_ITEM_ID = C.INVENTORY_ITEM_ID
AND C.ORGANIZATION_ID = v_wlji_org(v_index)
AND BIC.BILL_SEQUENCE_ID = bom_seq_id
AND BIC.COMPONENT_ITEM_ID = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
AND (BIC.operation_seq_num = (SELECT BOS.operation_seq_num
FROM BOM_OPERATION_SEQUENCES BOS
WHERE operation_sequence_id = l_start_op_seq_id )
OR
BIC.operation_seq_num = 1)
-- BC: BUGFIX 2380517 (CZH.I_OED-2)
--AND EFFECTIVITY_DATE <= SYSDATE
--AND nvl(DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND l_bom_rev_date between BIC.EFFECTIVITY_DATE
and nvl(BIC.DISABLE_DATE, l_bom_rev_date + 1)
-- EC: BUGFIX 2380517
AND EFFECTIVITY_DATE =(
SELECT MAX (EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS BIC2,
ENG_REVISED_ITEMS ERI
WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND (decode(BIC2.IMPLEMENTATION_DATE,
NULL, BIC2.OLD_COMPONENT_SEQUENCE_ID,
BIC2.COMPONENT_SEQUENCE_ID) =
decode(BIC.IMPLEMENTATION_DATE,
NULL, BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID)
OR BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM)
--AND BIC2.EFFECTIVITY_DATE <= SYSDATE --BUGFIX 2380517
AND BIC2.EFFECTIVITY_DATE <= l_bom_rev_date --BUGFIX 2380517
AND BIC2.REVISED_ITEM_SEQUENCE_ID =
ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND ( NVL(ERI.STATUS_TYPE,6) IN (4,6,7))
AND NOT EXISTS (
SELECT 'X'
FROM BOM_INVENTORY_COMPONENTS BICN,
ENG_REVISED_ITEMS ERI1
WHERE BICN.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BICN.OLD_COMPONENT_SEQUENCE_ID =
BIC.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE in (2,3)
--AND BICN.DISABLE_DATE <= SYSDATE --BUGFIX 2380517
AND BICN.DISABLE_DATE <= l_bom_rev_date --BUGFIX 2380517
AND ERI1.REVISED_ITEM_SEQUENCE_ID = BICN.REVISED_ITEM_SEQUENCE_ID
AND ( nvl(ERI1.STATUS_TYPE,6) IN (4,6,7) )
)
);
select kanban_size
into l_kanban_size
from mtl_kanban_cards
where kanban_card_id = v_wlji_kanban_card_id(v_index);
SELECT component_quantity, component_yield_factor
INTO l_component_quantity, l_component_yield_factor
FROM wsm_components_v
WHERE assembly_item_id = v_wlji_item(v_index)
AND component_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
AND nvl(alternate_bom_designator,'NULL') = nvl(v_wlji_alt_bom_designator(v_index),'NULL')
AND organization_id = v_wlji_org(v_index);
SELECT bic.component_quantity,
decode(bic.component_yield_factor, 0, 1,
bic.component_yield_factor) component_yield_factor,
bic.basis_type -- LBM enh
INTO l_component_quantity,
l_component_yield_factor,
l_comp_basis_type -- LBM enh
from mtl_System_items msi,
bom_inventory_components bic,
bom_bill_of_materials bom,
bom_bill_of_materials bom2
WHERE bic.bill_sequence_id = bom2.bill_sequence_id
and bom.common_bill_sequence_id = bom2.bill_sequence_id
and msi.organization_id = bom.organization_id
and msi.inventory_item_id = bom.assembly_item_id
and msi.build_in_wip_flag = 'Y'
and msi.pick_components_flag = 'N'
and bic.implementation_date is not null
and bom.assembly_item_id = v_wlji_item(v_index)
and bic.component_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
and bom.organization_id = v_wlji_org(v_index)
and nvl(bom.alternate_bom_designator,'NULL') = nvl(v_wlji_alt_bom_designator(v_index),'NULL')
and l_bom_rev_date between bic.effectivity_date
and nvl(bic.disable_date, l_bom_rev_date + 1);
select revision
into l_start_lot_revision
from WSM_source_lots_v
where lot_number = v_wsli(v_wlji_source_line_id(v_index)).lot_number
and organization_id = v_wlji_org(v_index)
and inventory_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id;
SELECT 1
INTO l_dummy
FROM BOM_INVENTORY_COMPONENTS BIC,
MTL_SYSTEM_ITEMS C
WHERE BIC.COMPONENT_ITEM_ID = C.INVENTORY_ITEM_ID
AND C.ORGANIZATION_ID = v_wlji_org(v_index)
AND BIC.BILL_SEQUENCE_ID = bom_seq_id
AND BIC.COMPONENT_ITEM_ID = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id
AND (BIC.operation_seq_num = (SELECT BOS.operation_seq_num
FROM BOM_OPERATION_SEQUENCES BOS
WHERE operation_sequence_id = l_start_op_seq_id)
OR BIC.operation_seq_num = 1 )
AND NVL(BIC.wip_supply_type, 1) <> 6 -- CHG: BUG 2696937/2652076
-- BC: BUGFIX 2380517 (CZH.I_OED-2)
--AND EFFECTIVITY_DATE <= SYSDATE
--AND nvl(DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND l_bom_rev_date between BIC.EFFECTIVITY_DATE
and nvl(BIC.DISABLE_DATE, l_bom_rev_date + 1)
-- EC: BUGFIX 2380517
AND EFFECTIVITY_DATE = (
SELECT MAX(EFFECTIVITY_DATE)
FROM BOM_INVENTORY_COMPONENTS BIC2,
ENG_REVISED_ITEMS ERI
WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
AND (decode(BIC2.IMPLEMENTATION_DATE,
NULL, BIC2.OLD_COMPONENT_SEQUENCE_ID,
BIC2.COMPONENT_SEQUENCE_ID) =
decode(BIC.IMPLEMENTATION_DATE,
NULL, BIC.OLD_COMPONENT_SEQUENCE_ID,
BIC.COMPONENT_SEQUENCE_ID)
OR BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM)
--AND BIC2.EFFECTIVITY_DATE <= SYSDATE --BUGFIX 2380517
AND BIC2.EFFECTIVITY_DATE <= l_bom_rev_date --BUGFIX 2380517
AND BIC2.REVISED_ITEM_SEQUENCE_ID = ERI.REVISED_ITEM_SEQUENCE_ID(+)
AND (NVL(ERI.STATUS_TYPE,6) IN (4,6,7))
AND NOT EXISTS (
SELECT 'X'
FROM BOM_INVENTORY_COMPONENTS BICN,
ENG_REVISED_ITEMS ERI1
WHERE BICN.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
AND BICN.OLD_COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
AND BICN.ACD_TYPE in (2,3)
--AND BICN.DISABLE_DATE <= SYSDATE --BUGFIX 2380517
AND BICN.DISABLE_DATE <= l_bom_rev_date --BUGFIX 2380517
AND ERI1.REVISED_ITEM_SEQUENCE_ID = BICN.REVISED_ITEM_SEQUENCE_ID
AND ( NVL(ERI1.STATUS_TYPE,6) IN (4,6,7) )
)
);
select schedule_group_id
into v_wlji_schedule_group_id(v_index)
from wip_discrete_jobs
where wip_entity_id = v_wlji_wip_entity_id(v_index)
and organization_id = v_wlji_org(v_index);
insert into wip_schedule_groups (
schedule_group_id,
schedule_group_name,
organization_id,
description,
created_by,
last_updated_by,
creation_date,
last_update_date)
select wip_schedule_groups_s.nextval,
wds.name,
v_wlji_org(v_index),
to_char(sysdate),
l_user,
l_user,
sysdate,
sysdate
from wsh_new_deliveries wds
where wds.delivery_id = v_wlji_delivery_id(v_index)
and not exists (
select 1
from wip_schedule_groups wsg
where wsg.organization_id = v_wlji_org(v_index)
and WSG.schedule_group_name = WDS.name);
select wsg.schedule_group_name, wsg.schedule_group_id
into v_wlji_schedule_group_name(v_index), v_wlji_schedule_group_id(v_index)
from wip_schedule_groups wsg,
wsh_new_deliveries wds
where wds.delivery_id = v_wlji_delivery_id(v_index)
and wsg.schedule_group_name = wds.name
and wsg.organization_id = v_wlji_org(v_index);
select 1 into l_dummy
from wip_schedule_groups_val_v wsg
where wsg.schedule_group_id = v_wlji_schedule_group_id(v_index)
and wsg.organization_id = v_wlji_org(v_index);
select
nvl(v_wlji_build_sequence(v_index), WDJ.build_sequence),
nvl(v_wlji_line_id(v_index), WDJ.line_id),
nvl(v_wlji_schedule_group_id(v_index), WDJ.schedule_group_id)
into
l_build_sequence,
l_line_id,
l_schedule_group_id
from
wip_discrete_jobs WDJ
where
WDJ.wip_entity_id = v_wlji_wip_entity_id(v_index);
select primary_item_id,
class_code,
scheduled_start_date,
scheduled_completion_date,
start_quantity,
net_quantity,
status_type,
firm_planned_flag,
job_type,
bom_reference_id,
alternate_bom_designator,
routing_reference_id,
alternate_routing_designator,
bom_revision_date,
routing_revision_date,
bom_revision,
routing_revision,
common_routing_sequence_id,
common_bom_sequence_id,
wip_supply_type,
scheduled_start_date,
scheduled_completion_date,
coproducts_supply,
kanban_card_id,
completion_subinventory, -- bug 2762029
completion_locator_id, -- bug 2762029
date_released
into p_old_primary_item_id,
p_old_class_code,
p_old_start_date,
p_old_complete_date,
p_old_quantity,
p_old_net_quantity,
p_old_status_type,
p_old_firm_planned_flag,
p_old_job_type,
p_old_bom_reference_id,
p_old_alt_bom_designator,
p_old_routing_reference_id,
p_old_alt_routing_designator,
p_old_bom_revision_date,
p_old_routing_revision_date,
p_old_bom_revision, -- bug 2762029
p_old_routing_revision, -- bug 2762029
p_old_com_rtg_seq_id,
p_old_com_bom_seq_id,
p_old_supply_type,
p_scheduled_start_date,
p_scheduled_completion_date,
p_coproducts_supply,
v_wlji_kanban_card_id(v_index),
p_old_completion_subinv,
p_old_completion_locator,
p_old_date_released
from wip_discrete_jobs
where wip_entity_id = v_wlji_wip_entity_id(v_index)
and organization_id = v_wlji_org(v_index);
p_change_alt_flag := 1; --i.e. update of at least one alternate has happenned
delete from wip_operations where wip_entity_id = v_wlji_wip_entity_id(v_index);
delete from wip_operation_resources where wip_entity_id = v_wlji_wip_entity_id(v_index);
delete from wip_requirement_operations where wip_entity_id = v_wlji_wip_entity_id(v_index);
delete from wip_operation_yields where wip_entity_id = v_wlji_wip_entity_id(v_index);
select bor.routing_sequence_id,
bor.COMPLETION_SUBINVENTORY,
bor.COMPLETION_LOCATOR_ID
into p_common_routing_sequence_id,
l_default_subinventory,
l_default_compl_loc_id
from bom_operational_routings bor, bom_alternate_designators bad
where ((bor.alternate_routing_designator is null and bad.alternate_designator_code is null
and bad.organization_id = -1)
or (bor.alternate_routing_designator = bad.alternate_designator_code
and bor.organization_id = bad.organization_id))
and bor.organization_id = v_wlji_org(v_index)
and bor.assembly_item_id = v_wlji_item(v_index)
and NVL(bor.alternate_routing_designator, '&*') = NVL(v_wlji_alt_routing_designator(v_index), '&*')
and bor.routing_type = 1
and bor.cfm_routing_flag = 3;
select locator_type
into mtl_locator_type
from mtl_secondary_inventories
where secondary_inventory_name = v_wlji_completion_subinventory(v_index)
and organization_id = v_wlji_org(v_index);
SELECT bom.common_bill_sequence_id
INTO p_common_bill_sequence_id
FROM bom_bill_of_materials bom
WHERE bom.alternate_bom_designator is NULL
AND BOM.assembly_item_id = v_wlji_item(v_index)
AND bom.organization_id = v_wlji_org(v_index);
SELECT bom.common_bill_sequence_id
INTO p_common_bill_sequence_id
FROM bom_bill_of_materials bom, bom_alternate_designators bad
WHERE ((bom.alternate_bom_designator is null and bad.alternate_designator_code is null
and bad.organization_id = -1)
OR (bom.alternate_bom_designator = bad.alternate_designator_code
and bom.organization_id = bad.organization_id))
AND bom.alternate_bom_designator = v_wlji_alt_bom_designator(v_index)
AND BOM.assembly_item_id = v_wlji_item(v_index)
AND bom.organization_id = v_wlji_org(v_index);
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 = v_wlji_org(v_index)
AND MS.organization_id = v_wlji_org(v_index)
AND MS.inventory_item_id = v_wlji_item(v_index)
AND MSUB.secondary_inventory_name = v_wlji_completion_subinventory(v_index)
AND MSUB.organization_id = v_wlji_org(v_index);
delete from wip_operations
where wip_entity_id = v_wlji_wip_entity_id(v_index);
delete from wip_operation_yields
where wip_entity_id = v_wlji_wip_entity_id(v_index);
delete from wip_operation_resources
where wip_entity_id = v_wlji_wip_entity_id(v_index);
delete from wip_requirement_operations
where wip_entity_id = v_wlji_wip_entity_id(v_index);
raise update_job_exception;
when update_job_exception then
handle_error(l_error_code, l_error_msg, l_stmt_num);
fnd_file.put_line(fnd_file.log, 'Planning to skip update of wo, wor, wro');
fnd_file.put_line(fnd_file.log, 'No Plans to skip update of wo, wor, wro');
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 = l_temp_cc
and organization_id = v_wlji_org(v_index);
select
decode(wdj.primary_item_id, null, 0,
decode(wdj.net_quantity,
wdj.start_quantity, v_wlji_start_quantity(v_index),
least(wdj.net_quantity,
nvl(v_wlji_start_quantity(v_index), wdj.net_quantity))))
into v_wlji_net_quantity(v_index)
from wip_discrete_jobs wdj
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_index)
and wdj.organization_id = v_wlji_org(v_index);
UPDATE WIP_OPERATIONS
SET
FIRST_UNIT_START_DATE = decode(l_txnexist,
0, NVL(v_wlji_fusd(v_index), FIRST_UNIT_START_DATE), -- bug 3394520
FIRST_UNIT_START_DATE),
FIRST_UNIT_COMPLETION_DATE = decode(l_txnexist,
0, NVL(v_wlji_lucd(v_index), FIRST_UNIT_COMPLETION_DATE), -- bug 3394520
FIRST_UNIT_COMPLETION_DATE),
LAST_UNIT_START_DATE = decode(l_txnexist,
0, NVL(v_wlji_fusd(v_index), LAST_UNIT_START_DATE), -- bug 3394520
LAST_UNIT_START_DATE),
LAST_UNIT_COMPLETION_DATE = decode(l_txnexist,
0, NVL(v_wlji_lucd(v_index), LAST_UNIT_COMPLETION_DATE), -- bug 3394520
LAST_UNIT_COMPLETION_DATE),
SCHEDULED_QUANTITY = ROUND(v_wlji_start_quantity(v_index), WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
QUANTITY_IN_QUEUE = decode(v_wlji_status_type(v_index),
1, (decode(OPERATION_SEQ_NUM,10,0,QUANTITY_IN_QUEUE)),
3, (decode(p_old_status_type,
1, decode(OPERATION_SEQ_NUM,10,ROUND(v_wlji_start_quantity(v_index),
WIP_CONSTANTS.MAX_DISPLAYED_PRECISION), QUANTITY_IN_QUEUE),
decode(l_qntydiff,
1, decode(OPERATION_SEQ_NUM,
10,ROUND(v_wlji_start_quantity(v_index),WIP_CONSTANTS.MAX_DISPLAYED_PRECISION),
QUANTITY_IN_QUEUE),
QUANTITY_IN_QUEUE))),
QUANTITY_IN_QUEUE),
LAST_UPDATED_BY = v_wlji_last_updt_by(v_index),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = v_wlji_last_updt_login(v_index),
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = v_wlji_request_id(v_index),
PROGRAM_APPLICATION_ID = v_wlji_program_application_id(v_index),
PROGRAM_ID = v_wlji_program_id(v_index)
WHERE ORGANIZATION_ID = v_wlji_org(v_index)
AND WIP_ENTITY_ID = v_wlji_wip_entity_id(v_index);
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into wo');
UPDATE WIP_OPERATION_RESOURCES
SET START_DATE = decode(l_txnexist,
0, NVL(v_wlji_fusd(v_index), START_DATE), -- bug 3394520
START_DATE),
COMPLETION_DATE = decode(l_txnexist,
0, NVL(v_wlji_lucd(v_index), COMPLETION_DATE),
COMPLETION_DATE),
LAST_UPDATED_BY = v_wlji_last_updt_by(v_index),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = v_wlji_last_updt_login(v_index),
PROGRAM_UPDATE_DATE = SYSDATE,
REQUEST_ID = v_wlji_request_id(v_index),
PROGRAM_APPLICATION_ID = v_wlji_program_application_id(v_index),
PROGRAM_ID = v_wlji_program_id(v_index)
WHERE ORGANIZATION_ID = v_wlji_org(v_index)
AND WIP_ENTITY_ID = v_wlji_wip_entity_id(v_index);
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into wor');
UPDATE WIP_REQUIREMENT_OPERATIONS WRO
SET WRO.DATE_REQUIRED =
(SELECT NVL(MIN(FIRST_UNIT_START_DATE), v_wlji_fusd(v_index))
FROM WIP_OPERATIONS
WHERE ORGANIZATION_ID = v_wlji_org(v_index)
AND WIP_ENTITY_ID = v_wlji_wip_entity_id(v_index)
AND OPERATION_SEQ_NUM = ABS(WRO.OPERATION_SEQ_NUM)),
LAST_UPDATED_BY = v_wlji_last_updt_by(v_index),
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = v_wlji_last_updt_login(v_index),
REQUEST_ID = v_wlji_request_id(v_index),
PROGRAM_UPDATE_DATE = SYSDATE,
PROGRAM_ID = v_wlji_program_id(v_index),
PROGRAM_APPLICATION_ID = v_wlji_program_application_id(v_index),
REQUIRED_QUANTITY = (QUANTITY_PER_ASSEMBLY * decode(wro.basis_type, 2, 1, ROUND(v_wlji_start_quantity(v_index), 6)))
WHERE ORGANIZATION_ID = v_wlji_org(v_index)
AND WIP_ENTITY_ID = v_wlji_wip_entity_id(v_index);
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into wro');
select min(operation_seq_num)
into min_op_seq_num
from wip_operations
where wip_entity_id = v_wlji_wip_entity_id(v_index);
select bd.scrap_account, bd.est_absorption_account, wo.department_id
into l_scrap_account10, l_est_scrap_abs_account10, l_department_id
from bom_departments bd, wip_operations wo
where wo.wip_entity_id = v_wlji_wip_entity_id(v_index)
and wo.operation_seq_num = min_op_seq_num
and bd.department_id = wo.department_id;
UPDATE WIP_OPERATION_YIELDS WOY
SET SCRAP_ACCOUNT = nvl(l_scrap_account10, WOY.SCRAP_ACCOUNT),
EST_SCRAP_ABSORB_ACCOUNT = nvl(l_est_scrap_abs_account10, WOY.EST_SCRAP_ABSORB_ACCOUNT)
WHERE WIP_ENTITY_ID = v_wlji_wip_entity_id(v_index)
and operation_seq_num = min_op_seq_num;
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into woy');
select max(operation_seq_num)
into max_op_seq_num
from wip_operations
where wip_entity_id = v_wlji_wip_entity_id(v_index);
select bd.scrap_account, bd.est_absorption_account, wo.department_id
into l_scrap_account9999, l_est_scrap_abs_account9999, l_department_id
from bom_departments bd, wip_operations wo
where wo.wip_entity_id = v_wlji_wip_entity_id(v_index)
and wo.operation_seq_num = max_op_seq_num
and bd.department_id = wo.department_id;
UPDATE WIP_OPERATION_YIELDS WOY
SET SCRAP_ACCOUNT = nvl(l_scrap_account9999, WOY.SCRAP_ACCOUNT),
EST_SCRAP_ABSORB_ACCOUNT = nvl(l_est_scrap_abs_account9999, WOY.EST_SCRAP_ABSORB_ACCOUNT)
WHERE WIP_ENTITY_ID = v_wlji_wip_entity_id(v_index)
and operation_seq_num = max_op_seq_num;
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into woy');
delete from wip_period_balances
where wip_entity_id = v_wlji_wip_entity_id(v_index)
and organization_id = v_wlji_org(v_index);
fnd_file.put_line(fnd_file.log, 'Deleted '||SQL%ROWCOUNT||' rows from wpb');
update wip_discrete_jobs wdj
set status_type = 7
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_index);
fnd_file.put_line(fnd_file.log, 'Updated status type to 7 in wdj');
x_new_name := WSMPOPRN.update_job_name
(p_wip_entity_id => v_wlji_wip_entity_id(v_index),
p_subinventory => v_wlji_completion_subinventory(v_index),
p_org_id => v_wlji_org(v_index),
p_txn_type => 2, -- COMPLETION
p_update_flag => TRUE,
p_dup_job_name => l_dup_job_name,
x_error_code => l_error_code,
x_error_msg => l_error_msg);
inv_kanban_pvt.Update_Card_Supply_Status
(X_Return_Status => l_returnStatus,
p_Kanban_Card_Id => v_wlji_kanban_card_id(v_index),
p_Supply_Status => inv_kanban_pvt.g_supply_status_Exception);
select meaning
into translated_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 7
and upper(enabled_flag) = 'Y';
update wip_discrete_jobs
set kanban_card_id = null
where wip_entity_id = v_wlji_wip_entity_id(v_index);
UPDATE WIP_DISCRETE_JOBS WDJ
set last_updated_by = v_wlji_last_updt_by(v_index),
last_update_login = v_wlji_last_updt_login(v_index),
request_id = v_wlji_request_id(v_index),
program_application_id = v_wlji_program_application_id(v_index),
program_id = v_wlji_program_id(v_index),
program_update_date = sysdate,
last_update_date = sysdate,
bom_reference_id = v_wlji_bom_reference_id(v_index),
routing_reference_id = v_wlji_routing_reference_id(v_index),
common_bom_sequence_id = p_common_bill_sequence_id,
common_routing_sequence_id = p_common_routing_sequence_id,
bom_revision = v_wlji_bom_revision(v_index),
routing_revision = v_wlji_routing_revision(v_index),
bom_revision_date = v_wlji_bom_revision_date(v_index),
routing_revision_date = v_wlji_routing_revision_date(v_index),
alternate_bom_designator = v_wlji_alt_bom_designator(v_index),
alternate_routing_designator = v_wlji_alt_routing_designator(v_index),
firm_planned_flag = v_wlji_firm_planned_flag(v_index),
start_quantity = nvl(round(v_wlji_start_quantity(v_index), wip_constants.max_displayed_precision),
wdj.start_quantity),
net_quantity = nvl(round(v_wlji_net_quantity(v_index), wip_constants.max_displayed_precision),
wdj.net_quantity),
status_type = nvl(v_wlji_status_type(v_index),wdj.status_type),
date_released = v_wlji_date_released(v_index), -- bug 2697295
scheduled_start_date = decode(l_txnexist, 0,
trunc(v_wlji_fusd(v_index),'MI'), wdj.scheduled_start_date),
scheduled_completion_date = trunc(v_wlji_lucd(v_index),'MI'),
completion_locator_id = v_wlji_completion_locator_id(v_index),
completion_subinventory = v_wlji_completion_subinventory(v_index),
coproducts_supply = nvl(v_wlji_coproducts_supply(v_index), wdj.coproducts_supply),
-- BA: BUG3272873
source_code = nvl(v_wlji_source_code(v_index),wdj.source_code),
source_line_id = nvl(v_wlji_source_line_id(v_index),wdj.source_line_id),
overcompletion_tolerance_type = nvl(v_wlji_overcompl_tol_type(v_index),
wdj.overcompletion_tolerance_type),
overcompletion_tolerance_value = nvl(v_wlji_overcompl_tol_value(v_index),
wdj.overcompletion_tolerance_value),
priority = nvl(v_wlji_priority(v_index),wdj.priority),
due_date = nvl(v_wlji_due_date(v_index),wdj.due_date),
attribute_category = nvl(v_wlji_attribute_category(v_index),wdj.attribute_category),
attribute1 = nvl(v_wlji_attribute1(v_index),wdj.attribute1),
attribute2 = nvl(v_wlji_attribute2(v_index),wdj.attribute2),
attribute3 = nvl(v_wlji_attribute3(v_index),wdj.attribute3),
attribute4 = nvl(v_wlji_attribute4(v_index),wdj.attribute4),
attribute5 = nvl(v_wlji_attribute5(v_index),wdj.attribute5),
attribute6 = nvl(v_wlji_attribute6(v_index),wdj.attribute6),
attribute7 = nvl(v_wlji_attribute7(v_index),wdj.attribute7),
attribute8 = nvl(v_wlji_attribute8(v_index),wdj.attribute8),
attribute9 = nvl(v_wlji_attribute9(v_index),wdj.attribute9),
attribute10 = nvl(v_wlji_attribute10(v_index),wdj.attribute10),
attribute11 = nvl(v_wlji_attribute11(v_index),wdj.attribute11),
attribute12 = nvl(v_wlji_attribute12(v_index),wdj.attribute12),
attribute13 = nvl(v_wlji_attribute13(v_index),wdj.attribute13),
attribute14 = nvl(v_wlji_attribute14(v_index),wdj.attribute14),
attribute15 = nvl(v_wlji_attribute15(v_index),wdj.attribute15),
-- EA: BUG3272873
est_scrap_account = nvl(p_est_scrap_account, wdj.est_scrap_account),
est_scrap_var_account = nvl(p_est_scrap_var_account, wdj.est_scrap_var_account),
description = nvl(RTRIM(v_wlji_description(v_index)), wdj.description)
where wdj.wip_entity_id = v_wlji_wip_entity_id(v_index);
end if; -- update jobs for which status is not cancelled
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows of wdj');
UPDATE WIP_ENTITIES WE
set description = nvl(v_wlji_description(v_index), we.description),
last_updated_by = v_wlji_last_updt_by(v_index),
last_update_login = v_wlji_last_updt_login(v_index),
request_id = v_wlji_request_id(v_index),
program_application_id = v_wlji_program_application_id(v_index),
program_id = v_wlji_program_id(v_index),
program_update_date = v_wlji_prog_updt_date(v_index),
last_update_date = v_wlji_last_updt_date(v_index)
where we.wip_entity_id = v_wlji_wip_entity_id(v_index);
fnd_file.put_line(fnd_file.log, 'Updated '||SQL%ROWCOUNT||' rows into we');
insert_into_period_balances (
p_wip_entity_id => v_wlji_wip_entity_id(v_index),
p_organization_id => v_wlji_org(v_index),
p_class_code => v_wlji_class_code(v_index),
p_release_date => v_wlji_date_released(v_index), --p_date_released,
p_error_code => l_error_code,
p_err_msg => l_error_msg
);
select mtl_material_transactions_s.nextval
into txn_tmp_header_id
from dual;
insert into mtl_material_transactions_temp(
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_header_id,
transaction_source_id,
inventory_item_id,
organization_id,
revision,
subinventory_code,
locator_id,
transaction_quantity,
primary_quantity,
transaction_uom,
transaction_type_id,
transaction_action_id,
transaction_source_type_id,
transaction_date,
acct_period_id,
source_code,
source_line_id,
wip_entity_type,
negative_req_flag,
operation_seq_num,
wip_supply_type,
wip_commit_flag,
process_flag,
posting_flag,
transaction_temp_id)
values (
v_wlji_last_updt_date(v_index),
v_wlji_creation_date(v_index),
v_wlji_last_updt_by(v_index),
v_wlji_created_by(v_index),
v_wlji_last_updt_login(v_index),
txn_header_id, /* TRANSACTION_HEADER_ID */
v_wlji_wip_entity_id(v_index), /* TRANSACTION_SOURCE_ID */
v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id, /* INVENTORY_ITEM_ID */
v_wlji_org(v_index), /* ORGANIZATION_ID */
v_wsli(v_wlji_source_line_id(v_index)).revision, /* REVISION */
v_wsli(v_wlji_source_line_id(v_index)).subinventory_code, /* SUBINVENTORY_CODE */
v_wsli(v_wlji_source_line_id(v_index)).locator_id,
-l_quantity_tobe_issued,
---1 * v_wsli(v_wlji_source_line_id(v_index)).quantity, /* TRANSACTION_QUANTITY */
---1 * v_wsli(v_wlji_source_line_id(v_index)).quantity, /* PRIMARY_QUANTITY */
-l_quantity_tobe_issued,
v_wsli(v_wlji_source_line_id(v_index)).primary_uom_code, /* UNIT_OF_MEASURE */
35, /* TRANSACTION_TYPE_ID */
1, /* TRANSACTION_ACTION_ID */
5, /* TRANSACTION_SOURCE_TYPE_ID */
SYSDATE, /* TRANSACTION_DATE */
WSMPLCVA.v_org(v_wlji_org(v_index)).MAX_ORG_ACC_PERIODS, /*ACCT_PERIOD_ID */
'WSM',
to_char(v_wlji_source_line_id(v_index)), /* SOURCE_LINE_ID */
5, /* WIP_ENTITY_TYPE */
1, /* neg req flag */
10, /* op seq */
'', /* supply type */
'N', /* WIP_COMMIT_FLAG */
'Y', /* PROCESS_FLAG */
'Y', /* POSTING_FLAG */
-- txn_tmp_header_id /* Transaction Temp Id */
mtl_material_transactions_s.nextval /* Transaction Temp Id */
) RETURNING transaction_temp_id into txn_tmp_header_id;
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into mmtt');
INSERT INTO MTL_TRANSACTION_LOTS_TEMP (
transaction_temp_id,
last_update_date,
creation_date,
last_updated_by,
created_by,
last_update_login,
transaction_quantity,
primary_quantity,
lot_number)
values (
txn_tmp_header_id,
v_wlji_last_updt_date(v_index),
v_wlji_creation_date(v_index),
v_wlji_last_updt_by(v_index),
v_wlji_created_by(v_index),
v_wlji_last_updt_login(v_index),
-l_quantity_tobe_issued,
---1 * v_wsli(v_wlji_source_line_id(v_index)).quantity,
---1 * v_wsli(v_wlji_source_line_id(v_index)).quantity,
-l_quantity_tobe_issued,
v_wsli(v_wlji_source_line_id(v_index)).lot_number);
fnd_file.put_line(fnd_file.log, 'Inserted '||SQL%ROWCOUNT||' rows into mtlt');
UPDATE WIP_REQUIREMENT_OPERATIONS
set wip_supply_type = 1
where wip_entity_id = v_wlji_wip_entity_id(v_index)
and operation_seq_num = 10
and inventory_item_id = v_wsli(v_wlji_source_line_id(v_index)).inventory_item_id;
fnd_file.put_line(fnd_file.log, 'Before Calling WSM_LotAttr_PVT.create_update_lotattr');
WSM_LotAttr_PVT.create_update_lotattr(
x_err_code => l_error_code,
x_err_msg => l_error_msg,
p_wip_entity_id => v_wlji_wip_entity_id(v_index),
p_org_id => v_wlji_org(v_index),
p_intf_txn_id => v_wlji_header_id(v_index),
p_intf_src_code => 'WSM',
p_src_lot_number => l_src_lot_number,
p_src_inv_item_id => l_src_inv_item_id);
fnd_file.put_line(fnd_file.log, 'WSM_LotAttr_PVT.create_update_lotattr returned Success');
select organization_code
into org_code
--from ORG_ORGANIZATION_DEFINITIONS
from mtl_parameters
where organization_id = v_wlji_org(v_index);
select meaning
into job_type_meaning
from mfg_lookups
where lookup_type = 'WIP_DISCRETE_JOB'
and lookup_code = v_wlji_job_type(v_index);
select unique(concatenated_segments)
into assembly_name
from mtl_system_items_kfv
where inventory_item_id = v_wlji_item(v_index)
and organization_id = v_wlji_org(v_index);
inv_kanban_pvt.Update_Card_Supply_Status(
X_Return_Status => l_returnStatus,
p_Kanban_Card_Id => v_wlji_kanban_card_id(v_index),
p_Supply_Status => inv_kanban_pvt.g_supply_status_Exception);
select meaning
into translated_meaning
from mfg_lookups
where lookup_type = 'MTL_KANBAN_SUPPLY_STATUS'
and lookup_code = 7
and upper(enabled_flag) = 'Y';
update wip_discrete_jobs
set kanban_card_id = null
where wip_entity_id = v_wlji_wip_entity_id(v_index);
update wsm_lot_job_interface
set process_status = v_wlji_process_status(i),
error_code = v_wlji_err_code(i),
error_msg = v_wlji_err_msg(i),
request_id = v_wlji_request_id(i),
program_id = v_wlji_program_id(i),
program_application_id = v_wlji_program_application_id(i)
where header_id = v_wlji_header_id(i);
update wsm_lot_job_interface
set process_status = 4,
error_code = -2,
error_msg = l_error_msg
where mode_flag = 2;
delete from wsm_starting_lots_interface
where header_id IN
(select wlji.source_line_id
from wsm_lot_job_interface wlji
where wlji.process_status = 5
--and wlji.group_id = batch_group_id
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value)
);
fnd_file.put_line(fnd_file.log, 'Deleted '||SQL%ROWCOUNT||' rows from wsli');
delete from wsm_lot_job_interface
where process_status = 5
--and group_id = batch_group_id
and NVL(transaction_date, creation_date)
<= decode(l_del_int_prof_value,
null,
NVL(transaction_date, creation_date) -1,
SYSDATE-l_del_int_prof_value);
fnd_file.put_line(fnd_file.log, 'Deleted '||SQL%ROWCOUNT||' rows from wlji');
delete from bom_explosion_temp where group_id = wsmpwrot.explosion_group_id;