Return all the occurrences of a substring / Get the all particular node values from XML

EX : Identify all the values from below strings which starts with "MTC":

* MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14
* MTCIN13;MTCIN14;100IN14;111IN14;123IN14
* MOCS13;MTCIN13;MTCIN14
* MOCS13;M100IN14;111IN14;123IN14

  WITH t AS
  (SELECT 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' AS list FROM dual
  UNION ALL
  SELECT 'BOB13;MTCIN23;123IN14' FROM dual
  UNION ALL
  SELECT 'MTCIN11;123IN14;MTC123X' FROM dual
  ) ,
  t2 AS
  (SELECT list,
    row_number() over (partition BY list order by 1) AS pos
  FROM t,
    (SELECT rownum FROM dual CONNECT BY rownum <= 100
    )
  ) -- assumes never more than 100 entries in list
SELECT list,
  ltrim(sys_connect_by_path(mtcs,';'),';') AS mtcs
FROM
  (SELECT list,
    ltrim(REGEXP_SUBSTR(';'
    ||list,';[[:alnum:]]*',1,pos),';') AS MTCs,
    row_number() over (partition BY list order by 1) rn
  FROM t2
  WHERE ltrim(REGEXP_SUBSTR(';'
    ||list,';[[:alnum:]]*',1,pos),';') LIKE 'MTC%'
  )
WHERE connect_by_isleaf = 1
  CONNECT BY list       = prior list
AND RN                  = prior RN+1
  START WITH rn         = 1

LIST                                           MTCS
---------------------------------------------- ------------------------------
BOB13;MTCIN23;123IN14                          MTCIN23
MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14 MTCIN13;MTCIN14
MTCIN11;123IN14;MTC123X                        MTCIN11;MTC123X

========================================================

FOr the same example above , if we need each substring in diffrent lines. Then

WITH t AS
  (SELECT 'MOCS13;MTCIN13;MTCIN14;100IN14;111IN14;123IN14' AS list FROM dual
  )
SELECT ltrim(REGEXP_SUBSTR(';'
  ||list,';[[:alnum:]]*',1,rn),';') AS MTCs
FROM t,
  (SELECT rownum rn
  FROM dual
    CONNECT BY rownum <=
    (SELECT LENGTH(regexp_replace(list,'[^;]'))+1 FROM t
    )
  )
WHERE ltrim(REGEXP_SUBSTR(';'
  ||list,';[[:alnum:]]*',1,rn),';') LIKE 'MTC%'

MTCS
-----------------------------------------------
MTCIN13
MTCIN14

 
/**************************************************************************
************************************************************************/

EX 2: Identify all distinct <Partnumbers> from below XML data

<vmsg action="PartsListResp"><mwid>HFERRIS</mwid><Model>70564</Model><PartList><Entry><PartNumber>70424-001</PartNumber><PartDesc>CABLE ASSEMBLY,POWER-ON INDICATOR</PartDesc><PartType>E</PartType><PartPrice>9.36</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>FAB-07529</PartNumber><PartDesc>SHIM, SENSOR TILT</PartDesc><PartType>E</PartType><PartPrice>96</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70437-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL ROBOT</PartDesc><PartType>E</PartType><PartPrice>52.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70435-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL CPS 1-4,IS</PartDesc><PartType>E</PartType><PartPrice>89.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70976-001</PartNumber><PartDesc>CABLE ASSY, Y-STAGE GND, IEP</PartDesc><PartType>E</PartType><PartPrice>5.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51274-001</PartNumber><PartDesc>ENCODER INTERFACE, PC BOARD</PartDesc><PartType>E</PartType><PartPrice>432</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>50227-008</PartNumber><PartDesc>CABLE CLAMP, FIXED DIA. HEAVY DUTY APP. #10SCREW</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>71006-001S</PartNumber><PartDesc>IMAGER MOTION CONTROL BOARD STACK SERVICE VERS</PartDesc><PartType>E</PartType><PartPrice>3968.7</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>71001-001</PartNumber><PartDesc>CASSETTE INTERFACE PLATE ASSEMBLY, IEP</PartDesc><PartType>E</PartType><PartPrice>2033.86</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51890-001</PartNumber><PartDesc>FAN MOUNT, VIBRATION</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03068-001</PartNumber><PartDesc>RETAINER,KEY SLOT</PartDesc><PartType>E</PartType><PartPrice>25.4</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51092-004</PartNumber><PartDesc>SCREW,SHBH,BLK, #4-40 .250 L</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>74158-001-RFB</PartNumber><PartDesc>TS PART ASSY, ROBOT ASSY</PartDesc><PartType>E</PartType><PartPrice>6701.68</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03305-001</PartNumber><PartDesc>PLATE,INTERFACE</PartDesc><PartType>E</PartType><PartPrice>646</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70443-001</PartNumber><PartDesc>CABLE ASSY,CASSETTE PRES SNSR #amp; LED IND,IS</PartDesc><PartType>E</PartType><PartPrice>554.04</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51761-002</PartNumber><PartDesc>SWITCHBOX, MANUAL, 2 TO 1</PartDesc><PartType>E</PartType><PartPrice>60</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51161-001</PartNumber><PartDesc>LENS,CONDENSER</PartDesc><PartType>E</PartType><PartPrice>775.8</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>71113-001</PartNumber><PartDesc>IEP CONDENSER ASSY</PartDesc><PartType>E</PartType><PartPrice>3750</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51344-002</PartNumber><PartDesc>ACCELEROMETER, ICP, MINI LOW NOISE</PartDesc><PartType>E</PartType><PartPrice>657</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51627-001</PartNumber><PartDesc>TISSUE, OPTICAL LENS CLEANER</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>03301-001</PartNumber><PartDesc>GUARD,CABLE GUIDE,SHORT</PartDesc><PartType>E</PartType><PartPrice>28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>04127-001</PartNumber><PartDesc>GUARD, CABLE GUIDE, LONG</PartDesc><PartType>E</PartType><PartPrice>30.8</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51067-010</PartNumber><PartDesc>SCREW,SHFH,SS,M3 X 10</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>RM-70524-001</PartNumber><PartDesc>REFURBISHED PCA,LED MODULE HOLDER</PartDesc><PartType>E</PartType><PartPrice>628</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03303-001</PartNumber><PartDesc>SHIELD,ROBOT</PartDesc><PartType>E</PartType><PartPrice>114.2</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03304-001</PartNumber><PartDesc>PANEL, LOWER VANITY</PartDesc><PartType>E</PartType><PartPrice>54.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51390-004</PartNumber><PartDesc>SCREW,SHBH,SS,BLK PASS,#6-32</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>70426-001</PartNumber><PartDesc>CABLE ASSY,SIS FAN/FILTER PRES SW,IS</PartDesc><PartType>E</PartType><PartPrice>149.08</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>ASY-03568</PartNumber><PartDesc>BOARD,LIGHT CONTROL IM FOR REPLACEMENT LED</PartDesc><PartType>E</PartType><PartPrice>232</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>ASY-03569</PartNumber><PartDesc>PCA,LED MODULE HOLDER (REPLACEMENT LED)</PartDesc><PartType>E</PartType><PartPrice>80</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70549-001</PartNumber><PartDesc>CABLE ASSY, DELTA TAU PS EXTENSION</PartDesc><PartType>E</PartType><PartPrice>15.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70530-001</PartNumber><PartDesc>CABLE ASSY, DELTA TAU INTERFACE RIBBON</PartDesc><PartType>E</PartType><PartPrice>22.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>PWR-00142</PartNumber><PartDesc>UPS BATTERY PACK, 24V, 1500VA, RS/XS SERIES</PartDesc><PartType>E</PartType><PartPrice>381</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03020-001</PartNumber><PartDesc>EFFECTOR, END, ROBOT</PartDesc><PartType>E</PartType><PartPrice>368</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>PWR-00140</PartNumber><PartDesc>UPS, 1500VA, 120VAC 50/60HZ, RS SERIES</PartDesc><PartType>E</PartType><PartPrice>588</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>PWR-00141</PartNumber><PartDesc>UPS, 1500VA, 230VAC 50/60HZ, RS SERIES</PartDesc><PartType>E</PartType><PartPrice>666</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>ASY-04635</PartNumber><PartDesc>KIT, SENSOR TILT RETROFIT</PartDesc><PartType>E</PartType><PartPrice>96</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70932-001</PartNumber><PartDesc>CABLE ASSY, SHEMP DC POWER, IEP,</PartDesc><PartType>E</PartType><PartPrice>26.28</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51697-006</PartNumber><PartDesc>CABLE, CAT5E ETHERNET, 10FT</PartDesc><PartType>E</PartType><PartPrice>45</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70440-001</PartNumber><PartDesc>CABLE ASSY,OCR CAMERA,IS</PartDesc><PartType>E</PartType><PartPrice>55.36</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70937-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL OCR CAMERA, IEP</PartDesc><PartType>E</PartType><PartPrice>59.2</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51812-001</PartNumber><PartDesc>SERIAL ADAPTOR, DB9(F) RJ-11 (F)</PartDesc><PartType>E</PartType><PartPrice>54.92</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>50612-001</PartNumber><PartDesc>MODULE,POWER INPUT,SH, DPST,10A</PartDesc><PartType>E</PartType><PartPrice>55.68</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51827-001</PartNumber><PartDesc>CABEL, PANEL MOUNT, FLATWIRE</PartDesc><PartType>E</PartType><PartPrice>47</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>04878-001</PartNumber><PartDesc>HUB DOOR PIVOT, THRU BOLT</PartDesc><PartType>E</PartType><PartPrice>43.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70550-001</PartNumber><PartDesc>ACCELEROMETER INTERFACE MODULE, IS ASSY.LEVEL 1</PartDesc><PartType>E</PartType><PartPrice>278.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70546-001</PartNumber><PartDesc>CABLE ASSY, TRIGGER LINES, IS</PartDesc><PartType>E</PartType><PartPrice>150.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>74158-001</PartNumber><PartDesc>TS PART ASSEMBLY, ROBOT ASSEMBLY</PartDesc><PartType>E</PartType><PartPrice>7090.78</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70659-001</PartNumber><PartDesc>PROGRAM ASSEMBLY,BOOT ROM,IS SHEMP</PartDesc><PartType>E</PartType><PartPrice>19</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>05381-001</PartNumber><PartDesc>DOOR CENTER ACESS</PartDesc><PartType>E</PartType><PartPrice>480</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>74109-001</PartNumber><PartDesc>TS ASSEMBLY,MOTOR,RADIAL AXIS ASSEMBLY</PartDesc><PartType>E</PartType><PartPrice>96.95</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70515-001</PartNumber><PartDesc>CABLE ASSY,SINGLE PHASE AMP,COMMUNICATION</PartDesc><PartType>E</PartType><PartPrice>57.72</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70942-001</PartNumber><PartDesc>CABLE ASSY,ELEC ENCL SHEMP J5 SENSOR INP.IEP</PartDesc><PartType>E</PartType><PartPrice>107.6</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>51140-006</PartNumber><PartDesc>SCREW, PHPH, SELF-TAP, #6-19, .375 #quot; LG</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry><Entry><PartNumber>CMP-01011</PartNumber><PartDesc>SCANNER, OCR, JDK-1472, TP-3000</PartDesc><PartType>E</PartType><PartPrice>1336</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>CMP-00979</PartNumber><PartDesc>SCANNER, OCR, JDK-1471, IMAGER</PartDesc><PartType>E</PartType><PartPrice>1348</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>04175-001</PartNumber><PartDesc>ENCLOSURE, ELECTRICAL TOP</PartDesc><PartType>E</PartType><PartPrice>57.8</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>03538-001S</PartNumber><PartDesc>SCANNER OCR 4710 SERVICE VERSION</PartDesc><PartType>E</PartType><PartPrice>1558.08</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>70525-001</PartNumber><PartDesc>CABLE ASSY, ELEC ENCL SHMP RS-232</PartDesc><PartType>E</PartType><PartPrice>46.32</PartPrice><PartReturn>Y</PartReturn><Reparable>Slow Return</Reparable></Entry><Entry><PartNumber>50868-030</PartNumber><PartDesc>WASHER, FLAT, METRIC, SS M3</PartDesc><PartType>E</PartType><PartPrice>1</PartPrice><PartReturn>N</PartReturn><Reparable>No Return</Reparable></Entry></PartList><MsgTotal>3</MsgTotal><MsgNum>3</MsgNum></vmsg>


In my example XML data is coming from table. we can hardcode the string or we can change the highlighted select query as per your requirement.

WITH T AS
  (SELECT msg_contents AS LIST
  FROM HOLX_VETTRO_OB_QUEUE
  WHERE 1             =1
  AND MSG_TYPE        ='PartsListResponse'
  AND msg_sequence_id ='2146981'
  )
select LTRIM(REGEXP_SUBSTR('<PartNumber>'
  ||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') AS Part_Number
FROM T,
  (SELECT rownum RN
  FROM DUAL
    CONNECT BY rownum <=
    (SELECT LENGTH(REGEXP_REPLACE(list,'[^<PartNumber>]'))+1 FROM T
    )
  )
WHERE LTRIM(REGEXP_SUBSTR('<PartNumber>'
  ||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') LIKE '%';



Altered select quesry and I ran this but Iam getting an error (single row subquery returns more than one row)

WITH T AS
  (SELECT msg_contents AS LIST
  FROM HOLX_VETTRO_OB_QUEUE
  WHERE 1                 =1
  AND MSG_TYPE            ='PartsListResponse'
  AND mwid                ='PKELLIHER'
  AND TRUNC(creation_date)=TRUNC(sysdate)
  AND msg_contents LIKE '%Model%70031%'
  )
select LTRIM(REGEXP_SUBSTR('<PartNumber>'
  ||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') AS PART_NUMBER
FROM T,
  (SELECT rownum RN
  FROM DUAL
    CONNECT BY rownum <=
    (SELECT LENGTH(REGEXP_REPLACE(list,'[^<PartNumber>]'))+1 FROM T
    )
  )
WHERE LTRIM(REGEXP_SUBSTR('<PartNumber>'
  ||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') LIKE '%';

Just replaced highlated one with 100--. This query will work for at most 100 records , Increase the number as per your requirement.



WITH T AS
  (SELECT msg_contents AS LIST
  FROM HOLX_VETTRO_OB_QUEUE
  WHERE 1                 =1
  AND MSG_TYPE            ='PartsListResponse'
  AND mwid                ='PKELLIHER'
  AND TRUNC(creation_date)=TRUNC(sysdate)
  AND msg_contents LIKE '%Model%70031%'
  )
SELECT LTRIM(REGEXP_SUBSTR('<PartNumber>'
  ||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') AS PART_NUMBER
FROM T,
  (SELECT rownum RN
  FROM DUAL
    CONNECT BY rownum <= 100 --(select length(REGEXP_REPLACE(list,'[^<PartNumber>]'))+1 from T)
  )
WHERE LTRIM(REGEXP_SUBSTR('<PartNumber>'
  ||list,'<PartNumber>[a-zA-Z0-9-]*',1,RN),'<PartNumber>') LIKE '%';


/*********************************************************************/ /**********************************************************************/
/**********************************************************************/
/**********************************************************************/


The importnat thing to understand from the above scripts is
what sort of characters available in Part_number field

'<PartNumber>[a-zA-Z0-9-]*'   

This implies part number will be having all numbers and small letters and capital letters and symbol '-'

Oracle HRMS Implementation Hints

HRMS Common Setup-

-Enable Functional Currency in GL Module

-Run "International HRMS Setup"

-Configure Business Group KFF :

          - Job, Grade, Position, Competence, Cost Allocation

-Define Business Group and enter its Other information

-Classify it as an "HR Organization" and enter its Other information

-Define a new Responsibility which can access all functions / Requests

-Configure the Following Profile Options :

          - HR:Business GroupResposibility Level

          - HR:Security ProfileResposibility Level

          - HR:User TypeResposibility Level 

      - DateTrack ReminderSite Level

-Define Lookups

-Define Value Sets

HR Setup-

HR-Work Structure Setup-

-Define HR Organization and enter its Other information

-Define Organizations Hierachy

-Define Jobs Groups

-Define Jobs

-Define Positions

-Define Positions Hierachy

-Define Grades

-Define Budget Calender

-Define Budget

HR-Leave Management-

-Define Elements Links

-Define Absence Types

-Develop / Enhrit Accrual Plan Formulas :

           - Accrual Formulas

           - Carryover Fomrulas

           - Ineligibility Accrual Formulas

-Define Accrual Plan

-Change the Links Defined by the System for the Accrual Plan to "Standard" 

HR-Employee Information-

-Configure Personal Information KeyFlexfield

-Enable Such information using "Special Information Types"

 under "Other Defination" Menu

HR-Recruitment Setup-

-Define Recruitment Lookups

-Might requires extra setup shared with

 Career Management and Self-Service

--Matching Using "Skills" Implementation--

-Define as many structure for "Personal Analysis Flex"

-Enable Structures on different HR Forms using "Special Information Types" under "Other Def. Menu"

-Enter Data on Job, Positions & Person Form

-Run "Job and Position Skills Matching Report" Request

--Matching Using "Competencies" Implementation "Suitaility Matching in Self-Service"--

-Define "Competencies KFF" Structure which will be used in the Business Group Def.

-Define Rating Scale

-Define Competence

-Define Competence Types

-Define Competence Requirement

-Enter "Competence Profile" for the person (Employee/Applicant)

--Setup Appraisal--

-Define "Competencies KFF" Structure which will be used in the Business Group Def.

-Define Proficiency Rating Scale

-Define Competence

-Define Competence Types

-Define Performance Rating Scale

-Define Weighting Rating Scale (Optional)

-Define Appraisal Template

Payroll Setup-

-Define Payment Type

    SWITCH TO RESPONSIBILITY : HR LOCALIZATION SEED DATA ENTRY

    OPEN THE FUNCTION : DEFAULTOWNING PARAMETER

        SELECT THE COUNTRY FROM THE NAME LOV

        IN THE OWNING PRODUCT LIST ENTER HUMAN RESOURCES AND PAYROLL IN TWO SEPARATE LINES

        SAVE YOUR WORK

    OPEN THE FUNCTION : PAYMENT TYPES

        ENTER THE PAYMENT TYPE NAME , CATEGORY, CURRENCY AND COUNTRY

-Define Org. Payment Methods

-Define Payroll

-Define Monetry Units

-Develop "Element Skip" & "Input Validation" Formulas

-Define Elements

-Define Elements Links

-Define Grade Rates

-Define Salary Basis (doesn''t means that employee will get the amount

 in the grade auto.)

-Register "User Defined Functions"

-Define Global Values

-Define Balances

-Define Table Structures

-Run "Create DFF Database Items" request

-Run "Create KFF Database Items" request

-Develop "Oracle Payroll" Formulas

-Define Formula Results

Security Setup--

-Define Security Profiles

-Define New Functions (If Any) :

           - Define Custom Form

           - Define Taskflow Node

           - Define Taskflow

           - Define Function

 

-Define Resposibilities

-Configure the following profiles for the resposibilities

          - HR:Security Profile

          - HR:User Type 

-Transactions Sequence for the Above Scope--

-Recurit to Hire--

-Entering Budget

-Raise Vacancy Requision

-Create Recruitment Activity

-Enter Applicant Details (applicant Quick Entry)

-Complete Applicant Information (FastPath / Enter & Maintain)

-Record Interviews Along with Changing Application Status

-Hire Applicant

-Maintain Employment--

-Maintain Employee Master Data (Gender, Address, Special Info..etc)

-Maintain Assignment information :

           - Org, Job, Positions, Grade, Payroll, Salary Basis

-Maintain Assignment Budget Values

-Maintain Personal Payment Method

-Maintain Costing

-Maintain Salary Administration

-Maintain Element Entries using :

             - Batch Element entry

             - Manual element Entry

-Payroll Processing--

-QuickPay

-Payroll Run

-View Assignment Results / Payroll Process Results

-Rollback Run then Payroll Run / Mark for retry then Retry

-Costing

-Transfer to GL

-Prepayment

-Cash / Cheque Writer / BACS

--T&L Setup

Define Compensation and Benefit Information/  Setup Elements and its Calculations as provided in

the Payroll Section

Define Element Sets

Define Time & Labor Information/ Define Element Time Information   

Define Time & Labor Information/ Define Holiday Calendar   

Holiday Days   

Define Time & Labor Information/ Define Shifts 

Define Time & Labor Information/ Define Work Plan  

Define Time & Labor Information/ Rotation Plan 

Define Time & Labor Information/ Earning Group 

Define Time & Labor Information/ Define Premium Eligibility Policy 

Define Time & Labor Information/ Define Premium Interaction Policy 

Define Time & Labor Information/ Define Shift Differential Policy  

Define Time & Labor Information/ Earning Policy

Daily/Weekly Base and Overtime Rules   

--T&L Transactions

-Assignment Time Information

-Create Batch Element Entry (BEE) Header Only

-Enter Time Card Information

-Submit "Validate for BEE" Request

-Submit "Transfer for BEE" Request

-Validate the Batch

-transfer the Batch to Create Element Entries for the Employee

-Process Payroll Processes

--TL & Absence Integration Workaround--

-Define Time Elements Information

-While Entering the Time Card Data Enter the "Hour Type" with the Equavilant time Element

 (e.g. Absence With Accrual Deduction /Absence with Payroll Deduction)

-For "Absence With Accrual Deduction", Such Element should feed the "Net Calculation Rules" in the

 "Accrual Plan" form, So any related element will be deducted from the Accrual Balance

-For "Absence With Payroll Deduction", Such Element will be used in Different Fastformula

 to deduct the Equavilant Amount

-Start Self-Service"--

-Define User for Each Employee

    -Assign Person / email to each user

    -Assign Manager Self-Service / Employee Self-Service Resposibilities

Oracle Tools-

-iSetup

-Implementation Wizard (Requires iSetup Knowledge)

-Web ADI (Application Desktop Inetgrators)

-XML Publisher (For Data / Reports) Publishing

-QuickPaint Reports (For Easy Generate simple HRMS Reports)