[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| added a call to Update_Flow_Status_Code in check_supply_
| creation_wf
|
| 11/04/2003 Kiran Konada
| added a call to display_wf_status instead of
| Update_Flow_Status_Code in check_supply_
| creation_wf
|
| 11/14/2003 Kiran Konada
| removed procedu set_parameter_lead_time_wf
| bcos of bug#3202825
|
|
|
| 12/11/03 Sushant Sawant
| removed update_flow_status_code calls in create_config_item_wf
| and added display_wf_status
|
|
| 01/19/04 Sushant Sawant
| Fixed Bug 3380730, 3380874 to provide proper status code on config line
| and corrected use of cto_workflow_api_pk.display_wf_status
|
|
| 01/23/04 Sushant Sawant
| Fixed Bug 3388135 to provide message for match in case of dropship models.
|
| 02/23/2004 Sushant Sawant fixed Bug 3419221
| New LINE_FLOW_STATUS code 'SUPPLY_ELIGIBLE' was introduced.
| Config Lines with Internal and External source types will be assigned this status.
| when the config line reached check supply creation workflow activity.
|
|
| 03/01/2004 Kiran Konada
| Bugfix 2318060
| 'N' value to BUILD_IN_WIP_FLAG is caught as expected error
| when workflow moves through set_parameter_work_order node
|
|
| 04/06/2004 KKONADA removed fullstop after BOM , bugfix#3554874
|
| 05/10/2004 Sushant Sawant
| fixed bug 3548069 in procedure validate_line.
| model lines with schedule ship date null should not
| be picked for config creation.
|
| 04/04/2005 Renga Kannan
| Fixed bug 4197665 in procedure check_supply_type_wf
| The to_char function had a wrong parameter in it.
| It was erroring out only in 10G instance. Fixed it.
|
|
| 06/01/2005 Renga Kannan
|
| Added nocopy Hint to all out parameters.
|
|
|
| 06/16/2005 Kiran Konada :
| chnaged for OPM project
| change comment : OPM
| check_supply_type:
| check for p_source_type in (1,66) is replaced by
| l_can_create_supply = N
| check_cto_can_create_supply new parameters l_sourcing_org
| and l_message
|
| check_supply_creation api
| check_cto_can_create_supply new parameters l_sourcing_org
| and l_message
|
|
| 07/05/2005 Renga Kannan
| Changed for MOAC project
| Code change for ONT RESERVATION TIME FENCE
|
|
|
| 18-AUG-2005 Kiran Konada
| bugfix#4556596
| when Check_supply_type_wf node detects that
| multiple sources are present workflow moves
| shipline. But there is no call to display_wf_status
| so line status remains at "supply eligible"
| Fix is to call display_wf_status
|
|
|
=============================================================================*/
G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_WORKFLOW';
-- The exception block for the following select statement is added by Renga Kannan
-- on 12/14/00. This part of the bug fix # 1381938
-- When the start Model workflow is called and it is having configuration item
-- in Oe_order_lines_all, it can be becasue of two things. One is the Auto create config
-- batch program is running and the other is due to link_item
-- In the case of Auto create config process we will get the program_id value in bcol table.
-- But in the case of link item the data may not be there in bcol table at all. So in the
-- case of link_item in the when_no_data_found exception we will set the l_program_id value to 0.
-- This will move the model work flow as well as the config line workflow.
begin
select program_id
into l_program_id
from bom_cto_order_lines
where line_id = to_number(p_itemkey);
select line_id
into l_config_line_id
from oe_order_lines_all
where ato_line_id = to_number(p_itemkey)
and item_type_code = 'CONFIG';
select ato_line_id
, order_quantity_uom , ordered_quantity -- added by sushant for reservation
, schedule_ship_date -- added by sushant for reservation
, source_type_code -- added by sushant for drop ship
into lTopAtoLineId
, l_reservation_uom_code , l_quantity_to_reserve
, l_schedule_ship_date
, v_source_type_code
from oe_order_lines_all
where line_id = to_number(p_itemkey);
select oeh.order_number , msi.segment1, oel.line_number || '.' || oel.shipment_number
, msi.inventory_item_id , msi.organization_id
into v_order_number, v_top_model_name, v_top_model_line_num
, v_top_model_id, v_ship_from_org_id
from oe_order_headers_all oeh , oe_order_lines_all oel , mtl_system_items msi
where oeh.header_id = oel.header_id
and oel.line_id = lTopAtoLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id ;
CTO_CONFIG_BOM_PK.g_t_dropped_item_type.delete ;
select oeh.order_number , msi.segment1, oel.line_number || '.' || oel.shipment_number
into v_order_number, v_top_model_name, v_top_model_line_num
from oe_order_headers_all oeh , oe_order_lines_all oel , mtl_system_items msi
where oeh.header_id = oel.header_id
and oel.line_id = lTopAtoLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id ;
select bcol.config_item_id,
bcol.inventory_item_id,
bcol.ship_from_org_id,
perform_match -- Sushant added this to check full item match
into l_config_id,
l_model_id,
l_mfg_org_id,
l_perform_match -- Sushant added this to check full item match
from bom_cto_order_lines bcol
where bcol.line_id = to_number(p_itemkey);
select line_id, header_id, inventory_item_id
into l_config_line_id, l_header_id, l_config_item_id
from oe_order_lines_all
where ato_line_id = to_number(p_itemkey)
and item_type_code = 'CONFIG';
select msi.segment1, oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' )
|| '.' || nvl(component_number , '' )
into v_top_config_name, v_top_config_line_num
from oe_order_lines_all oel , mtl_system_items msi
where oel.ato_line_id = lTopAtoLineId
and item_type_code = 'CONFIG'
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id ;
select msi.segment1, oel.line_number || '.' || oel.shipment_number || '.' || nvl( oel.option_number , '' )
|| '.' || nvl(component_number , '' )
into v_top_config_name, v_top_config_line_num
from oe_order_lines_all oel , mtl_system_items msi
where oel.ato_line_id = lTopAtoLineId
and item_type_code = 'CONFIG'
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id ;
select segment1
into l_config_item_name
from mtl_system_items
where inventory_item_id = l_config_id
and organization_id = l_mfg_org_id ;
select subinventory
from oe_order_lines
where line_id = p_top_model_line_id;
select msi.primary_uom_code
into l_primary_uom_code
from mtl_system_items msi
where msi.inventory_item_id = p_config_id
and msi.organization_id = p_mfg_org_id;
select segment1
into l_config_item_name
from mtl_system_items
where inventory_item_id = p_config_id
and organization_id = p_mfg_org_id ;
select organization_name into l_organization_name
from mtl_organizations
where organization_id = p_mfg_org_id ;
select organization_name into l_organization_name
from inv_organization_name_v
where organization_id = p_mfg_org_id ;
select segment1
into l_config_item_name
from mtl_system_items
where inventory_item_id = p_config_id
and organization_id = p_mfg_org_id ;
select organization_name into l_organization_name
from mtl_organizations
where organization_id = p_mfg_org_id ;
select organization_name into l_organization_name
from inv_organization_name_v
where organization_id = p_mfg_org_id ;
select segment1
into l_config_item_name
from mtl_system_items
where inventory_item_id = p_config_id
and organization_id = p_mfg_org_id ;
SELECT item_type_code, ato_line_id , header_id, line_id
INTO v_item_type_code, v_ato_line_id, v_header_id , v_config_line_id
FROM oe_order_lines_all
WHERE line_id = p_itemkey;
--below code calls display_wf_status to update the correct
--before booking and scheduling if item is reserved
--
l_stmt_num := 100;
select reservation_id
into lReserveId
from mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where mr.demand_source_line_id = oel.line_id --ato item line id
and oel.line_id = pLineId
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code='ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = mr.demand_source_header_id
--and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe) --bugfix 1799874
and mr.reservation_quantity > 0
and supply_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_inv
and rownum = 1;
select count(*)
into lRsvCount
from mtl_reservations mr,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where mr.demand_source_line_id = oel.line_id --ato item line id
and oel.line_id = pLineId
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code='ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = mr.demand_source_header_id
--and mr.demand_source_type_id = INV_RESERVATION_GLOBAL.g_source_type_oe
and mr.demand_source_type_id = decode(oeh.source_document_type_id, 10,
INV_RESERVATION_GLOBAL.g_source_type_internal_ord,
INV_RESERVATION_GLOBAL.g_source_type_oe)
and mr.reservation_quantity > 0
and rownum = 1;
select count(*)
into lFloCount
from wip_flow_schedules
where demand_source_type = inv_reservation_global.g_source_type_oe
and demand_source_line = to_char(pLineId)
and status <> 2; -- Flow Schedule status : 1 = Open 2 = Closed/Completed
select oel.inventory_item_id, oel.ship_from_org_id,
oel.header_id,
--(oel.ordered_quantity - oel.cancelled_quantity) --bugfix 2017099
oel.ordered_quantity
into l_config_item_id, l_mfg_org_id, l_header_id,
l_qty
from oe_order_lines_all oel
where oel.line_id = to_number(p_itemkey);
OE_Order_WF_Util.Update_Flow_Status_Code(
p_header_id => l_header_id,
p_line_id => to_number(p_itemkey),
p_flow_status_code => l_flow_status_code,
x_return_status => l_return_status);
select oel.ato_line_id
into l_top_ato_line_id
from oe_order_lines_all oel
where oel.line_id = to_number(p_itemkey);
select oel.ato_line_id
into lAtoLineId
from oe_order_lines_all oel
where line_id = to_number(p_itemkey);
select inventory_item_id, ship_from_org_id,item_type_code, source_type_code
into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code
from oe_order_lines_all
where line_id = to_number(p_itemkey)
and ato_line_id is not null;
select NVL(cfm_routing_flag,2)
into l_supply_type
from oe_order_lines_all oel,
bom_operational_routings bor
where oel.line_id = to_number(p_itemkey)
and oel.inventory_item_id = bor.assembly_item_id (+)
and oel.ship_from_org_id = bor.organization_id (+)
and bor.alternate_routing_designator (+) is NULL;
select oel.ordered_quantity --bufix 2017099
into l_quantity
from oe_order_lines_all oel
where oel.line_id = to_number(p_itemkey)
and exists (select '1'
from bom_operational_routings bor
where bor.assembly_item_id = oel.inventory_item_id
and bor.organization_id = oel.ship_from_org_id
and bor.alternate_routing_designator is null
and nvl(bor.cfm_routing_flag, 2) = 1)
and not exists (select '1'
from mtl_reservations mr
where mr.demand_source_line_id = oel.line_id
and mr.organization_id = oel.ship_from_org_id
--and mr.demand_source_type_id = inv_reservation_global.g_source_type_oe
and mr.demand_source_type_id =
decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and mr.reservation_quantity > 0);
select schedule_ship_date, header_id
into l_sch_ship_date, l_header_id
from oe_order_lines_all oel
where line_id = to_number(p_itemkey);
OE_Order_WF_Util.Update_Flow_Status_Code(
p_header_id => l_header_id,
p_line_id => to_number(p_itemkey),
p_flow_status_code => 'PRODUCTION_OPEN',
x_return_status => l_x_return_status);
SELECT '1' into l_dummy
FROM oe_order_lines_all
WHERE line_id = p_line_id
FOR UPDATE NOWAIT;
UPDATE oe_order_lines_all oel
SET oel.program_id = -99
WHERE oel.line_id = p_line_id
AND nvl(oel.program_id, 0) <> -99;
OE_DEBUG_PUB.add ('lock_line_id: ' || 'CTO_WORKFLOW.Lock_Line_Id: Could not lock line id '|| p_line_id ||' for update.');
UPDATE oe_order_lines_all oel
SET oel.program_id = null
WHERE oel.line_id = p_line_id
AND nvl(oel.program_id, 0) = -99;
-- different sessions. If this api is called after SELECT FOR UPDATE, then it will fail.
--
-- begin bugfix 2105156: Call lock_line_Id to manually lock the row if possible.
-- Lock_Line_Id API will update the program_id in oeol to -99.
-- if you cannot, raise RECORD_LOCKED exception
--
Lock_Line_Id ( to_number(p_itemkey), l_result );
select oel.ordered_quantity, -- bugfix 2017099
oel.ship_from_org_id,
oel.ato_line_id,--5108885
oel.ato_line_id, oel.line_id,oel.top_model_line_id, oel.item_type_code --fix for bug#1874380
into l_quantity, l_mfg_org_id, l_afas_line_id, l_ato_line_id, l_line_id,
l_top_model_line_id,l_item_type_code
from mtl_system_items msi,
oe_order_lines_all oel
where oel.line_id = to_number(p_itemkey)
and (oel.open_flag is null
or oel.open_flag = 'Y')
and oel.ordered_quantity > 0
and oel.inventory_item_id = msi.inventory_item_id
and msi.organization_id = oel.ship_from_org_id
and oel.schedule_status_code = 'SCHEDULED'
and oel.booked_flag = 'Y'
and oel.ato_line_id is not null
--and oel.shipping_interfaced_flag = 'Y'
and msi.replenish_to_order_flag = 'Y'
and msi.pick_components_flag = 'N'
and msi.build_in_wip_flag = 'Y'
and msi.bom_item_type = 4
/*----------------------------------+
ATO items do not have to have
a base model.
and msi.base_item_id is not NULL
+-----------------------------------*/
and not exists
(select '1'
from oe_order_lines_all oel2
where oel2.ship_from_org_id = oel.ship_from_org_id
and oel2.header_id = oel.header_id
and oel2.line_id = oel.line_id
and rownum = 1
and WIP_ATO_UTILS.check_wip_supply_type(
oel2.header_id,
oel2.line_id,
NULL,
oel2.ship_from_org_id)
not in (0,1)
)
and not exists
(select '1'
from bom_operational_routings bor
where bor.assembly_item_id = oel.inventory_item_id
and bor.organization_id = oel.ship_from_org_id
and bor.alternate_routing_designator is null
and nvl(bor.cfm_routing_flag, 2) = 1)
and not exists
(select '1'
from mtl_reservations mr
where mr.demand_source_line_id = oel.line_id
and mr.organization_id = oel.ship_from_org_id
--and mr.demand_source_type_id = inv_reservation_global.g_source_type_oe
and mr.demand_source_type_id =
decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and mr.reservation_quantity > 0)
FOR UPDATE OF oel.line_id NOWAIT; --bugfix 2053360
p_itemkey ||' for update.');
SELECT build_in_wip_flag
INTO l_build_in_wip
FROM mtl_system_items mtl,
Oe_order_lines_all oel
WHERE oel.line_id = to_number(p_itemkey)
AND oel.inventory_item_id = mtl.inventory_item_id
AND oel.ship_from_org_id = mtl.organization_id;
-- An update is needed again in case of retry activity. During the first run of AFAS, if there is
-- an error, the program_id is updated to null. When the AFAS activity is retried, the line
-- doesn't get picked up because the program_id is null.
UPDATE oe_order_lines_all
SET program_id = -99
WHERE line_id = to_number(p_itemkey)
AND NVL(program_id, 0) <> -99;
oe_debug_pub.add ('submit_conc_prog_wf: ' || 'updated program_id to -99');
DELETE FROM WIP_INTERFACE_ERRORS
WHERE INTERFACE_ID IN (
SELECT INTERFACE_ID
FROM WIP_JOB_SCHEDULE_INTERFACE
WHERE source_line_id = to_number(p_itemkey)
AND PROCESS_PHASE = 4
AND PROCESS_STATUS = 4);
oe_debug_pub.add ('submit_conc_prog_wf: ' || 'Rows deleted from wie:' || l_cnt);
DELETE FROM WIP_JOB_SCHEDULE_INTERFACE I
WHERE source_line_id = to_number(p_itemkey)
AND I.PROCESS_PHASE = 4
AND I.PROCESS_STATUS = 4;
oe_debug_pub.add ('submit_conc_prog_wf: ' || 'Rows deleted from wjsi:' || l_cnt);
Select line details to make sure the model line is valid.
+------------------------------------------------------------*/
select 1
into l_valid_model_line
from oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = p_line_id
and msi.organization_id = oel.ship_from_org_id
and msi.inventory_item_id = oel.inventory_item_id
and msi.bom_item_type = 1
--and msi.build_in_wip_flag = 'Y'
and msi.replenish_to_order_flag = 'Y'
and oel.open_flag = 'Y'
and (oel.cancelled_flag = 'N'
or oel.cancelled_flag is null)
and ( oel.booked_flag = 'Y' or v_aps_version >= 10 )
and schedule_ship_date is not null /* Fixed bug 3548069 */
and (
( oel.schedule_status_code = 'SCHEDULED'
and oel.source_type_code = 'INTERNAL'
--
-- Bug 14474393
-- Commenting this check since as per OM team the condition based on
-- schedule status code should be enough as this flag is controlled by
-- the profile OM: Bypass ATP
--
-- and oel.visible_demand_flag = 'Y'
)
OR ( oel.source_type_code = 'EXTERNAL' )
) ; /* BUG#2234858 additional changes Made by sushant for Drop Ship */
Select line details to make sure the config line is valid.
+------------------------------------------------------------*/
select 1
into l_valid_config_line
from oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = p_config_line_id
and msi.organization_id = oel.ship_from_org_id
and oel.inventory_item_id = msi.inventory_item_id
and msi.bom_item_type = 4
and msi.build_in_wip_flag = 'Y'
and msi.replenish_to_order_flag = 'Y'
and oel.open_flag = 'Y'
and (oel.cancelled_flag = 'N'
or oel.cancelled_flag is null)
--
-- Bug 14474393
-- Commenting this check since as per OM team the condition based on
-- schedule status code should be enough as this flag is controlled by
-- the profile OM: Bypass ATP
--
-- and oel.visible_demand_flag = 'Y'
and oel.booked_flag = 'Y'
and oel.schedule_status_code = 'SCHEDULED'
and ( oel.item_type_code = 'CONFIG' OR
--Adding INCLUDED item type code for SUN ER#9793792
( oel.item_type_code in ('STANDARD','OPTION','INCLUDED') AND --bugfix#2111718
oel.ato_line_id = p_config_line_id ) );
select 1
into l_config_item
from oe_order_lines_all oelM,
oe_order_lines_all oelC
where oelM.line_id = p_model_line_id
and oelC.ato_line_id = oelM.line_id
and oelC.item_type_code = 'CONFIG';
select sum(nvl(mrs.reservation_quantity,0))
into x_reserved_quantity
from mtl_system_items msi,
oe_order_lines_all oel,
mtl_reservations mrs
where oel.line_id = p_config_line_id
and oel.open_flag = 'Y'
--and (oel.ordered_quantity - oel.cancelled_quantity) > 0
and oel.ordered_quantity > 0 -- bugfix 2017099
and oel.inventory_item_id = msi.inventory_item_id
and msi.organization_id = oel.ship_from_org_id
and oel.item_type_code = 'CONFIG'
and oel.schedule_status_code = 'SCHEDULED'
and oel.booked_flag = 'Y'
and (oel.cancelled_flag = 'N'
or oel.cancelled_flag is null)
and msi.replenish_to_order_flag = 'Y'
and msi.pick_components_flag = 'N'
and msi.bom_item_type = 4
and msi.base_item_id is not NULL
and mrs.demand_source_line_id = oel.line_id
and mrs.demand_source_header_id is not NULL
and mrs.organization_id = oel.ship_from_org_id
--and mrs.demand_source_type_id = inv_reservation_global.g_source_type_oe
and mrs.demand_source_type_id =
decode (l_source_document_type_id, 10, inv_reservation_global.g_source_type_internal_ord,
inv_reservation_global.g_source_type_oe ) -- bugfix 1799874
and mrs.supply_source_type_id =
inv_reservation_global.g_source_type_inv
and mrs.reservation_quantity > 0
group by oel.line_id;
select wip_entity_id
into lWipEntityId
from wip_flow_schedules wfs,
oe_order_lines_all oel,
oe_order_headers_all oeh,
oe_transaction_types_all ota,
oe_transaction_types_tl otl,
mtl_sales_orders mso
where wfs.demand_source_line = to_char(pLineId) --config line id
and oel.line_id = pLineId
and oeh.header_id = oel.header_id
and oeh.order_type_id = ota.transaction_type_id
and ota.transaction_type_code='ORDER'
and ota.transaction_type_id = otl.transaction_type_id
and oeh.order_number = mso.segment1
and otl.name = mso.segment2
and otl.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.sales_order_id = wfs.demand_source_header_id
and oel.inventory_item_id = wfs.primary_item_id
and rownum = 1;
select ato_line_id
into l_ato_line_id
from oe_order_lines_all
where line_id = p_itemkey;
select inventory_item_id, ship_from_org_id,item_type_code, source_type_code,header_id
into l_inventory_item_id, l_ship_from_org_id,l_item_type_code, l_source_type_code,l_header_id
from oe_order_lines_all
where line_id = to_number(p_itemkey)
and ato_line_id is not null;