0 Replies Latest reply on Jul 3, 2019 12:24 PM by MarcLaf

    Conditional Printing

    MarcLaf

      Hi,

       

      I have a report that prints items and each of the items as a field called OVERPACK USED...

       

      I want the OVERPACK USED field to be printed ONLY ONE TIME per it's content... example: IF I have 3 records with the OVERPACK USED containing the same data '3', well I want the 3 records to be printed and the OVERPACK USED 3 printed after the 3 records:

       

      UN2739, Butyric anhydride, 8, III // //856

      UN2739, Butyric anhydride, 8, III // //856

      UN2739, Butyric anhydride, 8, III // //856

      OVERPACK USED 1

      UN1220, Isopropyl acetate, 3, II // //364

      UN1220, Isopropyl acetate, 3, II // //364

      OVERPACK USED 2

       

      Currently, OVERPACK USED is printed after each record...

       

       

      My report as 2 groups and one query:

       

       

      The Query:

      WITH

                        csg_info AS (

                          SELECT csg.csg_id,

                                 (SELECT code

                                    FROM org

                                   WHERE org_id = csg.org_id_orig) csg_ota_code,

                                 csg.date_raised csg_date_raised,

                                 csg.packed_by csg_packed_by,

                                 csg.seq_num csg_seq_num,

                                 (SELECT    line_1

                                         || CHR (10)

                                         || line_2

                                         || CHR (10)

                                         || line_3

                                         || CHR (10)

                                         || line_4

                                         || CHR (10)

                                         || line_5

                                         || CHR (10)

                                         || line_6

                                    FROM adr

                                   WHERE adr_id = (SELECT adr_id

                                                     FROM oxa

                                                    WHERE oxa_id = csg.oxa_id_dest))

                                                    dest_addr,

                                 (SELECT    line_1

                                         || CHR (10)

                                         || line_2

                                         || CHR (10)

                                         || line_3

                                         || CHR (10)

                                         || line_4

                                         || CHR (10)

                                         || line_5

                                         || CHR (10)

                                         || line_6

                                    FROM adr

                                   WHERE adr_id = (SELECT adr_id

                                                     FROM oxa

                                                    WHERE oxa_id = csg.oxa_id_orig))

                                                    orig_addr,

                                 csg.add_handling_dginfo add_hand_instr,

                                 ph.pcs_id,

                                 ph.pid,

                                 (select overpack_information

                                  from pcs

                                  where pcs.pcs_id = ph.pcs_id) overpack_information,

                                 pdg.ivt_authorization,

                                 NVL (pdg.neq, 0) neq,

                                 pdg.packing_type,

                                 (SELECT un_number

                                    FROM un

                                   WHERE un_id = dgp.un_id) un_number,

                                 pdg.proper_ship_name,

                                 -- Ticket 857489 Tech Name Added Back

                                 --CASE

                                   --WHEN pdg.dga_id IS NULL THEN

                                     pdg.technical_name

                                   --ELSE

                                     --NULL

                                 --END

                                 technical_name,    

                                 CASE

                                   WHEN TRIM(class_div) IS NOT NULL

                                     THEN ', ' || class_div

                                    ELSE ''

                                 END ||

                                 CASE

                                   WHEN TRIM(risk_1) IS NOT NULL

                                     THEN ', (' || dgp.risk_1 || ')'

                                   ELSE ''

                                 END ||                         

                                 CASE

                                   WHEN TRIM(risk_2) IS NOT NULL

                                     THEN ', (' || dgp.risk_2 || ')'

                                   ELSE ''

                                 END ||

                                 CASE

                                   WHEN TRIM(risk_3) IS NOT NULL

                                     THEN ', (' || dgp.risk_3 || ')'

                                   ELSE ''

                                 END  cdg1,                        

                                 DECODE(TRIM(pdg.pack_group),

                                        NULL,dgp.pack_group,              

                                        pdg.pack_group) pack_group,

                                 DECODE(TRIM(pdg.pax_pack_instr),

                                        NULL,dgp.pax_pack_instr,              

                                        pdg.pax_pack_instr) pax_pack_instr,

                                 DECODE(TRIM(pdg.cargo_pack_instr),

                                        NULL,dgp.cargo_pack_instr,              

                                        pdg.cargo_pack_instr) cargo_pack_instr,

                                 dgp.class_div

                            FROM csg,

                                (SELECT pcs.csg_id, pcs.pcs_id, pcs.pid

                                   FROM pcs

                                  WHERE pcs_id IN (SELECT pxs.pcs_id

                                                     FROM pxs

                                                    WHERE pxs.shp_id = :p_id)) ph,

                                 pdg, dgp, nmds.regulation_types rt

                           WHERE csg.csg_id = ph.csg_id

                             AND ph.pcs_id  = pdg.pcs_id

                             AND pdg.dgp_id = dgp.dgp_id

                             AND dgp.rgt_id = rt.rgt_id

                             AND rt.rgt_code = 'AIR'),

                        shp_info AS (

                          SELECT shp.shp_id shp_id_shp,

                                 org.code ta_code,

                                 shp.year_num,

                                 shp.yearly_seq_num,

                                 CASE

                                   WHEN shp.next_dg_flag IN ('P', 'C')

                                     THEN ''

                                   ELSE shp.ccbl_number

                                 END ccbl_number,

                                 shp.opened_date,

                                 shp.ta_signature shipper,

                                 shp.next_dg_flag,

                                 shp.dg_flag,

                                 shp.dg_day_phone,

                                 shp.dg_erp_flag,

                                 shp.dg_night_phone dg_night_phone,

                                 org.NAME site_name,

                                 (SELECT NAME

                                    FROM prt

                                   WHERE prt_id = shp.prt_id_dep) apt_from,

                                 (SELECT NAME

                                    FROM prt

                                   WHERE prt_id = shp.prt_id_arr) apt_to

                            FROM shp, org

                           WHERE shp.org_id_orig = org.org_id

                             AND shp.shp_id = :p_id)

                        SELECT shp_info.shp_id_shp,

                               csg_info.pid,

                               shp_info.ta_code,

                               shp_info.year_num,

                               shp_info.yearly_seq_num,

                               shp_info.ccbl_number,

                               shp_info.opened_date,

                               shp_info.site_name,

                               shp_info.shipper,

                               shp_info.apt_from,

                               shp_info.apt_to,

                               csg_info.csg_ota_code,

                               csg_info.csg_date_raised,

                               csg_info.csg_packed_by,

                               csg_info.csg_seq_num,

                               csg_info.dest_addr,

                               csg_info.orig_addr,

                               csg_info.add_hand_instr,

                               DECODE (SUBSTR (csg_info.class_div, 1, 1),

                                     '7', '  XXXXXXXXXXXXXXX',

                                     '                    XXXXXXXXXXX') CLASS,

                               CASE

                                  WHEN INSTR(shp_info.dg_flag||

                                             shp_info.next_dg_flag, 'P') > 0 THEN

                                      '             XXXXXXXX               XXXXXXXX'||

                                      '               XXXXXXXX'

                                  ELSE

                                      'XXXXXXXXX              XXXXXXXXX'||

                                      '             XXXXXXXXX'

                               END DG,                                                 

                               DECODE (:p_lang,

                               'E', '24 HRS EMERGENCY PHONE CONTACT: '

                                 || shp_info.dg_night_phone || '.',

                                 'NUMERO D''URGENCE 24 HRS: '

                                 || shp_info.dg_night_phone|| '.') emerg_phone,

                               csg_info.neq,

                               csg_info.un_number || ', ' ||

                                 csg_info.proper_ship_name ||

                                 -- Ticket 1221440 Tech Name Added Back only if it is not null,                            --otherwise don't show empty brackets

                                 CASE

                                   WHEN TRIM(csg_info.technical_name) IS NOT NULL                             THEN

                                     '('||csg_info.technical_name||')'

                                   ELSE ''

                                 END

                                 || 

                                 csg_info.cdg1 ||

                                 CASE

                                   WHEN TRIM(csg_info.pack_group) IS NOT NULL THEN

                                     ', '||csg_info.pack_group

                                   ELSE ''

                                 END || ' //' || 

                                 CASE

                                   WHEN TRIM(csg_info.packing_type) IS NOT NULL THEN

                                     ' '||csg_info.packing_type

                                   ELSE

                                     ''

                                 END || ' //'||

                                 CASE

                                   WHEN INSTR(shp_info.dg_flag||

                                              shp_info.next_dg_flag, 'P') > 0 THEN

                                     csg_info.pax_pack_instr

                                   ELSE

                                     csg_info.cargo_pack_instr

                                 END ||    

                                 CASE

                                   WHEN TRIM(csg_info.ivt_authorization) IS NOT NULL

                                     THEN ', ' || csg_info.ivt_authorization

                                   ELSE ''

                                 END cdg,

                                csg_info.overpack_information,

                                shp_info.dg_erp_flag

                          FROM csg_info, shp_info

       

      And the report layout:

       

       

      Is there a way to Only print the OVERPACK USED data when the content of the field changes ?

       

      Help would be appreciated!

       

      Thanks in advance,

       

      Marc L