***********************************
CREATE OR REPLACE PACKAGE BODY APPS.xx_yyy AS
/**
* Establishes the Oracle Apps Session so that the WHO values can be supplied
* initialize the application session with the 'xx_yy' generic
* @published
*
*/ 
PROCEDURE get_sr_details(
p_request_number_i IN VARCHAR2
,p_sr_detls_obj_o OUT NOCOPY xx_details_obj_type 
,p_business_id_tab_o OUT NOCOPY xx_bugid_tab_type
,p_note_tab_o OUT NOCOPY xx_sr_summary_tab_type
,p_notify_sr_tab_o OUT NOCOPY xx_notify_sr_tab_type
,p_notify_user_tab_o OUT NOCOPY xx_notify_user_tab_type
) 
IS
p_sr_detls_obj_o XX_DETAILS_OBJ_TYPE;

BEGIN 

SELECT sr.incident_number 
,sr.customer_ticket_number 
,sr.summary 
,sev.NAME 
,sr.owner 
,p.party_name 
,alt.contract_number 
,hzp.party_site_name 
,alt.service_line_name 
,alt.contract_end_date 
,alt.contract_status 
,alt.serial_number 
,sr.incident_attribute_12 
,sr.close_date 
,srb.rma_number 
INTO p_sr_detls_obj_o.incident_number
,p_sr_detls_obj_o.customer_ticket_number
,p_sr_detls_obj_o.summary 
,p_sr_detls_obj_o.NAME 
,p_sr_detls_obj_o.owner 
,p_sr_detls_obj_o.party_name 
,p_sr_detls_obj_o.contract_number 
,p_sr_detls_obj_o.party_site_name 
,p_sr_detls_obj_o.service_line_name 
,p_sr_detls_obj_o.contract_end_date 
,p_sr_detls_obj_o.contract_status 
,p_sr_detls_obj_o.serial_number 
,p_sr_detls_obj_o.incident_attribute_12 
,p_sr_detls_obj_o.close_date 
FROM cs_incidents_all_vl sr
,cs_incidents_all_B srb
,cs_incident_severities_vl sev
,cs_hz_sr_contact_points srcp
,hz_parties p
,cs_sr_hz_relationships_v hzr
,hz_party_sites hzp
,xxcts_ent_audit_log_tbl alt
WHERE sr.incident_severity_id = sev.incident_severity_id
AND sr.incident_id = srb.incident_id
AND sr.incident_id = alt.service_request_id 
AND sr.incident_id = srcp.incident_id
AND srcp.primary_flag = 'Y'
AND p.party_id = sub_party_id
AND rel_party_type = 'PARTY_RELATIONSHIP'
AND sub_party_type = 'PERSON'
AND hzr.rel_party_id = srcp.party_id
AND sr.incident_number = UPPER(p_request_number_i);

get_sr_bugid_details(p_sr_detls_obj_o.incident_id,p_business_id_tab_o);

get_sr_notify_details(p_sr_detls_obj_o.incident_id,p_notify_sr_tab_o);

get_notify_user_details(p_sr_detls_obj_o.customer_cco_name,p_notify_user_tab_o);

get_sr_note_details(p_request_number_i,p_note_tab_o); 

END get_sr_details;

PROCEDURE get_sr_bugid_details(
p_incident_id_i IN NUMBER
,p_business_id_tab_o OUT NOCOPY xx_bugid_tab_type
)
IS
CURSOR c_get_sr_bugid_details_cur
IS
SELECT def.defect_id,def.defect_number
FROM cs_incident_links_ext link
,css_def_defects_b def
WHERE link.to_object_id = def.defect_id AND
link.from_incident_id = p_incident_id_i;

p_business_id_tab_o xx_bugid_obj_type;

BEGIN

p_business_id_tab_o := xx_bugid_tab_type();

OPEN c_get_sr_bugid_details_cur;

FETCH c_get_sr_bugid_details_cur BULK COLLECT INTO p_business_id_tab_o;

CLOSE c_get_sr_bugid_details_cur;

xxcts_fea_utl_log.DEBUG('...Exiting xx_yy.get_sr_bugid_details');

END get_sr_bugid_details;

PROCEDURE get_sr_notify_details(
p_incident_id_i IN NUMBER
,p_notify_sr_tab_o OUT NOCOPY xx_notify_sr_tab_type
)
IS
CURSOR c_get_sr_notify_cur
IS
SELECT xns.notification_code 
FROM xxcts_cot_case_notifications xns
WHERE xns.incident_id = p_incident_id_i;

p_notify_sr_tab_o xx_notify_sr_obj_type;

BEGIN
p_notify_sr_tab_o := xx_notify_sr_tab_type();

OPEN c_get_sr_notify_cur;

FETCH c_get_sr_notify_cur BULK COLLECT INTO p_notify_sr_tab_o;

CLOSE c_get_sr_notify_cur;

xxcts_fea_utl_log.DEBUG('...Exiting xx_yy.get_sr_notify_details');

END get_sr_notify_details;

PROCEDURE get_notify_user_details(
p_contact_cco_name_i IN VARCHAR2
,p_notify_user_tab_o OUT NOCOPY xx_notify_user_tab_type
)
IS
CURSOR c_get_sr_notify_user_cur
IS
SELECT xnu.notification_code
FROM xxcts_cot_cont_notifications xnu
WHERE xnu.contact_cco_id = p_contact_cco_name_i;

p_notify_user_tab_o xx_notify_user_obj_type;

BEGIN
p_notify_user_tab_o := xx_notify_user_tab_type ();
OPEN c_get_sr_notify_user_cur;

FETCH c_get_sr_notify_user_cur BULK COLLECT INTO p_notify_user_tab_o;

CLOSE c_get_sr_notify_user_cur;

xxcts_fea_utl_log.DEBUG('...Exiting xx_yy.get_sr_notify_details');

END get_notify_USER_details;

PROCEDURE get_sr_note_details(
p_sr_number_i IN VARCHAR2
,p_note_tab_o OUT NOCOPY xx_sr_summary_tab_type
)
IS 
CURSOR c_get_sr_note_details_cur
IS
SELECT note.notes
,note.notes_detail
FROM jtf_notes_vl note
,fnd_user creator
,fnd_user updater
WHERE source_object_id = p_sr_number_i
AND note.created_by = creator.user_id
AND note.last_updated_by = updater.user_id
AND source_object_code = 'SR';

p_note_tab_o xx_sr_summary_obj_type;

BEGIN

p_note_tab_o := xx_sr_summary_tab_type ();

OPEN c_get_sr_note_details_cur;

FETCH c_get_sr_note_details_cur BULK COLLECT INTO p_note_tab_o;

CLOSE c_get_sr_note_details_cur;

xxcts_fea_utl_log.DEBUG('...Exiting xx_yy.get_sr_note_details');

END get_sr_note_details;

PROCEDURE apps_initialize_check
IS
BEGIN
-- Ensure Oracle Application Session has been established

IF (xx_fea_utl_session.user_id IS NULL)
THEN

xx_fea_utl_log.ERROR('Throwing MISSING_OAS Exception');

XX_FEA_UTL_EXCEPTION.throw('MISSING_OAS');

END IF;

END apps_initialize_check;

END xx_yy;
/
**********************************************