Monday 2 January 2017

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



No comments:

Post a Comment