Wednesday, January 1, 2014

Delete document attachments using API


Begin
l_entity_name FND_ATTACHED_DOCUMENTS.entity_name%type;
l_pk1_value FND_ATTACHED_DOCUMENTS.pk1_Value%type;
l_delete_document_flag varchar2 (1)   :=  'Y';    
l_user_id Number;
l_responsibility_id Number;
l_resp_application_id Number;
BEGIN

fnd_global.apps_initialize ( user_id          =>  l_user_id
                                      ,resp_id          =>  l_responsibility_id
                                      ,resp_appl_id  =>  l_resp_application_id);

fnd_attached_documents2_pkg.delete_attachments
( X_entity_name                 =>  l_entity_name
, X_pk1_value                    =>   l_pk1_value
, X_delete_document_flag   =>   l_delete_document_flag);

commit;
END;

SQL Query to list Active Responsibilities of a Active User

SQL Query to list Active Responsibilities of a Active User

SELECT fu.user_name,
       frv.responsibility_name,
      frv.responsibility_key,
      furgd.start_date,
       furgd.end_date
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND furgd.start_date                <= sysdate
AND coalesce(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND coalesce(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND coalesce(frv.end_date, sysdate + 1)   > sysdate;


Oracle SQL Query to list all Form Personalizations

Oracle SQL Query to list all Form Personalizations

SELECT ffv.form_id                 ,
  ffv.form_name                     ,
  ffv.user_form_name                ,
  ffv.description ,
  ffcr.SEQUENCE                     ,
  ffcr.description "Rule Name",
  ffcr.trigger_object,
  ffcr.condition
  From fnd_form_vl ffv,
              fnd_form_custom_rules ffcr
  Where ffv.form_name = ffcr.form_name
  Order By ffv.form_name, ffcr.SEQUENCE;