PurchaseOrder (With Requisition Preparer) D…

  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

  • January 3, 2017 | 22 views