Contract line -> Inventory Item

Useful query which will fetch your inventory items from contract lines.

Service Products:

SELECT kl.line_number,
       ks.name,
       itm.segment1,
       itm.segment2
FROM okc_k_headers_v kh,
     okc_k_lines_v kl,
     okc_k_items ki,
     okc_line_styles_v ks,
     mtl_system_items_b itm
WHERE kh.contract_number = 'contract number'
AND   kh.contract_number_modifier IS NULL
AND   kh.id = kl.dnz_chr_id
AND   kh.id = ki.dnz_chr_id
AND   kl.id = ki.cle_id
AND   kl.lse_id = ks.id
AND   ki.jtot_object1_code IN ('OKX_SERVICE')
AND   kh.inv_organization_id = itm.organization_id
AND   ki.object1_id2 = itm.organization_id
AND   ki.object1_id1 = itm.inventory_item_id
ORDER BY kl.line_number;

Covered Products:

SELECT kl.line_number,
       ks.NAME,
       i.segment1,
       i.segment2
FROM okc_k_headers_b kh,
     okc_k_lines_b kl,
     okc_k_items ki,
     okc_line_styles_v ks,
     csi_item_instances c,
     mtl_system_items_b i
WHERE kh.contract_number = 'contract number'
AND   kh.contract_number_modifier IS NULL
AND   kh.id = kl.dnz_chr_id
AND   kh.id = ki.dnz_chr_id
AND   kl.id = ki.cle_id
AND   kl.lse_id = ks.id
AND   ki.jtot_object1_code IN ('OKX_CUSTPROD')
AND   c.last_vld_organization_id = i.organization_id
AND   ki.object1_id1 = c.instance_id
AND   c.inventory_item_id = i.inventory_item_id
ORDER BY kl.line_number;

References

• Note 467334.1: 11i: How to Query the Service Contracts Tables for Header, Line, Subline and Billing Information in 11.5.10+ 
• OTN Forum

List Forms Personalization and XX-Forms

In a R12 upgrade project it can be a good idea to read some information from the database regarding the customizations.

List Forms Personalization

SELECT ft.user_function_name, 
       fo.form_name,
       c.* 
FROM fnd_form_custom_rules c,
     fnd_form_functions f,
     fnd_form_functions_tl ft,
     fnd_form fo
WHERE c.function_name = f.function_name
AND   f.function_id = ft.function_id
AND   f.form_id = fo.form_id
ORDER BY 2

List Custom Forms

SELECT ft.user_form_name,
       f.* 
FROM fnd_form f,
     fnd_form_tl ft
WHERE f.form_id = ft.form_id
AND   form_name LIKE 'XX%'
ORDER BY 4

Set completion status of Concurrent Request (Host)

If you have a concurrent request executable of type host (shell script on unix) and you want to set the status of the request to Warning or Error upon completion you need to use a small SQL-script to accomplish this.

Warning

DECLARE
  l_result     BOOLEAN;
  l_session_id NUMBER;
  l_reqid      NUMBER;
BEGIN
  l_reqid := '&&1';
  dbms_output.put_line('l_reqid '||l_reqid);
  fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, 
                        null, l_reqid, null,null,null,null,null,null,-1);
  l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.');
  COMMIT;
END;
/
exit 0;

Error

DECLARE
  l_result     BOOLEAN;
  l_session_id NUMBER;
  l_reqid      NUMBER;
BEGIN
  l_reqid := '&&1';
  dbms_output.put_line('l_reqid '||l_reqid);
  fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, 
                        null, l_reqid, null,null,null,null,null,null,-1);
  l_result := fnd_concurrent.set_completion_status('ERROR','Review log file for details.');
  COMMIT;
END;
/
exit 0;

Call the script from your shell script

sqlplus $unpw @$XX_TOP/sql/your file name.sql "$conc_request_id"

You can of course handle both Warning and Error in the same SQL-file.

Concurrent request output directory

In most cases the output files will be written to $COMMON_TOP/admin/out/<ENV.NAME>.

If you don't find anything there investgate the environment variables $APPLCSF (path), $APPLLOG (folder name) and $APPLOUT (folder name).

I read somewhere that if $APPLCSF is not set the outputs will be written to $<APPLICATION_SHORT_NAME>/<VALUE OF $APPLOUT> e.g $AR_TOP/admin/out.

Internet Exploring crashing when starting EBS (jInitiator)

Internet Exploring is crashing when starting EBS (jInitiator). You have installed jInitiator ver 1.3.1.21 without any problem but when you are trying to start the Forms environment IE is crashing. A workaround can be found here.

I have used this solution succsessfully both with XP and Win7.

List profile options and their values

This select will give you all the values for a specific profile option.

SELECT potl.language,
       potl.user_profile_option_name,
       potl.description,
       po.profile_option_id,
       DECODE(pov.level_id, 10001, 'Site',
                            10002, 'Application',
                            10003, 'Responsibility',
                            10004, 'User',
                            10005, 'Server',
                            10006, 'Org',
                            10007, DECODE(to_char(pov.level_value2), '-1', 'Responsibility',
                                   DECODE(to_char(pov.level_value), '-1', 'Server',
                                   'Server+Resp')),
                            'UnDef') level_set,
       DECODE(to_char(pov.level_id), '10001', '',
                                     '10002', app.application_short_name,
                                     '10003', rsp.responsibility_key,
                                     '10004', usr.user_name,
                                     '10005', svr.node_name,
                                     '10006', org.name,
                                     '10007', DECODE(to_char(pov.level_value2), '-1', 
                                                     rsp.responsibility_key,
                                              DECODE(to_char(pov.level_value), '-1',
                                             (SELECT node_name
                                              FROM fnd_nodes
                                              WHERE node_id = pov.level_value2),
                                             (SELECT node_name
                                              FROM fnd_nodes
                                              WHERE node_id = pov.level_value2)||'-'||
                                                              rsp.responsibility_key)),
                                     'UnDef') context,
       pov.profile_option_value value
FROM fnd_profile_options po,
     fnd_profile_options_tl potl,
     fnd_profile_option_values pov,
     fnd_user usr,
     fnd_application app,
     fnd_responsibility rsp,
     fnd_nodes svr,
     hr_operating_units org
WHERE po.profile_option_id = pov.profile_option_id
AND   po.profile_option_name = potl.profile_option_name
AND   usr.user_id (+) = pov.level_value
AND   app.application_id (+) = pov.level_value
AND   rsp.application_id (+) = pov.level_value_application_id
AND   rsp.responsibility_id (+) = pov.level_value
AND   svr.node_id (+) = pov.level_value
AND   org.organization_id (+) = pov.level_value
AND   po.profile_option_name = profile_option_name
ORDER BY 1, 5, 6

Tested with 11i.

Status of a customer

One of my clients who run the Finance modules (AR, ARP, GL etc), Service Contracts and Field Service in E-Business Suite R12 had some issues with customers who were inactive.
The issue arised when they were creating Service Requests (through API cs_servicerequest_pub.Create_ServiceRequest) and got an error reported by the API that the request could  not be created due to the customer was invalid.

The reason that a customer is invalid can vary but one reason is that some level of the customer is inactive. In AR and the TCA model the customer (party) status can be set in the following tables:

• hz_parties (TCA)
• hz_party_sites (TCA)
• hz_party_site_uses (TCA)
• hz_cust_accounts (TCA-AR)
• hz_cust_acct_sites_all (TCA-AR)
• hz_cust_site_uses_all (TCA-AR)

In my clients case status was Active in all tables except hz_party_site_uses. Below I will explain how it's possible to change the statuses.

Set status in Account Receivables

If you inactivate an Account Site Address/Account Site Details the status will be set to I (Inactive) in hz_party_sites, hz_party_site_uses, hz_cust_acct_sites_all and hz_cust_site_uses_all.

By using the Restore icon on an adress the status will be set to A (Active) in hz_party_sites and hz_cust_acct_sites_all. Note that the purpose level (hz_party_site_uses and hz_cust_site_uses_all) is still Inactive and need to be reactivated manually.

When a Business Purpose is restored the status will be set to A (Active) in hz_cust_site_uses_all and hz_party_site_uses. In hz_party_site_uses a new row is created with status A.

The strange thing is that if we look in Service (Contact Center), we now have one Active and one Inactive usage for Bill To.

The table hz_party_site_uses is version controlled and the row that have status A have object_version_number 1 (not 2 as expected).

To inactivate the account level in AR open the Account (in the Customer form) and change the status.

Set status in Service
To activate/inactivate an account, site or purpose in Service, use responsibility Field Service Manager and open Contact Center, go to tab Addresses. You need to check Include Inactive to show the inactive sites and purposes.

If you inactivate an address the status will be set to I (Inactive) in hz_party_sites, hz_party_site_uses, hz_cust_acct_sites_all and hz_cust_site_uses_all.

If you activate an address the status will be set to A (Active) in hz_party_sites and hz_cust_acct_sites_all. Note that the purpose level (hz_party_site_uses and hz_cust_site_uses_all) is still Inactive and need to be reactivated manually.

To inactivate a account go to tab Accounts.

Set status on party level
If you want to inactivate the Party level (hz_parties) you need to use the responsibility Oracle Customers Data Librarian Superuser and Navigate: Data Quality -> Party Maintenance tab.

The query below can be helpful if you want to check the statuses in the database.

SELECT hp.party_id,
       hp.party_name,
       hp.party_number,
       hps.party_site_id,
       hcas.org_id,
       hpsu.site_use_type,                
       hps.party_site_number,
       hp.status hz_parties,
       hps.status hz_party_sites,
       hpsu.status hz_party_site_uses,                 
       hcsu.site_use_code,                              
       ca.status hz_cust_accounts,
       hcas.status hz_cust_acct_sites_all,                
       hcsu.status hz_cust_site_uses_all
FROM hz_parties hp,
     hz_party_sites hps,
     hz_party_site_uses hpsu,
     hz_cust_accounts ca,
     hz_cust_acct_sites_all hcas,
     hz_cust_site_uses_all hcsu
WHERE hp.party_id = hps.party_id
AND   hp.party_id = ca.party_id
AND   hps.party_site_id = hcas.party_site_id
AND   hcsu.cust_acct_site_id = hcas.cust_acct_site_id
AND   hps.party_site_id = hpsu.party_site_id 
AND   hcsu.site_use_code = hpsu.site_use_type
AND   hp.party_id = party_id
AND   hcas.org_id = org_id;