Oracle Apps: How to create validation for a date parameter using a value set for Oracle Concurrent Program

One of my colleague was looking for a solution to restrict a date parameter on a concurrent program not to allow future dates.This post is to just show how to do that as it could help someone else as well.

Step 1. 
Create a Special Value set as below

Format Type     : Standard Date
Validation Type : Special
 Event                : Validate

 
FND PLSQL "
DECLARE
  v_date DATE:= :!VALUE;
BEGIN
  IF v_date > SYSDATE THEN
    FND_MESSAGE.SET_NAME('FND','FND_GENERIC_MESSAGE');
    FND_MESSAGE.SET_TOKEN('MESSAGE','Please choose current or past date.');
    FND_MESSAGE.RAISE_ERROR;
  END IF;
END;
"

Step 2.
Attach this value set to the Concurrent Program Parameter.

Now try to enter a future date parameter while submitting the concurrent program.

Feel free to point out if anything is wrong/missing in this post

Oracle Apps: Apply Credit Memo against Debit Memo using API

This is some sample code which we have used in Release 12.1.3 instance to apply Credit Memo against Debit Memo in Bulk.

This code expects you already have the Customer Account Number, Credit Memo and Debit Memo details. You just need to replace the SELECT from DUAL section in the cursor and replace with your values.

DECLARE
  CURSOR c_get_trx IS
    SELECT t.account_number   account_number
          ,TO_NUMBER(t.amt)   apply_amount
          ,t.dm_number           dm_trx_number
          ,t.dm_date dm_trx_date
          ,psd.customer_trx_id dm_trx_id
          ,psd.payment_schedule_id dm_ps_id
          ,psd.amount_due_remaining dm_amt_remaining
          ,psd.status dm_status
          ,t.cm_number cm_trx_number
          ,t.cm_date cm_trx_date
          ,psc.customer_trx_id cm_trx_id
          ,psc.payment_schedule_id cm_ps_id
          ,psc.amount_due_remaining cm_amt_remaining
          ,psc.status cm_status
    FROM   (SELECT '2412315'                             "ACCOUNT_NUMBER"
                   ,'100.50'                             "AMT"
                   ,'95000'                              "DM_NUMBER"
                   ,TO_DATE('01-Jan-2018','DD-Mon-YYYY') "DM_DATE"
                   ,'130000'                             "CM_NUMBER"
                   ,TO_DATE('05-Jan-2018','DD-Mon-YYYY') "CM_DATE"
            FROM   dual) t
           ,hz_cust_accounts ca
           ,ar_payment_schedules psd
           ,ar_payment_schedules psc
    WHERE  ca.account_number = t.account_number
    AND    psd.customer_id = ca.cust_account_id
    AND    psd.class = 'DM'
    AND    psd.trx_number = t.dm_number
    AND    TRUNC(psd.trx_date) = t.dm_date
    AND    psc.customer_id = ca.cust_account_id
    AND    psc.class = 'CM'
    AND    psc.trx_number = t.cm_number
    AND    TRUNC(psc.trx_date) = t.cm_date ;
 
  x_application_id      NUMBER;
  x_amount_applied_from NUMBER;
  x_amount_applied_to   NUMBER;
 
BEGIN
  -- Apps Initialize
  mo_global.set_policy_context('S'
                              ,0);
  --Initilize with the Receivables Responsibility
  fnd_global.apps_initialize(1000
                            ,50000
                            ,222);
 
  FOR r_rec IN c_get_trx
  LOOP
    DBMS_OUTPUT.put_line('Account Number ' || r_rec.account_number);
    DBMS_OUTPUT.put_line('Applying Credit Memo: ' || r_rec.cm_trx_number || ' to Debit Memo : ' || r_rec.dm_trx_number);
 
    IF NVL(r_rec.dm_status
          ,'X') != 'OP'
    THEN
      DBMS_OUTPUT.put_line('*****ERROR: Debit Memo is not Open. This cannot be applied.');
      CONTINUE;
    END IF;
 
    IF r_rec.dm_amt_remaining < r_rec.apply_amount
    THEN
      DBMS_OUTPUT.put_line('*****ERROR: Amount to be applied > Remaining amount on Debit Memo.');
      CONTINUE;
    END IF;
 
    BEGIN
      arp_process_application.cm_application(p_cm_ps_id                  => r_rec.cm_ps_id
                                            ,p_invoice_ps_id             => r_rec.dm_ps_id
                                            ,p_amount_applied            => r_rec.apply_amount
                                            ,p_apply_date                => SYSDATE
                                            ,p_gl_date                   => SYSDATE
                                            ,p_out_rec_application_id    => x_application_id
                                            ,p_acctd_amount_applied_from => x_amount_applied_from
                                            ,p_acctd_amount_applied_to   => x_amount_applied_to
                                            ,p_ussgl_transaction_code    => NULL
                                            ,p_attribute_category        => NULL
                                            ,p_attribute1                => NULL
                                            ,p_attribute2                => NULL
                                            ,p_attribute3                => NULL
                                            ,p_attribute4                => NULL
                                            ,p_attribute5                => NULL
                                            ,p_attribute6                => NULL
                                            ,p_attribute7                => NULL
                                            ,p_attribute8                => NULL
                                            ,p_attribute9                => NULL
                                            ,p_attribute10               => NULL
                                            ,p_attribute11               => NULL
                                            ,p_attribute12               => NULL
                                            ,p_attribute13               => NULL
                                            ,p_attribute14               => NULL
                                            ,p_attribute15               => NULL
                                            ,p_global_attribute_category => NULL
                                            ,p_global_attribute1         => NULL
                                            ,p_global_attribute2         => NULL
                                            ,p_global_attribute3         => NULL
                                            ,p_global_attribute4         => NULL
                                            ,p_global_attribute5         => NULL
                                            ,p_global_attribute6         => NULL
                                            ,p_global_attribute7         => NULL
                                            ,p_global_attribute8         => NULL
                                            ,p_global_attribute9         => NULL
                                            ,p_global_attribute10        => NULL
                                            ,p_global_attribute11        => NULL
                                            ,p_global_attribute12        => NULL
                                            ,p_global_attribute13        => NULL
                                            ,p_global_attribute14        => NULL
                                            ,p_global_attribute15        => NULL
                                            ,p_global_attribute16        => NULL
                                            ,p_global_attribute17        => NULL
                                            ,p_global_attribute18        => NULL
                                            ,p_global_attribute19        => NULL
                                            ,p_global_attribute20        => NULL
                                            ,p_customer_trx_line_id      => NULL
                                            ,p_module_name               => 'RAPI'
                                            ,p_module_version            => 1.0);
 
      DBMS_OUTPUT.put_line('Successfully applied!!!');
 
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('*****ERROR: Unable to apply credit memo ' || r_rec.cm_trx_number || ': ' ||
                             app_exception.get_type || ':' || app_exception.get_code || ':' || app_exception.get_text);
    END;
  END LOOP;
  COMMIT;
END;

Feel free to point out if anything is wrong/missing in this post.