Showing posts with label Cash Management. Show all posts
Showing posts with label Cash Management. Show all posts

R12 Query For Invoices Reconciliation and Currencies Data is stored in Oracle AP Tables

Here we find the tables involved in storing the Reconciliation and Currency Data related to the Payable INVOICE (Invoice_id = 1234567).

Reconciliation Tables:
    CE_STATEMENT_RECONCILS_ALL
    CE_STATEMENT_HEADERS
    CE_STATEMENT_LINES

SELECT DISTINCT CSL.*
  FROM CE_STATEMENT_HEADERS CSH,
       CE_STATEMENT_LINES CSL,
       CE_STATEMENT_RECONCILS_ALL CSR,
       AP_CHECKS_ALL ac,
       AP_INVOICE_PAYMENTS_ALL aip
 WHERE     CSL.statement_header_id = CSH.statement_header_id
       AND aip.check_id = ac.check_id
       AND ac.bank_account_id = CSH.bank_account_id
       AND aip.invoice_id = '1234567'
       AND TO_CHAR (ac.check_number) = CSL.bank_trx_number
       AND ac.check_id = aip.check_id
       AND csr.statement_line_id = CSL.statement_line_id;


Currencies Tables:
      FND_CURRENCIES
      AP_SYSTEM_PARAMETERS_ALL
      AP_INVOICES_ALL
    
SELECT *
  FROM FND_CURRENCIES
 WHERE currency_code IN (SELECT a.invoice_currency_code
                           FROM AP_INVOICES_ALL a
                          WHERE a.invoice_id = '1234567'
                         UNION
                         SELECT b.payment_currency_code
                           FROM AP_INVOICES_ALL b
                          WHERE b.invoice_id = '1234567'
                         UNION
                         SELECT c.base_currency_code
                           FROM AP_SYSTEM_PARAMETERS_ALL c

                          WHERE NVL (c.org_id, -99) = '982');

API to Update an Internal Bank Branch R12

Script for to update details of an Internal Bank Branch in Oracle Apps R12.1.1.


API USED: CE_BANK_PUB.UPDATE_BANK_BRANCH

SET SERVEROUTPUT ON;

DECLARE
   p_init_msg_list               VARCHAR2 (200);
   p_branch_id                   NUMBER;
   p_branch_name                 VARCHAR2 (200);
   p_branch_number               VARCHAR2 (200);
   p_branch_type                 VARCHAR2 (200);
   p_alternate_branch_name       VARCHAR2 (200);
   p_description                 VARCHAR2 (200);  
   p_country_validate            VARCHAR2 (200);
   p_bch_object_version_number   NUMBER;
   p_typ_object_version_number   NUMBER;
   p_rfc_object_version_number   NUMBER;
   p_eft_object_version_number   NUMBER;
   x_return_status               VARCHAR2 (200);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (200);
   p_count                       NUMBER;
BEGIN
   p_init_msg_list := fnd_api.g_true;
   p_branch_id := 219154;
   p_branch_number := 111000025;
   p_branch_type := 'ABA';
   p_branch_name := 'Test Branch';
   p_alternate_branch_name := 'updated Alternate Branch Name';
   p_description := 'TEST Branch description';
   p_country_validate := NULL;
   p_bch_object_version_number := 1;
   CE_BANK_PUB.UPDATE_BANK_BRANCH
        (p_init_msg_list                  => p_init_msg_list,
         p_branch_id                      => p_branch_id,
         p_branch_name                    => p_branch_name,
         p_branch_number                  => p_branch_number,
         p_branch_type                    => p_branch_type,
         p_alternate_branch_name          => p_alternate_branch_name,
         p_description                    => p_description,
         p_country_validate               => p_country_validate,
         p_bch_object_version_number      => p_bch_object_version_number,
         p_typ_object_version_number      => p_typ_object_version_number,
         p_rfc_object_version_number      => p_rfc_object_version_number,
         p_eft_object_version_number      => p_eft_object_version_number,
         x_return_status                  => x_return_status,
         x_msg_count                      => x_msg_count,
         x_msg_data                       => x_msg_data
        );
   DBMS_OUTPUT.put_line (   'P_BCH_OBJECT_VERSION_NUMBER = '
                         || p_bch_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_TYP_OBJECT_VERSION_NUMBER = '
                         || p_typ_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_RFC_OBJECT_VERSION_NUMBER = '
                         || p_rfc_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_EFT_OBJECT_VERSION_NUMBER = '
                         || p_eft_object_version_number
                        );
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

   IF x_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
   ELSIF x_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next,fnd_api.g_false);

         IF x_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
      END LOOP;
   END IF;
END;

Update an Internal Bank Branch R12

Script for to update details of an Internal Bank Branch in Oracle Apps R12.1.1
API USED: CE_BANK_PUB.UPDATE_BANK_BRANCH

SET SERVEROUTPUT ON;

DECLARE
   p_init_msg_list               VARCHAR2 (200);
   p_branch_id                   NUMBER;
   p_branch_name                 VARCHAR2 (200);
   p_branch_number               VARCHAR2 (200);
   p_branch_type                 VARCHAR2 (200);
   p_alternate_branch_name       VARCHAR2 (200);
   p_description                 VARCHAR2 (200);
   p_country_validate            VARCHAR2 (200);
   p_bch_object_version_number   NUMBER;
   p_typ_object_version_number   NUMBER;
   p_rfc_object_version_number   NUMBER;
   p_eft_object_version_number   NUMBER;
   x_return_status               VARCHAR2 (200);
   x_msg_count                   NUMBER;
   x_msg_data                    VARCHAR2 (200);
   p_count                       NUMBER;
BEGIN
   p_init_msg_list := fnd_api.g_true;
   p_branch_id := 219154;
   p_branch_number := 111000025;
   p_branch_type := 'ABA';
   p_branch_name := 'Test Branch';
   p_alternate_branch_name := 'updated Alternate Branch Name';
   p_description := 'TEST Branch description';
   p_country_validate := NULL;
   p_bch_object_version_number := 1;
   ce_bank_pub.update_bank_branch
                 (p_init_msg_list                  => p_init_msg_list,
                  p_branch_id                      => p_branch_id,
                  p_branch_name                    => p_branch_name,
                  p_branch_number                  => p_branch_number,
                  p_branch_type                    => p_branch_type,
                  p_alternate_branch_name          => p_alternate_branch_name,
                  p_description                    => p_description,
                  p_country_validate               => p_country_validate,
                  p_bch_object_version_number      => p_bch_object_version_number,
                  p_typ_object_version_number      => p_typ_object_version_number,
                  p_rfc_object_version_number      => p_rfc_object_version_number,
                  p_eft_object_version_number      => p_eft_object_version_number,
                  x_return_status                  => x_return_status,
                  x_msg_count                      => x_msg_count,
                  x_msg_data                       => x_msg_data
                 );
   DBMS_OUTPUT.put_line (   'P_BCH_OBJECT_VERSION_NUMBER = '
                         || p_bch_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_TYP_OBJECT_VERSION_NUMBER = '
                         || p_typ_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_RFC_OBJECT_VERSION_NUMBER = '
                         || p_rfc_object_version_number
                        );
   DBMS_OUTPUT.put_line (   'P_EFT_OBJECT_VERSION_NUMBER = '
                         || p_eft_object_version_number
                        );
   DBMS_OUTPUT.put_line ('X_RETURN_STATUS = ' || x_return_status);
   DBMS_OUTPUT.put_line ('X_MSG_COUNT = ' || x_msg_count);
   DBMS_OUTPUT.put_line ('X_MSG_DATA = ' || x_msg_data);

   IF x_msg_count = 1
   THEN
      DBMS_OUTPUT.put_line ('x_msg_data ' || x_msg_data);
   ELSIF x_msg_count > 1
   THEN
      LOOP
         p_count := p_count + 1;
         x_msg_data := fnd_msg_pub.get (fnd_msg_pub.g_next, fnd_api.g_false);

         IF x_msg_data IS NULL
         THEN
            EXIT;
         END IF;

         DBMS_OUTPUT.put_line ('Message' || p_count || ' ---' || x_msg_data);
      END LOOP;
   END IF;

END;
/





How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...