API to create list price with lines

--API to create list price with lines

DECLARE
v_return_status VARCHAR2(1) := NULL;
v_msg_count NUMBER := 0;
v_msg_data VARCHAR2 (2000);
v_price_list_rec qp_price_list_pub.price_list_rec_type;
v_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
v_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
v_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
v_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
v_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
ppr_price_list_rec qp_price_list_pub.price_list_rec_type;
ppr_price_list_val_rec qp_price_list_pub.price_list_val_rec_type;
ppr_price_list_line_tbl qp_price_list_pub.price_list_line_tbl_type;
ppr_price_list_line_val_tbl qp_price_list_pub.price_list_line_val_tbl_type;
ppr_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
ppr_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
ppr_pricing_attr_tbl qp_price_list_pub.pricing_attr_tbl_type;
ppr_pricing_attr_val_tbl qp_price_list_pub.pricing_attr_val_tbl_type;
--apps initilization variables
x_user_id NUMBER :=Fnd_Profile.VALUE('4473');
x_resp_id NUMBER :=fnd_global.resp_id;
x_appl_id NUMBER :=fnd_global.resp_appl_id;
x_org_id NUMBER :=fnd_global.org_id;
x_login_id NUMBER :=Fnd_Profile.VALUE('LOGIN_ID');
BEGIN
fnd_global.apps_initialize(x_user_id, x_resp_id, x_appl_id,x_login_id);
DBMS_OUTPUT.PUT_LINE('API Execution Started');
FND_MSG_PUB.INITIALIZE;
--Header Rec
v_price_list_rec.list_header_id := 1116962;
v_price_list_rec.list_type_code := 'PRL';
v_price_list_rec.operation := qp_globals.g_opr_update;
--Line Record Values
v_price_list_line_tbl(1).list_header_id := 1116962;
v_price_list_line_tbl(1).list_line_id := qp_list_lines_s.nextval;
v_price_list_line_tbl(1).list_line_type_code:= 'PLL';
v_price_list_line_tbl(1).operation := qp_globals.g_opr_create;
v_price_list_line_tbl(1).operand := 100;
v_price_list_line_tbl(1).arithmetic_operator:= 'UNIT_PRICE';
v_price_list_line_tbl(1).modifier_level_code :='LINE';
--
----Attribute Record Values
v_pricing_attr_tbl(1).pricing_attribute_id := qp_pricing_attributes_s.nextval;
v_pricing_attr_tbl(1).list_line_id := qp_list_lines_s.currval;
v_pricing_attr_tbl(1).operation := qp_globals.g_opr_create;
v_pricing_attr_tbl(1).product_attribute_context := 'Item';
v_pricing_attr_tbl(1).product_attribute := 'PRICING_ATTRIBUTE1';
v_pricing_attr_tbl(1).product_attr_value := '209957';
v_pricing_attr_tbl(1).product_uom_code := 'Ea';
v_pricing_attr_tbl(1).excluder_flag := 'N';
v_pricing_attr_tbl(1).attribute_grouping_no := 1;
v_pricing_attr_tbl(1).price_list_line_index := 1;
--
QP_PRICE_LIST_PUB.PROCESS_PRICE_LIST
( p_api_version_number => 1
,p_init_msg_list => fnd_api.g_true
,p_return_values => fnd_api.g_false
,p_commit => fnd_api.g_false
,x_return_status => v_return_status
,x_msg_count => v_msg_count
,x_msg_data => v_msg_data
,p_price_list_rec => v_price_list_rec
,p_price_list_line_tbl=> v_price_list_line_tbl
,p_pricing_attr_tbl => v_pricing_attr_tbl
,x_price_list_rec => ppr_price_list_rec
,x_price_list_val_rec => ppr_price_list_val_rec
,x_price_list_line_tbl=> ppr_price_list_line_tbl
,x_qualifiers_tbl => ppr_qualifiers_tbl
,x_qualifiers_val_tbl => ppr_qualifiers_val_tbl
,x_pricing_attr_tbl => ppr_pricing_attr_tbl
,x_pricing_attr_val_tbl => ppr_pricing_attr_val_tbl
,x_price_list_line_val_tbl => ppr_price_list_line_val_tbl
);
COMMIT;
END;

API to Update costing and balancing segments for element links

 /*Test on element_link_id=43033 to check that it's correction not update and in a business group 4350*/
--------------------
Declare
      l_object_version_number        number; --must be in , use a variable
      l_cost_allocation_keyflex_id        number;
      l_balancing_keyflex_id              number;
      l_cost_concat_segments_out           varchar2(4000);
      l_balance_concat_segments_out        varchar2(4000);
      l_effective_start_date            date;
      l_effective_end_date                 date;
    begin
    for I in (
                select  src_link.* --,src_bal_kff.*
                ,src_cost_kff.segment3 cost_segment3,src_cost_kff.segment4 cost_segment4
                ,src_cost_kff.segment5 cost_segment5
                ,src_bal_kff.segment1 bal_segment1 ,src_bal_kff.segment2 bal_segment2,src_bal_kff.segment3 bal_segment3
                ,src_bal_kff.segment4 bal_segment4,src_bal_kff.segment5 bal_segment5,src_bal_kff.segment6 bal_segment6
                ,src_bal_kff.segment7 bal_segment7
                 ,src_cost_kff.concatenated_segments cost_concatenated_segments
                 FROM   pay_element_links_f src_link,
                        pay_cost_allocation_keyflex src_cost_kff,
                        pay_cost_allocation_keyflex src_bal_kff
                  WHERE  src_link.business_group_id =4530-- Business Group you are dealing with
                                  AND src_link.cost_allocation_keyflex_id =
                                        src_cost_kff.cost_allocation_keyflex_id
                                  AND src_link.balancing_keyflex_id =
                                        src_bal_kff.cost_allocation_keyflex_id
                                  AND TRUNC (SYSDATE) BETWEEN src_link.effective_start_date
                                                          AND  src_link.effective_end_date
                 and src_link.element_link_id = 43033
     );
    LOOP
    BEGIN
     l_object_version_number :=i.object_version_number;
     pay_element_link_api.update_element_link
      (p_validate                        =>  false
      ,p_effective_date                => i.effective_start_date -- in     date
      ,p_element_link_id             => i.element_link_id--in     number
      ,p_datetrack_mode               => 'CORRECTION'--to correct
    ,p_costable_type             => i.costable_type       -- in     varchar2  default hr_api.g_varchar2
      ,p_element_set_id          => i.element_set_id         -- in     number    default hr_api.g_number
      ,p_multiply_value_flag     => i.multiply_value_flag       -- in     varchar2  default hr_api.g_varchar2
      ,p_standard_link_flag      => i.standard_link_flag       -- in     varchar2  default hr_api.g_varchar2
      ,p_transfer_to_gl_flag     => i.transfer_to_gl_flag           -- in     varchar2  default hr_api.g_varchar2
      --,p_comments                       -- in     varchar2  default hr_api.g_varchar2
      ,p_comment_id              => i.comment_id       -- in     varchar2  default hr_api.g_varchar2
      ,p_employment_category     => i.employment_category       -- in     varchar2  default hr_api.g_varchar2
      ,p_qualifying_age          => i.qualifying_age       -- in     number    default hr_api.g_number
      ,p_qualifying_length_of_service => i.qualifying_length_of_service    --in     number    default hr_api.g_number
      ,p_qualifying_units              => i.qualifying_units  --in     varchar2  default hr_api.g_varchar2
      ,p_attribute_category           => i.attribute_category   --in     varchar2  default hr_api.g_varchar2
        ,p_attribute1                 => i.attribute1 --     in     varchar2  default hr_api.g_varchar2
      ,p_attribute2                    => i.attribute2 --    in     varchar2  default hr_api.g_varchar2
      ,p_attribute3                     => i.attribute3 --   in     varchar2  default hr_api.g_varchar2
      ,p_attribute4                     => i.attribute4 --   in     varchar2  default hr_api.g_varchar2
      ,p_attribute5                     => i.attribute5 --   in     varchar2  default hr_api.g_varchar2
      ,p_attribute6                     => i.attribute6 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute7                      => i.attribute7 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute8                      => i.attribute8 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute9                      => i.attribute9 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute10                    => i.attribute10 --   in     varchar2  default hr_api.g_varchar2
      ,p_attribute11                     => i.attribute11 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute12                     => i.attribute12 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute13                     => i.attribute13 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute14                      => i.attribute14 -- in     varchar2  default hr_api.g_varchar2
      ,p_attribute15                     => i.attribute15 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute16                      => i.attribute16 -- in     varchar2  default hr_api.g_varchar2
      ,p_attribute17                      => i.attribute17 -- in     varchar2  default hr_api.g_varchar2
      ,p_attribute18                      => i.attribute18 -- in     varchar2  default hr_api.g_varchar2
      ,p_attribute19                     => i.attribute19 --  in     varchar2  default hr_api.g_varchar2
      ,p_attribute20                     => i.attribute20 --  in     varchar2  default hr_api.g_varchar2
      --,p_cost_segment1                    => i.cost_segment1 -- in     varchar2  default hr_api.g_varchar2
      --,p_cost_segment2                   => i.cost_segment2 -- in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment3                   => i.cost_segment3 -- in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment4                   => i.cost_segment4 -- in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment5                   => i.cost_segment5 --
      /*,p_cost_segment6                   in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment7                   in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment8                   in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment9                   in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment10                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment11                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment12                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment13                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment14                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment15                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment16                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment17                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment18                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment19                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment20                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment21                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment22                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment23                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment24                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment25                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment26                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment27                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment28                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment29                  in     varchar2  default hr_api.g_varchar2
      ,p_cost_segment30                  in     varchar2  default hr_api.g_varchar2*/
      ,p_balance_segment1               => i.bal_segment1 --  in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment2                => i.bal_segment2 -- in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment3               => i.bal_segment3 --  in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment4               => i.bal_segment4 --  in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment5                => i.bal_segment5 -- in     varchar2  default hr_api.g_varchar2*/
      ,p_balance_segment6                =>'000000'--change this only --RRWR
      ,p_balance_segment7               => i.bal_segment7 -- in  in     varchar2  default hr_api.g_varchar2
     /* ,p_balance_segment8                in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment9                in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment10               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment11               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment12               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment13               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment14               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment15               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment16               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment17               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment18               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment19               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment20               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment21               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment22               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment23               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment24               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment25               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment26               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment27               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment28               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment29               in     varchar2  default hr_api.g_varchar2
      ,p_balance_segment30             in     varchar2  default hr_api.g_varchar2 */
      ,p_cost_concat_segments_in       => i.cost_concatenated_segments--  in     varchar2  default hr_api.g_varchar2
     --,p_balance_concat_segments_in => i.bal_segment1||'.'||i.bal_segment2||'.'||i.bal_segment4||'.'||i.bal_segment5||'.132000.'||i.bal_segment7--in     varchar2  default hr_api.g_varchar2
      ,p_object_version_number            => l_object_version_number --must be in , use a variable
      ,p_cost_allocation_keyflex_id     => l_cost_allocation_keyflex_id
      ,p_balancing_keyflex_id           => l_balancing_keyflex_id
      ,p_cost_concat_segments_out      => l_cost_concat_segments_out
      ,p_balance_concat_segments_out   => l_balance_concat_segments_out--  out nocopy    varchar2
      ,p_effective_start_date             => l_effective_start_date
      ,p_effective_end_date                 => l_effective_end_date--out nocopy    date
      );           
   --   commit;--**
      Exception when others then
       dbms_output.put_line('Error Not updated element_link_id ' ||I.element_link_id );
      END;
End Loop;
end;

Getting row lock information from the Active Session History archive

select sample_time, session_state, blocking_session,
owner||'.'||object_name||':'||nvl(subobject_name,'-') obj_name,
    dbms_ROWID.ROWID_create (
        1,
        o.data_object_id,
        current_file#,
        current_block#,
        current_row#
    ) row_id
from dba_hist_active_sess_history s, dba_objects o
where user_id = 92
and sample_time between
    to_date('29-SEP-12 04.55.02 PM','dd-MON-yy hh:mi:ss PM')
    and
    to_date('29-SEP-12 05.05.02 PM','dd-MON-yy hh:mi:ss PM')
and event = 'enq: TX - row lock contention'
and o.data_object_id = s.current_obj#
order by 1,2;

Adding System Adminisitrator Reponsibility from the Backend

--get the user_id to feed the api below
select *
from fnd_user
where user_name like 'AHMAD.RAMMAL'  ----

--get the responsibility_id to feed the api below
select *
from FND_RESPONSIBILITY
where RESPONSIBILITY_KEY = 'SYSTEM_ADMINISTRATOR'


-- replace the user_id and responsibility_id with the out values above
begin
fnd_user_resp_groups_api.insert_assignment
(user_id => 19477
,responsibility_id => 20420
,responsibility_application_id => 1
,security_group_id => 0
,start_date => SYSDATE
,end_date => NULL
,description => NULL
);
end;

Commit;

AME responsibility linking for users through API

Run the following after you finish the api creation:
Also you might need after assigning AME transactions to user :
Run under system administrator : workflow directory services USER/ROLE validation

AME user setup

Accessing Approval Management Engine (AME)

Accessing Approval Management Engine (AME) is not possible as we access other responsibility / functions in Oracle Applications like Order Management / Application Developer etc.

AME is controlled by RBAC (Role Based Access Control). So in this case even you assign responsibility to the user but if user don't have the appropriate roles then you will not be able to access Approval Management functions.

There are two seeded responsibility for AME :-

• Approvals Management Administrator
• Approvals Management Business Analyst

Let's assign the responsibility directly to user and see what happens.

 Login with respective user.

Chose Approval Management Responsibility

You will receive below error.

With the screen shots above we can see that even though the responsibility is assigned to user while trying to access it, It says There is no valid navigation for this responsibility.

Accessing AME is two step process :-

• Assign the required roles to the user : This will give user access to use Approval Management functions.
• Grant access to respective AME transaction type : This will provide access to AME transaction type. If we don't perform this step then we will be able to access AME responsibility but will not be able to access any transaction types.

We have following seeded roles provided by Oracle that can be used to access AME.

• Approvals Management Administrator
• Approvals Management Business Analyst
• Approvals Management Process Owner
• Approvals Management System Administrator
• Approvals Management System Viewer

Step -1 : Assign the Roles

• Login with administrator / sysadmin user.
  
• Navigate to User Management -> User.
  
• Enter User Name and click on Go to find the user.
• Click on Update button
  
• Click on Assign Roles button.
  
• Search the role 'Approval Management Administrator' from LOV and select it.
  
  
• Based on the role chosen you will see number of functions added under users roles.
  

Step -2 : Grant Access to Transaction Types

• Login with administrator / sysadmin user.
• Navigate to Functional Administrator -> Grants -> Create Grants.
  
  
• Enter a name and description for the Grant
• Enter effective from date and optionally effective to date.
• Select Grantee Type from Security Context region
• All Users : The grant will be applied to all the users and all users will be given same right / privileges that is part of this grant. 
• Group Of Users : The grant will be applied to users those are part of the group.
• Specific User : Grant will be applicable of specific set of users that you select.
• I have opted for specific user.
• In addition to this you can also choose Operating Unit and Responsibility to make is more secure. Say you want to provide a user to AME access to US operating Unit only. In that case you can choose operating unit. If there is not such restriction required then leave it blank.
• From Data Security region select an object. In our case it will be 'AME Transaction Types'
  
• Click Next
• In the Data Context Type select 'All Rows'.
  
• Select 'AME Transaction Types' as permission set and click Next to review the setup.
  
• Finish to complete.
  

Now you are good to use AME and setup approvals. Happy Setups...

Workflow Background Engine

Workflow Background Engine

for example you might need this after updating roles for user

 - Login to System Administrator responsibility
 - Navigate to Concurrent > Manager > Administer
 - Query "Workflow Agent Listener Service"
 - Click on Restart

Or,

- Login to System Administrator responsibility
- OAM > Workflow > Service Components
 - Select "Workflow Agent Listener Service" and stop/start or restart


start Workflow Background Engine with recommended params as follows:



4. Process Deferred : Yes
5. Process Timeout : No
6. Process Stuck : No

4. Process Deferred : No
5. Process Timeout : Yes
6. Process Stuck : No

4. Process Deferred : No
5. Process Timeout : No
6. Process Stuck : Yes