OracleEBSPO常用的查询及TipsWord文件下载.docx
《OracleEBSPO常用的查询及TipsWord文件下载.docx》由会员分享，可在线阅读，更多相关《OracleEBSPO常用的查询及TipsWord文件下载.docx（7页珍藏版）》请在冰豆网上搜索。
RQH.REQUISITION_HEADER_IDandRQL.SOURCE_TYPE_CODE='
INVENTORY'
andRQL.SOURCE_ORGANIZATION_IDisnotnullandnotexists（select'
existinginternalorder'
fromOE_ORDER_LINES_ALLLINwhereLIN.SOURCE_DOCUMENT_LINE_ID=
RQL.REQUISITION_LINE_IDandLIN.SOURCE_DOCUMENT_TYPE_ID=10）orDERBYRQH.REQUISITION_HEADER_ID,
RQL.LINE_NUM;
2.关联PR的PORelationwith
RequistionandPOselectr.segment1"
ReqNum"
p.segment1"
PONum"
frompo_headers_allp,po_distributions_alld,po_req_distributions_allrd,po_requisition_lines_allrl,po_requisition_headers_allrwherep.po_header_id=d.po_header_idandd.req_distribution_id=rd.distribution_idandrd.requisition_line_id=rl.requisition_line_idandrl.requisition_header_id=r.requisition_header_id3.
有取消的PRlistMycancelRequistionselectprh.REQUISITION_HEADER_ID,
prh.PREPARER_ID,
prh.SEGMENT1"
REQNUM"
trunc（prh.CREATION_DATE）,prh.DESCRIPTION,prh.NOTE_TO_AUTHORIZER
fromapps.Po_Requisition_headers_allprh,
apps.po_action_historypah
whereAction_code='
CANCEL'
没有
andpah.object_type_code='
REQUISITION'
andpah.object_id=prh.REQUISITION_HEADER_ID4.
PO的PrlistallPurchaseRequisitionwithouta
PurchaseorderthatmeansaPRhasnotbeenautocreatedtoPO.
select
prh.segment1"
PRNUM"
trunc（prh.creation_date）"
CreateDON"
trunc（prl.creation_date）"
LineCreationDate"
prl.line_num"
Seq#"
msi.segment1"
ItemNum"
prl.item_description"
Description"
prl.quantity"
Qty"
trunc（prl.need_by_date）"
RequiredBy"
ppf1.full_name"
REQUESTOR"
ppf2.agent_name"
BUYER"
from
po.po_requisition_headers_allprh,
po.po_requisition_lines_allprl,
apps.per_people_fppf1,
（selectdistinctagent_id,agent_namefromapps.po_agents_v）ppf2,
po.po_req_distributions_allprd,
inv.mtl_system_items_bmsi,
po.po_line_locations_allpll,
po.po_lines_allpl,
po.po_headers_allph
Where
prh.requisition_header_id=prl.requisition_header_id
andprl.requisition_line_id=prd.requisition_line_id
andppf1.person_id=prh.preparer_id
andprh.creation_datebetweenppf1.effective_start_dateandppf1.effective_end_date
andppf2.agent_id（+）=msi.buyer_id
andmsi.inventory_item_id=prl.item_id
andmsi.organization_id=prl.destination_organization_id
andpll.line_location_id（+）=prl.line_location_id
andpll.po_header_id=ph.po_header_id（+）
ANDPLL.PO_LINE_ID=PL.PO_LINE_ID（+）
ANDPRH.AUTHORIZATION_STATUS='
APPROVED'
ANDPLL.LINE_LOCATION_IDISNULL
ANDPRL.CLOSED_CODEISNULL
ANDNVL（PRL.CANCEL_FLAG,'
N'
）&
lt;
&
gt;
'
Y'
orDERBY1,25.在PR转PO过程中的（应该是自动创建里面的数据吧）ListandalldataentryfromPRtill
POselectdistinctu.description"
Requestor"
porh.segment1as"
ReqNumber"
trunc（porh.Creation_Date）"
CreatedOn"
pord.LAST_UpdateD_BY,porh.Authorization_Status"
Status"
porh.Description"
poh.segment1"
PONumber"
trunc（poh.Creation_date）"
POCreationDate"
poh.AUTHORIZATION_STATUS"
POStatus"
trunc（poh.Approved_Date）"
ApprovedDate"
fromapps.po_headers_allpoh,apps.po_distributions_allpod,apps.po_req_distributions_allpord,apps.po_requisition_lines_allporl,apps.po_requisition_headers_allporh,apps.fnd_useruwhereporh.requisition_header_id=porl.requisition_header_idandporl.requisition_line_id=pord.requisition_line_idandpord.distribution_id=pod.req_distribution_id（+）andpod.po_header_id=poh.po_header_id（+）andporh.created_by=u.user_id
orderby26.没有自动创建PO成功的PRlistall
PurchaseRequisitionwithoutaPurchaseorderthatmeansaPRhasnotbeenautocreatedtoPO.
orDERBY1,27.PR与PO的关联表
PO_DISTRIBUTIONS_ALL=&
PO_HEADER_ID,
REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=&
PO_HEADER_ID,SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL=&
DISTRIBUTION_ID,
REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL=&
REQUISITION_LINE_ID）
PO_REQUISITION_HEADERS_ALL=&
REQUISITION_HEADER_ID,
REQUISITION_LINE_ID,SEGMENT1WhatyouhavetomakeajoinonPO_DISTRIBUTIONS_ALL（REQ_DISTRIBUTION_ID）and
PO_REQ_DISTRIBUTIONS_ALL（DISTRIBUTION_ID）toseeifthereisaPOforthereq.你要做的就是将
PO_DISTRIBUTIONS_ALL的REQ_DISTRIBUTION_ID与
PO_REQ_DISTRIBUTIONS_ALL
中的DISTRIBUTION_ID
Listall
关联，查看看PR是否有对应的PO8.未结POopenPO'
Sselecth.segment1"
PONUM"
h.authorization_status"
STATUS"
l.line_num"
SEQNUM"
ll.line_location_id,
d.po_distribution_id,h.type_lookup_code"
TYPE"
frompo.po_headers_allh,
po.po_lines_alll,po.po_line_locations_allll,po.po_distributions_alldwhereh.po_header_id=l.po_header_idandll.po_line_id=l.po_Line_idandll.line_location_id=d.line_location_idandh.closed_dateisnullandh.type_lookup_codenotin（'
QUOTATION'
）9.Listand
POWiththereapproval,invoiceandpaymentdetails
ListandPOWiththereapproval,invoiceandpaymentdetailsselecta.org_id"
ORGID"
E.SEGMENT1"
VENDORNUM"
e.vendor_name"
SUPPLIERNAME"
UPPER（e.vendor_type_lookup_code）"
VENDORTYPE"
f.vendor_site_code"
VENDORSITECODE"
f.ADDRESS_LINE1"
ADDRESS"
f.city"
CITY"
f.country"
COUNTRY"
to_char（trunc（d.CREATION_DATE））"
PODate"
d.segment1"
d.type_lookup_code"
POType"
c.quantity_ordered"
QTYorDERED"
c.quantity_cancelled"
QTYCANCELLED"
g.item_id"
ITEMID"
g.item_description"
ITEMDESCRIPTION"
g.unit_price"
UNITPRICE"
（NVL（c.quantity_ordered,0）-NVL（c.quantity_cancelled,0））*
NVL（g.unit_price,0）"
POLineAmount"
（selectdecode（ph.approved_FLAG,'
'
Approved'
）frompo.po_headers_allphwhereph.po_header_ID=d.po_header_id）"
PO
Approved?
"
a.invoice_type_lookup_code"
INVOICETYPE"
a.invoice_amount"
INVOICEAMOUNT"
to_char（trunc（a.INVOICE_DATE））"
INVOICEDATE"
a.invoice_num"
INVOICENUMBER"
（selectdecode（x.MATCH_STATUS_FLAG,'
A'
）fromap.ap_invoice_distributions_allxwherex.INVOICE_DISTRIBUTION_ID=b.invoice_distribution_id）"
InvoiceApproved?
a.amount_paid,
h.amount,
h.check_id,
h.invoice_payment_id"
PaymentId"
i.check_number"
ChequeNumber"
to_char（trunc（i.check_DATE））"
PAYMENTDATE"
FROM
AP.AP_INVOICES_ALLA,
AP.AP_INVOICE_DISTRIBUTIONS_ALLB,
PO.PO_DISTRIBUTIONS_ALLC,
PO.PO_HEADERS_ALLD,
PO.PO_VENDORSE,
PO.PO_VENDOR_SITES_ALLF,
PO.PO_LINES_ALLG,
AP.AP_INVOICE_PAYMENTS_ALLH,
AP.AP_CHECKS_ALLI
wherea.invoice_id=b.invoice_idandb.po_distribution_id=c.po_distribution_id（+）andc.po_header_id=d.po_header_id（+）ande.vendor_id（+）=d.VENDOR_IDandf.vendor_site_id（+）=d.vendor_site_idandd.po_header_id=g.po_header_idandc.po_line_id=g.po_line_idanda.invoice_id=h.invoice_idandh.check_id=i.check_idandf.vendor_site_id=i.vendor_site_idandc.PO_HEADER_IDisnotnullanda.payment_status_flag='
andd.type_lookup_code!
='
BLANKET'
升级会员 