Wednesday, 12 June 2013

Create Element Entry and Create Blance Adjustment

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
                        );

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;



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.

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?


In order to enable "About the Page" in Oracle Application following profile needs to be set to Yes.

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);

How to switch on Diagnostics for Trace in OAF Pages?


Kindly follow the below steps for specific user who can reproduce the issue and provide the logs and trace files :
- 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