/*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

 Select distinct
       AIA.INVOICE_NUM
     , AIA.INVOICE_DATE
     , JTD.TAX_CATEGORY_NAME GST_TAX_CAT
     , JTD.LINE_AMT
from
       APPS.JAI_TAX_DET_FCT_LINES_V JTD
     , AP_INVOICES_ALL              AIA
where
       AIA.INVOICE_ID               =:P_INVOICE_ID
       and JTD.TRX_NUMBER           = AIA.INVOICE_NUM
       and JTD.TAX_CATEGORY_NAME like 'GST%'

 SELECT
       JAW.REGIME_CODE
     , JAW.ACTUAL_SECTION_CODE
     , JAI.TDS_TAX_RATE
     , JAW.TAX_AMOUNT
     , JAW.PROCESS_MESSAGE
     , (
              SELECT
                     CONCATENATED_SEGMENTS
              FROM
                     GL_CODE_COMBINATIONS_KFV
              WHERE
                     CODE_COMBINATION_ID=DIST_CODE_COMBINATION_ID
       )
       ACCOUNT
     , JTC.TAX_CATEGORY_NAME
     , JCT.TAX_NAME
FROM
       JAI_AP_WTHLD_INV_TAXES JAW
     , JAI_TAX_CATEGORIES     JTC
     , JAI_CMN_TAXES_ALL      JCT
     , JAI_AP_TDS_INVOICES    JAI
WHERE
       JAI.INVOICE_ID         =JAW.INVOICE_ID
       AND JAI.INVOICE_ID     = :P_INVOICE_ID
       AND JAW.TAX_CATEGORY_ID=JTC.TAX_CATEGORY_ID
       AND JCT.TAX_ID         =JAW.TAX_TYPE_ID

SELECT DISTINCT

       hold_date

     , hold_lookup_code

     , hold_reason

FROM

       ap_holds_all

WHERE

       invoice_id     = :P_INVOICE_ID

       and STATUS_FLAG='S'

SELECT DISTINCT

       AILA.LINE_TYPE_LOOKUP_CODE LINE_TYPE

     , AILA.DESCRIPTION           INV_LINE_DESCRIPTION

     , (

              SELECT DISTINCT

                     SEGMENT1

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

       PO_NUM

     , (

              SELECT DISTINCT

                     TYPE_LOOKUP_CODE

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

       PO_TYPE

     , (

              SELECT DISTINCT

                     COMMENTS

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

       PO_DESCRIPTION

     , (

              SELECT DISTINCT

                     CREATION_DATE

              FROM

                     PO_HEADERS_ALL PHA

              WHERE

                     AILA.PO_HEADER_ID=PHA.PO_HEADER_ID

       )

            PO_CREATION_DATE

     , NULL NULL_RECEIPT_NUM

     , (

              SELECT DISTINCT

                     LOCATION_CODE

              FROM

                     HR_LOCATIONS HL

              WHERE

                     AILA.SHIP_TO_LOCATION_ID=HL.LOCATION_ID

       )

                                  SHIP_TO

     , NULL                       QTY_AP_LINES

     , NULL                       RATE_AP_LINES

     , AILA.AMOUNT                LINE_AMOUNT

     , APDA.LINE_TYPE_LOOKUP_CODE DIST_TYPE

     , APDA.DESCRIPTION           DIST_DESCRIPTION

     , (

              SELECT DISTINCT

                     CONCATENATED_SEGMENTS

              FROM

                     GL_CODE_COMBINATIONS_KFV

              WHERE

                     APDA.DIST_CODE_COMBINATION_ID=CODE_COMBINATION_ID

       )

                   CHARGE_ACCOUNT

     , APDA.AMOUNT DIST_AMOUNT

     , (

              SELECT DISTINCT

                     SEGMENT1

              FROM

                     PA_PROJECTS

              WHERE

                     PROJECT_ID=APDA.PROJECT_ID

       )

       PROJECT

     , (

              SELECT DISTINCT

                     NAME

              FROM

                     PA_PROJECTS

              WHERE

                     PROJECT_ID=APDA.PROJECT_ID

       )

       PROJECT_NAME

     , (

              SELECT DISTINCT

                     DESCRIPTION

              FROM

                     PA_PROJECTS

              WHERE

                     PROJECT_ID=APDA.PROJECT_ID

       )

       PROJECT_DESC

     , (

              SELECT DISTINCT

                     TASK_NAME

              FROM

                     PA_TASKS

              WHERE

                     TASK_ID=APDA.TASK_ID

       )

       TASK

     , APDA.EXPENDITURE_TYPE

     , (

              SELECT DISTINCT

                     NAME

              FROM

                     HR_ORGANIZATION_UNITS

              WHERE

                     APDA.EXPENDITURE_ORGANIZATION_ID=ORGANIZATION_ID

       )

                                                   EXPENDITURE_ORGANIZATION

     , TO_CHAR(APDA.ACCOUNTING_DATE,'DD-MON-YYYY') ACCOUNTING_DATE

FROM

       AP_INVOICES_ALL              AIA

     , AP_INVOICE_LINES_ALL         AILA

     , AP_INVOICE_DISTRIBUTIONS_ALL APDA

WHERE

       APDA.INVOICE_ID(+) = AILA.INVOICE_ID

       AND AIA.INVOICE_ID =AILA.INVOICE_ID

       AND AIA.INVOICE_ID =NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)

       AND AIA.INVOICE_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE) AND NVL(TO_DATE(:P_DATE_TO,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE)

SELECT DISTINCT  TO_CHAR(SYSDATE,'DD-MON-YYYY') RUNDATE,
    AIA.INVOICE_ID,
    AIA.INVOICE_NUM,
    ASU.VENDOR_NAME,
    ASSA.VENDOR_SITE_CODE,
    ASU.SEGMENT1                   SUPPLIER_NUM,
    AIA.INVOICE_ID                 VOUCHER,
    AIA.ATTRIBUTE5                 INV_CATEGORY,
    AT.NAME                        PAYYMT_TERM,
    AIA.SOURCE,
    AIA.DESCRIPTION INV_DESCRIPTION,
    AIA.INVOICE_TYPE_LOOKUP_CODE   INVOICE_TYPE,
    TO_CHAR(AIA.INVOICE_DATE,'DD-MON-YYYY') INVOICE_DATE,
    TO_CHAR(AIA.TERMS_DATE,'DD-MON-YYYY')TERMS_DATE
    ,TO_CHAR(AIA.GL_DATE,'DD-MON-YYYY')GL_DATE,
    AIA.INVOICE_AMOUNT,
    AIA.AMOUNT_PAID,
    ( AIA.INVOICE_AMOUNT - AIA.AMOUNT_PAID ) BALANCE,
    NVL(JAI.TDS_AMOUNT,0),
    TO_CHAR(APSA.DUE_DATE,'DD-MON-YYYY') DUE_DATE,
    APSA.GROSS_AMOUNT,
    APSA.AMOUNT_REMAINING,
    APSA.HOLD_FLAG,
    APSA.IBY_HOLD_REASON           HOLD_REASON,
    APSA.PAYMENT_METHOD_CODE PAYMENT_METHOD,
    APSA.PAYMENT_STATUS_FLAG       PAYMENT_STATUS
,DECODE (AIA.WFAPPROVAL_STATUS,'WFAPPROVED','APPROVED','MANUALLY APPROVED','APPROVED','NOT REQUIRED','APPROAL NOT REQUIRED', 'REQUIRED', 'NEED APPROVAL', 'NOT REQUIRED', AIA.WFAPPROVAL_STATUS
) APPROVAL_STATUS
   ,DECODE (AP_INVOICES_PKG.GET_POSTING_STATUS( AIA.INVOICE_ID),'N','NO','Y','YES') ACCOUNTED
   ,DECODE(APPS.AP_INVOICES_PKG.GET_APPROVAL_STATUS(AIA.INVOICE_ID,AIA.INVOICE_AMOUNT,AIA.PAYMENT_STATUS_FLAG,AIA.INVOICE_TYPE_LOOKUP_CODE),
                 'APPROVED',     'VALIDATED',
                 'FULL',         'FULLY VALIDATD',
                 'UNAPPROVED',   'UNVALIDATED',
                 'NEEDS REAPPROVAL','NEEDS REVALIDATION',
                 'NEVER APPROVED','NEVER VALIDATED',
                 'CANCELLED',    'CANCELLED',
                 'UNPAID',       'UNPAID',
                 'AVAILABLE',    'AVAILABLE'
                 )VALIDATED 
    FROM
    AP_SUPPLIERS               ASU,
    AP_SUPPLIER_SITES_ALL      ASSA,
    AP_INVOICES_ALL            AIA,
    AP_INVOICE_LINES_ALL       AILA,
    AP_TERMS                   AT,
    JAI_AP_TDS_INVOICES        JAI,
    AP_PAYMENT_SCHEDULES_ALL   APSA
    ,AP_INVOICE_DISTRIBUTIONS_ALL APDA
WHERE
    1 = 1
    AND ASU.VENDOR_ID = ASSA.VENDOR_ID
    AND AILA.INVOICE_ID = AIA.INVOICE_ID
    AND AIA.VENDOR_ID = ASU.VENDOR_ID
    AND AIA.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID
    AND AT.TERM_ID = AIA.TERMS_ID
    AND JAI.INVOICE_ID (+) = AIA.INVOICE_ID
    AND APSA.INVOICE_ID = AIA.INVOICE_ID
    AND APDA.INVOICE_ID(+) = AIA.INVOICE_ID
    AND AIA.INVOICE_NUM = NVL(:P_INVOICE_NUM,AIA.INVOICE_NUM)
AND AIA.INVOICE_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE) AND NVL(TO_DATE(:P_DATE_TO,'YYYY/MM/DD HH24:MI:SS'),AIA.INVOICE_DATE)

TCA
Sr. No.	Detail	API Name	Module
1	Create Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
2	Create a Customer Account Relationship API	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE	TCA
3	API to update a Customer Account Relationship TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE	TCA
4	API to Update a Customer Account TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT	TCA
5	Create Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE	TCA
6	API to Create a Customer Site TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE	TCA
7	API to Create a Customer Site Use TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE	TCA
8	API to Create a Party Site TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE	TCA
9	API to Create a Party Site Use TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE	TCA
10	API to create a Person Type Party and Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
11	API to Create a Phone Number (Contacts) TCA R12	HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT	TCA
12	API to create customer Addresses in TCA R12	HZ_LOCATION_V2PUB.CREATE_LOCATION	TCA
13	API to create Group in TCA R12	HZ_PARTY_V2PUB.CREATE_GROUP	TCA
14	API to populate loc_id HZ_LOCATIONS R12	HZ_TAX_ASSIGNMENT_V2PUB. CREATE_LOC_ASSIGNMENT	TCA
15	API to update a Customer Profile TCA R12	HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE	TCA
16	API to Update Customer Address in Oracle TCA R12	HZ_LOCATION_V2PUB.UPDATE_LOCATION	TCA
17	Assign Receipt method to Customer via API	hz_payment_method_pub.create_payment_method	TCA
18	Create Party of type Organization in Oracle TCA using API	hz_party_v2pub.create_organization	TCA
AP
19	API to apply hold on AP invoice in R12	AP_HOLDS_PKG.INSERT_SINGLE_HOLD	AP
20	API to cancel single AP invoice	AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE	AP
21	API to find AP INVOICE Status	AP_INVOICES_PKG.GET_APPROVAL_STATUS	AP
22	API to release hold on AP invoice in R12	AP_HOLDS_PKG.RELEASE_SINGLE_HOLD	AP
23	Credit Memo Creation via API	ar_credit_memo_api_pub.create_request	AP
24	API to create Supplier in R12	pos_vendor_pub_pkg.Create_Vendor	AP
25	API to create supplier site in R12	pos_vendor_pub_pkg.create_vendor_site	AP
26	API to create or update Supplier Hub UDA attr	pos_vendor_pub_pkg.process_user_attrs_data
PO
27	API for Cancelling the Purchase Order (PO) Document	PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT	PO
28	API to Update Purchase Order Document (PO)	PO_CHANGE_API1_S.UPDATE_PO	PO
AOL
29	API to load value in value set	FND_FLEX_VALUES_PKG.LOAD_ROW	AOL
30	API to submit concurrent program	fnd_request.submit_request	AOL