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

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

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