--Get Order with warehouse and Delivery Details
SELECT
--ooha.header_id,
--oola.line_id,
--ooha.org_id,
--ooha.ship_from_org_id,
ooha.order_number,
oola.line_number,
oola.ordered_item,
hou.name org_name,
wdd.source_header_type_name order_type,
(
SELECT organization_code
FROM apps.org_organization_definitions
WHERE organization_id = ooha.ship_from_org_id
)warehouse,
ooha.flow_status_code order_status,
oola.flow_status_code line_status,
oola.creation_date,
oola.schedule_ship_date,
oola.promise_date,
oola.ordered_quantity,
wdd.shipped_quantity,
wda.delivery_id delivery_or_shipment_number,
oola.unit_selling_price * oola.ordered_quantity line_total
FROM
apps.oe_order_headers_all ooha,
apps.oe_order_lines_all oola,
apps.hr_operating_units hou,
apps.wsh_delivery_details wdd,
apps.wsh_delivery_assignments wda
WHERE
1 = 1
AND ooha.header_id = oola.header_id
AND ooha.org_id = hou.organization_id
AND ooha.header_id = wdd.source_header_id (+)
AND oola.line_id = wdd.source_line_id (+)
AND wda.delivery_detail_id (+) = wdd.delivery_detail_id
AND ooha.order_number = '1656679'
ORDER BY 1,2;
--Get Order with Installed Base (IB) Details
SELECT ooha.order_number,
ottl.name,
oola.line_number,
oola.ordered_item,
ooha.flow_status_code order_status,
oola.flow_status_code line_status,
mtl.comms_nl_trackable_flag, --IB Trackable Flag
mtl.SHIPPABLE_ITEM_FLAG,
csi.instance_id
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
oe_transaction_types_tl ottl,
mtl_system_items_b mtl,
csi_item_instances csi
WHERE 1 = 1
AND ooha.header_id = oola.header_id
AND ottl.transaction_type_id = ooha.order_type_id
AND ottl.language = 'US'
AND mtl.segment1 = oola.ordered_item
AND mtl.ORGANIZATION_ID = oola.ship_from_org_id
AND csi.last_oe_order_line_id(+) = oola.line_id
AND ooha.order_number = '123';
SELECT mtl.inventory_item_id,
comms_nl_trackable_flag IB_Trackable,
mtl.SHIPPABLE_ITEM_FLAG,
organization_id,
mtl.segment1 item_name,
mtl.serial_number_control_code,
DECODE (mtl.serial_number_control_code,
1, 'No serial control',
5, 'At Receipt',
6, 'At Sales Issue',
2, 'Predefined',
'Invalid Value')
Serial_control,
mtl.lot_control_code,
DECODE (mtl.lot_control_code,
1, 'No lot control',
2, 'Full Control',
'Invalid Value')
Lot_control,
mtl.revision_qty_control_code,
DECODE (mtl.revision_qty_control_code,
1, 'No revision control',
2, 'Revision control enabled',
'Invalid Value')
Revision_control,
mtl.location_control_code,
DECODE (mtl.location_control_code,
1, 'No locator control',
2, 'Prespecified',
3, 'Dynamic Entry',
'Invalid Value')
Locator_control,
last_update_date
FROM mtl_system_items mtl
WHERE 1 = 1 --and mtl.inventory_item_id = (3837642)
AND mtl.segment1 = '186-3102-901' AND organization_id = 104;
--Link Between Sales Order and Material Transaction
SELECT (SELECT segment1
FROM mtl_system_items_b
WHERE INVENTORY_ITEM_ID = mmt.inventory_item_id
AND organization_id = 103)
Item_name,
(SELECT TRANSACTION_TYPE_NAME
FROM APPS.MTL_TRANSACTION_TYPES MTT
WHERE MTT.TRANSACTION_TYPE_ID = MMT.TRANSACTION_TYPE_ID)
transaction_type,
mmt.transaction_source_id,
(SELECT segment1
FROM mtl_sales_orders
WHERE sales_order_id = mmt.transaction_source_id)
Transaction_source,
mmt.*
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
mtl_material_transactions mmt
WHERE ooha.HEADER_ID = oola.HEADER_ID
AND oola.LINE_ID = mmt.TRX_SOURCE_LINE_ID
AND ooha.order_number = '1350910'
Comments
Post a Comment