Available Payments For Reconciliation Function

FUNCTION XX_GET_AVAIL_PAYMENT_FUNC(P_LEDGER       IN NUMBER,

                                   P_BANK_ACCT_ID IN NUMBER,

                                   P_AS_OF_DATE   IN DATE) RETURN NUMBER IS

  X_AMOUNT NUMBER;

BEGIN

  SELECT ABS(SUM(AMOUNT))

    INTO X_AMOUNT

    FROM (SELECT (CASE

                   WHEN C.BANK_CURRENCY_CODE <> C.CURRENCY_CODE THEN

                    (C.AMOUNT * NVL(C.EXCHANGE_RATE, 1))

                   ELSE

                    C.AMOUNT

                 END) AMOUNT C.ORG_ID,

                 C.CURRENT_BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

                 C.CHECK_DATE EFFECTIVE_DATE,

                 L.LEDGER_ID,

                 C.BANK_ACCOUNT_ID

            from AP_CHECKS_V C, XX_LOGO_TL L -- Its Custom Table only for Convert Org to Ledger

           where c.check_status = 'Negotiable'

             AND L.ORG_ID = C.ORG_ID

             AND C.CHECK_DATE <= P_AS_OF_DATE

             AND C.BANK_ACCOUNT_ID = P_BANK_ACCT_ID

             AND L.LEDGER_ID = P_LEDGER

         

          UNION ALL

         

          SELECT (CASE

                   WHEN C.BANK_CURRENCY_CODE <> C.CURRENCY_CODE THEN

                    (C.AMOUNT * NVL(C.EXCHANGE_RATE, 1))

                   ELSE

                    C.AMOUNT

                 END) AMOUNT C.ORG_ID,

                 C.CURRENT_BANK_ACCOUNT_NAME BANK_ACCOUNT_NAME,

                 C.CHECK_DATE EFFECTIVE_DATE,

                 L.LEDGER_ID,

                 C.BANK_ACCOUNT_ID

            FROM AP_CHECKS_VIEW C, XX_LOGO_TL L  -- Its Custom Table only for Convert Org to Ledger

           WHERE C.CHECK_STATUS = 'Voided'

             AND (C.VOID_DATE > P_AS_OF_DATE AND

                 C.CHECK_DATE <= P_AS_OF_DATE)

             AND C.BANK_ACCOUNT_ID = P_BANK_ACCT_ID

             AND L.ORG_ID = C.ORG_ID

             AND L.LEDGER_ID = P_LEDGER

         

         );

  RETURN X_AMOUNT;

EXCEPTION

  WHEN NO_DATA_FOUND THEN

    RETURN 0;

  WHEN OTHERS THEN

    RETURN 0;

END XX_GET_AVAIL_PAYMENT_FUNC;

Procedure to check AP Invoice and Payments

DECLARE

  prog_short_name    VARCHAR2(240);

  appl_short_name    VARCHAR2(240);

BEGIN

   prog_short_name := 'XXCUSTDET';    --Concurrent Program Short name

   appl_short_name := 'PO';              --Application Short name

   --Checking for program and executable is exist or not

   IF fnd_program.program_exists    (prog_short_name, appl_short_name) AND

     fnd_program.executable_exists (prog_short_name, appl_short_name)   

   THEN

   --deleting the program

      fnd_program.delete_program(prog_short_name, appl_short_name);

   --deleting the executable

      fnd_program.delete_executable(prog_short_name, appl_short_name);

      COMMIT;

DBMS_OUTPUT.PUT_LINE (prog_short_name || ' deleted successfully');

   ELSE

      DBMS_OUTPUT.PUT_LINE (prog_short_name || ' not found');

   END IF;

EXCEPTION

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE ('Error: ' || SQLERRM);

END;