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

Reassign Tasks for end dated rep to new rep

set serveroutput on size 320000

--owner id is the resource_id
/*
select *
from JTF_RS_RESOURCE_DTLS_VL
where resource_name like
*/

declare

l_OBJECT_VER_NUM             JTF_TASKS_V.OBJECT_VERSION_NUMBER%TYPE :=2;
l_TASK_STATUS_ID             JTF_TASKS_V.TASK_STATUS_ID%TYPE := 9;
l_TASK_STATUS_NAME           JTF_TASKS_V.TASK_STATUS%TYPE;
l_TASK_ID                    JTF_TASKS_V.TASK_ID%TYPE :=12341;
l_TASK_NUMBER                JTF_TASKS_V.TASK_NUMBER%TYPE;

l_return_status VARCHAR2(1);
l_msg_count NUMBER;
l_msg_data VARCHAR2(1000);

cursor task_cur is
select t.task_id task_id,
t.object_version_number object_version_number
from jtf_tasks_b t,
hz_party_sites ps,
hz_locations l
where t.open_flag = 'Y'
and t.owner_id != 100002064
and t.address_id = ps.party_site_id
and ps.location_id = l.location_id
and l.country = 'US'
and t.owner_id in (&OLD_ID);

begin

for task_rec in task_cur loop
    jtf_tasks_pub.update_task(
                            p_api_version => 1.0 ,
                            p_init_msg_list => fnd_api.g_true,
                            p_commit => fnd_api.g_false,
                            p_object_version_number => task_rec.object_version_number,
                            p_task_id => task_rec.task_id,
                            p_owner_id => &NEW_ID

,
                            x_return_status => l_return_status,
                            x_msg_count => l_msg_count,
                            x_msg_data => l_msg_data
                            );
end loop;

IF l_return_status <> fnd_api.g_ret_sts_success
  THEN
     IF l_msg_count > 0 THEN
        l_msg_data := NULL;
        FOR i IN 1..l_msg_count
           LOOP
             l_msg_data := l_msg_data ||' '||fnd_msg_pub.get(1, 'F');
           END LOOP;
        fnd_message.set_encoded(l_msg_data);
        dbms_output.put_line(substr(l_msg_data,1.1,200));
     END IF;

     ROLLBACK;
ELSE

     COMMIT;
END IF;
end;
/

SQL to Retry OEOH workflow activity

declare

    cursor c_orders_to_retry is

    SELECT P.INSTANCE_LABEL,

           WAS.ITEM_KEY,

           H.ORDER_NUMBER,

           H.ORG_ID,

           was.begin_date

      FROM WF_ITEM_ACTIVITY_STATUSES WAS,

           WF_PROCESS_ACTIVITIES P,

           OE_ORDER_HEADERS_ALL H

    WHERE     TO_NUMBER (WAS.ITEM_KEY) = H.HEADER_ID

           AND WAS.PROCESS_ACTIVITY = P.INSTANCE_ID

           AND P.ACTIVITY_ITEM_TYPE = 'OEOH'

           AND P.ACTIVITY_NAME = 'CLOSE_WAIT_FOR_L'

           AND WAS.ACTIVITY_STATUS = 'NOTIFIED'

           AND WAS.ITEM_TYPE = 'OEOH'

           AND NOT EXISTS

                      (SELECT /*+ NO_UNNEST */

                             1

                         FROM OE_ORDER_LINES_ALL

                        WHERE     HEADER_ID = TO_NUMBER (WAS.ITEM_KEY)

                              AND OPEN_FLAG = 'Y')

           --and h.order_number = '4214653'

           ;

    l_update_flag   varchar2(1) := 'N';      

begin

    dbms_output.put_line('Update Flag : '||l_update_flag);

    for o in c_orders_to_retry

    loop

        if l_update_flag = 'Y' then

            MO_GLOBAL.set_policy_context ('S', o.org_id);

            WF_ENGINE.HANDLEERROR('OEOH', o.ITEM_KEY, o.INSTANCE_LABEL, 'RETRY',NULL);

        end if;

        dbms_output.put_line('Order : '||o.order_number);

    end loop;

    --commit;

exception

when others then

    dbms_output.put_line('Error : '||sqlerrm);

end;    

Find Order number for given pick release rule

Select order number for pick release rule :

  SELECT DISTINCT wdd.source_header_number,

                  otl.name,

                  SUBSTRB (party.party_name, 1, 50) customer_name,

                  wdd.source_header_id,

                  wdd.source_header_type_id,

                  cust_acct.cust_account_id customer_id

    FROM wsh_delivery_details wdd,

         hz_parties party,

         hz_cust_accounts cust_acct,

         WSH_PICKING_RULES wpr,

         oe_transaction_types_tl otl

   WHERE     wdd.customer_id = cust_acct.cust_account_id

         AND cust_acct.party_id = party.party_id

         AND wdd.released_status IN ('B', 'R', 'X')

         AND wdd.organization_id = wpr.organization_id

         AND wdd.subinventory = NVL (wpr.rel_subinventory, wdd.subinventory)

         AND otl.language = USERENV ('LANG')

         AND otl.transaction_type_id = wdd.source_header_type_id

         AND otl.name = 'Order type'

         AND wpr.name = NVL ( :RELEASE.PICKING_RULE, wpr.name)

ORDER BY 2, 1

Pick Release Rule

SELECT wpr.NAME picking_rule_name,
                    wpr.backorders_only_flag,
                    DECODE (s2.meaning,
                            'Exclude', 'Unreleased',
                            'Include', 'All',
                            'Only', 'Backordered')
                       backorders,
                    s1.meaning shipment_priority,
                    wpr.shipment_priority_code,
                    wpr.organization_id,
                    wpr.organization_id org_id,
                    wpr.organization_id warehouse_id_lov,
                    h_org_tl.NAME warehouse,
                    org.organization_code warehouse_code,
                    DECODE (wpr.existing_rsvs_only_flag, 'Y', '*', NULL)
                       reservation_star,
                    wpr.existing_rsvs_only_flag,
                    wpr.ship_method_code,
                    wpr.from_scheduled_ship_date,
                    wpr.to_scheduled_ship_date,
                    wpr.from_requested_date,
                    wpr.to_requested_date,
                    ott.NAME order_type,
                    wpr.order_type_id,
                    TO_CHAR (h.order_number) order_number,
                    wpr.order_header_id,
                    os.set_name ship_set_number,
                    wpr.inventory_item_id,
                    msi.description item_description,
                    SUBSTRB (party.party_name, 1, 50) customer_name,
                    wpr.customer_id,
                    whzl.ui_location_code ship_to_location,
                    wpr.ship_to_location_id,
                    wpr.include_planned_lines,
                    wpr.autocreate_delivery_flag,
                    pgr.NAME pick_grouping_rule_name,
                    rsqr.NAME pick_seq_rule_name,
                    wpr.task_id,
                    wpr.project_id,
                    wpr.autodetail_pr_flag,
                    wpr.auto_pick_confirm_flag,
                    wpr.ship_set_number ship_set_id,
                    whrl.ui_location_code ship_from_location,
                    wpr.ship_from_location_id,
                    wpr.document_set_id,
                    wrs.NAME doc_name,
                    wpr.pick_from_subinventory,
                    wpr.pick_from_locator_id,
                    NULL pick_from_locator,
                    wpr.default_stage_subinventory,
                    wpr.default_stage_locator_id,
                    NULL default_stage_locator,
                    sm.meaning ship_method_meaning,
                    wpr.autopack_flag,
                    wpr.autopack_level,
                    wpr.ship_confirm_rule_id,
                    wscr.NAME ship_confirm_rule_name,
                    wpr.task_planning_flag,
                    wpr.region_id,
                    wpr.zone_id,
                    wpr.ac_delivery_criteria,
                    wpr.rel_subinventory,
                    wpr.category_set_id,
                    wpr.category_id,
                    wpr.sch_start_days,
                    wpr.sch_end_days,
                    wpr.sch_start_hours,
                    wpr.sch_end_hours,
                    wpr.req_start_days,
                    wpr.req_end_days,
                    wpr.req_start_hours,
                    wpr.req_end_hours,
                    wpr.append_flag,
                    wpr.task_priority,
                    wpr.allocation_method,
                    wpr.crossdock_criteria_id,
                    wpr.dynamic_replenishment_flag,
                    wpr.client_id client_id,
                    mcpv.client_name client_name
               FROM wsh_picking_rules wpr,
                    mtl_parameters org,
                    hr_all_organization_units_tl h_org_tl,
                    hz_parties party,
                    hz_cust_accounts cust_acct,
                    fnd_lookup_values s1,
                    fnd_lookup_values s2,
                    oe_sets os,
                    wsh_pick_grouping_rules pgr,
                    wsh_pick_sequence_rules rsqr,
                    oe_transaction_types_tl ott,
                    oe_transaction_types_all otb,
                    oe_order_headers_all h,
                    wsh_locations whrl,
                    wsh_locations whzl,
                    wsh_report_sets wrs,
                    wsh_ship_confirm_rules wscr,
                    mtl_system_items_b msi,
                    fnd_lookup_values sm,
                    mtl_client_parameters_v mcpv
              WHERE     wpr.organization_id =
                           NVL (fnd_profile.VALUE ('MFG_ORGANIZATION_ID'),
                                wpr.organization_id)                -- changed
                    AND wpr.name =
                           NVL (
                              fnd_PROFILE.VALUE ('XXCNC_WMS_DEFAULT_PICK_RULE'),
                              wpr.name)
                    AND org.organization_id(+) = wpr.organization_id
                    AND h_org_tl.organization_id(+) = wpr.organization_id
                    AND cust_acct.cust_account_id(+) = wpr.customer_id
                    AND cust_acct.party_id = party.party_id(+)
                    AND h_org_tl.LANGUAGE(+) = USERENV ('LANG')
                    AND pgr.pick_grouping_rule_id(+) =
                           wpr.pick_grouping_rule_id
                    AND rsqr.pick_sequence_rule_id(+) =
                           wpr.pick_sequence_rule_id
                    AND wpr.ship_confirm_rule_id = wscr.ship_confirm_rule_id(+)
                    AND s1.lookup_code(+) = wpr.shipment_priority_code
                    AND s1.lookup_type(+) = 'SHIPMENT_PRIORITY'
                    AND s1.LANGUAGE(+) = USERENV ('LANG')
                    AND s1.view_application_id(+) = 660
                    AND s1.security_group_id(+) = 0
                    AND s2.lookup_code = wpr.backorders_only_flag
                    AND s2.lookup_type = 'PICK_RELEASE_OPTIONS'
                    AND s2.LANGUAGE(+) = USERENV ('LANG')
                    AND s2.view_application_id(+) = 660
                    AND s2.security_group_id(+) = 0
                    AND otb.transaction_type_id(+) =
                           NVL (wpr.order_type_id, -1)
                    AND otb.transaction_type_code(+) = 'ORDER'
                    AND otb.transaction_type_id = ott.transaction_type_id(+)
                    AND ott.LANGUAGE(+) = USERENV ('LANG')
                    AND whzl.wsh_location_id(+) =
                           NVL (wpr.ship_to_location_id, -1)
                    AND whrl.wsh_location_id(+) =
                           NVL (wpr.ship_from_location_id, -1)
                    AND wrs.report_set_id(+) = wpr.document_set_id
                    AND os.set_id(+) = NVL (wpr.ship_set_number, -1)
                    AND h.header_id(+) = NVL (wpr.order_header_id, -1)
                    AND msi.inventory_item_id(+) = wpr.inventory_item_id
                    --AND msi.organization_id(+) = :parameter_organization_id
                    AND TRUNC (SYSDATE) BETWEEN NVL (wpr.start_date_active,
                                                     TRUNC (SYSDATE))
                                            AND NVL (wpr.end_date_active,
                                                     TRUNC (SYSDATE) + 1)
                    AND sm.lookup_type(+) = 'SHIP_METHOD'
                    AND sm.lookup_code(+) = wpr.ship_method_code
                    AND sm.view_application_id(+) = 3
                    AND sm.LANGUAGE(+) = USERENV ('LANG')
                    AND mcpv.client_id(+) = wpr.client_id
--put yout organization_id to filter the lsst
   ORDER BY picking_rule_name