[Home] [Help]
591: --
592: END IF;
593:
594: FND_DSQL.init;
595: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
596: FND_DSQL.add_text('SELECT inventory_item_id,');
597: FND_DSQL.add_bind(l_product.product_level);
598: FND_DSQL.add_text(',');
599: FND_DSQL.add_bind(l_discount);
771: --
772: -- END IF;
773:
774: FND_DSQL.init;
775: FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
776: FND_DSQL.add_text('SELECT inventory_item_id,');
777: FND_DSQL.add_bind(l_product.product_level);
778: FND_DSQL.add_text(',');
779: FND_DSQL.add_bind(l_discount.discount);
867: IS
868: --
869: CURSOR c_disc_for_item_count IS
870: SELECT COUNT(*)
871: FROM ozf_na_products_temp
872: WHERE inventory_item_id = p_product_id
873: AND product_level = 'PRODUCT';
874:
875: CURSOR c_discount_for_cat IS
878: volume_type,
879: volume_from,
880: volume_to,
881: uom
882: FROM ozf_na_products_temp
883: WHERE inventory_item_id = p_product_id
884: AND product_level = 'FAMILY';
885:
886: CURSOR c_discount_for_item IS
888: discount_type,
889: volume_type,
890: volume_from,
891: volume_to, uom
892: FROM ozf_na_products_temp
893: WHERE inventory_item_id = p_product_id
894: AND product_level = 'PRODUCT';
895:
896: l_max_accrual NUMBER;
1464: ol.unit_selling_price,
1465: ol.org_id,
1466: NVL(ol.actual_shipment_date,ol.fulfillment_date) conv_date,
1467: oh.transactional_curr_code
1468: FROM ( SELECT /*+ no_merge */ DISTINCT INVENTORY_ITEM_ID FROM OZF_NA_PRODUCTS_TEMP ) na,
1469: oe_order_lines_all ol,
1470: oe_order_headers_all oh
1471: WHERE ol.inventory_item_id = na.inventory_item_id
1472: AND ol.flow_status_code IN ('SHIPPED','CLOSED')
1500: NVL(exchange_rate_date, date_ordered) conv_date,
1501: currency_code transactional_curr_code
1502: FROM ozf_resale_lines_all
1503: WHERE inventory_item_id IN ( SELECT na.inventory_item_id
1504: FROM ozf_na_products_temp na)
1505: -- AND ol.flow_status_code IN ('SHIPPED','CLOSED')
1506: -- AND ol.cancelled_flag = 'N'
1507: -- AND ol.line_category_code <> 'RETURN'
1508: AND TRUNC(date_ordered) >= TRUNC(p_offer_start_date)
1532: a.customer_trx_line_id
1533: FROM ra_customer_trx_lines_all a,
1534: ra_customer_trx_all b
1535: WHERE a.inventory_item_id IN ( SELECT na.inventory_item_id
1536: FROM ozf_na_products_temp na)
1537: AND a.line_type = 'LINE'
1538: AND a.customer_trx_id = b.customer_trx_id
1539: AND b.complete_flag = 'Y'
1540: AND b.cust_trx_type_id = p_cust_trx_type_id
1562: ol.unit_selling_price,
1563: ol.org_id,
1564: NVL(ol.actual_arrival_date,ol.fulfillment_date) conv_date,
1565: oh.transactional_curr_code
1566: FROM ( SELECT /*+ no_merge */ DISTINCT INVENTORY_ITEM_ID FROM OZF_NA_PRODUCTS_TEMP ) NA,
1567: oe_order_lines_all ol,
1568: oe_order_headers_all oh
1569: WHERE ol.inventory_item_id = na.inventory_item_id
1570: AND ol.open_flag = 'N'
1599: a.currency_code,
1600: a.org_id --Added for bug 7030415
1601: FROM ozf_funds_utilized_all_b a,
1602: ozf_offers b,
1603: ozf_na_products_temp c
1604: WHERE a.plan_type = 'OFFR'
1605: AND a.plan_id = b.qp_list_header_id
1606: AND b.qp_list_header_id <> p_qp_list_header_id
1607: AND a.adjustment_date BETWEEN p_start_date and p_end_date
1813: l_offer_org_id := l_net_accrual_offers.orig_org_id;
1814: END IF;
1815:
1816: DELETE FROM ozf_na_customers_temp;
1817: DELETE FROM ozf_na_products_temp;
1818:
1819: ----------------- Denrom Customers ------------------
1820: IF l_net_accrual_offers.custom_setup_id = 105
1821: THEN
3485:
3486: <
3487:
3488: DELETE FROM ozf_na_customers_temp;
3489: DELETE FROM ozf_na_products_temp;
3490:
3491: END LOOP;
3492:
3493: ozf_utility_pvt.write_conc_log('-- Done -- ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));