Add New Responsibility from Back End

Run the below query giving the user name and responsibility needs to be added.,


DECLARE
   v_user_name                  VARCHAR2 (100) :='xyz.abc';
   v_responsibility_name  VARCHAR2 (100) := 'System Administrator';
   v_application_name      VARCHAR2 (100) := NULL;
   v_responsibility_key      VARCHAR2 (100) := NULL;
   v_security_group           VARCHAR2 (100) := NULL;
   v_description                  VARCHAR2 (100) := NULL;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,                  
                 frt.description
      INTO  v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
    WHERE fr.application_id = fa.application_id
      AND    fr.data_group_id = frg.security_group_id
      AND    fr.responsibility_id = frt.responsibility_id
     -- AND    frt.LANGUAGE = USERENV ('LANG')
      AND    upper(frt.responsibility_name) = upper(v_responsibility_name);

   fnd_user_pkg.addresp (username          => v_user_name,
                                              resp_app           => v_application_name,
                                              resp_key            => v_responsibility_key,
                                              security_group => v_security_group,
                                              description        => v_description,
                                              start_date          => SYSDATE,
                                              end_date            => NULL
                                             );
   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '
                         || v_responsibility_name
                         || ' is attached to the user '
                         || v_user_name
                         || ' Successfully'
                        );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line
         (   'Error encountered while attaching responsibilty to the user and the error is '
          || SQLERRM
         );
END;

For Checking query as below.,

SELECT  fu.user_name,
               frt.responsibility_name,fu.employee_id
  FROM fnd_user_resp_groups furg,
              fnd_user fu,
              fnd_responsibility_tl frt
 WHERE furg.user_id = fu.user_id
   AND    furg.responsibility_id = frt.responsibility_id
   AND    fu.user_name = 'xyz.abc'

Delete Element and its link to Assignment

declare
CURSOR   rec_check is
select  peef.ELEMENT_ENTRY_ID,peef.EFFECTIVE_START_DATE,peef.OBJECT_VERSION_NUMBER,paaf.ASSIGNMENT_NUMBER
 from
pay_element_entries_f peef,
pay_element_types_f petf,
pay_element_links_f pelf,
per_all_assignments_f paaf
where petf.ELEMENT_TYPE_ID=pelf.ELEMENT_TYPE_ID
and peef.ELEMENT_LINK_ID=pelf.ELEMENT_LINK_ID
and peef.ELEMENT_TYPE_ID=petf.ELEMENT_TYPE_ID
and peef.ASSIGNMENT_ID=paaf.ASSIGNMENT_ID
and petf.ELEMENT_NAMe='Final Processing'
and  sysdate between paaf.effective_start_date and paaf.effective_end_date
and  sysdate between petf.effective_start_date and petf.effective_end_date
and  sysdate between peef.effective_start_date and peef.effective_end_date
and  sysdate between pelf.effective_start_date and pelf.effective_end_date;

v_object_version_number number:=1;
v_start_date date:=null;
v_end_date date:=null;
v_delete_wrg boolean ;
v_date date:=null;
begin
FOR cur in rec_check
loop
dbms_output.put_line(1);
begin
pay_element_entry_api.delete_element_entry
(p_validate=>false
,p_datetrack_delete_mode =>'ZAP'
,p_effective_date =>cur.EFFECTIVE_START_DATE---TO_DATE('22-06-2008','DD-MM-YYYY')--v_date
,p_element_entry_id =>cur.ELEMENT_ENTRY_ID
,p_object_version_number =>v_object_version_number
,p_effective_start_date =>v_start_date
,p_effective_end_date =>v_end_date
,p_delete_warning =>v_delete_wrg
);
dbms_output.put_line('Done');
EXCEPTION
when others then
dbms_output.put_line(sqlcode||sqlerrm||'Assignment'||cur.ASSIGNMENT_NUMBER);
end;
end loop;

end;


commit;

Get user and runtime

TO get user submitted the report and run time with date:

In before parameter trigger

Define two variables:
xuser varchar2(30);
xrundate varchar2(30);

begin
   

    select REQUESTOR,to_char(request_date,'DD-Mon-YY HH24:MI')|| 'Hrs' into xuser,xrundate from   FND_CONC_REQ_SUMMARY_V where REQUEST_ID =  :P_CONC_REQUEST_ID;
:cp_user := nvL(xuser,'');
:cp_rundate := xrundate; 

end;

Pick frieght charges OM

select
HEADER_ID ,
LINE_ID ,
CHARGE_ID ,
CHARGE_NAME ,
CHARGE_AMOUNT
from OE_CHARGE_LINES_V
where header_id=(select header_id from oe_order_headers_all)

On order Quantity

SELECT sum(pl.quantity)
            FROM po_line_locations_all pll,
                 po_lines_all pl,
                 po_headers_all ph
           WHERE pl.po_header_id = ph.po_header_id
             AND pll.po_line_id = pl.po_line_id
             AND msi.organization_id = pll.ship_to_organization_id
             AND msi.inventory_item_id = pl.item_id