WSH Vehicle Load Sheet Details

SELECT
              distinct    wnd.delivery_id drop_off_delivery_id ,
              null drop_off_item_number    ,
              msi.description drop_off_item_description    ,
              wdd.load_seq_number drop_off_item_load_seq    ,
              wnd.gross_weight drop_off_del_gr_wt    ,
              wnd.net_weight drop_off_del_net_wt    ,
              wnd.weight_uom_code drop_off_del_weight_uom    ,
              wnd.name drop_off_delivery_name    ,
              wnd.waybill drop_off_delivery_waybill    ,
              wnd.loading_sequence drop_off_del_loading_seq    ,
              substrb ( cust.party_name,  1,  50 ) drop_off_customer_name    ,
              wl.meaning drop_off_del_status    ,
              wdd.customer_prod_seq  drop_off_cust_prod_seq   ,
              wdd.source_code drop_source_code    ,
              wdd.source_line_id drop_source_line_id     ,
              wdd.item_description det_drop_item_description,
              wdd.inventory_item_id drop_inventory_item_id,
                WSH_WSHRDVLD_XMLP_PKG.cf_drop_item_descformula(wdd.inventory_item_id, msi.description, wdd.item_description) CF_Drop_Item_Desc,
                WSH_WSHRDVLD_XMLP_PKG.cf_drop_off_cust_jobformula(wdd.source_code, wdd.source_line_id) CF_drop_off_cust_job,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('drop_off_item_num_disp', 'INV', 'MSTK', 101, MSI.ORGANIZATION_ID, MSI.INVENTORY_ITEM_ID, 'ALL', 'Y', 'VALUE') DROP_OFF_ITEM_NUM_DISP
            FROM
              wsh_delivery_details wdd    ,
              wsh_delivery_assignments_v wda    ,
              mtl_system_items_vl msi ,
              wsh_new_deliveries wnd    ,
              (SELECT party.party_name,
                      cust_acct.cust_account_id,
                      'C' consignee_flag
               FROM   hz_parties party,
                      hz_cust_accounts cust_acct
               WHERE  cust_acct.party_id = party.party_id
               UNION
               SELECT party.party_name,
                      pv.vendor_id cust_account_id,
                      'V' consignee_flag
               FROM   hz_parties party,
                      po_vendors pv
               WHERE  pv.party_id = party.party_id) cust,
              wsh_lookups wl
            WHERE
              wdd.delivery_detail_id = wda.delivery_detail_id    AND
              msi.inventory_item_id (+) = wdd.inventory_item_id     AND
              wdd.organization_id = msi.organization_id    AND
              wda.delivery_id = wnd.delivery_id    AND
              wda.delivery_id is not null     AND
              cust.cust_account_id (+) = wnd.customer_id    AND
              cust.consignee_flag (+) = nvl(wnd.consignee_flag,'C') AND              
              wl.lookup_type = 'DELIVERY_STATUS'    AND
              wnd.status_code = wl.lookup_code    AND
              wnd.delivery_type = 'STANDARD' AND
             wdd.container_flag IN ('N', 'Y')
             and wnd.delivery_id = :DROPOFFS_DELIVERY_ID
            ORDER BY DROP_OFF_DEL_GR_WT, DROP_OFF_DEL_LOADING_SEQ, drop_off_delivery_id, drop_off_delivery_waybill,
			drop_off_delivery_name, DROP_OFF_DEL_NET_WT, drop_off_del_weight_uom, DROP_OFF_DEL_STATUS,
			DROP_OFF_CUSTOMER_NAME, DET_DROP_ITEM_DESCRIPTION, drop_inventory_item_id,
            drop_off_del_loading_seq, drop_off_item_load_seq