ONT Order Discount Detail

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