Query to get Position Details in Oracle APPS

  SELECT name position_name,

         org_name,

         job_name,

         location_code,

         TO_CHAR(date_effective, 'DD-MON-RRRR') date_effective,

        TO_CHAR(date_end, 'DD-MON-RRRR') date_end 

    FROM per_positions_v 

   WHERE 1 = 1

         AND TRUNC (creation_date) BETWEEN TO_DATE ('01-JAN-2021',

                                                    'DD-MON-RRRR')

                                       AND TO_DATE ('31-DEC-2021',

                                                    'DD-MON-RRRR')

ORDER BY org_name ASC

Query to list Job Names in Oracle APPS

SELECT name job_name 

      ,TO_CHAR (date_from, 'DD-MON-RRRR') date_from

  FROM per_jobs_vl

 WHERE TRUNC (creation_date) BETWEEN TO_DATE ('01-JAN-2020', 'DD-MON-YYYY')

                                 AND TO_DATE ('31-DEC-2021', 'DD-MON-YYYY')

Query to get Approval Assignments in Approval Hierarchy - Oracle APPS

SELECT ppca.creation_date,

         ppca.control_group_id,

         hou.name operating_unit,

         ppv.position_id,

         ppv.name position_name,

         pcga.control_group_name,

         ppca.control_function_id,

         pcf.control_function_name,

         start_date,

         end_date

    FROM po_position_controls_all ppca

        ,hr_operating_units hou

        ,per_positions_v ppv

        ,po_control_groups_all pcga

        ,po_control_functions pcf

   WHERE hou.organization_id = ppca.org_id

     AND ppv.position_id = ppca.position_id

     AND pcga.control_group_id = ppca.control_group_id

     AND ppca.control_function_id = pcf.control_function_id

--     AND ppca.org_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

     AND TRUNC (ppca.creation_date) BETWEEN TO_DATE ('01-JAN-2021',

                                                     'DD-MON-YYYY')

                                        AND TO_DATE ('28-OCT-2021',

                                                     'DD-MON-YYYY')     

ORDER BY ppca.org_id ASC

        ,ppca.position_id ASC

        ,ppca.creation_date 

        DESC

Query to get Approval Control Limits in Approval Hierarchy-Oracle APPS

SELECT pcr.control_group_id,

         pcga.control_group_name

         rule_type_code,

         object_code,

         amount_limit,

         segment1_low,

         segment1_high,

         segment2_low,

         segment2_high,

         segment3_low,

         segment3_high,

         segment4_low,

         segment4_high,

         segment5_low,

         segment5_high,

         segment6_low,

         segment6_high,

         segment7_low,

         segment7_high,

         segment8_low,

         segment8_high,

         segment9_low,

         segment9_high,

         segment10_low,

         segment10_high

    FROM po_control_rules pcr

    ,po_control_groups_all pcga

   WHERE pcga.control_group_id = pcr.control_group_id

Query to get Approval Group details in Approval Hierarchy - Oracle APPS

SELECT name operating_unit

  ,control_group_id

  ,control_group_name

  ,description

  ,creation_date

FROM po_control_groups_all pcga

    ,hr_operating_units hou

WHERE hou.organization_id = pcga.org_id

  AND TRUNC (creation_date) BETWEEN TO_DATE ('01-JAN-2021',

     'DD-MON-YYYY')

      AND TO_DATE ('28-OCT-2021',

     'DD-MON-YYYY')

ORDER BY creation_date DESC

Query to get list of Active Employees along with their Supervisor’s details in Oracle APPS

SELECT employee.employee_number,

       employee.full_name,

       employee.email_address,

       paaf.supervisor_id,

       supervisor.full_name,

       supervisor.email_address

  FROM per_all_people_f employee

      ,per_all_assignments_f paaf

      ,per_people_x supervisor

 WHERE employee.current_employee_flag = 'Y'

   AND paaf.supervisor_id(+) = supervisor.person_id

   AND TRUNC (SYSDATE) BETWEEN employee.effective_start_date

                           AND employee.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN supervisor.effective_start_date

                           AND supervisor.effective_end_date

   AND employee.person_id = paaf.person_id

   AND employee.employee_number IS NOT NULL

   --AND employee.employee_number = '1234567890'

   AND employee.person_type_id =

          (SELECT person_type_id

             FROM per_person_types

            WHERE user_person_type = 'Employee'

              AND business_group_id = employee.business_group_id

            )

Query to get active employee details in Oracle APPS

SELECT ppf.employee_number

      ,ppf.full_name

  ,ppf.email_address

  FROM per_all_people_f ppf

 WHERE ppf.current_employee_flag = 'Y'

   AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date

                           AND ppf.effective_end_date

   AND ppf.employee_number IS NOT NULL

   AND ppf.person_type_id =

              (SELECT person_type_id

                 FROM per_person_types

                WHERE user_person_type = 'Employee'

                  AND business_group_id = ppf.business_group_id

  )

API to create GL Code Combination in Oracle APPS

DECLARE

   l_valid_combination   BOOLEAN;

   l_cr_combination      BOOLEAN;

   l_ccid                       gl_code_combinations_kfv.code_combination_id%TYPE;

   l_structure_num       fnd_id_flex_structures.id_flex_num%TYPE;

   l_conc_segs           gl_code_combinations_kfv.concatenated_segments%TYPE;

   l_error_msg1          VARCHAR2 (240);

   l_error_msg2          VARCHAR2 (240);

BEGIN

   l_conc_segs := '01-01-012-012345-012-012345-0123-0123-0000-0000';

BEGIN

      SELECT id_flex_num

        INTO l_structure_num

        FROM apps.fnd_id_flex_structures

       WHERE id_flex_code = 'GL#'

             AND id_flex_structure_code = 'XX_ACCOUNTING_FLEXFIELD';

EXCEPTION

WHEN OTHERS THEN

        l_structure_num := NULL;

END;

   --Check if Code Combination exits with the above Concatenated Segments

BEGIN

      SELECT code_combination_id

        INTO l_ccid

        FROM apps.gl_code_combinations_kfv

       WHERE concatenated_segments = l_conc_segs;

EXCEPTION

WHEN OTHERS THEN

         l_ccid := NULL;

END;

IF l_ccid IS NOT NULL

THEN

--The Code Combination is Available

DBMS_OUTPUT.PUT_LINE ('COMBINATION_ID= ' || l_ccid);

ELSE

--Code Combination does not exists. So creating new Combination.

l_valid_combination :=

         apps.fnd_flex_keyval.validate_segs (

            operation          => 'CHECK_COMBINATION',

            appl_short_name    => 'SQLGL',

            key_flex_code      => 'GL#',

            structure_number   => l_structure_num,

            concat_segments    => l_conc_segs

            );

l_error_msg1 := FND_FLEX_KEYVAL.ERROR_MESSAGE;

IF l_valid_combination

THEN

DBMS_OUTPUT.PUT_LINE (

'Validation Successful! Creating the Combination…');

--Create the New CCID

l_cr_combination :=

apps.fnd_flex_keyval.validate_segs (

               operation          => 'CREATE_COMBINATION',

               appl_short_name    => 'SQLGL',

               key_flex_code      => 'GL#',

               structure_number   => l_structure_num,

               concat_segments    => l_conc_segs);

l_error_msg2 := fnd_flex_keyval.error_message;

IF l_cr_combination

THEN

--Fetch the New CCID

SELECT code_combination_id

  INTO l_ccid

  FROM apps.gl_code_combinations_kfv

WHERE concatenated_segments = l_conc_segs;

DBMS_OUTPUT.PUT_LINE ('NEW COMBINATION_ID = ' || l_ccid);

ELSE

--Error in creating a new combination

DBMS_OUTPUT.PUT_LINE (

'Error in creating the combination: ' || l_error_msg2);

END IF;

ELSE

--The segments in the account string are not defined in gl value set

DBMS_OUTPUT.PUT_LINE (

'Error in validating the combination: ' || l_error_msg1);

END IF;

END IF;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE ('Main Error: '||SQLCODE || ' ' || SQLERRM);

END;

Query to get an Employees Leave History in Oracle APPS

 SELECT a.date_start,

         a.date_end,

         a.absence_days,

         (SELECT name

            FROM per_absence_attendance_types

           WHERE absence_attendance_type_id = a.absence_attendance_type_id

             AND business_group_id = a.business_group_id)

            leave_type

    FROM per_absence_attendances a

   WHERE person_id =

            (SELECT person_id

               FROM per_all_people_f

              WHERE employee_number = '1234567890'

                    AND employee_number IS NOT NULL)

ORDER BY date_start DESC

Query to get List of Employees Current and Previous Assignment Details in Oracle APPS

SELECT ppf.employee_number,

         paaf.assignment_number,

         ppf.full_name,

         paaf.organization_id current_org_id,

         paaf.effective_start_date current_org_start_date,

         (SELECT name

            FROM hr_all_organization_units

           WHERE organization_id = paaf.organization_id)

            current_org_name,

         paaf_previous.effective_start_date previous_org_start_date,

         paaf_previous.organization_id previous_org_id,

         (SELECT name

            FROM hr_all_organization_units

           WHERE organization_id = paaf_previous.organization_id)

            previous_org_name

    FROM per_all_assignments_f paaf,

         per_all_assignments_f paaf_previous,

         pay_people_groups ppg,

         pay_people_groups ppg_previous,

         per_all_people_f ppf

   WHERE paaf_previous.effective_end_date + 1 = paaf.effective_start_date

     AND paaf_previous.assignment_id = paaf.assignment_id

     AND paaf_previous.assignment_type = 'E'

     AND paaf.assignment_type = 'E'

     AND paaf.organization_id <> paaf_previous.organization_id

     AND paaf.PEOPLE_GROUP_ID = ppg.people_group_id

     AND paaf_previous.PEOPLE_GROUP_ID = ppg_previous.people_group_id

     AND paaf.effective_start_date BETWEEN ppf.effective_start_date

                                       AND ppf.effective_end_date

     AND paaf.person_id = ppf.person_id

AND ppf.employee_number = '1234567890'

ORDER BY paaf_previous.effective_start_date DESC

Employee Short Leave Details and Hours Calculation in Oracle APPS

SELECT paa.person_id,

       paa.date_start,

       paa.date_end,

       time_start,

       time_end,

       ROUND (

          ( ( (SUBSTR (time_end, 1, 2) - SUBSTR (time_start, 1, 2)) * 60

             + (SUBSTR (time_end, 4, 2) - SUBSTR (time_start, 4, 2)))

           / 60),

          2)

          Hours

  FROM per_absence_attendances paa

      ,per_absence_attendance_types paat

 WHERE 1=1

   AND paa.absence_attendance_type_id = paat.absence_attendance_type_id

   AND paat.name = 'Short Leave'

   AND paa.date_start BETWEEN :p_period_start_date AND :p_period_end_date

   AND paa.date_end BETWEEN :p_period_start_date AND :p_period_end_date

   AND paa.person_id = 1234567890

Query to Get Employee GOSI Details from Assignment in Oracle APPS

SELECT ppx.employee_number,

       ppx.full_name,

       ppf.payroll_name,

       hsck.concatenated_segments gosi_concatenated_segments,

       SUBSTR (hsck.concatenated_segments,

               0,

               INSTR (hsck.concatenated_segments, '.') - 1)

          employer

  FROM per_assignments_x paaf,

       hr_soft_coding_keyflex hsck,

       pay_payrolls_f ppf,

       per_people_x ppx

 WHERE     paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id

       AND paaf.payroll_id = ppf.payroll_id

       AND paaf.assignment_status_type_id = 1

       AND paaf.assignment_type = 'E'

       AND paaf.primary_flag = 'Y'

   AND ppx.person_id = paaf.person_id

       ;

Query to get Position Hierarchy details in Oracle APPS

SELECT DISTINCT hpx.business_group_id,

                hpx.entry_grade_id,

                hpx.job_id,

                hpx.location_id,

                hpx.organization_id,

                hpx.fte,

                hpx.max_persons,

                hpx.name position,

                ppx.full_name,

                hier.lev

  FROM hr_positions_x hpx,

       per_assignments_x pax,

       per_people_x ppx,

       (    SELECT pse.parent_position_id, LEVEL lev

              FROM per_pos_structure_elements pse

             WHERE 1 = 1

                   AND pse.pos_structure_version_id =

                          (SELECT ppsv.pos_structure_version_id

                             FROM per_position_structures pps,

                                  per_pos_structure_versions ppsv

                            WHERE pps.position_structure_id =

                                     ppsv.position_structure_id

                                  AND pps.name = :p_hierarchy_name

                                  AND TRUNC (SYSDATE) BETWEEN ppsv.date_from

                                                          AND NVL (

                                                                 ppsv.date_to,

                                                                 TO_DATE (

                                                                    '31-Dec-4712',

                                                                    'DD-MON-YYYY')))

        START WITH pse.subordinate_position_id =

                      (SELECT position_id

                         FROM hr_positions_x

                        WHERE name = :p_position_name)

        CONNECT BY PRIOR pse.parent_position_id = pse.subordinate_position_id

          ORDER BY LEVEL ASC) hier

 WHERE     hier.parent_position_id = hpx.position_id

       AND pax.position_id = hier.parent_position_id

       AND pax.person_id = ppx.person_id

Query to get values from the User Defined Tables (UDT) in Oracle APPS

SELECT pui.value, puc.user_column_name, pur.row_low_range_or_name

  FROM pay_user_tables put

      ,pay_user_rows_f pur

  ,pay_user_columns puc

  ,pay_user_column_instances_f pui

 WHERE pur.user_table_id = put.user_table_id

   AND pui.user_column_id = puc.user_column_id

   AND pui.user_row_id = pur.user_row_id

   AND SYSDATE BETWEEN pur.effective_start_date AND pur.effective_end_date

   AND SYSDATE BETWEEN pui.effective_start_date AND pui.effective_end_date

   AND puc.user_table_id = put.user_table_id   

   AND put.user_table_name = 'XX_UDT_NAME'

   AND puc.user_column_name = 'XX_COLUMN_NAME'

   

Query to get the Employee wise Elements and the effective date of the Element Entry in Oracle APPS

SELECT papf.employee_number,

       papf.full_name,

       petf.element_name,

       peef.effective_start_date

  FROM per_all_people_f papf,

       per_all_assignments_f paaf,

       pay_element_entries_f peef,

       pay_element_types_f petf,

       per_business_groups pb

 WHERE papf.person_id = paaf.person_id

   AND papf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

   AND papf.business_group_id = pb.business_group_id

   AND papf.current_employee_flag = 'Y'

   AND paaf.assignment_id = peef.assignment_id

   AND peef.element_type_id = petf.element_type_id

   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date

   AND papf.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN paaf.effective_start_date

   AND paaf.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN peef.effective_start_date

   AND peef.effective_end_date

   AND TRUNC(SYSDATE) BETWEEN petf.effective_start_date

   AND petf.effective_end_date

   AND petf.element_name IN ('XX_ELEMENT_NAME1', 'XX_ELEMENT_NAME2', 'XX_ELEMENT_NAME3')

Query to get the Employees having particular Elements in a given Pay Period in Oracle APPS

SELECT DISTINCT papf.employee_number

   ,papf.full_name

   ,petf.element_name

   ,peef.effective_start_date

   ,pivf.name input_field

   ,peevf.screen_entry_value input_value

   ,peef.creation_date

   ,peef.last_update_date

    FROM per_all_people_f papf,

         per_all_assignments_f paaf,

         pay_element_entries_f peef,

         pay_element_types_f petf,

         pay_element_entry_values_f peevf,

         pay_input_values_f pivf

   WHERE papf.person_id = paaf.person_id

     AND papf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

     AND papf.current_employee_flag = 'Y'

     AND paaf.assignment_id = peef.assignment_id

     AND peef.element_type_id = petf.element_type_id

     AND peef.element_entry_id = peevf.element_entry_id

     AND TO_DATE (:p_date) BETWEEN peevf.effective_start_date AND peevf.effective_end_date

     AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date

                                 AND papf.effective_end_date

AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date

                                 AND paaf.effective_end_date

AND TO_DATE (:p_date) BETWEEN peef.effective_start_date AND peef.effective_end_date

AND TRUNC (SYSDATE) BETWEEN petf.effective_start_date

                                 AND petf.effective_end_date

AND petf.element_type_id = pivf.element_type_id

AND peef.element_type_id = pivf.element_type_id

AND pivf.input_value_id = peevf.input_value_id

AND TRUNC (SYSDATE) BETWEEN pivf.effective_start_date

                                 AND pivf.effective_end_date

--AND UPPER(pivf.name) IN ('AMOUNT', 'LOAN NUMBER', 'PERCENT')

--AND petf.element_name IN ('XX_ELEMENT_NAME1', 'XX_ELEMENT_NAME2', 'XX_ELEMENT_NAME3')

AND peevf.screen_entry_value IS NOT NULL

ORDER BY papf.employee_number

Query to get the Request Group of a Concurrent Program in Oracle APPS

SELECT resp_tl.responsibility_name

      ,req_grp.request_group_name

  ,cp.user_concurrent_program_name

  FROM fnd_request_groups req_grp

  ,fnd_request_group_units req_grp_unit

  ,fnd_responsibility resp

  ,fnd_responsibility_tl resp_tl

  ,fnd_concurrent_programs_tl cp

 WHERE req_grp.request_group_id = req_grp_unit.request_group_id

   AND resp.responsibility_id = resp_tl.responsibility_id

   AND resp.request_group_id = req_grp_unit.request_group_id

   AND resp_tl.language = USERENV('LANG')

   AND cp.language = USERENV('LANG')

   AND req_grp_unit.request_unit_id = cp.concurrent_program_id

   AND cp.user_concurrent_program_name LIKE '%XX%' 

Sample code to send Email with Attachment in Oracle

CREATE OR REPLACE PROCEDURE xx_send_email(

p_email IN VARCHAR2 -- Single User Email ID , test@test.com for Group Email ID

   ,p_path IN VARCHAR2 -- path where logfile available. 

   ,p_filename IN VARCHAR2 DEFAULT NULL -- logfile name 

   ,p_progname IN VARCHAR2 DEFAULT NULL -- Program name 

   ,p_mesgbody IN VARCHAR2 -- mail body 

   ) 

IS 

TYPE my_curs_type IS REF CURSOR; 

cursor_data my_curs_type; 

v_smtp_server VARCHAR2(100) := 'testmail.server.com'; 

v_smtp_server_port NUMBER := 25; 

v_line VARCHAR2(1000); 

crlf VARCHAR2(2) := CHR (13) || CHR (10); 

v_mesg VARCHAR2(32767); 

v_conn UTL_SMTP.CONNECTION; 

v_file_handle UTL_FILE.file_type; 

invalid_path EXCEPTION; 

v_dbname VARCHAR2(20); 

dbdir VARCHAR2(50); 

v_subject VARCHAR2(200); 

v_email VARCHAR2(80); 

v_from_name VARCHAR2(50); 

v_to_name VARCHAR2(32767); 

v_message VARCHAR2(500); 

v_stat VARCHAR2(500); 

BEGIN 

v_dbname := database_name; 

dbdir := p_path; 

v_conn := UTL_SMTP.open_connection (v_smtp_server

                                   ,v_smtp_server_port

   ); 

UTL_SMTP.helo (v_conn, v_smtp_server); 

v_from_name := 'root@test_ip_address'; 

v_subject := 'From: "SOLAD005" <XX_Server_' || v_dbname;

UTL_SMTP.mail (v_conn, v_from_name); 

--Assign Email ids to the Receiptent 

IF p_email IS NOT NULL 

   AND LOWER(p_email) != 'test@test.com' THEN 

UTL_SMTP.rcpt (v_conn, p_email); 

v_to_name := p_email; 

ELSE v_stat := 'SELECT email FROM emails where program_name=''' ||p_progname|| ''''; 

dbms_output.put_line(v_stat); 

OPEN cursor_data FOR v_stat ;-- using p_progname; 

LOOP

FETCH cursor_data INTO v_email;

EXIT WHEN cursor_data%NOTFOUND; 

UTL_SMTP.rcpt (v_conn, v_email); 

v_to_name := v_to_name || ';' || v_email; 

END LOOP; 

CLOSE cursor_data; 

END IF; 

IF p_filename IS NULL THEN 

v_message := 'Hi' || crlf || crlf || p_progname || p_mesgbody; 

ELSE 

v_message := 'Hi' || crlf || crlf || p_progname || p_mesgbody ||' and invalid records are available in attachment';

END IF; UTL_SMTP.open_data (v_conn); 

v_mesg := 'Date: ' || TO_CHAR (SYSDATE, 'DD Mon RR hh24:mi:ss') || crlf 

                   || 'From: ' || v_from_name || crlf 

   || 'Subject: ' || v_subject || crlf 

   || 'To: ' || v_to_name || crlf 

   || 'Mime-Version: 1.0 ' || crlf 

   || 'Content-Type: multipart/mixed; boundary="DMW.Boundary.123456789" ' || crlf 

   || '' || crlf || '--DMW.Boundary.123456789' || crlf 

   || 'Content-Type: text/plain; name="message.txt"; charset=US-ASCII' 

   || crlf || 'Content-Disposition: inline; filename="c:\message.txt"' || crlf 

   || 'Content-Transfer-Encoding: 7bit' || crlf || '' 

   || crlf || v_message || crlf

   ;

UTL_SMTP.write_data (v_conn, v_mesg);

IF p_filename IS NOT NULL THEN 

BEGIN 

v_file_handle := UTL_FILE.fopen (dbdir, p_filename, 'r'); 

-- generate the MIME boundary line ... 

v_mesg := crlf || '--DMW.Boundary.123456789' || crlf || 'Content-Type: application/octet-stream; name="' 

               || p_filename || '"' || crlf || 'Content-Disposition: attachment; filename="' 

   || p_filename || '"' || crlf || 'Content-Transfer-Encoding: 7bit' || crlf || crlf

   ; 

UTL_SMTP.write_data (v_conn, v_mesg); 

LOOP 

UTL_FILE.get_line (v_file_handle, v_line); 

v_mesg := v_line crlf; 

UTL_SMTP.write_data (v_conn, v_mesg); 

END LOOP; 

EXCEPTION 

WHEN UTL_FILE.invalid_path THEN 

DBMS_OUTPUT.put_line ( 'Error in opening attachment ' ||p_filename ); 

WHEN UTL_SMTP.transient_error OR UTL_SMTP.permanent_error THEN 

UTL_SMTP.quit (v_conn); 

raise_application_error ( -20000, 'Failed to send mail due to the following error: ' ); 

WHEN OTHERS THEN 

NULL; 

END; 

END IF; 

v_mesg := crlf '--DMW.Boundary.123456789--' 

UTL_SMTP.close_data (v_conn); 

UTL_SMTP.quit (v_conn); 

END xx_send_email;

Difference between SQL Primary Key and Candidate Key in Oracle

• Primary Key:
• A Primary Key is a column or a combination of columns that uniquely identify a record. Only one Candidate Key can be Primary Key.

• Candidate Key: 
• A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. 
• There can be multiple Candidate Keys in one table. 
• Each Candidate Key can qualify as Primary Key.

One needs to be very careful in selecting the Primary Key as an incorrect selection can adversely impact the database architect and future normalization. For a Candidate Key to qualify as a Primary Key, it should be Non-NULL and Unique in any domain.

Query to get Supplier wise Invoice Details in Oracle APPS

   SELECT hou.name operating_unit,

         asp.vendor_name,

         asp.segment1 vendor_number,

         apsa.vendor_site_code,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id

                 AND aia.invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            toatl_booking_amount,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id

                 AND aia.invoice_type_lookup_code = 'PREPAYMENT'

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'AVAILABLE')

            prepayment_amount,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.org_id = apsa.org_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code = 'PREPAYMENT'

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'FULL')

            applied_prepayment_amount,

         (SELECT NVL (SUM (aipa.amount), 0)

            FROM ap_invoices_all aia, ap_invoice_payments_all aipa

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aipa.invoice_id = aia.invoice_id

                 AND aia.org_id = apsa.org_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code NOT IN

                        ('CREDIT', 'DEBIT', 'PREPAYMENT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            payment_amount,

         (SELECT NVL (SUM (aps.gross_amount), 0)

            FROM ap_invoices_all aia, ap_payment_schedules_all aps

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aps.invoice_id = aia.invoice_id

                 AND aia.org_id = apsa.org_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.invoice_type_lookup_code NOT IN ('CREDIT', 'DEBIT')

                 AND aps.hold_flag = 'Y')

            hold_amount,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id 

--AND aia.invoice_type_lookup_code NOT IN ('CREDIT','DEBIT','PREPAYMENT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) IN

                        ('NEVER APPROVED', 'NEEDS REAPPROVAL', 'CANCELLED'))

            booking_value,

         (SELECT COUNT (*)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.ORG_ID = apsa.ORG_ID

                 -- AND aia.invoice_type_lookup_code NOT IN ('CREDIT','DEBIT','PREPAYMENT')

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) IN

                        ('NEVER APPROVED', 'NEEDS REAPPROVAL', 'CANCELLED'))

            booking_count,

         (SELECT NVL (SUM (aia.invoice_amount), 0)

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code IN ('CREDIT', 'DEBIT')

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.ORG_ID = apsa.ORG_ID

                 AND aia.invoice_id NOT IN

                        (SELECT invoice_id FROM ap_invoice_payments_all)

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            deduction_amount,

         (SELECT NVL (SUM (aipa.amount), 0)

            FROM ap_invoices_all aia, ap_invoice_payments_all aipa

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aipa.invoice_id = aia.invoice_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.invoice_type_lookup_code IN ('CREDIT', 'DEBIT')

                 AND aia.invoice_num NOT LIKE '%TDS%'

                 AND aia.invoice_num NOT LIKE '%WCT%'

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.ORG_ID = apsa.ORG_ID

                 AND ap_invoices_pkg.get_approval_status (

                        aia.invoice_id,

                        aia.invoice_amount,

                        aia.payment_status_flag,

                        aia.invoice_type_lookup_code) = 'APPROVED')

            cm_payment_amount,

         (SELECT DISTINCT aia.invoice_currency_code

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND aia.org_id = apsa.org_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code)

            currency_code,

         (SELECT aia.exchange_rate

            FROM ap_invoices_all aia

           WHERE     aia.vendor_id = asp.vendor_id

                 AND aia.ORG_ID = apsa.ORG_ID

                 AND apsa.vendor_site_id = aia.vendor_site_id

                 AND apsa.invoice_currency_code = aia.invoice_currency_code

                 AND aia.creation_date =

                        (SELECT MAX (aia1.creation_date)

                           FROM ap_invoices_all aia1

                          WHERE     aia1.vendor_id = asp.vendor_id

                                AND aia1.org_id = apsa.org_id

                                AND apsa.vendor_site_id = aia1.vendor_site_id

                                AND aia1.INVOICE_CURRENCY_CODE <> 'INR'))

            conversion_rate

    FROM ap_suppliers asp

,ap_supplier_sites_all apsa

,hr_operating_units hou

   WHERE asp.vendor_id = apsa.vendor_id

     AND hou.organization_id = apsa.org_id

     AND asp.vendor_name = :p_vendor_name

ORDER BY 2

Import Standard Purchase Order Program or Import Price Catalog Failed in Oracle APPS

Below solution is from Oracle Support Doc ID 1632117.1

Caution: Before doing any changes please validate it from Oracle Support.

1)  Verify the executable method in the below PDOI concurrent programs:

     Import Standard Purchase Orders
     Import Price Catalogs
     Import Contract Purchase Agreements

     Steps:
     1. Navigate to System Administrator -> Concurrent -> Program -> Define
     2. Query for the program for example 'Import Standard Purchase Orders'
     3. Check the Executable method defined for the program. If it is 'SQL*Plus' then select the LOV beside Executable 'POXPDOI' and reselect the executable as'POXPDOI'.
     4. Confirm that the executable method has been changed as 'PL/SQL Procedure'
     5. Do the same for the other programs.

2)  Check whether the attribute 'MULTI_ORG_CATEGORY' is set to 'S' in all the below concurrent programs:

     Import Standard Purchase Orders
     Import Price Catalogs
     Import Contract Purchase Agreements

     Steps:
     1. Navigate to System Administration > Concurrent > Programs
     2. Query for the Program for example 'Import Standard Purchase Orders'
     3. Click on Update pencil icon
     4. Click on Request tab
     5. For field Operating Unit Mode - The value should be 'Single'
     6. Do the same for the other programs.

3)  Test and run the program import standard purchase orders. Program will get completed successfully and should not get any error

      Ex:  'Routine fdpsql cannot read the file
             /u16/oracle/XX/apps/apps_st/appl/po/12.0.0/sql/PO_PDOI_GRP.start_process
 

XML/BI Publisher Interview Questions in Oracle APPS

• What is BI Publisher?
• BI Publisher is a reporting tool for generating the reports. It is an engine that can be             integrated with systems supporting the business.

• What is the difference between XML Publisher & BI Publisher?
• Initially it was released on the name of XML Publisher(the initial patch set), later on they have added more features and called it as Business Intelligence Publisher. 
• In BI Publisher, by default we have integration with Data definitions in R12 instance. Both these names can be used interchangeably.

• Is BI Publisher integrated with Oracle Apps?
• Answer is Yes, it is tightly integrated with Oracle Apps for reporting needs. In 11.5.10 instances XML Publisher was used, in R12 we call it BI Publisher.

• What are the various Components required for developing a BI Publisher report?
• Required Components are:
• Data Template
• Layout Template
• Integration with Concurrent Manager.

• What is a Data Template?
• Data Template is an xml structure which contains the queries to be run against the database so that desired output in xml format is generated
• This generated xml output is then applied on to the Layout Template for the final output.

• What is a Layout Template?
•  Layout template defines how the user views the output, basically it can be developed using Microsoft word document in rft (rich text format) or Adobe pdf format. 
• The data output in xml format (from Data template) will be loaded in Layout Template at run time and the required final output file is generated.

• What are the output formats supported by Layout Template?
• .xls, .html, .pdf, eText etc are supported based on the business requirement.

• How does the concurrent program submitted by the user knows about the Data Template or Layout Template it should be using for generating the output?
• The concurrent program short_name will be mapped to the Data Definition Code of the Data Template. Layout Template is attached to the Data Template, this forms the mapping between all the three.

• Do you need to write multiple layout templates for each output type like pdf/xls/html?
• Answer is No, only Layout Template will be created, BI Publisher generates desired output format when the request is run.

• Can BI publisher reports be used in OAF pages?
• XDO template utility helper java classes are provided by Oracle.

• How do you pass parameters to the XML Publisher report?
• Concurrent program parameters should be passed, ensure that the parameter name/token are same as in the Concurrent Program definition and the Data Template


• What are the various sections available in the data template?
• Parameter Section
• Trigger Section
• SQL Statement Section
• Data Structure Section
• Lexical Section

• What does lexical section contain?
• The required lexical clause of Key Flex field or Descriptive FF are created under this section

• What triggers are supported in Data template?
• Before Report and After Report

• Where is the trigger code written?
• The trigger  code is written in the PL/SQL Package which is given under defaultpackage tag of data template.

• What is the default output format of the report?
• The default output format defined during the layout template creation will be used to generate the output, the same can be modified during the request submission and it will overwrite the one defined at layout template.

• Can you have multiple layout templates for a singe data template?
• Answer is Yes, multiple layouts can be defined, user has a choice here to use one among them at run time during concurrent request submission.

• Where do you register data and layout templates?
• Data Definitions Navigation:: XML Publisher Administrator Responsibility>>Data Definitions
  tab.
• Data Template Navigation:: XML Publisher Administrator Responsibility>>Templates tab.

• When you want to create a report output in 5 languages, do we need have to create 5 layout templates?
• Answer is No, XML/BI Publisher provides the required translation for your templates, based on the number of languages installed in your Oracle Apps Environment.

• What is the required installation for using BI Publisher Report?
• BI Publisher Desktop tool has be installed. Using this tool you can preview or test the report before deploying the same on to the instance.

• How do you move your layout or data template across instances?
• XDOLOADER is the utility will be used. 

• What is the tool to map required data output and layout templates so that they can be tested in local machine?
• Template viewer will be used.

• Which component is responsible for generating the output in xml format before applying it to Layout Template?
• Data Engine will take Data Template as the input and the output will be generated in xml format which will then be applied on layout template.

Consolidation Set in Oracle Payroll

• A Consolidation set is a collection of payrolls. You can group payrolls using these sets. 
• It helps in processing multiple payrolls at once. 
• If you wish to run two different payrolls on a particular date, you can just run the payroll process on the consolidation set having those two payrolls.

Different Formulas used in Oracle Payroll

There are many different types of formulas in Oracle Payroll. Few of them are:

• Accrual Formulae
• Accrual
• Accrual Carryover
• Accrual Ineligibility
• Accrual Sub Formula
• Oracle Payroll
• Element Skip
• Element Input Validation
• Element Type and Input Value determination
• Payroll Run Proration

What are Pre Payments in Oracle Payroll

• Pre-Payments is a process with which, the payroll system allots the payroll money in to the different personal payment methods of each employee. 
• Pre Payment is run as part of the post payroll process. 
• Run the prepayments with an override payment method. 

What are Recurring/Non Recurring Elements in Oracle Payroll

Elements can be divided into two types, based on the processing type.

1. Recurring: A Recurring Element, once attached to a person, gets added once for each pay period automatically. 
2. Non Recurring: A Non recurring element, once attached to a person continues for the related pay period only, and expires i.e. end dated after that period.

How to Remove Posting privileges for an User in Fusion

 Please follow Document ID from Oracle 2560426.1

Query to get E-Biz Tax Details in Oracle Apps

 SELECT xe.name entity_name

      ,ledger.name ledger_name

      ,hou.name operating_unit

      ,zxr.tax_regime_code tax_regime_code

      ,zxr.tax tax_code

      ,zxr.inclusive_tax_flag

      ,zxr.tax_status_code tax_status_code

      ,zxr.tax_rate_code tax_rate_code

      ,zxr.tax_jurisdiction_code

      ,zxr.rate_type_code

      ,zxr.percentage_rate

      ,zxr.effective_from rate_effective_from

      ,zxr.effective_to rate_effective_to

      ,acct.tax_account_ccid

      ,gcc.concatenated_segments tax_account

  FROM zx_rates_vl zxr

      ,zx_accounts acct

      ,hr_operating_units hou

      ,gl_ledgers ledger

      ,gl_code_combinations_kfv gcc

      ,xle_entity_profiles xe

 WHERE     1 = 1

       AND acct.tax_account_entity_code = 'RATES'

       AND zxr.active_flag = 'Y'

       AND TRUNC (SYSDATE) BETWEEN TRUNC (zxr.effective_from)

                               AND NVL (TRUNC (zxr.effective_to),

                                        TRUNC (SYSDATE) + 1)

       AND ledger.ledger_id = hou.set_of_books_id

       AND gcc.code_combination_id = acct.tax_account_ccid

       AND hou.organization_id = acct.internal_organization_id

       AND acct.tax_account_entity_id = zxr.tax_rate_id

       AND hou.default_legal_context_id = xe.legal_entity_id

--AND zxr.tax_regime_code = 'XX'

--AND zxr.tax_rate_code = 'XX'

--AND xe.legal_entity_identifier = 'XX12345'

Sample Query to get GL Balances in Oracle Apps

   SELECT bal.code_combination_id,

         segment1 company,

         segment2 region,

         segment3 department,

         segment4 account,

         segment5 country,

         segment6 project,

         segment7 future1,

         segment8 future2,

         DECODE (account_type,

                 'A', 'Asset',

                 'C', 'Budgetary (CR)',

                 'D', 'Budgetary (DR)',

                 'E', 'Expense',

                 'L', 'Liability',

                 'O', 'Owners equity',

                 'R', 'Revenue',

                 account_type)

            ACCOUNT_TYPE,

         period_name ,

         LAST_DAY (TO_DATE (period_name, 'MON-RR')) PERIOD_DATE,

        SUM (

            DECODE (actual_flag,

                    'B', NVL (period_net_dr, 0) - NVL (period_net_cr, 0),

                    0))

            PERIOD_NET_BUDGET_BALANCE,

         SUM (

            DECODE (actual_flag,

                    'A', NVL (period_net_dr, 0) - NVL (period_net_cr, 0),

                    0))

            PERIOD_NET_ACTUAL_BALANCE,

         SUM (

            DECODE (

               actual_flag,

               'E', DECODE (

                       encumbrance_type,

                       'Invoice', NVL (period_net_dr, 0)

                                  - NVL (period_net_cr, 0),

                       0),

               0))

            PERIOD_NET_INV_ENCUMBRANCE,

         SUM (

            DECODE (

               actual_flag,

               'E', DECODE (

                       encumbrance_type,

                       'Obligation', NVL (period_net_dr, 0)

                                     - NVL (period_net_cr, 0),

                       0),

               0))

            PERIOD_NET_OBL_ENCUMBRANCE,

         SUM (

            DECODE (

               actual_flag,

               'A',   NVL (begin_balance_dr, 0)

                    + NVL (period_net_dr, 0)

                    - NVL (begin_balance_cr, 0)

                    - NVL (period_net_cr, 0),

               0))

            CURRENT_GL_BALANCE,

         SUM (

            DECODE (

               actual_flag,

               'E', DECODE (

                       encumbrance_type,

                       'Invoice',   NVL (begin_balance_dr, 0)

                                  + NVL (period_net_dr, 0)

                                  - NVL (begin_balance_cr, 0)

                                  - NVL (period_net_cr, 0),

                       0),

               0))

            INVOICE_ENCUMBRANCE,

         SUM (

            DECODE (

               actual_flag,

               'E', DECODE (

                       encumbrance_type,

                       'Obligation',   NVL (begin_balance_dr, 0)

                                     + NVL (period_net_dr, 0)

                                     - NVL (begin_balance_cr, 0)

                                     - NVL (period_net_cr, 0),

                       0),

               0))

            OBLIGATION_ENCUMBRANCE,

         SUM (

            DECODE (

               actual_flag,

               'B',   NVL (begin_balance_dr, 0)

                    + NVL (period_net_dr, 0)

                    - NVL (begin_balance_cr, 0)

                    - NVL (period_net_cr, 0),

               0))

            ADOPTED_BUDGET,

         PERIOD_YEAR

    FROM gl_balances bal

            ,gl_encumbrance_types enc

            ,gl_code_combinations gcc

   WHERE bal.encumbrance_type_id = enc.encumbrance_type_id(+)

         AND bal.code_combination_id = gcc.code_combination_id

GROUP BY bal.code_combination_id,

         segment1,

         segment2,

         segment3,

         segment4,

         segment5,

         segment6,

         segment7,

         segment8,

         account_type,

         period_name,

         period_year;

Purchase Order Type(PO_HEADERS_ALL.TYPE_LOOKUP_CODE)

 select lookup_code, meaning

  from fnd_lookup_values 

  where lookup_type = 'POO:DOCUMENT_TYPE'

Issue with Description/Comment/Remark columns in Oracle(when column has value in multiple lines)

When extracting data into excel file from Oracle if columns like item_description, comments or remarks, etc have multiple lines as shown in below screenshot then data will not extract properly partial data move to next line.

To overcome this issue use CHR(10) with REPLACE command as shown in below screenshot.

 Now result showing in one line only.

API To Create Payment Documents For Internal Bank Accounts in Oracle Apps(CE)

As per the Oracle Support(Doc ID 2206912.1) there is no API available to create Payment Documents for Internal Bank Accounts in Oracle.

Payment Documents Table is CE_PAYMENT_DOCUMENTS.

Customer Interface failed with B5 Interface_Status in oracle apps

B5 means PRIMARY_SITE_USE_FLAG is mandatory when inserting an address.

Query to get Operating Unit wise Internal Bank, Bank Branch and Bank Account Information in Oracle Apps

SELECT cba.bank_account_name ,

  cba.bank_account_id,

  cba.bank_account_name_alt,

  cba.bank_account_num ,

  cba.multi_currency_allowed_flag ,

  cba.zero_amount_allowed ,

  cba.account_classification ,

  bb.bank_name ,

  cba.bank_id,

  bb.bank_number,

  bb.bank_branch_type ,

  bb.bank_branch_name ,

  cba.bank_branch_id ,

  bb.bank_branch_number ,

  bb.eft_swift_code ,

  bb.description BANK_DESCRIPTION,

  cba.currency_code ,

  bb.address_line1,

  bb.city,

  bb.county,

  bb.state,

  bb.zip_code,

  bb.country,

  ou.name ,

  gcf.concatenated_segments,

  cba.ap_use_allowed_flag,

  cba.ar_use_allowed_flag,

  cba.xtr_use_allowed_flag,

  cba.pay_use_allowed_flag

FROM apps.ce_bank_accounts cba,

  apps.ce_bank_acct_uses_all bau,

  apps.cefv_bank_branches bb,

  apps.hr_operating_units ou,

  apps.gl_code_combinations_kfv gcf

WHERE cba.bank_account_id         = bau.bank_account_id

AND cba.bank_branch_id            = bb.bank_branch_id

AND ou.organization_id            = bau.org_id

AND cba.asset_code_combination_id = gcf.code_combination_id

AND (cba.end_date IS NULL OR cba.end_date > TRUNC(SYSDATE))

Running the Customer Interface throwing "racina: ORA-00001: unique constraint (AR.HZ_LOCATIONS_PK) violated and racina: Error inserting addresses." Errors

When trying to Import customers using Customer Interface (RACUST) and receive the following error:

ORA-00001 running Customer Interface

racina: ORA-00001: unique constraint (AR.HZ_LOCATIONS_PK) violated

racina: Error inserting addresses.

The Customer Interface process is attempting to insert an existing location_id in the HZ_LOCATIONS table.

Running the Customer Interface throwing "racina: ORA-00001: unique constraint(AR.HZ_PARTY_SITES_U2) violated" error:

 When you run "Customer Interface" program by populating ra_customers_interface_all and ra_customers_interface_all interface tables throwing "racina: ORA-00001: unique constraint(AR.HZ_PARTY_SITES_U2) violated". This error is due to the next site number generated using HZ_PARTY_SITE_NUMBER_S is less than the max (party_site_number) that is already generated in system.

Please check party_site_number and exists take action accordingly.