Monday, 23 April 2018

Create AR Transaction/Invoice adjustment(API)

Scope: To apply the adjustment in AR Invoice. (eg. To make AR Invoice amount roundoff).

  • Before RoundOff adjustment API.

Applying Adjustment through API.


up_adj_rec              ar_adjustments%rowtype;
up_api_name             VARCHAR2(20);
up_api_version          NUMBER;
up_called_from          VARCHAR2(10);
up_check_amount         VARCHAR2(1);
up_chk_approval_limits  VARCHAR2(1);
up_commit_flag          VARCHAR2(1);
up_init_msg_list        VARCHAR2(1);
up_move_deferred_tax    VARCHAR2(10);
up_msg_count            NUMBER;
up_msg_data             VARCHAR2(2000);
up_new_adjust_id        ar_adjustments.adjustment_id%type;
up_new_adjust_number    ar_adjustments.adjustment_number%type;
up_old_adjust_id        ar_adjustments.adjustment_id%type;
up_return_status        VARCHAR2(5);
up_validation_level     NUMBER;


 apps.mo_global.init ('AR');
 apps.mo_global.set_policy_context ('S',204);  --Pass valid Org_id 
 apps.fnd_global.apps_initialize(1318,50559,222); -- Initiliaze the application with valid value.

up_adj_rec              := NULL;
up_api_name             := NULL;
up_api_version          := 1.0;
up_called_from          := NULL;
up_check_amount         := NULL;
up_chk_approval_limits  := NULL;
up_commit_flag          := NULL;
up_init_msg_list        := FND_API.G_TRUE;
up_move_deferred_tax    := 'Y';
up_msg_count            := 0;
up_msg_data             := NULL;
up_new_adjust_id        := NULL;
up_new_adjust_number    := NULL;
up_old_adjust_id        := NULL;
up_return_status        := NULL;
up_validation_level     := FND_API.G_VALID_LEVEL_FULL;

/* api- data adjustments mapping record - start */

up_adj_rec.ACCTD_AMOUNT         := 0.09;        
up_adj_rec.ADJUSTMENT_ID        := NULL;
up_adj_rec.ADJUSTMENT_NUMBER    := NULL;
up_adj_rec.ADJUSTMENT_TYPE      := 'M';                 
up_adj_rec.AMOUNT               := 0.09;         
up_adj_rec.CREATED_BY           := -1;        
up_adj_rec.CREATED_FROM         := 'XXTEST_RCVRoundoff';
up_adj_rec.CREATION_DATE        := SYSDATE;
up_adj_rec.GL_DATE              := SYSDATE;
up_adj_rec.LAST_UPDATE_DATE     := SYSDATE;
up_adj_rec.LAST_UPDATED_BY      := -1;
--up_adj_rec.POSTING_CONTROL_ID   := -3;
up_adj_rec.SET_OF_BOOKS_ID      := 1;
up_adj_rec.STATUS               := 'A';
up_adj_rec.TYPE                 := 'CHARGES';              
up_adj_rec.PAYMENT_SCHEDULE_ID  := 561827;               
up_adj_rec.APPLY_DATE           := SYSDATE;
up_adj_rec.RECEIVABLES_TRX_ID   := 13910;        -- this is rec activity
up_adj_rec.CUSTOMER_TRX_ID      := 1263466;       --- Transaction for which adjustment is made

/*  api- data adjustments mapping record - End */

p_api_name              => up_api_name,
p_api_version           => up_api_version,
p_init_msg_list         => up_init_msg_list,
p_commit_flag           => up_commit_flag,
p_validation_level      => up_validation_level,
p_msg_count             => up_msg_count,
p_msg_data              => up_msg_data,
p_return_status         => up_return_status,
p_adj_rec               => up_adj_rec,
p_chk_approval_limits   => up_chk_approval_limits,
p_check_amount          => up_check_amount,
p_move_deferred_tax     => up_move_deferred_tax,
p_new_adjust_number     => up_new_adjust_number,
p_new_adjust_id         => up_new_adjust_id,
p_called_from           => up_called_from,
p_old_adjust_id         => up_old_adjust_id

DBMS_OUTPUT.PUT_LINE('New Adjustment Number: ' || up_new_adjust_number);
DBMS_OUTPUT.PUT_LINE('New Adjustment ID: ' || up_new_adjust_id);

IF up_msg_count >=1 THEN
FOR I IN 1..up_msg_count LOOP
dbms_output.put_line(I||'. '||SUBSTR(FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE ), 1, 255));


  • After  Round Off Apply. The Transaction possibly looks like this below.

Here the adjustment applied successfully and transaction amount has been rounded off.

Monday, 16 April 2018

Opening Modal Dialog page using JavaScript

In APEX, when there is a requirement for opening pages dynamically JavaScript must be used. Normal pages can be easily opened in JavaScript’s by mentioning the URL using window.location, but Modal Dialog cannot be opened. URL will not be framed correctly for Modal Dialog pages.

Below code can be used for opening dialog pages dynamically using JavaScript’s in APEX.

Sample Code:

/*Frame the URL parts separately for passing parameters dynamically.
URL with App_ID,Page_ID,Session_ID */
var c = "f?p=&APP_ID.:1398:&SESSION.";
/*Pass the Input Parameters with values
To escape the special characters which are not allowed in URL, JavaScript escape() function can be used. */
var d = '::NO::P1398_REGION_CODE,P1398_REPORT_OPTION,P1398_DPD_DAYS,P1398_ASSET_CLASS,P1398_REPORT_MONTH,P1398_CONTRACT_TYPE,P1398_SORT_ORDER,P1398_PREV_MNTH_MINUS_1,P1398_PREV_MNTH_MINUS_2,P1398_PREV_MNTH_MINUS_3,P1398_CONTRACT_FY_START_DATE,P1398_SEGMENT_CODE,P1398_SUB_SEGMENT,P1398_SEGMENT_CODE_NAME,P1398_SUB_SEGMENT_NAME:'+strRgnCode+","+$x("P1392_REPORT_OPTION").value+","+$x("P1392_DPD_DAYS").value+","+$x("P1392_ASSET_CLASS").value+","+$x("P1392_REPORT_MONTH").value+","+$x("P1392_CONTRACT_TYPE").value+","+"DPD_ASC"+","+$x("P1392_PREV_MNTH_MINUS_1").value+","+$x("P1392_PREV_MNTH_MINUS_2").value+","+$x("P1392_PREV_MNTH_MINUS_3").value+","+$x("P1392_CONTRACT_FY_START_DATE").value+","+$x("P1392_SEGMENT_CODE").value+","+$x("P1392_SUB_SEGMENT").value+","+escape(strSegmentName)+","+escape(strSubSegmentName);
/*For opening dialogs/pages using JavaScript apex.navigation.dialog must be used*/
var e = ("javascript:apex.navigation.dialog('f?p=&APP_ID.:1398:&SESSION.").length;
var f = c.slice(0,e)+d+c.slice(e);

Hiding Hidden column cells in IR using CSS

Select List Pagination in APEX IG

One main disadvantage of APEX Interactive Grid is the pagination. IG default pagination doesn’t have the option as classic Select List Pagination, instead it has page by page selection with Next and Previous icons.
However, the classic Select List pagination with X-Y of Z option in Interactive Grid can be achieved using the below code:


function(config) {
    config.defaultGridViewOptions = {
        rowHeader: "sequence",
        pagination: {
            showRange: true,
            showPageSelector: true,
            showPageLinks: false,
// uncomment to hide the first and last buttons
//            firstAndLastButtons: false
    config.defaultIconViewOptions = {
        collectionClasses: "t-Cards t-Cards--compact t-Cards--displayIcons u-colors t-Cards--desc-2ln"
    return config; // don't forget to return this!

Place the above code in the JavaScript section of the Grid Attributes.


Refresh IG on Region Display Selector Activation

Interactive Grid region within the region display selector must be refreshed every time on click/activation of the region display selector.
This can be achieved by using the below code there by replacing the complex java scripts:


$('.apex-rds').data('onRegionChange', function(mode, activeTab) { 
  if (activeTab.href != "#SHOW_ALL"){ 


Displaying Processing Spinners in APEX AJAX Process

When a process is called in APEX page without page submission (AJAX Process), user will have no visual feedback that something is happening.
For example if an AJAX (apex.server.process) “EMP_INSERT” is called in the page on click of LOAD button, a spinner can be displayed during the process.


Create a dynamic action on click of the “LOAD” button.

Action: Execute Javascript


"EMP_INSERT", {}, { pageItems: '#P9_E_ID', //Process to be called & Items to submit
dataType: 'text',
beforeSend: function() {
var lSpinner$ = apex.util.showSpinner(); //displays processing spinners
success: function(pData) {
setTimeout(function() {
$(document).ajaxStop( function(){ $(".u-Processing").hide();});
//hides the spinner
}, 2000);

If you want to display the same spinner on click link column redirects to a pop up page:

    "EMP_INSERT", {}, { pageItems: '#P9_E_ID',
        dataType: 'text',
        beforeSend: function() {

        success: function(pData) {
            setTimeout(function() {
            }, 2000);

//setTimeout is used to simulate a 2 second AJAX call.