[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = g_pref_id_comp_short
and wpv.level_id = g_pref_level_id_site
and wpv.attribute_name = g_pref_val_comp_type_cset_att;
select bsd.seq_num || '.' || bsd.shift_num shift_id,
wip_ws_util.get_appended_date( bsd.shift_date, t.from_time) from_date,
wip_ws_util.get_appended_date( bsd.shift_date, t.to_time) to_date,
t.shift_num,
bsd.seq_num,
wip_ws_util.get_shift_info_for_display(mp.organization_id, bsd.seq_num, t.shift_num) as display
from mtl_parameters mp, bom_shift_dates bsd,
(select bst.calendar_code,
bst.shift_num,
min(bst.from_time) from_time,
max(decode(sign(bst.to_time - bst.from_time), -1, 24*60*60, 0) + bst.to_time) to_time
from bom_shift_times bst
group by bst.calendar_code, bst.shift_num ) t
where mp.organization_id = p_org_id
and mp.calendar_code = bsd.calendar_code
and bsd.calendar_code = t.calendar_code
and bsd.shift_num = t.shift_num
and bsd.exception_set_id = -1
and bsd.shift_date + t.to_time / (24*60*60) > sysdate
and bsd.shift_date between l_first_work_day and wip_ws_util.get_next_work_date_by_org_id(p_org_id,
wip_ws_util.get_next_work_date_by_org_id(p_org_id,l_first_work_day)) --fix bug 9484419
and bsd.seq_num is not null
order by to_date; --fix bug 9484419
* This procedure returns the string for all the component category selected in preferences for this org
*/
FUNCTION get_pref_comp_cat(p_org_id NUMBER) return VARCHAR2 IS
cat_string VARCHAR2(1048);
select wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = g_pref_id_comp_short
and wpv.level_id = g_pref_level_id_site
and wpv.attribute_name = g_pref_val_comp_type_cat_att
and wpv.sequence_number in (
select wpv1.sequence_number
from wip_preference_values wpv1
where wpv1.preference_id = g_pref_id_comp_short
and wpv1.level_id = g_pref_level_id_site
and wpv1.attribute_name = g_pref_val_comp_type_att
and wpv1.attribute_value_code = to_char(g_pref_val_comp_type_cat)
and wpv1.sequence_number in (
select wpv2.sequence_number
from wip_preference_values wpv2
where wpv2.preference_id = g_pref_id_comp_short
and wpv2.level_id = g_pref_level_id_site
and wpv2.attribute_name = g_pref_val_dtl_org_att
and wpv2.attribute_value_code = to_char(p_org_id)));
* This procedure returns the string for all the components selected in preference for this org
*/
FUNCTION get_pref_comp_id(p_org_id NUMBER) return VARCHAR2 IS
comp_string VARCHAR2(1048);
select wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = g_pref_id_comp_short
and wpv.level_id = g_pref_level_id_site
and wpv.attribute_name = g_pref_val_comp_type_item_att
and wpv.sequence_number in (
select wpv1.sequence_number
from wip_preference_values wpv1
where wpv1.preference_id = g_pref_id_comp_short
and wpv1.level_id = g_pref_level_id_site
and wpv1.attribute_name = g_pref_val_comp_type_att
and wpv1.attribute_value_code = to_char(g_pref_val_comp_type_item)
and wpv1.sequence_number in (
select wpv2.sequence_number
from wip_preference_values wpv2
where wpv2.preference_id = g_pref_id_comp_short
and wpv2.level_id = g_pref_level_id_site
and wpv2.attribute_name = g_pref_val_dtl_org_att
and wpv2.attribute_value_code = to_char(p_org_id)));
select msi.revision_qty_control_code,
msi.lot_control_code,
msi.serial_number_control_code
from mtl_system_items_b msi
where msi.organization_id = p_org_id
and msi.inventory_item_id = p_component_id;
fnd_msg_pub.Delete_Msg;
* This procedure inserts a component record in shortages temp table
*/
PROCEDURE insert_critical_component(p_org_id NUMBER,
p_inv_item_id NUMBER,
p_subinv_code VARCHAR2 ,
p_locator_id NUMBER,
p_avail_qty NUMBER) IS
BEGIN
insert into wip_ws_critical_comp_temp
(organization_id,
inventory_item_id,
supply_subinventory,
supply_locator_id,
onhand_qty,
projected_avail_qty
)values
(p_org_id,
p_inv_item_id,
p_subinv_code,
p_locator_id,
p_avail_qty,
p_avail_qty
);
END insert_critical_component;
FUNCTION is_all_component_selected(p_org_id NUMBER) RETURN BOOLEAN IS
CURSOR all_item_pref_csr IS
select wpv.attribute_value_code
from wip_preference_values wpv
where wpv.preference_id = g_pref_id_comp_short
and wpv.level_id = g_pref_level_id_site
and wpv.attribute_name = g_pref_val_comp_type_att
and wpv.sequence_number in (
select wpv1.sequence_number
from wip_preference_values wpv1
where wpv1.preference_id = g_pref_id_comp_short
and wpv1.level_id = g_pref_level_id_site
and wpv1.attribute_name = g_pref_val_dtl_org_att
and wpv1.attribute_value_code = to_char(p_org_id));
END is_all_component_selected;
* Call the procedure to insert the critical component into temp table
* It inserts a record for org component, and if subinv calc is selected in preference, then
* another record is inserted for subinv
*/
PROCEDURE get_pref_critical_components (p_org_id NUMBER, p_end_time DATE) IS
l_job_status_clause VARCHAR2(240);
l_sql := l_sql || 'select distinct wro.inventory_item_id, ';
if(is_all_component_selected(p_org_id)) then
l_all_clause := ' 1=1 ';
l_cat_clause := ' exists (select inventory_item_id ' ||
' from mtl_item_categories ' ||
' where inventory_item_id = wro.inventory_item_id '||
' and organization_id = wdj.organization_id '||
' and category_set_id = :cat_set_id '||
' and category_id in (:cat_ids))';
l_cat_clause := ' exists (select inventory_item_id ' ||
' from mtl_item_categories ' ||
' where inventory_item_id = wro.inventory_item_id '||
' and organization_id = wdj.organization_id '||
' and category_set_id = :cat_set_id '||
' and category_id in (select wpv.attribute_value_code '||
' from wip_preference_values wpv '||
' where wpv.preference_id = :pref_id_comp_short '||
' and wpv.level_id = :pref_level_id_site '||
' and wpv.attribute_name = :pref_val_comp_type_cat_att '||
' and wpv.sequence_number in ( '||
' select wpv1.sequence_number '||
' from wip_preference_values wpv1 '||
' where wpv1.preference_id = :pref_id_comp_short '||
' and wpv1.level_id = :pref_level_id_site '||
' and wpv1.attribute_name = :pref_val_comp_type_att '||
' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_cat) '||
' and wpv1.sequence_number in ( '||
' select wpv2.sequence_number '||
' from wip_preference_values wpv2 '||
' where wpv2.preference_id = :pref_id_comp_short '||
' and wpv2.level_id = :pref_level_id_site '||
' and wpv2.attribute_name = :pref_val_dtl_org_att '||
' and wpv2.attribute_value_code = to_char(wro.organization_id))) '||
' ))';
' select wpv.attribute_value_code ' ||
' from wip_preference_values wpv ' ||
' where wpv.preference_id = :pref_id_comp_short1 ' ||
' and wpv.level_id = :pref_level_id_site1 ' ||
' and wpv.attribute_name = :pref_val_comp_type_item_att1 ' ||
' and wpv.sequence_number in ( ' ||
' select wpv1.sequence_number ' ||
' from wip_preference_values wpv1 ' ||
' where wpv1.preference_id = :pref_id_comp_short2 ' ||
' and wpv1.level_id = :pref_level_id_site2 ' ||
' and wpv1.attribute_name = :pref_val_comp_type_att2 ' ||
' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_item2) ' ||
' and wpv1.sequence_number in ( ' ||
' select wpv2.sequence_number ' ||
' from wip_preference_values wpv2 ' ||
' where wpv2.preference_id = :pref_id_comp_short3 ' ||
' and wpv2.level_id = :pref_level_id_site3 ' ||
' and wpv2.attribute_name = :pref_val_dtl_org_att3 ' ||
' and wpv2.attribute_value_code = to_char(wro.organization_id))) )';
insert_critical_component(p_org_id, l_inv_item_id, null, null, l_comp_avail);
wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id||', onhand='||l_comp_avail );
insert_critical_component(p_org_id, l_inv_item_id, l_subinv_code, null, l_comp_avail);
wip_ws_util.trace_log( 'WIP_WS_SHORTAGE:get_pref_critical_components: shortage calc=subinv, back from insert_critical_component, org='||p_org_id||', item ='||l_inv_item_id);
* This procedure finds out the job ops to be considered based on timeline job statuses selected in preferences
* It stores the job ops in global pl/sql table for later use
*/
PROCEDURE get_job_ops(p_org_id NUMBER, p_end_time DATE) IS
l_job_status_clause VARCHAR2(240);
l_sql := l_sql || 'SELECT wo.organization_id, ';
l_sql := l_sql || 'select wro.inventory_item_id, ';
l_cat_clause := ' and exists (select inventory_item_id ' ||
' from mtl_item_categories ' ||
' where inventory_item_id = wro.inventory_item_id '||
' and organization_id = wro.organization_id '||
' and category_set_id = :cat_set_id '||
' and category_id in (:cat_ids))';
if(is_all_component_selected(p_org_id)) then
l_all_clause := ' 1=1 ';
l_cat_clause := ' exists (select inventory_item_id ' ||
' from mtl_item_categories ' ||
' where inventory_item_id = wro.inventory_item_id '||
' and organization_id = wro.organization_id '||
' and category_set_id = :cat_set_id '||
' and category_id in ( select wpv.attribute_value_code '||
' from wip_preference_values wpv '||
' where wpv.preference_id = :pref_id_comp_short '||
' and wpv.level_id = :pref_level_id_site '||
' and wpv.attribute_name = :pref_val_comp_type_cat_att '||
' and wpv.sequence_number in ( '||
' select wpv1.sequence_number '||
' from wip_preference_values wpv1 '||
' where wpv1.preference_id = :pref_id_comp_short '||
' and wpv1.level_id = :pref_level_id_site '||
' and wpv1.attribute_name = :pref_val_comp_type_att '||
' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_cat) '||
' and wpv1.sequence_number in ( '||
' select wpv2.sequence_number '||
' from wip_preference_values wpv2 '||
' where wpv2.preference_id = :pref_id_comp_short '||
' and wpv2.level_id = :pref_level_id_site '||
' and wpv2.attribute_name = :pref_val_dtl_org_att '||
' and wpv2.attribute_value_code = to_char(:l_org_id))) '||
' ))';
' select wpv.attribute_value_code ' ||
' from wip_preference_values wpv ' ||
' where wpv.preference_id = :pref_id_comp_short1 ' ||
' and wpv.level_id = :pref_level_id_site1 ' ||
' and wpv.attribute_name = :pref_val_comp_type_item_att1 ' ||
' and wpv.sequence_number in ( ' ||
' select wpv1.sequence_number ' ||
' from wip_preference_values wpv1 ' ||
' where wpv1.preference_id = :pref_id_comp_short2 ' ||
' and wpv1.level_id = :pref_level_id_site2 ' ||
' and wpv1.attribute_name = :pref_val_comp_type_att2 ' ||
' and wpv1.attribute_value_code = to_char(:pref_val_comp_type_item2) ' ||
' and wpv1.sequence_number in ( ' ||
' select wpv2.sequence_number ' ||
' from wip_preference_values wpv2 ' ||
' where wpv2.preference_id = :pref_id_comp_short3 ' ||
' and wpv2.level_id = :pref_level_id_site3 ' ||
' and wpv2.attribute_name = :pref_val_dtl_org_att3 ' ||
' and wpv2.attribute_value_code = to_char(wro.organization_id))) )';
select distinct
wor.wip_entity_id,
wor.operation_seq_num,
wor.resource_id,
nvl(wip_ws_dl_util.get_col_res_usage_req(wor.wip_entity_id, wor.operation_seq_num,wo.department_id, wor.resource_id, null),0) open_quantity,
wor.uom_code,
decode( wip_ws_time_entry.is_time_uom(wor.uom_code), 'Y',
inv_convert.inv_um_convert(-1,
38,
wor.usage_rate_or_amount,
wor.uom_code,
fnd_profile.value('BOM:HOUR_UOM_CODE'),
NULL,
NULL),
null) usage,
wor.applied_resource_units ,
wor.basis_type,
decode(wp.include_resource_efficiency, 1, nvl(bdr.efficiency, 1), 1) efficiency
from wip_operation_resources wor,
wip_operations wo,
wip_parameters wp,
bom_department_resources bdr
where wor.organization_id = p_org_id
and wor.wip_entity_id = p_wip_ent_id
and wor.operation_seq_num = p_op_seq_num
and wo.wip_entity_id = wor.wip_entity_id
and wo.organization_id = wor.organization_id
and wp.organization_id = wor.organization_id
and wo.operation_seq_num = wor.operation_seq_num
and bdr.resource_id = wor.resource_id
and bdr.department_id = nvl(wor.department_id, wo.department_id)
and wor.resource_id in (
select distinct to_number(wpv.attribute_value_code) resource_id
from wip_preference_values wpv
where wpv.preference_id = g_pref_id_res_short
and wpv.attribute_name = 'resource'
and wpv.level_id = 1
and wpv.sequence_number in (
select wpv_org.sequence_number
from wip_preference_values wpv_org
where wpv_org.preference_id = g_pref_id_res_short
and wpv_org.attribute_name = 'organization'
and to_number(wpv_org.attribute_value_code) = p_org_id))
order by resource_id;
select organization_id, resource_id, department_id from wip_ws_critical_res_temp;
insert into wip_ws_critical_res_temp
(organization_id,
resource_id,
department_id)
values
(
g_wip_job_critical_res_tbl(j).ORGANIZATION_ID,
g_wip_job_critical_res_tbl(j).RESOURCE_ID,
g_wip_job_critical_res_tbl(j).DEPARTMENT_ID
);
select wdj.organization_id,
wdj.primary_item_id inventory_item_id,
wdj.scheduled_completion_date receipt_date,
GREATEST(0, (wdj.start_quantity - wdj.quantity_completed
- wdj.quantity_scrapped)) item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 5 --wip supply
and mr.supply_source_header_id = wdj.wip_entity_id
and mr.organization_id = wdj.organization_id) reservation_qty,
wdj.wip_entity_id --added for bug 6886708 for logging
from wip_discrete_jobs wdj
where wdj.organization_id = p_org_id
and wdj.primary_item_id = p_inv_item_id
and trunc(wdj.scheduled_completion_date) = trunc(p_rcpt_date)
and wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
and (wdj.start_quantity - wdj.quantity_completed - wdj.quantity_scrapped) > 0
and wdj.job_type in (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD);
select SUM(GREATEST( 0, (wfs.planned_quantity - wfs.quantity_completed
- wfs.quantity_scrapped))) item_qty,
wfs.wip_entity_id --added for bug 6886708 for logging
from WIP_FLOW_SCHEDULES wfs
where wfs.status = 1
and wfs.SCHEDULED_FLAG = 1
and wfs.organization_id = p_org_id
and wfs.primary_item_id = p_inv_item_id
and trunc(wfs.scheduled_completion_date) = trunc(p_rcpt_date)
and (wfs.planned_quantity - wfs.quantity_completed - quantity_scrapped) > 0
and wfs.demand_source_header_id is null
and wfs.demand_source_line is null;
select SUM(-1*wro.required_quantity) item_qty
from wip_requirement_operations wro,
wip_discrete_jobs wdj
where wro.organization_id = p_org_id
and wro.inventory_item_id = p_inv_item_id
and trunc(wro.date_required) = trunc(p_rcpt_date)
and wro.organization_id = wdj.organization_id
and wro.wip_entity_id = wdj.wip_entity_id
and wro.wip_supply_type <> wip_constants.PHANTOM
and wro.required_quantity < 0
and wro.operation_seq_num > 0
and wdj.job_type in (WIP_CONSTANTS.STANDARD, WIP_CONSTANTS.NONSTANDARD)
and wdj.status_type IN (WIP_CONSTANTS.UNRELEASED, WIP_CONSTANTS.RELEASED,
WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD);
SELECT
SUM(MRP_HORIZONTAL_PLAN_SC.compute_daily_rate_t(dates.calendar_code, dates.exception_set_id,
sched.daily_production_rate, sched.quantity_completed,
sched.first_unit_completion_date, dates.calendar_date )) item_qty
FROM bom_calendar_dates dates,
mtl_parameters param,
wip_repetitive_schedules sched,
wip_repetitive_items rep_items
WHERE rep_items.primary_item_id = p_inv_item_id
and rep_items.organization_id = p_org_id
and rep_items.wip_entity_id = sched.wip_entity_id
and rep_items.line_id = sched.line_id
and sched.organization_id = rep_items.organization_id
and sched.status_type IN (WIP_CONSTANTS.UNRELEASED,
WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.COMP_CHRG, WIP_CONSTANTS.HOLD)
and dates.seq_num is not null
and TRUNC(dates.calendar_date) >= TRUNC(sched.first_unit_completion_date)
and TRUNC(dates.calendar_date)
<= (select trunc(cal.calendar_date - 1)
from bom_calendar_dates cal
where cal.exception_set_id = dates.exception_set_id
and cal.calendar_code = dates.calendar_code
and cal.seq_num = (select cal1.prior_seq_num + ceil(sched.processing_work_days)
from bom_calendar_dates cal1
where cal1.exception_set_id = dates.exception_set_id
and cal1.calendar_code = dates.calendar_code
and cal1.calendar_date = TRUNC(sched.first_unit_completion_date)) )
and dates.calendar_date = trunc(p_rcpt_date)
and dates.exception_set_id = param.calendar_exception_set_id
and dates.calendar_code = param.calendar_code
and param.organization_id = rep_items.organization_id;
SELECT
ms.to_org_primary_quantity item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 1 --po supply
and mr.supply_source_header_id = ms.po_header_id
and mr.supply_source_line_id = ms.po_line_id ) reservation_qty,
pd.PO_HEADER_ID --added for bug 6886708 for logging
FROM po_distributions_all pd,
mtl_supply ms
WHERE ms.item_id = p_inv_item_id
AND ms.to_organization_id = p_org_id
AND ( ms.supply_type_code = 'PO' or
ms.supply_type_code = 'ASN')
AND ms.destination_type_code = 'INVENTORY'
AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
AND pd.po_distribution_id = ms.po_distribution_id
AND ms.po_line_id is not null
AND ms.item_id is not null
AND ms.to_org_primary_quantity > 0
AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
WHERE ms.po_line_location_id = ODSS.line_location_id);
SELECT
(nvl(ms.to_org_primary_quantity,0) *
pd.req_line_quantity/prl.quantity) item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 18 --po req supply
and mr.supply_source_header_id = ms.req_header_id
and mr.supply_source_line_id = ms.req_line_id ) reservation_qty,
pd.requisition_line_id, --added for bug 6886708 for logging
prl.REQUISITION_HEADER_ID --added for bug 6886708 for logging
FROM po_req_distributions_all pd,
po_requisition_lines_all prl,
mtl_supply ms
WHERE ms.item_id = p_inv_item_id
AND ms.to_organization_id = p_org_id
AND ms.supply_type_code = 'REQ'
AND ms.destination_type_code = 'INVENTORY'
AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
AND pd.requisition_line_id = prl.requisition_line_id
AND prl.requisition_line_id = ms.req_line_id
AND ms.to_org_primary_quantity > 0
AND ms.req_line_id is not null
AND ms.item_id is not null
AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
WHERE ms.req_line_id = ODSS.requisition_line_id);
SELECT
SUM(nvl(ms.to_org_primary_quantity, 0) * pd.req_line_quantity/pl.quantity)
item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 18 --todo, need to check source type id
and mr.supply_source_header_id = ms.shipment_header_id
and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty,
pd.requisition_line_id, --added for bug 6886708 for logging
pl.REQUISITION_HEADER_ID --added for bug 6886708 for logging
FROM po_req_distributions_all pd,
po_requisition_lines_all pl,
mtl_supply ms
WHERE ms.item_id = p_inv_item_id
AND ms.to_organization_id = p_org_id
AND ms.supply_type_code = 'SHIPMENT'
AND ms.destination_type_code = 'INVENTORY'
AND pd.requisition_line_id = pl.requisition_line_id
AND pl.quantity > 0
AND pl.requisition_line_id = ms.req_line_id
AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
--AND ms.req_line_id is not null
AND ms.shipment_line_id is not null
AND ms.item_id is not null
AND ms.to_org_primary_quantity > 0;
SELECT
nvl(ms.TO_ORG_PRIMARY_QUANTITY, 0) * pd.req_line_quantity /
pl.quantity item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 18 --todo, need to check source type id
and mr.supply_source_header_id = ms.shipment_header_id
and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty
FROM po_requisition_lines_all pl,
po_req_distributions_all pd,
mtl_supply ms
WHERE ms.item_id = p_inv_item_id
AND ms.to_organization_id = p_org_id
AND ms.supply_type_code = 'RECEIVING'
AND ms.destination_type_code = 'INVENTORY'
AND pd.requisition_line_id = pl.requisition_line_id
AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
AND pl.quantity > 0
AND ms.req_line_id = pl.requisition_line_id
AND ms.po_distribution_id is null
AND ms.item_id is not null
AND ms.to_org_primary_quantity > 0
AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
WHERE ms.req_line_id = ODSS.requisition_line_id)
UNION ALL
SELECT
SUM(ms.to_org_primary_quantity) item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 18 --todo, need to check source type id
and mr.supply_source_header_id = ms.shipment_header_id
and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty
FROM mtl_secondary_inventories msub,
mtl_supply ms
WHERE ms.item_id = p_inv_item_id
AND ms.to_organization_id = p_org_id
AND ms.supply_type_code = 'RECEIVING'
AND ms.destination_type_code = 'INVENTORY'
AND ms.to_organization_id = msub.organization_id(+)
AND ms.to_subinventory = msub.secondary_inventory_name(+)
AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
AND ms.req_line_id is null
AND ms.po_distribution_id is null
AND ms.item_id is not null
AND ms.to_org_primary_quantity > 0;
SELECT
ms.to_org_primary_quantity item_qty,
(select sum(mr.reservation_quantity)
from mtl_reservations mr
where mr.supply_source_type_id = 18 --todo, need to check source type id
and mr.supply_source_header_id = ms.shipment_header_id
and mr.supply_source_line_id = ms.shipment_line_id ) reservation_qty,
pd.PO_HEADER_ID --added for bug 6886708 for logging
FROM po_distributions_all pd,
mtl_supply ms
WHERE ms.item_id = p_inv_item_id
AND ms.to_organization_id = p_org_id
AND ms.supply_type_code = 'RECEIVING'
AND ms.destination_type_code = 'INVENTORY'
AND trunc(ms.expected_delivery_date) = trunc(p_rcpt_date)
AND pd.po_distribution_id = ms.po_distribution_id
and ms.item_id is not null
AND ms.to_org_primary_quantity > 0
AND NOT EXISTS (select 'y' FROM OE_DROP_SHIP_SOURCES ODSS
WHERE ms.po_line_location_id = ODSS.line_location_id);
select inventory_item_id
from wip_ws_critical_comp_temp
where organization_id = p_org_id
and supply_subinventory is null;
update wip_ws_critical_comp_temp
set PROJECTED_AVAIL_QTY = PROJECTED_AVAIL_QTY + l_rcpt
where organization_id = p_org_id
and inventory_item_id = c_comp.inventory_item_id
and supply_subinventory is null;
* This procedure update the resource information in resource temp table
*/
PROCEDURE update_res_shift_avail(p_org_id NUMBER, p_dept_id NUMBER, p_resource_id NUMBER,
p_res_avail_date DATE, p_shift_num NUMBER,
p_onhand_qty NUMBER, p_proj_onhand NUMBER) IS
BEGIN
update
wip_ws_critical_res_temp
set
resource_avail_date = p_res_avail_date,
resource_shift_num = p_shift_num,
onhand_qty = p_onhand_qty,
projected_avail_qty = p_proj_onhand
where
organization_id = p_org_id and
department_id = p_dept_id and
resource_id = p_resource_id;
END update_res_shift_avail;
* Loops over the critical job op components pl/sql table and inserts each record into component
* shortage table
*/
PROCEDURE insert_components IS
BEGIN
wip_ws_util.log_time('insert_components: Inserting component shortage records');
wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:Entered: Number of records to insert='||g_wip_job_critical_comp_tbl.COUNT);
wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_components:inv_item='||g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID||
',org_id='||g_wip_job_critical_comp_tbl(i).ORGANIZATION_ID||
',wip_entity_id='||g_wip_job_critical_comp_tbl(i).WIP_ENTITY_ID||
',operation_seq_num='||g_wip_job_critical_comp_tbl(i).OPERATION_SEQ_NUM);
insert into wip_ws_comp_shortage(
ORGANIZATION_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
INVENTORY_ITEM_ID,
DEPARTMENT_ID,
PRIMARY_UOM_CODE,
DATE_REQUIRED,
REQUIRED_QTY,
QUANTITY_ISSUED,
QUANTITY_OPEN,
WIP_SUPPLY_TYPE,
SUPPLY_SUBINVENOTRY,
SUPPLY_LOCATOR_ID,
ONHAND_QTY,
PROJ_AVAIL_QTY,
SHORTAGE_QTY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
OBJECT_VERSION_NUMBER,
PROGRAM_RUN_DATE
)values(
g_wip_job_critical_comp_tbl(i).ORGANIZATION_ID,
g_wip_job_critical_comp_tbl(i).WIP_ENTITY_ID,
g_wip_job_critical_comp_tbl(i).OPERATION_SEQ_NUM,
g_wip_job_critical_comp_tbl(i).INVENTORY_ITEM_ID,
g_wip_job_critical_comp_tbl(i).DEPARTMENT_ID,
g_wip_job_critical_comp_tbl(i).PRIMARY_UOM_CODE,
g_wip_job_critical_comp_tbl(i).DATE_REQUIRED,
g_wip_job_critical_comp_tbl(i).REQUIRED_QTY,
g_wip_job_critical_comp_tbl(i).QUANTITY_ISSUED,
g_wip_job_critical_comp_tbl(i).QUANTITY_OPEN,
g_wip_job_critical_comp_tbl(i).WIP_SUPPLY_TYPE,
g_wip_job_critical_comp_tbl(i).SUPPLY_SUBINVENOTRY,
g_wip_job_critical_comp_tbl(i).SUPPLY_LOCATOR_ID,
g_wip_job_critical_comp_tbl(i).ONHAND_QTY,
g_wip_job_critical_comp_tbl(i).PROJ_AVAIL_QTY,
g_wip_job_critical_comp_tbl(i).SHORTAGE_QTY,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id,
g_request_id,
g_prog_appid,
g_prog_id,
g_init_obj_ver,
g_prog_run_date
);
wip_ws_util.log_time('insert_components: Done with inserting components');
END insert_components;
* Loops over the critical job op resources pl/sql table and inserts each record into resource
* shortage table
*/
PROCEDURE insert_resources IS
BEGIN
wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:Entered: Number of records to insert='||g_wip_job_critical_res_tbl.COUNT);
wip_ws_util.log_time('insert_resources: Inserting resource shortage records');
wip_ws_util.trace_log('WIP_WS_SHORTAGE:insert_resources:resource'||g_wip_job_critical_res_tbl(i).RESOURCE_ID||
',org_id='||g_wip_job_critical_res_tbl(i).ORGANIZATION_ID||
',wip_entity_id='||g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID||
',operation_seq_num='||g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM);
insert into wip_ws_res_shortage(
ORGANIZATION_ID,
WIP_ENTITY_ID,
OPERATION_SEQ_NUM,
RESOURCE_ID,
DEPARTMENT_ID,
DATE_REQUIRED,
REQUIRED_QTY,
QUANTITY_ISSUED,
QUANTITY_OPEN,
RESOURCE_AVAIL,
RESOURCE_PROJ_AVAIL,
RESOURCE_SHORTAGE,
PRIMARY_UOM_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
OBJECT_VERSION_NUMBER,
PROGRAM_RUN_DATE
)values(
g_wip_job_critical_res_tbl(i).ORGANIZATION_ID,
g_wip_job_critical_res_tbl(i).WIP_ENTITY_ID,
g_wip_job_critical_res_tbl(i).OPERATION_SEQ_NUM,
g_wip_job_critical_res_tbl(i).RESOURCE_ID,
g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
g_wip_job_critical_res_tbl(i).DATE_REQUIRED,
g_wip_job_critical_res_tbl(i).REQUIRED_QTY,
g_wip_job_critical_res_tbl(i).QUANTITY_ISSUED,
g_wip_job_critical_res_tbl(i).QUANTITY_OPEN,
g_wip_job_critical_res_tbl(i).RESOURCE_AVAIL,
g_wip_job_critical_res_tbl(i).RESOURCE_PROJ_AVAIL,
g_wip_job_critical_res_tbl(i).RESOURCE_SHORTAGE,
g_wip_job_critical_res_tbl(i).PRIMARY_UOM_CODE,
sysdate,
g_user_id,
sysdate,
g_user_id,
g_login_id,
g_request_id,
g_prog_appid,
g_prog_id,
g_init_obj_ver,
g_prog_run_date
);
wip_ws_util.log_time('insert_resources: Done with resource insertion');
END insert_resources;
* Delete all component records from comp shortage table for a given org
*/
PROCEDURE delete_components (p_org_id NUMBER) IS
BEGIN
wip_ws_util.log_time('delete_components: Starting to delete org components');
delete from wip_ws_comp_shortage
where organization_id = p_org_id;
wip_ws_util.log_time('delete_components: Done with deleting org components');
END delete_components;
* Delete all resource records from res shortage table for a given org
*/
PROCEDURE delete_resources(p_org_id NUMBER) IS
BEGIN
wip_ws_util.log_time('delete_resources: Starting to delete org resources');
delete from wip_ws_res_shortage
where organization_id = p_org_id;
wip_ws_util.log_time('delete_resources: Done with deleting org resources');
END delete_resources;
delete_components(p_org_id);
wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_components');
insert_components;
wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with insert_components');
delete_resources(p_org_id);
wip_ws_util.trace_log('WIP_WS_SHORTAGE:write_db:done with delete_resources');
insert_resources;
select department_id,
resource_id,
onhand_qty,
projected_avail_qty,
resource_avail_date,
resource_shift_num
from wip_ws_critical_res_temp
where organization_id = p_org_id
and department_id = p_dept_id
and resource_id = p_res_id;
wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 80:Calling update in temp table');
update_res_shift_avail(p_org_id, g_wip_job_critical_res_tbl(i).DEPARTMENT_ID,
g_wip_job_critical_res_tbl(i).RESOURCE_ID, trunc(current_res_req_date),
g_wip_job_critical_res_tbl(i).SHIFT_NUM, l_res_onhand_qty, l_res_remain_qty);
wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_res_shortage:Point 90:Done update in temp table');
select rowid,
organization_id,
inventory_item_id,
supply_subinventory,
nvl(onhand_qty,0) onhand_qty,
nvl(projected_avail_qty,0) projected_avail_qty
from wip_ws_critical_comp_temp
where organization_id = p_org_id
and inventory_item_id = l_inv_item_id
and nvl(supply_subinventory, 'NULL') = nvl(l_supply_subinv, 'NULL');
update wip_ws_critical_comp_temp
set projected_avail_qty = l_item_project_avail_qty
where organization_id = p_org_id
and inventory_item_id = l_inv_item_id
and nvl(supply_subinventory, 'NULL') = nvl(l_supply_subinv, 'NULL');
wip_ws_util.trace_log('WIP_WS_SHORTAGE:calc_comp_shortage:Point 100, total critical comp to be inserted='||g_wip_job_critical_comp_tbl.COUNT);