Query to calculate week number from a given date taking monday as first date of week in pl sql

Here is the Query to calculate week number from a given date taking monday as first date of week in pl sql.

SELECT ceil(( 7+(trunc(to_date('01-Jan-2009'),'d')-trunc(to_date('01-Jan-2009'),'Y')) )/7) FROM dual

Query to get the contact person of who raised the SR

Query to get the contact who have raised an Service Request in Oracle Service CRM. The person who raised an SR may be

1. Customer of
2. Employee of
3. Person
4. Organization -- In this case there will be no entry in cs_hz_sr_contact_points
5. Party Relationship

In cs_hz_sr_contact_points There will be an entry for contact point_type phone and contact_point_type email. It will also have contact_TYPE as PERSON OR PARTY_RELATIONSIP OR EMPLOYEE.

========================Query Begins==================
select inc.incident_number,
(Case when cont.CONTACT_TYPE in ('PERSON','PARTY_RELATIONSHIP') then per.person_first_name
when cont.contact_type='EMPLOYEE' then res.first_name end) as first_name,
(Case when cont.CONTACT_TYPE in ('PERSON','PARTY_RELATIONSHIP') then per.person_last_name
when cont.contact_type='EMPLOYEE' then res.last_name end) as last_name
from cs_incidents_all_b inc
join (select distinct incident_id,party_id,CONTACT_TYPE from cs_hz_sr_contact_points) cont
on inc.incident_id=cont.incident_id
left JOIN HZ_PARTy_relationships rel on rel.party_id=cont.party_id
left JOIN HZ_PARTIES per on per.party_id=
(case when CONTACT_TYPE='PERSON' then cont.party_id else rel.subject_id end)
left join per_all_people_f res on res.person_id=cont.party_id
where incident_number in ('10016729','10016730','10016731','10016732','10016733','10016734')
order by inc.incident_id
========================Query Ends====================

How to find error message comming in oracle API's

After the calling of API give the following
IF (fnd_msg_pub.count_msg > 0)THEN
FOR i IN 1..fnd_msg_pub.count_msg
LOOP

fnd_msg_pub.get
( p_msg_index => i,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => X_msg_count
);

DBMS_OUTPUT.PUT_LINE('API ERROR: ' || x_msg_data);
END LOOP;
dbms_output.put_line(x_jtf_note_id ||'--'|| x_return_status ||'--'|| X_msg_count ||'--'|| x_msg_data);
ELSE
DBMS_OUTPUT.PUT_LINE('Updated sr : ' || to_char(ChildIncId));
End if;

How to create database link in oracle

Use the following command

create database link daatabase_link_name connect to schema/username identified by "passwd" using 'service_name'

Assign Responsibilities to users in Oracle e-Business Suite

Step 1: Go To Navigator

Step 2: Select System Administrator

Step 3: Select Request
A Pop up will open asking to install Activex plugin. This is for the first time when you open.

Step 4: Select Security under Functions and double click Define
A pop up will open with users list. Press F11 to go to search mode.
Enter the User Name you wish to assign responsibility and press Cntrl + F11

Step 5: You will see the list of responsibilities already assigned for the user under Direct Responsibilities.
Select a empty space in the responsibility and click the button to go to responsibilities page. Type % in Find and you will find all the responsibilities.

Step 6: Select the responsibilities you want and press OK.
The selected responsibility will be added to your list.

Step 7: If you want to give a TO Date specify and save.