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;
Duvido alguém dar manutenção nela inteira e continuar funcionando! :-D
ResponderExcluir