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 ONT Orders by Item ONT Sales Order Acknowledgement JA India - RG23C Part I Report - draft - draft JA India - RG 23 D - draft WSH Vehicle Load Sheet Summary
Related Topics
WSH Backorder Detail WSH Packing Slip Report PDF Output XXWSH Packing Workbench Details WSH Pick Slip WSH Pick Slip Report PDF Output WSH Packing Slip WSH Bill of Lading WSH Bill of Lading PDF Output WSH Commercial Invoice PDF Output WSH Commercial Invoice WSH Open Deliveries
Blitz Report In Action