Issue: When a Ship Confirm is done after Orders are Booked and Pick Release is done…..( Ship Confirm Document set is kicked ) . Blank pages are printed from the printer if the report does not give any data.

Report is an XML Publisher report. (Reports 10g + Template)  (CUSTOMIZED PACKING SLIP REPORT)

Solution needed by the Client:  “I do not want Blank pages to spit out if the report has no data “

1^st Doubt:  Why is the report printing Blank pages?

Answer:     In our report we do not want the report to print if the Shipping method is UPS , FEDEX .. we want the CUSTOMIZED PACKING    SLIP  Report to be printed if the Shipping method is  “COMPANYS OWN TRUCK”

2^nd Doubt:  How are you controlling this (Shipping method)?

Answer   :   we have a LOOKUP for this and the values in this LOOKUP are compared with “wsh_carriers” table, Freight code column….for that DELIVERY. ( in the Main Query of the data model of the report)

So If the report is kicked off and the query is not satisfied due to above condition a BLANK Page is printed and if the Condition is satisfied for Company’s own truck the Packing slip Report is printed.

3^rd Doubt: Did you Customize Standard Packing Slip report or is it a Seeded Packing Slip report?

Answer: Since Client has his own Shipping Method (2nd Doubt) It’s a Customized Packing Slip report.

4^th Doubt: Why did you customize a Seeded Packing Slip report? Why can’t you do a Report from Scratch (New Packing Slip Report Altogether?)

Answer: In Order for Oracle to Kick the Report “THE SEEDED PARAMTERS” of the Seeded Report (Packing Slip) should be maintained so I did a Copy of the Original Report and then modified its RDF and the Seeded Concurrent Program name. In this way the Parameters of the Customized Report are the SAME AS Seeded Packing Slip Report.

DOCUMENT SET API which calls these Documents will have a certain Parameters so IT IS VERY IMPORTANT to maintain the same parameters as the original report.

Please Find Below the Original Packing Slip Report and Its Parameters and how we achieved this..

Make a copy of this In order Customize this report …..

Press COPY TO Button and add your own Custom Report Name (INCLUDE PARAMETERS) is a must. In this Way your Parameters are Maintained……

Click Ok..

Change your Application to Custom Shipping Application (This is a Customized Report)

Change the EXECUTABLE ….. Create your own Executable..

Now Lets Look at the Original rdf   ( now Change the name of this original report and maintain the same executable…)

This is your Customized Packing Slip Report Maintain the same parameters as the Original Packing Slip report.

Now change the Executable on the concurrent Program side.. ( Make the Executable “MYCUSTOM”)

And save…

Now modify your rdf according to your requirements.

Remember to deploy your Custom rdf in the respective reports directory ( CD $ <your custom shipping Dir > )

Now in the Beginning we attached this “MYCUSTOM_PACKSLIP” Report to our Custom document set as shown below:

Custom Shipping Application                                          MYCUSTOM_PACKSLIP REPORT

So this gets kicked off when ever there is a Ship Confirmation for a particular delivery

So whenever the report does not have Data a Blank page is getting printed.  So to avoid this we tried

1)   To limit it at XML Template level but did not succeed

What we did was we made ANOTHER COPY of   MYCUSTOM_PACKSLIP REPORT and we named it WRAPPER REPORT.

WRAPPER REPORT is another CONCURRENT PROGRAM, has its OWN EXECUTABLE and its OWN RDF

Original Packslip Report -à  MYCUSTOM_PACKSLIP REPORT à WRAPPER PACKSLIP REPORT

Wshrdpak.rdf                    à    mucust_wshrdpak.rdf                        à  wrap_wshrdpak.rdf

This Wrapper report is attached to Ship Confirm document set

In AFTERREPORT Trigger of the Wrapper report Based on IF conditions we KICK off a CONCURRENT PROGRAM which in turn

Kicks off our CUSTOMIZED PACKSLIP REPORT….(MYCUST_PACKSLIP)

Remember a Printer Should be attached to this report ( MYCUST_PACKSLIP ) ..but no printer is attached to WRAPPER REPORT

See the screenshot below

MYCUST_PACKSLIP Report                ( PAGE ****)

In the AFTER REPORT Trigger in the Wrapper Report we have the following code :

function AfterReport return boolean is

  l_return_status VARCHAR2(5);

  l_msg_count NUMBER;

  l_msg_data VARCHAR2(3000);

lc_freight_code varchar2(30); -----Addded  by PB

lc_printer      varchar2(30); -----Addded  by PB

ln_request_id   number;       -----Addded  by PB

n_RESP_APPL_ID  number;       -----Addded  by PB

n_RESP_ID       number;        -----Addded  by PB

n_USER_ID       number;        -----Addded  by PB

begin

----------------------------------------------------ORACLE SEEDED BEGINS---------------------------

---------------------------

-- Set Print Date Option --

---------------------------

-- If the Print_Mode is Final, that means we need to set the Final_Print_Date for the packing slips.

-- This is done by calling Set_Final_Print_Date server side procedure.

-- If the Print_Mode is not final, we do nothing.

-- The placeholder column :cp_print_date is used as the date to set.

BEGIN

  IF :p_print_mode='FINAL'

  THEN

    WSH_Document_PVT.Set_Final_Print_Date(1.0,NULL,'T',NULL,l_return_status,l_msg_count,l_msg_data,:p_delivery_id,'PACK_TYPE',:p_print_all,:cp_print_date);

    IF l_return_status<>'S'

    THEN

      SRW.message(1,'Fatal error encountered when trying to set final print date.');

      RAISE SRW.program_abort;

    END IF;

  END IF;

END;

--------------------------

-- User Exit Collection --

--------------------------

---------------------------------------------------ORACLE SEEDED ENDS --------------------------------------

-----------------------------------------

--Custom code to call concurrent program

-----------------------------------------

-----THIS FIRST LOOKS AT OUR LOOKUP/VALUESET for shipping method AND CHECKS THE FREIGHT CODE -------

begin

   BEGIN

    select NVL(wc.freight_code,'XXXX')

     into  lc_freight_code

     FROM wsh_new_deliveries del

         ,wsh_carriers wc

         ,wsh_carrier_services wcs

         ,apps.fnd_lookup_values fl

      where wc.carrier_id = wcs.carrier_id

      AND del.ship_method_code = wcs.ship_method_code

      AND UPPER(fl.lookup_code) = UPPER(wc.freight_code)

      AND fl.lookup_type = 'CUSTOM_PACKLIST_CARRIER_VS'

      and del.delivery_id = :p_delivery_id;-------------------------coming from parameter

  EXCEPTION

    WHEN OTHERS THEN

      lc_freight_code := 'XXXX';

  END;

-----THIS FIRST LOOKS AT PRINTER too if the printer is set for the organization -------

BEGIN

  SELECT NVL(WRP.PRINTER_NAME , 'YYYY')

  INTO lc_printer

  FROM

  WSH_REPORT_PRINTERS        WRP

,FND_CONCURRENT_PROGRAMS_TL FCP

WHERE

  WRP.concurrent_program_id = FCP.concurrent_program_id

  AND FCP.user_concurrent_program_name = 'MYCUSTOM_PackSlip' --( NOT THE WRAPPER PROGRAM but customized packing slip )----

  AND WRP.organization_id = :p_organization_id---------------comes from parameter passed for that particular organization ---

  AND WRP.enabled_flag = 'Y';

EXCEPTION

  WHEN OTHERS THEN

  lc_printer := 'YYYY';

  SRW.message('4444', lc_printer);

  SRW.message('5555', SUBSTR (SQLERRM,1,50)); 

END;

  IF lc_freight_code <> 'XXXX'  AND lc_printer <> 'YYYY'

  THEN

    SRW.message('909090','Freight code in value set & printer set for DC');

  n_RESP_APPL_ID := apps.FND_PROFILE.VALUE('RESP_APPL_ID');

  n_RESP_ID      := apps.FND_PROFILE.VALUE('RESP_ID');

  n_USER_ID      := apps.FND_PROFILE.VALUE('USER_ID');

   apps.FND_GLOBAL.Apps_Initialize(n_USER_ID, n_RESP_ID, n_RESP_APPL_ID);

----************************CALLING A PROCEDURE WHICH INTURN CALLS DOES A FND_SUBMIT . ('MYCUSTOM_PackSlip' REPORT ***********************-------

    XXATDWMS_PACKINGSLIP_WRAP_PRC(P_ORGANIZATION_ID      => :P_ORGANIZATION_ID

                                 ,P_DELIVERY_ID          => :P_DELIVERY_ID

                                 ,P_PRINT_CUST_ITEM      => :P_PRINT_CUST_ITEM

                                 ,P_ITEM_DISPLAY         => :P_ITEM_DISPLAY

                                 ,P_PRINT_MODE           => :P_PRINT_MODE

                                 ,P_PRINT_ALL            => :P_PRINT_ALL

                                 ,P_SORT                 => :P_SORT

                                 ,P_DELIVERY_DATE_LOW    => :P_DELIVERY_DATE_LOW

                                 ,P_DELIVERY_DATE_HIGH   => :P_DELIVERY_DATE_HIGH

                                 ,P_FREIGHT_CODE         => :P_FREIGHT_CODE

                                 ,P_QUANTITY_PRECISION   => :P_QUANTITY_PRECISION

                                 ,P_DISPLAY_UNSHIPPED    => :P_DISPLAY_UNSHIPPED

                                 );

    SRW.message('909090','Request id :'||ln_request_id);

  ELSE

  SRW.message('909090','Freight code NOT  in value set (and/or) printer NOT Set/Enabled for DC');

  END IF;

return(true);

EXCEPTION

    WHEN OTHERS THEN

    SRW.message ('123321' , 'Error'||SUBSTR(SQLERRM,1,50));

    return(true);

END;   

BEGIN

  SRW.user_exit('FND SRWEXIT');

EXCEPTION

  WHEN SRW.USER_EXIT_FAILURE THEN

    SRW.message(1,'Failed in SRWEXIT');

    RAISE;

END;

  return (TRUE);

end;

NOW TO PROVIDE YOU THE CODE FROM “XXATDWMS_PACKINGSLIP_WRAP_PRC” PROCDEURE

----PARAMTERS OF THE PROCEDURE ARE SAME PARAMETERS PASSED TO THE REPORT -------------------

CREATE OR REPLACE PROCEDURE APPS.XXATDWMS_PACKINGSLIP_WRAP_PRC(

                                                          P_ORGANIZATION_ID      IN NUMBER

                                                         ,P_DELIVERY_ID          IN NUMBER  

                                                         ,P_PRINT_CUST_ITEM      IN VARCHAR2

                                                         ,P_ITEM_DISPLAY         IN VARCHAR2

                                                         ,P_PRINT_MODE           IN VARCHAR2

                                                         ,P_PRINT_ALL            IN VARCHAR2

                                                         ,P_SORT                 IN VARCHAR2

                                                         ,P_DELIVERY_DATE_LOW    IN DATE

                                                         ,P_DELIVERY_DATE_HIGH   IN DATE

                                                         ,P_FREIGHT_CODE         IN VARCHAR2 

                                                         ,P_QUANTITY_PRECISION   IN NUMBER

                                                         ,P_DISPLAY_UNSHIPPED    IN VARCHAR2

                                                              )

/*************************************************************************

*

* THIS iS CALLED by A AFTER REPORT TRIGGER FROM A WRAPPER REPORT -----

*

**************************************************************************/

IS

ln_request_id      NUMBER;

lc_freight_code    VARCHAR2(240);

n_RESP_APPL_ID     NUMBER;

n_RESP_ID          NUMBER;

n_USER_ID          NUMBER;

ln_conc_program_id NUMBER;

lc_application     VARCHAR2(10);

lc_printer_style   VARCHAR2(40);

lc_printer_name    VARCHAR2(60);

lc_conc_prog_name  VARCHAR2(100) := 'ATDI Packing Slip Report';

ln_copies          NUMBER := 1;

lb_result1         BOOLEAN;

lb_result2         BOOLEAN;

Temp               BOOLEAN;

xml_layout         BOOLEAN;

BEGIN

FND_FILE.PUT_LINE(FND_FILE.LOG,'Entered: ');

            BEGIN

              SELECT concurrent_program_id

                    ,application_short_name

                    ,output_print_style

              INTO   ln_conc_program_id

                   ,lc_application

                   ,lc_printer_style

              FROM   fnd_concurrent_programs fcp

                    ,fnd_application fna

              WHERE  fcp.application_id = fna.application_id

              AND    concurrent_program_name = 'MYCUSTOM_PACKSLIP';

            EXCEPTION

              WHEN OTHERS THEN

                FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Occured when trying to derive the printer style for the concurrent program - '||lc_conc_prog_name||' - '||SUBSTR(SQLERRM,1,225));

            END;

FND_FILE.PUT_LINE(FND_FILE.LOG,'conc_prog_id:'||ln_conc_program_id);

FND_FILE.PUT_LINE(FND_FILE.LOG,'application_short_name:'||lc_application);

FND_FILE.PUT_LINE(FND_FILE.LOG,'printer_style:'||lc_printer_style);           

            BEGIN

              SELECT WRP.printer_name

              INTO   lc_printer_name

              FROM   wsh_report_printers WRP

              WHERE  WRP.concurrent_program_id = ln_conc_program_id

              AND    WRP.enabled_flag          = 'Y'

              AND    WRP.default_printer_flag  = 'Y'

              AND    WRP.organization_id = P_ORGANIZATION_ID;

            EXCEPTION

              WHEN OTHERS THEN

                FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Occured when trying to derive the printer name for the organization id - '||p_organization_id||' - '||SUBSTR(SQLERRM,1,225));

            END;

FND_FILE.PUT_LINE(FND_FILE.LOG,'printer_name:'||lc_printer_name);

FND_FILE.PUT_LINE(FND_FILE.LOG,'copies:'||ln_copies);          

            BEGIN

              IF(lc_printer_name IS NOT NULL) THEN

                lb_result1 := fnd_request.set_print_options(

                                                            printer         => lc_printer_name

                                                           ,style           => lc_printer_style

                                                           ,copies          => ln_copies

                                                           ,save_output     => TRUE

                                                           ,print_together  => 'N'

                                                           );

                 COMMIT;

                lb_result2 := fnd_submit.add_printer

                                                    (

                                                     printer => lc_printer_name

                                                    ,copies  => ln_copies

                                                      );

                 COMMIT;                                  

                IF(lb_result1)THEN

                   FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting Print Options successful');

                ELSE

                   FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting Print Options failed');

                END IF;

                IF(lb_result2)THEN

                   FND_FILE.PUT_LINE(FND_FILE.LOG,'Adding Printer successful');

                ELSE

                   FND_FILE.PUT_LINE(FND_FILE.LOG,'Adding Printer failed');

                END IF;

              END IF;

              FND_FILE.PUT_LINE(FND_FILE.LOG,'Printer Name - '||lc_printer_name);

              FND_FILE.PUT_LINE(FND_FILE.LOG,'Number Of Copies - '||ln_copies);

            END;

        n_RESP_APPL_ID := apps.FND_PROFILE.VALUE('RESP_APPL_ID');

        n_RESP_ID      := apps.FND_PROFILE.VALUE('RESP_ID');

        n_USER_ID      := apps.FND_PROFILE.VALUE('USER_ID');

     apps.FND_GLOBAL.Apps_Initialize(n_USER_ID, n_RESP_ID, n_RESP_APPL_ID);

     FND_FILE.PUT_LINE(FND_FILE.LOG,'Parameters sent to Report');

     FND_FILE.PUT_LINE(FND_FILE.LOG,'Org_id:'||P_ORGANIZATION_ID);

     FND_FILE.PUT_LINE(FND_FILE.LOG,'delivery_id:'||P_DELIVERY_ID);

xml_layout := FND_REQUEST.ADD_LAYOUT('XXWMS',’MYCUST_PACKSLIP’ ,'en','US','PDF');---------ADDING LAYOUT AS THIS IS XML PUBLISHER REPORT ----

     COMMIT;

     IF (xml_layout) THEN

     FND_FILE.PUT_LINE(FND_FILE.LOG,'Xml Template Successfully tied programtically');

     ELSE

     FND_FILE.PUT_LINE(FND_FILE.LOG,'Xml Template Unsucessful');

     END IF;

    ln_request_id :=  FND_REQUEST.SUBMIT_REQUEST (APPLICATION  => 'XXWMS'

                                 ,PROGRAM      => 'MYCUST_PACKSLIP'

                                 ,DESCRIPTION  => 'MYCUST_PACKSLIP'

                                 ,ARGUMENT1    =>  P_ORGANIZATION_ID

                                 ,ARGUMENT2    =>  P_DELIVERY_ID

                                 ,ARGUMENT3    =>  P_PRINT_CUST_ITEM

                                 ,ARGUMENT4    =>  P_ITEM_DISPLAY

                                 ,ARGUMENT5    =>  P_PRINT_MODE

                                 ,ARGUMENT6    =>  P_PRINT_ALL

                                 ,ARGUMENT7    =>  P_SORT

                                 ,ARGUMENT8    =>  P_DELIVERY_DATE_LOW

                                 ,ARGUMENT9    =>  P_DELIVERY_DATE_HIGH

                                 ,ARGUMENT10    => P_FREIGHT_CODE

                                 ,ARGUMENT11    => P_QUANTITY_PRECISION

                                 ,ARGUMENT12    => P_DISPLAY_UNSHIPPED); 

  COMMIT;                              

  FND_FILE.PUT_LINE(FND_FILE.LOG,'ln_request_id : '||ln_request_id);

EXCEPTION

  WHEN OTHERS THEN NULL;

    FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in called procedure from Wrapper : '||SQLERRM);

    COMMIT;

END XXATDWMS_PACKINGSLIP_WRAP_PRC;

/

So WHENEVER A DOCUMENT SET IS KICKED off

1)   IT KICKS OF OUR WRAPPER REPORT

2)   IN “AFTER REPORT” TRIGGER OF WRAPPER REPORT IF THE CONDITION IS NOT SATISFIED (SHIPPING METHOD IS UPS/FEDEX , THE PROCEDURE IS NOT CALLED AND SO THE CUSTOMIZED PACKING SLIP REPORT IS NOT CALLED)

3)   THIS WRAPPER REPORT DO NOT HAVE ANY PRINTER ATTACHED TO IT ( SO NO BLANK PAGE IS PRINTED) (REFER PAGE ****)

4)   IF THE CONDITION IS SATISFIED IN THE AFTER REPORT TRIGGER OF THE WRAPPER REPORT ( PROCUDURE IS CALLED WITH THE REPORT PARAMETERS PASSED AND THE MYCUST_PACKSLIP REPORT IS CALLED )

5)   MY CUST_PACKSLIP REPORT IS PRINTED AS THIS AS PRINTER ALREADY ATTACHED (MYCUST_PACKSLIP IS A XML PUBLISHER REPORT) (REFER PAGE ****)

SO IN THIS WAY WE CAN ELIMINATE BLANK PAGES…

Prepared by Pramod Bellamkonda B.E ; M.S (USA)

Oracle Apps Technical Consultant