OracleEBSPO常用的查询及Tips.docxVIP

Oracle EBSPO 常用的查询及 Tips 1.列出没有销售订单的内部采购订单 1.列出没有销售订单的内部采购订单 ---used to list all associatedInternal Requisitions that do not have an associated Internal Sales order Select RQH.SEGMENT1 REQ_NUM, RQL.LINE_NUM, RQL.REQUISITION_HEADER_ID , RQL.REQUISITION_LINE_ID, RQL.ITEM_ID , RQL.UNIT_MEAS_LOOKUP_CODE , RQL.UNIT_PRICE , RQL.QUANTITY , RQL.QUANTITY_CANCELLED, RQL.QUANTITY_DELIVERED , RQL.CANCEL_FLAG , RQL.SOURCE_TYPE_CODE , RQL.SOURCE_ORGANIZATION_ID , RQL.DESTINATION_ORGANIZATION_ID, RQH.TRANSFERRED_TO_OE_FLAG from PO_REQUISITION_LINES_ALL RQL, PO_REQUISITION_HEADERS_ALL RQH where RQL.REQUISITION_HEADER_ID = RQH.REQUISITION_HEADER_ID and RQL.SOURCE_TYPE_CODE = INVENTORY and RQL.SOURCE_ORGANIZATION_ID is not null and not exists (select existing internal order from OE_ORDER_LINES_ALL LIN where LIN.SOURCE_DOCUMENT_LINE_ID = RQL.REQUISITION_LINE_ID and LIN.SOURCE_DOCUMENT_TYPE_ID = 10) orDER BY RQH.REQUISITION_HEADER_ID, RQL.LINE_NUM;2. 关联 PR 的 PO Relation with Requistion and PO select r.segment1 Req Num, p.segment1 PO Num from po_headers_all p, po_distributions_all d, po_req_distributions_all rd, po_requisition_lines_all rl, po_requisition_headers_all r where p.po_header_id = d.po_header_id and d.req_distribution_id = rd.distribution_id and rd.requisition_line_id = rl.requisition_line_id and rl.requisition_header_id = r.requisition_header_id 3. 有取消的 PR list My cancel Requistion select prh.REQUISITION_HEADER_ID,prh.PREPARER_ID , prh.SEGMENT1 REQ NUM, trunc(prh.CREATION_DATE), prh.DESCRIPTION, prh.NOTE_TO_AUTHORIZER from apps.Po_Requisition_headers_all prh, apps.po_action_history pah where Action_code=CANCEL 没有and pah.object_type_code=REQUISITION and pah.object_id=prh.REQUISITION_HEADER_ID 4. 没有 PO 的 Pr list all Purchase Requisition without a Purchase order that means a PR has not been autocreated to PO. select prh.segment1 PR NUM, trunc(prh.creation_date) CreateD ON, trunc(prl.creation_date) Line Creation Date , prl.line_num Seq #, msi.segment1 Item Num, prl.item_descrip