eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CSP_REQUIRED_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 , to_number(null) po_requisition_line_id , to_number(null) po_requisition_header_id , to_char(null) po_requisition_number , to_number(null) po_line_location_id , to_number(null) order_header_id , to_number(null) order_line_id , 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 , crh.destination_organization_id ,mpd.organization_code , crh.ship_to_location_id ,to_char(null) freight_carrier_code ,to_char(null) freight_carrier , crl.required_quantity , mr.reservation_quantity ordered_quantity , to_char(null) order_quantity_uom , to_number(null) fulfilled_quantity , to_number(null) shipping_quantity , to_char(null) shipping_quantity_uom , to_number(null) shipped_quantity , to_number(null) received_quantity ,to_char(null) received_quantity_uom , to_number(null) cancelled_quantity , to_char(null) delivery_number , to_char(null) waybill_num ,crh.need_by_date , crl.order_by_date , to_date(null) request_date , to_date(null) promise_date , to_date(null) schedule_ship_date , to_date(null) actual_shipment_date , to_date(null) schedule_arrival_date , to_date(null) 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 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, fnd_lookups fl1, mtl_system_items_b_kfv msibk1, mtl_parameters mp ,mtl_parameters mpd , csp_part_priorities_v cppv , jtf_tasks_vl jtv , jtf_task_assignments jta , jtf_objects_vl jov , jtf_objects_vl jov1 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 mpd.organization_id = crh.destination_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 crh.task_id =jtv.task_id (+) and crh.task_assignment_id = jta.task_assignment_id (+) and 'ASSIGNEE' = jta.assignee_role(+) and jta.resource_type_code = jov.object_code (+) and crh.resource_type = jov1.object_code (+) UNION ALL select crl.requirement_header_id ,crl.requirement_line_id ,to_number(null) ,to_char(null) source_type_meaning , to_number(null) po_requisition_line_id , to_number(null) po_requisition_header_id , to_char(null) po_requisition_number , to_number(null) po_line_location_id , to_number(null) order_header_id , to_number(null) order_line_id , to_number(null) order_number ,crl.inventory_item_id ,msibk.concatenated_segments ,cppv.meaning ,decode(crh.open_requirement,'N','CLOSED','OPEN') ,fl.meaning status_meaning ,to_char(null) ,to_char(null) ,to_number(null) ,to_char(null) , crh.destination_organization_id ,mpd.organization_code , crh.ship_to_location_id ,to_char(null) freight_carrier_code ,to_char(null) freight_carrier , crl.required_quantity , to_number(null) ordered_quantity , to_char(null) order_quantity_uom , to_number(null) fulfilled_quantity , to_number(null) shipping_quantity , to_char(null) shipping_quantity_uom , to_number(null) shipped_quantity , to_number(null) received_quantity ,to_char(null) received_quantity_uom , to_number(null) cancelled_quantity , to_char(null) delivery_number , to_char(null) waybill_num ,crh.need_by_date , crl.order_by_date , to_date(null) request_date , to_date(null) promise_date , to_date(null) schedule_ship_date , to_date(null) actual_shipment_date , to_date(null) schedule_arrival_date , to_date(null) 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 csp_requirement_lines crl , mtl_system_items_b_kfv msibk , csp_requirement_headers crh ,fnd_lookups fl , csp_part_priorities_v cppv , jtf_tasks_vl jtv , jtf_task_assignments jta , jtf_objects_vl jov , jtf_objects_vl jov1 ,mtl_parameters mpd 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 = decode(crh.open_requirement,'N','CLOSED','OPEN') and fl.lookup_type = 'CSP_ORDER_LINE_STATUS_NO_OE' and crl.likelihood between cppv.lower_range(+) and cppv.upper_range(+) and crh.task_id =jtv.task_id (+) and crh.task_assignment_id = jta.task_assignment_id (+) and 'ASSIGNEE' = jta.assignee_role(+) and jta.resource_type_code = jov.object_code (+) and crh.resource_type = jov1.object_code (+) and mpd.organization_id (+)= crh.destination_organization_id 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 , 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(+) 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 oola.header_id = (select header_id from oe_order_lines_all where line_id = crld.source_id and crld.requirement_line_id = crl.requirement_line_id) UNION all select crl.REQUIREMENT_HEADER_ID , crl.REQUIREMENT_LINE_ID ,crld.req_line_detail_id ,fl.meaning source_type_meaing , crld.source_id , por.REQUISITION_HEADER_ID , prh.segment1 po_requisition_number , PLL.line_location_id , pll.po_header_id order_header_id , pll.po_line_id , 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 , null source_organization_code , crh.destination_organization_id , mpd.organization_code destination_organization_code , crh.ship_to_location_id ,null frieght_carrier_code , null freight_carrier , crl.REQUIRED_QUANTITY , pll.quantity , crl.uom_code order_quantity_uom , to_number(null) fullfilled_quantity , to_number(null) shipping_quantity , null shipping_quantity_uom , to_number(null) shipped_quantity , pll.QUANTITY_RECEIVED ,pll.UNIT_MEAS_LOOKUP_CODE , pll.QUANTITY_CANCELLED , to_char(null) delivery_number , to_char(null) waybill_num , crh.need_by_date , crl.order_by_date , prh.CREATION_DATE , pll.PROMISED_DATE , to_date(null) schedule_ship_date , to_date(null) actual_shipment_date , to_date(null) schedule_arrival_date , to_date(null) 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 po_headers POH , PO_LINE_LOCATIONS_ALL 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 ,mtl_parameters mpd ,csp_part_priorities_v cppv ,jtf_tasks_vl jtv , jtf_task_assignments jta ,jtf_objects_vl jov , jtf_objects_vl jov1 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 and mpd.organization_id (+) = crh.destination_organization_id and jtv.task_id(+) = crh.task_id and jta.task_assignment_id(+) = crh.task_assignment_id and jta.assignee_role(+) = 'ASSIGNEE' and jov.object_code(+) = jta.resource_type_code and jov1.object_code(+) = crh.resource_type

View Text - HTML Formatted

SELECT CRL.REQUIREMENT_HEADER_ID
, 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
, TO_CHAR(NULL) PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, TO_NUMBER(NULL) ORDER_HEADER_ID
, TO_NUMBER(NULL) ORDER_LINE_ID
, 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
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, TO_CHAR(NULL) FREIGHT_CARRIER_CODE
, TO_CHAR(NULL) FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, MR.RESERVATION_QUANTITY ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, TO_NUMBER(NULL) FULFILLED_QUANTITY
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, TO_CHAR(NULL) SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, TO_NUMBER(NULL) RECEIVED_QUANTITY
, TO_CHAR(NULL) RECEIVED_QUANTITY_UOM
, TO_NUMBER(NULL) CANCELLED_QUANTITY
, TO_CHAR(NULL) DELIVERY_NUMBER
, TO_CHAR(NULL) WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) REQUEST_DATE
, TO_DATE(NULL) PROMISE_DATE
, TO_DATE(NULL) SCHEDULE_SHIP_DATE
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) 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 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
, FND_LOOKUPS FL1
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_PARAMETERS MP
, MTL_PARAMETERS MPD
, CSP_PART_PRIORITIES_V CPPV
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
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 MPD.ORGANIZATION_ID = CRH.DESTINATION_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 CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+) UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_CHAR(NULL) SOURCE_TYPE_MEANING
, TO_NUMBER(NULL) PO_REQUISITION_LINE_ID
, TO_NUMBER(NULL) PO_REQUISITION_HEADER_ID
, TO_CHAR(NULL) PO_REQUISITION_NUMBER
, TO_NUMBER(NULL) PO_LINE_LOCATION_ID
, TO_NUMBER(NULL) ORDER_HEADER_ID
, TO_NUMBER(NULL) ORDER_LINE_ID
, TO_NUMBER(NULL) ORDER_NUMBER
, CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, CPPV.MEANING
, DECODE(CRH.OPEN_REQUIREMENT
, 'N'
, 'CLOSED'
, 'OPEN')
, FL.MEANING STATUS_MEANING
, TO_CHAR(NULL)
, TO_CHAR(NULL)
, TO_NUMBER(NULL)
, TO_CHAR(NULL)
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, TO_CHAR(NULL) FREIGHT_CARRIER_CODE
, TO_CHAR(NULL) FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, TO_NUMBER(NULL) ORDERED_QUANTITY
, TO_CHAR(NULL) ORDER_QUANTITY_UOM
, TO_NUMBER(NULL) FULFILLED_QUANTITY
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, TO_CHAR(NULL) SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, TO_NUMBER(NULL) RECEIVED_QUANTITY
, TO_CHAR(NULL) RECEIVED_QUANTITY_UOM
, TO_NUMBER(NULL) CANCELLED_QUANTITY
, TO_CHAR(NULL) DELIVERY_NUMBER
, TO_CHAR(NULL) WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, TO_DATE(NULL) REQUEST_DATE
, TO_DATE(NULL) PROMISE_DATE
, TO_DATE(NULL) SCHEDULE_SHIP_DATE
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) 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 CSP_REQUIREMENT_LINES CRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, CSP_REQUIREMENT_HEADERS CRH
, FND_LOOKUPS FL
, CSP_PART_PRIORITIES_V CPPV
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
, MTL_PARAMETERS MPD
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 = DECODE(CRH.OPEN_REQUIREMENT
, 'N'
, 'CLOSED'
, 'OPEN')
AND FL.LOOKUP_TYPE = 'CSP_ORDER_LINE_STATUS_NO_OE'
AND CRL.LIKELIHOOD BETWEEN CPPV.LOWER_RANGE(+)
AND CPPV.UPPER_RANGE(+)
AND CRH.TASK_ID =JTV.TASK_ID (+)
AND CRH.TASK_ASSIGNMENT_ID = JTA.TASK_ASSIGNMENT_ID (+)
AND 'ASSIGNEE' = JTA.ASSIGNEE_ROLE(+)
AND JTA.RESOURCE_TYPE_CODE = JOV.OBJECT_CODE (+)
AND CRH.RESOURCE_TYPE = JOV1.OBJECT_CODE (+)
AND MPD.ORGANIZATION_ID (+)= CRH.DESTINATION_ORGANIZATION_ID 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
, 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(+) 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 OOLA.HEADER_ID = (SELECT HEADER_ID
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = CRLD.SOURCE_ID
AND CRLD.REQUIREMENT_LINE_ID = CRL.REQUIREMENT_LINE_ID) UNION ALL SELECT CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, FL.MEANING SOURCE_TYPE_MEAING
, CRLD.SOURCE_ID
, POR.REQUISITION_HEADER_ID
, PRH.SEGMENT1 PO_REQUISITION_NUMBER
, PLL.LINE_LOCATION_ID
, PLL.PO_HEADER_ID ORDER_HEADER_ID
, PLL.PO_LINE_ID
, 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
, NULL SOURCE_ORGANIZATION_CODE
, CRH.DESTINATION_ORGANIZATION_ID
, MPD.ORGANIZATION_CODE DESTINATION_ORGANIZATION_CODE
, CRH.SHIP_TO_LOCATION_ID
, NULL FRIEGHT_CARRIER_CODE
, NULL FREIGHT_CARRIER
, CRL.REQUIRED_QUANTITY
, PLL.QUANTITY
, CRL.UOM_CODE ORDER_QUANTITY_UOM
, TO_NUMBER(NULL) FULLFILLED_QUANTITY
, TO_NUMBER(NULL) SHIPPING_QUANTITY
, NULL SHIPPING_QUANTITY_UOM
, TO_NUMBER(NULL) SHIPPED_QUANTITY
, PLL.QUANTITY_RECEIVED
, PLL.UNIT_MEAS_LOOKUP_CODE
, PLL.QUANTITY_CANCELLED
, TO_CHAR(NULL) DELIVERY_NUMBER
, TO_CHAR(NULL) WAYBILL_NUM
, CRH.NEED_BY_DATE
, CRL.ORDER_BY_DATE
, PRH.CREATION_DATE
, PLL.PROMISED_DATE
, TO_DATE(NULL) SCHEDULE_SHIP_DATE
, TO_DATE(NULL) ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL) SCHEDULE_ARRIVAL_DATE
, TO_DATE(NULL) 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 PO_HEADERS POH
, PO_LINE_LOCATIONS_ALL 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
, MTL_PARAMETERS MPD
, CSP_PART_PRIORITIES_V CPPV
, JTF_TASKS_VL JTV
, JTF_TASK_ASSIGNMENTS JTA
, JTF_OBJECTS_VL JOV
, JTF_OBJECTS_VL JOV1
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
AND MPD.ORGANIZATION_ID (+) = CRH.DESTINATION_ORGANIZATION_ID
AND JTV.TASK_ID(+) = CRH.TASK_ID
AND JTA.TASK_ASSIGNMENT_ID(+) = CRH.TASK_ASSIGNMENT_ID
AND JTA.ASSIGNEE_ROLE(+) = 'ASSIGNEE'
AND JOV.OBJECT_CODE(+) = JTA.RESOURCE_TYPE_CODE
AND JOV1.OBJECT_CODE(+) = CRH.RESOURCE_TYPE