2 Replies Latest reply: Dec 6, 2012 5:25 PM by Babs RSS

    OTL Timecard not showing up in frontend when created from backend using HXC

    Babs
      Hi All,

      I have created a SR but was hoping to get an answer quicker if possible, or atleast if anyone else facing similar issues. Its quite easy to reproduce in any environments (BTW I'm using R12), details below:

      I followed the Oracle OTL (white paper) on HXC TimeStore Deposit API. After many road blocks I'm able to create complete timecard (full week) using the API and also submit them, they all get auto approved too in our DEV and TEST environments. I checked the following tables for the OTL timecard week I created for a specific user and all data is valid (i.e the status is APPROVED in summary table).

      HXC_TIMECARD_SUMMARY
      HXC_TIME_BUILDING_BLOCKS
      HXC_TIME_ATTRIBUTE_USAGES
      HXC_TIME_ATTRIBUTES

      The API calls are initiated from our SOA (Oracle Fusion Middleware) server. The HXC TimeStore API's are embedded inside the PL/SQL (Wrapper) which is called from the composite's BPEL using DBAdapter.

      The issue I have now is I'm not able to verify that the timecard is created for a week for a specific user from the front end. The week shows up as blank in frontend for that user and if I try to create the timecard for the same week in the frontend it fails with Oracle's generic error. Make some sense as the timecard already exists for that week only it doesn't show up.

      Alternatively I'm able create timecard for any weeks other than what is already created using API from frontend and able to add more time/details into different days from backend (using hxc_timestore_deposit.create_time_entry) and then it shows up values against days which were filled from the API (backend).

      I dont know if this is any security issue as I'm calling FND_GLOBAL.APPS_INITIALIZE in my API initialize. We also have BAE custom PL/SQL running in our current Production (11i version) which creates timecards with leaves (public holidays) filled in and that seem to work fine.

      Here are sine details of the version I use:

      Oracle Applications
      Copyright (c) 2004 Oracle Corporation,
      Redwood Shores, California.
      All Rights Reserved.

      ----------------------------------------
      Login
      ----------------------------------------
      Site : ABC Corp Development Environment (DEV) 12.1.3
      Application : Time and Labor Engine
      Responsibility : OTL Super Timekeeper
      Security Group : Standard

      ----------------------------------------
      Database Server
      ----------------------------------------
      RDBMS : 11.2.0.3.0
      Oracle Applications : 12.1.3
      System Date : 06-12-2012 15:18:02

      ----------------------------------------
      Forms Server
      ----------------------------------------

      ----------------------------------------
      Current Form
      ----------------------------------------
      Form Application : Time and Labor Engine
      Form Name : HXCTKSTA
      Form Path : /oraapps/derpappl/hxc/12.0.0/forms/US/HXCTKSTA.fmx
      Form Version : 12.0.28.12010000.34
      Form Last Modified : $Date: 2011/08/01 00:29 $

      ----------------------------------------
      Scheme Display Profiles
      ----------------------------------------
      Java Look and Feel : ORACLE
      Java Color Scheme : KHAKI
      Color Scheme Indicator : GENERIC
      Indicator Colors : Y

      ----------------------------------------
      Forms
      ----------------------------------------
      APPSTAND : 12.0.6.12010000.6
      FNDSCSGN : 12.0.15.12010000.10
      HRSTAND : 12.0.14.12010000.5
      HXCTKSTA : 12.0.28.12010000.34

      ----------------------------------------
      Form Menus
      ----------------------------------------
      FNDMENU : 12.0.2.12010000.6

      ----------------------------------------
      Forms PL/SQL
      ----------------------------------------
      APPCORE : 12.0.29.12010000.22
      CUSTOM : 12.0.0
      FNDSQF : 12.0.3.12010000.8
      GHR : 12.0.46.12010000.55
      GLOBE : 12.0.76.12010000.10
      GMS : 12.0.51.12010000.20
      HRKPI : 12.0.0.12010000.1
      HR_GEN : 12.0.9.12010000.4
      IGILUTIL2 : 12.0.32
      IGILUTIL : 12.0.3
      OPM : 12.0.7.12010000.2
      PQH_GEN : 12.0.7
      PSA : 12.0.17
      PSAC : 12.0.5
      PSB : 12.0.2
      VERT1 : 12.0.0
      VERT2 : 12.0.0
      VERT3 : 12.0.0
      VERT4 : 12.0.0
      VERT5 : 12.0.0
      VERT : 12.0.0
        • 1. Re: OTL Timecard not showing up in frontend when created from backend using HXC
          Giuseppe Bonavita
          Dear Babs,


          tricky issue. let's establish some solid groud.

          are you successfully calling the hxc_timestore_deposit.execute_deposit_process after the hxc APIs?
          is the otuput variable "p_messages" populated after the above call?


          Thanks
          Regards
          Giuseppe
          • 2. Re: OTL Timecard not showing up in frontend when created from backend using HXC
            Babs
            Thanks Giuseppe, for your response and the answer is yes I'm able to call hxc_timestore_deposit.execute_deposit_process and its all good.

            BTW this issue is resolved, details below;

            Srujan from Oracle replied to my SR (very promptly) saying the below:

            Can you please use the following API and Packages and let us know the results
            hxc_timestore_deposit.create_time_entry*
            hxc_timestore_deposit.create_attribute*
            hxc_timestore_deposit.create_attribute*
            Please don't use the %_BB packages when you have other packages available.

            I followed his instruction and everything seem to work fine, apparently we dont need to call any BB api's to create the timecard, day or details just call timeentry + attributes and the api takes care of creating days, timecard if it does not exist.

            I initially followed the White Paper (OTL - HXC TimeStore Deposit) which clearly states that we should call all the api's in specific sequence as in create timecard use the building_block_id to create days and then enter time either using detail or timeentry which very misleading. My bad I should have drilled down the api to see how it all works but project time constraint and the size of that api (which is a huge maze) I didn't do it.

            Anyway hope this post shows up in google every time somebody searches for HXC TimeStore Deposit, so they follow these simple steps and avoid calling all %_BB api's.

            Here is sample code I used for creating a time entry where there was no timecard for that week, the below call created building block for the whole week (including days in HXC_TIME_BUILDING_BLOCKS table).

            DECLARE
            lr_tbl_timecard_info     hxc_self_service_time_deposit.timecard_info;
            lr_tbl_attributes_info   hxc_self_service_time_deposit.app_attributes_info;
            lr_tbl_messages          hxc_self_service_time_deposit.message_table;

            ln_error_count           NUMBER;
            lc_error_data            VARCHAR2 (4000);

            ln_person_id             NUMBER;
            ln_building_block_id     NUMBER;
            ln_transaction_date      DATE;

            lc_units                 NUMBER;
            lc_unit_value            NUMBER;
            lc_allowance_type        VARCHAR2 (100);
            ln_parent_bb_id          NUMBER;
            ln_new_timecard_id       NUMBER;
            ln_new_timecard_ovn      NUMBER;
            BEGIN
            lr_tbl_timecard_info.DELETE;
            lr_tbl_attributes_info.DELETE;
            ln_person_id := 621;
            lc_units := 200;
            lc_unit_value := 40;
            lc_allowance_type := 'Dinner Allowance';
            ln_building_block_id := NULL;
            ln_parent_bb_id := NULL;
            ln_new_timecard_id := NULL;
            ln_new_timecard_ovn := NULL;

            FND_GLOBAL.APPS_INITIALIZE (user_id        => 1134,
            resp_id        => 23818,
            resp_appl_id   => 809);

            SELECT fnd_date.canonical_to_date ('2012-11-26')
            INTO ln_transaction_date
            FROM DUAL;

            DBMS_OUTPUT.put_line ('TimeCard entry: ' || ln_transaction_date);

            hxc_timestore_deposit.create_time_entry (
            p_measure                  => '1',
            p_day                      => ln_transaction_date,
            p_resource_id              => ln_person_id,
            p_app_blocks               => lr_tbl_timecard_info,
            p_app_attributes           => lr_tbl_attributes_info,
            p_time_building_block_id   => ln_building_block_id);

            --hxc_timestore_deposit.create_detail_bb+
            --                 (p_type                          => 'MEASURE',
            --                  p_measure                       => 8,
            --                  p_parent_building_block_id      => ln_parent_bb_id,
            --                  p_comment_text                  => ' ',
            --                  p_app_blocks                    => lr_tbl_timecard_info,
            --                  p_app_attributes                => lr_tbl_attributes_info,
            --                  p_time_building_block_id        => ln_building_block_id
            --                 );
            --+
            DBMS_OUTPUT.put_line (
            +'Detail Building Block ID: ' || ln_building_block_id);+

            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'Project_Id',
            p_attribute_value     => '4004',

            p_app_attributes      => lr_tbl_attributes_info);
            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'Task_Id',
            p_attribute_value     => '2007',
            p_app_attributes      => lr_tbl_attributes_info);
            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'BILLABLE_FLAG',
            p_attribute_value     => 'N',
            p_app_attributes      => lr_tbl_attributes_info);
            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'SYSTEM_LINKAGE_FUNCTION',
            p_attribute_value     => 'ST',
            p_app_attributes      => lr_tbl_attributes_info);
            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'Expenditure_Type',
            p_attribute_value     => '604201 Contract Labour',

            p_app_attributes      => lr_tbl_attributes_info);

            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'PO Line Id',
            p_attribute_value     => '18005',
            p_app_attributes      => lr_tbl_attributes_info);
            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'PO Price Type',
            p_attribute_value     => 'STANDARD',
            p_app_attributes      => lr_tbl_attributes_info);
            hxc_timestore_deposit.create_attribute (
            p_building_block_id   => ln_building_block_id,
            p_attribute_name      => 'PO Header Id',
            p_attribute_value     => '22003',
            p_app_attributes      => lr_tbl_attributes_info);

            --hxc_timestore_deposit.create_attribute+
            --         (p_building_block_id      => ln_building_block_id,
            --          p_attribute_name         => 'Dummy Element Context',
            --          p_attribute_value        => 'ELEMENT - 62642',
            --          p_app_attributes         => lr_tbl_attributes_info
            --         );
            --hxc_timestore_deposit.create_attribute+
            --         (p_building_block_id      => ln_building_block_id,
            --          p_attribute_name         => 'ID Payroll Elements',
            --          p_attribute_value        => 'Normal Earnings',
            --          p_app_attributes         => lr_tbl_attributes_info
            --         );
            -- ID Payroll Elements
            --hxc_timestore_deposit.create_attribute+
            --         (p_building_block_id      => ln_building_block_id,
            --          p_attribute_name         => 'ID Payroll Elements',
            --          p_attribute_value        => 'Mileage Re-imbursement',
            --          p_app_attributes         => lr_tbl_attributes_info
            --         );
            --hxc_timestore_deposit.create_attribute+
            --         (p_building_block_id      => ln_building_block_id,
            --          p_attribute_name         => 'Dummy Element Context',
            --          p_attribute_value        => 'ELEMENT - 62216',
            --          p_app_attributes         => lr_tbl_attributes_info
            --         );
            --hxc_timestore_deposit.create_attribute+
            --         (p_building_block_id      => ln_building_block_id,
            --          p_attribute_name         => 'InputValue2',
            --          p_attribute_value        => lc_unit_value,
            --          p_app_attributes         => lr_tbl_attributes_info
            --         );
            --hxc_timestore_deposit.create_attribute+
            --         (p_building_block_id      => ln_building_block_id,
            --          p_attribute_name         => 'InputValue3',
            --          p_attribute_value        => lc_units,
            --          p_app_attributes         => lr_tbl_attributes_info
            --         );

            hxc_timestore_deposit.execute_deposit_process (
            p_validate                       => FALSE,
            p_app_blocks                     => lr_tbl_timecard_info,
            p_app_attributes                 => lr_tbl_attributes_info,
            p_messages                       => lr_tbl_messages,
            p_mode                           => 'SAVE',
            p_deposit_process                => 'OTL Deposit Process',
            p_retrieval_process              => 'Projects Retrieval Process',
            p_timecard_id                    => ln_new_timecard_id,
            p_timecard_ovn                   => ln_new_timecard_ovn,
            p_process_terminated_employees   => TRUE);

            IF (lr_tbl_messages.COUNT > 0)
            THEN
            ln_error_count := lr_tbl_messages.FIRST;

            WHILE (ln_error_count IS NOT NULL)
            LOOP
            lc_error_data :=
            +' Timecard Save Error: '+
            +|| fnd_message.get_string (+
            lr_tbl_messages (ln_error_count).application_short_name,
            lr_tbl_messages (ln_error_count).message_name);
            DBMS_OUTPUT.put_line (lc_error_data);
            ln_error_count := lr_tbl_messages.NEXT (ln_error_count);
            END LOOP;
            END IF;
            END;