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.