XLA to GL Link

 https://plsqlquery.blogspot.com/2025/12/xla-to-gl-link.html

SELECT

            acr.cash_receipt_id,

            acr.receipt_number,

            acr.receipt_date,

           araa.amount_applied ,

           araa.apply_date ,

           araa.gl_date,

           araa.gl_posted_date,

           a.ae_header_id,

       a.accounting_date , a.gl_transfer_date , a.gl_transfer_status_code,

        d.name Journal_name,

        e.status,

        f.segment1||'-'||f.segment2||'-'||f.segment3||'-'||f.segment4||'-'||f.segment5||'-'||f.segment6||'-'||f.segment7 GL_Account, 

        e.accounted_dr , e.accounted_cr

        

      

        FROM

            ar_cash_receipts_all            acr,

            ar_receivable_applications_all  araa,

            xla_ae_headers a,

            xla_ae_lines b,

            gl_import_references c,

            gl_je_headers d,

            gl_je_lines e,

            gl_code_combinations f

            

                WHERE 1 = 1

        AND araa.cash_receipt_id = acr.cash_receipt_id

        and araa.set_of_books_id = acr.set_of_books_id

        AND acr.receipt_number =  'Advance SPR_ 666184- 1st' --p_receipt_number

        and a.event_id = araa.event_id

        and a.ledger_id = araa.set_of_books_id

        and b.code_combination_id = araa.code_combination_id

        and a.ae_header_id = b.ae_header_id

        and a.ledger_id = b.ledger_id

        and a.application_id = b.application_id

        and a.application_id = 222

        and c.gl_sl_link_id = b.gl_sl_link_id

        and c.gl_sl_link_table = b.gl_sl_link_table

        and c.je_header_id = d.je_header_id

        and d.ledger_id = d.ledger_id

        and d.je_header_id = e.je_header_id

        and e.je_line_num = c.je_line_num

        and e.ledger_id = d.ledger_id

        and f.code_combination_id = e.code_combination_id

FUNCTION ( Number Of Days between two dates excluding Sat and Sun ) - Oracle EBS R12

https://plsqlquery.blogspot.com/2019/07/function-number-of-days-between-two.html

create or replace function skip_sat_sunday_f(P_INV_REC_DATE in date ,P_CHECK_DATE in date ) return number is
v_num number ;
begin
select
count(*) into v_num
--mydate ,to_char(mydate,'Day') 
from 
(
select trunc (add_months (sysdate,-24),'YY') -1 + level as mydate from dual 
connect by level <= (select trunc (add_months (sysdate ,48),'YY') - trunc (sysdate,'YY') from dual)
)
where to_char(mydate,'Day') in ('Saturday ','Sunday   ')
and (mydate) between  to_Date(P_INV_REC_DATE) and to_Date(P_CHECK_DATE);
return v_num;
exception 
  when others then 
    v_num:=0;
 return v_num;
end  ;

The COMMIT Statement

https://plsqlquery.blogspot.com/2018/05/the-commit-statement.html

The COMMIT Statement is used to save data permanently into database. All DDL and DCL statements execute automatic commit. Commit is also executed automatically in some other cases. After DML statements you have to use COMMIT statement for permanently saving.

SQL> commit;

commit complete

The DELETE Clause

https://plsqlquery.blogspot.com/2018/05/the-delete-clause.html

The DELETE Statement is used to remove row from table.

How to remove ASLAM'S record from EMP table? Aslam's EMPNO is 1.

SQL> DELETE EMP where EMPNO=1;

row deleted

Example of DML,DDL, DCL and TCL Commands

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

SELECT – Retrieves data from a table
INSERT –  Inserts data into a table
UPDATE – Updates existing data into a table
DELETE – Deletes all records from a table

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database
ALTER – Alters objects of the database
DROP – Deletes objects of the database
TRUNCATE – Deletes all records from a table and resets table identity to initial value.

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

GRANT – Gives user’s access privileges to database
REVOKE – Withdraws user’s access privileges to database given with the GRANT command

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

COMMIT – Saves work done in transactions
ROLLBACK – Restores database to original state since the last COMMIT command in transactions
SAVE TRANSACTION – Sets a save point within a transaction

Using TRIM in PLSQL Query

https://plsqlquery.blogspot.com/2018/05/using-trim-in-plsql-query.html

The Oracle/PLSQL TRIM function removes all specified characters either from the beginning or the end of a string.

The syntax for the TRIM function in Oracle/PLSQL is:

TRIM( [ [ LEADING | TRAILING | BOTH ] trim_character FROM ] string1 )

LEADING

The function will remove trim_character from the front of string1.

TRAILING

The function will remove trim_character from the end of string1.

BOTH

The function will remove trim_character from the front and end of string1.

trim_character

The character that will be removed from string1. If this parameter is omitted, the TRIM function will remove space characters from string1.

string1

The string to trim.

Returns

The TRIM function returns a string value.

Note

• If you do not choose a value for the first parameter (LEADING, TRAILING, BOTH), the TRIM function will remove trim_character from both the front and end of string1.

select ('      PAKISTAN                ') from dual

      PAKISTAN       
         

select trim('      PAKISTAN                ') from dual
PAKISTAN


select length(('      PAKISTAN                ')) from dual
30


select length(trim('      PAKISTAN                ')) from dual
8


select length('PAKISTAN                 ') from dual
25


select length(trim('PAKISTAN                 ')) from dual
8


select TRIM(' '  FROM  '   tech   ') from dual
tech

-----------------------------------------------------------------------------------------------------------------

LEADING

select TRIM(LEADING '1' FROM '12300000')from dual
2300000


select TRIM(LEADING '0' FROM '000012300000')from dual
12300000

------------------------------------------------------------------------------------------------------------------

TRAILING

select TRIM(TRAILING '2' FROM 'Tech12') from dual
Tech1


select TRIM(TRAILING '1' FROM 'Tech1') from dual
Tech

--------------------------------------------------------------------------------------------------------------------

BOTH

select TRIM(BOTH '1' FROM '123Tech111') from dual
23Tech

Using Outer Joins in PLSQL Query

https://plsqlquery.blogspot.com/2018/05/using-outer-joins-in-plsql-query.html

We use Outer Join when mismatched Values to be displayed      or
Using Outer Joins to Return Records with No Direct Match

The missing rows can be returned if an outer join operator is used in the join condition. The Operator is a Plus sign enclosed in parenthesis (+) and it is placed on the "side" of the join that is deficient in information. The Operator has the effect of creating one or more null rows, to which one or more rows from the non-deficient table can be joined.

In the syntax:

table1.column = is the condition that joins (or relates) the tables together
table2.column(+) = is the outer join symbol, which can be placed either side of the WHERE clause  condition,but not on both sides.
(Place the outer join symbol following the name of the column in the table without the matching rows.)



select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO = G.DEPTNO(+)

EMPNO	ENAME	SAL	COMM	DEPTNO	DNAME
7934	MILLER	1300	(null)	10	ACCOUNTING
7782	CLARK	2450	(null)	10	ACCOUNTING
7839	KING	5000	(null)	10	ACCOUNTING
7876	ADAMS	1100	(null)	20	RESEARCH
7369	SMITH	800	(null)	20	RESEARCH
7902	FORD	3000	(null)	20	RESEARCH
7788	SCOTT	3000	(null)	20	RESEARCH
7566	JONES	2975	(null)	20	RESEARCH
7900	JAMES	950	(null)	30	SALES
7844	TURNER	1500	0	30	SALES
7654	MARTIN	1250	1400	30	SALES
7521	WARD	1250	500	30	SALES
7499	ALLEN	1600	300	30	SALES
7698	BLAKE	2850	(null)	30	SALES
1	ASLAM	1200	(null)	(null)	(null)

Placed (+) on the "side" of the join that is deficient (missing) in information. Aslam's record exists in EMP table, but NULL DEPTNO does not exists in DEPT table, so put (+) on DEPT side because DEPT side is missing information

----------------------------------------------------------------------------------------------------------

select E.EMPNO, E.ENAME, E.SAL, E.COMM, G.DEPTNO, G.DNAME from EMP E, DEPT G
where E.DEPTNO(+) = G.DEPTNO

EMPNO	ENAME	SAL	COMM	DEPTNO	DNAME
7782	CLARK	2450	(null)	10	ACCOUNTING
7934	MILLER	1300	(null)	10	ACCOUNTING
7839	KING	5000	(null)	10	ACCOUNTING
7788	SCOTT	3000	(null)	20	RESEARCH
7876	ADAMS	1100	(null)	20	RESEARCH
7566	JONES	2975	(null)	20	RESEARCH
7902	FORD	3000	(null)	20	RESEARCH
7369	SMITH	800	(null)	20	RESEARCH
7900	JAMES	950	(null)	30	SALES
7844	TURNER	1500	0	30	SALES
7654	MARTIN	1250	1400	30	SALES
7521	WARD	1250	500	30	SALES
7499	ALLEN	1600	300	30	SALES
7698	BLAKE	2850	(null)	30	SALES
(null)	(null)	(null)	(null)	40	OPERATIONS

Placed (+) on the "side" of the join that is deficient (missing) in information. Operations department DEPTNO 40 exist in DEPT table, but DEPTNO 40 is not assigned to any employee, so put (+) on EMP side because EMP side is missing information