SQL - Assign Responsibility

--assign resp
--Find the RESPONSIBILITY_ID from RESPONSIBILITY_NAME:

select RESPONSIBILITY_ID,RESPONSIBILITY_NAME from FND_RESPONSIBILITY_VL where RESPONSIBILITY_NAME like 'Sys%Admin%';

--Verify whether the User is already having the responsibility assigned:
select USER_NAME,USER_ID from fnd_user where USER_NAME = 'JXGUPTA';
select USER_ID,RESPONSIBILITY_ID from FND_USER_RESP_GROUPS_DIRECT where USER_ID=52741 and RESPONSIBILITY_ID=20420;

--Assign the Responsibility Using the Following SQL:
;
DECLARE

  v_user_name  VARCHAR2(30) := 'JXGUPTA';
  Enter_Resp_Id  VARCHAR2(100) ;
  v_resp_appl  VARCHAR2(100);
  v_resp_key   VARCHAR2(100);
  v_appl_id    VARCHAR2(30);

BEGIN
select APPLICATION_ID ,RESPONSIBILITY_KEY
  into v_appl_id ,v_resp_key
  from FND_RESPONSIBILITY_VL
where RESPONSIBILITY_ID=20420 ;

select APPLICATION_SHORT_NAME
  into v_resp_appl
  from FND_APPLICATION_VL  
where upper(APPLICATION_ID) = v_appl_id;

  fnd_user_pkg.addresp(username       => v_user_name
                      ,resp_app       => v_resp_appl
                      ,resp_key       => v_resp_key
                      ,security_group => 'STANDARD'
                      ,description    => 'Auto Assignment'
                      ,start_date     => SYSDATE
                      ,end_date       => SYSDATE + 1000);
END;

SQL- reset password

you can use one of the 2 ways mentioned below :

1. select fnd_web_sec.change_password ('SCOTT', 'oracle99')  from dual;


2.
begin
apps.fnd_user_pkg.updateuser(
x_user_name => 'SCOTT'
, x_owner => 'SEED'
, x_unencrypted_password => 'welcome001'
, x_password_date => to_date('2','J'));
commit;
end;
/

HRMS wages query

SELECT papf.employee_number 
,papf.full_name 
,pj.NAME job 
,haou.NAME ORGANIZATION 
,ppp.proposed_salary_n salary 
FROM per_all_people_f papf 
,per_all_assignments_f paaf 
,per_jobs pj 
,hr_all_organization_units haou 
,per_position_definitions ppd 
,per_all_positions pap 
,per_pay_proposals ppp 
WHERE 1 = 1 
AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date 
AND papf.current_employee_flag = 'Y' 
AND papf.employee_number IS NOT NULL 
AND paaf.person_id = papf.person_id 
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date 
AND paaf.job_id = pj.job_id 
AND paaf.organization_id = haou.organization_id 
AND paaf.position_id = pap.position_id 
AND pap.position_definition_id = ppd.position_definition_id 
AND ppp.pay_proposal_id = (SELECT MAX (pay_proposal_id) 
                                                  FROM per_pay_proposals 
                                               WHERE assignment_id = paaf.assignment_id)

                          

Viewing Output/Log in Browser

Reports that produce an output other than a text file (for example HTML or PDF) are displayed in a browser. If you set the profile option VIEWER: TEXT to Browser, your text files are also displayed in a browser as opposed to the Report Viewer. VIEWER: TEXT may also be set to display your output in a text editor such as Microsoft Word, or in a spreadsheet such as Microsoft Excel.

You can use the Save As functionality of your browser to save an HTML report to your desktop. If your system supports multiple languages, you may have trouble with the saved output. Ask your system administrator for assistance with character set conversion from the server to your browser.

The name of the output/report file passed to the browser is created dynamically. The concurrent program name and the date on which the request was run are used to create the name of the file. The file extension is based on the Output Type" attribute of the concurrent program.

For example:

Request 1234 for the "Year End Exception Report" is run on September 27, 2001 and has an output file named o1234.out.

When you view this report online, 'Year End Exception Report_270901.html' is passed as the name of the file to the browser in the content header in addition to the mime-type. You can then save the file locally using this name.

How to Use E-Signature Support For PO Receipt (Doc ID 864598.1)

How to Use E-Signature Support For PO Receipt (Doc ID 864598.1)

here is not a standard approval workflow process for receiving items. Core 
Receiving does not have such functionality, it only checks that the originator 
of the document is valid.

You may nevertheless use the ERES E-Records and E-Signatures functionality 
in Purchasing (ie. Inspection, Transfer, Deliver) but you must implement Oracle 
Quality and set the profile option QA:PO Inspection to Oracle Quality in order 
to capture E-Records and E-Signatures in Oracle Purchasing. iSignatures enables 
users to upload any type of file to the database and to route it for approval using 
the ERES framework.

The only E-Record Enabled Events associated with Workflow Seeded Data 
will be: 

• Inspect Received Items (Receiving Quality Inspection)
• Transfer (Receiving Transfer with Quality Data Collection)
• Deliver to Inventory (Receiving Delivery with Quality Data Collection)

For more information, please refer to the guides below:

1. Implementing Oracle E-Records in Discrete Manufacturing Guide
a. Introduction>> Discrete Manufacturing Business Flows Using Oracle 
    E-Records>> Procure to Pay
b. Oracle E-Records Enabled Transactions Summary>> Oracle Purchasing

2. Oracle Quality User's Guide
a. E-records and E-Signatures for Oracle Purchasing

The following notes can be followed for more details on the setup:
Note 336709.1 Implementing E-Records and E-Signatures (11.5.10) in Discrete Manufacturing Suite
Note 864598.1 How to Use E-Signature Support For PO Receipt

Ref BUG:8654554 - ONDEMAND: ERECORD AND ESIGNATURE NOT WORK DURING DELIVER

Status of Closed on a task populate an end date automatically

    CURSOR c_task_details IS
      SELECT jt.task_id,
             jt.task_number,
             jt.object_version_number,
             jt.task_status_id,
             jt.owner_id,
             jt.customer_id,
             jt.last_update_date
      FROM jtf_tasks_b jt,
           jtf_task_statuses_tl ts
      WHERE jt.task_status_id = ts.task_status_id
      AND   ts.name = 'Closed'
      AND   jt.scheduled_end_date IS NULL
      AND   TRUNC(jt.last_update_date) = TRUNC(SYSDATE);
    --
  BEGIN
    --For Each Task
    FOR rec_task_details IN c_task_details
    LOOP
      x_total_count := x_total_count + 1;
      x_api_call_status := 'S';
      x_error_message   := NULL;
      x_temp_str        := NULL;
      --
      fnd_msg_pub.Delete_Msg;
      --
      --Call API to Update Schedule End Date to Sysdate for Today's Closed Task
      BEGIN
        jtf_tasks_pub.update_task(p_api_version => 1.0,
                                  p_object_version_number  => rec_task_details.object_version_number,
                                  p_task_id                => rec_task_details.task_id,
                                  p_task_number            => rec_task_details.task_number,
                                  p_task_status_id         => rec_task_details.task_status_id,
                                  p_owner_id               => rec_task_details.owner_id,
                                  p_customer_id            => rec_task_details.customer_id,
                                  p_scheduled_end_date     => rec_task_details.last_update_date,  --Updatable
                                  x_return_status          => x_o_return_status,
                                  x_msg_count              => x_o_msg_count,
                                  x_msg_data               => x_o_msg_data
                                  );

      EXCEPTION
        WHEN others THEN
          x_api_call_status := 'F';
          x_failure_count := x_failure_count + 1;
          x_error_message := '(Task Number = '||rec_task_details.task_number||')Unknown Error while getting Task Details : '||SUBSTR(SQLERRM,1,100);
          fnd_file.put_line(fnd_file.log, x_error_message);
          xxcnc_utilities_pkg.xxcnc_insert_error(rec_task_details.task_id,
                                                 'Calling API',
                                                 'JTF_TASKS_B',
                                                 'TASK_NUMBER',
                                                 x_error_message);
      END;
      --
      IF x_api_call_status <> 'F' THEN
        --Check Status
        IF x_o_return_status <> FND_API.G_RET_STS_SUCCESS THEN
          x_failure_count := x_failure_count + 1;
          --
          x_o_msg_count           := FND_MSG_PUB.COUNT_MSG;
          --Check Message Count
          IF x_o_msg_count > 0 THEN
            x_temp_str      := SUBSTR(FND_MSG_PUB.GET(FND_MSG_PUB.G_FIRST,FND_API.G_FALSE),1,512);
            x_error_message := x_temp_str;
            --
            FOR i in 1..(x_o_msg_count -1)
            LOOP
              x_temp_str      := SUBSTR(FND_MSG_PUB.GET(FND_MSG_PUB.G_NEXT, FND_API.G_FALSE),1,512);
              x_error_message := x_error_message||' '||x_temp_str;
            END LOOP;
          END IF;
          x_error_message := '(Task Number = '||rec_task_details.task_number||')'||x_error_message;
          fnd_file.put_line(fnd_file.log, x_error_message);
          --Log appropriate Error Message
          xxcnc_utilities_pkg.xxcnc_insert_error(rec_task_details.task_id,
                                                 'Return Status = F',
                                                 'JTF_TASKS_B',
                                                 'TASK_NUMBER',
                                                 x_error_message);
          --
        ELSE  --Success
          x_success_count := x_success_count + 1;
        END IF;
      END IF;
      --
    END LOOP;
    COMMIT;
 

SQL - Find All DFFs

select df.application_table_name,

    dft.title

    ,dft.last_update_date dff_update

    --,df.last_update_date 

    ,df.descriptive_flexfield_name

    ,dfc.descriptive_flex_context_code

    ,dfc.last_update_date context_update

    ,dfcu.end_user_column_name

    ,dfcu.last_update_date column_update

    ,dfcut.form_above_prompt

from fnd_descriptive_flexs df

    ,fnd_descriptive_flexs_tl dft

    ,fnd_descr_flex_contexts dfc

    ,fnd_descr_flex_column_usages dfcu

    ,fnd_descr_flex_col_usage_tl dfcut

where 1=1

  --and df.application_table_name like 'OE_ORDER_LIN%'

  and dft.title = 'Items'

  --and df.descriptive_flexfield_name like 'CST_ITEM_COST_DE%'

  and dft.descriptive_flexfield_name = df.descriptive_flexfield_name

  and dft.application_id = df.application_id

  and dfc.descriptive_flexfield_name = df.descriptive_flexfield_name

  and dfcu.descriptive_flexfield_name (+) = dfc.descriptive_flexfield_name

  and dfcu.descriptive_flex_context_code (+) = dfc.descriptive_flex_context_code

  and dfcut.descriptive_flexfield_name (+) = dfcu.descriptive_flexfield_name

  and dfcut.descriptive_flex_context_code (+) = dfcu.descriptive_flex_context_code

  and dfcut.application_column_name (+) = dfcu.application_column_name

order by   dft.title, dfc.descriptive_flex_context_code, dfcu.column_seq_num