'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,