[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insert paramaeter first_unit_start in wjsi table
| when finite scheduler is on.
| also,caluclated job_start date for item when finite
| scheduler us ON AND top-most item is flow
| bugfix#2739590
|
|
| 20-JAn-2003 Kiran Konada
| bugfix 2755695
| Create a new mesage for a buy item
| a) when top-most item is flow
| CTO_SUB_LEVEL_BUY_ITEMS
|
b) when top-most item is discrete
| debug message in AFAS log file
| Created a new message when Discrete is under flow
| CTO_SUB_LEVEL_DISCRETE_REQ
|
| 24-Jan_2003 Kiran Konada
| bugfix 2755655 and 2756247
| added a outer joing bom_operational routings atbel
| if no routing is present, nvl(cfm_routing_flag to -99)
| modfied the if conditions to check for
| if(cfm_routing_flag = -99 or 2)
|
| 28-JAN-2003 Kiran Konada
| bugfix 2765109
| When a DIS/BUY sub-item is required at OP SEQ 1 of
| a flow parent . It's earliest required date would
| be scheduled start date of the first schedule
|
|
| 29-Jan-2003 Kiran Konada
| bugfix 2775097
| addded the effectivity date whil getting
| child configuration items
|
|
| 12-FEB-2003 Kiran Konada
| bugfix 2786582
| Get-operation_offset_date API requires line_op_Seq_id as input.
| bug: operation_Sequence_id was being passed
| fix: pass line_op_seq_id
|
| operation seq in BOM form belongs to EVENt aasocciated iwth flow
| routing.
| EVENT is usually associated to either line_operation (and/or) process
| If event is not assocaited to any line_opeartion , we wil get the
| component required at that particular event at the start of flow
| schedle
|
|
| 01-MAR-2002 Kiran Konada
| bugfix 2827357
| changed ceil to Floor as wipltesb.pls was using floor. Cto needs to be in sync
| with WIP calculations
|
|
| 01-MAR-2002 Kiran konada
| bugifx 2817556
| added a attribute 'comment' to record structure in spec CTOSUBSS.pls
| added new record and table r_consolidated_sub_item, t_cons_item_details
| Added a new procedure check_recurring_item
|
|
|
|
| 05-MAR-2002 Kiran Konada
| bugfix 2834244
| check for effectivity added
|
| 21-MAR-2002 Kiran
| 2858631
|
|
| 13-AUG-2003 Kiran Konada
for bug# 3063156
propagte bugfix 3042904 to main
| Passed project_id and task_id as parameters to populate_req_interface
|
|
|
|
| 26-AUg-2003 Kiran Konada
| changes for DMF-J
| becuase of mutiple sources enahcement
| sourcetype 66 (invalid sourcing) is not an error any more
|
|
| 03-NOV-2003 Kiran Konada
|
| Main propagation bug#3140641
|
| Reverting bugfix made on 13-AUG-2003. removing project-id and task_id as
| as parametrs to populate req interface
| Instead passing P_top_most_line_id as parameter as interface_sourc_line_id
| to populate_req_interafce. porject_id and task_id is calculated within pop
| ulate req_intreface. This is done to remove dependency on CTOPROCS.pls spec
| reverted bugfix 3042904 and provided
solution thru fix 3129117
| Has functional dependecy on CTOPROCB.pls
|
|
| 02-05-2004 Kiran Konada
| Bugfix# 3418102
| Project_id and task_id is passed to child cofniguration item supply
| only when item attribute end_pegging_flag is set to 'I','X'
|
| 02-03-2005 Kiran Konada
| BUG#4153987
| FP :11.5.9 - 11.5.10 : of 4134956
| With this fix CTO will consider the component yield factor
|
| 06-Jan-2006 Kiran Konada
| bugfix#4492875
| Removed the debug statement having sql%rowcount as parameter, which
| was immeditaly after sql statement and before if statement using sql%rowcount
|
| Reason : if there is a logic dependent on sql%rowcount and debug log statement before
| it uses sql%rowcount , then logic may go wrong
|
|
| 20-Feb-2006 Kiran Konada
| FP 5011199 base bug 4998922
| Look at only primary BOM's
|
| 22-Feb-2006 Kiran Konada
| bigfix 4615409
| get operation_lead_time percent from bom_operational_routings
| NOT from bom_inventory_components
|
|
| 23-Feb-2006 kiran Konada
| bugfix 5676839
| in FLM routing we should EVENTS onlu ie operation_type = 1
=============================================================================*/
TYPE r_flow_sch_details IS RECORD(
t_flow_sch_index number,
order_line_id number, --sales order_line_id
t_item_details_index number,
schedule_number wip_flow_schedules.schedule_number%type,
wip_entity_id wip_flow_schedules.wip_entity_id%type,
scheduled_start_date wip_flow_schedules.scheduled_start_date%type,
planned_quantity wip_flow_schedules.planned_quantity%type,
scheduled_completion_date wip_flow_schedules.scheduled_completion_date%type,
build_sequence wip_flow_schedules.build_sequence%type,
line_id wip_flow_schedules.line_id%type,
line_code wip_lines.line_code%type,
synch_schedule_num wip_flow_schedules.synch_schedule_num%type,
SYNCH_OPERATION_SEQ_NUM wip_flow_schedules.SYNCH_OPERATION_SEQ_NUM%type
);
select SUM( decode( nvl(bic.basis_type,1), 1 , bic.COMPONENT_QUANTITY/bic.component_yield_factor, 0 )) ,
SUM( decode( nvl(bic.basis_type,1), 2 , bic.COMPONENT_QUANTITY/bic.component_yield_factor, 0 )) , 'Y'
INTO x_comp_item_qty , x_comp_lot_qty, v_recurr_flag
FROM BOM_INVENTORY_COMPONENTS bic,
bom_bill_of_materials bom
WHERE bic.bill_sequence_id = bom.common_bill_sequence_id
and bom.assembly_item_id = p_parent_item_id
and bom.organization_id = p_organization_id
AND bic.COMPONENT_ITEM_ID = p_item_id
and bic.effectivity_date <= sysdate --bugfix
and nvl(bic.disable_date,sysdate+1) > sysdate --2834244
and bom.ALTERNATE_BOM_DESIGNATOR is null --bug 4998922
GROUP BY bic.COMPONENT_ITEM_ID
HAVING COUNT(*) >1;
select min(OPERATION_SEQ_NUM)
into x_min_op_seq_num
FROM BOM_INVENTORY_COMPONENTS bic,
bom_bill_of_materials bom
WHERE bic.bill_sequence_id = bom.common_bill_sequence_id
and bom.assembly_item_id = p_parent_item_id
and bom.organization_id = p_organization_id
AND bic.COMPONENT_ITEM_ID = p_item_id
and bic.effectivity_date <= sysdate --bugfix
and nvl(bic.disable_date,sysdate+1) > sysdate --2834244
and bom.ALTERNATE_BOM_DESIGNATOR is null; --bug 4998922
Select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
INTO x_oper_lead_time_per
FROM BOM_INVENTORY_COMPONENTS bic,
bom_bill_of_materials bom,
--bugfix 4615409
bom_operational_routings bor_p,
bom_operation_sequences bos_p
WHERE bic.bill_sequence_id = bom.common_bill_sequence_id
and bom.assembly_item_id = p_parent_item_id
and bom.organization_id = p_organization_id
AND bic.COMPONENT_ITEM_ID = p_item_id
and bic.operation_seq_num = x_min_op_seq_num
and bic.effectivity_date <= sysdate --bugfix
and nvl(bic.disable_date,sysdate+1) > sysdate --2834244
and bom.ALTERNATE_BOM_DESIGNATOR is null --bug 4998922
--bugfix4615409
and bor_p.assembly_item_id = bom.assembly_item_id
and bor_p.organization_id = bom.organization_id
and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
and bic.operation_seq_num=bos_p.operation_seq_num
and nvl(bos_p.operation_type,1)=1;--only events for FLM routing 5676839
SELECT BCD1.CALENDAR_DATE into l_new_date
FROM BOM_CALENDAR_DATES BCD1,
BOM_CALENDAR_DATES BCD2,
MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = porgid
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(Pdate)
AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.PRIOR_SEQ_NUM) - pleadtime;
SELECT BCD1.CALENDAR_DATE into l_new_date
FROM BOM_CALENDAR_DATES BCD1,
BOM_CALENDAR_DATES BCD2,
MTL_PARAMETERS MP
WHERE MP.ORGANIZATION_ID = porgid
AND BCD1.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD2.CALENDAR_CODE = MP.CALENDAR_CODE
AND BCD1.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.EXCEPTION_SET_ID = MP.CALENDAR_EXCEPTION_SET_ID
AND BCD2.CALENDAR_DATE = TRUNC(Pdate)
AND BCD1.SEQ_NUM = NVL(BCD2.SEQ_NUM, BCD2.NEXT_SEQ_NUM) + pleadtime;
select component_item_id,
msi.concatenated_segments,
component_quantity/bic.component_yield_factor, -- bugfix 4134956: take component_yield_factor into account
bic.operation_seq_num,
nvl(bor.cfm_routing_flag,-99), --default to -99 if no routing and treat it as discrete
bor.routing_sequence_id,
nvl(msi.fixed_lead_time,0),
nvl(msi.variable_lead_time,0),
nvl(msi.full_lead_time,0),
nvl(msi.postprocessing_lead_time,0),
bic.bom_item_type,
msi.auto_created_config_flag,
bor.line_id,
wil.line_code,
end_assembly_pegging_flag, --Bugfix# 3418102
nvl(bic.basis_type,1), /* LBM Project */
bic.wip_supply_type --4645636
from bom_inventory_components bic,
bom_bill_of_materials bom,
mtl_System_items_kfv msi,
--mtl_system_items msi,
bom_operational_routings bor,
wip_lines wil
--bugfix 4615409
--bom_operational_routings bor_p,--parent
--bom_operation_sequences bos_p
where bic.bill_sequence_id = bom.common_bill_sequence_id
and bom.assembly_item_id = pParentItemId
and bom.organization_id = pOrganization_id
and bic.component_item_id = msi.inventory_item_id
and bic.effectivity_date <= sysdate --bugfix
and nvl(bic.disable_date,sysdate+1) > sysdate --2775097
and msi.organization_id = pOrganization_id
and bor.assembly_item_id (+)= bic.component_item_id
and bor.ALTERNATE_ROUTING_DESIGNATOR(+) is null
and bor.organization_id (+) = pOrganization_id
and bor.line_id = wil.line_id(+)
and msi.auto_created_config_flag = 'Y'
and bom.ALTERNATE_BOM_DESIGNATOR is null; --bug 4998922
select component_item_id,
msi.concatenated_segments,
component_quantity/bic.component_yield_factor, -- bugfix 4134956: take component_yield_factor into account
bic.operation_seq_num,
nvl(bor.cfm_routing_flag,-99), --default to -99 if no routing and treat it as discrete
bor.routing_sequence_id,
nvl(msi.fixed_lead_time,0),
nvl(msi.variable_lead_time,0),
nvl(msi.full_lead_time,0),
nvl(msi.postprocessing_lead_time,0),
bic.bom_item_type,
msi.auto_created_config_flag,
bor.line_id,
wil.line_code,
end_assembly_pegging_flag, --Bugfix# 3418102
nvl(bic.basis_type,1),/* LBM Project */
bic.wip_supply_type --4645636
from bom_inventory_components bic,
bom_bill_of_materials bom,
mtl_System_items_kfv msi,
--mtl_System_items_b msi,
bom_operational_routings bor,
wip_lines wil
--bugfix 4615409
--bom_operational_routings bor_p,--parent
--bom_operation_sequences bos_p
where bic.bill_sequence_id = bom.common_bill_sequence_id
and bom.assembly_item_id = pParentItemId
and bom.organization_id = pOrganization_id
and bic.component_item_id = msi.inventory_item_id
and bic.effectivity_date <= sysdate --bugfix
and nvl(bic.disable_date,sysdate+1) > sysdate --2775097
and msi.organization_id = pOrganization_id
and bor.assembly_item_id (+) = bic.component_item_id
and bor.ALTERNATE_ROUTING_DESIGNATOR(+) is null
and bor.organization_id (+) = pOrganization_id
and bor.line_id = wil.line_id(+)
and msi.replenish_to_order_flag = 'Y'
and bic.bom_item_type = 4
and bom.ALTERNATE_BOM_DESIGNATOR is null; --bug 4998922
select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
INTO pitems_table(l_index).operation_lead_time_percent
from bom_operational_routings bor_p,--parent
bom_operation_sequences bos_p
where bor_p.assembly_item_id = pParentItemId
and bor_p.organization_id = pOrganization_id
and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
and nvl(bos_p.operation_type,1)=1; --consider events only for FLM cases.5676839
select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
INTO pitems_table(l_index).operation_lead_time_percent
from bom_operational_routings bor_p,--parent
bom_operation_sequences bos_p
where bor_p.assembly_item_id = pParentItemId
and bor_p.organization_id = pOrganization_id
and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
and nvl(bos_p.operation_type,1)=1; --consider events only for FLM cases.5676839
INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP
( order_line_id,
item_index,
schedule_number,
wip_entity_id,
scheduled_start_date ,
planned_quantity ,
scheduled_completion_date,
build_sequence,
line_id
)
VALUES( pitems_table(pIndex).order_line_id,
pflow_sch_details(l_flow_index).t_item_details_index, --current child item index
pflow_sch_details(l_flow_index).schedule_number,
pflow_sch_details(l_flow_index).wip_entity_id,
pflow_sch_details(l_flow_index).scheduled_start_date,
pflow_sch_details(l_flow_index).planned_quantity,
pflow_sch_details(l_flow_index).scheduled_completion_date,
pflow_sch_details(l_flow_index).build_sequence,
pflow_sch_details(l_flow_index).line_id
);
Select distinct(bcmm.order_line_id),oeh.order_number
from bom_cto_mlsupply_main_temp bcmm,
oe_order_lines_all oel,
oe_order_headers_all oeh
where bcmm.order_line_id = oel.line_id
and oel.header_id = oeh.header_id
order by oeh.order_number, bcmm.order_line_id;
SELECT item_index,
parent_index,
ITEM_ID,
item_name,
ITEM_QUANTITY,
NEEDED_ITEM_QTY,
AUTO_CONFIG_FLAG,
JOB_START_DATE,
JOB_COMPLETION_DATE,
SOURCE_TYPE,
CFM_ROUTING_FLAG,
comments
FROM bom_cto_mlsupply_main_temp
WHERE order_line_id = l_order_line_id
order by item_index;
SELECT item_index,
schedule_number,
scheduled_start_date,
scheduled_completion_date,
synch_schedule_num
FROM bom_cto_mlsupply_flow_temp
WHERE order_line_id = l_order_line_id
order by item_index,scheduled_completion_date,schedule_number;
insert into wip for child discrete make --but wip mass load called with differnet sequenece
insert into child buy
*/
Procedure create_subassembly_jobs
(
p_mlsupply_parameter in number, --org parameter indicating whether auto-created or ( AtOITEM and autocreated) 1= autocreated and 2 =
p_Top_Assembly_LineId in number,
pSupplyQty in number,
p_wip_seq in number,
p_status_type in number,
p_class_code in varchar2,
p_conc_request_id IN NUMBER,
p_conc_program_id IN NUMBER,
p_conc_login_id IN NUMBER,
p_user_id IN NUMBER,
p_appl_conc_program_id IN NUMBER,
x_return_status out NOCOPY varchar2,
x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
)
is
l_finite_scheduler_flag number := null;
SELECT wfs.schedule_number,
wfs.wip_entity_id,
wfs.scheduled_start_date,
wfs.planned_quantity,
wfs.scheduled_completion_date,
wfs.build_sequence,
wfs.line_id,
wil.line_code
FROM wip_flow_schedules wfs,
wip_lines wil
WHERE demand_source_line = p_Top_Assembly_LineId
AND wfs.line_id = wil.line_id;
SELECT oel.inventory_item_id,
oel.ship_from_org_id,
oel.schedule_ship_date,
oel. project_id,
oel.task_id,
oel.ordered_quantity,
mtl.concatenated_segments,
mtl.auto_created_config_flag,
nvl(mtl.fixed_lead_time,0),
nvl(mtl.variable_lead_time,0),
nvl(mtl.full_lead_time,0),
order_quantity_uom ,
oeh.order_number,
nvl(bor.cfm_routing_flag,-99),
bor.routing_sequence_id
INTO l_item_id,
l_ship_org,
l_schedule_ship_date,
l_project_id,
l_task_id,
l_ordered_quantity,
l_item_name,
l_auto_config_flag,
l_fixed_lead_time,
l_variable_lead_time,
l_processing_lead_time,
l_ordered_uom,
l_order_number,
l_cfm_routing_flag,
l_routing_sequence_id
FROM oe_order_lines_all oel,
oe_order_headers_all oeh,
mtl_system_items_kfv mtl,
bom_operational_routings bor
WHERE oel.line_id = p_Top_Assembly_LineId
AND oeh.header_id = oel.header_id
AND oel.inventory_item_id = mtl.inventory_item_id
AND oel.ship_from_org_id = mtl.organization_id
AND bor.assembly_item_id (+)= mtl.inventory_item_id
AND bor.organization_id(+) = mtl.organization_id
AND bor.alternate_routing_designator(+) is null
;
IF (l_mlsupply_items(1).flow_start_index = 1) THEN --which means there was a row inserted
l_mlsupply_items(1).flow_end_index := l_flow_sch_details.last;
INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP
( order_line_id,
item_index,
schedule_number,
wip_entity_id,
scheduled_start_date ,
planned_quantity ,
scheduled_completion_date,
build_sequence,
line_id,
synch_schedule_num,
SYNCH_OPERATION_SEQ_NUM )
SELECT
p_Top_Assembly_LineId,
1 ,
schedule_number,
wip_entity_id,
scheduled_start_date ,
planned_quantity ,
scheduled_completion_date,
build_sequence,
line_id,
synch_schedule_num,
SYNCH_OPERATION_SEQ_NUM
FROM wip_flow_schedules
where demand_source_line = p_Top_Assembly_LineId;
--Insert for top most parent into BOM_CTO_MLSUPPLY_MAIN_TEMP table
l_stmt_num := 180;
INSERT INTO BOM_CTO_MLSUPPLY_MAIN_TEMP
( order_line_id,
item_index ,
item_id,
item_name,
AUTO_CONFIG_FLAG,
item_quantity,
needed_item_qty ,
cfm_routing_flag ,
routing_sequence_id ,
fixed_lead_time,
variable_lead_time ,
processing_lead_time ,
job_completion_date,
line_id,
line_code,
flow_start_index,
flow_end_index
)
VALUES ( p_Top_Assembly_LineId,
1, --as it is first elemnt
l_item_id,
l_item_name ,
l_auto_config_flag,
l_mlsupply_items(1).item_quantity,
l_mlsupply_items(1).needed_item_qty ,
l_cfm_routing_flag ,
l_routing_sequence_id ,
l_fixed_lead_time,
l_variable_lead_time ,
l_processing_lead_time ,
l_schedule_ship_date,
l_mlsupply_items(1).line_id,
l_mlsupply_items(1).line_code,
l_mlsupply_items(1).flow_start_index,
l_mlsupply_items(1).flow_end_index
) ;
--insert data into MAIN table
INSERT INTO BOM_CTO_MLSUPPLY_MAIN_TEMP
( order_line_id,
item_index ,
PARENT_INDEX,
item_id,
item_name,
AUTO_CONFIG_FLAG,
item_quantity,
needed_item_qty ,
cfm_routing_flag ,
routing_sequence_id ,
fixed_lead_time,
variable_lead_time ,
processing_lead_time ,
--job_completion_date,
line_id,
line_code,
flow_start_index,
flow_end_index,
source_type,
comments,
wip_supply_type,
OPERATION_SEQ_NUM
)
VALUES ( p_Top_Assembly_LineId,
l_index,
l_mlsupply_items(l_index).parent_index,
l_mlsupply_items(l_index).item_id,
l_mlsupply_items(l_index).item_name,
l_mlsupply_items(l_index).auto_config_flag,
l_mlsupply_items(l_index).item_quantity,
l_mlsupply_items(l_index).needed_item_qty ,
l_mlsupply_items(l_index).cfm_routing_flag,
l_mlsupply_items(l_index).routing_sequence_id ,
l_mlsupply_items(l_index).fixed_lead_time,
l_mlsupply_items(l_index).variable_lead_time ,
l_mlsupply_items(l_index).processing_lead_time ,
--l_schedule_ship_date,
l_mlsupply_items(l_index).line_id,
l_mlsupply_items(l_index).line_code,
l_mlsupply_items(l_index).flow_start_index,
l_mlsupply_items(l_index).flow_end_index,
l_mlsupply_items(l_index).source_type,
l_mlsupply_items(l_index).comment,
l_mlsupply_items(l_index).wip_supply_type, --4645636
l_mlsupply_items(l_index).operation_seq_num --4645636
) ;
select 'Y' INTO l_phantom
from BOM_CTO_MLSUPPLY_MAIN_TEMP
where wip_supply_type = 6
and rownum = 1;
--get the value of wip finite scheduler flag if not selected previously
IF(l_finite_scheduler_flag is null) THEN
SELECT nvl(use_finite_scheduler,2)
INTO l_finite_scheduler_flag
FROM wip_parameters
WHERE organization_id = l_ship_org;
--populate start date flag = 1implies insert satrt date in wjsi instead of completion date
l_mlsupply_items(l_mlsupply_items(l_index).parent_index).populate_start_date := 1;
--update parent items job start date
l_stmt_num := 300;
update bom_cto_mlsupply_main_temp
set job_start_date = l_mlsupply_items(l_mlsupply_items(l_index).parent_index).job_start_date
where item_index = l_mlsupply_items(l_index).parent_index
and order_line_id = p_Top_Assembly_LineId ;
l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
--as we need to insert both first unit start date as well as last unit completion date
--bugfix#2739590
-- rkaza. 05/05/2005.
-- Following block is only
-- needed for WIP items.
IF(l_mlsupply_items(1).cfm_routing_flag = 1) -- top item is flow
and l_mlsupply_items(l_index).source_type = 2
and l_mlsupply_items(l_index).cfm_routing_flag <> 1 THEN
get_start_date(
pCompletion_date => l_mlsupply_items(l_index).job_completion_date,
pQty => l_mlsupply_items(l_index).needed_item_qty,
pitemid => l_mlsupply_items(l_index).item_id,
porganization_id => l_ship_org,
pfixed_leadtime => l_mlsupply_items(l_index).fixed_lead_time,
pvariable_leadtime => l_mlsupply_items(l_index).variable_lead_time,
x_start_date => x_parent_job_start_date,
x_return_status => l_ret_status,
x_error_message => l_error_messsage,
x_message_name => l_msg_name
);
l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
update bom_cto_mlsupply_main_temp
set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
job_start_date = l_mlsupply_items(l_index).job_start_date -- could be null value
where item_index = l_index
and order_line_id = p_Top_Assembly_LineId ;
SELECT max(scheduled_completion_date)
into max_completion_date
from BOM_CTO_MLSUPPLY_FLOW_TEMP
where item_index = l_mlsupply_items(l_index).parent_index
and order_line_id = p_Top_Assembly_LineId ;
INSERT INTO BOM_CTO_MLSUPPLY_FLOW_TEMP (
order_line_id,
item_index,
schedule_number,
wip_entity_id,
scheduled_start_date ,
planned_quantity ,
scheduled_completion_date,
build_sequence,
line_id,
synch_schedule_num,
SYNCH_OPERATION_SEQ_NUM )
SELECT p_Top_Assembly_LineId,
l_index, --current child item index
schedule_number,
wip_entity_id,
scheduled_start_date ,
planned_quantity ,
scheduled_completion_date,
build_sequence,
line_id,
synch_schedule_num,
SYNCH_OPERATION_SEQ_NUM
FROM wip_flow_schedules
where primary_item_id = l_mlsupply_items(l_index).item_id
and synch_schedule_num in
( Select schedule_number
from BOM_CTO_MLSUPPLY_FLOW_TEMP
where item_index = l_mlsupply_items(l_index).parent_index
and order_line_id = p_Top_Assembly_LineId
);
Select nvl(line_op_seq_id,-99) --bugfix 2786582
into l_operation_seq_id
from bom_operation_sequences
where routing_sequence_id = l_mlsupply_items(l_mlsupply_items(l_index).parent_index).routing_sequence_id
and operation_seq_num = l_mlsupply_items(l_index).operation_seq_num
and operation_type =1
and nvl(EFFECTIVITY_DATE,sysdate+1) <= SYSDATE
and nvl(disable_date,sysdate+1) > sysdate;
SELECT min(scheduled_start_date)
into l_child_operation_date
from BOM_CTO_MLSUPPLY_FLOW_TEMP
where item_index = l_mlsupply_items(l_index).parent_index;
SELECT min(scheduled_completion_date)
into l_min_completion_date
from BOM_CTO_MLSUPPLY_FLOW_TEMP
where item_index = l_mlsupply_items(l_index).parent_index;
update bom_cto_mlsupply_main_temp
set job_completion_date = l_mlsupply_items(l_index).job_completion_date
where item_index = l_index
and order_line_id = p_Top_Assembly_LineId;
--get the value of wip finite scheduler flag if not selected previously when uder flow
IF(l_finite_scheduler_flag is null) THEN
SELECT nvl(use_finite_scheduler,2)
INTO l_finite_scheduler_flag
FROM wip_parameters
WHERE organization_id = l_ship_org;
l_mlsupply_items(l_index).populate_start_date := 1; -- to insert wip inetrface with satrt date
update bom_cto_mlsupply_main_temp
set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
job_start_date = l_mlsupply_items(l_index).job_start_date
where item_index = l_index
and order_line_id = p_Top_Assembly_LineId;
--as we need to insert both first unit start date as well as last unit completion date
--bugfix#2739590
IF(l_mlsupply_items(1).cfm_routing_flag = 1) THEN --top most item is flow
get_start_date(
pCompletion_date => l_mlsupply_items(l_index).job_completion_date,
pQty => l_mlsupply_items(l_index).needed_item_qty,
pitemid => l_mlsupply_items(l_index).item_id,
porganization_id => l_ship_org,
pfixed_leadtime => l_mlsupply_items(l_index).fixed_lead_time,
pvariable_leadtime => l_mlsupply_items(l_index).variable_lead_time,
x_start_date => x_parent_job_start_date,
x_return_status => l_ret_status,
x_error_message => l_error_messsage,
x_message_name => l_msg_name
);
update bom_cto_mlsupply_main_temp
set job_completion_date = l_mlsupply_items(l_index).job_completion_date,
job_start_date = l_mlsupply_items(l_index).job_start_date
where item_index = l_index
and order_line_id = p_Top_Assembly_LineId;
update bom_cto_mlsupply_main_temp
set job_completion_date = l_mlsupply_items(l_index).job_completion_date
where item_index = l_index
and order_line_id = p_Top_Assembly_LineId;
oe_debug_pub.add('create_subassembly_jobs: ' || 'before inserting children in wjsi ',1);
cto_wip_workflow_api_pk.cto_debug ('create_sub_assembly_jobs','insert po_interafce'|| l_mlsupply_items(l_index).item_id);
oe_debug_pub.add('create_subassembly_jobs: ' || 'insert po_interafce'|| l_mlsupply_items(l_index).item_id);
oe_debug_pub.add('create_sub_assembly_jobs insert po_interafce'|| l_mlsupply_items(l_index).item_id);
oe_debug_pub.add('create_subassembly_jobs: ' || 'Req Insert successful for '|| l_mlsupply_items(l_index).item_id ,1);
insert into wip_job_schedule_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
group_id,
source_code,
process_phase,
process_status,
organization_id,
load_type,
status_type,
last_unit_completion_date,
primary_item_id,
wip_supply_type,
class_code,
firm_planned_flag,
start_quantity,
bom_revision_date,
routing_revision_date,
project_id,
task_id,
due_date,
bom_revision
)
select SYSDATE,
p_user_id,
SYSDATE,
p_user_id,
p_conc_login_id,
p_conc_request_id,
p_conc_program_id,
p_appl_conc_program_id,
SYSDATE,
p_wip_seq,
'WICDOL',
WIP_CONSTANTS.ML_VALIDATION,
WIP_CONSTANTS.PENDING, -- process_status
l_ship_org, -- organization id
WIP_CONSTANTS.CREATE_JOB, --Load_Type
nvl(p_status_type, WIP_CONSTANTS.UNRELEASED), -- Status_Type
l_mlsupply_items(l_index).job_completion_date, -- Date Completed
l_mlsupply_items(l_index).item_id, --Primary_Item_Id
WIP_CONSTANTS.BASED_ON_BOM, -- Wip_Supply_Type
decode(p_class_code, null, null
, p_class_code), --Accouting Class
2, --Firm_Planned_Flag
l_mlsupply_items(l_index).needed_item_qty,
trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
'MI')+1/(60*24), --BOM_Revision_Date
greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
--Routing_Revision_Date
--bugfix 3418102
decode(l_mlsupply_items(l_index).pegging_flag,'I',l_project_id,'X',l_project_id, null),
decode(l_mlsupply_items(l_index).pegging_flag,'I',l_task_id,'X',l_task_id, null),
--end bugfix 3418102
l_mlsupply_items(l_index).job_completion_date,
BOM_REVISIONS.get_item_revision_fn
( 'ALL',
'ALL',
l_ship_org,
l_mlsupply_items(l_index).item_id,
(trunc (greatest(nvl(cal.calendar_date,SYSDATE),
SYSDATE),'MI')+1/(60*24) )
)
from bom_calendar_dates cal,
mtl_parameters mp,
wip_parameters wp,
mtl_system_items msi
where mp.organization_id = l_ship_org
and wp.organization_id = mp.organization_id
and msi.organization_id = l_ship_org
and msi.inventory_item_id = l_mlsupply_items(l_index).item_id --inventory item id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num =
(select greatest(1, (cal2.prior_seq_num -
(ceil(nvl(msi.fixed_lead_time,0) +
nvl(msi.variable_lead_time,0) *
l_mlsupply_items(l_index).needed_item_qty --bugfix 2074290: this is in primary uom
))))
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id =
mp.calendar_exception_set_id
and cal2.calendar_date =
trunc(l_mlsupply_items(l_index).job_completion_date)
);
oe_debug_pub.add('create_subassembly_jobs: ' || 'Number of Rows Inserted in WJSI for children : ' || to_char(SQL%ROWCOUNT));
oe_debug_pub.add('create_subassembly_jobs: ' || 'GROUP ID Inserted in WJSI for children : ' || x_groupID);
select wip_job_schedule_interface_s.nextval
into x_groupID
from dual;
insert into wip_job_schedule_interface
(last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_id,
program_application_id,
program_update_date,
group_id,
source_code,
process_phase,
process_status,
organization_id,
load_type,
status_type,
last_unit_completion_date,
primary_item_id,
wip_supply_type,
class_code,
firm_planned_flag,
start_quantity,
bom_revision_date,
routing_revision_date,
project_id,
task_id,
due_date,
bom_revision,
scheduling_method, --inserted ml_manual inorder to stop finite scheduler run
first_unit_start_date --enter first unit start date if finite scheduler is turned on bugfix#2739590
)
select SYSDATE,
p_user_id,--l_user_id,
SYSDATE,
p_user_id,--l_user_id,
null, --l_login_id,
null,
null,--35740,
null,--706,
SYSDATE,
x_groupID,
'WICDOL',
WIP_CONSTANTS.ML_VALIDATION,
WIP_CONSTANTS.PENDING, -- process_status
l_ship_org, -- organization id
WIP_CONSTANTS.CREATE_JOB, --Load_Type
nvl(p_status_type, WIP_CONSTANTS.UNRELEASED), -- Status_Type
l_mlsupply_items(l_index).job_completion_date, -- Date Completed
l_mlsupply_items(l_index).item_id, --Primary_Item_Id
WIP_CONSTANTS.BASED_ON_BOM, -- Wip_Supply_Type
decode(p_class_code, null, null
, p_class_code), --Accouting Class
2, --Firm_Planned_Flag
l_mlsupply_items(l_index).needed_item_qty,
trunc(greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
'MI')+1/(60*24), --BOM_Revision_Date
greatest(nvl(cal.calendar_date,SYSDATE), SYSDATE),
--Routing_Revision_Date
--bugfix 3418102
decode(l_mlsupply_items(l_index).pegging_flag,'I',l_project_id,'X',l_project_id, null),
decode(l_mlsupply_items(l_index).pegging_flag,'I',l_task_id,'X',l_task_id, null),
--end bugfix 3418102
l_mlsupply_items(l_index).job_completion_date,
BOM_REVISIONS.get_item_revision_fn
( 'ALL',
'ALL',
l_ship_org,
l_mlsupply_items(l_index).item_id,
(trunc (greatest(nvl(cal.calendar_date,SYSDATE),
SYSDATE),'MI')+1/(60*24) )
),
decode(nvl(wp.use_finite_scheduler,2), 1,
WIP_CONSTANTS.ML_MANUAL,
null),
decode(nvl(wp.use_finite_scheduler,2), 1,
l_mlsupply_items(l_index).job_start_date,
null)
from bom_calendar_dates cal,
mtl_parameters mp,
wip_parameters wp,
mtl_system_items msi
where mp.organization_id = l_ship_org
and wp.organization_id = mp.organization_id
and msi.organization_id = l_ship_org
and msi.inventory_item_id = l_mlsupply_items(l_index).item_id --inventory item id
and cal.calendar_code = mp.calendar_code
and cal.exception_set_id = mp.calendar_exception_set_id
and cal.seq_num =
(select greatest(1, (cal2.prior_seq_num -
(ceil(nvl(msi.fixed_lead_time,0) +
nvl(msi.variable_lead_time,0) *
l_mlsupply_items(l_index).needed_item_qty --bugfix 2074290: this is in primary uom
))))
from bom_calendar_dates cal2
where cal2.calendar_code = mp.calendar_code
and cal2.exception_set_id =
mp.calendar_exception_set_id
and cal2.calendar_date =
trunc(l_mlsupply_items(l_index).job_completion_date)
);
oe_debug_pub.add('create_subassembly_jobs: ' || 'Number of Rows Inserted in WJSI for children : ' || to_char(SQL%ROWCOUNT));
oe_debug_pub.add('create_subassembly_jobs: ' || 'GROUP ID Inserted in WJSI for children : ' || x_groupID);
oe_debug_pub.add('create_subassembly_jobs: ' || 'REQUEST ID Inserted in WJSI for children : ' || l_requestId);
update BOM_CTO_MLSUPPLY_MAIN_TEMP
set actual_parent_index = pitems_table(m_index).actual_parent_idx,
parent_index = pitems_table(m_index).parent_index
where ITEM_INDEX = m_index;
select INHERIT_PHANTOM_OP_SEQ
into l_inherit_phantom_op_seq
from bom_parameters
where organization_id = p_organization_id;
update BOM_CTO_MLSUPPLY_MAIN_TEMP
set operation_seq_num = pitems_table(l_index).operation_seq_num,
OPERATION_LEAD_TIME_PERCENT = pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT
where ITEM_INDEX = l_index;
select nvl(bos_p.OPERATION_LEAD_TIME_PERCENT,0)
INTO pitems_table(l_index).operation_lead_time_percent
from bom_operational_routings bor_p,--parent
bom_operation_sequences bos_p
where bor_p.assembly_item_id = pitems_table(l_parent_index).item_id
and bor_p.organization_id = p_organization_id
and bor_p.ALTERNATE_ROUTING_DESIGNATOR is null
and bos_p.routing_sequence_id = bor_p.common_routing_sequence_id
and bos_p.operation_seq_num = pitems_table(l_index).operation_seq_num
and nvl( bos_p.operation_type,1)=1; ---consider event only for flm routing 5676839
update BOM_CTO_MLSUPPLY_MAIN_TEMP
set --operation_seq_num = pitems_table(l_index).operation_seq_num,
OPERATION_LEAD_TIME_PERCENT = pitems_table(l_index).OPERATION_LEAD_TIME_PERCENT
where ITEM_INDEX = l_index;
select min(OPERATION_SEQ_NUM),sum(needed_item_qty)
into x_min_op_seq_num,l_cons_item_qty
FROM BOM_CTO_MLSUPPLY_MAIN_TEMP
WHERE parent_index = pitems_table(l_index).parent_index
AND item_id = pitems_table(l_index).item_id;
Update bom_cto_mlsupply_main_temp
set needed_item_qty = 0
where parent_index = pitems_table(l_index).parent_index
AND item_id = pitems_table(l_index).item_id
and Operation_seq_num <> x_min_op_seq_num;
Update bom_cto_mlsupply_main_temp
set needed_item_qty = l_cons_item_qty
where parent_index = pitems_table(l_index).parent_index
AND item_id = pitems_table(l_index).item_id
and Operation_seq_num = x_min_op_seq_num;