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