[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT CUST_ACCOUNT_ID
FROM HZ_CUST_ACCOUNTS_ALL
WHERE PARTY_ID = p_party_id
AND STATUS = 'A';
SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all
WHERE cust_account_id = p_account_id and
party_site_id = p_party_site_id and
org_id = p_org_id and
status = 'A';
SELECT site_use_id
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id = p_cust_acct_site_id and
site_use_code = p_site_use_code and
status = 'A';
SELECT b.address1
FROM hz_party_sites a, hz_locations b
WHERE a.location_id = b.location_id
AND a.party_site_id = p_party_site_id;
SELECT org.organization_name
FROM org_organization_definitions org
WHERE org.organization_id = p_org_id;
SELECT party_id
INTO l_party_id
FROM HZ_PARTY_SITES
WHERE party_site_id = p_party_site_id;
/* SELECT min(CUST_ACCOUNT_ID)
INTO x_account_id
FROM HZ_CUST_ACCOUNTS_ALL
WHERE PARTY_ID = l_party_id
AND STATUS = 'A'
AND CUST_ACCOUNT_ID is not null;*/
SELECT min(CUST_ACCOUNT_ID)
INTO x_account_id
FROM HZ_CUST_ACCOUNTS_ALL h1
WHERE h1.PARTY_ID = l_party_id
AND h1.STATUS = 'A'
AND h1.CUST_ACCOUNT_ID is not null
AND EXISTS (SELECT '1' FROM hz_cust_acct_sites_all h2
WHERE h2.party_site_id = p_party_site_id
AND h2.CUST_ACCOUNT_ID = h1.CUST_ACCOUNT_ID
AND h2.STATUS = 'A'
AND h2.ORG_ID = p_org_id
AND h2.BILL_TO_FLAG = 'Y');
SELECT min(CUST_ACCOUNT_ID)
INTO x_account_id
FROM HZ_CUST_ACCOUNTS_ALL h1
WHERE h1.PARTY_ID = l_party_id
AND h1.STATUS = 'A'
AND h1.CUST_ACCOUNT_ID is not null
AND EXISTS (SELECT '1' FROM hz_cust_acct_sites_all h2
WHERE h2.party_site_id = p_party_site_id
AND h2.CUST_ACCOUNT_ID = h1.CUST_ACCOUNT_ID
AND h2.STATUS = 'A'
AND h2.ORG_ID = p_org_id
AND h2.SHIP_TO_FLAG = 'Y');
SELECT cust_acct_site_id
FROM hz_cust_acct_sites_all
WHERE cust_account_id = p_account_id and
party_site_id = p_party_site_id and
org_id = p_org_id and
status = p_status;
SELECT site_use_id
FROM hz_cust_site_uses_all
WHERE cust_acct_site_id = p_cust_acct_site_id and
site_use_code = p_site_use_code and
status = p_status;
SELECT b.address1
FROM hz_party_sites a, hz_locations b
WHERE a.location_id = b.location_id
AND a.party_site_id = p_party_site_id;
SELECT org.organization_name
FROM org_organization_definitions org
WHERE org.organization_id = p_org_id;
Procedure Update_Errors (p_estimate_detail_id IN NUMBER,
p_line_submitted IN VARCHAR2,
p_submit_restriction_message IN VARCHAR2,
p_submit_error_message IN VARCHAR2,
p_submit_from_system IN VARCHAR2
) IS
pragma AUTONOMOUS_TRANSACTION;
-- SAVEPOINT Update_Errors;
UPDATE CS_ESTIMATE_DETAILS
SET line_submitted = p_line_submitted,
submit_restriction_message = p_submit_restriction_message,
submit_error_message = p_submit_error_message,
submit_from_system = p_submit_from_system,
last_update_date = sysdate
WHERE Estimate_Detail_Id = p_estimate_detail_id;
FND_MESSAGE.SET_TOKEN(token => 'PROG_NAME', value => 'Cs_Charge_Create_Order_PVT.Update_Errors');
END Update_Errors;
SELECT edt.incident_id,
edt.org_id,
edt.estimate_detail_id,
edt.currency_code,
edt.conversion_type_code,
edt.conversion_rate,
edt.conversion_rate_date,
edt.business_process_id,
edt.txn_billing_type_id,
edt.price_list_header_id,
edt.inventory_item_id,
edt.item_revision,
edt.unit_of_measure_code,
edt.quantity_required,
edt.selling_price,
edt.after_warranty_cost,
edt.invoice_to_org_id,
edt.ship_to_org_id,
edt.customer_product_id,
edt.installed_cp_return_by_date,
edt.new_cp_return_by_date, -- Bug 4586140
edt.add_to_order_flag,
edt.order_header_id,
edt.rollup_flag,
edt.purchase_order_num,
edt.return_reason_code,
edt.serial_number return_serial_number,
tt.LINE_ORDER_CATEGORY_CODE line_category_code,
edt.organization_id,
edt.transaction_inventory_org,
edt.invoice_to_account_id,
edt.ship_to_account_id,
edt.ship_to_contact_id,
edt.bill_to_contact_id,
edt.bill_to_party_id,
edt.ship_to_party_id,
tb.order_type_id,
tb.line_type_id,
i.comms_nl_trackable_flag,
sr.customer_id,
sr.account_id,
sr.incident_number,
tbt.billing_type,
cbtc.rollup_item_id,
cbtc.billing_category,
edt.list_price , -- 4870210
i.item_type item_type_code --6523849
FROM CS_ESTIMATE_DETAILS edt,
CS_TXN_BILLING_OETXN_ALL tb,
CS_TXN_BILLING_TYPES tbt,
MTL_SYSTEM_ITEMS_KFV i,
CS_INCIDENTS_ALL_B sr,
CS_TRANSACTION_TYPES_B tt,
cs_billing_type_categories cbtc
WHERE edt.incident_id = p_incident_id AND
edt.interface_to_oe_flag = 'Y' AND
edt.order_line_id IS NULL AND
edt.charge_line_type = 'ACTUAL' AND
edt.txn_billing_type_id = tb.txn_billing_type_id(+) AND
--edt.org_id = tb.org_id (+) AND
nvl(edt.org_id, '-999') = nvl(tb.org_id, '-999') AND
edt.inventory_item_id = i.inventory_item_id AND
nvl(i.organization_id,-999) = nvl(p_inv_org_id,-999) AND
edt.incident_id = sr.incident_id AND
edt.txn_billing_type_id = tbt.txn_billing_type_id AND
tt.transaction_type_id = tbt.transaction_type_id AND
tbt.billing_type = cbtc.billing_type AND
edt.line_submitted = 'N' AND
((edt.original_source_code = nvl(p_ctrl_orig_source,ORIGINAL_SOURCE_CODE)
and edt.source_code = nvl(p_ctrl_source,SOURCE_CODE)) OR
(p_ctrl_submit_source = 'DR'
and original_source_code = 'SR'
and edt.source_code = 'DR'))
order by edt.org_id,edt.estimate_detail_id
For Update nowait; -- for cross ou --7117301
SELECT nvl(max(edt.order_header_id),-999)
FROM CS_ESTIMATE_DETAILS edt,
CS_TXN_BILLING_OETXN_ALL tb,
OE_ORDER_HEADERS_ALL oe
WHERE edt.incident_id = p_incident_id AND
edt.currency_code = p_currency_code AND
nvl(edt.invoice_to_org_id,-999) = nvl(p_invoice_to_org_id,-999) AND
nvl(edt.ship_to_org_id,-999) = nvl(p_ship_to_org_id,-999) AND
nvl(edt.org_id,-999) = nvl(p_org_id,-999) AND
nvl(edt.purchase_order_num,'-999') = nvl(p_purchase_order_num,'-999') AND
edt.order_header_id is not null AND
edt.order_line_id is not null AND
edt.interface_to_oe_flag = 'Y' AND
edt.txn_billing_type_id = tb.txn_billing_type_id AND
nvl(edt.org_id,-999) = nvl(tb.org_id,-999) AND
tb.order_type_id = p_order_type_id AND
edt.order_header_id = oe.header_id AND
oe.open_flag = 'Y' AND
oe.booked_flag = decode(p_book_order_flag,'N','N','Y');
SELECT nvl(cust_po_number,'-999')
FROM oe_order_headers_all
WHERE header_id = p_order_header_id;
SELECT list_header_id
FROM qp_list_lines
WHERE list_line_id = p_list_line_id;
select inventory_item_id from csi_item_instances
where instance_id = p_instance_id;
SELECT count(cust_account_id)
FROM HZ_CUST_ACCOUNTS_ALL
WHERE party_id = p_party_id
AND NVL(status, 'A') = 'A';
l_update_ib_flag VARCHAR2(1);
SELECT booked_flag
FROM oe_order_headers_all
WHERE header_id = p_order_header_id;
SELECT party_number
FROM hz_parties
WHERE party_id = p_party_id;
l_line_tbl.delete;
l_line_dtl_tbl.delete;
l_ln_shipment_tbl.delete;
l_hd_payment_tbl.delete;
l_line_price_adj_tbl.delete;
/* SELECT billing_type
INTO l_billing_flag
FROM cs_txn_billing_types
WHERE txn_billing_type_id = EstDtlTab(i).txn_billing_type_id; */
SELECT cbtc.billing_category
INTO l_billing_flag
FROM cs_txn_billing_types ctbt, cs_billing_type_categories cbtc
WHERE ctbt.txn_billing_type_id = EstDtlTab(i).txn_billing_type_id
AND ctbt.billing_type = cbtc.billing_type;
SELECT primary_uom_code
INTO l_unit_code
FROM mtl_system_items
WHERE inventory_item_id = l_inv_item_id AND
organization_id = CS_STD.Get_Item_Valdn_Orgzn_ID;
SELECT l.arithmetic_operator,h.currency_code
INTO l_arith_operator, l_currency_code
FROM qp_list_headers h,
qp_list_lines l
WHERE h.list_header_id = l.list_header_id
AND l.list_line_id = l_modifier_line_id;
l_line_price_adj_tbl(j).updated_flag := 'Y';
l_line_price_adj_tbl(j).updated_flag := 'Y';
SELECT a.transaction_type_id,
b.sub_type_id,
c.transaction_type_id,
nvl(b.src_reference_reqd,'N'),
b.src_change_owner,
b.src_change_owner_to_code,
nvl(b.non_src_reference_reqd,'N'),
b.non_src_change_owner,
b.non_src_change_owner_to_code,
nvl(b.update_ib_flag,'N'),
b.src_return_reqd -- Bug 4586140
INTO l_transaction_type_id,
l_sub_type_id,
l_source_type_id,
l_src_reference_reqd,
l_src_change_owner,
l_src_change_owner_to_code,
l_non_src_reference_reqd,
l_non_src_change_owner,
l_non_src_change_owner_to_code,
l_update_ib_flag,
l_src_return_reqd_flag -- Bug 4586140
FROM CS_TXN_BILLING_TYPES a,
csi_txn_sub_types b,
csi_txn_types c
WHERE a.txn_billing_type_id = EstDtlTab(i).txn_billing_type_id
AND a.transaction_type_id = b.cs_transaction_type_id
AND b.transaction_type_id = c.transaction_type_id
AND c.source_application_id = 660
AND c.source_transaction_type =
decode(EstDtlTab(i).line_category_code,'RETURN', 'RMA_RECEIPT', 'ORDER', 'OM_SHIPMENT',null);
l_update_ib_flag := 'N';
-- update_ib_flag is 'Y',but do not raise any error message.
-- Bug fix 3564034
IF l_update_ib_flag = 'Y' THEN
IF EstDtlTab(i).customer_product_id is not null THEN
-- For return lines for IB, pass serial number to OM
IF EstDtlTab(i).line_category_code = 'RETURN' THEN
l_ib_serial_number := null;
select serial_number
into l_ib_serial_number
from CSI_ITEM_INSTANCES
where instance_id = EstDtlTab(i).customer_product_id;
are 'Y',update_ib_flag is 'Y' and the instance_id is null ***/
IF (l_src_reference_reqd = 'Y' OR
l_non_src_reference_reqd = 'Y') AND
l_update_ib_flag = 'Y' AND
EstDtlTab(i).customer_product_id IS NULL THEN
FND_MESSAGE.Set_Name('CS','CS_CHG_IB_MISSING_INSTANCE');
csi_txn_line_detail_tbl.delete;
csi_txn_party_detail_tbl.delete;
csi_txn_pty_acct_detail_tbl.delete;
csi_txn_ii_rltns_tbl.delete;
csi_txn_org_assgn_tbl.delete;
csi_txn_ext_attrib_vals_tbl.delete;
csi_txn_systems_tbl.delete;
/* BUG 4287842 for a loaner shipment, installation details should display the ITEM for which the instance_number was selected. Currently it displays the Item irrespective of the reference number*/
OPEN get_inv_item_id(EstDtlTab(i).customer_product_id);
SELECT internal_party_id
INTO l_internal_party_id
FROM csi_install_parameters
WHERE rownum = 1;
SELECT instance_party_id
INTO l_instance_party_id
FROM CSI_I_PARTIES
WHERE instance_id = EstDtlTab(i).Customer_Product_Id
AND relationship_type_code = 'OWNER'
AND party_id = (SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = EstDtlTab(i).account_id);
csi_txn_pty_acct_detail_tbl.delete;
SELECT internal_party_id
INTO l_internal_party_id
FROM csi_install_parameters
WHERE rownum = 1;
SELECT instance_party_id
INTO l_instance_party_id
FROM CSI_I_PARTIES
WHERE instance_id = EstDtlTab(i).Customer_Product_Id
AND relationship_type_code = 'OWNER'
AND party_id IN (l_internal_party_id,EstDtlTab(i).customer_id);
csi_txn_pty_acct_detail_tbl.delete;
csi_txn_party_detail_tbl.delete;
csi_txn_pty_acct_detail_tbl.delete;
END IF; -- IF Update_Ib_Flag = Y
( FND_LOG.level_statement, L_LOG_MODULE , 'Update_Ib_Flag: ' || l_update_ib_flag
);
( FND_LOG.level_statement, L_LOG_MODULE , 'l_line_price_adj_tbl.updated_flag is: ' || l_line_price_adj_tbl(j).updated_flag
);
ASO_ORDER_INT.Update_order(
P_Api_Version_Number => 1.0,
P_Qte_Rec => l_header_rec,
P_Qte_Line_Tbl => l_line_tbl,
P_Qte_Line_Dtl_Tbl => l_line_dtl_tbl,
P_Line_Shipment_Tbl => l_ln_shipment_tbl,
P_Header_Payment_Tbl => l_hd_payment_tbl,
P_Line_Price_Adj_Tbl => l_line_price_adj_tbl,
P_Lot_Serial_Tbl => l_lot_serial_tbl,
P_Control_Rec => l_control_rec,
X_Order_Header_Rec => x_order_header_rec,
X_Order_Line_Tbl => x_order_line_tbl,
X_Return_Status => x_return_status,
X_Msg_Count => x_msg_count,
X_Msg_Data => x_msg_data
);
Update_Estimate_Details(EstDtlTab(i).estimate_detail_id,
x_order_header_id,
x_order_line_tbl(k).order_line_id,
NULL,
NULL,
NULL,
p_submit_from_system);
and l_update_ib_flag = 'Y' THEN
-- assign order line id to source transaction id.
csi_txn_line_rec.source_transaction_id := x_order_line_tbl(1).order_line_id;
SELECT cust_account_id
INTO l_account_id
FROM hz_cust_accounts_all
WHERE party_id = p_party_id
AND status = 'A';
SELECT internal_party_id
INTO l_internal_party_id
FROM csi_install_parameters
WHERE rownum = 1;
SELECT instance_party_id
INTO l_instance_party_id
FROM CSI_I_PARTIES
WHERE instance_id = EstDtlTab(i).Customer_Product_Id
AND relationship_type_code = 'OWNER'
AND party_id = (SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = l_account_id);
csi_txn_pty_acct_detail_tbl.delete;
csi_txn_party_detail_tbl.delete;
csi_txn_pty_acct_detail_tbl.delete;
-- Update charge lines with order details
-- Fix for bug:3545283
FOR k in 1..x_order_line_tbl.count LOOP
-- UPDATE ESTIMATE_DETAILS WITH ORDER_HEADER_ID AND ORDER_LINE_ID.
-- Changes for 11.5.10.
-- NULL Values should be passed for submit_error_message,
-- submit_restriction_message and line_submitted columns as the
-- order creation is successful.
Update_Estimate_Details(EstDtlTab(i).estimate_detail_id,
x_order_header_id,
x_order_line_tbl(k).order_line_id,
NULL,
NULL,
NULL,
p_submit_from_system);
/* Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
p_line_submitted => 'N',
p_submit_restriction_message => NULL,
p_submit_error_message => temp_tab,
p_submit_from_system => p_submit_from_system); */
Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
p_line_submitted => 'N',
p_submit_restriction_message => NULL,
p_submit_error_message => x_msg_data,
p_submit_from_system => p_submit_from_system);
Update_Errors(p_estimate_detail_id => EstDtlTab(i).estimate_detail_id,
p_line_submitted => 'N',
p_submit_restriction_message => NULL,
p_submit_error_message => x_msg_data,
p_submit_from_system => p_submit_from_system);
PROCEDURE Update_Estimate_Details (
p_Estimate_Detail_Id IN NUMBER,
p_order_header_Id IN NUMBER,
p_order_line_Id IN NUMBER,
p_line_submitted IN VARCHAR2,
p_submit_restriction_message IN VARCHAR2,-- new
p_submit_error_message IN VARCHAR2,-- new
p_submit_from_system IN VARCHAR2 -- new
) IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_Estimate_Details' ;
UPDATE CS_ESTIMATE_DETAILS
SET Order_Header_Id = p_order_header_Id,
Order_Line_Id = p_order_line_Id,
line_submitted = p_line_submitted,
submit_restriction_message = p_submit_restriction_message,
submit_error_message = p_submit_error_message,
submit_from_system = p_submit_from_system,
last_update_date = sysdate,
last_update_login = fnd_global.login_id, --6027992
last_updated_by = fnd_global.user_id --6027992
WHERE Estimate_Detail_Id = p_estimate_detail_id;
end Update_Estimate_Details;