Date tracking in Oracle E-business is a mechanism to store data based on dates. This helps storage of historical data along with current data in the system.

The date tracked tables in Oracle end with ‘_F’ and have columns EFFECTIVE_START_DATE and EFFECTIVE_END_DATE. These columns along with PERSON_ID form the composite key to identify a person’s record at a given point of time.
For example, to get the person’s record as on current date –

SELECT *

  FROM per_all_people_f

 WHERE sysdate BETWEEN effective_start_date AND effective_end_date;

Modes of Date Tracking:

• Purge: This removes the entire record from the database
• End Date: This updates EFFECTIVE_END_DATE of current active row to today’s date.
• Correction:This mode will update the column. No record history will be retained.
• Update: This will add a new row in the table with the EFFECTIVE_START_DATE as today’s date and EFFECTIVE_END_DATE as EOT(31-DEC-4712 and update current active record with EFFECTIVE_END_DATE as yesterday’s date

If Update mode selected, system will check if there are any future updates entered for the record being updated. If it has been updated in future, system will prompt for two more modes

• UPDATE_CHANGE_INSERT: The changes that the user makes remain in effect until the effective end date of the current record. At that point the future scheduled changes take effect.
• UPDATE_OVERRIDE:The user’s changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.

 

All persons related data are stored in table PER_ALL_PEOPLE_F. This table stores the historic and current information of the employee based on the Date track mode used during the record updates.

Tables below present the same data, but in a different ways :

• PER_ALL_PEOPLE_F: Stores the Person data with Date Track
• PER_PEOPLE_F: a view over PER_ALL_PEOPLE_F with additional security on records. Like, which user can see what all records?
• PER_PEOPLE_X: shows up only the currently active record as of SYSDATE.
• PER_PEOPLE_V: a view used by E-Biz forms to show the data with additional security using security profiles.
• PER_ALL_PEOPLE_D: a view that shows the date track history.

Below are the tables which are linked to PER_ALL_PEOPLE_F using person_id.

• PER_ADDRESSES: stores the address of a person. It’s a DATED table.
• PER_PHONES: stores the Phone numbers of a person.
• PER_CONTACT_RELATIONSHIPS: stores the contacts of a person.
• PER_DISABILITIES_F: stores the disability information of a person.
• PER_PERSON_TYPE_USAGES_F: stores the person type of a person (example: Employee, Ex-employee, Beneficiary etc.)
• PER_QUALIFICATIONS: stores the qualification of a person.

 

SELECT
ooha.order_number,
wdd.DELIVERY_DETAIL_ID ,
wda.delivery_detail_id,
wnd.delivery_id
from
oe_order_headers_all ooha,
oe_order_lines_all oola
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,wsh_delivery_details wdd
where wdd.SOURCE_HEADER_ID=ooha.HEADER_ID
AND wdd.source_line_id = ool.line_id
and ooha.header_id=oola.HEADER_ID
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wnd.delivery_id = wda.delivery_id
and ooha.ORDER_NUMBER=:ORDER_NUMBER

 DECLARE

    -- Declare variables for the script

    l_vendor_id           NUMBER;

    l_vendor_site_id      NUMBER;

    l_item_id             NUMBER;

    l_category_id         NUMBER;

    l_currency_code       VARCHAR2(3);

    l_org_id              NUMBER;

    l_bpo_header_id       NUMBER;

    l_bpo_line_id         NUMBER;

    l_start_date          DATE := SYSDATE;

    l_end_date            DATE := ADD_MONTHS(SYSDATE, 12);

BEGIN

    -- Set variable values as per your requirements

    l_vendor_id := <vendor_id>;

    l_vendor_site_id := <vendor_site_id>;

    l_item_id := <item_id>;

    l_category_id := <category_id>;

    l_currency_code := 'USD';

    l_org_id := <org_id>;

    -- Create Blanket Purchase Order Header

    l_bpo_header_id := po_headers_s.nextval;

    INSERT INTO po_headers_all

        (po_header_id, segment1, type_lookup_code, supplier_id, supplier_site_id,

         currency_code, rate_date, rate_type, agreement_id, agreement_line_id,

         authorization_status, org_id, start_date, end_date, created_by, creation_date,

         last_updated_by, last_update_date, last_update_login, object_version_number)

    VALUES

        (l_bpo_header_id, 'BPO-' || to_char(SYSDATE, 'YYYYMMDD') || '-' || l_bpo_header_id,

         'BLANKET', l_vendor_id, l_vendor_site_id, l_currency_code, SYSDATE,

         'Corporate', NULL, NULL, 'INCOMPLETE', l_org_id, l_start_date, l_end_date,

         fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,

         fnd_global.login_id, 1);

    -- Create Blanket Purchase Order Line

    l_bpo_line_id := po_lines_s.nextval;

    INSERT INTO po_lines_all

        (po_header_id, po_line_id, line_num, item_id, category_id, quantity,

         unit_price, unit_meas_lookup_code, need_by_date, promise_date, org_id,

         created_by, creation_date, last_updated_by, last_update_date, last_update_login,

         object_version_number)

    VALUES

        (l_bpo_header_id, l_bpo_line_id, 1, l_item_id, l_category_id, 100,

         10, 'EACH', l_start_date, l_end_date, l_org_id,

         fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, fnd_global.login_id, 1);

    -- Commit the transaction

    COMMIT;

    DBMS_OUTPUT.PUT_LINE('Blanket Purchase Order created successfully with Header ID ' || l_bpo_header_id || ' and Line ID ' || l_bpo_line_id);

EXCEPTION

    WHEN OTHERS THEN

        -- Rollback the transaction in case of any exception

        ROLLBACK;

        DBMS_OUTPUT.PUT_LINE('Error creating Blanket Purchase Order: ' || SQLERRM);

END;

 /*NEW PUBLIC API - INV_ABC_ASSIGNMENTS_PUB .CREATE_ABC_ASSIGNMENTS IS ADDED TO INSERT/UPDATE ABC ASSIGNMENTS

IF THE ITEM ALREADY EXISTS IT WILL BE UPDATED/ASSIGNED TO THE NEW CLASS, ELSE WILL BE ADDED TO THE CLASS PASSED AS PARAMETER.*/

/* public api PLS file INVPAASS.pls ,INVPAASB.pls  */ 

-- organization V1( master organization, M1 (child org )

-- created item(s) ABC_API_1, ABC_API_2, ABC_API_3

-- This data is from the table MTL_SYSTEM_ITEMS_B

--SEGMENT1                                 INVENTORY_ITEM_ID

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

--ABC_API_1                                           24535205

-- created abc group ABC_API_EX   in M1

-- This data is from the table MTL_ABC_ASSIGNMENT_GROUPS

--ASSIGNMENT_GROUP_NAME                    ASSIGNMENT_GROUP_ID

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

-- ABC_API_EX                                             10250 

-- These are there clases used in  abc group ABC_API_EX  

-- This data is from  MTL_ABC_ASSGN_GROUP_CLASSES 

--ABC_CLASS_NAME                           ABC_CLASS_ID

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

--Class A                                            56 

--CLASS B                                            57

--Class C                                            58 

-- In the demo accept statements will be use to get  the 

-- three required . 

SET SERVEROUTPUT ON ;

ACCEPT 1 NUMBER PROMPT 'Please enter the  inventory_item_id :'

ACCEPT 2 NUMBER PROMPT 'Please enter the  assignment_group_id :'

ACCEPT 3 NUMBER PROMPT 'Please enter the  abc_class_id :'

DECLARE

ITEM_ID NUMBER ;

ASSIGN_GRP_ID NUMBER;

ABC_CLS_ID  NUMBER;

Cursor ITEM IS 

select msi.inventory_item_id

from apps.mtl_system_items_b msi,

     apps.mtl_abc_assignments maa,

     apps.mtl_abc_assignment_groups maag,

     apps.mtl_abc_classes mac,

     apps.mtl_abc_compile_headers mach

where msi.organization_id = :P_SOURCE_ORG_ID

and maa.inventory_item_id = msi.inventory_item_id

and maa.assignment_group_id = maag.assignment_group_id

and maag.organization_id = :P_SOURCE_ORG_ID

and maag.compile_id = mach.compile_id

and maa.abc_class_id = mac.abc_class_id 

and exists (select 1

            from apps.mtl_system_items_b msi2

            where msi2.organization_id = :P_DESTINATION_ORG_ID

            and msi2.inventory_item_id = msi.inventory_item_id);

Cursor TGT IS

  select maag.assignment_group_id,mac.abc_class_id

from apps.mtl_system_items_b msi,

     apps.mtl_abc_assignments maa,

     apps.mtl_abc_assignment_groups maag,

     apps.mtl_abc_classes mac,

     apps.mtl_abc_compile_headers mach

where msi.organization_id = :P_DESTINATION_ORG_ID

and maa.inventory_item_id = msi.inventory_item_id

and maa.assignment_group_id = maag.assignment_group_id

and maag.organization_id = :P_DESTINATION_ORG_ID

and maag.compile_id = mach.compile_id

and maa.abc_class_id = mac.abc_class_id;

-- These varible are to capture the success of the api 

-- and print the failure messages 

LSTATUS VARCHAR2(1); -- return status 

LMSG_COUNT NUMBER;   -- message count 

LMSG_DATA VARCHAR2(240); -- message data 

V_MSG VARCHAR2(4000);

I NUMBER ;

j NUMBER ;

ABC_ITEM  INV_ABC_Assignments_PUB.ABC_ASSIGNMENTS_REC_TYPE;

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

--  abc_assignments_rec_type record type

--  Record type to hold a abc assignment required columns

--                  inventory_item_id

--                  assignmentg_group_id

--                  abc_class_id

BEGIN 

-- these next three line will need to be modified

-- useed in a larger program to add a large number of items programaticly

for i in ITEM LOOP

  For J in TGT LOOP

ABC_ITEM.INVENTORY_ITEM_ID := i.inventory_item_id;

ABC_ITEM.ASSIGNMENT_GROUP_ID := j.assignment_group_id;

ABC_ITEM.abc_class_id := j.abc_class_id ;

 INV_ABC_Assignments_PUB.CREATE_ABC_ASSIGNMENTS( FND_API.G_TRUE,

                                                FND_API.G_TRUE,

                                                1.0,

                                                LSTATUS,

                                                LMSG_COUNT,

                                                LMSG_DATA ,

                                                ABC_ITEM);

                                                

                                                

        DBMS_OUTPUT.PUT_LINE ( 'Status :' || LSTATUS);

         

          IF (lstatus is null  or lstatus = 'S'  ) THEN -- changed for bug 22065914  

              DBMS_OUTPUT.PUT_LINE ( 'SUCCESS');

              COMMIT;

              DBMS_OUTPUT.PUT_LINE ( 'Item_id  ' || ITEM_ID || ' add to group ' || ASSIGN_GRP_ID || ' and Class id  ' || ABC_CLS_ID   );

              ELSE

              DBMS_OUTPUT.PUT_LINE ( 'FAILED, with Status :' || LSTATUS);

              DBMS_OUTPUT.PUT_LINE ( 'message count :' || LMSG_COUNT);

              dbms_output.put_line ( 'message :' || lmsg_data);

          IF ( lmsg_count > 1 ) THEN

             FOR i IN 1 .. lmsg_count

             LOOP

                fnd_msg_pub.get ( p_msg_index => i , p_encoded =>FND_API.G_FALSE,

                p_data => v_msg , p_msg_index_out => j ) ;

                dbms_output.put_line ( 'message :' || v_msg);

             END LOOP ;

           end if;

        END IF;

END LOOP;

 

 END LOOP;

END;

      

 List of Receipts API in Oracle Receivables

Below is the list of some of the Receipt API’s in Oracle Receivables. Receipt APIs provide an extension to existing functionality for creating and manipulating receipts through standard AR Receipts forms and lockboxes.

AR_RECEIPT_API_PUB is the main package that has several procedures to perform different actions.

1] AR_RECEIPT_API_PUB.CREATE_CASH

Use this procedure to create a single cash receipt for payment received in the form of a check or cash.

2] AR_RECEIPT_API_PUB.APPLY & AR_RECEIPT_API_PUB.APPLY_IN_DETAIL

Use these procedures to apply the cash receipts from a customer to an invoice, debit memo, or other debit item.

3] AR_RECEIPT_API_PUB.UNAPPLY

Use this procedure to unapply a cash receipt application against a specified installment of a debit item or payment schedule ID.

4] AR_RECEIPT_API_PUB.CREATE_AND_APPLY 

Use this procedure  to create a cash receipt and apply it to a specified installment of a debit item.

5] AR_RECEIPT_API_PUB.REVERSE

Use this procedure to reverse cash and miscellaneous receipts.

6] AR_RECEIPT_API_PUB.APPLY_ON_ACCOUNT

Use this procedure to apply a cash receipt on account.

7] AR_RECEIPT_API_PUB.UNAPPLY_ON_ACCOUNT

Use this procedure to unapply an on-account application of a specified cash receipt.

8] AR_RECEIPT_API_PUB.ACTIVITY_APPLICATION

Use this procedure to create an activity application on a cash receipt, including Short Term  Debit (STD) and Receipt Write-off applications.

9] AR_RECEIPT_API_PUB. ACTIVITY_UNAPPLICATION

Use this procedure to create a reversal of an activity application on a cash receipt including Short Term Debt and Receipt write-off.

10] AR_RECEIPT_API_PUB.CREATE_MISC

Use this procedure to create a miscellaneous receipt.

11] AR_RECEIPT_API_PUB.APPLY_OPEN_RECEIPT

Use this procedure to apply a cash receipt to another open receipt. Open receipts include unapplied cash, on-account cash, and claim investigation applications.

12] AR_RECEIPT_API_PUB.UNAPPLY_OPEN_RECEIPT

Use this procedure to reverse a payment netting application on a  cash receipt.

 

Collection

Collection is an Ordered group of elements, all of the same type.

Attributes of Collection

1.  FIRST
2.  LAST
3.  COUNT
4.  DELETE
5.  EXTAND
6.  TRIM
7.  NEXT
8.  EXIST
9.  PRIOR
10. LIMIT

Types of Collection

1.  Varray
2.  Nested Table
3.  PL/SQL Table or Associate Array

Varray:-
Varray stands of variable size array.Varray can be stored in the column of table.

Syntax of Varray:- Type type_name is VARRAY(length) of data_type;

Examples of Varray

1.

DECLARE
TYPE L_VAR IS VARRAY(10) OF VARCHAR2(3);
L_VAR1 L_VAR;
BEGIN
L_VAR1:=L_VAR(‘A’,’B’,’C’,’D’,’E’,’F’,’G’,’H’);
FOR I IN 1..8
LOOP
DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
END LOOP;
END;

2.

DECLARE
TYPE L_VAR IS VARRAY(10) OF VARCHAR2(3);
L_VAR1 L_VAR;
BEGIN
L_VAR1:=L_VAR(‘A’,’B’,’C’,’D’,’E’,’F’,’G’,’H’);
FOR I IN L_VAR1.FIRST..L_VAR1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
END LOOP;
END;

3.

DECLARE
TYPE L_VAR IS VARRAY(10) OF VARCHAR2(10);
L_VAR1 L_VAR:= L_VAR();
BEGIN
L_VAR1.EXTEND;
L_VAR1(1):=’HELLO’;
L_VAR1.EXTEND;
L_VAR1(2):= ‘ORACLE’;
L_VAR1.EXTEND;
L_VAR1(3):=’JAVA’;
L_VAR1.EXTEND;
L_VAR1(4):= ‘OAF’;
FOR I IN 1..4
LOOP
DBMS_OUTPUT.PUT_LINE(L_VAR1(I));
END LOOP;
END;

4.

DECLARE
CURSOR C1 IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_HDR IS VARRAY(100) OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR1 L_HDR:= L_HDR();
COUNTER NUMBER:=0;
BEGIN
FOR I IN C1
LOOP
COUNTER:= COUNTER+1;
L_HDR1.EXTEND;
L_HDR1(COUNTER):= I.PO_HEADER_ID;
DBMS_OUTPUT.PUT_LINE('PO_HEADER_ID IS '||L_HDR1(COUNTER));
END LOOP;
END;

5.

DECLARE
CURSOR C1 IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_HDR IS VARRAY(100) OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR1 L_HDR:=L_HDR();
COUNTER NUMBER:=0;
BEGIN
OPEN C1;
FOR I IN 1..100
LOOP
COUNTER:= COUNTER+1;
L_HDR1.EXTEND;
FETCH C1 INTO L_HDR1(COUNTER);
DBMS_OUTPUT.PUT_LINE('PO_HEADER_ID IS '||L_HDR1(COUNTER));
END LOOP;
END;

Nested Table:-
Nested table is like a one-dimensional array.

Syntax of Nested Table:-   TYPE type_name IS TABLE OF data_type;

Example of Nested Table

1.

DECLARE
TYPE L_TAB IS TABLE OF NUMBER;
L_TAB1 L_TAB;
BEGIN
L_TAB1:= L_TAB(1,2,3,4,5);
FOR I IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
END LOOP;
END;

2.

DECLARE
TYPE L_TAB IS TABLE OF NUMBER;
L_TAB1 L_TAB;
BEGIN
L_TAB1:= L_TAB(1,2,3,4,5);
FOR I IN L_TAB.FIRST..L_TAB1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
END LOOP;
END;

3.

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR L_TAB_HDR:= L_TAB_HDR();
COUNTER NUMBER:=0;
BEGIN
FOR I IN C_HDR
LOOP
COUNTER:= COUNTER+1;
L_HDR.EXTEND;
L_HDR(COUNTER):= I.PO_HEADER_ID;
DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
END LOOP;
END;

4.

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.PO_HEADER_ID%TYPE;
L_HDR L_TAB_HDR:= L_TAB_HDR();
COUNTER NUMBER:=0;
BEGIN
OPEN C_HDR;
FOR I IN 1..100
LOOP
COUNTER:= COUNTER+1;
L_HDR.EXTEND;
FETCH C_HDR INTO L_HDR(COUNTER);
DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
END LOOP;
END;

5.

DECLARE
CURSOR C_HDR IS
SELECT *
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL%ROWTYPE;
L_HDR L_TAB_HDR:= L_TAB_HDR();
COUNTER NUMBER:=0;
BEGIN
OPEN C_HDR;
FOR I IN 1..100
LOOP
COUNTER:= COUNTER+1;
L_HDR.EXTEND;
FETCH C_HDR INTO L_HDR(COUNTER);
DBMS_OUTPUT.PUT_LINE('HEADER ID'||CHR(9)||L_HDR(COUNTER).PO_HEADER_ID||CHR(9)||'PO NUMBER'||CHR(9)||L_HDR(COUNTER).SEGMENT1);
END LOOP;
END;

PL/SQL Table:-
PL/SQL Table helps you moves bulk data. They can store column or rows of Oracle Data.

Syntax:- TYPE type_name is TABLE OF data_type
INDEX BY NUMBER/VARCHAR2/PLS_INTRGER/BINARY_INTEGER;

Example:-

1.

DECLARE
TYPE L_TAB IS TABLE OF NUMBER
INDEX BY PLS_INTEGER;
L_TAB1 L_TAB;
BEGIN
L_TAB1(1):=1;
L_TAB1(2):=2;
L_TAB1(3):=3;
L_TAB1(4):=4;
L_TAB1(5):=5;
FOR I IN L_TAB1.FIRST..L_TAB1.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I));
END LOOP;
END;

2.

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID,SEGMENT1
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.SEGMENT1%TYPE
INDEX BY PLS_INTEGER;
L_HDR L_TAB_HDR;
COUNTER NUMBER:=0;
BEGIN
FOR I IN C_HDR
LOOP
COUNTER:= COUNTER+1;
L_HDR(COUNTER):= I.SEGMENT1;
DBMS_OUTPUT.PUT_LINE(L_HDR(COUNTER));
END LOOP;
END;

3.

DECLARE
CURSOR C_HDR IS
SELECT PO_HEADER_ID,SEGMENT1
FROM PO_HEADERS_ALL
WHERE ROWNUM<=100;
TYPE L_TAB_HDR IS TABLE OF PO_HEADERS_ALL.SEGMENT1%TYPE
INDEX BY PLS_INTEGER;
L_HDR L_TAB_HDR;
COUNTER NUMBER:=0;
BEGIN
FOR I IN C_HDR
LOOP
COUNTER:= COUNTER+1;
L_HDR(COUNTER):= I.SEGMENT1;
END LOOP;
FOR J IN 1..COUNTER
LOOP
DBMS_OUTPUT.PUT_LINE(L_HDR(J));
END LOOP;
END;

———————————————–

DECLARE
TYPE L_EMP_REC IS RECORD(
L_EMP_ID NUMBER,
L_NAME VARCHAR2(20),
L_SALARY NUMBER);
TYPE L_TAB IS TABLE OF L_EMP_REC;
L_TAB1 L_TAB;
BEGIN
SELECT EMPLOYEE_ID,LAST_NAME,SALARY
BULK COLLECT INTO L_TAB1
FROM EMPLOYEES;
FOR I IN L_TAB1.FIRST..L_TAB1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I).L_EMP_ID||CHR(9)||L_TAB1(I).L_NAME);
END LOOP;
END;

DECLARE
TYPE L_EMP_REC IS RECORD(L_EMP_ID NUMBER,
L_NAME VARCHAR2(20),
L_DEPT_ID NUMBER,
L_SAL NUMBER,
L_HIRE_DATE DATE
);
TYPE L_TAB IS TABLE OF L_EMP_REC;
L_TAB1 L_TAB;
CURSOR C1 IS
SELECT EMPLOYEE_ID,
LAST_NAME,
DEPARTMENT_ID,
SALARY,
HIRE_DATE
FROM EMPLOYEES;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO L_TAB1;
FOR I IN L_TAB1.FIRST..L_TAB1.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(L_TAB1(I).L_EMP_ID||CHR(9)||L_TAB1(I).L_NAME);
END LOOP;
END;