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

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

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;

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;

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;

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;

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

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;

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;

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;

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;

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:-

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;

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;

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

Oracle Apps -Learn and grow

Collection

Search This Blog

Popular Posts

Blog Archive

Visotros

Total Pageviews

Label Cloud

Popular Posts