Showing posts with label Oracle apps. Show all posts
Showing posts with label Oracle apps. Show all posts

Tuesday, 2 May 2017

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

ACTIVE USERS EMPLOYEE AND THEIR RESPONSIBILITIES IN ORACLE APPS

SELECT fu.user_name user_name
      ,fu.description user_description
      ,fu.email_address user_email
      ,ppf.full_name employee_name
      ,hou.name business_group
      ,fr.responsibility_name resp_name
FROM apps.fnd_user fu
    ,apps.per_all_people_f ppf
    ,apps.hr_all_organization_units hou
    ,apps.fnd_user_resp_groups_all fur
    ,apps.fnd_responsibility_tl fr
WHERE ppf.person_id = fu.employee_id
AND hou.organization_id = ppf.business_group_id
AND fu.user_id = fur.user_id
AND NVL (fur.end_date, SYSDATE + 1) > SYSDATE
AND fur.responsibility_id = fr.responsibility_id
--AND fr.responsibility_name LIKE 'Shipping User'
AND fr.LANGUAGE = 'US'
ORDER BY fu.user_name
        ,fr.responsibility_name

Thursday, 20 April 2017

CUSTOMER ITEM INTERFACE ORACLE APPS

CUSTOMER ITEM INTERFACE ORACLE APPS CODE:

PACKAGE SPEC:
CREATE OR REPLACE PACKAGE xxrs_cust_item_int_pkg
AS
PROCEDURE main(retcode OUT NUMBER
              ,errbuff OUT VARCHAR2
 ,p_cust_no IN NUMBER);
PROCEDURE cust_item_iface(p_cust_no IN NUMBER);
PROCEDURE cust_item_iface_xref(p_cust_no IN NUMBER);
END xxrs_cust_item_int_pkg;

PACKAGE BODY:

create or replace PACKAGE BODY xxrs_cust_item_int_pkg
AS
 PROCEDURE main( retcode   OUT NUMBER
                ,errbuff   OUT VARCHAR2
         ,p_cust_no  IN NUMBER
)
 AS
  --local variables
  l_req_id            NUMBER;
  lc_phase            VARCHAR2(50);
  lc_status           VARCHAR2(50);
  lc_dev_phase        VARCHAR2(50);
  lc_dev_status       VARCHAR2(50);
  lc_message          VARCHAR2(50);
  l_req_return_status BOOLEAN;
 BEGIN

   l_req_id := fnd_request.submit_request( application   => 'XXRS'
                                          ,program       => 'XXRSCUITEL'
                                          ,description   => ''
                                          ,start_time    => SYSDATE
                                          ,sub_request   => FALSE
                       );

      fnd_file.put_line(fnd_file.log,'Request Id for loading Customer Items to temp table'||l_req_id);

      COMMIT;

     l_req_return_status :=fnd_concurrent.wait_for_request (request_id      => l_req_id
                                                           ,INTERVAL        => 5
                                                           ,max_wait        => 1
                                                           ,phase           => lc_phase
                                                           ,STATUS          => lc_status
                                                           ,dev_phase       => lc_dev_phase
                                                           ,dev_status      => lc_dev_status
                                                           ,message         => lc_message
                                                          );
        xxrs_cust_item_int_pkg.cust_item_iface(p_cust_no);
        xxrs_cust_item_int_pkg.cust_item_iface_xref(p_cust_no);
 EXCEPTION
  WHEN OTHERS THEN
   fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
   retcode :=2;
 END main;

 PROCEDURE cust_item_iface(p_cust_no IN NUMBER
                          )
 AS
 --local variables
  v_user_id           NUMBER;
  v_acct_id           NUMBER :=p_cust_no;
  l_req_id            NUMBER;
  lc_phase            VARCHAR2(50);
  lc_status           VARCHAR2(50);
  lc_dev_phase        VARCHAR2(50);
  lc_dev_status       VARCHAR2(50);
  lc_message          VARCHAR2(50);
  l_req_return_status BOOLEAN;
  -- cursor is fetching the data from staging table to validate
 CURSOR c1 IS
 SELECT vendor_item
       ,medline_item
 FROM xx_cus_item_temp;

 BEGIN
  fnd_file.put_line(fnd_file.log,'v_acct_id '||v_acct_id);
   select user_id
   into v_user_id
   from fnd_user
   where user_name='ANONYMOUS';

   FOR i IN c1
   LOOP
   INSERT INTO mtl_ci_interface(process_flag
                                ,process_mode
                                ,lock_flag
                                ,last_updated_by
                                ,last_update_date
                                ,last_update_login
                                ,created_by
                                ,creation_date
                                ,transaction_type
                                ,customer_id
                                ,address_id
                                ,customer_item_number
                                ,customer_item_desc
                                ,item_definition_level
                                ,commodity_code_id
                                ,inactive_flag
                                )
                       VALUES (1              
                              ,1              
                              ,'N'            
                              ,v_user_id      
                              ,SYSDATE        
                              ,v_user_id      
                              ,v_user_id      
                              ,SYSDATE
                              ,'CREATE'
                              ,v_acct_id
                              ,NULL
                              ,i.medline_item
                              ,NULL
                              ,'1'
                              ,1
                              ,2
                              );
                       COMMIT;
   END LOOP;
             
l_req_id := fnd_request.submit_request(application    => 'INV'
                                           ,program       => 'INVCIINT'
                                           ,description   => ''
                                           ,start_time    => SYSDATE
                                           ,sub_request   => FALSE
                                           ,argument1  => 'N'
                                           ,argument2  => 'Y'
  );
     fnd_file.put_line(fnd_file.log,'request id for import customer items '||l_req_id);

     COMMIT;
      l_req_return_status :=fnd_concurrent.wait_for_request (request_id      => l_req_id
                                                            ,INTERVAL        => 5
                                                            ,max_wait        => 1
                                                            ,phase           => lc_phase
                                                            ,STATUS          => lc_status
                                                            ,dev_phase       => lc_dev_phase
                                                            ,dev_status      => lc_dev_status
                                                            ,message         => lc_message
                                                            );
 END cust_item_iface;
 

 PROCEDURE cust_item_iface_xref(p_cust_no IN NUMBER
                               )
 AS

  v_user_id    NUMBER;
  V_ITEM_ID    NUMBER;
  l_req_id     NUMBER;
  V_ACCT_ID    NUMBER :=p_cust_no;

   -- cursor is fetching the data from staging table to validate
  CURSOR c1 IS
  SELECT vendor_item
        ,medline_item
  FROM xx_cus_item_temp;

  BEGIN
    select user_id
    into v_user_id
    from fnd_user
    where user_name='ANONYMOUS';


   FOR i IN c1
   LOOP
     BEGIN
      SELECT DISTINCT inventory_item_id
      INTO v_item_id
      FROM mtl_system_items_b
      WHERE segment1=i.vendor_item;--'040-00';
     EXCEPTION
      WHEN no_data_found THEN
       v_item_id:=NULL;
       fnd_file.put_line(fnd_file.log,'for vendor_item'||i.vendor_item ||'inventory_item_id doesnot exists');
     END;

             INSERT INTO mtl_ci_xrefs_interface (process_flag
                                                ,process_mode
                                                ,lock_flag
                                                ,last_update_date
                                                ,last_updated_by
                                                ,creation_date
                                                ,created_by
                                                ,last_update_login
                                                ,transaction_type
                                                ,customer_id
                                                ,address_id
                                                ,customer_item_number
                                                ,item_definition_level
                                                ,inventory_item_id
                                                ,master_organization_id
                                                ,preference_number
                                                ,inactive_flag
                                                )
                                        VALUES (1
                                               ,1
                                               ,'N'
                                               ,SYSDATE
                                               ,v_user_id
                                               ,SYSDATE
                                               ,v_user_id
                                               ,v_user_id
                                               ,'CREATE'
                                               ,v_acct_id
                                               ,NULL
                                               ,i.medline_item
                                               ,'1'
                                               ,v_item_id
                                               ,83
                                               ,1
                                               ,2
                                              );
                                      COMMIT;

  END LOOP;

l_req_id := fnd_request.submit_request(application   => 'INV'
                                          ,program       => 'INVCIINTX'
                                          ,description   => ''
                                          ,start_time    => SYSDATE
                                          ,sub_request   => FALSE
                                          ,argument1   => 'N'
                                          ,argument2 => 'Y'
 );
    fnd_file.put_line(fnd_file.log,'request id for import customer item cross references'||l_req_id);
COMMIT;
 END cust_item_iface_xref;


end xxrs_cust_item_int_pkg;

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, 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