Oracle EBS Technical

Query to fetch Recurring Journal batch, Journal Header, Journal Line details(The accounting combination segments can be selected based on the Chart of Accounts Structure):


SELECT grb.NAME AS batch_name, grh.recurring_batch_id,
       grh.recurring_header_id, grh.NAME journal_name, grl.recurring_line_num,
       gcc.segment1, gcc.segment2, gcc.segment3, gcc.segment4, gcc.segment5,
       gcc.segment6, grl.description, grlc.rule_num, grlc.OPERATOR,
       grlc.amount
  FROM apps.gl_recurring_batches grb,
       apps.gl_recurring_line_calc_rules grlc,
       apps.gl_recurring_lines grl,
       apps.gl_recurring_headers_v grh,
       gl_code_combinations gcc
 WHERE grb.recurring_batch_id = grh.recurring_batch_id
   AND grlc.rule_num = grl.recurring_line_num
   AND grl.recurring_header_id = grlc.recurring_header_id
   AND grl.recurring_header_id = grh.recurring_header_id
   AND grl.code_combination_id = gcc.code_combination_id;


Query to derive Parent and Children Accounting Flexfield values, the query can be modified to derive the parent/child segment values for other segments. 
Parameters:
:P_Segment_Num -- To derive the segment number -- Required parameter
:P_Parent_Flex_Value --  For the parent segment value for which the children are required, if no value is passed it the query will fetch all the parent, child values for the segment mentioned in P_Segment_Num
 Note: Replace "<n>" with the segment number for which you want to find the 
          children

 SELECT gcc.code_combination_id
      ,ffvc.parent_flex_value
      ,gcc.segment1 Balancing_Segment
      ,gcc.segment<n>,ffvc.description
  FROM gl_code_combinations gcc,
       fnd_flex_value_children_v ffvc,
       fnd_id_flex_segments fifs
 WHERE 1=1
   AND fifs.flex_value_set_id = ffvc.flex_value_set_id
   AND fifs.id_flex_code='GL#'
   AND ffvc.parent_flex_value = NVL(:P_Parent_Flex_value, ffvc.parent_flex_value)
   AND ffvc.flex_value = gcc.segment<n>
   AND fifs.application_column_name = 'SEGMENT'||:P_Segment_Num;  


 ------------------------------------------------------------------------------------------------------------------------------------
How to Setup MICR Font- XML Publisher(Oracle EBS)
Background for Check printing
Check printing from the E-Business Suite has historically required a third-party solution that can either query the necessary data and format the output or accept the existing flat text output generated by the provided check print programs. To then generate the checks, the format components had to be placed with pixel-perfect precision onto the page using the necessary MICR font to format the bank routing number and bank account of the payee. Alternatively customers could take the existing check print program and customize it to format the output correctly. These solutions are costly in terms of their implementation, their ongoing maintenance and their complexity. This all adds to the total cost of ownership for the E-Business Suite.
Oracle XML Publisher offers integrated functionality that allows customers to design their own check format using familiar desktop tools that can then be applied to a check print data source, whether that is the seeded Oracle Report or a custom solution. This format is easily maintainable and can include all the necessary components required in the final output to be printed onto blank check stock.
Taking this approach reduces the complexity of the systems surrounding the check generation and thereby reducing the total cost of ownership of the E-Business Suite.

Creating the MICR data placeholders
XML Publisher has the ability to use external fonts at runtime; as long as the font is accessible at runtime on the server. XML Publisher does not currently license a MICR font but a quick search on the web will find both freeware and commercial fonts that can be used.
Once you have the MICR font you will need to make it available to MS Word. Do this by installing it in the <<windows root>>/fonts directory. The font can now be accessed from Word. You can check this by looking for it in the font drop box.
To insert the MICR strings into your template, insert the appropriate number of form fields and map them to the XML data that will come in at runtime. Highlight these fields and set the font to the MICR font installed earlier.

Following White paper will provide a step by step process with screenshots explaining the processing of setting up Magnetic Ink Character Recognition(MICR) Font in XML Publisher Administrator Responsibility:
Background for Check printing
Check printing from the E-Business Suite has historically required a third-party solution that can either query the necessary data and format the output or accept the existing flat text output generated by the provided check print programs. To then generate the checks, the format components had to be placed with pixel-perfect precision onto the page using the necessary MICR font to format the bank routing number and bank account of the payee. Alternatively customers could take the existing check print program and customize it to format the output correctly. These solutions are costly in terms of their implementation, their ongoing maintenance and their complexity. This all adds to the total cost of ownership for the E-Business Suite.
Oracle XML Publisher offers integrated functionality that allows customers to design their own check format using familiar desktop tools that can then be applied to a check print data source, whether that is the seeded Oracle Report or a custom solution. This format is easily maintainable and can include all the necessary components required in the final output to be printed onto blank check stock.
Taking this approach reduces the complexity of the systems surrounding the check generation and thereby reducing the total cost of ownership of the E-Business Suite.

Creating the MICR data placeholders
XML Publisher has the ability to use external fonts at runtime; as long as the font is accessible at runtime on the server. XML Publisher does not currently license a MICR font but a quick search on the web will find both freeware and commercial fonts that can be used.
Once you have the MICR font you will need to make it available to MS Word. Do this by installing it in the <<windows root>>/fonts directory. The font can now be accessed from Word. You can check this by looking for it in the font drop box.
To insert the MICR strings into your template, insert the appropriate number of form fields and map them to the XML data that will come in at runtime. Highlight these fields and set the font to the MICR font installed earlier.

Following White paper will provide a step by step process with screenshots explaining the processing of setting up Magnetic Ink Character Recognition(MICR) Font in XML Publisher Administrator Responsibility:

    No comments:

    Post a Comment