1. Tables and Views for SCM
  2. SCH_REC_LOT_NUMBER_DTLS_V

SCH_REC_LOT_NUMBER_DTLS_V

Details

  • Schema: FUSION

  • Object owner: SCH

  • Object type: VIEW

Columns

Name

HEADER_ID

BUSINESS_UNIT_ID

LINE_ID

LOT_ID

MFG_LOT_NUMBER

ORGANIZATION_ID

INVENTORY_ITEM_ID

LOT_NUMBER

Query

SQL_Statement

select header_id, business_unit_id, line_id , lot_id, mfg_lot_number, organization_id,inventory_item_id, lot_number from (

SELECT lot.header_id, hdr.business_unit_id, lot.line_id , lot.lot_id, lot.mfg_lot_number, org.organization_id,line.inventory_item_id, inv_lot.lot_number

FROM inv_lot_numbers inv_lot, fusion.sch_rec_headers hdr, fusion.sch_rec_lines line, fusion.sch_rec_lot_numbers lot, fusion.INV_ORG_PARAMETERS_V org

WHERE hdr.header_id=line.header_id

and line.line_id=lot.line_id

and hdr.business_unit_id=org.business_unit_id

AND nvl(org.project_reference_enabled, 0) != 1

AND inv_lot.lot_number = lot.mfg_lot_number

AND inv_lot.inventory_item_id = line.inventory_item_id

AND inv_lot.organization_id = org.organization_id

UNION

SELECT lot.header_id, hdr.business_unit_id, lot.line_id , lot.lot_id, lot.mfg_lot_number, org.organization_id,line.inventory_item_id, itln.lot_number

FROM inv_transaction_lot_numbers itln , fusion.sch_rec_headers hdr, fusion.sch_rec_lines line, fusion.sch_rec_lot_numbers lot, fusion.INV_ORG_PARAMETERS_V org

WHERE hdr.header_id=line.header_id

and line.line_id=lot.line_id

and hdr.business_unit_id=org.business_unit_id

AND nvl(org.project_reference_enabled, 0) != 1

AND itln.supplier_lot_number = lot.mfg_lot_number

AND itln.inventory_item_id = line.inventory_item_id

AND itln.organization_id = org.organization_id

)