[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| insert_wip_interface - inserts a record into |
| WIP_JOB_SCHEDULE_INTERFACE for |
| WIP_MASS_LOAD to create work orders |
| |
| To Do: Handle Errors. Need to discuss with Usha and Girish what |
| error information to include in Notification. |
| |
| HISTORY : |
| June 7, 99 Angela Makalintal Initial version |
| May 7, 01 Sajani Sheth Support for partial FAS |
| Sep 14, 01 Shashi Bhaskaran Fixed bug 1988967 |
| While selecting from wsh_delivery_details |
| we should check source_code='OE' |
| Sep 26, 01 Shashi Bhaskaran Fixed bug 2017099 |
| Check with ordered_quantity(OQ) instead of OQ-CQ |
| where CQ=cancelled_quantity. When a line is |
| is canceled, OQ gets reflected. |
| |
| Oct 24, 01 Shashi Bhaskaran Fixed bug 2074290 |
| Convert the ordered_quantity into Primary UOM for|
| comparing with get_reserved_qty. |
| |
| Oct 25, 02 Kundan Sarkar Bugfix 2644849 (2620282 in br)|
| Insert bom revision info in |
| wip_job_schedule_interface |
|
| DEC 12, 2002 Kiran Konada
| Added code for ML SUPPLy fetaure
|
| Sep 23, 2003 Renga Kannan |
| Changed the following two table acecss to |
| view. This change is recommended by shipping |
| team to avoid getting inbound/dropship lines.
| WSH_NEW_DELIVERIES to WSH_NEW_DELIVERIES_OB_GRP_V
WSH_DELIVERY_DETAILS to WSH_DELIVERY_DETAILS_OB_GRP_V
This changes brings a wsh dependency to our code
the wsh pre-req for this change is 3125046
| June 1, 05 Renga Kannann Added nocopy hint
=============================================================================*/
-- Global constant holding the package name
G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_ORDER_BOOK_UTIL';
Procedure: insert_wip_interface
Parameters: p_model_line_id - line id of the configuration item in
oe_order_lines_all
p_wip_seq - group id to be used in interface table
x_error_message - error message if insert fails
x_message_name - name of error message if insert
fails
Description: This function inserts a record into the
WIP_JOB_SCHEDULE_INTERFACE table for the creation of
work orders.
*****************************************************************************/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
PROCEDURE insert_wip_interface(
p_line_id 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
--ml supply var's
l_mlsupply_parameter number := 0;
insert_error exception;
select sum(wdd.requested_quantity) pQuantity,
-- Note: bug 1661094: wdd.requested_quantity is in primary uom
wda.delivery_id, wdd.load_seq_number lsn
from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
wsh_delivery_assignments wda
where wdd.source_line_id = p_line_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and wdd.source_code = 'OE' -- bugfix 1988967: only OE lines should be picked since
-- wsh_delivery_details can have lines related to
-- containers (source_code=WSH)
group by wdd.load_seq_number, wda.delivery_id
order by wda.delivery_id, wdd.load_seq_number;
select nvl(oel.dep_plan_required_flag, 'N')
into lDepPlanFlag
from oe_order_lines_all oel
where oel.line_id = p_line_id;
oe_debug_pub.add('insert_wip_interface: ' || 'Partial qty for WO creation (in primary UOM) : '||to_char(l_partial_qty), 2);
oe_debug_pub.add('insert_wip_interface: ' || 'Dep Plan Flag for line_id '||to_char(p_line_id)||' is '||lDepPlanFlag, 2);
SELECT ENABLE_LOWER_LEVEL_SUPPLY
INTO l_mlsupply_parameter
FROM bom_parameters bp,
oe_order_lines_all oel
WHERE oel.line_id = p_line_id
AND oel.ship_from_org_id = bp.organization_id;
oe_debug_pub.add('insert_wip_interface: ' || 'enavle lower level supply value is '|| l_mlsupply_parameter , 4);
oe_debug_pub.add('insert_wip_interface: ' || 'Before call to create_subassembly_jobs with enable lower supply param '|| l_mlsupply_parameter , 4);
oe_debug_pub.add ('insert_wip_interface: ' || 'failed after get_wroking_day' || l_return_status ,1);
oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
oe_debug_pub.add ('insert_wip_interface: ' || ' failed after call to get_working_day' || l_return_status ,1);
oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
oe_debug_pub.add('insert_wip_interface: ' || 'success from create_Subassembly_jobs ' ,1);
oe_debug_pub.add('insert_wip_interface: ' || l_error_message ,1);
-- Removed the decode stmt for status type column in the insert
l_stmt_num := 150;
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,
source_line_id,
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,
demand_class,
start_quantity,
bom_revision_date,
routing_revision_date,
project_id,
task_id,
due_date,
bom_revision /* 2620282 : Insert bom revision info */
)
select SYSDATE, /* Last_Updated_Date */
p_user_id, /* Last_Updated_By */
SYSDATE, /* Creation_Date */
p_user_id, /* Created_By */
p_conc_login_id, /* Last_Update_Login */
p_conc_request_id, /* Request_ID */
p_conc_program_id, /* Program_ID */
p_appl_conc_program_id, /* Program_Application_ID */
SYSDATE, /* Last Update Date */
p_wip_seq, /* group_id */
'WICDOL', /* source_code */
oel.line_id, /* source line id */
WIP_CONSTANTS.ML_VALIDATION, /* process_phase */
WIP_CONSTANTS.PENDING, /* process_status */
oel.ship_from_org_id, /* organization id */
WIP_CONSTANTS.CREATE_JOB, /* Load_Type */
nvl(p_status_type, WIP_CONSTANTS.UNRELEASED),/* Status_Type */
oel.schedule_ship_date, /* Date Completed */
oel.inventory_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 */
oel.demand_class_code, /* Demand Class */
l_partial_qty, /* Start Quantity: (in primary uom) */ --bugfix 2074290
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 */
oel.project_id, /* Project_ID */
oel.task_id, /* Task_ID */
oel.schedule_ship_date,
BOM_REVISIONS.get_item_revision_fn
( 'ALL',
'ALL',
oel.ship_from_org_id,
oel.inventory_item_id,
(trunc (greatest(nvl(cal.calendar_date,SYSDATE),
SYSDATE),'MI')+1/(60*24) )
) /* 2620282 : Insert bom revision info */
from bom_calendar_dates cal,
mtl_parameters mp,
wip_parameters wp,
mtl_system_items msi,
oe_order_lines_all oel
where oel.line_id = p_line_id
and mp.organization_id = oel.ship_from_org_id
and wp.organization_id = mp.organization_id
and msi.organization_id = oel.ship_from_org_id
and msi.inventory_item_id = oel.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_partial_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(oel.schedule_ship_date)
);
oe_debug_pub.add('insert_wip_interface: ' || 'Number of Rows Inserted in WJSI: ' || to_char(SQL%ROWCOUNT));
raise INSERT_ERROR ;
oe_debug_pub.add('insert_wip_interface: ' || 'line_id = '||to_char(p_line_id), 2);
oe_debug_pub.add('insert_wip_interface: ' || 'delivery_id = '||to_char(lNextRec.delivery_id), 2);
oe_debug_pub.add('insert_wip_interface: ' || 'lsn = '||to_char(lNextRec.lsn), 2);
oe_debug_pub.add('insert_wip_interface: ' || 'Qty = '||to_char(lNextRec.pQuantity), 2);
oe_debug_pub.add('insert_wip_interface: ' || 'l_current_qty::'||to_char(l_current_qty), 2);
oe_debug_pub.add('insert_wip_interface: ' || 'New l_current_qty::'||to_char(l_current_qty), 2);
SELECT ENABLE_LOWER_LEVEL_SUPPLY
INTO l_mlsupply_parameter
FROM bom_parameters bp,
oe_order_lines_all oel
WHERE oel.line_id = p_line_id
AND oel.ship_from_org_id = bp.organization_id;
oe_debug_pub.add ('insert_wip_interface: ' || 'failed after get_wroking_day' || l_return_status ,1);
oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
oe_debug_pub.add ('insert_wip_interface: ' || ' failed after call to get_working_day' || l_return_status ,1);
oe_debug_pub.add ('insert_wip_interface: ' || 'error message' || l_error_message ,1);
oe_debug_pub.add('insert_wip_interface: ' || 'success from get_working_day ' ,1);
oe_debug_pub.add('insert_wip_interface: ' || l_error_message ,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,
source_line_id,
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,
demand_class,
start_quantity,
bom_revision_date,
routing_revision_date,
project_id,
task_id,
due_date,
delivery_id,
build_sequence,
bom_revision /* 2620282 : Insert bom revision info */
)
select SYSDATE, /* Last_Updated_Date */
p_user_id, /* Last_Updated_By */
SYSDATE, /* Creation_Date */
p_user_id, /* Created_By */
p_conc_login_id, /* Last_Update_Login */
p_conc_request_id, /* Request_ID */
p_conc_program_id, /* Program_ID */
p_appl_conc_program_id, /* Program_Application_ID */
SYSDATE, /* Last Update Date */
p_wip_seq, /* group_id */
'WICDOL', /* source_code */
oel.line_id, /* source line id */
WIP_CONSTANTS.ML_VALIDATION, /* process_phase */
WIP_CONSTANTS.PENDING, /* process_status */
oel.ship_from_org_id, /* organization id */
WIP_CONSTANTS.CREATE_JOB, /* Load_Type */
nvl(p_status_type, WIP_CONSTANTS.UNRELEASED),/* Status_Type */
oel.schedule_ship_date, /* Date Completed */
oel.inventory_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 */
oel.demand_class_code, /* Demand Class */
INV_CONVERT.inv_um_convert(oel.inventory_item_id, --item_id
5, -- bugfix 2204376: pass precision of 5
l_current_qty,
oel.order_quantity_uom, --from uom
msi.primary_uom_code, --to uom
null, --from name
null --to name
), /* start 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 */
oel.project_id, /* Project_ID */
oel.task_id, /* Task_ID */
oel.schedule_ship_date,
lNextRec.delivery_id,
lNextRec.lsn,
BOM_REVISIONS.get_item_revision_fn
( 'ALL',
'ALL',
oel.ship_from_org_id,
oel.inventory_item_id,
(trunc (greatest(nvl(cal.calendar_date,SYSDATE),
SYSDATE),'MI')+1/(60*24) )
) /* 2620282 : Insert bom revision info */
from bom_calendar_dates cal,
mtl_parameters mp,
wip_parameters wp,
mtl_system_items msi,
oe_order_lines_all oel
where oel.line_id = p_line_id
and mp.organization_id = oel.ship_from_org_id
and wp.organization_id = mp.organization_id
and msi.organization_id = oel.ship_from_org_id
and msi.inventory_item_id = oel.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) *
INV_CONVERT.inv_um_convert -- bugfix 1661094:
(oel.inventory_item_id, -- added conversion logic
5, -- bugfix 2204376: pass precision of 5
l_current_qty,
oel.order_quantity_uom,
msi.primary_uom_code,
null,
null)
))))
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(oel.schedule_ship_date)
);
oe_debug_pub.add('insert_wip_interface: ' || 'Number of Rows Inserted in WJSI for departure planned : ' ||
to_char(SQL%ROWCOUNT),1);
raise INSERT_ERROR ;
oe_debug_pub.add('insert_wip_interface: ' || 'Qty of wo created::'||to_char(l_wo_created_qty),2);
oe_debug_pub.add('insert_wip_interface: ' || 'Exiting out of partial qty loop',2);
x_error_message := 'CTOWIPUB.insert wip interface expected excpn: ';
oe_debug_pub.add('insert_wip_interface: ' || ' expected excpn: ' || x_error_message,1);
x_error_message := 'CTOWIPUB.insert wip interface N expected excpn: '|| ':' ||
substrb(sqlerrm,1,100) ;
oe_debug_pub.add('insert_wip_inetrface: ' || ' UN expected excpn: ' || x_error_message,1);
x_error_message := 'CTOWIPUB.insert_wip_interface raised no-data-found: '|| ':' ||
substrb(sqlerrm,1,100);
oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
when INSERT_ERROR then
x_return_status := FND_API.G_RET_STS_ERROR;
x_error_message := 'CTOWIPUB.insert_wip_interface raised INSERT_ERROR:' || to_char(l_stmt_num)|| ':' ||
substrb(sqlerrm,1,100);
oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
x_error_message := 'CTOWIPUB.insert_wip_interface raised OTHERS excpn: ' || to_char(l_stmt_num)|| ':' ||
substrb(sqlerrm,1,100) ;
oe_debug_pub.add('insert_wip_interface: ' || x_error_message,1);
, 'insert_wip_interface'
);
END insert_wip_interface;
select 1
into l_eligible_line
from oe_order_lines_all oel,
mtl_customer_items mci
where oel.line_id = p_line_id
and oel.ordered_item_id = mci.customer_item_id (+)
and ((oel.item_identifier_type <> 'CUST')
or (oel.item_identifier_type = 'CUST'
and mci.dep_plan_prior_bld_flag <> 'Y')
or (validate_delivery_id(to_number(p_line_id)) = 1));
select 1
into l_eligible_line
from oe_order_lines_all oel
where exists (select 'Exists'
from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
wsh_delivery_assignments wda,
mtl_customer_items mci
where wdd.source_line_id = oel.line_id
and mci.customer_item_id = oel.ordered_item_id
and wda.delivery_detail_id = wdd.delivery_detail_id
and mci.dep_plan_prior_bld_flag = 'Y'
and oel.shipping_interfaced_flag = 'Y'
and wda.delivery_id is not NULL
and wdd.source_code = 'OE' -- bugfix 1988967
)
and oel.line_id = p_line_id;
select 'IMPORTED'
into l_imported
from oe_order_lines_all oel,
WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
mtl_system_items msi --bugfix 2074290: added msi
where oel.line_id = p_line_id
and wdd.source_line_id = oel.line_id
and oel.inventory_item_id = msi.inventory_item_id --bugfix 2074290: added joins
and oel.ship_from_org_id = msi.organization_id
and wdd.source_code = 'OE' -- bugfix 1988967
-- begin bugfix 2074290: convert OQ to primary uom since WDD stores requested qty in primary uom
and INV_CONVERT.inv_um_convert
(oel.inventory_item_id,
5, -- bugfix 2204376: pass precision of 5
oel.ordered_quantity,
oel.order_quantity_uom,
msi.primary_uom_code,
null,
null) = (select nvl(sum(wdd1.requested_quantity), 0) -- bugfix 2017099
--end bugfix 2074290
from WSH_DELIVERY_DETAILS_OB_GRP_V wdd1
where wdd1.source_line_id = oel.line_id
and wdd1.source_code = 'OE') --bugfix 1988967
and rownum = 1;
select 'NOTASSIGNED'
into l_assigned
from oe_order_lines_all oel,
WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
wsh_delivery_assignments wda
where oel.line_id = p_line_id
and wdd.source_line_id = oel.line_id
and wdd.source_code = 'OE' --bugfix 1988967
--and oel.ordered_quantity - nvl(oel.cancelled_quantity, 0) = (select nvl(sum(wdd1.requested_quantity), 0)
--from wsh_delivery_details wdd1
--where wdd1.source_line_id = oel.line_id)
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id is null
and rownum = 1;
select 'NOTPLANNED'
into l_planned
from oe_order_lines_all oel,
WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
wsh_delivery_assignments wda,
WSH_NEW_DELIVERIES_OB_GRP_V wnd
where oel.line_id = p_line_id
and wdd.source_line_id = oel.line_id
and wdd.source_code = 'OE' --bugfix 1988967
and wda.delivery_detail_id = wdd.delivery_detail_id
and wda.delivery_id = wnd.delivery_id
and nvl(wnd.planned_flag,'N') = 'N'
and rownum=1;