Correct way to read day from date in oracle

(TRIM ( TO_CHAR (p_date_end, 'DAY', 'NLS_DATE_LANGUAGE=English')) NOT IN ('FRIDAY', 'SATURDAY'))

query to get BI discoverer reports assigned to a user in R12

/* query to get BI discoverer reports assigned to a user in R12*/ SELECT distinct doc_name, folder_name, qs_doc_owner, owner_full_name, owner_email, report_user_name, report_user_email/*, MAX (qs_created_date)*//*,qs_created_by,user_id*/ FROM (SELECT doc.doc_name, obj.obj_name folder_name, bas.ba_name, qpp.qs_doc_owner, fu.description report_user_name, fu.email_address report_user_email, fu.user_name, /*MAX (qpp.qs_created_date) qs_created_date,*/ fu.end_date, (SELECT email_address FROM apps.fnd_user WHERE user_name = qpp.qs_doc_owner) owner_full_name, (SELECT description FROM apps.fnd_user WHERE user_name = qpp.qs_doc_owner) owner_email,qpp.qs_created_by,fu.user_id FROM eul4_us.eul5_documents doc, eul4_us.eul5_elem_xrefs xref, eul4_us.eul5_expressions EXP, eul4_us.eul5_objs obj, eul4_us.eul5_ba_obj_links bol, eul4_us.eul5_bas bas, eul4_us.eul5_qpp_stats qpp, apps.fnd_user fu WHERE xref.ex_from_id = doc.doc_id AND xref.ex_to_id = EXP.exp_id AND obj.obj_id = EXP.it_obj_id AND bol.bol_obj_id = obj.obj_id AND bas.ba_id = bol.bol_ba_id AND doc.doc_name = qpp.qs_doc_name AND TRUNC (SUBSTR (qpp.qs_created_by, 2)) * 1 = fu.user_id AND (upper(fu.email_address) like upper('%Pius.Piyush%%') OR qpp.qs_doc_owner like '%18032%' ) /*AND bas.ba_name NOT IN ('Purchasing', 'Items', 'Work In Process', 'Order Entry and Shipping', 'Cost Management', 'Planning') AND bas.ba_name NOT LIKE 'Bills%Of%'*/ GROUP BY doc.doc_name, obj.obj_name, bas.ba_name, qpp.qs_doc_owner, fu.user_name, fu.end_date, fu.description, fu.email_address,qpp.qs_created_by,fu.user_id) GROUP BY doc_name, folder_name, qs_doc_owner, report_user_name, report_user_email, owner_full_name, owner_email,qs_created_by,user_id

jpx import command in oaf in linux For VO extension

jpx import command in oaf in linux For VO extension java oracle.jrad.tools.xml.importer.JPXImporter $JAVA_TOP/xxeaa/xxVoExtnsnAppraisal0.jpx -username apps -password appspswd -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= 10.999.51.999)(PORT=4444))(CONNECT_DATA=(SID=TSMOKE)))" ================== java oracle.jrad.tools.xml.importer.JPXImporter $JAVA_TOP/xxeaa/xxVoExtnsnAppraisal0.jpx -username apps -password appspswd -dbconnection "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= 10.999.51.999)(PORT=4444))(CONNECT_DATA=(SID=STMOKE)))" Imported document : /oracle/apps/per/selfservice/appraisals/server/customizations/site/0/AppraisalVO Import completed successfully []$

RDF Paper Layout output showing different language(Greek) than required American English

Way-1 The charset property against the field was getting set to an erroneous value, it should have been 31, but was getting set to 371. Unfortunately you can’t see charset directly in Oracle Reports. The solution was to change the report to a REX format using rwcon60 alter the charset property of the offending fields and then convert it back to an RDF. I have searched on My Oracle Support but can’t find the article again (and Oracle blew away most of my favourites when we went across to MOS L. ). If it’s any help here’s the UNIX script we developed to fix the rdf our server. All references to ppsnli you will need change to your server. You need to put the file name as a parameter to the script name. e.g. if you save the script as FIXRDF run it as FIXRDF ================================================================================================================================================= #!/bin/sh ORACLE_HOME=/ppsnli/product/806 . $ORACLE_HOME/PPSNLI_auohspsnl08.env LD_LIBRARY_PATH=$ORACLE_HOME/jre11813/lib/i686/native_threads:$ORACLE_HOME/lib:$LD_LIBRARY_PATH REPORTS60_TMP=/tmp rwcon60 batch=yes stype=rdffile source=$1.rdf dtype=rexfile dest=$1.rex overwrite=yes rm -f dummy2.rex sed -e ‘s/CHARSET = 0/CHARSET = 31/’ -e ‘s/CHARSET = 781/CHARSET = 31/’ -e ‘s/CHARSET = 178/CHARSET = 31/’ -e ‘s/CHARSET = 871/CHARSET = 31/’ < $1.rex > dummy2.rex rwcon60 batch=yes overwrite=yes stype=rexfile source=dummy2.rex dtype=rdffile dest=$1.rdf rm dummy2.rex ================================================================================================================================================ Run the shell script by below command on linux ./FIXRDF.sh $MCLCUS_TOP/reports/US/MCCOSDMINV Run the shell script by below command on solaris sh FIXRDF.sh $MCLCUS_TOP/reports/US/MCCOSDMINV =======================Solari============Version================================================================================================ #!/bin/sh ORACLE_HOME=/oraapp/EBSSMOKE/apps/apps_st/appl . $ORACLE_HOME/APPSEBSSMOKE_ebststcm02.env # LD_LIBRARY_PATH=$ORACLE_HOME/jre11813/lib/i686/native_threads:$ORACLE_HOME/lib:$LD_LIBRARY_PATH REPORTS60_TMP=/tmp rwconverter batch=yes stype=rdffile source=$1.rdf dtype=rexfile dest=$1.rex overwrite=yes rm -f dummy2.rex sed -e 's/CHARSET = 0/CHARSET = 31/' -e 's/CHARSET = 781/CHARSET = 31/' -e 's/CHARSET = 178/CHARSET = 31/' -e 's/CHARSET = 871/CHARSET = 31/' < $1.rex > dummy2.rex rwconverter batch=yes overwrite=yes stype=rexfile source=dummy2.rex dtype=rdffile dest=$1.rdf rm dummy2.rex ============================================================================================================================================== Way-2 Certain fonts chosen in Report Builder on Windows have no mapping in UNIX and end up being set to 'Symbol' for whatever reason. Point in case is Fixedsys on Windows which maps to 'Symbol' on UNIX. The quick fix is to add the following section into uifont.ali file located: $ORACLE_HOME/guicommon/tk/admin/uifont.ali # Added PDF section below to cater for symbol fonts [ PDF ] Symbol = Arial This will ensure that any time a font is not mapped to anything in UNIX it will at least generate a legible font (Arial or whatever you choose). Note, only one mapping assignment should be declared. Ref: Symbol Characters ("Greek") Instead Of Expected Characters Are Displayed in Reports or Reports Parameter Form ( Doc ID 300416.1 ) PDF Concurrent Output Displays Unexpected Font After Upgrade To R12 from 11i ( Doc ID 1321874.1 ) ========================================================================================================================================

attachment details of any entity

SELECT fad.document_id, fad.entity_name, fad.pk1_value, fds.media_id, fds.file_name FROM fnd_attached_documents fad, per_objectives peo, fnd_documents fds --, fnd_lobs flb ,FND_DOCUMENTS_LONG_TEXT, FND_DOCUMENTS_SHORT_TEXT,FND_DOCUMENTS_LONG_RAW WHERE fad.pk1_value = TO_CHAR (peo.objective_id) AND fad.entity_name = 'PER_OBJECTIVES' AND fad.document_id = fds.document_id -- AND fds.media_id = flb.file_id AND ( TO_CHAR (peo.start_date, 'YYYY') = '2020' OR TO_CHAR (peo.target_date, 'YYYY') = '2020' );

Run R12 FAST formula from backend (TOAD) to debug

declare l_formula_id ff_formulas_f.formula_id%TYPE; l_effective_start_date ff_formulas_f.effective_start_date%TYPE; l_formula_name ff_formulas_f.formula_name %TYPE; l_inputs ff_exec.inputs_t; l_outputs ff_exec.outputs_t; begin insert into fnd_sessions values(userenv('SESSIONID'),sysdate); select ffm.formula_id,ffm.effective_start_date ,ffm.FORMULA_NAME into l_formula_id,l_effective_start_date ,l_formula_name from ff_formulas_f ffm where ffm.FORMULA_NAME='NP_HOUSING_ALLOWANCE_FF'; -- select * from ff_formulas_f where FORMULA_NAME='NP_HOUSING_ALLOWANCE_FF' dbms_output.put_line(' l_formula_name - l_formula_id - l_effective_start_date :=> '||l_formula_name||' - '||l_formula_id||' - ' ||to_char(l_effective_start_date,'DD-MON-YYYY')); ff_exec.init_formula (l_formula_id, l_effective_start_date, l_inputs, l_outputs ); -- -- Loop through the Input Values -- FOR i IN l_inputs.FIRST .. l_inputs.LAST LOOP if l_inputs(i).name='BUSINESS_GROUP_ID' then l_inputs(i).value := 81; end if; if l_inputs(i).name='ASSIGNMENT_ID' then l_inputs(i).value := 64; end if; if l_inputs(i).name='DATE_EARNED' then l_inputs(i).value :=fnd_date.date_to_canonical(trunc(to_date('31-DEC-2020'))); end if; if l_inputs(i).name='PAYROLL_ACTION_ID' then l_inputs(i).value := 166992; end if; /* if l_inputs(l_in_cnt).name='BUSINESS_GROUP_ID' then l_inputs(l_in_cnt).value := P_BUSINESS_GROUP_ID; end if; if l_inputs(l_in_cnt).name='BUSINESS_GROUP_ID' then l_inputs(l_in_cnt).value := P_BUSINESS_GROUP_ID; end if; */ -- -- Pass The each Input value name and its Value : Eg: START_DATE and p_start_date -- dbms_output.put_line('l_inputs-Name : ' ||l_inputs (i).NAME ); END LOOP; -- -- Run the formula -- ff_exec.run_formula (l_inputs , l_outputs ); FOR j IN l_outputs.FIRST .. l_outputs.LAST LOOP dbms_output.put_line('j-Name : ' ||l_outputs(j).NAME ); dbms_output.put_line('j-Value : ' ||l_outputs(j).VALUE ); END LOOP; exception when others then dbms_output.put_line('MainException : ' ||SQLERRM); end;

LOV Events in OAF

LOV Events in OAF There are some scenarios where we need to take some action on the basis of LOV, LOV fires three events: 1) lovUpdate : when user selects a value from lov. 2) lovValidate: when user enters a valid value in the lov input and tabs out then lov doesn't get pop-up. In this case lovValidate event is fired. 3) lovPrepare: when user clicks on torch icon: "lovPrepare" event is fired. //If we want to check which LOV has fired the event we can check it like this: {codecitation class="brush: java; gutter: true;" width="600px"} if (pageContext.isLovEvent()) { String lovInputSourceId = pageContext.getLovInputSourceId(); if ("myLovInput".equals(lovInputSourceId)) { am.invokeMethod("handleMyLovInputEvent"); } } // //Here is another sample code based on multiple LOV's {codecitation class="brush: java; gutter: true;" width="600px"} if (pageContext.isLovEvent()) { if ("Project".equals(lovInputSourceId)) { am.invokeMethod("clearChargeCode"); } if ("ChargeAccount".equals(lovInputSourceId)) { am.invokeMethod("clearTask"); } if ("Shipto".equals(lovInputSourceId)) { am.invokeMethod("clearAddress"); } }

Create Employee Salary Proposal record by hr_maintain_proposal_api.cre_or_upd_salary_proposal

DECLARE lb_inv_next_sal_date_warning BOOLEAN; lb_proposed_salary_warning BOOLEAN; lb_approved_warning BOOLEAN; lb_payroll_warning BOOLEAN; ln_pay_proposal_id NUMBER; ln_object_version_number NUMBER; BEGIN --2443(AN00004) -- Create or Upadte Employee Salary Proposal -- ---------------------------------------------------------------- hr_maintain_proposal_api.cre_or_upd_salary_proposal ( -- Input data elements -- ------------------------------ p_business_group_id => 81,--fnd_profile.value('PER_BUSINESS_GROUP_ID'), p_assignment_id => 2443, p_change_date => trunc(TO_DATE('01-AUG-2020')), p_proposed_salary_n => 1800, p_approved => 'Y', -- Output data elements -- -------------------------------- p_pay_proposal_id => ln_pay_proposal_id, p_object_version_number => ln_object_version_number, p_inv_next_sal_date_warning => lb_inv_next_sal_date_warning, p_proposed_salary_warning => lb_proposed_salary_warning, p_approved_warning => lb_approved_warning, p_payroll_warning => lb_payroll_warning ); COMMIT; dbms_output.put_line('ln_pay_proposal_id -> '||ln_pay_proposal_id); dbms_output.put_line('ln_object_version_number -> '||ln_object_version_number); EXCEPTION WHEN OTHERS THEN ROLLBACK; dbms_output.put_line(SQLERRM); END; /

AME Related Useful SQL Queries

/*GET AME Trx Type's Rules, conditions*/ select apl.APPLICATION_NAME Transaction_type, r.description Rule,r.rule_id,r.start_date,r.end_date ,condition Conditions from (select distinct rule_id, description,start_date,end_date from ame_rules where sysdate between start_date and end_date) r, (select distinct rule_id, LISTAGG (ame_utility_pkg.get_condition_description (acu.condition_id), chr(10)) WITHIN GROUP (order by acu.condition_id) OVER (partition by acu.rule_id) condition from ame_condition_usages acu) cu, ame_rule_usages aru, (select APPLICATION_NAME, application_id from ame_calling_apps_vl acav where application_name = 'Payables Invoice Approval' and rownum = 1) apl where r.rule_id = cu.rule_id --and r.rule_id = 20004 and aru.rule_id = r.rule_id and aru.item_id = apl.application_id order by r.description

How To Run Concurrent Program From Back end (PLSQL) and notify to USER by FND_REQUEST.ADD_NOTIFICATION

/*How To Run Concurrent Program From Back end (PLSQL) and notify to USER by  FND_REQUEST.ADD_NOTIFICATION*/

PROCEDURE submit_appl_host_prgm_p (
   errbuf         OUT          VARCHAR2,
   retcode        OUT          NUMBER,
   p_request_id   OUT NOCOPY   NUMBER
)
IS
   lv_request_id    NUMBER := 0;
   l_user_id        NUMBER;
   l_resp_id        NUMBER;
   l_resp_appl_id   NUMBER;
   l_notify_user  boolean;
BEGIN
   xxpk_debug_prc ('xxeaa_qnb_host2host_pkg.submit_appl_host_prgm_p',
                   'Enterred',
                   '00',
                   NULL,
                   SUBSTR (SQLERRM, 1, 299)
                  );

   SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
     INTO l_user_id, l_resp_id, l_resp_appl_id
     FROM fnd_user fnd, fnd_responsibility_tl fresp
    WHERE fnd.user_name = 'MANNAI.TECH'                             --SYSADMIN
      AND fresp.responsibility_name = 'Application Developer';
                               --Check Before MTP Valid Resposibility and User

   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => l_resp_id,
                               resp_appl_id      => l_resp_appl_id
                              );
  l_notify_user := FND_REQUEST.ADD_NOTIFICATION ('MANNAI.TECH');                           
   lv_request_id :=
      fnd_request.submit_request (application      => 'SQLAP',
                                  program          => 'XXEAA_SEND_PAYFILE2QNB',
                                  description      => NULL,
                                  start_time       => NULL,
                                  sub_request      => FALSE,
                                  argument1        => 'BLANK',
                                  argument2        => CHR (0),
                                  argument3        => CHR (0),
                                  argument4        => CHR (0),
                                  argument5        => CHR (0),
                                  argument6        => CHR (0),
                                  argument7        => CHR (0),
                                  argument8        => CHR (0),
                                  argument9        => CHR (0),
                                  argument10       => CHR (0),
                                  argument11       => CHR (0),
                                  argument12       => CHR (0),
                                  argument13       => CHR (0),
                                  argument14       => CHR (0),
                                  argument15       => CHR (0),
                                  argument16       => CHR (0),
                                  argument17       => CHR (0),
                                  argument18       => CHR (0),
                                  argument19       => CHR (0),
                                  argument20       => CHR (0),
                                  argument21       => CHR (0),
                                  argument22       => CHR (0),
                                  argument23       => CHR (0),
                                  argument24       => CHR (0),
                                  argument25       => CHR (0),
                                  argument26       => CHR (0),
                                  argument27       => CHR (0),
                                  argument28       => CHR (0),
                                  argument29       => CHR (0),
                                  argument30       => CHR (0),
                                  argument31       => CHR (0),
                                  argument32       => CHR (0),
                                  argument33       => CHR (0),
                                  argument34       => CHR (0),
                                  argument35       => CHR (0),
                                  argument36       => CHR (0),
                                  argument37       => CHR (0),
                                  argument38       => CHR (0),
                                  argument39       => CHR (0),
                                  argument40       => CHR (0),
                                  argument41       => CHR (0),
                                  argument42       => CHR (0),
                                  argument43       => CHR (0),
                                  argument44       => CHR (0),
                                  argument45       => CHR (0),
                                  argument46       => CHR (0),
                                  argument47       => CHR (0),
                                  argument48       => CHR (0),
                                  argument49       => CHR (0),
                                  argument50       => CHR (0),
                                  argument51       => CHR (0),
                                  argument52       => CHR (0),
                                  argument53       => CHR (0),
                                  argument54       => CHR (0),
                                  argument55       => CHR (0),
                                  argument56       => CHR (0),
                                  argument57       => CHR (0),
                                  argument58       => CHR (0),
                                  argument59       => CHR (0),
                                  argument60       => CHR (0),
                                  argument61       => CHR (0),
                                  argument62       => CHR (0),
                                  argument63       => CHR (0),
                                  argument64       => CHR (0),
                                  argument65       => CHR (0),
                                  argument66       => CHR (0),
                                  argument67       => CHR (0),
                                  argument68       => CHR (0),
                                  argument69       => CHR (0),
                                  argument70       => CHR (0),
                                  argument71       => CHR (0),
                                  argument72       => CHR (0),
                                  argument73       => CHR (0),
                                  argument74       => CHR (0),
                                  argument75       => CHR (0),
                                  argument76       => CHR (0),
                                  argument77       => CHR (0),
                                  argument78       => CHR (0),
                                  argument79       => CHR (0),
                                  argument80       => CHR (0),
                                  argument81       => CHR (0),
                                  argument82       => CHR (0),
                                  argument83       => CHR (0),
                                  argument84       => CHR (0),
                                  argument85       => CHR (0),
                                  argument86       => CHR (0),
                                  argument87       => CHR (0),
                                  argument88       => CHR (0),
                                  argument89       => CHR (0),
                                  argument90       => CHR (0),
                                  argument91       => CHR (0),
                                  argument92       => CHR (0),
                                  argument93       => CHR (0),
                                  argument94       => CHR (0),
                                  argument95       => CHR (0),
                                  argument96       => CHR (0),
                                  argument97       => CHR (0),
                                  argument98       => CHR (0),
                                  argument99       => CHR (0),
                                  argument100      => CHR (0)
                                 );
   COMMIT;
    xxpk_debug_prc ('xxeaa_qnb_host2host_pkg.submit_appl_host_prgm_p',
                      'lv_request_id -> '||to_char(lv_request_id),
                      '01',
                      NULL,
                      SUBSTR (SQLERRM, 1, 299)
                     );
   p_request_id := lv_request_id;
EXCEPTION
   WHEN OTHERS
   THEN
      xxpk_debug_prc ('xxeaa_qnb_host2host_pkg.submit_appl_host_prgm_p',
                      'Main Exception',
                      'ZZ',
                      NULL,
                      SUBSTR (SQLERRM, 1, 299)
                     );
      p_request_id := 0;
END submit_appl_host_prgm_p;

Some Imp Table

1) GL_AUTHORIZATION_LIMITS--GL Approval Limits

AR Invoice distribution line update API

/*AR Invoice distribution's Revenue Account Update API*/
/* Formatted on 2020/07/19 19:54 (Formatter Plus v4.8.8) */
DECLARE
   l_backout_flag               BOOLEAN;
   lout_backout_done_flag       BOOLEAN;
   p_cust_trx_line_gl_dist_id   NUMBER;
   l_customer_trx_id            NUMBER;
   l_header_gl_date             DATE;
   l_trx_date                   DATE;
   l_invoicing_rule_id          NUMBER;
   l_user_id                    NUMBER;
   l_resp_id                    NUMBER;
   l_resp_appl_id               NUMBER;
   r_rctgld                     ra_cust_trx_line_gl_dist%ROWTYPE;
l_old_ccid number;  
l_new_required_ccid number;
   CURSOR c_dist_line
   IS
      SELECT rctgd.*
        FROM ra_cust_trx_line_gl_dist_all rctgd
       WHERE rctgd.cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id;
BEGIN
   BEGIN
      SELECT rctgd.created_by, rcta.customer_trx_id, rcta.trx_date,
             rctgd.gl_date, rcta.invoicing_rule_id
             ,rctgd.CODE_COMBINATION_ID
        INTO l_user_id, l_customer_trx_id, l_trx_date,
             l_header_gl_date, l_invoicing_rule_id,l_old_ccid
        FROM ra_cust_trx_line_gl_dist_all rctgd, ra_customer_trx_all rcta
       WHERE 1 = 1
         AND rctgd.customer_trx_id = rcta.customer_trx_id
         AND rctgd.cust_trx_line_gl_dist_id = p_cust_trx_line_gl_dist_id
         and rctgd.ACCOUNT_CLASS='REV' ;
   END;

   BEGIN
      SELECT fnd.user_id, fresp.responsibility_id, fresp.application_id
        INTO l_user_id, l_resp_id, l_resp_appl_id
        FROM fnd_user fnd,
             fnd_responsibility_tl fresp,
             fnd_user_resp_groups_direct furg
       WHERE 1 = 1
         AND furg.user_id = fnd.user_id
         AND furg.responsibility_id = fresp.responsibility_id
         AND fnd.user_id = l_user_id
         AND UPPER (fresp.responsibility_name) LIKE '%MTC%REC%USE%'
         AND TRUNC (SYSDATE) BETWEEN furg.start_date
                                 AND NVL (furg.end_date, TRUNC (SYSDATE + 2))
         AND furg.start_date IS NOT NULL
         AND ROWNUM < 2;
--select * from fnd_user_resp_groups_direct        furg where furg.START_DATE is null

   --select * from fnd_responsibility_tl fresp where fresp.responsibility_name  like 'MTC%Rec%Use%'
   END;
  


   fnd_global.apps_initialize (user_id           => l_user_id,
                               resp_id           => l_resp_id,
                               resp_appl_id      => l_resp_appl_id
                              );

   FOR i IN c_dist_line
   LOOP
   begin
  
   select gcc_all segments as itis and seg3 is required Account into l_new_required_ccid
   from gl_code_combinations gcc
   where gcc.CODE_COMBINATION_ID=l_old_ccid;
   end;
      r_rctgld := i.*;
      r_rctgld.CODE_COMBINATION_ID:=l_new_required_ccid--
      arp_process_dist.update_dist
         (p_form_name                     => 'ARXTWMAI',
          p_form_version                  => NULL,
          p_backout_flag                  => l_backout_flag,
                       --looks have the relation with posting , have to verify
          p_cust_trx_line_gl_dist_id      => p_cust_trx_line_gl_dist_id,
          p_customer_trx_id               => l_customer_trx_id,
          p_dist_rec                      => r_rctgld,
                             --IN OUT NOCOPY ra_cust_trx_line_gl_dist%rowtype,
          p_header_gl_date                => l_header_gl_date,
          p_trx_date                      => l_trx_date,
          p_invoicing_rule_id             => l_invoicing_rule_id,
          p_backout_done_flag             => lout_backout_done_flag,
          p_exchange_rate                 => NULL,
          p_currency_code                 => NULL,
          p_precision                     => NULL,
          p_mau                           => NULL
         );
      COMMIT;
   END LOOP;
END;

PMS Objectives Mandatory R12 User Hook Implementation Steps

PMS Objectives Mandatory R12 User Hook Implementation Steps

1) Create package with the required procedure XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B
--The parameters must be same with same sequence (as it is with seeded hook API pkg.procedure
--here seeded pkg procedure is HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B)

2)--Get API_MODULE_ID
-- Choose the exact Module 
SELECT *
  FROM  hr_api_modules
WHERE  api_module_type = 'BP'
     AND  module_name LIKE '%UPDATE_APPRAISAL%'; --1297

3)-- get API_HOOK_ID
 -- Choose w.r.t. the exact API_Module_ID  

select * from  hr_api_hooks ahk where API_HOOK_TYPE='BP' AND API_MODULE_ID=1297--2925

4) --create_api_hook_call

DECLARE
   l_api_hook_call_id        NUMBER;
   l_object_version_number   NUMBER;
BEGIN
   hr_api_hook_call_api.create_api_hook_call (
      p_validate                => FALSE,
      p_effective_date          => TO_DATE (SYSDATE),
      p_api_hook_id             => 2925,                                                
      p_api_hook_call_type      => 'PP',--PKG-Procedure
      p_sequence                => 3000,--always keep this 3000
      p_enabled_flag            => 'Y',
      p_call_package            => 'XXEAA_PMS_USERHOOK_PKG',
      p_call_procedure          => 'XXUPDATE_APPRAISAL_B',             
      p_api_hook_call_id        => l_api_hook_call_id,
      p_object_version_number   => l_object_version_number);
      COMMIT;
     
      dbms_output.put_line('l_api_hook_call_id:-'||l_api_hook_call_id);
      dbms_output.put_line(' l_object_version_number:-'|| l_object_version_number);
END;
/* Output
l_api_hook_call_id:-1180
 l_object_version_number:-1
*/

5) --Check Wheather Attached Or Not:
--ENABLED_FLAG must be Y and Status =
--------------------------------------------------------------------------------

select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'

6)
-- Run Pre-Processor:
--------------------------------------------------------------------------------

DECLARE
BEGIN
   hr_api_user_hooks_utility.create_hooks_one_module(1297); -- (Pass Application Module ID )  API_MODULE_ID==1297
  dbms_output.put_line('Pre-Processor Run Successfully');
  commit;
   exception
   when others then
  dbms_output.put_line(sqlerrm);
END;

/*Output
Pre-Processor Run Successfully
*/


7)
--Check status
--Preproceddor_date must not be null after run pre-processor
--and Status must be V & ENCODED_ERROR must be null

select * from hr_api_hook_calls where call_package like '%XXEAA_PMS_USERHOOK_PKG%'
=================================================================
/* Formatted on 2020/07/14 23:35 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PACKAGE xxeaa_pms_userhook_pkg
IS
   PROCEDURE xxupdate_appraisal_b (
      p_effective_date                 IN   DATE,
      p_appraisal_id                   IN   NUMBER,
      p_object_version_number          IN   NUMBER,
      p_appraiser_person_id            IN   NUMBER,
      p_appraisal_date                 IN   DATE,
      p_appraisal_period_end_date      IN   DATE,
      p_appraisal_period_start_date    IN   DATE,
      p_type                           IN   VARCHAR2,
      p_next_appraisal_date            IN   DATE,
      p_status                         IN   VARCHAR2,
      p_comments                       IN   VARCHAR2,
      p_overall_performance_level_id   IN   NUMBER,
      p_open                           IN   VARCHAR2,
      p_attribute_category             IN   VARCHAR2,
      p_attribute1                     IN   VARCHAR2,
      p_attribute2                     IN   VARCHAR2,
      p_attribute3                     IN   VARCHAR2,
      p_attribute4                     IN   VARCHAR2,
      p_attribute5                     IN   VARCHAR2,
      p_attribute6                     IN   VARCHAR2,
      p_attribute7                     IN   VARCHAR2,
      p_attribute8                     IN   VARCHAR2,
      p_attribute9                     IN   VARCHAR2,
      p_attribute10                    IN   VARCHAR2,
      p_attribute11                    IN   VARCHAR2,
      p_attribute12                    IN   VARCHAR2,
      p_attribute13                    IN   VARCHAR2,
      p_attribute14                    IN   VARCHAR2,
      p_attribute15                    IN   VARCHAR2,
      p_attribute16                    IN   VARCHAR2,
      p_attribute17                    IN   VARCHAR2,
      p_attribute18                    IN   VARCHAR2,
      p_attribute19                    IN   VARCHAR2,
      p_attribute20                    IN   VARCHAR2,
      p_system_type                    IN   VARCHAR2,
      p_system_params                  IN   VARCHAR2,
      p_appraisee_access               IN   VARCHAR2,
      p_main_appraiser_id              IN   NUMBER,
      p_assignment_id                  IN   NUMBER,
      p_assignment_start_date          IN   DATE,
      p_asg_business_group_id          IN   NUMBER,
      p_assignment_organization_id     IN   NUMBER,
      p_assignment_job_id              IN   NUMBER,
      p_assignment_position_id         IN   NUMBER,
      p_assignment_grade_id            IN   NUMBER,
      p_appraisal_system_status        IN   VARCHAR2,
      p_potential_readiness_level      IN   VARCHAR2,
      p_potential_short_term_workopp   IN   VARCHAR2,
      p_potential_long_term_workopp    IN   VARCHAR2,
      p_potential_details              IN   VARCHAR2,
      p_event_id                       IN   NUMBER,
      p_show_competency_ratings        IN   VARCHAR2,
      p_show_objective_ratings         IN   VARCHAR2,
      p_show_questionnaire_info        IN   VARCHAR2,
      p_show_participant_details       IN   VARCHAR2,
      p_show_participant_ratings       IN   VARCHAR2,
      p_show_participant_names         IN   VARCHAR2,
      p_show_overall_ratings           IN   VARCHAR2,
      p_show_overall_comments          IN   VARCHAR2,
      p_update_appraisal               IN   VARCHAR2,
      p_provide_overall_feedback       IN   VARCHAR2,
      p_appraisee_comments             IN   VARCHAR2,
      p_plan_id                        IN   NUMBER,
      p_offline_status                 IN   VARCHAR2,
      p_retention_potential            IN   VARCHAR2,
      p_show_participant_comments      IN   VARCHAR2
   );
END xxeaa_pms_userhook_pkg;
/

CREATE OR REPLACE PACKAGE BODY xxeaa_pms_userhook_pkg
IS
   PROCEDURE xxupdate_appraisal_b
--HR_APPRAISALS_BK2.UPDATE_APPRAISAL_B (1297,2925)
                                 (
      p_effective_date                 IN   DATE,
      p_appraisal_id                   IN   NUMBER,
      p_object_version_number          IN   NUMBER,
      p_appraiser_person_id            IN   NUMBER,
      p_appraisal_date                 IN   DATE,
      p_appraisal_period_end_date      IN   DATE,
      p_appraisal_period_start_date    IN   DATE,
      p_type                           IN   VARCHAR2,
      p_next_appraisal_date            IN   DATE,
      p_status                         IN   VARCHAR2,
      p_comments                       IN   VARCHAR2,
      p_overall_performance_level_id   IN   NUMBER,
      p_open                           IN   VARCHAR2,
      p_attribute_category             IN   VARCHAR2,
      p_attribute1                     IN   VARCHAR2,
      p_attribute2                     IN   VARCHAR2,
      p_attribute3                     IN   VARCHAR2,
      p_attribute4                     IN   VARCHAR2,
      p_attribute5                     IN   VARCHAR2,
      p_attribute6                     IN   VARCHAR2,
      p_attribute7                     IN   VARCHAR2,
      p_attribute8                     IN   VARCHAR2,
      p_attribute9                     IN   VARCHAR2,
      p_attribute10                    IN   VARCHAR2,
      p_attribute11                    IN   VARCHAR2,
      p_attribute12                    IN   VARCHAR2,
      p_attribute13                    IN   VARCHAR2,
      p_attribute14                    IN   VARCHAR2,
      p_attribute15                    IN   VARCHAR2,
      p_attribute16                    IN   VARCHAR2,
      p_attribute17                    IN   VARCHAR2,
      p_attribute18                    IN   VARCHAR2,
      p_attribute19                    IN   VARCHAR2,
      p_attribute20                    IN   VARCHAR2,
      p_system_type                    IN   VARCHAR2,
      p_system_params                  IN   VARCHAR2,
      p_appraisee_access               IN   VARCHAR2,
      p_main_appraiser_id              IN   NUMBER,
      p_assignment_id                  IN   NUMBER,
      p_assignment_start_date          IN   DATE,
      p_asg_business_group_id          IN   NUMBER,
      p_assignment_organization_id     IN   NUMBER,
      p_assignment_job_id              IN   NUMBER,
      p_assignment_position_id         IN   NUMBER,
      p_assignment_grade_id            IN   NUMBER,
      p_appraisal_system_status        IN   VARCHAR2,
      p_potential_readiness_level      IN   VARCHAR2,
      p_potential_short_term_workopp   IN   VARCHAR2,
      p_potential_long_term_workopp    IN   VARCHAR2,
      p_potential_details              IN   VARCHAR2,
      p_event_id                       IN   NUMBER,
      p_show_competency_ratings        IN   VARCHAR2,
      p_show_objective_ratings         IN   VARCHAR2,
      p_show_questionnaire_info        IN   VARCHAR2,
      p_show_participant_details       IN   VARCHAR2,
      p_show_participant_ratings       IN   VARCHAR2,
      p_show_participant_names         IN   VARCHAR2,
      p_show_overall_ratings           IN   VARCHAR2,
      p_show_overall_comments          IN   VARCHAR2,
      p_update_appraisal               IN   VARCHAR2,
      p_provide_overall_feedback       IN   VARCHAR2,
      p_appraisee_comments             IN   VARCHAR2,
      p_plan_id                        IN   NUMBER,
      p_offline_status                 IN   VARCHAR2,
      p_retention_potential            IN   VARCHAR2,
      p_show_participant_comments      IN   VARCHAR2
   )
   IS
      lv_objectives_cnt   NUMBER := 0;
   BEGIN
      xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
                      'Enterred',
                      NULL,
                      '00',
                      SUBSTR (SQLERRM, 1, 399)
                     );

      BEGIN
         SELECT COUNT (1)
           INTO lv_objectives_cnt
           FROM per_objectives poe
          WHERE poe.appraisal_id = p_appraisal_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            lv_objectives_cnt := 0;
      END;

      xxpk_debug_prc ('XXEAA_PMS_USERHOOK_PKG.XXUPDATE_APPRAISAL_B',
                         'P_APPRAISAL_ID - P_ASSIGNMENT_ID :-> '
                      || TO_CHAR (p_appraisal_id)
                      || ' - '
                      || TO_CHAR (p_assignment_id),
                      'lv_objectives_cnt - ' || TO_CHAR (lv_objectives_cnt),
                      '01',
                      SUBSTR (SQLERRM, 1, 399)
                     );

      IF NVL (lv_objectives_cnt, 0) = 0
      THEN
         fnd_message.set_name ('PER', 'Objectives are mandatory !!');
         fnd_message.raise_error;
      END IF;
   END xxupdate_appraisal_b;
END xxeaa_pms_userhook_pkg;
/

Meaning of Statuses in HR_API_TRANSACTIONS Table

 Doc ID 438346.1
Here is a list of statuses and their meanings in hr_api_transactions table.

D - Deleted
C - Cancelled
E - Error
RI - Return for Correction
RIS - Return for Correction + Save For Later
S - Save for Later
W - Review page
Y - Pending Approval
YS - Pending Approval + Save for Later
AC - Approval Complete

create or get URL of concurrent program request output or log file by fnd_webfile

--SET SERVEROUTPUT ON
--It will work for Paper Layout RDF report for Sure
DECLARE
   l_request_id   NUMBER := 60277934;--:P_REQ_ID;                       -- The request id
   l_two_task     VARCHAR2 (256);
   l_gwyuid       VARCHAR2 (256);
   l_url          VARCHAR2 (1024);
BEGIN
   -- Get the value of the profile option named, Gateway User ID (GWYUID)
   --- l_gwyuid := fnd_profile.VALUE ('APPLSYSPUB/PUB');

   SELECT   profile_option_value
     INTO   l_gwyuid
     FROM   fnd_profile_options o, fnd_profile_option_values ov
    WHERE       profile_option_name = 'GWYUID'
            AND o.application_id = ov.application_id
            AND o.profile_option_id = ov.profile_option_id;


   -- Get the value of the profile option named, Two Task(TWO_TASK)

   SELECT   profile_option_value
     INTO   l_two_task
     FROM   fnd_profile_options o, fnd_profile_option_values ov
    WHERE       profile_option_name = 'TWO_TASK'
            AND o.application_id = ov.application_id
            AND o.profile_option_id = ov.profile_option_id;


   l_url :=
      fnd_webfile.get_url (file_type     => fnd_webfile.request_out, -- for out file
                           ID            => l_request_id,
                           gwyuid        => l_gwyuid,
                           two_task      => l_two_task,
                           expire_time   => 500-- minutes, security!.
                           );

   DBMS_OUTPUT.put_line (l_url);
END;
=========================================================================

--It will work for  RDF-XML-RTF Report for Sure
DECLARE
   l_outfile_name        fnd_conc_req_outputs.file_name%TYPE;
   l_outfile_node_name   fnd_conc_req_outputs.file_node_name%TYPE;
   l_mime_type           fnd_mime_types_vl.mime_type%TYPE;
   l_svc                 VARCHAR2 (100);
   l_id                  VARCHAR2 (100);
   l_base                VARCHAR2 (100);
   l_pos                 VARCHAR2 (100);
   url                   VARCHAR2 (100);
   l_request_id   NUMBER;
BEGIN
    xx_ff_calculation.xx_salary_slip_creation (:xx_payroll_header.month_year,
                                              :xx_payroll_header.person_id
                                               ,l_request_id
                                             );
   
   SELECT file_name, file_node_name
     INTO l_outfile_name, l_outfile_node_name
     FROM fnd_conc_req_outputs
    WHERE concurrent_request_id = l_request_id;

   SELECT mime_type
     INTO l_mime_type
     FROM fnd_mime_types_vl
    WHERE file_format_code = 'PDF';

   l_svc := fnd_conc_private_utils.get_fs_svc_name (l_outfile_node_name);
   l_id :=
      fnd_webfile.create_id (l_outfile_name,
                             l_svc,
                             10,
                             l_mime_type,
                             l_request_id,
                             'BINARY',
                             'Y'
                            );

   IF (l_id IS NULL)
   THEN
      fnd_message.retrieve;
      fnd_message.error;
   END IF;

   fnd_profile.get ('APPS_WEB_AGENT', l_base);
   l_pos := INSTR (l_base, '/', 1, 3);

   IF (l_pos > 0)
   THEN
      l_base := SUBSTR (l_base, 1, l_pos - 1);
   END IF;

   url := l_base || '/OA_CGI/FNDWRR.exe?' || 'temp_id=' || l_id;
   fnd_utilities.open_url (url);
END;

Oracle Workflow All Function Modes (funcmode)

/*Oracle Workflow All Function Modes (funcmode) */
   begin
---1
      if ( funcmode = 'RUN' ) then

           <your RUN executable statements>

           resultout := 'COMPLETE:<result>';

           return;

      end if;
---2
  if ( funcmode = 'CANCEL' ) then

           <your CANCEL executable statements>

           resultout := 'COMPLETE';

           return;

      end if;
---3
  if ( funcmode = 'SKIP' ) then

           <your SKIP executable statements>

           resultout := 'COMPLETE:<result>';

           return;

      end if;
---4
  if ( funcmode = 'RETRY' ) then

           <your RETRY executable statements>

           resultout := 'COMPLETE:<result>';

           return;

      end if;
---5
   if ( funcmode = 'VALIDATE' ) then

           <your VALIDATE executable statements>

           resultout := 'COMPLETE';

           return;

      end if;
---6
  if ( funcmode = 'RESPOND' ) then

           <your RESPOND executable statements>

           resultout := 'COMPLETE';

           return;

      end if;
---7
  if ( funcmode = 'FORWARD' ) then

           <your FORWARD executable statements>

           resultout := 'COMPLETE';

           return;

      end if;
---8
  if ( funcmode = 'TRANSFER' ) then

           <your TRANSFER executable statements>

           resultout := 'COMPLETE';

           return;

      end if;
---9
  if ( funcmode = 'QUESTION' ) then

           <your QUESTION executable statements>

           resultout := 'COMPLETE';

           return;

      end if;
--10
 if ( funcmode = 'ANSWER' ) then

           <your ANSWER executable statements>

           resultout := 'COMPLETE';

           return;

      end if;

---11
  if ( funcmode = 'TIMEOUT' ) then

           <your TIMEOUT executable statements>

           if (<condition_ok_to_proceed>) then

              resultout := 'COMPLETE';

           else

              resultout := wf_engine.eng_timedout;

           end if;

           return;

      end if;
--12 Other Developer Defined
  if ( funcmode = '<other funcmode>' ) then

           resultout := ' ';

           return;

      end if;
--Main exception
  exception

           when others then

            WF_CORE.CONTEXT ('<package name>', '<procedure name>', <itemtype>,

                            <itemkey>, to_char(<actid>), <funcmode>);

           raise;

  end <procedure name>;