07 September 2009

ORA-20002: 3207: User 'XXX_does not have access to notification 1234567. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 225 ORA-06512: at line 1

Error :
ORA-20002: 3207: User 'XXX_does not have access to notification 1234567. ORA-06512: at "APPS.WF_ADVANCED_WORKLIST", line 225 ORA-06512: at line 1

Cause:
-The reason for the errors is :-The entry tied to the FND User in question is associated with an Expired Employee.
-To verify this, run this query and enter username when prompted, and if r.EXPIRATION_DATE is less then SYSDATE the issue has been confirmed that the FND User in question is associated with an Expired Employee.

select f.USER_NAME "FND USER NAME", wfr.DISPLAY_NAME, f.END_DATE, r.EXPIRATION_DATE,
wfr.STATUS, wfr.EXPIRATION_DATE, wfr.ORIG_SYSTEM, wfr.PARENT_ORIG_SYSTEM
from apps.fnd_user f, apps.wf_roles wfr, apps.wf_local_user_roles r
where f.USER_NAME=wfr.NAME and wfr.NAME=r.ROLE_NAME and r.user_name = 'XXX_XXXX';

-What has most likely happened is that the Employee / FND User in question had a change to the Employee record that expired an original record while keeping a current record for that employee.
Solution
1. Open the FND Users form and query user in question, and remove the Person field and save.
2. Add back the person field and save again
3. Save the form.
4. Then run the Synchronize WF Local Tables program for ALL
5. If certain documents are stuck in In Process or Pre-Approved status then review datafix below.

06 August 2009

Accounting period status check

To check accounting period status:
select * from apps.GL_PERIOD_STATUSES ;

To get APPLICATION_ID , use SQL below:
select * from apps.fnd_application_tl where upper(APPLICATION_NAME) like :APPLICATION_NAME;

To check Inventory accounting period status:
select * from apps.ORG_ACCT_PERIODS
where organization_id= :ORGANIZATION_ID;

To know SET_OF_BOOKS_ID or OPERATING_UNIT or LEGAL_ENTITY or CHART_OF_ACCOUNTS_ID of an organization, use:
SELECT b.* FROM apps.ORG_ORGANIZATION_DEFINITIONS b /*, APPS.HR_OPERATING_UNITS a , */
WHERE b.ORGANIZATION_ID = :ORG_ID

01 July 2009

How to check LOCK on any object in Oracle

All locks information is store in table sys.v_$locked_object

select * from sys.v_$locked_object

Object details are in table ALL_TABLES

25 June 2009

Import thru PO Interface tables

*this article is in reference to standard PO only.
You need to insert records in three table sequencially:
1. PO_HEADERS_INTERFACE
2. PO_LINE_INTERFACE
3. PO_DISTRIBUTIONS_INTERFACE


-----------------------------------------------------
-- Inserting Validated Data Into PO_HEADERS_INTERFACE
-----------------------------------------------------

INSERT INTO po_headers_interface (interface_header_id,
process_code,
action,
org_id,
document_type_code,
document_num,
currency_code,
agent_id,
vendor_id,
vendor_site_code,
vendor_site_id,
ship_to_location_id,
bill_to_location_id,
comments,
vendor_doc_num,
freight_terms,
fob,
freight_carrier,
terms_id,
creation_date,
created_by,
last_update_date,
last_updated_by,
effective_date,
expiration_date,
amount_agreed)
VALUES (po_headers_interface_s.NEXTVAL, -- INTERFACE_HEADER_ID
'PENDING',
'ORIGINAL', -- Type of action to be performed ACTION
ph.org_id, -- OU of the inventory org for which the data is bing provided.
ph.document_subtype, -- Fixed Value for loading standard PO
ph.po_number, -- PO number. In this case legacy PO no is uploaded here.
ph.currency_code, -- Validated Currency code from FND_Currencies
ph.agent_id,
ph.vendor_id,
ph.supplier_site,
ph.vendor_site_id,
ph.ship_to_location_id,
ph.bill_to_location_id,
ph.po_desc,
ph.po_number, -- PO number. In this case legacy PO no is uploaded here.
ph.freight_terms,
ph.fob, --PH.freight_carrier
ph.freight_code,
ph.terms_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
ph.effective_date,
ph.expiration_date,
ph.amount_agreed);

---------------------------------------------------
-- Inserting Validated Data into PO_LINES_INTERFACE
---------------------------------------------------

INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_num,
item_id,
item_description,
uom_code,
unit_of_measure,
quantity,
unit_price,
ship_to_organization_id,
need_by_date,
promised_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
line_type,
vendor_product_num,
note_to_vendor,
shipment_num,
closed_code,
closed_reason,
CATEGORY,
price_break_lookup_code,
effective_date,
expiration_date,
list_price_per_unit)
VALUES (po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
pl.line_num,
pl.item_id,
LTRIM (RTRIM (pl.item_description)),
pl.uom_code,
pl.unit_of_measure,
pl.quantity,
pl.unit_price,
pl.ship_to_organization_id,
NULL, --pl.NEED_BY_DATE,
NULL, --pl.PROMISED_DATE,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NULL, --pl.line_type,
LTRIM (RTRIM (pl.vendor_item)),
pl.note_to_vendor, --new added
NULL, --pl.SHIPMENT_NUMBER,
pl.closed_code,
pl.closed_reason,
pl.primary_category,
'NON CUMULATIVE',
pl.effective_date,
pl.expiration_date,
pl.list_price_per_unit);


---insert line for blanket location
INSERT INTO po_lines_interface (interface_line_id,
interface_header_id,
line_num,
item_id,
item_description,
uom_code,
unit_of_measure,
quantity,
unit_price,
ship_to_organization_id,
need_by_date,
promised_date,
last_update_date,
last_updated_by,
creation_date,
created_by,
line_type,
vendor_product_num,
note_to_vendor,
shipment_num,
closed_code,
closed_reason,
CATEGORY,
price_break_lookup_code,
effective_date,
expiration_date,
shipment_type)
VALUES (po_lines_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
pl.line_num,
pl.item_id,
LTRIM (RTRIM (pl.item_description)),
pl.uom_code,
pl.unit_of_measure,
0, --QUANTITY,
pl.unit_price,
pl.ship_to_organization_id,
NULL, --pl.NEED_BY_DATE,
NULL, --pl.PROMISED_DATE,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
NULL, -- pl.line_type,
LTRIM (RTRIM (pl.vendor_item)),
NULL,
pl.line_num, --pl.note_to_vendor
pl.closed_code,
pl.closed_reason,
pl.primary_category,
NULL,
pl.effective_date,
pl.expiration_date,
'PRICE BREAK');

-----------------------------------------------------------
-- Inserting Validated Data into PO_DISTRIBUTIONS_INTERFACE
-----------------------------------------------------------

INSERT INTO po_distributions_interface (interface_header_id,
interface_line_id,
interface_distribution_id,
po_header_id,
po_line_id,
distribution_num,
quantity_ordered,
quantity_delivered,
quantity_billed,
quantity_cancelled,
destination_type_code,
deliver_to_location_id,
charge_account,
charge_account_id,
creation_date,
created_by)
VALUES (po_headers_interface_s.CURRVAL,
po_lines_interface_s.CURRVAL,
po_distributions_interface_s.NEXTVAL,
po_headers_interface_s.CURRVAL,
po_lines_interface_s.CURRVAL,
'1', -- distribution numbner
pl.quantity, -- ordered
NULL, -- delivered
NULL, -- billed
NULL, -- cancelled
pl.destination_type_code,
ph.ship_to_location_id,
pl.charge_account,
pl.charge_account_id,
SYSDATE,
fnd_global.user_id);

Commit;

After all this run program "Import Standard Purchase Orders"