[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE CANT_UPDATE_DETAIL_PARAM_MSG(
P_TOKEN_AN IN VARCHAR2,
P_TOKEN_CN IN VARCHAR2,
P_TOKEN_V IN VARCHAR2 );
PROCEDURE CANNOT_DELETE_LINE_MSG(
P_TOKEN_AN IN VARCHAR2);
X_UPDATE_IB_FLAG OUT NOCOPY VARCHAR2,
X_SRC_REFERENCE_REQD_FLAG OUT NOCOPY VARCHAR2,
X_SRC_RETURN_REQD_FLAG OUT NOCOPY VARCHAR2,
X_NON_SRC_REFERENCE_REQD OUT NOCOPY VARCHAR2,
X_NON_SRC_RETURN_REQD OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2);
P_UPDATE_IB_FLAG IN VARCHAR2,
X_COMMS_TRACKABLE_FLAG OUT NOCOPY VARCHAR2,
X_SERIAL_CONTROL_FLAG OUT NOCOPY VARCHAR2,
X_REV_CONTROL_FLAG OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2);
x_disallow_charge_update OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY NUMBER);
SELECT 'Y'
FROM cs_incidents_all
WHERE incident_id = p_incident_id;
SELECT meaning from ar_lookups
WHERE lookup_type = 'CREDIT_MEMO_REASON'
and lookup_code = p_return_reason_code;
SELECT lookup_code
FROM fnd_lookup_values
WHERE lookup_type = 'CS_CHG_LINE_TYPE' AND
lookup_code = p_charge_line_type;
SELECT business_process_id
FROM cs_business_processes
WHERE business_process_id = p_business_process_id;
SELECT 'Y'
FROM hz_parties
WHERE party_id = p_party_id
AND nvl(status, 'A') = 'A';
SELECT cust_account_id
FROM hz_cust_accounts
WHERE cust_account_id = p_account_id
AND party_id = p_party_id
AND nvl(status, 'A') = 'A';
SELECT party_type
FROM hz_parties
WHERE party_id = p_party_id;
SELECT rel.party_id
FROM hz_relationships party_rel,
hz_parties sub,
hz_parties rel,
ar_lookups ar,
ar_lookups relm,
hz_relationship_types typ,
hz_code_assignments asg
WHERE party_rel.object_id = p_party_id
AND party_rel.party_id = rel.party_id
AND rel.party_id = p_contact_id
AND party_rel.subject_id = sub.party_id
AND sub.party_type = 'PERSON'
AND sub.status = 'A'
AND ar.lookup_type(+) = 'CONTACT_TITLE'
AND sub.person_pre_name_adjunct = ar.lookup_code(+)
AND relm.lookup_type(+) = 'PARTY_RELATIONS_TYPE'
AND party_rel.relationship_code = relm.lookup_code(+)
AND nvl(party_rel.start_date, sysdate-1) < sysdate
AND nvl(party_rel.end_date , sysdate+1) > sysdate
AND party_rel.status = 'A'
AND asg.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND asg.owner_table_id = typ.relationship_type_id
AND asg.class_category = 'RELATIONSHIP_TYPE_GROUP'
AND asg.class_code = 'PARTY_REL_GRP_CONTACTS'
AND typ.relationship_type = party_rel.relationship_type
AND party_rel.subject_type = 'PERSON'
AND party_rel.subject_table_name = 'HZ_PARTIES'
AND party_rel.object_type = 'ORGANIZATION'
AND party_rel.object_table_name = 'HZ_PARTIES'
AND party_rel.relationship_code = typ.forward_rel_code;
SELECT rel.party_id
FROM hz_relationships party_rel,
hz_parties sub,
hz_parties rel,
ar_lookups ar,
ar_lookups relm,
hz_relationship_types typ,
hz_code_assignments asg
WHERE party_rel.object_id = p_party_id
AND party_rel.party_id = rel.party_id
AND rel.party_id = p_contact_id
AND party_rel.subject_id = sub.party_id
AND sub.party_type = 'PERSON'
AND ar.lookup_type(+) = 'CONTACT_TITLE'
AND sub.person_pre_name_adjunct = ar.lookup_code(+)
AND relm.lookup_type(+) = 'PARTY_RELATIONS_TYPE'
AND party_rel.relationship_code = relm.lookup_code(+)
AND nvl(party_rel.start_date, sysdate-1) < sysdate
AND nvl(party_rel.end_date , sysdate+1) > sysdate
AND party_rel.status = 'A'
AND asg.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
AND asg.owner_table_id = typ.relationship_type_id
AND asg.class_category = 'RELATIONSHIP_TYPE_GROUP'
AND asg.class_code = 'PARTY_REL_GRP_CONTACTS'
AND typ.relationship_type = party_rel.relationship_type
AND party_rel.subject_type = 'PERSON'
AND party_rel.subject_table_name = 'HZ_PARTIES'
AND party_rel.object_type = 'PERSON' -- added for bug # 4744186
AND party_rel.object_table_name = 'HZ_PARTIES'
AND party_rel.relationship_code = typ.forward_rel_code;
SELECT site.party_site_id
FROM HZ_PARTY_SITES site,
HZ_PARTY_SITE_USES site_use,
HZ_PARTIES party
WHERE site.party_site_id = p_party_site_id
AND site.party_id = p_party_id
AND site.party_id = party.party_id
AND site.party_site_id = site_use.party_site_id
AND nvl(site.status, 'A') = 'A'
AND site_use.site_use_type = 'BILL_TO';
SELECT site.party_site_id
FROM HZ_PARTY_SITES site,
HZ_PARTY_SITE_USES site_use,
HZ_PARTIES party
WHERE site.party_site_id = p_party_site_id
AND site.party_id = p_party_id
AND site.party_id = party.party_id
AND site.party_site_id = site_use.party_site_id
AND nvl(site.status, 'A') = 'A'
AND site_use.site_use_type = 'SHIP_TO';
SELECT site.party_site_id
FROM HZ_PARTY_SITES site,
HZ_PARTIES party
WHERE site.party_site_id = p_party_site_id
AND site.party_id = p_party_id
AND site.party_id = party.party_id
AND nvl(site.status, 'A') = 'A';
select id, chr_id
from okc_k_lines_b
where id = p_contract_line_id;
SELECT 'x'
FROM OKC_K_HEADERS_ALL_B HDR
--OKC_K_PARTY_ROLES_B PTY
WHERE
HDR.START_DATE IS NOT NULL AND
HDR.END_DATE IS NOT NULL AND
HDR.TEMPLATE_YN='N' AND
-- HDR.ID=PTY.CHR_ID AND
-- PTY.JTOT_OBJECT1_CODE='OKX_PARTY' AND
HDR.ID = p_contract_id;
SELECT cle_id
FROM okc_k_lines_b
WHERE id = p_coverage_id;
SELECT uom_code
FROM MTL_ITEM_UOMS_VIEW
WHERE uom_code = p_uom_code AND
inventory_item_id = P_INV_ID AND
organization_id = cs_std.get_item_valdn_orgzn_id ;
SELECT instance_id
FROM csi_item_instances
WHERE instance_id = p_instance_id
AND inventory_item_id = p_inv_id;
SELECT 'Y'
FROM CSI_ITEM_INSTANCES cp,
MTL_SYSTEM_ITEMS_KFV ITEMS
WHERE cp.instance_id = p_instance_id AND
CP.OWNER_PARTY_SOURCE_TABLE = 'HZ_PARTIES' AND
cp.owner_party_id = p_party_id AND
ITEMS.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID AND
ITEMS.ORGANIZATION_ID = cs_std.get_item_valdn_orgzn_id AND
exists (select 'x'
from csi_i_parties cip
where cip.party_id = p_party_id
and cip.instance_id = cp.instance_id
and cip.party_source_table = 'HZ_PARTIES')AND
(
(fnd_profile.value('CS_SR_RESTRICT_IB') = 'YES'
and CP.LOCATION_TYPE_CODE IN('HZ_PARTY_SITES','HZ_LOCATIONS'))
or (fnd_profile.value('CS_SR_RESTRICT_IB') <> 'YES'));
SELECT 'Y'
FROM csi_item_instances
WHERE instance_id = p_instance_id
AND serial_number = p_serial_number;
SELECT list_header_id
FROM QP_LIST_HEADERS_B
WHERE list_type_code in ('PRL','AGR')
AND list_header_id = p_price_list_id
AND sysdate between nvl(start_date_active,sysdate) and
nvl(end_date_active,sysdate);
SELECT
TO_NUMBER(hoi2.org_information3) OPERATING_UNIT
FROM
hr_organization_units hou,
hr_organization_information hoi1,
hr_organization_information hoi2,
mtl_parameters mp
WHERE
mp.organization_id = p_txn_inv_org
AND mp.organization_id = hou.organization_id
AND hou.organization_id = hoi1.organization_id
AND hoi1.org_information1 = 'INV'
AND hoi1.org_information2 = 'Y'
AND hoi1.org_information_context = 'CLASS'
AND hou.organization_id = hoi2.organization_id
AND hoi1.organization_id = hoi2.organization_id
AND hoi2.org_information_context = 'Accounting Information' ;
SELECT 1
FROM CS_ESTIMATE_DETAILS
WHERE estimate_detail_id = p_estimate_detail_id;
SELECT 1
FROM MTL_ITEM_REVISIONS
WHERE inventory_item_id = p_inventory_item_id and
revision = p_item_revision and
organization_id = cs_std.get_item_valdn_orgzn_id ;
SELECT line_number
FROM CS_ESTIMATE_DETAILS
WHERE incident_id = p_incident_id and
line_number = p_line_number;
SELECT max(line_number) + 1
INTO l_line_num
FROM CS_ESTIMATE_DETAILS
WHERE incident_id = p_est_detail_rec.incident_id;
SELECT cs_estimate_details_s.nextval
INTO l_ed_id
FROM DUAL ;
CS_ESTIMATE_DETAILS_PKG.Insert_Row(
p_org_id => l_est_detail_rec.org_id,
p_incident_id => l_est_detail_rec.incident_id,
p_original_source_id => l_est_detail_rec.original_source_id,
p_original_source_code => l_est_detail_rec.original_source_code,
p_source_id => l_est_detail_rec.source_id,
p_source_code => l_est_detail_rec.source_code,
p_contract_line_id => l_est_detail_rec.contract_line_id,
p_rate_type_code => l_est_detail_rec.rate_type_code,
p_contract_id => l_est_detail_rec.contract_id,
p_coverage_id => null,
p_coverage_txn_group_id => null,
p_CURRENCY_CODE => l_EST_DETAIL_rec.currency_code,
p_CONVERSION_RATE => l_EST_DETAIL_rec.conversion_rate,
p_CONVERSION_TYPE_CODE => l_EST_DETAIL_rec.conversion_type_code,
p_CONVERSION_RATE_DATE => l_EST_DETAIL_rec.conversion_rate_date,
p_invoice_to_org_id => l_est_detail_rec.invoice_to_org_id,
p_ship_to_org_id => l_est_detail_rec.ship_to_org_id,
p_purchase_order_num => l_est_detail_rec.purchase_order_num,
p_order_line_id => l_est_detail_rec.order_line_id,
p_line_type_id => l_est_detail_rec.line_type_id,
p_LINE_CATEGORY_CODE => l_EST_DETAIL_rec.LINE_CATEGORY_CODE,
p_price_list_header_id => l_est_detail_rec.price_list_id,
p_line_number => l_line_num,
p_inventory_item_id => l_est_detail_rec.inventory_item_id_in,
p_item_revision => l_est_detail_rec.item_revision,
p_SERIAL_NUMBER => l_EST_DETAIL_rec.SERIAL_NUMBER,
p_quantity_required => l_est_detail_rec.quantity_required,
p_unit_of_measure_code => l_est_detail_rec.unit_of_measure_code,
p_selling_price => l_est_detail_rec.selling_price,
p_after_warranty_cost => l_est_detail_rec.after_warranty_cost,
p_business_process_id => l_est_detail_rec.business_process_id,
p_transaction_type_id => l_est_detail_rec.transaction_type_id,
p_customer_product_id => l_est_detail_rec.customer_product_id,
p_order_header_id => l_est_detail_rec.order_header_id,
p_installed_cp_return_by_date => l_est_detail_rec.installed_cp_return_by_date,
p_new_cp_return_by_date => l_est_detail_rec.new_cp_return_by_date,
p_interface_to_oe_flag => nvl(l_est_detail_rec.interface_to_oe_flag, 'N'),
p_rollup_flag => nvl(l_est_detail_rec.rollup_flag,'N'),
p_no_charge_flag => nvl(l_est_detail_rec.no_charge_flag,'N'),
p_add_to_order_flag => nvl(l_est_detail_rec.add_to_order_flag,'N'),
p_return_reason_code => l_est_detail_rec.return_reason_code,
p_generated_by_bca_engine_flag => nvl(l_est_detail_rec.generated_by_bca_engine,'N'),
p_transaction_inventory_org => l_est_detail_rec.transaction_inventory_org,
p_transaction_sub_inventory => l_est_detail_rec.transaction_sub_inventory,
p_charge_line_type => l_est_detail_rec.charge_line_type,
p_ship_to_account_id => l_est_detail_rec.ship_to_account_id,
p_invoice_to_account_id => l_est_detail_rec.bill_to_account_id,
p_ship_to_contact_id => l_est_detail_rec.ship_to_contact_id,
p_bill_to_contact_id => l_est_detail_rec.bill_to_contact_id,
p_list_price => l_est_detail_rec.list_price,
p_activity_start_date_time => l_est_detail_rec.activity_start_time,
p_activity_end_date_time => l_est_detail_rec.activity_end_time,
p_contract_discount_amount => l_est_detail_rec.contract_discount_amount,
p_bill_to_party_id => l_est_detail_rec.bill_to_party_id,
p_ship_to_party_id => l_est_detail_rec.ship_to_party_id,
p_pricing_context => l_est_detail_rec.pricing_context,
p_pricing_attribute1 => l_est_detail_rec.pricing_attribute1,
p_pricing_attribute2 => l_est_detail_rec.pricing_attribute2,
p_pricing_attribute3 => l_est_detail_rec.pricing_attribute3,
p_pricing_attribute4 => l_est_detail_rec.pricing_attribute4,
p_pricing_attribute5 => l_est_detail_rec.pricing_attribute5,
p_pricing_attribute6 => l_est_detail_rec.pricing_attribute6,
p_pricing_attribute7 => l_est_detail_rec.pricing_attribute7,
p_pricing_attribute8 => l_est_detail_rec.pricing_attribute8,
p_pricing_attribute9 => l_est_detail_rec.pricing_attribute9,
p_pricing_attribute10 => l_est_detail_rec.pricing_attribute10,
p_pricing_attribute11 => l_est_detail_rec.pricing_attribute11,
p_pricing_attribute12 => l_est_detail_rec.pricing_attribute12,
p_pricing_attribute13 => l_est_detail_rec.pricing_attribute13,
p_pricing_attribute14 => l_est_detail_rec.pricing_attribute14,
p_pricing_attribute15 => l_est_detail_rec.pricing_attribute15,
p_pricing_attribute16 => l_est_detail_rec.pricing_attribute16,
p_pricing_attribute17 => l_est_detail_rec.pricing_attribute17,
p_pricing_attribute18 => l_est_detail_rec.pricing_attribute18,
p_pricing_attribute19 => l_est_detail_rec.pricing_attribute19,
p_pricing_attribute20 => l_est_detail_rec.pricing_attribute20,
p_pricing_attribute21 => l_est_detail_rec.pricing_attribute21,
p_pricing_attribute22 => l_est_detail_rec.pricing_attribute22,
p_pricing_attribute23 => l_est_detail_rec.pricing_attribute23,
p_pricing_attribute24 => l_est_detail_rec.pricing_attribute24,
p_pricing_attribute25 => l_est_detail_rec.pricing_attribute25,
p_pricing_attribute26 => l_est_detail_rec.pricing_attribute26,
p_pricing_attribute27 => l_est_detail_rec.pricing_attribute27,
p_pricing_attribute28 => l_est_detail_rec.pricing_attribute28,
p_pricing_attribute29 => l_est_detail_rec.pricing_attribute29,
p_pricing_attribute30 => l_est_detail_rec.pricing_attribute30,
p_pricing_attribute31 => l_est_detail_rec.pricing_attribute31,
p_pricing_attribute32 => l_est_detail_rec.pricing_attribute32,
p_pricing_attribute33 => l_est_detail_rec.pricing_attribute33,
p_pricing_attribute34 => l_est_detail_rec.pricing_attribute34,
p_pricing_attribute35 => l_est_detail_rec.pricing_attribute35,
p_pricing_attribute36 => l_est_detail_rec.pricing_attribute36,
p_pricing_attribute37 => l_est_detail_rec.pricing_attribute37,
p_pricing_attribute38 => l_est_detail_rec.pricing_attribute38,
p_pricing_attribute39 => l_est_detail_rec.pricing_attribute39,
p_pricing_attribute40 => l_est_detail_rec.pricing_attribute40,
p_pricing_attribute41 => l_est_detail_rec.pricing_attribute41,
p_pricing_attribute42 => l_est_detail_rec.pricing_attribute42,
p_pricing_attribute43 => l_est_detail_rec.pricing_attribute43,
p_pricing_attribute44 => l_est_detail_rec.pricing_attribute44,
p_pricing_attribute45 => l_est_detail_rec.pricing_attribute45,
p_pricing_attribute46 => l_est_detail_rec.pricing_attribute46,
p_pricing_attribute47 => l_est_detail_rec.pricing_attribute47,
p_pricing_attribute48 => l_est_detail_rec.pricing_attribute48,
p_pricing_attribute49 => l_est_detail_rec.pricing_attribute49,
p_pricing_attribute50 => l_est_detail_rec.pricing_attribute50,
p_pricing_attribute51 => l_est_detail_rec.pricing_attribute51,
p_pricing_attribute52 => l_est_detail_rec.pricing_attribute52,
p_pricing_attribute53 => l_est_detail_rec.pricing_attribute53,
p_pricing_attribute54 => l_est_detail_rec.pricing_attribute54,
p_pricing_attribute55 => l_est_detail_rec.pricing_attribute55,
p_pricing_attribute56 => l_est_detail_rec.pricing_attribute56,
p_pricing_attribute57 => l_est_detail_rec.pricing_attribute57,
p_pricing_attribute58 => l_est_detail_rec.pricing_attribute58,
p_pricing_attribute59 => l_est_detail_rec.pricing_attribute59,
p_pricing_attribute60 => l_est_detail_rec.pricing_attribute60,
p_pricing_attribute61 => l_est_detail_rec.pricing_attribute61,
p_pricing_attribute62 => l_est_detail_rec.pricing_attribute62,
p_pricing_attribute63 => l_est_detail_rec.pricing_attribute63,
p_pricing_attribute64 => l_est_detail_rec.pricing_attribute64,
p_pricing_attribute65 => l_est_detail_rec.pricing_attribute65,
p_pricing_attribute66 => l_est_detail_rec.pricing_attribute66,
p_pricing_attribute67 => l_est_detail_rec.pricing_attribute67,
p_pricing_attribute68 => l_est_detail_rec.pricing_attribute68,
p_pricing_attribute69 => l_est_detail_rec.pricing_attribute69,
p_pricing_attribute70 => l_est_detail_rec.pricing_attribute70,
p_pricing_attribute71 => l_est_detail_rec.pricing_attribute71,
p_pricing_attribute72 => l_est_detail_rec.pricing_attribute72,
p_pricing_attribute73 => l_est_detail_rec.pricing_attribute73,
p_pricing_attribute74 => l_est_detail_rec.pricing_attribute74,
p_pricing_attribute75 => l_est_detail_rec.pricing_attribute75,
p_pricing_attribute76 => l_est_detail_rec.pricing_attribute76,
p_pricing_attribute77 => l_est_detail_rec.pricing_attribute77,
p_pricing_attribute78 => l_est_detail_rec.pricing_attribute78,
p_pricing_attribute79 => l_est_detail_rec.pricing_attribute79,
p_pricing_attribute80 => l_est_detail_rec.pricing_attribute80,
p_pricing_attribute81 => l_est_detail_rec.pricing_attribute81,
p_pricing_attribute82 => l_est_detail_rec.pricing_attribute82,
p_pricing_attribute83 => l_est_detail_rec.pricing_attribute83,
p_pricing_attribute84 => l_est_detail_rec.pricing_attribute84,
p_pricing_attribute85 => l_est_detail_rec.pricing_attribute85,
p_pricing_attribute86 => l_est_detail_rec.pricing_attribute86,
p_pricing_attribute87 => l_est_detail_rec.pricing_attribute87,
p_pricing_attribute88 => l_est_detail_rec.pricing_attribute88,
p_pricing_attribute89 => l_est_detail_rec.pricing_attribute89,
p_pricing_attribute90 => l_est_detail_rec.pricing_attribute90,
p_pricing_attribute91 => l_est_detail_rec.pricing_attribute91,
p_pricing_attribute92 => l_est_detail_rec.pricing_attribute92,
p_pricing_attribute93 => l_est_detail_rec.pricing_attribute93,
p_pricing_attribute94 => l_est_detail_rec.pricing_attribute94,
p_pricing_attribute95 => l_est_detail_rec.pricing_attribute95,
p_pricing_attribute96 => l_est_detail_rec.pricing_attribute96,
p_pricing_attribute97 => l_est_detail_rec.pricing_attribute97,
p_pricing_attribute98 => l_est_detail_rec.pricing_attribute98,
p_pricing_attribute99 => l_est_detail_rec.pricing_attribute99,
p_pricing_attribute100 => l_est_detail_rec.pricing_attribute100,
p_attribute1 => l_est_detail_rec.attribute1,
p_attribute2 => l_est_detail_rec.attribute2,
p_attribute3 => l_est_detail_rec.attribute3,
p_attribute4 => l_est_detail_rec.attribute4,
p_attribute5 => l_est_detail_rec.attribute5,
p_attribute6 => l_est_detail_rec.attribute6,
p_attribute7 => l_est_detail_rec.attribute7,
p_attribute8 => l_est_detail_rec.attribute8,
p_attribute9 => l_est_detail_rec.attribute9,
p_attribute10 => l_est_detail_rec.attribute10,
p_attribute11 => l_est_detail_rec.attribute11,
p_attribute12 => l_est_detail_rec.attribute12,
p_attribute13 => l_est_detail_rec.attribute13,
p_attribute14 => l_est_detail_rec.attribute14,
p_attribute15 => l_est_detail_rec.attribute15,
p_context => l_est_detail_rec.context,
p_coverage_bill_rate_id => l_est_detail_rec.coverage_bill_rate_id,
p_coverage_billing_type_id => null,
p_txn_billing_type_id => l_est_detail_rec.txn_billing_type_id,
p_submit_restriction_message => l_est_detail_rec.submit_restriction_message,
p_submit_error_message => l_est_detail_rec.submit_error_message,
p_submit_from_system => l_est_detail_rec.submit_from_system,
p_line_submitted => nvl(l_est_detail_rec.line_submitted_flag, 'N'),
p_last_update_date => sysdate,
--p_last_update_login => p_user_id,
p_last_update_login => p_login_id,
p_last_updated_by => p_user_id,
p_creation_date => sysdate,
p_created_by => p_user_id,
p_estimate_detail_id => l_ed_id,
x_object_version_number => x_object_version_number );
PROCEDURE Update_Charge_Details(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_object_version_number OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
--p_resp_appl_id IN NUMBER := NULL,
--p_resp_id IN NUMBER := NULL,
--p_user_id IN NUMBER := NULL,
p_resp_appl_id IN NUMBER := FND_GLOBAL.RESP_APPL_ID,
p_resp_id IN NUMBER := FND_GLOBAL.RESP_ID,
p_user_id IN NUMBER := FND_GLOBAL.USER_ID,
p_login_id IN NUMBER := NULL,
p_transaction_control IN VARCHAR2 := FND_API.G_TRUE,
p_est_detail_rec IN CS_Charge_Details_PUB.Charges_Rec_Type
) IS
l_api_version NUMBER := 1.0 ;
l_api_name VARCHAR2(30) := 'Update_Charge_Details' ;
l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'update_charge_details';
SAVEPOINT Update_Charge_Details_PVT;
'Update_Charge_Details',
'B','C') THEN
CS_CHARGE_DETAILS_CUHK.Update_Charge_Details_Pre(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_object_version_number => x_object_version_number,
x_estimate_detail_id => l_ed_id,
x_msg_data => x_msg_data,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_transaction_control => p_transaction_control,
p_est_detail_rec => p_est_detail_rec);
'Update_Charge_Details',
'B', 'V') THEN
CS_CHARGE_DETAILS_VUHK.Update_Charge_Details_Pre(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_object_version_number => x_object_version_number,
x_estimate_detail_id => l_ed_id,
x_msg_data => x_msg_data,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_transaction_control => p_transaction_control,
p_est_detail_rec => p_est_detail_rec);
CS_ESTIMATE_DETAILS_PKG.Update_Row(
x_object_version_number => x_object_version_number,
p_ESTIMATE_DETAIL_ID => l_est_detail_rec.ESTIMATE_DETAIL_ID,
p_ORG_ID => l_est_detail_rec.org_id,
p_INCIDENT_ID => l_est_detail_rec.INCIDENT_ID,
p_ORIGINAL_SOURCE_ID => l_est_detail_rec.ORIGINAL_SOURCE_ID,
p_ORIGINAL_SOURCE_CODE => l_est_detail_rec.ORIGINAL_SOURCE_CODE,
p_SOURCE_ID => l_est_detail_rec.SOURCE_ID,
p_SOURCE_CODE => l_est_detail_rec.SOURCE_CODE,
p_contract_line_id => l_est_detail_rec.contract_line_id,
p_rate_type_code => l_est_detail_rec.rate_type_code,
p_contract_id => l_est_detail_rec.CONTRACT_ID,
p_coverage_id => null,
p_coverage_txn_group_id => null,
--p_EXCEPTION_COVERAGE_USED => l_est_detail_rec.EXCEPTION_COVERAGE_USED,
p_CURRENCY_CODE => l_est_detail_rec.currency_code,
p_CONVERSION_RATE => NULL,
p_CONVERSION_TYPE_CODE => NULL,
p_CONVERSION_RATE_DATE => NULL,
p_INVOICE_TO_ORG_ID => l_est_detail_rec.INVOICE_TO_ORG_ID,
p_SHIP_TO_ORG_ID => l_est_detail_rec.SHIP_TO_ORG_ID,
p_PURCHASE_ORDER_NUM => l_est_detail_rec.PURCHASE_ORDER_NUM,
p_ORDER_LINE_ID => l_est_detail_rec.ORDER_LINE_ID,
p_LINE_TYPE_ID => l_est_detail_rec.LINE_TYPE_ID,
p_LINE_CATEGORY_CODE => l_est_detail_rec.LINE_CATEGORY_CODE,
p_PRICE_LIST_HEADER_ID => l_est_detail_rec.PRICE_LIST_ID,
p_LINE_NUMBER => l_est_detail_rec.line_number,
p_INVENTORY_ITEM_ID => l_est_detail_rec.INVENTORY_ITEM_ID_in,
p_ITEM_REVISION => l_est_detail_rec.ITEM_REVISION,
p_SERIAL_NUMBER => l_est_detail_rec.SERIAL_NUMBER,
p_QUANTITY_REQUIRED => l_est_detail_rec.QUANTITY_REQUIRED,
p_UNIT_OF_MEASURE_CODE => l_est_detail_rec.UNIT_OF_MEASURE_CODE,
p_SELLING_PRICE => l_est_detail_rec.SELLING_PRICE,
p_AFTER_WARRANTY_COST => l_est_detail_rec.AFTER_WARRANTY_COST,
--p_FUNC_CURR_AFT_WARR_COST => l_est_detail_rec.FUNC_CURR_AFT_WARR_COST,
p_BUSINESS_PROCESS_ID => l_est_detail_rec.BUSINESS_PROCESS_ID,
p_TRANSACTION_TYPE_ID => l_est_detail_rec.TRANSACTION_TYPE_ID,
p_CUSTOMER_PRODUCT_ID => l_est_detail_rec.CUSTOMER_PRODUCT_ID,
p_ORDER_HEADER_ID => l_est_detail_rec.ORDER_HEADER_ID,
p_INSTALLED_CP_RETURN_BY_DATE => l_est_detail_rec.INSTALLED_CP_RETURN_BY_DATE,
p_NEW_CP_RETURN_BY_DATE => l_est_detail_rec.NEW_CP_RETURN_BY_DATE,
p_INTERFACE_TO_OE_FLAG => nvl(l_est_detail_rec.INTERFACE_TO_OE_FLAG, 'N'),
p_ROLLUP_FLAG => nvl(l_est_detail_rec.ROLLUP_FLAG, 'N'),
p_no_charge_flag => nvl(l_est_detail_rec.NO_CHARGE_FLAG, 'N'),
p_ADD_TO_ORDER_FLAG => nvl(l_est_detail_rec.ADD_TO_ORDER_FLAG, 'N'),
p_RETURN_REASON_CODE => l_est_detail_rec.RETURN_REASON_CODE,
p_GENERATED_BY_BCA_ENGINE_FLAG => nvl(l_est_detail_rec.GENERATED_BY_BCA_ENGINE, 'N'),
p_TRANSACTION_INVENTORY_ORG => l_est_detail_rec.TRANSACTION_INVENTORY_ORG,
p_TRANSACTION_SUB_INVENTORY => l_est_detail_rec.TRANSACTION_SUB_INVENTORY,
p_CHARGE_LINE_TYPE => l_est_detail_rec.CHARGE_LINE_TYPE,
p_SHIP_TO_ACCOUNT_ID => l_est_detail_rec.SHIP_TO_ACCOUNT_ID,
p_INVOICE_TO_ACCOUNT_ID => l_est_detail_rec.BILL_TO_ACCOUNT_ID,
p_SHIP_TO_CONTACT_ID => l_est_detail_rec.SHIP_TO_CONTACT_ID,
p_BILL_TO_CONTACT_ID => l_est_detail_rec.BILL_TO_CONTACT_ID,
p_LIST_PRICE => l_est_detail_rec.LIST_PRICE,--mviswana
p_ACTIVITY_START_DATE_TIME => l_est_detail_rec.ACTIVITY_START_TIME,
p_ACTIVITY_END_DATE_TIME => l_est_detail_rec.ACTIVITY_END_TIME,
p_CONTRACT_DISCOUNT_AMOUNT => l_est_detail_rec.CONTRACT_DISCOUNT_AMOUNT,
p_BILL_TO_PARTY_ID => l_est_detail_rec.BILL_TO_PARTY_ID,
p_SHIP_TO_PARTY_ID => l_est_detail_rec.SHIP_TO_PARTY_ID,
--p_tax_code => l_est_detail_rec.tax_code,
--p_est_tax_amount => l_est_detail_rec.est_tax_amount,
p_PRICING_CONTEXT => l_est_detail_rec.PRICING_CONTEXT,
p_PRICING_ATTRIBUTE1 => l_est_detail_rec.PRICING_ATTRIBUTE1,
p_PRICING_ATTRIBUTE2 => l_est_detail_rec.PRICING_ATTRIBUTE2,
p_PRICING_ATTRIBUTE3 => l_est_detail_rec.PRICING_ATTRIBUTE3,
p_PRICING_ATTRIBUTE4 => l_est_detail_rec.PRICING_ATTRIBUTE4,
p_PRICING_ATTRIBUTE5 => l_est_detail_rec.PRICING_ATTRIBUTE5,
p_PRICING_ATTRIBUTE6 => l_est_detail_rec.PRICING_ATTRIBUTE6,
p_PRICING_ATTRIBUTE7 => l_est_detail_rec.PRICING_ATTRIBUTE7,
p_PRICING_ATTRIBUTE8 => l_est_detail_rec.PRICING_ATTRIBUTE8,
p_PRICING_ATTRIBUTE9 => l_est_detail_rec.PRICING_ATTRIBUTE9,
p_PRICING_ATTRIBUTE10 => l_est_detail_rec.PRICING_ATTRIBUTE10,
p_PRICING_ATTRIBUTE11 => l_est_detail_rec.PRICING_ATTRIBUTE11,
p_PRICING_ATTRIBUTE12 => l_est_detail_rec.PRICING_ATTRIBUTE12,
p_PRICING_ATTRIBUTE13 => l_est_detail_rec.PRICING_ATTRIBUTE13,
p_PRICING_ATTRIBUTE14 => l_est_detail_rec.PRICING_ATTRIBUTE14,
p_PRICING_ATTRIBUTE15 => l_est_detail_rec.PRICING_ATTRIBUTE15,
p_PRICING_ATTRIBUTE16 => l_est_detail_rec.PRICING_ATTRIBUTE16,
p_PRICING_ATTRIBUTE17 => l_est_detail_rec.PRICING_ATTRIBUTE17,
p_PRICING_ATTRIBUTE18 => l_est_detail_rec.PRICING_ATTRIBUTE18,
p_PRICING_ATTRIBUTE19 => l_est_detail_rec.PRICING_ATTRIBUTE19,
p_PRICING_ATTRIBUTE20 => l_est_detail_rec.PRICING_ATTRIBUTE20,
p_PRICING_ATTRIBUTE21 => l_est_detail_rec.PRICING_ATTRIBUTE21,
p_PRICING_ATTRIBUTE22 => l_est_detail_rec.PRICING_ATTRIBUTE22,
p_PRICING_ATTRIBUTE23 => l_est_detail_rec.PRICING_ATTRIBUTE23,
p_PRICING_ATTRIBUTE24 => l_est_detail_rec.PRICING_ATTRIBUTE24,
p_pricing_attribute25 => l_est_detail_rec.pricing_attribute25,
p_pricing_attribute26 => l_est_detail_rec.pricing_attribute26,
p_pricing_attribute27 => l_est_detail_rec.pricing_attribute27,
p_pricing_attribute28 => l_est_detail_rec.pricing_attribute28,
p_pricing_attribute29 => l_est_detail_rec.pricing_attribute29,
p_pricing_attribute30 => l_est_detail_rec.pricing_attribute30,
p_pricing_attribute31 => l_est_detail_rec.pricing_attribute31,
p_pricing_attribute32 => l_est_detail_rec.pricing_attribute32,
p_pricing_attribute33 => l_est_detail_rec.pricing_attribute33,
p_pricing_attribute34 => l_est_detail_rec.pricing_attribute34,
p_pricing_attribute35 => l_est_detail_rec.pricing_attribute35,
p_pricing_attribute36 => l_est_detail_rec.pricing_attribute36,
p_pricing_attribute37 => l_est_detail_rec.pricing_attribute37,
p_pricing_attribute38 => l_est_detail_rec.pricing_attribute38,
p_pricing_attribute39 => l_est_detail_rec.pricing_attribute39,
p_pricing_attribute40 => l_est_detail_rec.pricing_attribute40,
p_pricing_attribute41 => l_est_detail_rec.pricing_attribute41,
p_pricing_attribute42 => l_est_detail_rec.pricing_attribute42,
p_pricing_attribute43 => l_est_detail_rec.pricing_attribute43,
p_pricing_attribute44 => l_est_detail_rec.pricing_attribute44,
p_pricing_attribute45 => l_est_detail_rec.pricing_attribute45,
p_pricing_attribute46 => l_est_detail_rec.pricing_attribute46,
p_pricing_attribute47 => l_est_detail_rec.pricing_attribute47,
p_pricing_attribute48 => l_est_detail_rec.pricing_attribute48,
p_pricing_attribute49 => l_est_detail_rec.pricing_attribute49,
p_pricing_attribute50 => l_est_detail_rec.pricing_attribute50,
p_pricing_attribute51 => l_est_detail_rec.pricing_attribute51,
p_pricing_attribute52 => l_est_detail_rec.pricing_attribute52,
p_pricing_attribute53 => l_est_detail_rec.pricing_attribute53,
p_pricing_attribute54 => l_est_detail_rec.pricing_attribute54,
p_pricing_attribute55 => l_est_detail_rec.pricing_attribute55,
p_pricing_attribute56 => l_est_detail_rec.pricing_attribute56,
p_pricing_attribute57 => l_est_detail_rec.pricing_attribute57,
p_pricing_attribute58 => l_est_detail_rec.pricing_attribute58,
p_pricing_attribute59 => l_est_detail_rec.pricing_attribute59,
p_pricing_attribute60 => l_est_detail_rec.pricing_attribute60,
p_pricing_attribute61 => l_est_detail_rec.pricing_attribute61,
p_pricing_attribute62 => l_est_detail_rec.pricing_attribute62,
p_pricing_attribute63 => l_est_detail_rec.pricing_attribute63,
p_pricing_attribute64 => l_est_detail_rec.pricing_attribute64,
p_pricing_attribute65 => l_est_detail_rec.pricing_attribute65,
p_pricing_attribute66 => l_est_detail_rec.pricing_attribute66,
p_pricing_attribute67 => l_est_detail_rec.pricing_attribute67,
p_pricing_attribute68 => l_est_detail_rec.pricing_attribute68,
p_pricing_attribute69 => l_est_detail_rec.pricing_attribute69,
p_pricing_attribute70 => l_est_detail_rec.pricing_attribute70,
p_pricing_attribute71 => l_est_detail_rec.pricing_attribute71,
p_pricing_attribute72 => l_est_detail_rec.pricing_attribute72,
p_pricing_attribute73 => l_est_detail_rec.pricing_attribute73,
p_pricing_attribute74 => l_est_detail_rec.pricing_attribute74,
p_pricing_attribute75 => l_est_detail_rec.pricing_attribute75,
p_pricing_attribute76 => l_est_detail_rec.pricing_attribute76,
p_pricing_attribute77 => l_est_detail_rec.pricing_attribute77,
p_pricing_attribute78 => l_est_detail_rec.pricing_attribute78,
p_pricing_attribute79 => l_est_detail_rec.pricing_attribute79,
p_pricing_attribute80 => l_est_detail_rec.pricing_attribute80,
p_pricing_attribute81 => l_est_detail_rec.pricing_attribute81,
p_pricing_attribute82 => l_est_detail_rec.pricing_attribute82,
p_pricing_attribute83 => l_est_detail_rec.pricing_attribute83,
p_pricing_attribute84 => l_est_detail_rec.pricing_attribute84,
p_pricing_attribute85 => l_est_detail_rec.pricing_attribute85,
p_pricing_attribute86 => l_est_detail_rec.pricing_attribute86,
p_pricing_attribute87 => l_est_detail_rec.pricing_attribute87,
p_pricing_attribute88 => l_est_detail_rec.pricing_attribute88,
p_pricing_attribute89 => l_est_detail_rec.pricing_attribute89,
p_pricing_attribute90 => l_est_detail_rec.pricing_attribute90,
p_pricing_attribute91 => l_est_detail_rec.pricing_attribute91,
p_pricing_attribute92 => l_est_detail_rec.pricing_attribute92,
p_pricing_attribute93 => l_est_detail_rec.pricing_attribute93,
p_pricing_attribute94 => l_est_detail_rec.pricing_attribute94,
p_pricing_attribute95 => l_est_detail_rec.pricing_attribute95,
p_pricing_attribute96 => l_est_detail_rec.pricing_attribute96,
p_pricing_attribute97 => l_est_detail_rec.pricing_attribute97,
p_pricing_attribute98 => l_est_detail_rec.pricing_attribute98,
p_pricing_attribute99 => l_est_detail_rec.pricing_attribute99,
p_pricing_attribute100 => l_est_detail_rec.pricing_attribute100,
p_attribute1 => l_est_detail_rec.attribute1,
p_attribute2 => l_est_detail_rec.attribute2,
p_attribute3 => l_est_detail_rec.attribute3,
p_attribute4 => l_est_detail_rec.attribute4,
p_attribute5 => l_est_detail_rec.attribute5,
p_attribute6 => l_est_detail_rec.attribute6,
p_attribute7 => l_est_detail_rec.attribute7,
p_attribute8 => l_est_detail_rec.attribute8,
p_attribute9 => l_est_detail_rec.attribute9,
p_attribute10 => l_est_detail_rec.attribute10,
p_attribute11 => l_est_detail_rec.attribute11,
p_attribute12 => l_est_detail_rec.attribute12,
p_attribute13 => l_est_detail_rec.attribute13,
p_attribute14 => l_est_detail_rec.attribute14,
p_attribute15 => l_est_detail_rec.attribute15,
p_context => l_est_detail_rec.context,
--p_organization_id => l_est_detail_rec.organization_id,
p_coverage_bill_rate_id => l_est_detail_rec.coverage_bill_rate_id,
p_coverage_billing_type_id => null,
p_txn_billing_type_id => l_est_detail_rec.txn_billing_type_id,
p_submit_restriction_message => l_est_detail_rec.submit_restriction_message,
p_submit_error_message => l_est_detail_rec.submit_error_message,
p_submit_from_system => l_est_detail_rec.submit_from_system,
p_line_submitted => nvl(l_est_detail_rec.line_submitted_flag, 'N'),
p_last_update_date => sysdate,
p_last_update_login => p_login_id,
p_last_updated_by => p_user_id,
p_creation_date => sysdate,
p_created_by => p_user_id
);
'Update_Charge_Details',
'A','C') THEN
CS_CHARGE_DETAILS_CUHK.Update_Charge_Details_Post(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_object_version_number => x_object_version_number,
x_estimate_detail_id => l_ed_id,
x_msg_data => x_msg_data,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_transaction_control => p_transaction_control,
p_est_detail_rec => p_est_detail_rec);
'Update_Charge_Details',
'A', 'V') THEN
CS_CHARGE_DETAILS_VUHK.Update_Charge_Details_Post(
p_api_version => l_api_version,
p_init_msg_list => FND_API.G_FALSE,
p_commit => p_commit,
p_validation_level => p_validation_level,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_object_version_number => x_object_version_number,
x_estimate_detail_id => l_ed_id,
x_msg_data => x_msg_data,
p_resp_appl_id => p_resp_appl_id,
p_resp_id => p_resp_id,
p_user_id => p_user_id,
p_login_id => p_login_id,
p_transaction_control => p_transaction_control,
p_est_detail_rec => p_est_detail_rec);
ROLLBACK TO Update_Charge_Details_PVT;
ROLLBACK TO Update_Charge_Details_PVT;
ROLLBACK TO Update_Charge_Details_PVT;
END Update_Charge_Details;
Procedure Delete_Charge_Details(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_transaction_control IN VARCHAR2 := FND_API.G_TRUE,
p_estimate_detail_id IN NUMBER := NULL) AS
l_api_name CONSTANT VARCHAR2(30) := 'Delete_Charge_Details' ;
l_prog_name CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||'delete_charge_details';
SAVEPOINT Delete_Charge_Details_PUB ;
Cannot_Delete_Line_Msg(l_api_name_full) ;
Cannot_Delete_Line_Msg(l_api_name_full) ;
DELETE FROM
CS_ESTIMATE_DETAILS
WHERE ESTIMATE_DETAIL_ID = p_estimate_detail_id ;
ROLLBACK TO Delete_Charge_Details_PUB;
ROLLBACK TO Delete_Charge_Details_PUB;
ROLLBACK TO Delete_Charge_Details_PUB;
END Delete_Charge_Details; -- End of Procedure Delete Charge Details
select *
from cs_estimate_details
where estimate_detail_id = p_estimate_detail_id;
l_update_ib_flag VARCHAR2(1);
l_update_org VARCHAR2(10);
l_disallow_charge_update VARCHAR2(1);
SELECT src_change_owner
FROM CSI_IB_TXN_TYPES
WHERE cs_transaction_type_id = p_txn_billing_type_id;
SELECT action_code
FROM csd_product_transactions
WHERE estimate_detail_id = p_estimate_detail_id;
/* Select action_code
from csd_product_txns_v
where estimate_detail_id = p_estimate_detail_id; */
SELECT serial_number
FROM csi_item_instances
WHERE instance_id = p_instance_id;
x_disallow_charge_update => l_disallow_charge_update,
x_msg_data => l_msg_data,
x_msg_count => l_msg_count,
x_return_status => l_return_status
);
FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_INSERT');
IF l_disallow_charge_update = 'Y' THEN
--raise error
FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_UPDATE');
FND_MESSAGE.Set_Name('CS', 'CS_CHG_CANNOT_UPDATE_CHRG_LINE');
FND_MESSAGE.Set_Name('CS', 'CS_CHG_CANNOT_UPDATE_CHRG_LINE');
FND_MESSAGE.Set_Name('CS', 'CS_CHG_CANNOT_UPDATE_CHRG_LINE');
FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_UPDATE_INPROG');
FND_MESSAGE.SET_NAME('CS', 'CS_CHG_CANNOT_UPDATE_INPROG');
X_UPDATE_IB_FLAG => l_update_ib_flag,
X_SRC_REFERENCE_REQD_FLAG => l_src_reference_reqd_flag,
X_SRC_RETURN_REQD_FLAG => l_src_return_reqd_flag,
X_NON_SRC_REFERENCE_REQD => l_non_src_reference_reqd_flag ,
X_NON_SRC_RETURN_REQD => l_non_src_return_reqd,
x_MSG_DATA => x_msg_data,
x_MSG_COUNT => x_msg_count,
X_RETURN_STATUS => l_return_status );
X_UPDATE_IB_FLAG => l_update_ib_flag,
X_SRC_REFERENCE_REQD_FLAG => l_src_reference_reqd_flag,
X_SRC_RETURN_REQD_FLAG => l_src_return_reqd_flag,
X_NON_SRC_REFERENCE_REQD => l_non_src_reference_reqd_flag ,
X_NON_SRC_RETURN_REQD => l_non_src_return_reqd,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status );
X_UPDATE_IB_FLAG => l_update_ib_flag,
X_SRC_REFERENCE_REQD_FLAG => l_src_reference_reqd_flag,
X_SRC_RETURN_REQD_FLAG => l_src_return_reqd_flag,
X_NON_SRC_REFERENCE_REQD => l_non_src_reference_reqd_flag ,
X_NON_SRC_RETURN_REQD => l_non_src_return_reqd,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status );
X_UPDATE_IB_FLAG => l_update_ib_flag,
X_SRC_REFERENCE_REQD_FLAG => l_src_reference_reqd_flag,
X_SRC_RETURN_REQD_FLAG => l_src_return_reqd_flag,
X_NON_SRC_REFERENCE_REQD => l_non_src_reference_reqd_flag ,
X_NON_SRC_RETURN_REQD => l_non_src_return_reqd,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status );
X_UPDATE_IB_FLAG => l_update_ib_flag,
X_SRC_REFERENCE_REQD_FLAG => l_src_reference_reqd_flag,
X_SRC_RETURN_REQD_FLAG => l_src_return_reqd_flag,
X_NON_SRC_REFERENCE_REQD => l_non_src_reference_reqd_flag ,
X_NON_SRC_RETURN_REQD => l_non_src_return_reqd,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status );
P_UPDATE_IB_FLAG => l_update_ib_flag,
X_COMMS_TRACKABLE_FLAG=> l_comms_trackable_flag,
X_SERIAL_CONTROL_FLAG => l_serial_control_flag,
X_REV_CONTROL_FLAG => l_rev_control_flag,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status);
l_update_ib_flag = 'Y' AND
l_line_order_category_code = 'RETURN' THEN
--DBMS_OUTPUT.PUT_LINE('Cannot change item');
Cant_Update_Detail_Param_Msg(l_api_name_full,
'INVENTORY_ITEM_ID',
to_char(p_charges_DETAIL_rec.inventory_item_id_in));
P_UPDATE_IB_FLAG => l_update_ib_flag,
X_COMMS_TRACKABLE_FLAG => l_comms_trackable_flag,
X_SERIAL_CONTROL_FLAG => l_serial_control_flag,
X_REV_CONTROL_FLAG => l_rev_control_flag,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status);
P_UPDATE_IB_FLAG => l_update_ib_flag,
X_COMMS_TRACKABLE_FLAG => l_comms_trackable_flag,
X_SERIAL_CONTROL_FLAG => l_serial_control_flag,
X_REV_CONTROL_FLAG => l_rev_control_flag,
X_MSG_DATA => l_msg_data,
X_MSG_COUNT => l_msg_count,
X_RETURN_STATUS => l_return_status);
SELECT internal_party_id into l_internal_party_id
FROM csi_install_parameters WHERE rownum = 1;
SELECT internal_party_id into l_internal_party_id
FROM csi_install_parameters WHERE rownum = 1;
--select internal_party_id from csi_install_parameters,
SELECT internal_party_id into l_internal_party_id
FROM csi_install_parameters;
SELECT internal_party_id into l_internal_party_id
FROM csi_install_parameters;
Cant_Update_Detail_Param_Msg(l_api_name_full,
'QUANTITY_REQUIRED',
to_char(p_charges_detail_rec.quantity_required));
SELECT intp.business_process_id,
inc.customer_id,
inc.customer_site_id,
inc.contract_id,
inc.contract_service_id,
inc.customer_po_number,
inc.customer_product_id,
inc.system_id, -- Fix bug
inc.inventory_item_id, -- Fix bug
inc.account_id,
inc.incident_date,
inc.creation_date,
substr(hza.account_name,1,30),
NVL(inc.bill_to_party_id, -999) bill_to_party_id,
NVL(inc.ship_to_party_id, -999) ship_to_party_id,
NVL(inc.bill_to_site_id,-999),bill_to_site_id,
NVL(inc.ship_to_site_id,-999)ship_to_site_id,
NVL(inc.bill_to_account_id,-999) bill_to_account_id,
NVL(inc.ship_to_account_id,-999) ship_to_account_id,
NVL(inc.bill_to_contact_id,-999) bill_to_contact_id,
NVL(inc.ship_to_contact_id,-999) ship_to_contact_id,
inc.caller_type
FROM cs_incidents_all_b inc,
CS_INCIDENT_TYPES intp,
CS_BUSINESS_PROCESSES bp,
hz_parties hzp,
hz_cust_accounts hza
WHERE inc.incident_id = p_incident_id
AND inc.incident_type_id = intp.incident_type_id
AND intp.business_process_id = bp.business_process_id
AND inc.customer_id = hzp.party_id
AND inc.account_id = hza.cust_account_id(+);
X_UPDATE_IB_FLAG OUT NOCOPY VARCHAR2,
X_SRC_REFERENCE_REQD_FLAG OUT NOCOPY VARCHAR2,
X_SRC_RETURN_REQD_FLAG OUT NOCOPY VARCHAR2,
X_NON_SRC_REFERENCE_REQD OUT NOCOPY VARCHAR2,
X_NON_SRC_RETURN_REQD OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
Cursor c_txn_type(p_txn_type_id IN NUMBER,
p_business_process_id IN NUMBER) IS
SELECT tt.transaction_type_id,
NVL(citt.src_reference_reqd,'N') src_reference_reqd,
NVL(citt.src_return_reqd,'N') src_return_reqd,
citt.src_change_owner_to_code,
NVL(citt.non_src_reference_reqd,'N') non_src_reference_reqd,
NVL(citt.non_src_return_reqd,'N') non_src_return_reqd,
citt.non_src_change_owner_to_code,
NVL(csit.update_ib_flag,'N') update_ib_flag,
nvl(tt.no_charge_flag, 'N') no_charge_flag,
nvl(tt.interface_to_oe_flag, 'N')interface_to_oe_flag,
tt.line_order_category_code,
ol.meaning line_category_meaning
FROM CS_TRANSACTION_TYPES_VL tt,
CS_BUS_PROCESS_TXNS bt,
csi_ib_txn_types citt,
csi_source_ib_types csit,
CSI_TXN_TYPES ctt,
OE_LOOKUPS ol
WHERE tt.transaction_type_id = p_txn_type_id
and bt.business_process_id = p_business_process_id
and bt.transaction_type_id = tt.transaction_type_id
and tt.line_order_category_code is not null
and ol.lookup_code = tt.line_order_category_code
and ol.lookup_type = 'LINE_CATEGORY'
and tt.transaction_type_id = citt.cs_transaction_type_id (+)
and nvl(citt.parent_reference_reqd, 'N') = 'N'
and nvl(ctt.source_transaction_type, 'OM_SHIPMENT') =
decode(tt.line_order_category_code, 'ORDER', 'OM_SHIPMENT',
nvl(ctt.source_transaction_type, 'OM_SHIPMENT'))
and nvl(ctt.source_transaction_type, 'RMA_RECEIPT') =
decode(tt.line_order_category_code, 'RETURN', 'RMA_RECEIPT',
nvl(ctt.source_transaction_type, 'RMA_RECEIPT'))
and citt.sub_type_id = csit.sub_type_id (+)
and csit.transaction_type_id = ctt.transaction_type_id (+)
and nvl(ctt.source_application_id, 660) = 660
and trunc(sysdate) between trunc(nvl(bt.start_date_active, sysdate))
and trunc(nvl(bt.end_date_active, sysdate));
SELECT tt.transaction_type_id,
nvl(tt.no_charge_flag, 'N') no_charge_flag,
nvl(tt.interface_to_oe_flag, 'N')interface_to_oe_flag,
tt.line_order_category_code,
ol.meaning line_category_meaning
FROM CS_TRANSACTION_TYPES_VL tt,
CS_BUS_PROCESS_TXNS bt,
OE_LOOKUPS ol
WHERE tt.transaction_type_id = p_txn_type_id
and bt.business_process_id = p_business_process_id
and bt.transaction_type_id = tt.transaction_type_id
and tt.line_order_category_code is not null
and ol.lookup_code = tt.line_order_category_code
and ol.lookup_type = 'LINE_CATEGORY'
and trunc(sysdate) between trunc(nvl(bt.start_date_active, sysdate))
and trunc(nvl(bt.end_date_active, sysdate));
x_update_ib_flag := v_txn_type.update_ib_flag;
P_UPDATE_IB_FLAG IN VARCHAR2,
X_COMMS_TRACKABLE_FLAG OUT NOCOPY VARCHAR2,
X_SERIAL_CONTROL_FLAG OUT NOCOPY VARCHAR2,
X_REV_CONTROL_FLAG OUT NOCOPY VARCHAR2,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2) IS
Cursor c_get_inv_item(p_inv_id NUMBER) IS
SELECT inventory_item_id,
serial_number_control_code,
revision_qty_control_code,
nvl(comms_nl_trackable_flag, 'N') comms_nl_trackable_flag
-- contract_item_type_code -- Fix for Bug # 3109160
FROM MTL_SYSTEM_ITEMS_KFV
WHERE organization_id = cs_std.get_item_valdn_orgzn_id
AND inventory_item_id = p_inv_id;
SELECT bc.billing_category
FROM cs_billing_type_categories bc,
cs_txn_billing_types bt
WHERE sysdate between nvl(bc.start_date_active,sysdate)
AND nvl(bc.end_date_active,sysdate)
AND sysdate between nvl(bt.start_date_active,sysdate)
AND nvl(bt.end_date_active,sysdate)
AND bt.billing_type = bc.billing_type
AND bt.transaction_type_id = p_txn_type_id
AND bc.billing_type IN (SELECT material_billable_flag
FROM MTL_SYSTEM_ITEMS_KFV
WHERE organization_id = cs_std.get_item_valdn_orgzn_id
AND inventory_item_id = p_inv_id);
SELECT ctbt.txn_billing_type_id
FROM mtl_system_items_kfv kfv,
cs_txn_billing_types ctbt
WHERE kfv.inventory_item_id = p_inventory_item_id
AND organization_id = cs_std.get_item_valdn_orgzn_id --
AND ctbt.transaction_type_id = p_txn_type_id
AND ctbt.billing_type = kfv.material_billable_flag;
SELECT uom_code
FROM MTL_ITEM_UOMS_VIEW
WHERE inventory_item_id = P_INV_ID AND
organization_id = cs_std.get_item_valdn_orgzn_id;
SELECT mum.uom_code
FROM mtl_system_items_b msi,
MTL_UNITS_OF_MEASURE_TL mum
WHERE msi.PRIMARY_UNIT_OF_MEASURE = mum.unit_of_measure
AND msi.INVENTORY_ITEM_ID = P_INV_ID
AND msi.organization_id = cs_std.get_item_valdn_orgzn_id;
SELECT incident_id
FROM CS_INCIDENTS_ALL_b
WHERE incident_id = p_source_id;
SELECT repair_line_id
FROM CSD_REPAIRS
WHERE repair_line_id = p_source_id;
SELECT debrief_line_id
FROM csf_debrief_lines
WHERE debrief_line_id = p_source_id;
SELECT site_use.party_site_id
FROM HZ_PARTY_SITE_USES site_use,
HZ_PARTY_SITES site,
HZ_PARTIES party
WHERE site_use.party_site_use_id = p_site_use_id
AND party.party_id = p_party_id
AND nvl(site_use.status,'A') = 'A'
AND site_use.site_use_type = 'BILL_TO'
AND site_use.party_site_id = site.party_site_id
AND site.party_id = party.party_id;
SELECT site_use.party_site_id
FROM HZ_PARTY_SITE_USES site_use,
HZ_PARTY_SITES site,
HZ_PARTIES party
WHERE site_use.party_site_use_id = p_site_use_id
AND party.party_id = p_party_id
AND nvl(site_use.status,'A') = 'A'
AND site_use.site_use_type = 'SHIP_TO'
AND site_use.party_site_id = site.party_site_id
AND site.party_id = party.party_id;
select currency_code
from qp_price_lists_v
where price_list_id = p_price_list_id;
SELECT currency_code
FROM qp_price_lists_v
WHERE price_list_id = p_price_list_id;
SELECT cov.contract_id
--cov.coverage_line_id,
--cov.coverage_name,
--ent.txn_group_id
FROM oks_ent_line_details_v cov,
oks_ent_txn_groups_v ent
WHERE cov.service_line_id = p_service_line_id
AND cov.coverage_line_id = ent.coverage_id
AND ent.business_process_id = p_business_process_id;
SELECT *
INTO x_charge_detail_rec
FROM CS_ESTIMATE_DETAILS
WHERE ESTIMATE_DETAIL_ID = p_estimate_detail_id
FOR UPDATE OF ESTIMATE_DETAIL_ID NOWAIT ;
SELECT order_line_id,
GENERATED_BY_BCA_ENGINE_FLAG,
Charge_line_type
INTO x_order_line_id,
x_gen_bca_flag,
x_charge_line_type
FROM CS_ESTIMATE_DETAILS
WHERE estimate_detail_id = p_estimate_detail_id
FOR UPDATE OF ESTIMATE_DETAIL_ID NOWAIT ;
SELECT organization_id
FROM hr_operating_units
WHERE organization_id = p_org_id;
PROCEDURE Cannot_Delete_Line_Msg
( p_token_an IN VARCHAR2
)
IS
BEGIN
FND_MESSAGE.Set_Name('CS', 'CS_API_CHG_CANT_DELETE_DET');
PROCEDURE Cant_Update_Detail_Param_Msg
( p_token_an IN VARCHAR2,
p_token_cn IN VARCHAR2,
p_token_v IN VARCHAR2
) IS
BEGIN
FND_MESSAGE.Set_Name('CS', 'CS_API_CHG_CANT_UPD_DET_PARAM');
SELECT 1
FROM fnd_user
WHERE user_id = p_user_id
AND TRUNC(SYSDATE) <= start_date
AND NVL(end_date, SYSDATE) >= SYSDATE;
SELECT 1
FROM fnd_logins
WHERE login_id = p_login_id
AND user_id = p_user_id;
SELECT 'x' into l_dummy
FROM fnd_user
WHERE user_id = p_user_id;
SELECT 'x' into l_dummy
FROM fnd_user
WHERE user_id = p_user_id
AND trunc(sysdate) BETWEEN trunc(nvl(start_date, sysdate))
AND trunc(nvl(end_date, sysdate));
SELECT 'x' into l_dummy
FROM fnd_logins
WHERE login_id = p_login_id
AND user_id = p_user_id;
select tb.line_type_id
from cs_txn_billing_oetxn_all tb, cs_txn_billing_types tt
where tb.txn_billing_type_id = p_txn_billing_type_id
and tb.txn_billing_type_id = tt.txn_billing_type_id and
tb.org_id = p_org_id;
x_disallow_charge_update OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_return_status OUT NOCOPY NUMBER
)IS
cursor c_charge_flags(p_incident_id IN NUMBER)IS
select nvl(csinst.disallow_new_charge, 'N'),
nvl(csinst.disallow_charge_update, 'N')
from cs_incident_statuses csinst,
cs_incidents_all csinall
where csinst.incident_status_id = csinall.incident_status_id
and csinall.incident_id = p_incident_id;
INTO x_disallow_new_charge, x_disallow_charge_update;
select nvl(ctt.create_charge_flag, 'Y')
from cs_transaction_types_b ctt
where transaction_type_id= p_txn_type_id ;
SELECT header_id,
open_flag
FROM OE_ORDER_HEADERS_ALL ooha,
HZ_CUST_ACCOUNTS acct,
HZ_PARTIES hp
WHERE ooha.sold_to_org_id = acct.cust_account_id
AND acct.party_id = hp.party_id
AND ooha.header_id = p_order_header_id
AND ooha.org_id = p_org_id;
UPDATE jtf_object_purge_param_tmp
SET
purge_status = 'E'
, purge_error_message = 'CS:CS_CHG_LINE_VAL_ERR'
WHERE
object_id IN
(
SELECT
t.object_id
FROM
cs_estimate_details e
, jtf_object_purge_param_tmp t
WHERE
e.incident_id = t.object_id
AND e.charge_line_type = 'ACTUAL'
AND e.order_line_id IS NULL
AND t.object_type = 'SR'
AND t.processing_set_id = p_processing_set_id
AND nvl(t.purge_status, 'S') = 'S'
)
AND nvl(purge_status, 'S') = 'S'
AND object_type = 'SR'
AND processing_set_id = p_processing_set_id;
DELETE /*+ index(e) */ cs_estimate_details e
WHERE
incident_id IN
(
SELECT /*+ no_unnest no_semijoin cardinality(10) */
object_id
FROM
jtf_object_purge_param_tmp
WHERE
processing_set_id = p_processing_set_id
AND object_type = 'SR'
AND NVL(purge_status, 'S') = 'S'
);
|| l_row_count || ' rows deleted.'
);