OracleEBSPO常用的查询及TipsWord文件下载.docx

1、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.REQUISITI

2、ON_HEADER_ID,RQL.LINE_NUM;2. 关联 PR 的 PO Relation withRequistion and PO select r.segment1 Req Num,p.segment1 PO Numfrom 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_dist

3、ribution_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_

4、TO_AUTHORIZERfrom apps.Po_Requisition_headers_all prh,apps.po_action_history pahwhere 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 aPurchase order that means a PR has not been autocreated to PO.se

5、lectprh.segment1 PR NUMtrunc（prh.creation_date） CreateD ONtrunc（prl.creation_date） Line Creation Date ,prl.line_num Seq #msi.segment1 Item Numprl.item_description Descriptionprl.quantity Qtytrunc（prl.need_by_date） Required Byppf1.full_name REQUESTORppf2.agent_name BUYERfrompo.po_requisition_headers_

6、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_header_id = prl.requisi

7、tion_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 msi.organization_id = prl.destinatio

8、n_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） <> YorDER BY 1,25. 在 PR

9、转 PO 过程中的 （ 应该是自动创建里 面的数据吧 ） List and all data entry 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 , poh.segment1 PO Number, trunc（poh.Creation_date） P

10、O Creation Date, poh.AUTHORIZATION_STATUS PO 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.requisitio

11、n_header_id = porl.requisition_header_id and porl.requisition_line_id = pord.requisition_line_id and 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. 没有自动创建 PO 成功的 PR list allPurchase Requisition without a Purch

12、ase order that means a PR has not been autocreated to PO.orDER BY 1,27.PR 与 PO 的关联表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_

13、HEADERS_ALL =&REQUISITION_HEADER_ID,REQUISITION_LINE_ID, SEGMENT1What you have to make a join on PO_DISTRIBUTIONS_ALL （REQ_DISTRIBUTION_ID） andPO_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中的

14、 DISTRIBUTION_IDList all关联，查看看 PR 是否有对应的 PO8. 未结 PO open POS select h.segment1 PO NUM, h.authorization_status STATUS, l.line_num SEQ NUM, ll.line_location_id,d.po_distribution_id , h.type_lookup_code TYPE from po.po_headers_all h,po.po_lines_all l, po.po_line_locations_all ll, po.po_distributions_al

15、l d where h.po_header_id = l.po_header_id and ll.po_line_id = l.po_Line_id and ll.line_location_id = d.line_location_id and h.closed_date is null and h.type_lookup_code not in （QUOTATION） 9.List andPO With there approval , invoice and payment detailsList and PO With there approval , invoice and paym

16、ent details select a.org_id ORG IDE.SEGMENT1 VENDOR NUMe.vendor_name SUPPLIER NAMEUPPER（e.vendor_type_lookup_code） VENDOR TYPEf.vendor_site_code VENDOR SITE CODEf.ADDRESS_LINE1 ADDRESSf.city CITY, f.country COUNTRY, to_char（trunc（d.CREATION_DATE） PO Date, d.segment1 , d.type_lookup_code PO Type, c.q

17、uantity_ordered QTY orDERED, c.quantity_cancelled QTY CANCELLEDg.item_id ITEM IDg.item_description ITEM DESCRIPTIONg.unit_price UNIT PRICE（NVL（c.quantity_ordered,0）-NVL（c.quantity_cancelled,0）*NVL（g.unit_price,0） PO Line Amount（select decode（ph.approved_FLAG, , Approved） from po.po_headers_all ph wh

18、ere ph.po_header_ID = d.po_header_id）POApproved?, a.invoice_type_lookup_code INVOICE TYPEa.invoice_amount INVOICE AMOUNT, to_char（trunc（a.INVOICE_DATE） INVOICE DATE, a.invoice_num INVOICE NUMBER（select decode（x.MATCH_STATUS_FLAG, A） from ap.ap_invoice_distributions_all x where x.INVOICE_DISTRIBUTION

19、_ID = b.invoice_distribution_id）Invoice Approved?, a.amount_paid,h.amount,h.check_id,h.invoice_payment_id Payment Idi.check_number Cheque Number, to_char（trunc（i.check_DATE） PAYMENT DATE FROMAP.AP_INVOICES_ALL A,AP.AP_INVOICE_DISTRIBUTIONS_ALL B,PO.PO_DISTRIBUTIONS_ALL C,PO.PO_HEADERS_ALL D,PO.PO_VE

20、NDORS E,PO.PO_VENDOR_SITES_ALL F,PO.PO_LINES_ALL G,AP.AP_INVOICE_PAYMENTS_ALL H,AP.AP_CHECKS_ALL Iwhere a.invoice_id = b.invoice_id and b.po_distribution_id = c. po_distribution_id （+） and c.po_header_id = d.po_header_id （+） and e.vendor_id （+） = d.VENDOR_ID and f.vendor_site_id （+） = d.vendor_site_id and d.po_header_id = g.po_header_id and c.po_line_id = g.po_line_id and a.invoice_id = h.invoice_id and h.check_id = i.check_id and f.vendor_site_id = i.vendor_site_id and c.PO_HEADER_ID is not null and a.payment_status_flag = and d.type_lookup_code != BLANKET