Olhadela no mercado de trabalho

Hoje eu dei uma parada na correria do dia-a-dia e fui dar uma olhada nas oportunidades de emprego que o Linkedin achou interessante para mim. Confesso que estou ficando preocupado com os "requisitos" das  vagas. Uma em especial já está a algum tempo passeando por aí, sem ser fechada, e eu tenho uma vaga idea da razão por ainda não terem encontrado ninguém. O que também é estranho, já que a empresa é multinacional e muito conhecida. Mas vamos aos pre-requisitos da vaga "Líder técnico da América Latina":

1. Bachelor's degree in MIS, CIS or equivalent technical work experience is needed.
2. 5+ years' experience in support & troubleshooting Windows & Mac OS platforms (Mac OSX 10.7+, Windows 7 & 8) including mobile devices running Apple iOS & Android OS flavors.
3. 5+ years' of demonstrated technical depth and experience in supporting one or more IT infrastructure areas such as Networking, Firewalls, Load Balancers, Active Directory, LDAP, DB fundamentals, and backup / storage.
4. 5+ years’ experience in advanced user facing support position(s).
5. Manage issue resolution including prioritization on time sensitive problems and escalations with corporate.
6. Experience dealing with off shore resources including 3rd party support vendors.
7. Synthesize business and technical needs across location and drive solution delivery.
8. Strong business acumen and working knowledge of applications/business processes.
9. Dedicated, passionate and experienced in growing / scaling an IT organization, keeping pace with XXXX's explosive growth.
10. Capability to work proficiently and to take ownership with minimal daily guidance and bring mature seasoned skills when working with production systems.
11. Ability to travel within South America and the United States.
12. Proficiency in Spanish, English and Portuguese.

Agora meu comentários:

1. Normal, o mínimo esperado para uma vaga de líder técnico da América Latina.
2. Hmm? 5 anos de suporte a dois sistemas operacionais que não convivem bem entre si mais dispositivos móveis com seus respectivos sistemas operacionais? 
3. Como? Mais 5 anos de suporte de infra-estrutura? O suporte dado no item 2. foram do segundo emprego?
4. Confesso que não entendi muito bem essa solicitação... seria reuniões com usuários? Levantamento de requisitos? Ou um headset na cabeça para atender chamados de primeiro nível?
5. Experiência com gestão de incidentes e problemas... o que obviamente requer conhecimento nas áreas de negócio.
6. Bem, vamos adicionar também atividades de gestão de equipes (inclusive de terceiros) em outros continentes... afinal, pelo menos de acordo com o princípio Dilbert, ser gerente é fácil.
7. Hmm... agora também atividades de analise... e proposta de solução técnica.
8. Isso já era meio óbvio, afinal já pediram isso nos items 5 e 7.
9. O profissional deverá trabalhar longas horas diariamente, incluindo feriados e fins de semana, preferencialmente sem receber horas extras. E deve ser automotivado enquanto faz isso. Espero que a empresa não cresça numa taxa exponencial ou um ataque cardíaco/colapso nervoso parece fazer parte do futuro do profissional.
10. Bom, o profissional já faz todo a gama de atividades de TI em uma empresa, incluindo gestão, então me parece meio lógico ele conseguir ser virar sozinho. O que assusta é que se espera que este profissional seja um generalista mão-na-massa que faça tudo bem e rápido, como se espera de um... especialista?!?
11. Ah, essa é fácil... o profissional já não tem vida pessoal mesmo. Vai ser um daqueles tontos que não desliga o celular nem para tomar banho.
12. Essa é engraçada... norte-americanos continuam achando que espanhol e português são a mesma coisa, apesar de em 90% dos casos eles não saberem absolutamente nada das duas.

A conclusão que chego é que o profissional com perfil para esta vaga, se for do sexo masculino, deverá ter no mínimo, repito, no mínimo, 3 testículos no saco escrotal. Se for do sexo feminino, deverá ser algo parecido com a Xena ou Mulher-Maravilha. Afinal de contas, a pessoa desempenhará as funções de:

• analista de suporte
• analista funcional
• arquiteto
• gerente
• especialista nos sistemas de produção, mas como eles não dizem quais sistemas são, então deve ser um "especialista genérico".

Infelizmente não publicaram o salário. Mas eu suspeito que não seja a soma de todas as funções.

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;