API-Element Entry Creation
pay_element_entry_api.create_element_entry
(p_validate => l_validate_only,
p_business_group_id => l_bus_group_id,
p_entry_type => l_entry_type,
p_assignment_id => l_assignment_id,
p_input_value_id1 => l_input_value_id1,
p_input_value_id2 => l_input_value_id2,
p_input_value_id3 => l_input_value_id3,
p_input_value_id4 => l_input_value_id4,
p_input_value_id5 => l_input_value_id5,
p_input_value_id6 => l_input_value_id6,
-- p_entry_value1 => rec_elemnt.input_value1,
-- p_entry_value2 => to_char(to_date(rec_elemnt.input_value2,'MM/DD/YYYY'),'DD-Mon-YYYY'),
--rec_elemnt.input_value2,
p_entry_value3 => to_char(to_date(rec_elemnt.input_value3,'MM/DD/YYYY'),'DD-Mon-YYYY'),
p_entry_value4 => rec_elemnt.input_value4,
p_entry_value5 => rec_elemnt.input_value5,
p_entry_value6 => rec_elemnt.input_value6,
p_element_link_id => l_element_link_id,
-- p_personal_payment_method_id => r1.pay_method_id,
p_effective_date => TRUNC
(rec_elemnt.date_from
),
--TRUNC(l_a_effective_start_date),--to_date('04/01/2013','MM/DD/YYYY'),
p_element_entry_id => l_element_entry_id,
p_object_version_number => l_object_version_number,
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_create_warning => l_create_warning
);
Create The adjustment API-
pay_balance_adjustment_api.create_adjustment
(p_validate => FALSE,
p_effective_date => to_date('04/01/2013','MM/DD/YYYY'), -- change as per requirement earlier it was sysdate
--TRUNC (rec_elemnt.date_from),
p_assignment_id => l_assignment_id,
p_consolidation_set_id => l_consolidation_set_id,
p_element_link_id => l_element_link_id,
p_input_value_id1 => l_input_value_id1,
p_input_value_id2 => l_input_value_id2,
p_input_value_id3 => l_input_value_id3,
p_input_value_id4 => l_input_value_id11,
p_input_value_id5 => l_input_value_id22,
p_entry_value1 => 0,--nvl(rec_elemnt.bal_input_value1,0),--input_value1
p_entry_value2 => 0,--nvl(rec_elemnt.bal_input_value2,0),--input_value2
--p_entry_value3 => 0,--nvl(rec_elemnt.bal_input_value3,0),--input_value3
p_entry_value4 => rec_elemnt.bal_input_value1,
p_entry_value5 => rec_elemnt.bal_input_value2,--nvl(rec_elemnt.bal_input_value4,0),--input_value4
p_element_entry_id => l_element_entry_id1,
p_effective_start_date => l_effective_start_date1,
p_effective_end_date => l_effective_end_date1,
p_object_version_number => l_object_version_number1,
p_create_warning => l_create_warning1
);
Wednesday, 12 June 2013
Delete Element and Respective Adjustment Deletion.
***************Delete all the Adjustment Elements**************************************
Declare
Cursor c1 Is select * from pay_element_entries_f where element_type_id in(
select element_type_id from pay_element_types_f where element_name like 'Staff Health Insurance%' )
and entry_type='B' and assignment_id=62;
begin
For v_rec in c1 loop
pay_balance_adjustment_api.DELETE_ADJUSTMENT(
p_validate =>false,
p_effective_date=>'01-Apr-2013',
p_element_entry_id=>v_rec.element_entry_id);
dbms_output.put_line('Done');
end loop;
Exception
When Others then
dbms_output.put_line('Error='||SQLERRM);
end;
Delete Element Entry after delting the Adjustment
**************************************************************************
Declare
l_element_entry_id NUMBER ;
l_ovn NUMBER ;
l_effstart_date DATE ;
v_count_succ NUMBER := 0;
v_count_err NUMBER := 0;
P_EFFECTIVE_START_DATE DATE ;
P_EFFECTIVE_END_DATE DATE ;
P_DELETE_WARNING BOOLEAN ;
CURSOR C1
IS
SELECT ent.element_entry_id,ent.object_version_number,ent.effective_start_date
--INTO l_element_entry_id ,l_ovn,l_effstart_date
FROM pay_element_entries_f ENT, pay_element_types_f ELE
WhERE ELE.business_group_id=81
AND ELE.element_type_id=ENT.element_type_id
AND ELE.element_name='Staff Health Insurance'
--AND ENT.ASSIGNMENT_ID in (123,154) ------need to be commented
AND trunc(sysdate) between ele.effective_start_date AND ele.effective_end_date
and ent.entry_type='E' and ent.assignment_id=62;
BEGIN
DBMS_OUTPUT.PUT_LINE('l_effstart_date For element entry=1'||l_effstart_date);
FOR i IN C1
LOOP
BEGIN
pay_element_entry_api.DELETE_ELEMENT_ENTRY (P_VALIDATE => FALSE
,P_DATETRACK_DELETE_MODE => 'ZAP'
,P_EFFECTIVE_DATE => i.effective_start_date
,P_ELEMENT_ENTRY_ID => i.element_entry_id
,P_OBJECT_VERSION_NUMBER => i.object_version_number
,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
,P_DELETE_WARNING => P_DELETE_WARNING
);
v_count_succ := v_count_succ+1;
DBMS_OUTPUT.PUT_LINE('Successful Count For element entry'||v_count_succ);
DBMS_OUTPUT.PUT_LINE('Successful Count For element entry'||l_element_entry_id);
EXCEPTION
WHEN OTHERS
THEN
v_count_err := v_count_err +1;
DBMS_OUTPUT.PUT_LINE('l_effstart_date For element entry'||l_effstart_date);
DBMS_OUTPUT.PUT_LINE('Unsuccessful CountFor element entry'||v_count_err);
DBMS_OUTPUT.PUT_LINE('Error For element entry'||l_element_entry_id);
DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace||'Exception : '||SQLERRM);
END;
END LOOP;
COMMIT;
END;
Declare
Cursor c1 Is select * from pay_element_entries_f where element_type_id in(
select element_type_id from pay_element_types_f where element_name like 'Staff Health Insurance%' )
and entry_type='B' and assignment_id=62;
begin
For v_rec in c1 loop
pay_balance_adjustment_api.DELETE_ADJUSTMENT(
p_validate =>false,
p_effective_date=>'01-Apr-2013',
p_element_entry_id=>v_rec.element_entry_id);
dbms_output.put_line('Done');
end loop;
Exception
When Others then
dbms_output.put_line('Error='||SQLERRM);
end;
Delete Element Entry after delting the Adjustment
**************************************************************************
Declare
l_element_entry_id NUMBER ;
l_ovn NUMBER ;
l_effstart_date DATE ;
v_count_succ NUMBER := 0;
v_count_err NUMBER := 0;
P_EFFECTIVE_START_DATE DATE ;
P_EFFECTIVE_END_DATE DATE ;
P_DELETE_WARNING BOOLEAN ;
CURSOR C1
IS
SELECT ent.element_entry_id,ent.object_version_number,ent.effective_start_date
--INTO l_element_entry_id ,l_ovn,l_effstart_date
FROM pay_element_entries_f ENT, pay_element_types_f ELE
WhERE ELE.business_group_id=81
AND ELE.element_type_id=ENT.element_type_id
AND ELE.element_name='Staff Health Insurance'
--AND ENT.ASSIGNMENT_ID in (123,154) ------need to be commented
AND trunc(sysdate) between ele.effective_start_date AND ele.effective_end_date
and ent.entry_type='E' and ent.assignment_id=62;
BEGIN
DBMS_OUTPUT.PUT_LINE('l_effstart_date For element entry=1'||l_effstart_date);
FOR i IN C1
LOOP
BEGIN
pay_element_entry_api.DELETE_ELEMENT_ENTRY (P_VALIDATE => FALSE
,P_DATETRACK_DELETE_MODE => 'ZAP'
,P_EFFECTIVE_DATE => i.effective_start_date
,P_ELEMENT_ENTRY_ID => i.element_entry_id
,P_OBJECT_VERSION_NUMBER => i.object_version_number
,P_EFFECTIVE_START_DATE => P_EFFECTIVE_START_DATE
,P_EFFECTIVE_END_DATE => P_EFFECTIVE_END_DATE
,P_DELETE_WARNING => P_DELETE_WARNING
);
v_count_succ := v_count_succ+1;
DBMS_OUTPUT.PUT_LINE('Successful Count For element entry'||v_count_succ);
DBMS_OUTPUT.PUT_LINE('Successful Count For element entry'||l_element_entry_id);
EXCEPTION
WHEN OTHERS
THEN
v_count_err := v_count_err +1;
DBMS_OUTPUT.PUT_LINE('l_effstart_date For element entry'||l_effstart_date);
DBMS_OUTPUT.PUT_LINE('Unsuccessful CountFor element entry'||v_count_err);
DBMS_OUTPUT.PUT_LINE('Error For element entry'||l_element_entry_id);
DBMS_OUTPUT.PUT_LINE(dbms_utility.format_error_backtrace||'Exception : '||SQLERRM);
END;
END LOOP;
COMMIT;
END;
Sunday, 3 June 2012
Tables that stores the OAF data.
JDR_UTILS PL/SQL package supports the MDS repository and can be used to query and maintain the repository.
Now let’s see how, all that is mentioned above, happens.
MDS repository has 4 tables in all. Just 4 tables? Yes you heard it right it has only 4 tables and that’s it. These 4 tables can manage all the stuff that we have discussed till now.
These 4 tables are:
1. JDR_PATHS: Stores the path of the documents, OA Framework pages and their parent child relationship.
2. JDR_COMPONENTS: Stores components on documents and OA Framework pages.
3. JDR_ATTRIBUTES: Stores attributes of components on documents and OA Framework pages.
4. JDR_ATTRIBUTES_TRANS: Stores translated attribute values of document components or OA framework pages.
Now let’s see how, all that is mentioned above, happens.
MDS repository has 4 tables in all. Just 4 tables? Yes you heard it right it has only 4 tables and that’s it. These 4 tables can manage all the stuff that we have discussed till now.
These 4 tables are:
1. JDR_PATHS: Stores the path of the documents, OA Framework pages and their parent child relationship.
2. JDR_COMPONENTS: Stores components on documents and OA Framework pages.
3. JDR_ATTRIBUTES: Stores attributes of components on documents and OA Framework pages.
4. JDR_ATTRIBUTES_TRANS: Stores translated attribute values of document components or OA framework pages.
How MOAC impacts the way we work in TOAD
11i
To set the Org id in TOAD
1. Get the Org_id from HR_ORGANIZATION_UNITS
2. In toad execute the below code
begin
fnd_client_info.set_org_context(&org_id);
end;
To set the responsibility context in TOAD
1. Get the User id, Responsibility Id and Application id from Front end
Help>Diagnostic>Examine and select BLOCK as "$PROFILES$" and then get the respective IDS
2. Using the IDs execute the below code in TOAD
begin
FND_GLOBAL.APPS_INITIALIZE(user_id in number,resp_id in number,resp_appl_id in number);
end;
R12:
To set the Org id in TOAD
1. Get the Org_id from HR_ORGANIZATION_UNITS
2. In toad execute the below code
--Sets the 201 as single Org id
exec MO_GLOBAL.SET_POLICY_CONTEXT('S',201);
Pass a value "S" in case you want your current session to work against Single ORG_ID
Pass a value of "M" in case you want your current session to work against multiple ORG_ID's
To set the responsibility context and initiate MOAC in TOAD
1. Get the User id, Responsibility Id and Application id from Front end
Help>Diagnostic>Examine and select BLOCK as "$PROFILES$" and then get the respective IDS
2. Using the IDs execute the below code in TOAD
a. exec FND_GLOBAL.INITIALIZE
This will set your responsibility id, user_id etc
b. call MO_GLOBAL.INIT('AR')
This will read the MO profile option values for your responsibility/user, and will initialize the Multi
Org Access.
MOAC for table access
In 11i _ALL Tables where non Org specific and Org specific views were created on these tables.
But in R12 its different concept
a. For the table AP_INVOICES_ALL a synonym AP_INVOICES_ALL is created in APPS.
b. Also another synonym AP_INVOICES is created which refers to AP_INOICES_ALL.
c. A Row Level security is applied to AP_INVOICES, using package function
MO_GLOBAL.ORG_SECURITY.
This can be double-checked by running SQL select * from all_policies where
object_name='AP_INVOICES'
e. The effect of this policy is that,whenever you access AP_INVOICES, Oracle RLS will dynamically
append WHERE CLAUSE similar to below
SELECT * FROM AP_INVOICES
WHERE EXISTS (SELECT 1 FROM mo_glob_org_access_tmp oa WHERE oa.organization_id =
org_id)
How to Enable "About the Page" in Oracle Application?
Profile name : "FND: Diagonistics"
Better try setting the above profile from Functional Administrator.
How to Submit A XML Publisher Report from Script
Follow the steps to Submit A XML Publisher Report from Script
1. Select and assign the layout before submitting the request as below.
l_attached := fnd_request.add_layout ( template_appl_name => 'XXXX'
,template_code => 'MFG_LABEL_PRN'
,template_language => 'US'
,template_territory => 'en'
,output_format => 'PDF'
);
2. Call the Submit_Request API next.
ln_req_id := fnd_request.submit_request (
'mfg', 'MFG_LABEL_PRN ', 'MFG Label', TO_CHAR(SYSDATE), TRUE);
1. Select and assign the layout before submitting the request as below.
l_attached := fnd_request.add_layout ( template_appl_name => 'XXXX'
,template_code => 'MFG_LABEL_PRN'
,template_language => 'US'
,template_territory => 'en'
,output_format => 'PDF'
);
2. Call the Submit_Request API next.
ln_req_id := fnd_request.submit_request (
'mfg', 'MFG_LABEL_PRN ', 'MFG Label', TO_CHAR(SYSDATE), TRUE);
How to switch on Diagnostics for Trace in OAF Pages?
- Using System Administrator responsibility, set profile options at the user level:
FND: Debug Log Enabled : Yes
FND: Debug Log Filename : leave it as null
FND: Debug Log Level : Statement
FND: Debug Log Module : %
FND: Diagnostics = Yes
Click on Diagnostics Link on the top of the page and then select the option : set trace level , and then click
Subscribe to:
Posts (Atom)