ONT Credit Order Detail

SELECT 
	 DECODE(:P_ORDER_BY, 'CUSTOMER', party.party_name) "CUST1",
	 DECODE(:P_ORDER_BY, 'CUSTOMER', H.ORDER_NUMBER) "RMA_NUM1",
	 DECODE(:P_ORDER_BY, 'RETURN_TYPE', otype.name) "RMA_TYPE2",
	 DECODE(:P_ORDER_BY, 'RETURN_TYPE', party.party_name) "CUST2",
	 DECODE(:P_ORDER_BY, 'RETURN_TYPE', H.ORDER_NUMBER) "RMA_NUM2",
	 DECODE(:P_ORDER_BY, 'RETURN_NUMBER', H.ORDER_NUMBER) "RMA_NUM3",
	 otype.name RETURN_TYPE,
                    ltype.name RETURN_LINE_TYPE,
	 H.ORDER_NUMBER RETURN_NUM,
	 party.party_NAME CUSTOMER,
	 cust.account_number  CUSTOMER_NUMBER,
	 to_char(H.ORDERED_DATE,'DD-MON-YY') RETURN_DATE,
	 L.LINE_NUMBER  LINE_NUM,
	L.LINE_NUMBER || DECODE( L.SHIPMENT_NUMBER, NULL, NULL,'.'||L.SHIPMENT_NUMBER) ||
	   DECODE(L.OPTION_NUMBER,NULL,NULL,'.' ||L.OPTION_NUMBER) LINE_SHIP_OPTION_NUM,
                  &rp_item_flex_all_seg item_flex,
	l.item_identifier_type,
                   l.inventory_item_id,
                   l.ordered_item_id,
                   l.ordered_item,
	LK.MEANING OPEN,
	 LU.MEANING REF_TYPE,
	 L.RETURN_CONTEXT REF_TYPE_CODE,
	 L.RETURN_ATTRIBUTE1   REF_ID,
	 to_char(L.REQUEST_DATE,'DD-MON-YY')  EXPECTED_DATE,
	 NVL(L.UNIT_SELLING_PRICE, 0) SELLING_PRICE,
	 H.TRANSACTIONAL_CURR_CODE CURRENCY_CODE,
	 NVL(L.ORDERED_QUANTITY, 0)  QTY_ORDERED,
	 NVL(L.ORDERED_QUANTITY, 0) - NVL(L.CANCELLED_QUANTITY, 0) QTY_EXPECTED,
	DECODE(L.LINE_CATEGORY_CODE, 'RETURN', 
		NVL(L.SHIPPED_QUANTITY, 0) ,0) QTY_RECEIVED,
	 DECODE(L.BOOKED_FLAG,'Y' ,NVL(L.FULFILLED_QUANTITY, 0),0) QTY_ACCEPTED,
	 NVL(L.ORDERED_QUANTITY,0) QTY_AUTHORIZED,
	  nvl(l.shipped_quantity,0)       qty_shipped,
	 TRX.TRX_NUMBER CREDIT_TO_INVOICE,
	 L.LINE_ID LINE_ID,
	 ONT_OEXOEORD_XMLP_PKG.AMT_EXPECTEDFORMULA(NVL(L.ORDERED_QUANTITY,0), NVL(L.UNIT_SELLING_PRICE, 0),H.TRANSACTIONAL_CURR_CODE) AMT_EXPECTED,
     ONT_OEXOEORD_XMLP_PKG.AMT_RECEIVEDFORMULA(DECODE(L.LINE_CATEGORY_CODE, 'RETURN', 
												NVL(L.SHIPPED_QUANTITY, 0) ,0), NVL(L.UNIT_SELLING_PRICE, 0),H.TRANSACTIONAL_CURR_CODE) AMT_RECEIVED,
	 ONT_OEXOEORD_XMLP_PKG.AMT_ACCEPTEDFORMULA(DECODE(L.BOOKED_FLAG,'Y' ,NVL(L.FULFILLED_QUANTITY, 0),0), NVL(L.UNIT_SELLING_PRICE, 0),H.TRANSACTIONAL_CURR_CODE) AMT_ACCEPTED,
   ONT_OEXOEORD_XMLP_PKG.Item_dspFormula (l.item_identifier_type , l.inventory_item_id, l.ordered_item_id,l.ordered_item,SI.ORGANIZATION_ID,SI.INVENTORY_ITEM_ID) Item_dsp
 FROM	OE_ORDER_LINES_ALL	L,
     	 OE_ORDER_HEADERS	H,             
                    oe_transaction_types_tl 	otype,            
                    oe_transaction_types_tl 	ltype,
	 OE_LOOKUPS		LU,
	OE_LOOKUPS		LK,
                   JTF_RS_SALESREPS            SR,
MTL_SYSTEM_ITEMS_VL	SI,
                   hz_cust_accounts  cust,
                   hz_parties party,
	 RA_CUSTOMER_TRX_ALL	TRX,
	 RA_CUSTOMER_TRX_LINES_ALL	TRXL
 WHERE	h.header_id = l.header_id
AND            L.ITEM_TYPE_CODE(+)!='INCLUDED'
AND	H.BOOKED_FLAG='Y' 
AND 	L.BOOKED_FLAG='Y'  
AND	LU.LOOKUP_TYPE(+) = 'REFERENCE_TYPE'
AND	LU.LOOKUP_CODE(+) = L.RETURN_CONTEXT
AND            LK.LOOKUP_TYPE='YES_NO'
AND            LK.LOOKUP_CODE=NVL(H.OPEN_FLAG ,'N')
and 	l.line_type_id = ltype.transaction_type_id(+)
and 	h.order_type_id = otype.transaction_type_id(+)
and            ltype.language(+) = :p_lang
and            otype.language(+) = :p_lang
AND	H.SALESREP_ID = SR.SALESREP_ID(+)
AND   L.INVENTORY_ITEM_ID = SI.INVENTORY_ITEM_ID
AND   SI.ORGANIZATION_ID   = :c_master_org
and    h.sold_to_org_id = cust.cust_account_id
and    party.party_id = cust.party_id
AND  L.CREDIT_INVOICE_LINE_ID = TRXL.CUSTOMER_TRX_LINE_ID(+)
AND  TRXL.CUSTOMER_TRX_ID = TRX.CUSTOMER_TRX_ID(+)
and nvl(h.org_id,0) = :p_organization_id
and nvl(sr.org_id,0) = :p_organization_id
&LP_RETURN_TYPE
&LP_RETURN_LINE_TYPE
&LP_LINE_CATEGORY
&LP_SALESREP
&LP_CUSTOMER_NAME
&LP_CUSTOMER_NUM
and &LP_ITEM
&LP_RETURN_NUM
&LP_RETURN_DATE
&LP_OPEN_RETURNS_ONLY
ORDER BY 1 ASC,2 ASC,
  3 ASC,4 ASC,5 ASC,6 ASC,9 ASC,10 ASC,11 ASC,7 ASC,20 ASC,35 ASC,L.LINE_NUMBER