[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_PRINT_DATE
(
P_INVOICE_ID IN NUMBER
);
| by selecting only the records associated with JA specific lookup type |
| all unnecessary rows in ZX_REP_TRX_DETAIL_T table are deleted |
| |
| Called from AR_TAX_EXTRACT.EXECUTE_SQL. |
| |
| Parameters : |
| IN : p_report_name varchar2 Required |
| p_request_id number Required |
| |
| MODIFICATION HISTORY |
| 27-Oct-03 Asako Takahashi created |
| |
| |
+===========================================================================*/
PROCEDURE FILTER_JA_AR_TAX_LINES
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
/* Following parameters are removed since we now access global variables directly
(
P_REPORT_NAME IN varchar2,
P_REQUEST_ID IN number,
P_EXP_CERT_DATE_FROM IN date,
P_EXP_CERT_DATE_TO IN date,
P_EXP_METHOD IN varchar2,
P_TRX_SOURCE_ID IN number,
P_INCLUDE_REFERENCED_SOURCE IN varchar2
)
*/
IS
l_delete_sql_string varchar2(3000);
l_insert_sql_string varchar2(1000);
SELECT batch_source_type, TO_NUMBER(global_attribute1)
INTO l_loc_trx_src_type, l_referenced_trx_src_id
FROM ra_batch_sources_all
WHERE batch_source_Id = P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
l_delete_sql_string:=
'DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and NOT(
substrb(DET.DOCUMENT_SUB_TYPE,10,2) in (''31'',''32'',''33'',''34'',''35'',''36'',''37'') and
DET.TRX_LINE_CLASS IN (''INVOICE'', ''CREDIT_MEMO'') and
DET.DOC_EVENT_STATUS <> ''CANCELLED'' and ( ' ||
lp_where_trx_source_id || '' || lp_where_inc_reference_source || '))';
'l_delete_sql_string::'||l_delete_sql_string);
EXECUTE IMMEDIATE l_delete_sql_string;
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and NOT(
substrb(DET.DOCUMENT_SUB_TYPE,10,2) in ('31','32','33','34','35','36','37') and
DET.TRX_LINE_CLASS IN ('INVOICE', 'CREDIT_MEMO') and
DET.DOC_EVENT_STATUS <> 'CANCELLED' and ( DET.TRX_BATCH_SOURCE_ID = P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID and 1 = 1));
'l_delete_sql_string::'||l_delete_sql_string);
SELECT batch_source_type, TO_NUMBER(global_attribute1)
INTO l_loc_trx_src_type, l_referenced_trx_src_id
FROM ra_batch_sources
WHERE batch_source_Id = P_TRL_GLOBAL_VARIABLES_REC.BATCH_SOURCE_ID;
l_delete_sql_string:=
'DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
NOT EXISTS
(
SELECT 1 FROM ra_batch_sources_all rbs,
ra_customer_trx_all rct
WHERE
rbs.batch_source_id = DET.TRX_BATCH_SOURCE_ID and
rct.batch_source_id = rbs.batch_source_id and
/* selecting trx with GUI TYPE 31,32,35,36,37 */
(
substrb(DET.DOCUMENT_SUB_TYPE,10,2) in (''31'',''32'',''35'',''36'',''37'') and
(';
l_delete_sql_string := l_delete_sql_string||' rbs.global_attribute_category = ''JA.TW.RAXSUMSC.BATCH_SOURCES'' and
rbs.global_attribute3 IS NOT NULL and (' ||
lp_where_trx_source_id || ' or ' ||
lp_where_inc_reference_source || ')
'; -- amit removed one bracket
l_delete_sql_string := l_delete_sql_string||'rbs.global_attribute3 IS NULL and '||
lp_where_trx_source_id || '
)';
l_delete_sql_string := l_delete_sql_string||' )
)
or
/* selecting trx with GUI TYPE 33,34 */
(substrb(DET.DOCUMENT_SUB_TYPE,10,2) in (''33'',''34'') and (' ||
lp_where_trx_source_id || ' or ' ||
lp_where_inc_reference_source || ')
)
or
/* selecting trx with GUI TYPE NULL */
(substrb(DET.DOCUMENT_SUB_TYPE,10,2) = '' '' and
rct.global_attribute_category = ''JA.TW.ARXTWMAI.RA_CUSTOMER_TRX'' and
rct.global_attribute9 IS NOT NULL and
DET.DOC_EVENT_STATUS <> ''CANCELLED'' and '|| -- Amit changed to <>
lp_where_trx_source_id || '
)
-- )
)';
EXECUTE IMMEDIATE l_delete_sql_string;
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
l_delete_sql_string:=
'DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
NOT EXISTS
(SELECT 1
FROM
RA_CUSTOMER_TRX_ALL rct,
JA_LOOKUPS ja1,
JA_LOOKUPS ja2,
JA_LOOKUPS ja3
WHERE
DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
DET.TRX_ID = rct.customer_trx_id and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN (''31'',''32'',''33'',''34'',''35'',''36'',''37'') and
DET.TRX_LINE_CLASS IN (''INVOICE'',''CREDIT_MEMO'') ' ||
lp_where_export_date_from ||
lp_where_export_date_to ||
lp_where_export_method|| ')';
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
EXECUTE IMMEDIATE l_delete_sql_string;
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT (DET.TRX_LINE_CLASS = 'CREDIT_MEMO' and
DET.EXTRACT_SOURCE_LEDGER = 'AR' and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN ('33','34'));
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
/* DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT
(
(DET.EXTRACT_SOURCE_LEDGER = 'AR' and
DET.TRX_LINE_CLASS in ('INVOICE', 'CREDIT_MEMO') and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN ('31','32','33','34','35','36','37') and
((DET.DOC_EVENT_STATUS = 'CANCELLED' and
DET.POSTED_FLAG = 'Y' ) or
DET.DOC_EVENT_STATUS = 'VALIDATED')
)
or
(DET.EXTRACT_SOURCE_LEDGER = 'AP' and
DET.POSTED_FLAG = 'Y' and
DET.TRX_LINE_CLASS in ('STANDARD INVOICES','AP_CREDIT_MEMO', 'AP_DEBIT_MEMO') and
substrb(DET.DOCUMENT_SUB_TYPE, 10,2) IN ('21','22', '23','24','25','26','27','28'))
);
DELETE
FROM zx_rep_trx_detail_t dtl
WHERE DTL.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
AND DTL.EXTRACT_SOURCE_LEDGER = 'AR'
AND EXISTS (SELECT 1 FROM ra_cust_trx_types_all types
WHERE types.org_id = dtl.internal_organization_id
AND dtl.trx_type_id = types.cust_trx_type_id
AND NVL(types.global_attribute1,'99') NOT IN ('31','32','33','34','35','36','37'));
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
| by selecting only the records associated with JA specific lookup type |
| all unnecessary rows in ZX_REP_DETAIL_T table are deleted |
| |
| Called from AR_TAX_EXTRACT.EXECUTE_SQL. |
| |
| Parameters : |
| IN : p_report_name varchar2 Required |
| p_request_id number Required |
| |
| MODIFICATION HISTORY |
| 27-Oct-03 Asako Takahashi created |
| |
| |
+===========================================================================*/
PROCEDURE FILTER_JA_AP_TAX_LINES
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
/* Following parameters are removed since we now access global variables directly
(
P_REPORT_NAME IN varchar2,
P_REQUEST_ID IN number,
P_GUI_TYPE IN varchar2,
P_REPRINT IN varchar2,
-- P_APPLIED_TRX_NUMBER_LOW in varchar2, -- no longer required TRL perform this filter
-- P_APPLIED_TRX_NUMBER_HIGH in varchar2, -- DET.ADJUSTED_DOC_NUMBER >= ''' ||P_ADJUSTED_DOC_NUM_LOW
P_MRCSOBTYPE in varchar2,
P_REPORTING_LEVEL in varchar2,
P_REPORTING_CONTEXT in number,
P_SET_OF_BOOKS_ID in number
)
*/
IS
l_delete_sql_string varchar2(3000);
IS SELECT vat_tax_id
FROM ar_vat_tax_all_b
WHERE org_id = p_org_id;
IS SELECT tax_id
FROM ap_tax_codes_all
WHERE org_id = p_org_id;
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
DET.TRX_LINE_CLASS IN ('STANDARD INVOICES', 'AP_CREDIT_MEMO', 'AP_DEBIT_MEMO') and
substrb(DET.DOCUMENT_SUB_TYPE, 10, 2) in ('21','22','23','24','25','26','27','28')
);
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
'ja_tax_extract.filter_ja_tax_lines '||'l_delete_sql_string: ' || l_delete_sql_string);
l_delete_sql_string:=
'DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
NOT EXISTS (SELECT 1 FROM AP_INVOICES ai
WHERE DET.EXTRACT_SOURCE_LEDGER = ''AP'' and
DET.TRX_LINE_CLASS IN (''STANDARD INVOICES'',''AP_CREDIT_MEMO'', ''AP_DEBIT_MEMO'') and
(DET.APPLIED_TO_TRX_NUMBER = ai.invoice_num OR DET.TRX_NUMBER = ai.invoice_num) and
DET.BILLING_TRADING_PARTNER_ID = ai.vendor_id and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN (''23'',''24'') '||
lp_gui_type_where ||
lp_reprint_where||')';
EXECUTE IMMEDIATE l_delete_sql_string;
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT (
(DET.EXTRACT_SOURCE_LEDGER = 'AR' and
DET.TRX_LINE_CLASS in ('INVOICE', 'CREDIT_MEMO') and
DET.DOC_EVENT_STATUS <> 'CANCELLED' and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN
('31','32','33','34','35','36','37')) or
(DET.EXTRACT_SOURCE_LEDGER = 'AP' and
DET.TRX_LINE_CLASS IN ('STANDARD INVOICES', 'AP_CREDIT_MEMO', 'AP_DEBIT_MEMO') and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN
('21','22','23','24','25','26','27','28'))
);
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
substrb(DET.DOCUMENT_SUB_TYPE,10,2) IN ('23','24') and
DET.CANCEL_FLAG <> 'Y'
);
' NULL supplier '||l_delete_sql_string );
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID and
NOT (DET.EXTRACT_SOURCE_LEDGER = 'AP' and
DET.FUNCTIONAL_CURRENCY_CODE <> DET.TRX_CURRENCY_CODE and
DET.SUPPLIER_EXCHANGE_RATE IS NOT NULL and
DET.CURRENCY_CONVERSION_RATE is NOT NULL);
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
org) and to support this, we use lp_where_org_art selection criteria */
if P_TRL_GLOBAL_VARIABLES_REC.REPORTING_LEVEL = '1000' then
lp_where_org_art:= ' and DET.LEDGER_ID = ' || P_TRL_GLOBAL_VARIABLES_REC.LEDGER_ID;
l_delete_sql_string:=
' DELETE from ZX_REP_TRX_DETAIL_T DET
WHERE DET.REQUEST_ID = '||P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID||' and
NOT ((DET.EXTRACT_SOURCE_LEDGER in (''AR'',''GL'') '||
lp_where_org_art ||' ) or
(DET.EXTRACT_SOURCE_LEDGER = ''AP''))';
'Deleted Count : '||to_char(SQL%ROWCOUNT) );
' l_delete_sql_string : '||l_delete_sql_string );
| This procedure calls the API to select the JA specific data from |
| JA receivables tables. |
| |
| Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
| |
| Parameters : |
| IN : p_zx_rep_detail_rec zx_rep_trx_detail_t%rowtype |
| p_report_name varchar2 -- required |
| |
| |
| MODIFICATION HISTORY |
| 27-Oct-03 Asako Takahashi created |
| |
| |
+===========================================================================*/
PROCEDURE POPULATE_JA_AR
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
/* Following parameter is removed since we now access global variables directly
(
-- IN parameters are passed as global variables.
-- DETAIL_TAX_LINE_ID_TBL
-- TRX_STATUS_CODE_TBL
-- TRX_BUSINESS_CATEGORY_TBL
-- TRX_BATCH_SOURCE_NAME_TBL
-- TRX_BATCH_SOURCE_ID_TBL
-- DOCUMENT_SUB_TYPE_TBL
-- TRX_BATCH_SOURCE_ID_TBL
-- GDF_RA_CUST_TRX_ATT7_TBL
-- GDF_RA_CUST_TRX_ATT5_TBL
-- GDF_RA_CUST_TRX_ATT6_TBL
-- PROD_FISC_CLASSIFICATION_TBL
P_REPORT_NAME IN varchar2
)
*/
IS
P_LOOKUP_TYPE varchar2(500);
SELECT detail_tax_line_id,
trx_id,
tax_status_code,
trx_business_category,
trx_batch_source_name,
document_sub_type,
product_fisc_classification,
trx_date --Bug 5251425
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_tax_status_code_tbl,
l_trx_business_category_tbl,
l_trx_batch_source_name_tbl,
l_document_sub_type_tbl,
l_prod_fisc_classification_tbl,
l_trx_date_tbl --Bug 5251425
FROM zx_rep_trx_detail_t itf1
WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
tax_status_mng, -- government tax type meaning
trx_business_category_mng, -- deductible code meaning
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_GOVERNMENT_TAX_TYPE_TBL(i),
X_DEDUCTIBLE_TYPE_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
tax_status_mng, -- government tax type meaning
gdf_ra_cust_trx_att9, -- orginal transaction number ZXTWRUIL
gdf_ra_batch_sources_att1, -- reference transaction source ZXTWRUIL
gdf_ra_batch_sources_att2, -- initial trx num ZXTWRUIL
gdf_ra_batch_sources_att3, -- invoice word ZXTWRUIL
gdf_ra_batch_sources_att4, -- final trx num ZXTWRUIL
attribute15, --Bug 5251425
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_GOVERNMENT_TAX_TYPE_TBL(i),
X_ORG_TRX_NUMBER_TBL(i),
X_REFERENCE_TRX_SRC_TBL(i),
X_INITIAL_TRX_NUM_TBL(i),
X_INVOICE_WORD_TBL(i),
X_FINAL_TRX_NUM_TBL(i),
x_trx_date_tbl(i), --Bug 5251425
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
attribute4, -- export method
attribute5, -- export certificate name
attribute6, -- export type
gdf_ra_cust_trx_att4, -- export certificate number ZXTWRZTR
gdf_ra_cust_trx_att5, -- export name ZXTWRZTR
gdf_ra_cust_trx_att6, -- export method ZXTWRZTR
gdf_ra_cust_trx_att7, -- export type ZXTWRZTR
gdf_ra_cust_trx_att8, -- export date ZXTWRZTR
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_EXPORT_METHOD_TBL(i),
X_EXPORT_CERTIFICATE_NAME_TBL(i),
X_EXPORT_TYPE_TBL(i),
X_GDF_RA_CUST_TRX_ATT4_TBL(i),
X_GDF_RA_CUST_TRX_ATT5_TBL(i),
X_GDF_RA_CUST_TRX_ATT6_TBL(i),
X_GDF_RA_CUST_TRX_ATT7_TBL(i),
X_GDF_RA_CUST_TRX_ATT8_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
INSERT INTO ZX_REP_TRX_JX_EXT_T(
detail_tax_line_ext_id,
detail_tax_line_id,
document_sub_type_mng,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id
)
SELECT
zx_rep_trx_jx_ext_t_s.nextval,
dtl.detail_tax_line_id,
types.global_attribute1,
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 dtl,
ra_cust_trx_types_all types
WHERE dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID
and dtl.extract_source_ledger = 'AR'
and types.cust_trx_type_id = dtl.trx_type_id
and types.org_id = dtl.internal_organization_id ;
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT loc.location_code,
loc.ADDRESS_LINE_1,
loc.ADDRESS_LINE_2,
loc.ADDRESS_LINE_3,
loc.TOWN_OR_CITY ,
loc.REGION_1,
loc.REGION_2,
loc.POSTAL_CODE,
loc.COUNTRY,
loc.GLOBAL_ATTRIBUTE1,
loc.GLOBAL_ATTRIBUTE4,
-- loc.GLOBAL_ATTRIBUTE5,
-- loc.GLOBAL_ATTRIBUTE6,
-- loc.GLOBAL_ATTRIBUTE11,
dtl.detail_tax_line_id
BULK COLLECT INTO l_location_code_tbl,
l_address_line_1_tbl,
l_address_line_2_tbl,
l_address_line_3_tbl,
l_city_tbl,
l_region_1_tbl,
l_region_2_tbl,
l_postal_code_tbl,
l_country_code_tbl,
l_loc_tax_reg_num_tbl,
l_taxable_person_tbl,
-- l_ind_sub_classif_tbl,
-- l_ind_classif_tbl,
l_detail_tax_line_id_tbl
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass,
hr_locations loc,
zx_rep_trx_detail_t dtl
WHERE rep_type.reporting_type_code = 'KR_BUSINESS_LOCATIONS'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id = dtl.tax_rate_id
AND dtl.tax_regime_code = rep_type.tax_regime_code
AND rep_ass.reporting_code_char_value = loc.location_code
AND loc.global_attribute_category = 'JA.KR.PERWSLOC.WITHHOLDING'
AND dtl.application_id in (222,101)
AND dtl.request_id = p_trl_global_variables_rec.request_id ;
SELECT global_attribute8,
global_attribute3
BULK COLLECT INTO
l_ind_classif_tbl,
l_ind_sub_classif_tbl
FROM hz_cust_acct_sites_all acct_site,
zx_rep_trx_detail_t dtl
WHERE acct_site.cust_acct_site_id = NVL(dtl.shipping_tp_address_id, dtl.billing_tp_address_id)
AND dtl.application_id = 222
AND dtl.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,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_location_code_tbl(i),
l_address_line_1_tbl(i),
l_address_line_2_tbl(i),
l_address_line_3_tbl(i),
l_city_tbl(i),
l_region_1_tbl(i),
l_region_2_tbl(i),
l_postal_code_tbl(i),
l_country_code_tbl(i),
l_loc_tax_reg_num_tbl(i),
l_taxable_person_tbl(i),
l_ind_sub_classif_tbl(i),
l_ind_classif_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
| This procedure calls the API to select the JA specific data from |
| JA payables tables. Currently only JA_LOOKUP_INFO plug-in is called |
| inside. |
| |
| Called from ARP_TAX_EXTRACT.POPULATE_MISSING_COLUMNS. |
| |
| Parameters : |
| |
| |
| MODIFICATION HISTORY |
| 27-Oct-03 Asako Takahashi created |
| |
| |
+===========================================================================*/
PROCEDURE POPULATE_JA_AP
(
P_TRL_GLOBAL_VARIABLES_REC IN ZX_EXTRACT_PKG.TRL_GLOBAL_VARIABLES_REC_TYPE
)
/* Following parameter is removed since we now access global variables directly
(
-- IN parameters are passed as global variables.
-- DETAIL_TAX_LINE_ID_TBL
-- TRX_STATUS_CODE_TBL
-- TRX_BUSINESS_CATEGORY_TBL
-- BILLING_TRADING_PARTNER_ID_TBL
-- ADJUSTED_DOC_NUMBER_TBL
-- TRX_ID_TBL
-- TAXABLE_AMT_TBL
-- TAX_AMT_TBL
-- CURRENCY_CONVERSION_RATE_TBL
-- PRECISION_TBL
-- REPRINT_TBL
P_REPORT_NAME IN varchar2
)
*/
IS
TYPE GOVERNMENT_TAX_TYPE_TBL is TABLE OF
ZX_REP_TRX_JX_EXT_T.TAX_STATUS_MNG%TYPE INDEX BY BINARY_INTEGER;
SELECT detail_tax_line_id,
trx_id,
trx_line_id,
tax_status_id,
tax_status_code,
trx_business_category,
document_sub_type,
billing_trading_partner_id,
adjusted_doc_number,
taxable_amt,
currency_conversion_rate,
precision,
supplier_exchange_rate,
tax_amt,
applied_to_trx_number,
trx_date
BULK COLLECT INTO l_detail_tax_line_id_tbl,
l_trx_id_tbl,
l_trx_line_id_tbl, --Bug#5673935
l_tax_status_id_tbl,
l_tax_status_code_tbl,
l_trx_business_category_tbl,
l_document_sub_type_tbl,
l_billing_tp_id_tbl,
l_adjusted_doc_number_tbl,
l_taxable_amt_tbl,
l_currency_conversion_rate_tbl,
l_precision_tbl,
l_supplier_exchange_rate_tbl,
l_tax_amt_tbl,
l_applied_to_trx_number_tbl,
l_trx_date_tbl
FROM zx_rep_trx_detail_t itf1
WHERE itf1.request_id = P_TRL_GLOBAL_VARIABLES_REC.REQUEST_ID;
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
tax_status_mng, -- government tax type meaning
trx_business_category_mng, -- deductible code meaning
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_GOVERNMENT_TAX_TYPE_TBL(i),
X_DEDUCTIBLE_TYPE_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
/****** Bulk Insert into Ext Table *******/
IF ( g_level_statement>= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_statement, 'ZX.TRL.ZX_JA_EXTRACT_PKG.POPULATE_JA_AP',
'Before into zx_rep_trx_jx_ext_t ap ');
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
tax_status_mng, -- government tax type meaning
trx_business_category_mng, -- deductible code meaning
document_sub_type_mng, -- document subtype meaning
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_GOVERNMENT_TAX_TYPE_TBL(i),
X_DEDUCTIBLE_TYPE_TBL(i),
X_GUI_TYPE_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
UPDATE_PRINT_DATE(l_trx_id_tbl(i));
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
gdf_ap_invoices_att4, -- print date
numeric12, -- document amount
numeric13, -- document base amount
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_PRINT_DATE_TBL(i),
X_INVOICE_AMOUNT_TBL(i),
X_BASE_AMOUNT_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
numeric12, -- document amount
numeric13, -- document base amount
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_INVOICE_AMOUNT_TBL(i),
X_BASE_AMOUNT_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
INSERT INTO zx_rep_trx_jx_ext_t (
request_id,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
detail_tax_line_ext_id,
detail_tax_line_id,
document_sub_type_mng,
numeric1,
numeric2,
numeric3,
numeric4
)
SELECT
p_trl_global_variables_rec.request_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
zx_rep_trx_jx_ext_t_s.NEXTVAL,
detail_tax_line_id,
document_sub_type,
CASE WHEN tax_line_change= 1 OR tax_recoverable_flag = 'N' THEN taxable_amt
ELSE 0
END,
CASE WHEN tax_line_change= 1 OR tax_recoverable_flag = 'N' THEN taxable_amt_funcl_curr
ELSE 0
END,
tax_amt,
tax_amt_funcl_curr
FROM (
SELECT itf.detail_tax_line_id,
itf.document_sub_type,
itf.tax_amt,
itf.tax_amt_funcl_curr,
itf.taxable_amt,
itf.taxable_amt_funcl_curr,
itf.tax_recoverable_flag,
RANK() OVER (PARTITION BY itf.trx_id,
itf.trx_line_id
ORDER BY NVL(itf.tax_recoverable_flag,'N'),
itf.actg_source_id,
itf.detail_tax_line_id
) AS tax_line_change
FROM zx_rep_trx_detail_t itf
WHERE itf.request_id = p_trl_global_variables_rec.request_id
AND itf.application_id = 200);
/****** Bulk Insert into Ext Table *******/
FORALL i in 1 .. nvl(l_detail_tax_line_id_tbl.last, 0)
INSERT INTO ZX_REP_TRX_JX_EXT_T
(detail_tax_line_ext_id,
detail_tax_line_id,
numeric1, -- inhouse invoice amt
numeric2, -- supplier invoice amt
numeric3, -- inhouse tax amt
numeric4, -- supplier tax amt
numeric5, -- taxable gainloss
numeric6, -- gainloss
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
X_INHOUSE_INV_AMT_TBL(i),
X_SUPPLIER_INV_AMT_TBL(i),
X_INHOUSE_TAX_AMT_TBL(i),
X_SUPPLIER_TAX_AMT_TBL(i),
X_TAXABLE_GAINLOSS_TBL(i),
X_TAX_GAINLOSS_TBL(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
'After insertion into zx_rep_trx_jx_ext_t ');
SELECT loc.location_code,
loc.ADDRESS_LINE_1,
loc.ADDRESS_LINE_2,
loc.ADDRESS_LINE_3,
loc.TOWN_OR_CITY ,
loc.REGION_1,
loc.REGION_2,
loc.POSTAL_CODE,
loc.COUNTRY,
loc.GLOBAL_ATTRIBUTE1,
loc.GLOBAL_ATTRIBUTE4,
-- loc.GLOBAL_ATTRIBUTE5,
-- loc.GLOBAL_ATTRIBUTE6,
-- loc.GLOBAL_ATTRIBUTE11,
dtl.detail_tax_line_id
BULK COLLECT INTO l_location_code_tbl,
l_address_line_1_tbl,
l_address_line_2_tbl,
l_address_line_3_tbl,
l_city_tbl,
l_region_1_tbl,
l_region_2_tbl,
l_postal_code_tbl,
l_country_code_tbl,
l_loc_tax_reg_num_tbl,
l_taxable_person_tbl,
-- l_ind_sub_classif_tbl,
-- l_ind_classif_tbl,
l_detail_tax_line_id_tbl
FROM zx_reporting_types_b rep_type,
zx_report_codes_assoc rep_ass,
hr_locations loc,
zx_rep_trx_detail_t dtl
WHERE rep_type.reporting_type_code = 'KR_BUSINESS_LOCATIONS'
AND rep_ass.reporting_type_id = rep_type.reporting_type_id
AND rep_ass.entity_code = 'ZX_RATES'
AND rep_ass.entity_id = dtl.tax_rate_id
AND dtl.tax_regime_code = rep_type.tax_regime_code
AND rep_ass.reporting_code_char_value = loc.location_code
AND loc.global_attribute_category = 'JA.KR.PERWSLOC.WITHHOLDING'
AND dtl.application_id = 200
AND dtl.request_id = p_trl_global_variables_rec.request_id ;
SELECT global_attribute4,
global_attribute5
BULK COLLECT INTO
l_ind_classif_tbl,
l_ind_sub_classif_tbl
FROM ap_supplier_sites_all sup_site,
zx_rep_trx_detail_t dtl
WHERE sup_site.vendor_site_id = NVL(dtl.shipping_tp_address_id, dtl.billing_tp_address_id)
AND dtl.application_id = 200
AND dtl.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,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES (zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_location_code_tbl(i),
l_address_line_1_tbl(i),
l_address_line_2_tbl(i),
l_address_line_3_tbl(i),
l_city_tbl(i),
l_region_1_tbl(i),
l_region_2_tbl(i),
l_postal_code_tbl(i),
l_country_code_tbl(i),
l_loc_tax_reg_num_tbl(i),
l_taxable_person_tbl(i),
l_ind_sub_classif_tbl(i),
l_ind_classif_tbl(i),
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id);
SELECT CLASSIFICATION_NAME
INTO x_lookup_meaning
FROM ZX_FC_CODES_DENORM_B
WHERE CLASSIFICATION_TYPE_CODE = 'DOCUMENT_SUBTYPE' and
-- CLASSIFICATION_CODE_LEVEL = 2 and --Bug5453806
CONCAT_CLASSIF_CODE = P_LOOKUP_CODE
AND LANGUAGE = userenv('LANG') --Bug 5453957
and p_trx_date >= effective_from and p_trx_date <= nvl(effective_to, p_trx_date);
SELECT TAX_STATUS_NAME
INTO x_lookup_meaning
FROM ZX_STATUS_TL stl, zx_status_b sb
WHERE sb.tax_status_code = P_LOOKUP_CODE
AND sb.tax_status_id = stl.tax_status_id
AND LANGUAGE = userenv('LANG')
and p_trx_date >= effective_from and p_trx_date <= nvl(effective_to, p_trx_date);
SELECT CLASSIFICATION_NAME
INTO x_lookup_meaning
FROM ZX_FC_CODES_DENORM_B
WHERE CLASSIFICATION_TYPE_CODE = 'TRX_BUSINESS_CATEGORY' and
-- CLASSIFICATION_CODE_LEVEL = 3 and --Bug5453806
CONCAT_CLASSIF_CODE = P_LOOKUP_CODE
AND LANGUAGE = userenv('LANG') --Bug 5453957
and p_trx_date >= effective_from and p_trx_date <= nvl(effective_to, p_trx_date);
SELECT JA.MEANING
INTO X_LOOKUP_MEANING
FROM JA_LOOKUPS JA
WHERE JA.LOOKUP_TYPE = P_LOOKUP_TYPE and
JA.LOOKUP_CODE = P_LOOKUP_CODE
and p_trx_date >= start_date_active and p_trx_date <= nvl(end_date_active, p_trx_date);
SELECT AI.GLOBAL_ATTRIBUTE4, AI.INVOICE_AMOUNT, AI.BASE_AMOUNT
INTO X_PRINT_DATE, X_INVOICE_AMT, X_BASE_AMT
FROM AP_INVOICES AI
WHERE AI.VENDOR_ID = P_VENDOR_ID and
AI.INVOICE_NUM = P_INVOICE_NUM;
SELECT decode(src.global_attribute1, NULL, src.batch_source_id,
src.global_attribute1)
INTO X_GDF_RA_BATCH_SOURCES_ATT1
FROM ra_batch_sources src
WHERE src.name = P_TRX_SOURCE_NAME;
SELECT substr(global_attribute3,1,2),
to_number(global_attribute2),
to_number(global_attribute4)
INTO X_GDF_RA_BATCH_SOURCES_ATT3,
X_GDF_RA_BATCH_SOURCES_ATT2,
X_GDF_RA_BATCH_SOURCES_ATT4
FROM ra_batch_sources
WHERE batch_source_id = X_GDF_RA_BATCH_SOURCES_ATT1;
| UPDATE_PRINT_DATE |
| Type : Private |
| Pre-req : None |
| Function : |
| This plug-in updates print date stored in ap_invoices table for |
| ZXTWPSPC |
| |
| Called from ZX_JA_EXTRACT_PKG.POPULATE |
| |
| Parameters : |
| IN : |
| p_trx_id IN NUMBER Required |
| |
| MODIFICATION HISTORY |
| 27-Oct-03 Asako Takahashi created |
| |
| |
+===========================================================================*/
PROCEDURE UPDATE_PRINT_DATE
(
p_invoice_id number
)
IS
l_dummy varchar2(150);
IS SELECT ai.global_attribute4
FROM ap_invoices ai
WHERE ai.invoice_id = l_invoice_id
FOR UPDATE NOWAIT;
'UPDATE_PRINT_DATE(+)');
UPDATE
ap_invoices
SET
global_attribute4 = fnd_date.date_to_canonical(sysdate)
WHERE
invoice_id = p_invoice_id;
'Update Count : '||to_char(SQL%ROWCOUNT) );
'UPDATE_PRINT_DATE(-)');
END update_print_date;
SELECT rct_org.trx_number
INTO X_ORG_TRX_NUMBER
FROM ra_customer_trx_all rct,
ra_customer_trx_all rct_org,
ra_batcH_sources_all rbs
WHERE rct_org.customer_Trx_id = rct.previous_customer_trx_id and
rct.batch_source_id = rbs.batch_source_id and
rbs.batch_source_id = P_TRX_SOURCE_ID and
rct.customer_trx_id = p_trx_id;
SELECT
rct.global_attribute4,
rct.global_attribute5,
rct.global_attribute6,
rct.global_attribute7,
rct.global_attribute8
INTO
X_GDF_RA_CUST_TRX_ATT4,
X_GDF_RA_CUST_TRX_ATT5,
X_GDF_RA_CUST_TRX_ATT6,
X_GDF_RA_CUST_TRX_ATT7,
X_GDF_RA_CUST_TRX_ATT8
FROM
ra_customer_trx_all rct
WHERE
rct.customer_trx_id = P_TRX_ID and
rct.global_attribute_category = 'JA.TW.ARXTWMAI.RA_CUSTOMER_TRX';
SELECT reg.bank_id,
reg.bank_branch_id,
reg.account_id,
reg.bank_account_num,
a.bank_name ,
a.bank_branch_name
FROM zx_party_tax_profile ptp,
xle_etb_profiles xlep,
zx_registrations reg ,
ce_bank_branches_v a
WHERE ptp.party_id = xlep.party_id
AND ptp.party_type_code = 'LEGAL_ESTABLISHMENT'
AND xlep.legal_entity_id = P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id
AND xlep.main_establishment_flag = 'Y'
AND reg.bank_id = a.bank_party_id
AND reg. bank_branch_id = a.branch_party_id
AND reg.party_tax_profile_id = ptp.party_tax_profile_id;
SELECT dtl.detail_tax_line_id
BULK COLLECT INTO l_detail_tax_line_id_tbl
FROM zx_rep_trx_detail_t dtl
WHERE dtl.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,
attribute28, --bank ID
attribute29, --Bracnh ID
attribute30, --Account ID
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
VALUES ( zx_rep_trx_jx_ext_t_s.nextval,
l_detail_tax_line_id_tbl(i),
l_bank_name,
l_branch_name,
l_bank_account_num,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_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,
attribute28,
attribute29,
attribute30,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id)
SELECT zx_rep_trx_jx_ext_t_s.nextval,
dtl.detail_tax_line_id,
loc.global_attribute5,
loc.global_attribute6,
loc.global_attribute7,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
p_trl_global_variables_rec.request_id
FROM hr_all_organization_units ou,
hr_organization_information oi,
hr_locations loc,
gl_sets_of_books sob,
zx_rep_trx_detail_t dtl
WHERE ou.organization_id = oi.organization_id
AND ou.location_id = loc.location_id
AND TO_NUMBER(oi.org_information1) = sob.set_of_books_id
AND oi.org_information_context = 'Legal Entity Accounting'
AND dtl.request_id = P_TRL_GLOBAL_VARIABLES_REC.request_id
AND ou.organization_id = nvl(dtl.internal_organization_id,
P_TRL_GLOBAL_VARIABLES_REC.legal_entity_id);