VIEW: APPS.CSM_REQ_LINES_V

Source

View Text - Preformatted

SELECT acc.access_id , lines.requirement_line_id , lines.requirement_header_id , lines.inventory_item_id , csm_util_pkg.item_name(msi.concatenated_segments) AS item_name, msi.description AS item_description, lines.uom_code , lines.required_quantity , lines.ship_complete_flag , lines.revision , lines.source_organization_id , lines.source_subinventory , lines.ordered_quantity , lines.order_line_id , lines.reservation_id , lines.local_reservation_id , lines.order_by_date , nvl(oe_lines.actual_arrival_date,oe_lines.schedule_arrival_date) arrival_date, oe_lines.shipping_method_code, lines.last_update_date, oe_headers.order_number, lines.sourced_from, null po_requisition_number, CSP_PICK_UTILS.get_order_status (oe_lines.LINE_ID, oe_lines.FLOW_STATUS_CODE) order_status, lines.attribute_category, lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4, lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8, lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12, lines.attribute13, lines.attribute14, lines.attribute15 , lines.LIKELIHOOD, lines.ITEM_SCRATCHPAD,oe_lines.schedule_arrival_date, csp_pick_utils.get_attribute_value('WAYBILL') waybill_number, oe_lines.actual_shipment_date, flv.meaning shipping_method_meaning, fl.meaning SOURCED_FROM_DISP, rld.source_id, CSM_UTIL_PKG.GET_NUMBER(lines.created_by) AS CREATED_BY, CSM_UTIL_PKG.GET_NUMBER(lines.last_updated_by) AS LAST_UPDATED_BY FROM csm_req_lines_acc acc, csp_requirement_lines lines, oe_order_lines_all oe_lines, oe_order_headers_all oe_headers, mtl_system_items_b_kfv msi, csp_req_line_details rld , fnd_lookup_values flv, fnd_lookups fl WHERE acc.user_id = asg_base.get_user_id AND acc.requirement_line_id = lines.requirement_line_id AND (lines.sourced_from = 'INVENTORY' OR lines.sourced_from is null) AND lines.requirement_line_id = rld.requirement_line_id(+) AND rld.source_id = oe_lines.line_id(+) AND oe_lines.header_id = oe_headers.header_id(+) AND msi.inventory_item_id = lines.inventory_item_id AND msi.organization_id = nvl(lines.source_organization_id, csm_profile_pkg.get_organization_id(acc.user_id)) and flv.LOOKUP_TYPE(+) = 'SHIP_METHOD' and flv.LANGUAGE(+) = asg_base.get_language and flv.lookup_code(+) = oe_lines.shipping_method_code and fl.lookup_type(+) = 'CSP_REQ_SOURCE_TYPE' and fl.lookup_code(+) = rld.source_type UNION SELECT acc.access_id , lines.requirement_line_id , lines.requirement_header_id , lines.inventory_item_id , msi.concatenated_segments AS item_name, msi.description AS item_description, lines.uom_code , lines.required_quantity , lines.ship_complete_flag , lines.revision , lines.source_organization_id , lines.source_subinventory , lines.ordered_quantity , lines.order_line_id , lines.reservation_id , lines.local_reservation_id , lines.order_by_date , POR.need_by_date arrival_date, NULL shipping_method_code, lines.last_update_date, to_number(poh.segment1) order_number, lines.sourced_from, prh.segment1 po_requisition_number, prh.authorization_status order_status, lines.attribute_category, lines.attribute1, lines.attribute2, lines.attribute3, lines.attribute4, lines.attribute5, lines.attribute6, lines.attribute7, lines.attribute8, lines.attribute9, lines.attribute10, lines.attribute11, lines.attribute12, lines.attribute13, lines.attribute14, lines.attribute15, lines.LIKELIHOOD, lines.ITEM_SCRATCHPAD, to_date(null) as schedule_arrival_date , to_char(null) waybill_number, to_date(null) actual_shipment_date, to_char(null) shipping_method_meaning, fl.meaning SOURCED_FROM_DISP, rld.source_id, CSM_UTIL_PKG.GET_NUMBER(lines.created_by) AS CREATED_BY, CSM_UTIL_PKG.GET_NUMBER(lines.last_updated_by) AS LAST_UPDATED_BY FROM csm_req_lines_acc acc, csp_requirement_lines lines, mtl_system_items_b_kfv msi, po_headers_all POH , PO_LINE_LOCATIONS_ALL PLL , PO_REQUISITION_LINES_ALL POR , po_requisition_headers_all prh , PO_LOOKUP_CODES plc , PO_LOOKUP_CODES plc1, csp_req_line_details rld, fnd_lookups fl WHERE acc.user_id = asg_base.get_user_id AND acc.requirement_line_id = lines.requirement_line_id AND msi.inventory_item_id = lines.inventory_item_id AND msi.organization_id = nvl(lines.source_organization_id, csm_profile_pkg.get_organization_id(acc.user_id)) AND lines.requirement_line_id = rld.requirement_line_id(+) AND por.requisition_line_id(+) = rld.source_id AND lines.sourced_from = 'VENDOR' AND PLL.line_location_id(+) = por.line_location_id AND POH.po_header_id(+) = PLL.po_header_id AND prh.REQUISITION_HEADER_ID(+) = por.REQUISITION_HEADER_ID AND plc.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS' and plc.LOOKUP_CODE(+) = NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE') AND plc1.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS' and plc1.LOOKUP_CODE(+) = NVL(PRH.AUTHORIZATION_STATUS,'INCOMPLETE') and fl.lookup_type(+) = 'CSP_REQ_SOURCE_TYPE' and fl.lookup_code(+) = rld.source_type

View Text - HTML Formatted

SELECT ACC.ACCESS_ID
, LINES.REQUIREMENT_LINE_ID
, LINES.REQUIREMENT_HEADER_ID
, LINES.INVENTORY_ITEM_ID
, CSM_UTIL_PKG.ITEM_NAME(MSI.CONCATENATED_SEGMENTS) AS ITEM_NAME
, MSI.DESCRIPTION AS ITEM_DESCRIPTION
, LINES.UOM_CODE
, LINES.REQUIRED_QUANTITY
, LINES.SHIP_COMPLETE_FLAG
, LINES.REVISION
, LINES.SOURCE_ORGANIZATION_ID
, LINES.SOURCE_SUBINVENTORY
, LINES.ORDERED_QUANTITY
, LINES.ORDER_LINE_ID
, LINES.RESERVATION_ID
, LINES.LOCAL_RESERVATION_ID
, LINES.ORDER_BY_DATE
, NVL(OE_LINES.ACTUAL_ARRIVAL_DATE
, OE_LINES.SCHEDULE_ARRIVAL_DATE) ARRIVAL_DATE
, OE_LINES.SHIPPING_METHOD_CODE
, LINES.LAST_UPDATE_DATE
, OE_HEADERS.ORDER_NUMBER
, LINES.SOURCED_FROM
, NULL PO_REQUISITION_NUMBER
, CSP_PICK_UTILS.GET_ORDER_STATUS (OE_LINES.LINE_ID
, OE_LINES.FLOW_STATUS_CODE) ORDER_STATUS
, LINES.ATTRIBUTE_CATEGORY
, LINES.ATTRIBUTE1
, LINES.ATTRIBUTE2
, LINES.ATTRIBUTE3
, LINES.ATTRIBUTE4
, LINES.ATTRIBUTE5
, LINES.ATTRIBUTE6
, LINES.ATTRIBUTE7
, LINES.ATTRIBUTE8
, LINES.ATTRIBUTE9
, LINES.ATTRIBUTE10
, LINES.ATTRIBUTE11
, LINES.ATTRIBUTE12
, LINES.ATTRIBUTE13
, LINES.ATTRIBUTE14
, LINES.ATTRIBUTE15
, LINES.LIKELIHOOD
, LINES.ITEM_SCRATCHPAD
, OE_LINES.SCHEDULE_ARRIVAL_DATE
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('WAYBILL') WAYBILL_NUMBER
, OE_LINES.ACTUAL_SHIPMENT_DATE
, FLV.MEANING SHIPPING_METHOD_MEANING
, FL.MEANING SOURCED_FROM_DISP
, RLD.SOURCE_ID
, CSM_UTIL_PKG.GET_NUMBER(LINES.CREATED_BY) AS CREATED_BY
, CSM_UTIL_PKG.GET_NUMBER(LINES.LAST_UPDATED_BY) AS LAST_UPDATED_BY
FROM CSM_REQ_LINES_ACC ACC
, CSP_REQUIREMENT_LINES LINES
, OE_ORDER_LINES_ALL OE_LINES
, OE_ORDER_HEADERS_ALL OE_HEADERS
, MTL_SYSTEM_ITEMS_B_KFV MSI
, CSP_REQ_LINE_DETAILS RLD
, FND_LOOKUP_VALUES FLV
, FND_LOOKUPS FL
WHERE ACC.USER_ID = ASG_BASE.GET_USER_ID
AND ACC.REQUIREMENT_LINE_ID = LINES.REQUIREMENT_LINE_ID
AND (LINES.SOURCED_FROM = 'INVENTORY' OR LINES.SOURCED_FROM IS NULL)
AND LINES.REQUIREMENT_LINE_ID = RLD.REQUIREMENT_LINE_ID(+)
AND RLD.SOURCE_ID = OE_LINES.LINE_ID(+)
AND OE_LINES.HEADER_ID = OE_HEADERS.HEADER_ID(+)
AND MSI.INVENTORY_ITEM_ID = LINES.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = NVL(LINES.SOURCE_ORGANIZATION_ID
, CSM_PROFILE_PKG.GET_ORGANIZATION_ID(ACC.USER_ID))
AND FLV.LOOKUP_TYPE(+) = 'SHIP_METHOD'
AND FLV.LANGUAGE(+) = ASG_BASE.GET_LANGUAGE
AND FLV.LOOKUP_CODE(+) = OE_LINES.SHIPPING_METHOD_CODE
AND FL.LOOKUP_TYPE(+) = 'CSP_REQ_SOURCE_TYPE'
AND FL.LOOKUP_CODE(+) = RLD.SOURCE_TYPE UNION SELECT ACC.ACCESS_ID
, LINES.REQUIREMENT_LINE_ID
, LINES.REQUIREMENT_HEADER_ID
, LINES.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS AS ITEM_NAME
, MSI.DESCRIPTION AS ITEM_DESCRIPTION
, LINES.UOM_CODE
, LINES.REQUIRED_QUANTITY
, LINES.SHIP_COMPLETE_FLAG
, LINES.REVISION
, LINES.SOURCE_ORGANIZATION_ID
, LINES.SOURCE_SUBINVENTORY
, LINES.ORDERED_QUANTITY
, LINES.ORDER_LINE_ID
, LINES.RESERVATION_ID
, LINES.LOCAL_RESERVATION_ID
, LINES.ORDER_BY_DATE
, POR.NEED_BY_DATE ARRIVAL_DATE
, NULL SHIPPING_METHOD_CODE
, LINES.LAST_UPDATE_DATE
, TO_NUMBER(POH.SEGMENT1) ORDER_NUMBER
, LINES.SOURCED_FROM
, PRH.SEGMENT1 PO_REQUISITION_NUMBER
, PRH.AUTHORIZATION_STATUS ORDER_STATUS
, LINES.ATTRIBUTE_CATEGORY
, LINES.ATTRIBUTE1
, LINES.ATTRIBUTE2
, LINES.ATTRIBUTE3
, LINES.ATTRIBUTE4
, LINES.ATTRIBUTE5
, LINES.ATTRIBUTE6
, LINES.ATTRIBUTE7
, LINES.ATTRIBUTE8
, LINES.ATTRIBUTE9
, LINES.ATTRIBUTE10
, LINES.ATTRIBUTE11
, LINES.ATTRIBUTE12
, LINES.ATTRIBUTE13
, LINES.ATTRIBUTE14
, LINES.ATTRIBUTE15
, LINES.LIKELIHOOD
, LINES.ITEM_SCRATCHPAD
, TO_DATE(NULL) AS SCHEDULE_ARRIVAL_DATE
, TO_CHAR(NULL) WAYBILL_NUMBER
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_CHAR(NULL) SHIPPING_METHOD_MEANING
, FL.MEANING SOURCED_FROM_DISP
, RLD.SOURCE_ID
, CSM_UTIL_PKG.GET_NUMBER(LINES.CREATED_BY) AS CREATED_BY
, CSM_UTIL_PKG.GET_NUMBER(LINES.LAST_UPDATED_BY) AS LAST_UPDATED_BY
FROM CSM_REQ_LINES_ACC ACC
, CSP_REQUIREMENT_LINES LINES
, MTL_SYSTEM_ITEMS_B_KFV MSI
, PO_HEADERS_ALL POH
, PO_LINE_LOCATIONS_ALL PLL
, PO_REQUISITION_LINES_ALL POR
, PO_REQUISITION_HEADERS_ALL PRH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, CSP_REQ_LINE_DETAILS RLD
, FND_LOOKUPS FL
WHERE ACC.USER_ID = ASG_BASE.GET_USER_ID
AND ACC.REQUIREMENT_LINE_ID = LINES.REQUIREMENT_LINE_ID
AND MSI.INVENTORY_ITEM_ID = LINES.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = NVL(LINES.SOURCE_ORGANIZATION_ID
, CSM_PROFILE_PKG.GET_ORGANIZATION_ID(ACC.USER_ID))
AND LINES.REQUIREMENT_LINE_ID = RLD.REQUIREMENT_LINE_ID(+)
AND POR.REQUISITION_LINE_ID(+) = RLD.SOURCE_ID
AND LINES.SOURCED_FROM = 'VENDOR'
AND PLL.LINE_LOCATION_ID(+) = POR.LINE_LOCATION_ID
AND POH.PO_HEADER_ID(+) = PLL.PO_HEADER_ID
AND PRH.REQUISITION_HEADER_ID(+) = POR.REQUISITION_HEADER_ID
AND PLC.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PLC.LOOKUP_CODE(+) = NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND PLC1.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PLC1.LOOKUP_CODE(+) = NVL(PRH.AUTHORIZATION_STATUS
, 'INCOMPLETE')
AND FL.LOOKUP_TYPE(+) = 'CSP_REQ_SOURCE_TYPE'
AND FL.LOOKUP_CODE(+) = RLD.SOURCE_TYPE