Product: | CSD - Depot Repair |
---|---|
Description: | The view for getting all the valid customer products. |
Implementation/DBA Data: | APPS.CSD_CP_REFERENCE_V |
SELECT CP.INSTANCE_NUMBER
, CP.INSTANCE_ID
, CP.OWNER_PARTY_ACCOUNT_ID
, CP.INVENTORY_ITEM_ID
, ITEMS.CONCATENATED_SEGMENTS
, ITEMS.DESCRIPTION
, CP.SERIAL_NUMBER
, CP.UNIT_OF_MEASURE
, UOM.UNIT_OF_MEASURE_TL
, CP.LOT_NUMBER
, CP.INVENTORY_REVISION
, CP.QUANTITY
, CIOA.OPERATING_UNIT_ID
, CPS.INCIDENT_ALLOWED_FLAG
, CPS.NAME STATUS
FROM CSI_ITEM_INSTANCES CP
, CSI_INSTANCE_STATUSES CPS
, CSI_I_ORG_ASSIGNMENTS CIOA
, MTL_SYSTEM_ITEMS_VL ITEMS
, MTL_UNITS_OF_MEASURE_VL UOM
WHERE ITEMS.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
AND ITEMS.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CP.UNIT_OF_MEASURE = UOM.UOM_CODE
AND CP.INSTANCE_STATUS_ID = CPS.INSTANCE_STATUS_ID(+)
AND CPS.TERMINATED_FLAG (+)!= 'Y'
AND CPS.INSTANCE_STATUS_ID (+)!= 1
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(CP.ACTIVE_START_DATE
, SYSDATE))
AND TRUNC(NVL(CP.ACTIVE_END_DATE
, SYSDATE + 1))
AND CP.INSTANCE_ID = CIOA.INSTANCE_ID(+)
AND CIOA.RELATIONSHIP_TYPE_CODE(+)= 'SOLD_FROM'