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

Thursday 4 June 2015

Apex Report Restriction Based on User level

declare
val varchar2(100);
c1 number:=0;
begin
val := APEX_CUSTOM_AUTH.GET_USERNAME;
select count(*) into c1 from apex_workspace_apex_users where upper(user_name)=upper(val) and user_name!='ADMIN1';
if c1=1 then
return true;
else
return false;
end if;

end;

Non Repeat The Column Record

Example Out Put :

Receipt Date Receipt Number Applied_Amount Receipt_Amount

01-Jun-2015 RC-1 10000 50000
01-Jun-2015 RC-1 500 50000
01-Jun-2015 RC-1 10000 50000
01-Jun-2015 RC-1 10000 50000

01-Jun-2015 RC-2 10000 70000
01-Jun-2015 RC-2 5005 70000
01-Jun-2015 RC-2 10000 70000
01-Jun-2015 RC-2 10000 70000

Script :

(Case When row_number() over(partition by Receipt_number order by Receipt_number)=1 Then Receipt_number Else '' End )Receipt_number

Actual Output :

01-Jun-2015 RC-1 10000 50000
01-Jun-2015 500 50000
01-Jun-2015 10000 50000
01-Jun-2015 10000 50000

01-Jun-2015 RC-2 10000 70000
01-Jun-2015 5005 70000
01-Jun-2015 10000 70000
01-Jun-2015 10000 70000

Script For Row Level Record TO Column Level Display

Actual Record :

1, Arun,
2, Mano
3, Devaraj

Query For Chaging the Record Into Columsn level:

select
LISTAGG( DEFAULT_DIST_CCID,',' ) WITHIN GROUP (ORDER BY Invoice_id)
from
Ap_invoice_Lines_All@Apps_Prod Where Invoice_id=18000

Script Output:

Arun,Mano,Devaraj

Wednesday 3 June 2015

AR Interface With Dynamic Loaded in BackEnd.

Function AP_Invoice_Submit
Return Number

Is

PRAGMA AUTONOMOUS_TRANSACTION;

v_request_id NUMBER;

Cursor AP_Invoice
Is

Select Distinct RESPONSIBILITY_KEY,PROFILE_OPTION_VALUE,A.APPLICATION_ID,RESPONSIBILITY_ID, ORGANIZATION_ID as Org_Id,Source
From AP_INVOICES_INTERFACE API,
Apps.Hr_Operating_Units HOU,Fnd_Responsibility_Vl A,fnd_profile_option_values B
Where API.OPERATING_UNIT=HOU.NAME
And A.responsibility_id(+) = B.level_value
And PROFILE_OPTION_VALUE=To_Char(HOU.ORGANIZATION_ID)
And RESPONSIBILITY_KEY Like '%PAYA%SUPER USER'
And Nvl(Status,0)!='PROCESSED';

BEGIN

For i In AP_Invoice
Loop
Fnd_Global.Apps_Initialize(1110,i.RESPONSIBILITY_ID,i.APPLICATION_ID);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST(
'SQLAP',
'APXIIMPT',
'Payables Open Interface Import',
TO_CHAR(SYSDATE,'DD-MON-RR HH24:MI:SS'),
FALSE,
i.ORG_ID,
i.Source,
NULL,
NULL,
NULL,
NULL,
NULL,
'Y',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL );

COMMIT;

End Loop;
Return v_request_id;
END;

AP Interface With Dynamic Loader With Back End

Function AP_Invoice_Submit
Return Number

Is

PRAGMA AUTONOMOUS_TRANSACTION;

v_request_id NUMBER;

Cursor AP_Invoice
Is

Select Distinct RESPONSIBILITY_KEY,PROFILE_OPTION_VALUE,A.APPLICATION_ID,RESPONSIBILITY_ID, ORGANIZATION_ID as Org_Id,Source
From AP_INVOICES_INTERFACE API,
Apps.Hr_Operating_Units HOU,Fnd_Responsibility_Vl A,fnd_profile_option_values B
Where API.OPERATING_UNIT=HOU.NAME
And A.responsibility_id(+) = B.level_value
And PROFILE_OPTION_VALUE=To_Char(HOU.ORGANIZATION_ID)
And RESPONSIBILITY_KEY Like '%PAYA%SUPER USER'
And Nvl(Status,0)!='PROCESSED';

BEGIN

For i In AP_Invoice
Loop
Fnd_Global.Apps_Initialize(1110,i.RESPONSIBILITY_ID,i.APPLICATION_ID);

v_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST(
'SQLAP',
'APXIIMPT',
'Payables Open Interface Import',
TO_CHAR(SYSDATE,'DD-MON-RR HH24:MI:SS'),
FALSE,
i.ORG_ID,
i.Source,
NULL,
NULL,
NULL,
NULL,
NULL,
'Y',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL );

COMMIT;

End Loop;
Return v_request_id;
END;

Tuesday 2 June 2015

How TO Execute Procedure In Funtion

FUNCTION P_Execute(P_C_Name Varchar2,P_Org_Id Number,P_GC_Name Varchar2,P_Currency_Code Varchar2)
RETURN Varchar2
--P_C_NAME Varchar2
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
-- BEGIN
---- P_C_NAME := NULL;

SOA_REPORT (P_C_Name,P_Org_Id,P_GC_Name,P_Currency_Code);
-- END;
RETURN P_C_Name;
END;

SOA Report With Dynamically Run Based on parameters

Create Replace procedure SOA_Report(P_C_Name Varchar2,P_Org_Id Number,P_GC_Name Varchar2,P_Currency_Code Varchar2)
--RETURN Number
Is

lc_boolean BOOLEAN;
ln_request_id NUMBER;
lc_printer_name VARCHAR2 (100);
lc_boolean1 BOOLEAN;
lc_boolean2 BOOLEAN;
V_CName VARCHAR2 (100);
V_Currency VARCHAR2 (100);
L_Org_Id Number;
P_PAY_GROUP Varchar2(100);
L_APPLICATION_ID Number;
L_RESPONSIBILITY_ID Number;
L_C_Org_ID Number;

Cursor C1 Is

Select Distinct Party_Name,G.Org_Id,Invoice_Currency_Code,Corporation_Class From Apps.Hz_Parties A,Apps.Hz_Cust_Accounts B,Apps.Hz_Party_Sites C,Apps.Hz_Locations D,Apps.Hz_Cust_Acct_Sites_All E,Apps.Hz_Cust_Site_Uses_All F, Apps.Ra_Customer_Trx_All G,
Hz_Organization_Profiles CG
Where A.Party_Id = B.Party_Id And C.Party_Id = B.Party_Id And C.Location_Id = D.Location_Id And C.Party_Site_Id = E.Party_Site_Id And E.Cust_Acct_Site_Id = F.Cust_Acct_Site_Id
And E.Cust_Account_Id = B.Cust_Account_Id
And G.Bill_To_Site_Use_Id=F.Site_Use_Id And Cg.Organization_Name=A.Party_Name And Cg.Party_Id=A.Party_Id
And Party_Name =Nvl(P_C_Name, Party_Name) And G.Org_Id=P_Org_Id
And INVOICE_CURRENCY_CODE=Nvl(P_Currency_Code,INVOICE_CURRENCY_CODE) And CORPORATION_CLASS=Nvl(P_GC_Name,CORPORATION_CLASS);

BEGIN

For i In C1
Loop

Begin

Select Distinct A.APPLICATION_ID,RESPONSIBILITY_ID,RIL.Org_Id as C_Org_ID
Into L_APPLICATION_ID,L_RESPONSIBILITY_ID,L_C_Org_ID
From ra_Customer_Trx_all RIL,Hr_Operating_Units OOD,
Fnd_Responsibility_Vl A,fnd_profile_option_values B
Where RIL.Org_Id=OOD.Organization_Id
And A.responsibility_id(+) = B.level_value
And RESPONSIBILITY_KEY Like '%RECEIVABLE%SUPER USER%'
And B.PROFILE_OPTION_VALUE=To_Char(RIL.Org_Id)
And RIL.Org_Id=P_Org_Id;
End;

Mo_Global.Init('AR');
Mo_Global.Set_Policy_Context ('S', L_C_ORG_ID);

Fnd_Global.Apps_Initialize(1110,L_RESPONSIBILITY_ID,L_APPLICATION_ID);

lc_boolean :=
fnd_submit.set_print_options (printer => lc_printer_name
,style => 'PDF Publisher'
,copies => 1
);

lc_boolean2 :=
FND_REQUEST.add_layout(template_appl_name => 'SQLAP'
,template_code => 'APXINDIA_XML'
,template_language => 'en'
' ,template_territory => 'US'
,output_format => 'PDF');

ln_request_id := APPS.FND_REQUEST.SUBMIT_REQUEST('SQLAP',
'APXINDIA_XML',
'',
TO_CHAR(SYSDATE,'DD-MON-RR HH24:MI:SS'),
FALSE,
i.Party_Name,
i.Org_Id,
i.Invoice_Currency_Code);

-- RETURN ln_request_id;
COMMIT;
End Loop;

--RETURN ln_request_id;
END;

Service Tax Import Without Interface

CREATE OR REPLACE PROCEDURE APPS.Ar_service_tax_ARK
IS
l_verify_flag CHAR (1);
l_error_message VARCHAR2 (2500);
l_organization_id NUMBER;
l_invoice_line_id NUMBER;
l_invoice_doc_id NUMBER;
l_code_combination_id NUMBER;
l_vendor_id NUMBER;
l_vendor_site_id NUMBER;
l_invoice_id NUMBER;
l_slno NUMBER;
l_location_id NUMBER;
v_lookup_code VARCHAR2 (25);
l_tax_category_id NUMBER;
bi_organization_id NUMBER;
L_tax Number;
L_Tax_Rate Number;
L_Tax_Amount Number;
L_RA_CUSTOMER_TRX_LINES_S Number;
L_RA_CUST_TRX_LINE_GL_DIST_S Number;
L_Spilt1 Number(15,3);
L_AMT1 Number(15,3);
L_Spilt2 Number(15,3);
L_AMT2 Number(15,3);
L_Spilt3 Number(15,3);
L_AMT3 Number(15,3);
T_Total Number;
L_TAX Number(15,3);
-- L_Code_Combination_Id Number;

CURSOR C1
IS

Select MODIFIABLE_FLAG,Org_id,Location_id,Tax_Id,INCLUSIVE_TAX_FLAG, Tax_Rate,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_CATEGORY_NAME,Tax_Name,Line_No,TAX_AMOUNT,TAX_CATEGORY_ID,TAX_TYPE,ADHOC_FLAG,Meaning,TRANSACTION_NUM as Customer_Trx_Id,TRANSACTION_LINE_NUM as Customer_trx_Line_Id,SERVICE_TYPE_CODE,Amount,Tax,A,B,C,
(Case When Tax<>0 Then Round(Amount*A/100) End) As Spilt1,
(Case When Tax<>0 Then Round(Amount) End) as Amt1,
(Case When Tax<>0 Then Round((Amount*A/100)*B/100) End) As Spilt2,
(Case When Tax<>0 Then (Amount*A/100) End) As Amt2,
(Case When Tax<>0 Then Round((Amount*A/100)*C/100) End) As Spilt3,
(Case When Tax<>0 Then (Amount*A/100) End) As Amt3
From (
Select Distinct --SERVICE_TYPE_CODE,
MODIFIABLE_FLAG,TD.Org_id,TD.Tax_Id,TD.INCLUSIVE_TAX_FLAG,(Case When TD.Tax_Rate Is Not Null Then TD.Tax_Rate Else 0 End) as Tax_Rate,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_CATEGORY_NAME,TD.Tax_Name,TL.Line_No,TD.TAX_AMOUNT,TL.TAX_CATEGORY_ID,TAX_TYPE,TD.ADHOC_FLAG,Meaning,TRANSACTION_NUM,TRANSACTION_LINE_NUM,
EXTENDED_AMOUNT as Amount,(Case When TL.Line_No=1 Then (EXTENDED_AMOUNT*TD.Tax_Rate/100)
End) as Tax,
(Select Distinct TD.Tax_Rate From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All DY,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And DY.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=DY.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=DY.TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=DY.TRANSACTION_LINE_NUM And Line_No=1) A,
(Select Distinct TD.Tax_Rate From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All DY,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And DY.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=DY.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=DY.TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=DY.TRANSACTION_LINE_NUM And Line_No=2) B,
(Select Distinct TD.Tax_Rate From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All DY,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And DY.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=DY.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=DY.TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=DY.TRANSACTION_LINE_NUM And Line_No=3) C,JILA.Location_id,JILA.SERVICE_TYPE_CODE
From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All JILA,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And JILA.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=JILA.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=TRANSACTION_LINE_NUM
And JILA.Attribute15 Is Null Order By TRANSACTION_NUM ,Line_No);

BEGIN
Mo_Global.Init('AR');

FOR i IN C1
LOOP

Begin
Select Apps.RA_CUSTOMER_TRX_LINES_S.NEXTval Into L_RA_CUSTOMER_TRX_LINES_S From Dual;

Select Apps.RA_CUST_TRX_LINE_GL_DIST_S.Nextval Into L_RA_CUST_TRX_LINE_GL_DIST_S From Dual;
End;

Begin

Select
(Case When Tax<>0 Then Round(Amount*A/100) End) As Spilt1,
(Case When Tax<>0 Then Round(Amount) End) as Amt1,
(Case When Tax<>0 Then Round((Amount*A/100)*B/100) End) As Spilt2,
(Case When Tax<>0 Then (Amount*A/100) End) As Amt2,
(Case When Tax<>0 Then Round((Amount*A/100)*C/100) End) As Spilt3,
(Case When Tax<>0 Then (Amount*A/100) End) As Amt3
Into L_Spilt1,L_AMT1,L_Spilt2,L_AMT2,L_Spilt3,L_AMT3
From (
Select Distinct --SERVICE_TYPE_CODE,
MODIFIABLE_FLAG,TD.Org_id,TD.Tax_Id,TD.INCLUSIVE_TAX_FLAG,(Case When TD.Tax_Rate Is Not Null Then TD.Tax_Rate Else 0 End) as Tax_Rate,
PRECEDENCE_1,
PRECEDENCE_2,
PRECEDENCE_3,
PRECEDENCE_4,
PRECEDENCE_5,
TAX_CATEGORY_NAME,TD.Tax_Name,TL.Line_No,TD.TAX_AMOUNT,TL.TAX_CATEGORY_ID,TAX_TYPE,TD.ADHOC_FLAG,Meaning,TRANSACTION_NUM,TRANSACTION_LINE_NUM,
EXTENDED_AMOUNT as Amount,(Case When TL.Line_No=1 Then (EXTENDED_AMOUNT*TD.Tax_Rate/100)
End) as Tax,
(Select Distinct TD.Tax_Rate From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All DY,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And DY.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=DY.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=DY.TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=DY.TRANSACTION_LINE_NUM And Line_No=1) A,
(Select Distinct TD.Tax_Rate From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All DY,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And DY.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=DY.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=DY.TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=DY.TRANSACTION_LINE_NUM And Line_No=2) B,
(Select Distinct TD.Tax_Rate From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All DY,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And DY.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=DY.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=DY.TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=DY.TRANSACTION_LINE_NUM And Line_No=3) C,JILA.Location_id
From JAI_CMN_TAX_CTGS_ALL TM,JAI_CMN_TAX_CTG_LINES TL,Apps.Jai_Cmn_Taxes_All TD,
Apps.Org_Organization_Definitions OOD,Jai_Interface_Lines_All JILA,apps.fnd_lookup_values FLV,RA_Customer_Trx_Lines_All RCTLA
Where TM.TAX_CATEGORY_ID=TL.TAX_CATEGORY_ID
And TL.TAX_ID=TD.TAX_ID
And JILA.SERVICE_TYPE_CODE=FLV.Lookup_Code
And TL.TAX_CATEGORY_ID=JILA.TAX_CATEGORY_ID
And RCTLA.CUstomer_trx_Id=TRANSACTION_NUM
And RCTLA.CUstomer_trx_Line_Id=TRANSACTION_LINE_NUM
And JILA.Attribute15 Is Null Order By TRANSACTION_NUM ,Line_No) Where Tax Is Not Null And TRANSACTION_NUM=i.Customer_Trx_Id;

End;

Begin

Select Distinct B.ATTRIBUTE_VALUE
Into L_Code_Combination_Id
From JAI_RGM_REGISTRATIONS A,JAI_RGM_PARTY_REGNS B,JAI_RGM_PARTIES C Where ATTRIBUTE_CODE='LIABILITY' And REGISTRATION_TYPE='ACCOUNTS'
And PARENT_REGISTRATION_ID In (Select REGISTRATION_ID From JAI_RGM_REGISTRATIONS Where ATTRIBUTE_CODE=i.Tax_Type And REGISTRATION_TYPE='TAX_TYPES')
And A.REGISTRATION_ID=B.REGISTRATION_ID And A.REGIME_ID=C.REGIME_ID And B.REGIME_ORG_ID=C.REGIME_ORG_ID
And ORGANIZATION_ID=i.Org_id And ORGANIZATION_TYPE='IO';

End;

If i.Line_no=1 Then
T_TOTAL:=L_Spilt1+L_Spilt2+L_Spilt3;
SAVEPOINT a1;

Insert into Ra_Customer_Trx_Lines_All(
CUSTOMER_TRX_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CUSTOMER_TRX_ID,
LINE_NUMBER,
SET_OF_BOOKS_ID,
LINE_TYPE,
EXTENDED_AMOUNT,
LINK_TO_CUST_TRX_LINE_ID,
AUTOTAX,
ORG_ID,
TAXABLE_AMOUNT)
Values
(L_RA_CUSTOMER_TRX_LINES_S,
Sysdate,---CUSTOMER_TRX_LINE_ID
1110, ---LAST_UPDATE_DATE,
sysdate, --CREATION_DATE
1110, --LAST_UPDATED_BY,
-1,--LAST_UPDATE_LOGIN,
i.CUSTOMER_TRX_ID,--CUSTOMER_TRX_ID,
i.Line_No,--LINE_NUMBER,
2031,--SET_OF_BOOKS_ID,
'TAX',--LINE_TYPE,
L_Spilt1,--EXTENDED_AMOUNT,
i.CUSTOMER_TRX_LINE_ID,--LINK_TO_CUST_TRX_LINE_ID,
'N',--AUTOTAX,
i.Org_id,--ORG_ID,
i.Amount-L_Spilt1);--TAXABLE_AMOUNT

Insert Into JAI_AR_TRX_TAX_LINES(
TAX_LINE_NO,--TAX_LINE_NO,
CUSTOMER_TRX_LINE_ID,--CUSTOMER_TRX_LINE_ID,
LINK_TO_CUST_TRX_LINE_ID,--LINK_TO_CUST_TRX_LINE_ID
PRECEDENCE_1,--PRECEDENCE_1,\
TAX_ID,--TAX_ID
TAX_RATE,--TAX_RATE
TAX_AMOUNT,--TAX_AMOUNT
FUNC_TAX_AMOUNT,--FUNC_TAX_AMOUNT
BASE_TAX_AMOUNT,--BASE_TAX_AMOUNT
CREATION_DATE,--CREATION_DATE
CREATED_BY,--CREATED_BY
LAST_UPDATE_DATE,--LAST_UPDATE_DATE,
LAST_UPDATED_BY,--LAST_UPDATED_BY
LAST_UPDATE_LOGIN--LAST_UPDATE_LOGIN)
)
Values
(i.Line_No,
L_RA_CUSTOMER_TRX_LINES_S,
i.Customer_trx_Line_Id,
i.PRECEDENCE_1,
i.Tax_Id,
I.Tax_Rate,
L_Spilt1,
L_Spilt1,
L_AMT1,
'14-MAR-2015',
1110,
'24-MAR-2015',
1110,
810505);

Insert Into ra_cust_trx_line_gl_dist(
CUST_TRX_LINE_GL_DIST_ID,
CUSTOMER_TRX_LINE_ID,
CODE_COMBINATION_ID,
SET_OF_BOOKS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PERCENT,
AMOUNT,
GL_DATE,
POSTING_CONTROL_ID,
ACCOUNT_CLASS,
CUSTOMER_TRX_ID,
ACCOUNT_SET_FLAG,
ACCTD_AMOUNT,
ORG_ID,
EVENT_ID
)
Values
( L_RA_CUST_TRX_LINE_GL_DIST_S,--CUST_TRX_LINE_GL_DIST_ID
L_RA_CUSTOMER_TRX_LINES_S,--CUSTOMER_TRX_LINE_ID
L_Code_Combination_Id,--CODE_COMBINATION_ID
2031,--SET_OF_BOOKS_ID
sysdate,--LAST_UPDATE_DATE
1110,--LAST_UPDATED_BY
sysdate,--CREATION_DATE
1110,--CREATED_BY
-1,--LAST_UPDATE_LOGIN
100,--PERCENT
L_Spilt1,--AMOUNT
sysdate,
-3,--POSTING_CONTROL_ID
'TAX',--ACCOUNT_CLASS
i.Customer_Trx_Id,--CUSTOMER_TRX_ID
'N',--ACCOUNT_SET_FLAG
L_Spilt1,--ACCTD_AMOUNT
i.Org_Id,--ORG_ID
(Select EVENT_ID From ra_cust_trx_line_gl_dist Where ACCOUNT_CLASS='REC' And Customer_trx_Id =i.Customer_Trx_Id)--EVENT_ID
);

Insert Into JAI_AR_TRXS
(CUSTOMER_TRX_ID,
ORGANIZATION_ID,
LOCATION_ID,
UPDATE_RG_FLAG,
ONCE_COMPLETED_FLAG,
TOTAL_AMOUNT,
LINE_AMOUNT,
TAX_AMOUNT,
TRX_NUMBER,
BATCH_SOURCE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
SET_OF_BOOKS_ID,
INVOICE_CURRENCY_CODE,
UPDATE_RG23D_FLAG,
LEGAL_ENTITY_ID,
COMPLETE_FLAG
)
Values
(i.CUSTOMER_TRX_ID,--CUSTOMER_TRX_ID
i.Org_Id,--ORGANIZATION_ID,
i.LOCATION_ID,--LOCATION_ID,
'N',--UPDATE_RG_FLAG,
'Y',--ONCE_COMPLETED_FLAG
i.amount,--TOTAL_AMOUNT
i.amount,--LINE_AMOUNT
0,--TAX_AMOUNT
(Select Trx_Number From Ra_Customer_Trx_All Where Customer_Trx_Id=i.Customer_Trx_Id and org_Id=i.org_Id),--TRX_NUMBER
(Select BATCH_SOURCE_ID From Ra_Customer_Trx_All Where Customer_Trx_Id=i.Customer_Trx_Id and org_Id=i.org_Id),--BATCH_SOURCE_ID
sysdate,--CREATION_DATE
1110,--CREATED_BY
sysdate,--LAST_UPDATE_DATE
1110,--LAST_UPDATED_BY
-1,--LAST_UPDATE_LOGIN
(Select SET_OF_BOOKS_ID From Ra_Customer_Trx_All Where Customer_Trx_Id=i.Customer_Trx_Id and org_Id=i.org_Id),--SET_OF_BOOKS_ID
(Select INVOICE_CURRENCY_CODE From Ra_Customer_Trx_All Where Customer_Trx_Id=i.Customer_Trx_Id and org_Id=i.org_Id),--INVOICE_CURRENCY_CODE
'N',--UPDATE_RG23D_FLAG
(Select LEGAL_ENTITY_ID From Ra_Customer_Trx_All Where Customer_Trx_Id=i.Customer_Trx_Id and org_Id=i.org_Id),--LEGAL_ENTITY_ID
'Y'--COMPLETE_FLAG
);

Insert Into JAI_AR_TRX_LINES
(CUSTOMER_TRX_LINE_ID,
CUSTOMER_TRX_ID,
LINE_NUMBER,
INVENTORY_ITEM_ID,
UNIT_CODE,
QUANTITY,
UNIT_SELLING_PRICE,
TAX_CATEGORY_ID,
LINE_AMOUNT,
TAX_AMOUNT,
TOTAL_AMOUNT,
GL_DATE,
AUTO_INVOICE_FLAG,
ASSESSABLE_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
VAT_ASSESSABLE_VALUE
)
Values
(i.CUSTOMER_TRX_LINE_ID,--CUSTOMER_TRX_LINE_ID
i.CUSTOMER_TRX_ID,--CUSTOMER_TRX_ID
(Select LINE_NUMBER From Ra_Customer_Trx_Lines_All Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID),--LINE_NUMBER
(Select INVENTORY_ITEM_ID From Ra_Customer_Trx_Lines_All Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID),--INVENTORY_ITEM_ID
(Select UOM_CODE From Ra_Customer_Trx_Lines_All Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID),--UNIT_CODE
(Select QUANTITY_INVOICED From Ra_Customer_Trx_Lines_All Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID),--QUANTITY
(Select UNIT_SELLING_PRICE From Ra_Customer_Trx_Lines_All Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID),--UNIT_SELLING_PRICE
0,--TAX_CATEGORY_ID
i.amount,--LINE_AMOUNT
0,--TAX_AMOUNT
i.amount,--TOTAL_AMOUNT
sysdate,--GL_DATE
'N',--AUTO_INVOICE_FLAG
i.amount,
sysdate,--CREATION_DATE
1110,--CREATED_BY
sysdate,--LAST_UPDATE_DATE
1110,--LAST_UPDATED_BY
-1,--LAST_UPDATE_LOGIN
(Select UNIT_SELLING_PRICE From Ra_Customer_Trx_Lines_All Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID)--VAT_ASSESSABLE_VALUE
);

Update JAI_AR_TRXS Set Organization_Id=i.Org_id,Location_id=i.Location_id Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID;

Update JAI_AR_TRX_LINES Set TAX_CATEGORY_ID=i.TAX_CATEGORY_ID,TAX_AMOUNT=T_TOTAL,TOTAL_AMOUNT=i.Amount+T_TOTAL,SERVICE_TYPE_CODE=i.SERVICE_TYPE_CODE Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID;

Update Ra_Customer_Trx_Lines_All Set TAX_EXEMPT_FLAG='S',GLOBAL_ATTRIBUTE_CATEGORY='JG.IN.ARXTWMAI.OFI TAX IMPORT' Where CUSTOMER_TRX_ID=i.CUSTOMER_TRX_ID And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID;

End If;

If i.Line_No =2 Then

Insert into Ra_Customer_Trx_Lines_All(
CUSTOMER_TRX_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CUSTOMER_TRX_ID,
LINE_NUMBER,
SET_OF_BOOKS_ID,
LINE_TYPE,
EXTENDED_AMOUNT,
LINK_TO_CUST_TRX_LINE_ID,
AUTOTAX,
ORG_ID,
TAXABLE_AMOUNT)
Values
(L_RA_CUSTOMER_TRX_LINES_S,
Sysdate,---CUSTOMER_TRX_LINE_ID
1110, ---LAST_UPDATE_DATE,
sysdate, --CREATION_DATE
1110, --LAST_UPDATED_BY,
-1,--LAST_UPDATE_LOGIN,
i.CUSTOMER_TRX_ID,--CUSTOMER_TRX_ID,
i.Line_No,--LINE_NUMBER,
2031,--SET_OF_BOOKS_ID,
'TAX',--LINE_TYPE,
L_Spilt2,--EXTENDED_AMOUNT,
i.CUSTOMER_TRX_LINE_ID,--LINK_TO_CUST_TRX_LINE_ID,
'N',--AUTOTAX,
i.Org_id,--ORG_ID,
i.Amount-L_Spilt2);--TAXABLE_AMOUNT

Insert Into JAI_AR_TRX_TAX_LINES(
TAX_LINE_NO,--TAX_LINE_NO,
CUSTOMER_TRX_LINE_ID,--CUSTOMER_TRX_LINE_ID,
LINK_TO_CUST_TRX_LINE_ID,--LINK_TO_CUST_TRX_LINE_ID
PRECEDENCE_1,--PRECEDENCE_1,\
TAX_ID,--TAX_ID
TAX_RATE,--TAX_RATE
TAX_AMOUNT,--TAX_AMOUNT
FUNC_TAX_AMOUNT,--FUNC_TAX_AMOUNT
BASE_TAX_AMOUNT,--BASE_TAX_AMOUNT
CREATION_DATE,--CREATION_DATE
CREATED_BY,--CREATED_BY
LAST_UPDATE_DATE,--LAST_UPDATE_DATE,
LAST_UPDATED_BY,--LAST_UPDATED_BY
LAST_UPDATE_LOGIN--LAST_UPDATE_LOGIN)
)
Values
(i.Line_No,
L_RA_CUSTOMER_TRX_LINES_S,
i.Customer_trx_Line_Id,
i.PRECEDENCE_1,
i.Tax_Id,
I.Tax_Rate,
L_Spilt2,
L_Spilt2,
L_AMT2,
Sysdate,
1110,
sysdate,
1110,
810505);

Insert Into ra_cust_trx_line_gl_dist(
CUST_TRX_LINE_GL_DIST_ID,
CUSTOMER_TRX_LINE_ID,
CODE_COMBINATION_ID,
SET_OF_BOOKS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PERCENT,
AMOUNT,
GL_DATE,
POSTING_CONTROL_ID,
ACCOUNT_CLASS,
CUSTOMER_TRX_ID,
ACCOUNT_SET_FLAG,
ACCTD_AMOUNT,
ORG_ID,
EVENT_ID
)
Values
( L_RA_CUST_TRX_LINE_GL_DIST_S,--CUST_TRX_LINE_GL_DIST_ID
L_RA_CUSTOMER_TRX_LINES_S,--CUSTOMER_TRX_LINE_ID
L_Code_Combination_Id,--CODE_COMBINATION_ID
2031,--SET_OF_BOOKS_ID
sysdate,--LAST_UPDATE_DATE
1110,--LAST_UPDATED_BY
sysdate,--CREATION_DATE
1110,--CREATED_BY
-1,--LAST_UPDATE_LOGIN
100,--PERCENT
L_Spilt2,--AMOUNT
sysdate,
-3,--POSTING_CONTROL_ID
'TAX',--ACCOUNT_CLASS
i.Customer_Trx_Id,--CUSTOMER_TRX_ID
'N',--ACCOUNT_SET_FLAG
L_Spilt2,--ACCTD_AMOUNT
i.Org_Id,--ORG_ID
(Select EVENT_ID From ra_cust_trx_line_gl_dist Where ACCOUNT_CLASS='REC' And Customer_trx_Id =i.Customer_Trx_Id)--EVENT_ID
);

End If;

If i.Line_No=3 Then

Insert into Ra_Customer_Trx_Lines_All(
CUSTOMER_TRX_LINE_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
CUSTOMER_TRX_ID,
LINE_NUMBER,
SET_OF_BOOKS_ID,
LINE_TYPE,
EXTENDED_AMOUNT,
LINK_TO_CUST_TRX_LINE_ID,
AUTOTAX,
ORG_ID,
TAXABLE_AMOUNT)
Values
(L_RA_CUSTOMER_TRX_LINES_S,
Sysdate,---CUSTOMER_TRX_LINE_ID
1110, ---LAST_UPDATE_DATE,
sysdate, --CREATION_DATE
1110, --LAST_UPDATED_BY,
-1,--LAST_UPDATE_LOGIN,
i.CUSTOMER_TRX_ID,--CUSTOMER_TRX_ID,
i.Line_No,--LINE_NUMBER,
2031,--SET_OF_BOOKS_ID,
'TAX',--LINE_TYPE,
L_Spilt3,--EXTENDED_AMOUNT,
i.CUSTOMER_TRX_LINE_ID,--LINK_TO_CUST_TRX_LINE_ID,
'N',--AUTOTAX,
i.Org_id,--ORG_ID,
i.Amount-L_Spilt3);--TAXABLE_AMOUNT

Insert Into JAI_AR_TRX_TAX_LINES(
TAX_LINE_NO,--TAX_LINE_NO,
CUSTOMER_TRX_LINE_ID,--CUSTOMER_TRX_LINE_ID,
LINK_TO_CUST_TRX_LINE_ID,--LINK_TO_CUST_TRX_LINE_ID
PRECEDENCE_1,--PRECEDENCE_1,\
TAX_ID,--TAX_ID
TAX_RATE,--TAX_RATE
TAX_AMOUNT,--TAX_AMOUNT
FUNC_TAX_AMOUNT,--FUNC_TAX_AMOUNT
BASE_TAX_AMOUNT,--BASE_TAX_AMOUNT
CREATION_DATE,--CREATION_DATE
CREATED_BY,--CREATED_BY
LAST_UPDATE_DATE,--LAST_UPDATE_DATE,
LAST_UPDATED_BY,--LAST_UPDATED_BY
LAST_UPDATE_LOGIN--LAST_UPDATE_LOGIN)
)
Values
(i.Line_No,
L_RA_CUSTOMER_TRX_LINES_S,
i.Customer_trx_Line_Id,
i.PRECEDENCE_1,
i.Tax_Id,
I.Tax_Rate,
L_Spilt3,
L_Spilt3,
L_AMT3,
sysdate,
1110,
sysdate,
1110,
810505);

Insert Into ra_cust_trx_line_gl_dist(
CUST_TRX_LINE_GL_DIST_ID,
CUSTOMER_TRX_LINE_ID,
CODE_COMBINATION_ID,
SET_OF_BOOKS_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
PERCENT,
AMOUNT,
GL_DATE,
POSTING_CONTROL_ID,
ACCOUNT_CLASS,
CUSTOMER_TRX_ID,
ACCOUNT_SET_FLAG,
ACCTD_AMOUNT,
ORG_ID,
EVENT_ID
)
Values
( L_RA_CUST_TRX_LINE_GL_DIST_S,--CUST_TRX_LINE_GL_DIST_ID
L_RA_CUSTOMER_TRX_LINES_S,--CUSTOMER_TRX_LINE_ID
L_Code_Combination_Id,--CODE_COMBINATION_ID
2031,--SET_OF_BOOKS_ID
sysdate,--LAST_UPDATE_DATE
1110,--LAST_UPDATED_BY
sysdate,--CREATION_DATE
1110,--CREATED_BY
-1,--LAST_UPDATE_LOGIN
100,--PERCENT
L_Spilt3,--AMOUNT
sysdate,
-3,--POSTING_CONTROL_ID
'TAX',--ACCOUNT_CLASS
i.Customer_Trx_Id,--CUSTOMER_TRX_ID
'N',--ACCOUNT_SET_FLAG
L_Spilt3,--ACCTD_AMOUNT
i.Org_Id,--ORG_ID
(Select EVENT_ID From ra_cust_trx_line_gl_dist Where ACCOUNT_CLASS='REC' And Customer_trx_Id =i.Customer_Trx_Id)--EVENT_ID
);

Update Ra_Customer_Trx_Lines_All Set TAX_RECOVERABLE=T_TOTAL Where Customer_Trx_Id=i.Customer_Trx_Id And CUSTOMER_TRX_LINE_ID=i.CUSTOMER_TRX_LINE_ID;

Update ra_cust_trx_line_gl_dist_All Set AMOUNT=i.Amount+T_TOTAL,ACCTD_AMOUNT=i.Amount+T_TOTAL Where ACCOUNT_CLASS='REC' And Customer_trx_Id =i.Customer_Trx_Id;

Update AR_PAYMENT_SCHEDULES_ALL Set TAX_ORIGINAL=L_Spilt1+L_Spilt2+L_Spilt3,TAX_REMAINING=L_Spilt1+L_Spilt2+L_Spilt3,
AMOUNT_DUE_ORIGINAL=i.Amount+T_TOTAL,AMOUNT_DUE_REMAINING=i.Amount+T_TOTAL,
AMOUNT_LINE_ITEMS_ORIGINAL=i.Amount,AMOUNT_LINE_ITEMS_REMAINING=i.Amount,ACCTD_AMOUNT_DUE_REMAINING=i.Amount+T_TOTAL Where Customer_Trx_Id=i.Customer_Trx_Id;

Update jai_interface_lines_all Set ATTRIBUTE15='Successfully Imported' Where TRANSACTION_NUM=i.Customer_Trx_Id And TRANSACTION_LINE_NUM=i.CUSTOMER_TRX_LINE_ID And ORG_ID=i.ORG_ID;
End If;

DBMS_Output.Put_Line(L_Code_Combination_Id);

DBMS_Output.Put_Line(L_Spilt1||' '||L_Spilt2||' '||L_Spilt3);

DBMS_Output.Put_Line(L_AMT1||' '||L_AMT2||' '||L_AMT3);

END LOOP;
Commit;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK TO a1;
l_error_message := SQLERRM;

END;
/

Asset_Retirment_Api

/* Formatted On 2013/08/01 17:00 (Formatter Plus V4.8.8) */
Create Or Replace Procedure Apps.XxARK_Asset_Retirment_Api (
P_Asset_Id In Number,
P_Book_Type_Code In Varchar2,
P_Cost_Retired In Number,
P_Proceeds_Of_Sale In Number,
P_Cost_Of_Removal In Number
)
Is
Api_Error Exception;
/*Test Asset Info */
L_Asset_Id Number := P_Asset_Id;
L_Book_Type_Code Varchar2 (15) := P_Book_Type_Code;
L_User_Id Number := 1001;
L_Cost_Retired Number := P_Cost_Retired;
L_Proceeds_Of_Sale Number := P_Proceeds_Of_Sale;
L_Cost_Of_Removal Number := P_Cost_Of_Removal;
V_Request_Id Number;
-- User_Id Must Properly Be Set To Run Calc Gain/Loss
/* Define Local Record Types */
L_Trans_Rec Fa_Api_Types.Trans_Rec_Type;
L_Dist_Trans_Rec Fa_Api_Types.Trans_Rec_Type;
L_Asset_Hdr_Rec Fa_Api_Types.Asset_Hdr_Rec_Type;
L_Asset_Retire_Rec Fa_Api_Types.Asset_Retire_Rec_Type;
L_Asset_Dist_Tbl Fa_Api_Types.Asset_Dist_Tbl_Type;
L_Subcomp_Tbl Fa_Api_Types.Subcomp_Tbl_Type;
L_Inv_Tbl Fa_Api_Types.Inv_Tbl_Type;
/* Misc Info */
L_Api_Version Number := 1;
L_Init_Msg_List Varchar2 (1) := Fnd_Api.G_False;
L_Commit Varchar2 (1) := Fnd_Api.G_True;
L_Validation_Level Number := Fnd_Api.G_Valid_Level_Full;
L_Calling_Fn Varchar2 (80) := 'Retirement Aset wrapper';
L_Return_Status Varchar2 (1) := Fnd_Api.G_False;
L_Msg_Count Number := 0;
L_Msg_Data Varchar2 (512);
L_Count Number;
L_Request_Id Number;
I Number := 0;
Temp_Str Varchar2 (512);
Mesg_Count Number;
Begin
Dbms_Output.Disable;
Dbms_Output.Enable (1000000);
Dbms_Output.Put_Line ('begin');
Fa_Srvr_Msg.Init_Server_Message;
Fa_Debug_Pkg.Set_Debug_Flag (Debug_Flag => 'YES');
-- Get Standard Who Info
L_Request_Id := Fnd_Global.Conc_Request_Id;
Fnd_Profile.Get ('LOGIN_ID', L_Trans_Rec.Who_Info.Last_Update_Login);
Fnd_Profile.Get ('USER_ID', L_Trans_Rec.Who_Info.Last_Updated_By);

If (L_Trans_Rec.Who_Info.Last_Updated_By Is Null)
Then
L_Trans_Rec.Who_Info.Last_Updated_By := -1;
End If;

If (L_Trans_Rec.Who_Info.Last_Update_Login Is Null)
Then
L_Trans_Rec.Who_Info.Last_Update_Login := -1;
End If;

L_Trans_Rec.Who_Info.Last_Update_Date := Sysdate;
L_Trans_Rec.Who_Info.Creation_Date := L_Trans_Rec.Who_Info.Last_Update_Date;
L_Trans_Rec.Who_Info.Created_By := L_Trans_Rec.Who_Info.Last_Updated_By;
L_Trans_Rec.Transaction_Type_Code := Null;
-- This Will Be Determined Inside Api
L_Trans_Rec.Transaction_Date_Entered := Null;
L_Asset_Hdr_Rec.Asset_Id := L_Asset_Id;
L_Asset_Hdr_Rec.Book_Type_Code := L_Book_Type_Code;
L_Asset_Hdr_Rec.Period_Of_Addition := Null;
L_Asset_Retire_Rec.Retirement_Prorate_Convention := Null;
L_Asset_Retire_Rec.Date_Retired := Null;
-- Will Be Current Period By Default
L_Asset_Retire_Rec.Units_Retired := Null;
L_Asset_Retire_Rec.Cost_Retired := L_Cost_Retired;
L_Asset_Retire_Rec.Proceeds_Of_Sale := L_Proceeds_Of_Sale;
L_Asset_Retire_Rec.Cost_Of_Removal := L_Cost_Of_Removal;
L_Asset_Retire_Rec.Retirement_Type_Code := 'SALE';
L_Asset_Retire_Rec.Trade_In_Asset_Id := Null;
L_Asset_Retire_Rec.Calculate_Gain_Loss := Fnd_Api.G_False;
Fnd_Profile.Put ('USER_ID', L_User_Id);
L_Asset_Dist_Tbl.Delete;
Fa_Retirement_Pub.Do_Retirement (P_Api_Version => L_Api_Version,
P_Init_Msg_List => L_Init_Msg_List,
P_Commit => L_Commit,
P_Validation_Level => L_Validation_Level,
P_Calling_Fn => L_Calling_Fn,
X_Return_Status => L_Return_Status,
X_Msg_Count => L_Msg_Count,
X_Msg_Data => L_Msg_Data,
Px_Trans_Rec => L_Trans_Rec,
Px_Dist_Trans_Rec => L_Dist_Trans_Rec,
Px_Asset_Hdr_Rec => L_Asset_Hdr_Rec,
Px_Asset_Retire_Rec => L_Asset_Retire_Rec,
P_Asset_Dist_Tbl => L_Asset_Dist_Tbl,
P_Subcomp_Tbl => L_Subcomp_Tbl,
P_Inv_Tbl => L_Inv_Tbl
);

If L_Return_Status = Fnd_Api.G_False
Then
Raise Api_Error;
End If;

Dbms_Output.Put_Line ( 'Asset Retirement Done: Retirement_id: '
|| L_Asset_Retire_Rec.Retirement_Id
);

--Commit;

-- Dump Debug Messages When Run In Debug Mode To Log File
If (Fa_Debug_Pkg.Print_Debug)
Then
Fa_Debug_Pkg.Write_Debug_Log;
End If;

Fa_Srvr_Msg.Add_Message (Calling_Fn => L_Calling_Fn,
Name => 'FA_SHARED_END_SUCCESS',
Token1 => 'PROGRAM',
Value1 => 'RETIREMENT_API'
);
Mesg_Count := Fnd_Msg_Pub.Count_Msg;

If (Mesg_Count > 0)
Then
Temp_Str := Fnd_Msg_Pub.Get (Fnd_Msg_Pub.G_First, Fnd_Api.G_False);
Dbms_Output.Put_Line ('dump: ' || Temp_Str);

For I In 1 .. (Mesg_Count - 1)
Loop
Temp_Str := Fnd_Msg_Pub.Get (Fnd_Msg_Pub.G_Next, Fnd_Api.G_False);
Dbms_Output.Put_Line ('dump: ' || Temp_Str);
End Loop;
Else
Dbms_Output.Put_Line ('dump: NO MESSAGE !');
End If;
Exception
When Api_Error
Then
Rollback Work;
Fa_Srvr_Msg.Add_Message (Calling_Fn => L_Calling_Fn,
Name => 'FA_SHARED_PROGRAM_FAILED',
Token1 => 'PROGRAM',
Value1 => L_Calling_Fn
);
Mesg_Count := Fnd_Msg_Pub.Count_Msg;

If (Mesg_Count > 0)
Then
Temp_Str := Fnd_Msg_Pub.Get (Fnd_Msg_Pub.G_First, Fnd_Api.G_False);
Dbms_Output.Put_Line ('dump: ' || Temp_Str);

For I In 1 .. (Mesg_Count - 1)
Loop
Temp_Str := Fnd_Msg_Pub.Get (Fnd_Msg_Pub.G_Next, Fnd_Api.G_False);
Dbms_Output.Put_Line ('dump: ' || Temp_Str);
End Loop;
Else
Dbms_Output.Put_Line ('dump: NO MESSAGE !');
End If;

Begin
Fnd_Global.Apps_Initialize (User_Id => 1184,
Resp_Id => 50367,
Resp_Appl_Id => 7000
);
V_Request_Id :=
Fnd_Request.Submit_Request ('OFA',
'FARET',
Null,
Null,
False,
L_Book_Type_Code
);
Commit;
Dbms_Output.Put_Line (V_Request_Id);
End;
End;
/

Exec Apps.XxARK_Asset_Retirment_Api (
461,--P_Asset_Id In Number,
'CU1 ASSET BOOK',--P_Book_Type_Code In Varchar2,
500,--P_Cost_Retired In Number,
300,--P_Proceeds_Of_Sale In Number,
150--P_Cost_Of_Removal In Number
)

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;