ONT Credit Order Summary

Description
Categories: BI Publisher, Sales
Application: Order Management
Source: Credit Order Summary Report (XML)
Short Name: OEXOEORS_XML
DB package: ONT_OEXOEORS_XMLP_PKG
SELECT
     	h.transactional_curr_code					Currency2,
     	h.conversion_rate 						Conversion_Rate,
     	h.conversion_type_code 						Conversion_Type_Code,
     	decode (substr(upper(:p_order_by),1,1),'W',wh.name,NULL) 	Warehouse_1,
     	decode (substr(upper(:p_order_by),1,1),'R',OTYPE.name,NULL) 	Return_Type_1,
                  decode (substr(upper(:p_order_by),1,1),'C',CUST_ACCT.CUST_ACCOUNT_ID ,NULL) 	Customer_id,
	  decode (substr(upper(:p_order_by),1,1),'C',PARTY.PARTY_NAME,NULL) 	Customer_Name_1,
     	wh.name 							Warehouse_2,
     	h.order_number 							Return_Number,
     	lu.meaning 							Open,
     	OTYPE.name 							Return_Type_2,
     	h.ordered_date 							Return_Date,
PARTY.PARTY_NAME customer_name_2,
CUST_ACCT.ACCOUNT_NUMBER Customer_number,
     	l.line_id 							Line_Id,
     	l.line_number 							Line_Number,
     	l.line_number || decode(l.shipment_number, null,null,'.'||l.shipment_number) ||
            decode(l.option_number,null,null,'.'||l.option_number) 	line_shipment_option_number,
     	l.item_identifier_type,
	L.INVENTORY_ITEM_ID,
	L.ORDERED_ITEM_ID,
	L.ORDERED_ITEM,
	&RP_ITEM_FLEX_ALL_SEG ITEM_FLEX,
      to_char(l.request_date,'DD-MON-YY') 							Expected_Receipt_Date,
      nvl(l.ordered_quantity,0)   					qty_authorized,
      decode(l.line_category_code, 'RETURN',
		nvl(l.shipped_quantity,0),0) 				qty_shipped,
      nvl(l.ordered_quantity,0)  * nvl(l.unit_selling_price,0) 		authorized_amount,
      decode(l.line_category_code, 'ORDER',0,
		trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id))
		- trunc(l.request_date)) 				Receipt_Days,
      decode(l.line_category_code,'ORDER',0,
		trunc(om_reports_common_pkg.oexoeors_get_workflow_date(l.line_id))
		- trunc(h.ordered_date)) 				Return_Days,
      lu2.meaning 							Line_Open,
      LTYPE.name 								Line_Type,
      l.item_revision 							item_revision,
	ONT_OEXOEORS_XMLP_PKG.c_data_not_foundformula(h.transactional_curr_code) C_DATA_NOT_FOUND,
	ONT_OEXOEORS_XMLP_PKG.c_order_countformula() C_ORDER_COUNT,
   ONT_OEXOEORS_XMLP_PKG.Item_dspFormula (l.item_identifier_type , l.inventory_item_id, l.ordered_item_id,l.ordered_item,SI.ORGANIZATION_ID, l.inventory_item_id) Item_dsp,
	ONT_OEXOEORS_XMLP_PKG.rp_dummy_item_p rp_dummy_item,
	ONT_OEXOEORS_XMLP_PKG.c_actual_receipt_days(nvl ( l.ordered_quantity , 0 ), decode ( l.line_category_code , 'ORDER' , 0 , trunc ( om_reports_common_pkg.oexoeors_get_workflow_date ( l.line_id ) ) - trunc ( l.request_date ) )) C_ACTUAL_RECEIPT_DAYS,
	ONT_OEXOEORS_XMLP_PKG.c_actual_return_days(nvl ( l.ordered_quantity , 0 ), decode ( l.line_category_code , 'ORDER' , 0 , trunc ( om_reports_common_pkg.oexoeors_get_workflow_date ( l.line_id ) ) - trunc ( h.ordered_date ) )) C_ACTUAL_RETURN_DAYS,
	ONT_OEXOEORS_XMLP_PKG.c_authorized_amount_p(h.transactional_curr_code,nvl(l.ordered_quantity,0)  * nvl(l.unit_selling_price,0),h.conversion_type_code,h.ordered_date,h.conversion_rate) c_authorized_amount
FROM
	oe_order_headers_all h,
     	oe_order_lines_all l,
      	mtl_system_items_vl si,
     	oe_lookups lu,
     	oe_lookups lu2,
	HZ_PARTIES PARTY,
	HZ_CUST_ACCOUNTS CUST_ACCT,
        	oe_ship_from_orgs_v wh,
	OE_TRANSACTION_TYPES_TL OTYPE,
	OE_TRANSACTION_TYPES_TL LTYPE
WHERE
	    l.header_id = h.header_id
	AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
  	AND l.item_type_code(+) != 'INCLUDED'
  	AND h.booked_flag = 'Y'
  	AND l.booked_flag = 'Y'
  	AND nvl(h.cancelled_flag,'N') = 'N'
  	AND l.inventory_item_id = si.inventory_item_id
	AND nvl(si.organization_id,0) = :c_master_org
	AND H.ORDER_TYPE_ID = OTYPE.TRANSACTION_TYPE_ID
	AND L.LINE_TYPE_ID = LTYPE.TRANSACTION_TYPE_ID
	AND h.sold_to_org_id = CUST_ACCT.CUST_ACCOUNT_ID(+)
  	AND l.ship_from_org_id = wh.organization_id(+)
  	AND lu.lookup_type='YES_NO'
  	AND lu.lookup_code = nvl(h.open_flag,'N')
  	AND lu2.lookup_type = 'YES_NO'
  	AND lu2.lookup_code = nvl(l.open_flag,'N')
  	AND nvl(h.org_id,0) = nvl(:P_ORGANIZATION_ID_V,0)
  	AND nvl(l.org_id,0) =  nvl(:P_ORGANIZATION_ID_V,0)
	AND OTYPE.LANGUAGE = userenv('LANG')
	AND LTYPE.LANGUAGE = userenv('LANG')
&lp_customer_name
&lp_customer_number
&lp_return_type
&lp_return_line_type
&lp_line_category
&lp_warehouse
&lp_return_num
&lp_return_date
&lp_exp_rec_date
&lp_open_returns_only
&lp_return_days
&lp_rec_days
ORDER BY 1 ASC,6 ASC,7 ASC,4 ASC,5 ASC,13 ASC,9 ASC,16 ASC,15 ASC,31 ASC,17 ASC,19 ASC,20 ASC,21 ASC,18 ASC , h.transactional_curr_code , PARTY.PARTY_NAME , h.order_number , l.line_number
Parameter Name SQL text Validation
Item Flex Code
 
Ledger Id
 
Number
Receipt Days (To)
 
Number
Receipt Days (From)
 
Number
Expected Receipt Date (To)
 
Date
Expected Receipt Date (From)
 
Date
Credit Order Days (To)
 
Number
Credit Order Days (From)
 
Number
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
Warehouse
 
LOV Oracle
Customer Number (To)
 
LOV Oracle
Customer Number (From)
 
LOV Oracle
Customer Name (To)
 
LOV Oracle
Customer Name (From)
 
LOV Oracle
Use Ledger Currency
 
LOV Oracle
Item Display
 
LOV Oracle
Open Credit Orders Only
 
LOV Oracle
Sort By
 
LOV Oracle