Tuesday, June 19, 2012

Barcode in XML Publisher

  1. Client Setup
    • Get the font IDAutomation font from idautomation
    •  Place the IDAutomation font under c:\Windows\Fonts.
    •  Select IDAutomation font for Barcode fields in XML Publisher Template.
    • Calling encoder in the template.(Only if vendor specific fonts and java encoder is used else ignore)
      • Add following expression in your template, It can be added directly to template or as a value to Form Field.
        <?register-barcode-vendor:'oracle.apps.xdo.template.rtf.util.barcoder.BarcodeUtilaaa';'XMLPBarVendor'?>
        Expression used to register vendor java encoder
      • Add format-barcode syntax to barcode field. Replace BARCODE in below syntax with your xml field.
        *<?format-barcode:BARCODE;'code128a';'XMLPBarVendor'?>*
  2. Server Setup -- Only needed if you have vendor specific barcode fonts else ignore.
    • If vendor specific fonts are used, java encoder will be provided along with font which will be recognized by external device. 
    • Below imports have to be added to the vendor provided java encoder. 
        package oracle.apps.xdo.template.rtf.util.barcoder;
      import java.util.Hashtable;
      import java.lang.reflect.Method;
      import oracle.apps.xdo.template.rtf.util.XDOBarcodeEncoder;
      import oracle.apps.xdo.common.log.Logger;
      // This class name will be used in the register vendor field in the template.
      public class BarcodeUtil implements XDOBarcodeEncoder
      // The class implements the XDOBarcodeEncoder interface
      {
      // This is the barcode vendor id that is used in the register vendor field and
      // format-barcode fields
      public static final String BARCODE_VENDOR_ID = "XMLPBarVendor";
      // The hastable is used to store references to the encoding methods
      public static final Hashtable ENCODERS = new Hashtable(10);
      // The BarcodeUtil class needs to be instantiated
      public static final BarcodeUtil mUtility = new BarcodeUtil();
      // This is the main code that is executed in the class, it is loading the methods
      // for the encoding into the hashtable. In this case we are loading the three code128
      // encoding methods we have created.
      static {
      try {
      Class[] clazz = new Class[] { "".getClass() } ;
      ENCODERS.put("code128a",mUtility.getClass().getMethod("code128a", clazz));
      ENCODERS.put("code128b",mUtility.getClass().getMethod("code128b", clazz));
      ENCODERS.put("code128c",mUtility.getClass().getMethod("code128c", clazz));
      } catch (Exception e) {
      // This is using the XML Publisher logging class to push errors to the XMLP log file.
      Logger.log(e,5);
      }
      }
      // The getVendorID method is called from the template layer at runtime to ensure the correct
      // encoding method are used
      public final String getVendorID()
      {
      return BARCODE_VENDOR_ID;
      }
      // The isSupported method is called to ensure that the encoding method
      // called from the template is actually present in this class. If not
      // then XMLP will report this in the log.
      public final boolean isSupported(String s)
      {
      if(s != null)
      return ENCODERS.containsKey(s.trim().toLowerCase());
      else
      return false;
      }
      // The encode method is called to then call the appropriate encoding method,
      // in this example the code128a/b/c methods.
      public final String encode(String s, String s1)
      {
      if(s != null && s1 != null)
      {
      try
      {
      Method method = (Method)ENCODERS.get(s1.trim().toLowerCase());
      if(method != null)
      return (String)method.invoke(this, new Object[] {
      s
      });
      else
      return s;
      }
      catch(Exception exception)
      {
      Logger.log(exception,5);
      }
      return s;
      } else
      {
      return s;
      }
      }
      /** Add Vendor Method for Code128a */
      public static final String code128a( String DataToEncode )
      {
      return Printable_string;
      }
      }
        
    •  Generate class file from java code and place it under OA_JAVA/oracle/apps/xdo/template/rtf/util/barcoder. If barcoder directory doesn't exist create one.


      $ cd $OA_JAVA/oracle/apps/xdo/template/rtf/util

      $ mkdir barcoder
    •  Change permissions of the barcoder directory to 777/755.
  3. XML Publisher Font Setup
    No longer XML publisher fonts needed to be placed on the server. They can be uploaded and used from XML publisher font file and font mappings.
    For explanatory purpose
    IDAutomationHC39M font and font name is used. Make sure to use your own font while defining.
    IMP***** Font selected while defining XML template MUST match with
    font name defined in xml publisher.

    • Navigate to XML Publisher responsibility.
      Go to Administration Tab.
      Click on Font Files and create font file
      Font Name: IDAutomationHC39M
      File: Select IDAutomationHC39M.ttf from your saved location.
      Click Apply.
      Click on Font Mappings. Click “Create Font Mapping Set”.
      Mapping Name: Barcodes
      Mapping Code: Barcodes
      Type: FO To Pdf
      Click Apply.
      Click on Create Font Mapping. Fill the values as below screen shot.
      Font Family: IDAutomationHC39M
      In the next page enter
      Font Value: IDAutomationHC39M
      Click Apply.
      Click on Administration Tab

      Expand FO Processing and enter Barcodes as Font Mapping Set value.

      Click Save.
Run the concurrent program and you should be able to see the Barcode printed on your output and is recognized by external scanning device.

Wednesday, May 30, 2012

Passing parameters to xml publisher reports from concurrent manager not working

Ensure that below steps are in place and followed.

  1. Make sure that the parameter TOKENS defined in concurrent program request are in CAPS.If defined in small or init case then XML publisher will not recognize parameters. All parameters in Data definition template should also be defined in CAPS.



  2. XML Data definition has following statement under properties tag

         <property name="include_parameters" value="true"/>

  3. Parameters sequence in data definition file should match with concurrent program parameter sequences.

    Submit concurrent program FND_REQUEST.SUBMIT_REQUEST along with XML Publisher layout and printer options

    FND_REQUEST.SUBMIT_REQUEST submits concurrent request to be processed by a concurrent manager.

    Using submit_request will only submits the program and will not attach any layout or print option. Code below will help to set XML publisher template/layout along with print option.

    *****Add_layout and Add_printer procedures are optional in calling submit_request. Use only if you need to set them.

    Layout is submitted to a concurrent request using below procedure

    fnd_request.add_layout (
                        template_appl_name   => 'Template Application',
                        template_code        => 'Template Code',
                        template_language    => 'en', --Use language from template definition
                        template_territory   => 'US', --Use territory from template definition
                        output_format        => 'PDF' --Use output format from template definition

                         );


    Setting printer while submitting concurrent program

    fnd_submit.set_print_options (printer      => lc_printer_name
                                       ,style        => 'PDF Publisher'
                                       ,copies       => 1
                                       );


    fnd_request.add_printer (
                        printer => printer_name,
                        copies  => 1);




    DECLARE
       lc_boolean        BOOLEAN;
       ln_request_id     NUMBER;
       lc_printer_name   VARCHAR2 (100);
       lc_boolean1       BOOLEAN;
       lc_boolean2       BOOLEAN;
    BEGIN

          -- Initialize Apps 
          fnd_global.apps_initialize (>USER_ID<
                                     ,>RESP_ID<
                                     ,>RESP_APPL_ID<
                                     );
       -- Set printer options
       lc_boolean :=
          fnd_submit.set_print_options (printer      => lc_printer_name
                                       ,style        => 'PDF Publisher'
                                       ,copies       => 1
                                       );
       --Add printer

       lc_boolean1 :=
                    fnd_request.add_printer (printer      => lc_printer_name
                                             ,copies       => 1);
      --Set Layout

      lc_boolean2 :=
                   fnd_request.add_layout (
                                template_appl_name   => 'Template Application',
                                template_code        => 'Template Code',
                                template_language    => 'en', --Use language from template definition
                                template_territory   => 'US', --Use territory from template definition
                                output_format        => 'PDF' --Use output format from template definition
                                        );
       ln_request_id :=
          fnd_request.submit_request ('FND',                -- application
                                      'COCN_PGM_SHORT_NAME',-- program short name
                                      '',                   -- description
                                      '',                   -- start time
                                      FALSE,                -- sub request
                                      'Argument1',          -- argument1
                                      'Argument2',          -- argument2
                                      'N',                  -- argument3
                                      NULL,                 -- argument4
                                      NULL,                 -- argument5
                                      'Argument6',          -- argument6
                                      CHR (0)               -- represents end of arguments
                                     );
       COMMIT;

       IF ln_request_id = 0
       THEN
          dbms.output.put_line ('Concurrent request failed to submit');
       END IF;
    END;



    Handling Date formats and passing date to concurrent program

    Below note is only applicable if value set FND_STANDARD_DATE is used in oracle apps concurrent program.

    Date parameters are passed from concurrent programs to subroutines in the format of  YYYY/MM/DD HH:MM:SS (eg: 2012/01/31 00:00:00). Date from concurrent program cannot be used directly in the programs. To handle date input from concurrent program, it has to be accepted in variable of VARCHAR2 and  convert to date format using the expression

               TO_DATE (SUBSTR (<date_variable>, 1, 10), 'yyyy/mm/dd')
                                                                   OR
              select FND_DATE.CANONICAL_TO_DATE(<date_variable>) from dual;

    Passing date to fnd_request.submit_request

    Date can only be passed to fnd_request.submit_request in the format of  YYYY/MM/DD HH:MM:SS (eg: 2012/01/31 00:00:00). Any other format will lead to error or invalid date conversion. Once accepted from concurrent program date will be converted to the format of 'DD-MON-YY'. It should be converted before passing to submit_request using the expression

    to_char(to_date(<date_variable>,'DD-MON-YY'),'yyyy/mm/dd')||' 00:00:00'

    FND_ATTACHMENT Details Oracle Apps


    SELECT   fndattdoc.pk1_value
            ,st.short_text
            ,fdlt.long_text
            ,pk1_value hdr_attach_pk
            ,fnddoc.datatype_name hdr_attach_dtype
            ,fndattfn.function_name
            ,fndattdoc.entity_name
        FROM fnd_attachment_functions fndattfn
            ,fnd_doc_category_usages fndcatusg
            ,fnd_documents_vl fnddoc
            ,fnd_attached_documents fndattdoc
            ,fnd_documents_short_text st
            ,fnd_documents_long_text fdlt
       WHERE fndattfn.attachment_function_id = fndcatusg.attachment_function_id
         AND fndcatusg.category_id = fnddoc.category_id
         AND fnddoc.document_id = fndattdoc.document_id
         AND fndattfn.function_name =<Func. Name>
         AND fndattdoc.entity_name =<Entity Name>
         AND fnddoc.category_description = <Category Name>

         AND fnddoc.media_id = st.media_id
         AND fnddoc.media_id = fdlt.media_id
    ORDER BY fndattdoc.seq_num


    Tuesday, May 29, 2012

    Customer information Queries



    Ship To Customer Address
    Use below query to fetch ship_to address for a Order or Invoice
    For Order join hcsu.site_use_id = order.ship_to_org_id
    For Invoice join  hcsu.site_use_id = invoice.ship_to_site_use_id
           SELECT hcsu.site_use_id
                ,hcsu.location
                ,hcas.cust_acct_site_id "address_id"
                ,hps.party_site_number
                , hl.address1 || '  ' || hl.address2
                ,hl.city
                ,hl.state
                ,hl.country
                ,hl.postal_code
                ,hcsu.bill_to_site_use_id
                ,hcsu.contact_id
            FROM hz_cust_accounts_all hca
                ,hz_cust_acct_sites hcas
                ,hz_cust_site_uses hcsu
                ,hz_party_sites hps
                ,hz_locations hl
           WHERE 1 = 1
             AND hca.cust_account_id = hcas.cust_account_id
             AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
             AND hps.location_id = hl.location_id
             AND hps.party_site_id = hcas.party_site_id
              --AND hcsu.primary_flag = 'Y'
             AND hcsu.status = 'A'
             AND hcsu.site_use_code = 'SHIP_TO'
             AND hca.cust_account_id = <customer_id>
             AND hcsu.org_id =<org_id>;

    Bill To Customer Address
    Use below query to fetch bill_to address for a Order or Invoice
    For Order join hcsu.site_use_id = order.invoice_to_org_id
    For Invoice join  hcsu.site_use_id = invoice.bill_to_site_use_id
           SELECT hcsu.site_use_id
                ,hcsu.location
                ,hcas.cust_acct_site_id "address_id"
                ,hps.party_site_number
                ,hl.address1 || '  ' || hl.address2
                ,hl.city
                ,hl.state
                ,hl.country
                ,hl.postal_code
                ,hcsu.contact_id
            FROM hz_cust_accounts_all hca
                ,hz_cust_acct_sites hcas
                ,hz_cust_site_uses hcsu
                ,hz_party_sites hps
                ,hz_locations hl
           WHERE 1 = 1
             AND hca.cust_account_id = hcas.cust_account_id
             AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
             AND hps.location_id = hl.location_id
             AND hps.party_site_id = hcas.party_site_id
             AND hcsu.primary_flag = 'Y'
             AND hcsu.status = 'A'
             AND hcsu.site_use_code = 'BILL_TO'
             AND hca.cust_account_id = <customer_id>
             AND hcsu.org_id =<org_id>;

    Cusomer Contact's by Party/SiteUses & Customer Contact information
    Use below query to get the customer contact by party/site uses.
    You can use the same query to get contacts contact type by joining with  hz_contact_points hcp
    using the conditions hcp.owner_table_name = 'HZ_PARTIES' and hcp.owner_table_id = hcar.party_id (from below query)

    SELECT  hcar.cust_account_id
        , hcar.cust_acct_site_id
        , hcar.party_id rel_party_id
        , hr.object_id org_party_id
        , hr.subject_id person_party_id
        , hr.relationship_type
        , hp.party_name contact_name
        FROM hz_cust_account_roles hcar
        , hz_relationships hr
        , hz_parties hp
        WHERE 1                   =1
            AND hcar.role_type       = 'CONTACT'
            AND hcar.party_id        = hr.party_id
            AND hr.relationship_code = 'CONTACT_OF'
            AND hr.subject_id        = hp.party_id
            AND hcar.cust_account_id = <customer_id>
     Above query with hz_contact_points join to get site uses contact details
    SELECT  hcar.cust_account_id
        , hcar.cust_acct_site_id
        , hcar.party_id rel_party_id
        , hr.object_id org_party_id
        , hr.subject_id person_party_id
        , hr.relationship_type
        , hp.party_name contact_name
        , hcp.email_address
        FROM hz_cust_account_roles hcar
        , hz_relationships hr
        , hz_parties hp
        , hz_contact_points hcp
        WHERE 1                   =1
            AND hcar.role_type       = 'CONTACT'
            AND hcar.party_id        = hr.party_id
            AND hr.relationship_code = 'CONTACT_OF'
            AND hr.subject_id        = hp.party_id
            AND hcp.owner_table_id = hcar.party_id
            --AND hcp.contact_point_type = 'EMAIL'
            AND hcar.cust_account_id = <customer_id>
            and hcar.cust_acct_site_id = (select cust_acct_site_id from
            hz_cust_site_uses_all where site_use_id = <site_use_id>);


    Customer Contact Phone/Fax/Email
    Customer contact can be defined at party or party site level.

    Contact details from Party level. Modify PHONE_LINE_TYPE to FAX or EMAIL to get related data.
           SELECT hcp.raw_phone_number
              FROM hz_contact_points hcp, hz_relationships hr, hz_parties hzp
             WHERE hcp.owner_table_id = hr.party_id
               AND hr.object_id = nvl(&party_id,hr.object_id)
               AND hr.status = 'A'
               AND hcp.status = 'A'
               AND hcp.primary_flag = 'Y'
               AND hzp.party_id = hr.party_id
               AND hcp.phone_line_type = 'GEN'
               AND hcp.owner_table_name = 'HZ_PARTIES'

    Contact details from Party Site level.
             SELECT hcp.raw_phone_number
               FROM hz_contact_points hcp
              WHERE hcp.owner_table_id = nvl(&party_site_id,hcp.owner_table_id)
                AND phone_line_type = 'GEN'
                AND primary_flag = 'Y'
                AND owner_table_name = 'HZ_PARTY_SITES';

    Customer Profile
    Customer
       SELECT description
         FROM ar_customer_profiles_v a, ra_terms b
        WHERE customer_id = nvl(&customer_id,customer_id)
          AND status = 'A'
          AND site_use_id IS NULL
          AND a.standard_terms = b.term_id;

    Customer Site
             SELECT description
               FROM ar_customer_profiles_v a, ra_terms b
              WHERE customer_id = nvl(&customer_id,customer_id)
                AND status = 'A'
                AND site_use_id = nvl(&bill_site_use_id,site_use_id)
                AND a.standard_terms = b.term_id;

    Customer Credit Limits
    Customer
       SELECT nvl(overall_credit_limit,0)
                     ,nvl(trx_credit_limit,0)
         FROM hz_cust_profile_amts
        WHERE cust_account_id = nvl(&customer_id,customer_id)
          --AND status = 'A'
          AND currency_code = &CURRENCY_CODE -- For Multi Org
          AND site_use_id IS NULL;

    Customer Site
             SELECT nvl(overall_credit_limit,0)
                   ,nvl(trx_credit_limit,0)
               FROM hz_cust_profile_amts
              WHERE cust_account_id = nvl(&customer_id,customer_id)
                --AND status = 'A'
                AND site_use_id =  nvl(&bill_site_use_id,site_use_id);