Thursday 21 September 2017

APLIST for an invoice using oracle Diagnostic tool
  Navigation -- Oracle Diagnostic Tool

Select application -- Payables

  Advance tab > Invoice > Invoice Data
Please enter the Responsbility_id , Search criteria (Invoice or Check) , Supplier Name, Invoice Id , Include GL Details and Include Related Data. Click on Run Test
 Below you can able to see the Results. Click on Report and then you can print/save the result file.



When the invoice Approval status is Not Required or Initiated Payables > Setup > Options > Payables


WFAPPROVAL_STATUS Types
NOT REQUIRED = When the Invoice Approval is not required (Use Invoice
Approval Workflow option On Invoice Tab of Payables Options is set to no)
REQUIRED = When the Invoice Approval is required (Use Invoice Approval
Workflow option On Invoice Tab of Payables Options is set to Yes)
INITIATED = When the Invoice Approval is required and the user initiates the
Approval Process (submits the invoice for the approval to the approver)
REJECTED = when the Approver rejects the Invoice
WFAPPROVED = when the Approver Approves the Invoice
MANUALLY APPROVED = when the user forcefully approves the Invoice without
submitting it to the approver for the approval.( (Allow Force Approval
option On Invoice Tab of Payables Options is set to Yes)




Question 1 if "Use Invoice Approval Workflow " option On Invoice Tab is set
to YES that means it is applicable for all the invoices then why database is
having wf_approval_status is 'Not required' for some invoices and
'WFAPPROVED' for some invoices?
Answer :- I can tell you give you the following reasons (there can be other
reasons too)
1. There can be a possibility that the ""Use Invoice Approval Workflow"
was set to NO earlier and the invoices are uploaded/ entered. But after that
some one has set this option to YES and uploaded/ entered the invoices or
the vice-versa. That is why some invoices have not required status.
2. The invoices are uploaded/ entered through two different
responsibilities which have different Operating unit. For one responsibility
this option is set to Yes and for another is it set to NO. (Please compare
the Org_id and the Responsibility_id)

Another Solution
APINV workflow customized if invoice matched to PO then wfapproval_status will be "NOT REQUIRED" and 
if invoice is not matched to PO then wfapproval_status will be "INITIATED".



/* Procedure:  iaw_po_check
  * Purpose: This procedure is a copy of AP_WFAPPROVAL_PKG.iaw_po_check.  It has been modified to change the logic so that any invoice with even one line associated with a PO will not require apporval.
 */
   PROCEDURE iaw_po_check (
      itemtype    IN              VARCHAR2,
      itemkey     IN              VARCHAR2,
      actid       IN              NUMBER,
      funcmode    IN              VARCHAR2,
      resultout   OUT NOCOPY      VARCHAR2
   )
   IS
      l_po_count            NUMBER;
      l_check_po_match      VARCHAR2 (3);
      l_org_id              NUMBER;
      l_debug               VARCHAR2 (240);
      l_api_name   CONSTANT VARCHAR2 (100)  := 'iaw_po_check';
      l_debug_info          VARCHAR2 (500);
      l_temp_invoice_id     NUMBER (15);                       -- Bug 5037108
      l_error_message       VARCHAR2 (2000);                   -- Bug 5037108
   BEGIN
      IF (g_level_procedure >= g_current_runtime_level)
      THEN
         fnd_log.STRING (g_level_procedure,
                         g_module_name || l_api_name,
                         'AP_WFAPPROVAL_PKG.iaw_po_check (+)'
                        );
      END IF;

--check 'Approve PO Matched' flag here
      l_check_po_match :=
                   wf_engine.getitemattrtext (itemtype, itemkey, 'APINV_AAPO');

             --we need to get the org_id until I can change the raise event
      --in the invoice workbench
      SELECT org_id
        INTO l_org_id
        FROM ap_invoices_all
       WHERE invoice_id = SUBSTR (itemkey, 1, INSTR (itemkey, '_') - 1);

      -- lets go ahead and set the wf attribute
      wf_engine.setitemattrnumber (itemtype, itemkey, 'APINV_AOI', l_org_id);
      --Now set the environment
      fnd_client_info.set_org_context (l_org_id);

      IF l_check_po_match = 'Y'
      THEN
         -- bug 2689116 Recoverable tax lines does not have po information
         -- populated on it. Added this check to the select statement.
         SELECT COUNT (invoice_distribution_id)
           INTO l_po_count
           FROM ap_invoice_distributions
          WHERE po_distribution_id IS NOT NULL
            AND invoice_id = SUBSTR (itemkey, 1, INSTR (itemkey, '_') - 1);

         IF NVL (l_po_count, 0) > 0
         THEN
            resultout := wf_engine.eng_completed || ':' || 'Y';

                         --update invoice status
            -- Bug 5037108 starts
            BEGIN
               SELECT     invoice_id
                     INTO l_temp_invoice_id
                     FROM ap_invoices
                    WHERE invoice_id =
                                  SUBSTR (itemkey, 1, INSTR (itemkey, '_') - 1)
                      AND wfapproval_status <> 'MANUALLY APPROVED'
               FOR UPDATE NOWAIT;
            EXCEPTION
               WHEN OTHERS
               THEN
                  l_debug_info := 'Invoice is in lock. Please try later';
                  fnd_message.set_name ('SQLAP',
                                        'AP_CARD_VERIFY_LOCK_FAILED');
                  l_error_message := fnd_message.get;
                  app_exception.raise_exception;
            END;

            -- Bug 5037108 Ends
            UPDATE ap_invoices
               SET wfapproval_status = 'NOT REQUIRED'
             WHERE invoice_id = SUBSTR (itemkey, 1, INSTR (itemkey, '_') - 1)
               AND wfapproval_status <> 'MANUALLY APPROVED';
         ELSE
            resultout := wf_engine.eng_completed || ':' || 'N';
         END IF;
      ELSE
         resultout := wf_engine.eng_completed || ':' || 'N';
      END IF;

      wf_engine.setitemattrtext (itemtype, itemkey, 'APINV_ADB', l_debug);
   EXCEPTION
      WHEN fnd_api.g_exc_error
      THEN
         wf_core.CONTEXT ('APINV',
                          'SELECT_APPROVER',
                          itemtype,
                          itemkey,
                          TO_CHAR (actid),
                          funcmode
                         );
         RAISE;
      WHEN fnd_api.g_exc_unexpected_error
      THEN
         wf_core.CONTEXT ('APINV',
                          'SELECT_APPROVER',
                          itemtype,
                          itemkey,
                          TO_CHAR (actid),
                          funcmode
                         );
         RAISE;
      WHEN OTHERS
      THEN
         wf_core.CONTEXT ('APINV',
                          'SELECT_APPROVER',
                          itemtype,
                          itemkey,
                          TO_CHAR (actid),
                          funcmode
                         );
         RAISE;
   END iaw_po_check;