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