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) ,decode(crld.source_type,'RES',msibk1.concatenated_segments,'IO',msibk2.concatenated_segments,'POREQ',msibk3.concatenated_segments) ,decode(crld.source_type,'RES',msibk1.description,'IO',msibk2.description,'POREQ',msibk3.description) ,decode(crld.source_type,'RES',mr.reservation_quantity,'IO',oola.ordered_quantity,'POREQ',prla.quantity) ,crl.ship_complete_flag ,crld.source_type ,fl.meaning ,crld.source_id ,to_char(decode(crld.source_type,'RES',mr.reservation_id,'IO',ooha.order_number,'POREQ',prha.segment1)) ,decode(crld.source_type,'RES',mr.organization_id,'IO',oola.ship_from_org_id,'POREQ',null,crl.source_organization_id) ,decode(crld.source_type,'RES',mp.organization_code,'IO',mp1.organization_code,'POREQ',null,mp2.organization_code) ,decode(crld.source_type,'RES',mr.subinventory_code,'IO',oola.subinventory,'POREQ',null,crl.source_subinventory) ,decode(crld.source_type,'RES',to_date(null),'IO',nvl(oola.actual_arrival_date,oola.schedule_arrival_date),'POREQ',prla.need_by_date) ,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) ,decode(crh.open_requirement,'N','Closed',decode(crld.source_type,'RES','Reserved','IO',csp_pick_utils.get_attribute_value('STATUS_MEANING'), 'POREQ',plc.displayed_field)) ,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)) ,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_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, csp_requirement_headers crh, oe_order_headers_all ooha, po_requisition_headers_all prha, po_lookup_codes plc, mtl_parameters mp, mtl_parameters mp1, mtl_parameters mp2, 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 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 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 ooha.header_id(+) = oola.header_id and prha.requisition_header_id(+) = prla.requisition_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.actual_arrival_date,oola.schedule_arrival_date), oola.shipping_method_code , crl.likelihood , csp_pick_utils.get_order_status(oola.line_id,oola.flow_status_code), csp_pick_utils.get_attribute_value('STATUS_MEANING'), 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)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MSIBK1.CONCATENATED_SEGMENTS
, 'IO'
, MSIBK2.CONCATENATED_SEGMENTS
, 'POREQ'
, MSIBK3.CONCATENATED_SEGMENTS)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MSIBK1.DESCRIPTION
, 'IO'
, MSIBK2.DESCRIPTION
, 'POREQ'
, MSIBK3.DESCRIPTION)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.RESERVATION_QUANTITY
, 'IO'
, OOLA.ORDERED_QUANTITY
, 'POREQ'
, PRLA.QUANTITY)
, CRL.SHIP_COMPLETE_FLAG
, CRLD.SOURCE_TYPE
, FL.MEANING
, CRLD.SOURCE_ID
, TO_CHAR(DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.RESERVATION_ID
, 'IO'
, OOHA.ORDER_NUMBER
, 'POREQ'
, PRHA.SEGMENT1))
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.ORGANIZATION_ID
, 'IO'
, OOLA.SHIP_FROM_ORG_ID
, 'POREQ'
, NULL
, CRL.SOURCE_ORGANIZATION_ID)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MP.ORGANIZATION_CODE
, 'IO'
, MP1.ORGANIZATION_CODE
, 'POREQ'
, NULL
, MP2.ORGANIZATION_CODE)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, MR.SUBINVENTORY_CODE
, 'IO'
, OOLA.SUBINVENTORY
, 'POREQ'
, NULL
, CRL.SOURCE_SUBINVENTORY)
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, TO_DATE(NULL)
, 'IO'
, NVL(OOLA.ACTUAL_ARRIVAL_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE)
, 'POREQ'
, PRLA.NEED_BY_DATE)
, 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)
, DECODE(CRH.OPEN_REQUIREMENT
, 'N'
, 'CLOSED'
, DECODE(CRLD.SOURCE_TYPE
, 'RES'
, 'RESERVED'
, 'IO'
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('STATUS_MEANING')
, 'POREQ'
, PLC.DISPLAYED_FIELD))
, 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))
, 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_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
, CSP_REQUIREMENT_HEADERS CRH
, OE_ORDER_HEADERS_ALL OOHA
, PO_REQUISITION_HEADERS_ALL PRHA
, PO_LOOKUP_CODES PLC
, MTL_PARAMETERS MP
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, 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 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 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 OOHA.HEADER_ID(+) = OOLA.HEADER_ID
AND PRHA.REQUISITION_HEADER_ID(+) = PRLA.REQUISITION_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.ACTUAL_ARRIVAL_DATE
, OOLA.SCHEDULE_ARRIVAL_DATE)
, OOLA.SHIPPING_METHOD_CODE
, CRL.LIKELIHOOD
, CSP_PICK_UTILS.GET_ORDER_STATUS(OOLA.LINE_ID
, OOLA.FLOW_STATUS_CODE)
, CSP_PICK_UTILS.GET_ATTRIBUTE_VALUE('STATUS_MEANING')
, 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