TABLE REGISTRATION IN ORACLE APPLICATION WITH EXAMPLE

DECLARE
   vc_appl_short_name   CONSTANT VARCHAR2 (40) := 'XXU';
   vc_tab_name          CONSTANT VARCHAR2 (32) := 'XXU_JOBWORK_LINES';
   vc_tab_type          CONSTANT VARCHAR2 (50) := 'T';
   vc_next_extent       CONSTANT NUMBER        := 512;
   vc_pct_free          CONSTANT NUMBER        := 10;
   vc_pct_used          CONSTANT NUMBER        := 70;
BEGIN   -- Start Register Custom Table
   -- Get the table details in cursor
   FOR table_detail IN (SELECT table_name, tablespace_name, pct_free, pct_used,
                              ini_trans, max_trans, initial_extent,
                              next_extent
                          FROM dba_tables
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register table
      ad_dd.register_table (p_appl_short_name => vc_appl_short_name,
                            p_tab_name        => table_detail.table_name,
                            p_tab_type        => vc_tab_type,
                            p_next_extent     => NVL(table_detail.next_extent, vc_next_extent),
                            p_pct_free        => NVL(table_detail.pct_free, vc_pct_free),
                            p_pct_used        => NVL(table_detail.pct_used, vc_pct_used)
                           );
   END LOOP; -- End Register Custom Table

   -- Start Register Columns
   -- Get the column details of the table in cursor
   FOR table_columns IN (SELECT column_name, column_id, data_type, data_length,
                               nullable
                          FROM all_tab_columns
                         WHERE table_name = vc_tab_name)
   LOOP
      -- Call the API to register column
      ad_dd.register_column (p_appl_short_name      => vc_appl_short_name,
                             p_tab_name             => vc_tab_name,
                             p_col_name             => table_columns.column_name,
                             p_col_seq              => table_columns.column_id,
                             p_col_type             => table_columns.data_type,
                             p_col_width            => table_columns.data_length,
                             p_nullable             => table_columns.nullable,
                             p_translate            => 'N',
                             p_precision            => NULL,
                             p_scale                => NULL
                            );
   END LOOP;   -- End Register Columns
   -- Start Register Primary Key
   -- Get the primary key detail of the table in cursor
   FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
                      FROM all_constraints
                     WHERE constraint_type = 'P' AND table_name = vc_tab_name)
   LOOP
      -- Call the API to register primary_key
      ad_dd.register_primary_key (p_appl_short_name      => vc_appl_short_name,
                                  p_key_name             => all_keys.constraint_name,
                                  p_tab_name             => all_keys.table_name,
                                  p_description          => 'Register primary key',
                                  p_key_type             => 'S',
                                  p_audit_flag           => 'Y',
                                  p_enabled_flag         => 'Y'
                                 );
      -- Start Register Primary Key Column
      -- Get the primary key column detial in cursor
      FOR all_columns IN (SELECT column_name, POSITION
                            FROM dba_cons_columns
                           WHERE table_name = all_keys.table_name
                             AND constraint_name = all_keys.constraint_name)
      LOOP
         -- Call the API to register primary_key_column
         ad_dd.register_primary_key_column
                                     (p_appl_short_name      => vc_appl_short_name,
                                      p_key_name             => all_keys.constraint_name,
                                      p_tab_name             => all_keys.table_name,
                                      p_col_name             => all_columns.column_name,
                                      p_col_sequence         => all_columns.POSITION
                                     );
      END LOOP; -- End Register Primary Key Column
   END LOOP;    -- End Register Primary Key

   COMMIT;
END;

What is the difference between 11i and R12 in Oracle Apps

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.

How to know what are the Seeded table,Transactional Tables, Static Data Tables in Oracle Applications

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%'

What are the Aggregate Functions in Oracle SQL

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

Aggregate functions are used to compute against a "returned column of numeric data" from your SELECT statement. They basically summarize the results of a particular column of selected data.

What is the difference between Lookup and Value Set

A "lookup type" consists of lookups that are static values in a list of values. Lookup code validation is a one to one match.

A table-validated "value set" may consist of values that are validated through a SQL statement, which allows the list of values to be dynamic.

Tip

You can define a table-validated value set on any table, including the lookups table. Thus, you can change a lookup type into a table-validated value set that can be used in flexfields.

Area of Difference	Lookup Type	Value Set
List of values	Static	Dynamic if the list is table-validated
Validation of values	One to one match of meaning to code included in a lookup view, or through the determinant of a reference data set	Validation by format or inclusion in a table
Format type of values	char	varchar2, number, and so on
Length of value	Text string up to 30 characters	Any type of variable length from 1 to 4000
Duplication of values	Never. Values are unique.	Duplicate values allowed
Management	Both administrators and end-users manage these, except system lookups or predefined lookups at the system customization level, which can't be modified.	Usually administrators maintain these, except some product "flexfield" codes, such as GL for Oracle Fusion General Ledger that the end-users maintain.

Both lookup types and value sets are used to create lists of values from which users select values.

A lookup type cannot use a value from a value set. However, value sets can use standard, common, or "set-enabled"lookups.

AP_SUPPLIERS , AR_CUSTOMERS DETAILS QUERY IN ORACLE APPS R12

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

https://mattermost.siba.ai/siba-demo/channels/town-square

Chat with your Siba Bot via email! Simply put your request in the subject and send email to sibabot@sirvisetti.com and you will get a reply within 5 minutes. Try this: To: sibabot@sirvisetti.com Subject: weather new york Body can be anything or left blank. You should receive an email back from Siba Bot within 5 minutes. Of course, when you get your own Siba, you can use your own email such as sibabot@mycompany.com and also set the reply delay (5 min is default but can be changed as needed). Make sure to try it today and post feedback/comments! http://siba.ai

OPM - GME_BATCH_HEADER Statuses Information

select * from all_objects
where object_name like 'GME%BATCH%'
and object_type = 'TABLE'
------------------------------------------------------------

SELECT o.ORGANIZATION_CODE, o.ORGANIZATION_NAME, o.ORGANIZATION_ID,
gbh.BATCH_NO,decode(gbh.BATCH_STATUS,3,'Completed',4,'Closed',2,'WIP',
'Just Created') Batch_Status,to_char(gbh.creation_date,'DD-MON-YYYY HH24:MI:SS AM')
FROM GME_BATCH_HEADER gbh,
ORG_ORGANIZATION_DEFINITIONS o
WHERE CREATION_DATE >=TO_DATE('01/05/2015','dd-mm-yy')
AND gbh.ORGANIZATION_ID=o.ORGANIZATION_ID
ORDER BY gbh.creation_date desc

--------------------------------------------------------------
select * from gme_batch_header
where batch_no = '1710000426'

BATCH_STATUS = 2 (WIP-Work In Process)
BATCH_STATUS = 1 (Pending)
BATCH_STATUS = 3 (Completed)
BATCH_STATUS = 4 (Closed)
BATCH_STATUS = -1 (Cancelled)
-------------------------------------------------------------------

How to fetch/get/retrive last record from the table in oracle sql

Example :

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