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;

Let us say you are having Inventory Store.

You have Inventory Stores in two different countries like India and USA

You have installed Oracle Apps single instance and entered all the suppliers information, customers data, tax rules etc.

And your business requirement is like this, all the rules and the data corresponding to indian store shouldn't be applicable/available to American store and vice versa.

So to meet the above requirements you will define two operating units one for india and one for USA.

Now each operating unit will have an ID known as ORG ID.

And we use ORG ID to secure our business data.

Now to do transactions for indian operations you need to define one responsibility and attach indian ORG ID to this responsibility.By this an indian will not see any data/rules applicable to USA.

Similarly you will define another responsibility for USA and attach USA’s ORG ID to this responsibility so that an American's will not see any data/rules corresponding to India.

This is the concept of ORG ID.

Now let us see what is Organization ID

Within India you have got various branches in different parts of the country in different states like AP,UP,MP etc.

And the business requirement is like this in AP you will maintain stock of Rice,UP for Wheat and MP for Maize.

To meet this business requirement you will define 3 Inventory Organizations under Indian Operating Unit to maintain the respective stocks of the states..

Each Inventory Organization will have an Organization ID.

This is the outline of the differences between ORG ID and Organization ID.

Navigation: Inventory (Responsibility) >> Setup >> Organizations >> Organizations

Both operating unit and inventory organization are organizations defined in the Oracle EBS (E-Business Suite). They differ in the organization classification which is highlighted in the red box in below images.

The information about all the organizations (including inventory organizations) are available in HR_ORGANIZATION_UNITS table.

The information about all the operating units are available in the HR_OPERATING_UNITS table.

An inventory organization is uniquely identified by ORGANIZATION_ID.

An operating unit is uniquely identified by ORG_ID in different tables for which the value is the ORGANIZATION_ID from HR_OPERATING_UNITS table.

Queries:

SELECT *

  FROM hr_organization_units;

SELECT *

  FROM hr_operating_units;

 SELECT
       fu.user_name                            CP_RUNBY
     , to_char(fcr.request_date,'DD-Mon-YYYY') CP_RUNDATE
FROM
       fnd_concurrent_requests fcr
     , fnd_user                fu
WHERE
       fcr.requested_by   =fu.user_id
       AND fcr.request_id = fnd_global.conc_request_id

 SELECT DISTINCT
       PAPF.FULL_NAME APPROVED_BY
     , AIAH.APPROVER_COMMENTS
     , TO_CHAR(AIAH.LAST_UPDATE_DATE,'DD-MON-YYYY') APPROVAL_DATE
     , APPROVAL_HISTORY_ID
FROM
       AP_INV_APRVL_HIST_ALL AIAH
     , FND_USER              FUS
     , PER_ALL_PEOPLE_F      PAPF
WHERE
       INVOICE_ID          = :INVOICE_ID
       AND RESPONSE        = 'APPROVED'
       AND AIAH.CREATED_BY = FUS.USER_ID
       AND FUS.EMPLOYEE_ID = PAPF.PERSON_ID