Tuesday 13 March 2012

if batch_status is completed then it will print the completed

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.TRANSACTION_SOURCE_ID,gmd.batch_id
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_ID = 5
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_id(+)
     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_
use_id and print the address like location,address1,address2,state,country,city. select
   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_meaning, 1, 35)  ship_method_meaning,
   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/Emergency Information  
   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.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) 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