AR Module in Oracle Apps

LIST AR Tables

AR Tables
--------------------------------------
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_CUSTOMER_TRX_TYPE_ALL
RA_CUSTOMER_TRX_LINE_GL_DIST_ALL
RA_CUSTOMERS
RA_TERMS
RA_CUSTOMER_TRX_LINE_SALESREPS
AR_PAYMENT_SCHEDULES
AR_CUSTOMER_PROFILES
AR_CUSTOMER_PROFILE_CLASSES
AR_CASH_RECEIPTS
AR_MISC_CASH_DISTRIBUTIONS
AR_CASH_RECEIPT_HISTORY
AR_RECEIVABLE_APPLICATIONS
HZ_PARTIES
HZ_PARTY_SITES
HZ_CUST_ACCOUNTS
HZ_CUST_ACCOUNT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_LOCATIONS
HZ_CONTACT_POINTS
HZ_CUST_CONTACT_POINTS

-- Basic Query
-- Query Hot to find Customer Number , Site Number , Party Number From Invoice
-- Source : http://oracleappps.blogspot.com/search/label/AR

/* Formatted on 04/03/2019 19.13.39 (QP5 v5.256.13226.35510) */
SELECT rcta.trx_number,
       cust_acct.account_number customer_number,
       party_site.party_site_number site_number,
       hp.party_number
  FROM ra_customer_trx_all rcta,
       hz_cust_accounts cust_acct,
       hz_party_sites party_site,
       hz_cust_acct_sites_all acct_site,
       hz_cust_site_uses_all site_uses,
       hz_parties hp
 WHERE     rcta.bill_to_customer_id = cust_acct.cust_account_id
       AND rcta.bill_to_customer_id = acct_site.cust_account_id
       AND rcta.bill_to_site_use_id = site_uses.site_use_id
       AND cust_acct.cust_account_id = acct_site.cust_account_id
       AND acct_site.cust_acct_site_id = site_uses.cust_acct_site_id
       AND site_uses.site_use_code = 'BILL_TO'
       AND party_site.party_site_id = acct_site.party_site_id
       AND hp.party_id = cust_acct.party_id
       AND hp.party_id = party_site.party_id
--       AND rcta.trx_number = '11111111'

PL SQL - Declaring Procedure and Function in BLOCK PL SQL

How to add procedure and function in Block PL SQL ,
In This section we are learning how to create a procedure or a function in Initialization ( in DECLARE Section ) in ...
after that , how to call the procedure or the function in logic in body ( after begin) in oracle

declare 
        a number;
        b number;
        c number;
    procedure find_min (x in number, y in number , z out number) is 
    begin 
        if x < y then 
             z:= x;
        else 
            z:= y;
        end if;
    end;
    
    function find_max (x in number , y in number) return number 
    is 
        z number;
    begin 
        if x > y then 
           z:= x;
        else 
           z:= y;
        end if;
        return z;
    end;

begin 
    a := 23;
    b := 45;
    find_min(a , b , c);
    dbms_output.put_line ('Minimum number is '|| c);
    
    c := find_max(a , b);
    dbms_output.put_line ('Maximum number is ' || c);
end;
/

if we're running or execute the procedure block in above, so the output that is ....
-----------------------------------------------
Output : 
Minimum number is 23
Maximum number is 45

EBS Oracle - SQL Basic Query in Oracle Apps

here i am going to show you how to get data Information with basic query in EBS Oracle apps ,




AP_PAYMENT_HISTORY_ALL

SELECT DISTINCT TRANSACTION_TYPE FROM AP_PAYMENT_HISTORY_ALL

Transaction_Type
-----------------------------------
TRANSACTION_TYPE
PAYMENT CREATED
PAYMENT CLEARING
MANUAL PAYMENT ADJUSTED
PAYMENT ADJUSTED
PAYMENT CLEARING ADJUSTED
REFUND RECORDED
PAYMENT UNCLEARING
PAYMENT CANCELLED

PL/SQL Oracle - Collections in Records

declare 
    type numlist is table of number;
    n numlist := numlist(10,23,31,12,45,12,34,45,56,12);
    
    procedure print_numlist (the_list NumList) is output varchar2(128);
    begin 
        if n.count = 0 then 
     dbms_output.put_line ('no elements in collection..');
    else 
       for i in the_list.first .. the_list.last loop 
            output := output || nvl (to_char(the_list(i)) , 'NULL' ) || ' ';
            dbms_output.put_line ('data ke ' || i || ' => '|| to_char(the_list(i)));
       end loop;
       dbms_output.put_line (' Output => ' || output );
    end if;
    end;
begin 
    dbms_output.put_line ('tees type ');
    print_numlist (n);
    
end;
/

sdfgsfdg



-- source : https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/collections.htm#BABEDEGG

SQL Oracle - Fungsi Character

kali ini saya akan memberikan list dari fungsi yang di sediakan oleh oracle untuk memanipulasi Character ...

Function	Description	Example	Result
regexp_count	menghitung Jumlah Karakter	select regexp_count( 'belajaroracle', 'a' ) from dual	3
CONCAT	menggabungkan 2 karakter atau column	SELECT CONCAT('Mike ', 'Hunt') FROM dual;	Mike Hunt
SUBSTR,INSTR	mengambil character sebelum special character	SELECT NVL (SUBSTR ('ABC_BLAH', 0, INSTR ('ABC_BLAH', '_') - 1), 'ABC_BLAH') TES_SUBSTR1,        NVL (SUBSTR ('123434-4', 0, INSTR ('123434-4', '-') - 1), '123434-4') TES_SUBSTR2   FROM DUAL	ABC, 123434

Format Date 

-- Date and Time Format Model 
-- source : http://docs.oracle.com/cd/B12037_01/server.101/b10759/sql_elements004.htm#sthref478

-- blog reference for Date Function http://erporacleapps.blogspot.com/2013/04/how-to-work-with-date-functions.html

DATE AND TIME
SELECT TO_DATE('1998 05 20','YYYY MM DD') TES_DATE FROM DUAL
Output : 20/05/1998

select to_date (to_char ('01-JAN-16') || ' 23:59:59', 'DD-MON-RR HH24:MI:SS')  concat_date_with_time from dual;
Output  : 01/01/2016 23.59.59

SELECT TO_CHAR(TO_DATE('27-OCT-18', 'DD-MON-RR') ,'fmDay Month YYYY') as tesDate FROM DUAL;
Output : Saturday October 2018

NEXT_DAY 
select next_day('04-Dec-19','Wed') from dual;  -- 11/12/2019
select  to_char ( add_months( trunc(sysdate,'y'), level-1 ), 'Month' ) IN_Month , 
       add_months( trunc(sysdate,'y'), level-1 ) open_period,  
       last_day ( add_months( trunc(sysdate,'y'), level-1 ) )  close_period
       from dual 
connect by level <= 12;

Output : 

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Oracle XMLTable

kali ini saya akan memberikan sedikit ilmu mengenai oracle ,
di Oracle ada sebuah Fitur  Function yaitu XMLTable ....

pertama buat lah sebuah table

CREATE TABLE EMPLOYEES2
(
   id     NUMBER,
   data   XMLTYPE
);

setelah itu tambahkan data berupa data XML  ke dalam table yang telah di buat sebelumnya

INSERT INTO EMPLOYEES2
     VALUES (1, xmltype ('
    
        John
        Watson
        30
        johnwatson@sh.com
    
    
        Sherlock
        Homes
        32
        sherlock@sh.com
    
    
        Jim
        Moriarty
        52
        jim@sh.com
    
    
        Mycroft
        Holmes
        41
        mycroft@sh.com
    
'));

-- link surce : https://www.viralpatel.net/oracle-xmltable-tutorial/

Basic Query - Oracle EBS

berikut ini adalah cara membuat procedure dalam oracle

begin
   for i in 1 .. 6
   loop
      dbms_output.put_line ('Cetak angka [' || i || '].');

      if mod(i, 2) = 0 then
         continue;
      end if;

      dbms_output.put_line ('Second Statement, index is [' || i || '].');
   end loop;
end;
/

dalam oracle bla bla .. ini tulisan nya belum bereees yaaah..