Tuesday, January 22, 2013

Unable to find Inventory Organization on Organization Assignment Form

Unable to find Inventory Organization on Organization Assignment Form

I was sucked tomorrow, when I was performing Oracle Inventory module setup and was try to define Master item. Problem we were facing was we were unable to find our Inventory Organization on Organization Assignment form. Only choice there was Master Organization.

Form Name: Inventory > Items > Master Items (INVIDITM.fmb)

Solution: After a lot of search on Metalink and Google, I was unable to resolve this issue. At the end of the day I found the basic mistake in definition of Child Inventory Organization. At Organization Parameter form there is field 'Item Master Organization', I was providing there my child inventory Organization name. Replaced that with master inventory organization. Issue was resolved.

Inventory > Setup > Organization > Parameters

Unable to find Inventory Organization on Organization Assignment Form
Unable to find Inventory Organization on Organization Assignment Form

Creating AP Checks in Oracle Applications R12 (Automatic Check Printing)

Creating AP Checks in Oracle Applications R12 (Automatic Check Printing)


Audience:  Should know the basics of  Oracle BI Publisher / XML Publisher.
Step 1- Create your own customized template in RTF file format (according to check format provided by the bank), using BI Publisher, you can get the xml code by submitting a simple payment, and view the log file of  program “Format Payment Instructions” copy the highlighted portion.
You can download a sample Check format from here
http://www.4shared.com/office/2UYWOpBh/Check_Printing_Format_-_28sep1.html


Step 2-  Create data definition from xml publisher responsibility > Home > Data Definition
Creating AP Checks
Creating AP Checks

Step 3- Create template from xml publisher responsibility > Home > Templates, also upload template file
Creating AP Checks
Creating AP Checks


Step 4- Go to Payables Manager > Setup > Payment > Payment Administrator, go to formats (go to task) option under formats section. 
Creating AP Checks printing
Creating AP Checks printing

Step 5- Create a new check format by pressing create button
Creating AP Checks printing
Creating AP Checks printing

Step 6- Enter  the  code  template in Code field, Enter desired name of  Format (e.g HBL Format) select Data Extract according to following screenshot, and Enter  the name of  Template Created in Step 3

Creating AP Checks printing
Creating AP Checks printing

Step 7-Create a new  payment process profile specific for  this payments of  this check format.

Step 8- Go to Payables Manager > Setup > Payment > Payment Administrator, go to Payment Process Profiles (go to task) option under codes section.
Creating AP Checks printing
Creating AP Checks printing

Step 8- Query your template created in step3 by name or by code and select that template.
Step 9- Press update button to update that template.
Creating AP Checks printing
Creating AP Checks printing
Step 10- Select
                                          i.                        Processing type ‘printed’
                                       ii.                        Print file ‘send to printer’
                                    iii.                        Disallow Save/Re-Print (if u want to restrict user not to view/save the file)
                                   iv.                        Automatically Print after Formatting (printing will start automatically)
                                      v.                        Select your installed printer form printer lov.
Creating AP Checks printing
Creating AP Checks printing
Make a payment entry in the system and your printer will start printing you check automatically, cheers J.
IBY_FD_EXTRACT_EXT_PUB is a standard PL/SQL package that is used to extend (i.e. add additional tags to) the XML file generated during a R12 Oracle Payments ‘Payment Process Request’:

This XML file is then used as the data source for the XML Publisher cheque or electronic file presentation layout.

To understand how to use IBY_FD_EXTRACT_EXT_PUB, we have to understand the structure of the XML file created by the Payments process request.

There are 4 main levels to the file. These are:
Top Level: Outbound Payment Instruction
This is the top level of the XML File and there is one Outbound Payment Instruction per Payment process request.

Level 2: Outbound Payment:
This is the Payment Level i.e. an individual cheque or BACS payment amount to a supplier. There can be multiple Outbound Payments per Outbound Payment Instruction.

Level 3: Document Payable:
Details the documents (i.e. invoices) being paid. There can be multiple Document Payable tags per Outbound Payment

Level 4: Document Payable Line:
This level details the invoice line. There can be multiple Document Payable Line tags per Document Payable.


Additional XML tags can be added at each of these 4 levels by coding different PL/SQL functions in IBY_FD_EXTRACT_EXT_PUB.
The following table lists the functions you need to modify to add additional tags to each level of the XML file:

XML File Level
IBY_FD_EXTRACT_EXT_PUB Function To Modify
Example of Parameter Usage
OutboundPaymentInstruction
Get_Ins_Ext_Agg(p_payment_instruction_id IN NUMBER)

SELECT *
FROM iby_pay_instructions_all
WHERE payment_instruction_id = p_payment_instruction_id;


OutboundPayment
Get_Pmt_Ext_Agg(p_payment_id IN NUMBER)
SELECT *
FROM iby_payments_all ipa
WHERE ipa.payment_id = p_payment_id;
DocumentPayable
Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)
SELECT *
FROM iby_docs_payable_all dp WHERE dp.document_payable_id = P_document_payable_id;
DocumentPayableLine
Get_Docline_Ext_Agg(p_document_payable_id IN NUMBER, p_line_number IN NUMBER)

PaymentProcessProfile
Get_Ppr_Ext_Agg(p_payment_service_request_id IN NUMBER)
SELECT *
FROM iby_pay_service_requests WHERE payment_service_request_id = p_payment_service_request_id;


Code Example:
  FUNCTION Get_Doc_Ext_Agg(p_document_payable_id IN NUMBER)
  RETURN XMLTYPE
  IS
    l_ins_ext_agg XMLTYPE;

    CURSOR l_ins_ext_csr (p_document_payable_id IN NUMBER) IS
    SELECT XMLConcat(
             XMLElement("Extend",
               XMLElement("DocSequenceValue", ai.DOC_SEQUENCE_VALUE)
                       )
           )
      FROM iby_docs_payable_all dp,ap_invoices_all ai
     WHERE dp.calling_app_doc_unique_ref2 = ai.invoice_id
     AND dp.document_payable_id = p_document_payable_id;

  BEGIN

    OPEN l_ins_ext_csr (p_document_payable_id);
    FETCH l_ins_ext_csr INTO l_ins_ext_agg;
    CLOSE l_ins_ext_csr;

    RETURN l_ins_ext_agg;

  END Get_Doc_Ext_Agg;


References:
2-               Doc ID 562806.1
3-               Following is the link to Add custom fields to AP checks


Friday, January 11, 2013

Oracle Report Format Disturbed. (Language changed and formating Issues)

Oracle Report Format Disturbed. (Language changed and formatting Issues)



Problem: When I was getting output of report form Oracle E-Business Suite R12, font of report and tittles of the report was disturbed. Report output was totally disturbed. Also the tittle of report was changed to some other language.


Solution: We have to add the following lines to our environment file.


1-  $ cd $APPL_TOP
2-  vi <SID>_hostname.env (eg. vi PROD_stest.env)
3-  Search for APPL_CONFIG_HOME
4-  Add the following line under APPL_CONFIG_HOME.

IX_PRINTING="/appshome/appsprod/PROD/apps/apps_st/appl/fnd/12.0.0/resource/ixlib.cfg"
export IX_PRINTING


Change the path as per your file system.
5-  Save and exit.
6-  Bounce the application services.
7-  Retest the issue.

Thursday, January 10, 2013

Tkprof - Convert Trace .trc To Readable

Tkprof - Convert Trace .trc To Readable


Here is the syntax of tkprof command.

tkprof <file.trc> <file.tkprof> explain=<apps_user>/<pswd>

Here,

 tkprof: is command
<file.trc>: is file name of trace file with location (if currently not in same directory)
<file.tkprof>: is output file name
explain=<apps_user>/<pswd>: provide apps user name and password.



For Example:



tkprof /usr/tmp/oracle.trc /usr/tmp/output.tkprof explain=apps/****