VIEW: APPS.CSP_REQ_LINE_DETAILS_V

Source

View Text - Preformatted

SELECT crl.inventory_item_id , msibk.concatenated_segments , msibk.description , crl.item_scratchpad , crl.uom_code , crl.revision , crl.required_quantity , DECODE(crld.source_type,'RES',mr.inventory_item_id,'IO',oola.inventory_item_id,'POREQ',prla.item_id,'MO',mtrl.inventory_item_id) , DECODE(crld.source_type,'RES',msibk1.concatenated_segments,'IO',msibk2.concatenated_segments,'POREQ',msibk3.concatenated_segments,'MO',msibk4.concatenated_segments) , DECODE(crld.source_type,'RES',msibk1.description,'IO',msibk2.description,'POREQ',msibk3.description,'MO',msibk4.description) , DECODE(crld.source_type,'RES',mr.reservation_quantity,'IO',oola.ordered_quantity,'POREQ',prla.quantity,'MO',mtrl.quantity) , crl.ship_complete_flag , crld.source_type , fl.meaning , crld.source_id , DECODE(crld.source_type,'RES',TO_CHAR(mr.reservation_id),'IO',ooha.order_number,'POREQ',prha.segment1,'MO',mtrh.request_number) , DECODE(crld.source_type,'RES',mr.organization_id,'IO',oola.ship_from_org_id,'POREQ',NULL,'MO',mtrh.organization_id,crl.source_organization_id) , DECODE(crld.source_type,'RES',mp.organization_code,'IO',mp1.organization_code,'POREQ',NULL,'MO',mp3.organization_code,mp2.organization_code) , DECODE(crld.source_type,'RES',mr.subinventory_code,'IO',oola.subinventory,'POREQ',NULL,'MO',mtrl.from_subinventory_code,crl.source_subinventory) , DECODE(crld.source_type,'RES',to_date(NULL),'IO',NVL(oola.SCHEDULE_ARRIVAL_DATE,NVL(oola.request_date, oola.promise_date)),'POREQ',prla.need_by_date,'MO',to_date(NULL)) , DECODE(crld.source_type,'RES',NULL,'IO',oola.shipping_method_code,NULL) , crl.likelihood , DECODE(crld.source_type,'RES','Reserved','IO',csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code),'POREQ',prha.authorization_status,'MO',mtrh.header_status) , DECODE(crh.open_requirement,'N','Closed',DECODE(crld.source_type,'RES','Reserved','IO',csp_pick_utils.get_line_status_meaning(oola.line_id,oola.booked_flag,oola.flow_status_code), 'POREQ',plc.displayed_field,'MO',TO_CHAR(NULL))) , crl.created_by , crl.creation_date , crl.last_updated_by , crl.last_update_date , crl.last_update_login , crl.requirement_header_id , crl.requirement_line_id , crld.req_line_detail_id , crl.attribute_category , crl.attribute1 , crl.attribute2 , crl.attribute3 , crl.attribute4 , crl.attribute5 , crl.attribute6 , crl.attribute7 , crl.attribute8 , crl.attribute9 , crl.attribute10 , crl.attribute11 , crl.attribute12 , crl.attribute13 , crl.attribute14 , crl.attribute15 , DECODE(crld.source_type,'RES',TO_CHAR(NULL),'IO',csp_pick_utils.get_attribute_value('WAYBILL'), 'POREQ',TO_CHAR(NULL),'MO',TO_CHAR(NULL)) , DECODE(crld.source_type,'RES',NULL,'IO',oola.freight_carrier_code ,NULL) , DECODE(crld.source_type,'RES',to_Date(NULL),'IO',oola.actual_shipment_date,to_date(NULL)) , csm.ship_method_code_meaning FROM csp_requirement_lines crl, csp_req_line_details crld, mtl_reservations mr, oe_order_lines_all oola, po_requisition_lines_all prla, mtl_txn_request_lines mtrl, mtl_system_items_b_kfv msibk, fnd_lookups fl, mtl_system_items_b_kfv msibk1, mtl_system_items_b_kfv msibk2, mtl_system_items_b_kfv msibk3, mtl_system_items_b_kfv msibk4, csp_requirement_headers crh, oe_order_headers_all ooha, po_requisition_headers_all prha, mtl_txn_request_headers mtrh, po_lookup_codes plc, mtl_parameters mp, mtl_parameters mp1, mtl_parameters mp2, mtl_parameters mp3, WSH_CARRIER_SHIP_METHODS_v csm WHERE crld.requirement_line_id(+) = crl.requirement_line_id AND crld.source_id = mr.reservation_id(+) AND crld.source_id = oola.line_id(+) AND crld.source_id = prla.requisition_line_id(+) AND crld.source_id = mtrl.line_id(+) AND crh.requirement_header_id = crl.requirement_header_id AND mp.organization_id(+) = mr.organization_id AND mp1.organization_id(+) = oola.ship_from_org_id AND mp2.organization_id(+) = crl.source_organization_id AND mp3.organization_id(+) = mtrh.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 msibk1.inventory_item_id (+) = mr.inventory_item_id AND msibk1.organization_id(+) = cs_std.get_item_valdn_orgzn_id AND msibk2.inventory_item_id (+) = oola.inventory_item_id AND msibk2.organization_id(+) = cs_std.get_item_valdn_orgzn_id AND msibk3.inventory_item_id (+) = prla.item_id AND msibk3.organization_id(+) = cs_std.get_item_valdn_orgzn_id AND msibk4.inventory_item_id (+) = mtrl.inventory_item_id AND msibk4.organization_id(+) = mtrl.organization_id AND ooha.header_id(+) = oola.header_id AND prha.requisition_header_id(+) = prla.requisition_header_id AND mtrh.header_id(+) = mtrl.header_id AND plc.lookup_type(+) = 'AUTHORIZATION STATUS' AND plc.LOOKUP_CODE(+) = prha.authorization_status AND csm.ship_method_code(+) = oola.shipping_method_code AND csm.organization_id(+) = oola.ship_from_org_id UNION ALL SELECT crl.inventory_item_id , msibk.concatenated_segments , msibk.description , crl.item_scratchpad , crl.uom_code , crl.revision , crl.required_quantity , oola.inventory_item_id, msibk1.concatenated_segments, msibk1.description, oola.ordered_quantity, crl.ship_complete_flag , crld.source_type , fl.meaning , oola.line_id, TO_CHAR(ooha.order_number), oola.ship_from_org_id , mp.organization_code, oola.subinventory, NVL(oola.SCHEDULE_ARRIVAL_DATE,NVL(oola.request_date, oola.promise_date)), oola.shipping_method_code , crl.likelihood , csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code), csp_pick_utils.get_line_status_meaning(oola.line_id,oola.booked_flag,oola.flow_status_code), crl.created_by, crl.creation_date, crl.last_updated_by, crl.last_update_date, crl.last_update_login, crl.requirement_header_id, crl.requirement_line_id, crld.req_line_detail_id, crl.attribute_category, crl.attribute1, crl.attribute2, crl.attribute3, crl.attribute4, crl.attribute5, crl.attribute6, crl.attribute7, crl.attribute8, crl.attribute9, crl.attribute10, crl.attribute11, crl.attribute12, crl.attribute13, crl.attribute14, crl.attribute15, csp_pick_utils.get_attribute_value('WAYBILL'), oola.freight_carrier_code, oola.actual_shipment_date, csm.ship_method_code_meaning FROM csp_requirement_lines crl, csp_req_line_details crld, oe_order_lines_all oola, mtl_system_items_b_kfv msibk, fnd_lookups fl, csp_requirement_headers crh, oe_order_headers_all ooha, mtl_parameters mp, WSH_CARRIER_SHIP_METHODS_v csm, mtl_system_items_b_kfv msibk1 WHERE crld.requirement_line_id(+) = crl.requirement_line_id AND crld.source_id = oola.split_from_line_id AND crh.requirement_header_id = crl.requirement_header_id AND mp.organization_id(+) = oola.ship_from_org_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 ooha.header_id = oola.header_id AND msibk1.inventory_item_id(+) = oola.inventory_item_id AND msibk1.organization_id(+) = cs_std.get_item_valdn_orgzn_id AND oola.header_id = (SELECT header_id FROM oe_order_lines_All WHERE line_id = crld.source_id ) AND csm.ship_method_code(+) = oola.shipping_method_code AND csm.organization_id(+) = oola.ship_from_org_id

View Text - HTML Formatted

SELECT CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, CRL.ITEM_SCRATCHPAD
, CRL.UOM_CODE
, CRL.REVISION
, CRL.REQUIRED_QUANTITY
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.INVENTORY_ITEM_ID
, 'IO'
, OOLA.INVENTORY_ITEM_ID
, 'POREQ'
, PRLA.ITEM_ID
, 'MO'
, MTRL.INVENTORY_ITEM_ID)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MSIBK1.CONCATENATED_SEGMENTS
, 'IO'
, MSIBK2.CONCATENATED_SEGMENTS
, 'POREQ'
, MSIBK3.CONCATENATED_SEGMENTS
, 'MO'
, MSIBK4.CONCATENATED_SEGMENTS)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MSIBK1.DESCRIPTION
, 'IO'
, MSIBK2.DESCRIPTION
, 'POREQ'
, MSIBK3.DESCRIPTION
, 'MO'
, MSIBK4.DESCRIPTION)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.RESERVATION_QUANTITY
, 'IO'
, OOLA.ORDERED_QUANTITY
, 'POREQ'
, PRLA.QUANTITY
, 'MO'
, MTRL.QUANTITY)
, CRL.SHIP_COMPLETE_FLAG
, CRLD.SOURCE_TYPE
, FL.MEANING
, CRLD.SOURCE_ID
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, TO_CHAR(MR.RESERVATION_ID)
, 'IO'
, OOHA.ORDER_NUMBER
, 'POREQ'
, PRHA.SEGMENT1
, 'MO'
, MTRH.REQUEST_NUMBER)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.ORGANIZATION_ID
, 'IO'
, OOLA.SHIP_FROM_ORG_ID
, 'POREQ'
, NULL
, 'MO'
, MTRH.ORGANIZATION_ID
, CRL.SOURCE_ORGANIZATION_ID)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MP.ORGANIZATION_CODE
, 'IO'
, MP1.ORGANIZATION_CODE
, 'POREQ'
, NULL
, 'MO'
, MP3.ORGANIZATION_CODE
, MP2.ORGANIZATION_CODE)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.SUBINVENTORY_CODE
, 'IO'
, OOLA.SUBINVENTORY
, 'POREQ'
, NULL
, 'MO'
, MTRL.FROM_SUBINVENTORY_CODE
, CRL.SOURCE_SUBINVENTORY)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, TO_DATE(NULL)
, 'IO'
, NVL(OOLA.SCHEDULE_ARRIVAL_DATE
, NVL(OOLA.REQUEST_DATE
, OOLA.PROMISE_DATE))
, 'POREQ'
, PRLA.NEED_BY_DATE
, 'MO'
, TO_DATE(NULL))
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, NULL
, 'IO'
, OOLA.SHIPPING_METHOD_CODE
, NULL)
, CRL.LIKELIHOOD
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, 'RESERVED'
, 'IO'
, CSP_PICK_UTILS.GET_ORDER_STATUS(OOLA.LINE_ID
, OOLA.FLOW_STATUS_CODE)
, 'POREQ'
, PRHA.AUTHORIZATION_STATUS
, 'MO'
, MTRH.HEADER_STATUS)
, DECODE(CRH.OPEN_REQUIREMENT
, 'N'
, 'CLOSED'
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, 'RESERVED'
, 'IO'
, CSP_PICK_UTILS.GET_LINE_STATUS_MEANING(OOLA.LINE_ID
, OOLA.BOOKED_FLAG
, OOLA.FLOW_STATUS_CODE)
, 'POREQ'
, PLC.DISPLAYED_FIELD
, 'MO'
, TO_CHAR(NULL)))
, CRL.CREATED_BY
, CRL.CREATION_DATE
, CRL.LAST_UPDATED_BY
, CRL.LAST_UPDATE_DATE
, CRL.LAST_UPDATE_LOGIN
, CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, CRL.ATTRIBUTE_CATEGORY
, CRL.ATTRIBUTE1
, CRL.ATTRIBUTE2
, CRL.ATTRIBUTE3
, CRL.ATTRIBUTE4
, CRL.ATTRIBUTE5
, CRL.ATTRIBUTE6
, CRL.ATTRIBUTE7
, CRL.ATTRIBUTE8
, CRL.ATTRIBUTE9
, CRL.ATTRIBUTE10
, CRL.ATTRIBUTE11
, CRL.ATTRIBUTE12
, CRL.ATTRIBUTE13
, CRL.ATTRIBUTE14
, CRL.ATTRIBUTE15
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, TO_CHAR(NULL)
, 'IO'
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('WAYBILL')
, 'POREQ'
, TO_CHAR(NULL)
, 'MO'
, TO_CHAR(NULL))
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, NULL
, 'IO'
, OOLA.FREIGHT_CARRIER_CODE
, NULL)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, TO_DATE(NULL)
, 'IO'
, OOLA.ACTUAL_SHIPMENT_DATE
, TO_DATE(NULL))
, CSM.SHIP_METHOD_CODE_MEANING
FROM CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, MTL_RESERVATIONS MR
, OE_ORDER_LINES_ALL OOLA
, PO_REQUISITION_LINES_ALL PRLA
, MTL_TXN_REQUEST_LINES MTRL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, FND_LOOKUPS FL
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
, MTL_SYSTEM_ITEMS_B_KFV MSIBK2
, MTL_SYSTEM_ITEMS_B_KFV MSIBK3
, MTL_SYSTEM_ITEMS_B_KFV MSIBK4
, CSP_REQUIREMENT_HEADERS CRH
, OE_ORDER_HEADERS_ALL OOHA
, PO_REQUISITION_HEADERS_ALL PRHA
, MTL_TXN_REQUEST_HEADERS MTRH
, PO_LOOKUP_CODES PLC
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, MTL_PARAMETERS MP3
, WSH_CARRIER_SHIP_METHODS_V CSM
WHERE CRLD.REQUIREMENT_LINE_ID(+) = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_ID = MR.RESERVATION_ID(+)
AND CRLD.SOURCE_ID = OOLA.LINE_ID(+)
AND CRLD.SOURCE_ID = PRLA.REQUISITION_LINE_ID(+)
AND CRLD.SOURCE_ID = MTRL.LINE_ID(+)
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND MP.ORGANIZATION_ID(+) = MR.ORGANIZATION_ID
AND MP1.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID
AND MP2.ORGANIZATION_ID(+) = CRL.SOURCE_ORGANIZATION_ID
AND MP3.ORGANIZATION_ID(+) = MTRH.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 MSIBK1.INVENTORY_ITEM_ID (+) = MR.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK2.INVENTORY_ITEM_ID (+) = OOLA.INVENTORY_ITEM_ID
AND MSIBK2.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK3.INVENTORY_ITEM_ID (+) = PRLA.ITEM_ID
AND MSIBK3.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND MSIBK4.INVENTORY_ITEM_ID (+) = MTRL.INVENTORY_ITEM_ID
AND MSIBK4.ORGANIZATION_ID(+) = MTRL.ORGANIZATION_ID
AND OOHA.HEADER_ID(+) = OOLA.HEADER_ID
AND PRHA.REQUISITION_HEADER_ID(+) = PRLA.REQUISITION_HEADER_ID
AND MTRH.HEADER_ID(+) = MTRL.HEADER_ID
AND PLC.LOOKUP_TYPE(+) = 'AUTHORIZATION STATUS'
AND PLC.LOOKUP_CODE(+) = PRHA.AUTHORIZATION_STATUS
AND CSM.SHIP_METHOD_CODE(+) = OOLA.SHIPPING_METHOD_CODE
AND CSM.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID UNION ALL SELECT CRL.INVENTORY_ITEM_ID
, MSIBK.CONCATENATED_SEGMENTS
, MSIBK.DESCRIPTION
, CRL.ITEM_SCRATCHPAD
, CRL.UOM_CODE
, CRL.REVISION
, CRL.REQUIRED_QUANTITY
, OOLA.INVENTORY_ITEM_ID
, MSIBK1.CONCATENATED_SEGMENTS
, MSIBK1.DESCRIPTION
, OOLA.ORDERED_QUANTITY
, CRL.SHIP_COMPLETE_FLAG
, CRLD.SOURCE_TYPE
, FL.MEANING
, OOLA.LINE_ID
, TO_CHAR(OOHA.ORDER_NUMBER)
, OOLA.SHIP_FROM_ORG_ID
, MP.ORGANIZATION_CODE
, OOLA.SUBINVENTORY
, NVL(OOLA.SCHEDULE_ARRIVAL_DATE
, NVL(OOLA.REQUEST_DATE
, OOLA.PROMISE_DATE))
, OOLA.SHIPPING_METHOD_CODE
, CRL.LIKELIHOOD
, CSP_PICK_UTILS.GET_ORDER_STATUS(OOLA.LINE_ID
, OOLA.FLOW_STATUS_CODE)
, CSP_PICK_UTILS.GET_LINE_STATUS_MEANING(OOLA.LINE_ID
, OOLA.BOOKED_FLAG
, OOLA.FLOW_STATUS_CODE)
, CRL.CREATED_BY
, CRL.CREATION_DATE
, CRL.LAST_UPDATED_BY
, CRL.LAST_UPDATE_DATE
, CRL.LAST_UPDATE_LOGIN
, CRL.REQUIREMENT_HEADER_ID
, CRL.REQUIREMENT_LINE_ID
, CRLD.REQ_LINE_DETAIL_ID
, CRL.ATTRIBUTE_CATEGORY
, CRL.ATTRIBUTE1
, CRL.ATTRIBUTE2
, CRL.ATTRIBUTE3
, CRL.ATTRIBUTE4
, CRL.ATTRIBUTE5
, CRL.ATTRIBUTE6
, CRL.ATTRIBUTE7
, CRL.ATTRIBUTE8
, CRL.ATTRIBUTE9
, CRL.ATTRIBUTE10
, CRL.ATTRIBUTE11
, CRL.ATTRIBUTE12
, CRL.ATTRIBUTE13
, CRL.ATTRIBUTE14
, CRL.ATTRIBUTE15
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('WAYBILL')
, OOLA.FREIGHT_CARRIER_CODE
, OOLA.ACTUAL_SHIPMENT_DATE
, CSM.SHIP_METHOD_CODE_MEANING
FROM CSP_REQUIREMENT_LINES CRL
, CSP_REQ_LINE_DETAILS CRLD
, OE_ORDER_LINES_ALL OOLA
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, FND_LOOKUPS FL
, CSP_REQUIREMENT_HEADERS CRH
, OE_ORDER_HEADERS_ALL OOHA
, MTL_PARAMETERS MP
, WSH_CARRIER_SHIP_METHODS_V CSM
, MTL_SYSTEM_ITEMS_B_KFV MSIBK1
WHERE CRLD.REQUIREMENT_LINE_ID(+) = CRL.REQUIREMENT_LINE_ID
AND CRLD.SOURCE_ID = OOLA.SPLIT_FROM_LINE_ID
AND CRH.REQUIREMENT_HEADER_ID = CRL.REQUIREMENT_HEADER_ID
AND MP.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_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 OOHA.HEADER_ID = OOLA.HEADER_ID
AND MSIBK1.INVENTORY_ITEM_ID(+) = OOLA.INVENTORY_ITEM_ID
AND MSIBK1.ORGANIZATION_ID(+) = CS_STD.GET_ITEM_VALDN_ORGZN_ID
AND OOLA.HEADER_ID = (SELECT HEADER_ID
FROM OE_ORDER_LINES_ALL
WHERE LINE_ID = CRLD.SOURCE_ID )
AND CSM.SHIP_METHOD_CODE(+) = OOLA.SHIPPING_METHOD_CODE
AND CSM.ORGANIZATION_ID(+) = OOLA.SHIP_FROM_ORG_ID