Third Party Process - Colombia

This page explains the steps involved in generating the Colombia Third party report balances.

This process ensures the passage of the third party details to GL. This is mandatory in reporting the third party details to the Colombian government and to comply with the regulations of the Commercial Code, Decree 2649 of 1993 and No.5 Orientation Professional Technical Council of Public Accounting for submitting tax information.

The report should be generated by the system recorded books so as to provide a clear, complete and reliable business history trader, subject to regulations issued by the Colombian government.

Colombia GL à CLL F041: Generate Magnetic Media SLA à

1.       Third Party Maintenance Balances

2.       Third Party Report Attributes

3.       Third Party Report Balances

Electronic File Transfer in AR – Brazil – Technical Information

Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to electronically transfer collection documents to banks using files in the standard CNAB format. The CLL F034 features allows companies to meet Brazilian Bank Transfer requirements by providing customers with the most important bank file formats in Brazil, as each bank has its own format.

Tables:

Setup Tables:

JL_BR_AR_BANK_INSTRUCTIONS à Bank Instruction table

JL_BR_AR_BANK_OCCURENCES à Bank Occurrences table

JL_BR_AR_REC_MET_ACCTS_ExT_ALL à Receipt Method Accounts table

Transaction Tables:

JL_BR_AR_SELECT_CONTROLS_ALL à Bank Transfer Remittance Batches selection criteria table

JL_BR_AR_SELECT_ACCOUNTS_ALL à Bank Account Distributions selection criteria table

JL_BR_AR_BORDEROS_ALL à Header for Remittance and Occurrence batches

JL_BR_AR_REMIT_BORDEROS_ALL à Bordero remittance additional information

JL_BR_AR_COLLECTION_DOCS_ALL à Collection documents associated to remittance borderos

JL_BR_AR_OCCURRENCE_DOCS_ALL à All occurrences related to the collection documents

JL_BR_AR_RET_INTERFACE_ALL à Interface table for Bank returns

JL_BR_AR_RET_INTERFACE_EXT_ALL à Interface table with rejected lines

JL_BR_AR_BANK_RETURNS_ALL à Temporary table to store bank returned occurrences.

Brazil Electronic File Transfer in Receivables

Companies in Brazil use bank services to collect customers. The Brazilian Electronic File Transfer for Receivables (CLL F034) provides the functionality to electronically transfer collection documents to banks using files in the standard CNAB format. The CLL F034 features allows companies to meet Brazilian Bank Transfer requirements by providing customers with the most important bank file formats in Brazil, as each bank has its own format.

Bank Collection Flow

Bank Collection – Setup

1.       Create Bank Accounts: Receivables à Setup à Receipt à Banks à Bank Accounts

2.       Define Format Programs: Receivables à Setup à Receipt à Format Programs

3.       Define Receipt Class: Receivables à Setup à Receipt à Receipt Classes

4.       Define Receipt method Accounts: Brazilian Localizations à Setup à Bank Collections à Receipt Method Accounts

5.       Define bank Instruction Codes: Brazilian Localization à Setup à Bank Collections à Instruction Codes

6.       Define Bank Occurrence Codes: Brazilian Localization à Setup à Bank Collections à Occurrence Codes

Excel Merge and Download using Custom Template in APEX

For Merging cells in Excel and for other CSS in download property, i.e  to create 

down loadable XLS formatted sheets from any version of APEX use the following steps:

1. Create a new blank page in the application using Customized template. In that customized template remove all the contents in the page, like navigation menus, APEX page Toolbar, and others , since by putting this in a Before Header process on a page, the entire page will be downloaded as a native Excel Spreadsheet.

2. Create PLSQL Region with no template.

-- Should use recursion for a full tree, but we only need executives. 

DECLARE

   l_dirtotal   NUMBER (12, 2);

BEGIN

   HTP.p

      ('<table border=1 white-space=nowrap><tr style="background-color:silver;">

    <th colspan=5>Employee</th><th>Title</th><th>Salary</th>

    <th>Direct Report Total</th></tr>'

      );

   FOR c_exec IN (SELECT *

                    FROM employee

                   WHERE direct_report_id IS NULL)

   LOOP

      SELECT SUM (salary)

        INTO l_dirtotal

        FROM employee

       WHERE direct_report_id = c_exec.ID;

      HTP.p

         (   '<tr height=80><th align=right><img src="'

          || c_exec.image

          || '"></th><th colspan=4 style="white-space:nowrap;font-size:36px;color:green;background-color:#CCFFCC;">'

          || c_exec.NAME

          || '</th><th>'

          || c_exec.title

          || '</th>

    <th style="font-size:36px;">'

          || TO_CHAR (c_exec.salary, '$999G999G999D00')

          || '</th><th>'

          || TO_CHAR (l_dirtotal, '$999G999G999G999D00')

          || '</th></tr>'

         );

      -- Layer 1

      FOR c_vp IN (SELECT *

                     FROM employee

                    WHERE direct_report_id = c_exec.ID)

      LOOP

         SELECT SUM (salary)

           INTO l_dirtotal

           FROM employee

          WHERE direct_report_id = c_vp.ID;

         HTP.p

            (   '<tr height=55><th colspan=3 align=right><img src="'

             || c_vp.image

             || '"></td><td colspan=2 style="font-size:24px;color:#0000CC;white-space:nowrap;background-color:#CCCCFF;">'

             || c_vp.NAME

             || '</td><td style="text-size:18px;text-align:center;">'

             || c_vp.title

             || '</td><td style="color:red;text-align:right;font-size:20px;">'

             || TO_CHAR (c_vp.salary, '$999G999G999D00')

             || '</td><td>'

             || TO_CHAR (l_dirtotal, '$999G999G999G999D00')

             || '</td></tr>'

            );

         -- Layer 2

         FOR c_grunt IN (SELECT *

                           FROM employee

                          WHERE direct_report_id = c_vp.ID)

         LOOP

            HTP.p

               (   '<tr height=50><th colspan=4 align=right>

        <img src="'

                || c_grunt.image

                || '"></td><td style="text-size:14px;white-space:nowrap;">'

                || c_grunt.NAME

                || '</td><td style="text-align:center;">'

                || c_grunt.title

                || '</td><td style="text-align:right;">'

                || TO_CHAR (c_grunt.salary, '$999G999G999D00')

                || '</td></tr>'

               );

         END LOOP;

      END LOOP;

   END LOOP;

   HTP.p ('</table>');

END;

(Modify the code as per the requirement)

3. Create Before Header process to set HTTP Headers.

BEGIN

   OWA_UTIL.mime_header ('application/vnd.ms-excel', FALSE);

   HTP.prn ('Content-Disposition: attachment; filename="spreadsheet.xls"

'  );

   OWA_UTIL.http_header_close;

END;

4. Redirect the Download button in your screen to this new page.

Sample Output:

Get Filtered Columns and Values in APEX Interactive Report

In order to get the filtered columns and their corresponding values in APEX Interactive Report, we can use this code. 

So if the user selects multiple filters in IR and we need to retrieve the values of filter columns and their corresponding values, conditions, operators used for the conditions,etc we can retrieve using the below code:

SELECT ircond.condition_name, ircond.condition_type, ircol.report_label,

       ircond.condition_column_name, ircond.condition_operator,

       ircond.condition_expr_type, ircond.condition_expression,

       ircond.condition_expression2, ircond.condition_sql,

       ircond.condition_display, ircond.condition_enabled

  FROM apex_application_page_ir ir JOIN apex_application_page_ir_rpt irr

       ON ir.interactive_report_id = irr.interactive_report_id

       JOIN apex_application_page_ir_cond ircond

       ON irr.report_id = ircond.report_id

       JOIN apex_application_page_ir_col ircol

       ON ir.interactive_report_id = ircol.interactive_report_id

     AND ircond.condition_column_name = ircol.column_alias

 WHERE ir.application_id = TO_NUMBER (:app_id)

   AND ir.page_id = TO_NUMBER (:app_page_id)

   AND ir.region_name = 'Interactive Report'

   AND ircond.condition_type = 'Filter'

   AND irr.session_id = :app_session

   AND irr.base_report_id =

          (SELECT apex_ir.get_last_viewed_report_id (TO_NUMBER (:app_page_id),

                                                     ir.region_id

                                                    )

             FROM DUAL)

Use a condition to identify the specific interactive report using either the region name or a static region ID. 

Note: Can filter using Actions Menu and also using Header column filter. 

Example:

The below filtered columns and corresponding values can be retrieved using the above code.