PO 常用的查询及Tips.docVIP

PO 常用的查询及Tips 1.鍒楀嚭娌℃湁閿€鍞鍗曠殑鍐呴儴閲囪喘璁㈠崟 绋嬪簭浠ｇ爜 ---used to list all Internal 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鐨凱O 绋嬪簭浠ｇ爜 -----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鐨凱r 绋嬪簭浠ｇ爜 -----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