[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT /*+ leading(temp) use_nl(temp line header) */
line.header_id, line.line_id, line.inventory_item_id, line.unit_list_price,
NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)) quantity,
header.transactional_curr_code, line.invoice_to_org_id,
line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
header.order_number
FROM oe_order_lines_all line, oe_order_headers_all header,
(SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
BETWEEN p_start_date AND p_end_date
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
--AND line.line_category_code <> 'RETURN'
AND line.inventory_item_id = temp.eligibility_id
AND line.header_id = header.header_id;
SELECT /*+ parallel(line) */
line.header_id, line.line_id, line.inventory_item_id, line.unit_list_price,
NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)) quantity,
header.transactional_curr_code, line.invoice_to_org_id,
line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
header.order_number
FROM oe_order_lines_all line, oe_order_headers_all header,
(SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
BETWEEN p_start_date AND p_end_date
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
--AND line.line_category_code <> 'RETURN'
AND line.inventory_item_id = temp.eligibility_id
AND line.header_id = header.header_id;
SELECT COUNT(DISTINCT eligibility_id)
FROM ozf_temp_eligibility;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_sold_to_org_id;
SELECT 'Y', object_type, qp_qualifier_group
FROM ozf_activity_customers
WHERE (
(site_use_id = p_invoice_to_org_id AND site_use_code = 'BILL_TO') OR
(site_use_id = p_ship_to_org_id AND site_use_code = 'SHIP_TO') OR
(party_id = p_party_id AND site_use_code IS NULL) OR
(party_id = -1)
)
AND object_class = 'OFFR'
AND object_id = p_list_header_id
AND ROWNUM = 1;
SELECT 'Y', object_type, qp_qualifier_group
FROM ozf_activity_customers
WHERE (
(cust_account_id = p_sold_to_org_id) OR
(party_id = p_party_id AND site_use_code IS NULL) OR
(party_id = -1)
)
AND object_class = 'OFFR'
AND object_id = p_list_header_id
AND ROWNUM = 1;
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, eligibility_id) ');
FND_DSQL.add_text('(SELECT ''OFFR'', product_id ' );
SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code
, beneficiary_account_id,autopay_party_attr,autopay_party_id -- Added for bug 7030415, correct org_id in accrual records
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT cust.cust_account_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use,
hz_cust_accounts cust,
oe_order_headers_all header
WHERE header.header_id = p_header_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND acct_site.cust_account_id = cust.cust_account_id
AND site_use.site_use_id = header.invoice_to_org_id ;
SELECT SUM(amount)
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND object_type = 'ORDER'
AND object_id = p_object_id
AND order_line_id = p_order_line_id
AND product_level_type = 'PRODUCT'
AND product_id = p_prod_id
AND utilization_type NOT IN ('REQUEST', 'TRANSFER'); --kdass 29-MAR-2006 bug 5120491
SELECT SUM(amount)
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND order_line_id = p_order_line_id
AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
SELECT NVL(invoiced_quantity, NVL(shipped_quantity, 0)) quantity,
ship_to_org_id, invoice_to_org_id
FROM oe_order_lines_all
WHERE line_id = p_order_line_id;
SELECT header.org_id
FROM oe_order_lines_all line, oe_order_headers_all header
WHERE line_id = p_line_id
AND line.header_id = header.header_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
SELECT autopay_party_attr,autopay_party_id
FROM ozf_offers
WHERE qp_list_header_id = p_offer_id;
SELECT oq.list_line_id, op.product_attribute, op.product_attr_value
FROM ozf_offer_discount_products op, ozf_qp_discounts oq
WHERE (op.product_attr_value = p_product_id OR op.product_attr_value = 'ALL')
AND op.offer_id = p_offer_id
AND op.offer_discount_line_id = oq.offer_discount_line_id
AND rownum = 1;
SELECT actual_shipment_date, shipped_quantity, flow_status_code, invoice_interface_status_code,
invoiced_quantity, sold_to_org_id, invoice_to_org_id, ship_to_org_id, shipping_quantity_uom,
order_quantity_uom, unit_selling_price, org_id, ordered_quantity
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT cust.trx_date -- transaction(invoice) date
FROM ra_customer_trx_all cust
, ra_customer_trx_lines_all cust_lines
WHERE cust.customer_trx_id = cust_lines.customer_trx_id
AND cust_lines.sales_order = p_order_number -- added condition for partial index for bug fix 3917556
AND cust_lines.interface_line_attribute6 = TO_CHAR(p_line_id);
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_account_id;
SELECT a.party_site_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b
WHERE b.site_use_id = p_account_site_id
AND b.cust_acct_site_id = a.cust_acct_site_id;
SELECT 1 FROM DUAL WHERE EXISTS
( SELECT 1
FROM ozf_sales_transactions_all trx
WHERE trx.line_id = p_line_id
AND source_code = 'OM');
SELECT 1
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND order_line_id = p_order_line_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code
, beneficiary_account_id, offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT cust.cust_account_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use,
hz_cust_accounts cust,
oe_order_headers_all header
WHERE header.header_id = p_header_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND acct_site.cust_account_id = cust.cust_account_id
AND site_use.site_use_id = header.invoice_to_org_id ;
SELECT NVL(apply_discount_flag,'N')
FROM ozf_offer_discount_products
WHERE offer_id = p_offer_id
AND product_attr_value = p_product_id;
select item_type, ITEMS_CATEGORY
from ozf_activity_products
where object_id = p_list_header_id
and item=p_inventory_item_id;
select condition_id_column
from ozf_denorm_queries
where context='ITEM'
and attribute =p_prod_attr and rownum = 1;
SELECT discount_type,volume_type
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_discount_line_id
AND tier_type = 'PBH';
SELECT group_no,pbh_line_id,include_volume_flag
FROM ozf_order_group_prod
WHERE order_line_id = p_order_line_id
AND qp_list_header_id = p_list_header_id;
SELECT opt.retroactive_flag
FROM ozf_offr_market_options opt
WHERE opt.GROUP_NUMBER= p_group_id
AND opt.qp_list_header_id = p_list_header_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE p_volume > volume_from
AND p_volume <= volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT MIN(volume_from),MAX(volume_to)
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE volume_to =p_max_volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT SUM(amount)
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND object_type = 'ORDER'
AND object_id = p_object_id
AND order_line_id = p_order_line_id
AND product_level_type = 'PRODUCT'
AND product_id = p_prod_id
AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
SELECT SUM(amount)
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND object_type = 'ORDER'
-- AND object_id = p_object_id
AND product_level_type = 'PRODUCT'
AND product_id = p_prod_id
AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
SELECT offer_discount_line_id ,volume_from ,volume_to, discount
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id
AND p_volume >= volume_from
ORDER BY volume_from DESC;
SELECT a.discount
FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
WHERE a.offer_discount_line_id = b.dis_offer_discount_id
AND b.pbh_offer_discount_id = p_pbh_line_id
AND b.offer_market_option_id = c.offer_market_option_id
AND c.qp_list_header_id = p_qp_list_header_id
AND c.group_number = p_group_id;
l_stmt := 'select ' || l_cond_id_column ||
' from mtl_system_items where ORGANIZATION_ID = FND_PROFILE.VALUE(''QP_ORGANIZATION_ID'') and inventory_item_id =:1 and rownum = 1';
SELECT offer_adjustment_id,
list_header_id,
effective_date,
approved_date
FROM ozf_offer_adjustments_b
WHERE status_code = 'ACTIVE'
AND NVL(budget_adjusted_flag,'N') = 'N'
AND effective_date < approved_date; --query only backdated adjustments
SELECT adj.original_discount, adj.modified_discount, lines.arithmetic_operator,
adj.created_from_adjustments, lines.list_line_id, rltd.to_list_line_id
FROM ozf_offer_adjustment_lines adj, qp_list_lines lines, ozf_offer_adj_rltd_lines rltd
WHERE adj.offer_adjustment_id = p_offer_adjustment_id
AND lines.list_line_type_code = 'DIS'
AND lines.list_line_id = adj.list_line_id
AND rltd.from_list_line_id = adj.list_line_id
AND rltd.offer_adjustment_id = adj.offer_adjustment_id;
SELECT off.offer_id, qp.description, qp.NAME,
nvl(off.transaction_currency_code,fund_request_curr_code) transaction_currency_code,
off.reusable, off.offer_type,
--kdass 09-DEC-2005 fix for bug 4872799
trunc(off.start_date) start_date
,off.volume_offer_type
FROM qp_list_headers_all qp, ozf_offers off
WHERE qp.list_header_id = p_list_header_id
AND qp.list_header_id = off.qp_list_header_id;
g_offer_id_tbl.delete;
UPDATE ozf_offer_adjustments_b
SET budget_adjusted_flag = 'Y',
object_version_number = object_version_number + 1,
status_code = 'CLOSED'
WHERE offer_adjustment_id = l_offerAdjustmentIdTbl(i);
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id,
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.arithmetic_operator,
-- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
'%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- ((adjl.modified_discount - oe.operand) * amount / oe.operand)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
oe_order_lines_all ol,
oe_price_adjustments oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
-- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
-- AND adjl.list_line_id = oe.list_line_id
AND oe.list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr
START WITH adjr.from_list_line_id = adjl.list_line_id
AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
)
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'ORDER'
AND util.price_adjustment_id = oe.price_adjustment_id
AND oe.list_line_type_code <> 'PBH'
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
AND ol.line_id = oe.line_id
UNION ALL
--for third party accrual.
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id,
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.operand_calculation_code,
'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.operand),
((adjl.modified_discount - oe.operand) * amount / oe.operand)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
OZF_RESALE_ADJUSTMENTS_ALL oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
AND adjl.list_line_id = oe.list_line_id
AND util.price_adjustment_id = oe.resale_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
-- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'TP_ORDER';
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id,
util.cust_account_id,
util.fund_id,
util.currency_code,
assocs.price_adjustment_id,
--NULL,
DECODE (oe.arithmetic_operator,
'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
((adjl.modified_discount - oe.operand) * amount / oe.operand)
) amount
FROM ozf_funds_utilized_all_b util,
oe_price_adj_assocs assocs,
oe_price_adjustments oe,
ozf_offer_adjustment_lines adjl,
ozf_temp_eligibility temp
WHERE util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
AND util.plan_type = 'OFFR'
AND util.price_adjustment_id = assocs.price_adjustment_id
AND oe.price_adjustment_id = assocs.rltd_price_adj_id
AND oe.adjusted_amount IS NOT NULL
AND oe.list_line_id = adjl.list_line_id
AND oe.operand <> adjl.modified_discount
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id, -- yzhao: 11.5.10 added billto_cust_account_id
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.arithmetic_operator,
-- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
'%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- ((adjl.modified_discount - oe.operand) * amount / oe.operand)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
oe_order_lines_all ol,
oe_price_adjustments oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
-- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
-- AND adjl.list_line_id = oe.list_line_id
AND oe.list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr
START WITH adjr.from_list_line_id = adjl.list_line_id
AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
)
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'ORDER'
AND util.price_adjustment_id = oe.price_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
AND ol.line_id = oe.line_id
UNION ALL
--for accrual in third party accrual.
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id,
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.operand_calculation_code,
'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.operand),
((adjl.modified_discount - oe.operand) * amount / oe.operand)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
OZF_RESALE_ADJUSTMENTS_ALL oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
AND adjl.list_line_id = oe.list_line_id
AND util.price_adjustment_id = oe.resale_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
-- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'TP_ORDER'
UNION -- for off invoice in direct sales
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id, -- yzhao: 11.5.10 added billto_cust_account_id
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL ,
DECODE (oe.arithmetic_operator,
-- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
'%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- ((adjl.modified_discount_td - oe.operand) * amount / oe.operand)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
oe_order_lines_all ol,
oe_price_adjustments oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
-- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
-- AND adjl.list_line_id = oe.list_line_id
AND oe.list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr
START WITH adjr.from_list_line_id = adjl.list_line_id
AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
)
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'ORDER'
AND util.price_adjustment_id = oe.price_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
AND ol.line_id = oe.line_id
UNION
--for off invoice in third party accrual.
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id,
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.operand_calculation_code,
'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.operand),
((adjl.modified_discount - oe.operand) * amount / oe.operand)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
OZF_RESALE_ADJUSTMENTS_ALL oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
AND adjl.list_line_id_td = oe.list_line_id
AND util.price_adjustment_id = oe.resale_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
-- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'TP_ORDER';
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id, -- yzhao: 11.5.10 added billto_cust_account_id
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_offer_adjustment_lines adjl,
oe_order_lines_all ol,
oe_price_adjustments oe
WHERE util.plan_type = 'OFFR'
AND util.plan_id = p_qp_list_header_id
-- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
-- AND adjl.list_line_id = oe.list_line_id
AND oe.list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr
START WITH adjr.from_list_line_id = adjl.list_line_id
AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
)
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'ORDER'
AND util.price_adjustment_id = oe.price_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND oe.line_id = ol.line_id
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
SELECT util.utilization_id,
util.object_type,
util.object_id,
util.order_line_id,
util.product_id,
util.billto_cust_account_id,
util.cust_account_id,
util.fund_id,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.arithmetic_operator,
-- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
'%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * oe.range_break_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * oe.range_break_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- (adjl.modified_discount * oe.range_break_quantity - oe.operand * oe.range_break_quantity) * amount / (oe.operand *oe.range_break_quantity)
) amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
FROM ozf_funds_utilized_all_b util,
ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl,
oe_order_lines_all ol,
oe_price_adjustments oe
WHERE util.plan_type = 'OFFR'
AND product_id IS NOT NULL
AND util.plan_id = p_qp_list_header_id
AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
-- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
-- AND adjl.list_line_id = oe.list_line_id
AND oe.list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr
START WITH adjr.from_list_line_id = adjl.list_line_id
AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
)
AND adjl.offer_adjustment_id = p_offer_adjustment_id
AND util.object_type = 'ORDER'
AND util.price_adjustment_id = oe.price_adjustment_id
AND adjustment_date BETWEEN p_from_date AND l_to_date
AND oe.line_id = ol.line_id
AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code,
reusable,
offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT sum(amount) adj_amt
FROM ozf_funds_utilized_all_b
--12/16/2005 changed by Feng
WHERE orig_utilization_id = p_utilization_id
--WHERE price_adjustment_id = p_price_adj_id
--AND fund_id = p_fund_id
--AND utilization_type ='ADJUSTMENT'
AND utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND adjustment_type_id in(-4,-5,-1);
SELECT org_id
FROM ozf_funds_utilized_all_b
WHERE utilization_id = p_utilization_id;
SELECT offer_adjustment_line_id,
qppa.product_attribute,
qppa.product_attr_value
FROM ozf_offer_adjustment_lines adjl, qp_pricing_attributes qppa
WHERE adjl.offer_adjustment_id = p_offer_adjustment_id AND adjl.list_line_id = qppa.list_line_id;
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) ');
FND_DSQL.add_text('(SELECT ''FUND'', ''N'', product_id,' );
FND_DSQL.add_text('SELECT -1 product_id FROM DUAL');
l_adjustment_product_sql := 'INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) '
|| '(SELECT ''FUND'', ''N'', product_id,'
|| product_rec.offer_adjustment_line_id
|| ' FROM ( '
|| l_temp_sql
|| '))';
|| 'Insert Sql'
|| l_stmt_denorm);
SELECT qp_list_header_id, volume_offer_type
FROM ozf_offers
WHERE offer_type = 'VOLUME_OFFER'
AND status_code = 'ACTIVE';
SELECT qp.description, qp.name ,nvl(ofr.transaction_currency_code, ofr.fund_request_curr_code)
FROM qp_list_headers_vl qp, ozf_offers ofr
WHERE qp.list_header_id = p_list_header_id
AND qp.list_header_id = ofr.qp_list_header_id;
SELECT description, name ,currency_code
FROM qp_list_headers_vl
WHERE list_header_id = p_list_header_id;
SELECT distinct billto_cust_account_id, cust_account_id,product_id,object_id,object_type,org_id
,ship_to_site_use_id,bill_to_site_use_id,exchange_rate_type --Added for bug 7030415
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adj_id
AND object_type = p_object_type
AND order_line_id = p_order_line_id;
SELECT distinct billto_cust_account_id, cust_account_id,product_id,object_id,object_type,org_id
,ship_to_site_use_id,bill_to_site_use_id,exchange_rate_type --Added for bug 7030415
FROM ozf_funds_utilized_all_b
WHERE price_adjustment_id = p_price_adj_id
AND object_type = p_object_type;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
select product_id , cust_account_id , fund_id , sum(DECODE(adjl.arithmetic_operator,'AMOUNT', (adjl.modified_discount - adjl.original_discount),
(( adjl.modified_discount - adjl.original_discount)* amount/adjl.original_discount))) AMount
from ozf_funds_utilized_all_vl util , ozf_temp_eligibility temp,
ozf_offer_adjustment_lines adjl
where util.plan_type = 'OFFR'
and product_id IS NOT NULL and util.plan_id = 7909
and util.product_id = temp.eligibility_id
and temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
and adjustment_date BETWEEN from_date and to_date
group by util.fund_id, util.product_id, util.fund_id,util.cust_account_id
*/ IS
/******************Also pass the adjsutment_type_id */
BEGIN
NULL;
SELECT SUM(DECODE(p_amt_qty, 'amt', line.unit_list_price, 1)*
NVL(line.invoiced_quantity, NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)))
), header.transactional_curr_code
FROM oe_order_lines_all line, oe_price_Adjustments adj, oe_order_headers_all header
WHERE line.line_id = adj.line_id
AND line.header_id = header.header_id
AND line.header_id = adj.header_id
AND adj.list_header_id = p_list_header_id
AND adj.applied_flag = 'Y'
AND line.cancelled_flag = 'N'
AND line.booked_flag = 'Y'
GROUP BY header.transactional_curr_code;
SELECT SUM(DECODE(p_amt_qty, 'amt', line.unit_list_price, 1)*
NVL(line.invoiced_quantity, NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)))
), header.transactional_curr_code
FROM oe_order_lines_all line, oe_price_Adjustments adj, oe_order_headers_all header
WHERE line.line_id = adj.line_id
AND line.header_id = header.header_id
AND line.header_id = adj.header_id
AND adj.list_header_id = p_list_header_id
AND adj.applied_flag = 'Y'
AND line.cancelled_flag = 'N'
AND line.booked_flag = 'Y'
AND line.flow_status_code in ('CLOSED','INVOICED')
GROUP BY header.transactional_curr_code;
SELECT discount,
discount_type_code
FROM ozf_volume_offer_tiers
WHERE p_order_amount BETWEEN
tier_value_from AND tier_value_to
AND qp_list_header_id = p_list_header_id;
SELECT distinct operand,
arithmetic_operator
FROM qp_modifier_summary_v qp
WHERE list_header_id = p_list_header_id;
SELECT distinct volume_type
FROM ozf_volume_offer_tiers tier
WHERE qp_list_header_id = p_list_header_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code)
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT old_Adj_amt,order_line_id, price_adjustment_id,gl_date,object_type
,object_id, gl_posted_flag, utilization_id FROM
( SELECT sum(plan_curr_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,min(gl_date) gl_date
,object_type
,object_id
,'Y' gl_posted_flag
,min(utilization_id) utilization_id
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
-- AND gl_date is not NULL -- only process shipped or invoiced order.
AND gl_posted_flag IN('Y','F')
AND utilization_type IN ( 'ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
GROUP BY order_line_id,object_type,object_id
UNION ALL
SELECT sum(plan_curr_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,min(gl_date) gl_date
,object_type
,object_id
,'X' gl_posted_flag
,min(utilization_id) utilization_id
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND gl_posted_flag = 'X'
AND utilization_type IN ('SALES_ACCRUAL','ADJUSTMENT','ACCRUAL')
AND price_adjustment_id IS NOT NULL
GROUP BY order_line_id,object_type,object_id
UNION ALL
SELECT sum(plan_curr_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,min(gl_date) gl_date
,object_type
,object_id
,NULL gl_posted_flag
,min(utilization_id) utilization_id
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND gl_posted_flag IS NULL
AND utilization_type IN ('UTILIZED','ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
GROUP BY order_line_id,object_type,object_id)
ORDER BY gl_date;
SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
'RETURN', -line.ordered_quantity) ordered_quantity,
DECODE(line.line_category_code,'ORDER',NVL(line.shipped_quantity,line.ordered_quantity),
'RETURN', line.invoiced_quantity,
line.ordered_quantity) shipped_quantity,
line.invoiced_quantity,
line.unit_list_price,
line.line_id,
line.actual_shipment_date,
line.fulfillment_date, -- invoiced date ?????
line.inventory_item_id,
header.transactional_curr_code
FROM oe_order_lines_all line, oe_order_headers_all header
WHERE line.line_id = p_order_line_id
AND line.header_id = header.header_id;
/* SELECT quantity ordered_quantity ,
quantity shipped_quantity,
quantity invoiced_quantity,
purchase_price unit_list_price,
resale_line_id line_id,
NVL(date_shipped, date_ordered) actual_shipment_date,
NVL(date_shipped, date_ordered) fulfillment_date, -- invoiced date ?????
inventory_item_id,
currency_code --dummy column
FROM OZF_RESALE_LINES_ALL
WHERE resale_line_id = p_resale_line_id;
SELECT line.quantity ordered_quantity ,
line.quantity shipped_quantity,
line.quantity invoiced_quantity,
adj.priced_unit_price unit_list_price,
line.resale_line_id line_id,
NVL(line.date_shipped, line.date_ordered) actual_shipment_date,
NVL(line.date_shipped, line.date_ordered) fulfillment_date, -- invoiced date ?????
line.inventory_item_id,
line.currency_code --dummy column
FROM OZF_RESALE_LINES_ALL line,ozf_resale_adjustments_all adj
WHERE line.resale_line_id = p_resale_line_id
AND adj.resale_adjustment_id = p_adj_id
AND line.resale_line_id = adj.resale_line_id;
SELECT offer_discount_line_id ,volume_from ,volume_to, discount
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id
AND p_volume >= volume_from
ORDER BY volume_from DESC;
SELECT discount_type,volume_type
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_discount_line_id
AND tier_type = 'PBH';
SELECT group_no,pbh_line_id,include_volume_flag
FROM ozf_order_group_prod
WHERE order_line_id = p_order_line_id
AND qp_list_header_id = p_list_header_id;
SELECT opt.retroactive_flag
FROM ozf_offr_market_options opt
WHERE opt.GROUP_NUMBER= p_group_id
AND opt.qp_list_header_id = p_list_header_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE p_volume > volume_from
AND p_volume <= volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT summ.individual_volume
FROM ozf_volume_detail det,ozf_volume_summary summ
WHERE det.order_line_id = p_order_line_id
AND det.qp_list_header_id = p_qp_list_header_id
AND det.volume_track_type = summ.individual_type
AND det.qp_list_header_id = summ.qp_list_header_id
AND det.source_code = p_source_code;
SELECT a.discount
FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
WHERE a.offer_discount_line_id = b.dis_offer_discount_id
AND b.pbh_offer_discount_id = p_pbh_line_id
AND b.offer_market_option_id = c.offer_market_option_id
AND c.qp_list_header_id = p_qp_list_header_id
AND c.group_number = p_group_id;
SELECT 'X' from ozf_funds_all_b
WHERE plan_id= p_list_header_id
AND accrual_basis = 'SALES'
UNION
SELECT 'X' from ozf_funds_all_b
WHERE plan_id = p_list_header_id
AND accrual_basis = 'CUSTOMER'
AND liability_flag = 'N';
SELECT SUM(adjusted_amount_per_pqty)
FROM oe_price_adjustments
WHERE line_id = p_order_line_id
AND accrual_flag = 'N'
AND applied_flag = 'Y'
AND list_line_type_code IN ('DIS', 'SUR', 'PBH', 'FREIGHT_CHARGE')
and pricing_group_sequence <
(SELECT pricing_group_sequence FROM oe_price_adjustments
WHERE price_Adjustment_id = p_price_adjust_id) ;
SELECT SUM(adjusted_amount_per_pqty)
FROM oe_price_adjustments
WHERE line_id = p_order_line_id
AND accrual_flag = 'N'
AND applied_flag = 'Y'
AND list_line_type_code IN ('DIS', 'SUR', 'PBH', 'FREIGHT_CHARGE');
SELECT MIN(volume_from),MAX(volume_to)
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE volume_to =p_max_volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code), offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT NVL(apply_discount_flag,'N')
FROM ozf_order_group_prod
WHERE offer_id = p_offer_id
AND order_line_id = p_line_id;
SELECT transaction_date
FROM ozf_sales_transactions
WHERE source_code = 'IS'
AND line_id = p_line_id;
SELECT exchange_rate_type, org_id
FROM ozf_funds_utilized_all_b
WHERE utilization_id=l_utilization_id;
SELECT sum(plan_curr_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,object_type
,object_id
,min(gl_date) gl_date
,min(utilization_id) utilization_id
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
GROUP BY order_line_id,object_type,object_id
ORDER BY gl_date;
SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
'RETURN', -line.ordered_quantity) ordered_quantity
FROM oe_order_lines_all line
WHERE line.line_id = p_order_line_id;*/
select sum(ordered_quantity)
FROM (
select sum(ordered_quantity) ordered_quantity from oe_order_lines_all
where line_id IN
(SELECT order_line_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
)
UNION
select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL
where resale_batch_id IN
(SELECT reference_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
)
);*/
select sum(ordered_quantity)
FROM (
SELECT SUM(DECODE(line_category_code,'ORDER',ordered_quantity,
'RETURN', -ordered_quantity)) ordered_quantity
from oe_order_lines_all oe,
(SELECT distinct order_line_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
) orders
where oe.line_id = orders.order_line_id
UNION
select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL ol ,
(SELECT distinct reference_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
) orders
where ol.resale_batch_id = orders.reference_id
);
SELECT discount_type,volume_type
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_discount_line_id
AND tier_type = 'PBH';
SELECT group_no,pbh_line_id,include_volume_flag
FROM ozf_order_group_prod
WHERE order_line_id = p_order_line_id
AND qp_list_header_id = p_list_header_id;
SELECT opt.retroactive_flag
FROM ozf_offr_market_options opt
WHERE opt.GROUP_NUMBER= p_group_id
AND opt.qp_list_header_id = p_list_header_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE p_volume > volume_from
AND p_volume <= volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT MIN(volume_from),MAX(volume_to)
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id;
SELECT discount
FROM ozf_offer_discount_lines
WHERE volume_to =p_max_volume_to
AND parent_discount_line_id = p_parent_discount_id;
SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
'RETURN', -line.ordered_quantity) ordered_quantity,
DECODE(line.line_category_code,'ORDER',NVL(line.shipped_quantity,line.ordered_quantity),
'RETURN', line.invoiced_quantity,
line.ordered_quantity) shipped_quantity,
line.invoiced_quantity,
line.unit_selling_price,
line.line_id,
line.actual_shipment_date,
line.fulfillment_date, -- invoiced date ?????
line.inventory_item_id,
header.transactional_curr_code,
header.header_id
FROM oe_order_lines_all line, oe_order_headers_all header
WHERE line.line_id = p_order_line_id
AND line.header_id = header.header_id;
SELECT line.quantity ordered_quantity ,
line.quantity shipped_quantity,
line.quantity invoiced_quantity,
adj.priced_unit_price unit_list_price,
line.resale_line_id line_id,
NVL(line.date_shipped, line.date_ordered) actual_shipment_date,
NVL(line.date_shipped, line.date_ordered) fulfillment_date, -- invoiced date ?????
line.inventory_item_id,
line.currency_code, --dummy column
line.resale_header_id
FROM OZF_RESALE_LINES_ALL line,ozf_resale_adjustments_all adj
WHERE line.resale_line_id = p_resale_line_id
AND adj.resale_adjustment_id = p_adj_id
AND line.resale_line_id = adj.resale_line_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code), offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_qp_list_header_id;
SELECT NVL(apply_discount_flag,'N')
FROM ozf_order_group_prod
WHERE offer_id = p_offer_id
AND order_line_id = p_line_id;
SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code
, beneficiary_account_id, offer_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT invoice_to_org_id, ship_to_org_id
FROM oe_order_lines_all
WHERE line_id = p_line_id;
SELECT cust.cust_account_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use,
hz_cust_accounts cust,
oe_order_headers_all header
WHERE header.header_id = p_header_id
AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
AND acct_site.cust_account_id = cust.cust_account_id
AND site_use.site_use_id = header.invoice_to_org_id ;
SELECT offer_discount_line_id ,volume_from ,volume_to, discount
FROM ozf_offer_discount_lines
WHERE parent_discount_line_id = p_parent_discount_id
AND p_volume >= volume_from
ORDER BY volume_from DESC;
SELECT a.discount
FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
WHERE a.offer_discount_line_id = b.dis_offer_discount_id
AND b.pbh_offer_discount_id = p_pbh_line_id
AND b.offer_market_option_id = c.offer_market_option_id
AND c.qp_list_header_id = p_qp_list_header_id
AND c.group_number = p_group_id;
SELECT billto_cust_account_id, bill_to_site_use_id, ship_to_site_use_id
FROM ozf_funds_utilized_all_b
WHERE (p_source_code = 'OM' AND object_type = 'ORDER' AND order_line_id = p_order_line_id)
OR (p_source_code = 'IS' AND object_type = 'TP_ORDER' AND object_id = p_order_line_id);
select sum(ordered_quantity)
FROM (
SELECT SUM (DECODE(line_category_code,'ORDER',ordered_quantity,
'RETURN', -ordered_quantity)) ordered_quantity
from oe_order_lines_all oe,
(SELECT distinct order_line_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
AND cust_account_id = p_cust_account_id
) orders
where oe.line_id = orders.order_line_id
UNION
select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL ol ,
(SELECT distinct reference_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
AND cust_account_id = p_cust_account_id
) orders
where ol.resale_batch_id = orders.reference_id
);
select sum(ordered_quantity)
FROM (
SELECT SUM (DECODE(line_category_code,'ORDER',ordered_quantity,
'RETURN', -ordered_quantity)) ordered_quantity
from oe_order_lines_all oe,
(SELECT distinct order_line_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
AND cust_account_id = p_cust_account_id
AND gl_date <= p_transaction_date
) orders
where oe.line_id = orders.order_line_id
UNION
select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL ol ,
(SELECT distinct reference_id FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
AND cust_account_id = p_cust_account_id
AND gl_date <= p_transaction_date
) orders
where ol.resale_batch_id = orders.reference_id
);
SELECT 1 FROM DUAL WHERE EXISTS
( SELECT 1
FROM
( SELECT sum(plan_curr_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,object_type
,object_id
,min(gl_date) gl_date
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
AND NVL(gl_posted_flag,'Y')='Y'
GROUP BY order_line_id,object_type,object_id
ORDER BY gl_date) earned,
( SELECT sum(plan_curr_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,object_type
,object_id
,min(gl_date) gl_date
FROM ozf_funds_utilized_all_b
WHERE plan_id = p_list_header_id
AND plan_type = 'OFFR'
AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND price_adjustment_id IS NOT NULL
-- AND gl_posted_flag in ('Y','N')
GROUP BY order_line_id,object_type,object_id
ORDER BY gl_date) utilized
WHERE utilized.old_Adj_amt <> earned.old_Adj_amt
AND utilized.order_line_id=earned.order_line_id
);
SELECT exchange_rate_type, org_id
FROM ozf_funds_utilized_all_b
WHERE utilization_id=l_utilization_id;