WSH Backorder Detail

Description
Categories: BI Publisher, Logistics
Application: Shipping Execution
Source: Backorder Detail Report (XML)
Short Name: WSHRDBDR_XML
DB package: WSH_WSHRDBDR_XMLP_PKG
select    wdd.currency_code   currency_code,
                wdd.inventory_item_id line_item,
                wdd.date_requested,
                nvl(wdd.source_line_set_id,wdd.source_line_id) source_line_set_id,
                &LP_ORDER_BY sort_by,
                wdd.source_header_number order_number,
                wdd.source_header_id source_header_id,
                substrb(party.party_name,1,50) customer_name,
                cust_acct.account_number customer_number,
                wdd.source_line_id,
                wdd.src_requested_quantity ordered_qty,
                wdd.src_requested_quantity_uom order_UOM,
                wdd.source_line_number line_number,
                wdd.top_model_line_id option_number,
                &rp_item_flex_all_seg  item_code,
                wdd.inventory_item_id,
                wdd.item_description,
                wdd.source_code,
                wdd.shipment_priority_code,
                wdd.requested_quantity_uom,
                sum(nvl(wdd.requested_quantity,0)) backordered_quantity,
                wdd.date_scheduled,
                wdd.organization_id,
                ood.organization_name,
            WSH_WSHRDBDR_XMLP_PKG.cf_curr_enabledformula(:CS_COUNT_SORT_ENABLED, :CS_SUM_SORT_ENABLED) CF_CURR_ENABLED,
                WSH_WSHRDBDR_XMLP_PKG.cf_sort_enabledformula(:CS_COUNT_DUOM, :CS_SUM_DUOM) CF_SORT_ENABLED,
                WSH_WSHRDBDR_XMLP_PKG.cf_distinct_uomformula(wdd.source_header_number) CF_DISTINCT_UOM,
                WSH_WSHRDBDR_XMLP_PKG.cf_source_codeformula(wdd.source_code) CF_source_code,
           /* WSH_WSHRDBDR_XMLP_PKG.cf_line_ordered_amtformula(WSH_WSHRDBDR_XMLP_PKG.cf_line_ordered_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id )), wdd.unit_price) CF_LINE_ORDERED_AMT,*/
		    WSH_WSHRDBDR_XMLP_PKG.cf_line_ordered_amtformula(WSH_WSHRDBDR_XMLP_PKG.cf_line_ordered_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id )),WSH_WSHRDBDR_XMLP_PKG.cf_selling_priceformula(wdd.source_line_id)) CF_LINE_ORDERED_AMT,
                WSH_WSHRDBDR_XMLP_PKG.cf_line_ordered_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id )) CF_LINE_ORDERED_QTY,
                WSH_WSHRDBDR_XMLP_PKG.cf_line_shipped_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id ), wdd.source_header_id, wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, wdd.inventory_item_id) CF_LINE_SHIPPED_QTY,
                WSH_WSHRDBDR_XMLP_PKG.cf_line_item_displayformula(wdd.inventory_item_id, wdd.organization_id, wdd.item_description) CF_LINE_ITEM_DISPLAY,
                WSH_WSHRDBDR_XMLP_PKG.cf_line_bo_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id ), wdd.source_header_id, wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, wdd.inventory_item_id) CF_LINE_BO_QTY,
               /* WSH_WSHRDBDR_XMLP_PKG.cf_line_bo_amtformula( WSH_WSHRDBDR_XMLP_PKG.cf_line_bo_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id ), wdd.source_header_id, wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, wdd.inventory_item_id), wdd.unit_price) CF_LINE_BO_AMT,*/
			   WSH_WSHRDBDR_XMLP_PKG.cf_line_bo_amtformula( WSH_WSHRDBDR_XMLP_PKG.cf_line_bo_qtyformula(nvl ( wdd.source_line_set_id , wdd.source_line_id ), wdd.source_header_id, wdd.requested_quantity_uom, wdd.src_requested_quantity_uom, wdd.inventory_item_id),WSH_WSHRDBDR_XMLP_PKG.cf_selling_priceformula(wdd.source_line_id)) CF_LINE_BO_AMT,
                WSH_WSHRDBDR_XMLP_PKG.cf_days_lateformula(WSH_WSHRDBDR_XMLP_PKG.cf_promise_dateformula(wdd.source_line_id)) CF_days_late,
                WSH_WSHRDBDR_XMLP_PKG.cf_promise_dateformula(wdd.source_line_id) CF_promise_date,
                WSH_WSHRDBDR_XMLP_PKG.cf_item_displayformula(wdd.inventory_item_id, wdd.organization_id, wdd.item_description) CF_ITEM_DISPLAY,
				WSH_WSHRDBDR_XMLP_PKG.cf_selling_priceformula(wdd.source_line_id) CF_selling_price
            from    org_organization_definitions ood,
                hz_parties party,
                hz_cust_accounts cust_acct,
                &lp_item_from
                &lp_country_from
                wsh_delivery_details wdd,
                mtl_system_items_vl si
            where   wdd.inventory_item_id = si.inventory_item_id (+)
            and wdd.organization_id = si.organization_id
            and wdd.released_status = 'B'
			and  wdd.replenishment_status is null -- bug#6689448 ( replenishment project)
            and cust_acct.party_id = party.party_id
            and wdd.customer_id = cust_acct.cust_account_id
            and wdd.organization_id = ood.organization_id
            and wdd.source_code = :P_SOURCE_SYSTEM
            &lp_item_id
            &lp_item_where
     --       &lp_inventory
            &lp_header_number
            &lp_order_type
            &lp_customer
            &lp_warehouse
            &lp_created_by
            &lp_country_where
      --      &lp_source_code
            group by
                wdd.currency_code,
                wdd.inventory_item_id,
                wdd.date_requested,
                substrb(party.party_name,1,50),
                ood.organization_name,
                wdd.source_header_number,
                wdd.source_header_id,
                cust_acct.account_number,
                wdd.source_line_id,
                wdd.src_requested_quantity,
                wdd.src_requested_quantity_uom,
                wdd.source_line_number,
                wdd.top_model_line_id,
                wdd.inventory_item_id,
                wdd.item_description,
                wdd.source_code,
                wdd.shipment_priority_code,
                wdd.requested_quantity_uom,
                wdd.date_scheduled,
                wdd.organization_id,
                wdd.source_line_set_id,
                &rp_item_flex_all_seg
            ORDER BY 1 ASC,5 ASC,23 ASC,24 ASC,9 ASC,8 ASC,6 ASC,18 ASC,2 ASC,12 ASC,4 ASC , wdd.currency_code , &LP_ORDER_BY
Parameter Name SQL text Validation
Item Flex Code
 
Sort By
 
LOV Oracle
Ship To Country
 
LOV Oracle
Created By
 
LOV Oracle
Item Display
 
LOV Oracle
Source Header Number To
 
LOV Oracle
Source Header Number From
 
LOV Oracle
Source Header Type
 
LOV Oracle
Customer
 
LOV Oracle
Item Category
 
LOV Oracle
Item Category Set
 
LOV Oracle
Item
 
Warehouse
 
LOV Oracle
Source System
 
LOV Oracle