ap invoice accounting date OR gl date update api

 CREATE OR REPLACE PACKAGE APPS.UPDATE_PKG
AS

      PROCEDURE main (
                      errbuf             OUT VARCHAR2,
                      retcode            OUT VARCHAR2
                     );

      PROCEDURE UPDATE ;

END UPDATE_PKG;
/
               ============  ****************   =============
CREATE OR REPLACE PACKAGE BODY APPS.UPDATE_PKG
AS
    PROCEDURE main (
                  errbuf             OUT VARCHAR2,
                  retcode            OUT VARCHAR2
                 ) IS
    begin

        --Calling Procedure to update the PERIOD_NAME and ACCOUNTING_DATE in Invoice Distribution Lines
        UPDATE ;
        --End
    end main;


    PROCEDURE UPDATE is
        V_PERIOD_NAME       VARCHAR2(20);
        V_ACCOUNTING_DATE   DATE;
        V_COUNT             number;
        CURSOR CUR IS
            SELECT
                AIDA.ROWID   ,
                AIDA.INVOICE_ID                     ,
                AIDA.DIST_CODE_COMBINATION_ID       ,
                AIDA.LAST_UPDATE_DATE            ,
                AIDA.LAST_UPDATED_BY                ,
                AIDA.ACCOUNTING_DATE               ,
                AIDA.PERIOD_NAME                    ,
                AIDA.SET_OF_BOOKS_ID                ,
                AIDA.AMOUNT  ,
                AIDA.DESCRIPTION                    ,
                AIDA.TYPE_1099                      ,
                AIDA.TAX_CODE_ID                    ,
                AIDA.POSTED_FLAG                    ,
                AIDA.BATCH_ID,
                AIDA.QUANTITY_INVOICED              ,
                AIDA.UNIT_PRICE                     ,
                AIDA.MATCH_STATUS_FLAG              ,
                AIDA.ATTRIBUTE_CATEGORY             ,
                AIDA.ATTRIBUTE1                     ,
                AIDA.ATTRIBUTE2                     ,
                AIDA.ATTRIBUTE3                     ,
                AIDA.ATTRIBUTE4                     ,
                AIDA.ATTRIBUTE5                     ,
                AIDA.PREPAY_AMOUNT_REMAINING        ,
                AIDA.ASSETS_ADDITION_FLAG           ,
                AIDA.ASSETS_TRACKING_FLAG           ,
                AIDA.DISTRIBUTION_LINE_NUMBER       ,
                AIDA.LINE_TYPE_LOOKUP_CODE          ,
                AIDA.PO_DISTRIBUTION_ID             ,
                AIDA.BASE_AMOUNT                    ,
                AIDA.EXCHANGE_RATE                  ,
                AIDA.EXCHANGE_RATE_TYPE             ,
                AIDA.EXCHANGE_DATE                  ,
                PA_ADDITION_FLAG               ,
                AIDA.JE_BATCH_ID                    ,
                AIDA.POSTED_AMOUNT                  ,
                AIDA.POSTED_BASE_AMOUNT             ,
                AIDA.ENCUMBERED_FLAG                ,
                AIDA.ACCRUAL_POSTED_FLAG            ,
                AIDA.CASH_POSTED_FLAG               ,
                AIDA.LAST_UPDATE_LOGIN              ,
                CASH_JE_BATCH_ID               ,
                AIDA.STAT_AMOUNT                    ,
                AIDA.ATTRIBUTE11                    ,
                AIDA.ATTRIBUTE12                    ,
                AIDA.ATTRIBUTE13                    ,
                AIDA.ATTRIBUTE14                    ,
                AIDA.ATTRIBUTE6                     ,
                AIDA.ATTRIBUTE7                     ,
                AIDA.ATTRIBUTE8                     ,
                AIDA.ATTRIBUTE9                     ,
                AIDA.ATTRIBUTE10                    ,
                AIDA.ATTRIBUTE15                    ,
                AIDA.ACCTS_PAY_CODE_COMBINATION_ID  ,
                RATE_VAR_CODE_COMBINATION_ID   ,
                PRICE_VAR_CODE_COMBINATION_ID  ,
                EXCHANGE_RATE_VARIANCE         ,
                INVOICE_PRICE_VARIANCE         ,
                BASE_INVOICE_PRICE_VARIANCE    ,
                REVERSAL_FLAG                  ,
                AIDA.PARENT_INVOICE_ID              ,
                AIDA.INCOME_TAX_REGION              ,
                AIDA.FINAL_MATCH_FLAG               ,
                AIDA.USSGL_TRANSACTION_CODE         ,
                AIDA.USSGL_TRX_CODE_CONTEXT         ,
                AIDA.EXPENDITURE_ITEM_DATE          ,
                AIDA.EXPENDITURE_ORGANIZATION_ID    ,
                AIDA.EXPENDITURE_TYPE               ,
                AIDA.PA_QUANTITY                    ,
                AIDA.PROJECT_ID                     ,
                AIDA.TASK_ID ,
                AIDA.PROJECT_ACCOUNTING_CONTEXT     ,
                QUANTITY_VARIANCE              ,
                BASE_QUANTITY_VARIANCE         ,
                PACKET_ID                      ,
                AIDA.AWT_FLAG,
                AIDA.AWT_GROUP_ID                   ,
                AWT_TAX_RATE_ID                ,
                AWT_GROSS_AMOUNT               ,
                AIDA.REFERENCE_1                    ,
                AIDA.REFERENCE_2                    ,
                AIDA.ORG_ID  ,
                OTHER_INVOICE_ID               ,
                AWT_INVOICE_ID                 ,
                AWT_ORIGIN_GROUP_ID            ,
                PROGRAM_APPLICATION_ID         ,
                PROGRAM_ID                     ,
                PROGRAM_UPDATE_DATE            ,
                REQUEST_ID                     ,
                AMOUNT_INCLUDES_TAX_FLAG       ,
                TAX_CODE_OVERRIDE_FLAG         ,
                TAX_RECOVERY_RATE              ,
                TAX_RECOVERY_OVERRIDE_FLAG     ,
                TAX_RECOVERABLE_FLAG           ,
                AIDA.AWARD_ID,
                START_EXPENSE_DATE             ,
                MERCHANT_DOCUMENT_NUMBER       ,
                MERCHANT_NAME                  ,
                MERCHANT_TAX_REG_NUMBER       ,
                MERCHANT_TAXPAYER_ID           ,
                COUNTRY_OF_SUPPLY              ,
                MERCHANT_REFERENCE ,
                ACCOUNTING_EVENT_ID
            FROM AP_INVOICES_ALL AIA,
                 AP_INVOICE_DISTRIBUTIONS_ALL AIDA
            WHERE AIA.INVOICE_NUM in ('332766')
            AND AIDA.POSTED_FLAG = 'N'
            AND DISTRIBUTION_LINE_NUMBER in (5)
            AND AIA.INVOICE_ID = AIDA.INVOICE_ID  FOR UPDATE;

        V_USER_ID NUMBER;
        V_RESP_ID NUMBER;
        V_RESP_APPL_ID NUMBER;

        BEGIN

          FND_PROFILE.GET('USER_ID',V_USER_ID);
          FND_PROFILE.GET('RESP_ID',V_RESP_ID);
          FND_PROFILE.GET('RESP_APPL_ID',V_RESP_APPL_ID);
          FND_GLOBAL.APPS_INITIALIZE(V_USER_ID,V_RESP_ID,V_RESP_APPL_ID);

        FOR REC IN CUR LOOP
            BEGIN

              V_ACCOUNTING_DATE := '01-AUG-13';


            AP_AID_TABLE_HANDLER_PKG.UPDATE_ROW
               (P_ROWID                         => REC.ROWID ,
                P_INVOICE_ID                    => REC.INVOICE_ID ,
                P_DIST_CODE_COMBINATION_ID      => REC.DIST_CODE_COMBINATION_ID ,
                P_LAST_UPDATE_DATE              => REC.LAST_UPDATE_DATE ,
                P_LAST_UPDATED_BY               => REC.LAST_UPDATED_BY ,
                P_ACCOUNTING_DATE               => V_ACCOUNTING_DATE,--REC.ACCOUNTING_DATE ,
                P_PERIOD_NAME                   => REC.PERIOD_NAME,
                P_SET_OF_BOOKS_ID               => REC.SET_OF_BOOKS_ID ,
                P_AMOUNT                        => REC.AMOUNT  ,
                P_DESCRIPTION                   => REC.DESCRIPTION ,
                P_TYPE_1099                     => REC.TYPE_1099 ,
                P_TAX_CODE_ID                   => REC.TAX_CODE_ID,
                P_POSTED_FLAG                   => REC.POSTED_FLAG ,
                P_BATCH_ID                      => REC.BATCH_ID,
                P_QUANTITY_INVOICED             => REC.QUANTITY_INVOICED,
                P_UNIT_PRICE                    => REC.UNIT_PRICE  ,
                P_MATCH_STATUS_FLAG             => REC.MATCH_STATUS_FLAG ,
                P_ATTRIBUTE_CATEGORY            => REC.ATTRIBUTE_CATEGORY ,
                P_ATTRIBUTE1                    => REC.ATTRIBUTE1 ,
                P_ATTRIBUTE2                    => REC.ATTRIBUTE2 ,
                P_ATTRIBUTE3                    => REC.ATTRIBUTE3 ,
                P_ATTRIBUTE4                    => REC.ATTRIBUTE4 ,
                P_ATTRIBUTE5                    => REC.ATTRIBUTE5 ,
                P_PREPAY_AMOUNT_REMAINING       => REC.PREPAY_AMOUNT_REMAINING ,
                P_ASSETS_ADDITION_FLAG          => REC.ASSETS_ADDITION_FLAG ,
                P_ASSETS_TRACKING_FLAG          => REC.ASSETS_TRACKING_FLAG ,
                P_DISTRIBUTION_LINE_NUMBER      => REC.DISTRIBUTION_LINE_NUMBER ,
                P_LINE_TYPE_LOOKUP_CODE         => REC.LINE_TYPE_LOOKUP_CODE ,
                P_PO_DISTRIBUTION_ID            => REC.PO_DISTRIBUTION_ID ,
                P_BASE_AMOUNT                   => REC.BASE_AMOUNT ,
                P_EXCHANGE_RATE                 => REC.EXCHANGE_RATE ,
                P_EXCHANGE_RATE_TYPE            => REC.EXCHANGE_RATE_TYPE ,
                P_EXCHANGE_DATE                 => REC.EXCHANGE_DATE ,
                P_PA_ADDITION_FLAG              => REC.PA_ADDITION_FLAG  ,
                P_JE_BATCH_ID                   => REC.JE_BATCH_ID ,
                P_POSTED_AMOUNT                 => REC.POSTED_AMOUNT ,
                P_POSTED_BASE_AMOUNT            => REC.POSTED_BASE_AMOUNT ,
                P_ENCUMBERED_FLAG               => REC.ENCUMBERED_FLAG ,
                P_ACCRUAL_POSTED_FLAG           => REC.ACCRUAL_POSTED_FLAG ,
                P_CASH_POSTED_FLAG              => REC.CASH_POSTED_FLAG ,
                P_LAST_UPDATE_LOGIN             => REC.LAST_UPDATE_LOGIN ,
                P_CASH_JE_BATCH_ID              => REC.CASH_JE_BATCH_ID ,
                P_STAT_AMOUNT                   => REC.STAT_AMOUNT ,
                P_ATTRIBUTE11                   => REC.ATTRIBUTE11,
                P_ATTRIBUTE12                   => REC.ATTRIBUTE12 ,
                P_ATTRIBUTE13                   => REC.ATTRIBUTE13 ,
                P_ATTRIBUTE14                   => REC.ATTRIBUTE14 ,
                P_ATTRIBUTE6                    => REC.ATTRIBUTE6 ,
                P_ATTRIBUTE7                    => REC.ATTRIBUTE7 ,
                P_ATTRIBUTE8                    => REC.ATTRIBUTE8 ,
                P_ATTRIBUTE9                    => REC.ATTRIBUTE9 ,
                P_ATTRIBUTE10                   => REC.ATTRIBUTE10 ,
                P_ATTRIBUTE15                   => REC.ATTRIBUTE15 ,
                P_ACCTS_PAY_CODE_COMB_ID        => REC.ACCTS_PAY_CODE_COMBINATION_ID,
                P_RATE_VAR_CODE_COMBINATION_ID  => REC.RATE_VAR_CODE_COMBINATION_ID ,
                P_PRICE_VAR_CODE_COMB_ID        => REC.PRICE_VAR_CODE_COMBINATION_ID ,
                P_EXCHANGE_RATE_VARIANCE        => REC.EXCHANGE_RATE_VARIANCE  ,
                P_INVOICE_PRICE_VARIANCE        => REC.INVOICE_PRICE_VARIANCE ,
                P_BASE_INVOICE_PRICE_VARIANCE   => REC.BASE_INVOICE_PRICE_VARIANCE ,
                P_REVERSAL_FLAG                 => REC.REVERSAL_FLAG   ,
                P_PARENT_INVOICE_ID             => REC.PARENT_INVOICE_ID ,
                P_INCOME_TAX_REGION             => REC.INCOME_TAX_REGION ,
                P_FINAL_MATCH_FLAG              => REC.FINAL_MATCH_FLAG ,
                P_USSGL_TRANSACTION_CODE        => REC.USSGL_TRANSACTION_CODE,
                P_USSGL_TRX_CODE_CONTEXT        => REC.USSGL_TRX_CODE_CONTEXT ,
                P_EXPENDITURE_ITEM_DATE         => REC.EXPENDITURE_ITEM_DATE ,
                P_EXPENDITURE_ORGANIZATION_ID   => REC.EXPENDITURE_ORGANIZATION_ID ,
                P_EXPENDITURE_TYPE              => REC.EXPENDITURE_TYPE ,
                P_PA_QUANTITY                   => REC.PA_QUANTITY ,
                P_PROJECT_ID                    => REC.PROJECT_ID ,
                P_TASK_ID                       => REC.TASK_ID ,
                P_PROJECT_ACCOUNTING_CONTEXT    => REC.PROJECT_ACCOUNTING_CONTEXT ,
                P_QUANTITY_VARIANCE             => REC.QUANTITY_VARIANCE ,
                P_BASE_QUANTITY_VARIANCE        => REC.BASE_QUANTITY_VARIANCE,
                P_PACKET_ID                     => REC.PACKET_ID ,
                P_AWT_FLAG                      => REC.AWT_FLAG ,
                P_AWT_GROUP_ID                  => REC.AWT_GROUP_ID ,
                P_AWT_TAX_RATE_ID               => REC.AWT_TAX_RATE_ID ,
                P_AWT_GROSS_AMOUNT              => REC.AWT_GROSS_AMOUNT ,
                P_REFERENCE_1                   => REC.REFERENCE_1  ,
                P_REFERENCE_2                   => REC.REFERENCE_2 ,
                P_ORG_ID                        => REC.ORG_ID ,
                P_OTHER_INVOICE_ID              => REC.OTHER_INVOICE_ID,
                P_AWT_INVOICE_ID                => REC.AWT_INVOICE_ID ,
                P_AWT_ORIGIN_GROUP_ID           => REC.AWT_ORIGIN_GROUP_ID ,
                P_PROGRAM_APPLICATION_ID        => REC.PROGRAM_APPLICATION_ID,
                P_PROGRAM_ID                    => REC.PROGRAM_ID  ,
                P_PROGRAM_UPDATE_DATE           => REC.PROGRAM_UPDATE_DATE ,
                P_REQUEST_ID                    => REC.REQUEST_ID ,
                P_AMOUNT_INCLUDES_TAX_FLAG      => REC.AMOUNT_INCLUDES_TAX_FLAG ,
                P_TAX_CODE_OVERRIDE_FLAG        => REC.TAX_CODE_OVERRIDE_FLAG ,
                P_TAX_RECOVERY_RATE             => REC.TAX_RECOVERY_RATE ,
                P_TAX_RECOVERY_OVERRIDE_FLAG    => REC.TAX_RECOVERY_OVERRIDE_FLAG ,
                P_TAX_RECOVERABLE_FLAG          => REC.TAX_RECOVERABLE_FLAG,
                P_AWARD_ID                      => REC.AWARD_ID ,
                P_START_EXPENSE_DATE            => REC.START_EXPENSE_DATE ,
                P_MERCHANT_DOCUMENT_NUMBER      => REC.MERCHANT_DOCUMENT_NUMBER,
                P_MERCHANT_NAME                 => REC.MERCHANT_NAME ,
                P_MERCHANT_TAX_REG_NUMBER       => REC.MERCHANT_TAX_REG_NUMBER ,
                P_MERCHANT_TAXPAYER_ID          => REC.MERCHANT_TAXPAYER_ID ,
                P_COUNTRY_OF_SUPPLY             => REC.COUNTRY_OF_SUPPLY ,
                P_MERCHANT_REFERENCE            => REC.MERCHANT_REFERENCE ,
                P_CALLING_SEQUENCE => 2);


                UPDATE ap_accounting_events_all
                    SET accounting_date  = '01-AUG-2013'
                WHERE accounting_event_id = REC.ACCOUNTING_EVENT_ID
                   AND event_status_code IN ('CREATED')
                   AND org_id = REC.ORG_ID
                   AND source_id = REC.INVOICE_ID;

                 v_count := cur%rowcount ;

              EXCEPTION
              WHEN OTHERS THEN
              DBMS_OUTPUT.PUT_LINE('Error Occured while updating the AP Invoice DIstrbution  :'||SQLCODE||'-'||SQLERRM);

        END;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('Total '||v_count||'  Line rows got updated');
      COMMIT;

    end UPDATE;

end UPDATE_PKG;
/

utl_mail send process

begin
  utl_mail.send(
  sender     => 'elangovan@____.com',
  recipients => 'dhana@____.com',
  message    => 'Hello World'
  );
end;

Employee Termination Report

SELECT ppf.employee_number
       , ppf.full_name, ppf.email_address, pps.date_start "Start Date",
       pps.actual_termination_date
                                      ,
       (SELECT l.meaning
          FROM hr_lookups l
         WHERE l.lookup_type(+) = 'LEAV_REAS'
               AND l.lookup_code(+) = pps.leaving_reason) leaving_reason
                                                                       
       ,
       pj.NAME job, houa.NAME organization_name,
       fu.user_name "Oracle User Name", fu.start_date "Oracle Start Date",
       fu.end_date "Oracle End Date",
       (SELECT MAX (start_time)
          FROM fnd_logins
         WHERE user_id = fu.user_id) "Last Logon Date",
       pps.last_update_date "Terminated On"              
                                           ,
       NVL2 (fu.end_date,
             fu.end_date - pps.actual_termination_date,
             NULL
            ) "Oracle end date after term"        
                                          ,
       NVL2 ((SELECT MAX (start_time)
                FROM fnd_logins
               WHERE user_id = fu.user_id),
             (SELECT MAX (start_time)
                FROM fnd_logins
               WHERE user_id = fu.user_id) - pps.actual_termination_date,
             NULL
            ) "Logon after term"                  
  FROM per_people_f ppf,
       per_people_f ppfs,
       per_assignments_f paf,
       per_assignment_status_types past,
       per_grades pg,
       per_jobs pj,
       per_job_groups pjg,
       per_pay_bases ppb,
       per_person_types ppt,
       pay_people_groups ppg,
       pay_payrolls_f pay,
       per_periods_of_service pps,
       hr_locations_all hl,
       hr_all_organization_units houg,
       hr_organization_units hougtl,
       hr_organization_units houa,
       hr_organization_units houb,
       hr_soft_coding_keyflex hsck,
       per_positions pap,
       per_person_type_usages_f pptu,
       fnd_user fu
 WHERE ppf.person_id = paf.person_id
   AND paf.business_group_id + 0 = houb.organization_id
   AND paf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id(+)
   AND paf.organization_id = houa.organization_id(+)
   AND paf.job_id = pj.job_id(+)
   AND paf.grade_id = pg.grade_id(+)
   AND paf.people_group_id = ppg.people_group_id(+)
   AND paf.pay_basis_id = ppb.pay_basis_id(+)
   AND paf.payroll_id = pay.payroll_id(+)
   AND paf.period_of_service_id = pps.period_of_service_id(+)
   AND paf.assignment_status_type_id = past.assignment_status_type_id
   AND paf.supervisor_id = ppfs.person_id(+)
   AND paf.position_id = pap.position_id(+)
   AND pptu.person_type_id = ppt.person_type_id
   AND pptu.person_id = ppf.person_id
   AND pj.job_group_id = pjg.job_group_id(+)
   AND ppt.system_person_type IN
                        ('EMP', 'EMP_APL', 'EX_EMP', 'EX_EMP_APL', 'RETIREE')
   AND hsck.segment1 = TO_CHAR (houg.organization_id(+))
   AND houg.organization_id = hougtl.organization_id(+)
   AND paf.location_id = hl.location_id(+)
   AND paf.organization_id = houa.organization_id(+)
   AND paf.effective_end_date BETWEEN NVL (ppfs.effective_start_date,
                                           paf.effective_end_date
                                          )
                                  AND NVL (ppfs.effective_end_date,
                                           paf.effective_end_date
                                          )
   AND paf.effective_end_date BETWEEN NVL (pay.effective_start_date,
                                           paf.effective_end_date
                                          )
                                  AND NVL (pay.effective_end_date,
                                           paf.effective_end_date
                                          )
   AND pps.actual_termination_date BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date
   AND pps.actual_termination_date BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date
   AND NVL (pps.actual_termination_date, SYSDATE) >=
               NVL (NVL (:p_start_date, pps.actual_termination_date), SYSDATE)
   AND NVL (pps.actual_termination_date, SYSDATE) <=
                 NVL (NVL (:p_end_date, pps.actual_termination_date), SYSDATE)
   AND NVL (ppf.current_employee_flag, 'N') = 'Y'
   AND NVL (paf.primary_flag, 'N') = 'Y'
   AND paf.assignment_type = 'E'
   AND pps.actual_termination_date BETWEEN pptu.effective_start_date
                                       AND pptu.effective_end_date
   AND system_person_type = 'EMP'
   AND ppf.person_id = fu.employee_id(+)
   AND (SELECT person_type_id
          FROM (SELECT   person_id, object_version_number, person_type_id
                    FROM per_people_f
                ORDER BY person_id, object_version_number DESC) x
         WHERE ROWNUM = 1 AND person_id = ppf.person_id) NOT IN (
                                              SELECT person_type_id
                                                FROM per_person_types
                                               WHERE system_person_type =
                                                                         'EMP');

Bank Statement Reconciliation process in Cash Management

                                                           The main reason for the Cash Management module is to reconcile all financial incoming and outgoing transactions related to Oracle. As the actual financial transactions have to be between supplier and customer banks it is necessary to get the bank statements on a regular basis and match the bank statement against all payments and receipts, etc.  If for some reason certain transactions could not be reconciled then probing both the statement and the transaction in Oracle will bring out the discrepancy. Hence this is also very effective internal audit mechanism for the organization implementing Oracle.

This is a 2 part post. The current and 2^nd post illustrates how to reconcile the statements. The 1^st post dealt with loading and importing bank statements received from the bank.

 

Auto-reconciliation

Login to Cash Management Administrator responsibility

Navigation: View > Requests

We can run the Bank Statement Loader program to load, import and reconcile a statement by selecting Process option “Load, Import, and AutoReconciliation”.

Bank Statement Load, Import & Reconcile

The requests which will run are,

Requests run by AutoReconciliation

The last request is AutoReconciliation Execution Report. The output of the request is,

AutoReconciliation Execution Report output

 

AutoReconciliation program

If the bank statement is loaded and imported, we can also run AutoReconciliation as a separate program.

AutoReconciliation program

 

Tables

After AutoReconciliation completes, the statement lines STATUS change to “RECONCILED” from “UNRECONCILED” for the lines which the program could reconcile, in the CE_STATEMENT_LINES table.

select * from CE_STATEMENT_HEADERS_ALL where statement_number =’0025/001 – 25-DEC-11′

CE_STATEMENT_HEADERS_ALL

select * from CE_STATEMENT_LINES where statement_header_id =16593

CE_STATEMENT_LINES

Note that AutoReconciliation program could not reconcile all the lines as there is an error in the opening and closing balances in the totals and that is why some of the line STATUS is UNRECONCILED.

We shall reconcile those lines using Manual Reconciliation.

 

Manual reconciliation

Login to Cash Management Administrator responsibility

Navigation: Bank Statements > Bank Statement Interface

Search for the Bank account for which the statement was imported

Reconciliation form

List of all the statements for the Bank Account comes up.

Statement list

Select the Statement here and press Review button.

 

The imported Statement header appears

Review statement

See the reconciled lines by clicking “Reconciled” button

Reconciled lines in statement

No lines have been reconciled which means we need to manually clear all the transactions. Close this window and go back to the previous window. Click Available button in the previous window to show the available transactions for reconciliation.

The Find Transactions form opens up.  

Find transaction lines

Enter the details to list lines only based on the criteria and click Find.

Find available transactions for reconciliation

Reconcile the lines by clicking on the checkbox and pressing the Reconcile button.

If the program finds a difference in the starting and ending balances you will get a message.

Reconciliation error

Click Yes to clear the transactions. Close window and go back to the previous window.

Back to Bank Statement form

Click on Reconciled button. Now the form shows some data, i.e. those reconciled transactions for this account and statement.

Reconciled transactions

If AutoReconciliation program had run successfully then we would not need to reconcile manually as we have done in the previous 2 steps as all the statement lines would have shown up on the Reconciled Transactions form.

 

Tables

Check the table CE_STATEMENT_LINES now for this statement

select * from CE_STATEMENT_LINES where statement_header_id =16593

Check CE_STATEMENT_LINES table

The status gives shows whether the statement line has been reconciled or not. Note that the first 4 lines (statement line id 219892 to 219895) have been reconciled except for statement line id 219891.

Once a line is reconciled it is copied to CE_STATEMENT_RECONCILS_ALL table.

If we check in the table for all the statement lines we shall find only the reconciled lines.

select * from CE_STATEMENT_RECONCILS_ALL where statement_line_id IN(219892,219893,219894,219895,219891)

CE_STATEMENT_RECONCILS_ALL table

Note the status of the reconciled lines. The status is M.

 

Unreconcile Transactions

The lines can be unreconciled as well. If we check the boxes in the window shown earlier and click on Unreconcile, the lines will again be available for reconciliation.

Unreconcile transactions

If we check the reconciliation table, CE_STATEMENT_RECONCILS_ALL, we will find an extra line for each of the previously reconciled lines. Now the previously reconciled lines have the same status, ‘M’, and the new lines added for unreconciling the transactions have status as ‘U’. 

Status change in CE_STATEMENT_RECONCILS_ALL table

 

Manual Reconciliation

We can also reconcile manually by going to,

Bank Statements > Manual Clearing > Clear Transactions

Clear transactions

The Find Transactions form opens up, which we have seen before as well.

In this case the system asks for the Bank Account first

Find transactions: Select bank account

Then we can give the same criteria as we had given before, in the Transaction tab.

Find transactions: Select dates

Notice that we are back in the same form for clearing transactions.

Clear transactions form

Now we can clear transactions as shown above. This way transactions can be reconciled manually. Once the transactions are reconciled the transactions are updated with RECONCILED status as well in the sub ledgers.