[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(ZRB.percentage_rate,ZRB.quantity_rate)
INTO l_taxrate
FROM zx_rates_b ZRB
WHERE ZRB.tax_rate_id = P_TAX_RATE_ID
/* UT TEST. these effectivity dates checking is not required for Reporting
AND p_a_date >= NVL(ZRB.effective_from, TO_DATE('01-01-1895', 'DD-MM-RRRR'))
AND p_a_date <= NVL(ZRB.effective_to, TO_DATE('31-12-2195', 'DD-MM-RRRR')) */
;
SELECT 'Yes' INTO l_new
FROM DUAL
WHERE p_r_date BETWEEN NVL(P_PERIOD_START_DATE,TO_DATE('01-01-1690','DD-MM-RRRR'))
AND NVL(P_PERIOD_END_DATE,TO_DATE('31-12-2690','DD-MM-RRRR'));
SELECT 'Yes'
INTO l_new
FROM DUAL
WHERE p_a_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1700','DD-MM-RRRR')) AND NVL(p_period_end_date,TO_DATE('31-12-4000','DD-MM-RRRR'));
SELECT NVL(ZRB.percentage_rate,ZRB.quantity_rate)
INTO l_taxrate
FROM zx_rates_b ZRB
WHERE ZRB.tax_rate_id = P_TAX_RATE_ID
AND p_a_date >= NVL(ZRB.effective_from, TO_DATE('01-01-1895', 'DD-MM-RRRR'))
AND p_a_date <= NVL(ZRB.effective_to, TO_DATE('31-12-2195', 'DD-MM-RRRR'));
SELECT 1
INTO l_rd
FROM DUAL
WHERE p_rev_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1890' ,'DD-MM-RRRR')) AND NVL(p_period_end_date,TO_DATE('30-12-2199','DD-MM-RRRR'));
SELECT 'Yes'
INTO l_new
FROM DUAL
WHERE p_rev_date BETWEEN NVL(p_period_start_date,TO_DATE('01-01-1690','DD-MM-RRRR')) AND NVl(p_period_end_date,TO_DATE('31-12-2690','DD-MM-RRRR'));
We should run this procedure before the selection process run i.e Before the TRL call (as in 11i)
Hence commenting this total procedure code.
*/
/*
PROCEDURE tax_date_maintenance_program(p_period_end_date IN DATE)
IS
-- +======================================================================+
-- | Name : tax_date_maintenance_program |
-- | Description : This procedure maintain the tax date |
-- | . |
-- | |
-- +======================================================================+
CURSOR lcu_cust_trx
IS
SELECT JG.trx_id CUSTOMER_TRX_ID
, MAX(RPT.apply_date) APPLY_DATE
, FND_DATE.CANONICAL_TO_DATE(JG.tax_invoice_date) TAX_INVOICE_DATE
FROM jg_zz_vat_trx_details JG
, ar_receivable_applications RPT
WHERE JG.trx_id = RPT.applied_customer_trx_id
AND JG.ledger_id = RPT.set_of_books_id
AND RPT.status = 'APP'
AND JG.tax_status_code = 'CL'
AND RPT.amount_applied >= 0
AND TRUNC(RPT.apply_date) <= TRUNC(P_PERIOD_END_DATE)
AND RPT.apply_date < FND_DATE.CANONICAL_TO_DATE(JG.tax_invoice_date)
AND NOT EXISTS (SELECT 1
FROM jg_zz_vat_trx_details JGZZ
WHERE JGZZ.trx_id = JG.trx_id
AND JGZZ.tax_invoice_date IS NOT NULL
)
GROUP BY JG.trx_id
,JG.tax_invoice_date
;
UPDATE jg_zz_vat_trx_details JG
SET JG.tax_invoice_date = FND_DATE.DATE_TO_CANONICAL(rec_cust_trx.apply_date )
WHERE JG.trx_id = rec_cust_trx.customer_trx_id;
PROCEDURE InsertIntoGlobal (
p_jg_info_n1 IN NUMBER DEFAULT NULL
, p_jg_info_n2 IN NUMBER DEFAULT NULL
, p_jg_info_n3 IN NUMBER DEFAULT NULL
, p_jg_info_n4 IN NUMBER DEFAULT NULL
, p_jg_info_n5 IN NUMBER DEFAULT NULL
, p_jg_info_n6 IN NUMBER DEFAULT NULL
, p_jg_info_n7 IN NUMBER DEFAULT NULL
, p_jg_info_n8 IN NUMBER DEFAULT NULL
, p_jg_info_n9 IN NUMBER DEFAULT NULL
, p_jg_info_n11 IN NUMBER DEFAULT NULL
, p_jg_info_n14 IN NUMBER DEFAULT NULL
, p_jg_info_n15 IN NUMBER DEFAULT NULL
, p_jg_info_n16 IN NUMBER DEFAULT NULL
, p_jg_info_n17 IN NUMBER DEFAULT NULL
, p_jg_info_n18 IN NUMBER DEFAULT NULL
, p_jg_info_n19 IN NUMBER DEFAULT NULL
, p_jg_info_n20 IN NUMBER DEFAULT NULL
, p_jg_info_n21 IN NUMBER DEFAULT NULL
, p_jg_info_n22 IN NUMBER DEFAULT NULL
, p_jg_info_n23 IN NUMBER DEFAULT NULL
, p_jg_info_n24 IN NUMBER DEFAULT NULL
, p_jg_info_n25 IN NUMBER DEFAULT NULL
, p_jg_info_n26 IN NUMBER DEFAULT NULL
, p_jg_info_n27 IN NUMBER DEFAULT NULL
, p_jg_info_d1 IN DATE DEFAULT NULL
, p_jg_info_d2 IN DATE DEFAULT NULL
, p_jg_info_d3 IN DATE DEFAULT NULL
, p_jg_info_d4 IN DATE DEFAULT NULL
, p_jg_info_d5 IN DATE DEFAULT NULL
, p_jg_info_v1 IN CHAR DEFAULT NULL
, p_jg_info_v2 IN CHAR DEFAULT NULL
, p_jg_info_v3 IN CHAR DEFAULT NULL
, p_jg_info_v4 IN CHAR DEFAULT NULL
, p_jg_info_v5 IN CHAR DEFAULT NULL
, p_jg_info_v6 IN CHAR DEFAULT NULL
, p_jg_info_v7 IN CHAR DEFAULT NULL
, p_jg_info_v8 IN CHAR DEFAULT NULL
, p_jg_info_v9 IN CHAR DEFAULT NULL
, p_jg_info_v10 IN CHAR DEFAULT NULL
, p_jg_info_v11 IN CHAR DEFAULT NULL
, p_jg_info_v12 IN CHAR DEFAULT NULL
, p_jg_info_v13 IN CHAR DEFAULT NULL
, p_jg_info_v14 IN CHAR DEFAULT NULL
, p_jg_info_v15 IN CHAR DEFAULT NULL
, p_jg_info_v16 IN CHAR DEFAULT NULL
, p_jg_info_v17 IN CHAR DEFAULT NULL
, p_jg_info_v18 IN CHAR DEFAULT NULL
, p_jg_info_v19 IN CHAR DEFAULT NULL
, p_jg_info_v20 IN CHAR DEFAULT NULL
, p_jg_info_v21 IN CHAR DEFAULT NULL
, p_jg_info_v22 IN CHAR DEFAULT NULL
, p_jg_info_v23 IN CHAR DEFAULT NULL
, p_jg_info_v24 IN CHAR DEFAULT NULL
, p_jg_info_v25 IN CHAR DEFAULT NULL
, p_jg_info_v26 IN CHAR DEFAULT NULL
, p_jg_info_v27 IN CHAR DEFAULT NULL
, p_jg_info_v28 IN CHAR DEFAULT NULL
, p_jg_info_v29 IN CHAR DEFAULT NULL
, p_jg_info_v30 IN CHAR DEFAULT NULL
, p_jg_info_v31 IN CHAR DEFAULT NULL
, p_jg_info_v32 IN CHAR DEFAULT NULL
, p_jg_info_v33 IN CHAR DEFAULT NULL
, p_jg_info_v34 IN CHAR DEFAULT NULL
, p_jg_info_v35 IN CHAR DEFAULT NULL
, p_jg_info_v36 IN CHAR DEFAULT NULL
, p_jg_info_v37 IN CHAR DEFAULT NULL
)
IS
-- +======================================================================+
-- | Name : InsertIntoGlobal |
-- | Description : This procedure inserts data into the Global Temp |
-- | table |
-- +======================================================================+
BEGIN
INSERT INTO JG_ZZ_VAT_TRX_GT(
jg_info_n1
, jg_info_n2
, jg_info_n3
, jg_info_n4
, jg_info_n5
, jg_info_n6
, jg_info_n7
, jg_info_n8
, jg_info_n9
, jg_info_n11
, jg_info_n14
, jg_info_n15
, jg_info_n16
, jg_info_n17
, jg_info_n18
, jg_info_n19
, jg_info_n20
, jg_info_n21
, jg_info_n22
, jg_info_n23
, jg_info_n24
, jg_info_n25
, jg_info_n26
, jg_info_n27
, jg_info_d1
, jg_info_d2
, jg_info_d3
, jg_info_d4
, jg_info_d5
, jg_info_v1
, jg_info_v2
, jg_info_v3
, jg_info_v4
, jg_info_v5
, jg_info_v6
, jg_info_v7
, jg_info_v8
, jg_info_v9
, jg_info_v10
, jg_info_v11
, jg_info_v12
, jg_info_v13
, jg_info_v14
, jg_info_v15
, jg_info_v16
, jg_info_v17
, jg_info_v18
, jg_info_v19
, jg_info_v20
, jg_info_v21
, jg_info_v22
, jg_info_v23
, jg_info_v24
, jg_info_v25
, jg_info_v26
, jg_info_v27
, jg_info_v28
, jg_info_v29
, jg_info_v30
, jg_info_v31
, jg_info_v32
, jg_info_v33
, jg_info_v34
, jg_info_v35
, jg_info_v36
, jg_info_v37
)
VALUES
(
p_jg_info_n1
, p_jg_info_n2
, p_jg_info_n3
, p_jg_info_n4
, p_jg_info_n5
, p_jg_info_n6
, p_jg_info_n7
, p_jg_info_n8
, p_jg_info_n9
, p_jg_info_n11
, p_jg_info_n14
, p_jg_info_n15
, p_jg_info_n16
, p_jg_info_n17
, p_jg_info_n18
, p_jg_info_n19
, p_jg_info_n20
, p_jg_info_n21
, p_jg_info_n22
, p_jg_info_n23
, p_jg_info_n24
, p_jg_info_n25
, p_jg_info_n26
, p_jg_info_n27
, p_jg_info_d1
, p_jg_info_d2
, p_jg_info_d3
, p_jg_info_d4
, p_jg_info_d5
, p_jg_info_v1
, p_jg_info_v2
, p_jg_info_v3
, p_jg_info_v4
, p_jg_info_v5
, p_jg_info_v6
, p_jg_info_v7
, p_jg_info_v8
, p_jg_info_v9
, p_jg_info_v10
, p_jg_info_v11
, p_jg_info_v12
, p_jg_info_v13
, p_jg_info_v14
, p_jg_info_v15
, p_jg_info_v16
, p_jg_info_v17
, p_jg_info_v18
, p_jg_info_v19
, p_jg_info_v20
, p_jg_info_v21
, p_jg_info_v22
, p_jg_info_v23
, p_jg_info_v24
, p_jg_info_v25
, p_jg_info_v26
, p_jg_info_v27
, p_jg_info_v28
, p_jg_info_v29
, p_jg_info_v30
, p_jg_info_v31
, p_jg_info_v32
, p_jg_info_v33
, p_jg_info_v34
, p_jg_info_v35
, p_jg_info_v36
, p_jg_info_v37
);
END InsertIntoGlobal;
SELECT application_column_name
INTO l_segment
FROM fnd_segment_attribute_values ,
gl_ledgers gl
WHERE id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_BALANCING'
AND application_id = 101
AND id_flex_num = gl.chart_of_accounts_id
AND gl.chart_of_accounts_id = p_coid
AND gl.ledger_id = p_ledger_id;
EXECUTE IMMEDIATE 'SELECT '||l_segment ||
' FROM gl_code_combinations '||
' WHERE code_combination_id = '||p_ccid
INTO bal_segment_value;
SELECT DISTINCT 'Receipt' RECEIPT,
ac.receipt_number RECEIPT_NUMBER,
ac.reversal_date REV_DATE,
decode(ac.status,'REV','Reversed',
'NSF','Reversed',
'STOP','Reversed') STATUS,
ac.receipt_date R_DATE ,
nvl(ac.amount,0) AMOUNT,
-1 * nvl(ac.amount,0) REV_AMOUNT,
at.tax_rate_code TAX_CODE,
ac.currency_code CURRENCT_CODE,
round(ac.amount * (1 - 1/(1 + at.PERCENTAGE_RATE/100.0)),2) R_TAX,
-1 * round(ac.amount * (1 - 1/(1 + at.PERCENTAGE_RATE/100.0)),2) REV_TAX,
aa.cash_receipt_id CASH_RECEIPT_ID
FROM ar_cash_receipts_all ac,
ar_receivable_applications_all aa,
zx_rates_b at
WHERE aa.cash_receipt_id = ac.cash_receipt_id
AND ac.org_id = aa.org_id
AND at.tax_rate_id = ac.vat_tax_id
AND ( ( P_REPORTING_LEVEL = 'LE' AND ac.legal_entity_id = P_LEGAL_ENTITY_ID)
OR ( P_REPORTING_LEVEL = 'LEDGER' AND ac.set_of_books_id = P_LEDGER_ID)
OR ( P_REPORTING_LEVEL = 'BSV' AND JG_ZZ_SUMMARY_AR_PKG.get_bsv(aa.code_combination_id,P_CHART_OF_ACC_ID,P_LEDGER_ID)= P_COMPANY )
)
AND (ac.vat_tax_id = P_TAX_RATE_ID OR P_TAX_RATE_ID IS NULL)
AND ( (aa.status = 'ACC')
OR (aa.applied_customer_trx_id IN(
SELECT trx1.customer_trx_id
FROM ra_customer_trx_all trx1, ra_cust_trx_types_all type1
WHERE trx1.cust_trx_type_id = type1.cust_trx_type_id
AND trx1.org_id = type1.org_id
AND type1.type ='DEP' ) ) )
AND (ac.receipt_date between
NVL(TO_DATE(P_GL_PERIOD_START_DATE ,'DD-MM-RRRR'),TO_DATE('01-01-1890' ,'DD-MM-RRRR'))
AND NVL(TO_DATE(P_GL_PERIOD_END_DATE,'DD-MM-RRRR'),TO_DATE('30-12-2099','DD-MM-RRRR'))
OR aa.cash_receipt_id IN
( SELECT distinct a1.cash_receipt_id
FROM ar_receivable_applications_all a1,
ar_receivable_applications_all a2,
hz_cust_accounts rc
WHERE
DECODE(SIGN(a1.amount_applied),-1,a1.gl_date,a1.gl_date) BETWEEN
NVL(TO_DATE(P_GL_PERIOD_START_DATE,'DD-MM-RRRR'),TO_DATE('01-01-1890','DD-MM-RRRR'))
AND nvl(TO_DATE(P_GL_PERIOD_END_DATE,'DD-MM-RRRR'), TO_DATE('30-12-2199','DD-MM-RRRR'))
AND a1.status = 'APP'
AND ( (a2.status = 'ACC')
OR (a2.applied_customer_trx_id IN(
SELECT trx2.customer_trx_id
FROM ra_customer_trx trx2, ra_cust_trx_types type2
WHERE trx2.cust_trx_type_id = type2.cust_trx_type_id
AND type2.type ='DEP' ) ) )
AND a1.cash_receipt_id = a2.cash_receipt_id
AND a1.org_id = a2.org_id))
ORDER BY ac.currency_code, at.tax_rate_code, ac.receipt_number,
SUBSTR(ac.receipt_date,1,10);
SELECT
DECODE(SIGN(aa.amount_applied),-1,'Unapplied','Applied') APPLIED,
aa.receivable_application_id RECEIVABLE_APPLICATION_ID,
aa.applied_customer_trx_id APPLIED_CUSTOMER_TRX_ID,
aa.gl_date A_DATE,
-1 * aa.amount_applied AMOUNT_APPLIED,
tr.invoice_currency_code INVOICE_CURRENCY_CODE,
at.tax_rate_code TAX_CODE,
-- aa.cash_receipt_id,
SUBSTR(hzp.party_name,1,20) CUSTOMER_NAME,
SUBSTR(csu.location,1,20) LOCATION,
at.tax_rate_id TAX_RATE_ID
-- ac.RECEIPT_NUMBER
FROM ar_receivable_applications_all aa,
zx_rates_b at,
ar_cash_receipts_all ac,
ra_customer_trx tr,
hz_cust_accounts rc,
hz_parties hzp,
hz_cust_acct_sites cs,
hz_cust_site_uses csu
WHERE aa.applied_customer_trx_id = tr.customer_trx_id
AND aa.cash_receipt_id = P_CASH_RECEIPT_ID
AND ac.cash_receipt_id = aa.cash_receipt_id
AND ac.org_id = aa.org_id
AND aa.status = 'APP'
AND rc.cust_account_id = tr.bill_to_customer_id
AND rc.party_id = hzp.party_id
AND rc.cust_account_id = cs.cust_account_id
AND cs.cust_acct_site_id = csu.cust_acct_site_id
AND tr.bill_to_site_use_id = csu.site_use_id
AND at.tax_rate_id = ac.vat_tax_id
AND aa.gl_date <= nvl(to_date(P_GL_PERIOD_END_DATE,'DD-MM-RRRR'),
to_date('31-12-4000','DD-MM-RRRR'))
ORDER BY aa.receivable_application_id;
SELECT tax_rate_code
FROM zx_rates_b
WHERE tax_rate_id = p_tax_rate_id;
SELECT JG.doc_seq_value SEQ_NUM
, JG.tax_rate TAX_RATE
, sum((NVL(JG.tax_amt,0)
+ NVL(JG.taxable_amt,0))) TRX_AMOUNT --bug 12325571
/* UT TEST change nvl(xxxamt__funcl_curr,0) to nvl(xxx_func_curr, xxx_amt) */
, sum((NVL(JG.tax_amt_funcl_curr, tax_amt)
+ NVL(JG.taxable_amt_funcl_curr, taxable_amt))) FUNC_AMOUNT --bug 12325571
, sum(NVL(JG.taxable_amt_funcl_curr, taxable_amt)) TAXABLE_AMOUNT --bug 12325571
, sum(NVL(JG.tax_amt_funcl_curr, tax_amt)) TAX_AMOUNT --bug 12325571
, JG.tax_invoice_date TAX_DATE
, JG.trx_date INVOICE_DATE
, JG.accounting_date GL_DATE
, DECODE(JG.trx_line_class
,'ADJUSTMENT',JG.applied_to_trx_number
, JG.trx_number) INVOICE_NUMBER
, JG.trx_id TRX_ID
, JG.billing_tp_name CUST_NAME
, JG.billing_tp_tax_reg_num TAX_REG_NUM
, JG.trx_currency_code CURR
, JG.tax_rate_code TAX_CODE
, JG.tax_rate_code_name TAX_DESC
, JG.tax_rate_vat_trx_type_desc VAT_DESC
, JG.tax_rate_vat_trx_type_code VAT_CODE
-- , JGR.tax_calendar_period PERIOD_NAME
, glp.period_name PERIOD_NAME
, JG.tax_rate_code TAX_RATE_CODE
, JG.tax_rate_code_vat_trx_type_mng VAT_TYPE
, glp.period_year PERIOD_YEAR
, JG.account_flexfield GL_ACcOUNT
, JG.trx_line_class CLASS_CODE
, JG.ledger_id LEDGER_ID
, JGVRE.enable_report_sequence_flag ENABLE_REPORT_SEQUENCE_FLAG
, glp.period_num PERIOD_NUM
FROM jg_zz_vat_trx_details JG
, jg_zz_vat_rep_status JGR
, jg_zz_vat_rep_entities JGVRE
, gl_periods glp
WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JGR.mapping_vat_rep_entity_id = JGVRE.vat_reporting_entity_id
AND JG.reporting_status_id = JGR.reporting_status_id
AND JGR.tax_calendar_period = P_PERIOD
AND glp.period_set_name = JGR.tax_calendar_name
AND glp.adjustment_period_flag = 'N' -- bug 14015901
AND JG.tax_invoice_date between glp.start_date and glp.end_date
AND JG.tax_rate_vat_trx_type_code IS NOT NULL
AND (JG.tax_rate_vat_trx_type_code <> P_EX_VAT_TRX_TYPE OR P_EX_VAT_TRX_TYPE IS NULL)
AND (JG.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE OR P_VAT_TRX_TYPE IS NULL)
AND JG.tax_rate_register_type_code = 'TAX'
AND JGR.source = 'AR'
/* UT CHANGE AND JG.extract_source_ledger = 'AR'
AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
-- added group by for bug 12325571
group by JG.doc_seq_value
, JG.tax_rate
, JG.tax_invoice_date
, JG.trx_date
, JG.accounting_date
, DECODE(JG.trx_line_class
,'ADJUSTMENT',JG.applied_to_trx_number
, JG.trx_number)
, JG.trx_id
, JG.billing_tp_name
, JG.billing_tp_tax_reg_num
, JG.trx_currency_code
, JG.tax_rate_code
, JG.tax_rate_code_name
, JG.tax_rate_vat_trx_type_desc
, JG.tax_rate_vat_trx_type_code
, glp.period_name
, JG.tax_rate_code
, JG.tax_rate_code_vat_trx_type_mng
, glp.period_year
, JG.account_flexfield
, JG.trx_line_class
, JG.ledger_id
, JGVRE.enable_report_sequence_flag
, glp.period_num
ORDER BY VAT_CODE
,PERIOD_YEAR DESC
,PERIOD_NUM DESC
,TRX_ID
,JG.tax_invoice_date
,JG.tax_rate_code;
SELECT JG.tax_rate_vat_trx_type_code VAT_TRX_TYPE_CODE,
JG.trx_id TRX_ID
FROM jg_zz_vat_trx_details JG
, jg_zz_vat_rep_status JGR
, jg_zz_vat_rep_entities JGVRE
, gl_periods glp
WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JGR.mapping_vat_rep_entity_id = JGVRE.vat_reporting_entity_id
AND JG.reporting_status_id = JGR.reporting_status_id
AND JGR.tax_calendar_period = P_PERIOD
AND glp.period_set_name = JGR.tax_calendar_name
AND glp.adjustment_period_flag = 'N' -- bug 14015901
AND JG.tax_invoice_date between glp.start_date and glp.end_date
AND JG.tax_rate_vat_trx_type_code IS NOT NULL
AND (JG.tax_rate_vat_trx_type_code <> P_EX_VAT_TRX_TYPE OR P_EX_VAT_TRX_TYPE IS NULL)
AND (JG.tax_rate_vat_trx_type_code = P_VAT_TRX_TYPE OR P_VAT_TRX_TYPE IS NULL)
AND JG.tax_rate_register_type_code = 'TAX'
AND JGR.source = 'AR'
/* UT CHANGE AND JG.extract_source_ledger = 'AR'
AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
ORDER BY VAT_TRX_TYPE_CODE
,PERIOD_YEAR DESC
,PERIOD_NUM DESC
,JG.tax_rate_code
,JG.tax_invoice_date;
SELECT JG.doc_seq_value SEQ_NUM
, JG.tax_rate TAX_RATE
, (NVL(JG.tax_amt,0)
+ NVL(JG.taxable_amt,0)) TRX_AMOUNT
, (NVL(JG.tax_amt_funcl_curr,0)
+ NVL(JG.taxable_amt_funcl_curr,0)) FUNC_AMOUNT
, NVL(JG.taxable_amt_funcl_curr,0) TAXABLE_AMOUNT
, NVL(JG.tax_amt_funcl_curr,0) TAX_AMOUNT
, JG.tax_invoice_date TAX_DATE
, JG.trx_date INVOICE_DATE
, JG.accounting_date GL_DATE
, DECODE(JG.trx_line_class
,'ADJUSTMENT',JG.applied_to_trx_number
, JG.trx_number) INVOICE_NUMBER
, JG.billing_tp_name CUST_NAME
, JG.billing_tp_tax_reg_num TAX_REG_NUM
, JG.trx_currency_code CURR
, JG.tax_rate_code TAX_CODE
, JG.tax_rate_code_name TAX_DESC
, JG.tax_rate_vat_trx_type_desc VAT_DESC
, JG.tax_rate_vat_trx_type_code VAT_TYPE
, JGR.tax_calendar_period PERIOD_NAME
, JG.tax_rate_code_vat_trx_type_mng VAT_CODE
, JGR.tax_calendar_year PERIOD_YEAR
, JG.account_flexfield GL_ACcOUNT
, JG.trx_line_class CLASS_CODE
, JG.trx_number RECEIPT_NUMBER
, JG.ar_cash_receipt_reverse_date REV_DATE
, JG.ar_cash_receipt_reverse_status STATUS
FROM jg_zz_vat_trx_details JG
, jg_zz_vat_rep_status JGR
WHERE JGR.vat_reporting_entity_id = P_VAT_REP_ENTITY_ID
AND JG.reporting_status_id = JGR.reporting_status_id
AND JGR.tax_calendar_period = P_PERIOD
AND JG.tax_rate_register_type_code = 'TAX'
AND JGR.source = 'AR'
/* UT CHANGE AND JG.extract_source_ledger = 'AR'
AND JG.tax_invoice_date BETWEEN JGR.period_start_date AND JGR.period_end_date */
;
SELECT COUNT(1) C_NO_DATA_COUNT
FROM jg_zz_vat_trx_gt
WHERE jg_info_v14 <> P_EX_VAT_TRX_TYPE
OR P_EX_VAT_TRX_TYPE IS NULL
;
SELECT 1
FROM ar_system_parameters
WHERE accounting_method = 'CASH';
SELECT entity_identifier
FROM jg_zz_vat_rep_entities
WHERE vat_reporting_entity_id=p_vat_rep_entity_id;
v_is_seq_updated VARCHAR2(1) := 'N';
select meaning
into l_reporting_mode_mng
from fnd_lookups
where lookup_type = 'JGZZ_REPORT_TYPE'
and lookup_code = decode(l_reporting_mode, 'COPY', 'R','FINAL','F','P');
SELECT precision
INTO l_precision
FROM fnd_currencies
WHERE currency_code = l_func_curr_code;
if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'INSERT INTO GLOBAL'); end if;
InsertIntoGlobal(
p_jg_info_v20 => l_entity_identifier -- entity_identifier
,p_jg_info_v21 => l_func_curr_code -- curr_code
,p_jg_info_v22 => l_company_name -- l_rep_legal_entity -- entity_name
,p_jg_info_v23 => l_registration_number -- l_taxpayer_id -- taxpayer_id
,p_jg_info_v24 => l_company_name -- company_name
,p_jg_info_v25 => l_trx_num -- registration_number
,p_jg_info_v26 => l_country -- country
,p_jg_info_v27 => l_address1 -- address1
,p_jg_info_v28 => l_address2 -- address2
,p_jg_info_v29 => l_address3 -- address3
,p_jg_info_v30 => l_address4 -- address4
,p_jg_info_v31 => l_city -- city
,p_jg_info_v32 => l_postal_code -- postal_code
,p_jg_info_v33 => l_contact -- contact
,p_jg_info_v34 => l_phone_number -- phone_number
,p_jg_info_v35 => l_reporting_mode_mng -- reporting mode meaning (bug 14024932)
,p_jg_info_v37 => l_trx_num -- trx_num
,p_jg_info_d4 => l_period_start_date -- period_start_date
,p_jg_info_d5 => l_period_end_date -- period_end_date
,p_jg_info_n26 => l_rep_legal_entity_id -- legalentity_id
,p_jg_info_n27 => l_period_year -- period_year
,p_jg_info_v19 => l_registration_number -- company tax Payer Id
,p_jg_info_v18 => l_tax_rate_code -- tax rate code
,p_jg_info_n25 => l_precision -- currency precision
,p_jg_info_v36 => 'H' -- Header record indicator
);
if gv_debug then FND_FILE.PUT_LINE(FND_FILE.LOG,'insert into global'); end if;
InsertIntoGlobal(
p_jg_info_v1 => r_ger_receipt.receipt
, p_jg_info_v2 => r_ger_receipt.status
, p_jg_info_v3 => r_ger_receipt.tax_code
, p_jg_info_v4 => r_ger_receipt.currenct_code
, p_jg_info_v10 => r_ger_receipt.receipt_number
, p_jg_info_d1 => r_ger_receipt.rev_date
, p_jg_info_d2 => r_ger_receipt.r_date
, p_jg_info_n1 => r_ger_receipt.amount
, p_jg_info_n2 => l_zero
, p_jg_info_n3 => r_ger_receipt.rev_amount
, p_jg_info_n4 => r_ger_receipt.r_tax
, p_jg_info_n5 => r_ger_receipt.rev_tax
, p_jg_info_n6 => r_ger_receipt.cash_receipt_id
, p_jg_info_n11 => l_rd
, p_jg_info_n14 => l_new_r_tax
, p_jg_info_n15 => l_new_amount
, p_jg_info_n16 => l_new_rev_tax
, p_jg_info_n17 => l_new_rev_amount
-- , p_jg_info_n18 => l_new_receipt
-- , p_jg_info_n19 => l_new_reversal
, p_jg_info_v15 => l_new_receipt /* modified during UT TEST */
, p_jg_info_v16 => l_new_reversal /* modified during UT TEST */
, p_jg_info_v5 => r_ger_appl.applied
, p_jg_info_v6 => r_ger_appl.invoice_currency_code
, p_jg_info_v7 => r_ger_appl.tax_code
, p_jg_info_v8 => r_ger_appl.customer_name
, p_jg_info_v9 => r_ger_appl.location
, p_jg_info_v13 => l_vat
, p_jg_info_v14 => l_new_application
, p_jg_info_n7 => r_ger_appl.receivable_application_id
, p_jg_info_n8 => r_ger_appl.applied_customer_trx_id
, p_jg_info_n9 => r_ger_appl.amount_applied
, p_jg_info_n20 => l_new_au
, p_jg_info_n21 => l_new_aa
, p_jg_info_n22 => l_app_vat
, p_jg_info_n23 => l_new_app_vat
, p_jg_info_n24 => l_unapp_vat
, p_jg_info_n25 => l_new_unapp_vat
, p_jg_info_d3 => r_ger_appl.a_date
, p_jg_info_v30 => 'JEDEDVOR'
);
/* Will insert the Onc-Account receipt details,which is not applied */
IF l_receipt_application = 0 THEN
InsertIntoGlobal(
p_jg_info_v1 => r_ger_receipt.receipt
, p_jg_info_v2 => r_ger_receipt.status
, p_jg_info_v3 => r_ger_receipt.tax_code
, p_jg_info_v4 => r_ger_receipt.currenct_code
, p_jg_info_v10 => r_ger_receipt.receipt_number
, p_jg_info_d1 => r_ger_receipt.rev_date
, p_jg_info_d2 => r_ger_receipt.r_date
, p_jg_info_n1 => r_ger_receipt.amount
, p_jg_info_n2 => l_zero
, p_jg_info_n3 => r_ger_receipt.rev_amount
, p_jg_info_n4 => r_ger_receipt.r_tax
, p_jg_info_n5 => r_ger_receipt.rev_tax
, p_jg_info_n6 => r_ger_receipt.cash_receipt_id
, p_jg_info_n11 => l_rd
, p_jg_info_n14 => l_new_r_tax
, p_jg_info_n15 => l_new_amount
, p_jg_info_n16 => l_new_rev_tax
, p_jg_info_n17 => l_new_rev_amount
-- , p_jg_info_n18 => l_new_receipt
-- , p_jg_info_n19 => l_new_reversal
, p_jg_info_v15 => l_new_receipt /* modified during UT TEST */
, p_jg_info_v16 => l_new_reversal /* modified during UT TEST */
, p_jg_info_v5 => NULL
, p_jg_info_v6 => NULL
, p_jg_info_v7 => NULL
, p_jg_info_v8 => NULL
, p_jg_info_v9 => NULL
, p_jg_info_v13 => NULL
, p_jg_info_v14 => NULL
, p_jg_info_n7 => NULL
, p_jg_info_n8 => NULL
, p_jg_info_n9 => NULL
, p_jg_info_n20 => NULL
, p_jg_info_n21 => NULL
, p_jg_info_n22 => NULL
, p_jg_info_n23 => NULL
, p_jg_info_n24 => NULL
, p_jg_info_n25 => NULL
, p_jg_info_d3 => NULL
, p_jg_info_v30 => 'JEDEDVOR'
);
InsertIntoGlobal(
p_jg_info_n1 => r_euar.seq_num
, p_jg_info_n2 => r_euar.tax_rate
, p_jg_info_n3 => r_euar.trx_amount
, p_jg_info_n4 => r_euar.func_amount
, p_jg_info_n5 => r_euar.taxable_amount
, p_jg_info_n6 => r_euar.tax_amount
, p_jg_info_n7 => r_euar.period_year
, p_jg_info_n8 => r_euar.ledger_id
, p_jg_info_n9 => r_euar.period_num
, p_jg_info_d1 => r_euar.tax_date
, p_jg_info_d2 => r_euar.invoice_date
, p_jg_info_d3 => r_euar.gl_date
, p_jg_info_v1 => r_euar.invoice_number
, p_jg_info_n25 => r_euar.trx_id
, p_jg_info_v2 => r_euar.cust_name
, p_jg_info_v3 => r_euar.tax_reg_num
, p_jg_info_v4 => r_euar.curr
, p_jg_info_v5 => r_euar.tax_code
, p_jg_info_v6 => r_euar.vat_code
, p_jg_info_v7 => r_euar.tax_desc
, p_jg_info_v8 => r_euar.vat_desc
, p_jg_info_v9 => r_euar.period_name
, p_jg_info_v10 => r_euar.gl_account
, p_jg_info_v14 => r_euar.vat_type
, p_jg_info_v15 => r_euar.enable_report_sequence_flag
, p_jg_info_v17 => r_euar.tax_rate_code
, p_jg_info_v18 => r_euar.class_code
);
UPDATE jg_zz_vat_trx_gt
SET jg_info_n20 = r_euar_data_count.c_no_data_count;
SELECT distinct JG_INFO_V40 INTO v_is_seq_updated FROM JG_ZZ_VAT_TRX_GT T1
WHERE T1.jg_info_n25 = r_seq_impl.trx_id
AND T1.jg_info_v6 = r_seq_impl.VAT_TRX_TYPE_CODE;
IF nvl(v_is_seq_updated,'N') <> 'Y' THEN
v_count := v_count+1;
UPDATE JG_ZZ_VAT_TRX_GT SET jg_info_n1 = v_count ,
jg_info_v40 = 'Y'
WHERE jg_info_n25 = r_seq_impl.trx_id
AND jg_info_v6 = r_seq_impl.VAT_TRX_TYPE_CODE;
SELECT jivl.vat_aggregate_limit_amt
INTO g_vat_agg_limit
FROM je_il_vat_limits jivl,
jg_zz_vat_rep_status jzvrs
WHERE jzvrs.vat_reporting_entity_id = p_vat_rep_entity_id
AND jzvrs.tax_calendar_period = p_period
AND jzvrs.tax_calendar_name = jivl.period_set_name
AND jivl.period_name = p_period
AND rownum = 1;
We should call this procedure before selection process ran i.e. Before the TRL call (as in 11i)
Hence commenting the call to procedure.
*/
-- tax_date_maintenance_program(l_period_end_date);