ONT Credit Order Detail

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Credit Order Detail Report (XML)
Short Name: OEXOEORD_XML
DB package: ONT_OEXOEORD_XMLP_PKG
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
Ask a question
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Use Ledger Currency
 
LOV Oracle
Credit Order Date (To)
 
Date
Credit Order Date (From)
 
Date
Credit Order Number (To)
 
Number
Credit Order Number (From)
 
Number
Line Category
 
LOV Oracle
Credit Order Line Type
 
LOV Oracle
Credit Order Type
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Customer Number (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Salesperson
 
LOV Oracle
Item Display
 
LOV Oracle
Item
 
LOV Oracle
Open Credit Orders Only
 
LOV Oracle
Sort By
 
LOV Oracle