Quick Queries

--Finding Responsibilities of a Program SELECT fcp.concurrent_program_name, fcpt.user_concurrent_program_name, fcp.concurrent_program_id, frt.responsibility_name, REQUEST_GROUP_NAME FROM fnd_concurrent_programs fcp, fnd_concurrent_programs_tl fcpt, fnd_request_groups frg, fnd_request_group_units frgu, fnd_responsibility fr, fnd_responsibility_tl frt WHERE USER_CONCURRENT_PROGRAM_NAME LIKE :p_conc_program_name AND fcp.concurrent_program_id = fcpt.concurrent_program_id AND fr.responsibility_id = frt.responsibility_id AND frgu.request_group_id = frg.request_group_id AND frgu.request_group_id = fr.request_group_id AND fcpt.concurrent_program_id = frgu.request_unit_id AND fcpt.language(+) = USERENV ('lang') AND frt.language(+) = USERENV ('lang');

--Create view for requests

create or replace view xxexpd_fnd_requests_v as select requestor, request_id, phase_code, status_code, request_date, actual_start_date, actual_completion_date, program, argument_text, program_short_name, user_concurrent_program_name, (select responsibility_name from fnd_responsibility_tl where responsibility_id = fr.responsibility_id) responsibility from fnd_conc_req_summary_v fr where requestor = 'RANREDDY' order by 2 desc; select (select RESPONSIBILITY_NAME from fnd_responsibility_tl where responsibility_id = a.responsibility_id )RESPONSIBILITY_NAME, request_id, priority_request_id, parent_request_id, requestor,user_concurrent_program_name , phase_Code,status_code ,actual_start_date, actual_completion_date, argument_text from fnd_conc_req_summary_v a where --requestor = 'RANREDDY' --request_id = 87593756 user_concurrent_program_name = 'EXPD Generic Send Email Program' order by 2 desc exec AD_ZD.grant_privs('SELECT', 'XXEXPD_TCA_ADDITIONAL_INFO', 'APPS_READ_ONLY_EXPD'); update ra_customer_trx_all set printing_pending = 'Y' where trx_number = '1921431391'; select *from hz_cust_accounts where status = 'A' and customer_class_code = 'HOTEL' and attribute2 = 'DIR' and account_name like '%Hotel Collect' BEGIN MO_GLOBAL.SET_POLICY_CONTEXT('S',304); END; select rcta.trx_number,rctla.unit_selling_price,rctla.line_number,rct.type from ra_customer_trx_lines_all rctla ,ra_customer_trx_all rcta ,ra_cust_trx_types_all rct ,ar_payment_schedules_all aps where rctla.attribute12 = '1309503170' and rcta.customer_trx_id = rctla.customer_trx_id and rcta.cust_trx_type_id = rct.cust_trx_type_id and rcta.customer_trx_id = aps.customer_trx_id and rcta.org_id = aps.org_id ORDER BY rcta.term_due_date

--Stetting a New Password from Back end

----------------------------------------

SET SERVEROUTPUT ON;

DECLARE

    v_user_name      VARCHAR2 (30) := UPPER ('USERNAME');

    v_new_password   VARCHAR2 (30) := 'oracle123';

    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;

/

--Adding the Sysadmin responsiblity DECLARE v_user_id NUMBER; BEGIN select user_id INTO v_user_id from fnd_user where user_name = 'RANREDDY'; fnd_user_resp_groups_api.insert_assignment ( user_id => v_user_id, responsibility_id => 20420, responsibility_application_id => 1, start_date => SYSDATE, end_date => NULL, description => NULL); COMMIT; END; /