[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
FROM RA_CUSTOMER_TRX_ALL Rct,
RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh
WHERE Rct.Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
AND Rctlgd.ACCOUNT_CLASS = 'REC'
AND Rctlgd.LATEST_REC_FLAG = 'Y'
--SLA condition
AND Ent.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'TRANSACTIONS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted transaction
--Period condition
AND Rctlgd.Gl_Date <= pd_period_end_date; --parameter:pd_period_end_date
SELECT SUM(NVL(Accounted_Dr,0) + NVL(-1 * Accounted_Cr,0)) Functional_Amount,
SUM(NVL(Entered_Dr,0) + NVL(-1 * Entered_Cr, 0)) Entered_Amount
FROM (
--Receipt
/*SELECT Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Ael.Accounted_Dr Accounted_Dr,
Ael.Accounted_Cr Accounted_Cr,
Ael.Entered_Dr Entered_Dr,
Ael.Entered_Cr Entered_Cr
FROM AR_CASH_RECEIPTS_ALL Acr,
AR_RECEIVABLE_APPLICATIONS_ALL App,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir
WHERE Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
AND App.Applied_Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
--SLA condition
AND Ent.Application_Id = 222
AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'RECEIPTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted Receipt
--Period condition
AND App.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
AND Aeh.Accounting_Date = App.Gl_Date*/
SELECT Jeh.Je_Header_Id Je_Header_Id,
Ael.Ae_Header_Id Ae_Header_Id,
Ael.Accounted_Dr Accounted_Dr,
Ael.Accounted_Cr Accounted_Cr,
Ael.Entered_Dr Entered_Dr,
Ael.Entered_Cr Entered_Cr
FROM Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh
WHERE Ael.Accounting_Class_Code = 'RECEIVABLE'
AND Ael.Application_Id = 222
AND (Ael.Ae_Header_Id,Ael.Ae_Line_Num) IN
(SELECT Xdl.Ae_Header_Id,
Xdl.Ae_Line_Num
FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
Xla_Ae_Headers Aeh,
Xla_Distribution_Links Xdl,
Ar_Distributions_All Ada
WHERE App.event_id = Aeh.event_id
AND Aeh.ae_header_id = Xdl.ae_header_id
AND Xdl.event_id = Aeh.event_id
--AND Xdl.source_distribution_id_num_1 = Ada.line_id
AND Xdl.Applied_To_Source_Id_Num_1 = App.applied_customer_trx_id
AND Ada.source_id = App.receivable_application_id
AND Ada.source_table = 'RA'
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND App.applied_customer_trx_id = pn_customer_trx_id
AND App.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
-- Choli updated, fix bug 12635156
AND (SELECT status
FROM AR_CASH_RECEIPT_HISTORY_ALL
WHERE cash_receipt_id = App.cash_receipt_id
AND cash_receipt_history_id =
(SELECT max(cash_receipt_history_id)
FROM AR_CASH_RECEIPT_HISTORY_ALL
WHERE cash_receipt_id = App.cash_receipt_id
AND gl_date < pd_period_end_date)) = 'CLEARED'
------------------------------------
)
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted Receipt
---CreditMemo
UNION ALL
SELECT Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Ael.Accounted_Dr Accounted_Dr,
Ael.Accounted_Cr Accounted_Cr,
Ael.Entered_Dr Entered_Dr,
Ael.Entered_Cr Entered_Cr
FROM RA_CUSTOMER_TRX_ALL Rct,
RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh
WHERE Rct.Previous_Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
AND Rctlgd.ACCOUNT_CLASS = 'REC'
AND Rctlgd.LATEST_REC_FLAG = 'Y'
--SLA condition
AND Ent.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'TRANSACTIONS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted CreditMemo
--Period condition
AND Rctlgd.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
AND Aeh.Accounting_Date = Rctlgd.Gl_Date
--Adjustment
UNION ALL
SELECT Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Ael.Accounted_Dr Accounted_Dr,
Ael.Accounted_Cr Accounted_Cr,
Ael.Entered_Dr Entered_Dr,
Ael.Entered_Cr Entered_Cr
FROM AR_ADJUSTMENTS_ALL Adj,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh
WHERE Adj.Customer_Trx_Id = pn_customer_trx_id -- variable: pn_customer_trx_id
--SLA condition
AND Ent.Application_Id = 222
AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'ADJUSTMENTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted Adjustment
--Period condition
AND Adj.Gl_Date <= pd_period_end_date --parameter:pd_period_end_date
AND Aeh.Accounting_Date = Adj.Gl_Date
);
SELECT SUBSTR(TO_CHAR(COMPILED_VALUE_ATTRIBUTES),5,1) Account_Type,
DECODE(Value_Category,'Subsidiary',
DECODE(pv_attribute_column4bal_side,
'ATTRIBUTE1',ATTRIBUTE1,'ATTRIBUTE2',ATTRIBUTE2,'ATTRIBUTE3',ATTRIBUTE3,
'ATTRIBUTE4',ATTRIBUTE4,'ATTRIBUTE5',ATTRIBUTE5,'ATTRIBUTE6',ATTRIBUTE6,
'ATTRIBUTE7',ATTRIBUTE7,'ATTRIBUTE8',ATTRIBUTE8,'ATTRIBUTE9',ATTRIBUTE9,
'ATTRIBUTE10',ATTRIBUTE10,'ATTRIBUTE11',ATTRIBUTE11,'ATTRIBUTE12',ATTRIBUTE12,
'ATTRIBUTE13',ATTRIBUTE13,'ATTRIBUTE14',ATTRIBUTE14,'ATTRIBUTE15',ATTRIBUTE15,
'ATTRIBUTE16',ATTRIBUTE16,'ATTRIBUTE17',ATTRIBUTE17,'ATTRIBUTE18',ATTRIBUTE18,
'ATTRIBUTE19',ATTRIBUTE19,'ATTRIBUTE20',ATTRIBUTE20,'ATTRIBUTE21',ATTRIBUTE21,
'ATTRIBUTE22',ATTRIBUTE22,'ATTRIBUTE23',ATTRIBUTE23,'ATTRIBUTE24',ATTRIBUTE24,
'ATTRIBUTE25',ATTRIBUTE25,'ATTRIBUTE26',ATTRIBUTE26,'ATTRIBUTE27',ATTRIBUTE27,
'ATTRIBUTE28',ATTRIBUTE28,'ATTRIBUTE29',ATTRIBUTE29,'ATTRIBUTE30',ATTRIBUTE30,
'ATTRIBUTE31',ATTRIBUTE31,'ATTRIBUTE32',ATTRIBUTE32,'ATTRIBUTE33',ATTRIBUTE33,
'ATTRIBUTE34',ATTRIBUTE34,'ATTRIBUTE35',ATTRIBUTE35,'ATTRIBUTE36',ATTRIBUTE36,
'ATTRIBUTE37',ATTRIBUTE37,'ATTRIBUTE38',ATTRIBUTE38,'ATTRIBUTE39',ATTRIBUTE39,
'ATTRIBUTE40',ATTRIBUTE40,'ATTRIBUTE41',ATTRIBUTE41,'ATTRIBUTE42',ATTRIBUTE42,
'ATTRIBUTE43',ATTRIBUTE43,'ATTRIBUTE44',ATTRIBUTE44,'ATTRIBUTE45',ATTRIBUTE45,
'ATTRIBUTE46',ATTRIBUTE46,'ATTRIBUTE47',ATTRIBUTE47,'ATTRIBUTE48',ATTRIBUTE48,
'ATTRIBUTE49',ATTRIBUTE49,'ATTRIBUTE50',ATTRIBUTE50, NULL),NULL) Balance_Side
FROM FND_FLEX_VALUES FFV
WHERE flex_value_set_id = pn_flex_value_set_id --flex_value_set_id for CN_GL_ACCOUNT
AND flex_value = pv_account_number; --Account Segment
SELECT MEANING
INTO lv_balance_side_meaning
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
AND LOOKUP_CODE = lv_balance_side_code
AND LANGUAGE = USERENV('LANG');
SELECT GT_INVOICE_NUMBER
FROM AR_GTA_TRX_HEADERS_ALL
WHERE RA_TRX_ID = pn_customer_trx_id
AND RA_GL_PERIOD = pv_period_name
AND SOURCE = 'GT';
SELECT attribute_column
,context_code
FROM ja_cn_dff_assignments
WHERE chart_of_accounts_id = pn_coa_id --parameter: pn_coa_id
AND dff_title_code = 'ARTR';
lv_cursor_sql := 'SELECT '||lv_attribute_column4bill_num||
' FROM AR_CASH_RECEIPTS_ALL'||
' WHERE Cash_Receipt_Id ='||pn_receipt_id||
' AND Attribute_Category='''||lv_context_code||'''';
SELECT SUM(func_end_balance) Functional_Balance,
SUM(orig_end_balance) Entered_Balance
FROM ja_cn_account_balances_v
WHERE third_party_id = pn_party_id
AND ledger_id = pn_ledger_id
AND legal_entity_id = pn_legal_entity_id
AND account_segment = pv_account_number
AND currency_code = pv_currency_code
AND period_name = pv_period_name;
SELECT DISTINCT Account_Number FROM(
SELECT Jab.Account_Segment Account_Number
FROM Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Ja_Cn_Code_Combination_v Jcc,
Ja_Cn_Account_Balances Jab
WHERE Jab.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Jab.Legal_Entity_Id = pn_legal_entity_id --parameter: pn_legal_entity_id
AND Jab.Period_Name = pv_first_period --parameter: pv_first_period
AND Jab.Account_Segment = Jcc.Account_Segment
AND Jab.Ledger_Id = Jcc.Ledger_id
AND Jab.Third_Party_TYPE = 'C' --only collecting customer
--SLA condition
AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
AND Aeh.Ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Jcc.Ledger_id = Aeh.Ledger_id
AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
--BSV condition, to handle one invoice having BSVs from different legal entity in distribution lines
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
)
ORDER BY Account_Number;
SELECT cust.Account_Number Customer_Number
FROM HZ_CUST_ACCOUNTS cust
WHERE cust.cust_account_id = pn_customer_id; --parameter: pn_customer_id
SELECT Fcv.NAME Entered_Currency,
bal.Entered_Amount,
bal.Functional_Amount
FROM(
SELECT currency_code
,SUM(NVL(Orig_Begin_Balance_Dr,0) - NVL(Orig_Begin_Balance_Cr,0)) Entered_Amount
,SUM(NVL(Func_Begin_Balance_Dr,0) - NVL(Func_Begin_Balance_Cr,0)) Functional_Amount
FROM ja_cn_account_balances
WHERE ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
AND third_party_id = ln_customer_id --variable: ln_customer_id
AND account_segment = lv_account_number --variable: lv_account_number
AND period_name = pv_first_period --parameter: pv_first_period
GROUP BY third_party_id, currency_code) bal,
Fnd_Currencies_Vl Fcv
WHERE bal.Functional_Amount <> 0
AND Fcv.currency_code = bal.currency_code
ORDER BY bal.currency_code;
SELECT DISTINCT Third_Party_Id Customer_Id
FROM ja_cn_account_balances
WHERE ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
AND period_name = pv_first_period --parameter: pv_first_period
AND third_party_type = 'C'
ORDER BY Third_Party_Id;
SELECT DECODE(COUNT(*)
,0
,'Y'
,'N') no_trans_flag
FROM GL_JE_HEADERS jeh,
GL_JE_LINES jel,
GL_IMPORT_REFERENCES gir,
XLA_AE_LINES ael,
XLA_AE_HEADERS aeh,
JA_CN_CODE_COMBINATION_V jcc,
HZ_CUST_ACCOUNTS cust,
HZ_PARTIES part,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE jeh.je_header_id = jel.je_header_id
AND jeh.je_header_id = gir.je_header_id
AND jel.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND ael.ae_header_id = aeh.ae_header_id
AND jcc.code_combination_id = jel.code_combination_id
AND jcc.ledger_id = jeh.ledger_id
AND cust.cust_account_id(+) = ael.party_id
AND cust.party_id = part.party_id(+)
AND jeh.status = 'P' -- AR posted to GL
AND ael.accounting_class_code = 'RECEIVABLE'--accountingclass is 'Receivable' in the accounting line in SLA
--BSV Condition
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
--Period condition
AND Gl.Ledger_Id = Jcc.ledger_id
AND Gl.period_set_name = Gp.period_set_name
AND Jeh.Period_Name = Gp.Period_Name
AND Gl.accounted_period_type = Gp.period_type
AND Gp.period_year = to_number(pv_accounting_year) --parameter: pv_accounting_year
AND Gp.start_date BETWEEN
(SELECT Gp1.Start_Date
FROM Gl_Periods Gp1
WHERE Gl.period_set_name = Gp1.period_set_name
AND Gl.accounted_period_type = Gp1.period_type
AND Gp1.period_name = pv_first_period) --parameter: pv_first_period
AND (SELECT Gp2.Start_Date
FROM Gl_Periods Gp2
WHERE Gl.period_set_name = Gp2.period_set_name
AND Gl.accounted_period_type = Gp2.period_type
AND Gp2.period_name = pv_first_period) --parameter: pv_first_period
AND jeh.ledger_id = pn_ledger_id; --parameter: pn_ledger_id
SELECT Fcv.NAME Functional_Currency
INTO lv_fun_currency
FROM Gl_Ledgers Gl, Fnd_Currencies_Vl Fcv
WHERE Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Fcv.CURRENCY_CODE = Gl.Currency_Code;
SELECT DISTINCT
jeh.je_header_id Je_Header_Id,
aeh.ae_header_id Ae_Header_Id,
gps.period_name Period_Name,
ael.party_id Party_ID,
part.party_number Customer_Number,
jcc.Account_Segment Account_Number,
pv_accounting_year Accounting_Year,
jeh.default_effective_date
FROM GL_JE_HEADERS jeh,
GL_JE_LINES jel,
GL_IMPORT_REFERENCES gir,
XLA_AE_LINES ael,
XLA_AE_HEADERS aeh,
JA_CN_CODE_COMBINATION_V jcc,
HZ_CUST_ACCOUNTS cust,
HZ_PARTIES part,
GL_PERIOD_STATUSES gps
WHERE jeh.je_header_id = jel.je_header_id
AND jeh.je_header_id = gir.je_header_id
AND jel.je_line_num = gir.je_line_num
AND gir.gl_sl_link_id = ael.gl_sl_link_id
AND gir.gl_sl_link_table = ael.gl_sl_link_table
AND ael.ae_header_id = aeh.ae_header_id
AND jcc.code_combination_id = jel.code_combination_id
AND jcc.ledger_id = jeh.ledger_id
AND cust.cust_account_id(+) = ael.party_id
AND cust.party_id = part.party_id(+)
AND jeh.period_name = gps.period_name
AND jeh.ledger_id = gps.ledger_id
AND jeh.status = 'P' -- AR posted to GL
AND ael.accounting_class_code = 'RECEIVABLE'--accountingclass is 'Receivable' in the accounting line in SLA
--BSV Condition
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Jcc.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND llbg.Legal_Entity_Id = pn_legal_entity_id) --parameter: pn_legal_entity_id
--ld_start_date and ld_end_date come from pv_accounting_year, pv_period_from, pv_period_to
AND gps.application_id = 101
AND ((gps.start_date BETWEEN ld_start_date AND ld_end_date) AND
(gps.end_date BETWEEN ld_start_date AND ld_end_date))
AND jeh.ledger_id = pn_ledger_id --parameter: pn_ledger_id
ORDER BY part.party_number, jcc.Account_Segment,jeh.default_effective_date,jeh.Je_Header_Id;
SELECT DISTINCT Rct.Customer_Trx_Id Customer_Trx_Id
FROM RA_CUSTOMER_TRX_ALL Rct,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh
WHERE Ent.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'TRANSACTIONS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Rct.Previous_Customer_Trx_Id IS NULL --Not On-account Credit Memo should not be export seperately
UNION
SELECT DISTINCT Rcta.Customer_Trx_Id Customer_Trx_Id
FROM RA_CUSTOMER_TRX_ALL Rcta,
RA_CUST_TRX_LINE_GL_DIST_ALL Gd
WHERE Rcta.Customer_Trx_Id IN (
SELECT DISTINCT App.Applied_Customer_Trx_Id Customer_Trx_Id
FROM AR_CASH_RECEIPTS_ALL Acr,
AR_RECEIVABLE_APPLICATIONS_ALL App,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh
WHERE Ent.Application_Id = 222
AND Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'RECEIPTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND App.Applied_Customer_Trx_Id IS NOT NULL
UNION
SELECT DISTINCT Adj.Customer_Trx_Id Customer_Trx_Id
FROM AR_ADJUSTMENTS_ALL Adj,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh
WHERE Ent.Application_Id = 222
AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'ADJUSTMENTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
UNION
SELECT DISTINCT Rct.Previous_Customer_Trx_Id Customer_Trx_Id
FROM RA_CUSTOMER_TRX_ALL Rct,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh
WHERE Ent.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'TRANSACTIONS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Rct.Previous_Customer_Trx_Id IS NOT NULL --Applied Credit Memo
)
--Period condition
AND Rcta.CUSTOMER_TRX_ID = Gd.CUSTOMER_TRX_ID
AND Gd.ACCOUNT_CLASS = 'REC'
AND Gd.LATEST_REC_FLAG = 'Y'
AND Gd.gl_date NOT BETWEEN
(SELECT Gp1.Start_Date
FROM Gl_Periods Gp1
,Gl_Ledgers Gl
WHERE Gl.period_set_name = Gp1.period_set_name
AND Gl.accounted_period_type = Gp1.period_type
AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp1.period_name = pv_period_from) --parameter: pv_period_from
AND (SELECT Gp2.End_Date
FROM Gl_Periods Gp2
,Gl_Ledgers Gl
WHERE Gl.period_set_name = Gp2.period_set_name
AND Gl.accounted_period_type = Gp2.period_type
AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp2.period_name = pv_period_to); --parameter: pv_period_to
SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
FROM RA_CUSTOMER_TRX_ALL Rct,
RA_CUST_TRX_LINE_GL_DIST_ALL Gd,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Rct.Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Rct.CUSTOMER_TRX_ID = Gd.CUSTOMER_TRX_ID
AND Gd.ACCOUNT_CLASS = 'REC'
AND Gd.LATEST_REC_FLAG = 'Y'
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Gd.gl_date BETWEEN Gp.start_date AND Gp.End_date
AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
(gp.end_date BETWEEN ld_start_date AND ld_end_date))
UNION
--for Receipts from AR_RECEIVABLE_APPLICATIONS_ALL and the status must be Applied
SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE App.Applied_Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND App.gl_date BETWEEN Gp.start_date AND Gp.End_date
AND app.STATUS = 'APP' --Receipts must be applied
AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
(gp.end_date BETWEEN ld_start_date AND ld_end_date))
UNION
--from Credit Memo
SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
FROM RA_CUSTOMER_TRX_ALL Rct,
RA_CUST_TRX_LINE_GL_DIST_ALL Gd,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Rct.Previous_Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Rct.CUSTOMER_TRX_ID = Gd.CUSTOMER_TRX_ID
AND Gd.ACCOUNT_CLASS = 'REC'
AND Gd.LATEST_REC_FLAG = 'Y'
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Gd.gl_date BETWEEN Gp.start_date AND Gp.End_date
AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
(gp.end_date BETWEEN ld_start_date AND ld_end_date))
UNION
--from adjustment
SELECT DISTINCT Gp.Period_Name,Gp.Start_Date,Gp.End_Date
FROM AR_ADJUSTMENTS_ALL Adj,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Adj.Customer_Trx_Id = ln_customer_trx_id --variable: ln_customer_trx_id
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Adj.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
AND ((gp.start_date BETWEEN ld_start_date AND ld_end_date) AND
(gp.end_date BETWEEN ld_start_date AND ld_end_date));
SELECT DISTINCT
Rct.customer_trx_id Customer_Trx_Id,
Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
Gp.Period_Num Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
NVL(round(Rct.Exchange_Rate,4),1) Exchange_Rate,
Fcv.currency_code Currency_Code,
Fcv.NAME Entered_Currency,
Rct.comments Description,
TO_CHAR(Rct.term_due_date,'YYYYMMDD') Due_Date,
TO_CHAR(Rctlgd.Gl_Date,'YYYYMMDD') Applied_Date,
'RECEIVABLE' Document_Type_Number,
TO_CHAR(Rct.cust_trx_type_id) Transaction_Type_Number,
Rct.trx_number Transaction_Number,
DECODE(lv_contract_number_column,
'1',Rct.interface_header_attribute1,'2',Rct.interface_header_attribute2,
'3',Rct.interface_header_attribute3,'4',Rct.interface_header_attribute4,
'5',Rct.interface_header_attribute5,'6',Rct.interface_header_attribute6,
'7',Rct.interface_header_attribute7,'8',Rct.interface_header_attribute8,
'9',Rct.interface_header_attribute9,'10',Rct.interface_header_attribute10,
'11',Rct.interface_header_attribute11,'12',Rct.interface_header_attribute12,
'13',Rct.interface_header_attribute13,'14',Rct.interface_header_attribute14,
'15',Rct.interface_header_attribute15, NULL) Contract_Number,
DECODE(Rct.interface_header_context,
'PROJECTS INVOICES',
Rct.interface_header_attribute1, NULL) Project_Number,
TO_CHAR(Rct.RECEIPT_METHOD_ID) Settlement_Method_Number,
NULL Payment_Date,
NULL/*DECODE(Rct.attribute_category,'China Accounting', --Updated for for fixing bug# 9793920
DECODE(lv_attribute_column4bill_num,
'ATTRIBUTE1',Rct.ATTRIBUTE1,'ATTRIBUTE2',Rct.ATTRIBUTE2,
'ATTRIBUTE3',Rct.ATTRIBUTE3,'ATTRIBUTE4',Rct.ATTRIBUTE4,
'ATTRIBUTE5',Rct.ATTRIBUTE5,'ATTRIBUTE6',Rct.ATTRIBUTE6,
'ATTRIBUTE7',Rct.ATTRIBUTE7,'ATTRIBUTE8',Rct.ATTRIBUTE8,
'ATTRIBUTE9',Rct.ATTRIBUTE9,'ATTRIBUTE10',Rct.ATTRIBUTE10,
'ATTRIBUTE11',Rct.ATTRIBUTE11,'ATTRIBUTE12',Rct.ATTRIBUTE12,
'ATTRIBUTE13',Rct.ATTRIBUTE13,'ATTRIBUTE14',Rct.ATTRIBUTE14,
'ATTRIBUTE15',Rct.ATTRIBUTE15,NULL), NULL)*/ Remittance_Bill_Number
FROM RA_CUSTOMER_TRX_ALL Rct,
RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh,
Gl_Ledgers Gl,
Gl_Periods Gp,
fnd_currencies_vl Fcv
WHERE Rct.customer_trx_id = ln_customer_trx_id --variable: ln_customer_trx_id
AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
AND Rctlgd.ACCOUNT_CLASS = 'REC'
AND Rctlgd.LATEST_REC_FLAG = 'Y'
--SLA condition
AND Ent.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'TRANSACTIONS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted transaction
--Period condition
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Rctlgd.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
--Currency condition
AND Rct.invoice_currency_code = Fcv.currency_code;
SELECT DISTINCT
Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
Gp.Period_Num Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
NVL(round(Acr.Exchange_Rate,4),1) Exchange_Rate,
Acr.Cash_Receipt_Id Cash_Receipt_Id,
Fcv.currency_code Currency_Code,
Fcv.NAME Entered_Currency,
Acr.Comments Description,
NULL Due_Date,
TO_CHAR(App.Gl_Date,'YYYYMMDD') Applied_Date,
'RECEIPT' Document_Type_Number,
Acr.Type Transaction_Type_Number,
Acr.Receipt_Number Transaction_Number,
Acr.Receipt_Method_Id Settlement_Method_Number,
TO_CHAR(Acr.Receipt_Date,'YYYYMMDD') Payment_Date
FROM AR_CASH_RECEIPTS_ALL Acr,
AR_RECEIVABLE_APPLICATIONS_ALL App,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir,
Gl_Ledgers Gl,
Gl_Periods Gp,
fnd_currencies_vl Fcv
WHERE Acr.Cash_Receipt_Id = App.Cash_Receipt_Id
AND App.Applied_Customer_Trx_Id = ln_customer_trx_id -- variable: ln_customer_trx_id
--SLA condition
AND Ent.Application_Id = 222
AND Acr.Cash_Receipt_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'RECEIPTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted payment
--Period condition
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND App.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
AND Gp.Period_Name = lv_period_name --variable: lv_period_name
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh.Accounting_Date = App.Gl_Date
--Currency condition
AND Acr.currency_code = Fcv.currency_code
--EXISTS means that the customer_trx_id is derived from receipt,so
--only this receipt need be exported.
--NOT EXISTS means that the customer_trx_id is from Transaction,so
--all the receipt applied to it need be exported.
AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1
WHERE Ent1.Entity_Code = 'RECEIPTS'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
)
OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1
WHERE Ent1.Entity_Code = 'RECEIPTS'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
)
--NOT EXISTS means that the customer_trx_id not from CreditMemo
AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
FROM Xla_Transaction_Entities Ent2,
Xla_Ae_Headers Aeh2,
RA_CUSTOMER_TRX_ALL Rct2
WHERE Ent2.Application_Id = 222
AND Rct2.Customer_Trx_Id = Ent2.Source_Id_Int_1
AND Ent2.Entity_Code = 'TRANSACTIONS'
AND Ent2.Entity_Id = Aeh2.Entity_Id
AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh2.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Rct2.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
--NOT EXISTS means that the customer_trx_id not from Adjustment
AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
FROM Xla_Transaction_Entities Ent3,
Xla_Ae_Headers Aeh3
WHERE Ent3.Entity_Code = 'ADJUSTMENTS'
AND Ent3.Entity_Id = Aeh3.Entity_Id
AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
ORDER BY Aeh.Ae_Header_Id,Jeh.Je_Header_Id DESC;
SELECT DISTINCT
Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Ael.Ae_Line_Num Ae_Line_Num,
Rct.Customer_Trx_Id Customer_Trx_Id,
TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
Gp.Period_Num Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
NVL(round(Rct.Exchange_Rate,4),1) Exchange_Rate,
lv_period_name Period_Name,
fcv.currency_code Currency_Code,
fcv.NAME Entered_Currency,
Rct.Comments Description,
TO_CHAR(Rct.term_due_date,'YYYYMMDD') Due_Date,
TO_CHAR(Rctlgd.Gl_Date,'YYYYMMDD') Applied_Date,
'RECEIVABLE' Document_Type_Number,
TO_CHAR(Rct.cust_trx_type_id) Transaction_Type_Number,
Rct.Trx_Number Transaction_Number,
DECODE(lv_contract_number_column,
'1',Rct.interface_header_attribute1,'2',Rct.interface_header_attribute2,
'3',Rct.interface_header_attribute3,'4',Rct.interface_header_attribute4,
'5',Rct.interface_header_attribute5,'6',Rct.interface_header_attribute6,
'7',Rct.interface_header_attribute7,'8',Rct.interface_header_attribute8,
'9',Rct.interface_header_attribute9,'10',Rct.interface_header_attribute10,
'11',Rct.interface_header_attribute11,'12',Rct.interface_header_attribute12,
'13',Rct.interface_header_attribute13,'14',Rct.interface_header_attribute14,
'15',Rct.interface_header_attribute15, NULL) Contract_Number,
DECODE(Rct.interface_header_context,
'PROJECTS INVOICES',
Rct.interface_header_attribute1,
NULL) Project_Number,
TO_CHAR(Rct.RECEIPT_METHOD_ID) Settlement_Method_Number,
NULL Payment_Date,
NULL/*DECODE(Rct.attribute_category,'China Accounting', --Updated for for fixing bug# 9793920
DECODE(lv_attribute_column4bill_num,
'ATTRIBUTE1',Rct.ATTRIBUTE1,'ATTRIBUTE2',Rct.ATTRIBUTE2,
'ATTRIBUTE3',Rct.ATTRIBUTE3,'ATTRIBUTE4',Rct.ATTRIBUTE4,
'ATTRIBUTE5',Rct.ATTRIBUTE5,'ATTRIBUTE6',Rct.ATTRIBUTE6,
'ATTRIBUTE7',Rct.ATTRIBUTE7,'ATTRIBUTE8',Rct.ATTRIBUTE8,
'ATTRIBUTE9',Rct.ATTRIBUTE9,'ATTRIBUTE10',Rct.ATTRIBUTE10,
'ATTRIBUTE11',Rct.ATTRIBUTE11,'ATTRIBUTE12',Rct.ATTRIBUTE12,
'ATTRIBUTE13',Rct.ATTRIBUTE13,'ATTRIBUTE14',Rct.ATTRIBUTE14,
'ATTRIBUTE15',Rct.ATTRIBUTE15,NULL), NULL)*/ Remittance_Bill_Number
FROM RA_CUSTOMER_TRX_ALL Rct,
RA_CUST_TRX_LINE_GL_DIST_ALL Rctlgd,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh,
Gl_Ledgers Gl,
Gl_Periods Gp,
fnd_currencies_vl fcv
WHERE Rct.Previous_Customer_Trx_Id = ln_customer_trx_id -- variable: ln_customer_trx_id
AND Rct.CUSTOMER_TRX_ID = Rctlgd.CUSTOMER_TRX_ID
AND Rctlgd.ACCOUNT_CLASS = 'REC'
AND Rctlgd.LATEST_REC_FLAG = 'Y'
--SLA condition
AND Ent.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'TRANSACTIONS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted creditmemo
--Period condition
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Rctlgd.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
AND Gp.Period_Name = lv_period_name --variable: lv_period_name
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh.Accounting_Date = Rctlgd.Gl_Date
--currecny condition
AND Rct.invoice_currency_code = fcv.currency_code
--EXISTS means that the customer_trx_id is derived from creditmemo,so
--only this creditmemo need be exported.
--NOT EXISTS means that the customer_trx_id is from Transaction,so
--all the creditmemo applied to it need be exported.
AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1,
RA_CUSTOMER_TRX_ALL Rct
WHERE Ent1.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent1.Source_Id_Int_1
AND Ent1.Entity_Code = 'TRANSACTIONS'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh1.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Rct.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
)
OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1,
RA_CUSTOMER_TRX_ALL Rct
WHERE Ent1.Application_Id = 222
AND Rct.Customer_Trx_Id = Ent1.Source_Id_Int_1
AND Ent1.Entity_Code = 'TRANSACTIONS'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh1.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Rct.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
)
--NOT EXISTS means that the customer_trx_id not from Receipt
AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
FROM Xla_Transaction_Entities Ent2,
Xla_Ae_Headers Aeh2
WHERE Ent2.Entity_Code = 'RECEIPTS'
AND Ent2.Entity_Id = Aeh2.Entity_Id
AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh2.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
--NOT EXISTS means that the customer_trx_id not from Adjustment
AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
FROM Xla_Transaction_Entities Ent3,
Xla_Ae_Headers Aeh3
WHERE Ent3.Entity_Code = 'ADJUSTMENTS'
AND Ent3.Entity_Id = Aeh3.Entity_Id
AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
ORDER BY Jeh.Je_Header_Id,Aeh.Ae_Header_Id,Ael.Ae_Line_Num;
SELECT DISTINCT
Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
TO_CHAR(Jeh.Date_Created,'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date,'YYYYMMDD') Journal_Date,
Gp.Period_Num Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
Adj.Adjustment_Id Adjustment_Id,
Adj.Comments Description,
TO_CHAR(Adj.Gl_Date,'YYYYMMDD') Applied_Date
FROM AR_ADJUSTMENTS_ALL Adj,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Import_References Gir,
Gl_Je_Headers Jeh,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Adj.Customer_Trx_Id = ln_customer_trx_id -- variable: ln_customer_trx_id
--SLA condition
AND Ent.Application_Id = 222
AND Adj.Adjustment_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'ADJUSTMENTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
--GL condition
AND Gir.Gl_Sl_Link_Id = Ael.Gl_Sl_Link_Id
AND Gir.Gl_Sl_Link_Table = Ael.Gl_Sl_Link_Table
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Jeh.Status = 'P' --only collecting posted adjustment
--Period condition
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Adj.Gl_Date BETWEEN Gp.start_date AND Gp.End_date
AND Gp.Period_Name = lv_period_name --variable: lv_period_name
AND Gl.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh.Accounting_Date = Adj.Gl_Date
--EXISTS means that the customer_trx_id is derived from adjust,so
--only this adjust need be exported.
--NOT EXISTS means that the customer_trx_id is from Transaction,so
--all Adjustments based on it need be exported.
AND ((EXISTS(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1
WHERE Ent1.Entity_Code = 'ADJUSTMENTS'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
)
OR NOT EXISTS(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1
WHERE Ent1.Entity_Code = 'ADJUSTMENTS'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh1.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
)
--NOT EXISTS means that the customer_trx_id not from CreditMemo
AND NOT EXISTS(SELECT Aeh2.Ae_Header_Id
FROM Xla_Transaction_Entities Ent2,
Xla_Ae_Headers Aeh2,
RA_CUSTOMER_TRX_ALL Rct2
WHERE Ent2.Application_Id = 222
AND Rct2.Customer_Trx_Id = Ent2.Source_Id_Int_1
AND Ent2.Entity_Code = 'TRANSACTIONS'
AND Ent2.Entity_Id = Aeh2.Entity_Id
AND Aeh2.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh2.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Rct2.Previous_Customer_Trx_Id IS NOT NULL) --variable: ln_ae_header_id
--NOT EXISTS means that the customer_trx_id not from Receipt
AND NOT EXISTS(SELECT Aeh3.Ae_Header_Id
FROM Xla_Transaction_Entities Ent3,
Xla_Ae_Headers Aeh3
WHERE Ent3.Entity_Code = 'RECEIPTS'
AND Ent3.Entity_Id = Aeh3.Entity_Id
AND Aeh3.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh3.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
ORDER BY Jeh.Je_Header_Id,Aeh.Ae_Header_Id;
SELECT DISTINCT voucher_number journal_number
FROM ja_cn_voucher_number
WHERE je_header_id = ln_je_header_id
AND legal_entity_id = pn_legal_entity_id;
/*SELECT DISTINCT journal_number
FROM ja_cn_journal_lines
WHERE je_header_id = ln_je_header_id;*/
SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
FROM Xla_Ae_Lines Ael,
Xla_Ae_Headers Aeh
WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
--BSV condition, one transaction having BSVs with different legal entity in distribution lines????
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
FROM Xla_Ae_Lines Ael
WHERE Ael.Accounting_Class_Code = 'RECEIVABLE'
AND Ael.Application_Id = 222
AND (Ael.Ae_Header_Id,Ael.Ae_Line_Num) IN
(SELECT Xdl.Ae_Header_Id,
Xdl.Ae_Line_Num
FROM AR_RECEIVABLE_APPLICATIONS_ALL App,
Xla_Ae_Headers Aeh,
Xla_Distribution_Links Xdl,
Ar_Distributions_All Ada
WHERE App.event_id = Aeh.event_id
AND Aeh.ae_header_id = Xdl.ae_header_id
AND Xdl.event_id = Aeh.event_id
--AND Xdl.source_distribution_id_num_1 = Ada.line_id
AND Xdl.Applied_To_Source_Id_Num_1 = App.applied_customer_trx_id
AND Ada.source_id = App.receivable_application_id
AND Ada.source_table = 'RA'
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND App.applied_customer_trx_id = ln_customer_trx_id
)
--BSV condition, one transaction having BSVs with different legal entity in distribution lines
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_i
SELECT SUM(NVL(Ael.Accounted_Dr,0) + NVL(-1 * Ael.Accounted_Cr,0)) Functional_Amount,
SUM(NVL(Ael.Entered_Dr,0) + NVL(-1 * Ael.Entered_Cr, 0)) Entered_Amount
FROM Xla_Ae_Lines Ael,
Xla_Ae_Headers Aeh
WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Ael.Accounting_Class_Code = 'RECEIVABLE'
AND Aeh.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Ael.Ae_Line_Num = ln_ae_line_num
--BSV condition, one transaction having BSVs with different legal entity in distribution lines????
AND EXISTS (SELECT llbg.Bal_Seg_Value
FROM ja_cn_ledger_le_bsv_gt llbg
WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(Ael.CODE_COMBINATION_ID) = llbg.Bal_Seg_Value
AND llbg.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND llbg.Legal_Entity_Id = pn_legal_entity_id); --parameter: pn_legal_entity_id
SELECT *
FROM ja_cn_dff_assignments
WHERE chart_of_accounts_id = pn_coa_id
AND (dff_title_code = 'ACBS' -- Balance Side
OR dff_title_code = 'ARTR' -- Remittance Bill Number for Transaction
);
SELECT DEFAULT_REFERENCE Contract_Number_Column
FROM RA_BATCH_SOURCES_ALL
WHERE NAME = 'ORDER ENTRY'
AND org_id = pn_legal_entity_id;
SELECT DECODE(COUNT(Gp.Period_Name),
0,
'N', --Not first period, in this case, year begnning balance would not be exported
'Y')--It is first period and year beginning balance should be exported
INTO lv_first_period_flag
FROM Gl_Ledgers Gl, Gl_Periods Gp
WHERE Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Gp.period_year = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
AND Gl.Ledger_Id = pn_ledger_id--parameter: pn_ledger_id
AND Gp.Period_Name = pv_period_from--parameter: pv_period_from
AND Gp.Start_Date =
(SELECT MIN(Gp1.START_DATE)
FROM Gl_Periods Gp1
WHERE Gl.period_set_name = Gp1.period_set_name
AND Gl.accounted_period_type = Gp1.period_type
AND Gp1.period_year = Gp.period_year);
SELECT Gp.Period_Name, Gp.Period_Num
INTO lv_first_period_name, ln_first_period_num
FROM Gl_Ledgers Gl, Gl_Periods Gp
WHERE Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Gp.period_year = TO_NUMBER(pv_accounting_year) --parameter: pv_accounting_year
AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.Start_Date =
(SELECT MIN(Gp1.START_DATE)
FROM Gl_Periods Gp1
WHERE Gl.period_set_name = Gp1.period_set_name
AND Gl.accounted_period_type = Gp1.period_type
AND Gp1.period_year = Gp.period_year);
END IF; --if it is period from is selected as first period
/*DELETE FROM JA_CN_LEDGER_LE_BSV_GT;
SELECT start_date
INTO ld_start_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND to_char(period_year) = pv_accounting_year;
SELECT end_date
INTO ld_end_date
FROM GL_PERIOD_STATUSES
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND to_char(period_year) = pv_accounting_year;
SELECT s.flex_value_set_id
INTO ln_flex_value_set_id
FROM fnd_id_flex_segments s
WHERE s.application_id = 101
AND s.id_flex_num = pn_coa_id
AND s.id_flex_code = 'GL#'
AND s.application_column_name =
(SELECT application_column_name
FROM fnd_segment_attribute_values
WHERE application_id = 101
AND segment_attribute_type = 'GL_ACCOUNT'
AND attribute_value = 'Y'
AND id_flex_num = pn_coa_id
AND id_flex_code = 'GL#');
SELECT fnd_currencies_vl.NAME
INTO lv_functional_currency
FROM fnd_currencies_vl
WHERE currency_code =
(SELECT currency_code
FROM gl_ledgers
WHERE ledger_id = pn_ledger_id);
NULL --Updated for fixing bug 10121399
,pv_document_type_number => v_transaction_row.Document_Type_Number
,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
,pv_transaction_number => v_transaction_row.Transaction_Number
,pv_invoice_number => lv_vat_invoice_number
,pv_contract_number => v_transaction_row.Contract_Number
,pv_project_number => v_transaction_row.Project_Number
,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
,pv_payment_date => v_transaction_row.Payment_Date
,pv_clear_flag => lv_clear_flag
,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
NULL --Updated for fixing bug 10121399
,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
,pv_applied_date => --v_transaction_row.Applied_Date
v_receipt_row.Journal_Date --Update for fixing bug 9962326
,pv_document_type_number => v_receipt_row.Document_Type_Number
,pv_transaction_type_number => v_receipt_row.Transaction_Type_Number
,pv_transaction_number => v_receipt_row.Transaction_Number
,pv_invoice_number => lv_vat_invoice_number
,pv_contract_number => v_transaction_row.Contract_Number
,pv_project_number => v_transaction_row.Project_Number
,pv_settlement_method_number => v_receipt_row.Settlement_Method_Number
,pv_payment_date => v_receipt_row.Payment_Date
,pv_clear_flag => lv_clear_flag
,pv_remittance_bill_number => Get_Remittance_Bill_Number(pn_coa_id --Updated for fixing bug# 9747676
,v_receipt_row.cash_receipt_id)
);
NULL --Updated for fixing bug 10121399
,pv_document_type_number => v_transaction_row.Document_Type_Number
,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
,pv_transaction_number => v_transaction_row.Transaction_Number
,pv_invoice_number => lv_vat_invoice_number
,pv_contract_number => v_transaction_row.Contract_Number
,pv_project_number => v_transaction_row.Project_Number
,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
,pv_payment_date => v_transaction_row.Payment_Date
,pv_clear_flag => lv_clear_flag
,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
NULL --Updated for fixing bug 10121399
,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
,pv_applied_date => --v_transaction_row.Applied_Date
v_credit_memo_row.Journal_Date --Update for fixing bug 9962326
,pv_document_type_number => v_credit_memo_row.Document_Type_Number
,pv_transaction_type_number => v_credit_memo_row.Transaction_Type_Number
,pv_transaction_number => v_credit_memo_row.Transaction_Number
,pv_invoice_number => lv_vat_invoice_number
,pv_contract_number => v_credit_memo_row.Contract_Number
,pv_project_number => v_credit_memo_row.Project_Number
,pv_settlement_method_number => v_credit_memo_row.Settlement_Method_Number
,pv_payment_date => v_credit_memo_row.Payment_Date
,pv_clear_flag => lv_clear_flag
,pv_remittance_bill_number => v_credit_memo_row.Remittance_Bill_Number);
NULL --Updated for fixing bug 10121399
,pv_applied_journal_number => TO_CHAR(ln_applied_journal_number)
,pv_applied_date => --v_trx_adjustment_row.Applied_Date
v_trx_adjustment_row.Journal_Date --Update for fixing bug 9962326
,pv_document_type_number => v_transaction_row.Document_Type_Number
,pv_transaction_type_number => v_transaction_row.Transaction_Type_Number
,pv_transaction_number => v_transaction_row.Transaction_Number
,pv_invoice_number => lv_vat_invoice_number
,pv_contract_number => v_transaction_row.Contract_Number
,pv_project_number => v_transaction_row.Project_Number
,pv_settlement_method_number => v_transaction_row.Settlement_Method_Number
,pv_payment_date => v_transaction_row.Payment_Date
,pv_clear_flag => lv_clear_flag
,pv_remittance_bill_number => v_transaction_row.Remittance_Bill_Number);
IF ln_row_count = 0 AND lv_beginning_bal_exported_flag = 'N' --Update for fixing bug 10041443
THEN
Ja_Cn_Utility.Print_No_Data_Found_For_Log('RECEIVABLE_DETAIL_REPORT',
Ja_Cn_Utility.GV_MODULE_APAR);