Tables used for Bank Statement Load, Import in Cash Management R12 with Explanation

Tables used for Bank Statement Load, Import in Cash Management  R12 with Explanation

CE_STMT_INT_TMP
The CE_STMT_INT_TMP table stores bank statement information loaded from bank flat file by SQL*Loader. This table is populated and deleted whenever the loading program is executed.

CE_STATEMENT_HEADERS_INT
The CE_STATEMENT_HEADERS_INT_ALL table stores information about bank statement details for importing. Each row contains the bank statement number, bank account number, control balances, and other statement- related information. After populating this table, you can run the Bank Statement Import program to transfer the statement information into the CE_STATEMENT_HEADERS_ALL table. The Bank Statement Interface form allows you to modify the statement interface information or to correct any errors encountered while uploading the data.

CE_STATEMENT_LINES_INTERFACE
The CE_STATEMENT_LINES_INTERFACE table stores information about bank statement line details for open interface. Each row contains the bank statement number, bank account number, statement line amount, and others. After populating this table, you can run the Bank Statement Import program to transfer the statement line information into the CE_STATEMENT_LINES table. The Bank Statement Interface form allows you to modify the statement line interface information or correct any errors encountered while uploading the data.

CE_STATEMENT_HEADERS
The CE_STATEMENT_HEADERS_ALL table stores bank statements. Each row in this table contains the statement name, statement date, GL date, bank account identifier, and other information about the statement. This table corresponds to the Bank Statement window of the Bank Statements form.
Once you have marked your statement as complete, the STATEMENT_COMPLETE_FLAG is set to Y, and you can no longer modify or update the statement.
AUTO_LOADED_FLAG is set to Y when your statement is uploaded from the interface table using the Bank Statement Import program

CE_STATEMENT_LINES
The CE_STATEMENT_LINES table stores information about bank statement lines. Each row in this table stores the statement header identifier, statement line number, associated transaction type, and transaction amount associated with the statement line.
This table corresponds to the Bank Statement Lines window of the Bank Statements form.

Bank Statement Import:

On successful completion, this program moves records from

(1) CE_STATEMENT_HEADERS_INT
(2) CE_STATEMENT_LINES_INTERFACE

TO

(3) CE_STATEMENT_HEADERS
(4) CE_STATEMENT_LINES tables.

Find requet_group of specific request or C.P

    SELECT

    --######################################################

    ---Find requet_group of specific request or C.P

    --######################################################

       RG.APPLICATION_ID "Request Group Application ID",

       RG.REQUEST_GROUP_ID "Request Group - Group ID",

       RG.REQUEST_GROUP_NAME,

       RG.DESCRIPTION,

       rgu.unit_application_id,

       rgu.request_group_id "Request Group Unit - Group ID",

       rgu.request_unit_id,

       cp.concurrent_program_id,

       cp.concurrent_program_name,

       cpt.user_concurrent_program_name,

       DECODE (rgu.request_unit_type,

               'P', 'Program',

               'S', 'Set',

               rgu.request_unit_type)

          "Unit Type"

  FROM fnd_request_groups rg,

       fnd_request_group_units rgu,

       fnd_concurrent_programs cp,

       FND_CONCURRENT_PROGRAMS_TL CPT

 WHERE     rg.request_group_id = rgu.request_group_id

       AND rgu.request_unit_id = cp.concurrent_program_id

       AND cp.concurrent_program_id = cpt.concurrent_program_id

       AND cpt.user_concurrent_program_name = 'Your_Concurrent_Program_Name';

VIEW for ap_invoices_interface mandatory and requied columns as APPS.AP_INVOICES_INTERFACE_V

CREATE OR REPLACE FORCE VIEW APPS.AP_INVOICES_INTERFACE_V

--##############################################################################--

--#                                                                            #--

--#   View Name : APPS.AP_INVOICES_INTERFACE_V                                 #--

--#                                                                            #--

--#  Description    :                                                          #--

--#   OFFICE MANDATORY AND REQUIED AP INVOICE INTERFACE COLUMNS                #--

--#  Initial Verion       Created By                  Last_Update_Date         #--

--#  --------------       ----------                  ----------------         #--

--#      1.0              Santosh                      05-JAN-2016             #--

--#                                                                            #--

--##############################################################################--

AS

   SELECT org_id,

          request_id,

          status,

          invoice_type_lookup_code,

          source,

          invoice_id,

          invoice_num,

          invoice_date,

          vendor_id,

          vendor_site_id,

          invoice_currency_code,

          invoice_amount,

          description,

          exchange_rate,

          terms_name,

          attribute_category,

          attribute10 dff_type_of_payment,

          --attribute1,

          attribute2 dff_cpr_number,

          attribute3 dff_manual_payment,

          attribute4 dff_service_name,

          attribute5 dff_number_of_instalments,

          attribute6 dff_reason_for_refund,

          attribute7 dff_اسم_الوكيل,

          attribute8 dff_pensioner_cpr,

          attribute9 dff_jv_reference,

          creation_date,

          created_by,

          last_update_date,

          last_updated_by,

          last_update_login,

          gl_date,

          po_number,

          vendor_num,

          vendor_name,

          vendor_site_code,

          exchange_rate_type,

          terms_id,

          doc_category_code,

          voucher_num,

          payment_method_lookup_code,

          pay_group_lookup_code,

          GROUP_ID

     /*###########################################

     -----Generic view for ap_invoice_interface

     SELECT org_id,

            status,

            invoice_type_lookup_code,

            source,

            request_id,

            invoice_id,

            invoice_num,

            invoice_date,

            gl_date,

            po_number,

            vendor_id,

            vendor_site_id,

            vendor_num,

            vendor_name,

            vendor_site_code,

            invoice_currency_code,

            invoice_amount,

            description,

            exchange_rate,

            exchange_rate_type,

            terms_id,

            terms_name,

            creation_date,

            created_by,

            last_update_date,

            last_updated_by,

            last_update_login,

            attribute_category,

            attribute1,

            attribute2,

            attribute3,

            attribute4,

            attribute5,

            attribute6,

            attribute7,

            attribute8,

            attribute9,

            attribute10,

            doc_category_code,

            voucher_num,

            payment_method_lookup_code,

            pay_group_lookup_code,

            GROUP_ID

       FROM apps.ap_invoices_interface

     ###########################################*/

     FROM apps.ap_invoices_interface;

VIEW for ap_invoice_lines_interface Mandatory and Requied columns as APPS.AP_INVOICE_LINES_INTERFACE_V

CREATE OR REPLACE FORCE VIEW APPS.AP_INVOICE_LINES_INTERFACE_V

--##############################################################################--

--#                                                                            #--

--#   View Name : APPS.AP_INVOICE_LINES_INTERFACE_V                            #--

--#                                                                            #--

--#  Description    :                                                         #--

--#   OFFICE MANDATORY AND REQUIED AP INVOICE LINES INTERFACE COLUMNS         #--

--#  Initial Verion       Created By                  Last_Update_Date         #--

--#  --------------       ----------                  ----------------         #--

--#      1.0              Santosh                      05-JAN-2016             #--

--#                                                                            #--

--##############################################################################--

AS

   SELECT org_id,

          invoice_id,

          invoice_line_id,

          line_number,

          line_type_lookup_code,

          amount,

          description,

          dist_code_combination_id,

          accounting_date,

          distribution_set_id,

          distribution_set_name,

          dist_code_concatenated,

          attribute_category,

          attribute1,

          attribute2,

          attribute3,

          attribute4,

          attribute5,

          assets_tracking_flag,

          po_header_id,

          po_number,

          po_line_id,

          po_line_number,

          po_line_location_id,

          po_shipment_num,

          po_distribution_id,

          po_distribution_num,

          po_unit_of_measure,

          inventory_item_id,

          item_description,

          quantity_invoiced,

          ship_to_location_code,

          unit_price,

          match_option,

          final_match_flag,

          created_by,

          creation_date,

          last_updated_by,

          last_update_date,

          last_update_login,

          line_group_number

     FROM ap_invoice_lines_interface;

CREATE OR REPLACE FORCE VIEW APPS.XXAP_INVLINE_INTERFACE_V

AS

   SELECT aii.org_id,

--##############################################################################--

--#                                                                            #--

--#   View Name : XXAP_INVLINE_INTERFACE_V                                     #--

--#                                                                            #--

--#  Description    :                                                         #--

--#   OFFICE MANDATORY AND REQUIED AP INVOICE AND LINES INTERFACE COLUMNS      #--

--#  Initial Verion       Created By                  Last_Update_Date         #--

--#  --------------       ----------                  ----------------         #--

--#      1.0              Santosh                      05-JAN-2016             #--

--#                                                                            #--

--##############################################################################--

          aii.request_id,

          aii.status,

          aii.invoice_type_lookup_code,

          aii.source,

          aii.invoice_id,

          aii.invoice_num,

          aii.invoice_date,

          aii.vendor_id,

          aii.vendor_site_id,

          aii.invoice_currency_code,

          aii.invoice_amount,

          aii.description,

          aii.exchange_rate,

          aii.terms_name,

          aii.attribute_category,

          aii.attribute2 dff_cpr_number,

          aii.attribute3 dff_manual_payment,

          aii.attribute4 dff_service_name,

          aii.attribute5 dff_number_of_instalments,

          aii.attribute6 dff_reason_for_refund,

          aii.attribute7 dff_اسم_الوكيل,

          aii.attribute8 dff_pensioner_cpr,

          aii.attribute9 dff_jv_reference,

          aii.creation_date,

          aii.created_by,

          aii.last_update_date,

          aii.last_updated_by,

          ali.invoice_id line_invoice_id,

          ali.invoice_line_id line_invoice_line_id,

          ali.line_number line_line_number,

          ali.line_type_lookup_code line_line_type_lookup_code,

          ali.amount line_amount,

          ali.description line_description,

          ali.dist_code_combination_id line_dist_code_combination_id,

          ali.distribution_set_id line_distribution_set_id,

          ali.distribution_set_name line_distribution_set_name,

          ali.dist_code_concatenated line_dist_code_concatenated,

          ali.attribute_category line_attribute_category,

          ali.attribute1 line_attribute1,

          ali.attribute2 line_attribute2,

          ali.attribute3 line_attribute3,

          ali.attribute4 line_attribute4,

          ali.attribute5 line_attribute5,

          ali.assets_tracking_flag line_assets_tracking_flag,

          ali.po_header_id line_po_header_id,

          ali.po_number line_po_number,

          ali.po_line_id line_po_line_id,

          ali.po_line_number line_po_line_number,

          ali.po_line_location_id line_po_line_location_id,

          ali.po_shipment_num line_po_shipment_num,

          ali.po_distribution_id line_po_distribution_id,

          ali.po_distribution_num line_po_distribution_num,

          ali.po_unit_of_measure line_po_unit_of_measure,

          ali.inventory_item_id line_inventory_item_id,

          ali.item_description line_item_description,

          ali.quantity_invoiced line_quantity_invoiced,

          ali.ship_to_location_code line_ship_to_location_code,

          ali.unit_price line_unit_price,

          ali.match_option line_match_option,

          ali.final_match_flag line_final_match_flag

     FROM ap_invoices_interface aii, ap_invoice_lines_interface ali

    WHERE aii.invoice_id = ali.invoice_id;