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

Sales Order Interface Full Process

Sales Order Custom Table

CREATE TABLE APPS.XXTEAM_ORD_IM_TL

(

OU_NAME VARCHAR2(100 BYTE),

CUSTOMER_NAME VARCHAR2(240 BYTE),

BILL_TO VARCHAR2(50 BYTE),

SHIP_TO VARCHAR2(50 BYTE),

TRANSACTION_TYPE VARCHAR2(100 BYTE),

CUSTOMER_PO_NUMBER VARCHAR2(100 BYTE),

ORDERED_DATE DATE,

PRICE_LIST VARCHAR2(150 BYTE),

PAYMENT_TERM VARCHAR2(50 BYTE),

WARA_HOUSE VARCHAR2(50 BYTE),

LINE_NUMBER NUMBER,

ITEM_CODE VARCHAR2(50 BYTE),

UOM VARCHAR2(20 BYTE),

LINE_TYPE VARCHAR2(50 BYTE),

QUANTITY NUMBER,

PRICE_UNIT NUMBER(10,2),

REQUEST_DATE VARCHAR2(50 BYTE),

SCHEDULED DATE,

CREATION_DATE DATE,

ERROR_MESSAGE VARCHAR2(3000 BYTE),

INTERFACED_FLAG VARCHAR2(10 BYTE) DEFAULT 'N',

TAX_FLAG VARCHAR2(10 BYTE) DEFAULT 'N',

REQUEST_ID NUMBER,

TAX_CATEGORY VARCHAR2(100 BYTE),

HEADER_ID NUMBER,

LINE_ID NUMBER

)

Import Sales Order Details Through Procedure and Validate with Open Interface

CREATE OR REPLACE Procedure APPS.XXX_ORDER_IMPORT(Errbuf Out Varchar2,Retcode Out Varchar2)

V_Errcode Varchar2(100);

V_Errmsg Varchar2(500);

V_Request_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_Count Number;

V_Count1 Number;

I_Error_Message Varchar2(3000);

I_Price_List_Id Number;--Qp_List_Headers_Tl@Apps@Apps.List_Header_Id%Type;

I_Term_Id Number;--Ra_Terms_Tl@Apps.TERM_ID%Type;

I_Order_Type_Id Number;--Oe_Transaction_Types_Tl@Apps.Transaction_Type_Id%Type;

I_Order_Source_Id Number;--Oe_Order_Sources@Apps.Order_Source_Id%Type;

I_Inventory_Item_Id Number;--Mtl_System_Items_B@Apps.Inventory_Item_Id%Type;

I_Organization_Id Number;--Org_Organization_Definitions@Apps.Organization_Id%Type;

I_Uom_Code Varchar2(50);--;Mtl_Unit_Of_Measure_Vl@Apps.Uom_Code%Type;

I_Line_Type_Id Number;--Oe_Transaction_Types_TL@Apps.Trasnaction_Type_Id%Type;

I_Currency_Code Varchar2(50);--Fnd_Currencies_Vl@Apps.Currency_Code%Type;

I_Customer_Id Number;--Ra_Customers@Apps.Customer_Id%Type;

I_Ship_To_Org_Id Number(10);

I_Bill_To_Org_Id Number(10);

I_Header_Id Number;

I_Line_Id Number;

I_Org_Id Number;

I_Sold_To_Org_Id Number;

I_SlNo Number :=0;

I_Tax_Id Number;

I_Tax_Rate Number;

V_HEADER_ID Number;

V_LINE_ID Number;

V_Line_Amount Number;

p_operating_unit VARCHAR2(20) := NULL;

p_order_source VARCHAR2(20) := '0';

p_orig_sys_document_ref VARCHAR2(20) := NULL;

p_operation_code VARCHAR2(20) := NULL;

p_validate_only VARCHAR2(20) := 'N';

p_debug_level VARCHAR2(20) := '1';

p_num_instances VARCHAR2(20) := '4';

p_sold_to_org_id VARCHAR2(20) := NULL;

p_sold_to_org VARCHAR2(20) := NULL;

p_change_sequence VARCHAR2(20) := NULL;

p_perf_param VARCHAR2(20) := 'Y';

p_rtrim_data VARCHAR2(20) := 'N';

p_pro_ord_with_null_flag VARCHAR2(20) := 'Y';

p_default_org_id VARCHAR2(20) := '87';

p_validate_desc_flex VARCHAR2(20) := 'N';

Cursor C_Order

Select * From XXTEAM_ORD_IM_TL;

Begin

For ORD In C_Order

Loop

If I_SlNo=0 Then

I_SlNo :=1;

End if;

If I_SlNo>0 Then

I_SlNo :=I_SlNo+1;

End if;

Begin

Select Oe_Headers_S.Nextval Into I_Header_Id From Dual;

Exception

When Others Then

I_Error_Message:='HEADER IS INVALID';

End;

Begin

Select Oe_Headers_S.Nextval Into I_Line_Id From Dual;

Exception

When Others Then

I_Error_Message:= 'LINE ID IS INVALID';

End;

Begin

Select Organization_Id Into I_Org_Id

From Hr_operating_Units Where Name =ORD.OU_NAME;

Exception

When Others Then

I_Error_Message:= 'OPERATING UNITS IS INVALID';

End;

Begin

Select List_Header_Id Into I_Price_List_Id

From Qp_List_Headers_Tl Where Name='WATER PRICELIST';

Exception

When Others Then

I_Error_Message:= 'PRICE LIST IS INVALID';

End;

Begin

Select Term_Id Into I_Term_Id

From Ra_Terms_Tl Where Name='IMMEDIATE';

Exception

When Others Then

I_Error_Message:= 'PAYMENT TERM IS INVALID';

End;

Begin

Select Transaction_Type_Id Into I_Order_type_Id

From oe_Transaction_Types_Tl Where Name='WAT_CORPORATE_SALES';

Exception

When Others Then

I_Error_Message:= 'ORDER TYPE IS INVALID';

End;

Begin

Select Order_Source_Id Into I_Order_Source_Id

From oe_Order_Sources Where Name='Online';

Exception

When Others Then

I_Error_Message:= 'ORDER SOURCE IS INVALID';

End;

Begin

Select Organization_Id Into I_Organization_Id

From Org_Organization_Definitions Where Organization_Code=ORD.WARA_HOUSE;

Exception

When Others Then

I_Error_Message:= 'WARE HOUSE IS INVALID';

End;

Begin

Select Customer_Id Into I_Sold_To_Org_Id

From Ar_Customers Where Customer_Name=ORD.CUSTOMER_NAME;

Exception

When Others Then

I_Error_Message:= 'CUSTOMER IS INVALID';

End;

Begin

Select Ship_Su.Site_Use_Id Into I_Ship_To_Org_Id

From Hz_Cust_Site_Uses_All Ship_Su,

Hz_Cust_Acct_Sites_All Ship_Cas,

Hz_Party_Sites Ship_Ps

Where Ship_Su.org_Id=83--I_Org_Id

And Ship_Su.Org_Id=Ship_Cas.Org_Id

And Ship_Su.Location=ORD.Ship_To

And Ship_Su.Cust_Acct_Site_Id=Ship_Cas.Cust_Acct_Site_Id(+)

And Ship_Cas.Party_Site_Id=Ship_Ps.party_Site_Id(+)

And Ship_Ps.Party_Site_Id In(Select Party_Site_Id From Hz_Party_Sites

Where Party_Id In( Select party_Id From Ar_Customers

Where Customer_Id=I_Customer_Id And Status='A'));

Exception

When Others Then

I_Error_Message:= 'SHIP TO SITE IS INVALID';

End;

Begin

Select Ship_Su.Site_Use_Id Into I_Bill_To_Org_Id

From Hz_Cust_Site_Uses_All Ship_Su,

Hz_Cust_Acct_Sites_All Ship_Cas,

Hz_Party_Sites Ship_Ps

Where Ship_Su.org_Id=83--I_Org_Id

And Ship_Su.Org_Id=Ship_Cas.Org_Id

And Ship_Su.Location=ORD.Bill_To

And Ship_Su.Cust_Acct_Site_Id=Ship_Cas.Cust_Acct_Site_Id(+)

And Ship_Cas.Party_Site_Id=Ship_Ps.party_Site_Id(+)

And Ship_Ps.Party_Site_Id In(Select Party_Site_Id From Hz_Party_Sites

Where Party_Id In( Select party_Id From Ar_Customers

Where Customer_Id=I_Customer_Id And Status='A'));

Exception

When Others Then

I_Error_Message:= 'BILL TO SITE IS INVALID';

End;

-------------Lines Details

Begin

Select Inventory_Item_Id Into I_Inventory_Item_Id

From Mtl_System_Items_B Where Segment1=ORD.Item_Code

And Organization_Id=I_Organization_Id;

Exception

When Others Then

I_Error_Message:= 'ITEM NAME NOT EXISTING';

End;

Begin

Select Uom_Code Into I_Uom_Code

From Mtl_Units_Of_Measure_Vl

Where Uom_Code=ORD.UOM;

Exception

When Others Then

I_Error_Message:= 'ITEM NAME NOT EXISTING';

End;

Begin

Select Transaction_Type_Id Into I_Line_Type_Id

From Oe_Transaction_Types_TL

Where Name =ORD.Line_Type;

Exception

When Others Then

I_Error_Message:= 'ITEM NAME NOT EXISTING';

End;

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

Insert Into OE_HEADERS_IFACE_ALL (

Order_Source_Id,

ORIG_SYS_DOCUMENT_REF,

Org_Id,

Sold_From_Org_Id,

Ship_From_Org_Id,

Ordered_Date,

Order_Type_id,

Sold_To_Org_Id,

Payment_Term_Id,

Operation_Code,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATEd_By,

Customer_Po_Number,Price_List_Id,Context,Ship_To_Org_Id,Invoice_To_Org_Id,Booked_Flag,SALESREP_ID,ATTRIBUTE1

)

Values(0,

I_Header_Id,

I_Org_Id,

I_Organization_Id,

Sysdate,

I_Order_Type_Id,

I_Sold_To_Org_Id,

I_term_Id,

'CREATE',

sysdate,

1131,

Sysdate,

1131,

ORD.CUSTOMER_PO_NUMBER,

I_Price_List_Id,

'Legasy_No',

I_Ship_To_Org_Id,

I_Bill_To_Org_Id,'Y',-3,I_Header_Id

);

Insert Into OE_LINES_IFACE_ALL(Order_Source_Id,

Orig_Sys_Document_Ref,

Orig_Sys_Line_Ref,

Line_Number,

Inventory_Item_Id,

Ordered_Quantity,

Ship_From_Org_Id,

Org_Id,

Pricing_Quantity,

Unit_Selling_Price,

Unit_list_Price,

Price_List_Id,

Payment_Term_Id,

Schedule_Ship_Date,

Request_Date,

CREATION_DATE,

CREATED_BY,

LAST_UPDATE_DATE,

LAST_UPDATEd_By,

line_Type_id,

Calculate_Price_Flag

)

Values

(0,

I_Header_Id,

I_Line_Id,

I_Inventory_Item_Id,

ORD.QUANTITY,

I_Organization_Id,

I_Org_Id,

ORD.QUANTITY,

ORD.Price_Unit,

I_Price_List_Id,

I_Term_ID,

Sysdate,

sysdate,

1131,

Sysdate,

1131,

I_line_Type_id,

'Y');

Commit;

Begin

Mo_Global.Init('ONT');

Fnd_Global.Apps_Initialize(User_Id=>1318,Resp_Id=>21623,Resp_Appl_Id=>660);

v_request_id:= FND_REQUEST.SUBMIT_REQUEST (

application => 'ONT'

,program => 'OEOIMP'

,description => 'Order Import'

,start_time => SYSDATE

,sub_request => NULL

,argument1 => p_operating_unit

,argument2 => p_order_source

,argument3 => p_orig_sys_document_ref

,argument4 => p_operation_code

,argument5 => p_validate_only

,argument6 => p_debug_level

,argument7 => p_num_instances

,argument8 => p_sold_to_org_id

,argument9 => p_sold_to_org

,argument10 => p_change_sequence

,argument11 => p_perf_param

,argument12 => p_rtrim_data

,argument13 => p_pro_ord_with_null_flag

,argument14 => p_default_org_id

,argument15 => p_validate_desc_flex

);

COMMIT;

--Concurrent Phase Loop Starts

Loop

V_Wait_Outcome := Fnd_Concurrent.Get_Request_Status(Request_Id => v_request_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('Request_id: '||v_request_id);

DBMS_OUTPUT.PUT_LINE('Header_id: '||I_Header_Id);

End ;

-- Begin

-- Select Tax_Id,Tax_Rate Into I_Tax_Id,I_Tax_Rate From Apps.Jai_Cmn_Taxes_All Where Org_Id In(Select ORGANIZATION_ID From Apps.Hr_Operating_Units Where Name =ORD.OU_NAME)

-- And Tax_Name=ORD.TAX_CATEGORY;

-- End;

-- Begin

-- Select HEADER_ID,LINE_ID,UNIT_SELLING_PRICE*ORDERED_QUANTITY as Line_Amount Into V_HEADER_ID,V_LINE_ID,V_Line_Amount From oe_Order_Lines_All

-- Where ORIG_SYS_DOCUMENT_REF=''||I_Header_Id||'';

-- End;

-- DBMS_OUTPUT.PUT_LINE('Tax: '||V_HEADER_ID||' '||V_LINE_ID||' '||V_Line_Amount ||' '||(V_Line_Amount*I_Tax_Rate/100));

-- Insert Into JAI_OM_OE_SO_TAXES

-- (TAX_LINE_NO,

-- LINE_ID,

-- HEADER_ID,

-- PRECEDENCE_1,

-- TAX_ID,

-- TAX_RATE,

-- TAX_AMOUNT,

-- BASE_TAX_AMOUNT,

-- FUNC_TAX_AMOUNT,

-- CREATION_DATE,

-- CREATED_BY,

-- LAST_UPDATE_DATE,

-- LAST_UPDATE_LOGIN

-- )Values

-- (1,

-- V_Line_Id,

-- V_Header_Id,

-- 0,

-- I_Tax_Id,

-- I_Tax_Rate,

-- (V_Line_Amount*I_Tax_Rate/100),

-- V_Line_Amount,

-- (V_Line_Amount*I_Tax_Rate/100),

-- sysdate,

-- 1131,

-- Sysdate,

-- 112272);

-- Commit;

-- Update JAI_OM_OE_SO_LINES Set TAX_AMOUNT=(Select Sum(TAX_AMOUNT) From JAI_OM_OE_SO_TAXES Where Header_Id=V_header_Id And Line_Id=V_line_Id)

-- Where Header_Id=V_header_Id And Line_Id=V_line_Id;

-- Commit;

-- Update JAI_OM_OE_SO_LINES Set LINE_TOT_AMOUNT=(Select Sum(TAX_AMOUNT+BASE_TAX_AMOUNT) From JAI_OM_OE_SO_TAXES Where Header_Id=V_header_Id And Line_Id=V_line_Id)

-- Where Header_Id=V_header_Id And Line_Id=V_line_Id;

-- Commit;

End Loop;

Exception

When Others Then

V_Errcode := Sqlcode;

V_Errmsg := Sqlerrm;

-- Insert Into Xx_Error_Table Values ('INV','XXCAPL_IMPORT_ITEM',V_Errcode,V_Errmsg,Sysdate,Fnd_Global.User_Id);

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

Commit;

End;

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

Tuesday 2 June 2015

No comments:

Post a Comment

Blog Archive