Diccionario de Datos
Diccionario de datos de los modelos de COÉXITO
| 🏠 Menú principal | ← Volver a vistas simples |
LACLS.LACLS_CO_MM_FINAL_V
Base de datos origen: PDB1
Tipo de objeto: VIEW
SQL
CREATE OR REPLACE FORCE EDITIONABLE VIEW "LACLS"."LACLS_CO_MM_FINAL_V" ("HEADER_ID", "FORMAT_ID", "FORMAT_CODE", "REPORTED_YEAR", "DESCRIPTION", "CONCEPT_ID", "COMPANY_ID", "CONCEPT_CODE", "LEDGER_ID", "PERIOD_NAME", "PARTY_ID", "NIT", "VERIFYING_DIGIT", "DOCUMENT_TYPE", "NAME", "FIRST_NAME", "SECOND_NAME", "FIRST_SURNAME", "SECOND_SURNAME", "COUNTRY_CODE", "STATE_CODE", "CITY_CODE", "ADDRESS", "PHONE_NUMBER", "PHONE_NUMBER2", "CELL_PHONE", "ECONOMIC_ACTIVITY", "EMAIL", "NATURE", "CODE_COMBINATION_ID", "ORIGIN", "SOURCE_ID", "CODE_COMBINATION_ID_RELATED", "ATTRIBUTE_CATEGORY", "REPORTED_FLAG", "LINE_TYPE_LOOKUP_CODE", "TAX_TYPE", "TAX_NAME", "ACCOUNT_NUMBER_RELATED", "CATEGORY", "ACCOUNT_NUMBER", "THIRD_PARTY_REPORT", "REPORTED_VALUE_1", "REPORTED_VALUE_2", "REPORTED_VALUE_3", "REPORTED_VALUE_4", "REPORTED_VALUE_5", "REPORTED_VALUE_6", "REPORTED_VALUE_7", "REPORTED_VALUE_8", "REPORTED_VALUE_9", "REPORTED_VALUE_10", "REPORTED_VALUE_11", "REPORTED_VALUE_12", "REPORTED_VALUE_13", "REPORTED_VALUE_14", "REPORTED_VALUE_15", "REPORTED_VALUE_16", "REPORTED_VALUE_17", "REPORTED_VALUE_18", "REPORTED_VALUE_19", "REPORTED_VALUE_20") AS
SELECT mml.header_id
/* $Header: LACLS_CO_MM_FINAL_V.vw 11.1.1113.3 2023/01/03 15:51:51 JFORERO noship $ */
, mml.format_id
, mmf.format_code
, mmf.reported_year
, mmf.description description
, mml.concept_id
, mmh.company_id
, mmc.concept_code
, mml.ledger_id
, mml.period_name
, mml.party_id
, NVL(mmp.nit,to_char(mml.party_id)) nit
, mmp.verifying_digit
, mmp.document_type
, NVL(mmp.name,'***** TERCERO NO IMPORTADO *****') name
, mmp.first_name
, mmp.second_name
, mmp.first_surname
, mmp.second_surname
-- , mmp.country_code
, (select lpad(LCDC.DANE_CODE_ID,3,'0')
from LACLS_CO_DANE_COUNTRY_CODES LCDC
where LCDC.GEOGRAPHY_CODE = mmp.country_code) country_code
, mmp.state_code
, mmp.city_code
, mmp.address
, mmp.phone_number
, mmp.phone_number2
, mmp.cell_phone
, mmp.economic_activity
, mmp.email
, mmp.nature
, mml.code_combination_id
, mml.origin
, mml.source_id
, mml.code_combination_id_related
, mml.attribute_category
, mml.reported_flag
, mml.line_type_lookup_code
, mml.tax_type
, mml.tax_name
, mml.account_number_related
, mml.category
, mml.account_number
, mml.third_party_report
, ROUND(SUM(mml.reported_value_1)) reported_value_1
, ROUND(SUM(mml.reported_value_2)) reported_value_2
, ROUND(SUM(mml.reported_value_3)) reported_value_3
, ROUND(SUM(mml.reported_value_4)) reported_value_4
, ROUND(SUM(mml.reported_value_5)) reported_value_5
, ROUND(SUM(mml.reported_value_6)) reported_value_6
, ROUND(SUM(mml.reported_value_7)) reported_value_7
, ROUND(SUM(mml.reported_value_8)) reported_value_8
, ROUND(SUM(mml.reported_value_9)) reported_value_9
, ROUND(SUM(mml.reported_value_10)) reported_value_10
, ROUND(SUM(mml.reported_value_11)) reported_value_11
, ROUND(SUM(mml.reported_value_12)) reported_value_12
, ROUND(SUM(mml.reported_value_13)) reported_value_13
, ROUND(SUM(mml.reported_value_14)) reported_value_14
, ROUND(SUM(mml.reported_value_15)) reported_value_15
, ROUND(SUM(mml.reported_value_16)) reported_value_16
, ROUND(SUM(mml.reported_value_17)) reported_value_17
, ROUND(SUM(mml.reported_value_18)) reported_value_18
, ROUND(SUM(mml.reported_value_19)) reported_value_19
, ROUND(SUM(mml.reported_value_20)) reported_value_20
FROM lacls_co_mm_header mmh
, lacls_co_mm_lines mml
, lacls_co_mm_concepts mmc
, lacls_co_mm_formats mmf
, lacls_co_third_party mmp
WHERE mml.concept_id = mmc.concept_id
AND mmf.format_id = mml.format_id
AND mmh.header_id = mml.header_id
AND mmf.reported_year = mmh.reported_year
AND mmp.party_id(+) = mml.party_id
AND NVL(mml.third_party_report,-999) <> -2
GROUP BY mml.header_id
, mml.format_id
, mmf.format_code
, mmf.reported_year
, mmf.description
, mml.concept_id
, mmh.company_id
, mmc.concept_code
, mml.ledger_id
, mml.period_name
, mml.party_id
, NVL(mmp.nit,to_char(mml.party_id))
, mmp.verifying_digit
, mmp.document_type
, mmp.name
, mmp.first_name
, mmp.second_name
, mmp.first_surname
, mmp.second_surname
, mmp.country_code
, mmp.state_code
, mmp.city_code
, mmp.address
, mmp.phone_number
, mmp.phone_number2
, mmp.cell_phone
, mmp.economic_activity
, mmp.email
, mmp.nature
, mml.code_combination_id
, mml.origin
, mml.source_id
, mml.code_combination_id_related
, mml.attribute_category
, mml.reported_flag
, mml.line_type_lookup_code
, mml.tax_type
, mml.tax_name
, mml.account_number_related
, mml.category
, mml.account_number
, mml.third_party_report
UNION ALL
-- Cuantias Menores
SELECT mml.header_id
, mml.format_id
, mmf.format_code
, mmf.reported_year
, mmf.description description
, mml.concept_id
, mmh.company_id
, mmc.concept_code
, mml.ledger_id
, mml.period_name
, to_number(mml.third_party_report) party_id
, mmp.nit nit
, mmp.verifying_digit
, mmp.document_type
, mmp.name name
, mmp.first_name
, mmp.second_name
, mmp.first_surname
, mmp.second_surname
, (select lpad(LCDC.DANE_CODE_ID,3,'0')
from LACLS_CO_DANE_COUNTRY_CODES LCDC
where LCDC.GEOGRAPHY_CODE = mmp.country_code) country_code
, mmp.state_code
, mmp.city_code
, mmp.address
, mmp.phone_number
, mmp.phone_number2
, mmp.cell_phone
, mmp.economic_activity
, mmp.email
, mmp.nature
, mml.code_combination_id
, mml.origin
, mml.source_id
, mml.code_combination_id_related
, mml.attribute_category
, mml.reported_flag
, mml.line_type_lookup_code
, mml.tax_type
, mml.tax_name
, mml.account_number_related
, mml.category
, mml.account_number
, mml.third_party_report
, ROUND(SUM(mml.reported_value_1)) reported_value_1
, ROUND(SUM(mml.reported_value_2)) reported_value_2
, ROUND(SUM(mml.reported_value_3)) reported_value_3
, ROUND(SUM(mml.reported_value_4)) reported_value_4
, ROUND(SUM(mml.reported_value_5)) reported_value_5
, ROUND(SUM(mml.reported_value_6)) reported_value_6
, ROUND(SUM(mml.reported_value_7)) reported_value_7
, ROUND(SUM(mml.reported_value_8)) reported_value_8
, ROUND(SUM(mml.reported_value_9)) reported_value_9
, ROUND(SUM(mml.reported_value_10)) reported_value_10
, ROUND(SUM(mml.reported_value_11)) reported_value_11
, ROUND(SUM(mml.reported_value_12)) reported_value_12
, ROUND(SUM(mml.reported_value_13)) reported_value_13
, ROUND(SUM(mml.reported_value_14)) reported_value_14
, ROUND(SUM(mml.reported_value_15)) reported_value_15
, ROUND(SUM(mml.reported_value_16)) reported_value_16
, ROUND(SUM(mml.reported_value_17)) reported_value_17
, ROUND(SUM(mml.reported_value_18)) reported_value_18
, ROUND(SUM(mml.reported_value_19)) reported_value_19
, ROUND(SUM(mml.reported_value_20)) reported_value_20
FROM lacls_co_mm_header mmh
, lacls_co_mm_lines mml
, lacls_co_mm_concepts mmc
, lacls_co_mm_formats mmf
, lacls_co_third_party mmp
WHERE mml.concept_id = mmc.concept_id
AND mmf.format_id = mml.format_id
AND mmh.header_id = mml.header_id
AND mmf.reported_year = mmh.reported_year
AND mmp.party_id = mml.third_party_report
AND mml.third_party_report = -2
GROUP BY mml.header_id
, mml.format_id
, mmf.format_code
, mmf.reported_year
, mmf.description
, mml.concept_id
, mmh.company_id
, mmc.concept_code
, mml.ledger_id
, mml.period_name
, mml.third_party_report
, mmp.nit
, mmp.verifying_digit
, mmp.document_type
, mmp.name
, mmp.first_name
, mmp.second_name
, mmp.first_surname
, mmp.second_surname
, mmp.country_code
, mmp.state_code
, mmp.city_code
, mmp.address
, mmp.phone_number
, mmp.phone_number2
, mmp.cell_phone
, mmp.economic_activity
, mmp.email
, mmp.nature
, mml.code_combination_id
, mml.origin
, mml.source_id
, mml.code_combination_id_related
, mml.attribute_category
, mml.reported_flag
, mml.line_type_lookup_code
, mml.tax_type
, mml.tax_name
, mml.account_number_related
, mml.category
, mml.account_number
, mml.third_party_report;