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 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 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.schedule_arrival_date , crh.task_id , crh.task_assignment_id 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 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 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 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
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
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.SCHEDULE_ARRIVAL_DATE
, CRH.TASK_ID
, CRH.TASK_ASSIGNMENT_ID
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
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 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
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