[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_row
(p_Rowid IN OUT NOCOPY VARCHAR2,
p_internal_organization_id NUMBER,
p_internal_org_location_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_event_type_code VARCHAR2,
p_trx_id NUMBER,
p_trx_date DATE,
p_trx_doc_revision VARCHAR2,
p_ledger_id NUMBER,
p_trx_currency_code VARCHAR2,
p_currency_conversion_date DATE,
p_currency_conversion_rate NUMBER,
p_currency_conversion_type VARCHAR2,
p_minimum_accountable_unit NUMBER,
p_precision NUMBER,
p_legal_entity_id NUMBER,
p_rounding_ship_to_party_id NUMBER,
p_rounding_ship_from_party_id NUMBER,
p_rounding_bill_to_party_id NUMBER,
p_rounding_bill_from_party_id NUMBER,
p_rndg_ship_to_party_site_id NUMBER,
p_rndg_ship_from_pty_site_id NUMBER, --reduced size p_rndg_ship_from_party_site_id
p_rndg_bill_to_party_site_id NUMBER,
p_rndg_bill_from_pty_site_id NUMBER, --reduced size p_rndg_bill_from_party_site_id
p_establishment_id NUMBER,
p_related_doc_application_id NUMBER,
p_related_doc_entity_code VARCHAR2,
p_related_doc_evt_class_code VARCHAR2, --reduced size p_related_doc_event_class_code
p_related_doc_trx_id NUMBER,
p_related_doc_number VARCHAR2,
p_related_doc_date DATE,
p_default_taxation_country VARCHAR2,
p_quote_flag VARCHAR2,
p_trx_number VARCHAR2,
p_trx_description VARCHAR2,
p_trx_communicated_date DATE,
p_batch_source_id NUMBER,
p_batch_source_name VARCHAR2,
--p_doc_seq_id NUMBER,
--p_doc_seq_name VARCHAR2,
--p_doc_seq_value VARCHAR2,
p_trx_due_date DATE,
p_trx_type_description VARCHAR2,
p_billing_trad_partner_name VARCHAR2, --reduced size p_billing_trading_partner_name
p_billing_trad_partner_number VARCHAR2, --reduced size p_billing_trading_partner_number
p_billing_tp_tax_report_flg VARCHAR2, --reduced size p_Billing_Tp_Tax_Reporting_Flag
p_billing_tp_taxpayer_id VARCHAR2,
p_document_sub_type VARCHAR2,
p_supplier_tax_invoice_number VARCHAR2,
p_supplier_tax_invoice_date DATE,--
p_supplier_exchange_rate NUMBER,
p_tax_invoice_date DATE,
p_tax_invoice_number VARCHAR2,
p_tax_event_class_code VARCHAR2,
p_tax_event_type_code VARCHAR2,
p_doc_event_status VARCHAR2,
p_rdng_ship_to_pty_tx_prof_id NUMBER,
p_rdng_ship_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_prof_id
p_rdng_bill_to_pty_tx_prof_id NUMBER,
p_rdng_bill_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_prof_id
p_rdng_ship_to_pty_tx_p_st_id NUMBER,
p_rdng_ship_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_p_st_id
p_rdng_bill_to_pty_tx_p_st_id NUMBER,
p_rdng_bill_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_p_st_id
p_trx_level_type VARCHAR2,
p_trx_line_id NUMBER,
p_line_level_action VARCHAR2,
p_trx_shipping_date DATE,
p_trx_receipt_date DATE,
p_trx_line_type VARCHAR2,
p_trx_line_date DATE,
p_trx_business_category VARCHAR2,
p_line_intended_use VARCHAR2,
p_user_defined_fisc_class VARCHAR2,
p_line_amt NUMBER,
p_trx_line_quantity NUMBER,
p_unit_price NUMBER,
p_exempt_certificate_number VARCHAR2,
p_exempt_reason VARCHAR2,
p_cash_discount NUMBER,
p_volume_discount NUMBER,
p_trading_discount NUMBER,
p_transfer_charge NUMBER,
p_transportation_charge NUMBER,
p_insurance_charge NUMBER,
p_other_charge NUMBER,
p_product_id NUMBER,
p_product_fisc_classification VARCHAR2,
p_product_org_id NUMBER,
p_uom_code VARCHAR2,
p_product_type VARCHAR2,
p_product_code VARCHAR2,
p_product_category VARCHAR2,
p_trx_sic_code VARCHAR2,
p_fob_point VARCHAR2,
p_ship_to_party_id NUMBER,
p_ship_from_party_id NUMBER,
p_poa_party_id NUMBER,
p_poo_party_id NUMBER,
p_bill_to_party_id NUMBER,
p_bill_from_party_id NUMBER,
p_merchant_party_id NUMBER,
p_ship_to_party_site_id NUMBER,
p_ship_from_party_site_id NUMBER,
p_poa_party_site_id NUMBER,
p_poo_party_site_id NUMBER,
p_bill_to_party_site_id NUMBER,
p_bill_from_party_site_id NUMBER,
p_ship_to_location_id NUMBER,
p_ship_from_location_id NUMBER,
p_poa_location_id NUMBER,
p_poo_location_id NUMBER,
p_bill_to_location_id NUMBER,
p_bill_from_location_id NUMBER,
p_account_ccid NUMBER,
p_account_string VARCHAR2,
p_merchant_party_country VARCHAR2,
p_ref_doc_application_id NUMBER,
p_ref_doc_entity_code VARCHAR2,
p_ref_doc_event_class_code VARCHAR2,
p_ref_doc_trx_id NUMBER,
p_ref_doc_line_id NUMBER,
p_ref_doc_line_quantity NUMBER,
p_applied_from_application_id NUMBER,
p_applied_from_entity_code VARCHAR2,
p_applied_from_evt_class_code VARCHAR2, --reduced size p_applied_from_event_class_code
p_applied_from_trx_id NUMBER,
p_applied_from_line_id NUMBER,
p_adjusted_doc_application_id NUMBER,
p_adjusted_doc_entity_code VARCHAR2,
p_adj_doc_event_class_code VARCHAR2, --reduced size p_adjusted_doc_event_class_code
p_adjusted_doc_trx_id NUMBER,
p_adjusted_doc_line_id NUMBER,
p_adjusted_doc_number VARCHAR2,
p_adjusted_doc_date DATE,
p_applied_to_application_id NUMBER,
p_applied_to_entity_code VARCHAR2,
p_applied_to_event_class_code VARCHAR2,
p_applied_to_trx_id NUMBER,
p_applied_to_trx_line_id NUMBER,
p_trx_id_level2 NUMBER,
p_trx_id_level3 NUMBER,
p_trx_id_level4 NUMBER,
p_trx_id_level5 NUMBER,
p_trx_id_level6 NUMBER,
p_trx_line_number NUMBER,
p_trx_line_description VARCHAR2,
p_product_description VARCHAR2,
p_trx_waybill_number VARCHAR2,
p_trx_line_gl_date DATE,
p_merchant_party_name VARCHAR2,
p_merchant_party_doc_number VARCHAR2, --reduced size p_merchant_party_document_number
p_merchant_party_reference VARCHAR2,
p_merchant_party_taxpayer_id VARCHAR2,
p_merchant_pty_tax_reg_number VARCHAR2, --reduced size p_merchant_party_tax_reg_number
p_paying_party_id NUMBER,
p_own_hq_party_id NUMBER,
p_trading_hq_party_id NUMBER,
p_poi_party_id NUMBER,
p_pod_party_id NUMBER,
p_title_transfer_party_id NUMBER,
p_paying_party_site_id NUMBER,
p_own_hq_party_site_id NUMBER,
p_trading_hq_party_site_id NUMBER,
p_poi_party_site_id NUMBER,
p_pod_party_site_id NUMBER,
p_title_transfer_pty_site_id NUMBER, --reduced size p_title_transfer_party_site_id
p_paying_location_id NUMBER,
p_own_hq_location_id NUMBER,
p_trading_hq_location_id NUMBER,
p_poc_location_id NUMBER,
p_poi_location_id NUMBER,
p_pod_location_id NUMBER,
p_title_transfer_location_id NUMBER,
p_banking_tp_taxpayer_id VARCHAR2,
p_assessable_value NUMBER,
p_asset_flag VARCHAR2,
p_asset_number VARCHAR2,
p_asset_accum_depreciation NUMBER,
p_asset_type VARCHAR2,
p_asset_cost NUMBER,
p_ship_to_party_tax_prof_id NUMBER,
p_ship_from_party_tax_prof_id NUMBER,
p_poa_party_tax_prof_id NUMBER,
p_poo_party_tax_prof_id NUMBER,
p_paying_party_tax_prof_id NUMBER,
p_own_hq_party_tax_prof_id NUMBER,
p_trading_hq_pty_tax_prof_id NUMBER, --reduced size p_trading_hq_party_tax_prof_id
p_poi_party_tax_prof_id NUMBER,
p_pod_party_tax_prof_id NUMBER,
p_bill_to_party_tax_prof_id NUMBER,
p_bill_from_party_tax_prof_id NUMBER,
p_title_trans_pty_tax_prof_id NUMBER, --reduced size p_title_trans_party_tax_prof_id
p_ship_to_site_tax_prof_id NUMBER,
p_ship_from_site_tax_prof_id NUMBER,
p_poa_site_tax_prof_id NUMBER,
p_poo_site_tax_prof_id NUMBER,
p_paying_site_tax_prof_id NUMBER,
p_own_hq_site_tax_prof_id NUMBER,
p_trading_hq_site_tax_prof_id NUMBER,
p_poi_site_tax_prof_id NUMBER,
p_pod_site_tax_prof_id NUMBER,
p_bill_to_site_tax_prof_id NUMBER,
p_bill_from_site_tax_prof_id NUMBER,
p_title_trn_site_tax_prof_id NUMBER, --reduced size p_title_trans_site_tax_prof_id
p_merchant_party_tax_prof_id NUMBER,
p_line_amt_includes_tax_flag VARCHAR2,
p_historical_flag VARCHAR2,
p_tax_classification_code VARCHAR2,
p_ctrl_hdr_tx_appl_flag VARCHAR2,
p_ctrl_total_line_tx_amt NUMBER,
p_tax_regime_id NUMBER,
p_tax_regime_code VARCHAR2,
p_tax_id NUMBER,
p_tax VARCHAR2,
p_tax_status_id NUMBER,
p_tax_status_code VARCHAR2,
p_tax_rate_id NUMBER,
p_tax_rate_code VARCHAR2,
p_tax_rate NUMBER,
p_tax_line_amt NUMBER,
p_line_class VARCHAR2,
p_input_tax_classif_code VARCHAR2,
p_output_tax_classif_code VARCHAR2,
p_ref_doc_trx_level_type VARCHAR2,
p_applied_to_trx_level_type VARCHAR2,
p_applied_from_trx_level_type VARCHAR2,
p_adjusted_doc_trx_level_type VARCHAR2,
p_exemption_control_flag VARCHAR2,
p_exempt_reason_code VARCHAR2,
p_receivables_trx_type_id NUMBER,
p_object_version_number NUMBER,
p_created_by NUMBER,
p_creation_date DATE,
p_last_updated_by NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER) IS
l_return_status VARCHAR2(1000);
SELECT rowid
FROM zx_transaction_lines
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND TRX_LINE_ID = p_trx_line_id
AND TRX_LEVEL_TYPE = p_trx_level_type;
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row.BEGIN',
'ZX_TRX_DETAIL: Insert_Row (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row',
'Insert into ZX_TRANSACTION_LINES (+)');
UPDATE ZX_TRANSACTION
SET EVENT_TYPE_CODE = p_event_type_code
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
INSERT INTO ZX_TRANSACTION_LINES (--SUBSCRIBER_ID,
--INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
--EVENT_TYPE_CODE,
TRX_LINE_ID,
TRX_LINE_NUMBER,
TRX_ID,
TRX_LEVEL_TYPE,
TRX_LINE_TYPE,
TRX_LINE_DATE,
TRX_BUSINESS_CATEGORY,
LINE_INTENDED_USE,
USER_DEFINED_FISC_CLASS,
LINE_AMT_INCLUDES_TAX_FLAG,
LINE_AMT,
TRX_LINE_QUANTITY,
UNIT_PRICE,
PRODUCT_ID,
PRODUCT_FISC_CLASSIFICATION,
PRODUCT_ORG_ID,
UOM_CODE,
PRODUCT_TYPE,
PRODUCT_CODE,
PRODUCT_CATEGORY,
MERCHANT_PARTY_ID,
ACCOUNT_CCID,
ACCOUNT_STRING,
REF_DOC_LINE_ID,
REF_DOC_LINE_QUANTITY,
REF_DOC_APPLICATION_ID,
REF_DOC_ENTITY_CODE,
REF_DOC_EVENT_CLASS_CODE,
REF_DOC_TRX_ID,
APPLIED_FROM_LINE_ID,
APPLIED_FROM_APPLICATION_ID,
APPLIED_FROM_ENTITY_CODE,
APPLIED_FROM_EVENT_CLASS_CODE,
APPLIED_FROM_TRX_ID,
ADJUSTED_DOC_LINE_ID,
ADJUSTED_DOC_DATE,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_ENTITY_CODE,
ADJUSTED_DOC_EVENT_CLASS_CODE,
ADJUSTED_DOC_TRX_ID,
TRX_LINE_DESCRIPTION,
PRODUCT_DESCRIPTION,
--TRX_COMMUNICATED_DATE,
TRX_LINE_GL_DATE,
--DOC_SEQ_ID,
--DOC_SEQ_NAME,
--DOC_SEQ_VALUE,
--RECEIVABLES_TRX_TYPE_ID,
--BATCH_SOURCE_NAME,
LINE_LEVEL_ACTION,
Historical_Flag,
--TRX_DATE,
--LEDGER_ID,
--MINIMUM_ACCOUNTABLE_UNIT,
--PRECISION,
--LEGAL_ENTITY_ID,
BILL_FROM_PARTY_SITE_ID,
BILL_TO_PARTY_SITE_ID,
SHIP_FROM_PARTY_SITE_ID,
SHIP_TO_PARTY_SITE_ID,
SHIP_TO_PARTY_ID,
SHIP_FROM_PARTY_ID,
BILL_TO_PARTY_ID,
BILL_FROM_PARTY_ID,
SHIP_TO_LOCATION_ID,
SHIP_FROM_LOCATION_ID,
BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID,
POA_LOCATION_ID,
POO_LOCATION_ID,
PAYING_LOCATION_ID,
OWN_HQ_LOCATION_ID,
TRADING_HQ_LOCATION_ID,
POC_LOCATION_ID,
POI_LOCATION_ID,
POD_LOCATION_ID,
TAX_REGIME_ID,
TAX_REGIME_CODE,
TAX_ID,
TAX,
TAX_STATUS_ID,
TAX_STATUS_CODE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE,
TAX_LINE_AMT,
LINE_CLASS,
INPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_CLASSIFICATION_CODE,
REF_DOC_TRX_LEVEL_TYPE,
APPLIED_TO_TRX_LEVEL_TYPE,
APPLIED_FROM_TRX_LEVEL_TYPE,
ADJUSTED_DOC_TRX_LEVEL_TYPE,
EXEMPTION_CONTROL_FLAG,
EXEMPT_REASON_CODE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON,
CASH_DISCOUNT,
VOLUME_DISCOUNT,
TRADING_DISCOUNT,
TRANSFER_CHARGE,
TRANSPORTATION_CHARGE,
INSURANCE_CHARGE,
OTHER_CHARGE,
RECEIVABLES_TRX_TYPE_ID,
CTRL_HDR_TX_APPL_FLAG,
CTRL_TOTAL_LINE_TX_AMT,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
VALUES (--p_subscriber_id,
--p_internal_organization_id,
p_application_id,
p_entity_code,
p_event_class_code,
--p_event_type_code,
p_trx_line_id,
p_trx_line_number,
p_trx_id,
p_trx_level_type,
p_trx_line_type,
p_trx_line_date,
p_trx_business_category,
p_line_intended_use,
p_user_defined_fisc_class,
p_Line_Amt_Includes_Tax_Flag,
p_line_amt,
p_trx_line_quantity,
p_unit_price,
p_product_id,
p_product_fisc_classification,
p_product_org_id,
p_uom_code,
p_product_type,
p_product_code,
p_product_category,
p_merchant_party_id,
p_account_ccid,
p_account_string,
p_ref_doc_line_id,
p_ref_doc_line_quantity,
p_ref_doc_application_id,
p_ref_doc_entity_code,
p_ref_doc_event_class_code,
p_ref_doc_trx_id,
p_applied_from_line_id,
p_applied_from_application_id,
p_applied_from_entity_code, --resized
p_applied_from_evt_class_code,--resized
p_applied_from_trx_id,
p_adjusted_doc_line_id,
p_adjusted_doc_date,
p_adjusted_doc_application_id,
p_adjusted_doc_entity_code,
p_adj_doc_event_class_code, --resized
p_adjusted_doc_trx_id,
p_trx_line_description,
p_product_description,
--p_trx_communicated_date,
p_trx_line_gl_date,
--p_doc_seq_id,
--p_doc_seq_name,
--p_doc_seq_value,
--p_receivables_trx_type_id,
--p_batch_source_name,
p_line_level_action,
p_Historical_Flag,
--p_trx_date,
--p_ledger_id,
--p_minimum_accountable_unit,
--p_precision,
--p_legal_entity_id,
p_bill_from_party_site_id,
p_bill_to_party_site_id,
p_ship_from_party_site_id,
p_ship_to_party_site_id,
p_ship_to_party_id,
p_ship_from_party_id,
p_bill_to_party_id,
p_bill_from_party_id,
p_ship_to_location_id,
p_ship_from_location_id,
p_bill_to_location_id,
p_bill_from_location_id,
p_poa_location_id,
p_poo_location_id,
p_paying_location_id,
p_own_hq_location_id,
p_trading_hq_location_id,
p_poc_location_id,
p_poi_location_id,
p_pod_location_id,
p_tax_regime_id,
p_tax_regime_code,
p_tax_id,
p_tax,
p_tax_status_id,
p_tax_status_code,
p_tax_rate_id,
p_tax_rate_code,
p_tax_rate,
p_tax_line_amt,
p_line_class,
p_input_tax_classif_code,
p_output_tax_classif_code,
p_ref_doc_trx_level_type,
p_applied_to_trx_level_type,
p_applied_from_trx_level_type,
p_adjusted_doc_trx_level_type,
p_exemption_control_flag,
p_exempt_reason_code,
p_exempt_certificate_number,
p_exempt_reason,
p_cash_discount,
p_volume_discount,
p_trading_discount,
p_transfer_charge,
p_transportation_charge,
p_insurance_charge,
p_other_charge,
p_receivables_trx_type_id,
p_ctrl_hdr_tx_appl_flag,
p_ctrl_total_line_tx_amt,
1, --p_object_version_number,
p_created_by,
p_creation_date,
p_last_updated_by,
p_last_update_date,
p_last_update_login);
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row',
'Insert into ZX_TRANSACTION_LINES (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Row.END',
'ZX_TRX_DETAIL: Insert_Row (-)');
END Insert_row;
PROCEDURE Update_row
(p_Rowid VARCHAR2,
p_internal_organization_id NUMBER,
p_internal_org_location_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_event_type_code VARCHAR2,
p_trx_id NUMBER,
p_trx_date DATE,
p_trx_doc_revision VARCHAR2,
p_ledger_id NUMBER,
p_trx_currency_code VARCHAR2,
p_currency_conversion_date DATE,
p_currency_conversion_rate NUMBER,
p_currency_conversion_type VARCHAR2,
p_minimum_accountable_unit NUMBER,
p_precision NUMBER,
p_legal_entity_id NUMBER,
p_rounding_ship_to_party_id NUMBER,
p_rounding_ship_from_party_id NUMBER,
p_rounding_bill_to_party_id NUMBER,
p_rounding_bill_from_party_id NUMBER,
p_rndg_ship_to_party_site_id NUMBER,
p_rndg_ship_from_pty_site_id NUMBER, --reduced size p_rndg_ship_from_party_site_id
p_rndg_bill_to_party_site_id NUMBER,
p_rndg_bill_from_pty_site_id NUMBER, --reduced size p_rndg_bill_from_party_site_id
p_establishment_id NUMBER,
p_related_doc_application_id NUMBER,
p_related_doc_entity_code VARCHAR2,
p_related_doc_evt_class_code VARCHAR2, --reduced size p_related_doc_event_class_code
p_related_doc_trx_id NUMBER,
p_related_doc_number VARCHAR2,
p_related_doc_date DATE,
p_default_taxation_country VARCHAR2,
p_quote_flag VARCHAR2,
p_trx_number VARCHAR2,
p_trx_description VARCHAR2,
p_trx_communicated_date DATE,
p_batch_source_id NUMBER,
p_batch_source_name VARCHAR2,
--p_doc_seq_id NUMBER,
--p_doc_seq_name VARCHAR2,
--p_doc_seq_value VARCHAR2,
p_trx_due_date DATE,
p_trx_type_description VARCHAR2,
p_billing_trad_partner_name VARCHAR2, --reduced size p_billing_trading_partner_name
p_billing_trad_partner_number VARCHAR2, --reduced size p_billing_trading_partner_number
p_billing_tp_tax_report_flg VARCHAR2, --reduced size p_Billing_Tp_Tax_Reporting_Flag
p_billing_tp_taxpayer_id VARCHAR2,
p_document_sub_type VARCHAR2,
p_supplier_tax_invoice_number VARCHAR2,
p_supplier_tax_invoice_date DATE,
p_supplier_exchange_rate NUMBER,
p_tax_invoice_date DATE,
p_tax_invoice_number VARCHAR2,
p_tax_event_class_code VARCHAR2,
p_tax_event_type_code VARCHAR2,
p_doc_event_status VARCHAR2,
p_rdng_ship_to_pty_tx_prof_id NUMBER,
p_rdng_ship_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_prof_id
p_rdng_bill_to_pty_tx_prof_id NUMBER,
p_rdng_bill_fr_pty_tx_prof_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_prof_id
p_rdng_ship_to_pty_tx_p_st_id NUMBER,
p_rdng_ship_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_ship_from_pty_tx_p_st_id
p_rdng_bill_to_pty_tx_p_st_id NUMBER,
p_rdng_bill_fr_pty_tx_p_st_id NUMBER, --reduced size p_rdng_bill_from_pty_tx_p_st_id
p_trx_level_type VARCHAR2,
p_trx_line_id NUMBER,
p_line_level_action VARCHAR2,
p_trx_shipping_date DATE,
p_trx_receipt_date DATE,
p_trx_line_type VARCHAR2,
p_trx_line_date DATE,
p_trx_business_category VARCHAR2,
p_line_intended_use VARCHAR2,
p_user_defined_fisc_class VARCHAR2,
p_line_amt NUMBER,
p_trx_line_quantity NUMBER,
p_unit_price NUMBER,
p_exempt_certificate_number VARCHAR2,
p_exempt_reason VARCHAR2,
p_cash_discount NUMBER,
p_volume_discount NUMBER,
p_trading_discount NUMBER,
p_transfer_charge NUMBER,
p_transportation_charge NUMBER,
p_insurance_charge NUMBER,
p_other_charge NUMBER,
p_product_id NUMBER,
p_product_fisc_classification VARCHAR2,
p_product_org_id NUMBER,
p_uom_code VARCHAR2,
p_product_type VARCHAR2,
p_product_code VARCHAR2,
p_product_category VARCHAR2,
p_trx_sic_code VARCHAR2,
p_fob_point VARCHAR2,
p_ship_to_party_id NUMBER,
p_ship_from_party_id NUMBER,
p_poa_party_id NUMBER,
p_poo_party_id NUMBER,
p_bill_to_party_id NUMBER,
p_bill_from_party_id NUMBER,
p_merchant_party_id NUMBER,
p_ship_to_party_site_id NUMBER,
p_ship_from_party_site_id NUMBER,
p_poa_party_site_id NUMBER,
p_poo_party_site_id NUMBER,
p_bill_to_party_site_id NUMBER,
p_bill_from_party_site_id NUMBER,
p_ship_to_location_id NUMBER,
p_ship_from_location_id NUMBER,
p_poa_location_id NUMBER,
p_poo_location_id NUMBER,
p_bill_to_location_id NUMBER,
p_bill_from_location_id NUMBER,
p_account_ccid NUMBER,
p_account_string VARCHAR2,
p_merchant_party_country VARCHAR2,
p_ref_doc_application_id NUMBER,
p_ref_doc_entity_code VARCHAR2,
p_ref_doc_event_class_code VARCHAR2,
p_ref_doc_trx_id NUMBER,
p_ref_doc_line_id NUMBER,
p_ref_doc_line_quantity NUMBER,
p_applied_from_application_id NUMBER,
p_applied_from_entity_code VARCHAR2,
p_applied_from_evt_class_code VARCHAR2, --reduced size p_applied_from_event_class_code
p_applied_from_trx_id NUMBER,
p_applied_from_line_id NUMBER,
p_adjusted_doc_application_id NUMBER,
p_adjusted_doc_entity_code VARCHAR2,
p_adj_doc_event_class_code VARCHAR2, --reduced size p_adjusted_doc_event_class_code
p_adjusted_doc_trx_id NUMBER,
p_adjusted_doc_line_id NUMBER,
p_adjusted_doc_number VARCHAR2,
p_adjusted_doc_date DATE,
p_applied_to_application_id NUMBER,
p_applied_to_entity_code VARCHAR2,
p_applied_to_event_class_code VARCHAR2,
p_applied_to_trx_id NUMBER,
p_applied_to_trx_line_id NUMBER,
p_trx_id_level2 NUMBER,
p_trx_id_level3 NUMBER,
p_trx_id_level4 NUMBER,
p_trx_id_level5 NUMBER,
p_trx_id_level6 NUMBER,
p_trx_line_number NUMBER,
p_trx_line_description VARCHAR2,
p_product_description VARCHAR2,
p_trx_waybill_number VARCHAR2,
p_trx_line_gl_date DATE,
p_merchant_party_name VARCHAR2,
p_merchant_party_doc_number VARCHAR2, --reduced size p_merchant_party_document_number
p_merchant_party_reference VARCHAR2,
p_merchant_party_taxpayer_id VARCHAR2,
p_merchant_pty_tax_reg_number VARCHAR2, --reduced size p_merchant_party_tax_reg_number
p_paying_party_id NUMBER,
p_own_hq_party_id NUMBER,
p_trading_hq_party_id NUMBER,
p_poi_party_id NUMBER,
p_pod_party_id NUMBER,
p_title_transfer_party_id NUMBER,
p_paying_party_site_id NUMBER,
p_own_hq_party_site_id NUMBER,
p_trading_hq_party_site_id NUMBER,
p_poi_party_site_id NUMBER,
p_pod_party_site_id NUMBER,
p_title_transfer_pty_site_id NUMBER, --reduced size p_title_transfer_party_site_id
p_paying_location_id NUMBER,
p_own_hq_location_id NUMBER,
p_trading_hq_location_id NUMBER,
p_poc_location_id NUMBER,
p_poi_location_id NUMBER,
p_pod_location_id NUMBER,
p_title_transfer_location_id NUMBER,
p_banking_tp_taxpayer_id VARCHAR2,
p_assessable_value NUMBER,
p_asset_flag VARCHAR2,
p_asset_number VARCHAR2,
p_asset_accum_depreciation NUMBER,
p_asset_type VARCHAR2,
p_asset_cost NUMBER,
p_ship_to_party_tax_prof_id NUMBER,
p_ship_from_party_tax_prof_id NUMBER,
p_poa_party_tax_prof_id NUMBER,
p_poo_party_tax_prof_id NUMBER,
p_paying_party_tax_prof_id NUMBER,
p_own_hq_party_tax_prof_id NUMBER,
p_trading_hq_pty_tax_prof_id NUMBER, --reduced size p_trading_hq_party_tax_prof_id
p_poi_party_tax_prof_id NUMBER,
p_pod_party_tax_prof_id NUMBER,
p_bill_to_party_tax_prof_id NUMBER,
p_bill_from_party_tax_prof_id NUMBER,
p_title_trans_pty_tax_prof_id NUMBER, --reduced size p_title_trans_party_tax_prof_id
p_ship_to_site_tax_prof_id NUMBER,
p_ship_from_site_tax_prof_id NUMBER,
p_poa_site_tax_prof_id NUMBER,
p_poo_site_tax_prof_id NUMBER,
p_paying_site_tax_prof_id NUMBER,
p_own_hq_site_tax_prof_id NUMBER,
p_trading_hq_site_tax_prof_id NUMBER,
p_poi_site_tax_prof_id NUMBER,
p_pod_site_tax_prof_id NUMBER,
p_bill_to_site_tax_prof_id NUMBER,
p_bill_from_site_tax_prof_id NUMBER,
p_title_trn_site_tax_prof_id NUMBER, --reduced size p_title_trans_site_tax_prof_id
p_merchant_party_tax_prof_id NUMBER,
p_line_amt_includes_tax_flag VARCHAR2,
p_historical_flag VARCHAR2,
p_tax_classification_code VARCHAR2,
p_ctrl_hdr_tx_appl_flag VARCHAR2,
p_ctrl_total_line_tx_amt NUMBER,
p_tax_regime_id NUMBER,
p_tax_regime_code VARCHAR2,
p_tax_id NUMBER,
p_tax VARCHAR2,
p_tax_status_id NUMBER,
p_tax_status_code VARCHAR2,
p_tax_rate_id NUMBER,
p_tax_rate_code VARCHAR2,
p_tax_rate NUMBER,
p_tax_line_amt NUMBER,
p_line_class VARCHAR2,
p_input_tax_classif_code VARCHAR2,
p_output_tax_classif_code VARCHAR2,
p_ref_doc_trx_level_type VARCHAR2,
p_applied_to_trx_level_type VARCHAR2,
p_applied_from_trx_level_type VARCHAR2,
p_adjusted_doc_trx_level_type VARCHAR2,
p_exemption_control_flag VARCHAR2,
p_exempt_reason_code VARCHAR2,
p_receivables_trx_type_id NUMBER,
p_object_version_number NUMBER,
p_created_by NUMBER,
p_creation_date DATE,
p_last_updated_by NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER) IS
l_return_status VARCHAR2(30);
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row.BEGIN',
'ZX_TRX_DETAIL: Update_Row (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row',
'Update ZX_TRANSACTION_LINES (+)');
UPDATE ZX_TRANSACTION_LINES
SET TRX_LINE_DESCRIPTION = p_trx_line_description,
TRX_LINE_TYPE = p_trx_line_type,
PRODUCT_ID = p_product_id,
PRODUCT_ORG_ID = p_product_org_id,
PRODUCT_CODE = p_product_code,
PRODUCT_TYPE = p_product_type,
PRODUCT_DESCRIPTION = p_product_description,
TRX_LINE_QUANTITY = p_trx_line_quantity,
UOM_CODE = p_uom_code,
UNIT_PRICE = p_unit_price,
LINE_AMT = p_line_amt,
PRODUCT_CATEGORY = p_product_category,
TRX_LINE_DATE = p_trx_line_date,
LINE_INTENDED_USE = p_line_intended_use,
USER_DEFINED_FISC_CLASS = p_user_defined_fisc_class,
TRX_BUSINESS_CATEGORY = p_trx_business_category,
ACCOUNT_CCID = p_account_ccid,
ACCOUNT_STRING = p_account_string,
TRX_LINE_GL_DATE = p_trx_line_gl_date,
LINE_LEVEL_ACTION = p_line_level_action,
MERCHANT_PARTY_ID = p_merchant_party_id,
MERCHANT_PARTY_COUNTRY = p_merchant_party_country,
BILL_FROM_PARTY_SITE_ID = p_bill_from_party_site_id,
BILL_TO_PARTY_SITE_ID = p_bill_to_party_site_id,
SHIP_FROM_PARTY_SITE_ID = p_ship_from_party_site_id,
SHIP_TO_PARTY_SITE_ID = p_ship_to_party_site_id,
SHIP_TO_PARTY_ID = p_ship_to_party_id,
SHIP_FROM_PARTY_ID = p_ship_from_party_id,
BILL_TO_PARTY_ID = p_bill_to_party_id,
BILL_FROM_PARTY_ID = p_bill_from_party_id,
SHIP_TO_LOCATION_ID = p_ship_to_location_id,
SHIP_FROM_LOCATION_ID = p_ship_from_location_id,
BILL_TO_LOCATION_ID = p_bill_to_location_id,
BILL_FROM_LOCATION_ID = p_bill_from_location_id,
POA_LOCATION_ID = p_poa_location_id,
POO_LOCATION_ID = p_poo_location_id,
PAYING_LOCATION_ID = p_paying_location_id,
OWN_HQ_LOCATION_ID = p_own_hq_location_id,
TRADING_HQ_LOCATION_ID = p_trading_hq_location_id,
POC_LOCATION_ID = p_poc_location_id,
POI_LOCATION_ID = p_poi_location_id,
POD_LOCATION_ID = p_pod_location_id,
POA_PARTY_ID = p_poa_party_id,
POO_PARTY_ID = p_poo_party_id,
POA_PARTY_SITE_ID = p_poa_party_site_id,
POO_PARTY_SITE_ID = p_poo_party_site_id,
REF_DOC_APPLICATION_ID = p_ref_doc_application_id,
REF_DOC_ENTITY_CODE = p_ref_doc_entity_code,
REF_DOC_EVENT_CLASS_CODE = p_ref_doc_event_class_code,
REF_DOC_TRX_ID = p_ref_doc_trx_id,
REF_DOC_LINE_ID = p_ref_doc_line_id,
REF_DOC_LINE_QUANTITY = p_ref_doc_line_quantity,
ADJUSTED_DOC_APPLICATION_ID = p_adjusted_doc_application_id,
ADJUSTED_DOC_ENTITY_CODE = p_adjusted_doc_entity_code,
ADJUSTED_DOC_EVENT_CLASS_CODE = p_adj_doc_event_class_code,
ADJUSTED_DOC_TRX_ID = p_adjusted_doc_trx_id,
ADJUSTED_DOC_LINE_ID = p_adjusted_doc_line_id,
ADJUSTED_DOC_DATE = p_adjusted_doc_date,
APPLIED_FROM_APPLICATION_ID = p_applied_from_application_id,
APPLIED_FROM_ENTITY_CODE = p_applied_from_entity_code,
APPLIED_FROM_EVENT_CLASS_CODE = p_applied_from_evt_class_code,
APPLIED_FROM_TRX_ID = p_applied_from_trx_id,
APPLIED_FROM_LINE_ID = p_applied_from_line_id,
APPLIED_TO_APPLICATION_ID = p_applied_to_application_id,
APPLIED_TO_ENTITY_CODE = p_applied_to_entity_code,
APPLIED_TO_EVENT_CLASS_CODE = p_applied_to_event_class_code,
APPLIED_TO_TRX_ID = p_applied_to_trx_id,
APPLIED_TO_TRX_LINE_ID = p_applied_to_trx_line_id,
PRODUCT_FISC_CLASSIFICATION = p_product_fisc_classification,
LINE_CLASS = p_line_class,
INPUT_TAX_CLASSIFICATION_CODE = p_input_tax_classif_code,
OUTPUT_TAX_CLASSIFICATION_CODE = p_output_tax_classif_code,
REF_DOC_TRX_LEVEL_TYPE = p_ref_doc_trx_level_type,
APPLIED_TO_TRX_LEVEL_TYPE = p_applied_to_trx_level_type,
APPLIED_FROM_TRX_LEVEL_TYPE = p_applied_from_trx_level_type,
ADJUSTED_DOC_TRX_LEVEL_TYPE = p_adjusted_doc_trx_level_type,
EXEMPTION_CONTROL_FLAG = p_exemption_control_flag,
EXEMPT_REASON_CODE = p_exempt_reason_code,
EXEMPT_CERTIFICATE_NUMBER = p_exempt_certificate_number,
EXEMPT_REASON = p_exempt_reason,
CASH_DISCOUNT = p_cash_discount,
VOLUME_DISCOUNT = p_volume_discount,
TRADING_DISCOUNT = p_trading_discount,
TRANSFER_CHARGE = p_transfer_charge,
TRANSPORTATION_CHARGE = p_transportation_charge,
INSURANCE_CHARGE = p_insurance_charge,
OTHER_CHARGE = p_other_charge,
RECEIVABLES_TRX_TYPE_ID = p_receivables_trx_type_id,
CTRL_HDR_TX_APPL_FLAG = p_ctrl_hdr_tx_appl_flag,
CTRL_TOTAL_LINE_TX_AMT = p_ctrl_total_line_tx_amt,
LINE_AMT_INCLUDES_TAX_FLAG = p_line_amt_includes_tax_flag,
HISTORICAL_FLAG = p_historical_flag,
OBJECT_VERSION_NUMBER = NVL(p_object_version_number, OBJECT_VERSION_NUMBER + 1),
CREATED_BY = p_created_by,
CREATION_DATE = p_creation_date,
LAST_UPDATED_BY = p_last_updated_by,
LAST_UPDATE_DATE = p_last_update_date,
LAST_UPDATE_LOGIN = p_last_update_login
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND TRX_LEVEL_TYPE = p_trx_level_type
AND TRX_LINE_ID = p_trx_line_id;
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row',
'Update ZX_TRANSACTION_LINES (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Row.END',
'ZX_TRX_DETAIL: Update_Row (-)');
END Update_row;
PROCEDURE Delete_row
(p_Rowid VARCHAR2,
p_internal_organization_id NUMBER,
p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_event_type_code VARCHAR2,
p_trx_id NUMBER,
p_trx_level_type VARCHAR2,
p_trx_line_id NUMBER
) IS
l_return_status VARCHAR2(30);
'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row.BEGIN',
'ZX_TRX_DETAIL: Delete_Row (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row',
'Deleting ZX_TRANSACTION_LINES');
DELETE ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND TRX_LEVEL_TYPE = p_trx_level_type
AND TRX_LINE_ID = p_trx_line_id;
'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row',
'Deleted ZX_TRANSACTION_LINES');
'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row',
'Calling ZX_API_PUB.Mark_tax_lines_deleted');
ZX_API_PUB.Mark_tax_lines_deleted
(p_api_version => 1.0,
p_init_msg_list => NULL,
p_commit => NULL,
p_validation_level => NULL,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_transaction_line_rec => l_transaction_line_rec);
'ZX.PLSQL.ZX_TRX_DETAIL.Delete_Row.END',
'ZX_TRX_DETAIL: Delete_Row (-)');
END Delete_row;
p_last_updated_by NUMBER,
p_last_update_date DATE,
p_last_update_login NUMBER) IS
CURSOR C IS
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_LINE_ID,
TRX_LINE_NUMBER,
TRX_ID,
TRX_LEVEL_TYPE,
TRX_LINE_TYPE,
TRX_LINE_DATE,
TRX_BUSINESS_CATEGORY,
LINE_INTENDED_USE,
USER_DEFINED_FISC_CLASS,
LINE_AMT_INCLUDES_TAX_FLAG,
LINE_AMT,
TRX_LINE_QUANTITY,
UNIT_PRICE,
PRODUCT_ID,
PRODUCT_FISC_CLASSIFICATION,
PRODUCT_ORG_ID,
UOM_CODE,
PRODUCT_TYPE,
PRODUCT_CODE,
PRODUCT_CATEGORY,
MERCHANT_PARTY_ID,
ACCOUNT_CCID,
ACCOUNT_STRING,
REF_DOC_LINE_ID,
REF_DOC_LINE_QUANTITY,
REF_DOC_APPLICATION_ID,
REF_DOC_ENTITY_CODE,
REF_DOC_EVENT_CLASS_CODE,
REF_DOC_TRX_ID,
APPLIED_FROM_LINE_ID,
APPLIED_FROM_APPLICATION_ID,
APPLIED_FROM_ENTITY_CODE,
APPLIED_FROM_EVENT_CLASS_CODE,
APPLIED_FROM_TRX_ID,
ADJUSTED_DOC_LINE_ID,
ADJUSTED_DOC_DATE,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_ENTITY_CODE,
ADJUSTED_DOC_EVENT_CLASS_CODE,
ADJUSTED_DOC_TRX_ID,
TRX_LINE_DESCRIPTION,
PRODUCT_DESCRIPTION,
TRX_LINE_GL_DATE,
LINE_LEVEL_ACTION,
HISTORICAL_FLAG,
BILL_FROM_PARTY_SITE_ID,
BILL_TO_PARTY_SITE_ID,
SHIP_FROM_PARTY_SITE_ID,
SHIP_TO_PARTY_SITE_ID,
SHIP_TO_PARTY_ID,
SHIP_FROM_PARTY_ID,
BILL_TO_PARTY_ID,
BILL_FROM_PARTY_ID,
SHIP_TO_LOCATION_ID,
SHIP_FROM_LOCATION_ID,
BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID,
POA_LOCATION_ID,
POO_LOCATION_ID,
PAYING_LOCATION_ID,
OWN_HQ_LOCATION_ID,
TRADING_HQ_LOCATION_ID,
POC_LOCATION_ID,
POI_LOCATION_ID,
POD_LOCATION_ID,
TAX_REGIME_ID,
TAX_REGIME_CODE,
TAX_ID,
TAX,
TAX_STATUS_ID,
TAX_STATUS_CODE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE,
TAX_LINE_AMT,
LINE_CLASS,
INPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_CLASSIFICATION_CODE,
REF_DOC_TRX_LEVEL_TYPE,
APPLIED_TO_TRX_LEVEL_TYPE,
APPLIED_FROM_TRX_LEVEL_TYPE,
ADJUSTED_DOC_TRX_LEVEL_TYPE,
EXEMPTION_CONTROL_FLAG,
EXEMPT_REASON_CODE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON,
CASH_DISCOUNT,
VOLUME_DISCOUNT,
TRADING_DISCOUNT,
TRANSFER_CHARGE,
TRANSPORTATION_CHARGE,
INSURANCE_CHARGE,
OTHER_CHARGE,
RECEIVABLES_TRX_TYPE_ID,
CTRL_HDR_TX_APPL_FLAG,
CTRL_TOTAL_LINE_TX_AMT,
OBJECT_VERSION_NUMBER,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_LINE_ID = p_trx_line_id
AND TRX_LEVEL_TYPE = p_trx_level_type
AND TRX_ID = p_trx_id
FOR UPDATE OF APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_LINE_ID,
TRX_LEVEL_TYPE,
TRX_ID
NOWAIT;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
(Recinfo.LAST_UPDATED_BY = p_LAST_UPDATED_BY) AND
(Recinfo.LAST_UPDATE_DATE = p_LAST_UPDATE_DATE) AND
((Recinfo.LAST_UPDATE_LOGIN = p_LAST_UPDATE_LOGIN) OR
((Recinfo.LAST_UPDATE_LOGIN IS NULL) AND
(p_LAST_UPDATE_LOGIN IS NULL))) ) THEN
return;
PROCEDURE Insert_Temporary_Table
(p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_trx_id NUMBER,
p_event_type_code VARCHAR2,
p_ledger_id NUMBER,
p_reporting_currency_code VARCHAR2,
p_currency_conversion_date DATE,
p_currency_conversion_type VARCHAR2,
p_currency_conversion_rate NUMBER,
p_minimum_accountable_unit NUMBER,
p_status VARCHAR2 DEFAULT NULL,
p_precision NUMBER,
p_line_level_action VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2 ) IS
l_return_status VARCHAR2(30);
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
TRX_LEVEL_TYPE,
TRX_LINE_ID,
NULL TRX_WAYBILL_NUMBER,
TRX_LINE_DESCRIPTION,
PRODUCT_DESCRIPTION,
TRX_LINE_GL_DATE,
NULL MERCHANT_PARTY_NAME,
NULL MERCHANT_PARTY_DOCUMENT_NUMBER,
NULL MERCHANT_PARTY_REFERENCE,
NULL MERCHANT_PARTY_TAXPAYER_ID,
NULL MERCHANT_PARTY_TAX_REG_NUMBER,
NULL ASSET_NUMBER
FROM ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND TRX_LINE_TYPE <> 'TAX';
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table.BEGIN',
'ZX_TRX_DETAIL: Insert_Temporary_Table (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Event: Event Type Code :'||p_event_type_code);
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Tables',
'Insert into zx_trx_headers_gt (+)');
INSERT INTO ZX_TRX_HEADERS_GT (INTERNAL_ORGANIZATION_ID,
INTERNAL_ORG_LOCATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
TRX_ID,
TRX_DATE,
--TRX_DOC_REVISION,
LEDGER_ID,
TRX_CURRENCY_CODE,
CURRENCY_CONVERSION_DATE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_TYPE,
MINIMUM_ACCOUNTABLE_UNIT,
PRECISION,
LEGAL_ENTITY_ID,
ROUNDING_SHIP_TO_PARTY_ID,
ROUNDING_SHIP_FROM_PARTY_ID,
ROUNDING_BILL_TO_PARTY_ID,
ROUNDING_BILL_FROM_PARTY_ID,
RNDG_SHIP_TO_PARTY_SITE_ID,
RNDG_SHIP_FROM_PARTY_SITE_ID,
RNDG_BILL_TO_PARTY_SITE_ID,
RNDG_BILL_FROM_PARTY_SITE_ID,
ESTABLISHMENT_ID,
RECEIVABLES_TRX_TYPE_ID,
--RELATED_DOC_APPLICATION_ID,
--RELATED_DOC_ENTITY_CODE,
--RELATED_DOC_EVENT_CLASS_CODE,
--RELATED_DOC_TRX_ID,
--REL_DOC_HDR_TRX_USER_KEY1,
--REL_DOC_HDR_TRX_USER_KEY2,
--REL_DOC_HDR_TRX_USER_KEY3,
--REL_DOC_HDR_TRX_USER_KEY4,
--REL_DOC_HDR_TRX_USER_KEY5,
--REL_DOC_HDR_TRX_USER_KEY6,
--RELATED_DOC_NUMBER,
--RELATED_DOC_DATE,
DEFAULT_TAXATION_COUNTRY,
Quote_Flag,
CTRL_TOTAL_HDR_TX_AMT,
TRX_NUMBER,
TRX_DESCRIPTION,
--TRX_COMMUNICATED_DATE,
--BATCH_SOURCE_ID,
--BATCH_SOURCE_NAME,
--DOC_SEQ_ID,
--DOC_SEQ_NAME,
--DOC_SEQ_VALUE,
--TRX_DUE_DATE,
--TRX_TYPE_DESCRIPTION,
--BILLING_TRADING_PARTNER_NAME,
--BILLING_TRADING_PARTNER_NUMBER,
--Billing_Tp_Tax_Reporting_Flag,
--BILLING_TP_TAXPAYER_ID,
DOCUMENT_SUB_TYPE,
SUPPLIER_TAX_INVOICE_NUMBER,
SUPPLIER_TAX_INVOICE_DATE,
SUPPLIER_EXCHANGE_RATE,
TAX_INVOICE_DATE,
TAX_INVOICE_NUMBER,
FIRST_PTY_ORG_ID,
PORT_OF_ENTRY_CODE,
TAX_REPORTING_FLAG,
SHIP_TO_CUST_ACCT_SITE_USE_ID,
BILL_TO_CUST_ACCT_SITE_USE_ID,
PROVNL_TAX_DETERMINATION_DATE,
APPLIED_TO_TRX_NUMBER,
SHIP_THIRD_PTY_ACCT_ID,
BILL_THIRD_PTY_ACCT_ID,
SHIP_THIRD_PTY_ACCT_SITE_ID,
BILL_THIRD_PTY_ACCT_SITE_ID,
VALIDATION_CHECK_FLAG,
--TAX_EVENT_CLASS_CODE,
TAX_EVENT_TYPE_CODE
--DOC_EVENT_STATUS,
--RDNG_SHIP_TO_PTY_TX_PROF_ID,
--RDNG_SHIP_FROM_PTY_TX_PROF_ID,
--RDNG_BILL_TO_PTY_TX_PROF_ID,
--RDNG_BILL_FROM_PTY_TX_PROF_ID,
--RDNG_SHIP_TO_PTY_TX_P_ST_ID,
--RDNG_SHIP_FROM_PTY_TX_P_ST_ID,
--RDNG_BILL_TO_PTY_TX_P_ST_ID,
--RDNG_BILL_FROM_PTY_TX_P_ST_ID
)
SELECT internal_organization_id,
internal_org_location_id,
application_id,
entity_code,
event_class_code,
event_type_code,
trx_id,
trx_date,
--p_trx_doc_revision,
ledger_id,
trx_currency_code,
currency_conversion_date,
currency_conversion_rate,
currency_conversion_type,
minimum_accountable_unit,
precision,
legal_entity_id,
rounding_ship_to_party_id,
rounding_ship_from_party_id,
rounding_bill_to_party_id,
rounding_bill_from_party_id,
rndg_ship_to_party_site_id,
rndg_ship_from_party_site_id,
rndg_bill_to_party_site_id,
rndg_bill_from_party_site_id,
establishment_id,
receivables_trx_type_id,
--p_related_doc_application_id,
--p_related_doc_entity_code,
--p_related_doc_evt_class_code, --reduced size p_related_doc_event_class_code
--p_related_doc_trx_id,
--p_rel_doc_hdr_trx_user_key1,
--p_rel_doc_hdr_trx_user_key2,
--p_rel_doc_hdr_trx_user_key3,
--p_rel_doc_hdr_trx_user_key4,
--p_rel_doc_hdr_trx_user_key5,
--p_rel_doc_hdr_trx_user_key6,
--p_related_doc_number,
--p_related_doc_date,
default_taxation_country,
Quote_Flag,
ctrl_total_hdr_tx_amt,
trx_number,
trx_description,
--p_trx_communicated_date,
--p_batch_source_id,
--p_batch_source_name,
--p_doc_seq_id,
--p_doc_seq_name,
--p_doc_seq_value,
--p_trx_due_date,
--p_trx_type_description,
--p_billing_trad_partner_name, --reduced size p_billing_trading_partner_name
--p_billing_trad_partner_number, --reduced size p_billing_trading_partner_number
--p_billing_tp_tax_report_flg, --reduced size p_Billing_Tp_Tax_Reporting_Flag
--p_billing_tp_taxpayer_id,
document_sub_type,
supplier_tax_invoice_number,
supplier_tax_invoice_date,
supplier_exchange_rate,
tax_invoice_date,
tax_invoice_number,
first_pty_org_id,
port_of_entry_code,
tax_reporting_flag,
ship_to_cust_acct_site_use_id,
bill_to_cust_acct_site_use_id,
provnl_tax_determination_date,
applied_to_trx_number,
ship_third_pty_acct_id,
bill_third_pty_acct_id,
ship_third_pty_acct_site_id,
bill_third_pty_acct_site_id,
validation_check_flag,
--p_tax_event_class_code,
tax_event_type_code--p_tax_event_type_code,
--p_doc_event_status,
--p_rdng_ship_to_pty_tx_prof_id,
--p_rdng_ship_fr_pty_tx_prof_id, --reduced size p_rdng_ship_from_pty_tx_prof_id
--p_rdng_bill_to_pty_tx_prof_id,
--p_rdng_bill_fr_pty_tx_prof_id, --reduced size p_rdng_bill_from_pty_tx_prof_id
--p_rdng_ship_to_pty_tx_p_st_id,
--p_rdng_ship_fr_pty_tx_p_st_id, --reduced size p_rdng_ship_from_pty_tx_p_st_id
--p_rdng_bill_to_pty_tx_p_st_id,
--p_rdng_bill_fr_pty_tx_p_st_id); --reduced size p_rdng_bill_from_pty_tx_p_st_id
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Insert into zx_trx_headers_gt (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Insert into ZX_TRANSACTION_LINES_GT (+)');
INSERT INTO ZX_TRANSACTION_LINES_GT (APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
TRX_LEVEL_TYPE,
TRX_LINE_ID,
LINE_LEVEL_ACTION,
TRX_SHIPPING_DATE,
TRX_RECEIPT_DATE,
TRX_LINE_TYPE,
TRX_LINE_DATE,
TRX_BUSINESS_CATEGORY,
LINE_INTENDED_USE,
USER_DEFINED_FISC_CLASS,
LINE_AMT,
TRX_LINE_QUANTITY,
UNIT_PRICE,
EXEMPT_CERTIFICATE_NUMBER,
EXEMPT_REASON,
CASH_DISCOUNT,
VOLUME_DISCOUNT,
TRADING_DISCOUNT,
TRANSFER_CHARGE,
TRANSPORTATION_CHARGE,
INSURANCE_CHARGE,
OTHER_CHARGE,
PRODUCT_ID,
PRODUCT_FISC_CLASSIFICATION,
PRODUCT_ORG_ID,
UOM_CODE,
PRODUCT_TYPE,
PRODUCT_CODE,
PRODUCT_CATEGORY,
--TRX_SIC_CODE,
--FOB_POINT,
SHIP_TO_PARTY_ID,
SHIP_FROM_PARTY_ID,
POA_PARTY_ID,
POO_PARTY_ID,
BILL_TO_PARTY_ID,
BILL_FROM_PARTY_ID,
MERCHANT_PARTY_ID,
SHIP_TO_PARTY_SITE_ID,
SHIP_FROM_PARTY_SITE_ID,
POA_PARTY_SITE_ID,
POO_PARTY_SITE_ID,
BILL_TO_PARTY_SITE_ID,
BILL_FROM_PARTY_SITE_ID,
SHIP_TO_LOCATION_ID,
SHIP_FROM_LOCATION_ID,
POA_LOCATION_ID,
POO_LOCATION_ID,
BILL_TO_LOCATION_ID,
BILL_FROM_LOCATION_ID,
ACCOUNT_CCID,
ACCOUNT_STRING,
MERCHANT_PARTY_COUNTRY,
REF_DOC_APPLICATION_ID,
REF_DOC_ENTITY_CODE,
REF_DOC_EVENT_CLASS_CODE,
REF_DOC_TRX_ID,
REF_DOC_LINE_ID,
REF_DOC_LINE_QUANTITY,
APPLIED_FROM_APPLICATION_ID,
APPLIED_FROM_ENTITY_CODE,
APPLIED_FROM_EVENT_CLASS_CODE,
APPLIED_FROM_TRX_ID,
APPLIED_FROM_LINE_ID,
ADJUSTED_DOC_APPLICATION_ID,
ADJUSTED_DOC_ENTITY_CODE,
ADJUSTED_DOC_EVENT_CLASS_CODE,
ADJUSTED_DOC_TRX_ID,
ADJUSTED_DOC_LINE_ID,
--ADJUSTED_DOC_NUMBER,
ADJUSTED_DOC_DATE,
APPLIED_TO_APPLICATION_ID,
APPLIED_TO_ENTITY_CODE,
APPLIED_TO_EVENT_CLASS_CODE,
APPLIED_TO_TRX_ID,
APPLIED_TO_TRX_LINE_ID,
--TRX_ID_LEVEL2,
--TRX_ID_LEVEL3,
--TRX_ID_LEVEL4,
--TRX_ID_LEVEL5,
--TRX_ID_LEVEL6,
TRX_LINE_NUMBER,
TRX_LINE_DESCRIPTION,
PRODUCT_DESCRIPTION,
--TRX_WAYBILL_NUMBER,
TRX_LINE_GL_DATE,
--MERCHANT_PARTY_NAME,
--MERCHANT_PARTY_DOCUMENT_NUMBER,
--MERCHANT_PARTY_REFERENCE,
--MERCHANT_PARTY_TAXPAYER_ID,
--MERCHANT_PARTY_TAX_REG_NUMBER,
--PAYING_PARTY_ID,
--OWN_HQ_PARTY_ID,
--TRADING_HQ_PARTY_ID,
--POI_PARTY_ID,
--POD_PARTY_ID,
--TITLE_TRANSFER_PARTY_ID,
--PAYING_PARTY_SITE_ID,
--OWN_HQ_PARTY_SITE_ID,
--TRADING_HQ_PARTY_SITE_ID,
--POI_PARTY_SITE_ID,
--POD_PARTY_SITE_ID,
--TITLE_TRANSFER_PARTY_SITE_ID,
PAYING_LOCATION_ID,
OWN_HQ_LOCATION_ID,
TRADING_HQ_LOCATION_ID,
POC_LOCATION_ID,
POI_LOCATION_ID,
POD_LOCATION_ID,
--TITLE_TRANSFER_LOCATION_ID,
--BANKING_TP_TAXPAYER_ID,
--ASSESSABLE_VALUE,
--ASSET_FLAG,
--ASSET_NUMBER,
--ASSET_ACCUM_DEPRECIATION,
--ASSET_TYPE,
--ASSET_COST,
--NUMERIC1,
--NUMERIC2,
--NUMERIC3,
--NUMERIC4,
--NUMERIC5,
--NUMERIC6,
--NUMERIC7,
--NUMERIC8,
--NUMERIC9,
--NUMERIC10,
--CHAR1,
--CHAR2,
--CHAR3,
--CHAR4,
--CHAR5,
--CHAR6,
--CHAR7,
--CHAR8,
--CHAR9,
--CHAR10,
--DATE1,
--DATE2,
--DATE3,
--DATE4,
--DATE5,
--DATE6,
--DATE7,
--DATE8,
--DATE9,
--DATE10,
--SHIP_TO_PARTY_TAX_PROF_ID,
--SHIP_FROM_PARTY_TAX_PROF_ID,
--POA_PARTY_TAX_PROF_ID,
--POO_PARTY_TAX_PROF_ID,
--PAYING_PARTY_TAX_PROF_ID,
--OWN_HQ_PARTY_TAX_PROF_ID,
--TRADING_HQ_PARTY_TAX_PROF_ID,
--POI_PARTY_TAX_PROF_ID,
--POD_PARTY_TAX_PROF_ID,
--BILL_TO_PARTY_TAX_PROF_ID,
--BILL_FROM_PARTY_TAX_PROF_ID,
--TITLE_TRANS_PARTY_TAX_PROF_ID,
--SHIP_TO_SITE_TAX_PROF_ID,
--SHIP_FROM_SITE_TAX_PROF_ID,
--POA_SITE_TAX_PROF_ID,
--POO_SITE_TAX_PROF_ID,
--PAYING_SITE_TAX_PROF_ID,
--OWN_HQ_SITE_TAX_PROF_ID,
--TRADING_HQ_SITE_TAX_PROF_ID,
--POI_SITE_TAX_PROF_ID,
--POD_SITE_TAX_PROF_ID,
--BILL_TO_SITE_TAX_PROF_ID,
--BILL_FROM_SITE_TAX_PROF_ID,
--TITLE_TRANS_SITE_TAX_PROF_ID,
--MERCHANT_PARTY_TAX_PROF_ID,
LINE_CLASS,
INPUT_TAX_CLASSIFICATION_CODE,
OUTPUT_TAX_CLASSIFICATION_CODE,
REF_DOC_TRX_LEVEL_TYPE,
APPLIED_TO_TRX_LEVEL_TYPE,
APPLIED_FROM_TRX_LEVEL_TYPE,
ADJUSTED_DOC_TRX_LEVEL_TYPE,
EXEMPTION_CONTROL_FLAG,
EXEMPT_REASON_CODE,
--RECEIVABLES_TRX_TYPE_ID, bug#4288610
CTRL_HDR_TX_APPL_FLAG,
CTRL_TOTAL_LINE_TX_AMT,
LINE_AMT_INCLUDES_TAX_FLAG,
HISTORICAL_FLAG
--TAX_CLASSIFICATION_CODE
)
SELECT application_id,
entity_code,
event_class_code,
trx_id,
trx_level_type,
trx_line_id,
line_level_action,
trx_shipping_date,
trx_receipt_date,
trx_line_type,
trx_line_date,
trx_business_category,
line_intended_use,
user_defined_fisc_class,
line_amt,
trx_line_quantity,
unit_price,
exempt_certificate_number,
exempt_reason,
cash_discount,
volume_discount,
trading_discount,
transfer_charge,
transportation_charge,
insurance_charge,
other_charge,
product_id,
product_fisc_classification,
product_org_id,
uom_code,
DECODE(product_type, 'MEMOS', NULL,
product_type) product_type,
product_code,
product_category,
--p_trx_sic_code,
--p_fob_point,
ship_to_party_id,
ship_from_party_id,
poa_party_id,
poo_party_id,
bill_to_party_id,
bill_from_party_id,
merchant_party_id,
ship_to_party_site_id,
ship_from_party_site_id,
poa_party_site_id,
poo_party_site_id,
bill_to_party_site_id,
bill_from_party_site_id,
ship_to_location_id,
ship_from_location_id,
poa_location_id,
poo_location_id,
bill_to_location_id,
bill_from_location_id,
account_ccid,
account_string,
merchant_party_country,
ref_doc_application_id,
ref_doc_entity_code,
ref_doc_event_class_code,
ref_doc_trx_id,
ref_doc_line_id,
ref_doc_line_quantity,
applied_from_application_id,
applied_from_entity_code,
applied_from_event_class_code,
applied_from_trx_id,
applied_from_line_id,
adjusted_doc_application_id,
adjusted_doc_entity_code,
adjusted_doc_event_class_code,
adjusted_doc_trx_id,
adjusted_doc_line_id,
--adjusted_doc_number,
adjusted_doc_date,
applied_to_application_id,
applied_to_entity_code,
applied_to_event_class_code,
applied_to_trx_id,
applied_to_trx_line_id,
--p_trx_id_level2,
--p_trx_id_level3,
--p_trx_id_level4,
--p_trx_id_level5,
--p_trx_id_level6,
trx_line_number,
trx_line_description,
product_description,
--trx_waybill_number,
trx_line_gl_date,
--p_merchant_party_name,
--p_merchant_party_doc_number,
--p_merchant_party_reference,
--p_merchant_party_taxpayer_id,
--p_merchant_pty_tax_reg_number,
--p_paying_party_id,
--p_own_hq_party_id,
--p_trading_hq_party_id,
--p_poi_party_id,
--p_pod_party_id,
--p_title_transfer_party_id,
--p_paying_party_site_id,
--p_own_hq_party_site_id,
--p_trading_hq_party_site_id,
--p_poi_party_site_id,
--p_pod_party_site_id,
--p_title_transfer_pty_site_id,
paying_location_id,
own_hq_location_id,
trading_hq_location_id,
poc_location_id,
poi_location_id,
pod_location_id,
--p_title_transfer_location_id,
--p_banking_tp_taxpayer_id,
--p_assessable_value,
--p_asset_flag,
--p_asset_number,
--p_asset_accum_depreciation,
--p_asset_type,
--p_asset_cost,
--p_numeric1,
--p_numeric2,
--p_numeric3,
--p_numeric4,
--p_numeric5,
--p_numeric6,
--p_numeric7,
--p_numeric8,
--p_numeric9,
--p_numeric10,
--p_char1,
--p_char2,
--p_char3,
--p_char4,
--p_char5,
--p_char6,
--p_char7,
--p_char8,
--p_char9,
--p_char10,
--p_date1,
--p_date2,
--p_date3,
--p_date4,
--p_date5,
--p_date6,
--p_date7,
--p_date8,
--p_date9,
--p_date10,
--p_ship_to_party_tax_prof_id,
--p_ship_from_party_tax_prof_id,
--p_poa_party_tax_prof_id,
--p_poo_party_tax_prof_id,
--p_paying_party_tax_prof_id,
--p_own_hq_party_tax_prof_id,
--p_trading_hq_pty_tax_prof_id,
--p_poi_party_tax_prof_id,
--p_pod_party_tax_prof_id,
--p_bill_to_party_tax_prof_id,
--p_bill_from_party_tax_prof_id,
--p_title_trans_pty_tax_prof_id,
--p_ship_to_site_tax_prof_id,
--p_ship_from_site_tax_prof_id,
--p_poa_site_tax_prof_id,
--p_poo_site_tax_prof_id,
--p_paying_site_tax_prof_id,
--p_own_hq_site_tax_prof_id,
--p_trading_hq_site_tax_prof_id,
--p_poi_site_tax_prof_id,
--p_pod_site_tax_prof_id,
--p_bill_to_site_tax_prof_id,
--p_bill_from_site_tax_prof_id,
--p_title_trn_site_tax_prof_id,
--p_merchant_party_tax_prof_id,
line_class,
input_tax_classification_code,
output_tax_classification_code,
ref_doc_trx_level_type,
applied_to_trx_level_type,
applied_from_trx_level_type,
adjusted_doc_trx_level_type,
nvl(exemption_control_flag,'S'), -- Bug 5211670
exempt_reason_code,
--receivables_trx_type_id, will add later
ctrl_hdr_tx_appl_flag,
ctrl_total_line_tx_amt,
line_amt_includes_tax_flag,
historical_flag
--p_tax_classification_code
FROM ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND TRX_LINE_TYPE <> 'TAX';
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Insert to ZX_TRANSACTION_LINES_GT, count : ' ||
TO_CHAR(SQL%ROWCOUNT));
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Insert into ZX_TRANSACTION_LINES_GT (-)');
IF p_event_type_code = 'STANDARD UPDATED' AND
p_line_level_action = 'SYNCHRONIZE' THEN
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
EVENT_TYPE_CODE,
TRX_ID,
TRX_NUMBER,
TRX_DESCRIPTION,
NULL TRX_COMMUNICATED_DATE,
NULL BATCH_SOURCE_ID,
NULL BATCH_SOURCE_NAME,
NULL DOC_SEQ_ID,
NULL DOC_SEQ_NAME,
NULL DOC_SEQ_VALUE,
NULL TRX_DUE_DATE,
NULL TRX_TYPE_DESCRIPTION,
NULL SUPPLIER_TAX_INVOICE_NUMBER,
NULL SUPPLIER_TAX_INVOICE_DATE,
NULL SUPPLIER_EXCHANGE_RATE,
NULL TAX_INVOICE_DATE,
NULL TAX_INVOICE_NUMBER,
NULL PORT_OF_ENTRY_CODE
INTO l_sync_trx_rec.APPLICATION_ID,
l_sync_trx_rec.ENTITY_CODE,
l_sync_trx_rec.EVENT_CLASS_CODE,
l_sync_trx_rec.EVENT_TYPE_CODE,
l_sync_trx_rec.TRX_ID,
l_sync_trx_rec.TRX_NUMBER,
l_sync_trx_rec.TRX_DESCRIPTION,
l_sync_trx_rec.TRX_COMMUNICATED_DATE,
l_sync_trx_rec.BATCH_SOURCE_ID,
l_sync_trx_rec.BATCH_SOURCE_NAME,
l_sync_trx_rec.DOC_SEQ_ID,
l_sync_trx_rec.DOC_SEQ_NAME,
l_sync_trx_rec.DOC_SEQ_VALUE,
l_sync_trx_rec.TRX_DUE_DATE,
l_sync_trx_rec.TRX_TYPE_DESCRIPTION,
l_sync_trx_rec.SUPPLIER_TAX_INVOICE_NUMBER,
l_sync_trx_rec.SUPPLIER_TAX_INVOICE_DATE,
l_sync_trx_rec.SUPPLIER_EXCHANGE_RATE,
l_sync_trx_rec.TAX_INVOICE_DATE,
l_sync_trx_rec.TAX_INVOICE_NUMBER,
l_sync_trx_rec.PORT_OF_ENTRY_CODE
FROM ZX_TRANSACTION
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'API ZX_SIM_CONDITIONS_PKG.create_sim_conditions (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'API ZX_SIM_CONDITIONS_PKG.create_sim_conditions (-)');
ELSIF p_event_type_code = 'STANDARD UPDATED' AND
p_line_level_action = 'SYNCHRONIZE' THEN
IF (g_level_procedure >= g_current_runtime_level ) THEN
FND_LOG.STRING(g_level_procedure,
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'API zx_api_pub.synchronize_tax_repository (+)');
SELECT COUNT(*)
INTO l_trx_lines_sync
FROM ZX_TRANSACTION_LINES
WHERE LINE_LEVEL_ACTION = 'SYNCHORONIZE'
AND APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
SELECT COUNT(*)
INTO l_trx_lines
FROM ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'After calling ZX_API_PUB.CALCULATE_TAX from 1,' ||
'l_return_status : ' || TO_CHAR(l_return_status) ||
' ' || 'l_msg_count :' || TO_CHAR(l_msg_count));
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'After calling ZX_API_PUB.CALCULATE_TAX from 2, ' ||
'l_return_status : ' || TO_CHAR(l_return_status) ||
' ' || 'l_msg_count :' || TO_CHAR(l_msg_count));
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'API zx_api_pub.synchronize_tax_repository (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'API zx_api_pub.calculate_tax for CREATE (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'After calling ZX_API_PUB.CALCULATE_TAX from 3,' ||
'l_return_status : ' || TO_CHAR(l_return_status) ||
' ' || 'l_msg_count :' || TO_CHAR(l_msg_count));
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'API zx_api_pub.calculate_tax for CREATE (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Update_Transaction_Lines (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Update_Transaction_Lines (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
'Return Status = ' || l_return_status);
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table.END',
'ZX_TRX_DETAIL: Insert_Temporary_Table (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Insert_Temporary_Table',
l_error_buffer);
END Insert_Temporary_Table;
PROCEDURE Update_Transaction_Lines
(p_application_id NUMBER,
p_entity_code VARCHAR2,
p_event_class_code VARCHAR2,
p_trx_id NUMBER) IS
l_return_status VARCHAR2(1000);
SELECT TLS.TRX_LINE_ID,
TLS.TRX_LINE_NUMBER
FROM ZX_TRANSACTION_LINES TLS
WHERE TLS.APPLICATION_ID = p_application_id
AND TLS.ENTITY_CODE = p_entity_code
AND TLS.EVENT_CLASS_CODE = p_event_class_code
AND TLS.TRX_ID = p_trx_id
AND TLS.TRX_LINE_TYPE = 'TAX';
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines.BEGIN',
'ZX_TRX_DETAIL: Update_Transaction_Lines (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
'Update ZX_TRANSACTION_LINES (+)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
'Delete ZX_TRANSACTION_LINES (+)');
DELETE FROM ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id
AND TRX_LINE_TYPE = 'TAX';
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
'Delete ZX_TRANSACTION_LINES (-)');
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
'Insert ZX_TRANSACTION_LINES (+)');
INSERT INTO ZX_TRANSACTION_LINES (APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_LINE_ID,
TRX_LINE_NUMBER,
TRX_ID,
TRX_LEVEL_TYPE,
TRX_LINE_TYPE,
TRX_LINE_DATE,
HISTORICAL_FLAG,
TAX_REGIME_ID,
TAX_REGIME_CODE,
TAX_ID,
TAX,
TAX_STATUS_ID,
TAX_STATUS_CODE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE,
TAX_LINE_AMT)
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
Recinfo.TRX_LINE_ID,
Recinfo.TRX_LINE_NUMBER,
TRX_ID,
TRX_LEVEL_TYPE,
'TAX' TRX_LINE_TYPE,
TRX_LINE_DATE,
HISTORICAL_FLAG,
TAX_REGIME_ID,
TAX_REGIME_CODE,
TAX_ID,
TAX,
TAX_STATUS_ID,
TAX_STATUS_CODE,
TAX_RATE_ID,
TAX_RATE_CODE,
TAX_RATE,
LINE_AMT
FROM ZX_LINES
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
'Insert ZX_TRANSACTION_LINES (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
'Update ZX_TRANSACTION_LINES (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines.END',
'ZX_TRX_DETAIL: Update_Transaction_Lines (-)');
'ZX.PLSQL.ZX_TRX_DETAIL.Update_Transaction_Lines',
p_error_buffer);
END Update_Transaction_Lines;
UPDATE ZX_LINES
SET
REPORTING_ONLY_FLAG = p_reporting_flag ,
LAST_UPDATED_BY = fnd_global.user_id ,
LAST_UPDATE_DATE = SYSDATE ,
LAST_UPDATE_LOGIN = fnd_global.conc_login_id ,
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
UPDATE ZX_LINES_SUMMARY
SET REPORTING_ONLY_FLAG = 'N'
WHERE APPLICATION_ID = p_application_id
AND ENTITY_CODE = p_entity_code
AND EVENT_CLASS_CODE = p_event_class_code
AND TRX_ID = p_trx_id;
DELETE ZX_LINES
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
DELETE ZX_LINES_SUMMARY
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
DELETE ZX_REC_NREC_DIST
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
DELETE ZX_LINES_DET_FACTORS
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
DELETE ZX_TRANSACTION
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
DELETE ZX_TRANSACTION_LINES
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
DELETE ZX_SIM_TRX_DISTS
WHERE APPLICATION_ID = p_application_id_tbl(i)
AND ENTITY_CODE = p_entity_code_tbl(i)
AND EVENT_CLASS_CODE = p_event_class_code_tbl(i)
AND TRX_ID = p_trx_id_tbl(i);
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID
FROM ZX_SIM_PURGE
WHERE TRX_ID = c_trx_id;
DELETE ZX_SIM_PURGE
WHERE TRX_ID = p_trx_id;
SELECT APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID
FROM ZX_SIM_PURGE;
DELETE ZX_SIM_PURGE;
'Inserting into ZX_IMPORT_TAX_LINES_GT (+)');
INSERT INTO ZX_IMPORT_TAX_LINES_GT (
SUMMARY_TAX_LINE_NUMBER,
INTERNAL_ORGANIZATION_ID,
APPLICATION_ID,
ENTITY_CODE,
EVENT_CLASS_CODE,
TRX_ID,
TAX_LINE_ALLOCATION_FLAG,
TAX_REGIME_CODE,
TAX,
TAX_STATUS_CODE,
TAX_RATE_CODE,
TAX_RATE,
TAX_AMT)
SELECT TRL.TRX_LINE_NUMBER,
TRX.INTERNAL_ORGANIZATION_ID,
TRL.APPLICATION_ID,
TRL.ENTITY_CODE,
TRL.EVENT_CLASS_CODE,
TRL.TRX_ID,
'N',
TRL.TAX_REGIME_CODE,
TRL.TAX,
TRL.TAX_STATUS_CODE,
TRL.TAX_RATE_CODE,
TRL.TAX_RATE,
TRL.TAX_LINE_AMT
FROM ZX_TRANSACTION_LINES TRL,
ZX_TRANSACTION TRX
WHERE TRL.TRX_ID = TRX.trx_id
AND TRL.APPLICATION_ID = p_application_id
AND TRL.ENTITY_CODE = p_entity_code
AND TRL.EVENT_CLASS_CODE = p_event_class_code
AND TRL.TRX_ID = p_trx_id
AND TRL.TRX_LINE_TYPE = 'TAX';
'Inserted into ZX_IMPORT_TAX_LINES_GT (-)');
SELECT message_text
FROM ZX_ERRORS_GT
WHERE trx_id = c_trx_id
AND event_class_code = c_event_class_code
AND application_id = c_application_id
AND entity_code = c_entity_code;