EFFECT of SLA Pre-Upgrade program

 How do we check whether “SLA Pre-Upgrade program” did its job or not. 

I found a way, first check whether MIGRATION_STATUS_CODE column is added to table GL_PERIOD_STATUSES.

then we can be checked using below query. If we have MIGRATION_STATUS_CODE  = P for periods from which you run the program, then we are on the track.

SELECT MIGRATION_STATUS_CODE, A.* FROM GL_PERIOD_STATUSES A

ORDER BY START_DATE DESC;

Meaning of MIGRATION_STATUS_CODE:

·         P = Pending Upgrade
The system is preparing to upgrade the accounting periods in this table that have a status P. The accounting transactions in these corresponding accounting periods will be upgraded from 11i to R12 by the Subledger products (i.e, AR, AP etc).

·         U = Upgraded
The accounting records have been upgraded from 11i to R12

·         N = New-Only used by GL

Supplier site not visible from application

Issue: Supplier site not visible from application, but it databse site exists.
Cause: The value in the supplier_notif_method in po_vendor_sites_all table for these problem sites was the word “NULL” instead of being NULL.

Fix is to run below SQL:

UPDATE po_vendor_sites_all
SET supplier_notif_method = NULL
WHERE vendor_site_id = ;

Create PO release during Requisition import without ASL

Here we will see how to create PO release during Requisition import without setting up Sourcing and ASL (Approved Supplier List)

While adding record to PO_REQUISITIONS_INTERFACE_ALL, we need to populate below columns..


AUTOSOURCE_FLAG, 
AUTOSOURCE_DOC_HEADER_ID,
AUTOSOURCE_DOC_LINE_NUM,
DOCUMENT_TYPE_CODE

After this run Requisition Import Program, it will create PO release even ASL is not setup.


Sample SQL for non-inventory item requisition:

      INSERT
      INTO PO_REQUISITIONS_INTERFACE_ALL
        (ORG_ID
        ,DELIVER_TO_LOCATION_ID
        ,DELIVER_TO_REQUESTOR_ID
        ,AUTHORIZATION_STATUS
        ,REQUISITION_TYPE
        ,QUANTITY
        ,UNIT_PRICE
        ,UOM_CODE
        ,CURRENCY_CODE
        ,PREPARER_ID
        ,CHARGE_ACCOUNT_ID
        ,SOURCE_TYPE_CODE
        ,HEADER_DESCRIPTION
        ,DESTINATION_TYPE_CODE
        ,DESTINATION_ORGANIZATION_ID
        ,INTERFACE_SOURCE_CODE
        ,CATEGORY_ID
        ,ITEM_DESCRIPTION
        ,SUGGESTED_VENDOR_ID
        ,SUGGESTED_VENDOR_SITE_ID
        ,AUTOSOURCE_FLAG
        ,AUTOSOURCE_DOC_HEADER_ID
        ,AUTOSOURCE_DOC_LINE_NUM
        ,DOCUMENT_TYPE_CODE
        )
      VALUES
        (l_org_id
        ,l_deliver_to_location_id
        ,l_deliver_to_requestor_id
        ,'APPROVED'
        ,'PURCHASE'
        ,l_quantity
        ,l_unit_price
        ,l_uom_code
        ,l_currency_code
        ,l_preparer_id
        ,l_charge_account_id
        ,'VENDOR'
        ,l_header_description
        ,'EXPENSE' 
        ,l_destination_organization_id
        ,l_interface_source_code
        ,l_category_id
        ,l_item_description
        ,l_suggested_vendor_id
        ,l_suggested_vendor_site_id
        ,'Y'
        ,l_autosource_doc_header_id
        ,l_autosource_doc_line_num
        ,l_document_type_code --'BLANKET'
        ) ;

Inventory articles for reference

What is Phantom Item and how does it behave :
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/bom/bomov02.htm

Relationships between Item Attributes:
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/inv/attrelat.htm#c_relratt

Item Attributes Listed by Group Name
http://download.oracle.com/docs/cd/A60725_05/html/comnls/us/inv/attbygrp.htm#r_itmattgrp

Convert Number to word (or text)

In many cases we need to put number in words, special when it comes to amount on Invoice or Cheque/Check. If you want to convert a number in word or in text using Oracle SQL, then you use below statment

select TO_CHAR(TO_DATE( 158 , 'J' ) , 'JSP' ) from dual;


Output:
ONE HUNDRED FIFTY-EIGHT

Procedure is not getting complied

If the package or procedure is not getting complied, then one of the reasons why the package XX_YY_ZZ is not getting compiled is there are few session locks which got applied. We can find them using below query, then release them and try compiling the package…

Query ran:

SELECT *

FROM v$session

WHERE SID IN (SELECT UNIQUE SID

FROM v$access

WHERE OBJECT='&OBJECT_NAME');

OR you may use below query and output of below query will give ready made statements to kill these locks and you can directly use it…

SELECT 'alter system kill session '''||SID||','||serial#||''';'

FROM v$session

WHERE SID IN (SELECT UNIQUE SID

FROM v$access

WHERE OBJECT='&OBJECT_NAME');

Example:

alter system kill session '1120,27788';

alter system kill session '1187,32246';

alter system kill session '1355,64';

alter system kill session '1060,11503';

alter system kill session '1314,21';

We already used this solution in one of the project. After running above statements, we could able to complete the package within few seconds.

ORG_IDs on Sales Order

ship_to_org_id            SITE_ID of SHIP_TO location for the customer on order


sold_to_org_id            cust_account_id means customer id

invoice_to_org_id        SITE_ID of BILL_TO location for the customer on order

ship_from_org_id         Organization_ID of wearhous from which the material is going to be shipped

How to apply Credit Hold on SO even before Booking the order

We can put all the Sales orders on Credit Check hold for a customer before booking the order.
Open customer details, go to 'Profile: Transaction' tab and enable a check box 'Credit Hold'.

 

This option is available at Customer level as well as Customer site level, you can use whatever applicable.


Please refer Metalink Doc ID 339665.1 for more details.
 

Order Management APIs and open interface

 Order Management APIs and open interface document
CLICK HERE

Get System Administrator Responsibility in Oracle Apps

Please use the following script to get system administrator responsibility

declare
BEGIN
   fnd_user_pkg.addresp (username            => 'DEVENDRA_GULVE',    ---- Give your user name of front end
                         resp_app            => 'SYSADMIN',
                         resp_key            => 'SYSTEM_ADMINISTRATOR',
                         security_group      => 'STANDARD',
                         description         => 'System Administrator',
                         start_date          => SYSDATE,
                         end_date            => NULL
                        );

COMMIT;

END;

Important Profile options for Responsibility

HR:Business Group
HR:User Type
GL Ledger Name
MO: Operating Unit

** Details will be published later...

How to assing item category to function - e.g. Purchasing

Goto: Inventory responsibility > Setup > Item > Categories > Default Category Sets

Here you can assign different Item categories to different functions of Oracle Application.

Requisition Import Problem for OSP / WIP

Issue: After the WIP move to the Outside processing operation, the requisition import is not picking up the record to create the requisition. Even after running work-flow background engine and running Requisition Import program for WIP, Oracle is not picking up records eligible.

Cause: The issue for requisition import not picking up the record was due to the setup 'Requisition Creation Time' on Outside processing tab on WIP Parameters. It was set to 'Manual'.

Solution: Change it to 'At Operation' and it will start working.

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.

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

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

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"

Serial number TOKEN is defined in a different organization

If you get error "Serial number &TOKEN is defined in a different organization" for receiving any serial number.
Check the ORGANIZATION_ID for the item in MTL_SERIAL_NUMBERS table and change is accordingly. If this is correct then try for the SQL given below for applying datafix..


UPDATE mtl_serial_numbers
SET current_status = :Status, -- refer the statuses given below
group_mark_id = NULL,
line_mark_id = NULL,
lot_line_mark_id = NULL
WHERE serial_number = :serial_number


LOOKUP_CODE MEANING
1 Defined but not used
3 Resides in stores
4 Issued out of stores
5 Resides in intransit
7 Resides in receiving
8 Resides in WIP

how to restrict sales of item to specific customer

Question:
Say I have 30 customers and suppose i want to sell my product for 2 customers only
I don't want to sell for others.
how can i do it?

Answer:
1. Make one group of customers to whom I want to sell the particular item.
2. On price-list form Qualifier (tab) add that group for that item line with desired context, attribute, operator, and value

BOM

Q. Why limited organizations are available in Change Organization window??
Ans: We need to assign organization to the application to access it from that application and the responsibility. Navigation is Inventory (R) --> Setups --> Organization --> Organization Access


Q: What is the use/meaning of Lead Time% in Routing (Main tab), count point, CUM Yield?
Ans: 1. Lead Time % is the % of total lead time taken for that particular operation.
2. Count Point is generally used in Project manufacturing. I am not sure about how does itr affect the functionality.
3. CUM Yield (Cumulative Yield): It is the addition of yield of all the previous operations.

Q. What is supply type=Supplier in BOM?
Ans: It is used for VMI (Vendor Managed Inventory). This is a concept from Lean manufacturing where some vendor/s are asked to maintain their inventory at our place, so that our inventory carrying cost goes down.