if batch_status is completed then it will print the completed.lot_number else it will print the wp.lot_number and in the rtf we have give the condition as <if:lot_number!=''>then the below query works
SELECT gbs.batchstep_no operation_no
, got.oprn_desc operation_desc
, gbsa.activity activity
, gat.activity_desc activity_desc
, gbsr.resources resources
, crmt.resource_desc resources_desc
, completed.lot_number
, wp.lot_number
FROM cr_rsrc_mst_tl crmt
, gme_batch_step_resources gbsr
, gmd_activities_tl gat
, gme_batch_step_activities gbsa
, gmd_operations_tl got
, gme_batch_steps gbs
,(select mtln.lot_number,mtln.TRANSACTI
from GME_MATERIAL_DETAILS gmd,
MTL_TRANSACTION_LOT_NUMBERS mtln,
gme_batch_header gbh
where line_type = -1
and mtln.inventory_item_id = gmd.inventory_item_id
and mtln.organization_id = gmd.organization_id
and mtln.TRANSACTION_SOURCE_TYPE_
and mtln.TRANSACTION_SOURCE_ID = gmd.batch_id
and mtln.organization_id = gmd.organization_id
and mtln.TRANSACTION_SOURCE_ID = gbh.batch_id
and gbh.BATCH_STATUS = 3) completed,
(select gppl.lot_number,gmd.batch_id
from GME_MATERIAL_DETAILS gmd,
GME_PENDING_PRODUCT_LOTS gppl,
gme_batch_header gbh
Where gmd.batch_id = gppl.batch_id
and gmd.MATERIAL_DETAIL_ID = gppl.MATERIAL_DETAIL_ID
and gmd.batch_id = gbh.batch_id
and gmd.organization_id = gbh.organization_id
and gbh.BATCH_STATUS in (1,2)) wp
WHERE gbs.oprn_id = got.oprn_id
AND got.language = USERENV ('LANG')
AND gbs.batch_id = gbsa.batch_id
AND gbs.batchstep_id = gbsa.batchstep_id
AND gbsa.activity = gat.activity
AND gat.language = userenv ('LANG')
AND gbs.batch_id = gbsr.batch_id
AND gbs.batchstep_id = gbsr.batchstep_id
AND gbsa.batchstep_activity_id = gbsr.batchstep_activity_id
AND gbsr.resources = crmt.resources
AND crmt.language = USERENV ('LANG')
AND gbs.batch_id=completed.batch_
AND gbs.batch_id = wp.batch_id(+)
ORDER BY gbs.batchstep_no
, gbsa.activity
, gbsr.resources
if attribute8 is not null then take the attribute8 value and compare that value in the table hz_cust_site_uses_all.site_
wnd.delivery_type,
wbrv.BILL_OF_LADING_NUMBER,
wbrv.DOCUMENT_INSTANCE_ID,
wbrv.BOOKING_NUMBER,
wbrv.SERVICE_CONTRACT,
wbrv.CARRIER_EXPORT_REF,
wbrv.SHIPPER_EXPORT_REF,
wbrv.NOTIFY_PARTY,
wbrv.PROBLEM_CONTACT_REF,
wbrv.PORT_OF_LOADING,
wbrv.PORT_OF_DISCHARGE,
wbrv.AETC_NUMBER,
wbrv.SHIPPER_SIGNED_BY,
wbrv.SHIPPER_SIGNED_DATE,
wbrv.CARRIER_SIGNED_BY,
wbrv.CARRIER_SIGNED_DATE,
wbrv.POD_SIGNED_BY,
wbrv.POD_SIGNED_DATE,
wbrv.BOOKING_OFFICE,
wbrv.ISSUING_OFFICE,
wbrv.ISSUING_PERSON,
wbrv.DATE_ISSUED,
wbrv.POD_COMMENTS,
wbrv.SHIPPER_SIGNED_HM_BY,
wbrv.SHIPPER_SIGNED_HM_DATE,
wbrv.CARRIER_SIGNED_HM_BY,
wbrv.CARRIER_SIGNED_HM_DATE,
wbrv.carrier_tracking_number,
wbrv.ADDITIONAL_SHIPMENT_INFO,
wbrv.INCOTERMS,
wbrv.DOCK_CODE,
wbrv.EXPORT_CARRIER,
wbrv.TRAILER_NUMBER,
wbrv.DELIVERY_ID,
to_char(wbrv.DELIVERY_ID) delivery_id_chr,
wbrv.DELIVERY_NAME,
wbrv.PICK_UP_LOCATION_ID,
wbrv.PICK_UP_LOCATION_CODE,
wbrv.DROP_OFF_LOCATION_ID,
wbrv.DROP_OFF_LOCATION_CODE,
wbrv.CARRIER_ID ,
--CARRIER_NAME,
wbrv.ship_method,
substr(csh.ship_method_
wbrv.TRIP_ID,
wbrv.TRIP_NAME,
wbrv.DOCUMENT_TYPE,
wbrv.STATUS,
wbrv.FINAL_PRINT_DATE,
wbrv.ENTITY_NAME,
wbrv.ENTITY_ID,
wbrv.DOC_SEQUENCE_CATEGORY_ID,
wbrv.POD_FLAG,
wbrv.REASON_OF_TRANSPORT,
wbrv.COD_AMOUNT,
wbrv.COD_CURRENCY_CODE,
wbrv.COD_REMIT_TO,
wbrv.COD_CHARGE_PAID_BY,
wbrv.BILL_FREIGHT_TO,
wbrv.CARRIED_BY,
wbrv.DELIVERY_LEG_ID,
to_char(wbrv.DELIVERY_LEG_ID) delivery_leg_id_chr,
wbrv.SEQUENCE_NUMBER,
wbrv.LOADING_ORDER_FLAG,
wbrv.GROSS_WEIGHT,
wbrv.NET_WEIGHT,
wbrv.WEIGHT_UOM_CODE,
wbrv.VOLUME,
wbrv.VOLUME_UOM_CODE,
wbrv.load_tender_status,
wbrv.POOLED_LOCATION,
wbrv.SHIPPER_NAME,
wbrv.from_location,
wbrv.from_address_1,
wbrv.from_address_2,
wbrv.from_address_3,
wbrv.from_city,
wbrv.from_region,
wbrv.from_postal_code,
wbrv.from_country,
-- wbrv.from_tax_name,
wbrv.CARRIER_NAME,
wbrv.carrier_location,
wbrv.carrier_address_1,
wbrv.carrier_address_2,
wbrv.carrier_address_3,
wbrv.carrier_city,
wbrv.carrier_region,
wbrv.carrier_country,
wbrv.carrier_postal_code,
wbrv.CUSTOMER_NAME,
wbrv.to_location,
wbrv.to_address_1,
wbrv.to_address_2,
wbrv.to_address_3,
wbrv.to_city,
wbrv.to_region,
wbrv.to_postal_code,
wbrv.to_country,
wbrv. organization_id,
--xdo begin
wbrv.trip_seal_code,
wbrv.departure_seal_code,
wbrv.incoterms fob_code,
wbrv.bill_freight_to,
wnd.cod_amount,
wnd.freight_terms_code,
wc.scac_code scac_code,
wnd.shipping_marks, -- Special Instructions
wnd.description, --Disclaimers/Certifications/
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,
-- xdo end
-- Bill to location, address, city, state, country
bill_su.location,
bill_loc.address1,
bill_loc.address2,
decode (bill_loc.city,
NULL, NULL,
bill_loc.city || ', '
)
|| decode (bill_loc.state,
NULL, bill_loc.province || ', ',
bill_loc.state || ', '
)
|| decode (bill_loc.postal_code,
NULL, NULL,
bill_loc.postal_code || ', '
)
|| decode (bill_loc.country, NULL, NULL, bill_loc.country) address5,
h.attribute8, -- shipvia1
h.attribute9, -- shipvia1
h.attribute10, -- shipvia1
-- Ship Via location, address, city, state, country
att8.location att8_location,
ATT8.address1 att8_address1,
att8.address2 att8_address2,
att8.address5 att8_address5,
att9.location att9_location,
ATT9.address1 att9_address1,
att9.address2 att9_address2,
att9.address5 att9_address5,
att10.location att10_location,
ATT10.address1 att10_address1,
att10.address2 att10_address2,
att10.address5 att10_address5
FROM
wsh_bols_rd_v wbrv ,
wsh_carrier_services csh,
wsh_new_deliveries wnd,
wsh_carriers wc,
oe_order_headers_all h,
hz_cust_site_uses_all bill_su,
hz_party_sites bill_ps,
hz_locations bill_loc,
hz_cust_acct_sites_all bill_cas,(SELECT
shipvia_su.site_use_id,
shipvia_su.location,
shipvia_loc.address1,
shipvia_loc.address2,
DECODE (shipvia_loc.city,
NULL, NULL,
shipvia_loc.city || ', '
)
|| DECODE (shipvia_loc.state,
NULL, shipvia_loc.province || ', ',
shipvia_loc.state || ', '
)
|| DECODE (shipvia_loc.postal_code,
NULL, NULL,
shipvia_loc.
)
|| DECODE (shipvia_loc.country, NULL, NULL, shipvia_loc.country) address5
from
hz_cust_site_uses_all shipvia_su,
hz_party_sites shipvia_ps,
hz_locations shipvia_loc,
hz_cust_acct_sites_all shipvia_cas
where 1=1
AND shipvia_su.cust_acct_site_id = shipvia_cas.cust_acct_site_id
AND shipvia_loc.location_id = shipvia_ps.location_id
AND shipvia_cas.party_site_id = shipvia_ps.party_site_id) ATT8,
(SELECT
shipvia_su.site_use_id,
shipvia_su.location,
shipvia_loc.address1,
shipvia_loc.address2,
DECODE (shipvia_loc.city,
NULL, NULL,
shipvia_loc.city || ', '
)
|| DECODE (shipvia_loc.state,
NULL, shipvia_loc.province || ', ',
shipvia_loc.state || ', '
)
|| DECODE (shipvia_loc.postal_code,
NULL, NULL,
shipvia_loc.postal_code || ', '
)
|| DECODE (shipvia_loc.country, NULL, NULL, shipvia_loc.country) address5
from
hz_cust_site_uses_all shipvia_su,
hz_party_sites shipvia_ps,
hz_locations shipvia_loc,
hz_cust_acct_sites_all shipvia_cas
where 1=1
AND shipvia_su.cust_acct_site_id = shipvia_cas.cust_acct_site_id
AND shipvia_loc.location_id = shipvia_ps.location_id
AND shipvia_cas.party_site_id = shipvia_ps.party_site_id) ATT9,
(SELECT
shipvia_su.site_use_id,
shipvia_su.location,
shipvia_loc.address1,
shipvia_loc.address2,
DECODE (shipvia_loc.city,
NULL, NULL,
shipvia_loc.city || ', '
)
|| DECODE (shipvia_loc.state,
NULL, shipvia_loc.province || ', ',
shipvia_loc.state || ', '
)
|| DECODE (shipvia_loc.postal_code,
NULL, NULL,
shipvia_loc.postal_code || ', '
)
|| DECODE (shipvia_loc.country, NULL, NULL, shipvia_loc.country) address5
from
hz_cust_site_uses_all shipvia_su,
hz_party_sites shipvia_ps,
hz_locations shipvia_loc,
hz_cust_acct_sites_all shipvia_cas
where 1=1
AND shipvia_su.cust_acct_site_id = shipvia_cas.cust_acct_site_id
AND shipvia_loc.location_id = shipvia_ps.location_id
AND shipvia_cas.party_site_id = shipvia_ps.party_site_id) ATT10
WHERE
wnd.delivery_id = wbrv.delivery_id(+)
and nvl(wnd.shipment_direction, 'O') IN ('O', 'IO')
AND csh.ship_method_code(+) = wbrv.ship_method
--xdo begin
AND csh.carrier_id = wc.carrier_id
AND h.header_id = wnd.source_header_id
AND bill_loc.location_id(+) = bill_ps.location_id
AND h.invoice_to_org_id = bill_su.site_use_id(+)
AND bill_su.cust_acct_site_id = bill_cas.cust_acct_site_id(+)
AND bill_cas.party_site_id = bill_ps.party_site_id(+)
AND h.attribute8 = att8.site_use_id(+)
AND h.attribute9 = att9.site_use_id(+)
AND h.attribute10 = att10.site_use_id(+)
--xdo end
&LP_DELIVERY_LEG_ID
&LP_DATE_RANGE
&LP_DELIVERY_ID
&LP_FREIGHT_CODE
&LP_ORGANIZATION_ID
&LP_TRIP_ID
ORDER BY wbrv.BILL_OF_LADING_NUMBER
No comments:
Post a Comment