[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
210: header.transactional_curr_code, line.invoice_to_org_id,
211: line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
212: header.order_number, header.org_id, line.order_quantity_uom, line.pricing_quantity_uom
213: FROM oe_order_lines_all line, oe_order_headers_all header,
214: (SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
215: WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
216: BETWEEN p_start_date AND p_end_date
217: AND line.booked_flag = 'Y'
218: AND line.cancelled_flag = 'N'
227: header.transactional_curr_code, line.invoice_to_org_id,
228: line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
229: header.order_number, header.org_id, line.order_quantity_uom, line.pricing_quantity_uom
230: FROM oe_order_lines_all line, oe_order_headers_all header,
231: (SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
232: WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
233: BETWEEN p_start_date AND p_end_date
234: AND line.booked_flag = 'Y'
235: AND line.cancelled_flag = 'N'
238: AND line.header_id = header.header_id;
239:
240: CURSOR c_count_temp IS
241: SELECT COUNT(DISTINCT eligibility_id)
242: FROM ozf_temp_eligibility;
243:
244: -- Segment and buying group has no acct info. use party_id for validation
245: CURSOR c_party_id(p_sold_to_org_id IN NUMBER) IS
246: SELECT party_id
321:
322: x_return_status := Fnd_Api.g_ret_sts_success;
323:
324: --kdass 28-FEB-2006 fixed bug 5059735 - denorm offer's product eligibility to handle all types of product levels
325: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
326:
327: FND_DSQL.init;
328: FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, eligibility_id) ');
329: FND_DSQL.add_text('(SELECT ''OFFR'', product_id ' );
324: --kdass 28-FEB-2006 fixed bug 5059735 - denorm offer's product eligibility to handle all types of product levels
325: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
326:
327: FND_DSQL.init;
328: FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, eligibility_id) ');
329: FND_DSQL.add_text('(SELECT ''OFFR'', product_id ' );
330: FND_DSQL.add_text(' FROM ( ');
331:
332: /*kdass 05-MAY-2006 bug 5205721 - use refresh_products() as it considers excluded items
1973: WHERE adj_line.offer_adjustment_id = p_offer_adjustment_id
1974: AND adj_line.offer_discount_line_id = orig_line.offer_discount_line_id;
1975:
1976:
1977: --ozf_temp_eligibility table can have huge volume of data given that the
1978: --prod categories will be denormed and added there.
1979: --Given that it is joined with oe_order_lines_all we need to go via
1980: --perf team's suggestion on the HINTs, so it requires now 2 cursors
1981: -- Reason for 2 more cursors is explained below
2881: -- commit happens after all processing
2882: --only check what happens if out of multiple errors candidate for
2883: --perform_adjustment, one errors out.
2884: --commit may be per adj for perform_adj
2885: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
2886: l_volume_offer := 'N';
2887:
2888:
2889: OPEN c_adjusted_offer_cur;
6025: ,util.reference_type
6026: ,util.reference_id
6027: ,util.exchange_rate_type
6028: FROM ozf_funds_utilized_all_b util,
6029: ozf_temp_eligibility temp,
6030: ozf_offer_adjustment_lines adjl,
6031: oe_order_lines_all ol,
6032: oe_price_adjustments oe
6033: WHERE util.plan_type = 'OFFR'
6082: ,util.reference_type
6083: ,util.reference_id
6084: ,util.exchange_rate_type
6085: FROM ozf_funds_utilized_all_b util,
6086: ozf_temp_eligibility temp,
6087: ozf_offer_adjustment_lines adjl,
6088: OZF_RESALE_ADJUSTMENTS_ALL oe
6089: WHERE util.plan_type = 'OFFR'
6090: AND product_id IS NOT NULL
6124: FROM ozf_funds_utilized_all_b util,
6125: oe_price_adj_assocs assocs,
6126: oe_price_adjustments oe,
6127: ozf_offer_adjustment_lines adjl,
6128: ozf_temp_eligibility temp
6129: WHERE util.plan_id = p_qp_list_header_id
6130: AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
6131: AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
6132: AND util.plan_type = 'OFFR'
6136: AND oe.list_line_id = adjl.list_line_id
6137: AND oe.operand <> adjl.modified_discount
6138: AND adjustment_date BETWEEN p_from_date AND l_to_date
6139: AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
6140: --- (-1) is inserted in ozf_temp_eligibility for 'ALL' items, refer query above and query below
6141: */
6142: CURSOR c_bdadj_trade_deal IS
6143: SELECT util.utilization_id,
6144: util.object_type,
6167: ,util.reference_type
6168: ,util.reference_id
6169: ,util.exchange_rate_type
6170: FROM ozf_funds_utilized_all_b util,
6171: ozf_temp_eligibility temp,
6172: ozf_offer_adjustment_lines adjl,
6173: oe_order_lines_all ol,
6174: oe_price_adjustments oe
6175: WHERE util.plan_type = 'OFFR'
6221: ,util.reference_type
6222: ,util.reference_id
6223: ,util.exchange_rate_type
6224: FROM ozf_funds_utilized_all_b util,
6225: ozf_temp_eligibility temp,
6226: ozf_offer_adjustment_lines adjl,
6227: OZF_RESALE_ADJUSTMENTS_ALL oe
6228: WHERE util.plan_type = 'OFFR'
6229: AND product_id IS NOT NULL
6271: ,util.reference_type
6272: ,util.reference_id
6273: ,util.exchange_rate_type
6274: FROM ozf_funds_utilized_all_b util,
6275: ozf_temp_eligibility temp,
6276: ozf_offer_adjustment_lines adjl,
6277: oe_order_lines_all ol,
6278: oe_price_adjustments oe
6279: WHERE util.plan_type = 'OFFR'
6326: ,util.reference_type
6327: ,util.reference_id
6328: ,util.exchange_rate_type
6329: FROM ozf_funds_utilized_all_b util,
6330: ozf_temp_eligibility temp,
6331: ozf_offer_adjustment_lines adjl,
6332: OZF_RESALE_ADJUSTMENTS_ALL oe
6333: WHERE util.plan_type = 'OFFR'
6334: AND product_id IS NOT NULL
6418: ,util.reference_type
6419: ,util.reference_id
6420: ,util.exchange_rate_type
6421: FROM ozf_funds_utilized_all_b util,
6422: ozf_temp_eligibility temp,
6423: ozf_offer_adjustment_lines adjl,
6424: oe_order_lines_all ol,
6425: oe_price_adjustments oe
6426: WHERE util.plan_type = 'OFFR'
6840: BEGIN
6841: -- ozf_utility_pvt.debug_message('enter validate_product_budget obj_id=' || p_object_id || ' budget_id=' || p_budget_id);
6842:
6843: x_return_status := fnd_api.g_ret_sts_success;
6844: EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
6845: write_conc_log ( l_full_name
6846: || ' : '
6847: || 'In Process Product '
6848: || p_offer_adjustment_id);
6851: FOR product_rec IN c_off_adj_lines
6852: LOOP
6853:
6854: FND_DSQL.init;
6855: FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) ');
6856: FND_DSQL.add_text('(SELECT ''FUND'', ''N'', product_id,' );
6857: FND_DSQL.add_text(product_rec.offer_adjustment_line_id );
6858: FND_DSQL.add_text(' FROM ( ');
6859:
6882: || 'Get Sql Returns'
6883: || l_temp_sql);
6884: ----DBMS_output.put_line ( 'get sql returns' || l_temp_sql);
6885: /*
6886: l_adjustment_product_sql := 'INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) '
6887: || '(SELECT ''FUND'', ''N'', product_id,'
6888: || product_rec.offer_adjustment_line_id
6889: || ' FROM ( '
6890: || l_temp_sql
7423: x_msg_data OUT NOCOPY VARCHAR2
7424: ) /*
7425: select product_id , cust_account_id , fund_id , sum(DECODE(adjl.arithmetic_operator,'AMOUNT', (adjl.modified_discount - adjl.original_discount),
7426: (( adjl.modified_discount - adjl.original_discount)* amount/adjl.original_discount))) AMount
7427: from ozf_funds_utilized_all_vl util , ozf_temp_eligibility temp,
7428: ozf_offer_adjustment_lines adjl
7429: where util.plan_type = 'OFFR'
7430: and product_id IS NOT NULL and util.plan_id = 7909
7431: and util.product_id = temp.eligibility_id