Lookup code: PQH_SS_TRANSACTION_STATUSselect 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
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;
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
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
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;
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:
FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’)
FND_FLEX.EVENT(‘KEY-EDIT’);
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;
custom table with attributes attribute_category VARCHAR2(150),
attribute1 VARCHAR2(240),
attribute2 VARCHAR2(240),
attribute3 VARCHAR2(240),
attribute4 VARCHAR2(240),
attribute5 VARCHAR2(240);
Register Table:
Declare
v_appl_short_name VARCHAR2 (40) := 'XXCUST';
v_tab_name VARCHAR2 (32) := 'XX_MO_LINES';
v_tab_type VARCHAR2 (50) := 'T';
v_next_extent NUMBER := 512;
v_pct_free NUMBER;
v_pct_used NUMBER;
BEGIN
-- — Unregister the custom table if it exists
ad_dd.delete_table (p_appl_short_name => 'XXCUST', p_tab_name => v_tab_name);
--— Register the custom tabl
FOR tab_details IN (SELECT table_name, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent
FROM dba_tables
WHERE table_name = v_tab_name)
LOOP
ad_dd.register_table (p_appl_short_name => v_appl_short_name,
p_tab_name => tab_details.table_name,
p_tab_type => v_tab_type,
p_next_extent => NVL (tab_details.next_extent, 512),
p_pct_free => NVL (tab_details.pct_free, 10),
p_pct_used => NVL (tab_details.pct_used, 70)
);
END LOOP;
--— Register the columns of custom table
FOR all_tab_cols IN (SELECT column_name, column_id, data_type, data_length, nullable
FROM all_tab_columns
WHERE table_name = v_tab_name)
LOOP
ad_dd.register_column (p_appl_short_name => v_appl_short_name,
p_tab_name => v_tab_name,
p_col_name => all_tab_cols.column_name,
p_col_seq => all_tab_cols.column_id,
p_col_type => all_tab_cols.data_type,
p_col_width => all_tab_cols.data_length,
p_nullable => all_tab_cols.nullable,
p_translate => 'N',
p_precision => NULL,
p_scale => NULL
);
END LOOP;
FOR all_keys IN (SELECT constraint_name, table_name, constraint_type
FROM all_constraints
WHERE constraint_type = 'P' AND table_name = v_tab_name)
LOOP
ad_dd.register_primary_key (p_appl_short_name => v_appl_short_name,
p_key_name => all_keys.constraint_name,
p_tab_name => all_keys.table_name,
p_description => 'Register primary key',
p_key_type => 'S',
p_audit_flag => 'N',
p_enabled_flag => 'Y'
);
FOR all_columns IN (SELECT column_name, POSITION
FROM dba_cons_columns
WHERE table_name = all_keys.table_name AND
constraint_name = all_keys.constraint_name)
LOOP
ad_dd.register_primary_key_column (p_appl_short_name => v_appl_short_name,
p_key_name => all_keys.constraint_name,
p_tab_name => all_keys.table_name,
p_col_name => all_columns.column_name,
p_col_sequence => all_columns.POSITION
);
END LOOP;
END LOOP;
COMMIT;
END;
Step 3: Register the DFF in Oracle Apps
Responsibility: Application Developer
Navigation: Flexfield > Descriptive > Register
Give Name, Title, Description and TableName all as same.
Structure Column : ATTRIBUTE_CATEGORY
Note:
Oracle apps provides the API named, FND_FLEX, for Descriptive flexfield events. You can to write all the block level triggers to have consistent normal behaviour of the descriptive flexfield.
WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-ITEM ‘);
PRE-QUERY: FND_FLEX.EVENT(‘PRE-QUERY’);
POST-QUERY: FND_FLEX.EVENT(‘POST-QUERY’);
WHEN-VALIDATE-ITEM: FND_FLEX.EVENT(‘WHEN-VALIDATE-RECORD’);
WHEN-NEW-ITEM-INSTANCE: FND_FLEX.EVENT(‘WHEN-NEW-ITEM-INSTANCE’);
PRE-INSERT: FND_FLEX.EVENT(‘PRE-INSERT’);
PRE-UPDATE: FND_FLEX.EVENT(‘PRE-UPDATE’);
