Here are the some commonly used SQLs/Tables in the Fusion SCM Cloud applications for implementation and support resources. This will help to understand the enterprise structure, setting up custom lookup and base tables in the SCM modules etc.,
1. Business Units: Business Unit is nothing but operating unit in Fusion applications
— Business Units
SELECT * FROM fusion.hr_operating_units
SELECT * FROM fusion.FUN_ALL_BUSINESS_UNITS_V — Operating units
— Ledgers
SELECT * FROM fusion.gl_ledgers
— Legal Entities
SELECT * FROM fusion.xle_entity_profiles
2. Inventory Organizations
— Inventory Org
SELECT * FROM fusion.inv_org_parameters
Note: org_organization_definitions isn’t valid table in Fusion applications
— Inventory Org Locations
SELECT * FROM fusion.hr_locations_all
3. Get Business Unit(s) and their inventory orgs, including ledger, legal entity for your enterprise
— Operating Units and their inventory orgs
SELECT op.business_unit_id,bu.bu_name, (select NAME from fusion.xle_entity_profiles WHERE legal_entity_id = op.legal_entity_id) legal_entity,GL.NAME LEDGER,
op.organization_id, op.organization_code Org_Code, HOU.name Org_Name, (SELECT location_name FROM fusion.hr_locations_all where location_id = HOU.location_id) Location
–HOU.internal_external_flag,HOU.date_from, HOU.date_to, HOU.object_version_number,
FROM fusion.FUN_ALL_BUSINESS_UNITS_V BU, fusion.inv_org_parameters OP, fusion.gl_ledgers gl, fusion.HR_ORGANIZATION_UNITS HOU, fusion.HR_ORG_UNIT_CLASSIFICATIONS_X HOUC
WHERE bu.bu_id = op.business_unit_id
AND bu.primary_ledger_id = gl.ledger_ID
AND hou.organization_id = HOUC.organization_id
and houc.CLASSIFICATION_CODE = ‘INV’
AND hou.organization_id = OP.organization_id
ORDER BY bu.bu_name, op.organization_code
4. Inventory
Item Definition:
SELECT * FROM fusion.egp_system_items_b
Onhand Quantities:
SELECT * FROM fusion.inv_onhand_quantities_detail
Completed Transactions (Material Transactions):
SELECT * FROM fusion.INV_MATERIAL_TXNS
5. Order Management
Sales Orders:
DOO_HEADERS_ALL, DOO_LINES_ALL, DOO_FULFILL_LINES_ALL, DOO_HEADERS_EFF_B, DOO_LINES_EFF_B, DOO_ORDER_ADDRESSES_V
Price Lists:
QP_PRICE_LISTS_VL, QP_PRICE_LIST_ITEMS, QP_PRICE_LIST_CHARGES
Shipping: Same as Oracle EBS Shipping tables.
WSH_NEW_DELIVERIES, WSH_DELIVERY_DETAILS, WSH_DELIVERY_ASSIGNMENTS AND WSH_FREIGHT_COSTS, WSH_CARRIERS
5. Procurement:
Suppliers:
POZ_SUPPLIERS, POZ_SUPPLIER_SITES_ALL_M
Requisitions:
POR_REQUISITION_HEADERS_ALL, POR_REQUISITION_LINES_ALL, POR_REQ_DISTRIBUTIONS_ALL
Requisition Interface:
POR_REQ_HEADERS_INTERFACE_ALL, POR_REQ_LINES_INTERFACE_ALL, POR_REQ_DISTS_INTERFACE_ALL and POR_REQ_IMPORT_ERRORS
Purchase Orders: Same as Oracle EBS PO tables
PO_HEADERS_ALL, PO_LINES_ALL, PO_LINE_LOCATIONS_ALL and PO_DISTRIBUTIONS_ALL
Purchase Order Interface: Same as Oracle EBS PO Interface tables
PO_HEADERS_INTERFACE, PO_LINES_INTERFACE, PO_LINE_LOCATIONS_INTERFACE, PO_DISTRIBUTIONS_INTERFACE and PO_INTERFACE_ERRORS
Receiving: Same as Oracle EBS Receiving tables
RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES, RCV_TRANSACTIONS