eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OZF_OFFER_PVT dependencies on OZF_OFFERS

Line 57: -- 28-OCT-2002 julou modified process_ozf_offers so that IEB is not update when offer

53: -- activate_offer_over, validate_offer API
54: -- 28-Oct-2002 RSSHARMA CHanged Grouping No from 10 to -1 for Customer and Dates
55: -- when a qualifier is created
56: -- 28-Oct-2002 RSSHARMA Changed code to handle Territory as Qualifier Type
57: -- 28-OCT-2002 julou modified process_ozf_offers so that IEB is not update when offer
58: -- is in PENDING_ACTIVE. Changed the order of updating and posting
59: -- LUMPSUM/SCAN_DATA
60: -- 01-NOV-2002 julou pass list_header_id to qualifiers tbl to fix copy API not copying
61: -- order_value_from and order_value_to.

Line 85: -- 13-MAR-2003 julou bug 2844095 - update ozf_offers.qualifier_deleted='Y' if qualifier is deleted

81: -- retrieving this Volume Offer Type and Passing it in the API call
82: -- 19-FEB-2003 julou bug 2806139 - correct process_qp_list_header.
83: -- only make offer active in qp when all approval/validation passed.
84: -- 26-FEB-2003 julou bug 2821174 - make IEB updatable to lower value
85: -- 13-MAR-2003 julou bug 2844095 - update ozf_offers.qualifier_deleted='Y' if qualifier is deleted
86: -- 01-Apr-2003 RSSHARMA fixed bug # 2778138. Added Customer SHip To to Offer Qualifiers
87: -- 15-Apr-2003 RSSHARMA Added Flex Field Code
88: -- 22-APR-2003 julou bug 2916480 - for custom setup 101 and 108, l_budget_required is null.
89: -- added check for l_budget_required and treat it as 'N' if NULL, in process_modifiers

Line 146: -- while the org_id is sent to ozf_offers

142: -- in push_discount_rules_to_qp procedure
143: -- Thu Oct 13 2005:7/21 PM RSSHARMA cleaned up MOAC code
144: -- Thu Oct 20 2005:4/14 PM RSSHARMA Added Following validations.
145: -- If The Security profile is off, in which case local(operating unit specific offers cannot be created), clear the org_id and set the global_flag to Y, for the data sent to QP
146: -- while the org_id is sent to ozf_offers
147: -- If the Offer is Lumpsum or scandata then , raise an exception if the org_id is not passed in since scandata and lumpsum offers always are org specific
148: -- Fri Oct 21 2005:6/45 PM RSSHARMA r12 changes to function discount_lines_exist
149: -- Changed function to query ozf_offer_discount_lines and ozf_offer_discount_products
150: -- if offer type is VOLUME_OFFER

Line 764: FROM ozf_offers

760:
761: -- fix for accrual_flag for multi-tier lines
762: CURSOR c_offer_type(p_list_header_id NUMBER) IS
763: SELECT DECODE(offer_type, 'VOLUME_OFFER', volume_offer_type, offer_type)
764: FROM ozf_offers
765: WHERE qp_list_header_id = p_list_header_id;
766: l_offer_type VARCHAR2(30);
767:
768: CURSOR c_pbh_pricing_attr_id(l_id NUMBER) IS

Line 1467: FROM ozf_offers

1463: l_offer_type VARCHAR2(30);
1464:
1465: CURSOR c_offer_type(l_list_header_id NUMBER) IS
1466: SELECT offer_type
1467: FROM ozf_offers
1468: WHERE qp_list_header_id = l_list_header_id;
1469:
1470: BEGIN
1471: SAVEPOINT process_market_qualifiers;

Line 1598: UPDATE ozf_offers

1594: END IF;
1595: END LOOP;
1596: --raise Fnd_Api.G_EXC_ERROR;
1597: IF l_qualifier_deleted = 'Y' THEN
1598: UPDATE ozf_offers
1599: SET qualifier_deleted = 'Y'
1600: WHERE qp_list_header_id = l_qp_list_header_id;
1601: END IF;
1602:

Line 1779: UPDATE ozf_offers

1775: RAISE Fnd_Api.g_exc_unexpected_error;
1776: END IF;
1777:
1778: IF l_qualifier_deleted = 'Y' THEN
1779: UPDATE ozf_offers
1780: SET qualifier_deleted = 'Y'
1781: WHERE qp_list_header_id = l_qp_list_header_id;
1782: END IF;
1783:

Line 2755: FROM ozf_offers

2751: l_offer_rec modifier_list_rec_type := p_offer_rec;
2752:
2753: CURSOR c_old_user_status_id IS
2754: SELECT user_status_id
2755: FROM ozf_offers
2756: WHERE qp_list_header_id = l_offer_rec.qp_list_header_id;
2757:
2758: BEGIN
2759: SAVEPOINT post_lumpsum_offer;

Line 2899: FROM ozf_offers

2895: l_offer_rec modifier_list_rec_type := p_offer_rec;
2896:
2897: CURSOR c_old_user_status_id IS
2898: SELECT user_status_id
2899: FROM ozf_offers
2900: WHERE qp_list_header_id = l_offer_rec.qp_list_header_id;
2901:
2902: BEGIN
2903: SAVEPOINT post_scan_data_offer;

Line 3602: FROM ozf_request_headers_all_b req,ozf_offers off

3598: )
3599: IS
3600: CURSOR c_req_header_rec(p_offer_id IN NUMBER) IS
3601: SELECT req.request_header_id,req.object_version_number,req.status_code
3602: FROM ozf_request_headers_all_b req,ozf_offers off
3603: WHERE req.request_number = off.offer_code
3604: AND off.qp_list_header_id = p_offer_id;
3605:
3606: l_req_header_id NUMBER;

Line 3698: FROM ozf_offers

3694: IS
3695:
3696: CURSOR c_offer_id(p_qp_list_header_id NUMBER) IS
3697: SELECT offer_id
3698: FROM ozf_offers
3699: WHERE qp_list_header_id = p_qp_list_header_id;
3700:
3701: CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
3702: SELECT q.start_date_active, o.start_date

Line 3703: FROM qp_list_headers_b q, ozf_offers o

3699: WHERE qp_list_header_id = p_qp_list_header_id;
3700:
3701: CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
3702: SELECT q.start_date_active, o.start_date
3703: FROM qp_list_headers_b q, ozf_offers o
3704: WHERE o.qp_list_header_id = q.list_header_id
3705: AND q.list_header_id = p_list_header_id;
3706:
3707: -- fix for bug 7004273 and 7201785

Line 3710: FROM ozf_sd_request_headers_all_b sdr, ozf_offers off

3706:
3707: -- fix for bug 7004273 and 7201785
3708: CURSOR c_sd_req_header_rec(p_offer_id IN NUMBER) IS
3709: SELECT sdr.object_version_number, sdr.request_header_id
3710: FROM ozf_sd_request_headers_all_b sdr, ozf_offers off
3711: --WHERE nvl(sdr.authorization_number,sdr.request_number) = off.offer_code
3712: WHERE sdr.request_number = off.offer_code
3713: AND off.qp_list_header_id = p_offer_id
3714: AND sdr.user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS', 'PENDING_OFFER_APPROVAL');

Line 3776: UPDATE ozf_offers

3772: END IF;
3773:
3774: IF p_offer_rec.status_code = 'DRAFT' THEN
3775: -- CP validation fails. update offer to DRAFT
3776: UPDATE ozf_offers
3777: SET status_code = 'DRAFT'
3778: ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
3779: ,status_date = SYSDATE
3780: ,object_version_number = object_version_number + 1

Line 3798: UPDATE ozf_offers

3794: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3795: --julou amount validation failed, update status depending on recal
3796: IF p_called_from = 'R' THEN
3797: IF p_offer_rec.offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
3798: UPDATE ozf_offers
3799: SET status_code = 'PENDING_ACTIVE',
3800: status_date = SYSDATE,
3801: object_version_number = object_version_number + 1,
3802: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', 'PENDING_ACTIVE')

Line 3805: UPDATE ozf_offers

3801: object_version_number = object_version_number + 1,
3802: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', 'PENDING_ACTIVE')
3803: WHERE qp_list_header_id = p_offer_rec.qp_list_header_id;
3804: ELSE
3805: UPDATE ozf_offers
3806: SET status_code = DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'),
3807: status_date = SYSDATE,
3808: object_version_number = object_version_number + 1,
3809: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'))

Line 3890: UPDATE ozf_offers

3886: ELSE
3887: l_start_date := l_start_date_o;
3888: END IF;
3889:
3890: UPDATE ozf_offers
3891: SET status_code = 'ACTIVE',
3892: status_date = SYSDATE,
3893: object_version_number = object_version_number + 1,
3894: start_date = l_start_date,

Line 4380: they are always local. The org_id is actually stored in ozf_offers, in this case.

4376: END IF;
4377:
4378: /**
4379: In case of lumpsum and scandata Offers the OrgID field appears in the UI, irrespective of the security profile since
4380: they are always local. The org_id is actually stored in ozf_offers, in this case.
4381: This code only makes sure that if the profile is OFF then the global flag is Y., to get rid of qp_list_header creation errors
4382: */
4383: IF NVL(fnd_profile.value('QP_SECURITY_CONTROL'), 'OFF') = 'OFF' THEN
4384: l_modifier_list_rec.global_flag := 'Y';

Line 4654: FROM ozf_offers

4650: AND act_product_used_by_id = p_modifier_list_rec.qp_list_header_id;
4651:
4652: CURSOR c_old_status IS
4653: SELECT status_code
4654: FROM ozf_offers
4655: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
4656:
4657: l_promotional_offers_rec OZF_Promotional_Offers_Pvt.offers_rec_Type;
4658: l_object_version_number NUMBER;

Line 4902: FROM ozf_offers

4898: )
4899: RETURN VARCHAR2 IS
4900: CURSOR c_offerType(cp_listHeaderId NUMBER) IS
4901: SELECT offer_type
4902: FROM ozf_offers
4903: WHERE qp_list_header_id = cp_listHeaderId;
4904: l_offerType OZF_OFFERS.offer_type%TYPE;
4905: BEGIN
4906: OPEN c_offerType(cp_listHeaderId => p_listHeaderId);

Line 4904: l_offerType OZF_OFFERS.offer_type%TYPE;

4900: CURSOR c_offerType(cp_listHeaderId NUMBER) IS
4901: SELECT offer_type
4902: FROM ozf_offers
4903: WHERE qp_list_header_id = cp_listHeaderId;
4904: l_offerType OZF_OFFERS.offer_type%TYPE;
4905: BEGIN
4906: OPEN c_offerType(cp_listHeaderId => p_listHeaderId);
4907: FETCH c_offerType INTO l_offerType;
4908: IF c_offerType%NOTFOUND THEN

Line 4922: FROM ozf_offers

4918: )
4919: RETURN VARCHAR2 IS
4920: CURSOR c_discountLevel(cp_listHeaderId IN NUMBER) IS
4921: SELECT MODIFIER_LEVEL_CODE
4922: FROM ozf_offers
4923: WHERE qp_list_header_id = cp_listHeaderId;
4924: l_discountlevel OZF_OFFERS.MODIFIER_LEVEL_CODE%TYPE;
4925: BEGIN
4926: OPEN c_discountLevel(cp_listHeaderId => p_listHeaderId);

Line 4924: l_discountlevel OZF_OFFERS.MODIFIER_LEVEL_CODE%TYPE;

4920: CURSOR c_discountLevel(cp_listHeaderId IN NUMBER) IS
4921: SELECT MODIFIER_LEVEL_CODE
4922: FROM ozf_offers
4923: WHERE qp_list_header_id = cp_listHeaderId;
4924: l_discountlevel OZF_OFFERS.MODIFIER_LEVEL_CODE%TYPE;
4925: BEGIN
4926: OPEN c_discountLevel(cp_listHeaderId => p_listHeaderId);
4927: FETCH c_discountLevel INTO l_discountLevel;
4928: IF c_discountLevel%NOTFOUND THEN

Line 5604: FROM ozf_offers

5600: l_map_table map_table;
5601:
5602: CURSOR cur_get_discount_level(p_list_header_id NUMBER) IS
5603: SELECT modifier_level_code
5604: FROM ozf_offers
5605: WHERE qp_list_header_id = p_list_header_id;
5606:
5607: l_discount_level VARCHAR2(30):= 'NONE';
5608: l_pricing_phase_id NUMBER;

Line 6228: CURSOR cur_get_ozf_offers IS

6224: l_offer_rec modifier_list_rec_type;
6225: l_amount_error VARCHAR2(1);
6226: l_offer_type VARCHAR2(30);
6227:
6228: CURSOR cur_get_ozf_offers IS
6229: SELECT OFFER_ID
6230: , QP_LIST_HEADER_ID
6231: , OFFER_TYPE
6232: , OFFER_CODE

Line 6254: FROM ozf_offers

6250: , TRANSACTION_CURRENCY_CODE
6251: , FUNCTIONAL_CURRENCY_CODE
6252: , ACTIVITY_MEDIA_ID
6253: , BREAK_TYPE
6254: FROM ozf_offers
6255: WHERE qp_list_header_id = p_qp_list_header_id;
6256:
6257: CURSOR c_approved_amount(l_id NUMBER) IS
6258: SELECT nvl(sum(approved_amount),0)

Line 6274: OPEN cur_get_ozf_offers;

6270: x_return_status := FND_API.g_ret_sts_success;
6271:
6272: l_status_code := OZF_Utility_PVT.get_system_status_code(p_new_status_id);
6273:
6274: OPEN cur_get_ozf_offers;
6275: FETCH cur_get_ozf_offers
6276: INTO l_offer_rec.OFFER_ID
6277: ,l_offer_rec.QP_LIST_HEADER_ID
6278: ,l_offer_rec.OFFER_TYPE

Line 6275: FETCH cur_get_ozf_offers

6271:
6272: l_status_code := OZF_Utility_PVT.get_system_status_code(p_new_status_id);
6273:
6274: OPEN cur_get_ozf_offers;
6275: FETCH cur_get_ozf_offers
6276: INTO l_offer_rec.OFFER_ID
6277: ,l_offer_rec.QP_LIST_HEADER_ID
6278: ,l_offer_rec.OFFER_TYPE
6279: ,l_offer_rec.OFFER_CODE

Line 6301: CLOSE cur_get_ozf_offers;

6297: ,l_offer_rec.TRANSACTION_CURRENCY_CODE
6298: ,l_offer_rec.FUNCTIONAL_CURRENCY_CODE
6299: ,l_offer_rec.ACTIVITY_MEDIA_ID
6300: ,l_offer_rec.BREAK_TYPE;
6301: CLOSE cur_get_ozf_offers;
6302:
6303: IF p_new_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS','ACTIVE')
6304: AND l_offer_rec.CUSTOM_SETUP_ID = 118 THEN
6305:

Line 6313: UPDATE ozf_offers

6309: END IF;
6310:
6311: IF l_status_code <> 'ACTIVE' THEN
6312:
6313: UPDATE ozf_offers
6314: SET status_code = l_status_code,
6315: status_date = SYSDATE,
6316: object_version_number = object_version_number + 1,
6317: user_status_id = p_new_status_id

Line 6364: UPDATE ozf_offers

6360: END IF;
6361:
6362: l_recal := FND_PROFILE.VALUE('OZF_BUDGET_ADJ_ALLOW_RECAL');
6363: -- update status according to recal flag
6364: UPDATE ozf_offers
6365: SET status_code = DECODE(l_amount_error, 'Y', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'), 'N', 'ACTIVE'),
6366: status_date = SYSDATE,
6367: object_version_number = object_version_number + 1,
6368: 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)

Line 6374: UPDATE ozf_offers

6370:
6371: /*
6372: -- julou if recal = 'N' if approved>committed -> active otherwise pending_active
6373: IF l_recal = 'N' THEN
6374: UPDATE ozf_offers
6375: SET status_code = decode(l_amount_error,'Y','PENDING_ACTIVE','N','ACTIVE'),
6376: status_date = SYSDATE,
6377: object_version_number = object_version_number + 1,
6378: 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)

Line 6387: UPDATE ozf_offers

6383: FETCH c_approved_amount INTO l_approved_amount;
6384: CLOSE c_approved_amount;
6385:
6386: IF l_approved_amount <= 0 THEN
6387: UPDATE ozf_offers
6388: SET status_code = 'DRAFT',
6389: status_date = SYSDATE,
6390: object_version_number = object_version_number + 1,
6391: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS','DRAFT')

Line 6467: FROM ozf_offers

6463:
6464: p_list_line_id NUMBER;
6465: CURSOR cur_get_discount_level(p_list_header_id NUMBER) IS
6466: SELECT modifier_level_code
6467: FROM ozf_offers
6468: WHERE qp_list_header_id = p_list_header_id;
6469:
6470: CURSOR cur_get_ov_discount_type(p_list_header_id NUMBER) IS
6471: SELECT order_value_discount_type

Line 6472: FROM ozf_offers

6468: WHERE qp_list_header_id = p_list_header_id;
6469:
6470: CURSOR cur_get_ov_discount_type(p_list_header_id NUMBER) IS
6471: SELECT order_value_discount_type
6472: FROM ozf_offers
6473: WHERE qp_list_header_id = p_list_header_id;
6474:
6475:
6476: CURSOR cur_get_offer_enddate(p_list_header_id NUMBER) IS

Line 6861: FROM ozf_offers

6857: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
6858:
6859: CURSOR cur_get_lumpsum_details IS
6860: SELECT status_code,lumpsum_amount,object_version_number,distribution_type,qp_list_header_id,offer_id
6861: FROM ozf_offers
6862: WHERE qp_list_header_id = p_qp_list_header_id;
6863:
6864: l_lumpsum_offer cur_get_lumpsum_details%rowtype;
6865:

Line 7051: FROM ozf_offers

7047: l_use_modifier_index boolean;
7048:
7049: CURSOR cur_get_discount_level(p_list_header_id NUMBER) IS
7050: SELECT modifier_level_code
7051: FROM ozf_offers
7052: WHERE qp_list_header_id = p_list_header_id;
7053:
7054: l_discount_level VARCHAR2(30);
7055: l_pricing_phase_id NUMBER;

Line 7073: FROM ozf_offers

7069: l_adv_options_exist NUMBER;
7070:
7071: CURSOR c_get_break_type(l_list_header_id NUMBER) IS
7072: SELECT break_type
7073: FROM ozf_offers
7074: WHERE qp_list_header_id = l_list_header_id;
7075:
7076: CURSOR cur_get_offer_enddate(p_list_header_id NUMBER) IS
7077: SELECT start_date_active, end_date_active

Line 8135: FROM ozf_offers

8131: IS
8132: CURSOR c_currency(cp_listHeaderId NUMBER)
8133: IS
8134: SELECT transaction_currency_code
8135: FROM ozf_offers
8136: WHERE qp_list_header_id = cp_listHeaderId;
8137: l_currency ozf_offers.transaction_currency_code%TYPE;
8138: BEGIN
8139: x_return_status := FND_API.G_RET_STS_SUCCESS;

Line 8137: l_currency ozf_offers.transaction_currency_code%TYPE;

8133: IS
8134: SELECT transaction_currency_code
8135: FROM ozf_offers
8136: WHERE qp_list_header_id = cp_listHeaderId;
8137: l_currency ozf_offers.transaction_currency_code%TYPE;
8138: BEGIN
8139: x_return_status := FND_API.G_RET_STS_SUCCESS;
8140: IF p_modifier_line_tbl.count > 0 THEN
8141: FOR i in p_modifier_line_tbl.first .. p_modifier_line_tbl.last LOOP

Line 8194: SELECT volume_offer_type FROM ozf_offers

8190: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
8191: l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
8192: -- RSSHARMA changed on 06-Feb-2003
8193: CURSOR c_volume_offer_type IS
8194: SELECT volume_offer_type FROM ozf_offers
8195: where qp_list_header_id = p_list_header_id;
8196:
8197: l_Volume_offer_type ozf_offers.volume_offer_type%type;
8198:

Line 8197: l_Volume_offer_type ozf_offers.volume_offer_type%type;

8193: CURSOR c_volume_offer_type IS
8194: SELECT volume_offer_type FROM ozf_offers
8195: where qp_list_header_id = p_list_header_id;
8196:
8197: l_Volume_offer_type ozf_offers.volume_offer_type%type;
8198:
8199: l_modifier_line_rec_tbl MODIFIER_LINE_TBL_TYPE := p_modifier_line_tbl;
8200:
8201: BEGIN

Line 8462: FROM ozf_offers

8458: AND decode(greatest(end_date_active, sysdate), sysdate,'N','Y') = 'Y';
8459:
8460: CURSOR c_offerType(cp_listHeaderId NUMBER) IS
8461: SELECT offer_type
8462: FROM ozf_offers
8463: WHERE qp_list_header_id = cp_listHeaderId;
8464:
8465: CURSOR c_voCnt(cp_listHeaderId NUMBER) IS
8466: SELECT 1 FROM dual WHERE EXISTS(SELECT

Line 8467: 'X' FROM ozf_offer_discount_lines a, ozf_offer_discount_products b, ozf_offers c

8463: WHERE qp_list_header_id = cp_listHeaderId;
8464:
8465: CURSOR c_voCnt(cp_listHeaderId NUMBER) IS
8466: SELECT 1 FROM dual WHERE EXISTS(SELECT
8467: 'X' FROM ozf_offer_discount_lines a, ozf_offer_discount_products b, ozf_offers c
8468: WHERE a.offer_discount_line_id = b.offer_discount_line_id
8469: AND a.offer_id = c.offer_id
8470: AND c.qp_list_header_id = cp_listHeaderId
8471: );

Line 8473: l_offerType OZF_OFFERS.offer_type%TYPE := null;

8469: AND a.offer_id = c.offer_id
8470: AND c.qp_list_header_id = cp_listHeaderId
8471: );
8472:
8473: l_offerType OZF_OFFERS.offer_type%TYPE := null;
8474: l_return NUMBER := -1;
8475: BEGIN
8476: l_offerType := null;
8477: OPEN c_offerType(p_list_header_id);

Line 8530: FROM qp_list_headers_b q, ozf_offers o

8526: IS
8527:
8528: CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
8529: SELECT q.start_date_active, o.start_date
8530: FROM qp_list_headers_b q, ozf_offers o
8531: WHERE o.qp_list_header_id = q.list_header_id
8532: AND q.list_header_id = p_list_header_id;
8533:
8534: l_start_date_q DATE;

Line 8558: UPDATE ozf_offers

8554: ELSE
8555: l_start_date := l_start_date_o;
8556: END IF;
8557:
8558: UPDATE ozf_offers
8559: SET user_status_id = p_modifier_list_rec.user_status_id,
8560: status_code = p_modifier_list_rec.status_code,
8561: status_date = SYSDATE,
8562: start_date = l_start_date,

Line 8649: FROM ozf_offers

8645: RETURN VARCHAR2
8646: IS
8647: CURSOR c_dateQualifier(cp_qpListHeaderId NUMBER) IS
8648: SELECT date_qualifier_profile_value
8649: FROM ozf_offers
8650: WHERE qp_list_header_id = cp_qpListHeaderId;
8651: l_dateQualifier VARCHAR2(1):= NULL;
8652: BEGIN
8653: OPEN c_dateQualifier(cp_qpListHeaderId => p_qpListHeaderId ) ;

Line 8712: FROM ozf_offers

8708: l_old_offer_id NUMBER;
8709:
8710: CURSOR cur_get_user_status IS
8711: SELECT user_status_id,owner_id,status_code,offer_id
8712: FROM ozf_offers
8713: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
8714:
8715: CURSOR c_offer_budget IS
8716: SELECT budget_source_type, budget_source_id, budget_amount_tc

Line 8717: FROM ozf_offers

8713: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
8714:
8715: CURSOR c_offer_budget IS
8716: SELECT budget_source_type, budget_source_id, budget_amount_tc
8717: FROM ozf_offers
8718: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
8719:
8720: CURSOR c_budget_required IS
8721: SELECT attr_available_flag

Line 8741: (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id);

8737: CURSOR c_na_line IS
8738: SELECT COUNT(*)
8739: FROM ozf_offer_discount_lines
8740: WHERE offer_id =
8741: (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id);
8742:
8743: CURSOR c_prg_buy_count IS
8744: SELECT COUNT(*)
8745: FROM qp_list_lines

Line 8771: AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);

8767:
8768: CURSOR c_vo_line (p_qp_list_header_id NUMBER)IS
8769: SELECT count(*) FROM ozf_offer_discount_products a, ozf_offer_discount_lines b
8770: WHERE a.offer_discount_line_id = b.offer_discount_line_id
8771: AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);
8772:
8773: CURSOR c_emptyDiscStruct(cp_qpListheaderId NUMBER) IS
8774: SELECT 1 FROM
8775: ozf_offer_discount_lines a , ozf_offers b

Line 8775: ozf_offer_discount_lines a , ozf_offers b

8771: AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);
8772:
8773: CURSOR c_emptyDiscStruct(cp_qpListheaderId NUMBER) IS
8774: SELECT 1 FROM
8775: ozf_offer_discount_lines a , ozf_offers b
8776: WHERE a.tier_type = 'PBH'
8777: AND a.offer_id = b.offer_id --8013
8778: AND
8779: ( NOT EXISTS(SELECT 'X' FROM ozf_offer_discount_products WHERE offer_discount_line_id = a.offer_discount_line_id AND excluder_flag = 'N')

Line 9355: UPDATE ozf_offers

9351: RAISE Fnd_Api.g_exc_unexpected_error;
9352: END IF;
9353: ELSIF l_status_code = 'DRAFT' THEN
9354: -- CP validation fails. update offer to DRAFT
9355: UPDATE ozf_offers
9356: SET status_code = 'DRAFT'
9357: ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
9358: ,status_date = SYSDATE
9359: ,object_version_number = object_version_number + 1

Line 9752: FROM ozf_offers

9748: p_qpListHeaderId IN NUMBER
9749: ) RETURN VARCHAR2 IS
9750: CURSOR c_accrualFlag(cp_qpListHeaderId NUMBER) IS
9751: SELECT decode( VOLUME_OFFER_TYPE, 'ACCRUAL','Y','N')
9752: FROM ozf_offers
9753: WHERE qp_list_header_id = cp_qpListHeaderId;
9754: l_accrualFlag VARCHAR2(1);
9755: BEGIN
9756: OPEN c_accrualFlag(cp_qpListHeaderId => p_qpListHeaderId);

Line 10127: FROM ozf_offer_discount_products a, ozf_offers b

10123: )
10124: IS
10125: CURSOR c_exclusions(cp_qpListHeaderId IN NUMBER) IS
10126: SELECT a.product_attribute, a.product_attr_value, a.excluder_flag
10127: FROM ozf_offer_discount_products a, ozf_offers b
10128: WHERE a.offer_id = b.offer_id
10129: AND a.excluder_flag = 'Y'
10130: AND b.qp_list_header_id = cp_qpListHeaderId;
10131: i NUMBER;

Line 10453: FROM ozf_offer_discount_products a, ozf_offers b

10449: l_api_version_number CONSTANT NUMBER := 1.0;
10450:
10451: CURSOR c_products(cp_qpListHeaderId NUMBER)IS
10452: SELECT a.off_discount_product_id
10453: FROM ozf_offer_discount_products a, ozf_offers b
10454: WHERE a.offer_id = b.offer_id
10455: AND a.excluder_flag = 'N'
10456: AND b.qp_list_header_id = cp_qpListHeaderId;
10457:

Line 10596: FROM ozf_offers

10592: IS
10593:
10594: CURSOR c_offer_info(l_qp_list_header_id NUMBER) IS
10595: SELECT offer_id, offer_type, custom_setup_id, offer_code, tier_level, object_version_number
10596: FROM ozf_offers
10597: WHERE qp_list_header_id = l_qp_list_header_id;
10598:
10599:
10600: CURSOR c_sdr_info(l_sdr_header_id NUMBER) IS

Line 11356: FROM ozf_offers

11352:
11353:
11354: CURSOR c_offer_info(l_qp_list_header_id NUMBER) IS
11355: SELECT offer_id, offer_type, custom_setup_id, offer_code, tier_level, object_version_number, transaction_currency_code, user_status_id
11356: FROM ozf_offers
11357: WHERE qp_list_header_id = l_qp_list_header_id;
11358:
11359: CURSOR c_user_status_id(l_new_status VARCHAR2) IS
11360: SELECT min(user_status_id)