Query to fetch Prepaid amount for a Standard Invoice

Query to fetch Prepaid amount for a Standard Invoice

 SELECT  (0 - SUM(NVL(aid.amount,0))) prepaid_amount
    FROM ap_invoice_distributions_all aid,
         ap_invoice_lines_all         ail
     WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND   ail.invoice_id = aid.invoice_id
     AND   ail.line_number = aid.invoice_line_number
     AND   aid.line_type_lookup_code = 'PREPAY'
     AND   aid.prepay_distribution_id IS NOT NULL
     AND   NVL(ail.invoice_includes_prepay_flag, 'N') = 'N';

OR

  SELECT ABS(SUM(amount))
    FROM ap_invoice_lines_all ail
   WHERE ail.invoice_id = &P_invoice_id -- invoice id of std invoice
     AND ail.line_type_lookup_code = 'PREPAY';

API and Query to get AP Invoice Status

API and Query to get AP Invoice Status:

1.  Get Invoice status from API
SELECT ai.invoice_num,
       ai.invoice_amount,
       AP_INVOICES_PKG.GET_APPROVAL_STATUS (ai.invoice_id,
                                            ai.invoice_amount,
                                            ai.payment_status_flag,
                                            ai.invoice_type_lookup_code)
          inv_status
  FROM ap_invoices_all ai
 WHERE invoice_num = '<inv number>' AND org_id = <org id>;



2. Get actual meaning from below query from lookup:
  SELECT lookup_type,
         LOOKUP_CODE,
         MEANING,
         DESCRIPTION,
         TAG,
         START_DATE_ACTIVE,
         END_DATE_ACTIVE,
         ENABLED_FLAG
    FROM FND_LOOKUP_VALUES_VL
   WHERE     (   NVL ('', territory_code) = territory_code
              OR territory_code IS NULL)
         AND (VIEW_APPLICATION_ID = 200)
         AND (SECURITY_GROUP_ID = 0)
         AND lookup_Type = 'NLS TRANSLATION'
         AND LOOKUP_CODE =  '<Inv_status from above query> ' ---'NEVER APPROVED'
ORDER BY LOOKUP_CODE;

AP Invoice Queries

List of PR Requesters for AP Invoice:
 SELECT DISTINCT pda1.deliver_to_person_id
           FROM ap_invoice_distributions_all aida,
                po_distributions_all pda,
                po_line_locations_all plla,
                po_lines_all pla,
                po_distributions_all pda1
          WHERE     aida.invoice_id = p_Transaction_id
                AND aida.po_distribution_id = pda.po_distribution_id
                AND pda.line_location_id = plla.line_location_id
                AND pda.po_line_id = pla.po_line_id
                AND pla.po_line_id = pda1.po_line_id
                AND pda1.deliver_to_person_id IS NOT NULL
                AND (   (    plla.inspection_required_flag = 'N'
                         AND plla.receipt_required_flag = 'N'
                         AND pda.distribution_type = 'STANDARD'
                         AND pda.po_distribution_id = pda1.po_distribution_id)
                     OR EXISTS
                           (SELECT 1
                              FROM ap_invoices_all aia
                             WHERE     aia.invoice_id = aida.invoice_id
                                   AND aia.invoice_type_lookup_code =
                                          'PREPAYMENT'));


List of Project Mangers for Projects on AP Invoice:
SELECT ppp.person_id
             FROM ap_invoice_distributions_all aid, pa_project_players ppp
            WHERE     aid.invoice_id = p_transaction_id
                  AND aid.project_id = ppp.project_id
                  AND ppp.person_id IS NOT NULL
                  AND ppp.project_role_type = 'PROJECT MANAGER'
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (ppp.start_date_active,
                                                      SYSDATE))
                                          AND TRUNC (
                                                 NVL (ppp.end_date_active,
                                                      SYSDATE + 1))
         GROUP BY ppp.person_id;


List of Task Mangers for Projects on AP Invoice:
SELECT pt.task_manager_person_id person_id
             FROM ap_invoice_distributions_all aid, pa_tasks pt
            WHERE     invoice_id = p_transaction_id
                  AND aid.task_id = pt.task_id
                  AND pt.task_manager_person_id IS NOT NULL
                  AND TRUNC (SYSDATE) BETWEEN TRUNC (
                                                 NVL (pt.start_date, SYSDATE))
                                          AND TRUNC (
                                                 NVL (pt.completion_date,
                                                      SYSDATE + 1))
         GROUP BY pt.task_manager_person_id;

Open various accounting periods in Oracle Apps

1. Please check "GL Set of Books Name" profile for the responsibility in which you are receiving,   from System Administrator responsibility. It should be set to the correct Set of Books name

2. Ensure the GL period is open for the accounting period in question using:
    Purchasing -> Setup -> Financials -> Accounting -> Open and Close Periods

3. Ensure the Purchasing period is open for the accounting period in question using:
    Purchasing ->  Setup-> Financials -> Accounting -> Control PO Periods

4. Ensure the Inventory period is open for the accounting period in question using:
    Inventory ->  Accounting Close Cycle -> Inventory Accounting Periods

5. Ensure the Accounts Payable period is open for the accounting period in question using:
    Payables -> Accounting -> Control Payables Period