VIEW: APPS.CS_SR_NEW_PRODUCTS_MAINT_V

Source

View Text - Preformatted

SELECT HZP.PARTY_NAME PRINCIPAL_PARTY_NAME, ITEM.INSTANCE_ID , ITEM.OWNER_PARTY_ACCOUNT_ID PARTY_ACCOUNT_ID, ITEM.INVENTORY_ITEM_ID, 'Y' MOST_RECENT_FLAG , ITEM.SERIAL_NUMBER , ITEM.INSTANCE_TYPE_CODE , ITEM.SYSTEM_ID , SYS.NAME SYSTEM, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER, ITEM.LOCATION_ID, ITEM.INSTANCE_STATUS_ID , ITEM.INSTANCE_NUMBER , OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER , ITEM.LOT_NUMBER , ITEM.INVENTORY_REVISION, SI.DESCRIPTION PRODUCT_DESCRIPTION , L2.LOOKUP_CODE SERVICED_STATUS_FLAG, OOL.LINE_NUMBER ORIGINAL_LINE_NUMBER , OOL.ACTUAL_SHIPMENT_DATE, ITEM.QUANTITY , INST.NAME CP_STATUS, HZA.ACCOUNT_NUMBER, ITEM.EXTERNAL_REFERENCE, ITEM.OWNER_PARTY_ID, ITEM.OWNER_PARTY_SOURCE_TABLE, ITEM.LOCATION_TYPE_CODE FROM HZ_CUST_ACCOUNTS HZA, HZ_PARTIES HZP, CS_LOOKUPS L2, MTL_SYSTEM_ITEMS SI, ASO_I_OE_ORDER_HEADERS_V OOH, ASO_I_OE_ORDER_LINES_V OOL, CSI_ITEM_INSTANCES ITEM, CSI_SYSTEMS_TL SYS, CSI_INSTANCE_STATUSES INST WHERE ITEM.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID AND ITEM.LOCATION_TYPE_CODE IN ( 'HZ_PARTY_SITES','HZ_LOCATIONS') AND ITEM.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND SI.ORGANIZATION_ID = CS_STD.Get_Item_Valdn_Orgzn_Id AND L2.LOOKUP_TYPE = 'CP_SERVICE_STATUS' AND L2.LOOKUP_CODE = CS_STD.CS_GET_SERVICED_STATUS(ITEM.INSTANCE_ID) AND ITEM.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+) AND OOL.HEADER_ID = OOH.HEADER_ID(+) AND ITEM.SYSTEM_ID = SYS.SYSTEM_ID(+) AND SYS.LANGUAGE(+) = userenv('LANG') AND ITEM.OWNER_PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID AND HZA.PARTY_ID = HZP.PARTY_ID AND INST.INSTANCE_STATUS_ID = ITEM.INSTANCE_STATUS_ID AND INST.INCIDENT_ALLOWED_FLAG = 'Y' AND SI.SERV_REQ_ENABLED_CODE = 'E' AND SI.COMMS_NL_TRACKABLE_FLAG='Y'

View Text - HTML Formatted

SELECT HZP.PARTY_NAME PRINCIPAL_PARTY_NAME
, ITEM.INSTANCE_ID
, ITEM.OWNER_PARTY_ACCOUNT_ID PARTY_ACCOUNT_ID
, ITEM.INVENTORY_ITEM_ID
, 'Y' MOST_RECENT_FLAG
, ITEM.SERIAL_NUMBER
, ITEM.INSTANCE_TYPE_CODE
, ITEM.SYSTEM_ID
, SYS.NAME SYSTEM
, OOH.ORDERED_DATE ORIGINAL_ORDER_DATE
, OOH.ORDER_NUMBER ORIGINAL_ORDER_NUMBER
, ITEM.LOCATION_ID
, ITEM.INSTANCE_STATUS_ID
, ITEM.INSTANCE_NUMBER
, OOH.CUST_PO_NUMBER PURCHASE_ORDER_NUMBER
, ITEM.LOT_NUMBER
, ITEM.INVENTORY_REVISION
, SI.DESCRIPTION PRODUCT_DESCRIPTION
, L2.LOOKUP_CODE SERVICED_STATUS_FLAG
, OOL.LINE_NUMBER ORIGINAL_LINE_NUMBER
, OOL.ACTUAL_SHIPMENT_DATE
, ITEM.QUANTITY
, INST.NAME CP_STATUS
, HZA.ACCOUNT_NUMBER
, ITEM.EXTERNAL_REFERENCE
, ITEM.OWNER_PARTY_ID
, ITEM.OWNER_PARTY_SOURCE_TABLE
, ITEM.LOCATION_TYPE_CODE
FROM HZ_CUST_ACCOUNTS HZA
, HZ_PARTIES HZP
, CS_LOOKUPS L2
, MTL_SYSTEM_ITEMS SI
, ASO_I_OE_ORDER_HEADERS_V OOH
, ASO_I_OE_ORDER_LINES_V OOL
, CSI_ITEM_INSTANCES ITEM
, CSI_SYSTEMS_TL SYS
, CSI_INSTANCE_STATUSES INST
WHERE ITEM.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID
AND ITEM.LOCATION_TYPE_CODE IN ( 'HZ_PARTY_SITES'
, 'HZ_LOCATIONS')
AND ITEM.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES'
AND SI.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND L2.LOOKUP_TYPE = 'CP_SERVICE_STATUS'
AND L2.LOOKUP_CODE = CS_STD.CS_GET_SERVICED_STATUS(ITEM.INSTANCE_ID)
AND ITEM.LAST_OE_ORDER_LINE_ID = OOL.LINE_ID(+)
AND OOL.HEADER_ID = OOH.HEADER_ID(+)
AND ITEM.SYSTEM_ID = SYS.SYSTEM_ID(+)
AND SYS.LANGUAGE(+) = USERENV('LANG')
AND ITEM.OWNER_PARTY_ACCOUNT_ID = HZA.CUST_ACCOUNT_ID
AND HZA.PARTY_ID = HZP.PARTY_ID
AND INST.INSTANCE_STATUS_ID = ITEM.INSTANCE_STATUS_ID
AND INST.INCIDENT_ALLOWED_FLAG = 'Y'
AND SI.SERV_REQ_ENABLED_CODE = 'E'
AND SI.COMMS_NL_TRACKABLE_FLAG='Y'