Substr and instr example

 Select Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',': ',1,2)+2 ace, Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',' ',-1,1) ace1,

    Substr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',': ',1,2)+2,

  Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',' ',-1,1)-(Instr('/tmp/pll/XXPOXPOAH.pll:  : 259 Simple',': ',1,2)+2)) exact_value 

 From Dual;

Inventory Item Creation OPI

ITEM IMPORT - INCOIN

Pre-requisites:

* Creating an Organization
* Code Combinations
* Templates
* Defining Item Status Codes
* Defining Item Types

Interface tables:
1. MTL_SYSTEM_ITEMS_INTERFACE
2. MTL_ITEM_REVISIONS_INTERFACE (If importing revisions)
3. MTL_ITEM_CATEGORIES_INTERFACE (If importing categories)
4. MTL_INTERFACE_ERRORS (View Fallouts after import)

Concurrent Programs:
1. Import Items

Parameters :

Process Set (Null for All) - Set_Process_Id = Some Number - Non-Required numeric value set
Create or Update Items = CREATE New Items or UPDATE Existing Items
Validate Items = Yes or No
Process Items = Yes or No
Delete Processed Rows = Yes or No
Gather Statistics = Yes or No

Base Table:

1. MTL_SYSTEM_ITEMS_B
2. MTL_ITEM_REVISIONS_B
3. MTL_CATEGORIES_B
4. MTL_CATEGORY_SETS_B
5. MTL_ITEM_STATUS
6. MTL_ITEM_TEMPLATES

Validation:
1. Check for valid item type. (FND_LOOKUP_TYPES_VL where LOOKUP_TYPE = 'ITEM_TYPE')
2. Check for valid part_id/segment of the source table. (MTL_SYSTEM_ITEMS_B)
3. Validate part_id/segment1 for master org.  (MTL_SYSTEM_ITEMS_B, MTL_PARAMETERS)
4. Validate and translate template id of the source table. (MTL_ITEM_TEMPLATES)
5. Check for valid template id. (Attributes are already set for items, default attributes for that template, i.e., purchasable, stockable, etc )
6. Check for valid item status.
7. Validate primary uom of the source table.
8. Validate attribute values.
9. Validate other UOMs of the source table.
10. Check for unique item type. Discard the item, if part has non-unique item type.
11. Check for description, inv_um uniqueness
12. Validate Inventory Organization id.
13. Load master records and category records only if all Load child record if no error found.


Some important columns that need to populated in the MTL_SYSTEM_ITEMS_INTERFACE tables
1. PROCESS_FLAG = 1 (1= Pending, 2= Assign Complete, 3= Assign/Validation Failed, 4= Validation succeeded; Import failed, 5 = Import in Process, 7 = Import succeeded)
2. TRANSACTION_TYPE = ‘CREATE’, ‘UPDATE’
3. SET_PROCESS_ID = 1
4. ORGANIZATION_ID
5. DESCRIPTION
6. ITEM_NUMBER and/or SEGMENT (n)
7. MATERIAL_COST
8. REVISION
9. TEMPLATE_ID
10. SUMMARY_FLAG
11. ENABLED_FLAG
12. PURCHASING_ITEM_FLAG
13. SALES_ACCOUNT (defaulted from MTL_PARAMETERS.SALES_ACCOUNT)
14. COST_OF_SALES_ACCOUNT (defaulted from MTL_PARAMETERS.COST_OF_SALES_ACCOUNT)

Links for AP - SLA / XLA - GL

Select Distinct Xal.Accounting_Date Gl_Date,Xah.Je_Category_Name Tr_Type, Ap.Segment1 Vendor_Num, Ap.Vendor_Name,Pvs.Vendor_Site_Code As Vendor_Site_Name ,
Aia.Doc_Sequence_Value Voucher_No, Aia.Invoice_Num, Aia.Invoice_Date, Xal.Currency_Code, Xal.Entered_Dr,Xal.Entered_Cr,Xal.Accounted_Dr,Xal.Accounted_Cr ,
Replace(Xal.Description, Chr(10),' ') Remarks
 From
Apps.Gl_Import_References Gir,
Apps.Gl_Je_Headers Gh,
Apps.Gl_Je_Lines Gl,
Apps.Xla_Ae_Lines Xal ,
Apps.Xla_Ae_Headers Xah,
Apps.Xla_Transaction_Entities_Upg Xte,
Apps.Ap_Invoices_All Aia,
Apps.Ap_Suppliers Ap,
Apps.Po_Vendor_Sites_All Pvs,
Apps.Gl_Code_Combinations Gcc
Where
Xal.Gl_Sl_Link_Id=Gir.Gl_Sl_Link_Id
And Xal.Ae_Header_Id = Xah.Ae_Header_Id
And Gir.Je_Header_Id = Gl.Je_Header_Id
And Gir.Je_Line_Num = Gl.Je_Line_Num
And Gl.Je_Header_Id = Gh.Je_Header_Id
And Xah.Entity_Id = Xte.Entity_Id
And Xte.Source_Id_Int_1 = Aia.Invoice_Id
And Xte.Security_Id_Int_1 = Aia.Org_Id
And Aia.Vendor_Id = Ap.Vendor_Id
And Ap.Vendor_Id = Pvs.Vendor_Id
And Aia.Vendor_Site_Id = Pvs.Vendor_Site_Id
And Gcc.Code_Combination_Id = Xal.Code_Combination_Id
And Gcc.Code_Combination_Id = Gl.Code_Combination_Id
And Aia.Org_Id = Nvl(:Org, Aia.Org_Id)
and xal.source_table in ('AP_INVOICES','AP_CHECKS')
And Nvl(Xah.Gl_Transfer_Status_Code,'N') = 'Y';

Download And Upload Report using Command Prompt FNDLOAD

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct /exceltest/TEST_UAT/UAT_TEST/apps/apps_st/appl/xxeil/12.0.0/reports/US/XX_WIP.ldt PROGRAM APPLICATION_SHORT_NAME="XXEIL" CONCURRENT_PROGRAM_NAME="XX_WIP"

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_WIP.ldt

Order To Cash Cycle

Order To Cash Process Steps:
1. Enter the Sales Order
2. Book the Sales Order
3. Launch the Pick List
4. Ship Confirm
5. Creative Invoice
6. Create the Receipts either manually or using Auto Lockbox
7. Transfer to GL
8. Journal import
9. Posting

Common Profiles to be done:
Before start to configure the setups needs to set the following profiles at Responsibility Level,
1. Qp : Item Validation Organization – IMO to be attached
2. Qp : Pricing Transaction Entity – Order Fulfilment
3. Qp: Source System Code – Oracle Pricing – (it is for Order Management)

Prerequisites:
1. Customer Profile Class
2. System Options
3. System Parameters
4. Pick Slip Grouping Rule
5. Release Sequencing Rule
6. Release Rule
7. Transaction Type
8. Document Sequence
9. Document Sequence Assignment
10. Grants & Roles
11. Customers
12. Profile Options

Basic Setups:
Go to Order Management Responsibility:
1. Customer Profile Option
Navigation Path – Setup  Customers  Profile Classes
2. System Options
Navigation Path - Setup  Customers  System Options
3. System Parameters
Navigation Path - Setup  System Parameters  Values
Note:
Once System Options and System Parameters defined, users are able to view the Sales Order form. Otherwise it will be freeze out field.
4.  Pick Slip Grouping Rule
Navigation Path - Setup  Shipping  Picking  Define Pick Slip Grouping Rules

5. Define Release Sequence Rule
Navigation Path - Setup  Shipping  Picking  Releasing Rule
6. Define Release Rules
Navigation Path - Setup  Shipping  Picking  Define Releasing Rule
7. Roles Definitions
Navigation Path - Setup  Shipping  Grants and Roles Definitions  Define Roles
8. Grants Definitions
Navigation Path - Setup  Shipping  Grants and Roles Definitions  Grants
9. Transaction Type
Navigation Path - Setup  Transaction Types  Define
10. Transaction Type Approvals
Navigation Path - Setup  Transaction Types  Approval

11. Document Sequence
Navigation Path - Setup  Shipping  Documents  Document Sequences
12. Document Category creations
Navigation Path - Setup  Shipping  Documents  Document Categories
13. Document Sequence Assignments
Navigation Path - Setup  Shipping  Documents  Document Sequences Assignment
14. Sales Person Creation
Navigation Path – Setup  Sales Persons
15. Price list Creation
Navigation Path – Pricing  Price Lists  Price List Setup
16. Payment Terms
Navigation Path – Setup  Orders  Payment Terms

17. Customer Creation
Navigation Path – Customers  Standard

Note: If The Data that defines inconsistent error shows when navigate to Account site from Customer Account –
Root Cause - To solve the problem, go to Flex Field and take Territory Flex field and Application is ‘Receivables’ is not frozen.

Solution – Freeze Territory Key Flex Field (Application – Receivables)

Major setups to be create in Customers:
1. Bill to site to be created
2. Ship to site to be created
3. Primary Bill to and Primary Ship to site enabled
4. In order Management Tab – select the inventory org in warehouse field
5. Select the Order type, Price List, Warehouse to be assigned in Account Site form

WRITE TO A FILE USING UTL_FILE

• Create a Table

 Create Table xx_parameters (

 param_name VARCHAR2(100) ,

 param_value NUMBER );

• Input some parameters
• Create a Directory 

CREATE OR REPLACE DIRECTORY DIR_MYTEMP AS '/home/oracle/ebsutl12/';

• Create the Procedure

CREATE OR REPLACE PROCEDURE xxtest_procedure_utl

AS

   CURSOR c_cur1

   IS

      SELECT param_name, param_value

        FROM xx_parameters;

   l_file   UTL_FILE.file_type;

   l_dir    VARCHAR2 (50)      := 'DIR_MYTEMP';

   l_name   VARCHAR2 (20)      := 'mydatafile.txt';

BEGIN

   l_file := UTL_FILE.fopen (l_dir, l_name, 'W');

   FOR cur_rec IN c_cur1

   LOOP

      UTL_FILE.put_line (l_file,

                         cur_rec.param_name || ',' || cur_rec.param_value

                        );

   END LOOP;

   UTL_FILE.fclose (l_file);

EXCEPTION

   WHEN UTL_FILE.invalid_path

   THEN

      DBMS_OUTPUT.put_line (‘invalid_path’);

      RAISE;

   WHEN UTL_FILE.invalid_mode

   THEN

      DBMS_OUTPUT.put_line (‘invalid_mode’);

      RAISE;

   WHEN UTL_FILE.invalid_filehandle

   THEN

      DBMS_OUTPUT.put_line (‘invalid_filehandle’);

      RAISE;

   WHEN UTL_FILE.invalid_operation

   THEN

      DBMS_OUTPUT.put_line (‘invalid_operation’);

      RAISE;

   WHEN UTL_FILE.read_error

   THEN

      DBMS_OUTPUT.put_line (‘read_error’);

      RAISE;

   WHEN UTL_FILE.write_error

   THEN

      DBMS_OUTPUT.put_line (‘write_error’);

      RAISE;

   WHEN UTL_FILE.internal_error

   THEN

      DBMS_OUTPUT.put_line (‘internal_error’);

      RAISE;

END xxtest_procedure_utl;

/

• Executing the procedure will create a file named mydatafile.txt in directory /home/oracle/ebsutl12/

Sample of DBMS_LOCK.SLEEP

• Create a Table

Create Table T_Sleep_Test(
S_No Number,
Dateandtime Date);

• Create a Procedure

CREATE OR REPLACE PROCEDURE XXTEST_PROCEDURE

IS

   l_cnt   NUMBER;

BEGIN

   l_cnt := 0;

   EXECUTE IMMEDIATE 'Truncate Table Xxsleep_table';

   FOR l_cnt IN 1 .. 5

   LOOP

      INSERT INTO xxsleep_table

           VALUES (l_cnt, SYSDATE);

      COMMIT;

      DBMS_LOCK.sleep (10);

   END LOOP;

END;

• Values get inserted into the table created above in an interval of 10 seconds

Exec XXTEST_PROCEDURE;

• Check once the execution of the procedure gets completed.

Select S_No, To_Char(Dateandtime,'DD-MON-RRRR HH24:MI:SS') Ace From   Xxsleep_table;

Locked Session in Oracle

Select L.Session_Id||','||V.Serial# Sid_Serial,
       L.Oracle_Username Ora_User,
       O.Object_Name,
       O.Object_Type,
       Decode(L.Locked_Mode,
          0, 'None',
          1, 'Null',
          2, 'Row-S (SS)',
          3, 'Row-X (SX)',
          4, 'Share',
          5, 'S/Row-X (SSX)',
          6, 'Exclusive',
          To_Char(L.Locked_Mode)
       ) Lock_Mode,
       O.Status,
       To_Char(O.Last_Ddl_Time,'dd.mm.yy') Last_DDL
From Dba_Objects O, Gv$Locked_Object L, V$Session V
Where O.Object_Id = L.Object_Id
      And L.Session_Id=V.Sid
Order By 2,3;

------------------------

ALTER SYSTEM KILL SESSION  'SID,SERIAL'; ( '633,857' );

Differences in the Table Suffix

_ALL - Multi org tables. Show data for all orgs. 
_V - Views. Mostly show data specific to the orgs user has logged in. 
_TL - Translation table. Store name, description etc. in mutliple languages. 
_VL - View which selects only one row from _TL table based on the user session's language and org_id. Uses USERENV ('LANG') to fetch data from join of _V and _TL 
_B - Base table. Mostly used for non-org specific data. 

Base CRM Tables

OKC_K_PARTY_ROLES_B
OKC_K_HEADERS_B
OKC_K_LINES_B
OKC_LINE_STYLES_B
OKC_K_LINES_TL
OKC_K_ITEMS
OKC_RULE_GROUPS_B
OKC_REACT_INTERVALS
OKC_TIMEVALUES_B
OKC_COVER_TIMES
OKC_RULES_B
OKS_BUS_PROCESSES_V
OKS_LEVEL_ELEMENTS
OKX_INCIDENT_SEVERITS_V
CSI_ITEM_INSTANCES
HZ_PARTIES
MTL_SYSTEM_ITEMS_B