WSH Backorder Detail

➜ Back to Library
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
Download
 
Related Blitz Reports
ONT Credit Order Detail
ONT Credit Order Summary
ONT Unbooked Orders Detail
WSH Shipped Delivery Lines
JA India - RG23C Part I Report - draft - draft
JA India - RG 23 D - draft
ONT Orders by Item
ONT Sales Order Acknowledgement
CSD Depot Repair Traveler