Set APEX select2 plugin Item Read Only

I have a form with some of the Select2 plugin items, I would like to set it to read only on the Edit page, here is how I do it:

 $('#s2id_P_ITEM').select2("readonly", true);

Replace P_ITEM with your own form Item

New version will be like this:

 $('#P_ITEM').select2("disabled", true);

Load AWS usage/billing data to Oracle

1. install aws cli and config using your ID and secret access key
2. setup cronjob in your database server to run following script:

#!/usr/bi/bash
billing_file_prex='XXXXX-aws-billing-detailed-line-items-with-resources-and-tags-'
mon=`date '+%Y-%m'`
billing_file="s3://cummins_aws_billing/$billing_file_prex$mon.csv.zip"
aws s3 cp $billing_file ./aws.csv.zip
unzip -o ./aws.csv.zip
mv "$billing_file_prex$mon.csv" aws.csv

Please replace XXXXX with your own account. You can also change the dir where you want to save your file.

3. In Oracle DB, create a dir EXT_TAB_DATA pointing to the file dir you use for the dowload

4. create an Oracle external table like this:

CREATE TABLE  "AWS_BILLING_EXTERNAL" 
   ( "INVOICEID" VARCHAR2(1000), 
 "PAYERACCOUNTID" VARCHAR2(1000), 
 "LINKEDACCOUNTID" VARCHAR2(2000), 
 "RECORDTYPE" VARCHAR2(1000), 
 "RECORDID" VARCHAR2(1000), 
 "PRODUCTNAME" VARCHAR2(2000), 
 "RATEID" VARCHAR2(1000), 
 "SUBSCRIPTIONID" VARCHAR2(1000), 
 "PRICINGPLANID" VARCHAR2(1000), 
 "USAGETYPE" VARCHAR2(200), 
 "OPERATION" VARCHAR2(1000), 
 "AVAILABILITYZONE" VARCHAR2(1000), 
 "RESERVEDINSTANCE" VARCHAR2(3000), 
 "ITEMDESCRIPTION" VARCHAR2(2000), 
 "USAGESTARTDATE" VARCHAR2(1000), 
 "USAGEENDDATE" VARCHAR2(1000), 
 "USAGEQUANTITY" VARCHAR2(1000), 
 "BLENDEDRATE" VARCHAR2(2000), 
 "BLENDEDCOST" VARCHAR2(3000), 
 "UNBLENDEDRATE" VARCHAR2(3000), 
 "UNBLENDEDCOST" VARCHAR2(1000), 
 "RESOURCEID" VARCHAR2(1000), 
 "AWS_CREATEDBY" VARCHAR2(3000), 
 "USER_APPLICATION" VARCHAR2(1000), 
 "USER_BILL_GROUP" VARCHAR2(1000), 
 "USER_ENVIRONMENT" VARCHAR2(1000), 
 "USER_NAME" VARCHAR2(1000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_TAB_DATA"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE ext_tab_data:'aws.bad'
    LOGFILE ext_tab_data:'aws.log'
    DISCARDFILE ext_tab_data:'aws-discarded.log'
    skip 1
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
        InvoiceID, 
 PayerAccountId, 
 LinkedAccountId, 
 RecordType, 
 RecordId, 
 ProductName, 
 RateId, 
 SubscriptionId, 
    PricingPlanId, 
 UsageType, 
 Operation, 
 AvailabilityZone, 
 ReservedInstance, 
 ItemDescription, 
 UsageStartDate, 
 UsageEndDate, 
 UsageQuantity, 
 BlendedRate, 
 BlendedCost, 
 UnBlendedRate, 
 UnBlendedCost, 
 ResourceId, 
 aws_createdBy, 
 user_Application, 
 user_Bill_Group, 
 user_Environment,
 user_Name
    )
                 )
      LOCATION
       ( 'aws.csv'
       )
    )
   REJECT LIMIT UNLIMITED
/

Load Azure Usage/Billing Data into Oracle

I need to download Azure usage/billing detail data into Oracle Table and build an APEX dashboard on top of it, here is what I did:

1. schedule a cronjob to run this script to get the Azure usage data file everyday, normally this will be on your database server:

import requests
import urllib2
import urllib
import csv
import os
import datetime

download_date=datetime.datetime.today().strftime('%Y%m')

ur='https://consumption.azure.com/v2/enrollments/your_enrollment_ID/usagedetails/download?'

mydict = {'billingPeriod': download_date}
url = ur + urllib.urlencode(mydict)

headers= {"Authorization":"Bearer XXXXXXXXX"}

billing_file='az.csv'
x = requests.get(url,headers=headers)
with open(os.path.join("/usr/users/your_dir", billing_file), 'wb') as f:
    f.write(x.content)

Replace XXXXX with your authorization key, which you can get from Azure.
Replace your_dir with your defined dir where you are going to download the csv file
Replace your_enrollment_ID with your own enrollment ID

2.  In Oracle DB, create a dir EXT_TAB_DATA pointing to the file dir you use for the dowload

3. create an Oracle External table similar to this:

CREATE TABLE  "AZURE_BILLING_EXTERNAL" 
   ( "ACCOUNTID" VARCHAR2(1000), 
 "ACCOUNTNAME" VARCHAR2(1000), 
 "ACCOUNTOWNEREMAIL" VARCHAR2(2000), 
 "ADDITIONALINFO" VARCHAR2(1000), 
 "CONSUMEDQUANTITY" VARCHAR2(1000), 
 "CONSUMEDSERVICE" VARCHAR2(2000), 
 "CONSUMEDSERVICEID" VARCHAR2(1000), 
 "COST" VARCHAR2(1000), 
 "COSTCENTER" VARCHAR2(1000), 
 "USE_DATE" VARCHAR2(200), 
 "DEPARTMENTID" VARCHAR2(1000), 
 "DEPARTMENTNAME" VARCHAR2(1000), 
 "INSTANCEID" VARCHAR2(3000), 
 "METERCATEGORY" VARCHAR2(2000), 
 "METERID" VARCHAR2(1000), 
 "METERNAME" VARCHAR2(1000), 
 "METERREGION" VARCHAR2(1000), 
 "METERSUBCATEGORY" VARCHAR2(2000), 
 "PRODUCT" VARCHAR2(3000), 
 "PRODUCTID" VARCHAR2(3000), 
 "RESOURCEGROUP" VARCHAR2(1000), 
 "RESOURCELOCATION" VARCHAR2(1000), 
 "RESOURCELOCATIONID" VARCHAR2(3000), 
 "RESOURCERATE" VARCHAR2(1000), 
 "SERVICEADMINISTRATORID" VARCHAR2(1000), 
 "SERVICEINFO1" VARCHAR2(1000), 
 "SERVICEINFO2" VARCHAR2(1000), 
 "STORESERVICEIDENTIFIER" VARCHAR2(1000), 
 "SUBSCRIPTIONGUID" VARCHAR2(1000), 
 "SUBSCRIPTIONID" VARCHAR2(1000), 
 "SUBSCRIPTIONNAME" VARCHAR2(1000), 
 "TAGS" VARCHAR2(2000), 
 "UNITOFMEASURE" VARCHAR2(1000)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "EXT_TAB_DATA"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY NEWLINE
    BADFILE ext_tab_data:'az.bad'
    LOGFILE ext_tab_data:'az.log'
    DISCARDFILE ext_tab_data:'az-discarded.log'
    skip 1
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL
    (
accountId,
accountName,
accountOwnerEmail,
additionalInfo,
consumedQuantity,
consumedService,
consumedServiceId,
cost,
costCenter,
use_date,
departmentId,
departmentName,
instanceId,
meterCategory,
meterId,
meterName,
meterRegion,
meterSubCategory,
product,
productId,
resourceGroup,
resourceLocation,
resourceLocationId,
resourceRate,
serviceAdministratorId,
serviceInfo1,
serviceInfo2,
storeServiceIdentifier,
subscriptionGuid,
subscriptionId,
subscriptionName,
Tags,
UnitOfMeasure
    )
             )
      LOCATION
       ( 'az.csv'
       )
    )
   REJECT LIMIT UNLIMITED
/




3. you now have the usage/billing data in Oracle table, so you can built your dashboard on top of it.



We are using Azure enterprise portal here, if you don't have enterprise portal for your billing, your url maybe different.

Preventing Apex Classic report csv download showing exponential

I have a Classic report with a Account_ID column where it has a big number, it shows ok but when download as csv and open it using  excel, it shows in exponential format instead of the original number, Here is how I fixed it like change the sql to something similar to this:

decode(INSTR(NVL(:REQUEST,'FOO'),'FLOW_EXCEL'),0,account_ID,'="'||account_ID||'"') account_id

So basically in the csv file, we will need to tell excel that to treat it as char by putting your number in quotes and proceeding with and equal sign, eg:

="001145",="55666",="02133"

Transfer user entered/LOV value to model dialog page and its title

I have a main report page(Page 1) where it has several LOV based parameter fields user use to apply to the underline report. It also has several button where each will pointing to a different model dialog page. What I what  is to transfer the use chosen value on the report page and apply their value to those model dialog page, the dialog page title also need to change dynamically based on the user chosen values. 
Here is how to do it:

1.  Create a hide item P1_DIALOG_TITLE, this will hold the value of the dialog title
2. On the report page(page 1),  create DA based on change of these parameter values, put three true actions:
 
    a. PL/SQL action, put 'null;' in the plsql.  This is to submit the user chosen values for those parameter
    b. put follow javascript into the second true action:
    3. Refresh the report action

var header_str
if ($v("P1_MONTH")!='') {
header_str='Billing Month:'+$v("P1_MONTH");
}
else {
header_str='Billing Month: Current Month';
}
if ($v("P1_BU")!='') {
header_str=header_str+" BU:"+$v("P1_BU");
}
if ($v("P1_BC")!='') {
header_str=header_str+' BC:'+$v("P1_BC");
}
if ($v("P1_RC")!='') {
header_str=header_str+' RC:'+$v("P1_RC");
}

$s('P1_DIALOG_TITLE',header_str)

In this case, I have 4 parameters: MONTH/BU/BC/RC

3. In the dialog page, apply the needed parameters to the sql section.
4. in the dialog page, put this in the Attribute of the page
   
   title: XXXX + $v('P1_DIALOG_TITLE')

Here XXX can be different title for different dialog page

Note. the dialog page will be able to share the same item from the Mail Page. I didn't realized this in the beginning, instead, I created separate hide items on the dialog page and tried to use the set value to carrier them over but that doesn't work

That's IT