Reports2017-11-18T12:27:27+00:00

ONT Order Discount Detail

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Order Discount Detail Report (XML)
Short Name: OEXPRPRD_XML
DB package: ONT_OEXPRPRD_XMLP_PKG
SELECT	 OT.NAME ORDER_TYPE,
	DECODE(:P_SORT_BY, 'CUSTOMER', ORG.NAME) DUMMY_CUSTOMER,
	H.ORDER_NUMBER ORDER_NUMBER,
	ORG.NAME CUSTOMER_NAME,
	L.LINE_NUMBER  LINE_NUM,
	L.SHIPMENT_NUMBER  SHIP_NUM,
	L.OPTION_NUMBER  OPTION_NUM,
                   DECODE(ITEM_TYPE_CODE,'SERVICE',
                                   L.LINE_NUMBER ||'.'|| L.SHIPMENT_NUMBER||'.'|| L.OPTION_NUMBER ||'.'||
                                                             L.COMPONENT_NUMBER ||'.'|| L.SERVICE_NUMBER,          
                   L.LINE_NUMBER || '.'
                            ||  L.SHIPMENT_NUMBER || decode(L.OPTION_NUMBER,null,null,'.'||L.OPTION_NUMBER)) LINE_SHIP_OPTION_NUM,
l.item_identifier_type,
L.ORDERED_ITEM OI,
L.ORDERED_ITEM_ID OID,
L.INVENTORY_ITEM_ID IID,
&RP_ITEM_FLEX_ALL_SEG ITEM_FLEX,
	DECODE (L.LINE_CATEGORY_CODE,'RETURN', 
		0-NVL( L.ORDERED_QUANTITY,0) , NVL(L.ORDERED_QUANTITY,0) ) QUANTITY,
	NVL(L.UNIT_LIST_PRICE,0)  LIST_PRICE,
	NVL(L.UNIT_SELLING_PRICE,0) SELL_PRICE,
	H.TRANSACTIONAL_CURR_CODE CURRENCY_CODE,
	LH.NAME DISCOUNT_NAME,
    &Item_dsp Item_dsp,
DECODE(PA.MODIFIER_LEVEL_CODE,'ORDER',NVL(L.UNIT_LIST_PRICE,0)*NVL(L.ORDERED_QUANTITY ,0)*PA.OPERAND *DECODE(PA.LIST_LINE_TYPE_CODE,'DIS',-1,1)/100,
(PA.ADJUSTED_AMOUNT* NVL(L.ORDERED_QUANTITY,0) ))DISCOUNT_AMT,
ROUND (DECODE( L.UNIT_LIST_PRICE, NULL, 999999999999999999 , 0, 999999999999999999,
		decode(PA.arithmetic_operator, '%', pa.operand,
                                  100*  (NVL(pa.adjusted_amount,0) /  l.unit_list_price) ) ) ,6) 	   DISCOUNT_PERCENT,
                      l.charge_periodicity_code, 
	ONT_OEXPRPRD_XMLP_PKG.cf_charge_periodicityformula(l.charge_periodicity_code) CF_CHARGE_PERIODICITY, 
--ONT_OEXPRPRD_XMLP_PKG.order_discountformula(:ORDER_LIST_AMT, :ORDER_SELL_AMT) ORDER_DISCOUNT, 
--	ONT_OEXPRPRD_XMLP_PKG.total_line_sell_priceformula(:Line_Sell_Price, DECODE ( L.LINE_CATEGORY_CODE , 'RETURN' , 0 - NVL ( L.ORDERED_QUANTITY , 0 ) , NVL ( L.ORDERED_QUANTITY , 0 ) )) Total_Line_Sell_Price, 
--	ONT_OEXPRPRD_XMLP_PKG.total_order_list_priceformula(:Line_List_Price, DECODE ( L.LINE_CATEGORY_CODE , 'RETURN' , 0 - NVL ( L.ORDERED_QUANTITY , 0 ) , NVL ( L.ORDERED_QUANTITY , 0 ) )) Total_Line_List_Price, 
	ONT_OEXPRPRD_XMLP_PKG.total_list_priceformula(NVL ( L.UNIT_LIST_PRICE , 0 ), DECODE ( L.LINE_CATEGORY_CODE , 'RETURN' , 0 - NVL ( L.ORDERED_QUANTITY , 0 ) , NVL ( L.ORDERED_QUANTITY , 0 ) )) TOTAL_LIST_PRICE, 
	ONT_OEXPRPRD_XMLP_PKG.total_sell_priceformula(NVL ( L.UNIT_SELLING_PRICE , 0 ), DECODE ( L.LINE_CATEGORY_CODE , 'RETURN' , 0 - NVL ( L.ORDERED_QUANTITY , 0 ) , NVL ( L.ORDERED_QUANTITY , 0 ) )) TOTAL_SELL_PRICE,
	ONT_OEXPRPRD_XMLP_PKG.RP_DUMMY_ITEM_p RP_DUMMY_ITEM, 
	ONT_OEXPRPRD_XMLP_PKG.discount_amountformula(DECODE ( PA.MODIFIER_LEVEL_CODE , 'ORDER' , NVL ( L.UNIT_LIST_PRICE , 0 ) * NVL ( L.ORDERED_QUANTITY , 0 ) * PA.OPERAND * DECODE ( PA.LIST_LINE_TYPE_CODE , 'DIS' , - 1 , 1 ) / 100 , ( PA.ADJUSTED_AMOUNT * NVL ( L.ORDERED_QUANTITY , 0 ) ) )) DISCOUNT_AMOUNT,
   	ONT_OEXPRPRD_XMLP_PKG.LIST_PRICE_DISPLAYFORMULA(NVL(L.UNIT_LIST_PRICE,0)) RP_LIST_PRICE,
	ONT_OEXPRPRD_XMLP_PKG.SELL_PRICE_DISPLAYFORMULA(NVL(L.UNIT_SELLING_PRICE,0)) RP_SELL_PRICE,
	ONT_OEXPRPRD_XMLP_PKG.Item_dspFormula(l.item_identifier_type,
                         L.INVENTORY_ITEM_ID,
			  L.ORDERED_ITEM_ID,
			  L.ORDERED_ITEM,
			  L.INVENTORY_ITEM_ID,
			  SI.ORGANIZATION_ID) Item_dsp 
FROM	OE_ORDER_LINES_ALL	L, 
	OE_ORDER_TYPES_V	OT, 
	OE_LINE_TYPES_V		LT, 
	HZ_CUST_SITE_USES_ALL   SU,
	HZ_CUST_ACCT_SITES_ALL	ACCT_SITE,
	HZ_LOCATIONS		LOC,
	HZ_PARTY_SITES		PARTY_SITE,
       MTL_SYSTEM_ITEMS_TL     T,
        MTL_SYSTEM_ITEMS_B_KFV  SI,
	RA_SALESREPS SR,  
  OE_SOLD_TO_ORGS_V	ORG,
	OE_PRICE_ADJUSTMENTS	PA,
	QP_LIST_HEADERS_VL      LH,
	OE_ORDER_HEADERS_ALL	H
WHERE	OT.ORDER_TYPE_ID (+)= H.ORDER_TYPE_ID
AND           LT.LINE_TYPE_ID (+) = L.LINE_TYPE_ID
AND 	H.SALESREP_ID = SR.SALESREP_ID(+)
AND	H.SHIP_TO_ORG_ID = SU.SITE_USE_ID(+)
AND     SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID 
AND	ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND	LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND	nvl(SI.ORGANIZATION_ID(+),0) = nvl(oe_sys_parameters.value('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id()),0)
AND     SI.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID 
AND     SI.ORGANIZATION_ID = T.ORGANIZATION_ID     
AND     T.LANGUAGE = userenv('LANG')               
AND	L.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID(+)
AND 	PA.LIST_HEADER_ID = LH.LIST_HEADER_ID
AND	(L.LINE_ID = PA.LINE_ID
	or pa.line_id is null)
AND	L.HEADER_ID = H.HEADER_ID(+)
AND	NVL(H.CANCELLED_FLAG, 'N') = 'N'
AND           NVL(L.CANCELLED_FLAG, 'N') ='N'
AND	ORG.ORGANIZATION_ID (+)= H.SOLD_TO_ORG_ID 
and	pa.header_id = h.header_id
AND     ( PA.LIST_LINE_TYPE_CODE = 'DIS'
                                OR  PA.LIST_LINE_TYPE_CODE = 'SUR'   
                                OR   PA.LIST_LINE_TYPE_CODE = 'PBH' ) 
AND           PA.APPLIED_FLAG='Y'
AND           NOT EXISTS 
                       (SELECT  'X' 
	      FROM OE_PRICE_ADJ_ASSOCS PAS, OE_PRICE_ADJUSTMENTS PA1
	      WHERE PAS.RLTD_PRICE_ADJ_ID = PA.PRICE_ADJUSTMENT_ID
	       AND  PA1.PRICE_ADJUSTMENT_ID=  PAS.PRICE_ADJUSTMENT_ID	
                         AND  PA1.LIST_LINE_TYPE_CODE ='PBH')
and nvl(h.org_id,0) = nvl(:p_organization_id,0)
and nvl(l.org_id,0) = nvl(:p_organization_id,0)
and nvl(sr.org_id,0) = nvl(:p_organization_id,0)  
&LP_OPEN_FLAG
&LP_SALESREP
&LP_ORDER_DATE
&LP_ORDER_TYPE
&LP_CUSTOMER_NAME
&LP_ORDER_NUM
and 1 = 1
&LP_CUSTOMER_NUM
&LP_ORDER_CATEGORY
&LP_ITEM
&LP_LINE_CATEGORY
ORDER BY 1 ASC,21 ASC,2 ASC,3 
  ASC,5 ASC,8 ASC,6 ASC,7 ASC,9 ASC,14 ASC,10 ASC,12 ASC,17 ASC,16 ASC,15 ASC,
  11 ASC , OT.NAME , SI.SEGMENT1 , LINE_SHIP_OPTION_NUM
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Mixed Precision
 
Number
Line Category
 
LOV Oracle
Order Category
 
LOV Oracle
Item Display
 
LOV Oracle
Open Orders Only
 
LOV Oracle
Order Number (To)
 
Number
Order Number (From)
 
Number
Line Type (To)
 
LOV Oracle
Line Type (From)
 
LOV Oracle
Order Type (To)
 
LOV Oracle
Order Type (From)
 
LOV Oracle
Order Date (To)
 
Date
Order Date (From)
 
Date
Salesperson (To)
 
LOV Oracle
Salesperson (From)
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Customer Number (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Sort By
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept