SCH_REC_LINE_DETAILS

Details

Schema: FUSION
Object owner: SCH
Object type: VIEW

Columns

Name
HEADER_ID BUSINESS_UNIT_ID LINE_ID INVENTORY_ITEM_ID ORGANIZATION_ID LOT_ID MFG_LOT_NUMBER LOT_NUMBER SERIAL_ID MFG_SERIAL_NUMBER SERIAL_NUMBER

Name

HEADER_ID

BUSINESS_UNIT_ID

LINE_ID

INVENTORY_ITEM_ID

ORGANIZATION_ID

LOT_ID

MFG_LOT_NUMBER

LOT_NUMBER

SERIAL_ID

MFG_SERIAL_NUMBER

SERIAL_NUMBER

Query

SQL_Statement
select line.header_id, lots.business_unit_id, line.line_id,line.inventory_item_id, lots.organization_id, lots.lot_id, lots.mfg_lot_number,lots.lot_number, serial.serial_id, serial.mfg_serial_number,serial.serial_number from fusion.sch_rec_lines line, fusion.sch_rec_lot_number_dtls_v lots, fusion.sch_rec_serial_number_dtls_v serial where line.line_id=lots.line_id and line.line_id=serial.line_id(+) and lots.lot_id=serial.lot_id(+) and lots.organization_id=serial.organization_id(+) union all select line.header_id, serial.business_unit_id, line.line_id,line.inventory_item_id, serial.organization_id, null, null,null, serial.serial_id, serial.mfg_serial_number, serial.serial_number from fusion.sch_rec_lines line, fusion.sch_rec_serial_number_dtls_v serial where line.line_id=serial.line_id and serial.lot_id is null union all select line.header_id, hdr.business_unit_id, line.line_id,line.inventory_item_id, org.organization_id, null, null,null,null, null,null from fusion.sch_rec_lines line, fusion.sch_rec_headers hdr, fusion.INV_ORG_PARAMETERS_V org where hdr.header_id=line.header_id and hdr.business_unit_id=org.business_unit_id AND nvl(org.project_reference_enabled, 0) != 1 and not exists (select 1 from fusion.sch_rec_lot_number_dtls_v where line_id=line.line_id) and not exists (select 1 from fusion.sch_rec_serial_number_dtls_v where line_id=line.line_id)

SQL_Statement

select line.header_id, lots.business_unit_id, line.line_id,line.inventory_item_id, lots.organization_id, lots.lot_id, lots.mfg_lot_number,lots.lot_number, serial.serial_id, serial.mfg_serial_number,serial.serial_number

from fusion.sch_rec_lines line, fusion.sch_rec_lot_number_dtls_v lots, fusion.sch_rec_serial_number_dtls_v serial

where line.line_id=lots.line_id

and line.line_id=serial.line_id(+)

and lots.lot_id=serial.lot_id(+)

and lots.organization_id=serial.organization_id(+)

union all

select line.header_id, serial.business_unit_id, line.line_id,line.inventory_item_id, serial.organization_id, null, null,null, serial.serial_id, serial.mfg_serial_number, serial.serial_number

from fusion.sch_rec_lines line, fusion.sch_rec_serial_number_dtls_v serial

where

line.line_id=serial.line_id

and serial.lot_id is null

union all

select line.header_id, hdr.business_unit_id, line.line_id,line.inventory_item_id, org.organization_id, null, null,null,null, null,null

from fusion.sch_rec_lines line, fusion.sch_rec_headers hdr, fusion.INV_ORG_PARAMETERS_V org

where

hdr.header_id=line.header_id

and hdr.business_unit_id=org.business_unit_id

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

and not exists (select 1 from fusion.sch_rec_lot_number_dtls_v where line_id=line.line_id)

and not exists (select 1 from fusion.sch_rec_serial_number_dtls_v where line_id=line.line_id)