ORACLE MASTERMINDS

Uninvoiced Receipts Report

Description

Uninvoiced Receipts report is designed to provide a preview of all accrual entries that will be posted to your account in a specified month.

/*Query 1* Company and Ledger Details/

SELECT   gsb.name                      c_company

,        fsp.inventory_organization_id c_organization_id

,        gsb.currency_code             GL_CURRENCY

,        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

,        glps.end_date                c_end_date

FROM     --gl_sets_of_books              gsb          

         GL_LEDGERS              gsb                   

,        financials_system_params_all  fsp

,        mtl_default_sets_view         mdv

,        fnd_lookups                   flo1

,        fnd_lookups                   flo2

,        gl_period_statuses       glps

--WHERE    gsb.set_of_books_id           = fsp.set_of_books_id     

WHERE    gsb.LEDGER_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'

AND      glps.application_id           = 201

AND      glps.set_of_books_id           = fsp.set_of_books_id

AND      ((:P_period_name is NULL

                AND trunc(sysdate) between

        trunc(glps.start_date) and trunc(glps.end_date))

OR

              (:P_period_name is not NULL

AND      :P_period_name = glps.period_name))

/*Query 2* Header Details/

SELECT pov.vendor_name c_sort,

          poh.segment1

       || DECODE (porl.release_num, NULL, '', ' - ' || porl.release_num)

                                                                   po_release,

       plt.line_type line_type, pol.line_num line, pol.item_description,

       pov.vendor_name vendor, poh.currency_code c_currency,

       fnc.minimum_accountable_unit min_acc_unit,

       pll.price_override unit_price, pll.shipment_num shipment,

       pll.unit_meas_lookup_code unit, pod.distribution_num distribution,

       pod.quantity_ordered dist_quantity, pll.quantity ship_quantity,

       NVL (pll.price_override, 0) shipment_price,

       pll.line_location_id p_po_line_location_id, apid.quantity_invoiced,

       mca.segment1, mca.segment2, mca.segment3, mca.segment4, mca.segment5,

       mca.segment6, mca.segment7, mca.segment8, mca.segment9, mca.segment10,

       mca.segment11, mca.segment12, mca.segment13, mca.segment14,

       mca.segment15, mca.segment16, mca.segment17, mca.segment18,

       mca.segment19, mca.segment20, pod.quantity_ordered dist_qty_ordered,

       pll.quantity ship_qty_ordered, fnc.PRECISION PRECISION,

       po_tax_sv.get_tax ('PO', pod.po_distribution_id) tax,

       NVL (NVL (pod.rate, poh.rate), 1) exchange_rate,

       NVL (pll.match_option, 'P') match_option,

       NVL (apid.rcv_transaction_id, -1) ap_rcv_txn_id,

       apid.invoice_id apid_invoice_id, pod.attribute4 project_number,

       poh.creation_date order_date, pvs.attribute1

  FROM po_lines_all pol,

       po_headers_all poh,

       ap_suppliers pov,

       po_line_types_vl plt,

       po_line_locations_all pll,

       po_releases_all porl,

       mtl_system_items_b msi,

       mtl_categories_vl mca,

       fnd_currencies fnc,

       po_distributions_all pod,

       ap_invoice_distributions_all apid,

       gl_code_combinations gcc1,

       gl_code_combinations gcc2,

       ap_supplier_sites_all pvs

 WHERE poh.po_header_id = pol.po_header_id

   AND pol.po_line_id = pll.po_line_id

   AND pll.line_location_id = pod.line_location_id

   AND pod.po_distribution_id = apid.po_distribution_id(+)

   AND poh.type_lookup_code IN ('BLANKET', 'STANDARD', 'PLANNED')

   AND pll.shipment_type IN ('BLANKET', 'STANDARD', 'SCHEDULED')

   AND pol.line_type_id = plt.line_type_id

   AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN

                                                      ('RATE', 'FIXED PRICE')

   AND DECODE (NVL (:p_inc_online_accruals, 'N'),

               'Y', 'N',

               NVL (pll.accrue_on_receipt_flag, 'N')

              ) = 'N'

   AND gcc1.code_combination_id = pod.code_combination_id

   AND gcc2.code_combination_id = pod.accrual_account_id

   AND (pll.quantity - NVL (pll.quantity_cancelled, 0) != 0)

   AND (   (    NVL (pll.accrue_on_receipt_flag, 'N') = 'N'

            AND NVL (pod.accrue_on_receipt_flag, 'N') = 'N'

           )

        OR (    NVL (:p_inc_online_accruals, 'N') = 'Y'

            AND NVL (pll.accrue_on_receipt_flag, 'N') = 'Y'

            AND NVL (pod.accrue_on_receipt_flag, 'N') = 'Y'

           )

       )

   AND msi.inventory_item_id(+) = pol.item_id

   AND :organization_id = NVL (msi.organization_id, :organization_id)

   AND mca.category_id = pol.category_id

   AND porl.po_release_id(+) = pll.po_release_id

   AND poh.vendor_id = pov.vendor_id

   AND fnc.currency_code = :c_functional_currency

   AND EXISTS (

          SELECT 'Got a receipt for this shipment'

            FROM rcv_transactions rct

           WHERE pll.line_location_id = rct.po_line_location_id

             AND NVL (rct.consigned_flag, 'N') = 'N'

             AND rct.transaction_type IN ('RECEIVE', 'MATCH')

             AND TRUNC (rct.transaction_date) <= :end_date)

   AND (:p_vendor_from IS NULL OR pov.vendor_name >= :p_vendor_from)

   AND (:p_vendor_to IS NULL OR pov.vendor_name <= :p_vendor_to)

   AND (   NVL (pod.accrued_flag, 'N') =

               DECODE (:p_yes_no,

                       NULL, NVL (pod.accrued_flag, 'N'),

                       :p_yes_no

                      )

        OR (NVL (:p_yes_no, 'Y') = 'Y' AND pod.accrue_on_receipt_flag = 'Y')

       )

   AND NVL (apid.line_type_lookup_code, 'PO_NOT_INVOICED') <> 'PREPAY'

   AND pov.vendor_id = pvs.vendor_id

   AND poh.vendor_site_id = pvs.vendor_site_id

   AND pvs.org_id = fnd_profile.VALUE ('ORG_ID')

   AND NVL (pvs.attribute1, '%') LIKE NVL (:ap_owner, '%')

  

   UNION ALL

  

   SELECT pov.vendor_name c_sort,

          poh.segment1

       || DECODE (porl.release_num, NULL, '', ' - ' || porl.release_num)

                                                                   po_release,

       plt.line_type line_type, pol.line_num line, pol.item_description,

       pov.vendor_name vendor, poh.currency_code c_currency,

       fnc.minimum_accountable_unit min_acc_unit,

       pll.price_override unit_price, pll.shipment_num shipment,

       pll.unit_meas_lookup_code unit, pod.distribution_num distribution,

       pod.quantity_ordered dist_quantity, pll.quantity ship_quantity,

       1 shipment_price, pll.line_location_id p_po_line_location_id,

       apid.quantity_invoiced, mca.segment1, mca.segment2, mca.segment3,

       mca.segment4, mca.segment5, mca.segment6, mca.segment7, mca.segment8,

       mca.segment9, mca.segment10, mca.segment11, mca.segment12,

       mca.segment13, mca.segment14, mca.segment15, mca.segment16,

       mca.segment17, mca.segment18, mca.segment19, mca.segment20,

       pod.amount_ordered dist_qty_ordered, pll.amount ship_qty_ordered,

       fnc.PRECISION PRECISION,

       po_tax_sv.get_tax ('PO', pod.po_distribution_id) tax,

       NVL (NVL (pod.rate, poh.rate), 1) exchange_rate,

       NVL (pll.match_option, 'P') match_option,

       NVL (apid.rcv_transaction_id, -1) ap_rcv_txn_id,

       apid.invoice_id apid_invoice_id, pod.attribute4 project_number,

       poh.creation_date order_date, pvs.attribute1

  FROM po_lines_all pol,

       po_headers_all poh,

       ap_suppliers pov,

       po_line_types_vl plt,

       po_line_locations_all pll,

       po_releases_all porl,

       mtl_system_items_b msi,

       mtl_categories_vl mca,

       fnd_currencies fnc,

       po_distributions_all pod,

       ap_invoice_distributions_all apid,

       gl_code_combinations gcc1,

       gl_code_combinations gcc2,

       ap_supplier_sites_all pvs

 WHERE poh.po_header_id = pol.po_header_id

   AND pol.po_line_id = pll.po_line_id

   AND pll.line_location_id = pod.line_location_id

   AND pod.po_distribution_id = apid.po_distribution_id(+)

   AND poh.type_lookup_code IN ('BLANKET', 'STANDARD', 'PLANNED')

   AND pll.shipment_type IN ('BLANKET', 'STANDARD', 'SCHEDULED')

   AND pol.line_type_id = plt.line_type_id

   AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN

                                                      ('RATE', 'FIXED PRICE')

   AND DECODE (NVL (:p_inc_online_accruals, 'N'),

               'Y', 'N',

               NVL (pll.accrue_on_receipt_flag, 'N')

              ) = 'N'

   AND gcc1.code_combination_id = pod.code_combination_id

   AND gcc2.code_combination_id = pod.accrual_account_id

   AND (pll.amount - NVL (pll.amount_cancelled, 0) != 0)

   AND (   (    NVL (pll.accrue_on_receipt_flag, 'N') = 'N'

            AND NVL (pod.accrue_on_receipt_flag, 'N') = 'N'

           )

        OR (    NVL (:p_inc_online_accruals, 'N') = 'Y'

            AND NVL (pll.accrue_on_receipt_flag, 'N') = 'Y'

            AND NVL (pod.accrue_on_receipt_flag, 'N') = 'Y'

           )

       )

   AND msi.inventory_item_id(+) = pol.item_id

   AND :organization_id = NVL (msi.organization_id, :organization_id)

   AND mca.category_id = pol.category_id

   AND porl.po_release_id(+) = pll.po_release_id

   AND poh.vendor_id = pov.vendor_id

   AND fnc.currency_code = :c_functional_currency

   AND EXISTS (

          SELECT 'Got a receipt for this shipment'

            FROM rcv_transactions rct

           WHERE pll.line_location_id = rct.po_line_location_id

             AND NVL (rct.consigned_flag, 'N') = 'N'

             AND rct.transaction_type IN ('RECEIVE', 'MATCH')

             AND TRUNC (rct.transaction_date) <= :end_date)

   AND (:p_vendor_from IS NULL OR pov.vendor_name >= :p_vendor_from)

   AND (:p_vendor_to IS NULL OR pov.vendor_name <= :p_vendor_to)

   AND (   NVL (pod.accrued_flag, 'N') =

               DECODE (:p_yes_no,

                       NULL, NVL (pod.accrued_flag, 'N'),

                       :p_yes_no

                      )

        OR (NVL (:p_yes_no, 'Y') = 'Y' AND pod.accrue_on_receipt_flag = 'Y')

       )

   AND NVL (apid.line_type_lookup_code, 'PO_NOT_INVOICED') <> 'PREPAY'

   AND pov.vendor_id = pvs.vendor_id

   AND poh.vendor_site_id = pvs.vendor_site_id

   AND pvs.org_id = fnd_profile.VALUE ('ORG_ID')

   AND NVL (pvs.attribute1, '%') LIKE NVL (:ap_owner, '%')

/*Query 3* Received line location Details/

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND ((NVL (rct.source_doc_quantity, 0)), :p_qty_precision)

             ) qty_received,

         rct.transaction_date receipt_date                          

    FROM rcv_transactions rct, po_lines pol

   WHERE rct.transaction_type IN ('RECEIVE', 'MATCH')

     AND TRUNC (rct.transaction_date) <= :end_date

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id, rct.transaction_date

UNION ALL

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND ((NVL (rct.amount, 0)), :p_qty_precision)) qty_received,

         rct.transaction_date receipt_date                         

    FROM rcv_transactions rct, po_lines pol

   WHERE rct.transaction_type IN ('RECEIVE', 'MATCH')

     AND TRUNC (rct.transaction_date) <= :end_date

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id, rct.transaction_date;

/*Query 4* Corrected received line location Details /

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND (NVL (rct1.source_doc_quantity, 0), :p_qty_precision)

             ) qty_corrected

    FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol

   WHERE rct.transaction_type IN ('RECEIVE', 'MATCH')

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct1.transaction_type = 'CORRECT'

     AND rct1.parent_transaction_id = rct.transaction_id

     AND TRUNC (rct1.transaction_date) <= :end_date

     AND rct1.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id

UNION ALL

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND (NVL (rct1.amount, 0), :p_qty_precision)) qty_corrected

    FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol

   WHERE rct.transaction_type IN ('RECEIVE', 'MATCH')

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct1.transaction_type = 'CORRECT'

     AND rct1.parent_transaction_id = rct.transaction_id

     AND TRUNC (rct1.transaction_date) <= :end_date

     AND rct1.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id;

/*Query 5* Return location Details /

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND ((NVL (rct.source_doc_quantity, 0)), :p_qty_precision)

             ) qty_returned

    FROM rcv_transactions rct, po_lines pol

   WHERE rct.transaction_type = 'RETURN TO VENDOR'

     AND TRUNC (rct.transaction_date) <= :end_date

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id

UNION ALL

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND ((NVL (rct.amount, 0)), :p_qty_precision)) qty_returned

    FROM rcv_transactions rct, po_lines pol

   WHERE rct.transaction_type = 'RETURN TO VENDOR'

     AND TRUNC (rct.transaction_date) <= :end_date

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id;

/*Query 6* Corrected return location Details /

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND (NVL (rct1.source_doc_quantity, 0), :p_qty_precision)

             ) qty_returned_corrected

    FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol

   WHERE rct.transaction_type = 'RETURN TO VENDOR'

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct1.transaction_type = 'CORRECT'

     AND rct1.parent_transaction_id = rct.transaction_id

     AND TRUNC (rct1.transaction_date) <= :end_date

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct.po_line_id = pol.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') NOT IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id

UNION ALL

SELECT   rct.po_line_location_id line_location_id,

         SUM (ROUND (NVL (rct1.amount, 0), :p_qty_precision)

             ) qty_returned_corrected

    FROM rcv_transactions rct, rcv_transactions rct1, po_lines pol

   WHERE rct.transaction_type = 'RETURN TO VENDOR'

     AND rct.po_line_location_id = :p_po_line_location_id

     AND rct1.transaction_type = 'CORRECT'

     AND rct1.parent_transaction_id = rct.transaction_id

     AND TRUNC (rct1.transaction_date) <= :end_date

     AND rct.po_line_location_id = :p_po_line_location_id

     AND pol.po_line_id = rct.po_line_id

     AND NVL (pol.order_type_lookup_code, 'QUANTITY') IN

                                                      ('RATE', 'FIXED PRICE')

GROUP BY rct.po_line_location_id;

By

Deepak J