[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, header.org_id, line.order_quantity_uom, line.pricing_quantity_uom
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, header.org_id, line.order_quantity_uom, line.pricing_quantity_uom
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)offer_currency_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(plan_curr_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(plan_curr_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) offer_currency_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(plan_curr_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;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_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.offer_adjustment_line_id,
adj.list_line_id to_list_line_id
FROM ozf_offer_adjustment_lines adj
WHERE adj.offer_adjustment_id = p_offer_adjustment_id
AND adj.created_from_adjustments = 'Y'
UNION ALL
SELECT adj.offer_adjustment_line_id,
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 =nvl(adj.list_line_id, adj.list_line_id_td)
AND rltd.from_list_line_id = adj.list_line_id
AND rltd.offer_adjustment_id = adj.offer_adjustment_id
UNION ALL
SELECT DISTINCT orig_line.parent_discount_line_id offer_adjustment_line_id,
--AMITAMKU Fix for bug 15935374
orig_line.parent_discount_line_id to_list_line_id --null to_list_line_id
FROM ozf_offer_adjustment_tiers adj_line,
ozf_offer_discount_lines orig_line
WHERE adj_line.offer_adjustment_id = p_offer_adjustment_id
AND adj_line.offer_discount_line_id = orig_line.offer_discount_line_id;
SELECT
/*+ leading(temp) use_nl(temp line header) */ line.header_id,
line.line_id,
line.inventory_item_id,
line.unit_list_price,
line.unit_selling_price,
nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0)) quantity,
nvl(line.pricing_quantity, nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0))) pricing_quantity,
line.invoice_to_org_id,
line.sold_to_org_id,
line.ship_to_org_id,
line.line_category_code,
line.reference_line_id,
line.order_quantity_uom,
line.pricing_quantity_uom,
line.price_list_id,
nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)) pricing_date,
line.actual_shipment_date,
line.request_date,
line.fulfillment_date,
line.line_type_id,
line.end_customer_id,
line.agreement_id,
line.cust_po_number,
line.ship_from_org_id,
line.shipment_priority_code,
line.shippable_flag,
line.schedule_ship_date,
line.source_type_code,
line.org_id,
line.freight_terms_code,
line.payment_term_id,
line.shipping_method_code,
line.item_identifier_type,
line.ordered_item_id,
line.top_model_line_id,
line.unit_percent_base_price,
line.preferred_grade,
line.Blanket_number,
line.minisite_id,
line.blanket_line_number,
line.transaction_phase_code,
line.charge_periodicity_code,
adj_line.offer_adjustment_line_id,
adj_line.list_header_id,
qp.orig_org_id offer_org_id,
off.transaction_currency_code offer_currency,
qpa.product_uom_code,
lines.arithmetic_operator,
lines.list_line_id,
line.shipped_quantity,
line.invoice_interface_status_code,
line.invoiced_quantity,
line.shipping_quantity_uom,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all line,
ozf_adj_temp_eligibility temp,
ozf_offer_adjustments_b adj,
ozf_offer_adjustment_lines adj_line,
ozf_offers off,
qp_list_lines lines,
qp_list_headers_all qp,
qp_pricing_attributes qpa
WHERE TRUNC(nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)))
BETWEEN decode(adj_line.created_from_adjustments, 'Y', TRUNC(nvl(lines.start_date_active, adj.effective_date)), adj.effective_date)
AND decode(adj_line.created_from_adjustments, 'Y', TRUNC(
CASE
WHEN lines.end_date_active IS NOT NULL
AND lines.end_date_active < adj.approved_date THEN lines.end_date_active
ELSE adj.approved_date
END), TRUNC(
CASE
WHEN adj.approved_date > nvl(lines.start_date_active, off.start_date) THEN nvl(lines.start_date_active, off.start_date)
ELSE adj.approved_date
END))
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
AND off.offer_type <> 'VOLUME_OFFER'
AND temp.object_type <> 'FUND'
AND line.inventory_item_id = temp.eligibility_id
AND temp.offer_adjustment_line_id = adj_line.offer_adjustment_line_id
AND adj.offer_adjustment_id = adj_line.offer_adjustment_id
AND qp.list_header_id = off.qp_list_header_id
AND qpa.list_line_id = lines.list_line_id
AND off.qp_list_header_id = adj.list_header_id
AND off.qp_list_header_id = lines.list_header_id
AND adj.list_header_id = lines.list_header_id
AND lines.list_line_id IN
(SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr START WITH adjr.from_list_line_id = adj_line.list_line_id
AND adjr.offer_adjustment_id = adj_line.offer_adjustment_id CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
UNION ALL
SELECT list_line_id
FROM ozf_offer_adjustment_lines);
SELECT /*+ parallel(line) */ line.header_id,
line.line_id,
line.inventory_item_id,
line.unit_list_price,
line.unit_selling_price,
nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0)) quantity,
nvl(line.pricing_quantity, nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0))) pricing_quantity,
line.invoice_to_org_id,
line.sold_to_org_id,
line.ship_to_org_id,
line.line_category_code,
line.reference_line_id,
line.order_quantity_uom,
line.pricing_quantity_uom,
line.price_list_id,
nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)) pricing_date,
line.actual_shipment_date,
line.request_date,
line.fulfillment_date,
line.line_type_id,
line.end_customer_id,
line.agreement_id,
line.cust_po_number,
line.ship_from_org_id,
line.shipment_priority_code,
line.shippable_flag,
line.schedule_ship_date,
line.source_type_code,
line.org_id,
line.freight_terms_code,
line.payment_term_id,
line.shipping_method_code,
line.item_identifier_type,
line.ordered_item_id,
line.top_model_line_id,
line.unit_percent_base_price,
line.preferred_grade,
line.Blanket_number,
line.minisite_id,
line.blanket_line_number,
line.transaction_phase_code,
line.charge_periodicity_code,
adj_line.offer_adjustment_line_id,
adj_line.list_header_id,
qp.orig_org_id offer_org_id,
off.transaction_currency_code offer_currency,
qpa.product_uom_code,
lines.arithmetic_operator,
lines.list_line_id,
line.shipped_quantity,
line.invoice_interface_status_code,
line.invoiced_quantity,
line.shipping_quantity_uom,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all line,
ozf_adj_temp_eligibility temp,
ozf_offer_adjustments_b adj,
ozf_offer_adjustment_lines adj_line,
ozf_offers off,
qp_list_lines lines,
qp_list_headers_all qp,
qp_pricing_attributes qpa
WHERE TRUNC(nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)))
BETWEEN decode(adj_line.created_from_adjustments, 'Y', TRUNC(nvl(lines.start_date_active, adj.effective_date)), adj.effective_date)
AND decode(adj_line.created_from_adjustments, 'Y', TRUNC(
CASE
WHEN lines.end_date_active IS NOT NULL
AND lines.end_date_active < adj.approved_date THEN lines.end_date_active
ELSE adj.approved_date
END), TRUNC(
CASE
WHEN adj.approved_date > nvl(lines.start_date_active, off.start_date) THEN nvl(lines.start_date_active, off.start_date)
ELSE adj.approved_date
END))
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
AND off.offer_type <> 'VOLUME_OFFER'
AND temp.object_type <> 'FUND'
AND line.inventory_item_id = temp.eligibility_id
AND temp.offer_adjustment_line_id = adj_line.offer_adjustment_line_id
AND adj.offer_adjustment_id = adj_line.offer_adjustment_id
AND qp.list_header_id = off.qp_list_header_id
AND qpa.list_line_id = lines.list_line_id
AND off.qp_list_header_id = adj.list_header_id
AND off.qp_list_header_id = lines.list_header_id
AND adj.list_header_id = lines.list_header_id
AND lines.list_line_id IN
(SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr START WITH adjr.from_list_line_id = adj_line.list_line_id
AND adjr.offer_adjustment_id = adj_line.offer_adjustment_id CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
UNION ALL
SELECT list_line_id
FROM ozf_offer_adjustment_lines);
SELECT
/*+ leading(temp) use_nl(temp line header) */ line.header_id,
line.line_id,
line.inventory_item_id,
line.unit_list_price,
line.unit_selling_price,
nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0)) quantity,
nvl(line.pricing_quantity, nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0))) pricing_quantity,
line.invoice_to_org_id,
line.sold_to_org_id,
line.ship_to_org_id,
line.line_category_code,
line.reference_line_id,
line.order_quantity_uom,
line.pricing_quantity_uom,
line.price_list_id,
nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)) pricing_date,
line.actual_shipment_date,
line.request_date,
line.fulfillment_date,
line.line_type_id,
line.end_customer_id,
line.agreement_id,
line.cust_po_number,
line.ship_from_org_id,
line.shipment_priority_code,
line.shippable_flag,
line.schedule_ship_date,
line.source_type_code,
line.org_id,
line.freight_terms_code,
line.payment_term_id,
line.shipping_method_code,
line.item_identifier_type,
line.ordered_item_id,
line.top_model_line_id,
line.unit_percent_base_price,
line.preferred_grade,
line.Blanket_number,
line.minisite_id,
line.blanket_line_number,
line.transaction_phase_code,
line.charge_periodicity_code,
adj_line.offer_adjustment_line_id,
adj_line.list_header_id,
qp.orig_org_id offer_org_id,
off.transaction_currency_code offer_currency,
qpa.product_uom_code,
lines.arithmetic_operator,
lines.list_line_id,
line.shipped_quantity,
line.invoice_interface_status_code,
line.invoiced_quantity,
line.shipping_quantity_uom,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all line,
ozf_adj_temp_eligibility temp,
ozf_offer_adjustments_b adj,
ozf_offer_adjustment_lines adj_line,
ozf_offers off,
qp_list_lines lines,
qp_list_headers_all qp,
qp_pricing_attributes qpa
WHERE TRUNC(nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)))
BETWEEN decode(adj_line.created_from_adjustments, 'Y', TRUNC(nvl(lines.start_date_active, adj.effective_date)), adj.effective_date)
AND decode(adj_line.created_from_adjustments, 'Y', TRUNC(
CASE
WHEN lines.end_date_active IS NOT NULL
AND lines.end_date_active < adj.approved_date THEN lines.end_date_active
ELSE adj.approved_date
END), TRUNC(
CASE
WHEN adj.approved_date > nvl(lines.start_date_active, off.start_date) THEN nvl(lines.start_date_active, off.start_date)
ELSE adj.approved_date
END))
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
AND off.offer_type <> 'VOLUME_OFFER'
AND temp.object_type <> 'FUND'
AND line.inventory_item_id = temp.eligibility_id
AND temp.offer_adjustment_line_id = adj_line.offer_adjustment_line_id
AND adj.offer_adjustment_id = adj_line.offer_adjustment_id
AND qp.list_header_id = off.qp_list_header_id
AND qpa.list_line_id = lines.list_line_id
AND off.qp_list_header_id = adj.list_header_id
AND off.qp_list_header_id = lines.list_header_id
AND adj.list_header_id = lines.list_header_id
AND lines.list_line_id IN
(SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr START WITH adjr.from_list_line_id = adj_line.list_line_id
AND adjr.offer_adjustment_id = adj_line.offer_adjustment_id CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
UNION ALL
SELECT list_line_id
FROM ozf_offer_adjustment_lines)
UNION ALL
SELECT
/*+ leading(temp) use_nl(temp line header) */ line.header_id,
line.line_id,
line.inventory_item_id,
line.unit_list_price,
line.unit_selling_price,
nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0)) quantity,
nvl(line.pricing_quantity, nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0))) pricing_quantity,
line.invoice_to_org_id,
line.sold_to_org_id,
line.ship_to_org_id,
line.line_category_code,
line.reference_line_id,
line.order_quantity_uom,
line.pricing_quantity_uom,
line.price_list_id,
nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)) pricing_date,
line.actual_shipment_date,
line.request_date,
line.fulfillment_date,
line.line_type_id,
line.end_customer_id,
line.agreement_id,
line.cust_po_number,
line.ship_from_org_id,
line.shipment_priority_code,
line.shippable_flag,
line.schedule_ship_date,
line.source_type_code,
line.org_id,
line.freight_terms_code,
line.payment_term_id,
line.shipping_method_code,
line.item_identifier_type,
line.ordered_item_id,
line.top_model_line_id,
line.unit_percent_base_price,
line.preferred_grade,
line.Blanket_number,
line.minisite_id,
line.blanket_line_number,
line.transaction_phase_code,
line.charge_periodicity_code,
orig_disc.offer_discount_line_id,
off.qp_list_header_id,
qp.orig_org_id offer_org_id,
off.transaction_currency_code offer_currency,
orig_disc.uom_code product_uom_code,
orig_disc.discount_type arithmetic_operator,
lines.list_line_id,
line.shipped_quantity,
line.invoice_interface_status_code,
line.invoiced_quantity,
line.shipping_quantity_uom,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all line,
ozf_adj_temp_eligibility temp,
ozf_offer_adjustments_b adj,
ozf_offer_adjustment_tiers adj_line,
ozf_offer_discount_lines orig_disc,
ozf_offers off,
qp_list_headers_all qp,
ozf_qp_discounts map_lines,
qp_list_lines lines
WHERE TRUNC(nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date))) BETWEEN adj.effective_date
AND TRUNC(off.start_date) -- no new products for volume offer.
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
AND off.offer_type = 'VOLUME_OFFER'
AND temp.object_type <> 'FUND'
AND line.inventory_item_id = temp.eligibility_id
AND temp.offer_adjustment_line_id = orig_disc.parent_discount_line_id
AND adj_line.offer_discount_line_id = orig_disc.offer_discount_line_id
AND adj.offer_adjustment_id = adj_line.offer_adjustment_id
AND orig_disc.offer_discount_line_id = map_lines.offer_discount_line_id
AND qp.list_header_id = off.qp_list_header_id
AND lines.list_line_id = map_lines.list_line_id
AND off.qp_list_header_id = adj.list_header_id
AND(lines.end_date_active IS NULL OR lines.end_date_active >= sysdate);
SELECT
/*+ parallel(line) */ line.header_id,
line.line_id,
line.inventory_item_id,
line.unit_list_price,
line.unit_selling_price,
nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0)) quantity,
nvl(line.pricing_quantity, nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0))) pricing_quantity,
line.invoice_to_org_id,
line.sold_to_org_id,
line.ship_to_org_id,
line.line_category_code,
line.reference_line_id,
line.order_quantity_uom,
line.pricing_quantity_uom,
line.price_list_id,
nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)) pricing_date,
line.actual_shipment_date,
line.request_date,
line.fulfillment_date,
line.line_type_id,
line.end_customer_id,
line.agreement_id,
line.cust_po_number,
line.ship_from_org_id,
line.shipment_priority_code,
line.shippable_flag,
line.schedule_ship_date,
line.source_type_code,
line.org_id,
line.freight_terms_code,
line.payment_term_id,
line.shipping_method_code,
line.item_identifier_type,
line.ordered_item_id,
line.top_model_line_id,
line.unit_percent_base_price,
line.preferred_grade,
line.Blanket_number,
line.minisite_id,
line.blanket_line_number,
line.transaction_phase_code,
line.charge_periodicity_code,
adj_line.offer_adjustment_line_id,
adj_line.list_header_id,
qp.orig_org_id offer_org_id,
off.transaction_currency_code offer_currency,
qpa.product_uom_code,
lines.arithmetic_operator,
lines.list_line_id,
line.shipped_quantity,
line.invoice_interface_status_code,
line.invoiced_quantity,
line.shipping_quantity_uom,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all line,
ozf_adj_temp_eligibility temp,
ozf_offer_adjustments_b adj,
ozf_offer_adjustment_lines adj_line,
ozf_offers off,
qp_list_lines lines,
qp_list_headers_all qp,
qp_pricing_attributes qpa
WHERE TRUNC(nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)))
BETWEEN decode(adj_line.created_from_adjustments, 'Y', TRUNC(nvl(lines.start_date_active, adj.effective_date)), adj.effective_date)
AND decode(adj_line.created_from_adjustments, 'Y', TRUNC(
CASE
WHEN lines.end_date_active IS NOT NULL
AND lines.end_date_active < adj.approved_date THEN lines.end_date_active
ELSE adj.approved_date
END), TRUNC(
CASE
WHEN adj.approved_date > nvl(lines.start_date_active, off.start_date) THEN nvl(lines.start_date_active, off.start_date)
ELSE adj.approved_date
END))
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
AND off.offer_type <> 'VOLUME_OFFER'
AND temp.object_type <> 'FUND'
AND line.inventory_item_id = temp.eligibility_id
AND temp.offer_adjustment_line_id = adj_line.offer_adjustment_line_id
AND adj.offer_adjustment_id = adj_line.offer_adjustment_id
AND qp.list_header_id = off.qp_list_header_id
AND qpa.list_line_id = lines.list_line_id
AND off.qp_list_header_id = adj.list_header_id
AND off.qp_list_header_id = lines.list_header_id
AND adj.list_header_id = lines.list_header_id
AND lines.list_line_id IN
(SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines adjr START WITH adjr.from_list_line_id = adj_line.list_line_id
AND adjr.offer_adjustment_id = adj_line.offer_adjustment_id CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
UNION ALL
SELECT list_line_id
FROM ozf_offer_adjustment_lines)
UNION ALL
SELECT
/*+ parallel(line) */ line.header_id,
line.line_id,
line.inventory_item_id,
line.unit_list_price,
line.unit_selling_price,
nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0)) quantity,
nvl(line.pricing_quantity, nvl(line.shipped_quantity, nvl(line.ordered_quantity, 0))) pricing_quantity,
line.invoice_to_org_id,
line.sold_to_org_id,
line.ship_to_org_id,
line.line_category_code,
line.reference_line_id,
line.order_quantity_uom,
line.pricing_quantity_uom,
line.price_list_id,
nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date)) pricing_date,
line.actual_shipment_date,
line.request_date,
line.fulfillment_date,
line.line_type_id,
line.end_customer_id,
line.agreement_id,
line.cust_po_number,
line.ship_from_org_id,
line.shipment_priority_code,
line.shippable_flag,
line.schedule_ship_date,
line.source_type_code,
line.org_id,
line.freight_terms_code,
line.payment_term_id,
line.shipping_method_code,
line.item_identifier_type,
line.ordered_item_id,
line.top_model_line_id,
line.unit_percent_base_price,
line.preferred_grade,
line.Blanket_number,
line.minisite_id,
line.blanket_line_number,
line.transaction_phase_code,
line.charge_periodicity_code,
orig_disc.offer_discount_line_id,
off.qp_list_header_id,
qp.orig_org_id offer_org_id,
off.transaction_currency_code offer_currency,
orig_disc.uom_code product_uom_code,
orig_disc.discount_type arithmetic_operator,
lines.list_line_id,
line.shipped_quantity,
line.invoice_interface_status_code,
line.invoiced_quantity,
line.shipping_quantity_uom,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all line,
ozf_adj_temp_eligibility temp,
ozf_offer_adjustments_b adj,
ozf_offer_adjustment_tiers adj_line,
ozf_offer_discount_lines orig_disc,
ozf_offers off,
qp_list_headers_all qp,
ozf_qp_discounts map_lines,
qp_list_lines lines
WHERE TRUNC(nvl(line.pricing_date, nvl(line.actual_shipment_date, line.fulfillment_date))) BETWEEN adj.effective_date
AND TRUNC(off.start_date) -- no new products for volume offer.
AND line.booked_flag = 'Y'
AND line.cancelled_flag = 'N'
AND off.offer_type = 'VOLUME_OFFER'
AND temp.object_type <> 'FUND'
AND line.inventory_item_id = temp.eligibility_id
AND temp.offer_adjustment_line_id = orig_disc.parent_discount_line_id
AND adj_line.offer_discount_line_id = orig_disc.offer_discount_line_id
AND adj.offer_adjustment_id = adj_line.offer_adjustment_id
AND orig_disc.offer_discount_line_id = map_lines.offer_discount_line_id
AND qp.list_header_id = off.qp_list_header_id
AND lines.list_line_id = map_lines.list_line_id
AND off.qp_list_header_id = adj.list_header_id
AND(lines.end_date_active IS NULL OR lines.end_date_active >= sysdate);
SELECT org_id, transactional_curr_code, order_type_id, sold_to_org_id, invoice_to_org_id,
ship_to_org_id, price_list_id, ordered_date, request_date, pricing_date,
agreement_id, cust_po_number, ship_from_org_id, order_category_code,
shipment_priority_code, freight_terms_code, payment_term_id, shipping_method_code,
conversion_rate_date, conversion_rate, conversion_type_code, minisite_id,
blanket_number
FROM oe_order_headers_all
WHERE header_id = p_header_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;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_sold_to_org_id;
SELECT COUNT(eligibility_id)
FROM ozf_adj_temp_eligibility;
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
,qp.orig_org_id offer_org_id, off.transaction_currency_code offer_currency
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;
l_adjIdByListLineId.delete;
EXECUTE IMMEDIATE 'DELETE FROM ozf_adj_temp_eligibility';
EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
write_conc_log ( l_full_name || ' : ' || ' Begin Inserting data into temp table for AdjustmentLineId ' || l_adjusted_line_cur.offer_adjustment_line_id );
FND_DSQL.add_text('INSERT INTO ozf_adj_temp_eligibility(object_type, eligibility_id, offer_adjustment_line_id) ');
FND_DSQL.add_text('(SELECT ''OFFR'', product_id, ' );
write_conc_log( 'Return Status After Inserting data into temp table for adjustmentId' || l_offerAdjustmentIdTbl(i) || ' IS ' || x_return_status);
l_uniqueHeaderIdtbl.DELETE;
l_lineInfoTblByLineId.DELETE;
l_lineIdTblByHdrId.DELETE;
l_tempLineIdTbl.DELETE;
l_hdrInfoTblByHeaderId.DELETE;
IF l_hdrOrgIdTbl.EXISTS(1) THEN l_hdrOrgIdTbl.DELETE; END IF;
IF l_orderCurrTbl.EXISTS(1) THEN l_orderCurrTbl.DELETE; END IF;
IF l_orderTypeIdTbl.EXISTS(1) THEN l_orderTypeIdTbl.DELETE; END IF;
IF l_hdrSoldToOrgIdTbl.EXISTS(1) THEN l_hdrSoldToOrgIdTbl.DELETE; END IF;
IF l_hdrInvoiceToOrgIdTbl.EXISTS(1) THEN l_hdrInvoiceToOrgIdTbl.DELETE; END IF;
IF l_hdrShipToOrgIdTbl.EXISTS(1) THEN l_hdrShipToOrgIdTbl.DELETE; END IF;
IF l_hdrPriceListIdTbl.EXISTS(1) THEN l_hdrPriceListIdTbl.DELETE; END IF;
IF l_hdrOrderedDateTbl.EXISTS(1) THEN l_hdrOrderedDateTbl.DELETE; END IF;
IF l_hdrRequestDateTbl.EXISTS(1) THEN l_hdrRequestDateTbl.DELETE; END IF;
IF l_hdrPricingDateTbl.EXISTS(1) THEN l_hdrPricingDateTbl.DELETE; END IF;
IF l_hdrAgreementIdTbl.EXISTS(1) THEN l_hdrAgreementIdTbl.DELETE; END IF;
IF l_hdrCustPONumberTbl.EXISTS(1) THEN l_hdrCustPONumberTbl.DELETE; END IF;
IF l_hdrShipFromOrgIdTbl.EXISTS(1) THEN l_hdrShipFromOrgIdTbl.DELETE; END IF;
IF l_hdrOrderCategoryCodeTbl.EXISTS(1) THEN l_hdrOrderCategoryCodeTbl.DELETE; END IF;
IF l_hdrShipmentPriorityCodeTbl.EXISTS(1) THEN l_hdrShipmentPriorityCodeTbl.DELETE; END IF;
IF l_hdrFreightTermsCodeTbl.EXISTS(1) THEN l_hdrFreightTermsCodeTbl.DELETE; END IF;
IF l_hdrPaymentTermIdTbl.EXISTS(1) THEN l_hdrPaymentTermIdTbl.DELETE; END IF;
IF l_hdrShippingMethodCodeTbl.EXISTS(1) THEN l_hdrShippingMethodCodeTbl.DELETE; END IF;
IF l_hdrConversionRateDateTbl.EXISTS(1) THEN l_hdrConversionRateDateTbl.DELETE; END IF;
IF l_hdrConversionRateTbl.EXISTS(1) THEN l_hdrConversionRateTbl.DELETE; END IF;
IF l_hdrConversionTypeCodeTbl.EXISTS(1) THEN l_hdrConversionTypeCodeTbl.DELETE; END IF;
IF l_hdrMinisiteIdTbl.EXISTS(1) THEN l_hdrMinisiteIdTbl.DELETE; END IF;
IF l_hdrBlanketNumberTbl.EXISTS(1) THEN l_hdrBlanketNumberTbl.DELETE; END IF;
l_tempLineIdTbl.DELETE;
l_control_rec.temp_table_insert_flag := 'N';
IF l_line_tbl.EXISTS(1) THEN l_line_tbl.DELETE; END IF;
IF G_LINE_INDEX_tbl.EXISTS(1) THEN G_LINE_INDEX_tbl.DELETE; END IF;
IF G_LINE_TYPE_CODE_TBL.EXISTS(1) THEN G_LINE_TYPE_CODE_TBL.DELETE; END IF;
IF G_PRICING_EFFECTIVE_DATE_TBL.EXISTS(1) THEN G_PRICING_EFFECTIVE_DATE_TBL.DELETE; END IF;
IF G_ACTIVE_DATE_FIRST_TBL.EXISTS(1) THEN G_ACTIVE_DATE_FIRST_TBL.DELETE; END IF;
IF G_ACTIVE_DATE_FIRST_TYPE_TBL.EXISTS(1) THEN G_ACTIVE_DATE_FIRST_TYPE_TBL.DELETE; END IF;
IF G_ACTIVE_DATE_SECOND_TBL.EXISTS(1) THEN G_ACTIVE_DATE_SECOND_TBL.DELETE; END IF;
IF G_ACTIVE_DATE_SECOND_TYPE_TBL.EXISTS(1) THEN G_ACTIVE_DATE_SECOND_TYPE_TBL.DELETE; END IF;
IF G_LINE_QUANTITY_TBL.EXISTS(1) THEN G_LINE_QUANTITY_TBL.DELETE; END IF;
IF G_LINE_UOM_CODE_TBL.EXISTS(1) THEN G_LINE_UOM_CODE_TBL.DELETE; END IF;
IF G_REQUEST_TYPE_CODE_TBL.EXISTS(1) THEN G_REQUEST_TYPE_CODE_TBL.DELETE; END IF;
IF G_PRICED_QUANTITY_TBL.EXISTS(1) THEN G_PRICED_QUANTITY_TBL.DELETE; END IF;
IF G_PRICED_UOM_CODE_TBL.EXISTS(1) THEN G_PRICED_UOM_CODE_TBL.DELETE; END IF;
IF G_CURRENCY_CODE_TBL.EXISTS(1) THEN G_CURRENCY_CODE_TBL.DELETE; END IF;
IF G_UNIT_PRICE_TBL.EXISTS(1) THEN G_UNIT_PRICE_TBL.DELETE; END IF;
IF G_PERCENT_PRICE_TBL.EXISTS(1) THEN G_PERCENT_PRICE_TBL.DELETE; END IF;
IF G_UOM_QUANTITY_TBL.EXISTS(1) THEN G_UOM_QUANTITY_TBL.DELETE; END IF;
IF G_ADJUSTED_UNIT_PRICE_TBL.EXISTS(1) THEN G_ADJUSTED_UNIT_PRICE_TBL.DELETE; END IF;
IF G_UPD_ADJUSTED_UNIT_PRICE_TBL.EXISTS(1) THEN G_UPD_ADJUSTED_UNIT_PRICE_TBL.DELETE; END IF;
IF G_PROCESSED_FLAG_TBL.EXISTS(1) THEN G_PROCESSED_FLAG_TBL.DELETE; END IF;
IF G_PRICE_FLAG_TBL.EXISTS(1) THEN G_PRICE_FLAG_TBL.DELETE; END IF;
IF G_LIST_PRICE_OVERRIDE_TBL.EXISTS(1) THEN G_LIST_PRICE_OVERRIDE_TBL.DELETE; END IF;
IF G_LINE_ID_TBL.EXISTS(1) THEN G_LINE_ID_TBL.DELETE; END IF;
IF G_PROCESSING_ORDER_TBL.EXISTS(1) THEN G_PROCESSING_ORDER_TBL.DELETE; END IF;
IF G_PRICING_STATUS_CODE_tbl.EXISTS(1) THEN G_PRICING_STATUS_CODE_tbl.DELETE; END IF;
IF G_PRICING_STATUS_TEXT_tbl.EXISTS(1) THEN G_PRICING_STATUS_TEXT_tbl.DELETE; END IF;
IF G_ROUNDING_FLAG_TBL.EXISTS(1) THEN G_ROUNDING_FLAG_TBL.DELETE; END IF;
IF G_ROUNDING_FACTOR_TBL.EXISTS(1) THEN G_ROUNDING_FACTOR_TBL.DELETE; END IF;
IF G_QUALIFIERS_EXIST_FLAG_TBL.EXISTS(1) THEN G_QUALIFIERS_EXIST_FLAG_TBL.DELETE; END IF;
IF G_PRICING_ATTRS_EXIST_FLAG_TBL.EXISTS(1) THEN G_PRICING_ATTRS_EXIST_FLAG_TBL.DELETE; END IF;
IF G_PRICE_LIST_ID_TBL.EXISTS(1) THEN G_PRICE_LIST_ID_TBL.DELETE; END IF;
IF G_PL_VALIDATED_FLAG_TBL.EXISTS(1) THEN G_PL_VALIDATED_FLAG_TBL.DELETE; END IF;
IF G_PRICE_REQUEST_CODE_TBL.EXISTS(1) THEN G_PRICE_REQUEST_CODE_TBL.DELETE; END IF;
IF G_USAGE_PRICING_TYPE_TBL.EXISTS(1) THEN G_USAGE_PRICING_TYPE_TBL.DELETE; END IF;
QP_PREQ_GRP.INSERT_LINES2
(p_LINE_INDEX => G_LINE_INDEX_TBL,
p_LINE_TYPE_CODE => G_LINE_TYPE_CODE_TBL,
p_PRICING_EFFECTIVE_DATE =>G_PRICING_EFFECTIVE_DATE_TBL,
p_ACTIVE_DATE_FIRST =>G_ACTIVE_DATE_FIRST_TBL,
p_ACTIVE_DATE_FIRST_TYPE =>G_ACTIVE_DATE_FIRST_TYPE_TBL,
p_ACTIVE_DATE_SECOND =>G_ACTIVE_DATE_SECOND_TBL,
p_ACTIVE_DATE_SECOND_TYPE =>G_ACTIVE_DATE_SECOND_TYPE_TBL,
p_LINE_QUANTITY => G_LINE_QUANTITY_TBL,
p_LINE_UOM_CODE => G_LINE_UOM_CODE_TBL,
p_REQUEST_TYPE_CODE => G_REQUEST_TYPE_CODE_TBL,
p_PRICED_QUANTITY => G_PRICED_QUANTITY_TBL,
p_PRICED_UOM_CODE => G_PRICED_UOM_CODE_TBL,
p_CURRENCY_CODE => G_CURRENCY_CODE_TBL,
p_UNIT_PRICE => G_UNIT_PRICE_TBL,
p_PERCENT_PRICE => G_PERCENT_PRICE_TBL,
p_UOM_QUANTITY => G_UOM_QUANTITY_TBL,
p_ADJUSTED_UNIT_PRICE =>G_ADJUSTED_UNIT_PRICE_TBL,
p_UPD_ADJUSTED_UNIT_PRICE =>G_UPD_ADJUSTED_UNIT_PRICE_TBL,
p_PROCESSED_FLAG =>G_PROCESSED_FLAG_TBL,
p_PRICE_FLAG =>G_PRICE_FLAG_TBL,
p_LINE_ID =>G_LINE_ID_TBL,
p_PROCESSING_ORDER =>G_PROCESSING_ORDER_TBL,
p_PRICING_STATUS_CODE =>G_PRICING_STATUS_CODE_tbl,
p_PRICING_STATUS_TEXT =>G_PRICING_STATUS_TEXT_tbl,
p_ROUNDING_FLAG =>G_ROUNDING_FLAG_TBL,
p_ROUNDING_FACTOR =>G_ROUNDING_FACTOR_TBL,
p_QUALIFIERS_EXIST_FLAG => G_QUALIFIERS_EXIST_FLAG_TBL,
p_PRICING_ATTRS_EXIST_FLAG =>G_PRICING_ATTRS_EXIST_FLAG_TBL,
p_PRICE_LIST_ID => G_PRICE_LIST_ID_TBL,
p_VALIDATED_FLAG => G_PL_VALIDATED_FLAG_TBL,
p_PRICE_REQUEST_CODE => G_PRICE_REQUEST_CODE_TBL,
p_USAGE_PRICING_TYPE =>G_USAGE_PRICING_TYPE_tbl,
p_LIST_PRICE_OVERRIDE_FLAG =>G_LIST_PRICE_OVERRIDE_TBL,
-- p_line_category =>G_LINE_CATEGORY_tbl,
x_status_code =>l_return_status_code,
x_status_text => l_price_return_msg);
FND_MESSAGE.set_name('OZF', 'OZF_ORDER_INSERT_ERR');
UPDATE ozf_offer_adjustments_b b
SET budget_adjusted_flag = 'Y',
object_version_number = object_version_number + 1,
status_code = 'CLOSED'
WHERE offer_adjustment_id = l_adjidbylisthdrid(t_i)
AND EXISTS
(SELECT 1
FROM ozf_funds_utilized_all_b
WHERE request_id = l_request_id
AND plan_id = b.list_header_id
UNION ALL
SELECT 1
FROM ozf_funds_utilized_all_b ofuab,
ozf_resale_adjustments ora
WHERE ofuab.plan_id = b.list_header_id
AND ora.list_header_id = b.list_header_id
AND ofuab.utilization_type = 'ACCRUAL'
AND ofuab.object_type = 'TP_ORDER');
SELECT offer_id, offer_type
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT 1
FROM oe_price_adjustments
WHERE line_id = p_line_id
AND list_header_id = p_list_header_id
AND list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines
START WITH to_list_line_id = p_list_line_id
CONNECT BY PRIOR from_list_line_id = to_list_line_id
UNION ALL
SELECT to_list_line_id FROM ozf_offer_adj_rltd_lines where to_list_line_id = p_list_line_id ) ;
SELECT 1
FROM oe_price_adjustments
WHERE line_id = p_line_id
AND list_header_id = p_list_header_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 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 party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_cust_account_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) offer_currency_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 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_type,
volume_type
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_discount_line_id
AND tier_type = 'PBH';
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 SUM(plan_curr_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;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_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';
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
SELECT nvl(transaction_currency_code, fund_request_curr_code) offer_currency_code,
transaction_currency_code,
beneficiary_account_id,
autopay_party_attr,
autopay_party_id,
description
FROM ozf_offers off, qp_list_headers_all qp
WHERE off.qp_list_header_id = p_list_header_id
AND off.qp_list_header_id = qp.list_header_id;
SELECT created_from_adjustments
FROM ozf_offer_adjustment_lines
WHERE list_line_id = p_list_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 SUM(plan_curr_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;
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 SUM(plan_curr_amount)
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines
START WITH to_list_line_id = p_list_line_id
CONNECT BY PRIOR from_list_line_id = to_list_line_id
UNION ALL
SELECT to_list_line_id FROM ozf_offer_adj_rltd_lines WHERE to_list_line_id =p_list_line_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 IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND adjustment_type_id in(-4,-5,-1);
SELECT org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
SELECT request_header_id
FROM ozf_sd_request_headers_all_b
WHERE offer_id = p_list_header_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
l_stmt := 'SELECT ' || l_column_name ||' FROM oe_order_lines_all WHERE line_id = :1 AND org_id = :2';
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
SELECT nvl(transaction_currency_code, fund_request_curr_code) offer_currency_code,
transaction_currency_code,
beneficiary_account_id,
autopay_party_attr,
autopay_party_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT created_from_adjustments
FROM ozf_offer_adjustment_lines
WHERE list_line_id = p_list_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 SUM(plan_curr_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;
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 SUM(plan_curr_amount)
FROM ozf_funds_utilized_all_b
WHERE plan_type = 'OFFR'
AND plan_id = p_list_header_id
AND list_line_id IN (SELECT from_list_line_id
FROM ozf_offer_adj_rltd_lines
START WITH to_list_line_id = p_list_line_id
CONNECT BY PRIOR from_list_line_id = to_list_line_id
UNION ALL
SELECT to_list_line_id FROM ozf_offer_adj_rltd_lines where to_list_line_id =p_list_line_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 IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT')
AND adjustment_type_id in(-4,-5,-1);
SELECT org_id
FROM hz_cust_site_uses_all
WHERE site_use_id = p_site_use_id;
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
p_adjIdByListHdrId.DELETE(p_ldets_rec.list_header_id);
SELECT offer_type, volume_offer_type,
custom_setup_id
FROM ozf_offers
WHERE qp_list_header_id = p_list_header_id;
SELECT *
FROM qp_ldets_v
WHERE line_index = p_index
ORDER BY list_line_id asc;
p_line_tbl(i).pricing_status_code <> QP_PREQ_PUB.G_STATUS_UPDATED;
l_ldets_tbl.DELETE;
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.plan_currency_code,
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) * plan_curr_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) * plan_curr_amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- ((adjl.modified_discount - oe.operand) * amount / oe.operand)
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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.plan_currency_code,
util.currency_code,
util.price_adjustment_id,
--NULL,
-- nirprasa 10/10/2011 Fixed Bug#13061780 ,This cursor was not taking care of different operand_types and zero operand.
DECODE (oe.operand_calculation_code,
--bug 13322965 - corrected formula for new price
/*
'NEWPRICE', (DECODE(oe.operand, 0, (oe.calculated_price - adjl.modified_discount) * oe.priced_quantity, (oe.operand - adjl.modified_discount) * plan_curr_amount /-oe.operand)),
*/
'NEWPRICE', (DECODE(oe.operand, 0, (oe.calculated_price - adjl.modified_discount) * oe.priced_quantity, (oe.operand - adjl.modified_discount) * oe.priced_quantity)),
'%', (DECODE(oe.operand, 0,adjl.modified_discount * oe.calculated_price * oe.priced_quantity / 100, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand)),
'AMT', (DECODE(oe.operand, 0,adjl.modified_discount * oe.priced_quantity, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand)),
'LUMPSUM', (DECODE(oe.operand, 0,adjl.modified_discount, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand))
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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 oe.list_line_id IN (SELECT from_list_line_id -- = oe.list_line_id : Fix for bug 12660466
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 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.plan_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.plan_currency_code,
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) * plan_curr_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) * plan_curr_amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- ((adjl.modified_discount - oe.operand) * amount / oe.operand)
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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.plan_currency_code,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.operand_calculation_code,
--bug 13322965 - corrected formula for new price
/*
'NEWPRICE', (DECODE(oe.operand, 0, (oe.calculated_price - adjl.modified_discount_td) * oe.priced_quantity, (oe.operand - adjl.modified_discount_td) * plan_curr_amount /-oe.operand)),
*/
'NEWPRICE', (DECODE(oe.operand, 0, (oe.calculated_price - adjl.modified_discount_td) * oe.priced_quantity, (oe.operand - adjl.modified_discount_td) * oe.priced_quantity)),
'%', (DECODE(oe.operand, 0,adjl.modified_discount_td * oe.calculated_price * oe.priced_quantity / 100, (adjl.modified_discount_td - oe.operand) * plan_curr_amount / oe.operand)),
'AMT', (DECODE(oe.operand, 0,adjl.modified_discount_td * oe.priced_quantity, (adjl.modified_discount_td - oe.operand) * plan_curr_amount / oe.operand)),
'LUMPSUM', (DECODE(oe.operand, 0,adjl.modified_discount_td, (adjl.modified_discount_td - oe.operand) * plan_curr_amount / oe.operand))
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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 oe.list_line_id IN (SELECT from_list_line_id -- = oe.list_line_id : Fix for bug 12660466
FROM ozf_offer_adj_rltd_lines adjr
START WITH adjr.from_list_line_id = adjl.list_line_id_td
AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_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.plan_currency_code,
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_td) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount_td) * plan_curr_amount /-oe.adjusted_amount_per_pqty)),
'%', DECODE(oe.operand, 0, adjl.modified_discount_td * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount_td - oe.operand) * plan_curr_amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount_td * ol.pricing_quantity, (adjl.modified_discount_td - oe.operand) * plan_curr_amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount_td, (adjl.modified_discount_td - oe.operand) * plan_curr_amount / oe.operand)
-- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
-- ((adjl.modified_discount_td - oe.operand) * amount / oe.operand)
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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.plan_currency_code,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE (oe.operand_calculation_code,
--bug 13322965 - corrected formula for new price
/*
'NEWPRICE', (DECODE(oe.operand, 0, (oe.calculated_price - adjl.modified_discount) * oe.priced_quantity, (oe.operand - adjl.modified_discount) * plan_curr_amount /-oe.operand)),
*/
'NEWPRICE', (DECODE(oe.operand, 0, (oe.calculated_price - adjl.modified_discount) * oe.priced_quantity, (oe.operand - adjl.modified_discount) * oe.priced_quantity)),
'%', (DECODE(oe.operand, 0,adjl.modified_discount * oe.calculated_price * oe.priced_quantity / 100, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand)),
'AMT', (DECODE(oe.operand, 0,adjl.modified_discount * oe.priced_quantity, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand)),
'LUMPSUM', (DECODE(oe.operand, 0,adjl.modified_discount, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand))
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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 oe.list_line_id IN (SELECT from_list_line_id -- = oe.list_line_id : Fix for bug 12660466
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 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.plan_currency_code,
util.currency_code,
util.price_adjustment_id,
--NULL,
DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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.plan_currency_code,
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) * plan_curr_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) * plan_curr_amount / oe.operand),
'AMT', DECODE(oe.operand, 0, adjl.modified_discount * oe.range_break_quantity, (adjl.modified_discount - oe.operand) * plan_curr_amount / oe.operand),
'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * plan_curr_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)
) plan_curr_amount
,util.org_id
,util.ship_to_site_use_id
,util.bill_to_site_use_id
,util.reference_type
,util.reference_id
,util.exchange_rate_type
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(plan_curr_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 exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_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,exchange_rate_date,gl_date,object_type
,object_id, gl_posted_flag, utilization_id FROM
( SELECT sum(fund_request_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,min(exchange_rate_date) exchange_rate_date
,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(fund_request_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,min(exchange_rate_date) exchange_rate_date
,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(fund_request_amount) old_Adj_amt
, order_line_id
,min(price_adjustment_id) price_adjustment_id
,min(exchange_rate_date) exchange_rate_date
,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,
line.shipping_quantity, -- Catch Weight ER
line.shipping_quantity_uom, -- Catch Weight ER
line.shipping_quantity2, -- Catch Weight ER
line.shipping_quantity_uom2, -- Catch Weight ER
line.fulfillment_base -- Catch Weight ER
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
line.quantity, -- Catch Weight ER : dummy column
line.uom_code, -- Catch Weight ER : dummy column
line.quantity, -- Catch Weight ER : dummy column
line.uom_code, -- Catch Weight ER : dummy column
NULL -- Catch Weight ER : 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),
transaction_currency_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 uom_code
FROM ozf_offer_discount_lines
WHERE offer_id = l_offer_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') IN ('Y','F') --ninarasi fix for bug 14610746
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;