Oracle Cloud Fusion: Supplier Bank Accounts Query

SELECT (SELECT NAME
        FROM   hr_operating_units
        WHERE  organization_id = pssm.prc_bu_id) BU_name,
   ps.segment1 supplier_number,
       hzp.party_name supplier_name,
       pssm.vendor_site_code supplier_site,
       bank.party_name bank_name,
       branch.party_name branch_name,
       ieb.bank_account_name,
       ieb.bank_account_num,
       ieb.currency_code,
       ieb.IBAN
FROM   poz_suppliers ps,
       poz_supplier_sites_all_m pssm,
       iby_external_payees_all payee,
       iby_pmt_instr_uses_all uses,
       iby_ext_bank_accounts ieb,
       hz_parties bank,
       hz_parties branch,
       hz_parties HZP
WHERE  ps.vendor_id = pssm.vendor_id
       AND ps.party_id = payee.payee_party_id
       AND payee.supplier_site_id = pssm.vendor_site_id
       AND uses.instrument_type = 'BANKACCOUNT'
       AND payee.ext_payee_id = uses.ext_pmt_party_id
       AND uses.payment_function = 'PAYABLES_DISB'
       AND uses.instrument_id = ieb.ext_bank_account_id
       AND ieb.bank_id = bank.party_id(+)
       AND ieb.branch_id = branch.party_id(+)
       AND hzp.party_id = ps.party_id
       AND SYSDATE BETWEEN Nvl(uses.start_date, SYSDATE) AND
                           Nvl(uses.end_date, SYSDATE)
       AND SYSDATE BETWEEN Nvl(ieb.start_date, SYSDATE) AND
                           Nvl(ieb.end_date, SYSDATE)
       AND Nvl(ps.end_date_active, SYSDATE + 1) > Trunc (SYSDATE)
       AND Nvl(pssm.inactive_date, SYSDATE + 1) > Trunc (SYSDATE) 

Oracle Cloud Fusion: Query to get the Supplier/Site Attachment Details

--------------------------------------------------------------------

-- Supplier Attachment

--------------------------------------------------------------------

SELECT psv.vendor_name supplier_name,

       fad.category_name,

       fdt.dm_type,

       fdt.file_name,

       fdt.title,

       fdt.description

FROM   poz_suppliers_v psv,

       fnd_attached_documents fad,

       fnd_documents_tl fdt

WHERE  1 = 1

       AND To_char (psv.vendor_id) = fad.pk1_value

       AND fad.document_id = fdt.document_id

       AND fad.entity_name = 'POZ_SUPPLIERS'

       AND fdt.language = Userenv ('LANG') 

   

--------------------------------------------------------------------

-- Supplier Site Attachment

--------------------------------------------------------------------

SELECT psv.vendor_name supplier_name,

       (SELECT NAME

        FROM   hr_operating_units

        WHERE  organization_id = pssv.prc_bu_id) Procurement_BU,

       pssv.vendor_site_code supplier_site,

       fad.category_name,

       fdt.dm_type,

       fdt.file_name,

       fdt.title,

       fdt.description

FROM   poz_suppliers_v psv,

       poz_supplier_sites_v pssv,

       fnd_attached_documents fad,

       fnd_documents_tl fdt

WHERE  1 = 1

       AND psv.vendor_id = pssv.vendor_id

       AND To_char (pssv.vendor_site_id) = fad.pk1_value

       AND fad.document_id = fdt.document_id

       AND fad.entity_name = 'POZ_SUPPLIER_SITES_ALL_M'

       AND fdt.language = Userenv ('LANG')    

Fusion: How To Exclude the BI Report’s Schedule Frequency From Holidays

BI Report scheduling frequency can be skipped based on the localized public holidays or weekends using by report triggers.

Pre-Requisites
Public holidays need to be captured in the Fusion Cloud applications’ Calendar Events.

Step 1: Calendar Events

Navigation: Setup and Maintenance > Manage Calendar Events



Step 2: SQL for Calendar Events

SELECT NAME,
       TO_CHAR(START_DATE_TIME, 'MM/DD/YYYY') START_DATE,
       TO_CHAR(END_DATE_TIME, 'MM/DD/YYYY') END_DATE,
       SHORT_CODE
  FROM PER_CALENDAR_EVENTS_VL
 WHERE CATEGORY = 'PH'
 ORDER BY START_DATE



Step 3: Add Event Triggers in Data Model

Schedule Triggers

A schedule trigger fires when a report job is scheduled to run. Schedule triggers are of type SQL Query. When a report job is scheduled to run, the schedule trigger executes the SQL statement defined for the trigger. If data is returned, then the report job is submitted. If data is not returned from the trigger SQL query, the report job is skipped.

SQL Query

SELECT 'TRUE' FROM DUAL

WHERE NOT EXISTS

(

-- Public Holidays

SELECT 'HOLIDAY' HOLIDAY

  FROM PER_CALENDAR_EVENTS_VL

 WHERE 1=1

   AND CATEGORY = 'PH'

   AND TO_CHAR(:P_RUN_DATE,  'MM/DD/YYYY') BETWEEN TO_CHAR(START_DATE_TIME, 'MM/DD/YYYY') AND TO_CHAR(END_DATE_TIME, 'MM/DD/YYYY')

 UNION 

-- Weekends  

SELECT 'HOLIDAY' 

  FROM DUAL 

 WHERE TO_CHAR(:P_RUN_DATE, 'D') IN (1,7)

)

The above query will exclude the weekends and public holidays.

Documents Reference

1. Fusion Global HR: How To Setup Calendar Events By Locations (Doc ID 1917689.1)

2. Adding Triggers: Guide

Fusion: How To Create Password Protected BI Publisher Report

Goal
How to create password protected BI publisher report outputs (pdf).

Solution
There are two types of password protected outputs (pdf) can be Created in the Fusion Cloud applications.
1. Static Password
2. Dynamic Password

1. Static Password
The below options required to update in the cloud application BI catalog services.
Navigation: Tools → Reports and Analytics → Browse Catalog

Navigate to appropriate report template folders and Click “Edit” the Template (which needed the password protection functionality) → Click “Properties” button.

Properties	Value
Enable PDF Security	True
Open Document Password

 Save the changes and Run the job.

The Report output will prompt the user to enter the password when open the output.

2. Dynamic Password

1. Create the Data Model (include/identify the field/value for password)

(ex, Supplier Number, Employee Number with DOB) 

(ex)

/DATA_DS/G_1/PASSWORD (Any customized/default password group)

/DATA_DS/G_1/SEGMENT1 (Supplier Number – Default Password, for supplier related reports)

2. Define RTF Template with the Custom Properties.

Navigation: RTF Template → File → Info → Properties → Advanced Properties → Custom

Add the below mentioned two custom properties.

Properties	Value	Type
xdo-pdf-security	true	Text
xdo-pdf-open-password	/DATA_DS/G_1/SEGMENT1	Text

Click “OK” and Save the Template.

Upload the template to BI catalog, Run the report and check the report output.

The Report output will prompt the user to enter the password when open the output.

Documents Reference

Oracle Support Documents

1. How To Set Password Protection For BI Publisher Report (Doc ID 2117187.1)

2. How To Password Protect PDF Documents At Runtime? (Doc ID 1355332.1)

Fusion Middleware Report Designer's Guide for Oracle Business Intelligence Publisher

Setting Report Processing and Output Document Properties → PDF Security Properties

https://docs.oracle.com/middleware/12212/bip/BIPRD/GUID-300980F2-CC27-4DA5-9420-2D607CF132BF.htm#BIPRD2995

Limitations

The offered solution applicable for PDF outputs only and Oracle is working on EXCEL outputs.

Enh 26197461 - ER: PASSWORD PROTECTION FOR EXCEL FILES SENT THROUGH ORACLE BI PUBLISHER

Employee / Contingent Worker Termination API (Sample Script)

EMPLOYEE Termination

DECLARE

   --

   --Common Variables

   l_terminate_emp_flag             VARCHAR2 (1)                                             := 'N';

   l_terminate_msg                  VARCHAR2 (600);

   l_person_id                      NUMBER                                                   := 533285;

   l_le_terminate_emp_exception     EXCEPTION;

   --- DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.actual_termination_emp

   --- IN variables

   l_effective_date                 DATE;

   l_termination_reason             per_periods_of_service.leaving_reason%TYPE               := 'XXSD_RESG5';

   -- HR_LOOKUPS (TYPE: LEAV_REAS)

   l_person_type_id                 per_person_types.person_type_id%TYPE                     := 1130;                                                                   -- 'EX_EMP'

   l_period_of_service_id           per_periods_of_service.period_of_service_id%TYPE;

   l_actual_termination_date        per_periods_of_service.actual_termination_date%TYPE      := TRUNC (SYSDATE);

   l_last_standard_process_date     per_periods_of_service.last_standard_process_date%TYPE   := TRUNC (SYSDATE + 10);

   l_object_version_number          per_periods_of_service.object_version_number%TYPE;

   l_start_date                     per_periods_of_service.date_start%TYPE;

   l_notif_term_date                DATE;

   --- OUT variables

   l_supervisor_warning             BOOLEAN                                                  := FALSE;

   l_event_warning                  BOOLEAN                                                  := FALSE;

   l_interview_warning              BOOLEAN                                                  := FALSE;

   l_review_warning                 BOOLEAN                                                  := FALSE;

   l_recruiter_warning              BOOLEAN                                                  := FALSE;

   l_asg_future_changes_warning     BOOLEAN                                                  := FALSE;

   l_entries_changed_warning        VARCHAR2 (300);

   l_pay_proposal_warning           BOOLEAN                                                  := FALSE;

   l_dod_warning                    BOOLEAN                                                  := FALSE;

   l_alu_change_warning             VARCHAR2 (300);

   --- DECLARE variables for HR_EX_EMPLOYEE_WORKER_API.final_process_emp

   --- IN variables

   l_final_process_date             per_periods_of_service.final_process_date%TYPE;

   --- OUT variables

   l_org_now_no_manager_warning     BOOLEAN                                                  := FALSE;

   l_f_asg_future_changes_warning   BOOLEAN                                                  := FALSE;

   l_f_entries_changed_warning      VARCHAR2 (300);

--

BEGIN

   --

   BEGIN

      SELECT pos.period_of_service_id,

             pos.object_version_number,

             date_start

        INTO l_period_of_service_id,

             l_object_version_number,

             l_start_date

        FROM per_periods_of_service pos

       WHERE pos.person_id = l_person_id;

   EXCEPTION

      WHEN OTHERS

      THEN

         l_terminate_msg    := 'Error while selecting employee details : ' || SUBSTR (SQLERRM, 1, 150);

         RAISE l_le_terminate_emp_exception;

   END;

   --

   SAVEPOINT terminate_employee_s1;

   --

   BEGIN

      /*

      * This API terminates an employee.

      * This API converts a person of type Employee >to a person of type

      * Ex-Employee. The person's period of service and any employee assignments are ended.

      */

      hr_ex_employee_api.actual_termination_emp (p_validate                        => FALSE,

                                                 p_effective_date                  => TRUNC (SYSDATE),

                                                 p_period_of_service_id            => l_period_of_service_id,

                                                 p_object_version_number           => l_object_version_number,

                                                 p_actual_termination_date         => l_actual_termination_date,

                                                 p_last_standard_process_date      => l_last_standard_process_date,

                                                 p_person_type_id                  => l_person_type_id,

                                                 p_leaving_reason                  => l_termination_reason,

                                                 p_supervisor_warning              => l_supervisor_warning,

                                                 p_event_warning                   => l_event_warning,

                                                 p_interview_warning               => l_interview_warning,

                                                 p_review_warning                  => l_review_warning,

                                                 p_recruiter_warning               => l_recruiter_warning,

                                                 p_asg_future_changes_warning      => l_asg_future_changes_warning,

                                                 p_entries_changed_warning         => l_entries_changed_warning,

                                                 p_pay_proposal_warning            => l_pay_proposal_warning,

                                                 p_dod_warning                     => l_dod_warning,

                                                 p_alu_change_warning              => l_alu_change_warning

                                                );

      IF l_object_version_number IS NULL

      THEN

         l_terminate_emp_flag    := 'N';

         l_terminate_msg         := 'Warning validating API: hr_ex_employee_api.actual_termination_emp';

         RAISE l_le_terminate_emp_exception;

      END IF;

      l_terminate_emp_flag    := 'Y';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_terminate_msg    := 'Error validating API: hr_ex_employee_api.actual_termination_emp : ' || SUBSTR (SQLERRM, 1, 150);

         RAISE l_le_terminate_emp_exception;

   END;

   IF l_terminate_emp_flag = 'Y'

   THEN

      BEGIN

         IF l_start_date > TRUNC (SYSDATE)

         THEN

            l_notif_term_date    := l_start_date + 1;

         ELSE

            l_notif_term_date    := TRUNC (SYSDATE);

         END IF;

         /*

         * This API updates employee termination information.

         * The ex-employee must exist in the relevant business group

         */

         apps.hr_ex_employee_api.update_term_details_emp (p_validate                        => FALSE,

                                                          p_effective_date                  => TRUNC (SYSDATE),

                                                          p_period_of_service_id            => l_period_of_service_id,

                                                          p_notified_termination_date       => l_notif_term_date,

                                                          p_projected_termination_date      => l_notif_term_date,

                                                          p_object_version_number           => l_object_version_number

                                                         );

      EXCEPTION

         WHEN OTHERS

         THEN

            l_terminate_msg         := 'Error validating API: hr_ex_employee_api.update_term_details_emp : ' || SUBSTR (SQLERRM, 1, 1500);

            l_terminate_emp_flag    := 'N';

            RAISE l_le_terminate_emp_exception;

      END;

      BEGIN

         /*

         * This API set the final process date for a terminated employee.

         * This API covers the second step in terminating a period of service and all

         * current assignments for an employee. It updates the period of service

         * details and date-effectively deletes all employee assignments as of the final process date.

         */

         apps.hr_ex_employee_api.final_process_emp (p_validate                        => FALSE,

                                                    p_period_of_service_id            => l_period_of_service_id,

                                                    p_object_version_number           => l_object_version_number,

                                                    p_final_process_date              => l_final_process_date,

                                                    p_org_now_no_manager_warning      => l_org_now_no_manager_warning,

                                                    p_asg_future_changes_warning      => l_f_asg_future_changes_warning,

                                                    p_entries_changed_warning         => l_f_entries_changed_warning

                                                   );

      EXCEPTION

         WHEN OTHERS

         THEN

            l_terminate_msg    := 'Error validating API: hr_ex_employee_api.final_process_emp : ' || SUBSTR (SQLERRM, 1, 150);

            RAISE l_le_terminate_emp_exception;

      END;

   END IF;

   --

   COMMIT;

--

EXCEPTION

   WHEN l_le_terminate_emp_exception

   THEN

      DBMS_OUTPUT.put_line (l_terminate_msg);

      ROLLBACK TO terminate_employee_s1;

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Terminate Employee. Error OTHERS while validating: ' || SQLERRM);

      ROLLBACK TO terminate_employee_s1;

END;

CONTINGENT WORKER Termination

DECLARE

   --

   --Common Variables

   l_terminate_cwk_flag           VARCHAR2 (1)                                               := 'N';

   l_terminate_msg                VARCHAR2 (600);

   l_person_id                    NUMBER                                                     := 533485;

   l_le_terminate_cwk_exception   EXCEPTION;

   --- DECLARE variables for hr_contingent_worker_api.actual_termination_placement

   --- IN variables

   l_effective_date               DATE                                                       := TRUNC (SYSDATE);

   l_termination_reason           per_periods_of_placement.termination_reason%TYPE           := 'EP';

   -- select * from hr_lookups where lookup_type = 'HR_CWK_TERMINATION_REASONS'

   l_person_type_id               per_person_types.person_type_id%TYPE                       := 1129;                                                                     -- EX_CWK

   l_period_of_placement_id       per_periods_of_placement.period_of_placement_id%TYPE;

   l_actual_termination_date      per_periods_of_placement.actual_termination_date%TYPE      := TRUNC (SYSDATE);

   l_last_standard_process_date   per_periods_of_placement.last_standard_process_date%TYPE   := TRUNC (SYSDATE + 10);

   l_object_version_number        per_periods_of_placement.object_version_number%TYPE;

   l_start_date                   per_periods_of_placement.date_start%TYPE;

   l_notif_term_date              DATE;

   --- OUT variables

   l_supervisor_warning           BOOLEAN                                                    := FALSE;

   l_event_warning                BOOLEAN                                                    := FALSE;

   l_interview_warning            BOOLEAN                                                    := FALSE;

   l_review_warning               BOOLEAN                                                    := FALSE;

   l_recruiter_warning            BOOLEAN                                                    := FALSE;

   l_asg_future_changes_warning   BOOLEAN                                                    := FALSE;

   l_entries_changed_warning      VARCHAR2 (300);

   l_pay_proposal_warning         BOOLEAN                                                    := FALSE;

   l_dod_warning                  BOOLEAN                                                    := FALSE;

   --- DECLARE variables for hr_contingent_worker_api.final_process_placement

   --- IN variables

   l_final_process_date           per_periods_of_service.final_process_date%TYPE;

   --- OUT variables

   l_org_now_no_manager_warning   BOOLEAN                                                    := FALSE;

--

BEGIN

   --

   BEGIN

      SELECT pos.period_of_placement_id,

             pos.object_version_number,

             date_start

        INTO l_period_of_placement_id,

             l_object_version_number,

             l_start_date

        FROM per_periods_of_placement pos

       WHERE pos.person_id = l_person_id;

   EXCEPTION

      WHEN OTHERS

      THEN

         l_terminate_msg    := 'Error while selecting cwk details : ' || SUBSTR (SQLERRM, 1, 150);

         RAISE l_le_terminate_cwk_exception;

   END;

   --

   SAVEPOINT terminate_cwk_s1;

   --

   BEGIN

      /*

      This API covers the first step in terminating a period of placement and

      all current assignments for a cwk, identified by person_id and date_start.

      You can use the API to set the actual termination date, the last standard

      process date, the new assignment status and the new person type

      */

      hr_contingent_worker_api.actual_termination_placement (p_validate                        => FALSE,

                                                             p_effective_date                  => l_effective_date,

                                                             p_person_id                       => l_person_id,

                                                             p_date_start                      => l_start_date,

                                                             p_person_type_id                  => l_person_type_id,

                                                             p_actual_termination_date         => l_actual_termination_date,

                                                             p_termination_reason              => l_termination_reason

                                                                                                                      --In/Out

      ,

                                                             p_object_version_number           => l_object_version_number,

                                                             p_last_standard_process_date      => l_last_standard_process_date

                                                                                                                              --Out

      ,

                                                             p_supervisor_warning              => l_supervisor_warning,

                                                             p_event_warning                   => l_event_warning,

                                                             p_interview_warning               => l_interview_warning,

                                                             p_review_warning                  => l_review_warning,

                                                             p_recruiter_warning               => l_recruiter_warning,

                                                             p_asg_future_changes_warning      => l_asg_future_changes_warning,

                                                             p_entries_changed_warning         => l_entries_changed_warning,

                                                             p_pay_proposal_warning            => l_pay_proposal_warning,

                                                             p_dod_warning                     => l_dod_warning

                                                            );

      IF l_object_version_number IS NULL

      THEN

         l_terminate_cwk_flag    := 'N';

         l_terminate_msg         := 'Warning validating API: hr_contingent_worker_api.actual_termination_placement';

         RAISE l_le_terminate_cwk_exception;

      END IF;

      l_terminate_cwk_flag    := 'Y';

   EXCEPTION

      WHEN OTHERS

      THEN

         l_terminate_msg    := 'Error validating API: hr_contingent_worker_api.actual_termination_placement : ' || SUBSTR (SQLERRM, 1, 150);

         RAISE l_le_terminate_cwk_exception;

   END;                                                                                                                      --hr_contingent_worker_api.actual_termination_placement

   IF l_terminate_cwk_flag = 'Y'

   THEN

      BEGIN

         /*

         This API covers the second step in terminating a period of placement and

         all current assignments for an cwk.  It updates the period of placement

         details and date-effectively deletes all the contingent worker assignments

         as of the final process date.

         */

         hr_contingent_worker_api.final_process_placement (p_validate                        => FALSE,

                                                           p_person_id                       => l_person_id,

                                                           p_date_start                      => l_start_date

                                                                                                            --In/Out

         ,

                                                           p_object_version_number           => l_object_version_number,

                                                           p_final_process_date              => l_final_process_date

                                                                                                                    --Out

         ,

                                                           p_org_now_no_manager_warning      => l_org_now_no_manager_warning,

                                                           p_asg_future_changes_warning      => l_asg_future_changes_warning,

                                                           p_entries_changed_warning         => l_entries_changed_warning

                                                          );

      EXCEPTION

         WHEN OTHERS

         THEN

            l_terminate_msg    := 'Error validating API: hr_contingent_worker_api.final_process_placement : ' || SUBSTR (SQLERRM, 1, 150);

            RAISE l_le_terminate_cwk_exception;

      END;                                                                                                                        --hr_contingent_worker_api.final_process_placement

   END IF;

   --

   COMMIT;

--

EXCEPTION

   WHEN l_le_terminate_cwk_exception

   THEN

      DBMS_OUTPUT.put_line (l_terminate_msg);

      ROLLBACK TO terminate_cwk_s1;

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('Terminate CWK. Error OTHERS while validating: ' || SQLERRM);

      ROLLBACK TO terminate_cwk_s1;

END;