Chitika Add

Friday, November 21

Performance Rating/Review Load



Performance Rating Load
From Application : Performance rating can be entered from application as in below
·         Select Appropriate Business Group Responsibility:  ex: HR Operations US
·         Navigation : HR Operations US > People > Enter and Maintain > Query for Employee > Assignments > Others > Performance
·         Enter, 'Effective Date' and 'Performance Rating', 'Next Date'
·         Save
Tables effected : PER_PERFORMANCE_REVIEWS
Loading through Program or backend: Performance rating can also be entered from backend or by developing a script and registering as Program for bulk Load
·         Api : HR_PERF_REVIEW_API.CREATE_PERF_REVIEW
·         Required data: 
Created_by,
person_id ( can be derived if Employee number provided)
full_name (just  to validate or to confirm the exact employee provided in csv or for any typo)
performance_rating  
Effective_date
next_review_date
Business_Group 

Single Insert : using the below script  done a single insert and the data get inserted into application and could able to see it ( refer to the below screenshot)
The below script is just a sample single insert script followed by a a package /procedure with required validations with commit and Roll back options





DECLARE
  -- Start of Variable declarations, Initialize Variables with appropriate values to test the script
   -- Input Variables
  V_VALIDATE BOOLEAN;
  -- Output Variables
  V_PERFORMANCE_REVIEW_ID NUMBER;
  -- Input Variables
  V_PERSON_ID NUMBER := 21227;
  V_EVENT_ID  NUMBER;
  V_REVIEW_DATE DATE                  := '01-NOV-2014' ;
  V_PERFORMANCE_RATING VARCHAR2(2000) := 'XX_4';
  V_NEXT_PERF_REVIEW_DATE DATE        := '01-NOV-2015';
  -- Output Variables
  V_OBJECT_VERSION_NUMBER    NUMBER;
  V_NEXT_REVIEW_DATE_WARNING BOOLEAN;
begin
  fnd_global.apps_initialize(23248,52441,800 );
  --  Calling API HR_PERF_REVIEW_API.CREATE_PERF_REVIEW
  HR_PERF_REVIEW_API.CREATE_PERF_REVIEW (
            P_VALIDATE => V_VALIDATE ,
            P_PERFORMANCE_REVIEW_ID => V_PERFORMANCE_REVIEW_ID ,
            P_PERSON_ID => V_PERSON_ID ,
            P_EVENT_ID => V_EVENT_ID ,
            P_REVIEW_DATE => V_REVIEW_DATE ,
            P_PERFORMANCE_RATING => V_PERFORMANCE_RATING ,
            P_NEXT_PERF_REVIEW_DATE => V_NEXT_PERF_REVIEW_DATE
            --,P_ATTRIBUTE_CATEGORY           => V_ATTRIBUTE_CATEGORY
            ,P_OBJECT_VERSION_NUMBER => V_OBJECT_VERSION_NUMBER ,
            P_NEXT_REVIEW_DATE_WARNING => V_NEXT_REVIEW_DATE_WARNING
            );
  dbms_output.put_line('V_PERFORMANCE_REVIEW_ID : ' || V_PERFORMANCE_REVIEW_ID);
  dbms_output.put_line('V_OBJECT_VERSION_NUMBER : ' || V_OBJECT_VERSION_NUMBER);
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('error : ' || sqlerrm);
END;
 


CREATE or replace package Apps.XX_HR_PERFORMANCE_PKG AS
 /*
  *************************************************************************
  ** XX Performance Review Load by Apps Associates
  *************************************************************************
  FILE_NAME         : XX_HR_PERFORMANCE_PKG.pks
  File Type         : PACKAGE SPECIFICATION
  PURPOSE           : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.
  Created    by        : Jithendra
  Creation Date        : 17-Sep-2014
  Updated by        :
  Updation Date        :
  VERSION           : 1.0
  Comments            :
 ***************************************************************************/

PROCEDURE XX_PERF_REVIEW_LOAD_PROC(
                           X_ERRBUF              OUT VARCHAR2,
                           X_RETCODE             OUT NUMBER,
                           V_PATH_LOCATION in varchar2,
                           v_file_name varchar2,
                           V_REVIEW_DATE in varchar2,
                           V_NEXT_PERF_REVIEW_DATE in varchar2
                            );
end XX_HR_PERFORMANCE_PKG;

/

CREATE or replace package body Apps.XX_HR_PERFORMANCE_PKG AS
 /*
  *************************************************************************
  ** XX Performance Review Load by Apps Associates
  *************************************************************************
  FILE_NAME         : XX_HR_PERFORMANCE_PKG.pkb
  File Type         : PACKAGE body
  PURPOSE           : The Package for the Wrapper Functions/Procedures used to Create Performance Ratings.
  Created    by        : Jithendra
  Creation Date        : 17-Sep-2014
  Updated by        :
  Updation Date        :
  VERSION           : 1.0
  Comments            :
 ***************************************************************************/

PROCEDURE XX_PERF_REVIEW_LOAD_PROC (
                           X_ERRBUF              OUT VARCHAR2,
                           X_RETCODE             OUT NUMBER,
                           V_PATH_LOCATION in varchar2,
                           v_file_name varchar2,                         
                           V_REVIEW_DATE in varchar2,
                           V_NEXT_PERF_REVIEW_DATE in varchar2
                            )
as
    V_FILE_TYPE UTL_FILE.FILE_TYPE;
    V_LINE varchar2 (1000);
    V_EMPLOYEE_NUMBER number;
    V_BUSINESS_GROUP   varchar2(200);
    V_LAST_NAME     varchar2(200);
    V_FIRST_NAME   varchar2(200);
    V_PERF_RATING_NAME varchar2(30);
    V_PERF_RATING_CODE VARCHAR2(30);
    V_REC_COUNT number := 0;
    V_VALIDATE                      BOOLEAN;
    V_PERSON_ID                     number ;
    V_PERFORMANCE_REVIEW_ID         NUMBER;
    V_OBJECT_VERSION_NUMBER         NUMBER;
    V_NEXT_REVIEW_DATE_WARNING      BOOLEAN;
    L_NEXT_REVIEW_DATE date;
    V_TOT_count Number :=0;
    v_SUC_count Number :=0;
    V_FAIL_Count Number :=0;
BEGIN

  fnd_file.put_line(fnd_file.output,'File Name:' ||V_PATH_LOCATION||'/'||v_file_name);
  fnd_file.put_line(fnd_file.output,'Review Date:' ||V_REVIEW_DATE);
  fnd_file.put_line(fnd_file.output,'Next Review Date:' ||V_NEXT_PERF_REVIEW_DATE);
 
 
  V_FILE_TYPE := UTL_FILE.FOPEN (v_path_location, v_file_name, 'R');
 
  if V_NEXT_PERF_REVIEW_DATE = null
  then
  L_NEXT_REVIEW_DATE := null;
  else
  L_NEXT_REVIEW_DATE := TO_DATE(V_NEXT_PERF_REVIEW_DATE,'dd-mon-yyyy');
  end if;
 
  IF UTL_FILE.IS_OPEN(V_FILE_TYPE) THEN
    LOOP
--    fnd_file.put_line(fnd_file.output,'start/repeat :- '||V_REC_COUNT);
        BEGIN
          
            begin
                UTL_FILE.GET_LINE(V_FILE_TYPE, V_LINE);
                V_TOT_count := V_TOT_count +1;
                Exception when no_data_found THEN
                    fnd_file.put_line(fnd_file.log,'No More Reccords ' ||V_REC_COUNT);
                    UTL_FILE.FCLOSE(V_FILE_TYPE);
                    EXIT;
            END;          
            if V_REC_COUNT = 0  then
                fnd_file.put_line(fnd_file.log,'skipiping the first line to read values '||V_LINE);
                V_REC_COUNT := V_REC_COUNT+1;
              
            else
              
                V_REC_COUNT := V_REC_COUNT+1;
              
            --    fnd_file.put_line(fnd_file.output,'Rec :- '||V_LINE);

                v_employee_number := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 1);
                --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_employee_number : '|| V_EMPLOYEE_NUMBER);

                v_business_group := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 2);
                --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_business_group : '||V_BUSINESS_GROUP);

                v_last_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 3);
                --fnd_file.put_line(fnd_file.output,'v_last_name : '||v_last_name);

                v_first_name := REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4);
                --fnd_file.put_line(fnd_file.output,'v_first_name : '||v_first_name);
              
                --V_PERF_RATING_NAME := nvl(trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5)),trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 4)));
                V_PERF_RATING_NAME := trim(BOTH CHR(13) from REGEXP_SUBSTR(V_LINE, '[^,]+', 1, 5));
              
  
                --fnd_file.put_line(fnd_file.output,'V_PERF_RATING_NAME : '||V_PERF_RATING_NAME);
              
                select papf.PERSON_ID , DECODE(V_PERF_RATING_NAME ,'1 - Lowest' , 'XX_1',
                                              '2' , 'XX_2',
                                               '3',  'XX_3',
                                              '4' , 'XX_4'
                                               ,'5 - Highest' ,'XX_5')
                into V_PERSON_ID, V_PERF_RATING_CODE
                from PER_ALL_PEOPLE_F papf,  per_business_groups pbg
                where EMPLOYEE_NUMBER = V_EMPLOYEE_NUMBER
                and sysdate between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE
                AND papf.business_group_id=pbg.business_group_id
                and pbg.name=v_business_group;
          
                --FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'v_person_id :'||V_PERSON_ID||', V_PERF_RATING_CODE :'||V_PERF_RATING_CODE);  
                          
                HR_PERF_REVIEW_API.CREATE_PERF_REVIEW( P_VALIDATE                     => V_VALIDATE
                                                      ,P_PERFORMANCE_REVIEW_ID        => V_PERFORMANCE_REVIEW_ID
                                                      ,P_PERSON_ID                    => V_PERSON_ID
                                                      ,P_EVENT_ID                     => null
                                                      ,P_REVIEW_DATE                  => TO_DATE(V_REVIEW_DATE,'dd-mon-yyyy')
                                                      ,P_PERFORMANCE_RATING           => V_PERF_RATING_CODE
                                                      ,P_NEXT_PERF_REVIEW_DATE        => L_NEXT_REVIEW_DATE
                                                      ,P_OBJECT_VERSION_NUMBER        => V_OBJECT_VERSION_NUMBER
                                                      ,P_NEXT_REVIEW_DATE_WARNING     => V_NEXT_REVIEW_DATE_WARNING
                                                      );
      
              
        
            --    FND_FILE.PUT_LINE(FND_FILE.log,V_LINE);
               --rollback;
                v_SUC_count := v_SUC_count +1;                
                 --COMMIT;
            end if;
      EXCEPTION when OTHERS then
        --fnd_file.put_line(fnd_file.log,'V_PERF_RATING_NAME'||V_PERF_RATING_NAME);
        --fnd_file.put_line(fnd_file.log,V_LINE);
        fnd_file.put_line(fnd_file.log,'Failed - '||V_EMPLOYEE_NUMBER||' with Error :'||sqlerrm);
        --EXIT;
        --continue;
        V_FAIL_Count := V_FAIL_Count +1;
      end ;
    
    END LOOP;
 end if;
--rollback;
 commit;
 V_TOT_count:=V_TOT_count-1;
          fnd_file.put_line(fnd_file.output,'Total No of records in file : '||V_TOT_count);
        fnd_file.put_line(fnd_file.output,'Failed records count: '||V_FAIL_Count);
        fnd_file.put_line(fnd_file.output,'Successful records count: '||V_Suc_Count);

EXCEPTION when OTHERS then
        FND_FILE.PUT_LINE(FND_FILE.log,'Other errors :'||SQLERRM);
        UTL_FILE.FCLOSE(V_FILE_TYPE);
END XX_PERF_REVIEW_LOAD_PROC;
end XX_HR_PERFORMANCE_PKG;

Oracle HRMS API – Update Employee Assignment

/*
To Update: Supervisor, Manager Flag, Bargaining Unit, Labour Union Member Flag, Gre, Time Card, Work Schedule, Normal Hours, Frequency, Time Normal Finish, Time Normal Start, Default Code Combination, Set of Books Id

API -- hr_assignment_api.update_emp_asg

To Update: Grade, Location, Job, Payroll, Organization, Employee Category, People Group

API -- hr_assignment_api.update_emp_asg_criteria
*/

--Example --

DECLARE
   -- Local Variables
   -- -----------------------
   LC_DT_UD_MODE          VARCHAR2(100)     := NULL;
   LN_ASSIGNMENT_ID       NUMBER            := 9375;
   LN_SUPERVISOR_ID       NUMBER            := NULL;
   LN_OBJECT_NUMBER       NUMBER            := 1;
   ln_people_group_id  NUMBER               := 1;

   -- Out Variables for Find Date Track Mode API
   -- -----------------------------------------------------------------
   lb_correction                           BOOLEAN;
   lb_update                               BOOLEAN;
   lb_update_override              BOOLEAN;
   lb_update_change_insert   BOOLEAN;
 
   -- Out Variables for Update Employee Assignment API
   -- ----------------------------------------------------------------------------
   ln_soft_coding_keyflex_id      HR_SOFT_CODING_KEYFLEX.SOFT_CODING_KEYFLEX_ID%TYPE;
   lc_concatenated_segments       VARCHAR2(2000);
   LN_COMMENT_ID                  PER_ALL_ASSIGNMENTS_F.COMMENT_ID%TYPE;
   lb_no_managers_warning         BOOLEAN;

 -- Out Variables for Update Employee Assgment Criteria
 -- -------------------------------------------------------------------------------
 ln_special_ceiling_step_id           PER_ALL_ASSIGNMENTS_F.SPECIAL_CEILING_STEP_ID%TYPE;
 lc_group_name                         VARCHAR2(30);
 ld_effective_start_date               PER_ALL_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
 LD_EFFECTIVE_END_DATE                 PER_ALL_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
 lb_org_now_no_manager_warning           BOOLEAN;
 lb_other_manager_warning                BOOLEAN;
 lb_spp_delete_warning                   BOOLEAN;
 lc_entries_changed_warning              VARCHAR2(30);
 lb_tax_district_changed_warn            BOOLEAN;



BEGIN
   -- Find Date Track Mode
   -- --------------------------------
   dt_api.find_dt_upd_modes
   (    p_effective_date                  => TO_DATE('12-JUN-2011'),
        p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
        p_base_key_column           => 'ASSIGNMENT_ID',
        p_base_key_value            => ln_assignment_id,
         -- Output data elements
         -- --------------------------------
         p_correction                          => lb_correction,
         p_update                                => lb_update,
         p_update_override              => lb_update_override,
         p_update_change_insert   => lb_update_change_insert
     );


   IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
   THEN
       -- UPDATE_OVERRIDE
       -- ---------------------------------
       lc_dt_ud_mode := 'UPDATE_OVERRIDE';
   END IF;



  IF ( lb_correction = TRUE )
  THEN
      -- CORRECTION
      -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
  END IF;



  IF ( lb_update = TRUE )
  THEN
      -- UPDATE
      -- --------------
      lc_dt_ud_mode := 'UPDATE';
   END IF;



  -- Update Employee Assignment
  -- ---------------------------------------------
 hr_assignment_api.update_emp_asg
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                              => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode         => lc_dt_ud_mode,
  p_assignment_id                            => ln_assignment_id,
  p_supervisor_id                              => NULL,
  p_change_reason                           => NULL,
  p_manager_flag                              => 'N',
  p_bargaining_unit_code              => NULL,
  p_labour_union_member_flag   => NULL,
  p_segment1                                       => 204,
  p_segment3                                       => 'N',
  p_normal_hours                              => 10,
  p_frequency                                       => 'W',
  -- Output data elements
  -- -------------------------------
  p_object_version_number             => ln_object_number,
  p_soft_coding_keyflex_id              => ln_soft_coding_keyflex_id,
  p_concatenated_segments             => lc_concatenated_segments,
  p_comment_id                                   => ln_comment_id,
  p_effective_start_date                      => ld_effective_start_date,
  p_effective_end_date                        => ld_effective_end_date,
  p_no_managers_warning               => lb_no_managers_warning,
  p_other_manager_warning            => lb_other_manager_warning
 );

 -- Find Date Track Mode for Second API
 -- ------------------------------------------------------
  dt_api.find_dt_upd_modes
  (  p_effective_date                   => TO_DATE('12-JUN-2011'),
     p_base_table_name            => 'PER_ALL_ASSIGNMENTS_F',
     p_base_key_column           => 'ASSIGNMENT_ID',
     p_base_key_value               => ln_assignment_id,
     -- Output data elements
     -- -------------------------------
     p_correction                           => lb_correction,
     p_update                                 => lb_update,
     p_update_override               => lb_update_override,
     p_update_change_insert    => lb_update_change_insert
  );



  IF ( lb_update_override = TRUE OR lb_update_change_insert = TRUE )
  THEN
    -- UPDATE_OVERRIDE
    -- --------------------------------
    lc_dt_ud_mode := 'UPDATE_OVERRIDE';
  END IF;



   IF ( lb_correction = TRUE )
   THEN
     -- CORRECTION
     -- ----------------------
     lc_dt_ud_mode := 'CORRECTION';
  END IF;



   IF ( lb_update = TRUE )
   THEN
     -- UPDATE
     -- --------------
     lc_dt_ud_mode := 'UPDATE';
   END IF;



 -- Update Employee Assgment Criteria
 -- -----------------------------------------------------
 hr_assignment_api.update_emp_asg_criteria
 ( -- Input data elements
  -- ------------------------------
  p_effective_date                                   => TO_DATE('12-JUN-2011'),
  p_datetrack_update_mode               => lc_dt_ud_mode,
  p_assignment_id                                 => ln_assignment_id,
  p_location_id                                        => 204,
  p_grade_id                                             => 29,
  p_job_id                                                  => 16,
  p_payroll_id                                          => 52,
  p_organization_id                               => 239,
  p_employment_category                    => 'FR',
  -- Output data elements
  -- -------------------------------
  p_people_group_id                              => ln_people_group_id,
  p_object_version_number                   => ln_object_number,
  p_special_ceiling_step_id                  => ln_special_ceiling_step_id,
  p_group_name                                        => lc_group_name,
  p_effective_start_date                           => ld_effective_start_date,
  p_effective_end_date                             => ld_effective_end_date,
  p_org_now_no_manager_warning  => lb_org_now_no_manager_warning,
  p_other_manager_warning                 => lb_other_manager_warning,
  p_spp_delete_warning                         => lb_spp_delete_warning,
  p_entries_changed_warning              => lc_entries_changed_warning,
  p_tax_district_changed_warning     => lb_tax_district_changed_warn
 );

 COMMIT;

EXCEPTION
         WHEN OTHERS THEN
                      ROLLBACK;
                      dbms_output.put_line(SQLERRM);
END;
/

Tuesday, September 16

Scheduling the Discoverer reports

Scheduling the Discoverer reports

Scheduling the Discoverer reports can be done only through Discoverer Plus /Desktop and the scheduled reports can be shared to users so that they can see the results from Application.

As the end users may not have access to Discoverer plus / Desktop, Tech team or DBA's have to schedule the reports and to share to the respective users who are looking for the scheduled results.

Prerequisites to schedule the discoverer reports:

user should have Scheduling access to schedule the reports from Discoverer plus/Discoverer Desktop

Steps to Verify and Enable the required Privileges :

Step1: Login to Discoverer Administrator as sysadmin

Step2:  Choose the responsibility "System Administrator"

Step3: Open an Existing Business Area "IDCO UK Contract and Receivables"

Step4 : Go to Tools Menu --> Privileges

Step5: Click on Select button on Privileges tab and search for the username "SYSADMIN" and click on "Go"

Step6: "Schedule Workbooks" Option on "Desktop and Plus Privilege" should be checked. if this was not checked, please check the check box and click on Apply.

Step7: Now navigate to tab "Scheduled workbooks" on same window.

Step8:

1.       Select the 3rd Radio button "Schedule Only if predicted time Exceeds (HH:MM:SS)"

2.       Set any time (ex: 5 mins i.e, 00:05:00)

3.       uncheck the "Limit number of scheduled workbooks" and time

4.       the other options can be choose as per requirement.

Click Apply and OK.


 

Schedule the Discoverer Reports :

Step1: Login to discoverer plus as syadmin ( make sure to chose the connect to as "Oracle Applications") and select the responsibility "System Administrator"

 

Step2 :  Navigate to Tools menu --> Manage Schedules

Step3: Click on Schedule and select the workbook. (here I am selecting the workbook ' IDCO UK 7 Buckets Aging Report ')

 

Step4:

1.       Enter the name for scheduled work book, (this should be unique, if you have entered any name that was being used by any other user, it will ask you to rename.)

2.       Select the work sheets to be run on the work book and click on Next

Step5:  Enter the Parameters and click on Next.

Step 6: Schedule the report as per your requirement and Finish. (for demo, I am choosing to run the report immediately and repeat for every minute and selecting the option 'yes, Keep all results'         and delete results after 7 days)

               

Step7: Click on share and choose all the users to access this scheduled workbook and click ok.

The scheduled reports can be unscheduled or deleted from the same window.


 

 

View the results from Application :

Step1: Login to application as end user and open any discoverer report as they do daily.

Step2: Instead of running the report select the link "workbook" on the top and choose scheduled workbooks from the lov and click on GO .

Here user can see all the scheduled reports that are shared to him.

in this example I have scheduled only one report to run for every minute , we can schedule n number of reports and also one report with multiple parameters.

 

Friday, August 22

Oracle EBS API to change user password and prompt to change their password the first attempt to login to the applications


The following API can be used to change the Ebs user password and prompt to change the same at the first logon,

    begin
    fnd_user_pkg.updateuser(
    x_user_name => 'username'
    , x_owner => 'CUST'
    , x_unencrypted_password => 'change123',
    x_password_date => to_date('2','J')
    );
    commit;
    end;

If we don't want to force the user to change the password, then we can remove the x_password_date from the api.

The standard FND_USER_PKG does this:

    decode(x_password_date, fnd_user_pkg.null_date, null,null,u.password_date, x_password_date)

    fnd_user_pkg.null_date is defined as to_date('2','J'),    which is 02-JAN-4712.

Hence, this decode says:

1. If the password_date parm = 02-JAN-4712, then set password date = null.
2. If the password_date is null, then set password date = existing password date (in effect does nothing)
3. Else use the existing password date (in effect does nothing).

Thursday, August 7

Value Set


Navigation Path for Value Set Creation?

Navigation Path for Value Set Creation:
Application-> Validation -> Set.
Once we create Independent & Dependent valueset then we can attach values to the valueset by using the following Navigation.
Application -> -> Validation
-> Values (To create values for value set)
NOTE: Once we attach any value to Independent & Dependent we can’t delete that value, but we can disable that value. Duplicate values are not allowed in list of values.

Develop a Report using Query and by creating valueset :Select USER_ID, USERNAME From FND_USER Where
USER_ID Between :X AND :Y
:$FLEX$ - It is One of the Oracle applications Key word which we use to get the prevents parameter value in current list of values “WHERE Clause”. We can be use Table Values in the “Where Clause Box”.
Query Using: $FLEX$

Select
      VENDOR_SITE_ID
From
      PO_VENDOR_SITE_ALL
Where
      VENODR_ID = :$FLEX$.VEN_TABLE


Note: We can give Where Clause Condition in creation of Second Value Set.
Practical:Query:
Select *
From ORF_ORGANIZATION_DEFINATIONS
Where ORGANIZATION_ID = :P_ORG_ID
And BUSINESS_GROUP_ID = :P_BUSINESS_GROUP_ID

In Where Clause write the statement as
Where
BUSINESS_GROUP_ID = :$FLEX$.BUISINESS_GROUP

Range:
When ever we have to restrict the user with in the given values we use Range. For example when ever our parameter is having “From Date and To Date” we have to use Range option to restrict the user to enter the values between Low and High.
Note:
Pre defined value set for date is “FND_DATE” and its default format is “DD-MON-YY”.Alias name is mandatory when we are specifying ‘:$FLEX$’ and Column Name in ‘Additional Column’.

What is Value Sets? What are Types Of Value Sets?

Oracle Application Object Library uses values; value sets and validation tables as important components of key FLEXFIELDs, descriptive FLEXFIELDs, and Standard Request Submission. This section helps you understand, use and change values, value sets, and validation tables. When you first define your FLEXFIELDs, you choose how many segments you want to use and what order you want them to appear. You also choose how you want to validate each of your segments. The decisions you make affect how you define your value sets and your values. You define your value sets first, either before or while you define your FLEXFIELD segment structures. You typically define your individual values only after your FLEXFIELD has been completely defined (and frozen and compiled). Depending on what type of value set you use, you may not need to predefine individual values at all before you can use your FLEXFIELD.
You can share value sets among segments in different FLEXFIELDs, segments in different structures of the same FLEXFIELD, and even segments within the same FLEXFIELD structure. You can share value sets across key and descriptive FLEXFIELDs. You can also use value sets for report parameters for your reports that use the Standard Request Submission feature.
Because the conditions you specify for your value sets determine what values you can use with them, you should plan both your values and your value sets at the same time. For example, if your values are 01, 02 instead of 1, 2, you would define the value set with Right–Justify Zero–fill set to Yes.

Value set is nothing but List of Values with validations. We can use the Value Sets when ever the Concurrent Program has parameters and while defining the Flex Fields. We have to attach the value sets to the Concurrent Program. Validations are depending on Client Requirement.

Value sets are of 8 types.There are several validation types that affect the way users enter and use segment or parameter values:
1. None (not validated at all)
2. Independent
3. Dependent
4. Table
5. Special (advanced)
6. Pair (advanced)
7. Translatable Independent
8. Translatable Dependent

You cannot change the validation type of an existing value set, since your changes affect all FLEXFIELDs and report parameters that use the same value set.

None:
 You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set. For example, if the value set does not allow alphabetic characters, your user could not enter the value ABC, but could enter the value 456 (for a value set with maximum length of three). The values of the segment using this value set are not otherwise validated, and they do not have descriptions. Because a NONE value set is not validated, a segment that uses this value set does not provide a list of values for your users. A segment that uses this value set (that is, a non–validated segment) cannot use FLEXFIELD value security rules to restrict the values a user can enter.

Independent :
 An Independent value set provides a predefined list of values for a segment. These values can have an associated description. For example, the value 01 could have a description of ‘Company 01’. The meaning of a value in this value set does not depend on the value of any other segment. Independent values are stored in an Oracle Application Object Library table. You define independent values using an Oracle Applications window, Segment Values.

Table :
  A table–validated value set provides a predefined list of values like an independent set, but its values are stored in an application table. You define which table you want to use, along with a WHERE cause to limit the values you want to use for your set. Typically, you use a table–validated set when you have a table whose values are already maintained in an application table (for example, a table of vendor names maintained by a Define Vendors form). Table validation also provides some advanced features such as allowing a segment to depend upon multiple prior segments in the same structure.

Dependent :
 A dependent value set is similar to an independent value set, except that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. You can think of a dependent value set as a collection of little value sets, with one little set for each independent value in the corresponding independent value set. You must define your independent value set before you define the dependent value set that depends on it. You define dependent values in the Segment Values windows, and your values are stored in an Oracle Application Object Library table.

Special and Pair Value Sets:
Special and pair value sets provide a mechanism to allow a”FLEXFIELD–within–a–FLEXFIELD”. These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal FLEXFIELD segments. Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another FLEXFIELD as a value set for a single segment or to provide a range FLEXFIELD as a value set for a pair of segments.

Translatable Independent and Translatable Dependent :A Translatable Independent value set is similar to Independent value set in that it provides a predefined list of values for a segment. However, a translated value can be used. A Translatable Dependent value set is similar to Dependent value set in that the available values in the list and the meaning of a given value depend on which independent value was selected in a prior segment of the FLEXFIELD structure. However, a translated value can be used. FLEXFIELD Value Security cannot be used with Translatable Independent or Translatable Dependent value sets. For format validation, translatable value sets must use the format type Char. The maximum size must be no greater than 150. The Number Only option and the Right–justify and Zero–Fill Numbers option cannot be used with translatable value sets. Range FLEXFIELDs cannot use Translatable Independent or Translatable Dependent value sets.

XML Parsing -reading xml file in oracle pl sql example

Please find the below stand alone script followed by sample XML file that I am using for this script.

Steps to be performed.

  1. create a XML file with name OMX_Cxml.xml (by copying the XML file data provided at the end of this post).
  2. Place the xml file in any server path ( in this example I have placed the file in USER_TEMP directory) directory namd and path can be seen in all_directories table
  3. Execute the below script and understand how am i reading each node and attribute value also How am i handling the loops for repeating nodes .
  4. Modify the script as per your xml file.


declare
  -- below variables are used in XML parsing or for api dbms_xmlparser
  L_BFILE bfile;                              -- this holds the binary file (from respective path)
  L_CLOB CLOB;                                -- this is to read the data from bfile into clob type
  l_parser dbms_xmlparser.parser;             -- this variable for parsing clob value to XMLparser
  MY_DOC DBMS_XMLDOM.DOMDOCUMENT;             -- this vairable shall hold XML parsed data
  CURRENT_ITEM_LIST DBMS_XMLDOM.DOMNODELIST;  -- this represents the XML group of nodes (1 or  repeating) and all it sub tags
  CURRENT_ITEM DBMS_XMLDOM.DOMNODE;           -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags
  CURRENT_ITEM_LIST1 DBMS_XMLDOM.DOMNODELIST; -- this represents the XML group of nodes (1 or  repeating) and all it sub tags
  CURRENT_ITEM1 DBMS_XMLDOM.DOMNODE;          -- this represents the XML group of nodes (only one instance of repeating group) and all it sub tags
  XMLELEM XMLDOM.DOMELEMENT;                  --This will read the root element information. In our file rooot element is :<cXML ..>
  NSPACE VARCHAR2(50);                        --This will read name sapce of root element information. In our file rooot element is :<cXML ..> and no namespaces are defined in file
  --- below variables are to be passed as parameters for DBMS_LOB.LOADCLOBFROMFILE
  L_DEST_OFFSET  NUMBER :=1;
  L_SRC_OFFSET   NUMBER :=1;
  L_BFILE_CSID   NUMBER := 0;
  L_LANG_CONTEXT NUMBER := DBMS_LOB.DEFAULT_LANG_CTX;
  L_WARNING      NUMBER;
  -- Declare local variables the correspond to the XML data
  -- in more serious implementation , consider using RECORD type
  ---Header information
  from_credential     VARCHAR2(1000);
  from_identity       VARCHAR2(1000);
  from_sharedsecret   VARCHAR2(1000);
  to_credential       VARCHAR2(1000);
  to_identity         VARCHAR2(1000);
  to_sharedsecret     VARCHAR2(1000);
  sender_credential   VARCHAR2(1000);
  sender_identity     VARCHAR2(1000);
  sender_sharedsecret VARCHAR2(1000);
  SENDER_USERAGENT    VARCHAR2(1000);
  ------------------
  ---Request information
  L_DEPLOYMENTMODE     VARCHAR2(1000);
  L_INVOICEID          VARCHAR2(1000);
  L_PURPOSE            VARCHAR2(1000);
  L_OPERATION          VARCHAR2(1000);
  L_INVOICEDATE        VARCHAR2(1000);
  L_ISACCOUNTINGINLINE VARCHAR2(1000);
  L_PAYINNUMBEROFDAYS  NUMBER;
  L_PERCENTAGERATE     NUMBER;
  L_EXTRINSIC_NAME     VARCHAR2(1000);
  L_EXTRINSIC_value    VARCHAR2(1000);
  --invoice partner information
  L_CONTACT_ROLE                VARCHAR2(1000);
  L_CONTACT_NAME                VARCHAR2(1000);
  L_CONTACT_STREET1             VARCHAR2(1000);
  L_CONTACT_STREET2             VARCHAR2(1000);
  L_CONTACT_CITY                VARCHAR2(1000);
  L_CONTACT_STATE               VARCHAR2(1000);
  L_CONTACT_POSTALCODE          VARCHAR2(1000);
  L_CONTACT_COUNTRY             VARCHAR2(1000);
  L_CONTACT_FROM_NAME           VARCHAR2(1000);
  L_CONTACT_FROM_STREET1        VARCHAR2(1000);
  L_CONTACT_FROM_STREET2        VARCHAR2(1000);
  L_CONTACT_FROM_CITY           VARCHAR2(1000);
  L_CONTACT_FROM_STATE          VARCHAR2(1000);
  L_CONTACT_FROM_POSTALCODE     VARCHAR2(1000);
  L_CONTACT_FROM_COUNTRY        VARCHAR2(1000);
  L_CONTACT_SOLDTO_NAME         VARCHAR2(1000);
  L_CONTACT_SOLDTO_STREET1      VARCHAR2(1000);
  L_CONTACT_SOLDTO_STREET2      VARCHAR2(1000);
  L_CONTACT_SOLDTO_CITY         VARCHAR2(1000);
  L_CONTACT_SOLDTO_STATE        VARCHAR2(1000);
  L_CONTACT_SOLDTO_POSTALCODE   VARCHAR2(1000);
  L_CONTACT_SOLDTO_COUNTRY      VARCHAR2(1000);
  L_CONTACT_BILLTO_NAME         VARCHAR2(1000);
  L_CONTACT_BILLTO_STREET1      VARCHAR2(1000);
  L_CONTACT_BILLTO_STREET2      VARCHAR2(1000);
  L_CONTACT_BILLTO_CITY         VARCHAR2(1000);
  L_CONTACT_BILLTO_STATE        VARCHAR2(1000);
  L_CONTACT_BILLTO_POSTALCODE   VARCHAR2(1000);
  L_CONTACT_BILLTO_COUNTRY      VARCHAR2(1000);
  L_CONTACT_RMTTO_NAME          VARCHAR2(1000);
  L_CONTACT_RMTTO_STREET1       VARCHAR2(1000);
  L_CONTACT_RMTTO_STREET2       VARCHAR2(1000);
  L_CONTACT_RMTTO_CITY          VARCHAR2(1000);
  L_CONTACT_RMTTO_STATE         VARCHAR2(1000);
  L_CONTACT_RMTTO_POSTALCODE    VARCHAR2(1000);
  L_CONTACT_RMTTO_COUNTRY       VARCHAR2(1000);
  L_CONTACT_SHIPROLE            VARCHAR2(1000);
  L_CONTACT_SHIPFROM_NAME       VARCHAR2(1000);
  L_CONTACT_SHIPFROM_STREET1    VARCHAR2(1000);
  L_CONTACT_SHIPFROM_STREET2    VARCHAR2(1000);
  L_CONTACT_SHIPFROM_CITY       VARCHAR2(1000);
  L_CONTACT_SHIPFROM_STATE      VARCHAR2(1000);
  L_CONTACT_SHIPFROM_POSTALCODE VARCHAR2(1000);
  L_CONTACT_SHIPFROM_COUNTRY    VARCHAR2(1000);
  L_CONTACT_SHIPTO_NAME         VARCHAR2(1000);
  L_CONTACT_SHIPTO_STREET1      VARCHAR2(1000);
  L_CONTACT_SHIPTO_STREET2      VARCHAR2(1000);
  L_CONTACT_SHIPTO_CITY         VARCHAR2(1000);
  L_CONTACT_SHIPTO_STATE        VARCHAR2(1000);
  L_CONTACT_SHIPTO_POSTALCODE   VARCHAR2(1000);
  L_CONTACT_SHIPTO_COUNTRY      VARCHAR2(1000);
  L_ORDER_ID                    NUMBER;
  L_PAYLOADID                   VARCHAR2(1000);
  L_INVOICELINENUMBER           NUMBER;
  L_QUANTITY                    NUMBER;
  L_UNITOFMEASURE               VARCHAR2(100);
  L_CURRENCY                    VARCHAR2(100);
  L_UNITPRICE                   VARCHAR2(100);
  L_INV_DET_ITEM_REF_LINENUM    NUMBER;
  L_SUPPLIERPARTID              VARCHAR2(100);
  L_DESCRIPTION                 VARCHAR2(1000);
  L_MANUFACTURERPARTID          VARCHAR2(1000);
  L_MANUFACTURERNAME            VARCHAR2(1000);
  L_SUBTOTALAMOUNT_CUR          VARCHAR2(1000);
  L_SUBTOTALAMOUNT              NUMBER;
  L_GROSSAMOUNT_CUR             VARCHAR2(1000);
  L_GROSSAMOUNT                 NUMBER;
  L_NETAMOUNT_CUR               VARCHAR2(1000);
  L_NETAMOUNT                   NUMBER;
  L_SRY_SUBTOTAL_CUR            VARCHAR2(1000);
  L_SRY_SUBTOTAL_AMOUNT         NUMBER;
  L_SRY_TAX_CUR                 VARCHAR2(1000);
  L_SRY_TAX_AMOUNT              NUMBER;
  L_SRY_TAX_PURPOSE             VARCHAR2(1000);
  L_SRY_TAX_CATEGORY            VARCHAR2(1000);
  L_SRY_TAX_RATE                VARCHAR2(1000);
  L_SRY_TAXABLE_CUR             VARCHAR2(1000);
  L_SRY_TAXABLE_AMOUNT          NUMBER;
  L_SRY_TAX_CUR1                VARCHAR2(1000);
  L_SRY_TAX_AMOUNT1             NUMBER;
  L_SRY_GROSS_CUR               VARCHAR2(1000);
  L_SRY_GROSS_AMOUNT            NUMBER;
  L_SRY_NET_CUR                 VARCHAR2(1000);
  L_SRY_NET_AMOUNT              NUMBER;
  L_SRY_DUE_CUR                 VARCHAR2(1000);
  L_SRY_DUE_AMOUNT              NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('begin :');
  -- Point to the XML file called 'OMX_Cxml.xml'which is located in the directory_path pointed at USER_TEMP in table all_directories
  L_BFILE := BFILENAME('USER_TEMP', 'OMX_Cxml.xml');
  -- creating a temporary space for clob variable with out cache so that it will not have any performance impact
  DBMS_LOB.CREATETEMPORARY(L_CLOB, cache=>false);
  -- Open the XML file in read mode
  dbms_lob.open(l_bfile, dbms_lob.lob_readonly);
  -- tranfer the file contents to CLOB variable
  --DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));
  dbms_lob.loadclobfromfile(dest_lob => l_clob, src_bfile => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE), DEST_OFFSET => L_DEST_OFFSET, SRC_OFFSET => L_SRC_OFFSET, BFILE_CSID => DBMS_LOB.DEFAULT_CSID, LANG_CONTEXT => L_LANG_CONTEXT, warning => l_warning );
  --close the bfile instance
  dbms_lob.close(l_bfile);
  -- This ends file manipulation and the file contents are now in a CLOB vairable
  -- This entry is important to deal with DATE format in the XML data
  dbms_session.set_nls('NLS_DATE_FORMAT','''DD-MON-YYYY''');
  -- Starting XML processing business
  -- Create a parser.
  L_PARSER := DBMS_XMLPARSER.NEWPARSER;
  -- Parse the document and create a new DOM document.
  -- temporarily I am prting the XML data for testing
  --DBMS_OUTPUT.PUT_LINE('parser : l_clob---'||L_CLOB);
  -- parse the xml data from clob variabl to XML_parser
  --DBMS_XMLPARSER.PARSECLOB(L_PARSER,L_CLOB);
  DBMS_XMLPARSER.PARSECLOB(L_PARSER, REGEXP_REPLACE(L_CLOB,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));
  -- The xml parsed CLOB is transfered to a variable called my_doc
  my_doc  := dbms_xmlparser.getdocument(l_parser);
  xmlelem := xmldom.getdocumentelement(my_doc);
  nspace  := xmldom.getnamespace(xmlelem);
  DBMS_OUTPUT.PUT_LINE('XML Root element information');
  DBMS_OUTPUT.PUT_LINE('Qualified Name: ' || DBMS_XMLDOM.GETQUALIFIEDNAME(XMLELEM));
  DBMS_OUTPUT.PUT_LINE('Local Name: ' || DBMS_XMLDOM.GETLOCALNAME(XMLELEM));
  DBMS_OUTPUT.PUT_LINE('Namespace: ' || NSPACE);
  DBMS_OUTPUT.PUT_LINE('Expanded Name: ' || DBMS_XMLDOM.GETEXPANDEDNAME(XMLELEM));
  -- Free resources associated with the CLOB and Parser now they are no longer needed.
  dbms_lob.freetemporary(l_clob);
  DBMS_XMLPARSER.FREEPARSER(L_PARSER);
  dbms_output.put_line('parser free:');
  -- The following statement shall search for a node starting with 'cXML/Header'
  -- Then it shall get the no of entries in this node. In our example the 'cXML/Header' contains only one
  -- entry, but a big one, because it contains from to and sender XML (there is ony one <Header> </Header>
  current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Header');
  -- note no slash at end
  DBMS_OUTPUT.PUT_LINE('#############     header loop begin #################');
  dbms_output.put_line('Count of cXML/Header : '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
  FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
  LOOP -- it should loop only once because the Header is only one instance
  
    current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header>
    DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
    --dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case
    --#############-------------Header/From----------------##############
    dbms_output.put_line('---------------From----------------');
    dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);
    dbms_output.put_line('From/Credential/@domain: '||from_credential);
    dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity);
    dbms_output.put_line('From/Credential/Identity:'||from_identity);
    dbms_xslprocessor.valueof(current_item,'From/Credential/SharedSecret/text()',from_sharedsecret);
    DBMS_OUTPUT.PUT_LINE('From/Credential/SharedSecret:'||FROM_SHAREDSECRET);
    --#############-------------Header/To----------------##############
    dbms_output.put_line('---------------To----------------');
    dbms_xslprocessor.valueof(current_item,'To/Credential/@domain',to_credential);
    dbms_output.put_line('From/Credential/@domain: '||to_credential);
    dbms_xslprocessor.valueof(current_item,'To/Credential/Identity/text()',to_identity);
    dbms_output.put_line('To/Credential/Identity:'||to_identity);
    dbms_xslprocessor.valueof(current_item,'To/Credential/SharedSecret/text()',to_sharedsecret);
    DBMS_OUTPUT.PUT_LINE('To/Credential/SharedSecret:'||TO_SHAREDSECRET);
    --#############-------------Header/Sender----------------##############
    dbms_output.put_line('---------------Sender----------------');
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/@domain',Sender_credential);
    dbms_output.put_line('Sender/Credential/@domain: '||Sender_credential);
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/Identity/text()',sender_identity);
    dbms_output.put_line('Sender/Credential/Identity:'||sender_identity);
    dbms_xslprocessor.valueof(current_item,'Sender/Credential/SharedSecret/text()',sender_sharedsecret);
    dbms_output.put_line('Sender/Credential/SharedSecret:'||sender_sharedsecret);
    dbms_xslprocessor.valueof(current_item,'Sender/UserAgent/text()',sender_UserAgent);
    DBMS_OUTPUT.PUT_LINE('Sender/UserAgent:'||SENDER_USERAGENT);
  END LOOP;
  --Now the following statement shall search for a node starting with 'cXML/Request'
  -- Then it shall get the no of entries in this node. In our example the 'cXML/Request' contains only one
  -- entry, but a big one, because it contains from to and sender XML (there is ony one <Request> </Request>
  current_item_list := dbms_xslprocessor.selectnodes(dbms_xmldom.makenode(my_doc),'cXML/Request');
  -- note no slash at end
  DBMS_OUTPUT.PUT_LINE('#############     Request loop begin      #################');
  dbms_output.put_line('Count of cXML/Request : '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
  FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
  LOOP -- it should loop only once because the Request is only one instance
  
    current_item := dbms_xmldom.item(current_item_list, cur_ent); --READ the current item from current entry of <Header>
    DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
    --dbms_output.put_line('current_item:'|| dbms_xmldom.getnodevalue(current_item)); --this will not work in our case
    DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@deploymentMode',L_DEPLOYMENTMODE);
    DBMS_OUTPUT.PUT_LINE('Request/@domain: '||l_deploymentMode);
    CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest');
    -- note no slash at end
    DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailRequest loop begin      #################');
    dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest: '||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
    FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
    LOOP
    
      CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
      DBMS_OUTPUT.PUT_LINE('current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM));
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceID',L_INVOICEID);
      dbms_output.put_line('InvoiceDetailRequestHeader/@invoiceID:'||L_INVOICEID );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@purpose',L_PURPOSE);
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@purpose:'||L_PURPOSE );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@operation',L_OPERATION );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@operation:'||L_OPERATION );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/@invoiceDate',L_INVOICEDATE );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/@invoiceDate:'||L_INVOICEDATE );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailLineIndicator/@isAccountingInLine',L_ISACCOUNTINGINLINE );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailLineIndicator/@isAccountingInLine:'||L_ISACCOUNTINGINLINE );
      --- retriving the InvoiceDetailPaymentTerm and Extrinsic details before re-initialinf current item list for retriving InvoicePartner & InvoiceDetailShipping
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@payInNumberOfDays',l_payInNumberOfDays );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@payInNumberOfDays:'||l_payInNumberOfDays );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/InvoiceDetailPaymentTerm/@percentageRate',l_percentageRate );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_percentageRate );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/@name',l_Extrinsic_name );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailRequestHeader/Extrinsic/@name'||L_EXTRINSIC_NAME );
      DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailRequestHeader/Extrinsic/text()',l_Extrinsic_value );
      DBMS_OUTPUT.PUT_LINE('InvoiceDetailPaymentTerm/@percentageRate:'||l_Extrinsic_value );
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner');
      -- note no slash at end
    
      DBMS_OUTPUT.PUT_LINE('#############     InvoicePartner loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node
      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP
      
        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('##############current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/@role',L_CONTACT_ROLE );
        DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_ROLE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/Name/text()', L_CONTACT_NAME);                         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Street[position()=2]/text()', L_CONTACT_STREET2);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/City/text()', L_CONTACT_CITY);           
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/State/text()', L_CONTACT_STATE);         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Contact/PostalAddress/Country/text()', L_CONTACT_COUNTRY);     
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Name: '|| L_CONTACT_NAME);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' City: '|| L_CONTACT_CITY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' State: '|| L_CONTACT_STATE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Country: '|| L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street1: '|| L_CONTACT_STREET1);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_ROLE||' Street2: '|| L_CONTACT_STREET2);
        IF L_CONTACT_ROLE              = 'from' THEN
          L_CONTACT_FROM_NAME         := L_CONTACT_NAME;
          L_CONTACT_FROM_CITY         := L_CONTACT_CITY;
          L_CONTACT_FROM_STATE        := L_CONTACT_STATE;
          L_CONTACT_FROM_POSTALCODE   := L_CONTACT_POSTALCODE;
          L_CONTACT_FROM_COUNTRY      := L_CONTACT_COUNTRY ;
          L_CONTACT_FROM_Street1      := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'soldTo' THEN
          L_CONTACT_SOLDTO_NAME       := L_CONTACT_NAME;
          L_CONTACT_SOLDTO_CITY       := L_CONTACT_CITY;
          L_CONTACT_SOLDTO_STATE      := L_CONTACT_STATE;
          L_CONTACT_SOLDTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_SOLDTO_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_SOLDTO_Street1    := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'remitTo' THEN
          L_CONTACT_RMTTO_NAME        := L_CONTACT_NAME;
          L_CONTACT_RMTTO_CITY        := L_CONTACT_CITY;
          L_CONTACT_RMTTO_STATE       := L_CONTACT_STATE;
          L_CONTACT_RMTTO_POSTALCODE  := L_CONTACT_POSTALCODE;
          L_CONTACT_RMTTO_COUNTRY     := L_CONTACT_COUNTRY ;
          L_CONTACT_RMTTO_Street1     := L_CONTACT_STREET1 ;
        elsif L_CONTACT_ROLE           = 'billTo' THEN
          L_CONTACT_billTO_NAME       := L_CONTACT_NAME;
          L_CONTACT_BILLTO_CITY       := L_CONTACT_CITY;
          L_CONTACT_BILLTO_STATE      := L_CONTACT_STATE;
          L_CONTACT_BILLTO_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_BILLTO_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_BILLTO_Street1    := L_CONTACT_STREET1 ;
        END IF;
      END LOOP;
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoiceDetailShipping/Contact');
      -- note no slash at end
     DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailShipping loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailRequestHeader/InvoicePartner'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node
   
      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP
      
        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('##########current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'@role',L_CONTACT_SHIPROLE );
        DBMS_OUTPUT.PUT_LINE('Contact/@role : '||L_CONTACT_SHIPROLE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Name/text()', L_CONTACT_NAME);                         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=1]/text()', L_CONTACT_STREET1);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Street[position()=2]/text()', L_CONTACT_Street2);      
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/City/text()', L_CONTACT_CITY);           
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/State/text()', L_CONTACT_STATE);         
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/PostalCode/text()', L_CONTACT_POSTALCODE);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'PostalAddress/Country/text()', L_CONTACT_COUNTRY);     
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Name: '|| L_CONTACT_NAME);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' City: '|| L_CONTACT_CITY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' State: '|| L_CONTACT_STATE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Postalcode: '|| L_CONTACT_POSTALCODE);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Country: '|| L_CONTACT_COUNTRY);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street1: '|| L_CONTACT_STREET1);
        DBMS_OUTPUT.PUT_LINE(L_CONTACT_SHIPROLE||' Street2: '|| L_CONTACT_STREET2);
        IF L_CONTACT_SHIPROLE            = 'shipFrom' THEN
          L_CONTACT_SHIPFROM_NAME       := L_CONTACT_NAME;
          L_CONTACT_SHIPFROM_CITY       := L_CONTACT_CITY;
          L_CONTACT_SHIPFROM_STATE      := L_CONTACT_STATE;
          L_CONTACT_SHIPFROM_POSTALCODE := L_CONTACT_POSTALCODE;
          L_CONTACT_SHIPFROM_COUNTRY    := L_CONTACT_COUNTRY ;
          L_CONTACT_SHIPFROM_STREET1    := L_CONTACT_STREET1 ;
        elsif L_CONTACT_SHIPROLE         = 'shipTo' THEN
          L_CONTACT_SHIPTO_NAME         := L_CONTACT_NAME;
          L_CONTACT_SHIPTO_CITY         := L_CONTACT_CITY;
          L_CONTACT_SHIPTO_STATE        := L_CONTACT_STATE;
          L_CONTACT_SHIPTO_POSTALCODE   := L_CONTACT_POSTALCODE;
          L_CONTACT_SHIPTO_COUNTRY      := L_CONTACT_COUNTRY ;
          L_CONTACT_SHIPTO_STREET1      := L_CONTACT_STREET1 ;
        END IF;
      END LOOP;
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder');
      -- note no slash at end
      DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailOrder loop begin      #################');
      DBMS_OUTPUT.PUT_LINE('count of cXML/Request/InvoiceDetailRequest/InvoiceDetailOrder'||DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)); -- find the no of entries in this node
    
      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP
        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('#########current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/@orderID',L_ORDER_ID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/@orderID: '||L_ORDER_ID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID', L_PAYLOADID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailOrderInfo/OrderReference/DocumentReference/@payloadID: '||L_PAYLOADID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@invoiceLineNumber', L_INVOICELINENUMBER );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@invoiceLineNumber: '||L_INVOICELINENUMBER );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/@quantity', L_QUANTITY );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/@quantity: '||L_QUANTITY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitOfMeasure/text()', L_UNITOFMEASURE );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitOfMeasure/text(): '||L_UNITOFMEASURE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/@currency', L_CURRENCY );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/@currency: '||L_CURRENCY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/UnitPrice/Money/text()', L_UNITPRICE );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/UnitPrice/Money/text(): '||L_UNITPRICE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber', L_INV_DET_ITEM_REF_LINENUM );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/@lineNumber: '||L_INV_DET_ITEM_REF_LINENUM );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text()', L_SUPPLIERPARTID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ItemID/SupplierPartID/text(): '||L_SUPPLIERPARTID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/Description/text()', L_DESCRIPTION );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/Description/text(): '||L_DESCRIPTION );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text()', L_MANUFACTURERPARTID );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerPartID/text(): '||L_MANUFACTURERPARTID );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text()', L_MANUFACTURERNAME );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/InvoiceDetailItemReference/ManufacturerName/text(): '||L_MANUFACTURERNAME );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/@currency', L_SUBTOTALAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/@currency: '||L_SUBTOTALAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/SubtotalAmount/Money/text()', L_SUBTOTALAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/SubtotalAmount/Money/text(): '||L_SUBTOTALAMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/@currency', L_GROSSAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/@currency: '||L_GROSSAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/GrossAmount/Money/text()', L_GROSSAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/GrossAmount/Money/text(): '||L_GROSSAMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/@currency', L_NETAMOUNT_CUR );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/@currency: '||L_NETAMOUNT_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'InvoiceDetailItem/NetAmount/Money/text()', L_NETAMOUNT );
        DBMS_OUTPUT.PUT_LINE('InvoiceDetailItem/NetAmount/Money/text(): '||L_NETAMOUNT );
      end LOOP;
    
      CURRENT_ITEM_LIST := DBMS_XSLPROCESSOR.SELECTNODES(DBMS_XMLDOM.MAKENODE(MY_DOC),'cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary');
      -- note no slash at end
      DBMS_OUTPUT.PUT_LINE('#############     InvoiceDetailSummary loop begin      #################');
      dbms_output.put_line('Count of cXML/Request/InvoiceDetailRequest/InvoiceDetailSummary'||dbms_xmldom.getLength(current_item_list)); -- find the no of entries in this node
      FOR CUR_ENT IN 0 .. DBMS_XMLDOM.GETLENGTH(CURRENT_ITEM_LIST)-1
      LOOP
        CURRENT_ITEM := DBMS_XMLDOM.ITEM(CURRENT_ITEM_LIST, CUR_ENT);
        DBMS_OUTPUT.PUT_LINE('#######current_item:'|| DBMS_XMLDOM.GETNODENAME(CURRENT_ITEM)||' - ' ||CUR_ENT);
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/@currency', L_SRY_SUBTOTAL_CUR );
        DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/@currency: '||L_SRY_SUBTOTAL_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'SubtotalAmount/Money/text()', L_SRY_SUBTOTAL_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('SubtotalAmount/Money/text(): '||L_SRY_SUBTOTAL_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/@currency', L_SRY_TAX_CUR );
        DBMS_OUTPUT.PUT_LINE('Tax/Money/@currency: '||L_SRY_TAX_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/Money/text()', L_SRY_TAX_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('Tax/Money/text(): '||L_SRY_TAX_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@purpose', L_SRY_TAX_PURPOSE );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@purpose: '||L_SRY_TAX_PURPOSE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@category', L_SRY_TAX_CATEGORY );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@category: '||L_SRY_TAX_CATEGORY );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/@percentageRate', L_SRY_TAX_RATE );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/@percentageRate: '||L_SRY_TAX_RATE );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/@currency', L_SRY_TAXABLE_CUR );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/@currency: '||L_SRY_TAXABLE_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxableAmount/Money/text()', L_SRY_TAXABLE_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxableAmount/Money/text(): '||L_SRY_TAXABLE_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/@currency', L_SRY_TAX_CUR1 );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/@currency: '||L_SRY_TAX_CUR1 );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'Tax/TaxDetail/TaxAmount/Money/text()', L_SRY_TAX_AMOUNT1 );
        DBMS_OUTPUT.PUT_LINE('Tax/TaxDetail/TaxAmount/Money/text(): '||L_SRY_TAX_AMOUNT1 );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/@currency', L_SRY_GROSS_CUR );
        DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/@currency: '||L_SRY_GROSS_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'GrossAmount/Money/text()', L_SRY_GROSS_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('GrossAmount/Money/text(): '||L_SRY_GROSS_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/@currency', L_SRY_NET_CUR );
        DBMS_OUTPUT.PUT_LINE('NetAmount/Money/@currency: '||L_SRY_NET_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'NetAmount/Money/text()', L_SRY_NET_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('NetAmount/Money/text(): '||L_SRY_NET_AMOUNT );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/@currency', L_SRY_DUE_CUR );
        DBMS_OUTPUT.PUT_LINE('DueAmount/Money/@currency: '||L_SRY_DUE_CUR );
        DBMS_XSLPROCESSOR.VALUEOF(CURRENT_ITEM,'DueAmount/Money/text()', L_SRY_DUE_AMOUNT );
        DBMS_OUTPUT.PUT_LINE('DueAmount/Money/text(): '||L_SRY_DUE_AMOUNT );
      END LOOP;
    END LOOP;
  END LOOP;
  dbms_xmldom.freedocument(my_doc);
EXCEPTION
WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('sqlerrm :'||SQLERRM);
  dbms_lob.close(l_bfile);
  DBMS_LOB.FREETEMPORARY(L_CLOB);
  DBMS_XMLPARSER.FREEPARSER(L_PARSER);
  DBMS_XMLDOM.FREEDOCUMENT(MY_DOC);
end;
/


-------------------XML File---------------
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd"><cXML version="1.2.009" payloadID="1405088578064.1105606342@qa-is82-real01.officemax.omx.com" timestamp="2014-07-11T12:00:00-00:00" xml:lang="en-US">
  <Header>
    <From>
      <Credential domain="DUNS">
        <Identity>178923231</Identity>
        <SharedSecret>boise1</SharedSecret>
      </Credential>
    </From>
    <To>
      <Credential domain="DUNS">
        <Identity>119851215</Identity>
      </Credential>
    </To>
    <Sender>
      <Credential domain="OracleSN">
        <Identity>oraclesn</Identity>
        <SharedSecret>boise1</SharedSecret>
      </Credential>
      <UserAgent>Oracle Supplier Network</UserAgent>
    </Sender>
  </Header>
  <Request deploymentMode="test">
    <InvoiceDetailRequest>
      <InvoiceDetailRequestHeader invoiceID="755055Jun2414" purpose="standard" operation="new" invoiceDate="2014-06-24T12:00:00-00:00">
        <InvoiceDetailHeaderIndicator/>
        <InvoiceDetailLineIndicator isAccountingInLine="yes"/>
        <InvoicePartner>
          <Contact role="from">
            <Name xml:lang="en-US">Laura Sebesta</Name>
            <PostalAddress>
              <Street>800 W. Bryn Mawr Ave.</Street>
              <Street>AMNE / AMSE cXML Profile</Street>
              <City>Itasca</City>
              <State>IL</State>
              <PostalCode>60143</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="soldTo">
            <Name xml:lang="en-US">INTERACTIVE DATA          </Name>
            <PostalAddress>
              <Street>ACCOUNTS PAYABLE          </Street>
              <Street>32 CROSBY DR              </Street>
              <City>BEDFORD            </City>
              <State>MA</State>
              <PostalCode>01730    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="remitTo">
            <Name xml:lang="en-US">Wachovia Bank</Name>
            <PostalAddress>
              <Street>P.O. Box 101705</Street>
              <City>Atlanta</City>
              <State>GA</State>
              <PostalCode>303921705</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
          <IdReference identifier="069984151" domain="accountID"/>
        </InvoicePartner>
        <InvoicePartner>
          <Contact role="billTo" addressID="161   ">
            <Name xml:lang="en-US">INTERACTIVE DATA CORP     </Name>
            <PostalAddress>
              <Street>US BOXBOROUGH             </Street>
              <Street>60 CODMAN HILL RD         </Street>
              <City>BOXBOROUGH         </City>
              <State>MA</State>
              <PostalCode>01719    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoicePartner>
        <InvoiceDetailShipping>
          <Contact role="shipFrom">
            <Name xml:lang="en-US">OfficeMax Contract</Name>
            <PostalAddress>
              <Street>125 Industrial Way</Street>
              <City>Portland</City>
              <State>ME</State>
              <PostalCode>04104</PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
          <Contact role="shipTo">
            <Name xml:lang="en-US">INTERACTIVE DATA CORP     </Name>
            <PostalAddress>
              <Street>US BOXBOROUGH             </Street>
              <Street>60 CODMAN HILL RD         </Street>
              <City>BOXBOROUGH         </City>
              <State>MA</State>
              <PostalCode>01719    </PostalCode>
              <Country isoCountryCode="US">United States</Country>
            </PostalAddress>
          </Contact>
        </InvoiceDetailShipping>
        <InvoiceDetailPaymentTerm payInNumberOfDays="35" percentageRate="0"/>
        <Extrinsic name="SHIPTOPARTNRIDX">161   </Extrinsic>
      </InvoiceDetailRequestHeader>
      <InvoiceDetailOrder>
        <InvoiceDetailOrderInfo>
          <OrderReference orderID="1035023685">
            <DocumentReference payloadID="Office Max.2503690.35088442.TEST.1035023685:0:103"/>
          </OrderReference>
        </InvoiceDetailOrderInfo>
        <InvoiceDetailItem invoiceLineNumber="401" quantity="3">
          <UnitOfMeasure>EA</UnitOfMeasure>
          <UnitPrice>
            <Money currency="USD">148.00</Money>
          </UnitPrice>
          <InvoiceDetailItemReference lineNumber="4">
            <ItemID>
              <SupplierPartID>Q7VG730M</SupplierPartID>
            </ItemID>
            <Description xml:lang="en-US">LCD MONITOR 17&quot;</Description>
            <ManufacturerPartID>VG730M</ManufacturerPartID>
            <ManufacturerName xml:lang="en-US">VIEWSO</ManufacturerName>
          </InvoiceDetailItemReference>
          <SubtotalAmount>
            <Money currency="USD">00444.00</Money>
          </SubtotalAmount>
          <GrossAmount>
            <Money currency="USD">444</Money>
          </GrossAmount>
          <NetAmount>
            <Money currency="USD">00444.00</Money>
          </NetAmount>
        </InvoiceDetailItem>
      </InvoiceDetailOrder>
      <InvoiceDetailSummary>
        <SubtotalAmount>
          <Money currency="USD">444.00</Money>
        </SubtotalAmount>
        <Tax>
          <Money currency="USD">27.75</Money>
          <Description xml:lang="en-US"/>
          <TaxDetail purpose="tax" category="sales" percentageRate="6.25">
            <TaxableAmount>
              <Money currency="USD">444.00</Money>
            </TaxableAmount>
            <TaxAmount>
              <Money currency="USD">27.75</Money>
            </TaxAmount>
            <TaxLocation xml:lang="en-US">usa</TaxLocation>
          </TaxDetail>
        </Tax>
        <GrossAmount>
          <Money currency="USD">471.75</Money>
        </GrossAmount>
        <NetAmount>
          <Money currency="USD">471.75</Money>
        </NetAmount>
        <DueAmount>
          <Money currency="USD">471.75</Money>
        </DueAmount>
      </InvoiceDetailSummary>
    </InvoiceDetailRequest>
  </Request>
</cXML>
 ------------------------------------------------------------
Issues occured:

1. when using this DBMS_LOB.LOADFROMFILE(DEST_LOB => L_CLOB, SRC_LOB => L_BFILE, AMOUNT => DBMS_LOB.GETLENGTH(L_BFILE));
   the data in xml file is reading in unknown characterset   

   Resolved: by using DBMS_LOB.LOADCLOBFROMFILE

2. when using this dbms_xmlparser.parseclob(l_parser,l_clob) getting the below error
    sqlerrm :ORA-31020: The operation is not allowed, Reason: For security reasons, ftp and http access over XDB repository is not allowed on server side
    resolved : by using
        dbms_xmlparser.parseclob(l_parser, REGEXP_REPLACE(l_clob,'<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">',''));

3. could not able to read the file when file is in "/idc/dev/app/apps/apps_st/appl/zzcus/12.0.0/XMLDIR" and getting the below error.
        ORA-22288: file or LOB operation FILEOPEN failed
            No such file or directory
    temporarily Resolved : by placing the file in  /idc/dev/csf/tmp.

4. Able to read the node values but not attribute values
    resolved : by using
   for Ex: <Identity>178923231</Identity>  by using: dbms_xslprocessor.valueof(current_item,'From/Credential/Identity/text()',from_identity)
   for Ex: <Credential domain="OracleSN">  by using: dbms_xslprocessor.valueof(current_item,'From/Credential/@domain',from_credential);