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.