HCM SQL: Mapping Between Absence Plan and Type

SELECT 
        aapf.absence_plan_id,
        aapft.name absence_plan_name,
        aatft.name absence_type_name,
        aatft.absence_type_id,
        aatp.ABSENCE_TYPE_PLAN_ID,
        aatf.status
    FROM
        anc_absence_plans_f_tl aapft,
        anc_absence_plans_f aapf,
        ANC_ABSENCE_TYPES_F_TL aatft,
        ANC_ABSENCE_TYPES_F aatf,
        ANC_ABSENCE_TYPE_PLANS_F aatp
    WHERE 1=1
        --------
        and aapft.absence_plan_id = aapf.absence_plan_id
        AND aapf.plan_status = 'A'   -- added to pick only Active Absence Plans
        AND aapft.language = 'US'
        and aapf.LEGISLATIVE_DATA_GROUP_ID = :P_LDG
        --------
        and aatft.absence_type_id = aatf.absence_type_id
        AND aatf.status = 'A'   -- added to pick only Active Absence Plans
        AND aatft.language = 'US'       
        and aatf.LEGISLATIVE_DATA_GROUP_ID = :P_LDG
        --------
        and aatp.ABSENCE_PLAN_ID = aapf.absence_plan_id
        and aatp.ABSENCE_TYPE_ID = aatf.ABSENCE_TYPE_ID
        --------   
        AND trunc(SYSDATE) BETWEEN aapf.effective_start_date AND aapf.effective_end_date
        AND trunc(SYSDATE) BETWEEN aapft.effective_start_date AND aapft.effective_end_date
        AND trunc(SYSDATE) BETWEEN aatp.effective_start_date AND aatp.effective_end_date
        AND trunc(SYSDATE) BETWEEN aatft.effective_start_date AND aatft.effective_end_date
        AND trunc(SYSDATE) BETWEEN aatf.effective_start_date AND aatf.effective_end_date

Multi Sheet Excel Output Using RTF Template with Custom Worksheet Name

<?spreadsheet-sheet-name: {concat(.//YEAR, ' ',.//STATE)}?>

By default, page breaks and section breaks specified in the RTF template create a new sheet in the output Excel workbook. You can control whether page breaks create a new sheet using the property xlsx-page-break-as-new-sheet. 

Set this property to "false" when you do not want page breaks in the RTF template to generate new sheets in the Excel workbook. 

A similar control does not exist for section breaks; that is, a section break in the template will always create a new sheet in the Excel workbook output.

For information on setting properties in an RTF template, see Section 4.13.3, "Setting Properties."

4.23.2 Specifying a Sheet Name

To specify a sheet name, use the following command in the template:

<?spreadsheet-sheet-name: xpath-expression?>

where xpath-expression is an XPath expression or a string constant.

For example, assume your template uses input data as follows:

 <?xml version="1.0" encoding="UTF-8" ?> 
   <ROWSET>
    <ROW>
      <CUSTOMER_NAME>Vgpsuwo Fjprpit</CUSTOMER_NAME> 
      <CUSTOMER_NUMBER>7795</CUSTOMER_NUMBER> 
      <YEAR>2005</YEAR> 
      <BRAND>MSPKID</BRAND> 
      <DIVISION>UWGLERXM</DIVISION> 
      <STATE>LD</STATE> 
      <INVOICE_DATE>2004-12-07T07:13:15.379-08:00</INVOICE_DATE> 
      <INVOICE_NO>806356</INVOICE_NO> 
      <INVOICE_AMOUNT>8181.704554734346</INVOICE_AMOUNT> 
    </ROW>
...
</ROWSET>

To generate a sheet name that shows the YEAR and STATE from the data (for example, "2005 ID") enter the following in a BI Publisher field in your template:

<?spreadsheet-sheet-name: {concat(.//YEAR, ' ',.//STATE)}?>

Ensure that your expression generates unique sheet names within the workbook.

https://blogs.oracle.com/xmlpublisher/multi-sheet-excel-output

Analytical SQL: Rank Vs Dense Rank

Rank Vs Dense Rank

select e.*, rank() over( order by salary desc) from employees e;
-- rank returned as 1,2,3,3,5

select e.*, dense_rank() over( order by salary desc) from employees e;
-- rank returned as 1,2,3,3,4

RTF Template: Column Width

Issue:

When creating a BI Publisher rdf template in Microsoft Word, with intended output to Microsoft Excel; is there a way to specify that a table should use auto width for the columns in the Excel output.

By default, when you use the BI Publisher Desktop addin for Microsoft Word; the table wizard creates a table in Word that does not result in output to Excel where the columns in Excel is sized to fit the data. Users of the report then have to manually size the excel columns every time they run the report.

I did try "Autofit to Contents" in Microsoft Word, but it didn't work.

Resolution:

In order to fix this i had to follow these steps (Using MS Word 2010/RTF file)

1. Select any Cell on the Table in your RTF
2. MS Word-Ribbon-menu: Select Layout -> Select -> Select Table (this will highlight the table)
3. Right click the highlighted table and goto "Table Properties"
4. In Table Properties window goto Column tab and enter "Preferred width" some value.

Remarks: Total Preferred width can be > 100%

There is a value of preferred width 100% = ex: 16 cm 

then for each column, you may decide, how much width you need. and just place the value.

Your template may look little wierd, but output will be fine.

$FLEX$ Syntax

$FLEX$ Syntax

Here is an example of using :$FLEX$.Value_Set_Name to set up value sets where one segment depends on a prior segment that itself depends on a prior segment ("cascading dependencies"). Assume you have a three-segment flexfield where the first segment is car manufacturer, the second segment is car model, and the third segment is car color. You could limit your third segment's values to only include car colors that are available for the car specified in the first two segments. Your three value sets might be defined as follows:

Segment Name         Manufacturer 
Value Set Name       Car_Maker_Name_Value_Set 
Validation Table     CAR_MAKERS 
Value Column         MANUFACTURER_NAME 
Description Column       MANUFACTURER_DESCRIPTION 
Hidden ID Column         MANUFACTURER_ID 
SQL Where Clause     (none) 

Segment Name         Model 
Value Set Name       Car_Model_Name_Value_Set 
Validation Table     CAR_MODELS 
Value Column         MODEL_NAME 
Description Column       MODEL_DESCRIPTION 
Hidden ID Column         MODEL_ID 
SQL Where Clause     WHERE MANUFACTURER_ID = 
                     :$FLEX$.Car_Maker_Name_Value_Set 

Segment Name         Color 
Value Set Name       Car_Color_Name_Value_Set 
Validation Table     CAR_COLORS 
Value Column         COLOR_NAME 
Description Column       COLOR_DESCRIPTION 
Hidden ID Column         COLOR_ID 
SQL Where Clause     WHERE MANUFACTURER_ID = 
                     :$FLEX$.Car_Maker_Name_Value_Set
                     AND MODEL_ID = 
                     :$FLEX$.Car_Model_Name_Value_Set 

In this example, MANUFACTURER_ID is the hidden ID column and MANUFACTURER_NAME is the value column of the Car_Maker_Name_Value_Set value set. The Model segment uses the hidden ID column of the previous value set, Car_Maker_Name_Value_Set, to compare against its WHERE clause. The end user never sees the hidden ID value for this example. 

How To check if Text Contains Arabic Text in SQL

Use the below code snippet to check

case
  when ascii(substr(ipa.BENEFICIARY_NAME,-1,1)) between 55424 and 56255
  then 'ARABIC'
  else 'OTHER'
end LANG_FLAG

select ascii('؀') first_letter, ascii('ۿ') last_letter from dual;

FIRST ASCII VALUE: 55424, LAST ASCII VALUE 56255 

https://www.unicode.org/charts/PDF/U0600.pdf

http://jrgraphix.net/r/Unicode/0600-06FF

SQL Hints: /*+ MONITOR */

https://oracle-base.com/articles/11g/report_sql_monitor_text.txt

SQL HINT

/*+ MONITOR */

/*+ MONITOR */

The MONITOR hint switches on SQL monitoring for statements that would not otherwise initiate it.

SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
FROM   emp e
       JOIN dept d ON e.deptno = d.deptno
GROUP BY d.dname
ORDER BY d.dname;


If you have long running statements you don't want to monitor, use the NO_MONITOR hint to prevent them being monitored.



-- 11gR2
SET LINESIZE 200
COLUMN sql_text FORMAT A80

SELECT sql_id, status, sql_text
FROM   v$sql_monitor
WHERE  username = 'SCOTT';

SQL_ID        STATUS              SQL_TEXT
------------- ------------------- --------------------------------------------------------------------------------
526mvccm5nfy4 DONE (ALL ROWS)     SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees
                                  FROM   emp e
                                         JOIN dept d ON e.deptno = d.deptno
                                  GROUP BY d.dname
                                  ORDER BY d.dname
                                 


Once the SQL_ID is identified, we can generate a report using the REPORT_SQL_MONITOR function.
*/

SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 1000
SET PAGESIZE 0
SET TRIM ON
SET TRIMSPOOL ON
SET ECHO OFF
SET FEEDBACK OFF

SPOOL /host/report_sql_monitor.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => '526mvccm5nfy4',
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF                                 



SQL Monitoring Report

SQL Text
------------------------------
SELECT /*+ MONITOR */ d.dname, WM_CONCAT(e.ename) AS employees FROM emp e JOIN dept d ON e.deptno = d.deptno GROUP BY d.dname ORDER BY d.dname

Global Information
------------------------------
 Status              :  DONE (ALL ROWS)
 Instance ID         :  1
 Session             :  SCOTT (53:1261)
 SQL ID              :  526mvccm5nfy4
 SQL Execution ID    :  16777217
 Execution Started   :  03/19/2011 12:55:13
 First Refresh Time  :  03/19/2011 12:55:13
 Last Refresh Time   :  03/19/2011 12:55:13
 Duration            :  .014344s
 Module/Action       :  SQL*Plus/-
 Service             :  SYS$USERS
 Program             :  sqlplus@oel5-11gR2-demo.localdomain (TNS V1-V3)
 Fetch Calls         :  4

Global Stats
======================================================================
| Elapsed |   Cpu   |    IO    | PL/SQL  |  Other   | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Time(s) | Waits(s) | Calls |  Gets  |
======================================================================
|    0.01 |    0.01 |     0.00 |    0.00 |     0.00 |     4 |     41 |
======================================================================

SQL Plan Monitoring Details (Plan Hash Value=2970111170)
================================================================================================================================================
| Id |            Operation            |  Name   |  Rows   | Cost |   Time    | Start  | Execs |   Rows   |  Mem  | Activity | Activity Detail |
|    |                                 |         | (Estim) |      | Active(s) | Active |       | (Actual) | (Max) |   (%)    |   (# samples)   |
================================================================================================================================================
|  0 | SELECT STATEMENT                |         |         |      |         1 |     +0 |     1 |        3 |       |          |                 |
|  1 |   SORT GROUP BY                 |         |       4 |    7 |         1 |     +0 |     1 |        3 |  2048 |          |                 |
|  2 |    MERGE JOIN                   |         |      14 |    6 |         1 |     +0 |     1 |       14 |       |          |                 |
|  3 |     TABLE ACCESS BY INDEX ROWID | DEPT    |       4 |    2 |         1 |     +0 |     1 |        4 |       |          |                 |
|  4 |      INDEX FULL SCAN            | PK_DEPT |       4 |    1 |         1 |     +0 |     1 |        4 |       |          |                 |
|  5 |     SORT JOIN                   |         |      14 |    4 |         1 |     +0 |     4 |       14 |  2048 |          |                 |
|  6 |      TABLE ACCESS FULL          | EMP     |      14 |    3 |         1 |     +0 |     1 |       14 |       |          |                 |
================================================================================================================================================