View: MSD_SR_BOOKING_DATA_V

Product:	MSD - Demand Planning
Description:	This is the Source View for Booking Data. This view would be applied to an Oracle Applications 11i instance from which the Booking information is to be retrieved.
Implementation/DBA Data:	APPS.MSD_SR_BOOKING_DATA_V

View Text

SELECT NVL(L.SHIP_FROM_ORG_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(L.INVENTORY_ITEM_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(CUST.CUSTOMER_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SC.LOOKUP_CODE
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(SREP.SALESREP_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(PRS.SITE_USE_ID
, MSD_SR_UTIL.GET_NULL_PK)
, NULL
, NVL(TRUNC(H.BOOKED_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.REQUEST_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.PROMISE_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, NVL(TRUNC(L.SCHEDULE_SHIP_DATE
, 'DD')
, TO_DATE('1000/01/01'
, 'YYYY/MM/DD'))
, MSD_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY * NVL(L.UNIT_SELLING_PRICE
, NVL(L.UNIT_LIST_PRICE
, 0)) * MSD_SR_UTIL.CONVERT_GLOBAL_AMT(H.TRANSACTIONAL_CURR_CODE
, H.BOOKED_DATE)
, MSD_SR_UTIL.UOM_CONV(L.ORDER_QUANTITY_UOM
, L.INVENTORY_ITEM_ID) * L.ORDERED_QUANTITY
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, MSD_SR_UTIL.GET_NULL_PK
, NULL
, L.INVENTORY_ITEM_ID
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 1
, NULL
, 3
, DECODE(L.ITEM_TYPE_CODE
, 'OPTION'
, DECODE(L.ATO_LINE_ID
, NULL
, NULL
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'N') )
, DECODE( L.ATO_LINE_ID
, L.LINE_ID
, NULL
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'N') ) )
, DECODE(L.ITEM_TYPE_CODE
, 'OPTION'
, DECODE(L.ATO_LINE_ID
, NULL
, NULL
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'Y') )
, DECODE(L.ATO_LINE_ID
, L.LINE_ID
, NULL
, MSD_SR_UTIL.FIND_PARENT_ITEM(L.LINK_TO_LINE_ID
, 'Y') ) ) )
FROM MSD_APP_INSTANCE_ORGS MORG
, MSD_SETUP_PARAMETERS PARA
, SO_LOOKUPS SC
, RA_SALESREPS_ALL SREP
, RA_CUSTOMERS CUST
, RA_SITE_USES_ALL PRS
, OE_ORDER_LINES_ALL L
, MTL_SYSTEM_ITEMS ITM
, OE_ORDER_HEADERS_ALL H
, (SELECT PARAMETER_VALUE
FROM MSD_SETUP_PARAMETERS
WHERE PARAMETER_NAME = 'MSD_CUSTOMER_ATTRIBUTE') FILTERCUST
WHERE PARA.PARAMETER_NAME = 'MSD_PLANNING_PERCENTAGE'
AND L.HEADER_ID = H.HEADER_ID
AND DECODE( ITM.ATO_FORECAST_CONTROL
, 3
, DECODE(L.ITEM_TYPE_CODE
, 'OPTION'
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 4
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_FACT (L.INVENTORY_ITEM_ID
, L.COMPONENT_SEQUENCE_ID
, L.LINK_TO_LINE_ID)
, 2)
, 'CLASS'
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 4
, MSD_SR_UTIL.IS_ITEM_OPTIONAL_FOR_FACT (L.INVENTORY_ITEM_ID
, L.COMPONENT_SEQUENCE_ID
, L.LINK_TO_LINE_ID)
, 2)
, 2)
, DECODE( L.ITEM_TYPE_CODE
, 'MODEL'
, 1
, 'STANDARD'
, 1
, 'KIT'
, 1
, 'OPTION'
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 1
, 2
, 1)
, 'CLASS'
, DECODE(NVL(PARA.PARAMETER_VALUE
, 1)
, 1
, 2
, 3
, DECODE( ITM.BOM_ITEM_TYPE
, 1
, 1
, 2)
, 1)
, 2) ) = 1
AND NVL(H.ORDER_SOURCE_ID
, 0) <> 10
AND H.BOOKED_FLAG = 'Y'
AND ITM.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND ITM.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND ITM.ATO_FORECAST_CONTROL IN ('1'
, '2')
AND ITM.MRP_PLANNING_CODE <> 6
AND H.SALES_CHANNEL_CODE = SC.LOOKUP_CODE (+)
AND SC.LOOKUP_TYPE (+) = 'SALES_CHANNEL'
AND H.SALESREP_ID = SREP.SALESREP_ID (+)
AND H.ORG_ID = SREP.ORG_ID (+)
AND CUST.CUSTOMER_ID = H.SOLD_TO_ORG_ID
AND PRS.SITE_USE_ID (+) = L.SHIP_TO_ORG_ID
AND PRS.SITE_USE_CODE (+) = 'SHIP_TO'
AND MORG.ORGANIZATION_ID = L.SHIP_FROM_ORG_ID
AND L.LINE_CATEGORY_CODE <> 'RETURN'
AND DECODE(NVL(LOWER(FILTERCUST.PARAMETER_VALUE)
, '1')
, '1'
, '1'
, 'ATTRIBUTE1'
, CUST.ATTRIBUTE1
, 'ATTRIBUTE2'
, CUST.ATTRIBUTE2
, 'ATTRIBUTE3'
, CUST.ATTRIBUTE3
, 'ATTRIBUTE4'
, CUST.ATTRIBUTE4
, 'ATTRIBUTE5'
, CUST.ATTRIBUTE5
, 'ATTRIBUTE6'
, CUST.ATTRIBUTE6
, 'ATTRIBUTE7'
, CUST.ATTRIBUTE7
, 'ATTRIBUTE8'
, CUST.ATTRIBUTE8
, 'ATTRIBUTE9'
, CUST.ATTRIBUTE9
, 'ATTRIBUTE10'
, CUST.ATTRIBUTE10
, 'ATTRIBUTE11'
, CUST.ATTRIBUTE11
, 'ATTRIBUTE12'
, CUST.ATTRIBUTE12
, 'ATTRIBUTE13'
, CUST.ATTRIBUTE13
, 'ATTRIBUTE14'
, CUST.ATTRIBUTE14
, 'ATTRIBUTE15'
, CUST.ATTRIBUTE15
, '2') = '1'

Columns