Encountered the symbol "?" when expecting one of the following

When running the pages from JDevloper if u get the error message
"Encountered the symbol "?" when expecting one of the following" then
you must put the following sentence at the end of the dbc file and things will work fine
JDBC\:processEscapes=true

HR Transaction usefull queries

Transaction statuses
SELECT


Final Status of the transaction:
SELECT PSAH.*
           FROM pqh_ss_approval_history psah
          WHERE psah.transaction_history_id = :Transaction_id
            AND transaction_history_id NOT IN (
                   SELECT transaction_id
                     FROM hr_api_transactions hat, wf_notifications wn
                    WHERE transaction_id = transaction_history_id
                      AND hat.item_key = wn.item_key
                      AND wn.status = 'CLOSED')select * from wf_notifications
where item_key=:item_key * FROM HR_LOOKUPS WHERE LOOKUP_TYPE = 'PQH_SS_TRANSACTION_STATUS'



AME Approvers list will be
-------------------------------
SELECT * FROM AME_TEMP_OLD_APPROVER_LISTS where transaction_id = :tranId
order by order_number
Adhoc appovers from below table
-----------------------------------
AME_TEMP_INSERTIONS

Script to Debug in PL/SQL (Store in custom table)

 CREATE TABLE ora_debug_logs (
  error_message  VARCHAR2(4000)
);
CREATE OR REPLACE PROCEDURE ora_log_proc (p_error_message  IN  VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO ora_debug_logs (error_message)
  VALUES (p_error_message);
  COMMIT;
END;
/

Standard Oracle String to Date conversion in Oracle SQL

Convert the string to Date
select TRUNC (fnd_conc_date.string_to_date ('2012-04-01')) from dual


Get Date format of a string
select fnd_conc_date.get_date_format('2012-04-01') from dual

Reset Cookies window.onload in Java Script

<Script language ="Javascript">
 function notNull()
 {
    if (document.LvDtlsFrm.calcDT.value == null || document.LvDtlsFrm.calcDT.value=="")
    alert("Please select Calculation Date:");
    else{
   
      document.LvDtlsFrm.submit();}
 }
var my_time = new Date();
function getCookie(c_name){     if (document.cookie.length>0){         c_start=document.cookie.indexOf(c_name + "=");         if (c_start!=-1){                  c_start=c_start + c_name.length+1;                  c_end=document.cookie.indexOf(";",c_start);                 if (c_end==-1) c_end=document.cookie.length;                 return unescape(document.cookie.substring(c_start,c_end));         }      }     return ""; } 
function setCookie(c_name,value,expiredays){     var exdate=new Date();     exdate.setDate(exdate.getDate()+expiredays);     document.cookie=c_name+ "=" +escape(value)+     ((expiredays==null) ? "" : ";expires="+exdate.toGMTString()); } 
function checkCookie(){      username=getCookie('username');     if(username!=null && username!="" && username == my_time ){    return false;     }else{ setCookie('username',my_time,365); document.LvDtlsFrm.submit();  } }
window.onload = checkCookie;
</Script>

use this notNull function also for button
<td align="right" nowrap><input type="button" name="LvbalDT" value="Submit" onclick="notNull()"></td>

Error: Please fix final approver from TestEmpName

To find the extact error, excute the below script.

-- getApplicableRules.sql
-- This script gives the list of applicable rules for a transaction.
-- User has to provide the value for the variable "transactionId"
-- before running the script.
set serveroutput on size 1000000
declare
fndApplicationId integer :=800;
transactionTypeId varchar2(30) := 'SSHRMS';
transactionId varchar2(10) := &P_tran_id ;
ruleIdsOut ame_util.idList;
ruleDescriptionsOut ame_util.stringList;
approvers ame_util.approversTable2;
flagApproversAsNotified varchar2(20) := ame_util.booleanFalse;
approvalProcessCompleteYNOut varchar2(20);
printString varchar2(200) := '';
begin
ame_engine.updateTransactionState(isTestTransactionIn => false,
isLocalTransactionIn => false,
fetchConfigVarsIn => true,
fetchOldApproversIn => false,
fetchInsertionsIn => false,
fetchDeletionsIn => false,
fetchAttributeValuesIn => true,
fetchInactiveAttValuesIn => false,
processProductionActionsIn => false,
processProductionRulesIn => false,
updateCurrentApproverListIn => false,
updateOldApproverListIn => false,
processPrioritiesIn => true,
prepareItemDataIn => false,
prepareRuleIdsIn => true,
prepareRuleDescsIn => true,
transactionIdIn => transactionId,
ameApplicationIdIn => null,
fndApplicationIdIn => fndApplicationId,
transactionTypeIdIn => transactionTypeId);
ame_engine.getApplicableRules
(ruleIdsOut => ruleIdsOut
,ruleDescriptionsOut => ruleDescriptionsOut);
--
for i in 1..ruleIdsOut.count loop
dbms_output.put_line('Applicable rule ' || i || ' is : ' || ruleDescriptionsOut(i));
end loop;
--
ame_api2.getNextApprovers4(applicationIdIn => fndApplicationId,
transactionTypeIn => transactionTypeId,
transactionIdIn => transactionId,
flagApproversAsNotifiedIn => flagApproversAsNotified,
approvalProcessCompleteYNOut => approvalProcessCompleteYNOut,
nextApproversOut => approvers);
if approvalProcessCompleteYNOut = ame_util.booleanTrue then
dbms_output.put_line ('Approval Process Completed');
else
dbms_output.put_line ('Approval Process Not Yet Complete');
end if;
dbms_output.put_line ('Total Approvers Fetched = ' || approvers.count);
dbms_output.put_line (' ');
if approvers.count <> 0 then
printString := '';
printString := printString || rpad('Id',3,' ');
printString := printString || rpad('Approver',15,' ');
printString := printString || rpad('ORD',4,' ');
printString := printString || rpad('IC',10,' ');
printString := printString || rpad('IT',5,' ');
printString := printString || rpad('SL',3,' ');
printString := printString || rpad('ATID',7,' ');
printString := printString || rpad('GCID',7,' ');
printString := printString || rpad('Approval Status',20,' ');
printString := printString || rpad('Occ',4,' ');
printString := printString || rpad('MO',2,' ');
dbms_output.put_line('-------------------------------------------------------------------------------');
dbms_output.put_line(printString);
dbms_output.put_line('-------------------------------------------------------------------------------');
for i in 1 .. approvers.count loop
printString := '';
printString := printString || rpad(to_char(i),3,' ');
printString := printString || rpad(approvers(i).name,15,' ');
printString := printString || rpad(approvers(i).approver_order_number,4,' ');
printString := printString || rpad(nvl(approvers(i).item_class,'null'),10,' ');
printString := printString || rpad(nvl(approvers(i).item_id,'null'),5,' ');
printString := printString || rpad(approvers(i).authority,3,' ');
printString := printString || rpad(approvers(i).action_type_id,7,' ');
printString := printString || rpad(approvers(i).group_or_chain_id,7,' ');
printString := printString || rpad(nvl(approvers(i).approval_status,'null'),20,' ');
printString := printString || rpad(approvers(i).occurrence,4,' ');
printString := printString || rpad(approvers(i).member_order_number,2,' ');
dbms_output.put_line(printString);
end loop;
end if;
end;
/

Compile Jsp files in OAF

Command:

Steps to follow
Copy .jsp file to $HTML_TOP
export PATH=$PATH:$FND_TOP/patch/115/bin
ojspCompile.pl --compile -s 'Test.jsp' --flush

Kill the locked Sessions

Query1:

select     oracle_username || ' (' || s.osuser || ')' username
,  s.sid || ',' || s.serial# sess_id
,  owner || '.' || object_name object
,  object_type
,  decode( l.block
,       0, 'Not Blocking'
,       1, 'Blocking'
,       2, 'Global') status
,  decode(v.locked_mode
,       0, 'None'
,       1, 'Null'
,       2, 'Row-S (SS)'
,       3, 'Row-X (SX)'
,       4, 'Share'
,       5, 'S/Row-X (SSX)'
,       6, 'Exclusive', TO_CHAR(lmode)) mode_held
from       v$locked_object v
,  dba_objects d
,  v$lock l
,  v$session s
where      v.object_id = d.object_id
and        v.object_id = l.id1
and        v.session_id = s.sid
order by oracle_username
,  session_id

Query2:

alter system kill session 'sessionId1,sessionId2';
commit;

Read Transaction Document from HR_API_TRANSACTIONS CLOB Data

Useful Queries

-----------------

Query 1:
SELECT extractvalue(VALUE(xx_row), '/ObjectiveEORow/Name') AS objective_name,
       extractvalue(VALUE(xx_row), '/ObjectiveEORow/StartDate') AS objective_start_date,
       decode(extractvalue(VALUE(xx_row),
                           '/ObjectiveEORow/WeightingPercent'),
              '(null)',
              0,
              '',
              0,
              extractvalue(VALUE(xx_row), '/ObjectiveEORow/WeightingPercent')) AS weightingpercent,
       extractvalue(VALUE(xx_row), '/ObjectiveEORow/GroupCode') AS groupcode,
       decode(extractvalue(VALUE(xx_row), '/ObjectiveEORow/ObjectiveId'),
              '(null)',
              0,
              extractvalue(VALUE(xx_row), '/PerAbsenceAttendancesEORow/AbsenceAttendanceId')) AS objectiveid
 
 
  SELECT  extractvalue(VALUE(xx_row), '/CNode/AbsenceAction') AS objective_name
  FROM HR_API_TRANSACTIONS xx_api,
       TABLE(xmlsequence(extract(xmlparse(document transaction_document
                                          wellformed),
                                 '/Transaction/TransCtx/CNode'))) xx_row
 WHERE xx_api.transaction_ref_id = *** Transaction id ********
------------------------------------------------------------------------------------------------
Query2: Leave Absence Cancelation employees

  SELECT selected_person_id, extractvalue(VALUE(xx_row), '/CNode/AbsenceAction') AS objective_name
  FROM HR_API_TRANSACTIONS xx_api,
       TABLE(xmlsequence(extract(xmlparse(document transaction_document
                                          wellformed),
                                 '/Transaction/TransCtx/CNode'))) xx_row
 WHERE extractvalue(VALUE(xx_row), '/CNode/AbsenceAction') = 'CancelMode'
 and TRANSACTION_REF_TABLE like 'PER_ABSENCE_ATTENDANCES'
 and trunc(creation_date) = trunc(sysdate)