Relation between (IRC_SUBMISSIONS , IRC_OFFERS) and PER_ALL_ASSIGNMENTS_M — Oracle Analytics

Oracle Transactional Business Intelligence Idea Lab

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Relation between (IRC_SUBMISSIONS , IRC_OFFERS) and PER_ALL_ASSIGNMENTS_M

891
Views
3
Comments

Organization Name

MBC

Description

Hi ,

After complete the recruitment process new row in PER_ALL_ASSIGNMENTS_M with Assignment type = 'E' no link with IRC_OFFERS (as per oracle support from SR).

we cannot count how many days between accepted the offer and the hire for internal candidates (hired late) because no relation between (IRC_SUBMISSIONS , IRC_OFFERS) and PER_ALL_ASSIGNMENTS_M

 

Thanks,

Rafik Beshar

Use Case and Business Need

we cannot count how many days between accepted the offer and the hire for internal candidates (hired late).

More details

We need this information to build report to count how many days for each cycle to close from select candidate and hire.

Original Idea Number: 3775bcd56a

Tagged:
6
6 votes

Submitted · Last Updated

Comments

  • Sergio Zendejas
    Sergio Zendejas Rank 1 - Community Starter

    Hi Rafik,

    Try checking out this table (I think it was introduced in one of the later releases, I am on 21A), PER_EMPL_OFFER_HR_TRACK. This table stores the value of the offer assignment id along with the action's performed on this table and the resultant assignment id's generated due to the action. Also, on the IRC_OFFERS there is a ASSIGNMENT_OFFER_ID which can be joined with the per_all_assignments_m table to capture the assignment details. Depending on how your data is being stored and processed, you can potentially get the subsequent assignment (pending worker) by using the DISTINCT ROW_NUMBER () OVER(Partition By) function or RANK. Once you have all the joined data you will be able to see the full lifecycle of an employees service from Candidate > Offer > Pending > Employee. I haven't looked too deep into the PER_EMPL_OFFER_HR_TRACK table but it does pull up results and captures the data you are looking for.

    Hope this helps in some way, not really a full blown solution but some sort of workaround. If you have SQL knowledge and know the tables well you should be able to get what you need with these few tables. I've built out something here on my end with these tables based on business need, so I'm sure you'll be able to get something out of it.

    p.s - If you've implemented ORC and are utilizing the module, the PER_ALL_ASSIGNMENTS_M table and PERIOD_OF_SERVICE_TABLE will have new types starting with "O" for Offer.

    Thanks!

    Sergio

     

  • Ketan-
    Ketan- Rank 1 - Community Starter

    Hi I am having duplicate OFFER_HR_TRACK_ID record in PER_EMPL_OFFER_HR_TRACK table for same OFFER_ASG_ID..

    what could be the issue.

  • D Swathi
    D Swathi Rank 1 - Community Starter

    Hi Folks,

    Regarding Above PER_EMPL_OFFER_HR_TRACK Still gives Duplicate Records?