MS Excel - Add drop down list and add conditional coloring or formatting

Detailed Steps:

Select the cells of the field. Here, Status. >> click on data tab

Data validation

Validation criteria >> allow as List

Source: Provide the list values

See the list is ready.

Now we will add conditional coloring.

Select the cells >> conditional formatting 

Select New Rule

Select Format only cells that contain >> equal to >> specify one value . " Not started". >> Format.

Select a color.

Ok

See its ready for Not started. Similarly , we can add for other values and colors.

MS Excel - Formatting tips and tricks for better project management

5 Tips:

1. Starting with a clean sheet.
2. Formatting the title row
1. Dark cell with light text color and bold
2. Light cell with dark text color and bold
3. Avoid using RAG colors for your title
3. Table borders
1. Alternatively, use white no fill color, it gives default grid lines
4. Text alignment
1. Center for short and standarized data like status, date, completion %
2. Left for mor characters like task name, description and comments.
5. Extra space for title and total row

OIC - ERP - How to submit ESS job and get job status from Oracle Integration

Usecase: submit an ESS job and get job status from Oracle Integration

Highlevel steps:

• Create an Oracle ERP Cloud connection.

https://soalicious.blogspot.com/2023/03/oic-create-erp-cloud-adapter-connection.html

• Create an Scheduled orchestration integration and call the created oracle erp adapter with following operation
• submit ESS job request
• get ESS job status

Detailed steps with screenshots:

Integration Flow:

1. Take an assign and define all the parameters like ESS job definition name, job package name, paramters required etc
2. Drop the Oracle ERP cloud adapter and call operation as "submitESSJobRequest" of ErpIntegrationService and map the required fields.
3. Take another assign and define the parameters for the loop to check job status like inProgressStatus as "true" and essJobStatus as ''
4. Take a while loop with condition as inProgressStatus = "true"
5. Keep a wait for required time. Here, its 30 sec.
6. Drop the Oracle ERP cloud adapter and call operation as "getESSJobStatus" of ErpIntegrationService and map the Submit ess job request response result to request id of get Ess job status.
7. Take an assign and depending on the get ess job response , assign the ess job status and and inprogressstatus.

Note: used a customized javascript function to convert get ess job status to boolean true or false based on the loop iterates.

https://soalicious.blogspot.com/2021/03/oic-js-return-boolean-status-based-on.html

OIC ERP - BIP Report Bursting using Email Delivery Channel

Usecase: Here, we will see how to burst a BIP report using Email Delivery channel.

Highlevel steps:

1. Create a BIP data model and email busting query.
2. Create a BIP report based on step1 data model.
3. Create an ESS job to call the BIP report anx add properties
4. Run the ESS job from Scheduled processes section
5. OIC - Create an Oracle ERP Cloud connection
6. OIC - Create an integration and call the erp adpater and ErpIntegrationService and run the ESS job
7. ERP Email delivery channel configuration

Bursting query used:

Select

'00121' as "KEY",

'Template' TEMPLATE,

'en-US' LOCALE,

'CSV' OUTPUT_FORMAT,

'CONTROL_REPORT' || to_char(SYSDATE, 'yyyymmdd') OUTPUT_NAME,

'EMAIL' DEL_CHANNEL,

'ReceiverEmail@gmail.com' PARAMETER1,

NULL PARAMETER2,

'SenderEmail' PARAMETER3,

'Import Invoice Control Report for Source : '|| :p_source || to_char(SYSDATE,'yyyymmdd') PARAMETER4,

'Please find attached Control Report' PARAMETER5,

'true" PARAMETER6,

NULL PARAMETER7

FROM DUAL

Detailed steps with Screenshots:

Step1: Create Data model and bursting query.

Navigation >> Tools >> Reports & Analytics >> Browse Catalog >> Create >> Data Model >> SQL Query >> provide a name, data source as Application_FSCM and put the SQL query >> ok >> add the Parameter required >> under bursting -- add the split by and delivery by and SQL Query for bursting.

Step2: Create the BIP report

From that created data model >> create report

Save the report under Custom folders

Then edit the report >> view a list >> change the output formats and default format as required >> save

Open the report and test.

Step3: Create an ESS job and properties

Navigation >> Tools >> My Enterprise >> Setup and Maintenance >> search tasks with Manage Enterprise Scheduler Job Definitions and Job sets for Financial, SCM and..>>  click on (+) button to create new ESS job

Step4 : Run ESS job from scheduled processes

Navigation >> Tools >> Scheduled Processes >> Schedule New Process >> search the ESS job >> provide the parameter values >> submit the job >>once succeeded, we will receive an email containing the BIP report.

Step 5 & 6: Call the created ess job from OIC. Follow my below blog for the steps.

https://soalicious.blogspot.com/2023/04/oic-erp-how-to-submit-ess-job-and-get.html

Step 7: Emily delivery channel config.

Database - What is the use of Decode function in SQL?

In Oracle, Decode function allows us to add procedural if-then-else logic to the query. Decode compares the expression to each search value one by one, If expression is equal to a search, then the corresponding result is returned by the Oracle database. If a match is not found , then the default is returned.

Example:

Select bank_name, decode(bank_id l, 001, 'SBI', 002, 'ICICI',003,'Axis',Gateway) result from banks;

This above statement is equivalent to if-then-else statement as below:

IF bank_id = 001 THEN

result := 'SBI';

ELSIF bank_id = 002 THEN

result :='ICICI';

ELSIF bank_id =003 THEN

result := 'Axis';

ELSE

result := 'Gateway';

END IF