OracleEBSPO常用的查詢及Tips

1、Oracle EBSPO 常用的查詢及 Tips-used to list allassociated1.列出沒有銷售訂單的內(nèi)部采購訂單 Internal Requisitions that do not have an Internal Sales orderSelect 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.QUA

2、NTITY_CANCELLED,RQL.QUANTITY_DELIVERED ,RQL.CANCEL_FLAG ,RQL.SOURCE_TYPE_CODE ,RQL.SOURCE_ORGANIZATION_ID ,RQL.DESTINATION_ORGANIZATION_ID,RQH.TRANSFERRED_TO_OE_FLAGfromPO_REQUISITION_LINES_ALL RQL,PO_REQUISITION_HEADERS_ALL RQHwhereRQL.REQUISITION_HEADER_ID =RQH.REQUISITION_HEADER_IDand RQL.SOURCE_

3、TYPE_CODE = INVENTORYand RQL.SOURCE_ORGANIZATION_ID is not nulland not exists (select existing internal orderfrom OE_ORDER_LINES_ALL LINwhere LIN.SOURCE_DOCUMENT_LINE_ID =RQL.REQUISITION_LINE_IDand LIN.SOURCE_DOCUMENT_TYPE_ID = 10) orDER BY RQH.REQUISITION_HEADER_ID,RQL.LINE_NUM;2. 關(guān)聯(lián) PR 的 PORelatio

4、n withRequistion and POselect 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.requisitio

5、n_line_id = rl.requisition_line_id and rl.requisition_header_id = r.requisition_header_id 3.有取消的 PRlist My cancel Requistion select prh.REQUISITION_HEADER_ID,prh.PREPARER_ID ,prh.SEGMENT1 REQ NUM,trunc(prh.CREATION_DATE),prh.DESCRIPTION,prh.NOTE_TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh,

6、apps.po_action_history pahwhere Action_code=CANCEL沒有and pah.object_type_code=REQUISITIONand pah.object_id=prh.REQUISITION_HEADER_ID 4.PO 的 Prlist all Purchase Requisition without aPurchase order that meansa PR has not beenautocreated to PO.selectprh.segment1 PR NUM,trunc(prh.creation_date) CreateD O

7、N,trunc(prl.creation_date) Line Creation Date ,prl.line_num Seq #,msi.segment1 Item Num,prl.item_description Description,prl.quantity Qty,trunc(prl.need_by_date) Required By,ppf1.full_name REQUESTOR,ppf2.agent_name BUYERfrompo.po_requisition_headers_all prh,po.po_requisition_lines_all prl,apps.per_p

8、eople_f ppf1,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,po.po_req_distributions_all prd,inv.mtl_system_items_b msi,po.po_line_locations_all pll,po.po_lines_all pl,po.po_headers_all phWhereprh.requisition_header_id = prl.requisition_header_idand prl.requisition_line_id = prd.re

9、quisition_line_idand ppf1.person_id = prh.preparer_idand prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_dateand ppf2.agent_id(+) = msi.buyer_idand msi.inventory_item_id = prl.item_idand anization_id = prl.destination_organization_idand pll.line_location_id(+) = prl

10、.line_location_idand pll.po_header_id = ph.po_header_id(+)AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)AND PRH.AUTHORIZATION_STATUS = APPROVEDAND PLL.LINE_LOCATION_ID IS NULLAND PRL.CLOSED_CODE IS NULLAND NVL(PRL.CANCEL_FLAG,N) <> YorDER BY 1,25. 在 PR 轉(zhuǎn) PO 過程中的 ( 應(yīng)該是自動創(chuàng)建里 面的數(shù)據(jù)吧 ) List and all data ent

11、ry from PR tillPOselect distinct u.description Requestor,porh.segment1 as Req Number,trunc(porh.Creation_Date) Created On,pord.LAST_UpdateD_BY, porh.Authorization_Status Status,porh.Description Description, poh.segment1 PO Number, trunc(poh.Creation_date) PO Creation Date, poh.AUTHORIZATION_STATUS P

12、O Status, trunc(poh.Approved_Date) Approved Date from apps.po_headers_all poh, apps.po_distributions_all pod, apps.po_req_distributions_all pord, apps.po_requisition_lines_all porl, apps.po_requisition_headers_all porh, apps.fnd_user u where porh.requisition_header_id = porl.requisition_header_idand

13、 porl.requisition_line_id = pord.requisition_line_idand pord.distribution_id = pod.req_distribution_id(+)and pod.po_header_id = poh.po_header_id(+)and porh.created_by = u.user_idorder by 2 6. 沒有自動創(chuàng)建 PO 成功的 PRlist allPurchase Requisition without a Purchase order that means a PR has not been autocreat

14、ed to PO.selectprh.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_description Description,prl.quantity Qty,trunc(prl.need_by_date) Required By,ppf1.full_name REQUESTOR,ppf2.agent_name BUYERfrompo.po_r

15、equisition_headers_all prh,po.po_requisition_lines_all prl,apps.per_people_f ppf1,(select distinct agent_id,agent_name from apps.po_agents_v ) ppf2,po.po_req_distributions_all prd,inv.mtl_system_items_b msi,po.po_line_locations_all pll,po.po_lines_all pl,po.po_headers_all phWhereprh.requisition_head

16、er_id = prl.requisition_header_idand prl.requisition_line_id = prd.requisition_line_idand ppf1.person_id = prh.preparer_idand prh.creation_date between ppf1.effective_start_date and ppf1.effective_end_dateand ppf2.agent_id(+) = msi.buyer_idand msi.inventory_item_id = prl.item_idand anization_

17、id = prl.destination_organization_idand pll.line_location_id(+) = prl.line_location_idand pll.po_header_id = ph.po_header_id(+)AND PLL.PO_LINE_ID = PL.PO_LINE_ID(+)AND PRH.AUTHORIZATION_STATUS = APPROVEDAND PLL.LINE_LOCATION_ID IS NULLAND PRL.CLOSED_CODE IS NULLAND NVL(PRL.CANCEL_FLAG,N) <> Yo

18、rDER BY 1,27.PR 與 PO 的關(guān)聯(lián)表PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_IDPO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_IDPO_REQUISITION_LINES_ALL=>REQUISITION_LINE_ID)PO_REQUISITION_HEADERS_ALL=>REQUISITION_HEADER_ID, REQUIS

19、ITION_LINE_ID, SEGMENT1What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req. 你要做的就是將 PO_DISTRIBUTIONS_ALL 的 REQ_DISTRIBUTION_ID 與 PO_REQ_DISTRIBUTIONS_ALL 中的 DISTRIBUTION_ID 關(guān)聯(lián)，查看看 PR是否有對應(yīng)的 P08.未

20、結(jié)PO- List all open POSselecth.segment1 PO NUM,h.authorization_status STATUS,I. line_num SEQ NUM,II. line_location_id,d. po_distribution_id ,h.type_lookup_code TYPEfrom po.po_headers_all h,po.po_lines_all l, po.po_line_locations_all ll,po.po_distributions_all dwhere h.po_header_id = l.po_header_idand

21、 ll.po_line_id = l.po_Line_idand ll.line_location_id = d.line_location_idand h.closed_date is nulland h.type_lookup_code not in (QUOTATION) 9.List andPO With there approval , invoice and payment detailsList and PO With there approval , invoice and payment detailsselect _id ORG ID,E.SEGMENT1 VEN

22、DOR NUM,e. vendor_name SUPPLIER NAME,UPPER(e.vendor_type_lookup_code) VENDOR TYPE,f. vendor_site_code VENDOR SITE CODE,f. ADDRESS_LINE1 ADDRESS,f. city CITY, f.country COUNTRY, to_char(trunc(d.CREATION_DATE) PO Date, d.segment1 PO NUM, d.type_lookup_code PO Type, c.quantity_ordered QTY orDERED, c.qu

23、antity_cancelled QTY CANCELLED,g. item_id ITEM ID ,g. item_description ITEM DESCRIPTION,g. unit_price UNIT PRICE,(NVL(c.quantity_ordered,0)-NVL(c.quantity_cancelled,0)*NVL(g.unit_price,0) PO Line Amount,(selectdecode(ph.approved_FLAG, Y, Approved)from po.po_headers_all phwhere ph.po_header_ID = d.po

24、_header_id)POApproved?, a.invoice_type_lookup_code INVOICE TYPE, a.invoice_amount INVOICE AMOUNT, to_char(trunc(a.INVOICE_DATE) INVOICE DATE,a.invoice_num INVOICE NUMBER,(selectdecode(x.MATCH_STATUS_FLAG, A, Approved)from ap.ap_invoice_distributions_all xwhere x.INVOICE_DISTRIBUTION_ID =b.invoice_distribution_id)Invoice Approved?,a.amount_paid