The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cbtc.billing_category
FROM csf_debrief_lines lines,
mtl_system_items_b msi,
cs_txn_billing_types txbt,
CS_BILLING_TYPE_CATEGORIES cbtc
WHERE lines.debrief_line_id = p_debrief_line_id
AND msi.inventory_item_id = lines.inventory_item_id
AND msi.organization_id = NVL(NVL(lines.issuing_inventory_org_id, lines.receiving_inventory_org_id), csm_profile_pkg.get_organization_id(p_user_id))
AND lines.transaction_type_id = txbt.transaction_type_id
AND msi.material_billable_flag = txbt.billing_type
AND txbt.billing_type = cbtc.billing_type;
SELECT cbtc.billing_category
FROM csf_debrief_lines lines,
cs_txn_billing_types txbt,
CS_BILLING_TYPE_CATEGORIES cbtc
WHERE lines.debrief_line_id = p_debrief_line_id
AND lines.txn_billing_type_id = txbt.txn_billing_type_id
AND txbt.billing_type = cbtc.billing_type;
CSM_ACC_PKG.Insert_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
,P_ACC_TABLE_NAME => g_debrief_acc_table_name
,P_SEQ_NAME => g_debrief_seq_name
,P_PK1_NAME => g_debrief_pk1_name
,P_PK1_NUM_VALUE => p_debrief_line_id
,P_USER_ID => p_user_id
);
SELECT dhdr.task_assignment_id, jtrs.user_id, jta.resource_id ,dbl.inventory_item_id, dbl.instance_id,
NVL(NVL(issuing_inventory_org_id, receiving_inventory_org_id), csm_profile_pkg.get_organization_id(jtrs.user_id)) AS organization_id
FROM csf_debrief_lines dbl,
csf_debrief_headers dhdr,
jtf_task_assignments jta,
jtf_rs_resource_extns jtrs
WHERE dbl.debrief_line_id = p_debrief_line_id
AND dbl.debrief_header_id = dhdr.debrief_header_id
AND jta.task_assignment_id = dhdr.task_assignment_id
AND jtrs.resource_id (+)= jta.resource_id
;
SELECT acc.user_id, dbl.inventory_item_id, dbl.instance_id,
NVL(NVL(dbl.issuing_inventory_org_id, dbl.receiving_inventory_org_id), csm_profile_pkg.get_organization_id(p_user_id)) AS organization_id
FROM csf_debrief_lines dbl,
csm_debrief_lines_acc acc
WHERE dbl.debrief_line_id = p_debrief_line_id
AND acc.debrief_line_id = dbl.debrief_line_id
AND acc.user_id = p_user_id;
CSM_ACC_PKG.Delete_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
,P_ACC_TABLE_NAME => g_debrief_acc_table_name
,P_PK1_NAME => g_debrief_pk1_name
,P_PK1_NUM_VALUE => p_debrief_line_id
,P_USER_ID => p_user_id
);
p_is_inventory_item_updated IN VARCHAR2, p_old_instance_id IN NUMBER,
p_is_instance_updated IN VARCHAR2)
IS
l_sqlerrno VARCHAR2(20);
SELECT dhdr.task_assignment_id,
jtrs.user_id,
jta.resource_id,
dbl.inventory_item_id,
dbl.instance_id,
NVL(nvl(issuing_inventory_org_id, receiving_inventory_org_id), csm_profile_pkg.get_organization_id(jtrs.user_id)) AS organization_id,
acc.access_id
FROM csf_debrief_lines dbl,
csf_debrief_headers dhdr,
csm_debrief_lines_acc acc,
jtf_task_assignments jta,
jtf_rs_resource_extns jtrs
WHERE dbl.debrief_line_id = p_debrief_line_id
AND dbl.debrief_header_id = dhdr.debrief_header_id
AND jta.task_assignment_id = dhdr.task_assignment_id
AND jtrs.resource_id = jta.resource_id
AND acc.debrief_line_id = dbl.debrief_line_id
AND acc.user_id = jtrs.user_id
;
IF p_is_inventory_item_updated = 'Y' THEN
-- insert the new inventory item
IF l_csm_debrfLnUpdInit_rec.inventory_item_id IS NOT NULL THEN
csm_system_item_event_pkg.system_item_mdirty_i(p_inventory_item_id=>l_csm_debrfLnUpdInit_rec.inventory_item_id,
p_organization_id=>l_csm_debrfLnUpdInit_rec.organization_id,
p_user_id=>l_csm_debrfLnUpdInit_rec.user_id);
IF p_is_instance_updated = 'Y' THEN
-- insert the new instance
IF l_csm_debrfLnUpdInit_rec.instance_id IS NOT NULL THEN
csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_csm_debrfLnUpdInit_rec.instance_id,
p_user_id=>l_csm_debrfLnUpdInit_rec.user_id,
p_flowtype=>NULL,
p_error_msg=>l_error_msg,
x_return_status=>l_return_status);
CSM_ACC_PKG.Update_Acc
( P_PUBLICATION_ITEM_NAMES => CSM_ACC_PKG.t_publication_item_list(get_debrief_pi_name (p_billing_category))
,P_ACC_TABLE_NAME => g_debrief_acc_table_name
,P_ACCESS_ID => p_access_id
,P_USER_ID => p_user_id
);