API to apply Credit Memo to refund activity

    The following sample code will use the existing credit memo, and will create a credit memo application to issue a refund using AR_CM_APPLICATION_PUB.ACTIVITY_APPLICATION.

--  Turn on DBMS_OUTPUT to display messages on screen
set serveroutput on size 1000000
--  Declaration section
DECLARE
   l_dummy varchar2(240);
   l_customer_trx_id ra_customer_trx.customer_trx_id%type;
   l_msg_count number := 0;
   l_msg_data varchar2(20000) := null;
   l_return_status varchar2(1);
   l_application_ref_type varchar2(30);
   l_application_ref_id number;
   l_application_ref_num VARCHAR2(30);
   l_receivable_application_id NUMBER;



BEGIN
   --  Set the applications and organization context
   fnd_global.apps_initialize(1318,50559,222,0);
   mo_global.init('AR');
   mo_global.set_policy_context('S',204);
   --  provide CUSTOMER_TRX_ID of the credit memo
   l_customer_trx_id := 527710;
   --  call ACTIVITY_APPLICATION
   AR_CM_APPLICATION_PUB.ACTIVITY_APPLICATION(
     P_API_VERSION => 1.0,
     P_INIT_MSG_LIST => FND_API.G_TRUE,
     P_COMMIT => FND_API.G_FALSE,
     P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
     p_customer_trx_id => l_customer_trx_id,
     p_amount_applied => 200,
     p_applied_payment_schedule_id => -8,
     p_receivables_trx_id => 2579,
     p_apply_date => to_date('04/20/2011','MM/DD/YYYY'),
     p_apply_gl_date => to_date('04/20/2011','MM/DD/YYYY'),
     p_application_ref_type => l_application_ref_type,
     p_application_ref_id => l_application_ref_id,
     p_application_ref_num => l_application_ref_num,
     p_receivable_application_id => l_application_ref_id,
     X_RETURN_STATUS => l_return_status,
     X_MSG_COUNT => l_msg_count,
     X_MSG_DATA => l_msg_data);
   --  return information about the status of the API run
   FND_MSG_PUB.count_and_get (
   p_encoded => FND_API.g_false,
   p_count => l_msg_count,
   p_data => l_msg_data );
   dbms_output.put_line('Return Status ==> '||l_return_status);
   dbms_output.put_line('l_msg_count ==> '||l_msg_count);

   -- display messages from the message stack
   FOR I IN 1..L_MSG_COUNT LOOP
       DBMS_OUTPUT.PUT_LINE(SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254));
   END LOOP;
   IF l_return_status <> 'S' THEN
      NULL;
   ELSE
      dbms_output.put_line(' CM Application_id = '|| l_application_ref_id );
      -- You can issue a COMMIT; at this point if you want to save the created credit memo to the database
   END IF;
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line('exception error!');
   dbms_output.put_line(substr(sqlerrm, 1, 80));
   fnd_message.retrieve(l_dummy);
   dbms_output.put_line(l_dummy);
END;

How to create Credit Memo: API to create Credit Memo

Reference: Document 1272600.1 (oracle.com)

API to create Credit Memo Transaction

 Credit memo creation in Oracle application

AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST : It will create a request for a Credit Memo

AR_CM_APPLICATION_PUB.ACTIVITY_APPLICATION: It will apply the credit memo to the Refund activity.

set serveroutput on size 1000000
--  Declaration section
DECLARE
  l_dummy              varchar2(240);
  l_customer_trx_id    ra_customer_trx.customer_trx_id%type;
  l_line_credits_flag  ra_cm_requests.line_credits_flag%type;
  l_line_amount        number;
  l_freight_amount     number := 0;
  l_cm_lines_tbl       arw_cmreq_cover.cm_line_tbl_type_cover;
  l_cm_reason_code     varchar2(150);
  l_comments           varchar2(150);
  l_msg_count          number := 0;
  l_msg_data           varchar2(20000) := null;
  l_return_status      varchar2(1);
  l_request_id         NUMBER;
  l_batch_source_name  varchar2(150) default null;
  cm_trx_id            number;
BEGIN
   --  Set the applications and org context
   fnd_global.apps_initialize(1318,50559,222,0);
   mo_global.init('AR');
   mo_global.set_policy_context('S',204);

   --  provide CUSTOMER_TRX_ID of the invoice to credit
   l_customer_trx_id := 527689;
 
   l_comments := 'Demo for Create_Request API';
   --  Populate Batch Source information
   l_batch_source_name := 'TEST API';
   --  the presence of l_cm_lines_tbl indicates this is a LINE-LEVEL credit
   -- details for (1) are credits against line #1 identified by customer_trx_line_id = 824472
   l_cm_lines_tbl(1).customer_trx_line_id := 824472;
   l_cm_lines_tbl(1).quantity_credited := -1;
   l_cm_lines_tbl(1).price := 150.00;
   l_cm_lines_tbl(1).extended_amount := -150.00;
   -- details for (2) are credits against line #2 identified by customer_trx_line_id = 824473
   l_cm_lines_tbl(2).customer_trx_line_id := 824473;
   l_cm_lines_tbl(2).quantity_credited := -2;
   l_cm_lines_tbl(2).price := 200.00;
   l_cm_lines_tbl(2).extended_amount := -400.00;
   --  call CREATE_REQUEST
   AR_CREDIT_MEMO_API_PUB.CREATE_REQUEST(
      P_API_VERSION => 1.0,
      P_INIT_MSG_LIST => FND_API.G_TRUE,
      P_COMMIT => FND_API.G_FALSE,
      P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
      P_CUSTOMER_TRX_ID => l_customer_trx_id,
      P_LINE_CREDIT_FLAG => 'Y',
      P_CM_REASON_CODE => 'EXPIRED PRODUCT',
      P_CM_LINE_TBL => l_cm_lines_tbl,
      P_SKIP_WORKFLOW_FLAG => 'Y',
      P_CREDIT_METHOD_INSTALLMENTS => null,
      P_CREDIT_METHOD_RULES => null,
      P_BATCH_SOURCE_NAME => l_batch_source_name,
      P_ORG_ID => 204,
      X_REQUEST_ID => l_request_id,
      X_RETURN_STATUS => l_return_status,
      X_MSG_COUNT => l_msg_count,
      X_MSG_DATA => l_msg_data);
   --  return information about the status of the API run
   FND_MSG_PUB.count_and_get (
      p_encoded => FND_API.g_false,
      p_count => l_msg_count,
      p_data => l_msg_data );
   dbms_output.put_line('Return Status ==> '||l_return_status);
   dbms_output.put_line('Credit Memo request_id ==> '||l_request_id);
   dbms_output.put_line('l_msg_count ==> '||l_msg_count);
  
   --  display messages from the message stack
   FOR I IN 1..L_MSG_COUNT LOOP
       DBMS_OUTPUT.PUT_LINE(SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254));
   END LOOP;
   IF l_return_status <> 'S' THEN
      NULL;
   ELSE
     select cm_customer_trx_id
     into cm_trx_id
     from ra_cm_requests_all
     where request_id = l_request_id;
     dbms_output.put_line(' CM trx_id = '|| cm_trx_id );
     
COMMIT;
   END IF;
  
EXCEPTION
WHEN OTHERS THEN
   dbms_output.put_line('exception error!');
   dbms_output.put_line(substr(sqlerrm, 1, 80));
   fnd_message.retrieve(l_dummy);
   dbms_output.put_line(l_dummy);
END;
/

Check the Credit memo Created in base table:

SQL> select trx_number from ra_customer_trx_All where customer_trx_id = 527702;

How to apply credit memo to refund activity: Apply Credit Memo to refund activity

Reference:

Document 1272600.1 (oracle.com)

API To unapply regular Credit Memo

Below script unapply a regular Credit Memo from an invoice. API uses procedure AR_CM_APPLICATION_PUB.unapply_regular_cm.

DECLARE 

           p_api_version                               NUMBER;

           p_init_msg_list                             VARCHAR2(200);

           p_commit                                    VARCHAR2(200);

           p_validation_level                          NUMBER;

           x_return_status                             VARCHAR2(200);

           x_msg_count                                 NUMBER;

           X_MSG_DATA                                  varchar2(200);

           p_customer_trx_id                           NUMBER; 

           p_reversal_gl_date                          DATE;      

           p_called_from                               VARCHAR2(200);    

           p_org_id                                    NUMBER;

   p_cm_customer_trx_id      NUMBER; 

   p_cm_trx_number VARCHAR2(200);

p_app_customer_trx_id NUMBER; 

p_app_trx_number      VARCHAR2(200);

p_installment      NUMBER; 

p_applied_payment_schedule_id  NUMBER; 

p_receivable_application_id   NUMBER; 

p_reversal_gl_date   DATE;     

p_called_from VARCHAR2(200);

p_org_id              NUMBER; 

BEGIN

      BEGIN 

         --

               FND_GLOBAL.APPS_INITIALIZE (  1001, 1234, 222);

mo_global.set_policy_context('S',812);    

      END;

  

 AR_CM_APPLICATION_PUB.unapply_regular_cm(

p_api_version          =>  1.0,

p_init_msg_list             => FND_API.G_TRUE,

p_commit                    =>FND_API.G_TRUE,    

p_validation_level         =>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_cm_customer_trx_id       => 567655,

p_cm_trx_number      => 346346,

p_app_customer_trx_id =>   2455424,

p_app_trx_number => 346346,

p_installment =>1,

p_applied_payment_schedule_id  =>233235,

p_receivable_application_id =>2352353,

p_reversal_gl_date =>  '22-AUG-2022',

p_called_from =>Null,

p_org_id              =>204

);

IF (x_return_status = 'S') THEN

   COMMIT;

   

       dbms_output.put_line('SUCCESS');

       dbms_output.put_line('Return Status            = '|| SUBSTR (x_return_status,1,255));

       dbms_output.put_line('Message Count             = '||x_msg_count);

    dbms_output.put_line('Message Data            = '||x_msg_data);

    dbms_output.put_line('p_receivable_application_id   = '||p_receivable_application_id);   

ELSE     

   ROLLBACK;

   

   dbms_output.put_line('Return Status    = '|| SUBSTR (x_return_status,1,255));

   dbms_output.put_line('Message Count     = '|| TO_CHAR(x_msg_count ));

   dbms_output.put_line('Message Data    = '|| SUBSTR (x_msg_data,1,255));

   dbms_output.put_line(APPS.FND_MSG_PUB.Get ( p_msg_index    => APPS.FND_MSG_PUB.G_LAST,    

          p_encoded      => APPS.FND_API.G_FALSE));

  

   IF x_msg_count >=0 THEN

   

      FOR I IN 1..10 LOOP

               dbms_output.put_line(I||'. '|| SUBSTR (FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));

     

      END LOOP;

   END IF;

   

END IF;        

 EXCEPTION 

  WHEN OTHERS THEN

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

END;

Reference:

Document 1677223.1 (oracle.com)

Find responsibility name of a program

 Use below query to find the responsibility of a concurrent program

Select fcpt.user_concurrent_program_name,

frg.request_group_name,

frt.responsibility_name

From apps.fnd_Responsibility fr,

apps.fnd_responsibility_tl frt,

apps.fnd_request_groups frg,

apps.fnd_request_group_units frgu,

apps.fnd_concurrent_programs_tl fcpt

Where fcpt.user_concurrent_program_name =  <Insert Program name here>

and fcpt.concurrent_program_id = frgu.request_unit_id

and frgu.request_group_id = frg.request_group_id

and frg.request_group_id = fr.request_group_id

and fr.responsibility_id = frt.responsibility_id

Order By 2

;

Accounts Payable Trial Balance Report Query

 Report name: Accounts Payable Trial Balance

The standard summary report for AP Trial Balance is based on below query.

SELECT

summary_dat.definition_code,

summary_dat.ledger_id,

gl.name,

gl.short_name,

gl.currency_code,

''SUMMARY'',

summary_dat.source_application_id,

summary_dat.code_combination_id,

decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_unrounded_orig_amt ,0),

decode(gl.ledger_category_code,''PRIMARY'',summary_dat.SUM_acctd_rounded_orig_amt,0),

summary_dat.sum_entd_unrounded_rem_amount,

summary_dat.sum_entd_rounded_rem_amount,

summary_dat.sum_acctd_unrounded_rem_amount,

summary_dat.sum_acctd_rounded_rem_amount,

summary_dat.party_name,

summary_dat.party_id,

summary_dat.balancing_segment_value,

summary_dat.natural_account_segment_value,

summary_dat.cost_center_segment_value,

summary_dat.intercompany_segment_value,

summary_dat.management_segment_value,

gl.currency_code

FROM

    (

        SELECT

  tb.definition_code,

  tb.ledger_id,

  tb.source_application_id,

  tb.code_combination_id,

  SUM(tb.entered_unrounded_rem_amount) SUM_ENTD_UNROUNDED_REM_AMOUNT,

  SUM(tb.entered_rounded_rem_amount) SUM_entd_rounded_rem_amount ,

  SUM(tb.acctd_unrounded_rem_amount) SUM_acctd_unrounded_rem_amount ,

  SUM(tb.acctd_rounded_rem_amount) SUM_acctd_rounded_rem_amount ,

  SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_unrounded_orig_amt,

  SUM(nvl(tiv.base_amount,tiv.invoice_amount)) SUM_acctd_rounded_orig_amt,

  tiv.party_name,

  tb.party_id,

  tb.balancing_segment_value,

  tb.natural_account_segment_value,

  tb.cost_center_segment_value,

  tb.intercompany_segment_value,

  tb.management_segment_value

 FROM

  AP_SLA_INVOICES_TRANSACTION_V tiv,

  xla_transaction_entities xte,

  -- inline view

  ( SELECT /*+ parallel(xtb) leading(xtb) NO_MERGE */  --added hint bug#8409806 bug9133956

  xtb.definition_code,

  nvl(xtb.applied_to_entity_id,xtb.source_entity_id) entity_id,

  xtb.code_combination_id ,

  xtb.source_application_id,

  SUM (Nvl(xtb.entered_unrounded_cr,0)) -  SUM (Nvl(xtb.entered_unrounded_dr,0)) entered_unrounded_rem_amount,

  SUM (Nvl(xtb.entered_rounded_cr,0)) -  SUM (Nvl(xtb.entered_rounded_dr,0)) entered_rounded_rem_amount,

  SUM (Nvl(xtb.acctd_unrounded_cr,0)) -  SUM (Nvl(xtb.acctd_unrounded_dr,0)) acctd_unrounded_rem_amount,

  SUM (Nvl(xtb.acctd_rounded_cr,0)) -  SUM (Nvl(xtb.acctd_rounded_dr,0)) acctd_rounded_rem_amount,

  xtb.ledger_id,

  xtb.party_id,

  xtb.balancing_segment_value,

  xtb.natural_account_segment_value,

  xtb.cost_center_segment_value,

  xtb.intercompany_segment_value,

  xtb.management_segment_value

  FROM     xla_trial_balances xtb

  where    xtb.definition_code = :1

    and xtb.source_application_id=200

    and xtb.gl_date between :2 and :3

    AND NVL(xtb.party_id,-99)    = NVL(:4,NVL(xtb.party_id,-99))

      GROUP BY  xtb.definition_code,

    nvl(xtb.applied_to_entity_id,xtb.source_entity_id) ,

    xtb.code_combination_id ,

    xtb.source_application_id,

    xtb.ledger_id,

    xtb.party_id,

    xtb.balancing_segment_value,

    xtb.natural_account_segment_value,

    xtb.cost_center_segment_value,

    xtb.intercompany_segment_value,

    xtb.management_segment_value

     HAVING SUM (Nvl(xtb.acctd_rounded_cr,0)) <> SUM (Nvl(xtb.acctd_rounded_dr,0))

  ) tb

  --end of inline view

 WHERE tb.entity_id=xte.entity_id

 AND tb.source_application_id=200

 AND xte.entity_code=''AP_INVOICES''

 AND xte.application_id=tb.source_application_id

 AND nvl(xte.source_id_int_1,-99)=tiv.invoice_id

 GROUP BY

  tb.definition_code, tb.ledger_id, tb.source_application_id,

  tb.code_combination_id, tiv.party_name,tb.party_id,

  tb.balancing_segment_value, tb.natural_account_segment_value,

  tb.cost_center_segment_value, tb.intercompany_segment_value,

  tb.management_segment_value

    ) summary_dat ,

   gl_ledgers gl

 WHERE summary_dat.ledger_id=gl.ledger_id

Script to get the Password of user account

 You can use below script to get the existing password of Oracle EBS user account.

SELECT usertable.user_name ,

(SELECT get_pwd.decrypt (UPPER (

(SELECT

(SELECT get_pwd.decrypt (UPPER (

(SELECT UPPER (FND_WEB_SEC.GET_GUEST_USERNAME_PWD) FROM DUAL

)), usertable.encrypted_foundation_password)

FROM DUAL

) AS apps_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER (

(SELECT SUBSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD,1, INSTR (FND_WEB_SEC.GET_GUEST_USERNAME_PWD, '/')- 1)

FROM DUAL

))

)),usertable.encrypted_user_password)

FROM DUAL

) AS encrypted_user_password

FROM fnd_user usertable

WHERE usertable.user_name LIKE UPPER (<Insert username here>);

Script to reset Oracle Apps User Password

You can use below script to reset user password.

SET serveroutput ON;

DECLARE

  v_user_name    VARCHAR2(30):= UPPER(<Insert user name here>);

  v_new_password VARCHAR2(30):= 'Welcome1'; -- give preferred password here

  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;

Note:

This script do not work in case if user account is locked.