Venky DevMastery

 Constraints ============ Constraints are conditions put on table columns. Oracle provides certain constraints that can be assigned to table columns. The total number of constraints are:  1.CHECK  2.UNIQUE  3.NOT NULL  4.PRIMARY KEY  5.FOREIGN KEY Check Constraint ---------------- A CHECK constraint can be used for validation,  for example, ensuring a hire date is greater than the company's establishment date. If a date lower than the establishment date is inserted, the constraint will throw an error. Unique Constraint ----------------- The UNIQUE constraint can be used to ensure a column, such as a cell number, has unique values for every person. A column with a UNIQUE constraint can allow records without a cell number (null values). Primary Key Constraint ---------------------- The PRIMARY KEY constraint is used to ensure values are both unique and not null. It achieves the goal of not allowing duplicate values and not allowing null values. If a PRIM...

  -- EMP Medical enrollment and their deduction rate SELECT      papf.person_number,     ler.name AS life_event_name,     pil.per_in_ler_stat_cd AS status, -- e.g., STRTD (Started), PROCD (Processed), CLSD (Closed)     pil.lf_evt_ocrd_dt AS life_event_date,     pil.ntfn_dt AS notification_date FROM      PER_ALL_PEOPLE_F papf,     BEN_PER_IN_LER pil,     BEN_LER_F ler WHERE      papf.person_id = pil.person_id     AND pil.ler_id = ler.ler_id     AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date     AND TRUNC(SYSDATE) BETWEEN ler.effective_start_date AND ler.effective_end_date     --AND papf.person_number = 'ADD_EMPLOYEE_NUMBER_HERE' ORDER BY      pil.lf_evt_ocrd_dt DESC PERSON_NUMBER LIFE_EVENT_NAME STATUS LIFE_EVENT_DATE NOTIFICATION_DATE 5585 Open STRTD 2026-01-01T00:00:00.000+00:00 2025-10-06T...

     Benefits module is often considered one of the most complex and robust modules because it sits at the intersection of HR policy, payroll deductions, and third-party vendor integrations. At a high level, the Benefits module manages the design, eligibility, and enrollment of employee benefits (health insurance, retirement plans, life insurance, wellness programs, etc.). Core functional concepts you must know: The Benefits Hierarchy (Plan Design): Oracle uses a strict building-block hierarchy to construct benefits. v Program: The highest level (e.g., "2026 US Executive Benefits Program"). v Plan Type: Categories within the program (e.g., Medical, Dental, Vision). v Option: The coverage level (e.g., Employee Only, Employee + Spouse, Family). Eligibility Profiles: These define who gets what . We attach criteria (e.g., Full-Time status, located in California, Job Grade > 3) to programs or plans to restrict enrollment t...

  Compensation  Click Here

  About Payroll Module =================== What is Payroll Module? What exactly we will do in it? What are different components that we have in payroll module? What are functional setups that comes in Payroll Module? If an organization want to  calculate payment or  salary amount to their employees,  make the payment,  creating salary accounts  and transfer the transations to General Ledger. Or they may be export the transactions to sub-ledger groups. For calculating     Payments     Accounts The payroll module will be executed Oracle Fusin Cloud Payroll Business Flow ========================================= > Define Consolidation Group > Define Organizational payment method(Check,Chash,Deposit..etc) > Personal payment Method > Third party payment method > Define Payroll Definitions > Retro, Proration pay methods > Element Classifications > Element > Element Eligi...

   --P2P Cycle SQL Script Functional Flow 1 ) Purchase Requisition Business user identifies need Creates PR in system Budget gets validated 2 ) PR Approval Manager reviews and approves Budget check happens Approval routing complete 3 ) Purchase Order Creation Approved PR converts to PO Supplier gets selected Terms & conditions set 4 ) PO Approval Amount-based routing Multi-level approval workflow Final authorization received 5 ) Goods/Service Receipt Warehouse receives items Quantity verified Quality inspection done 6 ) Invoice Receipt Supplier sends invoice Manual or EDI entry Initial validation runs 7 ) 3-Way Match PO vs Receipt vs Invoice Quantity must match Price tolerance checked 8 ) Invoice Approval Holds resolved Final invoice approval Exceptions handled 9 ) Payment Processing Payment terms applied Payment run created Bank file generated 10 ) GL Accounting Technical Flow SELECT  -- 1. Requisition Data (PR)     prh.requisition_number,     p...