How to get first day and last date of week, month, quarter, year in Oracle

--First day of current week(sunday)
select TRUNC(SYSDATE, 'Day') from dual;
--First day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day') from dual;
--First day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day') from dual;
--First day of current month
select TRUNC(SYSDATE , 'Month') from dual;
--First day of previous month
select TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month') from dual;
--First day of next month
select TRUNC(LAST_DAY(SYSDATE)+1 , 'Month') from dual;
--First day of current year
select TRUNC(SYSDATE , 'Year') from dual;
--First day of previous year
select TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year') from dual;
--First day of next year
select ADD_MONTHS(TRUNC(SYSDATE , 'Year'),12) from dual;
-- First Day of Current quater 
select TRUNC(SYSDATE , 'Q') from dual;
--  First Day of Previous Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),-3) from dual;
--  First Day of Next Quarter
select ADD_MONTHS(TRUNC(SYSDATE , 'Q'),3) from dual;

--Last day of current week(sunday)
select TRUNC(SYSDATE, 'Day')+6 from dual;
--Last day of next week(sunday)
select TRUNC(SYSDATE+7 , 'Day')+6 from dual;
--Last day of previous week(sunday)
select TRUNC(SYSDATE-7 , 'Day')+6 from dual;
--Last day of current month
select LAST_DAY(TRUNC(SYSDATE , 'Month')) from dual;
--Last day of previous month
select LAST_DAY(TRUNC(TRUNC(SYSDATE , 'Month')-1 , 'Month')) from dual;
--Last day of next month
select LAST_DAY(TRUNC(LAST_DAY(SYSDATE)+1 , 'Month')) from dual;
--Last day of current year
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Year'),11)) from dual;
--Last day of previous year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),11)) from dual;
--Last day of next year
select LAST_DAY(ADD_MONTHS(TRUNC(TRUNC(SYSDATE , 'Year')-1 , 'Year'),-13)) from dual;
-- Last Day of Current quater 
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),2)) from dual;
--  Last Day of Previous Quarter
select TRUNC(SYSDATE , 'Q')-1 from dual;
--  Last Day of Next Quarter
select LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE , 'Q'),5)) from dual;

Value Set Link

select *
  from fnd_flex_values y, APPLSYS.FND_FLEX_VALUES_TL u
 where y.flex_value_set_id in
       (select p.flex_value_set_id
          from fnd_flex_value_sets p
         where p.flex_value_set_name = 'JGC_GL_Divisions')
   and y.flex_value_id = u.flex_value_id
   and y.flex_value = u.flex_value_meaning
   AND Y.SUMMARY_FLAG = 'N'

Inventory Period Close for All Organization through API

/*create or replace PROCEDURE inventory_close_api (
  
   p_company       IN       VARCHAR2,
 
   p_period_date    IN       date
)
AS*/

DEClare
  p_company         VARCHAR2(100) := 'JGC_July_June';
  p_period_date     date     := TO_DATE('06/01/2014','MM/DD/014');
  
   CURSOR ls_period_close (
      lc_company       VARCHAR2,
    
      ln_period_date    Date
   )
   IS
      SELECT b.organization_code, b.organization_name, a.*
        FROM org_acct_periods a, org_organization_definitions b
       WHERE a.period_set_name = lc_company
        
         AND a.period_start_date =  ln_period_date
         AND a.open_flag = 'Y'
         AND a.organization_id = b.organization_id;

   lc_return_status    VARCHAR2 (1);
   lc_msg_data         VARCHAR2 (2000);
   lc_errbuf           VARCHAR2 (2000);
   ln_msg_count        NUMBER;
   p_closing_rowid     VARCHAR2 (100);
   x_wip_failed        BOOLEAN;
   x_close_failed      BOOLEAN;
   x_download_failed   BOOLEAN;
   x_rec_rpt_launch_failed BOOLEAN;
   x_req_id            NUMBER;
   x_return_status     VARCHAR2 (100);
   ln_user_id          NUMBER;
   ln_org_id           NUMBER;
   ln_resp_id          NUMBER;
   ln_resp_app_id      NUMBER;
BEGIN
   dbms_output.put_line ('1st paramaeter - '
                      || p_company
                      || ' - 3rd Parameter - '
                      || p_period_date
                     );
   dbms_output.put_line ('WHSE'|| '       '|| 'PERIOD YR');
  
   ln_user_id := 1738;                  --:=APPS.FND_PROFILE.VALUE('USER_ID');
   ln_resp_id := apps.fnd_profile.VALUE ('RESP_ID');
   ln_resp_app_id := apps.fnd_profile.VALUE ('RESP_APPL_ID');
   ln_org_id := apps.fnd_profile.VALUE ('ORG_ID');

   BEGIN
      fnd_client_info.set_org_context (ln_org_id);
   END;

   BEGIN
      fnd_global.apps_initialize (1738, ln_resp_id, ln_resp_app_id);
   END;

   FOR ln_period_close IN ls_period_close (p_company,
                                           --p_fiscal_year,
                                           p_period_date
                                          )
   LOOP
      EXIT WHEN ls_period_close%NOTFOUND;
       cst_accountingperiod_pub.close_period
               (
               p_api_version                 => 1.0,
                p_org_id                      => ln_period_close.organization_id,
                p_user_id                     => ln_period_close.created_by,
                p_login_id                    => ln_period_close.last_update_login,
                p_closing_acct_period_id      => ln_period_close.acct_period_id,
                x_wip_failed                  => x_wip_failed,
                x_close_failed                => x_close_failed,
                x_req_id                      => x_req_id,
                x_unprocessed_txns            => x_download_failed,
                x_rec_rpt_launch_failed       =>   x_rec_rpt_launch_failed,             
                x_return_status               => x_return_status
               );
      dbms_output.put_line (ln_period_close.organization_code
                         || '               '
                         || ln_period_close.period_start_date
                        );

      IF lc_return_status <> 'S'
      THEN
         dbms_output.put_line ('STATUS : ' || lc_return_status);
         COMMIT;

         IF ln_msg_count <= 1
         THEN
            lc_errbuf := lc_errbuf || lc_msg_data;
         ELSE
            FOR ln_i IN 1 .. ln_msg_count
            LOOP
               lc_errbuf := lc_errbuf || ' ' || fnd_msg_pub.get (ln_i, 'F');
               dbms_output.put_line ( 'STATUS : ' || lc_errbuf);
            END LOOP;
         END IF;
      END IF;

      IF     ln_period_close.period_close_date IS NOT NULL
         AND ln_period_close.open_flag = 'Y'
      THEN
         dbms_output.put_line
                          ('Inventory period still open for - '
                           || ln_period_close.organization_code
                           || 'as there are pending transactions for the month.'
                          );
      ELSE
         dbms_output.put_line ('Inventory period closed for organization - '
                            || ln_period_close.organization_code
                           );
      END IF;
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      dbms_output.put_line ('Exception - Inventory Close API' || SQLERRM
                        );
END;

Change Pay Group through Procedure

CREATE OR REPLACE PROCEDURE SUP_TO_VEN AS

  CURSOR C1 IS
    SELECT *
      FROM AP_SUPPLIER_SITES_ALL APSA
     WHERE APSA.PAY_GROUP_LOOKUP_CODE = 'SUPPLIER';
VCOUNT NUMBER :=0;

BEGIN
  FOR I IN C1 LOOP
    UPDATE AP_SUPPLIER_SITES_ALL ASSA
       SET ASSA.PAY_GROUP_LOOKUP_CODE = 'EMPLOYEE'
     WHERE ASSA.VENDOR_ID = I.VENDOR_ID;
    COMMIT;
    VCOUNT := VCOUNT + 1;
  END LOOP;
  DBMS_OUTPUT.put_line('TOTAL RECORDS FOR EMPLOYEED = '||VCOUNT);
end;

Find Value Set Detail

Select a.Descriptive_Flexfield_Name,
       a.Application_Column_Name,
       a.End_User_Column_Name,
       a.Flex_Value_Set_Id,
       c.Flex_Value_Set_Name,
       a.Display_Size,
       b.Application_Table_Name,
       b.Value_Column_Name,
       b.Value_Column_Type,
       b.Value_Column_Size,
       b.Id_Column_Name,
       b.Id_Column_Type,
       b.Id_Column_Size,
       b.Meaning_Column_Name,
       b.Meaning_Column_Type,
       b.Meaning_Column_Size
  From Fnd_Descr_Flex_Column_Usages a,
       Fnd_Flex_Validation_Tables   b,
       Fnd_Flex_Value_Sets          c

 Where /*a.CREATION_DATE >= to_date('01-JAN-2015')
And  */
 a.Flex_Value_Set_Id = b.Flex_Value_Set_Id
 And c.Flex_Value_Set_Id = a.Flex_Value_Set_Id
 And a.Descriptive_Flexfield_Name = '$SRS$.FCI_PAYMENT_RECEIPT'
 Order By a.Creation_Date Desc