Oracle: Get Month Start and End Dates for a Given Period

    SELECT   TRUNC (ADD_MONTHS ('01-JAN-19', LEVEL - 1), 'MM') start_date,
             LAST_DAY (ADD_MONTHS ('01-JAN-19', LEVEL - 1)) end_date
      FROM   DUAL
CONNECT BY   LEVEL <= CEIL (MONTHS_BETWEEN ('30-JUN-19', '01-JAN-19'))

Right Align a Column in a Table in OAF Page

Use following code in (processRequest):

 OATableBean tbl = (OATableBean)webBean.findChildRecursive("xxVO1");
         tbl.prepareForRendering(pageContext);
         DataObjectList dataformat = tbl.getColumnFormats();
         DictionaryData data = (DictionaryData)dataformat.getItem(pageContext.findChildIndex(tbl, "TableColumn"));
         data.put(UIConstants.CELL_NO_WRAP_FORMAT_KEY, Boolean.TRUE);

Making the Table rows Read Only in OAF Page

I have seen a lot of discussion on Oracle forums, where the user want to populate the data in read only mode but only specific rows.

Step -1: Create a transient attribute in the View Object attached to the Table of type Boolean.

Step -2: Create a SPEL.

Step -3: Attach this SPEL to the Read Only Property for the entire columns.

Step -4: Set the property at runtime for this Transient Attribute as TRUE or FALSE.


Write the below code in AM. This method is used to set the Read-only property to TRUE for each rows in the table.


------------- AM Code -----------------

    public void SetReadOnlyTrue() {

    OAViewObject var_vo = (OAViewObject)getXxVO1();

    if (var_vo!= null) {

    var_vo.executeQuery();

    int rowCountValue = var_vo.getRowCount();

    Row[] allRows = var_vo.getFilteredRows("XxAttribute","True");  /* Attribute in VO which specifies that the row is read only. */

    for ( int x= 0; x < allRows.length; x++ ){

    XxVORowImpl rowi = (XxVORowImpl)allRows[x];

    if(rowi!=null){

    rowi.setAttribute("TransientAttribute", Boolean.TRUE); }

    }

    }

    }

Call this method in controller (processRequest or processFormRequest).

How to check value(if it is null or not) in processFormRequest

if (pageContext.getParameter("ApplyBtn") != null){

         OAViewObject voh = (OAViewObject)am.findViewObject("XxVO1");
         try {
             voh.getCurrentRow().getAttribute("XxColumnName").toString();
         } catch (NullPointerException e) {
             throw new OAException("XxColumnName Name is Required", OAException.ERROR);
         }
}

Creating Lookup and Lookup Values through API

DECLARE
   err_code    VARCHAR2 (50);
   err_msg     VARCHAR2 (1000);
   ln_rowid    ROWID;
   ln_rowid1   ROWID;

   CURSOR C
   IS
        SELECT   CODE, NAMES
          FROM   XX_TABLE_NAME
         WHERE   LINE_STATUS <> 'INSERTED' OR LINE_STATUS IS NULL
      ORDER BY   1;
BEGIN
   fnd_lookup_types_pkg.insert_row (
      x_rowid                 => ln_rowid,
      x_lookup_type           => 'XX_TYPE',                      --Lookup Type
      x_security_group_id     => 0,                        --Security Group Id
      x_view_application_id   => 3,                  -- Application Id (0-AOL)
      x_application_id        => 50000,              -- Application Id (0-AOL)
      x_customization_level   => 'U',                                   --User
      x_meaning               => 'XX_MEANING',            --Meaning for Lookup
      x_description           => 'XX_DESCRIPTION',    --Description for Lookup
      x_creation_date         => SYSDATE,
      x_created_by            => -1,
      x_last_update_date      => SYSDATE,
      x_last_updated_by       => -1,
      x_last_update_login     => -1
   );


   FOR REC IN C
   LOOP
      BEGIN
         fnd_lookup_values_pkg.insert_row (
            x_rowid                 => ln_rowid1,
            x_lookup_type           => 'XX_TYPE',
            x_security_group_id     => 0,
            x_view_application_id   => 3,
            x_lookup_code           => rec.code,
            x_tag                   => NULL,
            x_attribute_category    => NULL,
            x_attribute1            => NULL,
            x_attribute2            => NULL,
            x_attribute3            => NULL,
            x_attribute4            => NULL,
            x_enabled_flag          => 'Y',
            x_start_date_active     => TO_DATE ('01-DEC-2015', 'DD-MON-YYYY'),
            x_end_date_active       => NULL,
            x_territory_code        => NULL,
            x_attribute5            => NULL,
            x_attribute6            => NULL,
            x_attribute7            => NULL,
            x_attribute8            => NULL,
            x_attribute9            => NULL,
            x_attribute10           => NULL,
            x_attribute11           => NULL,
            x_attribute12           => NULL,
            x_attribute13           => NULL,
            x_attribute14           => NULL,
            x_attribute15           => NULL,
            x_meaning               => REC.NAMES,             --Lookup Meaning
            x_description           => REC.NAMES,
            x_creation_date         => SYSDATE,
            x_created_by            => -1,
            x_last_update_date      => SYSDATE,
            x_last_updated_by       => -1,
            x_last_update_login     => -1
         );

         UPDATE   XX_TABLE_NAME
            SET   LINE_STATUS = 'INSERTED', ERROR_TEXT = ''
          WHERE   CODE = rec.CODE;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK;

            err_code := SQLCODE;
            err_msg := SUBSTR (SQLERRM, 1, 200);

            UPDATE   XX_TABLE_NAME
               SET   LINE_STATUS = 'ERROR',
                     ERROR_TEXT = err_code || ':' || err_msg
             WHERE   CODE = rec.CODE;

            COMMIT;

            DBMS_OUTPUT.put_line (SQLERRM);
      END;
   END LOOP;
END;

Popup Regions in R12.1.2 for Jdeveloper 10g OAF

Our objective is when a user hover its mouse over Employee name field a popup window will open that shows the details of that employee.

Step 1

For this we will create a query view of Employee name it as EmployeePopupVO for our popup region. After this create an external region with AM definition inside webui package as shown below name it as EmpSummaryPopupRN.

Step 2

Now in our EmployeeSearchPG page we will create a new flowLayout region and drag our EmpName messageStyleText field into it.

Step 3

Now under this flowLayout region EmpNameLayout we will create a region of style popup and set Region property to above external region (EmpSummaryPopupRN, created in Step1) name it as EmpSummaryPopup.

Now for the popup region to come up as soon as a user hover over EmpName field we need to set some properties of this field like


ID : Empname
Popup ID : EmpSummaryPopup
Popup Render Event : onHover
Popup Enable : True

After completing this run your page and hover over EmpName field you will see a new popup window coming on to your page as shown in starting of this article.

PO_UNIQUE_IDENTIFIER_CONT_ALL

PO_UNIQUE_IDENTIFIER_CONT_ALL stores information about the current, highest, system-generated numbers for the Oracle Purchasing tables that require special sequencing. You need one row for each sequentially system-generated number for each organization. The table includes rows for each of the following: purchase orders, requisitions, receipts, suppliers, quotations, and requests for quotations (RFQs). . For each organization, there are four rows for each of the following entities: PO_HEADERS_ALL, PO_REQUISITION_HEADERS_ALL, PO_HEADERS_RFQ and PO_HEADERS_QUOTE. There are two rows corresponding to the entities PO_VENDORS and RCV_SHIPMENT_HEADERS. . The information for the quotation and RFQ sub-entities is associated with the PO_HEADERS_ALL table entity. TABLE_NAME values for quotations and RFQs are 'PO_HEADERS_QUOTE' and 'PO_HEADERS_RFQ' respectively. . .

Registering Table through API in Oracle apps r12

BEGIN
AD_DD.REGISTER_TABLE ('FND','XXCUST_KFF_DEMO','T');
END;
/
BEGIN
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','CODE_COMBINATION_ID', 1, 'NUMBER', 38, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','ID_FLEX_NUM', 2, 'NUMBER', 38, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT1', 3, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT2', 4, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT3', 5, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT4', 6, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT5', 7, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT6', 8, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT7', 9, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT8', 10, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT9', 11, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT10', 12, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT11', 13, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT12', 14, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT13', 15, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT14', 16, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT15', 17, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT16', 18, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT17', 19, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT18', 20, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT19', 21, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT20', 22, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT21', 23, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT22', 24, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT23', 25, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT24', 26, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT25', 27, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT26', 28, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT27', 29, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT28', 30, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT29', 31, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SEGMENT30', 32, 'VARCHAR2', 100, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','SUMMARY_FLAG', 33, 'VARCHAR2', 1, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','ENABLED_FLAG', 34, 'VARCHAR2', 1, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','START_DATE_ACTIVE', 35, 'DATE', 9, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','END_DATE_ACTIVE', 36, 'DATE', 9, 'Y', 'N');
-- WHO Columns
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','CREATED_BY', 37, 'NUMBER', 38, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','CREATION_DATE', 38, 'DATE', 9, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','LAST_UPDATED_BY', 39, 'NUMBER', 38, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','LAST_UPDATE_DATE', 40, 'DATE', 9, 'Y', 'N');
AD_DD.REGISTER_COLUMN ('FND', 'XXCUST_KFF_DEMO','LAST_UPDATE_LOGIN', 41, 'NUMBER', 38, 'Y', 'N');
END;
/

PAY_ELEMENT_LINK_API.delete_element_link

DECLARE
   l_effective_start_date   DATE;
   l_effective_end_date     DATE;
   l_entries_warning        BOOLEAN;

   CURSOR c1
   IS
      SELECT   *
        FROM   hr.pay_element_links_f
       WHERE   ELEMENT_LINK_ID IN (SELECT   ELEMENT_LINK_ID FROM TMP_TABLE);
BEGIN
   FOR r1 IN c1
   LOOP
      PAY_ELEMENT_LINK_API.delete_element_link (
         p_effective_date          => R1.EFFECTIVE_START_DATE,
         p_element_link_id         => r1.ELEMENT_LINK_ID,
         p_datetrack_delete_mode   => 'ZAP',
         p_object_version_number   => R1.object_version_number,
         p_effective_start_date    => l_effective_start_date,
         p_effective_end_date      => l_effective_end_date,
         p_entries_warning         => l_entries_warning
      );

      DBMS_OUTPUT.PUT_LINE ('DELETED');
   END LOOP;

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('ERROR' || '     ' || SQLERRM);
END;

hr_sit_api.create_sit

DECLARE
   err_code                         varchar2 (50);
   err_msg                          varchar2 (1000);
   lv_pea_object_version_number     NUMBER;
   lv_analysis_criteria_id          NUMBER;
   lv_person_analysis_criteria_id   NUMBER;

   CURSOR EMP_DETAIL
   IS
      SELECT   *
        FROM   ---TMP_SIT_TABLE
       WHERE       SIT_NAME = :SIT_NAME
               AND (status <> 'U' OR STATUS IS NULL);
BEGIN
   FOR REC IN EMP_DETAIL
   LOOP
      BEGIN
         hr_sit_api.create_sit (
            p_validate                    => FALSE,
            p_effective_date              => SYSDATE,
            p_person_id                   => rec.PERSON_ID,
            p_pea_object_version_number   => lv_pea_object_version_number,
            p_business_group_id           => ---101,
            p_id_flex_num                 => -----50284,
            p_analysis_criteria_id        => lv_analysis_criteria_id,
            p_person_analysis_id          => lv_person_analysis_criteria_id,
            p_date_from                   => TO_DATE (rec.SEGMENT1,
                                                      'DD-MON-RRRR'),
            p_date_to                     => TO_DATE (rec.SEGMENT2,
                                                      'DD-MON-RRRR'), --=> NULL,
            p_segment1                    => TO_CHAR (rec.SEGMENT1,
                                                      'YYYY/MM/DD HH24:MI:SS'),
            p_segment2                    => TO_CHAR (rec.SEGMENT2,
                                                      'YYYY/MM/DD HH24:MI:SS'),
            p_segment3                    => rec.segment3,
            p_segment4                    => rec.segment4,
            p_segment5                    => rec.segment5,
            p_segment6                    => TO_CHAR (rec.SEGMENT6,
                                                      'YYYY/MM/DD HH24:MI:SS'),
            p_segment7                    => rec.segment7,
            p_segment8                    => rec.segment8,
            p_segment9                    => rec.segment9,
            p_segment10                   => rec.segment10,
            p_segment11                   => rec.segment11,
            p_segment12                   => rec.segment12,
            p_segment13                   => rec.segment13,
            p_segment14                   => rec.segment14,
            p_segment15                   => rec.segment15,
            p_segment16                   => rec.segment16,
            p_segment17                   => rec.segment17,
            p_segment18                   => rec.segment18,
            p_segment19                   => rec.segment19,
            p_segment20                   => rec.segment20,
            p_segment21                   => rec.segment21,
            p_segment22                   => rec.segment22,
            p_segment23                   => rec.segment23,
            p_segment24                   => rec.segment24,
            p_segment25                   => rec.segment25,
            p_segment26                   => rec.segment26,
            p_segment27                   => TO_CHAR (rec.SEGMENT27,
                                                      'YYYY/MM/DD HH24:MI:SS'),
            p_segment28                   => rec.segment28,
            p_segment29                   => rec.segment29,
            p_segment30                   => rec.segment30
         );

         COMMIT;

         UPDATE   ----TMP_SIT_TABLE
            SET   ANALYSIS_CRITERIA_ID = lv_analysis_criteria_id,
                  PERSON_ANALYSIS_ID = lv_person_analysis_criteria_id,
                  status = 'U',
                  ERROR_TEXT = 'No Error'
          WHERE   employee_number = rec.employee_number
                  AND srl_no = rec.srl_no;

         COMMIT;
      EXCEPTION
         WHEN OTHERS
         THEN
            ROLLBACK;

            err_code := SQLCODE;
            err_msg := SUBSTR (SQLERRM, 1, 200);

            UPDATE   ----TMP_SIT_TABLE
               SET   status = 'E', ERROR_TEXT = err_code || ':' || err_msg
             WHERE   employee_number = rec.employee_number
                     AND srl_no = rec.srl_no;

            COMMIT;

            DBMS_OUTPUT.put_line (' Error Msg:  ' || SQLERRM);
      END;
   END LOOP;
END;

hr_sit_api.delete_sit

DECLARE
   CURSOR c1
   IS
      SELECT   PERSON_ANALYSIS_ID, OBJECT_VERSION_NUMBER
        FROM   PER_PERSON_ANALYSES
       WHERE   TRUNC (CREATION_DATE) = TRUNC (SYSDATE);
BEGIN
   FOR r1 IN c1
   LOOP
      hr_sit_api.delete_sit (
         p_person_analysis_id          => r1.PERSON_ANALYSIS_ID,
         p_pea_object_version_number   => r1.OBJECT_VERSION_NUMBER,
         p_validate                    => FALSE
      );
   END LOOP;

   COMMIT;
END;

Introduction to Oracle 10g Pl/sql - Fundamentals 1

Introduction to Oracle 10g Pl/sql - Fundamentals 1-
Author
Nancy Greenberg

https://drive.google.com/open?id=17j94S3CBU3ivmysYbmNMqu-3k7q-gLmz

Kill Session

select s.sid,s.serial#, s.status,p.spid
from v$session s,v$process p
where s.username='USER_NAME'
and p.addr(+)=s.paddr;

alter system kill session '<sid>,<serial#>';