/* ************************************************************************************************************
INBOUND METRICS
Lines in-transit
************************************************************************************************************ */
SELECT
mp.organization_code "Warehouse",
rsh.receipt_num "Receipt Number",
rsh.shipment_num "Shipment Number",
wt.name "Trip",
NULL "Putaway LPN",
NULL "Putaway Subinventory",
NULL "Putaway Locator",
msib.segment1 "Item",
(select c_ext_attr11||'/'||c_ext_attr12 from apps.ego_mtl_sy_items_ext_b where organization_id = 89 and inventory_item_id = msib.inventory_item_id and attr_group_id = 42) "Item Temperature",
(rsl.quantity_shipped-rsl.quantity_received) "Quantity",
decode(msib.serial_number_control_code,1,'No','Yes') "Serial Item",
(cic.item_cost * (rsl.quantity_shipped-rsl.quantity_received)) "Value",
gsob.currency_code "Currency",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN 'ISOLE'
WHEN rsl.source_document_code = 'REQ' THEN 'IR'
ELSE rsl.source_document_code
END "Document Type",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN SUBSTR(rsh.shipment_num,LENGTH(rsh.shipment_num)-2,3)
WHEN rsl.source_document_code = 'REQ' THEN (SELECT organization_code FROM apps.mtl_parameters WHERE organization_id = rsl.from_organization_id)
ELSE pv.vendor_name || ' - ' || pvsa.city || ' - ' || pvsa.country
END "Source",
CASE
WHEN pra.release_num IS NOT NULL THEN pha.segment1 || '/' || pra.release_num
WHEN pha.segment1 IS NOT NULL THEN pha.segment1
ELSE prha.segment1
END "Document Number",
NVL(pla.line_num,prla.line_num) "Document Line",
rsl.shipment_line_status_code "Document Line Status",
'In Transit' "Current Status",
(
SELECT
ffvl.description
FROM
apps.mtl_item_categories mic,
apps.mtl_categories_b mcb,
apps.fnd_flex_values_vl ffvl
WHERE
mic.organization_id = msib.organization_id
AND mic.inventory_item_id = msib.inventory_item_id
AND mic.category_set_id = '1100000155'
AND mcb.category_id = mic.category_id
AND ffvl.flex_value_meaning = mcb.segment2
AND ffvl.flex_value_set_id = 1018149
) "Distribution Planner",
rsl.to_subinventory "To Subinventory",
nvl(shippedlpn.lpn,wlpn_asn.license_plate_number) "Shipped in LPN",
msib_shippedlpn.segment1 "Container Type",
round(shippedlpn.gross_weight,3) "Container Weight (KGS)",
case
when msib_shippedlpn.segment1 = 'CC-PF' then round((shippedlpn.volume/1000000000),3)
when shippedlpn.volume_uom_code = 'CMQ' then round((shippedlpn.volume/1000000),3)
when shippedlpn.volume_uom_code = 'DMQ' then round((shippedlpn.volume/1000),3)
when shippedlpn.volume_uom_code = 'MTQ' then round((shippedlpn.volume),3)
else null
end "Container Volume (MTQ)",
flvv.meaning "Ship Method",
NULL "Task Status",
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(rsh.shipped_date, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Shipped Date", -- need validation
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(rsh.expected_receipt_date, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Expected Receipt Date", -- what to use for?
NULL "Receipt Date",
NULL "Loaded Timestamp",
NULL "Drop Off Timestamp",
CASE
WHEN rsl.source_document_code != 'REQ' THEN NULL
WHEN rsh.shipped_date IS NULL THEN NULL
ELSE round((nvl((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'),sysdate)-nvl(rsh.shipped_date,sysdate)),2)
END "In Transit Leadtime (Days)",
NULL "Receiving Leadtime (Hours)",
NULL "Total Leadtime (Hours)",
(select
max(fad.last_update_date||': '||fdst.short_text || ' ['||fu.description||']')
from
apps.fnd_attached_documents fad,
apps.fnd_documents fd,
apps.fnd_documents_short_text fdst,
apps.fnd_document_categories_tl fdct,
apps.fnd_user fu
where 1=1
and fu.user_id = fad.last_updated_by
and fd.document_id = fad.document_id
and fdst.media_id = fd.media_id
and fdct.category_id = fd.category_id
and (fad.entity_name = 'RCV_HEADERS' and fad.pk1_value = rsh.shipment_header_id)
and fdct.user_name = 'Warehouse Comment'
) "Header Comment",
(select
max(fad.last_update_date||': '||fdst.short_text || ' ['||fu.description||']')
from
apps.fnd_attached_documents fad,
apps.fnd_documents fd,
apps.fnd_documents_short_text fdst,
apps.fnd_document_categories_tl fdct,
apps.fnd_user fu
where 1=1
and fu.user_id = fad.last_updated_by
and fd.document_id = fad.document_id
and fdst.media_id = fd.media_id
and fdct.category_id = fd.category_id
and (fad.entity_name = 'RCV_LINES' and fad.pk1_value = rsl.shipment_line_id)
and fdct.user_name = 'Warehouse Comment'
) "Line Comment"
FROM
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.po_headers_all pha,
apps.po_releases_all pra,
apps.po_lines_all pla,
apps.po_requisition_headers_all prha,
apps.po_requisition_lines_all prla,
apps.po_vendor_sites_all pvsa,
apps.po_vendors pv,
apps.wms_license_plate_numbers wlpn_asn,
apps.cst_item_costs cic,
apps.org_organization_definitions ood,
apps.gl_sets_of_books gsob,
apps.wsh_new_deliveries wnd,
apps.wsh_delivery_legs wdl,
apps.wsh_trip_stops wts,
apps.wsh_trips wt,
apps.fnd_lookup_values_vl flvv,
(
SELECT
wdd.lpn_id,
wda.delivery_id,
wdd.organization_id,
nvl(wdd_outermost.container_name,nvl(wdd_parent.container_name,wdd.container_name)) lpn,
nvl(wdd_outermost.inventory_item_id,nvl(wdd_parent.inventory_item_id,wdd.inventory_item_id)) container_id,
nvl(wdd_outermost.gross_weight,nvl(wdd_parent.gross_weight,wdd.gross_weight)) gross_weight,
nvl(wdd_outermost.weight_uom_code,nvl(wdd_parent.weight_uom_code,wdd.weight_uom_code)) weight_uom_code,
nvl(wdd_outermost.volume,nvl(wdd_parent.volume,wdd.volume)) volume,
nvl(wdd_outermost.volume_uom_code,nvl(wdd_parent.volume_uom_code,wdd.volume_uom_code)) volume_uom_code
FROM
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_delivery_details wdd_parent,
apps.wsh_delivery_assignments wda_parent,
apps.wsh_delivery_details wdd_outermost,
apps.wms_license_plate_numbers wlpn
WHERE 1=1
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd_parent.delivery_detail_id(+) = wda.parent_delivery_detail_id
AND wda_parent.delivery_detail_id(+) = wdd_parent.delivery_detail_id
AND wdd_outermost.delivery_detail_id(+) = wda_parent.parent_delivery_detail_id
AND wlpn.lpn_id = wdd.lpn_id
) shippedlpn,
apps.mtl_system_items_b msib_shippedlpn
WHERE 1=1
-- In Receiving Specific Joins
AND rsl.shipment_line_status_code != 'FULLY RECEIVED'
AND rsh.shipment_header_id = rsl.shipment_header_id
AND wlpn_asn.lpn_id(+) = rsl.asn_lpn_id
AND msib.inventory_item_id = rsl.item_id
AND msib.organization_id = rsl.to_organization_id
AND pha.po_header_id(+) = rsl.po_header_id
AND pra.po_header_id(+) = rsl.po_header_id
AND pra.po_release_id(+) = rsl.po_release_id
AND pla.po_line_id(+) = rsl.po_line_id
AND prla.requisition_line_id(+) = rsl.requisition_line_id
AND prha.requisition_header_id(+) = prla.requisition_header_id
AND pvsa.vendor_id(+) = pha.vendor_id
AND pvsa.vendor_site_id(+) = pha.vendor_site_id
AND pv.vendor_id(+) = pha.vendor_id
AND cic.inventory_item_id(+) = rsl.item_id
AND cic.organization_id(+) = rsl.to_organization_id
AND cic.cost_type_id(+) = 1 -- Frozen cost type
AND ood.organization_id = rsl.to_organization_id
AND gsob.set_of_books_id = ood.set_of_books_id
AND wnd.delivery_id(+) = regexp_replace(rsh.shipment_num,'['||nvl(regexp_replace(rsh.shipment_num,'[0-9]'),'X')||']') -- Don't ask...
AND wdl.delivery_id(+) = wnd.delivery_id
AND wts.stop_id(+) = wdl.drop_off_stop_id
AND wt.trip_id(+) = wts.trip_id
AND flvv.lookup_type(+) = 'SHIP_METHOD'
AND flvv.lookup_code(+) = wnd.ship_method_code
-- Shipped LPN Joins
AND shippedlpn.lpn_id(+) = rsl.asn_lpn_id
AND shippedlpn.organization_id(+) = rsl.from_organization_id
AND msib_shippedlpn.inventory_item_id(+) = shippedlpn.container_id
AND msib_shippedlpn.organization_id(+) = shippedlpn.organization_id
AND mp.organization_id = rsl.to_organization_id
AND mp.organization_code in ('CNS') -- Specific Warehouses
AND rsl.source_document_code(+) != 'RMA' -- Filter out RMA
AND rsl.source_document_code = 'REQ'
AND (rsl.to_subinventory IS NULL OR rsl.to_subinventory NOT IN (SELECT secondary_inventory_name FROM apps.mtl_secondary_inventories WHERE attribute2 IN ('DEMO','SERVICE','OTHER')))
and rsh.shipment_num = '75281060' --'71402663'
--and nvl(shippedlpn.lpn,wlpn_asn.license_plate_number) = 'UPD2444574'-- 'UPD2445582'
;
/* ************************************************************************************************************
INBOUND METRICS
Lines in-receiving
************************************************************************************************************ */
SELECT
mp.organization_code "Warehouse",
rsh.receipt_num "Receipt Number",
rsh.shipment_num "Shipment Number",
wt.name "Trip",
wlpn_putaway.license_plate_number "Putaway LPN",
NULL "Putaway Subinventory",
NULL "Putaway Locator",
msib.segment1 "Item",
(select c_ext_attr11||'/'||c_ext_attr12 from apps.ego_mtl_sy_items_ext_b where organization_id = 89 and inventory_item_id = msib.inventory_item_id and attr_group_id = 42) "Item Temperature",
rs.quantity "Quantity",
decode(msib.serial_number_control_code,1,'No','Yes') "Serial Item",
(cic.item_cost * rs.quantity) "Value",
gsob.currency_code "Currency",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN 'ISOLE'
WHEN rsl.source_document_code = 'REQ' THEN 'IR'
ELSE rsl.source_document_code
END "Document Type",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN SUBSTR(rsh.shipment_num,LENGTH(rsh.shipment_num)-2,3)
WHEN rsl.source_document_code = 'REQ' THEN (SELECT organization_code FROM apps.mtl_parameters WHERE organization_id = rsl.from_organization_id)
ELSE pv.vendor_name || ' - ' || pvsa.city || ' - ' || pvsa.country
END "Source",
CASE
WHEN pra.release_num IS NOT NULL THEN pha.segment1 || '/' || pra.release_num
WHEN pha.segment1 IS NOT NULL THEN pha.segment1
ELSE prha.segment1
END "Document Number",
NVL(pla.line_num,prla.line_num) "Document Line",
rsl.shipment_line_status_code "Document Line Status",
CASE
WHEN rs.lpn_id IS NULL THEN 'Pending Packing'
ELSE 'Pending Putaway'
END "Current Status",
(
SELECT
ffvl.description
FROM
apps.mtl_item_categories mic,
apps.mtl_categories_b mcb,
apps.fnd_flex_values_vl ffvl
WHERE
mic.organization_id = msib.organization_id
AND mic.inventory_item_id = msib.inventory_item_id
AND mic.category_set_id = '1100000155'
AND mcb.category_id = mic.category_id
AND ffvl.flex_value_meaning = mcb.segment2
AND ffvl.flex_value_set_id = 1018149
) "Distribution Planner",
rsl.to_subinventory "To Subinventory",
nvl(shippedlpn.lpn,wlpn_asn.license_plate_number) "Shipped in LPN",
msib_shippedlpn.segment1 "Container Type",
round(shippedlpn.gross_weight,3) "Container Weight (KGS)",
case
when msib_shippedlpn.segment1 = 'CC-PF' then round((shippedlpn.volume/1000000000),3)
when shippedlpn.volume_uom_code = 'CMQ' then round((shippedlpn.volume/1000000),3)
when shippedlpn.volume_uom_code = 'DMQ' then round((shippedlpn.volume/1000),3)
when shippedlpn.volume_uom_code = 'MTQ' then round((shippedlpn.volume),3)
else null
end "Container Volume (MTQ)",
flvv.meaning "Ship Method",
CASE
WHEN wdt_extra.move_order_line_id IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = wdt_extra.status)
WHEN mmtt.wms_task_status IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = mmtt.wms_task_status)
WHEN wdt.move_order_line_id IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = wdt.status)
END AS "Task Status",
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(rsh.shipped_date, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Shipped Date", -- need validation
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(rsh.expected_receipt_date, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Expected Receipt Date", -- what to use for?
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Receipt Date",
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(nvl(wdt.loaded_time,wdt_extra.loaded_time), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Loaded Timestamp",
NULL "Drop Off Timestamp",
CASE
WHEN rsl.source_document_code != 'REQ' THEN NULL
WHEN rsh.shipped_date IS NULL THEN NULL
ELSE round(((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE')-nvl(rsh.shipped_date,sysdate)),2)
END "In Transit Leadtime (Days)",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN NULL
ELSE round((nvl(nvl(wdt.loaded_time,wdt_extra.loaded_time),sysdate)-(select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'))*24,2)
END "Receiving Leadtime (Hours)",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN NULL
ELSE round((sysdate-(select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'))*24,2)
END "Total Leadtime (Hours)",
(select
max(fad.last_update_date||': '||fdst.short_text || ' ['||fu.description||']')
from
apps.fnd_attached_documents fad,
apps.fnd_documents fd,
apps.fnd_documents_short_text fdst,
apps.fnd_document_categories_tl fdct,
apps.fnd_user fu
where 1=1
and fu.user_id = fad.last_updated_by
and fd.document_id = fad.document_id
and fdst.media_id = fd.media_id
and fdct.category_id = fd.category_id
and (fad.entity_name = 'RCV_HEADERS' and fad.pk1_value = rsh.shipment_header_id)
and fdct.user_name = 'Warehouse Comment'
) "Header Comment",
(select
max(fad.last_update_date||': '||fdst.short_text || ' ['||fu.description||']')
from
apps.fnd_attached_documents fad,
apps.fnd_documents fd,
apps.fnd_documents_short_text fdst,
apps.fnd_document_categories_tl fdct,
apps.fnd_user fu
where 1=1
and fu.user_id = fad.last_updated_by
and fd.document_id = fad.document_id
and fdst.media_id = fd.media_id
and fdct.category_id = fd.category_id
and (fad.entity_name = 'RCV_LINES' and fad.pk1_value = rsl.shipment_line_id)
and fdct.user_name = 'Warehouse Comment'
) "Line Comment"
FROM
apps.rcv_supply rs, -- FOR IN RECEIVING.
apps.mtl_txn_request_lines mtrl, -- FOR IN RECEIVING
apps.mtl_material_transactions_temp mmtt, -- FOR IN RECEIVING
apps.wms_dispatched_tasks wdt, -- FOR IN RECEIVING
apps.wms_dispatched_tasks wdt_extra, -- FOR IN RECEIVING
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.wms_license_plate_numbers wlpn_putaway,
apps.po_headers_all pha,
apps.po_releases_all pra,
apps.po_lines_all pla,
apps.po_requisition_headers_all prha,
apps.po_requisition_lines_all prla,
apps.po_vendor_sites_all pvsa,
apps.po_vendors pv,
apps.wms_license_plate_numbers wlpn_asn,
apps.cst_item_costs cic,
apps.org_organization_definitions ood,
apps.gl_sets_of_books gsob,
apps.wsh_new_deliveries wnd,
apps.wsh_delivery_legs wdl,
apps.wsh_trip_stops wts,
apps.wsh_trips wt,
apps.fnd_lookup_values_vl flvv,
(
SELECT
wdd.lpn_id,
wda.delivery_id,
wdd.organization_id,
nvl(wdd_outermost.container_name,nvl(wdd_parent.container_name,wdd.container_name)) lpn,
nvl(wdd_outermost.inventory_item_id,nvl(wdd_parent.inventory_item_id,wdd.inventory_item_id)) container_id,
nvl(wdd_outermost.gross_weight,nvl(wdd_parent.gross_weight,wdd.gross_weight)) gross_weight,
nvl(wdd_outermost.weight_uom_code,nvl(wdd_parent.weight_uom_code,wdd.weight_uom_code)) weight_uom_code,
nvl(wdd_outermost.volume,nvl(wdd_parent.volume,wdd.volume)) volume,
nvl(wdd_outermost.volume_uom_code,nvl(wdd_parent.volume_uom_code,wdd.volume_uom_code)) volume_uom_code
FROM
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_delivery_details wdd_parent,
apps.wsh_delivery_assignments wda_parent,
apps.wsh_delivery_details wdd_outermost,
apps.wms_license_plate_numbers wlpn
WHERE 1=1
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd_parent.delivery_detail_id(+) = wda.parent_delivery_detail_id
AND wda_parent.delivery_detail_id(+) = wdd_parent.delivery_detail_id
AND wdd_outermost.delivery_detail_id(+) = wda_parent.parent_delivery_detail_id
AND wlpn.lpn_id = wdd.lpn_id
) shippedlpn,
apps.mtl_system_items_b msib_shippedlpn
WHERE 1=1
-- In Receiving Specific Joins
AND rsh.shipment_header_id(+) = rs.shipment_header_id
AND rsl.shipment_line_id(+) = rs.shipment_line_id
AND mtrl.lpn_id(+) = rs.lpn_id
AND mtrl.inventory_item_id(+) = rs.item_id
AND mtrl.reference_id(+) = rs.shipment_line_id -- Temp fix to be evaluated 12-NOV-2013
AND wlpn_putaway.lpn_id(+) = rs.lpn_id
AND rs.item_id IS NOT NULL -- No non-items
AND wdt.move_order_line_id(+) = mtrl.line_id
AND mmtt.move_order_line_id(+) = mtrl.line_id
AND wdt_extra.transaction_temp_id(+) = mmtt.transaction_temp_id
AND wdt_extra.status(+) IN (2,3,4)
AND wdt.status(+) NOT IN (2,3,4) -- Filter loaded and queued duplicates
AND wlpn_asn.lpn_id(+) = rsl.asn_lpn_id
AND msib.inventory_item_id = rsl.item_id
AND msib.organization_id = rsl.to_organization_id
AND pha.po_header_id(+) = rsl.po_header_id
AND pra.po_header_id(+) = rsl.po_header_id
AND pra.po_release_id(+) = rsl.po_release_id
AND pla.po_line_id(+) = rsl.po_line_id
AND prla.requisition_line_id(+) = rsl.requisition_line_id
AND prha.requisition_header_id(+) = prla.requisition_header_id
AND pvsa.vendor_id(+) = pha.vendor_id
AND pvsa.vendor_site_id(+) = pha.vendor_site_id
AND pv.vendor_id(+) = pha.vendor_id
AND cic.inventory_item_id(+) = rsl.item_id
AND cic.organization_id(+) = rsl.to_organization_id
AND cic.cost_type_id(+) = 1 -- Frozen cost type
AND ood.organization_id = rsl.to_organization_id
AND gsob.set_of_books_id = ood.set_of_books_id
AND wnd.delivery_id(+) = regexp_replace(rsh.shipment_num,'['||nvl(regexp_replace(rsh.shipment_num,'[0-9]'),'X')||']') -- Don't ask...
AND wdl.delivery_id(+) = wnd.delivery_id
AND wts.stop_id(+) = wdl.drop_off_stop_id
AND wt.trip_id(+) = wts.trip_id
AND flvv.lookup_type(+) = 'SHIP_METHOD'
AND flvv.lookup_code(+) = wnd.ship_method_code
-- Shipped LPN Joins
AND shippedlpn.lpn_id(+) = rsl.asn_lpn_id
AND shippedlpn.organization_id(+) = rsl.from_organization_id
AND msib_shippedlpn.inventory_item_id(+) = shippedlpn.container_id
AND msib_shippedlpn.inventory_item_id(+) = shippedlpn.container_id
AND msib_shippedlpn.organization_id(+) = shippedlpn.organization_id
AND mp.organization_id = rsl.to_organization_id
AND mp.organization_code in ('CAD') -- Specific Warehouses
AND rsl.source_document_code(+) != 'RMA' -- Filter out RMA
AND (rsl.to_subinventory IS NULL OR rsl.to_subinventory NOT IN (SELECT secondary_inventory_name FROM apps.mtl_secondary_inventories WHERE attribute2 IN ('DEMO','SERVICE','OTHER')))
and rsh.shipment_num = '75668199' --
--and nvl(shippedlpn.lpn,wlpn_asn.license_plate_number) = 'UPD2444574'
--and msib_shippedlpn.segment1 = 'CC-L_STOR-M10'
;
/* ************************************************************************************************************
INBOUND METRICS
Delivered lines - restrict how far back to look at the end
************************************************************************************************************ */
SELECT
mp.organization_code "Warehouse",
rsh.receipt_num "Receipt Number",
rsh.shipment_num "Shipment Number",
wt.name "Trip",
wlpn_putaway.license_plate_number "Putaway LPN",
rt.subinventory "Putaway Subinventory",
(select milk.concatenated_segments from apps.mtl_item_locations_kfv milk where milk.inventory_location_id = rt.locator_id) "Putaway Locator",
msib.segment1 "Item",
DECODE(msib.lot_control_code,1,'No','Yes') "Lot Control",
DECODE(msib.serial_number_control_code,1,'No','Yes') "Serial Control",
DECODE(msib.shelf_life_code,1,'No','Yes') "Expiry Control",
(select c_ext_attr11||'/'||c_ext_attr12 from apps.ego_mtl_sy_items_ext_b where organization_id = 89 and inventory_item_id = msib.inventory_item_id and attr_group_id = 42) "Item Temperature",
rt.quantity "Quantity",
decode(msib.serial_number_control_code,1,'No','Yes') "Serial Item",
(cic.item_cost * rt.quantity) "Value",
gsob.currency_code "Currency",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN 'ISOLE'
WHEN rsl.source_document_code = 'REQ' THEN 'IR'
ELSE rsl.source_document_code
END "Document Type",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN SUBSTR(rsh.shipment_num,LENGTH(rsh.shipment_num)-2,3)
WHEN rsl.source_document_code = 'REQ' THEN (SELECT organization_code FROM apps.mtl_parameters WHERE organization_id = rsl.from_organization_id)
ELSE pv.vendor_name || ' - ' || pvsa.city || ' - ' || pvsa.country
END "Source",
CASE
WHEN pra.release_num IS NOT NULL THEN pha.segment1 || '/' || pra.release_num
WHEN pha.segment1 IS NOT NULL THEN pha.segment1
ELSE prha.segment1
END "Document Number",
NVL(pla.line_num,prla.line_num) "Document Line",
rsl.shipment_line_status_code "Document Line Status",
'Completed' "Current Status",
(
SELECT
ffvl.description
FROM
apps.mtl_item_categories mic,
apps.mtl_categories_b mcb,
apps.fnd_flex_values_vl ffvl
WHERE
mic.organization_id = msib.organization_id
AND mic.inventory_item_id = msib.inventory_item_id
AND mic.category_set_id = '1100000155'
AND mcb.category_id = mic.category_id
AND ffvl.flex_value_meaning = mcb.segment2
AND ffvl.flex_value_set_id = 1018149
) "Distribution Planner",
rsl.to_subinventory "To Subinventory",
nvl(shippedlpn.lpn,wlpn_asn.license_plate_number) "Shipped in LPN",
msib_shippedlpn.segment1 "Container Type",
round(shippedlpn.gross_weight,3) "Container Weight (KGS)",
case
when msib_shippedlpn.segment1 = 'CC-PF' then round((shippedlpn.volume/1000000000),3)
when shippedlpn.volume_uom_code = 'CMQ' then round((shippedlpn.volume/1000000),3)
when shippedlpn.volume_uom_code = 'DMQ' then round((shippedlpn.volume/1000),3)
when shippedlpn.volume_uom_code = 'MTQ' then round((shippedlpn.volume),3)
else null
end "Container Volume (MTQ)",
flvv.meaning "Ship Method",
CASE
WHEN wdth.move_order_line_id IS NOT NULL THEN (SELECT MEANING FROM apps.FND_LOOKUP_VALUES_VL WHERE lookup_type = 'WMS_TASK_STATUS' AND LOOKUP_CODE = wdth.status)
END AS "Task Status",
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(rsh.shipped_date, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Shipped Date", -- need validation
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(rsh.expected_receipt_date, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Expected Receipt Date", -- what to use for?
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'), 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Receipt Date",
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(wdth.loaded_time, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Loaded Timestamp",
CASE
WHEN mp.organization_code = 'PYD' THEN TO_CHAR(FROM_TZ(CAST(TO_CHAR(wdth.drop_off_time, 'YYYY-MM-DD HH24:MI:SS') AS TIMESTAMP), 'UTC') AT TIME ZONE 'America/New_York', 'YYYY-MM-DD HH24:MI:SS')
END "Drop Off Timestamp",
CASE
WHEN rsl.source_document_code != 'REQ' THEN NULL
WHEN rsh.shipped_date IS NULL THEN NULL
ELSE round(((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE')-nvl(rsh.shipped_date,sysdate)),2)
END "In Transit Leadtime (Days)",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN NULL
ELSE round((nvl(wdth.loaded_time,sysdate)-(select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'))*24,2)
END "Receiving Leadtime (Hours)",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN NULL
ELSE round((nvl(wdth.drop_off_time,sysdate)-(select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'))*24,2)
END "Total Leadtime (Hours)",
(select
max(fad.last_update_date||': '||fdst.short_text || ' ['||fu.description||']')
from
apps.fnd_attached_documents fad,
apps.fnd_documents fd,
apps.fnd_documents_short_text fdst,
apps.fnd_document_categories_tl fdct,
apps.fnd_user fu
where 1=1
and fu.user_id = fad.last_updated_by
and fd.document_id = fad.document_id
and fdst.media_id = fd.media_id
and fdct.category_id = fd.category_id
and (fad.entity_name = 'RCV_HEADERS' and fad.pk1_value = rsh.shipment_header_id)
and fdct.user_name = 'Warehouse Comment'
) "Header Comment",
(select
max(fad.last_update_date||': '||fdst.short_text || ' ['||fu.description||']')
from
apps.fnd_attached_documents fad,
apps.fnd_documents fd,
apps.fnd_documents_short_text fdst,
apps.fnd_document_categories_tl fdct,
apps.fnd_user fu
where 1=1
and fu.user_id = fad.last_updated_by
and fd.document_id = fad.document_id
and fdst.media_id = fd.media_id
and fdct.category_id = fd.category_id
and (fad.entity_name = 'RCV_LINES' and fad.pk1_value = rsl.shipment_line_id)
and fdct.user_name = 'Warehouse Comment'
) "Line Comment"
FROM
apps.rcv_transactions rt, -- FOR DELIVER.
apps.wms_dispatched_tasks_history wdth, -- FOR DELIVER
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.wms_license_plate_numbers wlpn_putaway,
apps.po_headers_all pha,
apps.po_releases_all pra,
apps.po_lines_all pla,
apps.po_requisition_headers_all prha,
apps.po_requisition_lines_all prla,
apps.po_vendor_sites_all pvsa,
apps.po_vendors pv,
apps.wms_license_plate_numbers wlpn_asn,
apps.cst_item_costs cic,
apps.org_organization_definitions ood,
apps.gl_sets_of_books gsob,
apps.wsh_new_deliveries wnd,
apps.wsh_delivery_legs wdl,
apps.wsh_trip_stops wts,
apps.wsh_trips wt,
apps.fnd_lookup_values_vl flvv,
(
SELECT
wdd.lpn_id,
wda.delivery_id,
wdd.organization_id,
nvl(wdd_outermost.container_name,nvl(wdd_parent.container_name,wdd.container_name)) lpn,
nvl(wdd_outermost.inventory_item_id,nvl(wdd_parent.inventory_item_id,wdd.inventory_item_id)) container_id,
nvl(wdd_outermost.gross_weight,nvl(wdd_parent.gross_weight,wdd.gross_weight)) gross_weight,
nvl(wdd_outermost.weight_uom_code,nvl(wdd_parent.weight_uom_code,wdd.weight_uom_code)) weight_uom_code,
nvl(wdd_outermost.volume,nvl(wdd_parent.volume,wdd.volume)) volume,
nvl(wdd_outermost.volume_uom_code,nvl(wdd_parent.volume_uom_code,wdd.volume_uom_code)) volume_uom_code
FROM
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_delivery_details wdd_parent,
apps.wsh_delivery_assignments wda_parent,
apps.wsh_delivery_details wdd_outermost,
apps.wms_license_plate_numbers wlpn
WHERE 1=1
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd_parent.delivery_detail_id(+) = wda.parent_delivery_detail_id
AND wda_parent.delivery_detail_id(+) = wdd_parent.delivery_detail_id
AND wdd_outermost.delivery_detail_id(+) = wda_parent.parent_delivery_detail_id
AND wlpn.lpn_id = wdd.lpn_id
) shippedlpn,
apps.mtl_system_items_b msib_shippedlpn
WHERE 1=1
-- Deliver Joins
AND rsh.shipment_header_id(+) = rt.shipment_header_id
AND rsl.shipment_line_id(+) = rt.shipment_line_id
AND wlpn_putaway.lpn_id(+) = rt.lpn_id
AND wdth.lpn_id(+) = rt.lpn_id
AND wdth.source_document_id(+) = rt.transaction_id
AND wdth.status(+) != '11' -- Filter Aborted tasks
AND wlpn_asn.lpn_id(+) = rsl.asn_lpn_id
AND msib.inventory_item_id = rsl.item_id
AND msib.organization_id = rsl.to_organization_id
AND pha.po_header_id(+) = rsl.po_header_id
AND pra.po_header_id(+) = rsl.po_header_id
AND pra.po_release_id(+) = rsl.po_release_id
AND pla.po_line_id(+) = rsl.po_line_id
AND prla.requisition_line_id(+) = rsl.requisition_line_id
AND prha.requisition_header_id(+) = prla.requisition_header_id
AND pvsa.vendor_id(+) = pha.vendor_id
AND pvsa.vendor_site_id(+) = pha.vendor_site_id
AND pv.vendor_id(+) = pha.vendor_id
AND cic.inventory_item_id(+) = rsl.item_id
AND cic.organization_id(+) = rsl.to_organization_id
AND cic.cost_type_id(+) = 1 -- Frozen cost type
AND ood.organization_id = rsl.to_organization_id
AND gsob.set_of_books_id = ood.set_of_books_id
AND wnd.delivery_id(+) = regexp_replace(rsh.shipment_num,'['||nvl(regexp_replace(rsh.shipment_num,'[0-9]'),'X')||']') -- Don't ask...
AND wdl.delivery_id(+) = wnd.delivery_id
AND wts.stop_id(+) = wdl.drop_off_stop_id
AND wt.trip_id(+) = wts.trip_id
AND flvv.lookup_type(+) = 'SHIP_METHOD'
AND flvv.lookup_code(+) = wnd.ship_method_code
-- Shipped LPN Joins
AND shippedlpn.lpn_id(+) = rsl.asn_lpn_id
AND shippedlpn.organization_id(+) = rsl.from_organization_id
AND msib_shippedlpn.inventory_item_id(+) = shippedlpn.container_id
AND msib_shippedlpn.organization_id(+) = shippedlpn.organization_id
AND mp.organization_id = rsl.to_organization_id
AND mp.organization_code in ('SGD') -- Specific Warehouses
AND rsl.source_document_code(+) != 'RMA' -- Filter out RMA
AND (rsl.to_subinventory IS NULL OR rsl.to_subinventory NOT IN (SELECT secondary_inventory_name FROM apps.mtl_secondary_inventories WHERE attribute2 IN ('DEMO','SERVICE','OTHER')))
AND rt.transaction_type = 'DELIVER'
--AND rt.transaction_date > sysdate-2 -- CHANGE HERE TO LOOK FURTHER BACK
and rsh.shipment_num = '75666676'
--and nvl(shippedlpn.lpn,wlpn_asn.license_plate_number) = 'UPD2445582'
;
/**********************
Serial Intransit that will crash
***********************/
SELECT
mp.organization_code "Warehouse",
rsh.receipt_num "Receipt Number",
rsh.shipment_num "Shipment Number",
NULL "Putaway LPN",
msib.segment1 "Item",
msn.serial_number "Serial Number",
flvv_sns.meaning "Serial Status",
'1' "Quantity",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN 'ISOLE'
WHEN rsl.source_document_code = 'REQ' THEN 'IR'
ELSE rsl.source_document_code
END "Document Type",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN SUBSTR(rsh.shipment_num,LENGTH(rsh.shipment_num)-2,3)
WHEN rsl.source_document_code = 'REQ' THEN (SELECT organization_code FROM apps.mtl_parameters WHERE organization_id = rsl.from_organization_id)
ELSE pvsa.vendor_site_code_alt || ' - ' || pvsa.city || ' - ' || pvsa.country
END "Source",
CASE
WHEN pra.release_num IS NOT NULL THEN pha.segment1 || '/' || pra.release_num
WHEN pha.segment1 IS NOT NULL THEN pha.segment1
ELSE prha.segment1
END "Document Number",
NVL(pla.line_num,prla.line_num) "Document Line",
rsl.shipment_line_status_code "Document Line Status",
'In Transit' "Current Status",
rsl.to_subinventory "To Subinventory",
(
SELECT nvl(nvl(wdd_parent.container_name,wdd.container_name),wlpn_asn.license_plate_number)
FROM
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda,
apps.wsh_delivery_details wdd_parent
WHERE 1=1
AND wda.delivery_detail_id = wdd.delivery_detail_id
AND wdd_parent.delivery_detail_id(+) = wda.parent_delivery_detail_id
AND wdd.container_name = wlpn_asn.license_plate_number
) "Shipped in LPN",
CASE
WHEN rsl.source_document_code != 'REQ' THEN NULL
WHEN rsh.shipped_date IS NULL THEN NULL
ELSE round((nvl((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'),sysdate)-nvl(rsh.shipped_date,sysdate)),2)
END "In Transit Leadtime (Days)",
NULL "Receiving Leadtime (Hours)"
FROM
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.po_headers_all pha,
apps.po_releases_all pra,
apps.po_lines_all pla,
apps.po_requisition_headers_all prha,
apps.po_requisition_lines_all prla,
apps.po_vendor_sites_all pvsa,
apps.wms_license_plate_numbers wlpn_asn,
apps.cst_item_costs cic,
apps.org_organization_definitions ood,
apps.gl_sets_of_books gsob,
apps.fnd_lookup_values_vl flvv_sns,
apps.mtl_serial_numbers msn
WHERE 1=1
AND flvv_sns.lookup_code = msn.current_status
AND flvv_sns.lookup_type = 'SERIAL_NUM_STATUS'
AND msn.last_transaction_id = rsl.mmt_transaction_id
-- In Receiving Specific Joins
AND rsl.shipment_line_status_code != 'FULLY RECEIVED'
AND rsh.shipment_header_id = rsl.shipment_header_id
AND wlpn_asn.lpn_id(+) = rsl.asn_lpn_id
AND msib.inventory_item_id = rsl.item_id
AND msib.organization_id = rsl.to_organization_id
AND pha.po_header_id(+) = rsl.po_header_id
AND pra.po_header_id(+) = rsl.po_header_id
AND pra.po_release_id(+) = rsl.po_release_id
AND pla.po_line_id(+) = rsl.po_line_id
AND prla.requisition_line_id(+) = rsl.requisition_line_id
AND prha.requisition_header_id(+) = prla.requisition_header_id
AND pvsa.vendor_id(+) = pha.vendor_id
AND pvsa.vendor_site_id(+) = pha.vendor_site_id
AND cic.inventory_item_id = rsl.item_id
AND cic.organization_id = rsl.to_organization_id
AND cic.cost_type_id = 1 -- Frozen cost type
AND ood.organization_id = rsl.to_organization_id
AND gsob.set_of_books_id = ood.set_of_books_id
AND mp.organization_id = rsl.to_organization_id
AND mp.organization_code in ('SGD') -- Specific Warehouses
AND rsl.source_document_code(+) != 'RMA' -- Filter out RMA
AND rsl.source_document_code = 'REQ'
AND flvv_sns.meaning NOT IN ('Resides in intransit')
AND msib.serial_number_control_code != '1'
/****************************
Serials in Receiving already crashed
*****************************/
SELECT
mp.organization_code "Warehouse",
rsh.receipt_num "Receipt Number",
rsh.shipment_num "Shipment Number",
wlpn_putaway.license_plate_number "Putaway LPN",
msib.segment1 "Item",
rss.serial_num "Serial Number",
flvv_sns.meaning "Serial Status",
'1' "Quantity",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN 'ISOLE'
WHEN rsl.source_document_code = 'REQ' THEN 'IR'
ELSE rsl.source_document_code
END "Document Type",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN SUBSTR(rsh.shipment_num,LENGTH(rsh.shipment_num)-2,3)
WHEN rsl.source_document_code = 'REQ' THEN (SELECT organization_code FROM apps.mtl_parameters WHERE organization_id = rsl.from_organization_id)
ELSE pvsa.vendor_site_code_alt || ' - ' || pvsa.city || ' - ' || pvsa.country
END "Source",
CASE
WHEN pra.release_num IS NOT NULL THEN pha.segment1 || '/' || pra.release_num
WHEN pha.segment1 IS NOT NULL THEN pha.segment1
ELSE prha.segment1
END "Document Number",
NVL(pla.line_num,prla.line_num) "Document Line",
rsl.shipment_line_status_code "Document Line Status",
CASE
WHEN rs.lpn_id IS NULL THEN 'Pending Packing'
ELSE 'Pending Putaway'
END "Current Status",
rsl.to_subinventory "To Subinventory",
wlpn_asn.license_plate_number "Shipped in LPN",
CASE
WHEN rsl.source_document_code != 'REQ' THEN NULL
WHEN rsh.shipped_date IS NULL THEN NULL
ELSE round(((select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE')-nvl(rsh.shipped_date,sysdate)),2)
END "In Transit Leadtime (Days)",
CASE
WHEN rsh.shipment_num LIKE 'ISO%' THEN NULL
ELSE round((nvl(nvl(wdt.loaded_time,wdt_extra.loaded_time),sysdate)-(select min(transaction_date) from apps.rcv_transactions where shipment_line_id = rsl.shipment_line_id and transaction_type = 'RECEIVE'))*24,2)
END "Receiving Leadtime (Hours)"
FROM
apps.rcv_supply rs, -- FOR IN RECEIVING.
apps.mtl_txn_request_lines mtrl, -- FOR IN RECEIVING
apps.mtl_material_transactions_temp mmtt, -- FOR IN RECEIVING
apps.wms_dispatched_tasks wdt, -- FOR IN RECEIVING
apps.wms_dispatched_tasks wdt_extra, -- FOR IN RECEIVING
apps.rcv_shipment_headers rsh,
apps.rcv_shipment_lines rsl,
apps.mtl_parameters mp,
apps.mtl_system_items_b msib,
apps.wms_license_plate_numbers wlpn_putaway,
apps.po_headers_all pha,
apps.po_releases_all pra,
apps.po_lines_all pla,
apps.po_requisition_headers_all prha,
apps.po_requisition_lines_all prla,
apps.po_vendor_sites_all pvsa,
apps.wms_license_plate_numbers wlpn_asn,
apps.rcv_serials_supply rss,
apps.fnd_lookup_values_vl flvv_sns,
apps.mtl_serial_numbers msn
WHERE 1=1
AND flvv_sns.lookup_code(+) = msn.current_status
AND flvv_sns.lookup_type(+) = 'SERIAL_NUM_STATUS'
AND msn.serial_number(+) = rss.serial_num
-- In Receiving Specific Joins
AND rsh.shipment_header_id(+) = rs.shipment_header_id
AND rsl.shipment_line_id(+) = rs.shipment_line_id
AND mtrl.lpn_id(+) = rs.lpn_id
AND mtrl.inventory_item_id(+) = rs.item_id
AND wlpn_putaway.lpn_id(+) = rs.lpn_id
AND rs.item_id IS NOT NULL -- No non-items
AND wdt.move_order_line_id(+) = mtrl.line_id
AND mmtt.move_order_line_id(+) = mtrl.line_id
AND wdt_extra.transaction_temp_id(+) = mmtt.transaction_temp_id
AND wdt_extra.status(+) IN (2,3,4)
AND wdt.status(+) NOT IN (2,3,4) -- Filter loaded and queued duplicates
AND wlpn_asn.lpn_id(+) = rsl.asn_lpn_id
AND msib.inventory_item_id = rsl.item_id
AND msib.organization_id = rsl.to_organization_id
AND pha.po_header_id(+) = rsl.po_header_id
AND pra.po_header_id(+) = rsl.po_header_id
AND pra.po_release_id(+) = rsl.po_release_id
AND pla.po_line_id(+) = rsl.po_line_id
AND prla.requisition_line_id(+) = rsl.requisition_line_id
AND prha.requisition_header_id(+) = prla.requisition_header_id
AND pvsa.vendor_id(+) = pha.vendor_id
AND pvsa.vendor_site_id(+) = pha.vendor_site_id
AND mp.organization_id = rsl.to_organization_id
AND mp.organization_code in ('SGD') -- Specific Warehouses
AND rsl.source_document_code(+) != 'RMA' -- Filter out RMA
AND rss.shipment_line_id = rsl.shipment_line_id
AND flvv_sns.meaning != 'Resides in receiving'
;
Comments
Post a Comment