Purchase
Order (With Requisition Preparer)
Description
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;
By
Deepak J
No comments:
Post a Comment