Friday, 3 March 2017

BARCODE IN XML PUBLISHER REPORTS ORACLE R12

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'?>




·         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.



Navigate to XML Publisher responsibility.
Go to Administration Tab.
Click on Font Files and create font file


HERE THE FONT IS IDAutomationSHcC128M



Font Name: IDAutomationSHcC128M
File: Select IDAutomationSHcC128M.ttf from your saved location.
Click Apply.
Click on Font Mappings. Click “Create Font Mapping Set”.

Mapping Name: IDAutomationSHcC128M
Mapping Code: IDAutomationSHcC128M
Type: FO To Pdf
Click Apply.




Click on Create Font Mapping. Fill the values as below screen shot.
Font Family: IDAutomationSHcC128M
Font Value: IDAutomationSHcC128M
Click Apply.

In the next page enter

Click on Administration Tab


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




Friday, 3 February 2017

utl file creation for inventory item in oracle apps R12

utl file creation for inventory item in oracle apps R12

CREATE OR REPLACE procedure GE_INV_Out_BAL(Errbuf OUT varchar2,
  Retcode ouT varchar2,
f_id in number,
t_id in varchar2
--,dir_name varchar2
) as
cursor c1 is select
msi.segment1 item,
msi.inventory_item_id itemid,
msi.description itemdesc,
msi.primary_uom_code uom,
ood.organization_name name,
ood.organization_id id,
mc . segment1||','||mc.segment2 category
from
mtl_system_items_b msi,
org_organization_definitions ood,
mtl_item_categories mic,
mtl_categories mc
where
msi.organization_id = ood.organization_id
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id
and mic.category_id = mc.category_id
and msi.purchasing_item_flag = 'Y'
--and msi.inventory_item_id=63
and ood.organization_name='Vision Operations'
and msi.organization_id between f_id and t_id;

x_id utl_file.file_type;

l_count number(5) default 0;

path varchar2(1000);

--path V$PARAMETER.value%type;

begin
x_id:=utl_file.fopen('Select value into path from V$PARAMETER Where NAME like 'user_dump_dest'','invoutdata10.txt','W');
--select * from v$parameter where name like '%utl_file%'
for x1 in c1 loop
l_count:=l_count+1;
utl_file.put_line( x_id,'"'||
x1.item ||'"-"'||
x1.itemid ||'"-"'||
x1.itemdesc||'"-"'||
x1.uom ||'"-"'||
x1.name ||'"-"'||
x1.id ||'"-"'||
x1.category||'"' );
end loop;
utl_file.fclose(x_id);
Fnd_file.Put_line(Fnd_file.output,'No of Records transferred to the data file :'||l_count);
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted User name '||Fnd_Profile.Value('USERNAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submitted Responsibility name '||Fnd_profile.value('RESP_NAME'));
Fnd_File.Put_line(fnd_File.Output,' ');
Fnd_File.Put_line(fnd_File.Output,'Submission Date :'|| SYSDATE);
Exception
WHEN utl_file.invalid_operation THEN
fnd_file.put_line(fnd_File.log,'invalid operation');
utl_file.fclose_all;
WHEN utl_file.invalid_path THEN
fnd_file.put_line(fnd_File.log,'invalid path');
utl_file.fclose_all;
WHEN utl_file.invalid_mode THEN
fnd_file.put_line(fnd_File.log,'invalid mode');
utl_file.fclose_all;
WHEN utl_file.invalid_filehandle THEN
fnd_file.put_line(fnd_File.log,'invalid filehandle');
utl_file.fclose_all;
WHEN utl_file.read_error THEN
fnd_file.put_line(fnd_File.log,'read error');
utl_file.fclose_all;
WHEN utl_file.internal_error THEN
fnd_file.put_line(fnd_File.log,'internal error');
utl_file.fclose_all;
WHEN OTHERS THEN
fnd_file.put_line(fnd_File.log,'other error');
utl_file.fclose_all;
End GE_INV_Out_BAL;

Thursday, 19 January 2017

Open PO's to display the information regarding the PO in oracle apps R12

Open PO's to display the information regarding the PO like Amount Billed,Amount Ordered in Shipment Level.

select   
(l.quantity*l.unit_price) "Amount Ordered", 
(select sum(aid.amount) 
 from ap_invoices_all aia,
      ap_invoice_distributions_all aid
where aia.invoice_id = aid.invoice_id
and   aid.po_distribution_id = d.po_distribution_id)"Amount Billed",
(select sum(aip.amount )
from AP_INVOICE_PAYMENTS_all aip ,
     ap_invoice_distributions_all adi
where aip.invoice_id=adi.invoice_id
and   adi.po_distribution_id= d.po_distribution_id)"Amount Received",

gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5 "Charge account",
ll.ship_to_location_id "Ship to Location",
l.quantity            "quantity",
ll.quantity          "Ordered Quantity",
ll.quantity_Billed     "Billed Quantity",
ll.quantity_received  "Received Quantity",
h.segment1,
d.po_distribution_id
from   
po.po_headers_all h,   
po.po_lines_all l,   
po.po_line_locations_all ll,   
po.po_distributions_all d,
gl_code_combinations   gcc
where h.po_header_id = l.po_header_id   
and  gcc.code_combination_id=d.code_combination_id
and ll.po_line_id = l.po_Line_id   
and ll.line_location_id = d.line_location_id  
and h.segment1='1505'
and h.closed_date is null ;

Friday, 14 October 2016

11i to R12 Replaced Tables in Oracle apps

Following are the join conditions. Use these to 
complete your query. 

SO_HEADERS_ALL -- OE_ORDER_HEADERS_ALL 
SO_LINES_ALL -- OE_ORDER_LINES_ALL 
so_picking_lines_all -- WSH_DELIVERY_DETAILS 
so_picking_line_details -- wsh_delivery_assignments 


WSH_NEW_DELIVERIES.DELIVERY_IDWSH_DELIVERY_ASS IGNMENTS.DELIVERY_ID 

WSH_DELIVERY_DETAILS.DELIVERY_DETAIL_ID WSH_DELIVERY_ASSIGNMENTS.DELIVERY_DETAIL_ID 

WSH_DELIVERY_DETAILS.SOURCE_LINE_ID OE_ORDER_LINES_ALL.LINE_ID 

=================================================================================

RA_CUSTOMERS
The table below lists the corresponding HZ table and column for various columns in the current RA_CUSTOMERS view

Column in RA_CUSTOMERS
Corresponding Table
Column
customer_name
hz_parties
party_name,
customer_id
hz_cust_accounts
cust_account_id
customer_number
hz_cust_accounts
account_number
status
hz_cust_accounts
status
Join Conditions:
HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id
RA_ADDRESSES
Column in RA_ADDRESSES
Corresponding Table
Column
address_id
hz_cust_acct_sites_all
cust_acct_site_id
status
hz_cust_acct_sites_all
status
address1
hz_locations
address1
address2
hz_locations
address2
address3
hz_locations
address3
address4
hz_locations
address4
city
hz_locations
city
state
hz_locations
state
postal_code
hz_locations
postal_code
county
hz_locations
county
country
hz_locations
country
language
hz_locations
language
Source Tables:  HZ_PARTY_SITES, HZ_LOCATIONS, HZ_CUST_ACCT_SITES_ALL
Join Conditions:
HZ_CUST_ACCT_SITES_ALL.party_site_id = HZ_PARTY_SITES.party_site_id
HZ_LOCATIONS.location_id = HZ_PARTY_SITES.location_id
RA_SITE_USES
The table below lists the corresponding HZ table and column for various columns in the current RA_SITE_USES view
Column in RA_SITE_USES
Corresponding Table
Column
site_use_id
hz_cust_site_uses
site_use_id
site_use_code
hz_cust_site_uses
site_use_code
status
hz_cust_site_uses
status
address_id
hz_cust_site_uses
cust_acct_site_id

RA_CONTACTS
Column in RA_CONTACTS
Corresponding Table
Column
contact_id
hz_cust_account_roles
cust_account_role_id
status
hz_cust_account_roles
status
customer_id
hz_cust_account_roles
cust_account_id
address_id
hz_cust_account_roles
cust_acct_site_id
first_name
hz_parties
person_first_name
last_name
hz_parties
person_last_name
Source Tables: HZ_CUST_ACCOUNT_ROLES, HZ_PARTY_RELATIONSHIPS, HZ_PARTIES
Join Conditions:
HZ_CUST_ACCOUNT_ROLES.party_id = HZ_PARTY_RELATIONSHIPS.party_id
HZ_CUST_ACCOUNT_ROLES.role_type = 'CONTACT'
HZ_PARTIES.party_id = HZ_PARTY_RELATIONSHIPS.subject_id
RA_CONTACT_ROLES
Column in RA_CONTACT_ROLES
Corresponding Table
Column
contact_id
hz_role_responsibility
cust_account_role_id
usage_code
hz_role_responsibility
responsibility_type
primary_flag
hz_role_responsibility
primary_flag

11i Table
R12 Change
ra_addresses_all
SELECT acct_site.cust_account_id customer_id,     acct_site.cust_acct_site_id address_id
FROM hz_party_sites party_site,
                 hz_loc_assignments loc_assign,
                 hz_locations loc,
                 hz_cust_acct_sites_all acct_site
           WHERE acct_site.party_site_id = party_site.party_site_id
             AND loc.location_id = party_site.location_id
             AND loc.location_id = loc_assign.location_id
             AND NVL (acct_site.org_id, -99) = NVL (loc_assign.org_id, -99)
ra_site_uses_all
SELECT site_use_id, LOCATION, attribute1
 FROM hz_cust_site_uses_all
ra_customers
SELECT cust_acct.cust_account_id customer_id,
                 SUBSTRB (party.party_name, 1, 50) customer_name,
                 cust_acct.account_number customer_number
            FROM hz_parties party, hz_cust_accounts cust_acct
           WHERE cust_acct.party_id = party.party_id



Suppliers:

New R12 tables  -> Old 11i Tables
AP_SUPPLIERS - replaces PO_VENDORS
AP_SUPPLIER_SITES_ALL- replaces PO_VENDOR_SITES_ALL

Additional supplier related tables in IBY (Payments) and HZ (TCA):
IBY_EXTERNAL_PAYEES_ALL - stores Payee(supplier) information.
HZ_PARTIES - Party data for the suppliers.
HZ_PARTY_SITES - Party site data for the supplier sites.

Invoices:

Additional table in R12: AP_INVOICE_LINES_ALL
Allocations - AP_CHRG_ALLOCATIONS_ALL is obsolete in R12

Taxes:

Functionality provided by E-Business Tax
New tables in R12
ZX_LINES - Detailed Tax lines for the invoice (trx_id = invoice_id)
ZX_LINES_SUMMARY - Summary tax lines for the invoice (trx_id = invoice_id)
ZX_REC_NREC_DIST  - Tax distributions for the invoice (trx_id = invoice_id)
ZX_LINES_DET_FACTORS - Tax determination factors for the invoice (trx_id = invoice_id)

Payments:

Functionality moved to central Payments (IBY)
New IBY tables in R12:
IBY_PAY_SERVICE_REQUESTS  - Payment Process Request information

Accounting:

Functionality moved to SubLedger Accounting (SLA)
New R12 tables:
XLA_EVENTS -> replaces AP_ACOCUNTING_EVENTS_ALL 
XLA_AE_HEADERS -> replaces AP_AE_HEADERS_ALL
XLA_AE_LINES-> replaces AP_AE_LINES_ALL
XLA_DISTRIBUTION_LINKS

Trial Balance:

New R12 Table
XLA_TRIAL_BALANCES
AP_LIABILITY_BALANCE-> not used in new R12 transactions
AP_TRIAL_BALANCE -> not used in new R12 transactions

Bank Accounts:

Functionality moved to Cash Management.
CE_BANK_ACCOUNTS -> replaces AP_BANK_ACCOUNTS_ALL
CE_BANK_ACCT_USES_ALL  -> replaces AP_BANK_ACCOUNT_USES_ALL

CE_PAYMENT_DOCUMENTS -> AP_CHECK_STOCKS_ALL

Wednesday, 31 August 2016

PROCEDURE TO PRINT LOG INFORMATION FROM BACKEND AND FRONT END IN ORACLE APPS

PROCEDURE TO PRINT THE LOG INFORMATION FROM BACKEND AND FRONT END

PROCEDURE print_log(p_message IN VARCHAR2)
IS

BEGIN
    fnd_file.put_line(fnd_file.LOG,p_message);
    dbms_output.put_line(p_message);
END print_log;