PO 常用SQL

PO Study -- PO常用SQL

作者：Sky

1,输入税后金额，得到税前金额。（知道line id&line location id）
必须保存产生line id后才可以得到税的信息。
参考文件：http://etrm.oracle.com/pls/trm120/etrm_fndnav.show_file?n_file_id=148
SQL:

SELECT zx.tax_rate
  FROM zx_lines              zx,
       po_headers_all        ph,
       po_lines_all          pl,
       po_line_locations_all pll
 WHERE pl.po_header_id = ph.po_header_id
   AND pll.po_line_id = pl.po_line_id
   AND pll.po_header_id = ph.po_header_id
   AND zx.trx_line_id = pll.line_location_id
   AND zx.application_id = ''
   AND ph.segment1 = ''
   AND pl.line_num = ''
   AND ph.org_id = ''

2,修改purchase order&requisition 的Document number.要求每月重新初始化sequence。格式：YYYYMM+9999 2010041800001

SQL:

PROCEDURE update_document_number(p_table_name IN VARCHAR2, p_seq IN NUMBER) IS
BEGIN
  UPDATE po.po_unique_identifier_cont_all
     SET current_max_unique_identifier = p_seq,
         last_update_date              = SYSDATE,
         last_updated_by               = fnd_global.user_id
   WHERE table_name = p_table_name
     AND org_id = fnd_profile.VALUE('ORG_ID')
END
PROCEDURE create_document_number IS
BEGIN
  update_document_number('PO_REQUISITION_HEADERS',
                         to_char(SYSDATE, 'yyyymm')  '0000')
  update_document_number('PO_HEADERS',
                         to_char(SYSDATE, 'yyyymm')  '0000')
END

3，获得供应商的料号。
在requisition中，根据line中的ASL，自动带出供应商的料号。
SQL:

FUNCTION get_vendor_product_num(p_item_id               IN NUMBER,
                                p_vendor_id             IN NUMBER,
                                p_vendor_site_id        IN NUMBER,
                                p_using_organization_id IN NUMBER)
  RETURN VARCHAR2 IS
  x_using_organization_id NUMBER := p_using_organization_id
  x_asl_id                NUMBER
  x_vendor_product_num    VARCHAR2(240) := ''
  x_purchasing_uom        VARCHAR2(240) := ''
BEGIN
  po_autosource_sv.get_asl_info(p_item_id,
                                p_vendor_id,
                                p_vendor_site_id,
                                x_using_organization_id,
                                x_asl_id,
                                x_vendor_product_num,
                                x_purchasing_uom)
  RETURN x_vendor_product_num
END

4，看requisition line的料号sql：

SELECT mst.segment1  ' '  mst.description  item_number
  FROM po.po_requisition_lines_all b, mtl_system_items_vl mst
 WHERE b.item_id = mst.inventory_item_id
   AND mst.organization_id = b.destination_organization_id

列出没有销售订单的内部采购订单

---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

关联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

所有取消的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

没有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_description "Description",
       prl.quantity "Qty",
       trunc(prl.need_by_date) "Required By",
       ppf1.full_name "REQUESTOR",
       ppf2.agent_name "BUYER"
  FROM po.po_requisition_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 ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prl.requisition_line_id = prd.requisition_line_id
   AND ppf1.person_id = prh.preparer_id
   AND prh.creation_date BETWEEN ppf1.effective_start_date AND
       ppf1.effective_end_date
   AND ppf2.agent_id(+) = msi.buyer_id
   AND msi.inventory_item_id = prl.item_id
   AND msi.organization_id = prl.destination_organization_id
   AND pll.line_location_id(+) = prl.line_location_id
   AND pll.po_header_id = ph.po_header_id(+)
   AND pll.po_line_id = pl.po_line_id(+)
   AND prh.authorization_status = 'APPROVED'
   AND pll.line_location_id IS NULL
   AND prl.closed_code IS NULL
   AND nvl(prl.cancel_flag, 'N')  'Y'
 ORDER BY 1, 2

5.在PR转PO过程中的(应该是自动创建里面的数据吧)

 
----- List and all data entry from PR till PO
 
SELECT 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 "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.requisition_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_id
 ORDER BY 2

没有自动创建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_description "Description",
       prl.quantity "Qty",
       trunc(prl.need_by_date) "Required By",
       ppf1.full_name "REQUESTOR",
       ppf2.agent_name "BUYER"
  FROM po.po_requisition_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 ph
 WHERE prh.requisition_header_id = prl.requisition_header_id
   AND prl.requisition_line_id = prd.requisition_line_id
   AND ppf1.person_id = prh.preparer_id
   AND prh.creation_date BETWEEN ppf1.effective_start_date AND
       ppf1.effective_end_date
   AND ppf2.agent_id(+) = msi.buyer_id
   AND msi.inventory_item_id = prl.item_id
   AND msi.organization_id = prl.destination_organization_id
   AND pll.line_location_id(+) = prl.line_location_id
   AND pll.po_header_id = ph.po_header_id(+)
   AND pll.po_line_id = pl.po_line_id(+)
   AND prh.authorization_status = 'APPROVED'
   AND pll.line_location_id IS NULL
   AND prl.closed_code IS NULL
   AND nvl(prl.cancel_flag, 'N')  'Y'
 ORDER BY 1, 2

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, SEGMENT1

What 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关联，查看看PR是否有对应的PO

未结PO

----- List all open PO'S
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_all  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')

List and PO With there approval , invoice and payment details

----- List and PO With there approval , invoice and payment details
SELECT a.org_id "ORG ID",
       e.segment1 "VENDOR 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.quantity_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",
       (SELECT decode(ph.approved_flag, 'Y', 'Approved')
          FROM po.po_headers_all ph
         WHERE ph.po_header_id = d.po_header_id) "PO Approved?",
       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",
       (SELECT decode(x.match_status_flag, 'A', 'Approved')
          FROM ap.ap_invoice_distributions_all x
         WHERE x.invoice_distribution_id = b.invoice_distribution_id) "Invoice Approved?",
       a.amount_paid,
       h.amount,
       h.check_id,
       h.invoice_payment_id "Payment Id",
       i.check_number "Cheque Number",
       to_char(trunc(i.check_date)) "PAYMENT DATE"
 
  FROM ap.ap_invoices_all              a,
       ap.ap_invoice_distributions_all b,
       po.po_distributions_all         c,
       po.po_headers_all               d,
       po.po_vendors                   e,
       po.po_vendor_sites_all          f,
       po.po_lines_all                 g,
       ap.ap_invoice_payments_all      h,
       ap.ap_checks_all                i
 WHERE 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 = 'Y'
   AND d.type_lookup_code != 'BLANKET'

posted on 2011-05-10 22:28 Gutirez 阅读(920) 评论(0) 收藏举报