[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT acc.incident_id,
acc.user_id,
acc.counter
FROM csm_incidents_all_acc acc,
cs_incidents_all_b inc,
cs_incident_statuses_b ists
WHERE inc.incident_id = acc.incident_id
AND decode(CSM_UTIL_PKG.get_group_owner(inc.owner_group_id),
-1,
CSM_UTIL_PKG.get_owner(inc.created_by),
CSM_UTIL_PKG.get_group_owner(inc.owner_group_id)) = acc.user_id
AND (inc.creation_date < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id)))
AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
AND ists.CLOSE_FLAG = 'Y';
SELECT 1
FROM jtm_con_request_data
WHERE product_code = 'CSM'
AND package_name = 'CSM_SR_EVENT_PKG'
AND procedure_name = 'PURGE_INCIDENTS_CONC'
FOR UPDATE OF last_run_date NOWAIT;
l_incident_tbl.DELETE;
l_userid_tbl.DELETE;
UPDATE jtm_con_request_data
SET last_run_date = l_last_run_date
WHERE CURRENT OF l_upd_last_run_date_csr;
SELECT tsk.source_object_type_code
FROM jtf_tasks_b tsk, jtf_task_types_b ttype
WHERE tsk.task_id = p_task_id
AND tsk.task_type_id = ttype.task_type_id
AND (ttype.RULE = 'DISPATCH' OR ttype.private_flag = 'Y')
AND tsk.SCHEDULED_START_DATE IS NOT NULL
AND tsk.SCHEDULED_END_DATE IS NOT NULL
;
SELECT 1
FROM jtf_tasks_b jt,
jtf_task_statuses_b jts
WHERE jt.task_id = p_task_id
AND jt.task_status_id = jts.task_status_id
AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL
OR (jts.assigned_flag = 'Y'
OR jts.closed_flag = 'Y'
OR jts.completed_flag = 'Y'
));
SELECT 1
FROM jtf_task_assignments jta,
jtf_tasks_b jt,
jtf_task_statuses_b jts
WHERE jta.task_assignment_id = p_task_assg_id
AND jt.task_id = jta.task_id
AND jta.assignment_status_id = jts.task_status_id
AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL
OR (jts.assigned_flag = 'Y'
OR jts.closed_flag = 'Y'
OR jts.completed_flag = 'Y'
));
SELECT sr_contact_point_id,
contact_point_id,
contact_type,
party_id
FROM cs_hz_sr_contact_points
WHERE incident_id = p_incident_id
AND sr_contact_point_id = NVL(p_sr_contact_point_id, sr_contact_point_id);
SELECT jtrs.resource_id
FROM jtf_rs_resource_extns jtrs
WHERE jtrs.source_id = p_party_id
AND jtrs.CATEGORY = 'EMPLOYEE'
AND SYSDATE BETWEEN jtrs.start_date_active AND nvl(jtrs.end_date_active, SYSDATE)
;
-- insert resource into acc table
IF l_resource_id IS NOT NULL THEN
csm_resource_extns_event_pkg.resource_extns_acc_i(p_resource_id=>l_resource_id,
p_user_id=>p_user_id);
-- insert party record
csm_party_event_pkg.party_acc_i(p_party_id=> r_srcontpts_rec.party_id,
p_user_id=> p_user_id,
p_flowtype=> p_flowtype,
p_error_msg=> l_error_msg,
x_return_status=> l_return_status);
SELECT cir.relationship_id AS relationship_id ,
cir.subject_id AS instance_id ,
cii.inventory_item_id
FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
WHERE CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND p_transaction_date BETWEEN NVL(CIRo.active_start_date, p_transaction_date) AND NVL(CIRo.active_end_date, p_transaction_date)
AND (b_htm5='N' OR LEVEL <=2) --for htm5 functionality
START WITH CIRo.OBJECT_ID = p_instance_id
CONNECT BY NOCYCLE CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
) CIR,
CSI_ITEM_INSTANCES CII
WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
AND p_transaction_date BETWEEN NVL ( CII.ACTIVE_START_DATE , p_transaction_date )
AND NVL ( CII.ACTIVE_END_DATE , p_transaction_date)
UNION
-- select the current instance
SELECT l_null_relationship_id AS relationship_id,
cii.instance_id AS instance_id,
cii.inventory_item_id
FROM csi_item_instances cii
WHERE cii.instance_id = p_instance_id
AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
AND NVL(cii.active_end_date, p_transaction_date)
UNION
SELECT cir.relationship_id AS relationship_id,
cir.object_id AS instance_id,
cii.inventory_item_id
FROM CSI_II_RELATIONSHIPS cir,
csi_item_instances cii
WHERE cir.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND cir.SUBJECT_ID = p_instance_id
AND cii.instance_id = cir.object_id
AND p_transaction_date BETWEEN NVL(cir.active_start_date, p_transaction_date)
AND NVL(cir.active_end_date, p_transaction_date)
AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
AND NVL(cii.active_end_date, p_transaction_date)
;
SELECT attrval.attribute_value_id
FROM csi_iea_values attrval,
csi_i_extended_attribs attr
WHERE attrval.instance_id = p_instance_id
AND attrval.attribute_id = attr.attribute_id
AND SYSDATE BETWEEN NVL(attrval.active_start_date, SYSDATE) AND NVL(attrval.active_end_date, SYSDATE)
AND SYSDATE BETWEEN NVL(attr.active_start_date, SYSDATE) AND NVL(attr.active_end_date, SYSDATE)
;
-- process inserts of additional attributes
FOR r_csi_iea_values_ins_rec IN l_csi_iea_values_ins_csr(p_instance_id) LOOP
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_SEQ_NAME => g_acc_sequence_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => r_csi_iea_values_ins_rec.attribute_value_id
,P_USER_ID => p_user_id
);
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
,P_ACC_TABLE_NAME => g_incidents_acc_table_name
,P_SEQ_NAME => g_incidents_seq_name
,P_PK1_NAME => g_incidents_pk1_name
,P_PK1_NUM_VALUE => p_incident_id
,P_USER_ID => p_user_id
);
SELECT sr_contact_point_id,
contact_point_id,
contact_type,
party_id
FROM cs_hz_sr_contact_points
WHERE incident_id = p_incident_id
AND sr_contact_point_id = NVL(p_sr_contact_point_id, sr_contact_point_id);
SELECT jtrs.resource_id
FROM jtf_rs_resource_extns jtrs
WHERE jtrs.source_id = p_party_id
AND jtrs.CATEGORY = 'EMPLOYEE'
AND SYSDATE BETWEEN jtrs.start_date_active AND nvl(jtrs.end_date_active, SYSDATE)
;
-- delete resource from acc table
IF l_resource_id IS NOT NULL THEN
csm_resource_extns_event_pkg.resource_extns_acc_d(p_resource_id=>l_resource_id,
p_user_id=>p_user_id);
-- delete party record
csm_party_event_pkg.party_acc_d(p_party_id=> r_srcontpts_rec.party_id,
p_user_id=> p_user_id,
p_flowtype=> p_flowtype,
p_error_msg=> l_error_msg,
x_return_status=> l_return_status);
-- delete IB at location (logic not correct)
-- csm_item_instance_event_pkg.delete_ib_at_location(p_incident_id => p_incident_id, p_instance_id=>p_instance_id,p_party_site_id=>p_party_site_id,
-- p_party_id=>p_party_id,p_location_id=>p_location_id,
-- p_user_id=>p_user_id, p_flow_type=>p_flow_type);
-- delete IB notes
csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'CP',
p_sourceobjectid=>p_instance_id,
p_userid=>p_user_id,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);
SELECT cir.relationship_id AS relationship_id ,
cir.subject_id AS instance_id ,
cii.inventory_item_id
FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
WHERE CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND (b_htm5='N' OR LEVEL <=2) --for htm5 functionality SR1(A-B , B-C) & SR2(B-C,C-D) are downloaded. if no level for htm5, then SR1 will remove C-D as well
START WITH CIRo.OBJECT_ID = p_instance_id
CONNECT BY NOCYCLE CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
) CIR,
CSI_ITEM_INSTANCES CII
WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
AND EXISTS(SELECT 1 FROM csm_ii_relationships_acc acc
where acc.relationship_id = cir.relationship_id
and acc.user_id=p_user_id)
UNION
SELECT l_null_relationship_id AS relationship_id,
p_instance_id AS instance_id,
cii.inventory_item_id
FROM csi_item_instances cii
WHERE cii.instance_id = p_instance_id
AND EXISTS(SELECT 1 FROM csm_item_instances_acc acc
where acc.instance_id =p_instance_id
and acc.user_id=p_user_id)
-- get the parent instance
UNION
SELECT cir.relationship_id as relationship_id,
cir.object_id AS instance_id,
cii.inventory_item_id
FROM CSI_II_RELATIONSHIPS cir,
csi_item_instances cii
WHERE cir.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
AND cir.SUBJECT_ID = p_instance_id
AND cii.instance_id = cir.object_id
AND EXISTS(SELECT 1 FROM csm_ii_relationships_acc acc
where acc.relationship_id = cir.relationship_id
and acc.user_id=p_user_id);
SELECT attrval.attribute_value_id
FROM csi_iea_values attrval,
csi_i_extended_attribs attr
WHERE attrval.instance_id = p_instance_id
AND attrval.attribute_id = attr.attribute_id
AND EXISTS(SELECT 1 FROM CSM_CSI_ITEM_ATTR_ACC acc
WHERE acc.attribute_value_id = attrval.attribute_value_id
AND acc.USER_ID=p_user_id);
-- process deletes of additional attributes
FOR r_csi_iea_values_del_rec IN l_csi_iea_values_del_csr(p_instance_id) LOOP
CSM_ACC_PKG.Delete_acc
( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
,P_ACC_TABLE_NAME => g_acc_table_name1
,P_PK1_NAME => g_pk1_name1
,P_PK1_NUM_VALUE => r_csi_iea_values_del_rec.attribute_value_id
,P_USER_ID => p_user_id
);
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
,P_ACC_TABLE_NAME => g_incidents_acc_table_name
,P_PK1_NAME => g_incidents_pk1_name
,P_PK1_NUM_VALUE => p_incident_id
,P_USER_ID => p_user_id
);
SELECT inc.owner_group_id,
inc.created_by
FROM CS_INCIDENTS_ALL_B inc,
JTF_TASKS_B tsk,
JTF_TASK_TYPES_B ttype
WHERE tsk.TASK_ID=b_task_id
AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
AND ttype.RULE='DISPATCH';
SELECT csi.incident_id,
csi.customer_id,
csi.install_site_id,
csi.customer_product_id,
csi.inventory_item_id,
csi.inv_organization_id,
csi.contract_service_id,
csi.created_by,
csi.incident_location_id,
csi.customer_id party_id,
decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
'HZ_PARTY_SITE',
(select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
'HZ_LOCATION',
(select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
) location_id ,
nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
csi.owner_group_id,
csi.org_id
FROM cs_incidents_all_b csi
WHERE csi.incident_id = p_incident_id
AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION');
(SELECT 1
FROM csm_incidents_all_acc acc
WHERE acc.incident_id = csi.incident_id
AND acc.user_id = CSM_UTIL_PKG.get_group_owner(csi.owner_group_id));*/
SELECT NVL(LOCATION_ID,ADDRESS_ID)--R12Assest
FROM JTF_TASKS_B
WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
AND SOURCE_OBJECT_ID = p_incident_id;
SELECT TASK_ID
FROM JTF_TASKS_B tsk,
JTF_TASK_TYPES_B ttype
WHERE tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=b_incident_id
AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
AND ttype.RULE='DISPATCH';
SELECT TASK_ASSIGNMENT_ID,resource_id
FROM JTF_TASK_ASSIGNMENTS
WHERE TASK_ID=b_task_id;
SELECT RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE USER_ID=b_user_id;
SELECT USER_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID=b_resource_id;
--insert location for the sr
CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_sr_rec.incident_location_id,
p_user_id => l_owner_id);
--insert Accounts for the above party-R12
CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
(p_party_id=>l_sr_rec.customer_id
,p_user_id =>l_owner_id);
csm_task_event_pkg.acc_insert(p_task_id=>task_rec.task_id,p_user_id=>l_owner_id);
csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
SELECT csi.incident_id,
csi.customer_id,
csi.install_site_id,
csi.customer_product_id,
csi.inventory_item_id,
csi.inv_organization_id,
csi.contract_service_id,
csi.created_by,
csi.incident_location_id,
csi.customer_id party_id,
decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
'HZ_PARTY_SITE',
(select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
'HZ_LOCATION',
(select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
) location_id ,
nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
csi.owner_group_id
FROM cs_incidents_all_b csi
WHERE csi.incident_id = p_incident_id
AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION')
AND EXISTS
(SELECT 1
FROM csm_incidents_all_acc acc
WHERE acc.incident_id = csi.incident_id
AND acc.user_id = NVL(b_user_id,decode(CSM_UTIL_PKG.get_group_owner(csi.owner_group_id),-1,csi.created_by,CSM_UTIL_PKG.get_group_owner(csi.owner_group_id)) ));
SELECT tsk.TASK_ID
FROM JTF_TASKS_B tsk,
JTF_TASK_TYPES_B ttype
WHERE tsk.SOURCE_OBJECT_TYPE_CODE='SR'
AND tsk.SOURCE_OBJECT_ID=b_incident_id
AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
AND ttype.RULE='DISPATCH';
SELECT TASK_ASSIGNMENT_ID,resource_id
FROM JTF_TASK_ASSIGNMENTS
WHERE TASK_ID=b_task_id;
SELECT RESOURCE_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE USER_ID=b_user_id;
SELECT USER_ID
FROM JTF_RS_RESOURCE_EXTNS
WHERE RESOURCE_ID=b_resource_id;
--insert location for the sr
csm_hz_locations_event_pkg.delete_location(p_location_id => l_sr_rec.incident_location_id,
p_user_id => l_owner_id);
--Delete Accounts for the above party-R12
CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
(p_party_id=>l_sr_rec.customer_id
,p_user_id =>l_owner_id);
csm_task_event_pkg.acc_delete(p_task_id=>task_rec.task_id,p_user_id=>l_owner_id);
csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
/*Other grp's member Resource to be deleted from acc if assigned to him*/
OPEN c_resource_id(l_owner_id);
CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
PROCEDURE SR_UPD_INIT(p_incident_id IN NUMBER, p_is_install_site_updated IN VARCHAR2,
p_old_install_site_id IN NUMBER,
p_is_incident_location_updated IN VARCHAR2,
p_old_incident_location_id IN NUMBER, p_is_sr_customer_updated IN VARCHAR2,
p_old_sr_customer_id IN NUMBER, p_is_sr_instance_updated IN VARCHAR2,
p_old_instance_id IN NUMBER, p_is_inventory_item_updated IN VARCHAR2,
p_old_inventory_item_id IN NUMBER, p_old_organization_id IN NUMBER,
p_old_party_id IN NUMBER, p_old_location_id IN NUMBER,
p_is_contr_service_id_updated IN VARCHAR2, p_old_contr_service_id IN NUMBER)
IS
l_sqlerrno VARCHAR2(20);
SELECT csi.incident_id,
csi.customer_id,
csi.install_site_id,
csi.customer_product_id,
csi.inventory_item_id,
csi.inv_organization_id,
csi.contract_service_id,
csi.created_by,
csi.incident_location_id,
csi.customer_id party_id,
decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
'HZ_PARTY_SITE',
(select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
'HZ_LOCATION',
(select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
) location_id ,
nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
csi.owner_group_id
FROM cs_incidents_all_b csi
WHERE csi.incident_id = p_incident_id
AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION');
SELECT NVL(LOCATION_ID,ADDRESS_ID)--R12Assest
FROM JTF_TASKS_B
WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
AND SOURCE_OBJECT_ID = p_incident_id;
SELECT acc.access_id, acc.user_id
FROM csm_incidents_all_acc acc
WHERE acc.incident_id = p_incident_id;
IF p_is_incident_location_updated = 'Y' THEN
IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
--Delete old location
IF p_old_incident_location_id IS NOT NULL THEN
csm_hz_locations_event_pkg.delete_location(p_location_id => l_sr_rec.incident_location_id,
p_user_id => r_csm_task_assg_rec.user_id);
csm_hz_locations_event_pkg.insert_location(p_location_id => l_sr_rec.incident_location_id,
p_user_id => r_csm_task_assg_rec.user_id);
IF p_is_sr_customer_updated = 'Y' THEN
IF p_old_sr_customer_id IS NOT NULL THEN
-- spawn party del process
csm_party_event_pkg.party_acc_d(p_party_id=>p_old_sr_customer_id,
p_user_id=>r_csm_task_assg_rec.user_id,
p_flowtype=>NULL,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);
IF p_is_sr_instance_updated = 'Y' THEN
IF p_old_instance_id IS NOT NULL THEN
-- spawn SR item instance delete
csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_sr_rec.incident_id,
p_instance_id=>p_old_instance_id,
p_party_site_id=>p_old_incident_location_id,
p_party_id=>p_old_party_id,
p_location_id=>p_old_location_id,
p_organization_id=>NVL(p_old_organization_id, l_organization_id),
p_user_id=>r_csm_task_assg_rec.user_id,
p_flow_type=>NULL);
IF p_is_inventory_item_updated = 'Y' THEN
IF p_old_inventory_item_id IS NOT NULL THEN
csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
(p_inventory_item_id=>p_old_inventory_item_id,
p_organization_id=>NVL(p_old_organization_id, l_organization_id),
p_user_id=>r_csm_task_assg_rec.user_id,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);
IF p_is_contr_service_id_updated = 'Y' THEN
csm_contract_event_pkg.sr_contract_acc_u(p_incident_id=>l_sr_rec.incident_id,
p_old_contract_service_id=>p_old_contr_service_id,
p_contract_service_id=>l_sr_rec.contract_service_id,
p_user_id=>r_csm_task_assg_rec.user_id);
CSM_ACC_PKG.Update_Acc
( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
,P_ACC_TABLE_NAME => g_incidents_acc_table_name
,P_USER_ID => r_csm_task_assg_rec.user_id
,P_ACCESS_ID => r_csm_task_assg_rec.access_id
);
SELECT inc.incident_id
FROM cs_incidents_all_b inc,
cs_incident_statuses_b ists,
jtf_tasks_b tsk
WHERE inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
AND ists.CLOSE_FLAG = 'Y'
AND tsk.task_id = c_task_id
AND tsk.source_object_id = inc.incident_id;
SELECT ACCESS_ID
FROM CSM_QUERY_RESULTS_ACC
WHERE QUERY_ID = c_QUERY_ID
AND USER_ID = c_USER_ID
AND INSTANCE_ID = c_INSTANCE_ID;
SELECT INCIDENT_ID FROM CS_INCIDENTS_ALL_B
WHERE INCIDENT_NUMBER =c_incident_number;
SELECT inv.INCIDENT_ID,
inv.LINE_NUMBER,
inv.BUSINESS_PROCESS_ID,
inv.TXN_BILLING_TYPE_ID,
inv.INVENTORY_ITEM_ID,
inv.SERIAL_NUMBER,
inv.QUANTITY_REQUIRED,
inv.UNIT_OF_MEASURE_CODE,
inv.SELLING_PRICE,
inv.AFTER_WARRANTY_COST,
inv.CHARGE_LINE_TYPE,
inv.BILL_TO_PARTY_ID
FROM CS_ESTIMATE_DETAILS inv
WHERE INV.ORIGINAL_SOURCE_CODE = 'SR'
AND inv.INCIDENT_ID = c_incident_id ;
l_SQL_TEXT := 'SELECT inv.INCIDENT_ID, inv.LINE_NUMBER, inv.BUSINESS_PROCESS_ID,
inv.TXN_BILLING_TYPE_ID, inv.INVENTORY_ITEM_ID, inv.SERIAL_NUMBER,
inv.QUANTITY_REQUIRED, inv.UNIT_OF_MEASURE_CODE, inv.SELLING_PRICE,
inv.AFTER_WARRANTY_COST, inv.CHARGE_LINE_TYPE, inv.BILL_TO_PARTY_ID
FROM CS_ESTIMATE_DETAILS inv WHERE INV.ORIGINAL_SOURCE_CODE = ''SR''
AND inv.INCIDENT_ID = ' || l_INCIDENT_ID ;
CSM_QUERY_PKG.INSERT_RESULT
( p_USER_ID => l_USER_ID,
p_QUERY_ID => l_QUERY_ID,
p_INSTANCE_ID => l_INSTANCE_ID,
p_QUERY_RESULT => l_xml_blob,
p_commit => fnd_api.G_FALSE,
x_return_status => l_error_status,
x_error_message => l_error_msg
);