Oracle Apps Technical Database Full Method Of Knowing easy execution and Writing Query Normal way

AR Interface Full Process

About this Document

Purpose

The AR Invoice has to be created through Open Interface.

The Procedure Xx_Ar_Invoice_Creation_Opi is developed with oracle seeded interface tables to create AR Invoice.

1 Topical Essay

The PL/SQL Program is developed to create AR Invoice via open interface tables.

1.1 Basic Business Needs

Oracle AR Invoice has to be created automatically based on custom table data, when execute the stored procedure.

1.2 Definitions

Instead of Creating AR Invoice manually from Oracle front end screen, Open Interface program has been developed to create automatically through the stored Procedure.

1.3 Application Logic

User enters the requested details to create AR Invoice into Custom Table. When execute the Program Xx_Ar_Invoice_Creation_Opi, it creates AR Invoice against the given customer with mentioned line items.

1.4 Design Details

Custom Table xx_ar_invoice_table is Created to get the below input from users.

· HNO

· Batch Source Name

· Transaction Type

· Term

· Bill_To_Customer_Id

· Bill_To_Customer_Address_ID

· Currency Code

· Org Id

This table has unique HNO(Header No) column.

And the table xx_ar_invoice_line_table is created to get the line item details. This table consists more than one line details for a single AR Invoice Header.

· HNO

· LNO

· Item

· Quanity

· Unit Selling Price

The Lines in xx_ar_invoice_line_table has the unique LNO(HNO,LineNum) Column. And the HNO column with respect to the table xx_ar_invoice_table.HNO.

The user can enter the line item details only when the HNO exist in the Header( xx_ar_invoice_table) table.

When executes the procedure Xx_Ar_Invoice_Creation_Opi, it picks up the records from the custom Header (xx_ar_invoice_table) table with Created_Flag=’N’ .

This Program creates the AR Invoice only for the records having Nvl(Created_Flag,'N')='N' And Customer_Trx_Id Is Null in the table Xx_Ar_Invoice_Table .

This Procedure genarates Batch_Source_Name,Transaction_Type_id,Term_id, Revenue Account and Receivables Account for the input data.

Next the procedure inserts the Line Item Details from the table Xx_Ar_Invoice_Line_Table with respect to the column Xx_Ar_Invoice_Table.hno into the interface tables Ra_Interface_Lines_All And Ra_Interface_Distributions_All.This program inserts two records per line item with REC Account and REV Account.

Once the above values are inserted into interface tables, created_flag to be updated as ‘Y’ and procedure submits the interface program Autoinvoice Master Program for the given batch_source. Once the program is completed Customer_Trx_Id in custom table Xx_Ar_Invoice_Table to be updated by the value in the column Customer_Trx_Id in the table Ra_Interface_Lines_All.

2 Concurrent Program Details

Basic Purpose

This Procedure Creates AR Invoice through Open Interface.

2.1 SQL & PL/SQL Statements(Optional)

Table:

create table xx_ar_invoice_table

(

hno number,

batch_source_name varchar2(50),

transaction_type varchar2(50),

term varchar2(10),

Bill_to_Customer_id number,

Bill_to_Customer_address_id number,

currency_Code Varchar2(10),

Org_id Number,

inserted_flag varchar2(1),

created_flag varchar2(1),

customer_trx_id varchar2(20),

created_by number,

creation_date date,

last_updated_by number,

last_updated_date date)

create table xx_ar_invoice_line_table

(

hno number,

lno number,

Item Varchar2(50),

Quantity Number,

Unit_Selling_price number,

created_by number,

creation_date date,

last_updated_by number,

last_updated_date date)

ALTER TABLE xx_ar_invoice_table

add CONSTRAINT xx_ar_invoice_table_pk PRIMARY KEY (HNO);

ALTER TABLE xx_ar_invoice_line_table

add CONSTRAINT xx_ar_invoice_line_table_fk FOREIGN KEY (HNO) references xx_ar_invoice_table(HNO)

ALTER TABLE xx_ar_invoice_line_table

add CONSTRAINT xx_ar_invoice_line_table_pk primary KEY (HNO,LNO)

Create Or Replace Procedure Xx_Ar_Invoice_Creation_Opi

V_Ar_Line_Id Number;

V_Ar_Line_Dist_Id Number;

V_Item_Id Number;

V_Uom_Code Varchar2(10);

V_Conc_Req_Id Number;

V_Batch_Source_Id Number;

V_Customer_Trx_Id Number;

V_Phase Varchar2(100);

V_Status Varchar2(100);

V_Dev_Phase Varchar2(100);

V_Dev_Status Varchar2(100);

V_Message Varchar2(2000);

V_Wait_Outcome Boolean;

V_Errcode Varchar2(100);

V_Errmsg Varchar2(500);

V_Gl_Rev Number;

V_Gl_Rec Number;

V_Cust_Trx_Type_Id Number;

V_Term_Id Number;

V_Cust_Trx_Id Number;

V_Interface_Id Varchar2(50);

Cursor C1 Is

Select * From Xx_Ar_Invoice_Table Where Nvl(Inserted_Flag,'N')='N'

And Nvl(Created_Flag,'N')='N'

And Customer_Trx_Id Is Null

Order By Hno;

Begin

For J In C1 Loop

Select Batch_Source_Id

Into V_Batch_Source_Id

From Ra_Batch_Sources_All

Where Name = J.Batch_Source_Name And Org_Id = J.Org_Id;

Select Gl_Id_Rev,Gl_Id_Rec,Cust_Trx_Type_Id Into V_Gl_Rev,V_Gl_Rec,V_Cust_Trx_Type_Id

From Ra_Cust_Trx_Types_All Where Name=J.Transaction_Type;

Select Term_Id Into V_Term_Id From Apps.Ra_Terms Where Name=J.Term;

Declare

Cursor C2 Is

Select

Xait.Batch_Source_Name,

Xait.Bill_To_Customer_Id,

Xait.Bill_To_Customer_Address_Id,

Xait.Term,

Xait.Org_Id,

Xait.Currency_Code,

Xait.Hno,

Xait.Transaction_Type,

Xailt.Item,

Xailt.Quantity,

Xailt.Unit_Selling_Price

From Xx_Ar_Invoice_Table Xait , Xx_Ar_Invoice_Line_Table Xailt

Where Xait.Hno=Xailt.Hno

And Nvl(Xait.Inserted_Flag,'N')='N' And Nvl(Xait.Created_Flag,'N')='N' And Xait.Customer_Trx_Id Is Null

And Xait.Hno=J.Hno;

Begin

V_Cust_Trx_Id:=100;

For I In C2 Loop

Select Ra_Customer_Trx_Lines_S.Nextval Into V_Ar_Line_Id From Dual;

Select Inventory_Item_Id,Primary_Uom_Code Into V_Item_Id,V_Uom_Code From Inv.Mtl_System_Items_B

Where Segment1=I.Item And Rownum=1;

If V_Item_Id Is Not Null

And V_Batch_Source_Id Is Not Null

And V_Gl_Rev Is Not Null

And V_Gl_Rec Is Not Null

And V_Cust_Trx_Type_Id Is Not Null

And V_Term_Id Is Not Null Then

Insert Into Ra_Interface_Lines_All

(Interface_Line_Id,

Batch_Source_Name,

Line_Type,

Cust_Trx_Type_Id,

Cust_Trx_Type_Name,

Trx_Date,

Gl_Date,

Currency_Code,

Term_Id,

Orig_System_Bill_Customer_Id,

Orig_System_Bill_Address_Id,

Quantity,

Amount,

Description,

Conversion_Type,

Conversion_Rate,

Interface_Line_Context,

Interface_Line_Attribute1,

Interface_Line_Attribute2,

Org_Id,

Inventory_Item_Id,

Uom_Code,

Customer_Trx_Id

)

Values

(

V_Ar_Line_Id,

I.Batch_Source_Name,

'LINE',

V_Cust_Trx_Type_Id,

I.Transaction_Type,

Sysdate,

I.Currency_Code,

V_Term_Id,

I.Bill_To_Customer_Id,

I.Bill_To_Customer_Address_Id,

I.Quantity,

I.Quantity * I.Unit_Selling_Price,

I.Item,

'User',

'OPEN INVOICES',

'Invoice'||'-'||V_Ar_Line_Id,

I.Org_Id,

V_Item_Id,

V_Uom_Code,

V_Cust_Trx_Id

);

Commit;

Insert Into Ra_Interface_Distributions_All

(

Interface_Distribution_Id,

Interface_Line_Id,

Account_Class,

Amount,

Code_Combination_Id,

Percent,

Interface_Line_Context,

Interface_Line_Attribute1,

Org_Id

)

Values

(

Ra_Cust_Trx_Line_Gl_Dist_S.Nextval,

V_Ar_Line_Id,

'REC',

I.Quantity * I.Unit_Selling_Price,

V_Gl_Rec,

100,

'OPEN INVOICES',

'Invoice'||'-'||V_Ar_Line_Id,

I.Org_Id

);

Commit;

Insert Into Ra_Interface_Distributions_All

(

Interface_Distribution_Id,

Interface_Line_Id,

Account_Class,

Amount,

Code_Combination_Id,

Percent,

Interface_Line_Context,

Interface_Line_Attribute1,

Org_Id

)

Values

(

Ra_Cust_Trx_Line_Gl_Dist_S.Nextval,

V_Ar_Line_Id,

'REV',

I.Quantity * I.Unit_Selling_Price,

V_Gl_Rev,

100,

'OPEN INVOICES',

'Invoice'||'-'||V_Ar_Line_Id,

I.Org_Id

);

Commit;

V_Interface_Id:= 'Invoice'||'-'||V_Ar_Line_Id;

Dbms_Output.Put_Line('Item :'||I.Item||'Organization Id :'||I.Org_Id||' Inseted In Interface Iable');

Else

Dbms_Output.Put_Line('Item :'||I.Item||'Organization Id :'

||I.Org_Id||' does not Exist In Item Master or Given Batch Source Is Not Valid'

||' Or Check Transaction Type And Gl Code Combination Ids Are Valid');

End If;

End Loop;

End;

Update Xx_Ar_Invoice_Table Set Inserted_Flag='Y' Where Hno=J.Hno And Nvl(Inserted_Flag,'N')='N'

And Nvl(Created_Flag,'N')='N'

And Customer_Trx_Id Is Null;

Commit;

Mo_Global.Init('AR');

Mo_Global.Set_Policy_Context ('S', 82);

Fnd_Global.Apps_Initialize(1184,50369,7000);--- Here U Will Have Code For Initializing Global Variables

V_Conc_Req_Id:=Fnd_Request.Submit_Request(Application => 'AR',

Program => 'RAXMTR',

Description => 'Autoinvoice Master Program',

Start_Time => To_Char(Sysdate, 'DD/MM/YYYY HH:MI:SS'),

Sub_Request => False,

Argument1 => '1',

Argument2 => J.Org_Id,

Argument3 => V_Batch_Source_Id,

Argument4 => J.Batch_Source_Name,

Argument5 => To_Char(Sysdate,'DD/MM/YYYY HH:MI:SS'),

Argument6 => '',

Argument7 => '',

Argument8 => '',

Argument9 => '',

Argument10 => '',

Argument11 => '',

Argument12 => '',

Argument13 => '',

Argument14 => '',

Argument15 => '',

Argument16 => '',

Argument17 => '',

Argument18 => '',

Argument19 => '',

Argument20 => '',

Argument21 => '',

Argument22 => '',

Argument23 => '',

Argument24 => '',

Argument25 => '',

Argument26 => 'Y',

Argument27 => ''

);

Commit;

Loop

V_Wait_Outcome := Fnd_Concurrent.Get_Request_Status(Request_Id => V_Conc_Req_Id,

Phase => V_Phase,

Status => V_Status,

Dev_Phase => V_Dev_Phase,

Dev_Status => V_Dev_Status,

Message => V_Message);

Exit When Upper(V_Phase) = 'COMPLETED';

End Loop; --Concurrent Phase Loop End

Dbms_Output.Put_Line('Interface Program Completed');

Select Customer_Trx_Id Into V_Customer_Trx_Id From Ra_Customer_Trx_Lines_All

Where Interface_Line_Attribute1=V_Interface_Id;

If V_Customer_Trx_Id Is Not Null Then

Update Xx_Ar_Invoice_Table Set Created_Flag='Y',Customer_Trx_Id=V_Customer_Trx_Id Where Hno=J.Hno And Nvl(Inserted_Flag,'N')='Y'

And Nvl(Created_Flag,'N')='N'

And Customer_Trx_Id Is Null;

Commit;

End If;

End Loop;

Exception

When Others Then

V_Errcode := Sqlcode;

V_Errmsg := Sqlerrm;

Insert Into Xx_Error_Table Values ('AR','XX_AR_INVOICE_CREATION_OPI',V_Errcode,V_Errmsg,Sysdate,Fnd_Global.User_Id);

Dbms_Output.Put_Line('Errcode :'||Sqlcode||'Error Message :'||Sqlerrm);

Commit;

End;

2.2 Prerequisites & Dependencies

Custom Tables Xx_Ar_Invoice_Table, Xx_Ar_Invoice_Line_Table are created to get the user Input And Xx_Error_Table is used in exception part of the above procedure to store the error details.

2.3 Output