[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT part.party_number Customer_Number
FROM HZ_CUST_ACCOUNTS cust, HZ_PARTIES part, AP_INVOICES_ALL aia
WHERE cust.cust_account_id = aia.party_id
AND cust.party_id = part.party_id
AND aia.Invoice_Id = pn_invoice_id; --parameter: pn_invoice_id
SELECT TO_CHAR(Gp.Period_Num)
FROM Gl_Periods Gp, Gl_Ledgers Gl
WHERE 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 Gp.Period_Name = lv_period_name; --variable: lv_period_name
SELECT Period_Name
FROM Ap_Invoices_v
WHERE Invoice_Id = pn_invoice_id; --parameter: pn_invoice_id
SELECT DISTINCT Jcc.account_segment Prepaid_Account_Num
FROM Ap_Invoices_All Aia,
Xla_Transaction_Entities Ent,
Ja_Cn_Code_Combination_v Jcc,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael
WHERE Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aia.Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
AND Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT'
AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
AND Jcc.Ledger_id = Aeh.Ledger_id;
SELECT DISTINCT voucher_number journal_number
FROM ja_cn_voucher_number
WHERE je_header_id = pn_je_header_id
AND legal_entity_id = pn_legal_entity_id;
/*SELECT DISTINCT jcjl.journal_number
FROM ja_cn_journal_lines jcjl
WHERE jcjl.je_header_id = pn_je_header_id;*/
SELECT SUM(-1 * NVL(Ael.Accounted_Dr, 0) + NVL(Ael.Accounted_Cr, 0)) Functional_Amount,
SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(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 = 'LIABILITY'
AND ((pn_invoice_id IS NULL AND Aeh.Ae_Header_Id = pn_ae_header_id) --parameter: pn_ae_header_id
OR (pn_invoice_id IS NOT NULL AND
Aeh.Ae_Header_Id =
(DECODE((SELECT COUNT(*)
FROM Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1
WHERE Ent1.Application_Id = 200
AND Ent1.Entity_Code = 'AP_INVOICES'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aeh1.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
AND Aeh1.Ledger_Id = pn_ledger_id), --parameter: pn_ledger_id
0,
(SELECT MIN(Aeh1.Ae_Header_Id)
FROM Ap_Invoices_All Aia1,
Xla_Transaction_Entities Ent1,
Xla_Ae_Headers Aeh1
WHERE Ent1.Application_Id = 200
AND Aia1.Invoice_Id = Ent1.Source_Id_Int_1
AND Ent1.Entity_Code = 'AP_INVOICES'
AND Ent1.Entity_Id = Aeh1.Entity_Id
AND Aia1.Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
AND Aeh1.Ledger_Id = pn_ledger_id), --parameter: pn_ledger_id
pn_ae_header_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
SELECT -1 * NVL(Aip.Amount, 0) Entered_Amount,
-1 * NVL(Aip.Payment_Base_Amount, NVL(Aip.Amount, 0)) Functional_Amount
FROM Ap_Invoice_Payments_All Aip
WHERE Aip.Invoice_Payment_Id = pn_invoice_payment_id; -- parameter: pn_invoice_payment_id
SELECT SUM(NVL(Ael.Accounted_Dr, 0) + -1 * NVL(Ael.Accounted_Cr, 0)) Functional_Amount,
SUM(NVL(Ael.Entered_Dr, 0) + -1 * NVL(Ael.Entered_Cr, 0)) Entered_Amount
FROM Xla_Ae_Lines Ael,
Xla_Ae_Headers Aeh,
Ja_Cn_Code_Combination_v Jcc
WHERE Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Ael.Accounting_Class_Code <> 'LIABILITY'
AND Aeh.Ae_Header_Id = pn_ae_header_id --parameter: pn_ae_header_id
AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
AND Jcc.account_segment = pv_account_num --parameter: pv_account_num
AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_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
SELECT SUM(func_end_balance) Functional_Balance,
SUM(orig_end_balance) Entered_Balance
FROM ja_cn_account_balances_v jcab
WHERE jcab.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND jcab.legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
AND jcab.third_party_id = pn_supplier_id --parameter: pn_supplier_id
AND jcab.account_segment = pv_account_number --parameter: pv_account_number
AND jcab.currency_code = pv_currency_code --parameter: pv_currency_code
AND jcab.period_name = pv_period_name; --parameter: pv_period_name
SELECT SUM(NVL(jcab.func_period_net_dr, 0) +
-1 * NVL(jcab.func_period_net_cr, 0)) Functional_Balance,
SUM(NVL(jcab.orig_period_net_dr, 0) +
-1 * NVL(jcab.orig_period_net_cr, 0)) Entered_Balance
FROM ja_cn_account_balances jcab
WHERE jcab.ledger_id = pn_ledger_id --parameter: pn_ledger_id
AND jcab.legal_entity_id = pn_legal_entity_id --parameter: pn_legal_entity_id
AND jcab.third_party_id = pn_supplier_id --parameter: pn_supplier_id
AND jcab.account_segment = pv_account_number --parameter: pv_account_number
AND jcab.currency_code = pv_currency_code --parameter: pv_currency_code
AND jcab.period_name = pv_period_name; --parameter: pv_period_name
SELECT SUBSTR(TO_CHAR(COMPILED_VALUE_ATTRIBUTES), 5, 1) Account_Type,
DECODE(Value_Category,
'Subsidiary',
DECODE(lv_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 = ln_flex_value_set_id --variable: ln_flex_value_set_id
AND flex_value = pv_account_number; --parameter: pv_account_number
SELECT attribute_column
FROM ja_cn_dff_assignments
WHERE chart_of_accounts_id = pn_coa_id --parameter: pn_coa_id
AND dff_title_code = 'ACBS';
SELECT MEANING
INTO lv_debit
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
AND LOOKUP_CODE = 'D'
AND LANGUAGE = USERENV('LANG');
SELECT MEANING
INTO lv_credit
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'JA_CN_DEBIT_CREDIT'
AND LOOKUP_CODE = 'C'
AND LANGUAGE = USERENV('LANG');
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#');
pn_check_id IN NUMBER) --Updated for fixing bug# 9793920
RETURN VARCHAR2 IS
lv_bill_num VARCHAR(50) := NULL;
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 = 'APIN';
lv_cursor_sql := 'SELECT ' || lv_attribute_column4bill_num ||
' FROM Ap_Checks_All' || ' WHERE Check_Id =' ||
pn_check_id || ' AND Attribute_Category=''' ||
lv_context_code || ''''; --Updated for fixing bug# 9793920
SELECT TO_CHAR(apsa.Due_Date, 'YYYYMMDD') Due_Date
FROM Ap_Payment_Schedules_All apsa
WHERE apsa.Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
AND (pn_inovice_payment_id IS NULL
--To handle schedule payment
OR
(pn_inovice_payment_id IS NOT NULL AND EXISTS
(SELECT aipa.Payment_Num
FROM Ap_Invoice_Payments_All aipa
WHERE aipa.Invoice_Id = apsa.Invoice_Id
AND aipa.Payment_Num = apsa.Payment_Num
AND aipa.Invoice_Payment_id = pn_inovice_payment_id))) --parameter: pn_inovice_payment_id
ORDER BY Due_Date;
SELECT SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Inv_Amount
FROM Ap_Invoices_All Aia,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael
WHERE Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
--SLA condition
AND Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'LIABILITY'
AND (Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT' OR NOT EXISTS
(SELECT Ael1.Ae_HEADER_ID --filter accounting line for prepayment application
FROM Xla_Ae_Lines Ael1
WHERE Aeh.Ae_Header_Id = Ael1.Ae_Header_Id
AND Ael1.Accounting_Class_Code = 'PREPAID_EXPENSE'))
AND Aeh.Ae_Header_Id = Ael.Ae_Header_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
SELECT SUM(NVL(Paid_Amount, 0)) Paid_Amount
FROM (
--collecting payment amount
--Begin: Updated for fixing bug# 9747960
/*SELECT SUM(NVL(Ael.Entered_Dr,
0) + -1 * NVL(Ael.Entered_Cr,
0)) Paid_Amount
FROM Ap_Invoices_All Aia,
Ap_Checks_All Ach,
Ap_Invoice_Payments_All Aip,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir
WHERE Aia.Invoice_Id = Aip.Invoice_Id
AND Ach.Check_Id = Aip.Check_Id
AND Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
AND Aip.Accounting_Date <= (SELECT DISTINCT Gp.End_Date
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 Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = pv_period_name) --parameter: pv_period_name
--SLA condition
AND Ent.Application_Id = 200
AND Ach.Check_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_PAYMENTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'LIABILITY'
--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
--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*/
SELECT SUM(NVL(Aip.Amount, 0)) Paid_Amount
FROM Ap_Invoices_All Aia, Ap_Invoice_Payments_All Aip
WHERE Aia.Invoice_Id = Aip.Invoice_Id
AND Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
AND EXISTS
(SELECT Ach.Check_Id
FROM Ap_Checks_All Ach,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir
WHERE Ach.Check_Id = Aip.Check_Id
AND Aip.Accounting_Date <=
(SELECT DISTINCT Gp.End_Date
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 Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = pv_period_name) --parameter: pv_period_name
-- Choli updated, fix bug 12635156
AND Ach.cleared_date <=
(SELECT DISTINCT Gp.End_Date
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 Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = pv_period_name) --parameter: pv_period_name
------------------------------------------------------
--SLA condition
AND Ent.Application_Id = 200
AND Ach.Check_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_PAYMENTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'LIABILITY'
--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
--End: Updated for fixing bug# 9747960
UNION
--collecting prepaid amount
SELECT SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Paid_Amount
FROM Ap_Invoices_All Aia,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir
WHERE Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
AND Aia.Invoice_Type_Lookup_Code <> 'PREPAYMENT'
--SLA condition
AND Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
--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 prepayment application
--Period condition
AND Jeh.Default_Effective_Date <=
(SELECT DISTINCT Gp.End_Date
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 Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = pv_period_name) --parameter: pv_period_name
--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
);
SELECT SUM(NVL(Ael.Entered_Dr, 0) + -1 * NVL(Ael.Entered_Cr, 0)) Prepaid_Amount
FROM Ap_Invoices_All Aia,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Ja_Cn_Code_Combination_v Jcc
WHERE Aia.Invoice_Id = pn_invoice_id -- parameter: pn_invoice_id
AND Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT'
--SLA condition
AND Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
--AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
AND Ael.Accounting_Class_Code <> 'LIABILITY'
AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
AND Jcc.account_segment = pv_account_num --parameter: pv_account_num
AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Aeh.Ae_Header_Id = Ael.Ae_Header_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
SELECT SUM(-1 * NVL(Ael.Entered_Dr, 0) + NVL(Ael.Entered_Cr, 0)) Applied_Amount
FROM Ap_Prepay_History_All Aph,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Ja_Cn_Code_Combination_v Jcc,
Gl_Je_Headers Jeh,
Gl_Import_References Gir
WHERE Aph.Prepay_Invoice_Id = pn_invoice_id --parameter: pn_invoice_id
AND Aph.Accounting_Event_Id = Aeh.Event_Id
--SLA condition
AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Accounting_Class_Code = 'PREPAID_EXPENSE'
AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
AND Jcc.account_segment = pv_account_num --parameter: pv_account_num
AND Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
--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 prepayment application
--Period condition
AND Jeh.Default_Effective_Date <=
(SELECT DISTINCT Gp.End_Date
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 Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = pv_period_name) --parameter: pv_period_name
--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
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_Id IS NOT NULL
--SLA condition
AND Aeh.Ae_Header_Id = Ael.Ae_Header_Id
AND Ael.Accounting_Class_Code = lv_accouting_class_code --variable: lv_accouting_class_code
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 part.party_number Supplier_Number
FROM HZ_PARTIES part
WHERE part.party_id = pn_supplier_id --parameter: pn_supplier_id
UNION
SELECT Sup.Segment1 Supplier_Number
FROM ap_suppliers Sup
WHERE Sup.vendor_id = pn_supplier_id; --parameter: pn_supplier_id
SELECT Fcv.NAME Entered_Currency,
bal.Entered_Amount,
bal.Functional_Amount
FROM (SELECT currency_code,
DECODE(lv_accounting_class_code,
'LIABILITY',
SUM(NVL(Orig_Begin_Balance_Cr, 0) -
NVL(Orig_Begin_Balance_Dr, 0)),
'PREPAID_EXPENSE',
SUM(NVL(Orig_Begin_Balance_Dr, 0) -
NVL(Orig_Begin_Balance_Cr, 0))) Entered_Amount,
DECODE(lv_accounting_class_code,
'LIABILITY',
SUM(NVL(Func_Begin_Balance_Cr, 0) -
NVL(Func_Begin_Balance_Dr, 0)),
'PREPAID_EXPENSE',
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_supplier_id --variable: ln_supplier_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 Supplier_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_Id IS NOT NULL
ORDER BY Third_Party_Id;
SELECT DECODE(COUNT(*), 0, 'Y', 'N') no_trans_flag
FROM Gl_Je_Lines Jel,
Gl_Je_Headers Jeh,
Gl_Import_References Gir,
Ja_Cn_Code_Combination_v Jcc,
Gl_Ledgers Gl,
Gl_Periods Gp,
Xla_Ae_Lines Ael,
Xla_Ae_Headers Aeh,
Ap_Suppliers Sup
WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Gir.Je_Line_Num = Jel.Je_Line_Num
AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
AND Jcc.Ledger_id = Jeh.Ledger_id
AND Gl.Ledger_Id = Jcc.ledger_id
AND Jeh.Status = 'P' --only collecting posted payable
--Period condition
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
--SLA 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 Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Party_Id = Sup.Vendor_Id(+)
AND Ael.Party_Id = ln_supplier_id --variable: ln_supplier_id
AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <> 'EMPLOYEE' --not considering employee supplier
--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
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 trunc(accounting_date, 'mm') accounting_date
from AP_INVOICE_PAYMENTS_ALL aip
where aip.check_id = p_check_id;
select AP_INVOICES_PKG.GET_APPROVAL_STATUS(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code) into l_invoice_status
from ap_invoices_all ai
where invoice_id = p_invioce_id;
select EVENT_TYPE_CODE
into l_enent_type_code
from Xla_Ae_Headers
where Ae_Header_Id = p_ae_header_id;
select trunc(accounting_date, 'mm') accounting_date
from Xla_Ae_Headers aip
where aip.ae_header_id = p_ae_header_id ;
select AP_INVOICES_PKG.GET_APPROVAL_STATUS(ai.invoice_id,
ai.invoice_amount,
ai.payment_status_flag,
ai.invoice_type_lookup_code)
into l_invoice_status
from ap_invoices_all ai
where invoice_id = p_invioce_id;
SELECT DISTINCT Jeh.Je_Header_Id Je_Header_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Ael.Party_Id Supplier_Id,
Gp.Period_Name Inv_Period_Name,
Sup.Segment1 Supplier_Number,
Jcc.Account_Segment Account_Number,
TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
pv_accounting_year Accounting_Year,
Gp.Period_Num Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
Fcv.Name Functional_Currency
FROM Gl_Je_Lines Jel,
Gl_Je_Headers Jeh,
Gl_Import_References Gir,
Ja_Cn_Code_Combination_v Jcc,
Gl_Ledgers Gl,
Gl_Periods Gp,
Xla_Ae_Lines Ael,
Xla_Ae_Headers Aeh,
Ap_Suppliers Sup,
Fnd_Currencies_Vl Fcv
WHERE Jel.Je_Header_Id = Jeh.Je_Header_Id
AND Gir.Je_Header_Id = Jeh.Je_Header_Id
AND Gir.Je_Line_Num = Jel.Je_Line_Num
AND Jcc.Code_Combination_Id = Jel.Code_Combination_Id
AND Jcc.Ledger_id = Jeh.Ledger_id
AND Gl.Ledger_Id = Jcc.ledger_id
AND Jeh.Status = 'P' --only collecting posted payable
--Period condition
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_period_from) --parameter: pv_period_from
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_period_to) --parameter: pv_period_to
--SLA 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 Jcc.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Ael.Party_Id = Sup.Vendor_Id(+)
AND Ael.Accounting_Class_Code = 'LIABILITY' --only collecting 'Liability' account
AND NVL(Sup.Vendor_Type_Lookup_Code, 'CUSTOMER') <> 'EMPLOYEE' --not considering employee supplier
--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
AND Fcv.CURRENCY_CODE = Gl.Currency_Code
ORDER BY Supplier_Id, Journal_Date, Je_Header_Id;
SELECT Aia.Invoice_Id Invoice_Id
FROM Ap_Invoices_All Aia,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh
WHERE Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
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 Aia.Invoice_Id Invoice_Id
FROM Ap_Invoices_All Aia,
Ap_Checks_All Ach,
Ap_Invoice_Payments_All Aip,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh
WHERE Ent.Application_Id = 200
AND Aia.Invoice_Id = Aip.Invoice_Id
AND Ach.Check_Id = Aip.Check_Id
AND Ach.Check_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_PAYMENTS'
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
--Period condition
AND Aia.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
FROM (SELECT lv_inv_period_name Period_Name
FROM dual
UNION
SELECT DISTINCT Aip.Period_Name
FROM Ap_Invoices_All Aia, Ap_Invoice_Payments_All Aip
WHERE Aia.Invoice_Id = Aip.Invoice_Id
AND Aia.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
) Ppn --period name for payables
,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Ppn.Period_Name = Gp.Period_Name
AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id --Fix bug 10008055
AND 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 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 = lv_inv_period_name) --variable: lv_inv_period_name
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_period_to) --parameter: pv_period_to
ORDER BY Gp.Start_Date;
SELECT DISTINCT Aia.Invoice_Id Invoice_Id,
Aia.Terms_Id Terms_Id,
Jeh.Je_Header_Id Je_Header_Id,
Aia.Invoice_Currency_Code Currency_Code,
Jeh.Period_Name Period_Name,
TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
Jeh.Je_Category Journal_Category_Number,
NVL(ROUND(Aia.Exchange_Rate, 4), 1) Exchange_Rate,
'' Balance_Side,
Fcv.Name Entered_Currency,
Aia.Description Description,
'' Due_Date,
NULL Applied_Date,
'PAYABLE' Document_Type_Number,
Aia.Invoice_Type_Lookup_Code Transaction_Type_Number,
Aia.Invoice_Num Transaction_Number,
'' Invoice_Number,
Pha.Segment1 Contract_Number,
Pap.Segment1 Project_Number,
Aia.Payment_Method_Code Settlement_Method_Number,
NULL Payment_Date,
'' Clear_Flag,
'' Remittance_Bill_Number
FROM Ap_Invoices_All Aia,
Pa_Projects_All Pap,
Po_Headers_All Pha,
Fnd_Currencies_Vl Fcv,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir
WHERE Aia.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
--PO, project, currency condition
AND Aia.Project_Id = Pap.Project_Id(+)
AND Aia.Quick_Po_Header_Id = Pha.Po_Header_Id(+)
AND Fcv.Currency_Code = Aia.Invoice_Currency_Code
--SLA condition
AND Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
--AND Aeh.Event_Type_Code <> 'PREPAYMENT APPLIED'
--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 invoice
--Whether ae_header_id is derived from invoice or not
--To handle case like prepayment application and one invoice with different period
--If ae_header_id is derived from invoice,
--it indicates that ae_header_id derived from invoice or prepayment application and it may be used as a condition
AND ((EXISTS (SELECT Aid.Invoice_Id
FROM Ap_Invoice_Distributions_All Aid
WHERE Aid.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
AND Aid.period_name = lv_period_name) --variable: lv_period_name
AND Aeh.Ae_Header_Id = ln_ae_header_id) --variable: ln_ae_header_id
OR (NOT EXISTS
(SELECT Aid.Invoice_Id
FROM Ap_Invoice_Distributions_All Aid
WHERE Aid.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
AND Aid.period_name = lv_period_name)) --variable: lv_period_name
);
SELECT DISTINCT --updated by jarwang for bug 16611717
Aia.Invoice_Id Invoice_Id,
Aip.Invoice_Payment_Id Invoice_Payment_Id,
Ach.Check_Id Check_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Jeh.Je_Header_Id Je_Header_Id,
Aia.Terms_Id Terms_Id,
Ach.Currency_Code Currency_Code,
Aip.Period_Name Period_Name,
TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
pv_accounting_year Accounting_Year,
'' Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
NVL(ROUND(Ach.Exchange_Rate, 4), 1) Exchange_Rate,
'' Balance_Side,
Fcv.Name Entered_Currency,
Ach.Description Description,
Aia.Terms_Date Due_Date,
--TO_CHAR(Aia.Gl_Date
-- ,'YYYYMMDD') Applied_Date_Pay_Line,
--TO_CHAR(Aip.Accounting_Date
-- ,'YYYYMMDD') Applied_Date_Inv_Line,
'PAYMENT' Document_Type_Number,
Ach.Payment_Type_Flag Transaction_Type_Number,
TO_CHAR(Ach.Check_Number) Transaction_Number,
'' Invoice_Number,
Pha.Segment1 Contract_Number,
Pap.Segment1 Project_Number,
Ach.Payment_Method_Code Settlement_Method_Number,
TO_CHAR(Ach.CHECK_DATE, 'YYYYMMDD') Payment_Date,
'' Clear_Flag,
'' Remittance_Bill_Number
FROM Ap_Invoices_All Aia,
Ap_Checks_All Ach,
Ap_Invoice_Payments_All Aip,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir,
Pa_Projects_All Pap,
Po_Headers_All Pha,
Fnd_Currencies_Vl Fcv
WHERE Aia.Invoice_Id = Aip.Invoice_Id
AND Ach.Check_Id = Aip.Check_Id
AND Aia.Invoice_Id = ln_invoice_id -- variable: ln_invoice_id
AND Aip.Period_Name = lv_period_name --variable: lv_period_name
--SLA condition
AND Ent.Application_Id = 200
AND Ach.Check_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_PAYMENTS'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND (Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT' --collecting payment of prepayment invoice
OR NOT EXISTS
( --to filter prepayment application
SELECT Ael1.Ae_Header_Id
FROM Xla_Ae_Lines Ael1
WHERE Ael1.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Ael1.Accounting_Class_Code = 'PREPAID_EXPENSE'))
--Judge whether ae_header_id is from payment or not,
--if Yes, indicating that invoice id was derived from payment journal
--else, invoice id was derived from invoice journal itself
AND ((EXISTS
(SELECT Aeh1.Ae_Header_Id
FROM Xla_Transaction_Entities Ent1, Xla_Ae_Headers Aeh1
WHERE Ent1.Entity_Code = 'AP_PAYMENTS'
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 = 'AP_PAYMENTS'
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
)
--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
--PO, project condition
AND Aia.Project_Id = Pap.Project_Id(+)
AND Aia.Quick_Po_Header_Id = Pha.Po_Header_Id(+)
AND Ael.business_class_code='AP_LIAB' --added by jarwang for bug#16203205
AND Fcv.Currency_Code = Ach.Currency_Code;
SELECT DISTINCT Aia.Invoice_Id Invoice_Id,
Aeh.Ae_Header_Id Ae_Header_Id,
Jeh.Je_Header_Id Je_Header_Id,
Aia.Terms_Id Terms_Id,
Aia.Invoice_Currency_Code Currency_Code,
Jeh.Period_Name Period_Name,
TO_CHAR(Jeh.Date_Created, 'YYYYMMDD') Journal_Created_Date,
TO_CHAR(Jeh.Default_Effective_Date, 'YYYYMMDD') Journal_Date,
pv_accounting_year Accounting_Year,
'' Accounting_Period_Number,
Jeh.Je_Category Journal_Category_Number,
NVL(ROUND(Aia.Exchange_Rate, 4), 1) Exchange_Rate,
'' Balance_Side,
Fcv.Name Entered_Currency,
Aia.Description Description,
Aia.Terms_Date Due_Date,
TO_CHAR(Aia.Gl_Date, 'YYYYMMDD') Applied_Date_Prepaid_Line,
'PAYABLE' Document_Type_Number,
Aia.Invoice_Type_Lookup_Code Transaction_Type_Number,
TO_CHAR(Aia.Invoice_Num) Transaction_Number,
'' Invoice_Number,
Pha.Segment1 Contract_Number,
Pap.Segment1 Project_Number,
Aia.Payment_Method_Code Settlement_Method_Number,
'' Payment_Date,
DECODE(Aia.PAYMENT_STATUS_FLAG, 'Y', '1', '0') Clear_Flag,
'' Remittance_Bill_Number
FROM Ap_Invoices_All Aia,
Xla_Transaction_Entities Ent,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael,
Gl_Je_Headers Jeh,
Gl_Import_References Gir,
Pa_Projects_All Pap,
Po_Headers_All Pha,
Fnd_Currencies_Vl Fcv
WHERE Aia.Invoice_Id = ln_invoice_id -- variable: ln_invoice_id
--SLA condition
AND Ent.Application_Id = 200
AND Aia.Invoice_Id = Ent.Source_Id_Int_1
AND Ent.Entity_Code = 'AP_INVOICES'
AND Ent.Entity_Id = Aeh.Entity_Id
AND Aeh.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND (Aia.Invoice_Type_Lookup_Code = 'PREPAYMENT' --collecting prepayment inforamtion
OR NOT EXISTS
( --collecting invoice which is applied with prepayment
SELECT Ael1.Ae_Header_Id
FROM Xla_Ae_Lines Ael1
WHERE Ael1.Ae_Header_Id = Aeh.Ae_Header_Id
AND Ael1.Accounting_Class_Code = 'PREPAID_EXPENSE'))
--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 prepayment and its applied invoice
--PO, project condition
AND Aia.Project_Id = Pap.Project_Id(+)
AND Aia.Quick_Po_Header_Id = Pha.Po_Header_Id(+)
AND Fcv.Currency_Code = Aia.Invoice_Currency_Code;
SELECT Aph.Prepay_Invoice_Id
FROM Ap_Prepay_History_All Aph, Xla_Ae_Headers Aeh
WHERE Aph.Invoice_Id = ln_invoice_id --variable: ln_invoice_id
AND Aph.Accounting_Event_Id = Aeh.Event_Id
AND Aeh.Ae_Header_Id = ln_ae_header_id; --variable: ln_ae_header_id
SELECT DISTINCT Jcc.account_segment Prepaid_Account_Num
FROM Ja_Cn_Code_Combination_v Jcc,
Xla_Ae_Headers Aeh,
Xla_Ae_Lines Ael
WHERE Ael.Ae_Header_Id = ln_ae_header_id --variable: ln_ae_header_id
AND Ael.Ae_Header_Id = Aeh.Ae_Header_Id
AND Aeh.Ledger_Id = Jcc.Ledger_id
AND ((Aeh.Event_Type_Code = 'PREPAYMENT APPLIED' AND
Ael.Accounting_Class_Code = 'PREPAID_EXPENSE') OR
(Aeh.Event_Type_Code <> 'PREPAYMENT APPLIED' AND
Ael.Accounting_Class_Code <> 'LIABILITY'))
AND Jcc.Code_Combination_Id = Ael.Code_Combination_Id
AND Jcc.Ledger_id = pn_ledger_id; --parameter: pn_ledger_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
SELECT DECODE(lv_prepay_export_flag,
'Y', --if prepaid line has not been exported
DECODE(COUNT(*),
0,
'N', --it is not a prepayment invoice or current period is not the one prepayment created in
'Y'), --it is a prepayment invoice and current period is the one prepayment created in
'N') --prepaid line has been exported
INTO lv_prepay_export_flag
FROM Ap_Invoices_v
WHERE Invoice_Id = v_inv_id_row.Invoice_Id
AND Period_Name = v_period_name_row.period_name
AND Invoice_Type_Lookup_Code = 'PREPAYMENT';
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO lv_inv_prepay_same_period_flag
FROM Ap_Prepay_History_All Aph,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Prepay_Invoice_Id = v_inv_id_row.Invoice_Id --Updated for fixing bug# 9747676
AND Accounting_Date BETWEEN Gp.Start_Date AND Gp.End_Date
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = v_period_name_row.period_name;
,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
pv_applied_date => NULL --v_pay_row.Journal_Date --Update for fixing bug 10121399
,
pv_document_type_number => v_inv_row.Document_Type_Number,
pv_transaction_type_number => v_inv_row.Transaction_Type_Number,
pv_transaction_number => v_inv_row.Transaction_Number,
pv_invoice_number => v_inv_row.Invoice_Number,
pv_contract_number => v_inv_row.Contract_Number,
pv_project_number => v_inv_row.Project_Number,
pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
pv_payment_date => v_inv_row.Payment_Date,
pv_clear_flag => Get_Liability_Clear_Flag(v_inv_row.Invoice_Id,
v_pay_row.period_name,
pn_ledger_id,
pn_legal_entity_id),
pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
pn_functional_amount => --Updated for fixing bug# 9747960
/*Get_Liability_Amount(NULL
,v_pay_row.ae_header_id
,pn_ledger_id
,pn_legal_entity_id
,'F')*/ Get_Payment_Liability_Amount(v_pay_row.invoice_payment_id,
'F'),
pv_entered_currency => v_pay_row.Entered_Currency,
pn_entered_amount => --Updated for fixing bug# 9747960
/*Get_Liability_Amount(NULL
,v_pay_row.ae_header_id
,pn_ledger_id
,pn_legal_entity_id
,'E')*/ Get_Payment_Liability_Amount(v_pay_row.invoice_payment_id,
'E'),
pv_description => v_pay_row.Description,
pv_due_date => NULL /*Get_Due_Date(v_inv_row.Invoice_Id --Updated for fixing bug 10121399
,v_pay_row.Invoice_Payment_Id)*/,
pv_applied_journal_number => Get_Journal_Number(v_inv_row.je_header_id,
pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
,
pv_applied_date => --v_inv_row.Journal_Date
v_pay_row.Journal_Date --Updated for fixing bug 9962326
,
pv_document_type_number => v_pay_row.Document_Type_Number,
pv_transaction_type_number => v_pay_row.Transaction_Type_Number,
pv_transaction_number => v_pay_row.Transaction_Number,
pv_invoice_number => v_pay_row.Invoice_Number,
pv_contract_number => v_inv_row.Contract_Number,
pv_project_number => v_pay_row.Project_Number,
pv_settlement_method_number => v_pay_row.Settlement_Method_Number,
pv_payment_date => v_pay_row.Payment_Date,
pv_clear_flag => Get_Liability_Clear_Flag(v_inv_row.Invoice_Id,
v_pay_row.period_name,
pn_ledger_id,
pn_legal_entity_id),
pv_remittance_bill_number => Get_Remittance_Bill_Number(pn_coa_id,
v_pay_row.Check_Id) --Updated for fixing bug# 9793920
);
pv_applied_date => NULL --v_gl_sla_row.Journal_Date --Update for fixing bug 10121399
,
pv_document_type_number => v_prepaid_line_row.Document_Type_Number,
pv_transaction_type_number => v_prepaid_line_row.Transaction_Type_Number,
pv_transaction_number => v_prepaid_line_row.Transaction_Number,
pv_invoice_number => v_prepaid_line_row.Invoice_Number,
pv_contract_number => v_prepaid_line_row.Contract_Number,
pv_project_number => v_prepaid_line_row.Project_Number,
pv_settlement_method_number => v_prepaid_line_row.Settlement_Method_Number,
pv_payment_date => v_prepaid_line_row.Payment_Date,
pv_clear_flag => Get_Prepaid_Clear_Flag(v_prepay_inv_id_row.Prepay_Invoice_Id,
v_period_name_row.period_name,
lv_prepaid_account_num,
pn_ledger_id,
pn_legal_entity_id),
pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
,v_prepaid_line_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
pv_applied_date => NULL --v_gl_sla_row.Journal_Date --Update for fixing bug 10121399
,
pv_document_type_number => v_inv_line_row.Document_Type_Number,
pv_transaction_type_number => v_inv_line_row.Transaction_Type_Number,
pv_transaction_number => v_inv_line_row.Transaction_Number,
pv_invoice_number => v_inv_line_row.Invoice_Number,
pv_contract_number => v_inv_line_row.Contract_Number,
pv_project_number => v_inv_line_row.Project_Number,
pv_settlement_method_number => v_inv_line_row.Settlement_Method_Number,
pv_payment_date => v_inv_line_row.Payment_Date,
pv_clear_flag => Get_Liability_Clear_Flag(v_inv_Id_row.Invoice_Id,
v_period_name_row.period_name,
pn_ledger_id,
pn_legal_entity_id),
pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
,v_inv_line_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
pv_due_date => NULL /*Get_Due_Date(v_inv_row.Invoice_Id --Updated for fixing bug 10121399
,NULL)*/,
pv_applied_journal_number => Get_Journal_Number(ln_prepaid_je_header_id,
pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
,
pv_applied_date => --ln_prepaid_journal_date
v_gl_sla_row.Journal_Date --Update for fixing bug 9962326
,
pv_document_type_number => '',
pv_transaction_type_number => '',
pv_transaction_number => '',
pv_invoice_number => v_inv_row.Invoice_Number,
pv_contract_number => v_inv_row.Contract_Number,
pv_project_number => v_inv_row.Project_Number,
pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
pv_payment_date => v_inv_row.Payment_Date,
pv_clear_flag => Get_Prepaid_Clear_Flag(v_prepay_inv_id_row.Prepay_Invoice_Id,
v_period_name_row.period_name,
lv_prepaid_account_num,
pn_ledger_id,
pn_legal_entity_id),
pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
pv_due_date => NULL /*Get_Due_Date(v_inv_row.Invoice_Id --Updated for fixing bug 10121399
,NULL)*/,
pv_applied_journal_number => Get_Journal_Number(v_inv_line_row.je_header_id,
pn_legal_entity_id) --Add a parameter:legal entity, by Jianchao Chi for V2 Upgrade 06-Jan-2011
,
pv_applied_date => --v_inv_line_row.journal_date
v_gl_sla_row.Journal_Date --Update for fixing bug 9962326
,
pv_document_type_number => '',
pv_transaction_type_number => '',
pv_transaction_number => '',
pv_invoice_number => v_inv_row.Invoice_Number,
pv_contract_number => v_inv_row.Contract_Number,
pv_project_number => v_inv_row.Project_Number,
pv_settlement_method_number => v_inv_row.Settlement_Method_Number,
pv_payment_date => v_inv_row.Payment_Date,
pv_clear_flag => Get_Liability_Clear_Flag(v_inv_Id_row.Invoice_Id,
v_period_name_row.period_name,
pn_ledger_id,
pn_legal_entity_id),
pv_remittance_bill_number => '' /*Get_Remittance_Bill_Number(pn_coa_id
,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
SELECT DECODE(COUNT(*), 0, 'N', 'Y')
INTO lv_inv_prepay_same_period_flag
FROM Ap_Prepay_History_All Aph,
Gl_Ledgers Gl,
Gl_Periods Gp
WHERE Invoice_Id = v_inv_id_row.Invoice_Id
AND Accounting_Date BETWEEN Gp.Start_Date AND Gp.End_Date
AND Gl.period_set_name = Gp.period_set_name
AND Gl.accounted_period_type = Gp.period_type
AND Gl.Ledger_Id = pn_ledger_id --parameter: pn_ledger_id
AND Gp.period_name = v_period_name_row.period_name;
,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
,v_inv_row.Invoice_Id)*/ --Updated for fixing bug# 9793920
);
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('PAYABLE_DETAIL_REPORT',
Ja_Cn_Utility.GV_MODULE_APAR);