Oracle Fusion HCM Cloud/EBS (HCM, Financial): ORACLE PO Technical

Apps Interfaces, PO Receipt

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(+);

Oracle Fusion HCM Cloud/EBS (HCM, Financial)

ORACLE PO Technical

Cancel PO Requisitions

Queries

Received Quantity in PO

PO Approval Hierarchy

Link between PO_REQUISITION_LINES_ALL and PO_HEADERS_ALL

Get the Total Purchase Price

No comments:

Post a Comment

About Me

Blog Archive