Tuesday, 3 January 2017

  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


1 comment:

  1. eToro is the #1 forex broker for newbie and professional traders.

    ReplyDelete