Archive for the ‘Oracle’ Category

How To Get Last Record From The Table In Oracle Sql

October 30th, 2022, posted in Oracle Queries
Share

select * from (
  select * from oe_order_headers_all
   order by creation_date desc
  )
 where rownum = 1;

Share

AP_SUPPLIERS , AR_CUSTOMERS DETAILS QUERY IN ORACLE APPS R12

October 16th, 2022, posted in Oracle Queries
Share
AP_SUPPLIERS MASTER DETAILS QUERY:-
*****************************************

select  a.SEGMENT1 vendor_code,    
       b.VENDOR_ID,
       a.VENDOR_NAME,
       b.VENDOR_SITE_ID,
       (select organization_id
         from hr_operating_units
        where organization_id=b.ORG_ID
       )ou_id,
        (select name
         from hr_operating_units
        where organization_id=b.ORG_ID
       )operating_name,
       b.VENDOR_SITE_CODE,
       (b.ADDRESS_LINE1||b.ADDRESS_LINE2||b.ADDRESS_LINE3||b.CITY||b.STATE||b.ZIP) address,
       null GST_REGISTRATION_NO,    
       c.PAN_NO,
       b.state
  from ap_suppliers a,
     ap_supplier_sites_All b,
     JAI_AP_TDS_VENDOR_HDRS c,
     JAI_CMN_VENDOR_SITES d
where a.vendor_id=b.vendor_id
  and b.VENDOR_ID=c.vendor_id(+)
  and b.VENDOR_SITE_ID=c.VENDOR_SITE_ID(+)
  and b.VENDOR_ID=d.vendor_id(+)
  and b.VENDOR_SITE_ID=d.VENDOR_SITE_ID(+)
   AND (NVL(A.ENABLED_FLAG,'N')='Y' OR NVL(D.INACTIVE_FLAG,'N') <> 'N')
  and b.INACTIVE_DATE is  null
  and a.end_date_active is null
  AND A.SEGMENT1 = 'PRMAAA1'
 order by a.VENDOR_NAME,b.VENDOR_SITE_CODE,b.ORG_ID

AR_CUSTOMERS MASTER DETAILS QUERY:-
**********************************************

select hp.party_number customer_number,
       hca.cust_account_id customer_id,
       hp.party_name customer_name,
       hca.account_number customer_account_number,
       hcas.org_id ou_id,
       (select name from hr_operating_units where organization_id = hcas.org_id) operating_unit_name,
       HCAS.PARTY_SITE_ID CUSTOMER_SITE_ID,
        HPS.PARTY_SITE_NUMBER CUSTOMER_SITE_NUMBER,
       (   HL.ADDRESS1
        || HL.ADDRESS2
        || HL.ADDRESS3
        || HL.ADDRESS4
        || HL.CITY
        || HL.POSTAL_CODE)
          ADDRESS,
       NULL GST_REGISTRATION_NO,
       jac.PAN_NO PAN_NO,
       hl.state,
       HP.PARTY_TYPE CUSTOMER_TYPE
from hz_cust_acct_sites_all hcas,
              hz_cust_accounts_all hca,
              hz_parties hp,
              hz_party_sites hps,
              hz_locations hl,
              JAI_CMN_CUS_ADDRESSES jac
where 1=1
--and hca.account_number = 'RDAAAG2'
and hca.cust_account_id = hcas.cust_account_id
and hca.party_id = hp.party_id
and hcas.party_site_id = hps.party_site_id
and hca.party_id = hps.party_id
and hps.location_id = hl.location_id
and jac.address_id(+) = hcas.CUST_ACCT_SITE_ID
order by customer_name
Share

What Is The Difference Between 11i And R12 In Oracle Apps

September 25th, 2022, posted in Oracle
Share

11i
1) Consist of 3C
2) No concept of MOAC.
3) Major table changes like po_vendors.
4) Banks are created in Account Payables.
5) Ap_Invoices_lines_all table in finance is not there.

R12
1) Consist of 4C
2) Introduction of MOAC( Multi org access control)
3) Major table changes (Ap_suppliers)
4) Banks are created in Cash Management.
5) Subledger Accounting is not there.
5) Ap_invoices_lines_all is there.
6) Subledger Accounting is there.

Share

What are the Aggregate Functions in Oracle SQL

September 18th, 2022, posted in Oracle
Share

Aggregate Functions

MIN returns the smallest value in a given column
MAX returns the largest value in a given column
SUM returns the sum of the numeric values in a given column
AVG returns the average value of a given column
COUNT returns the total number of values in a given column
COUNT(*) returns the number of rows in a table
Share

Find the Seeded Table,Transactional Tables, Static Data

September 11th, 2022, posted in Oracle EBS Application, Oracle Queries
Share

You can tell based on the TABLESPACE the table belongs to – this is the new Oracle Applications Tablespace Model (OATM)

Run the following code to see:

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%SEED%' -- seeded data
AND table_name LIKE 'FND%'

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%TX%' -- transaction data
AND table_name LIKE 'FND%'

SELECT tablespace_name, table_name
FROM all_tables
WHERE tablespace_name LIKE '%ARCHIVE%' -- static data
AND table_name LIKE 'FND%'
Share