Checkbox in xml publisher report in Oracle Apps R12

Open your BI Publisher folder its 32 or 64 bit, check both if available.

C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config

C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config

Copy, paste and  rename file xdo example.cfg as xdo.cfg in the same folder.

do below 2 modifications for xdo.cfg file .

1. Add/update property under <properties>

<property name="rtf-checkbox-glyph">Wingdings;254;168</property>

<!--254 for checked box, 168 unchecked empty, google it for Wingdings 254 253 168 -->

2. check the fonts path

by default it was c:/WINT… change it to ur pc correct ex: C:\Windows\Fonts\wingding.ttf

3.

Enable Developer ribbon tab in Word.

Right click ribbon tab🡪 Customize 🡪 Enable the checkbox for Developer.

Insert checkbox from the Legacy Forms 🡪 Checkbox.

Add Condition in the checkbox as per your requirement ex:☐

It will work only on pdf as per oracle document.

Run the xml report output in pdf and check.

If condition true it will be checked, else empty.

4.

Deploying to Server:

Download file from https://github.com/IamDH4/ttf-wps-fonts/blob/master/wingding.ttf

Check in the Word template also having same font 🡪 optional.

Under Administration 🡪 configuration

Text: Wingdings;0254;0168

Oracle Apps R12 Get_Net_Accrual Technical Details

--------------------------------------------------------------------*/
DECLARE
P_Assignment_ID                   Number := 22432;
P_Plan_ID                          Number := 61;
P_Payroll_ID                       Number:= 61;
P_Business_Group_ID               Number := 81;
P_Assignment_Action_ID             Number := -1;
P_Calculation_Date                 Date := TO_DATE('31-DEC-2019');
P_Accrual_Start_Date               Date;-- default null
P_Accrual_Latest_Balance          Number;-- default null
P_Calling_Point                   Varchar2(20):= 'FRM';
P_Start_Date                      Date;
P_End_Date                       Date;
P_Accrual_End_Date               Date;
P_Accrual                        Number;
P_Net_Entitlement               Number;
g_package  varchar2(50) := '  per_accrual_calc_functions.';

  l_proc        varchar2(72) := g_package||'Get_Net_Accrual';
  l_absence     number := 0;   --changed for bug 6914353
  l_accrual     number;
  l_other       number := 0;   --changed for bug 6914353
  l_carryover   number;
  l_start_date  date;
  l_end_date    date;
  l_accrual_end_date date;
  l_defined_balance_id number;

  l_atd         date; --added for bug 6418568

  cursor c_get_balance is
  select defined_balance_id
  from pay_accrual_plans
  where accrual_plan_id = p_plan_id;

  --added for bug 6418568
  cursor c_get_atd is
   select nvl(pps.ACTUAL_TERMINATION_DATE,to_date('31/12/4712','dd/mm/yyyy'))
   from per_periods_of_service pps, per_all_assignments_f paaf
   where paaf.person_id = pps.person_id
    and paaf.period_of_service_id = pps.period_of_service_id
    and paaf.Assignment_ID = P_Assignment_ID
    and P_Calculation_Date between paaf.effective_start_date and paaf.effective_end_date;


begin


  open c_get_balance;
  fetch c_get_balance into l_defined_balance_id;
  close c_get_balance;

  if p_calling_point = 'BP' and
     l_defined_balance_id is not null and
     p_assignment_action_id <> -1 then
  --
    /* Procedure called from batch process, so
       get latest balance. */

    p_net_entitlement := pay_balance_pkg.get_value(
                            p_defined_balance_id => l_defined_balance_id
                           ,p_assignment_action_id => p_assignment_action_id
                            );
  --
  else
  --

    per_accrual_calc_functions.get_accrual(p_assignment_id => p_assignment_id,
                p_plan_id => p_plan_id,
                p_calculation_date => p_calculation_date,
                p_business_group_id => p_business_group_id,
        p_payroll_id => p_payroll_id,
                p_assignment_action_id => p_assignment_action_id,
                p_accrual_start_date => p_accrual_start_date,
                p_accrual_latest_balance => p_accrual_latest_balance,
                p_start_date => l_start_date,
                p_end_date => l_end_date,
        p_accrual_end_date => l_accrual_end_date,
                p_accrual => l_accrual);

    --start changes for bug 6418568
    open c_get_atd;
    fetch c_get_atd into l_atd;
    close c_get_atd;

    if l_accrual_end_date is not null then
    --
     l_absence := per_accrual_calc_functions.get_absence(p_assignment_id => p_assignment_id,
                             p_plan_id => p_plan_id,
     p_start_date => l_start_date,
     p_calculation_date => l_end_date);

     l_other := per_accrual_calc_functions.get_other_net_contribution(
     p_assignment_id => p_assignment_id,
                             p_plan_id => p_plan_id,
                             p_start_date => l_start_date,
                             p_calculation_date => l_end_date
     );
    else
    --
     if l_atd >= P_Calculation_Date then
--
      l_absence := per_accrual_calc_functions.get_absence(p_assignment_id => p_assignment_id,
                             p_plan_id => p_plan_id,
     p_start_date => l_start_date,
     p_calculation_date => l_end_date);

      l_other := per_accrual_calc_functions.get_other_net_contribution(
     p_assignment_id => p_assignment_id,
                             p_plan_id => p_plan_id,
                             p_start_date => l_start_date,
                             p_calculation_date => l_end_date
     );
--
     end if;
    --
    end if;
    --end changes for bug 6418568

l_carryover :=0;  -- 12880652
    l_carryover := per_accrual_calc_functions.get_carry_over(
                             p_assignment_id => p_assignment_id,
                             p_plan_id => p_plan_id,
                             p_start_date => l_start_date,
                             p_calculation_date => l_end_date);
-- 12880652

if P_Calculation_Date > l_atd and l_carryover <> 0 then
     l_other := per_accrual_calc_functions.get_other_net_contribution(
  p_assignment_id => p_assignment_id,
        p_plan_id => p_plan_id,
                           p_start_date => l_start_date,
                           p_calculation_date => l_end_date
       );

l_absence := per_accrual_calc_functions.get_absence(p_assignment_id => p_assignment_id,
                             p_plan_id => p_plan_id,
     p_start_date => l_start_date,
     p_calculation_date => l_end_date); -- added new 12880652


 end if;
-- 12880652

    --
    -- Set up values in the return parameters.
    --
    DBMS_OUTPUT.PUT_LINE( 'l_accrual->'||l_accrual||'->l_absence->'||l_absence||'--l_other->'|| l_other ||'-CARRYOVER-'|| l_carryover);
    p_net_entitlement := l_accrual - l_absence + l_other + l_carryover;
    p_accrual := l_accrual;
    p_start_date := l_start_date;
    p_end_date := l_end_date;
    p_accrual_end_date := l_accrual_end_date;
  --
  end if;

  --
 

--
end ;
--

Excel Trim remove spaces not working then use TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

Step1: Formula: =TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

C1 is the value

Step2: Copy all the column, replace with value.

Step3: convert text to number.

Oracle Apps R12 query for attachments from backend for sshr txns

1. get the item_key, for workflow administrator

select * from hr_api_transactions
where item_key = '217855'

2. get the transaction_id and other details from query 1,
the transaction_id can be in pk5_value or pk1_value for the below query 2.
if not then query based on created_by, creation_date from query 1.

 select  fd.*, fl.*--,fad.*--fd.*,
         from fnd_attached_documents fad 
             ,fnd_documents fd 
             ,fnd_lobs fl
             ,fnd_document_datatypes fdd
             ,fnd_document_categories_tl fdct   
         where fad.document_id = fd.document_id 
         and   fd.media_id     = fl.file_id 
         and   fd.datatype_id  = fdd.datatype_id
         and   fd.category_id  = fdct.category_id 
         and   fdd.user_name   = 'File' 
        --and   fad.entity_name = 'XXASSET' -- replace with the entity_name you want to extract.
        and fdct.language = 'US'
        --and pk5_value = '217855'
        and fad.creation_date like to_date('27-oct-2019')
        and fad.created_by = 12345
           
       
        and fd.creation_date like sysdate

Solution for xml output of item number type with prefix 00, showing output in excel without prefix

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override">  <?XX_XML_TAG?>  </fo:bidi-override>

Oracle Apps R12 Query for Employee contact relationship and details.

SELECT papf.person_id employee_id, papf.full_name employee_name,papf.employee_number,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,pcr.contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date, papf_cont.date_of_birth contact_dob, papf_cont.sex
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont --contact info also saved in per_all_people_f without emp_num
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = 'CONTACT'
AND hl.lookup_code(+) = pcr.contact_type
and papf.employee_number = '1234'

html to get co-ordinates (latitude/longitude) and validation around +/- 50 meters.

<!DOCTYPE html>
<html>
<body>

<p>Click the button to get your coordinates.</p>

<button onclick="getLocation()">Check In</button>


<p id="demo"></p>

<script>
var x = document.getElementById("demo");

function getLocation() {
  if (navigator.geolocation) {
    navigator.geolocation.getCurrentPosition(showPosition);
  } else {
    x.innerHTML = "Geolocation is not supported by this browser.";
  }
}

function showPosition(position) {

// x.innerHTML = "Latitude: " + position.coords.latitude +
  //"<br>Longitude: " + position.coords.longitude;
 
  var meters = 50;
  var off_latitude = 85.1288229;
  var off_longitude = 89.3264959;

 
  var curr_latitude = position.coords.latitude;
  var curr_longitude = position.coords.longitude;


var coef = meters * 0.0000089;

var plus50_lat = off_latitude + coef;
// pi / 180 d= 0.018
var plus50_long = off_longitude + coef / Math.cos(off_latitude * 0.018);

var minus50_lat = off_latitude - coef;
var minus50_long = off_longitude - coef / Math.cos(off_latitude * 0.018);


if (
((curr_latitude <= off_latitude && curr_latitude >= minus50_lat)
||(curr_latitude <= plus50_lat && curr_latitude >= off_latitude)
)
   
      && (curr_longitude >= off_longitude && curr_longitude <= plus50_long) )
      {
         x.innerHTML = "You are in the office, good Morning" + "<br>"+ "CurrLatitude: "+ curr_latitude + "<br>CurrLongitude: " +curr_longitude
                        + "<br>"+ "off_latitude : "+ off_latitude + "<br>off_longitude : " + off_longitude
                        + "<br>"+ "plus50_lat : "+ plus50_lat + "<br>plus50_long : " + plus50_long + "<br>"+ "minus50_lat : "+ minus50_lat + "<br>minus50_long : " + minus50_long ; 
      }
     
    else
      {
      x.innerHTML = "You are not in the office, please recheck"+ "<br>"+ "CurrLatitude: "+ curr_latitude + "<br>CurrLongitude: " +curr_longitude
                        + "<br>"+ "off_latitude : "+ off_latitude + "<br>off_longitude : " + off_longitude
                        + "<br>"+ "plus50_lat : "+ plus50_lat + "<br>plus50_long : " + plus50_long + "<br>"+ "minus50_lat : "+ minus50_lat + "<br>minus50_long : " + minus50_long ;   
      }

 
}
</script>

</body>
</html>