Resolving Stuck IN PROCESS of Purchase Order and Purchase Order Requisition

First the WF_ITEM_KEY, WF_ITEM_TYPE  of the IN PROCESS status PO need to be identified. Following sql statement can be used to get the details based on the PO No (segment1) and ORG_ID;
SELECT         wf_item_key, wf_item_type, authorization_status,
org_id, last_update_date, segment1 po_no
FROM             po_headers_all
WHERE          authorization_status = ‘IN PROCESS’
AND                org_id = 337
AND                segment1 in (‘po_no’,’po_no2′)
ORDER BY    last_update_date desc
.
Similarly  WF_ITEM_KEY, WF_ITEM_TYPE  of the IN PROCESS status PO REQ can be  identified. Using the following sql statement for the PO REQ No (segment1) and ORG_ID;
–To select all the inProcess for PO REQ
SELECT         wf_item_type , wf_item_key, authorization_status,
org_id , last_update_date, segment1 poreq_no
FROM             po_requisition_headers_all
WHERE         authorization_status = ‘IN PROCESS’
AND               org_id = 247
AND               segment1 in (‘po_req_no’,’po_req_no2′)
ORDER BY   last_update_date DESC
.
After getting the WF_ITEM_KEY and WF_ITEM_TYPE , you need to run the following process in the APPS SESSION LOGIN to remove stuck in process status;
For PO
begin
—  wf_engine.startprocess( ‘WF_ITEM_TYPE’ , ‘WF_ITEM_KEY’ );
wf_engine.startprocess( ‘POAPPRV’  ,  ‘123001-244471’ );
end;
For PO REQ
begin
—  wf_engine.startprocess( ‘WF_ITEM_TYPE’ , ‘WF_ITEM_KEY’ );
wf_engine.startprocess(‘REQAPPRV’  ,  ‘123001-244471’ );
end;
 

How to change PO Default Rate Type or PO System Parameters in oracle apps R12

Purchasing Super User => SETUP==>Organizations==>Purchasing Options ==> Document Default

SELECT default_rate_type FROM po_system_parameters;

Use the Default Options region to define the defaults you can later use to speed up data entry and enforce system wide requirements in many Oracle Purchasing forms.
*These options must be defined separately for each Operating Unit.
*By assigning a value at the responsibility level when that responsibility has been tied to an operating unit,it is possible to have different values for different operating units.
Requisition Import Group-By:
Use the ReqImport process to import requisitions from other Oracle or non-Oracle systems. Sources for requisitions can include Work In Process, Master Scheduling/MRP, and Inventory, as well as custom systems. Requisition Import creates a requisition line and a requisition distribution for each row it finds in the interface table. It then groups these lines on requisitions according to parameters defined below.
All: group all requisition lines on one requisition
Buyer: group lines for each buyer name on a separate requisition.
Category: group lines for each purchasing category on a separate requisition.
Item: group lines for each item on a separate requisition
Location: group lines for each location on a separate requisition
Vendor: group lines for each vendor on a separate requisition.
Rate Type:
Select the currency Rate Type that defaults on requisitions, purchase
orders, RFQs, and quotations. Use conversion rate types to automatically
assign a rate when you convert foreign currency journal amounts to functional
currency equivalents. You enter daily conversion rates for specific
combinations of foreign currency, date, and conversion rate type. If the
Rate Type is User, you can override this default for each document line. If
either your functional currency (defined in your set of books) or your
transaction currency (the currency you enter in a purchasing document window)
is Euro (the European Monetary Unit currency), and the other is another
European currency, Purchasing defaults in the appropriate conversion Rate Type,
Rate, and Rate Date.
Spot: An exchange rate which you enter to perform conversion based on the
rate on a specific date. It applies to the immediate delivery of a currency.
Corporate: An exchange rate you define to standardize rates for your
company. This rate is generally a standard market rate determined by
senior financial management for use throughout the organization.
User: An exchange rate you specify when you enter a foreign currency
journal entry.
EMU Fixed: An exchange rate General Ledger provides automatically when
you enter journals (after the EMU effective starting date) using a foreign
currency that has a fixed relationship with the Euro.
Minimum Release Amount:
This is the minimum release amount which defaults on blanket, contract, and
planned purchase orders. This amount is in your functional currency.
Taxable:
Check the Taxable box to set the default taxable status to Yes.
This default is for new items and for purchase order shipments without an item number. You can override the taxable status for each item or shipment.
The taxable status is printed on purchase orders
Price Break Type:
The type selected here will default on blanket and planned purchase orders.
Cumulative: price breaks apply to the cumulative quantity on all released shipments for the item
Non-cumulative: price breaks apply to quantities on individual released shipments for the item.
Price Type:
The type selected here will default on purchase orders. Use the
Lookup Codes window to define price types.
These are:
Cost Plus Fee
Cost Plus Percentage
Fixed
Indexed
Variable
Quotation Warning Delay (Days):
This option sets the number of days of warning before a quotation expires. When the limit is reached, you receive the following message in the Alert Notifications window: "Quotations active or approaching expiration".
RFQ Required:
Clicking this box causes an RFQ to be required before an item can be autocreated onto a P.O.
Receipt Close % Tolerance:
Purchasing automatically closes a shipment for receiving if it is within the receiving close tolerance at the receiving close point. It can be overridden for specific items and orders. If you are not going to receive the goods then on the purchase order set the receipt close tolerance to 100%. When it is set to 100% then when the PO is approved the line will be closed for receiving. Then when you match the invoice to the PO the status will change to Closed.
Invoice Close % Tolerance:
Purchasing automatically closes a shipment for invoicing if it is within the vendor invoice close tolerance at time of invoice matching. It can be overridden for specific items and orders.
CAUTION: If you set this to 100%, the PO will automatically close for
invoicing upon approval.
Line Type:
This is the default Line Type for requisition, RFQ, quotation, and purchase order lines. When you create any of these documents, the line type is part of your item information. You can override the line type for each document line.
Invoice Matching:
Select one of the following default Invoice Match options:
Two-Way - Purchase Order and invoice quantities must match within
tolerance. Often used for services where no receiver is
generated.
Three-Way - Purchase Order, receipt and invoice quantities must match
within tolerance. Must enter receiver.
Four-Way - Purchase Order, receipt, inspection and invoice quantities
must match within tolerance. Must enter receiver and
inspection quantities

Query for to get Tax Details In oracle apps R12

SELECT hou.organization_id            org_id,

       led.name                       ledger,

       hou.name                       operating_unit,

       --

       zxr.tax_regime_code            tax_regime_code,

       zxr.tax                        tax_code,

       zxr.tax_status_code            tax_status_code,

       zxr.tax_rate_code              tax_rate_code,

       zxr.tax_jurisdiction_code      tax_jurisdiction_code,

       --

       zxr.rate_type_code             rate_type_code,

       zxr.percentage_rate            percentage_rate,

       zxr.effective_from             rate_effective_from,

       zxr.effective_to               rate_effective_to,

       --

       acc.tax_account_ccid           tax_account_ccid,

       gcc.concatenated_segments      tax_account

  FROM

       zx_rates_vl                  zxr,

       zx_accounts                  acc,

       hr_operating_units           hou,

       gl_ledgers                   led,

       xxrl_gl_code_combinations_v  gcc

 WHERE

       1=1

   --

   -- AND zxr.tax_regime_code = 'UK VAT'

   -- AND zxr.tax_rate_code = 'UK_AR_DOM'

   --

   AND acc.tax_account_entity_code = 'RATES'

   AND zxr.active_flag = 'Y'

   AND TRUNC (SYSDATE) BETWEEN

          TRUNC (zxr.effective_from) AND

          NVL (TRUNC (zxr.effective_to), TRUNC (SYSDATE) + 1)

   --

   AND led.ledger_id = hou.set_of_books_id

   AND gcc.code_combination_id = acc.tax_account_ccid

   AND hou.organization_id = acc.internal_organization_id

   AND acc.tax_account_entity_id = zxr.tax_rate_id

   --

   ;

Query for to get Standard Concurrent Programs with arguments and valuesets in oracle apps R12

SELECT FCPL.USER_CONCURRENT_PROGRAM_NAME USER_CONCURRENT_PROGRAM_NAME,
       FDFCUV.COLUMN_SEQ_NUM COLUMN_SEQ_NUM,
       FDFCUV.END_USER_COLUMN_NAME PARAMETER_NAME,
       FDFCUV.FORM_LEFT_PROMPT PROMPT, FDFCUV.ENABLED_FLAG ENABLED_FLAG,
       FDFCUV.REQUIRED_FLAG REQUIRED_FLAG,
       FDFCUV.DISPLAY_FLAG DISPLAY_FLAG,
       FFVS.FLEX_VALUE_SET_NAME FLEX_VALUE_SET_NAME
  FROM fnd_concurrent_programs fcp,
       fnd_concurrent_programs_tl fcpl,
       fnd_descr_flex_col_usage_vl fdfcuv,
       fnd_flex_value_sets ffvs,
       fnd_lookup_values flv
 WHERE 1 = 1
   AND fcp.concurrent_program_id = fcpl.concurrent_program_id
   AND fdfcuv.descriptive_flexfield_name =
                                       '$SRS$.' || fcp.concurrent_program_name
   AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
   AND flv.lookup_code(+) = fdfcuv.default_type
   AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
   AND fcpl.LANGUAGE = USERENV ('LANG')
   AND flv.LANGUAGE(+) =
          USERENV
             ('LANG')
--AND    fcpl.user_concurrent_program_name LIKE 'XXENEUR%'
AND ffvs.flex_value_set_name NOT LIKE 'XX%'
And fcp.CONCURRENT_PROGRAM_NAME in(
'APXAAREP',
'APXCRRCR'
))

how to debug plsql blok

by using SQLERRM we can debug like below


EXCEPTION
                WHEN OTHERS THEN
                           FND_FILE.PUT_LINE (FND_FILE.LOG, 'Error in XXATU_BLACKLINE_EXTRACT');
                           v_error := SUBSTR(SQLERRM,1,240);
                           FND_FILE.PUT_LINE (FND_FILE.LOG, v_error);

Query for to get Standard Customization objects list in Oracle apps R12

Declare
v_user_cp1 varchar2(200);
v_cp1   varchar2(200);
v_user_cp varchar2(200);
v_cp   varchar2(200);
v_cp_method varchar2(200);
Cursor Cur_name is
select distinct  cc.concurrent_program_name,tl.user_concurrent_program_name
from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
where tl.application_id = cc.application_id
and tl.concurrent_program_id = cc.concurrent_program_id
and cc.execution_method_code in ('P','I')
and cc.concurrent_program_name not like 'XX%'
 And tl.LANGUAGE ='US';
cursor cur1 (cpname varchar2) is
    select distinct tl.user_concurrent_program_name, cc.concurrent_program_name,
    decode(cc.execution_method_code,
'P', 'Oracle Reports',
'I' ,'PL/SQL Stored Procedure'
) Execution_Method
    into v_user_cp,v_cp ,v_cp_method
    from fnd_concurrent_programs_tl tl, fnd_concurrent_programs cc
    where tl.application_id = cc.application_id
    and tl.concurrent_program_id = cc.concurrent_program_id
    and cc.execution_method_code in ('P','I')
    And cc.concurrent_program_name like 'XX%'
    And tl.LANGUAGE ='US'
    And cc.concurrent_program_name <> cpname
    And REGEXP_LIKE  (cc.concurrent_program_name, cpname);
begin
Open Cur_name;
 LOOP
  Fetch Cur_name into v_user_cp1,v_cp1;
  exit when Cur_name%notfound;
    open cur1(v_user_cp1);
    loop
    fetch cur1 into v_user_cp,v_cp,v_cp_method;
    exit when cur1%notfound;
    dbms_output.put_line(v_cp||' | '||v_user_cp||' | '||v_user_cp1||' | '||v_cp1||' | '||v_cp_method);
    end loop;
    close cur1;
 END LOOP;
  Close Cur_name;
end;

It will tack to time to execute query 15 to 20 mints.

how to set environment variable in putty

log in on putty
enter . /sharedapps/<instance Name>/EBSapps.env
you can enter 'R' for run mode

Customers in TCA Architecture in Release 12

Customers in TCA Architecture in Release 12

Scope and Application

This note is intended for anyone who must  upgrade queries or customizations from 11i which refer to any of the following views or anyone who requires an understanding of the TCA architecture  to create new queries or customizations:

• RA_CUSTOMERS
• RA_ADDRESSES
• RA_SITE_USES
• RA_CONTACTS
• RA_CONTACT_ROLES

Projects Uptake of the TCA Architecture in Release 12

1. The TCA Architecture

The following sections describe the individual views that are being replaced by the TCA (HZ) tables along with a mapping of view columns to HZ table columns and the query conditions that should be used to retrieve them

2.1 RA_CUSTOMERS

• In the 11i architecture RA_CUSTOMERS is a synonym in the APPS schema which points to the view RA_HCUSTOMERS. 
• The source file for RA_HCUSTOMERS in 11i is archz.odf.
• 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	substrb(party_name,1,50)
customer_id	hz_cust_accounts	cust_account_id
customer_number	hz_cust_accounts	account_number
status	hz_cust_accounts	status

• Source Tables:  HZ_PARTIES, HZ_CUST_ACCOUNTS
• Join Conditions:

HZ_CUST_ACCOUNTS..Party_Id = HZ_PARTIES.Party_Id

2.2 RA_ADDRESSES

• In the 11i architecture RA_ADDRESSES is a multi-org striped view based on RA_ADDRESSES_ALL.  RA_ADDRESSES_ALL is a synonym for the view RA_ADDRESSES_MORG.
• The source file for view RA_ADDRESSES_MORG in 11i is archz.odf.
• The table below lists the corresponding HZ table and column for various columns in the current RA_ADDRESSES view:

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

2.3 RA_SITE_USES

• In the 11i architecture RA_SITE_USES is a multi-org striped view based on RA_SITE_USES_ALL. RA_SITE_USES_ALL is a synonym for the view RA_SITE_USES_MORG.
• The source file for view RA_SITE_USES_MORG in 11i is archz.odf
•  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

• Source Tables:  HZ_CUST_SITE_USES
• Join Conditions:  none

2.4 RA_CONTACTS

• In the 11i architecture RA_CONTACTS is a synonym for the view RA_HCONTACTS
• The source file for view RA_HCONTACTS in 11i is archz.odf
• The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACTS view:

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	substrb(person_first_name,1,40)
last_name	hz_parties	substrb(person_last_name,1,50)

• 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

2.5 RA_CONTACT_ROLES

• In the 11i architecture RA_CONTACT_ROLES is a synonym for the view RA_HCONTACT_ROLES
• The source file for view RA_HCONTACT_ROLES in 11i is archz.odf
• The table below lists the corresponding HZ table and column for various columns in the current RA_CONTACT_ROLES view:

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

• Source Tables: HZ_ROLE_RESPONSIBILITY
• Join Conditions:  none.

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

RA_ADDRESSES_ALL is obsolete table in Release 11.5.10 and Release 12. You can get the customer address information from the following tables in Release 12.

HZ_CUST_ACCT_SITES_ALL
HZ_PARTY_SITES
HZ_LOCATIONS

Here is the Linking information:

- HZ_CUST_ACCT_SITES_ALL & HZ_PARTY_SITES - Link column PARTY_SITE_ID
- HZ_PARTY_SITES & HZ_LOCATIONS - Link column is LOCATION_ID.

Please share Your Comments