Wednesday 22 June 2016

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

Tuesday 14 June 2016

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.