Quando você achar que sua vida anda difícil...

Lembre-se da query abaixo. Sim, é uma única query e está em produção, mas eu não consigo imaginar que mente doentia pode criar algo assim.
Se achou pouco, eu ainda editei a query e tirei fora a maior parte dos comentários (o que incluía alguns statements também).

SELECT DISTINCT 'A' USE_TEMPLATE,
  wpsv.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    ='Printed Pick Slip'
  AND fad.pk1_value    = wdd.source_line_id
  ) PICKING_INSTRUCTION,
  (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
  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_HEADERS'
  AND fd.category_id   = fdc.category_id(+)
  AND fd.datatype_name = 'Short Text'
  AND fdc.user_name    ='Printed Pick Slip'
  AND fad.pk1_value    = ooha.header_id
  ) HEADER_PICK_INSTR ,
  ooha.attribute20 ir_delivery_note,
  (SELECT fl.meaning shipping_method
  FROM fnd_lookup_values fl
  WHERE ooha.shipping_method_code = fl.lookup_code(+)
  AND fl.language                 = userenv('LANG')
  AND fl.lookup_type              = 'SHIP_METHOD'
  AND fl.enabled_flag             = 'Y'
  AND SYSDATE BETWEEN fl.start_date_active AND NVL (fl.end_date_active, SYSDATE + 1 )
  ) freight_code ,
  DECODE(msik.reservable_type,2,wdd.subinventory,wpsv.subinventory_code) from_subinventory,
  DECODE(msik.reservable_type,2,wdd.locator_id,wpsv.from_locator_id) from_locator_id ,
  wpsv.to_subinventory ,
  wpsv.to_locator_id ,
  NVL(wpsv.transaction_id ,-99) transaction_id ,
  NVL(wdd.transaction_id,  -99) rev_txn_id ,
  wpsv.move_order_line_id ,
  wpsv.detailing_date ,
  'PICKED' line_status ,
  NVL(wdd.picked_quantity,wdd.requested_quantity) primary_qty ,
  mtrh.request_number mo_number ,
  (SELECT USER_NAME
  FROM fnd_user
  WHERE user_id = ooha.created_by
  ) entered_by ,
  wpsv.line_id mo_line_id ,
  wpsv.line_number mo_line_number ,
  wdd.delivery_detail_id delivery_detail_id1 ,
  wdd.delivery_detail_id ser_dd_id ,
  wdd.source_header_number ,
  TO_CHAR(wdd.source_header_id) order_n_header_char,
  TO_CHAR(wdd.source_line_id) order_n_line_char1 ,
  wdd.source_header_id ,
  wdd.source_line_id ,
  TO_CHAR(wdd.source_header_id) source_header_chr ,
  TO_CHAR(wdd.source_line_id) source_line_chr ,
  wdd.shipping_instructions ,
  wdd.ship_tolerance_above ,
  wdd.ship_tolerance_below ,
  wdd.inventory_item_id ,
  wdd.inventory_item_id WDD_INV_ITEM ,
  wdd.organization_id WDD_ORGANIZATION ,
  wdd.requested_quantity_uom ,
  NVL(
  (SELECT listagg(xiab.trade_attribute13, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id = wdd.organization_id
  AND wdd.inventory_item_id  = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN     = 'Y'
  ) ,
  (SELECT listagg(xiab.trade_attribute13, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id =
    (SELECT organization_id FROM foobar_2 WHERE organization_code ='000'
    )
  AND wdd.inventory_item_id = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN    = 'Y'
  ) ) CF_ECCN,
  NVL(
  (SELECT listagg(xiab.trade_attribute14, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id = wdd.organization_id
  AND wdd.inventory_item_id  = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN     = 'Y'
  ) ,
  (SELECT listagg(xiab.trade_attribute14, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id =
    (SELECT organization_id FROM foobar_2 WHERE organization_code ='000'
    )
  AND wdd.inventory_item_id = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN    = 'Y'
  ) ) HTS,
  NVL(
  (SELECT listagg(xiab.trade_attribute18, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id = wdd.organization_id
  AND wdd.inventory_item_id  = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN     = 'Y'
  ) ,
  (SELECT listagg(xiab.trade_attribute18, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id =
    (SELECT organization_id FROM foobar_2 WHERE organization_code ='000'
    )
  AND wdd.inventory_item_id = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN    = 'Y'
  ) ) CF_CTY_ORIGIN,
  wnd.delivery_id ,
  TO_CHAR(wnd.delivery_id) delivery_chr ,
  wnd.name delivery_name ,
  wnd.initial_pickup_location_id ,
  oola.ordered_quantity line_ordered_quantity ,
  f_currency.symbol currency_symbol ,
  oola.unit_selling_price ,
  (oola.unit_selling_price * oola.ordered_quantity) line_extended_total,
  oola.line_number sales_line_number ,
  (SELECT DECODE(ship_loc.country,'US',xiab.attribute1, 'CA',xiab.attribute2, 'MX',xiab.attribute11, 'TT',xiab.attribute12, xiab.attribute1)
  FROM foobar_1 xiab,
    mtl_parameters mp
  WHERE xiab.inventory_item_id = oola.inventory_item_id
  AND xiab.organization_id     = mp.master_organization_id
  AND mp.organization_id       = oola.ship_from_org_id
  ) us_sds_num,
  TO_CHAR(wdd.inventory_item_id) item_info ,
  DECODE(:P_ITEM_DISPLAY,'F',NULL,msitl.description) item_desc ,
  msik.revision_qty_control_code ,
  DECODE(:P_ITEM_DISPLAY,'D',NULL,msik.segment1) segment1 ,
  DECODE(wdd.released_status,'B', '(Backorder line)',NULL) BACKORDER,
  os.set_name ,
  wpgr.customer_flag CUSTOMER_FLAG1 ,
  wpgr.order_number_flag ,
  wpgr.subinventory_flag ,
  wpgr.customer_flag ,
  wpgr.ship_to_flag ,
  wpgr.carrier_flag ,
  wpgr.shipment_priority_flag ,
  wpgr.trip_stop_flag ,
  wpgr.delivery_flag ,
  wpgr.name ,
  wdd.ship_method_code carrier ,
  wdd.shipment_priority_code priority ,
  wdd.organization_id ,
  wdd.requested_quantity2 secondary_qty_requested ,
  wdd.requested_quantity_uom2 secondary_qty_requested_uom ,
  wdd.shipped_quantity2 secondary_qty_shipped ,
  wdd.requested_quantity_uom2 secondary_qty_shipped_uom ,
  wdd.preferred_grade grade ,
  wdd.freight_terms_code freight_terms ,
  wdd.date_requested requested_ship_date ,
  wdd.attribute1 wdd_attribute1 ,
  wdd.attribute2 wdd_attribute2 ,
  wdd.attribute3 wdd_attribute3 ,
  wdd.attribute4 wdd_attribute4 ,
  wdd.attribute5 wdd_attribute5 ,
  wdd.attribute6 wdd_attribute6 ,
  wdd.attribute7 wdd_attribute7 ,
  wdd.attribute8 wdd_attribute8 ,
  wdd.attribute9 wdd_attribute9 ,
  wdd.attribute10 wdd_attribute10 ,
  wdd.attribute11 wdd_attribute11 ,
  wdd.attribute12 wdd_attribute12 ,
  wdd.attribute13 wdd_attribute13 ,
  wdd.attribute14 wdd_attribute14 ,
  wdd.attribute15 wdd_attribute15 ,
  wnd.attribute1 wnd_attribute1 ,
  wnd.attribute2 wnd_attribute2 ,
  wnd.attribute3 wnd_attribute3 ,
  wnd.attribute4 wnd_attribute4 ,
  wnd.attribute5 wnd_attribute5 ,
  wnd.attribute6 wnd_attribute6 ,
  wnd.attribute7 wnd_attribute7 ,
  wnd.attribute8 wnd_attribute8 ,
  wnd.attribute9 wnd_attribute9 ,
  wnd.attribute10 wnd_attribute10 ,
  wnd.attribute11 wnd_attribute11 ,
  wnd.attribute12 wnd_attribute12 ,
  wnd.attribute13 wnd_attribute13 ,
  wnd.attribute14 wnd_attribute14 ,
  wnd.attribute15 wnd_attribute15 ,
  msik.concatenated_segments item_name ,
  wdd.source_code source_code ,
  ooha.cust_po_number cust_po_number ,
  NVL( bill_acct_site.translated_customer_name,bill_party.party_name) bill_customer,
  bill_su.location b_location ,
  bill_loc.address1 b_address1 ,
  bill_loc.address2 b_address2 ,
  bill_loc.address3 b_address3 ,
  bill_loc.city
  || DECODE( bill_loc.city,NULL,NULL, DECODE(bill_loc.state,NULL,NULL,', '
  || bill_loc.state))
  || DECODE(bill_loc.province,NULL,NULL,', '
  || bill_loc.province)
  || ' '
  || bill_loc.postal_code
  || ', '
  || bill_loc.country b_city_st_zip ,
  bill_loc.country b_country ,
  terr1.territory_short_name b_country_name ,
  NVL( ship_acct_site.translated_customer_name,ship_party.party_name) ship_customer,
  DECODE(ooha.deliver_to_org_id,NULL,ship_party.party_name,NULL) S_PARTY_NAME,
  ship_su.location s_location ,
  ship_loc.address1 s_address1 ,
  ship_loc.address2 s_address2 ,
  ship_loc.city
  || DECODE( ship_loc.city,NULL,NULL, DECODE(ship_loc.state,NULL,NULL,', '
  || ship_loc.state))
  || DECODE(ship_loc.province,NULL,NULL,', '
  || ship_loc.province)
  || ' '
  || ship_loc.postal_code
  || ', '
  || ship_loc.country s_city_st_zip,
  foobar_3.cf_warehouseformula(wdd.organization_id) CF_warehouse,
  (SELECT fl.meaning
  FROM fnd_lookup_values fl
  WHERE ooha.shipment_priority_code = fl.lookup_code(+)
  AND fl.language                   = userenv('LANG')
  AND fl.lookup_type                = 'SHIPMENT_PRIORITY'
  AND fl.enabled_flag               = 'Y'
  AND SYSDATE BETWEEN fl.start_date_active AND NVL (fl.end_date_active, SYSDATE + 1 )
  ) CF_temp ,
  foobar_3.cf_requisition_numberformula(wdd.source_header_id) CF_requisition_number ,
  foobar_3.cf_ship_to_addressformula(wpgr.ship_to_flag, wpsv.pick_slip_number) CF_ship_to_address ,
  foobar_3.cf_carrierformula(wpgr.carrier_flag, wdd.ship_method_code) CF_carrier ,
  foobar_3.cf_shipment_priorityformula(wpgr.shipment_priority_flag, wdd.shipment_priority_code) CF_shipment_priority ,
  foobar_3.cf_subinventoryformula(wpgr.subinventory_flag, DECODE ( msik.reservable_type , 2 , wdd.subinventory , wpsv.subinventory_code )) CF_subinventory,
  foobar_3.cf_delivery_idformula(wpgr.delivery_flag, wnd.delivery_id) CF_delivery_id ,
  wnd.delivery_id CF_delivery ,
  DECODE(ooha.ATTRIBUTE7,'Y','DUPLICATE',NULL) CF_DUPLICATE ,
  foobar_3.INSERT_GT_TBL(ooha.header_id,ooha.org_id,ooha.ATTRIBUTE7) CF_INSERT_GT_TBL ,
  foobar_3.cf_trip_stop_address1formula(wpgr.trip_stop_flag, wpgr.delivery_flag, foobar_3.cf_delivery_idformula(wpgr.delivery_flag, wnd.delivery_id), wpsv.pick_slip_number) CF_trip_stop_address,
  foobar_3.cf_order_numberformula(wpgr.order_number_flag, wdd.source_header_number) CF_order_number ,
  lk1.meaning so_freight_terms ,
  DECODE(ooha.deliver_to_org_id,NULL, NVL(ooha.packing_instructions, ship_party_site.addressee
  ||
  (SELECT '  Phone '
    || (DECODE(phone_country_code,NULL,NULL,'+'
    || phone_country_code
    || ' ')
    || DECODE(phone_area_code,NULL,NULL,'('
    || phone_area_code
    || ')')
    || phone_number)
  FROM hz_contact_points hcp
  WHERE owner_table_name = 'HZ_PARTY_SITES'
  AND primary_flag       = 'Y'
  AND owner_table_id     = ship_party_site.party_site_id
  AND contact_point_type = 'PHONE'
  AND PHONE_LINE_TYPE    = 'GEN'
  AND status             = 'A'
  ) ) , ooha.packing_instructions) packing_instructions,
  /*   (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_idFt
FROM
  (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_acct
WHERE msik.inventory_item_id     = msitl.inventory_item_id
AND msik.organization_id         = msitl.organization_id
AND msitl.LANGUAGE               = userenv('LANG')
AND wpsv.header_id               = mtrh.header_id
AND 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_id
AND wda.delivery_id                                 = wnd.delivery_id(+)
AND ( wnd.delivery_type                            IS NULL
OR wnd.delivery_type                                = 'STANDARD' )
AND wdd.source_line_id                              = oola.line_id
AND wdd.source_header_id                            = oola.header_id
AND 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_id
AND 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_id
AND bill_cust_acct.party_id                         = bill_party.party_id
AND 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_id
AND lk1.lookup_code(+)                              = ooha.freight_terms_code
AND lk1.lookup_type(+)                              = 'FREIGHT_TERMS'
AND wdd.released_status                             = :p_line_status
AND 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                  = 1
AND ooha.order_number  = to_number(:p_order_num_low)
AND ooha.ORDER_TYPE_ID = to_number(:p_order_type_id)
AND 1                  =1
AND 1                  =1
UNION
SELECT 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    ='Printed Pick Slip'
  AND fad.pk1_value    = wdd.source_line_id
  ) PICKING_INSTRUCTION,
  (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
  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_HEADERS'
  AND fd.category_id   = fdc.category_id(+)
  AND fd.datatype_name = 'Short Text'
  AND fdc.user_name    ='Printed Pick Slip'
  AND fad.pk1_value    = ooha.header_id
  ) HEADER_PICK_INSTR ,
  ooha.attribute20 ir_delivery_note,
  (SELECT fl.meaning shipping_method
  FROM fnd_lookup_values fl
  WHERE ooha.shipping_method_code = fl.lookup_code(+)
  AND fl.language                 = userenv('LANG')
  AND fl.lookup_type              = 'SHIP_METHOD'
  AND fl.enabled_flag             = 'Y'
  AND SYSDATE BETWEEN fl.start_date_active AND NVL (fl.end_date_active, SYSDATE + 1 )
  ) freight_code ,
  NULL from_subinventory ,
  NULL from_locator_id ,
  NULL to_subinventory ,
  NULL to_locator_id ,
  NULL transaction_id ,
  NULL rev_txn_id ,
  NULL move_order_line_id ,
  NULL detailing_date ,
  'PICKED' line_status ,
  NVL(wdd.picked_quantity,wdd.requested_quantity) primary_qty,
  (SELECT mtrh.request_number
  FROM mtl_txn_request_headers mtrh,
    mtl_txn_request_lines mtrl
  WHERE mtrh.header_id = mtrl.header_id
  AND mtrl.line_id     = wdd.move_order_line_id
  ) mo_number,
  ( SELECT USER_NAME FROM fnd_user WHERE user_id = ooha.created_by
  ) entered_by ,
  NULL mo_line_id,
  (SELECT mtrl.line_number
  FROM mtl_txn_request_lines mtrl
  WHERE mtrl.line_id = wdd.move_order_line_id
  ) mo_line_number ,
  wdd.delivery_detail_id delivery_detail_id1 ,
  wdd.delivery_detail_id ser_dd_id ,
  wdd.source_header_number ,
  TO_CHAR(wdd.source_header_id) order_n_header_char,
  TO_CHAR(wdd.source_line_id) order_n_line_char1 ,
  wdd.source_header_id ,
  wdd.source_line_id ,
  TO_CHAR(wdd.source_header_id) source_header_chr ,
  TO_CHAR(wdd.source_line_id) source_line_chr ,
  wdd.shipping_instructions ,
  wdd.ship_tolerance_above ,
  wdd.ship_tolerance_below ,
  wdd.inventory_item_id ,
  wdd.inventory_item_id WDD_INV_ITEM ,
  wdd.organization_id WDD_ORGANIZATION ,
  wdd.requested_quantity_uom ,
  NVL(
  (SELECT listagg(xiab.trade_attribute13, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id = wdd.organization_id
  AND wdd.inventory_item_id  = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN     = 'Y'
  ) ,
  (SELECT listagg(xiab.trade_attribute13, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id =
    (SELECT organization_id FROM foobar_2 WHERE organization_code ='000'
    )
  AND wdd.inventory_item_id = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN    = 'Y'
  ) ) CF_ECCN,
  NVL(
  (SELECT listagg(xiab.trade_attribute14, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id = wdd.organization_id
  AND wdd.inventory_item_id  = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN     = 'Y'
  ) ,
  (SELECT listagg(xiab.trade_attribute14, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id =
    (SELECT organization_id FROM foobar_2 WHERE organization_code ='000'
    )
  AND wdd.inventory_item_id = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN    = 'Y'
  ) ) HTS,
  NVL(
  (SELECT listagg(xiab.trade_attribute18, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id = wdd.organization_id
  AND wdd.inventory_item_id  = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN     = 'Y'
  ) ,
  (SELECT listagg(xiab.trade_attribute18, ', ') WITHIN GROUP (
  ORDER BY wdd.inventory_item_id)
  FROM foobar_1 xiab
  WHERE xiab.organization_id =
    (SELECT organization_id FROM foobar_2 WHERE organization_code ='000'
    )
  AND wdd.inventory_item_id = xiab.inventory_item_id
  AND :P_ECCN_CTY_ORIGIN    = 'Y'
  ) ) CF_CTY_ORIGIN,
  wnd.delivery_id ,
  TO_CHAR(wnd.delivery_id) delivery_chr ,
  wnd.name delivery_name ,
  wnd.initial_pickup_location_id ,
  oola.ordered_quantity line_ordered_quantity ,
  f_currency.symbol currency_symbol ,
  oola.unit_selling_price ,
  (oola.unit_selling_price * oola.ordered_quantity) line_extended_total,
  oola.line_number sales_line_number ,
  (SELECT DECODE(ship_loc.country,'US',xiab.attribute1, 'CA',xiab.attribute2, 'MX',xiab.attribute11, 'TT',xiab.attribute12, xiab.attribute1)
  FROM foobar_1 xiab,
    mtl_parameters mp
  WHERE xiab.inventory_item_id = oola.inventory_item_id
  AND xiab.organization_id     = mp.master_organization_id
  AND mp.organization_id       = oola.ship_from_org_id
  ) us_sds_num,
  TO_CHAR(wdd.inventory_item_id) item_info ,
  DECODE(:P_ITEM_DISPLAY,'F',NULL,msitl.description) item_desc ,
  msik.revision_qty_control_code ,
  DECODE(:P_ITEM_DISPLAY,'D',NULL,msik.segment1) segment1 ,
  DECODE(wdd.released_status,'B', '(Backorder line)',NULL) BACKORDER,
  os.set_name ,
  NULL CUSTOMER_FLAG1 ,
  NULL order_number_flag ,
  NULL subinventory_flag ,
  NULL customer_flag ,
  NULL ship_to_flag ,
  NULL carrier_flag ,
  NULL shipment_priority_flag ,
  NULL trip_stop_flag ,
  NULL delivery_flag ,
  (SELECT wpgr.NAME
  FROM mtl_txn_request_headers mtrh,
    mtl_txn_request_lines mtrl ,
    wsh_pick_grouping_rules wpgr
  WHERE mtrh.header_id      = mtrl.header_id
  AND mtrl.line_id          = wdd.move_order_line_id
  AND mtrh.grouping_rule_id = wpgr.pick_grouping_rule_id(+)
  ) name ,
  wdd.ship_method_code carrier ,
  wdd.shipment_priority_code priority ,
  wdd.organization_id ,
  wdd.requested_quantity2 secondary_qty_requested ,
  wdd.requested_quantity_uom2 secondary_qty_requested_uom,
  wdd.shipped_quantity2 secondary_qty_shipped ,
  wdd.requested_quantity_uom2 secondary_qty_shipped_uom ,
  wdd.preferred_grade grade ,
  wdd.freight_terms_code freight_terms ,
  wdd.date_requested requested_ship_date ,
  wdd.attribute1 wdd_attribute1 ,
  wdd.attribute2 wdd_attribute2 ,
  wdd.attribute3 wdd_attribute3 ,
  wdd.attribute4 wdd_attribute4 ,
  wdd.attribute5 wdd_attribute5 ,
  wdd.attribute6 wdd_attribute6 ,
  wdd.attribute7 wdd_attribute7 ,
  wdd.attribute8 wdd_attribute8 ,
  wdd.attribute9 wdd_attribute9 ,
  wdd.attribute10 wdd_attribute10 ,
  wdd.attribute11 wdd_attribute11 ,
  wdd.attribute12 wdd_attribute12 ,
  wdd.attribute13 wdd_attribute13 ,
  wdd.attribute14 wdd_attribute14 ,
  wdd.attribute15 wdd_attribute15 ,
  wnd.attribute1 wnd_attribute1 ,
  wnd.attribute2 wnd_attribute2 ,
  wnd.attribute3 wnd_attribute3 ,
  wnd.attribute4 wnd_attribute4 ,
  wnd.attribute5 wnd_attribute5 ,
  wnd.attribute6 wnd_attribute6 ,
  wnd.attribute7 wnd_attribute7 ,
  wnd.attribute8 wnd_attribute8 ,
  wnd.attribute9 wnd_attribute9 ,
  wnd.attribute10 wnd_attribute10 ,
  wnd.attribute11 wnd_attribute11 ,
  wnd.attribute12 wnd_attribute12 ,
  wnd.attribute13 wnd_attribute13 ,
  wnd.attribute14 wnd_attribute14 ,
  wnd.attribute15 wnd_attribute15 ,
  msik.concatenated_segments item_name ,
  wdd.source_code source_code ,
  ooha.cust_po_number cust_po_number ,
  NVL( bill_acct_site.translated_customer_name,bill_party.party_name) bill_customer,
  bill_su.location b_location ,
  bill_loc.address1 b_address1 ,
  bill_loc.address2 b_address2 ,
  bill_loc.address3 b_address3 ,
  bill_loc.city
  || DECODE( bill_loc.city,NULL,NULL, DECODE(bill_loc.state,NULL,NULL,', '
  || bill_loc.state))
  || DECODE(bill_loc.province,NULL,NULL,', '
  || bill_loc.province)
  || ' '
  || bill_loc.postal_code
  || ', '
  || bill_loc.country b_city_st_zip ,
  bill_loc.country b_country ,
  terr1.territory_short_name b_country_name ,
  NVL( ship_acct_site.translated_customer_name,ship_party.party_name) ship_customer,
  DECODE(ooha.deliver_to_org_id,NULL,ship_party.party_name,NULL) S_PARTY_NAME,
  ship_su.location s_location ,
  ship_loc.address1 s_address1 ,
  ship_loc.address2 s_address2 ,
  ship_loc.city
  || DECODE( ship_loc.city,NULL,NULL, DECODE(ship_loc.state,NULL,NULL,', '
  || ship_loc.state))
  || DECODE(ship_loc.province,NULL,NULL,', '
  || ship_loc.province)
  || ' '
  || ship_loc.postal_code
  || ', '
  || ship_loc.country s_city_st_zip,
  foobar_3.cf_warehouseformula(wdd.organization_id) CF_warehouse,
  (SELECT fl.meaning
  FROM fnd_lookup_values fl
  WHERE ooha.shipment_priority_code = fl.lookup_code(+)
  AND fl.language                   = userenv('LANG')
  AND fl.lookup_type                = 'SHIPMENT_PRIORITY'
  AND fl.enabled_flag               = 'Y'
  AND SYSDATE BETWEEN fl.start_date_active AND NVL (fl.end_date_active, SYSDATE + 1 )
  ) CF_temp ,
  foobar_3.cf_requisition_numberformula(wdd.source_header_id) CF_requisition_number,
  NULL CF_ship_to_address ,
  NULL CF_carrier ,
  NULL CF_shipment_priority ,
  NULL CF_subinventory ,
  NULL CF_delivery_id ,
  wnd.delivery_id CF_delivery ,
  DECODE(ooha.ATTRIBUTE7,'Y','DUPLICATE',NULL) CF_DUPLICATE ,
  foobar_3.INSERT_GT_TBL(ooha.header_id,ooha.org_id,ooha.ATTRIBUTE7) CF_INSERT_GT_TBL,
  NULL CF_trip_stop_address ,
  ooha.order_number CF_order_number,
  lk1.meaning so_freight_terms ,
  DECODE(ooha.deliver_to_org_id,NULL, NVL(ooha.packing_instructions, ship_party_site.addressee
  ||
  (SELECT '  Phone '
    || (DECODE(phone_country_code,NULL,NULL,'+'
    || phone_country_code
    || ' ')
    || DECODE(phone_area_code,NULL,NULL,'('
    || phone_area_code
    || ')')
    || phone_number)
  FROM hz_contact_points hcp
  WHERE owner_table_name = 'HZ_PARTY_SITES'
  AND primary_flag       = 'Y'
  AND owner_table_id     = ship_party_site.party_site_id
  AND contact_point_type = 'PHONE'
  AND PHONE_LINE_TYPE    = 'GEN'
  AND status             = 'A'
  ) ) , ooha.packing_instructions) packing_instructions,
  /*   (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 ,
  NULL 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 ,
  NULL F_TO_LOCATION ,
  NULL 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,
  NULL 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 ,
  NULL F_FROM_LOCATION ,
  foobar_3.cf_freight_terms_nameformula(wdd.freight_terms_code, wdd.source_code) CF_FREIGHT_TERMS_NAME ,
  NULL CF_REVISION ,
  foobar_3.B_task_idFt(wdd.organization_id) B_task_idFt
FROM
  /*        (
  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 ,
  wsh_delivery_assignments_v wda,
  wsh_new_deliveries wnd ,
  oe_order_lines_all oola ,
  oe_lookups lk1 ,
  oe_sets os ,
  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_acct
WHERE msik.inventory_item_id = msitl.inventory_item_id
AND msik.organization_id     = msitl.organization_id
AND msitl.LANGUAGE           = userenv('LANG')
AND wdd.carrier_id           = wc.carrier_id(+)
AND wdd.organization_id      = :p_organization_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_id
AND wda.delivery_id                                 = wnd.delivery_id(+)
AND ( wnd.delivery_type                            IS NULL
OR wnd.delivery_type                                = 'STANDARD' )
AND wdd.source_line_id                              = oola.line_id
AND wdd.source_header_id                            = oola.header_id
AND wdd.source_code                                 = 'OE'
AND wdd.container_flag                             IN ('N', 'Y')
AND wdd.ship_set_id                                 = os.set_id(+)
AND ooha.transactional_curr_code                    = f_currency.currency_code(+)
AND ooha.header_id                                  = wdd.source_header_id
AND 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_id
AND bill_cust_acct.party_id                         = bill_party.party_id
AND bill_loc.country                                = terr1.territory_code(+)
AND terr1.language                                  = userenv('LANG')
AND NVL(ooha.deliver_to_org_id,ooha.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(+)                         = ship_party_site.location_id
AND ship_cust_acct.party_id                         = ship_party.party_id
AND lk1.lookup_code(+)                              = ooha.freight_terms_code
AND lk1.lookup_type(+)                              = 'FREIGHT_TERMS'
AND wdd.released_status                             = :p_line_status
AND :P_PICK_SLIP_NUM_LOW                           IS NULL
AND :P_PICK_SLIP_NUM_HIGH                          IS NULL
AND DECODE (:P_MOVE_ORDER_HIGH,NULL,'1',
  (SELECT TO_CHAR(mtrh.request_number)
  FROM mtl_txn_request_headers mtrh,
    mtl_txn_request_lines mtrl
  WHERE mtrh.header_id = mtrl.header_id
  AND mtrl.line_id     = wdd.move_order_line_id
  ) )                  = NVL(TO_CHAR(:P_MOVE_ORDER_HIGH),'1')
AND 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 ooha.order_number  = to_number(:p_order_num_low)
AND ooha.ORDER_TYPE_ID = to_number(:p_order_type_id)
AND 1                  =1
AND 1                  =1
AND NOT EXISTS
  (SELECT 1
  FROM mtl_material_transactions mmt,
    mtl_txn_request_lines mtrl
  WHERE mmt.pick_slip_number IS NOT NULL
  AND mmt.move_order_line_id  = mtrl.line_id
  AND WDD.MOVE_ORDER_LINE_ID  = mtrl.line_id
  )
ORDER BY PICK_SLIP_NUMBER,
  CF_ORDER_NUMBER ,
  SSD ,
  SALES_LINE_NUMBER;