[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| 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 > 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 (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
FROM zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 222
AND itf1.entity_code = 'TRANSACTIONS'
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 ;
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;
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,
decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.MODELO347PR', ra_cust.global_attribute2,
'JE.ES.ARXTWMAI.MODELO415_347PR', ra_cust.global_attribute2,
NULL),
decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute3,
'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute3,
NULL),
decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute4,
'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute4,
NULL),
decode (ra_cust.global_attribute_category,
'JE.ES.ARXTWMAI.INVOICE_INFO',ra_cust.global_attribute5,
'JE.ES.ARXTWMAI.MODELO349', ra_cust.global_attribute5,
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 ra_customer_trx_all ra_cust,
zx_rep_trx_detail_t itf1
WHERE itf1.trx_id = ra_cust.customer_trx_id
AND itf1.application_id = 222
AND itf1.entity_code = 'TRANSACTIONS'
AND itf1.ledger_id = ra_cust.set_of_books_id
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
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 > 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 (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > sysdate)
AND rep_type.reporting_type_code= 'EMEA_VAT_REPORTING_TYPE')
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,
-- attribute3,
-- attribute13,
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 ),
-- decode ( ap_inv.global_attribute_category,
-- 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.global_attribute3,
-- NULL ),
-- decode ( ap_inv.global_attribute_category,
-- 'JE.CZ.APXINWKB.INVOICE_INFO', ap_inv.global_attribute4,
-- 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
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 > 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 (assoc.EFFECTIVE_TO is null or assoc.EFFECTIVE_TO > 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,
gjl.tax_type_code,
P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
FROM gl_je_lines gjl ,
zx_rep_trx_detail_t itf1
WHERE itf1.application_id = 101
AND itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND itf1.trx_id = gjl.je_header_id
AND itf1.trx_line_id = gjl.je_line_num;
'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;