[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION build_insert(p_columns IN SelectColumnTabType) RETURN VARCHAR2 IS
l_insert_str inv_mwb_globals.long_str;
l_procedure_name := 'BUILD_INSERT';
l_insert_str := 'INSERT INTO mtl_mwb_gtmp (';
l_insert_str := l_insert_str||l_temp;
l_insert_str := l_insert_str || ' ) ';
RETURN l_insert_str;
END build_insert;
p_select_columns IN SelectColumnTabType
,p_from_clause IN SQLClauseTabType
,p_where_clause IN SQLClauseTabType
,p_group_clause IN SQLClauseTabType
) RETURN VARCHAR2 IS
l_query_str inv_mwb_globals.very_long_str;
IF p_select_columns.COUNT > 0 THEN
i := p_select_columns.FIRST;
IF i = p_select_columns.FIRST THEN
l_query_str := 'SELECT ';
l_temp := p_select_columns(i).column_value||' '||p_select_columns(i).column_name;
l_temp := ' , '||p_select_columns(i).column_value||' '||p_select_columns(i).column_name;
i := p_select_columns.NEXT(i);
/* delete from rtest1;
insert into rtest1 values(l_query_str);
post_query; -- Updates id to names.
l_query_str := build_insert(g_onhand_select);
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
'moqd.lpn_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
'moqd.cost_group_id';
SELECT revision_qty_control_code,
lot_control_code,
serial_number_control_code
INTO l_rev_control,
l_lot_control,
l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
AND organization_id = inv_mwb_globals.g_tree_organization_id;
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
'moqd.cost_group_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
'moqd.lpn_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
'moqd.status_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
'moqd.cost_group_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
'moqd.lpn_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
'moqd.status_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
'moqd.lpn_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
'moqd.cost_group_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.REVISION).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'moqd.subinventory_code';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'moqd.locator_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LPN_ID).column_value :=
'moqd.lpn_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.CG_ID).column_value :=
'moqd.cost_group_id';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.STATUS_ID).column_value :=
'moqd.status_id';
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
SELECT revision_qty_control_code,
lot_control_code,
serial_number_control_code
INTO l_rev_control,
l_lot_control,
l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
AND organization_id = inv_mwb_globals.g_tree_organization_id;
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
SELECT lot_control_code,
serial_number_control_code
INTO l_lot_control,
l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
AND organization_id = inv_mwb_globals.g_tree_organization_id;
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
SELECT revision_qty_control_code,
serial_number_control_code
INTO l_rev_control,
l_serial_control
FROM mtl_system_items
WHERE organization_id = inv_mwb_globals.g_tree_organization_id
AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
SELECT revision_qty_control_code,
serial_number_control_code
INTO l_rev_control,
l_serial_control
FROM mtl_system_items
WHERE organization_id = inv_mwb_globals.g_tree_organization_id
AND inventory_item_id = inv_mwb_globals.g_tree_item_id;
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.revision).column_value :=
'moqd.revision';
inv_mwb_query_manager.g_onhand_select(inv_mwb_query_manager.LOT).column_value :=
'moqd.lot_number';
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
g_onhand_1_select,
g_onhand_1_from,
g_onhand_1_where,
g_onhand_1_group
);
l_query_str := build_insert(g_inbound_select);
g_inbound_select,
g_inbound_from,
g_inbound_where,
g_inbound_group
);
g_inbound_1_select,
g_inbound_1_from,
g_inbound_1_where,
g_inbound_1_group
);
l_query_str := build_insert(g_receiving_select);
g_receiving_select,
g_receiving_from,
g_receiving_where,
g_receiving_group
);
g_receiving_1_select,
g_receiving_1_from,
g_receiving_1_where,
g_receiving_1_group
);
l_query_str := build_insert(g_union_select);
g_union_select,
g_null_clause,
g_null_clause,
g_null_clause
);
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
g_inbound_select,
g_inbound_from,
g_inbound_where,
g_inbound_group
);
g_receiving_select,
g_receiving_from,
g_receiving_where,
g_receiving_group
);
g_null_select,
g_null_clause,
g_null_clause,
g_union_group
);
SELECT MATURITY_DATE
,HOLD_DATE
,SUPPLIER_LOT
,PARENT_LOT
,DOCUMENT_TYPE
,DOCUMENT_TYPE_ID
,DOCUMENT_NUMBER
,DOCUMENT_LINE_NUMBER
,RELEASE_NUMBER
,PO_RELEASE_ID
,RELEASE_LINE_NUMBER
,SHIPMENT_NUMBER
,SHIPMENT_HEADER_ID_INTERORG
,ASN
,SHIPMENT_HEADER_ID_ASN
,TRADING_PARTNER
,VENDOR_ID
,TRADING_PARTNER_SITE
,VENDOR_SITE_ID
,FROM_ORG
,FROM_ORG_ID
,TO_ORG
,TO_ORG_ID
,EXPECTED_RECEIPT_DATE
,SHIPPED_DATE
,OWNING_ORG
,OWNING_ORG_ID
,REQ_HEADER_ID
,OE_HEADER_ID
,PO_HEADER_ID
,ORIGINATION_DATE
,ACTION_CODE
,ACTION_DATE
,RETEST_DATE
,LOT
,SERIAL
,UNIT_NUMBER
,LOT_EXPIRY_DATE
,ORIGINATION_TYPE
,ORGANIZATION_CODE
,ORG_ID
,ITEM
,ITEM_DESCRIPTION
,ITEM_ID
,REVISION
,PRIMARY_UOM_CODE
,ONHAND
,RECEIVING
,INBOUND
,UNPACKED
,PACKED
,SECONDARY_UOM_CODE
,SECONDARY_ONHAND
,SECONDARY_RECEIVING
,SECONDARY_INBOUND
,SECONDARY_UNPACKED
,SECONDARY_PACKED
,SUBINVENTORY_CODE
,LOCATOR
,LOCATOR_ID
,LPN
,LPN_ID
,COST_GROUP
,CG_ID
,GRADE_CODE
,LOADED
,PLANNING_PARTY
,PLANNING_PARTY_ID
,OWNING_PARTY
,OWNING_PARTY_ID
,OWNING_ORGANIZATION_ID
,PLANNING_ORGANIZATION_ID
,PLANNING_TP_TYPE
,OWNING_TP_TYPE
,PROJECT_ID
,TASK_ID
,STATUS_ID -- Onhand Material Status Support
FROM MTL_MWB_GTMP;
SELECT meaning BULK COLLECT
INTO document_type_meaning
FROM mfg_lookups
WHERE lookup_type = 'MTL_DOCUMENT_TYPES'
ORDER BY lookup_code;
SELECT meaning BULK COLLECT
INTO lpn_context_meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_LPN_CONTEXT';
UPDATE MTL_MWB_GTMP
SET organization_code = inv_mwb_globals.g_organization_code;
SELECT organization_code
INTO l_to_org
FROM mtl_parameters
WHERE organization_id = rec.org_id;
UPDATE MTL_MWB_GTMP
SET organization_code = l_to_org
, to_org = l_to_org
WHERE org_id = rec.org_id;
UPDATE MTL_MWB_GTMP
SET SUBINVENTORY_CODE = inv_mwb_globals.g_subinventory_code;
UPDATE MTL_MWB_GTMP
SET owning_org = (SELECT organization_code
FROM mtl_parameters
WHERE organization_id = rec.owning_org_id)
WHERE owning_org_id = rec.owning_org_id;
UPDATE MTL_MWB_GTMP
SET lpn = (SELECT license_plate_number
FROM wms_license_plate_numbers
WHERE lpn_id = rec.lpn_id)
WHERE lpn_id = rec.lpn_id;
UPDATE MTL_MWB_GTMP
SET loaded = (SELECT 1
FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt
WHERE wdt.status = 4
AND wdt.task_type <> 2
AND wdt.transaction_temp_id = mmtt.transaction_temp_id
AND (mmtt.content_lpn_id = rec.lpn_id
OR mmtt.lpn_id = rec.lpn_id))
WHERE lpn_id = rec.lpn_id;
UPDATE MTL_MWB_GTMP
SET LOADED = 1
WHERE lpn_id = rec.lpn_id;
UPDATE MTL_MWB_GTMP
SET cost_group = inv_mwb_globals.g_cost_group;
UPDATE MTL_MWB_GTMP
SET cost_group = (SELECT distinct cost_group
FROM cst_cost_groups
WHERE cost_group_id = rec.cg_id)
WHERE cg_id = rec.cg_id;
UPDATE MTL_MWB_GTMP
SET (
ITEM
, ITEM_DESCRIPTION
, PRIMARY_UOM_CODE
) = (
SELECT
l_item_name -- Bug 6350236
, DESCRIPTION
, PRIMARY_UOM_CODE
FROM mtl_system_items_kfv
WHERE inventory_item_id = rec.item_id
AND organization_id = rec.org_id)
WHERE item_id = rec.item_id;
UPDATE MTL_MWB_GTMP
SET locator = substr(inv_mwb_globals.g_locator_name, 1, 100),--Bug6595049: truncating the locator to 100 chars
locator_id = inv_mwb_globals.g_locator_id; -- Bug 7408480
UPDATE MTL_MWB_GTMP
SET locator = substr((SELECT concatenated_segments
FROM mtl_item_locations_kfv
WHERE inventory_location_id = rec.locator_id), 1, 100) -- Bug 6595049: truncating the locator to 100 chars
WHERE locator_id = rec.locator_id;
UPDATE MTL_MWB_GTMP
SET LOCATOR = substr(INV_PROJECT.GET_LOCATOR(REC.LOCATOR_ID, REC.ORG_ID), 1, 100) -- Bug 6595049: truncating the locator to 100 chars
WHERE locator_id = rec.locator_id;
SELECT project_id
, task_id
INTO l_project_id
, l_task_id
FROM mtl_item_locations_kfv
WHERE inventory_location_id = rec.locator_id
AND organization_id = rec.org_id;
UPDATE MTL_MWB_GTMP
SET project_number = inv_mwb_globals.g_project_number;
UPDATE MTL_MWB_GTMP
SET project_number = (SELECT project_number
FROM pjm_projects_v
WHERE project_id = l_project_id)
WHERE locator_id = rec.locator_id;
UPDATE MTL_MWB_GTMP
SET task_number = inv_mwb_globals.g_task_number;
UPDATE MTL_MWB_GTMP
SET task_number = (SELECT task_number
FROM pjm_tasks_v
WHERE task_id = rec.task_id)
WHERE locator_id = rec.locator_id;
SELECT shipment_num, shipped_date
INTO l_shipment_num, l_shipped_date
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rec.shipment_header_id_asn;
UPDATE mtl_mwb_gtmp
SET document_number = l_shipment_num
, shipped_date = l_shipped_date
, document_type = l_mtl_location
, document_type_id = 4
WHERE shipment_header_id_asn = rec.shipment_header_id_asn;
UPDATE mtl_mwb_gtmp
SET document_line_number = (SELECT line_num
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rec.document_line_number)
WHERE shipment_header_id_asn = rec.shipment_header_id_asn
AND document_line_number = rec.document_line_number;
UPDATE mtl_mwb_gtmp
SET document_number = (SELECT segment1
FROM po_headers_all pha
WHERE pha.po_header_id = rec.po_header_id)
, document_type = l_mtl_location
, document_type_id = 1
WHERE po_header_id = rec.po_header_id;
UPDATE mtl_mwb_gtmp
SET document_line_number = (SELECT line_num
FROM po_lines_all pla
WHERE pla.po_line_id = rec.document_line_number)
WHERE po_header_id = rec.po_header_id
AND document_line_number = rec.document_line_number;
UPDATE mtl_mwb_gtmp
SET document_number = (SELECT segment1
FROM po_requisition_headers_all prha
WHERE prha.requisition_header_id = rec.req_header_id)
, document_type = l_mtl_location
, document_type_id = 2
WHERE req_header_id = rec.req_header_id;
UPDATE mtl_mwb_gtmp
SET document_line_number = (SELECT line_num
FROM po_requisition_lines_all prla
WHERE prla.requisition_line_id = rec.document_line_number)
WHERE req_header_id = rec.req_header_id
AND document_line_number = rec.document_line_number;
UPDATE mtl_mwb_gtmp
SET document_number = (SELECT shipment_num
FROM rcv_shipment_headers rsh
WHERE rsh.shipment_header_id = rec.shipment_header_id_asn)
, document_type = l_mtl_location
, document_type_id = 3
WHERE shipment_header_id_asn = rec.shipment_header_id_asn;
UPDATE mtl_mwb_gtmp
SET document_line_number = (SELECT line_num
FROM rcv_shipment_lines rsl
WHERE rsl.shipment_line_id = rec.document_line_number)
WHERE shipment_header_id_asn = rec.shipment_header_id_asn
AND document_line_number = rec.document_line_number;
SELECT vendor_id
, vendor_site_id
INTO l_vendor_id
, l_vendor_site_id
FROM po_headers_all
WHERE po_header_id = rec.po_header_id;
UPDATE mtl_mwb_gtmp
SET vendor_id = l_vendor_id
, vendor_site_id = l_vendor_site_id
WHERE po_header_id = rec.po_header_id;
UPDATE mtl_mwb_gtmp
SET trading_partner = (SELECT vendor_name
FROM po_vendors
WHERE vendor_id = l_vendor_id)
WHERE vendor_id = l_vendor_id;
UPDATE mtl_mwb_gtmp
SET trading_partner_site = (SELECT vendor_site_code
FROM po_vendor_sites_all
WHERE vendor_site_id = l_vendor_site_id)
WHERE vendor_id = l_vendor_id
AND vendor_site_id = l_vendor_site_id;
UPDATE mtl_mwb_gtmp
SET LPN_CONTEXT = lpn_context_meaning(3)
WHERE lpn_id = rec.lpn_id;
SELECT lpn_context
INTO l_lpn_context_id
FROM wms_license_plate_numbers
WHERE lpn_id = rec.lpn_id;
UPDATE mtl_mwb_gtmp
SET LPN_CONTEXT = (SELECT meaning
FROM mfg_lookups
WHERE lookup_type = 'WMS_LPN_CONTEXT'
AND lookup_code = l_lpn_context_id)
WHERE lpn_id = rec.lpn_id;
UPDATE mtl_mwb_gtmp
SET document_type = document_type_meaning(4)
, document_type_id = 4
WHERE shipment_header_id_asn = rec.shipment_header_id_asn;
UPDATE mtl_mwb_gtmp
SET planning_party = inv_mwb_globals.g_planning_party;
UPDATE mtl_mwb_gtmp
SET planning_party = (SELECT vendor_name || '-' || vendor_site_code
FROM po_vendor_sites_all ps, po_vendors pv
WHERE pv.vendor_id = ps.vendor_id
AND ps.vendor_site_id = rec.planning_organization_id)
WHERE planning_organization_id = rec.planning_organization_id;
UPDATE mtl_mwb_gtmp
SET owning_party = inv_mwb_globals.g_owning_party;
UPDATE mtl_mwb_gtmp
SET owning_party = (SELECT vendor_name || '-' || vendor_site_code
FROM po_vendor_sites_all ps, po_vendors pv
WHERE pv.vendor_id = ps.vendor_id
AND ps.vendor_site_id = rec.owning_organization_id)
WHERE owning_organization_id = rec.owning_organization_id;
UPDATE MTL_MWB_GTMP
SET (
ORIGINATION_TYPE
, ORIGINATION_DATE
, ACTION_DATE
, ACTION_CODE
, RETEST_DATE
, PARENT_LOT
, MATURITY_DATE
, HOLD_DATE
, SUPPLIER_LOT
, LOT_EXPIRY_DATE
) = (
SELECT
mfg.meaning /* Bug 5417041 */
, mln.origination_date
, mln.expiration_action_date
, mln.expiration_action_code
, mln.retest_date
, mln.parent_lot_number
, mln.maturity_date
, mln.hold_date
, mln.supplier_lot_number
, mln.expiration_date
FROM
mtl_lot_numbers mln
, mfg_lookups mfg
WHERE lot_number = rec.lot
AND mln.inventory_item_id = rec.item_id
AND mln.organization_id = rec.org_id
AND mfg.lookup_type = 'MTL_LOT_ORIGINATION_TYPE'
AND mfg.lookup_code = mln.origination_type)
WHERE item_id = rec.item_id
AND lot = rec.lot;
UPDATE MTL_MWB_GTMP
SET (
GRADE_CODE
, LOT_EXPIRY_DATE
) = (
SELECT grade_code
, expiration_date
FROM mtl_lot_numbers
WHERE lot_number = rec.lot
AND inventory_item_id = rec.item_id
AND organization_id = rec.org_id
)
WHERE LOT = rec.lot;
SELECT tracking_quantity_ind
INTO l_tracking_qty_ind
FROM mtl_system_items
WHERE organization_id = rec.org_id
AND inventory_item_id = rec.item_id;
UPDATE mtl_mwb_gtmp
SET SECONDARY_ONHAND = NULL
, SECONDARY_UNPACKED = NULL
, SECONDARY_PACKED = NULL
, SECONDARY_UOM_CODE = NULL
WHERE org_id = rec.org_id
AND item_id = rec.item_id;
UPDATE MTL_MWB_GTMP
SET LOT = inv_mwb_globals.g_lot_from;
UPDATE MTL_MWB_GTMP
SET SERIAL = inv_mwb_globals.g_serial_from;
select status_id
into rec.status_id
from mtl_serial_numbers
where inventory_item_id = rec.item_id
and serial_number = rec.SERIAL;
UPDATE MTL_MWB_GTMP
SET status_id = rec.status_id
WHERE item_id = rec.item_id
and serial = rec.SERIAL;
SELECT status_code
INTO l_status_name
FROM mtl_material_statuses_vl
WHERE status_id = rec.status_id;
UPDATE MTL_MWB_GTMP
SET status = l_status_name
WHERE status_id = rec.status_id;
SELECT serial_number_control_code
INTO l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
AND organization_id = inv_mwb_globals.g_tree_organization_id;
SELECT lot_control_code,
serial_number_control_code
INTO l_lot_control,
l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
AND organization_id = inv_mwb_globals.g_tree_organization_id;
SELECT revision_qty_control_code,
lot_control_code,
serial_number_control_code
INTO l_rev_control,
l_lot_control,
l_serial_control
FROM mtl_system_items
WHERE inventory_item_id = inv_mwb_globals.g_tree_item_id
AND organization_id = inv_mwb_globals.g_tree_organization_id;
g_onhand_select,
g_onhand_from,
g_onhand_where,
g_onhand_group
);
l_temp_rec SelectColumnRecType;
g_onhand_select.DELETE;
g_onhand_where.DELETE;
g_onhand_from.DELETE;
g_onhand_group.DELETE;
g_onhand_select(PO_RELEASE_ID) := l_temp_rec;
g_onhand_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_onhand_select(SHIPMENT_NUMBER) := l_temp_rec;
g_onhand_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_onhand_select(ASN) := l_temp_rec;
g_onhand_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_onhand_select(TRADING_PARTNER) := l_temp_rec;
g_onhand_select(VENDOR_ID) := l_temp_rec;
g_onhand_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_onhand_select(VENDOR_SITE_ID) := l_temp_rec;
g_onhand_select(FROM_ORG) := l_temp_rec;
g_onhand_select(FROM_ORG_ID) := l_temp_rec;
g_onhand_select(TO_ORG) := l_temp_rec;
g_onhand_select(TO_ORG_ID) := l_temp_rec;
g_onhand_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_onhand_select(SHIPPED_DATE) := l_temp_rec;
g_onhand_select(OWNING_ORG) := l_temp_rec;
g_onhand_select(OWNING_ORG_ID) := l_temp_rec;
g_onhand_select(REQ_HEADER_ID) := l_temp_rec;
g_onhand_select(OE_HEADER_ID) := l_temp_rec;
g_onhand_select(PO_HEADER_ID) := l_temp_rec;
g_onhand_select(MATURITY_DATE) := l_temp_rec;
g_onhand_select(HOLD_DATE) := l_temp_rec;
g_onhand_select(SUPPLIER_LOT) := l_temp_rec;
g_onhand_select(PARENT_LOT) := l_temp_rec;
g_onhand_select(DOCUMENT_TYPE) := l_temp_rec;
g_onhand_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_onhand_select(DOCUMENT_NUMBER) := l_temp_rec;
g_onhand_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_onhand_select(RELEASE_NUMBER) := l_temp_rec;
g_onhand_select(ORIGINATION_TYPE) := l_temp_rec;
g_onhand_select(ORIGINATION_DATE) := l_temp_rec;
g_onhand_select(ACTION_CODE) := l_temp_rec;
g_onhand_select(ACTION_DATE) := l_temp_rec;
g_onhand_select(RETEST_DATE) := l_temp_rec;
g_onhand_select(SECONDARY_UNPACKED) := l_temp_rec;
g_onhand_select(SECONDARY_PACKED) := l_temp_rec;
g_onhand_select(SUBINVENTORY_CODE) := l_temp_rec;
g_onhand_select(LOCATOR) := l_temp_rec;
g_onhand_select(LOCATOR_ID) := l_temp_rec;
g_onhand_select(LPN) := l_temp_rec;
g_onhand_select(LPN_ID) := l_temp_rec;
g_onhand_select(COST_GROUP) := l_temp_rec;
g_onhand_select(GRADE_CODE) := l_temp_rec;
g_onhand_select(CG_ID) := l_temp_rec;
g_onhand_select(LOADED) := l_temp_rec;
g_onhand_select(PLANNING_PARTY) := l_temp_rec;
g_onhand_select(PLANNING_PARTY_ID) := l_temp_rec;
g_onhand_select(OWNING_PARTY) := l_temp_rec;
g_onhand_select(OWNING_PARTY_ID) := l_temp_rec;
g_onhand_select(LOT) := l_temp_rec;
g_onhand_select(SERIAL) := l_temp_rec;
g_onhand_select(UNIT_NUMBER) := l_temp_rec;
g_onhand_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_onhand_select(ORGANIZATION_CODE) := l_temp_rec;
g_onhand_select(ORG_ID) := l_temp_rec;
g_onhand_select(ITEM) := l_temp_rec;
g_onhand_select(ITEM_DESCRIPTION) := l_temp_rec;
g_onhand_select(ITEM_ID) := l_temp_rec;
g_onhand_select(REVISION) := l_temp_rec;
g_onhand_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_onhand_select(ONHAND) := l_temp_rec;
g_onhand_select(RECEIVING) := l_temp_rec;
g_onhand_select(INBOUND) := l_temp_rec;
g_onhand_select(UNPACKED) := l_temp_rec;
g_onhand_select(PACKED) := l_temp_rec;
g_onhand_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_onhand_select(SECONDARY_ONHAND) := l_temp_rec;
g_onhand_select(SECONDARY_RECEIVING) := l_temp_rec;
g_onhand_select(SECONDARY_INBOUND) := l_temp_rec;
g_onhand_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_onhand_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_onhand_select(OWNING_TP_TYPE) := l_temp_rec;
g_onhand_select(PLANNING_TP_TYPE) := l_temp_rec;
g_onhand_select(STATUS) := l_temp_rec;
g_onhand_select(STATUS_ID) := l_temp_rec;
l_temp_rec SelectColumnRecType;
g_onhand_1_select.DELETE;
g_onhand_1_where.DELETE;
g_onhand_1_from.DELETE;
g_onhand_1_group.DELETE;
g_onhand_1_select(PO_RELEASE_ID) := l_temp_rec;
g_onhand_1_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_onhand_1_select(SHIPMENT_NUMBER) := l_temp_rec;
g_onhand_1_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_onhand_1_select(ASN) := l_temp_rec;
g_onhand_1_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_onhand_1_select(TRADING_PARTNER) := l_temp_rec;
g_onhand_1_select(VENDOR_ID) := l_temp_rec;
g_onhand_1_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_onhand_1_select(VENDOR_SITE_ID) := l_temp_rec;
g_onhand_1_select(FROM_ORG) := l_temp_rec;
g_onhand_1_select(FROM_ORG_ID) := l_temp_rec;
g_onhand_1_select(TO_ORG) := l_temp_rec;
g_onhand_1_select(TO_ORG_ID) := l_temp_rec;
g_onhand_1_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_onhand_1_select(SHIPPED_DATE) := l_temp_rec;
g_onhand_1_select(OWNING_ORG) := l_temp_rec;
g_onhand_1_select(OWNING_ORG_ID) := l_temp_rec;
g_onhand_1_select(REQ_HEADER_ID) := l_temp_rec;
g_onhand_1_select(OE_HEADER_ID) := l_temp_rec;
g_onhand_1_select(PO_HEADER_ID) := l_temp_rec;
g_onhand_1_select(MATURITY_DATE) := l_temp_rec;
g_onhand_1_select(HOLD_DATE) := l_temp_rec;
g_onhand_1_select(SUPPLIER_LOT) := l_temp_rec;
g_onhand_1_select(PARENT_LOT) := l_temp_rec;
g_onhand_1_select(DOCUMENT_TYPE) := l_temp_rec;
g_onhand_1_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_onhand_1_select(DOCUMENT_NUMBER) := l_temp_rec;
g_onhand_1_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_onhand_1_select(RELEASE_NUMBER) := l_temp_rec;
g_onhand_1_select(ORIGINATION_TYPE) := l_temp_rec;
g_onhand_1_select(ORIGINATION_DATE) := l_temp_rec;
g_onhand_1_select(ACTION_CODE) := l_temp_rec;
g_onhand_1_select(ACTION_DATE) := l_temp_rec;
g_onhand_1_select(RETEST_DATE) := l_temp_rec;
g_onhand_1_select(SECONDARY_UNPACKED) := l_temp_rec;
g_onhand_1_select(SECONDARY_PACKED) := l_temp_rec;
g_onhand_1_select(SUBINVENTORY_CODE) := l_temp_rec;
g_onhand_1_select(LOCATOR) := l_temp_rec;
g_onhand_1_select(LOCATOR_ID) := l_temp_rec;
g_onhand_1_select(LPN) := l_temp_rec;
g_onhand_1_select(LPN_ID) := l_temp_rec;
g_onhand_1_select(COST_GROUP) := l_temp_rec;
g_onhand_1_select(GRADE_CODE) := l_temp_rec;
g_onhand_1_select(CG_ID) := l_temp_rec;
g_onhand_1_select(LOADED) := l_temp_rec;
g_onhand_1_select(PLANNING_PARTY) := l_temp_rec;
g_onhand_1_select(PLANNING_PARTY_ID) := l_temp_rec;
g_onhand_1_select(OWNING_PARTY) := l_temp_rec;
g_onhand_1_select(OWNING_PARTY_ID) := l_temp_rec;
g_onhand_1_select(LOT) := l_temp_rec;
g_onhand_1_select(SERIAL) := l_temp_rec;
g_onhand_1_select(UNIT_NUMBER) := l_temp_rec;
g_onhand_1_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_onhand_1_select(ORGANIZATION_CODE) := l_temp_rec;
g_onhand_1_select(ORG_ID) := l_temp_rec;
g_onhand_1_select(ITEM) := l_temp_rec;
g_onhand_1_select(ITEM_DESCRIPTION) := l_temp_rec;
g_onhand_1_select(ITEM_ID) := l_temp_rec;
g_onhand_1_select(REVISION) := l_temp_rec;
g_onhand_1_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_onhand_1_select(ONHAND) := l_temp_rec;
g_onhand_1_select(RECEIVING) := l_temp_rec;
g_onhand_1_select(INBOUND) := l_temp_rec;
g_onhand_1_select(UNPACKED) := l_temp_rec;
g_onhand_1_select(PACKED) := l_temp_rec;
g_onhand_1_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_onhand_1_select(SECONDARY_ONHAND) := l_temp_rec;
g_onhand_1_select(SECONDARY_RECEIVING) := l_temp_rec;
g_onhand_1_select(SECONDARY_INBOUND) := l_temp_rec;
g_onhand_1_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_onhand_1_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_onhand_1_select(OWNING_TP_TYPE) := l_temp_rec;
g_onhand_1_select(PLANNING_TP_TYPE) := l_temp_rec;
g_onhand_1_select(STATUS) := l_temp_rec;
g_onhand_1_select(STATUS_ID) := l_temp_rec;
l_temp_rec SelectColumnRecType;
g_inbound_select.DELETE;
g_inbound_from.DELETE;
g_inbound_where.DELETE;
g_inbound_group.DELETE;
g_inbound_select(PO_RELEASE_ID) := l_temp_rec;
g_inbound_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_inbound_select(SHIPMENT_NUMBER) := l_temp_rec;
g_inbound_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_inbound_select(ASN) := l_temp_rec;
g_inbound_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_inbound_select(TRADING_PARTNER) := l_temp_rec;
g_inbound_select(VENDOR_ID) := l_temp_rec;
g_inbound_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_inbound_select(VENDOR_SITE_ID) := l_temp_rec;
g_inbound_select(FROM_ORG) := l_temp_rec;
g_inbound_select(FROM_ORG_ID) := l_temp_rec;
g_inbound_select(TO_ORG) := l_temp_rec;
g_inbound_select(TO_ORG_ID) := l_temp_rec;
g_inbound_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_inbound_select(SHIPPED_DATE) := l_temp_rec;
g_inbound_select(OWNING_ORG) := l_temp_rec;
g_inbound_select(OWNING_ORG_ID) := l_temp_rec;
g_inbound_select(REQ_HEADER_ID) := l_temp_rec;
g_inbound_select(OE_HEADER_ID) := l_temp_rec;
g_inbound_select(PO_HEADER_ID) := l_temp_rec;
g_inbound_select(MATURITY_DATE) := l_temp_rec;
g_inbound_select(HOLD_DATE) := l_temp_rec;
g_inbound_select(SUPPLIER_LOT) := l_temp_rec;
g_inbound_select(PARENT_LOT) := l_temp_rec;
g_inbound_select(DOCUMENT_TYPE) := l_temp_rec;
g_inbound_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_inbound_select(DOCUMENT_NUMBER) := l_temp_rec;
g_inbound_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_inbound_select(RELEASE_NUMBER) := l_temp_rec;
g_inbound_select(ORIGINATION_TYPE) := l_temp_rec;
g_inbound_select(ORIGINATION_DATE) := l_temp_rec;
g_inbound_select(ACTION_CODE) := l_temp_rec;
g_inbound_select(ACTION_DATE) := l_temp_rec;
g_inbound_select(RETEST_DATE) := l_temp_rec;
g_inbound_select(SECONDARY_UNPACKED) := l_temp_rec;
g_inbound_select(SECONDARY_PACKED) := l_temp_rec;
g_inbound_select(SUBINVENTORY_CODE) := l_temp_rec;
g_inbound_select(LOCATOR) := l_temp_rec;
g_inbound_select(LOCATOR_ID) := l_temp_rec;
g_inbound_select(LPN) := l_temp_rec;
g_inbound_select(LPN_ID) := l_temp_rec;
g_inbound_select(COST_GROUP) := l_temp_rec;
g_inbound_select(GRADE_CODE) := l_temp_rec;
g_inbound_select(CG_ID) := l_temp_rec;
g_inbound_select(LOADED) := l_temp_rec;
g_inbound_select(PLANNING_PARTY) := l_temp_rec;
g_inbound_select(PLANNING_PARTY_ID) := l_temp_rec;
g_inbound_select(OWNING_PARTY) := l_temp_rec;
g_inbound_select(OWNING_PARTY_ID) := l_temp_rec;
g_inbound_select(LOT) := l_temp_rec;
g_inbound_select(SERIAL) := l_temp_rec;
g_inbound_select(UNIT_NUMBER) := l_temp_rec;
g_inbound_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_inbound_select(ORGANIZATION_CODE) := l_temp_rec;
g_inbound_select(ORG_ID) := l_temp_rec;
g_inbound_select(ITEM) := l_temp_rec;
g_inbound_select(ITEM_DESCRIPTION) := l_temp_rec;
g_inbound_select(ITEM_ID) := l_temp_rec;
g_inbound_select(REVISION) := l_temp_rec;
g_inbound_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_inbound_select(ONHAND) := l_temp_rec;
g_inbound_select(RECEIVING) := l_temp_rec;
g_inbound_select(INBOUND) := l_temp_rec;
g_inbound_select(UNPACKED) := l_temp_rec;
g_inbound_select(PACKED) := l_temp_rec;
g_inbound_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_inbound_select(SECONDARY_ONHAND) := l_temp_rec;
g_inbound_select(SECONDARY_RECEIVING) := l_temp_rec;
g_inbound_select(SECONDARY_INBOUND) := l_temp_rec;
g_inbound_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_inbound_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_inbound_select(OWNING_TP_TYPE) := l_temp_rec;
g_inbound_select(PLANNING_TP_TYPE) := l_temp_rec;
g_inbound_select(STATUS) := l_temp_rec;
g_inbound_select(STATUS_ID) := l_temp_rec;
l_temp_rec SelectColumnRecType;
g_inbound_1_select.DELETE;
g_inbound_1_from.DELETE;
g_inbound_1_where.DELETE;
g_inbound_1_group.DELETE;
g_inbound_1_select(PO_RELEASE_ID) := l_temp_rec;
g_inbound_1_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_inbound_1_select(SHIPMENT_NUMBER) := l_temp_rec;
g_inbound_1_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_inbound_1_select(ASN) := l_temp_rec;
g_inbound_1_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_inbound_1_select(TRADING_PARTNER) := l_temp_rec;
g_inbound_1_select(VENDOR_ID) := l_temp_rec;
g_inbound_1_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_inbound_1_select(VENDOR_SITE_ID) := l_temp_rec;
g_inbound_1_select(FROM_ORG) := l_temp_rec;
g_inbound_1_select(FROM_ORG_ID) := l_temp_rec;
g_inbound_1_select(TO_ORG) := l_temp_rec;
g_inbound_1_select(TO_ORG_ID) := l_temp_rec;
g_inbound_1_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_inbound_1_select(SHIPPED_DATE) := l_temp_rec;
g_inbound_1_select(OWNING_ORG) := l_temp_rec;
g_inbound_1_select(OWNING_ORG_ID) := l_temp_rec;
g_inbound_1_select(REQ_HEADER_ID) := l_temp_rec;
g_inbound_1_select(OE_HEADER_ID) := l_temp_rec;
g_inbound_1_select(PO_HEADER_ID) := l_temp_rec;
g_inbound_1_select(MATURITY_DATE) := l_temp_rec;
g_inbound_1_select(HOLD_DATE) := l_temp_rec;
g_inbound_1_select(SUPPLIER_LOT) := l_temp_rec;
g_inbound_1_select(PARENT_LOT) := l_temp_rec;
g_inbound_1_select(DOCUMENT_TYPE) := l_temp_rec;
g_inbound_1_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_inbound_1_select(DOCUMENT_NUMBER) := l_temp_rec;
g_inbound_1_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_inbound_1_select(RELEASE_NUMBER) := l_temp_rec;
g_inbound_1_select(ORIGINATION_TYPE) := l_temp_rec;
g_inbound_1_select(ORIGINATION_DATE) := l_temp_rec;
g_inbound_1_select(ACTION_CODE) := l_temp_rec;
g_inbound_1_select(ACTION_DATE) := l_temp_rec;
g_inbound_1_select(RETEST_DATE) := l_temp_rec;
g_inbound_1_select(SECONDARY_UNPACKED) := l_temp_rec;
g_inbound_1_select(SECONDARY_PACKED) := l_temp_rec;
g_inbound_1_select(SUBINVENTORY_CODE) := l_temp_rec;
g_inbound_1_select(LOCATOR) := l_temp_rec;
g_inbound_1_select(LOCATOR_ID) := l_temp_rec;
g_inbound_1_select(LPN) := l_temp_rec;
g_inbound_1_select(LPN_ID) := l_temp_rec;
g_inbound_1_select(COST_GROUP) := l_temp_rec;
g_inbound_1_select(GRADE_CODE) := l_temp_rec;
g_inbound_1_select(CG_ID) := l_temp_rec;
g_inbound_1_select(LOADED) := l_temp_rec;
g_inbound_1_select(PLANNING_PARTY) := l_temp_rec;
g_inbound_1_select(PLANNING_PARTY_ID) := l_temp_rec;
g_inbound_1_select(OWNING_PARTY) := l_temp_rec;
g_inbound_1_select(OWNING_PARTY_ID) := l_temp_rec;
g_inbound_1_select(LOT) := l_temp_rec;
g_inbound_1_select(SERIAL) := l_temp_rec;
g_inbound_1_select(UNIT_NUMBER) := l_temp_rec;
g_inbound_1_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_inbound_1_select(ORGANIZATION_CODE) := l_temp_rec;
g_inbound_1_select(ORG_ID) := l_temp_rec;
g_inbound_1_select(ITEM) := l_temp_rec;
g_inbound_1_select(ITEM_DESCRIPTION) := l_temp_rec;
g_inbound_1_select(ITEM_ID) := l_temp_rec;
g_inbound_1_select(REVISION) := l_temp_rec;
g_inbound_1_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_inbound_1_select(ONHAND) := l_temp_rec;
g_inbound_1_select(RECEIVING) := l_temp_rec;
g_inbound_1_select(INBOUND) := l_temp_rec;
g_inbound_1_select(UNPACKED) := l_temp_rec;
g_inbound_1_select(PACKED) := l_temp_rec;
g_inbound_1_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_inbound_1_select(SECONDARY_ONHAND) := l_temp_rec;
g_inbound_1_select(SECONDARY_RECEIVING) := l_temp_rec;
g_inbound_1_select(SECONDARY_INBOUND) := l_temp_rec;
g_inbound_1_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_inbound_1_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_inbound_1_select(OWNING_TP_TYPE) := l_temp_rec;
g_inbound_1_select(PLANNING_TP_TYPE) := l_temp_rec;
g_inbound_1_select(STATUS) := l_temp_rec;
g_inbound_1_select(STATUS_ID) := l_temp_rec;
l_temp_rec SelectColumnRecType;
g_receiving_select.DELETE;
g_receiving_from.DELETE;
g_receiving_where.DELETE;
g_receiving_group.DELETE;
g_receiving_select(PO_RELEASE_ID) := l_temp_rec;
g_receiving_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_receiving_select(SHIPMENT_NUMBER) := l_temp_rec;
g_receiving_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_receiving_select(ASN) := l_temp_rec;
g_receiving_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_receiving_select(TRADING_PARTNER) := l_temp_rec;
g_receiving_select(VENDOR_ID) := l_temp_rec;
g_receiving_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_receiving_select(VENDOR_SITE_ID) := l_temp_rec;
g_receiving_select(FROM_ORG) := l_temp_rec;
g_receiving_select(FROM_ORG_ID) := l_temp_rec;
g_receiving_select(TO_ORG) := l_temp_rec;
g_receiving_select(TO_ORG_ID) := l_temp_rec;
g_receiving_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_receiving_select(SHIPPED_DATE) := l_temp_rec;
g_receiving_select(OWNING_ORG) := l_temp_rec;
g_receiving_select(OWNING_ORG_ID) := l_temp_rec;
g_receiving_select(REQ_HEADER_ID) := l_temp_rec;
g_receiving_select(OE_HEADER_ID) := l_temp_rec;
g_receiving_select(PO_HEADER_ID) := l_temp_rec;
g_receiving_select(MATURITY_DATE) := l_temp_rec;
g_receiving_select(HOLD_DATE) := l_temp_rec;
g_receiving_select(SUPPLIER_LOT) := l_temp_rec;
g_receiving_select(PARENT_LOT) := l_temp_rec;
g_receiving_select(DOCUMENT_TYPE) := l_temp_rec;
g_receiving_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_receiving_select(DOCUMENT_NUMBER) := l_temp_rec;
g_receiving_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_receiving_select(RELEASE_NUMBER) := l_temp_rec;
g_receiving_select(ORIGINATION_TYPE) := l_temp_rec;
g_receiving_select(ORIGINATION_DATE) := l_temp_rec;
g_receiving_select(ACTION_CODE) := l_temp_rec;
g_receiving_select(ACTION_DATE) := l_temp_rec;
g_receiving_select(RETEST_DATE) := l_temp_rec;
g_receiving_select(SECONDARY_UNPACKED) := l_temp_rec;
g_receiving_select(SECONDARY_PACKED) := l_temp_rec;
g_receiving_select(SUBINVENTORY_CODE) := l_temp_rec;
g_receiving_select(LOCATOR) := l_temp_rec;
g_receiving_select(LOCATOR_ID) := l_temp_rec;
g_receiving_select(LPN) := l_temp_rec;
g_receiving_select(LPN_ID) := l_temp_rec;
g_receiving_select(COST_GROUP) := l_temp_rec;
g_receiving_select(GRADE_CODE) := l_temp_rec;
g_receiving_select(CG_ID) := l_temp_rec;
g_receiving_select(LOADED) := l_temp_rec;
g_receiving_select(PLANNING_PARTY) := l_temp_rec;
g_receiving_select(PLANNING_PARTY_ID) := l_temp_rec;
g_receiving_select(OWNING_PARTY) := l_temp_rec;
g_receiving_select(OWNING_PARTY_ID) := l_temp_rec;
g_receiving_select(LOT) := l_temp_rec;
g_receiving_select(SERIAL) := l_temp_rec;
g_receiving_select(UNIT_NUMBER) := l_temp_rec;
g_receiving_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_receiving_select(ORGANIZATION_CODE) := l_temp_rec;
g_receiving_select(ORG_ID) := l_temp_rec;
g_receiving_select(ITEM) := l_temp_rec;
g_receiving_select(ITEM_DESCRIPTION) := l_temp_rec;
g_receiving_select(ITEM_ID) := l_temp_rec;
g_receiving_select(REVISION) := l_temp_rec;
g_receiving_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_receiving_select(ONHAND) := l_temp_rec;
g_receiving_select(RECEIVING) := l_temp_rec;
g_receiving_select(INBOUND) := l_temp_rec;
g_receiving_select(UNPACKED) := l_temp_rec;
g_receiving_select(PACKED) := l_temp_rec;
g_receiving_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_receiving_select(SECONDARY_ONHAND) := l_temp_rec;
g_receiving_select(SECONDARY_RECEIVING) := l_temp_rec;
g_receiving_select(SECONDARY_INBOUND) := l_temp_rec;
g_receiving_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_receiving_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_receiving_select(OWNING_TP_TYPE) := l_temp_rec;
g_receiving_select(PLANNING_TP_TYPE) := l_temp_rec;
g_receiving_select(STATUS) := l_temp_rec;
g_receiving_select(STATUS_ID) := l_temp_rec;
l_temp_rec SelectColumnRecType;
g_receiving_1_select.DELETE;
g_receiving_1_from.DELETE;
g_receiving_1_where.DELETE;
g_receiving_1_group.DELETE;
g_receiving_1_select(PO_RELEASE_ID) := l_temp_rec;
g_receiving_1_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_receiving_1_select(SHIPMENT_NUMBER) := l_temp_rec;
g_receiving_1_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_receiving_1_select(ASN) := l_temp_rec;
g_receiving_1_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_receiving_1_select(TRADING_PARTNER) := l_temp_rec;
g_receiving_1_select(VENDOR_ID) := l_temp_rec;
g_receiving_1_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_receiving_1_select(VENDOR_SITE_ID) := l_temp_rec;
g_receiving_1_select(FROM_ORG) := l_temp_rec;
g_receiving_1_select(FROM_ORG_ID) := l_temp_rec;
g_receiving_1_select(TO_ORG) := l_temp_rec;
g_receiving_1_select(TO_ORG_ID) := l_temp_rec;
g_receiving_1_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_receiving_1_select(SHIPPED_DATE) := l_temp_rec;
g_receiving_1_select(OWNING_ORG) := l_temp_rec;
g_receiving_1_select(OWNING_ORG_ID) := l_temp_rec;
g_receiving_1_select(REQ_HEADER_ID) := l_temp_rec;
g_receiving_1_select(OE_HEADER_ID) := l_temp_rec;
g_receiving_1_select(PO_HEADER_ID) := l_temp_rec;
g_receiving_1_select(MATURITY_DATE) := l_temp_rec;
g_receiving_1_select(HOLD_DATE) := l_temp_rec;
g_receiving_1_select(SUPPLIER_LOT) := l_temp_rec;
g_receiving_1_select(PARENT_LOT) := l_temp_rec;
g_receiving_1_select(DOCUMENT_TYPE) := l_temp_rec;
g_receiving_1_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_receiving_1_select(DOCUMENT_NUMBER) := l_temp_rec;
g_receiving_1_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_receiving_1_select(RELEASE_NUMBER) := l_temp_rec;
g_receiving_1_select(ORIGINATION_TYPE) := l_temp_rec;
g_receiving_1_select(ORIGINATION_DATE) := l_temp_rec;
g_receiving_1_select(ACTION_CODE) := l_temp_rec;
g_receiving_1_select(ACTION_DATE) := l_temp_rec;
g_receiving_1_select(RETEST_DATE) := l_temp_rec;
g_receiving_1_select(SECONDARY_UNPACKED) := l_temp_rec;
g_receiving_1_select(SECONDARY_PACKED) := l_temp_rec;
g_receiving_1_select(SUBINVENTORY_CODE) := l_temp_rec;
g_receiving_1_select(LOCATOR) := l_temp_rec;
g_receiving_1_select(LOCATOR_ID) := l_temp_rec;
g_receiving_1_select(LPN) := l_temp_rec;
g_receiving_1_select(LPN_ID) := l_temp_rec;
g_receiving_1_select(COST_GROUP) := l_temp_rec;
g_receiving_1_select(GRADE_CODE) := l_temp_rec;
g_receiving_1_select(CG_ID) := l_temp_rec;
g_receiving_1_select(LOADED) := l_temp_rec;
g_receiving_1_select(PLANNING_PARTY) := l_temp_rec;
g_receiving_1_select(PLANNING_PARTY_ID) := l_temp_rec;
g_receiving_1_select(OWNING_PARTY) := l_temp_rec;
g_receiving_1_select(OWNING_PARTY_ID) := l_temp_rec;
g_receiving_1_select(LOT) := l_temp_rec;
g_receiving_1_select(SERIAL) := l_temp_rec;
g_receiving_1_select(UNIT_NUMBER) := l_temp_rec;
g_receiving_1_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_receiving_1_select(ORGANIZATION_CODE) := l_temp_rec;
g_receiving_1_select(ORG_ID) := l_temp_rec;
g_receiving_1_select(ITEM) := l_temp_rec;
g_receiving_1_select(ITEM_DESCRIPTION) := l_temp_rec;
g_receiving_1_select(ITEM_ID) := l_temp_rec;
g_receiving_1_select(REVISION) := l_temp_rec;
g_receiving_1_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_receiving_1_select(ONHAND) := l_temp_rec;
g_receiving_1_select(RECEIVING) := l_temp_rec;
g_receiving_1_select(INBOUND) := l_temp_rec;
g_receiving_1_select(UNPACKED) := l_temp_rec;
g_receiving_1_select(PACKED) := l_temp_rec;
g_receiving_1_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_receiving_1_select(SECONDARY_ONHAND) := l_temp_rec;
g_receiving_1_select(SECONDARY_RECEIVING) := l_temp_rec;
g_receiving_1_select(SECONDARY_INBOUND) := l_temp_rec;
g_receiving_1_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_receiving_1_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_receiving_1_select(OWNING_TP_TYPE) := l_temp_rec;
g_receiving_1_select(PLANNING_TP_TYPE) := l_temp_rec;
g_receiving_1_select(STATUS) := l_temp_rec;
g_receiving_1_select(STATUS_ID) := l_temp_rec;
l_temp_rec SelectColumnRecType;
g_date_bind_tab.DELETE;
g_number_bind_tab.DELETE;
g_varchar_bind_tab.DELETE;
g_union_select.DELETE;
g_union_group.DELETE;
g_union_select(PO_RELEASE_ID) := l_temp_rec;
g_union_select(RELEASE_LINE_NUMBER) := l_temp_rec;
g_union_select(SHIPMENT_NUMBER) := l_temp_rec;
g_union_select(SHIPMENT_HEADER_ID_INTERORG) := l_temp_rec;
g_union_select(ASN) := l_temp_rec;
g_union_select(SHIPMENT_HEADER_ID_ASN) := l_temp_rec;
g_union_select(TRADING_PARTNER) := l_temp_rec;
g_union_select(VENDOR_ID) := l_temp_rec;
g_union_select(TRADING_PARTNER_SITE) := l_temp_rec;
g_union_select(VENDOR_SITE_ID) := l_temp_rec;
g_union_select(FROM_ORG) := l_temp_rec;
g_union_select(FROM_ORG_ID) := l_temp_rec;
g_union_select(TO_ORG) := l_temp_rec;
g_union_select(TO_ORG_ID) := l_temp_rec;
g_union_select(EXPECTED_RECEIPT_DATE) := l_temp_rec;
g_union_select(SHIPPED_DATE) := l_temp_rec;
g_union_select(OWNING_ORG) := l_temp_rec;
g_union_select(OWNING_ORG_ID) := l_temp_rec;
g_union_select(REQ_HEADER_ID) := l_temp_rec;
g_union_select(OE_HEADER_ID) := l_temp_rec;
g_union_select(PO_HEADER_ID) := l_temp_rec;
g_union_select(MATURITY_DATE) := l_temp_rec;
g_union_select(HOLD_DATE) := l_temp_rec;
g_union_select(SUPPLIER_LOT) := l_temp_rec;
g_union_select(PARENT_LOT) := l_temp_rec;
g_union_select(DOCUMENT_TYPE) := l_temp_rec;
g_union_select(DOCUMENT_TYPE_ID) := l_temp_rec;
g_union_select(DOCUMENT_NUMBER) := l_temp_rec;
g_union_select(DOCUMENT_LINE_NUMBER) := l_temp_rec;
g_union_select(RELEASE_NUMBER) := l_temp_rec;
g_union_select(ORIGINATION_TYPE) := l_temp_rec;
g_union_select(ORIGINATION_DATE) := l_temp_rec;
g_union_select(ACTION_CODE) := l_temp_rec;
g_union_select(ACTION_DATE) := l_temp_rec;
g_union_select(RETEST_DATE) := l_temp_rec;
g_union_select(SECONDARY_UNPACKED) := l_temp_rec;
g_union_select(SECONDARY_PACKED) := l_temp_rec;
g_union_select(SUBINVENTORY_CODE) := l_temp_rec;
g_union_select(LOCATOR) := l_temp_rec;
g_union_select(LOCATOR_ID) := l_temp_rec;
g_union_select(LPN) := l_temp_rec;
g_union_select(LPN_ID) := l_temp_rec;
g_union_select(COST_GROUP) := l_temp_rec;
g_union_select(CG_ID) := l_temp_rec;
g_union_select(LOADED) := l_temp_rec;
g_union_select(PLANNING_PARTY) := l_temp_rec;
g_union_select(PLANNING_PARTY_ID) := l_temp_rec;
g_union_select(OWNING_PARTY) := l_temp_rec;
g_union_select(OWNING_PARTY_ID) := l_temp_rec;
g_union_select(LOT) := l_temp_rec;
g_union_select(SERIAL) := l_temp_rec;
g_union_select(UNIT_NUMBER) := l_temp_rec;
g_union_select(LOT_EXPIRY_DATE) := l_temp_rec;
g_union_select(ORGANIZATION_CODE) := l_temp_rec;
g_union_select(ORG_ID) := l_temp_rec;
g_union_select(ITEM) := l_temp_rec;
g_union_select(ITEM_DESCRIPTION) := l_temp_rec;
g_union_select(ITEM_ID) := l_temp_rec;
g_union_select(REVISION) := l_temp_rec;
g_union_select(PRIMARY_UOM_CODE) := l_temp_rec;
g_union_select(ONHAND) := l_temp_rec;
g_union_select(RECEIVING) := l_temp_rec;
g_union_select(INBOUND) := l_temp_rec;
g_union_select(UNPACKED) := l_temp_rec;
g_union_select(PACKED) := l_temp_rec;
g_union_select(SECONDARY_UOM_CODE) := l_temp_rec;
g_union_select(SECONDARY_ONHAND) := l_temp_rec;
g_union_select(SECONDARY_RECEIVING) := l_temp_rec;
g_union_select(SECONDARY_INBOUND) := l_temp_rec;
g_union_select(OWNING_ORGANIZATION_ID) := l_temp_rec;
g_union_select(PLANNING_ORGANIZATION_ID) := l_temp_rec;
g_union_select(OWNING_TP_TYPE) := l_temp_rec;
g_union_select(PLANNING_TP_TYPE) := l_temp_rec;
g_union_select(STATUS) := l_temp_rec;
g_union_select(STATUS_ID) := l_temp_rec;
SELECT count(*)
INTO l_count
FROM wms_license_plate_numbers wlpn
WHERE wlpn.parent_lpn_id = inv_mwb_globals.g_tree_parent_lpn_id
AND wlpn.organization_id = inv_mwb_globals.g_tree_organization_id;
g_onhand_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'wlpn.subinventory_code';
g_onhand_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'wlpn.locator_id';
g_onhand_1_select(inv_mwb_query_manager.LPN).column_value :=
'wlpn.license_plate_number';
g_onhand_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
'wlpn.organization_id';
SELECT count(*)
INTO l_count
FROM wms_license_plate_numbers wlpn
WHERE wlpn.parent_lpn_id = inv_mwb_globals.g_tree_parent_lpn_id
AND wlpn.organization_id = inv_mwb_globals.g_tree_organization_id;
g_receiving_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'wlpn.subinventory_code';
g_receiving_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'wlpn.locator_id';
g_receiving_1_select(inv_mwb_query_manager.LPN).column_value :=
'wlpn.license_plate_number';
g_receiving_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
'wlpn.organization_id';
SELECT count(*)
INTO l_count
FROM wms_license_plate_numbers wlpn
WHERE wlpn.parent_lpn_id = inv_mwb_globals.g_tree_parent_lpn_id
AND wlpn.organization_id = inv_mwb_globals.g_tree_organization_id;
g_inbound_1_select(inv_mwb_query_manager.SUBINVENTORY_CODE).column_value :=
'wlpn.subinventory_code';
g_inbound_1_select(inv_mwb_query_manager.LOCATOR_ID).column_value :=
'wlpn.locator_id';
g_inbound_1_select(inv_mwb_query_manager.LPN).column_value :=
'wlpn.license_plate_number';
g_inbound_1_select(inv_mwb_query_manager.ORG_ID).column_value :=
'wlpn.organization_id';