The steps to create an LOV manually are

• Create the record group. You will need to type in the query on which the record group is
based.
• Create the LOV and set its Record Group property to the appropriate record group.
• Set the LOV property Column Mapping. You must type in the columns and their
headings, and then select a return item for each item that you want to populate from
the LOV.
• Assign the LOV to any text items from which you want the LOV to be available.

Retrieving Restricted Data

Do not use quotation marks with character and date items.
• The LIKE operator is implied with % or _.
• Use hash (#) in front of SQL operators.
• Use Query/Where for complex query conditions.
• Use default date format (DD-MON-YY) in Query/Where.
• Use quotes around literals in Query/Where.

API-HRMS-II

Simple E.g for Update absence details:

CREATE OR REPLACE PROCEDURE XXRI_UPDATE_ABSENCE(p_person_id in number,
p_date_start in date,
p_date_end in date,
p_absence_attendance_id in number)
AS
   l_effective_date              DATE;
   l_absence_attendance_id       NUMBER;
   l_absence_days                NUMBER;
   l_absence_hours               NUMBER;
   l_object_version_number       NUMBER;
   l_dur_dys_less_warning        BOOLEAN;
   l_dur_hrs_less_warning        BOOLEAN;
   l_exceeds_pto_entit_warning   BOOLEAN;
   l_exceeds_run_total_warning   BOOLEAN;
   l_abs_overlap_warning         BOOLEAN;
   l_abs_day_after_warning       BOOLEAN;
   l_dur_overwritten_warning     BOOLEAN;
   l_del_element_entry_warning   BOOLEAN;
   l_abs_attendance_reason_id    NUMBER;
   l_date_start                  DATE;
   l_date_end                    DATE;
   l_person_id                   NUMBER;
BEGIN
   l_object_version_number := NULL;

   SELECT  person_id,abs_attendance_reason_id, absence_attendance_id
     INTO  l_person_id,l_abs_attendance_reason_id, l_absence_attendance_id
     FROM per_absence_attendances ABS
    WHERE ABS.absence_attendance_id =p_absence_attendance_id;

   BEGIN
      SELECT MAX (object_version_number)
        INTO l_object_version_number
        FROM per_absence_attendances ABS
       WHERE ABS.absence_attendance_id = p_absence_attendance_id;

      hr_person_absence_api.update_person_absence
                  (p_effective_date                 => l_effective_date,
                   p_absence_attendance_id          => l_absence_attendance_id,
                   p_abs_attendance_reason_id       => l_abs_attendance_reason_id,
                   p_date_notification              => l_effective_date,
                   p_date_start                     => p_date_start,
                   p_date_end                       => p_date_end,
                   p_absence_days                   => l_absence_days,
                   p_absence_hours                  => l_absence_hours,
                   p_object_version_number          => l_object_version_number,
                   p_dur_dys_less_warning           => l_dur_dys_less_warning,
                   p_dur_hrs_less_warning           => l_dur_hrs_less_warning,
                   p_exceeds_pto_entit_warning      => l_exceeds_pto_entit_warning,
                   p_exceeds_run_total_warning      => l_exceeds_run_total_warning,
                   p_abs_overlap_warning            => l_abs_overlap_warning,
                   p_abs_day_after_warning          => l_abs_day_after_warning,
                   p_dur_overwritten_warning        => l_dur_overwritten_warning,
                   p_del_element_entry_warning      => l_del_element_entry_warning
                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Inner statement' || ' ' || SQLERRM);
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Outer statement' || ' ' || SQLERRM);
      COMMIT;
END XXRI_UPDATE_ABSENCE;
/
Compile and call the procedure with required parameters

CREATE OR REPLACE PROCEDURE XXRI_CREATE_ABSENCE
(p_person_id IN NUMBER,
p_absence_name IN VARCHAR2,
p_effective_date IN DATE,
p_start_date IN DATE,
p_end_date IN DATE,
P_ABSENCE_ATTENDANCE_ID OUT NUMBER
) AS
-- Essential functionality variables
L_VALIDATE_MODE BOOLEAN := FALSE;
L_PERSON_TYPE_ID NUMBER(15);
L_BUSINESS_GROUP_ID NUMBER;
L_EMPLOYEE_NO NUMBER;
L_DATE1 DATE;

-- API Return Variables
L_PERSON_ID NUMBER;
L_ASSIGNMENT_ID NUMBER;
L_ABS_DAYS NUMBER;
L_ABS_HOURS NUMBER;

L_ABSENCE_ATTENDANCE_ID NUMBER;
L_OBJ_VERSION_NUMBER NUMBER;
L_OCCURRENCE NUMBER;
L_ENTITLEMENT_WARNING BOOLEAN;
L_ABS_DAY_AFTER_WARNING BOOLEAN;
L_ABS_OVERLAP_WARNING BOOLEAN;

-- Constant variables
--L_MODULE_ID CONSTANT VARCHAR2(30) := 'XXFTHR_MIGRATION007';

-- Error Handling variables
L_ERROR_MESSAGE VARCHAR2(150);
L_ERROR_CODE VARCHAR2(30);
L_ERROR_STATEMENT VARCHAR2(50);

-- Count Variables
L_COUNT_TOTAL NUMBER := 0;
L_COUNT_SUCCESS NUMBER := 0; -- Total number of successful rows
L_CNT1 NUMBER := 0;
L_ERRM VARCHAR2(100);
L_ERR_AT_STMT NUMBER;
O_ABSENCE_ATTENDANCE_ID NUMBER;
O_OBJECT_VERSION_NUMBER NUMBER;
O_OCCURRENCE NUMBER;
O_DUR_DYS_LESS_WARNING BOOLEAN;
O_DUR_HRS_LESS_WARNING BOOLEAN;
O_EXCEEDS_PTO_ENTIT_WARNING BOOLEAN;
O_EXCEEDS_RUN_TOTAL_WARNING BOOLEAN;
O_ABS_OVERLAP_WARNING BOOLEAN;
O_ABS_DAY_AFTER_WARNING BOOLEAN;
O_DUR_OVERWRITTEN_WARNING BOOLEAN;
O_ABSENCE_DAYS NUMBER;
O_ABSENCE_HOURS NUMBER;

L_HIRE_DATE NUMBER;
L_BUSINESS_GROUP_ID VARCHAR(50);

L_ABSENCE_ATTENDANCE_TYPE_ID NUMBER;
L_ABS_ATTENDANCE_REASON_ID NUMBER;
L_BATCH_ID INTEGER;
L_CUTOFF_DATE VARCHAR(50) := '23-APR-2008';

X_USER_ID NUMBER;
X_RESP_ID NUMBER;
X_APPL_ID NUMBER;

BEGIN

--L_BUSINESS_GROUP_ID := 102;

--L_ERR_AT_STMT := 10;
--L_EMPLOYEE_NO := '2';
--L_CNT1 := L_CNT1 + 1;

--L_ABS_DAYS := 5;
--L_ABS_HOURS := NULL;
O_ABSENCE_ATTENDANCE_ID := NULL;
O_OBJECT_VERSION_NUMBER := NULL;
O_OCCURRENCE := NULL;
O_DUR_DYS_LESS_WARNING := NULL;
O_DUR_HRS_LESS_WARNING := NULL;
O_EXCEEDS_PTO_ENTIT_WARNING := NULL;
O_EXCEEDS_RUN_TOTAL_WARNING := NULL;
O_ABS_OVERLAP_WARNING := NULL;
O_ABS_DAY_AFTER_WARNING := NULL;
O_DUR_OVERWRITTEN_WARNING := NULL;
O_ABSENCE_DAYS := NULL;
O_ABSENCE_HOURS := NULL;
L_HIRE_DATE := NULL;
--L_PERSON_ID := NULL;
L_ABSENCE_ATTENDANCE_TYPE_ID := NULL;
L_ABS_ATTENDANCE_REASON_ID := NULL;

BEGIN

O_ABSENCE_DAYS := NULL;
L_PERSON_ID := 62;
SELECT USER_ID INTO X_USER_ID FROM FND_USER WHERE USER_NAME = 'SYSADMIN';

SELECT APPLICATION_ID
INTO X_APPL_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'PER';

SELECT RESPONSIBILITY_ID
INTO X_RESP_ID
FROM FND_APPLICATION FA, FND_RESPONSIBILITY_TL FR
WHERE FA.APPLICATION_SHORT_NAME = 'PER'
AND FA.APPLICATION_ID = FR.APPLICATION_ID
AND FR.RESPONSIBILITY_NAME = 'RI HRMS Manager';

FND_GLOBAL.APPS_INITIALIZE(X_USER_ID, X_RESP_ID, X_APPL_ID);

      BEGIN
         SELECT paat.absence_attendance_type_id
           INTO l_absence_attendance_type_id
           FROM per_absence_attendance_types paat
          WHERE UPPER (TRIM (paat.NAME)) = UPPER (TRIM (p_absence_name));
      EXCEPTION
         WHEN OTHERS
         THEN
            L_ERROR_MESSAGE := 'Absence Attendance Type Id is NULL';
       DBMS_OUTPUT.put_line ('E:' || L_ERROR_MESSAGE);
      END;


hr_person_absence_api.create_person_absence
(p_validate => FALSE
,p_effective_date => p_effective_date
,p_person_id => p_person_id
,p_business_group_id => 102
,p_absence_attendance_type_id => l_absence_attendance_type_id
,p_abs_attendance_reason_id => NULL
,p_comments => NULL
,p_date_notification => p_effective_date
,p_date_projected_start => NULL
,p_time_projected_start => NULL
,p_date_projected_end => NULL
,p_time_projected_end => NULL
,p_date_start => p_start_date
,p_time_start => NULL
,p_date_end => p_end_date
,p_time_end => NULL
,p_absence_days => O_ABSENCE_DAYS
,p_absence_hours => O_ABSENCE_HOURS
,p_authorising_person_id => NULL
,p_replacement_person_id => NULL
,p_attribute_category => NULL
,p_attribute1 => NULL
,p_attribute2 => NULL
,p_attribute3 => NULL
,p_attribute4 => NULL
,p_attribute5 => NULL
,p_attribute6 => NULL
,p_attribute7 => NULL
,p_attribute8 => NULL
,p_attribute9 => NULL
,p_attribute10 => NULL
,p_attribute11 => NULL
,p_attribute12 => NULL
,p_attribute13 => NULL
,p_attribute14 => NULL
,p_attribute15 => NULL
,p_attribute16 => NULL
,p_attribute17 => NULL
,p_attribute18 => NULL
,p_attribute19 => NULL
,p_attribute20 => NULL
,p_period_of_incapacity_id => NULL
,p_ssp1_issued => NULL
,p_maternity_id => NULL
,p_sickness_start_date => NULL
,p_sickness_end_date => NULL
,p_pregnancy_related_illness => NULL
,p_reason_for_notification_dela => NULL
,p_accept_late_notification_fla => NULL
,p_linked_absence_id => NULL
,P_ABSENCE_ATTENDANCE_ID => O_ABSENCE_ATTENDANCE_ID,
P_OBJECT_VERSION_NUMBER => O_OBJECT_VERSION_NUMBER,
P_OCCURRENCE => O_OCCURRENCE,
P_DUR_DYS_LESS_WARNING => O_DUR_DYS_LESS_WARNING,
P_DUR_HRS_LESS_WARNING => O_DUR_HRS_LESS_WARNING,
P_EXCEEDS_PTO_ENTIT_WARNING => O_EXCEEDS_PTO_ENTIT_WARNING,
P_EXCEEDS_RUN_TOTAL_WARNING => O_EXCEEDS_RUN_TOTAL_WARNING,
P_ABS_OVERLAP_WARNING => O_ABS_OVERLAP_WARNING,
P_ABS_DAY_AFTER_WARNING => O_ABS_DAY_AFTER_WARNING,
P_DUR_OVERWRITTEN_WARNING => O_DUR_OVERWRITTEN_WARNING);

END;

P_ABSENCE_ATTENDANCE_ID:=O_ABSENCE_ATTENDANCE_ID;
COMMIT;
END;
/

API-HRMS

Using api will help in post production Changes also.

Create a procedure to call the api.Mandatory columns will be explained in the api itself.Pass the values in the procedure so that data will be mapped to seeded table with validations.

Eg. of a HRMS API-We are going to call  hr_person_absence_api.delete_person_absence

CREATE OR REPLACE PROCEDURE xxri_delete_absence (p_absence_attendance_id NUMBER)
   AS
      CURSOR del_abscence
      IS
         SELECT absence_attendance_id, object_version_number
           FROM per_absence_attendances
          WHERE absence_attendance_id = p_absence_attendance_id;
   BEGIN
      --g_procedure_name := 'delete_emp_abscence';

      FOR i IN del_abscence
      LOOP
         BEGIN
            hr_person_absence_api.delete_person_absence
                         (p_validate                   => FALSE,
                          p_absence_attendance_id      => i.absence_attendance_id,
                          p_object_version_number      => i.object_version_number
                         );
            COMMIT;
         EXCEPTION
            WHEN OTHERS
            THEN
             --  g_error_code := SUBSTR (SQLERRM, 1, 200);
              -- g_error_msg :=
                --     g_package_name
                  --|| '.'
--                  || g_procedure_name
--                  || '-'
--                  || g_error_code;
--               fnd_file.put_line (fnd_file.LOG, g_error_msg);
fnd_file.put_line (fnd_file.LOG,SQLERRM);
               DBMS_OUTPUT.put_line ('Error deleting Abscence:' || SQLERRM);
         END;
      END LOOP;
   END;
/

Workflow Definitions Loader

WFload will be in 

$FND_TOP/bin

By default it can be used the following ways.
To upload:

WFLOAD apps/pwd 0 Y UPLOAD file.wft

To download:

WFLOAD apps/pwd 0 Y DOWNLOAD file.wft ITEMTYPE1 [ITEMTYPE2...ITEMTYPEn]

Workflow tables

WF_ACTIVITIES table stores the definition of an activity. Activities can be processes, notifications, functions or folders. A process activity is a modelled workflow process

WF_ITEMS is the runtime table for workflow processes. Each row
defines one work item within the system.

The WF_ITEM_ATTRIBUTES table stores definitions of attributes
associated with a process. Each row includes the sequence in which the
attribute is used as well as the format of the attribute data.

WF_NOTIFICATIONS holds the runtime information about a specific
instance of a sent message. A new row is created in the table each time a
message is sent.

and ...

wf_items ,wf_item_activity_statuses ,wf_process_activities ,wf_activities_tl ,wf_item_activity_statuses ,wf_process_activities ,wf_activities_tl

Basics Of Java

Java was designed to allow secure execution of code across network. To make Java secure many of the features of C and C++ were eliminated. Java does not use Pointers. Java programs cannot access arbitrary addresses in memory.

ref http://www.javabeginner.com/learn-java/introduction-to-java-programming

JSP, or Java Server Pages, use a form of Java code embedded in standard HTML tags to create dynamic web pages. Similar to PHP and ASP. 

Java Applets are another type of program used on the internet. Applets, or a small program that is executed from within a web page, can bring many new uses to a web browser. Things like chat clients, instant messaging, complex calculations, and much more are commonly handled by Applets. 

Simple Program:

class Hello { public static void main (String[] args) { System.out.println("Welcome to the world of Java Programming."); } // method main } // class Hello

To compile and run this program, you need to have installed JDK and added a line to your path statement referring to the directory of where it was install + \bin. (e.g. path %path%;c:\jdk\bin;)

• type this file into notepad or something
  
• save it as Hello.java(class name + .java)
  
• drop to a command prompt
  
• type javac Hello.java (e.g. "javac C:\work\Hello.java")
  
• type java Hello (e.g. "java C:\work\Hello")

MDS-Meta Data Service

OAF Runs using MDS logic:


Meta is the object in a web page,Data being access n a request on Meta,and services is the connection from database and action performed.


OAF session is opened using Jdeveloper.

The JDeveloper OA Extension offers the following design time tools:

·         Unified Modeling Language (UML) tools to model and generate business logic

·         Guided user interface to lay out client user interfaces

·         Code generation for Controller classes.

The OA Framework View is implemented using an Oracle technology called UIX. UIX stands for User Interface XML; it uses XML to describe the components and hierarchy that make up an application page. UIX also provides the runtime capabilities to translate that metadata into HTML output that can be shown on a web browser or a mobile device. The metadata used to describe the UI is loaded into a database repository called Metadata Services (MDS), which is used at runtime to generate the HTML pages

Using Choose

.In regular XSL programming, if a condition is met in the choose command then further XSL code is executed.

-Used for multiple conditions.

Use the following syntax for these elements:

expression?>

CREATING XML REPORT USING PROCEDURES

we are not using RDF file to generate XML file. We can use the Procedure for this Purpose.

1. First Declare the utlfile.
2. Next give the path to save the .xml file name. This can be stored in the selected directories only. This can be found out using the query

SELECT *

  FROM all_directories

Procedure:

CODING

CREATE OR REPLACE PROCEDURE xmlproc (errbuf VARCHAR2, retcode NUMBER)

IS

   myutlfile   UTL_FILE.file_type;

BEGIN

   myutlfile := UTL_FILE.fopen ('c:\temp', 'xinv.xml', 'W');

   UTL_FILE.put_line (myutlfile, '');

   UTL_FILE.put_line (myutlfile, '');

CREATE OR REPLACE PROCEDURE xmloutproc (errbuf VARCHAR2, retcode NUMBER)

IS

BEGIN

   fnd_file.put_line (fnd_file.output, '');

   fnd_file.put_line (fnd_file.output, '');

   FOR i IN (SELECT inventory_item_id, segment1, description, creation_date

               FROM mtl_system_items_b

              WHERE creation_date > TO_DATE ('01-JAN-2008')

                AND organization_id = 204)

   LOOP

      fnd_file.put_line (fnd_file.output, '');

      fnd_file.put_line (fnd_file.output,

                            ''

                         || i.inventory_item_id

                         || ''

                        );

      fnd_file.put_line (fnd_file.output,

                         '' || i.segment1 || ''

                        );

      fnd_file.put_line (fnd_file.output,

                         '' || i.description || ''

                        );

      fnd_file.put_line (fnd_file.output,

                            ''

                         || i.creation_date

                         || ''

                        );

      fnd_file.put_line (fnd_file.output, '');

   END LOOP;

   fnd_file.put_line (fnd_file.output, '');

END;

Compile the Procedure

BEGIN

   xmloutproc ('a', 1);

END;

Now create a executable ans program as the same u do for all other reports.

Conditional Formating

XML Publisher supports the usage of simple "if" statements, as well as more complex "choose" expressions.

                       Insert the following syntax to designate the beginning of the conditional area.

condition?>

                       Insert the following syntax at the end of the conditional area: .

Use the following syntax to construct an if-then-else statement in your RTF template:

element_condition then result1 else result2 end if?>

Defining Groups n Page breaks

that for each occurrence of an element, you want the included fields displayed

XML group element tag name?>

Insert the following tag after the final element:

Page Breaks

                       To create a page break after the occurrence of a specific element

                       To insert a page break between each occurrence of a group, insert the "split-by-page-break" form field within the group immediately before the tag that closes the group

GETTING TWO KIND OF OUTPUT AT A TIME

E.g:If we need both pdf and excel output at a time:While Submitting a report, You can see the Layout field having only one option. Our requirement is adding two templates here. So, Go to Options, And press ctrl S after adding both defined templates in Template definition:

 

 

Global Variables

Global Variables remains same for the session.

But using of parameter is recommaned compared to usage global variables.

e.g:

create or replace package glob as
procedure G_var;
end;

Create or replace package body as
g1 varchar2(10):='Global Variable';--Global varable declaration
procedure g_var as
begin
dbms_output.put_line(g1);
if g1='Global Variable'
then
g1:='Executed Global Varable';
dbms_output.put_line(g1);
end;
end;

now when u execute this package as glob.g1 the output will be Global Variable and Executed Global Variable.

In forms u can call a global variable in pre form and when-new form instance trigger:

:GLOBAL. := null;
in when new form tigger
:Global.Application_id:='PROD';

Create Table form a Report

I got a Scenerio that to create a table and insert values through a concurrent program:

To achieve this,we can do the fallowing:
1.create a procedure in DB to create a table
CREATE OR REPLACE PROCEDURE Create_Table
(errbuf      OUT VARCHAR2,retcode   
OUT VARCHAR2) IS
BEGIN
   Create table table_name(column_name);
END Create_Table;
2.Register this procedure in application as pl/sql stored procedure
3.Now run the program and it will create a table in DB

Now we can call this procedure in our reports sql query so while running the report we can create the table.

select Create_Table(Procedure name) from dual;

Number into word

This is easily done with the following command:

select to_char(to_date('56789','J'),'JSP') from dual
/
TO_CHAR(TO_DATE('56789','J'),'JSP')
--------------------------------------------
FIFTY-SIX THOUSAND SEVEN HUNDRED EIGHTY-NINE

J:- Julian date of 5373484

Query

To check the number of employees created,

select * from per_all_people_f where creation_date > sysdate - 2 ;

Inventory Tables

Oracle Inventory Tables

mtl_system_items
mtl_item_revisions
mtl_item_categories
mtl_material_transactions
mtl_transaction_lots_numbers
mtl_serial_numbers
mtl_system_item_interface
mtl_item_revisions_interface
mtl_item_categories_interface
mtl_transaction_interface
mtl_transaction_lots_interface
mtl_serial_numbers_interface

Order To cash Cycle-Techie reference

 

• Enter the Sales Order

Tables :oe_order_headers_all  -Header_id referencing key

• oe_order_lines_all
• wsh_delivery_details- delivery_id is the number
• wsh_delivery_assignments

*In shipping transaction form order status remains “Ready to Release”.

• Book the Sales Order

Data to be checked in :mtl_demand and mtl_reservations

• Launch Pick Release 

MTL_MATERIAL_TRANSACTIONS

• Ship Confirm

• MTL_MATERIAL_TRANSACTIONS_TEMP
• oe_order_lines_all
• MTL_MATERIAL_TRANSACTIONS
• mtl_transaction_accounts
• wsh_delivery_details
• wsh_delivery_assignments
• MTL_ONHAND_QUANTITIES

• Create Invoice .

       ra_customer_trx_all and ra_customer_trx_lines_all

RA_CUSTOMER_TRX_ALL will have the Invoice header information. The column INTERFACE_HEADER_ATTRIBUTE1 will have the Order Number.

RA_CUSTOMER_TRX_LINES_ALL will have the Invoice lines information. The column INTERFACE_LINE_ATTRIBUTE1 will have the Order Number.

• Create the Receipts either manually or using Auto Lockbox ( In this article we will concentrate on Manual creation)
• Transfer to General Ledger
• Journal Import
• Posting

Procure to Pay cycle-For techies Reference

Procure to Pay cycle covers the following steps:

1. Creating Requisition:

This depends on internal good requirement.This will be raised by an employee only. 

• Inter Org  transfer-From one inventory to other inventory
• Raising Purchase order-No stock supplier should provide

Important tables:

PO_REQUISITION_HEADERS_ALL

PO_REQUISITION_LINES_ALL

PO_REQ_DISTRIBUTIONS_ALL.

Joins can be made using REQUISITION_HEADER_ID and REQUISITION_LINE_ID 

2. Creating Purchase Order

• PO_HEADERS_ALL
• PO_LINES_ALL
• PO_DISTRIBUTIONS_ALL (REQ_HEADER_REFERENCE_NUM in Distributions table is the Requisition number for this PO)
• PO_LINE_LOCATIONS_ALL

The join columns are

header_id and line_id 

3. Creating Receipt

Receipt Tables are:

RCV_SHIPMENT_HEADERS

RCV_SHIPMENT_LINES (Lines Table has PO_HEADER_ID)

4. Creating Invoice in AP
Once the goods is Recieved, We will pay the vendor.Raise an invoice in AP

Invoice Tables:

AP_INVOICES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

Joins made using Invoice_id

Accounting Entries Tables:

AP_ACCOUNTING_EVENTS_ALL

AP_AE_HEADERS_ALL

AP_AE_LINES_ALL

Joins made using Accounting_event_id and ae_header_id

5. Paying the Invoice

Payment Tables:

AP_INVOICE_PAYMENTS_ALL

columns can be joined
invoice_id,accounting_event_id 

AP_PAYMENT_SCHEDULES_ALL:invoice_id

AP_CHECKS_ALL:doc_seq_id is the invoice number

AP_CHECK_FORMATS

AP_BANK_ACCOUNTS_ALL

AP_BANK_BRANCHES

AP_TERMS

6. Transfer, Import and Post to GL

Difference between org_id and organisation_id

Organization ID refers to a unique id of an inventory organization (mtl_parameters)

Org_id refers to a operating unit (hr_operating_units)

Interface Tables

GL INTERFACE TABLES

TNAME TABTYPE
------------------------------ ------- ----------------------
GL_BUDGET_INTERFACE TABLE
GL_DAILY_RATES_INTERFACE TABLE
GL_IEA_INTERFACE TABLE
GL_INTERFACE TABLE
GL_INTERFACE_CONTROL TABLE
GL_INTERFACE_HISTORY TABLE

AP INTERFACE TABLES
TNAME TABTYPE
------------------------------ ------- ------------------------
AP_INTERFACE_CONTROLS TABLE
AP_INTERFACE_REJECTIONS TABLE
AP_INVOICES_INTERFACE TABLE
AP_INVOICE_LINES_INTERFACE TABLE

AR INTERFACE TABLES


TNAME TABTYPE

------------------------------ --------------------------------------
AR_PAYMENTS_INTERFACE_ALL TABLE
AR_TAX_INTERFACE TABLE
HZ_DQM_SYNC_INTERFACE TABLE
HZ_PARTY_INTERFACE TABLE
HZ_PARTY_INTERFACE_ERRORS TABLE


RA_CUSTOMERS_INTERFACE_ALL TABLE
RA_INTERFACE_DISTRIBUTIONS_ALL TABLE
RA_INTERFACE_ERRORS_ALL TABLE
RA_INTERFACE_LINES_ALL TABLE
RA_INTERFACE_SALESCREDITS_ALL TABLE

FA INTERFACE TABLES

TNAME TABTYPE


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


FA_BUDGET_INTERFACE TABLE
FA_INV_INTERFACE TABLE
FA_PRODUCTION_INTERFACE TABLE
FA_TAX_INTERFACE TABLE

INVENTORY INTERFACE TABLES


TNAME TABTYPE


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


MTL_CC_ENTRIES_INTERFACE TABLE
MTL_CC_INTERFACE_ERRORS TABLE
MTL_CI_INTERFACE TABLE
MTL_CI_XREFS_INTERFACE TABLE
MTL_COPY_ORG_INTERFACE TABLE
MTL_CROSS_REFERENCES_INTERFACE TABLE
MTL_DEMAND_INTERFACE TABLE
MTL_DESC_ELEM_VAL_INTERFACE TABLE
MTL_EAM_ASSET_NUM_INTERFACE TABLE
MTL_EAM_ATTR_VAL_INTERFACE TABLE
MTL_INTERFACE_ERRORS TABLE

TNAME TABTYPE


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


MTL_INTERFACE_PROC_CONTROLS TABLE
MTL_ITEM_CATEGORIES_INTERFACE TABLE
MTL_ITEM_CHILD_INFO_INTERFACE TABLE
MTL_ITEM_REVISIONS_INTERFACE TABLE
MTL_ITEM_SUB_INVS_INTERFACE TABLE
MTL_OBJECT_GENEALOGY_INTERFACE TABLE
MTL_RELATED_ITEMS_INTERFACE TABLE
MTL_RESERVATIONS_INTERFACE TABLE
MTL_RTG_ITEM_REVS_INTERFACE TABLE
MTL_SECONDARY_LOCS_INTERFACE TABLE
MTL_SERIAL_NUMBERS_INTERFACE TABLE


TNAME TABTYPE


------------------------------ ------- ------------------------------------
MTL_SO_RMA_INTERFACE TABLE
MTL_SYSTEM_ITEMS_INTERFACE TABLE
MTL_TRANSACTIONS_INTERFACE TABLE
MTL_TRANSACTION_LOTS_INTERFACE TABLE
MTL_TXN_COST_DET_INTERFACE TABLE


PO INTERFACE TABLES

TNAME TABTYPE


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


PO_DISTRIBUTIONS_INTERFACE TABLE
PO_HEADERS_INTERFACE TABLE
PO_INTERFACE_ERRORS TABLE
PO_LINES_INTERFACE TABLE
PO_REQUISITIONS_INTERFACE_ALL TABLE
PO_REQ_DIST_INTERFACE_ALL TABLE
PO_RESCHEDULE_INTERFACE TABLE
RCV_HEADERS_INTERFACE TABLE
RCV_LOTS_INTERFACE TABLE
RCV_SERIALS_INTERFACE TABLE
RCV_TRANSACTIONS_INTERFACE TABLE

BOM INTERFACE TABLES

TNAME TABTYPE


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


BOM_BILL_OF_MTLS_INTERFACE TABLE
BOM_INTERFACE_DELETE_GROUPS TABLE
BOM_INVENTORY_COMPS_INTERFACE TABLE
BOM_OP_RESOURCES_INTERFACE TABLE
BOM_OP_ROUTINGS_INTERFACE TABLE
BOM_OP_SEQUENCES_INTERFACE TABLE
BOM_REF_DESGS_INTERFACE TABLE
BOM_SUB_COMPS_INTERFACE TABLE
CST_COMP_SNAP_INTERFACE TABLE
CST_INTERFACE_ERRORS TABLE
CST_ITEM_COSTS_INTERFACE TABLE
CST_ITEM_CST_DTLS_INTERFACE TABLE
CST_PC_COST_DET_INTERFACE TABLE
CST_PC_ITEM_COST_INTERFACE TABLE

WIP INTERFACE TABLES

TNAME TABTYPE


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


WIP_COST_TXN_INTERFACE TABLE
WIP_INTERFACE_ERRORS TABLE
WIP_JOB_DTLS_INTERFACE TABLE
WIP_JOB_SCHEDULE_INTERFACE TABLE
WIP_MOVE_TXN_INTERFACE TABLE
WIP_SCHEDULING_INTERFACE TABLE
WIP_TXN_INTERFACE_ERRORS TABLE

Lock a table

Locking a table prevents two users to update a table at same time. 

Using locks we can prevent data replications. 

LOCK TABLE employees
   IN EXCLUSIVE MODE;

This exclusive mode enables the user to look into the rows but can update it.

And this command for remote link tables.

LOCK TABLE employees@DB

IN SHARE MODE 

The alternative for this is to use sequence in your table.It will also prevent data duplications. 

Index

Indexing...


why we should go for indexing?


When we are using more then two tables with 'n' number of check with DB.
We can create index for the specific column name to be referenced.


e.g:


create

So now when i run the query in my package it will refer to the index in the ratio 1:1:1:1

i.e everything join conditionally.

This will reduce the time consumption also. index ix_index on per_all_people_f(person_id,effective_end_date,person_type_id,national_identifier);

Date track for update

To pull the date updated by eliminating the previous version we can use this truncate staement for date tracked tables which will give only the updated values.

E.g:- select nvl(pac.segment1,null) from
                         per_person_analyses ppa,
                         per_analysis_criteria pac,
                         per_all_people_f papf
                         where ppa.ANALYSIS_CRITERIA_ID=pac.ANALYSIS_CRITERIA_ID
                         and ppa.id_flex_num =p_id_flex_num and ppa.person_id=p_person_id
                         and papf.person_id=ppa.person_id and
                         trunc(sysdate) between papf.EFFECTIVE_START_DATE
                         and papf.EFFECTIVE_END_DATE and trunc(sysdate) between ppa.DATE_FROM
                         and nvl  (ppa.DATE_TO,trunc(sysdate));

NVL in reports

To choose all the values or the parametr value:

nvl(:p_param,colum_name);

this will replace the param even if we dont pass manually.

Custom Report Notes:

While Registering the report to instance we need to make sure od date format:

1.The default type is YYYY/MM/DD HH24:MI:SS
  go to properties of the parameter and update in format mask.

2.Have to pass P_Conc_Request_id to make sure we can retrive the request id.
 The parameter name and the token name should be the same.

3.In before report trigger have to call the client info if we take inputs from views.

eg: begin
      fnd_client_info.set_org_context('104');
      end;

4.If u need the template to be same for multiple inputs.e.g payslip for all employees to be generated in a template from two or more queries then have to use Link in reports builder.