Bank API in Oracle apps

===================================================================
CE_BANK_PUB.CREATE_BANK API to Create an Internal Bank in R12===================================================================
DECLARE
   p_init_msg_list             VARCHAR2 (200);
   p_country_code              VARCHAR2 (200);
   p_bank_name                 VARCHAR2 (200);
   p_bank_number               VARCHAR2 (200);
   p_alternate_bank_name       VARCHAR2 (200);
   p_short_bank_name           VARCHAR2 (200);
   p_description               VARCHAR2 (200);
   p_tax_payer_id              VARCHAR2 (200);
   p_tax_registration_number   VARCHAR2 (200);
   x_bank_id                   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_country_code        := 'US';
   p_bank_name           := 'Test Bank';
   p_bank_number         := '14258';
   p_alternate_bank_name := 'Alternate Test Bank';
   p_short_bank_name     := 'IDFC';
   p_description         := 'Test Bank Creation API';
   CE_BANK_PUB.CREATE_BANK
              (p_init_msg_list            => p_init_msg_list,
               p_country_code             => p_country_code,
               p_bank_name                => p_bank_name,
               p_bank_number              => p_bank_number,
               p_alternate_bank_name      => p_alternate_bank_name,
               p_short_bank_name          => p_short_bank_name,
               p_description              => p_description,
               x_bank_id                  => x_bank_id,
               x_return_status            => x_return_status,
               x_msg_count                => x_msg_count,
               x_msg_data                 => x_msg_data
              );

   DBMS_OUTPUT.put_line ('BANK_ID/PARTY_ID = ' || x_bank_id);
   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;
===================================================================
API to Update an Internal Bank in R12 (CE_BANK_PUB.UPDATE_BANK)====================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_bank_id                 NUMBER;
   p_bank_name               VARCHAR2 (200);
   p_bank_number             VARCHAR2 (200);
   p_alternate_bank_name     VARCHAR2 (200);
   p_short_bank_name         VARCHAR2 (200);
   p_description             VARCHAR2 (200);
   p_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_bank_id               := 1147;
   p_bank_name             := 'test bank2';
   p_bank_number           := 3933;
   p_alternate_bank_name   := 'Alternate Test Bank2';
   p_short_bank_name       := 'IDF';                    
   p_description           := 'Test Bank2 Description';
   p_object_version_number := 1;
   CE_BANK_PUB.UPDATE_BANK
           (p_init_msg_list              => p_init_msg_list,
            p_bank_id                    => p_bank_id,
            p_bank_name                  => p_bank_name,
            p_bank_number                => p_bank_number,
            p_alternate_bank_name        => p_alternate_bank_name,
            p_short_bank_name            => p_short_bank_name,
            p_description                => p_description,
            p_object_version_number      => p_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_OBJECT_VERSION_NUMBER = '
                         || p_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;
================================================================================
API to Check Existence of an Internal Bank R12 (CE_BANK_PUB.CHECK_BANK_EXIST)================================================================================
DECLARE
   p_country_code   VARCHAR2 (200);
   p_bank_name      VARCHAR2 (200);
   p_bank_number    VARCHAR2 (200);
   x_bank_id        NUMBER;
   x_end_date       DATE;
BEGIN
   p_country_code := 'US';
   p_bank_name    := 'Test Bank';
   p_bank_number  := 14258;
   ce_bank_pub.check_bank_exist (p_country_code      => p_country_code,
                                 p_bank_name         => p_bank_name,
                                 p_bank_number       => p_bank_number,
                                 x_bank_id           => x_bank_id,
                                 x_end_date          => x_end_date
                                );
   DBMS_OUTPUT.put_line (   'BANK_ID/PARTY_ID = '
                         || NVL (TO_CHAR (x_bank_id), 'NOT EXISTS')
                        );
   DBMS_OUTPUT.put_line (   'END_DATE = '
                         || NVL (TO_CHAR (x_end_date), 'NOT EXISTS')
                        );
END;
================================================================================
API to End Date an Internal Bank in R12 - CE_BANK_PUB.SET_BANK_END_DATE================================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_bank_id                 NUMBER;
   p_end_date                DATE;
   p_object_version_number   NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
BEGIN
   p_init_msg_list         := fnd_api.g_true;
   p_bank_id               := 119147;
   p_end_date              := SYSDATE + 360;
   p_object_version_number := 1;
   CE_BANK_PUB.SET_BANK_END_DATE
             (p_init_msg_list              => p_init_msg_list,
              p_bank_id                    => p_bank_id,
              p_end_date                   => p_end_date,
              p_object_version_number      => p_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_OBJECT_VERSION_NUMBER = '
                         || p_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);
END;
======================
BANK BRANCH APIS======================
================================================================================
API to Create an Internal Bank Branch R12 (CE_BANK_PUB.CREATE_BANK_BRANCH)================================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_bank_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_rfc_identifier          VARCHAR2 (200);
   x_branch_id               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_bank_id       := 219143; -- PARTY_ID of the BANK
   p_branch_name   := 'Test Branch';
   p_alternate_branch_name := 'Alternate Branch Name';
   p_description   := 'TEST Branch description';
 
   -- branch number/Routing Transit Number is subjected to
   -- the check digit validation
   p_branch_number := 111000025;
 
   -- Validated against AR_LOOKUPS with lookup_type = BANK_BRANCH_TYPE
   p_branch_type   := 'ABA';
 
   -- Validated against CE_LOOKUPS with lookup_type = RFC_IDENTIFIER
   p_rfc_identifier := 'AFC';

 
   CE_BANK_PUB.CREATE_BANK_BRANCH
            (p_init_msg_list              => p_init_msg_list,
             p_bank_id                    => p_bank_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_rfc_identifier             => p_rfc_identifier,
             x_branch_id                  => x_branch_id,
             x_return_status              => x_return_status,
             x_msg_count                  => x_msg_count,
             x_msg_data                   => x_msg_data
            );
   DBMS_OUTPUT.put_line ('BRANCH_ID/PARTY_ID = ' || x_branch_id);
   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;

================================================================================
API to Update an Internal Bank Branch R12 (CE_BANK_PUB.UPDATE_BANK_BRANCH)================================================================================

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;

================================================================================
CE_BANK_PUB.CHECK_BRANCH_EXISTS - API to check existence of an Internal Bank Branch in R12================================================================================

DECLARE
   p_bank_id         NUMBER;
   p_branch_name     VARCHAR2 (200);
   p_branch_number   VARCHAR2 (200);
   x_branch_id       NUMBER;
   x_end_date        DATE;
BEGIN
   p_bank_id        :=  1245;
   p_branch_name    := 'Test Branch';
   p_branch_number  := '1452789';
   CE_BANK_PUB.CHECK_BRANCH_EXIST
                  (p_bank_id            => p_bank_id,
                   p_branch_name        => p_branch_name,
                   p_branch_number      => p_branch_number,
                   x_branch_id          => x_branch_id,
                   x_end_date           => x_end_date
                  );
   DBMS_OUTPUT.put_line ('X_BRANCH_ID = ' || x_branch_id);
   DBMS_OUTPUT.put_line ('X_END_DATE = ' || x_end_date);
END;

=================================================================================
API to End Date an Internal Bank Branch in R12 - CE_BANK_PUB.SET_BANK_BRANCH_END_DATE=================================================================================

DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_branch_id               NUMBER;
   p_end_date                DATE;
   p_object_version_number   NUMBER;
   x_return_status           VARCHAR2 (200);
   x_msg_count               NUMBER;
   x_msg_data                VARCHAR2 (200);
BEGIN
   p_init_msg_list         := fnd_api.g_true;
   p_branch_id := 72141; -- CE_BANK_BRANCHES_V.BRANCH_PARTY_ID
   p_end_date              := SYSDATE + 360;
   p_object_version_number := 1;
   CE_BANK_PUB.SET_BANK_BRANCH_END_DATE
             (p_init_msg_list              => p_init_msg_list,
              p_branch_id                  => p_branch_id,
              p_end_date                   => p_end_date,
              p_object_version_number      => p_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_OBJECT_VERSION_NUMBER = '
                         || p_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);
END;
====================
BANK ACCOUNT APIS ====================
=================================================================================
API to Create an Internal Bank Account in R12 CE_BANK_PUB.CREATE_BANK_ACCT================================================================================
DECLARE
   p_init_msg_list   VARCHAR2 (200);
   p_acct_rec        apps.ce_bank_pub.bankacct_rec_type;
   x_acct_id         NUMBER;
   x_return_status   VARCHAR2 (200);
   x_msg_count       NUMBER;
   x_msg_data        VARCHAR2 (200);
   p_count           NUMBER;
 
BEGIN
   p_init_msg_list := NULL;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION 
   p_acct_rec.account_owner_party_id := 2041;
   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 14256789;
   p_acct_rec.currency := 'USD';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;
 
   CE_BANK_PUB.CREATE_BANK_ACCT
                  (p_init_msg_list      => p_init_msg_list,
                   p_acct_rec           => p_acct_rec,
                   x_acct_id            => x_acct_id,
                   x_return_status      => x_return_status,
                   x_msg_count          => x_msg_count,
                   x_msg_data           => x_msg_data
                  );
                        
   DBMS_OUTPUT.put_line ('X_ACCT_ID = ' || x_acct_id);
   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;

==========================================================================================
API to update an Internal Bank Account in Oracle Apps R12 (CE_BANK_PUB.UPDATE_BANK_ACCT)==========================================================================================
DECLARE
   p_init_msg_list           VARCHAR2 (200);
   p_acct_rec                apps.ce_bank_pub.bankacct_rec_type;
   p_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 := NULL;
   p_acct_rec.bank_account_id := 41001;
   -- HZ_PARTIES.PARTY_ID BANK BRANCH
   p_acct_rec.branch_id := 8056;
   -- HZ_PARTIES.PARTY_ID BANK
   p_acct_rec.bank_id := 8042;
   -- HZ_PARTIES.PARTY_ID ORGANIZATION
   p_acct_rec.account_owner_org_id := 23273;
   -- HZ_PARTIES.PARTY_ID Person related to ABOVE ORGANIZATION
   p_acct_rec.account_owner_party_id := 2041;
   p_acct_rec.account_classification := 'INTERNAL';
   p_acct_rec.bank_account_name := 'Test Bank Accunt';
   p_acct_rec.bank_account_num := 18256889;
   p_acct_rec.currency := 'USD';
   p_acct_rec.start_date := SYSDATE;
   p_acct_rec.end_date := NULL;
   p_object_version_number := 1;
   CE_BANK_PUB.UPDATE_BANK_ACCT
           (p_init_msg_list              => p_init_msg_list,
            p_acct_rec                   => p_acct_rec,
            p_object_version_number      => p_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_OBJECT_VERSION_NUMBER = '
                         || p_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;

Interface Table in Oracle Apps

GL INTERFACE TABLES

GL_BUDGET_INTERFACE 
GL_DAILY_RATES_INTERFACE 
GL_IEA_INTERFACE 
GL_INTERFACE 
GL_INTERFACE_CONTROL 
GL_INTERFACE_HISTORY 

AP INTERFACE TABLES 

AP_INTERFACE_CONTROLS
AP_INTERFACE_REJECTIONS
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE

AR INTERFACE TABLES

AR_PAYMENTS_INTERFACE_ALL
AR_TAX_INTERFACE
HZ_DQM_SYNC_INTERFACE
HZ_PARTY_INTERFACE
HZ_PARTY_INTERFACE_ERRORS
RA_CUSTOMERS_INTERFACE_ALL
RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_ERRORS_ALL
RA_INTERFACE_LINES_ALL
RA_INTERFACE_SALESCREDITS_ALL

FA INTERFACE TABLES 

FA_BUDGET_INTERFACE 
FA_INV_INTERFACE 
FA_PRODUCTION_INTERFACE 
FA_TAX_INTERFACE 

INVENTORY INTERFACE TABLES 

MTL_CC_ENTRIES_INTERFACE
MTL_CC_INTERFACE_ERRORS
MTL_CI_INTERFACE
MTL_CI_XREFS_INTERFACE
MTL_COPY_ORG_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_DEMAND_INTERFACE
MTL_DESC_ELEM_VAL_INTERFACE
MTL_EAM_ASSET_NUM_INTERFACE
MTL_EAM_ATTR_VAL_INTERFACE
MTL_INTERFACE_ERRORS
MTL_INTERFACE_PROC_CONTROLS
MTL_ITEM_CATEGORIES_INTERFACE
MTL_ITEM_CHILD_INFO_INTERFACE
MTL_ITEM_REVISIONS_INTERFACE
MTL_ITEM_SUB_INVS_INTERFACE
MTL_OBJECT_GENEALOGY_INTERFACE
MTL_RELATED_ITEMS_INTERFACE
MTL_RESERVATIONS_INTERFACE
MTL_RTG_ITEM_REVS_INTERFACE
MTL_SECONDARY_LOCS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE
MTL_SO_RMA_INTERFACE
MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_TXN_COST_DET_INTERFACE

PO INTERFACE TABLES

 PO_DISTRIBUTIONS_INTERFACE
PO_HEADERS_INTERFACE
PO_INTERFACE_ERRORS
PO_LINES_INTERFACE
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_RESCHEDULE_INTERFACE
RCV_HEADERS_INTERFACE
RCV_LOTS_INTERFACE
RCV_SERIALS_INTERFACE
RCV_TRANSACTIONS_INTERFACE


BOM INTERFACE TABLES 

BOM_BILL_OF_MTLS_INTERFACE
BOM_INTERFACE_DELETE_GROUPS
BOM_INVENTORY_COMPS_INTERFACE
BOM_OP_RESOURCES_INTERFACE
BOM_OP_ROUTINGS_INTERFACE
BOM_OP_SEQUENCES_INTERFACE
BOM_REF_DESGS_INTERFACE
BOM_SUB_COMPS_INTERFACE
CST_COMP_SNAP_INTERFACE
CST_INTERFACE_ERRORS
CST_ITEM_COSTS_INTERFACE
CST_ITEM_CST_DTLS_INTERFACE
CST_PC_COST_DET_INTERFACE
CST_PC_ITEM_COST_INTERFACE

WIP INTERFACE TABLES

WIP_COST_TXN_INTERFACE
WIP_INTERFACE_ERRORS
WIP_JOB_DTLS_INTERFACE
WIP_JOB_SCHEDULE_INTERFACE
WIP_MOVE_TXN_INTERFACE
WIP_SCHEDULING_INTERFACE
WIP_TXN_INTERFACE_ERRORS TABLE

ORDER MANAGEMENT INTERFACE TABLES

SO_CONFIGURATIONS_INTERFACE
SO_HEADERS_INTERFACE_ALL
SO_HEADER_ATTRIBUTES_INTERFACE
SO_LINES_INTERFACE_ALL
SO_LINE_ATTRIBUTES_INTERFACE
SO_LINE_DETAILS_INTERFACE
SO_PRICE_ADJUSTMENTS_INTERFACE
SO_SALES_CREDITS_INTERFACE
SO_SERVICE_DETAILS_INTERFACE
WSH_DELIVERIES_INTERFACE
WSH_FREIGHT_CHARGES_INTERFACE
WSH_PACKED_CONTAINER_INTERFACE 

Cash Management 
 
CE_STATEMENT_HEADERS_INT
CE_STATEMENT_LINES_INTERFACE

PL/SQL - FAQ 4

1.What is NOCOPY?

By default the IN parameter is passed by reference and the OUT and IN OUT parameters are passed by value.

NOCOPY:  is a compiles hint that can be used with OUT and IN OUT parameter to request to pass by reference. This improves the performance with OUT and INOUT parameters.

2.REPORT TYPES

A) Tabular Report                            B) Group Left Report

C) Group above Report                     D) Form like report

E) Matrix Report                              F) Multi Media Report

G) Mailing Label Report                     H) OLE Report.

3.ANCHOR

Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent object. The end of the anchor should be attached to the parent object.

A) Parent Object                              B) Child Object

Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object

An Anchor defines the relative position of an object to the object to which if this anchored.

* Symbol is By pressing the shift key Anchor can be moved.

Anchor Properties:

A) Child Edge percent on child edge type        B) Child Object Name

C) Collapse Horizontally                              D) Collapse vertically

E) Comments                                           F) Name

* Two child objects can be related to one parent object by an Anchor.

4.User Exits :

A user exit is: - Program that can be written and linked into the report builder executable or user exit DLL files.

User exits are building when ever the control needs to be passed from the report builder to a program, which performs some function and then control returns to the Report Builder.

Types of user exits:-

·         Oracle Pre-Complier user exits.

·         OCI (Oracle Call Interface user exits)

·         Non – Oracle user exits.

User exits can perform the following tasks.

·         Perform complex data manipulation.

·         Pass data to report builder from OS text files.

·         Support PL/SQL blocks.

·         Control real time devices are printer or robot.

5.Types of Triggers in Reports :-

* Report Triggers                   * Data Triggers                  * Layout Triggers

v  Report Triggers:-

·         After Parameter form Trigger.

·         After Report Trigger

·         Before parameter form Trigger

·         Before Report Trigger.

·         Between Pages Trigger

+ Firing Sequence

Ø  Before Parameter form Trigger

Ø  A fire before the runtime parameter form is displayed.

Ø  The parameter values can be accessed and changed.

Ø             After Parameter form Trigger.

Ø  Fires after the runtime parameter form is displayed.

Ø  The parameters can be accessed and their values can be checked.

Ø  Before Report Trigger

Ø  Fires before the reports is executed but after the queries are passed and data is fetched.

Ø  Between Pages Trigger.

Ø  Fires between each page of the report are formatted, except the very first pages.

Ø  This is used for customized page formatting.

Ø  After Report Trigger

Ø  Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)

Ø  This is used to clean up any initial processing that was done such as deleting the tables.

Ø  This Trigger always fires irrespective of success or failure of the report.

v  DATA TRIGGERS:

Ø  Ref Cursor Query.

Ø  This uses PL/SQL to fetch data for the report.

Ø  In this a PL/SQL function need to be specified to return a cursor value from a cursor variable.

Ø  Group Filter:

Ø  This is PL/SQL function that determines which records to be included in a group in the property is PL/SQL.

Ø  The function must return a BOOLEAN value.

True …….. Includes the current record in the report.

       False ……. Excludes the current record from the report.

Ø       Formula

Ø  These are Pl/SQL functions that populate formula or place holder columns

Ø  Validation Trigger 

Ø  These are also PL/SQL functions that are executed when parameter values are specified on the command line and when the runtime parameter form is accepted.

Ø  Are also used to validate the initial value property of the parameter.

v  Layout Triggers

Ø  Format Trigger.

Ø  These are PL/SQL functions executed before the object is formatted.

Ø  Used to dynamically change the formatting attributes of the object.

Ø  Action Trigger

Ø  These are Pl/SQL procedures executed when a button is selected in the run time previener.

Ø  This can be used to dynamically call another report or execute any other PL/SQL.

6.Formula Column

It performs a user-defined computation on another columns data, including Place-holder columns.

Formulas are PL/SQL functions that populate formula or place holder columns.

Cannot be used to populate parameter values.

7.Summary Column

·         Performs a computation on another columns data like sum, average, count, minimum, maximum, %, total.

Ø  For group reports, the report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is defined.

---à One at each group level above the column being summarized.

---à One at the report level.

8.Place Holder Column 

·         A Place holder column is a column for which, the data type and value can be set dynamically (Programmatically)

·         The value can be set for a place holder column in the following places.

·         Before report trigger if the place holder is a report level column.

·         Report level formula column, if the place holder is a report level column.

·         A formula in the place holders group below it (The value is set once for each record of the group)

9.Repeating Frame 

·         Repeating frame surrounds all of the fields that are created for a groups columns.

·         Repeating frame prints once for each record of the group.

·         For frames and repeating frames, the property elasticity defines whether the size of the frame or repeating frame should with the objects inside of it at runtime.

10. Frame

Surrounds the objects and protect them from being over written or pushed by other objects.

11. System Parameters in Reports

* Background                                * Copies                           * Currency

* Decimal                                     * Desformat                      * Desname

* Destype                                     * Mode                             * Orientation

* Print Job                                    * Thousands.

12. Data Link

·         Data links relate the results of multiple queries.

·         A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its parent group.

13. In which tables FF are stored?

A)               FND – ID – FLEXS

B)               FND-ID-FLEX-STRUCTURES

14. Advantages of stored functions and procedures

Ø  Applications can be modularized.

Ø           Easy maintenance.

·         Rowtines can be modified online without interfering other users.

·         One routine can be modified to effect multiple applications.

Ø  Improved data security and integrity.

·         Indirect access to database objects can be controlled from non privileged users with security privileges.

Ø  Improved performance.

·         Reparsing for multiple users can be avoided by exploiting the shared SQL area.

·         PL/SQL parsing at run-time can be avoided by pursing at compile time.

·         Number of calls to the database can be reduced and network traffic decreased by bundling commands.

Ø  Improved code clarity.

·         The clarity of code increases by using appropriate identifier names to describe the action of the routines which reduces the need for comments.

15. Difference between a function and a procedure

            Functions                                                       Procedures

ü  Invoke as a part of an expression.       Execute as a PL/SQL statement.

ü  Must contain a RETURN clause             Do not contain a RETURN Clause.

in the header                                        in the header

ü  Must return a single value.                   Can return none, one or many values.

ü  Must contain at fast one RETURN          Can contain a RETURN Statement.      Statement.

ü  Do not contain OUT and INOUT             Can contain IN, Out and IN OUT

IN OUT parameters.                                        Parameters.

16. About Cursors

Ø  Oracle server uses some private work areas to execute SQL statements and to store processing information.

Ø  By using PL/SQL cursors these private SQL areas can be named and the stored information can be accessed.

Two Types:

v  Implicit Cursors

·         Implicit cursors are declared by PL/SQL implicitly for all DML and PL/SQL select statements, including queries that return only one row.

·         Oracle Server implicitly opens a cursor to process each SQL statement not associated with on explicitly declared cursor.

·         The most recent implicit cursor can be returned as the SQL cursor.

v  Explicit Cursors

·         For queries that return more than one row, explicit cursors are declared and named by the programmes and manipulated through specific statements in the block’s executable actions.

·         Explicit cursors are used to individually process each row returned by a multiple-row SELECT statement.

·         The set of rows returned by a multiple – row query is called as active set.

Declare       Open        Fetch            Empty?         Close

 

Cursor Attributes:-

Attribute             Type                                    Description

% is open           Boolean      Evaluates to TRUE if the cursor is open.

% not found        Boolean      Evaluates to TRUE if the most recent fetch

                                                  doesn’t return a row.

% found                      Boolean      Evaluate to TRUE if the most recent fetch

returns a row. Complement of % not found.

% Row Count     Number      Evaluates the total number of rows returned so far.

PARAMETERIZED CURSORS:-

·         Parameters can be passed to the cursor in a cursor for loop.

·         It allow to open and close an explicit cursor several times in a block, returning a different active set on each occasion for each execution, the previous cursor is closed and reopened with a new set of parameters.

·         Sizes should not be mentioned for the data types of parameters the parameters names are for references in the query expression of the cursor.

17. Confined Mode:-

·         If it is on, child objects cannot be moved outside their enclosing parent objects.

·         If it is off child objects can be moved out sides their enclosing parent objects.

FLEX MODE:-

·         If it is on, parent borders stretch when child objects are moved against them.

·         If it is off, parent borders remain fixed when child objects are moved against them.

18. Parameters

·         A parameter is a variable whose value can be set at runtime (from the run time parameter      of the command line).

·         User parameters are created by the user and system parameters are created by Report Builder.

·         System parameters cannot be renamed or deleted.

Bind Parameters (Variables)

·         Bind references (or Variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number or date.

·         Bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and  START WITH clauses of queries.

·         Bind references cannot be referenced in FROM clauses.

·         Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.

·         If the parameter / column is not created before making a bind reference, report builder will create a parameter.

Lexical Parameters (Variables)

·         Lexical references are place holders for text that is embedded in a SELECT statement.

·         Lexical Variables can replace the clauses appearing after SLECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and  START WITH.

19. What is % Row type

·         % Row types is used to declare a record based on a collection of columns in a database table or view.

·         The fields in the record take their names and data types from the columns of the table or view.

·         The record can also store an entire row of data fetched from a cursor or cursor variable.

·         % Row type should be prefixed with the database table.

Ex: Declare

·         Emp_record   employee 5%  row type.

·         Then emp_record will have a structure consisting of all the fields each representing a column in the employees table.

20. What is a Ref Cursor?

·         Oracle server uses unnamed memory spaces to store data used in implicit cursors.

·         Ref cursors are used to define a cursor variable, which will point to that memory space and can be used like pointers in SQL ‘S’.