Oracle Forms Book Reference

go through this link..


https://books.google.co.in/books?id=j8OnBQAAQBAJ&pg=PA7&lpg=PA7&dq=post+query+code+in+oracle+forms&source=bl&ots=dBONFmoka2&sig=FgDc0KdhTSF07rvj6444QuuIHD0&hl=en&sa=X&ved=0CD4Q6AEwBWoVChMI3NrqvbijyAIVxJGOCh1KSwNg#v=onepage&q=WHEN-NEW-FORM-INSTANCE&f=false



to move loop:

WHEN-NEW-FORM-INSTANCE trigger in oracle forms

WHEN-NEW-FORM-INSTANCE trigger will work when you are opening form this will fetch all records available in your table and fetch into your block.

go_block('XXCAD_SR_REFUND_NOTIFICATIONS');
 if :XXCAD_SR_REFUND_NOTIFICATIONS.task_status = 'Open'
  then
  do_key('execute_query');
   else
  fnd_message.set_string ('There is no Open SRs in your Bin');
  fnd_message.show;
 end if;

sql query to know the Environment path in Linux in oracle Apps Technical

select variable_name, value
from fnd_env_context
where variable_name like '%\_TOP' escape '\'
and concurrent_process_id = (select max(concurrent_process_id) from fnd_env_context)
order by 1;

through sql Mask a Account Number(XXX5503) in bank account number in oracle apps 11i

select concat (substr(account_number,0,0),lpad(substr(account_number,-4), Length(account_number)-0,'X')) acc_num FROM hz_cust_accounts
                  WHERE cust_account_id = 3078 -- (cust_id)

sql query Reset password for Oracle Apps User in oracle apps 11i

DECLARE
   v_user_name      VARCHAR2 (30) := UPPER ('A1199024');
   v_new_password   VARCHAR2 (30) := 'welcome2airtel';
   v_status         BOOLEAN;
BEGIN
   v_status :=
      fnd_user_pkg.changepassword (username         => v_user_name,
                                   newpassword      => v_new_password
                                  );

   IF v_status = TRUE
   THEN
      DBMS_OUTPUT.put_line
                          (   'The password reset successfully for the User:'
                           || v_user_name
                          );
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line (   'Unable to reset password due to'
                            || SQLCODE
                            || ' '
                            || SUBSTR (SQLERRM, 1, 100)
                           );
      ROLLBACK;
   END IF;
END;

CUSTOMER ADDRESS AND PARTY Details in Oracle CRM

SELECT   csi.incident_number,
        address1,
        address2,
        address3,
        address4,
         csi.customer_id
  FROM   hz_parties hr, cs_incidents_all_b csi
WHERE   NVL (LTRIM (RTRIM (hr.address1)), 'Y') <>'Y'
         AND NVL (LTRIM (RTRIM (hr.address2)), 'Y')<>'Y'
         AND NVL (LTRIM (RTRIM (hr.address3)), 'Y')<>'Y'
         AND NVL (LTRIM (RTRIM (hr.address4)), 'Y')<>'Y'
         /*hr.address1 is not null
         and hr.address2 is not null
        and hr.address3 is not null
         and hr.address4 is not null*/
         AND csi.customer_id = hr.party_id
         AND ROWNUM <5

Query for Responsibility and Functions

Query for Responsibility and Functions
==============================
SELECT FRTL.RESPONSIBILITY_NAME, FFL.USER_FUNCTION_NAME, FFF.FUNCTION_NAME,ft.RESPONSIBILITY_NAME FROM FND_USER_RESP_GROUPS FURG, fnd_responsibility_tl ft , FND_RESPONSIBILITY FR, FND_COMPILED_MENU_FUNCTIONS FCMF, FND_FORM_FUNCTIONS FFF, FND_RESPONSIBILITY_TL FRTL, FND_FORM_FUNCTIONS_TL FFL WHERE FURG.RESPONSIBILITY_ID = FR.RESPONSIBILITY_ID and ft.RESPONSIBILITY_ID(+)= fr.RESPONSIBILITY_ID AND FURG.RESPONSIBILITY_APPLICATION_ID = FR.APPLICATION_ID AND FR.MENU_ID = FCMF.MENU_ID AND FCMF.GRANT_FLAG = 'Y' AND FCMF.FUNCTION_ID = FFF.FUNCTION_ID AND SYSDATE BETWEEN FR.START_DATE AND NVL(FR.END_DATE, SYSDATE+1) and fr.CREATION_DATE >= to_date('01-jan-2005','dd-mon-yyyy') AND FURG.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID AND FR.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID AND FRTL.LANGUAGE = 'US' AND FFL.LANGUAGE = 'US' AND FFF.FUNCTION_ID = FFL.FUNCTION_ID AND (FURG.END_DATE > SYSDATE OR FURG.END_DATE IS NULL) AND FFF.FUNCTION_NAME NOT IN ( SELECT FF.FUNCTION_NAME FROM FND_RESPONSIBILITY R, FND_USER_RESP_GROUPS RG , FND_RESP_FUNCTIONS RF, FND_FORM_FUNCTIONS FF, FND_RESPONSIBILITY_TL FRTL WHERE RG.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND RF.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND RF.RULE_TYPE = 'F' AND FF.FUNCTION_ID = RF.ACTION_ID AND FRTL.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID AND FRTL.RESPONSIBILITY_ID = RG.RESPONSIBILITY_ID AND FRTL.LANGUAGE = 'US' )