Introduction
To fetch the Purchase Order along with Requisition Preparer details, we can use the following queries.
/* Query 1 To Fetch Cancel PO*/
SELECT action_date cancel_po_date
FROM po_action_history pah
WHERE pah.object_id = :poh_po_header_id
AND ( ( pah.object_type_code = ‘PO’
AND pah.object_sub_type_code IN (‘PLANNED’, ‘STANDARD’)
)
OR ( pah.object_type_code = ‘PA’
AND pah.object_sub_type_code IN (‘BLANKET’, ‘CONTRACT’)
)
)
AND pah.action_code = ‘CANCEL’
AND :poh_po_type != ‘RELEASE’
AND :poh_cancel_flag = ‘Y’
AND sequence_num = (SELECT MAX (sequence_num)
FROM po_action_history pah2
WHERE pah2.object_id = :poh_po_header_id)
/* Query 2 To Fetch Cancel Release PO */
SELECT action_date cancel_release_date
FROM po_action_history pah
WHERE pah.object_id = :poh_po_release_id
AND pah.object_type_code = ‘RELEASE’
AND pah.action_code = ‘CANCEL’
/* Query 3 To Fetch Company Details */
SELECT gsb.NAME c_company, fsp.inventory_organization_id c_organization_id,
gsb.currency_code base_currency_code,
gsb.chart_of_accounts_id structure_acc, mdv.structure_id structure_cat,
mdv.category_set_id c_category_set_id, flo1.meaning c_yes,
flo2.meaning c_no, psp.manual_po_num_type manual_po_num_type
FROM gl_sets_of_books gsb,
financials_system_parameters fsp,
po_system_parameters psp,
mtl_default_sets_view mdv,
fnd_lookups flo1,
fnd_lookups flo2
WHERE gsb.set_of_books_id = fsp.set_of_books_id
AND mdv.functional_area_id = 2
AND flo1.lookup_type = ‘YES_NO’
AND flo1.lookup_code = ‘Y’
AND flo2.lookup_type = ‘YES_NO’
AND flo2.lookup_code = ‘N’
/* Query 4 To Fetch Requestor and Distribution details */
SELECT line_location_id pod_line_location_id,
po_distribution_id pod_po_distribution_id,
requestor_name pod_requestor_name,
ROUND (quantity_ordered, :p_qty_precision) pod_quantity_ordered
FROM po_distributions_print pod
/* Query 5 To Fetch File Name and its details */
SELECT fl.file_name
FROM fnd_documents_vl fdv,
fnd_attached_documents fad,
fnd_lobs fl,
po_headers poh
WHERE fdv.document_id = fad.document_id
AND fad.pk1_value = poh.po_header_id
AND fdv.datatype_name = ‘File’
AND fdv.category_description = ‘To Supplier’
AND fad.entity_name = ‘PO_HEADERS’
AND fdv.media_id = fl.file_id
AND poh.po_header_id = :poh_po_header_id
UNION
SELECT fl.file_name
FROM fnd_documents_vl fdv,
fnd_attached_documents fad,
fnd_lobs fl,
po_requisition_headers prh
WHERE fdv.document_id = fad.document_id
AND fad.pk1_value = prh.requisition_header_id
AND fdv.datatype_name = ‘File’
AND fdv.category_description = ‘To Supplier’
AND fad.entity_name = ‘REQ_HEADERS’
AND fdv.media_id = fl.file_id
AND prh.requisition_header_id = :cp_requisition_id;
/* Query 6 To Fetch Header details */
SELECT DECODE (:p_sortby, ‘PO NUMBER’, NULL, poh.document_buyer_last_name),
DECODE (:p_sortby, ‘PO NUMBER’, NULL, poh.document_buyer_first_name),
DECODE (:po_num_type, ‘NUMERIC’, NULL, poh.po_num),
DECODE (:po_num_type,
‘NUMERIC’, DECODE (RTRIM (poh.po_num, ‘0123456789’),
NULL, TO_NUMBER (poh.po_num),
-1
),
NULL
),
poh.po_type poh_po_type, por.release_type poh_release_type,
poh.po_num
|| DECODE (poh.po_type, ‘RELEASE’, ‘-‘ || por.release_num, NULL)
poh_po_num,
poh.po_num security_poh_po_num, por.release_num security_por_po_num,
poh.revision_num poh_revision_num, poh.vendor_name poh_vendor_name,
poh.vendor_address_line1 poh_vendor_address_line1,
poh.vendor_address_line2 poh_vendor_address_line2,
poh.vendor_address_line3 poh_vendor_address_line3,
DECODE (poh.vendor_city,
NULL, poh.vendor_state || ‘ ‘ || poh.vendor_postal_code,
poh.vendor_city
|| ‘, ‘
|| poh.vendor_state
|| ‘ ‘
|| poh.vendor_postal_code
) poh_vendor_adr_info,
poh.vendor_city poh_vendor_address_line4,
poh.vendor_state poh_vendor_address_line5,
poh.vendor_postal_code poh_vendor_address_line6,
poh.vendor_country poh_vendor_country, poh.customer_num poh_customer,
poh.vendor_num poh_vendor_num, poh.creation_date poh_creation_date,
poh.revised_date poh_revised_date,
SUBSTR ( SUBSTR (poh.document_buyer_first_name, 1, 1)
|| ‘ ‘
|| poh.document_buyer_last_name,
1,
12
) poh_buyer,
SUBSTR
(TRIM ( SUBSTR (poh.archive_buyer_first_name, 1, 1)
|| ‘ ‘
|| poh.archive_buyer_last_name
),
1,
12
) poh_archive_buyer,
poh.document_buyer_agent_id poh_agent_id,
poh.payment_terms poh_payment_terms, poh.ship_via poh_ship_via,
poh.fob poh_fob, poh.freight_terms poh_freight_terms,
SUBSTR (poh.vendor_contact_first_name,
1,
1
)
|| ‘ ‘
|| SUBSTR (poh.vendor_contact_last_name, 1, 10)
poh_vendor_contact_name,
poh.vendor_phone poh_vendor_phone,
poh.vendor_contact_phone poh_vendor_contact_phone,
poh.note_to_vendor poh_note_to_vendor,
poh.printed_date poh_printed_date,
poh.amount_agreed poh_amount_agreed, poh.cancel_flag poh_cancel_flag,
poh.confirming_order_flag poh_confirming_order_flag,
poh.acceptance_required_flag poh_acceptance_req_flag,
poh.acceptance_due_date poh_acceptance_due_date,
poh.currency_code poh_currency_code, poh.currency_code c_currency,
poh.currency_name poh_currency_name,
poh.currency_conversion_rate poh_currency_conversion_rate,
poh.bill_to_location_id poh_bill_to_location,
poh.ship_to_location_id poh_ship_to_location,
poh.po_header_id poh_po_header_id,
poh.po_release_id poh_po_release_id, poh.po_type poh_po_type,
poh.approved_flag poh_approved_flag, poh.print_count poh_print_count,
poh.effective_date poh_effective_date,
poh.expiration_date poh_expiration_date,
NVL (poh.po_release_id, -1) poh_join_release_id,
poh.vendor_site_id poh_vendor_site_id, poh.vendor_id poh_vendor_id
FROM po_headers_print poh, po_releases por
WHERE poh.po_release_id = por.po_release_id(+)
AND ( NVL (por.release_num, -1) BETWEEN NVL (:p_release_num_from,
NVL (por.release_num, -1)
)
AND NVL (:p_release_num_to,
NVL (por.release_num, -1)
)
OR poh.po_type != ‘RELEASE’
)
AND poh.document_buyer_agent_id =
NVL (:p_agent_id, poh.document_buyer_agent_id)
AND ( poh.release_date IS NULL
OR TRUNC (poh.release_date) BETWEEN NVL (:p_date_from,
TRUNC (poh.release_date)
)
AND NVL (:p_date_to,
TRUNC (poh.release_date)
+ 1
)
)
AND NVL (poh.approved_flag, ‘N’) =
NVL (:p_approved_flag, NVL (poh.approved_flag, ‘N’))
AND ( (NVL (:p_print_releases, ‘Y’) = ‘Y’)
OR (NVL (:p_print_releases, ‘Y’) = ‘N’ AND poh.po_type != ‘RELEASE’
)
)
AND NVL (poh.consigned_consumption_flag, ‘N’) <> ‘Y’
AND NVL (por.consigned_consumption_flag, ‘N’) <> ‘Y’
ORDER BY poh_po_num;
/* Query 7 To Fetch Header Notes */
SELECT datatype_id header_note_datatype_id, media_id header_note_media_id
FROM fnd_attached_docs_form_vl
WHERE ( (entity_name = ‘PO_HEADERS’ AND pk1_value = :poh_po_header_id)
OR (entity_name = ‘PO_VENDORS’ AND pk1_value = :poh_vendor_id)
)
AND function_name = ‘PO_PRINTPO’
AND datatype_id IN (1, 2)
ORDER BY seq_num;
/* Query 8 To Fetch Item Details */
SELECT DISTINCT msi.inventory_item_id msi_item_id, msi.description msi_desc,
msit.description msit_desc
FROM mtl_system_items_b msi, mtl_system_items_tl msit
WHERE msi.organization_id = :organization_id
AND msi.inventory_item_id = msit.inventory_item_id(+)
AND msi.organization_id = msit.organization_id(+)
AND USERENV (‘LANG’) = msit.LANGUAGE(+)
/* Query 9 To Fetch Item Notes */
SELECT datatype_id item_note_datatype_id, media_id item_note_media_id
FROM fnd_attached_docs_form_vl
WHERE :pol_po_item_id IS NOT NULL
AND entity_name = ‘MTL_SYSTEM_ITEMS’
AND pk1_value = :organization_id
AND pk2_value = :pol_po_item_id
AND function_name = ‘PO_PRINTPO’
AND datatype_id IN (1, 2)
ORDER BY seq_num;
/* Query 10 To Fetch Line Details */
SELECT DISTINCT pol.line_num pol_line_num, pol.po_item_id pol_po_item_id,
pol.revision_num pol_item_revision,
pol.vendor_product_num pol_vendor_product_num,
pol.item_description pol_item_description,
ROUND (pol.quantity_to_print,
:p_qty_precision
) pol_quantity_to_print,
pol.unit_of_measure,
pol.price_to_print pol_price_to_print,
pol.amount_to_print c_amount_pol,
ROUND (pol.quantity_comitted,
:p_qty_precision
) pol_quantity_comitted,
pol.un_number_and_desc pol_un_number_and_desc,
pol.hazard_class pol_hazard_class,
pol.cancel_flag pol_cancel_flag,
pol.cancel_date pol_cancel_date,
pol.note_to_vendor pol_note_to_vendor,
pol.contract_num pol_contract_num,
pol.po_quote_num pol_po_quote_num,
pol.vendor_quote_num pol_vendor_quote_num,
pol.quotation_line pol_quotation_line,
pol.po_header_id pol_po_header_id,
pol.po_line_id pol_po_line_id, ROWNUM pol_row_num,
pol.line_type pol_line_type,
NVL (pol.po_release_id, -1) pol_join_release_id,
NVL (pol.src_ga_flag, ‘N’) pol_src_ga_flag,
pol.from_header_id pol_from_header_id,
pol.from_line_id pol_from_line_id
FROM po_lines_print pol
WHERE –&cancel_where_clause AND
DECODE (:poh_po_type,
‘BLANKET’, DECODE (:p_blanket_lines, ‘Y’, -1, -2),
‘PLANNED’, DECODE (:p_blanket_lines, ‘Y’, -1, -2),
‘CONTRACT’, DECODE (:p_blanket_lines, ‘Y’, -1, -2),
-1
) = -1
ORDER BY pol.line_num;
/* Query 11 To Fetch Line Notes */
SELECT datatype_id line_note_datatype_id, media_id line_note_media_id
FROM fnd_attached_docs_form_vl, financials_system_params_all fsp
WHERE 1 = 1
AND org_id = fnd_profile.VALUE (‘ORG_ID’)
AND ( (entity_name = ‘PO_LINES’ AND pk1_value = :pol_po_line_id)
OR ( entity_name = ‘PO_LINES’
AND pk1_value = :pol_from_line_id
AND :pol_from_line_id IS NOT NULL
AND :pol_src_ga_flag = ‘Y’
AND ( publish_flag = ‘Y’
OR (security_type = 1 AND security_id = fsp.org_id)
OR (security_type = 2 AND security_id = fsp.set_of_books_id
)
OR (security_type = 4)
)
)
OR ( entity_name = ‘PO_HEADERS’
AND pk1_value = :pol_from_header_id
AND :pol_from_header_id IS NOT NULL
AND :pol_src_ga_flag = ‘Y’
AND ( publish_flag = ‘Y’
OR (security_type = 1 AND security_id = fsp.org_id)
OR (security_type = 2 AND security_id = fsp.set_of_books_id
)
OR (security_type = 4)
)
)
)
AND function_name = ‘PO_PRINTPO’
AND datatype_id IN (1, 2)
ORDER BY seq_num;
/* Query 12 To Fetch Shipments Notes */
SELECT plaa3.po_line_id, pll.shipment_num pll_shipment_num,
pll.due_date pll_due_date, pll.promised_date pll_promised_date,
pll.need_by_date pll_need_by_date, pllaa3.need_by_date,
pllaa2.po_header_id, pllaa2.po_line_id,
plaa.revision_num plaa_revision_num, pllaa1.max_rev_num,
pllaa2.max2_rev_num,
ROUND (pll.quantity_ordered, :p_qty_precision) pll_quantity_ordered,
pll.release_price pll_release_price,
ROUND (pll.quantity_cancelled,
:p_qty_precision
) pll_quantity_cancelled,
pll.cancel_flag pll_cancel_flag, pll.cancel_date pll_cancel_date,
pll.cancel_reason pll_cancel_reason, plaa.unit_meas_lookup_code,
pll.taxable_flag pll_taxable_flag, pll.start_date pll_start_date,
pll.end_date pll_end_date, pll.po_line_id pll_po_line_id,
pll.line_location_id pll_line_location_id,
pll.ship_to_location_id pll_ship_to_location,
NVL (pll.po_release_id, -1) pll_join_release_id,
NVL (pll.consigned_flag, ‘N’) pll_consigned_flag,
pll.amount pll_amount, pll.amount_cancelled pll_amount_cancelled,
DECODE (pha.revision_num – pllaa1.max_rev_num,
0, ( NVL (pllaa.need_by_date, SYSDATE)
– NVL (pllaa3.need_by_date, SYSDATE)
),
( NVL (pllaa.need_by_date, SYSDATE)
– NVL (pllaa.need_by_date, SYSDATE)
)
) pll_need_date_diff,
DECODE (pha.revision_num – plaa2.max_rev_num,
0, ( ASCII (NVL (plaa.unit_meas_lookup_code, ‘U’))
– ASCII (NVL (plaa1.unit_meas_lookup_code, ‘U’))
),
( ASCII (NVL (plaa.unit_meas_lookup_code, ‘U’))
– ASCII (NVL (plaa.unit_meas_lookup_code, ‘U’))
)
) pll_uom_diff,
DECODE (pha.revision_num – plaa2.max_rev_num,
0, (NVL (plaa.quantity, 0) – NVL (plaa1.quantity, 0)),
(NVL (plaa.quantity, 0) – NVL (plaa.quantity, 0)
)
) pll_qty_diff,
DECODE (pha.revision_num – plaa2.max_rev_num,
0, (NVL (plaa.unit_price, 0) – NVL (plaa1.unit_price, 0)),
(NVL (plaa.unit_price, 0) – NVL (plaa.unit_price, 0)
)
) pll_price_diff,
DECODE (plaa2.max_rev_num – pllaa1.max_rev_num,
0, (NVL (plaa.amount, 0) – NVL (plaa1.amount, 0)),
(NVL (plaa.amount, 0) – NVL (plaa.amount, 0)
)
) pll_amount_diff
FROM po_headers_all pha,
po_lines_archive_all plaa,
po_lines_archive_all plaa1,
(SELECT plaa.po_header_id, plaa.po_line_id,
MAX (plaa.revision_num) max_rev_num,
MAX (plaa.revision_num) max2_rev_num, COUNT (*) count1
FROM po_headers_all pha, po_lines_archive_all plaa
WHERE pha.po_header_id = plaa.po_header_id
GROUP BY plaa.po_header_id, plaa.po_line_id) plaa2,
(SELECT pha.po_header_id po_header_id, plaa.po_line_id po_line_id,
max_query.max_rev_num, MAX (plaa.revision_num)
max2_rev_num,
max_query.count1
FROM po_headers_all pha,
po_lines_archive_all plaa,
(SELECT plaa.po_header_id, plaa.po_line_id,
MAX (plaa.revision_num) max_rev_num,
COUNT (*) count1
FROM po_headers_all pha, po_lines_archive_all plaa
WHERE pha.po_header_id = plaa.po_header_id
GROUP BY plaa.po_header_id, plaa.po_line_id
HAVING COUNT (*) <> 1) max_query
WHERE pha.po_header_id = plaa.po_header_id
AND plaa.po_header_id = max_query.po_header_id
AND pha.po_header_id = max_query.po_header_id
AND plaa.po_line_id = max_query.po_line_id
AND ( plaa.revision_num < max_query.max_rev_num
OR DECODE (pha.revision_num, 0, 0) =
DECODE (plaa.revision_num,
0, 0
)
OR DECODE (plaa.revision_num, 0, 0) =
DECODE (max_query.max_rev_num,
0, 0
)
OR DECODE (pha.revision_num, 0, 0) =
DECODE (plaa.revision_num,
0, 0
)
)
GROUP BY pha.po_header_id,
plaa.po_line_id,
max_query.max_rev_num,
max_query.count1
UNION
SELECT plaa.po_header_id, plaa.po_line_id,
MAX (plaa.revision_num) max_rev_num,
MAX (plaa.revision_num) max2_rev_num, COUNT (*) count1
FROM po_headers_all pha, po_lines_archive_all plaa
WHERE pha.po_header_id = plaa.po_header_id
GROUP BY plaa.po_header_id, plaa.po_line_id
HAVING COUNT (*) = 1) plaa3,
po_line_locations_print pll,
po_line_locations_archive_all pllaa,
po_line_locations_archive_all pllaa3,
(SELECT pllaa.po_header_id, pllaa.po_line_id,
MAX (pllaa.revision_num) max_rev_num
FROM po_headers_all pha, po_line_locations_archive_all pllaa
WHERE pha.po_header_id = pllaa.po_header_id
GROUP BY pllaa.po_header_id, pllaa.po_line_id) pllaa1,
(SELECT pha.po_header_id po_header_id, pllaa.po_line_id po_line_id,
max_query.max_rev_num,
MAX (pllaa.revision_num) max2_rev_num, COUNT (*) count1
FROM po_headers_all pha,
po_line_locations_archive_all pllaa,
(SELECT MAX (pllaa.revision_num) max_rev_num,
pllaa.po_header_id, pllaa.po_line_id,
COUNT (*) count1
FROM po_headers_all pha,
po_line_locations_archive_all pllaa
WHERE pha.po_header_id = pllaa.po_header_id
GROUP BY pllaa.po_header_id, pllaa.po_line_id
HAVING COUNT (*) <> 1) max_query
WHERE pha.po_header_id = pllaa.po_header_id
AND pha.po_header_id = max_query.po_header_id
AND pllaa.po_line_id = max_query.po_line_id
AND ( pllaa.revision_num < max_query.max_rev_num
OR DECODE (pllaa.revision_num, 0, 0) =
DECODE (max_query.max_rev_num,
0, 0
)
OR DECODE (pha.revision_num, 0, 0) =
DECODE (max_query.max_rev_num,
0, 0
)
)
GROUP BY pha.po_header_id, pllaa.po_line_id, max_query.max_rev_num
UNION
SELECT pllaa.po_header_id, pllaa.po_line_id,
MAX (pllaa.revision_num) max_rev_num,
MAX (pllaa.revision_num) max2_rev_num, COUNT (*) count1
FROM po_headers_all pha, po_line_locations_archive_all pllaa
WHERE pha.po_header_id = pllaa.po_header_id
GROUP BY pllaa.po_header_id, pllaa.po_line_id
HAVING COUNT (*) = 1) pllaa2
WHERE pll.po_header_id = pha.po_header_id
AND plaa.po_header_id = pha.po_header_id
AND plaa1.po_header_id = pha.po_header_id
AND plaa2.po_header_id = pha.po_header_id
AND plaa3.po_header_id = pha.po_header_id
AND plaa2.po_header_id = plaa3.po_header_id
AND plaa.revision_num = plaa2.max_rev_num
AND pll.po_line_id = plaa2.po_line_id
AND plaa.po_line_id = plaa2.po_line_id
AND plaa1.revision_num = plaa3.max2_rev_num
AND pll.po_line_id = plaa3.po_line_id
AND plaa1.po_line_id = plaa3.po_line_id
AND pllaa.po_header_id = pllaa1.po_header_id
AND pllaa.po_line_id = pllaa1.po_line_id
AND pllaa.revision_num = pllaa1.max_rev_num
AND pllaa2.max2_rev_num = pllaa3.revision_num
AND pllaa2.po_line_id = pllaa3.po_line_id
AND pll.po_line_id = pllaa2.po_line_id
AND pll.po_line_id = pllaa1.po_line_id
AND pha.po_header_id = pllaa.po_header_id
AND pha.po_header_id = pllaa1.po_header_id
AND pha.po_header_id = pllaa2.po_header_id
AND pllaa2.po_header_id = pllaa3.po_header_id
ORDER BY plaa2.po_line_id, plaa2.max_rev_num DESC, plaa3.max2_rev_num DESC;
What we expect in the script.
This script helps us to comprehend how Purchase Order along with Requisition Preparer details. Couple of tables which is being used in the query are
po_headers_all pha,
po_lines_archive_all plaa,
po_lines_archive_all plaa1 etc.
Summary
This Post described the script Purchase Order along with Requisition Preparer details in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
