VIEW: APPS.CSP_SPARES_ORDERS_OM_V

Source

View Text - Preformatted

SELECT crh.requirement_header_id requirement_number , crl.requirement_line_id ,crld.req_line_detail_id ,fl.meaning source_type_meaning , to_number(null) po_requisition_line_id , to_number(null) po_requisition_header_id , null po_requisition_number , to_number(null) po_line_location_id , oola.header_id order_header_id , oola.line_id order_line_id , 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 , crh.destination_organization_id ,mpd.organization_code destination_organization_code , crh.ship_to_location_id , oola.freight_carrier_code , ofc.description freight_carrier , crl.required_quantity , oola.ordered_quantity , oola.order_quantity_uom , oola.fulfilled_quantity , oola.shipping_quantity , oola.shipping_quantity_uom , oola.shipped_quantity ,csp_pick_utils.get_received_Qty received_quantity ,csp_pick_utils.get_attribute_value('RECEIVED_QTY_UOM') received_quantity_uom , oola.cancelled_quantity , csp_pick_utils.get_Attribute_Value('DELIVERY_NUMBER') ,csp_pick_utils.get_Attribute_Value('WAYBILL') waybill_num ,crh.need_by_date , crl.order_by_date , oola.request_date , oola.promise_date , oola.schedule_ship_date , oola.actual_shipment_date , oola.schedule_arrival_date , oola.actual_arrival_date , jtv.source_object_name service_request , jtv.source_object_id incident_id , crh.task_id , jtv.task_number , crh.task_assignment_id ,to_char( decode(to_char(crh.task_assignment_id),to_char( NULL), to_char( crh.resource_type), to_char( jta.resource_type_code))) rsource_type , decode(crh.task_assignment_id, NULL, jov1.name, jov.name) resource_type_meaning , decode(crh.task_assignment_id, NULL, crh.resource_id, jta.resource_id) resource_id ,csp_pick_utils.get_object_name(nvl(crh.resource_type, jta.resource_type_code), nvl(crh.resource_id, jta.resource_id)) resource_name, crh.address_type 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 , jtf_tasks_vl jtv , jtf_task_assignments jta , oe_order_headers_all ooha ,mtl_parameters mps ,mtl_parameters mpd , org_freight ofc , jtf_objects_vl jov , jtf_objects_vl jov1 ,csp_req_line_details crld ,mtl_system_items_b_kfv msibk1 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 crh.task_id =jtv.task_id (+) and crh.task_assignment_id = jta.task_assignment_id (+) and 'ASSIGNEE' = jta.assignee_role(+) and mps.organization_id = oola.ship_from_org_id and mpd.organization_id (+) = crh.destination_organization_id and oola.freight_carrier_code = ofc.freight_code (+) and ofc.organization_id(+) = oola.ship_from_org_id and jta.resource_type_code = jov.object_code (+) and crh.resource_type = jov1.object_code (+) and crl.likelihood between cppv.lower_range(+) and cppv.upper_range(+) and crld.source_id > 0 UNION ALL SELECT crh.requirement_header_id requirement_number , crl.requirement_line_id ,crld.req_line_detail_id ,fl.meaning source_type_meaning , to_number(null) po_requisition_line_id , to_number(null) po_requisition_header_id , null po_requisition_number , to_number(null) po_line_location_id , oola.header_id order_header_id , oola.line_id order_line_id , 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 , crh.destination_organization_id ,mpd.organization_code destination_organization_code , crh.ship_to_location_id , oola.freight_carrier_code , ofc.description freight_carrier , to_number(null) required_quantity , oola.ordered_quantity , oola.order_quantity_uom , oola.fulfilled_quantity , oola.shipping_quantity , oola.shipping_quantity_uom , oola.shipped_quantity ,csp_pick_utils.get_received_Qty received_quantity ,csp_pick_utils.get_attribute_value('RECEIVED_QTY_UOM') received_quantity_uom , oola.cancelled_quantity , csp_pick_utils.get_Attribute_Value('DELIVERY_NUMBER') ,csp_pick_utils.get_Attribute_Value('WAYBILL') waybill_num ,crh.need_by_date , crl.order_by_date , oola.request_date , oola.promise_date , oola.schedule_ship_date , oola.actual_shipment_date , oola.schedule_arrival_date , oola.actual_arrival_date , jtv.source_object_name service_request , jtv.source_object_id incident_id , crh.task_id , jtv.task_number , crh.task_assignment_id ,to_char( decode(to_char(crh.task_assignment_id),to_char( NULL), to_char( crh.resource_type), to_char( jta.resource_type_code))) rsource_type , decode(crh.task_assignment_id, NULL, jov1.name, jov.name) resource_type_meaning , decode(crh.task_assignment_id, NULL, crh.resource_id, jta.resource_id) resource_id ,csp_pick_utils.get_object_name(nvl(crh.resource_type, jta.resource_type_code), nvl(crh.resource_id, jta.resource_id)) resource_name, crh.address_type 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 , jtf_tasks_vl jtv , jtf_task_assignments jta , oe_order_headers_all ooha ,mtl_parameters mps ,mtl_parameters mpd , org_freight ofc , jtf_objects_vl jov , jtf_objects_vl jov1 ,csp_req_line_details crld ,mtl_system_items_b_kfv msibk1 where crld.requirement_line_id = crl.requirement_line_id and crld.source_type = 'IO' and crld.source_id = oola.split_from_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 crh.task_id =jtv.task_id (+) and crh.task_assignment_id = jta.task_assignment_id (+) and 'ASSIGNEE' = jta.assignee_role(+) and mps.organization_id = oola.ship_from_org_id and mpd.organization_id (+) = crh.destination_organization_id and oola.freight_carrier_code = ofc.freight_code (+) and ofc.organization_id(+) = oola.ship_from_org_id and jta.resource_type_code = jov.object_code (+) and crh.resource_type = jov1.object_code (+) and crl.likelihood between cppv.lower_range(+) and cppv.upper_range(+) and crld.source_id > 0

View Text - HTML Formatted

SELECT CRH.REQUIREMENT_HEADER_ID REQUIREMENT_NUMBER
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) PO_REQUISITION_LINE_ID
, TO_NUMBER(NULL) PO_REQUISITION_HEADER_ID
, NULL PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, OOLA.HEADER_ID ORDER_HEADER_ID
, OOLA.LINE_ID ORDER_LINE_ID
, 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
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE DESTINATION_ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, OOLA.FREIGHT_CARRIER_CODE
, OFC.DESCRIPTION FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, OOLA.ORDERED_QUANTITY
, OOLA.ORDER_QUANTITY_UOM
, OOLA.FULFILLED_QUANTITY
, OOLA.SHIPPING_QUANTITY
, OOLA.SHIPPING_QUANTITY_UOM
, OOLA.SHIPPED_QUANTITY
, CSP_PICK_UTILS.GET_RECEIVED_QTY RECEIVED_QUANTITY
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('RECEIVED_QTY_UOM') RECEIVED_QUANTITY_UOM
, OOLA.CANCELLED_QUANTITY
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('DELIVERY_NUMBER')
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('WAYBILL') WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, OOLA.REQUEST_DATE
, OOLA.PROMISE_DATE
, OOLA.SCHEDULE_SHIP_DATE
, OOLA.ACTUAL_SHIPMENT_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE
, OOLA.ACTUAL_ARRIVAL_DATE
, JTV.SOURCE_OBJECT_NAME SERVICE_REQUEST
, JTV.SOURCE_OBJECT_ID INCIDENT_ID
, CRH.TASK_ID
, JTV.TASK_NUMBER
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR( DECODE(TO_CHAR(CRH.TASK_ASSIGNMENT_ID)
, TO_CHAR( NULL)
, TO_CHAR( CRH.RESOURCE_TYPE)
, TO_CHAR( JTA.RESOURCE_TYPE_CODE))) RSOURCE_TYPE
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, JOV1.NAME
, JOV.NAME) RESOURCE_TYPE_MEANING
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, CRH.RESOURCE_ID
, JTA.RESOURCE_ID) RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(NVL(CRH.RESOURCE_TYPE
, JTA.RESOURCE_TYPE_CODE)
, NVL(CRH.RESOURCE_ID
, JTA.RESOURCE_ID)) RESOURCE_NAME
, CRH.ADDRESS_TYPE
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
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, OE_ORDER_HEADERS_ALL OOHA
, MTL_PARAMETERS MPS
, MTL_PARAMETERS MPD
, ORG_FREIGHT OFC
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
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 CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND MPS.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND MPD.ORGANIZATION_ID (+) = CRH.DESTINATION_ORGANIZATION_ID
AND OOLA.FREIGHT_CARRIER_CODE = OFC.FREIGHT_CODE (+)
AND OFC.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+)
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE(+)
AND CRLD.SOURCE_ID > 0 UNION ALL SELECT CRH.REQUIREMENT_HEADER_ID REQUIREMENT_NUMBER
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) PO_REQUISITION_LINE_ID
, TO_NUMBER(NULL) PO_REQUISITION_HEADER_ID
, NULL PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, OOLA.HEADER_ID ORDER_HEADER_ID
, OOLA.LINE_ID ORDER_LINE_ID
, 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
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE DESTINATION_ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, OOLA.FREIGHT_CARRIER_CODE
, OFC.DESCRIPTION FREIGHT_CARRIER
, TO_NUMBER(NULL) REQUIRED_QUANTITY
, OOLA.ORDERED_QUANTITY
, OOLA.ORDER_QUANTITY_UOM
, OOLA.FULFILLED_QUANTITY
, OOLA.SHIPPING_QUANTITY
, OOLA.SHIPPING_QUANTITY_UOM
, OOLA.SHIPPED_QUANTITY
, CSP_PICK_UTILS.GET_RECEIVED_QTY RECEIVED_QUANTITY
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('RECEIVED_QTY_UOM') RECEIVED_QUANTITY_UOM
, OOLA.CANCELLED_QUANTITY
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('DELIVERY_NUMBER')
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('WAYBILL') WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, OOLA.REQUEST_DATE
, OOLA.PROMISE_DATE
, OOLA.SCHEDULE_SHIP_DATE
, OOLA.ACTUAL_SHIPMENT_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE
, OOLA.ACTUAL_ARRIVAL_DATE
, JTV.SOURCE_OBJECT_NAME SERVICE_REQUEST
, JTV.SOURCE_OBJECT_ID INCIDENT_ID
, CRH.TASK_ID
, JTV.TASK_NUMBER
, CRH.TASK_ASSIGNMENT_ID
, TO_CHAR( DECODE(TO_CHAR(CRH.TASK_ASSIGNMENT_ID)
, TO_CHAR( NULL)
, TO_CHAR( CRH.RESOURCE_TYPE)
, TO_CHAR( JTA.RESOURCE_TYPE_CODE))) RSOURCE_TYPE
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, JOV1.NAME
, JOV.NAME) RESOURCE_TYPE_MEANING
, DECODE(CRH.TASK_ASSIGNMENT_ID
, NULL
, CRH.RESOURCE_ID
, JTA.RESOURCE_ID) RESOURCE_ID
, CSP_PICK_UTILS.GET_OBJECT_NAME(NVL(CRH.RESOURCE_TYPE
, JTA.RESOURCE_TYPE_CODE)
, NVL(CRH.RESOURCE_ID
, JTA.RESOURCE_ID)) RESOURCE_NAME
, CRH.ADDRESS_TYPE
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
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, OE_ORDER_HEADERS_ALL OOHA
, MTL_PARAMETERS MPS
, MTL_PARAMETERS MPD
, ORG_FREIGHT OFC
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
, CSP_REQ_LINE_DETAILS CRLD
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
WHERE CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_TYPE = 'IO'
AND CRLD.SOURCE_ID = OOLA.SPLIT_FROM_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 CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND MPS.ORGANIZATION_ID = OOLA.SHIP_FROM_ORG_ID
AND MPD.ORGANIZATION_ID (+) = CRH.DESTINATION_ORGANIZATION_ID
AND OOLA.FREIGHT_CARRIER_CODE = OFC.FREIGHT_CODE (+)
AND OFC.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+)
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE(+)
AND CRLD.SOURCE_ID > 0