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 858: FROM ozf_offers

854:
855: -- fix for accrual_flag for multi-tier lines
856: CURSOR c_offer_type(p_list_header_id NUMBER) IS
857: SELECT DECODE(offer_type, 'VOLUME_OFFER', volume_offer_type, offer_type)
858: FROM ozf_offers
859: WHERE qp_list_header_id = p_list_header_id;
860: l_offer_type VARCHAR2(30);
861:
862: CURSOR c_pbh_pricing_attr_id(l_id NUMBER) IS

Line 1573: FROM ozf_offers

1569: l_offer_type VARCHAR2(30);
1570:
1571: CURSOR c_offer_type(l_list_header_id NUMBER) IS
1572: SELECT offer_type
1573: FROM ozf_offers
1574: WHERE qp_list_header_id = l_list_header_id;
1575:
1576: BEGIN
1577: SAVEPOINT process_market_qualifiers;

Line 1706: UPDATE ozf_offers

1702: END IF;
1703: END LOOP;
1704: --raise Fnd_Api.G_EXC_ERROR;
1705: IF l_qualifier_deleted = 'Y' THEN
1706: UPDATE ozf_offers
1707: SET qualifier_deleted = 'Y'
1708: WHERE qp_list_header_id = l_qp_list_header_id;
1709: END IF;
1710:

Line 1889: UPDATE ozf_offers

1885: RAISE Fnd_Api.g_exc_unexpected_error;
1886: END IF;
1887:
1888: IF l_qualifier_deleted = 'Y' THEN
1889: UPDATE ozf_offers
1890: SET qualifier_deleted = 'Y'
1891: WHERE qp_list_header_id = l_qp_list_header_id;
1892: END IF;
1893:

Line 2920: FROM ozf_offers

2916: l_offer_rec modifier_list_rec_type := p_offer_rec;
2917:
2918: CURSOR c_old_user_status_id IS
2919: SELECT user_status_id
2920: FROM ozf_offers
2921: WHERE qp_list_header_id = l_offer_rec.qp_list_header_id;
2922:
2923: BEGIN
2924: SAVEPOINT post_lumpsum_offer;

Line 3064: FROM ozf_offers

3060: l_offer_rec modifier_list_rec_type := p_offer_rec;
3061:
3062: CURSOR c_old_user_status_id IS
3063: SELECT user_status_id
3064: FROM ozf_offers
3065: WHERE qp_list_header_id = l_offer_rec.qp_list_header_id;
3066:
3067: BEGIN
3068: SAVEPOINT post_scan_data_offer;

Line 3768: FROM ozf_request_headers_all_b req,ozf_offers off

3764: )
3765: IS
3766: CURSOR c_req_header_rec(p_offer_id IN NUMBER) IS
3767: SELECT req.request_header_id,req.object_version_number,req.status_code
3768: FROM ozf_request_headers_all_b req,ozf_offers off
3769: WHERE req.request_number = off.offer_code
3770: AND off.qp_list_header_id = p_offer_id;
3771:
3772: l_req_header_id NUMBER;

Line 3864: FROM ozf_offers

3860: IS
3861:
3862: CURSOR c_offer_id(p_qp_list_header_id NUMBER) IS
3863: SELECT offer_id
3864: FROM ozf_offers
3865: WHERE qp_list_header_id = p_qp_list_header_id;
3866:
3867: CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
3868: SELECT q.start_date_active, o.start_date

Line 3869: FROM qp_list_headers_b q, ozf_offers o

3865: WHERE qp_list_header_id = p_qp_list_header_id;
3866:
3867: CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
3868: SELECT q.start_date_active, o.start_date
3869: FROM qp_list_headers_b q, ozf_offers o
3870: WHERE o.qp_list_header_id = q.list_header_id
3871: AND q.list_header_id = p_list_header_id;
3872:
3873: -- nepanda : start : fix for bug 8507709 : added cursor to find out if data is already there in qp_list_line table for an offer

Line 3885: FROM ozf_sd_request_headers_all_b sdr, ozf_offers off

3881: -- nepanda : end : fix for bug 8507709
3882: -- fix for bug 7004273 and 7201785
3883: CURSOR c_sd_req_header_rec(p_offer_id IN NUMBER) IS
3884: SELECT sdr.object_version_number, sdr.request_header_id
3885: FROM ozf_sd_request_headers_all_b sdr, ozf_offers off
3886: --WHERE nvl(sdr.authorization_number,sdr.request_number) = off.offer_code
3887: WHERE sdr.request_number = off.offer_code
3888: AND off.qp_list_header_id = p_offer_id
3889: AND sdr.user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_SD_REQUEST_STATUS', 'PENDING_OFFER_APPROVAL');

Line 3894: FROM ozf_offers

3890:
3891:
3892: CURSOR c_offer_status_code(p_qp_list_header_id NUMBER) IS
3893: SELECT status_code
3894: FROM ozf_offers
3895: WHERE qp_list_header_id = p_qp_list_header_id;
3896:
3897: l_api_name CONSTANT VARCHAR2(30) := 'Activate_Offer_Over';
3898: l_api_version CONSTANT NUMBER := 1.0;

Line 3911: l_status_code ozf_offers.status_code%type;

3907: l_start_date_o DATE;
3908: l_start_date DATE;
3909: l_obj_ver_num NUMBER;
3910: l_sdr_req_header_id NUMBER;
3911: l_status_code ozf_offers.status_code%type;
3912: BEGIN
3913: SAVEPOINT activate_offer_over;
3914: -- Standard call to check for call compatibility.
3915: IF NOT FND_API.Compatible_API_Call ( l_api_version,

Line 3957: UPDATE ozf_offers

3953: END IF;
3954:
3955: IF p_offer_rec.status_code = 'DRAFT' THEN
3956: -- CP validation fails. update offer to DRAFT
3957: UPDATE ozf_offers
3958: SET status_code = 'DRAFT'
3959: ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
3960: ,status_date = SYSDATE
3961: ,object_version_number = object_version_number + 1

Line 3979: UPDATE ozf_offers

3975: IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3976: --julou amount validation failed, update status depending on recal
3977: IF p_called_from = 'R' THEN
3978: IF p_offer_rec.offer_type IN ('LUMPSUM', 'SCAN_DATA') THEN
3979: UPDATE ozf_offers
3980: SET status_code = 'PENDING_ACTIVE',
3981: status_date = SYSDATE,
3982: object_version_number = object_version_number + 1,
3983: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', 'PENDING_ACTIVE')

Line 3986: UPDATE ozf_offers

3982: object_version_number = object_version_number + 1,
3983: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', 'PENDING_ACTIVE')
3984: WHERE qp_list_header_id = p_offer_rec.qp_list_header_id;
3985: ELSE
3986: UPDATE ozf_offers
3987: SET status_code = DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'),
3988: status_date = SYSDATE,
3989: object_version_number = object_version_number + 1,
3990: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'))

Line 4071: UPDATE ozf_offers

4067: ELSE
4068: l_start_date := l_start_date_o;
4069: END IF;
4070:
4071: UPDATE ozf_offers
4072: SET status_code = 'ACTIVE',
4073: status_date = SYSDATE,
4074: object_version_number = object_version_number + 1,
4075: start_date = l_start_date,

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

4622: END IF;
4623:
4624: /**
4625: In case of lumpsum and scandata Offers the OrgID field appears in the UI, irrespective of the security profile since
4626: they are always local. The org_id is actually stored in ozf_offers, in this case.
4627: 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
4628: */
4629: --Bugfix : 12649016
4630: /*IF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN

Line 5032: FROM ozf_offers

5028: AND act_product_used_by_id = p_modifier_list_rec.qp_list_header_id;
5029:
5030: CURSOR c_old_status IS
5031: SELECT status_code
5032: FROM ozf_offers
5033: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
5034:
5035: l_promotional_offers_rec OZF_Promotional_Offers_Pvt.offers_rec_Type;
5036: l_object_version_number NUMBER;

Line 5280: FROM ozf_offers

5276: )
5277: RETURN VARCHAR2 IS
5278: CURSOR c_offerType(cp_listHeaderId NUMBER) IS
5279: SELECT offer_type
5280: FROM ozf_offers
5281: WHERE qp_list_header_id = cp_listHeaderId;
5282: l_offerType OZF_OFFERS.offer_type%TYPE;
5283: BEGIN
5284: OPEN c_offerType(cp_listHeaderId => p_listHeaderId);

Line 5282: l_offerType OZF_OFFERS.offer_type%TYPE;

5278: CURSOR c_offerType(cp_listHeaderId NUMBER) IS
5279: SELECT offer_type
5280: FROM ozf_offers
5281: WHERE qp_list_header_id = cp_listHeaderId;
5282: l_offerType OZF_OFFERS.offer_type%TYPE;
5283: BEGIN
5284: OPEN c_offerType(cp_listHeaderId => p_listHeaderId);
5285: FETCH c_offerType INTO l_offerType;
5286: IF c_offerType%NOTFOUND THEN

Line 5300: FROM ozf_offers

5296: )
5297: RETURN VARCHAR2 IS
5298: CURSOR c_discountLevel(cp_listHeaderId IN NUMBER) IS
5299: SELECT MODIFIER_LEVEL_CODE
5300: FROM ozf_offers
5301: WHERE qp_list_header_id = cp_listHeaderId;
5302: l_discountlevel OZF_OFFERS.MODIFIER_LEVEL_CODE%TYPE;
5303: BEGIN
5304: OPEN c_discountLevel(cp_listHeaderId => p_listHeaderId);

Line 5302: l_discountlevel OZF_OFFERS.MODIFIER_LEVEL_CODE%TYPE;

5298: CURSOR c_discountLevel(cp_listHeaderId IN NUMBER) IS
5299: SELECT MODIFIER_LEVEL_CODE
5300: FROM ozf_offers
5301: WHERE qp_list_header_id = cp_listHeaderId;
5302: l_discountlevel OZF_OFFERS.MODIFIER_LEVEL_CODE%TYPE;
5303: BEGIN
5304: OPEN c_discountLevel(cp_listHeaderId => p_listHeaderId);
5305: FETCH c_discountLevel INTO l_discountLevel;
5306: IF c_discountLevel%NOTFOUND THEN

Line 6104: FROM ozf_offers

6100: l_map_table map_table;
6101:
6102: CURSOR cur_get_discount_level(p_list_header_id NUMBER) IS
6103: SELECT modifier_level_code
6104: FROM ozf_offers
6105: WHERE qp_list_header_id = p_list_header_id;
6106:
6107: l_discount_level VARCHAR2(30):= 'NONE';
6108: l_pricing_phase_id NUMBER;

Line 6758: CURSOR cur_get_ozf_offers IS

6754: l_offer_rec modifier_list_rec_type;
6755: l_amount_error VARCHAR2(1);
6756: l_offer_type VARCHAR2(30);
6757:
6758: CURSOR cur_get_ozf_offers IS
6759: SELECT OFFER_ID
6760: , QP_LIST_HEADER_ID
6761: , OFFER_TYPE
6762: , OFFER_CODE

Line 6784: FROM ozf_offers

6780: , TRANSACTION_CURRENCY_CODE
6781: , FUNCTIONAL_CURRENCY_CODE
6782: , ACTIVITY_MEDIA_ID
6783: , BREAK_TYPE
6784: FROM ozf_offers
6785: WHERE qp_list_header_id = p_qp_list_header_id;
6786:
6787: CURSOR c_approved_amount(l_id NUMBER) IS
6788: SELECT nvl(sum(approved_amount),0)

Line 6804: OPEN cur_get_ozf_offers;

6800: x_return_status := FND_API.g_ret_sts_success;
6801:
6802: l_status_code := OZF_Utility_PVT.get_system_status_code(p_new_status_id);
6803:
6804: OPEN cur_get_ozf_offers;
6805: FETCH cur_get_ozf_offers
6806: INTO l_offer_rec.OFFER_ID
6807: ,l_offer_rec.QP_LIST_HEADER_ID
6808: ,l_offer_rec.OFFER_TYPE

Line 6805: FETCH cur_get_ozf_offers

6801:
6802: l_status_code := OZF_Utility_PVT.get_system_status_code(p_new_status_id);
6803:
6804: OPEN cur_get_ozf_offers;
6805: FETCH cur_get_ozf_offers
6806: INTO l_offer_rec.OFFER_ID
6807: ,l_offer_rec.QP_LIST_HEADER_ID
6808: ,l_offer_rec.OFFER_TYPE
6809: ,l_offer_rec.OFFER_CODE

Line 6831: CLOSE cur_get_ozf_offers;

6827: ,l_offer_rec.TRANSACTION_CURRENCY_CODE
6828: ,l_offer_rec.FUNCTIONAL_CURRENCY_CODE
6829: ,l_offer_rec.ACTIVITY_MEDIA_ID
6830: ,l_offer_rec.BREAK_TYPE;
6831: CLOSE cur_get_ozf_offers;
6832:
6833: IF p_new_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS','ACTIVE')
6834: AND l_offer_rec.CUSTOM_SETUP_ID = 118 THEN
6835:

Line 6843: UPDATE ozf_offers

6839: END IF;
6840:
6841: IF l_status_code <> 'ACTIVE' THEN
6842:
6843: UPDATE ozf_offers
6844: SET status_code = l_status_code,
6845: status_date = SYSDATE,
6846: object_version_number = object_version_number + 1,
6847: user_status_id = p_new_status_id

Line 6894: UPDATE ozf_offers

6890: END IF;
6891:
6892: l_recal := FND_PROFILE.VALUE('OZF_BUDGET_ADJ_ALLOW_RECAL');
6893: -- update status according to recal flag
6894: UPDATE ozf_offers
6895: SET status_code = DECODE(l_amount_error, 'Y', DECODE(l_recal, 'N', 'PENDING_ACTIVE', 'Y', 'DRAFT'), 'N', 'ACTIVE'),
6896: status_date = SYSDATE,
6897: object_version_number = object_version_number + 1,
6898: 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 6904: UPDATE ozf_offers

6900:
6901: /*
6902: -- julou if recal = 'N' if approved>committed -> active otherwise pending_active
6903: IF l_recal = 'N' THEN
6904: UPDATE ozf_offers
6905: SET status_code = decode(l_amount_error,'Y','PENDING_ACTIVE','N','ACTIVE'),
6906: status_date = SYSDATE,
6907: object_version_number = object_version_number + 1,
6908: 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 6917: UPDATE ozf_offers

6913: FETCH c_approved_amount INTO l_approved_amount;
6914: CLOSE c_approved_amount;
6915:
6916: IF l_approved_amount <= 0 THEN
6917: UPDATE ozf_offers
6918: SET status_code = 'DRAFT',
6919: status_date = SYSDATE,
6920: object_version_number = object_version_number + 1,
6921: user_status_id = OZF_Utility_PVT.get_default_user_status('OZF_OFFER_STATUS','DRAFT')

Line 6997: FROM ozf_offers

6993:
6994: p_list_line_id NUMBER;
6995: CURSOR cur_get_discount_level(p_list_header_id NUMBER) IS
6996: SELECT modifier_level_code
6997: FROM ozf_offers
6998: WHERE qp_list_header_id = p_list_header_id;
6999:
7000: CURSOR cur_get_ov_discount_type(p_list_header_id NUMBER) IS
7001: SELECT order_value_discount_type

Line 7002: FROM ozf_offers

6998: WHERE qp_list_header_id = p_list_header_id;
6999:
7000: CURSOR cur_get_ov_discount_type(p_list_header_id NUMBER) IS
7001: SELECT order_value_discount_type
7002: FROM ozf_offers
7003: WHERE qp_list_header_id = p_list_header_id;
7004:
7005:
7006: CURSOR cur_get_offer_enddate(p_list_header_id NUMBER) IS

Line 7395: FROM ozf_offers

7391: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
7392:
7393: CURSOR cur_get_lumpsum_details IS
7394: SELECT status_code,lumpsum_amount,object_version_number,distribution_type,qp_list_header_id,offer_id
7395: FROM ozf_offers
7396: WHERE qp_list_header_id = p_qp_list_header_id;
7397:
7398: l_lumpsum_offer cur_get_lumpsum_details%rowtype;
7399:

Line 7585: FROM ozf_offers

7581: l_use_modifier_index boolean;
7582:
7583: CURSOR cur_get_discount_level(p_list_header_id NUMBER) IS
7584: SELECT modifier_level_code
7585: FROM ozf_offers
7586: WHERE qp_list_header_id = p_list_header_id;
7587:
7588: l_discount_level VARCHAR2(30);
7589: l_pricing_phase_id NUMBER;

Line 7607: FROM ozf_offers

7603: l_adv_options_exist NUMBER;
7604:
7605: CURSOR c_get_break_type(l_list_header_id NUMBER) IS
7606: SELECT break_type
7607: FROM ozf_offers
7608: WHERE qp_list_header_id = l_list_header_id;
7609:
7610: CURSOR cur_get_offer_enddate(p_list_header_id NUMBER) IS
7611: SELECT start_date_active, end_date_active

Line 8669: FROM ozf_offers

8665: IS
8666: CURSOR c_currency(cp_listHeaderId NUMBER)
8667: IS
8668: SELECT transaction_currency_code
8669: FROM ozf_offers
8670: WHERE qp_list_header_id = cp_listHeaderId;
8671: l_currency ozf_offers.transaction_currency_code%TYPE;
8672: BEGIN
8673: x_return_status := FND_API.G_RET_STS_SUCCESS;

Line 8671: l_currency ozf_offers.transaction_currency_code%TYPE;

8667: IS
8668: SELECT transaction_currency_code
8669: FROM ozf_offers
8670: WHERE qp_list_header_id = cp_listHeaderId;
8671: l_currency ozf_offers.transaction_currency_code%TYPE;
8672: BEGIN
8673: x_return_status := FND_API.G_RET_STS_SUCCESS;
8674: IF p_modifier_line_tbl.count > 0 THEN
8675: FOR i in p_modifier_line_tbl.first .. p_modifier_line_tbl.last LOOP

Line 8728: SELECT volume_offer_type FROM ozf_offers

8724: l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
8725: l_modifier_line_tbl qp_modifiers_pub.modifiers_tbl_type;
8726: -- RSSHARMA changed on 06-Feb-2003
8727: CURSOR c_volume_offer_type IS
8728: SELECT volume_offer_type FROM ozf_offers
8729: where qp_list_header_id = p_list_header_id;
8730:
8731: l_Volume_offer_type ozf_offers.volume_offer_type%type;
8732:

Line 8731: l_Volume_offer_type ozf_offers.volume_offer_type%type;

8727: CURSOR c_volume_offer_type IS
8728: SELECT volume_offer_type FROM ozf_offers
8729: where qp_list_header_id = p_list_header_id;
8730:
8731: l_Volume_offer_type ozf_offers.volume_offer_type%type;
8732:
8733: l_modifier_line_rec_tbl MODIFIER_LINE_TBL_TYPE := p_modifier_line_tbl;
8734:
8735: BEGIN

Line 8996: FROM ozf_offers

8992: AND decode(greatest(end_date_active, sysdate), sysdate,'N','Y') = 'Y';
8993:
8994: CURSOR c_offerType(cp_listHeaderId NUMBER) IS
8995: SELECT offer_type
8996: FROM ozf_offers
8997: WHERE qp_list_header_id = cp_listHeaderId;
8998:
8999: CURSOR c_voCnt(cp_listHeaderId NUMBER) IS
9000: SELECT 1 FROM dual WHERE EXISTS(SELECT

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

8997: WHERE qp_list_header_id = cp_listHeaderId;
8998:
8999: CURSOR c_voCnt(cp_listHeaderId NUMBER) IS
9000: SELECT 1 FROM dual WHERE EXISTS(SELECT
9001: 'X' FROM ozf_offer_discount_lines a, ozf_offer_discount_products b, ozf_offers c
9002: WHERE a.offer_discount_line_id = b.offer_discount_line_id
9003: AND a.offer_id = c.offer_id
9004: AND c.qp_list_header_id = cp_listHeaderId
9005: );

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

9003: AND a.offer_id = c.offer_id
9004: AND c.qp_list_header_id = cp_listHeaderId
9005: );
9006:
9007: l_offerType OZF_OFFERS.offer_type%TYPE := null;
9008: l_return NUMBER := -1;
9009: BEGIN
9010: l_offerType := null;
9011: OPEN c_offerType(p_list_header_id);

Line 9064: FROM qp_list_headers_b q, ozf_offers o

9060: IS
9061:
9062: CURSOR c_offer_start_date(p_list_header_id NUMBER) IS
9063: SELECT q.start_date_active, o.start_date
9064: FROM qp_list_headers_b q, ozf_offers o
9065: WHERE o.qp_list_header_id = q.list_header_id
9066: AND q.list_header_id = p_list_header_id;
9067:
9068: l_start_date_q DATE;

Line 9092: UPDATE ozf_offers

9088: ELSE
9089: l_start_date := l_start_date_o;
9090: END IF;
9091:
9092: UPDATE ozf_offers
9093: SET user_status_id = p_modifier_list_rec.user_status_id,
9094: status_code = p_modifier_list_rec.status_code,
9095: status_date = SYSDATE,
9096: start_date = l_start_date,

Line 9183: FROM ozf_offers

9179: RETURN VARCHAR2
9180: IS
9181: CURSOR c_dateQualifier(cp_qpListHeaderId NUMBER) IS
9182: SELECT date_qualifier_profile_value
9183: FROM ozf_offers
9184: WHERE qp_list_header_id = cp_qpListHeaderId;
9185: l_dateQualifier VARCHAR2(1):= NULL;
9186: BEGIN
9187: OPEN c_dateQualifier(cp_qpListHeaderId => p_qpListHeaderId ) ;

Line 9251: FROM ozf_offers

9247: l_TPM_process_enabled VARCHAR2 (1) := NVL(fnd_profile.VALUE ('OZF_TPM_PROCESS_ENABLED'),'N');
9248:
9249: CURSOR cur_get_user_status IS
9250: SELECT user_status_id,owner_id,status_code,offer_id,transaction_currency_code,fund_request_curr_code
9251: FROM ozf_offers
9252: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
9253:
9254: CURSOR c_offer_budget IS
9255: SELECT budget_source_type, budget_source_id, budget_amount_tc

Line 9256: FROM ozf_offers

9252: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
9253:
9254: CURSOR c_offer_budget IS
9255: SELECT budget_source_type, budget_source_id, budget_amount_tc
9256: FROM ozf_offers
9257: WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id;
9258:
9259: CURSOR c_budget_required IS
9260: SELECT attr_available_flag

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

9276: CURSOR c_na_line IS
9277: SELECT COUNT(*)
9278: FROM ozf_offer_discount_lines
9279: WHERE offer_id =
9280: (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_modifier_list_rec.qp_list_header_id);
9281:
9282: CURSOR c_prg_buy_count IS
9283: SELECT COUNT(*)
9284: FROM qp_list_lines

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

9306:
9307: CURSOR c_vo_line (p_qp_list_header_id NUMBER)IS
9308: SELECT count(*) FROM ozf_offer_discount_products a, ozf_offer_discount_lines b
9309: WHERE a.offer_discount_line_id = b.offer_discount_line_id
9310: AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);
9311:
9312: CURSOR c_emptyDiscStruct(cp_qpListheaderId NUMBER) IS
9313: SELECT 1 FROM
9314: ozf_offer_discount_lines a , ozf_offers b

Line 9314: ozf_offer_discount_lines a , ozf_offers b

9310: AND b.offer_id = (SELECT offer_id FROM ozf_offers WHERE qp_list_header_id = p_qp_list_header_id);
9311:
9312: CURSOR c_emptyDiscStruct(cp_qpListheaderId NUMBER) IS
9313: SELECT 1 FROM
9314: ozf_offer_discount_lines a , ozf_offers b
9315: WHERE a.tier_type = 'PBH'
9316: AND a.offer_id = b.offer_id --8013
9317: AND
9318: ( NOT EXISTS(SELECT 'X' FROM ozf_offer_discount_products WHERE offer_discount_line_id = a.offer_discount_line_id AND excluder_flag = 'N')

Line 9511: OZF_Utility_PVT.debug_message(FND_LOG.LEVEL_STATEMENT,l_full_name,'ozf_offers_pvt.process_modifiers before calling offer_dates getDateQualifier = '|| getDateQualifier(p_qpListHeaderId => p_modifier_list_rec.qp_list_header_id));

9507: IF p_modifier_list_rec.offer_type = 'VOLUME_OFFER' THEN
9508: l_modifier_list_rec.offer_type := p_modifier_list_rec.volume_offer_type;
9509: END IF;
9510:
9511: OZF_Utility_PVT.debug_message(FND_LOG.LEVEL_STATEMENT,l_full_name,'ozf_offers_pvt.process_modifiers before calling offer_dates getDateQualifier = '|| getDateQualifier(p_qpListHeaderId => p_modifier_list_rec.qp_list_header_id));
9512: IF p_modifier_list_rec.modifier_operation = 'UPDATE' THEN
9513: IF getDateQualifier(p_qpListHeaderId => p_modifier_list_rec.qp_list_header_id) = 'A'
9514: THEN
9515: offer_dates(

Line 9936: UPDATE ozf_offers

9932: RAISE Fnd_Api.g_exc_unexpected_error;
9933: END IF;
9934: ELSIF l_status_code = 'DRAFT' THEN
9935: -- CP validation fails. update offer to DRAFT
9936: UPDATE ozf_offers
9937: SET status_code = 'DRAFT'
9938: ,user_status_id = OZF_Utility_PVT.get_default_user_status ('OZF_OFFER_STATUS', 'DRAFT')
9939: ,status_date = SYSDATE
9940: ,object_version_number = object_version_number + 1

Line 10333: FROM ozf_offers

10329: p_qpListHeaderId IN NUMBER
10330: ) RETURN VARCHAR2 IS
10331: CURSOR c_accrualFlag(cp_qpListHeaderId NUMBER) IS
10332: SELECT decode( VOLUME_OFFER_TYPE, 'ACCRUAL','Y','N')
10333: FROM ozf_offers
10334: WHERE qp_list_header_id = cp_qpListHeaderId;
10335: l_accrualFlag VARCHAR2(1);
10336: BEGIN
10337: OPEN c_accrualFlag(cp_qpListHeaderId => p_qpListHeaderId);

Line 10708: FROM ozf_offer_discount_products a, ozf_offers b

10704: )
10705: IS
10706: CURSOR c_exclusions(cp_qpListHeaderId IN NUMBER) IS
10707: SELECT a.product_attribute, a.product_attr_value, a.excluder_flag
10708: FROM ozf_offer_discount_products a, ozf_offers b
10709: WHERE a.offer_id = b.offer_id
10710: AND a.excluder_flag = 'Y'
10711: AND b.qp_list_header_id = cp_qpListHeaderId;
10712: i NUMBER;

Line 11035: FROM ozf_offer_discount_products a, ozf_offers b

11031: l_api_version_number CONSTANT NUMBER := 1.0;
11032:
11033: CURSOR c_products(cp_qpListHeaderId NUMBER)IS
11034: SELECT a.off_discount_product_id
11035: FROM ozf_offer_discount_products a, ozf_offers b
11036: WHERE a.offer_id = b.offer_id
11037: AND a.excluder_flag = 'N'
11038: AND b.qp_list_header_id = cp_qpListHeaderId;
11039:

Line 11184: FROM ozf_offers

11180: offer_code,
11181: tier_level,
11182: object_version_number,
11183: budget_source_id --//Bug 10648426
11184: FROM ozf_offers
11185: WHERE qp_list_header_id = l_qp_list_header_id;
11186:
11187:
11188: CURSOR c_sdr_info(l_sdr_header_id NUMBER) IS

Line 12164: FROM ozf_offers

12160:
12161:
12162: CURSOR c_offer_info(l_qp_list_header_id NUMBER) IS
12163: SELECT offer_id, offer_type, custom_setup_id, offer_code, tier_level, object_version_number, transaction_currency_code, user_status_id
12164: FROM ozf_offers
12165: WHERE qp_list_header_id = l_qp_list_header_id;
12166:
12167: CURSOR c_user_status_id(l_new_status VARCHAR2) IS
12168: SELECT min(user_status_id)