[Home] [Help]
1113: -- pass header_id to resolve perf issue
1114: CURSOR c_is_parent_line(p_header_id IN NUMBER,
1115: p_line_id IN NUMBER) IS
1116: SELECT 1
1117: FROM oe_order_lines_all
1118: WHERE header_id = p_header_id
1119: AND split_from_line_id = p_line_id;
1120:
1121: CURSOR c_utilization_id (p_line_id IN NUMBER, p_object_id IN NUMBER,
2434:
2435: --Added for bug 7030415, get order's org_id
2436: CURSOR c_order_org_id (p_line_id IN NUMBER) IS
2437: SELECT header.org_id
2438: FROM oe_order_lines_all line, oe_order_headers_all header
2439: WHERE line_id = p_line_id
2440: AND line.header_id = header.header_id;
2441:
2442: CURSOR c_offer_type (p_offer_id IN NUMBER) IS
2845: line.shipping_quantity2, -- Catch Weight ER
2846: line.shipping_quantity_uom2, -- Catch Weight ER
2847: line.fulfillment_base, -- Catch Weight ER
2848: line.order_quantity_uom -- Catch Weight ER
2849: FROM oe_order_lines_all line, oe_order_headers_all header
2850: WHERE line_id = p_line_id
2851: AND line.header_id = header.header_id;
2852:
2853: -- Catch Weight ER - start
2873: AND object_type = 'ORDER';
2874:
2875: CURSOR c_order_count (p_header_id IN NUMBER) IS
2876: SELECT SUM (ordered_quantity - NVL (cancelled_quantity, 0))
2877: FROM oe_order_lines_all
2878: WHERE header_id = p_header_id;
2879:
2880: -- Added adjusted_amount for bug fix 4173825
2881: CURSOR c_mod_level (p_price_ad_id IN NUMBER) IS
2930: AND price_adjustment_id IS NULL;
2931:
2932: CURSOR c_split_line(p_line_id IN NUMBER) IS
2933: SELECT line_id
2934: FROM oe_order_lines_all
2935: WHERE split_from_line_id IS NOT NULL
2936: AND line_id = p_line_id
2937: AND split_by = 'SYSTEM';
2938:
2949: AND off.qp_list_header_id = qp.list_header_id;
2950:
2951: CURSOR c_is_parent_line (p_line_id IN NUMBER) IS
2952: SELECT 1
2953: FROM oe_order_lines_all
2954: WHERE split_from_line_id = p_line_id;
2955:
2956: l_is_parent_line NUMBER;
2957:
3109: FETCH c_offer_details INTO l_custom_setup_id, l_offer_name;
3110: CLOSE c_offer_details;
3111:
3112: /*If its SDR offer then set context to order's org. get profile value which stores the column name
3113: of oe_order_lines_all where purchase price is stored. Using the column name generate a
3114: dynamic sql and execute the ref cursor.*/
3115:
3116: IF l_custom_setup_id = 118 THEN --ER9447673
3117: MO_GLOBAL.set_policy_context('S', l_order_org_id);
3117: MO_GLOBAL.set_policy_context('S', l_order_org_id);
3118: l_column_name := FND_PROFILE.VALUE('OZF_ITEM_COST_COLUMN');
3119:
3120: IF l_column_name IS NOT NULL AND l_column_name <> FND_API.G_MISS_CHAR THEN
3121: l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1 AND org_id = :2';
3122:
3123: IF g_debug_flag = 'Y' THEN
3124: ozf_utility_pvt.write_conc_log('Query to obtain purchase price '||l_stmt);
3125: END IF;
3315: MO_GLOBAL.set_policy_context('S', l_order_org_id);
3316: l_column_name := FND_PROFILE.VALUE('OZF_ITEM_COST_COLUMN');
3317:
3318: IF l_column_name IS NOT NULL AND l_column_name <> FND_API.G_MISS_CHAR THEN
3319: l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1 AND org_id = :2';
3320:
3321: IF g_debug_flag = 'Y' THEN
3322: ozf_utility_pvt.write_conc_log('Query to obtain purchase price '||l_stmt);
3323: END IF;
3931:
3932:
3933: CURSOR c_actual_shipment_date(p_line_id IN NUMBER) IS
3934: SELECT actual_shipment_date, shipping_quantity, shipping_quantity_uom, shipping_quantity2, shipping_quantity_uom2, fulfillment_base
3935: FROM oe_order_lines_all
3936: WHERE line_id = p_line_id;
3937:
3938: --fix for bug 13824967
3939: CURSOR c_invoice_date(p_line_id IN NUMBER, p_order_number IN VARCHAR2) IS
3947:
3948: -- add by feliu on 08/03/04, get split line id to use in create postivie adjustment.
3949: CURSOR c_split_line(p_line_id IN NUMBER, p_header_id IN NUMBER) IS
3950: SELECT line_id
3951: FROM oe_order_lines_all
3952: WHERE header_id = p_header_id
3953: AND split_from_line_id = p_line_id
3954: AND split_by = 'SYSTEM';
3955:
3967: AND object_type = 'ORDER';
3968:
3969: CURSOR c_orig_order_info (p_line_id IN NUMBER) IS
3970: SELECT NVL(shipped_quantity,ordered_quantity)
3971: FROM oe_order_lines_all
3972: WHERE line_id =p_line_id;
3973:
3974: CURSOR c_orig_adjustment_amount (p_order_line_id IN NUMBER) IS
3975: SELECT plan_curr_amount, amount, fund_request_amount, --nirprasa, ER 8399134
4024: DECODE(line.line_category_code,'ORDER',NVL(line.shipped_quantity,0),
4025: 'RETURN', line.invoiced_quantity,
4026: line.ordered_quantity) shipped_quantity
4027:
4028: FROM oe_order_lines_all line, oe_order_headers_all header
4029: WHERE line.line_id = p_order_line_id
4030: AND line.header_id = header.header_id;
4031:
4032:
4404: IF l_custom_setup_id = 118 THEN
4405: l_column_name := FND_PROFILE.VALUE('OZF_ITEM_COST_COLUMN');
4406:
4407: IF l_column_name IS NOT NULL AND l_column_name <> FND_API.G_MISS_CHAR THEN
4408: l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1';
4409:
4410: IF g_debug_flag = 'Y' THEN
4411: ozf_utility_pvt.write_conc_log('Query to obtain purchase price ' || l_stmt);
4412: END IF;
5708: util.order_line_id OrderLineId,
5709: NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0)) ShippedQuantity,
5710: (NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0))*line.unit_selling_price) Revenue,
5711: NVL(util.plan_curr_amount,0) AccrualAmount
5712: FROM oe_order_lines_all line, ozf_funds_utilized_all_b util,
5713: ozf_offers off, oe_price_adjustments oe,
5714: ozf_xref_map map
5715: WHERE line.line_id = util.order_line_id
5716: AND line.header_id = util.object_id
5736: util.cust_account_id CustomerId,
5737: NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0)) ShippedQuantity,
5738: (NVL(line.invoiced_quantity, NVL(line.shipped_quantity, 0))*line.unit_selling_price) Revenue,
5739: NVL(util.plan_curr_amount,0) AccrualAmount
5740: FROM oe_order_lines_all line, ozf_funds_utilized_all_b util,
5741: ozf_offers off, oe_price_adjustments oe,
5742: ozf_xref_map map
5743: WHERE line.line_id = util.order_line_id
5744: AND line.header_id = util.object_id