[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 : |
| August 14, 99 Angela Makalintal Initial version |
| February 26, 01 Shashi Bhaskaran bugfix 1642355 |
| May 7, 01 Sajani Sheth Support for partial FAS |
| June 16, 01 Shashi Bhaskaran bugfix 1835357 |
| Replaced fnd_file calls with oe_debug_pub |
| August 16,2001 Kiran Konada, fix for bug#1874380 |
| to support ATO item under a PTO. |
| item_type_code for an ato item under PTO |
| is 'OPTION' and top_model_line_id will NOT be|
| null, UNLIKE an ato item order, where |
| item_type_code = 'Standard' and |
| top_model_lined_id is null |
| This fix has actually been provided in |
| branched code 115.15.1155.4 |
| |
| |
| 08/29/2001 Renga Kannan |
| Modified the code for Porcuring config |
| This batch program should not pick up |
| the Buy config/ATO item orders |
| This check is added to get_order_lines |
| procedure. |
| |
| 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. |
| |
| Feb 18, 02 Shashi Bhaskaran Fixed bug 2227841 |
| Performance: Removed call to GET_NOTINV_QTY and |
| GET_RESERVED_QTY from get_order_lines main cursor|
| and added soon after fetching the cursor. |
| |
| Feb 27, 02 Shashi Bhaskaran Fixed bug 2243672 |
| Set the org context using OM's API |
| |
| Jun 05, 02 Shashi Bhaskaran Fixed bug 2388802 |
| Because of the earlier fix (2227841), the cursor |
| picked up the non-eligbile rows and locked them. |
| Removed the for update clause from the cursor and|
| locked it for the real eligible row. |
| |
| Oct 24, 02 Kundan Sarkar Fixed bug 2628896 |
| To propagate fix 2420381 from branch to main |
| |
| Oct 25, 02 Kundan Sarkar Bugfix 2644849 (2620282 in br)|
| Sales order not seen in LOV while doing WIP |
| completion as revision info is not passed |
| while reserving the sales order against work |
| order. |
| |
| Dec 06, 02 Kundan Sarkar Bugfix 2698837 (2681321 in br)|
| Not creating work order in shipping org if the |
| item is sourced from a different org and sourcing|
| rule is "TRANSFER FROM". |
| |
| May 07, 03 Kundan Sarkar Bugfix 2930170 |
| May 09, 03 (2868148 and 2946071 in br) |
| 2868148: |
| Considering supply from Flow Schedule before |
| creating supply through AFAS to prevent multiple|
| work order creation. |
| 2946071: |
| Need to handle null condition when flow_supply |
| returns NO_DATA_FOUND |
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.
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
|24-SEP-2003 : Kiran Konada
| Chnages for patchset-J
| with mutiple sources enhancement ,
| expected error from query sourcing org has been removed
| source_type =66 refers to mutiple sourcing
|
| statements after call to query org has been modified to look at
| source type =66 instead of expected error status
|
|
|19-Nov-2003 : Kiran Konada
| bugfix 2885568
| There was a full table scan on wip_discrete_jobs
| unique index present on wip_enity_id and organization_id
| hece, joined oe_order_lines_all and got the ship_from _org_id
|
| original query
| select dj.wip_entity_id, we.wip_entity_name
| into l_wip_entity_id, l_job_name
| from wip_discrete_jobs dj, wip_entities we|
| where dj.wip_entity_id = we.wip_entity_id
| and dj.source_line_id = l_line_id
| and dj.source_code = 'WICDOL'
|
| Changed query
|
| added follwoing where clause
|
| and oel.line_id = l_line_id
| and dj.primary_item_id = oel.inventory_item_id
| and oel.ship_from_org_id = dj.organization_id ;
| ** This cursor has been split. The new approach is to insert the data into a temp table using the same sql statement
| ** without the workflow conditions and then filter the data with the additional conditions for work flow status.
| ** The cursor c_work_order_eligible will now be using bom_cto_order_lines_gt, wf_item_activity_statuses
| ** and wf_process_activities tables.
| */
/*=============================================================================*/
PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
UPDATE oe_order_lines_all oel
SET oel.program_id = null
WHERE oel.line_id = p_line_id
AND oel.program_id = -99;
x_orders_loaded - Number of rows inserted into interface table
x_wip_seq i - Group ID in interface table
x_message_name - Error message name
x_message_text - Error message text
Description: This function inserts a record into the
WIP_JOB_SCHEDULE_INTERFACE table for the creation of
work orders through AutoCreate FAS in OM.
*****************************************************************************/
FUNCTION get_order_lines(p_org_id IN NUMBER,
p_offset_days IN NUMBER,
p_load_type IN NUMBER,
p_class_code IN varchar2,
p_status_type IN NUMBER,
p_order_number IN NUMBER,
p_line_id IN NUMBER,
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,
--Flow ER 14595064
p_create_flow_schedule IN NUMBER DEFAULT 2,
x_orders_loaded OUT NoCopy NUMBER,
x_wip_seq OUT NoCopy NUMBER,
x_message_name OUT NoCopy VARCHAR2,
x_message_text OUT NoCopy VARCHAR2
)
return integer
IS
l_x_hold_result_out VARCHAR2(1);
l_rows_selected NUMBER := 0;
l_rows_inserted NUMBER := 0;
Select Eligible Records based on parameters and workflow status.
****************************************************************/
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('get_order_lines: ' || 'Begin Get Order Lines.', 1);
sql_stmt := 'select /*+ ordered index (WAS WF_ITEM_ACTIVITY_STATUSES_PK) */ '||
' oel.line_id line_id, '||
' oel.ship_from_org_id ship_from_org_id, '||
' oel.header_id header_id, '||
' nvl(oel.org_id,-1) org_id, ' ||
' oel.ato_line_id ato_line_id, '||
' oel.inventory_item_id inventory_item_id '||
'from '||
' oe_order_lines_all OEL, '||
' wf_item_activity_statuses WAS, '||
' wf_process_activities WPA, '||
' mtl_system_items MSI ';
sql_stmt := 'select /*+ ordered index (WPA WF_PROCESS_ACTIVITIES_N1) index (WAS WF_ITEM_ACTIVITY_STATUSES_N1) */ '||
' oel.line_id line_id, '||
' oel.ship_from_org_id ship_from_org_id, '||
' oel.header_id header_id, '||
' nvl(oel.org_id,-1) org_id, ' ||
' oel.ato_line_id ato_line_id, '||
' oel.inventory_item_id inventory_item_id '||
'from '||
' wf_process_activities WPA, '||
' wf_item_activity_statuses WAS, '||
' oe_order_lines_all OEL, '||
' mtl_system_items MSI ';
'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 oel.header_id in (select oeh.header_id '||
'from oe_order_headers_all oeh ' ||
'where oeh.order_number = :l_order_number) ';
'(select oelc.line_id '||
' from oe_order_lines_all oelc '||
' where (oelc.ato_line_id = :l_line_id '||--5108885
'and oelc.item_type_code = ''CONFIG'') '||
'or (oelc.line_id = :l_line_id '||
'and oelc.item_type_code = ''STANDARD'' ' ||
'and oelc.top_model_line_id is null) '||
--Adding INCLUDED item type code for SUN ER#9793792
'or (oelc.line_id = :l_line_id '||
'and oelc.ato_line_id = oelc.line_id '||
'and oelc.item_type_code = ''INCLUDED'') ' ||
'or (oelc.line_id = :l_line_id '|| -- ATO item within PTO
'and oelc.ato_line_id = oelc.line_id '||
'and oelc.item_type_code = ''OPTION'')) '; -- fix for bug#1874380
'(select CAL.CALENDAR_DATE '||
'from bom_calendar_dates cal, '||
'mtl_parameters mp '||
'where mp.organization_id = oel.ship_from_org_id '||
'and cal.calendar_code = mp.calendar_code '||
'and cal.exception_set_id = mp.calendar_exception_set_id '||
'and cal.seq_num = '||
'(select cal2.prior_seq_num '||
'- nvl(:p_offset_days, 0) '||
'- (ceil(nvl(msi.fixed_lead_time,0) '||
'+ nvl(msi.variable_lead_time,0) '||
'* (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 ) '||
'- CTO_WIP_WRAPPER.GET_RESERVED_QTY(oel.line_id)) )) '||
--bugfix 3034619: added parenthesis
--bugfix 2074290: convert the OQ and then
-- subtract from get_reserved_qty
'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))) ';
we have selected. The drive_mark variable tells us which parameters
we are using, so we are sure to send the right ones to SQL.
*/
if (drive_mark = 0) then
-- No (optional) parameter is passed
Open WorkOrder FOR sql_stmt;
--l_rows_selected := l_rows_selected + 1;
SELECT ship_from_org_id
INTO l_ship_from_org_id
FROM OE_ORDER_LINES_ALL
WHERE line_id = WorkOrder_Rec.ato_line_id --- bug fix 5207010 . We should lock based on ATO line id
and (GET_NOTINV_QTY(WorkOrder_Rec.line_id) - GET_RESERVED_QTY(WorkOrder_Rec.line_id)) > 0
FOR UPDATE NOWAIT; -- bugfix 2388802: lock the row which is really eligible
OE_DEBUG_PUB.add ('get_order_lines: ' || 'Could not lock line id '|| to_char(WorkOrder_Rec.line_id) ||' for update.');
l_rows_selected := l_rows_selected + 1; --Bugfix 6146803
l_rows_selected := l_rows_selected - 1;
l_rows_selected := l_rows_selected - 1;
--The sequence will be generated just before calling insert_wip_interface.
/*
if (l_rows_selected - l_rows_errored = 1) then
select wip_job_schedule_interface_s.nextval
into x_wip_seq
from dual;
select nvl(oel.dep_plan_required_flag,'N')
into l_dep_plan_flag
from oe_order_lines_all oel
where oel.line_id = WorkOrder_Rec.line_id;
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(WorkOrder_Rec.line_id)
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 schedule_ship_date
into l_schedule_ship_date
from oe_order_lines_all
where line_id = WorkOrder_Rec.line_id;
-- Check if the line has any flow schedules. Update the line status accordingly.
if CTO_WORKFLOW.Flow_Sch_Exists(to_number(WorkOrder_Rec.line_id)) then
IF PG_DEBUG <> 0 THEN
oe_debug_pub.add('get_order_lines: Flow_Sch_Exists. Updating flow status code to PRODUCTION_OPEN ' ||
'for line_id:'|| WorkOrder_Rec.line_id || ' header_id:' || WorkOrder_Rec.header_id);
OE_Order_WF_Util.Update_Flow_Status_Code(p_header_id => WorkOrder_Rec.header_id,
p_line_id => to_number(WorkOrder_Rec.line_id),
p_flow_status_code => 'PRODUCTION_OPEN',
x_return_status => l_x_return_status);
select wip_job_schedule_interface_s.nextval
into x_wip_seq
from dual;
CTO_WIP_UTIL.insert_wip_interface(
p_line_id => WorkOrder_Rec.line_id,
p_wip_seq => x_wip_seq,
p_status_type => p_status_type,
p_class_code => p_class_code,
p_conc_request_id => p_conc_request_id,
p_conc_program_id => p_conc_program_id,
p_conc_login_id => p_conc_login_id,
p_user_id => p_user_id,
p_appl_conc_program_id => p_appl_conc_program_id,
x_return_status => l_x_return_status,
x_error_message => l_x_error_message,
x_message_name => l_x_message_name);
oe_debug_pub.add('get_order_lines: ' || 'Insert Error Message: ' || l_x_error_message,1);
OE_DEBUG_PUB.add('get_order_lines: ' || 'FAILED in Insert WIP Interface: ' || l_x_return_status ||
'For Order Line ' || to_char(WorkOrder_Rec.line_id), 1);
OE_DEBUG_PUB.add('get_order_lines: ' || 'Success in Insert WIP Interface: ' || l_x_return_status ||
'For Order Line ' || to_char(WorkOrder_Rec.line_id), 1);
l_rows_inserted := l_rows_inserted + 1;
end if; /* end of insert into wip_job_schedule_interface*/
OE_DEBUG_PUB.add('get_order_lines: ' || 'Deliveries not planned, not inserting into wjsi', 2);
OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Order Lines Selected: ' ||
to_char(l_rows_selected), 1);
OE_DEBUG_PUB.add('get_order_lines: ' || 'Number of Order Lines Inserted: ' ||
to_char(l_rows_inserted), 1);
x_orders_loaded := l_rows_inserted;
select dj.wip_entity_id, we.wip_entity_name
into l_wip_entity_id, l_job_name
from wip_discrete_jobs dj, wip_entities we,
oe_order_lines_all oel
where dj.wip_entity_id = we.wip_entity_id
and dj.source_line_id = l_line_id
and dj.source_code = 'WICDOL'
--bugfix 2885568 to remove full table scan on wip_discrete_jobs
--use unique index of wip_enitity _id and organization_id
and oel.line_id = l_line_id
and dj.primary_item_id = oel.inventory_item_id --for using index wdj_N1
and oel.ship_from_org_id = dj.organization_id ;--for using index wdj_u1
OE_DEBUG_PUB.add ('get_order_lines: ' || 'Could not lock line id '|| to_char(p_line_id) ||' for update.',1);
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.
*****************************************************************************/
FUNCTION reserve_wo_to_so(p_wip_seq IN NUMBER,
p_message_text VARCHAR2,
p_message_name VARCHAR2
)
RETURN integer
IS
-- WorkOrder_Rec number;
/* Cursor to select records to reserve */
cursor c_wip_job_records is
select mso.sales_order_id,
oel.line_id,
oel.ship_from_org_id,
oel.inventory_item_id,
oel.order_quantity_uom,
--oel.ordered_quantity,
oel.source_document_type_id, -- bugfix 1799874: to check if it is an internal SO or regular
INV_CONVERT.inv_um_convert -- bugfix 1661094: added conversion logic
(oel.inventory_item_id,
5, -- bugfix 2204376: pass precision of 5
wei.start_quantity,
msi.primary_uom_code,
oel.order_quantity_uom,
null,
null) start_quantity,
inv_reservation_global.g_source_type_wip,
wei.wip_entity_id,
oel.schedule_ship_date,
-- Passing revision info only if revision_qty_control_code
-- is not equal to 1
-- wei.bom_revision
-- 2620282: Selecting bom revision info
decode( nvl(msi.revision_qty_control_code , 1 ) , 1, NULL , wei.bom_revision) bom_revision,
oel.org_id -- bugfix 3014000
from wip_job_schedule_interface wei,
oe_order_lines_all oel,
mtl_sales_orders mso,
oe_order_headers_all oeh,
--oe_order_types_v oet
oe_transaction_types_tl oet,
mtl_system_items msi -- bugfix 1661094:
where wei.group_id = p_wip_seq
and wei.source_line_id = oel.line_id
and oeh.header_id = oel.header_id
and oet.transaction_type_id = oeh.order_type_id
and mso.segment1 = to_char(oeh.order_number)
and mso.segment2 = oet.name
and oet.language = (select language_code
from fnd_languages
where installed_flag = 'B')
and mso.segment3 = lSourceCode
and wei.load_type = WIP_CONSTANTS.CREATE_JOB
and wei.organization_id = oel.ship_from_org_id
and wei.process_phase = WIP_CONSTANTS.ML_COMPLETE
-- bug 9314772.added warning status jobs for creating reservations.pdube
-- and wei.process_status = WIP_CONSTANTS.COMPLETED -- 3202934
and wei.process_status IN (WIP_CONSTANTS.COMPLETED,WIP_CONSTANTS.WARNING)
and msi.inventory_item_id = oel.inventory_item_id
and msi.organization_id = oel.ship_from_org_id;
update wip_discrete_jobs
set status_type = 7 -- CANCELLED
where wip_entity_id = WorkOrder_Rec.wip_entity_id;
update wip_job_schedule_interface
set process_phase = WIP_CONSTANTS.ML_VALIDATION,
process_status = WIP_CONSTANTS.RUNNING
where wip_entity_id = WorkOrder_Rec.wip_entity_id
and group_id = p_wip_seq;
select status_type
into l_status_type
from wip_job_schedule_interface
where group_id = p_wip_seq
and source_line_id = WorkOrder_Rec.line_id
and last_update_date <> creation_date
and rownum = 1;
INV_RESERVATION_PUB.delete_reservation
(
p_api_version_number => 1.0
, p_init_msg_lst => fnd_api.g_true
, x_return_status => l_status
, x_msg_count => l_msg_count
, x_msg_data => l_msg_txt
, p_rsv_rec => l_rsv_rec
, p_serial_number => l_dummy_sn
);
oe_debug_pub.add('reserve_wo_to_so: ' || 'Return Status from delete reservation: ' || l_status,1);
oe_debug_pub.add('reserve_wo_to_so: ' || 'Msg Txt from delete reservation: ' || l_msg_txt,1);
update wip_discrete_jobs
set status_type = 7 -- CANCEL
where wip_entity_id = WorkOrder_Rec.wip_entity_id;
update wip_job_schedule_interface
set process_phase = WIP_CONSTANTS.ML_VALIDATION,
process_status = WIP_CONSTANTS.RUNNING
where wip_entity_id = WorkOrder_Rec.wip_entity_id
and group_id = p_wip_seq;
UPDATE oe_order_lines_all
SET program_id = null
WHERE line_id = WorkOrder_Rec.line_id
AND program_id = -99;
-- unlock the lines. Update the program_id to null.
update oe_order_lines_all
set program_id = null
where program_id = -99
and line_id in (select wei.source_line_id
from wip_job_schedule_interface wei
where wei.group_id = p_wip_seq);
select nvl(sum(INV_CONVERT.inv_um_convert
(oel.inventory_item_id,
5, -- bugfix 2204376: pass precision of 5
mr.reservation_quantity,
mr.reservation_uom_code,
msi.primary_uom_code,
null,
null)),0)
from mtl_reservations mr,
oe_order_headers_all oeh,
oe_order_lines_all oel,
mtl_system_items msi --bugfix 2074290: added msi
where oel.line_id = pLineId
and oel.header_id = oeh.header_id
and mr.demand_source_line_id = oel.line_id
and mr.organization_id = oel.ship_from_org_id
and oel.inventory_item_id = msi.inventory_item_id --bugfix 2074290: added joins
and oel.ship_from_org_id = msi.organization_id
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
select nvl(sum(wjs.start_quantity), 0)
from wip_job_schedule_interface wjs,
oe_order_lines_all oel
where oel.line_id = pLineId
and wjs.source_line_id = oel.line_id
and (wjs.process_status = WIP_CONSTANTS.PENDING
or wjs.process_status = WIP_CONSTANTS.RUNNING
or wjs.process_status = WIP_CONSTANTS.COMPLETED)
--Bugfix 12397938
and wjs.source_code = 'WICDOL';
select nvl(sum(planned_quantity - quantity_completed),0) -- 2946071
from wip_flow_schedules
where demand_source_line = to_char(pLineId)
and demand_source_type = inv_reservation_global.g_source_type_oe;
select decode( max(shipping_interfaced_flag),
'N', max(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)),
'Y', nvl(sum(wdd.requested_quantity), 0) )
into l_quantity
from WSH_DELIVERY_DETAILS_OB_GRP_V wdd,
oe_order_lines_all oel,
mtl_system_items msi
where oel.line_id = pLineId
and oel.inventory_item_id = msi.inventory_item_id
and oel.ship_from_org_id = msi.organization_id
and wdd.source_line_id(+) = oel.line_id
and wdd.source_code(+) = 'OE'
and nvl(wdd.inv_interfaced_flag(+),'N') = 'N'
and nvl(released_status(+),'N') <> 'D';