Merging Discoverer workbooks or Moving/Copying Worksheet from one workbook to another

May be I am missing something (suggestions are most welcome) but to achieve these I am going with the new workbook approach.

Steps:

1> Login to Discoverer Desktop

2> Open all the workbooks you want to merge

3> Now click the 'Restore Down' button (with Double square symbol ) of the last opened workbook.
   > You can see all the opened workbooks in the same window.

4> select the 1st worksheet of the 1st workbook and drop into the vacant space of the window with 'Control' button pressed.
   Note: Without 'Ctrl' it will completely move the sheet from workbook, with 'Ctrl' it will just copy the sheet from the workbook
   > So 'Ctrl' button pressed and left click on the worksheet and drag to the vacant area.
   >> It will create a new workbook with that worksheet, do the same drag and drop for other worksheets from all the workbooks to the new workbook.
     >> Save the new workbook with the name as required.
     >> At the end, share this workbook to the respective users.

Related Post: Renaming Discoverer Workbooks

Renaming Discoverer Workbook

You cannot rename the existing workbook, what you can do is, save the workbook with new name and delete the existing one.(OR you can go with the Oracle non supported approach and update the base table eul5_documents)

Issue with this approach is, you need to re-share this workbook again with all the original users .

Steps:

1> Run the below script and note down the user name with which workbook has been shared

select fu1.user_name||'||'||fu1.description "Workbook Owner",
       doc_name "Workbook Name",doc_description "Description",
       fu2.user_name||'||'||fu2.description "Shared With"
  from eul_us.eul5_eul_users usrs,
       eul_us.eul5_documents docs,
       eul_us.eul5_access_privs privs,
       fnd_user fu1,
       fnd_user fu2
 where privs.gd_doc_id = docs.doc_id
   and usrs.eu_id = privs.ap_eu_id
   and '#'||fu1.user_id = ap_created_by
   and '#'||fu2.user_id = eu_username
   --and fu2.user_name = 'KABHAY'
   --and doc_name like 'Project Transaction%'
   ;

2> Login to Discoverer Desktop, take the backup of the workbook on database/local machine

3> Save as this workbook with the <New Name>
   >> File > Save As...

4> Delete the original workbook (This step will remove all the sharing)
   >> File > Manage Workbooks > Delete...

5> Share the renamed workbook with the users again (user list from 1st step)
   >> File > Manage Workbooks > Sharing...

AR API to change Receipt from Unidentified to Unapplied

Recently I had a requirement to move unapplied AR receipts from one customer to another customer and apply to the related customer invoice.
I did not find any API to update the customer details of an unapplied receipt, nasty way to update the customer on unapplied receipt is to update related base tables (as_cash_receipts_all and ar_payment_schedules_all), I won't suggest to go with this.

There is an API to move unidentified receipt (Receipt without customer details) to unapplied receipts.
So solution offered was to run data loader for unapplied receipts to remove customers to make those to unidentified receipts and then use API ar_receipt_update_api_pub.update_receipt_unid_to_unapp to make it unapplied on required customer and then using API apps.ar_receipt_api_pub.apply, apply that receipt to related customer invocie.

Please find the sample script for API 'ar_receipt_update_api_pub.update_receipt_unid_to_unapp'

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

declare
   cursor cur_unid_rcpts
   is
    select acr.org_id
          ,acr.receipt_number
          ,hca.cust_account_id
          ,acr.cash_receipt_id
     from ar_cash_receipts_all acr
         ,hz_cust_accounts hca
    where acr.status = 'UNID'
      -- Account number stored in attribute3
      and trim(leading 0 from acr.attribute3 )= hca.account_number
      and acr.type = 'CASH'
      and acr.receipt_number = 'AKTest'--<<ReceiptNumber>>
      order by acr.receipt_number;
         
   x_return_status               varchar2 (200);
   x_msg_count                   number;
   x_msg_data                    varchar2 (200);
   x_status                      varchar2 (200);
   p_count                       number;

begin

   for rec_unid_rcpts in cur_unid_rcpts loop
  
       -- Setting up the org context
       apps.mo_global.init ('AR');
       mo_global.set_policy_context('S' , rec_unid_rcpts.org_id) ;
        
       ar_receipt_update_api_pub.update_receipt_unid_to_unapp
              (p_api_version                    => 1.0,
               p_init_msg_list                  => apps.fnd_api.g_true,
               p_commit                         => apps.fnd_api.g_false,
               p_validation_level               => apps.fnd_api.g_valid_level_full,
               x_return_status                  => x_return_status,
               x_msg_count                      => x_msg_count,
               x_msg_data                       => x_msg_data,
               p_cash_receipt_id                => rec_unid_rcpts.cash_receipt_id,
               p_pay_from_customer          => rec_unid_rcpts.cust_account_id,
               x_status                              => x_status
              );
       dbms_output.put_line ('API Status = ' || x_return_status);
       dbms_output.put_line ('Receipt Status = ' || x_status);

       if x_msg_count = 1 then
          dbms_output.put_line ('API Error Msg: ' || x_msg_data);
       elsif x_msg_count > 1 then
      
          for i in 1 .. x_msg_count
          loop
             x_msg_data := fnd_msg_pub.get (i, fnd_api.g_false);
             dbms_output.put_line ('Error Message_' || i || ': ' || x_msg_data);
          end loop;
       else
          -- Commit the changes
          commit;  
       end if;
   end loop;  
exception
   when others then
      dbms_output.put_line('Error: '||sqlerrm); 
end;
---------------------------------------------------------