ORACLE PO Technical

Apps Interfaces, PO Receipt

Apps Interfaces, PO Receipt

Can not update PO via update_po api and getting error in Budgetary Control

Updating PO using PO_CHANGE_API1_S.Update_PO

Oracle Purchasing Tables & PO_CHANGE_API1_S API

Cancel PO Requisitions

API To Cancel or Finally Close Requisition

How to cancel Requisition in Oracle EBS

Canceling Requisitions and Requisition Lines

Queries

Purchase Categories in purchace order

Base tables for Purchase Requisition in Oracle Apps

Relation Between PO_HEADERS_ALL and PO_REQUISITION_HEADERS_ALL

Need Query to find out Buyer,Position and Corresponding Limits in Purchasing

SQL to link Requisitions with Purchase Orders

PO Approval History - SQL Query

Oracle SCM : RFQ (Request For Quotation) Query

Oracle Purchasing Key Tables and their functionalities

ORA-00257: ARCHIVER ERROR. CONNECT INTERNAL ONLY, UNTIL FREED

REQ, PO and Receipt Details in Purchase Order

Relation Between PO_HEADERS_ALL and PO_REQUISITION_HEADERS_ALL

PO_LINES_ALL relation to PO_REQUISITION_LINES_ALL

Relation Between PO_HEADERS_ALL and PO_REQUISITION_HEADERS_ALL

Important Join conditions between Oracle apps modules

Pending Transaction Quantity Done in Sona (Important)

AP/PO Link Queries

to link between AP and PO

APPS TABLES WITH LINKS

API To Cancel or Finally Close Requisition

PO TABLES and LINKS

Oracle Apps Purchase Order (PO) Tables
PO Queries in Oracle Apps

Received Quantity in PO

Invoicing of PO for over received quantity (Functional)

RCV_TRANSACTIONS

Query To Get Po Receipt Received And Shipment Quantity Detail

only Receive quantity not on hand quantity.

Query to Check PO Quantities received for an item in a given date range

 

PO Approval Hierarchy

How To Query Approval Group and Approval Assignment Details For iProcurement And Purchasing Users

Approval hierarchy for Requisition and Purchase orders

PO Requisition Approval hierarchy

query for position hierarchy

Oracle Application “Position Hierarchy SQL Query”

How to Query Position Hierarchy Chain of Command For A User

Link between PO_REQUISITION_LINES_ALL and PO_HEADERS_ALL

PO_REQUISITION_LINES_ALL.BLANKET_PO_HEADER_ID PO_HEADERS_ALL.PO_HEADER_ID

PON_AUCTION_HEADERS_ALL

PON_BID_HEADERS  

Get the Total Purchase Price

FUNCTION get_po_total (x_type_lookup_code IN VARCHAR2,

x_po_header_id IN NUMBER,

x_po_release_id IN NUMBER)

return NUMBER

po_inq_sv.get_po_total (:type_lookup_code,

:po_header_id,

NULL

) total_price

SELECT pha.po_header_id, pha.segment1, pha.revision_num,
pha.authorization_status, pha.vendor_id,
po_inq_sv.get_po_total (pha.type_lookup_code,
pha.po_header_id,
NULL
) po_total_price,
pha.attribute15, pbh.trading_partner_name,
paa.auction_header_id, pbav.bid_number, pbav.sequence_number,
pbav.attribute_name, pbav.VALUE, paa.section_name,
paa.line_number, paa.attr_group_seq_number
FROM po_headers_all pha,
pon_bid_headers pbh,
pon_auction_attributes paa,
pon_bid_attribute_values pbav WHERE ( pha.po_header_id = pbh.po_header_id
AND pha.authorization_status = 'INCOMPLETE'
-- Only Pick the INCOMPLETE status PO
AND pha.revision_num = 0 -- Only pick the 0 revsion Number
AND pha.attribute15 IS NULL
-- check if the discount/cost factor alrady applied
AND pbh.po_header_id = cp_po_header_id
AND pha.org_id = cp_org_id
)
--
AND pbav.line_number = paa.line_number
AND pbav.attribute_name = paa.attribute_name
AND pbav.auction_header_id = paa.auction_header_id
AND pbav.sequence_number = paa.sequence_number
AND paa.attribute_list_id = -1
AND pbav.bid_number = pbh.bid_number
AND pbav.VALUE IS NOT NULL
AND pbav.auction_header_id = pbh.auction_header_id
AND NVL (paa.internal_attr_flag, 'N') =
NVL (pbh.evaluation_flag, 'N')
AND (NVL (pbh.evaluation_flag, 'N')) = 'N'
---------Only Digits record will be filter--------------
AND REGEXP_LIKE (pbav.VALUE, '^[[:digit:]]+$');

----------------------------

/* Formatted on 2016/05/08 16:44 (Formatter Plus v4.8.8) */
SELECT poha.org_id, poha.po_header_id, pora.po_release_id,
       poha.segment1 po_number, pola.po_line_id, plla.line_location_id,
       pola.item_id, poha.type_lookup_code,
       msib.concatenated_segments item_number, pola.item_description,
       msib_tl.long_description, plla.ship_to_organization_id,
       pola.unit_meas_lookup_code, pola.quantity, pola.unit_price,
       (NVL (pola.quantity, 0) * NVL (unit_price, 0)) pol_line_total,
       plla.need_by_date, plla.shipment_type
  FROM po_headers_all poha,
       po_releases_all pora,
       po_lines_all pola,
       po_line_locations_all plla,
       mtl_system_items_b_kfv msib,
       mtl_system_items_tl msib_tl
 WHERE poha.org_id = pola.org_id
   AND poha.po_header_id = pola.po_header_id
   AND poha.po_header_id = pora.po_header_id(+)
   AND pola.po_line_id = plla.po_line_id
   AND msib.inventory_item_id = pola.item_id
   AND msib.organization_id = plla.ship_to_organization_id
   AND msib.organization_id = msib_tl.organization_id(+)
   AND msib.inventory_item_id = msib_tl.inventory_item_id(+)
   AND msib_tl.LANGUAGE(+) = USERENV ('LANG')
--   AND msib.concatenated_segments = '01.001001.0002'
   AND poha.type_lookup_code != 'STANDARD'

----------------------------

SELECT *

  FROM (WITH cate_lookup_with AS

             (SELECT lookup_code, meaning, description, tag,

                     start_date_active, end_date_active, enabled_flag,

                     lookup_type, attribute_category, attribute1, attribute2,

                     attribute3, attribute4, attribute5, attribute6,

                     attribute7, attribute8, attribute9, attribute10,

                     attribute11, attribute12, attribute13, attribute14,

                     attribute15, view_application_id, security_group_id,

                     territory_code, created_by, creation_date,

                     last_update_date, last_updated_by, last_update_login,

                     row_id

                FROM fnd_lookup_values_vl

               WHERE (   NVL ('', territory_code) = territory_code

                      OR territory_code IS NULL

                     )

                 AND lookup_type = 'GS CATEGORY BUYER GROUP MAP'

                 AND (lookup_type = 'GS CATEGORY BUYER GROUP MAP')

                 AND (view_application_id = 3)

                 AND (security_group_id = 0))

        SELECT prla.org_id, prla.destination_organization_id,

               msib.organization_id item_organization_id,

              

               ---(Header Info)

               prha.requisition_header_id, prha.preparer_id,

               paf_request.organization_id request_organization_id,

               hr_general.decode_organization

                               (paf_request.organization_id)

                                                            request_dept_name,

               prha.segment1 requisition_number, prha.description,

               prha.approved_date,

               prha.authorization_status req_authorization_status,

               pah_req.sequence_num req_sequence_num, pah_req.employee_id,

               po_employees_sv.get_emp_name

                                         (pah_req.employee_id)

                                                              req_approved_by,

               hr_general.decode_organization

                                       (paf_app.organization_id)

                                                                app_dept_name,

               pah_req.note req_note,

                                     ---(Detail Info)

                                     prla.requisition_line_id, prla.item_id,

               msib.concatenated_segments service_name, prla.description,

               prla.category_id, mc.concatenated_segments req_category_name,

               prla.creation_date reql_creation_date,

               prla.last_update_date reql_last_modify_date,

               prda.distribution_id, pda.po_distribution_id, pola.po_line_id,

               pola.creation_date pol_creation_date,

               pola.last_update_date pol_last_modify_date, poha.po_header_id,

               poha.segment1 po_number,

               poha.authorization_status po_authorization_status,

               poha.agent_id,

               po_employees_sv.get_emp_name (poha.agent_id) agent_name,

               paf_agent.organization_id agent_organization_id,

               hr_general.decode_organization

                                   (paf_agent.organization_id)

                                                              agent_dept_name,

               :p_user_name disp_user_name

          FROM po_action_history pah_req,

               per_assignments_f paf_app,

               per_assignments_f paf_request,

               po_requisition_headers_all prha,

               po_requisition_lines_all prla,

               mtl_categories_b_kfv mc,

               mtl_system_items_b_kfv msib,

               po_req_distributions_all prda,

               po_distributions_all pda,

               po_lines_all pola,

               po_headers_all poha,

               per_all_people_f ppf_agent,

               per_all_assignments_f paf_agent

         WHERE 1 = 1

           AND prha.requisition_header_id =

                    NVL (:p_requisition_header_id, prha.requisition_header_id)

           ---(link between PO_ACTION_HISTORY and PO_REQUISITION_HEADERS_ALL)

           AND pah_req.object_id = prha.requisition_header_id

           AND (    pah_req.object_type_code = 'REQUISITION'

                AND pah_req.action_code = 'APPROVE'

                AND pah_req.sequence_num IN (

                       SELECT MAX (pah_req1.sequence_num)

                         FROM po_action_history pah_req1

                        WHERE pah_req1.object_id = pah_req.object_id

                          AND pah_req1.action_code = 'APPROVE'

                          AND pah_req1.object_type_code = 'REQUISITION')

               )

           -------(link between PER_ASSIGNMENTS_F and PO_REQUISITION_HEADERS_ALL)

           AND (    prha.preparer_id = paf_request.person_id

                AND SYSDATE BETWEEN paf_request.effective_start_date

                                AND paf_request.effective_end_date

               )

           -------(link between PER_ASSIGNMENTS_F and PO_ACTION_HISTORY)

           AND (pah_req.employee_id = paf_app.person_id(+)

                AND SYSDATE BETWEEN paf_app.effective_start_date(+) AND paf_app.effective_end_date(+))

           ----(link between PO_REQUISITION_HEADERS_ALL and PO_REQUISITION_LINES_ALL)

           AND (    prha.requisition_header_id = prla.requisition_header_id

                AND prha.authorization_status = 'APPROVED'

               )

           -----(link between PO_REQUISITION_HEADERS_ALL and MTL_CATEGORIES_B_KFV)

           AND prla.category_id = mc.category_id

           AND TRIM (mc.concatenated_segments) IN (SELECT TRIM (meaning)

                                                     FROM cate_lookup_with)

           -----(link between PO_REQUISITION_LINES_ALL and MTL_SYSTEM_ITEMS_B_KFV)

           AND (    prla.item_id = msib.inventory_item_id

                AND prla.destination_organization_id = msib.organization_id

               )

           -----(link between PO_REQ_DISTRIBUTIONS_ALL and PO_REQUISITION_LINES_ALL)

           AND prda.requisition_line_id = prla.requisition_line_id

           ---- (link between PO_REQ_DISTRIBUTIONS_ALL and PO_DISTRIBUTIONS_ALL)

           AND prda.distribution_id = pda.req_distribution_id(+)

           -------(link between PO_DISTRIBUTIONS_ALL and PO_LINES_ALL)

           AND pda.po_line_id = pola.po_line_id(+)

           -------(link between PO_LINES_ALL and PO_HEADERS_ALL)

           AND pola.po_header_id = poha.po_header_id(+)

           -------(link between PO_HEADERS_ALL and PER_ALL_PEOPLE_F)

           AND poha.agent_id = ppf_agent.person_id(+)

           AND SYSDATE BETWEEN ppf_agent.effective_start_date(+) AND ppf_agent.effective_end_date(+)

           --------(link between PER_PEOPLE_F and PER_ASSIGNMENTS_F)

           AND ppf_agent.person_id = paf_agent.person_id(+)

           AND SYSDATE BETWEEN paf_agent.effective_start_date(+) AND paf_agent.effective_end_date(+)

--   AND prha.segment1 = '20152000229'--'20152000226'

       )
--------------------------------------------------------------------------------
WITH with_attributes AS
     (SELECT pha.org_id, pha.po_header_id, pha.segment1, pha.revision_num,
             pha.authorization_status, pha.vendor_id,
             pbh.trading_partner_name, paa.auction_header_id, pbav.bid_number,
             pbav.sequence_number, pbav.attribute_name, pbav.VALUE,
             paa.section_name, paa.line_number, paa.attr_group_seq_number
        FROM po_headers_all pha,
             pon_bid_headers pbh,
             pon_auction_attributes paa,
             pon_bid_attribute_values pbav
       WHERE pha.po_header_id = pbh.po_header_id
         AND pbav.line_number = paa.line_number
         AND pbav.attribute_name = paa.attribute_name
         AND pbav.auction_header_id = paa.auction_header_id
         AND pbav.sequence_number = paa.sequence_number
         AND paa.attribute_list_id = -1
         AND pbav.bid_number = pbh.bid_number
--   AND pbav.VALUE IS NOT NULL
         AND pbav.auction_header_id = pbh.auction_header_id
         AND NVL (paa.internal_attr_flag, 'N') =
                                                NVL (pbh.evaluation_flag, 'N')
         AND (NVL (pbh.evaluation_flag, 'N')) = 'N'
         AND paa.attribute_name IN
                ('Delivery Terms',
                 'Shipment Type',
                 'Delivery Days',
                 'Warranty Period'
                ))
SELECT poha.org_id, poha.po_header_id, pora.po_release_id, pora.release_num,
       pora.release_type, poha.segment1 po_number,
       NVL2 (pora.po_release_id,
             pora.release_date,
             poha.creation_date
            ) po_date,
       NVL2 (pora.po_release_id, pora.agent_id, poha.agent_id) agent_id,
       NVL2 (pora.po_release_id,
             papf_por.employee_number,
             papf_poh.employee_number
            ) agent_number,
       NVL2 (pora.po_release_id,
             papf_por.full_name,
             papf_poh.full_name
            ) agent_name,
       NVL2 (pora.po_release_id,
             pora.authorization_status,
             poha.authorization_status
            ) authorization_status,
       poha.vendor_id vendor_id, pv.segment1 vendor_number, pv.vendor_name,
       poha.vendor_site_id, pvs.vendor_site_code, poha.currency_code,
       poha.type_lookup_code,
       po_inq_sv.get_po_total (poha.type_lookup_code,
                               poha.po_header_id,
                               NVL (pora.po_release_id, NULL)
                              ) po_total,
       pbh.bid_number, pbh.auction_header_id
  FROM po_headers_all poha,
       po_releases_all pora,
       pon_bid_headers pbh,
       po_vendors pv,
       po_vendor_sites_all pvs,
       per_all_people_f papf_poh,
       per_all_people_f papf_por
 WHERE pbh.po_header_id = poha.po_header_id
   AND pora.po_header_id(+) = poha.po_header_id
   AND pv.vendor_id = poha.vendor_id
   AND pvs.vendor_site_id = poha.vendor_site_id
   AND poha.agent_id = papf_poh.person_id
   AND SYSDATE BETWEEN papf_poh.effective_start_date
                   AND papf_poh.effective_end_date
   AND pora.agent_id = papf_por.person_id(+)
   AND SYSDATE BETWEEN papf_poh.effective_start_date(+) AND papf_por.effective_end_date(+);