List Tables Information Per Module in EBS Oracle Apps

HR ( Human Resource and Application Hierarchy )

HR_OPERATING_UNITS
HR_ORGANIZATION_UNITS                        -- TABLE IO - Inventory Organization
HR_ALL_ORGANIZATION_UNITS_TL        -- TABLE IO - Inventory Organization
HR_ALL_ORGANIZATION_UNITS    PK/ FK ( ORGANIZATION_ID, LOCATION_ID )
ORG_ORGANIZATION_DEFINITIONS     --
      Important Columns :
             (ORGANIZATION_ID,
              ORGANIZATION_CODE,
              ORGANIZATION_NAME,
              SET_OF_BOOKS_ID,
              CHART_OF_ACCOUNTS_ID,
              OPERATING_UNIT,
              LEGAL_ENTITY)
PER_SECURITY_PROFILES
PER_SECURITY_ORGANIZATIONS_V

ORG_ACCESS_V
FND_USER
MTL_PARAMETERS
PER_PEOPLE_F
PER_ALL_PEOPLE_F

-- query berikut adalah untuk menampilkan semua kode  OU
select  hoi.organization_id ou_id, hoi.org_information5 "kode OU"
from hr_organization_information hoi
where org_information_context = 'Operating Unit Information'

-- query berikut adalah untuk menampilkan kode io dan kode OU nya
select  hoi.organization_id io_id, hoi.org_information3 id_ou
from hr_organization_information hoi
where org_information_context = 'Accounting Information'



FND Tables
FND_TERRITORIES
FND_LOOKUPS
=========================================================
iProcurement module 

PON_AUCTION_ITEM_PRICES_ALL

PON_AUCTION_HEADERS_ALL

di Table PON_AUCTION_ITEM_PRICES_ALL ada kolom dengan nama REQUISITION_NUMBER,
ini adalah NO PR, PURCHASE_BASIS ada 2 pilihan GOODS dan SERVICES

AGENT = BUYER
AGENT_ID = BUYER_ID

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

AR ( Account Receivable ) 

AR_BATCH_SOURCES_ALL
AR_CASH_RECEIPTS_ALL
AR_CUSTOMERS                      -- Customer Informations
AR_RECEIVABLES_TRX_ALL
AR_MEMO_LINES_ALL_B
AR_ADJUSTMENTS_ALL       -- Adjustment Informations

AR_PAYMENT_SCHEDULES_ALL
RA_CUSTOMER_TRX_ALL
RA_CUSTOMER_TRX_LINES_ALL
RA_ADDRESSES_ALL

HZ_PARTIES
HZ_PARTY_PREFERENCES
HZ_ORGANIZATION_PROFILES

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

INV ( Inventory ) 

MTL_CATEGORIES

MTL_SYSTEM_ITEMS      -- Item Information / Table Master Item 

MTL_SYSTEM_ITEMS_B

MTL_MATERIAL_TRANSACTIONS

MTL_TRANSACTION_ACCOUNTS

MTL_TRANSACTION_TYPES
MTL_ITEM_CATEGORIES
MTL_ONHAND_QUANTITIES_DETAIL
MTL_UNITS_OF_MEASURE


SELECT MIC.INVENTORY_ITEM_ID,
       MIC.ORGANIZATION_ID,
       MIC.CATEGORY_SET_ID,
       MIC.CATEGORY_ID
  FROM MTL_ITEM_CATEGORIES MIC

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

OM ( Order Management ) 

OE_ORDER_HEADERS_ALL    -- OM Order header
OE_ORDER_LINES_ALL           -- OM Order lines

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

PO Tables ( Purchasing )

-- table PR (Purchase Requisitions to Purchase Order (PO) ) 

PO_VENDORS
PO_VENDOR_SITES_ALL

PO_REQUISITION_HEADERS_ALL       PK ( REQUISITION_HEADER_ID )

PO_REQUISITION_LINES_ALL              PK (REQUISITION_LINE_ID), FK ( REQUISITION_HEADER_ID )

PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_ALL             
       PK (PO_DISTRIBUTION_ID)
       FK (PO_HEADER_ID, PO_LINE_ID, LINE_LOCATION_ID,
              CODE_COMBINATION_ID, PO_RELEASE_ID, REQ_DISTRIBUTION_ID)
PO_HEADERS_ALL
   type_lookup_code  : PLANNED, CONTRACT, RFQ, BLANKET, QUOTATION, STANDARD
PO_LINES_ALL
PO_LINE_LOCATIONS_ALL       PK ( LINE_LOCATION_ID )   fk ( PO_HEADER_ID, PO_LINE_ID, PO_RELEASE_ID , ORG_ID (OU ID))
PO_RELEASES_ALL
PO_LOOKUP_CODES
PO_DOCUMENT_TYPES_ALL

PO_DOC_STYLE_HEADERS
PO_ACTION_HISTORY
RCV_SHIPMENT_HEADERS
RCV_SHIPMENT_LINES

SELECT DISTINCT pah.OBJECT_TYPE_CODE, pah.OBJECT_SUB_TYPE_CODE
  FROM PO_ACTION_HISTORY pah

-- Link from PR to PO
select prd.*
from po_req_distributions_all prd , po_distributions_all pd
where prd.distribution_id = pd.req_distribution_id

=========================================================
AP Tabkes - ( Account Payable )

AP_BATCHES_ALL
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_PAYMENTS_ALL
AP_ACCOUNTING_EVENTS_ALL
    Column
    - EVENT_TYPE_CODE :
                          EVENT_TYPE_CODE
                          INVOICE CANCELLATION
                          INVOICE
                          PAYMENT
                          INVOICE ADJUSTMENT
                          PREPAYMENT APPLICATION
                          PAYMENT CLEARING
                          PAYMENT ADJUSTMENT
                          PAYMENT CANCELLATION
                          PREPAYMENT UNAPPLICATION
                          PAYMENT UNCLEARING
    - SOURCE_TABLE : Values=AP_CHECKS, AP_INVOICES, AP_PAYMENT_HISTORY
AP_CHECKS_ALL
AP_SUPPLIERS
AP_SUPPLIER_SITES_ALL
AP_SUPPLIER_CONTACTS
AP_PREPAY_HISTORY_ALL
- COLUMNS
PREPAY_HISTORY_ID
PREPAY_INVOICE_ID
PREPAY_LINE_NUM
ACCOUNTING_EVENT_ID
HISTORICAL_FLAG
INVOICE_ID
INVOICE_ADJUSTMENT_EVENT_ID
ORG_ID
AP_PAYMENT_HISTORY_ALL
- COLUMNS
PAYMENT_HISTORY_ID
CHECK_ID
ACCOUNTING_DATE
TRANSACTION_TYPE  -- OPTION ('PAYMENT CREATED',  'PAYMENT CANCELLED')
POSTED_FLAG
MATCHED_FLAG
ACCOUNTING_EVENT_ID
ORG_ID

AP_BANK_ACCOUNTS_ALL



=========================================================
XLA Tables ( SubLedger Accounting - XLA ) 
-- link source : https://orafinappssetups.blogspot.com/2013/01/faq-on-transfer-to-gl-in-r12.html

XLA_AE_HEADERS
XLA_AE_LINES
GL_IMPORT_REFERENCES
GL_CODE_COMBINATIONS
GL_CODE_COMBINATIONS_KFV

Table Name = XLA_AE_HEADERS
         Columns = ACCOUNTING_ENTRY_STATUS_CODE
         -- Description : This column Represents accounting status of a transaction event...
         Option Values =
            IF ACCOUNTING_ENTRY_STATUS_CODE = F then event is Accounted Successfully
            IF ACCOUNTING_ENTRY_STATUS_CODE = N then event is still not processed
            IF ACCOUNTING_ENTRY_STATUS_CODE = I then event is failed
            IF ACCOUNTING_ENTRY_STATUS_CODE = R then event is in Error
            IF ACCOUNTING_ENTRY_STATUS_CODE = D then event is Draft Accounting Entry

XLA_AE_HEADERS =>
            the table shows the transfer status of the accounting data,
            after successful transfer to the GL and Journal Import :
            gl_transfer_status_code = 'Y'
            gl_transfer_date is not null
            group_id is not null

=========================================================
GL Tables ( General Ledger ) 


PK = Primary Key
FK = Foreign Key

Table Information in EBS Oracle Apps

Table HR ( Human Resources )

select oav.organization_id
     , oav.resp_application_id
     , oav.responsibility_id
     , oav.organization_name
     , oav.organization_code
     , oav.responsibility_name
     , oav.application_name
from org_access_v oav
where rownum <= 20

SQL Oracle - Fungsi Numeric

dalam oracle ada function yang berguna untuk memanipulasi data angka ,


1. ABS : Nilai Absolute dari nilai X
    SELECT ABS (-2.75) x, ABS (0) y, ABS (2.75) z FROM DUAL;
    Output :
    X         Y     Z
    2.75     0     2.75


 2. SIGN : Untuk Mengetahui apakah suatu nilai adalah positif, negatif atau nol...
     nilai balikan SIGN ada 3, yaitu :
     -    1  jika x > 0
     -    0  jika x = 0
     -   -1 jika x < 0

  select sign (-2.75) x, sign (0) y, sign (2.75) z from dual; 
     Output :
     X    Y Z
    -1     0    1


3. SQRT : Menghasilkan Nilai Akar Kuadrat dari Argument X,
                 -- nilai x tidak boleh negatif

    select sqrt (9), sqrt (6.25), sqrt (0) from dual;
    Output :
      sqrt(9)  sqrt(6.25)  sqrt(0)
        -----       -----       -----
         3        2.5         0

    -- LENGTH : Menghitung panjang karakter
    -- POWER   : berguna untuk melakukan operasi eksponensial ( pemangkatan )

/*
date di simpan sebagai nilai numerik, maka dapat dilakukan operati aritmatika seperti penjumlahan dan pengurangan terhadap data DATE tersebut..

table operasi aritmetika pada DATE

OPERASI                         HASIL            DESKRIPSI
DATE + NUMBER           DATE             menambahkan sejumlah hari terhadap DATE
DATE - NUMBER            DATE             mengurangkan sejumlah hari dari DATE
DATE - DATE                  NUMBER      menghitung selisih hari antara dua Tanggal

*/

AP Module in EBS Oracle Apps

Basic of Account Payable module in Oracle Apps
-----------------------
concept of AP Module by Technically ,
I am gonna to explain from Table Information AP Module and basic Knowledge you must understand

Table Name : AP_CHECKS_ALL

select distinct payment_type_flag from AP_CHECKS_ALL

Important columns : 
   AMOUNT : Payment Amount
   BANK_ACCOUNT_ID : longer used
   BANK_ACCOUNT_NAME : Payment identifier
   CHECK_ID NUMBER : Payment number
   CHECK_NUMBER : Supplier name
   VENDOR_NAME : Supplier name
   VENDOR_SITE_CODE : Supplier site code
   PAYMENT_TYPE_FLAG : Type of payment
   A = Payment Process Request
   M = Manual
   Q = Quick Payment
   R = Reunfund

-- source => http://oracleapps88.blogspot.com/2011/11/apchecksall.html

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