/* (SELECT MIN (request_date) FROM oe_order_lines_all l WHERE l.org_id = ooha.org_id AND l.header_id= ooha.header_id )so_request_date, */ TRUNC(oola.schedule_ship_date) SSD , foobar_3.cf_customer_nameformula(wpgr.customer_flag, wdd.source_header_id) CF_customer_name , foobar_3.CP_warehouse_code_p CP_warehouse_code , foobar_3.CP_warehouse_name_p CP_warehouse_name , foobar_3.st_addr1_p st_addr1 , foobar_3.ts_addr3_p ts_addr3 , foobar_3.ts_addr4_p ts_addr4 , foobar_3.ts_addr5_p ts_addr5 , foobar_3.ts_addr1_p ts_addr1 , foobar_3.ts_addr2_p ts_addr2 , foobar_3.st_addr4_p st_addr4 , foobar_3.st_addr5_p st_addr5 , foobar_3.st_addr2_p st_addr2 , foobar_3.st_addr3_p st_addr3 , foobar_3.f_to_locationformula(wpsv.to_locator_id, wdd.organization_id) F_TO_LOCATION , foobar_3.cf_customerformula(wpgr.customer_flag, wdd.source_header_id) CF_customer , foobar_3.cf_trip_idformula(wnd.delivery_id, wnd.initial_pickup_location_id) CF_trip_id , foobar_3.f_item_descriptionformula(msitl.description, wdd.inventory_item_id, wdd.organization_id, msitl.description) F_ITEM_DESCRIPTION , foobar_3.f_requested_quantityformula(wdd.source_header_id, wdd.source_line_id, wpsv.move_order_line_id) F_REQUESTED_QUANTITY , foobar_3.f_shipped_quantityformula() F_SHIPPED_QUANTITY , foobar_3.cf_trip_chrformula(foobar_3.cf_trip_idformula(wnd.delivery_id, wnd.initial_pickup_location_id)) CF_TRIP_CHR , foobar_3.CP_trip_name_p CP_trip_name , foobar_3.f_from_locationformula(DECODE ( msik.reservable_type , 2 , wdd.locator_id , wpsv.from_locator_id ), wdd.organization_id) F_FROM_LOCATION, foobar_3.cf_freight_terms_nameformula(wdd.freight_terms_code, wdd.source_code) CF_FREIGHT_TERMS_NAME , foobar_3.cf_revisionformula(NVL ( wpsv.transaction_id , - 99 )) CF_REVISION , foobar_3.B_task_idFt(wdd.organization_id) B_task_idFtFROM (SELECT mmt.subinventory_code subinventory_code , mmt.locator_id from_locator_id , mmt.transfer_subinventory to_subinventory, mmt.transfer_locator_id to_locator_id , ABS(mmt.primary_quantity) primary_qty , mmt.pick_slip_number , mmt.transaction_date detailing_date , mmt.move_order_line_id , 'PICKED' line_status , mmt.transaction_id , mmt.transaction_uom , mmt.secondary_transaction_quantity , mmt.secondary_uom_code , mtrl.header_id , mtrl.line_id , mtrl.line_number FROM mtl_material_transactions mmt, mtl_txn_request_lines mtrl WHERE mmt.pick_slip_number IS NOT NULL AND NVL(mmt.transaction_quantity,0) < 0 AND mmt.move_order_line_id = mtrl.line_id AND mtrl.organization_id = :p_organization_id AND 1 =1 AND 1 =1 ) wpsv , wsh_delivery_details wdd , wsh_carriers wc , mtl_txn_request_headers mtrh , wsh_delivery_assignments_v wda, wsh_new_deliveries wnd , oe_order_lines_all oola , oe_lookups lk1 , oe_sets os , wsh_pick_grouping_rules wpgr , mtl_system_items_b_kfv msik , mtl_system_items_tl msitl , oe_order_headers_all ooha , fnd_currencies f_currency , hz_cust_site_uses_all bill_su , hz_cust_acct_sites_all bill_acct_site, hz_party_sites bill_party_site , hz_locations bill_loc , hz_parties bill_party , hz_cust_accounts bill_cust_acct , fnd_territories_tl terr1 , hz_cust_site_uses_all ship_su , hz_cust_acct_sites_all ship_acct_site, hz_party_sites ship_party_site , hz_locations ship_loc , hz_parties ship_party , hz_cust_accounts ship_cust_acctWHERE msik.inventory_item_id = msitl.inventory_item_idAND msik.organization_id = msitl.organization_idAND msitl.LANGUAGE = userenv('LANG')AND wpsv.header_id = mtrh.header_idAND wpsv.move_order_line_id = wdd.move_order_line_id(+)AND wdd.carrier_id = wc.carrier_id(+)AND NVL(wpsv.transaction_id,-99) = DECODE(NVL(wdd.transaction_id ,-99),-99,NVL(wpsv.transaction_id,-99),wdd.transaction_id)AND wdd.inventory_item_id = msik.inventory_item_id(+)AND wdd.organization_id = msik.organization_id(+)AND NVL(TRUNC(oola.SCHEDULE_SHIP_DATE),TRUNC(SYSDATE)) BETWEEN NVL(:P_SCHEDULE_SHIP_DATE_LOW,NVL(TRUNC(oola.SCHEDULE_SHIP_DATE),TRUNC(SYSDATE))) AND NVL(:P_SCHEDULE_SHIP_DATE_HIGH,NVL(TRUNC(oola.SCHEDULE_SHIP_DATE),TRUNC(SYSDATE)))AND wdd.delivery_detail_id = wda.delivery_detail_idAND wda.delivery_id = wnd.delivery_id(+)AND ( wnd.delivery_type IS NULLOR wnd.delivery_type = 'STANDARD' )AND wdd.source_line_id = oola.line_idAND wdd.source_header_id = oola.header_idAND wdd.source_code = 'OE'AND wdd.container_flag IN ('N', 'Y')AND wdd.ship_set_id = os.set_id(+)AND mtrh.grouping_rule_id = wpgr.pick_grouping_rule_id(+)AND ooha.transactional_curr_code = f_currency.currency_code(+)AND ooha.header_id = wdd.source_header_idAND ooha.invoice_to_org_id = bill_su.site_use_id(+)AND bill_su.cust_acct_site_id = bill_acct_site.cust_acct_site_id(+)AND bill_acct_site.party_site_id = bill_party_site.party_site_id(+)AND bill_acct_site.cust_account_id = bill_cust_acct.cust_account_id(+)AND bill_loc.location_id(+) = bill_party_site.location_idAND bill_cust_acct.party_id = bill_party.party_idAND bill_loc.country = terr1.territory_code(+)AND terr1.language = userenv('LANG')AND NVL(oola.deliver_to_org_id,oola.ship_to_org_id) = ship_su.site_use_id(+)AND ship_su.cust_acct_site_id = ship_acct_site.cust_acct_site_id(+)AND ship_acct_site.party_site_id = ship_party_site.party_site_id(+)AND ship_acct_site.cust_account_id = ship_cust_acct.cust_account_id(+)AND ship_loc.location_id(+) = NVL(wdd.deliver_to_location_id,wdd.ship_to_location_id)AND ship_cust_acct.party_id = ship_party.party_idAND lk1.lookup_code(+) = ooha.freight_terms_codeAND lk1.lookup_type(+) = 'FREIGHT_TERMS'AND wdd.released_status = :p_line_statusAND EXISTS ( ( SELECT 1 FROM dual WHERE 1 =1 AND 'All' = :p_print )UNION (SELECT 1 FROM DUAL WHERE 1 = 1 AND 'Original' = :p_print AND NVL(OOHA.ATTRIBUTE7,'N') != 'Y' )UNION (SELECT 1 FROM DUAL WHERE 1 = 1 AND 'Duplicate' = :p_print AND NVL(OOHA.ATTRIBUTE7,'N') = 'Y' ) )AND 1 = 1AND ooha.order_number = to_number(:p_order_num_low)AND ooha.ORDER_TYPE_ID = to_number(:p_order_type_id)AND 1 =1AND 1 =1UNIONSELECT DISTINCT 'B' USE_TEMPLATE , 100000000000000000000 pick_slip_number, (SELECT listagg(fdst.short_text, ', ') WITHIN GROUP ( ORDER BY fad.seq_num) FROM fnd_attached_documents fad , fnd_documents_vl fd , FND_DOCUMENTS_SHORT_TEXT fdst, fnd_document_categories_vl fdc--, Defect 4322 WHERE fad.document_id = fd.document_id AND fdst.media_id = fd.media_id AND SYSDATE BETWEEN NVL(fd.start_date_active, SYSDATE - 1) AND NVL(fd.end_date_active, SYSDATE + 1) AND fad.entity_name = 'OE_ORDER_LINES' AND fd.category_id = fdc.category_id(+) AND fd.datatype_name = 'Short Text' AND fdc.user_name