Purchase
Order (With Requisition Preparer)
Order (With Requisition Preparer)
Description
To fetch the Purchase Order along with Requisition
Preparer details we can use the following queries.
Preparer details we can use the following queries.
/* Query 1 To Fetch Cancel
PO*/
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
pah2
WHERE pah2.object_id = :poh_po_header_id)
/* Query 2 To Fetch Cancel
Release PO */
Release PO */
SELECT action_date
cancel_release_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 */
Details */
SELECT gsb.NAME c_company, fsp.inventory_organization_id
c_organization_id,
c_organization_id,
gsb.currency_code
base_currency_code,
base_currency_code,
gsb.chart_of_accounts_id
structure_acc, mdv.structure_id
structure_cat,
structure_acc, mdv.structure_id
structure_cat,
mdv.category_set_id
c_category_set_id,
flo1.meaning c_yes,
c_category_set_id,
flo1.meaning c_yes,
flo2.meaning c_no, psp.manual_po_num_type
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 */
and Distribution details */
SELECT line_location_id pod_line_location_id,
po_distribution_id
pod_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 */
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 */
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_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,
security_por_po_num,
poh.revision_num poh_revision_num, poh.vendor_name
poh_vendor_name,
poh_vendor_name,
poh.vendor_address_line1
poh_vendor_address_line1,
poh_vendor_address_line1,
poh.vendor_address_line2
poh_vendor_address_line2,
poh_vendor_address_line2,
poh.vendor_address_line3
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_address_line4,
poh.vendor_state
poh_vendor_address_line5,
poh_vendor_address_line5,
poh.vendor_postal_code
poh_vendor_address_line6,
poh_vendor_address_line6,
poh.vendor_country
poh_vendor_country,
poh.customer_num
poh_customer,
poh_vendor_country,
poh.customer_num
poh_customer,
poh.vendor_num poh_vendor_num, poh.creation_date
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_agent_id,
poh.payment_terms
poh_payment_terms,
poh.ship_via poh_ship_via,
poh_payment_terms,
poh.ship_via poh_ship_via,
poh.fob poh_fob, poh.freight_terms
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_vendor_contact_phone,
poh.note_to_vendor
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_amount_agreed,
poh.cancel_flag
poh_cancel_flag,
poh.confirming_order_flag
poh_confirming_order_flag,
poh_confirming_order_flag,
poh.acceptance_required_flag
poh_acceptance_req_flag,
poh_acceptance_req_flag,
poh.acceptance_due_date
poh_acceptance_due_date,
poh_acceptance_due_date,
poh.currency_code
poh_currency_code,
poh.currency_code
c_currency,
poh_currency_code,
poh.currency_code
c_currency,
poh.currency_name
poh_currency_name,
poh_currency_name,
poh.currency_conversion_rate
poh_currency_conversion_rate,
poh_currency_conversion_rate,
poh.bill_to_location_id
poh_bill_to_location,
poh_bill_to_location,
poh.ship_to_location_id
poh_ship_to_location,
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_po_release_id,
poh.po_type poh_po_type,
poh.approved_flag
poh_approved_flag,
poh.print_count
poh_print_count,
poh_approved_flag,
poh.print_count
poh_print_count,
poh.effective_date
poh_effective_date,
poh_effective_date,
poh.expiration_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
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 */
Notes */
SELECT datatype_id header_note_datatype_id, media_id
header_note_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 */
Details */
SELECT DISTINCT msi.inventory_item_id
msi_item_id, msi.description msi_desc,
msi_item_id, msi.description msi_desc,
msit.description msit_desc
FROM mtl_system_items_b msi, mtl_system_items_tl
msit
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 */
Notes */
SELECT datatype_id item_note_datatype_id, media_id
item_note_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 */
Details */
SELECT DISTINCT pol.line_num pol_line_num, pol.po_item_id
pol_po_item_id,
pol_po_item_id,
pol.revision_num
pol_item_revision,
pol_item_revision,
pol.vendor_product_num
pol_vendor_product_num,
pol_vendor_product_num,
pol.item_description
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_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_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_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_vendor_quote_num,
pol.quotation_line
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_header_id,
pol.from_line_id pol_from_line_id
FROM po_lines_print pol
WHERE –&cancel_where_clause
AND
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 */
Notes */
SELECT datatype_id
line_note_datatype_id, media_id line_note_media_id
line_note_datatype_id, media_id line_note_media_id
FROM fnd_attached_docs_form_vl, financials_system_params_all
fsp
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 */
Shipments Notes */
SELECT plaa3.po_line_id, pll.shipment_num
pll_shipment_num,
pll_shipment_num,
pll.due_date pll_due_date, pll.promised_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,
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_date,
pll.cancel_reason
pll_cancel_reason,
plaa.unit_meas_lookup_code,
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_po_line_id,
pll.line_location_id
pll_line_location_id,
pll_line_location_id,
pll.ship_to_location_id
pll_ship_to_location,
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,
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
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
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
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
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
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;
By
Deepak J
Recent Posts
