VIEW: APPS.CSP_DC_PARTS_V

Source

View Text - Preformatted

SELECT crl.requirement_header_id , crl.requirement_line_id , crld.req_line_detail_id , fl.meaning source_type_meaning , mr.reservation_id order_number , crl.inventory_item_id , msibk.concatenated_segments , cppv.meaning , 'RESERVED' , fl1.meaning status_meaning , msibk1.concatenated_segments supplied_item_number , msibk1.description , mr.organization_id , mp.organization_code , hrs.name source_organization_name , crl.required_quantity , mr.reservation_quantity ordered_quantity , TO_CHAR(NULL) order_quantity_uom , crh.need_by_date , crl.order_by_date , to_date(NULL) schedule_arrival_date , crh.task_id , crh.task_assignment_id, TO_CHAR(NULL) shipping_method, csp_part_search_pvt.get_src_distance(crh.requirement_header_id, mr.organization_id, mr.subinventory_code) AS distance, NULL AS ship_cost FROM csp_requirement_lines crl, csp_req_line_details crld, csp_requirement_headers crh, mtl_reservations mr, mtl_system_items_b_kfv msibk, fnd_lookups fl, mtl_system_items_b_kfv msibk1, mtl_parameters mp , csp_part_priorities_v cppv , fnd_lookups fl1 , hr_all_organization_units hrs WHERE crld.source_type = 'RES' AND crld.requirement_line_id = crl.requirement_line_id AND crh.requirement_header_id = crl.requirement_header_id AND crld.source_id = mr.reservation_id AND mp.organization_id = mr.organization_id AND msibk.inventory_item_id = crl.inventory_item_id AND msibk.organization_id = cs_std.get_item_valdn_orgzn_id AND fl.lookup_type = 'CSP_REQ_SOURCE_TYPE' AND crld.source_type = fl.lookup_code AND fl1.lookup_code = 'RESERVED' AND fl1.lookup_type = 'CSP_ORDER_LINE_STATUS_NO_OE' AND msibk1.inventory_item_id = mr.inventory_item_id AND msibk1.organization_id = cs_std.get_item_valdn_orgzn_id AND crl.likelihood BETWEEN cppv.lower_range(+) AND cppv.upper_range(+) AND hrs.organization_id = mr.organization_id UNION ALL SELECT crl.requirement_header_id , crl.requirement_line_id , to_number(NULL) , TO_CHAR(NULL) source_type_meaning , to_number(NULL) order_number , crl.inventory_item_id , msibk.concatenated_segments , cppv.meaning , 'OPEN' , fl.meaning status_meaning , TO_CHAR(NULL) , TO_CHAR(NULL) , to_number(NULL) , TO_CHAR(NULL) , TO_CHAR(NULL) source_organization_name , crl.required_quantity , to_number(NULL) ordered_quantity , TO_CHAR(NULL) order_quantity_uom , crh.need_by_date , crl.order_by_date , to_date(NULL) schedule_arrival_date , crh.task_id , crh.task_assignment_id, TO_CHAR(NULL) shipping_method, TO_CHAR(NULL) AS distance, NULL AS ship_cost FROM csp_requirement_lines crl , mtl_system_items_b_kfv msibk , csp_requirement_headers crh , fnd_lookups fl , csp_part_priorities_v cppv WHERE crl.requirement_line_id NOT IN (SELECT DISTINCT(requirement_line_id) FROM csp_req_line_details ) AND crh.requirement_header_id = crl.requirement_header_id AND msibk.inventory_item_id = crl.inventory_item_id AND msibk.organization_id = cs_std.get_item_valdn_orgzn_id AND fl.lookup_code = 'OPEN' AND fl.lookup_type = 'CSP_ORDER_LINE_STATUS_NO_OE' AND crl.likelihood BETWEEN cppv.lower_range(+) AND cppv.upper_range(+) UNION ALL SELECT crh.requirement_header_id requirement_number , crl.requirement_line_id , crld.req_line_detail_id , fl.meaning source_type_meaning , ooha.order_number , crl.inventory_item_id , msibk.concatenated_segments item_number , cppv.meaning , csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code) status , csp_pick_utils.get_Attribute_Value('STATUS_MEANING') status_meaning , msibk1.concatenated_segments supplied_item_number , msibk1.description , oola.ship_from_org_id , mps.organization_code source_organization_code , hrs.name source_organization_name , crl.required_quantity , oola.ordered_quantity , oola.order_quantity_uom , crh.need_by_date , crl.order_by_date , oola.request_date , crh.task_id , crh.task_assignment_id, ship_lk.meaning AS shipping_method, csp_part_search_pvt.get_src_distance(crh.requirement_header_id, oola.SHIP_FROM_ORG_ID, oola.SUBINVENTORY) AS distance, (SELECT shipping_cost FROM csp_shipping_details_v WHERE shipping_method = oola.SHIPPING_METHOD_CODE AND organization_id = oola.SHIP_FROM_ORG_ID AND to_location_id = crh.ship_to_location_id AND location_source = 'HR' ) AS ship_cost FROM oe_order_lines_all oola , mtl_system_items_b_kfv msibk , csp_requirement_headers crh , csp_requirement_lines crl , csp_part_priorities_v cppv , fnd_lookups fl , oe_order_headers_all ooha , mtl_parameters mps , csp_req_line_details crld , mtl_system_items_b_kfv msibk1 , hr_all_organization_units hrs, fnd_lookup_values_vl ship_lk WHERE crld.requirement_line_id = crl.requirement_line_id AND crld.source_type = 'IO' AND crld.source_id = oola.line_id AND fl.lookup_type = 'CSP_REQ_SOURCE_TYPE' AND crld.source_type = fl.lookup_code AND crl.inventory_item_id = msibk.inventory_item_id AND msibk.organization_id = cs_std.get_item_valdn_orgzn_id AND oola.header_id = ooha.header_id AND oola.inventory_item_id = msibk1.inventory_item_id AND msibk1.organization_id = cs_std.get_item_valdn_orgzn_id AND crl.requirement_header_id = crh.requirement_header_id AND mps.organization_id = oola.ship_from_org_id AND crl.likelihood BETWEEN cppv.lower_range(+) AND cppv.upper_range(+) AND hrs.organization_id = oola.ship_from_org_id AND ship_lk.lookup_type = 'SHIP_METHOD' AND ship_lk.lookup_code = oola.SHIPPING_METHOD_CODE UNION ALL SELECT crl.REQUIREMENT_HEADER_ID , crl.REQUIREMENT_LINE_ID , crld.req_line_detail_id , fl.meaning source_type_meaing , to_number(NVL(poh.segment1,prh.segment1)) order_number , crl.inventory_item_id , msibk.concatenated_segments item_number , cppv.meaning , DECODE(poh.po_header_id,NULL,PRH.AUTHORIZATION_STATUS,NVL(POH.AUTHORIZATION_STATUS,'INCOMPLETE')) status , DECODE(poh.po_header_id,NULL,plc1.DISPLAYED_FIELD,plc.DISPLAYED_FIELD) status_meaning , msibk1.concatenated_segments supplied_item_number , msibk1.description , to_number(NULL) source_organization_id , TO_CHAR(NULL) source_organization_code , TO_CHAR(NULL) source_organization_name , crl.REQUIRED_QUANTITY , pll.quantity , crl.uom_code order_quantity_uom , crh.need_by_date , crl.order_by_date , to_date(NULL) schedule_arrival_date , crh.task_id , crh.task_assignment_id, TO_CHAR(NULL) shipping_method, TO_CHAR(NULL) AS distance, NULL AS ship_cost FROM po_headers POH , PO_LINE_LOCATIONS_V PLL , PO_REQUISITION_LINES POR , PO_LOOKUP_CODES plc , PO_LOOKUP_CODES plc1 , csp_requirement_lines crl , csp_req_line_details crld , fnd_lookups fl , po_requisition_headers prh , csp_requirement_headers crh , mtl_system_items_b_kfv msibk , mtl_system_items_b_kfv msibk1 , csp_part_priorities_v cppv WHERE crld.requirement_line_id = crl.requirement_line_id AND crld.source_type = 'POREQ' AND crld.source_id = por.requisition_line_id(+) AND fl.lookup_type = 'CSP_REQ_SOURCE_TYPE' AND crld.source_type = fl.lookup_code 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 msibk.inventory_item_id = crl.inventory_item_id AND msibK.organization_id = cs_std.get_item_valdn_orgzn_id AND msibk1.inventory_item_id(+) = por.item_id AND msibK1.organization_id(+) = cs_std.get_item_valdn_orgzn_id AND crl.likelihood BETWEEN cppv.lower_range(+) AND cppv.upper_range AND crh.REQUIREMENT_HEADER_ID = crl.REQUIREMENT_HEADER_ID

View Text - HTML Formatted

SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, MR.RESERVATION_ID ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, CPPV.MEANING
, 'RESERVED'
, FL1.MEANING STATUS_MEANING
, MSIBK1.CONCATENATED_SEGMENTS SUPPLIED_ITEM_NUMBER
, MSIBK1.DESCRIPTION
, MR.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, HRS.NAME SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, MR.RESERVATION_QUANTITY ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR(NULL) SHIPPING_METHOD
, CSP_PART_SEARCH_PVT.GET_SRC_DISTANCE(CRH.REQUIREMENT_HEADER_ID
, MR.ORGANIZATION_ID
, MR.SUBINVENTORY_CODE) AS DISTANCE
, NULL AS SHIP_COST
FROM CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, CSP_REQUIREMENT_HEADERS CRH
, MTL_RESERVATIONS MR
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, FND_LOOKUPS FL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_PARAMETERS MP
, CSP_PART_PRIORITIES_V CPPV
, FND_LOOKUPS FL1
, HR_ALL_ORGANIZATION_UNITS HRS
WHERE CRLD.SOURCE_TYPE = 'RES'
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND CRLD.SOURCE_ID = MR.RESERVATION_ID
AND MP.ORGANIZATION_ID = MR.ORGANIZATION_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND FL.LOOKUP_TYPE = 'CSP_REQ_SOURCE_TYPE'
AND CRLD.SOURCE_TYPE = FL.LOOKUP_CODE
AND FL1.LOOKUP_CODE = 'RESERVED'
AND FL1.LOOKUP_TYPE = 'CSP_ORDER_LINE_STATUS_NO_OE'
AND MSIBK1.INVENTORY_ITEM_ID = MR.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE(+)
AND HRS.ORGANIZATION_ID = MR.ORGANIZATION_ID UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL) SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, CPPV.MEANING
, 'OPEN'
, FL.MEANING STATUS_MEANING
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, TO_CHAR(NULL) SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, TO_NUMBER(NULL) ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR(NULL) SHIPPING_METHOD
, TO_CHAR(NULL) AS DISTANCE
, NULL AS SHIP_COST
FROM CSP_REQUIREMENT_LINES CRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQUIREMENT_HEADERS CRH
, FND_LOOKUPS FL
, CSP_PART_PRIORITIES_V CPPV
WHERE CRL.REQUIREMENT_LINE_ID NOT IN (SELECT DISTINCT(REQUIREMENT_LINE_ID)
FROM CSP_REQ_LINE_DETAILS )
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND FL.LOOKUP_CODE = 'OPEN'
AND FL.LOOKUP_TYPE = 'CSP_ORDER_LINE_STATUS_NO_OE'
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE(+) UNION ALL SELECT CRH.REQUIREMENT_HEADER_ID REQUIREMENT_NUMBER
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, OOHA.ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM_NUMBER
, CPPV.MEANING
, CSP_PICK_UTILS.GET_ORDER_STATUS(OOLA.LINE_ID
, OOLA.FLOW_STATUS_CODE) STATUS
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('STATUS_MEANING') STATUS_MEANING
, MSIBK1.CONCATENATED_SEGMENTS SUPPLIED_ITEM_NUMBER
, MSIBK1.DESCRIPTION
, OOLA.SHIP_FROM_ORG_ID
, MPS.ORGANIZATION_CODE SOURCE_ORGANIZATION_CODE
, HRS.NAME SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, OOLA.ORDERED_QUANTITY
, OOLA.ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, OOLA.REQUEST_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
, SHIP_LK.MEANING AS SHIPPING_METHOD
, CSP_PART_SEARCH_PVT.GET_SRC_DISTANCE(CRH.REQUIREMENT_HEADER_ID
, OOLA.SHIP_FROM_ORG_ID
, OOLA.SUBINVENTORY) AS DISTANCE
, (SELECT SHIPPING_COST
FROM CSP_SHIPPING_DETAILS_V
WHERE SHIPPING_METHOD = OOLA.SHIPPING_METHOD_CODE
AND ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND TO_LOCATION_ID = CRH.SHIP_TO_LOCATION_ID
AND LOCATION_SOURCE = 'HR' ) AS SHIP_COST
FROM OE_ORDER_LINES_ALL OOLA
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQUIREMENT_HEADERS CRH
, CSP_REQUIREMENT_LINES CRL
, CSP_PART_PRIORITIES_V CPPV
, FND_LOOKUPS FL
, OE_ORDER_HEADERS_ALL OOHA
, MTL_PARAMETERS MPS
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, HR_ALL_ORGANIZATION_UNITS HRS
, FND_LOOKUP_VALUES_VL SHIP_LK
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_TYPE = 'IO'
AND CRLD.SOURCE_ID = OOLA.LINE_ID
AND FL.LOOKUP_TYPE = 'CSP_REQ_SOURCE_TYPE'
AND CRLD.SOURCE_TYPE = FL.LOOKUP_CODE
AND CRL.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND OOLA.HEADER_ID = OOHA.HEADER_ID
AND OOLA.INVENTORY_ITEM_ID = MSIBK1.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CRL.REQUIREMENT_HEADER_ID = CRH.REQUIREMENT_HEADER_ID
AND MPS.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE(+)
AND HRS.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND SHIP_LK.LOOKUP_TYPE = 'SHIP_METHOD'
AND SHIP_LK.LOOKUP_CODE = OOLA.SHIPPING_METHOD_CODE UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEAING
, TO_NUMBER(NVL(POH.SEGMENT1
, PRH.SEGMENT1)) ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS ITEM_NUMBER
, CPPV.MEANING
, DECODE(POH.PO_HEADER_ID
, NULL
, PRH.AUTHORIZATION_STATUS
, NVL(POH.AUTHORIZATION_STATUS
, 'INCOMPLETE')) STATUS
, DECODE(POH.PO_HEADER_ID
, NULL
, PLC1.DISPLAYED_FIELD
, PLC.DISPLAYED_FIELD) STATUS_MEANING
, MSIBK1.CONCATENATED_SEGMENTS SUPPLIED_ITEM_NUMBER
, MSIBK1.DESCRIPTION
, TO_NUMBER(NULL) SOURCE_ORGANIZATION_ID
, TO_CHAR(NULL) SOURCE_ORGANIZATION_CODE
, TO_CHAR(NULL) SOURCE_ORGANIZATION_NAME
, CRL.REQUIRED_QUANTITY
, PLL.QUANTITY
, CRL.UOM_CODE ORDER_QUANTITY_UOM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR(NULL) SHIPPING_METHOD
, TO_CHAR(NULL) AS DISTANCE
, NULL AS SHIP_COST
FROM PO_HEADERS POH
, PO_LINE_LOCATIONS_V PLL
, PO_REQUISITION_LINES POR
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, FND_LOOKUPS FL
, PO_REQUISITION_HEADERS PRH
, CSP_REQUIREMENT_HEADERS CRH
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, CSP_PART_PRIORITIES_V CPPV
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_TYPE = 'POREQ'
AND CRLD.SOURCE_ID = POR.REQUISITION_LINE_ID(+)
AND FL.LOOKUP_TYPE = 'CSP_REQ_SOURCE_TYPE'
AND CRLD.SOURCE_TYPE = FL.LOOKUP_CODE
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 MSIBK.INVENTORY_ITEM_ID = CRL.INVENTORY_ITEM_ID
AND MSIBK.ORGANIZATION_ID = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK1.INVENTORY_ITEM_ID(+) = POR.ITEM_ID
AND MSIBK1.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID