Query AME Setup from backend

 /* Formatted on 1/27/2025 9:15:59 AM (QP5 v5.336) */

  SELECT ar.rule_id,

         art.description

             rule_name,

         ar.start_date,

         ar.end_date,

         ame_utility_pkg.get_condition_description (acu.condition_id)

             condition,

         aty.name

             action_type,

         ame_utility_pkg.get_action_description (ameactionusageeo.action_id)

             AS approver_group

    FROM ame_rules           ar,

         ame_rules_tl        art,

         ame_condition_usages acu,

         ame_action_usages   ameactionusageeo,

         ame_actions_vl      act,

         ame_action_types_vl aty,

         (SELECT *

            FROM ame_action_type_usages

           WHERE     rule_type <> 2

                 AND SYSDATE BETWEEN start_date

                                 AND NVL (end_date - (1 / 86400), SYSDATE)) atu

   WHERE     ar.rule_id = art.rule_id

         AND art.language = 'US'

         AND TRUNC (SYSDATE) BETWEEN ar.start_date

                                 AND NVL (

                                         ar.end_date,

                                         TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'))

--         AND UPPER (art.description) LIKE '%DOC%'

         AND ame_utility_pkg.get_condition_description (acu.condition_id) LIKE  '%HR_PERSONAL_INFO_JSP_PRC%'--'%HR_EIT_UPDATE_INFO_PRC%'

         AND acu.rule_id = ar.rule_id

         AND TRUNC (SYSDATE) BETWEEN acu.start_date

                                 AND NVL (

                                         acu.end_date,

                                         TO_DATE ('31-DEC-4712', 'DD-MON-YYYY'))

         AND (   (SYSDATE BETWEEN ameactionusageeo.start_date

                              AND NVL (ameactionusageeo.end_date - (1 / 86400),

                                       SYSDATE))

              OR (    SYSDATE < ameactionusageeo.start_date

                  AND ameactionusageeo.start_date <

                      NVL (ameactionusageeo.end_date,

                           ameactionusageeo.start_date + (1 / 86400))))

         AND SYSDATE BETWEEN act.start_date

                         AND NVL (act.end_date - (1 / 86400), SYSDATE)

         AND SYSDATE BETWEEN aty.start_date

                         AND NVL (aty.end_date - (1 / 86400), SYSDATE)

         AND aty.action_type_id = atu.action_type_id

         AND act.action_id = ameactionusageeo.action_id

         AND act.action_type_id = aty.action_type_id

         AND ameactionusageeo.rule_id = ar.rule_id

ORDER BY ar.rule_id

Query Workflow Transaction Status

Lookup code: PQH_SS_TRANSACTION_STATUS

select distinct lookup_code,meaning from fnd_lookup_values
where lookup_type='PQH_SS_TRANSACTION_STATUS'

           D - Deleted

C - Cancelled

E - Error

RI - Return for Correction

RIS - Return for Correction + Save For Later

S - Save for Later

W - Review page

Y - Pending Approval

YS - Pending Approval + Save for Later

Function to calculate number of absence days in the year per month and calculation (Maternity Leave)

 number of days of given month and calc for maternity leave. 

for 45 + 45 days of maternity leave , employee is eligible for 52.5 days full Paid and then no paid. 

The leave periods are as follows:

First period: 19th Aug 2024 to 2nd Oct 2024 (45 days)

Second period: 3rd Oct 2024 to 16th Nov 2024 (45 days)

August month 13 days full paid., 0 days unpaid

September month 30 days full paid , 0 days unpaid

October month 2 days full paid, 15 days half paid. i.e., total fullPaid 9.5 days ( 2 + 15/2), unpaid 21.5 days

November month 0 days paid. i.e., 30 days unpaid

FUNCTION GET_MATERNITY_UNPAID_DAYS_F (p_assignment_id   NUMBER,
                                      P_PROC_START      DATE)
    RETURN NUMBER
AS
    l_days_taken    NUMBER := 0;
    l_days_unpaid   NUMBER := 0;
    p_month         NUMBER := TO_NUMBER (TO_CHAR (P_PROC_START, 'mm'));
    p_year          NUMBER := TO_NUMBER (TO_CHAR (P_PROC_START, 'yyyy'));
BEGIN
   <<outer_loop>>
    FOR rec
        IN (  SELECT date_start, date_end
                FROM per_absence_attendances
               WHERE     person_id = (SELECT person_id
                                        FROM per_assignments_x
                                       WHERE assignment_id = p_assignment_id)
                     AND ABSENCE_ATTENDANCE_TYPE_ID = 68
                     AND date_start >=
                         TO_DATE ('01-01-' || TO_CHAR (p_year), 'dd-mm-yyyy')
                     AND date_start <
                         TO_DATE ('01-01-' || TO_CHAR (p_year + 1),
                                  'dd-mm-yyyy')
                     AND date_start <=
                         ADD_MONTHS (
                             TO_DATE (
                                    '01-'
                                 || TO_CHAR (p_month)
                                 || '-'
                                 || TO_CHAR (p_year),
                                 'dd-mm-yyyy'),
                             1) --ignore anything that starts after month in question
            ORDER BY date_start)
    LOOP
        FOR rec2 IN (    SELECT rec.date_start + ROWNUM - 1     AS calc_date
                           FROM DUAL
                     CONNECT BY LEVEL <= rec.date_end - rec.date_start + 1)
        LOOP
            l_days_taken := l_days_taken + 1;

            IF TRUNC (rec2.calc_date, 'MM') =
               ADD_MONTHS (
                   TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-yyyy'),
                   1)
            THEN
                EXIT outer_loop;          --gone past month in question, stop.
            ELSIF TRUNC (rec2.calc_date, 'MM') =
                  TO_DATE ('01-' || p_month || '-' || p_year, 'dd-mm-yyyy')
            THEN
                IF l_days_taken = 53
                THEN
                    --hit the end of the allocation here, so half day
                    l_days_unpaid := l_days_unpaid + 0.5;
                ELSIF l_days_taken >= 54
                THEN
                    --past the last half day, take a full day unpaid
                    l_days_unpaid := l_days_unpaid + 1;
                END IF;
            END IF;
        END LOOP;
    END LOOP;

    RETURN l_days_unpaid;
END; 

Oracle Apps R12 HRMS Payroll Security (Menu fnd_sessions will not available and LOV's will not work)

Add the below functions without prompt to your HR Menu

else fnd_sessions will not available and LOV's will not work

1. HRMS Create Document

2. Salary Administration:Approve

3.Navigator: Disable Multiform

4. Desktop Integration - Create Document

oracle ebs r12 19c database query running slow solution ex: cst_cg_cost_history_v view

 if query running very slow then add this /*+ optimizer_features_enable('12.1.0.2') */ 
ex: 
select /*+ optimizer_features_enable('12.1.0.2') */  * from dual;
SELECT /*+ optimizer_features_enable('12.1.0.2') */ ACTUAL_COST FROM cst_cg_cost_history_v cst
where cst.inventory_item_id = MMT.INVENTORY_ITEM_ID
AND TRUNC(cst.TRANSACTION_DATE) = TRUNC(MMT.TRANSACTION_DATE)
and cst.organization_id = mmt.organization_id

EBS R12 Custom Form to add search Query

 https://www.youtube.com/watch?v=hLP4fGmy4Bc (check from 9min)
https://erpschools.com/erps/forms/query_find-form-in-oracle-apps
1. open APPSTAND.fmb form.
2. from object_groups drag QUERY_FIND to your form module(name of your custom form) and drop with ctl key pressed,
  once asked for confirmation select sub-class.
3. new QUERY_FIND OBJECTs will be created with same name under DataBlocks, Canvases, Object Groups of your custom form.
4. QUERY_FIND data block
Button New
replace the code with your block ex: DEPT
app_find.new('DEPT');
5. Button Find
:parameter.g_query_find := 'TRUE';
app_find.find('DEPT');
:parameter.g_query_find := 'FALSE';
6. open the canvas QUERY_FIND
drag one text field and give name ex: EMP_NUMBER_Q
7. Drag Trigger named QUERY_FIND from Appstand.fmb (STANDARD_OBJECTS) from main triggers section
and drop it to under your block ex: DEPT
8. open the code of this trigger we just dropped, modify it
--APPS_STANDARD.EVENT('QUERY_FIND'); --comment this
app_find.query_find('DEPT_WIND', --your main block window
'QUERY_FIND',
'QUERY_FIND');
9. under DEPT block new trigger pre-query:
if :parameter.g_query_find = 'TRUE' then
copy(:QUERY_FIND.EMP_NUMBER_Q,'DEPT.EMPNO');
:parameter.g_query_find := 'TRUE';
end if;

EBS R12 KFF in Custom Form

 1. update form level trigger WHEN-NEW-FORM-INSTANCE

declare
cursor get_cao is        
select  to_char(id_flex_num)        
from    fnd_id_flex_structures        
where   id_flex_structure_code = 'XX COA'               
 and id_flex_code = 'GL#';     
 v_cao    varchar2(1000);            
 begin    
  open get_cao;    
 fetch get_cao into v_cao;    
 if get_cao%notfound then        
 v_cao := '101';
     end if;    
     close get_cao;       
app_standard.event('WHEN-NEW-FORM-INSTANCE');    
 fnd_key_flex.define(      
 BLOCK=>'SFF_MO_HEADER_V',       --SFF_MO_HEADER_V block SFF_MOVE_ORDER_CAN in canvas
 FIELD=>'DESTINATION_ACCOUNT_SEGMENTS',  --field for LOV should be not database   
  APPL_SHORT_NAME=>'SQLGL',     
   CODE=>'GL#',      
   NUM=>v_cao,      
   ID=>'DESTINATION_ACCOUNT_CCID',--to show/store the CCID      
   DESCRIPTION=>'',
   TITLE=>'Test Title',      
   VALIDATE=>'FULL',      
   QBE_IN=>'Y',      
   DERIVE_ALWAYS=>'Y',      
   updateable => '',      
   --VRULE=>'\nSUMMARY_FLAG\nI\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN',      
   --vrule => 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED \nE\nAPPL='||'"'||'SQLGL'||'"'||'; name=Parent Values are not allowed\nN'
   where_clause => 'summary_flag !=''Y''',      
   QUERY_SECURITY =>'Y');  
   end ;--initialize;     

2. Field Properties
DESTINATION_ACCOUNT_SEGMENTS,  --field for LOV should be not database   
Database Item: No
Query Only: No
List of Values: Enable List Lamp
Validate from List: No

3. field DESTINATION_ACCOUNT_CCID,--to show/store the CCID      

Triggers:

3.Invoke Flexfield functionality by calling FND_Flex.Event(event) from Block Level:

• PRE-QUERY --  FND_FLEX.EVENT('PRE-QUERY' );
• POST-QUERY -- FND_FLEX.EVENT('POST-QUERY');
• PRE-INSERT -- 
• PRE-UPDATE -- 
• WHEN-VALIDATE-RECORD  (item Level down)
• WHEN-NEW-ITEM-INSTANCE (item Level down)
• WHEN-VALIDATE-ITEM (item Level down)

3. Write an item level trigger WHEN-NEW-ITEM-INSTANCE on DESTINATION_ACCOUNT_SEGMENTS as

FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’)

4. Write an item level trigger KEY-EDIT on BTL_KFF as

FND_FLEX.EVENT(‘KEY-EDIT’);

5. Write an item level trigger WHEN-VALIDATE-ITEM on DESTINATION_ACCOUNT_SEGMENTS as

FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM’);

IF :XX_BLOCK.DESTINATION_ACCOUNT_CCID = -1 THEN
FND_MESSAGE.SET_STRING(‘You Have Selected An Undefined Code Combination !’);
FND_MESSAGE.SHOW;
RAISE FORM_TRIGGER_FAILURE;
END IF;