[Home] [Help]
10: -- process_offer_product
11: -- HISTORY
12: -- 4/18/2002 Mumu Pande Create.
13: -- 10/17/2003 Ying Zhao fix bug 3197570 - BACKDATED ADJUSTMENTS FOR AMOUNT TYPE DICOUNT RULE
14: -- 12/14/2003 kdass changed table name from ams_temp_eligibility to ozf_temp_eligibility
15: -- 02/09/2004 yzhao fix bug MASS1R1011510:REOP:VOLUME OFFER DISCOUNT LEVL NOT CHANGING EVEN AFTER REACHG VOL
16: -- offer notes object should remain in AMS_OFFR, not OZF_OFFR
17: -- 07/08/2004 kdass changed the dynamic cursors in perform_adjustment to static cursors
18: -- 07/19/2004 kdass fix for 11.5.9 bug 3742174
177: header.transactional_curr_code, line.invoice_to_org_id,
178: line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
179: header.order_number
180: FROM oe_order_lines_all line, oe_order_headers_all header,
181: (SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
182: WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
183: BETWEEN p_start_date AND p_end_date
184: AND line.booked_flag = 'Y'
185: AND line.cancelled_flag = 'N'
194: header.transactional_curr_code, line.invoice_to_org_id,
195: line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
196: header.order_number
197: FROM oe_order_lines_all line, oe_order_headers_all header,
198: (SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
199: WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
200: BETWEEN p_start_date AND p_end_date
201: AND line.booked_flag = 'Y'
202: AND line.cancelled_flag = 'N'
205: AND line.header_id = header.header_id;
206:
207: CURSOR c_count_temp IS
208: SELECT COUNT(DISTINCT eligibility_id)
209: FROM ozf_temp_eligibility;
210:
211: -- Segment and buying group has no acct info. use party_id for validation
212: CURSOR c_party_id(p_sold_to_org_id IN NUMBER) IS
213: SELECT party_id
280:
281: x_return_status := Fnd_Api.g_ret_sts_success;
282:
283: --kdass 28-FEB-2006 fixed bug 5059735 - denorm offer's product eligibility to handle all types of product levels
284: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
285:
286: FND_DSQL.init;
287: FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, eligibility_id) ');
288: FND_DSQL.add_text('(SELECT ''OFFR'', product_id ' );
283: --kdass 28-FEB-2006 fixed bug 5059735 - denorm offer's product eligibility to handle all types of product levels
284: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
285:
286: FND_DSQL.init;
287: FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, eligibility_id) ');
288: FND_DSQL.add_text('(SELECT ''OFFR'', product_id ' );
289: FND_DSQL.add_text(' FROM ( ');
290:
291: /*kdass 05-MAY-2006 bug 5205721 - use refresh_products() as it considers excluded items
2112: ,util.bill_to_site_use_id
2113: ,util.reference_type
2114: ,util.reference_id
2115: FROM ozf_funds_utilized_all_b util,
2116: ozf_temp_eligibility temp,
2117: ozf_offer_adjustment_lines adjl,
2118: oe_order_lines_all ol,
2119: oe_price_adjustments oe
2120: WHERE util.plan_type = 'OFFR'
2160: ,util.bill_to_site_use_id
2161: ,util.reference_type
2162: ,util.reference_id
2163: FROM ozf_funds_utilized_all_b util,
2164: ozf_temp_eligibility temp,
2165: ozf_offer_adjustment_lines adjl,
2166: OZF_RESALE_ADJUSTMENTS_ALL oe
2167: WHERE util.plan_type = 'OFFR'
2168: AND product_id IS NOT NULL
2197: FROM ozf_funds_utilized_all_b util,
2198: oe_price_adj_assocs assocs,
2199: oe_price_adjustments oe,
2200: ozf_offer_adjustment_lines adjl,
2201: ozf_temp_eligibility temp
2202: WHERE util.plan_id = p_qp_list_header_id
2203: AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2204: AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2205: AND util.plan_type = 'OFFR'
2209: AND oe.list_line_id = adjl.list_line_id
2210: AND oe.operand <> adjl.modified_discount
2211: AND adjustment_date BETWEEN p_from_date AND l_to_date
2212: AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
2213: --- (-1) is inserted in ozf_temp_eligibility for 'ALL' items, refer query above and query below
2214: */
2215: CURSOR c_bdadj_trade_deal IS
2216: SELECT util.utilization_id,
2217: util.object_type,
2238: ,util.bill_to_site_use_id
2239: ,util.reference_type
2240: ,util.reference_id
2241: FROM ozf_funds_utilized_all_b util,
2242: ozf_temp_eligibility temp,
2243: ozf_offer_adjustment_lines adjl,
2244: oe_order_lines_all ol,
2245: oe_price_adjustments oe
2246: WHERE util.plan_type = 'OFFR'
2284: ,util.bill_to_site_use_id
2285: ,util.reference_type
2286: ,util.reference_id
2287: FROM ozf_funds_utilized_all_b util,
2288: ozf_temp_eligibility temp,
2289: ozf_offer_adjustment_lines adjl,
2290: OZF_RESALE_ADJUSTMENTS_ALL oe
2291: WHERE util.plan_type = 'OFFR'
2292: AND product_id IS NOT NULL
2327: ,util.bill_to_site_use_id
2328: ,util.reference_type
2329: ,util.reference_id
2330: FROM ozf_funds_utilized_all_b util,
2331: ozf_temp_eligibility temp,
2332: ozf_offer_adjustment_lines adjl,
2333: oe_order_lines_all ol,
2334: oe_price_adjustments oe
2335: WHERE util.plan_type = 'OFFR'
2374: ,util.bill_to_site_use_id
2375: ,util.reference_type
2376: ,util.reference_id
2377: FROM ozf_funds_utilized_all_b util,
2378: ozf_temp_eligibility temp,
2379: ozf_offer_adjustment_lines adjl,
2380: OZF_RESALE_ADJUSTMENTS_ALL oe
2381: WHERE util.plan_type = 'OFFR'
2382: AND product_id IS NOT NULL
2457: ,util.bill_to_site_use_id
2458: ,util.reference_type
2459: ,util.reference_id
2460: FROM ozf_funds_utilized_all_b util,
2461: ozf_temp_eligibility temp,
2462: ozf_offer_adjustment_lines adjl,
2463: oe_order_lines_all ol,
2464: oe_price_adjustments oe
2465: WHERE util.plan_type = 'OFFR'
2812: BEGIN
2813: -- ozf_utility_pvt.debug_message('enter validate_product_budget obj_id=' || p_object_id || ' budget_id=' || p_budget_id);
2814:
2815: x_return_status := fnd_api.g_ret_sts_success;
2816: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
2817: write_conc_log ( l_full_name
2818: || ' : '
2819: || 'In Process Product '
2820: || p_offer_adjustment_id);
2823: FOR product_rec IN c_off_adj_lines
2824: LOOP
2825:
2826: FND_DSQL.init;
2827: FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) ');
2828: FND_DSQL.add_text('(SELECT ''FUND'', ''N'', product_id,' );
2829: FND_DSQL.add_text(product_rec.offer_adjustment_line_id );
2830: FND_DSQL.add_text(' FROM ( ');
2831:
2854: || 'Get Sql Returns'
2855: || l_temp_sql);
2856: ----DBMS_output.put_line ( 'get sql returns' || l_temp_sql);
2857: /*
2858: l_adjustment_product_sql := 'INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) '
2859: || '(SELECT ''FUND'', ''N'', product_id,'
2860: || product_rec.offer_adjustment_line_id
2861: || ' FROM ( '
2862: || l_temp_sql
3384: x_msg_data OUT NOCOPY VARCHAR2
3385: ) /*
3386: select product_id , cust_account_id , fund_id , sum(DECODE(adjl.arithmetic_operator,'AMOUNT', (adjl.modified_discount - adjl.original_discount),
3387: (( adjl.modified_discount - adjl.original_discount)* amount/adjl.original_discount))) AMount
3388: from ozf_funds_utilized_all_vl util , ozf_temp_eligibility temp,
3389: ozf_offer_adjustment_lines adjl
3390: where util.plan_type = 'OFFR'
3391: and product_id IS NOT NULL and util.plan_id = 7909
3392: and util.product_id = temp.eligibility_id