[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_sold_to_org_id;
SELECT 'Y'
FROM ozf_na_customers_temp
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 ROWNUM = 1;
SELECT 'Y'
FROM ozf_na_customers_temp
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 ROWNUM = 1;
SELECT party_id
FROM hz_cust_accounts
WHERE cust_account_id = p_sold_to_org_id;
SELECT 'Y'
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_qp_list_header_id
AND ROWNUM = 1;
SELECT 'Y'
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_qp_list_header_id
AND ROWNUM = 1;
SELECT qualifier_attr_value terr_id
FROM ozf_offer_qualifiers
WHERE offer_id = p_offer_id;
SELECT terr_qual_id
FROM jtf_terr_qual_all
WHERE terr_id = p_terr_id;
SELECT COUNT(1)
FROM jtf_terr_values_all
WHERE low_value_char = p_country_code
AND terr_qual_id = p_terr_qual_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS(SELECT 1
FROM ozf_activity_products
WHERE item = p_inventory_item_id
AND item_type = 'PRICING_ATTRIBUTE1'
AND object_class = 'OFFR'
AND object_id = p_qp_list_header_id);
SELECT COUNT(*)
FROM ozf_offer_qualifiers
WHERE offer_id = p_offer_id
AND active_flag = 'Y';
SELECT qualifier_id
FROM ozf_offer_qualifiers
WHERE offer_id = p_offer_id
AND active_flag = 'Y';
SELECT NVL(qualifier_context,
DECODE(qualifier_attribute,
'BUYER', 'CUSTOMER_GROUP',
'CUSTOMER_BILL_TO', 'CUSTOMER',
'CUSTOMER', 'CUSTOMER',
'LIST', 'CUSTOMER_GROUP',
'SEGMENT', 'CUSTOMER_GROUP',
'TERRITORY', 'TERRITORY',
'SHIP_TO', 'CUSTOMER')) qualifier_context,
DECODE(qualifier_attribute,
'BUYER', 'QUALIFIER_ATTRIBUTE3',
'CUSTOMER_BILL_TO', 'QUALIFIER_ATTRIBUTE14',
'CUSTOMER', 'QUALIFIER_ATTRIBUTE2',
'LIST', 'QUALIFIER_ATTRIBUTE1',
'SEGMENT', 'QUALIFIER_ATTRIBUTE2',
'TERRITORY', 'QUALIFIER_ATTRIBUTE1',
'SHIP_TO', 'QUALIFIER_ATTRIBUTE11',
qualifier_attribute) qualifier_attribute,
qualifier_attr_value,
'=' comparison_operator_code
FROM ozf_offer_qualifiers
WHERE qualifier_id = p_qualifier_id;
FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
FND_DSQL.add_text('last_update_login,confidential_flag,');
FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
SELECT a.functional_area_id
FROM mtl_default_category_sets a,
mtl_category_sets_b b,
mtl_categories c
WHERE a.functional_area_id in (7,11)
AND a.category_set_id = b.category_set_id
AND c.structure_id = b.structure_id
AND c.category_id = p_category_id;
SELECT product_id,
product_level,
off_discount_product_id,
offer_discount_line_id,
NVL(uom_code, 'NA') uom_code
FROM ozf_offer_discount_products
WHERE excluder_flag = 'N'
AND offer_id = p_offer_id;
SELECT product_level,
product_id
FROM ozf_offer_discount_products
WHERE parent_off_disc_prod_id = p_off_discount_product_id
AND excluder_flag = 'Y';
SELECT discount,
discount_type,
NVL(volume_from,0),
volume_to,
DECODE(volume_type, 'PRICING_ATTRIBUTE12', 'AMT', 'PRICING_ATTRIBUTE10', 'QTY', NULL, 'NA')
FROM ozf_offer_discount_lines
WHERE offer_discount_line_id = p_offer_discount_line_id;
FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
FND_DSQL.add_text('SELECT inventory_item_id,');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
FND_DSQL.add_text('SELECT ');
FND_DSQL.add_text('SELECT ');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
SELECT product_id,
product_level,
off_discount_product_id,
NVL(uom_code, 'NA') uom_code
FROM ozf_offer_discount_products
WHERE excluder_flag = 'N'
AND offer_id = p_offer_id;
SELECT product_level,
product_id
FROM ozf_offer_discount_products
WHERE parent_off_disc_prod_id = p_off_discount_product_id
AND excluder_flag = 'Y';
SELECT discount,
discount_type,
NVL(volume_from,0) volume_from,
volume_to,
DECODE(volume_type, 'PRICING_ATTRIBUTE12', 'AMT', 'PRICING_ATTRIBUTE10', 'QTY', NULL, 'NA') volume_type
FROM ozf_offer_discount_lines
WHERE offer_id = p_offer_id;
FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
FND_DSQL.add_text('SELECT inventory_item_id,');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
FND_DSQL.add_text('SELECT ');
FND_DSQL.add_text('SELECT ');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
SELECT COUNT(*)
FROM ozf_na_products_temp
WHERE inventory_item_id = p_product_id
AND product_level = 'PRODUCT';
SELECT discount,
discount_type,
volume_type,
volume_from,
volume_to,
uom
FROM ozf_na_products_temp
WHERE inventory_item_id = p_product_id
AND product_level = 'FAMILY';
SELECT discount,
discount_type,
volume_type,
volume_from,
volume_to, uom
FROM ozf_na_products_temp
WHERE inventory_item_id = p_product_id
AND product_level = 'PRODUCT';
SELECT b.maximum_compensation
FROM pv_ge_benefits_vl a, pv_benft_products b
WHERE a.benefit_id = b.benefit_id
AND a.benefit_type_code = 'PVREFFRL'
AND a.additional_info_1 = p_offer_id
AND b.product_category_id = p_category_id;
SELECT NVL(DECODE(gl_posted_flag, 'Y', plan_curr_amount), 0) line_amount, product_id
FROM ozf_funds_utilized_all_b
WHERE reference_type = 'LEAD_REFERRAL'
AND reference_id = p_referral_id
AND plan_type = 'OFFR'
AND plan_id = p_list_hdr_id
AND object_type = 'ORDER'
AND object_id = p_order_hdr_id;
l_stmt := 'SELECT';
SELECT ozf_na_conc_exceptions_s.NEXTVAL
FROM DUAL;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM ozf_na_conc_exceptions
WHERE na_conc_exception_id = p_na_conc_exception_id);
INSERT INTO ozf_na_conc_exceptions(na_conc_exception_id
,act_budget_used_by_id
,arc_act_budget_used_by
,budget_source_type
,budget_source_id
,request_amount
,request_currency
,request_date
,status_code
,approved_amount
,approved_in_currency
,approval_date
,approver_id
,transfer_type
,requester_id
,object_type
,object_id
,product_level_type
,product_id
,cust_account_id
,utilization_type
,adjustment_date
,gl_date
,billto_cust_account_id
,reference_type
,reference_id
,order_line_id
,org_id)
VALUES(l_na_conc_exception_id
,p_act_budgets_rec.act_budget_used_by_id
,p_act_budgets_rec.arc_act_budget_used_by
,p_act_budgets_rec.budget_source_type
,p_act_budgets_rec.budget_source_id
,p_act_budgets_rec.request_amount
,p_act_budgets_rec.request_currency
,p_act_budgets_rec.request_date
,p_act_budgets_rec.status_code
,p_act_budgets_rec.approved_amount
,p_act_budgets_rec.approved_in_currency
,p_act_budgets_rec.approval_date
,p_act_budgets_rec.approver_id
,p_act_budgets_rec.transfer_type
,p_act_budgets_rec.requester_id
,p_act_util_rec.object_type
,p_act_util_rec.object_id
,p_act_util_rec.product_level_type
,p_act_util_rec.product_id
,p_act_util_rec.cust_account_id
,p_act_util_rec.utilization_type
,p_act_util_rec.adjustment_date
,p_act_util_rec.gl_date
,p_act_util_rec.billto_cust_account_id
,p_act_util_rec.reference_type
,p_act_util_rec.reference_id
,p_act_util_rec.order_line_id
,p_act_util_rec.org_id);
SELECT *
FROM ozf_na_conc_exceptions;
DELETE FROM ozf_na_conc_exceptions
WHERE na_conc_exception_id = l_exception_rec.na_conc_exception_id;
SELECT ozf.offer_id,
ozf.qp_list_header_id,
ozf.latest_na_completion_date,
ozf.custom_setup_id,
ozf.tier_level,
NVL(ozf.transaction_currency_code, ozf.fund_request_curr_code) fund_request_curr_code,
transaction_currency_code,
ozf.qualifier_id,
ozf.na_rule_header_id,
ozf.owner_id,
TRUNC(qp.start_date_active) start_date_active,
TRUNC(qp.end_date_active + 1) - (1/86400) end_date_active,
qp.orig_org_id,
qp_tl.description offer_name,
ozf.sales_method_flag,
NVL(ozf.resale_line_id_processed, 0) resale_line_id_processed
FROM ozf_offers ozf,
qp_list_headers_all_b qp,
qp_list_headers_tl qp_tl
WHERE ozf.offer_type = 'NET_ACCRUAL'
AND ozf.status_code = 'ACTIVE'
AND ozf.offer_id = NVL(p_offer_id, ozf.offer_id)
AND ozf.qp_list_header_id = qp.list_header_id
AND qp.list_header_id = qp_tl.list_header_id
-- AND qp.orig_org_id = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))
AND qp_tl.language = USERENV('LANG');
SELECT na_deduction_rule_id
FROM ozf_na_rule_lines
WHERE na_rule_header_id = p_na_rule_header_id
AND active_flag = 'Y';
SELECT a.na_deduction_rule_id,
a.transaction_source_code,
a.transaction_type_code,
a.deduction_identifier_id,
a.deduction_identifier_org_id,
b.name
FROM ozf_na_deduction_rules_b a,
ozf_na_deduction_rules_tl b
WHERE a.na_deduction_rule_id = b.na_deduction_rule_id
AND b.language = USERENV('LANG')
AND a.na_deduction_rule_id = p_deduction_rule_id;
SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
ol.header_id,
ol.line_id,
ol.invoice_to_org_id,
ol.ship_to_org_id,
ol.sold_to_org_id,
ol.inventory_item_id,
ol.shipped_quantity,
ol.fulfilled_quantity,
ol.invoiced_quantity,
ol.pricing_quantity,
ol.pricing_quantity_uom,
ol.unit_selling_price,
ol.org_id,
ol.fulfillment_date conv_date,
oh.transactional_curr_code,
ol.shipping_quantity, -- Catch Weight ER
ol.shipping_quantity_uom, -- Catch Weight ER
ol.shipping_quantity2, -- Catch Weight ER
ol.shipping_quantity_uom2, -- Catch Weight ER
ol.fulfillment_base -- Catch Weight ER
FROM ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) na,
oe_order_lines_all ol,
oe_order_headers_all oh
WHERE ol.inventory_item_id = na.inventory_item_id
--AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <> 'RETURN'
AND OL.ACTUAL_SHIPMENT_DATE IS NOT NULL
AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
--AND ( NVL(OL.ACTUAL_SHIPMENT_DATE, OL.FULFILLMENT_DATE) BETWEEN p_start_date AND p_end_date)
AND OL.HEADER_ID = OH.HEADER_ID
UNION ALL
SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
ol.header_id,
ol.line_id,
ol.invoice_to_org_id,
ol.ship_to_org_id,
ol.sold_to_org_id,
ol.inventory_item_id,
ol.shipped_quantity,
ol.fulfilled_quantity,
ol.invoiced_quantity,
ol.pricing_quantity,
ol.pricing_quantity_uom,
ol.unit_selling_price,
ol.org_id,
ol.fulfillment_date conv_date,
oh.transactional_curr_code,
ol.shipping_quantity, -- Catch Weight ER
ol.shipping_quantity_uom, -- Catch Weight ER
ol.shipping_quantity2, -- Catch Weight ER
ol.shipping_quantity_uom2, -- Catch Weight ER
ol.fulfillment_base -- Catch Weight ER
FROM ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) na,
oe_order_lines_all ol,
oe_order_headers_all oh
WHERE ol.inventory_item_id = na.inventory_item_id
--AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <> 'RETURN'
AND OL.ACTUAL_SHIPMENT_DATE IS NULL
AND (OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date)
--AND ( NVL(OL.ACTUAL_SHIPMENT_DATE, OL.FULFILLMENT_DATE) BETWEEN p_start_date AND p_end_date)
AND OL.HEADER_ID = OH.HEADER_ID;
SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
OL.SHIPPING_QUANTITY, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
OL.SHIPPING_QUANTITY2, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
OL.FULFILLMENT_BASE -- Catch Weight ER
FROM
( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
WHERE
OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
--AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
AND OL.ACTUAL_SHIPMENT_DATE is not null
AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
AND OL.ORG_ID = p_offer_org_id
AND OL.HEADER_ID = OH.HEADER_ID
UNION ALL
SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
OL.SHIPPING_QUANTITY, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
OL.SHIPPING_QUANTITY2, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
OL.FULFILLMENT_BASE -- Catch Weight ER
FROM
( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
WHERE
OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
--AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
AND OL.ACTUAL_SHIPMENT_DATE is NULL
AND (OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date)
AND OL.ORG_ID = p_offer_org_id
AND OL.HEADER_ID = OH.HEADER_ID;
SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
OL.SHIPPING_QUANTITY, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
OL.SHIPPING_QUANTITY2, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
OL.FULFILLMENT_BASE -- Catch Weight ER
FROM
( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
WHERE
OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
--AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
AND OL.ACTUAL_SHIPMENT_DATE is not null
AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
AND OL.HEADER_ID = OH.HEADER_ID
UNION ALL
SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
OL.SHIPPING_QUANTITY, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
OL.SHIPPING_QUANTITY2, -- Catch Weight ER
OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
OL.FULFILLMENT_BASE -- Catch Weight ER
FROM
( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
WHERE
OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
--AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
AND OL.ACTUAL_SHIPMENT_DATE is NULL
AND (OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date)
AND OL.HEADER_ID = OH.HEADER_ID;
SELECT resale_header_id header_id,
resale_line_id line_id,
date_ordered actual_shipment_date,
NULL fulfillment_date,
bill_to_site_use_id invoice_to_org_id,
ship_to_site_use_id ship_to_org_id,
bill_to_cust_account_id sold_to_org_id,
inventory_item_id,
quantity shipped_quantity,
quantity fulfilled_quantity,
quantity invoiced_quantity,
quantity pricing_quantity,
uom_code pricing_quantity_uom,
selling_price unit_selling_price,
org_id,
NVL(exchange_rate_date, date_ordered) conv_date,
currency_code transactional_curr_code
FROM ozf_resale_lines_all
WHERE inventory_item_id IN ( SELECT na.inventory_item_id
FROM ozf_na_products_temp na)
-- AND ol.flow_status_code IN ('SHIPPED','CLOSED')
-- AND ol.cancelled_flag = 'N'
-- AND ol.line_category_code <> 'RETURN'
AND TRUNC(date_ordered) >= TRUNC(p_offer_start_date)
AND TRUNC(date_ordered) <= TRUNC(NVL(p_offer_end_date, SYSDATE))
AND org_id = NVL(p_offer_org_id, org_id)
AND quantity > 0
AND resale_header_id > p_resale_line_id
ORDER BY resale_line_id;
SELECT NVL(a.extended_amount, 0) extended_amount,
a.inventory_item_id,
a.quantity_credited,
a.quantity_invoiced,
a.uom_code,
b.sold_to_customer_id,
b.bill_to_site_use_id,
b.ship_to_site_use_id,
b.invoice_currency_code,
b.customer_trx_id,
b.complete_flag,
b.trx_date conv_date,
a.customer_trx_line_id
FROM ra_customer_trx_lines_all a,
ra_customer_trx_all b
WHERE a.inventory_item_id IN ( SELECT na.inventory_item_id
FROM ozf_na_products_temp na)
AND a.line_type = 'LINE'
AND a.customer_trx_id = b.customer_trx_id
AND b.complete_flag = 'Y'
AND b.cust_trx_type_id = p_cust_trx_type_id
AND b.trx_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
AND b.org_id = p_org_id;
SELECT
/*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
OL.HEADER_ID,
OL.LINE_ID,
OL.INVOICE_TO_ORG_ID,
OL.SHIP_TO_ORG_ID,
OL.SOLD_TO_ORG_ID,
OL.INVENTORY_ITEM_ID,
OL.SHIPPED_QUANTITY,
OL.FULFILLED_QUANTITY,
OL.INVOICED_QUANTITY,
OL.PRICING_QUANTITY,
OL.PRICING_QUANTITY_UOM,
OL.UNIT_SELLING_PRICE,
OL.ORG_ID,
OL.FULFILLMENT_DATE CONV_DATE,
OH.TRANSACTIONAL_CURR_CODE
FROM
( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP) NA,
OE_ORDER_LINES_ALL OL,
OE_ORDER_HEADERS_ALL OH
WHERE OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
AND OL.OPEN_FLAG = 'N'
AND OL.CANCELLED_FLAG = 'N'
AND OL.LINE_CATEGORY_CODE = 'RETURN'
AND OL.ACTUAL_ARRIVAL_DATE IS NOT NULL
AND ( OL.ACTUAL_ARRIVAL_DATE BETWEEN p_start_date AND p_end_date )
AND OL.HEADER_ID = OH.HEADER_ID
AND OH.ORDER_TYPE_ID = p_order_type_id
UNION ALL
SELECT
/*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
OL.HEADER_ID,
OL.LINE_ID,
OL.INVOICE_TO_ORG_ID,
OL.SHIP_TO_ORG_ID,
OL.SOLD_TO_ORG_ID,
OL.INVENTORY_ITEM_ID,
OL.SHIPPED_QUANTITY,
OL.FULFILLED_QUANTITY,
OL.INVOICED_QUANTITY,
OL.PRICING_QUANTITY,
OL.PRICING_QUANTITY_UOM,
OL.UNIT_SELLING_PRICE,
OL.ORG_ID,
OL.FULFILLMENT_DATE CONV_DATE,
OH.TRANSACTIONAL_CURR_CODE
FROM
( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP) NA,
OE_ORDER_LINES_ALL OL,
OE_ORDER_HEADERS_ALL OH
WHERE OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
AND OL.OPEN_FLAG = 'N'
AND OL.CANCELLED_FLAG = 'N'
AND OL.LINE_CATEGORY_CODE = 'RETURN'
AND OL.ACTUAL_ARRIVAL_DATE IS NULL
AND ( OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date )
AND OL.HEADER_ID = OH.HEADER_ID
AND OH.ORDER_TYPE_ID = p_order_type_id;
SELECT NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0) line_amount,
a.cust_account_id,
a.adjustment_date conv_date,
a.currency_code,
a.org_id --Added for bug 7030415
FROM ozf_funds_utilized_all_b a,
ozf_offers b,
ozf_na_products_temp c
WHERE a.plan_type = 'OFFR'
AND a.plan_id = b.qp_list_header_id
AND b.qp_list_header_id <> p_qp_list_header_id
AND a.adjustment_date BETWEEN p_start_date and p_end_date
AND a.utilization_type IN ('ACCRUAL','ADJUSTMENT')
AND b.activity_media_id = p_activity_media_id
AND a.product_id = c.inventory_item_id
AND a.product_level_type = 'PRODUCT';*/
SELECT a.order_line_id,
--SUM(NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0)) line_amount,
NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0) line_amount, -- Fix for Bug 16301672
a.cust_account_id,
a.adjustment_date conv_date,
a.currency_code,
a.object_type,
a.reference_type,
a.reference_id,
a.org_id --Added for bug 7030415
FROM ozf_funds_utilized_all_b a,
ozf_offers b,
ozf_na_products_temp c
WHERE a.plan_type = 'OFFR'
AND a.plan_id = b.qp_list_header_id
AND b.qp_list_header_id <> p_qp_list_header_id
AND a.adjustment_date BETWEEN p_start_date and p_end_date
AND a.utilization_type IN ('ACCRUAL','ADJUSTMENT')
AND b.activity_media_id = p_activity_media_id
AND a.product_id = c.inventory_item_id
AND a.product_level_type = 'PRODUCT'
ORDER BY a.product_id, a.product_level_type, a.order_line_id; -- Fix for Bug 16301672
SELECT o.inventory_item_id inventory_item_id,
NVL(o.shipping_quantity, o.pricing_quantity) pricing_quantity, -- Catch Weight ER
o.pricing_quantity_uom pricing_quantity_uom,
o.line_id order_line_id,
o.org_id,
o.shipping_quantity, -- Catch Weight ER
o.shipping_quantity_uom, -- Catch Weight ER
o.shipping_quantity2, -- Catch Weight ER
o.shipping_quantity_uom2, -- Catch Weight ER
o.fulfillment_base -- Catch Weight ER
FROM oe_order_lines_all o
WHERE o.line_id = p_order_line_id;
SELECT inventory_item_id,
quantity_credited,
uom_code,
org_id -- added for bug # 10379136
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id = p_cust_trx_line_id;
SELECT inventory_item_id,
quantity_invoiced,
uom_code,
org_id -- added for bug # 10379136
FROM ra_customer_trx_lines_all
WHERE customer_trx_line_id = p_cust_trx_line_id;
SELECT NVL(SUM(plan_curr_amount),0)
FROM ozf_funds_utilized_all_b
WHERE reference_type = 'TRX_LINE'
AND reference_id = p_customer_trx_line_id
AND product_id = p_inventory_item_id
AND product_level_type = 'PRODUCT'
AND plan_type = 'OFFR'
AND plan_id = p_qp_list_header_id;
SELECT exchange_rate_type
FROM ozf_sys_parameters_all
WHERE org_id = p_org_id;
SELECT 1
FROM gl_period_statuses gl, ozf_sys_parameters_all sp
WHERE gl.application_id = 101
AND sp.org_id = p_org_id
AND sp.set_of_books_id = gl.set_of_books_id
AND gl.adjustment_period_flag = 'N'
AND p_gl_date BETWEEN gl.start_date AND gl.end_date
AND gl.closing_status IN ('O', 'F');
SELECT cust.cust_account_id
FROM hz_cust_acct_sites_all acct_site,
hz_cust_site_uses_all site_use,
hz_cust_accounts cust
WHERE 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 = p_invoice_to_org_id ;
SELECT terr_val.low_value_char
FROM ozf_offer_qualifiers offer_qual,
jtf_terr_qual_all terr_qual,
jtf_terr_values_all terr_val
WHERE offer_qual.offer_id = p_offer_id
AND offer_qual.qualifier_attr_value = terr_qual.terr_id
AND terr_qual.qual_usg_id = -1065 -- Pick Country Qualifier only
AND terr_qual.terr_qual_id = terr_val.terr_qual_id;
SELECT hzloc.country
FROM hz_cust_site_uses_all hzcsua,
hz_cust_acct_sites_all hzcasa,
hz_locations hzloc,
hz_party_sites hzps
WHERE hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id
AND hzcasa.party_site_id = hzps.party_site_id
AND hzps.location_id = hzloc.location_id
AND hzcsua.status = 'A'
AND hzcsua.site_use_id = p_site_use_id;
SELECT uom_code
FROM ozf_offer_discount_lines
WHERE offer_id = l_offer_id;
l_terr_countries_tbl.delete;
INSERT INTO OZF_NA_UNIQUE_PRODUCTS_TEMP(inventory_item_id)
SELECT DISTINCT inventory_item_id FROM ozf_na_products_temp;
SELECT count(*) INTO l_tot_products
FROM OZF_NA_UNIQUE_PRODUCTS_TEMP;
SELECT count(*) INTO l_tot_customers
FROM ozf_na_customers_temp;
l_order_line_tbl.delete;
l_ar_trx_line_tbl.delete;
l_return_line_tbl.delete;
l_act_budgets_rec_tbl.DELETE;
UPDATE ozf_offers
SET latest_na_completion_date = l_as_of_date
WHERE offer_id = l_net_accrual_offers.offer_id;
l_idsm_line_tbl.delete;
UPDATE ozf_offers
SET resale_line_id_processed = l_idsm_line_processed
WHERE offer_id = l_net_accrual_offers.offer_id;
SELECT offer_type,
tier_level,
qp_list_header_id,
custom_setup_id
FROM ozf_offers
WHERE offer_id = p_offer_id;
SELECT a.*
FROM oe_order_lines_all a
WHERE TRUNC(NVL(a.actual_shipment_date,a.fulfillment_date))
BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
AND a.flow_status_code IN ('SHIPPED','CLOSED')
AND a.cancelled_flag = 'N'
AND a.line_category_code <> 'RETURN';
SELECT offer_type, tier_level, qp_list_header_id, custom_setup_id
FROM ozf_offers
WHERE offer_id = p_offer_id;
SELECT a.header_id,
a.line_id,
a.invoice_to_org_id,
a.ship_to_org_id,
a.sold_to_org_id
FROM oe_order_lines_all a
WHERE (NVL(a.actual_shipment_date,a.fulfillment_date)) BETWEEN p_start_date AND p_end_date
-- AND a.flow_status_code IN ('SHIPPED','CLOSED')
AND a.booked_flag = 'Y'
AND a.cancelled_flag = 'N'
AND a.line_category_code <> 'RETURN'
AND a.inventory_item_id = p_product_id;
l_header_id_tbl.delete ;
l_line_id_tbl.delete;
l_invoice_to_org_id_tbl.delete;
l_ship_to_org_id_tbl.delete;
l_sold_to_org_id_tbl.delete;