[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT min(offer_discount_line_id) INTO l_offer_discount_line_id
FROM ozf_offer_discount_lines
WHERE offer_id = p_offer_id AND tier_type ='PBH';
SELECT name INTO l_formula_name FROM qp_price_formulas_tl WHERE price_formula_id = p_formula_id AND language = userenv('lang');
SELECT QP_QP_Form_Pricing_Attr.Get_Attribute_Value('QP_ATTR_DEFNS_QUALIFIER',p_qualifier_ctx, p_qualifier_attr, p_qualifier_id)
from dual;
SELECT SUM(DECODE(recal_flag, 'N',committed_amt)) committed_amount
FROM ozf_object_checkbook_v
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT SUM(committed_amt) recal_committed_amount
FROM ozf_object_checkbook_v
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT NVL(SUM(NVL(utilized_amt,0)),0) utlized_amount
FROM ozf_object_checkbook_v
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT NVL(SUM(NVL(paid_amt,0)),0) paid_amount FROM ozf_object_checkbook_v
WHERE object_id = list_header_id
AND object_type = 'OFFR';
SELECT ams_qp_list_line_no_s.nextval from dual;
SELECT count(1)
FROM ozf_act_budgets
where act_budget_used_by_id = list_header_id
and arc_act_budget_used_by = 'OFFR'
AND transfer_type = 'REQUEST';
SELECT count(1)
FROM ozf_act_budgets
where act_budget_used_by_id = list_header_id
and arc_act_budget_used_by = 'OFFR'
AND transfer_type = 'REQUEST'
AND status_code NOT IN ('CLOSED', 'REJECTED');
SELECT jsi.section_id
FROM ibe_dsp_section_items jsi
WHERE jsi.inventory_item_id = p_inventory_item_id;
SELECT list_line_id,modifier_level_code,price_break_type_code
FROM qp_list_lines
WHERE list_header_id = p_advanced_options_rec.list_header_id;
SELECT a.cust_account_id
FROM hz_cust_acct_sites_all a, hz_cust_site_uses_all b
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND b.site_use_code = p_site_use_code
AND b.site_use_id = p_site_use_id;
l_modifiers_tbl(i).operation := Qp_Globals.G_OPR_UPDATE;
OZF_Promotional_Offers_Pvt.UPDATE_OFFERS(
p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_offers_rec => l_promotional_offers_rec,
x_object_version_number => l_object_version_number
);
SELECT proration_type_code ,
product_precedence ,
pricing_group_sequence,
print_on_invoice_flag ,
pricing_phase_id ,
modifier_level_code ,
automatic_flag
FROM qp_list_lines
WHERE list_line_id = parent_list_line_id;
SELECT product_attribute_context,
product_attribute ,
product_attr_value,
product_uom_code ,
pricing_attribute_context
FROM qp_pricing_attributes
WHERE list_line_id = parent_list_line_id;
SELECT DECODE(offer_type, 'VOLUME_OFFER', volume_offer_type, offer_type)
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT c.pricing_attribute_id,
c.list_line_id
FROM qp_rltd_modifiers a, qp_pricing_attributes b, qp_pricing_attributes c
WHERE c.list_line_id = a.from_rltd_modifier_id
AND a.to_rltd_modifier_id = b.list_line_id
AND b.pricing_attribute_id = l_id;
l_pricing_attr_tbl(l_line_index).operation := 'UPDATE';
l_modifiers_tbl(l_line_index).operation := 'UPDATE';
SELECT nvl(related_modifier_id,-1) related_modifier_id
FROM ozf_related_deal_lines a
WHERE a.modifier_id = cp_listLineId;
SELECT pricing_attribute_id
FROM qp_pricing_attributes
WHERE list_line_id = cp_listLineId
AND product_attribute = cp_productAttr
AND product_attr_value = cp_productAttrValue
AND excluder_flag = 'Y';
SELECT qpq.qualifier_context
,qpq.qualifier_attribute
,qpq.qualifier_attr_value
,qpq.qualifier_attr_value_to
,qpq.comparison_operator_code
,qpq.qualifier_grouping_no
,qpq.start_date_active
,qpq.end_date_active
,qpq.active_flag
,qpq.context
,qpq.attribute1
,qpq.attribute2
,qpq.attribute3
,qpq.attribute4
,qpq.attribute5
,qpq.attribute6
,qpq.attribute7
,qpq.attribute8
,qpq.attribute9
,qpq.attribute10
,qpq.attribute11
,qpq.attribute12
,qpq.attribute13
,qpq.attribute14
,qpq.attribute15
,qpq.list_line_id
,qpq.list_header_id
FROM qp_qualifiers qpq
WHERE qpq.list_line_id = p_list_line_id;
SELECT related_modifier_id
FROM ozf_related_deal_lines
WHERE modifier_id = p_modifier_id
AND qp_list_header_id = p_qp_list_header_id;
DELETE FROM qp_qualifiers
WHERE list_header_id = l_modifier_qualifier.list_header_id
AND list_line_id = l_rltd_modifier_id;
l_qualifier_deleted VARCHAR2(1) := 'N';
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = l_list_header_id;
IF p_qualifiers_tbl(i).operation = 'DELETE' THEN
l_qualifier_deleted := 'Y';
IF l_qualifier_deleted = 'Y' THEN
UPDATE ozf_offers
SET qualifier_deleted = 'Y'
WHERE qp_list_header_id = l_qp_list_header_id;
l_qualifier_deleted VARCHAR2(1) := 'N';
IF p_qualifiers_tbl(i).operation = 'DELETE' THEN
l_qualifier_deleted := 'Y';
IF l_qualifier_deleted = 'Y' THEN
UPDATE ozf_offers
SET qualifier_deleted = 'Y'
WHERE qp_list_header_id = l_qp_list_header_id;
SELECT activity_budget_id,object_version_number
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_modifier_list_rec.qp_list_header_id
AND arc_act_budget_used_by = 'OFFR';
IF p_operation = 'UPDATE' AND p_modifier_list_rec.offer_type = 'SCAN_DATA' AND get_active_budget_source_count(p_modifier_list_rec.qp_list_header_id) =1 THEN
OPEN cur_get_offer_budget;
OZF_Actbudgets_Pvt.update_act_budgets(
p_api_version => l_api_version
,p_init_msg_list => Fnd_Api.g_false
,p_commit => Fnd_Api.g_false
,p_validation_level => Fnd_Api.g_valid_level_full
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_act_budgets_rec => l_act_budgets_rec
);
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND qualifier_context = 'SOLD_BY'
ORDER BY qualifier_id;
l_operation := 'UPDATE';
IF l_operation ='UPDATE' THEN -- in case there are multiple only the first created will be updated
OZF_Volume_Offer_Qual_PVT.update_vo_qualifier
(
p_api_version_number => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_qualifiers_rec => l_qualifier_rec -- IN OZF_OFFER_PVT.qualifiers_Rec_Type
);
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND list_line_id = -1
AND qualifier_context IN ('CUSTOMER', 'CUSTOMER_GROUP','TERRITORY')
and rownum < 2;
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND list_line_id = -1
AND qualifier_context IN ('SOLD_BY');
SELECT ship_from_stock_flag
FROM ozf_request_headers_all_b
WHERE agreement_number=p_offr_code
AND ship_from_stock_flag='Y';
IF p_modifier_list_rec.modifier_operation ='UPDATE' THEN
OPEN c_qualifier_id(p_modifier_list_rec.qp_list_header_id);
l_qualifier_tbl(i).operation := 'DELETE';
l_qualifier_tbl(i).operation := 'DELETE';
END IF; -- end UPDATE mode
l_qualifier_tbl.DELETE;
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND qualifier_context IN ('CUSTOMER', 'CUSTOMER_GROUP','TERRITORY')
ORDER BY qualifier_id;
l_operation := 'UPDATE';
IF l_operation ='UPDATE' THEN -- in case there are multiple only the first created will be updated
OZF_Volume_Offer_Qual_PVT.update_vo_qualifier
(
p_api_version_number => p_api_version
, p_init_msg_list => p_init_msg_list
, p_commit => p_commit
, p_validation_level => FND_API.G_VALID_LEVEL_FULL
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_qualifiers_rec => l_qualifiers_rec -- IN OZF_OFFER_PVT.qualifiers_Rec_Type
);
SELECT count(limit_id)
FROM qp_limits
WHERE list_line_id = p_list_line_id
AND limit_number = p_limit_number;
l_limits_rec.operation := 'DELETE';
l_limits_rec.operation := 'UPDATE';
IF (l_limits_rec.operation = 'DELETE'
OR (l_limits_rec.amount IS NOT NULL AND
l_limits_rec.amount <> FND_API.G_MISS_NUM)) THEN
OZF_DEBUG_PVT.DEBUG_MO('OZF_OFFER_PVT.process_limits before calling QP for list_header_id : '|| p_list_header_id);
SELECT user_status_id
FROM ozf_offers
WHERE qp_list_header_id = l_offer_rec.qp_list_header_id;
SELECT user_status_id
FROM ozf_offers
WHERE qp_list_header_id = l_offer_rec.qp_list_header_id;
SELECT TRUNC(creation_date)
FROM qp_list_headers
WHERE list_header_id = l_list_header_id;
ELSIF p_offer_rec.offer_operation = 'UPDATE' THEN
OPEN c_creation_date(p_offer_rec.qp_list_header_id);
ELSIF p_offer_rec.offer_operation = 'UPDATE' THEN
OPEN c_creation_date(p_offer_rec.qp_list_header_id);
SELECT nvl(sum(approved_amount),0)
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = l_id;
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_offer_rec.custom_setup_id
AND object_attribute = 'BREQ';
SELECT NVL(SUM(scan_value * scan_unit_forecast/quantity), 0)
FROM ams_act_products
WHERE arc_act_product_used_by = 'OFFR'
AND act_product_used_by_id = p_offer_rec.qp_list_header_id;
SELECT NVL(SUM(request_amount), 0)
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = p_qp_list_header_id;
SELECT NVL(SUM(scan_value * scan_unit_forecast/quantity), 0)
FROM ams_act_products
WHERE arc_act_product_used_by = 'OFFR'
AND act_product_used_by_id = p_qp_list_header_id;
SELECT act_offer_used_by_id
FROM ozf_act_offers
WHERE arc_act_offer_used_by = 'CSCH'
AND qp_list_header_id = l_qp_id;
SELECT list_header_id
FROM ams_act_lists
WHERE list_act_type = 'TARGET'
AND list_used_by = 'CSCH'
AND list_used_by_id = l_sch_id;
SELECT start_date_time, end_date_time
FROM ams_campaign_schedules_vl
WHERE schedule_id = l_sch_id
AND status_code = 'ACTIVE';
PROCEDURE update_request_status (
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_qp_list_header_id IN NUMBER
)
IS
CURSOR c_req_header_rec(p_offer_id IN NUMBER) IS
SELECT req.request_header_id,req.object_version_number,req.status_code
FROM ozf_request_headers_all_b req,ozf_offers off
WHERE req.request_number = off.offer_code
AND off.qp_list_header_id = p_offer_id;
l_api_name VARCHAR2 (60) := 'update_request_status';
UPDATE ozf_request_headers_all_b
SET status_code ='APPROVED',
object_version_number = l_obj_ver_num + 1
WHERE request_header_id = l_req_header_id;
END update_request_status;
SELECT offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT q.start_date_active, o.start_date
FROM qp_list_headers_b q, ozf_offers o
WHERE o.qp_list_header_id = q.list_header_id
AND q.list_header_id = p_list_header_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_list_lines
WHERE list_header_id = l_list_header_id);
SELECT sdr.object_version_number, sdr.request_header_id
FROM ozf_sd_request_headers_all_b sdr, ozf_offers off
--WHERE nvl(sdr.authorization_number,sdr.request_number) = off.offer_code
WHERE sdr.request_number = off.offer_code
AND off.qp_list_header_id = p_offer_id
AND sdr.user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS', 'PENDING_OFFER_APPROVAL');
SELECT status_code
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
pv_referral_comp_pub.Update_Referral_Status (p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => p_commit,
p_validation_level => FND_API.g_valid_level_full,
p_offer_id => l_offer_id,
p_pass_validation_flag => l_pass_validation_flag,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
UPDATE ozf_offers
SET status_code = 'DRAFT'
,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
,status_date = SYSDATE
,object_version_number = object_version_number + 1
WHERE qp_list_header_id = p_offer_rec.qp_list_header_id;
UPDATE ozf_offers
SET status_code = 'PENDING_ACTIVE',
status_date = SYSDATE,
object_version_number = object_version_number + 1,
user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', 'PENDING_ACTIVE')
WHERE qp_list_header_id = p_offer_rec.qp_list_header_id;
UPDATE ozf_offers
SET status_code = DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'),
status_date = SYSDATE,
object_version_number = object_version_number + 1,
user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'))
WHERE qp_list_header_id = p_offer_rec.qp_list_header_id;
UPDATE ozf_approval_access
SET action_code = NULL
, action_date = NULL
, action_performed_by = NULL
, workflow_itemkey = NULL
, approval_access_flag = 'Y'
, object_version_number = object_version_number + 1
, last_update_date = sysdate
, last_updated_by = FND_GLOBAL.user_id
WHERE approval_access_id IN
(SELECT apr.approval_access_id
FROM ozf_approval_access apr
, ozf_request_headers_all_b req
, jtf_rs_resource_extns jre
WHERE req.request_header_id = apr.object_id
AND apr.object_id = req.request_header_id
AND req.offer_id = p_offer_rec.qp_list_header_id
AND req.request_class = 'SOFT_FUND' -- or 'SPECIAL_PRICE'
AND req.approved_by = jre.resource_id
AND apr.action_performed_by = jre.user_id);
RETURN;-- validation fails, update to DRAFT or PENDING_ACTIVE and return(no posting)
UPDATE ozf_offers
SET status_code = 'ACTIVE',
status_date = SYSDATE,
object_version_number = object_version_number + 1,
start_date = l_start_date,
user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', 'ACTIVE')
WHERE qp_list_header_id = p_offer_rec.qp_list_header_id;
update_request_status (x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_qp_list_header_id => p_offer_rec.qp_list_header_id);
UPDATE ozf_sd_request_headers_all_b
SET user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS', 'ACTIVE') ,
object_version_number = l_obj_ver_num + 1
WHERE request_header_id = l_sdr_req_header_id;
UPDATE qp_list_headers_b
SET active_flag = 'Y'
WHERE list_header_id = p_offer_rec.qp_list_header_id;
UPDATE qp_qualifiers
SET active_flag='Y'
WHERE list_header_id = p_offer_rec.qp_list_header_id;
SELECT qualifier_id , qualifier_context , qualifier_attribute
FROM qp_qualifiers
WHERE list_header_id = l_list_header_id
AND qualifier_context = 'ORDER'
AND qualifier_attribute in ('QUALIFIER_ATTRIBUTE1','QUALIFIER_ATTRIBUTE8');
l_qualifier_tbl(i).operation :='DELETE';
l_qualifier_tbl(i).operation :='UPDATE';
l_qualifier_tbl(i).operation :='DELETE';
l_qualifier_tbl(i).operation :='UPDATE';
SELECT 1 from AMS_SOURCE_CODES
WHERE source_code = p_modifier_list_rec.offer_code;
SELECT list_line_id
FROM qp_limits
WHERE limit_id = p_limit_id;
SELECT hdr.orig_org_id, hdr.global_flag
FROM qp_list_headers_all_b hdr
WHERE hdr.list_header_id = l_list_hdr_id;
ELSIF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
IF (p_modifier_list_rec.user_status_id <> FND_API.g_miss_num)
AND (p_modifier_list_rec.user_status_id <> p_old_status_id) THEN
--nepanda : fix for bug 8507709 : call process_offer_activation only when status is changing from DRAFT to ACTIVE (in order to prevent populating qp_list_lines table in case of ON-HOLD to ACTIVE
IF p_new_status_code = 'ACTIVE' THEN
IF p_approval_type is NULL AND p_modifier_list_rec.offer_type NOT IN ('LUMPSUM', 'SCAN_DATA') THEN
l_modifier_list_rec.active_flag := 'Y';
/*IF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
IF p_modifier_list_rec.offer_type NOT IN ('LUMPSUM','SCAN_DATA') AND NVL(fnd_profile.value('QP_SECURITY_CONTROL'), 'OFF') = 'OFF' THEN
l_modifier_list_rec.global_flag := 'Y';
IF p_modifier_list_rec.modifier_operation = 'UPDATE' AND p_modifier_list_rec.offer_type NOT IN ('ACCRUAL','OFF_INVOICE','DEAL') THEN
OPEN get_offer_info_frm_hdr(p_modifier_list_rec.qp_list_header_id);
IF p_modifier_list_rec.modifier_operation IN ('UPDATE','DELETE') THEN
l_limits_rec.list_header_id := p_modifier_list_rec.qp_list_header_id;
l_limits_rec.operation := 'DELETE'; -- Delete header limit only when committed_amount_eq_max = 'N' (Fix for bug # 10173582)
l_limits_rec.operation := 'UPDATE';
IF p_modifier_list_rec.offer_amount IS NULL OR -- if committed=max is no or Committed Amount is null delete the limit
(p_modifier_list_rec.committed_amount_eq_max = 'N')
THEN
l_limits_rec.operation := 'DELETE';
l_limits_rec.operation := 'UPDATE';
IF p_modifier_list_rec.modifier_operation IN ('UPDATE','DELETE') THEN
l_limits_rec.list_header_id := p_modifier_list_rec.qp_list_header_id;
l_limits_rec.operation := 'DELETE';
l_limits_rec.operation := 'UPDATE';
ELSIF p_modifier_list_rec.offer_operation = 'UPDATE' THEN
IF (p_modifier_list_rec.currency_code IS NULL) THEN
OZF_Utility_PVT.error_message('OZF_OFFR_LS_SD_CURR_REQD');
SELECT NVL(SUM(scan_value * scan_unit_forecast/quantity),0)
FROM ams_act_products
WHERE arc_act_product_used_by = 'OFFR'
AND act_product_used_by_id = p_modifier_list_rec.qp_list_header_id;
SELECT status_code
FROM ozf_offers
WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
ELSIF p_modifier_list_rec.offer_operation = 'UPDATE' THEN
OZF_Promotional_Offers_Pvt.UPDATE_OFFERS
(
p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_offers_rec => l_promotional_offers_rec,
x_object_version_number => l_object_version_number
);
ELSIF p_modifier_list_rec.offer_operation = 'DELETE' THEN
OZF_Promotional_Offers_Pvt.DELETE_OFFERS
(
p_api_version_number => 1.0,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_offer_id => l_promotional_offers_rec.offer_id,
p_object_version_number => l_promotional_offers_rec.object_version_number
);
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = cp_listHeaderId;
SELECT MODIFIER_LEVEL_CODE
FROM ozf_offers
WHERE qp_list_header_id = cp_listHeaderId;
PROCEDURE populateUpdatePricingAttr(
x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_modifierLineRec IN MODIFIER_LINE_REC_TYPE
, x_pricingAttrTbl IN OUT NOCOPY Qp_Modifiers_Pub.pricing_attr_tbl_type
)
IS
CURSOR c_pricingAttr(cp_listLineId NUMBER) IS
SELECT pricing_attribute_id FROM qp_pricing_attributes
WHERE list_line_id = cp_listLineId
OR list_line_id IN (select to_rltd_modifier_id FROM qp_rltd_modifiers WHERE from_rltd_modifier_id = cp_listLineId AND rltd_modifier_grp_type = 'PRICE BREAK');
END populateUpdatePricingAttr;
populateUpdatePricingAttr
(
x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_modifierLineRec => l_modifierLineRec
, x_pricingAttrTbl => x_pricingAttrTbl
);
l_modifiersTbl.delete;
l_pricingAttrTbl.delete;
SELECT ffvs.flex_value_set_id
FROM FND_DESCR_FLEX_COL_USAGE_VL dcu,
fnd_flex_value_sets ffvs
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'QP_ATTR_DEFNS_PRICING'
AND dcu.enabled_flag = 'Y'
AND dcu.flex_value_set_id = ffvs.flex_value_set_id(+)
AND dcu.DESCRIPTIVE_FLEX_CONTEXT_CODE = 'ITEM'
AND dcu.application_id = 661
AND dcu.application_column_name = p_modifier_line_rec.PRODUCT_ATTR;
ELSIF p_modifier_line_rec.operation = 'UPDATE' THEN
IF (p_modifier_line_rec.product_attr IS NULL )
THEN
FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
SELECT modifier_level_code
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT start_date_active, end_date_active
FROM qp_list_headers_b
WHERE list_header_id = p_list_header_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_list_lines
WHERE list_header_id = l_list_header_id);
SELECT pricing_phase_id,print_on_invoice_flag,incompatibility_grp_code,pricing_group_sequence,product_precedence
FROM qp_list_lines
WHERE list_header_id = l_list_header_id;
l_qualifiers_tbl(i).operation := 'UPDATE';
l_qualifiers_tbl(i).operation := 'DELETE';
SELECT OFFER_ID
, QP_LIST_HEADER_ID
, OFFER_TYPE
, OFFER_CODE
, REUSABLE
, CUSTOM_SETUP_ID
, USER_STATUS_ID
, OWNER_ID
, OBJECT_VERSION_NUMBER
, PERF_DATE_FROM
, PERF_DATE_TO
, STATUS_CODE
, STATUS_DATE
, ORDER_VALUE_DISCOUNT_TYPE
, MODIFIER_LEVEL_CODE
, OFFER_AMOUNT
, LUMPSUM_AMOUNT
, LUMPSUM_PAYMENT_TYPE
, DISTRIBUTION_TYPE
, BUDGET_AMOUNT_FC
, BUDGET_AMOUNT_TC
, TRANSACTION_CURRENCY_CODE
, FUNCTIONAL_CURRENCY_CODE
, ACTIVITY_MEDIA_ID
, BREAK_TYPE
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT nvl(sum(approved_amount),0)
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = l_id;
update ozf_sd_request_headers_all_b set user_status_id= OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS','ACTIVE')
where offer_id= l_offer_rec.qp_list_header_id;
UPDATE ozf_offers
SET status_code = l_status_code,
status_date = SYSDATE,
object_version_number = object_version_number + 1,
user_status_id = p_new_status_id
WHERE qp_list_header_id = p_qp_list_header_id;
update ozf_sd_request_headers_all_b set user_status_id= OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS','OFFER_REJECTED')
where offer_id= l_offer_rec.qp_list_header_id;
UPDATE ozf_offers
SET status_code = DECODE(l_amount_error, 'Y', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'), 'N', 'ACTIVE'),
status_date = SYSDATE,
object_version_number = object_version_number + 1,
user_status_id = decode(l_amount_error, 'Y', OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT')),'N', p_new_status_id)
WHERE qp_list_header_id = p_qp_list_header_id;
UPDATE ozf_offers
SET status_code = decode(l_amount_error,'Y','PENDING_ACTIVE','N','ACTIVE'),
status_date = SYSDATE,
object_version_number = object_version_number + 1,
user_status_id = decode(l_amount_error,'Y',OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS','PENDING_ACTIVE'),'N',p_new_status_id)
WHERE qp_list_header_id = p_qp_list_header_id;
UPDATE ozf_offers
SET status_code = 'DRAFT',
status_date = SYSDATE,
object_version_number = object_version_number + 1,
user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS','DRAFT')
WHERE qp_list_header_id = p_qp_list_header_id;
IF l_amount_error = 'N' THEN -- update qp to active only when validation passes
IF l_offer_rec.offer_type NOT IN('LUMPSUM', 'SCAN_DATA', 'NET_ACCRUAL') THEN
UPDATE qp_list_headers_b
SET active_flag = 'Y'
WHERE list_header_id = p_qp_list_header_id;
UPDATE qp_qualifiers
SET active_flag='Y'
WHERE list_header_id = p_qp_list_header_id;
SELECT modifier_level_code
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT order_value_discount_type
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT start_date_active, end_date_active
FROM qp_list_headers_b
WHERE list_header_id = p_list_header_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_list_lines
WHERE list_header_id = l_list_header_id);
SELECT pricing_phase_id,print_on_invoice_flag,incompatibility_grp_code,pricing_group_sequence,product_precedence
FROM qp_list_lines
WHERE list_header_id = l_list_header_id;
SELECT status_code,lumpsum_amount,object_version_number,distribution_type,qp_list_header_id,offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT nvl(sum(line_lumpsum_qty),0)
FROM ams_act_products
WHERE ARC_ACT_PRODUCT_USED_BY = 'OFFR'
AND ACT_PRODUCT_USED_BY_ID = p_qp_list_header_id;
SELECT list_line_id
FROM qp_list_lines
WHERE list_header_id = p_list_header_id
AND list_line_type_code = 'PRG';
SELECT modifier_level_code
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM qp_list_lines
WHERE list_header_id = l_list_header_id);
SELECT pricing_phase_id,print_on_invoice_flag,incompatibility_grp_code,pricing_group_sequence,product_precedence
FROM qp_list_lines
WHERE list_header_id = l_list_header_id;
SELECT break_type
FROM ozf_offers
WHERE qp_list_header_id = l_list_header_id;
SELECT start_date_active, end_date_active
FROM qp_list_headers_b
WHERE list_header_id = p_list_header_id;
SELECT pricing_attribute_id
FROM qp_pricing_attributes
WHERE list_line_id = p_list_line_id
AND excluder_flag = 'N';
SELECT limit_id
FROM qp_limits
WHERE limit_number = p_limit_number
AND list_line_id = p_list_line_id;
SELECT modifier_id, related_modifier_id
FROM ozf_related_deal_lines
WHERE related_deal_lines_id = l_id;
SELECT creation_date, created_by
FROM ozf_related_deal_lines
WHERE related_deal_lines_id = l_id;
l_modifier_line_tbl.delete;
IF l_modifier_line_all_acc_tbl(l_all_index).operation = 'UPDATE' THEN
OPEN get_accr_pricing_attribute_id(l_modifier_line_all_acc_tbl(l_all_index).list_line_id);
IF l_modifier_line_acc_tbl(l_acc_index).operation = 'UPDATE' THEN
OPEN get_accr_pricing_attribute_id(l_modifier_line_acc_tbl(l_acc_index).list_line_id);
v_modifier_ret_tbl.delete;
ELSIF l_modifier_line_tbl(i).operation = 'UPDATE' THEN
OPEN c_creation(l_related_lines_rec.related_deal_lines_id);
OZF_Related_Lines_PVT.update_related_lines
(
p_api_version_number => 1.0
,x_return_status => x_return_Status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_related_lines_rec => l_related_lines_rec
,x_object_version_number => l_object_version_number
);
SELECT transaction_currency_code
FROM ozf_offers
WHERE qp_list_header_id = cp_listHeaderId;
SELECT volume_offer_type FROM ozf_offers
where qp_list_header_id = p_list_header_id;
SELECT 1
FROM DUAL
WHERE EXISTS(
SELECT 1
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND act_budget_used_by_id = p_modifier_list_rec.qp_list_header_id);
SELECT count(1)
FROM ozf_act_offers
where qp_list_header_id = list_header_id;
SELECT COUNT(*)
FROM qp_list_lines
WHERE list_header_id = l_list_header_id
AND decode(greatest(end_date_active, sysdate), sysdate,'N','Y') = 'Y';
SELECT offer_type
FROM ozf_offers
WHERE qp_list_header_id = cp_listHeaderId;
SELECT 1 FROM dual WHERE EXISTS(SELECT
'X' FROM ozf_offer_discount_lines a, ozf_offer_discount_products b, ozf_offers c
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND a.offer_id = c.offer_id
AND c.qp_list_header_id = cp_listHeaderId
);
PROCEDURE Update_Offer_Status
(
p_commit IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_modifier_list_rec IN modifier_list_rec_type
)
IS
CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
SELECT q.start_date_active, o.start_date
FROM qp_list_headers_b q, ozf_offers o
WHERE o.qp_list_header_id = q.list_header_id
AND q.list_header_id = p_list_header_id;
l_api_name CONSTANT VARCHAR2(30) := 'update_offer_status';
SAVEPOINT update_offer_status;
UPDATE ozf_offers
SET user_status_id = p_modifier_list_rec.user_status_id,
status_code = p_modifier_list_rec.status_code,
status_date = SYSDATE,
start_date = l_start_date,
object_version_number = object_version_number + 1
WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
UPDATE qp_list_headers_b
SET active_flag = 'Y'
WHERE list_header_id = p_modifier_list_rec.qp_list_header_id;
UPDATE qp_qualifiers
SET active_flag = 'Y'
WHERE list_header_id = p_modifier_list_rec.qp_list_header_id;
ROLLBACK TO update_offer_status;
END update_offer_status;
SELECT date_qualifier_profile_value
FROM ozf_offers
WHERE qp_list_header_id = cp_qpListHeaderId;
SELECT user_status_id,owner_id,status_code,offer_id,transaction_currency_code,fund_request_curr_code
FROM ozf_offers
WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
SELECT budget_source_type, budget_source_id, budget_amount_tc
FROM ozf_offers
WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE object_attribute = 'BREQ'
AND custom_setup_id = p_modifier_list_rec.custom_setup_id;
SELECT COUNT(*)
FROM ams_act_products
WHERE arc_act_product_used_by = 'OFFR'
AND act_product_used_by_id = p_modifier_list_rec.qp_list_header_id;
SELECT COUNT(*)
FROM qp_list_lines
WHERE list_header_id = p_modifier_list_rec.qp_list_header_id;
SELECT COUNT(*)
FROM ozf_offer_discount_lines
WHERE offer_id =
(SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id);
SELECT COUNT(*)
FROM qp_list_lines
WHERE list_header_id = p_modifier_list_rec.qp_list_header_id
AND TRUNC(SYSDATE) <= TRUNC(NVL(end_date_active, SYSDATE))
AND list_line_type_code = 'PRG';
SELECT COUNT(*)
FROM qp_list_lines
WHERE list_header_id = p_modifier_list_rec.qp_list_header_id
AND TRUNC(SYSDATE) <= TRUNC(NVL(end_date_active, SYSDATE))
AND list_line_type_code = 'DIS';
SELECT activity_budget_id
FROM ozf_act_budgets
WHERE act_budget_used_by_id = p_qp_list_header_id
AND arc_act_budget_used_by = 'OFFR';
SELECT count(*) FROM ozf_offer_discount_products a, ozf_offer_discount_lines b
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);
SELECT 1 FROM
ozf_offer_discount_lines a , ozf_offers b
WHERE a.tier_type = 'PBH'
AND a.offer_id = b.offer_id --8013
AND
( NOT EXISTS(SELECT 'X' FROM ozf_offer_discount_products WHERE offer_discount_line_id = a.offer_discount_line_id AND excluder_flag = 'N')
OR NOT EXISTS(SELECT 'X' FROM ozf_offer_discount_lines WHERE parent_discount_line_id = a.offer_discount_line_id)
)
AND b.qp_list_header_id = cp_qpListheaderId;
SELECT COUNT(*)
FROM ozf_act_budgets
WHERE arc_act_budget_used_by = 'OFFR'
AND status_code = 'NEW'
AND transfer_type = 'REQUEST'
AND act_budget_used_by_id = p_list_header_id;
SELECT COUNT(*)
FROM qp_limits
WHERE list_header_id = p_qp_list_header_id
AND list_line_id = -1 ;
IF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
OPEN cur_get_user_status;
IF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
IF getDateQualifier(p_qpListHeaderId => p_modifier_list_rec.qp_list_header_id) = 'A'
THEN
offer_dates(
p_modifier_list_rec => p_modifier_list_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
(p_modifier_list_rec.modifier_operation = 'UPDATE' )
AND
(p_modifier_list_rec.orig_org_id IS NULL)
)
)
THEN
FND_MESSAGE.SET_NAME('QP','QP_ATTRIBUTE_REQUIRED');
ELSIF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
IF l_new_status_code = 'ACTIVE' AND p_modifier_list_rec.offer_type = 'VOLUME_OFFER' THEN
ozf_check_dup_prod_pvt.check_dup_prod(
x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_offer_id => l_old_offer_id);
/*IF (p_modifier_list_rec.modifier_operation = 'UPDATE') AND (p_modifier_list_rec.user_status_id <> FND_API.g_miss_num)
AND (p_modifier_list_rec.user_status_id <> l_old_status_id)
THEN
raise_offer_event(p_offer_id => temp_modifier_list_rec.qp_list_header_id );
IF p_modifier_list_rec.offer_operation = 'UPDATE'
AND p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
IF (p_modifier_list_rec.user_status_id <> FND_API.g_miss_num)
AND (p_modifier_list_rec.user_status_id <> l_old_status_id) THEN
IF l_new_status_code = 'ACTIVE' THEN
-- julou bug 2122722 activating offer w/o discount lines
IF p_modifier_list_rec.offer_type IN ('LUMPSUM','SCAN_DATA') THEN
OPEN c_prod_line;
UPDATE ozf_offers
SET status_code = 'DRAFT'
,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
,status_date = SYSDATE
,object_version_number = object_version_number + 1
WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
(p_modifier_list_rec.modifier_operation ='UPDATE' AND get_campaign_count(p_modifier_list_rec.qp_list_header_id) < 1)
THEN
IF p_modifier_list_rec.offer_used_by_id <> Fnd_Api.g_miss_num
AND p_modifier_list_rec.offer_used_by_id IS NOT NULL
THEN
offer_object_usage(
p_modifier_list_rec => temp_modifier_list_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
(p_modifier_list_rec.modifier_operation ='UPDATE' AND get_budget_source_count(p_modifier_list_rec.qp_list_header_id) < 1)
THEN
IF (temp_modifier_list_rec.budget_source_id <> Fnd_Api.g_miss_num
AND temp_modifier_list_rec.budget_source_id IS NOT NULL )
AND ( p_modifier_list_rec.offer_amount <> Fnd_Api.g_miss_num
AND p_modifier_list_rec.offer_amount IS NOT NULL
)
THEN
offer_budget(
p_modifier_list_rec => temp_modifier_list_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_operation => p_modifier_list_rec.modifier_operation
);
IF( (p_modifier_list_rec.custom_setup_id = 119 AND p_modifier_list_rec.modifier_operation = 'CREATE') OR (p_modifier_list_rec.custom_setup_id <> 119 AND p_modifier_list_rec.modifier_operation IN ('CREATE','UPDATE'))) THEN
IF (p_modifier_list_rec.ql_qualifier_id <> Fnd_Api.g_miss_num
AND p_modifier_list_rec.ql_qualifier_id IS NOT NULL)
OR
(
p_modifier_list_rec.sales_method_flag IS NOT NULL
AND
p_modifier_list_rec.sales_method_flag <> Fnd_Api.g_miss_char
)
THEN
offer_qualifier(
p_modifier_list_rec => temp_modifier_list_rec
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
INSERT INTO ozf_offer_qualifiers(
qualifier_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,qualifier_grouping_no
,qualifier_context
,qualifier_attribute
,qualifier_attr_value
,start_date_active
,end_date_active
,offer_id
,active_flag
,object_version_number)
VALUES(
ozf_offer_qualifiers_s.NEXTVAL
,SYSDATE
,FND_GLOBAL.user_id
,SYSDATE
,FND_GLOBAL.user_id
,FND_GLOBAL.conc_login_id
,10
,NULL--l_na_qual_context
,p_modifier_list_rec.na_qualifier_type--l_na_qual_attribute
,TO_CHAR(p_modifier_list_rec.na_qualifier_id)
,p_modifier_list_rec.start_date_active
,p_modifier_list_rec.end_date_active
,l_offer_id
,'Y'
,1);
update ozf_sd_request_headers_all_b set user_status_id= OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS','OFFER_REJECTED')
where offer_id=p_modifier_list_rec.qp_list_header_id;
update ozf_sd_request_headers_all_b set user_status_id= OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS','ACTIVE')
where offer_id= p_modifier_list_rec.qp_list_header_id;
SELECT a.offer_discount_line_id
, a.product_context
, a.product_attribute
, a.product_attr_value
, a.apply_discount_flag
, a.include_volume_flag
, a.excluder_flag
, b.volume_break_type
, b.volume_type
, b.discount_type
, b.uom_code
FROM ozf_offer_discount_products a, ozf_offer_discount_lines b
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND a.off_discount_product_id = cp_offDiscountProductId;
SELECT decode( VOLUME_OFFER_TYPE, 'ACCRUAL','Y','N')
FROM ozf_offers
WHERE qp_list_header_id = cp_qpListHeaderId;
SELECT offer_discount_line_id
FROM ozf_offer_discount_products
WHERE off_discount_product_id = cp_offDiscountProductId ;
SELECT a.volume_break_type
, a.discount_type
FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
WHERE a.offer_discount_line_id = b.offer_discount_line_id
AND b.off_discount_product_id = cp_offDiscountProductId;
SELECT decode(c.apply_discount_flag, 'N',0,a.discount) discount
, decode(c.apply_discount_flag, 'N',null,a.formula_id) formula_id
, b.volume_break_type
, b.discount_type
FROM ozf_offer_discount_lines a, ozf_offer_discount_lines b, ozf_offer_discount_products c
WHERE a.parent_discount_line_id = b.offer_discount_line_id
AND b.offer_discount_line_id = c.offer_discount_line_id
AND c.excluder_flag = 'N'
AND c.off_discount_product_id = cp_offDiscountProductId
AND a.offer_discount_line_id = cp_offerDiscountLineId;
SELECT min(volume_from)
FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
WHERE a.parent_discount_line_id = b.offer_discount_line_id
AND b.off_discount_product_id = cp_offDiscountProductId;
SELECT a.offer_discount_line_id
FROM ozf_offer_discount_lines a, ozf_offer_discount_products b
WHERE a.parent_discount_line_id = b.offer_discount_line_id
AND b.off_discount_product_id = cp_offerDiscountProductId;
SELECT volume_from, volume_to
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = cp_offerDiscountLineId ;
SELECT a.product_attribute, a.product_attr_value, a.excluder_flag
FROM ozf_offer_discount_products a, ozf_offers b
WHERE a.offer_id = b.offer_id
AND a.excluder_flag = 'Y'
AND b.qp_list_header_id = cp_qpListHeaderId;
SELECT a.off_discount_product_id
FROM ozf_offer_discount_products a, ozf_offers b
WHERE a.offer_id = b.offer_id
AND a.excluder_flag = 'N'
AND b.qp_list_header_id = cp_qpListHeaderId;
SELECT supplier_site_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = l_sdr_header_id;
SELECT nvl(grace_days,0)
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = l_supplier_site_id;
SELECT offer_id,
offer_type,
custom_setup_id,
offer_code,
tier_level,
object_version_number,
budget_source_id --//Bug 10648426
FROM ozf_offers
WHERE qp_list_header_id = l_qp_list_header_id;
SELECT sales_order_currency,
request_currency_code,
request_start_date,
request_end_date,
authorization_number,
request_number,
asignee_resource_id,
request_basis,
org_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = l_sdr_header_id;
IF p_operation='UPDATE' THEN
OPEN c_offer_info(p_qp_list_header_id);
SELECT request_line_id,product_context,
prod_catg_id,inventory_item_id,
item_uom,product_cost,
product_cost_currency,
requested_discount_type,
requested_discount_value,
approved_discount_type,
approved_discount_value,
approved_discount_currency,
requested_discount_currency,
cost_basis,limit_qty,
start_date,end_date,
create_from
FROM ozf_sd_request_lines_all
WHERE request_header_id = p_sdr_header_id
AND vendor_approved_flag='Y';
SELECT list_line_no,list_line_id
FROM qp_list_lines
WHERE list_line_no = to_char(p_request_line_id)
AND list_header_id = p_qp_list_header_id ;
SELECT sales_order_currency,
request_currency_code,
supplier_site_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = p_sdr_header_id;
SELECT allow_qty_increase
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id = p_supplier_site_id;
SELECT limit_id
FROM qp_limits
WHERE list_line_id = p_list_line_id
AND limit_number = 3;
x_modifier_line_tbl(l_prod_index).OPERATION := 'UPDATE';
SELECT request_customer_id,party_id,
cust_account_id,site_use_id,
cust_usage_code,end_customer_flag,
cust_usage_value
FROM ozf_sd_customer_details
WHERE request_header_id = p_sdr_header_id
AND end_customer_flag='N'
AND NVL(operation_flag, 'I') = 'I';
SELECT request_customer_id,party_id,
cust_account_id,site_use_id,
cust_usage_code,end_customer_flag,
cust_usage_value
FROM ozf_sd_customer_details
WHERE request_header_id = p_sdr_header_id
AND end_customer_flag='Y'
AND NVL(operation_flag, 'I') = 'I';
SELECT count(request_customer_id)
FROM ozf_sd_customer_details
WHERE request_header_id = p_sdr_header_id
AND end_customer_flag = 'Y';
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = p_qp_list_header_id
--bug 12632240 ninarasi
AND ((qualifier_context = 'CUSTOMER'
AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE2', 'QUALIFIER_ATTRIBUTE14',
'QUALIFIER_ATTRIBUTE11', 'QUALIFIER_ATTRIBUTE20'))
OR (qualifier_context = 'CUSTOMER_GROUP'
AND qualifier_attribute IN ('QUALIFIER_ATTRIBUTE1', 'QUALIFIER_ATTRIBUTE2',
'QUALIFIER_ATTRIBUTE3'))
OR (qualifier_context = 'TERRITORY'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE1')
OR (qualifier_context = 'MODLIST'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE32'
AND (qualifier_attr_value <>'Y'
OR qualifier_grouping_no <> -1
OR comparison_operator_code <> '='))
OR (qualifier_context = 'ORDER'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE21'));
SELECT internal_order_number,org_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id = p_sdr_header_id;
SELECT header_id
FROM oe_order_headers_all
WHERE order_number = p_internal_order_number
AND org_id=p_org_id;
SELECT COUNT(qualifier_id)
FROM qp_qualifiers
WHERE list_header_id = p_qp_list_header_id
AND (qualifier_context = 'MODLIST'
AND qualifier_attribute = 'QUALIFIER_ATTRIBUTE32'
AND qualifier_grouping_no = -1
AND comparison_operator_code = '='
AND qualifier_attr_value = 'Y');
SELECT count(request_customer_id)
FROM ozf_sd_customer_details
WHERE request_header_id = p_sdr_header_id
AND end_customer_flag = 'Y'
AND NVL(operation_flag, 'I') = 'I';
SELECT qualifier_id
FROM qp_qualifiers
WHERE list_header_id = p_qp_list_header_id
AND list_line_id = -1
AND qualifier_attribute = l_qualifier_attribute
AND qualifier_context = l_qualifier_context
AND to_char(qualifier_attr_value) = l_qualifier_attr_value;
SELECT request_customer_id,party_id,
cust_account_id,site_use_id,
cust_usage_code,end_customer_flag,
cust_usage_value, operation_flag
FROM ozf_sd_customer_details
WHERE request_header_id = p_sdr_header_id
AND end_customer_flag = 'N'
AND operation_flag IS NOT NULL;
SELECT NVL(max(qualifier_grouping_no),0) + 10
FROM qp_qualifiers
WHERE list_header_id = p_qp_list_header_id
AND list_line_id =-1
AND qualifier_grouping_no > 0;
x_qualifier_tbl(l_qual_index).operation := 'DELETE';
IF l_operation_count>0 THEN --For delete operation(if atleast one end customer is to be kept)-insert costomer and end customer with grouping
FOR cust_line_rec IN cur_get_cust_qual_lines LOOP
FOR end_cust_line_rec IN cur_get_end_cust_qual_lines LOOP
--Populate customer record
x_qualifier_tbl(l_qual_index).list_header_id := p_qp_list_header_id;
ELSE --For delete operation(if all the end customer is to be deleted)- Insert only the customer
FOR cust_line_rec IN cur_get_cust_qual_lines LOOP
x_qualifier_tbl(l_qual_index).list_header_id := p_qp_list_header_id;
END IF; --For delete operation(if all the end customer is to be deleted)
x_qualifier_tbl(l_qual_index).operation := 'DELETE';
UPDATE ozf_sd_customer_details
SET operation_flag = NULL
WHERE request_header_id = p_sdr_header_id
AND operation_flag = 'I';
DELETE FROM ozf_sd_customer_details
WHERE request_header_id = p_sdr_header_id
AND operation_flag = 'D';
SELECT offer_id, offer_type, custom_setup_id, offer_code, tier_level, object_version_number, transaction_currency_code, user_status_id
FROM ozf_offers
WHERE qp_list_header_id = l_qp_list_header_id;
SELECT min(user_status_id)
FROM ams_user_statuses_vl
WHERE system_status_type = 'OZF_OFFR_STATUS'
AND system_status_code=l_new_status;
SELECT accrual_type,cust_account_id,supplier_site_id
FROM ozf_sd_request_headers_all_b
WHERE request_header_id=l_sdr_hdr_id;
SELECT cust_account_id, site_use_id
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id=l_supplier_site_id;
SELECT allow_qty_increase
FROM ozf_supp_trd_prfls_all
WHERE supplier_site_id=l_supplier_site_id;
SELECT access_id,user_id,status
FROM (
SELECT activity_access_id access_id,user_or_role_id user_id,'ACCESS' status
FROM ams_act_access
WHERE act_access_to_object_id=l_qp_list_header_id
AND arc_act_access_to_object='OFFR'
AND arc_user_or_role_type = 'USER'
UNION
SELECT request_access_id access_id, user_id user_id,'REQUEST' status
FROM ozf_sd_request_access
WHERE request_header_id=l_request_header_id
AND approver_flag='Y'
AND enabled_flag='Y');
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM ams_act_access
WHERE act_access_to_object_id = l_qp_list_header_id
AND arc_act_access_to_object='OFFR'
AND user_or_role_id = l_resource_id
AND arc_user_or_role_type = 'USER');
SELECT activity_access_id,object_version_number
FROM ams_act_access
WHERE act_access_to_object_id = l_qp_list_header_id
AND arc_act_access_to_object = 'OFFR'
AND user_or_role_id = l_resource_id
AND arc_user_or_role_type = 'USER';
SELECT user_status_id
FROM OZF_SD_REQUEST_HEADERS_ALL_B
WHERE request_header_id=p_sdr_header_id;
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
AND object_attribute = 'TAPL';
SELECT attr_available_flag
FROM ams_custom_setup_attr
WHERE custom_setup_id = p_custom_setup_id
AND object_attribute = 'BAPL';
SELECT NVL(resource_id,-1)
FROM ozf_sd_request_access
WHERE request_header_id= p_sdr_header_id;
l_operation := 'UPDATE';
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
debug_message('N: pass list_header_id for update'||x_qp_list_header_id);
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
l_modifier_list_rec.offer_operation := 'UPDATE';
l_modifier_list_rec.modifier_operation := 'UPDATE';
update ozf_sd_request_headers_all_b
set user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS','ACTIVE')
where request_header_id = p_sdr_header_id;