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.

Calling oracle Procedure which return recordsets from PHP

Here is an example of how to fetch records from oracle procedure using cursor which returns a recordset and read it from PHP.

Please create a procedure to get values from a table region_master.

CREATE OR REPLACE PROCEDURE "GETREGIONLISTBYNAMEANDDESC"(

        RegionName In Region_MASTER.region_name%TYPE,

        RegionDesc In Region_MASTER.region_desc%TYPE,

        ResultSet OUT sys_refcursor)

IS

begin

OPEN ResultSet for

    SELECT region_id,region_code,region_name,region_desc,updated_dt FROM Region_MASTER WHERE region_name LIKE '%'|| RegionName || '%'

    AND region_desc LIKE '%' || RegionDesc || '%';

END;

To fetch the results from php write the following code in PHP to get the values.

$curs = OCINewCursor($varOrConnection);

$sqlAssetData = "BEGIN CPM.GETREGIONLISTBYNAMEANDDESC( :regname, :regdesc, :ResultSet); END;";

$resAssetData = ociparse($varOrConnection, $sqlAssetData) or die ('Can not Execute statment');

ocibindbyname($resAssetData, ":regname", &$regname,32);

ocibindbyname($resAssetData, ":regdesc", &$regdesc,32);

ocibindbyname($resAssetData, ":ResultSet", &$curs, -1, OCI_B_CURSOR);                                                                         

ociexecute($resAssetData) or die ('Can not Execute statment');

ociexecute($curs);                                                                                                                            

while (OCIFetchInto($curs,&$ResultSet)) {                                                                                                     

   echo "

";
    print_r($ResultSet); 
   echo "";

}

Error Your password has expired. Please contact administrator to reset it while logging oracle portal

If you get this error "Your password has expired. Please contact administrator to reset it." in oracle portal, The solution is 

STEP 1:Login sqlplus as dba.

STEP2: Enter the following "execute orasso.WWSSO_OID_INTEGRATION.reset_passwd('portal username',null,'new password');"

STEP3: Login into your new portal with this password.