[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT v1.reporting_code_name, v1.reporting_code_char_value
FROM
(SELECT v.reporting_code_name, v.reporting_code_char_value
FROM (
SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 1 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.tax_line_id = p_tax_line_id
AND rep_types.reporting_type_code = 'INTRA_EU_VAT_TRANSACTION_TYPE'
AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
AND rep_assoc.entity_id = NVL(zxl.direct_rate_result_id, zxl.rate_result_id)
AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND ROWNUM=1
UNION
SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 2 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.tax_line_id = p_tax_line_id
AND rep_types.reporting_type_code = 'INTRA_EU_VAT_TRANSACTION_TYPE'
AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
AND rep_assoc.entity_id = zxl.tax_rate_id
AND rep_assoc.entity_code = 'ZX_RATES'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND ROWNUM = 1
UNION
SELECT rep_codes.reporting_code_name, rep_codes.reporting_code_char_value, 3 order_num
FROM zx_report_codes_assoc rep_assoc,
zx_reporting_types_b rep_types,
zx_reporting_codes_vl rep_codes,
zx_lines zxl
WHERE zxl.tax_line_id = p_tax_line_id
AND rep_types.reporting_type_code = 'INTRA_EU_VAT_TRANSACTION_TYPE'
AND rep_types.reporting_type_id = rep_assoc.reporting_type_id
AND rep_assoc.entity_id = zxl.status_result_id
AND rep_assoc.entity_code = 'ZX_PROCESS_RESULTS'
AND zxl.trx_date BETWEEN rep_assoc.effective_from AND
NVL(rep_assoc.effective_to, zxl.trx_date)
AND rep_assoc.reporting_code_id = rep_codes.reporting_code_id
AND ROWNUM = 1
) v
WHERE v.reporting_code_name IS NOT NULL
ORDER BY v.order_num,v.reporting_code_name) v1
WHERE ROWNUM = 1;
| This procedure calls the API to select the JE specific data from |
| JE receivables tables. |
| |
| Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
| |
| Parameters : |
| IN: P_TRL_GLOBAL_VARIABLES_REC ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE|
| |
| |
| MODIFICATION HISTORY |
| 13-FEB-2006 RJREDDY Created |
| |
| |
+===========================================================================*/
PROCEDURE POPULATE_JE_AR
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
IS
TYPE ATTRIBUTE1_TBL is TABLE OF
ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
SELECT detail_tax_line_id,
itf1.tax_status_code,
itf1.trx_business_category,
itf1.document_sub_type,
itf1.TAX_RATE_ID,
(SELECT assoc.reporting_code_char_value
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND itf1.TAX_RATE_ID = assoc.entity_id
AND assoc.entity_code = 'ZX_RATES'
AND (assoc.EFFECTIVE_TO is null
or assoc.EFFECTIVE_TO >= DECODE(itf1.EVENT_CLASS_CODE,'CREDIT_MEMO',itf1.TAX_DETERMINE_DATE,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
)
AND rep_type.reporting_type_code IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
),
(SELECT assoc.reporting_code_char_value
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND itf1.TAX_RATE_ID = assoc.entity_id
AND assoc.entity_code = 'ZX_RATES'
AND DECODE(itf1.EVENT_CLASS_CODE,'CREDIT_MEMO',itf1.TAX_DETERMINE_DATE,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
BETWEEN assoc.EFFECTIVE_FROM AND
NVL(assoc.EFFECTIVE_TO,
DECODE(itf1.EVENT_CLASS_CODE,'CREDIT_MEMO',itf1.TAX_DETERMINE_DATE,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
)
AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'),
itf1.TAX_LINE_ID
FROM zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 222
AND itf1.extract_source_ledger = 'AR'
AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO','APP','ADJ')
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
SELECT assoc.reporting_code_char_value
FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND assoc.entity_id = p_entity_id
AND assoc.entity_code = 'ZX_RATES'
AND rep_type.reporting_type_code = p_reporting_type ;
UPDATE zx_rep_trx_detail_t dtl
set dtl.tax_line_id = (select min (tax_line_id)
from zx_lines lines
where lines.application_id = 222
and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
and lines.tax_rate_id = dtl.tax_rate_id
and nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
)
WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
and dtl.APPLICATION_ID =222
and dtl.APPLIED_FROM_ENTITY_CODE = 'APP';
UPDATE zx_rep_trx_detail_t dtl
set dtl.tax_line_id = (select min (tax_line_id)
from zx_lines lines
where lines.application_id = 222
and lines.trx_id = dtl.ADJUSTED_DOC_TRX_ID
and lines.trx_line_id = dtl.APPLIED_TO_TRX_LINE_ID
AND lines.tax_rate_id = dtl.tax_rate_id
AND nvl(lines.hq_estb_reg_number,fnd_api.g_miss_char) =
P_TRL_GLOBAL_VARIABLES_REC.FIRST_PARTY_TAX_REG_NUM
)
WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
and dtl.EXTRACT_SOURCE_LEDGER = 'AR'
and dtl.APPLICATION_ID =222
and dtl.EVENT_CLASS_CODE = 'ADJ';
SELECT detail_tax_line_id
BULK COLLECT INTO l_detail_tax_line_id_tbl
FROM zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 222
AND itf1.extract_source_ledger = 'AR'
AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO','APP','ADJ')
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute9,
attribute11,
attribute12,
attribute13,
attribute23,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
SELECT zx_rep_trx_jx_ext_t_s.nextval,
itf1.detail_tax_line_id,
CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
(SELECT decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.MODELO347PR', ra_cust.global_attribute2,
'JE.ES.ARXTWMAI.MODELO415_347PR', ra_cust.global_attribute2,
NULL)
FROM ra_customer_trx_all ra_cust
WHERE ra_cust.customer_trx_id = itf1.trx_id
AND ra_cust.set_of_books_id = itf1.ledger_id)
ELSE NULL
END ,
CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
(SELECT decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute3,
'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute3,
NULL)
FROM ra_customer_trx_all ra_cust
WHERE ra_cust.customer_trx_id = itf1.trx_id
AND ra_cust.set_of_books_id = itf1.ledger_id)
ELSE NULL
END ,
CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
(SELECT decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute4,
'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute4,
NULL)
FROM ra_customer_trx_all ra_cust
WHERE ra_cust.customer_trx_id = itf1.trx_id
AND ra_cust.set_of_books_id = itf1.ledger_id )
ELSE NULL
END ,
CASE WHEN (itf1.entity_code = 'TRANSACTIONS' AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO')) THEN
(SELECT decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute5,
'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute5,
NULL)
FROM ra_customer_trx_all ra_cust
WHERE ra_cust.customer_trx_id = itf1.trx_id
AND ra_cust.set_of_books_id = itf1.ledger_id )
ELSE NULL
END ,
substr(itf1.trx_business_category,(instr(itf1.trx_business_category,'MOD',1,1)+3),
length(itf1.trx_business_category)),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
FROM zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 222
AND itf1.extract_source_ledger = 'AR'
AND itf1.event_class_code IN ('INVOICE','DEBIT_MEMO','CREDIT_MEMO','APP','ADJ')
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
UPDATE ZX_REP_TRX_JX_EXT_T
SET
TAX_STATUS_MNG = L_TAX_STATUS_CODE_TBL(i),
TRX_BUSINESS_CATEGORY_MNG = L_TRX_BUSINESS_CATEGORY_TBL(i),
DOCUMENT_SUB_TYPE_MNG = L_DOCUMENT_SUB_TYPE_TBL(i),
ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
attribute25 = L_ATTRIBUTE25_TBL(i), --Bug 5510822
attribute26 = L_ATTRIBUTE26_TBL(i), --EMEA Changes
attribute27 = L_ATTRIBUTE27_TBL(i),
attribute28 = L_ATTRIBUTE28_TBL(i)
WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i) and
request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
| This procedure calls the API to select the JE specific data from |
| JE payables tables. Currently only JE_LOOKUP_INFO plug-in is called |
| inside. |
| |
| Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
| |
| Parameters : |
| |
| |
| MODIFICATION HISTORY |
| 13-FEB-2006 RJREDDY Created |
| |
| |
+===========================================================================*/
PROCEDURE POPULATE_JE_AP
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
IS
TYPE ATTRIBUTE1_TBL is TABLE OF
ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
SELECT
detail_tax_line_id,
fsp.vat_country_code,
tax_rate_id ,
decode ( hr_loc.global_attribute_category,
'JE.ES.PERWSLOC.PRL_NO', hr_loc.global_attribute1,
NULL ),
decode ( hr_loc.global_attribute_category,
'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute1,
NULL ),
decode ( hr_loc.global_attribute_category,
'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute2,
NULL ),
decode ( hr_loc.global_attribute_category,
'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute3,
NULL ),
decode ( hr_loc.global_attribute_category,
'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute4,
NULL ),
decode ( hr_loc.global_attribute_category,
'JE.ES.PERWSLOC.PRL_YES', hr_loc.global_attribute5,
NULL ),
(SELECT assoc.reporting_code_char_value FROM
zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND itf1.TAX_RATE_ID = assoc.entity_id
AND assoc.entity_code = 'ZX_RATES'
AND (assoc.EFFECTIVE_TO is null
or assoc.EFFECTIVE_TO >= NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
AND rep_type.reporting_type_code IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
),
(SELECT assoc.reporting_code_char_value FROM
zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND itf1.TAX_RATE_ID = assoc.entity_id
AND assoc.entity_code = 'ZX_RATES'
AND NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
BETWEEN assoc.EFFECTIVE_FROM AND
NVL(assoc.EFFECTIVE_TO,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE'),
itf1.tax_line_id
FROM financials_system_params_all fsp,
hr_locations_all hr_loc,
zx_rep_trx_detail_t itf1
WHERE
itf1.application_id = 200
AND itf1.entity_code = 'AP_INVOICES'
AND itf1.ledger_id = fsp.set_of_books_id
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND itf1.ship_to_location_id = hr_loc.ship_to_location_id(+) ;
SELECT assoc.reporting_code_char_value
FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND assoc.entity_id = p_entity_id
AND assoc.entity_code = 'ZX_RATES'
AND rep_type.reporting_type_code = p_reporting_type ;
SELECT detail_tax_line_id
BULK COLLECT INTO l_tax_line_id_tbl
FROM zx_rep_trx_detail_t itf1
WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
tax_status_mng,
trx_business_category_mng,
document_sub_type_mng,
attribute8,
attribute11,
attribute12,
attribute20,
attribute21,
attribute22,
attribute23,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
SELECT zx_rep_trx_jx_ext_t_s.nextval,
itf1.detail_tax_line_id,
itf1.tax_status_code,
itf1.trx_business_category,
itf1.document_sub_type,
decode ( ap_inv.global_attribute_category,
'JE.SK.APXINWKB.INVOICE_INFO', ap_inv.global_attribute2,
'JE.HU.APXINWKB.TAX_DATE', ap_inv.global_attribute2,
NULL ),
decode ( ap_inv.global_attribute_category,
'JE.ES.APXINWKB.INVOICE_INFO', ap_inv.global_attribute2,
'JE.ES.APXINWKB.MODELO349', ap_inv.global_attribute2,
NULL ),
decode ( ap_inv.global_attribute_category,
'JE.ES.APXINWKB.INVOICE_INFO', ap_inv.global_attribute3,
'JE.ES.APXINWKB.MODELO349', ap_inv.global_attribute3,
NULL ),
ap_inv.source,
decode ( ap_inv.global_attribute_category,
'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE3,
'JE.IL.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE3,
NULL ),
decode ( ap_inv.global_attribute_category,
'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE4,
'JE.IL.APXINWKB.INVOICE_INFO', ap_inv.GLOBAL_ATTRIBUTE4,
NULL ),
substr(itf1.trx_business_category,(instr(itf1.trx_business_category,'MOD',1,1)+3),
length(itf1.trx_business_category)),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
FROM ap_invoices_all ap_inv,
zx_rep_trx_detail_t itf1
WHERE itf1.trx_id = ap_inv.invoice_id
and itf1.application_id = 200
and itf1.entity_code = 'AP_INVOICES'
and itf1.ledger_id = ap_inv.set_of_books_id
and itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
'No. of rows inserted into ZX_REP_TRX_JX_EXT_T : '||to_char(SQL%ROWCOUNT) );
UPDATE ZX_REP_TRX_JX_EXT_T
SET ATTRIBUTE10 = L_ATTRIBUTE10_TBL(i),
ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
ATTRIBUTE14 = L_ATTRIBUTE14_TBL(i),
ATTRIBUTE15 = L_ATTRIBUTE15_TBL(i),
ATTRIBUTE16 = L_ATTRIBUTE16_TBL(i),
ATTRIBUTE17 = L_ATTRIBUTE17_TBL(i),
ATTRIBUTE18 = L_ATTRIBUTE18_TBL(i),
ATTRIBUTE19 = L_ATTRIBUTE19_TBL(i),
attribute25 = l_attribute25_tbl(i), --Bug 5510822
attribute26 = l_attribute26_tbl(i),--Emea Changes
attribute27 = l_attribute27_tbl(i),
attribute28 = l_attribute28_tbl(i)
WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i)
and request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
| This procedure calls the API to select the JE specific data from |
| JE GL tables. |
| |
| Called from |
| |
| Parameters : |
| IN: P_TRL_GLOBAL_VARIABLES_REC ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE|
| |
| |
| MODIFICATION HISTORY |
| 22-MAY-2006 VSDOSHI Created |
| |
| |
+===========================================================================*/
PROCEDURE POPULATE_JE_GL
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
IS
--Bug 5636632
TYPE ATTRIBUTE1_TBL is TABLE OF
ZX_REP_TRX_JX_EXT_T.ATTRIBUTE1%TYPE INDEX BY BINARY_INTEGER;
SELECT assoc.reporting_code_char_value
FROM zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND assoc.entity_id = p_entity_id
AND assoc.entity_code = 'ZX_RATES'
AND rep_type.reporting_type_code = p_reporting_type ;
SELECT detail_tax_line_id,
itf1.tax_status_code,
itf1.trx_business_category,
itf1.document_sub_type,
itf1.TAX_RATE_ID,
(SELECT assoc.reporting_code_char_value FROM
zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND itf1.TAX_RATE_ID = assoc.entity_id
AND assoc.entity_code = 'ZX_RATES'
AND (assoc.EFFECTIVE_TO is null
or assoc.EFFECTIVE_TO >= NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
AND rep_type.reporting_type_code IN ('CZ_TAX_ORIGIN','HU_TAX_ORIGIN','PL_TAX_ORIGIN','CH_VAT_REGIME')
) ,
(SELECT assoc.reporting_code_char_value FROM
zx_reporting_types_b rep_type,zx_report_codes_assoc assoc
WHERE rep_type.reporting_type_id = assoc.reporting_type_id
AND itf1.TAX_RATE_ID = assoc.entity_id
AND assoc.entity_code = 'ZX_RATES'
AND NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
BETWEEN assoc.EFFECTIVE_FROM AND
NVL(assoc.EFFECTIVE_TO,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TAX_INVOICE_DATE_HIGH,
itf1.tax_invoice_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.TRX_DATE_HIGH,
itf1.trx_date,
NVL2(P_TRL_GLOBAL_VARIABLES_REC.GL_DATE_HIGH,
itf1.gl_date,
sysdate
)
)
)
)
AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
FROM zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 101
AND itf1.entity_code = 'GL_JE_LINES'
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
'Before insertion into ZX_REP_TRX_JX_EXT_T - 1');
SELECT detail_tax_line_id
BULK COLLECT INTO l_detail_tax_line_id_tbl
FROM zx_rep_trx_detail_t itf1
WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
'Count Before insertion - 1'||to_char(l_detail_tax_line_id_tbl.count));
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
attribute24,
request_id)
SELECT zx_rep_trx_jx_ext_t_s.nextval,
itf1.detail_tax_line_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
itf1.tax_type_code, --gjl.tax_type_code,
P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
FROM zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 101
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND EXISTS (SELECT 1
FROM gl_je_lines gjl
WHERE gjl.je_header_id = itf1.trx_id
AND gjl.je_line_num = itf1.trx_line_id
);
'No. of rows inserted into ZX_REP_TRX_JX_EXT_T - 1'||to_char(sql%ROWCOUNT));
UPDATE ZX_REP_TRX_JX_EXT_T
SET
TAX_STATUS_MNG = L_TAX_STATUS_CODE_TBL(i),
TRX_BUSINESS_CATEGORY_MNG = L_TRX_BUSINESS_CATEGORY_TBL(i),
DOCUMENT_SUB_TYPE_MNG = L_DOCUMENT_SUB_TYPE_TBL(i),
ATTRIBUTE1 = L_ATTRIBUTE1_TBL(i),
ATTRIBUTE2 = L_ATTRIBUTE2_TBL(i),
ATTRIBUTE3 = L_ATTRIBUTE3_TBL(i),
ATTRIBUTE4 = L_ATTRIBUTE4_TBL(i),
ATTRIBUTE5 = L_ATTRIBUTE5_TBL(i),
ATTRIBUTE6 = L_ATTRIBUTE6_TBL(i),
ATTRIBUTE7 = L_ATTRIBUTE7_TBL(i),
attribute25 = L_ATTRIBUTE25_TBL(i),
attribute26 = L_ATTRIBUTE26_TBL(i)
WHERE detail_tax_line_id = L_DETAIL_TAX_LINE_ID_TBL(i) and
request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;