[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO XLA_Distribution_Links t1
(APPLICATION_ID,
EVENT_ID,
AE_HEADER_ID,
AE_LINE_NUM,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_NUM_1,
STATISTICAL_AMOUNT,
UNROUNDED_ENTERED_CR,
UNROUNDED_ENTERED_DR,
UNROUNDED_ACCOUNTED_CR,
UNROUNDED_ACCOUNTED_DR,
REF_AE_HEADER_ID,
ACCOUNTING_LINE_CODE,
ACCOUNTING_LINE_TYPE_CODE,
MERGE_DUPLICATE_CODE,
TAX_SUMMARY_LINE_REF_ID,
TAX_REC_NREC_DIST_REF_ID,
TEMP_LINE_NUM,
REF_EVENT_ID,
UPG_BATCH_ID,
LINE_DEFINITION_OWNER_CODE,
LINE_DEFINITION_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE)
SELECT 200 Application_ID,
Event_ID,
AE_Header_ID,
AE_Line_Num,
'AP_INV_DIST' Source_Distribution_Type,
Invoice_Distribution_ID,
Stat_Amount,
/* 5755674 Populating the distribution amounts instead of
the entered and accounted amounts from ae lines */
DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
DECODE(SIGN(NVL(Amount,0)),
-1, NULL,
0, DECODE(SIGN(Base_Amount),
-1, NULL,
NVL(Amount,0)),
NVL(Amount,0)),
DECODE(SIGN(NVL(Amount,0)),
-1, ABS(NVL(Amount,0)),
0, DECODE(SIGN(NVL(Base_Amount, Amount)),
-1, ABS(nvl(Amount,0)),
NULL),
NULL)) Entered_Cr,
DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
DECODE(SIGN(NVL(Amount,0)),
-1, ABS(NVL(Amount,0)),
0, DECODE(SIGN(Base_Amount),
-1, ABS(NVL(Amount,0)),
NULL),
NULL),
DECODE(SIGN(NVL(Amount,0)),
-1, NULL,
0, DECODE(SIGN(Base_Amount),
-1, NULL,
NVL(Amount,0)),
NVL(Amount,0))) Entered_Dr,
DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
DECODE(SIGN(NVL(Amount,0)),
-1, NULL,
0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
-1, NULL,
NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
DECODE(SIGN(NVL(Amount,0)),
-1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
-1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
NULL),
NULL)) Accounted_Cr,
DECODE(Accounting_Line_Code, 'AP_LIAB_INV',
DECODE(SIGN(NVL(Amount,0)),
-1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
-1, ABS(NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
NULL),
NULL),
DECODE(SIGN(NVL(Amount,0)),
-1, NULL,
0, DECODE(SIGN(NVL(Acctd_Amount, Amount)),
-1, NULL,
NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount)),
NVL(NVL2(Min_Acct_Unit,ROUND(Acctd_Amount/Min_Acct_Unit)*
Min_Acct_Unit, ROUND(Acctd_Amount, Precision)), Amount))) Accounted_Dr,
Ref_Ae_Header_ID,
Accounting_Line_Code,
'S' Accounting_Line_Type_Code,
'N' Merge_Duplicate_Code,
Summary_Tax_Line_ID,
Detail_Tax_Dist_ID,
Row_Number() OVER (PARTITION BY AE_Header_ID ORDER BY AE_Line_Num,
Invoice_Line_Number, Distribution_Line_Number) Temp_Line_Num,
Ref_Event_ID,
UPG_Batch_ID,
'S' Line_Definition_Owner_Code,
'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
'INVOICES' Event_Class_Code,
'INVOICES_ALL' Event_Type_Code
FROM (
SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg) no_expand
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1)
use_nl_with_index (aid, ap_invoice_distributions_n27) */
AEH.Event_ID,
AEH.AE_Header_ID,
AEL.AE_Line_Num,
AID.Invoice_Distribution_ID,
AID.Stat_Amount,
AID.Amount Amount,
NVL(AID.Base_Amount, Amount) Base_Amount,
AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
AEH.AE_Header_ID Ref_AE_Header_ID,
'AP_LIAB_INV' Accounting_Line_Code,
AID.Summary_Tax_Line_ID,
AID.Detail_Tax_Dist_ID,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
AID.Invoice_Line_Number,
AID.Distribution_Line_Number,
FC.Minimum_Accountable_Unit Min_Acct_Unit,
FC.Precision Precision
FROM AP_Invoices_All AI,
XLA_Upgrade_Dates UPG,
AP_Accounting_Events_All AAE,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Invoice_Distributions_All AID,
FND_Currencies FC
WHERE AI.Invoice_ID between p_start_id and p_end_id
AND AI.Set_Of_Books_ID = UPG.Ledger_ID
AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
AND AI.Invoice_ID = AAE.Source_ID
AND AAE.Source_Table = 'AP_INVOICES'
AND AAE.AX_Accounted_Flag IS NULL
AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
'PREPAYMENT UNAPPLICATION')
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND AEL.Source_Table = 'AP_INVOICES'
AND AEL.Accounting_Class_Code IN ('LIABILITY')
AND AID.Invoice_ID = AEL.Source_ID
AND AID.Invoice_ID = AI.Invoice_ID
AND AID.Accounting_Event_ID = AAE.Accounting_Event_ID
AND AID.Line_Type_Lookup_Code <> 'PREPAY'
AND AID.Prepay_Tax_Parent_ID IS NULL
AND AEL.Account_Overlay_Source_ID IS NULL
AND AEL.Currency_Code = FC.Currency_Code
UNION ALL
SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg) no_expand
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1)
use_nl_with_index (aid, ap_invoice_distributions_n27) */
AEH.Event_ID,
AEH.AE_Header_ID,
AEL.AE_Line_Num,
AID.Invoice_Distribution_ID,
AID.Stat_Amount,
AID.Amount Amount,
NVL(AID.Base_Amount, Amount) Base_Amount,
AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
AEH.AE_Header_ID Ref_AE_Header_ID,
'AP_LIAB_INV' Accounting_Line_Code,
AID.Summary_Tax_Line_ID,
AID.Detail_Tax_Dist_ID,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
AID.Invoice_Line_Number,
AID.Distribution_Line_Number,
FC.Minimum_Accountable_Unit Min_Acct_Unit,
FC.Precision Precision
FROM AP_Invoices_All AI,
XLA_Upgrade_Dates UPG,
AP_Accounting_Events_All AAE,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Invoice_Distributions_All AID,
FND_Currencies FC
WHERE AI.Invoice_ID between p_start_id and p_end_id
AND AI.Set_Of_Books_ID = UPG.Ledger_ID
AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
AND AI.Invoice_ID = AAE.Source_ID
AND AAE.Source_Table = 'AP_INVOICES'
AND AAE.AX_Accounted_Flag IS NULL
AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
'PREPAYMENT UNAPPLICATION')
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND AEL.Source_Table = 'AP_INVOICES'
AND AEL.Accounting_Class_Code IN ('LIABILITY')
AND AID.Invoice_ID = AEL.Source_ID
AND AID.Invoice_ID = AI.Invoice_ID
AND AID.Accounting_Event_ID = AAE.Accounting_Event_ID
AND AID.Line_Type_Lookup_Code <> 'PREPAY'
AND AID.Prepay_Tax_Parent_ID IS NULL
AND AEL.Account_Overlay_Source_ID = AID.Old_Distribution_ID
AND AEL.Currency_Code = FC.Currency_Code
UNION ALL
SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg)
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1)
use_nl_with_index (aid, ap_invoice_distributions_n26) */
AEH.Event_id,
AEH.AE_Header_ID,
AEL.AE_Line_Num,
AID.Invoice_Distribution_ID,
AID.Stat_Amount,
AID.Amount Amount,
NVL(AID.Base_Amount, Amount) Base_Amount,
AID.Amount * AEL.Currency_Conversion_Rate Acctd_Amount,
AEH.AE_Header_ID Ref_AE_Header_ID,
DECODE(AID.Line_Type_Lookup_Code, 'ITEM', 'AP_ITEM_EXPENSE',
'FREIGHT', 'AP_FREIGHT_EXPENSE', 'MISCELLANEOUS',
'AP_MISC_EXPENSE', 'REC_TAX', 'AP_RECOV_TAX', 'NONREC_TAX',
'AP_NON_RECOV_TAX', 'AWT', 'AP_WITHHOLD_TAX', 'TIPV',
'AP_INV_PRICE_VAR', 'TERV', 'AP_TAX_EX_RATE_VAR',
'IPV', 'AP_INV_PRICE_VAR', 'ERV', 'AP_EX_RATE_VAR') ||
DECODE(AI.Invoice_Type_Lookup_Code, 'CREDIT MEMO', '_CM',
'DEBIT MEMO', '_DM', 'PREPAYMENT', '_PREPAY', '_INV')
Accounting_Line_Code,
AID.Summary_Tax_Line_ID,
AID.Detail_Tax_Dist_ID,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
AID.Invoice_Line_Number,
AID.Distribution_Line_Number,
FC.Minimum_Accountable_Unit Min_Acct_Unit,
FC.Precision Precision
FROM AP_Invoices_All AI,
XLA_Upgrade_Dates UPG,
AP_Accounting_Events_All AAE,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Invoice_Distributions_All AID,
FND_Currencies FC
WHERE AI.Invoice_ID between p_start_id and p_end_id
AND AI.Set_Of_Books_ID = UPG.Ledger_ID
AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
AND AI.Invoice_ID = AAE.Source_ID
AND AAE.Source_Table = 'AP_INVOICES'
AND AAE.AX_Accounted_Flag IS NULL
AND AAE.Event_Type_Code NOT IN ('PREPAYMENT APPLICATION',
'PREPAYMENT UNAPPLICATION')
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND AEL.Source_Table = 'AP_INVOICE_DISTRIBUTIONS'
AND AEL.Accounting_Class_Code IN ('ACCRUAL', 'ITEM EXPENSE', 'IPV',
'EXCHANGE_RATE_VARIANCE', 'FREIGHT', 'NRTAX', 'AWT', 'RTAX',
'PREPAID_EXPENSE')
AND AID.Invoice_id = AI.Invoice_id
AND DECODE(AEL.Accounting_Class_Code, 'ACCRUAL', 'ITEM',
'ITEM EXPENSE', 'ITEM', 'NRTAX', 'NONREC_TAX', 'RTAX', 'REC_TAX',
'EXCHANGE_RATE_VARIANCE', 'ERV', 'PREPAID_EXPENSE', 'ITEM',
AEL.Accounting_Class_Code) =
DECODE(AID.Line_Type_Lookup_Code, 'TIPV', 'IPV', 'TERV', 'ERV',
'MISCELLANEOUS', 'ITEM', AID.Line_Type_Lookup_Code)
AND AEL.Source_ID = AID.Old_Distribution_ID
AND AEL.Currency_Code = FC.Currency_Code);
'Insert into AP_Prepay_History_All');
INSERT INTO AP_Prepay_History_All
(PREPAY_HISTORY_ID,
PREPAY_INVOICE_ID,
PREPAY_LINE_NUM,
ACCOUNTING_EVENT_ID,
HISTORICAL_FLAG,
INVOICE_ID,
ORG_ID,
POSTED_FLAG,
RELATED_PREPAY_APP_EVENT_ID,
TRANSACTION_TYPE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE,
INVOICE_LINE_NUMBER,
ACCOUNTING_DATE)
SELECT /*+ ordered use_nl_with_index(AIL,AP_INVOICE_LINES_U1) */ ap_prepay_history_s.nextval,
AIL.Prepay_Invoice_ID,
AIL.Prepay_Line_Number,
APH.Accounting_Event_ID,
'Y',
APH.Invoice_ID,
APH.Org_ID,
APH.Posted_Flag,
APH.Accounting_Event_ID,
decode(sign(APH.amount), -1, 'PREPAYMENT APPLIED',
'PREPAYMENT UNAPPLIED') Transaction_Type,
FND_GLOBAL.User_ID Last_Updated_By,
Sysdate Last_Update_Date,
FND_GLOBAL.Conc_Login_ID Last_Update_Login,
FND_GLOBAL.User_ID Created_By,
Sysdate Creation_Date,
APH.Invoice_Line_Number,
APH.Accounting_Date
FROM
(SELECT /*+ ordered index(AI, ap_invoices_u1) swap_join_inputs(UPG)
use_nl_with_index(AID,AP_INVOICE_DISTRIBUTIONS_U1) */
AID.Accounting_Event_ID,
AID.Invoice_ID,
AID.Org_ID,
AID.Posted_Flag,
AID.Amount,
AID.Invoice_Line_Number,
AID.Accounting_Date,
Row_Number() OVER (PARTITION BY AID.Accounting_Event_ID, AID.Invoice_ID
ORDER BY Invoice_Line_Number) RNum
FROM AP_Invoices_All AI,
XLA_Upgrade_Dates UPG,
AP_Invoice_Distributions_All AID
WHERE AI.Invoice_ID BETWEEN p_start_id and p_end_id
AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date and UPG.End_Date
AND AI.Set_Of_Books_ID = UPG.Ledger_ID
AND AID.Invoice_ID = AI.Invoice_ID
AND AID.Line_Type_Lookup_Code = 'PREPAY'
AND AID.Accounting_Event_ID IS NOT NULL) APH,
AP_Invoice_Lines_All AIL
WHERE AIL.Invoice_ID = APH.Invoice_ID
AND AIL.Line_Number = APH.Invoice_Line_Number
AND AIL.Prepay_Invoice_ID IS NOT NULL
AND APH.RNum = 1;
'Insert into AP_Prepay_App_Dists');
INSERT INTO AP_Prepay_App_Dists
(PREPAY_APP_DIST_ID,
PREPAY_DIST_LOOKUP_CODE,
INVOICE_DISTRIBUTION_ID,
PREPAY_APP_DISTRIBUTION_ID,
ACCOUNTING_EVENT_ID,
PREPAY_HISTORY_ID,
PA_ADDITION_FLAG,
AMOUNT,
BASE_AMOUNT,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATED_BY,
CREATION_DATE)
SELECT AP_Prepay_App_Dists_S.Nextval,
Prepay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Prepay_App_Distribution_ID,
Accounting_Event_ID,
Prepay_History_ID,
PA_Addition_Flag,
DECODE(Rank_Num, Dist_Count, Entered_Amount + Delta_Entered, Entered_Amount) Amount,
DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Accounted,
Accounted_Amount) Base_Amount,
FND_GLOBAL.User_ID Last_Updated_By,
SYSDATE Last_Update_Date,
FND_GLOBAL.Conc_Login_ID Last_Update_Login,
FND_GLOBAL.User_ID Created_By,
SYSDATE Creation_Date
FROM (
SELECT Prepay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Prepay_App_Distribution_ID,
Accounting_Event_ID,
Prepay_History_ID,
PA_Addition_Flag,
NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)) Entered_Amount,
NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)) Accounted_Amount,
NVL2(Minimum_Accountable_Unit, ROUND(Line_Entered_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, Line_Entered_Amt) -
SUM(NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)))
OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
Partkey) Delta_Entered,
NVL2(Minimum_Accountable_Unit, ROUND(Line_Accounted_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, Line_Accounted_Amt) -
SUM(NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)))
OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
Partkey) Delta_Accounted,
RANK() OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code, Partkey
Order By Amount, Distribution_Line_Number) Rank_Num,
COUNT(*) OVER (Partition By Invoice_ID, AE_Header_ID, Prepay_Dist_Lookup_Code,
Partkey) Dist_Count
FROM (
SELECT /*+ ordered use_hash (asp) index (ai, ap_invoices_u1)
swap_join_inputs (asp) swap_join_inputs (upg)
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (aph, ap_prepay_history_n1)
use_nl_with_index (aid, ap_invoice_distributions_n27)
use_nl_with_index (aid1, ap_invoice_distributions_u2)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1)
use_nl_with_index (aidp, ap_invoice_distributions_n26) */
DECODE(AEL.Accounting_Class_Code, 'RTAX',
'PREPAY APPL REC TAX', 'NRTAX', 'PREPAY APPL NONREC TAX',
'PREPAY APPL') Prepay_Dist_Lookup_Code,
AID.Invoice_Distribution_ID Invoice_Distribution_ID,
AIDP.Invoice_Distribution_ID Prepay_App_Distribution_ID,
AAE.Accounting_Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
APH.Prepay_History_ID Prepay_History_ID,
AID.PA_Addition_Flag PA_Addition_Flag,
AI.Invoice_ID Invoice_ID,
AID.Amount Amount,
AID.Distribution_Line_Number Distribution_Line_Number,
NVL2(AEL.Account_Overlay_Source_ID, AID1.Invoice_Distribution_ID, 1) Partkey,
FC.Minimum_Accountable_Unit Minimum_Accountable_Unit,
FC.Precision Precision,
NVL(AEL.Accounted_Cr, 0) - NVL(AEL.Accounted_Dr, 0) Line_Accounted_Amt,
NVL(AEL.Entered_Cr, 0) - NVL(AEL.Entered_Dr, 0) Line_Entered_Amt,
(NVL(AEL.Accounted_Cr, 0) - NVL(AEL.Accounted_Dr, 0)) *
NVL(AID.Base_amount, AID.Amount) / NVL2(AEL.Account_Overlay_Source_ID,
DECODE(NVL(AID1.base_amount, AID1.amount), 0, 1,
NVL(AID1.Base_Amount, AID1.Amount)),
DECODE(NVL(AI.Base_Amount, AI.Invoice_Amount), 0, 1,
NVL(AI.Base_Amount, AI.Invoice_Amount))) Accounted_Amt,
(NVL(AEL.Entered_Cr, 0) - NVL(AEL.Entered_Dr, 0)) * AID.Amount /
NVL2(AEL.Account_Overlay_Source_ID, DECODE(AID1.Amount,0,1,AID1.Amount),
DECODE(AI.Invoice_Amount,0,1,AI.Invoice_Amount)) Entered_Amt
FROM AP_Invoices_All AI,
XLA_Upgrade_Dates UPG,
AP_System_Parameters_All ASP,
AP_Accounting_Events_All AAE,
AP_Prepay_History_All APH,
AP_Invoice_Distributions_All AID,
AP_Inv_Dists_Source AID1,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Invoice_Distributions_All AIDP,
FND_Currencies FC
WHERE AI.Invoice_ID BETWEEN p_start_id AND p_end_id
AND AI.Set_Of_Books_ID = UPG.Ledger_ID
AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date AND UPG.End_Date
AND AI.Org_ID = ASP.Org_ID
AND AI.Invoice_ID = AAE.Source_ID
AND AAE.Source_Table = 'AP_INVOICES'
AND AAE.AX_Accounted_Flag IS NULL
AND AAE.Event_Type_Code IN ('PREPAYMENT APPLICATION', 'PREPAYMENT UNAPPLICATION')
AND AI.Invoice_ID = APH.Invoice_ID
AND AAE.Accounting_Event_ID = APH.Accounting_Event_ID
AND AID.Invoice_ID = AI.Invoice_ID
AND AID.Line_Type_Lookup_Code <> 'PREPAY'
AND AID.Prepay_Tax_Parent_ID IS NULL
AND AID1.Invoice_ID = AI.Invoice_ID
AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEH.Ledger_ID = ASP.Set_Of_Books_ID
AND AEH.AE_Header_ID = AEL.AE_Header_ID
AND AEL.Application_ID = AEH.Application_ID
AND AIDP.Accounting_Event_ID = APH.Accounting_Event_ID
AND AIDP.Old_Distribution_ID = AEL.Source_ID
AND AIDP.Accounting_Event_ID <> AID1.Accounting_Event_ID
AND AEL.Source_Table = 'AP_INVOICE_DISTRIBUTIONS'
AND AEL.Accounting_Class_Code IN ('PREPAID_EXPENSE', 'RTAX', 'NRTAX')
AND AID.Old_Distribution_ID = NVL(AEL.Account_Overlay_Source_ID,
AID.Old_Distribution_ID)
AND FC.Currency_Code = ASP.Base_Currency_Code));
'Insert into Distribution Links for Prepayments');
INSERT INTO XLA_Distribution_Links t1
(APPLICATION_ID,
EVENT_ID,
AE_HEADER_ID,
AE_LINE_NUM,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_NUM_1,
STATISTICAL_AMOUNT,
UNROUNDED_ENTERED_CR,
UNROUNDED_ENTERED_DR,
UNROUNDED_ACCOUNTED_CR,
UNROUNDED_ACCOUNTED_DR,
REF_AE_HEADER_ID,
ACCOUNTING_LINE_CODE,
ACCOUNTING_LINE_TYPE_CODE,
MERGE_DUPLICATE_CODE,
TEMP_LINE_NUM,
REF_EVENT_ID,
UPG_BATCH_ID,
LINE_DEFINITION_OWNER_CODE,
LINE_DEFINITION_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE)
SELECT /*+ ordered index (ai, ap_invoices_u1) swap_join_inputs (upg)
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (apad, ap_prepay_app_dists_n3)
use_nl_with_index (aid, ap_invoice_distributions_u2)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1) */
200 Application_ID,
AEH.Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
AEL.AE_Line_Num AE_Line_Num,
'AP_INV_DIST' Source_Distribution_Type,
APAD.Prepay_App_Dist_ID Source_Distribution_ID_Num_1,
NULL Statistical_Amount,
DECODE(SIGN(APAD.Amount), 1, APAD.Amount, NULL) Unrounded_Entered_Cr,
DECODE(SIGN(APAD.Amount),-1, APAD.Amount, NULL) Unrounded_Entered_Dr,
DECODE(SIGN(APAD.Base_Amount), 1, APAD.Base_Amount, NULL) Unrounded_Accounted_Cr,
DECODE(SIGN(APAD.Base_Amount),-1, APAD.Base_Amount, NULL) Unrounded_Accounted_Dr,
AEH.AE_Header_ID Ref_AE_Header_ID,
DECODE(AEL.Accounting_Class_Code,
'GAIN', 'AP_GAIN_PREPAY_APP', 'LOSS', 'AP_LOSS_PREPAY_APP',
'LIABILITY', 'AP_LIABILITY_PREPAY_APP', 'PREPAID_EXPENSE',
'AP_PREPAID_EXP_ACCR_PREPAY_APP', 'ROUNDING',
'AP_FINAL_PMT_ROUND_PREPAY_APP', 'NRTAX',
'AP_NRTAX_PREPAY_PAY_RATE_APP', 'RTAX', 'AP_RECOV_PREPAY_PAY_RATE_APP',
'ACCRUAL', 'AP_ACCR_PREPAY_PAY_RATE_APP', 'ITEM EXPENSE',
'AP_ITEM_PREPAY_PAY_RATE_APP',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PREPAY_PAY_RATE',
'IPV', 'AP_IPV_PREPAY_PAY_RATE_APP', 'NRTAX',
'AP_NRTAX_PREPAY_PAY_RATE_APP', 'RTAX',
'AP_RECOV_PREPAY_PAY_RATE_APP', 'FREIGHT',
'AP_FREIGHT_PREPAY_PAY_RATE_APP', 'AP_ITEM_PREPAY_PAY_RATE_APP')
Accounting_Line_Code,
'S' Accounting_Line_Type_Code,
'N' Merge_Duplicate_Code,
RANK() OVER (PARTITION BY AEH.AE_Header_ID
ORDER BY AEL.AE_Line_Num,
APAD.Invoice_Distribution_ID,
APAD.Prepay_App_Distribution_ID,
APAD.Prepay_Dist_Lookup_Code) Temp_Line_Num,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
'S' Line_Definition_Owner_Code,
'ACCRUAL_INVOICES_ALL' Line_Definition_Code,
'INVOICES' Event_Class_Code,
'INVOICES_ALL' Event_Type_Code
FROM AP_Invoices_All AI,
XLA_Upgrade_Dates UPG,
AP_Accounting_Events_All AAE,
AP_Prepay_App_Dists APAD,
AP_Invoice_Distributions_All AID,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL
WHERE AI.Invoice_ID BETWEEN p_start_id AND p_end_id
AND TRUNC(AI.GL_Date) BETWEEN UPG.Start_Date and UPG.End_Date
AND AI.Set_Of_Books_ID = UPG.Ledger_ID
AND AI.Invoice_ID = AID.Invoice_ID
AND AAE.Source_Table = 'AP_INVOICES'
AND AI.Invoice_ID = AAE.Source_ID
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AAE.AX_Accounted_Flag IS NULL
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND AAE.Accounting_Event_ID = APAD.Accounting_Event_ID
AND APAD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AID.Old_Distribution_ID
= NVL(AEL.Account_Overlay_Source_ID, AID.Old_Distribution_ID);
'Insert into ap_payment_hist_dists');
INSERT INTO ap_payment_hist_dists
(PAYMENT_HIST_DIST_ID,
ACCOUNTING_EVENT_ID,
PAY_DIST_LOOKUP_CODE,
INVOICE_DISTRIBUTION_ID,
AMOUNT,
PAYMENT_HISTORY_ID,
INVOICE_PAYMENT_ID,
CLEARED_BASE_AMOUNT,
HISTORICAL_FLAG,
MATURED_BASE_AMOUNT,
PAID_BASE_AMOUNT,
REVERSAL_FLAG,
CREATED_BY,
CREATION_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
PA_ADDITION_FLAG)
SELECT AP_Payment_Hist_Dists_S.Nextval,
Accounting_Event_ID,
Pay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Decode(Rank_Num, Dist_Count, Entered_Amount + Delta_Entered, Entered_Amount) Amount,
Payment_History_ID,
Invoice_Payment_ID,
(CASE
WHEN (Accounting_Class_Code IN ('CASH', 'DISCOUNT') AND Recon_Accounting_Flag = 'Y')
OR (Accounting_Class_Code IN ('BANK_ CHG', 'BANK_ERROR')) THEN
DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
ELSE NULL
END) Cleared_Base_Amount,
'Y' Historical_Flag,
(CASE
WHEN (Accounting_Class_Code = 'CASH_CLEARING' AND
Future_Pay_Due_Date IS NOT NULL)
OR (Accounting_Class_Code = 'CASH' AND
Future_Pay_Due_Date IS NOT NULL AND Recon_Accounting_Flag = 'N') THEN
DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
ELSE NULL
END) Matured_Base_Amount,
(CASE
WHEN (Accounting_Class_Code IN ('CASH_CLEARING', 'DISCOUNT') AND
Future_Pay_Due_Date IS NULL)
OR (Accounting_Class_Code IN ('CASH', 'DISCOUNT') AND
Future_Pay_Due_Date IS NULL AND Recon_Accounting_Flag = 'N')
OR (Accounting_Class_Code = 'FUTURE_DATED_PMT' AND
Future_Pay_Due_Date IS NULL AND Recon_Accounting_Flag = 'N') THEN
DECODE(Rank_Num, Dist_Count, Accounted_Amount + Delta_Entered, Accounted_Amount)
ELSE NULL
END) Paid_Base_Amount,
Reversal_Flag,
FND_GLOBAL.User_ID Created_By,
Sysdate Creation_Date,
Sysdate Last_Update_Date,
FND_GLOBAL.User_ID Last_Updated_By,
FND_GLOBAL.Conc_Login_ID Last_Update_Login,
PA_Addition_Flag
FROM (SELECT Pay_Dist_Lookup_Code,
Invoice_Distribution_ID,
Accounting_Event_ID,
Payment_History_ID,
Invoice_Payment_ID,
Accounting_Class_Code,
PA_Addition_Flag,
Reversal_Flag,
Recon_Accounting_Flag,
Future_Pay_Due_Date,
NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)) Entered_Amount,
NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)) Accounted_Amount,
NVL2(Minimum_Accountable_Unit, ROUND(Line_Entered_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, Line_Entered_Amt) -
SUM(NVL2(Minimum_Accountable_Unit, ROUND(Entered_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Entered_Amt, Precision)))
OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
Partkey) Delta_Entered,
NVL2(Minimum_Accountable_Unit, ROUND(Line_Accounted_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, Line_Accounted_Amt) -
SUM(NVL2(Minimum_Accountable_Unit, ROUND(Accounted_Amt/Minimum_Accountable_Unit)*
Minimum_Accountable_Unit, ROUND(Accounted_Amt, Precision)))
OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
Partkey) Delta_Accounted,
Rank() OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
Partkey
Order By Amount, Distribution_Line_Number) Rank_Num,
Count(*) OVER (Partition By Invoice_ID, AE_Header_ID, Pay_Dist_Lookup_Code,
Partkey) Dist_Count
FROM (SELECT /*+ ordered use_hash(asp, upg) index (ac, ap_checks_u1)
swap_join_inputs (upg) swap_join_inputs (asp)
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1)
use_nl_with_index (aip, ap_invoice_payments_n8)
use_nl_with_index (aph, ap_payment_history_n2)
use_nl_with_index (aid1, ap_invoice_distributions_u2) */
AAE.Accounting_Event_ID,
APH.Payment_History_ID,
AIP.Invoice_Payment_ID,
DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'CASH',
'CASH_CLEARING', 'CASH', 'CASH', 'CASH', Accounting_Class_Code)
Pay_Dist_Lookup_Code,
AC.Future_Pay_Due_Date,
ASP.Recon_Accounting_Flag,
AEH.AE_Header_ID,
AID.Invoice_ID,
AID.Invoice_Distribution_ID,
AID.Distribution_Line_Number,
AID.Amount,
AID.PA_Addition_Flag,
AIP.Reversal_Flag,
AEL.Accounting_Class_Code,
NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0) Line_Entered_Amt,
NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0) Line_Accounted_Amt,
DECODE(AEL.Account_Overlay_Source_ID, NULL,
(NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0))
* NVL(AID.Base_Amount, AID.Amount)
/ DECODE(NVL(AI.Base_Amount, AI.Invoice_Amount), 0, 1,
NVL(AI.Base_Amount, AI.Invoice_Amount)),
(NVL(AEL.Accounted_Cr,0) - NVL(AEL.Accounted_Dr,0))
* NVL(AID.Base_Amount,AID.Amount)
/ DECODE(NVL(AID1.Base_Amount,AID1.Amount), 0, 1,
NVL(AID1.Base_Amount,AID1.Amount))) Accounted_Amt,
DECODE(AEL.Account_Overlay_Source_ID, NULL,
(NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0))
* AID.Amount / DECODE(AI.Invoice_Amount,0,1,AI.Invoice_Amount),
(NVL(AEL.Entered_Cr,0) - NVL(AEL.Entered_Dr,0))
* AID.Amount / DECODE(AID1.Amount,0,1,AID1.Amount)) Entered_Amt,
NVL2(AEL.Account_Overlay_Source_ID, AID1.Invoice_Distribution_ID, 1) Partkey,
FC.Minimum_Accountable_Unit,
FC.Precision
FROM AP_Checks_All AC,
AP_System_Parameters_All ASP,
XLA_Upgrade_Dates UPG,
AP_Accounting_Events_All AAE,
AP_Invoice_Payments_All AIP,
AP_Payment_History_All APH,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Invoice_Distributions_All AID,
AP_Invoices_All AI,
AP_Inv_Dists_Source AID1,
FND_Currencies FC
WHERE AC.Check_ID BETWEEN p_start_id AND p_end_id
AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
AND AC.Org_ID = ASP.Org_ID
AND AAE.Source_Table = 'AP_CHECKS'
AND AAE.Source_ID = AC.Check_ID
AND AAE.AX_Accounted_Flag IS NULL
AND AAE.Accounting_Event_ID = APH.Accounting_Event_ID
AND APH.Check_ID = AC.Check_ID
AND AIP.Accounting_Event_ID = AAE.Accounting_Event_ID
AND AIP.Check_ID = AC.Check_ID
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND AIP.Invoice_ID = AID.Invoice_ID
AND AID.Old_Distribution_ID = NVL (AEL.Account_Overlay_Source_ID,
AID.Old_Distribution_ID)
AND ASP.Set_Of_Books_ID = AEH.Ledger_ID
AND AI.Invoice_ID = AIP.Invoice_ID
AND AID1.Invoice_Distribution_ID = AID.Old_Distribution_ID
AND ((AEL.Source_Table = 'AP_INVOICE_PAYMENTS'
AND AEL.Source_ID = AIP.Invoice_Payment_ID)
OR (AEL.Source_Table = 'AP_CHECKS'
AND AEL.Source_ID = AC.Check_ID))
AND ((DECODE(APH.Transaction_Type, 'PAYMENT CLEARING', 'CASH',
'PAYMENT UNCLEARING', 'CASH',
'PAYMENT MATURITY', DECODE (ASP.Recon_Accounting_Flag, 'Y',
'CASH_CLEARING', 'CASH'),
'PAYMENT CREATED', DECODE(AC.Future_Pay_Due_Date, NULL,
DECODE(ASP.Recon_Accounting_Flag, 'Y', 'CASH_CLEARING', 'CASH'),
'FUTURE_DATED_PMT'),
'PAYMENT CANCELLED', DECODE(AC.Future_Pay_Due_Date, NULL,
DECODE(ASP.Recon_Accounting_Flag, 'Y', 'CASH_CLEARING', 'CASH'),
'FUTURE_DATED_PMT')) = AEL.Accounting_Class_Code)
OR (AEL.Accounting_Class_Code IN ('DISCOUNT','BANK_CHG', 'BANK_ERROR')))
AND FC.Currency_Code = ASP.Base_Currency_Code));
'Insert into Distribution Links for payments');
INSERT INTO XLA_Distribution_Links t1
(APPLICATION_ID,
EVENT_ID,
AE_HEADER_ID,
AE_LINE_NUM,
SOURCE_DISTRIBUTION_TYPE,
SOURCE_DISTRIBUTION_ID_NUM_1,
UNROUNDED_ENTERED_CR,
UNROUNDED_ENTERED_DR,
UNROUNDED_ACCOUNTED_CR,
UNROUNDED_ACCOUNTED_DR,
REF_AE_HEADER_ID,
ACCOUNTING_LINE_CODE,
ACCOUNTING_LINE_TYPE_CODE,
MERGE_DUPLICATE_CODE,
TEMP_LINE_NUM,
REF_EVENT_ID,
UPG_BATCH_ID,
LINE_DEFINITION_OWNER_CODE,
LINE_DEFINITION_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE)
SELECT /*+ ordered use_hash(asp, upg) index (ac, ap_checks_u1)
swap_join_inputs (upg) swap_join_inputs (asp)
use_nl_with_index (aae, ap_accounting_events_n1)
use_nl_with_index (aeh, xla_ae_headers_n2)
use_nl_with_index (ael, xla_ae_lines_u1)
use_nl_with_index (aph, ap_payment_history_n2)
use_nl_with_index (aphd, ap_payment_hist_dists_n1)
use_nl_with_index (aid, ap_invoice_distributions_u2) */
200 Application_ID,
AEH.Event_ID Accounting_Event_ID,
AEH.AE_Header_ID AE_Header_ID,
AEL.AE_Line_Num AE_Line_Num,
'AP_PMT_DIST' Source_Distribution_Type,
APHD.Payment_Hist_Dist_ID Source_Distribution_ID_Num_1,
DECODE(SIGN(APHD.Amount), 1, APHD.Amount, NULL) Unrounded_Entered_Cr,
DECODE(SIGN(APHD.Amount),-1, APHD.Amount, NULL) Unrounded_Entered_Dr,
DECODE(SIGN(APHD.Paid_Base_Amount), 1, APHD.Paid_Base_Amount, NULL) Unrounded_Accounted_Cr,
DECODE(SIGN(APHD.Paid_Base_Amount),-1, APHD.Paid_Base_Amount, NULL) Unrounded_Accounted_Dr,
AEH.AE_Header_ID Ref_AE_Header_ID,
(CASE
WHEN AC.Payment_Type_Flag = 'R' THEN
DECODE(AEL.Accounting_Class_Code,
'CASH_CLEARING', 'AP_CASH_CLEAR_REF', 'CASH', 'AP_CASH_REF',
'ACCRUAL', 'AP_ACCRUAL_REF', 'DISCOUNT', 'AP_DISCOUNT_ACCR_REF',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_REF',
'ROUNDING', 'AP_FINAL_PMT_ROUNDING_REF',
'GAIN', 'AP_GAIN_REF', 'FREIGHT', 'AP_FREIGHT_EXPENSE_REF',
'IPV', 'AP_INV_PRICE_VAR_REF', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_REF',
'LOSS', 'AP_LOSS_REF', 'LIABILITY', 'AP_LIAB_REF',
'NRTAX', 'AP_NON_RECOV_TAX_REF',
'PREPAID_EXPENSE', 'AP_PREPAID_EXP_REF', 'RTAX','AP_RECOV_TAX_REF',
'AWT', 'AP_WITHHOLD_TAX_ACCR_REF')
WHEN APH.Transaction_Type = 'PAYMENT MATURITY' THEN
DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT_MAT',
'CASH_CLEARING', 'AP_CASH_CLEAR_PMT_MAT',
'CASH', 'AP_CASH_PMT_MAT', 'GAIN', 'AP_GAIN_PMT_MAT',
'LOSS', 'AP_LOSS_PMT_MAT',
'ROUNDING', 'AP_FUTURE_PMT_ROUNDING_MAT')
WHEN APH.Transaction_Type IN ('PAYMENT CREATED', 'PAYMENT CANCELLED') THEN
DECODE(AEL.Accounting_Class_Code, 'FUTURE_DATED_PMT', 'AP_FUTURE_DATED_PMT',
'CASH_CLEARING', 'AP_CASH_CLEAR_PMT', 'CASH', 'AP_CASH_PMT',
'ACCRUAL', 'AP_ACCRUAL_PMT', 'DISCOUNT', 'AP_DISCOUNT_ACCR_PMT',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_PMT',
'ROUNDING', 'AP_FINAL_PMT_ROUNDING_PMT',
'GAIN', 'AP_GAIN_PMT', 'FREIGHT', 'AP_FREIGHT_EXPENSE_PMT',
'IPV', 'AP_INV_PRICE_VAR_PMT', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_PMT',
'LOSS', 'AP_LOSS_PMT', 'LIABILITY', 'AP_LIAB_PMT',
'NRTAX', 'AP_NON_RECOV_TAX_PMT',
'PREPAID_EXPENSE', 'AP_PREPAID_EXP_PMT', 'RTAX','AP_RECOV_TAX_PMT',
'AWT', 'AP_WITHHOLD_TAX_ACCR_PMT')
WHEN APH.Transaction_Type IN ('PAYMENT CLEARING', 'PAYMENT UNCLEARING') THEN
DECODE(AEL.Accounting_Class_Code, 'BANK_CHG', 'AP_BANK_CHARGES_CLEAR',
'CASH_CLEARING', 'AP_CASH_CLEAR_CLEAR', 'CASH', 'AP_CASH_CLEAR',
'ACCRUAL', 'AP_ACCRUAL_CLEAR', 'DISCOUNT', 'AP_DISCOUNT_ACCR_CLEAR',
'EXCHANGE_RATE_VARIANCE', 'AP_EX_RATE_VAR_CLEAR','BANK_ERROR', 'AP_BANK_ERROR_CLEAR',
'ROUNDING', 'AP_FINAL_PMT_ROUNDING_CLEAR',
'GAIN', 'AP_GAIN_PMT_CLEAR', 'FREIGHT', 'AP_FREIGHT_EXPENSE_CLEAR',
'IPV', 'AP_INV_PRICE_VAR_CLEAR', 'ITEM EXPENSE', 'AP_ITEM_EXPENSE_CLEAR',
'LOSS', 'AP_LOSS_PMT_CLEAR', 'LIABILITY', 'AP_LIAB_CLEAR',
'NRTAX', 'AP_NON_RECOV_TAX_CLEAR',
'RTAX','AP_RECOV_TAX_CLEAR',
'AWT', 'AP_WITHHOLD_TAX_ACCR_CLEAR')
END) AS Accounting_Line_Code,
'S' Accounting_Line_Type_Code,
'N' Merge_Duplicate_Code,
Row_Number() OVER (PARTITION BY AEH.AE_Header_ID
ORDER BY AEL.AE_Line_Num,
APHD.Invoice_Distribution_ID,
APHD.Invoice_Payment_ID,
APHD.Payment_History_ID) Temp_Line_Num,
AEH.Event_ID Ref_Event_ID,
AEL.Upg_Batch_ID,
'S' Line_Definition_Owner_Code,
'ACCRUAL_PAYMENTS_ALL' Line_Definition_Code,
'PAYMENTS' Event_Class_Code,
'PAYMENTS_ALL' Event_Type_Code
FROM AP_Checks_All AC,
AP_System_Parameters_All ASP,
XLA_Upgrade_Dates UPG,
AP_Accounting_Events_All AAE,
AP_Payment_History_All APH,
XLA_AE_Headers AEH,
XLA_AE_Lines AEL,
AP_Payment_Hist_Dists APHD,
AP_Invoice_Distributions_All AID
WHERE AC.Check_ID BETWEEN p_start_id AND p_end_id
AND TRUNC(AC.Check_Date) BETWEEN UPG.Start_Date and UPG.End_Date
AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
AND AC.Org_ID = ASP.Org_ID
AND AAE.Source_Table = 'AP_CHECKS'
AND AC.Check_ID = AAE.Source_ID
AND AAE.Accounting_Event_ID = AEH.Event_ID
AND AEH.Application_ID = 200
AND AAE.AX_Accounted_Flag IS NULL
AND AEL.AE_Header_ID = AEH.AE_Header_ID
AND AEL.Application_ID = 200
AND AAE.Accounting_Event_ID = APH.Accounting_Event_ID
AND APH.Check_ID = AC.Check_ID
AND APH.Payment_History_ID = APHD.Payment_History_ID
AND APHD.Invoice_Payment_ID = DECODE(AEL.Source_Table, 'AP_INVOICE_PAYMENTS',
AEL.Source_ID, APHD.Invoice_Payment_ID)
AND APHD.Invoice_Distribution_ID = AID.Invoice_Distribution_ID
AND AID.Old_Distribution_ID
= NVL(AEL.Account_Overlay_Source_ID, AID.Old_Distribution_ID);
SELECT Name
INTO l_sob_name
FROM GL_Ledgers
WHERE Ledger_ID = p_ledger_id;
SELECT count(*)
INTO l_org_count
FROM AP_System_Parameters_All
WHERE Set_Of_Books_ID = p_ledger_id
AND Future_Dated_Pmt_Liab_Relief = 'MATURITY';
IF p_mode = 'UPDATE' THEN
BEGIN
SELECT Start_Date,
End_Date
INTO l_gl_date_from,
l_gl_date_to
FROM XLA_Upgrade_Dates
WHERE Ledger_ID = p_ledger_id;
INSERT INTO xla_tb_balances_gt
(Definition_Code,
Code_Combination_ID,
Balance_Date,
Balance_Amount)
SELECT 'AP_200_' || p_ledger_id,
Code_Combination_ID,
Balance_Date,
Balance_Amount
FROM (SELECT APL.Code_Combination_ID Code_Combination_ID,
UPG.Start_Date Balance_Date,
SUM(NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0)) Balance_Amount
FROM AP_Liability_Balance APL,
XLA_Upgrade_Dates UPG
WHERE APL.Set_Of_Books_ID = p_ledger_id
AND APL.Set_Of_Books_ID = UPG.Ledger_ID
AND APL.Accounting_Date < UPG.Start_Date
HAVING SUM(NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0)) <> 0
GROUP BY APL.Set_Of_Books_ID,
APL.Code_Combination_ID,
UPG.Start_Date);
INSERT INTO xla_tb_balances_gt
(Definition_Code,
Code_Combination_ID,
Balance_Date,
Balance_Amount)
SELECT 'AP_200_' || p_ledger_id,
BAL.Code_Combination_ID,
BAL.Balance_Date,
SUM(BAL.Remaining_Amount)
FROM (SELECT APL.Set_Of_Books_ID Ledger_ID,
APL.Code_Combination_ID Code_Combination_ID,
UPG.Start_Date Balance_Date,
NVL(APL.Accounted_Cr,0) - NVL(APL.Accounted_Dr,0) Remaining_Amount
FROM AP_Liability_Balance APL,
XLA_Upgrade_Dates UPG
WHERE APL.Set_Of_Books_ID = p_ledger_id
AND APL.Set_Of_Books_ID = UPG.Ledger_ID
AND APL.Accounting_Date < UPG.Start_Date
UNION ALL
SELECT XEH.Ledger_ID Ledger_ID,
XEL.Code_Combination_ID Code_Combination_ID,
UPG.Start_Date Balance_Date,
NVL(XEL.Accounted_Cr,0) - NVL(XEL.Accounted_Dr,0) Remaining_Amount
FROM XLA_AE_Lines XEL,
XLA_AE_Headers XEH,
AP_Checks_ALL AC,
AP_System_Parameters_ALL ASP,
XLA_Upgrade_Dates UPG
WHERE XEL.Accounting_Class_Code = 'FUTURE_DATED_PMT'
AND XEL.AE_Header_ID = XEH.AE_Header_ID
AND XEH.GL_Transfer_Status_Code = 'Y'
AND TRUNC(XEH.Accounting_Date) < UPG.Start_Date
AND XEL.Source_Table = 'AP_CHECKS'
AND XEL.Source_ID = AC.Check_ID
AND AC.Org_ID = ASP.Org_ID
AND ASP.Set_Of_Books_ID = p_ledger_id
AND ASP.Set_Of_Books_ID = UPG.Ledger_ID
AND ASP.Future_Dated_Pmt_Liab_Relief = 'MATURITY'
AND NOT EXISTS (SELECT 'Payment Maturity'
FROM AP_Payment_History_All APH,
XLA_Events XLE,
XLA_AE_Headers XEH1
WHERE APH.Accounting_Event_ID = XLE.Event_ID
AND XLE.Event_ID = XEH1.Event_ID
AND APH.Check_ID = AC.Check_ID
AND APH.Transaction_Type = 'PAYMENT MATURITY'
AND TRUNC(APH.Accounting_Date) < UPG.Start_Date
and XEH1.GL_Transfer_Status_Code = 'Y')) BAL
HAVING SUM(BAL.Remaining_Amount) <> 0
GROUP BY BAL.Ledger_ID,
BAL.Code_Combination_ID,
BAL.Balance_Date;
l_mode VARCHAR2(30) := 'UPDATE';
l_sql_stmt := 'SELECT /*+ parallel(AI) parallel(GPS) use_merge(AI,GPS) */ '
|| ' MIN(INVOICE_ID), MAX(INVOICE_ID) '
|| 'FROM AP_INVOICES_ALL AI, '
|| 'XLA_UPGRADE_DATES GPS '
|| 'WHERE TRUNC(AI.GL_DATE) BETWEEN GPS.Start_Date AND GPS.End_Date '
|| 'AND GPS.Ledger_ID = AI.Set_Of_Books_ID';
ad_parallel_updates_pkg.initialize_id_range(
ad_parallel_updates_pkg.ID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
l_id_column,
p_worker_id,
p_num_workers,
p_batch_size, 0,
l_sql_stmt);
ad_parallel_updates_pkg.get_id_range(
l_start_id,
l_end_id,
l_any_rows_to_process,
p_batch_size,
TRUE);
ad_parallel_updates_pkg.processed_id_range
(l_rows_processed,
l_end_id);
ad_parallel_updates_pkg.get_id_range
(l_start_id,
l_end_id,
l_any_rows_to_process,
p_batch_size,
FALSE);
l_sql_stmt := 'SELECT MIN(AC.CHECK_ID), MAX(AC.CHECK_ID) ' ||
'FROM AP_CHECKS_ALL AC, ' ||
' XLA_UPGRADE_DATES GPS, ' ||
' AP_SYSTEM_PARAMETERS_ALL ASP ' ||
'WHERE TRUNC(AC.Check_Date) BETWEEN GPS.Start_Date ' ||
' AND GPS.End_Date ' ||
'AND GPS.Ledger_ID = ASP.Set_Of_Books_ID ' ||
'AND ASP.Org_ID = AC.Org_ID ';
ad_parallel_updates_pkg.initialize_id_range(
ad_parallel_updates_pkg.ID_RANGE,
l_table_owner,
l_table_name,
l_script_name,
l_id_column,
p_worker_id,
p_num_workers,
p_batch_size, 0,
l_sql_stmt);
ad_parallel_updates_pkg.get_id_range(
l_start_id,
l_end_id,
l_any_rows_to_process,
p_batch_size,
TRUE);
ad_parallel_updates_pkg.processed_id_range
(l_rows_processed,
l_end_id);
ad_parallel_updates_pkg.get_id_range
(l_start_id,
l_end_id,
l_any_rows_to_process,
p_batch_size,
FALSE);
SELECT count(*)
INTO l_rows_to_process
FROM AP_Trial_Balance_Ledgers
WHERE ((Status IS NULL)
OR (Status = 'S' and Worker_No = p_worker_id));
SELECT count(*)
INTO l_restarted_ledgers
FROM AP_Trial_Balance_Ledgers
WHERE Status = 'S'
AND Worker_No = p_worker_id;
AP_Debug_Pkg.Print('Y', 'Update the ledger for processing');
UPDATE AP_Trial_Balance_Ledgers
SET Status = 'S',
Worker_No = p_worker_id
WHERE Status IS NULL
AND Rownum < 2;
SELECT Ledger_ID
INTO l_ledger_id
FROM AP_Trial_Balance_Ledgers
WHERE Status = 'S'
AND Worker_No = p_worker_id;
UPDATE AP_Trial_Balance_Ledgers
SET Status = 'P'
WHERE Status = 'S'
AND Worker_No = p_worker_id;
SELECT count(*)
INTO l_rows_to_process
FROM AP_Trial_Balance_Ledgers
WHERE Status IS NULL;
l_gps_update_error EXCEPTION;
AP_Debug_Pkg.Print('Y', 'Inserting into ap_trial_balance_ledgers');
INSERT INTO ap_trial_balance_ledgers
(Ledger_ID,
Worker_No,
Status,
Creation_Date,
Created_By,
Last_Update_Date,
Last_Updated_By)
SELECT Distinct Set_Of_Books_ID,
NULL,
NULL,
Sysdate,
5,
Sysdate,
5
FROM AP_Liability_Balance
WHERE Set_Of_Books_ID IN (SELECT Ledger_ID FROM XLA_Upgrade_Dates);
SELECT xla_upg_batches_s.nextval
INTO l_batch_id
FROM DUAL;
SELECT sub_module
INTO l_inv_script_name
FROM ap_invoices_upg_control
WHERE module_name = 'SLA_ONDEMAND_INV_UPGRADE'
AND end_date IS NULL;
INSERT INTO ap_invoices_upg_control
(Module_Name,
Sub_Module,
Upgrade_Phase,
Start_Date,
End_Date,
Creation_Date,
Created_By,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Request_ID)
VALUES ('SLA_ONDEMAND_INV_UPGRADE',
l_inv_script_name,
'AP_SLA_ONDEMAND',
sysdate,
NULL,
sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
sysdate,
FND_GLOBAL.Login_ID,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id);
SELECT sub_module
INTO l_pay_script_name
FROM ap_invoices_upg_control
WHERE module_name = 'SLA_ONDEMAND_PAY_UPGRADE'
AND end_date IS NULL;
INSERT INTO ap_invoices_upg_control
(Module_Name,
Sub_Module,
Upgrade_Phase,
Start_Date,
End_Date,
Creation_Date,
Created_By,
Last_Updated_By,
Last_Update_Date,
Last_Update_Login,
Program_Application_ID,
Program_ID,
Request_ID)
VALUES ('SLA_ONDEMAND_PAY_UPGRADE',
l_pay_script_name,
'AP_SLA_ONDEMAND',
sysdate,
NULL,
sysdate,
FND_GLOBAL.User_ID,
FND_GLOBAL.User_ID,
sysdate,
FND_GLOBAL.Login_ID,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
FND_GLOBAL.conc_request_id);
RAISE l_gps_update_error;
UPDATE AP_Invoices_Upg_Control
SET End_Date = Sysdate
WHERE Module_Name IN ('SLA_ONDEMAND_INV_UPGRADE', 'SLA_ONDEMAND_PAY_UPGRADE')
AND Upgrade_Phase = 'AP_SLA_ONDEMAND'
AND End_Date IS NULL;
WHEN l_gps_update_error THEN
g_retcode := -1;