[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT orderdetail.item_id,
SUM(DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
orderdetail.EXTENDED_PRICE,
Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE)))-
SUM(gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
orderdetail.ORDER_QTY1,itemmst.Item_UM)*
pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate))-
SUM(pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
orderdetail.exchange_rate,orderdetail.mul_div_sign)) Margin
FROM
OP_ORDR_HDR orderhdr,
OP_ORDR_DTL orderdetail,
IC_ITEM_MST itemmst
WHERE orderhdr.order_id = orderdetail.order_id
AND orderdetail.item_id = itemmst.item_id
AND trunc(orderdetail.ACTUAL_SHIPDATE)
between p_prd_start_date and p_prd_end_date
AND orderhdr.ORGN_CODE=p_Sales_orgn
GROUP BY orderhdr.orgn_code,orderdetail.item_id
ORDER BY Margin desc;
SELECT orderhdr.billcust_id,
SUM(DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
orderdetail.EXTENDED_PRICE,
Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE)))-
SUM(gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
orderdetail.ORDER_QTY1,itemmst.Item_UM)*
pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate))-
SUM(pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
orderdetail.exchange_rate,orderdetail.mul_div_sign)) Margin
FROM
OP_ORDR_HDR orderhdr,
OP_ORDR_DTL orderdetail,
IC_ITEM_MST itemmst
WHERE orderhdr.order_id = orderdetail.order_id
AND orderdetail.item_id = itemmst.item_id
AND trunc(orderdetail.ACTUAL_SHIPDATE)
between p_prd_start_date and p_prd_end_date
AND orderhdr.ORGN_CODE=p_Sales_orgn
GROUP BY orderhdr.orgn_code,orderhdr.billcust_id
ORDER BY Margin desc;
pv_pmisa_top_n_item.DELETE;
pv_pmisa_top_n_cust.DELETE;
SELECT item_id, SUM(line_margin) margin
FROM
(
SELECT orderdetail.item_id,
DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
orderdetail.EXTENDED_PRICE,
Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE))
-
gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
orderdetail.ORDER_QTY1,itemmst.Item_UM)*
pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate)
-
pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
orderdetail.exchange_rate,orderdetail.mul_div_sign) line_Margin
FROM
OP_ORDR_HDR orderhdr,
OP_ORDR_DTL orderdetail,
IC_ITEM_MST itemmst,
SY_ORGN_MST org,
GL_PLCY_MST pol
WHERE orderhdr.order_id = orderdetail.order_id
AND orderdetail.item_id = itemmst.item_id
AND trunc(orderdetail.ACTUAL_SHIPDATE)
between p_prd_start_date and p_prd_end_date
AND org.orgn_code = orderhdr.orgn_code
AND pol.co_code = org.co_code
AND pol.org_id = p_OU_ID
UNION ALL
SELECT itemmst.item_id,
(DECODE(sob.CURRENCY_CODE,
orderhdr.TRANSACTIONAL_CURR_CODE,
orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE,
orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE
* orderhdr.CONVERSION_RATE)
)
- (gmicuom.I2UOM_CV(itemmst.Item_Id,
0, orderdetail.ORDER_QUANTITY_UOM,
orderdetail.ORDERED_QUANTITY,itemmst.Item_UM)*
pmi_common_pkg.PMICO_GET_COST(itemmst.item_id,
fromwhse.whse_code,
p_cost_mthd,
orderdetail.Actual_Shipment_date)
)
- (pmi_sales_pkg.PMIOM_GET_CHARGE(
orderdetail.header_Id,
orderdetail.line_Id,
orderdetail.ordered_quantity*orderdetail.unit_selling_price,
orderhdr.transactional_curr_code,
sob.currency_code,
orderhdr.conversion_rate,
orderdetail.ordered_quantity)
) line_Margin
FROM
OE_ORDER_HEADERS_ALL orderhdr,
OE_ORDER_LINES_ALL orderdetail,
OE_SYSTEM_PARAMETERS_ALL masterorg,
MTL_SYSTEM_ITEMS msi,
IC_ITEM_MST itemmst,
HR_OPERATING_UNITS ou,
GL_SETS_OF_BOOKS sob,
IC_WHSE_MST fromwhse
WHERE
orderhdr.header_id = orderdetail.header_id
AND masterorg.org_id = orderhdr.org_id
AND msi.organization_id = masterorg.master_organization_id
AND msi.inventory_item_id = orderdetail.inventory_item_id
AND itemmst.item_no = msi.segment1
AND trunc(orderdetail.ACTUAL_SHIPMENT_DATE) between p_prd_start_date and p_prd_end_date
AND ou.organization_id = p_OU_ID
AND ou.organization_id = orderhdr.org_id
AND sob.set_of_books_id = ou.set_of_books_id
AND fromwhse.mtl_organization_id = orderdetail.ship_from_org_id
)
GROUP BY item_id
ORDER BY Margin desc;
SELECT cust_id, SUM(line_margin) margin
FROM
(
SELECT orderhdr.billcust_id cust_id,
DECODE(orderdetail.BASE_CURRENCY,orderdetail.BILLING_CURRENCY,
orderdetail.EXTENDED_PRICE,
Decode(orderdetail.mul_div_sign,0,orderdetail.EXTENDED_PRICE*orderdetail.EXCHANGE_RATE,
orderdetail.EXTENDED_PRICE/orderdetail.EXCHANGE_RATE))
-
gmicuom.I2UOM_CV(orderdetail.Item_Id,0,orderdetail.ORDER_UM1,
orderdetail.ORDER_QTY1,itemmst.Item_UM)*
pmi_common_pkg.PMICO_GET_COST(orderdetail.item_id,
orderdetail.From_whse,p_cost_mthd,orderdetail.Actual_Shipdate)
-
pmi_sales_pkg.PMISA_GET_CHARGE(orderdetail.order_Id,orderdetail.line_Id,
orderdetail.extended_price,orderdetail.billing_currency,orderdetail.base_currency,
orderdetail.exchange_rate,orderdetail.mul_div_sign) line_Margin
FROM
OP_ORDR_HDR orderhdr,
OP_ORDR_DTL orderdetail,
IC_ITEM_MST itemmst,
SY_ORGN_MST org,
GL_PLCY_MST pol
WHERE orderhdr.order_id = orderdetail.order_id
AND orderdetail.item_id = itemmst.item_id
AND trunc(orderdetail.ACTUAL_SHIPDATE)
between p_prd_start_date and p_prd_end_date
AND org.orgn_code = orderhdr.orgn_code
AND pol.co_code = org.co_code
AND pol.org_id = p_OU_ID
UNION ALL
SELECT billingcustomer.party_id cust_id,
(DECODE(sob.CURRENCY_CODE,
orderhdr.TRANSACTIONAL_CURR_CODE,
orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE,
orderdetail.ORDERED_QUANTITY * orderdetail.UNIT_SELLING_PRICE
* orderhdr.CONVERSION_RATE)
)
- (gmicuom.I2UOM_CV(itemmst.Item_Id,
0, orderdetail.ORDER_QUANTITY_UOM,
orderdetail.ORDERED_QUANTITY,itemmst.Item_UM)*
pmi_common_pkg.PMICO_GET_COST(itemmst.item_id,
fromwhse.whse_code,
p_cost_mthd,
orderdetail.Actual_Shipment_date)
)
- (pmi_sales_pkg.PMIOM_GET_CHARGE(
orderdetail.header_Id,
orderdetail.line_Id,
orderdetail.ordered_quantity*orderdetail.unit_selling_price,
orderhdr.transactional_curr_code,
sob.currency_code,
orderhdr.conversion_rate,
orderdetail.ordered_quantity)
) line_Margin
FROM
OE_ORDER_HEADERS_ALL orderhdr,
OE_ORDER_LINES_ALL orderdetail,
OE_SYSTEM_PARAMETERS_ALL masterorg,
MTL_SYSTEM_ITEMS msi,
IC_ITEM_MST itemmst,
HR_OPERATING_UNITS ou,
GL_SETS_OF_BOOKS sob,
IC_WHSE_MST fromwhse,
PMI_HZ_PARTY_V billingcustomer
WHERE
orderhdr.header_id = orderdetail.header_id
AND masterorg.org_id = orderhdr.org_id
AND msi.organization_id = masterorg.master_organization_id
AND msi.inventory_item_id = orderdetail.inventory_item_id
AND itemmst.item_no = msi.segment1
AND trunc(orderdetail.ACTUAL_SHIPMENT_DATE) between p_prd_start_date and p_prd_end_date
AND ou.organization_id = p_OU_ID
AND ou.organization_id = orderhdr.org_id
AND sob.set_of_books_id = ou.set_of_books_id
AND fromwhse.mtl_organization_id = orderdetail.ship_from_org_id
AND billingcustomer.SITE_USE_ID(+) = orderhdr.invoice_to_org_id
)
GROUP BY cust_id
ORDER BY Margin desc;
pv_pmisa_top_n_item_by_ou.DELETE;
pv_pmisa_top_n_cust_by_ou.DELETE;
select start_date into pv_pmisa_vd_start_date
from pmi_gl_calendar_v
where period_year=p_from_year and period_name=p_from_period and co_code=p_sales_company;
select end_date into pv_pmisa_vd_end_date
from pmi_gl_calendar_v
where period_year=p_to_year and period_name=p_to_period and co_code=p_sales_company;
SELECT SUM(abs(DECODE(P_Base_Currency,p_Billing_Currency,
Extended_amount,
Decode(p_mul_div_sign,0,Extended_amount*p_Exchange_Rate,
Extended_amount/p_Exchange_Rate)))
)
FROM op_ordr_chg a, op_chrg_mst b
WHERE a.order_id = P_order_id
AND a.line_id = P_line_id
AND a.charge_id = b.charge_id
AND b.charge_type in (20,30);
SELECT sum(abs(DECODE(P_Base_Currency,p_Billing_Currency,
Extended_amount,
Decode(p_mul_div_sign,0,Extended_amount*p_Exchange_Rate,
Extended_amount/p_Exchange_Rate))))
FROM op_ordr_chg a, op_chrg_mst b
WHERE a.order_id = P_order_id
AND a.line_id IS NULL
AND a.charge_id = b.charge_id
AND b.charge_type in (20,30);
SELECT SUM(DECODE(Base_Currency,Billing_Currency,
Extended_price,
Decode(mul_div_sign,0,Extended_price*Exchange_Rate,
Extended_price/Exchange_Rate)))
FROM op_ordr_dtl
WHERE order_id = P_order_id;
SELECT SUM(opa.adjusted_amount) * AVG(ool.ordered_quantity) line_level_discount
FROM oe_price_adjustments_v opa
, oe_order_lines_all ool
WHERE opa.line_id =p_line_id
and ool.line_id = p_line_id
and ool.header_id = p_header_id
and nvl(opa.applied_flag,'Y') = 'Y'
and nvl(opa.accrual_flag,'N') = 'N'
and list_line_type_code = 'DIS';
SELECT SUM(decode(opa.arithmetic_operator,
null, 0,
'%', opa.operand*ool.unit_list_price/100,
'AMT',opa.operand,
'NEWPRICE',ool.unit_list_price - opa.operand) * ool.ordered_quantity
) order_level_discount
FROM oe_price_adjustments_v opa
, oe_order_lines_all ool
WHERE opa.HEADER_ID = p_header_id
and opa.line_id is null
and ool.line_id = p_line_id
and ool.header_id = p_header_id
and nvl(opa.applied_flag,'Y') = 'Y'
and nvl(opa.accrual_flag,'N') = 'N'
and list_line_type_code = 'DIS';