Oracle Apps: Basic Tables

Business Groups [Only]: per_business_groups

Operating Units [Only]: hr_operating_units

Inventory Orgs [Only]: org_organization_definitions

---------------------------------------------------
BG/OU/IO: hr_all_organization_units
---------------------------------------------------

Database Not Open Error

Resolving :

C:\Users\abhishek>sqlplus sys/*****@localhost:1521/XEPDB2 as SYSDBA

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Mar 19 16:39:29 2019
Version 18.4.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.


Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

SQL> alter database open;

Database altered.

Oracle Fusion Financials Tables and Views Link

Important Links:

Tables and Views
https://docs.oracle.com/cloud/farel12/financialscs_gs/OEDMF/toc.htm

Tables:

https://docs.oracle.com/cloud/farel12/financialscs_gs/OEDMF/TablesOverview.htm#TablesOverview

Views:

https://docs.oracle.com/cloud/farel12/financialscs_gs/OEDMF/ViewsOverview.htm#ViewsOverview

plsql authid current_user explained

====================

There are two types of rights on procedure

1. definer [by default]
2. invoker [can be used by adding authid current_user]

---------------------
Lets take an example, there are two users , user1, user2, both have defined temp table.

user1/user 1                        user2/user2/u

table: temp                         table: temp
empid empname                empid empname
100   smith                         100   johns
----------------------------------------------------------
create a procedure in user1 [schema]

create or replace procedure get_user
is
v_user varchar2(100);
begin
select empname into v_user from temp;
dbms_output.put_line(v_user);
end;

grant this procedure to user2 as well.
> grant execute on get_user to user2;
---------------------------------------------------------------

when you run it from user1> you get smith.
when you run it from user2> you get smith.

reason: since proc is run by definer rights by default.
--------------------------------------------------------------

to get johns printed:

you need to define as below in user1: so that while executed, it will be executed with invoker rights

create or replace procedure get_user authid current_user
is
v_user varchar2(100);
begin
select empname into v_user from temp;
dbms_output.put_line(v_user);
end;

==============================================================

We always create package and proc in custom schema, and then use those pkgs on schema's like HR and GL etc, then if don't use authid current_user, your proc will give undesired results because of above reason.

Refer: https://www.youtube.com/watch?v=L8YYimP7cAQ&list=PLVlQHNRLflP-B-e7daJkgKPS_vmya5mY6&index=13&t=0s

plsql utl_file put_line, get_line

UTL_FILE
==================================================================
-> get data from file
-> upload data to file.
==================================================================
Pre-Req
===================================================================
1. using system user, grant user to create dir.  [login to sys]
grant create any directory to xxdbuser

2. create a alias directory [logical dir]
 create or replace directoty daily_rate as '/u01/ebs/rate';
 create or replace directoty daily_rate as 'C:\';

3. give read and write priv on directory [from sys as sysdba]
  grant read, write on directory daily_rate to xxdbuser;

=================================================================

1. write data into file
    a. putf()
    b. put_line()
 
        1. use file pointer variable : declare file_type [declare section] this var will hold the file
            fv_rate_file utl_file.file_type;
        2. open file: fopen(file_pointer,,)  [under begin]
            fv_rate_file := utl_file.fopen(<alias_dir>,<file_name>,<mode: r,w>)
            fv_rate_file := utl_file.fopen(daily_rate,gl_rate.csv,w)
        3. write data into file put_line or putf []    fp,'%s\n',<data>
            utl_file.putf(fv_rate_file,'content')
        4. close the file.
            utl_file.fclose(fv_rate_file)


2.  Read data from file
    a. get_line()
        1. define a file pointer variable.
            fv_rate_file  utl_file.file_type;
            v_line        varchar2(2000);
        2. open file fopen
            fv_rate_file := utl_file.fopen(<alias_dir>,<file_name>,<mode: r>)
        3. fetch data from file.
            utl_file.get_line(fv_rate_file,v_line)
        4.  close the file.
            utl_file.fclose(fv_rate_file) 
         

Common Error for long text in single line:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148

This error appear when output string in a line is very long.  The FOPEN max_linesize parameter must be a number in the range 1 and 32767. If unspecified, Oracle supplies a default value of 1024. Recommendation is, use "max_linesize => 32767" for each utl_file.fopen. In the above case I have added the maximum value to overcome the error.
 fid UTL_FILE.FILE_TYPE := UTL_FILE.FOPEN (v_location, v_filename , 'W', 32767);

Handle oracle.xdo11g.xqxp.XQException at oracle.xdo11g.xslt.XSLStylesheet.flushErrors

Data Model

Receipts -----> Receipt Accounting
[G_MAIN] ---->[G_DIST]


<?for-each-group:G_RECEIPT?>

Line	Chart of Account	Description	Debit Amount	Credit Amount
<?for-each:G_DIST?> SL	CHART_OF_ACCOUNTS	CHAR_OF_ACC_DESC	0.00	0.00 <?end for-each?>

Amount

xyz

xyz

--xyz

<?sum(./G_DIST[DEBIT_AMOUNT!='']/DEBIT_AMOUNT)?>
<?end for-each-group?>

Here to fetch the sum of debit amounts : you need to write below (./ mean current group, and within current group of G_MAIN, we have another group G_DIST, and we will sum DIST_AMOUNT)
<?sum(./G_DIST/DEBIT_AMOUNT)?>

If you get error, that mean debit or credit amounts are null.

oracle.xdo.XDOException: oracle.xdo11g.xqxp.XQException: FORG0001
at oracle.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:1300)
at oracle.xdo.template.fo.util.FOUtility.generateFO(FOUtility.java:308)
at oracle.xdo.template.FOProcessor.createFO(FOProcessor.java:2105)
at oracle.xdo.template.FOProcessor.generate(FOProcessor.java:1208)
at RTF2PDF2.runRTFto(RTF2PDF2.java:484)
at RTF2PDF2.runXDO(RTF2PDF2.java:343)
at RTF2PDF2.main(RTF2PDF2.java:235)
Caused by: oracle.xdo11g.xqxp.XQException: FORG0001
at oracle.xdo11g.xslt.XSLStylesheet.flushErrors(XSLStylesheet.java:1850)

Now to handle this issue, we need to add a condition as below [highlighted in Red]

<?sum(./G_DIST[DEBIT_AMOUNT!='']/DEBIT_AMOUNT)?>
<?sum(./G_DIST/DEBIT_AMOUNT)?>

Oracle AR Receipt Accounting SQL for fusion

SELECT glcc.segment1 company, glcc.segment2 LOCATION,
       glcc.segment3 cost_center, glcc.segment4 ACCOUNT,
       glcc.segment5 product, glcc.segment6 channel, glcc.segment7 project,
       gjh.posted_date posted_on_dt, gjh.je_source, gjh.je_category,
       gjb.NAME je_batch_name, gjh.NAME journal_name, '' je_seq_name,
       '' je_seq_num, gjl.je_line_num je_line, gjl.description je_line_descr,
       xal.entered_cr global_cr, xal.entered_dr global_dr,
       xal.currency_code global_cur,
       acra.receipt_number transaction_num,
       acra.receipt_date transaction_date,
       xal.accounting_class_code transaction_type, xal.accounted_cr local_cr,
       xal.accounted_dr local_dr, gl.currency_code local_cur,
       (NVL (xal.accounted_dr, 0) - NVL (xal.accounted_cr, 0)
       ) transaction_amount,
       gl.currency_code transaction_curr_code, gjh.period_name fiscal_period,
       gl.NAME ledger_name
  FROM ar_cash_receipts_all acra,
       xla_transaction_entities xte,
       xla_ae_lines xal,
       xla_ae_headers xah,
       gl_code_combinations glcc,
       gl_import_references gir,
       gl_je_lines gjl,
       gl_je_headers gjh,
       gl_ledgers gl,
       gl_balances gb,
       gl_je_batches gjb
 WHERE 1 = 1
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'RECEIPTS'
   AND NVL (xte.source_id_int_1, -99) = acra.cash_receipt_id
   AND xte.application_id = xal.application_id
   AND xte.entity_id = xah.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.application_id = xah.application_id
   AND xal.code_combination_id = glcc.code_combination_id
   AND xal.gl_sl_link_id = gir.gl_sl_link_id
   AND xal.gl_sl_link_table = gir.gl_sl_link_table
   AND gir.je_header_id = gjl.je_header_id
   AND gir.je_line_num = gjl.je_line_num
   AND gjl.je_header_id = gjh.je_header_id
   AND gjh.ledger_id = gl.ledger_id
   AND gb.code_combination_id = glcc.code_combination_id
   AND gb.period_name = gjh.period_name
   AND gb.currency_code = gl.currency_code
   AND gjh.je_batch_id = gjb.je_batch_id
   and ACRA.CASH_RECEIPT_ID = 300000005465113

NodeJs : Getting Started With

Day: 1

Using require, and importing standard nodejs module Filesystem and os available at(nodejs.org/api)

//this is the file we will be running from terminal

console.log('Starting Project')

//load standard node module from nodejs.org/api

const fs = require('fs');

const os = require('os');

fs.appendFile('Welcome.txt','Hello '+os.userInfo().username+' !',(error)=>{

if(error){

console.log('Unable to write')

}

})

fs.appendFile
-> first parameter: filename, it will create if it does not exist
-> 2nd parameter: text, which will be written to file
-> 3rd parameter: callback function: while processing in case of any error, error will be thrown

How to Define a Promise and call it in JavaScript

/*
How to define promises

1. create a new instance of promise
const myP = new Promise()

2. Promise constructor has a single arg, which is again a function
const myP = new Promise(function(){

})

3. Now this function has two agrs
const myP = new Promise(function(resolve, reject){

})

4. Now this function is called right away, where we can make http request or db connection and data fetch. Lets add a async settimeout
const myP = new Promise(function(resolve, reject){
    setTimeout(()=>{
        console.log('2 sec wait')
    },2000)
})


5. Pass a either reject or resolve into async call

const myP = new Promise(function(resolve, reject){
    setTimeout(()=>{
        resolve('2 Sec Time Over')
    },2000)
})


6. access myP using method then, which has 2 args [which are functions],  first is called for resolve, and 2nd is called for reject

myP.then(
    function(data){console.log(data)},
    function(err){console.log(err)}
    )

*/

Code:

const myPromise = new Promise(function(resolve, reject){
    setTimeout(() => {
        resolve("This is resolved")
    }, 2000);
})

myPromise.then(
    function(data){
    console.log(data)
    },
    function(err){
    console.log(err)    
    }       
)

How to call a XMLHttpRequest in javascript

1. We need a JSON Data provider

http://puzzle.mead.io/puzzle?wordCount=3

2.  Steps of Making a HTTP Request

• constructor function
• const req = new XMLHttpRequest()
• Initialize request [param: 1. Method: GET 2. URL ]
• req.open('GET','http://puzzle.mead.io/puzzle?wordCount=3')
• Send Request [it takes time to get response]
• req.send()
• https://developer.mozilla.org/en-US/docs/Web/API/XMLHttpRequest/readyState
• add event listener, event name: readystatechange,  
• req.addEventListener('readystatechange',(e)=>{
• if(e.target.readyState == 4){
• const data = JSON.parse(e.target.responseText)
• console.log(data)
• }
• }

Final Code:


const getMessage = function(){

const req = new XMLHttpRequest();

req.addEventListener('readystatechange',(e)=>{
if (req.readyState == 4){
const data  = JSON.parse(e.target.responseText)
console.log(data)
}
})

req.open('GET','http://puzzle.mead.io/puzzle?wordCount=3')
req.send();

}

How to define callback function in Java script

//How to define callbacks

Define two js files
1. apps.js
2. functions.js

apps.js, is the main js file, which will call many other functions, once of them may be functions.js
withing functions.js , we define  getMessage, and getMessage2 function.


/*apps.js : step by step we are building this function body
--first define a function
  getMessage2();

--define a function as parameter to this function.
  getMessage2(function(){})

--you may also write this as arrow function
  getMessage2(()=>{})

--this function [arrow function]is called callback function, which returns a value in parameter[this callback function body reside here in apps.js ]
  getMessage2((param1)=>{
      console.log(param1)
  })

--once the value is returned into this parameter from callback function, this will be printed 
============================================
functions.js

1. const getMessage2 = function(){}

--callback function passed as parameter here, which was defined in app.js
2. const getMessage2 = function(callback){}

--since callback function has a parameter[see apps.js]
3. const getMessage2 = function(callback){
    callback('Static Param value')
}

4. async functionality addition
const getMessage = function(callback){
    setTimeout(() => {
        console.log('timeout 2 sec')
        callback('Parameter Static Value')
    }, 2000);  
}
*/