Oracle Apps KT

Friday, May 1, 2020

Uploading Lookups in Oracle Apps R12 (English Only)

custom interface table.

CREATE TABLE XXLOOKUP_CODES_AR
(LOOKUP_CODE VARCHAR2(30),
MEANING_EN VARCHAR2(240),
MEANING_AR VARCHAR2(240),
DESCRIPTION_EN VARCHAR2(240),
DESCRIPTION_AR VARCHAR2(240),
ATTRIBUTE1 VARCHAR2(240),
ATTRIBUTE2 VARCHAR2(240))

alter table xxlookup_codes_ar
add (lkp_err_msg varchar2(240))

update xxlookup_codes_ar
set lkp_process_flag_ar = 'N';

SELECT * FROM xxlookup_codes_ar

alter session set NLS_LANGUAGE ='AMERICAN';--'ARABIC' AMERICAN

DECLARE
V_ERROR VARCHAR2(4000);
CURSOR get_lookup_details
IS

SELECT ltype.application_id,
ltype.customization_level,
ltype.creation_date,
ltype.created_by,
ltype.last_update_date,
ltype.last_updated_by,
ltype.last_update_login,
tl.lookup_type,
tl.security_group_id,
tl.view_application_id,
tl.description,
tl.meaning
FROM fnd_lookup_types_tl tl, fnd_lookup_types ltype
WHERE ltype.lookup_type = 'FFZA_LICENSE_TYPE'
AND ltype.lookup_type = tl.lookup_type
AND language = 'US';

CURSOR get_country
IS

SELECT DISTINCT
lookup_code,
meaning_en,
meaning_ar,
description_en, description_ar
FROM xxlookup_codes_ar
WHERE lkp_process_flag_ar = 'N'
and lookup_code <> '58'
;

l_rowid VARCHAR2 (100) := 0;
BEGIN
FOR i IN get_lookup_details
LOOP
FOR j IN get_country
LOOP
l_rowid := NULL;
BEGIN
fnd_lookup_values_pkg.insert_row (
x_rowid => l_rowid,
x_lookup_type => i.lookup_type,
x_security_group_id => i.security_group_id,
x_view_application_id => i.view_application_id,
x_lookup_code => j.lookup_code,
x_tag => '',
x_attribute_category => NULL,
x_attribute1 => NULL,
x_attribute2 => NULL,
x_attribute3 => NULL,
x_attribute4 => NULL,
x_enabled_flag => 'Y',
x_start_date_active =>NULL,
x_end_date_active => NULL,
x_territory_code => NULL,
x_attribute5 => NULL,
x_attribute6 => NULL,
x_attribute7 => NULL,
x_attribute8 => NULL,
x_attribute9 => NULL,
x_attribute10 => NULL,
x_attribute11 => NULL,
x_attribute12 => NULL,
x_attribute13 => NULL,
x_attribute14 => NULL,
x_attribute15 => NULL,
x_meaning => j.meaning_en,
x_description => j.description_en,--j.B,
x_creation_date => SYSDATE,
x_created_by => i.created_by,
x_last_update_date => i.last_update_date,
x_last_updated_by => i.last_updated_by,
x_last_update_login => i.last_update_login);

COMMIT;

-- update XX_LOOKUP_VALUES set upload_status= 'DONE' WHERE LOOKUP_CODE=J.LOOKUP_CODE;
EXCEPTION
WHEN OTHERS
THEN
V_ERROR := (SQLERRM);
DBMS_OUTPUT.PUT_LINE(V_ERROR);
-- update XX_LOOKUP_VALUES set upload_status= 'Inner Exception: ' || V_ERROR WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;
END LOOP;
END LOOP;
--EXCEPTION
-- WHEN OTHERS
-- THEN
-- update XX_COUNTRY set upload_status='Main Exception: ' || V_ERROR WHERE LOOKUP_CODE=J.LOOKUP_CODE;
END;

Wednesday, February 19, 2020

Oracle Report using profile security Apps r12

To Initialize Apps in Oracle Report: BELOW 3 steps mandatory(3.1 to 3.3) and use the secured tables in query.
3.1. Before report trigger

srw.USER_EXIT ('FND SRWINIT');
fnd_global.apps_initialize(FND_PROFILE.VALUE('USER_ID'),FND_PROFILE.VALUE('RESP_ID'),FND_PROFILE.VALUE('RESP_APPL_ID'));
mo_global.init ('S');

3.2. After Report Trigger

SRW.USER_EXIT('FND SRWEXIT');

3.3 Add user parameter

P_CONC_REQUEST_ID datatype - Number, Width - 200

Tuesday, February 18, 2020

Checkbox in xml publisher report in Oracle Apps R12

Open your BI Publisher folder its 32 or 64 bit, check both if available.

C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config

C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config

Copy, paste and rename file xdo example.cfg as xdo.cfg in the same folder.

do below 2 modifications for xdo.cfg file .

1. Add/update property under <properties>

<property name="rtf-checkbox-glyph">Wingdings;254;168</property>

2. check the fonts path

by default it was c:/WINT… change it to ur pc correct ex: C:\Windows\Fonts\wingding.ttf

Enable Developer ribbon tab in Word.

Right click ribbon tab🡪 Customize 🡪 Enable the checkbox for Developer.

Insert checkbox from the Legacy Forms 🡪 Checkbox.

$C:\Users\Afzal\OneDrive\Pictures\Screenshots\2020-02-19.png$

Add Condition in the checkbox as per your requirement ex:☐

It will work only on pdf as per oracle document.

Run the xml report output in pdf and check.

If condition true it will be checked, else empty.

Deploying to Server:

Download file from https://github.com/IamDH4/ttf-wps-fonts/blob/master/wingding.ttf

Check in the Word template also having same font 🡪 optional.

Under Administration 🡪 configuration

Text: Wingdings;0254;0168

Monday, January 20, 2020

Oracle Apps R12 Get_Net_Accrual Technical Details

--------------------------------------------------------------------*/
DECLARE
P_Assignment_ID Number := 22432;
P_Plan_ID Number := 61;
P_Payroll_ID Number:= 61;
P_Business_Group_ID Number := 81;
P_Assignment_Action_ID Number := -1;
P_Calculation_Date Date := TO_DATE('31-DEC-2019');
P_Accrual_Start_Date Date;-- default null
P_Accrual_Latest_Balance Number;-- default null
P_Calling_Point Varchar2(20):= 'FRM';
P_Start_Date Date;
P_End_Date Date;
P_Accrual_End_Date Date;
P_Accrual Number;
P_Net_Entitlement Number;
g_package varchar2(50) := ' per_accrual_calc_functions.';

l_proc varchar2(72) := g_package||'Get_Net_Accrual';
l_absence number := 0; --changed for bug 6914353
l_accrual number;
l_other number := 0; --changed for bug 6914353
l_carryover number;
l_start_date date;
l_end_date date;
l_accrual_end_date date;
l_defined_balance_id number;

l_atd date; --added for bug 6418568

cursor c_get_balance is
select defined_balance_id
from pay_accrual_plans
where accrual_plan_id = p_plan_id;

--added for bug 6418568
cursor c_get_atd is
select nvl(pps.ACTUAL_TERMINATION_DATE,to_date('31/12/4712','dd/mm/yyyy'))
from per_periods_of_service pps, per_all_assignments_f paaf
where paaf.person_id = pps.person_id
and paaf.period_of_service_id = pps.period_of_service_id
and paaf.Assignment_ID = P_Assignment_ID
and P_Calculation_Date between paaf.effective_start_date and paaf.effective_end_date;

begin

open c_get_balance;
fetch c_get_balance into l_defined_balance_id;
close c_get_balance;

if p_calling_point = 'BP' and
l_defined_balance_id is not null and
p_assignment_action_id <> -1 then
--
/* Procedure called from batch process, so
get latest balance. */

p_net_entitlement := pay_balance_pkg.get_value(
p_defined_balance_id => l_defined_balance_id
,p_assignment_action_id => p_assignment_action_id
);
--
else
--

per_accrual_calc_functions.get_accrual(p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_calculation_date => p_calculation_date,
p_business_group_id => p_business_group_id,
p_payroll_id => p_payroll_id,
p_assignment_action_id => p_assignment_action_id,
p_accrual_start_date => p_accrual_start_date,
p_accrual_latest_balance => p_accrual_latest_balance,
p_start_date => l_start_date,
p_end_date => l_end_date,
p_accrual_end_date => l_accrual_end_date,
p_accrual => l_accrual);

--start changes for bug 6418568
open c_get_atd;
fetch c_get_atd into l_atd;
close c_get_atd;

if l_accrual_end_date is not null then
--
l_absence := per_accrual_calc_functions.get_absence(p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date);

l_other := per_accrual_calc_functions.get_other_net_contribution(
p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date
);
else
--
if l_atd >= P_Calculation_Date then
--
l_absence := per_accrual_calc_functions.get_absence(p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date);

l_other := per_accrual_calc_functions.get_other_net_contribution(
p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date
);
--
end if;
--
end if;
--end changes for bug 6418568

l_carryover :=0; -- 12880652
l_carryover := per_accrual_calc_functions.get_carry_over(
p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date);
-- 12880652

if P_Calculation_Date > l_atd and l_carryover <> 0 then
l_other := per_accrual_calc_functions.get_other_net_contribution(
p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date
);

l_absence := per_accrual_calc_functions.get_absence(p_assignment_id => p_assignment_id,
p_plan_id => p_plan_id,
p_start_date => l_start_date,
p_calculation_date => l_end_date); -- added new 12880652

end if;
-- 12880652

--
-- Set up values in the return parameters.
--
DBMS_OUTPUT.PUT_LINE( 'l_accrual->'||l_accrual||'->l_absence->'||l_absence||'--l_other->'|| l_other ||'-CARRYOVER-'|| l_carryover);
p_net_entitlement := l_accrual - l_absence + l_other + l_carryover;
p_accrual := l_accrual;
p_start_date := l_start_date;
p_end_date := l_end_date;
p_accrual_end_date := l_accrual_end_date;
--
end if;

--

--
end ;
--

Monday, December 23, 2019

Excel Trim remove spaces not working then use TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

Step1: Formula: =TRIM(SUBSTITUTE(C1,CHAR(160),CHAR(32)))

C1 is the value

Step2: Copy all the column, replace with value.

Step3: convert text to number.

Sunday, November 17, 2019

Oracle Apps R12 query for attachments from backend for sshr txns

1. get the item_key, for workflow administrator

select * from hr_api_transactions
where item_key = '217855'

2. get the transaction_id and other details from query 1,
the transaction_id can be in pk5_value or pk1_value for the below query 2.
if not then query based on created_by, creation_date from query 1.

select fd.*, fl.*--,fad.*--fd.*,
from fnd_attached_documents fad
,fnd_documents fd
,fnd_lobs fl
,fnd_document_datatypes fdd
,fnd_document_categories_tl fdct
where fad.document_id = fd.document_id
and fd.media_id = fl.file_id
and fd.datatype_id = fdd.datatype_id
and fd.category_id = fdct.category_id
and fdd.user_name = 'File'
--and fad.entity_name = 'XXASSET' -- replace with the entity_name you want to extract.
and fdct.language = 'US'
--and pk5_value = '217855'
and fad.creation_date like to_date('27-oct-2019')
and fad.created_by = 12345

and fd.creation_date like sysdate

Sunday, October 27, 2019

Solution for xml output of item number type with prefix 00, showing output in excel without prefix

<fo:bidi-override direction="ltr" unicode-bidi="bidi-override"> <?XX_XML_TAG?> </fo:bidi-override>

Search This Blog