R12 - SQL Query to get payment method for a vendor site

Unlike in 11i, the payment method code for a vendor site is no longer present in ap_supplier_sites_all table in R12. This is now moved to the Oracle Payments module tables.

The below query can be used to get the payment method code for a given vendor site id in R12:

SELECT ieppm.payment_method_code

FROM ap_supplier_sites_all assa,

   ap_suppliers sup,

   iby_external_payees_all iepa,

   iby_ext_party_pmt_mthds ieppm

WHERE sup.vendor_id                     = assa.vendor_id

AND assa.vendor_site_id                 = iepa.supplier_site_id

AND iepa.ext_payee_id                   = ieppm.ext_pmt_party_id

AND NVL(ieppm.inactive_date, SYSDATE+1) > SYSDATE

AND assa.vendor_site_id                 = :vendor_site_id

AND ieppm.primary_flag                  = 'Y'

AND assa.pay_site_flag                  = 'Y';

Using a sequence to default a value in a table column without using triggers

Often times, a default/unique value might be needed to be inserted into a column of a table which can then act as a primary or unique identifier.

The usual methodology will be to do that using a row level trigger which fires on INSERT and updates the column value with the sequence value.

SQL> create trigger tab1
  2  before insert on tab1
  3  for each row
  4  begin
  5    if (:new.x is null)
  6    then
  7       :new.x := s.nextval;
  8    end if;
  9  end;
 10  /
Trigger created. 

This can be replaced by the below method from Oracle Database 12c.

SQL> create sequence s;
Sequence created.
SQL> create table tab1
  2  ( x int
  3      default s.nextval
  4          primary key,
  5    y varchar2(30)
  6  );
Table created.

SQL> insert into tab1 (x,y)
  2  values ( default, 'hello' );
1 row created.

SQL> insert into tab1 (y)
  2  values ( 'world' );
1 row created.

SQL> select * from tab1;

         X  Y
  ————————  ————————
         1  hello
         2  world 

Oracle Applications - Not all Rows have been Retrieved. Data may be inaccurate

This is a warning to indicate that the workbook query extracts a huge number of records.

You can avoid this error by doing as below:

•  Click on Tools -> Options

•     In the Query Governor Tab, make sure the “Limit retrieved query data to” check box is unchecked.

•         Refresh the worksheet. You should not be getting the error now.

Oracle Applications - Getting Journals posted after a cut off time - Oracle GL

select '="'||to_char(to_date(h.period_name,'MON-YY'),'MON-YY')||'"' as period_name
                           ,'="'||to_char(h.posted_date,'DD-MON-YYYY HH24:MI:SS')||'"' as posted_date
                          --,to_char(h.posted_date,v_date_format) as posted_date
                         -- ,to_char(h.posted_date,'DD-MON-YYYY HH:MI:SS') as posted_date
                           ,u2.user_name     as Posted_by
                          ,replace(u2.description,',',NULL)   as Posted_by_name
                          ,replace(replace(replace(b.name,',',NULL),chr(10),NULL),chr(13),NULL) as batch_name
                          ,h.name             as journal_name
                          ,replace(replace(replace(h.description,',',NULL),chr(10),NULL),chr(13),NULL) as description
                          ,h.je_source
                          ,h.je_category
                          --,h.date_created
                          ,'="'||to_char(h.date_created,'DD-MON-YYYY HH24:MI:SS')||'"' as date_created
                          ,u1.user_name     as Created_by
                          ,replace(u1.description,',',NULL)     as Created_by_name
                    from apps.gl_je_batches    b
                        ,apps.gl_je_headers    h
                        ,apps.gl_je_categories cat
                        ,fnd_user                u1
                        ,fnd_user                u2
                    where b.je_batch_id      = h.je_batch_id
                      and   h.created_by      = u1.user_id
                      and h.last_updated_by = u2.user_id
                      and h.je_category     = cat.je_category_name
                      and h.set_of_books_id = 1
                      --and h.period_name     in ('JUN-10')
                      and h.period_name     = 'APR-12'
                      and h.status             = 'P'
                      and h.posted_date >= to_date('4-MAY-2012 21:00:00','DD-MON-YYYY HH24:MI:SS')
        order by h.posted_date;

Oracle Applications - Query to get all responsibilities assigned to all active users

select  distinct responsibility_name ,y.user_name
       ,y.description
      , z.employee_number
      , z.full_name,
       z.first_name,
       z.last_name
        from apps.fnd_user_resp_groups x , fnd_user y , per_people_f z ,fnd_responsibility_tl a
              where x.responsibility_id = a.responsibility_id    
    and  x.start_date <=sysdate and (x.end_date is null OR x.end_date > sysdate)
    and y.user_id = x.user_id
    and  y.start_date <=sysdate and (y.end_date is null OR y.end_date > sysdate)
    and z.employee_number = y.user_name
    and  z.effective_start_date <=sysdate
    and (z.effective_end_date  > sysdate or z.effective_end_date is NULL)
    and language = 'US'
    order by employee_number nulls first

Copying data along with the Column headers from Data grid in Toad

When you need to copy data from the Toad Data grid, CTRL+V will not copy the column headers. If you need the Column headers to be copied, press CTRL+INSERT to copy the headers as well.

SQL to get the Next or previous Weekend Date - NEXT_DAY

The below SQL gives the next Saturday and the next Sunday dates.


SELECT NEXT_DAY('06-MAR-2012', 'SATURDAY') "next saturday" 
,NEXT_DAY('06-MAR-2012', 'SUNDAY') "next sunday"
FROM dual;

If the requirement is to find the previous weekend dates, just subtract the dates by 7.


SELECT NEXT_DAY('06-MAR-2012', 'SATURDAY')-7 "next saturday" 
,NEXT_DAY('06-MAR-2012', 'SUNDAY')-7 "next sunday" 
FROM dual;