[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT currency_code
FROM gl_sets_of_books
WHERE set_of_books_id = c_set_of_books_id;
SELECT Derive_type
FROM Fnd_Currencies
WHERE Currency_Code IN (v_frm_curr);
Changed the select to get the rate into cursor.
*/
CURSOR get_curr_rate(p_to_curr IN VARCHAR2, p_from_curr IN VARCHAR2) IS
SELECT Conversion_Rate
FROM Gl_Daily_Rates
WHERE To_Currency = p_to_curr
AND From_Currency = p_from_curr
AND trunc(Conversion_Date) = trunc(nvl(c_conversion_date,SYSDATE))
AND Conversion_Type = c_conversion_type;
Removed the select and changed the same into a cursor.
*/
OPEN get_curr_rate(v_func_curr,v_frm_curr);
Removed the select and changed the same into a cursor.
*/
OPEN get_curr_rate(v_func_curr,v_frm_curr);
SELECT
SUM( decode(match_status_flag, 'A', 1, 0))
, COUNT(invoice_distribution_id)
FROM
ap_invoice_distributions_all
WHERE invoice_id = pn_invoice_id;
'SELECT '
|| pv_col_name
|| ' FROM gl_code_combinations WHERE code_combination_id = :a'
INTO
lv_val
USING
pn_ccid;
'SELECT '
|| pv_col_name
|| ' FROM gl_code_combinations WHERE code_combination_id = :a'
INTO
lv_val
USING
pn_ccid;
SELECT DISTINCT
line.taxable_basis
, head.fbt_rate
, head.surcharge_rate
, head.edu_cess_rate
, head.sh_cess_rate
FROM
jai_fbt_setup_lines line
, jai_fbt_setup_headers head
WHERE line.legal_entity_id = pn_legal_entity_id
AND line.legal_entity_id = head.legal_entity_id
--modified by lvxiao for R12 new changegs on 06-Nov-2008, begin
-----------------------------------------------------------------------------------
AND head.fbt_year = pn_fbt_year
-----------------------------------------------------------------------------------
--modified by lvxiao for R12 new changegs on 06-Nov-2008, end
AND fringe_benefit_type_code = pv_fringe_benefit_type_code;
SELECT
ledger_id
FROM
xle_fp_ou_ledger_v
WHERE legal_entity_id = pn_legal_entity_id;
SELECT
org_information1
FROM
HR_ORGANIZATION_INFORMATION
WHERE ORGANIZATION_ID=pn_legal_entity_id
AND ORG_INFORMATION_CONTEXT ='Legal Entity Accounting';*/
SELECT
NVL( fc.precision
, 2
) precision
INTO
ln_precision
FROM
gl_sets_of_books gsob
, fnd_currencies fc
WHERE gsob.set_of_books_id = fnd_profile.Value('GL_SET_OF_BKS_ID')
AND gsob.currency_code = fc.currency_code;
SELECT aia.exchange_rate
INTO ln_exchange_rate
FROM AP_INVOICES_ALL aia
, Ap_Invoice_Distributions_All aida
WHERE aida.invoice_distribution_id = pn_inv_dist_id
AND aida.invoice_id = aia.invoice_id;
SELECT currency_conversion_rate
INTO ln_exchange_rate
FROM gl_je_headers head
WHERE je_header_id = pn_je_header_id;
SELECT
currency_code
INTO
x_fbt_repository_type.FBT_CURRENCY
FROM
gl_sets_of_books
WHERE set_of_books_id = ln_sob_id;
SELECT aida.accounting_date
INTO x_fbt_repository_type.invoice_date
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_distribution_id = pn_inv_dist_id;
SELECT invoice_num
INTO x_fbt_repository_type.je_name
FROM ap_invoices_all aia
, ap_invoice_distributions_all aida
WHERE invoice_distribution_id = pn_inv_dist_id
AND aia.invoice_id = aida.invoice_id;
SELECT aida.invoice_line_number
--aida.distribution_line_number
INTO x_fbt_repository_type.JE_LINE_NUM
FROM ap_invoice_distributions_all aida
WHERE aida.invoice_distribution_id = pn_inv_dist_id;
PROCEDURE Insert_Fbt_Repository
( p_fbt_repository IN JAI_FBT_REPOSITORY%ROWTYPE
)
IS
ln_fbt_trans_id NUMBER;
lv_procedure_name VARCHAR2(40) := 'Insert_Fbt_Repository';
SELECT Jai_Fbt_Repository_s.NEXTVAL
INTO ln_fbt_trans_id
FROM dual;
INSERT INTO JAI_FBT_REPOSITORY
( FBT_TRANSACTION_ID
, LEGAL_ENTITY_ID
, PERIOD_START_DATE
, PERIOD_END_DATE
, SOURCE
, JE_HEADER_ID
, BATCH_NAME
, JE_SOURCE
, JE_NAME
, PERIOD_NAME
, JE_LINE_NUM
, INVOICE_DISTRIBUTION_ID
, INVOICE_DATE
, INVOICE_CURRENCY_CODE
, DISTRIBUTION_AMT
, DIST_CODE_COMBINATION_ID
, DIST_NATURAL_ACCOUNT_VALUE
, DIST_BALANCE_ACCOUNT_VALUE
, FRINGE_BENEFIT_TYPE_CODE
, FBT_CURRENCY
, TAXABLE_BASIS
, FBT_TAXABLE_AMOUNT
, FBT_TAX_AMOUNT
, FBT_SURCHARGE_AMOUNT
, FBT_EDU_CESS_AMOUNT
, FBT_SH_CESS_AMOUNT
, MANUAL_FLAG
--modified by lvxiao for R12 new changes on 06-Nov-2008, begin
-----------------------------------------------------------------------------------
, CONVERSION_RATE
, CONVERTED_AMOUNT
, CONVERSION_TYPE
, CONVERSION_DATE
, MODIFIED_FLAG
-----------------------------------------------------------------------------------
--modified by lvxiao for R12 new changes on 06-Nov-2008, end
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, LAST_UPDATE_LOGIN
)
VALUES ( ln_fbt_trans_id
, P_FBT_REPOSITORY.LEGAL_ENTITY_ID
, P_FBT_REPOSITORY.PERIOD_START_DATE
, P_FBT_REPOSITORY.PERIOD_END_DATE
, P_FBT_REPOSITORY.SOURCE
, P_FBT_REPOSITORY.JE_HEADER_ID
, P_FBT_REPOSITORY.BATCH_NAME
, P_FBT_REPOSITORY.JE_SOURCE
, P_FBT_REPOSITORY.JE_NAME
, P_FBT_REPOSITORY.PERIOD_NAME
, P_FBT_REPOSITORY.JE_LINE_NUM
, P_FBT_REPOSITORY.INVOICE_DISTRIBUTION_ID
, P_FBT_REPOSITORY.INVOICE_DATE
, P_FBT_REPOSITORY.INVOICE_CURRENCY_CODE
, P_FBT_REPOSITORY.DISTRIBUTION_AMT
, P_FBT_REPOSITORY.DIST_CODE_COMBINATION_ID
, P_FBT_REPOSITORY.DIST_NATURAL_ACCOUNT_VALUE
, P_FBT_REPOSITORY.DIST_BALANCE_ACCOUNT_VALUE
, P_FBT_REPOSITORY.FRINGE_BENEFIT_TYPE_CODE
, P_FBT_REPOSITORY.FBT_CURRENCY
, P_FBT_REPOSITORY.TAXABLE_BASIS
, P_FBT_REPOSITORY.FBT_TAXABLE_AMOUNT
, P_FBT_REPOSITORY.FBT_TAX_AMOUNT
, P_FBT_REPOSITORY.FBT_SURCHARGE_AMOUNT
, P_FBT_REPOSITORY.FBT_EDU_CESS_AMOUNT
, P_FBT_REPOSITORY.FBT_SH_CESS_AMOUNT
, 'N' -- indicate manual transactions
--modified by lvxiao for R12 new changes on 06-Nov-2008, begin
-----------------------------------------------------------------------------------
, P_FBT_REPOSITORY.CONVERSION_RATE
, P_FBT_REPOSITORY.CONVERTED_AMOUNT
, P_FBT_REPOSITORY.CONVERSION_TYPE
, P_FBT_REPOSITORY.CONVERSION_DATE
, 0 -- indicate no modification transactions
-----------------------------------------------------------------------------------
--modified by lvxiao for R12 new changes on 06-Nov-2008, end
, SYSDATE
, fnd_global.user_id
, SYSDATE
, fnd_global.user_id
, fnd_global.login_id
);
END Insert_Fbt_Repository;
SELECT
OPERATING_UNIT_ID
FROM
XLE_FP_OU_LEDGER_V
WHERE LEGAL_ENTITY_ID = pn_legal_entity_id;
SELECT
organization_id operating_unit_id
FROM
hr_organization_information
WHERE org_information2 = pn_legal_entity_id --LEGAL ENTITY ID
AND ORG_INFORMATION_CONTEXT='Operating Unit Information';
SELECT
chart_of_accounts_id
FROM
gl_sets_of_books
WHERE
set_of_books_id = fnd_profile.Value('GL_SET_OF_BKS_ID');
SELECT
application_column_name
FROM
FND_SEGMENT_ATTRIBUTE_VALUES
WHERE application_id = 101
AND id_flex_code ='GL#'
AND segment_attribute_type = 'GL_ACCOUNT'
AND id_flex_num = pn_coa
AND attribute_value = 'Y';
SELECT
application_column_name
FROM
FND_SEGMENT_ATTRIBUTE_VALUES
WHERE application_id = 101
AND id_flex_code ='GL#'
AND segment_attribute_type = 'GL_BALANCING'
AND id_flex_num = pn_coa
AND attribute_value = 'Y';
/* cursor to select all the eligible invoices exclude manual modified transactions in JAI_FBT_REPOSITORY
The invoice to be eligible for FBT should meet the following criteria
1) invoice should be of type
('STANDARD','DEBIT', 'CREDIT', 'EXPENSE REPORT','MIXED')
2) invoice should be validated invoice
3) only non-recoverable tax lines are eligible for FBT
4) match the criteria entered by the user in CP parameters form
5) do not re-fetch transactions that have been modified in JAI_FBT_REPOSITORY
*/
-- this clause gets all the matched invoices with non-recoverable tax lines
CURSOR get_eligible_invoices_cur
( pv_nat_acc_seg VARCHAR2
, pv_bal_acc_seg VARCHAR2
-- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
----------------------------------------------------------
--, pn_operating_unit NUMBER
, pn_legal_entity_id NUMBER
----------------------------------------------------------
-- Modified by Jia for bug#7675638 on 30-Dec-2008, End
, pn_fbt_year NUMBER
)
IS
SELECT
Get_Natural_Acc_Seg( pv_nat_acc_seg
, /*commented out the below section for FBT 11.5 backport by Eric Ma on 29-July-2008
decode( NVL(accrue_on_receipt_flag, 'N')
, 'N'
, det.DIST_CODE_COMBINATION_ID
, 'Y'
, po.code_combination_id
)
*/
det.DIST_CODE_COMBINATION_ID
) nat_acct_seg
, Get_Balance_Acc_Seg( pv_bal_acc_seg
, det.DIST_CODE_COMBINATION_id
) bal_acct_seg
, det.amount
, det.dist_match_type
, det.invoice_distribution_id
, det.dist_code_combination_id
, head.invoice_currency_code
, head.exchange_rate_type
, head.exchange_rate
, head.exchange_date
FROM
ap_invoices_all head
, ap_invoice_distributions_all det
, po_distributions_all po
WHERE head.invoice_id = det.invoice_id
-- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
--------------------------------------------------------------
--AND head.org_id = pn_operating_unit
AND head.legal_entity_id = pn_legal_entity_id
--------------------------------------------------------------
-- Modified by Jia for bug#7675638 on 30-Dec-2008, End
AND det.po_distribution_id = po.po_distribution_id
AND invoice_type_lookup_code IN ( 'STANDARD'
, 'DEBIT'
, 'CREDIT'
, 'EXPENSE REPORT'
,'MIXED'
)
AND Check_Inv_Validation( head.invoice_id
) = 'VALIDATED'
AND (det.dist_match_type IN ( 'ITEM_TO_PO'
, 'ITEM_TO_RECEIPT'
)
--modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, begin
-------------------------------------------------------------------------------------------
OR EXISTS (SELECT 1
FROM jai_ap_match_inv_taxes jamit
, ap_invoice_distributions_all aida1
WHERE jamit.invoice_distribution_id
= det.invoice_distribution_id
AND jamit.parent_invoice_distribution_id
= aida1.invoice_distribution_id
AND aida1.dist_match_type IN ('ITEM_TO_PO'
, 'ITEM_TO_RECEIPT'
)
/* ja_in_ap_tax_distributions jiatd
, ap_invoice_distributions_all aida1
WHERE jiatd.invoice_id
= det.invoice_id
AND jiatd.distribution_line_number
= det.distribution_line_number
AND jiatd.parent_invoice_distribution_id
= aida1.invoice_distribution_id */
)
)
AND det.accounting_date BETWEEN ld_start_date AND ld_end_date
-- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
--------------------------------------------------------------
--AND det.org_id = pn_operating_unit
AND det.org_id = head.org_id
--------------------------------------------------------------
-- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
AND det.invoice_distribution_id IN ( SELECT
aida.invoice_distribution_id
FROM
AP_INVOICE_DISTRIBUTIONS_ALL AIDA
WHERE aida.invoice_id = head.invoice_id
AND NOT EXISTS
( SELECT 1
FROM JAI_AP_MATCH_INV_TAXES JAMIT
WHERE JAMIT.INVOICE_DISTRIBUTION_ID
= AIDA.INVOICE_DISTRIBUTION_ID
AND RECOVERABLE_FLAG = 'Y'
/* ja_in_ap_tax_distributions jiatdi
WHERE jiatdi.invoice_id
= AIDA.invoice_id
AND jiatdi.distribution_line_number
= AIDA.distribution_line_number */
)
)
/* following code excludes the manual modified transactions in JAI_FBT_REPOSITORY
this is for FBT R12 new change
*/
AND NOT EXISTS
( SELECT 1
FROM JAI_FBT_REPOSITORY jfr
WHERE jfr.invoice_distribution_id
= det.invoice_distribution_id
)
-------------------------------------------------------------------------------------------
--modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, end
AND ( ( NVL(accrue_on_receipt_flag, 'N') = 'N'
AND Get_Natural_Acc_Seg( pv_nat_acc_seg
, det.DIST_CODE_COMBINATION_id
) IN ( SELECT NATURAL_ACCOUNT_VALUE
FROM jai_fbt_setup_lines
WHERE legal_entity_id = pn_legal_entity_id
AND fbt_year = pn_fbt_year
AND FRINGE_BENEFIT_TYPE_CODE
= NVL(pv_fringe_benefit_type_code
, FRINGE_BENEFIT_TYPE_CODE)
)
)
)
UNION
-- this clause gets all the unmatched invoices
SELECT
Get_Natural_Acc_Seg( pv_nat_acc_seg
, det.DIST_CODE_COMBINATION_id
) nat_acct_seg
, Get_Balance_Acc_Seg( pv_bal_acc_seg
, det.DIST_CODE_COMBINATION_id
) bal_acct_seg
, det.amount
, det.dist_match_type
, det.invoice_distribution_id
, det.dist_code_combination_id
, head.invoice_currency_code
, head.exchange_rate_type
, head.exchange_rate
, head.exchange_date
FROM
ap_invoices_all head
, ap_invoice_distributions_all det
WHERE head.invoice_id = det.invoice_id
-- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
--------------------------------------------------------------
--AND head.org_id = pn_operating_unit
AND head.legal_entity_id = pn_legal_entity_id
--------------------------------------------------------------
-- Modified by Jia for bug#7675638 on 30-Dec-2008, End
AND invoice_type_lookup_code IN ( 'STANDARD'
, 'DEBIT'
, 'CREDIT'
, 'EXPENSE REPORT'
, 'MIXED'
)
AND Check_Inv_Validation( head.invoice_id
) = 'VALIDATED'
AND (det.dist_match_type IS NULL
OR det.dist_match_type NOT IN ( 'ITEM_TO_PO'
, 'ITEM_TO_RECEIPT'
)
)
--modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, begin
-------------------------------------------------------------------------------------------
AND NOT EXISTS ( SELECT 1
FROM jai_ap_match_inv_taxes jamit
WHERE jamit.invoice_distribution_id
= det.invoice_distribution_id
/* ja_in_ap_tax_distributions jiatd
WHERE jiatd.invoice_id
= det.invoice_id
AND jiatd.distribution_line_number
= det.distribution_line_number */
)
------------------------------------------------------------------------------------------
--modified by lvxiao for upgrade code for new changes to R12 from 11i on 06-Nov-2008, end
/* following code excludes the manual modified transactions in JAI_FBT_REPOSITORY
this is for FBT R12 new change
*/
AND NOT EXISTS
( SELECT 1
FROM JAI_FBT_REPOSITORY jfr
WHERE jfr.invoice_distribution_id
= det.invoice_distribution_id
)
AND det.accounting_date BETWEEN ld_start_date AND ld_end_date
-- Modified by Jia for bug#7675638 on 30-Dec-2008, Begin
--------------------------------------------------------------
--AND det.org_id = pn_operating_unit
AND det.org_id = head.org_id
--------------------------------------------------------------
-- Modified by Jia for bug#7675638 on 30-Dec-2008, End
AND Get_Natural_Acc_Seg( pv_nat_acc_seg
, det.DIST_CODE_COMBINATION_id
) IN ( SELECT
NATURAL_ACCOUNT_VALUE
FROM
jai_fbt_setup_lines
WHERE legal_entity_id = pn_legal_entity_id
AND fbt_year = pn_fbt_year
AND FRINGE_BENEFIT_TYPE_CODE =
NVL( pv_fringe_benefit_type_code
, FRINGE_BENEFIT_TYPE_CODE
)
);
/* cursor to select all the eligible journals from GL exclude manual modified journals in JAI_FBT_REPOSITORY
The invoice to be eligible for FBT should meet the following criteria
1) all journal entries should be considered for FBT process excluding the
following source:('Payables')
2) only posted journals are considered
3) only journal entries with matched nature account are considered
4) match the criteria entered by the user in CP parameters form
5) do not re-fetch journals that have been modified in JAI_FBT_REPOSITORY
*/
CURSOR get_eligible_journals_cur
( pv_nat_acc_seg VARCHAR2,
pv_bal_acc_seg VARCHAR2,
pn_fbt_year NUMBER
)
IS
SELECT /* Rowid(head)
Index(head,GL_JE_HEADERS_U1)
Index(batch,GL_JE_BATCHES_U1)
*/
Get_Natural_Acc_Seg(PV_NAT_ACC_SEG
, LINE.CODE_COMBINATION_ID) NAT_ACCT_SEG,
Get_Balance_Acc_Seg(PV_BAL_ACC_SEG
, LINE.CODE_COMBINATION_ID) BAL_ACCT_SEG,
(NVL(LINE.ENTERED_DR, 0)
-NVL(LINE.ENTERED_CR, 0)) DISTRIBUTION_AMT,
LINE.JE_LINE_NUM JE_LINE_NUM,
LINE.CODE_COMBINATION_ID JE_LINE_CCID,
HEAD.JE_HEADER_ID JE_HEADER_ID,
BATCH.NAME JE_BATCH_NAME,
HEAD.JE_SOURCE JE_SOURCE,
HEAD.NAME JE_NAME,
HEAD.PERIOD_NAME PERIOD_NAME,
LINE.EFFECTIVE_DATE JE_LINE_EFFECTIVE_DATE,
HEAD.CURRENCY_CODE CURRENCY_CODE,
HEAD.CURRENCY_CONVERSION_TYPE,
HEAD.CURRENCY_CONVERSION_RATE,
HEAD.CURRENCY_CONVERSION_DATE
FROM
gl_je_headers head
, gl_je_lines line
, gl_je_batches batch
WHERE line.effective_date >= ld_start_date
AND line.effective_date <= ld_end_date
AND line.STATUS = 'P'
AND head.je_source <> 'Payables'
AND head.JE_HEADER_ID = line.JE_HEADER_ID
AND head.JE_BATCH_ID = batch.JE_BATCH_ID
AND Get_Natural_Acc_Seg( pv_nat_acc_seg
, line.CODE_COMBINATION_ID
) IN ( SELECT NATURAL_ACCOUNT_VALUE
FROM jai_fbt_setup_lines
WHERE legal_entity_id = pn_legal_entity_id
AND fbt_year = pn_fbt_year
AND FRINGE_BENEFIT_TYPE_CODE =
NVL( pv_fringe_benefit_type_code
, FRINGE_BENEFIT_TYPE_CODE
)
)
--modified by lvxiao for upgrade code to R12 on 06-Nov-2008,begin
-----------------------------------------------------------------------------------
AND Get_Balance_Acc_Seg( pv_bal_acc_seg
, line.CODE_COMBINATION_ID
) IN ( SELECT segment_value
FROM GL_LEDGER_NORM_SEG_VALS
WHERE legal_entity_id = pn_legal_entity_id
AND SEGMENT_TYPE_CODE = 'B'
)
-----------------------------------------------------------------------------------
--modified by lvxiao for upgrade code to R12 on 06-Nov-2008,end
--Following code excludes manual modified journals in JAI_FBT_REPOSITORY
AND NOT EXISTS
( SELECT 1
FROM JAI_FBT_REPOSITORY jfr
WHERE jfr.je_header_id = head.JE_HEADER_ID
AND jfr.je_line_num = line.JE_LINE_NUM
AND head.je_header_id = line.je_header_id
);
SELECT
fringe_benefit_type_code
FROM
jai_fbt_setup_lines
WHERE natural_account_value = pn_nat_account
AND legal_entity_id = pn_legal_entity_id
--modified by lvxiao for R12 new change on 06-Nov-2008, begin
-----------------------------------------------------------------------------------
AND fbt_year = pn_fbt_year;
DELETE
FROM
jai_fbt_repository
WHERE legal_entity_id = pn_legal_entity_id
--modified by lvxiao for R12 new change on 06-Nov-2008, begin
-----------------------------------------------------------------------------------
AND invoice_date >= ld_start_date
AND invoice_date <= ld_end_date
/* AND period_start_date >= ld_start_date
AND period_end_date <= ld_end_date*/
AND fringe_benefit_type_code = NVL( pv_fringe_benefit_type_code
, FRINGE_BENEFIT_TYPE_CODE
)
AND (manual_flag IS NULL OR manual_flag = 'N') -- not manual transactions
AND (modified_flag IS NULL OR modified_flag = 0 ); -- transactions not been modified.
/* put all the required values in the record type for insertion
into jai_fbt_repository table
*/
INSERT_FBT_REPOSITORY( fbt_repository_rec
);
/* put all the required values in the record type for insertion
into jai_fbt_repository table
*/
INSERT_FBT_REPOSITORY( fbt_repository_rec
);
SELECT period_end_date
INTO ld_period_end_date
FROM jai_fbt_process_date
WHERE legal_entity_id = pn_legal_entity_id
AND fbt_year = pn_fbt_year;
UPDATE jai_fbt_process_date
SET period_end_date = ld_end_date
WHERE legal_entity_id = pn_legal_entity_id
AND fbt_year = pn_fbt_year;
INSERT
INTO JAI_FBT_PROCESS_DATE(LEGAL_ENTITY_ID,
FBT_YEAR,
PERIOD_START_DATE,
PERIOD_END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES(pn_legal_entity_id,
pn_fbt_year,
ld_start_date,
ld_end_date,
SYSDATE,
FND_GLOBAL.USER_ID,
SYSDATE,
FND_GLOBAL.USER_ID,
FND_GLOBAL.LOGIN_ID);