How to remove last character in notepad ++

1. Open file in Notepad++

2. Ctrl + H to open the Find and Replace dialog.

3. select Regular expression in Search Mode at the bottom.

4. Find what: .$ (if any specific character like , then use ,$)

5. Replace with: (leave this blank).

6. Click "Replace All".

Query to find out schedule request set

 SELECT
    fcr.request_id,
    fcr.description,
    fcpt.user_concurrent_program_name
    || nvl2(fcr.description, ' ('
                             || fcr.description
                             || ')', NULL)                                                      conc_prog,
    fcr.requested_start_date,
    to_char(actual_start_date, 'DD-MON-RRRR HH24:MI:SS'),
    to_char(actual_completion_date, 'DD-MON-RRRR HH24:MI:SS'),
    fu.user_name                                                       requestor,
    fu.description                                                     requested_by,
    fu.email_address,
    frt.responsibility_name                                            requested_by_resp,
    TRIM(fl.meaning)                                                   status,
    fcr.phase_code,
    fcr.status_code,
    fcr.argument_text                                                  "PARAMETERS",
    to_char(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS')                requested,
    to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')        requested_start,
    to_char((fcr.requested_start_date), 'HH24:MI:SS')                  start_time,
    decode(fcr.hold_flag, 'Y', 'Yes', 'N', 'No')                       on_hold,
    CASE
        WHEN fcr.hold_flag = 'Y' THEN
            substr(fu.description, 0, 40)
    END                                                                last_update_by,
    CASE
        WHEN fcr.hold_flag = 'Y' THEN
            fcr.last_update_date
    END                                                                last_update_date,
    fcr.increment_dates,
    CASE
        WHEN fcrc.class_info IS NULL THEN
            'Yes: '
            || to_char(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
        ELSE
            'n/a'
    END                                                                run_once,
    CASE
        WHEN fcrc.class_type = 'P' THEN
            'Repeat every '
            || substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1)
            || decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months',
                      'H', ' hours', 'D',
                      ' days')
            || decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run',
            'C',
                      ' from the completion of the prior run')
        ELSE
            'n/a'
    END                                                                set_days_of_week,
    CASE
        WHEN fcrc.class_type = 'S'
             AND instr(substr(fcrc.class_info, 33), '1', 1) > 0 THEN
            'Days of week: '
            || decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ')
            || decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ')
            || decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ')
            || decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ')
            || decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ')
            || decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ')
            || decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
        ELSE
            'n/a'
    END                                                                days_of_week,
    logfile_name,
    outfile_name
FROM
    apps.fnd_concurrent_requests       fcr,
    apps.fnd_user                      fu,
    apps.fnd_concurrent_programs       fcp,
    apps.fnd_concurrent_programs_tl    fcpt,
    apps.fnd_printer_styles_tl         fpst,
    apps.fnd_conc_release_classes      fcrc,
    apps.fnd_responsibility_tl         frt,
    apps.fnd_lookups                   fl
WHERE
        fcp.application_id = fcpt.application_id
    AND fcr.requested_by = fu.user_id
    AND fcr.concurrent_program_id = fcp.concurrent_program_id
    AND fcr.program_application_id = fcp.application_id
    AND fcr.concurrent_program_id = fcpt.concurrent_program_id
    AND fcr.responsibility_id = frt.responsibility_id
    AND fcr.print_style = fpst.printer_style_name (+)
    AND fcr.release_class_id = fcrc.release_class_id (+)
    AND fcr.status_code = fl.lookup_code
    AND fl.lookup_type = 'CP_STATUS_CODE'
    AND fcr.phase_code = 'P'
    AND frt.language = 'US'
    AND fpst.language = 'US'
    AND fcpt.language = 'US'
    AND upper(fcr.description) LIKE '%REQUESTSET%NAME%'
ORDER BY
    fu.description,
    fcr.requested_start_date DESC;

Query to find customer dunning contact email id

 SELECT
    account_number    account_number,
    obj.party_name    customer_name,
    sub.person_first_name,
    sub.person_last_name,
    sub.party_name    contact_name,
    LISTAGG(hcp.email_address, ',') WITHIN GROUP(
        ORDER BY
            hca.cust_account_id
    )                 emild_list,
    hrr.responsibility_type
FROM
    apps.hz_cust_accounts     hca,
    apps.hz_parties           obj,
    apps.hz_relationships     rel,
    apps.hz_contact_points    hcp,
    apps.hz_parties           sub,
    hz_cust_account_roles     hcar,
    hz_role_responsibility    hrr
WHERE
        hca.party_id = rel.object_id
    AND hca.party_id = obj.party_id
    AND rel.subject_id = sub.party_id
    AND rel.relationship_type = 'CONTACT'
    AND rel.directional_flag = 'F'
    AND rel.party_id = hcp.owner_table_id
   -- and hca.account_number=  '752588'
    AND hcp.owner_table_name = 'HZ_PARTIES'
    AND hcar.cust_account_id = hca.cust_account_id
    AND hcar.party_id = rel.party_id
    AND hrr.cust_account_role_id (+) = hcar.cust_account_role_id
    AND hrr.responsibility_type = 'DUN'
    AND hcp.contact_point_type = 'EMAIL'
GROUP BY
    account_number,
    obj.party_name,
    sub.person_first_name,
    sub.person_last_name,
    sub.party_name,
    hrr.responsibility_type

Query to get the service contract party roles

 SELECT
    okh.id,
    okh.contract_number,
    okh.contract_number_modifier,
    okh.authoring_org_id,
    hp.party_name,
    hp.party_number,
    hca.account_number,
    flv.meaning,
    okr.rle_code
FROM
    okc_k_party_roles_b   okr,
    okc_k_headers_all_b   okh,
    hz_parties            hp,
    hz_cust_accounts_all  hca,
    fnd_lookup_values     flv
WHERE
        okr.chr_id = okh.id
    AND okr.jtot_object1_code = 'OKX_PARTY'
    AND okh.contract_number = '111111111'
    AND okr.object1_id1 = hca.party_id
    AND hp.party_id = hca.party_id
    AND okr.rle_code = flv.lookup_code
    AND flv.lookup_type = 'OKC_ROLE'
    --AND trunc(nvl(flv.end_date_active, sysdate)) >= trunc(sysdate)
    --AND flv.enabled_flag = 'Y'
    AND flv.language = userenv('LANG')
    ;

Query to find list of schedule concurrent program

 SELECT fcr.request_id ,
  fcpt.user_concurrent_program_name
  || NVL2(fcr.description, ' ('
  || fcr.description
  || ')', NULL) conc_prog ,
  fu.user_name requestor ,
  fu.description requested_by ,
  fu.email_address ,
  frt.responsibility_name requested_by_resp ,
  TRIM(fl.meaning) STATUS ,
  fcr.phase_code ,
  fcr.status_code ,
  fcr.argument_text "PARAMETERS" ,
  TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested ,
  TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start ,
  TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time ,
  DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN SUBSTR( fu.description , 0 , 40 )
  END last_update_by ,
  CASE
    WHEN fcr.hold_flag = 'Y'
    THEN fcr.last_update_date
  END last_update_date ,
  fcr.increment_dates ,
  CASE
    WHEN fcrc.CLASS_INFO IS NULL
    THEN 'Yes: '
      || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
    ELSE 'n/a'
  END run_once ,
  CASE
    WHEN fcrc.class_type = 'P'
    THEN 'Repeat every '
      || SUBSTR(fcrc.class_info, 1, instr(fcrc.class_info, ':')           - 1)
      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1), 'N', ' minutes', 'M', ' months', 'H', ' hours', 'D', ' days')
      || DECODE(SUBSTR(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1), 'S', ' from the start of the prior run', 'C', ' from the completion of the prior run')
    ELSE 'n/a'
  END set_days_of_week ,
  CASE
    WHEN fcrc.class_type                         = 'S'
    AND instr(SUBSTR(fcrc.class_info, 33),'1',1) > 0
    THEN 'Days of week: '
      || DECODE(SUBSTR(fcrc.class_info, 33, 1), '1', 'Sun, ')
      || DECODE(SUBSTR(fcrc.class_info, 34, 1), '1', 'Mon, ')
      || DECODE(SUBSTR(fcrc.class_info, 35, 1), '1', 'Tue, ')
      || DECODE(SUBSTR(fcrc.class_info, 36, 1), '1', 'Wed, ')
      || DECODE(SUBSTR(fcrc.class_info, 37, 1), '1', 'Thu, ')
      || DECODE(SUBSTR(fcrc.class_info, 38, 1), '1', 'Fri, ')
      || DECODE(SUBSTR(fcrc.class_info, 39, 1), '1', 'Sat ')
    ELSE 'n/a'
  END days_of_week
FROM apps.fnd_concurrent_requests fcr ,
  apps.fnd_user fu ,
  apps.fnd_concurrent_programs fcp ,
  apps.fnd_concurrent_programs_tl fcpt ,
  apps.fnd_printer_styles_tl fpst ,
  apps.fnd_conc_release_classes fcrc ,
  apps.fnd_responsibility_tl frt ,
  apps.fnd_lookups fl
WHERE fcp.application_id       = fcpt.application_id
AND fcr.requested_by           = fu.user_id
AND fcr.concurrent_program_id  = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcr.concurrent_program_id  = fcpt.concurrent_program_id
AND fcr.responsibility_id      = frt.responsibility_id
AND fcr.print_style            = fpst.printer_style_name(+)
AND fcr.release_class_id       = fcrc.release_class_id(+)
AND fcr.status_code            = fl.lookup_code
AND fl.lookup_type             = 'CP_STATUS_CODE'
AND fcr.phase_code             = 'P'
AND frt.language               = 'US'
AND fpst.language              = 'US'
AND fcpt.language              = 'US'
and fcpt.user_concurrent_program_name ='Concurrent program name'
ORDER BY Fu.Description,
  Fcr.Requested_Start_Date ASC  
  ;

Query to find receipt number from invoice

 SELECT
    acr.receipt_number,
    acr.receipt_date,
    acr.amount,
    rct.customer_trx_id,
    rct.trx_number,
    rct.trx_date,
    acr.last_update_date,
    rct.last_update_date,
    acr.receipt_method_id
FROM
    ar_receivable_applications_all  ara,
    ar_cash_receipts_all            acr,
    ra_customer_trx_all             rct
WHERE
        1 = 1
    --    ara.status = 'APP'
    AND ara.cash_receipt_id = acr.cash_receipt_id
    AND ara.applied_customer_trx_id = rct.customer_trx_id
    AND rct.org_id = 1111
    AND rct.trx_number IN ( '111111' )
ORDER BY
    rct.last_update_date DESC;

Query to find list of all open invoice for the customer

 SELECT
    hp.party_id,
    hp.party_number,
    hp.party_name,
    hp.party_type,
    hca.account_number,
    hcp.send_statements,
    hcp.statement_cycle_id,
    hcp.dunning_letters,
    rcta.org_id,
    hou.name,
    rcta.trx_number                           invoice_number,
    ar.class,
    rcta.purchase_order                       purchase_order,
    rcta.trx_date                             transaction_date,
    to_char(ar.due_date, 'MM/DD/YYYY')        due_date,
    ar.amount_due_remaining                   amount_due_remaining,
    trunc(sysdate - ar.due_date)              day_late,
    hca.cust_account_id
FROM
    hz_parties                hp,
    hz_cust_accounts          hca,
    hz_customer_profiles      hcp,
    ra_customer_trx_all       rcta,
    hr_operating_units        hou,
    ar_payment_schedules_all  ar
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.site_use_id IS NULL
    AND rcta.org_id = hou.organization_id
    AND rcta.bill_to_customer_id = hcp.cust_account_id
    AND rcta.customer_trx_id = ar.customer_trx_id
    AND ar.class = 'INV'
    AND ar.status = 'OP'
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND hca.account_number = '37382'
ORDER BY
    hp.party_name,
    hca.account_number;

Query to find customer Profile and Profile amount information

 SELECT DISTINCT
    hp.party_id,
    hp.party_number,
    hca.account_number,
    hp.party_name,
    hp.party_type,
    ac.name           collector_name,
    ac.description    collector_description,
    hcp.send_statements,
    hcp.statement_cycle_id,
    ascs.name         statement_cycle_name,
    hcp.dunning_letters,
    hcp.dunning_letter_set_id,
    adls.name         dunning_letter_set,
    hcpa.min_statement_amount,
    hcpa.min_dunning_amount,
    hcpa.currency_code,
    hca.cust_account_id
FROM
    hz_parties              hp,
    hz_cust_accounts        hca,
    hz_customer_profiles    hcp,
    hz_cust_profile_amts    hcpa,
    ar_statement_cycles     ascs,
    ar_dunning_letter_sets  adls,
    ar_collectors           ac
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.cust_account_profile_id = hcpa.cust_account_profile_id (+)
    AND hcp.collector_id = ac.collector_id (+)
    AND hcp.statement_cycle_id = ascs.statement_cycle_id (+)
    AND hcp.dunning_letter_set_id = adls.dunning_letter_set_id (+)
    AND hcp.site_use_id IS NULL
    AND hcpa.site_use_id IS NULL
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
    AND hca.account_number = '1111111';

Link between ar_collectors and per_all_people_f

  SELECT
    ac.name,
    ac.description,
    papf.full_name
FROM
    ar_collectors     ac,
    per_all_people_f  papf
WHERE
        1 = 1
    AND ac.employee_id = papf.person_id(+)
    AND ac.status = 'A'
    AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date);

Query to find list of responsibilities for a user in oracle apps

 SELECT
    fu.user_name,
    frt.responsibility_name,
    furg.start_date,
    furg.end_date,
    fr.responsibility_key,
    fa.application_short_name "Application Short Name"
FROM
    apps.fnd_user_resp_groups_direct    furg,
    apps.fnd_user                       fu,
    apps.fnd_responsibility_tl          frt,
    apps.fnd_responsibility             fr,
    apps.fnd_application_tl             fat,
    apps.fnd_application                fa
WHERE
        furg.user_id = fu.user_id
    AND furg.responsibility_id = frt.responsibility_id
    AND fr.responsibility_id = frt.responsibility_id
    AND fa.application_id = fat.application_id
    AND fr.application_id = fat.application_id
    AND frt.language = userenv('LANG')
    AND upper(fu.user_name) = upper('USER_NAME')
    AND trunc(nvl(furg.end_date, sysdate)) >= trunc(sysdate)
ORDER BY
    furg.start_date;

Query to find Note Source

SELECT

    jnv.source_object_code,

    jnv.source_object_meaning,

    jov.from_table,

    COUNT(*) count

FROM

    jtf_notes_vl    jnv,

    jtf_objects_vl  jov

WHERE

    jnv.source_object_code = jov.object_code (+)

GROUP BY

    jnv.source_object_code,

    jnv.source_object_meaning,

    jov.from_table

ORDER BY

    COUNT(*) DESC;

Query to find customer profile information in oracle apps

 SELECT
    hp.party_id,
    hp.party_number,
    hp.party_name,
    hp.party_type,
    hca.account_number,
    hcpc.name           profile_class,
    hcpc.description    profile_class_description,
    ac.name             collector_name,
    ac.description      collector_description,
    hcp.send_statements,
    ascs.name           statement_cycle_name,
    hcp.statement_cycle_id,
    hcp.dunning_letters,
    hcp.dunning_letter_set_id,
    adls.name           dunning_letter_set,
    hcp.object_version_number,
    hca.cust_account_id,
    hcp.cust_account_profile_id
FROM
    hz_parties               hp,
    hz_cust_accounts         hca,
    ar_statement_cycles      ascs,
    ar_dunning_letter_sets   adls,
    ar_collectors            ac,
    hz_cust_profile_classes  hcpc,
    hz_customer_profiles     hcp
WHERE
        1 = 1
    AND hp.party_id = hca.party_id
    AND hca.cust_account_id = hcp.cust_account_id
    AND hcp.statement_cycle_id = ascs.statement_cycle_id (+)
    AND hcp.dunning_letter_set_id = adls.dunning_letter_set_id (+)
    AND hcp.collector_id = ac.collector_id (+)
    AND hcp.profile_class_id = hcpc.profile_class_id (+)
    AND hcp.site_use_id IS NULL
    AND hp.status = 'A'
    AND hca.status = 'A'
    AND hcp.status = 'A'
    AND hp.party_type = 'ORGANIZATION'
;

Script to assign the concurrent program to a request group

DECLARE

   l_ret_code                    NUMBER;

   l_ret_message                 VARCHAR2 (1000);  

                                           

BEGIN

  

xxconv_util.add_program_to_group ('Custom Application'              --program application name

                                      , 'DELIVERABLE_PRG'                                --program short name 

                                      , 'PA Manager'       --request group name 

                                      , 'Projects'      --request group application

                                      , l_ret_code

                                      , l_ret_message

                                       );

       

COMMIT;

EXCEPTION  WHEN OTHERS THEN 

DBMS_OUTPUT.PUT_LINE('Error Attaching Program To Request Group  ' || SQLERRM);

ROLLBACK;

END;

/

Query to find location of rtf template in bursting control file

 select 'xdo://'|| 

       xtb.application_short_name||'.'||

         xtb.template_code ||'.'||

         xtb.default_language ||'.'||

         xtb.default_territory

  from   apps.xdo_templates_b xtb

 where   xtb.template_code ='ARXSGP';

Query to find the last access date for a Responsibility in R12

SELECT
    frt.responsibility_name,
    fu.user_name,
    MAX(flr.start_time) "Last Connect"
FROM
    applsys.fnd_login_responsibilities    flr,
    applsys.fnd_user                      fu,
    applsys.fnd_logins                    fl,
    applsys.fnd_responsibility_tl         frt
WHERE
        fl.login_id = flr.login_id
    AND fl.user_id = fu.user_id
    AND fu.user_name = 'SANDEEP_SHARMA' -- Username
    AND frt.responsibility_id = flr.responsibility_id
       --and frt.responsibility_name = '&ResponsibilityName'
GROUP BY
    frt.responsibility_name,
    fu.user_name;

Query to find oracle patch is applied or not

 SELECT DISTINCT
    e.patch_name,
    a.bug_number,
    ad_patch.is_patch_applied('R12', - 1, a.bug_number)            patch_applied,
    to_char(c.end_date, 'DD-MON-RRRR HH24:MI:SS')           end_date,
    e.patch_type,
    d.patch_abstract
FROM
    ad_bugs             a,
    ad_patch_run_bugs   b,
    ad_patch_runs       c,
    ad_patch_drivers    d,
    ad_applied_patches  e
WHERE
        a.bug_id = b.bug_id
    AND b.patch_run_id = c.patch_run_id
    AND c.patch_driver_id = d.patch_driver_id
    AND d.applied_patch_id = e.applied_patch_id
   AND e.patch_name in ('33515489')--patch_number
ORDER BY
    1,
    3 ASC;

API Script to Terminate Service Contract/Line/Subline in Oracle Apps

oks_terminate_contract_pub.Terminate_Contract

set serveroutput on size 100000;

DECLARE
   l_return_status   VARCHAR2 (100);
   l_date_term       DATE;
   L_MSG_DATA        VARCHAR2 (1000);
   l_msg_count       NUMBER;
   l_msg_index_out   NUMBER;
   l_user_id        NUMBER;
   l_resp_id        NUMBER;
   l_resp_appl_id   NUMBER;
BEGIN
/*Note:-
P_contract_id            --> Contract Header Id
Line Id                     --> Contract Line Id
P_subline_id              --> Contract Subline Id
P_termination_date    --> Termination Date
p_fullcredit_yn          --> Indicates if full credit has to be given during termination and default value is ‘N’
p_suppress_credit_yn --> Indicates if credit has to be suppressed during termination and default value is ‘N’
p_term_reason_code  --> Termination Reason
p_override_amount    --> Override Amount*/
   BEGIN
    SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name = 'TEST_USER';    
   END;
  
   BEGIN
    SELECT responsibility_id, application_id
      INTO l_resp_id, l_resp_appl_id
      FROM fnd_responsibility_tl
     WHERE responsibility_name = 'NUAN NDI Ireland Service Contracts Manager'
       and application_id = 515 ;  --Service Contracts
   END;
  
   fnd_global.apps_initialize (user_id        => l_user_id,
                               resp_id        => l_resp_id,
                               resp_appl_id   => l_resp_appl_id
                              );
                             
   okc_context.set_okc_org_context (p_chr_id => 1953781);
  
   oks_terminate_contract_pub.Terminate_Contract(
      p_contract_id          => 1953781,
      p_line_id              => NULL,
      p_subline_id           => NULL,
      P_Termination_Date     => TO_DATE ('17-OCT-2022'),
      p_fullcredit_yn        => 'N', --Indicates if full credit has to be given during termination and default value is ‘N’
      p_suppress_credit_yn   => 'N', --Indicates if credit has to be suppressed during termination and default value is ‘N’
      p_term_reason_code     => 'RMA',
      p_override_amount      => NULL,
      x_return_status        => l_return_status);
  
   DBMS_OUTPUT.put_line ('l_return_status - ' || l_return_status);
   IF l_return_status = 'S'
   THEN
      COMMIT;
   END IF;
  
   IF l_return_status <> 'S'
   THEN
      FOR i IN 1 .. fnd_msg_pub.count_msg
      LOOP
         fnd_msg_pub.get (p_msg_index       => i,
                          p_encoded         => 'T',
                          p_data            => l_msg_data,
                          p_msg_index_out   => l_msg_index_out);
         fnd_message.set_encoded (l_msg_data);
         l_msg_data := fnd_message.get;
         DBMS_OUTPUT.put_line (l_msg_data);
      END LOOP;
   END IF;
END;
/

query to find service contract terminate reason

SELECT LOOKUP_CODE,MEANING,DESCRIPTION
FROM   fnd_lookups fndlup
where  fndlup.lookup_type = 'OKC_TERMINATION_REASON'
and trunc(nvl(END_DATE_ACTIVE,sysdate)) >= trunc(sysdate)

API Script to update expiration date for Install Base

csi_item_instance_pub.update_item_instance

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    CURSOR cur_ib IS
    SELECT
        instance_id,
        inventory_item_id item_id,
        system_id,
        object_version_number,
        external_reference,
        serial_number,
        last_oe_order_line_id,
        attribute10
    FROM
        csi_item_instances csi
    WHERE 1=1
and csi.instance_id = 9710652--9710653
;
      --
    l_external_reference     VARCHAR2(50);
    l_instance_id            NUMBER;
    l_object_version_number  NUMBER;
    l_end_user               VARCHAR2(100);
    v_instance_rec           csi_datastructures_pub.instance_rec;
    v_ext_attrib_values_tbl  csi_datastructures_pub.extend_attrib_values_tbl;
    v_party_tbl              csi_datastructures_pub.party_tbl;
    v_account_tbl            csi_datastructures_pub.party_account_tbl;
    v_pricing_attrib_tbl     csi_datastructures_pub.pricing_attribs_tbl;
    v_org_assignments_tbl    csi_datastructures_pub.organization_units_tbl;
    v_asset_assignment_tbl   csi_datastructures_pub.instance_asset_tbl;
    v_txn_rec                csi_datastructures_pub.transaction_rec;
    v_instance_id_lst        csi_datastructures_pub.id_tbl;
    v_status_code            VARCHAR2(100);
    t_output                 VARCHAR2(1000);
    v_return_status          VARCHAR2(1000);
    v_msg_dummy              VARCHAR2(1000);
    v_msg_count              NUMBER;
    v_msg_data               VARCHAR2(1000);
    l_system_id              csi_item_instances.system_id%TYPE;
BEGIN
      --
    FOR rec_ib IN cur_ib LOOP
dbms_output.put_line('instance_id = ' || rec_ib.instance_id);
        BEGIN
            v_instance_rec.instance_id := rec_ib.instance_id;
v_instance_rec.active_end_date := '01-JUN-2023';
            v_instance_rec.object_version_number := rec_ib.object_version_number;
            v_txn_rec.transaction_id := NULL;
            v_txn_rec.transaction_date := sysdate;
            v_txn_rec.source_transaction_date := sysdate;
            v_txn_rec.transaction_type_id := 1;
            --v_txn_rec.txn_sub_type_id := NULL;
            csi_item_instance_pub.update_item_instance(1,
                                                      fnd_api.g_false,
                                                      fnd_api.g_false,
                                                      fnd_api.g_valid_level_full,
                                                      v_instance_rec,
                                                      v_ext_attrib_values_tbl,
                                                      v_party_tbl,
                                                      v_account_tbl,
                                                      v_pricing_attrib_tbl,
                                                      v_org_assignments_tbl,
                                                      v_asset_assignment_tbl,
                                                      v_txn_rec,
                                                      v_instance_id_lst,
                                                      v_return_status,
                                                      v_msg_count,
                                                      v_msg_data);
            DBMS_OUTPUT.put_line ('v_return_status = ' || v_return_status);
IF v_return_status != 'S' THEN
               --
                IF v_msg_count > 0 THEN
                    FOR j IN 1..v_msg_count LOOP
                        fnd_msg_pub.get(j, fnd_api.g_false,
                                       v_msg_data,
                                       v_msg_dummy);
                        t_output := ( 'Msg'
                                      || to_char(j)
                                      || ': '
                                      || v_msg_data );
                        dbms_output.put_line(substr(t_output, 1,
                                                   255));
                    END LOOP;
                END IF;
            -- DBMS_OUTPUT.put_line ('v_return_status = ' || v_return_status);
            END IF;
            --
            IF v_return_status = 'S' THEN
                COMMIT;
            ELSE
                ROLLBACK;
            END IF;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('exception v_return_status = ' || v_return_status);
        END;
    END LOOP;
END;
/

API script to end date responsibilities for a User

 SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
    CURSOR emp_resp_det 
    IS
SELECT
usr.user_id,
usr.user_name,
papf.employee_number,
papf.full_name,
resp.responsibility_name,
resp.responsibility_id,
resp.application_id,
furg.security_group_id,
furg.start_date,
furg.end_date,
trunc(sysdate) responsibility_end_date
FROM
per_all_people_f          papf,
fnd_responsibility_tl     resp,
fnd_user                  usr,
fnd_user_resp_groups_all  furg
WHERE
1 = 1
AND trunc(sysdate) BETWEEN trunc(furg.start_date) AND trunc(nvl(furg.end_date,sysdate))
AND furg.responsibility_id = resp.responsibility_id
AND usr.user_id = furg.user_id
AND papf.person_id = usr.employee_id
AND trunc(sysdate) BETWEEN trunc(papf.effective_start_date) AND trunc(papf.effective_end_date)
AND resp.responsibility_name <> 'NUAN Access Request System'
--AND papf.employee_number = 1111111
ORDER BY usr.user_id;
    l_flag VARCHAR2(2) := NULL;
    l_count NUMBER := 0;
BEGIN
    dbms_output.put_line('Script To End Date Responsibilities For Given User');
    dbms_output.put_line('--------------------------------------------------------------------------------');
    dbms_output.put_line('USER_ID,' || 'USER_NAME,' || 'RESPONSIBILITY_NAME,' ||  'L_FLAG');
    l_count := 0;
    FOR rec_emp_resp_det IN emp_resp_det 
    LOOP
        BEGIN
            l_flag := NULL;
            
            fnd_user_resp_groups_api.update_assignment(
                                                        user_id => rec_emp_resp_det.user_id, 
                                                        responsibility_id => rec_emp_resp_det.responsibility_id,
                                                        responsibility_application_id => rec_emp_resp_det.application_id, 
                                                        security_group_id => rec_emp_resp_det.security_group_id,
                                                        start_date => rec_emp_resp_det.start_date,
                                                        end_date => rec_emp_resp_det.responsibility_end_date, 
                                                        description => NULL, 
                                                        update_who_columns => 'Y'
                                                      );
            l_flag := 'S';
        EXCEPTION
            WHEN OTHERS 
            THEN
                dbms_output.put_line('Error Updating End date for employee ' || rec_emp_resp_det.employee_number || ': ' || sqlerrm);
                l_flag := 'E';
        END;
        dbms_output.put_line(rec_emp_resp_det.user_id || ',' || rec_emp_resp_det.user_name|| ',' ||rec_emp_resp_det.responsibility_name ||  ',' || l_flag);
        l_count := l_count + 1;
    END LOOP;
    dbms_output.put_line('--------------------------------------------------------------------------------');
    dbms_output.put_line('Total Records Processed = ' ||l_count);
    dbms_output.put_line('--------------------------------------------------------------------------------');
    COMMIT;
EXCEPTION
    WHEN OTHERS 
    THEN
        dbms_output.put_line('Error :' || sqlerrm);
END;
/